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

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