CRM/Case/BAO/Case.php

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