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   protected $_limitNumberPlus1 = 11; // this should be one more than self::LIMIT
  73
  74   protected $_foundRows = array();
  75
  76   /**
  77    * Class constructor.
  78    *
  79    * @param array $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     $table = CRM_Utils_SQL_TempTable::build()->setCategory('custom')->setMemory()->setUtf8();
 151     $this->_tableName = $table->getName();
 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 ";
 204
 205     foreach ($this->_tableFields as $name => $desc) {
 206       $sql .= "$name $desc,\n";
 207     }
 208
 209     $sql .= "
 210   PRIMARY KEY ( id )
 211 ";
 212     $table->createWithColumns($sql);
 213
 214     $entityIdTable = CRM_Utils_SQL_TempTable::build()->setCategory('custom')->setMemory()->setUtf8();
 215     $this->_entityIDTableName = $entityIdTable->getName();
 216     $sql = "
 217   id int unsigned NOT NULL AUTO_INCREMENT,
 218   entity_id int unsigned NOT NULL,
 219
 220   UNIQUE INDEX unique_entity_id ( entity_id ),
 221   PRIMARY KEY ( id )
 222 ";
 223     $entityIdTable->createWithColumns($sql);
 224
 225     if (!empty($this->_formValues['is_unit_test'])) {
 226       $this->_tableNameForTest = $this->_tableName;
 227     }
 228   }
 229
 230   public function fillTable() {
 231     foreach ($this->_partialQueries as $partialQuery) {
 232       /** @var $partialQuery CRM_Contact_Form_Search_Custom_FullText_AbstractPartialQuery */
 233       if (!$this->_table || $this->_table == $partialQuery->getName()) {
 234         if ($partialQuery->isActive()) {
 235           $result = $partialQuery->fillTempTable($this->_text, $this->_entityIDTableName, $this->_tableName, $this->_limitClause, $this->_limitDetailClause);
 236           $this->_foundRows[$partialQuery->getName()] = $result['count'];
 237         }
 238       }
 239     }
 240
 241     $this->filterACLContacts();
 242   }
 243
 244   public function filterACLContacts() {
 245     if (CRM_Core_Permission::check('view all contacts')) {
 246       CRM_Core_DAO::executeQuery("DELETE FROM {$this->_tableName} WHERE contact_id IN (SELECT id FROM civicrm_contact WHERE is_deleted = 1)");
 247       return;
 248     }
 249
 250     $session = CRM_Core_Session::singleton();
 251     $contactID = $session->get('userID');
 252     if (!$contactID) {
 253       $contactID = 0;
 254     }
 255
 256     CRM_Contact_BAO_Contact_Permission::cache($contactID);
 257
 258     $params = array(1 => array($contactID, 'Integer'));
 259
 260     $sql = "
 261 DELETE     t.*
 262 FROM       {$this->_tableName} t
 263 WHERE      NOT EXISTS ( SELECT c.contact_id
 264                         FROM civicrm_acl_contact_cache c
 265                         WHERE c.user_id = %1 AND t.contact_id = c.contact_id )
 266 ";
 267     CRM_Core_DAO::executeQuery($sql, $params);
 268
 269     $sql = "
 270 DELETE     t.*
 271 FROM       {$this->_tableName} t
 272 WHERE      t.table_name = 'Activity' AND
 273            NOT EXISTS ( SELECT c.contact_id
 274                         FROM civicrm_acl_contact_cache c
 275                         WHERE c.user_id = %1 AND ( t.target_contact_id = c.contact_id OR t.target_contact_id IS NULL ) )
 276 ";
 277     CRM_Core_DAO::executeQuery($sql, $params);
 278
 279     $sql = "
 280 DELETE     t.*
 281 FROM       {$this->_tableName} t
 282 WHERE      t.table_name = 'Activity' AND
 283            NOT EXISTS ( SELECT c.contact_id
 284                         FROM civicrm_acl_contact_cache c
 285                         WHERE c.user_id = %1 AND ( t.assignee_contact_id = c.contact_id OR t.assignee_contact_id IS NULL ) )
 286 ";
 287     CRM_Core_DAO::executeQuery($sql, $params);
 288   }
 289
 290   /**
 291    * @param CRM_Core_Form $form
 292    */
 293   public function buildForm(&$form) {
 294     $config = CRM_Core_Config::singleton();
 295
 296     $form->applyFilter('__ALL__', 'trim');
 297     $form->add('text',
 298       'text',
 299       ts('Find'),
 300       TRUE
 301     );
 302
 303     // also add a select box to allow the search to be constrained
 304     $tables = array('' => ts('All tables'));
 305     foreach ($this->_partialQueries as $partialQuery) {
 306       /** @var $partialQuery CRM_Contact_Form_Search_Custom_FullText_AbstractPartialQuery */
 307       if ($partialQuery->isActive()) {
 308         $tables[$partialQuery->getName()] = $partialQuery->getLabel();
 309       }
 310     }
 311
 312     $form->add('select', 'table', ts('Tables'), $tables);
 313
 314     $form->assign('csID', $form->get('csid'));
 315
 316     // also add the limit constant
 317     $form->assign('limit', self::LIMIT);
 318
 319     // set form defaults
 320     if (!empty($form->_formValues)) {
 321       $defaults = array();
 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 = array(
 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 = array();
 363     foreach ($this->_partialQueries as $partialQuery) {
 364       /** @var $partialQuery CRM_Contact_Form_Search_Custom_FullText_AbstractPartialQuery */
 365       $summary[$partialQuery->getName()] = array();
 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 = array();
 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'] = CRM_Utils_Array::value($dao->$name, $activityTypes);
 385         }
 386       }
 387       if (isset($row['participant_role'])) {
 388         $participantRole = explode(CRM_Core_DAO::VALUE_SEPARATOR, $row['participant_role']);
 389         $viewRoles = array();
 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'] = array();
 410     foreach (array_keys($summary) as $table) {
 411       $summary['Count'][$table] = CRM_Utils_Array::value($table, $this->_foundRows);
 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 array();
 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 }