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

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 4.4                                                |
   5  +--------------------------------------------------------------------+
   6  | Copyright CiviCRM LLC (c) 2004-2013                                |
   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-2013
  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   protected $_formValues;
  40
  41   protected $_columns;
  42
  43   protected $_text = NULL;
  44
  45   protected $_textID = NULL;
  46
  47   protected $_table = NULL;
  48
  49   protected $_tableName = NULL;
  50
  51   protected $_entityIDTableName = NULL;
  52
  53   protected $_tableFields = NULL;
  54
  55   protected $_limitClause = NULL;
  56
  57   protected $_limitRowClause = NULL;
  58
  59   protected $_limitDetailClause = NULL;
  60
  61   protected $_limitNumber      = 10;
  62   protected $_limitNumberPlus1 = 11; // this should be one more than self::LIMIT
  63
  64   protected $_foundRows = array();
  65
  66   function __construct(&$formValues) {
  67     $this->_formValues = &$formValues;
  68
  69     $this->_text = CRM_Utils_Array::value('text', $formValues);
  70     $this->_table = CRM_Utils_Array::value('table', $formValues);
  71
  72     if (!$this->_text) {
  73       $this->_text = CRM_Utils_Request::retrieve('text', 'String', CRM_Core_DAO::$_nullObject);
  74       if ($this->_text) {
  75         $this->_text = trim($this->_text);
  76         $formValues['text'] = $this->_text;
  77       }
  78     }
  79
  80     if (!$this->_table) {
  81       $this->_table = CRM_Utils_Request::retrieve('table', 'String', CRM_Core_DAO::$_nullObject);
  82       if ($this->_table) {
  83         $formValues['table'] = $this->_table;
  84       }
  85     }
  86
  87     // fix text to include wild card characters at begining and end
  88     if ($this->_text) {
  89       if (is_numeric($this->_text)) {
  90         $this->_textID = $this->_text;
  91       }
  92
  93       $strtolower = function_exists('mb_strtolower') ? 'mb_strtolower' : 'strtolower';
  94       $this->_text = $strtolower(CRM_Core_DAO::escapeString($this->_text));
  95       if (strpos($this->_text, '%') === FALSE) {
  96         $this->_text = "'%{$this->_text}%'";
  97       }
  98       else {
  99         $this->_text = "'{$this->_text}'";
 100       }
 101     }
 102     else {
 103       $this->_text = "'%'";
 104     }
 105
 106     if (!$this->_table) {
 107       $this->_limitClause = " LIMIT {$this->_limitNumberPlus1}";
 108       $this->_limitRowClause = $this->_limitDetailClause = "  LIMIT {$this->_limitNumber}";
 109     }
 110     else {
 111       // when there is table specified, we would like to use the pager. But since
 112       // 1. this custom search has slightly different structure ,
 113       // 2. we are in constructor right now,
 114       // we 'll use a small hack -
 115       $rowCount = CRM_Utils_Array::value('crmRowCount', $_REQUEST, CRM_Utils_Pager::ROWCOUNT);
 116       $pageId = CRM_Utils_Array::value('crmPID', $_REQUEST, 1);
 117       $offset = ($pageId - 1) * $rowCount;
 118       $this->_limitClause = NULL;
 119       $this->_limitRowClause = " LIMIT $rowCount";
 120       $this->_limitDetailClause = " LIMIT $offset, $rowCount";
 121     }
 122   }
 123
 124   function __destruct() {
 125   }
 126
 127   function initialize() {
 128     static $initialized = FALSE;
 129
 130     if (!$initialized) {
 131       $initialized = TRUE;
 132
 133       $this->buildTempTable();
 134
 135       $this->fillTable();
 136     }
 137   }
 138
 139   function buildTempTable() {
 140     $randomNum = md5(uniqid());
 141     $this->_tableName = "civicrm_temp_custom_details_{$randomNum}";
 142
 143     $this->_tableFields = array(
 144       'id' => 'int unsigned NOT NULL AUTO_INCREMENT',
 145       'table_name' => 'varchar(16)',
 146       'contact_id' => 'int unsigned',
 147       'sort_name' => 'varchar(128)',
 148       'assignee_contact_id' => 'int unsigned',
 149       'assignee_sort_name' => 'varchar(128)',
 150       'target_contact_id' => 'int unsigned',
 151       'target_sort_name' => 'varchar(128)',
 152       'activity_id' => 'int unsigned',
 153       'activity_type_id' => 'int unsigned',
 154       'record_type' => 'varchar(16)',
 155       'client_id' => 'int unsigned',
 156       'case_id' => 'int unsigned',
 157       'case_start_date' => 'datetime',
 158       'case_end_date' => 'datetime',
 159       'case_is_deleted' => 'tinyint',
 160       'subject' => 'varchar(255)',
 161       'details' => 'varchar(255)',
 162       'contribution_id' => 'int unsigned',
 163       'financial_type'         => 'varchar(255)',
 164       'contribution_page' => 'varchar(255)',
 165       'contribution_receive_date' => 'datetime',
 166       'contribution_total_amount' => 'decimal(20,2)',
 167       'contribution_trxn_Id' => 'varchar(255)',
 168       'contribution_source' => 'varchar(255)',
 169       'contribution_status' => 'varchar(255)',
 170       'contribution_check_number' => 'varchar(255)',
 171       'participant_id' => 'int unsigned',
 172       'event_title' => 'varchar(255)',
 173       'participant_fee_level' => 'varchar(255)',
 174       'participant_fee_amount' => 'int unsigned',
 175       'participant_source' => 'varchar(255)',
 176       'participant_register_date' => 'datetime',
 177       'participant_status' => 'varchar(255)',
 178       'participant_role' => 'varchar(255)',
 179       'membership_id' => 'int unsigned',
 180       'membership_fee' => 'int unsigned',
 181       'membership_type' => 'varchar(255)',
 182       'membership_start_date' => 'datetime',
 183       'membership_end_date' => 'datetime',
 184       'membership_source' => 'varchar(255)',
 185       'membership_status' => 'varchar(255)',
 186     );
 187
 188     $sql = "
 189 CREATE TEMPORARY TABLE {$this->_tableName} (
 190 ";
 191
 192     foreach ($this->_tableFields as $name => $desc) {
 193       $sql .= "$name $desc,\n";
 194     }
 195
 196     $sql .= "
 197   PRIMARY KEY ( id )
 198 ) ENGINE=HEAP DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci
 199 ";
 200     CRM_Core_DAO::executeQuery($sql);
 201
 202     $this->_entityIDTableName = "civicrm_temp_custom_entityID_{$randomNum}";
 203     $sql = "
 204 CREATE TEMPORARY TABLE {$this->_entityIDTableName} (
 205   id int unsigned NOT NULL AUTO_INCREMENT,
 206   entity_id int unsigned NOT NULL,
 207
 208   UNIQUE INDEX unique_entity_id ( entity_id ),
 209   PRIMARY KEY ( id )
 210 ) ENGINE=HEAP DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci
 211 ";
 212     CRM_Core_DAO::executeQuery($sql);
 213   }
 214
 215   function fillTable() {
 216     $config = CRM_Core_Config::singleton();
 217
 218     if ((!$this->_table || $this->_table == 'Contact')) {
 219       $this->fillContact();
 220     }
 221
 222     if ((!$this->_table || $this->_table == 'Activity') &&
 223       CRM_Core_Permission::check('view all activities')
 224     ) {
 225       $this->fillActivity();
 226     }
 227
 228     if ((!$this->_table || $this->_table == 'Case') &&
 229       in_array('CiviCase', $config->enableComponents)
 230     ) {
 231       $this->fillCase();
 232     }
 233
 234     if ((!$this->_table || $this->_table == 'Contribution') &&
 235       in_array('CiviContribute', $config->enableComponents) &&
 236       CRM_Core_Permission::check('access CiviContribute')
 237     ) {
 238       $this->fillContribution();
 239     }
 240
 241     if ((!$this->_table || $this->_table == 'Participant') &&
 242       (in_array('CiviEvent', $config->enableComponents) &&
 243         CRM_Core_Permission::check('view event participants')
 244       )
 245     ) {
 246       $this->fillParticipant();
 247     }
 248
 249     if ((!$this->_table || $this->_table == 'Membership') &&
 250       in_array('CiviMember', $config->enableComponents) &&
 251       CRM_Core_Permission::check('access CiviMember')
 252     ) {
 253       $this->fillMembership();
 254     }
 255
 256     $this->filterACLContacts();
 257   }
 258
 259   function filterACLContacts() {
 260     if (CRM_Core_Permission::check('view all contacts')) {
 261       CRM_Core_DAO::executeQuery("DELETE FROM {$this->_tableName} WHERE contact_id IN (SELECT id FROM civicrm_contact WHERE is_deleted = 1)");
 262       return;
 263     }
 264
 265     $session = CRM_Core_Session::singleton();
 266     $contactID = $session->get('userID');
 267     if (!$contactID) {
 268       $contactID = 0;
 269     }
 270
 271     CRM_Contact_BAO_Contact_Permission::cache($contactID);
 272
 273     $params = array(1 => array($contactID, 'Integer'));
 274
 275     $sql = "
 276 DELETE     t.*
 277 FROM       {$this->_tableName} t
 278 WHERE      NOT EXISTS ( SELECT c.id
 279                         FROM civicrm_acl_contact_cache c
 280                         WHERE c.user_id = %1 AND t.contact_id = c.contact_id )
 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.id
 289                         FROM civicrm_acl_contact_cache c
 290                         WHERE c.user_id = %1 AND ( t.target_contact_id = c.contact_id OR t.target_contact_id IS NULL ) )
 291 ";
 292     CRM_Core_DAO::executeQuery($sql, $params);
 293
 294     $sql = "
 295 DELETE     t.*
 296 FROM       {$this->_tableName} t
 297 WHERE      t.table_name = 'Activity' AND
 298            NOT EXISTS ( SELECT c.id
 299                         FROM civicrm_acl_contact_cache c
 300                         WHERE c.user_id = %1 AND ( t.assignee_contact_id = c.contact_id OR t.assignee_contact_id IS NULL ) )
 301 ";
 302     CRM_Core_DAO::executeQuery($sql, $params);
 303   }
 304
 305   function fillCustomInfo(&$tables,
 306     $extends
 307   ) {
 308
 309     $sql = "
 310 SELECT     cg.table_name, cf.column_name
 311 FROM       civicrm_custom_group cg
 312 INNER JOIN civicrm_custom_field cf ON cf.custom_group_id = cg.id
 313 WHERE      cg.extends IN $extends
 314 AND        cg.is_active = 1
 315 AND        cf.is_active = 1
 316 AND        cf.is_searchable = 1
 317 AND        cf.html_type IN ( 'Text', 'TextArea', 'RichTextEditor' )
 318 ";
 319
 320     $dao = CRM_Core_DAO::executeQuery($sql);
 321     while ($dao->fetch()) {
 322       if (!array_key_exists($dao->table_name, $tables)) {
 323         $tables[$dao->table_name] = array(
 324           'id' => 'entity_id',
 325           'fields' => array(),
 326         );
 327       }
 328       $tables[$dao->table_name]['fields'][$dao->column_name] = NULL;
 329     }
 330   }
 331
 332   function runQueries(&$tables) {
 333     $sql = "TRUNCATE {$this->_entityIDTableName}";
 334     CRM_Core_DAO::executeQuery($sql);
 335
 336     $maxRowCount = 0;
 337     foreach ($tables as $tableName => $tableValues) {
 338       if ($tableName == 'final') {
 339         continue;
 340       }
 341       else if ($tableName == 'sql') {
 342         foreach ($tableValues as $sqlStatement) {
 343           $sql = "
 344 REPLACE INTO {$this->_entityIDTableName} ( entity_id )
 345 $sqlStatement
 346 {$this->_limitClause}
 347 ";
 348           CRM_Core_DAO::executeQuery($sql);
 349         }
 350       }
 351       else {
 352         $clauses = array();
 353
 354         foreach ($tableValues['fields'] as $fieldName => $fieldType) {
 355           if ($fieldType == 'Int') {
 356             if ($this->_textID) {
 357               $clauses[] = "$fieldName = {$this->_textID}";
 358             }
 359           }
 360           else {
 361             $clauses[] = "$fieldName LIKE {$this->_text}";
 362           }
 363         }
 364
 365         if (empty($clauses)) {
 366           continue;
 367         }
 368
 369         $whereClause = implode(' OR ', $clauses);
 370
 371         //resolve conflict between entity tables.
 372         if ($tableName == 'civicrm_note' &&
 373           $entityTable = CRM_Utils_Array::value('entity_table', $tableValues)
 374         ) {
 375           $whereClause .= " AND entity_table = '{$entityTable}'";
 376         }
 377
 378         $sql = "
 379 REPLACE  INTO {$this->_entityIDTableName} ( entity_id )
 380 SELECT   {$tableValues['id']}
 381 FROM     $tableName
 382 WHERE    ( $whereClause )
 383 AND      {$tableValues['id']} IS NOT NULL
 384 GROUP BY {$tableValues['id']}
 385 {$this->_limitClause}
 386 ";
 387         CRM_Core_DAO::executeQuery($sql);
 388       }
 389     }
 390
 391     if (isset($tables['final'])) {
 392       foreach ($tables['final'] as $sqlStatement) {
 393         CRM_Core_DAO::executeQuery($sqlStatement);
 394       }
 395     }
 396
 397     $rowCount = "SELECT count(*) FROM {$this->_entityIDTableName}";
 398     $tableKey = array_keys($tables);
 399     $this->_foundRows[ucfirst(str_replace('civicrm_', '', $tableKey[0]))] =
 400       CRM_Core_DAO::singleValueQuery($rowCount);
 401   }
 402
 403   function fillContactIDs() {
 404     $contactSQL = array();
 405     $contactSQL[] = "
 406 SELECT     et.entity_id
 407 FROM       civicrm_entity_tag et
 408 INNER JOIN civicrm_tag t ON et.tag_id = t.id
 409 WHERE      et.entity_table = 'civicrm_contact'
 410 AND        et.tag_id       = t.id
 411 AND        t.name LIKE {$this->_text}
 412 GROUP BY   et.entity_id
 413 ";
 414
 415     // lets delete all the deceased contacts from the entityID box
 416     // this allows us to keep numbers in sync
 417     // when we have acl contacts, the situation gets even more murky
 418     $final = array();
 419     $final[] = "DELETE FROM {$this->_entityIDTableName} WHERE entity_id IN (SELECT id FROM civicrm_contact WHERE is_deleted = 1)";
 420
 421     $tables = array(
 422       'civicrm_contact' => array(
 423         'id' => 'id',
 424         'fields' => array(
 425           'sort_name' => NULL,
 426           'nick_name' => NULL,
 427           'display_name' => NULL,
 428         ),
 429       ),
 430       'civicrm_address' => array(
 431         'id' => 'contact_id',
 432         'fields' => array(
 433           'street_address' => NULL,
 434           'city' => NULL,
 435           'postal_code' => NULL,
 436         ),
 437       ),
 438       'civicrm_email' => array(
 439         'id' => 'contact_id',
 440         'fields' => array('email' => NULL),
 441       ),
 442       'civicrm_phone' => array(
 443         'id' => 'contact_id',
 444         'fields' => array('phone' => NULL),
 445       ),
 446       'civicrm_note' => array(
 447         'id' => 'entity_id',
 448         'entity_table' => 'civicrm_contact',
 449         'fields' => array(
 450           'subject' => NULL,
 451           'note' => NULL,
 452         ),
 453       ),
 454       'sql'   => $contactSQL,
 455       'final' => $final,
 456     );
 457
 458     // get the custom data info
 459     $this->fillCustomInfo($tables,
 460       "( 'Contact', 'Individual', 'Organization', 'Household' )"
 461     );
 462
 463     $this->runQueries($tables);
 464   }
 465
 466   function fillContact() {
 467
 468     $this->fillContactIDs();
 469
 470     //move data from entity table to detail table.
 471     $this->moveEntityToDetail('Contact');
 472   }
 473
 474   function fillActivityIDs() {
 475     $contactSQL = array();
 476
 477     $contactSQL[] = "
 478 SELECT     distinct ca.id
 479 FROM       civicrm_activity ca
 480 INNER JOIN civicrm_activity_contact cat ON cat.activity_id = ca.id
 481 INNER JOIN civicrm_contact c ON cat.contact_id = c.id
 482 LEFT  JOIN civicrm_email e ON cat.contact_id = e.contact_id
 483 LEFT  JOIN civicrm_option_group og ON og.name = 'activity_type'
 484 LEFT  JOIN civicrm_option_value ov ON ( ov.option_group_id = og.id )
 485 WHERE      ( (c.sort_name LIKE {$this->_text} OR c.display_name LIKE {$this->_text}) OR
 486              ( e.email LIKE {$this->_text}    AND
 487                ca.activity_type_id = ov.value AND
 488                ov.name IN ('Inbound Email', 'Email') ) )
 489 AND        (ca.is_deleted = 0 OR ca.is_deleted IS NULL)
 490 AND        (c.is_deleted = 0 OR c.is_deleted IS NULL)
 491 ";
 492
 493     $contactSQL[] = "
 494 SELECT     et.entity_id
 495 FROM       civicrm_entity_tag et
 496 INNER JOIN civicrm_tag t ON et.tag_id = t.id
 497 INNER JOIN civicrm_activity ca ON et.entity_id = ca.id
 498 WHERE      et.entity_table = 'civicrm_activity'
 499 AND        et.tag_id       = t.id
 500 AND        t.name LIKE {$this->_text}
 501 AND        (ca.is_deleted = 0 OR ca.is_deleted IS NULL)
 502 GROUP BY   et.entity_id
 503 ";
 504
 505     $contactSQL[] = "
 506 SELECT distinct ca.id
 507 FROM   civicrm_activity ca
 508 WHERE  (ca.subject LIKE  {$this->_text} OR ca.details LIKE  {$this->_text})
 509 AND    (ca.is_deleted = 0 OR ca.is_deleted IS NULL)
 510 ";
 511
 512     $final = array();
 513
 514     $tables = array(
 515       'civicrm_activity' => array( 'fields' => array() ),
 516       'sql' => $contactSQL,
 517       'final' => $final,
 518     );
 519
 520     $this->fillCustomInfo($tables, "( 'Activity' )");
 521     $this->runQueries($tables);
 522   }
 523
 524   function fillActivity() {
 525
 526     $this->fillActivityIDs();
 527
 528     //move data from entity table to detail table
 529     $this->moveEntityToDetail('Activity');
 530   }
 531
 532   function fillCase() {
 533     $this->fillCaseIDs( );
 534
 535     //move data from entity table to detail table
 536     $this->moveEntityToDetail('Case');
 537   }
 538
 539   function fillCaseIDs( ) {
 540     $contactSQL = array();
 541
 542     $contactSQL[] = "
 543 SELECT    distinct cc.id
 544 FROM      civicrm_case cc
 545 LEFT JOIN civicrm_case_contact ccc ON cc.id = ccc.case_id
 546 LEFT JOIN civicrm_contact c ON ccc.contact_id = c.id
 547 WHERE     (c.sort_name LIKE {$this->_text} OR c.display_name LIKE {$this->_text})
 548           AND (cc.is_deleted = 0 OR cc.is_deleted IS NULL)
 549 ";
 550
 551     if ($this->_textID) {
 552       $contactSQL[] = "
 553 SELECT    distinct cc.id
 554 FROM      civicrm_case cc
 555 LEFT JOIN civicrm_case_contact ccc ON cc.id = ccc.case_id
 556 LEFT JOIN civicrm_contact c ON ccc.contact_id = c.id
 557 WHERE     cc.id = {$this->_textID}
 558           AND (cc.is_deleted = 0 OR cc.is_deleted IS NULL)
 559 ";
 560     }
 561
 562     $contactSQL[] = "
 563 SELECT     et.entity_id
 564 FROM       civicrm_entity_tag et
 565 INNER JOIN civicrm_tag t ON et.tag_id = t.id
 566 WHERE      et.entity_table = 'civicrm_case'
 567 AND        et.tag_id       = t.id
 568 AND        t.name LIKE {$this->_text}
 569 GROUP BY   et.entity_id
 570 ";
 571
 572     $tables = array(
 573       'civicrm_case' => array( 'fields' => array( ) ),
 574       'sql' => $contactSQL,
 575     );
 576
 577     $this->runQueries($tables);
 578   }
 579
 580   function fillContribution() {
 581     //get contribution ids in entity table.
 582     $this->fillContributionIDs();
 583
 584     //move data from entity table to detail table
 585     $this->moveEntityToDetail('Contribution');
 586   }
 587
 588   /**
 589    * get contribution ids in entity tables.
 590    */
 591   function fillContributionIDs() {
 592     $contactSQL = array();
 593     $contactSQL[] = "
 594 SELECT     distinct cc.id
 595 FROM       civicrm_contribution cc
 596 INNER JOIN civicrm_contact c ON cc.contact_id = c.id
 597 WHERE      (c.sort_name LIKE {$this->_text} OR
 598            c.display_name LIKE {$this->_text})
 599 ";
 600     $tables = array(
 601       'civicrm_contribution' => array('id' => 'id',
 602         'fields' => array(
 603           'source' => NULL,
 604           'amount_level' => NULL,
 605           'trxn_Id' => NULL,
 606           'invoice_id' => NULL,
 607           'check_number' => ($this->_textID) ? 'Int' : NULL,
 608           'total_amount' => ($this->_textID) ? 'Int' : NULL,
 609         ),
 610       ),
 611       'sql' => $contactSQL,
 612       'civicrm_note' => array(
 613         'id' => 'entity_id',
 614         'entity_table' => 'civicrm_contribution',
 615         'fields' => array(
 616           'subject' => NULL,
 617           'note' => NULL,
 618         ),
 619       ),
 620     );
 621
 622     // get the custom data info
 623     $this->fillCustomInfo($tables, "( 'Contribution' )");
 624     $this->runQueries($tables);
 625   }
 626
 627   function fillParticipant() {
 628     //get participant ids in entity table.
 629     $this->fillParticipantIDs();
 630
 631     //move data from entity table to detail table
 632     $this->moveEntityToDetail('Participant');
 633   }
 634
 635   /**
 636    * get participant ids in entity tables.
 637    */
 638   function fillParticipantIDs() {
 639     $contactSQL = array();
 640     $contactSQL[] = "
 641 SELECT     distinct cp.id
 642 FROM       civicrm_participant cp
 643 INNER JOIN civicrm_contact c ON cp.contact_id = c.id
 644 WHERE      (c.sort_name LIKE {$this->_text} OR c.display_name LIKE {$this->_text})
 645 ";
 646     $tables = array(
 647       'civicrm_participant' => array('id' => 'id',
 648         'fields' => array(
 649           'source' => NULL,
 650           'fee_level' => NULL,
 651           'fee_amount' => ($this->_textID) ? 'Int' : NULL,
 652         ),
 653       ),
 654       'sql' => $contactSQL,
 655       'civicrm_note' => array(
 656         'id' => 'entity_id',
 657         'entity_table' => 'civicrm_participant',
 658         'fields' => array(
 659           'subject' => NULL,
 660           'note' => NULL,
 661         ),
 662       ),
 663     );
 664
 665     // get the custom data info
 666     $this->fillCustomInfo($tables, "( 'Participant' )");
 667     $this->runQueries($tables);
 668   }
 669
 670   function fillMembership() {
 671
 672     //get membership ids in entity table.
 673     $this->fillMembershipIDs();
 674
 675     //move data from entity table to detail table
 676     $this->moveEntityToDetail('Membership');
 677   }
 678
 679   /**
 680    * get membership ids in entity tables.
 681    */
 682   function fillMembershipIDs() {
 683     $contactSQL = array();
 684     $contactSQL[] = "
 685 SELECT     distinct cm.id
 686 FROM       civicrm_membership cm
 687 INNER JOIN civicrm_contact c ON cm.contact_id = c.id
 688 WHERE      (c.sort_name LIKE {$this->_text} OR c.display_name LIKE {$this->_text})
 689 ";
 690     $tables = array(
 691       'civicrm_membership' => array('id' => 'id',
 692         'fields' => array('source' => NULL),
 693       ),
 694       'sql' => $contactSQL,
 695     );
 696
 697     // get the custom data info
 698     $this->fillCustomInfo($tables, "( 'Membership' )");
 699     $this->runQueries($tables);
 700   }
 701
 702   function buildForm(&$form) {
 703     $config = CRM_Core_Config::singleton();
 704
 705     $form->applyFilter('__ALL__', 'trim');
 706     $form->add('text',
 707       'text',
 708       ts('Find'),
 709       TRUE
 710     );
 711
 712     // also add a select box to allow the search to be constrained
 713     $tables = array('' => ts('All tables'));
 714     if (CRM_Core_Permission::check('view all contacts')) {
 715       $tables['Contact'] = ts('Contacts');
 716     }
 717     if (CRM_Core_Permission::check('view all activities')) {
 718       $tables['Activity'] = ts('Activities');
 719     }
 720     if (in_array('CiviCase', $config->enableComponents)) {
 721       $tables['Case'] = ts('Cases');
 722     }
 723     if (in_array('CiviContribute', $config->enableComponents)) {
 724       $tables['Contribution'] = ts('Contributions');
 725     }
 726     if (in_array('CiviEvent', $config->enableComponents) && CRM_Core_Permission::check('view event participants')) {
 727       $tables['Participant'] = ts('Participants');
 728     }
 729     if (in_array('CiviMember', $config->enableComponents)) {
 730       $tables['Membership'] = ts('Memberships');
 731     }
 732
 733     $form->add('select',
 734       'table',
 735       ts('Tables'),
 736       $tables
 737     );
 738
 739     $form->assign('csID', $form->get('csid'));
 740
 741     // also add the limit constant
 742     $form->assign('limit', self::LIMIT);
 743
 744     /**
 745      * You can define a custom title for the search form
 746      */
 747     $this->setTitle(ts('Full-text Search'));
 748   }
 749
 750   function &columns() {
 751     $this->_columns = array(
 752       ts('Contact Id') => 'contact_id',
 753       ts('Name') => 'sort_name',
 754     );
 755
 756     return $this->_columns;
 757   }
 758
 759   function summary() {
 760     $this->initialize();
 761
 762     $summary = array('Contact' => array(),
 763       'Activity' => array(),
 764       'Case' => array(),
 765       'Contribution' => array(),
 766       'Participant' => array(),
 767       'Membership' => array(),
 768     );
 769
 770
 771     // now iterate through the table and add entries to the relevant section
 772     $sql = "SELECT * FROM {$this->_tableName}";
 773     if ($this->_table) {
 774       $sql .= " {$this->_limitRowClause} ";
 775     }
 776     $dao = CRM_Core_DAO::executeQuery($sql);
 777
 778     $activityTypes = CRM_Core_PseudoConstant::activityType(TRUE, TRUE);
 779     $roleIds = CRM_Event_PseudoConstant::participantRole();
 780     while ($dao->fetch()) {
 781       $row = array();
 782       foreach ($this->_tableFields as $name => $dontCare) {
 783         if ($name != 'activity_type_id') {
 784           $row[$name] = $dao->$name;
 785         }
 786         else {
 787           $row['activity_type'] = CRM_Utils_Array::value($dao->$name, $activityTypes);
 788         }
 789       }
 790       if (isset($row['participant_role'])) {
 791         $participantRole = explode(CRM_Core_DAO::VALUE_SEPARATOR, $row['participant_role']);
 792         $viewRoles = array();
 793         foreach ($participantRole as $k => $v) {
 794           $viewRoles[] = $roleIds[$v];
 795         }
 796         $row['participant_role'] = implode(', ', $viewRoles);
 797       }
 798       $summary[$dao->table_name][] = $row;
 799     }
 800
 801     $summary['Count'] = array();
 802     foreach (array_keys($summary) as $table) {
 803       $summary['Count'][$table] = CRM_Utils_Array::value($table, $this->_foundRows);
 804       if ($summary['Count'][$table] >= self::LIMIT) {
 805         $summary['addShowAllLink'][$table] = TRUE;
 806       }
 807       else {
 808         $summary['addShowAllLink'][$table] = FALSE;
 809       }
 810     }
 811
 812     return $summary;
 813   }
 814
 815   function count() {
 816     $this->initialize();
 817
 818     if ($this->_table) {
 819       return $this->_foundRows[$this->_table];
 820     }
 821     else {
 822       return CRM_Core_DAO::singleValueQuery("SELECT count(id) FROM {$this->_tableName}");
 823     }
 824   }
 825
 826   function contactIDs($offset = 0, $rowcount = 0, $sort = NULL) {
 827     $this->initialize();
 828
 829     return CRM_Core_DAO::singleValueQuery("SELECT contact_id FROM {$this->_tableName}");
 830   }
 831
 832   function all($offset = 0, $rowcount = 0, $sort = NULL,
 833     $includeContactIDs = FALSE, $justIDs = FALSE
 834   ) {
 835     $this->initialize();
 836
 837     if ($justIDs) {
 838       $select = "contact_a.contact_id as contact_id";
 839     }
 840     else {
 841       $select = "
 842   contact_a.contact_id   as contact_id  ,
 843   contact_a.sort_name as sort_name
 844 ";
 845     }
 846
 847     $sql = "
 848 SELECT $select
 849 FROM   {$this->_tableName} contact_a
 850        {$this->_limitRowClause}
 851 ";
 852     return $sql;
 853   }
 854
 855   function from() {
 856     return NULL;
 857   }
 858
 859   function where($includeContactIDs = FALSE) {
 860     return NULL;
 861   }
 862
 863   function templateFile() {
 864     return 'CRM/Contact/Form/Search/Custom/FullText.tpl';
 865   }
 866
 867   function setDefaultValues() {
 868     return array();
 869   }
 870
 871   function alterRow(&$row) {}
 872
 873   function setTitle($title) {
 874     if ($title) {
 875       CRM_Utils_System::setTitle($title);
 876     }
 877   }
 878
 879   /**
 880    * get entity id retrieve related data from db and move all data to detail table.
 881    *
 882    */
 883   function moveEntityToDetail($tableName) {
 884     $sql = NULL;
 885     switch ($tableName) {
 886       case 'Contact':
 887         $sql = "
 888 INSERT INTO {$this->_tableName}
 889 ( contact_id, sort_name, table_name )
 890 SELECT     c.id, c.sort_name, 'Contact'
 891   FROM     {$this->_entityIDTableName} ct
 892 INNER JOIN civicrm_contact c ON ct.entity_id = c.id
 893 {$this->_limitDetailClause}
 894 ";
 895         break;
 896
 897       case 'Activity':
 898         $sql = "
 899 INSERT INTO {$this->_tableName}
 900 ( table_name, activity_id, subject, details, contact_id, sort_name, record_type,
 901   activity_type_id, case_id, client_id )
 902 SELECT    'Activity', ca.id, substr(ca.subject, 1, 50), substr(ca.details, 1, 250),
 903            c1.id, c1.sort_name, cac.record_type_id,
 904            ca.activity_type_id,
 905            cca.case_id,
 906            ccc.contact_id as client_id
 907 FROM       {$this->_entityIDTableName} eid
 908 INNER JOIN civicrm_activity ca ON ca.id = eid.entity_id
 909 INNER JOIN  civicrm_activity_contact cac ON cac.activity_id = ca.id
 910 INNER JOIN  civicrm_contact c1 ON cac.contact_id = c1.id
 911 LEFT JOIN  civicrm_case_activity cca ON cca.activity_id = ca.id
 912 LEFT JOIN  civicrm_case_contact ccc ON ccc.case_id = cca.case_id
 913 WHERE (ca.is_deleted = 0 OR ca.is_deleted IS NULL)
 914 GROUP BY ca.id
 915 {$this->_limitDetailClause}
 916 ";
 917         break;
 918
 919       case 'Contribution':
 920         $sql = "
 921 INSERT INTO {$this->_tableName}
 922 ( table_name, contact_id, sort_name, contribution_id, financial_type, contribution_page, contribution_receive_date,
 923   contribution_total_amount, contribution_trxn_Id, contribution_source, contribution_status, contribution_check_number )
 924    SELECT  'Contribution', c.id, c.sort_name, cc.id, cct.name, ccp.title, cc.receive_date,
 925            cc.total_amount, cc.trxn_id, cc.source, contribution_status.label, cc.check_number
 926      FROM  {$this->_entityIDTableName} ct
 927 INNER JOIN civicrm_contribution cc ON cc.id = ct.entity_id
 928 LEFT JOIN  civicrm_contact c ON cc.contact_id = c.id
 929 LEFT JOIN  civicrm_financial_type cct ON cct.id = cc.financial_type_id
 930 LEFT JOIN  civicrm_contribution_page ccp ON ccp.id = cc.contribution_page_id
 931 LEFT JOIN  civicrm_option_group option_group_contributionStatus ON option_group_contributionStatus.name = 'contribution_status'
 932 LEFT JOIN  civicrm_option_value contribution_status ON
 933 ( contribution_status.option_group_id = option_group_contributionStatus.id AND contribution_status.value = cc.contribution_status_id )
 934 {$this->_limitDetailClause}
 935 ";
 936         break;
 937
 938       case 'Participant':
 939         $sql = "
 940 INSERT INTO {$this->_tableName}
 941 ( table_name, contact_id, sort_name, participant_id, event_title, participant_fee_level, participant_fee_amount,
 942 participant_register_date, participant_source, participant_status, participant_role )
 943    SELECT  'Participant', c.id, c.sort_name, cp.id, ce.title, cp.fee_level, cp.fee_amount, cp.register_date, cp.source,
 944            participantStatus.label, cp.role_id
 945      FROM  {$this->_entityIDTableName} ct
 946 INNER JOIN civicrm_participant cp ON cp.id = ct.entity_id
 947 LEFT JOIN  civicrm_contact c ON cp.contact_id = c.id
 948 LEFT JOIN  civicrm_event ce ON ce.id = cp.event_id
 949 LEFT JOIN  civicrm_participant_status_type participantStatus ON participantStatus.id = cp.status_id
 950 {$this->_limitDetailClause}
 951 ";
 952         break;
 953
 954       case 'Membership':
 955         $sql = "
 956 INSERT INTO {$this->_tableName}
 957 ( table_name, contact_id, sort_name, membership_id, membership_type, membership_fee, membership_start_date,
 958 membership_end_date, membership_source, membership_status )
 959    SELECT  'Membership', c.id, c.sort_name, cm.id, cmt.name, cc.total_amount, cm.start_date, cm.end_date, cm.source, cms.name
 960      FROM  {$this->_entityIDTableName} ct
 961 INNER JOIN civicrm_membership cm ON cm.id = ct.entity_id
 962 LEFT JOIN  civicrm_contact c ON cm.contact_id = c.id
 963 LEFT JOIN  civicrm_membership_type cmt ON cmt.id = cm.membership_type_id
 964 LEFT JOIN  civicrm_membership_payment cmp ON cmp.membership_id = cm.id
 965 LEFT JOIN  civicrm_contribution cc ON cc.id = cmp.contribution_id
 966 LEFT JOIN  civicrm_membership_status cms ON cms.id = cm.status_id
 967 {$this->_limitDetailClause}
 968 ";
 969         break;
 970
 971       case 'Case':
 972         $sql = "
 973 INSERT INTO {$this->_tableName}
 974 ( table_name, contact_id, sort_name, case_id, case_start_date, case_end_date, case_is_deleted )
 975 SELECT 'Case', c.id, c.sort_name, cc.id, DATE(cc.start_date), DATE(cc.end_date), cc.is_deleted
 976 FROM       {$this->_entityIDTableName} ct
 977 INNER JOIN civicrm_case cc ON cc.id = ct.entity_id
 978 LEFT JOIN  civicrm_case_contact ccc ON cc.id = ccc.case_id
 979 LEFT JOIN  civicrm_contact c ON ccc.contact_id = c.id
 980 {$this->_limitDetailClause}
 981 ";
 982         break;
 983
 984     }
 985
 986     if ($sql) {
 987       CRM_Core_DAO::executeQuery($sql);
 988     }
 989   }
 990 }
 991