Commit | Line | Data |
---|---|---|
6a488035 | 1 | <?php |
6a488035 TO |
2 | /* |
3 | +--------------------------------------------------------------------+ | |
bc77d7c0 | 4 | | Copyright CiviCRM LLC. All rights reserved. | |
6a488035 | 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 | | |
6a488035 | 9 | +--------------------------------------------------------------------+ |
d25dd0ee | 10 | */ |
6a488035 TO |
11 | |
12 | /** | |
13 | * | |
14 | * @package CRM | |
ca5cec67 | 15 | * @copyright CiviCRM LLC https://civicrm.org/licensing |
6a488035 TO |
16 | */ |
17 | class CRM_Report_Form_Contribute_History extends CRM_Report_Form { | |
c86d4e7c SL |
18 | /** |
19 | * @var array | |
20 | */ | |
be2fb01f | 21 | protected $_relationshipColumns = []; |
6a488035 | 22 | |
be2fb01f | 23 | protected $_customGroupExtends = [ |
70bea8e2 | 24 | 'Contact', |
25 | 'Individual', | |
7d793900 | 26 | 'Contribution', |
be2fb01f | 27 | ]; |
6a488035 | 28 | |
be2fb01f | 29 | protected $_referenceYear = [ |
6a488035 TO |
30 | 'this_year' => '', |
31 | 'other_year' => '', | |
be2fb01f | 32 | ]; |
6a488035 TO |
33 | protected $_yearStatisticsFrom = ''; |
34 | ||
35 | protected $_yearStatisticsTo = ''; | |
74cf4551 | 36 | |
74cf4551 | 37 | /** |
76c8a771 | 38 | * Class constructor. |
39 | * | |
40 | * @throws \CRM_Core_Exception | |
74cf4551 | 41 | */ |
00be9182 | 42 | public function __construct() { |
70bea8e2 | 43 | $this->_autoIncludeIndexedFieldsAsOrderBys = 1; |
6a488035 | 44 | $yearsInPast = 4; |
9d72cede EM |
45 | $date = CRM_Core_SelectValues::date('custom', NULL, $yearsInPast, 0); |
46 | $count = $date['maxYear']; | |
be2fb01f | 47 | $optionYear = ['' => ts('- select -')]; |
6a488035 TO |
48 | |
49 | $this->_yearStatisticsFrom = $date['minYear']; | |
50 | $this->_yearStatisticsTo = $date['maxYear']; | |
51 | ||
52 | while ($date['minYear'] <= $count) { | |
53 | $optionYear[$date['minYear']] = $date['minYear']; | |
54 | $date['minYear']++; | |
55 | } | |
56 | ||
be2fb01f | 57 | $relationTypeOp = []; |
6a488035 TO |
58 | $relationshipTypes = CRM_Core_PseudoConstant::relationshipType(); |
59 | foreach ($relationshipTypes as $rid => $rtype) { | |
60 | if ($rtype['label_a_b'] != $rtype['label_b_a']) { | |
61 | $relationTypeOp[$rid] = "{$rtype['label_a_b']}/{$rtype['label_b_a']}"; | |
62 | } | |
63 | else { | |
64 | $relationTypeOp[$rid] = $rtype['label_a_b']; | |
65 | } | |
66 | } | |
67 | ||
be2fb01f CW |
68 | $this->_columns = [ |
69 | 'civicrm_contact' => [ | |
c301f76e | 70 | 'dao' => 'CRM_Contact_DAO_Contact', |
be2fb01f CW |
71 | 'fields' => [ |
72 | 'sort_name' => [ | |
c301f76e | 73 | 'title' => ts('Contact Name'), |
74 | 'default' => TRUE, | |
75 | 'required' => TRUE, | |
76 | 'no_repeat' => TRUE, | |
be2fb01f CW |
77 | ], |
78 | 'first_name' => [ | |
70bea8e2 | 79 | 'title' => ts('First Name'), |
be2fb01f CW |
80 | ], |
81 | 'middle_name' => [ | |
70bea8e2 | 82 | 'title' => ts('Middle Name'), |
be2fb01f CW |
83 | ], |
84 | 'last_name' => [ | |
70bea8e2 | 85 | 'title' => ts('Last Name'), |
be2fb01f CW |
86 | ], |
87 | 'id' => [ | |
c301f76e | 88 | 'no_display' => TRUE, |
89 | 'default' => TRUE, | |
90 | 'required' => TRUE, | |
be2fb01f CW |
91 | ], |
92 | 'gender_id' => [ | |
70bea8e2 | 93 | 'title' => ts('Gender'), |
be2fb01f CW |
94 | ], |
95 | 'birth_date' => [ | |
70bea8e2 | 96 | 'title' => ts('Birth Date'), |
be2fb01f CW |
97 | ], |
98 | 'age' => [ | |
70bea8e2 | 99 | 'title' => ts('Age'), |
100 | 'dbAlias' => 'TIMESTAMPDIFF(YEAR, contact_civireport.birth_date, CURDATE())', | |
be2fb01f CW |
101 | ], |
102 | 'contact_type' => [ | |
c301f76e | 103 | 'title' => ts('Contact Type'), |
be2fb01f CW |
104 | ], |
105 | 'contact_sub_type' => [ | |
c301f76e | 106 | 'title' => ts('Contact Subtype'), |
be2fb01f CW |
107 | ], |
108 | ], | |
c301f76e | 109 | 'grouping' => 'contact-fields', |
be2fb01f CW |
110 | 'order_bys' => [ |
111 | 'sort_name' => [ | |
70bea8e2 | 112 | 'title' => ts('Last Name, First Name'), |
113 | 'default' => '1', | |
114 | 'default_weight' => '0', | |
115 | 'default_order' => 'ASC', | |
be2fb01f CW |
116 | ], |
117 | 'first_name' => [ | |
70bea8e2 | 118 | 'name' => 'first_name', |
119 | 'title' => ts('First Name'), | |
be2fb01f CW |
120 | ], |
121 | 'gender_id' => [ | |
70bea8e2 | 122 | 'name' => 'gender_id', |
123 | 'title' => ts('Gender'), | |
be2fb01f CW |
124 | ], |
125 | 'birth_date' => [ | |
70bea8e2 | 126 | 'name' => 'birth_date', |
127 | 'title' => ts('Birth Date'), | |
be2fb01f CW |
128 | ], |
129 | 'contact_type' => [ | |
70bea8e2 | 130 | 'title' => ts('Contact Type'), |
be2fb01f CW |
131 | ], |
132 | 'contact_sub_type' => [ | |
70bea8e2 | 133 | 'title' => ts('Contact Subtype'), |
be2fb01f CW |
134 | ], |
135 | ], | |
136 | 'filters' => [ | |
137 | 'sort_name' => ['title' => ts('Contact Name')], | |
138 | 'id' => [ | |
c301f76e | 139 | 'title' => ts('Contact ID'), |
140 | 'no_display' => TRUE, | |
be2fb01f CW |
141 | ], |
142 | 'gender_id' => [ | |
70bea8e2 | 143 | 'title' => ts('Gender'), |
144 | 'operatorType' => CRM_Report_Form::OP_MULTISELECT, | |
145 | 'options' => CRM_Core_PseudoConstant::get('CRM_Contact_DAO_Contact', 'gender_id'), | |
be2fb01f CW |
146 | ], |
147 | 'birth_date' => [ | |
70bea8e2 | 148 | 'title' => ts('Birth Date'), |
149 | 'operatorType' => CRM_Report_Form::OP_DATE, | |
be2fb01f CW |
150 | ], |
151 | 'contact_type' => [ | |
70bea8e2 | 152 | 'title' => ts('Contact Type'), |
be2fb01f CW |
153 | ], |
154 | 'contact_sub_type' => [ | |
70bea8e2 | 155 | 'title' => ts('Contact Subtype'), |
be2fb01f CW |
156 | ], |
157 | ], | |
158 | ], | |
159 | 'civicrm_email' => [ | |
c301f76e | 160 | 'dao' => 'CRM_Core_DAO_Email', |
be2fb01f CW |
161 | 'fields' => [ |
162 | 'email' => [ | |
c301f76e | 163 | 'title' => ts('Email'), |
164 | 'no_repeat' => TRUE, | |
be2fb01f CW |
165 | ], |
166 | ], | |
101f8739 | 167 | 'filters' => [ |
168 | 'on_hold' => [ | |
169 | 'title' => ts('On Hold'), | |
170 | 'type' => CRM_Utils_Type::T_INT, | |
171 | 'operatorType' => CRM_Report_Form::OP_MULTISELECT, | |
172 | 'options' => ['' => ts('Any')] + CRM_Core_PseudoConstant::emailOnHoldOptions(), | |
173 | ], | |
174 | ], | |
c301f76e | 175 | 'grouping' => 'contact-fields', |
be2fb01f CW |
176 | ], |
177 | 'civicrm_phone' => [ | |
c301f76e | 178 | 'dao' => 'CRM_Core_DAO_Phone', |
be2fb01f CW |
179 | 'fields' => [ |
180 | 'phone' => [ | |
c301f76e | 181 | 'title' => ts('Phone'), |
182 | 'no_repeat' => TRUE, | |
be2fb01f CW |
183 | ], |
184 | ], | |
c301f76e | 185 | 'grouping' => 'contact-fields', |
be2fb01f CW |
186 | ], |
187 | ] + $this->addAddressFields(FALSE, FALSE, FALSE, []) + [ | |
188 | 'civicrm_relationship' => [ | |
c301f76e | 189 | 'dao' => 'CRM_Contact_DAO_Relationship', |
be2fb01f CW |
190 | 'fields' => [ |
191 | 'relationship_type_id' => [ | |
c301f76e | 192 | 'title' => ts('Relationship Type'), |
193 | 'default' => TRUE, | |
be2fb01f CW |
194 | ], |
195 | 'contact_id_a' => ['no_display' => TRUE], | |
196 | 'contact_id_b' => ['no_display' => TRUE], | |
197 | ], | |
198 | 'filters' => [ | |
199 | 'relationship_type_id' => [ | |
c301f76e | 200 | 'title' => ts('Relationship Type'), |
201 | 'operatorType' => CRM_Report_Form::OP_MULTISELECT, | |
202 | 'options' => $relationTypeOp, | |
203 | 'type' => CRM_Utils_Type::T_STRING, | |
be2fb01f CW |
204 | ], |
205 | ], | |
206 | ], | |
207 | ] + [ | |
208 | 'civicrm_contribution' => [ | |
c301f76e | 209 | 'dao' => 'CRM_Contribute_DAO_Contribution', |
be2fb01f CW |
210 | 'fields' => [ |
211 | 'total_amount' => [ | |
c301f76e | 212 | 'title' => ts('Amount Statistics'), |
213 | 'default' => TRUE, | |
214 | 'required' => TRUE, | |
215 | 'no_display' => TRUE, | |
be2fb01f CW |
216 | 'statistics' => ['sum' => ts('Aggregate Amount')], |
217 | ], | |
218 | 'receive_date' => [ | |
c301f76e | 219 | 'required' => TRUE, |
220 | 'default' => TRUE, | |
221 | 'no_display' => TRUE, | |
be2fb01f CW |
222 | ], |
223 | ], | |
c301f76e | 224 | 'grouping' => 'contri-fields', |
be2fb01f CW |
225 | 'filters' => [ |
226 | 'this_year' => [ | |
c301f76e | 227 | 'title' => ts('This Year'), |
228 | 'operatorType' => CRM_Report_Form::OP_SELECT, | |
229 | 'options' => $optionYear, | |
230 | 'default' => '', | |
be2fb01f CW |
231 | ], |
232 | 'other_year' => [ | |
c301f76e | 233 | 'title' => ts('Other Years'), |
234 | 'operatorType' => CRM_Report_Form::OP_SELECT, | |
235 | 'options' => $optionYear, | |
236 | 'default' => '', | |
be2fb01f CW |
237 | ], |
238 | 'receive_date' => ['operatorType' => CRM_Report_Form::OP_DATE], | |
f85b4a88 | 239 | 'receipt_date' => ['operatorType' => CRM_Report_Form::OP_DATE], |
be2fb01f | 240 | 'contribution_status_id' => [ |
c301f76e | 241 | 'title' => ts('Contribution Status'), |
242 | 'operatorType' => CRM_Report_Form::OP_MULTISELECT, | |
c0aaecf9 | 243 | 'options' => CRM_Contribute_BAO_Contribution::buildOptions('contribution_status_id', 'search'), |
be2fb01f CW |
244 | 'default' => [1], |
245 | ], | |
246 | 'financial_type_id' => [ | |
c301f76e | 247 | 'title' => ts('Financial Type'), |
8ee006e7 | 248 | 'type' => CRM_Utils_Type::T_INT, |
c301f76e | 249 | 'operatorType' => CRM_Report_Form::OP_MULTISELECT, |
250 | 'options' => CRM_Contribute_PseudoConstant::financialType(), | |
be2fb01f CW |
251 | ], |
252 | 'total_amount' => [ | |
c301f76e | 253 | 'title' => ts('Contribution Amount'), |
be2fb01f CW |
254 | ], |
255 | 'total_sum' => [ | |
c301f76e | 256 | 'title' => ts('Aggregate Amount'), |
257 | 'type' => CRM_Report_Form::OP_INT, | |
258 | 'dbAlias' => 'civicrm_contribution_total_amount_sum', | |
259 | 'having' => TRUE, | |
be2fb01f CW |
260 | ], |
261 | ], | |
262 | ], | |
263 | ]; | |
264 | $this->_columns += [ | |
265 | 'civicrm_financial_trxn' => [ | |
37e697e7 | 266 | 'dao' => 'CRM_Financial_DAO_FinancialTrxn', |
be2fb01f CW |
267 | 'fields' => [ |
268 | 'card_type_id' => [ | |
d72b084a | 269 | 'title' => ts('Credit Card Type'), |
5e0343e8 | 270 | 'dbAlias' => 'GROUP_CONCAT(financial_trxn_civireport.card_type_id SEPARATOR ",")', |
be2fb01f CW |
271 | ], |
272 | ], | |
273 | 'filters' => [ | |
274 | 'card_type_id' => [ | |
d72b084a | 275 | 'title' => ts('Credit Card Type'), |
37e697e7 | 276 | 'operatorType' => CRM_Report_Form::OP_MULTISELECT, |
5e0343e8 | 277 | 'options' => CRM_Financial_DAO_FinancialTrxn::buildOptions('card_type_id'), |
37e697e7 E |
278 | 'default' => NULL, |
279 | 'type' => CRM_Utils_Type::T_STRING, | |
be2fb01f CW |
280 | ], |
281 | ], | |
282 | ], | |
283 | ]; | |
6a488035 | 284 | |
9d72cede | 285 | $this->_columns['civicrm_contribution']['fields']['civicrm_upto_' . |
be2fb01f CW |
286 | $this->_yearStatisticsFrom] = [ |
287 | 'title' => ts('Up To %1 Donation', [1 => $this->_yearStatisticsFrom]), | |
6a488035 TO |
288 | 'default' => TRUE, |
289 | 'type' => CRM_Utils_Type::T_MONEY, | |
290 | 'is_statistics' => TRUE, | |
be2fb01f | 291 | ]; |
6a488035 TO |
292 | |
293 | $yearConter = $this->_yearStatisticsFrom; | |
294 | $yearConter++; | |
295 | while ($yearConter <= $this->_yearStatisticsTo) { | |
be2fb01f CW |
296 | $this->_columns['civicrm_contribution']['fields'][$yearConter] = [ |
297 | 'title' => ts('%1 Donation', [1 => $yearConter]), | |
6a488035 TO |
298 | 'default' => TRUE, |
299 | 'type' => CRM_Utils_Type::T_MONEY, | |
300 | 'is_statistics' => TRUE, | |
be2fb01f | 301 | ]; |
6a488035 TO |
302 | $yearConter++; |
303 | } | |
304 | ||
be2fb01f | 305 | $this->_columns['civicrm_contribution']['fields']['aggregate_amount'] = [ |
9d72cede | 306 | 'title' => ts('Aggregate Amount'), |
6a488035 TO |
307 | 'type' => CRM_Utils_Type::T_MONEY, |
308 | 'is_statistics' => TRUE, | |
be2fb01f | 309 | ]; |
6a488035 | 310 | |
16e2e80c | 311 | $this->_groupFilter = TRUE; |
6a488035 TO |
312 | $this->_tagFilter = TRUE; |
313 | parent::__construct(); | |
314 | } | |
315 | ||
00be9182 | 316 | public function preProcess() { |
6a488035 TO |
317 | parent::preProcess(); |
318 | } | |
319 | ||
00be9182 | 320 | public function select() { |
be2fb01f | 321 | $select = []; |
3b2bbbfb | 322 | // @todo remove this & use parent (with maybe some override in this or better yet selectWhere fn) |
be2fb01f | 323 | $this->_columnHeaders = []; |
6a488035 TO |
324 | |
325 | foreach ($this->_columns as $tableName => $table) { | |
326 | if (array_key_exists('fields', $table)) { | |
327 | foreach ($table['fields'] as $fieldName => $field) { | |
328 | ||
9d72cede EM |
329 | if (!empty($field['required']) || |
330 | !empty($this->_params['fields'][$fieldName]) | |
331 | ) { | |
6a488035 TO |
332 | if ($tableName == 'civicrm_relationship') { |
333 | $this->_relationshipColumns["{$tableName}_{$fieldName}"] = "{$field['dbAlias']} as {$tableName}_{$fieldName}"; | |
9c1bc317 | 334 | $this->_columnHeaders["{$tableName}_{$fieldName}"]['type'] = $field['type'] ?? NULL; |
6a488035 TO |
335 | $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = $field['title']; |
336 | continue; | |
337 | } | |
338 | ||
a7488080 | 339 | if (!empty($field['is_statistics'])) { |
6a488035 TO |
340 | $this->_columnHeaders[$fieldName]['type'] = $field['type']; |
341 | $this->_columnHeaders[$fieldName]['title'] = $field['title']; | |
342 | continue; | |
343 | } | |
2f4c2f5d | 344 | elseif ($fieldName == 'receive_date') { |
9d72cede EM |
345 | if ((CRM_Utils_Array::value('this_year_op', $this->_params) == |
346 | 'fiscal' && !empty($this->_params['this_year_value'])) || | |
347 | (CRM_Utils_Array::value('other_year_op', $this->_params == | |
348 | 'fiscal') && !empty($this->_params['other_year_value'])) | |
349 | ) { | |
350 | $select[] = self::fiscalYearOffset($field['dbAlias']) . | |
351 | " as {$tableName}_{$fieldName}"; | |
352 | } | |
353 | else { | |
354 | $select[] = " YEAR(" . $field['dbAlias'] . ")" . | |
355 | " as {$tableName}_{$fieldName}"; | |
356 | } | |
6a488035 | 357 | } |
f480d851 DG |
358 | elseif ($fieldName == 'total_amount') { |
359 | $select[] = "SUM({$field['dbAlias']}) as {$tableName}_{$fieldName}"; | |
360 | } | |
6a488035 TO |
361 | else { |
362 | $select[] = "{$field['dbAlias']} as {$tableName}_{$fieldName}"; | |
9c1bc317 | 363 | $this->_columnHeaders["{$tableName}_{$fieldName}"]['type'] = $field['type'] ?? NULL; |
6a488035 TO |
364 | $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = $field['title']; |
365 | } | |
a7488080 | 366 | if (!empty($field['no_display'])) { |
6a488035 TO |
367 | $this->_columnHeaders["{$tableName}_{$fieldName}"]['no_display'] = TRUE; |
368 | } | |
369 | } | |
370 | } | |
371 | } | |
372 | } | |
d1641c51 | 373 | $this->_selectClauses = $select; |
6a488035 TO |
374 | |
375 | $this->_select = "SELECT " . implode(', ', $select) . " "; | |
376 | } | |
377 | ||
00be9182 | 378 | public function from() { |
6a488035 TO |
379 | $this->_from = " |
380 | FROM civicrm_contact {$this->_aliases['civicrm_contact']} | |
2f4c2f5d | 381 | INNER JOIN civicrm_contribution {$this->_aliases['civicrm_contribution']} |
6a488035 | 382 | ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_contribution']}.contact_id AND |
9f108b4d JJ |
383 | {$this->_aliases['civicrm_contribution']}.is_test = 0 AND |
384 | {$this->_aliases['civicrm_contribution']}.is_template = 0"; | |
6a488035 | 385 | |
6a488035 | 386 | $relContacAlias = 'contact_relationship'; |
2f4c2f5d | 387 | $this->_relationshipFrom = " INNER JOIN civicrm_relationship {$this->_aliases['civicrm_relationship']} |
6a488035 TO |
388 | ON (({$this->_aliases['civicrm_relationship']}.contact_id_a = {$relContacAlias}.id OR {$this->_aliases['civicrm_relationship']}.contact_id_b = {$relContacAlias}.id ) AND {$this->_aliases['civicrm_relationship']}.is_active = 1) "; |
389 | ||
3b2bbbfb | 390 | $this->joinAddressFromContact(); |
391 | $this->joinPhoneFromContact(); | |
392 | $this->joinEmailFromContact(); | |
37e697e7 E |
393 | |
394 | // for credit card type | |
395 | $this->addFinancialTrxnFromClause(); | |
6a488035 TO |
396 | } |
397 | ||
00be9182 | 398 | public function where() { |
be2fb01f | 399 | $whereClauses = $havingClauses = $relationshipWhere = []; |
6a488035 | 400 | $this->_relationshipWhere = ''; |
2b4a1828 | 401 | $this->_contributionClauses = []; |
6a488035 TO |
402 | |
403 | foreach ($this->_columns as $tableName => $table) { | |
404 | if (array_key_exists('filters', $table)) { | |
405 | foreach ($table['filters'] as $fieldName => $field) { | |
406 | $clause = NULL; | |
407 | if ($fieldName == 'this_year' || $fieldName == 'other_year') { | |
408 | continue; | |
9d72cede | 409 | } |
84178120 | 410 | elseif (CRM_Utils_Array::value('type', $field) & CRM_Utils_Type::T_DATE |
9d72cede | 411 | ) { |
9c1bc317 CW |
412 | $relative = $this->_params["{$fieldName}_relative"] ?? NULL; |
413 | $from = $this->_params["{$fieldName}_from"] ?? NULL; | |
414 | $to = $this->_params["{$fieldName}_to"] ?? NULL; | |
6a488035 TO |
415 | |
416 | $clause = $this->dateClause($field['name'], $relative, $from, $to, $field['type']); | |
417 | } | |
418 | else { | |
9c1bc317 | 419 | $op = $this->_params["{$fieldName}_op"] ?? NULL; |
6a488035 TO |
420 | if ($op) { |
421 | $clause = $this->whereClause($field, | |
422 | $op, | |
423 | CRM_Utils_Array::value("{$fieldName}_value", $this->_params), | |
424 | CRM_Utils_Array::value("{$fieldName}_min", $this->_params), | |
425 | CRM_Utils_Array::value("{$fieldName}_max", $this->_params) | |
426 | ); | |
427 | } | |
428 | } | |
429 | ||
430 | if (!empty($clause)) { | |
431 | if ($tableName == 'civicrm_relationship') { | |
432 | $relationshipWhere[] = $clause; | |
433 | continue; | |
434 | } | |
435 | ||
2b4a1828 | 436 | // Make contribution filters work. |
437 | // Note total_sum is already accounted for in the main buildRows | |
438 | // and this_year and other_year skip the loop above. | |
439 | if ($tableName == 'civicrm_contribution' && $fieldName != 'total_sum') { | |
440 | $this->_contributionClauses[$fieldName] = $clause; | |
6a488035 TO |
441 | } |
442 | ||
a7488080 | 443 | if (!empty($field['having'])) { |
6a488035 TO |
444 | $havingClauses[] = $clause; |
445 | } | |
446 | else { | |
447 | $whereClauses[] = $clause; | |
448 | } | |
449 | } | |
450 | } | |
451 | } | |
452 | } | |
453 | ||
454 | if (empty($whereClauses)) { | |
455 | $this->_where = "WHERE ( 1 ) "; | |
456 | $this->_having = ""; | |
457 | } | |
458 | else { | |
459 | $this->_where = "WHERE " . implode(' AND ', $whereClauses); | |
460 | } | |
461 | ||
462 | if ($this->_aclWhere) { | |
463 | $this->_where .= " AND {$this->_aclWhere} "; | |
464 | } | |
465 | ||
466 | if (!empty($havingClauses)) { | |
467 | // use this clause to construct group by clause. | |
468 | $this->_having = "HAVING " . implode(' AND ', $havingClauses); | |
469 | } | |
470 | ||
471 | if (!empty($relationshipWhere)) { | |
9d72cede EM |
472 | $this->_relationshipWhere = ' AND ' . |
473 | implode(' AND ', $relationshipWhere); | |
6a488035 TO |
474 | } |
475 | } | |
476 | ||
00be9182 | 477 | public function groupBy() { |
be2fb01f | 478 | $groupBy = [ |
d1641c51 | 479 | "{$this->_aliases['civicrm_contribution']}.contact_id", |
480 | "YEAR({$this->_aliases['civicrm_contribution']}.receive_date)", | |
be2fb01f | 481 | ]; |
b708c08d | 482 | $this->_groupBy = CRM_Contact_BAO_Query::getGroupByFromSelectColumns($this->_selectClauses, $groupBy); |
6a488035 TO |
483 | } |
484 | ||
74cf4551 | 485 | /** |
ab432335 | 486 | * Override to set limit to 10. |
decced8b | 487 | * |
11630f6a | 488 | * @param int|null $rowCount |
decced8b | 489 | * |
ab432335 | 490 | * @return array |
74cf4551 | 491 | */ |
11630f6a SL |
492 | public function limit($rowCount = NULL) { |
493 | $rowCount = $rowCount ?? $this->getRowCount(); | |
ab432335 | 494 | return parent::limit($rowCount); |
6a488035 TO |
495 | } |
496 | ||
74cf4551 | 497 | /** |
ab432335 EM |
498 | * Override to set pager with limit is 10. |
499 | * | |
11630f6a | 500 | * @param int|null $rowCount |
74cf4551 | 501 | */ |
11630f6a SL |
502 | public function setPager($rowCount = NULL) { |
503 | $rowCount = $rowCount ?? $this->getRowCount(); | |
6a488035 TO |
504 | parent::setPager($rowCount); |
505 | } | |
506 | ||
74cf4551 | 507 | /** |
71d8f758 | 508 | * @param array $rows |
74cf4551 EM |
509 | * |
510 | * @return array | |
511 | */ | |
00be9182 | 512 | public function statistics(&$rows) { |
6a488035 TO |
513 | $statistics = parent::statistics($rows); |
514 | $count = 0; | |
515 | foreach ($rows as $rownum => $row) { | |
516 | if (is_numeric($rownum)) { | |
517 | $count++; | |
518 | } | |
519 | } | |
be2fb01f | 520 | $statistics['counts']['rowCount'] = [ |
9d72cede | 521 | 'title' => ts('Primary Contact(s) Listed'), |
6a488035 | 522 | 'value' => $count, |
be2fb01f | 523 | ]; |
6a488035 TO |
524 | |
525 | if ($this->_rowsFound && ($this->_rowsFound > $count)) { | |
be2fb01f | 526 | $statistics['counts']['rowsFound'] = [ |
9d72cede | 527 | 'title' => ts('Total Primary Contact(s)'), |
6a488035 | 528 | 'value' => $this->_rowsFound, |
be2fb01f | 529 | ]; |
6a488035 TO |
530 | } |
531 | ||
532 | return $statistics; | |
533 | } | |
534 | ||
74cf4551 EM |
535 | /** |
536 | * @param $fields | |
537 | * @param $files | |
e8cf95b4 | 538 | * @param self $self |
74cf4551 EM |
539 | * |
540 | * @return array | |
541 | */ | |
00be9182 | 542 | public static function formRule($fields, $files, $self) { |
be2fb01f | 543 | $errors = []; |
9d72cede EM |
544 | if (!empty($fields['this_year_value']) && |
545 | !empty($fields['other_year_value']) && | |
6a488035 TO |
546 | ($fields['this_year_value'] == $fields['other_year_value']) |
547 | ) { | |
548 | $errors['other_year_value'] = ts("Value for filters 'This Year' and 'Other Years' can not be same."); | |
549 | } | |
550 | return $errors; | |
551 | } | |
552 | ||
00be9182 | 553 | public function postProcess() { |
6a488035 TO |
554 | // get ready with post process params |
555 | $this->beginPostProcess(); | |
556 | $this->fixReportParams(); | |
557 | ||
558 | $this->buildACLClause($this->_aliases['civicrm_contact']); | |
559 | $this->select(); | |
560 | $this->where(); | |
561 | $this->from(); | |
7e597787 | 562 | $this->customDataFrom(); |
6a488035 TO |
563 | $this->groupBy(); |
564 | ||
97fa836c | 565 | $sql = NULL; |
be2fb01f | 566 | $rows = []; |
6a488035 TO |
567 | |
568 | // build array of result based on column headers. This method also allows | |
569 | // modifying column headers before using it to build result set i.e $rows. | |
97fa836c | 570 | $this->buildRows($sql, $rows); |
6a488035 TO |
571 | |
572 | // format result set. | |
573 | $this->formatDisplay($rows, FALSE); | |
574 | ||
575 | // assign variables to templates | |
576 | $this->doTemplateAssignment($rows); | |
577 | ||
578 | // do print / pdf / instance stuff if needed | |
579 | $this->endPostProcess($rows); | |
580 | } | |
581 | ||
00be9182 | 582 | public function fixReportParams() { |
a7488080 | 583 | if (!empty($this->_params['this_year_value'])) { |
6a488035 TO |
584 | $this->_referenceYear['this_year'] = $this->_params['this_year_value']; |
585 | } | |
a7488080 | 586 | if (!empty($this->_params['other_year_value'])) { |
6a488035 TO |
587 | $this->_referenceYear['other_year'] = $this->_params['other_year_value']; |
588 | } | |
589 | } | |
590 | ||
74cf4551 | 591 | /** |
645ee340 | 592 | * @param $sql |
74cf4551 EM |
593 | * @param $rows |
594 | */ | |
00be9182 | 595 | public function buildRows($sql, &$rows) { |
be2fb01f | 596 | $contactIds = []; |
6a488035 TO |
597 | |
598 | $addWhere = ''; | |
599 | ||
a7488080 | 600 | if (!empty($this->_referenceYear['other_year'])) { |
9d72cede EM |
601 | (CRM_Utils_Array::value('other_year_op', $this->_params) == |
602 | 'calendar') ? $other_receive_date = 'YEAR (contri.receive_date)' : $other_receive_date = self::fiscalYearOffset('contri.receive_date'); | |
9f108b4d | 603 | $addWhere .= " AND {$this->_aliases['civicrm_contact']}.id NOT IN ( SELECT DISTINCT cont.id FROM civicrm_contact cont, civicrm_contribution contri WHERE cont.id = contri.contact_id AND {$other_receive_date} = {$this->_referenceYear['other_year']} AND contri.is_test = 0 AND contri.is_template = 0 ) "; |
6a488035 | 604 | } |
a7488080 | 605 | if (!empty($this->_referenceYear['this_year'])) { |
9d72cede EM |
606 | (CRM_Utils_Array::value('this_year_op', $this->_params) == |
607 | 'calendar') ? $receive_date = 'YEAR (contri.receive_date)' : $receive_date = self::fiscalYearOffset('contri.receive_date'); | |
9f108b4d | 608 | $addWhere .= " AND {$this->_aliases['civicrm_contact']}.id IN ( SELECT DISTINCT cont.id FROM civicrm_contact cont, civicrm_contribution contri WHERE cont.id = contri.contact_id AND {$receive_date} = {$this->_referenceYear['this_year']} AND contri.is_test = 0 AND contri.is_template = 0 ) "; |
2f4c2f5d | 609 | } |
6a488035 | 610 | $this->limit(); |
9d1ef4d6 | 611 | $getContacts = "SELECT {$this->_aliases['civicrm_contact']}.id as cid, SUM({$this->_aliases['civicrm_contribution']}.total_amount) as civicrm_contribution_total_amount_sum {$this->_from} {$this->_where} {$addWhere} GROUP BY {$this->_aliases['civicrm_contact']}.id {$this->_having} {$this->_limit}"; |
2f4c2f5d | 612 | |
6a488035 TO |
613 | $dao = CRM_Core_DAO::executeQuery($getContacts); |
614 | ||
615 | while ($dao->fetch()) { | |
616 | $contactIds[] = $dao->cid; | |
617 | } | |
6a488035 TO |
618 | $this->setPager(); |
619 | ||
be2fb01f | 620 | $relationshipRows = []; |
6a488035 TO |
621 | if (empty($contactIds)) { |
622 | return; | |
623 | } | |
624 | ||
625 | $primaryContributions = $this->buildContributionRows($contactIds); | |
626 | ||
627 | list($relationshipRows, $relatedContactIds) = $this->buildRelationshipRows($contactIds); | |
628 | ||
629 | if (empty($relatedContactIds)) { | |
630 | $rows = $primaryContributions; | |
631 | return; | |
632 | } | |
633 | ||
634 | $relatedContributions = $this->buildContributionRows($relatedContactIds); | |
635 | ||
be2fb01f | 636 | $summaryYears = []; |
6a488035 | 637 | $summaryYears[] = "civicrm_upto_{$this->_yearStatisticsFrom}"; |
9d72cede | 638 | $yearConter = $this->_yearStatisticsFrom; |
6a488035 TO |
639 | $yearConter++; |
640 | while ($yearConter <= $this->_yearStatisticsTo) { | |
641 | $summaryYears[] = $yearConter; | |
642 | $yearConter++; | |
643 | } | |
644 | $summaryYears[] = 'aggregate_amount'; | |
645 | ||
646 | foreach ($primaryContributions as $cid => $primaryRow) { | |
647 | $row = $primaryRow; | |
648 | if (!isset($relationshipRows[$cid])) { | |
649 | $rows[$cid] = $row; | |
650 | continue; | |
651 | } | |
be2fb01f | 652 | $total = []; |
6a488035 TO |
653 | $total['civicrm_contact_sort_name'] = ts('Total'); |
654 | foreach ($summaryYears as $year) { | |
655 | $total[$year] = CRM_Utils_Array::value($year, $primaryRow, 0); | |
656 | } | |
657 | ||
658 | $relatedContact = FALSE; | |
659 | $rows[$cid] = $row; | |
660 | foreach ($relationshipRows[$cid] as $relcid => $relRow) { | |
661 | if (!isset($relatedContributions[$relcid])) { | |
662 | continue; | |
663 | } | |
664 | $relatedContact = TRUE; | |
665 | $relatedRow = $relatedContributions[$relcid]; | |
666 | foreach ($summaryYears as $year) { | |
667 | $total[$year] += CRM_Utils_Array::value($year, $relatedRow, 0); | |
668 | } | |
669 | ||
670 | foreach (array_keys($this->_relationshipColumns) as $col) { | |
a7488080 | 671 | if (!empty($relRow[$col])) { |
6a488035 TO |
672 | $relatedRow[$col] = $relRow[$col]; |
673 | } | |
674 | } | |
675 | $rows["{$cid}_{$relcid}"] = $relatedRow; | |
676 | } | |
677 | if ($relatedContact) { | |
678 | $rows["{$cid}_total"] = $total; | |
be2fb01f | 679 | $rows["{$cid}_bank"] = ['civicrm_contact_sort_name' => ' ']; |
6a488035 TO |
680 | } |
681 | } | |
682 | } | |
683 | ||
74cf4551 EM |
684 | /** |
685 | * @param $contactIds | |
686 | * | |
687 | * @return array | |
688 | */ | |
00be9182 | 689 | public function buildContributionRows($contactIds) { |
be2fb01f | 690 | $rows = []; |
6a488035 TO |
691 | if (empty($contactIds)) { |
692 | return $rows; | |
693 | } | |
694 | ||
2b4a1828 | 695 | $contributionClauses = ''; |
696 | if (!empty($this->_contributionClauses)) { | |
697 | $contributionClauses = ' AND ' . implode(' AND ', $this->_contributionClauses); | |
698 | } | |
699 | ||
c301f76e | 700 | $sqlContribution = "{$this->_select} {$this->_from} WHERE {$this->_aliases['civicrm_contact']}.id IN (" . |
9d72cede | 701 | implode(',', $contactIds) . |
2b4a1828 | 702 | ") AND {$this->_aliases['civicrm_contribution']}.is_test = 0 AND {$this->_aliases['civicrm_contribution']}.is_template = 0 {$contributionClauses} {$this->_groupBy} "; |
6a488035 | 703 | |
9d72cede | 704 | $dao = CRM_Core_DAO::executeQuery($sqlContribution); |
6a488035 | 705 | $contributionSum = 0; |
be2fb01f | 706 | $yearcal = []; |
6a488035 TO |
707 | while ($dao->fetch()) { |
708 | if (!$dao->civicrm_contact_id) { | |
709 | continue; | |
710 | } | |
711 | ||
712 | foreach ($this->_columnHeaders as $key => $value) { | |
713 | if (property_exists($dao, $key)) { | |
714 | $rows[$dao->civicrm_contact_id][$key] = $dao->$key; | |
715 | } | |
716 | } | |
717 | if ($dao->civicrm_contribution_receive_date) { | |
9d72cede EM |
718 | if ($dao->civicrm_contribution_receive_date > |
719 | $this->_yearStatisticsFrom | |
720 | ) { | |
6a488035 TO |
721 | $rows[$dao->civicrm_contact_id][$dao->civicrm_contribution_receive_date] = $dao->civicrm_contribution_total_amount; |
722 | } | |
723 | else { | |
724 | if (!isset($rows[$dao->civicrm_contact_id]["civicrm_upto_{$this->_yearStatisticsFrom}"])) { | |
725 | $rows[$dao->civicrm_contact_id]["civicrm_upto_{$this->_yearStatisticsFrom}"] = 0; | |
726 | } | |
727 | ||
728 | $rows[$dao->civicrm_contact_id]["civicrm_upto_{$this->_yearStatisticsFrom}"] += $dao->civicrm_contribution_total_amount; | |
729 | } | |
730 | } | |
731 | ||
732 | if (!isset($rows[$dao->civicrm_contact_id]['aggregate_amount'])) { | |
733 | $rows[$dao->civicrm_contact_id]['aggregate_amount'] = 0; | |
734 | } | |
735 | $rows[$dao->civicrm_contact_id]['aggregate_amount'] += $dao->civicrm_contribution_total_amount; | |
736 | } | |
6a488035 TO |
737 | return $rows; |
738 | } | |
739 | ||
74cf4551 EM |
740 | /** |
741 | * @param $contactIds | |
742 | * | |
743 | * @return array | |
744 | */ | |
00be9182 | 745 | public function buildRelationshipRows($contactIds) { |
be2fb01f | 746 | $relationshipRows = $relatedContactIds = []; |
6a488035 | 747 | if (empty($contactIds)) { |
be2fb01f | 748 | return [$relationshipRows, $relatedContactIds]; |
6a488035 TO |
749 | } |
750 | ||
751 | $relContactAlias = 'contact_relationship'; | |
752 | $addRelSelect = ''; | |
753 | if (!empty($this->_relationshipColumns)) { | |
754 | $addRelSelect = ', ' . implode(', ', $this->_relationshipColumns); | |
755 | } | |
c301f76e | 756 | $sqlRelationship = "SELECT {$this->_aliases['civicrm_relationship']}.relationship_type_id as relationship_type_id, {$this->_aliases['civicrm_relationship']}.contact_id_a as contact_id_a, {$this->_aliases['civicrm_relationship']}.contact_id_b as contact_id_b {$addRelSelect} FROM civicrm_contact {$relContactAlias} {$this->_relationshipFrom} WHERE {$relContactAlias}.id IN (" . |
9d72cede | 757 | implode(',', $contactIds) . |
d1641c51 | 758 | ") AND {$this->_aliases['civicrm_relationship']}.is_active = 1 {$this->_relationshipWhere} GROUP BY {$this->_aliases['civicrm_relationship']}.contact_id_a, {$this->_aliases['civicrm_relationship']}.contact_id_b, {$this->_aliases['civicrm_relationship']}.relationship_type_id"; |
6a488035 TO |
759 | $relationshipTypes = CRM_Core_PseudoConstant::relationshipType(); |
760 | ||
761 | $dao = CRM_Core_DAO::executeQuery($sqlRelationship); | |
762 | while ($dao->fetch()) { | |
be2fb01f | 763 | $row = []; |
6a488035 TO |
764 | foreach (array_keys($this->_relationshipColumns) as $rel_column) { |
765 | $row[$rel_column] = $dao->$rel_column; | |
766 | } | |
767 | if (in_array($dao->contact_id_a, $contactIds)) { | |
768 | $row['civicrm_relationship_relationship_type_id'] = $relationshipTypes[$dao->relationship_type_id]['label_a_b']; | |
769 | $row['civicrm_relationship_contact_id'] = $dao->contact_id_b; | |
770 | $relationshipRows[$dao->contact_id_a][$dao->contact_id_b] = $row; | |
771 | $relatedContactIds[$dao->contact_id_b] = $dao->contact_id_b; | |
772 | } | |
773 | if (in_array($dao->contact_id_b, $contactIds)) { | |
774 | $row['civicrm_relationship_contact_id'] = $dao->contact_id_a; | |
775 | $row['civicrm_relationship_relationship_type_id'] = $relationshipTypes[$dao->relationship_type_id]['label_b_a']; | |
776 | $relationshipRows[$dao->contact_id_b][$dao->contact_id_a] = $row; | |
777 | $relatedContactIds[$dao->contact_id_a] = $dao->contact_id_a; | |
778 | } | |
779 | } | |
be2fb01f | 780 | return [$relationshipRows, $relatedContactIds]; |
6a488035 | 781 | } |
2f4c2f5d | 782 | |
74cf4551 | 783 | /** |
4f1f1f2a | 784 | * Override "This Year" $op options |
74cf4551 EM |
785 | * @param string $type |
786 | * @param null $fieldName | |
787 | * | |
788 | * @return array | |
789 | */ | |
00be9182 | 790 | public function getOperationPair($type = "string", $fieldName = NULL) { |
0b0b778f | 791 | if ($fieldName == 'this_year' || $fieldName == 'other_year') { |
be2fb01f | 792 | return [ |
9d72cede | 793 | 'calendar' => ts('Is Calendar Year'), |
21dfd5f5 | 794 | 'fiscal' => ts('Fiscal Year Starting'), |
be2fb01f | 795 | ]; |
0b0b778f | 796 | } |
797 | return parent::getOperationPair($type, $fieldName); | |
6a488035 | 798 | } |
2f4c2f5d | 799 | |
74cf4551 | 800 | /** |
ced9bfed EM |
801 | * Alter display of rows. |
802 | * | |
803 | * Iterate through the rows retrieved via SQL and make changes for display purposes, | |
804 | * such as rendering contacts as links. | |
805 | * | |
806 | * @param array $rows | |
807 | * Rows generated by SQL, with an array for each row. | |
74cf4551 | 808 | */ |
00be9182 | 809 | public function alterDisplay(&$rows) { |
6a488035 TO |
810 | if (empty($rows)) { |
811 | return; | |
812 | } | |
813 | ||
6a488035 TO |
814 | $last_primary = NULL; |
815 | foreach ($rows as $rowNum => $row) { | |
816 | // Highlight primary contact and amount row | |
817 | if (is_numeric($rowNum) || | |
818 | ($last_primary && ($rowNum == "{$last_primary}_total")) | |
819 | ) { | |
820 | if (is_numeric($rowNum)) { | |
821 | $last_primary = $rowNum; | |
822 | } | |
823 | foreach ($row as $key => $value) { | |
824 | if ($key == 'civicrm_contact_id') { | |
825 | continue; | |
826 | } | |
827 | if (empty($value)) { | |
828 | $row[$key] = ''; | |
829 | continue; | |
830 | } | |
831 | ||
832 | if ($last_primary && ($rowNum == "{$last_primary}_total")) { | |
2b4a1828 | 833 | // Passing non-numeric is deprecated, but this isn't a perfect fix |
834 | // since it will still format things like postal code 90210 as | |
835 | // "90,210.00", but that predates the deprecation. See dev/core#2819 | |
836 | if (is_numeric($value)) { | |
837 | $value = CRM_Utils_Money::formatLocaleNumericRoundedForDefaultCurrency($value); | |
838 | } | |
6a488035 | 839 | } |
2b4a1828 | 840 | // TODO: It later tries to format this as money which then gives a warning. One option is to instead set something like $row[$key]['classes'] and then use that in the template, but I don't think the stock template supports something like that. |
6a488035 TO |
841 | $row[$key] = '<strong>' . $value . '</strong>'; |
842 | } | |
843 | $rows[$rowNum] = $row; | |
844 | } | |
845 | ||
2123ee37 | 846 | // The main rows don't have this set so gives a smarty warning. |
847 | if (!isset($row['civicrm_relationship_relationship_type_id'])) { | |
848 | $rows[$rowNum]['civicrm_relationship_relationship_type_id'] = ''; | |
849 | } | |
850 | ||
6a488035 | 851 | // Convert Display name into link |
9d72cede EM |
852 | if (!empty($row['civicrm_contact_sort_name']) && |
853 | !empty($row['civicrm_contact_id']) | |
854 | ) { | |
6a488035 TO |
855 | $url = CRM_Report_Utils_Report::getNextUrl('contribute/detail', |
856 | 'reset=1&force=1&id_op=eq&id_value=' . $row['civicrm_contact_id'], | |
857 | $this->_absoluteUrl, $this->_id | |
858 | ); | |
859 | $rows[$rowNum]['civicrm_contact_sort_name_link'] = $url; | |
860 | $rows[$rowNum]['civicrm_contact_sort_name_hover'] = ts("View Contribution Details for this Contact."); | |
861 | } | |
70bea8e2 | 862 | |
5e0343e8 | 863 | if (!empty($row['civicrm_financial_trxn_card_type_id'])) { |
864 | $rows[$rowNum]['civicrm_financial_trxn_card_type_id'] = $this->getLabels($row['civicrm_financial_trxn_card_type_id'], 'CRM_Financial_DAO_FinancialTrxn', 'card_type_id'); | |
37e697e7 E |
865 | } |
866 | ||
b2f2700e | 867 | $this->alterDisplayContactFields($row, $rows, $rowNum, NULL, NULL); |
868 | $this->alterDisplayAddressFields($row, $rows, $rowNum, NULL, NULL); | |
6a488035 TO |
869 | } |
870 | } | |
96025800 | 871 | |
9d72cede | 872 | } |