3 +--------------------------------------------------------------------+
4 | CiviCRM version 4.3 |
5 +--------------------------------------------------------------------+
6 | Copyright CiviCRM LLC (c) 2004-2013 |
7 +--------------------------------------------------------------------+
8 | This file is a part of CiviCRM. |
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. |
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. |
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 +--------------------------------------------------------------------+
31 * @copyright CiviCRM LLC (c) 2004-2013
35 class CRM_Report_Form_Extended
extends CRM_Report_Form
{
36 protected $_addressField = FALSE;
38 protected $_emailField = FALSE;
40 protected $_summary = NULL;
42 protected $_customGroupExtends = array();
43 protected $_baseTable = 'civicrm_contact';
45 function __construct() {
46 parent
::__construct();
49 function preProcess() {
59 * From clause build where baseTable & fromClauses are defined
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'];
70 if (strstr($this->_from
, 'civicrm_contact')) {
71 $this->_from
.= $this->_aclFrom
;
77 * Define any from clauses in use (child classes to override)
79 function fromClauses() {
85 //@todo - need to re-visit this - bad behaviour from pa
86 if ($this->_groupBy
== 'GROUP BY') {
87 $this->_groupBY
= NULL;
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";
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
);
103 function statistics(&$rows) {
104 return parent
::statistics($rows);
107 function postProcess() {
108 if (!empty($this->_aclTable
) && CRM_Utils_Array
::value($this->_aclTable
, $this->_aliases
)) {
109 $this->buildACLClause($this->_aliases
[$this->_aclTable
]);
111 parent
::postProcess();
114 function alterDisplay(&$rows) {
115 parent
::alterDisplay($rows);
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)) {
125 $selectedFields = array_keys($firstRow);
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;
138 if (empty($alterfunctions)) {
139 // - no manipulation to be done
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]);
152 function getLineItemColumns() {
154 'civicrm_line_item' =>
156 'dao' => 'CRM_Price_BAO_LineItem',
160 array('title' => ts('Quantity'),
161 'type' => CRM_Utils_Type
::T_INT
,
163 array('sum' => ts('Total Quantity Selected')),
166 array('title' => ts('Unit Price'),
169 array('title' => ts('Line Total'),
170 'type' => CRM_Utils_Type
::T_MONEY
,
172 array('sum' => ts('Total of Line Items')),
175 'participant_count' =>
176 array('title' => ts('Participant Count'),
178 array('sum' => ts('Total Participants')),
183 array('title' => ts('Quantity'),
184 'type' => CRM_Utils_Type
::T_INT
,
185 'operator' => CRM_Report_Form
::OP_INT
,
191 array('title' => ts('Price Field'),
193 'price_field_value_id' =>
194 array('title' => ts('Price Field Option'),
197 array('title' => ts('Individual Line Item'),
205 function getPriceFieldValueColumns() {
207 'civicrm_price_field_value' =>
209 'dao' => 'CRM_Price_BAO_PriceFieldValue',
211 'price_field_value_label' =>
212 array('title' => ts('Price Field Value Label'),
218 'price_field_value_label' =>
219 array('title' => ts('Price Fields Value Label'),
220 'type' => CRM_Utils_Type
::T_STRING
,
221 'operator' => 'like',
228 array('title' => ts('Price Field Value Label'),
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
237 'price_field_value_label' =>
238 array('title' => ts('Price Field Value Label'),
246 function getPriceFieldColumns() {
248 'civicrm_price_field' =>
250 'dao' => 'CRM_Price_BAO_PriceField',
253 'price_field_label' =>
254 array('title' => ts('Price Field Label'),
260 'price_field_label' =>
261 array('title' => ts('Price Field Label'),
262 'type' => CRM_Utils_Type
::T_STRING
,
263 'operator' => 'like',
269 'price_field_label' =>
270 array('title' => ts('Price Field Label'),
276 'price_field_label' =>
277 array('title' => ts('Price Field Label'),
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');
291 'civicrm_participant' =>
293 'dao' => 'CRM_Event_DAO_Participant',
295 array('participant_id' => array('title' => 'Participant ID'),
296 'participant_record' => array(
298 'title' => 'Participant Id',
300 'event_id' => array('title' => ts('Event ID'),
301 'type' => CRM_Utils_Type
::T_STRING
,
302 'alter_display' => 'alterEventID',
304 'status_id' => array('title' => ts('Status'),
305 'alter_display' => 'alterParticipantStatus',
307 'role_id' => array('title' => ts('Role'),
308 'alter_display' => 'alterParticipantRole',
310 'participant_fee_level' => NULL,
311 'participant_fee_amount' => NULL,
312 'participant_register_date' => array('title' => ts('Registration Date')),
314 'grouping' => 'event-fields',
317 'event_id' => array('name' => 'event_id',
318 'title' => ts('Event'),
319 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
320 'options' => $_events['all'],
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'),
330 'title' => ts('Participant Role'),
331 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
332 'options' => CRM_Event_PseudoConstant
::participantRole(),
334 'participant_register_date' => array(
335 'title' => ' Registration Date',
336 'operatorType' => CRM_Report_Form
::OP_DATE
,
342 array('title' => ts('Event'), 'default_weight' => '1', 'default_order' => 'ASC'),
347 array('title' => ts('Event')),
353 function getMembershipColumns() {
355 'civicrm_membership' => array(
356 'dao' => 'CRM_Member_DAO_Membership',
357 'grouping' => 'member-fields',
359 'membership_type_id' => array(
360 'title' => 'Membership Type',
362 'alter_display' => 'alterMembershipTypeID',
364 'status_id' => array(
365 'title' => 'Membership Status',
367 'alter_display' => 'alterMembershipStatusID',
370 'start_date' => array(
371 'title' => ts('Current Cycle Start Date'),
374 'title' => ts('Current Membership Cycle End Date'),
377 'group_bys' => array(
378 'membership_type_id' => array(
379 'title' => ts('Membership Type'),
383 'join_date' => array(
384 'type' => CRM_Utils_Type
::T_DATE
,
385 'operatorType' => CRM_Report_Form
::OP_DATE
,
392 function getMembershipTypeColumns() {
394 'civicrm_membership_type' => array(
395 'dao' => 'CRM_Member_DAO_MembershipType',
396 'grouping' => 'member-fields',
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(),
410 function getEventColumns() {
412 'civicrm_event' => array(
413 'dao' => 'CRM_Event_DAO_Event',
417 'no_display' => TRUE,
420 'title' => array('title' => ts('Event Title'),
423 'event_type_id' => array('title' => ts('Event Type'),
425 'alter_display' => 'alterEventType',
427 'fee_label' => array('title' => ts('Fee Label')),
428 'event_start_date' => array('title' => ts('Event Start Date'),
430 'event_end_date' => array('title' => ts('Event End Date')),
431 'max_participants' => array('title' => ts('Capacity'),
432 'type' => CRM_Utils_Type
::T_INT
,
435 'grouping' => 'event-fields',
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'),
443 'event_title' => array(
445 'title' => ts('Event Title'),
446 'operatorType' => CRM_Report_Form
::OP_STRING
,
449 'order_bys' => array(
450 'event_type_id' => array(
451 'title' => ts('Event Type'),
452 'default_weight' => '2',
453 'default_order' => 'ASC',
456 'group_bys' => array(
457 'event_type_id' => array(
458 'title' => ts('Event Type'),
465 function getContributionColumns() {
467 'civicrm_contribution' =>
469 'dao' => 'CRM_Contribute_DAO_Contribution',
472 'contribution_id' => array(
475 'financial_type_id' => array('title' => ts('Financial Type'),
477 'alter_display' => 'alterContributionType',
479 'payment_instrument_id' => array('title' => ts('Payment Instrument'),
480 'alter_display' => 'alterPaymentType',
482 'source' => array('title' => 'Contribution Source'),
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'),
490 array('sum' => ts('Total Amount')),
491 'type' => CRM_Utils_Type
::T_MONEY
,
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(),
503 'payment_instrument_id' =>
504 array('title' => ts('Payment Type'),
505 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
506 'options' => CRM_Contribute_PseudoConstant
::paymentInstrument(),
508 'contribution_status_id' =>
509 array('title' => ts('Contribution Status'),
510 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
511 'options' => CRM_Contribute_PseudoConstant
::contributionStatus(),
514 array('title' => ts('Contribution Amount')),
518 'payment_instrument_id' =>
519 array('title' => ts('Payment Instrument'),
521 'financial_type_id' =>
522 array('title' => ts('Financial Type'),
526 array('financial_type_id' =>
527 array('title' => ts('Financial Type')),
528 'payment_instrument_id' =>
529 array('title' => ts('Payment Instrument')),
531 array('title' => ts('Individual Contribution'),
534 'source' => array('title' => 'Contribution Source'),
536 'grouping' => 'contribution-fields',
541 function getContactColumns() {
543 'civicrm_contact' => array(
544 'dao' => 'CRM_Contact_DAO_Contact',
546 'display_name' => array(
547 'title' => ts('Contact Name'),
550 'title' => ts('Contact ID'),
551 'alter_display' => 'alterContactID',
553 'first_name' => array(
554 'title' => ts('First Name'),
556 'last_name' => array(
557 'title' => ts('Last Name'),
559 'nick_name' => array(
560 'title' => ts('Nickname'),
561 'alter_display' => 'alterNickname',
566 'title' => ts('Contact ID'),
569 'sort_name' => array(
570 'title' => ts('Contact Name'),
573 'grouping' => 'contact-fields',
574 'order_bys' => array(
575 'sort_name' => array(
576 'title' => ts('Last Name, First Name'),
578 'default_weight' => '0',
579 'default_order' => 'ASC',
586 function getCaseColumns() {
588 'civicrm_case' => array(
589 'dao' => 'CRM_Case_DAO_Case',
592 'title' => ts('Case ID'),
596 'title' => ts('Case Subject'),
599 'status_id' => array(
600 'title' => ts('Status'),
603 'case_type_id' => array(
604 'title' => ts('Case Type'),
607 'case_start_date' => array(
608 'title' => ts('Case Start Date'),
609 'name' => 'start_date',
612 'case_end_date' => array(
613 'title' => ts('Case End Date'),
614 'name' => 'end_date',
617 'case_duration' => array(
618 'name' => 'duration',
619 'title' => ts('Duration (Days)'),
622 'case_is_deleted' => array(
623 'name' => 'is_deleted',
624 'title' => ts('Case Deleted?'),
626 'type' => CRM_Utils_Type
::T_INT
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',
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
,
642 'case_type_id' => array(
643 'title' => ts('Case Type'),
644 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
645 'options' => $this->case_types
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'
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
,
659 'name' => 'is_deleted'
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
677 function getAddressColumns($options = array()) {
678 $defaultOptions = array(
680 'prefix_label' => '',
689 $options = array_merge($defaultOptions,$options);
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',
697 $options['prefix'] . 'name' => array(
698 'title' => ts($options['prefix_label'] . 'Address Name'),
699 'default' => CRM_Utils_Array
::value('name', $options['defaults'], FALSE),
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',
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',
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',
717 $options['prefix'] . 'street_number' => array(
718 'name' => 'street_number',
719 'title' => ts($options['prefix_label'] . 'Street Number'),
721 'default' => CRM_Utils_Array
::value('street_number', $options['defaults'], FALSE),
722 'name' => 'street_number',
724 $options['prefix'] . 'street_name' => array(
725 'name' => 'street_name',
726 'title' => ts($options['prefix_label'] . 'Street Name'),
728 'default' => CRM_Utils_Array
::value('street_name', $options['defaults'], FALSE),
729 'name' => 'street_name',
731 $options['prefix'] . 'street_unit' => array(
732 'name' => 'street_unit',
733 'title' => ts($options['prefix_label'] . 'Street Unit'),
735 'default' => CRM_Utils_Array
::value('street_unit', $options['defaults'], FALSE),
736 'name' => 'street_unit',
738 $options['prefix'] . 'city' => array(
739 'title' => ts($options['prefix_label'] . 'City'),
740 'default' => CRM_Utils_Array
::value('city', $options['defaults'], FALSE),
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',
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',
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',
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',
767 'grouping' => 'location-fields',
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'),
776 'name' => 'street_number',
778 $options['prefix'] . 'street_name' => array(
779 'title' => ts($options['prefix_label'] . 'Street Name'),
780 'name' => $options['prefix'] . 'street_name',
781 'operator' => 'like',
783 $options['prefix'] . 'postal_code' => array(
784 'title' => ts($options['prefix_label'] . 'Postal Code'),
786 'name' => 'postal_code',
788 $options['prefix'] . 'city' => array(
789 'title' => ts($options['prefix_label'] . 'City'),
790 'operator' => 'like',
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(),
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(),
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(),
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',
823 $options['prefix'] . 'street_number' => array(
824 'title' => ts($options['prefix_label'] . 'Odd / Even Street Number'),
825 'name' => 'street_number',
827 $options['prefix'] . 'street_address' => array(
828 'title' => ts($options['prefix_label'] . 'Street Address'),
829 'name' => 'street_address',
831 $options['prefix'] . 'city' => array(
832 'title' => ts($options['prefix_label'] . 'City'),
835 $options['prefix'] . 'postal_code' => array(
836 'title' => ts($options['prefix_label'] . 'Post Code'),
837 'name' => 'postal_code',
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',
848 $options['prefix'] . 'city' => array(
849 'title' => ts($options['prefix_label'] . 'City'),
852 $options['prefix'] . 'postal_code' => array(
853 'title' => ts($options['prefix_label'] . 'Post Code'),
854 'name' => 'postal_code',
856 $options['prefix'] . 'state_province_id' => array(
857 'title' => ts($options['prefix_label'] . 'State/Province'),
858 'name' => 'state_province_id',
860 $options['prefix'] . 'country_id' => array(
861 'title' => ts($options['prefix_label'] . 'Country'),
862 'name' => 'country_id',
864 $options['prefix'] . 'county_id' => array(
865 'title' => ts($options['prefix_label'] . 'County'),
866 'name' => 'county_id',
870 return $addressFields;
874 * Get Information about advertised Joins
876 function getAvailableJoins() {
878 'priceFieldValue_from_lineItem' => array(
879 'leftTable' => 'civicrm_line_item',
880 'rightTable' => 'civicrm_price_field_value',
881 'callback' => 'joinPriceFieldValueFromLineItem',
883 'priceField_from_lineItem' => array(
884 'leftTable' => 'civicrm_line_item',
885 'rightTable' => 'civicrm_price_field',
886 'callback' => 'joinPriceFieldFromLineItem',
888 'participant_from_lineItem' => array(
889 'leftTable' => 'civicrm_line_item',
890 'rightTable' => 'civicrm_participant',
891 'callback' => 'joinParticipantFromLineItem',
893 'contribution_from_lineItem' => array(
894 'leftTable' => 'civicrm_line_item',
895 'rightTable' => 'civicrm_contribution',
896 'callback' => 'joinContributionFromLineItem',
898 'membership_from_lineItem' => array(
899 'leftTable' => 'civicrm_line_item',
900 'rightTable' => 'civicrm_membership',
901 'callback' => 'joinMembershipFromLineItem',
903 'contribution_from_participant' => array(
904 'leftTable' => 'civicrm_participant',
905 'rightTable' => 'civicrm_contribution',
906 'callback' => 'joinContributionFromParticipant',
908 'contribution_from_membership' => array(
909 'leftTable' => 'civicrm_membership',
910 'rightTable' => 'civicrm_contribution',
911 'callback' => 'joinContributionFromMembership',
913 'membership_from_contribution' => array(
914 'leftTable' => 'civicrm_contribution',
915 'rightTable' => 'civicrm_membership',
916 'callback' => 'joinMembershipFromContribution',
918 'membershipType_from_membership' => array(
919 'leftTable' => 'civicrm_membership',
920 'rightTable' => 'civicrm_membership_type',
921 'callback' => 'joinMembershipTypeFromMembership',
923 'lineItem_from_contribution' => array(
924 'leftTable' => 'civicrm_contribution',
925 'rightTable' => 'civicrm_line_item',
926 'callback' => 'joinLineItemFromContribution',
928 'lineItem_from_membership' => array(
929 'leftTable' => 'civicrm_membership',
930 'rightTable' => 'civicrm_line_item',
931 'callback' => 'joinLineItemFromMembership',
933 'contact_from_participant' => array(
934 'leftTable' => 'civicrm_participant',
935 'rightTable' => 'civicrm_contact',
936 'callback' => 'joinContactFromParticipant',
938 'contact_from_membership' => array(
939 'leftTable' => 'civicrm_membership',
940 'rightTable' => 'civicrm_contact',
941 'callback' => 'joinContactFromMembership',
943 'contact_from_contribution' => array(
944 'leftTable' => 'civicrm_contribution',
945 'rightTable' => 'civicrm_contact',
946 'callback' => 'joinContactFromContribution',
948 'event_from_participant' => array(
949 'leftTable' => 'civicrm_participant',
950 'rightTable' => 'civicrm_event',
951 'callback' => 'joinEventFromParticipant',
953 'address_from_contact' => array(
954 'leftTable' => 'civicrm_contact',
955 'rightTable' => 'civicrm_address',
956 'callback' => 'joinAddressFromContact',
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
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";
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";
975 function joinPriceFieldFromLineItem() {
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
983 * Define join from line item table to participant table
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')
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?
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
1008 * Define join from Participant to Contribution table
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
1019 * Define join from Membership to Contribution table
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
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";
1036 function joinMembershipFromContribution() {
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";
1044 function joinMembershipTypeFromMembership() {
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
1051 function joinContributionFromLineItem() {
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')
1061 WHERE contribution_civireport_direct.id IS NOT NULL
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')
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
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')
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
1087 function joinLineItemFromContribution() {
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
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
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
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')
1113 WHERE line_item_civireport.id IS NOT NULL
1114 ) as {$this->_aliases['civicrm_line_item']}
1115 ON {$this->_aliases['civicrm_line_item']}.contid = {$this->_aliases['civicrm_contribution']}.id
1121 function joinLineItemFromMembership() {
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
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')
1132 WHERE line_item_civireport.id IS NOT NULL
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')
1141 WHERE line_item_civireport.id IS NOT NULL
1142 ) as {$this->_aliases['civicrm_line_item']}
1143 ON {$this->_aliases['civicrm_line_item']}.contid = {$this->_aliases['civicrm_contribution']}.id
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";
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";
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";
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)";
1170 * Retrieve text for financial type from pseudoconstant
1172 function alterNickName($value, &$row) {
1173 if(empty($row['civicrm_contact_id'])){
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>";
1183 * Retrieve text for contribution type from pseudoconstant
1185 function alterContributionType($value, &$row) {
1186 return is_string(CRM_Contribute_PseudoConstant
::financialType($value, FALSE)) ? CRM_Contribute_PseudoConstant
::financialType($value, FALSE) : '';
1190 * Retrieve text for contribution status from pseudoconstant
1192 function alterContributionStatus($value, &$row) {
1193 return CRM_Contribute_PseudoConstant
::contributionStatus($value);
1197 * Retrieve text for payment instrument from pseudoconstant
1199 function alterEventType($value, &$row) {
1200 return CRM_Event_PseudoConstant
::eventType($value);
1203 function alterEventID($value, &$row) {
1204 return is_string(CRM_Event_PseudoConstant
::event($value, FALSE)) ? CRM_Event_PseudoConstant
::event($value, FALSE) : '';
1207 function alterMembershipTypeID($value, &$row) {
1208 return is_string(CRM_Member_PseudoConstant
::membershipType($value, FALSE)) ? CRM_Member_PseudoConstant
::membershipType($value, FALSE) : '';
1211 function alterMembershipStatusID($value, &$row) {
1212 return is_string(CRM_Member_PseudoConstant
::membershipStatus($value, FALSE)) ? CRM_Member_PseudoConstant
::membershipStatus($value, FALSE) : '';
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;
1218 $row[$selectedfield . '_hover'] = ts("%1 for this country.", array(1 => $value));
1219 $countries = CRM_Core_PseudoConstant
::country($value, FALSE);
1220 if(!is_array($countries)){
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;
1228 $row[$selectedfield . '_hover'] = ts("%1 for this county.", array(1 => $value));
1229 $counties = CRM_Core_PseudoConstant
::county($value, FALSE);
1230 if(!is_array($counties)){
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;
1238 $row[$selectedfield . '_hover'] = ts("%1 for this state.", array(1 => $value));
1240 $states = CRM_Core_PseudoConstant
::stateProvince($value, FALSE);
1241 if(!is_array($states)){
1246 function alterContactID($value, &$row, $fieldname) {
1247 $row[$fieldname . '_link'] = CRM_Utils_System
::url("civicrm/contact/view", 'reset=1&cid=' . $value, $this->_absoluteUrl
);
1251 function alterParticipantStatus($value) {
1252 if (empty($value)) {
1255 return CRM_Event_PseudoConstant
::participantStatus($value, FALSE, 'label');
1258 function alterParticipantRole($value) {
1259 if (empty($value)) {
1262 $roles = explode(CRM_Core_DAO
::VALUE_SEPARATOR
, $value);
1264 foreach ($roles as $role) {
1265 $value[$role] = CRM_Event_PseudoConstant
::participantRole($role, FALSE);
1267 return implode(', ', $value);
1270 function alterPaymentType($value) {
1271 $paymentInstruments = CRM_Contribute_PseudoConstant
::paymentInstrument();
1272 return $paymentInstruments[$value];