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