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