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