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_TopDonor extends CRM_Report_Form { | |
18 | ||
19 | protected $_summary = NULL; | |
be2fb01f | 20 | protected $_customGroupExtends = [ |
70bea8e2 | 21 | 'Contact', |
22 | 'Individual', | |
7d793900 | 23 | 'Contribution', |
be2fb01f | 24 | ]; |
6a488035 | 25 | |
1728e9a0 | 26 | /** |
27 | * This report has not been optimised for group filtering. | |
28 | * | |
29 | * The functionality for group filtering has been improved but not | |
30 | * all reports have been adjusted to take care of it. This report has not | |
31 | * and will run an inefficient query until fixed. | |
32 | * | |
1728e9a0 | 33 | * @var bool |
0e480632 | 34 | * @see https://issues.civicrm.org/jira/browse/CRM-19170 |
1728e9a0 | 35 | */ |
36 | protected $groupFilterNotOptimised = TRUE; | |
37 | ||
be2fb01f | 38 | public $_drilldownReport = ['contribute/detail' => 'Link to Detail Report']; |
6a488035 | 39 | |
74cf4551 | 40 | /** |
74cf4551 | 41 | */ |
00be9182 | 42 | public function __construct() { |
70bea8e2 | 43 | $this->_autoIncludeIndexedFieldsAsOrderBys = 1; |
be2fb01f CW |
44 | $this->_columns = [ |
45 | 'civicrm_contact' => [ | |
ae5ffbb7 | 46 | 'dao' => 'CRM_Contact_DAO_Contact', |
be2fb01f CW |
47 | 'fields' => [ |
48 | 'display_name' => [ | |
ae5ffbb7 TO |
49 | 'title' => ts('Contact Name'), |
50 | 'required' => TRUE, | |
51 | 'no_repeat' => TRUE, | |
be2fb01f CW |
52 | ], |
53 | 'first_name' => [ | |
ae5ffbb7 | 54 | 'title' => ts('First Name'), |
be2fb01f CW |
55 | ], |
56 | 'middle_name' => [ | |
70bea8e2 | 57 | 'title' => ts('Middle Name'), |
be2fb01f CW |
58 | ], |
59 | 'last_name' => [ | |
ae5ffbb7 | 60 | 'title' => ts('Last Name'), |
be2fb01f CW |
61 | ], |
62 | 'id' => [ | |
70bea8e2 | 63 | 'no_display' => TRUE, |
64 | 'required' => TRUE, | |
be2fb01f CW |
65 | ], |
66 | 'gender_id' => [ | |
70bea8e2 | 67 | 'title' => ts('Gender'), |
be2fb01f CW |
68 | ], |
69 | 'birth_date' => [ | |
70bea8e2 | 70 | 'title' => ts('Birth Date'), |
be2fb01f CW |
71 | ], |
72 | 'age' => [ | |
70bea8e2 | 73 | 'title' => ts('Age'), |
74 | 'dbAlias' => 'TIMESTAMPDIFF(YEAR, contact_civireport.birth_date, CURDATE())', | |
be2fb01f CW |
75 | ], |
76 | 'contact_type' => [ | |
ae5ffbb7 | 77 | 'title' => ts('Contact Type'), |
be2fb01f CW |
78 | ], |
79 | 'contact_sub_type' => [ | |
ae5ffbb7 | 80 | 'title' => ts('Contact Subtype'), |
be2fb01f CW |
81 | ], |
82 | ], | |
bef8d64c | 83 | 'filters' => $this->getBasicContactFilters(), |
c3fdd2b7 | 84 | 'group_bys' => ['contact_contact_id' => ['name' => 'id', 'required' => 1, 'no_display' => 1]], |
be2fb01f CW |
85 | ], |
86 | 'civicrm_line_item' => [ | |
cbfbab2f | 87 | 'dao' => 'CRM_Price_DAO_LineItem', |
be2fb01f CW |
88 | ], |
89 | ]; | |
c3fdd2b7 | 90 | $this->_columns += $this->getAddressColumns(['group_by' => FALSE]); |
be2fb01f CW |
91 | $this->_columns += [ |
92 | 'civicrm_contribution' => [ | |
ae5ffbb7 | 93 | 'dao' => 'CRM_Contribute_DAO_Contribution', |
be2fb01f CW |
94 | 'fields' => [ |
95 | 'total_amount' => [ | |
ae5ffbb7 TO |
96 | 'title' => ts('Amount Statistics'), |
97 | 'required' => TRUE, | |
be2fb01f | 98 | 'statistics' => [ |
ae5ffbb7 TO |
99 | 'sum' => ts('Aggregate Amount'), |
100 | 'count' => ts('Donations'), | |
101 | 'avg' => ts('Average'), | |
be2fb01f CW |
102 | ], |
103 | ], | |
104 | 'currency' => [ | |
ae5ffbb7 TO |
105 | 'required' => TRUE, |
106 | 'no_display' => TRUE, | |
be2fb01f CW |
107 | ], |
108 | ], | |
109 | 'filters' => [ | |
110 | 'receive_date' => [ | |
ae5ffbb7 TO |
111 | 'default' => 'this.year', |
112 | 'operatorType' => CRM_Report_Form::OP_DATE, | |
be2fb01f | 113 | ], |
f85b4a88 | 114 | 'receipt_date' => ['operatorType' => CRM_Report_Form::OP_DATE], |
be2fb01f | 115 | 'currency' => [ |
ccc29f8e | 116 | 'title' => ts('Currency'), |
ae5ffbb7 TO |
117 | 'operatorType' => CRM_Report_Form::OP_MULTISELECT, |
118 | 'options' => CRM_Core_OptionGroup::values('currencies_enabled'), | |
119 | 'default' => NULL, | |
120 | 'type' => CRM_Utils_Type::T_STRING, | |
be2fb01f CW |
121 | ], |
122 | 'total_range' => [ | |
ae5ffbb7 TO |
123 | 'title' => ts('Show no. of Top Donors'), |
124 | 'type' => CRM_Utils_Type::T_INT, | |
125 | 'default_op' => 'eq', | |
be2fb01f CW |
126 | ], |
127 | 'financial_type_id' => [ | |
ae5ffbb7 TO |
128 | 'name' => 'financial_type_id', |
129 | 'title' => ts('Financial Type'), | |
8ee006e7 | 130 | 'type' => CRM_Utils_Type::T_INT, |
ae5ffbb7 | 131 | 'operatorType' => CRM_Report_Form::OP_MULTISELECT, |
51d1f926 | 132 | 'options' => CRM_Contribute_BAO_Contribution::buildOptions('financial_type_id', 'search'), |
be2fb01f CW |
133 | ], |
134 | 'contribution_status_id' => [ | |
ae5ffbb7 TO |
135 | 'title' => ts('Contribution Status'), |
136 | 'operatorType' => CRM_Report_Form::OP_MULTISELECT, | |
c0aaecf9 | 137 | 'options' => CRM_Contribute_BAO_Contribution::buildOptions('contribution_status_id', 'search'), |
be2fb01f CW |
138 | 'default' => [1], |
139 | ], | |
140 | ], | |
c3fdd2b7 | 141 | 'group_bys' => ['contribution_currency' => ['name' => 'currency', 'required' => 1, 'no_display' => 1]], |
be2fb01f CW |
142 | ], |
143 | 'civicrm_financial_trxn' => [ | |
bdfeefa3 | 144 | 'dao' => 'CRM_Financial_DAO_FinancialTrxn', |
be2fb01f CW |
145 | 'fields' => [ |
146 | 'card_type_id' => [ | |
d72b084a | 147 | 'title' => ts('Credit Card Type'), |
5e0343e8 | 148 | 'dbAlias' => 'GROUP_CONCAT(financial_trxn_civireport.card_type_id SEPARATOR ",")', |
be2fb01f CW |
149 | ], |
150 | ], | |
151 | 'filters' => [ | |
152 | 'card_type_id' => [ | |
d72b084a | 153 | 'title' => ts('Credit Card Type'), |
bdfeefa3 | 154 | 'operatorType' => CRM_Report_Form::OP_MULTISELECT, |
5e0343e8 | 155 | 'options' => CRM_Financial_DAO_FinancialTrxn::buildOptions('card_type_id'), |
bdfeefa3 E |
156 | 'default' => NULL, |
157 | 'type' => CRM_Utils_Type::T_STRING, | |
be2fb01f CW |
158 | ], |
159 | ], | |
160 | ], | |
161 | 'civicrm_email' => [ | |
ae5ffbb7 | 162 | 'dao' => 'CRM_Core_DAO_Email', |
be2fb01f CW |
163 | 'fields' => [ |
164 | 'email' => [ | |
ae5ffbb7 TO |
165 | 'title' => ts('Email'), |
166 | 'default' => TRUE, | |
167 | 'no_repeat' => TRUE, | |
be2fb01f CW |
168 | ], |
169 | ], | |
101f8739 | 170 | 'filters' => [ |
171 | 'on_hold' => [ | |
172 | 'title' => ts('On Hold'), | |
173 | 'type' => CRM_Utils_Type::T_INT, | |
174 | 'operatorType' => CRM_Report_Form::OP_MULTISELECT, | |
175 | 'options' => ['' => ts('Any')] + CRM_Core_PseudoConstant::emailOnHoldOptions(), | |
176 | ], | |
177 | ], | |
ae5ffbb7 | 178 | 'grouping' => 'email-fields', |
be2fb01f CW |
179 | ], |
180 | 'civicrm_phone' => [ | |
ae5ffbb7 | 181 | 'dao' => 'CRM_Core_DAO_Phone', |
be2fb01f CW |
182 | 'fields' => [ |
183 | 'phone' => [ | |
ae5ffbb7 TO |
184 | 'title' => ts('Phone'), |
185 | 'default' => TRUE, | |
186 | 'no_repeat' => TRUE, | |
be2fb01f CW |
187 | ], |
188 | ], | |
ae5ffbb7 | 189 | 'grouping' => 'phone-fields', |
be2fb01f CW |
190 | ], |
191 | ]; | |
6a488035 | 192 | |
f787b3c4 BT |
193 | // Add charts support |
194 | $this->_charts = [ | |
195 | '' => ts('Tabular'), | |
196 | 'barChart' => ts('Bar Chart'), | |
197 | 'pieChart' => ts('Pie Chart'), | |
198 | ]; | |
199 | ||
16e2e80c | 200 | $this->_groupFilter = TRUE; |
6a488035 | 201 | $this->_tagFilter = TRUE; |
7a961f19 | 202 | $this->_currencyColumn = 'civicrm_contribution_currency'; |
6a488035 TO |
203 | parent::__construct(); |
204 | } | |
205 | ||
74cf4551 EM |
206 | /** |
207 | * @param $fields | |
208 | * @param $files | |
e8cf95b4 | 209 | * @param self $self |
74cf4551 EM |
210 | * |
211 | * @return array | |
212 | */ | |
00be9182 | 213 | public static function formRule($fields, $files, $self) { |
be2fb01f | 214 | $errors = []; |
6a488035 | 215 | |
9c1bc317 CW |
216 | $op = $fields['total_range_op'] ?? NULL; |
217 | $val = $fields['total_range_value'] ?? NULL; | |
6a488035 | 218 | |
be2fb01f | 219 | if (!in_array($op, [ |
9d72cede | 220 | 'eq', |
21dfd5f5 | 221 | 'lte', |
be2fb01f | 222 | ]) |
9d72cede | 223 | ) { |
6a488035 TO |
224 | $errors['total_range_op'] = ts("Please select 'Is equal to' OR 'Is Less than or equal to' operator"); |
225 | } | |
226 | ||
227 | if ($val && !CRM_Utils_Rule::positiveInteger($val)) { | |
228 | $errors['total_range_value'] = ts("Please enter positive number"); | |
229 | } | |
230 | return $errors; | |
231 | } | |
232 | ||
00be9182 | 233 | public function from() { |
6a488035 TO |
234 | $this->_from = " |
235 | FROM civicrm_contact {$this->_aliases['civicrm_contact']} {$this->_aclFrom} | |
65f9ed10 | 236 | INNER JOIN civicrm_contribution {$this->_aliases['civicrm_contribution']} |
9f108b4d | 237 | ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_contribution']}.contact_id AND {$this->_aliases['civicrm_contribution']}.is_test = 0 AND {$this->_aliases['civicrm_contribution']}.is_template = 0 |
18f511e2 | 238 | "; |
bdfeefa3 E |
239 | |
240 | // for credit card type | |
241 | $this->addFinancialTrxnFromClause(); | |
242 | ||
18f511e2 | 243 | $this->joinAddressFromContact(); |
244 | $this->joinPhoneFromContact(); | |
245 | $this->joinEmailFromContact(); | |
6a488035 TO |
246 | } |
247 | ||
00be9182 | 248 | public function where() { |
be2fb01f | 249 | $clauses = []; |
93406a82 | 250 | $this->_tempClause = $this->_outerCluase = $this->_groupLimit = ''; |
6a488035 TO |
251 | foreach ($this->_columns as $tableName => $table) { |
252 | if (array_key_exists('filters', $table)) { | |
253 | foreach ($table['filters'] as $fieldName => $field) { | |
254 | $clause = NULL; | |
255 | if (CRM_Utils_Array::value('type', $field) & CRM_Utils_Type::T_DATE) { | |
9c1bc317 CW |
256 | $relative = $this->_params["{$fieldName}_relative"] ?? NULL; |
257 | $from = $this->_params["{$fieldName}_from"] ?? NULL; | |
258 | $to = $this->_params["{$fieldName}_to"] ?? NULL; | |
6a488035 TO |
259 | |
260 | if ($relative || $from || $to) { | |
261 | $clause = $this->dateClause($field['name'], $relative, $from, $to, $field['type']); | |
262 | } | |
263 | } | |
264 | else { | |
9c1bc317 | 265 | $op = $this->_params["{$fieldName}_op"] ?? NULL; |
6a488035 TO |
266 | if ($op) { |
267 | $clause = $this->whereClause($field, | |
268 | $op, | |
269 | CRM_Utils_Array::value("{$fieldName}_value", $this->_params), | |
270 | CRM_Utils_Array::value("{$fieldName}_min", $this->_params), | |
271 | CRM_Utils_Array::value("{$fieldName}_max", $this->_params) | |
272 | ); | |
273 | } | |
274 | } | |
275 | ||
276 | if (!empty($clause)) { | |
277 | if ($fieldName == 'total_range') { | |
9c1bc317 | 278 | $value = $this->_params["total_range_value"] ?? NULL; |
6a488035 | 279 | $this->_outerCluase = " WHERE (( @rows := @rows + 1) <= {$value}) "; |
93406a82 | 280 | $this->_groupLimit = " LIMIT {$value}"; |
6a488035 TO |
281 | } |
282 | else { | |
283 | $clauses[] = $clause; | |
284 | } | |
285 | } | |
286 | } | |
287 | } | |
288 | } | |
289 | if (empty($clauses)) { | |
290 | $this->_where = "WHERE ( 1 ) "; | |
291 | } | |
292 | else { | |
293 | $this->_where = "WHERE " . implode(' AND ', $clauses); | |
294 | } | |
295 | ||
296 | if ($this->_aclWhere) { | |
297 | $this->_where .= " AND {$this->_aclWhere} "; | |
298 | } | |
299 | } | |
300 | ||
969a8733 | 301 | /** |
302 | * Build output rows. | |
303 | * | |
304 | * @param string $sql | |
305 | * @param array $rows | |
306 | */ | |
307 | public function buildRows($sql, &$rows) { | |
6a488035 TO |
308 | $setVariable = " SET @rows:=0, @rank=0 "; |
309 | CRM_Core_DAO::singleValueQuery($setVariable); | |
969a8733 | 310 | $sql = " |
311 | SELECT * FROM ( {$this->_select} {$this->_from} {$this->_where} {$this->_groupBy} | |
312 | ORDER BY civicrm_contribution_total_amount_sum DESC | |
313 | ) as abc {$this->_outerCluase} $this->_limit | |
314 | "; | |
315 | parent::buildRows($sql, $rows); | |
6a488035 TO |
316 | } |
317 | ||
74cf4551 | 318 | /** |
100fef9d | 319 | * @param int $groupID |
74cf4551 | 320 | */ |
00be9182 | 321 | public function add2group($groupID) { |
6a488035 | 322 | if (is_numeric($groupID)) { |
969a8733 | 323 | $this->_limit = $this->_groupLimit; |
be2fb01f | 324 | $rows = []; |
969a8733 | 325 | $this->_columnHeaders['civicrm_contact_id'] = 1; |
326 | $this->buildRows('', $rows); | |
6a488035 | 327 | |
be2fb01f | 328 | $contact_ids = []; |
6a488035 | 329 | // Add resulting contacts to group |
969a8733 | 330 | foreach ($rows as $row) { |
331 | $contact_ids[$row['civicrm_contact_id']] = $row['civicrm_contact_id']; | |
6a488035 TO |
332 | } |
333 | ||
334 | CRM_Contact_BAO_GroupContact::addContactsToGroup($contact_ids, $groupID); | |
335 | CRM_Core_Session::setStatus(ts("Listed contact(s) have been added to the selected group."), ts('Contacts Added'), 'success'); | |
336 | } | |
337 | } | |
338 | ||
74cf4551 EM |
339 | /** |
340 | * @param int $rowCount | |
341 | */ | |
11630f6a SL |
342 | public function limit($rowCount = NULL) { |
343 | $rowCount = $rowCount ?? $this->getRowCount(); | |
6a488035 TO |
344 | // lets do the pager if in html mode |
345 | $this->_limit = NULL; | |
74cf4551 | 346 | |
dbb4a0f9 PN |
347 | // CRM-14115, over-ride row count if rowCount is specified in URL |
348 | if ($this->_dashBoardRowCount) { | |
349 | $rowCount = $this->_dashBoardRowCount; | |
350 | } | |
969a8733 | 351 | if ($this->_outputMode == 'html') { |
ab432335 | 352 | // Replace only first occurrence of SELECT. |
6a488035 | 353 | $this->_select = preg_replace('/SELECT/', 'SELECT SQL_CALC_FOUND_ROWS ', $this->_select, 1); |
a3d827a7 | 354 | $pageId = CRM_Utils_Request::retrieve('crmPID', 'Integer'); |
6a488035 TO |
355 | |
356 | if (!$pageId && !empty($_POST) && isset($_POST['crmPID_B'])) { | |
357 | if (!isset($_POST['PagerBottomButton'])) { | |
358 | unset($_POST['crmPID_B']); | |
359 | } | |
360 | else { | |
9d72cede | 361 | $pageId = max((int) @$_POST['crmPID_B'], 1); |
6a488035 TO |
362 | } |
363 | } | |
364 | ||
365 | $pageId = $pageId ? $pageId : 1; | |
366 | $this->set(CRM_Utils_Pager::PAGE_ID, $pageId); | |
367 | $offset = ($pageId - 1) * $rowCount; | |
368 | ||
bf00d1b6 DL |
369 | $offset = CRM_Utils_Type::escape($offset, 'Int'); |
370 | $rowCount = CRM_Utils_Type::escape($rowCount, 'Int'); | |
371 | ||
6a488035 TO |
372 | $this->_limit = " LIMIT $offset, " . $rowCount; |
373 | } | |
374 | } | |
375 | ||
74cf4551 | 376 | /** |
ced9bfed EM |
377 | * Alter display of rows. |
378 | * | |
379 | * Iterate through the rows retrieved via SQL and make changes for display purposes, | |
380 | * such as rendering contacts as links. | |
381 | * | |
382 | * @param array $rows | |
383 | * Rows generated by SQL, with an array for each row. | |
74cf4551 | 384 | */ |
00be9182 | 385 | public function alterDisplay(&$rows) { |
6a488035 TO |
386 | $entryFound = FALSE; |
387 | $rank = 1; | |
388 | if (!empty($rows)) { | |
389 | foreach ($rows as $rowNum => $row) { | |
390 | ||
391 | $rows[$rowNum]['civicrm_donor_rank'] = $rank++; | |
392 | // convert display name to links | |
393 | if (array_key_exists('civicrm_contact_display_name', $row) && | |
9d72cede EM |
394 | array_key_exists('civicrm_contact_id', $row) && |
395 | !empty($row['civicrm_contribution_currency']) | |
396 | ) { | |
6a488035 | 397 | $url = CRM_Report_Utils_Report::getNextUrl('contribute/detail', |
9d72cede EM |
398 | 'reset=1&force=1&id_op=eq&id_value=' . $row['civicrm_contact_id'] . |
399 | "¤cy_value=" . $row['civicrm_contribution_currency'], | |
6a488035 TO |
400 | $this->_absoluteUrl, $this->_id, $this->_drilldownReport |
401 | ); | |
402 | $rows[$rowNum]['civicrm_contact_display_name_link'] = $url; | |
403 | $entryFound = TRUE; | |
404 | } | |
21d62de7 | 405 | $entryFound = $this->alterDisplayAddressFields($row, $rows, $rowNum, 'contribute/detail', 'List all contribution(s)') ? TRUE : $entryFound; |
6a488035 | 406 | |
5e0343e8 | 407 | if (!empty($row['civicrm_financial_trxn_card_type_id'])) { |
408 | $rows[$rowNum]['civicrm_financial_trxn_card_type_id'] = $this->getLabels($row['civicrm_financial_trxn_card_type_id'], 'CRM_Financial_DAO_FinancialTrxn', 'card_type_id'); | |
bdfeefa3 E |
409 | $entryFound = TRUE; |
410 | } | |
411 | ||
4e7b6a04 | 412 | $entryFound = $this->alterDisplayContactFields($row, $rows, $rowNum, NULL, NULL) ? TRUE : $entryFound; |
413 | ||
6a488035 TO |
414 | // skip looking further in rows, if first row itself doesn't |
415 | // have the column we need | |
416 | if (!$entryFound) { | |
417 | break; | |
418 | } | |
419 | } | |
420 | } | |
421 | } | |
96025800 | 422 | |
6a488035 | 423 | } |