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