4 +--------------------------------------------------------------------+
5 | CiviCRM version 4.5 |
6 +--------------------------------------------------------------------+
7 | Copyright CiviCRM LLC (c) 2004-2014 |
8 +--------------------------------------------------------------------+
9 | This file is a part of CiviCRM. |
11 | CiviCRM is free software; you can copy, modify, and distribute it |
12 | under the terms of the GNU Affero General Public License |
13 | Version 3, 19 November 2007 and the CiviCRM Licensing Exception. |
15 | CiviCRM is distributed in the hope that it will be useful, but |
16 | WITHOUT ANY WARRANTY; without even the implied warranty of |
17 | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. |
18 | See the GNU Affero General Public License for more details. |
20 | You should have received a copy of the GNU Affero General Public |
21 | License and the CiviCRM Licensing Exception along |
22 | with this program; if not, contact CiviCRM LLC |
23 | at info[AT]civicrm[DOT]org. If you have questions about the |
24 | GNU Affero General Public License or the licensing of CiviCRM, |
25 | see the CiviCRM license FAQ at http://civicrm.org/licensing |
26 +--------------------------------------------------------------------+
32 * @copyright CiviCRM LLC (c) 2004-2014
36 class CRM_Campaign_BAO_Query
{
37 //since normal activity clause clause get collides.
39 CIVICRM_ACTIVITY
= 'civicrm_survey_activity',
40 CIVICRM_ACTIVITY_TARGET
= 'civicrm_survey_activity_target',
41 CIVICRM_ACTIVITY_ASSIGNMENT
= 'civicrm_survey_activity_assignment';
44 * static field for all the campaign fields
49 static $_campaignFields = NULL;
51 static $_applySurveyClause = FALSE;
54 * Function get the fields for campaign.
56 * @return array self::$_campaignFields an associative array of campaign fields
59 static function &getFields() {
60 if (!isset(self
::$_campaignFields)) {
61 self
::$_campaignFields = array();
64 return self
::$_campaignFields;
68 * if survey, campaign are involved, add the specific fields.
75 static function select(&$query) {
76 self
::$_applySurveyClause = FALSE;
77 if (is_array($query->_params
)) {
78 foreach ($query->_params
as $values) {
79 if (!is_array($values) ||
count($values) != 5) {
83 list($name, $op, $value, $grouping, $wildcard) = $values;
84 if ($name == 'campaign_survey_id') {
85 self
::$_applySurveyClause = TRUE;
90 // CRM-13810 Translate campaign_id to label for search builder
91 // CRM-14238 Only translate when we are in contact mode
92 // Other modes need the untranslated data for export and other functions
93 if (is_array($query->_select
) && $query->_mode
== CRM_Contact_BAO_Query
::MODE_CONTACTS
) {
94 foreach($query->_select
as $field => $queryString) {
95 if (substr($field, -11) == 'campaign_id') {
96 $query->_pseudoConstantsSelect
[$field] = array(
97 'pseudoField' => 'campaign_id',
99 'bao' => 'CRM_Activity_BAO_Activity',
106 //get survey clause in force,
107 //only when we have survey id.
108 if (!self
::$_applySurveyClause) {
112 //all below tables are require to fetch result.
114 //1. get survey activity target table in.
115 $query->_select
['survey_activity_target_contact_id'] = 'civicrm_activity_target.contact_id as survey_activity_target_contact_id';
116 $query->_select
['survey_activity_target_id'] = 'civicrm_activity_target.id as survey_activity_target_id';
117 $query->_element
['survey_activity_target_id'] = 1;
118 $query->_element
['survey_activity_target_contact_id'] = 1;
119 $query->_tables
[self
::CIVICRM_ACTIVITY_TARGET
] = 1;
120 $query->_whereTables
[self
::CIVICRM_ACTIVITY_TARGET
] = 1;
122 //2. get survey activity table in.
123 $query->_select
['survey_activity_id'] = 'civicrm_activity.id as survey_activity_id';
124 $query->_element
['survey_activity_id'] = 1;
125 $query->_tables
[self
::CIVICRM_ACTIVITY
] = 1;
126 $query->_whereTables
[self
::CIVICRM_ACTIVITY
] = 1;
128 //3. get the assignee table in.
129 $query->_select
['survey_interviewer_id'] = 'civicrm_activity_assignment.id as survey_interviewer_id';
130 $query->_element
['survey_interviewer_id'] = 1;
131 $query->_tables
[self
::CIVICRM_ACTIVITY_ASSIGNMENT
] = 1;
132 $query->_whereTables
[self
::CIVICRM_ACTIVITY_ASSIGNMENT
] = 1;
134 //4. get survey table.
135 $query->_select
['campaign_survey_id'] = 'civicrm_survey.id as campaign_survey_id';
136 $query->_element
['campaign_survey_id'] = 1;
137 $query->_tables
['civicrm_survey'] = 1;
138 $query->_whereTables
['civicrm_survey'] = 1;
140 //5. get campaign table.
141 $query->_select
['campaign_id'] = 'civicrm_campaign.id as campaign_id';
142 $query->_element
['campaign_id'] = 1;
143 $query->_tables
['civicrm_campaign'] = 1;
144 $query->_whereTables
['civicrm_campaign'] = 1;
150 static function where(&$query) {
151 //get survey clause in force,
152 //only when we have survey id.
153 if (!self
::$_applySurveyClause) {
158 foreach (array_keys($query->_params
) as $id) {
159 if ($query->_mode
== CRM_Contact_BAO_Query
::MODE_CONTACTS
) {
160 $query->_useDistinct
= TRUE;
163 self
::whereClauseSingle($query->_params
[$id], $query);
171 static function whereClauseSingle(&$values, &$query) {
172 //get survey clause in force,
173 //only when we have survey id.
174 if (!self
::$_applySurveyClause) {
178 list($name, $op, $value, $grouping, $wildcard) = $values;
181 case 'campaign_survey_id':
182 $query->_qill
[$grouping][] = ts('Survey - %1', array(1 => CRM_Core_DAO
::getFieldValue('CRM_Campaign_DAO_Survey', $value, 'title')));
184 $query->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause('civicrm_activity.source_record_id',
185 $op, $value, 'Integer'
187 $query->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause('civicrm_survey.id',
188 $op, $value, 'Integer'
192 case 'survey_status_id':
193 $activityStatus = CRM_Core_PseudoConstant
::activityStatus();
195 $query->_qill
[$grouping][] = ts('Survey Status - %1', array(1 => $activityStatus[$value]));
196 $query->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause('civicrm_activity.status_id',
197 $op, $value, 'Integer'
201 case 'campaign_search_voter_for':
202 if (in_array($value, array('release', 'interview'))) {
203 $query->_where
[$grouping][] = '(civicrm_activity.is_deleted = 0 OR civicrm_activity.is_deleted IS NULL)';
207 case 'survey_interviewer_id':
208 $surveyInterviewerName = CRM_Core_DAO
::getFieldValue('CRM_Contact_DAO_Contact', $value, 'sort_name');
209 $query->_qill
[$grouping][] = ts('Survey Interviewer - %1', array(1 => $surveyInterviewerName));
210 $query->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause('civicrm_activity_assignment.contact_id',
211 $op, $value, 'Integer'
222 * @return null|string
224 static function from($name, $mode, $side) {
226 //get survey clause in force,
227 //only when we have survey id.
228 if (!self
::$_applySurveyClause) {
232 $activityContacts = CRM_Core_OptionGroup
::values('activity_contacts', FALSE, FALSE, FALSE, NULL, 'name');
233 $sourceID = CRM_Utils_Array
::key('Activity Source', $activityContacts);
234 $assigneeID = CRM_Utils_Array
::key('Activity Assignees', $activityContacts);
235 $targetID = CRM_Utils_Array
::key('Activity Targets', $activityContacts);
238 case self
::CIVICRM_ACTIVITY_TARGET
:
239 $from = " INNER JOIN civicrm_activity_contact civicrm_activity_target
240 ON ( civicrm_activity_target.contact_id = contact_a.id AND civicrm_activity_target.record_type_id = $targetID) ";
243 case self
::CIVICRM_ACTIVITY
:
244 $surveyActivityTypes = CRM_Campaign_PseudoConstant
::activityType();
245 $surveyKeys = "(" . implode(',', array_keys($surveyActivityTypes)) . ")";
246 $from = " INNER JOIN civicrm_activity ON ( civicrm_activity.id = civicrm_activity_target.activity_id
247 AND civicrm_activity.activity_type_id IN $surveyKeys ) ";
250 case self
::CIVICRM_ACTIVITY_ASSIGNMENT
:
252 INNER JOIN civicrm_activity_contact civicrm_activity_assignment ON ( civicrm_activity.id = civicrm_activity_assignment.activity_id AND
253 civicrm_activity_assignment.record_type_id = $assigneeID ) ";
256 case 'civicrm_survey':
257 $from = " INNER JOIN civicrm_survey ON ( civicrm_survey.id = civicrm_activity.source_record_id ) ";
260 case 'civicrm_campaign':
261 $from = " $side JOIN civicrm_campaign ON ( civicrm_campaign.id = civicrm_survey.campaign_id ) ";
270 * @param bool $includeCustomFields
274 static function defaultReturnProperties($mode,
275 $includeCustomFields = TRUE
278 if ($mode & CRM_Contact_BAO_Query
::MODE_CAMPAIGN
) {
282 'contact_sub_type' => 1,
287 'street_number' => 1,
288 'street_address' => 1,
291 'state_province' => 1,
295 'survey_activity_target_id' => 1,
296 'survey_activity_id' => 1,
297 'survey_status_id' => 1,
298 'campaign_survey_id' => 1,
300 'survey_interviewer_id' => 1,
301 'survey_activity_target_contact_id' => 1,
311 static function tableNames(&$tables) {}
317 static function searchAction(&$row, $id) {}
322 static function info(&$tables) {
323 //get survey clause in force,
324 //only when we have survey id.
325 if (!self
::$_applySurveyClause) {
329 $weight = end($tables);
330 $tables[self
::CIVICRM_ACTIVITY_TARGET
] = ++
$weight;
331 $tables[self
::CIVICRM_ACTIVITY
] = ++
$weight;
332 $tables[self
::CIVICRM_ACTIVITY_ASSIGNMENT
] = ++
$weight;
333 $tables['civicrm_survey'] = ++
$weight;
334 $tables['civicrm_campaign'] = ++
$weight;
338 * add all the elements shared between,
339 * normal voter search and voter listing (GOTV form)
343 * @param CRM_Core_Form $form
347 static function buildSearchForm(&$form) {
349 $attributes = CRM_Core_DAO
::getAttribute('CRM_Core_DAO_Address');
350 $className = CRM_Utils_System
::getClassName($form);
352 $form->add('text', 'sort_name', ts('Contact Name'),
353 CRM_Core_DAO
::getAttribute('CRM_Contact_DAO_Contact', 'sort_name')
355 $form->add('text', 'street_name', ts('Street Name'), $attributes['street_name']);
356 $form->add('text', 'street_number', ts('Street Number'), $attributes['street_number']);
357 $form->add('text', 'street_unit', ts('Street Unit'), $attributes['street_unit']);
358 $form->add('text', 'street_address', ts('Street Address'), $attributes['street_address']);
359 $form->add('text', 'city', ts('City'), $attributes['city']);
360 $form->add('text', 'postal_code', ts('Zip / Postal Code'), $attributes['postal_code']);
362 //@todo FIXME - using the CRM_Core_DAO::VALUE_SEPARATOR creates invalid html - if you can find the form
363 // this is loaded onto then replace with something like '__' & test
364 $separator = CRM_Core_DAO
::VALUE_SEPARATOR
;
365 $contactTypes = CRM_Contact_BAO_ContactType
::getSelectElements(FALSE, TRUE, $separator);
366 $form->add('select', 'contact_type', ts('Contact Type(s)'), $contactTypes, FALSE,
367 array('id' => 'contact_type', 'multiple' => 'multiple', 'class' => 'crm-select2')
369 $groups = CRM_Core_PseudoConstant
::group();
370 $form->add('select', 'group', ts('Groups'), $groups, FALSE,
371 array('id' => 'group', 'multiple' => 'multiple', 'class' => 'crm-select2')
374 $showInterviewer = FALSE;
375 if (CRM_Core_Permission
::check('administer CiviCampaign')) {
376 $showInterviewer = TRUE;
378 $form->assign('showInterviewer', $showInterviewer);
380 if ($showInterviewer ||
381 $className == 'CRM_Campaign_Form_Gotv'
384 $form->addEntityRef('survey_interviewer_id', ts('Interviewer'), array('class' => 'big'));
387 if (isset($form->_interviewerId
) && $form->_interviewerId
) {
388 $userId = $form->_interviewerId
;
391 $session = CRM_Core_Session
::singleton();
392 $userId = $session->get('userID');
396 $defaults['survey_interviewer_id'] = $userId;
397 $form->setDefaults($defaults);
401 //build ward and precinct custom fields.
403 SELECT fld.id, fld.label
404 FROM civicrm_custom_field fld
405 INNER JOIN civicrm_custom_group grp on fld.custom_group_id = grp.id
406 WHERE grp.name = %1';
407 $dao = CRM_Core_DAO
::executeQuery($query, array(1 => array('Voter_Info', 'String')));
408 $customSearchFields = array();
409 while ($dao->fetch()) {
411 'ward', 'precinct') as $name) {
412 if (stripos($name, $dao->label
) !== FALSE) {
414 $fieldName = 'custom_' . $dao->id
;
415 $customSearchFields[$name] = $fieldName;
416 CRM_Core_BAO_CustomField
::addQuickFormElement($form, $fieldName, $fieldId, FALSE, FALSE);
421 $form->assign('customSearchFields', $customSearchFields);
423 $surveys = CRM_Campaign_BAO_Survey
::getSurveys();
425 if (empty($surveys) &&
426 ($className == 'CRM_Campaign_Form_Search')
428 CRM_Core_Error
::statusBounce(ts('Could not find survey for %1 respondents.',
429 array(1 => $form->get('op'))
431 CRM_Utils_System
::url('civicrm/survey/add',
438 //If survey had associated campaign and
439 //campaign has some contact groups, don't
440 //allow to search the contacts those are not
441 //in given campaign groups ( ie not in constituents )
442 $props = array('class' => 'crm-select2');
443 if ($form->get('searchVoterFor') == 'reserve') {
444 $props['onChange'] = "buildCampaignGroups( );return false;";
446 $form->add('select', 'campaign_survey_id', ts('Survey'), $surveys, TRUE, $props);
450 * Retrieve all valid voter ids,
451 * and build respective clause to restrict search.
453 * @param array $criteria an array
454 * @return $voterClause as a string
462 static public function voterClause($params) {
463 $voterClause = array();
464 $fromClause = $whereClause = NULL;
465 if (!is_array($params) ||
empty($params)) {
468 $surveyId = CRM_Utils_Array
::value('campaign_survey_id', $params);
469 $searchVoterFor = CRM_Utils_Array
::value('campaign_search_voter_for', $params);
471 //get the survey activities.
472 $activityStatus = CRM_Core_PseudoConstant
::activityStatus('name');
473 $status = array('Scheduled');
474 if ($searchVoterFor == 'reserve') {
475 $status[] = 'Completed';
478 $completedStatusId = NULL;
479 foreach ($status as $name) {
480 if ($statusId = array_search($name, $activityStatus)) {
481 $statusIds[] = $statusId;
482 if ($name == 'Completed') {
483 $completedStatusId = $statusId;
488 $voterActValues = CRM_Campaign_BAO_Survey
::getSurveyVoterInfo($surveyId, NULL, $statusIds);
490 if (!empty($voterActValues)) {
492 $voterIds = array_keys($voterActValues);
493 if ($searchVoterFor == 'reserve') {
494 $operator = 'NOT IN';
495 //filter out recontact survey contacts.
496 $recontactInterval = CRM_Core_DAO
::getFieldValue('CRM_Campaign_DAO_Survey',
497 $surveyId, 'recontact_interval'
499 $recontactInterval = unserialize($recontactInterval);
501 is_array($recontactInterval) &&
502 !empty($recontactInterval)
505 foreach ($voterActValues as $values) {
506 $numOfDays = CRM_Utils_Array
::value($values['result'], $recontactInterval);
508 $values['status_id'] == $completedStatusId
510 $recontactIntSeconds = $numOfDays * 24 * 3600;
511 $actDateTimeSeconds = CRM_Utils_Date
::unixTime($values['activity_date_time']);
512 $totalSeconds = $recontactIntSeconds +
$actDateTimeSeconds;
513 //don't consider completed survey activity
514 //unless it fulfill recontact interval criteria.
515 if ($totalSeconds <= time()) {
519 $voterIds[$values['voter_id']] = $values['voter_id'];
524 //lets dump these ids in tmp table and
525 //use appropriate join depend on operator.
526 if (!empty($voterIds)) {
527 $voterIdCount = count($voterIds);
529 //create temporary table to store voter ids.
530 $tempTableName = CRM_Core_DAO
::createTempTableName('civicrm_survey_respondent');
531 CRM_Core_DAO
::executeQuery("DROP TABLE IF EXISTS {$tempTableName}");
534 CREATE TEMPORARY TABLE {$tempTableName} (
535 id int unsigned NOT NULL AUTO_INCREMENT,
536 survey_contact_id int unsigned NOT NULL,
540 CRM_Core_DAO
::executeQuery($query);
545 $processIds = $voterIds;
546 $insertIds = array_splice($processIds, $insertedCount, $batch);
547 if (!empty($insertIds)) {
548 $insertSQL = "INSERT IGNORE INTO {$tempTableName}( survey_contact_id )
549 VALUES (" . implode('),(', $insertIds) . ');';
550 CRM_Core_DAO
::executeQuery($insertSQL);
552 $insertedCount +
= $batch;
553 } while ($insertedCount < $voterIdCount);
555 if ($operator == 'IN') {
556 $fromClause = " INNER JOIN {$tempTableName} ON ( {$tempTableName}.survey_contact_id = contact_a.id )";
559 $fromClause = " LEFT JOIN {$tempTableName} ON ( {$tempTableName}.survey_contact_id = contact_a.id )";
560 $whereClause = "( {$tempTableName}.survey_contact_id IS NULL )";
564 $voterClause = array(
565 'fromClause' => $fromClause,
566 'whereClause' => $whereClause,
573 * Build the campaign clause for component serach.
576 public static function componentSearchClause(&$params, &$query) {
577 $op = CRM_Utils_Array
::value('op', $params, '=');
578 $campaign = CRM_Utils_Array
::value('campaign', $params);
579 $tableName = CRM_Utils_Array
::value('tableName', $params);
580 $grouping = CRM_Utils_Array
::value('grouping', $params);
581 if (CRM_Utils_System
::isNull($campaign) ||
empty($tableName)) {
585 // fixme - what is the purpose of this code? $campaign should be
586 // an integer, not an array
587 if (is_array($campaign)) {
589 'current_campaign', 'past_campaign') as $ignore) {
590 $index = array_search($ignore, $campaign);
591 if ($index !== FALSE)unset($campaign[$index]);
595 $allCampaigns = CRM_Campaign_BAO_Campaign
::getCampaigns(NULL, NULL, FALSE, FALSE, FALSE, TRUE);
597 $campaignIds = $campaignTitles = array();
598 if (is_array($campaign)) {
599 foreach ($campaign as $campId) {
600 $campaignIds[$campId] = $campId;
601 $campaignTitles[$campId] = $allCampaigns[$campId];
603 if (count($campaignIds) > 1) {
605 $campaignIds = '(' . implode(',', $campaignIds) . ')';
608 $campaignIds = reset($campaignIds);
612 $campaignIds = $campaign;
613 if (array_key_exists($campaignIds, $allCampaigns)) {
614 $campaignTitles[$campaignIds] = $allCampaigns[$campaignIds];
617 $query->_qill
[$grouping][] = ts('Campaigns %1',
619 ) . ' ' . implode(' ' . ts('or') . ' ', $campaignTitles);
620 $query->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause("{$tableName}.campaign_id",
625 $query->_tables
[$tableName] = $query->_whereTables
[$tableName] = 1;