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
17 class CRM_Report_Form_Contribute_RecurSummary
extends CRM_Report_Form
{
21 public function __construct() {
24 'civicrm_contribution_recur' => [
25 'dao' => 'CRM_Contribute_DAO_ContributionRecur',
31 'payment_instrument_id' => [
32 'title' => ts('Payment Instrument'),
37 'title' => ts('Started'),
42 'title' => ts('Cancelled'),
46 'contribution_status_id' => [
47 'title' => ts('Active'),
52 'title' => ts('Total Amount'),
59 'title' => ts('Start Date'),
60 'operatorType' => CRM_Report_Form
::OP_DATETIME
,
61 'type' => CRM_Utils_Type
::T_TIME
,
66 $this->_currencyColumn
= 'civicrm_contribution_recur_currency';
67 parent
::__construct();
75 public function setDefaultValues($freeze = TRUE) {
76 return parent
::setDefaultValues($freeze);
79 public function select() {
80 // @todo remove & only adjust parent with selectWhere fn (if needed)
82 $this->_columnHeaders
= [];
83 foreach ($this->_columns
as $tableName => $table) {
84 if (array_key_exists('group_bys', $table)) {
85 foreach ($table['group_bys'] as $fieldName => $field) {
86 if (!empty($this->_params
['group_bys'][$fieldName])) {
87 switch (CRM_Utils_Array
::value($fieldName, $this->_params
['group_bys_freq'])) {
89 $select[] = "DATE_SUB({$field['dbAlias']}, INTERVAL WEEKDAY({$field['dbAlias']}) DAY) AS {$tableName}_{$fieldName}_start";
90 $select[] = "YEARWEEK({$field['dbAlias']}) AS {$tableName}_{$fieldName}_subtotal";
91 $select[] = "WEEKOFYEAR({$field['dbAlias']}) AS {$tableName}_{$fieldName}_interval";
92 $field['title'] = 'Week';
96 $select[] = "MAKEDATE(YEAR({$field['dbAlias']}), 1) AS {$tableName}_{$fieldName}_start";
97 $select[] = "YEAR({$field['dbAlias']}) AS {$tableName}_{$fieldName}_subtotal";
98 $select[] = "YEAR({$field['dbAlias']}) AS {$tableName}_{$fieldName}_interval";
99 $field['title'] = 'Year';
103 $select[] = "DATE_SUB({$field['dbAlias']}, INTERVAL (DAYOFMONTH({$field['dbAlias']})-1) DAY) as {$tableName}_{$fieldName}_start";
104 $select[] = "MONTH({$field['dbAlias']}) AS {$tableName}_{$fieldName}_subtotal";
105 $select[] = "MONTHNAME({$field['dbAlias']}) AS {$tableName}_{$fieldName}_interval";
106 $field['title'] = 'Month';
110 $select[] = "STR_TO_DATE(CONCAT( 3 * QUARTER( {$field['dbAlias']} ) -2 , '/', '1', '/', YEAR( {$field['dbAlias']} ) ), '%m/%d/%Y') AS {$tableName}_{$fieldName}_start";
111 $select[] = "QUARTER({$field['dbAlias']}) AS {$tableName}_{$fieldName}_subtotal";
112 $select[] = "QUARTER({$field['dbAlias']}) AS {$tableName}_{$fieldName}_interval";
113 $field['title'] = 'Quarter';
116 if (!empty($this->_params
['group_bys_freq'][$fieldName])) {
117 $this->_interval
= $field['title'];
118 $this->_columnHeaders
["{$tableName}_{$fieldName}_start"]['title'] = $field['title'] . ' Beginning';
119 $this->_columnHeaders
["{$tableName}_{$fieldName}_start"]['type'] = $field['type'];
120 $this->_columnHeaders
["{$tableName}_{$fieldName}_start"]['group_by'] = $this->_params
['group_bys_freq'][$fieldName];
122 // just to make sure these values are transfered to rows.
123 // since we need that for calculation purpose,
124 // e.g making subtotals look nicer or graphs
125 $this->_columnHeaders
["{$tableName}_{$fieldName}_interval"] = ['no_display' => TRUE];
126 $this->_columnHeaders
["{$tableName}_{$fieldName}_subtotal"] = ['no_display' => TRUE];
132 if (array_key_exists('fields', $table)) {
133 foreach ($table['fields'] as $fieldName => $field) {
134 if (!empty($field['required']) ||
135 !empty($this->_params
['fields'][$fieldName])
138 // only include statistics columns if set
139 if (!empty($field['statistics'])) {
140 foreach ($field['statistics'] as $stat => $label) {
141 switch (strtolower($stat)) {
143 $select[] = "SUM({$field['dbAlias']}) as {$tableName}_{$fieldName}_{$stat}";
144 $this->_columnHeaders
["{$tableName}_{$fieldName}_{$stat}"]['title'] = $label;
145 $this->_columnHeaders
["{$tableName}_{$fieldName}_{$stat}"]['type'] = $field['type'];
146 $this->_statFields
[] = "{$tableName}_{$fieldName}_{$stat}";
150 $select[] = "COUNT({$field['dbAlias']}) as {$tableName}_{$fieldName}_{$stat}";
151 $this->_columnHeaders
["{$tableName}_{$fieldName}_{$stat}"]['type'] = CRM_Utils_Type
::T_INT
;
152 $this->_columnHeaders
["{$tableName}_{$fieldName}_{$stat}"]['title'] = $label;
153 $this->_statFields
[] = "{$tableName}_{$fieldName}_{$stat}";
157 $select[] = "ROUND(AVG({$field['dbAlias']}),2) as {$tableName}_{$fieldName}_{$stat}";
158 $this->_columnHeaders
["{$tableName}_{$fieldName}_{$stat}"]['type'] = $field['type'];
159 $this->_columnHeaders
["{$tableName}_{$fieldName}_{$stat}"]['title'] = $label;
160 $this->_statFields
[] = "{$tableName}_{$fieldName}_{$stat}";
166 $select[] = "{$field['dbAlias']} as {$tableName}_{$fieldName}";
167 $this->_columnHeaders
["{$tableName}_{$fieldName}"]['type'] = $field['type'] ??
NULL;
168 $this->_columnHeaders
["{$tableName}_{$fieldName}"]['title'] = $field['title'] ??
NULL;
174 $this->_selectClauses
= $select;
176 $this->_select
= "SELECT " . implode(', ', $select) . " ";
179 public function from() {
180 $softCreditJoin = "LEFT";
181 if (!empty($this->_params
['fields']['soft_amount']) &&
182 empty($this->_params
['fields']['total_amount'])
184 // if its only soft credit stats, use inner join
185 $softCreditJoin = "INNER";
189 FROM civicrm_contribution_recur {$this->_aliases['civicrm_contribution_recur']}
193 public function postProcess() {
194 $this->beginPostProcess();
195 $sql = $this->buildQuery(TRUE);
198 $this->buildRows($sql, $rows);
199 $this->formatDisplay($rows);
200 $this->doTemplateAssignment($rows);
201 $this->endPostProcess($rows);
204 public function groupBy() {
205 $this->_groupBy
= CRM_Contact_BAO_Query
::getGroupByFromSelectColumns($this->_selectClauses
, "{$this->_aliases['civicrm_contribution_recur']}.payment_instrument_id");
209 * Alter display of rows.
211 * Iterate through the rows retrieved via SQL and make changes for display purposes,
212 * such as rendering contacts as links.
215 * Rows generated by SQL, with an array for each row.
217 public function alterDisplay(&$rows) {
219 $paymentInstruments = CRM_Contribute_PseudoConstant
::paymentInstrument();
223 $startDateFrom = $this->_params
["start_date_to"] ??
NULL;
224 $startDateTo = $this->_params
["start_date_from"] ??
NULL;
225 $startDateRelative = $this->_params
["start_date_relative"] ??
NULL;
227 $startedDateSql = $this->dateClause('start_date', $startDateRelative, $startDateFrom, $startDateTo);
228 $startedDateSql = $startedDateSql ?
$startedDateSql : " ( 1 ) ";
230 $cancelledDateSql = $this->dateClause('cancel_date', $startDateRelative, $startDateFrom, $startDateTo);
231 $cancelledDateSql = $cancelledDateSql ?
$cancelledDateSql : " ( cancel_date IS NOT NULL ) ";
233 $started = $cancelled = $active = $total = 0;
235 foreach ($rows as $rowNum => $row) {
237 $paymentInstrumentId = $row['civicrm_contribution_recur_payment_instrument_id'] ??
NULL;
239 $rows[$rowNum]['civicrm_contribution_recur_start_date'] = 0;
240 $rows[$rowNum]['civicrm_contribution_recur_cancel_date'] = 0;
241 $rows[$rowNum]['civicrm_contribution_recur_contribution_status_id'] = 0;
243 $startedSql = "SELECT count(*) as count FROM civicrm_contribution_recur WHERE payment_instrument_id = $paymentInstrumentId AND $startedDateSql ";
245 $startedDao = CRM_Core_DAO
::executeQuery($startedSql);
246 $startedDao->fetch();
248 $rows[$rowNum]['civicrm_contribution_recur_start_date'] = $startedDao->count
;
249 $started = $started +
$startedDao->count
;
251 $cancelledSql = "SELECT count(*) as count FROM civicrm_contribution_recur WHERE payment_instrument_id = $paymentInstrumentId AND $cancelledDateSql ";
253 $cancelledDao = CRM_Core_DAO
::executeQuery($cancelledSql);
254 $cancelledDao->fetch();
256 $rows[$rowNum]['civicrm_contribution_recur_cancel_date'] = $cancelledDao->count
;
258 $cancelled = $cancelled +
$cancelledDao->count
;
260 $activeSql = "SELECT count(*) as count FROM civicrm_contribution_recur WHERE payment_instrument_id = $paymentInstrumentId";
261 list($from, $to) = $this->getFromTo($startDateRelative, $startDateFrom, $startDateTo);
262 // To find active recurring contribution start date must be >= to start of selected date-range AND
263 // end date or cancel date must be >= to end of selected date-range if NOT null OR end date is null
265 $activeSql .= " AND start_date >= '{$from}'";
268 $activeSql .= " AND (
269 ( end_date >= '{$to}' AND end_date IS NOT NULL ) OR
270 ( cancel_date >= '{$to}' AND cancel_date IS NOT NULL ) OR
274 $activeDao = CRM_Core_DAO
::executeQuery($activeSql);
277 $rows[$rowNum]['civicrm_contribution_recur_contribution_status_id'] = $activeDao->count
;
279 $active = $active +
$activeDao->count
;
283 SELECT SUM(cc.total_amount) as amount FROM `civicrm_contribution` cc
284 INNER JOIN civicrm_contribution_recur cr ON (cr.id = cc.contribution_recur_id AND cr.payment_instrument_id = {$paymentInstrumentId})
285 WHERE cc.contribution_status_id = 1 AND cc.is_test = 0 AND cc.is_template = 0 AND ";
286 $amountSql .= str_replace("start_date", "cc.`receive_date`", $startedDateSql);
287 $amountDao = CRM_Core_DAO
::executeQuery($amountSql);
289 if ($amountDao->amount
) {
290 $lineTotal = $amountDao->amount
;
293 $rows[$rowNum]['civicrm_contribution_recur_amount'] = CRM_Utils_Money
::format($lineTotal);
295 $total = $total +
$amountDao->amount
;
297 // handle payment instrument id
298 if ($value = CRM_Utils_Array
::value('civicrm_contribution_recur_payment_instrument_id', $row)) {
299 $rows[$rowNum]['civicrm_contribution_recur_payment_instrument_id'] = $paymentInstruments[$value];
303 // skip looking further in rows, if first row itself doesn't
304 // have the column we need
309 // Add total line only if results are available
310 if (count($rows) > 0) {
312 'civicrm_contribution_recur_payment_instrument_id' => '',
313 'civicrm_contribution_recur_start_date' => $started,
314 'civicrm_contribution_recur_cancel_date' => $cancelled,
315 'civicrm_contribution_recur_contribution_status_id' => $active,
316 'civicrm_contribution_recur_amount' => CRM_Utils_Money
::format($total),