From 433465bcf55bf84dbb047f4c24fe40c96e559695 Mon Sep 17 00:00:00 2001 From: Pradeep Nayak Date: Tue, 5 Jul 2016 06:31:01 +0530 Subject: [PATCH] [ready-for-core-team-review]CRM-16189, added batch filter on contribution summary report (#8598) * CRM-16189, added batch filter on contribution summary report ---------------------------------------- * CRM-16189: Improve support for Accrual Method bookkeeping https://issues.civicrm.org/jira/browse/CRM-16189 * CRM-16189, changed code to use same function as of Detail report to list batches ---------------------------------------- * CRM-16189: Improve support for Accrual Method bookkeeping https://issues.civicrm.org/jira/browse/CRM-16189 Conflicts: CRM/Batch/BAO/Batch.php * --CRM-16189, order batch by title * CRM-16189, generalized and improved code ---------------------------------------- * CRM-16189: Improve support for Accrual Method bookkeeping https://issues.civicrm.org/jira/browse/CRM-16189 * CRM-16189, generalized and improved code ---------------------------------------- * CRM-16189: Improve support for Accrual Method bookkeeping https://issues.civicrm.org/jira/browse/CRM-16189 --- CRM/Batch/BAO/Batch.php | 2 +- CRM/Report/Form/Contribute/Detail.php | 73 ++++++++----------- CRM/Report/Form/Contribute/Summary.php | 21 ++++++ CRM/Report/Form/Member/ContributionDetail.php | 68 ++++++++--------- 4 files changed, 82 insertions(+), 82 deletions(-) diff --git a/CRM/Batch/BAO/Batch.php b/CRM/Batch/BAO/Batch.php index bc1947bfaf..171ad95e37 100644 --- a/CRM/Batch/BAO/Batch.php +++ b/CRM/Batch/BAO/Batch.php @@ -507,7 +507,7 @@ class CRM_Batch_BAO_Batch extends CRM_Batch_DAO_Batch { FROM civicrm_batch WHERE item_count >= 1 AND status_id != {$dataEntryStatusId} - ORDER BY id DESC"; + ORDER BY title"; $batches = array(); $dao = CRM_Core_DAO::executeQuery($query); diff --git a/CRM/Report/Form/Contribute/Detail.php b/CRM/Report/Form/Contribute/Detail.php index 721c5b242a..1a7c9ac065 100644 --- a/CRM/Report/Form/Contribute/Detail.php +++ b/CRM/Report/Form/Contribute/Detail.php @@ -38,7 +38,6 @@ class CRM_Report_Form_Contribute_Detail extends CRM_Report_Form { protected $_emailField = FALSE; protected $_summary = NULL; - protected $_allBatches = NULL; protected $_softFrom = NULL; @@ -271,6 +270,25 @@ class CRM_Report_Form_Contribute_Detail extends CRM_Report_Form { ), ), ), + 'civicrm_batch' => array( + 'dao' => 'CRM_Batch_DAO_EntityBatch', + 'grouping' => 'contri-fields', + 'fields' => array( + 'batch_id' => array( + 'name' => 'id', + 'title' => ts('Batch Name'), + ), + ), + 'filters' => array( + 'bid' => array( + 'title' => ts('Batch Name'), + 'operatorType' => CRM_Report_Form::OP_MULTISELECT, + 'options' => CRM_Batch_BAO_Batch::getBatches(), + 'type' => CRM_Utils_Type::T_INT, + 'dbAlias' => 'batch_civireport.batch_id', + ), + ), + ), 'civicrm_contribution_ordinality' => array( 'dao' => 'CRM_Contribute_DAO_Contribution', 'alias' => 'cordinality', @@ -309,29 +327,6 @@ class CRM_Report_Form_Contribute_Detail extends CRM_Report_Form { $this->_groupFilter = TRUE; $this->_tagFilter = TRUE; - // Don't show Batch display column and filter unless batches are being used - $this->_allBatches = CRM_Batch_BAO_Batch::getBatches(); - if (!empty($this->_allBatches)) { - $this->_columns['civicrm_batch']['dao'] = 'CRM_Batch_DAO_Batch'; - $this->_columns['civicrm_batch']['fields']['batch_id'] = array( - 'name' => 'id', - 'title' => ts('Batch Name'), - ); - $this->_columns['civicrm_batch']['filters']['bid'] = array( - 'name' => 'id', - 'title' => ts('Batch Name'), - 'type' => CRM_Utils_Type::T_INT, - 'operatorType' => CRM_Report_Form::OP_MULTISELECT, - 'options' => $this->_allBatches, - ); - $this->_columns['civicrm_entity_batch']['dao'] = 'CRM_Batch_DAO_EntityBatch'; - $this->_columns['civicrm_entity_batch']['fields']['entity_batch_id'] = array( - 'name' => 'batch_id', - 'default' => TRUE, - 'no_display' => TRUE, - ); - } - // If we have active campaigns add those elements to both the fields and filters if ($campaignEnabled && !empty($this->activeCampaigns)) { $this->_columns['civicrm_contribution']['fields']['campaign_id'] = array( @@ -447,22 +442,16 @@ class CRM_Report_Form_Contribute_Detail extends CRM_Report_Form { {$this->_aliases['civicrm_contribution']}.id = {$this->_aliases['civicrm_note']}.entity_id )"; } //for contribution batches - if ($this->_allBatches && - (!empty($this->_params['fields']['batch_id']) || - !empty($this->_params['bid_value'])) + if (!empty($this->_params['fields']['batch_id']) || + !empty($this->_params['bid_value']) ) { $this->_from .= " - LEFT JOIN ( - SELECT entity_id, financial_trxn_id - FROM civicrm_entity_financial_trxn - WHERE entity_table = 'civicrm_contribution' - GROUP BY entity_id - ) tx ON tx.entity_id = {$this->_aliases['civicrm_contribution']}.id - LEFT JOIN civicrm_entity_batch {$this->_aliases['civicrm_entity_batch']} - ON ({$this->_aliases['civicrm_entity_batch']}.entity_id = tx.financial_trxn_id - AND {$this->_aliases['civicrm_entity_batch']}.entity_table = 'civicrm_financial_trxn') - LEFT JOIN civicrm_batch {$this->_aliases['civicrm_batch']} - ON {$this->_aliases['civicrm_batch']}.id = {$this->_aliases['civicrm_entity_batch']}.batch_id"; + LEFT JOIN civicrm_entity_financial_trxn eft + ON eft.entity_id = {$this->_aliases['civicrm_contribution']}.id AND + eft.entity_table = 'civicrm_contribution' + LEFT JOIN civicrm_entity_batch {$this->_aliases['civicrm_batch']} + ON ({$this->_aliases['civicrm_batch']}.entity_id = eft.financial_trxn_id + AND {$this->_aliases['civicrm_batch']}.entity_table = 'civicrm_financial_trxn')"; } } @@ -702,7 +691,7 @@ UNION ALL $contributionStatus = CRM_Contribute_PseudoConstant::contributionStatus(); $paymentInstruments = CRM_Contribute_PseudoConstant::paymentInstrument(); $contributionPages = CRM_Contribute_PseudoConstant::contributionPage(); - + $batches = CRM_Batch_BAO_Batch::getBatches(); foreach ($rows as $rowNum => $row) { if (!empty($this->_noRepeats) && $this->_outputMode != 'csv') { // don't repeat contact details if its same as the previous row @@ -773,10 +762,8 @@ UNION ALL $rows[$rowNum]['civicrm_contribution_payment_instrument_id'] = $paymentInstruments[$value]; $entryFound = TRUE; } - if (array_key_exists('civicrm_batch_batch_id', $row)) { - if ($value = $row['civicrm_batch_batch_id']) { - $rows[$rowNum]['civicrm_batch_batch_id'] = CRM_Core_DAO::getFieldValue('CRM_Batch_DAO_Batch', $value, 'title'); - } + if (!empty($row['civicrm_batch_batch_id'])) { + $rows[$rowNum]['civicrm_batch_batch_id'] = CRM_Utils_Array::value($row['civicrm_batch_batch_id'], $batches); $entryFound = TRUE; } diff --git a/CRM/Report/Form/Contribute/Summary.php b/CRM/Report/Form/Contribute/Summary.php index d320115f4d..5c2f76cd18 100644 --- a/CRM/Report/Form/Contribute/Summary.php +++ b/CRM/Report/Form/Contribute/Summary.php @@ -210,6 +210,18 @@ class CRM_Report_Form_Contribute_Summary extends CRM_Report_Form { ), ), ), + 'civicrm_batch' => array( + 'dao' => 'CRM_Batch_DAO_EntityBatch', + 'grouping' => 'contri-fields', + 'filters' => array( + 'batch_id' => array( + 'title' => ts('Batch Title'), + 'operatorType' => CRM_Report_Form::OP_MULTISELECT, + 'options' => CRM_Batch_BAO_Batch::getBatches(), + 'type' => CRM_Utils_Type::T_INT, + ), + ), + ), 'civicrm_contribution_soft' => array( 'dao' => 'CRM_Contribute_DAO_ContributionSoft', 'fields' => array( @@ -463,6 +475,15 @@ class CRM_Report_Form_Contribute_Summary extends CRM_Report_Form { {$this->_aliases['civicrm_address']}.contact_id AND {$this->_aliases['civicrm_address']}.is_primary = 1\n"; } + if (!empty($this->_params['batch_id_value'])) { + $this->_from .= " + LEFT JOIN civicrm_entity_financial_trxn eft + ON eft.entity_id = {$this->_aliases['civicrm_contribution']}.id AND + eft.entity_table = 'civicrm_contribution' + LEFT JOIN civicrm_entity_batch {$this->_aliases['civicrm_batch']} + ON {$this->_aliases['civicrm_batch']}.entity_id = eft.financial_trxn_id AND + {$this->_aliases['civicrm_batch']}.entity_table = 'civicrm_financial_trxn'\n"; + } $this->getPermissionedFTQuery($this); } diff --git a/CRM/Report/Form/Member/ContributionDetail.php b/CRM/Report/Form/Member/ContributionDetail.php index 54b9c113fe..5ecf3266ba 100644 --- a/CRM/Report/Form/Member/ContributionDetail.php +++ b/CRM/Report/Form/Member/ContributionDetail.php @@ -38,7 +38,6 @@ class CRM_Report_Form_Member_ContributionDetail extends CRM_Report_Form { protected $_emailField = FALSE; protected $_summary = NULL; - protected $_allBatches = NULL; protected $_customGroupExtends = array( 'Contribution', @@ -231,6 +230,25 @@ class CRM_Report_Form_Member_ContributionDetail extends CRM_Report_Form { ), ), ), + 'civicrm_batch' => array( + 'dao' => 'CRM_Batch_DAO_EntityBatch', + 'grouping' => 'contri-fields', + 'fields' => array( + 'batch_id' => array( + 'name' => 'id', + 'title' => ts('Batch Name'), + ), + ), + 'filters' => array( + 'bid' => array( + 'title' => ts('Batch Name'), + 'operatorType' => CRM_Report_Form::OP_MULTISELECT, + 'options' => CRM_Batch_BAO_Batch::getBatches(), + 'type' => CRM_Utils_Type::T_INT, + 'dbAlias' => 'batch_civireport.batch_id', + ), + ), + ), 'civicrm_contribution_product' => array( 'dao' => 'CRM_Contribute_DAO_ContributionProduct', 'fields' => array( @@ -346,29 +364,6 @@ class CRM_Report_Form_Member_ContributionDetail extends CRM_Report_Form { $this->_groupFilter = TRUE; $this->_tagFilter = TRUE; - // Don't show Batch display column and filter unless batches are being used - $this->_allBatches = CRM_Batch_BAO_Batch::getBatches(); - if (!empty($this->_allBatches)) { - $this->_columns['civicrm_batch']['dao'] = 'CRM_Batch_DAO_Batch'; - $this->_columns['civicrm_batch']['fields']['batch_id'] = array( - 'name' => 'id', - 'title' => ts('Batch Name'), - ); - $this->_columns['civicrm_batch']['filters']['bid'] = array( - 'name' => 'id', - 'title' => ts('Batch Name'), - 'type' => CRM_Utils_Type::T_INT, - 'operatorType' => CRM_Report_Form::OP_MULTISELECT, - 'options' => $this->_allBatches, - ); - $this->_columns['civicrm_entity_batch']['dao'] = 'CRM_Batch_DAO_EntityBatch'; - $this->_columns['civicrm_entity_batch']['fields']['entity_batch_id'] = array( - 'name' => 'batch_id', - 'default' => TRUE, - 'no_display' => TRUE, - ); - } - if ($campaignEnabled && !empty($this->activeCampaigns)) { $this->_columns['civicrm_contribution']['fields']['campaign_id'] = array( 'title' => ts('Campaign'), @@ -505,18 +500,16 @@ class CRM_Report_Form_Member_ContributionDetail extends CRM_Report_Form { {$this->_aliases['civicrm_phone']}.is_primary = 1)"; } //for contribution batches - if ($this->_allBatches && - (!empty($this->_params['fields']['batch_id']) || - !empty($this->_params['bid_value'])) + if (!empty($this->_params['fields']['batch_id']) || + !empty($this->_params['bid_value']) ) { $this->_from .= " - LEFT JOIN civicrm_entity_financial_trxn tx ON (tx.entity_id = {$this->_aliases['civicrm_contribution']}.id AND - tx.entity_table = 'civicrm_contribution') - LEFT JOIN civicrm_entity_batch {$this->_aliases['civicrm_entity_batch']} - ON ({$this->_aliases['civicrm_entity_batch']}.entity_id = tx.financial_trxn_id AND - {$this->_aliases['civicrm_entity_batch']}.entity_table = 'civicrm_financial_trxn') - LEFT JOIN civicrm_batch {$this->_aliases['civicrm_batch']} - ON {$this->_aliases['civicrm_batch']}.id = {$this->_aliases['civicrm_entity_batch']}.batch_id"; + LEFT JOIN civicrm_entity_financial_trxn eft + ON eft.entity_id = {$this->_aliases['civicrm_contribution']}.id AND + eft.entity_table = 'civicrm_contribution' + LEFT JOIN civicrm_entity_batch {$this->_aliases['civicrm_batch']} + ON ({$this->_aliases['civicrm_batch']}.entity_id = eft.financial_trxn_id + AND {$this->_aliases['civicrm_batch']}.entity_table = 'civicrm_financial_trxn')"; } if ($this->_addressField OR @@ -663,6 +656,7 @@ class CRM_Report_Form_Member_ContributionDetail extends CRM_Report_Form { $contributionTypes = CRM_Contribute_PseudoConstant::financialType(); $contributionStatus = CRM_Contribute_PseudoConstant::contributionStatus(); $paymentInstruments = CRM_Contribute_PseudoConstant::paymentInstrument(); + $batches = CRM_Batch_BAO_Batch::getBatches(); //altering the csv display adding additional fields if ($this->_outputMode == 'csv') { @@ -745,10 +739,8 @@ class CRM_Report_Form_Member_ContributionDetail extends CRM_Report_Form { $entryFound = TRUE; } - if (array_key_exists('civicrm_batch_batch_id', $row)) { - if ($value = $row['civicrm_batch_batch_id']) { - $rows[$rowNum]['civicrm_batch_batch_id'] = CRM_Core_DAO::getFieldValue('CRM_Batch_DAO_Batch', $value, 'title'); - } + if (!empty($row['civicrm_batch_batch_id'])) { + $rows[$rowNum]['civicrm_batch_batch_id'] = CRM_Utils_Array::value($row['civicrm_batch_batch_id'], $batches); $entryFound = TRUE; } -- 2.25.1