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