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_Repeat extends CRM_Report_Form { | |
18 | protected $_amountClauseWithAND = NULL; | |
19 | ||
70bea8e2 | 20 | protected $_customGroupExtends = array( |
21 | 'Contact', | |
22 | 'Individual', | |
23 | ); | |
24 | ||
6a488035 TO |
25 | public $_drilldownReport = array('contribute/detail' => 'Link to Detail Report'); |
26 | ||
74cf4551 | 27 | /** |
26b55a9e | 28 | * Temp table for first time frame. |
29 | * | |
30 | * @var int | |
31 | */ | |
32 | protected $tempTableRepeat1 = NULL; | |
33 | ||
34 | /** | |
35 | * Temp table for second time frame. | |
36 | * | |
37 | * @var int | |
38 | */ | |
39 | protected $tempTableRepeat2 = NULL; | |
40 | ||
41 | /** | |
42 | * The table the report is being grouped by. | |
43 | * | |
44 | * @var string | |
45 | */ | |
46 | protected $groupByTable; | |
47 | ||
48 | /** | |
49 | * The field the report is being grouped by. | |
50 | * | |
51 | * @var string | |
52 | */ | |
53 | protected $groupByFieldName; | |
54 | ||
55 | /** | |
56 | * The alias of the table the report is being grouped by. | |
57 | * | |
58 | * @var string | |
59 | */ | |
60 | protected $groupByTableAlias; | |
61 | ||
62 | /** | |
63 | * The column in the contribution table that joins to the temp tables. | |
64 | * | |
c86d4e7c | 65 | * @var string |
26b55a9e | 66 | */ |
67 | protected $contributionJoinTableColumn; | |
68 | ||
1728e9a0 | 69 | /** |
70 | * This report has been optimised for group filtering. | |
71 | * | |
1728e9a0 | 72 | * @var bool |
0e480632 | 73 | * @see https://issues.civicrm.org/jira/browse/CRM-19170 |
1728e9a0 | 74 | */ |
75 | protected $groupFilterNotOptimised = FALSE; | |
76 | ||
26b55a9e | 77 | /** |
78 | * Class constructor. | |
74cf4551 | 79 | */ |
00be9182 | 80 | public function __construct() { |
6a488035 | 81 | $this->_columns = array( |
9d72cede | 82 | 'civicrm_contact' => array( |
6a488035 TO |
83 | 'dao' => 'CRM_Contact_DAO_Contact', |
84 | 'grouping' => 'contact-fields', | |
8c0f07c0 | 85 | 'fields' => array_merge( |
86 | $this->getBasicContactFields(), | |
87 | array( | |
88 | 'sort_name' => array( | |
89 | 'title' => ts('Contact Name'), | |
90 | 'no_repeat' => TRUE, | |
91 | 'default' => TRUE, | |
92 | ), | |
93 | ) | |
6a488035 | 94 | ), |
8c0f07c0 | 95 | 'filters' => array_merge( |
96 | $this->getBasicContactFilters(array('deceased' => NULL)), | |
97 | array( | |
98 | 'percentage_change' => array( | |
99 | 'title' => ts('Percentage Change'), | |
100 | 'type' => CRM_Utils_Type::T_INT, | |
101 | 'operatorType' => CRM_Report_Form::OP_INT, | |
102 | 'name' => 'percentage_change', | |
103 | 'dbAlias' => '( ( contribution_civireport2.total_amount_sum - contribution_civireport1.total_amount_sum ) * 100 / contribution_civireport1.total_amount_sum )', | |
104 | ), | |
105 | ) | |
6a488035 | 106 | ), |
9d72cede EM |
107 | 'group_bys' => array( |
108 | 'id' => array( | |
6a488035 TO |
109 | 'title' => ts('Contact'), |
110 | 'default' => TRUE, | |
111 | ), | |
112 | ), | |
70bea8e2 | 113 | 'order_bys' => array( |
114 | 'sort_name' => array( | |
115 | 'title' => ts('Last Name, First Name'), | |
116 | 'default' => '1', | |
117 | 'default_weight' => '0', | |
118 | 'default_order' => 'ASC', | |
119 | ), | |
120 | 'first_name' => array( | |
121 | 'title' => ts('First Name'), | |
122 | ), | |
123 | 'gender_id' => array( | |
124 | 'name' => 'gender_id', | |
125 | 'title' => ts('Gender'), | |
126 | ), | |
127 | 'birth_date' => array( | |
128 | 'name' => 'birth_date', | |
129 | 'title' => ts('Birth Date'), | |
130 | ), | |
131 | 'contact_type' => array( | |
132 | 'title' => ts('Contact Type'), | |
133 | ), | |
134 | 'contact_sub_type' => array( | |
135 | 'title' => ts('Contact Subtype'), | |
136 | ), | |
137 | ), | |
6a488035 | 138 | ), |
26b55a9e | 139 | 'civicrm_email' => array( |
140 | 'dao' => 'CRM_Core_DAO_Email', | |
141 | 'fields' => array( | |
142 | 'email' => array( | |
143 | 'title' => ts('Email'), | |
144 | 'no_repeat' => TRUE, | |
145 | ), | |
146 | ), | |
101f8739 | 147 | 'filters' => [ |
148 | 'on_hold' => [ | |
149 | 'title' => ts('On Hold'), | |
150 | 'type' => CRM_Utils_Type::T_INT, | |
151 | 'operatorType' => CRM_Report_Form::OP_MULTISELECT, | |
152 | 'options' => ['' => ts('Any')] + CRM_Core_PseudoConstant::emailOnHoldOptions(), | |
153 | ], | |
154 | ], | |
26b55a9e | 155 | 'grouping' => 'contact-fields', |
156 | ), | |
9d72cede | 157 | 'civicrm_phone' => array( |
6a488035 | 158 | 'dao' => 'CRM_Core_DAO_Phone', |
9d72cede EM |
159 | 'fields' => array( |
160 | 'phone' => array( | |
6a488035 TO |
161 | 'title' => ts('Phone'), |
162 | 'no_repeat' => TRUE, | |
163 | ), | |
164 | ), | |
165 | 'grouping' => 'contact-fields', | |
166 | ), | |
9d72cede | 167 | 'civicrm_financial_type' => array( |
b914f4e8 PN |
168 | 'dao' => 'CRM_Financial_DAO_FinancialType', |
169 | 'fields' => array('financial_type' => array('title' => ts('Financial Type'))), | |
9d72cede EM |
170 | 'grouping' => 'contri-fields', |
171 | 'group_bys' => array( | |
172 | 'financial_type' => array( | |
173 | 'name' => 'id', | |
174 | 'title' => ts('Financial Type'), | |
175 | ), | |
6a488035 TO |
176 | ), |
177 | ), | |
9d72cede | 178 | 'civicrm_contribution' => array( |
6a488035 | 179 | 'dao' => 'CRM_Contribute_DAO_Contribution', |
9d72cede | 180 | 'fields' => array( |
6a488035 | 181 | 'contribution_source' => NULL, |
9d72cede | 182 | 'total_amount1' => array( |
6a488035 TO |
183 | 'name' => 'total_amount', |
184 | 'alias' => 'contribution1', | |
185 | 'title' => ts('Range One Stat'), | |
9698bf7b | 186 | 'type' => CRM_Utils_Type::T_STRING, |
6a488035 TO |
187 | 'default' => TRUE, |
188 | 'required' => TRUE, | |
5396af74 | 189 | 'clause' => 'contribution_civireport1.total_amount_count as contribution1_total_amount_count, contribution_civireport1.total_amount_sum as contribution1_total_amount_sum', |
6a488035 | 190 | ), |
9d72cede | 191 | 'total_amount2' => array( |
6a488035 TO |
192 | 'name' => 'total_amount', |
193 | 'alias' => 'contribution2', | |
194 | 'title' => ts('Range Two Stat'), | |
9698bf7b | 195 | 'type' => CRM_Utils_Type::T_STRING, |
6a488035 TO |
196 | 'default' => TRUE, |
197 | 'required' => TRUE, | |
5396af74 | 198 | 'clause' => 'contribution_civireport2.total_amount_count as contribution2_total_amount_count, contribution_civireport2.total_amount_sum as contribution2_total_amount_sum', |
6a488035 TO |
199 | ), |
200 | ), | |
201 | 'grouping' => 'contri-fields', | |
9d72cede EM |
202 | 'filters' => array( |
203 | 'receive_date1' => array( | |
6a488035 TO |
204 | 'title' => ts('Initial Date Range'), |
205 | 'default' => 'previous.year', | |
6a488035 TO |
206 | 'operatorType' => CRM_Report_Form::OP_DATE, |
207 | 'name' => 'receive_date', | |
208 | ), | |
9d72cede | 209 | 'receive_date2' => array( |
6a488035 TO |
210 | 'title' => ts('Second Date Range'), |
211 | 'default' => 'this.year', | |
6a488035 TO |
212 | 'operatorType' => CRM_Report_Form::OP_DATE, |
213 | 'name' => 'receive_date', | |
214 | ), | |
9d72cede | 215 | 'total_amount1' => array( |
6a488035 TO |
216 | 'title' => ts('Range One Amount'), |
217 | 'type' => CRM_Utils_Type::T_INT, | |
218 | 'operatorType' => CRM_Report_Form::OP_INT, | |
219 | 'name' => 'total_amount', | |
220 | ), | |
9d72cede | 221 | 'total_amount2' => array( |
6a488035 TO |
222 | 'title' => ts('Range Two Amount'), |
223 | 'type' => CRM_Utils_Type::T_INT, | |
224 | 'operatorType' => CRM_Report_Form::OP_INT, | |
225 | 'name' => 'total_amount', | |
226 | ), | |
9d72cede | 227 | 'financial_type_id' => array( |
b914f4e8 | 228 | 'title' => ts('Financial Type'), |
8ee006e7 | 229 | 'type' => CRM_Utils_Type::T_INT, |
6a488035 | 230 | 'operatorType' => CRM_Report_Form::OP_MULTISELECT, |
51d1f926 | 231 | 'options' => CRM_Contribute_BAO_Contribution::buildOptions('financial_type_id', 'search'), |
6a488035 | 232 | ), |
9d72cede | 233 | 'contribution_status_id' => array( |
b914f4e8 | 234 | 'title' => ts('Contribution Status'), |
6a488035 | 235 | 'operatorType' => CRM_Report_Form::OP_MULTISELECT, |
c0aaecf9 | 236 | 'options' => CRM_Contribute_BAO_Contribution::buildOptions('contribution_status_id', 'search'), |
6a488035 TO |
237 | 'default' => array('1'), |
238 | ), | |
239 | ), | |
240 | 'group_bys' => array('contribution_source' => NULL), | |
241 | ), | |
8c0f07c0 | 242 | ) + $this->addAddressFields(TRUE, FALSE, TRUE, array('country_id' => FALSE)); |
6a488035 | 243 | |
16e2e80c | 244 | $this->_groupFilter = TRUE; |
6a488035 TO |
245 | $this->_tagFilter = TRUE; |
246 | ||
247 | parent::__construct(); | |
248 | } | |
249 | ||
74cf4551 | 250 | /** |
26b55a9e | 251 | * Override parent select for reasons someone will someday make sense of & document. |
74cf4551 | 252 | */ |
00be9182 | 253 | public function select() { |
affcc9d2 | 254 | $select = []; |
6a488035 TO |
255 | $append = NULL; |
256 | // since contact fields not related to financial type | |
b914f4e8 | 257 | if (array_key_exists('financial_type', $this->_params['group_bys']) || |
6a488035 TO |
258 | array_key_exists('contribution_source', $this->_params['group_bys']) |
259 | ) { | |
260 | unset($this->_columns['civicrm_contact']['fields']['id']); | |
261 | } | |
262 | ||
263 | foreach ($this->_columns as $tableName => $table) { | |
264 | if (array_key_exists('fields', $table)) { | |
265 | foreach ($table['fields'] as $fieldName => $field) { | |
9d72cede EM |
266 | if (!empty($field['required']) || |
267 | !empty($this->_params['fields'][$fieldName]) | |
268 | ) { | |
6a488035 TO |
269 | if (isset($field['clause'])) { |
270 | $select[] = $field['clause']; | |
271 | ||
272 | // FIXME: dirty hack for setting columnHeaders | |
9c1bc317 | 273 | $this->_columnHeaders["{$field['alias']}_{$field['name']}_sum"]['type'] = $field['type'] ?? NULL; |
6a488035 | 274 | $this->_columnHeaders["{$field['alias']}_{$field['name']}_sum"]['title'] = $field['title']; |
9c1bc317 | 275 | $this->_columnHeaders["{$field['alias']}_{$field['name']}_count"]['type'] = $field['type'] ?? NULL; |
6a488035 TO |
276 | $this->_columnHeaders["{$field['alias']}_{$field['name']}_count"]['title'] = $field['title']; |
277 | continue; | |
278 | } | |
279 | ||
280 | // only include statistics columns if set | |
281 | $select[] = "{$field['dbAlias']} as {$field['alias']}_{$field['name']}"; | |
9c1bc317 CW |
282 | $this->_columnHeaders["{$field['alias']}_{$field['name']}"]['type'] = $field['type'] ?? NULL; |
283 | $this->_columnHeaders["{$field['alias']}_{$field['name']}"]['title'] = $field['title'] ?? NULL; | |
a7488080 | 284 | if (!empty($field['no_display'])) { |
6a488035 TO |
285 | $this->_columnHeaders["{$field['alias']}_{$field['name']}"]['no_display'] = TRUE; |
286 | } | |
287 | } | |
288 | } | |
289 | } | |
290 | } | |
d1641c51 | 291 | $this->_selectClauses = $select; |
6a488035 TO |
292 | $this->_select = "SELECT " . implode(', ', $select) . " "; |
293 | } | |
294 | ||
74cf4551 | 295 | /** |
26b55a9e | 296 | * Inspect the group by params to determine group by information. |
74cf4551 | 297 | */ |
26b55a9e | 298 | public function setGroupByInformation() { |
9d72cede EM |
299 | if (!empty($this->_params['group_bys']) && |
300 | is_array($this->_params['group_bys']) | |
301 | ) { | |
6a488035 TO |
302 | foreach ($this->_columns as $tableName => $table) { |
303 | if (array_key_exists('group_bys', $table)) { | |
304 | foreach ($table['group_bys'] as $fieldName => $field) { | |
a7488080 | 305 | if (!empty($this->_params['group_bys'][$fieldName])) { |
26b55a9e | 306 | $this->groupByTable = $tableName; |
307 | $this->groupByTableAlias = $field['alias']; | |
308 | $this->groupByFieldName = $field['name']; | |
309 | if ($this->groupByTable == 'civicrm_contact') { | |
310 | $this->contributionJoinTableColumn = "contact_id"; | |
311 | } | |
312 | elseif ($this->groupByTable == 'civicrm_contribution_type') { | |
313 | $this->contributionJoinTableColumn = "contribution_type_id"; | |
6a488035 | 314 | } |
26b55a9e | 315 | elseif ($this->groupByTable == 'civicrm_contribution') { |
316 | $this->contributionJoinTableColumn = $this->groupByFieldName; | |
6a488035 | 317 | } |
26b55a9e | 318 | elseif ($this->groupByTable == 'civicrm_address') { |
319 | $this->contributionJoinTableColumn = "contact_id"; | |
320 | } | |
321 | elseif ($this->groupByTable == 'civicrm_financial_type') { | |
322 | $this->contributionJoinTableColumn = 'financial_type_id'; | |
323 | } | |
324 | return; | |
6a488035 TO |
325 | } |
326 | } | |
327 | } | |
328 | } | |
329 | ||
6a488035 TO |
330 | } |
331 | } | |
332 | ||
00be9182 | 333 | public function from() { |
26b55a9e | 334 | $this->buildTempTables(); |
335 | $fromCol = $this->groupByFieldName; | |
6a488035 | 336 | |
26b55a9e | 337 | $from = "$this->groupByTable $this->groupByTableAlias"; |
338 | ||
339 | if ($this->groupByTable == 'civicrm_contact') { | |
6a488035 TO |
340 | $from .= " |
341 | LEFT JOIN civicrm_address {$this->_aliases['civicrm_address']} ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_address']}.contact_id | |
342 | LEFT JOIN civicrm_email {$this->_aliases['civicrm_email']} | |
343 | ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_email']}.contact_id AND {$this->_aliases['civicrm_email']}.is_primary = 1 | |
344 | LEFT JOIN civicrm_phone {$this->_aliases['civicrm_phone']} | |
345 | ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_phone']}.contact_id AND {$this->_aliases['civicrm_phone']}.is_primary = 1"; | |
346 | ||
347 | } | |
26b55a9e | 348 | elseif ($this->groupByTable == 'civicrm_address') { |
6a488035 TO |
349 | $from .= " |
350 | INNER JOIN civicrm_contact {$this->_aliases['civicrm_contact']} ON {$this->_aliases['civicrm_address']}.contact_id = {$this->_aliases['civicrm_contact']}.id"; | |
26b55a9e | 351 | $this->groupByTableAlias = $this->_aliases['civicrm_contact']; |
9d72cede | 352 | $fromCol = "id"; |
6a488035 TO |
353 | } |
354 | ||
355 | $this->_from = " | |
356 | FROM $from | |
26b55a9e | 357 | LEFT JOIN $this->tempTableRepeat1 {$this->_aliases['civicrm_contribution']}1 |
358 | ON {$this->groupByTableAlias}.$fromCol = {$this->_aliases['civicrm_contribution']}1 | |
359 | .{$this->contributionJoinTableColumn} | |
360 | LEFT JOIN $this->tempTableRepeat2 {$this->_aliases['civicrm_contribution']}2 | |
361 | ON {$this->groupByTableAlias}.$fromCol = {$this->_aliases['civicrm_contribution']}2.{$this->contributionJoinTableColumn}"; | |
5e3dec81 JP |
362 | |
363 | //Join temp table if report is filtered by group. This is specific to 'notin' operator and covered in unit test(ref dev/core#212) | |
364 | if (!empty($this->_params['gid_op']) && $this->_params['gid_op'] == 'notin') { | |
365 | $this->joinGroupTempTable('civicrm_contact', 'id', $this->_aliases['civicrm_contact']); | |
366 | } | |
6a488035 | 367 | } |
26b55a9e | 368 | |
30f8901b PN |
369 | /** |
370 | * @param string $replaceAliasWith | |
371 | * | |
372 | * @return mixed|string | |
373 | */ | |
374 | public function fromContribution($replaceAliasWith = 'contribution1') { | |
97b4dc6b | 375 | $this->setFromBase('civicrm_contribution', 'contact_id', $replaceAliasWith); |
376 | ||
30f8901b PN |
377 | $temp = $this->_aliases['civicrm_contribution']; |
378 | $this->_aliases['civicrm_contribution'] = $replaceAliasWith; | |
97b4dc6b | 379 | $from = $this->_from; |
b55aa3de | 380 | $this->_aliases['civicrm_contribution'] = $temp; |
30f8901b PN |
381 | $this->_where = ''; |
382 | return $from; | |
dec56960 | 383 | } |
26b55a9e | 384 | |
74cf4551 EM |
385 | /** |
386 | * @param string $replaceAliasWith | |
387 | * | |
388 | * @return mixed|string | |
389 | */ | |
00be9182 | 390 | public function whereContribution($replaceAliasWith = 'contribution1') { |
9f108b4d JJ |
391 | $clauses = array( |
392 | "is_test" => "{$this->_aliases['civicrm_contribution']}.is_test = 0", | |
393 | "is_template" => "{$this->_aliases['civicrm_contribution']}.is_template = 0", | |
394 | ); | |
6a488035 TO |
395 | |
396 | foreach ($this->_columns['civicrm_contribution']['filters'] as $fieldName => $field) { | |
397 | $clause = NULL; | |
398 | if (CRM_Utils_Array::value('type', $field) & CRM_Utils_Type::T_DATE) { | |
9c1bc317 CW |
399 | $relative = $this->_params["{$fieldName}_relative"] ?? NULL; |
400 | $from = $this->_params["{$fieldName}_from"] ?? NULL; | |
401 | $to = $this->_params["{$fieldName}_to"] ?? NULL; | |
6a488035 TO |
402 | |
403 | $clause = $this->dateClause($field['dbAlias'], $relative, $from, $to, $field['type']); | |
404 | } | |
405 | else { | |
9c1bc317 | 406 | $op = $this->_params["{$fieldName}_op"] ?? NULL; |
6a488035 TO |
407 | if ($op) { |
408 | $clause = $this->whereClause($field, | |
409 | $op, | |
410 | CRM_Utils_Array::value("{$fieldName}_value", $this->_params), | |
411 | CRM_Utils_Array::value("{$fieldName}_min", $this->_params), | |
412 | CRM_Utils_Array::value("{$fieldName}_max", $this->_params) | |
413 | ); | |
414 | } | |
415 | } | |
416 | if (!empty($clause)) { | |
417 | $clauses[$fieldName] = $clause; | |
418 | } | |
419 | } | |
420 | ||
421 | if (!$this->_amountClauseWithAND) { | |
affcc9d2 | 422 | $amountClauseWithAND = []; |
a7488080 | 423 | if (!empty($clauses['total_amount1'])) { |
6a488035 TO |
424 | $amountClauseWithAND[] = str_replace("{$this->_aliases['civicrm_contribution']}.total_amount", |
425 | "{$this->_aliases['civicrm_contribution']}1.total_amount_sum", $clauses['total_amount1']); | |
426 | } | |
a7488080 | 427 | if (!empty($clauses['total_amount2'])) { |
6a488035 TO |
428 | $amountClauseWithAND[] = str_replace("{$this->_aliases['civicrm_contribution']}.total_amount", |
429 | "{$this->_aliases['civicrm_contribution']}2.total_amount_sum", $clauses['total_amount2']); | |
430 | } | |
431 | $this->_amountClauseWithAND = !empty($amountClauseWithAND) ? implode(' AND ', $amountClauseWithAND) : NULL; | |
432 | } | |
433 | ||
434 | if ($replaceAliasWith == 'contribution1') { | |
435 | unset($clauses['receive_date2'], $clauses['total_amount2']); | |
436 | } | |
437 | else { | |
438 | unset($clauses['receive_date1'], $clauses['total_amount1']); | |
439 | } | |
440 | ||
84178120 | 441 | $whereClause = !empty($clauses) ? "WHERE " . implode(' AND ', $clauses) : ''; |
6a488035 TO |
442 | |
443 | if ($replaceAliasWith) { | |
444 | $whereClause = str_replace($this->_aliases['civicrm_contribution'], $replaceAliasWith, $whereClause); | |
445 | } | |
446 | ||
447 | return $whereClause; | |
448 | } | |
449 | ||
00be9182 | 450 | public function where() { |
6a488035 | 451 | if (!$this->_amountClauseWithAND) { |
5396af74 | 452 | $this->_amountClauseWithAND |
453 | = "!({$this->_aliases['civicrm_contribution']}1.total_amount_count IS NULL AND {$this->_aliases['civicrm_contribution']}2.total_amount_count IS NULL)"; | |
6a488035 TO |
454 | } |
455 | $clauses = array("atleast_one_amount" => $this->_amountClauseWithAND); | |
456 | ||
457 | foreach ($this->_columns as $tableName => $table) { | |
9d72cede EM |
458 | if (array_key_exists('filters', $table) && |
459 | $tableName != 'civicrm_contribution' | |
460 | ) { | |
6a488035 TO |
461 | foreach ($table['filters'] as $fieldName => $field) { |
462 | $clause = NULL; | |
9c1bc317 | 463 | $op = $this->_params["{$fieldName}_op"] ?? NULL; |
6a488035 TO |
464 | if ($op) { |
465 | $clause = $this->whereClause($field, | |
466 | $op, | |
467 | CRM_Utils_Array::value("{$fieldName}_value", $this->_params), | |
468 | CRM_Utils_Array::value("{$fieldName}_min", $this->_params), | |
469 | CRM_Utils_Array::value("{$fieldName}_max", $this->_params) | |
470 | ); | |
471 | } | |
472 | if (!empty($clause)) { | |
473 | $clauses[$fieldName] = $clause; | |
474 | } | |
475 | } | |
476 | } | |
477 | } | |
478 | ||
84178120 | 479 | $this->_where = !empty($clauses) ? "WHERE " . implode(' AND ', $clauses) : ''; |
6a488035 TO |
480 | } |
481 | ||
74cf4551 | 482 | /** |
bef4d7ee | 483 | * @param array $fields |
484 | * @param array $files | |
485 | * @param CRM_Core_Form $self | |
74cf4551 EM |
486 | * |
487 | * @return array | |
488 | */ | |
00be9182 | 489 | public static function formRule($fields, $files, $self) { |
6a488035 | 490 | |
affcc9d2 | 491 | $errors = $checkDate = $errorCount = []; |
6a488035 TO |
492 | |
493 | $rules = array( | |
494 | 'id' => array( | |
495 | 'sort_name', | |
8c0f07c0 | 496 | 'exposed_id', |
6a488035 TO |
497 | 'display_name', |
498 | 'addressee_display', | |
30f85891 RN |
499 | 'contact_type', |
500 | 'contact_sub_type', | |
6a488035 TO |
501 | 'email', |
502 | 'phone', | |
503 | 'state_province_id', | |
504 | 'country_id', | |
505 | 'city', | |
8c0f07c0 | 506 | 'county_id', |
6a488035 TO |
507 | 'street_address', |
508 | 'supplemental_address_1', | |
8c0f07c0 | 509 | 'supplemental_address_2', |
510 | 'supplemental_address_3', | |
511 | 'street_number', | |
512 | 'street_name', | |
513 | 'street_unit', | |
6a488035 | 514 | 'postal_code', |
8c0f07c0 | 515 | 'postal_code_suffix', |
516 | 'prefix_id', | |
517 | 'first_name', | |
518 | 'nick_name', | |
519 | 'middle_name', | |
520 | 'last_name', | |
521 | 'suffix_id', | |
522 | 'postal_greeting_display', | |
523 | 'email_greeting_display', | |
524 | 'addressee_display', | |
525 | 'gender_id', | |
526 | 'birth_date', | |
527 | 'age', | |
528 | 'job_title', | |
529 | 'organization_name', | |
530 | 'external_identifier', | |
531 | 'do_not_email', | |
532 | 'do_not_phone', | |
533 | 'do_not_mail', | |
534 | 'do_not_sms', | |
535 | 'is_opt_out', | |
536 | 'is_deceased', | |
537 | 'preferred_language', | |
6a488035 TO |
538 | ), |
539 | 'country_id' => array('country_id'), | |
540 | 'state_province_id' => array('country_id', 'state_province_id'), | |
541 | 'contribution_source' => array('contribution_source'), | |
b914f4e8 | 542 | 'financial_type' => array('financial_type'), |
6a488035 TO |
543 | ); |
544 | ||
545 | $idMapping = array( | |
546 | 'id' => ts('Contact'), | |
22a17535 | 547 | 'exposed_id' => ts('Contact'), |
6a488035 TO |
548 | 'country_id' => ts('Country'), |
549 | 'state_province_id' => ts('State/Province'), | |
550 | 'contribution_source' => ts('Contribution Source'), | |
b914f4e8 | 551 | 'financial_type' => ts('Financial Type'), |
6a488035 TO |
552 | 'sort_name' => ts('Contact Name'), |
553 | 'email' => ts('Email'), | |
554 | 'phone' => ts('Phone'), | |
555 | ); | |
556 | ||
557 | if (empty($fields['group_bys'])) { | |
558 | $errors['fields'] = ts('Please select at least one Group by field.'); | |
559 | } | |
560 | elseif ((array_key_exists('contribution_source', $fields['group_bys']) || | |
9d72cede EM |
561 | array_key_exists('contribution_type', $fields['group_bys']) |
562 | ) && | |
6a488035 TO |
563 | (count($fields['group_bys']) > 1) |
564 | ) { | |
565 | $errors['fields'] = ts('You can not use other Group by with Financial type or Contribution source.'); | |
566 | } | |
567 | else { | |
568 | foreach ($fields['fields'] as $fld_id => $value) { | |
5210bcce | 569 | if (!($fld_id == 'total_amount1') && !($fld_id == 'total_amount2') && !(substr($fld_id, 0, 7) === "custom_")) { |
6a488035 | 570 | $found = FALSE; |
affcc9d2 | 571 | $invlidGroups = []; |
6a488035 TO |
572 | foreach ($fields['group_bys'] as $grp_id => $val) { |
573 | $validFields = $rules[$grp_id]; | |
574 | if (in_array($fld_id, $validFields)) { | |
575 | $found = TRUE; | |
576 | } | |
577 | else { | |
578 | $invlidGroups[] = $idMapping[$grp_id]; | |
579 | } | |
580 | } | |
581 | if (!$found) { | |
582 | $erorrGrps = implode(',', $invlidGroups); | |
9d72cede | 583 | $tempErrors[] = ts("Do not select field %1 with Group by %2.", array( |
353ffa53 TO |
584 | 1 => $idMapping[$fld_id], |
585 | 2 => $erorrGrps, | |
586 | )); | |
6a488035 TO |
587 | } |
588 | } | |
589 | } | |
590 | if (!empty($tempErrors)) { | |
d3e86119 | 591 | $errors['fields'] = implode("<br/>", $tempErrors); |
6a488035 TO |
592 | } |
593 | } | |
594 | ||
8cc574cf | 595 | if (!empty($fields['gid_value']) && !empty($fields['group_bys'])) { |
6a488035 TO |
596 | if (!array_key_exists('id', $fields['group_bys'])) { |
597 | $errors['gid_value'] = ts("Filter with Group only allow with group by Contact"); | |
598 | } | |
599 | } | |
600 | ||
601 | if ($fields['receive_date1_relative'] == '0') { | |
602 | $checkDate['receive_date1']['receive_date1_from'] = $fields['receive_date1_from']; | |
603 | $checkDate['receive_date1']['receive_date1_to'] = $fields['receive_date1_to']; | |
604 | } | |
605 | ||
606 | if ($fields['receive_date2_relative'] == '0') { | |
607 | $checkDate['receive_date2']['receive_date2_from'] = $fields['receive_date2_from']; | |
608 | $checkDate['receive_date2']['receive_date2_to'] = $fields['receive_date2_to']; | |
609 | } | |
610 | ||
611 | foreach ($checkDate as $date_range => $range_data) { | |
612 | foreach ($range_data as $key => $value) { | |
186e835d | 613 | if (!CRM_Utils_System::isNull($value)) { |
6a488035 TO |
614 | $errorCount[$date_range][$key]['valid'] = 'true'; |
615 | $errorCount[$date_range][$key]['is_empty'] = 'false'; | |
616 | } | |
617 | else { | |
618 | $errorCount[$date_range][$key]['valid'] = 'false'; | |
619 | $errorCount[$date_range][$key]['is_empty'] = 'true'; | |
620 | if (is_array($value)) { | |
621 | foreach ($value as $v) { | |
622 | if ($v) { | |
623 | $errorCount[$date_range][$key]['is_empty'] = 'false'; | |
624 | } | |
625 | } | |
626 | } | |
627 | elseif (!isset($value)) { | |
628 | $errorCount[$date_range][$key]['is_empty'] = 'false'; | |
629 | } | |
630 | } | |
631 | } | |
632 | } | |
633 | ||
634 | $errorText = ts("Select valid date range"); | |
635 | foreach ($errorCount as $date_range => $error_data) { | |
636 | ||
637 | if (($error_data[$date_range . '_from']['valid'] == 'false') && | |
638 | ($error_data[$date_range . '_to']['valid'] == 'false') | |
639 | ) { | |
640 | ||
641 | if (($error_data[$date_range . '_from']['is_empty'] == 'true') && | |
642 | ($error_data[$date_range . '_to']['is_empty'] == 'true') | |
643 | ) { | |
644 | $errors[$date_range . '_relative'] = $errorText; | |
645 | } | |
646 | ||
647 | if ($error_data[$date_range . '_from']['is_empty'] == 'false') { | |
648 | $errors[$date_range . '_from'] = $errorText; | |
649 | } | |
650 | ||
651 | if ($error_data[$date_range . '_to']['is_empty'] == 'false') { | |
652 | $errors[$date_range . '_to'] = $errorText; | |
653 | } | |
654 | } | |
655 | elseif (($error_data[$date_range . '_from']['valid'] == 'true') && | |
656 | ($error_data[$date_range . '_to']['valid'] == 'false') | |
657 | ) { | |
658 | if ($error_data[$date_range . '_to']['is_empty'] == 'false') { | |
659 | $errors[$date_range . '_to'] = $errorText; | |
660 | } | |
661 | } | |
662 | elseif (($error_data[$date_range . '_from']['valid'] == 'false') && | |
663 | ($error_data[$date_range . '_to']['valid'] == 'true') | |
664 | ) { | |
665 | if ($error_data[$date_range . '_from']['is_empty'] == 'false') { | |
666 | $errors[$date_range . '_from'] = $errorText; | |
667 | } | |
668 | } | |
669 | } | |
670 | ||
671 | return $errors; | |
672 | } | |
673 | ||
74cf4551 | 674 | /** |
71d8f758 | 675 | * @param array $rows |
74cf4551 EM |
676 | * |
677 | * @return array | |
678 | */ | |
00be9182 | 679 | public function statistics(&$rows) { |
6a488035 | 680 | $statistics = parent::statistics($rows); |
388b7f79 | 681 | $sql = "{$this->_select} {$this->_from} {$this->_where}"; |
5b0a2753 | 682 | $dao = $this->executeReportQuery($sql); |
6a488035 | 683 | //store contributions in array 'contact_sums' for comparison |
affcc9d2 | 684 | $contact_sums = []; |
6a488035 | 685 | while ($dao->fetch()) { |
388b7f79 SB |
686 | $contact_sums[$dao->contact_civireport_id] = array( |
687 | 'contribution1_total_amount_sum' => $dao->contribution1_total_amount_sum, | |
688 | 'contribution2_total_amount_sum' => $dao->contribution2_total_amount_sum, | |
689 | ); | |
6a488035 TO |
690 | } |
691 | ||
692 | $total_distinct_contacts = count($contact_sums); | |
9e9b9b82 | 693 | $maintained = 0; |
694 | $upgraded = 0; | |
695 | $downgraded = 0; | |
696 | $new = 0; | |
697 | $lapsed = 0; | |
6a488035 TO |
698 | |
699 | foreach ($contact_sums as $uid => $row) { | |
9d72cede EM |
700 | if ($row['contribution1_total_amount_sum'] && |
701 | $row['contribution2_total_amount_sum'] | |
702 | ) { | |
703 | $change = ($row['contribution1_total_amount_sum'] - | |
704 | $row['contribution2_total_amount_sum']); | |
705 | if ($change == 0) { | |
9e9b9b82 | 706 | $maintained += 1; |
9d72cede EM |
707 | } |
708 | elseif ($change > 0) { | |
9e9b9b82 | 709 | $upgraded += 1; |
9d72cede EM |
710 | } |
711 | elseif ($change < 0) { | |
9e9b9b82 | 712 | $downgraded += 1; |
9d72cede | 713 | } |
6a488035 TO |
714 | } |
715 | elseif ($row['contribution1_total_amount_sum']) { | |
9e9b9b82 | 716 | $new += 1; |
6a488035 TO |
717 | } |
718 | elseif ($row['contribution2_total_amount_sum']) { | |
9e9b9b82 | 719 | $lapsed += 1; |
6a488035 TO |
720 | } |
721 | } | |
722 | ||
723 | //calculate percentages from numbers | |
9e9b9b82 | 724 | if (!empty($total_distinct_contacts)) { |
725 | $maintained = ($maintained / $total_distinct_contacts) * 100; | |
726 | $upgraded = ($upgraded / $total_distinct_contacts) * 100; | |
727 | $downgraded = ($downgraded / $total_distinct_contacts) * 100; | |
728 | $new = ($new / $total_distinct_contacts) * 100; | |
729 | $lapsed = ($lapsed / $total_distinct_contacts) * 100; | |
730 | } | |
6a488035 TO |
731 | //display percentages for new, lapsed, upgraded, downgraded, and maintained contributors |
732 | $statistics['counts']['count_new'] = array( | |
9e9b9b82 | 733 | 'value' => $new, |
fd6a6828 | 734 | 'title' => ts('% New Donors'), |
6a488035 TO |
735 | ); |
736 | $statistics['counts']['count_lapsed'] = array( | |
9e9b9b82 | 737 | 'value' => $lapsed, |
fd6a6828 | 738 | 'title' => ts('% Lapsed Donors'), |
6a488035 TO |
739 | ); |
740 | $statistics['counts']['count_upgraded'] = array( | |
9e9b9b82 | 741 | 'value' => $upgraded, |
fd6a6828 | 742 | 'title' => ts('% Upgraded Donors'), |
6a488035 TO |
743 | ); |
744 | $statistics['counts']['count_downgraded'] = array( | |
9e9b9b82 | 745 | 'value' => $downgraded, |
fd6a6828 | 746 | 'title' => ts('% Downgraded Donors'), |
6a488035 TO |
747 | ); |
748 | $statistics['counts']['count_maintained'] = array( | |
9e9b9b82 | 749 | 'value' => $maintained, |
fd6a6828 | 750 | 'title' => ts('% Maintained Donors'), |
6a488035 TO |
751 | ); |
752 | ||
753 | $select = " | |
754 | SELECT COUNT({$this->_aliases['civicrm_contribution']}1.total_amount_count ) as count, | |
755 | SUM({$this->_aliases['civicrm_contribution']}1.total_amount_sum ) as amount, | |
756 | ROUND(AVG({$this->_aliases['civicrm_contribution']}1.total_amount_sum), 2) as avg, | |
2f4c2f5d | 757 | COUNT({$this->_aliases['civicrm_contribution']}2.total_amount_count ) as count2, |
6a488035 | 758 | SUM({$this->_aliases['civicrm_contribution']}2.total_amount_sum ) as amount2, |
625749cb | 759 | ROUND(AVG({$this->_aliases['civicrm_contribution']}2.total_amount_sum), 2) as avg2, |
760 | currency"; | |
761 | $sql = "{$select} {$this->_from} {$this->_where} | |
2f4c2f5d | 762 | GROUP BY currency |
625749cb | 763 | "; |
5b0a2753 | 764 | $dao = $this->executeReportQuery($sql); |
6a488035 | 765 | |
affcc9d2 | 766 | $amount = $average = $amount2 = $average2 = []; |
625749cb | 767 | $count = $count2 = 0; |
768 | while ($dao->fetch()) { | |
769 | if ($dao->amount) { | |
5396af74 | 770 | $amount[] |
771 | = CRM_Utils_Money::format($dao->amount, $dao->currency) . "(" . | |
9d72cede EM |
772 | $dao->count . ")"; |
773 | $average[] = CRM_Utils_Money::format($dao->avg, $dao->currency); | |
625749cb | 774 | } |
775 | ||
776 | $count += $dao->count; | |
777 | if ($dao->amount2) { | |
5396af74 | 778 | $amount2[] |
779 | = CRM_Utils_Money::format($dao->amount2, $dao->currency) . "(" . | |
9d72cede EM |
780 | $dao->count . ")"; |
781 | $average2[] = CRM_Utils_Money::format($dao->avg2, $dao->currency); | |
625749cb | 782 | } |
783 | $count2 += $dao->count2; | |
6a488035 TO |
784 | } |
785 | ||
fd6a6828 | 786 | $statistics['counts']['range_one_title'] = array('title' => ts('Initial Date Range:')); |
625749cb | 787 | $statistics['counts']['amount'] = array( |
788 | 'value' => implode(', ', $amount), | |
fd6a6828 | 789 | 'title' => ts('Total Amount'), |
625749cb | 790 | 'type' => CRM_Utils_Type::T_STRING, |
791 | ); | |
792 | $statistics['counts']['count'] = array( | |
793 | 'value' => $count, | |
fd6a6828 | 794 | 'title' => ts('Total Donations'), |
625749cb | 795 | ); |
796 | $statistics['counts']['avg'] = array( | |
797 | 'value' => implode(', ', $average), | |
fd6a6828 | 798 | 'title' => ts('Average'), |
625749cb | 799 | 'type' => CRM_Utils_Type::T_STRING, |
800 | ); | |
801 | $statistics['counts']['range_two_title'] = array( | |
fd6a6828 | 802 | 'title' => ts('Second Date Range:'), |
625749cb | 803 | ); |
804 | $statistics['counts']['amount2'] = array( | |
805 | 'value' => implode(', ', $amount2), | |
fd6a6828 | 806 | 'title' => ts('Total Amount'), |
625749cb | 807 | 'type' => CRM_Utils_Type::T_STRING, |
808 | ); | |
809 | $statistics['counts']['count2'] = array( | |
810 | 'value' => $count2, | |
fd6a6828 | 811 | 'title' => ts('Total Donations'), |
625749cb | 812 | ); |
813 | $statistics['counts']['avg2'] = array( | |
814 | 'value' => implode(', ', $average2), | |
fd6a6828 | 815 | 'title' => ts('Average'), |
625749cb | 816 | 'type' => CRM_Utils_Type::T_STRING, |
817 | ); | |
818 | ||
6a488035 TO |
819 | return $statistics; |
820 | } | |
821 | ||
00be9182 | 822 | public function postProcess() { |
6a488035 | 823 | $this->beginPostProcess(); |
6a488035 | 824 | |
5e3dec81 | 825 | $this->buildGroupTempTable(); |
6a488035 TO |
826 | $this->select(); |
827 | $this->from(); | |
3fd8e21a | 828 | $this->customDataFrom(); |
6a488035 TO |
829 | $this->where(); |
830 | $this->groupBy(); | |
a6572737 | 831 | $this->orderBy(); |
6a488035 TO |
832 | $this->limit(); |
833 | ||
834 | $count = 0; | |
a6572737 | 835 | $sql = "{$this->_select} {$this->_from} {$this->_where} {$this->_groupBy} {$this->_orderBy} {$this->_limit}"; |
5b0a2753 | 836 | $dao = $this->executeReportQuery($sql); |
affcc9d2 | 837 | $rows = []; |
6a488035 TO |
838 | while ($dao->fetch()) { |
839 | foreach ($this->_columnHeaders as $key => $value) { | |
840 | $rows[$count][$key] = $dao->$key; | |
841 | } | |
842 | $count++; | |
843 | } | |
844 | ||
845 | // FIXME: calculate % using query | |
846 | foreach ($rows as $uid => $row) { | |
9d72cede EM |
847 | if ($row['contribution1_total_amount_sum'] && |
848 | $row['contribution2_total_amount_sum'] | |
849 | ) { | |
6a488035 | 850 | $rows[$uid]['change'] = number_format((($row['contribution2_total_amount_sum'] - |
9d72cede EM |
851 | $row['contribution1_total_amount_sum'] |
852 | ) * 100) / | |
853 | ($row['contribution1_total_amount_sum']), 2 | |
6a488035 TO |
854 | ); |
855 | } | |
856 | elseif ($row['contribution1_total_amount_sum']) { | |
857 | $rows[$uid]['change'] = ts('Skipped Donation'); | |
858 | } | |
859 | elseif ($row['contribution2_total_amount_sum']) { | |
860 | $rows[$uid]['change'] = ts('New Donor'); | |
861 | } | |
862 | if ($row['contribution1_total_amount_count']) { | |
5396af74 | 863 | $rows[$uid]['contribution1_total_amount_sum'] |
864 | = $row['contribution1_total_amount_sum'] . | |
9d72cede | 865 | " ({$row['contribution1_total_amount_count']})"; |
6a488035 TO |
866 | } |
867 | if ($row['contribution2_total_amount_count']) { | |
5396af74 | 868 | $rows[$uid]['contribution2_total_amount_sum'] |
869 | = $row['contribution2_total_amount_sum'] . | |
9d72cede | 870 | " ({$row['contribution2_total_amount_count']})"; |
6a488035 TO |
871 | } |
872 | } | |
873 | $this->_columnHeaders['change'] = array( | |
fd6a6828 | 874 | 'title' => ts('% Change'), |
6a488035 TO |
875 | 'type' => CRM_Utils_Type::T_INT, |
876 | ); | |
877 | ||
878 | // hack to fix title | |
879 | list($from1, $to1) = $this->getFromTo(CRM_Utils_Array::value("receive_date1_relative", $this->_params), | |
880 | CRM_Utils_Array::value("receive_date1_from", $this->_params), | |
881 | CRM_Utils_Array::value("receive_date1_to", $this->_params) | |
882 | ); | |
883 | $from1 = CRM_Utils_Date::customFormat($from1, NULL, array('d')); | |
884 | $to1 = CRM_Utils_Date::customFormat($to1, NULL, array('d')); | |
885 | ||
886 | list($from2, $to2) = $this->getFromTo(CRM_Utils_Array::value("receive_date2_relative", $this->_params), | |
887 | CRM_Utils_Array::value("receive_date2_from", $this->_params), | |
888 | CRM_Utils_Array::value("receive_date2_to", $this->_params) | |
889 | ); | |
890 | $from2 = CRM_Utils_Date::customFormat($from2, NULL, array('d')); | |
891 | $to2 = CRM_Utils_Date::customFormat($to2, NULL, array('d')); | |
892 | ||
38e1bd73 | 893 | $this->_columnHeaders['contribution1_total_amount_sum']['title'] = "$from1 - $to1"; |
894 | $this->_columnHeaders['contribution2_total_amount_sum']['title'] = "$from2 - $to2"; | |
6a488035 | 895 | unset($this->_columnHeaders['contribution1_total_amount_count'], |
9d72cede | 896 | $this->_columnHeaders['contribution2_total_amount_count'] |
6a488035 TO |
897 | ); |
898 | ||
899 | $this->formatDisplay($rows); | |
900 | ||
901 | // assign variables to templates | |
902 | $this->doTemplateAssignment($rows); | |
903 | ||
904 | $this->endPostProcess($rows); | |
905 | } | |
906 | ||
74cf4551 | 907 | /** |
ced9bfed EM |
908 | * Alter display of rows. |
909 | * | |
910 | * Iterate through the rows retrieved via SQL and make changes for display purposes, | |
911 | * such as rendering contacts as links. | |
912 | * | |
913 | * @param array $rows | |
914 | * Rows generated by SQL, with an array for each row. | |
74cf4551 | 915 | */ |
00be9182 | 916 | public function alterDisplay(&$rows) { |
6a488035 TO |
917 | list($from1, $to1) = $this->getFromTo(CRM_Utils_Array::value("receive_date1_relative", $this->_params), |
918 | CRM_Utils_Array::value("receive_date1_from", $this->_params), | |
919 | CRM_Utils_Array::value("receive_date1_to", $this->_params) | |
920 | ); | |
921 | list($from2, $to2) = $this->getFromTo(CRM_Utils_Array::value("receive_date2_relative", $this->_params), | |
922 | CRM_Utils_Array::value("receive_date2_from", $this->_params), | |
923 | CRM_Utils_Array::value("receive_date2_to", $this->_params) | |
924 | ); | |
925 | ||
926 | $dateUrl = ""; | |
927 | if ($from1) { | |
928 | $dateUrl .= "receive_date1_from={$from1}&"; | |
929 | } | |
930 | if ($to1) { | |
931 | $dateUrl .= "receive_date1_to={$to1}&"; | |
932 | } | |
933 | if ($from2) { | |
934 | $dateUrl .= "receive_date2_from={$from2}&"; | |
935 | } | |
936 | if ($to2) { | |
937 | $dateUrl .= "receive_date2_to={$to2}&"; | |
938 | } | |
939 | ||
940 | foreach ($rows as $rowNum => $row) { | |
941 | // handle country | |
942 | if (array_key_exists('address_civireport_country_id', $row)) { | |
943 | if ($value = $row['address_civireport_country_id']) { | |
944 | $rows[$rowNum]['address_civireport_country_id'] = CRM_Core_PseudoConstant::country($value, FALSE); | |
945 | ||
946 | $url = CRM_Report_Utils_Report::getNextUrl('contribute/detail', | |
947 | "reset=1&force=1&" . | |
9d72cede EM |
948 | "country_id_op=in&country_id_value={$value}&" . |
949 | "$dateUrl", | |
6a488035 TO |
950 | $this->_absoluteUrl, $this->_id, $this->_drilldownReport |
951 | ); | |
952 | ||
953 | $rows[$rowNum]['address_civireport_country_id_link'] = $url; | |
954 | $rows[$rowNum]['address_civireport_country_id_hover'] = ts("View contributions for this Country."); | |
955 | } | |
956 | } | |
957 | ||
958 | // handle state province | |
959 | if (array_key_exists('address_civireport_state_province_id', $row)) { | |
960 | if ($value = $row['address_civireport_state_province_id']) { | |
961 | $rows[$rowNum]['address_civireport_state_province_id'] = CRM_Core_PseudoConstant::stateProvince($value, FALSE); | |
962 | ||
963 | $url = CRM_Report_Utils_Report::getNextUrl('contribute/detail', | |
964 | "reset=1&force=1&" . | |
9d72cede EM |
965 | "state_province_id_op=in&state_province_id_value={$value}&" . |
966 | "$dateUrl", | |
6a488035 TO |
967 | $this->_absoluteUrl, $this->_id, $this->_drilldownReport |
968 | ); | |
969 | ||
970 | $rows[$rowNum]['address_civireport_state_province_id_link'] = $url; | |
971 | $rows[$rowNum]['address_civireport_state_province_id_hover'] = ts("View repeatDetails for this state."); | |
972 | } | |
973 | } | |
974 | ||
975 | // convert display name to links | |
976 | if (array_key_exists('contact_civireport_sort_name', $row) && | |
977 | array_key_exists('contact_civireport_id', $row) | |
978 | ) { | |
979 | $url = CRM_Report_Utils_Report::getNextUrl('contribute/detail', | |
980 | 'reset=1&force=1&id_op=eq&id_value=' . $row['contact_civireport_id'], | |
981 | $this->_absoluteUrl, $this->_id, $this->_drilldownReport | |
982 | ); | |
2f4c2f5d | 983 | |
6a488035 TO |
984 | $rows[$rowNum]['contact_civireport_sort_name_link'] = $url; |
985 | $rows[$rowNum]['contact_civireport_sort_name_hover'] = ts("View Contribution details for this contact"); | |
986 | } | |
987 | } | |
988 | // foreach ends | |
989 | } | |
96025800 | 990 | |
26b55a9e | 991 | /** |
992 | * Build the temp tables for comparison. | |
993 | */ | |
994 | protected function buildTempTables() { | |
995 | $this->setGroupByInformation(); | |
996 | $create = $subSelect1 = $subSelect2 = NULL; | |
997 | if ($this->tempTableRepeat1) { | |
998 | return; | |
999 | } | |
1000 | ||
1001 | if ($this->groupByTable == 'civicrm_financial_type') { | |
1002 | $subSelect1 = 'contribution1.contact_id,'; | |
1003 | $subSelect2 = 'contribution2.contact_id,'; | |
1004 | $create = 'contact_id int unsigned,'; | |
1005 | } | |
1006 | ||
1007 | $subWhere = $this->whereContribution(); | |
1008 | $from = $this->fromContribution(); | |
1009 | $subContributionQuery1 = " | |
1010 | SELECT {$subSelect1} contribution1.{$this->contributionJoinTableColumn}, | |
1011 | sum( contribution1.total_amount ) AS total_amount_sum, | |
1012 | count( * ) AS total_amount_count | |
1013 | {$from} | |
1014 | {$subWhere} | |
1015 | GROUP BY contribution1.{$this->contributionJoinTableColumn}"; | |
1016 | ||
1017 | $subWhere = $this->whereContribution('contribution2'); | |
1018 | $from = $this->fromContribution('contribution2'); | |
1019 | $subContributionQuery2 = " | |
1020 | SELECT {$subSelect2} contribution2.{$this->contributionJoinTableColumn}, | |
1021 | sum( contribution2.total_amount ) AS total_amount_sum, | |
1022 | count( * ) AS total_amount_count, | |
1023 | currency | |
1024 | {$from} | |
1025 | {$subWhere} | |
d1641c51 | 1026 | GROUP BY contribution2.{$this->contributionJoinTableColumn}, currency"; |
03843223 | 1027 | $this->tempTableRepeat1 = $this->createTemporaryTable('tempTableRepeat1', " |
26b55a9e | 1028 | {$create} |
1029 | {$this->contributionJoinTableColumn} int unsigned, | |
98e0cf82 | 1030 | total_amount_sum decimal(20,2), |
26b55a9e | 1031 | total_amount_count int |
03843223 | 1032 | ", TRUE, TRUE); |
5b0a2753 | 1033 | $this->executeReportQuery("INSERT INTO $this->tempTableRepeat1 {$subContributionQuery1}"); |
26b55a9e | 1034 | |
5b0a2753 | 1035 | $this->executeReportQuery(" |
26b55a9e | 1036 | ALTER TABLE $this->tempTableRepeat1 ADD INDEX ({$this->contributionJoinTableColumn}) |
1037 | "); | |
1038 | ||
03843223 | 1039 | $this->tempTableRepeat2 = $this->createTemporaryTable('tempTableRepeat2', " |
26b55a9e | 1040 | {$create} |
1041 | {$this->contributionJoinTableColumn} int unsigned, | |
98e0cf82 | 1042 | total_amount_sum decimal(20,2), |
26b55a9e | 1043 | total_amount_count int, |
1044 | currency varchar(3) | |
03843223 | 1045 | ", TRUE, TRUE); |
26b55a9e | 1046 | $sql = "INSERT INTO $this->tempTableRepeat2 {$subContributionQuery2}"; |
5b0a2753 | 1047 | $this->executeReportQuery($sql); |
26b55a9e | 1048 | |
5b0a2753 | 1049 | $this->executeReportQuery(" |
26b55a9e | 1050 | ALTER TABLE $this->tempTableRepeat2 ADD INDEX ({$this->contributionJoinTableColumn}) |
1051 | "); | |
1052 | ||
1053 | } | |
1054 | ||
6a488035 | 1055 | } |