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