3 +--------------------------------------------------------------------+
4 | CiviCRM version 4.7 |
5 +--------------------------------------------------------------------+
6 | Copyright CiviCRM LLC (c) 2004-2018 |
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-2018
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';
48 public function __construct() {
49 parent
::__construct();
55 public function preProcess() {
62 public function select() {
68 * From clause build where baseTable & fromClauses are defined
70 public function from() {
71 if (!empty($this->_baseTable
)) {
72 $this->buildACLClause($this->_aliases
['civicrm_contact']);
73 $this->_from
= "FROM {$this->_baseTable} {$this->_aliases[$this->_baseTable]}";
74 $availableClauses = $this->getAvailableJoins();
75 foreach ($this->fromClauses() as $fromClause) {
76 $fn = $availableClauses[$fromClause]['callback'];
79 if (strstr($this->_from
, 'civicrm_contact')) {
80 $this->_from
.= $this->_aclFrom
;
86 * Define any from clauses in use (child classes to override)
90 public function fromClauses() {
94 public function groupBy() {
96 //@todo - need to re-visit this - bad behaviour from pa
97 if ($this->_groupBy
== 'GROUP BY') {
98 $this->_groupBY
= NULL;
100 // if a stat field has been selected the do a group by
101 if (!empty($this->_statFields
) && empty($this->_groupBy
)) {
102 $this->_groupBy
[] = $this->_aliases
[$this->_baseTable
] . ".id";
104 //@todo - this should be in the parent function or at parent level - perhaps build query should do this?
105 if (!empty($this->_groupBy
) && is_array($this->_groupBy
)) {
106 $this->_groupBy
= 'GROUP BY ' . implode(',', $this->_groupBy
);
110 public function orderBy() {
119 public function statistics(&$rows) {
120 return parent
::statistics($rows);
123 public function postProcess() {
124 if (!empty($this->_aclTable
) && !empty($this->_aliases
[$this->_aclTable
])) {
125 $this->buildACLClause($this->_aliases
[$this->_aclTable
]);
127 parent
::postProcess();
131 * Alter display of rows.
133 * Iterate through the rows retrieved via SQL and make changes for display purposes,
134 * such as rendering contacts as links.
137 * Rows generated by SQL, with an array for each row.
139 public function alterDisplay(&$rows) {
140 parent
::alterDisplay($rows);
142 //THis is all generic functionality which can hopefully go into the parent class
143 // it introduces the option of defining an alter display function as part of the column definition
144 // @tod tidy up the iteration so it happens in this function
145 list($firstRow) = $rows;
146 // no result to alter
147 if (empty($firstRow)) {
150 $selectedFields = array_keys($firstRow);
152 $alterfunctions = $altermap = array();
153 foreach ($this->_columns
as $tablename => $table) {
154 if (array_key_exists('fields', $table)) {
155 foreach ($table['fields'] as $field => $specs) {
156 if (in_array($tablename . '_' . $field, $selectedFields) &&
157 array_key_exists('alter_display', $specs)
159 $alterfunctions[$tablename . '_' .
160 $field] = $specs['alter_display'];
161 $altermap[$tablename . '_' . $field] = $field;
166 if (empty($alterfunctions)) {
167 // - no manipulation to be done
171 foreach ($rows as $index => & $row) {
172 foreach ($row as $selectedfield => $value) {
173 if (array_key_exists($selectedfield, $alterfunctions)) {
174 $rows[$index][$selectedfield] = $this->{$alterfunctions[$selectedfield]}($value, $row, $selectedfield, $altermap[$selectedfield]);
183 public function getLineItemColumns() {
185 'civicrm_line_item' => array(
186 'dao' => 'CRM_Price_BAO_LineItem',
189 'title' => ts('Quantity'),
190 'type' => CRM_Utils_Type
::T_INT
,
191 'statistics' => array('sum' => ts('Total Quantity Selected')),
193 'unit_price' => array(
194 'title' => ts('Unit Price'),
196 'line_total' => array(
197 'title' => ts('Line Total'),
198 'type' => CRM_Utils_Type
::T_MONEY
,
199 'statistics' => array('sum' => ts('Total of Line Items')),
202 'participant_count' => array(
203 'title' => ts('Participant Count'),
204 'statistics' => array('sum' => ts('Total Participants')),
208 'title' => ts('Quantity'),
209 'type' => CRM_Utils_Type
::T_INT
,
210 'operator' => CRM_Report_Form
::OP_INT
,
213 'group_bys' => array(
214 'price_field_id' => array(
215 'title' => ts('Price Field'),
217 'price_field_value_id' => array(
218 'title' => ts('Price Field Option'),
220 'line_item_id' => array(
221 'title' => ts('Individual Line Item'),
232 public function getPriceFieldValueColumns() {
234 'civicrm_price_field_value' => array(
235 'dao' => 'CRM_Price_BAO_PriceFieldValue',
237 'price_field_value_label' => array(
238 'title' => ts('Price Field Value Label'),
243 'price_field_value_label' => array(
244 'title' => ts('Price Fields Value Label'),
245 'type' => CRM_Utils_Type
::T_STRING
,
246 'operator' => 'like',
250 'order_bys' => array(
252 'title' => ts('Price Field Value Label'),
255 'group_bys' => //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' => array(
261 'title' => ts('Price Field Value Label'),
272 public function getPriceFieldColumns() {
274 'civicrm_price_field' => array(
275 'dao' => 'CRM_Price_BAO_PriceField',
277 'price_field_label' => array(
278 'title' => ts('Price Field Label'),
283 'price_field_label' => array(
284 'title' => ts('Price Field Label'),
285 'type' => CRM_Utils_Type
::T_STRING
,
286 'operator' => 'like',
290 'order_bys' => array(
291 'price_field_label' => array(
292 'title' => ts('Price Field Label'),
296 'group_bys' => array(
297 'price_field_label' => array(
298 'title' => ts('Price Field Label'),
309 public function getParticipantColumns() {
310 static $_events = array();
311 if (!isset($_events['all'])) {
312 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');
315 'civicrm_participant' => array(
316 'dao' => 'CRM_Event_DAO_Participant',
318 'participant_id' => array('title' => ts('Participant ID')),
319 'participant_record' => array(
321 'title' => ts('Participant ID'),
324 'title' => ts('Event ID'),
325 'type' => CRM_Utils_Type
::T_STRING
,
326 'alter_display' => 'alterEventID',
328 'status_id' => array(
329 'title' => ts('Status'),
330 'alter_display' => 'alterParticipantStatus',
333 'title' => ts('Role'),
334 'alter_display' => 'alterParticipantRole',
336 'participant_fee_level' => NULL,
337 'participant_fee_amount' => NULL,
338 'participant_register_date' => array('title' => ts('Registration Date')),
340 'grouping' => 'event-fields',
343 'name' => 'event_id',
344 'title' => ts('Event'),
345 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
346 'options' => $_events['all'],
349 'name' => 'status_id',
350 'title' => ts('Participant Status'),
351 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
352 'options' => CRM_Event_PseudoConstant
::participantStatus(NULL, NULL, 'label'),
356 'title' => ts('Participant Role'),
357 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
358 'options' => CRM_Event_PseudoConstant
::participantRole(),
360 'participant_register_date' => array(
361 'title' => ts('Registration Date'),
362 'operatorType' => CRM_Report_Form
::OP_DATE
,
365 'order_bys' => array(
367 'title' => ts('Event'),
368 'default_weight' => '1',
369 'default_order' => 'ASC',
372 'group_bys' => array(
373 'event_id' => array('title' => ts('Event')),
382 public function getMembershipColumns() {
384 'civicrm_membership' => array(
385 'dao' => 'CRM_Member_DAO_Membership',
386 'grouping' => 'member-fields',
388 'membership_type_id' => array(
389 'title' => ts('Membership Type'),
391 'alter_display' => 'alterMembershipTypeID',
393 'status_id' => array(
394 'title' => ts('Membership Status'),
396 'alter_display' => 'alterMembershipStatusID',
399 'start_date' => array(
400 'title' => ts('Current Cycle Start Date'),
403 'title' => ts('Current Membership Cycle End Date'),
406 'group_bys' => array(
407 'membership_type_id' => array(
408 'title' => ts('Membership Type'),
412 'join_date' => array(
413 'type' => CRM_Utils_Type
::T_DATE
,
414 'operatorType' => CRM_Report_Form
::OP_DATE
,
424 public function getMembershipTypeColumns() {
426 'civicrm_membership_type' => array(
427 'dao' => 'CRM_Member_DAO_MembershipType',
428 'grouping' => 'member-fields',
432 'title' => ts('Membership Types'),
433 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
434 'type' => CRM_Utils_Type
::T_INT + CRM_Utils_Type
::T_ENUM
,
435 'options' => CRM_Member_PseudoConstant
::membershipType(),
445 public function getEventColumns() {
447 'civicrm_event' => array(
448 'dao' => 'CRM_Event_DAO_Event',
451 'no_display' => TRUE,
455 'title' => ts('Event Title'),
458 'event_type_id' => array(
459 'title' => ts('Event Type'),
461 'alter_display' => 'alterEventType',
463 'fee_label' => array('title' => ts('Fee Label')),
464 'event_start_date' => array(
465 'title' => ts('Event Start Date'),
467 'event_end_date' => array('title' => ts('Event End Date')),
468 'max_participants' => array(
469 '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 public function getContributionColumns() {
508 'civicrm_contribution' => array(
509 'dao' => 'CRM_Contribute_DAO_Contribution',
511 'contribution_id' => array(
514 'financial_type_id' => array(
515 'title' => ts('Financial Type'),
517 'alter_display' => 'alterContributionType',
519 'payment_instrument_id' => array(
520 'title' => ts('Payment Method'),
521 'alter_display' => 'alterPaymentType',
523 'source' => array('title' => ts('Contribution Source')),
525 'receive_date' => array('default' => TRUE),
526 'receipt_date' => NULL,
527 'fee_amount' => NULL,
528 'net_amount' => NULL,
529 'total_amount' => array(
530 'title' => ts('Amount'),
531 'statistics' => array('sum' => ts('Total Amount')),
532 'type' => CRM_Utils_Type
::T_MONEY
,
536 'receive_date' => array('operatorType' => CRM_Report_Form
::OP_DATE
),
537 'financial_type_id' => array(
538 'title' => ts('Financial Type'),
539 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
540 'options' => CRM_Contribute_PseudoConstant
::financialType(),
542 'payment_instrument_id' => array(
543 'title' => ts('Payment Type'),
544 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
545 'options' => CRM_Contribute_PseudoConstant
::paymentInstrument(),
547 'contribution_status_id' => array(
548 'title' => ts('Contribution Status'),
549 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
550 'options' => CRM_Contribute_PseudoConstant
::contributionStatus(),
552 'total_amount' => array('title' => ts('Contribution Amount')),
554 'order_bys' => array(
555 'payment_instrument_id' => array(
556 'title' => ts('Payment Method'),
558 'financial_type_id' => array(
559 'title' => ts('Financial Type'),
562 'group_bys' => array(
563 'financial_type_id' => array('title' => ts('Financial Type')),
564 'payment_instrument_id' => array('title' => ts('Payment Method')),
565 'contribution_id' => array(
566 'title' => ts('Individual Contribution'),
569 'source' => array('title' => ts('Contribution Source')),
571 'grouping' => 'contribution-fields',
579 public function getContactColumns() {
581 'civicrm_contact' => array(
582 'dao' => 'CRM_Contact_DAO_Contact',
584 'display_name' => array(
585 'title' => ts('Contact Name'),
588 'title' => ts('Contact ID'),
589 'alter_display' => 'alterContactID',
591 'first_name' => array(
592 'title' => ts('First Name'),
594 'last_name' => array(
595 'title' => ts('Last Name'),
597 'nick_name' => array(
598 'title' => ts('Nickname'),
599 'alter_display' => 'alterNickname',
604 'title' => ts('Contact ID'),
606 'sort_name' => array(
607 'title' => ts('Contact Name'),
610 'grouping' => 'contact-fields',
611 'order_bys' => array(
612 'sort_name' => array(
613 'title' => ts('Last Name, First Name'),
615 'default_weight' => '0',
616 'default_order' => 'ASC',
626 public function getCaseColumns() {
628 'civicrm_case' => array(
629 'dao' => 'CRM_Case_DAO_Case',
632 'title' => ts('Case ID'),
636 'title' => ts('Case Subject'),
639 'status_id' => array(
640 'title' => ts('Status'),
643 'case_type_id' => array(
644 'title' => ts('Case Type'),
647 'case_start_date' => array(
648 'title' => ts('Case Start Date'),
649 'name' => 'start_date',
652 'case_end_date' => array(
653 'title' => ts('Case End Date'),
654 'name' => 'end_date',
657 'case_duration' => array(
658 'name' => 'duration',
659 'title' => ts('Duration (Days)'),
662 'case_is_deleted' => array(
663 'name' => 'is_deleted',
664 'title' => ts('Case Deleted?'),
666 'type' => CRM_Utils_Type
::T_INT
,
670 'case_start_date' => array(
671 'title' => ts('Case Start Date'),
672 'operatorType' => CRM_Report_Form
::OP_DATE
,
673 'type' => CRM_Utils_Type
::T_DATE
,
674 'name' => 'start_date',
676 'case_end_date' => array(
677 'title' => ts('Case End Date'),
678 'operatorType' => CRM_Report_Form
::OP_DATE
,
679 'type' => CRM_Utils_Type
::T_DATE
,
680 'name' => 'end_date',
682 'case_type_id' => array(
683 'title' => ts('Case Type'),
684 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
685 'options' => $this->case_types
,
687 'case_status_id' => array(
688 'title' => ts('Case Status'),
689 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
690 'options' => $this->case_statuses
,
691 'name' => 'status_id',
693 'case_is_deleted' => array(
694 'title' => ts('Case Deleted?'),
695 'type' => CRM_Report_Form
::OP_INT
,
696 'operatorType' => CRM_Report_Form
::OP_SELECT
,
697 'options' => $this->deleted_labels
,
699 'name' => 'is_deleted',
707 * Get address columns to add to array.
709 * @param array $options
710 * Options for the report.
711 * - prefix prefix to add (e.g. 'honor' when getting address details for honor contact
712 * - prefix_label optional prefix lable eg. "Honoree " for front end
713 * - group_by enable these fields for group by - default false
714 * - order_by enable these fields for order by
715 * - filters enable these fields for filtering
716 * - defaults - (is this working?) values to pre-populate
719 * address columns definition
721 public function getAddressColumns($options = array()) {
722 $defaultOptions = array(
724 'prefix_label' => '',
729 'country_id' => TRUE,
733 $options = array_merge($defaultOptions, $options);
735 $addressFields = array(
736 $options['prefix'] . 'civicrm_address' => array(
737 'dao' => 'CRM_Core_DAO_Address',
738 'name' => 'civicrm_address',
739 'alias' => $options['prefix'] . 'civicrm_address',
741 $options['prefix'] . 'name' => array(
742 'title' => ts($options['prefix_label'] . 'Address Name'),
743 'default' => CRM_Utils_Array
::value('name', $options['defaults'], FALSE),
746 $options['prefix'] . 'street_address' => array(
747 'title' => ts($options['prefix_label'] . 'Street Address'),
748 'default' => CRM_Utils_Array
::value('street_address', $options['defaults'], FALSE),
749 'name' => 'street_address',
751 $options['prefix'] . 'supplemental_address_1' => array(
752 'title' => ts($options['prefix_label'] .
753 'Supplementary Address Field 1'),
754 'default' => CRM_Utils_Array
::value('supplemental_address_1', $options['defaults'], FALSE),
755 'name' => 'supplemental_address_1',
757 $options['prefix'] . 'supplemental_address_2' => array(
758 'title' => ts($options['prefix_label'] .
759 'Supplementary Address Field 2'),
760 'default' => CRM_Utils_Array
::value('supplemental_address_2', $options['defaults'], FALSE),
761 'name' => 'supplemental_address_2',
763 $options['prefix'] . 'supplemental_address_3' => array(
764 'title' => ts($options['prefix_label'] .
765 'Supplementary Address Field 3'),
766 'default' => CRM_Utils_Array
::value('supplemental_address_3', $options['defaults'], FALSE),
767 'name' => 'supplemental_address_3',
769 $options['prefix'] . 'street_number' => array(
770 'name' => 'street_number',
771 'title' => ts($options['prefix_label'] . 'Street Number'),
773 'default' => CRM_Utils_Array
::value('street_number', $options['defaults'], FALSE),
775 $options['prefix'] . 'street_name' => array(
776 'name' => 'street_name',
777 'title' => ts($options['prefix_label'] . 'Street Name'),
779 'default' => CRM_Utils_Array
::value('street_name', $options['defaults'], FALSE),
781 $options['prefix'] . 'street_unit' => array(
782 'name' => 'street_unit',
783 'title' => ts($options['prefix_label'] . 'Street Unit'),
785 'default' => CRM_Utils_Array
::value('street_unit', $options['defaults'], FALSE),
787 $options['prefix'] . 'city' => array(
788 'title' => ts($options['prefix_label'] . 'City'),
789 'default' => CRM_Utils_Array
::value('city', $options['defaults'], FALSE),
792 $options['prefix'] . 'postal_code' => array(
793 'title' => ts($options['prefix_label'] . 'Postal Code'),
794 'default' => CRM_Utils_Array
::value('postal_code', $options['defaults'], FALSE),
795 'name' => 'postal_code',
797 $options['prefix'] . 'county_id' => array(
798 'title' => ts($options['prefix_label'] . 'County'),
799 'default' => CRM_Utils_Array
::value('county_id', $options['defaults'], FALSE),
800 'alter_display' => 'alterCountyID',
801 'name' => 'county_id',
803 $options['prefix'] . 'state_province_id' => array(
804 'title' => ts($options['prefix_label'] . 'State/Province'),
805 'default' => CRM_Utils_Array
::value('state_province_id', $options['defaults'], FALSE),
806 'alter_display' => 'alterStateProvinceID',
807 'name' => 'state_province_id',
809 $options['prefix'] . 'country_id' => array(
810 'title' => ts($options['prefix_label'] . 'Country'),
811 'default' => CRM_Utils_Array
::value('country_id', $options['defaults'], FALSE),
812 'alter_display' => 'alterCountryID',
813 'name' => 'country_id',
816 'grouping' => 'location-fields',
820 if ($options['filters']) {
821 $addressFields[$options['prefix'] . 'civicrm_address']['filters'] = array(
822 $options['prefix'] . 'street_number' => array(
823 'title' => ts($options['prefix_label'] . 'Street Number'),
825 'name' => 'street_number',
827 $options['prefix'] . 'street_name' => array(
828 'title' => ts($options['prefix_label'] . 'Street Name'),
829 'name' => $options['prefix'] . 'street_name',
830 'operator' => 'like',
832 $options['prefix'] . 'postal_code' => array(
833 'title' => ts($options['prefix_label'] . 'Postal Code'),
835 'name' => 'postal_code',
837 $options['prefix'] . 'city' => array(
838 'title' => ts($options['prefix_label'] . 'City'),
839 'operator' => 'like',
842 $options['prefix'] . 'county_id' => array(
843 'name' => 'county_id',
844 'title' => ts($options['prefix_label'] . 'County'),
845 'type' => CRM_Utils_Type
::T_INT
,
846 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
847 'options' => CRM_Core_PseudoConstant
::county(),
849 $options['prefix'] . 'state_province_id' => array(
850 'name' => 'state_province_id',
851 'title' => ts($options['prefix_label'] . 'State/Province'),
852 'type' => CRM_Utils_Type
::T_INT
,
853 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
854 'options' => CRM_Core_PseudoConstant
::stateProvince(),
856 $options['prefix'] . 'country_id' => array(
857 'name' => 'country_id',
858 'title' => ts($options['prefix_label'] . 'Country'),
859 'type' => CRM_Utils_Type
::T_INT
,
860 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
861 'options' => CRM_Core_PseudoConstant
::country(),
866 if ($options['order_by']) {
867 $addressFields[$options['prefix'] .
868 'civicrm_address']['order_bys'] = array(
869 $options['prefix'] . 'street_name' => array(
870 'title' => ts($options['prefix_label'] . 'Street Name'),
871 'name' => 'street_name',
873 $options['prefix'] . 'street_number' => array(
874 'title' => ts($options['prefix_label'] . 'Odd / Even Street Number'),
875 'name' => 'street_number',
877 $options['prefix'] . 'street_address' => array(
878 'title' => ts($options['prefix_label'] . 'Street Address'),
879 'name' => 'street_address',
881 $options['prefix'] . 'city' => array(
882 'title' => ts($options['prefix_label'] . 'City'),
885 $options['prefix'] . 'postal_code' => array(
886 'title' => ts($options['prefix_label'] . 'Post Code'),
887 'name' => 'postal_code',
892 if ($options['group_by']) {
893 $addressFields['civicrm_address']['group_bys'] = array(
894 $options['prefix'] . 'street_address' => array(
895 'title' => ts($options['prefix_label'] . 'Street Address'),
896 'name' => 'street_address',
898 $options['prefix'] . 'city' => array(
899 'title' => ts($options['prefix_label'] . 'City'),
902 $options['prefix'] . 'postal_code' => array(
903 'title' => ts($options['prefix_label'] . 'Post Code'),
904 'name' => 'postal_code',
906 $options['prefix'] . 'state_province_id' => array(
907 'title' => ts($options['prefix_label'] . 'State/Province'),
908 'name' => 'state_province_id',
910 $options['prefix'] . 'country_id' => array(
911 'title' => ts($options['prefix_label'] . 'Country'),
912 'name' => 'country_id',
914 $options['prefix'] . 'county_id' => array(
915 'title' => ts($options['prefix_label'] . 'County'),
916 'name' => 'county_id',
920 return $addressFields;
924 * Get Information about advertised Joins.
928 public function getAvailableJoins() {
930 'priceFieldValue_from_lineItem' => array(
931 'leftTable' => 'civicrm_line_item',
932 'rightTable' => 'civicrm_price_field_value',
933 'callback' => 'joinPriceFieldValueFromLineItem',
935 'priceField_from_lineItem' => array(
936 'leftTable' => 'civicrm_line_item',
937 'rightTable' => 'civicrm_price_field',
938 'callback' => 'joinPriceFieldFromLineItem',
940 'participant_from_lineItem' => array(
941 'leftTable' => 'civicrm_line_item',
942 'rightTable' => 'civicrm_participant',
943 'callback' => 'joinParticipantFromLineItem',
945 'contribution_from_lineItem' => array(
946 'leftTable' => 'civicrm_line_item',
947 'rightTable' => 'civicrm_contribution',
948 'callback' => 'joinContributionFromLineItem',
950 'membership_from_lineItem' => array(
951 'leftTable' => 'civicrm_line_item',
952 'rightTable' => 'civicrm_membership',
953 'callback' => 'joinMembershipFromLineItem',
955 'contribution_from_participant' => array(
956 'leftTable' => 'civicrm_participant',
957 'rightTable' => 'civicrm_contribution',
958 'callback' => 'joinContributionFromParticipant',
960 'contribution_from_membership' => array(
961 'leftTable' => 'civicrm_membership',
962 'rightTable' => 'civicrm_contribution',
963 'callback' => 'joinContributionFromMembership',
965 'membership_from_contribution' => array(
966 'leftTable' => 'civicrm_contribution',
967 'rightTable' => 'civicrm_membership',
968 'callback' => 'joinMembershipFromContribution',
970 'membershipType_from_membership' => array(
971 'leftTable' => 'civicrm_membership',
972 'rightTable' => 'civicrm_membership_type',
973 'callback' => 'joinMembershipTypeFromMembership',
975 'lineItem_from_contribution' => array(
976 'leftTable' => 'civicrm_contribution',
977 'rightTable' => 'civicrm_line_item',
978 'callback' => 'joinLineItemFromContribution',
980 'lineItem_from_membership' => array(
981 'leftTable' => 'civicrm_membership',
982 'rightTable' => 'civicrm_line_item',
983 'callback' => 'joinLineItemFromMembership',
985 'contact_from_participant' => array(
986 'leftTable' => 'civicrm_participant',
987 'rightTable' => 'civicrm_contact',
988 'callback' => 'joinContactFromParticipant',
990 'contact_from_membership' => array(
991 'leftTable' => 'civicrm_membership',
992 'rightTable' => 'civicrm_contact',
993 'callback' => 'joinContactFromMembership',
995 'contact_from_contribution' => array(
996 'leftTable' => 'civicrm_contribution',
997 'rightTable' => 'civicrm_contact',
998 'callback' => 'joinContactFromContribution',
1000 'event_from_participant' => array(
1001 'leftTable' => 'civicrm_participant',
1002 'rightTable' => 'civicrm_event',
1003 'callback' => 'joinEventFromParticipant',
1005 'address_from_contact' => array(
1006 'leftTable' => 'civicrm_contact',
1007 'rightTable' => 'civicrm_address',
1008 'callback' => 'joinAddressFromContact',
1014 * Add join from contact table to address. Prefix will be added to both tables
1015 * as it's assumed you are using it to get address of a secondary contact
1017 * @param string $prefix
1019 public function joinAddressFromContact($prefix = '') {
1020 $this->_from
.= " LEFT JOIN civicrm_address {$this->_aliases[$prefix .
1022 ON {$this->_aliases[$prefix .
1023 'civicrm_address']}.contact_id = {$this->_aliases[$prefix .
1024 'civicrm_contact']}.id";
1027 public function joinPriceFieldValueFromLineItem() {
1028 $this->_from
.= " LEFT JOIN civicrm_price_field_value {$this->_aliases['civicrm_price_field_value']}
1029 ON {$this->_aliases['civicrm_line_item']}.price_field_value_id = {$this->_aliases['civicrm_price_field_value']}.id";
1032 public function joinPriceFieldFromLineItem() {
1034 LEFT JOIN civicrm_price_field {$this->_aliases['civicrm_price_field']}
1035 ON {$this->_aliases['civicrm_line_item']}.price_field_id = {$this->_aliases['civicrm_price_field']}.id
1040 * Define join from line item table to participant table.
1042 public function joinParticipantFromLineItem() {
1043 $this->_from
.= " LEFT JOIN civicrm_participant {$this->_aliases['civicrm_participant']}
1044 ON ( {$this->_aliases['civicrm_line_item']}.entity_id = {$this->_aliases['civicrm_participant']}.id
1045 AND {$this->_aliases['civicrm_line_item']}.entity_table = 'civicrm_participant')
1050 * Define join from line item table to Membership table. Seems to be still via contribution
1051 * as the entity. Have made 'inner' to restrict does that make sense?
1053 public function joinMembershipFromLineItem() {
1054 $this->_from
.= " INNER JOIN civicrm_contribution {$this->_aliases['civicrm_contribution']}
1055 ON ( {$this->_aliases['civicrm_line_item']}.entity_id = {$this->_aliases['civicrm_contribution']}.id
1056 AND {$this->_aliases['civicrm_line_item']}.entity_table = 'civicrm_contribution')
1057 LEFT JOIN civicrm_membership_payment pp
1058 ON {$this->_aliases['civicrm_contribution']}.id = pp.contribution_id
1059 LEFT JOIN civicrm_membership {$this->_aliases['civicrm_membership']}
1060 ON pp.membership_id = {$this->_aliases['civicrm_membership']}.id
1065 * Define join from Participant to Contribution table.
1067 public function joinContributionFromParticipant() {
1068 $this->_from
.= " LEFT JOIN civicrm_participant_payment pp
1069 ON {$this->_aliases['civicrm_participant']}.id = pp.participant_id
1070 LEFT JOIN civicrm_contribution {$this->_aliases['civicrm_contribution']}
1071 ON pp.contribution_id = {$this->_aliases['civicrm_contribution']}.id
1076 * Define join from Membership to Contribution table.
1078 public function joinContributionFromMembership() {
1079 $this->_from
.= " LEFT JOIN civicrm_membership_payment pp
1080 ON {$this->_aliases['civicrm_membership']}.id = pp.membership_id
1081 LEFT JOIN civicrm_contribution {$this->_aliases['civicrm_contribution']}
1082 ON pp.contribution_id = {$this->_aliases['civicrm_contribution']}.id
1086 public function joinParticipantFromContribution() {
1087 $this->_from
.= " LEFT JOIN civicrm_participant_payment pp
1088 ON {$this->_aliases['civicrm_contribution']}.id = pp.contribution_id
1089 LEFT JOIN civicrm_participant {$this->_aliases['civicrm_participant']}
1090 ON pp.participant_id = {$this->_aliases['civicrm_participant']}.id";
1093 public function joinMembershipFromContribution() {
1095 LEFT JOIN civicrm_membership_payment pp
1096 ON {$this->_aliases['civicrm_contribution']}.id = pp.contribution_id
1097 LEFT JOIN civicrm_membership {$this->_aliases['civicrm_membership']}
1098 ON pp.membership_id = {$this->_aliases['civicrm_membership']}.id";
1101 public function joinMembershipTypeFromMembership() {
1103 LEFT JOIN civicrm_membership_type {$this->_aliases['civicrm_membership_type']}
1104 ON {$this->_aliases['civicrm_membership']}.membership_type_id = {$this->_aliases['civicrm_membership_type']}.id
1108 public function joinContributionFromLineItem() {
1110 // this can be stored as a temp table & indexed for more speed. Not done at this state.
1111 // 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
1112 $this->_from
.= " LEFT JOIN (SELECT line_item_civireport.id as lid, contribution_civireport_direct.*
1113 FROM civicrm_line_item line_item_civireport
1114 LEFT JOIN civicrm_contribution contribution_civireport_direct
1115 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')
1118 WHERE contribution_civireport_direct.id IS NOT NULL
1120 UNION SELECT line_item_civireport.id as lid, contribution_civireport.*
1121 FROM civicrm_line_item line_item_civireport
1122 LEFT JOIN civicrm_participant participant_civireport
1123 ON (line_item_civireport.line_total > 0 AND line_item_civireport.entity_id = participant_civireport.id AND line_item_civireport.entity_table = 'civicrm_participant')
1125 LEFT JOIN civicrm_participant_payment pp
1126 ON participant_civireport.id = pp.participant_id
1127 LEFT JOIN civicrm_contribution contribution_civireport
1128 ON pp.contribution_id = contribution_civireport.id
1130 UNION SELECT line_item_civireport.id as lid,contribution_civireport.*
1131 FROM civicrm_line_item line_item_civireport
1132 LEFT JOIN civicrm_membership membership_civireport
1133 ON (line_item_civireport.line_total > 0 AND line_item_civireport.entity_id =membership_civireport.id AND line_item_civireport.entity_table = 'civicrm_membership')
1135 LEFT JOIN civicrm_membership_payment pp
1136 ON membership_civireport.id = pp.membership_id
1137 LEFT JOIN civicrm_contribution contribution_civireport
1138 ON pp.contribution_id = contribution_civireport.id
1139 ) as {$this->_aliases['civicrm_contribution']}
1140 ON {$this->_aliases['civicrm_contribution']}.lid = {$this->_aliases['civicrm_line_item']}.id
1144 public function joinLineItemFromContribution() {
1146 // this can be stored as a temp table & indexed for more speed. Not done at this stage.
1147 // 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
1150 SELECT contribution_civireport_direct.id AS contid, line_item_civireport.*
1151 FROM civicrm_contribution contribution_civireport_direct
1152 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')
1153 WHERE line_item_civireport.id IS NOT NULL
1156 SELECT contribution_civireport_direct.id AS contid, line_item_civireport.*
1157 FROM civicrm_contribution contribution_civireport_direct
1158 LEFT JOIN civicrm_participant_payment pp ON contribution_civireport_direct.id = pp.contribution_id
1159 LEFT JOIN civicrm_participant p ON pp.participant_id = p.id
1160 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')
1161 WHERE line_item_civireport.id IS NOT NULL
1165 SELECT contribution_civireport_direct.id AS contid, line_item_civireport.*
1166 FROM civicrm_contribution contribution_civireport_direct
1167 LEFT JOIN civicrm_membership_payment pp ON contribution_civireport_direct.id = pp.contribution_id
1168 LEFT JOIN civicrm_membership p ON pp.membership_id = p.id
1169 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')
1170 WHERE line_item_civireport.id IS NOT NULL
1171 ) as {$this->_aliases['civicrm_line_item']}
1172 ON {$this->_aliases['civicrm_line_item']}.contid = {$this->_aliases['civicrm_contribution']}.id
1178 public function joinLineItemFromMembership() {
1180 // this can be stored as a temp table & indexed for more speed. Not done at this stage.
1181 // 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
1184 SELECT contribution_civireport_direct.id AS contid, line_item_civireport.*
1185 FROM civicrm_contribution contribution_civireport_direct
1186 LEFT JOIN civicrm_line_item line_item_civireport
1187 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')
1189 WHERE line_item_civireport.id IS NOT NULL
1193 SELECT contribution_civireport_direct.id AS contid, line_item_civireport.*
1194 FROM civicrm_contribution contribution_civireport_direct
1195 LEFT JOIN civicrm_membership_payment pp ON contribution_civireport_direct.id = pp.contribution_id
1196 LEFT JOIN civicrm_membership p ON pp.membership_id = p.id
1197 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')
1198 WHERE line_item_civireport.id IS NOT NULL
1199 ) as {$this->_aliases['civicrm_line_item']}
1200 ON {$this->_aliases['civicrm_line_item']}.contid = {$this->_aliases['civicrm_contribution']}.id
1204 public function joinContactFromParticipant() {
1205 $this->_from
.= " LEFT JOIN civicrm_contact {$this->_aliases['civicrm_contact']}
1206 ON {$this->_aliases['civicrm_participant']}.contact_id = {$this->_aliases['civicrm_contact']}.id";
1209 public function joinContactFromMembership() {
1210 $this->_from
.= " LEFT JOIN civicrm_contact {$this->_aliases['civicrm_contact']}
1211 ON {$this->_aliases['civicrm_membership']}.contact_id = {$this->_aliases['civicrm_contact']}.id";
1214 public function joinContactFromContribution() {
1215 $this->_from
.= " LEFT JOIN civicrm_contact {$this->_aliases['civicrm_contact']}
1216 ON {$this->_aliases['civicrm_contribution']}.contact_id = {$this->_aliases['civicrm_contact']}.id";
1219 public function joinEventFromParticipant() {
1220 $this->_from
.= " LEFT JOIN civicrm_event {$this->_aliases['civicrm_event']}
1221 ON ({$this->_aliases['civicrm_event']}.id = {$this->_aliases['civicrm_participant']}.event_id ) AND
1222 ({$this->_aliases['civicrm_event']}.is_template IS NULL OR
1223 {$this->_aliases['civicrm_event']}.is_template = 0)";
1227 * Retrieve text for financial type from pseudoconstant.
1234 public function alterNickName($value, &$row) {
1235 if (empty($row['civicrm_contact_id'])) {
1238 $contactID = $row['civicrm_contact_id'];
1239 return "<div id=contact-{$contactID} class='crm-entity'>
1240 <span class='crm-editable crmf-nick_name crm-editable-enabled'>
1241 " . $value . "</span></div>";
1245 * Retrieve text for contribution type from pseudoconstant.
1250 * @return array|string
1252 public function alterContributionType($value, &$row) {
1253 return is_string(CRM_Contribute_PseudoConstant
::financialType($value, FALSE)) ? CRM_Contribute_PseudoConstant
::financialType($value, FALSE) : '';
1257 * Retrieve text for contribution status from pseudoconstant.
1264 public function alterContributionStatus($value, &$row) {
1265 return CRM_Contribute_PseudoConstant
::contributionStatus($value);
1269 * Retrieve text for payment instrument from pseudoconstant.
1276 public function alterEventType($value, &$row) {
1277 return CRM_Event_PseudoConstant
::eventType($value);
1284 * @return array|string
1286 public function alterEventID($value, &$row) {
1287 return is_string(CRM_Event_PseudoConstant
::event($value, FALSE)) ? CRM_Event_PseudoConstant
::event($value, FALSE) : '';
1294 * @return array|string
1296 public function alterMembershipTypeID($value, &$row) {
1297 return is_string(CRM_Member_PseudoConstant
::membershipType($value, FALSE)) ? CRM_Member_PseudoConstant
::membershipType($value, FALSE) : '';
1304 * @return array|string
1306 public function alterMembershipStatusID($value, &$row) {
1307 return is_string(CRM_Member_PseudoConstant
::membershipStatus($value, FALSE)) ? CRM_Member_PseudoConstant
::membershipStatus($value, FALSE) : '';
1313 * @param $selectedfield
1314 * @param string $criteriaFieldName
1318 public function alterCountryID($value, &$row, $selectedfield, $criteriaFieldName) {
1319 $url = CRM_Utils_System
::url(CRM_Utils_System
::currentPath(), "reset=1&force=1&{$criteriaFieldName}_op=in&{$criteriaFieldName}_value={$value}", $this->_absoluteUrl
);
1320 $row[$selectedfield . '_link'] = $url;
1321 $row[$selectedfield .
1322 '_hover'] = ts("%1 for this country.", array(1 => $value));
1323 $countries = CRM_Core_PseudoConstant
::country($value, FALSE);
1324 if (!is_array($countries)) {
1332 * @param $selectedfield
1333 * @param string $criteriaFieldName
1337 public function alterCountyID($value, &$row, $selectedfield, $criteriaFieldName) {
1338 $url = CRM_Utils_System
::url(CRM_Utils_System
::currentPath(), "reset=1&force=1&{$criteriaFieldName}_op=in&{$criteriaFieldName}_value={$value}", $this->_absoluteUrl
);
1339 $row[$selectedfield . '_link'] = $url;
1340 $row[$selectedfield .
1341 '_hover'] = ts("%1 for this county.", array(1 => $value));
1342 $counties = CRM_Core_PseudoConstant
::county($value, FALSE);
1343 if (!is_array($counties)) {
1351 * @param $selectedfield
1352 * @param string $criteriaFieldName
1356 public function alterStateProvinceID($value, &$row, $selectedfield, $criteriaFieldName) {
1357 $url = CRM_Utils_System
::url(CRM_Utils_System
::currentPath(), "reset=1&force=1&{$criteriaFieldName}_op=in&{$criteriaFieldName}_value={$value}", $this->_absoluteUrl
);
1358 $row[$selectedfield . '_link'] = $url;
1359 $row[$selectedfield .
1360 '_hover'] = ts("%1 for this state.", array(1 => $value));
1362 $states = CRM_Core_PseudoConstant
::stateProvince($value, FALSE);
1363 if (!is_array($states)) {
1371 * @param string $fieldname
1375 public function alterContactID($value, &$row, $fieldname) {
1376 $row[$fieldname . '_link'] = CRM_Utils_System
::url("civicrm/contact/view",
1377 'reset=1&cid=' . $value, $this->_absoluteUrl
);
1386 public function alterParticipantStatus($value) {
1387 if (empty($value)) {
1390 return CRM_Event_PseudoConstant
::participantStatus($value, FALSE, 'label');
1396 * @return string|void
1398 public function alterParticipantRole($value) {
1399 if (empty($value)) {
1402 $roles = explode(CRM_Core_DAO
::VALUE_SEPARATOR
, $value);
1404 foreach ($roles as $role) {
1405 $value[$role] = CRM_Event_PseudoConstant
::participantRole($role, FALSE);
1407 return implode(', ', $value);
1415 public function alterPaymentType($value) {
1416 $paymentInstruments = CRM_Contribute_PseudoConstant
::paymentInstrument();
1417 return $paymentInstruments[$value];