3 +--------------------------------------------------------------------+
4 | CiviCRM version 4.7 |
5 +--------------------------------------------------------------------+
6 | Copyright CiviCRM LLC (c) 2004-2016 |
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-2016
33 class CRM_Contact_Form_Search_Custom_DateAdded
extends CRM_Contact_Form_Search_Custom_Base
implements CRM_Contact_Form_Search_Interface
{
35 protected $_debug = 0;
36 protected $_aclFrom = NULL;
37 protected $_aclWhere = NULL;
42 * @param array $formValues
44 public function __construct(&$formValues) {
45 parent
::__construct($formValues);
47 $this->_includeGroups
= CRM_Utils_Array
::value('includeGroups', $formValues, array());
48 $this->_excludeGroups
= CRM_Utils_Array
::value('excludeGroups', $formValues, array());
50 $this->_columns
= array(
51 ts('Contact ID') => 'contact_id',
52 ts('Contact Type') => 'contact_type',
53 ts('Name') => 'sort_name',
54 ts('Date Added') => 'date_added',
59 * @param CRM_Core_Form $form
61 public function buildForm(&$form) {
62 $form->addDate('start_date', ts('Start Date'), FALSE, array('formatType' => 'custom'));
63 $form->addDate('end_date', ts('End Date'), FALSE, array('formatType' => 'custom'));
65 $groups = CRM_Core_PseudoConstant
::nestedGroup();
67 $select2style = array(
69 'style' => 'width: 100%; max-width: 60em;',
70 'class' => 'crm-select2',
71 'placeholder' => ts('- select -'),
74 $form->add('select', 'includeGroups',
75 ts('Include Group(s)'),
81 $form->add('select', 'excludeGroups',
82 ts('Exclude Group(s)'),
88 $this->setTitle('Search by date added to CiviCRM');
90 //redirect if group not available for search criteria
91 if (count($groups) == 0) {
92 CRM_Core_Error
::statusBounce(ts("Atleast one Group must be present for search."),
93 CRM_Utils_System
::url('civicrm/contact/search/custom/list',
100 * if you are using the standard template, this array tells the template what elements
101 * are part of the search criteria
103 $form->assign('elements', array('start_date', 'end_date', 'includeGroups', 'excludeGroups'));
109 public function summary() {
115 * @param int $rowcount
117 * @param bool $returnSQL
121 public function contactIDs($offset = 0, $rowcount = 0, $sort = NULL, $returnSQL = FALSE) {
122 return $this->all($offset, $rowcount, $sort, FALSE, TRUE);
127 * @param int $rowcount
129 * @param bool $includeContactIDs
130 * @param bool $justIDs
135 $offset = 0, $rowcount = 0, $sort = NULL,
136 $includeContactIDs = FALSE, $justIDs = FALSE
139 $this->_includeGroups
= CRM_Utils_Array
::value('includeGroups', $this->_formValues
, array());
141 $this->_excludeGroups
= CRM_Utils_Array
::value('excludeGroups', $this->_formValues
, array());
143 $this->_allSearch
= FALSE;
144 $this->_groups
= FALSE;
146 if (empty($this->_includeGroups
) && empty($this->_excludeGroups
)) {
148 $this->_allSearch
= TRUE;
151 if (!empty($this->_includeGroups
) ||
!empty($this->_excludeGroups
)) {
153 $this->_groups
= TRUE;
157 $selectClause = "contact_a.id as contact_id";
158 $groupBy = " GROUP BY contact_a.id";
159 $sort = "contact_a.id";
162 $selectClause = "contact_a.id as contact_id,
163 contact_a.contact_type as contact_type,
164 contact_a.sort_name as sort_name,
165 d.date_added as date_added";
166 $groupBy = " GROUP BY contact_id ";
169 return $this->sql($selectClause,
170 $offset, $rowcount, $sort,
171 $includeContactIDs, $groupBy
178 public function from() {
180 $randomNum = md5(uniqid());
181 $this->_tableName
= "civicrm_temp_custom_{$randomNum}";
183 //grab the contacts added in the date range first
184 $sql = "CREATE TEMPORARY TABLE dates_{$this->_tableName} ( id int primary key, date_added date ) ENGINE=HEAP";
185 if ($this->_debug
> 0) {
186 print "-- Date range query: <pre>";
190 CRM_Core_DAO
::executeQuery($sql);
192 $startDate = CRM_Utils_Date
::mysqlToIso(CRM_Utils_Date
::processDate($this->_formValues
['start_date']));
194 if (!empty($this->_formValues
['end_date'])) {
195 $endDate = CRM_Utils_Date
::mysqlToIso(CRM_Utils_Date
::processDate($this->_formValues
['end_date']));
196 # tack 11:59pm on to make search inclusive of the end date
197 $endDateFix = "AND date_added <= '" . substr($endDate, 0, 10) . " 23:59:00'";
200 $dateRange = "INSERT INTO dates_{$this->_tableName} ( id, date_added )
203 min(civicrm_log.modified_date) AS date_added
205 civicrm_contact LEFT JOIN civicrm_log
206 ON (civicrm_contact.id = civicrm_log.entity_id AND
207 civicrm_log.entity_table = 'civicrm_contact')
211 date_added >= '$startDate'
214 if ($this->_debug
> 0) {
215 print "-- Date range query: <pre>";
220 CRM_Core_DAO
::executeQuery($dateRange, CRM_Core_DAO
::$_nullArray);
222 // Only include groups in the search query of one or more Include OR Exclude groups has been selected.
224 if ($this->_groups
) {
225 //block for Group search
226 $smartGroup = array();
227 $group = new CRM_Contact_DAO_Group();
228 $group->is_active
= 1;
230 while ($group->fetch()) {
231 $allGroups[] = $group->id
;
232 if ($group->saved_search_id
) {
233 $smartGroup[$group->saved_search_id
] = $group->id
;
236 $includedGroups = implode(',', $allGroups);
238 if (!empty($this->_includeGroups
)) {
239 $iGroups = implode(',', $this->_includeGroups
);
242 //if no group selected search for all groups
243 $iGroups = $includedGroups;
245 if (is_array($this->_excludeGroups
)) {
246 $xGroups = implode(',', $this->_excludeGroups
);
252 $sql = "DROP TEMPORARY TABLE IF EXISTS Xg_{$this->_tableName}";
253 CRM_Core_DAO
::executeQuery($sql, CRM_Core_DAO
::$_nullArray);
254 $sql = "CREATE TEMPORARY TABLE Xg_{$this->_tableName} ( contact_id int primary key) ENGINE=HEAP";
255 CRM_Core_DAO
::executeQuery($sql, CRM_Core_DAO
::$_nullArray);
257 //used only when exclude group is selected
259 $excludeGroup = "INSERT INTO Xg_{$this->_tableName} ( contact_id )
260 SELECT DISTINCT civicrm_group_contact.contact_id
261 FROM civicrm_group_contact, dates_{$this->_tableName} AS d
263 d.id = civicrm_group_contact.contact_id AND
264 civicrm_group_contact.status = 'Added' AND
265 civicrm_group_contact.group_id IN( {$xGroups})";
267 CRM_Core_DAO
::executeQuery($excludeGroup, CRM_Core_DAO
::$_nullArray);
269 //search for smart group contacts
270 foreach ($this->_excludeGroups
as $keys => $values) {
271 if (in_array($values, $smartGroup)) {
272 $ssId = CRM_Utils_Array
::key($values, $smartGroup);
274 $smartSql = CRM_Contact_BAO_SavedSearch
::contactIDsSQL($ssId);
276 $smartSql = $smartSql . " AND contact_a.id NOT IN (
277 SELECT contact_id FROM civicrm_group_contact
278 WHERE civicrm_group_contact.group_id = {$values} AND civicrm_group_contact.status = 'Removed')";
280 $smartGroupQuery = " INSERT IGNORE INTO Xg_{$this->_tableName}(contact_id) $smartSql";
282 CRM_Core_DAO
::executeQuery($smartGroupQuery, CRM_Core_DAO
::$_nullArray);
287 $sql = "DROP TEMPORARY TABLE IF EXISTS Ig_{$this->_tableName}";
288 CRM_Core_DAO
::executeQuery($sql, CRM_Core_DAO
::$_nullArray);
289 $sql = "CREATE TEMPORARY TABLE Ig_{$this->_tableName}
290 ( id int PRIMARY KEY AUTO_INCREMENT,
292 group_names varchar(64)) ENGINE=HEAP";
294 if ($this->_debug
> 0) {
295 print "-- Include groups query: <pre>";
300 CRM_Core_DAO
::executeQuery($sql, CRM_Core_DAO
::$_nullArray);
302 $includeGroup = "INSERT INTO Ig_{$this->_tableName} (contact_id, group_names)
303 SELECT d.id as contact_id, civicrm_group.name as group_name
304 FROM dates_{$this->_tableName} AS d
305 INNER JOIN civicrm_group_contact
306 ON civicrm_group_contact.contact_id = d.id
307 LEFT JOIN civicrm_group
308 ON civicrm_group_contact.group_id = civicrm_group.id";
310 //used only when exclude group is selected
312 $includeGroup .= " LEFT JOIN Xg_{$this->_tableName}
313 ON d.id = Xg_{$this->_tableName}.contact_id";
315 $includeGroup .= " WHERE
316 civicrm_group_contact.status = 'Added' AND
317 civicrm_group_contact.group_id IN($iGroups)";
319 //used only when exclude group is selected
321 $includeGroup .= " AND Xg_{$this->_tableName}.contact_id IS null";
324 if ($this->_debug
> 0) {
325 print "-- Include groups query: <pre>";
326 print "$includeGroup;";
330 CRM_Core_DAO
::executeQuery($includeGroup, CRM_Core_DAO
::$_nullArray);
332 //search for smart group contacts
333 foreach ($this->_includeGroups
as $keys => $values) {
334 if (in_array($values, $smartGroup)) {
336 $ssId = CRM_Utils_Array
::key($values, $smartGroup);
338 $smartSql = CRM_Contact_BAO_SavedSearch
::contactIDsSQL($ssId);
340 $smartSql .= " AND contact_a.id IN (
341 SELECT id AS contact_id
342 FROM dates_{$this->_tableName} )";
344 $smartSql .= " AND contact_a.id NOT IN (
345 SELECT contact_id FROM civicrm_group_contact
346 WHERE civicrm_group_contact.group_id = {$values} AND civicrm_group_contact.status = 'Removed')";
348 //used only when exclude group is selected
350 $smartSql .= " AND contact_a.id NOT IN (SELECT contact_id FROM Xg_{$this->_tableName})";
353 $smartGroupQuery = " INSERT IGNORE INTO
354 Ig_{$this->_tableName}(contact_id)
357 CRM_Core_DAO
::executeQuery($smartGroupQuery, CRM_Core_DAO
::$_nullArray);
358 if ($this->_debug
> 0) {
359 print "-- Smart group query: <pre>";
360 print "$smartGroupQuery;";
363 $insertGroupNameQuery = "UPDATE IGNORE Ig_{$this->_tableName}
364 SET group_names = (SELECT title FROM civicrm_group
365 WHERE civicrm_group.id = $values)
366 WHERE Ig_{$this->_tableName}.contact_id IS NOT NULL
367 AND Ig_{$this->_tableName}.group_names IS NULL";
368 CRM_Core_DAO
::executeQuery($insertGroupNameQuery, CRM_Core_DAO
::$_nullArray);
369 if ($this->_debug
> 0) {
370 print "-- Smart group query: <pre>";
371 print "$insertGroupNameQuery;";
377 // end if( $this->_groups ) condition
378 $this->buildACLClause('contact_a');
379 $from = "FROM civicrm_contact contact_a";
381 /* We need to join to this again to get the date_added value */
383 $from .= " INNER JOIN dates_{$this->_tableName} d ON (contact_a.id = d.id) {$this->_aclFrom}";
385 // Only include groups in the search query of one or more Include OR Exclude groups has been selected.
387 if ($this->_groups
) {
388 $from .= " INNER JOIN Ig_{$this->_tableName} temptable1 ON (contact_a.id = temptable1.contact_id)";
395 * @param bool $includeContactIDs
399 public function where($includeContactIDs = FALSE) {
401 if ($this->_aclWhere
) {
402 $where .= " AND {$this->_aclWhere} ";
410 public function templateFile() {
411 return 'CRM/Contact/Form/Search/Custom.tpl';
417 public function setTitle($title) {
419 CRM_Utils_System
::setTitle($title);
422 CRM_Utils_System
::setTitle(ts('Search'));
429 public function count() {
432 $dao = CRM_Core_DAO
::executeQuery($sql,
433 CRM_Core_DAO
::$_nullArray
438 public function __destruct() {
439 //drop the temp. tables if they exist
440 if (!empty($this->_includeGroups
)) {
441 $sql = "DROP TEMPORARY TABLE IF EXISTS Ig_{$this->_tableName}";
442 CRM_Core_DAO
::executeQuery($sql, CRM_Core_DAO
::$_nullArray);
445 if (!empty($this->_excludeGroups
)) {
446 $sql = "DROP TEMPORARY TABLE IF EXISTS Xg_{$this->_tableName}";
447 CRM_Core_DAO
::executeQuery($sql, CRM_Core_DAO
::$_nullArray);
452 * @param string $tableAlias
454 public function buildACLClause($tableAlias = 'contact') {
455 list($this->_aclFrom
, $this->_aclWhere
) = CRM_Contact_BAO_Contact_Permission
::cacheClause($tableAlias);