3 +--------------------------------------------------------------------+
4 | CiviCRM version 4.5 |
5 +--------------------------------------------------------------------+
6 | Copyright CiviCRM LLC (c) 2004-2014 |
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-2014
35 class CRM_Contact_Form_Search_Custom_Group
36 extends CRM_Contact_Form_Search_Custom_Base
37 implements CRM_Contact_Form_Search_Interface
{
39 protected $_formValues;
41 protected $_tableName = NULL;
43 protected $_where = ' (1) ';
45 protected $_aclFrom = NULL;
46 protected $_aclWhere = NULL;
51 function __construct(&$formValues) {
52 $this->_formValues
= $formValues;
53 $this->_columns
= array(
54 ts('Contact Id') => 'contact_id',
55 ts('Contact Type') => 'contact_type',
56 ts('Name') => 'sort_name',
57 ts('Group Name') => 'gname',
58 ts('Tag Name') => 'tname',
61 $this->_includeGroups
= CRM_Utils_Array
::value('includeGroups', $this->_formValues
, array());
62 $this->_excludeGroups
= CRM_Utils_Array
::value('excludeGroups', $this->_formValues
, array());
63 $this->_includeTags
= CRM_Utils_Array
::value('includeTags', $this->_formValues
, array());
64 $this->_excludeTags
= CRM_Utils_Array
::value('excludeTags', $this->_formValues
, array());
67 $this->_allSearch
= FALSE;
68 $this->_groups
= FALSE;
70 $this->_andOr
= CRM_Utils_Array
::value('andOr', $this->_formValues
);
73 //make easy to check conditions for groups and tags are
74 //selected or it is empty search
75 if (empty($this->_includeGroups
) && empty($this->_excludeGroups
) &&
76 empty($this->_includeTags
) && empty($this->_excludeTags
)
79 $this->_allSearch
= TRUE;
82 $this->_groups
= (!empty($this->_includeGroups
) ||
!empty($this->_excludeGroups
));
84 $this->_tags
= (!empty($this->_includeTags
) ||
!empty($this->_excludeTags
));
87 function __destruct() {
88 // mysql drops the tables when connectiomn is terminated
89 // cannot drop tables here, since the search might be used
90 // in other parts after the object is destroyed
94 * @param CRM_Core_Form $form
96 function buildForm(&$form) {
98 $this->setTitle(ts('Include / Exclude Search'));
100 $groups = CRM_Core_PseudoConstant
::group();
102 $tags = CRM_Core_PseudoConstant
::get('CRM_Core_DAO_EntityTag', 'tag_id', array('onlyActive' => FALSE));
103 if (count($groups) == 0 ||
count($tags) == 0) {
104 CRM_Core_Session
::setStatus(ts("At least one Group and Tag must be present for Custom Group / Tag search."), ts('Missing Group/Tag'));
105 $url = CRM_Utils_System
::url('civicrm/contact/search/custom/list', 'reset=1');
106 CRM_Utils_System
::redirect($url);
109 $select2style = array(
111 'style' => 'width: 100%; max-width: 60em;',
112 'class' => 'crm-select2',
113 'placeholder' => ts('- select -'),
116 $form->add('select', 'includeGroups',
117 ts('Include Group(s)'),
123 $form->add('select', 'excludeGroups',
124 ts('Exclude Group(s)'),
131 '1' => ts('Show contacts that meet the Groups criteria AND the Tags criteria'),
132 '0' => ts('Show contacts that meet the Groups criteria OR the Tags criteria'),
134 $form->addRadio('andOr', ts('AND/OR'), $andOr, NULL, '<br />', TRUE);
136 $form->add('select', 'includeTags',
137 ts('Include Tag(s)'),
143 $form->add('select', 'excludeTags',
144 ts('Exclude Tag(s)'),
151 * if you are using the standard template, this array tells the template what elements
152 * are part of the search criteria
154 $form->assign('elements', array('includeGroups', 'excludeGroups', 'andOr', 'includeTags', 'excludeTags'));
158 * Set search form field defaults here.
163 function setDefaultValues() {
164 $defaults = array( 'andOr' => '1' );
166 if (!empty($this->_formValues
)) {
167 $defaults['andOr'] = CRM_Utils_Array
::value('andOr', $this->_formValues
, '1');
169 $defaults['includeGroups'] = CRM_Utils_Array
::value('includeGroups', $this->_formValues
);
170 $defaults['excludeGroups'] = CRM_Utils_Array
::value('excludeGroups', $this->_formValues
);
172 $defaults['includeTags'] = CRM_Utils_Array
::value('includeTags', $this->_formValues
);
173 $defaults['excludeTags'] = CRM_Utils_Array
::value('excludeTags', $this->_formValues
);
181 * @param int $rowcount
183 * @param bool $includeContactIDs
184 * @param bool $justIDs
189 $offset = 0, $rowcount = 0, $sort = NULL,
190 $includeContactIDs = FALSE, $justIDs = FALSE
194 $selectClause = "contact_a.id as contact_id";
197 $selectClause = "contact_a.id as contact_id,
198 contact_a.contact_type as contact_type,
199 contact_a.sort_name as sort_name";
201 //distinguish column according to user selection
202 if (($this->_includeGroups
&& !$this->_includeTags
)) {
203 unset($this->_columns
['Tag Name']);
204 $selectClause .= ", GROUP_CONCAT(DISTINCT group_names ORDER BY group_names ASC ) as gname";
206 elseif ($this->_includeTags
&& (!$this->_includeGroups
)) {
207 unset($this->_columns
['Group Name']);
208 $selectClause .= ", GROUP_CONCAT(DISTINCT tag_names ORDER BY tag_names ASC ) as tname";
210 elseif (!empty($this->_includeTags
) && !empty($this->_includeGroups
)) {
211 $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";
214 unset($this->_columns
['Tag Name']);
215 unset($this->_columns
['Group Name']);
219 $from = $this->from();
221 $where = $this->where($includeContactIDs);
223 if (!$justIDs && !$this->_allSearch
) {
224 $groupBy = " GROUP BY contact_a.id";
228 // we do this since this if stmt is called by the smart group part of the code
229 // adding a groupBy clause and saving it as a smart group messes up the query and
231 // andrew hunt seemed to have rewritten this piece when he worked on this search
235 $sql = "SELECT $selectClause $from WHERE $where $groupBy";
237 // Define ORDER BY for query in $sort, with default value
240 if (is_string($sort)) {
241 $sort = CRM_Utils_Type
::escape($sort, 'String');
242 $sql .= " ORDER BY $sort ";
245 $sql .= " ORDER BY " . trim($sort->orderBy());
249 $sql .= " ORDER BY contact_id ASC";
253 $sql .= " ORDER BY contact_a.id ASC";
256 if ($offset >= 0 && $rowcount > 0) {
257 $sql .= " LIMIT $offset, $rowcount ";
269 $iGroups = $xGroups = $iTags = $xTags = 0;
272 $randomNum = md5(uniqid());
273 $this->_tableName
= "civicrm_temp_custom_{$randomNum}";
275 //block for Group search
276 $smartGroup = array();
277 if ($this->_groups ||
$this->_allSearch
) {
278 $group = new CRM_Contact_DAO_Group();
279 $group->is_active
= 1;
281 while ($group->fetch()) {
282 $allGroups[] = $group->id
;
283 if ($group->saved_search_id
) {
284 $smartGroup[$group->saved_search_id
] = $group->id
;
287 $includedGroups = implode(',', $allGroups);
289 if (!empty($this->_includeGroups
)) {
290 $iGroups = implode(',', $this->_includeGroups
);
293 //if no group selected search for all groups
296 if (is_array($this->_excludeGroups
)) {
297 $xGroups = implode(',', $this->_excludeGroups
);
303 $sql = "CREATE TEMPORARY TABLE Xg_{$this->_tableName} ( contact_id int primary key) ENGINE=MyISAM";
304 CRM_Core_DAO
::executeQuery($sql);
306 //used only when exclude group is selected
308 $excludeGroup = "INSERT INTO Xg_{$this->_tableName} ( contact_id )
309 SELECT DISTINCT civicrm_group_contact.contact_id
310 FROM civicrm_group_contact, civicrm_contact
312 civicrm_contact.id = civicrm_group_contact.contact_id AND
313 civicrm_group_contact.status = 'Added' AND
314 civicrm_group_contact.group_id IN( {$xGroups})";
316 CRM_Core_DAO
::executeQuery($excludeGroup);
318 //search for smart group contacts
319 foreach ($this->_excludeGroups
as $keys => $values) {
320 if (in_array($values, $smartGroup)) {
321 $ssGroup = new CRM_Contact_DAO_Group();
322 $ssGroup->id
= $values;
323 if (!$ssGroup->find(TRUE)) {
324 CRM_Core_Error
::fatal();
326 CRM_Contact_BAO_GroupContactCache
::load($ssGroup);
329 SELECT gcc.contact_id
330 FROM civicrm_group_contact_cache gcc
331 WHERE gcc.group_id = {$ssGroup->id}
333 $smartGroupQuery = " INSERT IGNORE INTO Xg_{$this->_tableName}(contact_id) $smartSql";
334 CRM_Core_DAO
::executeQuery($smartGroupQuery);
339 $sql = "CREATE TEMPORARY TABLE Ig_{$this->_tableName} ( id int PRIMARY KEY AUTO_INCREMENT,
341 group_names varchar(64)) ENGINE=MyISAM";
343 CRM_Core_DAO
::executeQuery($sql);
346 $includeGroup = "INSERT INTO Ig_{$this->_tableName} (contact_id, group_names)
347 SELECT civicrm_contact.id as contact_id, civicrm_group.title as group_name
349 INNER JOIN civicrm_group_contact
350 ON civicrm_group_contact.contact_id = civicrm_contact.id
351 LEFT JOIN civicrm_group
352 ON civicrm_group_contact.group_id = civicrm_group.id";
355 $includeGroup = "INSERT INTO Ig_{$this->_tableName} (contact_id, group_names)
356 SELECT civicrm_contact.id as contact_id, ''
357 FROM civicrm_contact";
361 //used only when exclude group is selected
363 $includeGroup .= " LEFT JOIN Xg_{$this->_tableName}
364 ON civicrm_contact.id = Xg_{$this->_tableName}.contact_id";
368 $includeGroup .= " WHERE
369 civicrm_group_contact.status = 'Added' AND
370 civicrm_group_contact.group_id IN($iGroups)";
373 $includeGroup .= " WHERE ( 1 ) ";
376 //used only when exclude group is selected
378 $includeGroup .= " AND Xg_{$this->_tableName}.contact_id IS null";
381 CRM_Core_DAO
::executeQuery($includeGroup);
383 //search for smart group contacts
385 foreach ($this->_includeGroups
as $keys => $values) {
386 if (in_array($values, $smartGroup)) {
387 $ssGroup = new CRM_Contact_DAO_Group();
388 $ssGroup->id
= $values;
389 if (!$ssGroup->find(TRUE)) {
390 CRM_Core_Error
::fatal();
392 CRM_Contact_BAO_GroupContactCache
::load($ssGroup);
395 SELECT gcc.contact_id
396 FROM civicrm_group_contact_cache gcc
397 WHERE gcc.group_id = {$ssGroup->id}
400 //used only when exclude group is selected
402 $smartSql .= " AND gcc.contact_id NOT IN (SELECT contact_id FROM Xg_{$this->_tableName})";
405 $smartGroupQuery = " INSERT IGNORE INTO Ig_{$this->_tableName}(contact_id)
408 CRM_Core_DAO
::executeQuery($smartGroupQuery);
409 $insertGroupNameQuery = "UPDATE IGNORE Ig_{$this->_tableName}
410 SET group_names = (SELECT title FROM civicrm_group
411 WHERE civicrm_group.id = $values)
412 WHERE Ig_{$this->_tableName}.contact_id IS NOT NULL
413 AND Ig_{$this->_tableName}.group_names IS NULL";
414 CRM_Core_DAO
::executeQuery($insertGroupNameQuery);
418 //group contact search end here;
420 //block for Tags search
421 if ($this->_tags ||
$this->_allSearch
) {
423 $tag = new CRM_Core_DAO_Tag();
426 while ($tag->fetch()) {
427 $allTags[] = $tag->id
;
429 $includedTags = implode(',', $allTags);
431 if (!empty($this->_includeTags
)) {
432 $iTags = implode(',', $this->_includeTags
);
435 //if no group selected search for all groups
438 if (is_array($this->_excludeTags
)) {
439 $xTags = implode(',', $this->_excludeTags
);
445 $sql = "CREATE TEMPORARY TABLE Xt_{$this->_tableName} ( contact_id int primary key) ENGINE=MyISAM";
446 CRM_Core_DAO
::executeQuery($sql);
448 //used only when exclude tag is selected
450 $excludeTag = "INSERT INTO Xt_{$this->_tableName} ( contact_id )
451 SELECT DISTINCT civicrm_entity_tag.entity_id
452 FROM civicrm_entity_tag, civicrm_contact
454 civicrm_entity_tag.entity_table = 'civicrm_contact' AND
455 civicrm_contact.id = civicrm_entity_tag.entity_id AND
456 civicrm_entity_tag.tag_id IN( {$xTags})";
458 CRM_Core_DAO
::executeQuery($excludeTag);
461 $sql = "CREATE TEMPORARY TABLE It_{$this->_tableName} ( id int PRIMARY KEY AUTO_INCREMENT,
463 tag_names varchar(64)) ENGINE=MyISAM";
465 CRM_Core_DAO
::executeQuery($sql);
468 $includeTag = "INSERT INTO It_{$this->_tableName} (contact_id, tag_names)
469 SELECT civicrm_contact.id as contact_id, civicrm_tag.name as tag_name
471 INNER JOIN civicrm_entity_tag
472 ON ( civicrm_entity_tag.entity_table = 'civicrm_contact' AND
473 civicrm_entity_tag.entity_id = civicrm_contact.id )
474 LEFT JOIN civicrm_tag
475 ON civicrm_entity_tag.tag_id = civicrm_tag.id";
478 $includeTag = "INSERT INTO It_{$this->_tableName} (contact_id, tag_names)
479 SELECT civicrm_contact.id as contact_id, ''
480 FROM civicrm_contact";
483 //used only when exclude tag is selected
485 $includeTag .= " LEFT JOIN Xt_{$this->_tableName}
486 ON civicrm_contact.id = Xt_{$this->_tableName}.contact_id";
489 $includeTag .= " WHERE civicrm_entity_tag.tag_id IN($iTags)";
492 $includeTag .= " WHERE ( 1 ) ";
495 //used only when exclude tag is selected
497 $includeTag .= " AND Xt_{$this->_tableName}.contact_id IS null";
500 CRM_Core_DAO
::executeQuery($includeTag);
503 $from = " FROM civicrm_contact contact_a";
506 * CRM-10850 / CRM-10848
507 * If we use include / exclude groups as smart groups for ACL's having the below causes
508 * a cycle which messes things up. Hence commenting out for now
509 * $this->buildACLClause('contact_a');
513 * check the situation and set booleans
515 $Ig = ($iGroups != 0);
517 $Xg = ($xGroups != 0);
521 if (!$this->_groups
&& !$this->_tags
) {
526 * Set from statement depending on array sel
528 $whereitems = array();
529 foreach (array('Ig', 'It') as $inc) {
530 if ($this->_andOr
== 1) {
532 $from .= " INNER JOIN {$inc}_{$this->_tableName} temptable$inc ON (contact_a.id = temptable$inc.contact_id)";
537 $from .= " LEFT JOIN {$inc}_{$this->_tableName} temptable$inc ON (contact_a.id = temptable$inc.contact_id)";
541 $whereitems[] = "temptable$inc.contact_id IS NOT NULL";
544 $this->_where
= $whereitems ?
"(" . implode(' OR ', $whereitems) . ')' : '(1)';
545 foreach (array('Xg', 'Xt') as $exc) {
547 $from .= " LEFT JOIN {$exc}_{$this->_tableName} temptable$exc ON (contact_a.id = temptable$exc.contact_id)";
548 $this->_where
.= " AND temptable$exc.contact_id IS NULL";
552 $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}";
554 if ($this->_aclWhere
) {
555 $this->_where
.= " AND {$this->_aclWhere} ";
558 // also exclude all contacts that are deleted
560 $this->_where
.= " AND (contact_a.is_deleted != 1) ";
566 * @param bool $includeContactIDs
570 function where($includeContactIDs = FALSE) {
571 if ($includeContactIDs) {
572 $contactIDs = array();
574 foreach ($this->_formValues
as $id => $value) {
576 substr($id, 0, CRM_Core_Form
::CB_PREFIX_LEN
) == CRM_Core_Form
::CB_PREFIX
578 $contactIDs[] = substr($id, CRM_Core_Form
::CB_PREFIX_LEN
);
582 if (!empty($contactIDs)) {
583 $contactIDs = implode(', ', $contactIDs);
584 $clauses[] = "contact_a.id IN ( $contactIDs )";
586 $where = "{$this->_where} AND " . implode(' AND ', $clauses);
589 $where = $this->_where
;
596 * Functions below generally don't need to be modified
604 $dao = CRM_Core_DAO
::executeQuery($sql);
610 * @param int $rowcount
612 * @param bool $returnSQL
616 function contactIDs($offset = 0, $rowcount = 0, $sort = NULL, $returnSQL = FALSE) {
617 return $this->all($offset, $rowcount, $sort, FALSE, TRUE);
623 function &columns() {
624 return $this->_columns
;
637 function templateFile() {
638 return 'CRM/Contact/Form/Search/Custom.tpl';
644 function setTitle($title) {
646 CRM_Utils_System
::setTitle($title);
649 CRM_Utils_System
::setTitle(ts('Search'));
654 * @param string $tableAlias
656 function buildACLClause($tableAlias = 'contact') {
657 list($this->_aclFrom
, $this->_aclWhere
) = CRM_Contact_BAO_Contact_Permission
::cacheClause($tableAlias);