From d70ada18ed2c77c579a1e5e27c5460922436e1b8 Mon Sep 17 00:00:00 2001 From: eileen Date: Sun, 23 Sep 2018 13:42:01 +1200 Subject: [PATCH] Fix missing amount in soft credit mode --- CRM/Report/Form/Contribute/Detail.php | 66 ++++++++++++--------- tests/phpunit/api/v3/ReportTemplateTest.php | 25 ++++++++ 2 files changed, 62 insertions(+), 29 deletions(-) diff --git a/CRM/Report/Form/Contribute/Detail.php b/CRM/Report/Form/Contribute/Detail.php index 46411ad870..466e47f201 100644 --- a/CRM/Report/Form/Contribute/Detail.php +++ b/CRM/Report/Form/Contribute/Detail.php @@ -253,6 +253,7 @@ class CRM_Report_Form_Contribute_Detail extends CRM_Report_Form { 'contribution_id' => array( 'name' => 'id', 'required' => TRUE, + 'default' => TRUE, 'title' => ts('Contribution'), ), ), @@ -262,6 +263,7 @@ class CRM_Report_Form_Contribute_Detail extends CRM_Report_Form { 'dao' => 'CRM_Contribute_DAO_ContributionSoft', 'fields' => array( 'soft_credit_type_id' => array('title' => ts('Soft Credit Type')), + 'soft_credit_amount' => ['title' => ts('Soft Credit amount'), 'name' => 'amount', 'type' => CRM_Utils_Type::T_MONEY], ), 'filters' => array( 'soft_credit_type_id' => array( @@ -272,6 +274,12 @@ class CRM_Report_Form_Contribute_Detail extends CRM_Report_Form { 'type' => CRM_Utils_Type::T_STRING, ), ), + 'group_bys' => array( + 'soft_credit_id' => array( + 'name' => 'id', + 'title' => ts('Soft Credit'), + ), + ), ), 'civicrm_financial_trxn' => array( 'dao' => 'CRM_Financial_DAO_FinancialTrxn', @@ -376,18 +384,7 @@ class CRM_Report_Form_Contribute_Detail extends CRM_Report_Form { ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_contribution']}.contact_id AND {$this->_aliases['civicrm_contribution']}.is_test = 0"; - if (CRM_Utils_Array::value('contribution_or_soft_value', $this->_params) == - 'both' - ) { - $this->_from .= "\n LEFT JOIN civicrm_contribution_soft contribution_soft_civireport - ON contribution_soft_civireport.contribution_id = {$this->_aliases['civicrm_contribution']}.id"; - } - elseif (CRM_Utils_Array::value('contribution_or_soft_value', $this->_params) == - 'soft_credits_only' - ) { - $this->_from .= "\n INNER JOIN civicrm_contribution_soft contribution_soft_civireport - ON contribution_soft_civireport.contribution_id = {$this->_aliases['civicrm_contribution']}.id"; - } + $this->joinContributionToSoftCredit(); $this->appendAdditionalFromJoins(); } @@ -533,9 +530,7 @@ GROUP BY {$this->_aliases['civicrm_contribution']}.currency"; } // 1. use main contribution query to build temp table 1 $sql = $this->buildQuery(); - $tempQuery = "CREATE TEMPORARY TABLE civireport_contribution_detail_temp1 {$this->_databaseAttributes} AS {$sql}"; - $this->temporaryTables['civireport_contribution_detail_temp1'] = ['name' => 'civireport_contribution_detail_temp1', 'temporary' => TRUE]; - $this->executeReportQuery($tempQuery); + $this->createTemporaryTable('civireport_contribution_detail_temp1', $sql); $this->setPager(); // 2. customize main contribution query for soft credit, and build temp table 2 with soft credit contributions only @@ -551,13 +546,10 @@ GROUP BY {$this->_aliases['civicrm_contribution']}.currency"; $select = str_ireplace('contribution_civireport.total_amount', 'contribution_soft_civireport.amount', $this->_select); $select = str_ireplace("'Contribution' as", "'Soft Credit' as", $select); - // We really don't want to join soft credit in if not required. - if (!empty($this->_groupBy) && !$this->noDisplayContributionOrSoftColumn) { - $this->_groupBy .= ', contribution_soft_civireport.amount'; - } + // we inner join with temp1 to restrict soft contributions to those in temp1 table. // no group by here as we want to display as many soft credit rows as actually exist. - $sql = "{$select} {$this->_from} {$this->_where}"; + $sql = "{$select} {$this->_from} {$this->_where} $this->_groupBy"; $tempQuery = "CREATE TEMPORARY TABLE civireport_contribution_detail_temp2 {$this->_databaseAttributes} AS {$sql}"; $this->executeReportQuery($tempQuery); $this->temporaryTables['civireport_contribution_detail_temp2'] = ['name' => 'civireport_contribution_detail_temp2', 'temporary' => TRUE]; @@ -583,7 +575,7 @@ GROUP BY {$this->_aliases['civicrm_contribution']}.currency"; if ($this->isContributionBaseMode ) { $this->executeReportQuery( - "CREATE TEMPORARY TABLE civireport_contribution_detail_temp3 {$this->_databaseAttributes} AS (SELECT * FROM civireport_contribution_detail_temp1)" + "CREATE TEMPORARY TABLE civireport_contribution_detail_temp3 {$this->_databaseAttributes} AS (SELECT * FROM {$this->temporaryTables['civireport_contribution_detail_temp1']['name']})" ); } elseif (CRM_Utils_Array::value('contribution_or_soft_value', $this->_params) == @@ -595,7 +587,7 @@ GROUP BY {$this->_aliases['civicrm_contribution']}.currency"; } else { $this->executeReportQuery("CREATE TEMPORARY TABLE civireport_contribution_detail_temp3 {$this->_databaseAttributes} -(SELECT * FROM civireport_contribution_detail_temp1) +(SELECT * FROM {$this->temporaryTables['civireport_contribution_detail_temp1']['name']}) UNION ALL (SELECT * FROM civireport_contribution_detail_temp2)"); } @@ -606,12 +598,11 @@ UNION ALL /** * Store group bys into array - so we can check elsewhere what is grouped. * - * If we are generating a table of soft credits we do not want to be using - * group by. + * If we are generating a table of soft credits we need to group by them. */ protected function storeGroupByArray() { if ($this->queryMode === 'SoftCredit') { - $this->_groupByArray = []; + $this->_groupByArray = [$this->_aliases['civicrm_contribution_soft'] . '.id']; } else { parent::storeGroupByArray(); @@ -744,7 +735,7 @@ UNION ALL array_key_exists('civicrm_contribution_contribution_id', $row) ) { $query = " -SELECT civicrm_contact_id, civicrm_contact_sort_name, civicrm_contribution_total_amount, civicrm_contribution_currency +SELECT civicrm_contact_id, civicrm_contact_sort_name, civicrm_contribution_total_amount_sum, civicrm_contribution_currency FROM civireport_contribution_detail_temp2 WHERE civicrm_contribution_contribution_id={$row['civicrm_contribution_contribution_id']}"; $dao = CRM_Core_DAO::executeQuery($query); @@ -755,7 +746,7 @@ WHERE civicrm_contribution_contribution_id={$row['civicrm_contribution_contribu $dao->civicrm_contact_id); $string = $string . ($string ? $separator : '') . "{$dao->civicrm_contact_sort_name} " . - CRM_Utils_Money::format($dao->civicrm_contribution_total_amount, $dao->civicrm_contribution_currency); + CRM_Utils_Money::format($dao->civicrm_contribution_total_amount_sum, $dao->civicrm_contribution_currency); } $rows[$rowNum]['civicrm_contribution_soft_credits'] = $string; } @@ -767,7 +758,7 @@ WHERE civicrm_contribution_contribution_id={$row['civicrm_contribution_contribu ) { $query = " SELECT civicrm_contact_id, civicrm_contact_sort_name -FROM civireport_contribution_detail_temp1 +FROM {$this->temporaryTables['civireport_contribution_detail_temp1']['name']} WHERE civicrm_contribution_contribution_id={$row['civicrm_contribution_contribution_id']}"; $dao = CRM_Core_DAO::executeQuery($query); $string = ''; @@ -920,7 +911,7 @@ WHERE civicrm_contribution_contribution_id={$row['civicrm_contribution_contribu public function softCreditFrom() { $this->_from = " - FROM civireport_contribution_detail_temp1 temp1_civireport + FROM {$this->temporaryTables['civireport_contribution_detail_temp1']['name']} temp1_civireport INNER JOIN civicrm_contribution {$this->_aliases['civicrm_contribution']} ON temp1_civireport.civicrm_contribution_contribution_id = {$this->_aliases['civicrm_contribution']}.id INNER JOIN civicrm_contribution_soft contribution_soft_civireport @@ -975,4 +966,21 @@ WHERE civicrm_contribution_contribution_id={$row['civicrm_contribution_contribu $this->addFinancialTrxnFromClause(); } + /** + * Add join to the soft credit table. + */ + protected function joinContributionToSoftCredit() { + if (!CRM_Utils_Array::value('contribution_or_soft_value', $this->_params) && !$this->isTableSelected('civicrm_contribution_soft')) { + return; + } + $joinType = ' LEFT '; + if (CRM_Utils_Array::value('contribution_or_soft_value', $this->_params) == 'soft_credits_only') { + $joinType = ' INNER '; + } + $this->_from .= " + $joinType JOIN civicrm_contribution_soft {$this->_aliases['civicrm_contribution_soft']} + ON {$this->_aliases['civicrm_contribution_soft']}.contribution_id = {$this->_aliases['civicrm_contribution']}.id + "; + } + } diff --git a/tests/phpunit/api/v3/ReportTemplateTest.php b/tests/phpunit/api/v3/ReportTemplateTest.php index 87ba2a7e0c..630645dfb0 100644 --- a/tests/phpunit/api/v3/ReportTemplateTest.php +++ b/tests/phpunit/api/v3/ReportTemplateTest.php @@ -526,6 +526,31 @@ class api_v3_ReportTemplateTest extends CiviUnitTestCase { ); } + /** + * Test the amount column is populated on soft credit details. + */ + public function testContributionDetailSoftCreditsOnly() { + $contactID = $this->individualCreate(); + $contactID2 = $this->individualCreate(); + $this->contributionCreate(['contact_id' => $contactID, 'api.ContributionSoft.create' => ['amount' => 5, 'contact_id' => $contactID2]]); + $template = 'contribute/detail'; + $rows = $this->callAPISuccess('report_template', 'getrows', array( + 'report_id' => $template, + 'contribution_or_soft_value' => 'soft_credits_only', + 'fields' => [ + 'sort_name' => '1', + 'email' => '1', + 'financial_type_id' => '1', + 'receive_date' => '1', + 'total_amount' => '1', + ], + 'options' => array('metadata' => ['sql', 'labels']), + )); + foreach (array_keys($rows['metadata']['labels']) as $header) { + $this->assertTrue(!empty($rows['values'][0][$header])); + } + } + /** * Test the group filter works on the various reports. * -- 2.25.1