Eileen has uploaded a new change for review.
https://gerrit.wikimedia.org/r/265143
Change subject: CRM-17837 Improve Lybunt report.
......................................................................
CRM-17837 Improve Lybunt report.
Add tests for lynbunt results, get custom data working for contacts in it,
remove non-working address group bys,
fix query to be more efficient on a large dataset
Change-Id: If33c0a42c699b07d21f48d7bec8c35a261c54d13
---
M CRM/Report/Form.php
M CRM/Report/Form/Contribute/Lybunt.php
M api/v3/ReportTemplate.php
M api/v3/examples/ReportTemplate/Getrows.php
M api/v3/examples/ReportTemplate/Getstatistics.php
5 files changed, 436 insertions(+), 265 deletions(-)
git pull ssh://gerrit.wikimedia.org:29418/wikimedia/fundraising/crm/civicrm
refs/changes/43/265143/1
diff --git a/CRM/Report/Form.php b/CRM/Report/Form.php
index 4ec83c2..936db07 100644
--- a/CRM/Report/Form.php
+++ b/CRM/Report/Form.php
@@ -297,11 +297,20 @@
protected $_aclWhere = NULL;
/**
- * Array of DAO tables having columns included in SELECT or ORDER BY clause
+ * Array of DAO tables having columns included in SELECT or ORDER BY clause.
+ *
+ * Where has also been added to this although perhaps the 'includes both'
array should have a different name.
*
* @var array
*/
protected $_selectedTables;
+
+ /**
+ * Array of DAO tables having columns included in WHERE or HAVING clause
+ *
+ * @var array
+ */
+ protected $filteredTables;
/**
* Output mode e.g 'print', 'csv', 'pdf'.
@@ -400,6 +409,13 @@
protected $_createNew;
/**
+ * When a grand total row has calculated the status we pop it off to here.
+ *
+ * This allows us to access it from the stats function and avoid
recalculating.
+ */
+ protected $rollupRow = array();
+
+ /**
* SQL being run in this report.
*
* The sql in the report is stored in this variable in order to be displayed
on the developer tab.
@@ -408,6 +424,16 @@
*/
protected $sql;
+
+ /**
+ * SQL being run in this report as an array.
+ *
+ * The sql in the report is stored in this variable in order to be returned
to api & test calls.
+ *
+ * @var string
+ */
+
+ protected $sqlArray;
/**
* Class constructor.
*/
@@ -1255,6 +1281,7 @@
);
$this->assignTabs();
+ $this->sqlArray[] = $sql;
foreach (array('LEFT JOIN') as $term) {
$sql = str_replace($term, '<br>  ' . $term, $sql);
}
@@ -2307,26 +2334,27 @@
* @return bool
*/
public function grandTotal(&$rows) {
- if (!$this->_rollup || ($this->_rollup == '') ||
+ if (!$this->_rollup || ($this->_rollup == '' || count($rows) == 1) ||
($this->_limit && count($rows) >= self::ROW_COUNT_LIMIT)
) {
return FALSE;
}
- $lastRow = array_pop($rows);
+
+ $this->rollupRow = array_pop($rows);
foreach ($this->_columnHeaders as $fld => $val) {
if (!in_array($fld, $this->_statFields)) {
if (!$this->_grandFlag) {
- $lastRow[$fld] = "Grand Total";
+ $this->rollupRow[$fld] = "Grand Total";
$this->_grandFlag = TRUE;
}
else {
- $lastRow[$fld] = "";
+ $this->rollupRow[$fld] = "";
}
}
}
- $this->assign('grandStat', $lastRow);
+ $this->assign('grandStat', $this->rollupRow);
return TRUE;
}
@@ -2874,7 +2902,7 @@
}
if (!empty($orderByField)) {
- $this->_orderByFields[] = $orderByField;
+ $this->_orderByFields[$orderByField['tplField']] = $orderByField;
$orderBys[] = "{$orderByField['dbAlias']} {$orderBy['order']}";
// Record any section headers for assignment to the template
@@ -2897,22 +2925,8 @@
* @return array
*/
public function unselectedSectionColumns() {
- $selectColumns = array();
- foreach ($this->_columns as $tableName => $table) {
- if (array_key_exists('fields', $table)) {
- foreach ($table['fields'] as $fieldName => $field) {
- if (!empty($field['required']) ||
- !empty($this->_params['fields'][$fieldName])
- ) {
-
- $selectColumns["{$tableName}_{$fieldName}"] = 1;
- }
- }
- }
- }
-
if (is_array($this->_sections)) {
- return array_diff_key($this->_sections, $selectColumns);
+ return array_diff_key($this->_sections, $this->getSelectColumns());
}
else {
return array();
@@ -3052,7 +3066,7 @@
$statistics = array();
$count = count($rows);
-
+ // Why do we increment the count for rollup seems to artificially inflate
the count.
if ($this->_rollup && ($this->_rollup != '') && $this->_grandFlag) {
$count++;
}
@@ -3351,6 +3365,15 @@
}
/**
+ * Get the sql used to generate the report.
+ *
+ * @return string
+ */
+ public function getReportSql() {
+ return $this->sqlArray;
+ }
+
+ /**
* Use the form name to create the tpl file name.
*
* @return string
@@ -3463,7 +3486,6 @@
* @param int $rowCount
*/
public function setPager($rowCount = self::ROW_COUNT_LIMIT) {
-
// CRM-14115, over-ride row count if rowCount is specified in URL
if ($this->_dashBoardRowCount) {
$rowCount = $this->_dashBoardRowCount;
@@ -3478,8 +3500,12 @@
'status' => ts('Records') . ' %%StatusMessage%%',
'buttonBottom' => 'PagerBottomButton',
'buttonTop' => 'PagerTopButton',
- 'pageID' => $this->get(CRM_Utils_Pager::PAGE_ID),
);
+ if (!empty($this->controller)) {
+ // This happens when being called from the api Really we want the api
to be able to
+ // pass paging parameters, but at this stage just preventing test
crashes.
+ $params['pageID'] = $this->get(CRM_Utils_Pager::PAGE_ID);
+ }
$pager = new CRM_Utils_Pager($params);
$this->assign_by_ref('pager', $pager);
@@ -3728,6 +3754,7 @@
case 'Money':
$curFilters[$fieldName]['operatorType'] = CRM_Report_Form::OP_FLOAT;
$curFilters[$fieldName]['type'] = CRM_Utils_Type::T_MONEY;
+ $curFields[$fieldName]['type'] = CRM_Utils_Type::T_MONEY;
break;
case 'Float':
@@ -3998,6 +4025,22 @@
}
/**
+ * Check if table name has columns in WHERE or HAVING clause.
+ *
+ * @param string $tableName
+ * Name of table (index of $this->_columns array).
+ *
+ * @return bool
+ */
+ public function isTableFiltered($tableName) {
+ // Cause the array to be generated if not previously done.
+ if (!$this->_selectedTables && ! $this->filteredTables) {
+ $this->selectedTables();
+ }
+ return in_array($tableName, $this->filteredTables);
+ }
+
+ /**
* Fetch array of DAO tables having columns included in SELECT or ORDER BY
clause.
*
* If the array is unset it will be built.
@@ -4044,6 +4087,7 @@
'nnll'
) {
$this->_selectedTables[] = $tableName;
+ $this->filteredTables[] = $tableName;
break;
}
}
@@ -4629,4 +4673,26 @@
}
}
+
+ /**
+ * @return array
+ */
+ public function getSelectColumns()
+ {
+ $selectColumns = array();
+ foreach ($this->_columns as $tableName => $table) {
+ if (array_key_exists('fields', $table)) {
+ foreach ($table['fields'] as $fieldName => $field) {
+ if (!empty($field['required']) ||
+ !empty($this->_params['fields'][$fieldName])
+ ) {
+
+ $selectColumns["{$tableName}_{$fieldName}"] = 1;
+ }
+ }
+ }
+ }
+ return $selectColumns;
+ }
+
}
diff --git a/CRM/Report/Form/Contribute/Lybunt.php
b/CRM/Report/Form/Contribute/Lybunt.php
index cfa16c2..f795127 100644
--- a/CRM/Report/Form/Contribute/Lybunt.php
+++ b/CRM/Report/Form/Contribute/Lybunt.php
@@ -40,16 +40,42 @@
'pieChart' => 'Pie Chart',
);
- protected $_customGroupExtends = array(
- 'Contact',
- 'Individual',
- 'Contribution',
- );
-
public $_drilldownReport = array('contribute/detail' => 'Link to Detail
Report');
protected $lifeTime_from = NULL;
protected $lifeTime_where = NULL;
+ protected $_customGroupExtends = array(
+ 'Contact',
+ 'Individual',
+ 'Household',
+ 'Organization',
+ );
+
+ /**
+ * Report has results.
+ *
+ * Allows us to bypass the later query if no results are available.
+ *
+ * @var bool
+ */
+ protected $hasResults = FALSE;
+ /**
+ * Table containing list of contact IDs.
+ *
+ * @var string
+ */
+ protected $contactTempTable = '';
+
+ /**
+ * Status clause to be added in to both contact based & contribution based
queries.
+ *
+ * The rationale seems to be that we construct a list of contacts and then
show the relevant contributions for them.
+ *
+ * Presumably the clause originally was only status but now type is included
too.
+ *
+ * @var string
+ */
+ protected $_statusClause = '';
/**
*/
@@ -188,7 +214,7 @@
),
),
);
- $this->_columns += $this->addAddressFields();
+ $this->_columns += $this->addAddressFields(FALSE);
$this->_columns += array(
'civicrm_contribution' => array(
'dao' => 'CRM_Contribute_DAO_Contribution',
@@ -199,17 +225,22 @@
'required' => TRUE,
'no_repeat' => TRUE,
),
- 'total_amount' => array(
- 'title' => ts('Total Amount'),
- 'no_display' => TRUE,
- 'required' => TRUE,
- 'no_repeat' => TRUE,
- ),
'receive_date' => array(
'title' => ts('Year'),
'no_display' => TRUE,
'required' => TRUE,
'no_repeat' => TRUE,
+ ),
+ 'last_year_total_amount' => array(
+ 'title' => ts('Last Year Total'),
+ 'default' => TRUE,
+ 'type' => CRM_Utils_Type::T_MONEY,
+ ),
+ 'civicrm_life_time_total' => array(
+ 'title' => ts('Lifetime Total'),
+ 'default' => TRUE,
+ 'type' => CRM_Utils_Type::T_MONEY,
+ 'statistics' => array('sum' => ts('Lifetime total')),
),
),
'filters' => array(
@@ -234,7 +265,7 @@
),
),
'order_bys' => array(
- 'total_amount' => array(
+ 'last_year_total_amount' => array(
'title' => ts('Total amount last year (affects available
columns)'),
'description' => ts('When ordering by this life time amount cannot
be calculated'),
'default' => '0',
@@ -263,134 +294,146 @@
parent::__construct();
}
- public function preProcess() {
- parent::preProcess();
- }
-
- public function select() {
-
- $this->_columnHeaders = $select = array();
- $current_year = $this->_params['yid_value'];
- $previous_year = $current_year - 1;
-
- foreach ($this->_columns as $tableName => $table) {
-
- if (array_key_exists('fields', $table)) {
- foreach ($table['fields'] as $fieldName => $field) {
-
- if (!empty($field['required']) ||
- !empty($this->_params['fields'][$fieldName])
- ) {
- if ($fieldName == 'total_amount') {
- $select[] = "SUM({$field['dbAlias']}) as
{$tableName}_{$fieldName}";
-
- $this->_columnHeaders["{$previous_year}"]['type'] =
$field['type'];
- $this->_columnHeaders["{$previous_year}"]['title'] =
$previous_year;
-
- $this->_columnHeaders["civicrm_life_time_total"]['type'] =
$field['type'];
- $this->_columnHeaders["civicrm_life_time_total"]['title'] =
'LifeTime';;
- }
- elseif ($fieldName == 'receive_date') {
- $select[] = self::fiscalYearOffset($field['dbAlias']) .
- " as {$tableName}_{$fieldName} ";
- }
- else {
- $select[] = "{$field['dbAlias']} as {$tableName}_{$fieldName} ";
- $this->_columnHeaders["{$tableName}_{$fieldName}"]['type'] =
CRM_Utils_Array::value('type', $field);
- $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] =
CRM_Utils_Array::value('title', $field);
- }
-
- if (!empty($field['no_display'])) {
- $this->_columnHeaders["{$tableName}_{$fieldName}"]['no_display']
= TRUE;
- }
- }
- }
- }
+ /**
+ * Build select clause for a single field.
+ *
+ * @param string $tableName
+ * @param string $tableKey
+ * @param string $fieldName
+ * @param string $field
+ *
+ * @return bool
+ */
+ public function selectClause(&$tableName, $tableKey, &$fieldName, &$field) {
+ if ($fieldName == 'last_year_total_amount') {
+ $this->_columnHeaders["{$tableName}_{$fieldName}"] = $field;
+ $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] =
$this->getLastYearColumnTitle();
+ $this->_statFields[$this->getLastYearColumnTitle()] =
"{$tableName}_{$fieldName}";
+ return "SUM(IF(" .
$this->whereClauseLastYear('contribution_civireport.receive_date') . ",
contribution_civireport.total_amount, 0)) as {$tableName}_{$fieldName}";
}
-
- $this->_select = "SELECT " . implode(', ', $select) . " ";
+ if ($fieldName == 'civicrm_life_time_total') {
+ if (!$this->isOrderByLastYearTotal()) {
+ $this->_columnHeaders["{$tableName}_{$fieldName}"] = $field;
+ $this->_statFields[$field['title']] = "{$tableName}_{$fieldName}";
+ return "SUM({$this->_aliases[$tableName]}.total_amount) as
{$tableName}_{$fieldName}";
+ }
+ return "'' as 'hack'";
+ }
+ if ($fieldName == 'receive_date') {
+ return self::fiscalYearOffset($field['dbAlias']) .
+ " as {$tableName}_{$fieldName} ";
+ }
+ return FALSE;
}
+ /**
+ * Get the title for the last year column.
+ */
+ public function getLastYearColumnTitle() {
+ if ($this->getYearFilterType() == 'calendar') {
+ return $this->getCurrentYear() - 1;
+ }
+ return ($this->getCurrentYear() -1) . '/' . ($this->getCurrentYear());
+ }
+
+ /**
+ * Construct from clause.
+ *
+ * On the first run we are creating a table of contacts to include in the
report.
+ *
+ * Once contactTempTable is populated we should avoid using any further
filters that affect
+ * the contacts that should be visible.
+ */
public function from() {
-
- $this->_from = "
- FROM civicrm_contribution {$this->_aliases['civicrm_contribution']}
- INNER JOIN civicrm_contact {$this->_aliases['civicrm_contact']}
- ON {$this->_aliases['civicrm_contact']}.id =
{$this->_aliases['civicrm_contribution']}.contact_id
- {$this->_aclFrom}";
-
- if ($this->isTableSelected('civicrm_email')) {
- $this->_from .= "
- LEFT JOIN civicrm_email {$this->_aliases['civicrm_email']}
- ON {$this->_aliases['civicrm_contact']}.id =
{$this->_aliases['civicrm_email']}.contact_id
- AND {$this->_aliases['civicrm_email']}.is_primary = 1";
+ if (!empty($this->contactTempTable)) {
+ $this->_from = "
+ FROM civicrm_contribution {$this->_aliases['civicrm_contribution']}
+ INNER JOIN $this->contactTempTable restricted_contacts
+ ON restricted_contacts.cid =
{$this->_aliases['civicrm_contribution']}.contact_id
+ AND {$this->_aliases['civicrm_contribution']}.is_test = 0
+ INNER JOIN civicrm_contact {$this->_aliases['civicrm_contact']}
+ ON restricted_contacts.cid =
{$this->_aliases['civicrm_contact']}.id";
+ if ($this->isTableSelected('civicrm_email')) {
+ $this->_from .= "
+ LEFT JOIN civicrm_email {$this->_aliases['civicrm_email']}
+ ON {$this->_aliases['civicrm_contact']}.id =
{$this->_aliases['civicrm_email']}.contact_id
+ AND {$this->_aliases['civicrm_email']}.is_primary = 1";
+ }
+ if ($this->isTableSelected('civicrm_phone')) {
+ $this->_from .= "
+ LEFT JOIN civicrm_phone {$this->_aliases['civicrm_phone']}
+ ON {$this->_aliases['civicrm_contact']}.id =
{$this->_aliases['civicrm_phone']}.contact_id
+ AND {$this->_aliases['civicrm_phone']}.is_primary = 1";
+ }
+ $this->addAddressFromClause();
}
- if ($this->isTableSelected('civicrm_phone')) {
- $this->_from .= "
- LEFT JOIN civicrm_phone {$this->_aliases['civicrm_phone']}
- ON {$this->_aliases['civicrm_contact']}.id =
{$this->_aliases['civicrm_phone']}.contact_id AND
- {$this->_aliases['civicrm_phone']}.is_primary = 1";
- }
- $this->addAddressFromClause();
- }
-
- public function where() {
- $this->_statusClause = "";
- $clauses = array($this->_aliases['civicrm_contribution'] . '.is_test = 0');
- $current_year = $this->_params['yid_value'];
- $previous_year = $current_year - 1;
-
- foreach ($this->_columns as $tableName => $table) {
- if (array_key_exists('filters', $table)) {
- foreach ($table['filters'] as $fieldName => $field) {
- $clause = NULL;
- if ($fieldName == 'yid') {
- $clause = "contribution_civireport.contact_id NOT IN
-(SELECT distinct contri.contact_id FROM civicrm_contribution contri
- WHERE contri.is_test = 0 AND " .
- self::fiscalYearOffset('contri.receive_date') . " =
$current_year) AND contribution_civireport.contact_id IN (SELECT distinct
contri.contact_id FROM civicrm_contribution contri
- WHERE " . self::fiscalYearOffset('contri.receive_date') .
- " = $previous_year AND contri.is_test = 0) AND " .
self::fiscalYearOffset('contribution_civireport.receive_date') . " =
$previous_year";
- }
- elseif (CRM_Utils_Array::value('type', $field) &
CRM_Utils_Type::T_DATE
- ) {
- $relative = CRM_Utils_Array::value("{$fieldName}_relative",
$this->_params);
- $from = CRM_Utils_Array::value("{$fieldName}_from",
$this->_params);
- $to = CRM_Utils_Array::value("{$fieldName}_to", $this->_params);
-
- if ($relative || $from || $to) {
- $clause = $this->dateClause($field['name'], $relative, $from,
$to, $field['type']);
- }
- }
- else {
- $op = CRM_Utils_Array::value("{$fieldName}_op", $this->_params);
- if ($op) {
- $clause = $this->whereClause($field,
- $op,
- CRM_Utils_Array::value("{$fieldName}_value", $this->_params),
- CRM_Utils_Array::value("{$fieldName}_min", $this->_params),
- CRM_Utils_Array::value("{$fieldName}_max", $this->_params)
- );
- if (($fieldName == 'contribution_status_id' ||
- $fieldName == 'financial_type_id') && !empty($clause)
- ) {
- $this->_statusClause .= " AND " . $clause;
- }
- }
- }
-
- if (!empty($clause)) {
- $clauses[] = $clause;
- }
- }
+ else {
+ $this->_from .= "FROM civicrm_contact
{$this->_aliases['civicrm_contact']}
+ INNER JOIN civicrm_contribution
{$this->_aliases['civicrm_contribution']} USE index (received_date)
+ ON {$this->_aliases['civicrm_contribution']}.contact_id =
{$this->_aliases['civicrm_contact']}.id
+ AND {$this->_aliases['civicrm_contribution']}.is_test = 0
+ AND " .
$this->whereClauseLastYear("{$this->_aliases['civicrm_contribution']}.receive_date")
. "
+ {$this->_aclFrom}
+ LEFT JOIN civicrm_contribution cont_exclude ON cont_exclude.contact_id
= {$this->_aliases['civicrm_contact']}.id
+ AND " . $this->whereClauseThisYear('cont_exclude.receive_date')
+ ;
+ if ($this->isTableFiltered('civicrm_email')) {
+ $this->_from .= "
+ LEFT JOIN civicrm_email {$this->_aliases['civicrm_email']}
+ ON {$this->_aliases['civicrm_contact']}.id =
{$this->_aliases['civicrm_email']}.contact_id
+ AND {$this->_aliases['civicrm_email']}.is_primary = 1";
+ }
+ if ($this->isTableFiltered('civicrm_phone')) {
+ $this->_from .= "
+ LEFT JOIN civicrm_phone {$this->_aliases['civicrm_phone']}
+ ON {$this->_aliases['civicrm_contact']}.id =
{$this->_aliases['civicrm_phone']}.contact_id
+ AND {$this->_aliases['civicrm_phone']}.is_primary = 1";
+ }
+ if ($this->isTableFiltered('civicrm_address')) {
+ $this->_from .= "
+ LEFT JOIN civicrm_address {$this->_aliases['civicrm_address']}
+ ON ({$this->_aliases['civicrm_contact']}.id =
{$this->_aliases['civicrm_address']}.contact_id)
+ AND {$this->_aliases['civicrm_address']}.is_primary = 1\n";
}
}
- $this->_where = 'WHERE ' . implode(' AND ', $clauses);
+ }
- if ($this->_aclWhere) {
- $this->_where .= " AND {$this->_aclWhere} ";
+ /**
+ * Generate where clause.
+ *
+ * We are overriding this primarily for 'before-after' handling of the
receive_date placeholder field.
+ *
+ * We call this twice. The first time we are generating a temp table and we
want to do an IS NULL on the
+ * join that draws in contributions from this year. The second time we are
filtering elsewhere (contacts via
+ * the temp table & contributions via selective addition of contributions in
the select function).
+ *
+ * If lifetime total is NOT selected we can add a further filter here to
possibly improve performance
+ * but the benefit if unproven as yet.
+ * $clause =
$this->whereClauseLastYear("{$this->_aliases['civicrm_contribution']}.receive_date");
+ *
+ * @param array $field Field specifications
+ * @param string $op Query operator (not an exact match to sql)
+ * @param mixed $value
+ * @param float $min
+ * @param float $max
+ *
+ * @return null|string
+ */
+ public function whereClause(&$field, $op, $value, $min, $max) {
+ if ($field['name'] == 'receive_date') {
+ $clause = 1;
+ if (empty($this->contactTempTable)) {
+ $this->_whereClauses[] = "cont_exclude.id IS NULL";
+ }
}
+ else {
+ $clause = parent::whereClause($field, $op, $value, $min, $max);
+ }
+ if ($field['name'] == 'contribution_status_id' || $field['name'] ==
'financial_type_id') {
+ $this->_statusClause .= " AND " . $clause;
+ }
+ return $clause;
}
/**
@@ -403,34 +446,89 @@
* @param int $current_year
* @return null|string
*/
- public function whereClauseLastYear($fieldName, $current_year = NULL) {
- if (empty($current_year)) {
- $current_year = $this->_params['yid_value'];
- }
- $previous_year = $current_year - 1;
- if (CRM_Utils_Array::value('yid_op', $this->_params) == 'calendar') {
- $firstDateOfYear = "{$previous_year}-01-01";
- $lastDateOfYear = "{$previous_year}-12-31 23:11:59";
+ public function whereClauseLastYear($fieldName) {
+ return "$fieldName BETWEEN '" . $this->getFirstDateOfPriorRange() . "' AND
'" . $this->getLastDateOfPriorRange() . "'";
+ }
+
+ /**
+ * Generate where clause for last calendar year or fiscal year.
+ *
+ * @todo must be possible to re-use relative dates stuff.
+ *
+ * @param string $fieldName
+ *
+ * @param int $current_year
+ * @return null|string
+ */
+ public function whereClauseThisYear($fieldName, $current_year = NULL) {
+ return "$fieldName BETWEEN '" . $this->getFirstDateOfCurrentRange() . "'
AND '" . $this->getLastDateOfCurrentRange() . "'";
+ }
+
+
+ /**
+ * Get the year value for the current year.
+ *
+ * @return string
+ */
+ public function getCurrentYear() {
+ return $this->_params['yid_value'];
+ }
+
+ /**
+ * Get the date time of the first date in the 'this year' range.
+ *
+ * @return string
+ */
+ public function getFirstDateOfCurrentRange() {
+ $current_year = $this->getCurrentYear();
+ if ($this->getYearFilterType() == 'calendar') {
+ return "{$current_year }-01-01";
}
else {
$fiscalYear = CRM_Core_Config::singleton()->fiscalYearStart;
- $firstDateOfYear =
"{$previous_year}-{$fiscalYear['M']}-{$fiscalYear['d']}";
- $lastDateOfYear = date('Ymdhis', strtotime(date($current_year .
'-m-d'), '- 1 second'));
+ return "{$current_year}-{$fiscalYear['M']}-{$fiscalYear['d']}";
}
- return "$fieldName BETWEEN '{$firstDateOfYear}' AND '{$lastDateOfYear}'";
}
+
+ /**
+ * Get the year value for the current year.
+ *
+ * @return string
+ */
+ public function getYearFilterType() {
+ return CRM_Utils_Array::value('yid_op', $this->_params, 'calendar');
+ }
+
+ /**
+ * Get the date time of the last date in the 'this year' range.
+ *
+ * @return string
+ */
+ public function getLastDateOfCurrentRange() {
+ return date('YmdHis', strtotime('+ 1 year - 1 second',
strtotime($this->getFirstDateOfCurrentRange())));
+ }
+
+ /**
+ * Get the date time of the first date in the 'last year' range.
+ *
+ * @return string
+ */
+ public function getFirstDateOfPriorRange() {
+ return date('YmdHis', strtotime('- 1 year',
strtotime($this->getFirstDateOfCurrentRange())));
+ }
+
+ /**
+ * Get the date time of the last date in the 'last year' range.
+ *
+ * @return string
+ */
+ public function getLastDateOfPriorRange() {
+ return date('YmdHis', strtotime('+ 1 year - 1 second',
strtotime($this->getFirstDateOfPriorRange())));
+ }
+
public function groupBy() {
$this->_groupBy = "GROUP BY
{$this->_aliases['civicrm_contribution']}.contact_id ";
-
- if (!$this->isOrderByLastYearTotal()) {
- // If we are ordering by last year total we can't also get the lifetime
total
- // in the same query without significant re-work so we may as well drop
the
- // expensive clause that supports it.
- $this->_groupBy .= ', ' .
self::fiscalYearOffset($this->_aliases['civicrm_contribution'] .
- '.receive_date');
- }
-
$this->assign('chartSupported', TRUE);
}
@@ -440,112 +538,111 @@
* @return array
*/
public function statistics(&$rows) {
+ // Set rollup to Null to foll the dubious parent:statistics calculation.
+ if (!empty($this->_rollup)) {
+ $this->_rowsFound = $this->_rowsFound -1;
+ $this->_rollup = '';
+ }
+
$statistics = parent::statistics($rows);
if (!empty($rows)) {
- $select = "
- SELECT SUM({$this->_aliases['civicrm_contribution']}.total_amount ) as
amount,
- SUM(IF( " . $this->whereClauseLastYear('receive_date') . ",
total_amount, 0)) as last_year";
-
- $where = "WHERE {$this->_aliases['civicrm_contact']}.id IN (" .
implode(',', $this->_contactIds) . ")
- AND {$this->_aliases['civicrm_contribution']}.is_test = 0
{$this->_statusClause}";
-
- $sql = "{$select} {$this->_from} {$where}";
-
- $dao = CRM_Core_DAO::executeQuery($sql);
- if ($dao->fetch()) {
- $statistics['counts']['last_year_amount'] = array(
- 'value' => $dao->last_year,
- 'title' => 'Total ' . ($this->_params['yid_value'] - 1),
- 'type' => CRM_Utils_Type::T_MONEY,
- );
- $statistics['counts']['amount'] = array(
- 'value' => $dao->amount,
+ if (!empty($this->rollupRow) &&
!empty($this->rollupRow['civicrm_contribution_civicrm_life_time_total'])) {
+ $statistics['counts']['civicrm_contribution_civicrm_life_time_total']
= array(
+ 'value' =>
$this->rollupRow['civicrm_contribution_civicrm_life_time_total'],
'title' => 'Total LifeTime',
'type' => CRM_Utils_Type::T_MONEY,
);
+ }
+ else {
+ $select = "SELECT
SUM({$this->_aliases['civicrm_contribution']}.total_amount) as amount ";
+ $sql = "{$select} {$this->_from} {$this->_where}";
+ $dao = CRM_Core_DAO::executeQuery($sql);
+ if ($dao->fetch()) {
+ $statistics['counts']['amount'] = array(
+ 'value' => $dao->amount,
+ 'title' => 'Total LifeTime',
+ 'type' => CRM_Utils_Type::T_MONEY,
+ );
+ }
}
}
return $statistics;
}
- public function postProcess() {
-
- // get ready with post process params
- $this->beginPostProcess();
-
+ /**
+ * This function is called by both the api (tests) and the UI.
+ */
+ public function beginPostProcessCommon() {
$this->buildQuery();
$this->resetFormSql();
- $rows = $this->_contactIds = array();
- $this->limit();
- $getContacts = "SELECT SQL_CALC_FOUND_ROWS
{$this->_aliases['civicrm_contact']}.id as cid {$this->_from} {$this->_where}
GROUP BY {$this->_aliases['civicrm_contact']}.id {$this->_limit}";
- $this->addToDeveloperTab($getContacts);
- $dao = CRM_Core_DAO::executeQuery($getContacts);
-
- while ($dao->fetch()) {
- $this->_contactIds[] = $dao->cid;
- }
- $dao->free();
+ $this->contactTempTable = 'civicrm_report_temp_lybunt_c_' . date('Ymd_') .
uniqid();
if (empty($this->_params['charts'])) {
$this->setPager();
}
+ $this->limit();
+ $getContacts = "
+ CREATE TEMPORARY TABLE $this->contactTempTable
+ SELECT SQL_CALC_FOUND_ROWS {$this->_aliases['civicrm_contact']}.id as
cid {$this->_from} {$this->_where}
+ GROUP BY {$this->_aliases['civicrm_contact']}.id {$this->_limit}";
+ $this->addToDeveloperTab($getContacts);
+ CRM_Core_DAO::executeQuery($getContacts);
- if (!empty($this->_contactIds) || !empty($this->_params['charts'])) {
- $this->_where = "WHERE {$this->_aliases['civicrm_contact']}.id IN (" .
implode(',', $this->_contactIds) . ")
- AND {$this->_aliases['civicrm_contribution']}.is_test = 0
{$this->_statusClause}";
+ if ($this->isOrderByLastYearTotal()) {
+ $this->_rollup = '';
+ unset($this->_columnHeaders['civicrm_life_time_total']);
+ }
+ // Reset where clauses to be regenerated in postProcess.
+ $this->_whereClauses = array();
+ }
- if ($this->isOrderByLastYearTotal()) {
- $this->_rollup = '';
- $this->_where .= " AND " . $this->whereClauseLastYear('receive_date');
- unset($this->_columnHeaders['civicrm_life_time_total']);
- }
+ /**
+ * 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) {
+ $this->select();
+ $this->from();
+ $this->customDataFrom();
+ $this->where();
+ $this->groupBy();
+ $this->orderBy();
- $sql = "{$this->_select} {$this->_from} {$this->_where}
{$this->_groupBy} {$this->_rollup}";
-
- if (!empty($this->_orderByArray)) {
- $this->_orderBy = str_replace('contact_civireport.',
'civicrm_contact_', "ORDER BY " . implode(', ', $this->_orderByArray));
- $this->_orderBy = str_replace('contribution_civireport.',
'civicrm_contribution_', $this->_orderBy);
- $sql = "SELECT * FROM ( $sql ) as inner_query {$this->_orderBy}";
- }
-
- $this->addToDeveloperTab($sql);
- $dao = CRM_Core_DAO::executeQuery($sql);
- $current_year = $this->_params['yid_value'];
- $previous_year = $current_year - 1;
-
- while ($dao->fetch()) {
-
- if (!$dao->civicrm_contribution_contact_id) {
- continue;
- }
-
- $row = array();
- foreach ($this->_columnHeaders as $key => $value) {
- if (property_exists($dao, $key)) {
- $rows[$dao->civicrm_contribution_contact_id][$key] = $dao->$key;
- }
- }
-
- if ($dao->civicrm_contribution_receive_date) {
- if ($dao->civicrm_contribution_receive_date == $previous_year) {
-
$rows[$dao->civicrm_contribution_contact_id][$dao->civicrm_contribution_receive_date]
= $dao->civicrm_contribution_total_amount;
- }
- }
- else {
-
$rows[$dao->civicrm_contribution_contact_id]['civicrm_life_time_total'] =
$dao->civicrm_contribution_total_amount;
- }
- }
- $dao->free();
+ // 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}";
}
- $this->formatDisplay($rows, FALSE);
+ if ($applyLimit && empty($this->_params['charts'])) {
+ $this->limit();
+ }
- // assign variables to templates
- $this->doTemplateAssignment($rows);
+ $sql = "{$this->_select} {$this->_from} {$this->_where} {$this->_groupBy}
{$this->_having} {$this->_rollup} {$this->_limit}";
- // do print / pdf / instance stuff if needed
- $this->endPostProcess($rows);
+ 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}";
+ }
+
+ CRM_Utils_Hook::alterReportVar('sql', $this, $this);
+ $this->addToDeveloperTab($sql);
+ return $sql;
}
/**
@@ -553,6 +650,7 @@
*/
function resetFormSql() {
$this->sql = '';
+ $this->sqlArray = array();
}
/**
diff --git a/api/v3/ReportTemplate.php b/api/v3/ReportTemplate.php
index bf0cea9..4eaf71e 100644
--- a/api/v3/ReportTemplate.php
+++ b/api/v3/ReportTemplate.php
@@ -154,7 +154,8 @@
$sql = $reportInstance->buildQuery();
$rows = $metadata = $requiredMetadata = array();
$reportInstance->buildRows($sql, $rows);
- $requiredMetadata = array();
+ $reportInstance->formatDisplay($rows);
+
if (isset($params['options']) && !empty($params['options']['metadata'])) {
$requiredMetadata = $params['options']['metadata'];
if (in_array('title', $requiredMetadata)) {
@@ -162,11 +163,14 @@
}
if (in_array('labels', $requiredMetadata)) {
foreach ($reportInstance->_columnHeaders as $key => $header) {
- //would be better just to expect reports to provide titles but reports
are not consistent so we anticipate empty
+ // Would be better just to expect reports to provide titles but
reports are not consistent so we anticipate empty
//NB I think these are already translated
$metadata['metadata']['labels'][$key] = !empty($header['title']) ?
$header['title'] : '';
}
}
+ if (in_array('sql', $requiredMetadata)) {
+ $metadata['metadata']['sql'] = $reportInstance->getReportSql();
+ }
}
return array($rows, $reportInstance, $metadata);
}
diff --git a/api/v3/examples/ReportTemplate/Getrows.php
b/api/v3/examples/ReportTemplate/Getrows.php
index 50d6461..8c79043 100644
--- a/api/v3/examples/ReportTemplate/Getrows.php
+++ b/api/v3/examples/ReportTemplate/Getrows.php
@@ -52,17 +52,20 @@
'0' => array(
'civicrm_contact_sort_name' => 'Default Organization',
'civicrm_contact_id' => '1',
+ 'civicrm_contact_sort_name_link' =>
'/index.php?q=civicrm/report/contact/detail&reset=1&force=1&id_op=eq&id_value=1',
+ 'civicrm_contact_sort_name_hover' => 'View Constituent Detail Report
for this contact.',
),
'1' => array(
'civicrm_contact_sort_name' => 'Second Domain',
'civicrm_contact_id' => '2',
+ 'civicrm_contact_sort_name_link' =>
'/index.php?q=civicrm/report/contact/detail&reset=1&force=1&id_op=eq&id_value=2',
+ 'civicrm_contact_sort_name_hover' => 'View Constituent Detail Report
for this contact.',
),
),
'metadata' => array(
'title' => 'ERROR: Title is not Set',
'labels' => array(
'civicrm_contact_sort_name' => 'Contact Name',
- 'civicrm_contact_id' => 'Internal Contact ID',
),
),
);
diff --git a/api/v3/examples/ReportTemplate/Getstatistics.php
b/api/v3/examples/ReportTemplate/Getstatistics.php
index 93e6fc6..bc98a48 100644
--- a/api/v3/examples/ReportTemplate/Getstatistics.php
+++ b/api/v3/examples/ReportTemplate/Getstatistics.php
@@ -9,7 +9,7 @@
*/
function report_template_getstatistics_example() {
$params = array(
- 'report_id' => 'contribute/recur',
+ 'report_id' => 'contribute/recursummary',
);
try{
@@ -41,7 +41,8 @@
$expectedResult = array(
'is_error' => 0,
'version' => 3,
- 'count' => 2,
+ 'count' => 1,
+ 'id' => 'counts',
'values' => array(
'counts' => array(
'rowCount' => array(
@@ -49,7 +50,6 @@
'value' => 0,
),
),
- 'filters' => array(),
),
);
--
To view, visit https://gerrit.wikimedia.org/r/265143
To unsubscribe, visit https://gerrit.wikimedia.org/r/settings
Gerrit-MessageType: newchange
Gerrit-Change-Id: If33c0a42c699b07d21f48d7bec8c35a261c54d13
Gerrit-PatchSet: 1
Gerrit-Project: wikimedia/fundraising/crm/civicrm
Gerrit-Branch: master
Gerrit-Owner: Eileen <[email protected]>
_______________________________________________
MediaWiki-commits mailing list
[email protected]
https://lists.wikimedia.org/mailman/listinfo/mediawiki-commits
