/**
* Build the report query.
*
* @param bool $applyLimit
*
* @return string
*/
public function buildQuery($applyLimit = TRUE)
{
$this->select();
$this->from();
$this->customDataFrom();
$this->where();
$this->groupBy();
$this->orderBy();
// order_by columns not selected for display need to be included in SELECT
$unselectedSectionColumns = $this->unselectedSectionColumns();
foreach ($unselectedSectionColumns as $alias => $section) {
$this->_select .= ", {$section['dbAlias']} as {$alias}";
}
if ($applyLimit && empty($this->_params['charts'])) {
$this->limit();
}
CRM_Utils_Hook::alterReportVar('sql', $this, $this);
$sql = "{$this->_select} {$this->_from} {$this->_where} {$this->_groupBy} {$this->_having} {$this->_orderBy} {$this->_limit}";
$this->addToDeveloperTab($sql);
return $sql;
}
/**
* Build the report query.
*
* The issue we are hitting is that if we want to do group by & then ORDER BY we have to
* wrap the query in an outer query with the order by - otherwise the group by takes precedent.
* This is an issue when we want to group by contact but order by the maximum aggregate donation.
*
* @param bool $applyLimit
*
* @return string
*/
public function buildQuery($applyLimit = TRUE)
{
// Calling where & select before FROM allows us to build temp tables to use in from.
$this->where();
$this->select();
$this->from();
$this->customDataFrom(empty($this->contactTempTable));
$this->groupBy();
$this->orderBy();
$this->getPermissionedFTQuery($this);
$limitFilter = '';
// order_by columns not selected for display need to be included in SELECT
// This differs from parent in that we are getting those not in order by rather than not in
// sections, as we need to adapt to our contact group by.
$unselectedSectionColumns = array_diff_key($this->_orderByFields, $this->getSelectColumns());
foreach ($unselectedSectionColumns as $alias => $section) {
$this->_select .= ", {$section['dbAlias']} as {$alias}";
}
if ($applyLimit && empty($this->_params['charts'])) {
$this->limit();
}
$sql = "{$this->_select} {$this->_from} {$this->_where} {$limitFilter} {$this->_groupBy} {$this->_having} {$this->_rollup}";
if (!empty($this->_orderByArray)) {
$this->_orderBy = str_replace('contact_civireport.', 'civicrm_contact_', "ORDER BY ISNULL(civicrm_contribution_contact_id), " . implode(', ', $this->_orderByArray));
$this->_orderBy = str_replace('contribution_civireport.', 'civicrm_contribution_', $this->_orderBy);
foreach ($this->_orderByFields as $field) {
$this->_orderBy = str_replace($field['dbAlias'], $field['tplField'], $this->_orderBy);
}
$sql = str_replace('SQL_CALC_FOUND_ROWS', '', $sql);
$sql = "SELECT SQL_CALC_FOUND_ROWS * FROM ( {$sql} ) as inner_query {$this->_orderBy} {$this->_limit}";
}
CRM_Utils_Hook::alterReportVar('sql', $this, $this);
$this->addToDeveloperTab($sql);
return $sql;
}
public function postProcess()
{
// get the acl clauses built before we assemble the query
$this->buildACLClause($this->_aliases['civicrm_contact']);
// get ready with post process params
$this->beginPostProcess();
// build query
$sql = $this->buildQuery();
// main sql statement
$this->select();
$this->from();
$this->customDataFrom();
$this->where();
$this->groupBy();
$this->orderBy();
// order_by columns not selected for display need to be included in SELECT
$unselectedSectionColumns = $this->unselectedSectionColumns();
foreach ($unselectedSectionColumns as $alias => $section) {
$this->_select .= ", {$section['dbAlias']} as {$alias}";
}
if (!empty($applyLimit) && empty($this->_params['charts'])) {
$this->limit();
}
CRM_Utils_Hook::alterReportVar('sql', $this, $this);
// store the duration count in temp table
$this->_tempTableName = CRM_Core_DAO::createTempTableName('civicrm_activity');
// build temporary table column names base on column headers of result
$dbColumns = array();
foreach ($this->_columnHeaders as $fieldName => $dontCare) {
$dbColumns[] = $fieldName . ' VARCHAR(128)';
}
// create temp table to store main result
$tempQuery = "CREATE TEMPORARY TABLE {$this->_tempTableName} (\n id int unsigned NOT NULL AUTO_INCREMENT, " . implode(', ', $dbColumns) . ' , PRIMARY KEY (id))';
CRM_Core_DAO::executeQuery($tempQuery);
// build main report query
$sql = "{$this->_select} {$this->_from} {$this->_where} {$this->_groupBy} {$this->_having} {$this->_orderBy} {$this->_limit}";
// store the result in temporary table
$insertCols = '';
$insertQuery = "INSERT INTO {$this->_tempTableName} ( " . implode(',', array_keys($this->_columnHeaders)) . " )\n{$sql}";
CRM_Core_DAO::executeQuery($insertQuery);
// now build the query for duration sum
$this->from(TRUE);
$this->where(TRUE);
$this->groupBy(FALSE);
// build the query to calulate duration sum
$sql = "SELECT SUM(activity_civireport.duration) as civicrm_activity_duration_total {$this->_from} {$this->_where} {$this->_groupBy} {$this->_having} {$this->_orderBy} {$this->_limit}";
// create temp table to store duration
$this->_tempDurationSumTableName = CRM_Core_DAO::createTempTableName('civicrm_activity');
$tempQuery = "CREATE TEMPORARY TABLE {$this->_tempDurationSumTableName} (\n id int unsigned NOT NULL AUTO_INCREMENT, civicrm_activity_duration_total VARCHAR(128), PRIMARY KEY (id))";
CRM_Core_DAO::executeQuery($tempQuery);
// store the result in temporary table
$insertQuery = "INSERT INTO {$this->_tempDurationSumTableName} (civicrm_activity_duration_total)\n {$sql}";
CRM_Core_DAO::executeQuery($insertQuery);
// build array of result based on column headers. This method also allows
// modifying column headers before using it to build result set i.e $rows.
$rows = array();
$query = "SELECT {$this->_tempTableName}.*, {$this->_tempDurationSumTableName}.civicrm_activity_duration_total\n FROM {$this->_tempTableName} INNER JOIN {$this->_tempDurationSumTableName}\n ON ({$this->_tempTableName}.id = {$this->_tempDurationSumTableName}.id)";
// finally add duration total to column headers
$this->_columnHeaders['civicrm_activity_duration_total'] = array('no_display' => 1);
$this->buildRows($query, $rows);
// format result set.
$this->formatDisplay($rows);
// assign variables to templates
$this->doTemplateAssignment($rows);
//reset the sql building to default, which is used / called during other actions like "add to group"
// now build the query for duration sum
$this->from();
$this->where();
// do print / pdf / instance stuff if needed
$this->endPostProcess($rows);
}
/**
* Over-written to allow pre-constraints
*
* @param boolean $applyLimit
*
* @return string
*/
function buildQuery($applyLimit = TRUE) {
if (empty($this->_params)) {
$this->_params = $this->controller->exportValues($this->_name);
}
$this->storeJoinFiltersArray();
$this->select();
$this->from();
$this->where();
$this->aggregateSelect();
$this->customDataFrom();
if ($this->_preConstrain && !$this->_preConstrained) {
$this->generateTempTable();
$this->_preConstrained = TRUE;
$this->select();
$this->from();
$this->customDataFrom();
$this->constrainedWhere();
$this->aggregateSelect();
}
$this->orderBy();
$this->groupBy();
// order_by columns not selected for display need to be included in SELECT
$unselectedSectionColumns = $this->unselectedSectionColumns();
foreach ($unselectedSectionColumns as $alias => $section) {
$this->_select .= ", {$section['dbAlias']} as {$alias}";
}
if ($applyLimit && !CRM_Utils_Array::value('charts', $this->_params)) {
$this->limit();
}
//4.2 support - method may not exist
if (method_exists('CRM_Utils_Hook', 'alterReportVar')) {
CRM_Utils_Hook::alterReportVar('sql', $this, $this);
}
$sql = "{$this->_select} {$this->_from} {$this->_where} {$this->_groupBy} {$this->_having} {$this->_orderBy} {$this->_limit}";
return $sql;
}
/**
* @param $rows
* @param bool $pager
*/
public function formatDisplay(&$rows, $pager = TRUE)
{
parent::formatDisplay($rows, $pager);
// Format the nested rows.
foreach ($this->_nestedRows as $month => &$mRows) {
foreach ($mRows as $eventId => &$eRows) {
$this->alterDisplay($eRows);
CRM_Utils_Hook::alterReportVar('rows', $eRows, $this);
$this->alterCustomDataDisplay($eRows);
}
}
$this->assign('events', $this->_allEvents);
}
