3 +--------------------------------------------------------------------+
4 | Copyright CiviCRM LLC. All rights reserved. |
6 | This work is published under the GNU AGPLv3 license with some |
7 | permitted exceptions and without any warranty. For full license |
8 | and copyright information, see https://civicrm.org/licensing |
9 +--------------------------------------------------------------------+
15 * @copyright CiviCRM LLC https://civicrm.org/licensing
17 class CRM_Campaign_BAO_Query
{
18 //since normal activity clause clause get collides.
20 CIVICRM_ACTIVITY
= 'civicrm_survey_activity',
21 CIVICRM_ACTIVITY_TARGET
= 'civicrm_survey_activity_target',
22 CIVICRM_ACTIVITY_ASSIGNMENT
= 'civicrm_survey_activity_assignment';
25 * Static field for all the campaign fields.
29 public static $_campaignFields = NULL;
31 public static $_applySurveyClause = FALSE;
34 * Function get the fields for campaign.
37 * self::$_campaignFields an associative array of campaign fields
39 public static function &getFields() {
40 if (!isset(self
::$_campaignFields)) {
41 self
::$_campaignFields = [];
44 return self
::$_campaignFields;
48 * If survey, campaign are involved, add the specific fields.
50 * @param CRM_Contact_BAO_Contact $query
52 public static function select(&$query) {
53 self
::$_applySurveyClause = FALSE;
54 if (is_array($query->_params
)) {
55 foreach ($query->_params
as $values) {
56 if (!is_array($values) ||
count($values) != 5) {
60 list($name, $op, $value, $grouping, $wildcard) = $values;
61 if ($name == 'campaign_survey_id') {
62 self
::$_applySurveyClause = TRUE;
67 // CRM-13810 Translate campaign_id to label for search builder
68 // CRM-14238 Only translate when we are in contact mode
69 // Other modes need the untranslated data for export and other functions
70 if (is_array($query->_select
) && $query->_mode
== CRM_Contact_BAO_Query
::MODE_CONTACTS
) {
71 foreach ($query->_select
as $field => $queryString) {
72 if (substr($field, -11) == 'campaign_id') {
73 $query->_pseudoConstantsSelect
[$field] = [
74 'pseudoField' => 'campaign_id',
76 'bao' => 'CRM_Activity_BAO_Activity',
82 //get survey clause in force,
83 //only when we have survey id.
84 if (!self
::$_applySurveyClause) {
88 //all below tables are require to fetch result.
90 //1. get survey activity target table in.
91 $query->_select
['survey_activity_target_contact_id'] = 'civicrm_activity_target.contact_id as survey_activity_target_contact_id';
92 $query->_select
['survey_activity_target_id'] = 'civicrm_activity_target.id as survey_activity_target_id';
93 $query->_element
['survey_activity_target_id'] = 1;
94 $query->_element
['survey_activity_target_contact_id'] = 1;
95 $query->_tables
[self
::CIVICRM_ACTIVITY_TARGET
] = 1;
96 $query->_whereTables
[self
::CIVICRM_ACTIVITY_TARGET
] = 1;
98 //2. get survey activity table in.
99 $query->_select
['survey_activity_id'] = 'civicrm_activity.id as survey_activity_id';
100 $query->_element
['survey_activity_id'] = 1;
101 $query->_tables
[self
::CIVICRM_ACTIVITY
] = 1;
102 $query->_whereTables
[self
::CIVICRM_ACTIVITY
] = 1;
104 //3. get the assignee table in.
105 $query->_select
['survey_interviewer_id'] = 'civicrm_activity_assignment.id as survey_interviewer_id';
106 $query->_element
['survey_interviewer_id'] = 1;
107 $query->_tables
[self
::CIVICRM_ACTIVITY_ASSIGNMENT
] = 1;
108 $query->_whereTables
[self
::CIVICRM_ACTIVITY_ASSIGNMENT
] = 1;
110 //4. get survey table.
111 $query->_select
['campaign_survey_id'] = 'civicrm_survey.id as campaign_survey_id';
112 $query->_element
['campaign_survey_id'] = 1;
113 $query->_tables
['civicrm_survey'] = 1;
114 $query->_whereTables
['civicrm_survey'] = 1;
116 //5. get campaign table.
117 $query->_select
['campaign_id'] = 'civicrm_campaign.id as campaign_id';
118 $query->_element
['campaign_id'] = 1;
119 $query->_tables
['civicrm_campaign'] = 1;
120 $query->_whereTables
['civicrm_campaign'] = 1;
126 public static function where(&$query) {
127 //get survey clause in force,
128 //only when we have survey id.
129 if (!self
::$_applySurveyClause) {
134 foreach (array_keys($query->_params
) as $id) {
135 if ($query->_mode
== CRM_Contact_BAO_Query
::MODE_CONTACTS
) {
136 $query->_useDistinct
= TRUE;
139 self
::whereClauseSingle($query->_params
[$id], $query);
147 public static function whereClauseSingle(&$values, &$query) {
148 //get survey clause in force,
149 //only when we have survey id.
150 if (!self
::$_applySurveyClause) {
154 list($name, $op, $value, $grouping, $wildcard) = $values;
157 case 'campaign_survey_id':
158 $query->_qill
[$grouping][] = ts('Survey - %1', [1 => CRM_Core_DAO
::getFieldValue('CRM_Campaign_DAO_Survey', $value, 'title')]);
160 $query->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause('civicrm_activity.source_record_id',
161 $op, $value, 'Integer'
163 $query->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause('civicrm_survey.id',
164 $op, $value, 'Integer'
168 case 'survey_status_id':
169 $activityStatus = CRM_Core_PseudoConstant
::activityStatus();
171 $query->_qill
[$grouping][] = ts('Survey Status - %1', [1 => $activityStatus[$value]]);
172 $query->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause('civicrm_activity.status_id',
173 $op, $value, 'Integer'
177 case 'campaign_search_voter_for':
178 if (in_array($value, ['release', 'interview'])) {
179 $query->_where
[$grouping][] = '(civicrm_activity.is_deleted = 0 OR civicrm_activity.is_deleted IS NULL)';
183 case 'survey_interviewer_id':
184 $surveyInterviewerName = CRM_Core_DAO
::getFieldValue('CRM_Contact_DAO_Contact', $value, 'sort_name');
185 $query->_qill
[$grouping][] = ts('Survey Interviewer - %1', [1 => $surveyInterviewerName]);
186 $query->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause('civicrm_activity_assignment.contact_id',
187 $op, $value, 'Integer'
194 * @param string $name
198 * @return null|string
200 public static function from($name, $mode, $side) {
202 //get survey clause in force,
203 //only when we have survey id.
204 if (!self
::$_applySurveyClause) {
208 $activityContacts = CRM_Activity_BAO_ActivityContact
::buildOptions('record_type_id', 'validate');
209 $sourceID = CRM_Utils_Array
::key('Activity Source', $activityContacts);
210 $assigneeID = CRM_Utils_Array
::key('Activity Assignees', $activityContacts);
211 $targetID = CRM_Utils_Array
::key('Activity Targets', $activityContacts);
214 case self
::CIVICRM_ACTIVITY_TARGET
:
215 $from = " INNER JOIN civicrm_activity_contact civicrm_activity_target
216 ON ( civicrm_activity_target.contact_id = contact_a.id AND civicrm_activity_target.record_type_id = $targetID) ";
219 case self
::CIVICRM_ACTIVITY
:
220 $surveyActivityTypes = CRM_Campaign_PseudoConstant
::activityType();
221 $surveyKeys = "(" . implode(',', array_keys($surveyActivityTypes)) . ")";
222 $from = " INNER JOIN civicrm_activity ON ( civicrm_activity.id = civicrm_activity_target.activity_id
223 AND civicrm_activity.activity_type_id IN $surveyKeys ) ";
226 case self
::CIVICRM_ACTIVITY_ASSIGNMENT
:
228 INNER JOIN civicrm_activity_contact civicrm_activity_assignment ON ( civicrm_activity.id = civicrm_activity_assignment.activity_id AND
229 civicrm_activity_assignment.record_type_id = $assigneeID ) ";
232 case 'civicrm_survey':
233 $from = " INNER JOIN civicrm_survey ON ( civicrm_survey.id = civicrm_activity.source_record_id ) ";
236 case 'civicrm_campaign':
237 $from = " $side JOIN civicrm_campaign ON ( civicrm_campaign.id = civicrm_survey.campaign_id ) ";
246 * @param bool $includeCustomFields
250 public static function defaultReturnProperties(
252 $includeCustomFields = TRUE
255 if ($mode & CRM_Contact_BAO_Query
::MODE_CAMPAIGN
) {
259 'contact_sub_type' => 1,
264 'street_number' => 1,
265 'street_address' => 1,
268 'state_province' => 1,
272 'survey_activity_target_id' => 1,
273 'survey_activity_id' => 1,
274 'survey_status_id' => 1,
275 'campaign_survey_id' => 1,
277 'survey_interviewer_id' => 1,
278 'survey_activity_target_contact_id' => 1,
288 public static function tableNames(&$tables) {
295 public static function searchAction(&$row, $id) {
301 public static function info(&$tables) {
302 //get survey clause in force,
303 //only when we have survey id.
304 if (!self
::$_applySurveyClause) {
308 $weight = end($tables);
309 $tables[self
::CIVICRM_ACTIVITY_TARGET
] = ++
$weight;
310 $tables[self
::CIVICRM_ACTIVITY
] = ++
$weight;
311 $tables[self
::CIVICRM_ACTIVITY_ASSIGNMENT
] = ++
$weight;
312 $tables['civicrm_survey'] = ++
$weight;
313 $tables['civicrm_campaign'] = ++
$weight;
317 * Add all the elements shared between,
318 * normal voter search and voter listing (GOTV form)
320 * @param CRM_Core_Form $form
322 public static function buildSearchForm(&$form) {
324 $attributes = CRM_Core_DAO
::getAttribute('CRM_Core_DAO_Address');
325 $className = CRM_Utils_System
::getClassName($form);
327 $form->add('text', 'sort_name', ts('Contact Name'),
328 CRM_Core_DAO
::getAttribute('CRM_Contact_DAO_Contact', 'sort_name')
330 $form->add('text', 'street_name', ts('Street Name'), $attributes['street_name']);
331 $form->add('text', 'street_number', ts('Street Number'), $attributes['street_number']);
332 $form->add('text', 'street_unit', ts('Street Unit'), $attributes['street_unit']);
333 $form->add('text', 'street_address', ts('Street Address'), $attributes['street_address']);
334 $form->add('text', 'city', ts('City'), $attributes['city']);
335 $form->add('text', 'postal_code', ts('Postal Code'), $attributes['postal_code']);
337 //@todo FIXME - using the CRM_Core_DAO::VALUE_SEPARATOR creates invalid html - if you can find the form
338 // this is loaded onto then replace with something like '__' & test
339 $separator = CRM_Core_DAO
::VALUE_SEPARATOR
;
340 $contactTypes = CRM_Contact_BAO_ContactType
::getSelectElements(FALSE, TRUE, $separator);
341 $form->add('select', 'contact_type', ts('Contact Type(s)'), $contactTypes, FALSE,
342 ['id' => 'contact_type', 'multiple' => 'multiple', 'class' => 'crm-select2']
344 $groups = CRM_Core_PseudoConstant
::nestedGroup();
345 $form->add('select', 'group', ts('Groups'), $groups, FALSE,
346 ['multiple' => 'multiple', 'class' => 'crm-select2']
349 $showInterviewer = FALSE;
350 if (CRM_Core_Permission
::check('manage campaign')) {
351 $showInterviewer = TRUE;
353 $form->assign('showInterviewer', $showInterviewer);
355 if ($showInterviewer ||
356 $className == 'CRM_Campaign_Form_Gotv'
359 $form->addEntityRef('survey_interviewer_id', ts('Interviewer'), ['class' => 'big']);
362 if (isset($form->_interviewerId
) && $form->_interviewerId
) {
363 $userId = $form->_interviewerId
;
366 $session = CRM_Core_Session
::singleton();
367 $userId = $session->get('userID');
371 $defaults['survey_interviewer_id'] = $userId;
372 $form->setDefaults($defaults);
376 //build ward and precinct custom fields.
378 SELECT fld.id, fld.label
379 FROM civicrm_custom_field fld
380 INNER JOIN civicrm_custom_group grp on fld.custom_group_id = grp.id
381 WHERE grp.name = %1';
382 $dao = CRM_Core_DAO
::executeQuery($query, [1 => ['Voter_Info', 'String']]);
383 $customSearchFields = [];
384 while ($dao->fetch()) {
385 foreach (['ward', 'precinct'] as $name) {
386 if (stripos($name, $dao->label
) !== FALSE) {
388 $fieldName = 'custom_' . $dao->id
;
389 $customSearchFields[$name] = $fieldName;
390 CRM_Core_BAO_CustomField
::addQuickFormElement($form, $fieldName, $fieldId, FALSE);
395 $form->assign('customSearchFields', $customSearchFields);
397 $surveys = CRM_Campaign_BAO_Survey
::getSurveys();
399 if (empty($surveys) &&
400 ($className == 'CRM_Campaign_Form_Search')
402 CRM_Core_Error
::statusBounce(ts('Could not find survey for %1 respondents.',
403 [1 => $form->get('op')]
405 CRM_Utils_System
::url('civicrm/survey/add',
412 //If survey had associated campaign and
413 //campaign has some contact groups, don't
414 //allow to search the contacts those are not
415 //in given campaign groups ( ie not in constituents )
416 $props = ['class' => 'crm-select2'];
417 if ($form->get('searchVoterFor') == 'reserve') {
418 $props['onChange'] = "buildCampaignGroups( );return false;";
420 $form->add('select', 'campaign_survey_id', ts('Survey'), $surveys, TRUE, $props);
424 * Retrieve all valid voter ids,
425 * and build respective clause to restrict search.
427 * @param array $criteria
429 * @return $voterClause as a string
433 * @param array $params
437 public static function voterClause($params) {
439 $fromClause = $whereClause = NULL;
440 if (!is_array($params) ||
empty($params)) {
443 $surveyId = $params['campaign_survey_id'] ??
NULL;
444 $searchVoterFor = $params['campaign_search_voter_for'] ??
NULL;
446 //get the survey activities.
447 $activityStatus = CRM_Core_PseudoConstant
::activityStatus('name');
448 $status = ['Scheduled'];
449 if ($searchVoterFor == 'reserve') {
450 $status[] = 'Completed';
453 $completedStatusId = NULL;
454 foreach ($status as $name) {
455 if ($statusId = array_search($name, $activityStatus)) {
456 $statusIds[] = $statusId;
457 if ($name == 'Completed') {
458 $completedStatusId = $statusId;
463 $voterActValues = CRM_Campaign_BAO_Survey
::getSurveyVoterInfo($surveyId, NULL, $statusIds);
465 if (!empty($voterActValues)) {
467 $voterIds = array_keys($voterActValues);
468 if ($searchVoterFor == 'reserve') {
469 $operator = 'NOT IN';
470 //filter out recontact survey contacts.
471 $recontactInterval = CRM_Core_DAO
::getFieldValue('CRM_Campaign_DAO_Survey',
472 $surveyId, 'recontact_interval'
474 $recontactInterval = CRM_Utils_String
::unserialize($recontactInterval);
476 is_array($recontactInterval) &&
477 !empty($recontactInterval)
480 foreach ($voterActValues as $values) {
481 $numOfDays = $recontactInterval[$values['result']] ??
NULL;
483 $values['status_id'] == $completedStatusId
485 $recontactIntSeconds = $numOfDays * 24 * 3600;
486 $actDateTimeSeconds = CRM_Utils_Date
::unixTime($values['activity_date_time']);
487 $totalSeconds = $recontactIntSeconds +
$actDateTimeSeconds;
488 //don't consider completed survey activity
489 //unless it fulfill recontact interval criteria.
490 if ($totalSeconds <= time()) {
494 $voterIds[$values['voter_id']] = $values['voter_id'];
499 //lets dump these ids in tmp table and
500 //use appropriate join depend on operator.
501 if (!empty($voterIds)) {
502 $voterIdCount = count($voterIds);
504 //create temporary table to store voter ids.
505 $tempTable = CRM_Utils_SQL_TempTable
::build();
506 $tempTableName = $tempTable->getName();
507 CRM_Core_DAO
::executeQuery("DROP TEMPORARY TABLE IF EXISTS {$tempTableName}");
508 $tempTable->createWithColumns('id int unsigned NOT NULL AUTO_INCREMENT, survey_contact_id int unsigned NOT NULL, PRIMARY KEY ( id )');
513 $processIds = $voterIds;
514 $insertIds = array_splice($processIds, $insertedCount, $batch);
515 if (!empty($insertIds)) {
516 $insertSQL = "INSERT IGNORE INTO {$tempTableName}( survey_contact_id )
517 VALUES (" . implode('),(', $insertIds) . ');';
518 CRM_Core_DAO
::executeQuery($insertSQL);
520 $insertedCount +
= $batch;
521 } while ($insertedCount < $voterIdCount);
523 if ($operator == 'IN') {
524 $fromClause = " INNER JOIN {$tempTableName} ON ( {$tempTableName}.survey_contact_id = contact_a.id )";
527 $fromClause = " LEFT JOIN {$tempTableName} ON ( {$tempTableName}.survey_contact_id = contact_a.id )";
528 $whereClause = "( {$tempTableName}.survey_contact_id IS NULL )";
533 'fromClause' => $fromClause,
534 'whereClause' => $whereClause,