CRM/Case/BAO/Case.php

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