Merge pull request #5248 from solgenomics/topic/batch_update_trials
[sgn.git] / lib / CXGN / BrAPI / v1 / GermplasmAttributes.pm
blob148d9928cdf3f80ce2df7db4ccc3d56388aa7917
1 package CXGN::BrAPI::v1::GermplasmAttributes;
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;
11 extends 'CXGN::BrAPI::v1::Common';
13 sub search {
14 my $self = shift;
15 my $inputs = shift;
16 my @attribute_category_dbids = $inputs->{attribute_category_dbids} ? @{$inputs->{attribute_category_dbids}} : ();
18 my $page_size = $self->page_size;
19 my $page = $self->page;
20 my $status = $self->status;
21 my @data;
22 my $where_clause = '';
23 if (scalar(@attribute_category_dbids)>0) {
24 my $s = join ',', @attribute_category_dbids;
25 $where_clause .= "AND cv.cv_id IN ($s)";
27 my $accession_type_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($self->bcs_schema, 'accession', 'stock_type')->cvterm_id();
28 my $q = "SELECT cv.cv_id, cv.name, cv.definition, b.cvterm_id, b.name, b.definition, stockprop.value
29 FROM stockprop
30 JOIN stock using(stock_id)
31 JOIN cvterm as b on (stockprop.type_id=b.cvterm_id)
32 JOIN cv on (b.cv_id=cv.cv_id)
33 WHERE stock.type_id=?
34 $where_clause
35 ORDER BY cv.cv_id;";
37 my $h = $self->bcs_schema()->storage->dbh()->prepare($q);
38 $h->execute($accession_type_cvterm_id);
39 my %attribute_hash;
40 while (my ($attributeCategoryDbId, $attributeCategoryName, $attributeCategoryDesc, $attributeDbId, $name, $description, $value) = $h->fetchrow_array()) {
41 if (exists($attribute_hash{$attributeDbId})) {
42 my $values = $attribute_hash{$attributeDbId}->[5];
43 push @$values, $value;
44 $attribute_hash{$attributeDbId}->[5] = $values;
45 } else {
46 $attribute_hash{$attributeDbId} = [$attributeCategoryDbId, $attributeCategoryName, $attributeCategoryDesc, $name, $description, [$value]];
50 foreach (keys %attribute_hash) {
51 my $prophash = $self->get_cvtermprop_hash($_);
52 my $attributeCategoryDbId = $attribute_hash{$_}->[0];
53 push @data, {
54 attributeDbId => "$_",
55 code => $prophash->{'code'} ? join ',', @{$prophash->{'code'}} : '',
56 uri => $prophash->{'uri'} ? join ',', @{$prophash->{'uri'}} : '',
57 name => $attribute_hash{$_}->[3],
58 description => $attribute_hash{$_}->[4],
59 attributeCategoryDbId => "$attributeCategoryDbId",
60 #attributeCategoryName => $attribute_hash{$_}->[1],
61 datatype => $prophash->{'datatype'} ? join ',', @{$prophash->{'datatype'}} : '',
62 values => $attribute_hash{$_}->[5]
66 my ($data_window, $pagination) = CXGN::BrAPI::Pagination->paginate_array(\@data,$page_size,$page);
67 my %result = (data => $data_window);
68 my @data_files;
69 return CXGN::BrAPI::JSONResponse->return_success(\%result, $pagination, \@data_files, $status, 'Germplasm-attributes list result constructed');
72 sub germplasm_attributes_categories_list {
73 my $self = shift;
75 my $page_size = $self->page_size;
76 my $page = $self->page;
77 my $status = $self->status;
78 my $accession_type_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($self->bcs_schema, 'accession', 'stock_type')->cvterm_id();
79 my $q = "SELECT distinct(cv.cv_id), cv.name, cv.definition
80 FROM stockprop
81 JOIN stock using(stock_id)
82 JOIN cvterm as b on (stockprop.type_id=b.cvterm_id)
83 JOIN cv on (b.cv_id=cv.cv_id)
84 WHERE stock.type_id=?
85 GROUP BY (cv.cv_id)
86 ORDER BY cv.cv_id;";
88 my $h = $self->bcs_schema()->storage->dbh()->prepare($q);
89 $h->execute($accession_type_cvterm_id);
90 my @data;
91 while (my ($attributeCategoryDbId, $attributeCategoryName, $attributeCategoryDesc) = $h->fetchrow_array()) {
92 push @data, {
93 attributeCategoryDbId => "$attributeCategoryDbId",
94 name => $attributeCategoryName,
95 attributeCategoryName => $attributeCategoryName
98 my ($data_window, $pagination) = CXGN::BrAPI::Pagination->paginate_array(\@data,$page_size,$page);
99 my %result = (data => $data_window);
100 my @data_files;
101 return CXGN::BrAPI::JSONResponse->return_success(\%result, $pagination, \@data_files, $status, 'Germplasm-attributes categories list result constructed');
105 sub germplasm_attributes_germplasm_detail {
106 my $self = shift;
107 my $inputs = shift;
108 my $stock_id = $inputs->{stock_id};
109 my @attribute_dbids = $inputs->{attribute_dbids} ? @{$inputs->{attribute_dbids}} : ();
111 my $page_size = $self->page_size;
112 my $page = $self->page;
113 my $status = $self->status;
115 my $where = '';
116 if (scalar(@attribute_dbids)>0){
117 my $sql = join ',', @attribute_dbids;
118 $where = "and b.cvterm_id IN ($sql)";
121 my $offset = $page_size*$page;
122 my $limit = $page_size;
123 my $accession_type_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($self->bcs_schema, 'accession', 'stock_type')->cvterm_id();
124 my $q = "SELECT cv.cv_id, cv.name, cv.definition, b.cvterm_id, b.name, b.definition, stockprop.value, stockprop.stockprop_id, count(stockprop.value) OVER() AS full_count
125 FROM stockprop
126 JOIN stock using(stock_id)
127 JOIN cvterm as b on (stockprop.type_id=b.cvterm_id)
128 JOIN cv on (b.cv_id=cv.cv_id)
129 WHERE stock.type_id=? and stock.stock_id=? $where
130 ORDER BY cv.cv_id
131 LIMIT $limit
132 OFFSET $offset;";
134 my $h = $self->bcs_schema()->storage->dbh()->prepare($q);
135 $h->execute($accession_type_cvterm_id, $stock_id);
136 my @data;
137 my $total_count = 0;
138 while (my ($attributeCategoryDbId, $attributeCategoryName, $attributeCategoryDesc, $attributeDbId, $name, $description, $value, $stockprop_id, $count) = $h->fetchrow_array()) {
139 $total_count = $count;
140 push @data, {
141 attributeDbId => $attributeDbId,
142 attributeName => $name,
143 attributeCode => $name,
144 description => $description,
145 attributeCategoryDbId => $attributeCategoryDbId,
146 attributeCategoryName => $attributeCategoryName,
147 value => $value,
148 dateDetermined => '',
149 contextOfUse => [],
150 crop => undef,
151 defaultValue => undef,
152 documentationURL => undef,
153 growthStage => undef,
154 institution => undef,
155 language => undef,
156 method => {},
157 ontologyDbId =>,
158 ontologyName =>,
159 ontologyReference => {},
160 scale=> {},
161 scientist=> undef,
162 status=> undef,
163 submissionTimestamp=> undef,
164 synonyms => [],
165 trait => {},
166 xref=> undef
169 my %result = (
170 germplasmDbId=>$stock_id,
171 data => \@data
173 my @data_files;
174 my $pagination = CXGN::BrAPI::Pagination->pagination_response($total_count,$page_size,$page);
175 return CXGN::BrAPI::JSONResponse->return_success(\%result, $pagination, \@data_files, $status, 'Germplasm-attributes detail result constructed');
178 sub get_cvtermprop_hash {
179 my $self = shift;
180 my $cvterm_id = shift;
181 my $prop_rs = $self->bcs_schema->resultset('Cv::Cvtermprop')->search({'me.cvterm_id' => $cvterm_id}, {join=>['type'], +select=>['type.name', 'me.value'], +as=>['name', 'value']});
182 my $prop_hash;
183 while (my $r = $prop_rs->next()){
184 push @{ $prop_hash->{$r->get_column('name')} }, $r->get_column('value');
186 #print STDERR Dumper $prop_hash;
187 return $prop_hash;