Commit | Line | Data |
---|---|---|
6a488035 TO |
1 | <?php |
2 | /* | |
3 | +--------------------------------------------------------------------+ | |
4 | | CiviCRM version 4.3 | | |
5 | +--------------------------------------------------------------------+ | |
6 | | Copyright CiviCRM LLC (c) 2004-2013 | | |
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 | +--------------------------------------------------------------------+ | |
26 | */ | |
27 | ||
28 | /** | |
29 | * | |
30 | * @package CRM | |
31 | * @copyright CiviCRM LLC (c) 2004-2013 | |
32 | * $Id$ | |
33 | * | |
34 | */ | |
35 | class CRM_Report_Form_Extended extends CRM_Report_Form { | |
36 | protected $_addressField = FALSE; | |
37 | ||
38 | protected $_emailField = FALSE; | |
39 | ||
40 | protected $_summary = NULL; | |
41 | ||
42 | protected $_customGroupExtends = array(); | |
43 | protected $_baseTable = 'civicrm_contact'; | |
44 | ||
45 | function __construct() { | |
46 | parent::__construct(); | |
47 | } | |
48 | ||
49 | function preProcess() { | |
50 | parent::preProcess(); | |
51 | } | |
52 | ||
53 | function select() { | |
54 | parent::select(); | |
55 | } | |
56 | ||
57 | ||
58 | /* | |
59 | * From clause build where baseTable & fromClauses are defined | |
60 | */ | |
61 | function from() { | |
62 | if (!empty($this->_baseTable)) { | |
63 | $this->buildACLClause($this->_aliases['civicrm_contact']); | |
64 | $this->_from = "FROM {$this->_baseTable} {$this->_aliases[$this->_baseTable]}"; | |
65 | $availableClauses = $this->getAvailableJoins(); | |
66 | foreach ($this->fromClauses() as $fromClause) { | |
67 | $fn = $availableClauses[$fromClause]['callback']; | |
68 | $this->$fn(); | |
69 | } | |
70 | if (strstr($this->_from, 'civicrm_contact')) { | |
71 | $this->_from .= $this->_aclFrom; | |
72 | } | |
73 | } | |
74 | } | |
75 | ||
76 | /* | |
77 | * Define any from clauses in use (child classes to override) | |
78 | */ | |
79 | function fromClauses() { | |
80 | return array(); | |
81 | } | |
82 | ||
83 | function groupBy() { | |
84 | parent::groupBy(); | |
85 | //@todo - need to re-visit this - bad behaviour from pa | |
86 | if ($this->_groupBy == 'GROUP BY') { | |
87 | $this->_groupBY = NULL; | |
88 | } | |
89 | // if a stat field has been selected the do a group by | |
90 | if (!empty($this->_statFields) && empty($this->_groupBy)) { | |
91 | $this->_groupBy[] = $this->_aliases[$this->_baseTable] . ".id"; | |
92 | } | |
93 | //@todo - this should be in the parent function or at parent level - perhaps build query should do this? | |
94 | if (!empty($this->_groupBy) && is_array($this->_groupBy)) { | |
95 | $this->_groupBy = 'GROUP BY ' . implode(',', $this->_groupBy); | |
96 | } | |
97 | } | |
98 | ||
99 | function orderBy() { | |
100 | parent::orderBy(); | |
101 | } | |
102 | ||
103 | function statistics(&$rows) { | |
104 | return parent::statistics($rows); | |
105 | } | |
106 | ||
107 | function postProcess() { | |
108 | if (!empty($this->_aclTable) && CRM_Utils_Array::value($this->_aclTable, $this->_aliases)) { | |
109 | $this->buildACLClause($this->_aliases[$this->_aclTable]); | |
110 | } | |
111 | parent::postProcess(); | |
112 | } | |
113 | ||
114 | function alterDisplay(&$rows) { | |
115 | parent::alterDisplay($rows); | |
116 | ||
117 | //THis is all generic functionality which can hopefully go into the parent class | |
118 | // it introduces the option of defining an alter display function as part of the column definition | |
119 | // @tod tidy up the iteration so it happens in this function | |
120 | list($firstRow) = $rows; | |
121 | // no result to alter | |
122 | if (empty($firstRow)) { | |
123 | return; | |
124 | } | |
125 | $selectedFields = array_keys($firstRow); | |
126 | ||
127 | $alterfunctions = $altermap = array(); | |
128 | foreach ($this->_columns as $tablename => $table) { | |
129 | if (array_key_exists('fields', $table)) { | |
130 | foreach ($table['fields'] as $field => $specs) { | |
131 | if (in_array($tablename . '_' . $field, $selectedFields) && array_key_exists('alter_display', $specs)) { | |
132 | $alterfunctions[$tablename . '_' . $field] = $specs['alter_display']; | |
133 | $altermap[$tablename . '_' . $field] = $field; | |
134 | } | |
135 | } | |
136 | } | |
137 | } | |
138 | if (empty($alterfunctions)) { | |
139 | // - no manipulation to be done | |
140 | return; | |
141 | } | |
142 | ||
143 | foreach ($rows as $index => & $row) { | |
144 | foreach ($row as $selectedfield => $value) { | |
145 | if (array_key_exists($selectedfield, $alterfunctions)) { | |
146 | $rows[$index][$selectedfield] = $this->$alterfunctions[$selectedfield]($value, $row, $selectedfield, $altermap[$selectedfield]); | |
147 | } | |
148 | } | |
149 | } | |
150 | } | |
151 | ||
152 | function getLineItemColumns() { | |
153 | return array( | |
154 | 'civicrm_line_item' => | |
155 | array( | |
156 | 'dao' => 'CRM_Price_BAO_LineItem', | |
157 | 'fields' => | |
158 | array( | |
159 | 'qty' => | |
160 | array('title' => ts('Quantity'), | |
161 | 'type' => CRM_Utils_Type::T_INT, | |
162 | 'statistics' => | |
163 | array('sum' => ts('Total Quantity Selected')), | |
164 | ), | |
165 | 'unit_price' => | |
166 | array('title' => ts('Unit Price'), | |
167 | ), | |
168 | 'line_total' => | |
169 | array('title' => ts('Line Total'), | |
170 | 'type' => CRM_Utils_Type::T_MONEY, | |
171 | 'statistics' => | |
172 | array('sum' => ts('Total of Line Items')), | |
173 | ), | |
174 | ), | |
175 | 'participant_count' => | |
176 | array('title' => ts('Participant Count'), | |
177 | 'statistics' => | |
178 | array('sum' => ts('Total Participants')), | |
179 | ), | |
180 | 'filters' => | |
181 | array( | |
182 | 'qty' => | |
183 | array('title' => ts('Quantity'), | |
184 | 'type' => CRM_Utils_Type::T_INT, | |
185 | 'operator' => CRM_Report_Form::OP_INT, | |
186 | ), | |
187 | ), | |
188 | 'group_bys' => | |
189 | array( | |
190 | 'price_field_id' => | |
191 | array('title' => ts('Price Field'), | |
192 | ), | |
193 | 'price_field_value_id' => | |
194 | array('title' => ts('Price Field Option'), | |
195 | ), | |
196 | 'line_item_id' => | |
197 | array('title' => ts('Individual Line Item'), | |
198 | 'name' => 'id', | |
199 | ), | |
200 | ), | |
201 | ), | |
202 | ); | |
203 | } | |
204 | ||
205 | function getPriceFieldValueColumns() { | |
206 | return array( | |
207 | 'civicrm_price_field_value' => | |
208 | array( | |
9da8dc8c | 209 | 'dao' => 'CRM_Price_BAO_PriceFieldValue', |
6a488035 TO |
210 | 'fields' => array( |
211 | 'price_field_value_label' => | |
212 | array('title' => ts('Price Field Value Label'), | |
213 | 'name' => 'label', | |
214 | ), | |
215 | ), | |
216 | 'filters' => | |
217 | array( | |
218 | 'price_field_value_label' => | |
219 | array('title' => ts('Price Fields Value Label'), | |
220 | 'type' => CRM_Utils_Type::T_STRING, | |
221 | 'operator' => 'like', | |
222 | 'name' => 'label', | |
223 | ), | |
224 | ), | |
225 | 'order_bys' => | |
226 | array( | |
227 | 'label' => | |
228 | array('title' => ts('Price Field Value Label'), | |
229 | ), | |
230 | ), | |
231 | 'group_bys' => | |
232 | //note that we have a requirement to group by label such that all 'Promo book' lines | |
233 | // are grouped together across price sets but there may be a separate need to group | |
234 | // by id so that entries in one price set are distinct from others. Not quite sure what | |
235 | // to call the distinction for end users benefit | |
236 | array( | |
237 | 'price_field_value_label' => | |
238 | array('title' => ts('Price Field Value Label'), | |
239 | 'name' => 'label', | |
240 | ), | |
241 | ), | |
242 | ), | |
243 | ); | |
244 | } | |
245 | ||
246 | function getPriceFieldColumns() { | |
247 | return array( | |
248 | 'civicrm_price_field' => | |
249 | array( | |
9da8dc8c | 250 | 'dao' => 'CRM_Price_BAO_PriceField', |
6a488035 TO |
251 | 'fields' => |
252 | array( | |
253 | 'price_field_label' => | |
254 | array('title' => ts('Price Field Label'), | |
255 | 'name' => 'label', | |
256 | ), | |
257 | ), | |
258 | 'filters' => | |
259 | array( | |
260 | 'price_field_label' => | |
261 | array('title' => ts('Price Field Label'), | |
262 | 'type' => CRM_Utils_Type::T_STRING, | |
263 | 'operator' => 'like', | |
264 | 'name' => 'label', | |
265 | ), | |
266 | ), | |
267 | 'order_bys' => | |
268 | array( | |
269 | 'price_field_label' => | |
270 | array('title' => ts('Price Field Label'), | |
271 | 'name' => 'label', | |
272 | ), | |
273 | ), | |
274 | 'group_bys' => | |
275 | array( | |
276 | 'price_field_label' => | |
277 | array('title' => ts('Price Field Label'), | |
278 | 'name' => 'label', | |
279 | ), | |
280 | ), | |
281 | ), | |
282 | ); | |
283 | } | |
284 | ||
285 | function getParticipantColumns() { | |
286 | static $_events = array(); | |
287 | if (!isset($_events['all'])) { | |
288 | CRM_Core_PseudoConstant::populate($_events['all'], 'CRM_Event_DAO_Event', FALSE, 'title', 'is_active', "is_template IS NULL OR is_template = 0", 'end_date DESC'); | |
289 | } | |
290 | return array( | |
291 | 'civicrm_participant' => | |
292 | array( | |
293 | 'dao' => 'CRM_Event_DAO_Participant', | |
294 | 'fields' => | |
295 | array('participant_id' => array('title' => 'Participant ID'), | |
296 | 'participant_record' => array( | |
297 | 'name' => 'id', | |
298 | 'title' => 'Participant Id', | |
299 | ), | |
300 | 'event_id' => array('title' => ts('Event ID'), | |
301 | 'type' => CRM_Utils_Type::T_STRING, | |
302 | 'alter_display' => 'alterEventID', | |
303 | ), | |
304 | 'status_id' => array('title' => ts('Status'), | |
305 | 'alter_display' => 'alterParticipantStatus', | |
306 | ), | |
307 | 'role_id' => array('title' => ts('Role'), | |
308 | 'alter_display' => 'alterParticipantRole', | |
309 | ), | |
310 | 'participant_fee_level' => NULL, | |
311 | 'participant_fee_amount' => NULL, | |
312 | 'participant_register_date' => array('title' => ts('Registration Date')), | |
313 | ), | |
314 | 'grouping' => 'event-fields', | |
315 | 'filters' => | |
316 | array( | |
317 | 'event_id' => array('name' => 'event_id', | |
318 | 'title' => ts('Event'), | |
319 | 'operatorType' => CRM_Report_Form::OP_MULTISELECT, | |
320 | 'options' => $_events['all'], | |
321 | ), | |
322 | 'sid' => array( | |
323 | 'name' => 'status_id', | |
324 | 'title' => ts('Participant Status'), | |
325 | 'operatorType' => CRM_Report_Form::OP_MULTISELECT, | |
326 | 'options' => CRM_Event_PseudoConstant::participantStatus(NULL, NULL, 'label'), | |
327 | ), | |
328 | 'rid' => array( | |
329 | 'name' => 'role_id', | |
330 | 'title' => ts('Participant Role'), | |
331 | 'operatorType' => CRM_Report_Form::OP_MULTISELECT, | |
332 | 'options' => CRM_Event_PseudoConstant::participantRole(), | |
333 | ), | |
334 | 'participant_register_date' => array( | |
335 | 'title' => ' Registration Date', | |
336 | 'operatorType' => CRM_Report_Form::OP_DATE, | |
337 | ), | |
338 | ), | |
339 | 'order_bys' => | |
340 | array( | |
341 | 'event_id' => | |
342 | array('title' => ts('Event'), 'default_weight' => '1', 'default_order' => 'ASC'), | |
343 | ), | |
344 | 'group_bys' => | |
345 | array( | |
346 | 'event_id' => | |
347 | array('title' => ts('Event')), | |
348 | ), | |
349 | ), | |
350 | ); | |
351 | } | |
352 | ||
353 | function getMembershipColumns() { | |
354 | return array( | |
355 | 'civicrm_membership' => array( | |
356 | 'dao' => 'CRM_Member_DAO_Membership', | |
357 | 'grouping' => 'member-fields', | |
358 | 'fields' => array( | |
359 | 'membership_type_id' => array( | |
360 | 'title' => 'Membership Type', | |
361 | 'required' => TRUE, | |
362 | 'alter_display' => 'alterMembershipTypeID', | |
363 | ), | |
364 | 'status_id' => array( | |
365 | 'title' => 'Membership Status', | |
366 | 'required' => TRUE, | |
367 | 'alter_display' => 'alterMembershipStatusID', | |
368 | ), | |
369 | 'join_date' => NULL, | |
370 | 'start_date' => array( | |
371 | 'title' => ts('Current Cycle Start Date'), | |
372 | ), | |
373 | 'end_date' => array( | |
374 | 'title' => ts('Current Membership Cycle End Date'), | |
375 | ), | |
376 | ), | |
377 | 'group_bys' => array( | |
378 | 'membership_type_id' => array( | |
379 | 'title' => ts('Membership Type'), | |
380 | ), | |
381 | ), | |
382 | 'filters' => array( | |
383 | 'join_date' => array( | |
384 | 'type' => CRM_Utils_Type::T_DATE, | |
385 | 'operatorType' => CRM_Report_Form::OP_DATE, | |
386 | ), | |
387 | ), | |
388 | ), | |
389 | ); | |
390 | } | |
391 | ||
392 | function getMembershipTypeColumns() { | |
393 | return array( | |
394 | 'civicrm_membership_type' => array( | |
395 | 'dao' => 'CRM_Member_DAO_MembershipType', | |
396 | 'grouping' => 'member-fields', | |
397 | 'filters' => array( | |
398 | 'gid' => array( | |
399 | 'name' => 'id', | |
400 | 'title' => ts('Membership Types'), | |
401 | 'operatorType' => CRM_Report_Form::OP_MULTISELECT, | |
402 | 'type' => CRM_Utils_Type::T_INT + CRM_Utils_Type::T_ENUM, | |
403 | 'options' => CRM_Member_PseudoConstant::membershipType(), | |
404 | ), | |
405 | ), | |
406 | ), | |
407 | ); | |
408 | } | |
409 | ||
410 | function getEventColumns() { | |
411 | return array( | |
412 | 'civicrm_event' => array( | |
413 | 'dao' => 'CRM_Event_DAO_Event', | |
414 | 'fields' => | |
415 | array( | |
416 | 'id' => array( | |
417 | 'no_display' => TRUE, | |
418 | 'required' => TRUE, | |
419 | ), | |
420 | 'title' => array('title' => ts('Event Title'), | |
421 | 'required' => TRUE, | |
422 | ), | |
423 | 'event_type_id' => array('title' => ts('Event Type'), | |
424 | 'required' => TRUE, | |
425 | 'alter_display' => 'alterEventType', | |
426 | ), | |
427 | 'fee_label' => array('title' => ts('Fee Label')), | |
428 | 'event_start_date' => array('title' => ts('Event Start Date'), | |
429 | ), | |
430 | 'event_end_date' => array('title' => ts('Event End Date')), | |
431 | 'max_participants' => array('title' => ts('Capacity'), | |
432 | 'type' => CRM_Utils_Type::T_INT, | |
433 | ), | |
434 | ), | |
435 | 'grouping' => 'event-fields', | |
436 | 'filters' => array( | |
437 | 'event_type_id' => array( | |
438 | 'name' => 'event_type_id', | |
439 | 'title' => ts('Event Type'), | |
440 | 'operatorType' => CRM_Report_Form::OP_MULTISELECT, | |
441 | 'options' => CRM_Core_OptionGroup::values('event_type'), | |
442 | ), | |
443 | 'event_title' => array( | |
444 | 'name' => 'title', | |
445 | 'title' => ts('Event Title'), | |
446 | 'operatorType' => CRM_Report_Form::OP_STRING, | |
447 | ), | |
448 | ), | |
449 | 'order_bys' => array( | |
450 | 'event_type_id' => array( | |
451 | 'title' => ts('Event Type'), | |
452 | 'default_weight' => '2', | |
453 | 'default_order' => 'ASC', | |
454 | ), | |
455 | ), | |
456 | 'group_bys' => array( | |
457 | 'event_type_id' => array( | |
458 | 'title' => ts('Event Type'), | |
459 | ), | |
460 | ), | |
461 | ), | |
462 | ); | |
463 | } | |
464 | ||
465 | function getContributionColumns() { | |
466 | return array( | |
467 | 'civicrm_contribution' => | |
468 | array( | |
469 | 'dao' => 'CRM_Contribute_DAO_Contribution', | |
470 | 'fields' => | |
471 | array( | |
472 | 'contribution_id' => array( | |
473 | 'name' => 'id', | |
474 | ), | |
475 | 'financial_type_id' => array('title' => ts('Financial Type'), | |
476 | 'default' => TRUE, | |
477 | 'alter_display' => 'alterContributionType', | |
478 | ), | |
479 | 'payment_instrument_id' => array('title' => ts('Payment Instrument'), | |
480 | 'alter_display' => 'alterPaymentType', | |
481 | ), | |
482 | 'source' => array('title' => 'Contribution Source'), | |
483 | 'trxn_id' => NULL, | |
484 | 'receive_date' => array('default' => TRUE), | |
485 | 'receipt_date' => NULL, | |
486 | 'fee_amount' => NULL, | |
487 | 'net_amount' => NULL, | |
488 | 'total_amount' => array('title' => ts('Amount'), | |
489 | 'statistics' => | |
490 | array('sum' => ts('Total Amount')), | |
491 | 'type' => CRM_Utils_Type::T_MONEY, | |
492 | ), | |
493 | ), | |
494 | 'filters' => | |
495 | array( | |
496 | 'receive_date' => | |
497 | array('operatorType' => CRM_Report_Form::OP_DATE), | |
498 | 'financial_type_id' => | |
499 | array('title' => ts('Financial Type'), | |
500 | 'operatorType' => CRM_Report_Form::OP_MULTISELECT, | |
501 | 'options' => CRM_Contribute_PseudoConstant::financialType(), | |
502 | ), | |
503 | 'payment_instrument_id' => | |
504 | array('title' => ts('Payment Type'), | |
505 | 'operatorType' => CRM_Report_Form::OP_MULTISELECT, | |
506 | 'options' => CRM_Contribute_PseudoConstant::paymentInstrument(), | |
507 | ), | |
508 | 'contribution_status_id' => | |
509 | array('title' => ts('Contribution Status'), | |
510 | 'operatorType' => CRM_Report_Form::OP_MULTISELECT, | |
511 | 'options' => CRM_Contribute_PseudoConstant::contributionStatus(), | |
512 | ), | |
513 | 'total_amount' => | |
514 | array('title' => ts('Contribution Amount')), | |
515 | ), | |
516 | 'order_bys' => | |
517 | array( | |
518 | 'payment_instrument_id' => | |
519 | array('title' => ts('Payment Instrument'), | |
520 | ), | |
521 | 'financial_type_id' => | |
522 | array('title' => ts('Financial Type'), | |
523 | ), | |
524 | ), | |
525 | 'group_bys' => | |
526 | array('financial_type_id' => | |
527 | array('title' => ts('Financial Type')), | |
528 | 'payment_instrument_id' => | |
529 | array('title' => ts('Payment Instrument')), | |
530 | 'contribution_id' => | |
531 | array('title' => ts('Individual Contribution'), | |
532 | 'name' => 'id', | |
533 | ), | |
534 | 'source' => array('title' => 'Contribution Source'), | |
535 | ), | |
536 | 'grouping' => 'contribution-fields', | |
537 | ), | |
538 | ); | |
539 | } | |
540 | ||
541 | function getContactColumns() { | |
542 | return array( | |
543 | 'civicrm_contact' => array( | |
544 | 'dao' => 'CRM_Contact_DAO_Contact', | |
545 | 'fields' => array( | |
546 | 'display_name' => array( | |
547 | 'title' => ts('Contact Name'), | |
548 | ), | |
549 | 'id' => array( | |
550 | 'title' => ts('Contact ID'), | |
551 | 'alter_display' => 'alterContactID', | |
552 | ), | |
553 | 'first_name' => array( | |
554 | 'title' => ts('First Name'), | |
555 | ), | |
556 | 'last_name' => array( | |
557 | 'title' => ts('Last Name'), | |
558 | ), | |
559 | 'nick_name' => array( | |
560 | 'title' => ts('Nickname'), | |
561 | 'alter_display' => 'alterNickname', | |
562 | ), | |
563 | ), | |
564 | 'filters' => array( | |
565 | 'id' => array( | |
566 | 'title' => ts('Contact ID'), | |
567 | ) | |
568 | , | |
569 | 'sort_name' => array( | |
570 | 'title' => ts('Contact Name'), | |
571 | ), | |
572 | ), | |
573 | 'grouping' => 'contact-fields', | |
574 | 'order_bys' => array( | |
575 | 'sort_name' => array( | |
576 | 'title' => ts('Last Name, First Name'), | |
577 | 'default' => '1', | |
578 | 'default_weight' => '0', | |
579 | 'default_order' => 'ASC', | |
580 | ), | |
581 | ), | |
582 | ), | |
583 | ); | |
584 | } | |
585 | ||
586 | function getCaseColumns() { | |
587 | return array( | |
588 | 'civicrm_case' => array( | |
589 | 'dao' => 'CRM_Case_DAO_Case', | |
590 | 'fields' => array( | |
591 | 'id' => array( | |
592 | 'title' => ts('Case ID'), | |
593 | 'required' => false | |
594 | ), | |
595 | 'subject' => array( | |
596 | 'title' => ts('Case Subject'), | |
597 | 'default' => true | |
598 | ), | |
599 | 'status_id' => array( | |
600 | 'title' => ts('Status'), | |
601 | 'default' => true | |
602 | ), | |
603 | 'case_type_id' => array( | |
604 | 'title' => ts('Case Type'), | |
605 | 'default' => true | |
606 | ), | |
607 | 'case_start_date' => array( | |
608 | 'title' => ts('Case Start Date'), | |
609 | 'name' => 'start_date', | |
610 | 'default' => true | |
611 | ), | |
612 | 'case_end_date' => array( | |
613 | 'title' => ts('Case End Date'), | |
614 | 'name' => 'end_date', | |
615 | 'default' => true | |
616 | ), | |
617 | 'case_duration' => array( | |
618 | 'name' => 'duration', | |
619 | 'title' => ts('Duration (Days)'), | |
620 | 'default' => false | |
621 | ), | |
622 | 'case_is_deleted' => array( | |
623 | 'name' => 'is_deleted', | |
624 | 'title' => ts('Case Deleted?'), | |
625 | 'default' => false, | |
626 | 'type' => CRM_Utils_Type::T_INT | |
627 | ) | |
628 | ), | |
629 | 'filters' => array( | |
630 | 'case_start_date' => array( | |
631 | 'title' => ts('Case Start Date'), | |
632 | 'operatorType' => CRM_Report_Form::OP_DATE, | |
633 | 'type' => CRM_Utils_Type::T_DATE, | |
634 | 'name' => 'start_date', | |
635 | ), | |
636 | 'case_end_date' => array( | |
637 | 'title' => ts('Case End Date'), | |
638 | 'operatorType' => CRM_Report_Form::OP_DATE, | |
639 | 'type' => CRM_Utils_Type::T_DATE, | |
640 | 'name' => 'end_date' | |
641 | ), | |
642 | 'case_type_id' => array( | |
643 | 'title' => ts('Case Type'), | |
644 | 'operatorType' => CRM_Report_Form::OP_MULTISELECT, | |
645 | 'options' => $this->case_types | |
646 | ), | |
647 | 'case_status_id' => array( | |
648 | 'title' => ts('Case Status'), | |
649 | 'operatorType' => CRM_Report_Form::OP_MULTISELECT, | |
650 | 'options' => $this->case_statuses, | |
651 | 'name' => 'status_id' | |
652 | ), | |
653 | 'case_is_deleted' => array( | |
654 | 'title' => ts('Case Deleted?'), | |
655 | 'type' => CRM_Report_Form::OP_INT, | |
656 | 'operatorType' => CRM_Report_Form::OP_SELECT, | |
657 | 'options' => $this->deleted_labels, | |
658 | 'default' => 0, | |
659 | 'name' => 'is_deleted' | |
660 | ) | |
661 | ) | |
662 | ) | |
663 | ); | |
664 | } | |
665 | ||
666 | /* | |
667 | * function for adding address fields to construct function in reports | |
668 | * @param array $options Options for the report | |
669 | * - prefix prefix to add (e.g. 'honor' when getting address details for honor contact | |
670 | * - prefix_label optional prefix lable eg. "Honoree " for front end | |
671 | * - group_by enable these fields for group by - default false | |
672 | * - order_by enable these fields for order by | |
673 | * - filters enable these fields for filtering | |
674 | * - defaults - (is this working?) values to pre-populate | |
675 | * @return array address fields for construct clause | |
676 | */ | |
677 | function getAddressColumns($options = array()) { | |
678 | $defaultOptions = array( | |
679 | 'prefix' => '', | |
680 | 'prefix_label' => '', | |
681 | 'group_by' => false, | |
682 | 'order_by' => true, | |
683 | 'filters' => true, | |
684 | 'defaults' => array( | |
685 | 'country_id' => TRUE | |
686 | ), | |
687 | ); | |
688 | ||
689 | $options = array_merge($defaultOptions,$options); | |
690 | ||
691 | $addressFields = array( | |
692 | $options['prefix'] . 'civicrm_address' => array( | |
693 | 'dao' => 'CRM_Core_DAO_Address', | |
694 | 'name' => 'civicrm_address', | |
695 | 'alias' => $options['prefix'] . 'civicrm_address', | |
696 | 'fields' => array( | |
697 | $options['prefix'] . 'name' => array( | |
698 | 'title' => ts($options['prefix_label'] . 'Address Name'), | |
699 | 'default' => CRM_Utils_Array::value('name', $options['defaults'], FALSE), | |
700 | 'name' => 'name', | |
701 | ), | |
702 | $options['prefix'] . 'street_address' => array( | |
703 | 'title' => ts($options['prefix_label'] . 'Street Address'), | |
704 | 'default' => CRM_Utils_Array::value('street_address', $options['defaults'], FALSE), | |
705 | 'name' => 'street_address', | |
706 | ), | |
707 | $options['prefix'] . 'supplemental_address_1' => array( | |
708 | 'title' => ts($options['prefix_label'] . 'Supplementary Address Field 1'), | |
709 | 'default' => CRM_Utils_Array::value('supplemental_address_1', $options['defaults'], FALSE), | |
710 | 'name' => 'supplemental_address_1', | |
711 | ), | |
712 | $options['prefix'] . 'supplemental_address_2' => array( | |
713 | 'title' => ts($options['prefix_label'] . 'Supplementary Address Field 2'), | |
714 | 'default' => CRM_Utils_Array::value('supplemental_address_2', $options['defaults'], FALSE), | |
715 | 'name' => 'supplemental_address_2', | |
716 | ), | |
717 | $options['prefix'] . 'street_number' => array( | |
718 | 'name' => 'street_number', | |
719 | 'title' => ts($options['prefix_label'] . 'Street Number'), | |
720 | 'type' => 1, | |
721 | 'default' => CRM_Utils_Array::value('street_number', $options['defaults'], FALSE), | |
722 | 'name' => 'street_number', | |
723 | ), | |
724 | $options['prefix'] . 'street_name' => array( | |
725 | 'name' => 'street_name', | |
726 | 'title' => ts($options['prefix_label'] . 'Street Name'), | |
727 | 'type' => 1, | |
728 | 'default' => CRM_Utils_Array::value('street_name', $options['defaults'], FALSE), | |
729 | 'name' => 'street_name', | |
730 | ), | |
731 | $options['prefix'] . 'street_unit' => array( | |
732 | 'name' => 'street_unit', | |
733 | 'title' => ts($options['prefix_label'] . 'Street Unit'), | |
734 | 'type' => 1, | |
735 | 'default' => CRM_Utils_Array::value('street_unit', $options['defaults'], FALSE), | |
736 | 'name' => 'street_unit', | |
737 | ), | |
738 | $options['prefix'] . 'city' => array( | |
739 | 'title' => ts($options['prefix_label'] . 'City'), | |
740 | 'default' => CRM_Utils_Array::value('city', $options['defaults'], FALSE), | |
741 | 'name' => 'city', | |
742 | ), | |
743 | $options['prefix'] . 'postal_code' => array( | |
744 | 'title' => ts($options['prefix_label'] . 'Postal Code'), | |
745 | 'default' => CRM_Utils_Array::value('postal_code', $options['defaults'], FALSE), | |
746 | 'name' => 'postal_code', | |
747 | ), | |
748 | $options['prefix'] . 'county_id' => array( | |
749 | 'title' => ts($options['prefix_label'] . 'County'), | |
750 | 'default' => CRM_Utils_Array::value('county_id', $options['defaults'], FALSE), | |
751 | 'alter_display' => 'alterCountyID', | |
752 | 'name' => 'county_id', | |
753 | ), | |
754 | $options['prefix'] . 'state_province_id' => array( | |
755 | 'title' => ts($options['prefix_label'] . 'State/Province'), | |
756 | 'default' => CRM_Utils_Array::value('state_province_id', $options['defaults'], FALSE), | |
757 | 'alter_display' => 'alterStateProvinceID', | |
758 | 'name' => 'state_province_id', | |
759 | ), | |
760 | $options['prefix'] . 'country_id' => array( | |
761 | 'title' => ts($options['prefix_label'] . 'Country'), | |
762 | 'default' => CRM_Utils_Array::value('country_id', $options['defaults'], FALSE), | |
763 | 'alter_display' => 'alterCountryID', | |
764 | 'name' => 'country_id', | |
765 | ), | |
766 | ), | |
767 | 'grouping' => 'location-fields', | |
768 | ), | |
769 | ); | |
770 | ||
771 | if ($options['filters']) { | |
772 | $addressFields[$options['prefix'] .'civicrm_address']['filters'] = array( | |
773 | $options['prefix'] . 'street_number' => array( | |
774 | 'title' => ts($options['prefix_label'] . 'Street Number'), | |
775 | 'type' => 1, | |
776 | 'name' => 'street_number', | |
777 | ), | |
778 | $options['prefix'] . 'street_name' => array( | |
779 | 'title' => ts($options['prefix_label'] . 'Street Name'), | |
780 | 'name' => $options['prefix'] . 'street_name', | |
781 | 'operator' => 'like', | |
782 | ), | |
783 | $options['prefix'] . 'postal_code' => array( | |
784 | 'title' => ts($options['prefix_label'] . 'Postal Code'), | |
785 | 'type' => 1, | |
786 | 'name' => 'postal_code', | |
787 | ), | |
788 | $options['prefix'] . 'city' => array( | |
789 | 'title' => ts($options['prefix_label'] . 'City'), | |
790 | 'operator' => 'like', | |
791 | 'name' => 'city', | |
792 | ), | |
793 | $options['prefix'] . 'county_id' => array( | |
794 | 'name' => 'county_id', | |
795 | 'title' => ts($options['prefix_label'] . 'County'), | |
796 | 'type' => CRM_Utils_Type::T_INT, | |
797 | 'operatorType' => CRM_Report_Form::OP_MULTISELECT, | |
798 | 'options' => CRM_Core_PseudoConstant::county(), | |
799 | ), | |
800 | $options['prefix'] . 'state_province_id' => array( | |
801 | 'name' => 'state_province_id', | |
802 | 'title' => ts($options['prefix_label'] . 'State/Province'), | |
803 | 'type' => CRM_Utils_Type::T_INT, | |
804 | 'operatorType' => CRM_Report_Form::OP_MULTISELECT, | |
805 | 'options' => CRM_Core_PseudoConstant::stateProvince(), | |
806 | ), | |
807 | $options['prefix'] . 'country_id' => array( | |
808 | 'name' => 'country_id', | |
809 | 'title' => ts($options['prefix_label'] . 'Country'), | |
810 | 'type' => CRM_Utils_Type::T_INT, | |
811 | 'operatorType' => CRM_Report_Form::OP_MULTISELECT, | |
812 | 'options' => CRM_Core_PseudoConstant::country(), | |
813 | ), | |
814 | ); | |
815 | } | |
816 | ||
817 | if ($options['order_by']) { | |
818 | $addressFields[$options['prefix'] . 'civicrm_address']['order_bys'] = array( | |
819 | $options['prefix'] . 'street_name' => array( | |
820 | 'title' => ts($options['prefix_label'] . 'Street Name'), | |
821 | 'name' => 'street_name', | |
822 | ), | |
823 | $options['prefix'] . 'street_number' => array( | |
824 | 'title' => ts($options['prefix_label'] . 'Odd / Even Street Number'), | |
825 | 'name' => 'street_number', | |
826 | ), | |
827 | $options['prefix'] . 'street_address' => array( | |
828 | 'title' => ts($options['prefix_label'] . 'Street Address'), | |
829 | 'name' => 'street_address', | |
830 | ), | |
831 | $options['prefix'] . 'city' => array( | |
832 | 'title' => ts($options['prefix_label'] . 'City'), | |
833 | 'name' => 'city', | |
834 | ), | |
835 | $options['prefix'] . 'postal_code' => array( | |
836 | 'title' => ts($options['prefix_label'] . 'Post Code'), | |
837 | 'name' => 'postal_code', | |
838 | ), | |
839 | ); | |
840 | } | |
841 | ||
842 | if ($options['group_by']) { | |
843 | $addressFields['civicrm_address']['group_bys'] = array( | |
844 | $options['prefix'] . 'street_address' => array( | |
845 | 'title' => ts($options['prefix_label'] . 'Street Address'), | |
846 | 'name' => 'street_address', | |
847 | ), | |
848 | $options['prefix'] . 'city' => array( | |
849 | 'title' => ts($options['prefix_label'] . 'City'), | |
850 | 'name' => 'city', | |
851 | ), | |
852 | $options['prefix'] . 'postal_code' => array( | |
853 | 'title' => ts($options['prefix_label'] . 'Post Code'), | |
854 | 'name' => 'postal_code', | |
855 | ), | |
856 | $options['prefix'] . 'state_province_id' => array( | |
857 | 'title' => ts($options['prefix_label'] . 'State/Province'), | |
858 | 'name' => 'state_province_id', | |
859 | ), | |
860 | $options['prefix'] . 'country_id' => array( | |
861 | 'title' => ts($options['prefix_label'] . 'Country'), | |
862 | 'name' => 'country_id', | |
863 | ), | |
864 | $options['prefix'] . 'county_id' => array( | |
865 | 'title' => ts($options['prefix_label'] . 'County'), | |
866 | 'name' => 'county_id', | |
867 | ), | |
868 | ); | |
869 | } | |
870 | return $addressFields; | |
871 | } | |
872 | ||
873 | /* | |
874 | * Get Information about advertised Joins | |
875 | */ | |
876 | function getAvailableJoins() { | |
877 | return array( | |
878 | 'priceFieldValue_from_lineItem' => array( | |
879 | 'leftTable' => 'civicrm_line_item', | |
880 | 'rightTable' => 'civicrm_price_field_value', | |
881 | 'callback' => 'joinPriceFieldValueFromLineItem', | |
882 | ), | |
883 | 'priceField_from_lineItem' => array( | |
884 | 'leftTable' => 'civicrm_line_item', | |
885 | 'rightTable' => 'civicrm_price_field', | |
886 | 'callback' => 'joinPriceFieldFromLineItem', | |
887 | ), | |
888 | 'participant_from_lineItem' => array( | |
889 | 'leftTable' => 'civicrm_line_item', | |
890 | 'rightTable' => 'civicrm_participant', | |
891 | 'callback' => 'joinParticipantFromLineItem', | |
892 | ), | |
893 | 'contribution_from_lineItem' => array( | |
894 | 'leftTable' => 'civicrm_line_item', | |
895 | 'rightTable' => 'civicrm_contribution', | |
896 | 'callback' => 'joinContributionFromLineItem', | |
897 | ), | |
898 | 'membership_from_lineItem' => array( | |
899 | 'leftTable' => 'civicrm_line_item', | |
900 | 'rightTable' => 'civicrm_membership', | |
901 | 'callback' => 'joinMembershipFromLineItem', | |
902 | ), | |
903 | 'contribution_from_participant' => array( | |
904 | 'leftTable' => 'civicrm_participant', | |
905 | 'rightTable' => 'civicrm_contribution', | |
906 | 'callback' => 'joinContributionFromParticipant', | |
907 | ), | |
908 | 'contribution_from_membership' => array( | |
909 | 'leftTable' => 'civicrm_membership', | |
910 | 'rightTable' => 'civicrm_contribution', | |
911 | 'callback' => 'joinContributionFromMembership', | |
912 | ), | |
913 | 'membership_from_contribution' => array( | |
914 | 'leftTable' => 'civicrm_contribution', | |
915 | 'rightTable' => 'civicrm_membership', | |
916 | 'callback' => 'joinMembershipFromContribution', | |
917 | ), | |
918 | 'membershipType_from_membership' => array( | |
919 | 'leftTable' => 'civicrm_membership', | |
920 | 'rightTable' => 'civicrm_membership_type', | |
921 | 'callback' => 'joinMembershipTypeFromMembership', | |
922 | ), | |
923 | 'lineItem_from_contribution' => array( | |
924 | 'leftTable' => 'civicrm_contribution', | |
925 | 'rightTable' => 'civicrm_line_item', | |
926 | 'callback' => 'joinLineItemFromContribution', | |
927 | ), | |
928 | 'lineItem_from_membership' => array( | |
929 | 'leftTable' => 'civicrm_membership', | |
930 | 'rightTable' => 'civicrm_line_item', | |
931 | 'callback' => 'joinLineItemFromMembership', | |
932 | ), | |
933 | 'contact_from_participant' => array( | |
934 | 'leftTable' => 'civicrm_participant', | |
935 | 'rightTable' => 'civicrm_contact', | |
936 | 'callback' => 'joinContactFromParticipant', | |
937 | ), | |
938 | 'contact_from_membership' => array( | |
939 | 'leftTable' => 'civicrm_membership', | |
940 | 'rightTable' => 'civicrm_contact', | |
941 | 'callback' => 'joinContactFromMembership', | |
942 | ), | |
943 | 'contact_from_contribution' => array( | |
944 | 'leftTable' => 'civicrm_contribution', | |
945 | 'rightTable' => 'civicrm_contact', | |
946 | 'callback' => 'joinContactFromContribution', | |
947 | ), | |
948 | 'event_from_participant' => array( | |
949 | 'leftTable' => 'civicrm_participant', | |
950 | 'rightTable' => 'civicrm_event', | |
951 | 'callback' => 'joinEventFromParticipant', | |
952 | ), | |
953 | 'address_from_contact' => array( | |
954 | 'leftTable' => 'civicrm_contact', | |
955 | 'rightTable' => 'civicrm_address', | |
956 | 'callback' => 'joinAddressFromContact', | |
957 | ), | |
958 | ); | |
959 | } | |
960 | ||
961 | /* | |
962 | * Add join from contact table to address. Prefix will be added to both tables | |
963 | * as it's assumed you are using it to get address of a secondary contact | |
964 | */ | |
965 | function joinAddressFromContact( $prefix = '') { | |
966 | $this->_from .= " LEFT JOIN civicrm_address {$this->_aliases[$prefix . 'civicrm_address']} | |
967 | ON {$this->_aliases[$prefix . 'civicrm_address']}.contact_id = {$this->_aliases[$prefix . 'civicrm_contact']}.id"; | |
968 | } | |
969 | ||
970 | function joinPriceFieldValueFromLineItem() { | |
971 | $this->_from .= " LEFT JOIN civicrm_price_field_value {$this->_aliases['civicrm_price_field_value']} | |
972 | ON {$this->_aliases['civicrm_line_item']}.price_field_value_id = {$this->_aliases['civicrm_price_field_value']}.id"; | |
973 | } | |
974 | ||
975 | function joinPriceFieldFromLineItem() { | |
976 | $this->_from .= " | |
977 | LEFT JOIN civicrm_price_field {$this->_aliases['civicrm_price_field']} | |
978 | ON {$this->_aliases['civicrm_line_item']}.price_field_id = {$this->_aliases['civicrm_price_field']}.id | |
979 | "; | |
980 | } | |
981 | ||
982 | /* | |
983 | * Define join from line item table to participant table | |
984 | */ | |
985 | function joinParticipantFromLineItem() { | |
986 | $this->_from .= " LEFT JOIN civicrm_participant {$this->_aliases['civicrm_participant']} | |
987 | ON ( {$this->_aliases['civicrm_line_item']}.entity_id = {$this->_aliases['civicrm_participant']}.id | |
988 | AND {$this->_aliases['civicrm_line_item']}.entity_table = 'civicrm_participant') | |
989 | "; | |
990 | } | |
991 | ||
992 | /* | |
993 | * Define join from line item table to Membership table. Seems to be still via contribution | |
994 | * as the entity. Have made 'inner' to restrict does that make sense? | |
995 | */ | |
996 | function joinMembershipFromLineItem() { | |
997 | $this->_from .= " INNER JOIN civicrm_contribution {$this->_aliases['civicrm_contribution']} | |
998 | ON ( {$this->_aliases['civicrm_line_item']}.entity_id = {$this->_aliases['civicrm_contribution']}.id | |
999 | AND {$this->_aliases['civicrm_line_item']}.entity_table = 'civicrm_contribution') | |
1000 | LEFT JOIN civicrm_membership_payment pp | |
1001 | ON {$this->_aliases['civicrm_contribution']}.id = pp.contribution_id | |
1002 | LEFT JOIN civicrm_membership {$this->_aliases['civicrm_membership']} | |
1003 | ON pp.membership_id = {$this->_aliases['civicrm_membership']}.id | |
1004 | "; | |
1005 | } | |
1006 | ||
1007 | /* | |
1008 | * Define join from Participant to Contribution table | |
1009 | */ | |
1010 | function joinContributionFromParticipant() { | |
1011 | $this->_from .= " LEFT JOIN civicrm_participant_payment pp | |
1012 | ON {$this->_aliases['civicrm_participant']}.id = pp.participant_id | |
1013 | LEFT JOIN civicrm_contribution {$this->_aliases['civicrm_contribution']} | |
1014 | ON pp.contribution_id = {$this->_aliases['civicrm_contribution']}.id | |
1015 | "; | |
1016 | } | |
1017 | ||
1018 | /* | |
1019 | * Define join from Membership to Contribution table | |
1020 | */ | |
1021 | function joinContributionFromMembership() { | |
1022 | $this->_from .= " LEFT JOIN civicrm_membership_payment pp | |
1023 | ON {$this->_aliases['civicrm_membership']}.id = pp.membership_id | |
1024 | LEFT JOIN civicrm_contribution {$this->_aliases['civicrm_contribution']} | |
1025 | ON pp.contribution_id = {$this->_aliases['civicrm_contribution']}.id | |
1026 | "; | |
1027 | } | |
1028 | ||
1029 | function joinParticipantFromContribution() { | |
1030 | $this->_from .= " LEFT JOIN civicrm_participant_payment pp | |
1031 | ON {$this->_aliases['civicrm_contribution']}.id = pp.contribution_id | |
1032 | LEFT JOIN civicrm_participant {$this->_aliases['civicrm_participant']} | |
1033 | ON pp.participant_id = {$this->_aliases['civicrm_participant']}.id"; | |
1034 | } | |
1035 | ||
1036 | function joinMembershipFromContribution() { | |
1037 | $this->_from .= " | |
1038 | LEFT JOIN civicrm_membership_payment pp | |
1039 | ON {$this->_aliases['civicrm_contribution']}.id = pp.contribution_id | |
1040 | LEFT JOIN civicrm_membership {$this->_aliases['civicrm_membership']} | |
1041 | ON pp.membership_id = {$this->_aliases['civicrm_membership']}.id"; | |
1042 | } | |
1043 | ||
1044 | function joinMembershipTypeFromMembership() { | |
1045 | $this->_from .= " | |
1046 | LEFT JOIN civicrm_membership_type {$this->_aliases['civicrm_membership_type']} | |
1047 | ON {$this->_aliases['civicrm_membership']}.membership_type_id = {$this->_aliases['civicrm_membership_type']}.id | |
1048 | "; | |
1049 | } | |
1050 | ||
1051 | function joinContributionFromLineItem() { | |
1052 | ||
1053 | // this can be stored as a temp table & indexed for more speed. Not done at this state. | |
1054 | // another option is to cache it but I haven't tried to put that code in yet (have used it before for one hour caching | |
1055 | $this->_from .= " LEFT JOIN (SELECT line_item_civireport.id as lid, contribution_civireport_direct.* | |
1056 | FROM civicrm_line_item line_item_civireport | |
1057 | LEFT JOIN civicrm_contribution contribution_civireport_direct | |
1058 | ON (line_item_civireport.line_total > 0 AND line_item_civireport.entity_id = contribution_civireport_direct.id AND line_item_civireport.entity_table = 'civicrm_contribution') | |
1059 | ||
1060 | ||
1061 | WHERE contribution_civireport_direct.id IS NOT NULL | |
1062 | ||
1063 | UNION SELECT line_item_civireport.id as lid, contribution_civireport.* | |
1064 | FROM civicrm_line_item line_item_civireport | |
1065 | LEFT JOIN civicrm_participant participant_civireport | |
1066 | ON (line_item_civireport.line_total > 0 AND line_item_civireport.entity_id = participant_civireport.id AND line_item_civireport.entity_table = 'civicrm_participant') | |
1067 | ||
1068 | LEFT JOIN civicrm_participant_payment pp | |
1069 | ON participant_civireport.id = pp.participant_id | |
1070 | LEFT JOIN civicrm_contribution contribution_civireport | |
1071 | ON pp.contribution_id = contribution_civireport.id | |
1072 | ||
1073 | UNION SELECT line_item_civireport.id as lid,contribution_civireport.* | |
1074 | FROM civicrm_line_item line_item_civireport | |
1075 | LEFT JOIN civicrm_membership membership_civireport | |
1076 | ON (line_item_civireport.line_total > 0 AND line_item_civireport.entity_id =membership_civireport.id AND line_item_civireport.entity_table = 'civicrm_membership') | |
1077 | ||
1078 | LEFT JOIN civicrm_membership_payment pp | |
1079 | ON membership_civireport.id = pp.membership_id | |
1080 | LEFT JOIN civicrm_contribution contribution_civireport | |
1081 | ON pp.contribution_id = contribution_civireport.id | |
1082 | ) as {$this->_aliases['civicrm_contribution']} | |
1083 | ON {$this->_aliases['civicrm_contribution']}.lid = {$this->_aliases['civicrm_line_item']}.id | |
1084 | "; | |
1085 | } | |
1086 | ||
1087 | function joinLineItemFromContribution() { | |
1088 | ||
1089 | // this can be stored as a temp table & indexed for more speed. Not done at this stage. | |
1090 | // another option is to cache it but I haven't tried to put that code in yet (have used it before for one hour caching | |
1091 | $this->_from .= " | |
1092 | LEFT JOIN ( | |
1093 | SELECT contribution_civireport_direct.id AS contid, line_item_civireport.* | |
1094 | FROM civicrm_contribution contribution_civireport_direct | |
1095 | LEFT JOIN civicrm_line_item line_item_civireport ON (line_item_civireport.line_total > 0 AND line_item_civireport.entity_id = contribution_civireport_direct.id AND line_item_civireport.entity_table = 'civicrm_contribution') | |
1096 | WHERE line_item_civireport.id IS NOT NULL | |
1097 | ||
1098 | UNION | |
1099 | SELECT contribution_civireport_direct.id AS contid, line_item_civireport.* | |
1100 | FROM civicrm_contribution contribution_civireport_direct | |
1101 | LEFT JOIN civicrm_participant_payment pp ON contribution_civireport_direct.id = pp.contribution_id | |
1102 | LEFT JOIN civicrm_participant p ON pp.participant_id = p.id | |
1103 | LEFT JOIN civicrm_line_item line_item_civireport ON (line_item_civireport.line_total > 0 AND line_item_civireport.entity_id = p.id AND line_item_civireport.entity_table = 'civicrm_participant') | |
1104 | WHERE line_item_civireport.id IS NOT NULL | |
1105 | ||
1106 | UNION | |
1107 | ||
1108 | SELECT contribution_civireport_direct.id AS contid, line_item_civireport.* | |
1109 | FROM civicrm_contribution contribution_civireport_direct | |
1110 | LEFT JOIN civicrm_membership_payment pp ON contribution_civireport_direct.id = pp.contribution_id | |
1111 | LEFT JOIN civicrm_membership p ON pp.membership_id = p.id | |
1112 | LEFT JOIN civicrm_line_item line_item_civireport ON (line_item_civireport.line_total > 0 AND line_item_civireport.entity_id = p.id AND line_item_civireport.entity_table = 'civicrm_membership') | |
2f4c2f5d | 1113 | WHERE line_item_civireport.id IS NOT NULL |
6a488035 TO |
1114 | ) as {$this->_aliases['civicrm_line_item']} |
1115 | ON {$this->_aliases['civicrm_line_item']}.contid = {$this->_aliases['civicrm_contribution']}.id | |
1116 | ||
1117 | ||
1118 | "; | |
1119 | } | |
1120 | ||
1121 | function joinLineItemFromMembership() { | |
1122 | ||
1123 | // this can be stored as a temp table & indexed for more speed. Not done at this stage. | |
1124 | // another option is to cache it but I haven't tried to put that code in yet (have used it before for one hour caching | |
1125 | $this->_from .= " | |
1126 | LEFT JOIN ( | |
1127 | SELECT contribution_civireport_direct.id AS contid, line_item_civireport.* | |
1128 | FROM civicrm_contribution contribution_civireport_direct | |
1129 | LEFT JOIN civicrm_line_item line_item_civireport | |
1130 | ON (line_item_civireport.line_total > 0 AND line_item_civireport.entity_id = contribution_civireport_direct.id AND line_item_civireport.entity_table = 'civicrm_contribution') | |
1131 | ||
2f4c2f5d | 1132 | WHERE line_item_civireport.id IS NOT NULL |
6a488035 TO |
1133 | |
1134 | UNION | |
1135 | ||
1136 | SELECT contribution_civireport_direct.id AS contid, line_item_civireport.* | |
1137 | FROM civicrm_contribution contribution_civireport_direct | |
1138 | LEFT JOIN civicrm_membership_payment pp ON contribution_civireport_direct.id = pp.contribution_id | |
1139 | LEFT JOIN civicrm_membership p ON pp.membership_id = p.id | |
1140 | LEFT JOIN civicrm_line_item line_item_civireport ON (line_item_civireport.line_total > 0 AND line_item_civireport.entity_id = p.id AND line_item_civireport.entity_table = 'civicrm_membership') | |
2f4c2f5d | 1141 | WHERE line_item_civireport.id IS NOT NULL |
6a488035 TO |
1142 | ) as {$this->_aliases['civicrm_line_item']} |
1143 | ON {$this->_aliases['civicrm_line_item']}.contid = {$this->_aliases['civicrm_contribution']}.id | |
1144 | "; | |
1145 | } | |
1146 | ||
1147 | function joinContactFromParticipant() { | |
1148 | $this->_from .= " LEFT JOIN civicrm_contact {$this->_aliases['civicrm_contact']} | |
1149 | ON {$this->_aliases['civicrm_participant']}.contact_id = {$this->_aliases['civicrm_contact']}.id"; | |
1150 | } | |
1151 | ||
1152 | function joinContactFromMembership() { | |
1153 | $this->_from .= " LEFT JOIN civicrm_contact {$this->_aliases['civicrm_contact']} | |
1154 | ON {$this->_aliases['civicrm_membership']}.contact_id = {$this->_aliases['civicrm_contact']}.id"; | |
1155 | } | |
1156 | ||
1157 | function joinContactFromContribution() { | |
1158 | $this->_from .= " LEFT JOIN civicrm_contact {$this->_aliases['civicrm_contact']} | |
1159 | ON {$this->_aliases['civicrm_contribution']}.contact_id = {$this->_aliases['civicrm_contact']}.id"; | |
1160 | } | |
1161 | ||
1162 | function joinEventFromParticipant() { | |
1163 | $this->_from .= " LEFT JOIN civicrm_event {$this->_aliases['civicrm_event']} | |
1164 | ON ({$this->_aliases['civicrm_event']}.id = {$this->_aliases['civicrm_participant']}.event_id ) AND | |
1165 | ({$this->_aliases['civicrm_event']}.is_template IS NULL OR | |
1166 | {$this->_aliases['civicrm_event']}.is_template = 0)"; | |
1167 | } | |
1168 | ||
1169 | /* | |
1170 | * Retrieve text for financial type from pseudoconstant | |
1171 | */ | |
1172 | function alterNickName($value, &$row) { | |
1173 | if(empty($row['civicrm_contact_id'])){ | |
1174 | return; | |
1175 | } | |
1176 | $contactID = $row['civicrm_contact_id']; | |
1177 | return "<div id=contact-{$contactID} class='crm-entity'> | |
1178 | <span class='crm-editable crmf-nick_name crm-editable-enabled' data-action='create'> | |
1179 | " . $value . "</span></div>"; | |
1180 | } | |
1181 | ||
1182 | /* | |
1183 | * Retrieve text for contribution type from pseudoconstant | |
1184 | */ | |
1185 | function alterContributionType($value, &$row) { | |
b914f4e8 | 1186 | return is_string(CRM_Contribute_PseudoConstant::financialType($value, FALSE)) ? CRM_Contribute_PseudoConstant::financialType($value, FALSE) : ''; |
6a488035 TO |
1187 | } |
1188 | ||
1189 | /* | |
1190 | * Retrieve text for contribution status from pseudoconstant | |
1191 | */ | |
1192 | function alterContributionStatus($value, &$row) { | |
1193 | return CRM_Contribute_PseudoConstant::contributionStatus($value); | |
1194 | } | |
1195 | ||
1196 | /* | |
1197 | * Retrieve text for payment instrument from pseudoconstant | |
1198 | */ | |
1199 | function alterEventType($value, &$row) { | |
1200 | return CRM_Event_PseudoConstant::eventType($value); | |
1201 | } | |
1202 | ||
1203 | function alterEventID($value, &$row) { | |
1204 | return is_string(CRM_Event_PseudoConstant::event($value, FALSE)) ? CRM_Event_PseudoConstant::event($value, FALSE) : ''; | |
1205 | } | |
1206 | ||
1207 | function alterMembershipTypeID($value, &$row) { | |
1208 | return is_string(CRM_Member_PseudoConstant::membershipType($value, FALSE)) ? CRM_Member_PseudoConstant::membershipType($value, FALSE) : ''; | |
1209 | } | |
1210 | ||
1211 | function alterMembershipStatusID($value, &$row) { | |
1212 | return is_string(CRM_Member_PseudoConstant::membershipStatus($value, FALSE)) ? CRM_Member_PseudoConstant::membershipStatus($value, FALSE) : ''; | |
1213 | } | |
1214 | ||
1215 | function alterCountryID($value, &$row, $selectedfield, $criteriaFieldName) { | |
1216 | $url = CRM_Utils_System::url(CRM_Utils_System::currentPath(), "reset=1&force=1&{$criteriaFieldName}_op=in&{$criteriaFieldName}_value={$value}", $this->_absoluteUrl); | |
1217 | $row[$selectedfield . '_link'] = $url; | |
10a5be27 | 1218 | $row[$selectedfield . '_hover'] = ts("%1 for this country.", array(1 => $value)); |
6a488035 TO |
1219 | $countries = CRM_Core_PseudoConstant::country($value, FALSE); |
1220 | if(!is_array($countries)){ | |
1221 | return $countries; | |
1222 | } | |
1223 | } | |
1224 | ||
1225 | function alterCountyID($value, &$row,$selectedfield, $criteriaFieldName) { | |
1226 | $url = CRM_Utils_System::url(CRM_Utils_System::currentPath(), "reset=1&force=1&{$criteriaFieldName}_op=in&{$criteriaFieldName}_value={$value}", $this->_absoluteUrl); | |
1227 | $row[$selectedfield . '_link'] = $url; | |
10a5be27 | 1228 | $row[$selectedfield . '_hover'] = ts("%1 for this county.", array(1 => $value)); |
6a488035 TO |
1229 | $counties = CRM_Core_PseudoConstant::county($value, FALSE); |
1230 | if(!is_array($counties)){ | |
1231 | return $counties; | |
1232 | } | |
1233 | } | |
1234 | ||
1235 | function alterStateProvinceID($value, &$row, $selectedfield, $criteriaFieldName) { | |
1236 | $url = CRM_Utils_System::url(CRM_Utils_System::currentPath(), "reset=1&force=1&{$criteriaFieldName}_op=in&{$criteriaFieldName}_value={$value}", $this->_absoluteUrl); | |
1237 | $row[$selectedfield . '_link'] = $url; | |
10a5be27 | 1238 | $row[$selectedfield . '_hover'] = ts("%1 for this state.", array(1 => $value)); |
6a488035 TO |
1239 | |
1240 | $states = CRM_Core_PseudoConstant::stateProvince($value, FALSE); | |
1241 | if(!is_array($states)){ | |
1242 | return $states; | |
1243 | } | |
1244 | } | |
1245 | ||
1246 | function alterContactID($value, &$row, $fieldname) { | |
1247 | $row[$fieldname . '_link'] = CRM_Utils_System::url("civicrm/contact/view", 'reset=1&cid=' . $value, $this->_absoluteUrl); | |
1248 | return $value; | |
1249 | } | |
1250 | ||
1251 | function alterParticipantStatus($value) { | |
1252 | if (empty($value)) { | |
1253 | return; | |
1254 | } | |
1255 | return CRM_Event_PseudoConstant::participantStatus($value, FALSE, 'label'); | |
1256 | } | |
1257 | ||
1258 | function alterParticipantRole($value) { | |
1259 | if (empty($value)) { | |
1260 | return; | |
1261 | } | |
1262 | $roles = explode(CRM_Core_DAO::VALUE_SEPARATOR, $value); | |
1263 | $value = array(); | |
1264 | foreach ($roles as $role) { | |
1265 | $value[$role] = CRM_Event_PseudoConstant::participantRole($role, FALSE); | |
1266 | } | |
1267 | return implode(', ', $value); | |
1268 | } | |
1269 | ||
1270 | function alterPaymentType($value) { | |
1271 | $paymentInstruments = CRM_Contribute_PseudoConstant::paymentInstrument(); | |
1272 | return $paymentInstruments[$value]; | |
1273 | } | |
1274 | } | |
1275 |