/**
* Get the list Activities.
*
* @param array $input
* Array of parameters.
* Keys include
* - contact_id int contact_id whose activities we want to retrieve
* - offset int which row to start from ?
* - rowCount int how many rows to fetch
* - sort object|array object or array describing sort order for sql query.
* - admin boolean if contact is admin
* - caseId int case ID
* - context string page on which selector is build
* - activity_type_id int|string the activitiy types we want to restrict by
*
* @return array
* Relevant data object values of open activities
*/
public static function &getActivities($input)
{
// Step 1: Get the basic activity data.
$bulkActivityTypeID = CRM_Core_OptionGroup::getValue('activity_type', 'Bulk Email', 'name');
$activityContacts = CRM_Core_OptionGroup::values('activity_contacts', FALSE, FALSE, FALSE, NULL, 'name');
$sourceID = CRM_Utils_Array::key('Activity Source', $activityContacts);
$assigneeID = CRM_Utils_Array::key('Activity Assignees', $activityContacts);
$targetID = CRM_Utils_Array::key('Activity Targets', $activityContacts);
$config = CRM_Core_Config::singleton();
$randomNum = md5(uniqid());
$activityTempTable = "civicrm_temp_activity_details_{$randomNum}";
$tableFields = array('activity_id' => 'int unsigned', 'activity_date_time' => 'datetime', 'source_record_id' => 'int unsigned', 'status_id' => 'int unsigned', 'subject' => 'varchar(255)', 'source_contact_name' => 'varchar(255)', 'activity_type_id' => 'int unsigned', 'activity_type' => 'varchar(128)', 'case_id' => 'int unsigned', 'case_subject' => 'varchar(255)', 'campaign_id' => 'int unsigned');
$sql = "CREATE TEMPORARY TABLE {$activityTempTable} ( ";
$insertValueSQL = array();
// The activityTempTable contains the sorted rows
// so in order to maintain the sort order as-is we add an auto_increment
// field; we can sort by this later to ensure the sort order stays correct.
$sql .= " fixed_sort_order INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,";
foreach ($tableFields as $name => $desc) {
$sql .= "{$name} {$desc},\n";
$insertValueSQL[] = $name;
}
// add unique key on activity_id just to be sure
// this cannot be primary key because we need that for the auto_increment
// fixed_sort_order field
$sql .= "\n UNIQUE KEY ( activity_id )\n ) ENGINE=HEAP DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci\n ";
CRM_Core_DAO::executeQuery($sql);
$insertSQL = "INSERT INTO {$activityTempTable} (" . implode(',', $insertValueSQL) . " ) ";
$order = $limit = $groupBy = '';
$groupBy = " GROUP BY tbl.activity_id ";
if (!empty($input['sort'])) {
if (is_a($input['sort'], 'CRM_Utils_Sort')) {
$orderBy = $input['sort']->orderBy();
if (!empty($orderBy)) {
$order = " ORDER BY {$orderBy}";
}
} elseif (trim($input['sort'])) {
$sort = CRM_Utils_Type::escape($input['sort'], 'String');
$order = " ORDER BY {$sort} ";
}
}
if (empty($order)) {
// context = 'activity' in Activities tab.
$order = CRM_Utils_Array::value('context', $input) == 'activity' ? " ORDER BY tbl.activity_date_time desc " : " ORDER BY tbl.status_id asc, tbl.activity_date_time asc ";
}
if (!empty($input['rowCount']) && $input['rowCount'] > 0) {
$limit = " LIMIT {$input['offset']}, {$input['rowCount']} ";
}
$input['count'] = FALSE;
list($sqlClause, $params) = self::getActivitySQLClause($input);
$query = "{$insertSQL}\n SELECT DISTINCT tbl.* from ( {$sqlClause} )\nas tbl ";
// Filter case activities - CRM-5761.
$components = self::activityComponents();
if (!in_array('CiviCase', $components)) {
$query .= "\nLEFT JOIN civicrm_case_activity ON ( civicrm_case_activity.activity_id = tbl.activity_id )\n WHERE civicrm_case_activity.id IS NULL";
}
$query = $query . $groupBy . $order . $limit;
$dao = CRM_Core_DAO::executeQuery($query, $params);
// step 2: Get target and assignee contacts for above activities
// create temp table for target contacts
$activityContactTempTable = "civicrm_temp_activity_contact_{$randomNum}";
$query = "CREATE TEMPORARY TABLE {$activityContactTempTable} (\n activity_id int unsigned, contact_id int unsigned, record_type_id varchar(16),\n contact_name varchar(255), is_deleted int unsigned, counter int unsigned, INDEX index_activity_id( activity_id ) )\n ENGINE=MYISAM DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci";
CRM_Core_DAO::executeQuery($query);
// note that we ignore bulk email for targets, since we don't show it in selector
$query = "\nINSERT INTO {$activityContactTempTable} ( activity_id, contact_id, record_type_id, contact_name, is_deleted )\nSELECT ac.activity_id,\n ac.contact_id,\n ac.record_type_id,\n c.sort_name,\n c.is_deleted\nFROM {$activityTempTable}\nINNER JOIN civicrm_activity a ON ( a.id = {$activityTempTable}.activity_id )\nINNER JOIN civicrm_activity_contact ac ON ( ac.activity_id = {$activityTempTable}.activity_id )\nINNER JOIN civicrm_contact c ON c.id = ac.contact_id\nWHERE ac.record_type_id != %1\n";
$params = array(1 => array($targetID, 'Integer'));
CRM_Core_DAO::executeQuery($query, $params);
// for each activity insert one target contact
// if we load all target contacts the performance will suffer a lot for mass-activities.
$query = "\nINSERT INTO {$activityContactTempTable} ( activity_id, contact_id, record_type_id, contact_name, is_deleted, counter )\nSELECT ac.activity_id,\n ac.contact_id,\n ac.record_type_id,\n c.sort_name,\n c.is_deleted,\n count(ac.contact_id)\nFROM {$activityTempTable}\nINNER JOIN civicrm_activity a ON ( a.id = {$activityTempTable}.activity_id )\nINNER JOIN civicrm_activity_contact ac ON ( ac.activity_id = {$activityTempTable}.activity_id )\nINNER JOIN civicrm_contact c ON c.id = ac.contact_id\nWHERE ac.record_type_id = %1\nGROUP BY ac.activity_id\n";
CRM_Core_DAO::executeQuery($query, $params);
// step 3: Combine all temp tables to get final query for activity selector
// sort by the original sort order, stored in fixed_sort_order
$query = "\nSELECT {$activityTempTable}.*,\n {$activityContactTempTable}.contact_id,\n {$activityContactTempTable}.record_type_id,\n {$activityContactTempTable}.contact_name,\n {$activityContactTempTable}.is_deleted,\n {$activityContactTempTable}.counter,\n re.parent_id as is_recurring_activity\nFROM {$activityTempTable}\nINNER JOIN {$activityContactTempTable} on {$activityTempTable}.activity_id = {$activityContactTempTable}.activity_id\nLEFT JOIN civicrm_recurring_entity re on {$activityContactTempTable}.activity_id = re.entity_id\nORDER BY fixed_sort_order\n ";
$dao = CRM_Core_DAO::executeQuery($query);
// CRM-3553, need to check user has access to target groups.
$mailingIDs = CRM_Mailing_BAO_Mailing::mailingACLIDs();
$accessCiviMail = CRM_Core_Permission::check('access CiviMail') || CRM_Mailing_Info::workflowEnabled() && CRM_Core_Permission::check('create mailings');
// Get all campaigns.
$allCampaigns = CRM_Campaign_BAO_Campaign::getCampaigns(NULL, NULL, FALSE, FALSE, FALSE, TRUE);
$values = array();
while ($dao->fetch()) {
$activityID = $dao->activity_id;
$values[$activityID]['activity_id'] = $dao->activity_id;
$values[$activityID]['source_record_id'] = $dao->source_record_id;
$values[$activityID]['activity_type_id'] = $dao->activity_type_id;
$values[$activityID]['activity_type'] = $dao->activity_type;
$values[$activityID]['activity_date_time'] = $dao->activity_date_time;
$values[$activityID]['status_id'] = $dao->status_id;
$values[$activityID]['subject'] = $dao->subject;
$values[$activityID]['campaign_id'] = $dao->campaign_id;
$values[$activityID]['is_recurring_activity'] = $dao->is_recurring_activity;
if ($dao->campaign_id) {
$values[$activityID]['campaign'] = $allCampaigns[$dao->campaign_id];
}
if (empty($values[$activityID]['assignee_contact_name'])) {
$values[$activityID]['assignee_contact_name'] = array();
}
if (empty($values[$activityID]['target_contact_name'])) {
$values[$activityID]['target_contact_name'] = array();
$values[$activityID]['target_contact_counter'] = $dao->counter;
}
// if deleted, wrap in <del>
if ($dao->is_deleted) {
$dao->contact_name = "<del>{$dao->contact_name}</del>";
}
if ($dao->record_type_id == $sourceID && $dao->contact_id) {
$values[$activityID]['source_contact_id'] = $dao->contact_id;
$values[$activityID]['source_contact_name'] = $dao->contact_name;
}
if (!$bulkActivityTypeID || $bulkActivityTypeID != $dao->activity_type_id) {
// build array of target / assignee names
if ($dao->record_type_id == $targetID && $dao->contact_id) {
$values[$activityID]['target_contact_name'][$dao->contact_id] = $dao->contact_name;
}
if ($dao->record_type_id == $assigneeID && $dao->contact_id) {
$values[$activityID]['assignee_contact_name'][$dao->contact_id] = $dao->contact_name;
}
// case related fields
$values[$activityID]['case_id'] = $dao->case_id;
$values[$activityID]['case_subject'] = $dao->case_subject;
} else {
$values[$activityID]['recipients'] = ts('(%1 recipients)', array(1 => $dao->counter));
$values[$activityID]['mailingId'] = FALSE;
if ($accessCiviMail && ($mailingIDs === TRUE || in_array($dao->source_record_id, $mailingIDs))) {
$values[$activityID]['mailingId'] = TRUE;
}
}
}
return $values;
}
/**
* Returns all the rows in the given offset and rowCount.
*
* @param string $action
* The action being performed.
* @param int $offset
* The row number to start from.
* @param int $rowCount
* The number of rows to return.
* @param string $sort
* The sql string that describes the sort order.
* @param string $output
* What should the result set include (web/email/csv).
*
* @return array
* rows in the given offset and rowCount
*/
public function &getRows($action, $offset, $rowCount, $sort, $output = NULL)
{
$result = $this->_query->searchQuery($offset, $rowCount, $sort, FALSE, FALSE, FALSE, FALSE, FALSE, $this->_activityClause);
$rows = array();
$mailingIDs = CRM_Mailing_BAO_Mailing::mailingACLIDs();
$accessCiviMail = CRM_Core_Permission::check('access CiviMail');
//get all campaigns.
$allCampaigns = CRM_Campaign_BAO_Campaign::getCampaigns(NULL, NULL, FALSE, FALSE, FALSE, TRUE);
$engagementLevels = CRM_Campaign_PseudoConstant::engagementLevel();
$activityContacts = CRM_Core_OptionGroup::values('activity_contacts', FALSE, FALSE, FALSE, NULL, 'name');
$sourceID = CRM_Utils_Array::key('Activity Source', $activityContacts);
$assigneeID = CRM_Utils_Array::key('Activity Assignees', $activityContacts);
$targetID = CRM_Utils_Array::key('Activity Targets', $activityContacts);
//get all activity types
$activityTypes = CRM_Core_PseudoConstant::activityType(TRUE, TRUE, FALSE, 'name', TRUE);
while ($result->fetch()) {
$row = array();
// ignore rows where we dont have an activity id
if (empty($result->activity_id)) {
continue;
}
// the columns we are interested in
foreach (self::$_properties as $property) {
if (isset($result->{$property})) {
$row[$property] = $result->{$property};
}
}
$contactId = CRM_Utils_Array::value('contact_id', $row);
if (!$contactId) {
$contactId = CRM_Utils_Array::value('source_contact_id', $row);
}
$row['target_contact_name'] = CRM_Activity_BAO_ActivityContact::getNames($row['activity_id'], $targetID);
$row['assignee_contact_name'] = CRM_Activity_BAO_ActivityContact::getNames($row['activity_id'], $assigneeID);
list($row['source_contact_name'], $row['source_contact_id']) = CRM_Activity_BAO_ActivityContact::getNames($row['activity_id'], $sourceID, TRUE);
$row['source_contact_name'] = implode(',', array_values($row['source_contact_name']));
$row['source_contact_id'] = implode(',', $row['source_contact_id']);
if ($this->_context == 'search') {
$row['checkbox'] = CRM_Core_Form::CB_PREFIX . $result->activity_id;
}
$row['contact_type'] = CRM_Contact_BAO_Contact_Utils::getImage($result->contact_sub_type ? $result->contact_sub_type : $result->contact_type, FALSE, $result->contact_id);
$accessMailingReport = FALSE;
$activityTypeId = $row['activity_type_id'];
if ($row['activity_is_test']) {
$row['activity_type'] = $row['activity_type'] . " (test)";
}
$bulkActivityTypeID = CRM_Utils_Array::key('Bulk Email', $activityTypes);
$row['mailingId'] = '';
if ($accessCiviMail && ($mailingIDs === TRUE || in_array($result->source_record_id, $mailingIDs)) && $bulkActivityTypeID == $activityTypeId) {
$row['mailingId'] = CRM_Utils_System::url('civicrm/mailing/report', "mid={$result->source_record_id}&reset=1&cid={$contactId}&context=activitySelector");
$row['recipients'] = ts('(recipients)');
$row['target_contact_name'] = '';
$row['assignee_contact_name'] = '';
$accessMailingReport = TRUE;
}
$activityActions = new CRM_Activity_Selector_Activity($result->contact_id, NULL);
$actionLinks = $activityActions->actionLinks($activityTypeId, CRM_Utils_Array::value('source_record_id', $row), $accessMailingReport, CRM_Utils_Array::value('activity_id', $row), $this->_key, $this->_compContext);
$row['action'] = CRM_Core_Action::formLink($actionLinks, NULL, array('id' => $result->activity_id, 'cid' => $contactId, 'cxt' => $this->_context), ts('more'), FALSE, 'activity.selector.row', 'Activity', $result->activity_id);
//carry campaign to selector.
$row['campaign'] = CRM_Utils_Array::value($result->activity_campaign_id, $allCampaigns);
$row['campaign_id'] = $result->activity_campaign_id;
if ($engagementLevel = CRM_Utils_Array::value('activity_engagement_level', $row)) {
$row['activity_engagement_level'] = CRM_Utils_Array::value($engagementLevel, $engagementLevels, $engagementLevel);
}
//Check if recurring activity
$repeat = CRM_Core_BAO_RecurringEntity::getPositionAndCount($row['activity_id'], 'civicrm_activity');
$row['repeat'] = '';
if ($repeat) {
$row['repeat'] = ts('Repeating (%1 of %2)', array(1 => $repeat[0], 2 => $repeat[1]));
}
$rows[] = $row;
}
return $rows;
}
/**
* Returns total number of rows for the query.
*
* @param
* @return int Total number of rows
* @access public
*/
function getTotalCount($action, $mailingType = null)
{
$condition = null;
// get count as per mailing type - CRM-4882
if ($mailingType) {
switch ($mailingType) {
case 'unscheduled':
$condition = " AND m.scheduled_id IS NULL";
break;
case 'scheduled':
$condition = " AND m.scheduled_id IS NOT NULL AND ( m.is_archived IS NULL OR m.is_archived != 1 )";
break;
case 'archived':
$condition = " AND m.is_archived = 1";
break;
}
}
require_once 'CRM/Mailing/BAO/Mailing.php';
return CRM_Mailing_BAO_Mailing::mailingACLIDs(true, $condition);
}
static function checkPermission($id)
{
if (!$id) {
return;
}
$mailingIDs = CRM_Mailing_BAO_Mailing::mailingACLIDs();
if (!in_array($id, $mailingIDs)) {
CRM_Core_Error::fatal(ts('You do not have permission to access this mailing report'));
}
return;
}
/**
* function to get the list Actvities
*
* @param array reference $params array of parameters
* @param int $offset which row to start from ?
* @param int $rowCount how many rows to fetch
* @param object|array $sort object or array describing sort order for sql query.
* @param type $type type of activity we're interested in
* @param boolean $admin if contact is admin
* @param int $caseId case id
* @param string $context context , page on which selector is build
*
* @return array (reference) $values the relevant data object values of open activitie
*
* @access public
* @static
*/
static function &getActivities(&$data, $offset = null, $rowCount = null, $sort = null, $admin = false, $caseId = null, $context = null)
{
//step 1: Get the basic activity data
require_once 'CRM/Core/OptionGroup.php';
$bulkActivityTypeID = CRM_Core_OptionGroup::getValue('activity_type', 'Bulk Email', 'name');
$config =& CRM_Core_Config::singleton();
$randomNum = md5(uniqid());
$activityTempTable = "civicrm_temp_activity_details_{$randomNum}";
$tableFields = array('activity_id' => 'int unsigned', 'activity_date_time' => 'datetime', 'status_id' => 'int unsigned', 'subject' => 'varchar(255)', 'source_contact_id' => 'int unsigned', 'source_record_id' => 'int unsigned', 'source_contact_name' => 'varchar(255)', 'activity_type_id' => 'int unsigned', 'activity_type' => 'varchar(128)', 'case_id' => 'int unsigned', 'case_subject' => 'varchar(255)');
$sql = "CREATE TEMPORARY TABLE {$activityTempTable} ( ";
$insertValueSQL = array();
foreach ($tableFields as $name => $desc) {
$sql .= "{$name} {$desc},\n";
$insertValueSQL[] = $name;
}
$sql .= "\n PRIMARY KEY ( activity_id )\n ) ENGINE=HEAP DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci\n ";
CRM_Core_DAO::executeQuery($sql);
$insertSQL = "INSERT INTO {$activityTempTable} (" . implode(',', $insertValueSQL) . " ) ";
$order = $limit = $groupBy = '';
$groupBy = " GROUP BY activity_id";
if ($sort) {
$orderBy = $sort->orderBy();
if (!empty($orderBy)) {
$order = " ORDER BY {$orderBy}";
}
}
if (empty($order)) {
if ($context == 'activity') {
$order = " ORDER BY activity_date_time desc ";
} else {
$order = " ORDER BY status_id asc, activity_date_time asc ";
}
}
if ($rowCount > 0) {
$limit = " LIMIT {$offset}, {$rowCount} ";
}
list($sqlClause, $params) = self::getActivitySQLClause($data['contact_id'], $admin, $caseId, $context);
$query = "{$insertSQL}\n SELECT DISTINCT * from ( {$sqlClause} )\nas tbl ";
$query = $query . $groupBy . $order . $limit;
$dao = CRM_Core_DAO::executeQuery($query, $params);
// step 2: Get target and assignee contacts for above activities
// create temp table for target contacts
$activityTargetContactTempTable = "civicrm_temp_target_contact_{$randomNum}";
$query = "CREATE TEMPORARY TABLE {$activityTargetContactTempTable} ( \n activity_id int unsigned, target_contact_id int unsigned, target_contact_name varchar(255) )\n ENGINE=MYISAM DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci";
CRM_Core_DAO::executeQuery($query);
// note that we ignore bulk email for targets, since we don't show it in selector
$query = "INSERT INTO {$activityTargetContactTempTable} ( activity_id, target_contact_id, target_contact_name )\n SELECT at.activity_id, \n at.target_contact_id , \n c.sort_name\n FROM civicrm_activity_target at\n INNER JOIN {$activityTempTable} ON ( at.activity_id = {$activityTempTable}.activity_id \n AND {$activityTempTable}.activity_type_id <> {$bulkActivityTypeID} )\n INNER JOIN civicrm_contact c ON c.id = at.target_contact_id";
CRM_Core_DAO::executeQuery($query);
// create temp table for assignee contacts
$activityAssigneetContactTempTable = "civicrm_temp_assignee_contact_{$randomNum}";
$query = "CREATE TEMPORARY TABLE {$activityAssigneetContactTempTable} ( \n activity_id int unsigned, assignee_contact_id int unsigned, assignee_contact_name varchar(255) )\n ENGINE=MYISAM DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci";
CRM_Core_DAO::executeQuery($query);
// note that we ignore bulk email for assignee, since we don't show it in selector
$query = "INSERT INTO {$activityAssigneetContactTempTable} ( activity_id, assignee_contact_id, assignee_contact_name )\n SELECT DISTINCT ( aa.activity_id ) , \n aa.assignee_contact_id, \n c.sort_name\n FROM civicrm_activity_assignment aa\n INNER JOIN {$activityTempTable} ON ( aa.activity_id = {$activityTempTable}.activity_id\n AND {$activityTempTable}.activity_type_id <> {$bulkActivityTypeID} )\n INNER JOIN civicrm_contact c ON c.id = aa.assignee_contact_id";
CRM_Core_DAO::executeQuery($query);
// step 3: Combine all temp tables to get final query for activity selector
$query = " \n SELECT {$activityTempTable}.*, \n {$activityTargetContactTempTable}.target_contact_id,{$activityTargetContactTempTable}.target_contact_name, \n {$activityAssigneetContactTempTable}.assignee_contact_id, {$activityAssigneetContactTempTable}.assignee_contact_name\n FROM {$activityTempTable}\n LEFT JOIN {$activityTargetContactTempTable} on {$activityTempTable}.activity_id = {$activityTargetContactTempTable}.activity_id\n LEFT JOIN {$activityAssigneetContactTempTable} on {$activityTempTable}.activity_id = {$activityAssigneetContactTempTable}.activity_id \n ";
$dao = CRM_Core_DAO::executeQuery($query);
//CRM-3553, need to check user has access to target groups.
require_once 'CRM/Mailing/BAO/Mailing.php';
$mailingIDs =& CRM_Mailing_BAO_Mailing::mailingACLIDs();
$accessCiviMail = CRM_Core_Permission::check('access CiviMail');
$values = array();
while ($dao->fetch()) {
$activityID = $dao->activity_id;
$values[$activityID]['activity_id'] = $dao->activity_id;
$values[$activityID]['source_record_id'] = $dao->source_record_id;
$values[$activityID]['activity_type_id'] = $dao->activity_type_id;
$values[$activityID]['activity_type'] = $dao->activity_type;
$values[$activityID]['activity_date_time'] = $dao->activity_date_time;
$values[$activityID]['status_id'] = $dao->status_id;
$values[$activityID]['subject'] = $dao->subject;
$values[$activityID]['source_contact_name'] = $dao->source_contact_name;
$values[$activityID]['source_contact_id'] = $dao->source_contact_id;
if ($bulkActivityTypeID != $dao->activity_type_id) {
// build array of target / assignee names
$values[$activityID]['target_contact_name'][$dao->target_contact_id] = $dao->target_contact_name;
$values[$activityID]['assignee_contact_name'][$dao->assignee_contact_id] = $dao->assignee_contact_name;
// case related fields
$values[$activityID]['case_id'] = $dao->case_id;
$values[$activityID]['case_subject'] = $dao->case_subject;
} else {
$values[$activityID]['recipients'] = ts('(recipients)');
if ($accessCiviMail && in_array($dao->source_record_id, $mailingIDs)) {
$values[$activityID]['mailingId'] = CRM_Utils_System::url('civicrm/mailing/report', "mid={$dao->source_record_id}&reset=1&cid={$dao->source_contact_id}&context=activitySelector");
$values[$activityID]['target_contact_name'] = '';
$values[$activityID]['assignee_contact_name'] = '';
}
}
}
return $values;
}
/**
* function to get the list Actvities
*
* @param array $input array of parameters
* Keys include
* - contact_id int contact_id whose activties we want to retrieve
* - offset int which row to start from ?
* - rowCount int how many rows to fetch
* - sort object|array object or array describing sort order for sql query.
* - admin boolean if contact is admin
* - caseId int case ID
* - context string page on which selector is build
* - activity_type_id int|string the activitiy types we want to restrict by
*
* @return array (reference) $values the relevant data object values of open activitie
*
* @access public
* @static
*/
static function &getActivities($input)
{
//step 1: Get the basic activity data
$bulkActivityTypeID = CRM_Core_OptionGroup::getValue('activity_type', 'Bulk Email', 'name');
$config = CRM_Core_Config::singleton();
$randomNum = md5(uniqid());
$activityTempTable = "civicrm_temp_activity_details_{$randomNum}";
$tableFields = array('activity_id' => 'int unsigned', 'activity_date_time' => 'datetime', 'status_id' => 'int unsigned', 'subject' => 'varchar(255)', 'source_contact_id' => 'int unsigned', 'source_record_id' => 'int unsigned', 'source_contact_name' => 'varchar(255)', 'activity_type_id' => 'int unsigned', 'activity_type' => 'varchar(128)', 'case_id' => 'int unsigned', 'case_subject' => 'varchar(255)', 'campaign_id' => 'int unsigned');
$sql = "CREATE TEMPORARY TABLE {$activityTempTable} ( ";
$insertValueSQL = array();
foreach ($tableFields as $name => $desc) {
$sql .= "{$name} {$desc},\n";
$insertValueSQL[] = $name;
}
$sql .= "\n PRIMARY KEY ( activity_id )\n ) ENGINE=HEAP DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci\n ";
CRM_Core_DAO::executeQuery($sql);
$insertSQL = "INSERT INTO {$activityTempTable} (" . implode(',', $insertValueSQL) . " ) ";
$order = $limit = $groupBy = '';
//$groupBy = " GROUP BY tbl.activity_id";
if (!empty($input['sort'])) {
if (is_a($input['sort'], 'CRM_Utils_Sort')) {
$orderBy = $input['sort']->orderBy();
if (!empty($orderBy)) {
$order = " ORDER BY {$orderBy}";
}
} elseif (trim($input['sort'])) {
$order = " ORDER BY {$input['sort']}";
}
}
if (empty($order)) {
$order = CRM_Utils_Array::value('context', $input) == 'activity' ? " ORDER BY tbl.activity_date_time desc " : " ORDER BY tbl.status_id asc, tbl.activity_date_time asc ";
}
if (!empty($input['rowCount']) && $input['rowCount'] > 0) {
$limit = " LIMIT {$input['offset']}, {$input['rowCount']} ";
}
$input['count'] = FALSE;
list($sqlClause, $params) = self::getActivitySQLClause($input);
$query = "{$insertSQL}\n SELECT DISTINCT tbl.* from ( {$sqlClause} )\nas tbl ";
//filter case activities - CRM-5761
$components = self::activityComponents();
if (!in_array('CiviCase', $components)) {
$query .= "\nLEFT JOIN civicrm_case_activity ON ( civicrm_case_activity.activity_id = tbl.activity_id )\n WHERE civicrm_case_activity.id IS NULL";
}
$query = $query . $groupBy . $order . $limit;
$dao = CRM_Core_DAO::executeQuery($query, $params);
$notbulkActivityClause = '';
if ($bulkActivityTypeID) {
$notbulkActivityClause = " AND {$activityTempTable}.activity_type_id <> {$bulkActivityTypeID} ";
}
// step 2: Get target and assignee contacts for above activities
// create temp table for target contacts
$activityTargetContactTempTable = "civicrm_temp_target_contact_{$randomNum}";
$query = "CREATE TEMPORARY TABLE {$activityTargetContactTempTable} (\n activity_id int unsigned, target_contact_id int unsigned, target_contact_name varchar(255) )\n ENGINE=MYISAM DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci";
CRM_Core_DAO::executeQuery($query);
// note that we ignore bulk email for targets, since we don't show it in selector
$query = "INSERT INTO {$activityTargetContactTempTable} ( activity_id, target_contact_id, target_contact_name )\n SELECT at.activity_id,\n at.target_contact_id ,\n c.sort_name\n FROM civicrm_activity_target at\n INNER JOIN {$activityTempTable} ON ( at.activity_id = {$activityTempTable}.activity_id\n {$notbulkActivityClause} )\n INNER JOIN civicrm_contact c ON c.id = at.target_contact_id\n WHERE c.is_deleted = 0";
CRM_Core_DAO::executeQuery($query);
// create temp table for assignee contacts
$activityAssigneetContactTempTable = "civicrm_temp_assignee_contact_{$randomNum}";
$query = "CREATE TEMPORARY TABLE {$activityAssigneetContactTempTable} (\n activity_id int unsigned, assignee_contact_id int unsigned, assignee_contact_name varchar(255) )\n ENGINE=MYISAM DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci";
CRM_Core_DAO::executeQuery($query);
// note that we ignore bulk email for assignee, since we don't show it in selector
$query = "INSERT INTO {$activityAssigneetContactTempTable} ( activity_id, assignee_contact_id, assignee_contact_name )\n SELECT DISTINCT ( aa.activity_id ) ,\n aa.assignee_contact_id,\n c.sort_name\n FROM civicrm_activity_assignment aa\n INNER JOIN {$activityTempTable} ON ( aa.activity_id = {$activityTempTable}.activity_id\n {$notbulkActivityClause} )\n INNER JOIN civicrm_contact c ON c.id = aa.assignee_contact_id\n WHERE c.is_deleted = 0";
CRM_Core_DAO::executeQuery($query);
// step 3: Combine all temp tables to get final query for activity selector
$query = "\n SELECT {$activityTempTable}.*,\n {$activityTargetContactTempTable}.target_contact_id,{$activityTargetContactTempTable}.target_contact_name,\n {$activityAssigneetContactTempTable}.assignee_contact_id, {$activityAssigneetContactTempTable}.assignee_contact_name\n FROM {$activityTempTable}\n LEFT JOIN {$activityTargetContactTempTable} on {$activityTempTable}.activity_id = {$activityTargetContactTempTable}.activity_id\n LEFT JOIN {$activityAssigneetContactTempTable} on {$activityTempTable}.activity_id = {$activityAssigneetContactTempTable}.activity_id\n ";
$dao = CRM_Core_DAO::executeQuery($query);
//CRM-3553, need to check user has access to target groups.
$mailingIDs = CRM_Mailing_BAO_Mailing::mailingACLIDs();
$accessCiviMail = CRM_Core_Permission::check('access CiviMail') || CRM_Mailing_Info::workflowEnabled() && CRM_Core_Permission::check('create mailings');
//get all campaigns.
$allCampaigns = CRM_Campaign_BAO_Campaign::getCampaigns(NULL, NULL, FALSE, FALSE, FALSE, TRUE);
$values = array();
while ($dao->fetch()) {
$activityID = $dao->activity_id;
$values[$activityID]['activity_id'] = $dao->activity_id;
$values[$activityID]['source_record_id'] = $dao->source_record_id;
$values[$activityID]['activity_type_id'] = $dao->activity_type_id;
$values[$activityID]['activity_type'] = $dao->activity_type;
$values[$activityID]['activity_date_time'] = $dao->activity_date_time;
$values[$activityID]['status_id'] = $dao->status_id;
$values[$activityID]['subject'] = $dao->subject;
$values[$activityID]['source_contact_name'] = $dao->source_contact_name;
$values[$activityID]['source_contact_id'] = $dao->source_contact_id;
$values[$activityID]['campaign_id'] = $dao->campaign_id;
if ($dao->campaign_id) {
$values[$activityID]['campaign'] = $allCampaigns[$dao->campaign_id];
}
if (!CRM_Utils_Array::value('assignee_contact_name', $values[$activityID])) {
$values[$activityID]['assignee_contact_name'] = array();
}
if (!CRM_Utils_Array::value('target_contact_name', $values[$activityID])) {
$values[$activityID]['target_contact_name'] = array();
}
if (!$bulkActivityTypeID || $bulkActivityTypeID != $dao->activity_type_id) {
// build array of target / assignee names
if ($dao->target_contact_id) {
$values[$activityID]['target_contact_name'][$dao->target_contact_id] = $dao->target_contact_name;
}
if ($dao->assignee_contact_id) {
$values[$activityID]['assignee_contact_name'][$dao->assignee_contact_id] = $dao->assignee_contact_name;
}
// case related fields
$values[$activityID]['case_id'] = $dao->case_id;
$values[$activityID]['case_subject'] = $dao->case_subject;
} else {
$values[$activityID]['recipients'] = ts('(recipients)');
$values[$activityID]['mailingId'] = '';
if ($accessCiviMail && in_array($dao->source_record_id, $mailingIDs)) {
$values[$activityID]['mailingId'] = CRM_Utils_System::url('civicrm/mailing/report', "mid={$dao->source_record_id}&reset=1&cid={$dao->source_contact_id}&context=activitySelector");
}
}
}
// add info on whether the related contacts are deleted (CRM-5673)
// FIXME: ideally this should be tied to ACLs
// grab all the related contact ids
$cids = array();
foreach ($values as $value) {
$cids[] = $value['source_contact_id'];
}
$cids = array_filter(array_unique($cids));
// see which of the cids are of deleted contacts
if ($cids) {
$sql = 'SELECT id FROM civicrm_contact WHERE id IN (' . implode(', ', $cids) . ') AND is_deleted = 1';
$dao = CRM_Core_DAO::executeQuery($sql);
$dels = array();
while ($dao->fetch()) {
$dels[] = $dao->id;
}
// hide the deleted contacts
foreach ($values as &$value) {
if (in_array($value['source_contact_id'], $dels)) {
unset($value['source_contact_id'], $value['source_contact_name']);
}
}
}
return $values;
}
/**
* returns all the rows in the given offset and rowCount
*
* @param enum $action the action being performed
* @param int $offset the row number to start from
* @param int $rowCount the number of rows to return
* @param string $sort the sql string that describes the sort order
* @param enum $output what should the result set include (web/email/csv)
*
* @return array rows in the given offset and rowCount
*/
function &getRows($action, $offset, $rowCount, $sort, $output = null)
{
$result = $this->_query->searchQuery($offset, $rowCount, $sort, false, false, false, false, false, $this->_activityClause);
$rows = array();
require_once 'CRM/Mailing/BAO/Mailing.php';
require_once 'CRM/Mailing/Info.php';
$mailingIDs =& CRM_Mailing_BAO_Mailing::mailingACLIDs();
$accessCiviMail = CRM_Core_Permission::check('access CiviMail');
while ($result->fetch()) {
$row = array();
// ignore rows where we dont have an activity id
if (empty($result->activity_id)) {
continue;
}
// the columns we are interested in
foreach (self::$_properties as $property) {
if (isset($result->{$property})) {
$row[$property] = $result->{$property};
}
}
$contactId = CRM_Utils_Array::value('contact_id', $row);
if (!$contactId) {
$contactId = CRM_Utils_Array::value('source_contact_id', $row);
}
$row['target_contact_name'] = CRM_Activity_BAO_ActivityTarget::getTargetNames($row['activity_id']);
$row['assignee_contact_name'] = CRM_Activity_BAO_ActivityAssignment::getAssigneeNames($row['activity_id']);
if (CRM_Utils_Array::value('source_contact_id', $row)) {
$row['source_contact_name'] = CRM_Contact_BAO_Contact::displayName($row['source_contact_id']);
}
if ($this->_context == 'search') {
$row['checkbox'] = CRM_Core_Form::CB_PREFIX . $result->activity_id;
}
require_once 'CRM/Contact/BAO/Contact/Utils.php';
$row['contact_type'] = CRM_Contact_BAO_Contact_Utils::getImage($result->contact_sub_type ? $result->contact_sub_type : $result->contact_type, false, $result->contact_id);
$accessMailingReport = false;
$activityType = CRM_Core_PseudoConstant::activityType(true, true);
$activityTypeId = CRM_Utils_Array::key($row['activity_type'], $activityType);
if ($row['activity_is_test']) {
$row['activity_type'] = $row['activity_type'] . " (test)";
}
$bulkActivityTypeID = CRM_Utils_Array::key('Bulk Email', $activityType);
if ($accessCiviMail && in_array($result->source_record_id, $mailingIDs) && $bulkActivityTypeID == $activityTypeId) {
$row['mailingId'] = CRM_Utils_System::url('civicrm/mailing/report', "mid={$result->source_record_id}&reset=1&cid={$result->source_contact_id}&context=activitySelector");
$row['recipients'] = ts('(recipients)');
$row['target_contact_name'] = '';
$row['assignee_contact_name'] = '';
$accessMailingReport = true;
}
require_once 'CRM/Activity/Selector/Activity.php';
$activityActions = new CRM_Activity_Selector_Activity($result->contact_id, null);
$actionLinks = $activityActions->actionLinks($activityTypeId, CRM_Utils_Array::value('source_record_id', $row), $accessMailingReport, CRM_Utils_Array::value('activity_id', $row), $this->_key, $this->_compContext);
$row['action'] = CRM_Core_Action::formLink($actionLinks, null, array('id' => $result->activity_id, 'cid' => $contactId, 'cxt' => $this->_context));
$rows[] = $row;
}
return $rows;
}
