view release on metacpan or  search on metacpan

lib/CohortExplorer/Command/Query/Search.pm  view on Meta::CPAN

package CohortExplorer::Command::Query::Search;

use strict;
use warnings;

our $VERSION = 0.14;

use base qw(CohortExplorer::Command::Query);
use CLI::Framework::Exceptions qw( :all );

#-------
# Command is available to both standard and longitudinal datasources
sub usage_text {
 q\
     search [--out|o=<directory>] [--export|e=<table>] [--export-all|a] [--save-command|s] [--stats|S] [--cond|c=<cond>] 
            [variable] : search entities with/without conditions on variables
              
              
     NOTES
         The variables entity_id and visit (if applicable) must not be provided as arguments as they are already part of
         the query-set. However, the user can impose conditions on both variables.

         Other variables in arguments/cond (option) must be referenced as <table>.<variable>.

         The conditions can be imposed using the operators such as =, !=, >, <, >=, <=, between, not_between, like, 
         not_like, ilike, in, not_in, regexp and not_regexp. 

         The keyword undef can be used to search for null values.

         The directory specified in 'out' option must have RWX enabled (i.e. chmod 777) for CohortExplorer.


     EXAMPLES
         search --out=/home/user/exports --stats --save-command --cond=DS.Status='=, CTL, MCI' GDS.Score
                 
         search --out=/home/user/exports --stats --save-command --cond=CER.Score='<=, 30' GDS.Score

         search --out=/home/user/exports --export-all --cond=SD.Sex='=, Male' CER.Score DIS.Status

         search -o/home/user/exports -eDS -eSD -c entity_id='like, DCR%' DIS.Status

         search -o/home/user/exports -Ssa -c visit='in, 1, 3, 5' DIS.Status 

         search -o/home/user/exports -c CER.Score='between, 25, 30' DIS.Status
 \;
}

sub get_valid_variables {
 my ($self) = @_;
 my $ds     = $self->cache->get('cache')->{datasource};
 my @vars   = keys %{ $ds->variable_info };
 return $ds->type eq 'standard'
   ? [ qw/entity_id/, @vars ]
   : [ qw/entity_id visit/, @vars ];
}

sub create_query_params {
 my ( $self, $opts, @args ) = @_;
 my ( $ds, $csv ) = @{ $self->cache->get('cache') }{qw/datasource csv/};
 my $dialect = $ds->dialect;
 my $struct  = $ds->entity_structure;
 my %map     = @{ $struct->{-columns} };
 my $aliase_in_having = $dialect->aliase_in_having || undef;

 my ( @vars, %param );

 # Extract all variables from args/cond (option) except
 # entity_id and visit as they are dealt separately
 for my $v ( @args, keys %{ $opts->{cond} } ) {
  if ( !grep ( $_ eq $v, ( 'entity_id', 'visit', @vars ) ) ) {
   push @vars, $v;
  }
 }

 for (@vars) {
  ##---- BUILD 'WHERE' FOR TABLES AND VARIABLES ----##
  my ( $t, $v ) = @{ $ds->variable_info->{$_} }{qw/table variable/};
  my $table_type = $ds->table_info->{$t}{__type__};
  
  push @{ $param{$table_type}{-where}{ $map{table} }{-in} },    $t;
  push @{ $param{$table_type}{-where}{ $map{variable} }{-in} }, $v;
  
  my $col_sql = 'CAST( NULLIF( '
    . $dialect->aggregate(
        (
         (
           $table_type eq 'static' ? 'DISTINCT' : ''
         )
         . " CASE WHEN CONCAT($map{table}, '.', $map{variable} )  = '$_' THEN TRIM( $map{value} ) ELSE NULL END "
        )