Case.php 112 KB
Newer Older
totten's avatar
totten committed
1 2 3
<?php
/*
 +--------------------------------------------------------------------+
totten's avatar
totten committed
4
 | CiviCRM version 5                                                  |
totten's avatar
totten committed
5
 +--------------------------------------------------------------------+
yashodha's avatar
yashodha committed
6
 | Copyright CiviCRM LLC (c) 2004-2018                                |
totten's avatar
totten committed
7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25
 +--------------------------------------------------------------------+
 | This file is a part of CiviCRM.                                    |
 |                                                                    |
 | CiviCRM is free software; you can copy, modify, and distribute it  |
 | under the terms of the GNU Affero General Public License           |
 | Version 3, 19 November 2007 and the CiviCRM Licensing Exception.   |
 |                                                                    |
 | CiviCRM is distributed in the hope that it will be useful, but     |
 | WITHOUT ANY WARRANTY; without even the implied warranty of         |
 | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.               |
 | See the GNU Affero General Public License for more details.        |
 |                                                                    |
 | You should have received a copy of the GNU Affero General Public   |
 | License and the CiviCRM Licensing Exception along                  |
 | with this program; if not, contact CiviCRM LLC                     |
 | at info[AT]civicrm[DOT]org. If you have questions about the        |
 | GNU Affero General Public License or the licensing of CiviCRM,     |
 | see the CiviCRM license FAQ at http://civicrm.org/licensing        |
 +--------------------------------------------------------------------+
26
 */
totten's avatar
totten committed
27 28 29 30

/**
 *
 * @package CRM
yashodha's avatar
yashodha committed
31
 * @copyright CiviCRM LLC (c) 2004-2018
totten's avatar
totten committed
32 33 34
 */

/**
Eileen McNaughton's avatar
Eileen McNaughton committed
35
 * This class contains the functions for Case Management.
totten's avatar
totten committed
36 37 38 39
 */
class CRM_Case_BAO_Case extends CRM_Case_DAO_Case {

  /**
Eileen McNaughton's avatar
Eileen McNaughton committed
40
   * Static field for all the case information that we can potentially export.
totten's avatar
totten committed
41 42 43 44
   *
   * @var array
   */
  static $_exportableFields = NULL;
yashodha's avatar
yashodha committed
45

46
  /**
Eileen McNaughton's avatar
Eileen McNaughton committed
47
   * Class constructor.
48
   */
49
  public function __construct() {
totten's avatar
totten committed
50 51 52 53
    parent::__construct();
  }

  /**
colemanw's avatar
colemanw committed
54 55 56 57
   * Is CiviCase enabled?
   *
   * @return bool
   */
58
  public static function enabled() {
colemanw's avatar
colemanw committed
59 60 61 62
    $config = CRM_Core_Config::singleton();
    return in_array('CiviCase', $config->enableComponents);
  }

totten's avatar
totten committed
63
  /**
Eileen McNaughton's avatar
Eileen McNaughton committed
64
   * Create a case object.
totten's avatar
totten committed
65
   *
Eileen McNaughton's avatar
Eileen McNaughton committed
66
   * The function extracts all the params it needs to initialize the create a
totten's avatar
totten committed
67 68 69
   * case object. the params array could contain additional unused name/value
   * pairs
   *
70 71
   * @param array $params
   *   (reference ) an assoc array of name/value pairs.
72
   *
73
   * @return CRM_Case_BAO_Case
totten's avatar
totten committed
74
   */
75
  public static function add(&$params) {
totten's avatar
totten committed
76 77
    $caseDAO = new CRM_Case_DAO_Case();
    $caseDAO->copyValues($params);
78 79 80
    $result = $caseDAO->save();
    $caseDAO->find(TRUE); // Get other case values (required by XML processor), this adds to $result array
    return $result;
totten's avatar
totten committed
81 82 83
  }

  /**
Eileen McNaughton's avatar
Eileen McNaughton committed
84
   * Takes an associative array and creates a case object.
totten's avatar
totten committed
85
   *
86
   * @param array $params
Eileen McNaughton's avatar
Eileen McNaughton committed
87
   *   (reference) an assoc array of name/value pairs.
88
   *
89
   * @return CRM_Case_BAO_Case
totten's avatar
totten committed
90
   */
91
  public static function &create(&$params) {
92 93 94
    // CRM-20958 - These fields are managed by MySQL triggers. Watch out for clients resaving stale timestamps.
    unset($params['created_date']);
    unset($params['modified_date']);
95 96 97 98 99
    $caseStatus = CRM_Case_PseudoConstant::caseStatus('name');
    // for resolved case the end date should set to now
    if (!empty($params['status_id']) && $params['status_id'] == array_search('Closed', $caseStatus)) {
      $params['end_date'] = date("Ymd");
    }
100

totten's avatar
totten committed
101 102
    $transaction = new CRM_Core_Transaction();

103
    if (!empty($params['id'])) {
totten's avatar
totten committed
104 105 106 107 108 109 110 111
      CRM_Utils_Hook::pre('edit', 'Case', $params['id'], $params);
    }
    else {
      CRM_Utils_Hook::pre('create', 'Case', NULL, $params);
    }

    $case = self::add($params);

112
    if (!empty($params['custom']) &&
totten's avatar
totten committed
113 114 115 116 117 118 119 120 121 122
      is_array($params['custom'])
    ) {
      CRM_Core_BAO_CustomValueTable::store($params['custom'], 'civicrm_case', $case->id);
    }

    if (is_a($case, 'CRM_Core_Error')) {
      $transaction->rollback();
      return $case;
    }

123
    if (!empty($params['id'])) {
totten's avatar
totten committed
124 125 126 127 128 129 130 131 132 133 134 135 136
      CRM_Utils_Hook::post('edit', 'Case', $case->id, $case);
    }
    else {
      CRM_Utils_Hook::post('create', 'Case', $case->id, $case);
    }
    $transaction->commit();

    //we are not creating log for case
    //since case log can be tracked using log for activity.
    return $case;
  }

  /**
colemanw's avatar
colemanw committed
137
   * Process case activity add/delete
totten's avatar
totten committed
138 139
   * takes an associative array and
   *
140 141
   * @param array $params
   *   (reference ) an assoc array of name/value pairs.
totten's avatar
totten committed
142 143
   *
   */
144
  public static function processCaseActivity(&$params) {
totten's avatar
totten committed
145 146 147 148 149 150 151 152 153
    $caseActivityDAO = new CRM_Case_DAO_CaseActivity();
    $caseActivityDAO->activity_id = $params['activity_id'];
    $caseActivityDAO->case_id = $params['case_id'];

    $caseActivityDAO->find(TRUE);
    $caseActivityDAO->save();
  }

  /**
Eileen McNaughton's avatar
Eileen McNaughton committed
154
   * Get the case subject for Activity.
totten's avatar
totten committed
155
   *
156 157
   * @param int $activityId
   *   Activity id.
totten's avatar
totten committed
158
   *
colemanw's avatar
colemanw committed
159
   * @return string|null
totten's avatar
totten committed
160
   */
161
  public static function getCaseSubject($activityId) {
totten's avatar
totten committed
162 163 164 165 166 167 168 169 170
    $caseActivity = new CRM_Case_DAO_CaseActivity();
    $caseActivity->activity_id = $activityId;
    if ($caseActivity->find(TRUE)) {
      return CRM_Core_DAO::getFieldValue('CRM_Case_BAO_Case', $caseActivity->case_id, 'subject');
    }
    return NULL;
  }

  /**
colemanw's avatar
colemanw committed
171
   * Get the case type.
totten's avatar
totten committed
172 173
   *
   * @param int $caseId
174 175
   * @param string $colName
   *
176 177
   * @return string
   *   case type
totten's avatar
totten committed
178
   */
179
  public static function getCaseType($caseId, $colName = 'title') {
Adam Roses Wight's avatar
Adam Roses Wight committed
180 181 182 183 184
    $query = "
SELECT  civicrm_case_type.{$colName} FROM civicrm_case
LEFT JOIN civicrm_case_type ON
  civicrm_case.case_type_id = civicrm_case_type.id
WHERE civicrm_case.id = %1";
totten's avatar
totten committed
185

Adam Roses Wight's avatar
Adam Roses Wight committed
186
    $queryParams = array(1 => array($caseId, 'Integer'));
totten's avatar
totten committed
187

Adam Roses Wight's avatar
Adam Roses Wight committed
188
    return CRM_Core_DAO::singleValueQuery($query, $queryParams);
totten's avatar
totten committed
189 190 191
  }

  /**
192
   * Delete the record that are associated with this case.
totten's avatar
totten committed
193 194
   * record are deleted from case
   *
195 196
   * @param int $caseId
   *   Id of the case to delete.
totten's avatar
totten committed
197
   *
198 199
   * @param bool $moveToTrash
   *
200 201
   * @return bool
   *   is successful
totten's avatar
totten committed
202
   */
203
  public static function deleteCase($caseId, $moveToTrash = FALSE) {
totten's avatar
totten committed
204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246
    CRM_Utils_Hook::pre('delete', 'Case', $caseId, CRM_Core_DAO::$_nullArray);

    //delete activities
    $activities = self::getCaseActivityDates($caseId);
    if ($activities) {
      foreach ($activities as $value) {
        CRM_Activity_BAO_Activity::deleteActivity($value, $moveToTrash);
      }
    }

    if (!$moveToTrash) {
      $transaction = new CRM_Core_Transaction();
    }
    $case = new CRM_Case_DAO_Case();
    $case->id = $caseId;
    if (!$moveToTrash) {
      $result = $case->delete();
      $transaction->commit();
    }
    else {
      $result = $case->is_deleted = 1;
      $case->save();
    }

    if ($result) {
      // CRM-7364, disable relationships
      self::enableDisableCaseRelationships($caseId, FALSE);

      CRM_Utils_Hook::post('delete', 'Case', $caseId, $case);

      // remove case from recent items.
      $caseRecent = array(
        'id' => $caseId,
        'type' => 'Case',
      );
      CRM_Utils_Recent::del($caseRecent);
      return TRUE;
    }

    return FALSE;
  }

  /**
Eileen McNaughton's avatar
Eileen McNaughton committed
247
   * Enable disable case related relationships.
totten's avatar
totten committed
248
   *
249 250 251 252
   * @param int $caseId
   *   Case id.
   * @param bool $enable
   *   Action.
totten's avatar
totten committed
253
   */
254
  public static function enableDisableCaseRelationships($caseId, $enable) {
totten's avatar
totten committed
255 256 257 258 259 260
    $contactIds = self::retrieveContactIdsByCaseId($caseId);
    if (!empty($contactIds)) {
      foreach ($contactIds as $cid) {
        $roles = self::getCaseRoles($cid, $caseId);
        if (!empty($roles)) {
          $relationshipIds = implode(',', array_keys($roles));
yashodha's avatar
yashodha committed
261 262
          $enable = (int) $enable;
          $query = "UPDATE civicrm_relationship SET is_active = {$enable}
totten's avatar
totten committed
263 264 265 266 267 268 269 270
                        WHERE id IN ( {$relationshipIds} )";
          CRM_Core_DAO::executeQuery($query);
        }
      }
    }
  }

  /**
271
   * Retrieve contact_id by case_id.
totten's avatar
totten committed
272
   *
273 274
   * @param int $caseId
   *   ID of the case.
275
   *
colemanw's avatar
colemanw committed
276
   * @param int $contactID
277
   * @param int $startArrayAt This is to support legacy calls to Case.Get API which may rely on the first array index being set to 1
totten's avatar
totten committed
278 279 280
   *
   * @return array
   */
281
  public static function retrieveContactIdsByCaseId($caseId, $contactID = NULL, $startArrayAt = 0) {
totten's avatar
totten committed
282 283 284 285
    $caseContact = new CRM_Case_DAO_CaseContact();
    $caseContact->case_id = $caseId;
    $caseContact->find();
    $contactArray = array();
286
    $count = $startArrayAt;
totten's avatar
totten committed
287 288 289 290 291 292 293 294 295 296 297
    while ($caseContact->fetch()) {
      if ($contactID != $caseContact->contact_id) {
        $contactArray[$count] = $caseContact->contact_id;
        $count++;
      }
    }

    return $contactArray;
  }

  /**
Eileen McNaughton's avatar
Eileen McNaughton committed
298
   * Look up a case using an activity ID.
totten's avatar
totten committed
299
   *
colemanw's avatar
colemanw committed
300
   * @param int $activityId
301
   *
totten's avatar
totten committed
302 303
   * @return int, case ID
   */
304
  public static function getCaseIdByActivityId($activityId) {
totten's avatar
totten committed
305 306 307 308 309 310 311 312 313 314 315 316 317 318 319
    $originalId = CRM_Core_DAO::singleValueQuery(
      'SELECT original_id FROM civicrm_activity WHERE id = %1',
      array('1' => array($activityId, 'Integer'))
    );
    $caseId = CRM_Core_DAO::singleValueQuery(
      'SELECT case_id FROM civicrm_case_activity WHERE activity_id in (%1,%2)',
      array(
        '1' => array($activityId, 'Integer'),
        '2' => array($originalId ? $originalId : $activityId, 'Integer'),
      )
    );
    return $caseId;
  }

  /**
320
   * Retrieve contact names by caseId.
totten's avatar
totten committed
321
   *
322 323
   * @param int $caseId
   *   ID of the case.
totten's avatar
totten committed
324 325 326
   *
   * @return array
   */
327
  public static function getContactNames($caseId) {
totten's avatar
totten committed
328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343
    $contactNames = array();
    if (!$caseId) {
      return $contactNames;
    }

    $query = "
    SELECT  contact_a.sort_name name,
            contact_a.display_name as display_name,
            contact_a.id cid,
            contact_a.birth_date as birth_date,
            ce.email as email,
            cp.phone as phone
      FROM  civicrm_contact contact_a
 LEFT JOIN  civicrm_case_contact ON civicrm_case_contact.contact_id = contact_a.id
 LEFT JOIN  civicrm_email ce ON ( ce.contact_id = contact_a.id AND ce.is_primary = 1)
 LEFT JOIN  civicrm_phone cp ON ( cp.contact_id = contact_a.id AND cp.is_primary = 1)
344
     WHERE  contact_a.is_deleted = 0 AND civicrm_case_contact.case_id = %1
345
     ORDER BY civicrm_case_contact.id";
totten's avatar
totten committed
346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363

    $dao = CRM_Core_DAO::executeQuery($query,
      array(1 => array($caseId, 'Integer'))
    );
    while ($dao->fetch()) {
      $contactNames[$dao->cid]['contact_id'] = $dao->cid;
      $contactNames[$dao->cid]['sort_name'] = $dao->name;
      $contactNames[$dao->cid]['display_name'] = $dao->display_name;
      $contactNames[$dao->cid]['email'] = $dao->email;
      $contactNames[$dao->cid]['phone'] = $dao->phone;
      $contactNames[$dao->cid]['birth_date'] = $dao->birth_date;
      $contactNames[$dao->cid]['role'] = ts('Client');
    }

    return $contactNames;
  }

  /**
Eileen McNaughton's avatar
Eileen McNaughton committed
364
   * Retrieve case_id by contact_id.
totten's avatar
totten committed
365
   *
colemanw's avatar
colemanw committed
366
   * @param int $contactID
367 368
   * @param bool $includeDeleted
   *   Include the deleted cases in result.
369 370
   * @param null $caseType
   *
totten's avatar
totten committed
371 372
   * @return array
   */
373
  public static function retrieveCaseIdsByContactId($contactID, $includeDeleted = FALSE, $caseType = NULL) {
totten's avatar
totten committed
374 375 376 377 378
    $query = "
SELECT ca.id as id
FROM civicrm_case_contact cc
INNER JOIN civicrm_case ca ON cc.case_id = ca.id
";
379 380
    if (isset($caseType)) {
      $query .=
381
        "INNER JOIN civicrm_case_type ON civicrm_case_type.id = ca.case_type_id
Adam Roses Wight's avatar
Adam Roses Wight committed
382
WHERE cc.contact_id = %1 AND civicrm_case_type.name = '{$caseType}'";
383
    }
Divya Jose's avatar
Divya Jose committed
384 385 386
    if (!isset($caseType)) {
      $query .= "WHERE cc.contact_id = %1";
    }
totten's avatar
totten committed
387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402
    if (!$includeDeleted) {
      $query .= " AND ca.is_deleted = 0";
    }

    $params = array(1 => array($contactID, 'Integer'));
    $dao = CRM_Core_DAO::executeQuery($query, $params);

    $caseArray = array();
    while ($dao->fetch()) {
      $caseArray[] = $dao->id;
    }

    $dao->free();
    return $caseArray;
  }

403 404
  /**
   * @param string $type
colemanw's avatar
colemanw committed
405
   * @param int $userID
colemanw's avatar
colemanw committed
406
   * @param string $condition
407 408 409
   *
   * @return string
   */
410 411 412
  public static function getCaseActivityCountQuery($type = 'upcoming', $userID, $condition = NULL) {
    return sprintf(" SELECT COUNT(*) FROM (%s) temp ", self::getCaseActivityQuery($type, $userID, $condition));
  }
totten's avatar
totten committed
413

414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439
  /**
   * @param string $type
   * @param int $userID
   * @param string $condition
   * @param string $limit
   *
   * @return string
   */
  public static function getCaseActivityQuery($type = 'upcoming', $userID, $condition = NULL, $limit = NULL, $order = NULL) {
    $selectClauses = array(
      'civicrm_case.id as case_id',
      'civicrm_case.subject as case_subject',
      'civicrm_contact.id as contact_id',
      'civicrm_contact.sort_name as sort_name',
      'civicrm_phone.phone as phone',
      'civicrm_contact.contact_type as contact_type',
      'civicrm_contact.contact_sub_type as contact_sub_type',
      't_act.activity_type_id',
      'c_type.title as case_type',
      'civicrm_case.case_type_id as case_type_id',
      'cov_status.label as case_status',
      'cov_status.label as case_status_name',
      't_act.status_id',
      'civicrm_case.start_date as case_start_date',
      'case_relation_type.label_b_a as case_role',
    );
totten's avatar
totten committed
440 441

    if ($type == 'upcoming') {
442 443 444 445 446 447
      $selectClauses = array_merge($selectClauses, array(
        't_act.desired_date as case_scheduled_activity_date',
        't_act.id as case_scheduled_activity_id',
        't_act.act_type_name as case_scheduled_activity_type_name',
        't_act.act_type AS case_scheduled_activity_type',
      ));
totten's avatar
totten committed
448 449
    }
    elseif ($type == 'recent') {
450 451 452 453 454 455
      $selectClauses = array_merge($selectClauses, array(
        't_act.desired_date as case_recent_activity_date',
        't_act.id as case_recent_activity_id',
        't_act.act_type_name as case_recent_activity_type_name',
        't_act.act_type AS case_recent_activity_type',
      ));
totten's avatar
totten committed
456
    }
457
    elseif ($type == 'any') {
458 459 460 461 462 463
      $selectClauses = array_merge($selectClauses, array(
        't_act.desired_date as case_activity_date',
        't_act.id as case_activity_id',
        't_act.act_type_name as case_activity_type_name',
        't_act.act_type AS case_activity_type',
      ));
464
    }
totten's avatar
totten committed
465

466 467
    $query = CRM_Contact_BAO_Query::appendAnyValueToSelect($selectClauses, 'case_id');

totten's avatar
totten committed
468 469 470 471 472 473 474 475 476 477 478 479 480 481 482 483 484 485 486 487 488 489 490 491 492 493 494 495 496 497 498 499 500 501 502 503 504 505 506 507 508 509 510
    $query .= " FROM civicrm_case
                  INNER JOIN civicrm_case_contact ON civicrm_case.id = civicrm_case_contact.case_id
                  INNER JOIN civicrm_contact ON civicrm_case_contact.contact_id = civicrm_contact.id ";

    if ($type == 'upcoming') {
      // This gets the earliest activity per case that's scheduled within 14 days from now.
      // 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.
      // In this case we don't really care which one, so min(id) works.
      // optimized in CRM-11837
      $query .= " INNER JOIN
(
  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
  FROM (
    SELECT *
    FROM (
      SELECT *
      FROM civicrm_view_case_activity_upcoming
      ORDER BY activity_date_time ASC, id ASC
      ) AS upcomingOrdered
    ) AS act
  LEFT JOIN civicrm_option_group aog ON aog.name='activity_type'
  LEFT JOIN civicrm_option_value aov ON ( aov.option_group_id = aog.id AND aov.value = act.activity_type_id )
) AS t_act
";
    }
    elseif ($type == 'recent') {
      // Similarly, the most recent activity in the past 14 days, and exclude scheduled.
      //improve query performance - CRM-10598
      $query .= " INNER JOIN
(
  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
  FROM (
    SELECT *
    FROM (
      SELECT *
      FROM civicrm_view_case_activity_recent
      ORDER BY activity_date_time DESC, id ASC
      ) AS recentOrdered
    ) AS act
LEFT JOIN civicrm_option_group aog ON aog.name='activity_type'
  LEFT JOIN civicrm_option_value aov ON ( aov.option_group_id = aog.id AND aov.value = act.activity_type_id )
) AS t_act ";
    }
511
    elseif ($type == 'any') {
512 513 514 515 516 517 518 519 520 521 522 523 524 525
      $query .= " LEFT JOIN
(
  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
  FROM civicrm_activity act4
  LEFT JOIN civicrm_case_activity ca4
    ON ca4.activity_id = act4.id
    AND act4.is_current_revision = 1
  LEFT JOIN civicrm_option_group aog
    ON aog.name='activity_type'
  LEFT JOIN civicrm_option_value aov
    ON aov.option_group_id = aog.id
    AND aov.value = act4.activity_type_id
) AS t_act";
    }
totten's avatar
totten committed
526 527 528 529 530 531 532 533 534 535 536 537

    $query .= "
        ON t_act.case_id = civicrm_case.id
 LEFT JOIN civicrm_phone ON (civicrm_phone.contact_id = civicrm_contact.id AND civicrm_phone.is_primary=1)
 LEFT JOIN civicrm_relationship case_relationship
 ON ( case_relationship.contact_id_a = civicrm_case_contact.contact_id AND case_relationship.contact_id_b = {$userID}
      AND case_relationship.case_id = civicrm_case.id )

 LEFT JOIN civicrm_relationship_type case_relation_type
 ON ( case_relation_type.id = case_relationship.relationship_type_id
      AND case_relation_type.id = case_relationship.relationship_type_id )

Adam Roses Wight's avatar
Adam Roses Wight committed
538 539
 LEFT JOIN civicrm_case_type c_type
 ON civicrm_case.case_type_id = c_type.id
totten's avatar
totten committed
540 541 542 543 544 545 546 547 548 549 550

 LEFT JOIN civicrm_option_group cog_status
 ON cog_status.name = 'case_status'

 LEFT JOIN civicrm_option_value cov_status
 ON ( civicrm_case.status_id = cov_status.value
      AND cog_status.id = cov_status.option_group_id )
";

    if ($condition) {
      // CRM-8749 backwards compatibility - callers of this function expect to start $condition with "AND"
551
      $query .= " WHERE (1) AND $condition ";
totten's avatar
totten committed
552
    }
553
    $query .= " GROUP BY case_id ";
totten's avatar
totten committed
554

555 556
    if ($order) {
      $query .= $order;
totten's avatar
totten committed
557
    }
558 559 560 561 562 563 564 565 566 567
    else {
      if ($type == 'upcoming') {
        $query .= " ORDER BY case_scheduled_activity_date ASC ";
      }
      elseif ($type == 'recent') {
        $query .= " ORDER BY case_recent_activity_date ASC ";
      }
      elseif ($type == 'any') {
        $query .= " ORDER BY case_activity_date ASC ";
      }
totten's avatar
totten committed
568
    }
569 570 571

    if ($limit) {
      $query .= $limit;
572
    }
totten's avatar
totten committed
573 574 575 576 577

    return $query;
  }

  /**
Eileen McNaughton's avatar
Eileen McNaughton committed
578
   * Retrieve cases related to particular contact or whole contact used in Dashboard and Tab.
totten's avatar
totten committed
579
   *
580
   * @param bool $allCases
581
   * @param array $params
582
   * @param string $context
583
   * @param bool $getCount
584
   *
585 586
   * @return array
   *   Array of Cases
totten's avatar
totten committed
587
   */
588
  public static function getCases($allCases = TRUE, $params = array(), $context = 'dashboard', $getCount = FALSE) {
totten's avatar
totten committed
589 590 591 592 593
    $condition = NULL;
    $casesList = array();

    //validate access for own cases.
    if (!self::accessCiviCase()) {
594
      return $getCount ? 0 : $casesList;
totten's avatar
totten committed
595 596
    }

597 598 599 600 601 602 603 604 605 606 607 608 609 610 611
    $type = CRM_Utils_Array::value('type', $params, 'upcoming');
    $userID = CRM_Core_Session::singleton()->get('userID');

    $caseActivityTypeColumn = 'case_activity_type_name';
    $caseActivityDateColumn = 'case_activity_date';
    $caseActivityIDColumn = 'case_activity_id';
    if ($type == 'upcoming') {
      $caseActivityDateColumn = 'case_scheduled_activity_date';
      $caseActivityTypeColumn = 'case_scheduled_activity_type';
      $caseActivityIDColumn = 'case_scheduled_activity_id';
    }
    elseif ($type == 'recent') {
      $caseActivityDateColumn = 'case_recent_activity_date';
      $caseActivityTypeColumn = 'case_recent_activity_type';
      $caseActivityIDColumn = 'case_recent_activity_id';
totten's avatar
totten committed
612 613 614 615 616 617 618
    }

    //validate access for all cases.
    if ($allCases && !CRM_Core_Permission::check('access all cases and activities')) {
      $allCases = FALSE;
    }

619
    $whereClauses = array('civicrm_case.is_deleted = 0 AND civicrm_contact.is_deleted <> 1');
totten's avatar
totten committed
620 621

    if (!$allCases) {
622
      $whereClauses[] .= " case_relationship.contact_id_b = {$userID} ";
totten's avatar
totten committed
623
    }
624 625
    if (empty($params['status_id']) && ($type == 'upcoming' || $type == 'any')) {
      $whereClauses[] = " civicrm_case.status_id != " . CRM_Core_PseudoConstant::getKey('CRM_Case_BAO_Case', 'case_status_id', 'Closed');
totten's avatar
totten committed
626 627
    }

628 629 630 631 632 633
    foreach (array('case_type_id', 'status_id') as $column) {
      if (!empty($params[$column])) {
        $whereClauses[] = sprintf("civicrm_case.%s IN (%s)", $column, $params[$column]);
      }
    }
    $condition = implode(' AND ', $whereClauses);
totten's avatar
totten committed
634

635 636 637
    $totalCount = CRM_Core_DAO::singleValueQuery(self::getCaseActivityCountQuery($type, $userID, $condition));
    if ($getCount) {
      return $totalCount;
totten's avatar
totten committed
638
    }
639 640 641 642 643 644 645 646 647
    $casesList['total'] = $totalCount;

    $limit = '';
    if (!empty($params['rp'])) {
      $params['offset'] = ($params['page'] - 1) * $params['rp'];
      $params['rowCount'] = $params['rp'];
      if (!empty($params['rowCount']) && $params['rowCount'] > 0) {
        $limit = " LIMIT {$params['offset']}, {$params['rowCount']} ";
      }
totten's avatar
totten committed
648
    }
649 650 651 652 653 654 655

    $order = NULL;
    if (!empty($params['sortBy'])) {
      if (strstr($params['sortBy'], 'date ')) {
        $params['sortBy'] = str_replace('date', $caseActivityDateColumn, $params['sortBy']);
      }
      $order = "ORDER BY " . $params['sortBy'];
656
    }
totten's avatar
totten committed
657

658 659 660 661 662
    $query = self::getCaseActivityQuery($type, $userID, $condition, $limit, $order);
    $result = CRM_Core_DAO::executeQuery($query);

    $caseStatus = CRM_Core_OptionGroup::values('case_status', FALSE, FALSE, FALSE, " AND v.name = 'Urgent' ");

totten's avatar
totten committed
663 664 665 666 667 668 669 670 671 672 673 674 675 676 677
    // we're going to use the usual actions, so doesn't make sense to duplicate definitions
    $actions = CRM_Case_Selector_Search::links();

    // check is the user has view/edit signer permission
    $permissions = array(CRM_Core_Permission::VIEW);
    if (CRM_Core_Permission::check('access all cases and activities') ||
      (!$allCases && CRM_Core_Permission::check('access my cases and activities'))
    ) {
      $permissions[] = CRM_Core_Permission::EDIT;
    }
    if (CRM_Core_Permission::check('delete in CiviCase')) {
      $permissions[] = CRM_Core_Permission::DELETE;
    }
    $mask = CRM_Core_Action::mask($permissions);

678 679 680 681 682 683 684 685 686 687 688 689 690 691 692 693 694 695 696 697 698 699 700 701
    $caseTypes = CRM_Case_PseudoConstant::caseType('name');
    foreach ($result->fetchAll() as $key => $case) {
      $casesList[$key] = array();
      $casesList[$key]['DT_RowId'] = $case['case_id'];
      $casesList[$key]['DT_RowAttr'] = array('data-entity' => 'case', 'data-id' => $case['case_id']);
      $casesList[$key]['DT_RowClass'] = "crm-entity";

      $casesList[$key]['activity_list'] = sprintf('<a title="%s" class="crm-expand-row" href="%s"></a>',
        ts('Activities'),
        CRM_Utils_System::url('civicrm/case/details', array('caseid' => $case['case_id'], 'cid' => $case['contact_id'], 'type' => $type))
      );

      $phone = empty($case['phone']) ? '' : '<br /><span class="description">' . $case['phone'] . '</span>';
      $casesList[$key]['contact_id'] = sprintf('<a href="%s">%s</a>%s<br /><span class="description">%s: %d</span>',
        CRM_Utils_System::url('civicrm/contact/view', array('cid' => $case['contact_id'])),
        $case['sort_name'],
        $phone,
        ts('Case ID'),
        $case['case_id']
      );
      $casesList[$key]['subject'] = $case['case_subject'];
      $casesList[$key]['case_status'] = in_array($case['case_status'], $caseStatus) ? sprintf('<strong>%s</strong>', strtoupper($case['case_status'])) : $case['case_status'];
      $casesList[$key]['case_type'] = $case['case_type'];
      $casesList[$key]['case_role'] = CRM_Utils_Array::value('case_role', $case, '---');
deb.monish's avatar
deb.monish committed
702
      $casesList[$key]['manager'] = self::getCaseManagerContact($caseTypes[$case['case_type_id']], $case['case_id']);
703 704 705 706 707 708 709 710 711 712 713 714

      $casesList[$key]['date'] = $case[$caseActivityTypeColumn];
      if (($actId = CRM_Utils_Array::value('case_scheduled_activity_id', $case)) ||
        ($actId = CRM_Utils_Array::value('case_recent_activity_id', $case))
      ) {
        if (self::checkPermission($actId, 'view', $case['activity_type_id'], $userID)) {
          if ($type == 'recent') {
            $casesList[$key]['date'] = sprintf('<a class="action-item crm-hover-button" href="%s" title="%s">%s</a>',
              CRM_Utils_System::url('civicrm/case/activity/view', array('reset' => 1, 'cid' => $case['contact_id'], 'aid' => $case[$caseActivityIDColumn])),
              ts('View activity'),
              $case[$caseActivityTypeColumn]
            );
totten's avatar
totten committed
715 716
          }
          else {
717 718 719 720 721 722 723
            $status = CRM_Utils_Date::overdue($case[$caseActivityDateColumn]) ? 'status-overdue' : 'status-scheduled';
            $casesList[$key]['date'] = sprintf('<a class="crm-popup %s" href="%s" title="%s">%s</a> &nbsp;&nbsp;',
             $status,
              CRM_Utils_System::url('civicrm/case/activity/view', array('reset' => 1, 'cid' => $case['contact_id'], 'aid' => $case[$caseActivityIDColumn])),
              ts('View activity'),
              $case[$caseActivityTypeColumn]
            );
totten's avatar
totten committed
724 725
          }
        }
726 727 728 729 730 731
        if (self::checkPermission($actId, 'edit', $case['activity_type_id'], $userID)) {
          $casesList[$key]['date'] .= sprintf('<a class="action-item crm-hover-button" href="%s" title="%s"><i class="crm-i fa-pencil"></i></a>',
            CRM_Utils_System::url('civicrm/case/activity', array('reset' => 1, 'cid' => $case['contact_id'], 'caseid' => $case['case_id'], 'action' => 'update')),
            ts('Edit activity')
          );
        }
totten's avatar
totten committed
732
      }
733 734 735 736 737 738 739 740 741 742 743 744 745
      $casesList[$key]['date'] .= "<br/>" . CRM_Utils_Date::customFormat($case[$caseActivityDateColumn]);
      $casesList[$key]['links'] = CRM_Core_Action::formLink($actions['primaryActions'], $mask,
        array(
          'id' => $case['case_id'],
          'cid' => $case['contact_id'],
          'cxt' => $context,
        ),
        ts('more'),
        FALSE,
        'case.actions.primary',
        'Case',
        $case['case_id']
      );
totten's avatar
totten committed
746 747 748 749 750 751
    }

    return $casesList;
  }

  /**
colemanw's avatar
colemanw committed
752 753 754 755
   * Get the summary of cases counts by type and status.
   *
   * @param bool $allCases
   * @return array
totten's avatar
totten committed
756
   */
757
  public static function getCasesSummary($allCases = TRUE) {
totten's avatar
totten committed
758 759 760 761 762 763 764
    $caseSummary = array();

    //validate access for civicase.
    if (!self::accessCiviCase()) {
      return $caseSummary;
    }

765 766
    $userID = CRM_Core_Session::singleton()->get('userID');

totten's avatar
totten committed
767 768 769 770 771
    //validate access for all cases.
    if ($allCases && !CRM_Core_Permission::check('access all cases and activities')) {
      $allCases = FALSE;
    }

yashodha's avatar
yashodha committed
772
    $caseTypes = CRM_Case_PseudoConstant::caseType();
totten's avatar
totten committed
773
    $caseStatuses = CRM_Case_PseudoConstant::caseStatus();
yashodha's avatar
yashodha committed
774
    $caseTypes = array_flip($caseTypes);
totten's avatar
totten committed
775 776 777 778 779 780 781 782 783 784 785 786 787 788 789 790

    // get statuses as headers for the table
    $url = CRM_Utils_System::url('civicrm/case/search', "reset=1&force=1&all=1&status=");
    foreach ($caseStatuses as $key => $name) {
      $caseSummary['headers'][$key]['status'] = $name;
      $caseSummary['headers'][$key]['url'] = $url . $key;
    }

    // build rows with actual data
    $rows = array();
    $myGroupByClause = $mySelectClause = $myCaseFromClause = $myCaseWhereClause = '';

    if ($allCases) {
      $userID = 'null';
      $all = 1;
      $case_owner = 1;
791
      $myGroupByClause = ' GROUP BY civicrm_case.id';
totten's avatar
totten committed
792 793
    }
    else {
yashodha's avatar
yashodha committed
794
      $all = 0;
totten's avatar
totten committed
795 796
      $case_owner = 2;
      $myCaseWhereClause = " AND case_relationship.contact_id_b = {$userID}";
yashodha's avatar
yashodha committed
797
      $myGroupByClause = " GROUP BY CONCAT(case_relationship.case_id,'-',case_relationship.contact_id_b)";
totten's avatar
totten committed
798
    }
jitendra's avatar
jitendra committed
799
    $myGroupByClause .= ", case_status.label, status_id, case_type_id";
totten's avatar
totten committed
800

801
    // FIXME: This query could be a lot more efficient if it used COUNT() instead of returning all rows and then counting them with php
totten's avatar
totten committed
802
    $query = "
Adam Roses Wight's avatar
Adam Roses Wight committed
803 804
SELECT case_status.label AS case_status, status_id, civicrm_case_type.title AS case_type,
 case_type_id, case_relationship.contact_id_b
totten's avatar
totten committed
805
 FROM civicrm_case
806
 INNER JOIN civicrm_case_contact cc on cc.case_id = civicrm_case.id
Adam Roses Wight's avatar
Adam Roses Wight committed
807
 LEFT JOIN civicrm_case_type ON civicrm_case.case_type_id = civicrm_case_type.id
totten's avatar
totten committed
808 809 810 811 812
 LEFT JOIN civicrm_option_group option_group_case_status ON ( option_group_case_status.name = 'case_status' )
 LEFT JOIN civicrm_option_value case_status ON ( civicrm_case.status_id = case_status.value
 AND option_group_case_status.id = case_status.option_group_id )
 LEFT JOIN civicrm_relationship case_relationship ON ( case_relationship.case_id  = civicrm_case.id
 AND case_relationship.contact_id_b = {$userID})
813
 WHERE is_deleted = 0 AND cc.contact_id IN (SELECT id FROM civicrm_contact WHERE is_deleted <> 1)
totten's avatar
totten committed
814 815
{$myCaseWhereClause} {$myGroupByClause}";

eileen's avatar
eileen committed
816
    $res = CRM_Core_DAO::executeQuery($query);
totten's avatar
totten committed
817
    while ($res->fetch()) {
818
      if (!empty($rows[$res->case_type]) && !empty($rows[$res->case_type][$res->case_status])) {
totten's avatar
totten committed
819 820 821 822 823 824 825 826 827 828 829 830 831 832 833 834 835
        $rows[$res->case_type][$res->case_status]['count'] = $rows[$res->case_type][$res->case_status]['count'] + 1;
      }
      else {
        $rows[$res->case_type][$res->case_status] = array(
          'count' => 1,
          'url' => CRM_Utils_System::url('civicrm/case/search',
            "reset=1&force=1&status={$res->status_id}&type={$res->case_type_id}&case_owner={$case_owner}"
          ),
        );
      }
    }
    $caseSummary['rows'] = array_merge($caseTypes, $rows);

    return $caseSummary;
  }

  /**
836
   * Get Case roles.
totten's avatar
totten committed
837
   *
838 839 840 841
   * @param int $contactID
   *   Contact id.
   * @param int $caseID
   *   Case id.
colemanw's avatar
colemanw committed
842
   * @param int $relationshipID
843
   * @param bool $activeOnly
844
   *
845 846
   * @return array
   *   case role / relationships
totten's avatar
totten committed
847 848
   *
   */
849
  public static function getCaseRoles($contactID, $caseID, $relationshipID = NULL, $activeOnly = TRUE) {
totten's avatar
totten committed
850
    $query = '
851 852
    SELECT  rel.id as civicrm_relationship_id,
            con.sort_name as sort_name,
totten's avatar
totten committed
853 854
            civicrm_email.email as email,
            civicrm_phone.phone as phone,
855 856 857 858 859 860 861 862 863 864
            con.id as civicrm_contact_id,
            IF(rel.contact_id_a = %1, civicrm_relationship_type.label_a_b, civicrm_relationship_type.label_b_a) as relation,
            civicrm_relationship_type.id as relation_type,
            IF(rel.contact_id_a = %1, "a_b", "b_a") as relationship_direction
      FROM  civicrm_relationship rel
 INNER JOIN  civicrm_relationship_type ON rel.relationship_type_id = civicrm_relationship_type.id
 INNER JOIN  civicrm_contact con ON ((con.id <> %1 AND con.id IN (rel.contact_id_a, rel.contact_id_b)) OR (con.id = %1 AND rel.contact_id_b = rel.contact_id_a AND rel.contact_id_a = %1))
 LEFT JOIN  civicrm_phone ON (civicrm_phone.contact_id = con.id AND civicrm_phone.is_primary = 1)
 LEFT JOIN  civicrm_email ON (civicrm_email.contact_id = con.id AND civicrm_email.is_primary = 1)
     WHERE  (rel.contact_id_a = %1 OR rel.contact_id_b = %1) AND rel.case_id = %2
865 866 867 868 869
       AND con.is_deleted = 0';

    if ($activeOnly) {
      $query .= ' AND rel.is_active = 1 AND (rel.end_date IS NULL OR rel.end_date > NOW())';
    }
totten's avatar
totten committed
870 871 872 873 874 875 876

    $params = array(
      1 => array($contactID, 'Positive'),
      2 => array($caseID, 'Positive'),
    );

    if ($relationshipID) {
877
      $query .= ' AND rel.id = %3 ';
totten's avatar
totten committed
878 879 880 881 882 883 884 885 886 887 888 889 890 891
      $params[3] = array($relationshipID, 'Integer');
    }
    $dao = CRM_Core_DAO::executeQuery($query, $params);

    $values = array();
    while ($dao->fetch()) {
      $rid = $dao->civicrm_relationship_id;
      $values[$rid]['cid'] = $dao->civicrm_contact_id;
      $values[$rid]['relation'] = $dao->relation;
      $values[$rid]['name'] = $dao->sort_name;
      $values[$rid]['email'] = $dao->email;
      $values[$rid]['phone'] = $dao->phone;
      $values[$rid]['relation_type'] = $dao->relation_type;
      $values[$rid]['rel_id'] = $dao->civicrm_relationship_id;
892 893
      $values[$rid]['client_id'] = $contactID;
      $values[$rid]['relationship_direction'] = $dao->relationship_direction;
totten's avatar
totten committed
894 895 896 897 898 899 900
    }

    $dao->free();
    return $values;
  }

  /**
901
   * Get Case Activities.
totten's avatar
totten committed
902
   *
903 904 905 906 907 908
   * @param int $caseID
   *   Case id.
   * @param array $params
   *   Posted params.
   * @param int $contactID
   *   Contact id.
totten's avatar
totten committed
909
   *
910
   * @param null $context
colemanw's avatar
colemanw committed
911
   * @param int $userID
912 913
   * @param null $type
   *
914
   * @return array
915
   *   Array of case activities
totten's avatar
totten committed
916 917
   *
   */
918
  public static function getCaseActivity($caseID, &$params, $contactID, $context = NULL, $userID = NULL, $type = NULL) {
totten's avatar
totten committed
919 920
    $values = array();

921
    $activityContacts = CRM_Activity_BAO_ActivityContact::buildOptions('record_type_id', 'validate');
yashodha's avatar
yashodha committed
922
    $assigneeID = CRM_Utils_Array::key('Activity Assignees', $activityContacts);
yashodha's avatar
yashodha committed
923
    $sourceID = CRM_Utils_Array::key('Activity Source', $activityContacts);
yashodha's avatar
yashodha committed
924
    $targetID = CRM_Utils_Array::key('Activity Targets', $activityContacts);
yashodha's avatar
yashodha committed
925

totten's avatar
totten committed
926 927
    // CRM-5081 - formatting the dates to omit seconds.
    // Note the 00 in the date format string is needed otherwise later on it thinks scheduled ones are overdue.
928
    $select = "
jitendra's avatar
jitendra committed
929 930 931 932 933 934 935 936 937 938 939 940 941 942 943 944 945 946 947 948 949
           SELECT SQL_CALC_FOUND_ROWS COUNT(ca.id) AS ismultiple,
                  ca.id AS id,
                  ca.activity_type_id AS type,
                  ca.activity_type_id AS activity_type_id,
                  tcc.sort_name AS target_contact_name,
                  tcc.id AS target_contact_id,
                  scc.sort_name AS source_contact_name,
                  scc.id AS source_contact_id,
                  acc.sort_name AS assignee_contact_name,
                  acc.id AS assignee_contact_id,
                  DATE_FORMAT(
                    IF(ca.activity_date_time < NOW() AND ca.status_id=ov.value,
                      ca.activity_date_time,
                      DATE_ADD(NOW(), INTERVAL 1 YEAR)
                    ), '%Y%m%d%H%i00') AS overdue_date,
                  DATE_FORMAT(ca.activity_date_time, '%Y%m%d%H%i00') AS display_date,
                  ca.status_id AS status,
                  ca.subject AS subject,
                  ca.is_deleted AS deleted,
                  ca.priority_id AS priority,
                  ca.weight AS weight,
950
                  GROUP_CONCAT(ef.file_id) AS attachment_ids ";
totten's avatar
totten committed
951

yashodha's avatar
yashodha committed
952
    $from = "
953 954 955 956 957 958 959 960 961 962 963 964 965 966 967 968 969 970 971 972 973 974 975 976 977 978 979 980 981 982 983 984 985 986 987 988 989 990 991 992
             FROM civicrm_case_activity cca
       INNER JOIN civicrm_activity ca
               ON ca.id = cca.activity_id
       INNER JOIN civicrm_activity_contact cas
               ON cas.activity_id = ca.id
              AND cas.record_type_id = {$sourceID}
       INNER JOIN civicrm_contact scc
               ON scc.id = cas.contact_id
        LEFT JOIN civicrm_activity_contact caa
               ON caa.activity_id = ca.id
              AND caa.record_type_id = {$assigneeID}
        LEFT JOIN civicrm_contact acc
               ON acc.id = caa.contact_id
        LEFT JOIN civicrm_activity_contact cat
               ON cat.activity_id = ca.id
              AND cat.record_type_id = {$targetID}
        LEFT JOIN civicrm_contact tcc
               ON tcc.id = cat.contact_id
       INNER JOIN civicrm_option_group cog
               ON cog.name = 'activity_type'
       INNER JOIN civicrm_option_value cov
               ON cov.option_group_id = cog.id
              AND cov.value = ca.activity_type_id
              AND cov.is_active = 1
        LEFT JOIN civicrm_entity_file ef
               ON ef.entity_table = 'civicrm_activity'
              AND ef.entity_id = ca.id
  LEFT OUTER JOIN civicrm_option_group og
               ON og.name = 'activity_status'
  LEFT OUTER JOIN civicrm_option_value ov
               ON ov.option_group_id=og.id
              AND ov.name = 'Scheduled'";

    $where = '
            WHERE cca.case_id= %1
              AND ca.is_current_revision = 1';

    if (!empty($params['source_contact_id'])) {
      $where .= "
              AND cas.contact_id = " . CRM_Utils_Type::escape($params['source_contact_id'], 'Integer');
totten's avatar
totten committed
993 994
    }

995
    if (!empty($params['status_id'])) {
996 997
      $where .= "
              AND ca.status_id = " . CRM_Utils_Type::escape($params['status_id'], 'Integer');
totten's avatar
totten committed
998 999
    }

1000
    if (!empty($params['activity_deleted'])) {
1001 1002
      $where .= "
              AND ca.is_deleted = 1";
totten's avatar
totten committed
1003 1004
    }
    else {
1005 1006
      $where .= "
              AND ca.is_deleted = 0";
totten's avatar
totten committed
1007 1008
    }

1009
    if (!empty($params['activity_type_id'])) {
1010 1011
      $where .= "
              AND ca.activity_type_id = " . CRM_Utils_Type::escape($params['activity_type_id'], 'Integer');
totten's avatar
totten committed
1012 1013
    }

1014
    if (!empty($params['activity_date_low'])) {
totten's avatar
totten committed
1015 1016
      $fromActivityDate = CRM_Utils_Type::escape(CRM_Utils_Date::processDate($params['activity_date_low']), 'Date');
    }
1017 1018 1019 1020 1021
    if (!empty($fromActivityDate)) {
      $where .= "
              AND ca.activity_date_time >= '{$fromActivityDate}'";
    }

1022
    if (!empty($params['activity_date_high'])) {
totten's avatar
totten committed
1023 1024 1025 1026
      $toActivityDate = CRM_Utils_Type::escape(CRM_Utils_Date::processDate($params['activity_date_high']), 'Date');
      $toActivityDate = $toActivityDate ? $toActivityDate + 235959 : NULL;
    }
    if (!empty($toActivityDate)) {
1027 1028
      $where .= "
              AND ca.activity_date_time <= '{$toActivityDate}'";
totten's avatar
totten committed
1029 1030
    }

jitendra's avatar
jitendra committed
1031 1032
    $groupBy = "
         GROUP BY ca.id, tcc.id, scc.id, acc.id, ov.value";
totten's avatar
totten committed
1033

1034 1035
    $sortBy = CRM_Utils_Array::value('sortBy', $params);
    if (!$sortBy) {
totten's avatar
totten committed
1036
      // CRM-5081 - added id to act like creation date
1037 1038
      $orderBy = "
         ORDER BY overdue_date ASC, display_date DESC, weight DESC";
totten's avatar
totten committed
1039 1040
    }
    else {
1041 1042
      $sortBy = CRM_Utils_Type::escape($sortBy, 'String');
      $orderBy = " ORDER BY $sortBy ";
totten's avatar
totten committed
1043 1044
    }

1045 1046 1047
    $page = CRM_Utils_Array::value('page', $params);
    $rp = CRM_Utils_Array::value('rp', $params);

totten's avatar
totten committed
1048 1049 1050 1051 1052 1053 1054
    if (!$page) {
      $page = 1;
    }
    if (!$rp) {
      $rp = 10;
    }
    $start = (($page - 1) * $rp);
1055
    $limit = " LIMIT $start, $rp";
totten's avatar
totten committed
1056

1057
    $query = $select . $from . $where . $groupBy . $orderBy . $limit;
1058
    $queryParams = array(1 => array($caseID, 'Integer'));
totten's avatar
totten committed
1059

1060
    $dao = CRM_Core_DAO::executeQuery($query, $queryParams);
1061
    $caseCount = CRM_Core_DAO::singleValueQuery('SELECT FOUND_ROWS()');
1062

yashodha's avatar
yashodha committed
1063
    $activityTypes = CRM_Case_PseudoConstant::caseActivityType(FALSE, TRUE);
1064
    $activityStatuses = CRM_Core_PseudoConstant::activityStatus();
totten's avatar
totten committed
1065 1066 1067 1068 1069 1070 1071 1072 1073

    $url = CRM_Utils_System::url("civicrm/case/activity",
      "reset=1&cid={$contactID}&caseid={$caseID}", FALSE, NULL, FALSE
    );

    $contextUrl = '';
    if ($context == 'fulltext') {
      $contextUrl = "&context={$context}";
    }
yashodha's avatar
yashodha committed
1074 1075 1076
    $editUrl = "{$url}&action=update{$contextUrl}";
    $deleteUrl = "{$url}&action=delete{$contextUrl}";
    $restoreUrl = "{$url}&action=renew{$contextUrl}";
1077
    $viewTitle = ts('View activity');
totten's avatar
totten committed
1078 1079

    $emailActivityTypeIDs = array(
1080 1081
      'Email' => CRM_Core_PseudoConstant::getKey('CRM_Activity_BAO_Activity', 'activity_type_id', 'Email'),
      'Inbound Email' => CRM_Core_PseudoConstant::getKey('CRM_Activity_BAO_Activity', 'activity_type_id', 'Inbound Email'),
totten's avatar
totten committed
1082 1083 1084 1085
    );

    $caseDeleted = CRM_Core_DAO::getFieldValue('CRM_Case_DAO_Case', $caseID, 'is_deleted');

jitendra's avatar
jitendra committed
1086 1087 1088 1089
    $compStatusValues = array_keys(
      CRM_Activity_BAO_Activity::getStatusesByType(CRM_Activity_BAO_Activity::COMPLETED) +
      CRM_Activity_BAO_Activity::getStatusesByType(CRM_Activity_BAO_Activity::CANCELLED)
    );
1090 1091

    $contactViewUrl = CRM_Utils_System::url("civicrm/contact/view", "reset=1&cid=", FALSE, NULL, FALSE);
totten's avatar
totten committed
1092 1093 1094 1095 1096 1097 1098
    $hasViewContact = CRM_Core_Permission::giveMeAllACLs();

    if (!$userID) {
      $session = CRM_Core_Session::singleton();
      $userID = $session->get('userID');
    }

1099 1100
    $caseActivities = array();

totten's avatar
totten committed
1101
    while ($dao->fetch()) {