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