CRM/Case/BAO/Case.php

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