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