File

tripal_pub/includes/pub_search.inc, line 407

Code

function tripal_pub_get_search_results($search_array, $limit, $pager_id) {

  // build the SQL based on the criteria provided by the user
  $select = "SELECT DISTINCT P.*, CP.nid ";
  $from = "FROM {pub} P
               LEFT JOIN public.chado_pub CP on P.pub_id = CP.pub_id 
               INNER JOIN {cvterm} CVT on CVT.cvterm_id = P.type_id
            ";
  $where = "WHERE (NOT P.title = 'null') "; // always exclude the dummy pub
  $order = "ORDER BY P.pyear DESC, P.title ASC";
  $fargs = array(); // arguments for from, inner join clause
  $wargs = array(); // arguments for where clause
  $join = 0;

  $num_criteria = $search_array['num_criteria'];
  $from_year = $search_array['from_year'];
  $to_year = $search_array['to_year'];

  for ($i = 1; $i <= $num_criteria; $i++) {
    $value = $search_array['criteria'][$i]['search_terms'];
    $type_id = $search_array['criteria'][$i]['scope'];
    $mode = $search_array['criteria'][$i]['mode'];
    $op = $search_array['criteria'][$i]['operation'];

    // skip criteria with no values
    if (!$value) {
      continue;
    }

    // to prevent SQL injection make sure our operator is
    // what we expect
    if ($op and $op != "AND" and $op != "OR" and $op != 'NOT') {
      $op = 'AND';
    }
    if ($op == 'NOT') {
      $op = 'AND NOT';
    }
    if (!$op) {
      $op = 'AND';
    }

    $action = "= lower('%s')";
    if ($mode == 'Contains') {
      $action = 'LIKE lower(\'%%%s%%\')';
    }
    if ($mode == 'Starts With') {
      $action = '= lower(\'%%%s\')';
    }
    if ($mode == 'Ends With') {
      $action = '= lower(\'%s%%\')';
    }

    // get the scope type
    $values = array('cvterm_id' => $type_id);
    $cvterm = tripal_core_chado_select('cvterm', array('name'), $values);
    $type_name = $cvterm[0]->name;
    if ($type_name == 'Title') {
      $where .= " $op (lower(P.title)  $action) ";
      $wargs[] = $value;
    }
    elseif ($type_name == 'Year') {
      $where .= " $op (lower(P.pyear)  $action) ";
      $wargs[] = $value;
    }
    elseif ($type_name == 'Volume') {
      $where .= " $op (lower(P.volume)  $action) ";
      $wargs[] = $value;
    }
    elseif ($type_name == 'Issue') {
      $where .= " $op (lower(P.issue)  $action)";
      $wargs[] = $value;
    }
    elseif ($type_name == 'Journal Name') {
      $from .= " LEFT JOIN {pubprop} PP$i ON PP$i.pub_id = P.pub_id AND PP$i.type_id = %d ";
      $where .= " $op ((lower(P.series_name) $action and CVT.name = 'Journal Article') OR
                       (lower(PP$i.value) $action)) ";
      $fargs[] = $type_id;
      $wargs[] = $value;
      $wargs[] = $value;
    }
    elseif ($type_name == 'Conference Name') {
      $from .= " LEFT JOIN {pubprop} PP$i ON PP$i.pub_id = P.pub_id AND PP$i.type_id = %d ";
      $where .= " $op ((lower(P.series_name) $action and CVT.name = 'Conference Proceedings') OR
                       (lower(PP$i.value) $action)) ";
      $fargs[] = $type_id;
      $wargs[] = $value;
      $wargs[] = $value;
    }
    elseif ($type_name == 'Publication Type') {
      $where .= " $op (lower(CVT.name) $action)";
      $wargs[] = $value;
    }
    elseif ($type_id == 0) { //'Any Field'
      $from .= " LEFT JOIN {pubprop} PP$i ON PP$i.pub_id = P.pub_id ";
      $where .= " $op (lower(PP$i.value)  $action OR
                       lower(P.title) $action OR 
                       lower(P.volumetitle) $action OR
                       lower(P.publisher) $action OR
                       lower(P.uniquename) $action OR
                       lower(P.pubplace) $action OR
                       lower(P.miniref) $action OR
                       lower(P.series_name) $action) ";
      $wargs[] = $value;
      $wargs[] = $value;
      $wargs[] = $value;
      $wargs[] = $value;
      $wargs[] = $value;
      $wargs[] = $value;
      $wargs[] = $value;
      $wargs[] = $value;
    }
    // for all other properties
    else {
      $from .= " LEFT JOIN {pubprop} PP$i ON PP$i.pub_id = P.pub_id AND PP$i.type_id = %d ";
      $where .= " $op (lower(PP$i.value) $action) ";
      $fargs[] = $type_id;
      $wargs[] = $value;
    }

  }
  if ($from_year and $to_year) {
    $where .= " AND (P.pyear ~ '....' AND to_number(P.pyear,'9999') >= %d AND to_number(P.pyear,'9999') <= %d) ";
    $wargs[] = $from_year;
    $wargs[] = $to_year;
  }
  $sql = "$select $from $where $order";
  $count = "SELECT count(*) FROM ($select $from $where $order) as t1";
  $args = array_merge($fargs, $wargs);
  //dpm(array($mode, $sql, $args));
  return chado_pager_query($sql, $limit, $pager_id, $count, $args);
}