CRM/Contact/Form/Search/Custom/FullText.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 4.6                                                |
   5  +--------------------------------------------------------------------+
   6  | Copyright CiviCRM LLC (c) 2004-2014                                |
   7  +--------------------------------------------------------------------+
   8  | This file is a part of CiviCRM.                                    |
   9  |                                                                    |
  10  | CiviCRM is free software; you can copy, modify, and distribute it  |
  11  | under the terms of the GNU Affero General Public License           |
  12  | Version 3, 19 November 2007 and the CiviCRM Licensing Exception.   |
  13  |                                                                    |
  14  | CiviCRM is distributed in the hope that it will be useful, but     |
  15  | WITHOUT ANY WARRANTY; without even the implied warranty of         |
  16  | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.               |
  17  | See the GNU Affero General Public License for more details.        |
  18  |                                                                    |
  19  | You should have received a copy of the GNU Affero General Public   |
  20  | License and the CiviCRM Licensing Exception along                  |
  21  | with this program; if not, contact CiviCRM LLC                     |
  22  | at info[AT]civicrm[DOT]org. If you have questions about the        |
  23  | GNU Affero General Public License or the licensing of CiviCRM,     |
  24  | see the CiviCRM license FAQ at http://civicrm.org/licensing        |
  25  +--------------------------------------------------------------------+
  26 */
  27
  28 /**
  29  *
  30  * @package CRM
  31  * @copyright CiviCRM LLC (c) 2004-2014
  32  * $Id$
  33  *
  34  */
  35 class CRM_Contact_Form_Search_Custom_FullText implements CRM_Contact_Form_Search_Interface {
  36
  37   const LIMIT = 10;
  38
  39   /**
  40    * @var array CRM_Contact_Form_Search_Custom_FullText_AbstractPartialQuery
  41    */
  42   protected $_partialQueries = NULL;
  43
  44   protected $_formValues;
  45
  46   protected $_columns;
  47
  48   protected $_text = NULL;
  49
  50   protected $_table = NULL;
  51
  52   protected $_tableName = NULL;
  53
  54   protected $_entityIDTableName = NULL;
  55
  56   protected $_tableFields = NULL;
  57
  58   /**
  59    * @var array|null NULL if no limit; or array(0 => $limit, 1 => $offset)
  60    */
  61   protected $_limitClause = NULL;
  62
  63   /**
  64    * @var array|null NULL if no limit; or array(0 => $limit, 1 => $offset)
  65    */
  66   protected $_limitRowClause = NULL;
  67
  68   /**
  69    * @var array|null NULL if no limit; or array(0 => $limit, 1 => $offset)
  70    */
  71   protected $_limitDetailClause = NULL;
  72
  73   protected $_limitNumber = 10;
  74   protected $_limitNumberPlus1 = 11; // this should be one more than self::LIMIT
  75
  76   protected $_foundRows = array();
  77
  78   /**
  79    * @param $formValues
  80    */
  81   public function __construct(&$formValues) {
  82     $this->_partialQueries = array(
  83       new CRM_Contact_Form_Search_Custom_FullText_Contact(),
  84       new CRM_Contact_Form_Search_Custom_FullText_Activity(),
  85       new CRM_Contact_Form_Search_Custom_FullText_Case(),
  86       new CRM_Contact_Form_Search_Custom_FullText_Contribution(),
  87       new CRM_Contact_Form_Search_Custom_FullText_Participant(),
  88       new CRM_Contact_Form_Search_Custom_FullText_Membership(),
  89     );
  90
  91     $formValues['table'] = $this->getFieldValue($formValues, 'table', 'String');
  92     $this->_table = $formValues['table'];
  93
  94     $formValues['text'] = trim($this->getFieldValue($formValues, 'text', 'String', ''));
  95     $this->_text = $formValues['text'];
  96
  97     if (!$this->_table) {
  98       $this->_limitClause = array($this->_limitNumberPlus1, NULL);
  99       $this->_limitRowClause = $this->_limitDetailClause = array($this->_limitNumber, NULL);
 100     }
 101     else {
 102       // when there is table specified, we would like to use the pager. But since
 103       // 1. this custom search has slightly different structure ,
 104       // 2. we are in constructor right now,
 105       // we 'll use a small hack -
 106       $rowCount = CRM_Utils_Array::value('crmRowCount', $_REQUEST, CRM_Utils_Pager::ROWCOUNT);
 107       $pageId = CRM_Utils_Array::value('crmPID', $_REQUEST, 1);
 108       $offset = ($pageId - 1) * $rowCount;
 109       $this->_limitClause = NULL;
 110       $this->_limitRowClause = array($rowCount, NULL);
 111       $this->_limitDetailClause = array($rowCount, $offset);
 112     }
 113
 114     $this->_formValues = $formValues;
 115   }
 116
 117   /**
 118    * Get a value from $formValues. If missing, get it from the request.
 119    *
 120    * @param $formValues
 121    * @param $field
 122    * @param $type
 123    * @param null $default
 124    * @return mixed|null
 125    */
 126   public function getFieldValue($formValues, $field, $type, $default = NULL) {
 127     $value = CRM_Utils_Array::value($field, $formValues);
 128     if (!$value) {
 129       return CRM_Utils_Request::retrieve($field, $type, CRM_Core_DAO::$_nullObject, FALSE, $default);
 130     }
 131     return $value;
 132   }
 133
 134   public function __destruct() {
 135   }
 136
 137   public function initialize() {
 138     static $initialized = FALSE;
 139
 140     if (!$initialized) {
 141       $initialized = TRUE;
 142
 143       $this->buildTempTable();
 144
 145       $this->fillTable();
 146     }
 147   }
 148
 149   public function buildTempTable() {
 150     $randomNum = md5(uniqid());
 151     $this->_tableName = "civicrm_temp_custom_details_{$randomNum}";
 152
 153     $this->_tableFields = array(
 154       'id' => 'int unsigned NOT NULL AUTO_INCREMENT',
 155       'table_name' => 'varchar(16)',
 156       'contact_id' => 'int unsigned',
 157       'sort_name' => 'varchar(128)',
 158       'display_name' => 'varchar(128)',
 159       'assignee_contact_id' => 'int unsigned',
 160       'assignee_sort_name' => 'varchar(128)',
 161       'target_contact_id' => 'int unsigned',
 162       'target_sort_name' => 'varchar(128)',
 163       'activity_id' => 'int unsigned',
 164       'activity_type_id' => 'int unsigned',
 165       'record_type' => 'varchar(16)',
 166       'client_id' => 'int unsigned',
 167       'case_id' => 'int unsigned',
 168       'case_start_date' => 'datetime',
 169       'case_end_date' => 'datetime',
 170       'case_is_deleted' => 'tinyint',
 171       'subject' => 'varchar(255)',
 172       'details' => 'varchar(255)',
 173       'contribution_id' => 'int unsigned',
 174       'financial_type' => 'varchar(255)',
 175       'contribution_page' => 'varchar(255)',
 176       'contribution_receive_date' => 'datetime',
 177       'contribution_total_amount' => 'decimal(20,2)',
 178       'contribution_trxn_Id' => 'varchar(255)',
 179       'contribution_source' => 'varchar(255)',
 180       'contribution_status' => 'varchar(255)',
 181       'contribution_check_number' => 'varchar(255)',
 182       'participant_id' => 'int unsigned',
 183       'event_title' => 'varchar(255)',
 184       'participant_fee_level' => 'varchar(255)',
 185       'participant_fee_amount' => 'int unsigned',
 186       'participant_source' => 'varchar(255)',
 187       'participant_register_date' => 'datetime',
 188       'participant_status' => 'varchar(255)',
 189       'participant_role' => 'varchar(255)',
 190       'membership_id' => 'int unsigned',
 191       'membership_fee' => 'int unsigned',
 192       'membership_type' => 'varchar(255)',
 193       'membership_start_date' => 'datetime',
 194       'membership_end_date' => 'datetime',
 195       'membership_source' => 'varchar(255)',
 196       'membership_status' => 'varchar(255)',
 197       // We may have multiple files to list on one record.
 198       // The temporary-table approach can't store full details for all of them
 199       'file_ids' => 'varchar(255)', // comma-separate id listing
 200     );
 201
 202     $sql = "
 203 CREATE TEMPORARY TABLE {$this->_tableName} (
 204 ";
 205
 206     foreach ($this->_tableFields as $name => $desc) {
 207       $sql .= "$name $desc,\n";
 208     }
 209
 210     $sql .= "
 211   PRIMARY KEY ( id )
 212 ) ENGINE=HEAP DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci
 213 ";
 214     CRM_Core_DAO::executeQuery($sql);
 215
 216     $this->_entityIDTableName = "civicrm_temp_custom_entityID_{$randomNum}";
 217     $sql = "
 218 CREATE TEMPORARY TABLE {$this->_entityIDTableName} (
 219   id int unsigned NOT NULL AUTO_INCREMENT,
 220   entity_id int unsigned NOT NULL,
 221
 222   UNIQUE INDEX unique_entity_id ( entity_id ),
 223   PRIMARY KEY ( id )
 224 ) ENGINE=HEAP DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci
 225 ";
 226     CRM_Core_DAO::executeQuery($sql);
 227   }
 228
 229   public function fillTable() {
 230     foreach ($this->_partialQueries as $partialQuery) {
 231       /** @var $partialQuery CRM_Contact_Form_Search_Custom_FullText_AbstractPartialQuery */
 232       if (!$this->_table || $this->_table == $partialQuery->getName()) {
 233         if ($partialQuery->isActive()) {
 234           $result = $partialQuery->fillTempTable($this->_text, $this->_entityIDTableName, $this->_tableName, $this->_limitClause, $this->_limitDetailClause);
 235           $this->_foundRows[$partialQuery->getName()] = $result['count'];
 236         }
 237       }
 238     }
 239
 240     $this->filterACLContacts();
 241   }
 242
 243   public function filterACLContacts() {
 244     if (CRM_Core_Permission::check('view all contacts')) {
 245       CRM_Core_DAO::executeQuery("DELETE FROM {$this->_tableName} WHERE contact_id IN (SELECT id FROM civicrm_contact WHERE is_deleted = 1)");
 246       return;
 247     }
 248
 249     $session = CRM_Core_Session::singleton();
 250     $contactID = $session->get('userID');
 251     if (!$contactID) {
 252       $contactID = 0;
 253     }
 254
 255     CRM_Contact_BAO_Contact_Permission::cache($contactID);
 256
 257     $params = array(1 => array($contactID, 'Integer'));
 258
 259     $sql = "
 260 DELETE     t.*
 261 FROM       {$this->_tableName} t
 262 WHERE      NOT EXISTS ( SELECT c.id
 263                         FROM civicrm_acl_contact_cache c
 264                         WHERE c.user_id = %1 AND t.contact_id = c.contact_id )
 265 ";
 266     CRM_Core_DAO::executeQuery($sql, $params);
 267
 268     $sql = "
 269 DELETE     t.*
 270 FROM       {$this->_tableName} t
 271 WHERE      t.table_name = 'Activity' AND
 272            NOT EXISTS ( SELECT c.id
 273                         FROM civicrm_acl_contact_cache c
 274                         WHERE c.user_id = %1 AND ( t.target_contact_id = c.contact_id OR t.target_contact_id IS NULL ) )
 275 ";
 276     CRM_Core_DAO::executeQuery($sql, $params);
 277
 278     $sql = "
 279 DELETE     t.*
 280 FROM       {$this->_tableName} t
 281 WHERE      t.table_name = 'Activity' AND
 282            NOT EXISTS ( SELECT c.id
 283                         FROM civicrm_acl_contact_cache c
 284                         WHERE c.user_id = %1 AND ( t.assignee_contact_id = c.contact_id OR t.assignee_contact_id IS NULL ) )
 285 ";
 286     CRM_Core_DAO::executeQuery($sql, $params);
 287   }
 288
 289   /**
 290    * @param CRM_Core_Form $form
 291    */
 292   public function buildForm(&$form) {
 293     $config = CRM_Core_Config::singleton();
 294
 295     $form->applyFilter('__ALL__', 'trim');
 296     $form->add('text',
 297       'text',
 298       ts('Find'),
 299       TRUE
 300     );
 301
 302     // also add a select box to allow the search to be constrained
 303     $tables = array('' => ts('All tables'));
 304     foreach ($this->_partialQueries as $partialQuery) {
 305       /** @var $partialQuery CRM_Contact_Form_Search_Custom_FullText_AbstractPartialQuery */
 306       if ($partialQuery->isActive()) {
 307         $tables[$partialQuery->getName()] = $partialQuery->getLabel();
 308       }
 309     }
 310
 311     $form->add('select', 'table', ts('Tables'), $tables);
 312
 313     $form->assign('csID', $form->get('csid'));
 314
 315     // also add the limit constant
 316     $form->assign('limit', self::LIMIT);
 317
 318     // set form defaults
 319     if (!empty($form->_formValues)) {
 320       $defaults = array();
 321
 322       if (isset($form->_formValues['text'])) {
 323         $defaults['text'] = $form->_formValues['text'];
 324       }
 325
 326       if (isset($form->_formValues['table'])) {
 327         $defaults['table'] = $form->_formValues['table'];
 328         $form->assign('table', $form->_formValues['table']);
 329       }
 330
 331       $form->setDefaults($defaults);
 332     }
 333
 334     /**
 335      * You can define a custom title for the search form
 336      */
 337     $this->setTitle(ts('Full-text Search'));
 338
 339     $searchService = CRM_Core_BAO_File::getSearchService();
 340     $form->assign('allowFileSearch', !empty($searchService) && CRM_Core_Permission::check('access uploaded files'));
 341   }
 342
 343   /**
 344    * @return array
 345    */
 346   public function &columns() {
 347     $this->_columns = array(
 348       ts('Contact ID') => 'contact_id',
 349       ts('Name') => 'sort_name',
 350     );
 351
 352     return $this->_columns;
 353   }
 354
 355   /**
 356    * @return array
 357    */
 358   public function summary() {
 359     $this->initialize();
 360
 361     $summary = array();
 362     foreach ($this->_partialQueries as $partialQuery) {
 363       /** @var $partialQuery CRM_Contact_Form_Search_Custom_FullText_AbstractPartialQuery */
 364       $summary[$partialQuery->getName()] = array();
 365     }
 366
 367     // now iterate through the table and add entries to the relevant section
 368     $sql = "SELECT * FROM {$this->_tableName}";
 369     if ($this->_table) {
 370       $sql .= " {$this->toLimit($this->_limitRowClause)} ";
 371     }
 372     $dao = CRM_Core_DAO::executeQuery($sql);
 373
 374     $activityTypes = CRM_Core_PseudoConstant::activityType(TRUE, TRUE);
 375     $roleIds = CRM_Event_PseudoConstant::participantRole();
 376     while ($dao->fetch()) {
 377       $row = array();
 378       foreach ($this->_tableFields as $name => $dontCare) {
 379         if ($name != 'activity_type_id') {
 380           $row[$name] = $dao->$name;
 381         }
 382         else {
 383           $row['activity_type'] = CRM_Utils_Array::value($dao->$name, $activityTypes);
 384         }
 385       }
 386       if (isset($row['participant_role'])) {
 387         $participantRole = explode(CRM_Core_DAO::VALUE_SEPARATOR, $row['participant_role']);
 388         $viewRoles = array();
 389         foreach ($participantRole as $v) {
 390           $viewRoles[] = $roleIds[$v];
 391         }
 392         $row['participant_role'] = implode(', ', $viewRoles);
 393       }
 394       if (!empty($row['file_ids'])) {
 395         $fileIds = (explode(',', $row['file_ids']));
 396         $fileHtml = '';
 397         foreach ($fileIds as $fileId) {
 398           $paperclip = CRM_Core_BAO_File::paperIconAttachment('*', $fileId);
 399           if ($paperclip) {
 400             $fileHtml .= implode('', $paperclip);
 401           }
 402         }
 403         $row['fileHtml'] = $fileHtml;
 404       }
 405       $summary[$dao->table_name][] = $row;
 406     }
 407
 408     $summary['Count'] = array();
 409     foreach (array_keys($summary) as $table) {
 410       $summary['Count'][$table] = CRM_Utils_Array::value($table, $this->_foundRows);
 411       if ($summary['Count'][$table] >= self::LIMIT) {
 412         $summary['addShowAllLink'][$table] = TRUE;
 413       }
 414       else {
 415         $summary['addShowAllLink'][$table] = FALSE;
 416       }
 417     }
 418
 419     return $summary;
 420   }
 421
 422   /**
 423    * @return null|string
 424    */
 425   public function count() {
 426     $this->initialize();
 427
 428     if ($this->_table) {
 429       return $this->_foundRows[$this->_table];
 430     }
 431     else {
 432       return CRM_Core_DAO::singleValueQuery("SELECT count(id) FROM {$this->_tableName}");
 433     }
 434   }
 435
 436   /**
 437    * @param int $offset
 438    * @param int $rowcount
 439    * @param null $sort
 440    * @param bool $returnSQL
 441    *
 442    * @return null|string
 443    */
 444   public function contactIDs($offset = 0, $rowcount = 0, $sort = NULL, $returnSQL = FALSE) {
 445     $this->initialize();
 446
 447     if ($returnSQL) {
 448       return $this->all($offset, $rowcount, $sort, FALSE, TRUE);
 449     }
 450     else {
 451       return CRM_Core_DAO::singleValueQuery("SELECT contact_id FROM {$this->_tableName}");
 452     }
 453   }
 454
 455   /**
 456    * @param int $offset
 457    * @param int $rowcount
 458    * @param null $sort
 459    * @param bool $includeContactIDs
 460    * @param bool $justIDs
 461    *
 462    * @return string
 463    */
 464   public function all($offset = 0, $rowcount = 0, $sort = NULL, $includeContactIDs = FALSE, $justIDs = FALSE) {
 465     $this->initialize();
 466
 467     if ($justIDs) {
 468       $select = "contact_a.id as contact_id";
 469     }
 470     else {
 471       $select = "
 472   contact_a.contact_id   as contact_id  ,
 473   contact_a.sort_name as sort_name
 474 ";
 475     }
 476
 477     $sql = "
 478 SELECT $select
 479 FROM   {$this->_tableName} contact_a
 480        {$this->toLimit($this->_limitRowClause)}
 481 ";
 482     return $sql;
 483   }
 484
 485   /**
 486    * @return null
 487    */
 488   public function from() {
 489     return NULL;
 490   }
 491
 492   /**
 493    * @param bool $includeContactIDs
 494    *
 495    * @return null
 496    */
 497   public function where($includeContactIDs = FALSE) {
 498     return NULL;
 499   }
 500
 501   /**
 502    * @return string
 503    */
 504   public function templateFile() {
 505     return 'CRM/Contact/Form/Search/Custom/FullText.tpl';
 506   }
 507
 508   /**
 509    * @return array
 510    */
 511   public function setDefaultValues() {
 512     return array();
 513   }
 514
 515   /**
 516    * @param $row
 517    */
 518   public function alterRow(&$row) {
 519   }
 520
 521   /**
 522    * @param $title
 523    */
 524   public function setTitle($title) {
 525     if ($title) {
 526       CRM_Utils_System::setTitle($title);
 527     }
 528   }
 529
 530   /**
 531    * @param int|array $limit
 532    * @return string
 533    *   SQL
 534    * @see CRM_Contact_Form_Search_Custom_FullText_AbstractPartialQuery::toLimit
 535    */
 536   public function toLimit($limit) {
 537     if (is_array($limit)) {
 538       list ($limit, $offset) = $limit;
 539     }
 540     if (empty($limit)) {
 541       return '';
 542     }
 543     $result = "LIMIT {$limit}";
 544     if ($offset) {
 545       $result .= " OFFSET {$offset}";
 546     }
 547     return $result;
 548   }
 549 }