Commit | Line | Data |
---|---|---|
d4161664 DG |
1 | <?php |
2 | /* | |
3 | +--------------------------------------------------------------------+ | |
bc77d7c0 | 4 | | Copyright CiviCRM LLC. All rights reserved. | |
d4161664 | 5 | | | |
bc77d7c0 TO |
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 | | |
d4161664 DG |
9 | +--------------------------------------------------------------------+ |
10 | */ | |
11 | ||
12 | /** | |
13 | * | |
14 | * @package CRM | |
ca5cec67 | 15 | * @copyright CiviCRM LLC https://civicrm.org/licensing |
d4161664 DG |
16 | */ |
17 | class CRM_Report_Form_Contribute_RecurSummary extends CRM_Report_Form { | |
c86d4e7c | 18 | |
d4161664 DG |
19 | /** |
20 | */ | |
21 | public function __construct() { | |
22 | ||
be2fb01f CW |
23 | $this->_columns = [ |
24 | 'civicrm_contribution_recur' => [ | |
d4161664 | 25 | 'dao' => 'CRM_Contribute_DAO_ContributionRecur', |
be2fb01f CW |
26 | 'fields' => [ |
27 | 'id' => [ | |
d4161664 DG |
28 | 'no_display' => TRUE, |
29 | 'required' => TRUE, | |
be2fb01f CW |
30 | ], |
31 | 'payment_instrument_id' => [ | |
d4161664 DG |
32 | 'title' => ts('Payment Instrument'), |
33 | 'default' => TRUE, | |
34 | 'required' => TRUE, | |
be2fb01f CW |
35 | ], |
36 | 'start_date' => [ | |
d4161664 DG |
37 | 'title' => ts('Started'), |
38 | 'default' => TRUE, | |
39 | 'required' => TRUE, | |
be2fb01f CW |
40 | ], |
41 | 'cancel_date' => [ | |
d4161664 DG |
42 | 'title' => ts('Cancelled'), |
43 | 'default' => TRUE, | |
44 | 'required' => TRUE, | |
be2fb01f CW |
45 | ], |
46 | 'contribution_status_id' => [ | |
d4161664 DG |
47 | 'title' => ts('Active'), |
48 | 'default' => TRUE, | |
49 | 'required' => TRUE, | |
be2fb01f CW |
50 | ], |
51 | 'amount' => [ | |
d4161664 DG |
52 | 'title' => ts('Total Amount'), |
53 | 'default' => TRUE, | |
54 | 'required' => TRUE, | |
be2fb01f CW |
55 | ], |
56 | ], | |
57 | 'filters' => [ | |
58 | 'start_date' => [ | |
d4161664 | 59 | 'title' => ts('Start Date'), |
5aae2a90 | 60 | 'operatorType' => CRM_Report_Form::OP_DATETIME, |
61 | 'type' => CRM_Utils_Type::T_TIME, | |
be2fb01f CW |
62 | ], |
63 | ], | |
64 | ], | |
65 | ]; | |
d4161664 DG |
66 | $this->_currencyColumn = 'civicrm_contribution_recur_currency'; |
67 | parent::__construct(); | |
68 | } | |
69 | ||
1415b394 | 70 | /** |
d4161664 DG |
71 | * @param bool $freeze |
72 | * | |
73 | * @return array | |
74 | */ | |
75 | public function setDefaultValues($freeze = TRUE) { | |
76 | return parent::setDefaultValues($freeze); | |
77 | } | |
78 | ||
79 | public function select() { | |
18f511e2 | 80 | // @todo remove & only adjust parent with selectWhere fn (if needed) |
be2fb01f CW |
81 | $select = []; |
82 | $this->_columnHeaders = []; | |
d4161664 DG |
83 | foreach ($this->_columns as $tableName => $table) { |
84 | if (array_key_exists('group_bys', $table)) { | |
85 | foreach ($table['group_bys'] as $fieldName => $field) { | |
d4161664 DG |
86 | if (!empty($this->_params['group_bys'][$fieldName])) { |
87 | switch (CRM_Utils_Array::value($fieldName, $this->_params['group_bys_freq'])) { | |
88 | case 'YEARWEEK': | |
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'; | |
93 | break; | |
94 | ||
95 | case 'YEAR': | |
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'; | |
100 | break; | |
101 | ||
102 | case 'MONTH': | |
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'; | |
107 | break; | |
108 | ||
109 | case 'QUARTER': | |
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'; | |
114 | break; | |
115 | } | |
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]; | |
121 | ||
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 | |
be2fb01f CW |
125 | $this->_columnHeaders["{$tableName}_{$fieldName}_interval"] = ['no_display' => TRUE]; |
126 | $this->_columnHeaders["{$tableName}_{$fieldName}_subtotal"] = ['no_display' => TRUE]; | |
d4161664 DG |
127 | } |
128 | } | |
129 | } | |
130 | } | |
131 | ||
132 | if (array_key_exists('fields', $table)) { | |
133 | foreach ($table['fields'] as $fieldName => $field) { | |
d4161664 DG |
134 | if (!empty($field['required']) || |
135 | !empty($this->_params['fields'][$fieldName]) | |
136 | ) { | |
137 | ||
138 | // only include statistics columns if set | |
139 | if (!empty($field['statistics'])) { | |
140 | foreach ($field['statistics'] as $stat => $label) { | |
141 | switch (strtolower($stat)) { | |
142 | case 'sum': | |
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}"; | |
147 | break; | |
148 | ||
149 | case 'count': | |
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}"; | |
154 | break; | |
155 | ||
156 | case 'avg': | |
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}"; | |
161 | break; | |
162 | } | |
163 | } | |
164 | } | |
165 | else { | |
166 | $select[] = "{$field['dbAlias']} as {$tableName}_{$fieldName}"; | |
9c1bc317 CW |
167 | $this->_columnHeaders["{$tableName}_{$fieldName}"]['type'] = $field['type'] ?? NULL; |
168 | $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = $field['title'] ?? NULL; | |
d4161664 DG |
169 | } |
170 | } | |
171 | } | |
172 | } | |
173 | } | |
d1641c51 | 174 | $this->_selectClauses = $select; |
d4161664 DG |
175 | |
176 | $this->_select = "SELECT " . implode(', ', $select) . " "; | |
177 | } | |
178 | ||
d4161664 DG |
179 | public function from() { |
180 | $softCreditJoin = "LEFT"; | |
181 | if (!empty($this->_params['fields']['soft_amount']) && | |
182 | empty($this->_params['fields']['total_amount']) | |
183 | ) { | |
184 | // if its only soft credit stats, use inner join | |
185 | $softCreditJoin = "INNER"; | |
186 | } | |
187 | ||
188 | $this->_from = " | |
189 | FROM civicrm_contribution_recur {$this->_aliases['civicrm_contribution_recur']} | |
190 | "; | |
191 | } | |
192 | ||
193 | public function postProcess() { | |
194 | $this->beginPostProcess(); | |
195 | $sql = $this->buildQuery(TRUE); | |
be2fb01f | 196 | $rows = []; |
d4161664 DG |
197 | |
198 | $this->buildRows($sql, $rows); | |
199 | $this->formatDisplay($rows); | |
200 | $this->doTemplateAssignment($rows); | |
201 | $this->endPostProcess($rows); | |
202 | } | |
203 | ||
204 | public function groupBy() { | |
b708c08d | 205 | $this->_groupBy = CRM_Contact_BAO_Query::getGroupByFromSelectColumns($this->_selectClauses, "{$this->_aliases['civicrm_contribution_recur']}.payment_instrument_id"); |
d4161664 DG |
206 | } |
207 | ||
208 | /** | |
209 | * Alter display of rows. | |
210 | * | |
211 | * Iterate through the rows retrieved via SQL and make changes for display purposes, | |
212 | * such as rendering contacts as links. | |
213 | * | |
214 | * @param array $rows | |
215 | * Rows generated by SQL, with an array for each row. | |
216 | */ | |
217 | public function alterDisplay(&$rows) { | |
24782d26 | 218 | |
d4161664 DG |
219 | $paymentInstruments = CRM_Contribute_PseudoConstant::paymentInstrument(); |
220 | ||
221 | $entryFound = FALSE; | |
222 | ||
9c1bc317 CW |
223 | $startDateFrom = $this->_params["start_date_to"] ?? NULL; |
224 | $startDateTo = $this->_params["start_date_from"] ?? NULL; | |
225 | $startDateRelative = $this->_params["start_date_relative"] ?? NULL; | |
d4161664 | 226 | |
5aae2a90 | 227 | $startedDateSql = $this->dateClause('start_date', $startDateRelative, $startDateFrom, $startDateTo); |
228 | $startedDateSql = $startedDateSql ? $startedDateSql : " ( 1 ) "; | |
d4161664 | 229 | |
5aae2a90 | 230 | $cancelledDateSql = $this->dateClause('cancel_date', $startDateRelative, $startDateFrom, $startDateTo); |
231 | $cancelledDateSql = $cancelledDateSql ? $cancelledDateSql : " ( cancel_date IS NOT NULL ) "; | |
24782d26 | 232 | |
d4161664 DG |
233 | $started = $cancelled = $active = $total = 0; |
234 | ||
235 | foreach ($rows as $rowNum => $row) { | |
24782d26 | 236 | |
9c1bc317 | 237 | $paymentInstrumentId = $row['civicrm_contribution_recur_payment_instrument_id'] ?? NULL; |
d4161664 DG |
238 | |
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; | |
24782d26 | 242 | |
5aae2a90 | 243 | $startedSql = "SELECT count(*) as count FROM civicrm_contribution_recur WHERE payment_instrument_id = $paymentInstrumentId AND $startedDateSql "; |
24782d26 | 244 | |
d4161664 DG |
245 | $startedDao = CRM_Core_DAO::executeQuery($startedSql); |
246 | $startedDao->fetch(); | |
247 | ||
248 | $rows[$rowNum]['civicrm_contribution_recur_start_date'] = $startedDao->count; | |
249 | $started = $started + $startedDao->count; | |
250 | ||
5aae2a90 | 251 | $cancelledSql = "SELECT count(*) as count FROM civicrm_contribution_recur WHERE payment_instrument_id = $paymentInstrumentId AND $cancelledDateSql "; |
24782d26 | 252 | |
d4161664 DG |
253 | $cancelledDao = CRM_Core_DAO::executeQuery($cancelledSql); |
254 | $cancelledDao->fetch(); | |
255 | ||
256 | $rows[$rowNum]['civicrm_contribution_recur_cancel_date'] = $cancelledDao->count; | |
257 | ||
258 | $cancelled = $cancelled + $cancelledDao->count; | |
259 | ||
260 | $activeSql = "SELECT count(*) as count FROM civicrm_contribution_recur WHERE payment_instrument_id = $paymentInstrumentId"; | |
5aae2a90 | 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 | |
264 | if (!empty($from)) { | |
265 | $activeSql .= " AND start_date >= '{$from}'"; | |
d4161664 | 266 | } |
5aae2a90 | 267 | if (!empty($to)) { |
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 | |
271 | end_date IS NULL )"; | |
d4161664 | 272 | } |
24782d26 | 273 | |
d4161664 DG |
274 | $activeDao = CRM_Core_DAO::executeQuery($activeSql); |
275 | $activeDao->fetch(); | |
276 | ||
277 | $rows[$rowNum]['civicrm_contribution_recur_contribution_status_id'] = $activeDao->count; | |
278 | ||
279 | $active = $active + $activeDao->count; | |
24782d26 | 280 | |
d4161664 DG |
281 | $lineTotal = 0; |
282 | $amountSql = " | |
d1641c51 | 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}) | |
9f108b4d | 285 | WHERE cc.contribution_status_id = 1 AND cc.is_test = 0 AND cc.is_template = 0 AND "; |
5aae2a90 | 286 | $amountSql .= str_replace("start_date", "cc.`receive_date`", $startedDateSql); |
d4161664 DG |
287 | $amountDao = CRM_Core_DAO::executeQuery($amountSql); |
288 | $amountDao->fetch(); | |
289 | if ($amountDao->amount) { | |
290 | $lineTotal = $amountDao->amount; | |
291 | } | |
292 | ||
293 | $rows[$rowNum]['civicrm_contribution_recur_amount'] = CRM_Utils_Money::format($lineTotal); | |
294 | ||
295 | $total = $total + $amountDao->amount; | |
296 | ||
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]; | |
300 | $entryFound = TRUE; | |
301 | } | |
302 | ||
303 | // skip looking further in rows, if first row itself doesn't | |
304 | // have the column we need | |
305 | if (!$entryFound) { | |
306 | break; | |
307 | } | |
308 | } | |
309 | // Add total line only if results are available | |
310 | if (count($rows) > 0) { | |
be2fb01f | 311 | $lastRow = [ |
1415b394 | 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), | |
be2fb01f | 317 | ]; |
d4161664 DG |
318 | $rows[] = $lastRow; |
319 | } | |
320 | } | |
24782d26 | 321 | |
d4161664 | 322 | } |