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 = array(), $onlyCount = FALSE) {
 125     //build the limit and order clause.
 126     $limitClause = $orderByClause = $lookupTableJoins = NULL;
 127     if (!$onlyCount) {
 128       $sortParams = array(
 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 = array();
 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] = array($petitionTypeID, 'Positive');
 174     }
 175
 176     if (!empty($params['title'])) {
 177       $where[] = "( survey.title LIKE %2 )";
 178       $queryParams[2] = array('%' . trim($params['title']) . '%', 'String');
 179     }
 180     if (!empty($params['campaign_id'])) {
 181       $where[] = '( survey.campaign_id = %3 )';
 182       $queryParams[3] = array($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 = array();
 220     $properties = array(
 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 = array('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 = array();
 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] = array();
 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 string
 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 = array();
 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 = array()) {
 355     $customGroups = array();
 356     if (!is_array($surveyTypes)) {
 357       $surveyTypes = array($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 = array()) {
 433     $voterDetails = array();
 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(array(
 443           'contact_type',
 444           'contact_sub_type',
 445           'sort_name',
 446         ),
 447         array_keys($autocompleteContactSearch)
 448       ), 1);
 449     }
 450
 451     $select = $from = array();
 452     foreach ($returnProperties as $property => $ignore) {
 453       $value = (in_array($property, array(
 454         'city',
 455         'street_address',
 456       ))) ? 'address' : $property;
 457       switch ($property) {
 458         case 'sort_name':
 459         case 'contact_type':
 460         case 'contact_sub_type':
 461           $select[] = "$property as $property";
 462           $from['contact'] = 'civicrm_contact contact';
 463           break;
 464
 465         case 'email':
 466         case 'phone':
 467         case 'city':
 468         case 'street_address':
 469           $select[] = "$property as $property";
 470           $from[$value] = "LEFT JOIN civicrm_{$value} {$value} ON ( contact.id = {$value}.contact_id AND {$value}.is_primary = 1 ) ";
 471           break;
 472
 473         case 'country':
 474         case 'state_province':
 475           $select[] = "{$property}.name as $property";
 476           if (!in_array('address', $from)) {
 477             $from['address'] = 'LEFT JOIN civicrm_address address ON ( contact.id = address.contact_id AND address.is_primary = 1) ';
 478           }
 479           $from[$value] = " LEFT JOIN civicrm_{$value} {$value} ON ( address.{$value}_id = {$value}.id  ) ";
 480           break;
 481       }
 482     }
 483
 484     //finally retrieve contact details.
 485     if (!empty($select) && !empty($from)) {
 486       $fromClause = implode(' ', $from);
 487       $selectClause = implode(', ', $select);
 488       $whereClause = "contact.id IN (" . implode(',', $voterIds) . ')';
 489
 490       $query = "
 491   SELECT  contact.id as contactId, $selectClause
 492     FROM  $fromClause
 493    WHERE  $whereClause";
 494
 495       $contact = CRM_Core_DAO::executeQuery($query);
 496       while ($contact->fetch()) {
 497         $voterDetails[$contact->contactId]['contact_id'] = $contact->contactId;
 498         foreach ($returnProperties as $property => $ignore) {
 499           $voterDetails[$contact->contactId][$property] = $contact->$property;
 500         }
 501         $image = CRM_Contact_BAO_Contact_Utils::getImage($contact->contact_sub_type ? $contact->contact_sub_type : $contact->contact_type,
 502           FALSE,
 503           $contact->contactId
 504         );
 505         $voterDetails[$contact->contactId]['contact_type'] = $image;
 506       }
 507     }
 508
 509     return $voterDetails;
 510   }
 511
 512   /**
 513    * This function retrieve survey related activities w/ for give voter ids.
 514    *
 515    * @param int $surveyId
 516    *   Survey id.
 517    * @param array $voterIds
 518    *   VoterIds.
 519    *
 520    * @param int $interviewerId
 521    * @param array $statusIds
 522    *
 523    * @return array
 524    *   array of survey activity.
 525    */
 526   public static function voterActivityDetails($surveyId, $voterIds, $interviewerId = NULL, $statusIds = array()) {
 527     $activityDetails = array();
 528     if (!$surveyId ||
 529       !is_array($voterIds) || empty($voterIds)
 530     ) {
 531       return $activityDetails;
 532     }
 533
 534     $whereClause = NULL;
 535     if (is_array($statusIds) && !empty($statusIds)) {
 536       $whereClause = ' AND ( activity.status_id IN ( ' . implode(',', array_values($statusIds)) . ' ) )';
 537     }
 538
 539     $targetContactIds = ' ( ' . implode(',', $voterIds) . ' ) ';
 540     $activityContacts = CRM_Activity_BAO_ActivityContact::buildOptions('record_type_id', 'validate');
 541     $assigneeID = CRM_Utils_Array::key('Activity Assignees', $activityContacts);
 542     $targetID = CRM_Utils_Array::key('Activity Targets', $activityContacts);
 543
 544     $params[1] = array($surveyId, 'Integer');
 545     $query = "
 546     SELECT  activity.id, activity.status_id,
 547             activityTarget.contact_id as voter_id,
 548             activityAssignment.contact_id as interviewer_id
 549       FROM  civicrm_activity activity
 550 INNER JOIN  civicrm_activity_contact activityTarget
 551   ON ( activityTarget.activity_id = activity.id AND activityTarget.record_type_id = $targetID )
 552 INNER JOIN  civicrm_activity_contact activityAssignment
 553   ON ( activityAssignment.activity_id = activity.id AND activityAssignment.record_type_id = $assigneeID )
 554      WHERE  activity.source_record_id = %1
 555      AND  ( activity.is_deleted IS NULL OR activity.is_deleted = 0 ) ";
 556     if (!empty($interviewerId)) {
 557       $query .= "AND activityAssignment.contact_id = %2 ";
 558       $params[2] = array($interviewerId, 'Integer');
 559     }
 560     $query .= "AND  activityTarget.contact_id IN {$targetContactIds}
 561             $whereClause";
 562     $activity = CRM_Core_DAO::executeQuery($query, $params);
 563     while ($activity->fetch()) {
 564       $activityDetails[$activity->voter_id] = array(
 565         'voter_id' => $activity->voter_id,
 566         'status_id' => $activity->status_id,
 567         'activity_id' => $activity->id,
 568         'interviewer_id' => $activity->interviewer_id,
 569       );
 570     }
 571
 572     return $activityDetails;
 573   }
 574
 575   /**
 576    * This function retrieve survey related activities.
 577    *
 578    * @param int $surveyId
 579    * @param int $interviewerId
 580    * @param array $statusIds
 581    * @param array $voterIds
 582    * @param bool $onlyCount
 583    *
 584    * @return array
 585    *   An array of survey activity.
 586    */
 587   public static function getSurveyActivities(
 588     $surveyId,
 589     $interviewerId = NULL,
 590     $statusIds = NULL,
 591     $voterIds = NULL,
 592     $onlyCount = FALSE
 593   ) {
 594     $activities = array();
 595     $surveyActivityCount = 0;
 596     if (!$surveyId) {
 597       return ($onlyCount) ? 0 : $activities;
 598     }
 599
 600     $where = array();
 601     if (!empty($statusIds)) {
 602       $where[] = '( activity.status_id IN ( ' . implode(',', array_values($statusIds)) . ' ) )';
 603     }
 604
 605     if ($interviewerId) {
 606       $where[] = "( activityAssignment.contact_id =  $interviewerId )";
 607     }
 608
 609     if (!empty($voterIds)) {
 610       $where[] = "( activityTarget.contact_id IN ( " . implode(',', $voterIds) . " ) )";
 611     }
 612
 613     $whereClause = NULL;
 614     if (!empty($where)) {
 615       $whereClause = ' AND ( ' . implode(' AND ', $where) . ' )';
 616     }
 617
 618     $actTypeId = CRM_Core_DAO::getFieldValue('CRM_Campaign_DAO_Survey', $surveyId, 'activity_type_id');
 619     if (!$actTypeId) {
 620       return $activities;
 621     }
 622
 623     if ($onlyCount) {
 624       $select = "SELECT count(activity.id)";
 625     }
 626     else {
 627       $select = "
 628     SELECT  activity.id, activity.status_id,
 629             activityTarget.contact_id as voter_id,
 630             activityAssignment.contact_id as interviewer_id,
 631             activity.result as result,
 632             activity.activity_date_time as activity_date_time,
 633             contact_a.display_name as voter_name";
 634     }
 635
 636     $activityContacts = CRM_Activity_BAO_ActivityContact::buildOptions('record_type_id', 'validate');
 637     $assigneeID = CRM_Utils_Array::key('Activity Assignees', $activityContacts);
 638     $targetID = CRM_Utils_Array::key('Activity Targets', $activityContacts);
 639
 640     $query = "
 641             $select
 642       FROM  civicrm_activity activity
 643 INNER JOIN  civicrm_activity_contact activityTarget
 644   ON ( activityTarget.activity_id = activity.id AND activityTarget.record_type_id = $targetID )
 645 INNER JOIN  civicrm_activity_contact activityAssignment
 646   ON ( activityAssignment.activity_id = activity.id AND activityAssignment.record_type_id = $assigneeID )
 647 INNER JOIN  civicrm_contact contact_a ON ( activityTarget.contact_id = contact_a.id )
 648      WHERE  activity.source_record_id = %1
 649        AND  activity.activity_type_id = %2
 650        AND  ( activity.is_deleted IS NULL OR activity.is_deleted = 0 )
 651             $whereClause";
 652
 653     $params = array(
 654       1 => array($surveyId, 'Integer'),
 655       2 => array($actTypeId, 'Integer'),
 656     );
 657
 658     if ($onlyCount) {
 659       $dbCount = CRM_Core_DAO::singleValueQuery($query, $params);
 660       return ($dbCount) ? $dbCount : 0;
 661     }
 662
 663     $activity = CRM_Core_DAO::executeQuery($query, $params);
 664
 665     while ($activity->fetch()) {
 666       $activities[$activity->id] = array(
 667         'id' => $activity->id,
 668         'voter_id' => $activity->voter_id,
 669         'voter_name' => $activity->voter_name,
 670         'status_id' => $activity->status_id,
 671         'interviewer_id' => $activity->interviewer_id,
 672         'result' => $activity->result,
 673         'activity_date_time' => $activity->activity_date_time,
 674       );
 675     }
 676
 677     return $activities;
 678   }
 679
 680   /**
 681    * Retrieve survey voter information.
 682    *
 683    * @param int $surveyId
 684    *   Survey id.
 685    * @param int $interviewerId
 686    *   Interviewer id.
 687    * @param array $statusIds
 688    *   Survey status ids.
 689    *
 690    * @return array
 691    *   Survey related contact ids.
 692    */
 693   public static function getSurveyVoterInfo($surveyId, $interviewerId = NULL, $statusIds = array()) {
 694     $voterIds = array();
 695     if (!$surveyId) {
 696       return $voterIds;
 697     }
 698
 699     $cacheKey = $surveyId;
 700     if ($interviewerId) {
 701       $cacheKey .= "_{$interviewerId}";
 702     }
 703     if (is_array($statusIds) && !empty($statusIds)) {
 704       $cacheKey = "{$cacheKey}_" . implode('_', $statusIds);
 705     }
 706
 707     static $contactIds = array();
 708     if (!isset($contactIds[$cacheKey])) {
 709       $activities = self::getSurveyActivities($surveyId, $interviewerId, $statusIds);
 710       foreach ($activities as $values) {
 711         $voterIds[$values['voter_id']] = $values;
 712       }
 713       $contactIds[$cacheKey] = $voterIds;
 714     }
 715
 716     return $contactIds[$cacheKey];
 717   }
 718
 719   /**
 720    * This function retrieve all option groups which are created as a result set.
 721    *
 722    * @param string $valueColumnName
 723    * @return array
 724    *   an array of option groups.
 725    */
 726   public static function getResultSets($valueColumnName = 'title') {
 727     $resultSets = array();
 728     $valueColumnName = CRM_Utils_Type::escape($valueColumnName, 'String');
 729
 730     $query = "SELECT id, {$valueColumnName} FROM civicrm_option_group WHERE name LIKE 'civicrm_survey_%' AND is_active=1";
 731     $dao = CRM_Core_DAO::executeQuery($query);
 732     while ($dao->fetch()) {
 733       $resultSets[$dao->id] = $dao->$valueColumnName;
 734     }
 735
 736     return $resultSets;
 737   }
 738
 739   /**
 740    * check survey activity.
 741    *
 742    * @param int $activityId
 743    *   Activity id.
 744    * @return bool
 745    */
 746   public static function isSurveyActivity($activityId) {
 747     $isSurveyActivity = FALSE;
 748     if (!$activityId) {
 749       return $isSurveyActivity;
 750     }
 751
 752     $activity = new CRM_Activity_DAO_Activity();
 753     $activity->id = $activityId;
 754     $activity->selectAdd('source_record_id, activity_type_id');
 755     if ($activity->find(TRUE) &&
 756       $activity->source_record_id
 757     ) {
 758       $surveyActTypes = self::getSurveyActivityType();
 759       if (array_key_exists($activity->activity_type_id, $surveyActTypes)) {
 760         $isSurveyActivity = TRUE;
 761       }
 762     }
 763
 764     return $isSurveyActivity;
 765   }
 766
 767   /**
 768    * This function retrive all response options of survey.
 769    *
 770    * @param int $surveyId
 771    *   Survey id.
 772    * @return array
 773    *   an array of option values
 774    */
 775   public static function getResponsesOptions($surveyId) {
 776     $responseOptions = array();
 777     if (!$surveyId) {
 778       return $responseOptions;
 779     }
 780
 781     $resultId = CRM_Core_DAO::getFieldValue('CRM_Campaign_DAO_Survey', $surveyId, 'result_id');
 782     if ($resultId) {
 783       $responseOptions = CRM_Core_OptionGroup::valuesByID($resultId);
 784     }
 785
 786     return $responseOptions;
 787   }
 788
 789   /**
 790    * This function return all voter links with respecting permissions.
 791    *
 792    * @param int $surveyId
 793    * @param bool $enclosedInUL
 794    * @param string $extraULName
 795    * @return array|string
 796    *   $url array of permissioned links
 797    */
 798   public static function buildPermissionLinks($surveyId, $enclosedInUL = FALSE, $extraULName = 'more') {
 799     $menuLinks = array();
 800     if (!$surveyId) {
 801       return $menuLinks;
 802     }
 803
 804     static $voterLinks = array();
 805     if (empty($voterLinks)) {
 806       $permissioned = FALSE;
 807       if (CRM_Core_Permission::check('manage campaign') ||
 808         CRM_Core_Permission::check('administer CiviCampaign')
 809       ) {
 810         $permissioned = TRUE;
 811       }
 812
 813       if ($permissioned || CRM_Core_Permission::check("reserve campaign contacts")) {
 814         $voterLinks['reserve'] = array(
 815           'name' => 'reserve',
 816           'url' => 'civicrm/survey/search',
 817           'qs' => 'sid=%%id%%&reset=1&op=reserve',
 818           'title' => ts('Reserve Respondents'),
 819         );
 820       }
 821       if ($permissioned || CRM_Core_Permission::check("interview campaign contacts")) {
 822         $voterLinks['release'] = array(
 823           'name' => 'interview',
 824           'url' => 'civicrm/survey/search',
 825           'qs' => 'sid=%%id%%&reset=1&op=interview&force=1',
 826           'title' => ts('Interview Respondents'),
 827         );
 828       }
 829       if ($permissioned || CRM_Core_Permission::check("release campaign contacts")) {
 830         $voterLinks['interview'] = array(
 831           'name' => 'release',
 832           'url' => 'civicrm/survey/search',
 833           'qs' => 'sid=%%id%%&reset=1&op=release&force=1',
 834           'title' => ts('Release Respondents'),
 835         );
 836       }
 837     }
 838
 839     if (CRM_Core_Permission::check('access CiviReport')) {
 840       $reportID = self::getReportID($surveyId);
 841       if ($reportID) {
 842         $voterLinks['report'] = array(
 843           'name' => 'report',
 844           'url' => "civicrm/report/instance/{$reportID}",
 845           'qs' => 'reset=1',
 846           'title' => ts('View Survey Report'),
 847         );
 848       }
 849     }
 850
 851     $ids = array('id' => $surveyId);
 852     foreach ($voterLinks as $link) {
 853       if (!empty($link['qs']) &&
 854         !CRM_Utils_System::isNull($link['qs'])
 855       ) {
 856         $urlPath = CRM_Utils_System::url(CRM_Core_Action::replace($link['url'], $ids),
 857           CRM_Core_Action::replace($link['qs'], $ids)
 858         );
 859         $menuLinks[] = sprintf('<a href="%s" class="action-item crm-hover-button" title="%s">%s</a>',
 860           $urlPath,
 861           CRM_Utils_Array::value('title', $link),
 862           $link['title']
 863         );
 864       }
 865     }
 866     if ($enclosedInUL) {
 867       $extraLinksName = strtolower($extraULName);
 868       $allLinks = '';
 869       CRM_Utils_String::append($allLinks, '</li><li>', $menuLinks);
 870       $allLinks = "$extraULName <ul id='panel_{$extraLinksName}_xx' class='panel'><li>{$allLinks}</li></ul>";
 871       $menuLinks = "<span class='btn-slide crm-hover-button' id={$extraLinksName}_xx>{$allLinks}</span>";
 872     }
 873
 874     return $menuLinks;
 875   }
 876
 877   /**
 878    * Retrieve survey associated profile id.
 879    *
 880    * @param int $surveyId
 881    *
 882    * @return mixed|null
 883    */
 884   public static function getSurveyProfileId($surveyId) {
 885     if (!$surveyId) {
 886       return NULL;
 887     }
 888
 889     static $ufIds = array();
 890     if (!array_key_exists($surveyId, $ufIds)) {
 891       //get the profile id.
 892       $ufJoinParams = array(
 893         'entity_id' => $surveyId,
 894         'entity_table' => 'civicrm_survey',
 895         'module' => 'CiviCampaign',
 896       );
 897
 898       list($first, $second) = CRM_Core_BAO_UFJoin::getUFGroupIds($ufJoinParams);
 899
 900       if ($first) {
 901         $ufIds[$surveyId] = array($first);
 902       }
 903       if ($second) {
 904         $ufIds[$surveyId][] = array_shift($second);
 905       }
 906     }
 907
 908     return CRM_Utils_Array::value($surveyId, $ufIds);
 909   }
 910
 911   /**
 912    * @param int $surveyId
 913    *
 914    * @return mixed
 915    */
 916   public Static function getReportID($surveyId) {
 917     static $reportIds = array();
 918
 919     if (!array_key_exists($surveyId, $reportIds)) {
 920       $query = "SELECT MAX(id) as id FROM civicrm_report_instance WHERE name = %1";
 921       $reportID = CRM_Core_DAO::singleValueQuery($query, array(1 => array("survey_{$surveyId}", 'String')));
 922       $reportIds[$surveyId] = $reportID;
 923     }
 924     return $reportIds[$surveyId];
 925   }
 926
 927   /**
 928    * Decides the contact type for given survey.
 929    *
 930    * @param int $surveyId
 931    *
 932    * @return null|string
 933    */
 934   public static function getSurveyContactType($surveyId) {
 935     $contactType = NULL;
 936
 937     //apply filter of profile type on search.
 938     $profileId = self::getSurveyProfileId($surveyId);
 939     if ($profileId) {
 940       $profileType = CRM_Core_BAO_UFField::getProfileType($profileId);
 941       if (in_array($profileType, CRM_Contact_BAO_ContactType::basicTypes())) {
 942         $contactType = $profileType;
 943       }
 944     }
 945
 946     return $contactType;
 947   }
 948
 949   /**
 950    * Get survey supportable profile types.
 951    */
 952   public static function surveyProfileTypes() {
 953     static $profileTypes;
 954
 955     if (!isset($profileTypes)) {
 956       $profileTypes = array_merge(array('Activity', 'Contact'), CRM_Contact_BAO_ContactType::basicTypes());
 957       $profileTypes = array_diff($profileTypes, array('Organization', 'Household'));
 958     }
 959
 960     return $profileTypes;
 961   }
 962
 963   /**
 964    * Get the valid survey response fields those.
 965    * are configured with profile and custom fields.
 966    *
 967    * @param int $surveyId
 968    *   Survey id.
 969    * @param int $surveyTypeId
 970    *   Survey activity type id.
 971    *
 972    * @return array
 973    *   an array of valid survey response fields.
 974    */
 975   public static function getSurveyResponseFields($surveyId, $surveyTypeId = NULL) {
 976     if (empty($surveyId)) {
 977       return array();
 978     }
 979
 980     static $responseFields;
 981     $cacheKey = "{$surveyId}_{$surveyTypeId}";
 982
 983     if (isset($responseFields[$cacheKey])) {
 984       return $responseFields[$cacheKey];
 985     }
 986
 987     $responseFields[$cacheKey] = array();
 988
 989     $profileId = self::getSurveyProfileId($surveyId);
 990
 991     if (!$profileId) {
 992       return $responseFields;
 993     }
 994
 995     if (!$surveyTypeId) {
 996       $surveyTypeId = CRM_Core_DAO::getFieldValue('CRM_Campaign_DAO_Survey', $surveyId, 'activity_type_id');
 997     }
 998
 999     $profileFields = CRM_Core_BAO_UFGroup::getFields($profileId,
1000       FALSE, CRM_Core_Action::VIEW, NULL, NULL, FALSE, NULL, FALSE, NULL, CRM_Core_Permission::CREATE, 'field_name', TRUE
1001     );
1002
1003     //don't load these fields in grid.
1004     $removeFields = array('File', 'RichTextEditor');
1005
1006     $supportableFieldTypes = self::surveyProfileTypes();
1007
1008     // get custom fields of type survey
1009     $customFields = CRM_Core_BAO_CustomField::getFields('Activity', FALSE, FALSE, $surveyTypeId);
1010
1011     foreach ($profileFields as $name => $field) {
1012       //get only contact and activity fields.
1013       //later stage we might going to consider contact type also.
1014       if (in_array($field['field_type'], $supportableFieldTypes)) {
1015         // we should allow all supported custom data for survey
1016         // In case of activity, allow normal activity and with subtype survey,
1017         // suppress custom data of other activity types
1018         if (CRM_Core_BAO_CustomField::getKeyID($name)) {
1019           if (!in_array($field['html_type'], $removeFields)) {
1020             if ($field['field_type'] != 'Activity') {
1021               $responseFields[$cacheKey][$name] = $field;
1022             }
1023             elseif (array_key_exists(CRM_Core_BAO_CustomField::getKeyID($name), $customFields)) {
1024               $responseFields[$cacheKey][$name] = $field;
1025             }
1026           }
1027         }
1028         else {
1029           $responseFields[$cacheKey][$name] = $field;
1030         }
1031       }
1032     }
1033
1034     return $responseFields[$cacheKey];
1035   }
1036
1037   /**
1038    * Get all interviewers of surveys.
1039    *
1040    * @return array
1041    *   an array of valid survey response fields.
1042    */
1043   public static function getInterviewers() {
1044     static $interviewers;
1045
1046     if (isset($interviewers)) {
1047       return $interviewers;
1048     }
1049
1050     $whereClause = NULL;
1051     $activityTypes = self::getSurveyActivityType();
1052     if (!empty($activityTypes)) {
1053       $whereClause = ' WHERE survey.activity_type_id IN ( ' . implode(' , ', array_keys($activityTypes)) . ' )';
1054     }
1055
1056     $interviewers = array();
1057     $activityContacts = CRM_Activity_BAO_ActivityContact::buildOptions('record_type_id', 'validate');
1058     $assigneeID = CRM_Utils_Array::key('Activity Assignees', $activityContacts);
1059
1060     $query = "
1061     SELECT  contact.id as id,
1062             contact.sort_name as sort_name
1063       FROM  civicrm_contact contact
1064 INNER JOIN civicrm_activity_contact assignment ON ( assignment.contact_id = contact.id AND record_type_id = $assigneeID )
1065 INNER JOIN  civicrm_activity activity ON ( activity.id = assignment.activity_id )
1066 INNER JOIN  civicrm_survey survey ON ( activity.source_record_id = survey.id )
1067             {$whereClause}";
1068
1069     $interviewer = CRM_Core_DAO::executeQuery($query);
1070     while ($interviewer->fetch()) {
1071       $interviewers[$interviewer->id] = $interviewer->sort_name;
1072     }
1073
1074     return $interviewers;
1075   }
1076
1077   /**
1078    * Check and update the survey respondents.
1079    *
1080    * @param array $params
1081    *
1082    * @return array
1083    *   success message
1084    */
1085   public static function releaseRespondent($params) {
1086     $activityStatus = CRM_Core_PseudoConstant::activityStatus('name');
1087     $reserveStatusId = array_search('Scheduled', $activityStatus);
1088     $surveyActivityTypes = CRM_Campaign_BAO_Survey::getSurveyActivityType();
1089     if (!empty($surveyActivityTypes) && is_array($surveyActivityTypes)) {
1090       $surveyActivityTypesIds = array_keys($surveyActivityTypes);
1091     }
1092
1093     //retrieve all survey activities related to reserve action.
1094     $releasedCount = 0;
1095     if ($reserveStatusId && !empty($surveyActivityTypesIds)) {
1096       $query = '
1097     SELECT  activity.id as id,
1098             activity.activity_date_time as activity_date_time,
1099             survey.id as surveyId,
1100             survey.release_frequency as release_frequency
1101       FROM  civicrm_activity activity
1102 INNER JOIN  civicrm_survey survey ON ( survey.id = activity.source_record_id )
1103      WHERE  activity.is_deleted = 0
1104        AND  activity.status_id = %1
1105        AND  activity.activity_type_id IN ( ' . implode(', ', $surveyActivityTypesIds) . ' )';
1106       $activity = CRM_Core_DAO::executeQuery($query, array(1 => array($reserveStatusId, 'Positive')));
1107       $releasedIds = array();
1108       while ($activity->fetch()) {
1109         if (!$activity->release_frequency) {
1110           continue;
1111         }
1112         $reservedSeconds = CRM_Utils_Date::unixTime($activity->activity_date_time);
1113         $releasedSeconds = $activity->release_frequency * 24 * 3600;
1114         $totalReservedSeconds = $reservedSeconds + $releasedSeconds;
1115         if ($totalReservedSeconds < time()) {
1116           $releasedIds[$activity->id] = $activity->id;
1117         }
1118       }
1119
1120       //released respondent.
1121       if (!empty($releasedIds)) {
1122         $query = '
1123 UPDATE  civicrm_activity
1124    SET  is_deleted = 1
1125  WHERE  id IN ( ' . implode(', ', $releasedIds) . ' )';
1126         CRM_Core_DAO::executeQuery($query);
1127         $releasedCount = count($releasedIds);
1128       }
1129     }
1130
1131     $rtnMsg = array(
1132       'is_error' => 0,
1133       'messages' => "Number of respondents released = {$releasedCount}",
1134     );
1135
1136     return $rtnMsg;
1137   }
1138
1139   /**
1140    * Get options for a given field.
1141    * @see CRM_Core_DAO::buildOptions
1142    *
1143    * @param string $fieldName
1144    * @param string $context : @see CRM_Core_DAO::buildOptionsContext
1145    * @param array $props : whatever is known about this dao object
1146    *
1147    * @return array|bool
1148    */
1149   public static function buildOptions($fieldName, $context = NULL, $props = array()) {
1150     $params = array();
1151     // Special logic for fields whose options depend on context or properties
1152     switch ($fieldName) {
1153       case 'activity_type_id':
1154         $campaignCompId = CRM_Core_Component::getComponentID('CiviCampaign');
1155         if ($campaignCompId) {
1156           $params['condition'] = array("component_id={$campaignCompId}");
1157         }
1158         break;
1159     }
1160     return CRM_Core_PseudoConstant::get(__CLASS__, $fieldName, $params, $context);
1161   }
1162
1163 }