/** * 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; }