3 +--------------------------------------------------------------------+
5 +--------------------------------------------------------------------+
6 | Copyright CiviCRM LLC (c) 2004-2018 |
7 +--------------------------------------------------------------------+
8 | This file is a part of CiviCRM. |
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. |
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. |
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 +--------------------------------------------------------------------+
31 * @copyright CiviCRM LLC (c) 2004-2018
33 class CRM_Contact_Form_Search_Custom_Group
extends CRM_Contact_Form_Search_Custom_Base
implements CRM_Contact_Form_Search_Interface
{
35 protected $_formValues;
37 protected $_tableName = NULL;
39 protected $_where = ' (1) ';
41 protected $_aclFrom = NULL;
42 protected $_aclWhere = NULL;
47 * @param array $formValues
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',
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());
65 $this->_allSearch
= FALSE;
66 $this->_groups
= 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
)
75 $this->_allSearch
= TRUE;
78 $this->_groups
= (!empty($this->_includeGroups
) ||
!empty($this->_excludeGroups
));
80 $this->_tags
= (!empty($this->_includeTags
) ||
!empty($this->_excludeTags
));
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
90 * @param CRM_Core_Form $form
92 public function buildForm(&$form) {
94 $this->setTitle(ts('Include / Exclude Search'));
96 $groups = CRM_Core_PseudoConstant
::nestedGroup();
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);
105 $select2style = array(
107 'style' => 'width: 100%; max-width: 60em;',
108 'class' => 'crm-select2',
109 'placeholder' => ts('- select -'),
112 $form->add('select', 'includeGroups',
113 ts('Include Group(s)'),
119 $form->add('select', 'excludeGroups',
120 ts('Exclude Group(s)'),
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'),
130 $form->addRadio('andOr', ts('AND/OR'), $andOr, NULL, '<br />', TRUE);
132 $form->add('select', 'includeTags',
133 ts('Include Tag(s)'),
139 $form->add('select', 'excludeTags',
140 ts('Exclude Tag(s)'),
147 * if you are using the standard template, this array tells the template what elements
148 * are part of the search criteria
150 $form->assign('elements', array('includeGroups', 'excludeGroups', 'andOr', 'includeTags', 'excludeTags'));
155 * @param int $rowcount
157 * @param bool $includeContactIDs
158 * @param bool $justIDs
163 $offset = 0, $rowcount = 0, $sort = NULL,
164 $includeContactIDs = FALSE, $justIDs = FALSE
168 $selectClause = "contact_a.id as contact_id";
171 $selectClause = "contact_a.id as contact_id,
172 contact_a.contact_type as contact_type,
173 contact_a.sort_name as sort_name";
175 //distinguish column according to user selection
176 if (($this->_includeGroups
&& !$this->_includeTags
)) {
177 unset($this->_columns
[ts('Tag Name')]);
178 $selectClause .= ", GROUP_CONCAT(DISTINCT group_names ORDER BY group_names ASC ) as gname";
180 elseif ($this->_includeTags
&& (!$this->_includeGroups
)) {
181 unset($this->_columns
[ts('Group Name')]);
182 $selectClause .= ", GROUP_CONCAT(DISTINCT tag_names ORDER BY tag_names ASC ) as tname";
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";
188 unset($this->_columns
[ts('Tag Name')]);
189 unset($this->_columns
[ts('Group Name')]);
193 $from = $this->from();
195 $where = $this->where($includeContactIDs);
197 if (!$justIDs && !$this->_allSearch
) {
198 $groupBy = " GROUP BY contact_a.id";
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
205 // andrew hunt seemed to have rewritten this piece when he worked on this search
209 $sql = "SELECT $selectClause $from WHERE $where $groupBy";
211 // Define ORDER BY for query in $sort, with default value
214 if (is_string($sort)) {
215 $sort = CRM_Utils_Type
::escape($sort, 'String');
216 $sql .= " ORDER BY $sort ";
219 $sql .= " ORDER BY " . trim($sort->orderBy());
223 $sql .= " ORDER BY contact_id ASC";
227 $sql .= " ORDER BY contact_a.id ASC";
230 if ($offset >= 0 && $rowcount > 0) {
231 $sql .= " LIMIT $offset, $rowcount ";
241 public function from() {
243 $iGroups = $xGroups = $iTags = $xTags = 0;
246 $randomNum = md5(uniqid());
247 $this->_tableName
= "civicrm_temp_custom_{$randomNum}";
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;
255 while ($group->fetch()) {
256 $allGroups[] = $group->id
;
257 if ($group->saved_search_id
) {
258 $smartGroup[$group->saved_search_id
] = $group->id
;
261 $includedGroups = implode(',', $allGroups);
263 //CRM-15049 - Include child group ids.
264 $childGroupIds = CRM_Contact_BAO_Group
::getChildGroupIds($this->_includeGroups
);
265 if (count($childGroupIds) > 0) {
266 $this->_includeGroups
= array_merge($this->_includeGroups
, $childGroupIds);
269 if (!empty($this->_includeGroups
)) {
270 $iGroups = implode(',', $this->_includeGroups
);
273 //if no group selected search for all groups
276 if (is_array($this->_excludeGroups
)) {
277 $xGroups = implode(',', $this->_excludeGroups
);
283 $sql = "CREATE TEMPORARY TABLE Xg_{$this->_tableName} ( contact_id int primary key) ENGINE=InnoDB";
284 CRM_Core_DAO
::executeQuery($sql);
286 //used only when exclude group is selected
288 $excludeGroup = "INSERT INTO Xg_{$this->_tableName} ( contact_id )
289 SELECT DISTINCT civicrm_group_contact.contact_id
290 FROM civicrm_group_contact, civicrm_contact
292 civicrm_contact.id = civicrm_group_contact.contact_id AND
293 civicrm_group_contact.status = 'Added' AND
294 civicrm_group_contact.group_id IN( {$xGroups})";
296 CRM_Core_DAO
::executeQuery($excludeGroup);
298 //search for smart group contacts
299 foreach ($this->_excludeGroups
as $keys => $values) {
300 if (in_array($values, $smartGroup)) {
301 $ssGroup = new CRM_Contact_DAO_Group();
302 $ssGroup->id
= $values;
303 if (!$ssGroup->find(TRUE)) {
304 CRM_Core_Error
::fatal();
306 CRM_Contact_BAO_GroupContactCache
::load($ssGroup);
309 SELECT gcc.contact_id
310 FROM civicrm_group_contact_cache gcc
311 WHERE gcc.group_id = {$ssGroup->id}
313 $smartGroupQuery = " INSERT IGNORE INTO Xg_{$this->_tableName}(contact_id) $smartSql";
314 CRM_Core_DAO
::executeQuery($smartGroupQuery);
319 $sql = "CREATE TEMPORARY TABLE Ig_{$this->_tableName} ( id int PRIMARY KEY AUTO_INCREMENT,
321 group_names varchar(64)) ENGINE=InnoDB";
323 CRM_Core_DAO
::executeQuery($sql);
326 $includeGroup = "INSERT INTO Ig_{$this->_tableName} (contact_id, group_names)
327 SELECT civicrm_contact.id as contact_id, civicrm_group.title as group_name
329 INNER JOIN civicrm_group_contact
330 ON civicrm_group_contact.contact_id = civicrm_contact.id
331 LEFT JOIN civicrm_group
332 ON civicrm_group_contact.group_id = civicrm_group.id";
335 $includeGroup = "INSERT INTO Ig_{$this->_tableName} (contact_id, group_names)
336 SELECT civicrm_contact.id as contact_id, ''
337 FROM civicrm_contact";
339 //used only when exclude group is selected
341 $includeGroup .= " LEFT JOIN Xg_{$this->_tableName}
342 ON civicrm_contact.id = Xg_{$this->_tableName}.contact_id";
346 $includeGroup .= " WHERE
347 civicrm_group_contact.status = 'Added' AND
348 civicrm_group_contact.group_id IN($iGroups)";
351 $includeGroup .= " WHERE ( 1 ) ";
354 //used only when exclude group is selected
356 $includeGroup .= " AND Xg_{$this->_tableName}.contact_id IS null";
359 CRM_Core_DAO
::executeQuery($includeGroup);
361 //search for smart group contacts
363 foreach ($this->_includeGroups
as $keys => $values) {
364 if (in_array($values, $smartGroup)) {
365 $ssGroup = new CRM_Contact_DAO_Group();
366 $ssGroup->id
= $values;
367 if (!$ssGroup->find(TRUE)) {
368 CRM_Core_Error
::fatal();
370 CRM_Contact_BAO_GroupContactCache
::load($ssGroup);
373 SELECT gcc.contact_id
374 FROM civicrm_group_contact_cache gcc
375 WHERE gcc.group_id = {$ssGroup->id}
378 //used only when exclude group is selected
380 $smartSql .= " AND gcc.contact_id NOT IN (SELECT contact_id FROM Xg_{$this->_tableName})";
383 $smartGroupQuery = " INSERT IGNORE INTO Ig_{$this->_tableName}(contact_id)
386 CRM_Core_DAO
::executeQuery($smartGroupQuery);
387 $insertGroupNameQuery = "UPDATE IGNORE Ig_{$this->_tableName}
388 SET group_names = (SELECT title FROM civicrm_group
389 WHERE civicrm_group.id = $values)
390 WHERE Ig_{$this->_tableName}.contact_id IS NOT NULL
391 AND Ig_{$this->_tableName}.group_names IS NULL";
392 CRM_Core_DAO
::executeQuery($insertGroupNameQuery);
396 //group contact search end here;
398 //block for Tags search
399 if ($this->_tags ||
$this->_allSearch
) {
401 $tag = new CRM_Core_DAO_Tag();
404 while ($tag->fetch()) {
405 $allTags[] = $tag->id
;
407 $includedTags = implode(',', $allTags);
409 if (!empty($this->_includeTags
)) {
410 $iTags = implode(',', $this->_includeTags
);
413 //if no group selected search for all groups
416 if (is_array($this->_excludeTags
)) {
417 $xTags = implode(',', $this->_excludeTags
);
423 $sql = "CREATE TEMPORARY TABLE Xt_{$this->_tableName} ( contact_id int primary key) ENGINE=InnoDB";
424 CRM_Core_DAO
::executeQuery($sql);
426 //used only when exclude tag is selected
428 $excludeTag = "INSERT INTO Xt_{$this->_tableName} ( contact_id )
429 SELECT DISTINCT civicrm_entity_tag.entity_id
430 FROM civicrm_entity_tag, civicrm_contact
432 civicrm_entity_tag.entity_table = 'civicrm_contact' AND
433 civicrm_contact.id = civicrm_entity_tag.entity_id AND
434 civicrm_entity_tag.tag_id IN( {$xTags})";
436 CRM_Core_DAO
::executeQuery($excludeTag);
439 $sql = "CREATE TEMPORARY TABLE It_{$this->_tableName} ( id int PRIMARY KEY AUTO_INCREMENT,
441 tag_names varchar(64)) ENGINE=InnoDB";
443 CRM_Core_DAO
::executeQuery($sql);
446 $includeTag = "INSERT INTO It_{$this->_tableName} (contact_id, tag_names)
447 SELECT civicrm_contact.id as contact_id, civicrm_tag.name as tag_name
449 INNER JOIN civicrm_entity_tag
450 ON ( civicrm_entity_tag.entity_table = 'civicrm_contact' AND
451 civicrm_entity_tag.entity_id = civicrm_contact.id )
452 LEFT JOIN civicrm_tag
453 ON civicrm_entity_tag.tag_id = civicrm_tag.id";
456 $includeTag = "INSERT INTO It_{$this->_tableName} (contact_id, tag_names)
457 SELECT civicrm_contact.id as contact_id, ''
458 FROM civicrm_contact";
461 //used only when exclude tag is selected
463 $includeTag .= " LEFT JOIN Xt_{$this->_tableName}
464 ON civicrm_contact.id = Xt_{$this->_tableName}.contact_id";
467 $includeTag .= " WHERE civicrm_entity_tag.tag_id IN($iTags)";
470 $includeTag .= " WHERE ( 1 ) ";
473 //used only when exclude tag is selected
475 $includeTag .= " AND Xt_{$this->_tableName}.contact_id IS null";
478 CRM_Core_DAO
::executeQuery($includeTag);
481 $from = " FROM civicrm_contact contact_a";
484 * CRM-10850 / CRM-10848
485 * If we use include / exclude groups as smart groups for ACL's having the below causes
486 * a cycle which messes things up. Hence commenting out for now
487 * $this->buildACLClause('contact_a');
491 * check the situation and set booleans
493 $Ig = ($iGroups != 0);
495 $Xg = ($xGroups != 0);
499 if (!$this->_groups
&& !$this->_tags
) {
504 * Set from statement depending on array sel
506 $whereitems = array();
507 foreach (array('Ig', 'It') as $inc) {
508 if ($this->_andOr
== 1) {
510 $from .= " INNER JOIN {$inc}_{$this->_tableName} temptable$inc ON (contact_a.id = temptable$inc.contact_id)";
515 $from .= " LEFT JOIN {$inc}_{$this->_tableName} temptable$inc ON (contact_a.id = temptable$inc.contact_id)";
519 $whereitems[] = "temptable$inc.contact_id IS NOT NULL";
522 $this->_where
= $whereitems ?
"(" . implode(' OR ', $whereitems) . ')' : '(1)';
523 foreach (array('Xg', 'Xt') as $exc) {
525 $from .= " LEFT JOIN {$exc}_{$this->_tableName} temptable$exc ON (contact_a.id = temptable$exc.contact_id)";
526 $this->_where
.= " AND temptable$exc.contact_id IS NULL";
530 $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}";
532 if ($this->_aclWhere
) {
533 $this->_where
.= " AND {$this->_aclWhere} ";
536 // also exclude all contacts that are deleted
538 $this->_where
.= " AND (contact_a.is_deleted != 1) ";
544 * @param bool $includeContactIDs
548 public function where($includeContactIDs = FALSE) {
549 if ($includeContactIDs) {
550 $contactIDs = array();
552 foreach ($this->_formValues
as $id => $value) {
554 substr($id, 0, CRM_Core_Form
::CB_PREFIX_LEN
) == CRM_Core_Form
::CB_PREFIX
556 $contactIDs[] = substr($id, CRM_Core_Form
::CB_PREFIX_LEN
);
560 if (!empty($contactIDs)) {
561 $contactIDs = implode(', ', $contactIDs);
562 $clauses[] = "contact_a.id IN ( $contactIDs )";
564 $where = "{$this->_where} AND " . implode(' AND ', $clauses);
567 $where = $this->_where
;
574 * Functions below generally don't need to be modified
580 public function count() {
583 $dao = CRM_Core_DAO
::executeQuery($sql);
589 * @param int $rowcount
591 * @param bool $returnSQL
595 public function contactIDs($offset = 0, $rowcount = 0, $sort = NULL, $returnSQL = FALSE) {
596 return $this->all($offset, $rowcount, $sort, FALSE, TRUE);
604 public function &columns() {
605 return $this->_columns
;
613 public function summary() {
622 public function templateFile() {
623 return 'CRM/Contact/Form/Search/Custom.tpl';
627 * Set title on search.
629 * @param string $title
631 public function setTitle($title) {
633 CRM_Utils_System
::setTitle($title);
636 CRM_Utils_System
::setTitle(ts('Search'));
643 * @param string $tableAlias
645 public function buildACLClause($tableAlias = 'contact') {
646 list($this->_aclFrom
, $this->_aclWhere
) = CRM_Contact_BAO_Contact_Permission
::cacheClause($tableAlias);