From 16654cd433bde6722c1b3d68843dfd21422b2be5 Mon Sep 17 00:00:00 2001 From: "Donald A. Lobo" Date: Tue, 26 Nov 2013 06:55:20 -0800 Subject: [PATCH] CRM-13838 - Report iltering by price level http://issues.civicrm.org/jira/browse/CRM-13838 --- CRM/Report/Form/Event/ParticipantListing.php | 183 ++++++++++++------- 1 file changed, 117 insertions(+), 66 deletions(-) diff --git a/CRM/Report/Form/Event/ParticipantListing.php b/CRM/Report/Form/Event/ParticipantListing.php index 39060238d0..7ec5255232 100644 --- a/CRM/Report/Form/Event/ParticipantListing.php +++ b/CRM/Report/Form/Event/ParticipantListing.php @@ -1,28 +1,29 @@ CRM_Utils_Type::T_STRING, ), 'status_id' => array('title' => ts('Status'), - 'default' => TRUE, + 'default' => TRUE, ), 'role_id' => array('title' => ts('Role'), - 'default' => TRUE, + 'default' => TRUE, ), 'fee_currency' => array( - 'required' => TRUE, - 'no_display' => TRUE, + 'required' => TRUE, + 'no_display' => TRUE, ), 'participant_fee_level' => NULL, 'participant_fee_amount' => NULL, @@ -204,9 +206,9 @@ class CRM_Report_Form_Event_ParticipantListing extends CRM_Report_Form_Event { 'filters' => array( 'event_id' => array('name' => 'event_id', - 'title' => ts('Event'), - 'operatorType' => CRM_Report_Form::OP_MULTISELECT, - 'options' => $this->getEventFilterOptions(), + 'title' => ts('Event'), + 'operatorType' => CRM_Report_Form::OP_MULTISELECT, + 'options' => $this->getEventFilterOptions(), ), 'sid' => array( 'name' => 'status_id', @@ -313,31 +315,49 @@ class CRM_Report_Form_Event_ParticipantListing extends CRM_Report_Form_Event { 'operatorType' => CRM_Report_Form::OP_DATE, ), 'financial_type_id' => array('title' => ts('Financial Type'), - 'operatorType' => CRM_Report_Form::OP_MULTISELECT, - 'options' => CRM_Contribute_PseudoConstant::financialType(), + 'operatorType' => CRM_Report_Form::OP_MULTISELECT, + 'options' => CRM_Contribute_PseudoConstant::financialType(), ), 'currency' => array('title' => ts('Contribution Currency'), - 'operatorType' => CRM_Report_Form::OP_MULTISELECT, - 'options' => CRM_Core_OptionGroup::values('currencies_enabled'), - 'default' => NULL, - 'type' => CRM_Utils_Type::T_STRING, + 'operatorType' => CRM_Report_Form::OP_MULTISELECT, + 'options' => CRM_Core_OptionGroup::values('currencies_enabled'), + 'default' => NULL, + 'type' => CRM_Utils_Type::T_STRING, ), 'payment_instrument_id' => array('title' => ts('Payment Type'), - 'operatorType' => CRM_Report_Form::OP_MULTISELECT, - 'options' => CRM_Contribute_PseudoConstant::paymentInstrument(), + 'operatorType' => CRM_Report_Form::OP_MULTISELECT, + 'options' => CRM_Contribute_PseudoConstant::paymentInstrument(), ), 'contribution_status_id' => array('title' => ts('Contribution Status'), + 'operatorType' => CRM_Report_Form::OP_MULTISELECT, + 'options' => CRM_Contribute_PseudoConstant::contributionStatus(), + 'default' => NULL + ), + ), + ), + + 'civicrm_line_item' => array( + 'dao' => 'CRM_Price_DAO_LineItem', + 'grouping' => 'priceset-fields', + 'filters' => array( + 'price_field_value_id' => array( + 'name' => 'price_field_value_id', + 'title' => ts('Fee Level'), 'operatorType' => CRM_Report_Form::OP_MULTISELECT, - 'options' => CRM_Contribute_PseudoConstant::contributionStatus(), - 'default' => NULL + 'options' => $this->getPriceLevels(), ), ), ), ); - $this->_options = array('blank_column_begin' => array('title' => ts('Blank column at the Begining'), + + + $this->_options = array( + 'blank_column_begin' => array( + 'title' => ts('Blank column at the Begining'), 'type' => 'checkbox', ), - 'blank_column_end' => array('title' => ts('Blank column at the End'), + 'blank_column_end' => array( + 'title' => ts('Blank column at the End'), 'type' => 'select', 'options' => array( '' => '-select-', @@ -350,15 +370,19 @@ class CRM_Report_Form_Event_ParticipantListing extends CRM_Report_Form_Event { // If we have active campaigns add those elements to both the fields and filters if ($campaignEnabled && !empty($this->activeCampaigns)) { - $this->_columns['civicrm_participant']['fields']['campaign_id'] = array( - 'title' => ts('Campaign'), - 'default' => 'false', - ); - $this->_columns['civicrm_participant']['filters']['campaign_id'] = array('title' => ts('Campaign'), - 'operatorType' => CRM_Report_Form::OP_MULTISELECT, - 'options' => $this->activeCampaigns, - ); - $this->_columns['civicrm_participant']['order_bys']['campaign_id'] = array('title' => ts('Campaign')); + $this->_columns['civicrm_participant']['fields']['campaign_id'] = + array( + 'title' => ts('Campaign'), + 'default' => 'false', + ); + $this->_columns['civicrm_participant']['filters']['campaign_id'] = + array( + 'title' => ts('Campaign'), + 'operatorType' => CRM_Report_Form::OP_MULTISELECT, + 'options' => $this->activeCampaigns, + ); + $this->_columns['civicrm_participant']['order_bys']['campaign_id'] = + array('title' => ts('Campaign')); } @@ -366,6 +390,24 @@ class CRM_Report_Form_Event_ParticipantListing extends CRM_Report_Form_Event { parent::__construct(); } + function getPriceLevels() { + $query = " +SELECT DISTINCT cv.label, cv.id +FROM civicrm_price_field_value cv +LEFT JOIN civicrm_price_field cf ON cv.price_field_id = cf.id +LEFT JOIN civicrm_price_set_entity ce ON ce.price_set_id = cf.price_set_id +WHERE ce.entity_table = 'civicrm_event' +GROUP BY cv.label +"; + $dao = CRM_Core_DAO::executeQuery($query); + $elements = array(); + while ($dao->fetch()) { + $elements[$dao->id] = "$dao->label\n"; + } + + return $elements; + } //searches database for priceset values + function preProcess() { parent::preProcess(); @@ -377,17 +419,19 @@ class CRM_Report_Form_Event_ParticipantListing extends CRM_Report_Form_Event { //add blank column at the Start if (array_key_exists('options', $this->_params) && - CRM_Utils_Array::value('blank_column_begin', $this->_params['options'])) { + CRM_Utils_Array::value('blank_column_begin', $this->_params['options'])) { $select[] = " '' as blankColumnBegin"; $this->_columnHeaders['blankColumnBegin']['title'] = '_ _ _ _'; } foreach ($this->_columns as $tableName => $table) { + if ($tableName == 'civicrm_line_item'){ + $this->_lineitemField = TRUE; + } if (array_key_exists('fields', $table)) { foreach ($table['fields'] as $fieldName => $field) { if (CRM_Utils_Array::value('required', $field) || CRM_Utils_Array::value($fieldName, $this->_params['fields']) ) { - if ($tableName == 'civicrm_contribution') { $this->_contribField = TRUE; } @@ -446,6 +490,12 @@ class CRM_Report_Form_Event_ParticipantListing extends CRM_Report_Form_Event { ON (pp.contribution_id = {$this->_aliases['civicrm_contribution']}.id) "; } + if ($this->_lineitemField){ + $this->_from .= " + LEFT JOIN civicrm_line_item line_item_civireport + ON line_item_civireport.entity_id = {$this->_aliases['civicrm_participant']}.id + "; + } } function where() { @@ -477,10 +527,10 @@ class CRM_Report_Form_Event_ParticipantListing extends CRM_Report_Form_Event { if ($op) { $clause = $this->whereClause($field, - $op, - CRM_Utils_Array::value("{$fieldName}_value", $this->_params), - CRM_Utils_Array::value("{$fieldName}_min", $this->_params), - CRM_Utils_Array::value("{$fieldName}_max", $this->_params) + $op, + CRM_Utils_Array::value("{$fieldName}_value", $this->_params), + CRM_Utils_Array::value("{$fieldName}_min", $this->_params), + CRM_Utils_Array::value("{$fieldName}_max", $this->_params) ); } } @@ -512,6 +562,7 @@ class CRM_Report_Form_Event_ParticipantListing extends CRM_Report_Form_Event { // build query $sql = $this->buildQuery(TRUE); + // build array of result based on column headers. This method also allows // modifying column headers before using it to build result set i.e $rows. $this->buildRows($sql, $rows); @@ -546,8 +597,8 @@ class CRM_Report_Form_Event_ParticipantListing extends CRM_Report_Form_Event { $rows[$rowNum]['civicrm_participant_event_id'] = CRM_Event_PseudoConstant::event($value, FALSE); $url = CRM_Report_Utils_Report::getNextUrl('event/income', - 'reset=1&force=1&id_op=in&id_value=' . $value, - $this->_absoluteUrl, $this->_id, $this->_drilldownReport + 'reset=1&force=1&id_op=in&id_value=' . $value, + $this->_absoluteUrl, $this->_id, $this->_drilldownReport ); $rows[$rowNum]['civicrm_participant_event_id_link'] = $url; $rows[$rowNum]['civicrm_participant_event_id_hover'] = ts("View Event Income Details for this Event"); @@ -599,12 +650,12 @@ class CRM_Report_Form_Event_ParticipantListing extends CRM_Report_Form_Event { ($id = CRM_Utils_Array::value('civicrm_participant_participant_record', $row)) ) { $url = CRM_Report_Utils_Report::getNextUrl('contact/detail', - "reset=1&force=1&id_op=eq&id_value=$cid", - $this->_absoluteUrl, $this->_id, $this->_drilldownReport + "reset=1&force=1&id_op=eq&id_value=$cid", + $this->_absoluteUrl, $this->_id, $this->_drilldownReport ); $viewUrl = CRM_Utils_System::url("civicrm/contact/view/participant", - "reset=1&id=$id&cid=$cid&action=view&context=participant" + "reset=1&id=$id&cid=$cid&action=view&context=participant" ); $contactTitle = ts('View Contact Details'); @@ -638,7 +689,7 @@ class CRM_Report_Form_Event_ParticipantListing extends CRM_Report_Form_Event { if ($value = $row['civicrm_contact_employer_id']) { $rows[$rowNum]['civicrm_contact_employer_id'] = CRM_Contact_BAO_Contact::displayName($value); $url = CRM_Utils_System::url('civicrm/contact/view', - 'reset=1&cid=' . $value, $this->_absoluteUrl + 'reset=1&cid=' . $value, $this->_absoluteUrl ); $rows[$rowNum]['civicrm_contact_employer_id_link'] = $url; $rows[$rowNum]['civicrm_contact_employer_id_hover'] = ts('View Contact Summary for this Contact.'); @@ -683,7 +734,7 @@ class CRM_Report_Form_Event_ParticipantListing extends CRM_Report_Form_Event { } $entryFound = TRUE; } - + if (array_key_exists('civicrm_contact_gender_id', $row)) { if ($value = $row['civicrm_contact_gender_id']) { $rows[$rowNum]['civicrm_contact_gender_id'] = $genders[$value]; @@ -694,7 +745,7 @@ class CRM_Report_Form_Event_ParticipantListing extends CRM_Report_Form_Event { // display birthday in the configured custom format if (array_key_exists('civicrm_contact_birth_date', $row)) { if ($value = $row['civicrm_contact_birth_date']) { - $rows[$rowNum]['civicrm_contact_birth_date'] = CRM_Utils_Date::customFormat($row['civicrm_contact_birth_date'], '%Y%m%d'); + $rows[$rowNum]['civicrm_contact_birth_date'] = CRM_Utils_Date::customFormat($row['civicrm_contact_birth_date'], '%Y%m%d'); } $entryFound = TRUE; } @@ -706,4 +757,4 @@ class CRM_Report_Form_Event_ParticipantListing extends CRM_Report_Form_Event { } } } -} \ No newline at end of file +} -- 2.25.1