tripal_pub.pub_search.inc 24 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657
  1. <?php
  2. /**
  3. * @file
  4. * Functions related to searching remote publication databases
  5. */
  6. /**
  7. * @stephen
  8. *
  9. * @ingroup tripal_pub
  10. */
  11. function tripal_pub_search_page() {
  12. $limit = 25;
  13. // generate the search form
  14. $form = drupal_get_form('tripal_pub_search_form');
  15. $output = drupal_render($form);
  16. // retrieve any results
  17. if (array_key_exists('tripal_pub_search_form', $_SESSION) and
  18. $_SESSION['tripal_pub_search_form']['perform_search']) {
  19. $num_criteria = $_SESSION['tripal_pub_search_form']['num_criteria'];
  20. $from_year = $_SESSION['tripal_pub_search_form']['from_year'];
  21. $to_year = $_SESSION['tripal_pub_search_form']['to_year'];
  22. $search_array = array();
  23. $search_array['num_criteria'] = $num_criteria;
  24. $search_array['from_year'] = $from_year;
  25. $search_array['to_year'] = $to_year;
  26. for ($i = 0; $i <= $num_criteria; $i++) {
  27. $search_array['criteria'][$i]['search_terms'] = $_SESSION['tripal_pub_search_form']['criteria'][$i]['search_terms'];
  28. $search_array['criteria'][$i]['scope'] = $_SESSION['tripal_pub_search_form']['criteria'][$i]['scope'];
  29. $search_array['criteria'][$i]['mode'] = $_SESSION['tripal_pub_search_form']['criteria'][$i]['mode'];
  30. $search_array['criteria'][$i]['operation'] = $_SESSION['tripal_pub_search_form']['criteria'][$i]['operation'];
  31. }
  32. // get the list of publications from the remote database using the search criteria.
  33. $pubs = tripal_pub_get_search_results($search_array, $limit);
  34. $page = isset($_GET['page']) ? $_GET['page'] : '0';
  35. $total_records = $_SESSION['chado_pager'][0]['total_records'];
  36. $total_pages = (int) ($total_records / $limit) + 1;
  37. // iterate through the results and construct the table displaying the publications
  38. $rows = array();
  39. $i = $page * $limit + 1;
  40. foreach ($pubs as $pub) {
  41. // get the citation for this publication
  42. $values = array(
  43. 'pub_id' => $pub->pub_id,
  44. 'type_id' => array(
  45. 'name' => 'Citation',
  46. ),
  47. );
  48. $citation_rec = chado_generate_var('pubprop', $values);
  49. $citation_rec = chado_expand_var($citation_rec, 'field', 'pubprop.value');
  50. // if we have the citation then use it, otherwise, just use the title
  51. $title = htmlspecialchars($pub->title);
  52. $result = $title;
  53. if ($pub->nid) {
  54. $result = l($title ,'node/' . $pub->nid, array('attributes' => array('target' => '_blank')));
  55. }
  56. if ($citation_rec->value) {
  57. $citation = htmlspecialchars($citation_rec->value);
  58. $result .= '<br>' . $citation;
  59. }
  60. $rows[] = array(
  61. number_format($i) . ".",
  62. $pub->pyear,
  63. $result
  64. );
  65. $i++;
  66. }
  67. if (count($rows) == 0) {
  68. $rows[] = array(
  69. array(
  70. 'data' => 'No results found',
  71. 'colspan' => 3
  72. )
  73. );
  74. }
  75. $headers = array('', 'Year', 'Publication');
  76. $table = array(
  77. 'header' => $headers,
  78. 'rows' => $rows,
  79. 'attributes' => array('id' => 'tripal-pub-search-results-table', 'border' => '0'),
  80. 'sticky' => TRUE,
  81. 'caption' => '',
  82. 'colgroups' => array(),
  83. 'empty' => '',
  84. );
  85. $results = theme_table($table);
  86. // generate the pager
  87. pager_default_initialize($total_records, $limit);
  88. $pager = array(
  89. 'tags' => array(),
  90. 'element' => 0,
  91. 'parameters' => array(),
  92. 'quantity' => $limit,
  93. );
  94. $pager = theme_pager($pager);
  95. // join all to form the results
  96. $output .= "<br><p><b>Found " . number_format($total_records) .
  97. ". Page " . ($page + 1) . " of $total_pages. " .
  98. " Results</b></br>" . $results . $pager;
  99. }
  100. return $output;
  101. }
  102. /**
  103. * Provides the form to search pubmed
  104. *
  105. * @ingroup tripal_pub
  106. */
  107. function tripal_pub_search_form($form, &$form_state) {
  108. // Default values can come in the following ways:
  109. //
  110. // 1) as elements of the $pub_importer object. This occurs when editing an existing importer
  111. // 2) in the $form_state['values'] array which occurs on a failed validation or
  112. // ajax callbacks from non submit form elements
  113. // 3) in the $form_state['input'] array which occurs on ajax callbacks from submit
  114. // form elements and the form is being rebuilt
  115. //
  116. // Set the default values. If the pub_import_id isn't already defined by the form values
  117. // and one is provided then look it up in the database
  118. $criteria = NULL;
  119. $num_criteria = 2;
  120. $from_year = '';
  121. $to_year = '';
  122. // if the session has variables then use those. This should only happen when
  123. // the 'Test Criteria' button is clicked.
  124. if (array_key_exists('tripal_pub_search_form', $_SESSION)) {
  125. $num_criteria = $_SESSION['tripal_pub_search_form']['num_criteria'] ? $_SESSION['tripal_pub_search_form']['num_criteria'] : $num_criteria;
  126. $from_year = $_SESSION['tripal_pub_search_form']['from_year'] ? $_SESSION['tripal_pub_search_form']['from_year'] : '';
  127. $to_year = $_SESSION['tripal_pub_search_form']['to_year'] ? $_SESSION['tripal_pub_search_form']['to_year'] : '';
  128. }
  129. if (array_key_exists('values', $form_state)) {
  130. $num_criteria = $form_state['values']['num_criteria'] ? $form_state['values']['num_criteria'] : $num_criteria;
  131. $from_year = $form_state['values']['from_year'] ? $form_state['values']['from_year'] : $from_year;
  132. $to_year = $form_state['values']['to_year'] ? $form_state['values']['to_year'] : $to_year;
  133. }
  134. if (array_key_exists('input', $form_state) and !empty($form_state['input'])) {
  135. $num_criteria = $form_state['input']['num_criteria'] ? $form_state['input']['num_criteria'] : $num_criteria;
  136. $from_year = $form_state['input']['from_year'] ? $form_state['input']['from_year'] : $from_year;
  137. $to_year = $form_state['input']['to_year'] ? $form_state['input']['to_year'] : $to_year;
  138. }
  139. if (array_key_exists('triggering_element', $form_state) and
  140. $form_state['triggering_element']['#name'] == 'add') {
  141. $num_criteria++;
  142. }
  143. if (array_key_exists('triggering_element', $form_state) and
  144. $form_state['triggering_element']['#name'] == 'remove') {
  145. $num_criteria--;
  146. }
  147. $form['num_criteria']= array(
  148. '#type' => 'hidden',
  149. '#default_value' => $num_criteria,
  150. );
  151. $form['admin-instructions'] = array(
  152. '#markup' => theme('tripal_admin_message', array('message' =>
  153. t('Administrators, you can select the fields with which a user can use to search,
  154. by checking the desired fields on the ' .
  155. l('Publication Module Settings Page', 'admin/tripal/chado/tripal_pub/configuration'). ' in
  156. the section titled "Search Options". The selected fields will appear in the dropdowns below.'))),
  157. );
  158. $form['instructions'] = array(
  159. '#markup' => t('To search for publications enter keywords in the text boxes below.
  160. You can limit your search by selecting the field in the dropdown box. Click the
  161. add and remove buttons to add additional fields for searching. '),
  162. );
  163. // get publication properties list
  164. $properties = array();
  165. $properties[] = 'Any Field';
  166. $sql = "
  167. SELECT DISTINCT CVTS.cvterm_id, CVTS.name, CVTS.definition
  168. FROM {cvtermpath} CVTP
  169. INNER JOIN {cvterm} CVTS ON CVTP.subject_id = CVTS.cvterm_id
  170. INNER JOIN {cvterm} CVTO ON CVTP.object_id = CVTO.cvterm_id
  171. INNER JOIN {cv} ON CVTO.cv_id = CV.cv_id
  172. WHERE CV.name = 'tripal_pub' and
  173. (CVTO.name = 'Publication Details' or CVTS.name = 'Publication Type') and
  174. NOT CVTS.is_obsolete = 1
  175. ORDER BY CVTS.name ASC
  176. ";
  177. $allowed_fields = variable_get('tripal_pub_allowed_search_fields', array());
  178. $prop_types = chado_query($sql);
  179. foreach ($prop_types as $prop) {
  180. if(array_key_exists($prop->cvterm_id, $allowed_fields) and $allowed_fields[$prop->cvterm_id] > 0) {
  181. $properties[$prop->cvterm_id] = $prop->name;
  182. }
  183. }
  184. for($i = 1; $i <= $num_criteria; $i++) {
  185. $search_terms = '';
  186. $scope = '';
  187. $operation = '';
  188. $mode = '';
  189. // first populate defaults using any values in the SESSION variable
  190. if (array_key_exists('tripal_pub_search_form', $_SESSION)) {
  191. $search_terms = $_SESSION['tripal_pub_search_form']['criteria'][$i]['search_terms'] ? $_SESSION['tripal_pub_search_form']['criteria'][$i]['search_terms'] : $search_terms;
  192. $scope = $_SESSION['tripal_pub_search_form']['criteria'][$i]['scope'] ? $_SESSION['tripal_pub_search_form']['criteria'][$i]['scope'] : $scope;
  193. $mode = $_SESSION['tripal_pub_search_form']['criteria'][$i]['mode'] ? $_SESSION['tripal_pub_search_form']['criteria'][$i]['mode'] : $mode;
  194. $operation = $_SESSION['tripal_pub_search_form']['criteria'][$i]['operation'] ? $_SESSION['tripal_pub_search_form']['criteria'][$i]['operation'] : $operation;
  195. }
  196. if (array_key_exists('values', $form_state)) {
  197. $search_terms = array_key_exists("search_terms-$i", $form_state['values']) ? $form_state['values']["search_terms-$i"] : $search_terms;
  198. $scope = array_key_exists("scope-$i", $form_state['values']) ? $form_state['values']["scope-$i"] : $scope;
  199. $mode = array_key_exists("mode-$i", $form_state['values']) ? $form_state['values']["mode-$i"] : $mode;
  200. $operation = array_key_exists("operation-$i", $form_state['values']) ? $form_state['values']["operation-$i"] : $operation;
  201. }
  202. if (array_key_exists('input', $form_state)) {
  203. $search_terms = array_key_exists("search_terms-$i", $form_state['input']) ? $form_state['input']["search_terms-$i"] : $search_terms;
  204. $scope = array_key_exists("scope-$i", $form_state['input']) ? $form_state['input']["scope-$i"] : $scope;
  205. $mode = array_key_exists("mode-$i", $form_state['input']) ? $form_state['input']["mode-$i"] : $mode;
  206. $operation = array_key_exists("operation-$i", $form_state['input']) ? $form_state['input']["operation-$i"] : $operation;
  207. }
  208. // default to searching the title and abstract
  209. if (!$scope) {
  210. $scope = 'abstract';
  211. }
  212. $form['criteria'][$i]["search_terms-$i"] = array(
  213. '#type' => 'textfield',
  214. '#default_value' => $search_terms,
  215. '#required' => FALSE,
  216. );
  217. $form['criteria'][$i]["scope-$i"] = array(
  218. '#type' => 'select',
  219. '#options' => $properties,
  220. '#default_value' => $scope,
  221. '#attributes' => array('class' => array('tripal-pub-search-form-scope-select')),
  222. );
  223. /*
  224. $form['criteria'][$i]["mode-$i"] = array(
  225. '#type' => 'select',
  226. '#options' => array(
  227. 'Contains' => 'Contains',
  228. 'Starts With' => 'Starts With',
  229. 'Ends With' => 'Ends With',
  230. 'Exactly' => 'Exactly'),
  231. '#default_value' => $mode,
  232. );*/
  233. if ($i > 1) {
  234. $form['criteria'][$i]["operation-$i"] = array(
  235. '#type' => 'select',
  236. '#options' => array(
  237. 'AND' => 'AND',
  238. 'OR' => 'OR',
  239. 'NOT' => 'NOT'),
  240. '#default_value' => $operation,
  241. );
  242. }
  243. if ($i == $num_criteria) {
  244. if($i > 1) {
  245. $form['criteria'][$i]["remove-$i"] = array(
  246. '#type' => 'button',
  247. '#name' => 'remove',
  248. '#value' => t('Remove'),
  249. '#ajax' => array(
  250. 'callback' => "tripal_pubs_search_form_ajax_update",
  251. 'wrapper' => 'tripal-pub-search-form-criteria',
  252. 'effect' => 'fade',
  253. 'method' => 'replace',
  254. 'prevent' => 'click'
  255. ),
  256. // When this button is clicked, the form will be validated and submitted.
  257. // Therefore, we set custom submit and validate functions to override the
  258. // default form submit. In the validate function we set the form_state
  259. // to rebuild the form so the submit function never actually gets called,
  260. // but we need it or Drupal will run the default validate anyway.
  261. // we also set #limit_validation_errors to empty so fields that
  262. // are required that don't have values won't generate warnings.
  263. '#submit' => array('tripal_pub_search_form_ajax_button_submit'),
  264. '#validate' => array('tripal_pub_search_form_ajax_button_validate'),
  265. '#limit_validation_errors' => array(),
  266. );
  267. }
  268. $form['criteria'][$i]["add-$i"] = array(
  269. '#type' => 'button',
  270. '#name' => 'add',
  271. '#value' => t('Add'),
  272. '#ajax' => array(
  273. 'callback' => "tripal_pubs_search_form_ajax_update",
  274. 'wrapper' => 'tripal-pub-search-form-criteria',
  275. 'effect' => 'fade',
  276. 'method' => 'replace',
  277. 'prevent' => 'click'
  278. ),
  279. // When this button is clicked, the form will be validated and submitted.
  280. // Therefore, we set custom submit and validate functions to override the
  281. // default form submit. In the validate function we set the form_state
  282. // to rebuild the form so the submit function never actually gets called,
  283. // but we need it or Drupal will run the default validate anyway.
  284. // we also set #limit_validation_errors to empty so fields that
  285. // are required that don't have values won't generate warnings.
  286. '#submit' => array('tripal_pub_search_form_ajax_button_submit'),
  287. '#validate' => array('tripal_pub_search_form_ajax_button_validate'),
  288. '#limit_validation_errors' => array(),
  289. );
  290. }
  291. }
  292. $form['criteria']["date"] = array(
  293. '#type' => 'select',
  294. '#options' => array('Years' => 'Years'),
  295. '#attributes' => array('class' => array('tripal-pub-search-form-scope-select')),
  296. );
  297. $form['criteria']["from_year"] = array(
  298. '#type' => 'textfield',
  299. '#default_value' => $from_year,
  300. '#required' => FALSE,
  301. '#title' => 'from',
  302. '#size' => 4,
  303. '#maxlength' => 4,
  304. );
  305. $form['criteria']["to_year"] = array(
  306. '#type' => 'textfield',
  307. '#default_value' => $to_year,
  308. '#required' => FALSE,
  309. '#title' => 'to',
  310. '#size' => 4,
  311. '#maxlength' => 4,
  312. );
  313. $form['search'] = array(
  314. '#type' => 'submit',
  315. '#value' => t('Search'),
  316. );
  317. $form['reset'] = array(
  318. '#type' => 'submit',
  319. '#value' => t('Reset'),
  320. );
  321. $form['criteria']['#theme'] = 'tripal_pub_search_setup_form_elements';
  322. return $form;
  323. }
  324. /**
  325. * This function is used to rebuild the form if an ajax call is made vai a button.
  326. * The button causes the form to be submitted. We don't want this so we override
  327. * the validate and submit routines on the form button. Therefore, this function
  328. * only needs to tell Drupal to rebuild the form
  329. *
  330. * @ingroup tripal_pub
  331. */
  332. function tripal_pub_search_form_ajax_button_submit() {
  333. $form_state['rebuild'] = TRUE;
  334. }
  335. /**
  336. * This function is just a dummy to override the default form submit on ajax calls for buttons
  337. *
  338. * @ingroup tripal_pub
  339. */
  340. function tripal_pub_search_form_ajax_button_validate() {
  341. // do nothing
  342. }
  343. /**
  344. * Validate the tripal_pub_search_form form
  345. *
  346. * @ingroup tripal_pub
  347. */
  348. function tripal_pub_search_form_validate($form, &$form_state) {
  349. $num_criteria = $form_state['values']['num_criteria'];
  350. $from_year = $form_state['values']['from_year'];
  351. $to_year = $form_state['values']['to_year'];
  352. $op = $form_state['values']['op'];
  353. // no need to vlaidate on a reset
  354. if ($op == 'Reset') {
  355. return;
  356. }
  357. if($from_year and !$to_year) {
  358. form_set_error('to_year', 'Please provide a 4-digit year.');
  359. }
  360. if(!$from_year and $to_year) {
  361. form_set_error('from_year', 'Please provide a 4-digit year.');
  362. }
  363. if($from_year and !preg_match('/\d\d\d\d/' , $from_year)) {
  364. form_set_error('from_year', 'Please provide a 4-digit year.');
  365. }
  366. if($to_year and !preg_match('/\d\d\d\d/' , $to_year)) {
  367. form_set_error('to_year', 'Please provide a 4-digit year.');
  368. }
  369. }
  370. /**
  371. * Submit the tripal_pub_search_form form
  372. *
  373. * @ingroup tripal_pub
  374. */
  375. function tripal_pub_search_form_submit($form, &$form_state) {
  376. $num_criteria = $form_state['values']['num_criteria'];
  377. $from_year = $form_state['values']['from_year'];
  378. $to_year = $form_state['values']['to_year'];
  379. $op = $form_state['values']['op'];
  380. // set the session variables
  381. if($op == 'Search') {
  382. $_SESSION['tripal_pub_search_form']['num_criteria'] = $num_criteria;
  383. unset($_SESSION['tripal_pub_search_form']['criteria']);
  384. for ($i = 0; $i <= $num_criteria; $i++) {
  385. $search_terms = '';
  386. $scope = '';
  387. $mode = 'Contains';
  388. $operation = '';
  389. if (array_key_exists("search_terms-$i", $form_state['values'])) {
  390. $search_terms = trim($form_state['values']["search_terms-$i"]);
  391. }
  392. if (array_key_exists("scope-$i", $form_state['values'])) {
  393. $scope = $form_state['values']["scope-$i"];
  394. }
  395. if (array_key_exists("operation-$i", $form_state['values'])) {
  396. $operation = $form_state['values']["operation-$i"];
  397. }
  398. //$mode = $form_state['values']["mode-$i"];
  399. $_SESSION['tripal_pub_search_form']['criteria'][$i] = array(
  400. 'search_terms' => $search_terms,
  401. 'scope' => $scope,
  402. 'mode' => $mode,
  403. 'operation' => $operation
  404. );
  405. }
  406. $_SESSION['tripal_pub_search_form']['from_year'] = $from_year;
  407. $_SESSION['tripal_pub_search_form']['to_year'] = $to_year;
  408. $_SESSION['tripal_pub_search_form']['perform_search'] = 1;
  409. }
  410. if($op == 'Reset') {
  411. unset($_SESSION['tripal_pub_search_form']);
  412. }
  413. }
  414. /**
  415. * AHAH callback
  416. *
  417. * @ingroup tripal_pub
  418. */
  419. function tripal_pub_search_page_update_criteria($action, $i) {
  420. $status = TRUE;
  421. // prepare and render the form
  422. $form = tripal_core_ahah_prepare_form();
  423. $data = theme('tripal_pub_search_form', $form);
  424. // bind javascript events to the new objects that will be returned
  425. // so that AHAH enabled elements will work.
  426. $settings = tripal_core_ahah_bind_events();
  427. // return the updated JSON
  428. drupal_json(
  429. array(
  430. 'status' => $status,
  431. 'data' => $data,
  432. 'settings' => $settings,
  433. )
  434. );
  435. }
  436. /**
  437. * @stephen
  438. *
  439. * @ingroup tripal_pub
  440. */
  441. function tripal_pub_get_search_results($search_array, $limit) {
  442. // build the SQL based on the criteria provided by the user
  443. $select = "SELECT DISTINCT P.*, CP.nid ";
  444. $from = "FROM {pub} P
  445. LEFT JOIN public.chado_pub CP on P.pub_id = CP.pub_id
  446. INNER JOIN {cvterm} CVT on CVT.cvterm_id = P.type_id
  447. ";
  448. $where = "WHERE (NOT P.title = 'null') "; // always exclude the dummy pub
  449. $order = "ORDER BY P.pyear DESC, P.title ASC";
  450. $args = array(); // arguments for where clause
  451. $join = 0;
  452. $num_criteria = $search_array['num_criteria'];
  453. $from_year = $search_array['from_year'];
  454. $to_year = $search_array['to_year'];
  455. for ($i = 1; $i <= $num_criteria; $i++) {
  456. $value = $search_array['criteria'][$i]['search_terms'];
  457. $type_id = $search_array['criteria'][$i]['scope'];
  458. $mode = $search_array['criteria'][$i]['mode'];
  459. $op = $search_array['criteria'][$i]['operation'];
  460. // skip criteria with no values
  461. if(!$value) {
  462. continue;
  463. }
  464. // to prevent SQL injection make sure our operator is
  465. // what we expect
  466. if ($op and $op != "AND" and $op != "OR" and $op != 'NOT') {
  467. $op = 'AND';
  468. }
  469. if ($op == 'NOT') {
  470. $op = 'AND NOT';
  471. }
  472. if (!$op) {
  473. $op = 'AND';
  474. }
  475. // get the scope type
  476. $values = array('cvterm_id' => $type_id);
  477. $cvterm = chado_select_record('cvterm', array('name'), $values);
  478. $type_name = '';
  479. if (count($cvterm) > 0) {
  480. $type_name = $cvterm[0]->name;
  481. }
  482. if ($type_name == 'Title') {
  483. $where .= " $op (lower(P.title) LIKE lower(:crit$i)) ";
  484. $args[":crit$i"] = '%' . $value . '%';
  485. }
  486. elseif ($type_name == 'Year') {
  487. $where .= " $op (lower(P.pyear) = lower(:crit$i)) ";
  488. $args[":crit$i"] = '%' . $value . '%';
  489. }
  490. elseif ($type_name == 'Volume') {
  491. $where .= " $op (lower(P.volume) = lower(:crit$i)) ";
  492. $args[":crit$i"] = '%' . $value . '%';
  493. }
  494. elseif ($type_name == 'Issue') {
  495. $where .= " $op (lower(P.issue) = lower(:crit$i)) ";
  496. $args[":crit$i"] = '%' . $value . '%';
  497. }
  498. elseif ($type_name == 'Journal Name') {
  499. $from .= " LEFT JOIN {pubprop} PP$i ON PP$i.pub_id = P.pub_id AND PP$i.type_id = :crit$i ";
  500. $where .= " $op ((lower(P.series_name) = lower(:crit$i) and CVT.name = 'Journal Article') OR
  501. (lower(PP$i.value) = lower(:crit$i))) ";
  502. $args[":crit$i"] = $type_id;
  503. }
  504. elseif ($type_name == 'Conference Name') {
  505. $from .= " LEFT JOIN {pubprop} PP$i ON PP$i.pub_id = P.pub_id AND PP$i.type_id = :crit$i ";
  506. $where .= " $op ((lower(P.series_name) = lower(:crit$i) and CVT.name = 'Conference Proceedings') OR
  507. (lower(PP$i.value) = lower(:crit$i))) ";
  508. $args[":crit$i"] = $type_id;
  509. }
  510. elseif ($type_name == 'Publication Type') {
  511. $where .= " $op (lower(CVT.name) = lower(:crit$i))";
  512. $args[":crit$i"] = $value;
  513. }
  514. elseif ($type_id == 0) { //'Any Field'
  515. $from .= " LEFT JOIN {pubprop} PP$i ON PP$i.pub_id = P.pub_id ";
  516. $where .= " $op (lower(PP$i.value) LIKE lower(:crit$i) OR
  517. lower(P.title) LIKE lower(:crit$i) OR
  518. lower(P.volumetitle) LIKE lower(:crit$i) OR
  519. lower(P.publisher) LIKE lower(:crit$i) OR
  520. lower(P.uniquename) LIKE lower(:crit$i) OR
  521. lower(P.pubplace) LIKE lower(:crit$i) OR
  522. lower(P.miniref) LIKE lower(:crit$i) OR
  523. lower(P.series_name) LIKE lower(:crit$i)) ";
  524. $args[":crit$i"] = '%' . $value . '%';
  525. }
  526. // for all other properties
  527. else {
  528. $from .= " LEFT JOIN {pubprop} PP$i ON PP$i.pub_id = P.pub_id AND PP$i.type_id = :type_id$i ";
  529. $where .= " $op (lower(PP$i.value) LIKE lower(:crit$i)) ";
  530. $args[":crit$i"] = '%' . $value . '%';
  531. $args[":type_id$i"] = $type_id;
  532. }
  533. }
  534. if($from_year and $to_year) {
  535. $where .= " AND (P.pyear ~ '....' AND to_number(P.pyear,'9999') >= :from$i AND to_number(P.pyear,'9999') <= :to$i) ";
  536. $args[":from$i"] = $from_year;
  537. $args[":to$i"] = $to_year;
  538. }
  539. $sql = "$select $from $where $order";
  540. $count = "SELECT count(*) FROM ($select $from $where $order) as t1";
  541. return chado_pager_query($sql, $args, $limit, 0, $count);
  542. }
  543. /**
  544. * Ajax callback to update the form
  545. *
  546. * @ingroup tripal_pub
  547. */
  548. function tripal_pubs_search_form_ajax_update($form, $form_state) {
  549. return $form['criteria'];
  550. }
  551. /**
  552. * Theme the tripal_pub_search_setup_form form
  553. *
  554. * @ingroup tripal_pub
  555. */
  556. function theme_tripal_pub_search_setup_form_elements($variables) {
  557. $form = $variables['form'];
  558. $rows = array();
  559. // put each criteria element in a single table row
  560. foreach ($form as $i => $element) {
  561. if(is_numeric($i)) {
  562. $rows[] = array(
  563. drupal_render($element["operation-$i"]),
  564. drupal_render($element["scope-$i"]),
  565. //drupal_render($element["mode-$i"]) .
  566. drupal_render($element["search_terms-$i"]),
  567. array(
  568. 'data' => drupal_render($element["add-$i"]) . drupal_render($element["remove-$i"]),
  569. 'nowrap' => 'nowrap',
  570. ),
  571. );
  572. }
  573. }
  574. // add in the from_year and to_year elements as the final row of the table
  575. $rows[] = array(
  576. '&nbsp;',
  577. drupal_render($form['date']),
  578. array(
  579. 'data' =>
  580. "<div id=\"pub-search-form-dates-row\">
  581. <div id=\"pub-search-form-dates\"> ".
  582. drupal_render($form['from_year']) .
  583. drupal_render($form['to_year']) . "
  584. </div>
  585. </div>
  586. ",
  587. ),
  588. ''
  589. );
  590. $headers = array();
  591. $table = array(
  592. 'header' => $headers,
  593. 'rows' => $rows,
  594. 'attributes' => array('id' => 'tripal-pub-search-form-table', 'border' => '0'),
  595. 'sticky' => TRUE,
  596. 'caption' => '',
  597. 'colgroups' => array(),
  598. 'empty' => '',
  599. );
  600. $results = '<div id="tripal-pub-search-form-criteria">';
  601. $results .= theme_table($table);
  602. $results .= '</div>';
  603. return $results;
  604. }