3 +--------------------------------------------------------------------+
4 | CiviCRM version 4.5 |
5 +--------------------------------------------------------------------+
6 | Copyright CiviCRM LLC (c) 2004-2014 |
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-2014
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';
51 function __construct() {
52 parent
::__construct();
55 function preProcess() {
65 * From clause build where baseTable & fromClauses are defined
68 if (!empty($this->_baseTable
)) {
69 $this->buildACLClause($this->_aliases
['civicrm_contact']);
70 $this->_from
= "FROM {$this->_baseTable} {$this->_aliases[$this->_baseTable]}";
71 $availableClauses = $this->getAvailableJoins();
72 foreach ($this->fromClauses() as $fromClause) {
73 $fn = $availableClauses[$fromClause]['callback'];
76 if (strstr($this->_from
, 'civicrm_contact')) {
77 $this->_from
.= $this->_aclFrom
;
83 * Define any from clauses in use (child classes to override)
88 function fromClauses() {
94 //@todo - need to re-visit this - bad behaviour from pa
95 if ($this->_groupBy
== 'GROUP BY') {
96 $this->_groupBY
= NULL;
98 // if a stat field has been selected the do a group by
99 if (!empty($this->_statFields
) && empty($this->_groupBy
)) {
100 $this->_groupBy
[] = $this->_aliases
[$this->_baseTable
] . ".id";
102 //@todo - this should be in the parent function or at parent level - perhaps build query should do this?
103 if (!empty($this->_groupBy
) && is_array($this->_groupBy
)) {
104 $this->_groupBy
= 'GROUP BY ' . implode(',', $this->_groupBy
);
117 function statistics(&$rows) {
118 return parent
::statistics($rows);
121 function postProcess() {
122 if (!empty($this->_aclTable
) && !empty($this->_aliases
[$this->_aclTable
])) {
123 $this->buildACLClause($this->_aliases
[$this->_aclTable
]);
125 parent
::postProcess();
131 function alterDisplay(&$rows) {
132 parent
::alterDisplay($rows);
134 //THis is all generic functionality which can hopefully go into the parent class
135 // it introduces the option of defining an alter display function as part of the column definition
136 // @tod tidy up the iteration so it happens in this function
137 list($firstRow) = $rows;
138 // no result to alter
139 if (empty($firstRow)) {
142 $selectedFields = array_keys($firstRow);
144 $alterfunctions = $altermap = array();
145 foreach ($this->_columns
as $tablename => $table) {
146 if (array_key_exists('fields', $table)) {
147 foreach ($table['fields'] as $field => $specs) {
148 if (in_array($tablename . '_' . $field, $selectedFields) && array_key_exists('alter_display', $specs)) {
149 $alterfunctions[$tablename . '_' . $field] = $specs['alter_display'];
150 $altermap[$tablename . '_' . $field] = $field;
155 if (empty($alterfunctions)) {
156 // - no manipulation to be done
160 foreach ($rows as $index => & $row) {
161 foreach ($row as $selectedfield => $value) {
162 if (array_key_exists($selectedfield, $alterfunctions)) {
163 $rows[$index][$selectedfield] = $this->$alterfunctions[$selectedfield]($value, $row, $selectedfield, $altermap[$selectedfield]);
172 function getLineItemColumns() {
174 'civicrm_line_item' =>
176 'dao' => 'CRM_Price_BAO_LineItem',
180 array('title' => ts('Quantity'),
181 'type' => CRM_Utils_Type
::T_INT
,
183 array('sum' => ts('Total Quantity Selected')),
186 array('title' => ts('Unit Price'),
189 array('title' => ts('Line Total'),
190 'type' => CRM_Utils_Type
::T_MONEY
,
192 array('sum' => ts('Total of Line Items')),
195 'participant_count' =>
196 array('title' => ts('Participant Count'),
198 array('sum' => ts('Total Participants')),
203 array('title' => ts('Quantity'),
204 'type' => CRM_Utils_Type
::T_INT
,
205 'operator' => CRM_Report_Form
::OP_INT
,
211 array('title' => ts('Price Field'),
213 'price_field_value_id' =>
214 array('title' => ts('Price Field Option'),
217 array('title' => ts('Individual Line Item'),
228 function getPriceFieldValueColumns() {
230 'civicrm_price_field_value' =>
232 'dao' => 'CRM_Price_BAO_PriceFieldValue',
234 'price_field_value_label' =>
235 array('title' => ts('Price Field Value Label'),
241 'price_field_value_label' =>
242 array('title' => ts('Price Fields Value Label'),
243 'type' => CRM_Utils_Type
::T_STRING
,
244 'operator' => 'like',
251 array('title' => ts('Price Field Value Label'),
255 //note that we have a requirement to group by label such that all 'Promo book' lines
256 // are grouped together across price sets but there may be a separate need to group
257 // by id so that entries in one price set are distinct from others. Not quite sure what
258 // to call the distinction for end users benefit
260 'price_field_value_label' =>
261 array('title' => ts('Price Field Value Label'),
272 function getPriceFieldColumns() {
274 'civicrm_price_field' =>
276 'dao' => 'CRM_Price_BAO_PriceField',
279 'price_field_label' =>
280 array('title' => ts('Price Field Label'),
286 'price_field_label' =>
287 array('title' => ts('Price Field Label'),
288 'type' => CRM_Utils_Type
::T_STRING
,
289 'operator' => 'like',
295 'price_field_label' =>
296 array('title' => ts('Price Field Label'),
302 'price_field_label' =>
303 array('title' => ts('Price Field Label'),
314 function getParticipantColumns() {
315 static $_events = array();
316 if (!isset($_events['all'])) {
317 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');
320 'civicrm_participant' =>
322 'dao' => 'CRM_Event_DAO_Participant',
324 array('participant_id' => array('title' => 'Participant ID'),
325 'participant_record' => array(
327 'title' => ts('Participant ID'),
329 'event_id' => array('title' => ts('Event ID'),
330 'type' => CRM_Utils_Type
::T_STRING
,
331 'alter_display' => 'alterEventID',
333 'status_id' => array('title' => ts('Status'),
334 'alter_display' => 'alterParticipantStatus',
336 'role_id' => array('title' => ts('Role'),
337 'alter_display' => 'alterParticipantRole',
339 'participant_fee_level' => NULL,
340 'participant_fee_amount' => NULL,
341 'participant_register_date' => array('title' => ts('Registration Date')),
343 'grouping' => 'event-fields',
346 'event_id' => array('name' => 'event_id',
347 'title' => ts('Event'),
348 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
349 'options' => $_events['all'],
352 'name' => 'status_id',
353 'title' => ts('Participant Status'),
354 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
355 'options' => CRM_Event_PseudoConstant
::participantStatus(NULL, NULL, 'label'),
359 'title' => ts('Participant Role'),
360 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
361 'options' => CRM_Event_PseudoConstant
::participantRole(),
363 'participant_register_date' => array(
364 'title' => ' Registration Date',
365 'operatorType' => CRM_Report_Form
::OP_DATE
,
371 array('title' => ts('Event'), 'default_weight' => '1', 'default_order' => 'ASC'),
376 array('title' => ts('Event')),
385 function getMembershipColumns() {
387 'civicrm_membership' => array(
388 'dao' => 'CRM_Member_DAO_Membership',
389 'grouping' => 'member-fields',
391 'membership_type_id' => array(
392 'title' => 'Membership Type',
394 'alter_display' => 'alterMembershipTypeID',
396 'status_id' => array(
397 'title' => 'Membership Status',
399 'alter_display' => 'alterMembershipStatusID',
402 'start_date' => array(
403 'title' => ts('Current Cycle Start Date'),
406 'title' => ts('Current Membership Cycle End Date'),
409 'group_bys' => array(
410 'membership_type_id' => array(
411 'title' => ts('Membership Type'),
415 'join_date' => array(
416 'type' => CRM_Utils_Type
::T_DATE
,
417 'operatorType' => CRM_Report_Form
::OP_DATE
,
427 function getMembershipTypeColumns() {
429 'civicrm_membership_type' => array(
430 'dao' => 'CRM_Member_DAO_MembershipType',
431 'grouping' => 'member-fields',
435 'title' => ts('Membership Types'),
436 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
437 'type' => CRM_Utils_Type
::T_INT + CRM_Utils_Type
::T_ENUM
,
438 'options' => CRM_Member_PseudoConstant
::membershipType(),
448 function getEventColumns() {
450 'civicrm_event' => array(
451 'dao' => 'CRM_Event_DAO_Event',
455 'no_display' => TRUE,
458 'title' => array('title' => ts('Event Title'),
461 'event_type_id' => array('title' => ts('Event Type'),
463 'alter_display' => 'alterEventType',
465 'fee_label' => array('title' => ts('Fee Label')),
466 'event_start_date' => array('title' => ts('Event Start Date'),
468 'event_end_date' => array('title' => ts('Event End Date')),
469 'max_participants' => array('title' => ts('Capacity'),
470 'type' => CRM_Utils_Type
::T_INT
,
473 'grouping' => 'event-fields',
475 'event_type_id' => array(
476 'name' => 'event_type_id',
477 'title' => ts('Event Type'),
478 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
479 'options' => CRM_Core_OptionGroup
::values('event_type'),
481 'event_title' => array(
483 'title' => ts('Event Title'),
484 'operatorType' => CRM_Report_Form
::OP_STRING
,
487 'order_bys' => array(
488 'event_type_id' => array(
489 'title' => ts('Event Type'),
490 'default_weight' => '2',
491 'default_order' => 'ASC',
494 'group_bys' => array(
495 'event_type_id' => array(
496 'title' => ts('Event Type'),
506 function getContributionColumns() {
508 'civicrm_contribution' =>
510 'dao' => 'CRM_Contribute_DAO_Contribution',
513 'contribution_id' => array(
516 'financial_type_id' => array('title' => ts('Financial Type'),
518 'alter_display' => 'alterContributionType',
520 'payment_instrument_id' => array('title' => ts('Payment Instrument'),
521 'alter_display' => 'alterPaymentType',
523 'source' => array('title' => 'Contribution Source'),
525 'receive_date' => array('default' => TRUE),
526 'receipt_date' => NULL,
527 'fee_amount' => NULL,
528 'net_amount' => NULL,
529 'total_amount' => array('title' => ts('Amount'),
531 array('sum' => ts('Total Amount')),
532 'type' => CRM_Utils_Type
::T_MONEY
,
538 array('operatorType' => CRM_Report_Form
::OP_DATE
),
539 'financial_type_id' =>
540 array('title' => ts('Financial Type'),
541 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
542 'options' => CRM_Contribute_PseudoConstant
::financialType(),
544 'payment_instrument_id' =>
545 array('title' => ts('Payment Type'),
546 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
547 'options' => CRM_Contribute_PseudoConstant
::paymentInstrument(),
549 'contribution_status_id' =>
550 array('title' => ts('Contribution Status'),
551 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
552 'options' => CRM_Contribute_PseudoConstant
::contributionStatus(),
555 array('title' => ts('Contribution Amount')),
559 'payment_instrument_id' =>
560 array('title' => ts('Payment Instrument'),
562 'financial_type_id' =>
563 array('title' => ts('Financial Type'),
567 array('financial_type_id' =>
568 array('title' => ts('Financial Type')),
569 'payment_instrument_id' =>
570 array('title' => ts('Payment Instrument')),
572 array('title' => ts('Individual Contribution'),
575 'source' => array('title' => 'Contribution Source'),
577 'grouping' => 'contribution-fields',
585 function getContactColumns() {
587 'civicrm_contact' => array(
588 'dao' => 'CRM_Contact_DAO_Contact',
590 'display_name' => array(
591 'title' => ts('Contact Name'),
594 'title' => ts('Contact ID'),
595 'alter_display' => 'alterContactID',
597 'first_name' => array(
598 'title' => ts('First Name'),
600 'last_name' => array(
601 'title' => ts('Last Name'),
603 'nick_name' => array(
604 'title' => ts('Nickname'),
605 'alter_display' => 'alterNickname',
610 'title' => ts('Contact ID'),
613 'sort_name' => array(
614 'title' => ts('Contact Name'),
617 'grouping' => 'contact-fields',
618 'order_bys' => array(
619 'sort_name' => array(
620 'title' => ts('Last Name, First Name'),
622 'default_weight' => '0',
623 'default_order' => 'ASC',
633 function getCaseColumns() {
635 'civicrm_case' => array(
636 'dao' => 'CRM_Case_DAO_Case',
639 'title' => ts('Case ID'),
643 'title' => ts('Case Subject'),
646 'status_id' => array(
647 'title' => ts('Status'),
650 'case_type_id' => array(
651 'title' => ts('Case Type'),
654 'case_start_date' => array(
655 'title' => ts('Case Start Date'),
656 'name' => 'start_date',
659 'case_end_date' => array(
660 'title' => ts('Case End Date'),
661 'name' => 'end_date',
664 'case_duration' => array(
665 'name' => 'duration',
666 'title' => ts('Duration (Days)'),
669 'case_is_deleted' => array(
670 'name' => 'is_deleted',
671 'title' => ts('Case Deleted?'),
673 'type' => CRM_Utils_Type
::T_INT
677 'case_start_date' => array(
678 'title' => ts('Case Start Date'),
679 'operatorType' => CRM_Report_Form
::OP_DATE
,
680 'type' => CRM_Utils_Type
::T_DATE
,
681 'name' => 'start_date',
683 'case_end_date' => array(
684 'title' => ts('Case End Date'),
685 'operatorType' => CRM_Report_Form
::OP_DATE
,
686 'type' => CRM_Utils_Type
::T_DATE
,
689 'case_type_id' => array(
690 'title' => ts('Case Type'),
691 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
692 'options' => $this->case_types
694 'case_status_id' => array(
695 'title' => ts('Case Status'),
696 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
697 'options' => $this->case_statuses
,
698 'name' => 'status_id'
700 'case_is_deleted' => array(
701 'title' => ts('Case Deleted?'),
702 'type' => CRM_Report_Form
::OP_INT
,
703 'operatorType' => CRM_Report_Form
::OP_SELECT
,
704 'options' => $this->deleted_labels
,
706 'name' => 'is_deleted'
714 * adding address fields to construct function in reports
715 * @param array $options Options for the report
716 * - prefix prefix to add (e.g. 'honor' when getting address details for honor contact
717 * - prefix_label optional prefix lable eg. "Honoree " for front end
718 * - group_by enable these fields for group by - default false
719 * - order_by enable these fields for order by
720 * - filters enable these fields for filtering
721 * - defaults - (is this working?) values to pre-populate
722 * @return array address fields for construct clause
725 * Get address columns to add to array
726 * @param array $options
727 * - prefix Prefix to add to table (in case of more than one instance of the table)
728 * - prefix_label Label to give columns from this address table instance
729 * @return array address columns definition
732 * @param array $options
736 function getAddressColumns($options = array()) {
737 $defaultOptions = array(
739 'prefix_label' => '',
748 $options = array_merge($defaultOptions,$options);
750 $addressFields = array(
751 $options['prefix'] . 'civicrm_address' => array(
752 'dao' => 'CRM_Core_DAO_Address',
753 'name' => 'civicrm_address',
754 'alias' => $options['prefix'] . 'civicrm_address',
756 $options['prefix'] . 'name' => array(
757 'title' => ts($options['prefix_label'] . 'Address Name'),
758 'default' => CRM_Utils_Array
::value('name', $options['defaults'], FALSE),
761 $options['prefix'] . 'street_address' => array(
762 'title' => ts($options['prefix_label'] . 'Street Address'),
763 'default' => CRM_Utils_Array
::value('street_address', $options['defaults'], FALSE),
764 'name' => 'street_address',
766 $options['prefix'] . 'supplemental_address_1' => array(
767 'title' => ts($options['prefix_label'] . 'Supplementary Address Field 1'),
768 'default' => CRM_Utils_Array
::value('supplemental_address_1', $options['defaults'], FALSE),
769 'name' => 'supplemental_address_1',
771 $options['prefix'] . 'supplemental_address_2' => array(
772 'title' => ts($options['prefix_label'] . 'Supplementary Address Field 2'),
773 'default' => CRM_Utils_Array
::value('supplemental_address_2', $options['defaults'], FALSE),
774 'name' => 'supplemental_address_2',
776 $options['prefix'] . 'street_number' => array(
777 'name' => 'street_number',
778 'title' => ts($options['prefix_label'] . 'Street Number'),
780 'default' => CRM_Utils_Array
::value('street_number', $options['defaults'], FALSE),
781 'name' => 'street_number',
783 $options['prefix'] . 'street_name' => array(
784 'name' => 'street_name',
785 'title' => ts($options['prefix_label'] . 'Street Name'),
787 'default' => CRM_Utils_Array
::value('street_name', $options['defaults'], FALSE),
788 'name' => 'street_name',
790 $options['prefix'] . 'street_unit' => array(
791 'name' => 'street_unit',
792 'title' => ts($options['prefix_label'] . 'Street Unit'),
794 'default' => CRM_Utils_Array
::value('street_unit', $options['defaults'], FALSE),
795 'name' => 'street_unit',
797 $options['prefix'] . 'city' => array(
798 'title' => ts($options['prefix_label'] . 'City'),
799 'default' => CRM_Utils_Array
::value('city', $options['defaults'], FALSE),
802 $options['prefix'] . 'postal_code' => array(
803 'title' => ts($options['prefix_label'] . 'Postal Code'),
804 'default' => CRM_Utils_Array
::value('postal_code', $options['defaults'], FALSE),
805 'name' => 'postal_code',
807 $options['prefix'] . 'county_id' => array(
808 'title' => ts($options['prefix_label'] . 'County'),
809 'default' => CRM_Utils_Array
::value('county_id', $options['defaults'], FALSE),
810 'alter_display' => 'alterCountyID',
811 'name' => 'county_id',
813 $options['prefix'] . 'state_province_id' => array(
814 'title' => ts($options['prefix_label'] . 'State/Province'),
815 'default' => CRM_Utils_Array
::value('state_province_id', $options['defaults'], FALSE),
816 'alter_display' => 'alterStateProvinceID',
817 'name' => 'state_province_id',
819 $options['prefix'] . 'country_id' => array(
820 'title' => ts($options['prefix_label'] . 'Country'),
821 'default' => CRM_Utils_Array
::value('country_id', $options['defaults'], FALSE),
822 'alter_display' => 'alterCountryID',
823 'name' => 'country_id',
826 'grouping' => 'location-fields',
830 if ($options['filters']) {
831 $addressFields[$options['prefix'] .'civicrm_address']['filters'] = array(
832 $options['prefix'] . 'street_number' => array(
833 'title' => ts($options['prefix_label'] . 'Street Number'),
835 'name' => 'street_number',
837 $options['prefix'] . 'street_name' => array(
838 'title' => ts($options['prefix_label'] . 'Street Name'),
839 'name' => $options['prefix'] . 'street_name',
840 'operator' => 'like',
842 $options['prefix'] . 'postal_code' => array(
843 'title' => ts($options['prefix_label'] . 'Postal Code'),
845 'name' => 'postal_code',
847 $options['prefix'] . 'city' => array(
848 'title' => ts($options['prefix_label'] . 'City'),
849 'operator' => 'like',
852 $options['prefix'] . 'county_id' => array(
853 'name' => 'county_id',
854 'title' => ts($options['prefix_label'] . 'County'),
855 'type' => CRM_Utils_Type
::T_INT
,
856 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
857 'options' => CRM_Core_PseudoConstant
::county(),
859 $options['prefix'] . 'state_province_id' => array(
860 'name' => 'state_province_id',
861 'title' => ts($options['prefix_label'] . 'State/Province'),
862 'type' => CRM_Utils_Type
::T_INT
,
863 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
864 'options' => CRM_Core_PseudoConstant
::stateProvince(),
866 $options['prefix'] . 'country_id' => array(
867 'name' => 'country_id',
868 'title' => ts($options['prefix_label'] . 'Country'),
869 'type' => CRM_Utils_Type
::T_INT
,
870 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
871 'options' => CRM_Core_PseudoConstant
::country(),
876 if ($options['order_by']) {
877 $addressFields[$options['prefix'] . 'civicrm_address']['order_bys'] = array(
878 $options['prefix'] . 'street_name' => array(
879 'title' => ts($options['prefix_label'] . 'Street Name'),
880 'name' => 'street_name',
882 $options['prefix'] . 'street_number' => array(
883 'title' => ts($options['prefix_label'] . 'Odd / Even Street Number'),
884 'name' => 'street_number',
886 $options['prefix'] . 'street_address' => array(
887 'title' => ts($options['prefix_label'] . 'Street Address'),
888 'name' => 'street_address',
890 $options['prefix'] . 'city' => array(
891 'title' => ts($options['prefix_label'] . 'City'),
894 $options['prefix'] . 'postal_code' => array(
895 'title' => ts($options['prefix_label'] . 'Post Code'),
896 'name' => 'postal_code',
901 if ($options['group_by']) {
902 $addressFields['civicrm_address']['group_bys'] = array(
903 $options['prefix'] . 'street_address' => array(
904 'title' => ts($options['prefix_label'] . 'Street Address'),
905 'name' => 'street_address',
907 $options['prefix'] . 'city' => array(
908 'title' => ts($options['prefix_label'] . 'City'),
911 $options['prefix'] . 'postal_code' => array(
912 'title' => ts($options['prefix_label'] . 'Post Code'),
913 'name' => 'postal_code',
915 $options['prefix'] . 'state_province_id' => array(
916 'title' => ts($options['prefix_label'] . 'State/Province'),
917 'name' => 'state_province_id',
919 $options['prefix'] . 'country_id' => array(
920 'title' => ts($options['prefix_label'] . 'Country'),
921 'name' => 'country_id',
923 $options['prefix'] . 'county_id' => array(
924 'title' => ts($options['prefix_label'] . 'County'),
925 'name' => 'county_id',
929 return $addressFields;
933 * Get Information about advertised Joins
938 function getAvailableJoins() {
940 'priceFieldValue_from_lineItem' => array(
941 'leftTable' => 'civicrm_line_item',
942 'rightTable' => 'civicrm_price_field_value',
943 'callback' => 'joinPriceFieldValueFromLineItem',
945 'priceField_from_lineItem' => array(
946 'leftTable' => 'civicrm_line_item',
947 'rightTable' => 'civicrm_price_field',
948 'callback' => 'joinPriceFieldFromLineItem',
950 'participant_from_lineItem' => array(
951 'leftTable' => 'civicrm_line_item',
952 'rightTable' => 'civicrm_participant',
953 'callback' => 'joinParticipantFromLineItem',
955 'contribution_from_lineItem' => array(
956 'leftTable' => 'civicrm_line_item',
957 'rightTable' => 'civicrm_contribution',
958 'callback' => 'joinContributionFromLineItem',
960 'membership_from_lineItem' => array(
961 'leftTable' => 'civicrm_line_item',
962 'rightTable' => 'civicrm_membership',
963 'callback' => 'joinMembershipFromLineItem',
965 'contribution_from_participant' => array(
966 'leftTable' => 'civicrm_participant',
967 'rightTable' => 'civicrm_contribution',
968 'callback' => 'joinContributionFromParticipant',
970 'contribution_from_membership' => array(
971 'leftTable' => 'civicrm_membership',
972 'rightTable' => 'civicrm_contribution',
973 'callback' => 'joinContributionFromMembership',
975 'membership_from_contribution' => array(
976 'leftTable' => 'civicrm_contribution',
977 'rightTable' => 'civicrm_membership',
978 'callback' => 'joinMembershipFromContribution',
980 'membershipType_from_membership' => array(
981 'leftTable' => 'civicrm_membership',
982 'rightTable' => 'civicrm_membership_type',
983 'callback' => 'joinMembershipTypeFromMembership',
985 'lineItem_from_contribution' => array(
986 'leftTable' => 'civicrm_contribution',
987 'rightTable' => 'civicrm_line_item',
988 'callback' => 'joinLineItemFromContribution',
990 'lineItem_from_membership' => array(
991 'leftTable' => 'civicrm_membership',
992 'rightTable' => 'civicrm_line_item',
993 'callback' => 'joinLineItemFromMembership',
995 'contact_from_participant' => array(
996 'leftTable' => 'civicrm_participant',
997 'rightTable' => 'civicrm_contact',
998 'callback' => 'joinContactFromParticipant',
1000 'contact_from_membership' => array(
1001 'leftTable' => 'civicrm_membership',
1002 'rightTable' => 'civicrm_contact',
1003 'callback' => 'joinContactFromMembership',
1005 'contact_from_contribution' => array(
1006 'leftTable' => 'civicrm_contribution',
1007 'rightTable' => 'civicrm_contact',
1008 'callback' => 'joinContactFromContribution',
1010 'event_from_participant' => array(
1011 'leftTable' => 'civicrm_participant',
1012 'rightTable' => 'civicrm_event',
1013 'callback' => 'joinEventFromParticipant',
1015 'address_from_contact' => array(
1016 'leftTable' => 'civicrm_contact',
1017 'rightTable' => 'civicrm_address',
1018 'callback' => 'joinAddressFromContact',
1024 * Add join from contact table to address. Prefix will be added to both tables
1025 * as it's assumed you are using it to get address of a secondary contact
1028 * @param string $prefix
1030 function joinAddressFromContact( $prefix = '') {
1031 $this->_from
.= " LEFT JOIN civicrm_address {$this->_aliases[$prefix . 'civicrm_address']}
1032 ON {$this->_aliases[$prefix . 'civicrm_address']}.contact_id = {$this->_aliases[$prefix . 'civicrm_contact']}.id";
1035 function joinPriceFieldValueFromLineItem() {
1036 $this->_from
.= " LEFT JOIN civicrm_price_field_value {$this->_aliases['civicrm_price_field_value']}
1037 ON {$this->_aliases['civicrm_line_item']}.price_field_value_id = {$this->_aliases['civicrm_price_field_value']}.id";
1040 function joinPriceFieldFromLineItem() {
1042 LEFT JOIN civicrm_price_field {$this->_aliases['civicrm_price_field']}
1043 ON {$this->_aliases['civicrm_line_item']}.price_field_id = {$this->_aliases['civicrm_price_field']}.id
1048 * Define join from line item table to participant table
1050 function joinParticipantFromLineItem() {
1051 $this->_from
.= " LEFT JOIN civicrm_participant {$this->_aliases['civicrm_participant']}
1052 ON ( {$this->_aliases['civicrm_line_item']}.entity_id = {$this->_aliases['civicrm_participant']}.id
1053 AND {$this->_aliases['civicrm_line_item']}.entity_table = 'civicrm_participant')
1058 * Define join from line item table to Membership table. Seems to be still via contribution
1059 * as the entity. Have made 'inner' to restrict does that make sense?
1061 function joinMembershipFromLineItem() {
1062 $this->_from
.= " INNER JOIN civicrm_contribution {$this->_aliases['civicrm_contribution']}
1063 ON ( {$this->_aliases['civicrm_line_item']}.entity_id = {$this->_aliases['civicrm_contribution']}.id
1064 AND {$this->_aliases['civicrm_line_item']}.entity_table = 'civicrm_contribution')
1065 LEFT JOIN civicrm_membership_payment pp
1066 ON {$this->_aliases['civicrm_contribution']}.id = pp.contribution_id
1067 LEFT JOIN civicrm_membership {$this->_aliases['civicrm_membership']}
1068 ON pp.membership_id = {$this->_aliases['civicrm_membership']}.id
1073 * Define join from Participant to Contribution table
1075 function joinContributionFromParticipant() {
1076 $this->_from
.= " LEFT JOIN civicrm_participant_payment pp
1077 ON {$this->_aliases['civicrm_participant']}.id = pp.participant_id
1078 LEFT JOIN civicrm_contribution {$this->_aliases['civicrm_contribution']}
1079 ON pp.contribution_id = {$this->_aliases['civicrm_contribution']}.id
1084 * Define join from Membership to Contribution table
1086 function joinContributionFromMembership() {
1087 $this->_from
.= " LEFT JOIN civicrm_membership_payment pp
1088 ON {$this->_aliases['civicrm_membership']}.id = pp.membership_id
1089 LEFT JOIN civicrm_contribution {$this->_aliases['civicrm_contribution']}
1090 ON pp.contribution_id = {$this->_aliases['civicrm_contribution']}.id
1094 function joinParticipantFromContribution() {
1095 $this->_from
.= " LEFT JOIN civicrm_participant_payment pp
1096 ON {$this->_aliases['civicrm_contribution']}.id = pp.contribution_id
1097 LEFT JOIN civicrm_participant {$this->_aliases['civicrm_participant']}
1098 ON pp.participant_id = {$this->_aliases['civicrm_participant']}.id";
1101 function joinMembershipFromContribution() {
1103 LEFT JOIN civicrm_membership_payment pp
1104 ON {$this->_aliases['civicrm_contribution']}.id = pp.contribution_id
1105 LEFT JOIN civicrm_membership {$this->_aliases['civicrm_membership']}
1106 ON pp.membership_id = {$this->_aliases['civicrm_membership']}.id";
1109 function joinMembershipTypeFromMembership() {
1111 LEFT JOIN civicrm_membership_type {$this->_aliases['civicrm_membership_type']}
1112 ON {$this->_aliases['civicrm_membership']}.membership_type_id = {$this->_aliases['civicrm_membership_type']}.id
1116 function joinContributionFromLineItem() {
1118 // this can be stored as a temp table & indexed for more speed. Not done at this state.
1119 // 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
1120 $this->_from
.= " LEFT JOIN (SELECT line_item_civireport.id as lid, contribution_civireport_direct.*
1121 FROM civicrm_line_item line_item_civireport
1122 LEFT JOIN civicrm_contribution contribution_civireport_direct
1123 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')
1126 WHERE contribution_civireport_direct.id IS NOT NULL
1128 UNION SELECT line_item_civireport.id as lid, contribution_civireport.*
1129 FROM civicrm_line_item line_item_civireport
1130 LEFT JOIN civicrm_participant participant_civireport
1131 ON (line_item_civireport.line_total > 0 AND line_item_civireport.entity_id = participant_civireport.id AND line_item_civireport.entity_table = 'civicrm_participant')
1133 LEFT JOIN civicrm_participant_payment pp
1134 ON participant_civireport.id = pp.participant_id
1135 LEFT JOIN civicrm_contribution contribution_civireport
1136 ON pp.contribution_id = contribution_civireport.id
1138 UNION SELECT line_item_civireport.id as lid,contribution_civireport.*
1139 FROM civicrm_line_item line_item_civireport
1140 LEFT JOIN civicrm_membership membership_civireport
1141 ON (line_item_civireport.line_total > 0 AND line_item_civireport.entity_id =membership_civireport.id AND line_item_civireport.entity_table = 'civicrm_membership')
1143 LEFT JOIN civicrm_membership_payment pp
1144 ON membership_civireport.id = pp.membership_id
1145 LEFT JOIN civicrm_contribution contribution_civireport
1146 ON pp.contribution_id = contribution_civireport.id
1147 ) as {$this->_aliases['civicrm_contribution']}
1148 ON {$this->_aliases['civicrm_contribution']}.lid = {$this->_aliases['civicrm_line_item']}.id
1152 function joinLineItemFromContribution() {
1154 // this can be stored as a temp table & indexed for more speed. Not done at this stage.
1155 // 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
1158 SELECT contribution_civireport_direct.id AS contid, line_item_civireport.*
1159 FROM civicrm_contribution contribution_civireport_direct
1160 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')
1161 WHERE line_item_civireport.id IS NOT NULL
1164 SELECT contribution_civireport_direct.id AS contid, line_item_civireport.*
1165 FROM civicrm_contribution contribution_civireport_direct
1166 LEFT JOIN civicrm_participant_payment pp ON contribution_civireport_direct.id = pp.contribution_id
1167 LEFT JOIN civicrm_participant p ON pp.participant_id = p.id
1168 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')
1169 WHERE line_item_civireport.id IS NOT NULL
1173 SELECT contribution_civireport_direct.id AS contid, line_item_civireport.*
1174 FROM civicrm_contribution contribution_civireport_direct
1175 LEFT JOIN civicrm_membership_payment pp ON contribution_civireport_direct.id = pp.contribution_id
1176 LEFT JOIN civicrm_membership p ON pp.membership_id = p.id
1177 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')
1178 WHERE line_item_civireport.id IS NOT NULL
1179 ) as {$this->_aliases['civicrm_line_item']}
1180 ON {$this->_aliases['civicrm_line_item']}.contid = {$this->_aliases['civicrm_contribution']}.id
1186 function joinLineItemFromMembership() {
1188 // this can be stored as a temp table & indexed for more speed. Not done at this stage.
1189 // 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
1192 SELECT contribution_civireport_direct.id AS contid, line_item_civireport.*
1193 FROM civicrm_contribution contribution_civireport_direct
1194 LEFT JOIN civicrm_line_item line_item_civireport
1195 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')
1197 WHERE line_item_civireport.id IS NOT NULL
1201 SELECT contribution_civireport_direct.id AS contid, line_item_civireport.*
1202 FROM civicrm_contribution contribution_civireport_direct
1203 LEFT JOIN civicrm_membership_payment pp ON contribution_civireport_direct.id = pp.contribution_id
1204 LEFT JOIN civicrm_membership p ON pp.membership_id = p.id
1205 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')
1206 WHERE line_item_civireport.id IS NOT NULL
1207 ) as {$this->_aliases['civicrm_line_item']}
1208 ON {$this->_aliases['civicrm_line_item']}.contid = {$this->_aliases['civicrm_contribution']}.id
1212 function joinContactFromParticipant() {
1213 $this->_from
.= " LEFT JOIN civicrm_contact {$this->_aliases['civicrm_contact']}
1214 ON {$this->_aliases['civicrm_participant']}.contact_id = {$this->_aliases['civicrm_contact']}.id";
1217 function joinContactFromMembership() {
1218 $this->_from
.= " LEFT JOIN civicrm_contact {$this->_aliases['civicrm_contact']}
1219 ON {$this->_aliases['civicrm_membership']}.contact_id = {$this->_aliases['civicrm_contact']}.id";
1222 function joinContactFromContribution() {
1223 $this->_from
.= " LEFT JOIN civicrm_contact {$this->_aliases['civicrm_contact']}
1224 ON {$this->_aliases['civicrm_contribution']}.contact_id = {$this->_aliases['civicrm_contact']}.id";
1227 function joinEventFromParticipant() {
1228 $this->_from
.= " LEFT JOIN civicrm_event {$this->_aliases['civicrm_event']}
1229 ON ({$this->_aliases['civicrm_event']}.id = {$this->_aliases['civicrm_participant']}.event_id ) AND
1230 ({$this->_aliases['civicrm_event']}.is_template IS NULL OR
1231 {$this->_aliases['civicrm_event']}.is_template = 0)";
1235 * Retrieve text for financial type from pseudoconstant
1243 function alterNickName($value, &$row) {
1244 if(empty($row['civicrm_contact_id'])){
1247 $contactID = $row['civicrm_contact_id'];
1248 return "<div id=contact-{$contactID} class='crm-entity'>
1249 <span class='crm-editable crmf-nick_name crm-editable-enabled' data-action='create'>
1250 " . $value . "</span></div>";
1254 * Retrieve text for contribution type from pseudoconstant
1260 * @return array|string
1262 function alterContributionType($value, &$row) {
1263 return is_string(CRM_Contribute_PseudoConstant
::financialType($value, FALSE)) ? CRM_Contribute_PseudoConstant
::financialType($value, FALSE) : '';
1267 * Retrieve text for contribution status from pseudoconstant
1275 function alterContributionStatus($value, &$row) {
1276 return CRM_Contribute_PseudoConstant
::contributionStatus($value);
1280 * Retrieve text for payment instrument from pseudoconstant
1288 function alterEventType($value, &$row) {
1289 return CRM_Event_PseudoConstant
::eventType($value);
1296 * @return array|string
1298 function alterEventID($value, &$row) {
1299 return is_string(CRM_Event_PseudoConstant
::event($value, FALSE)) ? CRM_Event_PseudoConstant
::event($value, FALSE) : '';
1306 * @return array|string
1308 function alterMembershipTypeID($value, &$row) {
1309 return is_string(CRM_Member_PseudoConstant
::membershipType($value, FALSE)) ? CRM_Member_PseudoConstant
::membershipType($value, FALSE) : '';
1316 * @return array|string
1318 function alterMembershipStatusID($value, &$row) {
1319 return is_string(CRM_Member_PseudoConstant
::membershipStatus($value, FALSE)) ? CRM_Member_PseudoConstant
::membershipStatus($value, FALSE) : '';
1325 * @param $selectedfield
1326 * @param $criteriaFieldName
1330 function alterCountryID($value, &$row, $selectedfield, $criteriaFieldName) {
1331 $url = CRM_Utils_System
::url(CRM_Utils_System
::currentPath(), "reset=1&force=1&{$criteriaFieldName}_op=in&{$criteriaFieldName}_value={$value}", $this->_absoluteUrl
);
1332 $row[$selectedfield . '_link'] = $url;
1333 $row[$selectedfield . '_hover'] = ts("%1 for this country.", array(1 => $value));
1334 $countries = CRM_Core_PseudoConstant
::country($value, FALSE);
1335 if(!is_array($countries)){
1343 * @param $selectedfield
1344 * @param $criteriaFieldName
1348 function alterCountyID($value, &$row,$selectedfield, $criteriaFieldName) {
1349 $url = CRM_Utils_System
::url(CRM_Utils_System
::currentPath(), "reset=1&force=1&{$criteriaFieldName}_op=in&{$criteriaFieldName}_value={$value}", $this->_absoluteUrl
);
1350 $row[$selectedfield . '_link'] = $url;
1351 $row[$selectedfield . '_hover'] = ts("%1 for this county.", array(1 => $value));
1352 $counties = CRM_Core_PseudoConstant
::county($value, FALSE);
1353 if(!is_array($counties)){
1361 * @param $selectedfield
1362 * @param $criteriaFieldName
1366 function alterStateProvinceID($value, &$row, $selectedfield, $criteriaFieldName) {
1367 $url = CRM_Utils_System
::url(CRM_Utils_System
::currentPath(), "reset=1&force=1&{$criteriaFieldName}_op=in&{$criteriaFieldName}_value={$value}", $this->_absoluteUrl
);
1368 $row[$selectedfield . '_link'] = $url;
1369 $row[$selectedfield . '_hover'] = ts("%1 for this state.", array(1 => $value));
1371 $states = CRM_Core_PseudoConstant
::stateProvince($value, FALSE);
1372 if(!is_array($states)){
1384 function alterContactID($value, &$row, $fieldname) {
1385 $row[$fieldname . '_link'] = CRM_Utils_System
::url("civicrm/contact/view", 'reset=1&cid=' . $value, $this->_absoluteUrl
);
1394 function alterParticipantStatus($value) {
1395 if (empty($value)) {
1398 return CRM_Event_PseudoConstant
::participantStatus($value, FALSE, 'label');
1406 function alterParticipantRole($value) {
1407 if (empty($value)) {
1410 $roles = explode(CRM_Core_DAO
::VALUE_SEPARATOR
, $value);
1412 foreach ($roles as $role) {
1413 $value[$role] = CRM_Event_PseudoConstant
::participantRole($role, FALSE);
1415 return implode(', ', $value);
1423 function alterPaymentType($value) {
1424 $paymentInstruments = CRM_Contribute_PseudoConstant
::paymentInstrument();
1425 return $paymentInstruments[$value];