3 +--------------------------------------------------------------------+
4 | CiviCRM version 4.6 |
5 +--------------------------------------------------------------------+
6 | Copyright CiviCRM LLC (c) 2004-2015 |
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-2015
35 class CRM_Campaign_BAO_Query
{
36 //since normal activity clause clause get collides.
38 CIVICRM_ACTIVITY
= 'civicrm_survey_activity',
39 CIVICRM_ACTIVITY_TARGET
= 'civicrm_survey_activity_target',
40 CIVICRM_ACTIVITY_ASSIGNMENT
= 'civicrm_survey_activity_assignment';
43 * Static field for all the campaign fields.
47 static $_campaignFields = NULL;
49 static $_applySurveyClause = FALSE;
52 * Function get the fields for campaign.
55 * self::$_campaignFields an associative array of campaign fields
57 public static function &getFields() {
58 if (!isset(self
::$_campaignFields)) {
59 self
::$_campaignFields = array();
62 return self
::$_campaignFields;
66 * If survey, campaign are involved, add the specific fields.
72 public static function select(&$query) {
73 self
::$_applySurveyClause = FALSE;
74 if (is_array($query->_params
)) {
75 foreach ($query->_params
as $values) {
76 if (!is_array($values) ||
count($values) != 5) {
80 list($name, $op, $value, $grouping, $wildcard) = $values;
81 if ($name == 'campaign_survey_id') {
82 self
::$_applySurveyClause = TRUE;
87 // CRM-13810 Translate campaign_id to label for search builder
88 // CRM-14238 Only translate when we are in contact mode
89 // Other modes need the untranslated data for export and other functions
90 if (is_array($query->_select
) && $query->_mode
== CRM_Contact_BAO_Query
::MODE_CONTACTS
) {
91 foreach ($query->_select
as $field => $queryString) {
92 if (substr($field, -11) == 'campaign_id') {
93 $query->_pseudoConstantsSelect
[$field] = array(
94 'pseudoField' => 'campaign_id',
96 'bao' => 'CRM_Activity_BAO_Activity',
102 //get survey clause in force,
103 //only when we have survey id.
104 if (!self
::$_applySurveyClause) {
108 //all below tables are require to fetch result.
110 //1. get survey activity target table in.
111 $query->_select
['survey_activity_target_contact_id'] = 'civicrm_activity_target.contact_id as survey_activity_target_contact_id';
112 $query->_select
['survey_activity_target_id'] = 'civicrm_activity_target.id as survey_activity_target_id';
113 $query->_element
['survey_activity_target_id'] = 1;
114 $query->_element
['survey_activity_target_contact_id'] = 1;
115 $query->_tables
[self
::CIVICRM_ACTIVITY_TARGET
] = 1;
116 $query->_whereTables
[self
::CIVICRM_ACTIVITY_TARGET
] = 1;
118 //2. get survey activity table in.
119 $query->_select
['survey_activity_id'] = 'civicrm_activity.id as survey_activity_id';
120 $query->_element
['survey_activity_id'] = 1;
121 $query->_tables
[self
::CIVICRM_ACTIVITY
] = 1;
122 $query->_whereTables
[self
::CIVICRM_ACTIVITY
] = 1;
124 //3. get the assignee table in.
125 $query->_select
['survey_interviewer_id'] = 'civicrm_activity_assignment.id as survey_interviewer_id';
126 $query->_element
['survey_interviewer_id'] = 1;
127 $query->_tables
[self
::CIVICRM_ACTIVITY_ASSIGNMENT
] = 1;
128 $query->_whereTables
[self
::CIVICRM_ACTIVITY_ASSIGNMENT
] = 1;
130 //4. get survey table.
131 $query->_select
['campaign_survey_id'] = 'civicrm_survey.id as campaign_survey_id';
132 $query->_element
['campaign_survey_id'] = 1;
133 $query->_tables
['civicrm_survey'] = 1;
134 $query->_whereTables
['civicrm_survey'] = 1;
136 //5. get campaign table.
137 $query->_select
['campaign_id'] = 'civicrm_campaign.id as campaign_id';
138 $query->_element
['campaign_id'] = 1;
139 $query->_tables
['civicrm_campaign'] = 1;
140 $query->_whereTables
['civicrm_campaign'] = 1;
146 public static function where(&$query) {
147 //get survey clause in force,
148 //only when we have survey id.
149 if (!self
::$_applySurveyClause) {
154 foreach (array_keys($query->_params
) as $id) {
155 if ($query->_mode
== CRM_Contact_BAO_Query
::MODE_CONTACTS
) {
156 $query->_useDistinct
= TRUE;
159 self
::whereClauseSingle($query->_params
[$id], $query);
167 public static function whereClauseSingle(&$values, &$query) {
168 //get survey clause in force,
169 //only when we have survey id.
170 if (!self
::$_applySurveyClause) {
174 list($name, $op, $value, $grouping, $wildcard) = $values;
177 case 'campaign_survey_id':
178 $query->_qill
[$grouping][] = ts('Survey - %1', array(1 => CRM_Core_DAO
::getFieldValue('CRM_Campaign_DAO_Survey', $value, 'title')));
180 $query->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause('civicrm_activity.source_record_id',
181 $op, $value, 'Integer'
183 $query->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause('civicrm_survey.id',
184 $op, $value, 'Integer'
188 case 'survey_status_id':
189 $activityStatus = CRM_Core_PseudoConstant
::activityStatus();
191 $query->_qill
[$grouping][] = ts('Survey Status - %1', array(1 => $activityStatus[$value]));
192 $query->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause('civicrm_activity.status_id',
193 $op, $value, 'Integer'
197 case 'campaign_search_voter_for':
198 if (in_array($value, array('release', 'interview'))) {
199 $query->_where
[$grouping][] = '(civicrm_activity.is_deleted = 0 OR civicrm_activity.is_deleted IS NULL)';
203 case 'survey_interviewer_id':
204 $surveyInterviewerName = CRM_Core_DAO
::getFieldValue('CRM_Contact_DAO_Contact', $value, 'sort_name');
205 $query->_qill
[$grouping][] = ts('Survey Interviewer - %1', array(1 => $surveyInterviewerName));
206 $query->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause('civicrm_activity_assignment.contact_id',
207 $op, $value, 'Integer'
214 * @param string $name
218 * @return null|string
220 public static function from($name, $mode, $side) {
222 //get survey clause in force,
223 //only when we have survey id.
224 if (!self
::$_applySurveyClause) {
228 $activityContacts = CRM_Core_OptionGroup
::values('activity_contacts', FALSE, FALSE, FALSE, NULL, 'name');
229 $sourceID = CRM_Utils_Array
::key('Activity Source', $activityContacts);
230 $assigneeID = CRM_Utils_Array
::key('Activity Assignees', $activityContacts);
231 $targetID = CRM_Utils_Array
::key('Activity Targets', $activityContacts);
234 case self
::CIVICRM_ACTIVITY_TARGET
:
235 $from = " INNER JOIN civicrm_activity_contact civicrm_activity_target
236 ON ( civicrm_activity_target.contact_id = contact_a.id AND civicrm_activity_target.record_type_id = $targetID) ";
239 case self
::CIVICRM_ACTIVITY
:
240 $surveyActivityTypes = CRM_Campaign_PseudoConstant
::activityType();
241 $surveyKeys = "(" . implode(',', array_keys($surveyActivityTypes)) . ")";
242 $from = " INNER JOIN civicrm_activity ON ( civicrm_activity.id = civicrm_activity_target.activity_id
243 AND civicrm_activity.activity_type_id IN $surveyKeys ) ";
246 case self
::CIVICRM_ACTIVITY_ASSIGNMENT
:
248 INNER JOIN civicrm_activity_contact civicrm_activity_assignment ON ( civicrm_activity.id = civicrm_activity_assignment.activity_id AND
249 civicrm_activity_assignment.record_type_id = $assigneeID ) ";
252 case 'civicrm_survey':
253 $from = " INNER JOIN civicrm_survey ON ( civicrm_survey.id = civicrm_activity.source_record_id ) ";
256 case 'civicrm_campaign':
257 $from = " $side JOIN civicrm_campaign ON ( civicrm_campaign.id = civicrm_survey.campaign_id ) ";
266 * @param bool $includeCustomFields
270 public static function defaultReturnProperties(
272 $includeCustomFields = TRUE
275 if ($mode & CRM_Contact_BAO_Query
::MODE_CAMPAIGN
) {
279 'contact_sub_type' => 1,
284 'street_number' => 1,
285 'street_address' => 1,
288 'state_province' => 1,
292 'survey_activity_target_id' => 1,
293 'survey_activity_id' => 1,
294 'survey_status_id' => 1,
295 'campaign_survey_id' => 1,
297 'survey_interviewer_id' => 1,
298 'survey_activity_target_contact_id' => 1,
308 public static function tableNames(&$tables) {
315 public static function searchAction(&$row, $id) {
321 public static function info(&$tables) {
322 //get survey clause in force,
323 //only when we have survey id.
324 if (!self
::$_applySurveyClause) {
328 $weight = end($tables);
329 $tables[self
::CIVICRM_ACTIVITY_TARGET
] = ++
$weight;
330 $tables[self
::CIVICRM_ACTIVITY
] = ++
$weight;
331 $tables[self
::CIVICRM_ACTIVITY_ASSIGNMENT
] = ++
$weight;
332 $tables['civicrm_survey'] = ++
$weight;
333 $tables['civicrm_campaign'] = ++
$weight;
337 * Add all the elements shared between,
338 * normal voter search and voter listing (GOTV form)
341 * @param CRM_Core_Form $form
344 public static function buildSearchForm(&$form) {
346 $attributes = CRM_Core_DAO
::getAttribute('CRM_Core_DAO_Address');
347 $className = CRM_Utils_System
::getClassName($form);
349 $form->add('text', 'sort_name', ts('Contact Name'),
350 CRM_Core_DAO
::getAttribute('CRM_Contact_DAO_Contact', 'sort_name')
352 $form->add('text', 'street_name', ts('Street Name'), $attributes['street_name']);
353 $form->add('text', 'street_number', ts('Street Number'), $attributes['street_number']);
354 $form->add('text', 'street_unit', ts('Street Unit'), $attributes['street_unit']);
355 $form->add('text', 'street_address', ts('Street Address'), $attributes['street_address']);
356 $form->add('text', 'city', ts('City'), $attributes['city']);
357 $form->add('text', 'postal_code', ts('Zip / Postal Code'), $attributes['postal_code']);
359 //@todo FIXME - using the CRM_Core_DAO::VALUE_SEPARATOR creates invalid html - if you can find the form
360 // this is loaded onto then replace with something like '__' & test
361 $separator = CRM_Core_DAO
::VALUE_SEPARATOR
;
362 $contactTypes = CRM_Contact_BAO_ContactType
::getSelectElements(FALSE, TRUE, $separator);
363 $form->add('select', 'contact_type', ts('Contact Type(s)'), $contactTypes, FALSE,
364 array('id' => 'contact_type', 'multiple' => 'multiple', 'class' => 'crm-select2')
366 $groups = CRM_Core_PseudoConstant
::nestedGroup();
367 $form->add('select', 'group', ts('Groups'), $groups, FALSE,
368 array('multiple' => 'multiple', 'class' => 'crm-select2')
371 $showInterviewer = FALSE;
372 if (CRM_Core_Permission
::check('administer CiviCampaign')) {
373 $showInterviewer = TRUE;
375 $form->assign('showInterviewer', $showInterviewer);
377 if ($showInterviewer ||
378 $className == 'CRM_Campaign_Form_Gotv'
381 $form->addEntityRef('survey_interviewer_id', ts('Interviewer'), array('class' => 'big'));
384 if (isset($form->_interviewerId
) && $form->_interviewerId
) {
385 $userId = $form->_interviewerId
;
388 $session = CRM_Core_Session
::singleton();
389 $userId = $session->get('userID');
393 $defaults['survey_interviewer_id'] = $userId;
394 $form->setDefaults($defaults);
398 //build ward and precinct custom fields.
400 SELECT fld.id, fld.label
401 FROM civicrm_custom_field fld
402 INNER JOIN civicrm_custom_group grp on fld.custom_group_id = grp.id
403 WHERE grp.name = %1';
404 $dao = CRM_Core_DAO
::executeQuery($query, array(1 => array('Voter_Info', 'String')));
405 $customSearchFields = array();
406 while ($dao->fetch()) {
411 if (stripos($name, $dao->label
) !== FALSE) {
413 $fieldName = 'custom_' . $dao->id
;
414 $customSearchFields[$name] = $fieldName;
415 CRM_Core_BAO_CustomField
::addQuickFormElement($form, $fieldName, $fieldId, FALSE, FALSE);
420 $form->assign('customSearchFields', $customSearchFields);
422 $surveys = CRM_Campaign_BAO_Survey
::getSurveys();
424 if (empty($surveys) &&
425 ($className == 'CRM_Campaign_Form_Search')
427 CRM_Core_Error
::statusBounce(ts('Could not find survey for %1 respondents.',
428 array(1 => $form->get('op'))
430 CRM_Utils_System
::url('civicrm/survey/add',
437 //If survey had associated campaign and
438 //campaign has some contact groups, don't
439 //allow to search the contacts those are not
440 //in given campaign groups ( ie not in constituents )
441 $props = array('class' => 'crm-select2');
442 if ($form->get('searchVoterFor') == 'reserve') {
443 $props['onChange'] = "buildCampaignGroups( );return false;";
445 $form->add('select', 'campaign_survey_id', ts('Survey'), $surveys, TRUE, $props);
449 * Retrieve all valid voter ids,
450 * and build respective clause to restrict search.
452 * @param array $criteria
454 * @return $voterClause as a string
457 * @param array $params
461 static public function voterClause($params) {
462 $voterClause = array();
463 $fromClause = $whereClause = NULL;
464 if (!is_array($params) ||
empty($params)) {
467 $surveyId = CRM_Utils_Array
::value('campaign_survey_id', $params);
468 $searchVoterFor = CRM_Utils_Array
::value('campaign_search_voter_for', $params);
470 //get the survey activities.
471 $activityStatus = CRM_Core_PseudoConstant
::activityStatus('name');
472 $status = array('Scheduled');
473 if ($searchVoterFor == 'reserve') {
474 $status[] = 'Completed';
477 $completedStatusId = NULL;
478 foreach ($status as $name) {
479 if ($statusId = array_search($name, $activityStatus)) {
480 $statusIds[] = $statusId;
481 if ($name == 'Completed') {
482 $completedStatusId = $statusId;
487 $voterActValues = CRM_Campaign_BAO_Survey
::getSurveyVoterInfo($surveyId, NULL, $statusIds);
489 if (!empty($voterActValues)) {
491 $voterIds = array_keys($voterActValues);
492 if ($searchVoterFor == 'reserve') {
493 $operator = 'NOT IN';
494 //filter out recontact survey contacts.
495 $recontactInterval = CRM_Core_DAO
::getFieldValue('CRM_Campaign_DAO_Survey',
496 $surveyId, 'recontact_interval'
498 $recontactInterval = unserialize($recontactInterval);
500 is_array($recontactInterval) &&
501 !empty($recontactInterval)
504 foreach ($voterActValues as $values) {
505 $numOfDays = CRM_Utils_Array
::value($values['result'], $recontactInterval);
507 $values['status_id'] == $completedStatusId
509 $recontactIntSeconds = $numOfDays * 24 * 3600;
510 $actDateTimeSeconds = CRM_Utils_Date
::unixTime($values['activity_date_time']);
511 $totalSeconds = $recontactIntSeconds +
$actDateTimeSeconds;
512 //don't consider completed survey activity
513 //unless it fulfill recontact interval criteria.
514 if ($totalSeconds <= time()) {
518 $voterIds[$values['voter_id']] = $values['voter_id'];
523 //lets dump these ids in tmp table and
524 //use appropriate join depend on operator.
525 if (!empty($voterIds)) {
526 $voterIdCount = count($voterIds);
528 //create temporary table to store voter ids.
529 $tempTableName = CRM_Core_DAO
::createTempTableName('civicrm_survey_respondent');
530 CRM_Core_DAO
::executeQuery("DROP TEMPORARY TABLE IF EXISTS {$tempTableName}");
533 CREATE TEMPORARY TABLE {$tempTableName} (
534 id int unsigned NOT NULL AUTO_INCREMENT,
535 survey_contact_id int unsigned NOT NULL,
539 CRM_Core_DAO
::executeQuery($query);
544 $processIds = $voterIds;
545 $insertIds = array_splice($processIds, $insertedCount, $batch);
546 if (!empty($insertIds)) {
547 $insertSQL = "INSERT IGNORE INTO {$tempTableName}( survey_contact_id )
548 VALUES (" . implode('),(', $insertIds) . ');';
549 CRM_Core_DAO
::executeQuery($insertSQL);
551 $insertedCount +
= $batch;
552 } while ($insertedCount < $voterIdCount);
554 if ($operator == 'IN') {
555 $fromClause = " INNER JOIN {$tempTableName} ON ( {$tempTableName}.survey_contact_id = contact_a.id )";
558 $fromClause = " LEFT JOIN {$tempTableName} ON ( {$tempTableName}.survey_contact_id = contact_a.id )";
559 $whereClause = "( {$tempTableName}.survey_contact_id IS NULL )";
563 $voterClause = array(
564 'fromClause' => $fromClause,
565 'whereClause' => $whereClause,
572 * Build the campaign clause for component serach.
575 public static function componentSearchClause(&$params, &$query) {
576 $op = CRM_Utils_Array
::value('op', $params, '=');
577 $campaign = CRM_Utils_Array
::value('campaign', $params);
578 $tableName = CRM_Utils_Array
::value('tableName', $params);
579 $grouping = CRM_Utils_Array
::value('grouping', $params);
580 if (CRM_Utils_System
::isNull($campaign) ||
empty($tableName)) {
584 // fixme - what is the purpose of this code? $campaign should be
585 // an integer, not an array
586 if (is_array($campaign)) {
591 $index = array_search($ignore, $campaign);
592 if ($index !== FALSE) {
593 unset($campaign[$index]);
598 $allCampaigns = CRM_Campaign_BAO_Campaign
::getCampaigns(NULL, NULL, FALSE, FALSE, FALSE, TRUE);
600 $campaignIds = $campaignTitles = array();
601 if (is_array($campaign)) {
602 foreach ($campaign as $campId) {
603 $campaignIds[$campId] = $campId;
604 $campaignTitles[$campId] = $allCampaigns[$campId];
608 $campaignIds = $campaign;
609 if (array_key_exists($campaignIds, $allCampaigns)) {
610 $campaignTitles[$campaignIds] = $allCampaigns[$campaignIds];
613 $query->_qill
[$grouping][] = ts('Campaigns %1',
615 ) . ' ' . implode(' ' . ts('or') . ' ', $campaignTitles);
616 $query->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause("{$tableName}.campaign_id",
621 $query->_tables
[$tableName] = $query->_whereTables
[$tableName] = 1;