3 +--------------------------------------------------------------------+
4 | CiviCRM version 4.6 |
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_DateAdded
extends CRM_Contact_Form_Search_Custom_Base
implements CRM_Contact_Form_Search_Interface
{
37 protected $_debug = 0;
42 public function __construct(&$formValues) {
43 parent
::__construct($formValues);
45 $this->_includeGroups
= CRM_Utils_Array
::value('includeGroups', $formValues, array());
46 $this->_excludeGroups
= CRM_Utils_Array
::value('excludeGroups', $formValues, array());
48 $this->_columns
= array(
49 ts('Contact ID') => 'contact_id',
50 ts('Contact Type') => 'contact_type',
51 ts('Name') => 'sort_name',
52 ts('Date Added') => 'date_added',
57 * @param CRM_Core_Form $form
59 public function buildForm(&$form) {
60 $form->addDate('start_date', ts('Start Date'), FALSE, array('formatType' => 'custom'));
61 $form->addDate('end_date', ts('End Date'), FALSE, array('formatType' => 'custom'));
63 $groups = CRM_Core_PseudoConstant
::nestedGroup();
65 $select2style = array(
67 'style' => 'width: 100%; max-width: 60em;',
68 'class' => 'crm-select2',
69 'placeholder' => ts('- select -'),
72 $form->add('select', 'includeGroups',
73 ts('Include Group(s)'),
79 $form->add('select', 'excludeGroups',
80 ts('Exclude Group(s)'),
86 $this->setTitle('Search by date added to CiviCRM');
88 //redirect if group not available for search criteria
89 if (count($groups) == 0) {
90 CRM_Core_Error
::statusBounce(ts("Atleast one Group must be present for search."),
91 CRM_Utils_System
::url('civicrm/contact/search/custom/list',
98 * if you are using the standard template, this array tells the template what elements
99 * are part of the search criteria
101 $form->assign('elements', array('start_date', 'end_date', 'includeGroups', 'excludeGroups'));
107 public function summary() {
111 public function contactIDs($offset = 0, $rowcount = 0, $sort = NULL, $returnSQL = FALSE) {
112 return $this->all($offset, $rowcount, $sort, FALSE, TRUE);
117 * @param int $rowcount
119 * @param bool $includeContactIDs
120 * @param bool $justIDs
125 $offset = 0, $rowcount = 0, $sort = NULL,
126 $includeContactIDs = FALSE, $justIDs = FALSE
129 $this->_includeGroups
= CRM_Utils_Array
::value('includeGroups', $this->_formValues
, array());
131 $this->_excludeGroups
= CRM_Utils_Array
::value('excludeGroups', $this->_formValues
, array());
133 $this->_allSearch
= FALSE;
134 $this->_groups
= FALSE;
136 if (empty($this->_includeGroups
) && empty($this->_excludeGroups
)) {
138 $this->_allSearch
= TRUE;
141 if (!empty($this->_includeGroups
) ||
!empty($this->_excludeGroups
)) {
143 $this->_groups
= TRUE;
147 $selectClause = "contact_a.id as contact_id";
148 $groupBy = " GROUP BY contact_a.id";
149 $sort = "contact_a.id";
152 $selectClause = "contact_a.id as contact_id,
153 contact_a.contact_type as contact_type,
154 contact_a.sort_name as sort_name,
155 d.date_added as date_added";
156 $groupBy = " GROUP BY contact_id ";
159 return $this->sql($selectClause,
160 $offset, $rowcount, $sort,
161 $includeContactIDs, $groupBy
168 public function from() {
170 $randomNum = md5(uniqid());
171 $this->_tableName
= "civicrm_temp_custom_{$randomNum}";
173 //grab the contacts added in the date range first
174 $sql = "CREATE TEMPORARY TABLE dates_{$this->_tableName} ( id int primary key, date_added date ) ENGINE=HEAP";
175 if ($this->_debug
> 0) {
176 print "-- Date range query: <pre>";
180 CRM_Core_DAO
::executeQuery($sql);
182 $startDate = CRM_Utils_Date
::mysqlToIso(CRM_Utils_Date
::processDate($this->_formValues
['start_date']));
184 if (!empty($this->_formValues
['end_date'])) {
185 $endDate = CRM_Utils_Date
::mysqlToIso(CRM_Utils_Date
::processDate($this->_formValues
['end_date']));
186 # tack 11:59pm on to make search inclusive of the end date
187 $endDateFix = "AND date_added <= '" . substr($endDate, 0, 10) . " 23:59:00'";
190 $dateRange = "INSERT INTO dates_{$this->_tableName} ( id, date_added )
193 min(civicrm_log.modified_date) AS date_added
195 civicrm_contact LEFT JOIN civicrm_log
196 ON (civicrm_contact.id = civicrm_log.entity_id AND
197 civicrm_log.entity_table = 'civicrm_contact')
201 date_added >= '$startDate'
204 if ($this->_debug
> 0) {
205 print "-- Date range query: <pre>";
210 CRM_Core_DAO
::executeQuery($dateRange, CRM_Core_DAO
::$_nullArray);
212 // Only include groups in the search query of one or more Include OR Exclude groups has been selected.
214 if ($this->_groups
) {
215 //block for Group search
216 $smartGroup = array();
217 $group = new CRM_Contact_DAO_Group();
218 $group->is_active
= 1;
220 while ($group->fetch()) {
221 $allGroups[] = $group->id
;
222 if ($group->saved_search_id
) {
223 $smartGroup[$group->saved_search_id
] = $group->id
;
226 $includedGroups = implode(',', $allGroups);
228 if (!empty($this->_includeGroups
)) {
229 $iGroups = implode(',', $this->_includeGroups
);
232 //if no group selected search for all groups
233 $iGroups = $includedGroups;
235 if (is_array($this->_excludeGroups
)) {
236 $xGroups = implode(',', $this->_excludeGroups
);
242 $sql = "DROP TEMPORARY TABLE IF EXISTS Xg_{$this->_tableName}";
243 CRM_Core_DAO
::executeQuery($sql, CRM_Core_DAO
::$_nullArray);
244 $sql = "CREATE TEMPORARY TABLE Xg_{$this->_tableName} ( contact_id int primary key) ENGINE=HEAP";
245 CRM_Core_DAO
::executeQuery($sql, CRM_Core_DAO
::$_nullArray);
247 //used only when exclude group is selected
249 $excludeGroup = "INSERT INTO Xg_{$this->_tableName} ( contact_id )
250 SELECT DISTINCT civicrm_group_contact.contact_id
251 FROM civicrm_group_contact, dates_{$this->_tableName} AS d
253 d.id = civicrm_group_contact.contact_id AND
254 civicrm_group_contact.status = 'Added' AND
255 civicrm_group_contact.group_id IN( {$xGroups})";
257 CRM_Core_DAO
::executeQuery($excludeGroup, CRM_Core_DAO
::$_nullArray);
259 //search for smart group contacts
260 foreach ($this->_excludeGroups
as $keys => $values) {
261 if (in_array($values, $smartGroup)) {
262 $ssId = CRM_Utils_Array
::key($values, $smartGroup);
264 $smartSql = CRM_Contact_BAO_SavedSearch
::contactIDsSQL($ssId);
266 $smartSql = $smartSql . " AND contact_a.id NOT IN (
267 SELECT contact_id FROM civicrm_group_contact
268 WHERE civicrm_group_contact.group_id = {$values} AND civicrm_group_contact.status = 'Removed')";
270 $smartGroupQuery = " INSERT IGNORE INTO Xg_{$this->_tableName}(contact_id) $smartSql";
272 CRM_Core_DAO
::executeQuery($smartGroupQuery, CRM_Core_DAO
::$_nullArray);
277 $sql = "DROP TEMPORARY TABLE IF EXISTS Ig_{$this->_tableName}";
278 CRM_Core_DAO
::executeQuery($sql, CRM_Core_DAO
::$_nullArray);
279 $sql = "CREATE TEMPORARY TABLE Ig_{$this->_tableName}
280 ( id int PRIMARY KEY AUTO_INCREMENT,
282 group_names varchar(64)) ENGINE=HEAP";
284 if ($this->_debug
> 0) {
285 print "-- Include groups query: <pre>";
290 CRM_Core_DAO
::executeQuery($sql, CRM_Core_DAO
::$_nullArray);
292 $includeGroup = "INSERT INTO Ig_{$this->_tableName} (contact_id, group_names)
293 SELECT d.id as contact_id, civicrm_group.name as group_name
294 FROM dates_{$this->_tableName} AS d
295 INNER JOIN civicrm_group_contact
296 ON civicrm_group_contact.contact_id = d.id
297 LEFT JOIN civicrm_group
298 ON civicrm_group_contact.group_id = civicrm_group.id";
300 //used only when exclude group is selected
302 $includeGroup .= " LEFT JOIN Xg_{$this->_tableName}
303 ON d.id = Xg_{$this->_tableName}.contact_id";
305 $includeGroup .= " WHERE
306 civicrm_group_contact.status = 'Added' AND
307 civicrm_group_contact.group_id IN($iGroups)";
309 //used only when exclude group is selected
311 $includeGroup .= " AND Xg_{$this->_tableName}.contact_id IS null";
314 if ($this->_debug
> 0) {
315 print "-- Include groups query: <pre>";
316 print "$includeGroup;";
320 CRM_Core_DAO
::executeQuery($includeGroup, CRM_Core_DAO
::$_nullArray);
322 //search for smart group contacts
323 foreach ($this->_includeGroups
as $keys => $values) {
324 if (in_array($values, $smartGroup)) {
326 $ssId = CRM_Utils_Array
::key($values, $smartGroup);
328 $smartSql = CRM_Contact_BAO_SavedSearch
::contactIDsSQL($ssId);
330 $smartSql .= " AND contact_a.id IN (
331 SELECT id AS contact_id
332 FROM dates_{$this->_tableName} )";
334 $smartSql .= " AND contact_a.id NOT IN (
335 SELECT contact_id FROM civicrm_group_contact
336 WHERE civicrm_group_contact.group_id = {$values} AND civicrm_group_contact.status = 'Removed')";
338 //used only when exclude group is selected
340 $smartSql .= " AND contact_a.id NOT IN (SELECT contact_id FROM Xg_{$this->_tableName})";
343 $smartGroupQuery = " INSERT IGNORE INTO
344 Ig_{$this->_tableName}(contact_id)
347 CRM_Core_DAO
::executeQuery($smartGroupQuery, CRM_Core_DAO
::$_nullArray);
348 if ($this->_debug
> 0) {
349 print "-- Smart group query: <pre>";
350 print "$smartGroupQuery;";
353 $insertGroupNameQuery = "UPDATE IGNORE Ig_{$this->_tableName}
354 SET group_names = (SELECT title FROM civicrm_group
355 WHERE civicrm_group.id = $values)
356 WHERE Ig_{$this->_tableName}.contact_id IS NOT NULL
357 AND Ig_{$this->_tableName}.group_names IS NULL";
358 CRM_Core_DAO
::executeQuery($insertGroupNameQuery, CRM_Core_DAO
::$_nullArray);
359 if ($this->_debug
> 0) {
360 print "-- Smart group query: <pre>";
361 print "$insertGroupNameQuery;";
367 // end if( $this->_groups ) condition
369 $from = "FROM civicrm_contact contact_a";
371 /* We need to join to this again to get the date_added value */
373 $from .= " INNER JOIN dates_{$this->_tableName} d ON (contact_a.id = d.id)";
375 // Only include groups in the search query of one or more Include OR Exclude groups has been selected.
377 if ($this->_groups
) {
378 $from .= " INNER JOIN Ig_{$this->_tableName} temptable1 ON (contact_a.id = temptable1.contact_id)";
385 * @param bool $includeContactIDs
389 public function where($includeContactIDs = FALSE) {
396 public function templateFile() {
397 return 'CRM/Contact/Form/Search/Custom.tpl';
403 public function setTitle($title) {
405 CRM_Utils_System
::setTitle($title);
408 CRM_Utils_System
::setTitle(ts('Search'));
415 public function count() {
418 $dao = CRM_Core_DAO
::executeQuery($sql,
419 CRM_Core_DAO
::$_nullArray
424 public function __destruct() {
425 //drop the temp. tables if they exist
426 if (!empty($this->_includeGroups
)) {
427 $sql = "DROP TEMPORARY TABLE IF EXISTS Ig_{$this->_tableName}";
428 CRM_Core_DAO
::executeQuery($sql, CRM_Core_DAO
::$_nullArray);
431 if (!empty($this->_excludeGroups
)) {
432 $sql = "DROP TEMPORARY TABLE IF EXISTS Xg_{$this->_tableName}";
433 CRM_Core_DAO
::executeQuery($sql, CRM_Core_DAO
::$_nullArray);