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