From 6b60d32c4cd370acbb64c070b1329a9170c099b2 Mon Sep 17 00:00:00 2001 From: eileen Date: Wed, 12 Sep 2018 17:00:16 +1200 Subject: [PATCH] Extract query to generate annual totals and add test --- CRM/Contribute/BAO/Contribution.php | 147 ++++++++++-------- .../CRM/Contribute/BAO/ContributionTest.php | 32 ++++ 2 files changed, 117 insertions(+), 62 deletions(-) diff --git a/CRM/Contribute/BAO/Contribution.php b/CRM/Contribute/BAO/Contribution.php index 6fda2f758f..2ce7c31a30 100644 --- a/CRM/Contribute/BAO/Contribution.php +++ b/CRM/Contribute/BAO/Contribution.php @@ -1311,73 +1311,20 @@ WHERE civicrm_contribution.contact_id = civicrm_contact.id } /** - * @param int $contactID + * Generate summary of amount received in the current fiscal year to date from the contact or contacts. + * + * @param int|array $contactIDs * * @return array */ - public static function annual($contactID) { - if (is_array($contactID)) { - $contactIDs = implode(',', $contactID); - } - else { - $contactIDs = $contactID; + public static function annual($contactIDs) { + if (!is_array($contactIDs)) { + // In practice I can't fine any evidence that this function is ever called with + // anything other than a single contact id, but left like this due to .... fear. + $contactIDs = explode(',', $contactIDs); } - $config = CRM_Core_Config::singleton(); - $startDate = $endDate = NULL; - - $currentMonth = date('m'); - $currentDay = date('d'); - if ((int ) $config->fiscalYearStart['M'] > $currentMonth || - ((int ) $config->fiscalYearStart['M'] == $currentMonth && - (int ) $config->fiscalYearStart['d'] > $currentDay - ) - ) { - $year = date('Y') - 1; - } - else { - $year = date('Y'); - } - $nextYear = $year + 1; - - if ($config->fiscalYearStart) { - $newFiscalYearStart = $config->fiscalYearStart; - if ($newFiscalYearStart['M'] < 10) { - $newFiscalYearStart['M'] = '0' . $newFiscalYearStart['M']; - } - if ($newFiscalYearStart['d'] < 10) { - $newFiscalYearStart['d'] = '0' . $newFiscalYearStart['d']; - } - $config->fiscalYearStart = $newFiscalYearStart; - $monthDay = $config->fiscalYearStart['M'] . $config->fiscalYearStart['d']; - } - else { - $monthDay = '0101'; - } - $startDate = "$year$monthDay"; - $endDate = "$nextYear$monthDay"; - CRM_Financial_BAO_FinancialType::getAvailableFinancialTypes($financialTypes); - $additionalWhere = " AND b.financial_type_id IN (0)"; - $liWhere = " AND i.financial_type_id IN (0)"; - if (!empty($financialTypes)) { - $additionalWhere = " AND b.financial_type_id IN (" . implode(',', array_keys($financialTypes)) . ") AND i.id IS NULL"; - $liWhere = " AND i.financial_type_id NOT IN (" . implode(',', array_keys($financialTypes)) . ")"; - } - $query = " - SELECT count(*) as count, - sum(total_amount) as amount, - avg(total_amount) as average, - currency - FROM civicrm_contribution b - LEFT JOIN civicrm_line_item i ON i.contribution_id = b.id AND i.entity_table = 'civicrm_contribution' $liWhere - WHERE b.contact_id IN ( $contactIDs ) - AND b.contribution_status_id = 1 - AND b.is_test = 0 - AND b.receive_date >= $startDate - AND b.receive_date < $endDate - $additionalWhere - GROUP BY currency - "; + $query = self::getAnnualQuery($contactIDs); $dao = CRM_Core_DAO::executeQuery($query); $count = 0; $amount = $average = array(); @@ -5560,6 +5507,82 @@ LIMIT 1;"; return $actionLinks; } + /** + * Get a query to determine the amount donated by the contact/s in the current financial year. + * + * @param array $contactIDs + * + * @return string + */ + public static function getAnnualQuery($contactIDs) { + $contactIDs = implode(',', $contactIDs); + $config = CRM_Core_Config::singleton(); + $currentMonth = date('m'); + $currentDay = date('d'); + if ( + (int) $config->fiscalYearStart['M'] > $currentMonth || + ( + (int) $config->fiscalYearStart['M'] == $currentMonth && + (int) $config->fiscalYearStart['d'] > $currentDay + ) + ) { + $year = date('Y') - 1; + } + else { + $year = date('Y'); + } + $nextYear = $year + 1; + + if ($config->fiscalYearStart) { + $newFiscalYearStart = $config->fiscalYearStart; + if ($newFiscalYearStart['M'] < 10) { + // This is just a clumsy way of adding padding. + // @todo next round look for a nicer way. + $newFiscalYearStart['M'] = '0' . $newFiscalYearStart['M']; + } + if ($newFiscalYearStart['d'] < 10) { + // This is just a clumsy way of adding padding. + // @todo next round look for a nicer way. + $newFiscalYearStart['d'] = '0' . $newFiscalYearStart['d']; + } + $config->fiscalYearStart = $newFiscalYearStart; + $monthDay = $config->fiscalYearStart['M'] . $config->fiscalYearStart['d']; + } + else { + // First of January. + $monthDay = '0101'; + } + $startDate = "$year$monthDay"; + $endDate = "$nextYear$monthDay"; + $financialTypes = []; + CRM_Financial_BAO_FinancialType::getAvailableFinancialTypes($financialTypes); + // this is a clumsy way of saying never return anything + // @todo improve! + $liWhere = " AND i.financial_type_id IN (0)"; + if (!empty($financialTypes)) { + $liWhere = " AND i.financial_type_id NOT IN (" . implode(',', array_keys($financialTypes)) . ")"; + } + $whereClauses = [ + 'b.contact_id IN (' . $contactIDs . ')', + 'b.contribution_status_id = ' . (int) CRM_Core_PseudoConstant::getKey('CRM_Contribute_BAO_Contribution', 'contribution_status_id', 'Completed'), + 'b.is_test = 0', + 'b.receive_date >= ' . $startDate, + 'b.receive_date < ' . $endDate, + ]; + CRM_Financial_BAO_FinancialType::buildPermissionedClause($whereClauses, NULL, 'b'); + $query = " + SELECT COUNT(*) as count, + SUM(total_amount) as amount, + AVG(total_amount) as average, + currency + FROM civicrm_contribution b + LEFT JOIN civicrm_line_item i ON i.contribution_id = b.id AND i.entity_table = 'civicrm_contribution' $liWhere + WHERE " . implode(' AND ', $whereClauses) . " + GROUP BY currency + "; + return $query; + } + /** * Assign Test Value. * diff --git a/tests/phpunit/CRM/Contribute/BAO/ContributionTest.php b/tests/phpunit/CRM/Contribute/BAO/ContributionTest.php index 783bb40325..e5b81d7335 100644 --- a/tests/phpunit/CRM/Contribute/BAO/ContributionTest.php +++ b/tests/phpunit/CRM/Contribute/BAO/ContributionTest.php @@ -31,6 +31,8 @@ */ class CRM_Contribute_BAO_ContributionTest extends CiviUnitTestCase { + use CRMTraits_Financial_FinancialACLTrait; + /** * Clean up after tests. */ @@ -305,6 +307,36 @@ class CRM_Contribute_BAO_ContributionTest extends CiviUnitTestCase { ); } + /** + * Test that financial type data is not added to the annual query if acls not enabled. + */ + public function testAnnualQueryWithFinancialACLsEnabled() { + $this->enableFinancialACLs(); + $this->createLoggedInUserWithFinancialACL(); + $permittedFinancialType = CRM_Core_PseudoConstant::getKey('CRM_Contribute_BAO_Contribution', 'financial_type_id', 'Donation'); + $sql = CRM_Contribute_BAO_Contribution::getAnnualQuery([1, 2, 3]); + $this->assertContains('SUM(total_amount) as amount,', $sql); + $this->assertContains('WHERE b.contact_id IN (1,2,3)', $sql); + $this->assertContains('b.financial_type_id IN (' . $permittedFinancialType . ')', $sql); + + // Run it to make sure it's not bad sql. + CRM_Core_DAO::executeQuery($sql); + $this->disableFinancialACLs(); + } + + /** + * Test that financial type data is not added to the annual query if acls not enabled. + */ + public function testAnnualQueryWithFinancialACLsDisabled() { + $sql = CRM_Contribute_BAO_Contribution::getAnnualQuery([1, 2, 3]); + $this->assertContains('SUM(total_amount) as amount,', $sql); + $this->assertContains('WHERE b.contact_id IN (1,2,3)', $sql); + $this->assertNotContains('b.financial_type_id', $sql); + //$this->assertNotContains('line_item', $sql); + // Run it to make sure it's not bad sql. + CRM_Core_DAO::executeQuery($sql); + } + /** * Display sort name during. * Update multiple contributions -- 2.25.1