CRM/Case/BAO/Case.php

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