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