3 +--------------------------------------------------------------------+
5 +--------------------------------------------------------------------+
6 | Copyright CiviCRM LLC (c) 2004-2019 |
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-2019
33 class CRM_Campaign_BAO_Query
{
34 //since normal activity clause clause get collides.
36 CIVICRM_ACTIVITY
= 'civicrm_survey_activity',
37 CIVICRM_ACTIVITY_TARGET
= 'civicrm_survey_activity_target',
38 CIVICRM_ACTIVITY_ASSIGNMENT
= 'civicrm_survey_activity_assignment';
41 * Static field for all the campaign fields.
45 static $_campaignFields = NULL;
47 static $_applySurveyClause = FALSE;
50 * Function get the fields for campaign.
53 * self::$_campaignFields an associative array of campaign fields
55 public static function &getFields() {
56 if (!isset(self
::$_campaignFields)) {
57 self
::$_campaignFields = array();
60 return self
::$_campaignFields;
64 * If survey, campaign are involved, add the specific fields.
66 * @param CRM_Contact_BAO_Contact $query
68 public static function select(&$query) {
69 self
::$_applySurveyClause = FALSE;
70 if (is_array($query->_params
)) {
71 foreach ($query->_params
as $values) {
72 if (!is_array($values) ||
count($values) != 5) {
76 list($name, $op, $value, $grouping, $wildcard) = $values;
77 if ($name == 'campaign_survey_id') {
78 self
::$_applySurveyClause = TRUE;
83 // CRM-13810 Translate campaign_id to label for search builder
84 // CRM-14238 Only translate when we are in contact mode
85 // Other modes need the untranslated data for export and other functions
86 if (is_array($query->_select
) && $query->_mode
== CRM_Contact_BAO_Query
::MODE_CONTACTS
) {
87 foreach ($query->_select
as $field => $queryString) {
88 if (substr($field, -11) == 'campaign_id') {
89 $query->_pseudoConstantsSelect
[$field] = array(
90 'pseudoField' => 'campaign_id',
92 'bao' => 'CRM_Activity_BAO_Activity',
98 //get survey clause in force,
99 //only when we have survey id.
100 if (!self
::$_applySurveyClause) {
104 //all below tables are require to fetch result.
106 //1. get survey activity target table in.
107 $query->_select
['survey_activity_target_contact_id'] = 'civicrm_activity_target.contact_id as survey_activity_target_contact_id';
108 $query->_select
['survey_activity_target_id'] = 'civicrm_activity_target.id as survey_activity_target_id';
109 $query->_element
['survey_activity_target_id'] = 1;
110 $query->_element
['survey_activity_target_contact_id'] = 1;
111 $query->_tables
[self
::CIVICRM_ACTIVITY_TARGET
] = 1;
112 $query->_whereTables
[self
::CIVICRM_ACTIVITY_TARGET
] = 1;
114 //2. get survey activity table in.
115 $query->_select
['survey_activity_id'] = 'civicrm_activity.id as survey_activity_id';
116 $query->_element
['survey_activity_id'] = 1;
117 $query->_tables
[self
::CIVICRM_ACTIVITY
] = 1;
118 $query->_whereTables
[self
::CIVICRM_ACTIVITY
] = 1;
120 //3. get the assignee table in.
121 $query->_select
['survey_interviewer_id'] = 'civicrm_activity_assignment.id as survey_interviewer_id';
122 $query->_element
['survey_interviewer_id'] = 1;
123 $query->_tables
[self
::CIVICRM_ACTIVITY_ASSIGNMENT
] = 1;
124 $query->_whereTables
[self
::CIVICRM_ACTIVITY_ASSIGNMENT
] = 1;
126 //4. get survey table.
127 $query->_select
['campaign_survey_id'] = 'civicrm_survey.id as campaign_survey_id';
128 $query->_element
['campaign_survey_id'] = 1;
129 $query->_tables
['civicrm_survey'] = 1;
130 $query->_whereTables
['civicrm_survey'] = 1;
132 //5. get campaign table.
133 $query->_select
['campaign_id'] = 'civicrm_campaign.id as campaign_id';
134 $query->_element
['campaign_id'] = 1;
135 $query->_tables
['civicrm_campaign'] = 1;
136 $query->_whereTables
['civicrm_campaign'] = 1;
142 public static function where(&$query) {
143 //get survey clause in force,
144 //only when we have survey id.
145 if (!self
::$_applySurveyClause) {
150 foreach (array_keys($query->_params
) as $id) {
151 if ($query->_mode
== CRM_Contact_BAO_Query
::MODE_CONTACTS
) {
152 $query->_useDistinct
= TRUE;
155 self
::whereClauseSingle($query->_params
[$id], $query);
163 public static function whereClauseSingle(&$values, &$query) {
164 //get survey clause in force,
165 //only when we have survey id.
166 if (!self
::$_applySurveyClause) {
170 list($name, $op, $value, $grouping, $wildcard) = $values;
173 case 'campaign_survey_id':
174 $query->_qill
[$grouping][] = ts('Survey - %1', array(1 => CRM_Core_DAO
::getFieldValue('CRM_Campaign_DAO_Survey', $value, 'title')));
176 $query->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause('civicrm_activity.source_record_id',
177 $op, $value, 'Integer'
179 $query->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause('civicrm_survey.id',
180 $op, $value, 'Integer'
184 case 'survey_status_id':
185 $activityStatus = CRM_Core_PseudoConstant
::activityStatus();
187 $query->_qill
[$grouping][] = ts('Survey Status - %1', array(1 => $activityStatus[$value]));
188 $query->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause('civicrm_activity.status_id',
189 $op, $value, 'Integer'
193 case 'campaign_search_voter_for':
194 if (in_array($value, array('release', 'interview'))) {
195 $query->_where
[$grouping][] = '(civicrm_activity.is_deleted = 0 OR civicrm_activity.is_deleted IS NULL)';
199 case 'survey_interviewer_id':
200 $surveyInterviewerName = CRM_Core_DAO
::getFieldValue('CRM_Contact_DAO_Contact', $value, 'sort_name');
201 $query->_qill
[$grouping][] = ts('Survey Interviewer - %1', array(1 => $surveyInterviewerName));
202 $query->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause('civicrm_activity_assignment.contact_id',
203 $op, $value, 'Integer'
210 * @param string $name
214 * @return null|string
216 public static function from($name, $mode, $side) {
218 //get survey clause in force,
219 //only when we have survey id.
220 if (!self
::$_applySurveyClause) {
224 $activityContacts = CRM_Activity_BAO_ActivityContact
::buildOptions('record_type_id', 'validate');
225 $sourceID = CRM_Utils_Array
::key('Activity Source', $activityContacts);
226 $assigneeID = CRM_Utils_Array
::key('Activity Assignees', $activityContacts);
227 $targetID = CRM_Utils_Array
::key('Activity Targets', $activityContacts);
230 case self
::CIVICRM_ACTIVITY_TARGET
:
231 $from = " INNER JOIN civicrm_activity_contact civicrm_activity_target
232 ON ( civicrm_activity_target.contact_id = contact_a.id AND civicrm_activity_target.record_type_id = $targetID) ";
235 case self
::CIVICRM_ACTIVITY
:
236 $surveyActivityTypes = CRM_Campaign_PseudoConstant
::activityType();
237 $surveyKeys = "(" . implode(',', array_keys($surveyActivityTypes)) . ")";
238 $from = " INNER JOIN civicrm_activity ON ( civicrm_activity.id = civicrm_activity_target.activity_id
239 AND civicrm_activity.activity_type_id IN $surveyKeys ) ";
242 case self
::CIVICRM_ACTIVITY_ASSIGNMENT
:
244 INNER JOIN civicrm_activity_contact civicrm_activity_assignment ON ( civicrm_activity.id = civicrm_activity_assignment.activity_id AND
245 civicrm_activity_assignment.record_type_id = $assigneeID ) ";
248 case 'civicrm_survey':
249 $from = " INNER JOIN civicrm_survey ON ( civicrm_survey.id = civicrm_activity.source_record_id ) ";
252 case 'civicrm_campaign':
253 $from = " $side JOIN civicrm_campaign ON ( civicrm_campaign.id = civicrm_survey.campaign_id ) ";
262 * @param bool $includeCustomFields
266 public static function defaultReturnProperties(
268 $includeCustomFields = TRUE
271 if ($mode & CRM_Contact_BAO_Query
::MODE_CAMPAIGN
) {
275 'contact_sub_type' => 1,
280 'street_number' => 1,
281 'street_address' => 1,
284 'state_province' => 1,
288 'survey_activity_target_id' => 1,
289 'survey_activity_id' => 1,
290 'survey_status_id' => 1,
291 'campaign_survey_id' => 1,
293 'survey_interviewer_id' => 1,
294 'survey_activity_target_contact_id' => 1,
304 public static function tableNames(&$tables) {
311 public static function searchAction(&$row, $id) {
317 public static function info(&$tables) {
318 //get survey clause in force,
319 //only when we have survey id.
320 if (!self
::$_applySurveyClause) {
324 $weight = end($tables);
325 $tables[self
::CIVICRM_ACTIVITY_TARGET
] = ++
$weight;
326 $tables[self
::CIVICRM_ACTIVITY
] = ++
$weight;
327 $tables[self
::CIVICRM_ACTIVITY_ASSIGNMENT
] = ++
$weight;
328 $tables['civicrm_survey'] = ++
$weight;
329 $tables['civicrm_campaign'] = ++
$weight;
333 * Add all the elements shared between,
334 * normal voter search and voter listing (GOTV form)
336 * @param CRM_Core_Form $form
338 public static function buildSearchForm(&$form) {
340 $attributes = CRM_Core_DAO
::getAttribute('CRM_Core_DAO_Address');
341 $className = CRM_Utils_System
::getClassName($form);
343 $form->add('text', 'sort_name', ts('Contact Name'),
344 CRM_Core_DAO
::getAttribute('CRM_Contact_DAO_Contact', 'sort_name')
346 $form->add('text', 'street_name', ts('Street Name'), $attributes['street_name']);
347 $form->add('text', 'street_number', ts('Street Number'), $attributes['street_number']);
348 $form->add('text', 'street_unit', ts('Street Unit'), $attributes['street_unit']);
349 $form->add('text', 'street_address', ts('Street Address'), $attributes['street_address']);
350 $form->add('text', 'city', ts('City'), $attributes['city']);
351 $form->add('text', 'postal_code', ts('Postal Code'), $attributes['postal_code']);
353 //@todo FIXME - using the CRM_Core_DAO::VALUE_SEPARATOR creates invalid html - if you can find the form
354 // this is loaded onto then replace with something like '__' & test
355 $separator = CRM_Core_DAO
::VALUE_SEPARATOR
;
356 $contactTypes = CRM_Contact_BAO_ContactType
::getSelectElements(FALSE, TRUE, $separator);
357 $form->add('select', 'contact_type', ts('Contact Type(s)'), $contactTypes, FALSE,
358 array('id' => 'contact_type', 'multiple' => 'multiple', 'class' => 'crm-select2')
360 $groups = CRM_Core_PseudoConstant
::nestedGroup();
361 $form->add('select', 'group', ts('Groups'), $groups, FALSE,
362 array('multiple' => 'multiple', 'class' => 'crm-select2')
365 $showInterviewer = FALSE;
366 if (CRM_Core_Permission
::check('administer CiviCampaign')) {
367 $showInterviewer = TRUE;
369 $form->assign('showInterviewer', $showInterviewer);
371 if ($showInterviewer ||
372 $className == 'CRM_Campaign_Form_Gotv'
375 $form->addEntityRef('survey_interviewer_id', ts('Interviewer'), array('class' => 'big'));
378 if (isset($form->_interviewerId
) && $form->_interviewerId
) {
379 $userId = $form->_interviewerId
;
382 $session = CRM_Core_Session
::singleton();
383 $userId = $session->get('userID');
387 $defaults['survey_interviewer_id'] = $userId;
388 $form->setDefaults($defaults);
392 //build ward and precinct custom fields.
394 SELECT fld.id, fld.label
395 FROM civicrm_custom_field fld
396 INNER JOIN civicrm_custom_group grp on fld.custom_group_id = grp.id
397 WHERE grp.name = %1';
398 $dao = CRM_Core_DAO
::executeQuery($query, array(1 => array('Voter_Info', 'String')));
399 $customSearchFields = array();
400 while ($dao->fetch()) {
405 if (stripos($name, $dao->label
) !== FALSE) {
407 $fieldName = 'custom_' . $dao->id
;
408 $customSearchFields[$name] = $fieldName;
409 CRM_Core_BAO_CustomField
::addQuickFormElement($form, $fieldName, $fieldId, FALSE);
414 $form->assign('customSearchFields', $customSearchFields);
416 $surveys = CRM_Campaign_BAO_Survey
::getSurveys();
418 if (empty($surveys) &&
419 ($className == 'CRM_Campaign_Form_Search')
421 CRM_Core_Error
::statusBounce(ts('Could not find survey for %1 respondents.',
422 array(1 => $form->get('op'))
424 CRM_Utils_System
::url('civicrm/survey/add',
431 //If survey had associated campaign and
432 //campaign has some contact groups, don't
433 //allow to search the contacts those are not
434 //in given campaign groups ( ie not in constituents )
435 $props = array('class' => 'crm-select2');
436 if ($form->get('searchVoterFor') == 'reserve') {
437 $props['onChange'] = "buildCampaignGroups( );return false;";
439 $form->add('select', 'campaign_survey_id', ts('Survey'), $surveys, TRUE, $props);
443 * Retrieve all valid voter ids,
444 * and build respective clause to restrict search.
446 * @param array $criteria
448 * @return $voterClause as a string
451 * @param array $params
455 static public function voterClause($params) {
456 $voterClause = array();
457 $fromClause = $whereClause = NULL;
458 if (!is_array($params) ||
empty($params)) {
461 $surveyId = CRM_Utils_Array
::value('campaign_survey_id', $params);
462 $searchVoterFor = CRM_Utils_Array
::value('campaign_search_voter_for', $params);
464 //get the survey activities.
465 $activityStatus = CRM_Core_PseudoConstant
::activityStatus('name');
466 $status = array('Scheduled');
467 if ($searchVoterFor == 'reserve') {
468 $status[] = 'Completed';
471 $completedStatusId = NULL;
472 foreach ($status as $name) {
473 if ($statusId = array_search($name, $activityStatus)) {
474 $statusIds[] = $statusId;
475 if ($name == 'Completed') {
476 $completedStatusId = $statusId;
481 $voterActValues = CRM_Campaign_BAO_Survey
::getSurveyVoterInfo($surveyId, NULL, $statusIds);
483 if (!empty($voterActValues)) {
485 $voterIds = array_keys($voterActValues);
486 if ($searchVoterFor == 'reserve') {
487 $operator = 'NOT IN';
488 //filter out recontact survey contacts.
489 $recontactInterval = CRM_Core_DAO
::getFieldValue('CRM_Campaign_DAO_Survey',
490 $surveyId, 'recontact_interval'
492 $recontactInterval = unserialize($recontactInterval);
494 is_array($recontactInterval) &&
495 !empty($recontactInterval)
498 foreach ($voterActValues as $values) {
499 $numOfDays = CRM_Utils_Array
::value($values['result'], $recontactInterval);
501 $values['status_id'] == $completedStatusId
503 $recontactIntSeconds = $numOfDays * 24 * 3600;
504 $actDateTimeSeconds = CRM_Utils_Date
::unixTime($values['activity_date_time']);
505 $totalSeconds = $recontactIntSeconds +
$actDateTimeSeconds;
506 //don't consider completed survey activity
507 //unless it fulfill recontact interval criteria.
508 if ($totalSeconds <= time()) {
512 $voterIds[$values['voter_id']] = $values['voter_id'];
517 //lets dump these ids in tmp table and
518 //use appropriate join depend on operator.
519 if (!empty($voterIds)) {
520 $voterIdCount = count($voterIds);
522 //create temporary table to store voter ids.
523 $tempTableName = CRM_Core_DAO
::createTempTableName('civicrm_survey_respondent');
524 CRM_Core_DAO
::executeQuery("DROP TEMPORARY TABLE IF EXISTS {$tempTableName}");
527 CREATE TEMPORARY TABLE {$tempTableName} (
528 id int unsigned NOT NULL AUTO_INCREMENT,
529 survey_contact_id int unsigned NOT NULL,
533 CRM_Core_DAO
::executeQuery($query);
538 $processIds = $voterIds;
539 $insertIds = array_splice($processIds, $insertedCount, $batch);
540 if (!empty($insertIds)) {
541 $insertSQL = "INSERT IGNORE INTO {$tempTableName}( survey_contact_id )
542 VALUES (" . implode('),(', $insertIds) . ');';
543 CRM_Core_DAO
::executeQuery($insertSQL);
545 $insertedCount +
= $batch;
546 } while ($insertedCount < $voterIdCount);
548 if ($operator == 'IN') {
549 $fromClause = " INNER JOIN {$tempTableName} ON ( {$tempTableName}.survey_contact_id = contact_a.id )";
552 $fromClause = " LEFT JOIN {$tempTableName} ON ( {$tempTableName}.survey_contact_id = contact_a.id )";
553 $whereClause = "( {$tempTableName}.survey_contact_id IS NULL )";
557 $voterClause = array(
558 'fromClause' => $fromClause,
559 'whereClause' => $whereClause,