CRM/Case/BAO/Case.php

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