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_Lybunt extends CRM_Report_Form { | |
18 | ||
c160fde8 | 19 | /** |
20 | * This is the report that links will lead to. | |
21 | * | |
22 | * It is a bit problematic to use contribute/detail for anything other than a single contact | |
23 | * as the filtering from this report does not carry over to that report. | |
24 | * | |
25 | * @var array | |
26 | */ | |
be2fb01f | 27 | public $_drilldownReport = ['contribute/detail' => 'Link to Detail Report']; |
6a488035 TO |
28 | |
29 | protected $lifeTime_from = NULL; | |
430ae6dd | 30 | protected $lifeTime_where = NULL; |
be2fb01f | 31 | protected $_customGroupExtends = [ |
5571417a | 32 | 'Contribution', |
c160fde8 | 33 | 'Contact', |
34 | 'Individual', | |
35 | 'Household', | |
36 | 'Organization', | |
be2fb01f | 37 | ]; |
c160fde8 | 38 | |
39 | /** | |
40 | * Table containing list of contact IDs. | |
41 | * | |
42 | * @var string | |
43 | */ | |
44 | protected $contactTempTable = ''; | |
45 | ||
46 | /** | |
87755286 | 47 | * This report has been optimised for group filtering. |
c160fde8 | 48 | * |
87755286 | 49 | * @var bool |
0e480632 | 50 | * @see https://issues.civicrm.org/jira/browse/CRM-19170 |
c160fde8 | 51 | */ |
87755286 | 52 | protected $groupFilterNotOptimised = FALSE; |
430ae6dd | 53 | |
c160fde8 | 54 | /** |
55 | * Class constructor. | |
74cf4551 | 56 | */ |
00be9182 | 57 | public function __construct() { |
66ddb273 | 58 | $this->optimisedForOnlyFullGroupBy = FALSE; |
55f71fa7 | 59 | $this->_rollup = 'WITH ROLLUP'; |
49e1ea9f | 60 | $this->_autoIncludeIndexedFieldsAsOrderBys = 1; |
9d72cede | 61 | $yearsInPast = 10; |
6a488035 | 62 | $yearsInFuture = 1; |
9d72cede EM |
63 | $date = CRM_Core_SelectValues::date('custom', NULL, $yearsInPast, $yearsInFuture); |
64 | $count = $date['maxYear']; | |
6a488035 TO |
65 | while ($date['minYear'] <= $count) { |
66 | $optionYear[$date['minYear']] = $date['minYear']; | |
67 | $date['minYear']++; | |
68 | } | |
69 | ||
be2fb01f CW |
70 | $this->_columns = [ |
71 | 'civicrm_contact' => [ | |
a130e045 DG |
72 | 'dao' => 'CRM_Contact_DAO_Contact', |
73 | 'grouping' => 'contact-field', | |
c160fde8 | 74 | 'fields' => $this->getBasicContactFields(), |
be2fb01f CW |
75 | 'order_bys' => [ |
76 | 'sort_name' => [ | |
49e1ea9f | 77 | 'title' => ts('Last Name, First Name'), |
a7d034b3 | 78 | 'default' => '0', |
49e1ea9f | 79 | 'default_order' => 'ASC', |
be2fb01f CW |
80 | ], |
81 | 'first_name' => [ | |
49e1ea9f | 82 | 'name' => 'first_name', |
83 | 'title' => ts('First Name'), | |
be2fb01f CW |
84 | ], |
85 | 'gender_id' => [ | |
49e1ea9f | 86 | 'name' => 'gender_id', |
87 | 'title' => ts('Gender'), | |
be2fb01f CW |
88 | ], |
89 | 'birth_date' => [ | |
49e1ea9f | 90 | 'name' => 'birth_date', |
91 | 'title' => ts('Birth Date'), | |
be2fb01f CW |
92 | ], |
93 | 'contact_type' => [ | |
a130e045 | 94 | 'title' => ts('Contact Type'), |
be2fb01f CW |
95 | ], |
96 | 'contact_sub_type' => [ | |
a130e045 | 97 | 'title' => ts('Contact Subtype'), |
be2fb01f CW |
98 | ], |
99 | ], | |
100 | 'filters' => [ | |
101 | 'sort_name' => [ | |
a130e045 DG |
102 | 'title' => ts('Donor Name'), |
103 | 'operator' => 'like', | |
be2fb01f CW |
104 | ], |
105 | 'id' => [ | |
49e1ea9f | 106 | 'title' => ts('Contact ID'), |
107 | 'no_display' => TRUE, | |
be2fb01f CW |
108 | ], |
109 | 'gender_id' => [ | |
49e1ea9f | 110 | 'title' => ts('Gender'), |
111 | 'operatorType' => CRM_Report_Form::OP_MULTISELECT, | |
112 | 'options' => CRM_Core_PseudoConstant::get('CRM_Contact_DAO_Contact', 'gender_id'), | |
be2fb01f CW |
113 | ], |
114 | 'birth_date' => [ | |
49e1ea9f | 115 | 'title' => ts('Birth Date'), |
116 | 'operatorType' => CRM_Report_Form::OP_DATE, | |
be2fb01f CW |
117 | ], |
118 | 'contact_type' => [ | |
49e1ea9f | 119 | 'title' => ts('Contact Type'), |
be2fb01f CW |
120 | ], |
121 | 'contact_sub_type' => [ | |
49e1ea9f | 122 | 'title' => ts('Contact Subtype'), |
be2fb01f CW |
123 | ], |
124 | 'is_deceased' => [], | |
125 | 'do_not_phone' => [], | |
126 | 'do_not_email' => [], | |
127 | 'do_not_sms' => [], | |
128 | 'do_not_mail' => [], | |
129 | 'is_opt_out' => [], | |
130 | ], | |
131 | ], | |
132 | 'civicrm_line_item' => [ | |
3191db62 | 133 | 'dao' => 'CRM_Price_DAO_LineItem', |
be2fb01f CW |
134 | ], |
135 | 'civicrm_email' => [ | |
a130e045 DG |
136 | 'dao' => 'CRM_Core_DAO_Email', |
137 | 'grouping' => 'contact-field', | |
be2fb01f CW |
138 | 'fields' => [ |
139 | 'email' => [ | |
a130e045 DG |
140 | 'title' => ts('Email'), |
141 | 'default' => TRUE, | |
be2fb01f CW |
142 | ], |
143 | 'on_hold' => [ | |
0a618a8d | 144 | 'title' => ts('Email on hold'), |
be2fb01f CW |
145 | ], |
146 | ], | |
3d5e64d5 | 147 | 'filters' => [ |
148 | 'on_hold' => [ | |
149 | 'title' => ts('On Hold'), | |
f8035bb4 | 150 | 'type' => CRM_Utils_Type::T_INT, |
151 | 'operatorType' => CRM_Report_Form::OP_MULTISELECT, | |
152 | 'options' => ['' => ts('Any')] + CRM_Core_PseudoConstant::emailOnHoldOptions(), | |
3d5e64d5 | 153 | ], |
154 | ], | |
be2fb01f CW |
155 | ], |
156 | 'civicrm_phone' => [ | |
a130e045 DG |
157 | 'dao' => 'CRM_Core_DAO_Phone', |
158 | 'grouping' => 'contact-field', | |
be2fb01f CW |
159 | 'fields' => [ |
160 | 'phone' => [ | |
a130e045 DG |
161 | 'title' => ts('Phone'), |
162 | 'default' => TRUE, | |
be2fb01f CW |
163 | ], |
164 | ], | |
165 | ], | |
166 | ]; | |
c160fde8 | 167 | $this->_columns += $this->addAddressFields(FALSE); |
be2fb01f CW |
168 | $this->_columns += [ |
169 | 'civicrm_contribution' => [ | |
a130e045 | 170 | 'dao' => 'CRM_Contribute_DAO_Contribution', |
be2fb01f CW |
171 | 'fields' => [ |
172 | 'contact_id' => [ | |
a130e045 DG |
173 | 'title' => ts('contactId'), |
174 | 'no_display' => TRUE, | |
175 | 'required' => TRUE, | |
176 | 'no_repeat' => TRUE, | |
be2fb01f CW |
177 | ], |
178 | 'receive_date' => [ | |
a130e045 DG |
179 | 'title' => ts('Year'), |
180 | 'no_display' => TRUE, | |
181 | 'required' => TRUE, | |
182 | 'no_repeat' => TRUE, | |
be2fb01f CW |
183 | ], |
184 | 'last_year_total_amount' => [ | |
c160fde8 | 185 | 'title' => ts('Last Year Total'), |
186 | 'default' => TRUE, | |
187 | 'type' => CRM_Utils_Type::T_MONEY, | |
3fd9a92a | 188 | 'required' => TRUE, |
be2fb01f CW |
189 | ], |
190 | 'civicrm_life_time_total' => [ | |
c160fde8 | 191 | 'title' => ts('Lifetime Total'), |
192 | 'default' => TRUE, | |
193 | 'type' => CRM_Utils_Type::T_MONEY, | |
be2fb01f | 194 | 'statistics' => ['sum' => ts('Lifetime total')], |
8ffac470 | 195 | 'required' => TRUE, |
be2fb01f CW |
196 | ], |
197 | ], | |
198 | 'filters' => [ | |
199 | 'yid' => [ | |
a130e045 DG |
200 | 'name' => 'receive_date', |
201 | 'title' => ts('This Year'), | |
202 | 'operatorType' => CRM_Report_Form::OP_SELECT, | |
203 | 'options' => $optionYear, | |
204 | 'default' => date('Y'), | |
55f71fa7 | 205 | 'type' => CRM_Utils_Type::T_INT, |
be2fb01f CW |
206 | ], |
207 | 'financial_type_id' => [ | |
a130e045 | 208 | 'title' => ts('Financial Type'), |
525ae77a | 209 | 'type' => CRM_Utils_Type::T_INT, |
a130e045 | 210 | 'operatorType' => CRM_Report_Form::OP_MULTISELECT, |
51d1f926 | 211 | 'options' => CRM_Contribute_BAO_Contribution::buildOptions('financial_type_id', 'search'), |
be2fb01f CW |
212 | ], |
213 | 'contribution_status_id' => [ | |
a130e045 DG |
214 | 'title' => ts('Contribution Status'), |
215 | 'operatorType' => CRM_Report_Form::OP_MULTISELECT, | |
c0aaecf9 | 216 | 'options' => CRM_Contribute_BAO_Contribution::buildOptions('contribution_status_id', 'search'), |
be2fb01f CW |
217 | 'default' => ['1'], |
218 | ], | |
219 | ], | |
220 | 'order_bys' => [ | |
221 | 'last_year_total_amount' => [ | |
c160fde8 | 222 | 'title' => ts('Total amount last year'), |
223 | 'default' => '1', | |
a7d034b3 | 224 | 'default_weight' => '0', |
225 | 'default_order' => 'DESC', | |
be2fb01f CW |
226 | ], |
227 | ], | |
228 | ], | |
229 | ]; | |
230 | $this->_columns += [ | |
231 | 'civicrm_financial_trxn' => [ | |
62a40853 | 232 | 'dao' => 'CRM_Financial_DAO_FinancialTrxn', |
be2fb01f CW |
233 | 'fields' => [ |
234 | 'card_type_id' => [ | |
d72b084a | 235 | 'title' => ts('Credit Card Type'), |
5e0343e8 | 236 | 'dbAlias' => 'GROUP_CONCAT(financial_trxn_civireport.card_type_id SEPARATOR ",")', |
be2fb01f CW |
237 | ], |
238 | ], | |
239 | 'filters' => [ | |
240 | 'card_type_id' => [ | |
d72b084a | 241 | 'title' => ts('Credit Card Type'), |
62a40853 | 242 | 'operatorType' => CRM_Report_Form::OP_MULTISELECT, |
5e0343e8 | 243 | 'options' => CRM_Financial_DAO_FinancialTrxn::buildOptions('card_type_id'), |
62a40853 E |
244 | 'default' => NULL, |
245 | 'type' => CRM_Utils_Type::T_STRING, | |
be2fb01f CW |
246 | ], |
247 | ], | |
248 | ], | |
249 | ]; | |
6a488035 | 250 | |
d62fab33 | 251 | // If we have a campaign, build out the relevant elements |
689c6bd2 | 252 | $this->addCampaignFields('civicrm_contribution'); |
d62fab33 | 253 | |
f787b3c4 BT |
254 | // Add charts support |
255 | $this->_charts = [ | |
256 | '' => ts('Tabular'), | |
257 | 'barChart' => ts('Bar Chart'), | |
258 | 'pieChart' => ts('Pie Chart'), | |
259 | ]; | |
260 | ||
16e2e80c | 261 | $this->_groupFilter = TRUE; |
6a488035 TO |
262 | $this->_tagFilter = TRUE; |
263 | parent::__construct(); | |
264 | } | |
265 | ||
c160fde8 | 266 | /** |
267 | * Build select clause for a single field. | |
268 | * | |
269 | * @param string $tableName | |
270 | * @param string $tableKey | |
271 | * @param string $fieldName | |
272 | * @param string $field | |
273 | * | |
274 | * @return string | |
275 | */ | |
276 | public function selectClause(&$tableName, $tableKey, &$fieldName, &$field) { | |
277 | if ($fieldName == 'last_year_total_amount') { | |
278 | $this->_columnHeaders["{$tableName}_{$fieldName}"] = $field; | |
279 | $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = $this->getLastYearColumnTitle(); | |
280 | $this->_statFields[$this->getLastYearColumnTitle()] = "{$tableName}_{$fieldName}"; | |
c86d4e7c | 281 | return "SUM(IF(" . $this->whereClauseLastYear('contribution_civireport.receive_date') . ", contribution_civireport.total_amount, 0)) as {$tableName}_{$fieldName}"; |
c160fde8 | 282 | } |
283 | if ($fieldName == 'civicrm_life_time_total') { | |
284 | $this->_columnHeaders["{$tableName}_{$fieldName}"] = $field; | |
285 | $this->_statFields[$field['title']] = "{$tableName}_{$fieldName}"; | |
286 | return "SUM({$this->_aliases[$tableName]}.total_amount) as {$tableName}_{$fieldName}"; | |
287 | } | |
288 | if ($fieldName == 'receive_date') { | |
289 | return self::fiscalYearOffset($field['dbAlias']) . | |
290 | " as {$tableName}_{$fieldName} "; | |
291 | } | |
292 | return FALSE; | |
293 | } | |
6a488035 | 294 | |
c160fde8 | 295 | /** |
296 | * Get the title for the last year column. | |
297 | */ | |
298 | public function getLastYearColumnTitle() { | |
299 | if ($this->getYearFilterType() == 'calendar') { | |
300 | return ts('Total for ') . ($this->getCurrentYear() - 1); | |
301 | } | |
302 | return ts('Total for Fiscal Year ') . ($this->getCurrentYear() - 1) . '-' . ($this->getCurrentYear()); | |
303 | } | |
6a488035 | 304 | |
c160fde8 | 305 | /** |
306 | * Construct from clause. | |
307 | * | |
308 | * On the first run we are creating a table of contacts to include in the report. | |
309 | * | |
310 | * Once contactTempTable is populated we should avoid using any further filters that affect | |
311 | * the contacts that should be visible. | |
312 | */ | |
313 | public function from() { | |
314 | if (!empty($this->contactTempTable)) { | |
315 | $this->_from = " | |
316 | FROM civicrm_contribution {$this->_aliases['civicrm_contribution']} | |
317 | INNER JOIN $this->contactTempTable restricted_contacts | |
318 | ON restricted_contacts.cid = {$this->_aliases['civicrm_contribution']}.contact_id | |
319 | AND {$this->_aliases['civicrm_contribution']}.is_test = 0 | |
9f108b4d | 320 | AND {$this->_aliases['civicrm_contribution']}.is_template = 0 |
c160fde8 | 321 | INNER JOIN civicrm_contact {$this->_aliases['civicrm_contact']} |
322 | ON restricted_contacts.cid = {$this->_aliases['civicrm_contact']}.id"; | |
18f511e2 | 323 | |
324 | $this->joinAddressFromContact(); | |
325 | $this->joinPhoneFromContact(); | |
326 | $this->joinEmailFromContact(); | |
6a488035 | 327 | } |
c160fde8 | 328 | else { |
87755286 | 329 | $this->setFromBase('civicrm_contact'); |
6a488035 | 330 | |
c160fde8 | 331 | $this->_from .= " INNER JOIN civicrm_contribution {$this->_aliases['civicrm_contribution']} "; |
332 | if (!$this->groupTempTable) { | |
333 | // The received_date index is better than the contribution_status_id index (fairly substantially). | |
334 | // But if we have already pre-filtered down to a group of contacts then we want that to be the | |
335 | // primary filter and the index hint will block that. | |
336 | $this->_from .= "USE index (received_date)"; | |
337 | } | |
338 | $this->_from .= " ON {$this->_aliases['civicrm_contribution']}.contact_id = {$this->_aliases['civicrm_contact']}.id | |
339 | AND {$this->_aliases['civicrm_contribution']}.is_test = 0 | |
9f108b4d | 340 | AND {$this->_aliases['civicrm_contribution']}.is_template = 0 |
c160fde8 | 341 | AND " . $this->whereClauseLastYear("{$this->_aliases['civicrm_contribution']}.receive_date") . " |
1679e19c | 342 | {$this->_aclFrom} "; |
c160fde8 | 343 | $this->selectivelyAddLocationTablesJoinsToFilterQuery(); |
344 | } | |
6a488035 | 345 | |
62a40853 E |
346 | // for credit card type |
347 | $this->addFinancialTrxnFromClause(); | |
c160fde8 | 348 | } |
6a488035 | 349 | |
c160fde8 | 350 | /** |
351 | * Generate where clause. | |
352 | * | |
353 | * We are overriding this primarily for 'before-after' handling of the receive_date placeholder field. | |
354 | * | |
355 | * We call this twice. The first time we are generating a temp table and we want to do an IS NULL on the | |
356 | * join that draws in contributions from this year. The second time we are filtering elsewhere (contacts via | |
357 | * the temp table & contributions via selective addition of contributions in the select function). | |
358 | * | |
359 | * If lifetime total is NOT selected we can add a further filter here to possibly improve performance | |
360 | * but the benefit if unproven as yet. | |
361 | * $clause = $this->whereClauseLastYear("{$this->_aliases['civicrm_contribution']}.receive_date"); | |
362 | * | |
363 | * @param array $field Field specifications | |
364 | * @param string $op Query operator (not an exact match to sql) | |
365 | * @param mixed $value | |
366 | * @param float $min | |
367 | * @param float $max | |
368 | * | |
369 | * @return null|string | |
370 | */ | |
371 | public function whereClause(&$field, $op, $value, $min, $max) { | |
372 | if ($field['name'] == 'receive_date') { | |
373 | $clause = 1; | |
374 | if (empty($this->contactTempTable)) { | |
1679e19c | 375 | $clause = "{$this->_aliases['civicrm_contact']}.id NOT IN ( |
376 | SELECT cont_exclude.contact_id | |
377 | FROM civicrm_contribution cont_exclude | |
378 | WHERE " . $this->whereClauseThisYear('cont_exclude.receive_date') | |
379 | . ")"; | |
c160fde8 | 380 | } |
381 | } | |
87755286 | 382 | // Group filtering is already done so skip. |
383 | elseif (!empty($field['group']) && $this->contactTempTable) { | |
384 | return 1; | |
385 | } | |
c160fde8 | 386 | else { |
387 | $clause = parent::whereClause($field, $op, $value, $min, $max); | |
a27c90f2 | 388 | } |
c160fde8 | 389 | return $clause; |
6a488035 TO |
390 | } |
391 | ||
c160fde8 | 392 | /** |
393 | * Generate where clause for last calendar year or fiscal year. | |
394 | * | |
395 | * @todo must be possible to re-use relative dates stuff. | |
396 | * | |
397 | * @param string $fieldName | |
398 | * | |
399 | * @return string | |
400 | */ | |
401 | public function whereClauseLastYear($fieldName) { | |
402 | return "$fieldName BETWEEN '" . $this->getFirstDateOfPriorRange() . "' AND '" . $this->getLastDateOfPriorRange() . "'"; | |
6a488035 TO |
403 | } |
404 | ||
a7d034b3 | 405 | /** |
406 | * Generate where clause for last calendar year or fiscal year. | |
407 | * | |
408 | * @todo must be possible to re-use relative dates stuff. | |
409 | * | |
410 | * @param string $fieldName | |
411 | * | |
c160fde8 | 412 | * @param int $current_year |
a7d034b3 | 413 | * @return null|string |
414 | */ | |
c160fde8 | 415 | public function whereClauseThisYear($fieldName, $current_year = NULL) { |
416 | return "$fieldName BETWEEN '" . $this->getFirstDateOfCurrentRange() . "' AND '" . $this->getLastDateOfCurrentRange() . "'"; | |
417 | } | |
418 | ||
c160fde8 | 419 | /** |
420 | * Get the year value for the current year. | |
421 | * | |
422 | * @return string | |
423 | */ | |
424 | public function getCurrentYear() { | |
425 | return $this->_params['yid_value']; | |
426 | } | |
427 | ||
428 | /** | |
429 | * Get the date time of the first date in the 'this year' range. | |
430 | * | |
431 | * @return string | |
432 | */ | |
433 | public function getFirstDateOfCurrentRange() { | |
434 | $current_year = $this->getCurrentYear(); | |
435 | if ($this->getYearFilterType() == 'calendar') { | |
436 | return "{$current_year }-01-01"; | |
a7d034b3 | 437 | } |
438 | else { | |
439 | $fiscalYear = CRM_Core_Config::singleton()->fiscalYearStart; | |
c160fde8 | 440 | return "{$current_year}-{$fiscalYear['M']}-{$fiscalYear['d']}"; |
a7d034b3 | 441 | } |
a7d034b3 | 442 | } |
443 | ||
c160fde8 | 444 | /** |
445 | * Get the year value for the current year. | |
446 | * | |
447 | * @return string | |
448 | */ | |
449 | public function getYearFilterType() { | |
450 | return CRM_Utils_Array::value('yid_op', $this->_params, 'calendar'); | |
451 | } | |
a7d034b3 | 452 | |
c160fde8 | 453 | /** |
454 | * Get the date time of the last date in the 'this year' range. | |
455 | * | |
456 | * @return string | |
457 | */ | |
458 | public function getLastDateOfCurrentRange() { | |
459 | return date('YmdHis', strtotime('+ 1 year - 1 second', strtotime($this->getFirstDateOfCurrentRange()))); | |
460 | } | |
a7d034b3 | 461 | |
c160fde8 | 462 | /** |
463 | * Get the date time of the first date in the 'last year' range. | |
464 | * | |
465 | * @return string | |
466 | */ | |
467 | public function getFirstDateOfPriorRange() { | |
468 | return date('YmdHis', strtotime('- 1 year', strtotime($this->getFirstDateOfCurrentRange()))); | |
469 | } | |
a7d034b3 | 470 | |
c160fde8 | 471 | /** |
472 | * Get the date time of the last date in the 'last year' range. | |
473 | * | |
474 | * @return string | |
475 | */ | |
476 | public function getLastDateOfPriorRange() { | |
477 | return date('YmdHis', strtotime('+ 1 year - 1 second', strtotime($this->getFirstDateOfPriorRange()))); | |
478 | } | |
479 | ||
c160fde8 | 480 | public function groupBy() { |
481 | $this->_groupBy = "GROUP BY {$this->_aliases['civicrm_contribution']}.contact_id "; | |
36d2f4d5 | 482 | $this->_select = CRM_Contact_BAO_Query::appendAnyValueToSelect($this->_selectClauses, "{$this->_aliases['civicrm_contribution']}.contact_id"); |
6a488035 TO |
483 | $this->assign('chartSupported', TRUE); |
484 | } | |
485 | ||
74cf4551 | 486 | /** |
71d8f758 | 487 | * @param array $rows |
74cf4551 EM |
488 | * |
489 | * @return array | |
490 | */ | |
00be9182 | 491 | public function statistics(&$rows) { |
c160fde8 | 492 | |
6a488035 | 493 | $statistics = parent::statistics($rows); |
c160fde8 | 494 | // The parent class does something odd where it adds an extra row to the count for the grand total. |
495 | // Perhaps that works on some other report? But here it just seems odd. | |
496 | $this->countStat($statistics, count($rows)); | |
6a488035 | 497 | if (!empty($rows)) { |
c160fde8 | 498 | if (!empty($this->rollupRow) && !empty($this->rollupRow['civicrm_contribution_last_year_total_amount'])) { |
be2fb01f | 499 | $statistics['counts']['civicrm_contribution_last_year_total_amount'] = [ |
c160fde8 | 500 | 'value' => $this->rollupRow['civicrm_contribution_last_year_total_amount'], |
501 | 'title' => $this->getLastYearColumnTitle(), | |
502 | 'type' => CRM_Utils_Type::T_MONEY, | |
be2fb01f | 503 | ]; |
c160fde8 | 504 | |
505 | } | |
506 | if (!empty($this->rollupRow) && !empty($this->rollupRow['civicrm_contribution_civicrm_life_time_total'])) { | |
be2fb01f | 507 | $statistics['counts']['civicrm_contribution_civicrm_life_time_total'] = [ |
c160fde8 | 508 | 'value' => $this->rollupRow['civicrm_contribution_civicrm_life_time_total'], |
509 | 'title' => ts('Total LifeTime'), | |
6a488035 | 510 | 'type' => CRM_Utils_Type::T_MONEY, |
be2fb01f | 511 | ]; |
6a488035 | 512 | } |
c160fde8 | 513 | else { |
514 | $select = "SELECT SUM({$this->_aliases['civicrm_contribution']}.total_amount) as amount, | |
c86d4e7c | 515 | SUM(IF( " . $this->whereClauseLastYear('contribution_civireport.receive_date') . ", contribution_civireport.total_amount, 0)) as last_year |
c160fde8 | 516 | "; |
517 | $sql = "{$select} {$this->_from} {$this->_where}"; | |
518 | $dao = CRM_Core_DAO::executeQuery($sql); | |
519 | if ($dao->fetch()) { | |
be2fb01f | 520 | $statistics['counts']['amount'] = [ |
c160fde8 | 521 | 'value' => $dao->amount, |
522 | 'title' => ts('Total LifeTime'), | |
523 | 'type' => CRM_Utils_Type::T_MONEY, | |
be2fb01f CW |
524 | ]; |
525 | $statistics['counts']['last_year'] = [ | |
c160fde8 | 526 | 'value' => $dao->last_year, |
527 | 'title' => $this->getLastYearColumnTitle(), | |
528 | 'type' => CRM_Utils_Type::T_MONEY, | |
be2fb01f | 529 | ]; |
c160fde8 | 530 | } |
531 | } | |
6a488035 TO |
532 | } |
533 | ||
534 | return $statistics; | |
535 | } | |
536 | ||
c160fde8 | 537 | /** |
538 | * This function is called by both the api (tests) and the UI. | |
539 | */ | |
540 | public function beginPostProcessCommon() { | |
a7d034b3 | 541 | $this->buildQuery(); |
c160fde8 | 542 | // @todo this acl has no test coverage and is very hard to test manually so could be fragile. |
4400048b | 543 | $this->resetFormSqlAndWhereHavingClauses(); |
6a488035 | 544 | |
f0197a3d | 545 | $this->contactTempTable = $this->createTemporaryTable('rptlybunt', " |
546 | SELECT SQL_CALC_FOUND_ROWS {$this->_aliases['civicrm_contact']}.id as cid {$this->_from} | |
547 | {$this->_where} | |
548 | GROUP BY {$this->_aliases['civicrm_contact']}.id" | |
549 | ); | |
e463c072 | 550 | $this->limit(); |
e463c072 | 551 | if (empty($this->_params['charts'])) { |
6a488035 TO |
552 | $this->setPager(); |
553 | } | |
554 | ||
c160fde8 | 555 | // Reset where clauses to be regenerated in postProcess. |
be2fb01f | 556 | $this->_whereClauses = []; |
c160fde8 | 557 | } |
a7d034b3 | 558 | |
c160fde8 | 559 | /** |
560 | * Build the report query. | |
561 | * | |
562 | * The issue we are hitting is that if we want to do group by & then ORDER BY we have to | |
563 | * wrap the query in an outer query with the order by - otherwise the group by takes precedent. | |
564 | * This is an issue when we want to group by contact but order by the maximum aggregate donation. | |
565 | * | |
566 | * @param bool $applyLimit | |
567 | * | |
568 | * @return string | |
569 | */ | |
570 | public function buildQuery($applyLimit = TRUE) { | |
87755286 | 571 | $this->buildGroupTempTable(); |
f0384ec0 | 572 | $this->buildPermissionClause(); |
2c0e8e6b | 573 | // Calling where & select before FROM allows us to build temp tables to use in from. |
574 | $this->where(); | |
c160fde8 | 575 | $this->select(); |
576 | $this->from(); | |
577 | $this->customDataFrom(empty($this->contactTempTable)); | |
2c0e8e6b | 578 | |
c160fde8 | 579 | $this->groupBy(); |
580 | $this->orderBy(); | |
c160fde8 | 581 | $limitFilter = ''; |
582 | ||
583 | // order_by columns not selected for display need to be included in SELECT | |
584 | // This differs from parent in that we are getting those not in order by rather than not in | |
585 | // sections, as we need to adapt to our contact group by. | |
586 | $unselectedSectionColumns = array_diff_key($this->_orderByFields, $this->getSelectColumns()); | |
587 | foreach ($unselectedSectionColumns as $alias => $section) { | |
588 | $this->_select .= ", {$section['dbAlias']} as {$alias}"; | |
589 | } | |
6a488035 | 590 | |
c160fde8 | 591 | if ($applyLimit && empty($this->_params['charts'])) { |
592 | $this->limit(); | |
593 | } | |
6a488035 | 594 | |
bad98dd5 | 595 | $sql = "{$this->_select} {$this->_from} {$this->_where} {$limitFilter} {$this->_groupBy} {$this->_having} {$this->_rollup}"; |
6a488035 | 596 | |
c160fde8 | 597 | if (!empty($this->_orderByArray)) { |
598 | $this->_orderBy = str_replace('contact_civireport.', 'civicrm_contact_', "ORDER BY ISNULL(civicrm_contribution_contact_id), " . implode(', ', $this->_orderByArray)); | |
599 | $this->_orderBy = str_replace('contribution_civireport.', 'civicrm_contribution_', $this->_orderBy); | |
600 | foreach ($this->_orderByFields as $field) { | |
601 | $this->_orderBy = str_replace($field['dbAlias'], $field['tplField'], $this->_orderBy); | |
6a488035 | 602 | } |
c160fde8 | 603 | $sql = str_replace('SQL_CALC_FOUND_ROWS', '', $sql); |
604 | $sql = "SELECT SQL_CALC_FOUND_ROWS * FROM ( $sql ) as inner_query {$this->_orderBy} $this->_limit"; | |
6a488035 TO |
605 | } |
606 | ||
c160fde8 | 607 | CRM_Utils_Hook::alterReportVar('sql', $this, $this); |
608 | $this->addToDeveloperTab($sql); | |
6a488035 | 609 | |
c160fde8 | 610 | return $sql; |
6a488035 TO |
611 | } |
612 | ||
a7d034b3 | 613 | /** |
4400048b | 614 | * Reset the form sql and where / having clause arrays. |
615 | * | |
616 | * We do an early iteration of the report queries to generate the temp table. | |
617 | * | |
618 | * However, that iteration populates the sql for the developer tab, | |
619 | * the whereClauses & the havingClauses and they are populated again in the normal | |
620 | * report flow. This is harmless but confusing - ie. the where clause winds up repeating | |
621 | * the same filters and the dev tab shows the query twice, so we rest them. | |
a7d034b3 | 622 | */ |
4400048b | 623 | protected function resetFormSqlAndWhereHavingClauses() { |
a7d034b3 | 624 | $this->sql = ''; |
be2fb01f CW |
625 | $this->_havingClauses = []; |
626 | $this->_whereClauses = []; | |
627 | $this->sqlArray = []; | |
a7d034b3 | 628 | } |
629 | ||
74cf4551 EM |
630 | /** |
631 | * @param $rows | |
632 | */ | |
00be9182 | 633 | public function buildChart(&$rows) { |
6a488035 | 634 | |
be2fb01f | 635 | $graphRows = []; |
9d72cede | 636 | $count = 0; |
be2fb01f | 637 | $display = []; |
6a488035 TO |
638 | |
639 | $current_year = $this->_params['yid_value']; | |
640 | $previous_year = $current_year - 1; | |
641 | $interval[$previous_year] = $previous_year; | |
f787b3c4 | 642 | $interval['life_time'] = ts('Life Time'); |
6a488035 TO |
643 | |
644 | foreach ($rows as $key => $row) { | |
8ffac470 SL |
645 | // The final row contains the totals so we don't need to include it here. |
646 | if (!empty($row['civicrm_contribution_contact_id'])) { | |
647 | $display['life_time'] = CRM_Utils_Array::value('life_time', $display) + | |
648 | $row['civicrm_contribution_civicrm_life_time_total']; | |
649 | $display[$previous_year] = CRM_Utils_Array::value($previous_year, $display) + $row['civicrm_contribution_last_year_total_amount']; | |
650 | } | |
6a488035 TO |
651 | } |
652 | ||
9d72cede | 653 | $config = CRM_Core_Config::Singleton(); |
6a488035 | 654 | $graphRows['value'] = $display; |
be2fb01f | 655 | $chartInfo = [ |
9d72cede | 656 | 'legend' => ts('Lybunt Report'), |
6a488035 | 657 | 'xname' => ts('Year'), |
be2fb01f CW |
658 | 'yname' => ts('Amount (%1)', [1 => $config->defaultCurrency]), |
659 | ]; | |
6a488035 TO |
660 | if ($this->_params['charts']) { |
661 | // build chart. | |
dc61ee93 | 662 | CRM_Utils_Chart::reportChart($graphRows, $this->_params['charts'], $interval, $chartInfo); |
6a488035 TO |
663 | $this->assign('chartType', $this->_params['charts']); |
664 | } | |
665 | } | |
666 | ||
74cf4551 | 667 | /** |
ced9bfed EM |
668 | * Alter display of rows. |
669 | * | |
670 | * Iterate through the rows retrieved via SQL and make changes for display purposes, | |
671 | * such as rendering contacts as links. | |
672 | * | |
673 | * @param array $rows | |
674 | * Rows generated by SQL, with an array for each row. | |
74cf4551 | 675 | */ |
00be9182 | 676 | public function alterDisplay(&$rows) { |
72a9af52 NG |
677 | $entryFound = FALSE; |
678 | ||
6a488035 TO |
679 | foreach ($rows as $rowNum => $row) { |
680 | //Convert Display name into link | |
681 | if (array_key_exists('civicrm_contact_sort_name', $row) && | |
682 | array_key_exists('civicrm_contribution_contact_id', $row) | |
683 | ) { | |
684 | $url = CRM_Report_Utils_Report::getNextUrl('contribute/detail', | |
9d72cede EM |
685 | 'reset=1&force=1&id_op=eq&id_value=' . |
686 | $row['civicrm_contribution_contact_id'], | |
6a488035 TO |
687 | $this->_absoluteUrl, $this->_id, $this->_drilldownReport |
688 | ); | |
689 | $rows[$rowNum]['civicrm_contact_sort_name_link'] = $url; | |
690 | $rows[$rowNum]['civicrm_contact_sort_name_hover'] = ts("View Contribution Details for this Contact."); | |
72a9af52 | 691 | $entryFound = TRUE; |
6a488035 | 692 | } |
d62fab33 RN |
693 | |
694 | // convert campaign_id to campaign title | |
695 | if (array_key_exists('civicrm_contribution_campaign_id', $row)) { | |
696 | if ($value = $row['civicrm_contribution_campaign_id']) { | |
4238f84b | 697 | $rows[$rowNum]['civicrm_contribution_campaign_id'] = $this->campaigns[$value]; |
d62fab33 RN |
698 | $entryFound = TRUE; |
699 | } | |
700 | } | |
0a618a8d | 701 | // Display 'Yes' if the email is on hold (leave blank for no so it stands out better). |
702 | if (array_key_exists('civicrm_email_on_hold', $row)) { | |
703 | $rows[$rowNum]['civicrm_email_on_hold'] = $row['civicrm_email_on_hold'] ? ts('Yes') : ''; | |
704 | $entryFound = TRUE; | |
705 | } | |
a27c90f2 | 706 | |
c160fde8 | 707 | $entryFound = $this->alterDisplayAddressFields($row, $rows, $rowNum, NULL, 'List all contribution(s)') ? TRUE : $entryFound; |
708 | $entryFound = $this->alterDisplayContactFields($row, $rows, $rowNum, NULL, 'List all contribution(s)') ? TRUE : $entryFound; | |
49e1ea9f | 709 | |
5e0343e8 | 710 | if (!empty($row['civicrm_financial_trxn_card_type_id'])) { |
711 | $rows[$rowNum]['civicrm_financial_trxn_card_type_id'] = $this->getLabels($row['civicrm_financial_trxn_card_type_id'], 'CRM_Financial_DAO_FinancialTrxn', 'card_type_id'); | |
62a40853 E |
712 | $entryFound = TRUE; |
713 | } | |
714 | ||
72a9af52 NG |
715 | // skip looking further in rows, if first row itself doesn't |
716 | // have the column we need | |
717 | if (!$entryFound) { | |
718 | break; | |
a27c90f2 | 719 | } |
6a488035 TO |
720 | } |
721 | } | |
722 | ||
74cf4551 | 723 | /** |
4f1f1f2a | 724 | * Override "This Year" $op options |
74cf4551 EM |
725 | * @param string $type |
726 | * @param null $fieldName | |
727 | * | |
728 | * @return array | |
729 | */ | |
00be9182 | 730 | public function getOperationPair($type = "string", $fieldName = NULL) { |
6a488035 | 731 | if ($fieldName == 'yid') { |
be2fb01f | 732 | return [ |
9d72cede | 733 | 'calendar' => ts('Is Calendar Year'), |
21dfd5f5 | 734 | 'fiscal' => ts('Fiscal Year Starting'), |
be2fb01f | 735 | ]; |
6a488035 TO |
736 | } |
737 | return parent::getOperationPair($type, $fieldName); | |
738 | } | |
96025800 | 739 | |
6a488035 | 740 | } |