lib/SGN/Controller/AJAX/Search/Stock.pm

   1
   2 package SGN::Controller::AJAX::Search::Stock;
   3
   4 use Moose;
   5
   6 BEGIN { extends 'Catalyst::Controller::REST' }
   7
   8 use Data::Dumper;
   9 use JSON::Any;
  10
  11
  12 __PACKAGE__->config(
  13     default   => 'application/json',
  14     stash_key => 'rest',
  15     map       => { 'application/json' => 'JSON', 'text/html' => 'JSON' },
  16    );
  17
  18
  19 sub stock_search :Path('/ajax/search/stocks') Args(0) {
  20     my $self = shift;
  21     my $c = shift;
  22
  23     my $params = $c->req->params() || {};
  24
  25     my %query;
  26
  27
  28     my $matchtype = $params->{any_name_matchtype};
  29     my $any_name  = $params->{any_name};
  30
  31     unless ($matchtype eq 'exactly') { #trim whitespace from both ends unless exact search was specified
  32       $any_name =~ s/^\s+|\s+$//g;
  33     }
  34
  35     my $schema = $c->dbic_schema("Bio::Chado::Schema", 'sgn_chado');
  36
  37     my ($or_conditions, $and_conditions);
  38     $or_conditions->{'me.stock_id'} =  { '>' => 0 };
  39     $and_conditions->{'me.stock_id'} = { '>' => 0 };
  40     if (exists($params->{any_name} ) && $params->{any_name} ) {
  41         my $start = '%';
  42         my $end = '%';
  43         if ( $matchtype eq 'exactly' ) {
  44             $start = '';
  45             $end = '';
  46         } elsif ( $matchtype eq 'starts_with' ) {
  47             $start = '';
  48         } elsif ( $matchtype eq 'ends_with' ) {
  49             $end = '';
  50         }
  51
  52         $or_conditions = [
  53             { 'me.name'          => {'ilike', $start.$any_name.$end} },
  54             { 'me.uniquename'    => {'ilike', $start.$any_name.$end} },
  55             { 'me.description'   => {'ilike', $start.$any_name.$end} },
  56             { 'stockprops.value'   => {'ilike', $start.$any_name.$end} }
  57             ] ;
  58     } else {
  59         $or_conditions = [ { 'me.uniquename' => { '!=', undef } } ];
  60     }
  61
  62
  63     ###############
  64     if (exists($params->{organism} ) && $params->{organism} ) {
  65         $and_conditions->{'me.organism_id'} = $params->{organism} ;
  66     }
  67
  68     if (exists($params->{stock_type} ) && $params->{stock_type} ) {
  69         $and_conditions->{'me.type_id'} = $params->{stock_type} ;
  70     }
  71
  72     if (exists($params->{person} ) && $params->{person} ) {
  73         my $editor = $params->{person};
  74         my ($first_name, $last_name ) = split ',' , $editor;
  75         $first_name =~ s/\s+//g;
  76         $last_name =~ s/\s+//g;
  77
  78         my $p_rs = $c->dbic_schema("CXGN::People::Schema")->resultset("SpPerson")->search(
  79             {
  80                 first_name => { 'ilike' , '%'.$first_name.'%' } ,
  81                 last_name  => { 'ilike' , '%'.$last_name.'%' }
  82             }
  83             );
  84
  85         my $stock_owner_rs = $c->dbic_schema("CXGN::Phenome::Schema")->resultset("StockOwner")->search(
  86             {
  87                 sp_person_id => { -in  => $p_rs->get_column('sp_person_id')->as_query },
  88             });
  89         my @stock_ids;
  90         while ( my $o = $stock_owner_rs->next ) {
  91             my $stock_id = $o->stock_id;
  92             push @stock_ids, $stock_id ;
  93         }
  94         my $stock_ids = $stock_owner_rs->get_column('stock_id');
  95         $and_conditions->{'me.stock_id'} = { '-in' => \@stock_ids } ;
  96     }
  97 ###############
  98     if (exists($params->{trait} ) && $params->{trait} ) {
  99         $and_conditions->{ 'observable.name' }  = $params->{trait} ;
 100     }
 101
 102     if (exists($params->{project} ) && $params->{project} ) {
 103         $and_conditions->{ 'lower(project.name)' } = { -like  => lc($params->{project} ) } ;
 104     }
 105
 106     if (exists($params->{location} ) && $params->{location} ) {
 107         $and_conditions->{ 'lower(nd_geolocation.description)' } = { -like  => lc($params->{location}) };
 108     }
 109
 110     if (exists($params->{year} ) && $params->{year} ) {
 111         $and_conditions->{ 'lower(projectprops.value)' } = { -like  => lc($params->{year} ) } ;
 112     }
 113
 114     if (exists($params->{organization} ) && $params->{organization} ) {
 115         $and_conditions->{ 'project_relationship_subject_projects.object_project_id' } = $params->{organization} ;
 116
 117     }
 118
 119     my $draw = $params->{draw};
 120     $draw =~ s/\D//g; # cast to int
 121
 122     my $rows = $params->{length} || 10;
 123     my $start = $params->{start};
 124
 125     my $page = int($start / $rows)+1;
 126
 127     # get the count first
 128     my $rs = $schema->resultset("Stock::Stock")->search(
 129         {
 130             'me.is_obsolete'   => 'f',
 131             -and => [
 132                  $or_conditions,
 133                  $and_conditions
 134                 ],
 135         },
 136         {
 137             join => ['type', 'organism', 'stockprops', { nd_experiment_stocks => { nd_experiment => {'nd_experiment_phenotypes' => {'phenotype' => 'observable' }}}}, { nd_experiment_stocks => { nd_experiment => { 'nd_experiment_projects' => { 'project' => ['projectprops', 'project_relationship_subject_projects'] }  } } }, { nd_experiment_stocks => { nd_experiment => 'nd_geolocation' } } ],
 138             distinct => 1,
 139         }
 140     );
 141
 142
 143     my $records_total = $rs->count();
 144
 145     #
 146     my $rs2 = $schema->resultset("Stock::Stock")->search(
 147         {
 148             'me.is_obsolete'   => 'f',
 149             -and => [
 150                  $or_conditions,
 151                  $and_conditions
 152                 ],
 153         } ,
 154         {
 155             join => ['type', 'organism', 'stockprops', { nd_experiment_stocks => { nd_experiment => {'nd_experiment_phenotypes' => {'phenotype' => 'observable' }}}} ,  { nd_experiment_stocks => { nd_experiment => { 'nd_experiment_projects' => { 'project' => ['projectprops', 'project_relationship_subject_projects'] } } } } , { nd_experiment_stocks => { nd_experiment => 'nd_geolocation' } } ],
 156
 157             '+select' => [ 'type.name' , 'organism.species' ],
 158             '+as'     => [ 'cvterm_name' , 'species' ],
 159             page      => $page,
 160             rows      => $rows,
 161             order_by  => 'me.name',
 162             distinct  => 1,
 163         }
 164         );
 165
 166
 167     my @result;
 168     while (my $a        = $rs2->next()) {
 169         my $uniquename  = $a->uniquename;
 170         my $type_id     = $a->type_id ;
 171         my $type        = $a->get_column('cvterm_name');
 172         my $organism_id = $a->organism_id;
 173         my $organism    = $a->get_column('species');
 174         my $stock_id    = $a->stock_id;
 175         push @result, [  "<a href=\"/stock/$stock_id/view\">$uniquename</a>", $type, $organism ];
 176     }
 177
 178     $c->stash->{rest} = { data => [ @result ], draw => $draw, recordsTotal => $records_total,  recordsFiltered => $records_total };
 179
 180
 181 }
 182
 183 1;