CRM/Campaign/BAO/Survey.php

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