- /**
- * Get the activity Count.
- *
- * @param array $input
- * Array of parameters.
- * Keys include
- * - contact_id int contact_id whose activities we want to retrieve
- * - admin boolean if contact is admin
- * - caseId int case ID
- * - context string page on which selector is build
- * - activity_type_id int|string the activity types we want to restrict by
- *
- * @return int
- * count of activities
- */
- public static function &getActivitiesCount($input) {
- $input['count'] = TRUE;
- list($sqlClause, $params) = self::getActivitySQLClause($input);
-
- //filter case activities - CRM-5761
- $components = self::activityComponents();
- if (!in_array('CiviCase', $components)) {
- $query = "
- SELECT COUNT(DISTINCT(tbl.activity_id)) as count
- FROM ( {$sqlClause} ) as tbl
-LEFT JOIN civicrm_case_activity ON ( civicrm_case_activity.activity_id = tbl.activity_id )
- WHERE civicrm_case_activity.id IS NULL";
- }
- else {
- $query = "SELECT COUNT(DISTINCT(activity_id)) as count from ( {$sqlClause} ) as tbl";
- }
-
- return CRM_Core_DAO::singleValueQuery($query, $params);
- }
-
- /**
- * Get the activity sql clause to pick activities.
- *
- * @param array $input
- * Array of parameters.
- * Keys include
- * - contact_id int contact_id whose activities we want to retrieve
- * - admin boolean if contact is admin
- * - caseId int case ID
- * - context string page on which selector is build
- * - count boolean are we interested in the count clause only?
- * - activity_type_id int|string the activity types we want to restrict by
- *
- * @return int
- * count of activities
- */
- public static function getActivitySQLClause($input) {
- $params = array();
- $sourceWhere = $targetWhere = $assigneeWhere = $caseWhere = 1;
-
- $config = CRM_Core_Config::singleton();
- if (!CRM_Utils_Array::value('admin', $input, FALSE)) {
- $sourceWhere = ' ac.contact_id = %1 ';
- $caseWhere = ' civicrm_case_contact.contact_id = %1 ';
-
- $params = array(1 => array($input['contact_id'], 'Integer'));
- }
-
- $commonClauses = array(
- "civicrm_option_group.name = 'activity_type'",
- "civicrm_activity.is_deleted = 0",
- "civicrm_activity.is_current_revision = 1",
- "civicrm_activity.is_test= 0",
- );
-
- if ($input['context'] != 'activity') {
- $commonClauses[] = "civicrm_activity.status_id = 1";
- }
-
- // Filter on component IDs.
- $components = self::activityComponents();
- if (!empty($components)) {
- $componentsIn = implode(',', array_keys($components));
- $commonClauses[] = "( civicrm_option_value.component_id IS NULL OR civicrm_option_value.component_id IN ( $componentsIn ) )";
- }
- else {
- $commonClauses[] = "civicrm_option_value.component_id IS NULL";
- }
-
- // activity type ID clause
- if (!empty($input['activity_type_id'])) {
- if (is_array($input['activity_type_id'])) {
- foreach ($input['activity_type_id'] as $idx => $value) {
- $input['activity_type_id'][$idx] = CRM_Utils_Type::escape($value, 'Positive');
- }
- $commonClauses[] = "civicrm_activity.activity_type_id IN ( " . implode(",", $input['activity_type_id']) . " ) ";
- }
- else {
- $activityTypeID = CRM_Utils_Type::escape($input['activity_type_id'], 'Positive');
- $commonClauses[] = "civicrm_activity.activity_type_id = $activityTypeID";
- }
- }
-
- // exclude by activity type clause
- if (!empty($input['activity_type_exclude_id'])) {
- if (is_array($input['activity_type_exclude_id'])) {
- foreach ($input['activity_type_exclude_id'] as $idx => $value) {
- $input['activity_type_exclude_id'][$idx] = CRM_Utils_Type::escape($value, 'Positive');
- }
- $commonClauses[] = "civicrm_activity.activity_type_id NOT IN ( " . implode(",", $input['activity_type_exclude_id']) . " ) ";
- }
- else {
- $activityTypeID = CRM_Utils_Type::escape($input['activity_type_exclude_id'], 'Positive');
- $commonClauses[] = "civicrm_activity.activity_type_id != $activityTypeID";
- }
- }
-
- $commonClause = implode(' AND ', $commonClauses);
-
- $includeCaseActivities = FALSE;
- if (in_array('CiviCase', $components)) {
- $includeCaseActivities = TRUE;
- }
-
- // build main activity table select clause
- $sourceSelect = '';
-
- $activityContacts = CRM_Core_OptionGroup::values('activity_contacts', FALSE, FALSE, FALSE, NULL, 'name');
- $sourceID = CRM_Utils_Array::key('Activity Source', $activityContacts);
- $sourceJoin = "
-INNER JOIN civicrm_activity_contact ac ON ac.activity_id = civicrm_activity.id
-INNER JOIN civicrm_contact contact ON ac.contact_id = contact.id
-";
-
- if (!$input['count']) {
- $sourceSelect = ',
- civicrm_activity.activity_date_time,
- civicrm_activity.source_record_id,
- civicrm_activity.status_id,
- civicrm_activity.subject,
- contact.sort_name as source_contact_name,
- civicrm_option_value.value as activity_type_id,
- civicrm_option_value.label as activity_type,
- null as case_id, null as case_subject,
- civicrm_activity.campaign_id as campaign_id
- ';
-
- $sourceJoin .= "
-LEFT JOIN civicrm_activity_contact src ON (src.activity_id = ac.activity_id AND src.record_type_id = {$sourceID} AND src.contact_id = contact.id)
-";
- }
-
- $sourceClause = "
- SELECT civicrm_activity.id as activity_id
- {$sourceSelect}
- from civicrm_activity
- left join civicrm_option_value on
- civicrm_activity.activity_type_id = civicrm_option_value.value
- left join civicrm_option_group on
- civicrm_option_group.id = civicrm_option_value.option_group_id
- {$sourceJoin}
- where
- {$sourceWhere}
- AND $commonClause
- ";
-
- // Build case clause
- // or else exclude Inbound Emails that have been filed on a case.
- $caseClause = '';
-
- if ($includeCaseActivities) {
- $caseSelect = '';
- if (!$input['count']) {
- $caseSelect = ',
- civicrm_activity.activity_date_time,
- civicrm_activity.source_record_id,
- civicrm_activity.status_id,
- civicrm_activity.subject,
- contact.sort_name as source_contact_name,
- civicrm_option_value.value as activity_type_id,
- civicrm_option_value.label as activity_type,
- null as case_id, null as case_subject,
- civicrm_activity.campaign_id as campaign_id';
- }
-
- $caseClause = "
- union all
-
- SELECT civicrm_activity.id as activity_id
- {$caseSelect}
- from civicrm_activity
- inner join civicrm_case_activity on
- civicrm_case_activity.activity_id = civicrm_activity.id
- inner join civicrm_case on
- civicrm_case_activity.case_id = civicrm_case.id
- inner join civicrm_case_contact on
- civicrm_case_contact.case_id = civicrm_case.id and {$caseWhere}
- left join civicrm_option_value on
- civicrm_activity.activity_type_id = civicrm_option_value.value
- left join civicrm_option_group on
- civicrm_option_group.id = civicrm_option_value.option_group_id
- {$sourceJoin}
- where
- {$caseWhere}
- AND $commonClause
- and ( ( civicrm_case_activity.case_id IS NULL ) OR
- ( civicrm_option_value.name <> 'Inbound Email' AND
- civicrm_option_value.name <> 'Email' AND civicrm_case_activity.case_id
- IS NOT NULL )
- )
- ";
- }
-
- $returnClause = " {$sourceClause} {$caseClause} ";
-
- return array($returnClause, $params);
- }
-