CRM/Campaign/BAO/Survey.php

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