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_DateAdded
extends CRM_Contact_Form_Search_Custom_Base
implements CRM_Contact_Form_Search_Interface
{
37 protected $_debug = 0;
42 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 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'));
111 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
124 function all($offset = 0, $rowcount = 0, $sort = NULL,
125 $includeContactIDs = FALSE, $justIDs = FALSE
128 $this->_includeGroups
= CRM_Utils_Array
::value('includeGroups', $this->_formValues
, array());
130 $this->_excludeGroups
= CRM_Utils_Array
::value('excludeGroups', $this->_formValues
, array());
132 $this->_allSearch
= FALSE;
133 $this->_groups
= FALSE;
135 if (empty($this->_includeGroups
) && empty($this->_excludeGroups
)) {
137 $this->_allSearch
= TRUE;
140 if (!empty($this->_includeGroups
) ||
!empty($this->_excludeGroups
)) {
142 $this->_groups
= TRUE;
146 $selectClause = "contact_a.id as contact_id";
147 $groupBy = " GROUP BY contact_a.id";
148 $sort = "contact_a.id";
151 $selectClause = "contact_a.id as contact_id,
152 contact_a.contact_type as contact_type,
153 contact_a.sort_name as sort_name,
154 d.date_added as date_added";
155 $groupBy = " GROUP BY contact_id ";
158 return $this->sql($selectClause,
159 $offset, $rowcount, $sort,
160 $includeContactIDs, $groupBy
169 $randomNum = md5(uniqid());
170 $this->_tableName
= "civicrm_temp_custom_{$randomNum}";
172 //grab the contacts added in the date range first
173 $sql = "CREATE TEMPORARY TABLE dates_{$this->_tableName} ( id int primary key, date_added date ) ENGINE=HEAP";
174 if ($this->_debug
> 0) {
175 print "-- Date range query: <pre>";
179 CRM_Core_DAO
::executeQuery($sql);
181 $startDate = CRM_Utils_Date
::mysqlToIso(CRM_Utils_Date
::processDate($this->_formValues
['start_date']));
183 if (!empty($this->_formValues
['end_date'])) {
184 $endDate = CRM_Utils_Date
::mysqlToIso(CRM_Utils_Date
::processDate($this->_formValues
['end_date']));
185 # tack 11:59pm on to make search inclusive of the end date
186 $endDateFix = "AND date_added <= '" . substr($endDate, 0, 10) . " 23:59:00'";
189 $dateRange = "INSERT INTO dates_{$this->_tableName} ( id, date_added )
192 min(civicrm_log.modified_date) AS date_added
194 civicrm_contact LEFT JOIN civicrm_log
195 ON (civicrm_contact.id = civicrm_log.entity_id AND
196 civicrm_log.entity_table = 'civicrm_contact')
200 date_added >= '$startDate'
203 if ($this->_debug
> 0) {
204 print "-- Date range query: <pre>";
209 CRM_Core_DAO
::executeQuery($dateRange, CRM_Core_DAO
::$_nullArray);
211 // Only include groups in the search query of one or more Include OR Exclude groups has been selected.
213 if ($this->_groups
) {
214 //block for Group search
215 $smartGroup = array();
216 $group = new CRM_Contact_DAO_Group();
217 $group->is_active
= 1;
219 while ($group->fetch()) {
220 $allGroups[] = $group->id
;
221 if ($group->saved_search_id
) {
222 $smartGroup[$group->saved_search_id
] = $group->id
;
225 $includedGroups = implode(',', $allGroups);
227 if (!empty($this->_includeGroups
)) {
228 $iGroups = implode(',', $this->_includeGroups
);
231 //if no group selected search for all groups
232 $iGroups = $includedGroups;
234 if (is_array($this->_excludeGroups
)) {
235 $xGroups = implode(',', $this->_excludeGroups
);
241 $sql = "DROP TEMPORARY TABLE IF EXISTS Xg_{$this->_tableName}";
242 CRM_Core_DAO
::executeQuery($sql, CRM_Core_DAO
::$_nullArray);
243 $sql = "CREATE TEMPORARY TABLE Xg_{$this->_tableName} ( contact_id int primary key) ENGINE=HEAP";
244 CRM_Core_DAO
::executeQuery($sql, CRM_Core_DAO
::$_nullArray);
246 //used only when exclude group is selected
248 $excludeGroup = "INSERT INTO Xg_{$this->_tableName} ( contact_id )
249 SELECT DISTINCT civicrm_group_contact.contact_id
250 FROM civicrm_group_contact, dates_{$this->_tableName} AS d
252 d.id = civicrm_group_contact.contact_id AND
253 civicrm_group_contact.status = 'Added' AND
254 civicrm_group_contact.group_id IN( {$xGroups})";
256 CRM_Core_DAO
::executeQuery($excludeGroup, CRM_Core_DAO
::$_nullArray);
258 //search for smart group contacts
259 foreach ($this->_excludeGroups
as $keys => $values) {
260 if (in_array($values, $smartGroup)) {
261 $ssId = CRM_Utils_Array
::key($values, $smartGroup);
263 $smartSql = CRM_Contact_BAO_SavedSearch
::contactIDsSQL($ssId);
265 $smartSql = $smartSql . " AND contact_a.id NOT IN (
266 SELECT contact_id FROM civicrm_group_contact
267 WHERE civicrm_group_contact.group_id = {$values} AND civicrm_group_contact.status = 'Removed')";
269 $smartGroupQuery = " INSERT IGNORE INTO Xg_{$this->_tableName}(contact_id) $smartSql";
271 CRM_Core_DAO
::executeQuery($smartGroupQuery, CRM_Core_DAO
::$_nullArray);
276 $sql = "DROP TEMPORARY TABLE IF EXISTS Ig_{$this->_tableName}";
277 CRM_Core_DAO
::executeQuery($sql, CRM_Core_DAO
::$_nullArray);
278 $sql = "CREATE TEMPORARY TABLE Ig_{$this->_tableName}
279 ( id int PRIMARY KEY AUTO_INCREMENT,
281 group_names varchar(64)) ENGINE=HEAP";
283 if ($this->_debug
> 0) {
284 print "-- Include groups query: <pre>";
289 CRM_Core_DAO
::executeQuery($sql, CRM_Core_DAO
::$_nullArray);
291 $includeGroup = "INSERT INTO Ig_{$this->_tableName} (contact_id, group_names)
292 SELECT d.id as contact_id, civicrm_group.name as group_name
293 FROM dates_{$this->_tableName} AS d
294 INNER JOIN civicrm_group_contact
295 ON civicrm_group_contact.contact_id = d.id
296 LEFT JOIN civicrm_group
297 ON civicrm_group_contact.group_id = civicrm_group.id";
299 //used only when exclude group is selected
301 $includeGroup .= " LEFT JOIN Xg_{$this->_tableName}
302 ON d.id = Xg_{$this->_tableName}.contact_id";
304 $includeGroup .= " WHERE
305 civicrm_group_contact.status = 'Added' AND
306 civicrm_group_contact.group_id IN($iGroups)";
308 //used only when exclude group is selected
310 $includeGroup .= " AND Xg_{$this->_tableName}.contact_id IS null";
313 if ($this->_debug
> 0) {
314 print "-- Include groups query: <pre>";
315 print "$includeGroup;";
319 CRM_Core_DAO
::executeQuery($includeGroup, CRM_Core_DAO
::$_nullArray);
321 //search for smart group contacts
322 foreach ($this->_includeGroups
as $keys => $values) {
323 if (in_array($values, $smartGroup)) {
325 $ssId = CRM_Utils_Array
::key($values, $smartGroup);
327 $smartSql = CRM_Contact_BAO_SavedSearch
::contactIDsSQL($ssId);
329 $smartSql .= " AND contact_a.id IN (
330 SELECT id AS contact_id
331 FROM dates_{$this->_tableName} )";
333 $smartSql .= " AND contact_a.id NOT IN (
334 SELECT contact_id FROM civicrm_group_contact
335 WHERE civicrm_group_contact.group_id = {$values} AND civicrm_group_contact.status = 'Removed')";
337 //used only when exclude group is selected
339 $smartSql .= " AND contact_a.id NOT IN (SELECT contact_id FROM Xg_{$this->_tableName})";
342 $smartGroupQuery = " INSERT IGNORE INTO
343 Ig_{$this->_tableName}(contact_id)
346 CRM_Core_DAO
::executeQuery($smartGroupQuery, CRM_Core_DAO
::$_nullArray);
347 if ($this->_debug
> 0) {
348 print "-- Smart group query: <pre>";
349 print "$smartGroupQuery;";
352 $insertGroupNameQuery = "UPDATE IGNORE Ig_{$this->_tableName}
353 SET group_names = (SELECT title FROM civicrm_group
354 WHERE civicrm_group.id = $values)
355 WHERE Ig_{$this->_tableName}.contact_id IS NOT NULL
356 AND Ig_{$this->_tableName}.group_names IS NULL";
357 CRM_Core_DAO
::executeQuery($insertGroupNameQuery, CRM_Core_DAO
::$_nullArray);
358 if ($this->_debug
> 0) {
359 print "-- Smart group query: <pre>";
360 print "$insertGroupNameQuery;";
366 // end if( $this->_groups ) condition
368 $from = "FROM civicrm_contact contact_a";
370 /* We need to join to this again to get the date_added value */
372 $from .= " INNER JOIN dates_{$this->_tableName} d ON (contact_a.id = d.id)";
374 // Only include groups in the search query of one or more Include OR Exclude groups has been selected.
376 if ($this->_groups
) {
377 $from .= " INNER JOIN Ig_{$this->_tableName} temptable1 ON (contact_a.id = temptable1.contact_id)";
384 * @param bool $includeContactIDs
388 function where($includeContactIDs = FALSE) {
395 function templateFile() {
396 return 'CRM/Contact/Form/Search/Custom.tpl';
402 function setTitle($title) {
404 CRM_Utils_System
::setTitle($title);
407 CRM_Utils_System
::setTitle(ts('Search'));
417 $dao = CRM_Core_DAO
::executeQuery($sql,
418 CRM_Core_DAO
::$_nullArray
423 function __destruct() {
424 //drop the temp. tables if they exist
425 if (!empty($this->_includeGroups
)) {
426 $sql = "DROP TEMPORARY TABLE IF EXISTS Ig_{$this->_tableName}";
427 CRM_Core_DAO
::executeQuery($sql, CRM_Core_DAO
::$_nullArray);
430 if (!empty($this->_excludeGroups
)) {
431 $sql = "DROP TEMPORARY TABLE IF EXISTS Xg_{$this->_tableName}";
432 CRM_Core_DAO
::executeQuery($sql, CRM_Core_DAO
::$_nullArray);