3 +--------------------------------------------------------------------+
4 | CiviCRM version 4.7 |
5 +--------------------------------------------------------------------+
6 | Copyright CiviCRM LLC (c) 2004-2017 |
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-2017
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 if (!empty($this->_includeGroups
)) {
264 $iGroups = implode(',', $this->_includeGroups
);
267 //if no group selected search for all groups
270 if (is_array($this->_excludeGroups
)) {
271 $xGroups = implode(',', $this->_excludeGroups
);
277 $sql = "CREATE TEMPORARY TABLE Xg_{$this->_tableName} ( contact_id int primary key) ENGINE=InnoDB";
278 CRM_Core_DAO
::executeQuery($sql);
280 //used only when exclude group is selected
282 $excludeGroup = "INSERT INTO Xg_{$this->_tableName} ( contact_id )
283 SELECT DISTINCT civicrm_group_contact.contact_id
284 FROM civicrm_group_contact, civicrm_contact
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})";
290 CRM_Core_DAO
::executeQuery($excludeGroup);
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();
300 CRM_Contact_BAO_GroupContactCache
::load($ssGroup);
303 SELECT gcc.contact_id
304 FROM civicrm_group_contact_cache gcc
305 WHERE gcc.group_id = {$ssGroup->id}
307 $smartGroupQuery = " INSERT IGNORE INTO Xg_{$this->_tableName}(contact_id) $smartSql";
308 CRM_Core_DAO
::executeQuery($smartGroupQuery);
313 $sql = "CREATE TEMPORARY TABLE Ig_{$this->_tableName} ( id int PRIMARY KEY AUTO_INCREMENT,
315 group_names varchar(64)) ENGINE=InnoDB";
317 CRM_Core_DAO
::executeQuery($sql);
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
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";
329 $includeGroup = "INSERT INTO Ig_{$this->_tableName} (contact_id, group_names)
330 SELECT civicrm_contact.id as contact_id, ''
331 FROM civicrm_contact";
333 //used only when exclude group is selected
335 $includeGroup .= " LEFT JOIN Xg_{$this->_tableName}
336 ON civicrm_contact.id = Xg_{$this->_tableName}.contact_id";
340 $includeGroup .= " WHERE
341 civicrm_group_contact.status = 'Added' AND
342 civicrm_group_contact.group_id IN($iGroups)";
345 $includeGroup .= " WHERE ( 1 ) ";
348 //used only when exclude group is selected
350 $includeGroup .= " AND Xg_{$this->_tableName}.contact_id IS null";
353 CRM_Core_DAO
::executeQuery($includeGroup);
355 //search for smart group contacts
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();
364 CRM_Contact_BAO_GroupContactCache
::load($ssGroup);
367 SELECT gcc.contact_id
368 FROM civicrm_group_contact_cache gcc
369 WHERE gcc.group_id = {$ssGroup->id}
372 //used only when exclude group is selected
374 $smartSql .= " AND gcc.contact_id NOT IN (SELECT contact_id FROM Xg_{$this->_tableName})";
377 $smartGroupQuery = " INSERT IGNORE INTO Ig_{$this->_tableName}(contact_id)
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);
390 //group contact search end here;
392 //block for Tags search
393 if ($this->_tags ||
$this->_allSearch
) {
395 $tag = new CRM_Core_DAO_Tag();
398 while ($tag->fetch()) {
399 $allTags[] = $tag->id
;
401 $includedTags = implode(',', $allTags);
403 if (!empty($this->_includeTags
)) {
404 $iTags = implode(',', $this->_includeTags
);
407 //if no group selected search for all groups
410 if (is_array($this->_excludeTags
)) {
411 $xTags = implode(',', $this->_excludeTags
);
417 $sql = "CREATE TEMPORARY TABLE Xt_{$this->_tableName} ( contact_id int primary key) ENGINE=InnoDB";
418 CRM_Core_DAO
::executeQuery($sql);
420 //used only when exclude tag is selected
422 $excludeTag = "INSERT INTO Xt_{$this->_tableName} ( contact_id )
423 SELECT DISTINCT civicrm_entity_tag.entity_id
424 FROM civicrm_entity_tag, civicrm_contact
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})";
430 CRM_Core_DAO
::executeQuery($excludeTag);
433 $sql = "CREATE TEMPORARY TABLE It_{$this->_tableName} ( id int PRIMARY KEY AUTO_INCREMENT,
435 tag_names varchar(64)) ENGINE=InnoDB";
437 CRM_Core_DAO
::executeQuery($sql);
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
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";
450 $includeTag = "INSERT INTO It_{$this->_tableName} (contact_id, tag_names)
451 SELECT civicrm_contact.id as contact_id, ''
452 FROM civicrm_contact";
455 //used only when exclude tag is selected
457 $includeTag .= " LEFT JOIN Xt_{$this->_tableName}
458 ON civicrm_contact.id = Xt_{$this->_tableName}.contact_id";
461 $includeTag .= " WHERE civicrm_entity_tag.tag_id IN($iTags)";
464 $includeTag .= " WHERE ( 1 ) ";
467 //used only when exclude tag is selected
469 $includeTag .= " AND Xt_{$this->_tableName}.contact_id IS null";
472 CRM_Core_DAO
::executeQuery($includeTag);
475 $from = " FROM civicrm_contact contact_a";
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');
485 * check the situation and set booleans
487 $Ig = ($iGroups != 0);
489 $Xg = ($xGroups != 0);
493 if (!$this->_groups
&& !$this->_tags
) {
498 * Set from statement depending on array sel
500 $whereitems = array();
501 foreach (array('Ig', 'It') as $inc) {
502 if ($this->_andOr
== 1) {
504 $from .= " INNER JOIN {$inc}_{$this->_tableName} temptable$inc ON (contact_a.id = temptable$inc.contact_id)";
509 $from .= " LEFT JOIN {$inc}_{$this->_tableName} temptable$inc ON (contact_a.id = temptable$inc.contact_id)";
513 $whereitems[] = "temptable$inc.contact_id IS NOT NULL";
516 $this->_where
= $whereitems ?
"(" . implode(' OR ', $whereitems) . ')' : '(1)';
517 foreach (array('Xg', 'Xt') as $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";
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}";
526 if ($this->_aclWhere
) {
527 $this->_where
.= " AND {$this->_aclWhere} ";
530 // also exclude all contacts that are deleted
532 $this->_where
.= " AND (contact_a.is_deleted != 1) ";
538 * @param bool $includeContactIDs
542 public function where($includeContactIDs = FALSE) {
543 if ($includeContactIDs) {
544 $contactIDs = array();
546 foreach ($this->_formValues
as $id => $value) {
548 substr($id, 0, CRM_Core_Form
::CB_PREFIX_LEN
) == CRM_Core_Form
::CB_PREFIX
550 $contactIDs[] = substr($id, CRM_Core_Form
::CB_PREFIX_LEN
);
554 if (!empty($contactIDs)) {
555 $contactIDs = implode(', ', $contactIDs);
556 $clauses[] = "contact_a.id IN ( $contactIDs )";
558 $where = "{$this->_where} AND " . implode(' AND ', $clauses);
561 $where = $this->_where
;
568 * Functions below generally don't need to be modified
574 public function count() {
577 $dao = CRM_Core_DAO
::executeQuery($sql);
583 * @param int $rowcount
585 * @param bool $returnSQL
589 public function contactIDs($offset = 0, $rowcount = 0, $sort = NULL, $returnSQL = FALSE) {
590 return $this->all($offset, $rowcount, $sort, FALSE, TRUE);
598 public function &columns() {
599 return $this->_columns
;
607 public function summary() {
616 public function templateFile() {
617 return 'CRM/Contact/Form/Search/Custom.tpl';
621 * Set title on search.
623 * @param string $title
625 public function setTitle($title) {
627 CRM_Utils_System
::setTitle($title);
630 CRM_Utils_System
::setTitle(ts('Search'));
637 * @param string $tableAlias
639 public function buildACLClause($tableAlias = 'contact') {
640 list($this->_aclFrom
, $this->_aclWhere
) = CRM_Contact_BAO_Contact_Permission
::cacheClause($tableAlias);