CRM/Case/BAO/Case.php

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