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