Commit | Line | Data |
---|---|---|
6a488035 | 1 | <?php |
6a488035 TO |
2 | /* |
3 | +--------------------------------------------------------------------+ | |
fee14197 | 4 | | CiviCRM version 5 | |
6a488035 | 5 | +--------------------------------------------------------------------+ |
6b83d5bd | 6 | | Copyright CiviCRM LLC (c) 2004-2019 | |
6a488035 TO |
7 | +--------------------------------------------------------------------+ |
8 | | This file is a part of CiviCRM. | | |
9 | | | | |
10 | | CiviCRM is free software; you can copy, modify, and distribute it | | |
11 | | under the terms of the GNU Affero General Public License | | |
12 | | Version 3, 19 November 2007 and the CiviCRM Licensing Exception. | | |
13 | | | | |
14 | | CiviCRM is distributed in the hope that it will be useful, but | | |
15 | | WITHOUT ANY WARRANTY; without even the implied warranty of | | |
16 | | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. | | |
17 | | See the GNU Affero General Public License for more details. | | |
18 | | | | |
19 | | You should have received a copy of the GNU Affero General Public | | |
20 | | License and the CiviCRM Licensing Exception along | | |
21 | | with this program; if not, contact CiviCRM LLC | | |
22 | | at info[AT]civicrm[DOT]org. If you have questions about the | | |
23 | | GNU Affero General Public License or the licensing of CiviCRM, | | |
24 | | see the CiviCRM license FAQ at http://civicrm.org/licensing | | |
25 | +--------------------------------------------------------------------+ | |
d25dd0ee | 26 | */ |
6a488035 TO |
27 | |
28 | /** | |
29 | * | |
30 | * @package CRM | |
6b83d5bd | 31 | * @copyright CiviCRM LLC (c) 2004-2019 |
6a488035 TO |
32 | */ |
33 | class CRM_Report_Form_Contribute_Summary extends CRM_Report_Form { | |
6a488035 TO |
34 | |
35 | protected $_charts = array( | |
36 | '' => 'Tabular', | |
37 | 'barChart' => 'Bar Chart', | |
38 | 'pieChart' => 'Pie Chart', | |
39 | ); | |
bb4049e2 | 40 | protected $_customGroupExtends = array('Contribution', 'Contact', 'Individual'); |
f813f78e | 41 | protected $_customGroupGroupBy = TRUE; |
6a488035 TO |
42 | |
43 | public $_drilldownReport = array('contribute/detail' => 'Link to Detail Report'); | |
44 | ||
35dd1d26 | 45 | /** |
46 | * To what frequency group-by a date column | |
47 | * | |
48 | * @var array | |
49 | */ | |
50 | protected $_groupByDateFreq = array( | |
51 | 'MONTH' => 'Month', | |
52 | 'YEARWEEK' => 'Week', | |
c5e4cf0e | 53 | 'DATE' => 'Day', |
35dd1d26 | 54 | 'QUARTER' => 'Quarter', |
55 | 'YEAR' => 'Year', | |
56 | 'FISCALYEAR' => 'Fiscal Year', | |
57 | ); | |
58 | ||
43c1fa19 | 59 | /** |
60 | * This report has been optimised for group filtering. | |
61 | * | |
62 | * CRM-19170 | |
63 | * | |
64 | * @var bool | |
65 | */ | |
66 | protected $groupFilterNotOptimised = FALSE; | |
67 | ||
74cf4551 | 68 | /** |
ae6f7290 | 69 | * Class constructor. |
74cf4551 | 70 | */ |
00be9182 | 71 | public function __construct() { |
6a488035 | 72 | $this->_columns = array( |
a1a2a83d TO |
73 | 'civicrm_contact' => array( |
74 | 'dao' => 'CRM_Contact_DAO_Contact', | |
3da9fd47 | 75 | 'fields' => array_merge( |
76 | $this->getBasicContactFields(), | |
77 | array( | |
78 | 'sort_name' => array( | |
79 | 'title' => ts('Contact Name'), | |
80 | 'no_repeat' => TRUE, | |
ac58d438 | 81 | ), |
3da9fd47 | 82 | ) |
83 | ), | |
1ebe1a57 | 84 | 'filters' => $this->getBasicContactFilters(array('deceased' => NULL)), |
a1a2a83d TO |
85 | 'grouping' => 'contact-fields', |
86 | 'group_bys' => array( | |
87 | 'id' => array('title' => ts('Contact ID')), | |
88 | 'sort_name' => array( | |
89 | 'title' => ts('Contact Name'), | |
6a488035 TO |
90 | ), |
91 | ), | |
a1a2a83d TO |
92 | ), |
93 | 'civicrm_email' => array( | |
94 | 'dao' => 'CRM_Core_DAO_Email', | |
95 | 'fields' => array( | |
96 | 'email' => array( | |
97 | 'title' => ts('Email'), | |
98 | 'no_repeat' => TRUE, | |
6a488035 TO |
99 | ), |
100 | ), | |
a1a2a83d TO |
101 | 'grouping' => 'contact-fields', |
102 | ), | |
1f76c4cd E |
103 | 'civicrm_line_item' => array( |
104 | 'dao' => 'CRM_Price_DAO_LineItem', | |
105 | ), | |
a1a2a83d TO |
106 | 'civicrm_phone' => array( |
107 | 'dao' => 'CRM_Core_DAO_Phone', | |
108 | 'fields' => array( | |
109 | 'phone' => array( | |
110 | 'title' => ts('Phone'), | |
111 | 'no_repeat' => TRUE, | |
6a488035 TO |
112 | ), |
113 | ), | |
a1a2a83d TO |
114 | 'grouping' => 'contact-fields', |
115 | ), | |
116 | 'civicrm_financial_type' => array( | |
117 | 'dao' => 'CRM_Financial_DAO_FinancialType', | |
118 | 'fields' => array('financial_type' => NULL), | |
119 | 'grouping' => 'contri-fields', | |
120 | 'group_bys' => array( | |
121 | 'financial_type' => array('title' => ts('Financial Type')), | |
122 | ), | |
123 | ), | |
124 | 'civicrm_contribution' => array( | |
125 | 'dao' => 'CRM_Contribute_DAO_Contribution', | |
9d72cede | 126 | //'bao' => 'CRM_Contribute_BAO_Contribution', |
a1a2a83d | 127 | 'fields' => array( |
3b654372 | 128 | 'contribution_status_id' => array( |
8a347ca5 RO |
129 | 'title' => ts('Contribution Status'), |
130 | ), | |
a1a2a83d TO |
131 | 'contribution_source' => array('title' => ts('Source')), |
132 | 'currency' => array( | |
133 | 'required' => TRUE, | |
134 | 'no_display' => TRUE, | |
6a488035 | 135 | ), |
a1a2a83d TO |
136 | 'total_amount' => array( |
137 | 'title' => ts('Contribution Amount Stats'), | |
138 | 'default' => TRUE, | |
139 | 'statistics' => array( | |
a1a2a83d | 140 | 'count' => ts('Contributions'), |
8a347ca5 | 141 | 'sum' => ts('Contribution Aggregate'), |
a1a2a83d | 142 | 'avg' => ts('Contribution Avg'), |
9d72cede | 143 | ), |
6a488035 | 144 | ), |
5afce5ad | 145 | 'non_deductible_amount' => array( |
146 | 'title' => ts('Non-deductible Amount'), | |
147 | ), | |
a1a2a83d TO |
148 | ), |
149 | 'grouping' => 'contri-fields', | |
150 | 'filters' => array( | |
151 | 'receive_date' => array('operatorType' => CRM_Report_Form::OP_DATE), | |
f53a072c | 152 | 'thankyou_date' => array('operatorType' => CRM_Report_Form::OP_DATE), |
a1a2a83d TO |
153 | 'contribution_status_id' => array( |
154 | 'title' => ts('Contribution Status'), | |
155 | 'operatorType' => CRM_Report_Form::OP_MULTISELECT, | |
156 | 'options' => CRM_Contribute_PseudoConstant::contributionStatus(), | |
157 | 'default' => array(1), | |
158 | 'type' => CRM_Utils_Type::T_INT, | |
159 | ), | |
160 | 'currency' => array( | |
fd6a6828 | 161 | 'title' => ts('Currency'), |
a1a2a83d TO |
162 | 'operatorType' => CRM_Report_Form::OP_MULTISELECT, |
163 | 'options' => CRM_Core_OptionGroup::values('currencies_enabled'), | |
164 | 'default' => NULL, | |
165 | 'type' => CRM_Utils_Type::T_STRING, | |
166 | ), | |
167 | 'financial_type_id' => array( | |
168 | 'title' => ts('Financial Type'), | |
169 | 'operatorType' => CRM_Report_Form::OP_MULTISELECT, | |
803e39be | 170 | 'options' => CRM_Financial_BAO_FinancialType::getAvailableFinancialTypes(), |
a1a2a83d TO |
171 | 'type' => CRM_Utils_Type::T_INT, |
172 | ), | |
173 | 'contribution_page_id' => array( | |
174 | 'title' => ts('Contribution Page'), | |
175 | 'operatorType' => CRM_Report_Form::OP_MULTISELECT, | |
176 | 'options' => CRM_Contribute_PseudoConstant::contributionPage(), | |
177 | 'type' => CRM_Utils_Type::T_INT, | |
178 | ), | |
179 | 'total_amount' => array( | |
180 | 'title' => ts('Contribution Amount'), | |
181 | ), | |
5afce5ad | 182 | 'non_deductible_amount' => array( |
183 | 'title' => ts('Non-deductible Amount'), | |
184 | ), | |
a1a2a83d TO |
185 | 'total_sum' => array( |
186 | 'title' => ts('Contribution Aggregate'), | |
187 | 'type' => CRM_Report_Form::OP_INT, | |
188 | 'dbAlias' => 'civicrm_contribution_total_amount_sum', | |
189 | 'having' => TRUE, | |
190 | ), | |
191 | 'total_count' => array( | |
192 | 'title' => ts('Contribution Count'), | |
193 | 'type' => CRM_Report_Form::OP_INT, | |
194 | 'dbAlias' => 'civicrm_contribution_total_amount_count', | |
195 | 'having' => TRUE, | |
196 | ), | |
197 | 'total_avg' => array( | |
198 | 'title' => ts('Contribution Avg'), | |
199 | 'type' => CRM_Report_Form::OP_INT, | |
200 | 'dbAlias' => 'civicrm_contribution_total_amount_avg', | |
201 | 'having' => TRUE, | |
9b0380d9 DS |
202 | ), |
203 | ), | |
a1a2a83d TO |
204 | 'group_bys' => array( |
205 | 'receive_date' => array( | |
206 | 'frequency' => TRUE, | |
207 | 'default' => TRUE, | |
208 | 'chart' => TRUE, | |
9b0380d9 | 209 | ), |
a1a2a83d | 210 | 'contribution_source' => NULL, |
8a347ca5 RO |
211 | 'contribution_status_id' => array( |
212 | 'title' => ts('Contribution Status'), | |
213 | 'operatorType' => CRM_Report_Form::OP_MULTISELECT, | |
214 | 'options' => CRM_Contribute_PseudoConstant::contributionStatus(), | |
215 | 'default' => array(1), | |
216 | 'type' => CRM_Utils_Type::T_INT, | |
217 | ), | |
a1a2a83d TO |
218 | ), |
219 | ), | |
fa5fb88c E |
220 | 'civicrm_financial_trxn' => array( |
221 | 'dao' => 'CRM_Financial_DAO_FinancialTrxn', | |
222 | 'fields' => array( | |
5e0343e8 | 223 | 'card_type_id' => array( |
d72b084a | 224 | 'title' => ts('Credit Card Type'), |
5e0343e8 | 225 | 'dbAlias' => 'GROUP_CONCAT(financial_trxn_civireport.card_type_id SEPARATOR ",")', |
fa5fb88c E |
226 | ), |
227 | ), | |
228 | 'filters' => array( | |
5e0343e8 | 229 | 'card_type_id' => array( |
fa5fb88c E |
230 | 'title' => ts('Credit Card Type'), |
231 | 'operatorType' => CRM_Report_Form::OP_MULTISELECT, | |
d72b084a | 232 | 'options' => CRM_Financial_DAO_FinancialTrxn::buildOptions('card_type_id'), |
fa5fb88c E |
233 | 'default' => NULL, |
234 | 'type' => CRM_Utils_Type::T_STRING, | |
235 | ), | |
236 | ), | |
237 | ), | |
433465bc PN |
238 | 'civicrm_batch' => array( |
239 | 'dao' => 'CRM_Batch_DAO_EntityBatch', | |
240 | 'grouping' => 'contri-fields', | |
8d2ca6b8 E |
241 | 'fields' => array( |
242 | 'batch_id' => array( | |
243 | 'name' => 'batch_id', | |
244 | 'title' => ts('Batch Title'), | |
c76af7c4 E |
245 | 'dbAlias' => 'GROUP_CONCAT(DISTINCT batch_civireport.batch_id |
246 | ORDER BY batch_civireport.batch_id SEPARATOR ",")', | |
8d2ca6b8 E |
247 | ), |
248 | ), | |
433465bc PN |
249 | 'filters' => array( |
250 | 'batch_id' => array( | |
251 | 'title' => ts('Batch Title'), | |
252 | 'operatorType' => CRM_Report_Form::OP_MULTISELECT, | |
253 | 'options' => CRM_Batch_BAO_Batch::getBatches(), | |
254 | 'type' => CRM_Utils_Type::T_INT, | |
255 | ), | |
256 | ), | |
8d2ca6b8 | 257 | 'group_bys' => array( |
236e4b41 | 258 | 'batch_id' => array('title' => ts('Batch Title')), |
8d2ca6b8 | 259 | ), |
433465bc | 260 | ), |
a1a2a83d TO |
261 | 'civicrm_contribution_soft' => array( |
262 | 'dao' => 'CRM_Contribute_DAO_ContributionSoft', | |
263 | 'fields' => array( | |
264 | 'soft_amount' => array( | |
265 | 'title' => ts('Soft Credit Amount Stats'), | |
266 | 'name' => 'amount', | |
267 | 'statistics' => array( | |
a1a2a83d | 268 | 'count' => ts('Soft Credits'), |
8a347ca5 | 269 | 'sum' => ts('Soft Credit Aggregate'), |
a1a2a83d | 270 | 'avg' => ts('Soft Credit Avg'), |
9d72cede | 271 | ), |
9b0380d9 DS |
272 | ), |
273 | ), | |
a1a2a83d TO |
274 | 'grouping' => 'contri-fields', |
275 | 'filters' => array( | |
276 | 'amount' => array( | |
277 | 'title' => ts('Soft Credit Amount'), | |
278 | ), | |
279 | 'soft_credit_type_id' => array( | |
fd6a6828 | 280 | 'title' => ts('Soft Credit Type'), |
a1a2a83d TO |
281 | 'operatorType' => CRM_Report_Form::OP_MULTISELECT, |
282 | 'options' => CRM_Core_OptionGroup::values('soft_credit_type'), | |
283 | 'default' => NULL, | |
284 | 'type' => CRM_Utils_Type::T_STRING, | |
285 | ), | |
286 | 'soft_sum' => array( | |
287 | 'title' => ts('Soft Credit Aggregate'), | |
288 | 'type' => CRM_Report_Form::OP_INT, | |
289 | 'dbAlias' => 'civicrm_contribution_soft_soft_amount_sum', | |
290 | 'having' => TRUE, | |
291 | ), | |
292 | 'soft_count' => array( | |
293 | 'title' => ts('Soft Credits Count'), | |
294 | 'type' => CRM_Report_Form::OP_INT, | |
295 | 'dbAlias' => 'civicrm_contribution_soft_soft_amount_count', | |
296 | 'having' => TRUE, | |
297 | ), | |
298 | 'soft_avg' => array( | |
299 | 'title' => ts('Soft Credit Avg'), | |
300 | 'type' => CRM_Report_Form::OP_INT, | |
301 | 'dbAlias' => 'civicrm_contribution_soft_soft_amount_avg', | |
302 | 'having' => TRUE, | |
303 | ), | |
304 | ), | |
305 | ), | |
306 | ) + $this->addAddressFields(); | |
6a488035 | 307 | |
bb3ab6f8 | 308 | $this->addCampaignFields('civicrm_contribution', TRUE); |
35d8497b | 309 | |
16e2e80c CW |
310 | $this->_tagFilter = TRUE; |
311 | $this->_groupFilter = TRUE; | |
35d8497b | 312 | $this->_currencyColumn = 'civicrm_contribution_currency'; |
6a488035 TO |
313 | parent::__construct(); |
314 | } | |
315 | ||
74cf4551 | 316 | /** |
ae6f7290 | 317 | * Set select clause. |
74cf4551 | 318 | */ |
00be9182 | 319 | public function select() { |
6a488035 TO |
320 | $select = array(); |
321 | $this->_columnHeaders = array(); | |
322 | foreach ($this->_columns as $tableName => $table) { | |
323 | if (array_key_exists('group_bys', $table)) { | |
324 | foreach ($table['group_bys'] as $fieldName => $field) { | |
a7488080 | 325 | if (!empty($this->_params['group_bys'][$fieldName])) { |
6a488035 TO |
326 | switch (CRM_Utils_Array::value($fieldName, $this->_params['group_bys_freq'])) { |
327 | case 'YEARWEEK': | |
9d72cede EM |
328 | $select[] = "DATE_SUB({$field['dbAlias']}, INTERVAL WEEKDAY({$field['dbAlias']}) DAY) AS {$tableName}_{$fieldName}_start"; |
329 | $select[] = "YEARWEEK({$field['dbAlias']}) AS {$tableName}_{$fieldName}_subtotal"; | |
330 | $select[] = "WEEKOFYEAR({$field['dbAlias']}) AS {$tableName}_{$fieldName}_interval"; | |
c5e4cf0e | 331 | $field['title'] = ts('Week Beginning'); |
6a488035 TO |
332 | break; |
333 | ||
334 | case 'YEAR': | |
9d72cede EM |
335 | $select[] = "MAKEDATE(YEAR({$field['dbAlias']}), 1) AS {$tableName}_{$fieldName}_start"; |
336 | $select[] = "YEAR({$field['dbAlias']}) AS {$tableName}_{$fieldName}_subtotal"; | |
337 | $select[] = "YEAR({$field['dbAlias']}) AS {$tableName}_{$fieldName}_interval"; | |
c5e4cf0e | 338 | $field['title'] = ts('Year Beginning'); |
6a488035 TO |
339 | break; |
340 | ||
35dd1d26 | 341 | case 'FISCALYEAR': |
342 | $config = CRM_Core_Config::singleton(); | |
343 | $fy = $config->fiscalYearStart; | |
344 | $fiscal = self::fiscalYearOffset($field['dbAlias']); | |
345 | ||
346 | $select[] = "DATE_ADD(MAKEDATE({$fiscal}, 1), INTERVAL ({$fy{'M'}})-1 MONTH) AS {$tableName}_{$fieldName}_start"; | |
347 | $select[] = "{$fiscal} AS {$tableName}_{$fieldName}_subtotal"; | |
348 | $select[] = "{$fiscal} AS {$tableName}_{$fieldName}_interval"; | |
c5e4cf0e | 349 | $field['title'] = ts('Fiscal Year Beginning'); |
35dd1d26 | 350 | break; |
351 | ||
6a488035 | 352 | case 'MONTH': |
9d72cede EM |
353 | $select[] = "DATE_SUB({$field['dbAlias']}, INTERVAL (DAYOFMONTH({$field['dbAlias']})-1) DAY) as {$tableName}_{$fieldName}_start"; |
354 | $select[] = "MONTH({$field['dbAlias']}) AS {$tableName}_{$fieldName}_subtotal"; | |
355 | $select[] = "MONTHNAME({$field['dbAlias']}) AS {$tableName}_{$fieldName}_interval"; | |
c5e4cf0e | 356 | $field['title'] = ts('Month Beginning'); |
357 | break; | |
358 | ||
359 | case 'DATE': | |
360 | $select[] = "DATE({$field['dbAlias']}) as {$tableName}_{$fieldName}_start"; | |
361 | $field['title'] = ts('Date'); | |
6a488035 TO |
362 | break; |
363 | ||
364 | case 'QUARTER': | |
9d72cede EM |
365 | $select[] = "STR_TO_DATE(CONCAT( 3 * QUARTER( {$field['dbAlias']} ) -2 , '/', '1', '/', YEAR( {$field['dbAlias']} ) ), '%m/%d/%Y') AS {$tableName}_{$fieldName}_start"; |
366 | $select[] = "QUARTER({$field['dbAlias']}) AS {$tableName}_{$fieldName}_subtotal"; | |
367 | $select[] = "QUARTER({$field['dbAlias']}) AS {$tableName}_{$fieldName}_interval"; | |
6a488035 TO |
368 | $field['title'] = 'Quarter'; |
369 | break; | |
370 | } | |
a7488080 | 371 | if (!empty($this->_params['group_bys_freq'][$fieldName])) { |
891a34b4 | 372 | $this->_interval = $this->_params['group_bys_freq'][$fieldName]; |
c5e4cf0e | 373 | $this->_columnHeaders["{$tableName}_{$fieldName}_start"]['title'] = $field['title']; |
6a488035 TO |
374 | $this->_columnHeaders["{$tableName}_{$fieldName}_start"]['type'] = $field['type']; |
375 | $this->_columnHeaders["{$tableName}_{$fieldName}_start"]['group_by'] = $this->_params['group_bys_freq'][$fieldName]; | |
376 | ||
b44e3f84 | 377 | // just to make sure these values are transferred to rows. |
6a488035 TO |
378 | // since we need that for calculation purpose, |
379 | // e.g making subtotals look nicer or graphs | |
380 | $this->_columnHeaders["{$tableName}_{$fieldName}_interval"] = array('no_display' => TRUE); | |
381 | $this->_columnHeaders["{$tableName}_{$fieldName}_subtotal"] = array('no_display' => TRUE); | |
382 | } | |
383 | } | |
384 | } | |
385 | } | |
386 | ||
387 | if (array_key_exists('fields', $table)) { | |
388 | foreach ($table['fields'] as $fieldName => $field) { | |
9d72cede EM |
389 | if (!empty($field['required']) || |
390 | !empty($this->_params['fields'][$fieldName]) | |
391 | ) { | |
6a488035 | 392 | // only include statistics columns if set |
a7488080 | 393 | if (!empty($field['statistics'])) { |
6a488035 | 394 | foreach ($field['statistics'] as $stat => $label) { |
030eb4f9 | 395 | $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['title'] = $label; |
396 | $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['type'] = $field['type']; | |
397 | $this->_statFields[] = "{$tableName}_{$fieldName}_{$stat}"; | |
6a488035 TO |
398 | switch (strtolower($stat)) { |
399 | case 'sum': | |
400 | $select[] = "SUM({$field['dbAlias']}) as {$tableName}_{$fieldName}_{$stat}"; | |
6a488035 TO |
401 | break; |
402 | ||
403 | case 'count': | |
404 | $select[] = "COUNT({$field['dbAlias']}) as {$tableName}_{$fieldName}_{$stat}"; | |
405 | $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['type'] = CRM_Utils_Type::T_INT; | |
6a488035 TO |
406 | break; |
407 | ||
408 | case 'avg': | |
409 | $select[] = "ROUND(AVG({$field['dbAlias']}),2) as {$tableName}_{$fieldName}_{$stat}"; | |
6a488035 TO |
410 | break; |
411 | } | |
412 | } | |
413 | } | |
414 | else { | |
415 | $select[] = "{$field['dbAlias']} as {$tableName}_{$fieldName}"; | |
416 | $this->_columnHeaders["{$tableName}_{$fieldName}"]['type'] = CRM_Utils_Array::value('type', $field); | |
417 | $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = CRM_Utils_Array::value('title', $field); | |
418 | } | |
419 | } | |
420 | } | |
421 | } | |
422 | } | |
423 | ||
d1641c51 | 424 | $this->_selectClauses = $select; |
6a488035 TO |
425 | $this->_select = "SELECT " . implode(', ', $select) . " "; |
426 | } | |
427 | ||
74cf4551 | 428 | /** |
ae6f7290 | 429 | * Set form rules. |
430 | * | |
431 | * @param array $fields | |
432 | * @param array $files | |
433 | * @param CRM_Report_Form_Contribute_Summary $self | |
74cf4551 EM |
434 | * |
435 | * @return array | |
436 | */ | |
00be9182 | 437 | public static function formRule($fields, $files, $self) { |
ae6f7290 | 438 | // Check for searching combination of display columns and |
439 | // grouping criteria | |
6a488035 TO |
440 | $ignoreFields = array('total_amount', 'sort_name'); |
441 | $errors = $self->customDataFormRule($fields, $ignoreFields); | |
442 | ||
a7488080 | 443 | if (empty($fields['fields']['total_amount'])) { |
6a488035 | 444 | foreach (array( |
c86d4e7c SL |
445 | 'total_count_value', |
446 | 'total_sum_value', | |
447 | 'total_avg_value', | |
448 | ) as $val) { | |
a7488080 | 449 | if (!empty($fields[$val])) { |
6a488035 TO |
450 | $errors[$val] = ts("Please select the Amount Statistics"); |
451 | } | |
452 | } | |
453 | } | |
454 | ||
455 | return $errors; | |
456 | } | |
457 | ||
ae6f7290 | 458 | /** |
459 | * Set from clause. | |
460 | * | |
461 | * @param string $entity | |
73b448bf | 462 | * |
463 | * @todo fix function signature to match parent. Remove hacky passing of $entity | |
464 | * to acheive unclear results. | |
ae6f7290 | 465 | */ |
030eb4f9 | 466 | public function from($entity = NULL) { |
467 | $softCreditJoinType = "LEFT"; | |
9d72cede EM |
468 | if (!empty($this->_params['fields']['soft_amount']) && |
469 | empty($this->_params['fields']['total_amount']) | |
470 | ) { | |
9b0380d9 | 471 | // if its only soft credit stats, use inner join |
030eb4f9 | 472 | $softCreditJoinType = "INNER"; |
473 | } | |
474 | ||
475 | $softCreditJoin = "{$softCreditJoinType} JOIN civicrm_contribution_soft {$this->_aliases['civicrm_contribution_soft']} | |
476 | ON {$this->_aliases['civicrm_contribution_soft']}.contribution_id = {$this->_aliases['civicrm_contribution']}.id"; | |
477 | if ($entity == 'contribution' || empty($this->_params['fields']['soft_amount'])) { | |
478 | $softCreditJoin .= " AND {$this->_aliases['civicrm_contribution_soft']}.id = (SELECT MIN(id) FROM civicrm_contribution_soft cs WHERE cs.contribution_id = {$this->_aliases['civicrm_contribution']}.id) "; | |
9b0380d9 DS |
479 | } |
480 | ||
43c1fa19 | 481 | $this->setFromBase('civicrm_contact'); |
482 | ||
483 | $this->_from .= " | |
f813f78e | 484 | INNER JOIN civicrm_contribution {$this->_aliases['civicrm_contribution']} |
6a488035 TO |
485 | ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_contribution']}.contact_id AND |
486 | {$this->_aliases['civicrm_contribution']}.is_test = 0 | |
030eb4f9 | 487 | {$softCreditJoin} |
f813f78e | 488 | LEFT JOIN civicrm_financial_type {$this->_aliases['civicrm_financial_type']} |
6a488035 | 489 | ON {$this->_aliases['civicrm_contribution']}.financial_type_id ={$this->_aliases['civicrm_financial_type']}.id |
18f511e2 | 490 | "; |
f813f78e | 491 | |
18f511e2 | 492 | $this->joinAddressFromContact(); |
493 | $this->joinPhoneFromContact(); | |
494 | $this->joinEmailFromContact(); | |
6a488035 | 495 | |
8d2ca6b8 E |
496 | //for contribution batches |
497 | if ($this->isTableSelected('civicrm_batch')) { | |
433465bc | 498 | $this->_from .= " |
8d2ca6b8 E |
499 | LEFT JOIN civicrm_entity_financial_trxn eft |
500 | ON eft.entity_id = {$this->_aliases['civicrm_contribution']}.id AND | |
501 | eft.entity_table = 'civicrm_contribution' | |
502 | LEFT JOIN civicrm_entity_batch {$this->_aliases['civicrm_batch']} | |
503 | ON ({$this->_aliases['civicrm_batch']}.entity_id = eft.financial_trxn_id | |
504 | AND {$this->_aliases['civicrm_batch']}.entity_table = 'civicrm_financial_trxn')"; | |
433465bc | 505 | } |
fa5fb88c | 506 | |
08730aaf | 507 | $this->addFinancialTrxnFromClause(); |
6a488035 TO |
508 | } |
509 | ||
ae6f7290 | 510 | /** |
511 | * Set group by clause. | |
512 | */ | |
00be9182 | 513 | public function groupBy() { |
6a488035 | 514 | $this->_groupBy = ""; |
14a59757 | 515 | $groupByColumns = array(); |
6a488035 | 516 | $append = FALSE; |
34c3a72b | 517 | if (!empty($this->_params['group_bys']) && |
518 | is_array($this->_params['group_bys']) | |
6a488035 TO |
519 | ) { |
520 | foreach ($this->_columns as $tableName => $table) { | |
521 | if (array_key_exists('group_bys', $table)) { | |
522 | foreach ($table['group_bys'] as $fieldName => $field) { | |
a7488080 CW |
523 | if (!empty($this->_params['group_bys'][$fieldName])) { |
524 | if (!empty($field['chart'])) { | |
6a488035 TO |
525 | $this->assign('chartSupported', TRUE); |
526 | } | |
527 | ||
9d72cede EM |
528 | if (!empty($table['group_bys'][$fieldName]['frequency']) && |
529 | !empty($this->_params['group_bys_freq'][$fieldName]) | |
530 | ) { | |
6a488035 | 531 | |
bad98dd5 | 532 | $append = "YEAR({$field['dbAlias']});;"; |
6a488035 | 533 | if (in_array(strtolower($this->_params['group_bys_freq'][$fieldName]), |
9d72cede EM |
534 | array('year') |
535 | )) { | |
6a488035 TO |
536 | $append = ''; |
537 | } | |
35dd1d26 | 538 | if ($this->_params['group_bys_freq'][$fieldName] == 'FISCALYEAR') { |
14a59757 | 539 | $groupByColumns[] = self::fiscalYearOffset($field['dbAlias']); |
35dd1d26 | 540 | } |
541 | else { | |
14a59757 | 542 | $groupByColumns[] = "$append {$this->_params['group_bys_freq'][$fieldName]}({$field['dbAlias']})"; |
35dd1d26 | 543 | } |
6a488035 TO |
544 | $append = TRUE; |
545 | } | |
546 | else { | |
14a59757 | 547 | $groupByColumns[] = $field['dbAlias']; |
6a488035 TO |
548 | } |
549 | } | |
550 | } | |
551 | } | |
552 | } | |
553 | ||
554 | if (!empty($this->_statFields) && | |
14a59757 | 555 | (($append && count($groupByColumns) <= 1) || (!$append)) && |
9d72cede | 556 | !$this->_having |
6a488035 TO |
557 | ) { |
558 | $this->_rollup = " WITH ROLLUP"; | |
559 | } | |
bad98dd5 | 560 | $groupBy = array(); |
14a59757 | 561 | foreach ($groupByColumns as $key => $val) { |
dc852c7b | 562 | if (strpos($val, ';;') !== FALSE) { |
bad98dd5 | 563 | $groupBy = array_merge($groupBy, explode(';;', $val)); |
564 | } | |
565 | else { | |
14a59757 | 566 | $groupBy[] = $groupByColumns[$key]; |
bad98dd5 | 567 | } |
568 | } | |
569 | $this->_groupBy = "GROUP BY " . implode(', ', $groupBy); | |
6a488035 TO |
570 | } |
571 | else { | |
d1641c51 | 572 | $groupBy = "{$this->_aliases['civicrm_contact']}.id"; |
6a488035 TO |
573 | $this->_groupBy = "GROUP BY {$this->_aliases['civicrm_contact']}.id"; |
574 | } | |
d1641c51 | 575 | $this->_groupBy .= $this->_rollup; |
bad98dd5 | 576 | // append select with ANY_VALUE() keyword |
36d2f4d5 | 577 | $this->_select = CRM_Contact_BAO_Query::appendAnyValueToSelect($this->_selectClauses, $groupBy); |
6a488035 TO |
578 | } |
579 | ||
ae6f7290 | 580 | /** |
581 | * Store having clauses as an array. | |
582 | */ | |
00be9182 | 583 | public function storeWhereHavingClauseArray() { |
51fa20cb | 584 | parent::storeWhereHavingClauseArray(); |
9d72cede EM |
585 | if (empty($this->_params['fields']['soft_amount']) && |
586 | !empty($this->_havingClauses) | |
587 | ) { | |
51fa20cb | 588 | foreach ($this->_havingClauses as $key => $havingClause) { |
9d72cede | 589 | if (stristr($havingClause, 'soft_soft')) { |
51fa20cb | 590 | unset($this->_havingClauses[$key]); |
591 | } | |
592 | } | |
0d668154 | 593 | } |
51fa20cb | 594 | } |
595 | ||
74cf4551 | 596 | /** |
ae6f7290 | 597 | * Set statistics. |
598 | * | |
599 | * @param array $rows | |
74cf4551 EM |
600 | * |
601 | * @return array | |
602 | */ | |
00be9182 | 603 | public function statistics(&$rows) { |
6a488035 TO |
604 | $statistics = parent::statistics($rows); |
605 | ||
9b0380d9 | 606 | $softCredit = CRM_Utils_Array::value('soft_amount', $this->_params['fields']); |
ae6f7290 | 607 | $onlySoftCredit = $softCredit && !CRM_Utils_Array::value('total_amount', $this->_params['fields']); |
030eb4f9 | 608 | $group = "\nGROUP BY {$this->_aliases['civicrm_contribution']}.currency"; |
35d8497b | 609 | |
030eb4f9 | 610 | $this->from('contribution'); |
c90bacf9 | 611 | $this->customDataFrom(); |
6dd5c484 | 612 | |
c5b8382f | 613 | $contriQuery = " |
9b0380d9 DS |
614 | COUNT({$this->_aliases['civicrm_contribution']}.total_amount ) as civicrm_contribution_total_amount_count, |
615 | SUM({$this->_aliases['civicrm_contribution']}.total_amount ) as civicrm_contribution_total_amount_sum, | |
616 | ROUND(AVG({$this->_aliases['civicrm_contribution']}.total_amount), 2) as civicrm_contribution_total_amount_avg, | |
030eb4f9 | 617 | {$this->_aliases['civicrm_contribution']}.currency as currency |
35dd1d26 | 618 | {$this->_from} {$this->_where}"; |
9b0380d9 DS |
619 | |
620 | if ($softCredit) { | |
030eb4f9 | 621 | $this->from(); |
0b0d9821 | 622 | $this->customDataFrom(); |
3b413375 | 623 | $select = " |
9b0380d9 DS |
624 | COUNT({$this->_aliases['civicrm_contribution_soft']}.amount ) as civicrm_contribution_soft_soft_amount_count, |
625 | SUM({$this->_aliases['civicrm_contribution_soft']}.amount ) as civicrm_contribution_soft_soft_amount_sum, | |
3b413375 | 626 | ROUND(AVG({$this->_aliases['civicrm_contribution_soft']}.amount), 2) as civicrm_contribution_soft_soft_amount_avg"; |
627 | $contriQuery = "{$select}, {$contriQuery}"; | |
628 | $softSQL = "SELECT {$select}, {$this->_aliases['civicrm_contribution']}.currency as currency | |
629 | {$this->_from} {$this->_where} {$group} {$this->_having}"; | |
9b0380d9 | 630 | } |
c93f6d83 | 631 | |
35dd1d26 | 632 | $contriSQL = "SELECT {$contriQuery} {$group} {$this->_having}"; |
030eb4f9 | 633 | $contriDAO = CRM_Core_DAO::executeQuery($contriSQL); |
634 | ||
35dd1d26 | 635 | $totalAmount = $average = $mode = $median = $softTotalAmount = $softAverage = array(); |
ae6884ce | 636 | $count = $softCount = 0; |
030eb4f9 | 637 | while ($contriDAO->fetch()) { |
6c552737 | 638 | $totalAmount[] |
030eb4f9 | 639 | = CRM_Utils_Money::format($contriDAO->civicrm_contribution_total_amount_sum, $contriDAO->currency) . |
640 | " (" . $contriDAO->civicrm_contribution_total_amount_count . ")"; | |
641 | $average[] = CRM_Utils_Money::format($contriDAO->civicrm_contribution_total_amount_avg, $contriDAO->currency); | |
642 | $count += $contriDAO->civicrm_contribution_total_amount_count; | |
643 | } | |
c93f6d83 | 644 | |
35dd1d26 | 645 | $groupBy = "\n{$group}, {$this->_aliases['civicrm_contribution']}.total_amount"; |
646 | $orderBy = "\nORDER BY civicrm_contribution_total_amount_count DESC"; | |
d1641c51 | 647 | $modeSQL = "SELECT MAX(civicrm_contribution_total_amount_count) as civicrm_contribution_total_amount_count, |
648 | SUBSTRING_INDEX(GROUP_CONCAT(amount ORDER BY mode.civicrm_contribution_total_amount_count DESC SEPARATOR ';'), ';', 1) as amount, | |
649 | currency | |
650 | FROM (SELECT {$this->_aliases['civicrm_contribution']}.total_amount as amount, | |
35dd1d26 | 651 | {$contriQuery} {$groupBy} {$orderBy}) as mode GROUP BY currency"; |
652 | ||
8446bae6 | 653 | $mode = $this->calculateMode($modeSQL); |
654 | $median = $this->calculateMedian(); | |
35dd1d26 | 655 | |
030eb4f9 | 656 | if ($softCredit) { |
657 | $softDAO = CRM_Core_DAO::executeQuery($softSQL); | |
658 | while ($softDAO->fetch()) { | |
6c552737 | 659 | $softTotalAmount[] |
030eb4f9 | 660 | = CRM_Utils_Money::format($softDAO->civicrm_contribution_soft_soft_amount_sum, $softDAO->currency) . |
661 | " (" . $softDAO->civicrm_contribution_soft_soft_amount_count . ")"; | |
662 | $softAverage[] = CRM_Utils_Money::format($softDAO->civicrm_contribution_soft_soft_amount_avg, $softDAO->currency); | |
663 | $softCount += $softDAO->civicrm_contribution_soft_soft_amount_count; | |
9b0380d9 DS |
664 | } |
665 | } | |
c93f6d83 | 666 | |
9b0380d9 | 667 | if (!$onlySoftCredit) { |
35d8497b | 668 | $statistics['counts']['amount'] = array( |
669 | 'title' => ts('Total Amount'), | |
670 | 'value' => implode(', ', $totalAmount), | |
671 | 'type' => CRM_Utils_Type::T_STRING, | |
672 | ); | |
673 | $statistics['counts']['count'] = array( | |
be205937 | 674 | 'title' => ts('Total Contributions'), |
35d8497b | 675 | 'value' => $count, |
676 | ); | |
677 | $statistics['counts']['avg'] = array( | |
678 | 'title' => ts('Average'), | |
679 | 'value' => implode(', ', $average), | |
680 | 'type' => CRM_Utils_Type::T_STRING, | |
681 | ); | |
35dd1d26 | 682 | $statistics['counts']['mode'] = array( |
683 | 'title' => ts('Mode'), | |
684 | 'value' => implode(', ', $mode), | |
685 | 'type' => CRM_Utils_Type::T_STRING, | |
686 | ); | |
687 | $statistics['counts']['median'] = array( | |
688 | 'title' => ts('Median'), | |
689 | 'value' => implode(', ', $median), | |
690 | 'type' => CRM_Utils_Type::T_STRING, | |
691 | ); | |
9b0380d9 DS |
692 | } |
693 | if ($softCredit) { | |
694 | $statistics['counts']['soft_amount'] = array( | |
695 | 'title' => ts('Total Soft Credit Amount'), | |
696 | 'value' => implode(', ', $softTotalAmount), | |
697 | 'type' => CRM_Utils_Type::T_STRING, | |
698 | ); | |
699 | $statistics['counts']['soft_count'] = array( | |
700 | 'title' => ts('Total Soft Credits'), | |
701 | 'value' => $softCount, | |
702 | ); | |
703 | $statistics['counts']['soft_avg'] = array( | |
704 | 'title' => ts('Average Soft Credit'), | |
705 | 'value' => implode(', ', $softAverage), | |
706 | 'type' => CRM_Utils_Type::T_STRING, | |
707 | ); | |
6a488035 TO |
708 | } |
709 | return $statistics; | |
710 | } | |
711 | ||
ae6f7290 | 712 | /** |
713 | * Post process function. | |
714 | */ | |
00be9182 | 715 | public function postProcess() { |
0c00c2f2 | 716 | $this->buildACLClause($this->_aliases['civicrm_contact']); |
6a488035 TO |
717 | parent::postProcess(); |
718 | } | |
719 | ||
ae6f7290 | 720 | /** |
721 | * Build table rows for output. | |
722 | * | |
723 | * @param string $sql | |
724 | * @param array $rows | |
725 | */ | |
030eb4f9 | 726 | public function buildRows($sql, &$rows) { |
727 | $dao = CRM_Core_DAO::executeQuery($sql); | |
728 | if (!is_array($rows)) { | |
729 | $rows = array(); | |
730 | } | |
731 | ||
732 | // use this method to modify $this->_columnHeaders | |
733 | $this->modifyColumnHeaders(); | |
ae6f7290 | 734 | $contriRows = array(); |
030eb4f9 | 735 | $unselectedSectionColumns = $this->unselectedSectionColumns(); |
736 | ||
737 | //CRM-16338 if both soft-credit and contribution are enabled then process the contribution's | |
738 | //total amount's average, count and sum separately and add it to the respective result list | |
739 | $softCredit = (!empty($this->_params['fields']['soft_amount']) && !empty($this->_params['fields']['total_amount'])) ? TRUE : FALSE; | |
740 | if ($softCredit) { | |
741 | $this->from('contribution'); | |
0b0d9821 | 742 | $this->customDataFrom(); |
030eb4f9 | 743 | $contriSQL = "{$this->_select} {$this->_from} {$this->_where} {$this->_groupBy} {$this->_having} {$this->_orderBy} {$this->_limit}"; |
744 | $contriDAO = CRM_Core_DAO::executeQuery($contriSQL); | |
745 | $contriFields = array( | |
746 | 'civicrm_contribution_total_amount_sum', | |
747 | 'civicrm_contribution_total_amount_avg', | |
c86d4e7c SL |
748 | 'civicrm_contribution_total_amount_count', |
749 | ); | |
030eb4f9 | 750 | $contriRows = array(); |
751 | while ($contriDAO->fetch()) { | |
752 | $contriRow = array(); | |
753 | foreach ($contriFields as $column) { | |
754 | $contriRow[$column] = $contriDAO->$column; | |
755 | } | |
756 | $contriRows[] = $contriRow; | |
757 | } | |
758 | } | |
759 | ||
760 | $count = 0; | |
761 | while ($dao->fetch()) { | |
762 | $row = array(); | |
763 | foreach ($this->_columnHeaders as $key => $value) { | |
764 | if ($softCredit && array_key_exists($key, $contriRows[$count])) { | |
765 | $row[$key] = $contriRows[$count][$key]; | |
766 | } | |
767 | elseif (property_exists($dao, $key)) { | |
768 | $row[$key] = $dao->$key; | |
769 | } | |
770 | } | |
771 | ||
772 | // section headers not selected for display need to be added to row | |
773 | foreach ($unselectedSectionColumns as $key => $values) { | |
774 | if (property_exists($dao, $key)) { | |
775 | $row[$key] = $dao->$key; | |
776 | } | |
777 | } | |
778 | ||
779 | $count++; | |
780 | $rows[] = $row; | |
781 | } | |
782 | ||
783 | } | |
784 | ||
74cf4551 | 785 | /** |
ae6f7290 | 786 | * Build chart. |
787 | * | |
788 | * @param array $rows | |
74cf4551 | 789 | */ |
00be9182 | 790 | public function buildChart(&$rows) { |
6a488035 | 791 | $graphRows = array(); |
6a488035 | 792 | |
a7488080 CW |
793 | if (!empty($this->_params['charts'])) { |
794 | if (!empty($this->_params['group_bys']['receive_date'])) { | |
0c98098b | 795 | |
0d8afee2 CW |
796 | $contrib = !empty($this->_params['fields']['total_amount']) ? TRUE : FALSE; |
797 | $softContrib = !empty($this->_params['fields']['soft_amount']) ? TRUE : FALSE; | |
0c98098b DS |
798 | |
799 | foreach ($rows as $key => $row) { | |
800 | if ($row['civicrm_contribution_receive_date_subtotal']) { | |
801 | $graphRows['receive_date'][] = $row['civicrm_contribution_receive_date_start']; | |
802 | $graphRows[$this->_interval][] = $row['civicrm_contribution_receive_date_interval']; | |
803 | if ($softContrib && $contrib) { | |
804 | // both contri & soft contri stats are present | |
805 | $graphRows['multiValue'][0][] = $row['civicrm_contribution_total_amount_sum']; | |
806 | $graphRows['multiValue'][1][] = $row['civicrm_contribution_soft_soft_amount_sum']; | |
9d72cede | 807 | } |
4c9b6178 | 808 | elseif ($softContrib) { |
0c98098b DS |
809 | // only soft contributions |
810 | $graphRows['multiValue'][0][] = $row['civicrm_contribution_soft_soft_amount_sum']; | |
9d72cede EM |
811 | } |
812 | else { | |
0c98098b DS |
813 | // only contributions |
814 | $graphRows['multiValue'][0][] = $row['civicrm_contribution_total_amount_sum']; | |
815 | } | |
816 | } | |
6a488035 | 817 | } |
6a488035 | 818 | |
0c98098b DS |
819 | if ($softContrib && $contrib) { |
820 | $graphRows['barKeys'][0] = ts('Contributions'); | |
821 | $graphRows['barKeys'][1] = ts('Soft Credits'); | |
822 | $graphRows['legend'] = ts('Contributions and Soft Credits'); | |
9d72cede | 823 | } |
4c9b6178 | 824 | elseif ($softContrib) { |
0c98098b DS |
825 | $graphRows['legend'] = ts('Soft Credits'); |
826 | } | |
6a488035 TO |
827 | |
828 | // build the chart. | |
9d72cede | 829 | $config = CRM_Core_Config::Singleton(); |
6a488035 | 830 | $graphRows['xname'] = $this->_interval; |
e60e0c01 | 831 | $graphRows['yname'] = ts('Amount (%1)', array(1 => $config->defaultCurrency)); |
6a488035 TO |
832 | CRM_Utils_OpenFlashChart::chart($graphRows, $this->_params['charts'], $this->_interval); |
833 | $this->assign('chartType', $this->_params['charts']); | |
834 | } | |
835 | } | |
836 | } | |
837 | ||
74cf4551 | 838 | /** |
ced9bfed EM |
839 | * Alter display of rows. |
840 | * | |
841 | * Iterate through the rows retrieved via SQL and make changes for display purposes, | |
842 | * such as rendering contacts as links. | |
843 | * | |
844 | * @param array $rows | |
845 | * Rows generated by SQL, with an array for each row. | |
74cf4551 | 846 | */ |
00be9182 | 847 | public function alterDisplay(&$rows) { |
6a488035 | 848 | $entryFound = FALSE; |
8a347ca5 | 849 | $contributionStatus = CRM_Contribute_PseudoConstant::contributionStatus(); |
6a488035 TO |
850 | |
851 | foreach ($rows as $rowNum => $row) { | |
852 | // make count columns point to detail report | |
9d72cede EM |
853 | if (!empty($this->_params['group_bys']['receive_date']) && |
854 | !empty($row['civicrm_contribution_receive_date_start']) && | |
855 | CRM_Utils_Array::value('civicrm_contribution_receive_date_start', $row) && | |
856 | !empty($row['civicrm_contribution_receive_date_subtotal']) | |
857 | ) { | |
6a488035 TO |
858 | |
859 | $dateStart = CRM_Utils_Date::customFormat($row['civicrm_contribution_receive_date_start'], '%Y%m%d'); | |
9d72cede EM |
860 | $endDate = new DateTime($dateStart); |
861 | $dateEnd = array(); | |
6a488035 TO |
862 | |
863 | list($dateEnd['Y'], $dateEnd['M'], $dateEnd['d']) = explode(':', $endDate->format('Y:m:d')); | |
864 | ||
865 | switch (strtolower($this->_params['group_bys_freq']['receive_date'])) { | |
866 | case 'month': | |
867 | $dateEnd = date("Ymd", mktime(0, 0, 0, $dateEnd['M'] + 1, | |
9d72cede EM |
868 | $dateEnd['d'] - 1, $dateEnd['Y'] |
869 | )); | |
6a488035 TO |
870 | break; |
871 | ||
872 | case 'year': | |
873 | $dateEnd = date("Ymd", mktime(0, 0, 0, $dateEnd['M'], | |
9d72cede EM |
874 | $dateEnd['d'] - 1, $dateEnd['Y'] + 1 |
875 | )); | |
6a488035 TO |
876 | break; |
877 | ||
35dd1d26 | 878 | case 'fiscalyear': |
879 | $dateEnd = date("Ymd", mktime(0, 0, 0, $dateEnd['M'], | |
880 | $dateEnd['d'] - 1, $dateEnd['Y'] + 1 | |
881 | )); | |
882 | break; | |
883 | ||
6a488035 TO |
884 | case 'yearweek': |
885 | $dateEnd = date("Ymd", mktime(0, 0, 0, $dateEnd['M'], | |
9d72cede EM |
886 | $dateEnd['d'] + 6, $dateEnd['Y'] |
887 | )); | |
6a488035 TO |
888 | break; |
889 | ||
890 | case 'quarter': | |
891 | $dateEnd = date("Ymd", mktime(0, 0, 0, $dateEnd['M'] + 3, | |
9d72cede EM |
892 | $dateEnd['d'] - 1, $dateEnd['Y'] |
893 | )); | |
6a488035 TO |
894 | break; |
895 | } | |
896 | $url = CRM_Report_Utils_Report::getNextUrl('contribute/detail', | |
9d72cede EM |
897 | "reset=1&force=1&receive_date_from={$dateStart}&receive_date_to={$dateEnd}", |
898 | $this->_absoluteUrl, | |
899 | $this->_id, | |
900 | $this->_drilldownReport | |
901 | ); | |
6a488035 TO |
902 | $rows[$rowNum]['civicrm_contribution_receive_date_start_link'] = $url; |
903 | $rows[$rowNum]['civicrm_contribution_receive_date_start_hover'] = ts('List all contribution(s) for this date unit.'); | |
904 | $entryFound = TRUE; | |
905 | } | |
906 | ||
907 | // make subtotals look nicer | |
908 | if (array_key_exists('civicrm_contribution_receive_date_subtotal', $row) && | |
909 | !$row['civicrm_contribution_receive_date_subtotal'] | |
910 | ) { | |
911 | $this->fixSubTotalDisplay($rows[$rowNum], $this->_statFields); | |
912 | $entryFound = TRUE; | |
913 | } | |
914 | ||
915 | // convert display name to links | |
916 | if (array_key_exists('civicrm_contact_sort_name', $row) && | |
917 | array_key_exists('civicrm_contact_id', $row) | |
918 | ) { | |
919 | $url = CRM_Report_Utils_Report::getNextUrl('contribute/detail', | |
920 | 'reset=1&force=1&id_op=eq&id_value=' . $row['civicrm_contact_id'], | |
921 | $this->_absoluteUrl, $this->_id, $this->_drilldownReport | |
922 | ); | |
923 | $rows[$rowNum]['civicrm_contact_sort_name_link'] = $url; | |
924 | $rows[$rowNum]['civicrm_contact_sort_name_hover'] = ts("Lists detailed contribution(s) for this record."); | |
925 | $entryFound = TRUE; | |
926 | } | |
927 | ||
8a347ca5 RO |
928 | // convert contribution status id to status name |
929 | if ($value = CRM_Utils_Array::value('civicrm_contribution_contribution_status_id', $row)) { | |
930 | $rows[$rowNum]['civicrm_contribution_contribution_status_id'] = $contributionStatus[$value]; | |
931 | $entryFound = TRUE; | |
932 | } | |
933 | ||
5e0343e8 | 934 | if (!empty($row['civicrm_financial_trxn_card_type_id'])) { |
935 | $rows[$rowNum]['civicrm_financial_trxn_card_type_id'] = $this->getLabels($row['civicrm_financial_trxn_card_type_id'], 'CRM_Financial_DAO_FinancialTrxn', 'card_type_id'); | |
fa5fb88c E |
936 | $entryFound = TRUE; |
937 | } | |
938 | ||
a7dbbc5d | 939 | // If using campaigns, convert campaign_id to campaign title |
6a488035 TO |
940 | if (array_key_exists('civicrm_contribution_campaign_id', $row)) { |
941 | if ($value = $row['civicrm_contribution_campaign_id']) { | |
bb3ab6f8 | 942 | $rows[$rowNum]['civicrm_contribution_campaign_id'] = $this->campaigns[$value]; |
6a488035 TO |
943 | } |
944 | $entryFound = TRUE; | |
945 | } | |
2ebf49a4 | 946 | |
8d2ca6b8 | 947 | // convert batch id to batch title |
c76af7c4 E |
948 | if (!empty($row['civicrm_batch_batch_id']) && !in_array('Subtotal', $rows[$rowNum])) { |
949 | $rows[$rowNum]['civicrm_batch_batch_id'] = $this->getLabels($row['civicrm_batch_batch_id'], 'CRM_Batch_BAO_EntityBatch', 'batch_id'); | |
8d2ca6b8 E |
950 | $entryFound = TRUE; |
951 | } | |
952 | ||
6a488035 | 953 | $entryFound = $this->alterDisplayAddressFields($row, $rows, $rowNum, 'contribute/detail', 'List all contribution(s) for this ') ? TRUE : $entryFound; |
7a4192f7 | 954 | $entryFound = $this->alterDisplayContactFields($row, $rows, $rowNum, 'contribute/detail', 'List all contribution(s) for this ') ? TRUE : $entryFound; |
6a488035 | 955 | |
6a488035 TO |
956 | // skip looking further in rows, if first row itself doesn't |
957 | // have the column we need | |
958 | if (!$entryFound) { | |
959 | break; | |
960 | } | |
961 | } | |
962 | } | |
96025800 | 963 | |
8446bae6 | 964 | /** |
965 | * Calculate mode. | |
966 | * | |
967 | * Note this is a slow query. Alternative is extended reports. | |
968 | * | |
969 | * @param string $sql | |
970 | * @return array|null | |
971 | */ | |
972 | protected function calculateMode($sql) { | |
973 | $mode = []; | |
974 | $modeDAO = CRM_Core_DAO::executeQuery($sql); | |
975 | while ($modeDAO->fetch()) { | |
976 | if ($modeDAO->civicrm_contribution_total_amount_count > 1) { | |
977 | $mode[] = CRM_Utils_Money::format($modeDAO->amount, $modeDAO->currency); | |
978 | } | |
979 | else { | |
980 | $mode[] = 'N/A'; | |
981 | } | |
982 | } | |
983 | return $mode; | |
984 | } | |
985 | ||
986 | /** | |
987 | * Calculate mode. | |
988 | * | |
989 | * Note this is a slow query. Alternative is extended reports. | |
990 | * | |
991 | * @return array|null | |
992 | */ | |
993 | protected function calculateMedian() { | |
994 | $sql = "{$this->_from} {$this->_where}"; | |
995 | $currencies = CRM_Core_OptionGroup::values('currencies_enabled'); | |
996 | $median = []; | |
997 | foreach ($currencies as $currency => $val) { | |
998 | $midValue = 0; | |
999 | $where = "AND {$this->_aliases['civicrm_contribution']}.currency = '{$currency}'"; | |
1000 | $rowCount = CRM_Core_DAO::singleValueQuery("SELECT count(*) as count {$sql} {$where}"); | |
1001 | ||
1002 | $even = FALSE; | |
1003 | $offset = 1; | |
1004 | $medianRow = floor($rowCount / 2); | |
1005 | if ($rowCount % 2 == 0 && !empty($medianRow)) { | |
1006 | $even = TRUE; | |
1007 | $offset++; | |
1008 | $medianRow--; | |
1009 | } | |
1010 | ||
1011 | $medianValue = "SELECT {$this->_aliases['civicrm_contribution']}.total_amount as median | |
1012 | {$sql} {$where} | |
1013 | ORDER BY median LIMIT {$medianRow},{$offset}"; | |
1014 | $medianValDAO = CRM_Core_DAO::executeQuery($medianValue); | |
1015 | while ($medianValDAO->fetch()) { | |
1016 | if ($even) { | |
1017 | $midValue = $midValue + $medianValDAO->median; | |
1018 | } | |
1019 | else { | |
1020 | $median[] = CRM_Utils_Money::format($medianValDAO->median, $currency); | |
1021 | } | |
1022 | } | |
1023 | if ($even) { | |
1024 | $midValue = $midValue / 2; | |
1025 | $median[] = CRM_Utils_Money::format($midValue, $currency); | |
1026 | } | |
1027 | } | |
1028 | return $median; | |
1029 | } | |
1030 | ||
6a488035 | 1031 | } |