CRM/Campaign/BAO/Query.php

   1 <?php
   2
   3 /*
   4  +--------------------------------------------------------------------+
   5  | CiviCRM version 4.4                                                |
   6  +--------------------------------------------------------------------+
   7  | Copyright CiviCRM LLC (c) 2004-2013                                |
   8  +--------------------------------------------------------------------+
   9  | This file is a part of CiviCRM.                                    |
  10  |                                                                    |
  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.   |
  14  |                                                                    |
  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.        |
  19  |                                                                    |
  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  +--------------------------------------------------------------------+
  27 */
  28
  29 /**
  30  *
  31  * @package CRM
  32  * @copyright CiviCRM LLC (c) 2004-2013
  33  * $Id$
  34  *
  35  */
  36 class CRM_Campaign_BAO_Query {
  37   //since normal activity clause clause get collides.
  38   CONST
  39     CIVICRM_ACTIVITY = 'civicrm_survey_activity',
  40     CIVICRM_ACTIVITY_TARGET = 'civicrm_survey_activity_target',
  41     CIVICRM_ACTIVITY_ASSIGNMENT = 'civicrm_survey_activity_assignment';
  42
  43   /**
  44    * static field for all the campaign fields
  45    *
  46    * @var array
  47    * @static
  48    */
  49   static $_campaignFields = NULL;
  50
  51   static $_applySurveyClause = FALSE;
  52
  53   /**
  54    * Function get the fields for campaign.
  55    *
  56    * @return array self::$_campaignFields  an associative array of campaign fields
  57    * @static
  58    */
  59   static function &getFields() {
  60     if (!isset(self::$_campaignFields)) {
  61       self::$_campaignFields = array();
  62     }
  63
  64     return self::$_campaignFields;
  65   }
  66
  67   /**
  68    * if survey, campaign are involved, add the specific fields.
  69    *
  70    * @return void
  71    * @access public
  72    */
  73   static function select(&$query) {
  74     self::$_applySurveyClause = FALSE;
  75     if (is_array($query->_params)) {
  76       foreach ($query->_params as $values) {
  77         if (!is_array($values) || count($values) != 5) {
  78           continue;
  79         }
  80
  81         list($name, $op, $value, $grouping, $wildcard) = $values;
  82         if ($name == 'campaign_survey_id') {
  83           self::$_applySurveyClause = TRUE;
  84           break;
  85         }
  86       }
  87     }
  88
  89     //get survey clause in force,
  90     //only when we have survey id.
  91     if (!self::$_applySurveyClause) {
  92       return;
  93     }
  94
  95     //all below tables are require to fetch  result.
  96
  97     //1. get survey activity target table in.
  98     $query->_select['survey_activity_target_contact_id'] = 'civicrm_activity_target.contact_id as survey_activity_target_contact_id';
  99     $query->_select['survey_activity_target_id'] = 'civicrm_activity_target.id as survey_activity_target_id';
 100     $query->_element['survey_activity_target_id'] = 1;
 101     $query->_element['survey_activity_target_contact_id'] = 1;
 102     $query->_tables[self::CIVICRM_ACTIVITY_TARGET] = 1;
 103     $query->_whereTables[self::CIVICRM_ACTIVITY_TARGET] = 1;
 104
 105     //2. get survey activity table in.
 106     $query->_select['survey_activity_id'] = 'civicrm_activity.id as survey_activity_id';
 107     $query->_element['survey_activity_id'] = 1;
 108     $query->_tables[self::CIVICRM_ACTIVITY] = 1;
 109     $query->_whereTables[self::CIVICRM_ACTIVITY] = 1;
 110
 111     //3. get the assignee table in.
 112     $query->_select['survey_interviewer_id'] = 'civicrm_activity_assignment.id as survey_interviewer_id';
 113     $query->_element['survey_interviewer_id'] = 1;
 114     $query->_tables[self::CIVICRM_ACTIVITY_ASSIGNMENT] = 1;
 115     $query->_whereTables[self::CIVICRM_ACTIVITY_ASSIGNMENT] = 1;
 116
 117     //4. get survey table.
 118     $query->_select['campaign_survey_id'] = 'civicrm_survey.id as campaign_survey_id';
 119     $query->_element['campaign_survey_id'] = 1;
 120     $query->_tables['civicrm_survey'] = 1;
 121     $query->_whereTables['civicrm_survey'] = 1;
 122
 123     //5. get campaign table.
 124     $query->_select['campaign_id'] = 'civicrm_campaign.id as campaign_id';
 125     $query->_element['campaign_id'] = 1;
 126     $query->_tables['civicrm_campaign'] = 1;
 127     $query->_whereTables['civicrm_campaign'] = 1;
 128   }
 129
 130   static function where(&$query) {
 131     //get survey clause in force,
 132     //only when we have survey id.
 133     if (!self::$_applySurveyClause) {
 134       return;
 135     }
 136
 137     $grouping = NULL;
 138     foreach (array_keys($query->_params) as $id) {
 139       if ($query->_mode == CRM_Contact_BAO_Query::MODE_CONTACTS) {
 140         $query->_useDistinct = TRUE;
 141       }
 142
 143       self::whereClauseSingle($query->_params[$id], $query);
 144     }
 145   }
 146
 147   static function whereClauseSingle(&$values, &$query) {
 148     //get survey clause in force,
 149     //only when we have survey id.
 150     if (!self::$_applySurveyClause) {
 151       return;
 152     }
 153
 154     list($name, $op, $value, $grouping, $wildcard) = $values;
 155
 156     switch ($name) {
 157       case 'campaign_survey_id':
 158         $query->_qill[$grouping][] = ts('Survey - %1', array(1 => CRM_Core_DAO::getFieldValue('CRM_Campaign_DAO_Survey', $value, 'title')));
 159
 160         $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause('civicrm_activity.source_record_id',
 161           $op, $value, 'Integer'
 162         );
 163         $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause('civicrm_survey.id',
 164           $op, $value, 'Integer'
 165         );
 166         return;
 167
 168       case 'survey_status_id':
 169         $activityStatus = CRM_Core_PseudoConstant::activityStatus();
 170
 171         $query->_qill[$grouping][] = ts('Survey Status - %1', array(1 => $activityStatus[$value]));
 172         $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause('civicrm_activity.status_id',
 173           $op, $value, 'Integer'
 174         );
 175         return;
 176
 177       case 'campaign_search_voter_for':
 178         if (in_array($value, array('release', 'interview'))) {
 179           $query->_where[$grouping][] = '(civicrm_activity.is_deleted = 0 OR civicrm_activity.is_deleted IS NULL)';
 180         }
 181         return;
 182
 183       case 'survey_interviewer_id':
 184         $surveyInterviewerName = NULL;
 185         foreach ($query->_params as $paramValues) {
 186           if (CRM_Utils_Array::value(0, $paramValues) == 'survey_interviewer_name') {
 187             $surveyInterviewerName = CRM_Utils_Array::value(2, $paramValues);
 188             break;
 189           }
 190         }
 191         $query->_qill[$grouping][] = ts('Survey Interviewer - %1', array(1 => $surveyInterviewerName));
 192         $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause('civicrm_activity_assignment.contact_id',
 193           $op, $value, 'Integer'
 194         );
 195         return;
 196     }
 197   }
 198
 199   static function from($name, $mode, $side) {
 200     $from = NULL;
 201     //get survey clause in force,
 202     //only when we have survey id.
 203     if (!self::$_applySurveyClause) {
 204       return $from;
 205     }
 206
 207     $activityContacts = CRM_Core_OptionGroup::values('activity_contacts', FALSE, FALSE, FALSE, NULL, 'name');
 208     $sourceID = CRM_Utils_Array::key('Activity Source', $activityContacts);
 209     $assigneeID = CRM_Utils_Array::key('Activity Assignees', $activityContacts);
 210     $targetID = CRM_Utils_Array::key('Activity Targets', $activityContacts);
 211
 212     switch ($name) {
 213       case self::CIVICRM_ACTIVITY_TARGET:
 214         $from = " INNER JOIN civicrm_activity_contact civicrm_activity_target
 215    ON ( civicrm_activity_target.contact_id = contact_a.id AND civicrm_activity_target.record_type_id = $targetID) ";
 216         break;
 217
 218       case self::CIVICRM_ACTIVITY:
 219         $surveyActivityTypes = CRM_Campaign_PseudoConstant::activityType();
 220         $surveyKeys          = "(" . implode(',', array_keys($surveyActivityTypes)) . ")";
 221         $from                = " INNER JOIN civicrm_activity ON ( civicrm_activity.id = civicrm_activity_target.activity_id
 222                                  AND civicrm_activity.activity_type_id IN $surveyKeys ) ";
 223         break;
 224
 225       case self::CIVICRM_ACTIVITY_ASSIGNMENT:
 226         $from = "
 227 INNER JOIN  civicrm_activity_contact civicrm_activity_assignment ON ( civicrm_activity.id = civicrm_activity_assignment.activity_id AND
 228 civicrm_activity_assignment.record_type_id = $assigneeID ) ";
 229         break;
 230
 231       case 'civicrm_survey':
 232         $from = " INNER JOIN civicrm_survey ON ( civicrm_survey.id = civicrm_activity.source_record_id ) ";
 233         break;
 234
 235       case 'civicrm_campaign':
 236         $from = " $side JOIN civicrm_campaign ON ( civicrm_campaign.id = civicrm_survey.campaign_id ) ";
 237         break;
 238     }
 239
 240     return $from;
 241   }
 242
 243   static function defaultReturnProperties($mode,
 244     $includeCustomFields = TRUE
 245   ) {
 246     $properties = NULL;
 247     if ($mode & CRM_Contact_BAO_Query::MODE_CAMPAIGN) {
 248       $properties = array(
 249         'contact_id' => 1,
 250         'contact_type' => 1,
 251         'contact_sub_type' => 1,
 252         'sort_name' => 1,
 253         'display_name' => 1,
 254         'street_unit' => 1,
 255         'street_name' => 1,
 256         'street_number' => 1,
 257         'street_address' => 1,
 258         'city' => 1,
 259         'postal_code' => 1,
 260         'state_province' => 1,
 261         'country' => 1,
 262         'email' => 1,
 263         'phone' => 1,
 264         'survey_activity_target_id' => 1,
 265         'survey_activity_id' => 1,
 266         'survey_status_id' => 1,
 267         'campaign_survey_id' => 1,
 268         'campaign_id' => 1,
 269         'survey_interviewer_id' => 1,
 270         'survey_activity_target_contact_id' => 1,
 271       );
 272     }
 273
 274     return $properties;
 275   }
 276
 277   static function tableNames(&$tables) {}
 278   static function searchAction(&$row, $id) {}
 279
 280   static function info(&$tables) {
 281     //get survey clause in force,
 282     //only when we have survey id.
 283     if (!self::$_applySurveyClause) {
 284       return;
 285     }
 286
 287     $weight = end($tables);
 288     $tables[self::CIVICRM_ACTIVITY_TARGET] = ++$weight;
 289     $tables[self::CIVICRM_ACTIVITY] = ++$weight;
 290     $tables[self::CIVICRM_ACTIVITY_ASSIGNMENT] = ++$weight;
 291     $tables['civicrm_survey'] = ++$weight;
 292     $tables['civicrm_campaign'] = ++$weight;
 293   }
 294
 295   /**
 296    * add all the elements shared between,
 297    * normal voter search and voter listing (GOTV form)
 298    *
 299    * @access public
 300    *
 301    * @return void
 302    * @static
 303    */
 304   static function buildSearchForm(&$form) {
 305
 306     $attributes = CRM_Core_DAO::getAttribute('CRM_Core_DAO_Address');
 307     $className = CRM_Utils_System::getClassName($form);
 308
 309     $form->add('text', 'sort_name', ts('Contact Name'),
 310       CRM_Core_DAO::getAttribute('CRM_Contact_DAO_Contact', 'sort_name')
 311     );
 312     $form->add('text', 'street_name', ts('Street Name'), $attributes['street_name']);
 313     $form->add('text', 'street_number', ts('Street Number'), $attributes['street_number']);
 314     $form->add('text', 'street_unit', ts('Street Unit'), $attributes['street_unit']);
 315     $form->add('text', 'street_address', ts('Street Address'), $attributes['street_address']);
 316     $form->add('text', 'city', ts('City'), $attributes['city']);
 317     $form->add('text', 'postal_code', ts('Zip / Postal Code'), $attributes['postal_code']);
 318
 319     $contactTypes = CRM_Contact_BAO_ContactType::getSelectElements();
 320     $form->add('select', 'contact_type', ts('Contact Type(s)'), $contactTypes, FALSE,
 321       array('id' => 'contact_type', 'multiple' => 'multiple', 'title' => ts('- select -'))
 322     );
 323     $groups = CRM_Core_PseudoConstant::group();
 324     $form->add('select', 'group', ts('Groups'), $groups, FALSE,
 325       array('id' => 'group', 'multiple' => 'multiple', 'title' => ts('- select -'))
 326     );
 327
 328     $showInterviewer = FALSE;
 329     if (CRM_Core_Permission::check('administer CiviCampaign')) {
 330       $showInterviewer = TRUE;
 331     }
 332     $form->assign('showInterviewer', $showInterviewer);
 333
 334     if ($showInterviewer ||
 335       $className == 'CRM_Campaign_Form_Gotv'
 336     ) {
 337       //autocomplete url
 338       $dataUrl = CRM_Utils_System::url('civicrm/ajax/rest',
 339         'className=CRM_Contact_Page_AJAX&fnName=getContactList&json=1&reset=1',
 340         FALSE, NULL, FALSE
 341       );
 342
 343       $form->assign('dataUrl', $dataUrl);
 344       $form->add('text', 'survey_interviewer_name', ts('Interviewer'));
 345       $form->add('hidden', 'survey_interviewer_id', '', array('id' => 'survey_interviewer_id'));
 346
 347       $userId = NULL;
 348       if (isset($form->_interviewerId) && $form->_interviewerId) {
 349         $userId = $form->_interviewerId;
 350       }
 351       if (!$userId) {
 352         $session = CRM_Core_Session::singleton();
 353         $userId = $session->get('userID');
 354       }
 355       if ($userId) {
 356         $defaults = array();
 357         $defaults['survey_interviewer_id'] = $userId;
 358         $defaults['survey_interviewer_name'] = CRM_Core_DAO::getFieldValue('CRM_Contact_DAO_Contact',
 359           $userId,
 360           'sort_name',
 361           'id'
 362         );
 363         $form->setDefaults($defaults);
 364       }
 365     }
 366
 367     //build ward and precinct custom fields.
 368     $query = '
 369     SELECT  fld.id, fld.label
 370       FROM  civicrm_custom_field fld
 371 INNER JOIN  civicrm_custom_group grp on fld.custom_group_id = grp.id
 372      WHERE  grp.name = %1';
 373     $dao = CRM_Core_DAO::executeQuery($query, array(1 => array('Voter_Info', 'String')));
 374     $customSearchFields = array();
 375     while ($dao->fetch()) {
 376       foreach (array(
 377         'ward', 'precinct') as $name) {
 378         if (stripos($name, $dao->label) !== FALSE) {
 379           $fieldId = $dao->id;
 380           $fieldName = 'custom_' . $dao->id;
 381           $customSearchFields[$name] = $fieldName;
 382           CRM_Core_BAO_CustomField::addQuickFormElement($form, $fieldName, $fieldId, FALSE, FALSE);
 383           break;
 384         }
 385       }
 386     }
 387     $form->assign('customSearchFields', $customSearchFields);
 388
 389     $surveys = CRM_Campaign_BAO_Survey::getSurveys();
 390
 391     if (empty($surveys) &&
 392       ($className == 'CRM_Campaign_Form_Search')
 393     ) {
 394       CRM_Core_Error::statusBounce(ts('Could not find survey for %1 respondents.',
 395           array(1 => $form->get('op'))
 396         ),
 397         CRM_Utils_System::url('civicrm/survey/add',
 398           'reset=1&action=add'
 399         )
 400       );
 401     }
 402
 403     //CRM-7406 --
 404     //If survey had associated campaign and
 405     //campaign has some contact groups, don't
 406     //allow to search the contacts those are not
 407     //in given campaign groups ( ie not in constituents )
 408     $groupJs = NULL;
 409     if ($form->get('searchVoterFor') == 'reserve') {
 410       $groupJs = array('onChange' => "buildCampaignGroups( );return false;");
 411     }
 412     $form->add('select', 'campaign_survey_id', ts('Survey'), $surveys, TRUE, $groupJs);
 413   }
 414
 415   /*
 416    * Retrieve all valid voter ids,
 417    * and build respective clause to restrict search.
 418    *
 419    * @param  array  $criteria an array
 420    * @return $voterClause as a string
 421    * @static
 422    */
 423   static public function voterClause($params) {
 424     $voterClause = array();
 425     $fromClause = $whereClause = NULL;
 426     if (!is_array($params) || empty($params)) {
 427       return $voterClause;
 428     }
 429     $surveyId       = CRM_Utils_Array::value('campaign_survey_id', $params);
 430     $interviewerId  = CRM_Utils_Array::value('survey_interviewer_id', $params);
 431     $searchVoterFor = CRM_Utils_Array::value('campaign_search_voter_for', $params);
 432
 433     //get the survey activities.
 434     $activityStatus = CRM_Core_PseudoConstant::activityStatus('name');
 435     $status = array('Scheduled');
 436     if ($searchVoterFor == 'reserve') {
 437       $status[] = 'Completed';
 438     }
 439
 440     $completedStatusId = NULL;
 441     foreach ($status as $name) {
 442       if ($statusId = array_search($name, $activityStatus)) {
 443         $statusIds[] = $statusId;
 444         if ($name == 'Completed') {
 445           $completedStatusId = $statusId;
 446         }
 447       }
 448     }
 449
 450     $voterActValues = CRM_Campaign_BAO_Survey::getSurveyVoterInfo($surveyId, NULL, $statusIds);
 451
 452     if (!empty($voterActValues)) {
 453       $operator = 'IN';
 454       $voterIds = array_keys($voterActValues);
 455       if ($searchVoterFor == 'reserve') {
 456         $operator = 'NOT IN';
 457         //filter out recontact survey contacts.
 458         $recontactInterval = CRM_Core_DAO::getFieldValue('CRM_Campaign_DAO_Survey',
 459           $surveyId, 'recontact_interval'
 460         );
 461         $recontactInterval = unserialize($recontactInterval);
 462         if ($surveyId &&
 463           is_array($recontactInterval) &&
 464           !empty($recontactInterval)
 465         ) {
 466           $voterIds = array();
 467           foreach ($voterActValues as $values) {
 468             $numOfDays = CRM_Utils_Array::value($values['result'], $recontactInterval);
 469             if ($numOfDays &&
 470               $values['status_id'] == $completedStatusId
 471             ) {
 472               $recontactIntSeconds = $numOfDays * 24 * 3600;
 473               $actDateTimeSeconds  = CRM_Utils_Date::unixTime($values['activity_date_time']);
 474               $totalSeconds        = $recontactIntSeconds + $actDateTimeSeconds;
 475               //don't consider completed survey activity
 476               //unless it fulfill recontact interval criteria.
 477               if ($totalSeconds <= time()) {
 478                 continue;
 479               }
 480             }
 481             $voterIds[$values['voter_id']] = $values['voter_id'];
 482           }
 483         }
 484       }
 485
 486       //lets dump these ids in tmp table and
 487       //use appropriate join depend on operator.
 488       if (!empty($voterIds)) {
 489         $voterIdCount = count($voterIds);
 490
 491         //create temporary table to store voter ids.
 492         $tempTableName = CRM_Core_DAO::createTempTableName('civicrm_survey_respondent');
 493         CRM_Core_DAO::executeQuery("DROP TABLE IF EXISTS {$tempTableName}");
 494
 495         $query = "
 496      CREATE TEMPORARY TABLE {$tempTableName} (
 497             id int unsigned NOT NULL AUTO_INCREMENT,
 498             survey_contact_id int unsigned NOT NULL,
 499   PRIMARY KEY ( id )
 500 );
 501 ";
 502         CRM_Core_DAO::executeQuery($query);
 503
 504         $batch = 100;
 505         $insertedCount = 0;
 506         do {
 507           $processIds = $voterIds;
 508           $insertIds = array_splice($processIds, $insertedCount, $batch);
 509           if (!empty($insertIds)) {
 510             $insertSQL = "INSERT IGNORE INTO {$tempTableName}( survey_contact_id )
 511                      VALUES (" . implode('),(', $insertIds) . ');';
 512             CRM_Core_DAO::executeQuery($insertSQL);
 513           }
 514           $insertedCount += $batch;
 515         } while ($insertedCount < $voterIdCount);
 516
 517         if ($operator == 'IN') {
 518           $fromClause = " INNER JOIN {$tempTableName} ON ( {$tempTableName}.survey_contact_id = contact_a.id )";
 519         }
 520         else {
 521           $fromClause = " LEFT JOIN {$tempTableName} ON ( {$tempTableName}.survey_contact_id = contact_a.id )";
 522           $whereClause = "( {$tempTableName}.survey_contact_id IS NULL )";
 523         }
 524       }
 525     }
 526     $voterClause = array(
 527       'fromClause' => $fromClause,
 528       'whereClause' => $whereClause,
 529     );
 530
 531     return $voterClause;
 532   }
 533
 534   /**
 535    * Build the campaign clause for component serach.
 536    *
 537    **/
 538   public static function componentSearchClause(&$params, &$query) {
 539     $op        = CRM_Utils_Array::value('op', $params, '=');
 540     $campaign  = CRM_Utils_Array::value('campaign', $params);
 541     $tableName = CRM_Utils_Array::value('tableName', $params);
 542     $grouping  = CRM_Utils_Array::value('grouping', $params);
 543     if (CRM_Utils_System::isNull($campaign) || empty($tableName)) {
 544       return;
 545     }
 546
 547     // fixme - what is the purpose of this code? $campaign should be
 548     // an integer, not an array
 549     if (is_array($campaign)) {
 550       foreach (array(
 551         'current_campaign', 'past_campaign') as $ignore) {
 552         $index = array_search($ignore, $campaign);
 553         if ($index !== FALSE)unset($campaign[$index]);
 554       }
 555     }
 556
 557     $allCampaigns = CRM_Campaign_BAO_Campaign::getCampaigns(NULL, NULL, FALSE, FALSE, FALSE, TRUE);
 558
 559     $campaignIds = $campaignTitles = array();
 560     if (is_array($campaign)) {
 561       foreach ($campaign as $campId) {
 562         $campaignIds[$campId] = $campId;
 563         $campaignTitles[$campId] = $allCampaigns[$campId];
 564       }
 565       if (count($campaignIds) > 1) {
 566         $op = 'IN';
 567         $campaignIds = '(' . implode(',', $campaignIds) . ')';
 568       }
 569       else {
 570         $campaignIds = reset($campaignIds);
 571       }
 572     }
 573     else {
 574       $campaignIds = $campaign;
 575       if (array_key_exists($campaignIds, $allCampaigns)) {
 576         $campaignTitles[$campaignIds] = $allCampaigns[$campaignIds];
 577       }
 578     }
 579     $query->_qill[$grouping][] = ts('Campaigns %1',
 580       array(1 => $op)
 581     ) . ' ' . implode(' ' . ts('or') . ' ', $campaignTitles);
 582     $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("{$tableName}.campaign_id",
 583       $op,
 584       $campaignIds,
 585       'Integer'
 586     );
 587     $query->_tables[$tableName] = $query->_whereTables[$tableName] = 1;
 588   }
 589 }
 590