3 +--------------------------------------------------------------------+
4 | Copyright CiviCRM LLC. All rights reserved. |
6 | This work is published under the GNU AGPLv3 license with some |
7 | permitted exceptions and without any warranty. For full license |
8 | and copyright information, see https://civicrm.org/licensing |
9 +--------------------------------------------------------------------+
15 * @copyright CiviCRM LLC https://civicrm.org/licensing
19 class CRM_Report_Form_Contribute_RecurSummary
extends CRM_Report_Form
{
23 public function __construct() {
26 'civicrm_contribution_recur' => [
27 'dao' => 'CRM_Contribute_DAO_ContributionRecur',
33 'payment_instrument_id' => [
34 'title' => ts('Payment Instrument'),
39 'title' => ts('Started'),
44 'title' => ts('Cancelled'),
48 'contribution_status_id' => [
49 'title' => ts('Active'),
54 'title' => ts('Total Amount'),
61 'title' => ts('Start Date'),
62 'operatorType' => CRM_Report_Form
::OP_DATETIME
,
63 'type' => CRM_Utils_Type
::T_TIME
,
68 $this->_currencyColumn
= 'civicrm_contribution_recur_currency';
69 parent
::__construct();
77 public function setDefaultValues($freeze = TRUE) {
78 return parent
::setDefaultValues($freeze);
81 public function select() {
82 // @todo remove & only adjust parent with selectWhere fn (if needed)
84 $this->_columnHeaders
= [];
85 foreach ($this->_columns
as $tableName => $table) {
86 if (array_key_exists('group_bys', $table)) {
87 foreach ($table['group_bys'] as $fieldName => $field) {
88 if (!empty($this->_params
['group_bys'][$fieldName])) {
89 switch (CRM_Utils_Array
::value($fieldName, $this->_params
['group_bys_freq'])) {
91 $select[] = "DATE_SUB({$field['dbAlias']}, INTERVAL WEEKDAY({$field['dbAlias']}) DAY) AS {$tableName}_{$fieldName}_start";
92 $select[] = "YEARWEEK({$field['dbAlias']}) AS {$tableName}_{$fieldName}_subtotal";
93 $select[] = "WEEKOFYEAR({$field['dbAlias']}) AS {$tableName}_{$fieldName}_interval";
94 $field['title'] = 'Week';
98 $select[] = "MAKEDATE(YEAR({$field['dbAlias']}), 1) AS {$tableName}_{$fieldName}_start";
99 $select[] = "YEAR({$field['dbAlias']}) AS {$tableName}_{$fieldName}_subtotal";
100 $select[] = "YEAR({$field['dbAlias']}) AS {$tableName}_{$fieldName}_interval";
101 $field['title'] = 'Year';
105 $select[] = "DATE_SUB({$field['dbAlias']}, INTERVAL (DAYOFMONTH({$field['dbAlias']})-1) DAY) as {$tableName}_{$fieldName}_start";
106 $select[] = "MONTH({$field['dbAlias']}) AS {$tableName}_{$fieldName}_subtotal";
107 $select[] = "MONTHNAME({$field['dbAlias']}) AS {$tableName}_{$fieldName}_interval";
108 $field['title'] = 'Month';
112 $select[] = "STR_TO_DATE(CONCAT( 3 * QUARTER( {$field['dbAlias']} ) -2 , '/', '1', '/', YEAR( {$field['dbAlias']} ) ), '%m/%d/%Y') AS {$tableName}_{$fieldName}_start";
113 $select[] = "QUARTER({$field['dbAlias']}) AS {$tableName}_{$fieldName}_subtotal";
114 $select[] = "QUARTER({$field['dbAlias']}) AS {$tableName}_{$fieldName}_interval";
115 $field['title'] = 'Quarter';
118 if (!empty($this->_params
['group_bys_freq'][$fieldName])) {
119 $this->_interval
= $field['title'];
120 $this->_columnHeaders
["{$tableName}_{$fieldName}_start"]['title'] = $field['title'] . ' Beginning';
121 $this->_columnHeaders
["{$tableName}_{$fieldName}_start"]['type'] = $field['type'];
122 $this->_columnHeaders
["{$tableName}_{$fieldName}_start"]['group_by'] = $this->_params
['group_bys_freq'][$fieldName];
124 // just to make sure these values are transfered to rows.
125 // since we need that for calculation purpose,
126 // e.g making subtotals look nicer or graphs
127 $this->_columnHeaders
["{$tableName}_{$fieldName}_interval"] = ['no_display' => TRUE];
128 $this->_columnHeaders
["{$tableName}_{$fieldName}_subtotal"] = ['no_display' => TRUE];
134 if (array_key_exists('fields', $table)) {
135 foreach ($table['fields'] as $fieldName => $field) {
136 if (!empty($field['required']) ||
137 !empty($this->_params
['fields'][$fieldName])
140 // only include statistics columns if set
141 if (!empty($field['statistics'])) {
142 foreach ($field['statistics'] as $stat => $label) {
143 switch (strtolower($stat)) {
145 $select[] = "SUM({$field['dbAlias']}) as {$tableName}_{$fieldName}_{$stat}";
146 $this->_columnHeaders
["{$tableName}_{$fieldName}_{$stat}"]['title'] = $label;
147 $this->_columnHeaders
["{$tableName}_{$fieldName}_{$stat}"]['type'] = $field['type'];
148 $this->_statFields
[] = "{$tableName}_{$fieldName}_{$stat}";
152 $select[] = "COUNT({$field['dbAlias']}) as {$tableName}_{$fieldName}_{$stat}";
153 $this->_columnHeaders
["{$tableName}_{$fieldName}_{$stat}"]['type'] = CRM_Utils_Type
::T_INT
;
154 $this->_columnHeaders
["{$tableName}_{$fieldName}_{$stat}"]['title'] = $label;
155 $this->_statFields
[] = "{$tableName}_{$fieldName}_{$stat}";
159 $select[] = "ROUND(AVG({$field['dbAlias']}),2) as {$tableName}_{$fieldName}_{$stat}";
160 $this->_columnHeaders
["{$tableName}_{$fieldName}_{$stat}"]['type'] = $field['type'];
161 $this->_columnHeaders
["{$tableName}_{$fieldName}_{$stat}"]['title'] = $label;
162 $this->_statFields
[] = "{$tableName}_{$fieldName}_{$stat}";
168 $select[] = "{$field['dbAlias']} as {$tableName}_{$fieldName}";
169 $this->_columnHeaders
["{$tableName}_{$fieldName}"]['type'] = CRM_Utils_Array
::value('type', $field);
170 $this->_columnHeaders
["{$tableName}_{$fieldName}"]['title'] = CRM_Utils_Array
::value('title', $field);
176 $this->_selectClauses
= $select;
178 $this->_select
= "SELECT " . implode(', ', $select) . " ";
181 public function from() {
182 $softCreditJoin = "LEFT";
183 if (!empty($this->_params
['fields']['soft_amount']) &&
184 empty($this->_params
['fields']['total_amount'])
186 // if its only soft credit stats, use inner join
187 $softCreditJoin = "INNER";
191 FROM civicrm_contribution_recur {$this->_aliases['civicrm_contribution_recur']}
195 public function postProcess() {
196 $this->beginPostProcess();
197 $sql = $this->buildQuery(TRUE);
200 $this->buildRows($sql, $rows);
201 $this->formatDisplay($rows);
202 $this->doTemplateAssignment($rows);
203 $this->endPostProcess($rows);
206 public function groupBy() {
207 $this->_groupBy
= CRM_Contact_BAO_Query
::getGroupByFromSelectColumns($this->_selectClauses
, "{$this->_aliases['civicrm_contribution_recur']}.payment_instrument_id");
211 * Alter display of rows.
213 * Iterate through the rows retrieved via SQL and make changes for display purposes,
214 * such as rendering contacts as links.
217 * Rows generated by SQL, with an array for each row.
219 public function alterDisplay(&$rows) {
221 $paymentInstruments = CRM_Contribute_PseudoConstant
::paymentInstrument();
225 $startDateFrom = CRM_Utils_Array
::value("start_date_to", $this->_params
);
226 $startDateTo = CRM_Utils_Array
::value("start_date_from", $this->_params
);
227 $startDateRelative = CRM_Utils_Array
::value("start_date_relative", $this->_params
);
229 $startedDateSql = $this->dateClause('start_date', $startDateRelative, $startDateFrom, $startDateTo);
230 $startedDateSql = $startedDateSql ?
$startedDateSql : " ( 1 ) ";
232 $cancelledDateSql = $this->dateClause('cancel_date', $startDateRelative, $startDateFrom, $startDateTo);
233 $cancelledDateSql = $cancelledDateSql ?
$cancelledDateSql : " ( cancel_date IS NOT NULL ) ";
235 $started = $cancelled = $active = $total = 0;
237 foreach ($rows as $rowNum => $row) {
239 $paymentInstrumentId = CRM_Utils_Array
::value('civicrm_contribution_recur_payment_instrument_id', $row);
241 $rows[$rowNum]['civicrm_contribution_recur_start_date'] = 0;
242 $rows[$rowNum]['civicrm_contribution_recur_cancel_date'] = 0;
243 $rows[$rowNum]['civicrm_contribution_recur_contribution_status_id'] = 0;
245 $startedSql = "SELECT count(*) as count FROM civicrm_contribution_recur WHERE payment_instrument_id = $paymentInstrumentId AND $startedDateSql ";
247 $startedDao = CRM_Core_DAO
::executeQuery($startedSql);
248 $startedDao->fetch();
250 $rows[$rowNum]['civicrm_contribution_recur_start_date'] = $startedDao->count
;
251 $started = $started +
$startedDao->count
;
253 $cancelledSql = "SELECT count(*) as count FROM civicrm_contribution_recur WHERE payment_instrument_id = $paymentInstrumentId AND $cancelledDateSql ";
255 $cancelledDao = CRM_Core_DAO
::executeQuery($cancelledSql);
256 $cancelledDao->fetch();
258 $rows[$rowNum]['civicrm_contribution_recur_cancel_date'] = $cancelledDao->count
;
260 $cancelled = $cancelled +
$cancelledDao->count
;
262 $activeSql = "SELECT count(*) as count FROM civicrm_contribution_recur WHERE payment_instrument_id = $paymentInstrumentId";
263 list($from, $to) = $this->getFromTo($startDateRelative, $startDateFrom, $startDateTo);
264 // To find active recurring contribution start date must be >= to start of selected date-range AND
265 // end date or cancel date must be >= to end of selected date-range if NOT null OR end date is null
267 $activeSql .= " AND start_date >= '{$from}'";
270 $activeSql .= " AND (
271 ( end_date >= '{$to}' AND end_date IS NOT NULL ) OR
272 ( cancel_date >= '{$to}' AND cancel_date IS NOT NULL ) OR
276 $activeDao = CRM_Core_DAO
::executeQuery($activeSql);
279 $rows[$rowNum]['civicrm_contribution_recur_contribution_status_id'] = $activeDao->count
;
281 $active = $active +
$activeDao->count
;
285 SELECT SUM(cc.total_amount) as amount FROM `civicrm_contribution` cc
286 INNER JOIN civicrm_contribution_recur cr ON (cr.id = cc.contribution_recur_id AND cr.payment_instrument_id = {$paymentInstrumentId})
287 WHERE cc.contribution_status_id = 1 AND cc.is_test = 0 AND ";
288 $amountSql .= str_replace("start_date", "cc.`receive_date`", $startedDateSql);
289 $amountDao = CRM_Core_DAO
::executeQuery($amountSql);
291 if ($amountDao->amount
) {
292 $lineTotal = $amountDao->amount
;
295 $rows[$rowNum]['civicrm_contribution_recur_amount'] = CRM_Utils_Money
::format($lineTotal);
297 $total = $total +
$amountDao->amount
;
299 // handle payment instrument id
300 if ($value = CRM_Utils_Array
::value('civicrm_contribution_recur_payment_instrument_id', $row)) {
301 $rows[$rowNum]['civicrm_contribution_recur_payment_instrument_id'] = $paymentInstruments[$value];
305 // skip looking further in rows, if first row itself doesn't
306 // have the column we need
311 // Add total line only if results are available
312 if (count($rows) > 0) {
314 'civicrm_contribution_recur_payment_instrument_id' => '',
315 'civicrm_contribution_recur_start_date' => $started,
316 'civicrm_contribution_recur_cancel_date' => $cancelled,
317 'civicrm_contribution_recur_contribution_status_id' => $active,
318 'civicrm_contribution_recur_amount' => CRM_Utils_Money
::format($total),