*
* @return string
*/
- public static function getCaseActivityQuery($type = 'upcoming', $userID = NULL, $condition = NULL) {
- if (!$userID) {
- $session = CRM_Core_Session::singleton();
- $userID = $session->get('userID');
- }
+ public static function getCaseActivityCountQuery($type = 'upcoming', $userID, $condition = NULL) {
+ return sprintf(" SELECT COUNT(*) FROM (%s) temp ", self::getCaseActivityQuery($type, $userID, $condition));
+ }
- $query = "SELECT
-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, ";
+ /**
+ * @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',
+ );
if ($type == 'upcoming') {
- $query .= "
-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 ";
+ $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',
+ ));
}
elseif ($type == 'recent') {
- $query .= "
-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 ";
+ $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',
+ ));
}
elseif ($type == 'any') {
- $query .= "
-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 ";
+ $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',
+ ));
}
+ $query = CRM_Contact_BAO_Query::appendAnyValueToSelect($selectClauses, 'case_id');
+
$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 ($condition) {
// CRM-8749 backwards compatibility - callers of this function expect to start $condition with "AND"
- $query .= " WHERE (1) $condition ";
+ $query .= " WHERE (1) AND $condition ";
}
+ $query .= " GROUP BY case_id ";
- if ($type == 'upcoming') {
- $query .= " ORDER BY case_scheduled_activity_date ASC ";
+ if ($order) {
+ $query .= $order;
}
- elseif ($type == 'recent') {
- $query .= " ORDER BY case_recent_activity_date ASC ";
+ 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 ";
+ }
}
- elseif ($type == 'any') {
- $query .= " ORDER BY case_activity_date ASC ";
+
+ if ($limit) {
+ $query .= $limit;
}
return $query;
* Retrieve cases related to particular contact or whole contact used in Dashboard and Tab.
*
* @param bool $allCases
- *
- * @param int $userID
- *
- * @param string $type
- * /upcoming,recent,all/.
- *
+ * @param array $params
* @param string $context
+ * @param bool $getCount
*
* @return array
* Array of Cases
*/
- public static function getCases($allCases = TRUE, $userID = NULL, $type = 'upcoming', $context = 'dashboard') {
+ public static function getCases($allCases = TRUE, $params = array(), $context = 'dashboard', $getCount = FALSE) {
$condition = NULL;
$casesList = array();
//validate access for own cases.
if (!self::accessCiviCase()) {
- return $casesList;
+ return $getCount ? 0 : $casesList;
}
- if (!$userID) {
- $session = CRM_Core_Session::singleton();
- $userID = $session->get('userID');
+ $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';
}
//validate access for all cases.
$allCases = FALSE;
}
- $condition = " AND civicrm_case.is_deleted = 0 AND civicrm_contact.is_deleted <> 1";
+ $whereClauses = array('civicrm_case.is_deleted = 0 AND civicrm_contact.is_deleted <> 1');
if (!$allCases) {
- $condition .= " AND case_relationship.contact_id_b = {$userID} ";
+ $whereClauses[] .= " case_relationship.contact_id_b = {$userID} ";
}
- if ($type == 'upcoming' || $type == 'any') {
- $closedId = CRM_Core_PseudoConstant::getKey('CRM_Case_BAO_Case', 'case_status_id', 'Closed');
- $condition .= "
-AND civicrm_case.status_id != $closedId";
+ 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');
}
- $query = self::getCaseActivityQuery($type, $userID, $condition);
-
- $queryParams = array();
- $result = CRM_Core_DAO::executeQuery($query,
- $queryParams
- );
-
- $caseStatus = CRM_Core_OptionGroup::values('case_status', FALSE, FALSE, FALSE, " AND v.name = 'Urgent' ");
-
- $resultFields = array(
- 'contact_id',
- 'contact_type',
- 'sort_name',
- 'phone',
- 'case_id',
- 'case_subject',
- 'case_type',
- 'case_type_id',
- 'status_id',
- 'case_status',
- 'case_status_name',
- 'activity_type_id',
- 'case_start_date',
- 'case_role',
- );
+ 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);
- if ($type == 'upcoming') {
- $resultFields[] = 'case_scheduled_activity_date';
- $resultFields[] = 'case_scheduled_activity_type_name';
- $resultFields[] = 'case_scheduled_activity_type';
- $resultFields[] = 'case_scheduled_activity_id';
+ $totalCount = CRM_Core_DAO::singleValueQuery(self::getCaseActivityCountQuery($type, $userID, $condition));
+ if ($getCount) {
+ return $totalCount;
}
- elseif ($type == 'recent') {
- $resultFields[] = 'case_recent_activity_date';
- $resultFields[] = 'case_recent_activity_type_name';
- $resultFields[] = 'case_recent_activity_type';
- $resultFields[] = 'case_recent_activity_id';
+ $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']} ";
+ }
}
- elseif ($type == 'any') {
- $resultFields[] = 'case_activity_date';
- $resultFields[] = 'case_activity_type_name';
- $resultFields[] = 'case_activity_type';
- $resultFields[] = 'case_activity_id';
+
+ $order = NULL;
+ if (!empty($params['sortBy'])) {
+ if (strstr($params['sortBy'], 'date ')) {
+ $params['sortBy'] = str_replace('date', $caseActivityDateColumn, $params['sortBy']);
+ }
+ $order = "ORDER BY " . $params['sortBy'];
}
+ $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' ");
+
// we're going to use the usual actions, so doesn't make sense to duplicate definitions
$actions = CRM_Case_Selector_Search::links();
}
$mask = CRM_Core_Action::mask($permissions);
- while ($result->fetch()) {
- foreach ($resultFields as $donCare => $field) {
- $casesList[$result->case_id][$field] = $result->$field;
- if ($field == 'contact_type') {
- $casesList[$result->case_id]['contact_type_icon'] = CRM_Contact_BAO_Contact_Utils::getImage($result->contact_sub_type ? $result->contact_sub_type : $result->contact_type
- );
- $casesList[$result->case_id]['action'] = CRM_Core_Action::formLink($actions['primaryActions'], $mask,
- array(
- 'id' => $result->case_id,
- 'cid' => $result->contact_id,
- 'cxt' => $context,
- ),
- ts('more'),
- FALSE,
- 'case.actions.primary',
- 'Case',
- $result->case_id
- );
- }
- elseif ($field == 'case_status') {
- if (in_array($result->$field, $caseStatus)) {
- $casesList[$result->case_id]['class'] = "status-urgent";
+ $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, '---');
+
+ $caseManagerContact = self::getCaseManagerContact($caseTypes[$case['case_type_id']], $case['case_id']);
+ if (!empty($caseManagerContact)) {
+ $casesList[$key]['manager'] = sprintf('<a href="%s">%s</a>',
+ CRM_Utils_System::url('civicrm/contact/view', array('cid' => CRM_Utils_Array::value('casemanager_id', $caseManagerContact))),
+ CRM_Utils_Array::value('casemanager', $caseManagerContact)
+ );
+ }
+ $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]
+ );
}
else {
- $casesList[$result->case_id]['class'] = "status-normal";
+ $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> ',
+ $status,
+ CRM_Utils_System::url('civicrm/case/activity/view', array('reset' => 1, 'cid' => $case['contact_id'], 'aid' => $case[$caseActivityIDColumn])),
+ ts('View activity'),
+ $case[$caseActivityTypeColumn]
+ );
}
}
+ 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')
+ );
+ }
}
- //CRM-4510.
- $caseTypes = CRM_Case_PseudoConstant::caseType('name');
- $caseManagerContact = self::getCaseManagerContact($caseTypes[$result->case_type_id], $result->case_id);
- if (!empty($caseManagerContact)) {
- $casesList[$result->case_id]['casemanager_id'] = CRM_Utils_Array::value('casemanager_id', $caseManagerContact);
- $casesList[$result->case_id]['casemanager'] = CRM_Utils_Array::value('casemanager', $caseManagerContact);
- }
-
- //do check user permissions for edit/view activity.
- if (($actId = CRM_Utils_Array::value('case_scheduled_activity_id', $casesList[$result->case_id])) ||
- ($actId = CRM_Utils_Array::value('case_recent_activity_id', $casesList[$result->case_id]))
- ) {
- $casesList[$result->case_id]["case_{$type}_activity_editable"] = self::checkPermission($actId,
- 'edit',
- $casesList[$result->case_id]['activity_type_id'], $userID
- );
- $casesList[$result->case_id]["case_{$type}_activity_viewable"] = self::checkPermission($actId,
- 'view',
- $casesList[$result->case_id]['activity_type_id'], $userID
- );
- }
+ $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']
+ );
}
return $casesList;
* Get the summary of cases counts by type and status.
*
* @param bool $allCases
- * @param int $userID
* @return array
*/
- public static function getCasesSummary($allCases = TRUE, $userID) {
+ public static function getCasesSummary($allCases = TRUE) {
$caseSummary = array();
//validate access for civicase.
return $caseSummary;
}
+ $userID = CRM_Core_Session::singleton()->get('userID');
+
//validate access for all cases.
if ($allCases && !CRM_Core_Permission::check('access all cases and activities')) {
$allCases = FALSE;
$viewTitle = ts('View activity');
$emailActivityTypeIDs = array(
- 'Email' => CRM_Core_OptionGroup::getValue('activity_type',
- 'Email',
- 'name'
- ),
- 'Inbound Email' => CRM_Core_OptionGroup::getValue('activity_type',
- 'Inbound Email',
- 'name'
- ),
+ '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'),
);
$caseDeleted = CRM_Core_DAO::getFieldValue('CRM_Case_DAO_Case', $caseID, 'is_deleted');
$compStatusValues = array();
$compStatusNames = array('Completed', 'Left Message', 'Cancelled', 'Unreachable', 'Not Required');
foreach ($compStatusNames as $name) {
- $compStatusValues[] = CRM_Core_OptionGroup::getValue('activity_status', $name, 'name');
+ $compStatusValues[] = CRM_Core_PseudoConstant::getKey('CRM_Activity_BAO_Activity', 'activity_status_id', $name);
}
$contactViewUrl = CRM_Utils_System::url("civicrm/contact/view", "reset=1&cid=", FALSE, NULL, FALSE);
$activityParams['source_record_id'] = $activityId;
$activityParams['source_contact_id'] = $userID;
- $activityParams['activity_type_id'] = CRM_Core_OptionGroup::getValue('activity_type', 'Email', 'name');
+ $activityParams['activity_type_id'] = CRM_Core_PseudoConstant::getKey('CRM_Activity_DAO_Activity', 'activity_type_id', 'Email');
$activityParams['activity_date_time'] = date('YmdHis');
- $activityParams['status_id'] = CRM_Core_OptionGroup::getValue('activity_status', 'Completed', 'name');
- $activityParams['medium_id'] = CRM_Core_OptionGroup::getValue('encounter_medium', 'email', 'name');
+ $activityParams['status_id'] = CRM_Core_PseudoConstant::getKey('CRM_Activity_DAO_Activity', 'activity_status_id', 'Completed');
+ $activityParams['medium_id'] = CRM_Core_PseudoConstant::getKey('CRM_Activity_DAO_Activity', 'encounter_medium', 'email');
$activityParams['case_id'] = $caseId;
$activityParams['is_auto'] = 0;
$activityParams['target_id'] = $clientId;
$params['activity_date_time'] = $result['date'];
$params['details'] = $result['body'];
$params['source_contact_id'] = $result['from']['id'];
- $params['status_id'] = CRM_Core_OptionGroup::getValue('activity_status',
- 'Completed',
- 'name'
- );
+ $params['status_id'] = CRM_Core_PseudoConstant::getKey('CRM_Activity_BAO_Activity', 'activity_status_id', 'Completed');
$details = CRM_Case_PseudoConstant::caseActivityType();
$matches = array();
}
}
if (!isset($params['activity_type_id'])) {
- $params['activity_type_id'] = CRM_Core_OptionGroup::getValue('activity_type', 'Inbound Email', 'name');
+ $params['activity_type_id'] = CRM_Core_PseudoConstant::getKey('CRM_Activity_BAO_Activity', 'activity_type_id', 'Inbound Email');
}
// create activity
$caseID = implode(',', $cases['case_id']);
$contactID = implode(',', $cases['contact_id']);
- $condition = "
- AND civicrm_case_contact.contact_id IN( {$contactID} )
+ $condition = " civicrm_case_contact.contact_id IN( {$contactID} )
AND civicrm_case.id IN( {$caseID})
AND civicrm_case.is_deleted = {$cases['case_deleted']}";
'source_contact_id' => $session->get('userID'),
'subject' => $caseRelationship . ' : ' . $assigneContactName,
'activity_date_time' => date('YmdHis'),
- 'status_id' => CRM_Core_OptionGroup::getValue('activity_status', 'Completed', 'name'),
+ 'status_id' => CRM_Core_PseudoConstant::getKey('CRM_Activity_BAO_Activity', 'activity_status_id', 'Completed'),
);
//if $relContactId is passed, role is added or modified.
if (!empty($relContactId)) {
$activityParams['assignee_contact_id'] = $assigneContactIds;
-
- $activityTypeID = CRM_Core_OptionGroup::getValue('activity_type',
- 'Assign Case Role',
- 'name'
- );
+ $activityTypeID = CRM_Core_PseudoConstant::getKey('CRM_Activity_BAO_Activity', 'activity_type_id', 'Assign Case Role');
}
else {
- $activityTypeID = CRM_Core_OptionGroup::getValue('activity_type',
- 'Remove Case Role',
- 'name'
- );
+ $activityTypeID = CRM_Core_PseudoConstant::getKey('CRM_Activity_BAO_Activity', 'activity_type_id', 'Remove Case Role');
}
$activityParams['activity_type_id'] = $activityTypeID;
$doFilterCases = FALSE;
if (!CRM_Core_Permission::check('access all cases and activities')) {
$doFilterCases = TRUE;
- $session = CRM_Core_Session::singleton();
- $filterCases = CRM_Case_BAO_Case::getCases(FALSE, $session->get('userID'));
+ $filterCases = CRM_Case_BAO_Case::getCases(FALSE);
}
//2. fetch the details of related cases.