CRM/Case/BAO/Case.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 4.7                                                |
   5  +--------------------------------------------------------------------+
   6  | Copyright CiviCRM LLC (c) 2004-2015                                |
   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-2015
  32  * $Id$
  33  *
  34  */
  35
  36 /**
  37  * This class contains the functions for Case Management.
  38  *
  39  */
  40 class CRM_Case_BAO_Case extends CRM_Case_DAO_Case {
  41
  42   /**
  43    * Static field for all the case information that we can potentially export.
  44    *
  45    * @var array
  46    */
  47   static $_exportableFields = NULL;
  48
  49   /**
  50    * Class constructor.
  51    */
  52   public function __construct() {
  53     parent::__construct();
  54   }
  55
  56   /**
  57    * Is CiviCase enabled?
  58    *
  59    * @return bool
  60    */
  61   public static function enabled() {
  62     $config = CRM_Core_Config::singleton();
  63     return in_array('CiviCase', $config->enableComponents);
  64   }
  65
  66   /**
  67    * Create a case object.
  68    *
  69    * The function extracts all the params it needs to initialize the create a
  70    * case object. the params array could contain additional unused name/value
  71    * pairs
  72    *
  73    * @param array $params
  74    *   (reference ) an assoc array of name/value pairs.
  75    *
  76    * @return CRM_Case_BAO_Case
  77    */
  78   public static function add(&$params) {
  79     $caseDAO = new CRM_Case_DAO_Case();
  80     $caseDAO->copyValues($params);
  81     return $caseDAO->save();
  82   }
  83
  84   /**
  85    * Given the list of params in the params array, fetch the object
  86    * and store the values in the values array.
  87    *
  88    * @param array $params
  89    *   Input parameters to find object.
  90    * @param array $values
  91    *   Output values of the object.
  92    * @param array $ids
  93    *   The array that holds all the db ids.
  94    *
  95    * @return CRM_Case_BAO_Case|null
  96    *   The found object or null
  97    */
  98   public static function &getValues(&$params, &$values, &$ids) {
  99     $case = new CRM_Case_BAO_Case();
 100
 101     $case->copyValues($params);
 102
 103     if ($case->find(TRUE)) {
 104       $ids['case'] = $case->id;
 105       CRM_Core_DAO::storeValues($case, $values);
 106       return $case;
 107     }
 108     return NULL;
 109   }
 110
 111   /**
 112    * Takes an associative array and creates a case object.
 113    *
 114    * @param array $params
 115    *   (reference) an assoc array of name/value pairs.
 116    *
 117    * @return CRM_Case_BAO_Case
 118    */
 119   public static function &create(&$params) {
 120     $transaction = new CRM_Core_Transaction();
 121
 122     if (!empty($params['id'])) {
 123       CRM_Utils_Hook::pre('edit', 'Case', $params['id'], $params);
 124     }
 125     else {
 126       CRM_Utils_Hook::pre('create', 'Case', NULL, $params);
 127     }
 128
 129     $case = self::add($params);
 130
 131     if (!empty($params['custom']) &&
 132       is_array($params['custom'])
 133     ) {
 134       CRM_Core_BAO_CustomValueTable::store($params['custom'], 'civicrm_case', $case->id);
 135     }
 136
 137     if (is_a($case, 'CRM_Core_Error')) {
 138       $transaction->rollback();
 139       return $case;
 140     }
 141
 142     if (!empty($params['id'])) {
 143       CRM_Utils_Hook::post('edit', 'Case', $case->id, $case);
 144     }
 145     else {
 146       CRM_Utils_Hook::post('create', 'Case', $case->id, $case);
 147     }
 148     $transaction->commit();
 149
 150     //we are not creating log for case
 151     //since case log can be tracked using log for activity.
 152     return $case;
 153   }
 154
 155   /**
 156    * Create case contact record.
 157    *
 158    * @param array $params
 159    *   case_id, contact_id
 160    *
 161    * @return object
 162    */
 163   public static function addCaseToContact($params) {
 164     $caseContact = new CRM_Case_DAO_CaseContact();
 165     $caseContact->case_id = $params['case_id'];
 166     $caseContact->contact_id = $params['contact_id'];
 167     $caseContact->find(TRUE);
 168     $caseContact->save();
 169
 170     // add to recently viewed
 171     $caseType = CRM_Case_BAO_Case::getCaseType($caseContact->case_id);
 172     $url = CRM_Utils_System::url('civicrm/contact/view/case',
 173       "action=view&reset=1&id={$caseContact->case_id}&cid={$caseContact->contact_id}&context=home"
 174     );
 175
 176     $title = CRM_Contact_BAO_Contact::displayName($caseContact->contact_id) . ' - ' . $caseType;
 177
 178     $recentOther = array();
 179     if (CRM_Core_Permission::checkActionPermission('CiviCase', CRM_Core_Action::DELETE)) {
 180       $recentOther['deleteUrl'] = CRM_Utils_System::url('civicrm/contact/view/case',
 181         "action=delete&reset=1&id={$caseContact->case_id}&cid={$caseContact->contact_id}&context=home"
 182       );
 183     }
 184
 185     // add the recently created case
 186     CRM_Utils_Recent::add($title,
 187       $url,
 188       $caseContact->case_id,
 189       'Case',
 190       $params['contact_id'],
 191       NULL,
 192       $recentOther
 193     );
 194
 195     return $caseContact;
 196   }
 197
 198   /**
 199    * Delete case contact record.
 200    *
 201    * @param int $caseID
 202    */
 203   public static function deleteCaseContact($caseID) {
 204     $caseContact = new CRM_Case_DAO_CaseContact();
 205     $caseContact->case_id = $caseID;
 206     $caseContact->delete();
 207
 208     // delete the recently created Case
 209     $caseRecent = array(
 210       'id' => $caseID,
 211       'type' => 'Case',
 212     );
 213     CRM_Utils_Recent::del($caseRecent);
 214   }
 215
 216   /**
 217    * Convert associative array names to values and vice-versa.
 218    *
 219    * This function is used by both the web form layer and the api. Note that
 220    * the api needs the name => value conversion, also the view layer typically
 221    * requires value => name conversion
 222    *
 223    * @param array $defaults
 224    * @param string $property
 225    * @param array $lookup
 226    * @param bool $reverse
 227    *
 228    * @return bool
 229    */
 230   public static function lookupValue(&$defaults, $property, &$lookup, $reverse) {
 231     $id = $property . '_id';
 232
 233     $src = $reverse ? $property : $id;
 234     $dst = $reverse ? $id : $property;
 235
 236     if (!array_key_exists($src, $defaults)) {
 237       return FALSE;
 238     }
 239
 240     $look = $reverse ? array_flip($lookup) : $lookup;
 241
 242     if (is_array($look)) {
 243       if (!array_key_exists($defaults[$src], $look)) {
 244         return FALSE;
 245       }
 246     }
 247     $defaults[$dst] = $look[$defaults[$src]];
 248     return TRUE;
 249   }
 250
 251   /**
 252    * Retrieve DB object based on input parameters.
 253    *
 254    * It also stores all the retrieved values in the default array.
 255    *
 256    * @param array $params
 257    *   (reference ) an assoc array of name/value pairs.
 258    * @param array $defaults
 259    *   (reference ) an assoc array to hold the name / value pairs.
 260    *                        in a hierarchical manner
 261    * @param array $ids
 262    *   (reference) the array that holds all the db ids.
 263    *
 264    * @return CRM_Case_BAO_Case
 265    */
 266   public static function retrieve(&$params, &$defaults, &$ids) {
 267     $case = CRM_Case_BAO_Case::getValues($params, $defaults, $ids);
 268     return $case;
 269   }
 270
 271   /**
 272    * Process case activity add/delete
 273    * takes an associative array and
 274    *
 275    * @param array $params
 276    *   (reference ) an assoc array of name/value pairs.
 277    *
 278    */
 279   public static function processCaseActivity(&$params) {
 280     $caseActivityDAO = new CRM_Case_DAO_CaseActivity();
 281     $caseActivityDAO->activity_id = $params['activity_id'];
 282     $caseActivityDAO->case_id = $params['case_id'];
 283
 284     $caseActivityDAO->find(TRUE);
 285     $caseActivityDAO->save();
 286   }
 287
 288   /**
 289    * Get the case subject for Activity.
 290    *
 291    * @param int $activityId
 292    *   Activity id.
 293    *
 294    * @return string|null
 295    */
 296   public static function getCaseSubject($activityId) {
 297     $caseActivity = new CRM_Case_DAO_CaseActivity();
 298     $caseActivity->activity_id = $activityId;
 299     if ($caseActivity->find(TRUE)) {
 300       return CRM_Core_DAO::getFieldValue('CRM_Case_BAO_Case', $caseActivity->case_id, 'subject');
 301     }
 302     return NULL;
 303   }
 304
 305   /**
 306    * Get the case type.
 307    *
 308    * @param int $caseId
 309    * @param string $colName
 310    *
 311    * @return string
 312    *   case type
 313    */
 314   public static function getCaseType($caseId, $colName = 'title') {
 315     $query = "
 316 SELECT  civicrm_case_type.{$colName} FROM civicrm_case
 317 LEFT JOIN civicrm_case_type ON
 318   civicrm_case.case_type_id = civicrm_case_type.id
 319 WHERE civicrm_case.id = %1";
 320
 321     $queryParams = array(1 => array($caseId, 'Integer'));
 322
 323     return CRM_Core_DAO::singleValueQuery($query, $queryParams);
 324   }
 325
 326   /**
 327    * Delete the record that are associated with this case.
 328    * record are deleted from case
 329    *
 330    * @param int $caseId
 331    *   Id of the case to delete.
 332    *
 333    * @param bool $moveToTrash
 334    *
 335    * @return bool
 336    *   is successful
 337    */
 338   public static function deleteCase($caseId, $moveToTrash = FALSE) {
 339     CRM_Utils_Hook::pre('delete', 'Case', $caseId, CRM_Core_DAO::$_nullArray);
 340
 341     //delete activities
 342     $activities = self::getCaseActivityDates($caseId);
 343     if ($activities) {
 344       foreach ($activities as $value) {
 345         CRM_Activity_BAO_Activity::deleteActivity($value, $moveToTrash);
 346       }
 347     }
 348
 349     if (!$moveToTrash) {
 350       $transaction = new CRM_Core_Transaction();
 351     }
 352     $case = new CRM_Case_DAO_Case();
 353     $case->id = $caseId;
 354     if (!$moveToTrash) {
 355       $result = $case->delete();
 356       $transaction->commit();
 357     }
 358     else {
 359       $result = $case->is_deleted = 1;
 360       $case->save();
 361     }
 362
 363     if ($result) {
 364       // CRM-7364, disable relationships
 365       self::enableDisableCaseRelationships($caseId, FALSE);
 366
 367       CRM_Utils_Hook::post('delete', 'Case', $caseId, $case);
 368
 369       // remove case from recent items.
 370       $caseRecent = array(
 371         'id' => $caseId,
 372         'type' => 'Case',
 373       );
 374       CRM_Utils_Recent::del($caseRecent);
 375       return TRUE;
 376     }
 377
 378     return FALSE;
 379   }
 380
 381   /**
 382    * Enable disable case related relationships.
 383    *
 384    * @param int $caseId
 385    *   Case id.
 386    * @param bool $enable
 387    *   Action.
 388    *
 389    * @return void
 390    */
 391   public static function enableDisableCaseRelationships($caseId, $enable) {
 392     $contactIds = self::retrieveContactIdsByCaseId($caseId);
 393     if (!empty($contactIds)) {
 394       foreach ($contactIds as $cid) {
 395         $roles = self::getCaseRoles($cid, $caseId);
 396         if (!empty($roles)) {
 397           $relationshipIds = implode(',', array_keys($roles));
 398           $enable = (int) $enable;
 399           $query = "UPDATE civicrm_relationship SET is_active = {$enable}
 400                         WHERE id IN ( {$relationshipIds} )";
 401           CRM_Core_DAO::executeQuery($query);
 402         }
 403       }
 404     }
 405   }
 406
 407   /**
 408    * Delete the activities related to case.
 409    *
 410    * @param int $activityId
 411    *   Id of the activity.
 412    *
 413    * @return void
 414    */
 415   public static function deleteCaseActivity($activityId) {
 416     $case = new CRM_Case_DAO_CaseActivity();
 417     $case->activity_id = $activityId;
 418     $case->delete();
 419   }
 420
 421   /**
 422    * Retrieve contact_id by case_id.
 423    *
 424    * @param int $caseId
 425    *   ID of the case.
 426    *
 427    * @param int $contactID
 428    *
 429    * @return array
 430    */
 431   public static function retrieveContactIdsByCaseId($caseId, $contactID = NULL) {
 432     $caseContact = new CRM_Case_DAO_CaseContact();
 433     $caseContact->case_id = $caseId;
 434     $caseContact->find();
 435     $contactArray = array();
 436     $count = 1;
 437     while ($caseContact->fetch()) {
 438       if ($contactID != $caseContact->contact_id) {
 439         $contactArray[$count] = $caseContact->contact_id;
 440         $count++;
 441       }
 442     }
 443
 444     return $contactArray;
 445   }
 446
 447   /**
 448    * Look up a case using an activity ID.
 449    *
 450    * @param int $activityId
 451    *
 452    * @return int, case ID
 453    */
 454   public static function getCaseIdByActivityId($activityId) {
 455     $originalId = CRM_Core_DAO::singleValueQuery(
 456       'SELECT original_id FROM civicrm_activity WHERE id = %1',
 457       array('1' => array($activityId, 'Integer'))
 458     );
 459     $caseId = CRM_Core_DAO::singleValueQuery(
 460       'SELECT case_id FROM civicrm_case_activity WHERE activity_id in (%1,%2)',
 461       array(
 462         '1' => array($activityId, 'Integer'),
 463         '2' => array($originalId ? $originalId : $activityId, 'Integer'),
 464       )
 465     );
 466     return $caseId;
 467   }
 468
 469   /**
 470    * Retrieve contact names by caseId.
 471    *
 472    * @param int $caseId
 473    *   ID of the case.
 474    *
 475    * @return array
 476    */
 477   public static function getContactNames($caseId) {
 478     $contactNames = array();
 479     if (!$caseId) {
 480       return $contactNames;
 481     }
 482
 483     $query = "
 484     SELECT  contact_a.sort_name name,
 485             contact_a.display_name as display_name,
 486             contact_a.id cid,
 487             contact_a.birth_date as birth_date,
 488             ce.email as email,
 489             cp.phone as phone
 490       FROM  civicrm_contact contact_a
 491  LEFT JOIN  civicrm_case_contact ON civicrm_case_contact.contact_id = contact_a.id
 492  LEFT JOIN  civicrm_email ce ON ( ce.contact_id = contact_a.id AND ce.is_primary = 1)
 493  LEFT JOIN  civicrm_phone cp ON ( cp.contact_id = contact_a.id AND cp.is_primary = 1)
 494      WHERE  civicrm_case_contact.case_id = %1";
 495
 496     $dao = CRM_Core_DAO::executeQuery($query,
 497       array(1 => array($caseId, 'Integer'))
 498     );
 499     while ($dao->fetch()) {
 500       $contactNames[$dao->cid]['contact_id'] = $dao->cid;
 501       $contactNames[$dao->cid]['sort_name'] = $dao->name;
 502       $contactNames[$dao->cid]['display_name'] = $dao->display_name;
 503       $contactNames[$dao->cid]['email'] = $dao->email;
 504       $contactNames[$dao->cid]['phone'] = $dao->phone;
 505       $contactNames[$dao->cid]['birth_date'] = $dao->birth_date;
 506       $contactNames[$dao->cid]['role'] = ts('Client');
 507     }
 508
 509     return $contactNames;
 510   }
 511
 512   /**
 513    * Retrieve case_id by contact_id.
 514    *
 515    * @param int $contactID
 516    * @param bool $includeDeleted
 517    *   Include the deleted cases in result.
 518    * @param null $caseType
 519    *
 520    * @return array
 521    */
 522   public static function retrieveCaseIdsByContactId($contactID, $includeDeleted = FALSE, $caseType = NULL) {
 523     $query = "
 524 SELECT ca.id as id
 525 FROM civicrm_case_contact cc
 526 INNER JOIN civicrm_case ca ON cc.case_id = ca.id
 527 ";
 528     if (isset($caseType)) {
 529       $query .=
 530         "INNER JOIN civicrm_case_type ON civicrm_case_type.id = ca.case_type_id
 531 WHERE cc.contact_id = %1 AND civicrm_case_type.name = '{$caseType}'";
 532     }
 533     if (!isset($caseType)) {
 534       $query .= "WHERE cc.contact_id = %1";
 535     }
 536     if (!$includeDeleted) {
 537       $query .= " AND ca.is_deleted = 0";
 538     }
 539
 540     $params = array(1 => array($contactID, 'Integer'));
 541     $dao = CRM_Core_DAO::executeQuery($query, $params);
 542
 543     $caseArray = array();
 544     while ($dao->fetch()) {
 545       $caseArray[] = $dao->id;
 546     }
 547
 548     $dao->free();
 549     return $caseArray;
 550   }
 551
 552   /**
 553    * @param string $type
 554    * @param int $userID
 555    * @param null $condition
 556    * @param int $isDeleted
 557    *
 558    * @return string
 559    */
 560   public static function getCaseActivityQuery($type = 'upcoming', $userID = NULL, $condition = NULL, $isDeleted = 0) {
 561     if (!$userID) {
 562       $session = CRM_Core_Session::singleton();
 563       $userID = $session->get('userID');
 564     }
 565
 566     $actStatus = array_flip(CRM_Core_PseudoConstant::activityStatus('name'));
 567     $scheduledStatusId = $actStatus['Scheduled'];
 568
 569     $query = "SELECT
 570 civicrm_case.id as case_id,
 571 civicrm_case.subject as case_subject,
 572 civicrm_contact.id as contact_id,
 573 civicrm_contact.sort_name as sort_name,
 574 civicrm_phone.phone as phone,
 575 civicrm_contact.contact_type as contact_type,
 576 civicrm_contact.contact_sub_type as contact_sub_type,
 577 t_act.activity_type_id,
 578 c_type.title as case_type,
 579 civicrm_case.case_type_id as case_type_id,
 580 cov_status.label as case_status,
 581 cov_status.label as case_status_name,
 582 t_act.status_id,
 583 civicrm_case.start_date as case_start_date,
 584 case_relation_type.label_b_a as case_role, ";
 585
 586     if ($type == 'upcoming') {
 587       $query .= "
 588 t_act.desired_date as case_scheduled_activity_date,
 589 t_act.id as case_scheduled_activity_id,
 590 t_act.act_type_name as case_scheduled_activity_type_name,
 591 t_act.act_type AS case_scheduled_activity_type ";
 592     }
 593     elseif ($type == 'recent') {
 594       $query .= "
 595 t_act.desired_date as case_recent_activity_date,
 596 t_act.id as case_recent_activity_id,
 597 t_act.act_type_name as case_recent_activity_type_name,
 598 t_act.act_type AS case_recent_activity_type ";
 599     }
 600     elseif ($type == 'any') {
 601       $query .= "
 602 t_act.desired_date as case_activity_date,
 603 t_act.id as case_activity_id,
 604 t_act.act_type_name as case_activity_type_name,
 605 t_act.act_type AS case_activity_type ";
 606     }
 607
 608     $query .= " FROM civicrm_case
 609                   INNER JOIN civicrm_case_contact ON civicrm_case.id = civicrm_case_contact.case_id
 610                   INNER JOIN civicrm_contact ON civicrm_case_contact.contact_id = civicrm_contact.id ";
 611
 612     if ($type == 'upcoming') {
 613       // This gets the earliest activity per case that's scheduled within 14 days from now.
 614       // Note we have an inner select to get the min activity id in order to remove duplicates in case there are two with the same datetime.
 615       // In this case we don't really care which one, so min(id) works.
 616       // optimized in CRM-11837
 617       $query .= " INNER JOIN
 618 (
 619   SELECT case_id, act.id, activity_date_time AS desired_date, activity_type_id, status_id, aov.name AS act_type_name, aov.label AS act_type
 620   FROM (
 621     SELECT *
 622     FROM (
 623       SELECT *
 624       FROM civicrm_view_case_activity_upcoming
 625       ORDER BY activity_date_time ASC, id ASC
 626       ) AS upcomingOrdered
 627     GROUP BY case_id
 628     ) AS act
 629   LEFT JOIN civicrm_option_group aog ON aog.name='activity_type'
 630   LEFT JOIN civicrm_option_value aov ON ( aov.option_group_id = aog.id AND aov.value = act.activity_type_id )
 631 ) AS t_act
 632 ";
 633     }
 634     elseif ($type == 'recent') {
 635       // Similarly, the most recent activity in the past 14 days, and exclude scheduled.
 636       //improve query performance - CRM-10598
 637       $query .= " INNER JOIN
 638 (
 639   SELECT case_id, act.id, activity_date_time AS desired_date, activity_type_id, status_id, aov.name AS act_type_name, aov.label AS act_type
 640   FROM (
 641     SELECT *
 642     FROM (
 643       SELECT *
 644       FROM civicrm_view_case_activity_recent
 645       ORDER BY activity_date_time DESC, id ASC
 646       ) AS recentOrdered
 647     GROUP BY case_id
 648     ) AS act
 649 LEFT JOIN civicrm_option_group aog ON aog.name='activity_type'
 650   LEFT JOIN civicrm_option_value aov ON ( aov.option_group_id = aog.id AND aov.value = act.activity_type_id )
 651 ) AS t_act ";
 652     }
 653     elseif ($type == 'any') {
 654       $query .= " LEFT JOIN
 655 (
 656   SELECT ca4.case_id, act4.id AS id, act4.activity_date_time AS desired_date, act4.activity_type_id, act4.status_id, aov.name AS act_type_name, aov.label AS act_type
 657   FROM civicrm_activity act4
 658   LEFT JOIN civicrm_case_activity ca4
 659     ON ca4.activity_id = act4.id
 660     AND act4.is_current_revision = 1
 661   LEFT JOIN civicrm_option_group aog
 662     ON aog.name='activity_type'
 663   LEFT JOIN civicrm_option_value aov
 664     ON aov.option_group_id = aog.id
 665     AND aov.value = act4.activity_type_id
 666 ) AS t_act";
 667     }
 668
 669     $query .= "
 670         ON t_act.case_id = civicrm_case.id
 671  LEFT JOIN civicrm_phone ON (civicrm_phone.contact_id = civicrm_contact.id AND civicrm_phone.is_primary=1)
 672  LEFT JOIN civicrm_relationship case_relationship
 673  ON ( case_relationship.contact_id_a = civicrm_case_contact.contact_id AND case_relationship.contact_id_b = {$userID}
 674       AND case_relationship.case_id = civicrm_case.id )
 675
 676  LEFT JOIN civicrm_relationship_type case_relation_type
 677  ON ( case_relation_type.id = case_relationship.relationship_type_id
 678       AND case_relation_type.id = case_relationship.relationship_type_id )
 679
 680  LEFT JOIN civicrm_case_type c_type
 681  ON civicrm_case.case_type_id = c_type.id
 682
 683  LEFT JOIN civicrm_option_group cog_status
 684  ON cog_status.name = 'case_status'
 685
 686  LEFT JOIN civicrm_option_value cov_status
 687  ON ( civicrm_case.status_id = cov_status.value
 688       AND cog_status.id = cov_status.option_group_id )
 689 ";
 690
 691     if ($condition) {
 692       // CRM-8749 backwards compatibility - callers of this function expect to start $condition with "AND"
 693       $query .= " WHERE (1) $condition ";
 694     }
 695
 696     if ($type == 'upcoming') {
 697       $query .= " ORDER BY case_scheduled_activity_date ASC ";
 698     }
 699     elseif ($type == 'recent') {
 700       $query .= " ORDER BY case_recent_activity_date ASC ";
 701     }
 702     elseif ($type == 'any') {
 703       $query .= " ORDER BY case_activity_date ASC ";
 704     }
 705
 706     return $query;
 707   }
 708
 709   /**
 710    * Retrieve cases related to particular contact or whole contact used in Dashboard and Tab.
 711    *
 712    * @param bool $allCases
 713    *
 714    * @param int $userID
 715    *
 716    * @param string $type
 717    *   /upcoming,recent,all/.
 718    *
 719    * @param string $context
 720    *
 721    * @return array
 722    *   Array of Cases
 723    */
 724   public static function getCases($allCases = TRUE, $userID = NULL, $type = 'upcoming', $context = 'dashboard') {
 725     $condition = NULL;
 726     $casesList = array();
 727
 728     //validate access for own cases.
 729     if (!self::accessCiviCase()) {
 730       return $casesList;
 731     }
 732
 733     if (!$userID) {
 734       $session = CRM_Core_Session::singleton();
 735       $userID = $session->get('userID');
 736     }
 737
 738     //validate access for all cases.
 739     if ($allCases && !CRM_Core_Permission::check('access all cases and activities')) {
 740       $allCases = FALSE;
 741     }
 742
 743     $condition = " AND civicrm_case.is_deleted = 0 AND civicrm_contact.is_deleted <> 1";
 744
 745     if (!$allCases) {
 746       $condition .= " AND case_relationship.contact_id_b = {$userID} ";
 747     }
 748     if ($type == 'upcoming' || $type == 'any') {
 749       $closedId = CRM_Core_OptionGroup::getValue('case_status', 'Closed', 'name');
 750       $condition .= "
 751 AND civicrm_case.status_id != $closedId";
 752     }
 753
 754     $query = self::getCaseActivityQuery($type, $userID, $condition);
 755
 756     $queryParams = array();
 757     $result = CRM_Core_DAO::executeQuery($query,
 758       $queryParams
 759     );
 760
 761     $caseStatus = CRM_Core_OptionGroup::values('case_status', FALSE, FALSE, FALSE, " AND v.name = 'Urgent' ");
 762
 763     $resultFields = array(
 764       'contact_id',
 765       'contact_type',
 766       'sort_name',
 767       'phone',
 768       'case_id',
 769       'case_subject',
 770       'case_type',
 771       'case_type_id',
 772       'status_id',
 773       'case_status',
 774       'case_status_name',
 775       'activity_type_id',
 776       'case_start_date',
 777       'case_role',
 778     );
 779
 780     if ($type == 'upcoming') {
 781       $resultFields[] = 'case_scheduled_activity_date';
 782       $resultFields[] = 'case_scheduled_activity_type_name';
 783       $resultFields[] = 'case_scheduled_activity_type';
 784       $resultFields[] = 'case_scheduled_activity_id';
 785     }
 786     elseif ($type == 'recent') {
 787       $resultFields[] = 'case_recent_activity_date';
 788       $resultFields[] = 'case_recent_activity_type_name';
 789       $resultFields[] = 'case_recent_activity_type';
 790       $resultFields[] = 'case_recent_activity_id';
 791     }
 792     elseif ($type == 'any') {
 793       $resultFields[] = 'case_activity_date';
 794       $resultFields[] = 'case_activity_type_name';
 795       $resultFields[] = 'case_activity_type';
 796       $resultFields[] = 'case_activity_id';
 797     }
 798
 799     // we're going to use the usual actions, so doesn't make sense to duplicate definitions
 800     $actions = CRM_Case_Selector_Search::links();
 801
 802     // check is the user has view/edit signer permission
 803     $permissions = array(CRM_Core_Permission::VIEW);
 804     if (CRM_Core_Permission::check('access all cases and activities') ||
 805       (!$allCases && CRM_Core_Permission::check('access my cases and activities'))
 806     ) {
 807       $permissions[] = CRM_Core_Permission::EDIT;
 808     }
 809     if (CRM_Core_Permission::check('delete in CiviCase')) {
 810       $permissions[] = CRM_Core_Permission::DELETE;
 811     }
 812     $mask = CRM_Core_Action::mask($permissions);
 813
 814     while ($result->fetch()) {
 815       foreach ($resultFields as $donCare => $field) {
 816         $casesList[$result->case_id][$field] = $result->$field;
 817         if ($field == 'contact_type') {
 818           $casesList[$result->case_id]['contact_type_icon'] = CRM_Contact_BAO_Contact_Utils::getImage($result->contact_sub_type ? $result->contact_sub_type : $result->contact_type
 819           );
 820           $casesList[$result->case_id]['action'] = CRM_Core_Action::formLink($actions['primaryActions'], $mask,
 821             array(
 822               'id' => $result->case_id,
 823               'cid' => $result->contact_id,
 824               'cxt' => $context,
 825             ),
 826             ts('more'),
 827             FALSE,
 828             'case.actions.primary',
 829             'Case',
 830             $result->case_id
 831           );
 832           $casesList[$result->case_id]['moreActions'] = CRM_Core_Action::formLink($actions['moreActions'],
 833             $mask,
 834             array(
 835               'id' => $result->case_id,
 836               'cid' => $result->contact_id,
 837               'cxt' => $context,
 838             ),
 839             ts('more'),
 840             TRUE,
 841             'case.actions.more',
 842             'Case',
 843             $result->case_id
 844           );
 845         }
 846         elseif ($field == 'case_status') {
 847           if (in_array($result->$field, $caseStatus)) {
 848             $casesList[$result->case_id]['class'] = "status-urgent";
 849           }
 850           else {
 851             $casesList[$result->case_id]['class'] = "status-normal";
 852           }
 853         }
 854       }
 855       //CRM-4510.
 856       $caseTypes = CRM_Case_PseudoConstant::caseType('name');
 857       $caseManagerContact = self::getCaseManagerContact($caseTypes[$result->case_type_id], $result->case_id);
 858       if (!empty($caseManagerContact)) {
 859         $casesList[$result->case_id]['casemanager_id'] = CRM_Utils_Array::value('casemanager_id', $caseManagerContact);
 860         $casesList[$result->case_id]['casemanager'] = CRM_Utils_Array::value('casemanager', $caseManagerContact);
 861       }
 862
 863       //do check user permissions for edit/view activity.
 864       if (($actId = CRM_Utils_Array::value('case_scheduled_activity_id', $casesList[$result->case_id])) ||
 865         ($actId = CRM_Utils_Array::value('case_recent_activity_id', $casesList[$result->case_id]))
 866       ) {
 867         $casesList[$result->case_id]["case_{$type}_activity_editable"] = self::checkPermission($actId,
 868           'edit',
 869           $casesList[$result->case_id]['activity_type_id'], $userID
 870         );
 871         $casesList[$result->case_id]["case_{$type}_activity_viewable"] = self::checkPermission($actId,
 872           'view',
 873           $casesList[$result->case_id]['activity_type_id'], $userID
 874         );
 875       }
 876     }
 877
 878     return $casesList;
 879   }
 880
 881   /**
 882    * Get the summary of cases counts by type and status.
 883    *
 884    * @param bool $allCases
 885    * @param int $userID
 886    * @return array
 887    */
 888   public static function getCasesSummary($allCases = TRUE, $userID) {
 889     $caseSummary = array();
 890
 891     //validate access for civicase.
 892     if (!self::accessCiviCase()) {
 893       return $caseSummary;
 894     }
 895
 896     //validate access for all cases.
 897     if ($allCases && !CRM_Core_Permission::check('access all cases and activities')) {
 898       $allCases = FALSE;
 899     }
 900
 901     $caseTypes = CRM_Case_PseudoConstant::caseType();
 902     $caseStatuses = CRM_Case_PseudoConstant::caseStatus();
 903     $caseTypes = array_flip($caseTypes);
 904
 905     // get statuses as headers for the table
 906     $url = CRM_Utils_System::url('civicrm/case/search', "reset=1&force=1&all=1&status=");
 907     foreach ($caseStatuses as $key => $name) {
 908       $caseSummary['headers'][$key]['status'] = $name;
 909       $caseSummary['headers'][$key]['url'] = $url . $key;
 910     }
 911
 912     // build rows with actual data
 913     $rows = array();
 914     $myGroupByClause = $mySelectClause = $myCaseFromClause = $myCaseWhereClause = '';
 915
 916     if ($allCases) {
 917       $userID = 'null';
 918       $all = 1;
 919       $case_owner = 1;
 920       $myGroupByClause = ' GROUP BY civicrm_case.id';
 921     }
 922     else {
 923       $all = 0;
 924       $case_owner = 2;
 925       $myCaseWhereClause = " AND case_relationship.contact_id_b = {$userID}";
 926       $myGroupByClause = " GROUP BY CONCAT(case_relationship.case_id,'-',case_relationship.contact_id_b)";
 927     }
 928
 929     // FIXME: This query could be a lot more efficient if it used COUNT() instead of returning all rows and then counting them with php
 930     $query = "
 931 SELECT case_status.label AS case_status, status_id, civicrm_case_type.title AS case_type,
 932  case_type_id, case_relationship.contact_id_b
 933  FROM civicrm_case
 934  INNER JOIN civicrm_case_contact cc on cc.case_id = civicrm_case.id
 935  LEFT JOIN civicrm_case_type ON civicrm_case.case_type_id = civicrm_case_type.id
 936  LEFT JOIN civicrm_option_group option_group_case_status ON ( option_group_case_status.name = 'case_status' )
 937  LEFT JOIN civicrm_option_value case_status ON ( civicrm_case.status_id = case_status.value
 938  AND option_group_case_status.id = case_status.option_group_id )
 939  LEFT JOIN civicrm_relationship case_relationship ON ( case_relationship.case_id  = civicrm_case.id
 940  AND case_relationship.contact_id_b = {$userID})
 941  WHERE is_deleted = 0 AND cc.contact_id IN (SELECT id FROM civicrm_contact WHERE is_deleted <> 1)
 942 {$myCaseWhereClause} {$myGroupByClause}";
 943
 944     $res = CRM_Core_DAO::executeQuery($query, CRM_Core_DAO::$_nullArray);
 945     while ($res->fetch()) {
 946       if (!empty($rows[$res->case_type]) && !empty($rows[$res->case_type][$res->case_status])) {
 947         $rows[$res->case_type][$res->case_status]['count'] = $rows[$res->case_type][$res->case_status]['count'] + 1;
 948       }
 949       else {
 950         $rows[$res->case_type][$res->case_status] = array(
 951           'count' => 1,
 952           'url' => CRM_Utils_System::url('civicrm/case/search',
 953             "reset=1&force=1&status={$res->status_id}&type={$res->case_type_id}&case_owner={$case_owner}"
 954           ),
 955         );
 956       }
 957     }
 958     $caseSummary['rows'] = array_merge($caseTypes, $rows);
 959
 960     return $caseSummary;
 961   }
 962
 963   /**
 964    * Get Case roles.
 965    *
 966    * @param int $contactID
 967    *   Contact id.
 968    * @param int $caseID
 969    *   Case id.
 970    * @param int $relationshipID
 971    *
 972    * @return array
 973    *   case role / relationships
 974    *
 975    */
 976   public static function getCaseRoles($contactID, $caseID, $relationshipID = NULL) {
 977     $query = '
 978     SELECT  civicrm_relationship.id as civicrm_relationship_id,
 979             civicrm_contact.sort_name as sort_name,
 980             civicrm_email.email as email,
 981             civicrm_phone.phone as phone,
 982             civicrm_relationship.contact_id_b as civicrm_contact_id,
 983             civicrm_relationship.contact_id_a as client_id,
 984             civicrm_relationship_type.label_a_b as relation,
 985             civicrm_relationship_type.id as relation_type
 986       FROM  civicrm_relationship
 987  INNER JOIN  civicrm_relationship_type ON civicrm_relationship.relationship_type_id = civicrm_relationship_type.id
 988  INNER JOIN  civicrm_contact ON civicrm_relationship.contact_id_b = civicrm_contact.id
 989  LEFT JOIN  civicrm_phone ON (civicrm_phone.contact_id = civicrm_contact.id AND civicrm_phone.is_primary = 1)
 990  LEFT JOIN  civicrm_email ON (civicrm_email.contact_id = civicrm_contact.id AND civicrm_email.is_primary = 1)
 991      WHERE  civicrm_relationship.contact_id_a = %1 AND civicrm_relationship.case_id = %2';
 992
 993     $params = array(
 994       1 => array($contactID, 'Positive'),
 995       2 => array($caseID, 'Positive'),
 996     );
 997
 998     if ($relationshipID) {
 999       $query .= ' AND civicrm_relationship.id = %3 ';
1000       $params[3] = array($relationshipID, 'Integer');
1001     }
1002     $dao = CRM_Core_DAO::executeQuery($query, $params);
1003
1004     $values = array();
1005     while ($dao->fetch()) {
1006       $rid = $dao->civicrm_relationship_id;
1007       $values[$rid]['cid'] = $dao->civicrm_contact_id;
1008       $values[$rid]['relation'] = $dao->relation;
1009       $values[$rid]['name'] = $dao->sort_name;
1010       $values[$rid]['email'] = $dao->email;
1011       $values[$rid]['phone'] = $dao->phone;
1012       $values[$rid]['relation_type'] = $dao->relation_type;
1013       $values[$rid]['rel_id'] = $dao->civicrm_relationship_id;
1014       $values[$rid]['client_id'] = $dao->client_id;
1015     }
1016
1017     $dao->free();
1018     return $values;
1019   }
1020
1021   /**
1022    * Get Case Activities.
1023    *
1024    * @param int $caseID
1025    *   Case id.
1026    * @param array $params
1027    *   Posted params.
1028    * @param int $contactID
1029    *   Contact id.
1030    *
1031    * @param null $context
1032    * @param int $userID
1033    * @param null $type
1034    *
1035    * @return array
1036    *   Array of case activities
1037    *
1038    */
1039   public static function getCaseActivity($caseID, &$params, $contactID, $context = NULL, $userID = NULL, $type = NULL) {
1040     $values = array();
1041
1042     $activityContacts = CRM_Core_OptionGroup::values('activity_contacts', FALSE, FALSE, FALSE, NULL, 'name');
1043     $assigneeID = CRM_Utils_Array::key('Activity Assignees', $activityContacts);
1044     $sourceID = CRM_Utils_Array::key('Activity Source', $activityContacts);
1045     $targetID = CRM_Utils_Array::key('Activity Targets', $activityContacts);
1046
1047     // CRM-5081 - formatting the dates to omit seconds.
1048     // Note the 00 in the date format string is needed otherwise later on it thinks scheduled ones are overdue.
1049     $select = "
1050            SELECT COUNT(ca.id) AS ismultiple,
1051                   ca.id AS id,
1052                   ca.activity_type_id AS type,
1053                   ca.activity_type_id AS activity_type_id,
1054                   tcc.sort_name AS target_contact_name,
1055                   tcc.id AS target_contact_id,
1056                   scc.sort_name AS source_contact_name,
1057                   scc.id AS source_contact_id,
1058                   acc.sort_name AS assignee_contact_name,
1059                   acc.id AS assignee_contact_id,
1060                   DATE_FORMAT(
1061                     IF(ca.activity_date_time < NOW() AND ca.status_id=ov.value,
1062                       ca.activity_date_time,
1063                       DATE_ADD(NOW(), INTERVAL 1 YEAR)
1064                     ), '%Y%m%d%H%i00') AS overdue_date,
1065                   DATE_FORMAT(ca.activity_date_time, '%Y%m%d%H%i00') AS display_date,
1066                   ca.status_id AS status,
1067                   ca.subject AS subject,
1068                   ca.is_deleted AS deleted,
1069                   ca.priority_id AS priority,
1070                   ca.weight AS weight,
1071                   GROUP_CONCAT(ef.file_id) AS attachment_ids ";
1072
1073     $from = "
1074              FROM civicrm_case_activity cca
1075        INNER JOIN civicrm_activity ca
1076                ON ca.id = cca.activity_id
1077        INNER JOIN civicrm_activity_contact cas
1078                ON cas.activity_id = ca.id
1079               AND cas.record_type_id = {$sourceID}
1080        INNER JOIN civicrm_contact scc
1081                ON scc.id = cas.contact_id
1082         LEFT JOIN civicrm_activity_contact caa
1083                ON caa.activity_id = ca.id
1084               AND caa.record_type_id = {$assigneeID}
1085         LEFT JOIN civicrm_contact acc
1086                ON acc.id = caa.contact_id
1087         LEFT JOIN civicrm_activity_contact cat
1088                ON cat.activity_id = ca.id
1089               AND cat.record_type_id = {$targetID}
1090         LEFT JOIN civicrm_contact tcc
1091                ON tcc.id = cat.contact_id
1092        INNER JOIN civicrm_option_group cog
1093                ON cog.name = 'activity_type'
1094        INNER JOIN civicrm_option_value cov
1095                ON cov.option_group_id = cog.id
1096               AND cov.value = ca.activity_type_id
1097               AND cov.is_active = 1
1098         LEFT JOIN civicrm_entity_file ef
1099                ON ef.entity_table = 'civicrm_activity'
1100               AND ef.entity_id = ca.id
1101   LEFT OUTER JOIN civicrm_option_group og
1102                ON og.name = 'activity_status'
1103   LEFT OUTER JOIN civicrm_option_value ov
1104                ON ov.option_group_id=og.id
1105               AND ov.name = 'Scheduled'";
1106
1107     $where = '
1108             WHERE cca.case_id= %1
1109               AND ca.is_current_revision = 1';
1110
1111     if (!empty($params['source_contact_id'])) {
1112       $where .= "
1113               AND cas.contact_id = " . CRM_Utils_Type::escape($params['source_contact_id'], 'Integer');
1114     }
1115
1116     if (!empty($params['status_id'])) {
1117       $where .= "
1118               AND ca.status_id = " . CRM_Utils_Type::escape($params['status_id'], 'Integer');
1119     }
1120
1121     if (!empty($params['activity_deleted'])) {
1122       $where .= "
1123               AND ca.is_deleted = 1";
1124     }
1125     else {
1126       $where .= "
1127               AND ca.is_deleted = 0";
1128     }
1129
1130     if (!empty($params['activity_type_id'])) {
1131       $where .= "
1132               AND ca.activity_type_id = " . CRM_Utils_Type::escape($params['activity_type_id'], 'Integer');
1133     }
1134
1135     if (!empty($params['activity_date_low'])) {
1136       $fromActivityDate = CRM_Utils_Type::escape(CRM_Utils_Date::processDate($params['activity_date_low']), 'Date');
1137     }
1138     if (!empty($fromActivityDate)) {
1139       $where .= "
1140               AND ca.activity_date_time >= '{$fromActivityDate}'";
1141     }
1142
1143     if (!empty($params['activity_date_high'])) {
1144       $toActivityDate = CRM_Utils_Type::escape(CRM_Utils_Date::processDate($params['activity_date_high']), 'Date');
1145       $toActivityDate = $toActivityDate ? $toActivityDate + 235959 : NULL;
1146     }
1147     if (!empty($toActivityDate)) {
1148       $where .= "
1149               AND ca.activity_date_time <= '{$toActivityDate}'";
1150     }
1151
1152     $groupBy = "
1153          GROUP BY ca.id ";
1154
1155     $sortBy = CRM_Utils_Array::value('sortBy', $params);
1156     if (!$sortBy) {
1157       // CRM-5081 - added id to act like creation date
1158       $orderBy = "
1159          ORDER BY overdue_date ASC, display_date DESC, weight DESC";
1160     }
1161     else {
1162       $sortBy = CRM_Utils_Type::escape($sortBy, 'String');
1163       $orderBy = " ORDER BY $sortBy ";
1164     }
1165
1166     $page = CRM_Utils_Array::value('page', $params);
1167     $rp = CRM_Utils_Array::value('rp', $params);
1168
1169     if (!$page) {
1170       $page = 1;
1171     }
1172     if (!$rp) {
1173       $rp = 10;
1174     }
1175     $start = (($page - 1) * $rp);
1176     $limit = " LIMIT $start, $rp";
1177
1178     $query = $select . $from . $where . $groupBy . $orderBy . $limit;
1179     $queryParams = array(1 => array($caseID, 'Integer'));
1180
1181     $dao = CRM_Core_DAO::executeQuery($query, $queryParams);
1182
1183     $activityTypes = CRM_Case_PseudoConstant::caseActivityType(FALSE, TRUE);
1184     $activityStatuses = CRM_Core_PseudoConstant::activityStatus();
1185
1186     $url = CRM_Utils_System::url("civicrm/case/activity",
1187       "reset=1&cid={$contactID}&caseid={$caseID}", FALSE, NULL, FALSE
1188     );
1189
1190     $contextUrl = '';
1191     if ($context == 'fulltext') {
1192       $contextUrl = "&context={$context}";
1193     }
1194     $editUrl = "{$url}&action=update{$contextUrl}";
1195     $deleteUrl = "{$url}&action=delete{$contextUrl}";
1196     $restoreUrl = "{$url}&action=renew{$contextUrl}";
1197     $viewTitle = ts('View activity');
1198     $statusTitle = ts('Edit Status');
1199
1200     $emailActivityTypeIDs = array(
1201       'Email' => CRM_Core_OptionGroup::getValue('activity_type',
1202         'Email',
1203         'name'
1204       ),
1205       'Inbound Email' => CRM_Core_OptionGroup::getValue('activity_type',
1206         'Inbound Email',
1207         'name'
1208       ),
1209     );
1210
1211     $emailActivityTypeIDs = array(
1212       'Email' => CRM_Core_OptionGroup::getValue('activity_type',
1213         'Email',
1214         'name'
1215       ),
1216       'Inbound Email' => CRM_Core_OptionGroup::getValue('activity_type',
1217         'Inbound Email',
1218         'name'
1219       ),
1220     );
1221
1222     $caseDeleted = CRM_Core_DAO::getFieldValue('CRM_Case_DAO_Case', $caseID, 'is_deleted');
1223
1224     // define statuses which are handled like Completed status (others are assumed to be handled like Scheduled status)
1225     $compStatusValues = array();
1226     $compStatusNames = array('Completed', 'Left Message', 'Cancelled', 'Unreachable', 'Not Required');
1227     foreach ($compStatusNames as $name) {
1228       $compStatusValues[] = CRM_Core_OptionGroup::getValue('activity_status', $name, 'name');
1229     }
1230
1231     $contactViewUrl = CRM_Utils_System::url("civicrm/contact/view", "reset=1&cid=", FALSE, NULL, FALSE);
1232     $hasViewContact = CRM_Core_Permission::giveMeAllACLs();
1233     $clientIds = self::retrieveContactIdsByCaseId($caseID);
1234
1235     if (!$userID) {
1236       $session = CRM_Core_Session::singleton();
1237       $userID = $session->get('userID');
1238     }
1239
1240     $caseActivities = array();
1241     $caseCount = 0;
1242
1243     while ($dao->fetch()) {
1244       $caseCount++;
1245       $caseActivity = array();
1246       $caseActivityId = $dao->id;
1247
1248       $allowView = self::checkPermission($caseActivityId, 'view', $dao->activity_type_id, $userID);
1249       $allowEdit = self::checkPermission($caseActivityId, 'edit', $dao->activity_type_id, $userID);
1250       $allowDelete = self::checkPermission($caseActivityId, 'delete', $dao->activity_type_id, $userID);
1251
1252       //do not have sufficient permission
1253       //to access given case activity record.
1254       if (!$allowView && !$allowEdit && !$allowDelete) {
1255         continue;
1256       }
1257
1258       //Add classes to the row, via DataTables syntax
1259       $caseActivity['DT_RowClass'] = "crm-entity";
1260
1261       if (CRM_Utils_Array::crmInArray($dao->status, $compStatusValues)) {
1262         $caseActivity['DT_RowClass'] .= " status-completed";
1263       }
1264       else {
1265         if (CRM_Utils_Date::overdue($dao->display_date)) {
1266           $caseActivity['DT_RowClass'] .= " status-overdue";
1267         }
1268         else {
1269           $caseActivity['DT_RowClass'] .= " status-scheduled";
1270         }
1271       }
1272
1273       if (!empty($dao->priority)) {
1274         if ($dao->priority == CRM_Core_OptionGroup::getValue('priority', 'Urgent', 'name')) {
1275           $caseActivity['DT_RowClass'] .= " priority-urgent ";
1276         }
1277         elseif ($dao->priority == CRM_Core_OptionGroup::getValue('priority', 'Low', 'name')) {
1278           $caseActivity['DT_RowClass'] .= " priority-low ";
1279         }
1280       }
1281
1282       //Add data to the row for inline editing, via DataTable syntax
1283       $caseActivity['DT_RowData'] = array();
1284       $caseActivity['DT_RowData']['entity'] = 'activity';
1285       $caseActivity['DT_RowData']['id'] = $caseActivityId;
1286
1287       //Activity Date and Time
1288       $caseActivity['activity_date_time'] = CRM_Utils_Date::customFormat($dao->display_date);
1289
1290       //Activity Subject
1291       $caseActivity['subject'] = $dao->subject;
1292
1293       //Activity Type
1294       $caseActivity['type'] = $activityTypes[$dao->type]['label'];
1295
1296       //Activity Target (With)
1297       $targetContact = '';
1298       if (isset($dao->target_contact_id)) {
1299         $targetContact = $dao->target_contact_name;
1300         if ($hasViewContact) {
1301           $targetContact = '<a href="' . $contactViewUrl . $dao->target_contact_id . '">' . $dao->target_contact_name . '</a>';
1302         }
1303       }
1304       $caseActivity['target_contact_name'] = $targetContact;
1305
1306       //Activity Source Contact (Reporter)
1307       $sourceContact = $dao->source_contact_name;
1308       if ($hasViewContact) {
1309         $sourceContact = '<a href="' . $contactViewUrl . $dao->source_contact_id . '">' . $dao->source_contact_name . '</a>';
1310       }
1311       $caseActivity['source_contact_name'] = $sourceContact;
1312
1313       //Activity Assignee. CRM-4485.
1314       $assigneeContact = '';
1315       if (isset($dao->assignee_contact_id)) {
1316         $assigneeContact = $dao->assignee_contact_name;
1317         if ($hasViewContact) {
1318           $assigneeContact = '<a href="' . $contactViewUrl . $dao->assignee_contact_id . '">' . $dao->assignee_contact_name . '</a>';
1319         }
1320       }
1321       $caseActivity['assignee_contact_name'] = $assigneeContact;
1322
1323       //Activity Status
1324       $caseActivity['status_id'] = $activityStatuses[$dao->status];
1325
1326       // FIXME: Why are we not using CRM_Core_Action for these links? This is too much manual work and likely to get out-of-sync with core markup.
1327       $url = "";
1328       $css = 'class="action-item crm-hover-button"';
1329       if ($allowView) {
1330         $viewUrl = CRM_Utils_System::url('civicrm/case/activity/view', array('cid' => $contactID, 'aid' => $caseActivityId));
1331         $url = '<a ' . str_replace('action-item', 'action-item medium-pop-up', $css) . 'href="' . $viewUrl . '" title="' . $viewTitle . '">' . ts('View') . '</a>';
1332       }
1333       $additionalUrl = "&id={$caseActivityId}";
1334       if (!$dao->deleted) {
1335         //hide edit link of activity type email.CRM-4530.
1336         if (!in_array($dao->type, $emailActivityTypeIDs)) {
1337           //hide Edit link if activity type is NOT editable (special case activities).CRM-5871
1338           if ($allowEdit) {
1339             $url .= '<a ' . $css . ' href="' . $editUrl . $additionalUrl . '">' . ts('Edit') . '</a> ';
1340           }
1341         }
1342         if ($allowDelete) {
1343           $url .= ' <a ' . str_replace('action-item', 'action-item small-popup', $css) . ' href="' . $deleteUrl . $additionalUrl . '">' . ts('Delete') . '</a>';
1344         }
1345       }
1346       elseif (!$caseDeleted) {
1347         $url = ' <a ' . $css . ' href="' . $restoreUrl . $additionalUrl . '">' . ts('Restore') . '</a>';
1348         $caseActivity['status_id'] = $caseActivity['status_id'] . '<br /> (deleted)';
1349       }
1350
1351       //check for operations.
1352       if (self::checkPermission($caseActivityId, 'Move To Case', $dao->activity_type_id)) {
1353         $url .= ' <a ' . $css . ' href="#" onClick="Javascript:fileOnCase( \'move\',' . $caseActivityId . ', ' . $caseID . ', this ); return false;">' . ts('Move To Case') . '</a> ';
1354       }
1355       if (self::checkPermission($caseActivityId, 'Copy To Case', $dao->activity_type_id)) {
1356         $url .= ' <a ' . $css . ' href="#" onClick="Javascript:fileOnCase( \'copy\',' . $caseActivityId . ',' . $caseID . ', this ); return false;">' . ts('Copy To Case') . '</a> ';
1357       }
1358       // if there are file attachments we will return how many and, if only one, add a link to it
1359       if (!empty($dao->attachment_ids)) {
1360         $attachmentIDs = explode(',', $dao->attachment_ids);
1361         $caseActivity['no_attachments'] = count($attachmentIDs);
1362         if ($caseActivity['no_attachments'] == 1) {
1363           // if there is only one it's easy to do a link - otherwise just flag it
1364           $attachmentViewUrl = CRM_Utils_System::url(
1365             "civicrm/file",
1366             "reset=1&eid=" . $caseActivityId . "&id=" . $dao->attachment_ids,
1367             FALSE,
1368             NULL,
1369             FALSE
1370           );
1371           $url .= " <a href='$attachmentViewUrl' ><span class='icon paper-icon'></span></a>";
1372         }
1373       }
1374
1375       $caseActivity['links'] = $url;
1376
1377       array_push($caseActivities, $caseActivity);
1378     }
1379     $dao->free();
1380
1381     $caseActivitiesDT = array();
1382     $caseActivitiesDT['data'] = $caseActivities;
1383     $caseActivitiesDT['recordsTotal'] = $caseCount;
1384     $caseActivitiesDT['recordsFiltered'] = $caseCount;
1385
1386     return $caseActivitiesDT;
1387   }
1388
1389   /**
1390    * Get Case Related Contacts.
1391    *
1392    * @param int $caseID
1393    *   Case id.
1394    * @param bool $skipDetails
1395    *   If true include details of contacts.
1396    *
1397    * @return array
1398    *   array of return properties
1399    *
1400    */
1401   public static function getRelatedContacts($caseID, $skipDetails = FALSE) {
1402     $values = array();
1403     $query = '
1404       SELECT cc.display_name as name, cc.sort_name as sort_name, cc.id, crt.label_b_a as role, ce.email
1405       FROM civicrm_relationship cr
1406       LEFT JOIN civicrm_relationship_type crt
1407         ON crt.id = cr.relationship_type_id
1408       LEFT JOIN civicrm_contact cc
1409         ON cc.id = cr.contact_id_b
1410       LEFT JOIN civicrm_email ce
1411         ON ce.contact_id = cc.id
1412         AND ce.is_primary= 1
1413       WHERE cr.case_id =  %1
1414       GROUP BY cc.id';
1415
1416     $params = array(1 => array($caseID, 'Integer'));
1417     $dao = CRM_Core_DAO::executeQuery($query, $params);
1418
1419     while ($dao->fetch()) {
1420       if ($skipDetails) {
1421         $values[$dao->id] = 1;
1422       }
1423       else {
1424         $values[] = array(
1425           'contact_id' => $dao->id,
1426           'display_name' => $dao->name,
1427           'sort_name' => $dao->sort_name,
1428           'role' => $dao->role,
1429           'email' => $dao->email,
1430         );
1431       }
1432     }
1433     $dao->free();
1434
1435     return $values;
1436   }
1437
1438   /**
1439    * Send e-mail copy of activity
1440    *
1441    * @param int $clientId
1442    * @param int $activityId
1443    *   Activity Id.
1444    * @param array $contacts
1445    *   Array of related contact.
1446    *
1447    * @param null $attachments
1448    * @param int $caseId
1449    *
1450    * @return void
1451    */
1452   public static function sendActivityCopy($clientId, $activityId, $contacts, $attachments = NULL, $caseId) {
1453     if (!$activityId) {
1454       return;
1455     }
1456
1457     $tplParams = $activityInfo = array();
1458     //if its a case activity
1459     if ($caseId) {
1460       $activityTypeId = CRM_Core_DAO::getFieldValue('CRM_Activity_DAO_Activity', $activityId, 'activity_type_id');
1461       $nonCaseActivityTypes = CRM_Core_PseudoConstant::activityType();
1462       if (!empty($nonCaseActivityTypes[$activityTypeId])) {
1463         $anyActivity = TRUE;
1464       }
1465       else {
1466         $anyActivity = FALSE;
1467       }
1468       $tplParams['isCaseActivity'] = 1;
1469       $tplParams['client_id'] = $clientId;
1470     }
1471     else {
1472       $anyActivity = TRUE;
1473     }
1474
1475     $xmlProcessorProcess = new CRM_Case_XMLProcessor_Process();
1476     $isRedact = $xmlProcessorProcess->getRedactActivityEmail();
1477
1478     $xmlProcessorReport = new CRM_Case_XMLProcessor_Report();
1479
1480     $activityInfo = $xmlProcessorReport->getActivityInfo($clientId, $activityId, $anyActivity, $isRedact);
1481     if ($caseId) {
1482       $activityInfo['fields'][] = array('label' => 'Case ID', 'type' => 'String', 'value' => $caseId);
1483     }
1484     $tplParams['activity'] = $activityInfo;
1485     foreach ($tplParams['activity']['fields'] as $k => $val) {
1486       if (CRM_Utils_Array::value('label', $val) == ts('Subject')) {
1487         $activitySubject = $val['value'];
1488         break;
1489       }
1490     }
1491     $session = CRM_Core_Session::singleton();
1492     // CRM-8926 If user is not logged in, use the activity creator as userID
1493     if (!($userID = $session->get('userID'))) {
1494       $userID = CRM_Activity_BAO_Activity::getSourceContactID($activityId);
1495     }
1496
1497     //also create activities simultaneously of this copy.
1498     $activityParams = array();
1499
1500     $activityParams['source_record_id'] = $activityId;
1501     $activityParams['source_contact_id'] = $userID;
1502     $activityParams['activity_type_id'] = CRM_Core_OptionGroup::getValue('activity_type', 'Email', 'name');
1503     $activityParams['activity_date_time'] = date('YmdHis');
1504     $activityParams['status_id'] = CRM_Core_OptionGroup::getValue('activity_status', 'Completed', 'name');
1505     $activityParams['medium_id'] = CRM_Core_OptionGroup::getValue('encounter_medium', 'email', 'name');
1506     $activityParams['case_id'] = $caseId;
1507     $activityParams['is_auto'] = 0;
1508     $activityParams['target_id'] = $clientId;
1509
1510     $tplParams['activitySubject'] = $activitySubject;
1511
1512     // if it’s a case activity, add hashed id to the template (CRM-5916)
1513     if ($caseId) {
1514       $tplParams['idHash'] = substr(sha1(CIVICRM_SITE_KEY . $caseId), 0, 7);
1515     }
1516
1517     $result = array();
1518     list($name, $address) = CRM_Contact_BAO_Contact_Location::getEmailDetails($userID);
1519
1520     $receiptFrom = "$name <$address>";
1521
1522     $recordedActivityParams = array();
1523
1524     foreach ($contacts as $mail => $info) {
1525       $tplParams['contact'] = $info;
1526       self::buildPermissionLinks($tplParams, $activityParams);
1527
1528       $displayName = CRM_Utils_Array::value('display_name', $info);
1529
1530       list($result[CRM_Utils_Array::value('contact_id', $info)], $subject, $message, $html) = CRM_Core_BAO_MessageTemplate::sendTemplate(
1531         array(
1532           'groupName' => 'msg_tpl_workflow_case',
1533           'valueName' => 'case_activity',
1534           'contactId' => CRM_Utils_Array::value('contact_id', $info),
1535           'tplParams' => $tplParams,
1536           'from' => $receiptFrom,
1537           'toName' => $displayName,
1538           'toEmail' => $mail,
1539           'attachments' => $attachments,
1540         )
1541       );
1542
1543       $activityParams['subject'] = $activitySubject . ' - copy sent to ' . $displayName;
1544       $activityParams['details'] = $message;
1545
1546       if (!empty($result[$info['contact_id']])) {
1547         /*
1548          * Really only need to record one activity with all the targets combined.
1549          * Originally the template was going to possibly have different content, e.g. depending on permissions,
1550          * but it's always the same content at the moment.
1551          */
1552         if (empty($recordedActivityParams)) {
1553           $recordedActivityParams = $activityParams;
1554         }
1555         else {
1556           $recordedActivityParams['subject'] .= "; $displayName";
1557         }
1558         $recordedActivityParams['target_contact_id'][] = $info['contact_id'];
1559       }
1560       else {
1561         unset($result[CRM_Utils_Array::value('contact_id', $info)]);
1562       }
1563     }
1564
1565     if (!empty($recordedActivityParams)) {
1566       $activity = CRM_Activity_BAO_Activity::create($recordedActivityParams);
1567
1568       //create case_activity record if its case activity.
1569       if ($caseId) {
1570         $caseParams = array(
1571           'activity_id' => $activity->id,
1572           'case_id' => $caseId,
1573         );
1574         self::processCaseActivity($caseParams);
1575       }
1576     }
1577
1578     return $result;
1579   }
1580
1581   /**
1582    * Retrieve count of activities having a particular type, and
1583    * associated with a particular case.
1584    *
1585    * @param int $caseId
1586    *   ID of the case.
1587    * @param int $activityTypeId
1588    *   ID of the activity type.
1589    *
1590    * @return array
1591    */
1592   public static function getCaseActivityCount($caseId, $activityTypeId) {
1593     $queryParam = array(
1594       1 => array($caseId, 'Integer'),
1595       2 => array($activityTypeId, 'Integer'),
1596     );
1597     $query = "SELECT count(ca.id) as countact
1598  FROM       civicrm_activity ca
1599  INNER JOIN civicrm_case_activity cca ON ca.id = cca.activity_id
1600  WHERE      ca.activity_type_id = %2
1601  AND       cca.case_id = %1
1602  AND        ca.is_deleted = 0";
1603
1604     $dao = CRM_Core_DAO::executeQuery($query, $queryParam);
1605     if ($dao->fetch()) {
1606       return $dao->countact;
1607     }
1608
1609     return FALSE;
1610   }
1611
1612   /**
1613    * Create an activity for a case via email.
1614    *
1615    * @param int $file
1616    *   Email sent.
1617    *
1618    * @return array|void
1619    *   $activity object of newly creted activity via email
1620    */
1621   public static function recordActivityViaEmail($file) {
1622     if (!file_exists($file) ||
1623       !is_readable($file)
1624     ) {
1625       return CRM_Core_Error::fatal(ts('File %1 does not exist or is not readable',
1626         array(1 => $file)
1627       ));
1628     }
1629
1630     $result = CRM_Utils_Mail_Incoming::parse($file);
1631     if ($result['is_error']) {
1632       return $result;
1633     }
1634
1635     foreach ($result['to'] as $to) {
1636       $caseId = NULL;
1637
1638       $emailPattern = '/^([A-Z0-9._%+-]+)\+([\d]+)@[A-Z0-9.-]+\.[A-Z]{2,4}$/i';
1639       $replacement = preg_replace($emailPattern, '$2', $to['email']);
1640
1641       if ($replacement !== $to['email']) {
1642         $caseId = $replacement;
1643         //if caseId is invalid, return as error file
1644         if (!CRM_Core_DAO::getFieldValue('CRM_Case_DAO_Case', $caseId, 'id')) {
1645           return CRM_Core_Error::createAPIError(ts('Invalid case ID ( %1 ) in TO: field.',
1646             array(1 => $caseId)
1647           ));
1648         }
1649       }
1650       else {
1651         continue;
1652       }
1653
1654       // TODO: May want to replace this with a call to getRelatedAndGlobalContacts() when this feature is revisited.
1655       // (Or for efficiency call the global one outside the loop and then union with this each time.)
1656       $contactDetails = self::getRelatedContacts($caseId, TRUE);
1657
1658       if (!empty($contactDetails[$result['from']['id']])) {
1659         $params = array();
1660         $params['subject'] = $result['subject'];
1661         $params['activity_date_time'] = $result['date'];
1662         $params['details'] = $result['body'];
1663         $params['source_contact_id'] = $result['from']['id'];
1664         $params['status_id'] = CRM_Core_OptionGroup::getValue('activity_status',
1665           'Completed',
1666           'name'
1667         );
1668
1669         $details = CRM_Case_PseudoConstant::caseActivityType();
1670         $matches = array();
1671         preg_match('/^\W+([a-zA-Z0-9_ ]+)(\W+)?\n/i',
1672           $result['body'], $matches
1673         );
1674
1675         if (!empty($matches) && isset($matches[1])) {
1676           $activityType = trim($matches[1]);
1677           if (isset($details[$activityType])) {
1678             $params['activity_type_id'] = $details[$activityType]['id'];
1679           }
1680         }
1681         if (!isset($params['activity_type_id'])) {
1682           $params['activity_type_id'] = CRM_Core_OptionGroup::getValue('activity_type', 'Inbound Email', 'name');
1683         }
1684
1685         // create activity
1686         $activity = CRM_Activity_BAO_Activity::create($params);
1687
1688         $caseParams = array(
1689           'activity_id' => $activity->id,
1690           'case_id' => $caseId,
1691         );
1692         self::processCaseActivity($caseParams);
1693       }
1694       else {
1695         return CRM_Core_Error::createAPIError(ts('FROM email contact %1 doesn\'t have a relationship to the referenced case.',
1696           array(1 => $result['from']['email'])
1697         ));
1698       }
1699     }
1700   }
1701
1702   /**
1703    * Retrieve the scheduled activity type and date.
1704    *
1705    * @param array $cases
1706    *   Array of contact and case id.
1707    *
1708    * @param string $type
1709    *
1710    * @return array
1711    *   Array of scheduled activity type and date
1712    *
1713    *
1714    */
1715   public static function getNextScheduledActivity($cases, $type = 'upcoming') {
1716     $session = CRM_Core_Session::singleton();
1717     $userID = $session->get('userID');
1718
1719     $caseID = implode(',', $cases['case_id']);
1720     $contactID = implode(',', $cases['contact_id']);
1721
1722     $condition = "
1723  AND civicrm_case_contact.contact_id IN( {$contactID} )
1724  AND civicrm_case.id IN( {$caseID})
1725  AND civicrm_case.is_deleted     = {$cases['case_deleted']}";
1726
1727     $query = self::getCaseActivityQuery($type, $userID, $condition, $cases['case_deleted']);
1728
1729     $res = CRM_Core_DAO::executeQuery($query, CRM_Core_DAO::$_nullArray);
1730
1731     $activityInfo = array();
1732     while ($res->fetch()) {
1733       if ($type == 'upcoming') {
1734         $activityInfo[$res->case_id]['date'] = $res->case_scheduled_activity_date;
1735         $activityInfo[$res->case_id]['type'] = $res->case_scheduled_activity_type;
1736       }
1737       else {
1738         $activityInfo[$res->case_id]['date'] = $res->case_recent_activity_date;
1739         $activityInfo[$res->case_id]['type'] = $res->case_recent_activity_type;
1740       }
1741     }
1742
1743     return $activityInfo;
1744   }
1745
1746   /**
1747    * Combine all the exportable fields from the lower levels object.
1748    *
1749    * @return array
1750    *   array of exportable Fields
1751    */
1752   public static function &exportableFields() {
1753     if (!self::$_exportableFields) {
1754       if (!self::$_exportableFields) {
1755         self::$_exportableFields = array();
1756       }
1757
1758       $fields = CRM_Case_DAO_Case::export();
1759       $fields['case_role'] = array('title' => ts('Role in Case'));
1760       $fields['case_type'] = array(
1761         'title' => ts('Case Type'),
1762         'name' => 'case_type',
1763       );
1764       $fields['case_status'] = array(
1765         'title' => ts('Case Status'),
1766         'name' => 'case_status',
1767       );
1768
1769       self::$_exportableFields = $fields;
1770     }
1771     return self::$_exportableFields;
1772   }
1773
1774   /**
1775    * Restore the record that are associated with this case.
1776    *
1777    * @param int $caseId
1778    *   Id of the case to restore.
1779    *
1780    * @return bool
1781    */
1782   public static function restoreCase($caseId) {
1783     //restore activities
1784     $activities = self::getCaseActivityDates($caseId);
1785     if ($activities) {
1786       foreach ($activities as $value) {
1787         CRM_Activity_BAO_Activity::restoreActivity($value);
1788       }
1789     }
1790     //restore case
1791     $case = new CRM_Case_DAO_Case();
1792     $case->id = $caseId;
1793     $case->is_deleted = 0;
1794     $case->save();
1795
1796     //CRM-7364, enable relationships
1797     self::enableDisableCaseRelationships($caseId, TRUE);
1798     return TRUE;
1799   }
1800
1801   /**
1802    * @param $groupInfo
1803    * @param null $sort
1804    * @param null $showLinks
1805    * @param bool $returnOnlyCount
1806    * @param int $offset
1807    * @param int $rowCount
1808    *
1809    * @return array
1810    */
1811   public static function getGlobalContacts(&$groupInfo, $sort = NULL, $showLinks = NULL, $returnOnlyCount = FALSE, $offset = 0, $rowCount = 25) {
1812     $globalContacts = array();
1813
1814     $settingsProcessor = new CRM_Case_XMLProcessor_Settings();
1815     $settings = $settingsProcessor->run();
1816     if (!empty($settings)) {
1817       $groupInfo['name'] = $settings['groupname'];
1818       if ($groupInfo['name']) {
1819         $searchParams = array('name' => $groupInfo['name']);
1820         $results = array();
1821         CRM_Contact_BAO_Group::retrieve($searchParams, $results);
1822         if ($results) {
1823           $groupInfo['id'] = $results['id'];
1824           $groupInfo['title'] = $results['title'];
1825           $params = array(array('group', 'IN', array($groupInfo['id'] => 1), 0, 0));
1826           $return = array('contact_id' => 1, 'sort_name' => 1, 'display_name' => 1, 'email' => 1, 'phone' => 1);
1827           list($globalContacts) = CRM_Contact_BAO_Query::apiQuery($params, $return, NULL, $sort, $offset, $rowCount, TRUE, $returnOnlyCount);
1828
1829           if ($returnOnlyCount) {
1830             return $globalContacts;
1831           }
1832
1833           if ($showLinks) {
1834             foreach ($globalContacts as $idx => $contact) {
1835               $globalContacts[$idx]['sort_name'] = '<a href="' . CRM_Utils_System::url('civicrm/contact/view', "reset=1&cid={$contact['contact_id']}") . '">' . $contact['sort_name'] . '</a>';
1836             }
1837           }
1838         }
1839       }
1840     }
1841     return $globalContacts;
1842   }
1843
1844   /**
1845    * Convenience function to get both case contacts and global in one array.
1846    * @param int $caseId
1847    *
1848    * @return array
1849    */
1850   public static function getRelatedAndGlobalContacts($caseId) {
1851     $relatedContacts = self::getRelatedContacts($caseId);
1852
1853     $groupInfo = array();
1854     $globalContacts = self::getGlobalContacts($groupInfo);
1855
1856     //unset values which are not required.
1857     foreach ($globalContacts as $k => & $v) {
1858       unset($v['email_id']);
1859       unset($v['group_contact_id']);
1860       unset($v['status']);
1861       unset($v['phone']);
1862       $v['role'] = $groupInfo['title'];
1863     }
1864     //include multiple listings for the same contact/different roles.
1865     $relatedGlobalContacts = array_merge($relatedContacts, $globalContacts);
1866     return $relatedGlobalContacts;
1867   }
1868
1869   /**
1870    * Get Case ActivitiesDueDates with given criteria.
1871    *
1872    * @param int $caseID
1873    *   Case id.
1874    * @param array $criteriaParams
1875    *   Given criteria.
1876    * @param bool $latestDate
1877    *   If set newest or oldest date is selected.
1878    *
1879    * @return array
1880    *   case activities due dates
1881    *
1882    */
1883   public static function getCaseActivityDates($caseID, $criteriaParams = array(), $latestDate = FALSE) {
1884     $values = array();
1885     $selectDate = " ca.activity_date_time";
1886     $where = $groupBy = ' ';
1887
1888     if (!$caseID) {
1889       return NULL;
1890     }
1891
1892     if ($latestDate) {
1893       if (!empty($criteriaParams['activity_type_id'])) {
1894         $where .= " AND ca.activity_type_id    = " . CRM_Utils_Type::escape($criteriaParams['activity_type_id'], 'Integer');
1895         $where .= " AND ca.is_current_revision = 1";
1896         $groupBy .= " GROUP BY ca.activity_type_id";
1897       }
1898
1899       if (!empty($criteriaParams['newest'])) {
1900         $selectDate = " max(ca.activity_date_time) ";
1901       }
1902       else {
1903         $selectDate = " min(ca.activity_date_time) ";
1904       }
1905     }
1906
1907     $query = "SELECT ca.id, {$selectDate} as activity_date
1908                   FROM civicrm_activity ca
1909                   LEFT JOIN civicrm_case_activity cca ON cca.activity_id = ca.id LEFT JOIN civicrm_case cc ON cc.id = cca.case_id
1910                   WHERE cc.id = %1 {$where} {$groupBy}";
1911
1912     $params = array(1 => array($caseID, 'Integer'));
1913     $dao = CRM_Core_DAO::executeQuery($query, $params);
1914
1915     while ($dao->fetch()) {
1916       $values[$dao->id]['id'] = $dao->id;
1917       $values[$dao->id]['activity_date'] = $dao->activity_date;
1918     }
1919     $dao->free();
1920     return $values;
1921   }
1922
1923   /**
1924    * Create activities when Case or Other roles assigned/modified/deleted.
1925    *
1926    * @param int $caseId
1927    * @param int $relationshipId
1928    *   Relationship id.
1929    * @param int $relContactId
1930    *   Case role assignee contactId.
1931    * @param int $contactId
1932    *
1933    * @return void
1934    *   on success creates activity and case activity
1935    *
1936    */
1937   public static function createCaseRoleActivity($caseId, $relationshipId, $relContactId = NULL, $contactId = NULL) {
1938     if (!$caseId || !$relationshipId || empty($relationshipId)) {
1939       return;
1940     }
1941
1942     $queryParam = array();
1943     if (is_array($relationshipId)) {
1944       $relationshipId = implode(',', $relationshipId);
1945       $relationshipClause = " civicrm_relationship.id IN ($relationshipId)";
1946     }
1947     else {
1948       $relationshipClause = " civicrm_relationship.id = %1";
1949       $queryParam[1] = array($relationshipId, 'Positive');
1950     }
1951
1952     $query = "
1953    SELECT  cc.display_name as clientName,
1954            cca.display_name as  assigneeContactName,
1955            civicrm_relationship.case_id as caseId,
1956            civicrm_relationship_type.label_a_b as relation_a_b,
1957            civicrm_relationship_type.label_b_a as relation_b_a,
1958            civicrm_relationship.contact_id_b as rel_contact_id,
1959            civicrm_relationship.contact_id_a as assign_contact_id
1960      FROM  civicrm_relationship_type,  civicrm_relationship
1961  LEFT JOIN  civicrm_contact cc  ON cc.id  = civicrm_relationship.contact_id_b
1962  LEFT JOIN  civicrm_contact cca ON cca.id = civicrm_relationship.contact_id_a
1963     WHERE  civicrm_relationship.relationship_type_id = civicrm_relationship_type.id AND {$relationshipClause}";
1964
1965     $dao = CRM_Core_DAO::executeQuery($query, $queryParam);
1966
1967     while ($dao->fetch()) {
1968       //to get valid assignee contact(s).
1969       if (isset($dao->caseId) || $dao->rel_contact_id != $contactId) {
1970         $caseRelationship = $dao->relation_a_b;
1971         $assigneContactName = $dao->clientName;
1972         $assigneContactIds[$dao->rel_contact_id] = $dao->rel_contact_id;
1973       }
1974       else {
1975         $caseRelationship = $dao->relation_b_a;
1976         $assigneContactName = $dao->assigneeContactName;
1977         $assigneContactIds[$dao->assign_contact_id] = $dao->assign_contact_id;
1978       }
1979     }
1980
1981     $session = CRM_Core_Session::singleton();
1982     $activityParams = array(
1983       'source_contact_id' => $session->get('userID'),
1984       'subject' => $caseRelationship . ' : ' . $assigneContactName,
1985       'activity_date_time' => date('YmdHis'),
1986       'status_id' => CRM_Core_OptionGroup::getValue('activity_status', 'Completed', 'name'),
1987     );
1988
1989     //if $relContactId is passed, role is added or modified.
1990     if (!empty($relContactId)) {
1991       $activityParams['assignee_contact_id'] = $assigneContactIds;
1992
1993       $activityTypeID = CRM_Core_OptionGroup::getValue('activity_type',
1994         'Assign Case Role',
1995         'name'
1996       );
1997     }
1998     else {
1999       $activityTypeID = CRM_Core_OptionGroup::getValue('activity_type',
2000         'Remove Case Role',
2001         'name'
2002       );
2003     }
2004
2005     $activityParams['activity_type_id'] = $activityTypeID;
2006
2007     $activity = CRM_Activity_BAO_Activity::create($activityParams);
2008
2009     //create case_activity record.
2010     $caseParams = array(
2011       'activity_id' => $activity->id,
2012       'case_id' => $caseId,
2013     );
2014
2015     CRM_Case_BAO_Case::processCaseActivity($caseParams);
2016   }
2017
2018   /**
2019    * Get case manger
2020    * contact which is assigned a case role of case manager.
2021    *
2022    * @param int $caseType
2023    *   Case type.
2024    * @param int $caseId
2025    *   Case id.
2026    *
2027    * @return array
2028    *   array of contact on success otherwise empty
2029    *
2030    */
2031   public static function getCaseManagerContact($caseType, $caseId) {
2032     if (!$caseType || !$caseId) {
2033       return NULL;
2034     }
2035
2036     $caseManagerContact = array();
2037     $xmlProcessor = new CRM_Case_XMLProcessor_Process();
2038
2039     $managerRoleId = $xmlProcessor->getCaseManagerRoleId($caseType);
2040
2041     if (!empty($managerRoleId)) {
2042       $managerRoleQuery = "
2043 SELECT civicrm_contact.id as casemanager_id,
2044        civicrm_contact.sort_name as casemanager
2045  FROM civicrm_contact
2046  LEFT JOIN civicrm_relationship ON (civicrm_relationship.contact_id_b = civicrm_contact.id AND civicrm_relationship.relationship_type_id = %1)
2047  LEFT JOIN civicrm_case ON civicrm_case.id = civicrm_relationship.case_id
2048  WHERE civicrm_case.id = %2";
2049
2050       $managerRoleParams = array(
2051         1 => array($managerRoleId, 'Integer'),
2052         2 => array($caseId, 'Integer'),
2053       );
2054
2055       $dao = CRM_Core_DAO::executeQuery($managerRoleQuery, $managerRoleParams);
2056       if ($dao->fetch()) {
2057         $caseManagerContact['casemanager_id'] = $dao->casemanager_id;
2058         $caseManagerContact['casemanager'] = $dao->casemanager;
2059       }
2060     }
2061
2062     return $caseManagerContact;
2063   }
2064
2065   /**
2066    * Get all cases with no end dates.
2067    *
2068    * @param array $params
2069    * @param array $excludeCaseIds
2070    * @param bool $excludeDeleted
2071    *
2072    * @param bool $includeClosed
2073    *
2074    * @return array
2075    *   Case and related data keyed on case id
2076    */
2077   public static function getUnclosedCases($params = array(), $excludeCaseIds = array(), $excludeDeleted = TRUE, $includeClosed = FALSE) {
2078     //params from ajax call.
2079     $where = array($includeClosed ? '(1)' : '(ca.end_date is null)');
2080     if ($caseType = CRM_Utils_Array::value('case_type', $params)) {
2081       $where[] = "( civicrm_case_type.title LIKE '%$caseType%' )";
2082     }
2083     if ($sortName = CRM_Utils_Array::value('sort_name', $params)) {
2084       $config = CRM_Core_Config::singleton();
2085       $search = ($config->includeWildCardInName) ? "%$sortName%" : "$sortName%";
2086       $where[] = "( sort_name LIKE '$search' )";
2087     }
2088     if ($cid = CRM_Utils_Array::value('contact_id', $params)) {
2089       $where[] = " c.id = $cid ";
2090     }
2091     if (is_array($excludeCaseIds) &&
2092       !CRM_Utils_System::isNull($excludeCaseIds)
2093     ) {
2094       $where[] = ' ( ca.id NOT IN ( ' . implode(',', $excludeCaseIds) . ' ) ) ';
2095     }
2096     if ($excludeDeleted) {
2097       $where[] = ' ( ca.is_deleted = 0 OR ca.is_deleted IS NULL ) ';
2098     }
2099
2100     //filter for permissioned cases.
2101     $filterCases = array();
2102     $doFilterCases = FALSE;
2103     if (!CRM_Core_Permission::check('access all cases and activities')) {
2104       $doFilterCases = TRUE;
2105       $session = CRM_Core_Session::singleton();
2106       $filterCases = CRM_Case_BAO_Case::getCases(FALSE, $session->get('userID'));
2107     }
2108     $whereClause = implode(' AND ', $where);
2109
2110     $limitClause = '';
2111     if ($limit = CRM_Utils_Array::value('limit', $params)) {
2112       $limitClause = "LIMIT 0, $limit";
2113     }
2114
2115     $query = "
2116     SELECT  c.id as contact_id,
2117             c.sort_name,
2118             ca.id,
2119             ca.subject as case_subject,
2120             civicrm_case_type.title as case_type,
2121             ca.start_date as start_date,
2122             ca.end_date as end_date,
2123             ca.status_id
2124       FROM  civicrm_case ca INNER JOIN civicrm_case_contact cc ON ca.id=cc.case_id
2125  INNER JOIN  civicrm_contact c ON cc.contact_id=c.id
2126  INNER JOIN  civicrm_case_type ON ca.case_type_id = civicrm_case_type.id
2127      WHERE  {$whereClause}
2128   ORDER BY  c.sort_name, ca.end_date
2129             {$limitClause}
2130 ";
2131     $dao = CRM_Core_DAO::executeQuery($query);
2132     $statuses = CRM_Case_BAO_Case::buildOptions('status_id', 'create');
2133
2134     $unclosedCases = array();
2135     while ($dao->fetch()) {
2136       if ($doFilterCases && !array_key_exists($dao->id, $filterCases)) {
2137         continue;
2138       }
2139       $unclosedCases[$dao->id] = array(
2140         'sort_name' => $dao->sort_name,
2141         'case_type' => $dao->case_type,
2142         'contact_id' => $dao->contact_id,
2143         'start_date' => $dao->start_date,
2144         'end_date' => $dao->end_date,
2145         'case_subject' => $dao->case_subject,
2146         'case_status' => $statuses[$dao->status_id],
2147       );
2148     }
2149     $dao->free();
2150
2151     return $unclosedCases;
2152   }
2153
2154   /**
2155    * @param int $contactId
2156    * @param bool $excludeDeleted
2157    *
2158    * @return null|string
2159    */
2160   public static function caseCount($contactId = NULL, $excludeDeleted = TRUE) {
2161     $whereConditions = array();
2162     if ($excludeDeleted) {
2163       $whereConditions[] = "( civicrm_case.is_deleted = 0 OR civicrm_case.is_deleted IS NULL )";
2164     }
2165     if ($contactId) {
2166       $whereConditions[] = "civicrm_case_contact.contact_id = {$contactId}";
2167     }
2168     if (!CRM_Core_Permission::check('access all cases and activities')) {
2169       static $accessibleCaseIds;
2170       if (!is_array($accessibleCaseIds)) {
2171         $session = CRM_Core_Session::singleton();
2172         $accessibleCaseIds = array_keys(self::getCases(FALSE, $session->get('userID'), 'any'));
2173       }
2174       //no need of further processing.
2175       if (empty($accessibleCaseIds)) {
2176         return 0;
2177       }
2178       $whereConditions[] = "( civicrm_case.id in (" . implode(',', $accessibleCaseIds) . ") )";
2179     }
2180
2181     $whereClause = '';
2182     if (!empty($whereConditions)) {
2183       $whereClause = "WHERE " . implode(' AND ', $whereConditions);
2184     }
2185
2186     $query = "
2187    SELECT  count( civicrm_case.id )
2188      FROM  civicrm_case
2189 LEFT JOIN  civicrm_case_contact ON ( civicrm_case.id = civicrm_case_contact.case_id )
2190            {$whereClause}";
2191
2192     return CRM_Core_DAO::singleValueQuery($query);
2193   }
2194
2195   /**
2196    * Retrieve cases related to particular contact.
2197    *
2198    * @param int $contactId
2199    *   Contact id.
2200    * @param bool $excludeDeleted
2201    *   Do not include deleted cases.
2202    *
2203    * @return array
2204    */
2205   public static function getContactCases($contactId, $excludeDeleted = TRUE) {
2206     $cases = array();
2207     if (!$contactId) {
2208       return $cases;
2209     }
2210
2211     $whereClause = "civicrm_case_contact.contact_id = %1";
2212     if ($excludeDeleted) {
2213       $whereClause .= " AND ( civicrm_case.is_deleted = 0 OR civicrm_case.is_deleted IS NULL )";
2214     }
2215
2216     $query = "
2217     SELECT  civicrm_case.id, civicrm_case_type.title as case_type, civicrm_case.start_date
2218       FROM  civicrm_case
2219 INNER JOIN  civicrm_case_contact ON ( civicrm_case.id = civicrm_case_contact.case_id )
2220  LEFT JOIN  civicrm_case_type ON civicrm_case.case_type_id = civicrm_case_type.id
2221      WHERE  {$whereClause}";
2222
2223     $dao = CRM_Core_DAO::executeQuery($query, array(1 => array($contactId, 'Integer')));
2224     while ($dao->fetch()) {
2225       $cases[$dao->id] = array(
2226         'case_id' => $dao->id,
2227         'case_type' => $dao->case_type,
2228         'case_start_date' => $dao->start_date,
2229       );
2230     }
2231     $dao->free();
2232
2233     return $cases;
2234   }
2235
2236   /**
2237    * Retrieve related cases for give case.
2238    *
2239    * @param int $mainCaseId
2240    *   Id of main case.
2241    * @param int $contactId
2242    *   Id of contact.
2243    * @param bool $excludeDeleted
2244    *   Do not include deleted cases.
2245    *
2246    * @return array
2247    */
2248   public static function getRelatedCases($mainCaseId, $contactId, $excludeDeleted = TRUE) {
2249     //FIXME : do check for permissions.
2250
2251     $relatedCases = array();
2252     if (!$mainCaseId || !$contactId) {
2253       return $relatedCases;
2254     }
2255
2256     $linkActType = array_search('Link Cases',
2257       CRM_Core_PseudoConstant::activityType(TRUE, TRUE, FALSE, 'name')
2258     );
2259     if (!$linkActType) {
2260       return $relatedCases;
2261     }
2262
2263     $whereClause = "mainCase.id = %2";
2264     if ($excludeDeleted) {
2265       $whereClause .= " AND ( relAct.is_deleted = 0 OR relAct.is_deleted IS NULL )";
2266     }
2267
2268     //1. first fetch related case ids.
2269     $query = "
2270     SELECT  relCaseAct.case_id
2271       FROM  civicrm_case mainCase
2272  INNER JOIN  civicrm_case_activity mainCaseAct ON (mainCaseAct.case_id = mainCase.id)
2273  INNER JOIN  civicrm_activity mainAct          ON (mainCaseAct.activity_id = mainAct.id AND mainAct.activity_type_id = %1)
2274  INNER JOIN  civicrm_case_activity relCaseAct  ON (relCaseAct.activity_id = mainAct.id AND mainCaseAct.id !=  relCaseAct.id)
2275  INNER JOIN  civicrm_activity relAct           ON (relCaseAct.activity_id = relAct.id  AND relAct.activity_type_id = %1)
2276      WHERE  $whereClause";
2277
2278     $dao = CRM_Core_DAO::executeQuery($query, array(
2279       1 => array($linkActType, 'Integer'),
2280       2 => array($mainCaseId, 'Integer'),
2281     ));
2282     $relatedCaseIds = array();
2283     while ($dao->fetch()) {
2284       $relatedCaseIds[$dao->case_id] = $dao->case_id;
2285     }
2286     $dao->free();
2287
2288     // there are no related cases.
2289     if (empty($relatedCaseIds)) {
2290       return $relatedCases;
2291     }
2292
2293     $whereClause = 'relCase.id IN ( ' . implode(',', $relatedCaseIds) . ' )';
2294     if ($excludeDeleted) {
2295       $whereClause .= " AND ( relCase.is_deleted = 0 OR relCase.is_deleted IS NULL )";
2296     }
2297
2298     //filter for permissioned cases.
2299     $filterCases = array();
2300     $doFilterCases = FALSE;
2301     if (!CRM_Core_Permission::check('access all cases and activities')) {
2302       $doFilterCases = TRUE;
2303       $session = CRM_Core_Session::singleton();
2304       $filterCases = CRM_Case_BAO_Case::getCases(FALSE, $session->get('userID'));
2305     }
2306
2307     //2. fetch the details of related cases.
2308     $query = "
2309     SELECT  relCase.id as id,
2310             civicrm_case_type.title as case_type,
2311             client.display_name as client_name,
2312             client.id as client_id
2313       FROM  civicrm_case relCase
2314  INNER JOIN  civicrm_case_contact relCaseContact ON ( relCase.id = relCaseContact.case_id )
2315  INNER JOIN  civicrm_contact      client         ON ( client.id = relCaseContact.contact_id )
2316  LEFT JOIN  civicrm_case_type ON relCase.case_type_id = civicrm_case_type.id
2317      WHERE  {$whereClause}";
2318
2319     $dao = CRM_Core_DAO::executeQuery($query);
2320     $contactViewUrl = CRM_Utils_System::url("civicrm/contact/view", "reset=1&cid=");
2321     $hasViewContact = CRM_Core_Permission::giveMeAllACLs();
2322
2323     while ($dao->fetch()) {
2324       $caseView = NULL;
2325       if (!$doFilterCases || array_key_exists($dao->id, $filterCases)) {
2326         $caseViewStr = "reset=1&id={$dao->id}&cid={$dao->client_id}&action=view&context=case&selectedChild=case";
2327         $caseViewUrl = CRM_Utils_System::url("civicrm/contact/view/case", $caseViewStr);
2328         $caseView = "<a class='action-item no-popup crm-hover-button' href='{$caseViewUrl}'>" . ts('View Case') . "</a>";
2329       }
2330       $clientView = $dao->client_name;
2331       if ($hasViewContact) {
2332         $clientView = "<a href='{$contactViewUrl}{$dao->client_id}'>$dao->client_name</a>";
2333       }
2334
2335       $relatedCases[$dao->id] = array(
2336         'case_id' => $dao->id,
2337         'case_type' => $dao->case_type,
2338         'client_name' => $clientView,
2339         'links' => $caseView,
2340       );
2341     }
2342     $dao->free();
2343
2344     return $relatedCases;
2345   }
2346
2347   /**
2348    * Merge two duplicate contacts' cases - follow CRM-5758 rules.
2349    *
2350    * @see CRM_Dedupe_Merger::cpTables()
2351    *
2352    * TODO: use the 3rd $sqls param to append sql statements rather than executing them here
2353    *
2354    * @param int $mainContactId
2355    * @param int $otherContactId
2356    */
2357   public static function mergeContacts($mainContactId, $otherContactId) {
2358     self::mergeCases($mainContactId, NULL, $otherContactId);
2359   }
2360
2361   /**
2362    * Function perform two task.
2363    * 1. Merge two duplicate contacts cases - follow CRM-5758 rules.
2364    * 2. Merge two cases of same contact - follow CRM-5598 rules.
2365    *
2366    * @param int $mainContactId
2367    *   Contact id of main contact record.
2368    * @param int $mainCaseId
2369    *   Case id of main case record.
2370    * @param int $otherContactId
2371    *   Contact id of record which is going to merge.
2372    * @param int $otherCaseId
2373    *   Case id of record which is going to merge.
2374    *
2375    * @param bool $changeClient
2376    *
2377    * @return int|NULL
2378    */
2379   public static function mergeCases(
2380     $mainContactId, $mainCaseId = NULL, $otherContactId = NULL,
2381     $otherCaseId = NULL, $changeClient = FALSE) {
2382     $moveToTrash = TRUE;
2383
2384     $duplicateContacts = FALSE;
2385     if ($mainContactId && $otherContactId &&
2386       $mainContactId != $otherContactId
2387     ) {
2388       $duplicateContacts = TRUE;
2389     }
2390
2391     $duplicateCases = FALSE;
2392     if ($mainCaseId && $otherCaseId &&
2393       $mainCaseId != $otherCaseId
2394     ) {
2395       $duplicateCases = TRUE;
2396     }
2397
2398     $mainCaseIds = array();
2399     if (!$duplicateContacts && !$duplicateCases) {
2400       return $mainCaseIds;
2401     }
2402
2403     $activityTypes = CRM_Core_PseudoConstant::activityType(TRUE, TRUE, FALSE, 'name');
2404     $activityStatuses = CRM_Core_PseudoConstant::activityStatus('name');
2405     $activityContacts = CRM_Core_OptionGroup::values('activity_contacts', FALSE, FALSE, FALSE, NULL, 'name');
2406     $sourceID = CRM_Utils_Array::key('Activity Source', $activityContacts);
2407     $assigneeID = CRM_Utils_Array::key('Activity Assignees', $activityContacts);
2408     $targetID = CRM_Utils_Array::key('Activity Targets', $activityContacts);
2409
2410     $processCaseIds = array($otherCaseId);
2411     if ($duplicateContacts && !$duplicateCases) {
2412       if ($changeClient) {
2413         $processCaseIds = array($mainCaseId);
2414       }
2415       else {
2416         //get all case ids for other contact.
2417         $processCaseIds = self::retrieveCaseIdsByContactId($otherContactId, TRUE);
2418       }
2419       if (!is_array($processCaseIds)) {
2420         return;
2421       }
2422     }
2423
2424     $session = CRM_Core_Session::singleton();
2425     $currentUserId = $session->get('userID');
2426
2427     CRM_Utils_Hook::pre_case_merge($mainContactId, $mainCaseId, $otherContactId, $otherCaseId, $changeClient);
2428
2429     // copy all cases and connect to main contact id.
2430     foreach ($processCaseIds as $otherCaseId) {
2431       if ($duplicateContacts) {
2432         $mainCase = CRM_Core_DAO::copyGeneric('CRM_Case_DAO_Case', array('id' => $otherCaseId));
2433         $mainCaseId = $mainCase->id;
2434         if (!$mainCaseId) {
2435           continue;
2436         }
2437
2438         // CRM-11662 Copy Case custom data
2439         $extends = array('case');
2440         $groupTree = CRM_Core_BAO_CustomGroup::getGroupDetail(NULL, NULL, $extends);
2441         if ($groupTree) {
2442           foreach ($groupTree as $groupID => $group) {
2443             $table[$groupTree[$groupID]['table_name']] = array('entity_id');
2444             foreach ($group['fields'] as $fieldID => $field) {
2445               $table[$groupTree[$groupID]['table_name']][] = $groupTree[$groupID]['fields'][$fieldID]['column_name'];
2446             }
2447           }
2448
2449           foreach ($table as $tableName => $tableColumns) {
2450             $insert = 'INSERT INTO ' . $tableName . ' (' . implode(', ', $tableColumns) . ') ';
2451             $tableColumns[0] = $mainCaseId;
2452             $select = 'SELECT ' . implode(', ', $tableColumns);
2453             $from = ' FROM ' . $tableName;
2454             $where = " WHERE {$tableName}.entity_id = {$otherCaseId}";
2455             $query = $insert . $select . $from . $where;
2456             $dao = CRM_Core_DAO::executeQuery($query, CRM_Core_DAO::$_nullArray);
2457           }
2458         }
2459
2460         $mainCase->free();
2461
2462         $mainCaseIds[] = $mainCaseId;
2463         //insert record for case contact.
2464         $otherCaseContact = new CRM_Case_DAO_CaseContact();
2465         $otherCaseContact->case_id = $otherCaseId;
2466         $otherCaseContact->find();
2467         while ($otherCaseContact->fetch()) {
2468           $mainCaseContact = new CRM_Case_DAO_CaseContact();
2469           $mainCaseContact->case_id = $mainCaseId;
2470           $mainCaseContact->contact_id = $otherCaseContact->contact_id;
2471           if ($mainCaseContact->contact_id == $otherContactId) {
2472             $mainCaseContact->contact_id = $mainContactId;
2473           }
2474           //avoid duplicate object.
2475           if (!$mainCaseContact->find(TRUE)) {
2476             $mainCaseContact->save();
2477           }
2478           $mainCaseContact->free();
2479         }
2480         $otherCaseContact->free();
2481       }
2482       elseif (!$otherContactId) {
2483         $otherContactId = $mainContactId;
2484       }
2485
2486       if (!$mainCaseId || !$otherCaseId ||
2487         !$mainContactId || !$otherContactId
2488       ) {
2489         continue;
2490       }
2491
2492       // get all activities for other case.
2493       $otherCaseActivities = array();
2494       CRM_Core_DAO::commonRetrieveAll('CRM_Case_DAO_CaseActivity', 'case_id', $otherCaseId, $otherCaseActivities);
2495
2496       //for duplicate cases do not process singleton activities.
2497       $otherActivityIds = $singletonActivityIds = array();
2498       foreach ($otherCaseActivities as $caseActivityId => $otherIds) {
2499         $otherActId = CRM_Utils_Array::value('activity_id', $otherIds);
2500         if (!$otherActId || in_array($otherActId, $otherActivityIds)) {
2501           continue;
2502         }
2503         $otherActivityIds[] = $otherActId;
2504       }
2505       if ($duplicateCases) {
2506         if ($openCaseType = array_search('Open Case', $activityTypes)) {
2507           $sql = "
2508 SELECT  id
2509   FROM  civicrm_activity
2510  WHERE  activity_type_id = $openCaseType
2511    AND  id IN ( " . implode(',', array_values($otherActivityIds)) . ');';
2512           $dao = CRM_Core_DAO::executeQuery($sql);
2513           while ($dao->fetch()) {
2514             $singletonActivityIds[] = $dao->id;
2515           }
2516           $dao->free();
2517         }
2518       }
2519
2520       // migrate all activities and connect to main contact.
2521       $copiedActivityIds = $activityMappingIds = array();
2522       sort($otherActivityIds);
2523       foreach ($otherActivityIds as $otherActivityId) {
2524
2525         //for duplicate cases -
2526         //do not migrate singleton activities.
2527         if (!$otherActivityId || in_array($otherActivityId, $singletonActivityIds)) {
2528           continue;
2529         }
2530
2531         //migrate activity record.
2532         $otherActivity = new CRM_Activity_DAO_Activity();
2533         $otherActivity->id = $otherActivityId;
2534         if (!$otherActivity->find(TRUE)) {
2535           continue;
2536         }
2537
2538         $mainActVals = array();
2539         $mainActivity = new CRM_Activity_DAO_Activity();
2540         CRM_Core_DAO::storeValues($otherActivity, $mainActVals);
2541         $mainActivity->copyValues($mainActVals);
2542         $mainActivity->id = NULL;
2543         $mainActivity->activity_date_time = CRM_Utils_Date::isoToMysql($otherActivity->activity_date_time);
2544         $mainActivity->source_record_id = CRM_Utils_Array::value($mainActivity->source_record_id,
2545           $activityMappingIds
2546         );
2547
2548         $mainActivity->original_id = CRM_Utils_Array::value($mainActivity->original_id,
2549           $activityMappingIds
2550         );
2551
2552         $mainActivity->parent_id = CRM_Utils_Array::value($mainActivity->parent_id,
2553           $activityMappingIds
2554         );
2555         $mainActivity->save();
2556         $mainActivityId = $mainActivity->id;
2557         if (!$mainActivityId) {
2558           continue;
2559         }
2560
2561         $activityMappingIds[$otherActivityId] = $mainActivityId;
2562         // insert log of all activities
2563         CRM_Activity_BAO_Activity::logActivityAction($mainActivity);
2564
2565         $otherActivity->free();
2566         $mainActivity->free();
2567         $copiedActivityIds[] = $otherActivityId;
2568
2569         //create case activity record.
2570         $mainCaseActivity = new CRM_Case_DAO_CaseActivity();
2571         $mainCaseActivity->case_id = $mainCaseId;
2572         $mainCaseActivity->activity_id = $mainActivityId;
2573         $mainCaseActivity->save();
2574         $mainCaseActivity->free();
2575
2576         //migrate source activity.
2577         $otherSourceActivity = new CRM_Activity_DAO_ActivityContact();
2578         $otherSourceActivity->activity_id = $otherActivityId;
2579         $otherSourceActivity->record_type_id = $sourceID;
2580         $otherSourceActivity->find();
2581         while ($otherSourceActivity->fetch()) {
2582           $mainActivitySource = new CRM_Activity_DAO_ActivityContact();
2583           $mainActivitySource->record_type_id = $sourceID;
2584           $mainActivitySource->activity_id = $mainActivityId;
2585           $mainActivitySource->contact_id = $otherSourceActivity->contact_id;
2586           if ($mainActivitySource->contact_id == $otherContactId) {
2587             $mainActivitySource->contact_id = $mainContactId;
2588           }
2589           //avoid duplicate object.
2590           if (!$mainActivitySource->find(TRUE)) {
2591             $mainActivitySource->save();
2592           }
2593           $mainActivitySource->free();
2594         }
2595         $otherSourceActivity->free();
2596
2597         //migrate target activities.
2598         $otherTargetActivity = new CRM_Activity_DAO_ActivityContact();
2599         $otherTargetActivity->activity_id = $otherActivityId;
2600         $otherTargetActivity->record_type_id = $targetID;
2601         $otherTargetActivity->find();
2602         while ($otherTargetActivity->fetch()) {
2603           $mainActivityTarget = new CRM_Activity_DAO_ActivityContact();
2604           $mainActivityTarget->record_type_id = $targetID;
2605           $mainActivityTarget->activity_id = $mainActivityId;
2606           $mainActivityTarget->contact_id = $otherTargetActivity->contact_id;
2607           if ($mainActivityTarget->contact_id == $otherContactId) {
2608             $mainActivityTarget->contact_id = $mainContactId;
2609           }
2610           //avoid duplicate object.
2611           if (!$mainActivityTarget->find(TRUE)) {
2612             $mainActivityTarget->save();
2613           }
2614           $mainActivityTarget->free();
2615         }
2616         $otherTargetActivity->free();
2617
2618         //migrate assignee activities.
2619         $otherAssigneeActivity = new CRM_Activity_DAO_ActivityContact();
2620         $otherAssigneeActivity->activity_id = $otherActivityId;
2621         $otherAssigneeActivity->record_type_id = $assigneeID;
2622         $otherAssigneeActivity->find();
2623         while ($otherAssigneeActivity->fetch()) {
2624           $mainAssigneeActivity = new CRM_Activity_DAO_ActivityContact();
2625           $mainAssigneeActivity->activity_id = $mainActivityId;
2626           $mainAssigneeActivity->record_type_id = $assigneeID;
2627           $mainAssigneeActivity->contact_id = $otherAssigneeActivity->contact_id;
2628           if ($mainAssigneeActivity->contact_id == $otherContactId) {
2629             $mainAssigneeActivity->contact_id = $mainContactId;
2630           }
2631           //avoid duplicate object.
2632           if (!$mainAssigneeActivity->find(TRUE)) {
2633             $mainAssigneeActivity->save();
2634           }
2635           $mainAssigneeActivity->free();
2636         }
2637         $otherAssigneeActivity->free();
2638
2639         // copy custom fields and attachments
2640         $aparams = array(
2641           'activityID' => $otherActivityId,
2642           'mainActivityId' => $mainActivityId,
2643         );
2644         CRM_Activity_BAO_Activity::copyExtendedActivityData($aparams);
2645       }
2646
2647       //copy case relationship.
2648       if ($duplicateContacts) {
2649         //migrate relationship records.
2650         $otherRelationship = new CRM_Contact_DAO_Relationship();
2651         $otherRelationship->case_id = $otherCaseId;
2652         $otherRelationship->find();
2653         $otherRelationshipIds = array();
2654         while ($otherRelationship->fetch()) {
2655           $otherRelVals = array();
2656           $updateOtherRel = FALSE;
2657           CRM_Core_DAO::storeValues($otherRelationship, $otherRelVals);
2658
2659           $mainRelationship = new CRM_Contact_DAO_Relationship();
2660           $mainRelationship->copyValues($otherRelVals);
2661           $mainRelationship->id = NULL;
2662           $mainRelationship->case_id = $mainCaseId;
2663           if ($mainRelationship->contact_id_a == $otherContactId) {
2664             $updateOtherRel = TRUE;
2665             $mainRelationship->contact_id_a = $mainContactId;
2666           }
2667
2668           //case creator change only when we merge user contact.
2669           if ($mainRelationship->contact_id_b == $otherContactId) {
2670             //do not change creator for change client.
2671             if (!$changeClient) {
2672               $updateOtherRel = TRUE;
2673               $mainRelationship->contact_id_b = ($currentUserId) ? $currentUserId : $mainContactId;
2674             }
2675           }
2676           $mainRelationship->end_date = CRM_Utils_Date::isoToMysql($otherRelationship->end_date);
2677           $mainRelationship->start_date = CRM_Utils_Date::isoToMysql($otherRelationship->start_date);
2678
2679           //avoid duplicate object.
2680           if (!$mainRelationship->find(TRUE)) {
2681             $mainRelationship->save();
2682           }
2683           $mainRelationship->free();
2684
2685           //get the other relationship ids to update end date.
2686           if ($updateOtherRel) {
2687             $otherRelationshipIds[$otherRelationship->id] = $otherRelationship->id;
2688           }
2689         }
2690         $otherRelationship->free();
2691
2692         //update other relationships end dates
2693         if (!empty($otherRelationshipIds)) {
2694           $sql = 'UPDATE  civicrm_relationship
2695                                SET  end_date = CURDATE()
2696                              WHERE  id IN ( ' . implode(',', $otherRelationshipIds) . ')';
2697           CRM_Core_DAO::executeQuery($sql);
2698         }
2699       }
2700
2701       //move other case to trash.
2702       $mergeCase = self::deleteCase($otherCaseId, $moveToTrash);
2703       if (!$mergeCase) {
2704         continue;
2705       }
2706
2707       $mergeActSubject = $mergeActSubjectDetails = $mergeActType = '';
2708       if ($changeClient) {
2709         $mainContactDisplayName = CRM_Contact_BAO_Contact::displayName($mainContactId);
2710         $otherContactDisplayName = CRM_Contact_BAO_Contact::displayName($otherContactId);
2711
2712         $mergeActType = array_search('Reassigned Case', $activityTypes);
2713         $mergeActSubject = ts("Case %1 reassigned client from %2 to %3. New Case ID is %4.",
2714           array(
2715             1 => $otherCaseId,
2716             2 => $otherContactDisplayName,
2717             3 => $mainContactDisplayName,
2718             4 => $mainCaseId,
2719           )
2720         );
2721       }
2722       elseif ($duplicateContacts) {
2723         $mergeActType = array_search('Merge Case', $activityTypes);
2724         $mergeActSubject = ts("Case %1 copied from contact id %2 to contact id %3 via merge. New Case ID is %4.",
2725           array(
2726             1 => $otherCaseId,
2727             2 => $otherContactId,
2728             3 => $mainContactId,
2729             4 => $mainCaseId,
2730           )
2731         );
2732       }
2733       else {
2734         $mergeActType = array_search('Merge Case', $activityTypes);
2735         $mergeActSubject = ts("Case %1 merged into case %2", array(1 => $otherCaseId, 2 => $mainCaseId));
2736         if (!empty($copiedActivityIds)) {
2737           $sql = '
2738 SELECT id, subject, activity_date_time, activity_type_id
2739 FROM civicrm_activity
2740 WHERE id IN (' . implode(',', $copiedActivityIds) . ')';
2741           $dao = CRM_Core_DAO::executeQuery($sql);
2742           while ($dao->fetch()) {
2743             $mergeActSubjectDetails .= "{$dao->activity_date_time} :: {$activityTypes[$dao->activity_type_id]}";
2744             if ($dao->subject) {
2745               $mergeActSubjectDetails .= " :: {$dao->subject}";
2746             }
2747             $mergeActSubjectDetails .= "<br />";
2748           }
2749         }
2750       }
2751
2752       //Create merge activity record. Source for merge activity is the logged in user's contact ID ($currentUserId).
2753       $activityParams = array(
2754         'subject' => $mergeActSubject,
2755         'details' => $mergeActSubjectDetails,
2756         'status_id' => array_search('Completed', $activityStatuses),
2757         'activity_type_id' => $mergeActType,
2758         'source_contact_id' => $currentUserId,
2759         'activity_date_time' => date('YmdHis'),
2760       );
2761
2762       $mergeActivity = CRM_Activity_BAO_Activity::create($activityParams);
2763       $mergeActivityId = $mergeActivity->id;
2764       if (!$mergeActivityId) {
2765         continue;
2766       }
2767       $mergeActivity->free();
2768
2769       //connect merge activity to case.
2770       $mergeCaseAct = array(
2771         'case_id' => $mainCaseId,
2772         'activity_id' => $mergeActivityId,
2773       );
2774
2775       self::processCaseActivity($mergeCaseAct);
2776     }
2777
2778     CRM_Utils_Hook::post_case_merge($mainContactId, $mainCaseId, $otherContactId, $otherCaseId, $changeClient);
2779
2780     return $mainCaseIds;
2781   }
2782
2783   /**
2784    * Validate contact permission for
2785    * edit/view on activity record and build links.
2786    *
2787    * @param array $tplParams
2788    *   Params to be sent to template for sending email.
2789    * @param array $activityParams
2790    *   Info of the activity.
2791    *
2792    * @return void
2793    */
2794   public static function buildPermissionLinks(&$tplParams, $activityParams) {
2795     $activityTypeId = CRM_Core_DAO::getFieldValue('CRM_Activity_DAO_Activity', $activityParams['source_record_id'],
2796       'activity_type_id', 'id'
2797     );
2798
2799     if (!empty($tplParams['isCaseActivity'])) {
2800       $tplParams['editActURL'] = CRM_Utils_System::url('civicrm/case/activity',
2801         "reset=1&cid={$activityParams['target_id']}&caseid={$activityParams['case_id']}&action=update&id={$activityParams['source_record_id']}", TRUE
2802       );
2803
2804       $tplParams['viewActURL'] = CRM_Utils_System::url('civicrm/case/activity/view',
2805         "reset=1&aid={$activityParams['source_record_id']}&cid={$activityParams['target_id']}&caseID={$activityParams['case_id']}", TRUE
2806       );
2807
2808       $tplParams['manageCaseURL'] = CRM_Utils_System::url('civicrm/contact/view/case',
2809         "reset=1&id={$activityParams['case_id']}&cid={$activityParams['target_id']}&action=view&context=home", TRUE
2810       );
2811     }
2812     else {
2813       $tplParams['editActURL'] = CRM_Utils_System::url('civicrm/contact/view/activity',
2814         "atype=$activityTypeId&action=update&reset=1&id={$activityParams['source_record_id']}&cid={$tplParams['contact']['contact_id']}&context=activity", TRUE
2815       );
2816
2817       $tplParams['viewActURL'] = CRM_Utils_System::url('civicrm/contact/view/activity',
2818         "atype=$activityTypeId&action=view&reset=1&id={$activityParams['source_record_id']}&cid={$tplParams['contact']['contact_id']}&context=activity", TRUE
2819       );
2820     }
2821   }
2822
2823   /**
2824    * Validate contact permission for
2825    * given operation on activity record.
2826    *
2827    * @param int $activityId
2828    *   Activity record id.
2829    * @param string $operation
2830    *   User operation.
2831    * @param int $actTypeId
2832    *   Activity type id.
2833    * @param int $contactId
2834    *   Contact id/if not pass consider logged in.
2835    * @param bool $checkComponent
2836    *   Do we need to check component enabled.
2837    *
2838    * @return bool
2839    */
2840   public static function checkPermission($activityId, $operation, $actTypeId = NULL, $contactId = NULL, $checkComponent = TRUE) {
2841     $allow = FALSE;
2842     if (!$actTypeId && $activityId) {
2843       $actTypeId = CRM_Core_DAO::getFieldValue('CRM_Activity_DAO_Activity', $activityId, 'activity_type_id');
2844     }
2845
2846     if (!$activityId || !$operation || !$actTypeId) {
2847       return $allow;
2848     }
2849
2850     //do check for civicase component enabled.
2851     if ($checkComponent && !self::enabled()) {
2852       return $allow;
2853     }
2854
2855     //do check for cases.
2856     $caseActOperations = array(
2857       'File On Case',
2858       'Link Cases',
2859       'Move To Case',
2860       'Copy To Case',
2861     );
2862
2863     if (in_array($operation, $caseActOperations)) {
2864       static $unclosedCases;
2865       if (!is_array($unclosedCases)) {
2866         $unclosedCases = self::getUnclosedCases();
2867       }
2868       if ($operation == 'File On Case') {
2869         $allow = (empty($unclosedCases)) ? FALSE : TRUE;
2870       }
2871       else {
2872         $allow = (count($unclosedCases) > 1) ? TRUE : FALSE;
2873       }
2874     }
2875
2876     $actionOperations = array('view', 'edit', 'delete');
2877     if (in_array($operation, $actionOperations)) {
2878
2879       //do cache when user has non/supper permission.
2880       static $allowOperations;
2881
2882       if (!is_array($allowOperations) ||
2883         !array_key_exists($operation, $allowOperations)
2884       ) {
2885
2886         if (!$contactId) {
2887           $session = CRM_Core_Session::singleton();
2888           $contactId = $session->get('userID');
2889         }
2890
2891         //check for permissions.
2892         $permissions = array(
2893           'view' => array(
2894             'access my cases and activities',
2895             'access all cases and activities',
2896           ),
2897           'edit' => array(
2898             'access my cases and activities',
2899             'access all cases and activities',
2900           ),
2901           'delete' => array('delete activities'),
2902         );
2903
2904         //check for core permission.
2905         $hasPermissions = array();
2906         $checkPermissions = CRM_Utils_Array::value($operation, $permissions);
2907         if (is_array($checkPermissions)) {
2908           foreach ($checkPermissions as $per) {
2909             if (CRM_Core_Permission::check($per)) {
2910               $hasPermissions[$operation][] = $per;
2911             }
2912           }
2913         }
2914
2915         //has permissions.
2916         if (!empty($hasPermissions)) {
2917           //need to check activity object specific.
2918           if (in_array($operation, array(
2919             'view',
2920             'edit',
2921           ))
2922           ) {
2923             //do we have supper permission.
2924             if (in_array('access all cases and activities', $hasPermissions[$operation])) {
2925               $allowOperations[$operation] = $allow = TRUE;
2926             }
2927             else {
2928               //user has only access to my cases and activity.
2929               //here object specific permmions come in picture.
2930
2931               //edit - contact must be source or assignee
2932               //view - contact must be source/assignee/target
2933               $isTarget = $isAssignee = $isSource = FALSE;
2934               $activityContacts = CRM_Core_OptionGroup::values('activity_contacts', FALSE, FALSE, FALSE, NULL, 'name');
2935               $sourceID = CRM_Utils_Array::key('Activity Source', $activityContacts);
2936               $assigneeID = CRM_Utils_Array::key('Activity Assignees', $activityContacts);
2937               $targetID = CRM_Utils_Array::key('Activity Targets', $activityContacts);
2938
2939               $target = new CRM_Activity_DAO_ActivityContact();
2940               $target->record_type_id = $targetID;
2941               $target->activity_id = $activityId;
2942               $target->contact_id = $contactId;
2943               if ($target->find(TRUE)) {
2944                 $isTarget = TRUE;
2945               }
2946
2947               $assignee = new CRM_Activity_DAO_ActivityContact();
2948               $assignee->activity_id = $activityId;
2949               $assignee->record_type_id = $assigneeID;
2950               $assignee->contact_id = $contactId;
2951               if ($assignee->find(TRUE)) {
2952                 $isAssignee = TRUE;
2953               }
2954
2955               $source = new CRM_Activity_DAO_ActivityContact();
2956               $source->activity_id = $activityId;
2957               $source->record_type_id = $sourceID;
2958               $source->contact_id = $contactId;
2959               if ($source->find(TRUE)) {
2960                 $isSource = TRUE;
2961               }
2962
2963               if ($operation == 'edit') {
2964                 if ($isAssignee || $isSource) {
2965                   $allow = TRUE;
2966                 }
2967               }
2968               if ($operation == 'view') {
2969                 if ($isTarget || $isAssignee || $isSource) {
2970                   $allow = TRUE;
2971                 }
2972               }
2973             }
2974           }
2975           elseif (is_array($hasPermissions[$operation])) {
2976             $allowOperations[$operation] = $allow = TRUE;
2977           }
2978         }
2979         else {
2980           //contact do not have permission.
2981           $allowOperations[$operation] = FALSE;
2982         }
2983       }
2984       else {
2985         //use cache.
2986         //here contact might have supper/non permission.
2987         $allow = $allowOperations[$operation];
2988       }
2989     }
2990
2991     //do further only when operation is granted.
2992     if ($allow) {
2993       $activityTypes = CRM_Core_PseudoConstant::activityType(TRUE, TRUE, FALSE, 'name');
2994
2995       //get the activity type name.
2996       $actTypeName = CRM_Utils_Array::value($actTypeId, $activityTypes);
2997
2998       //do not allow multiple copy / edit action.
2999       $singletonNames = array(
3000         'Open Case',
3001         'Reassigned Case',
3002         'Merge Case',
3003         'Link Cases',
3004         'Assign Case Role',
3005         'Email',
3006         'Inbound Email',
3007       );
3008
3009       //do not allow to delete these activities, CRM-4543
3010       $doNotDeleteNames = array('Open Case', 'Change Case Type', 'Change Case Status', 'Change Case Start Date');
3011
3012       //allow edit operation.
3013       $allowEditNames = array('Open Case');
3014
3015       // do not allow File on Case
3016       $doNotFileNames = array(
3017         'Open Case',
3018         'Change Case Type',
3019         'Change Case Status',
3020         'Change Case Start Date',
3021         'Reassigned Case',
3022         'Merge Case',
3023         'Link Cases',
3024         'Assign Case Role',
3025       );
3026
3027       if (in_array($actTypeName, $singletonNames)) {
3028         $allow = FALSE;
3029         if ($operation == 'File On Case') {
3030           $allow = (in_array($actTypeName, $doNotFileNames)) ? FALSE : TRUE;
3031         }
3032         if (in_array($operation, $actionOperations)) {
3033           $allow = TRUE;
3034           if ($operation == 'edit') {
3035             $allow = (in_array($actTypeName, $allowEditNames)) ? TRUE : FALSE;
3036           }
3037           elseif ($operation == 'delete') {
3038             $allow = (in_array($actTypeName, $doNotDeleteNames)) ? FALSE : TRUE;
3039           }
3040         }
3041       }
3042       if ($allow && ($operation == 'delete') &&
3043         in_array($actTypeName, $doNotDeleteNames)
3044       ) {
3045         $allow = FALSE;
3046       }
3047
3048       if ($allow && ($operation == 'File On Case') &&
3049         in_array($actTypeName, $doNotFileNames)
3050       ) {
3051         $allow = FALSE;
3052       }
3053
3054       //check settings file for masking actions
3055       //on the basis the activity types
3056       //hide Edit link if activity type is NOT editable
3057       //(special case activities).CRM-5871
3058       if ($allow && in_array($operation, $actionOperations)) {
3059         static $actionFilter = array();
3060         if (!array_key_exists($operation, $actionFilter)) {
3061           $xmlProcessor = new CRM_Case_XMLProcessor_Process();
3062           $actionFilter[$operation] = $xmlProcessor->get('Settings', 'ActivityTypes', FALSE, $operation);
3063         }
3064         if (array_key_exists($operation, $actionFilter[$operation]) &&
3065           in_array($actTypeId, $actionFilter[$operation][$operation])
3066         ) {
3067           $allow = FALSE;
3068         }
3069       }
3070     }
3071
3072     return $allow;
3073   }
3074
3075   /**
3076    * Since we drop 'access CiviCase', allow access
3077    * if user has 'access my cases and activities'
3078    * or 'access all cases and activities'
3079    */
3080   public static function accessCiviCase() {
3081     if (!self::enabled()) {
3082       return FALSE;
3083     }
3084
3085     if (CRM_Core_Permission::check('access my cases and activities') ||
3086       CRM_Core_Permission::check('access all cases and activities')
3087     ) {
3088       return TRUE;
3089     }
3090
3091     return FALSE;
3092   }
3093
3094   /**
3095    * Verify user has permission to access a case.
3096    *
3097    * @param int $caseId
3098    * @param bool $denyClosed
3099    *   Set TRUE if one wants closed cases to be treated as inaccessible.
3100    *
3101    * @return bool
3102    */
3103   public static function accessCase($caseId, $denyClosed = TRUE) {
3104     if (!$caseId || !self::enabled()) {
3105       return FALSE;
3106     }
3107
3108     // This permission always has access
3109     if (CRM_Core_Permission::check('access all cases and activities')) {
3110       return TRUE;
3111     }
3112
3113     // This permission is required at minimum
3114     if (!CRM_Core_Permission::check('access my cases and activities')) {
3115       return FALSE;
3116     }
3117
3118     $session = CRM_Core_Session::singleton();
3119     $userID = CRM_Utils_Type::validate($session->get('userID'), 'Positive');
3120     $caseId = CRM_Utils_Type::validate($caseId, 'Positive');
3121
3122     $condition = " AND civicrm_case.is_deleted = 0 ";
3123     $condition .= " AND case_relationship.contact_id_b = {$userID} ";
3124     $condition .= " AND civicrm_case.id = {$caseId}";
3125
3126     if ($denyClosed) {
3127       $closedId = CRM_Core_OptionGroup::getValue('case_status', 'Closed', 'name');
3128       $condition .= " AND civicrm_case.status_id != $closedId";
3129     }
3130
3131     // We don't actually care about activities in the case, but the underlying
3132     // query is verbose, and this allows us to share the basic query with
3133     // getCases(). $type=='any' means that activities will be left-joined.
3134     $query = self::getCaseActivityQuery('any', $userID, $condition);
3135     $queryParams = array();
3136     $dao = CRM_Core_DAO::executeQuery($query,
3137       $queryParams
3138     );
3139
3140     return (bool) $dao->fetch();
3141   }
3142
3143   /**
3144    * Check whether activity is a case Activity.
3145    *
3146    * @param int $activityID
3147    *   Activity id.
3148    *
3149    * @return bool
3150    */
3151   public static function isCaseActivity($activityID) {
3152     $isCaseActivity = FALSE;
3153     if ($activityID) {
3154       $params = array(1 => array($activityID, 'Integer'));
3155       $query = "SELECT id FROM civicrm_case_activity WHERE activity_id = %1";
3156       if (CRM_Core_DAO::singleValueQuery($query, $params)) {
3157         $isCaseActivity = TRUE;
3158       }
3159     }
3160
3161     return $isCaseActivity;
3162   }
3163
3164   /**
3165    * Get all the case type ids currently in use.
3166    *
3167    * @return array
3168    */
3169   public static function getUsedCaseType() {
3170     static $caseTypeIds;
3171
3172     if (!is_array($caseTypeIds)) {
3173       $query = "SELECT DISTINCT( civicrm_case.case_type_id ) FROM civicrm_case";
3174
3175       $dao = CRM_Core_DAO::executeQuery($query);
3176       $caseTypeIds = array();
3177       while ($dao->fetch()) {
3178         $typeId = explode(CRM_Core_DAO::VALUE_SEPARATOR,
3179           $dao->case_type_id
3180         );
3181         $caseTypeIds[] = $typeId[1];
3182       }
3183     }
3184
3185     return $caseTypeIds;
3186   }
3187
3188   /**
3189    * Get all the case status ids currently in use.
3190    *
3191    * @return array
3192    */
3193   public static function getUsedCaseStatuses() {
3194     static $caseStatusIds;
3195
3196     if (!is_array($caseStatusIds)) {
3197       $query = "SELECT DISTINCT( civicrm_case.status_id ) FROM civicrm_case";
3198
3199       $dao = CRM_Core_DAO::executeQuery($query);
3200       $caseStatusIds = array();
3201       while ($dao->fetch()) {
3202         $caseStatusIds[] = $dao->status_id;
3203       }
3204     }
3205
3206     return $caseStatusIds;
3207   }
3208
3209   /**
3210    * Get all the encounter medium ids currently in use.
3211    *
3212    * @return array
3213    */
3214   public static function getUsedEncounterMediums() {
3215     static $mediumIds;
3216
3217     if (!is_array($mediumIds)) {
3218       $query = "SELECT DISTINCT( civicrm_activity.medium_id )  FROM civicrm_activity";
3219
3220       $dao = CRM_Core_DAO::executeQuery($query);
3221       $mediumIds = array();
3222       while ($dao->fetch()) {
3223         $mediumIds[] = $dao->medium_id;
3224       }
3225     }
3226
3227     return $mediumIds;
3228   }
3229
3230   /**
3231    * Check case configuration.
3232    *
3233    * @param int $contactId
3234    *
3235    * @return array
3236    */
3237   public static function isCaseConfigured($contactId = NULL) {
3238     $configured = array_fill_keys(array('configured', 'allowToAddNewCase', 'redirectToCaseAdmin'), FALSE);
3239
3240     //lets check for case configured.
3241     $allCasesCount = CRM_Case_BAO_Case::caseCount(NULL, FALSE);
3242     $configured['configured'] = ($allCasesCount) ? TRUE : FALSE;
3243     if (!$configured['configured']) {
3244       //do check for case type and case status.
3245       $caseTypes = CRM_Case_PseudoConstant::caseType('title', FALSE);
3246       if (!empty($caseTypes)) {
3247         $configured['configured'] = TRUE;
3248         if (!$configured['configured']) {
3249           $caseStatuses = CRM_Case_PseudoConstant::caseStatus('label', FALSE);
3250           if (!empty($caseStatuses)) {
3251             $configured['configured'] = TRUE;
3252           }
3253         }
3254       }
3255     }
3256     if ($configured['configured']) {
3257       //do check for active case type and case status.
3258       $caseTypes = CRM_Case_PseudoConstant::caseType();
3259       if (!empty($caseTypes)) {
3260         $caseStatuses = CRM_Case_PseudoConstant::caseStatus();
3261         if (!empty($caseStatuses)) {
3262           $configured['allowToAddNewCase'] = TRUE;
3263         }
3264       }
3265
3266       //do we need to redirect user to case admin.
3267       if (!$configured['allowToAddNewCase'] && $contactId) {
3268         //check for current contact case count.
3269         $currentContatCasesCount = CRM_Case_BAO_Case::caseCount($contactId);
3270         //redirect user to case admin page.
3271         if (!$currentContatCasesCount) {
3272           $configured['redirectToCaseAdmin'] = TRUE;
3273         }
3274       }
3275     }
3276
3277     return $configured;
3278   }
3279
3280   /**
3281    * Used during case component enablement and during ugprade.
3282    *
3283    * @return bool
3284    */
3285   public static function createCaseViews() {
3286     $errorScope = CRM_Core_TemporaryErrorScope::ignoreException();
3287     $dao = new CRM_Core_DAO();
3288
3289     $sql = self::createCaseViewsQuery('upcoming');
3290     $dao->query($sql);
3291     if (PEAR::getStaticProperty('DB_DataObject', 'lastError')) {
3292       return FALSE;
3293     }
3294
3295     // Above error doesn't get caught?
3296     $doublecheck = $dao->singleValueQuery("SELECT count(id) FROM civicrm_view_case_activity_upcoming");
3297     if (is_null($doublecheck)) {
3298       return FALSE;
3299     }
3300
3301     $sql = self::createCaseViewsQuery('recent');
3302     $dao->query($sql);
3303     if (PEAR::getStaticProperty('DB_DataObject', 'lastError')) {
3304       return FALSE;
3305     }
3306
3307     // Above error doesn't get caught?
3308     $doublecheck = $dao->singleValueQuery("SELECT count(id) FROM civicrm_view_case_activity_recent");
3309     if (is_null($doublecheck)) {
3310       return FALSE;
3311     }
3312
3313     return TRUE;
3314   }
3315
3316   /**
3317    * Helper function, also used by the upgrade in case of error
3318    *
3319    * @param string $section
3320    *
3321    * @return string
3322    */
3323   public static function createCaseViewsQuery($section = 'upcoming') {
3324     $sql = "";
3325     $scheduled_id = CRM_Core_OptionGroup::getValue('activity_status', 'Scheduled', 'name');
3326     switch ($section) {
3327       case 'upcoming':
3328         $sql = "CREATE OR REPLACE VIEW `civicrm_view_case_activity_upcoming`
3329  AS SELECT ca.case_id, a.id, a.activity_date_time, a.status_id, a.activity_type_id
3330  FROM civicrm_case_activity ca
3331  INNER JOIN civicrm_activity a ON ca.activity_id=a.id
3332  WHERE a.activity_date_time <= DATE_ADD( NOW(), INTERVAL 14 DAY )
3333  AND a.is_current_revision = 1 AND a.is_deleted=0 AND a.status_id = $scheduled_id";
3334         break;
3335
3336       case 'recent':
3337         $sql = "CREATE OR REPLACE VIEW `civicrm_view_case_activity_recent`
3338  AS SELECT ca.case_id, a.id, a.activity_date_time, a.status_id, a.activity_type_id
3339  FROM civicrm_case_activity ca
3340  INNER JOIN civicrm_activity a ON ca.activity_id=a.id
3341  WHERE a.activity_date_time <= NOW()
3342  AND a.activity_date_time >= DATE_SUB( NOW(), INTERVAL 14 DAY )
3343  AND a.is_current_revision = 1 AND a.is_deleted=0 AND a.status_id <> $scheduled_id";
3344         break;
3345     }
3346     return $sql;
3347   }
3348
3349   /**
3350    * Add/copy relationships, when new client is added for a case
3351    *
3352    * @param int $caseId
3353    *   Case id.
3354    * @param int $contactId
3355    *   Contact id / new client id.
3356    *
3357    * @return void
3358    */
3359   public static function addCaseRelationships($caseId, $contactId) {
3360     // get the case role / relationships for the case
3361     $caseRelationships = new CRM_Contact_DAO_Relationship();
3362     $caseRelationships->case_id = $caseId;
3363     $caseRelationships->find();
3364     $relationshipTypes = array();
3365
3366     // make sure we don't add duplicate relationships of same relationship type.
3367     while ($caseRelationships->fetch() && !in_array($caseRelationships->relationship_type_id, $relationshipTypes)) {
3368       $values = array();
3369       CRM_Core_DAO::storeValues($caseRelationships, $values);
3370
3371       // add relationship for new client.
3372       $newRelationship = new CRM_Contact_DAO_Relationship();
3373       $newRelationship->copyValues($values);
3374       $newRelationship->id = NULL;
3375       $newRelationship->case_id = $caseId;
3376       $newRelationship->contact_id_a = $contactId;
3377       $newRelationship->end_date = CRM_Utils_Date::isoToMysql($caseRelationships->end_date);
3378       $newRelationship->start_date = CRM_Utils_Date::isoToMysql($caseRelationships->start_date);
3379
3380       // another check to avoid duplicate relationship, in cases where client is removed and re-added again.
3381       if (!$newRelationship->find(TRUE)) {
3382         $newRelationship->save();
3383       }
3384       $newRelationship->free();
3385
3386       // store relationship type of newly created relationship
3387       $relationshipTypes[] = $caseRelationships->relationship_type_id;
3388     }
3389   }
3390
3391   /**
3392    * Get the list of clients for a case.
3393    *
3394    * @param int $caseId
3395    *
3396    * @return array
3397    *   associated array with client ids
3398    */
3399   public static function getCaseClients($caseId) {
3400     $clients = array();
3401     $caseContact = new CRM_Case_DAO_CaseContact();
3402     $caseContact->case_id = $caseId;
3403     $caseContact->find();
3404
3405     while ($caseContact->fetch()) {
3406       $clients[] = $caseContact->contact_id;
3407     }
3408
3409     return $clients;
3410   }
3411
3412   /**
3413    * Get options for a given case field.
3414    * @see CRM_Core_DAO::buildOptions
3415    *
3416    * @param string $fieldName
3417    * @param string $context
3418    * @see CRM_Core_DAO::buildOptionsContext
3419    * @param array $props
3420    *   Whatever is known about this dao object.
3421    *
3422    * @return array|bool
3423    */
3424   public static function buildOptions($fieldName, $context = NULL, $props = array()) {
3425     $className = __CLASS__;
3426     $params = array();
3427     switch ($fieldName) {
3428       // This field is not part of this object but the api supports it
3429       case 'medium_id':
3430         $className = 'CRM_Activity_BAO_Activity';
3431         break;
3432     }
3433     return CRM_Core_PseudoConstant::get($className, $fieldName, $params, $context);
3434   }
3435
3436 }