CRM/Campaign/BAO/Query.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | Copyright CiviCRM LLC. All rights reserved.                        |
   5  |                                                                    |
   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  +--------------------------------------------------------------------+
  10  */
  11
  12 /**
  13  *
  14  * @package CRM
  15  * @copyright CiviCRM LLC https://civicrm.org/licensing
  16  */
  17 class CRM_Campaign_BAO_Query {
  18   //since normal activity clause clause get collides.
  19   const
  20     CIVICRM_ACTIVITY = 'civicrm_survey_activity',
  21     CIVICRM_ACTIVITY_TARGET = 'civicrm_survey_activity_target',
  22     CIVICRM_ACTIVITY_ASSIGNMENT = 'civicrm_survey_activity_assignment';
  23
  24   /**
  25    * Static field for all the campaign fields.
  26    *
  27    * @var array
  28    */
  29   public static $_campaignFields = NULL;
  30
  31   public static $_applySurveyClause = FALSE;
  32
  33   /**
  34    * Function get the fields for campaign.
  35    *
  36    * @return array
  37    *   self::$_campaignFields  an associative array of campaign fields
  38    */
  39   public static function &getFields() {
  40     if (!isset(self::$_campaignFields)) {
  41       self::$_campaignFields = [];
  42     }
  43
  44     return self::$_campaignFields;
  45   }
  46
  47   /**
  48    * If survey, campaign are involved, add the specific fields.
  49    *
  50    * @param CRM_Contact_BAO_Contact $query
  51    */
  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) {
  57           continue;
  58         }
  59
  60         list($name, $op, $value, $grouping, $wildcard) = $values;
  61         if ($name == 'campaign_survey_id') {
  62           self::$_applySurveyClause = TRUE;
  63           break;
  64         }
  65       }
  66     }
  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',
  75             'idCol' => $field,
  76             'bao' => 'CRM_Activity_BAO_Activity',
  77           ];
  78         }
  79       }
  80     }
  81
  82     //get survey clause in force,
  83     //only when we have survey id.
  84     if (!self::$_applySurveyClause) {
  85       return;
  86     }
  87
  88     //all below tables are require to fetch  result.
  89
  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;
  97
  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;
 103
 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;
 109
 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;
 115
 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;
 121   }
 122
 123   /**
 124    * @param $query
 125    */
 126   public static function where(&$query) {
 127     //get survey clause in force,
 128     //only when we have survey id.
 129     if (!self::$_applySurveyClause) {
 130       return;
 131     }
 132
 133     $grouping = NULL;
 134     foreach (array_keys($query->_params) as $id) {
 135       if ($query->_mode == CRM_Contact_BAO_Query::MODE_CONTACTS) {
 136         $query->_useDistinct = TRUE;
 137       }
 138
 139       self::whereClauseSingle($query->_params[$id], $query);
 140     }
 141   }
 142
 143   /**
 144    * @param $values
 145    * @param $query
 146    */
 147   public 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', [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', [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, ['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 = 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'
 188         );
 189         return;
 190     }
 191   }
 192
 193   /**
 194    * @param string $name
 195    * @param $mode
 196    * @param $side
 197    *
 198    * @return null|string
 199    */
 200   public static function from($name, $mode, $side) {
 201     $from = NULL;
 202     //get survey clause in force,
 203     //only when we have survey id.
 204     if (!self::$_applySurveyClause) {
 205       return $from;
 206     }
 207
 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);
 212
 213     switch ($name) {
 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) ";
 217         break;
 218
 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 ) ";
 224         break;
 225
 226       case self::CIVICRM_ACTIVITY_ASSIGNMENT:
 227         $from = "
 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 ) ";
 230         break;
 231
 232       case 'civicrm_survey':
 233         $from = " INNER JOIN civicrm_survey ON ( civicrm_survey.id = civicrm_activity.source_record_id ) ";
 234         break;
 235
 236       case 'civicrm_campaign':
 237         $from = " $side JOIN civicrm_campaign ON ( civicrm_campaign.id = civicrm_survey.campaign_id ) ";
 238         break;
 239     }
 240
 241     return $from;
 242   }
 243
 244   /**
 245    * @param $mode
 246    * @param bool $includeCustomFields
 247    *
 248    * @return array|null
 249    */
 250   public static function defaultReturnProperties(
 251     $mode,
 252     $includeCustomFields = TRUE
 253   ) {
 254     $properties = NULL;
 255     if ($mode & CRM_Contact_BAO_Query::MODE_CAMPAIGN) {
 256       $properties = [
 257         'contact_id' => 1,
 258         'contact_type' => 1,
 259         'contact_sub_type' => 1,
 260         'sort_name' => 1,
 261         'display_name' => 1,
 262         'street_unit' => 1,
 263         'street_name' => 1,
 264         'street_number' => 1,
 265         'street_address' => 1,
 266         'city' => 1,
 267         'postal_code' => 1,
 268         'state_province' => 1,
 269         'country' => 1,
 270         'email' => 1,
 271         'phone' => 1,
 272         'survey_activity_target_id' => 1,
 273         'survey_activity_id' => 1,
 274         'survey_status_id' => 1,
 275         'campaign_survey_id' => 1,
 276         'campaign_id' => 1,
 277         'survey_interviewer_id' => 1,
 278         'survey_activity_target_contact_id' => 1,
 279       ];
 280     }
 281
 282     return $properties;
 283   }
 284
 285   /**
 286    * @param $tables
 287    */
 288   public static function tableNames(&$tables) {
 289   }
 290
 291   /**
 292    * @param $row
 293    * @param int $id
 294    */
 295   public static function searchAction(&$row, $id) {
 296   }
 297
 298   /**
 299    * @param $tables
 300    */
 301   public static function info(&$tables) {
 302     //get survey clause in force,
 303     //only when we have survey id.
 304     if (!self::$_applySurveyClause) {
 305       return;
 306     }
 307
 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;
 314   }
 315
 316   /**
 317    * Add all the elements shared between,
 318    * normal voter search and voter listing (GOTV form)
 319    *
 320    * @param CRM_Core_Form $form
 321    */
 322   public static function buildSearchForm(&$form) {
 323
 324     $attributes = CRM_Core_DAO::getAttribute('CRM_Core_DAO_Address');
 325     $className = CRM_Utils_System::getClassName($form);
 326
 327     $form->add('text', 'sort_name', ts('Contact Name'),
 328       CRM_Core_DAO::getAttribute('CRM_Contact_DAO_Contact', 'sort_name')
 329     );
 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']);
 336
 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']
 343     );
 344     $groups = CRM_Core_PseudoConstant::nestedGroup();
 345     $form->add('select', 'group', ts('Groups'), $groups, FALSE,
 346       ['multiple' => 'multiple', 'class' => 'crm-select2']
 347     );
 348
 349     $showInterviewer = FALSE;
 350     if (CRM_Core_Permission::check('administer CiviCampaign')) {
 351       $showInterviewer = TRUE;
 352     }
 353     $form->assign('showInterviewer', $showInterviewer);
 354
 355     if ($showInterviewer ||
 356       $className == 'CRM_Campaign_Form_Gotv'
 357     ) {
 358
 359       $form->addEntityRef('survey_interviewer_id', ts('Interviewer'), ['class' => 'big']);
 360
 361       $userId = NULL;
 362       if (isset($form->_interviewerId) && $form->_interviewerId) {
 363         $userId = $form->_interviewerId;
 364       }
 365       if (!$userId) {
 366         $session = CRM_Core_Session::singleton();
 367         $userId = $session->get('userID');
 368       }
 369       if ($userId) {
 370         $defaults = [];
 371         $defaults['survey_interviewer_id'] = $userId;
 372         $form->setDefaults($defaults);
 373       }
 374     }
 375
 376     //build ward and precinct custom fields.
 377     $query = '
 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) {
 387           $fieldId = $dao->id;
 388           $fieldName = 'custom_' . $dao->id;
 389           $customSearchFields[$name] = $fieldName;
 390           CRM_Core_BAO_CustomField::addQuickFormElement($form, $fieldName, $fieldId, FALSE);
 391           break;
 392         }
 393       }
 394     }
 395     $form->assign('customSearchFields', $customSearchFields);
 396
 397     $surveys = CRM_Campaign_BAO_Survey::getSurveys();
 398
 399     if (empty($surveys) &&
 400       ($className == 'CRM_Campaign_Form_Search')
 401     ) {
 402       CRM_Core_Error::statusBounce(ts('Could not find survey for %1 respondents.',
 403           [1 => $form->get('op')]
 404         ),
 405         CRM_Utils_System::url('civicrm/survey/add',
 406           'reset=1&action=add'
 407         )
 408       );
 409     }
 410
 411     //CRM-7406 --
 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;";
 419     }
 420     $form->add('select', 'campaign_survey_id', ts('Survey'), $surveys, TRUE, $props);
 421   }
 422
 423   /*
 424    * Retrieve all valid voter ids,
 425    * and build respective clause to restrict search.
 426    *
 427    * @param array $criteria
 428    *   An array.
 429    * @return $voterClause as a string
 430    */
 431
 432   /**
 433    * @param array $params
 434    *
 435    * @return array
 436    */
 437   public static function voterClause($params) {
 438     $voterClause = [];
 439     $fromClause = $whereClause = NULL;
 440     if (!is_array($params) || empty($params)) {
 441       return $voterClause;
 442     }
 443     $surveyId = $params['campaign_survey_id'] ?? NULL;
 444     $searchVoterFor = $params['campaign_search_voter_for'] ?? NULL;
 445
 446     //get the survey activities.
 447     $activityStatus = CRM_Core_PseudoConstant::activityStatus('name');
 448     $status = ['Scheduled'];
 449     if ($searchVoterFor == 'reserve') {
 450       $status[] = 'Completed';
 451     }
 452
 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;
 459         }
 460       }
 461     }
 462
 463     $voterActValues = CRM_Campaign_BAO_Survey::getSurveyVoterInfo($surveyId, NULL, $statusIds);
 464
 465     if (!empty($voterActValues)) {
 466       $operator = 'IN';
 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'
 473         );
 474         $recontactInterval = CRM_Utils_String::unserialize($recontactInterval);
 475         if ($surveyId &&
 476           is_array($recontactInterval) &&
 477           !empty($recontactInterval)
 478         ) {
 479           $voterIds = [];
 480           foreach ($voterActValues as $values) {
 481             $numOfDays = $recontactInterval[$values['result']] ?? NULL;
 482             if ($numOfDays &&
 483               $values['status_id'] == $completedStatusId
 484             ) {
 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()) {
 491                 continue;
 492               }
 493             }
 494             $voterIds[$values['voter_id']] = $values['voter_id'];
 495           }
 496         }
 497       }
 498
 499       //lets dump these ids in tmp table and
 500       //use appropriate join depend on operator.
 501       if (!empty($voterIds)) {
 502         $voterIdCount = count($voterIds);
 503
 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 )');
 509
 510         $batch = 100;
 511         $insertedCount = 0;
 512         do {
 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);
 519           }
 520           $insertedCount += $batch;
 521         } while ($insertedCount < $voterIdCount);
 522
 523         if ($operator == 'IN') {
 524           $fromClause = " INNER JOIN {$tempTableName} ON ( {$tempTableName}.survey_contact_id = contact_a.id )";
 525         }
 526         else {
 527           $fromClause = " LEFT JOIN {$tempTableName} ON ( {$tempTableName}.survey_contact_id = contact_a.id )";
 528           $whereClause = "( {$tempTableName}.survey_contact_id IS NULL )";
 529         }
 530       }
 531     }
 532     $voterClause = [
 533       'fromClause' => $fromClause,
 534       'whereClause' => $whereClause,
 535     ];
 536
 537     return $voterClause;
 538   }
 539
 540 }