CRM/Campaign/BAO/Survey.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
  18 /**
  19  * Class CRM_Campaign_BAO_Survey.
  20  */
  21 class CRM_Campaign_BAO_Survey extends CRM_Campaign_DAO_Survey {
  22
  23   /**
  24    * Retrieve DB object based on input parameters.
  25    *
  26    * It also stores all the retrieved values in the default array.
  27    *
  28    * @param array $params
  29    *   (reference ) an assoc array of name/value pairs.
  30    * @param array $defaults
  31    *   (reference ) an assoc array to hold the flattened values.
  32    *
  33    * @return CRM_Campaign_DAO_Survey|null
  34    */
  35   public static function retrieve(&$params, &$defaults) {
  36     $dao = new CRM_Campaign_DAO_Survey();
  37
  38     $dao->copyValues($params);
  39
  40     if ($dao->find(TRUE)) {
  41       CRM_Core_DAO::storeValues($dao, $defaults);
  42       return $dao;
  43     }
  44     return NULL;
  45   }
  46
  47   /**
  48    * Takes an associative array and creates a Survey object based on the
  49    * supplied values.
  50    *
  51    * @param array $params
  52    *
  53    * @return bool|CRM_Campaign_DAO_Survey
  54    */
  55   public static function create(&$params) {
  56     if (empty($params)) {
  57       return FALSE;
  58     }
  59
  60     if (!empty($params['is_default'])) {
  61       $query = "UPDATE civicrm_survey SET is_default = 0";
  62       CRM_Core_DAO::executeQuery($query);
  63     }
  64
  65     if (!(CRM_Utils_Array::value('id', $params))) {
  66
  67       if (!(CRM_Utils_Array::value('created_id', $params))) {
  68         $session = CRM_Core_Session::singleton();
  69         $params['created_id'] = $session->get('userID');
  70       }
  71       if (!(CRM_Utils_Array::value('created_date', $params))) {
  72         $params['created_date'] = date('YmdHis');
  73       }
  74
  75       CRM_Utils_Hook::pre('create', 'Survey', NULL, $params);
  76     }
  77     else {
  78       CRM_Utils_Hook::pre('edit', 'Survey', $params['id'], $params);
  79     }
  80
  81     $dao = new CRM_Campaign_DAO_Survey();
  82     $dao->copyValues($params);
  83     $dao->save();
  84
  85     if (!empty($params['id'])) {
  86       CRM_Utils_Hook::post('edit', 'Survey', $dao->id, $dao);
  87     }
  88     else {
  89       CRM_Utils_Hook::post('create', 'Survey', $dao->id, $dao);
  90     }
  91
  92     if (!empty($params['custom']) &&
  93       is_array($params['custom'])
  94     ) {
  95       CRM_Core_BAO_CustomValueTable::store($params['custom'], 'civicrm_survey', $dao->id);
  96     }
  97     return $dao;
  98   }
  99
 100   /**
 101    * Retrieve surveys for dashboard.
 102    *
 103    * @param array $params
 104    * @param bool $onlyCount
 105    *
 106    * @return array|int
 107    */
 108   public static function getSurveySummary($params = [], $onlyCount = FALSE) {
 109     //build the limit and order clause.
 110     $limitClause = $orderByClause = $lookupTableJoins = NULL;
 111     if (!$onlyCount) {
 112       $sortParams = [
 113         'sort' => 'created_date',
 114         'offset' => 0,
 115         'rowCount' => 10,
 116         'sortOrder' => 'desc',
 117       ];
 118       foreach ($sortParams as $name => $default) {
 119         if (!empty($params[$name])) {
 120           $sortParams[$name] = $params[$name];
 121         }
 122       }
 123
 124       //need to lookup tables.
 125       $orderOnSurveyTable = TRUE;
 126       if ($sortParams['sort'] == 'campaign') {
 127         $orderOnSurveyTable = FALSE;
 128         $lookupTableJoins = '
 129  LEFT JOIN civicrm_campaign campaign ON ( campaign.id = survey.campaign_id )';
 130         $orderByClause = "ORDER BY campaign.title {$sortParams['sortOrder']}";
 131       }
 132       elseif ($sortParams['sort'] == 'activity_type') {
 133         $orderOnSurveyTable = FALSE;
 134         $lookupTableJoins = "
 135  LEFT JOIN civicrm_option_value activity_type ON ( activity_type.value = survey.activity_type_id
 136                                                    OR survey.activity_type_id IS NULL )
 137 INNER JOIN civicrm_option_group grp ON ( activity_type.option_group_id = grp.id AND grp.name = 'activity_type' )";
 138         $orderByClause = "ORDER BY activity_type.label {$sortParams['sortOrder']}";
 139       }
 140       elseif ($sortParams['sort'] == 'isActive') {
 141         $sortParams['sort'] = 'is_active';
 142       }
 143       if ($orderOnSurveyTable) {
 144         $orderByClause = "ORDER BY survey.{$sortParams['sort']} {$sortParams['sortOrder']}";
 145       }
 146       $limitClause = "LIMIT {$sortParams['offset']}, {$sortParams['rowCount']}";
 147     }
 148
 149     //build the where clause.
 150     $queryParams = $where = [];
 151
 152     //we only have activity type as a
 153     //difference between survey and petition.
 154     $petitionTypeID = CRM_Core_PseudoConstant::getKey('CRM_Activity_BAO_Activity', 'activity_type_id', 'Petition');
 155     if ($petitionTypeID) {
 156       $where[] = "( survey.activity_type_id != %1 )";
 157       $queryParams[1] = [$petitionTypeID, 'Positive'];
 158     }
 159
 160     if (!empty($params['title'])) {
 161       $where[] = "( survey.title LIKE %2 )";
 162       $queryParams[2] = ['%' . trim($params['title']) . '%', 'String'];
 163     }
 164     if (!empty($params['campaign_id'])) {
 165       $where[] = '( survey.campaign_id = %3 )';
 166       $queryParams[3] = [$params['campaign_id'], 'Positive'];
 167     }
 168     if (!empty($params['activity_type_id'])) {
 169       $typeId = $params['activity_type_id'];
 170       if (is_array($params['activity_type_id'])) {
 171         $typeId = implode(' , ', $params['activity_type_id']);
 172       }
 173       $where[] = "( survey.activity_type_id IN ( {$typeId} ) )";
 174     }
 175     $whereClause = NULL;
 176     if (!empty($where)) {
 177       $whereClause = ' WHERE ' . implode(" \nAND ", $where);
 178     }
 179
 180     $selectClause = '
 181 SELECT  survey.id                         as id,
 182         survey.title                      as title,
 183         survey.is_active                  as is_active,
 184         survey.result_id                  as result_id,
 185         survey.is_default                 as is_default,
 186         survey.campaign_id                as campaign_id,
 187         survey.activity_type_id           as activity_type_id,
 188         survey.release_frequency          as release_frequency,
 189         survey.max_number_of_contacts     as max_number_of_contacts,
 190         survey.default_number_of_contacts as default_number_of_contacts';
 191     if ($onlyCount) {
 192       $selectClause = 'SELECT COUNT(*)';
 193     }
 194     $fromClause = 'FROM  civicrm_survey survey';
 195
 196     $query = "{$selectClause} {$fromClause} {$lookupTableJoins} {$whereClause} {$orderByClause} {$limitClause}";
 197
 198     //return only count.
 199     if ($onlyCount) {
 200       return (int) CRM_Core_DAO::singleValueQuery($query, $queryParams);
 201     }
 202
 203     $surveys = [];
 204     $properties = [
 205       'id',
 206       'title',
 207       'campaign_id',
 208       'is_active',
 209       'is_default',
 210       'result_id',
 211       'activity_type_id',
 212       'release_frequency',
 213       'max_number_of_contacts',
 214       'default_number_of_contacts',
 215     ];
 216
 217     $survey = CRM_Core_DAO::executeQuery($query, $queryParams);
 218     while ($survey->fetch()) {
 219       foreach ($properties as $property) {
 220         $surveys[$survey->id][$property] = $survey->$property;
 221       }
 222     }
 223
 224     return $surveys;
 225   }
 226
 227   /**
 228    * Get the survey count.
 229    *
 230    */
 231   public static function getSurveyCount() {
 232     return (int) CRM_Core_DAO::singleValueQuery('SELECT COUNT(*) FROM civicrm_survey');
 233   }
 234
 235   /**
 236    * Get Surveys.
 237    *
 238    * @param bool $onlyActive
 239    *   Retrieve only active surveys.
 240    * @param bool $onlyDefault
 241    *   Retrieve only default survey.
 242    * @param bool $forceAll
 243    *   Retrieve all surveys.
 244    * @param bool $includePetition
 245    *   Include or exclude petitions.
 246    *
 247    */
 248   public static function getSurveys($onlyActive = TRUE, $onlyDefault = FALSE, $forceAll = FALSE, $includePetition = FALSE) {
 249     $cacheKey = 0;
 250     $cacheKeyParams = ['onlyActive', 'onlyDefault', 'forceAll', 'includePetition'];
 251     foreach ($cacheKeyParams as $param) {
 252       $cacheParam = $$param;
 253       if (!$cacheParam) {
 254         $cacheParam = 0;
 255       }
 256       $cacheKey .= '_' . $cacheParam;
 257     }
 258
 259     static $surveys;
 260
 261     if (!isset($surveys[$cacheKey])) {
 262       if (!$includePetition) {
 263         //we only have activity type as a
 264         //difference between survey and petition.
 265         $petitionTypeID = CRM_Core_PseudoConstant::getKey('CRM_Activity_BAO_Activity', 'activity_type_id', 'petition');
 266
 267         $where = [];
 268         if ($petitionTypeID) {
 269           $where[] = "( survey.activity_type_id != {$petitionTypeID} )";
 270         }
 271       }
 272       if (!$forceAll && $onlyActive) {
 273         $where[] = '( survey.is_active  = 1 )';
 274       }
 275       if (!$forceAll && $onlyDefault) {
 276         $where[] = '( survey.is_default = 1 )';
 277       }
 278       $whereClause = implode(' AND ', $where);
 279
 280       $query = "
 281 SELECT  survey.id    as id,
 282         survey.title as title
 283   FROM  civicrm_survey as survey
 284  WHERE  {$whereClause}";
 285       $surveys[$cacheKey] = [];
 286       $survey = CRM_Core_DAO::executeQuery($query);
 287       while ($survey->fetch()) {
 288         $surveys[$cacheKey][$survey->id] = $survey->title;
 289       }
 290     }
 291
 292     return $surveys[$cacheKey];
 293   }
 294
 295   /**
 296    * Get Survey activity types.
 297    *
 298    * @param string $returnColumn
 299    * @param bool $includePetitionActivityType
 300    *
 301    * @return mixed
 302    */
 303   public static function getSurveyActivityType($returnColumn = 'label', $includePetitionActivityType = FALSE) {
 304     static $activityTypes;
 305     $cacheKey = "{$returnColumn}_{$includePetitionActivityType}";
 306
 307     if (!isset($activityTypes[$cacheKey])) {
 308       $activityTypes = [];
 309       $campaignCompId = CRM_Core_Component::getComponentID('CiviCampaign');
 310       if ($campaignCompId) {
 311         $condition = " AND v.component_id={$campaignCompId}";
 312         if (!$includePetitionActivityType) {
 313           $condition .= " AND v.name != 'Petition'";
 314         }
 315         $activityTypes[$cacheKey] = CRM_Core_OptionGroup::values('activity_type',
 316           FALSE, FALSE, FALSE,
 317           $condition,
 318           $returnColumn
 319         );
 320       }
 321     }
 322     if (!empty($activityTypes[$cacheKey])) {
 323       return $activityTypes[$cacheKey];
 324     }
 325     else {
 326       return;
 327     }
 328   }
 329
 330   /**
 331    * Get Surveys custom groups.
 332    *
 333    * @param array $surveyTypes
 334    *   an array of survey type id.
 335    *
 336    * @return array
 337    */
 338   public static function getSurveyCustomGroups($surveyTypes = []) {
 339     $customGroups = [];
 340     if (!is_array($surveyTypes)) {
 341       $surveyTypes = [$surveyTypes];
 342     }
 343
 344     if (!empty($surveyTypes)) {
 345       $activityTypes = array_flip($surveyTypes);
 346     }
 347     else {
 348       $activityTypes = self::getSurveyActivityType();
 349     }
 350
 351     if (!empty($activityTypes)) {
 352       $extendSubType = implode('[[:>:]]|[[:<:]]', array_keys($activityTypes));
 353
 354       $query = "SELECT cg.id, cg.name, cg.title, cg.extends_entity_column_value
 355                       FROM civicrm_custom_group cg
 356                       WHERE cg.is_active = 1 AND cg.extends_entity_column_value REGEXP '[[:<:]]{$extendSubType}[[:>:]]'";
 357
 358       $dao = CRM_Core_DAO::executeQuery($query);
 359       while ($dao->fetch()) {
 360         $customGroups[$dao->id]['id'] = $dao->id;
 361         $customGroups[$dao->id]['name'] = $dao->name;
 362         $customGroups[$dao->id]['title'] = $dao->title;
 363         $customGroups[$dao->id]['extends'] = $dao->extends_entity_column_value;
 364       }
 365     }
 366
 367     return $customGroups;
 368   }
 369
 370   /**
 371    * Update the is_active flag in the db.
 372    *
 373    * @param int $id
 374    *   Id of the database record.
 375    * @param bool $is_active
 376    *   Value we want to set the is_active field.
 377    *
 378    * @return bool
 379    *   true if we found and updated the object, else false
 380    */
 381   public static function setIsActive($id, $is_active) {
 382     return CRM_Core_DAO::setFieldValue('CRM_Campaign_DAO_Survey', $id, 'is_active', $is_active);
 383   }
 384
 385   /**
 386    * Delete the survey.
 387    *
 388    * @param int $id
 389    *   Survey id.
 390    *
 391    * @return mixed|null
 392    */
 393   public static function del($id) {
 394     if (!$id) {
 395       return NULL;
 396     }
 397     $reportId = CRM_Campaign_BAO_Survey::getReportID($id);
 398     if ($reportId) {
 399       CRM_Report_BAO_ReportInstance::del($reportId);
 400     }
 401     $dao = new CRM_Campaign_DAO_Survey();
 402     $dao->id = $id;
 403     return $dao->delete();
 404   }
 405
 406   /**
 407    * This function retrieve contact information.
 408    *
 409    * @param array $voterIds
 410    * @param array $returnProperties
 411    *   An array of return elements.
 412    *
 413    * @return array
 414    *   array of contact info.
 415    */
 416   public static function voterDetails($voterIds, $returnProperties = []) {
 417     $voterDetails = [];
 418     if (!is_array($voterIds) || empty($voterIds)) {
 419       return $voterDetails;
 420     }
 421
 422     if (empty($returnProperties)) {
 423       $autocompleteContactSearch = CRM_Core_BAO_Setting::valueOptions(CRM_Core_BAO_Setting::SYSTEM_PREFERENCES_NAME,
 424         'contact_autocomplete_options'
 425       );
 426       $returnProperties = array_fill_keys(array_merge(
 427         ['contact_type', 'contact_sub_type', 'sort_name'],
 428         array_keys($autocompleteContactSearch)
 429       ), 1);
 430     }
 431
 432     $select = $from = [];
 433     foreach ($returnProperties as $property => $ignore) {
 434       $value = (in_array($property, [
 435         'city',
 436         'street_address',
 437       ])) ? 'address' : $property;
 438       switch ($property) {
 439         case 'sort_name':
 440         case 'contact_type':
 441         case 'contact_sub_type':
 442           $select[] = "$property as $property";
 443           $from['contact'] = 'civicrm_contact contact';
 444           break;
 445
 446         case 'email':
 447         case 'phone':
 448         case 'city':
 449         case 'street_address':
 450           $select[] = "$property as $property";
 451           $from[$value] = "LEFT JOIN civicrm_{$value} {$value} ON ( contact.id = {$value}.contact_id AND {$value}.is_primary = 1 ) ";
 452           break;
 453
 454         case 'country':
 455         case 'state_province':
 456           $select[] = "{$property}.name as $property";
 457           if (!in_array('address', $from)) {
 458             $from['address'] = 'LEFT JOIN civicrm_address address ON ( contact.id = address.contact_id AND address.is_primary = 1) ';
 459           }
 460           $from[$value] = " LEFT JOIN civicrm_{$value} {$value} ON ( address.{$value}_id = {$value}.id  ) ";
 461           break;
 462       }
 463     }
 464
 465     //finally retrieve contact details.
 466     if (!empty($select) && !empty($from)) {
 467       $fromClause = implode(' ', $from);
 468       $selectClause = implode(', ', $select);
 469       $whereClause = "contact.id IN (" . implode(',', $voterIds) . ')';
 470
 471       $query = "
 472   SELECT  contact.id as contactId, $selectClause
 473     FROM  $fromClause
 474    WHERE  $whereClause";
 475
 476       $contact = CRM_Core_DAO::executeQuery($query);
 477       while ($contact->fetch()) {
 478         $voterDetails[$contact->contactId]['contact_id'] = $contact->contactId;
 479         foreach ($returnProperties as $property => $ignore) {
 480           $voterDetails[$contact->contactId][$property] = $contact->$property;
 481         }
 482         $image = CRM_Contact_BAO_Contact_Utils::getImage($contact->contact_sub_type ? $contact->contact_sub_type : $contact->contact_type,
 483           FALSE,
 484           $contact->contactId
 485         );
 486         $voterDetails[$contact->contactId]['contact_type'] = $image;
 487       }
 488     }
 489
 490     return $voterDetails;
 491   }
 492
 493   /**
 494    * This function retrieve survey related activities w/ for give voter ids.
 495    *
 496    * @param int $surveyId
 497    *   Survey id.
 498    * @param array $voterIds
 499    *   VoterIds.
 500    *
 501    * @param int $interviewerId
 502    * @param array $statusIds
 503    *
 504    * @return array
 505    *   array of survey activity.
 506    */
 507   public static function voterActivityDetails($surveyId, $voterIds, $interviewerId = NULL, $statusIds = []) {
 508     $activityDetails = [];
 509     if (!$surveyId ||
 510       !is_array($voterIds) || empty($voterIds)
 511     ) {
 512       return $activityDetails;
 513     }
 514
 515     $whereClause = NULL;
 516     if (is_array($statusIds) && !empty($statusIds)) {
 517       $whereClause = ' AND ( activity.status_id IN ( ' . implode(',', array_values($statusIds)) . ' ) )';
 518     }
 519
 520     $targetContactIds = ' ( ' . implode(',', $voterIds) . ' ) ';
 521     $activityContacts = CRM_Activity_BAO_ActivityContact::buildOptions('record_type_id', 'validate');
 522     $assigneeID = CRM_Utils_Array::key('Activity Assignees', $activityContacts);
 523     $targetID = CRM_Utils_Array::key('Activity Targets', $activityContacts);
 524
 525     $params[1] = [$surveyId, 'Integer'];
 526     $query = "
 527     SELECT  activity.id, activity.status_id,
 528             activityTarget.contact_id as voter_id,
 529             activityAssignment.contact_id as interviewer_id
 530       FROM  civicrm_activity activity
 531 INNER JOIN  civicrm_activity_contact activityTarget
 532   ON ( activityTarget.activity_id = activity.id AND activityTarget.record_type_id = $targetID )
 533 INNER JOIN  civicrm_activity_contact activityAssignment
 534   ON ( activityAssignment.activity_id = activity.id AND activityAssignment.record_type_id = $assigneeID )
 535      WHERE  activity.source_record_id = %1
 536      AND  ( activity.is_deleted IS NULL OR activity.is_deleted = 0 ) ";
 537     if (!empty($interviewerId)) {
 538       $query .= "AND activityAssignment.contact_id = %2 ";
 539       $params[2] = [$interviewerId, 'Integer'];
 540     }
 541     $query .= "AND  activityTarget.contact_id IN {$targetContactIds}
 542             $whereClause";
 543     $activity = CRM_Core_DAO::executeQuery($query, $params);
 544     while ($activity->fetch()) {
 545       $activityDetails[$activity->voter_id] = [
 546         'voter_id' => $activity->voter_id,
 547         'status_id' => $activity->status_id,
 548         'activity_id' => $activity->id,
 549         'interviewer_id' => $activity->interviewer_id,
 550       ];
 551     }
 552
 553     return $activityDetails;
 554   }
 555
 556   /**
 557    * This function retrieve survey related activities.
 558    *
 559    * @param int $surveyId
 560    * @param int $interviewerId
 561    * @param array $statusIds
 562    * @param array $voterIds
 563    * @param bool $onlyCount
 564    *
 565    * @return array
 566    *   An array of survey activity.
 567    */
 568   public static function getSurveyActivities(
 569     $surveyId,
 570     $interviewerId = NULL,
 571     $statusIds = NULL,
 572     $voterIds = NULL,
 573     $onlyCount = FALSE
 574   ) {
 575     $activities = [];
 576     $surveyActivityCount = 0;
 577     if (!$surveyId) {
 578       return ($onlyCount) ? 0 : $activities;
 579     }
 580
 581     $where = [];
 582     if (!empty($statusIds)) {
 583       $where[] = '( activity.status_id IN ( ' . implode(',', array_values($statusIds)) . ' ) )';
 584     }
 585
 586     if ($interviewerId) {
 587       $where[] = "( activityAssignment.contact_id =  $interviewerId )";
 588     }
 589
 590     if (!empty($voterIds)) {
 591       $where[] = "( activityTarget.contact_id IN ( " . implode(',', $voterIds) . " ) )";
 592     }
 593
 594     $whereClause = NULL;
 595     if (!empty($where)) {
 596       $whereClause = ' AND ( ' . implode(' AND ', $where) . ' )';
 597     }
 598
 599     $actTypeId = CRM_Core_DAO::getFieldValue('CRM_Campaign_DAO_Survey', $surveyId, 'activity_type_id');
 600     if (!$actTypeId) {
 601       return $activities;
 602     }
 603
 604     if ($onlyCount) {
 605       $select = "SELECT count(activity.id)";
 606     }
 607     else {
 608       $select = "
 609     SELECT  activity.id, activity.status_id,
 610             activityTarget.contact_id as voter_id,
 611             activityAssignment.contact_id as interviewer_id,
 612             activity.result as result,
 613             activity.activity_date_time as activity_date_time,
 614             contact_a.display_name as voter_name";
 615     }
 616
 617     $activityContacts = CRM_Activity_BAO_ActivityContact::buildOptions('record_type_id', 'validate');
 618     $assigneeID = CRM_Utils_Array::key('Activity Assignees', $activityContacts);
 619     $targetID = CRM_Utils_Array::key('Activity Targets', $activityContacts);
 620
 621     $query = "
 622             $select
 623       FROM  civicrm_activity activity
 624 INNER JOIN  civicrm_activity_contact activityTarget
 625   ON ( activityTarget.activity_id = activity.id AND activityTarget.record_type_id = $targetID )
 626 INNER JOIN  civicrm_activity_contact activityAssignment
 627   ON ( activityAssignment.activity_id = activity.id AND activityAssignment.record_type_id = $assigneeID )
 628 INNER JOIN  civicrm_contact contact_a ON ( activityTarget.contact_id = contact_a.id )
 629      WHERE  activity.source_record_id = %1
 630        AND  activity.activity_type_id = %2
 631        AND  ( activity.is_deleted IS NULL OR activity.is_deleted = 0 )
 632             $whereClause";
 633
 634     $params = [
 635       1 => [$surveyId, 'Integer'],
 636       2 => [$actTypeId, 'Integer'],
 637     ];
 638
 639     if ($onlyCount) {
 640       $dbCount = CRM_Core_DAO::singleValueQuery($query, $params);
 641       return ($dbCount) ? $dbCount : 0;
 642     }
 643
 644     $activity = CRM_Core_DAO::executeQuery($query, $params);
 645
 646     while ($activity->fetch()) {
 647       $activities[$activity->id] = [
 648         'id' => $activity->id,
 649         'voter_id' => $activity->voter_id,
 650         'voter_name' => $activity->voter_name,
 651         'status_id' => $activity->status_id,
 652         'interviewer_id' => $activity->interviewer_id,
 653         'result' => $activity->result,
 654         'activity_date_time' => $activity->activity_date_time,
 655       ];
 656     }
 657
 658     return $activities;
 659   }
 660
 661   /**
 662    * Retrieve survey voter information.
 663    *
 664    * @param int $surveyId
 665    *   Survey id.
 666    * @param int $interviewerId
 667    *   Interviewer id.
 668    * @param array $statusIds
 669    *   Survey status ids.
 670    *
 671    * @return array
 672    *   Survey related contact ids.
 673    */
 674   public static function getSurveyVoterInfo($surveyId, $interviewerId = NULL, $statusIds = []) {
 675     $voterIds = [];
 676     if (!$surveyId) {
 677       return $voterIds;
 678     }
 679
 680     $cacheKey = $surveyId;
 681     if ($interviewerId) {
 682       $cacheKey .= "_{$interviewerId}";
 683     }
 684     if (is_array($statusIds) && !empty($statusIds)) {
 685       $cacheKey = "{$cacheKey}_" . implode('_', $statusIds);
 686     }
 687
 688     static $contactIds = [];
 689     if (!isset($contactIds[$cacheKey])) {
 690       $activities = self::getSurveyActivities($surveyId, $interviewerId, $statusIds);
 691       foreach ($activities as $values) {
 692         $voterIds[$values['voter_id']] = $values;
 693       }
 694       $contactIds[$cacheKey] = $voterIds;
 695     }
 696
 697     return $contactIds[$cacheKey];
 698   }
 699
 700   /**
 701    * This function retrieve all option groups which are created as a result set.
 702    *
 703    * @param string $valueColumnName
 704    * @return array
 705    *   an array of option groups.
 706    */
 707   public static function getResultSets($valueColumnName = 'title') {
 708     $resultSets = [];
 709     $valueColumnName = CRM_Utils_Type::escape($valueColumnName, 'String');
 710
 711     $query = "SELECT id, {$valueColumnName} FROM civicrm_option_group WHERE name LIKE 'civicrm_survey_%' AND is_active=1";
 712     $dao = CRM_Core_DAO::executeQuery($query);
 713     while ($dao->fetch()) {
 714       $resultSets[$dao->id] = $dao->$valueColumnName;
 715     }
 716
 717     return $resultSets;
 718   }
 719
 720   /**
 721    * check survey activity.
 722    *
 723    * @param int $activityId
 724    *   Activity id.
 725    * @return bool
 726    */
 727   public static function isSurveyActivity($activityId) {
 728     $isSurveyActivity = FALSE;
 729     if (!$activityId) {
 730       return $isSurveyActivity;
 731     }
 732
 733     $activity = new CRM_Activity_DAO_Activity();
 734     $activity->id = $activityId;
 735     $activity->selectAdd('source_record_id, activity_type_id');
 736     if ($activity->find(TRUE) &&
 737       $activity->source_record_id
 738     ) {
 739       $surveyActTypes = self::getSurveyActivityType();
 740       if (array_key_exists($activity->activity_type_id, $surveyActTypes)) {
 741         $isSurveyActivity = TRUE;
 742       }
 743     }
 744
 745     return $isSurveyActivity;
 746   }
 747
 748   /**
 749    * This function retrive all response options of survey.
 750    *
 751    * @param int $surveyId
 752    *   Survey id.
 753    * @return array
 754    *   an array of option values
 755    */
 756   public static function getResponsesOptions($surveyId) {
 757     $responseOptions = [];
 758     if (!$surveyId) {
 759       return $responseOptions;
 760     }
 761
 762     $resultId = CRM_Core_DAO::getFieldValue('CRM_Campaign_DAO_Survey', $surveyId, 'result_id');
 763     if ($resultId) {
 764       $responseOptions = CRM_Core_OptionGroup::valuesByID($resultId);
 765     }
 766
 767     return $responseOptions;
 768   }
 769
 770   /**
 771    * This function return all voter links with respecting permissions.
 772    *
 773    * @param int $surveyId
 774    * @param bool $enclosedInUL
 775    * @param string $extraULName
 776    * @return array|string
 777    *   $url array of permissioned links
 778    */
 779   public static function buildPermissionLinks($surveyId, $enclosedInUL = FALSE, $extraULName = 'more') {
 780     $menuLinks = [];
 781     if (!$surveyId) {
 782       return $menuLinks;
 783     }
 784
 785     static $voterLinks = [];
 786     if (empty($voterLinks)) {
 787       $permissioned = FALSE;
 788       if (CRM_Core_Permission::check('manage campaign') ||
 789         CRM_Core_Permission::check('administer CiviCampaign')
 790       ) {
 791         $permissioned = TRUE;
 792       }
 793
 794       if ($permissioned || CRM_Core_Permission::check("reserve campaign contacts")) {
 795         $voterLinks['reserve'] = [
 796           'name' => 'reserve',
 797           'url' => 'civicrm/survey/search',
 798           'qs' => 'sid=%%id%%&reset=1&op=reserve',
 799           'title' => ts('Reserve Respondents'),
 800         ];
 801       }
 802       if ($permissioned || CRM_Core_Permission::check("interview campaign contacts")) {
 803         $voterLinks['release'] = [
 804           'name' => 'interview',
 805           'url' => 'civicrm/survey/search',
 806           'qs' => 'sid=%%id%%&reset=1&op=interview&force=1',
 807           'title' => ts('Interview Respondents'),
 808         ];
 809       }
 810       if ($permissioned || CRM_Core_Permission::check("release campaign contacts")) {
 811         $voterLinks['interview'] = [
 812           'name' => 'release',
 813           'url' => 'civicrm/survey/search',
 814           'qs' => 'sid=%%id%%&reset=1&op=release&force=1',
 815           'title' => ts('Release Respondents'),
 816         ];
 817       }
 818     }
 819
 820     if (CRM_Core_Permission::check('access CiviReport')) {
 821       $reportID = self::getReportID($surveyId);
 822       if ($reportID) {
 823         $voterLinks['report'] = [
 824           'name' => 'report',
 825           'url' => "civicrm/report/instance/{$reportID}",
 826           'qs' => 'reset=1',
 827           'title' => ts('View Survey Report'),
 828         ];
 829       }
 830     }
 831
 832     $ids = ['id' => $surveyId];
 833     foreach ($voterLinks as $link) {
 834       if (!empty($link['qs']) &&
 835         !CRM_Utils_System::isNull($link['qs'])
 836       ) {
 837         $urlPath = CRM_Utils_System::url(CRM_Core_Action::replace($link['url'], $ids),
 838           CRM_Core_Action::replace($link['qs'], $ids)
 839         );
 840         $menuLinks[] = sprintf('<a href="%s" class="action-item crm-hover-button" title="%s">%s</a>',
 841           $urlPath,
 842           CRM_Utils_Array::value('title', $link),
 843           $link['title']
 844         );
 845       }
 846     }
 847     if ($enclosedInUL) {
 848       $extraLinksName = strtolower($extraULName);
 849       $allLinks = '';
 850       CRM_Utils_String::append($allLinks, '</li><li>', $menuLinks);
 851       $allLinks = "$extraULName <ul id='panel_{$extraLinksName}_xx' class='panel'><li>{$allLinks}</li></ul>";
 852       $menuLinks = "<span class='btn-slide crm-hover-button' id={$extraLinksName}_xx>{$allLinks}</span>";
 853     }
 854
 855     return $menuLinks;
 856   }
 857
 858   /**
 859    * Retrieve survey associated profile id.
 860    *
 861    * @param int $surveyId
 862    *
 863    * @return mixed|null
 864    */
 865   public static function getSurveyProfileId($surveyId) {
 866     if (!$surveyId) {
 867       return NULL;
 868     }
 869
 870     static $ufIds = [];
 871     if (!array_key_exists($surveyId, $ufIds)) {
 872       //get the profile id.
 873       $ufJoinParams = [
 874         'entity_id' => $surveyId,
 875         'entity_table' => 'civicrm_survey',
 876         'module' => 'CiviCampaign',
 877       ];
 878
 879       list($first, $second) = CRM_Core_BAO_UFJoin::getUFGroupIds($ufJoinParams);
 880
 881       if ($first) {
 882         $ufIds[$surveyId] = [$first];
 883       }
 884       if ($second) {
 885         $ufIds[$surveyId][] = array_shift($second);
 886       }
 887     }
 888
 889     return $ufIds[$surveyId] ?? NULL;
 890   }
 891
 892   /**
 893    * @param int $surveyId
 894    *
 895    * @return mixed
 896    */
 897   public static function getReportID($surveyId) {
 898     static $reportIds = [];
 899
 900     if (!array_key_exists($surveyId, $reportIds)) {
 901       $query = "SELECT MAX(id) as id FROM civicrm_report_instance WHERE name = %1";
 902       $reportID = CRM_Core_DAO::singleValueQuery($query, [1 => ["survey_{$surveyId}", 'String']]);
 903       $reportIds[$surveyId] = $reportID;
 904     }
 905     return $reportIds[$surveyId];
 906   }
 907
 908   /**
 909    * Decides the contact type for given survey.
 910    *
 911    * @param int $surveyId
 912    *
 913    * @return null|string
 914    */
 915   public static function getSurveyContactType($surveyId) {
 916     $contactType = NULL;
 917
 918     //apply filter of profile type on search.
 919     $profileId = self::getSurveyProfileId($surveyId);
 920     if ($profileId) {
 921       $profileType = CRM_Core_BAO_UFField::getProfileType($profileId);
 922       if (in_array($profileType, CRM_Contact_BAO_ContactType::basicTypes())) {
 923         $contactType = $profileType;
 924       }
 925     }
 926
 927     return $contactType;
 928   }
 929
 930   /**
 931    * Get survey supportable profile types.
 932    */
 933   public static function surveyProfileTypes() {
 934     static $profileTypes;
 935
 936     if (!isset($profileTypes)) {
 937       $profileTypes = array_merge(['Activity', 'Contact'], CRM_Contact_BAO_ContactType::basicTypes());
 938       $profileTypes = array_diff($profileTypes, ['Organization', 'Household']);
 939     }
 940
 941     return $profileTypes;
 942   }
 943
 944   /**
 945    * Get the valid survey response fields those.
 946    * are configured with profile and custom fields.
 947    *
 948    * @param int $surveyId
 949    *   Survey id.
 950    * @param int $surveyTypeId
 951    *   Survey activity type id.
 952    *
 953    * @return array
 954    *   an array of valid survey response fields.
 955    */
 956   public static function getSurveyResponseFields($surveyId, $surveyTypeId = NULL) {
 957     if (empty($surveyId)) {
 958       return [];
 959     }
 960
 961     static $responseFields;
 962     $cacheKey = "{$surveyId}_{$surveyTypeId}";
 963
 964     if (isset($responseFields[$cacheKey])) {
 965       return $responseFields[$cacheKey];
 966     }
 967
 968     $responseFields[$cacheKey] = [];
 969
 970     $profileId = self::getSurveyProfileId($surveyId);
 971
 972     if (!$profileId) {
 973       return $responseFields;
 974     }
 975
 976     if (!$surveyTypeId) {
 977       $surveyTypeId = CRM_Core_DAO::getFieldValue('CRM_Campaign_DAO_Survey', $surveyId, 'activity_type_id');
 978     }
 979
 980     $profileFields = CRM_Core_BAO_UFGroup::getFields($profileId,
 981       FALSE, CRM_Core_Action::VIEW, NULL, NULL, FALSE, NULL, FALSE, NULL, CRM_Core_Permission::CREATE, 'field_name', TRUE
 982     );
 983
 984     //don't load these fields in grid.
 985     $removeFields = ['File', 'RichTextEditor'];
 986
 987     $supportableFieldTypes = self::surveyProfileTypes();
 988
 989     // get custom fields of type survey
 990     $customFields = CRM_Core_BAO_CustomField::getFields('Activity', FALSE, FALSE, $surveyTypeId);
 991
 992     foreach ($profileFields as $name => $field) {
 993       //get only contact and activity fields.
 994       //later stage we might going to consider contact type also.
 995       if (in_array($field['field_type'], $supportableFieldTypes)) {
 996         // we should allow all supported custom data for survey
 997         // In case of activity, allow normal activity and with subtype survey,
 998         // suppress custom data of other activity types
 999         if (CRM_Core_BAO_CustomField::getKeyID($name)) {
1000           if (!in_array($field['html_type'], $removeFields)) {
1001             if ($field['field_type'] != 'Activity') {
1002               $responseFields[$cacheKey][$name] = $field;
1003             }
1004             elseif (array_key_exists(CRM_Core_BAO_CustomField::getKeyID($name), $customFields)) {
1005               $responseFields[$cacheKey][$name] = $field;
1006             }
1007           }
1008         }
1009         else {
1010           $responseFields[$cacheKey][$name] = $field;
1011         }
1012       }
1013     }
1014
1015     return $responseFields[$cacheKey];
1016   }
1017
1018   /**
1019    * Get all interviewers of surveys.
1020    *
1021    * @return array
1022    *   an array of valid survey response fields.
1023    */
1024   public static function getInterviewers() {
1025     static $interviewers;
1026
1027     if (isset($interviewers)) {
1028       return $interviewers;
1029     }
1030
1031     $whereClause = NULL;
1032     $activityTypes = self::getSurveyActivityType();
1033     if (!empty($activityTypes)) {
1034       $whereClause = ' WHERE survey.activity_type_id IN ( ' . implode(' , ', array_keys($activityTypes)) . ' )';
1035     }
1036
1037     $interviewers = [];
1038     $activityContacts = CRM_Activity_BAO_ActivityContact::buildOptions('record_type_id', 'validate');
1039     $assigneeID = CRM_Utils_Array::key('Activity Assignees', $activityContacts);
1040
1041     $query = "
1042     SELECT  contact.id as id,
1043             contact.sort_name as sort_name
1044       FROM  civicrm_contact contact
1045 INNER JOIN civicrm_activity_contact assignment ON ( assignment.contact_id = contact.id AND record_type_id = $assigneeID )
1046 INNER JOIN  civicrm_activity activity ON ( activity.id = assignment.activity_id )
1047 INNER JOIN  civicrm_survey survey ON ( activity.source_record_id = survey.id )
1048             {$whereClause}";
1049
1050     $interviewer = CRM_Core_DAO::executeQuery($query);
1051     while ($interviewer->fetch()) {
1052       $interviewers[$interviewer->id] = $interviewer->sort_name;
1053     }
1054
1055     return $interviewers;
1056   }
1057
1058   /**
1059    * Check and update the survey respondents.
1060    *
1061    * @param array $params
1062    *
1063    * @return array
1064    *   success message
1065    */
1066   public static function releaseRespondent($params) {
1067     $activityStatus = CRM_Core_PseudoConstant::activityStatus('name');
1068     $reserveStatusId = array_search('Scheduled', $activityStatus);
1069     $surveyActivityTypes = CRM_Campaign_BAO_Survey::getSurveyActivityType();
1070     if (!empty($surveyActivityTypes) && is_array($surveyActivityTypes)) {
1071       $surveyActivityTypesIds = array_keys($surveyActivityTypes);
1072     }
1073
1074     //retrieve all survey activities related to reserve action.
1075     $releasedCount = 0;
1076     if ($reserveStatusId && !empty($surveyActivityTypesIds)) {
1077       $query = '
1078     SELECT  activity.id as id,
1079             activity.activity_date_time as activity_date_time,
1080             survey.id as surveyId,
1081             survey.release_frequency as release_frequency
1082       FROM  civicrm_activity activity
1083 INNER JOIN  civicrm_survey survey ON ( survey.id = activity.source_record_id )
1084      WHERE  activity.is_deleted = 0
1085        AND  activity.status_id = %1
1086        AND  activity.activity_type_id IN ( ' . implode(', ', $surveyActivityTypesIds) . ' )';
1087       $activity = CRM_Core_DAO::executeQuery($query, [1 => [$reserveStatusId, 'Positive']]);
1088       $releasedIds = [];
1089       while ($activity->fetch()) {
1090         if (!$activity->release_frequency) {
1091           continue;
1092         }
1093         $reservedSeconds = CRM_Utils_Date::unixTime($activity->activity_date_time);
1094         $releasedSeconds = $activity->release_frequency * 24 * 3600;
1095         $totalReservedSeconds = $reservedSeconds + $releasedSeconds;
1096         if ($totalReservedSeconds < time()) {
1097           $releasedIds[$activity->id] = $activity->id;
1098         }
1099       }
1100
1101       //released respondent.
1102       if (!empty($releasedIds)) {
1103         $query = '
1104 UPDATE  civicrm_activity
1105    SET  is_deleted = 1
1106  WHERE  id IN ( ' . implode(', ', $releasedIds) . ' )';
1107         CRM_Core_DAO::executeQuery($query);
1108         $releasedCount = count($releasedIds);
1109       }
1110     }
1111
1112     $rtnMsg = [
1113       'is_error' => 0,
1114       'messages' => "Number of respondents released = {$releasedCount}",
1115     ];
1116
1117     return $rtnMsg;
1118   }
1119
1120   /**
1121    * Get options for a given field.
1122    * @see CRM_Core_DAO::buildOptions
1123    *
1124    * @param string $fieldName
1125    * @param string $context : @see CRM_Core_DAO::buildOptionsContext
1126    * @param array $props : whatever is known about this dao object
1127    *
1128    * @return array|bool
1129    */
1130   public static function buildOptions($fieldName, $context = NULL, $props = []) {
1131     $params = [];
1132     // Special logic for fields whose options depend on context or properties
1133     switch ($fieldName) {
1134       case 'activity_type_id':
1135         $campaignCompId = CRM_Core_Component::getComponentID('CiviCampaign');
1136         if ($campaignCompId) {
1137           $params['condition'] = ["component_id={$campaignCompId}"];
1138         }
1139         break;
1140     }
1141     return CRM_Core_PseudoConstant::get(__CLASS__, $fieldName, $params, $context);
1142   }
1143
1144 }