From 9f108b4d6e3b343d2edae88201a1948e5012a190 Mon Sep 17 00:00:00 2001 From: Jaap Jansma Date: Wed, 30 Jun 2021 13:30:43 +0200 Subject: [PATCH] dev/financial#6 do not include template contributions in reports --- CRM/Report/Form/Contribute/Bookkeeping.php | 3 ++- CRM/Report/Form/Contribute/Detail.php | 3 ++- CRM/Report/Form/Contribute/History.php | 9 ++++---- .../Form/Contribute/HouseholdSummary.php | 2 +- CRM/Report/Form/Contribute/Lybunt.php | 2 ++ .../Form/Contribute/OrganizationSummary.php | 2 +- CRM/Report/Form/Contribute/RecurSummary.php | 2 +- CRM/Report/Form/Contribute/Repeat.php | 5 +++- CRM/Report/Form/Contribute/SoftCredit.php | 2 +- CRM/Report/Form/Contribute/Summary.php | 3 ++- CRM/Report/Form/Contribute/Sybunt.php | 9 +++++--- CRM/Report/Form/Contribute/TopDonor.php | 2 +- CRM/Report/Form/Member/ContributionDetail.php | 2 +- .../Contribute/fixtures/dataset-ascii.sql | 23 ++++++++++--------- tests/phpunit/api/v3/ReportTemplateTest.php | 2 ++ 15 files changed, 43 insertions(+), 28 deletions(-) diff --git a/CRM/Report/Form/Contribute/Bookkeeping.php b/CRM/Report/Form/Contribute/Bookkeeping.php index f2239d6ce7..2a3af71516 100644 --- a/CRM/Report/Form/Contribute/Bookkeeping.php +++ b/CRM/Report/Form/Contribute/Bookkeeping.php @@ -387,7 +387,8 @@ class CRM_Report_Form_Contribute_Bookkeeping extends CRM_Report_Form { $this->_from = "FROM civicrm_contact {$this->_aliases['civicrm_contact']} {$this->_aclFrom} INNER JOIN civicrm_contribution {$this->_aliases['civicrm_contribution']} ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_contribution']}.contact_id AND - {$this->_aliases['civicrm_contribution']}.is_test = 0 + {$this->_aliases['civicrm_contribution']}.is_test = 0 AND + {$this->_aliases['civicrm_contribution']}.is_template = 0 LEFT JOIN civicrm_membership_payment payment ON ( {$this->_aliases['civicrm_contribution']}.id = payment.contribution_id ) LEFT JOIN civicrm_membership {$this->_aliases['civicrm_membership']} diff --git a/CRM/Report/Form/Contribute/Detail.php b/CRM/Report/Form/Contribute/Detail.php index ab5ac670e5..219f4f3d82 100644 --- a/CRM/Report/Form/Contribute/Detail.php +++ b/CRM/Report/Form/Contribute/Detail.php @@ -400,7 +400,8 @@ class CRM_Report_Form_Contribute_Detail extends CRM_Report_Form { $this->_from .= " INNER JOIN civicrm_contribution {$this->_aliases['civicrm_contribution']} ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_contribution']}.contact_id - AND {$this->_aliases['civicrm_contribution']}.is_test = 0"; + AND {$this->_aliases['civicrm_contribution']}.is_test = 0 + AND {$this->_aliases['civicrm_contribution']}.is_template = 0"; $this->joinContributionToSoftCredit(); $this->appendAdditionalFromJoins(); diff --git a/CRM/Report/Form/Contribute/History.php b/CRM/Report/Form/Contribute/History.php index 1db29aa24c..3a85c348b7 100644 --- a/CRM/Report/Form/Contribute/History.php +++ b/CRM/Report/Form/Contribute/History.php @@ -376,7 +376,8 @@ class CRM_Report_Form_Contribute_History extends CRM_Report_Form { FROM civicrm_contact {$this->_aliases['civicrm_contact']} INNER JOIN civicrm_contribution {$this->_aliases['civicrm_contribution']} ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_contribution']}.contact_id AND - {$this->_aliases['civicrm_contribution']}.is_test = 0 "; + {$this->_aliases['civicrm_contribution']}.is_test = 0 AND + {$this->_aliases['civicrm_contribution']}.is_template = 0"; $relContacAlias = 'contact_relationship'; $this->_relationshipFrom = " INNER JOIN civicrm_relationship {$this->_aliases['civicrm_relationship']} @@ -592,12 +593,12 @@ class CRM_Report_Form_Contribute_History extends CRM_Report_Form { if (!empty($this->_referenceYear['other_year'])) { (CRM_Utils_Array::value('other_year_op', $this->_params) == 'calendar') ? $other_receive_date = 'YEAR (contri.receive_date)' : $other_receive_date = self::fiscalYearOffset('contri.receive_date'); - $addWhere .= " AND {$this->_aliases['civicrm_contact']}.id NOT IN ( SELECT DISTINCT cont.id FROM civicrm_contact cont, civicrm_contribution contri WHERE cont.id = contri.contact_id AND {$other_receive_date} = {$this->_referenceYear['other_year']} AND contri.is_test = 0 ) "; + $addWhere .= " AND {$this->_aliases['civicrm_contact']}.id NOT IN ( SELECT DISTINCT cont.id FROM civicrm_contact cont, civicrm_contribution contri WHERE cont.id = contri.contact_id AND {$other_receive_date} = {$this->_referenceYear['other_year']} AND contri.is_test = 0 AND contri.is_template = 0 ) "; } if (!empty($this->_referenceYear['this_year'])) { (CRM_Utils_Array::value('this_year_op', $this->_params) == 'calendar') ? $receive_date = 'YEAR (contri.receive_date)' : $receive_date = self::fiscalYearOffset('contri.receive_date'); - $addWhere .= " AND {$this->_aliases['civicrm_contact']}.id IN ( SELECT DISTINCT cont.id FROM civicrm_contact cont, civicrm_contribution contri WHERE cont.id = contri.contact_id AND {$receive_date} = {$this->_referenceYear['this_year']} AND contri.is_test = 0 ) "; + $addWhere .= " AND {$this->_aliases['civicrm_contact']}.id IN ( SELECT DISTINCT cont.id FROM civicrm_contact cont, civicrm_contribution contri WHERE cont.id = contri.contact_id AND {$receive_date} = {$this->_referenceYear['this_year']} AND contri.is_test = 0 AND contri.is_template = 0 ) "; } $this->limit(); $getContacts = "SELECT {$this->_aliases['civicrm_contact']}.id as cid, SUM({$this->_aliases['civicrm_contribution']}.total_amount) as civicrm_contribution_total_amount_sum {$this->_from} {$this->_where} {$addWhere} GROUP BY {$this->_aliases['civicrm_contact']}.id {$this->_having} {$this->_limit}"; @@ -686,7 +687,7 @@ class CRM_Report_Form_Contribute_History extends CRM_Report_Form { $sqlContribution = "{$this->_select} {$this->_from} WHERE {$this->_aliases['civicrm_contact']}.id IN (" . implode(',', $contactIds) . - ") AND {$this->_aliases['civicrm_contribution']}.is_test = 0 {$this->_statusClause} {$this->_groupBy} "; + ") AND {$this->_aliases['civicrm_contribution']}.is_test = 0 AND {$this->_aliases['civicrm_contribution']}.is_template = 0 {$this->_statusClause} {$this->_groupBy} "; $dao = CRM_Core_DAO::executeQuery($sqlContribution); $contributionSum = 0; diff --git a/CRM/Report/Form/Contribute/HouseholdSummary.php b/CRM/Report/Form/Contribute/HouseholdSummary.php index 4ed79397d5..13e028a1a9 100644 --- a/CRM/Report/Form/Contribute/HouseholdSummary.php +++ b/CRM/Report/Form/Contribute/HouseholdSummary.php @@ -235,7 +235,7 @@ class CRM_Report_Form_Contribute_HouseholdSummary extends CRM_Report_Form { ({$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_relationship']}.$this->otherContact ) {$this->_aclFrom} INNER JOIN civicrm_contribution {$this->_aliases['civicrm_contribution']} ON - ({$this->_aliases['civicrm_contribution']}.contact_id = {$this->_aliases['civicrm_relationship']}.$this->otherContact ) AND {$this->_aliases['civicrm_contribution']}.is_test = 0 "; + ({$this->_aliases['civicrm_contribution']}.contact_id = {$this->_aliases['civicrm_relationship']}.$this->otherContact ) AND {$this->_aliases['civicrm_contribution']}.is_test = 0 AND {$this->_aliases['civicrm_contribution']}.is_template = 0 "; $this->joinAddressFromContact(); $this->joinEmailFromContact(); diff --git a/CRM/Report/Form/Contribute/Lybunt.php b/CRM/Report/Form/Contribute/Lybunt.php index 97c0d8f9d3..97ed34f08d 100644 --- a/CRM/Report/Form/Contribute/Lybunt.php +++ b/CRM/Report/Form/Contribute/Lybunt.php @@ -307,6 +307,7 @@ class CRM_Report_Form_Contribute_Lybunt extends CRM_Report_Form { INNER JOIN $this->contactTempTable restricted_contacts ON restricted_contacts.cid = {$this->_aliases['civicrm_contribution']}.contact_id AND {$this->_aliases['civicrm_contribution']}.is_test = 0 + AND {$this->_aliases['civicrm_contribution']}.is_template = 0 INNER JOIN civicrm_contact {$this->_aliases['civicrm_contact']} ON restricted_contacts.cid = {$this->_aliases['civicrm_contact']}.id"; @@ -326,6 +327,7 @@ class CRM_Report_Form_Contribute_Lybunt extends CRM_Report_Form { } $this->_from .= " ON {$this->_aliases['civicrm_contribution']}.contact_id = {$this->_aliases['civicrm_contact']}.id AND {$this->_aliases['civicrm_contribution']}.is_test = 0 + AND {$this->_aliases['civicrm_contribution']}.is_template = 0 AND " . $this->whereClauseLastYear("{$this->_aliases['civicrm_contribution']}.receive_date") . " {$this->_aclFrom} "; $this->selectivelyAddLocationTablesJoinsToFilterQuery(); diff --git a/CRM/Report/Form/Contribute/OrganizationSummary.php b/CRM/Report/Form/Contribute/OrganizationSummary.php index f195cea044..1da83c809a 100644 --- a/CRM/Report/Form/Contribute/OrganizationSummary.php +++ b/CRM/Report/Form/Contribute/OrganizationSummary.php @@ -242,7 +242,7 @@ class CRM_Report_Form_Contribute_OrganizationSummary extends CRM_Report_Form { ({$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_relationship']}.$this->otherContact ) {$this->_aclFrom} INNER JOIN civicrm_contribution {$this->_aliases['civicrm_contribution']} ON - ({$this->_aliases['civicrm_contribution']}.contact_id = {$this->_aliases['civicrm_relationship']}.$this->otherContact ) AND {$this->_aliases['civicrm_contribution']}.is_test = 0 "; + ({$this->_aliases['civicrm_contribution']}.contact_id = {$this->_aliases['civicrm_relationship']}.$this->otherContact ) AND {$this->_aliases['civicrm_contribution']}.is_test = 0 AND {$this->_aliases['civicrm_contribution']}.is_template = 0 "; $this->joinAddressFromContact(); $this->joinEmailFromContact(); diff --git a/CRM/Report/Form/Contribute/RecurSummary.php b/CRM/Report/Form/Contribute/RecurSummary.php index 403ad49530..21d6883cd4 100644 --- a/CRM/Report/Form/Contribute/RecurSummary.php +++ b/CRM/Report/Form/Contribute/RecurSummary.php @@ -282,7 +282,7 @@ class CRM_Report_Form_Contribute_RecurSummary extends CRM_Report_Form { $amountSql = " SELECT SUM(cc.total_amount) as amount FROM `civicrm_contribution` cc INNER JOIN civicrm_contribution_recur cr ON (cr.id = cc.contribution_recur_id AND cr.payment_instrument_id = {$paymentInstrumentId}) - WHERE cc.contribution_status_id = 1 AND cc.is_test = 0 AND "; + WHERE cc.contribution_status_id = 1 AND cc.is_test = 0 AND cc.is_template = 0 AND "; $amountSql .= str_replace("start_date", "cc.`receive_date`", $startedDateSql); $amountDao = CRM_Core_DAO::executeQuery($amountSql); $amountDao->fetch(); diff --git a/CRM/Report/Form/Contribute/Repeat.php b/CRM/Report/Form/Contribute/Repeat.php index 18bfd109bd..5a028ae438 100644 --- a/CRM/Report/Form/Contribute/Repeat.php +++ b/CRM/Report/Form/Contribute/Repeat.php @@ -380,7 +380,10 @@ LEFT JOIN $this->tempTableRepeat2 {$this->_aliases['civicrm_contribution']}2 * @return mixed|string */ public function whereContribution($replaceAliasWith = 'contribution1') { - $clauses = array("is_test" => "{$this->_aliases['civicrm_contribution']}.is_test = 0"); + $clauses = array( + "is_test" => "{$this->_aliases['civicrm_contribution']}.is_test = 0", + "is_template" => "{$this->_aliases['civicrm_contribution']}.is_template = 0", + ); foreach ($this->_columns['civicrm_contribution']['filters'] as $fieldName => $field) { $clause = NULL; diff --git a/CRM/Report/Form/Contribute/SoftCredit.php b/CRM/Report/Form/Contribute/SoftCredit.php index 31401b4633..a5d1f927d1 100644 --- a/CRM/Report/Form/Contribute/SoftCredit.php +++ b/CRM/Report/Form/Contribute/SoftCredit.php @@ -462,7 +462,7 @@ GROUP BY {$this->_aliases['civicrm_contribution_soft']}.contact_id, constituentn public function where() { parent::where(); - $this->_where .= " AND {$this->_aliases['civicrm_contribution']}.is_test = 0 "; + $this->_where .= " AND {$this->_aliases['civicrm_contribution']}.is_test = 0 AND {$this->_aliases['civicrm_contribution']}.is_template = 0 "; } /** diff --git a/CRM/Report/Form/Contribute/Summary.php b/CRM/Report/Form/Contribute/Summary.php index c73da90e77..27d08eb05a 100644 --- a/CRM/Report/Form/Contribute/Summary.php +++ b/CRM/Report/Form/Contribute/Summary.php @@ -493,7 +493,8 @@ class CRM_Report_Form_Contribute_Summary extends CRM_Report_Form { $this->_from .= " INNER JOIN civicrm_contribution {$this->_aliases['civicrm_contribution']} ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_contribution']}.contact_id AND - {$this->_aliases['civicrm_contribution']}.is_test = 0 + {$this->_aliases['civicrm_contribution']}.is_test = 0 AND + {$this->_aliases['civicrm_contribution']}.is_template = 0 {$softCreditJoin} LEFT JOIN civicrm_financial_type {$this->_aliases['civicrm_financial_type']} ON {$this->_aliases['civicrm_contribution']}.financial_type_id ={$this->_aliases['civicrm_financial_type']}.id diff --git a/CRM/Report/Form/Contribute/Sybunt.php b/CRM/Report/Form/Contribute/Sybunt.php index dc6b31898a..4349dae5a7 100644 --- a/CRM/Report/Form/Contribute/Sybunt.php +++ b/CRM/Report/Form/Contribute/Sybunt.php @@ -326,7 +326,10 @@ class CRM_Report_Form_Contribute_Sybunt extends CRM_Report_Form { public function where() { $this->_statusClause = ""; - $clauses = [$this->_aliases['civicrm_contribution'] . '.is_test = 0']; + $clauses = [ + $this->_aliases['civicrm_contribution'] . '.is_test = 0', + $this->_aliases['civicrm_contribution'] . '.is_template = 0', + ]; foreach ($this->_columns as $tableName => $table) { if (array_key_exists('filters', $table)) { foreach ($table['filters'] as $fieldName => $field) { @@ -336,7 +339,7 @@ class CRM_Report_Form_Contribute_Sybunt extends CRM_Report_Form { (SELECT distinct cont.id FROM civicrm_contact cont, civicrm_contribution contri WHERE cont.id = contri.contact_id AND " . self::fiscalYearOffset('contri.receive_date') . - " = {$this->_params['yid_value']} AND contri.is_test = 0 )"; + " = {$this->_params['yid_value']} AND contri.is_test = 0 AND contri.is_template = 0 )"; } elseif (CRM_Utils_Array::value('type', $field) & CRM_Utils_Type::T_DATE ) { @@ -440,7 +443,7 @@ class CRM_Report_Form_Contribute_Sybunt extends CRM_Report_Form { $sql = "" . "{$this->_select} {$this->_from} WHERE {$this->_aliases['civicrm_contact']}.id IN (" . implode(',', $contactIds) . - ") AND {$this->_aliases['civicrm_contribution']}.is_test = 0 {$this->_statusClause} {$this->_groupBy} "; + ") AND {$this->_aliases['civicrm_contribution']}.is_test = 0 AND {$this->_aliases['civicrm_contribution']}.is_template = 0 {$this->_statusClause} {$this->_groupBy} "; } $current_year = $this->_params['yid_value']; diff --git a/CRM/Report/Form/Contribute/TopDonor.php b/CRM/Report/Form/Contribute/TopDonor.php index c5e05b0e16..4e3ae6e87f 100644 --- a/CRM/Report/Form/Contribute/TopDonor.php +++ b/CRM/Report/Form/Contribute/TopDonor.php @@ -225,7 +225,7 @@ class CRM_Report_Form_Contribute_TopDonor extends CRM_Report_Form { $this->_from = " FROM civicrm_contact {$this->_aliases['civicrm_contact']} {$this->_aclFrom} INNER JOIN civicrm_contribution {$this->_aliases['civicrm_contribution']} - ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_contribution']}.contact_id AND {$this->_aliases['civicrm_contribution']}.is_test = 0 + ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_contribution']}.contact_id AND {$this->_aliases['civicrm_contribution']}.is_test = 0 AND {$this->_aliases['civicrm_contribution']}.is_template = 0 "; // for credit card type diff --git a/CRM/Report/Form/Member/ContributionDetail.php b/CRM/Report/Form/Member/ContributionDetail.php index 2282549158..b032020a9f 100644 --- a/CRM/Report/Form/Member/ContributionDetail.php +++ b/CRM/Report/Form/Member/ContributionDetail.php @@ -518,7 +518,7 @@ class CRM_Report_Form_Member_ContributionDetail extends CRM_Report_Form { SELECT contribution.id, {$this->_aliases['civicrm_contact']}.id, m.id FROM civicrm_contribution contribution INNER JOIN civicrm_contact {$this->_aliases['civicrm_contact']} - ON {$this->_aliases['civicrm_contact']}.id = contribution.contact_id AND contribution.is_test = 0 + ON {$this->_aliases['civicrm_contact']}.id = contribution.contact_id AND contribution.is_test = 0 AND contribution.is_template = 0 LEFT JOIN civicrm_membership_payment mp ON contribution.id = mp.contribution_id LEFT JOIN civicrm_membership m diff --git a/tests/phpunit/CRM/Report/Form/Contribute/fixtures/dataset-ascii.sql b/tests/phpunit/CRM/Report/Form/Contribute/fixtures/dataset-ascii.sql index 7a667dbdd6..32a9cc7142 100644 --- a/tests/phpunit/CRM/Report/Form/Contribute/fixtures/dataset-ascii.sql +++ b/tests/phpunit/CRM/Report/Form/Contribute/fixtures/dataset-ascii.sql @@ -59,17 +59,18 @@ INSERT INTO `civicrm_contact` (`id`, `contact_type`, `contact_sub_type`, `do_not -- Dumping data for table `civicrm_contribution` -- -INSERT INTO `civicrm_contribution` (`id`, `contact_id`, `financial_type_id`, `contribution_page_id`, `payment_instrument_id`, `receive_date`, `non_deductible_amount`, `total_amount`, `fee_amount`, `net_amount`, `trxn_id`, `invoice_id`, `currency`, `cancel_date`, `cancel_reason`, `receipt_date`, `thankyou_date`, `source`, `amount_level`, `contribution_recur_id`, `is_test`, `is_pay_later`, `contribution_status_id`, `address_id`, `check_number`, `campaign_id`) VALUES -(1, 2, 1, NULL, 4, '2010-04-11 00:00:00', 0.00, 125.00, NULL, NULL, NULL, NULL, 'USD', NULL, NULL, NULL, NULL, 'Apr 2007 Mailer 1', NULL, NULL, 0, 0, 1, NULL, '1041', NULL), -(2, 4, 1, NULL, 1, '2010-03-21 00:00:00', 0.00, 50.00, NULL, NULL, 'P20901X1', NULL, 'USD', NULL, NULL, NULL, NULL, 'Online: Save the Penguins', NULL, NULL, 0, 0, 1, NULL, NULL, NULL), -(3, 6, 1, NULL, 4, '2010-04-29 00:00:00', 0.00, 25.00, NULL, NULL, NULL, NULL, 'USD', NULL, NULL, NULL, NULL, 'Apr 2007 Mailer 1', NULL, NULL, 0, 0, 1, NULL, '2095', NULL), -(4, 8, 1, NULL, 4, '2010-04-11 00:00:00', 0.00, 50.00, NULL, NULL, NULL, NULL, 'USD', NULL, NULL, NULL, NULL, 'Apr 2007 Mailer 1', NULL, NULL, 0, 0, 1, NULL, '10552', NULL), -(5, 16, 1, NULL, 4, '2010-04-15 00:00:00', 0.00, 500.00, NULL, NULL, NULL, NULL, 'USD', NULL, NULL, NULL, NULL, 'Apr 2007 Mailer 1', NULL, NULL, 0, 0, 1, NULL, '509', NULL), -(6, 19, 1, NULL, 4, '2010-04-11 00:00:00', 0.00, 175.00, NULL, NULL, NULL, NULL, 'USD', NULL, NULL, NULL, NULL, 'Apr 2007 Mailer 1', NULL, NULL, 0, 0, 1, NULL, '102', NULL), -(7, 82, 1, NULL, 1, '2010-03-27 00:00:00', 0.00, 50.00, NULL, NULL, 'P20193L2', NULL, 'USD', NULL, NULL, NULL, NULL, 'Online: Save the Penguins', NULL, NULL, 0, 0, 1, NULL, NULL, NULL), -(8, 92, 1, NULL, 1, '2010-03-08 00:00:00', 0.00, 10.00, NULL, NULL, 'P40232Y3', NULL, 'USD', NULL, NULL, NULL, NULL, 'Online: Help CiviCRM', NULL, NULL, 0, 0, 1, NULL, NULL, NULL), -(9, 34, 1, NULL, 1, '2010-04-22 00:00:00', 0.00, 250.00, NULL, NULL, 'P20193L6', NULL, 'USD', NULL, NULL, NULL, NULL, 'Online: Help CiviCRM', NULL, NULL, 0, 0, 1, NULL, NULL, NULL), -(10, 71, 1, NULL, 1, '2009-07-01 11:53:50', 0.00, 500.00, NULL, NULL, 'PL71', NULL, 'USD', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, 0, 1, NULL, NULL, NULL); +INSERT INTO `civicrm_contribution` (`id`, `contact_id`, `financial_type_id`, `contribution_page_id`, `payment_instrument_id`, `receive_date`, `non_deductible_amount`, `total_amount`, `fee_amount`, `net_amount`, `trxn_id`, `invoice_id`, `currency`, `cancel_date`, `cancel_reason`, `receipt_date`, `thankyou_date`, `source`, `amount_level`, `contribution_recur_id`, `is_test`, `is_pay_later`, `contribution_status_id`, `address_id`, `check_number`, `campaign_id`, `is_template`) VALUES +(1, 2, 1, NULL, 4, '2010-04-11 00:00:00', 0.00, 125.00, NULL, NULL, NULL, NULL, 'USD', NULL, NULL, NULL, NULL, 'Apr 2007 Mailer 1', NULL, NULL, 0, 0, 1, NULL, '1041', NULL, 0), +(2, 4, 1, NULL, 1, '2010-03-21 00:00:00', 0.00, 50.00, NULL, NULL, 'P20901X1', NULL, 'USD', NULL, NULL, NULL, NULL, 'Online: Save the Penguins', NULL, NULL, 0, 0, 1, NULL, NULL, NULL, 0), +(3, 6, 1, NULL, 4, '2010-04-29 00:00:00', 0.00, 25.00, NULL, NULL, NULL, NULL, 'USD', NULL, NULL, NULL, NULL, 'Apr 2007 Mailer 1', NULL, NULL, 0, 0, 1, NULL, '2095', NULL, 0), +(4, 8, 1, NULL, 4, '2010-04-11 00:00:00', 0.00, 50.00, NULL, NULL, NULL, NULL, 'USD', NULL, NULL, NULL, NULL, 'Apr 2007 Mailer 1', NULL, NULL, 0, 0, 1, NULL, '10552', NULL, 0), +(5, 16, 1, NULL, 4, '2010-04-15 00:00:00', 0.00, 500.00, NULL, NULL, NULL, NULL, 'USD', NULL, NULL, NULL, NULL, 'Apr 2007 Mailer 1', NULL, NULL, 0, 0, 1, NULL, '509', NULL, 0), +(6, 19, 1, NULL, 4, '2010-04-11 00:00:00', 0.00, 175.00, NULL, NULL, NULL, NULL, 'USD', NULL, NULL, NULL, NULL, 'Apr 2007 Mailer 1', NULL, NULL, 0, 0, 1, NULL, '102', NULL, 0), +(7, 82, 1, NULL, 1, '2010-03-27 00:00:00', 0.00, 50.00, NULL, NULL, 'P20193L2', NULL, 'USD', NULL, NULL, NULL, NULL, 'Online: Save the Penguins', NULL, NULL, 0, 0, 1, NULL, NULL, NULL, 0), +(8, 92, 1, NULL, 1, '2010-03-08 00:00:00', 0.00, 10.00, NULL, NULL, 'P40232Y3', NULL, 'USD', NULL, NULL, NULL, NULL, 'Online: Help CiviCRM', NULL, NULL, 0, 0, 1, NULL, NULL, NULL, 0), +(9, 34, 1, NULL, 1, '2010-04-22 00:00:00', 0.00, 250.00, NULL, NULL, 'P20193L6', NULL, 'USD', NULL, NULL, NULL, NULL, 'Online: Help CiviCRM', NULL, NULL, 0, 0, 1, NULL, NULL, NULL, 0), +(10, 71, 1, NULL, 1, '2009-07-01 11:53:50', 0.00, 500.00, NULL, NULL, 'PL71', NULL, 'USD', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, 0, 1, NULL, NULL, NULL, 1), +(11, 71, 1, NULL, 1, '2009-07-01 11:53:51', 0.00, 500.00, NULL, NULL, 'PL72', NULL, 'USD', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, 0, 1, NULL, NULL, NULL, 0); -- -- Dumping data for table `civicrm_email` diff --git a/tests/phpunit/api/v3/ReportTemplateTest.php b/tests/phpunit/api/v3/ReportTemplateTest.php index 02a1202dbe..c0c25c5980 100644 --- a/tests/phpunit/api/v3/ReportTemplateTest.php +++ b/tests/phpunit/api/v3/ReportTemplateTest.php @@ -496,6 +496,7 @@ class api_v3_ReportTemplateTest extends CiviUnitTestCase { $expected = preg_replace('/\s+/', ' ', 'COLLATE ' . $inUseCollation . ' AS SELECT SQL_CALC_FOUND_ROWS contact_civireport.id as cid FROM civicrm_contact contact_civireport INNER JOIN civicrm_contribution contribution_civireport USE index (received_date) ON contribution_civireport.contact_id = contact_civireport.id AND contribution_civireport.is_test = 0 + AND contribution_civireport.is_template = 0 AND contribution_civireport.receive_date BETWEEN \'20140701000000\' AND \'20150630235959\' WHERE contact_civireport.id NOT IN ( SELECT cont_exclude.contact_id @@ -857,6 +858,7 @@ class api_v3_ReportTemplateTest extends CiviUnitTestCase { INNER JOIN civicrm_contribution contribution_civireport ON contact_civireport.id = contribution_civireport.contact_id AND contribution_civireport.is_test = 0 + AND contribution_civireport.is_template = 0 LEFT JOIN civicrm_contribution_soft contribution_soft_civireport ON contribution_soft_civireport.contribution_id = contribution_civireport.id AND contribution_soft_civireport.id = (SELECT MIN(id) FROM civicrm_contribution_soft cs WHERE cs.contribution_id = contribution_civireport.id) LEFT JOIN civicrm_financial_type financial_type_civireport -- 2.25.1