crm.fsf.org/20131203/files/sites/all/modules-old/civicrm/CRM/Contact/Form/Search/Custom/Group.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 4.6                                                |
   5  +--------------------------------------------------------------------+
   6  | Copyright CiviCRM LLC (c) 2004-2015                                |
   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-2015
  32  * $Id$
  33  *
  34  */
  35 class CRM_Contact_Form_Search_Custom_Group extends CRM_Contact_Form_Search_Custom_Base implements CRM_Contact_Form_Search_Interface {
  36
  37   protected $_formValues;
  38
  39   protected $_tableName = NULL;
  40
  41   protected $_where = ' (1) ';
  42
  43   protected $_aclFrom = NULL;
  44   protected $_aclWhere = NULL;
  45
  46   /**
  47    * @param $formValues
  48    */
  49   public function __construct(&$formValues) {
  50     $this->_formValues = $formValues;
  51     $this->_columns = array(
  52       ts('Contact ID') => 'contact_id',
  53       ts('Contact Type') => 'contact_type',
  54       ts('Name') => 'sort_name',
  55       ts('Group Name') => 'gname',
  56       ts('Tag Name') => 'tname',
  57     );
  58
  59     $this->_includeGroups = CRM_Utils_Array::value('includeGroups', $this->_formValues, array());
  60     $this->_excludeGroups = CRM_Utils_Array::value('excludeGroups', $this->_formValues, array());
  61     $this->_includeTags = CRM_Utils_Array::value('includeTags', $this->_formValues, array());
  62     $this->_excludeTags = CRM_Utils_Array::value('excludeTags', $this->_formValues, array());
  63
  64     //define variables
  65     $this->_allSearch = FALSE;
  66     $this->_groups = FALSE;
  67     $this->_tags = FALSE;
  68     $this->_andOr = CRM_Utils_Array::value('andOr', $this->_formValues);
  69     //make easy to check conditions for groups and tags are
  70     //selected or it is empty search
  71     if (empty($this->_includeGroups) && empty($this->_excludeGroups) &&
  72       empty($this->_includeTags) && empty($this->_excludeTags)
  73     ) {
  74       //empty search
  75       $this->_allSearch = TRUE;
  76     }
  77
  78     $this->_groups = (!empty($this->_includeGroups) || !empty($this->_excludeGroups));
  79
  80     $this->_tags = (!empty($this->_includeTags) || !empty($this->_excludeTags));
  81   }
  82
  83   public function __destruct() {
  84     // mysql drops the tables when connection is terminated
  85     // cannot drop tables here, since the search might be used
  86     // in other parts after the object is destroyed
  87   }
  88
  89   /**
  90    * @param CRM_Core_Form $form
  91    */
  92   public function buildForm(&$form) {
  93
  94     $this->setTitle(ts('Include / Exclude Search'));
  95
  96     $groups = CRM_Core_PseudoConstant::nestedGroup();
  97
  98     $tags = CRM_Core_PseudoConstant::get('CRM_Core_DAO_EntityTag', 'tag_id', array('onlyActive' => FALSE));
  99     if (count($groups) == 0 || count($tags) == 0) {
 100       CRM_Core_Session::setStatus(ts("At least one Group and Tag must be present for Custom Group / Tag search."), ts('Missing Group/Tag'));
 101       $url = CRM_Utils_System::url('civicrm/contact/search/custom/list', 'reset=1');
 102       CRM_Utils_System::redirect($url);
 103     }
 104
 105     $select2style = array(
 106       'multiple' => TRUE,
 107       'style' => 'width: 100%; max-width: 60em;',
 108       'class' => 'crm-select2',
 109       'placeholder' => ts('- select -'),
 110     );
 111
 112     $form->add('select', 'includeGroups',
 113       ts('Include Group(s)'),
 114       $groups,
 115       FALSE,
 116       $select2style
 117     );
 118
 119     $form->add('select', 'excludeGroups',
 120       ts('Exclude Group(s)'),
 121       $groups,
 122       FALSE,
 123       $select2style
 124     );
 125
 126     $andOr = array(
 127       '1' => ts('Show contacts that meet the Groups criteria AND the Tags criteria'),
 128       '0' => ts('Show contacts that meet the Groups criteria OR  the Tags criteria'),
 129     );
 130     $form->addRadio('andOr', ts('AND/OR'), $andOr, NULL, '<br />', TRUE);
 131
 132     $form->add('select', 'includeTags',
 133       ts('Include Tag(s)'),
 134       $tags,
 135       FALSE,
 136       $select2style
 137     );
 138
 139     $form->add('select', 'excludeTags',
 140       ts('Exclude Tag(s)'),
 141       $tags,
 142       FALSE,
 143       $select2style
 144     );
 145
 146     /**
 147      * if you are using the standard template, this array tells the template what elements
 148      * are part of the search criteria
 149      */
 150     $form->assign('elements', array('includeGroups', 'excludeGroups', 'andOr', 'includeTags', 'excludeTags'));
 151   }
 152
 153   /**
 154    * @param int $offset
 155    * @param int $rowcount
 156    * @param NULL $sort
 157    * @param bool $includeContactIDs
 158    * @param bool $justIDs
 159    *
 160    * @return string
 161    */
 162   public function all(
 163     $offset = 0, $rowcount = 0, $sort = NULL,
 164     $includeContactIDs = FALSE, $justIDs = FALSE
 165   ) {
 166
 167     if ($justIDs) {
 168       $selectClause = "contact_a.id as contact_id";
 169     }
 170     else {
 171       $selectClause = "contact_a.id as contact_id,
 172                          contact_a.contact_type as contact_type,
 173                          contact_a.sort_name    as sort_name";
 174
 175       //distinguish column according to user selection
 176       if (($this->_includeGroups && !$this->_includeTags)) {
 177         unset($this->_columns['Tag Name']);
 178         $selectClause .= ", GROUP_CONCAT(DISTINCT group_names ORDER BY group_names ASC ) as gname";
 179       }
 180       elseif ($this->_includeTags && (!$this->_includeGroups)) {
 181         unset($this->_columns['Group Name']);
 182         $selectClause .= ", GROUP_CONCAT(DISTINCT tag_names  ORDER BY tag_names ASC ) as tname";
 183       }
 184       elseif (!empty($this->_includeTags) && !empty($this->_includeGroups)) {
 185         $selectClause .= ", GROUP_CONCAT(DISTINCT group_names ORDER BY group_names ASC ) as gname , GROUP_CONCAT(DISTINCT tag_names ORDER BY tag_names ASC ) as tname";
 186       }
 187       else {
 188         unset($this->_columns['Tag Name']);
 189         unset($this->_columns['Group Name']);
 190       }
 191     }
 192
 193     $from = $this->from();
 194
 195     $where = $this->where($includeContactIDs);
 196
 197     if (!$justIDs && !$this->_allSearch) {
 198       $groupBy = " GROUP BY contact_a.id";
 199     }
 200     else {
 201       // CRM-10850
 202       // we do this since this if stmt is called by the smart group part of the code
 203       // adding a groupBy clause and saving it as a smart group messes up the query and
 204       // bad things happen
 205       // andrew hunt seemed to have rewritten this piece when he worked on this search
 206       $groupBy = NULL;
 207     }
 208
 209     $sql = "SELECT $selectClause $from WHERE  $where $groupBy";
 210
 211     // Define ORDER BY for query in $sort, with default value
 212     if (!$justIDs) {
 213       if (!empty($sort)) {
 214         if (is_string($sort)) {
 215           $sort = CRM_Utils_Type::escape($sort, 'String');
 216           $sql .= " ORDER BY $sort ";
 217         }
 218         else {
 219           $sql .= " ORDER BY " . trim($sort->orderBy());
 220         }
 221       }
 222       else {
 223         $sql .= " ORDER BY contact_id ASC";
 224       }
 225     }
 226     else {
 227       $sql .= " ORDER BY contact_a.id ASC";
 228     }
 229
 230     if ($offset >= 0 && $rowcount > 0) {
 231       $sql .= " LIMIT $offset, $rowcount ";
 232     }
 233
 234     return $sql;
 235   }
 236
 237   /**
 238    * @return string
 239    * @throws Exception
 240    */
 241   public function from() {
 242
 243     $iGroups = $xGroups = $iTags = $xTags = 0;
 244
 245     //define table name
 246     $randomNum = md5(uniqid());
 247     $this->_tableName = "civicrm_temp_custom_{$randomNum}";
 248
 249     //block for Group search
 250     $smartGroup = array();
 251     if ($this->_groups || $this->_allSearch) {
 252       $group = new CRM_Contact_DAO_Group();
 253       $group->is_active = 1;
 254       $group->find();
 255       while ($group->fetch()) {
 256         $allGroups[] = $group->id;
 257         if ($group->saved_search_id) {
 258           $smartGroup[$group->saved_search_id] = $group->id;
 259         }
 260       }
 261       $includedGroups = implode(',', $allGroups);
 262
 263       if (!empty($this->_includeGroups)) {
 264         $iGroups = implode(',', $this->_includeGroups);
 265       }
 266       else {
 267         //if no group selected search for all groups
 268         $iGroups = NULL;
 269       }
 270       if (is_array($this->_excludeGroups)) {
 271         $xGroups = implode(',', $this->_excludeGroups);
 272       }
 273       else {
 274         $xGroups = 0;
 275       }
 276
 277       $sql = "CREATE TEMPORARY TABLE Xg_{$this->_tableName} ( contact_id int primary key) ENGINE=MyISAM";
 278       CRM_Core_DAO::executeQuery($sql);
 279
 280       //used only when exclude group is selected
 281       if ($xGroups != 0) {
 282         $excludeGroup = "INSERT INTO  Xg_{$this->_tableName} ( contact_id )
 283                   SELECT  DISTINCT civicrm_group_contact.contact_id
 284                   FROM civicrm_group_contact, civicrm_contact
 285                   WHERE
 286                      civicrm_contact.id = civicrm_group_contact.contact_id AND
 287                      civicrm_group_contact.status = 'Added' AND
 288                      civicrm_group_contact.group_id IN( {$xGroups})";
 289
 290         CRM_Core_DAO::executeQuery($excludeGroup);
 291
 292         //search for smart group contacts
 293         foreach ($this->_excludeGroups as $keys => $values) {
 294           if (in_array($values, $smartGroup)) {
 295             $ssGroup = new CRM_Contact_DAO_Group();
 296             $ssGroup->id = $values;
 297             if (!$ssGroup->find(TRUE)) {
 298               CRM_Core_Error::fatal();
 299             }
 300             CRM_Contact_BAO_GroupContactCache::load($ssGroup);
 301
 302             $smartSql = "
 303 SELECT gcc.contact_id
 304 FROM   civicrm_group_contact_cache gcc
 305 WHERE  gcc.group_id = {$ssGroup->id}
 306 ";
 307             $smartGroupQuery = " INSERT IGNORE INTO Xg_{$this->_tableName}(contact_id) $smartSql";
 308             CRM_Core_DAO::executeQuery($smartGroupQuery);
 309           }
 310         }
 311       }
 312
 313       $sql = "CREATE TEMPORARY TABLE Ig_{$this->_tableName} ( id int PRIMARY KEY AUTO_INCREMENT,
 314                                                                    contact_id int,
 315                                                                    group_names varchar(64)) ENGINE=MyISAM";
 316
 317       CRM_Core_DAO::executeQuery($sql);
 318
 319       if ($iGroups) {
 320         $includeGroup = "INSERT INTO Ig_{$this->_tableName} (contact_id, group_names)
 321                  SELECT              civicrm_contact.id as contact_id, civicrm_group.title as group_name
 322                  FROM                civicrm_contact
 323                     INNER JOIN       civicrm_group_contact
 324                             ON       civicrm_group_contact.contact_id = civicrm_contact.id
 325                     LEFT JOIN        civicrm_group
 326                             ON       civicrm_group_contact.group_id = civicrm_group.id";
 327       }
 328       else {
 329         $includeGroup = "INSERT INTO Ig_{$this->_tableName} (contact_id, group_names)
 330                  SELECT              civicrm_contact.id as contact_id, ''
 331                  FROM                civicrm_contact";
 332       }
 333       //used only when exclude group is selected
 334       if ($xGroups != 0) {
 335         $includeGroup .= " LEFT JOIN        Xg_{$this->_tableName}
 336                                           ON       civicrm_contact.id = Xg_{$this->_tableName}.contact_id";
 337       }
 338
 339       if ($iGroups) {
 340         $includeGroup .= " WHERE
 341                                      civicrm_group_contact.status = 'Added'  AND
 342                                      civicrm_group_contact.group_id IN($iGroups)";
 343       }
 344       else {
 345         $includeGroup .= " WHERE ( 1 ) ";
 346       }
 347
 348       //used only when exclude group is selected
 349       if ($xGroups != 0) {
 350         $includeGroup .= " AND  Xg_{$this->_tableName}.contact_id IS null";
 351       }
 352
 353       CRM_Core_DAO::executeQuery($includeGroup);
 354
 355       //search for smart group contacts
 356
 357       foreach ($this->_includeGroups as $keys => $values) {
 358         if (in_array($values, $smartGroup)) {
 359           $ssGroup = new CRM_Contact_DAO_Group();
 360           $ssGroup->id = $values;
 361           if (!$ssGroup->find(TRUE)) {
 362             CRM_Core_Error::fatal();
 363           }
 364           CRM_Contact_BAO_GroupContactCache::load($ssGroup);
 365
 366           $smartSql = "
 367 SELECT gcc.contact_id
 368 FROM   civicrm_group_contact_cache gcc
 369 WHERE  gcc.group_id = {$ssGroup->id}
 370 ";
 371
 372           //used only when exclude group is selected
 373           if ($xGroups != 0) {
 374             $smartSql .= " AND gcc.contact_id NOT IN (SELECT contact_id FROM  Xg_{$this->_tableName})";
 375           }
 376
 377           $smartGroupQuery = " INSERT IGNORE INTO Ig_{$this->_tableName}(contact_id)
 378                                      $smartSql";
 379
 380           CRM_Core_DAO::executeQuery($smartGroupQuery);
 381           $insertGroupNameQuery = "UPDATE IGNORE Ig_{$this->_tableName}
 382                                          SET group_names = (SELECT title FROM civicrm_group
 383                                                             WHERE civicrm_group.id = $values)
 384                                          WHERE Ig_{$this->_tableName}.contact_id IS NOT NULL
 385                                          AND Ig_{$this->_tableName}.group_names IS NULL";
 386           CRM_Core_DAO::executeQuery($insertGroupNameQuery);
 387         }
 388       }
 389     }
 390     //group contact search end here;
 391
 392     //block for Tags search
 393     if ($this->_tags || $this->_allSearch) {
 394       //find all tags
 395       $tag = new CRM_Core_DAO_Tag();
 396       $tag->is_active = 1;
 397       $tag->find();
 398       while ($tag->fetch()) {
 399         $allTags[] = $tag->id;
 400       }
 401       $includedTags = implode(',', $allTags);
 402
 403       if (!empty($this->_includeTags)) {
 404         $iTags = implode(',', $this->_includeTags);
 405       }
 406       else {
 407         //if no group selected search for all groups
 408         $iTags = NULL;
 409       }
 410       if (is_array($this->_excludeTags)) {
 411         $xTags = implode(',', $this->_excludeTags);
 412       }
 413       else {
 414         $xTags = 0;
 415       }
 416
 417       $sql = "CREATE TEMPORARY TABLE Xt_{$this->_tableName} ( contact_id int primary key) ENGINE=MyISAM";
 418       CRM_Core_DAO::executeQuery($sql);
 419
 420       //used only when exclude tag is selected
 421       if ($xTags != 0) {
 422         $excludeTag = "INSERT INTO  Xt_{$this->_tableName} ( contact_id )
 423                   SELECT  DISTINCT civicrm_entity_tag.entity_id
 424                   FROM civicrm_entity_tag, civicrm_contact
 425                   WHERE
 426                      civicrm_entity_tag.entity_table = 'civicrm_contact' AND
 427                      civicrm_contact.id = civicrm_entity_tag.entity_id AND
 428                      civicrm_entity_tag.tag_id IN( {$xTags})";
 429
 430         CRM_Core_DAO::executeQuery($excludeTag);
 431       }
 432
 433       $sql = "CREATE TEMPORARY TABLE It_{$this->_tableName} ( id int PRIMARY KEY AUTO_INCREMENT,
 434                                                                contact_id int,
 435                                                                tag_names varchar(64)) ENGINE=MyISAM";
 436
 437       CRM_Core_DAO::executeQuery($sql);
 438
 439       if ($iTags) {
 440         $includeTag = "INSERT INTO It_{$this->_tableName} (contact_id, tag_names)
 441                  SELECT              civicrm_contact.id as contact_id, civicrm_tag.name as tag_name
 442                  FROM                civicrm_contact
 443                     INNER JOIN       civicrm_entity_tag
 444                             ON       ( civicrm_entity_tag.entity_table = 'civicrm_contact' AND
 445                                        civicrm_entity_tag.entity_id = civicrm_contact.id )
 446                     LEFT JOIN        civicrm_tag
 447                             ON       civicrm_entity_tag.tag_id = civicrm_tag.id";
 448       }
 449       else {
 450         $includeTag = "INSERT INTO It_{$this->_tableName} (contact_id, tag_names)
 451                  SELECT              civicrm_contact.id as contact_id, ''
 452                  FROM                civicrm_contact";
 453       }
 454
 455       //used only when exclude tag is selected
 456       if ($xTags != 0) {
 457         $includeTag .= " LEFT JOIN        Xt_{$this->_tableName}
 458                                        ON       civicrm_contact.id = Xt_{$this->_tableName}.contact_id";
 459       }
 460       if ($iTags) {
 461         $includeTag .= " WHERE   civicrm_entity_tag.tag_id IN($iTags)";
 462       }
 463       else {
 464         $includeTag .= " WHERE ( 1 ) ";
 465       }
 466
 467       //used only when exclude tag is selected
 468       if ($xTags != 0) {
 469         $includeTag .= " AND  Xt_{$this->_tableName}.contact_id IS null";
 470       }
 471
 472       CRM_Core_DAO::executeQuery($includeTag);
 473     }
 474
 475     $from = " FROM civicrm_contact contact_a";
 476
 477     /*
 478      * CRM-10850 / CRM-10848
 479      * If we use include / exclude groups as smart groups for ACL's having the below causes
 480      * a cycle which messes things up. Hence commenting out for now
 481      * $this->buildACLClause('contact_a');
 482      */
 483
 484     /*
 485      * check the situation and set booleans
 486      */
 487     $Ig = ($iGroups != 0);
 488     $It = ($iTags != 0);
 489     $Xg = ($xGroups != 0);
 490     $Xt = ($xTags != 0);
 491
 492     //PICK UP FROM HERE
 493     if (!$this->_groups && !$this->_tags) {
 494       $this->_andOr = 1;
 495     }
 496
 497     /*
 498      * Set from statement depending on array sel
 499      */
 500     $whereitems = array();
 501     foreach (array('Ig', 'It') as $inc) {
 502       if ($this->_andOr == 1) {
 503         if ($$inc) {
 504           $from .= " INNER JOIN {$inc}_{$this->_tableName} temptable$inc ON (contact_a.id = temptable$inc.contact_id)";
 505         }
 506       }
 507       else {
 508         if ($$inc) {
 509           $from .= " LEFT JOIN {$inc}_{$this->_tableName} temptable$inc ON (contact_a.id = temptable$inc.contact_id)";
 510         }
 511       }
 512       if ($$inc) {
 513         $whereitems[] = "temptable$inc.contact_id IS NOT NULL";
 514       }
 515     }
 516     $this->_where = $whereitems ? "(" . implode(' OR ', $whereitems) . ')' : '(1)';
 517     foreach (array('Xg', 'Xt') as $exc) {
 518       if ($$exc) {
 519         $from .= " LEFT JOIN {$exc}_{$this->_tableName} temptable$exc ON (contact_a.id = temptable$exc.contact_id)";
 520         $this->_where .= " AND temptable$exc.contact_id IS NULL";
 521       }
 522     }
 523
 524     $from .= " LEFT JOIN civicrm_email ON ( contact_a.id = civicrm_email.contact_id AND ( civicrm_email.is_primary = 1 OR civicrm_email.is_bulkmail = 1 ) ) {$this->_aclFrom}";
 525
 526     if ($this->_aclWhere) {
 527       $this->_where .= " AND {$this->_aclWhere} ";
 528     }
 529
 530     // also exclude all contacts that are deleted
 531     // CRM-11627
 532     $this->_where .= " AND (contact_a.is_deleted != 1) ";
 533
 534     return $from;
 535   }
 536
 537   /**
 538    * @param bool $includeContactIDs
 539    *
 540    * @return string
 541    */
 542   public function where($includeContactIDs = FALSE) {
 543     if ($includeContactIDs) {
 544       $contactIDs = array();
 545
 546       foreach ($this->_formValues as $id => $value) {
 547         if ($value &&
 548           substr($id, 0, CRM_Core_Form::CB_PREFIX_LEN) == CRM_Core_Form::CB_PREFIX
 549         ) {
 550           $contactIDs[] = substr($id, CRM_Core_Form::CB_PREFIX_LEN);
 551         }
 552       }
 553
 554       if (!empty($contactIDs)) {
 555         $contactIDs = implode(', ', $contactIDs);
 556         $clauses[] = "contact_a.id IN ( $contactIDs )";
 557       }
 558       $where = "{$this->_where} AND " . implode(' AND ', $clauses);
 559     }
 560     else {
 561       $where = $this->_where;
 562     }
 563
 564     return $where;
 565   }
 566
 567   /*
 568    * Functions below generally don't need to be modified
 569    */
 570
 571   /**
 572    * @inheritDoc
 573    */
 574   public function count() {
 575     $sql = $this->all();
 576
 577     $dao = CRM_Core_DAO::executeQuery($sql);
 578     return $dao->N;
 579   }
 580
 581   /**
 582    * @param int $offset
 583    * @param int $rowcount
 584    * @param NULL $sort
 585    * @param bool $returnSQL
 586    *
 587    * @return string
 588    */
 589   public function contactIDs($offset = 0, $rowcount = 0, $sort = NULL, $returnSQL = FALSE) {
 590     return $this->all($offset, $rowcount, $sort, FALSE, TRUE);
 591   }
 592
 593   /**
 594    * @return array
 595    */
 596   public function &columns() {
 597     return $this->_columns;
 598   }
 599
 600   /**
 601    * @return NULL
 602    */
 603   public function summary() {
 604     return NULL;
 605   }
 606
 607   /**
 608    * @return string
 609    */
 610   public function templateFile() {
 611     return 'CRM/Contact/Form/Search/Custom.tpl';
 612   }
 613
 614   /**
 615    * @param $title
 616    */
 617   public function setTitle($title) {
 618     if ($title) {
 619       CRM_Utils_System::setTitle($title);
 620     }
 621     else {
 622       CRM_Utils_System::setTitle(ts('Search'));
 623     }
 624   }
 625
 626   /**
 627    * @param string $tableAlias
 628    */
 629   public function buildACLClause($tableAlias = 'contact') {
 630     list($this->_aclFrom, $this->_aclWhere) = CRM_Contact_BAO_Contact_Permission::cacheClause($tableAlias);
 631   }
 632
 633 }