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