Commit | Line | Data |
---|---|---|
6a488035 | 1 | <?php |
6a488035 | 2 | /* |
bc77d7c0 TO |
3 | +--------------------------------------------------------------------+ |
4 | | Copyright CiviCRM LLC. All rights reserved. | | |
5 | | | | |
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 | | |
9 | +--------------------------------------------------------------------+ | |
e70a7fc0 | 10 | */ |
6a488035 TO |
11 | |
12 | /** | |
13 | * | |
14 | * @package CRM | |
ca5cec67 | 15 | * @copyright CiviCRM LLC https://civicrm.org/licensing |
6a488035 | 16 | */ |
70d00fce | 17 | class CRM_Report_Form_Event_ParticipantListing extends CRM_Report_Form { |
6a488035 TO |
18 | |
19 | protected $_summary = NULL; | |
9b34c9e3 | 20 | |
e8e008f5 | 21 | protected $_contribField = FALSE; |
26dcc739 E |
22 | protected $_groupFilter = TRUE; |
23 | protected $_tagFilter = TRUE; | |
9c607320 | 24 | protected $_balance = FALSE; |
1b303c2e | 25 | protected $campaigns; |
6a488035 | 26 | |
9d72cede EM |
27 | protected $_customGroupExtends = array( |
28 | 'Participant', | |
29 | 'Contact', | |
30 | 'Individual', | |
21dfd5f5 | 31 | 'Event', |
9d72cede | 32 | ); |
28ac0bbe | 33 | |
6a488035 TO |
34 | public $_drilldownReport = array('event/income' => 'Link to Detail Report'); |
35 | ||
74cf4551 | 36 | /** |
73b448bf | 37 | * Class constructor. |
74cf4551 | 38 | */ |
00be9182 | 39 | public function __construct() { |
28ac0bbe | 40 | $this->_autoIncludeIndexedFieldsAsOrderBys = 1; |
f813f78e | 41 | |
6a488035 | 42 | $this->_columns = array( |
9d72cede | 43 | 'civicrm_contact' => array( |
6a488035 | 44 | 'dao' => 'CRM_Contact_DAO_Contact', |
f6164149 | 45 | 'fields' => array_merge(array( |
46 | // CRM-17115 - to avoid changing report output at this stage re-instate | |
47 | // old field name for sort name | |
9d72cede EM |
48 | 'sort_name_linked' => array( |
49 | 'title' => ts('Participant Name'), | |
6a488035 TO |
50 | 'required' => TRUE, |
51 | 'no_repeat' => TRUE, | |
52 | 'dbAlias' => 'contact_civireport.sort_name', | |
c86d4e7c SL |
53 | ), |
54 | ), | |
f6164149 | 55 | $this->getBasicContactFields(), |
56 | array( | |
57 | 'age_at_event' => array( | |
58 | 'title' => ts('Age at Event'), | |
59 | 'dbAlias' => 'TIMESTAMPDIFF(YEAR, contact_civireport.birth_date, event_civireport.start_date)', | |
60 | ), | |
61 | ) | |
6a488035 TO |
62 | ), |
63 | 'grouping' => 'contact-fields', | |
9d72cede EM |
64 | 'order_bys' => array( |
65 | 'sort_name' => array( | |
66 | 'title' => ts('Last Name, First Name'), | |
9b34c9e3 PJ |
67 | 'default' => '1', |
68 | 'default_weight' => '0', | |
69 | 'default_order' => 'ASC', | |
6a488035 | 70 | ), |
70bea8e2 | 71 | 'first_name' => array( |
72 | 'name' => 'first_name', | |
73 | 'title' => ts('First Name'), | |
74 | ), | |
9d72cede | 75 | 'gender_id' => array( |
28ac0bbe DG |
76 | 'name' => 'gender_id', |
77 | 'title' => ts('Gender'), | |
78 | ), | |
9d72cede | 79 | 'birth_date' => array( |
28ac0bbe DG |
80 | 'name' => 'birth_date', |
81 | 'title' => ts('Birth Date'), | |
82 | ), | |
9d72cede | 83 | 'age_at_event' => array( |
28ac0bbe DG |
84 | 'name' => 'age_at_event', |
85 | 'title' => ts('Age at Event'), | |
86 | ), | |
70bea8e2 | 87 | 'contact_type' => array( |
88 | 'title' => ts('Contact Type'), | |
89 | ), | |
90 | 'contact_sub_type' => array( | |
91 | 'title' => ts('Contact Subtype'), | |
92 | ), | |
6a488035 | 93 | ), |
da7ac680 | 94 | 'filters' => CRM_Report_Form::getBasicContactFilters(), |
6a488035 | 95 | ), |
9d72cede | 96 | 'civicrm_email' => array( |
6a488035 | 97 | 'dao' => 'CRM_Core_DAO_Email', |
9d72cede EM |
98 | 'fields' => array( |
99 | 'email' => array( | |
100 | 'title' => ts('Email'), | |
6a488035 TO |
101 | 'no_repeat' => TRUE, |
102 | ), | |
103 | ), | |
104 | 'grouping' => 'contact-fields', | |
9d72cede EM |
105 | 'filters' => array( |
106 | 'email' => array( | |
107 | 'title' => ts('Participant E-mail'), | |
6a488035 TO |
108 | 'operator' => 'like', |
109 | ), | |
110 | ), | |
111 | ), | |
2f5f081b | 112 | ); |
113 | $this->_columns += $this->getAddressColumns(); | |
114 | $this->_columns += array( | |
9d72cede | 115 | 'civicrm_participant' => array( |
6a488035 | 116 | 'dao' => 'CRM_Event_DAO_Participant', |
9d72cede | 117 | 'fields' => array( |
fd6a6828 | 118 | 'participant_id' => array('title' => ts('Participant ID')), |
6a488035 TO |
119 | 'participant_record' => array( |
120 | 'name' => 'id', | |
121 | 'no_display' => TRUE, | |
122 | 'required' => TRUE, | |
123 | ), | |
124 | 'event_id' => array( | |
125 | 'default' => TRUE, | |
126 | 'type' => CRM_Utils_Type::T_STRING, | |
127 | ), | |
9d72cede EM |
128 | 'status_id' => array( |
129 | 'title' => ts('Status'), | |
130 | 'default' => TRUE, | |
6a488035 | 131 | ), |
9d72cede EM |
132 | 'role_id' => array( |
133 | 'title' => ts('Role'), | |
134 | 'default' => TRUE, | |
6a488035 | 135 | ), |
9bf1940a | 136 | 'fee_currency' => array( |
16654cd4 DL |
137 | 'required' => TRUE, |
138 | 'no_display' => TRUE, | |
9bf1940a | 139 | ), |
59545672 J |
140 | 'registered_by_id' => array( |
141 | 'title' => ts('Registered by Participant ID'), | |
142 | ), | |
ebc8dcbc | 143 | 'registered_by_name' => array( |
144 | 'title' => ts('Registered by Participant Name'), | |
d1737d2c | 145 | 'name' => 'registered_by_id', |
ebc8dcbc | 146 | ), |
806f395a J |
147 | 'source' => array( |
148 | 'title' => ts('Source'), | |
149 | ), | |
6a488035 | 150 | 'participant_fee_level' => NULL, |
3f04cb59 | 151 | 'participant_fee_amount' => array('title' => ts('Participant Fee')), |
6a488035 | 152 | 'participant_register_date' => array('title' => ts('Registration Date')), |
9c607320 TC |
153 | 'total_paid' => array( |
154 | 'title' => ts('Total Paid'), | |
86752719 | 155 | 'dbAlias' => 'IFNULL(SUM(ft.total_amount), 0)', |
9c607320 TC |
156 | 'type' => 1024, |
157 | ), | |
158 | 'balance' => array( | |
159 | 'title' => ts('Balance'), | |
86752719 | 160 | 'dbAlias' => 'participant_civireport.fee_amount - IFNULL(SUM(ft.total_amount), 0)', |
9c607320 TC |
161 | 'type' => 1024, |
162 | ), | |
6a488035 TO |
163 | ), |
164 | 'grouping' => 'event-fields', | |
9d72cede | 165 | 'filters' => array( |
2107cde9 CW |
166 | 'event_id' => array( |
167 | 'name' => 'event_id', | |
168 | 'title' => ts('Event'), | |
169 | 'operatorType' => CRM_Report_Form::OP_ENTITYREF, | |
170 | 'type' => CRM_Utils_Type::T_INT, | |
9d72cede | 171 | 'attributes' => array( |
af00ced5 | 172 | 'entity' => 'Event', |
21dfd5f5 | 173 | 'select' => array('minimumInputLength' => 0), |
9d72cede | 174 | ), |
6a488035 TO |
175 | ), |
176 | 'sid' => array( | |
177 | 'name' => 'status_id', | |
178 | 'title' => ts('Participant Status'), | |
8ee006e7 | 179 | 'type' => CRM_Utils_Type::T_INT, |
6a488035 TO |
180 | 'operatorType' => CRM_Report_Form::OP_MULTISELECT, |
181 | 'options' => CRM_Event_PseudoConstant::participantStatus(NULL, NULL, 'label'), | |
182 | ), | |
183 | 'rid' => array( | |
184 | 'name' => 'role_id', | |
185 | 'title' => ts('Participant Role'), | |
186 | 'operatorType' => CRM_Report_Form::OP_MULTISELECT, | |
187 | 'options' => CRM_Event_PseudoConstant::participantRole(), | |
188 | ), | |
189 | 'participant_register_date' => array( | |
fd6a6828 | 190 | 'title' => ts('Registration Date'), |
6a488035 TO |
191 | 'operatorType' => CRM_Report_Form::OP_DATE, |
192 | ), | |
9d72cede EM |
193 | 'fee_currency' => array( |
194 | 'title' => ts('Fee Currency'), | |
9bf1940a | 195 | 'operatorType' => CRM_Report_Form::OP_MULTISELECT, |
196 | 'options' => CRM_Core_OptionGroup::values('currencies_enabled'), | |
d1b0931b | 197 | 'default' => NULL, |
9bf1940a | 198 | 'type' => CRM_Utils_Type::T_STRING, |
199 | ), | |
59545672 J |
200 | 'registered_by_id' => array( |
201 | 'title' => ts('Registered by Participant ID'), | |
202 | 'type' => CRM_Utils_Type::T_STRING, | |
203 | 'operator' => 'like', | |
204 | ), | |
806f395a J |
205 | 'source' => array( |
206 | 'title' => ts('Source'), | |
207 | 'type' => CRM_Utils_Type::T_STRING, | |
208 | 'operator' => 'like', | |
209 | ), | |
6a488035 | 210 | ), |
9d72cede EM |
211 | 'order_bys' => array( |
212 | 'participant_register_date' => array( | |
213 | 'title' => ts('Registration Date'), | |
08ef564e DG |
214 | 'default_weight' => '1', |
215 | 'default_order' => 'ASC', | |
216 | ), | |
9d72cede EM |
217 | 'event_id' => array( |
218 | 'title' => ts('Event'), | |
219 | 'default_weight' => '1', | |
21dfd5f5 | 220 | 'default_order' => 'ASC', |
9d72cede | 221 | ), |
6a488035 TO |
222 | ), |
223 | ), | |
9d72cede | 224 | 'civicrm_phone' => array( |
6a488035 | 225 | 'dao' => 'CRM_Core_DAO_Phone', |
9d72cede EM |
226 | 'fields' => array( |
227 | 'phone' => array( | |
228 | 'title' => ts('Phone'), | |
6a488035 TO |
229 | 'default' => TRUE, |
230 | 'no_repeat' => TRUE, | |
231 | ), | |
232 | ), | |
233 | 'grouping' => 'contact-fields', | |
234 | ), | |
9d72cede | 235 | 'civicrm_event' => array( |
6a488035 | 236 | 'dao' => 'CRM_Event_DAO_Event', |
e1a1a24e | 237 | 'fields' => array( |
d2316af0 ML |
238 | 'event_type_id' => array( |
239 | 'title' => ts('Event Type'), | |
240 | ), | |
241 | 'event_start_date' => array( | |
242 | 'title' => ts('Event Start Date'), | |
243 | ), | |
244 | 'event_end_date' => array( | |
245 | 'title' => ts('Event End Date'), | |
246 | ), | |
6a488035 TO |
247 | ), |
248 | 'grouping' => 'event-fields', | |
9d72cede | 249 | 'filters' => array( |
6a488035 TO |
250 | 'eid' => array( |
251 | 'name' => 'event_type_id', | |
252 | 'title' => ts('Event Type'), | |
8ee006e7 | 253 | 'type' => CRM_Utils_Type::T_INT, |
6a488035 TO |
254 | 'operatorType' => CRM_Report_Form::OP_MULTISELECT, |
255 | 'options' => CRM_Core_OptionGroup::values('event_type'), | |
256 | ), | |
e1a1a24e AH |
257 | 'event_start_date' => array( |
258 | 'title' => ts('Event Start Date'), | |
259 | 'operatorType' => CRM_Report_Form::OP_DATE, | |
260 | ), | |
d2316af0 ML |
261 | 'event_end_date' => array( |
262 | 'title' => ts('Event End Date'), | |
263 | 'operatorType' => CRM_Report_Form::OP_DATE, | |
264 | ), | |
6a488035 | 265 | ), |
9d72cede EM |
266 | 'order_bys' => array( |
267 | 'event_type_id' => array( | |
268 | 'title' => ts('Event Type'), | |
269 | 'default_weight' => '2', | |
21dfd5f5 | 270 | 'default_order' => 'ASC', |
9d72cede | 271 | ), |
94a8422b AH |
272 | 'event_start_date' => array( |
273 | 'title' => ts('Event Start Date'), | |
274 | ), | |
6a488035 TO |
275 | ), |
276 | ), | |
67f14d40 M |
277 | 'civicrm_note' => array( |
278 | 'dao' => 'CRM_Core_DAO_Note', | |
279 | 'fields' => array( | |
280 | 'participant_note' => array( | |
281 | 'name' => 'note', | |
282 | 'title' => ts('Participant Note'), | |
283 | ), | |
284 | ), | |
285 | ), | |
42e9d54f AH |
286 | 'civicrm_contribution' => array( |
287 | 'dao' => 'CRM_Contribute_DAO_Contribution', | |
288 | 'fields' => array( | |
289 | 'contribution_id' => array( | |
290 | 'name' => 'id', | |
9b34c9e3 PJ |
291 | 'no_display' => TRUE, |
292 | 'required' => TRUE, | |
42e9d54f AH |
293 | 'csv_display' => TRUE, |
294 | 'title' => ts('Contribution ID'), | |
295 | ), | |
296 | 'financial_type_id' => array('title' => ts('Financial Type')), | |
297 | 'receive_date' => array('title' => ts('Payment Date')), | |
298 | 'contribution_status_id' => array('title' => ts('Contribution Status')), | |
299 | 'payment_instrument_id' => array('title' => ts('Payment Type')), | |
300 | 'contribution_source' => array( | |
301 | 'name' => 'source', | |
302 | 'title' => ts('Contribution Source'), | |
303 | ), | |
304 | 'currency' => array( | |
305 | 'required' => TRUE, | |
21dfd5f5 | 306 | 'no_display' => TRUE, |
42e9d54f AH |
307 | ), |
308 | 'trxn_id' => NULL, | |
42e9d54f | 309 | 'fee_amount' => array('title' => ts('Transaction Fee')), |
21dfd5f5 | 310 | 'net_amount' => NULL, |
9b34c9e3 | 311 | ), |
42e9d54f AH |
312 | 'grouping' => 'contrib-fields', |
313 | 'filters' => array( | |
314 | 'receive_date' => array( | |
fd6a6828 | 315 | 'title' => ts('Payment Date'), |
42e9d54f AH |
316 | 'operatorType' => CRM_Report_Form::OP_DATE, |
317 | ), | |
9d72cede EM |
318 | 'financial_type_id' => array( |
319 | 'title' => ts('Financial Type'), | |
8ee006e7 | 320 | 'type' => CRM_Utils_Type::T_INT, |
9d72cede EM |
321 | 'operatorType' => CRM_Report_Form::OP_MULTISELECT, |
322 | 'options' => CRM_Contribute_PseudoConstant::financialType(), | |
d79a8b5c | 323 | ), |
9d72cede EM |
324 | 'currency' => array( |
325 | 'title' => ts('Contribution Currency'), | |
326 | 'operatorType' => CRM_Report_Form::OP_MULTISELECT, | |
327 | 'options' => CRM_Core_OptionGroup::values('currencies_enabled'), | |
328 | 'default' => NULL, | |
329 | 'type' => CRM_Utils_Type::T_STRING, | |
d79a8b5c | 330 | ), |
9d72cede EM |
331 | 'payment_instrument_id' => array( |
332 | 'title' => ts('Payment Type'), | |
8ee006e7 | 333 | 'type' => CRM_Utils_Type::T_INT, |
9d72cede EM |
334 | 'operatorType' => CRM_Report_Form::OP_MULTISELECT, |
335 | 'options' => CRM_Contribute_PseudoConstant::paymentInstrument(), | |
d79a8b5c | 336 | ), |
9d72cede EM |
337 | 'contribution_status_id' => array( |
338 | 'title' => ts('Contribution Status'), | |
339 | 'operatorType' => CRM_Report_Form::OP_MULTISELECT, | |
c0aaecf9 | 340 | 'options' => CRM_Contribute_BAO_Contribution::buildOptions('contribution_status_id', 'search'), |
21dfd5f5 | 341 | 'default' => NULL, |
16654cd4 DL |
342 | ), |
343 | ), | |
344 | ), | |
16654cd4 DL |
345 | 'civicrm_line_item' => array( |
346 | 'dao' => 'CRM_Price_DAO_LineItem', | |
347 | 'grouping' => 'priceset-fields', | |
348 | 'filters' => array( | |
349 | 'price_field_value_id' => array( | |
350 | 'name' => 'price_field_value_id', | |
351 | 'title' => ts('Fee Level'), | |
8ee006e7 | 352 | 'type' => CRM_Utils_Type::T_INT, |
d79a8b5c | 353 | 'operatorType' => CRM_Report_Form::OP_MULTISELECT, |
16654cd4 | 354 | 'options' => $this->getPriceLevels(), |
d79a8b5c | 355 | ), |
42e9d54f AH |
356 | ), |
357 | ), | |
6a488035 | 358 | ); |
16654cd4 | 359 | |
16654cd4 DL |
360 | $this->_options = array( |
361 | 'blank_column_begin' => array( | |
362 | 'title' => ts('Blank column at the Begining'), | |
6a488035 TO |
363 | 'type' => 'checkbox', |
364 | ), | |
16654cd4 DL |
365 | 'blank_column_end' => array( |
366 | 'title' => ts('Blank column at the End'), | |
6a488035 TO |
367 | 'type' => 'select', |
368 | 'options' => array( | |
fd6a6828 | 369 | '' => ts('-select-'), |
6a488035 TO |
370 | 1 => ts('One'), |
371 | 2 => ts('Two'), | |
372 | 3 => ts('Three'), | |
373 | ), | |
374 | ), | |
375 | ); | |
f813f78e | 376 | |
f6164149 | 377 | // CRM-17115 avoid duplication of sort_name - would be better to standardise name |
378 | // & behaviour across reports but trying for no change at this point. | |
379 | $this->_columns['civicrm_contact']['fields']['sort_name']['no_display'] = TRUE; | |
380 | ||
58be4d6e | 381 | // If we have campaigns enabled, add those elements to both the fields, filters and sorting |
382 | $this->addCampaignFields('civicrm_participant', FALSE, TRUE); | |
f813f78e | 383 | |
9bf1940a | 384 | $this->_currencyColumn = 'civicrm_participant_fee_currency'; |
6a488035 TO |
385 | parent::__construct(); |
386 | } | |
f813f78e | 387 | |
74cf4551 | 388 | /** |
ae5ffbb7 TO |
389 | * Searches database for priceset values. |
390 | * | |
74cf4551 EM |
391 | * @return array |
392 | */ | |
00be9182 | 393 | public function getPriceLevels() { |
16654cd4 | 394 | $query = " |
b2d8fcb8 | 395 | SELECT CONCAT(cv.label, ' (', ps.title, ' - ', cf.label , ')') label, cv.id |
74558a7b BS |
396 | FROM civicrm_price_field_value cv |
397 | LEFT JOIN civicrm_price_field cf | |
398 | ON cv.price_field_id = cf.id | |
399 | LEFT JOIN civicrm_price_set_entity ce | |
400 | ON ce.price_set_id = cf.price_set_id | |
401 | LEFT JOIN civicrm_price_set ps | |
402 | ON ce.price_set_id = ps.id | |
403 | WHERE ce.entity_table = 'civicrm_event' | |
404 | ORDER BY cv.label | |
16654cd4 DL |
405 | "; |
406 | $dao = CRM_Core_DAO::executeQuery($query); | |
affcc9d2 | 407 | $elements = []; |
16654cd4 DL |
408 | while ($dao->fetch()) { |
409 | $elements[$dao->id] = "$dao->label\n"; | |
410 | } | |
411 | ||
412 | return $elements; | |
ae5ffbb7 | 413 | } |
6a488035 | 414 | |
00be9182 | 415 | public function preProcess() { |
6a488035 TO |
416 | parent::preProcess(); |
417 | } | |
418 | ||
00be9182 | 419 | public function select() { |
affcc9d2 CW |
420 | $select = []; |
421 | $this->_columnHeaders = []; | |
6a488035 TO |
422 | |
423 | //add blank column at the Start | |
9d72cede EM |
424 | if (array_key_exists('options', $this->_params) && |
425 | !empty($this->_params['options']['blank_column_begin']) | |
426 | ) { | |
6a488035 TO |
427 | $select[] = " '' as blankColumnBegin"; |
428 | $this->_columnHeaders['blankColumnBegin']['title'] = '_ _ _ _'; | |
429 | } | |
430 | foreach ($this->_columns as $tableName => $table) { | |
431 | if (array_key_exists('fields', $table)) { | |
432 | foreach ($table['fields'] as $fieldName => $field) { | |
9d72cede EM |
433 | if (!empty($field['required']) || |
434 | !empty($this->_params['fields'][$fieldName]) | |
435 | ) { | |
e8e008f5 AH |
436 | if ($tableName == 'civicrm_contribution') { |
437 | $this->_contribField = TRUE; | |
438 | } | |
9c607320 TC |
439 | if ($fieldName == 'total_paid' || $fieldName == 'balance') { |
440 | $this->_balance = TRUE; | |
0a0cc1a6 | 441 | // modify the select if filtered by fee_level as the from clause |
442 | // already selects the total_amount from civicrm_contribution table | |
6d0e864f | 443 | if (!empty($this->_params['price_field_value_id_value'])) { |
444 | $field['dbAlias'] = str_replace('SUM(ft.total_amount)', 'ft.total_amount', $field['dbAlias']); | |
445 | } | |
9c607320 | 446 | } |
6a488035 TO |
447 | $alias = "{$tableName}_{$fieldName}"; |
448 | $select[] = "{$field['dbAlias']} as $alias"; | |
9c1bc317 CW |
449 | $this->_columnHeaders["{$tableName}_{$fieldName}"]['type'] = $field['type'] ?? NULL; |
450 | $this->_columnHeaders["{$tableName}_{$fieldName}"]['no_display'] = $field['no_display'] ?? NULL; | |
451 | $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = $field['title'] ?? NULL; | |
6a488035 TO |
452 | $this->_selectAliases[] = $alias; |
453 | } | |
454 | } | |
455 | } | |
456 | } | |
457 | //add blank column at the end | |
9c1bc317 | 458 | $blankcols = $this->_params['blank_column_end'] ?? NULL; |
7621050b | 459 | if ($blankcols) { |
6a488035 TO |
460 | for ($i = 1; $i <= $blankcols; $i++) { |
461 | $select[] = " '' as blankColumnEnd_{$i}"; | |
462 | $this->_columnHeaders["blank_{$i}"]['title'] = "_ _ _ _"; | |
463 | } | |
464 | } | |
465 | ||
d1641c51 | 466 | $this->_selectClauses = $select; |
6a488035 TO |
467 | $this->_select = "SELECT " . implode(', ', $select) . " "; |
468 | } | |
469 | ||
74cf4551 EM |
470 | /** |
471 | * @param $fields | |
472 | * @param $files | |
473 | * @param $self | |
474 | * | |
475 | * @return array | |
476 | */ | |
00be9182 | 477 | public static function formRule($fields, $files, $self) { |
affcc9d2 | 478 | $errors = $grouping = []; |
6a488035 TO |
479 | return $errors; |
480 | } | |
481 | ||
00be9182 | 482 | public function from() { |
6a488035 TO |
483 | $this->_from = " |
484 | FROM civicrm_participant {$this->_aliases['civicrm_participant']} | |
f813f78e | 485 | LEFT JOIN civicrm_event {$this->_aliases['civicrm_event']} |
486 | ON ({$this->_aliases['civicrm_event']}.id = {$this->_aliases['civicrm_participant']}.event_id ) AND | |
99f13d8d | 487 | {$this->_aliases['civicrm_event']}.is_template = 0 |
f813f78e | 488 | LEFT JOIN civicrm_contact {$this->_aliases['civicrm_contact']} |
6a488035 TO |
489 | ON ({$this->_aliases['civicrm_participant']}.contact_id = {$this->_aliases['civicrm_contact']}.id ) |
490 | {$this->_aclFrom} | |
e8e008f5 | 491 | "; |
185c566a | 492 | |
493 | $this->joinAddressFromContact(); | |
494 | $this->joinPhoneFromContact(); | |
495 | $this->joinEmailFromContact(); | |
496 | ||
67f14d40 M |
497 | // Include participant note. |
498 | if ($this->isTableSelected('civicrm_note')) { | |
499 | $this->_from .= " | |
500 | LEFT JOIN civicrm_note {$this->_aliases['civicrm_note']} | |
501 | ON ( {$this->_aliases['civicrm_note']}.entity_table = 'civicrm_participant' AND | |
502 | {$this->_aliases['civicrm_participant']}.id = {$this->_aliases['civicrm_note']}.entity_id )"; | |
503 | } | |
504 | ||
e8e008f5 AH |
505 | if ($this->_contribField) { |
506 | $this->_from .= " | |
42e9d54f | 507 | LEFT JOIN civicrm_participant_payment pp |
9b34c9e3 PJ |
508 | ON ({$this->_aliases['civicrm_participant']}.id = pp.participant_id) |
509 | LEFT JOIN civicrm_contribution {$this->_aliases['civicrm_contribution']} | |
510 | ON (pp.contribution_id = {$this->_aliases['civicrm_contribution']}.id) | |
f813f78e | 511 | "; |
e8e008f5 | 512 | } |
6d0e864f | 513 | if (!empty($this->_params['price_field_value_id_value'])) { |
16654cd4 DL |
514 | $this->_from .= " |
515 | LEFT JOIN civicrm_line_item line_item_civireport | |
5fa8e57d | 516 | ON line_item_civireport.entity_table = 'civicrm_participant' AND |
94e8ea9e TC |
517 | line_item_civireport.entity_id = {$this->_aliases['civicrm_participant']}.id AND |
518 | line_item_civireport.qty > 0 | |
16654cd4 DL |
519 | "; |
520 | } | |
9c607320 TC |
521 | if ($this->_balance) { |
522 | $this->_from .= " | |
523 | LEFT JOIN civicrm_entity_financial_trxn eft | |
94e8ea9e | 524 | ON (eft.entity_id = {$this->_aliases['civicrm_contribution']}.id) |
9c607320 | 525 | LEFT JOIN civicrm_financial_trxn ft |
94e8ea9e | 526 | ON (ft.id = eft.financial_trxn_id AND eft.entity_table = 'civicrm_contribution') AND |
86752719 | 527 | (ft.is_payment = 1) |
9c607320 TC |
528 | "; |
529 | } | |
6a488035 TO |
530 | } |
531 | ||
00be9182 | 532 | public function where() { |
affcc9d2 | 533 | $clauses = []; |
6a488035 TO |
534 | foreach ($this->_columns as $tableName => $table) { |
535 | if (array_key_exists('filters', $table)) { | |
536 | foreach ($table['filters'] as $fieldName => $field) { | |
537 | $clause = NULL; | |
538 | ||
539 | if (CRM_Utils_Array::value('type', $field) & CRM_Utils_Type::T_DATE) { | |
9c1bc317 CW |
540 | $relative = $this->_params["{$fieldName}_relative"] ?? NULL; |
541 | $from = $this->_params["{$fieldName}_from"] ?? NULL; | |
542 | $to = $this->_params["{$fieldName}_to"] ?? NULL; | |
6a488035 TO |
543 | |
544 | if ($relative || $from || $to) { | |
545 | $clause = $this->dateClause($field['name'], $relative, $from, $to, $field['type']); | |
546 | } | |
547 | } | |
548 | else { | |
9c1bc317 | 549 | $op = $this->_params["{$fieldName}_op"] ?? NULL; |
6a488035 TO |
550 | |
551 | if ($fieldName == 'rid') { | |
9c1bc317 | 552 | $value = $this->_params["{$fieldName}_value"] ?? NULL; |
6a488035 | 553 | if (!empty($value)) { |
75314e67 | 554 | $operator = ''; |
555 | if ($op == 'notin') { | |
556 | $operator = 'NOT'; | |
557 | } | |
6e987e3a | 558 | |
559 | $regexp = "[[:cntrl:]]*" . implode('[[:>:]]*|[[:<:]]*', $value) . "[[:cntrl:]]*"; | |
560 | $clause = "{$field['dbAlias']} {$operator} REGEXP '{$regexp}'"; | |
6a488035 TO |
561 | } |
562 | $op = NULL; | |
563 | } | |
564 | ||
565 | if ($op) { | |
566 | $clause = $this->whereClause($field, | |
9d72cede EM |
567 | $op, |
568 | CRM_Utils_Array::value("{$fieldName}_value", $this->_params), | |
569 | CRM_Utils_Array::value("{$fieldName}_min", $this->_params), | |
570 | CRM_Utils_Array::value("{$fieldName}_max", $this->_params) | |
6a488035 TO |
571 | ); |
572 | } | |
573 | } | |
574 | ||
575 | if (!empty($clause)) { | |
576 | $clauses[] = $clause; | |
577 | } | |
578 | } | |
579 | } | |
580 | } | |
581 | if (empty($clauses)) { | |
582 | $this->_where = "WHERE {$this->_aliases['civicrm_participant']}.is_test = 0 "; | |
583 | } | |
584 | else { | |
ae5ffbb7 | 585 | $this->_where = "WHERE {$this->_aliases['civicrm_participant']}.is_test = 0 AND " . |
9d72cede | 586 | implode(' AND ', $clauses); |
6a488035 TO |
587 | } |
588 | if ($this->_aclWhere) { | |
589 | $this->_where .= " AND {$this->_aclWhere} "; | |
590 | } | |
591 | } | |
592 | ||
00be9182 | 593 | public function groupBy() { |
b708c08d | 594 | $this->_groupBy = CRM_Contact_BAO_Query::getGroupByFromSelectColumns($this->_selectClauses, "{$this->_aliases['civicrm_participant']}.id"); |
1bfb7a7e DS |
595 | } |
596 | ||
00be9182 | 597 | public function postProcess() { |
6a488035 TO |
598 | // get the acl clauses built before we assemble the query |
599 | $this->buildACLClause($this->_aliases['civicrm_contact']); | |
2f5f081b | 600 | parent::postProcess(); |
6a488035 TO |
601 | } |
602 | ||
7621050b J |
603 | /** |
604 | * @param $rows | |
605 | * @param $entryFound | |
606 | * @param $row | |
100fef9d | 607 | * @param int $rowId |
7621050b J |
608 | * @param $rowNum |
609 | * @param $types | |
ab432335 EM |
610 | * |
611 | * @return bool | |
7621050b | 612 | */ |
9d72cede | 613 | private function _initBasicRow(&$rows, &$entryFound, $row, $rowId, $rowNum, $types) { |
7621050b J |
614 | if (!array_key_exists($rowId, $row)) { |
615 | return FALSE; | |
616 | } | |
617 | ||
618 | $value = $row[$rowId]; | |
619 | if ($value) { | |
620 | $rows[$rowNum][$rowId] = $types[$value]; | |
621 | } | |
622 | $entryFound = TRUE; | |
623 | } | |
624 | ||
74cf4551 | 625 | /** |
ced9bfed EM |
626 | * Alter display of rows. |
627 | * | |
628 | * Iterate through the rows retrieved via SQL and make changes for display purposes, | |
629 | * such as rendering contacts as links. | |
630 | * | |
631 | * @param array $rows | |
632 | * Rows generated by SQL, with an array for each row. | |
74cf4551 | 633 | */ |
00be9182 | 634 | public function alterDisplay(&$rows) { |
6a488035 TO |
635 | $entryFound = FALSE; |
636 | $eventType = CRM_Core_OptionGroup::values('event_type'); | |
9d72cede | 637 | $financialTypes = CRM_Contribute_PseudoConstant::financialType(); |
c3b82060 | 638 | $contributionStatus = CRM_Contribute_PseudoConstant::contributionStatus(NULL, 'label'); |
9b34c9e3 | 639 | $paymentInstruments = CRM_Contribute_PseudoConstant::paymentInstrument(); |
9b34c9e3 | 640 | |
6a488035 TO |
641 | foreach ($rows as $rowNum => $row) { |
642 | // make count columns point to detail report | |
643 | // convert display name to links | |
644 | if (array_key_exists('civicrm_participant_event_id', $row)) { | |
7621050b | 645 | $eventId = $row['civicrm_participant_event_id']; |
1a3fb0ce | 646 | if ($eventId) { |
7621050b | 647 | $rows[$rowNum]['civicrm_participant_event_id'] = CRM_Event_PseudoConstant::event($eventId, FALSE); |
9b34c9e3 | 648 | |
6a488035 | 649 | $url = CRM_Report_Utils_Report::getNextUrl('event/income', |
9d72cede EM |
650 | 'reset=1&force=1&id_op=in&id_value=' . $eventId, |
651 | $this->_absoluteUrl, $this->_id, $this->_drilldownReport | |
6a488035 TO |
652 | ); |
653 | $rows[$rowNum]['civicrm_participant_event_id_link'] = $url; | |
654 | $rows[$rowNum]['civicrm_participant_event_id_hover'] = ts("View Event Income Details for this Event"); | |
655 | } | |
656 | $entryFound = TRUE; | |
657 | } | |
658 | ||
659 | // handle event type id | |
1a3fb0ce | 660 | $this->_initBasicRow($rows, $entryFound, $row, 'civicrm_event_event_type_id', $rowNum, $eventType); |
6a488035 TO |
661 | |
662 | // handle participant status id | |
663 | if (array_key_exists('civicrm_participant_status_id', $row)) { | |
7621050b J |
664 | $statusId = $row['civicrm_participant_status_id']; |
665 | if ($statusId) { | |
666 | $rows[$rowNum]['civicrm_participant_status_id'] = CRM_Event_PseudoConstant::participantStatus($statusId, FALSE, 'label'); | |
6a488035 TO |
667 | } |
668 | $entryFound = TRUE; | |
669 | } | |
670 | ||
671 | // handle participant role id | |
672 | if (array_key_exists('civicrm_participant_role_id', $row)) { | |
7621050b J |
673 | $roleId = $row['civicrm_participant_role_id']; |
674 | if ($roleId) { | |
675 | $roles = explode(CRM_Core_DAO::VALUE_SEPARATOR, $roleId); | |
affcc9d2 | 676 | $roleId = []; |
6a488035 | 677 | foreach ($roles as $role) { |
7621050b | 678 | $roleId[$role] = CRM_Event_PseudoConstant::participantRole($role, FALSE); |
6a488035 | 679 | } |
7621050b | 680 | $rows[$rowNum]['civicrm_participant_role_id'] = implode(', ', $roleId); |
6a488035 TO |
681 | } |
682 | $entryFound = TRUE; | |
683 | } | |
684 | ||
ebc8dcbc | 685 | // Handle registered by name |
686 | if (array_key_exists('civicrm_participant_registered_by_name', $row)) { | |
687 | $registeredById = $row['civicrm_participant_registered_by_name']; | |
688 | if ($registeredById) { | |
689 | $registeredByContactId = CRM_Core_DAO::getFieldValue("CRM_Event_DAO_Participant", $registeredById, 'contact_id', 'id'); | |
690 | $rows[$rowNum]['civicrm_participant_registered_by_name'] = CRM_Contact_BAO_Contact::displayName($registeredByContactId); | |
691 | $rows[$rowNum]['civicrm_participant_registered_by_name_link'] = CRM_Utils_System::url('civicrm/contact/view', 'reset=1&cid=' . $registeredByContactId, $this->_absoluteUrl); | |
692 | $rows[$rowNum]['civicrm_participant_registered_by_name_hover'] = ts('View Contact Summary for Contact that registered the participant.'); | |
693 | } | |
694 | } | |
695 | ||
696 | // Handle value seperator in Fee Level | |
6a488035 | 697 | if (array_key_exists('civicrm_participant_participant_fee_level', $row)) { |
7621050b J |
698 | $feeLevel = $row['civicrm_participant_participant_fee_level']; |
699 | if ($feeLevel) { | |
700 | CRM_Event_BAO_Participant::fixEventLevel($feeLevel); | |
701 | $rows[$rowNum]['civicrm_participant_participant_fee_level'] = $feeLevel; | |
6a488035 TO |
702 | } |
703 | $entryFound = TRUE; | |
704 | } | |
705 | ||
706 | // Convert display name to link | |
9c1bc317 CW |
707 | $displayName = $row['civicrm_contact_sort_name_linked'] ?? NULL; |
708 | $cid = $row['civicrm_contact_id'] ?? NULL; | |
709 | $id = $row['civicrm_participant_participant_record'] ?? NULL; | |
7621050b J |
710 | |
711 | if ($displayName && $cid && $id) { | |
6a488035 | 712 | $url = CRM_Report_Utils_Report::getNextUrl('contact/detail', |
9d72cede EM |
713 | "reset=1&force=1&id_op=eq&id_value=$cid", |
714 | $this->_absoluteUrl, $this->_id, $this->_drilldownReport | |
6a488035 TO |
715 | ); |
716 | ||
717 | $viewUrl = CRM_Utils_System::url("civicrm/contact/view/participant", | |
9d72cede | 718 | "reset=1&id=$id&cid=$cid&action=view&context=participant" |
6a488035 TO |
719 | ); |
720 | ||
721 | $contactTitle = ts('View Contact Details'); | |
722 | $participantTitle = ts('View Participant Record'); | |
723 | ||
724 | $rows[$rowNum]['civicrm_contact_sort_name_linked'] = "<a title='$contactTitle' href=$url>$displayName</a>"; | |
223313a9 J |
725 | // Add a "View" link to the participant record if this isn't a CSV/PDF/printed document. |
726 | if ($this->_outputMode !== 'csv' && $this->_outputMode !== 'pdf' && $this->_outputMode !== 'print') { | |
9d72cede EM |
727 | $rows[$rowNum]['civicrm_contact_sort_name_linked'] .= |
728 | "<span style='float: right;'><a title='$participantTitle' href=$viewUrl>" . | |
729 | ts('View') . "</a></span>"; | |
6a488035 TO |
730 | } |
731 | $entryFound = TRUE; | |
732 | } | |
733 | ||
4fabb77b | 734 | // Convert campaign_id to campaign title |
58be4d6e | 735 | $this->_initBasicRow($rows, $entryFound, $row, 'civicrm_participant_campaign_id', $rowNum, $this->campaigns); |
4fabb77b | 736 | |
9b34c9e3 | 737 | // handle contribution status |
ea2ef428 | 738 | $this->_initBasicRow($rows, $entryFound, $row, 'civicrm_contribution_contribution_status_id', $rowNum, $contributionStatus); |
9b34c9e3 PJ |
739 | |
740 | // handle payment instrument | |
ea2ef428 | 741 | $this->_initBasicRow($rows, $entryFound, $row, 'civicrm_contribution_payment_instrument_id', $rowNum, $paymentInstruments); |
9b34c9e3 PJ |
742 | |
743 | // handle financial type | |
ea2ef428 | 744 | $this->_initBasicRow($rows, $entryFound, $row, 'civicrm_contribution_financial_type_id', $rowNum, $financialTypes); |
9b34c9e3 | 745 | |
e4e2ff09 | 746 | $entryFound = $this->alterDisplayContactFields($row, $rows, $rowNum, 'event/participantListing', 'View Event Income Details') ? TRUE : $entryFound; |
9b34c9e3 | 747 | |
2f5f081b | 748 | $entryFound = $this->alterDisplayAddressFields($row, $rows, $rowNum, 'event/ParticipantListing', 'List all participant(s) for this ') ? TRUE : $entryFound; |
28ac0bbe | 749 | |
6a488035 TO |
750 | // skip looking further in rows, if first row itself doesn't |
751 | // have the column we need | |
752 | if (!$entryFound) { | |
753 | break; | |
754 | } | |
755 | } | |
756 | } | |
96025800 | 757 | |
16654cd4 | 758 | } |