/**
* Set group by clause.
*/
public function groupBy()
{
$this->_groupBy = "";
$append = FALSE;
if (!empty($this->_params['group_bys']) && is_array($this->_params['group_bys'])) {
foreach ($this->_columns as $tableName => $table) {
if (array_key_exists('group_bys', $table)) {
foreach ($table['group_bys'] as $fieldName => $field) {
if (!empty($this->_params['group_bys'][$fieldName])) {
if (!empty($field['chart'])) {
$this->assign('chartSupported', TRUE);
}
if (!empty($table['group_bys'][$fieldName]['frequency']) && !empty($this->_params['group_bys_freq'][$fieldName])) {
$append = "YEAR({$field['dbAlias']});;";
if (in_array(strtolower($this->_params['group_bys_freq'][$fieldName]), array('year'))) {
$append = '';
}
if ($this->_params['group_bys_freq'][$fieldName] == 'FISCALYEAR') {
$this->_groupBy[] = self::fiscalYearOffset($field['dbAlias']);
} else {
$this->_groupBy[] = "{$append} {$this->_params['group_bys_freq'][$fieldName]}({$field['dbAlias']})";
}
$append = TRUE;
} else {
$this->_groupBy[] = $field['dbAlias'];
}
}
}
}
}
if (!empty($this->_statFields) && ($append && count($this->_groupBy) <= 1 || !$append) && !$this->_having) {
$this->_rollup = " WITH ROLLUP";
}
$groupBy = array();
foreach ($this->_groupBy as $key => $val) {
if (strpos($val, ';;') !== FALSE) {
$groupBy = array_merge($groupBy, explode(';;', $val));
} else {
$groupBy[] = $this->_groupBy[$key];
}
}
$this->_groupBy = "GROUP BY " . implode(', ', $groupBy);
} else {
$groupBy = "{$this->_aliases['civicrm_contact']}.id";
$this->_groupBy = "GROUP BY {$this->_aliases['civicrm_contact']}.id";
}
$this->_groupBy .= $this->_rollup;
// append select with ANY_VALUE() keyword
$this->_select = CRM_Contact_BAO_Query::appendAnyValueToSelect($this->_selectClauses, $groupBy);
}
public function groupBy()
{
$this->assign('chartSupported', TRUE);
$this->_rollup = " WITH ROLLUP";
$this->_select = CRM_Contact_BAO_Query::appendAnyValueToSelect($this->_selectClauses, "{$this->_aliases['civicrm_event']}.id");
$this->_groupBy = " GROUP BY {$this->_aliases['civicrm_event']}.id {$this->_rollup}";
}
/**
* Calculate section totals.
*
* When "order by" fields are marked as sections, this assigns to the template
* an array of total counts for each section. This data is used by the Smarty
* plugin {sectionTotal}.
*/
public function sectionTotals()
{
// Reports using order_bys with sections must populate $this->_selectAliases in select() method.
if (empty($this->_selectAliases)) {
return;
}
if (!empty($this->_sections)) {
// build the query with no LIMIT clause
$select = str_ireplace('SELECT SQL_CALC_FOUND_ROWS ', 'SELECT ', $this->_select);
$sql = "{$select} {$this->_from} {$this->_where} {$this->_groupBy} {$this->_having} {$this->_orderBy}";
// pull section aliases out of $this->_sections
$sectionAliases = array_keys($this->_sections);
$ifnulls = array();
foreach (array_merge($sectionAliases, $this->_selectAliases) as $alias) {
$ifnulls[] = "ifnull({$alias}, '') as {$alias}";
}
$this->_select = "SELECT " . implode(", ", $ifnulls);
$this->_select = CRM_Contact_BAO_Query::appendAnyValueToSelect($ifnulls, $sectionAliases);
// Group (un-limited) report by all aliases and get counts. This might
// be done more efficiently when the contents of $sql are known, ie. by
// overriding this method in the report class.
$query = $this->_select . ", count(*) as ct from ({$sql}) as subquery group by " . implode(", ", $sectionAliases);
// initialize array of total counts
$totals = array();
$dao = CRM_Core_DAO::executeQuery($query);
while ($dao->fetch()) {
// let $this->_alterDisplay translate any integer ids to human-readable values.
$rows[0] = $dao->toArray();
$this->alterDisplay($rows);
$row = $rows[0];
// add totals for all permutations of section values
$values = array();
$i = 1;
$aliasCount = count($sectionAliases);
foreach ($sectionAliases as $alias) {
$values[] = $row[$alias];
$key = implode(CRM_Core_DAO::VALUE_SEPARATOR, $values);
if ($i == $aliasCount) {
// the last alias is the lowest-level section header; use count as-is
$totals[$key] = $dao->ct;
} else {
// other aliases are higher level; roll count into their total
$totals[$key] += $dao->ct;
}
}
}
$this->assign('sectionTotals', $totals);
}
}
public function groupBy()
{
$this->_groupBy = "";
if (is_array($this->_params['group_bys']) && !empty($this->_params['group_bys'])) {
foreach ($this->_columns as $tableName => $table) {
if (array_key_exists('group_bys', $table)) {
foreach ($table['group_bys'] as $fieldName => $field) {
if (!empty($this->_params['group_bys'][$fieldName])) {
if (!empty($field['chart'])) {
$this->assign('chartSupported', TRUE);
}
if (!empty($table['group_bys'][$fieldName]['frequency']) && !empty($this->_params['group_bys_freq'][$fieldName])) {
$append = "YEAR({$field['dbAlias']})";
if (in_array(strtolower($this->_params['group_bys_freq'][$fieldName]), array('year'))) {
$append = '';
}
$this->_groupBy[] = $append;
$this->_groupBy[] = "{$this->_params['group_bys_freq'][$fieldName]}({$field['dbAlias']})";
$append = TRUE;
} else {
$this->_groupBy[] = $field['dbAlias'];
}
}
}
}
}
$this->_rollup = ' WITH ROLLUP';
$this->_select = CRM_Contact_BAO_Query::appendAnyValueToSelect($this->_selectClauses, array_filter($this->_groupBy));
$this->_groupBy = 'GROUP BY ' . implode(', ', array_filter($this->_groupBy)) . " {$this->_rollup} ";
} else {
$this->_groupBy = CRM_Contact_BAO_Query::getGroupByFromSelectColumns($this->_selectClauses, "{$this->_aliases['civicrm_membership']}.join_date");
}
}
public function groupBy()
{
$this->assign('chartSupported', TRUE);
$fiscalYearOffset = self::fiscalYearOffset("{$this->_aliases['civicrm_contribution']}.receive_date");
$this->_groupBy = "GROUP BY {$this->_aliases['civicrm_contribution']}.contact_id, {$fiscalYearOffset}";
$this->_select = CRM_Contact_BAO_Query::appendAnyValueToSelect($this->_selectClauses, array("{$this->_aliases['civicrm_contribution']}.contact_id", $fiscalYearOffset));
$this->_groupBy .= " {$this->_rollup}";
}
public function sectionTotals()
{
// Reports using order_bys with sections must populate $this->_selectAliases in select() method.
if (empty($this->_selectAliases)) {
return;
}
if (!empty($this->_sections)) {
// pull section aliases out of $this->_sections
$sectionAliases = array_keys($this->_sections);
$ifnulls = array();
foreach (array_merge($sectionAliases, $this->_selectAliases) as $alias) {
$ifnulls[] = "ifnull({$alias}, '') as {$alias}";
}
$this->_select = "SELECT " . implode(", ", $ifnulls);
$this->_select = CRM_Contact_BAO_Query::appendAnyValueToSelect($ifnulls, $sectionAliases);
$query = $this->_select . ", count(DISTINCT civicrm_activity_id) as ct from civireport_activity_temp_target group by " . implode(", ", $sectionAliases);
// initialize array of total counts
$totals = array();
$dao = CRM_Core_DAO::executeQuery($query);
while ($dao->fetch()) {
// let $this->_alterDisplay translate any integer ids to human-readable values.
$rows[0] = $dao->toArray();
$this->alterDisplay($rows);
$row = $rows[0];
// add totals for all permutations of section values
$values = array();
$i = 1;
$aliasCount = count($sectionAliases);
foreach ($sectionAliases as $alias) {
$values[] = $row[$alias];
$key = implode(CRM_Core_DAO::VALUE_SEPARATOR, $values);
if ($i == $aliasCount) {
// the last alias is the lowest-level section header; use count as-is
$totals[$key] = $dao->ct;
} else {
// other aliases are higher level; roll count into their total
$totals[$key] += $dao->ct;
}
}
}
$this->assign('sectionTotals', $totals);
}
}
/**
* 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, tbl.activity_type, tbl.case_id, tbl.case_subject ";
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=InnoDB 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);
$activityFields = array("ac.activity_id", "ac.contact_id", "ac.record_type_id", "c.sort_name", "c.is_deleted");
$select = CRM_Contact_BAO_Query::appendAnyValueToSelect($activityFields, "ac.activity_id");
// 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 )\n{$select}, 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;
}
public function groupBy()
{
$this->_groupBy = "";
$append = FALSE;
if (is_array($this->_params['group_bys']) && !empty($this->_params['group_bys'])) {
foreach ($this->_columns as $tableName => $table) {
if (array_key_exists('group_bys', $table)) {
foreach ($table['group_bys'] as $fieldName => $field) {
if (!empty($this->_params['group_bys'][$fieldName])) {
if (!empty($field['chart'])) {
$this->assign('chartSupported', TRUE);
}
if (!empty($table['group_bys'][$fieldName]['frequency']) && !empty($this->_params['group_bys_freq'][$fieldName])) {
$append = "YEAR({$field['dbAlias']}),";
if (in_array(strtolower($this->_params['group_bys_freq'][$fieldName]), array('year'))) {
$append = '';
}
$this->_groupBy[] = "{$append} {$this->_params['group_bys_freq'][$fieldName]}({$field['dbAlias']})";
$append = TRUE;
} else {
$this->_groupBy[] = $field['dbAlias'];
}
}
}
}
}
if (!empty($this->_statFields) && ($append && count($this->_groupBy) <= 1 || !$append) && !$this->_having) {
$this->_rollup = " WITH ROLLUP";
}
$groupBy = $this->_groupBy;
$this->_groupBy = "GROUP BY " . implode(', ', $this->_groupBy);
} else {
$groupBy = "{$this->_aliases['civicrm_contact']}.id";
$this->_groupBy = "GROUP BY {$groupBy}";
}
$this->_select = CRM_Contact_BAO_Query::appendAnyValueToSelect($this->_selectClauses, $groupBy);
$this->_groupBy .= " {$this->_rollup}";
}
public function sectionTotals()
{
// Reports using order_bys with sections must populate $this->_selectAliases in select() method.
if (empty($this->_selectAliases)) {
return;
}
if (!empty($this->_sections)) {
// build the query with no LIMIT clause
$select = str_ireplace('SELECT SQL_CALC_FOUND_ROWS ', 'SELECT ', $this->_select);
$sql = "{$select} {$this->_from} {$this->_where} {$this->_groupBy} {$this->_having} {$this->_orderBy}";
// pull section aliases out of $this->_sections
$sectionAliases = array_keys($this->_sections);
$ifnulls = array();
foreach (array_merge($sectionAliases, $this->_selectAliases) as $alias) {
$ifnulls[] = "ifnull({$alias}, '') as {$alias}";
}
$this->_select = "SELECT " . implode(", ", $ifnulls);
$this->_select = CRM_Contact_BAO_Query::appendAnyValueToSelect($ifnulls, $sectionAliases);
/* Group (un-limited) report by all aliases and get counts. This might
* be done more efficiently when the contents of $sql are known, ie. by
* overriding this method in the report class.
*/
$addtotals = '';
if (array_search("civicrm_contribution_total_amount_sum", $this->_selectAliases) !== FALSE) {
$addtotals = ", sum(civicrm_contribution_total_amount_sum) as sumcontribs";
$showsumcontribs = TRUE;
}
$query = $this->_select . "{$addtotals}, count(*) as ct from civireport_contribution_detail_temp3 group by " . implode(", ", $sectionAliases);
// initialize array of total counts
$sumcontribs = $totals = array();
$dao = CRM_Core_DAO::executeQuery($query);
while ($dao->fetch()) {
// let $this->_alterDisplay translate any integer ids to human-readable values.
$rows[0] = $dao->toArray();
$this->alterDisplay($rows);
$row = $rows[0];
// add totals for all permutations of section values
$values = array();
$i = 1;
$aliasCount = count($sectionAliases);
foreach ($sectionAliases as $alias) {
$values[] = $row[$alias];
$key = implode(CRM_Core_DAO::VALUE_SEPARATOR, $values);
if ($i == $aliasCount) {
// the last alias is the lowest-level section header; use count as-is
$totals[$key] = $dao->ct;
if ($showsumcontribs) {
$sumcontribs[$key] = $dao->sumcontribs;
}
} else {
// other aliases are higher level; roll count into their total
$totals[$key] = array_key_exists($key, $totals) ? $totals[$key] + $dao->ct : $dao->ct;
if ($showsumcontribs) {
$sumcontribs[$key] = array_key_exists($key, $sumcontribs) ? $sumcontribs[$key] + $dao->sumcontribs : $dao->sumcontribs;
}
}
}
}
if ($showsumcontribs) {
$totalandsum = array();
// ts exception to avoid having ts("%1 %2: %3")
$title = '%1 contributions / soft-credits: %2';
if (CRM_Utils_Array::value('contribution_or_soft_value', $this->_params) == 'contributions_only') {
$title = '%1 contributions: %2';
} elseif (CRM_Utils_Array::value('contribution_or_soft_value', $this->_params) == 'soft_credits_only') {
$title = '%1 soft-credits: %2';
}
foreach ($totals as $key => $total) {
$totalandsum[$key] = ts($title, array(1 => $total, 2 => CRM_Utils_Money::format($sumcontribs[$key])));
}
$this->assign('sectionTotals', $totalandsum);
} else {
$this->assign('sectionTotals', $totals);
}
}
}
public function groupBy()
{
$this->_rollup = 'WITH ROLLUP';
$this->_select = CRM_Contact_BAO_Query::appendAnyValueToSelect($this->selectClause, array("{$this->_aliases['civicrm_contribution_soft']}.contact_id", "constituentname.id"));
$this->_groupBy = "\nGROUP BY {$this->_aliases['civicrm_contribution_soft']}.contact_id, constituentname.id {$this->_rollup}";
}
