lib/CXGN/Trial/Search.pm

   1 package CXGN::Trial::Search;
   2
   3 =head1 NAME
   4
   5 CXGN::Trial::Search - an object to handle searching for trials given criteria
   6
   7 =head1 USAGE
   8
   9 my $trial_search = CXGN::Trial::Search->new({
  10     bcs_schema=>$schema,
  11     location_list=>\@locations,
  12     program_list=>\@breeding_program_names,
  13     program_id_list=>\@breeding_programs_ids,
  14     year_list=>\@years,
  15     trial_type_list=>\@trial_types,
  16     trial_id_list=>\@trial_ids,
  17     trial_name_list=>\@trial_names,
  18     trial_name_is_exact=>1
  19 });
  20 my ($result, $total_count) = $trial_search->search();
  21
  22 =head1 DESCRIPTION
  23
  24
  25 =head1 AUTHORS
  26
  27  With code adapted from SGN::Controller::AJAX::Search::Trial
  28
  29 =cut
  30
  31 use strict;
  32 use warnings;
  33 use Moose;
  34 use Try::Tiny;
  35 use Data::Dumper;
  36 use SGN::Model::Cvterm;
  37 use CXGN::Calendar;
  38 use JSON;
  39
  40 has 'bcs_schema' => ( isa => 'Bio::Chado::Schema',
  41     is => 'rw',
  42     required => 1,
  43 );
  44
  45 has 'program_list' => (
  46     isa => 'ArrayRef[Str]|Undef',
  47     is => 'rw',
  48 );
  49
  50 has 'program_id_list' => (
  51     isa => 'ArrayRef[Int]|Undef',
  52     is => 'rw',
  53 );
  54
  55 has 'location_list' => (
  56     isa => 'ArrayRef[Str]|Undef',
  57     is => 'rw',
  58 );
  59
  60 has 'location_id_list' => (
  61     isa => 'ArrayRef[Int]|Undef',
  62     is => 'rw',
  63 );
  64
  65 has 'year_list' => (
  66     isa => 'ArrayRef[Str]|Undef',
  67     is => 'rw',
  68 );
  69
  70 has 'trial_type_list' => (
  71     isa => 'ArrayRef[Str]|Undef',
  72     is => 'rw',
  73 );
  74
  75 has 'trial_id_list' => (
  76     isa => 'ArrayRef[Int]|Undef',
  77     is => 'rw',
  78 );
  79
  80 has 'trial_name_list' => (
  81     isa => 'ArrayRef[Str]|Undef',
  82     is => 'rw',
  83 );
  84
  85 has 'folder_id_list' => (
  86     isa => 'ArrayRef[Int]|Undef',
  87     is => 'rw',
  88 );
  89
  90 has 'folder_name_list' => (
  91     isa => 'ArrayRef[Str]|Undef',
  92     is => 'rw',
  93 );
  94
  95 has 'trial_name_is_exact' => (
  96     isa => 'Bool|Undef',
  97     is => 'rw',
  98     default => 0
  99 );
 100
 101 has 'accession_list' => (
 102     isa => 'ArrayRef[Int]|Undef',
 103     is => 'rw',
 104 );
 105
 106 has 'accession_name_list' => (
 107     isa => 'ArrayRef[Str]|Undef',
 108     is => 'rw',
 109 );
 110
 111 has 'trial_design_list' => (
 112     isa => 'ArrayRef[Str]|Undef',
 113     is => 'rw',
 114 );
 115
 116 has 'trait_list' => (
 117     isa => 'ArrayRef[Int]|Undef',
 118     is => 'rw',
 119 );
 120
 121 has 'externalReferenceSources' => (
 122     isa => 'ArrayRef[Str]|Undef',
 123     is => 'rw',
 124 );
 125
 126 has 'externalReferenceIds' => (
 127     isa => 'ArrayRef[Str]|Undef',
 128     is => 'rw',
 129 );
 130
 131 has 'trial_has_tissue_samples' => (
 132     isa => 'Bool|Undef',
 133     is => 'rw',
 134     default => 0
 135 );
 136
 137 has 'field_trials_only' => (
 138     isa => 'Bool|Undef',
 139     is => 'rw',
 140     default => 0
 141 );
 142
 143 has 'sort_by' => (
 144     isa => 'Str|Undef',
 145     is => 'rw'
 146 );
 147
 148 has 'order_by' => (
 149     isa => 'Str|Undef',
 150     is => 'rw'
 151 );
 152
 153 has 'limit' => (
 154     isa => 'Int|Undef',
 155     is => 'rw'
 156 );
 157
 158 has 'offset' => (
 159     isa => 'Int|Undef',
 160     is => 'rw'
 161 );
 162
 163 sub search {
 164     my $self = shift;
 165     my $schema = $self->bcs_schema();
 166     my $program_list = $self->program_list;
 167     my $program_id_list = $self->program_id_list;
 168     my $location_list = $self->location_list;
 169     my $location_id_list = $self->location_id_list;
 170     my $year_list = $self->year_list;
 171     my $trial_type_list = $self->trial_type_list;
 172     my $trial_id_list = $self->trial_id_list;
 173     my $trial_name_list = $self->trial_name_list;
 174     my $folder_id_list = $self->folder_id_list;
 175     my $folder_name_list = $self->folder_name_list;
 176     my $trial_design_list = $self->trial_design_list;
 177     my $trial_name_is_exact = $self->trial_name_is_exact;
 178     my $accession_list = $self->accession_list;
 179     my $accession_name_list = $self->accession_name_list;
 180     my $trial_has_tissue_samples = $self->trial_has_tissue_samples;
 181     my $trait_list = $self->trait_list;
 182     my $limit = $self->limit;
 183     my $offset = $self->offset;
 184     my $externalReferenceIds = $self->externalReferenceIds();
 185     my $externalReferenceSources = $self->externalReferenceSources();
 186     my $sort_by = $self->sort_by;
 187     my $order_by = $self->order_by;
 188
 189     my $accession_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'accession', 'stock_type')->cvterm_id();
 190     my $phenotyping_experiment_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'phenotyping_experiment', 'experiment_type')->cvterm_id();
 191     my $breeding_program_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'breeding_program', 'project_property')->cvterm_id();
 192     my $breeding_program_trial_relationship_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'breeding_program_trial_relationship', 'project_relationship')->cvterm_id();
 193     my $trial_folder_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'trial_folder', 'project_property')->cvterm_id();
 194     my $analysis_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'analysis_metadata_json', 'project_property')->cvterm_id();
 195     my $cross_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'cross', 'stock_type')->cvterm_id();
 196     my $location_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'project location', 'project_property')->cvterm_id();
 197     my $year_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'project year', 'project_property')->cvterm_id();
 198     my $design_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'design', 'project_property')->cvterm_id();
 199     my $harvest_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'project_harvest_date', 'project_property')->cvterm_id();
 200     my $planting_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'project_planting_date', 'project_property')->cvterm_id();
 201     my $project_has_tissue_sample_entries = SGN::Model::Cvterm->get_cvterm_row($schema, 'project_has_tissue_sample_entries', 'project_property')->cvterm_id();
 202     my $genotyping_facility_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'genotyping_facility', 'project_property')->cvterm_id();
 203     my $genotyping_facility_submitted_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'genotyping_facility_submitted', 'project_property')->cvterm_id();
 204     my $genotyping_facility_status_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'genotyping_facility_status', 'project_property')->cvterm_id();
 205     my $genotyping_plate_format_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'genotyping_plate_format', 'project_property')->cvterm_id();
 206     my $genotyping_plate_sample_type_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'genotyping_plate_sample_type', 'project_property')->cvterm_id();
 207     my $genotyping_facility_plate_id_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'genotyping_facility_plate_id', 'project_property')->cvterm_id();
 208     my $sampling_facility_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'sampling_facility', 'project_property')->cvterm_id();
 209     my $sampling_facility_sample_type_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'sampling_trial_sample_type', 'project_property')->cvterm_id();
 210     my $additional_info_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($self->bcs_schema,'project_additional_info', 'project_property')->cvterm_id();
 211
 212     my $calendar_funcs = CXGN::Calendar->new({});
 213
 214     my $project_type_cv_id = $schema->resultset("Cv::Cv")->find( { name => "project_type" } )->cv_id();
 215     my $project_type_rs = $schema->resultset("Cv::Cvterm")->search( { cv_id => $project_type_cv_id } );
 216     my %trial_types;
 217     while ( my $row = $project_type_rs->next() ) {
 218         $trial_types{ $row->cvterm_id } = $row->name();
 219     }
 220     my $trial_types_sql = join ("," , keys %trial_types);
 221
 222     my $not_trials_rs = $schema->resultset("Project::Projectprop")->search(
 223         [
 224             { type_id => $breeding_program_cvterm_id },
 225             { type_id => $trial_folder_cvterm_id },
 226             { type_id => $cross_cvterm_id },
 227             { type_id => $analysis_cvterm_id}
 228         ]
 229     );
 230     my %not_trials;
 231     while ( my $row = $not_trials_rs->next() ) {
 232         $not_trials{ $row->project_id() } = 1;
 233     }
 234
 235     my %locations;
 236     my $location_rs = $schema->resultset("NaturalDiversity::NdGeolocation")->search( {} );
 237     while ( my $row = $location_rs->next() ) {
 238         $locations{ $row->nd_geolocation_id() } = $row->description();
 239     }
 240
 241     my @where_clause;
 242
 243     if ($trial_has_tissue_samples){
 244         push @where_clause, "trial_has_tissue_samples.value IS NOT NULL";
 245     }
 246
 247     if ($program_id_list && scalar(@$program_id_list)>0) {
 248         my $sql = join ("," , @$program_id_list);
 249         push @where_clause, "breeding_program.project_id in ($sql)";
 250     }
 251     if ($program_list && scalar(@$program_list)>0) {
 252         my $sql = join ("','" , @$program_list);
 253         my $program_sql = "'" . $sql . "'";
 254         push @where_clause, "breeding_program.name in ($program_sql)";
 255     }
 256     if ($year_list && scalar(@$year_list)>0) {
 257         my $sql = join ("','" , @$year_list);
 258         my $year_sql = "'" . $sql . "'";
 259         push @where_clause, "year.value in ($year_sql)";
 260     }
 261     if ($trial_type_list && scalar(@$trial_type_list)>0) {
 262         my $sql = join ("','" , @$trial_type_list);
 263         my $trial_type_sql = "'" . $sql . "'";
 264         push @where_clause, "(trial_type_name.name in ($trial_type_sql) OR projectprop.value in ($trial_type_sql))";
 265     }
 266     if ($trial_id_list && scalar(@$trial_id_list)>0) {
 267         my $sql = join ("," , @$trial_id_list);
 268         push @where_clause, "study.project_id in ($sql)";
 269     }
 270     if ($trial_name_is_exact){
 271         if ($trial_name_list && scalar(@$trial_name_list)>0) {
 272             my $sql = join ("','" , @$trial_name_list);
 273             my $trial_sql = "'" . $sql . "'";
 274             push @where_clause, "study.name in ($trial_sql)";
 275         }
 276     } else {
 277         if ($trial_name_list && scalar(@$trial_name_list)>0) {
 278             my @or_clause;
 279             foreach (@$trial_name_list){
 280                 push @or_clause, "study.name LIKE '%".$_."%'";
 281             }
 282             my $sql = join (" OR " , @or_clause);
 283             push @where_clause, "($sql)";
 284         }
 285     }
 286     if ($folder_id_list && scalar(@$folder_id_list)>0) {
 287         my $sql = join ("," , @$folder_id_list);
 288         push @where_clause, "folder.project_id in ($sql)";
 289     }
 290     if ($folder_name_list && scalar(@$folder_name_list)>0) {
 291         my $sql = join ("','" , @$folder_name_list);
 292         my $folder_sql = "'" . $sql . "'";
 293         push @where_clause, "folder.name in ($folder_sql)";
 294     }
 295     if ($trial_design_list && scalar(@$trial_design_list)>0) {
 296         my $sql = join ("','" , @$trial_design_list);
 297         my $design_sql = "'" . $sql . "'";
 298         push @where_clause, "design.value in ($design_sql)";
 299     }
 300     if ($location_id_list && scalar(@$location_id_list)>0) {
 301         my $sql = join ("','" , @$location_id_list);
 302         my $location_sql = "'" . $sql . "'";
 303         push @where_clause, "location.value in ($location_sql)";
 304     }
 305     my $accession_join = '';
 306     if ( ($accession_list && scalar(@$accession_list)>0) || ($accession_name_list && scalar(@$accession_name_list)>0) ) {
 307         $accession_join = " JOIN nd_experiment_project ON(study.project_id=nd_experiment_project.project_id) JOIN nd_experiment USING(nd_experiment_id) JOIN nd_experiment_stock USING(nd_experiment_id) JOIN stock AS obs_unit ON(nd_experiment_stock.stock_id=obs_unit.stock_id) JOIN stock_relationship ON(stock_relationship.subject_id=obs_unit.stock_id) JOIN stock AS accession ON(stock_relationship.object_id=accession.stock_id AND accession.type_id=$accession_cvterm_id) ";
 308     }
 309     if ($accession_list && scalar(@$accession_list)>0) {
 310         my $sql = join ("," , @$accession_list);
 311         push @where_clause, "accession.stock_id in ($sql)";
 312     }
 313     if ($accession_name_list && scalar(@$accession_name_list)>0) {
 314         my $sql = join ("','" , @$accession_name_list);
 315         my $accession_sql = "'" . $sql . "'";
 316         push @where_clause, "accession.uniquename in ($accession_sql)";
 317     }
 318
 319     my $trait_join = '';
 320     if ($trait_list && scalar(@$trait_list)>0) {
 321         my $sql = join ("," , @$trait_list);
 322         push @where_clause, "phenotype.cvalue_id in ($sql)";
 323         $trait_join = " JOIN nd_experiment_project ON(study.project_id=nd_experiment_project.project_id) JOIN nd_experiment AS trial_experiment ON(trial_experiment.nd_experiment_id=nd_experiment_project.nd_experiment_id) JOIN nd_experiment_stock ON(trial_experiment.nd_experiment_id=nd_experiment_stock.nd_experiment_id) JOIN stock AS obs_unit ON(nd_experiment_stock.stock_id=obs_unit.stock_id) JOIN nd_experiment_stock AS nd_experiment_stock_obs ON(nd_experiment_stock_obs.stock_id=obs_unit.stock_id) JOIN nd_experiment AS phenotype_experiment ON(nd_experiment_stock_obs.nd_experiment_id=phenotype_experiment.nd_experiment_id AND phenotype_experiment.type_id=$phenotyping_experiment_cvterm_id) JOIN nd_experiment_phenotype ON(nd_experiment_phenotype.nd_experiment_id=phenotype_experiment.nd_experiment_id) JOIN phenotype USING(phenotype_id) ";
 324     }
 325
 326     my @refSource_whereClause = ();
 327     foreach my $refSource (@$externalReferenceSources){
 328         push @refSource_whereClause, "'$refSource' = ANY(xref.xrefSources)"
 329     }
 330     my $refSource_whereClause_str = join (" OR " ,@refSource_whereClause);
 331     if ($refSource_whereClause_str){
 332         push @where_clause, "( $refSource_whereClause_str )";
 333     }
 334
 335     # see  https://plant-breeding-api.readthedocs.io/en/latest/docs/best_practices/Search_Services.html#request-and-response-rules
 336     my @refId_whereClause = ();
 337     foreach my $refId (@$externalReferenceIds){
 338         push @refId_whereClause, "'$refId' = ANY(xref.xrefIds)"
 339     }
 340     my $refId_whereClause_str = join (" OR " ,@refId_whereClause);
 341     if ($refSource_whereClause_str){
 342         push @where_clause, "( $refId_whereClause_str )";
 343     }
 344
 345     my $where_clause = scalar(@where_clause)>0 ? " WHERE " . (join (" AND " , @where_clause)) : '';
 346
 347     if(!$sort_by) {
 348         $sort_by = " ORDER BY study.name ";
 349     }
 350
 351     my $q = "SELECT study.name, study.project_id, study.description, folder.name, folder.project_id, folder.description, trial_type_name.cvterm_id, trial_type_name.name, projectprop.value as trial_type_value, year.value, location.value, breeding_program.name, breeding_program.project_id, breeding_program.description, harvest_date.value, planting_date.value, design.value, genotyping_facility.value, genotyping_facility_submitted.value, genotyping_facility_status.value, genotyping_plate_format.value, genotyping_plate_sample_type.value, genotyping_facility_plate_id.value, sampling_facility.value, sampling_facility_sample_type.value, project_additional_info.value, count(study.project_id) OVER() AS full_count, xref.xrefjson
 352         FROM project AS study
 353         JOIN project_relationship AS bp_rel ON(study.project_id=bp_rel.subject_project_id AND bp_rel.type_id=$breeding_program_trial_relationship_id)
 354         JOIN project AS breeding_program ON(bp_rel.object_project_id=breeding_program.project_id)
 355         LEFT JOIN project_relationship AS folder_rel ON(study.project_id=folder_rel.subject_project_id AND folder_rel.type_id=$trial_folder_cvterm_id)
 356         LEFT JOIN project AS folder ON(folder_rel.object_project_id=folder.project_id)
 357         LEFT JOIN projectprop ON(study.project_id=projectprop.project_id AND projectprop.type_id IN ($trial_types_sql))
 358         LEFT JOIN cvterm AS trial_type_name ON(projectprop.type_id=trial_type_name.cvterm_id)
 359         LEFT JOIN cv AS project_type ON(trial_type_name.cv_id=project_type.cv_id AND project_type.name='project_type')
 360         LEFT JOIN projectprop AS year ON(study.project_id=year.project_id AND year.type_id=$year_cvterm_id)
 361         LEFT JOIN projectprop AS location ON(study.project_id=location.project_id AND location.type_id=$location_cvterm_id)
 362         LEFT JOIN projectprop AS harvest_date ON(study.project_id=harvest_date.project_id AND harvest_date.type_id=$harvest_cvterm_id)
 363         LEFT JOIN projectprop AS planting_date ON(study.project_id=planting_date.project_id AND planting_date.type_id=$planting_cvterm_id)
 364         LEFT JOIN projectprop AS design ON(study.project_id=design.project_id AND design.type_id=$design_cvterm_id)
 365         LEFT JOIN projectprop AS trial_has_tissue_samples ON(study.project_id=trial_has_tissue_samples.project_id AND trial_has_tissue_samples.type_id=$project_has_tissue_sample_entries)
 366         LEFT JOIN projectprop AS genotyping_facility ON(study.project_id=genotyping_facility.project_id AND genotyping_facility.type_id=$genotyping_facility_cvterm_id)
 367         LEFT JOIN projectprop AS genotyping_facility_submitted ON(study.project_id=genotyping_facility_submitted.project_id AND genotyping_facility_submitted.type_id=$genotyping_facility_submitted_cvterm_id)
 368         LEFT JOIN projectprop AS genotyping_facility_status ON(study.project_id=genotyping_facility_status.project_id AND genotyping_facility_status.type_id=$genotyping_facility_status_cvterm_id)
 369         LEFT JOIN projectprop AS genotyping_plate_format ON(study.project_id=genotyping_plate_format.project_id AND genotyping_plate_format.type_id=$genotyping_plate_format_cvterm_id)
 370         LEFT JOIN projectprop AS genotyping_plate_sample_type ON(study.project_id=genotyping_plate_sample_type.project_id AND genotyping_plate_sample_type.type_id=$genotyping_plate_sample_type_cvterm_id)
 371         LEFT JOIN projectprop AS genotyping_facility_plate_id ON(study.project_id=genotyping_facility_plate_id.project_id AND genotyping_facility_plate_id.type_id=$genotyping_facility_plate_id_cvterm_id)
 372         LEFT JOIN projectprop AS sampling_facility ON(study.project_id=sampling_facility.project_id AND sampling_facility.type_id=$sampling_facility_cvterm_id)
 373         LEFT JOIN projectprop AS sampling_facility_sample_type ON(study.project_id=sampling_facility_sample_type.project_id AND sampling_facility_sample_type.type_id=$sampling_facility_sample_type_cvterm_id)
 374         LEFT JOIN projectprop AS project_additional_info ON(study.project_id=project_additional_info.project_id AND project_additional_info.type_id=$additional_info_cvterm_id)
 375         LEFT JOIN (
 376                 select a.project_id, array_agg(referenceId) as xrefIds, array_agg(referenceSource) as xrefSources, jsonb_agg(jsonb_build_object('referenceSource', a.referenceSource, 'referenceId', a.referenceId)) as xrefjson
 377                     from (
 378                         select name as referenceSource, dbxref.accession as referenceId, pd.project_id
 379                             from db
 380                                 join dbxref on db.db_id = dbxref.db_id
 381                                 join project_dbxref pd on dbxref.dbxref_id = pd.dbxref_id
 382                     ) a
 383                     group by a.project_id
 384          ) xref on xref.project_id = study.project_id
 385
 386         $accession_join
 387         $trait_join
 388         $where_clause
 389         GROUP BY(study.name, study.project_id, study.description, folder.name, folder.project_id, folder.description, trial_type_name.cvterm_id, trial_type_name.name, projectprop.value, year.value, location.value, breeding_program.name, breeding_program.project_id, breeding_program.description, harvest_date.value, planting_date.value, design.value, genotyping_facility.value, genotyping_facility_submitted.value, genotyping_facility_status.value, genotyping_plate_format.value, genotyping_plate_sample_type.value, genotyping_facility_plate_id.value, sampling_facility.value, sampling_facility_sample_type.value, project_additional_info.value, xref.xrefjson) " .
 390         $sort_by . $order_by . ";";
 391
 392     print STDERR Dumper $q;
 393     my $h = $schema->storage->dbh()->prepare($q);
 394     $h->execute();
 395
 396     my @result;
 397     my $total_count = 0;
 398     my $subtract_count = 0;
 399     while (my ($study_name, $study_id, $study_description, $folder_name, $folder_id, $folder_description, $trial_type_id, $trial_type_name, $trial_type_value, $year, $location_id, $breeding_program_name, $breeding_program_id, $breeding_program_description, $harvest_date, $planting_date, $design, $genotyping_facility, $genotyping_facility_submitted, $genotyping_facility_status, $genotyping_plate_format, $genotyping_plate_sample_type, $genotyping_facility_plate_id, $sampling_facility, $sampling_facility_sample_type, $project_additional_info, $full_count) = $h->fetchrow_array()) {
 400         my $location_name = $location_id ? $locations{$location_id} : '';
 401         my $project_harvest_date = $harvest_date ? $calendar_funcs->display_start_date($harvest_date) : '';
 402         my $project_planting_date = $planting_date ? $calendar_funcs->display_start_date($planting_date) : '';
 403
 404         #In the future a 'project_class' would make this more clean by differentiating different project classes explicitly
 405         if ( $not_trials{$study_id} ) {
 406             $subtract_count++;
 407             next;
 408         }
 409         if ($self->field_trials_only){
 410             if ($design && ($design eq 'treatment' || $design eq 'genotype_data_project' || $design eq 'pcr_genotype_data_project' || $design eq 'genotyping_plate' || $design eq 'sampling_trial' || $design eq 'drone_run' || $design eq 'drone_run_band')) {
 411                 $subtract_count++;
 412                 next();
 413             }
 414             if ($trial_type_name && ($trial_type_name eq 'crossing_trial')) {
 415                 $subtract_count++;
 416                 next();
 417             }
 418         }
 419
 420         push @result, {
 421             trial_id                      => $study_id,
 422             trial_name                    => $study_name,
 423             description                   => $study_description,
 424             folder_id                     => $folder_id,
 425             folder_name                   => $folder_name,
 426             folder_description            => $folder_description,
 427             trial_type                    => $trial_type_name,
 428             trial_type_name               => $trial_type_name,
 429             trial_type_id                 => $trial_type_id,
 430             trial_type_value              => $trial_type_value,
 431             year                          => $year,
 432             location_id                   => $location_id,
 433             location_name                 => $location_name,
 434             breeding_program_id           => $breeding_program_id,
 435             breeding_program_name         => $breeding_program_name,
 436             breeding_program_description  => $breeding_program_description,
 437             project_harvest_date          => $project_harvest_date,
 438             project_planting_date         => $project_planting_date,
 439             design                        => $design,
 440             genotyping_facility           => $genotyping_facility,
 441             genotyping_facility_submitted => $genotyping_facility_submitted,
 442             genotyping_facility_status    => $genotyping_facility_status,
 443             genotyping_plate_format       => $genotyping_plate_format,
 444             genotyping_plate_sample_type  => $genotyping_plate_sample_type,
 445             genotyping_facility_plate_id  => $genotyping_facility_plate_id,
 446             sampling_facility             => $sampling_facility,
 447             sampling_trial_sample_type    => $sampling_facility_sample_type,
 448             additional_info               => $project_additional_info ? decode_json($project_additional_info) : undef
 449         };
 450         $total_count = $full_count;
 451     }
 452
 453     #pagination in sql query not possible unitl we have project_class explicitly
 454     my @data_window;
 455     if (($limit && defined($limit) || ($offset && defined($offset)))){
 456         my $start = $offset;
 457         my $end = $offset + $limit - 1;
 458         for( my $i = $start; $i <= $end; $i++ ) {
 459             if ($result[$i]) {
 460                 push @data_window, $result[$i];
 461             }
 462         }
 463     } else {
 464         @data_window = @result;
 465     }
 466
 467     #print STDERR "TOTAL: $total_count SUBTRACT: $subtract_count \n";
 468     $total_count = $total_count-$subtract_count;
 469     #print STDERR Dumper \@result;
 470
 471     return (\@data_window, $total_count);
 472 }
 473
 474 1;