CRM/Campaign/BAO/Query.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 4.7                                                |
   5  +--------------------------------------------------------------------+
   6  | Copyright CiviCRM LLC (c) 2004-2015                                |
   7  +--------------------------------------------------------------------+
   8  | This file is a part of CiviCRM.                                    |
   9  |                                                                    |
  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.   |
  13  |                                                                    |
  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.        |
  18  |                                                                    |
  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  +--------------------------------------------------------------------+
  26  */
  27
  28 /**
  29  *
  30  * @package CRM
  31  * @copyright CiviCRM LLC (c) 2004-2015
  32  */
  33 class CRM_Campaign_BAO_Query {
  34   //since normal activity clause clause get collides.
  35   const
  36     CIVICRM_ACTIVITY = 'civicrm_survey_activity',
  37     CIVICRM_ACTIVITY_TARGET = 'civicrm_survey_activity_target',
  38     CIVICRM_ACTIVITY_ASSIGNMENT = 'civicrm_survey_activity_assignment';
  39
  40   /**
  41    * Static field for all the campaign fields.
  42    *
  43    * @var array
  44    */
  45   static $_campaignFields = NULL;
  46
  47   static $_applySurveyClause = FALSE;
  48
  49   /**
  50    * Function get the fields for campaign.
  51    *
  52    * @return array
  53    *   self::$_campaignFields  an associative array of campaign fields
  54    */
  55   public static function &getFields() {
  56     if (!isset(self::$_campaignFields)) {
  57       self::$_campaignFields = array();
  58     }
  59
  60     return self::$_campaignFields;
  61   }
  62
  63   /**
  64    * If survey, campaign are involved, add the specific fields.
  65    *
  66    * @param CRM_Contact_BAO_Contact $query
  67    */
  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) {
  73           continue;
  74         }
  75
  76         list($name, $op, $value, $grouping, $wildcard) = $values;
  77         if ($name == 'campaign_survey_id') {
  78           self::$_applySurveyClause = TRUE;
  79           break;
  80         }
  81       }
  82     }
  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',
  91             'idCol' => $field,
  92             'bao' => 'CRM_Activity_BAO_Activity',
  93           );
  94         }
  95       }
  96     }
  97
  98     //get survey clause in force,
  99     //only when we have survey id.
 100     if (!self::$_applySurveyClause) {
 101       return;
 102     }
 103
 104     //all below tables are require to fetch  result.
 105
 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;
 113
 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;
 119
 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;
 125
 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;
 131
 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;
 137   }
 138
 139   /**
 140    * @param $query
 141    */
 142   public static function where(&$query) {
 143     //get survey clause in force,
 144     //only when we have survey id.
 145     if (!self::$_applySurveyClause) {
 146       return;
 147     }
 148
 149     $grouping = NULL;
 150     foreach (array_keys($query->_params) as $id) {
 151       if ($query->_mode == CRM_Contact_BAO_Query::MODE_CONTACTS) {
 152         $query->_useDistinct = TRUE;
 153       }
 154
 155       self::whereClauseSingle($query->_params[$id], $query);
 156     }
 157   }
 158
 159   /**
 160    * @param $values
 161    * @param $query
 162    */
 163   public static function whereClauseSingle(&$values, &$query) {
 164     //get survey clause in force,
 165     //only when we have survey id.
 166     if (!self::$_applySurveyClause) {
 167       return;
 168     }
 169
 170     list($name, $op, $value, $grouping, $wildcard) = $values;
 171
 172     switch ($name) {
 173       case 'campaign_survey_id':
 174         $query->_qill[$grouping][] = ts('Survey - %1', array(1 => CRM_Core_DAO::getFieldValue('CRM_Campaign_DAO_Survey', $value, 'title')));
 175
 176         $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause('civicrm_activity.source_record_id',
 177           $op, $value, 'Integer'
 178         );
 179         $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause('civicrm_survey.id',
 180           $op, $value, 'Integer'
 181         );
 182         return;
 183
 184       case 'survey_status_id':
 185         $activityStatus = CRM_Core_PseudoConstant::activityStatus();
 186
 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'
 190         );
 191         return;
 192
 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)';
 196         }
 197         return;
 198
 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'
 204         );
 205         return;
 206     }
 207   }
 208
 209   /**
 210    * @param string $name
 211    * @param $mode
 212    * @param $side
 213    *
 214    * @return null|string
 215    */
 216   public static function from($name, $mode, $side) {
 217     $from = NULL;
 218     //get survey clause in force,
 219     //only when we have survey id.
 220     if (!self::$_applySurveyClause) {
 221       return $from;
 222     }
 223
 224     $activityContacts = CRM_Core_OptionGroup::values('activity_contacts', FALSE, FALSE, FALSE, NULL, 'name');
 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);
 228
 229     switch ($name) {
 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) ";
 233         break;
 234
 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 ) ";
 240         break;
 241
 242       case self::CIVICRM_ACTIVITY_ASSIGNMENT:
 243         $from = "
 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 ) ";
 246         break;
 247
 248       case 'civicrm_survey':
 249         $from = " INNER JOIN civicrm_survey ON ( civicrm_survey.id = civicrm_activity.source_record_id ) ";
 250         break;
 251
 252       case 'civicrm_campaign':
 253         $from = " $side JOIN civicrm_campaign ON ( civicrm_campaign.id = civicrm_survey.campaign_id ) ";
 254         break;
 255     }
 256
 257     return $from;
 258   }
 259
 260   /**
 261    * @param $mode
 262    * @param bool $includeCustomFields
 263    *
 264    * @return array|null
 265    */
 266   public static function defaultReturnProperties(
 267     $mode,
 268     $includeCustomFields = TRUE
 269   ) {
 270     $properties = NULL;
 271     if ($mode & CRM_Contact_BAO_Query::MODE_CAMPAIGN) {
 272       $properties = array(
 273         'contact_id' => 1,
 274         'contact_type' => 1,
 275         'contact_sub_type' => 1,
 276         'sort_name' => 1,
 277         'display_name' => 1,
 278         'street_unit' => 1,
 279         'street_name' => 1,
 280         'street_number' => 1,
 281         'street_address' => 1,
 282         'city' => 1,
 283         'postal_code' => 1,
 284         'state_province' => 1,
 285         'country' => 1,
 286         'email' => 1,
 287         'phone' => 1,
 288         'survey_activity_target_id' => 1,
 289         'survey_activity_id' => 1,
 290         'survey_status_id' => 1,
 291         'campaign_survey_id' => 1,
 292         'campaign_id' => 1,
 293         'survey_interviewer_id' => 1,
 294         'survey_activity_target_contact_id' => 1,
 295       );
 296     }
 297
 298     return $properties;
 299   }
 300
 301   /**
 302    * @param $tables
 303    */
 304   public static function tableNames(&$tables) {
 305   }
 306
 307   /**
 308    * @param $row
 309    * @param int $id
 310    */
 311   public static function searchAction(&$row, $id) {
 312   }
 313
 314   /**
 315    * @param $tables
 316    */
 317   public static function info(&$tables) {
 318     //get survey clause in force,
 319     //only when we have survey id.
 320     if (!self::$_applySurveyClause) {
 321       return;
 322     }
 323
 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;
 330   }
 331
 332   /**
 333    * Add all the elements shared between,
 334    * normal voter search and voter listing (GOTV form)
 335    *
 336    * @param CRM_Core_Form $form
 337    */
 338   public static function buildSearchForm(&$form) {
 339
 340     $attributes = CRM_Core_DAO::getAttribute('CRM_Core_DAO_Address');
 341     $className = CRM_Utils_System::getClassName($form);
 342
 343     $form->add('text', 'sort_name', ts('Contact Name'),
 344       CRM_Core_DAO::getAttribute('CRM_Contact_DAO_Contact', 'sort_name')
 345     );
 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']);
 352
 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')
 359     );
 360     $groups = CRM_Core_PseudoConstant::nestedGroup();
 361     $form->add('select', 'group', ts('Groups'), $groups, FALSE,
 362       array('multiple' => 'multiple', 'class' => 'crm-select2')
 363     );
 364
 365     $showInterviewer = FALSE;
 366     if (CRM_Core_Permission::check('administer CiviCampaign')) {
 367       $showInterviewer = TRUE;
 368     }
 369     $form->assign('showInterviewer', $showInterviewer);
 370
 371     if ($showInterviewer ||
 372       $className == 'CRM_Campaign_Form_Gotv'
 373     ) {
 374
 375       $form->addEntityRef('survey_interviewer_id', ts('Interviewer'), array('class' => 'big'));
 376
 377       $userId = NULL;
 378       if (isset($form->_interviewerId) && $form->_interviewerId) {
 379         $userId = $form->_interviewerId;
 380       }
 381       if (!$userId) {
 382         $session = CRM_Core_Session::singleton();
 383         $userId = $session->get('userID');
 384       }
 385       if ($userId) {
 386         $defaults = array();
 387         $defaults['survey_interviewer_id'] = $userId;
 388         $form->setDefaults($defaults);
 389       }
 390     }
 391
 392     //build ward and precinct custom fields.
 393     $query = '
 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()) {
 401       foreach (array(
 402                  'ward',
 403                  'precinct',
 404                ) as $name) {
 405         if (stripos($name, $dao->label) !== FALSE) {
 406           $fieldId = $dao->id;
 407           $fieldName = 'custom_' . $dao->id;
 408           $customSearchFields[$name] = $fieldName;
 409           CRM_Core_BAO_CustomField::addQuickFormElement($form, $fieldName, $fieldId, FALSE, FALSE);
 410           break;
 411         }
 412       }
 413     }
 414     $form->assign('customSearchFields', $customSearchFields);
 415
 416     $surveys = CRM_Campaign_BAO_Survey::getSurveys();
 417
 418     if (empty($surveys) &&
 419       ($className == 'CRM_Campaign_Form_Search')
 420     ) {
 421       CRM_Core_Error::statusBounce(ts('Could not find survey for %1 respondents.',
 422           array(1 => $form->get('op'))
 423         ),
 424         CRM_Utils_System::url('civicrm/survey/add',
 425           'reset=1&action=add'
 426         )
 427       );
 428     }
 429
 430     //CRM-7406 --
 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;";
 438     }
 439     $form->add('select', 'campaign_survey_id', ts('Survey'), $surveys, TRUE, $props);
 440   }
 441
 442   /*
 443    * Retrieve all valid voter ids,
 444    * and build respective clause to restrict search.
 445    *
 446    * @param array $criteria
 447    *   An array.
 448    * @return $voterClause as a string
 449    */
 450   /**
 451    * @param array $params
 452    *
 453    * @return array
 454    */
 455   static public function voterClause($params) {
 456     $voterClause = array();
 457     $fromClause = $whereClause = NULL;
 458     if (!is_array($params) || empty($params)) {
 459       return $voterClause;
 460     }
 461     $surveyId = CRM_Utils_Array::value('campaign_survey_id', $params);
 462     $searchVoterFor = CRM_Utils_Array::value('campaign_search_voter_for', $params);
 463
 464     //get the survey activities.
 465     $activityStatus = CRM_Core_PseudoConstant::activityStatus('name');
 466     $status = array('Scheduled');
 467     if ($searchVoterFor == 'reserve') {
 468       $status[] = 'Completed';
 469     }
 470
 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;
 477         }
 478       }
 479     }
 480
 481     $voterActValues = CRM_Campaign_BAO_Survey::getSurveyVoterInfo($surveyId, NULL, $statusIds);
 482
 483     if (!empty($voterActValues)) {
 484       $operator = 'IN';
 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'
 491         );
 492         $recontactInterval = unserialize($recontactInterval);
 493         if ($surveyId &&
 494           is_array($recontactInterval) &&
 495           !empty($recontactInterval)
 496         ) {
 497           $voterIds = array();
 498           foreach ($voterActValues as $values) {
 499             $numOfDays = CRM_Utils_Array::value($values['result'], $recontactInterval);
 500             if ($numOfDays &&
 501               $values['status_id'] == $completedStatusId
 502             ) {
 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()) {
 509                 continue;
 510               }
 511             }
 512             $voterIds[$values['voter_id']] = $values['voter_id'];
 513           }
 514         }
 515       }
 516
 517       //lets dump these ids in tmp table and
 518       //use appropriate join depend on operator.
 519       if (!empty($voterIds)) {
 520         $voterIdCount = count($voterIds);
 521
 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}");
 525
 526         $query = "
 527      CREATE TEMPORARY TABLE {$tempTableName} (
 528             id int unsigned NOT NULL AUTO_INCREMENT,
 529             survey_contact_id int unsigned NOT NULL,
 530   PRIMARY KEY ( id )
 531 );
 532 ";
 533         CRM_Core_DAO::executeQuery($query);
 534
 535         $batch = 100;
 536         $insertedCount = 0;
 537         do {
 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);
 544           }
 545           $insertedCount += $batch;
 546         } while ($insertedCount < $voterIdCount);
 547
 548         if ($operator == 'IN') {
 549           $fromClause = " INNER JOIN {$tempTableName} ON ( {$tempTableName}.survey_contact_id = contact_a.id )";
 550         }
 551         else {
 552           $fromClause = " LEFT JOIN {$tempTableName} ON ( {$tempTableName}.survey_contact_id = contact_a.id )";
 553           $whereClause = "( {$tempTableName}.survey_contact_id IS NULL )";
 554         }
 555       }
 556     }
 557     $voterClause = array(
 558       'fromClause' => $fromClause,
 559       'whereClause' => $whereClause,
 560     );
 561
 562     return $voterClause;
 563   }
 564
 565 }