You are here

function biblio_build_query in Bibliography Module 6

Same name and namespace in other branches
  1. 6.2 includes/biblio.pages.inc \biblio_build_query()
  2. 7 includes/biblio.pages.inc \biblio_build_query()
  3. 7.2 includes/biblio.pages.inc \biblio_build_query()
1 call to biblio_build_query()
biblio_db_search in ./biblio.pages.inc

File

./biblio.pages.inc, line 124

Code

function biblio_build_query($arg_list) {
  global $user, $db_type;
  static $bcc;

  //biblio_contributor (bc) count , increase for every invocation
  static $tcc;

  //term counter, increase for every invocation
  if (!isset($bcc)) {
    $bcc = 0;
  }
  if (!isset($tcc)) {
    $tcc = 0;
  }
  $inline = $rss_info['feed'] = false;
  $joins = array();
  $selects = array();
  $count_selects = array();
  $count_limit = '';
  $selects[] = "DISTINCT(n.nid)";
  $count_selects[] = "DISTINCT(n.nid)";

  //$selects[] = "n.*";

  //$selects[] = "b.*";
  $selects[] = "bt.name as biblio_type_name";
  $joins[] = "left join {biblio} b  on n.vid=b.vid ";
  $joins[] = "inner join {biblio_types} bt  on b.biblio_type=bt.tid ";

  // POSIX regular expression matching, case insensitive
  $match_op = $db_type == 'pgsql' ? '~*' : 'RLIKE';

  // The following is to be used to compare titles
  // LOWER() is required for case insensitive sorting (at least, in PostgreSQL)
  $sort_title = " CASE\n                    WHEN SUBSTR(n.title,1,1)='\"'   THEN LOWER(SUBSTR(n.title,2))\n                    WHEN SUBSTR(n.title,1,1)='\\''   THEN LOWER(SUBSTR(n.title,2))\n                    WHEN SUBSTR(n.title,1,2)='A '   THEN LOWER(SUBSTR(n.title,3))\n                    WHEN SUBSTR(n.title,1,3)='An '  THEN LOWER(SUBSTR(n.title,4))\n                    WHEN SUBSTR(n.title,1,4)='The ' THEN LOWER(SUBSTR(n.title,5))\n                    ELSE LOWER(n.title)\n                 END ";
  $limit = '';
  if (variable_get('biblio_view_only_own', 0)) {
    $limit .= " AND n.uid = {$user->uid} ";
  }
  if (array_search('sort', $arg_list) === FALSE) {
    $arg_list[] = 'sort';
    $arg_list[] = variable_get('biblio_sort', 'year');
    $arg_list[] = 'order';
    $arg_list[] = strtolower(variable_get('biblio_order', 'desc'));
  }
  $limits = null;
  if (!isset($_SESSION['biblio_filter']) || !is_array($_SESSION['biblio_filter'])) {
    $_SESSION['biblio_filter'] = array();
  }
  $session =& $_SESSION['biblio_filter'];
  if (!in_array('no_filters', $arg_list)) {
    foreach ($session as $filter) {
      $arg_list = array_merge($arg_list, $filter);
    }
  }
  if (count($arg_list)) {
    $args = array();
    while ($arg_list) {
      $type = $arg_list[0];
      array_shift($arg_list);
      $operator = $operator ? $operator : " AND ";

      //defaults to AND
      switch ($type) {
        case 'no_filters':
          break;
        case 'and':
          $operator = " AND ";
          break;
        case 'or':
          $operator = " OR ";
          break;
        case 'inline':
          $inline = true;
          break;
        case 'rss.xml':
          $rss_info['feed'] = true;
          $count_limit = 'LIMIT ' . variable_get('biblio_rss_number_of_entries', 10);
          break;
        case 'profile':
          $inline = "profile";
          break;
        case 'cid':
        case 'aid':
          $bcc++;
          $term = explode("?", array_shift($arg_list));
          $joins[] = "inner join {biblio_contributor} as bc" . $bcc . " on n.vid = bc" . $bcc . ".vid";
          $where[] = "bc" . $bcc . ".cid = '%d' ";
          $terms[] = db_escape_string($term[0]);
          array_push($args, $type, $term[0]);
          break;
        case 'term':
        case 'term_id':
          $term = explode("?", array_shift($arg_list));
          $joins[] = "inner join {term_node} as tn" . $tcc . " on n.vid = tn" . $tcc . ".vid";
          if ($type == 'term') {
            $joins[] = "inner join  {term_data} as td on tn" . $tcc . ".tid= td.tid";
            $where[] = "td.name = '%s' ";
          }
          elseif ($type == 'term_id') {
            $where[] = "tn" . $tcc . ".tid = '%d' ";
          }
          $terms[] = db_escape_string($term[0]);
          array_push($args, $type, $term[0]);
          $tcc++;
          break;
        case 'tg':
          $term = explode("?", array_shift($arg_list));
          $where[] = "substring({$sort_title},1 ,1)" . $match_op . " LOWER('%s')";
          $terms[] = db_escape_string($term[0]);
          array_push($args, $type, $term[0]);
          $operator = NULL;
          break;
        case 'ag':

          //selects entries whoose authors firstname starts with the letter provided
          $term = explode("?", array_shift($arg_list));
          $where[] = " UPPER(substring(bcd.lastname,1,1)) = '%s' ";

          //$where['bc-rank'] = "bc.rank=0";
          $joins['bc'] = '  INNER JOIN {biblio_contributor} as bc on b.vid = bc.vid ';
          $joins['bcd'] = '  JOIN {biblio_contributor_data} as bcd on bc.cid = bcd.cid ';
          $terms[] = db_escape_string(strtoupper($term[0]));
          array_push($args, $type, $term[0]);
          $operator = NULL;
          break;
        case 'author':
          $bcc++;
          $term = explode("?", array_shift($arg_list));
          if (is_numeric($term[0])) {
            $joins[] = "inner join {biblio_contributor} as bc" . $bcc . " on n.vid = bc" . $bcc . ".vid";
            $cids = db_query('SELECT cid FROM {biblio_contributor_data}
                              WHERE cid = %d OR aka = (SELECT aka FROM {biblio_contributor_data} WHERE cid = %d)', $term[0], $term[0]);
            while ($cid = db_fetch_object($cids)) {
              $wr .= empty($wr) ? '' : ' OR ';
              $wr .= "bc" . $bcc . ".cid = {$cid->cid} ";
            }
            $where[] = !empty($wr) ? $wr : "bc" . $bcc . ".cid = -1 ";
          }
          else {
            $where[] = " bcd" . $bcc . '.name ' . $match_op . ' "[[:<:]]%s[[:>:]]" ';
            $joins[] = " JOIN {biblio_contributor} as bc" . $bcc . " on b.vid = bc" . $bcc . ".vid ";
            $joins[] = " JOIN {biblio_contributor_data} as bcd" . $bcc . " on bc" . $bcc . ".cid = bcd" . $bcc . ".cid ";
            $terms[] = db_escape_string($term[0]);
            $operator = NULL;
            $rss_info['title'] = t("Publications by " . $term[0]);
            $rss_info['description'] = t("These publications by %author are part of the works listed at %sitename", array(
              '%author' => $term[0],
              '%sitename' => variable_get('site_name', 'Drupal'),
            ));
            $rss_info['link'] = '/author/' . $term[0];
          }
          array_push($args, $type, $term[0]);
          break;
        case 'publisher':
          $term = explode("?", array_shift($arg_list));
          $where[] = "b.biblio_publisher " . $match_op . " '%s' ";
          $terms[] = db_escape_string($term[0]);
          array_push($args, $type, $term[0]);
          $operator = NULL;
          break;
        case 'year':
          $term = db_escape_string(array_shift($arg_list));
          $where[] = "b.biblio_year=%d ";

          //$limit .= " AND b.biblio_year=%d ";
          $terms[] = (int) $term;
          array_push($args, $type, (int) $term);
          $operator = NULL;
          break;
        case 'uid':
          $term = db_escape_string(array_shift($arg_list));
          $where[] = "n.uid=%d ";

          //$limit .= " AND b.biblio_year=%d ";
          $terms[] = (int) $term;
          array_push($args, $type, (int) $term);
          $operator = NULL;

          //            $rss_info['title'] = t("Publications by " . $term[0]);
          //            $rss_info['description'] = t("These publications by %author are part of the works listed at %sitename", array('%author' => $term[0], '%sitename' => variable_get('site_name', 'Drupal')));
          //            $rss_info['link'] = '/author/' . $term[0];
          break;
        case 'keyword':
          $bkd++;
          $term = explode("?", array_shift($arg_list));
          if (is_numeric($term[0])) {
            $terms[] = db_escape_string($term[0]);
            $joins[] = "inner join {biblio_keyword} as bk{$bkd} on n.vid = bk{$bkd}.vid";

            //$joins[] = "inner join {biblio_keyword_data} as bkd on bk.kid= bkd.kid";
            $where[] = "bk{$bkd}.kid = %d ";
          }
          elseif (strlen($term[0]) == 1) {
            $joins['bk'] = '  JOIN {biblio_keyword} as bk on b.vid = bk.vid ';
            $joins['bkd'] = '  LEFT JOIN {biblio_keyword_data} as bkd on bk.kid = bkd.kid ';
            $selects[] = "bkd.word as biblio_keyword";
            $where[] = " UPPER(substring(bkd.word,1,1)) = '%s' ";
            $terms[] = db_escape_string(strtoupper($term[0]));

            //array_push($args, $type, $term[0]);
          }
          else {
            $where[] = " bkd" . $bkd . '.word ' . $match_op . ' "[[:<:]]%s[[:>:]]" ';
            $joins[] = " JOIN {biblio_keyword} as bk" . $bkd . " on b.vid = bk" . $bkd . ".vid ";
            $joins[] = " JOIN {biblio_keyword_data} as bkd" . $bkd . " on bk" . $bkd . ".kid = bkd" . $bkd . ".kid ";
            $terms[] = db_escape_string($term[0]);
            $operator = NULL;
            $rss_info['title'] = t("Keyword " . $term[0]);
            $rss_info['description'] = t("These publications, containing the keyword: %keyword, are part of the works listed at %sitename", array(
              '%keyword' => $term[0],
              '%sitename' => variable_get('site_name', 'Drupal'),
            ));
            $rss_info['link'] = '/keyword/' . $term[0];
          }
          array_push($args, $type, $term[0]);
          $operator = NULL;
          break;
        case 'citekey':
          $term = explode("?", array_shift($arg_list));
          $terms[] = db_escape_string($term[0]);
          $where[] = "b.biblio_citekey= '%s' ";
          array_push($args, $type, $term[0]);
          $operator = NULL;
          break;
        case 'type':
          $term = db_escape_string(array_shift($arg_list));
          $where[] = "b.biblio_type=%d ";

          //$limit .= $operator. "b.biblio_type=%d ";
          $terms[] = (int) $term;
          array_push($args, $type, (int) $term);
          $operator = NULL;
          break;
        case 'order':
          $term = db_escape_string(strtolower(array_shift($arg_list))) == 'desc' ? 'desc' : 'asc';
          $sort_attrib['order'] = $term;
          break;
        case 'sort':
          $term = db_escape_string(array_shift($arg_list));
          $sort_attrib['sort'] = $term;
          switch ($term) {
            case 'type':
              $sortby = "ORDER BY bt.name %s, {$sort_title}";
              $selects[] = "bt.name, {$sort_title}";
              break;
            case 'title':
              $sortby = "ORDER BY {$sort_title} %s";
              $selects[] = $sort_title;
              break;
            case 'author':
              $sortby = "ORDER BY bcd.lastname %s ";
              $where['bc-rank'] = "bc.rank=0";
              $where['bc-auth-category'] = "bc.auth_category IN (1,5)";
              $joins['bc'] = '  INNER JOIN {biblio_contributor} as bc on b.vid = bc.vid ';
              $joins['bcd'] = '  JOIN {biblio_contributor_data} as bcd on bc.cid = bcd.cid ';
              $selects[] = "bcd.lastname";
              break;
            case 'keyword':

              // added msh 070808
              $sortby = "ORDER BY bkd.word %s ";
              $joins['bk'] = '  JOIN {biblio_keyword} as bk on b.vid = bk.vid ';
              $joins['bkd'] = '  LEFT JOIN {biblio_keyword_data} as bkd on bk.kid = bkd.kid ';
              $selects[] = "bkd.word as biblio_keyword";

              //$count_selects[] = "bkd.word";
              break;
            case 'year':
            default:
              $sortby = "ORDER BY b.biblio_year %s, b.biblio_date %s, {$sort_title} %s";
              $selects[] = "b.biblio_year, b.biblio_date";
              $selects[] = $sort_title;
          }

          //end switch
          break;
        case 'search':
          $term = explode("?", array_shift($arg_list));
          $result_nids = split(',', $term[0]);
          $where[] = "n.nid in (" . db_placeholders($result_nids) . ")";
          foreach ($result_nids as $result_nid) {
            $terms[] = db_escape_string($result_nid);
            array_push($args, $type, $result_nid);
          }

          // Save search keyword to show in the filter list.
          $term = array_shift($arg_list);
          array_push($args, $type, $term);
          $operator = NULL;
          break;
        default:
          $fields = biblio_get_db_fields();
          $term = explode("?", array_shift($arg_list));
          if (in_array("biblio_{$type}", $fields)) {
            $where[] = "b.biblio_{$type} " . $match_op . " '%s' ";
            $terms[] = db_escape_string($term[0]);
            array_push($args, $type, $term[0]);
            $operator = NULL;
          }
          break;
      }
    }
  }
  $where[] = "n.type='biblio' ";
  if ($user->uid != 1) {
    $where[] = 'n.status = 1 ';
  }

  //show only published entries to everyone except admin
  $select = implode(', ', $selects);
  $count_select = implode(', ', $count_selects);
  $join = implode(' ', $joins);
  $where_clause = count($where) > 1 ? '(' . implode(') AND (', $where) . ')' : $where[0];
  $query = db_rewrite_sql("SELECT {$select} FROM {node} n {$join}  WHERE {$where_clause} {$limit} {$sortby} {$count_limit}");
  $count_query = db_rewrite_sql("SELECT COUNT({$count_select}) FROM {node} n {$join}  WHERE {$where_clause} {$limit} {$count_limit}");
  $_SESSION['last_biblio_query'] = $query;
  $terms[] = $sort_attrib['order'];

  // this is either asc or desc to be inserted into the first term of the ORDER clause
  if ($sort_attrib['sort'] == 'year') {
    $terms[] = $sort_attrib['order'];

    // we need any extra order term when sorting by year since there are to date terms biblio_year and biblio_date
    $terms[] = 'asc';
  }
  $_SESSION['last_biblio_query_terms'] = $terms;
  return array(
    'query' => $query,
    'query_terms' => $terms,
    'count_query' => $count_query,
    'args' => $args,
    'sort_attrib' => $sort_attrib,
    'rss' => $rss_info,
  );
}