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

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