CRM/Case/BAO/Case.php

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