lib/CXGN/BrAPI/v2/GermplasmAttributeValues.pm

   1 package CXGN::BrAPI::v2::GermplasmAttributeValues;
   2
   3 use Moose;
   4 use Data::Dumper;
   5 use SGN::Model::Cvterm;
   6 use CXGN::Trial;
   7 use CXGN::Chado::Stock;
   8 use CXGN::BrAPI::Pagination;
   9 use CXGN::BrAPI::JSONResponse;
  10
  11 extends 'CXGN::BrAPI::v2::Common';
  12
  13 sub search {
  14         my $self = shift;
  15         my $inputs = shift;
  16         my @attribute_dbids = $inputs->{attribute_dbids} ? @{$inputs->{attribute_dbids}} : ();
  17         my $value_id = $inputs->{attributeValueDbId} || ($inputs->{attributeValueDbIds} || ());
  18
  19         my $page_size = $self->page_size;
  20         my $page = $self->page;
  21         my $status = $self->status;
  22
  23         my $where = '';
  24         if (scalar(@attribute_dbids)>0){
  25                 my $sql = join ',', @attribute_dbids;
  26                 $where = "and b.cvterm_id IN ($sql)";
  27         }
  28         if ($value_id){
  29                 my $stock_ids;
  30                 my $attribute_ids;
  31                 foreach(@$value_id){
  32                         my ($stock_id, $attribute_id) = split(/b/, $_);
  33                         $stock_ids .=  $stock_id . ",";
  34                         $attribute_ids .=  $attribute_id . ",";
  35                 }
  36                 if($stock_ids && $attribute_ids){
  37                         chop($stock_ids);
  38                         chop($attribute_ids);
  39                         $where = $where . "and stock.stock_id IN ($stock_ids) and b.cvterm_id IN ($attribute_ids)";
  40                 }
  41         }
  42
  43         my $offset = $page_size*$page;
  44         my $limit = $page_size;
  45         my $accession_type_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($self->bcs_schema, 'accession', 'stock_type')->cvterm_id();
  46         my $q = "SELECT cv.cv_id, cv.name, cv.definition, b.cvterm_id, b.name, b.definition, stockprop.value, stockprop.stockprop_id, stock.stock_id, stock.name, count(stockprop.value) OVER() AS full_count
  47                 FROM stockprop
  48                 JOIN stock using(stock_id)
  49                 JOIN cvterm as b on (stockprop.type_id=b.cvterm_id)
  50                 JOIN cv on (b.cv_id=cv.cv_id)
  51                 WHERE stock.type_id=? $where
  52                 ORDER BY cv.cv_id
  53                 LIMIT $limit
  54                 OFFSET $offset;";
  55
  56         my $h = $self->bcs_schema()->storage->dbh()->prepare($q);
  57         $h->execute($accession_type_cvterm_id);
  58         my @data;
  59         my $total_count = 0;
  60         while (my ($attributeCategoryDbId, $attributeCategoryName, $attributeCategoryDesc, $attributeDbId, $name, $description, $value, $stockprop_id, $stock_id, $stock_name, $count) = $h->fetchrow_array()) {
  61                 $total_count = $count;
  62                 push @data, {
  63                         additionalInfo=>{},
  64                         germplasmDbId=> qq|$stock_id|,
  65                         germplasmName=> $stock_name,
  66                         attributeDbId => qq|$attributeDbId|,
  67                         attributeName => $name,
  68                         attributeValueDbId=> $stock_id."b".$attributeDbId,
  69                         value => $value,
  70                         determinedDate =>undef,
  71                 externalReferences=> [],
  72                 };
  73         }
  74         my %result = (
  75                 data => \@data
  76         );
  77         my @data_files;
  78         my $pagination = CXGN::BrAPI::Pagination->pagination_response($total_count,$page_size,$page);
  79         return CXGN::BrAPI::JSONResponse->return_success(\%result, $pagination, \@data_files, $status, 'Attribute values detail result constructed');
  80 }
  81
  82 sub _sql_from_arrayref {
  83     my $arrayref = shift;
  84     my $sql = join ("," , @$arrayref);
  85     return $sql;
  86 }
  87
  88 1;