This is an automated email from the ASF dual-hosted git repository. adamsaghy pushed a commit to branch develop in repository https://gitbox.apache.org/repos/asf/fineract.git
commit 115063b89e00a942faa07819748ffb1fd95e4bff Author: Attila Budai <[email protected]> AuthorDate: Wed Feb 11 08:14:02 2026 +0100 FINERACT-2466: fix trial balance report Signed-off-by: Attila Budai <[email protected]> --- .../db/changelog/tenant/changelog-tenant.xml | 1 + ...summary_fix_external_owners_and_aggregation.xml | 242 ++++++++++++ .../client/feign/helpers/FeignAccountHelper.java | 6 + .../client/feign/helpers/FeignSchedulerHelper.java | 74 ++++ .../tests/FeignTrialBalanceSummaryReportTest.java | 427 +++++++++++++++++++++ 5 files changed, 750 insertions(+) diff --git a/fineract-provider/src/main/resources/db/changelog/tenant/changelog-tenant.xml b/fineract-provider/src/main/resources/db/changelog/tenant/changelog-tenant.xml index 55f8380e1e..297426a0ac 100644 --- a/fineract-provider/src/main/resources/db/changelog/tenant/changelog-tenant.xml +++ b/fineract-provider/src/main/resources/db/changelog/tenant/changelog-tenant.xml @@ -229,4 +229,5 @@ <include file="parts/0208_trial_balance_summary_with_asset_owner_journal_entry_aggregation_fix.xml" relativeToChangelogFile="true" /> <include file="parts/0209_transaction_summary_with_asset_owner_and_from_asset_owner_id_for_asset_sales.xml" relativeToChangelogFile="true" /> <include file="parts/0210_add_configuration_password_reuse_check_history_count.xml" relativeToChangelogFile="true" /> + <include file="parts/0211_trial_balance_summary_fix_external_owners_and_aggregation.xml" relativeToChangelogFile="true" /> </databaseChangeLog> diff --git a/fineract-provider/src/main/resources/db/changelog/tenant/parts/0211_trial_balance_summary_fix_external_owners_and_aggregation.xml b/fineract-provider/src/main/resources/db/changelog/tenant/parts/0211_trial_balance_summary_fix_external_owners_and_aggregation.xml new file mode 100644 index 0000000000..94bca487eb --- /dev/null +++ b/fineract-provider/src/main/resources/db/changelog/tenant/parts/0211_trial_balance_summary_fix_external_owners_and_aggregation.xml @@ -0,0 +1,242 @@ +<?xml version="1.0" encoding="UTF-8"?> +<!-- + + Licensed to the Apache Software Foundation (ASF) under one + or more contributor license agreements. See the NOTICE file + distributed with this work for additional information + regarding copyright ownership. The ASF licenses this file + to you under the Apache License, Version 2.0 (the + "License"); you may not use this file except in compliance + with the License. You may obtain a copy of the License at + + http://www.apache.org/licenses/LICENSE-2.0 + + Unless required by applicable law or agreed to in writing, + software distributed under the License is distributed on an + "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + KIND, either express or implied. See the License for the + specific language governing permissions and limitations + under the License. + +--> +<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog"; + xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"; + xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.3.xsd";> + + <changeSet author="fineract" id="trial-balance-summary-with-asset-owner-update-5"> + <update tableName="stretchy_report"> + <column name="report_sql"> + <![CDATA[ +WITH retained_earning AS ( + SELECT DISTINCT + '${endDate}' AS postingdate, + lp.name AS product, + gl_code AS glacct, + COALESCE((SELECT name FROM acc_gl_account WHERE gl_code = e.gl_code), '') AS description, + COALESCE(e.owner_external_id, 'self') AS assetowner, + SUM(opening_balance_amount) AS beginningbalance, + 0 AS debitmovement, + 0 AS creditmovement, + SUM(opening_balance_amount) AS endingbalance + FROM acc_gl_journal_entry_annual_summary e, m_product_loan lp + WHERE e.office_id = ${officeId} + AND lp.id = product_id + AND EXTRACT(YEAR FROM e.year_end_date) < EXTRACT(YEAR FROM CAST('${endDate}' AS DATE)) + GROUP BY gl_code, lp.name, office_id, owner_external_id +), +aggregated_date AS ( + SELECT MAX(aggregated_on_date_to) AS latest + FROM m_journal_entry_aggregation_tracking + WHERE aggregated_on_date_to < '${endDate}' +), +summary_snapshot_baseline_data AS ( + SELECT + lp.NAME AS productname, + acc_gl_account.gl_code AS glcode, + acc_gl_account.NAME AS glname, + CASE WHEN ags.external_owner_id IS NULL THEN 0 ELSE ags.external_owner_id END AS assetowner, + SUM(ags.debit_amount) AS debitamount, + SUM(ags.credit_amount) AS creditamount + FROM acc_gl_account + JOIN m_journal_entry_aggregation_summary ags ON acc_gl_account.id = ags.gl_account_id + JOIN m_product_loan lp ON lp.id = ags.product_id + WHERE ags.entity_type_enum = 1 + AND ags.manual_entry = FALSE + AND ags.aggregated_on_date <= (SELECT latest FROM aggregated_date) + AND (ags.office_id = ${officeId}) + GROUP BY productname, glcode, glname, assetowner +), +post_snapshot_delta_data AS ( + SELECT + lp.NAME AS productname, + acc_gl_account.gl_code AS glcode, + acc_gl_account.NAME AS glname, + CASE WHEN aw.owner_id IS NULL THEN 0 ELSE aw.owner_id END AS assetowner, + SUM(CASE WHEN acc_gl_journal_entry.type_enum = 2 THEN amount ELSE 0 END) AS debitamount, + SUM(CASE WHEN acc_gl_journal_entry.type_enum = 1 THEN amount ELSE 0 END) AS creditamount + FROM acc_gl_account + JOIN acc_gl_journal_entry ON acc_gl_account.id = acc_gl_journal_entry.account_id + JOIN m_loan m ON m.id = acc_gl_journal_entry.entity_id + JOIN m_product_loan lp ON lp.id = m.product_id + LEFT JOIN m_external_asset_owner_journal_entry_mapping aw ON aw.journal_entry_id = acc_gl_journal_entry.id + WHERE acc_gl_journal_entry.entity_type_enum = 1 + AND acc_gl_journal_entry.manual_entry = FALSE + AND ( + (SELECT latest FROM aggregated_date) IS NULL + OR acc_gl_journal_entry.submitted_on_date > (SELECT latest FROM aggregated_date) + ) + AND acc_gl_journal_entry.submitted_on_date < '${endDate}' + AND (acc_gl_journal_entry.office_id = ${officeId}) + GROUP BY productname, glcode, glname, assetowner +), +merged_historical_data AS ( + SELECT + COALESCE(s.productname, p.productname) AS productname, + COALESCE(s.glcode, p.glcode) AS glcode, + COALESCE(s.glname, p.glname) AS glname, + COALESCE(s.assetowner, p.assetowner, 0) AS assetowner, + COALESCE(s.debitamount, 0) + COALESCE(p.debitamount, 0) AS debitamount, + COALESCE(s.creditamount, 0) + COALESCE(p.creditamount, 0) AS creditamount + FROM summary_snapshot_baseline_data s + LEFT JOIN post_snapshot_delta_data p + ON s.glcode = p.glcode + AND s.productname = p.productname + AND s.assetowner = p.assetowner + + UNION ALL + + SELECT + p.productname AS productname, + p.glcode AS glcode, + p.glname AS glname, + COALESCE(p.assetowner, 0) AS assetowner, + COALESCE(p.debitamount, 0) AS debitamount, + COALESCE(p.creditamount, 0) AS creditamount + FROM post_snapshot_delta_data p + LEFT JOIN summary_snapshot_baseline_data s + ON s.glcode = p.glcode + AND s.productname = p.productname + AND s.assetowner = p.assetowner + WHERE s.glcode IS NULL +), +current_cob_data AS ( + SELECT + lp.name AS productname, + account_id, + acc_gl_account.gl_code AS glcode, + acc_gl_account.name AS glname, + CASE WHEN aw.owner_id IS NULL THEN 0 ELSE aw.owner_id END AS assetowner, + SUM(CASE WHEN acc_gl_journal_entry.type_enum = 2 THEN amount ELSE 0 END) AS debitamount, + SUM(CASE WHEN acc_gl_journal_entry.type_enum = 1 THEN amount ELSE 0 END) AS creditamount + FROM acc_gl_journal_entry + JOIN acc_gl_account ON acc_gl_account.id = acc_gl_journal_entry.account_id + JOIN m_loan m ON m.id = acc_gl_journal_entry.entity_id + JOIN m_product_loan lp ON lp.id = m.product_id + LEFT JOIN m_external_asset_owner_journal_entry_mapping aw ON aw.journal_entry_id = acc_gl_journal_entry.id + WHERE acc_gl_journal_entry.entity_type_enum = 1 + AND acc_gl_journal_entry.manual_entry = FALSE + AND acc_gl_journal_entry.submitted_on_date = '${endDate}' + AND (acc_gl_journal_entry.office_id = ${officeId}) + GROUP BY productname, account_id, glcode, glname, assetowner +) + +SELECT * +FROM ( + SELECT * + FROM retained_earning + WHERE glacct = (SELECT gl_code FROM acc_gl_account WHERE name = 'Retained Earnings Prior Year') + + UNION + + SELECT + txnreport.postingdate, + txnreport.product, + txnreport.glacct, + txnreport.description, + txnreport.assetowner, + (COALESCE(txnreport.beginningbalance, 0) + COALESCE(summary.beginningbalance, 0)) AS beginningbalance, + txnreport.debitmovement AS debitmovement, + txnreport.creditmovement AS creditmovement, + (COALESCE(txnreport.endingbalance, 0) + COALESCE(summary.beginningbalance, 0)) AS endingbalance + FROM ( + SELECT * + FROM ( + SELECT DISTINCT + '${endDate}' AS postingdate, + loan.pname AS product, + loan.gl_code AS glacct, + loan.glname AS description, + COALESCE((SELECT external_id FROM m_external_asset_owner WHERE id = loan.assetowner), 'self') AS assetowner, + loan.openingbalance AS beginningbalance, + (loan.debitamount * 1) AS debitmovement, + (loan.creditamount * -1) AS creditmovement, + (loan.openingbalance + loan.debitamount - loan.creditamount) AS endingbalance + FROM ( + SELECT DISTINCT + g.pname AS pname, + g.gl_code AS gl_code, + g.glname AS glname, + COALESCE(mh.assetowner, c.assetowner, 0) AS assetowner, + COALESCE(mh.debitamount, 0) - COALESCE(mh.creditamount, 0) AS openingbalance, + COALESCE(c.debitamount, 0) AS debitamount, + COALESCE(c.creditamount, 0) AS creditamount + FROM ( + SELECT DISTINCT ag.gl_code, ag.id, pl.NAME AS pname, ag.NAME AS glname + FROM acc_gl_account ag + JOIN acc_product_mapping am ON am.gl_account_id = ag.id AND am.product_type = 1 + JOIN m_product_loan pl ON pl.id = am.product_id + ) g + LEFT JOIN merged_historical_data mh + ON g.gl_code = mh.glcode + AND mh.productname = g.pname + LEFT JOIN current_cob_data c + ON g.gl_code = c.glcode + AND c.productname = g.pname + AND mh.assetowner = c.assetowner + + UNION ALL + + SELECT DISTINCT + c.productname AS pname, + c.glcode AS gl_code, + c.glname AS glname, + COALESCE(c.assetowner, 0) AS assetowner, + 0 AS openingbalance, + COALESCE(c.debitamount, 0) AS debitamount, + COALESCE(c.creditamount, 0) AS creditamount + FROM current_cob_data c + LEFT JOIN ( + SELECT g3.gl_code, g3.pname, mh.assetowner + FROM ( + SELECT DISTINCT ag.gl_code, pl.NAME AS pname + FROM acc_gl_account ag + JOIN acc_product_mapping am ON am.gl_account_id = ag.id AND am.product_type = 1 + JOIN m_product_loan pl ON pl.id = am.product_id + ) g3 + LEFT JOIN merged_historical_data mh + ON g3.gl_code = mh.glcode + AND mh.productname = g3.pname + ) matched + ON matched.gl_code = c.glcode + AND matched.pname = c.productname + AND matched.assetowner = c.assetowner + WHERE matched.gl_code IS NULL + ) loan + ) a + ) AS txnreport + LEFT JOIN retained_earning summary + ON txnreport.glacct = summary.glacct + AND txnreport.assetowner = summary.assetowner + AND summary.product = txnreport.product +) report +WHERE report.endingbalance != 0 + OR report.debitmovement != 0 + OR report.creditmovement != 0 +ORDER BY glacct + + ]]> + </column> + <where>report_name='Trial Balance Summary Report with Asset Owner'</where> + </update> + </changeSet> +</databaseChangeLog> diff --git a/integration-tests/src/test/java/org/apache/fineract/integrationtests/client/feign/helpers/FeignAccountHelper.java b/integration-tests/src/test/java/org/apache/fineract/integrationtests/client/feign/helpers/FeignAccountHelper.java index 4dc91104c1..163e968e40 100644 --- a/integration-tests/src/test/java/org/apache/fineract/integrationtests/client/feign/helpers/FeignAccountHelper.java +++ b/integration-tests/src/test/java/org/apache/fineract/integrationtests/client/feign/helpers/FeignAccountHelper.java @@ -71,6 +71,12 @@ public class FeignAccountHelper { return new Account(account.getId().intValue(), getAccountType(type)); } + public String getGlCode(Account account) { + GetGLAccountsResponse response = ok( + () -> fineractClient.generalLedgerAccount().retreiveAccount(account.getAccountID().longValue(), Collections.emptyMap())); + return response.getGlCode(); + } + private Integer getAccountTypeId(String type) { return switch (type) { case "ASSET" -> 1; diff --git a/integration-tests/src/test/java/org/apache/fineract/integrationtests/client/feign/helpers/FeignSchedulerHelper.java b/integration-tests/src/test/java/org/apache/fineract/integrationtests/client/feign/helpers/FeignSchedulerHelper.java new file mode 100644 index 0000000000..0528639f7b --- /dev/null +++ b/integration-tests/src/test/java/org/apache/fineract/integrationtests/client/feign/helpers/FeignSchedulerHelper.java @@ -0,0 +1,74 @@ +/** + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ +package org.apache.fineract.integrationtests.client.feign.helpers; + +import static org.apache.fineract.client.feign.util.FeignCalls.ok; + +import java.time.Duration; +import java.time.Instant; +import java.time.OffsetDateTime; +import java.time.temporal.ChronoUnit; +import java.util.List; +import org.apache.fineract.client.feign.FineractFeignClient; +import org.apache.fineract.client.feign.util.FeignCalls; +import org.apache.fineract.client.models.ExecuteJobRequest; +import org.apache.fineract.client.models.GetJobsResponse; +import org.apache.fineract.client.models.JobDetailHistoryData; +import org.awaitility.Awaitility; + +public class FeignSchedulerHelper { + + private final FineractFeignClient fineractClient; + + public FeignSchedulerHelper(FineractFeignClient fineractClient) { + this.fineractClient = fineractClient; + } + + public void stopScheduler() { + FeignCalls.executeVoid(() -> fineractClient.scheduler().changeSchedulerStatus("stop")); + } + + public void startScheduler() { + FeignCalls.executeVoid(() -> fineractClient.scheduler().changeSchedulerStatus("start")); + } + + public void executeAndAwaitJob(String jobDisplayName) { + stopScheduler(); + + List<GetJobsResponse> allJobs = ok(() -> fineractClient.schedulerJob().retrieveAll8()); + GetJobsResponse targetJob = allJobs.stream().filter(j -> jobDisplayName.equals(j.getDisplayName())).findFirst() + .orElseThrow(() -> new RuntimeException("Job not found: " + jobDisplayName)); + + Instant beforeExecuteTime = Instant.now().truncatedTo(ChronoUnit.SECONDS); + FeignCalls.executeVoid(() -> fineractClient.schedulerJob().executeJob(targetJob.getJobId(), "executeJob", new ExecuteJobRequest())); + + Awaitility.await().atMost(Duration.ofMinutes(2)).pollInterval(Duration.ofSeconds(1)).pollDelay(Duration.ofSeconds(1)).until(() -> { + GetJobsResponse job = ok(() -> fineractClient.schedulerJob().retrieveOne5(targetJob.getJobId())); + JobDetailHistoryData history = job.getLastRunHistory(); + if (history == null || history.getJobRunStartTime() == null) { + return false; + } + OffsetDateTime startTime = history.getJobRunStartTime(); + if (startTime.toInstant().isBefore(beforeExecuteTime)) { + return false; + } + return history.getJobRunEndTime() != null && !history.getJobRunEndTime().toInstant().isBefore(startTime.toInstant()); + }); + } +} diff --git a/integration-tests/src/test/java/org/apache/fineract/integrationtests/client/feign/tests/FeignTrialBalanceSummaryReportTest.java b/integration-tests/src/test/java/org/apache/fineract/integrationtests/client/feign/tests/FeignTrialBalanceSummaryReportTest.java new file mode 100644 index 0000000000..a33dfcd0b9 --- /dev/null +++ b/integration-tests/src/test/java/org/apache/fineract/integrationtests/client/feign/tests/FeignTrialBalanceSummaryReportTest.java @@ -0,0 +1,427 @@ +/** + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ +package org.apache.fineract.integrationtests.client.feign.tests; + +import static org.junit.jupiter.api.Assertions.assertFalse; +import static org.junit.jupiter.api.Assertions.assertNotNull; +import static org.junit.jupiter.api.Assertions.assertTrue; + +import java.math.BigDecimal; +import java.util.List; +import java.util.Map; +import java.util.UUID; +import java.util.stream.Collectors; +import java.util.stream.IntStream; +import lombok.extern.slf4j.Slf4j; +import org.apache.fineract.client.models.BusinessStep; +import org.apache.fineract.client.models.BusinessStepRequest; +import org.apache.fineract.client.models.ChargeRequest; +import org.apache.fineract.client.models.ExternalAssetOwnerRequest; +import org.apache.fineract.client.models.GetFinancialActivityAccountsResponse; +import org.apache.fineract.client.models.PostClientsRequest; +import org.apache.fineract.client.models.PostFinancialActivityAccountsRequest; +import org.apache.fineract.client.models.PostInitiateTransferResponse; +import org.apache.fineract.client.models.PostLoanProductsRequest; +import org.apache.fineract.client.models.PostLoansLoanIdChargesRequest; +import org.apache.fineract.client.models.PostLoansLoanIdRequest; +import org.apache.fineract.client.models.PostLoansLoanIdTransactionsRequest; +import org.apache.fineract.client.models.PostLoansRequest; +import org.apache.fineract.client.models.ResultsetColumnHeaderData; +import org.apache.fineract.client.models.ResultsetRowData; +import org.apache.fineract.client.models.RunReportsResponse; +import org.apache.fineract.integrationtests.client.FeignIntegrationTest; +import org.apache.fineract.integrationtests.client.feign.helpers.FeignAccountHelper; +import org.apache.fineract.integrationtests.client.feign.helpers.FeignBusinessDateHelper; +import org.apache.fineract.integrationtests.client.feign.helpers.FeignClientHelper; +import org.apache.fineract.integrationtests.client.feign.helpers.FeignGlobalConfigurationHelper; +import org.apache.fineract.integrationtests.client.feign.helpers.FeignLoanHelper; +import org.apache.fineract.integrationtests.client.feign.helpers.FeignSchedulerHelper; +import org.apache.fineract.integrationtests.client.feign.helpers.FeignTransactionHelper; +import org.apache.fineract.integrationtests.client.feign.modules.LoanTestData; +import org.apache.fineract.integrationtests.common.Utils; +import org.apache.fineract.integrationtests.common.accounting.Account; +import org.junit.jupiter.api.AfterAll; +import org.junit.jupiter.api.Assertions; +import org.junit.jupiter.api.BeforeAll; +import org.junit.jupiter.api.MethodOrderer; +import org.junit.jupiter.api.Order; +import org.junit.jupiter.api.Test; +import org.junit.jupiter.api.TestMethodOrder; + +@Slf4j +@TestMethodOrder(MethodOrderer.OrderAnnotation.class) +public class FeignTrialBalanceSummaryReportTest extends FeignIntegrationTest { + + private static final String REPORT_NAME = "Trial Balance Summary Report with Asset Owner"; + private static final BigDecimal LOAN_PRINCIPAL = BigDecimal.valueOf(15000.0); + + private FeignAccountHelper accountHelper; + private FeignGlobalConfigurationHelper globalConfigHelper; + private FeignBusinessDateHelper businessDateHelper; + private FeignLoanHelper loanHelper; + private FeignTransactionHelper transactionHelper; + private FeignClientHelper clientHelper; + private FeignSchedulerHelper schedulerHelper; + + private Account assetAccount; + private Account feePenaltyAccount; + private Account transferAccount; + private Account expenseAccount; + private Account incomeAccount; + private Account overpaymentAccount; + private String todaysDate; + private List<BusinessStep> originalBusinessSteps; + private List<GetFinancialActivityAccountsResponse> originalFinancialMappings; + + @BeforeAll + public void setup() { + accountHelper = new FeignAccountHelper(fineractClient()); + globalConfigHelper = new FeignGlobalConfigurationHelper(fineractClient()); + businessDateHelper = new FeignBusinessDateHelper(fineractClient()); + loanHelper = new FeignLoanHelper(fineractClient()); + transactionHelper = new FeignTransactionHelper(fineractClient()); + clientHelper = new FeignClientHelper(fineractClient()); + schedulerHelper = new FeignSchedulerHelper(fineractClient()); + + todaysDate = Utils.getLocalDateOfTenant().toString(); + + originalBusinessSteps = ok( + () -> fineractClient().businessStepConfiguration().retrieveAllConfiguredBusinessStep("LOAN_CLOSE_OF_BUSINESS")) + .getBusinessSteps(); + originalFinancialMappings = ok(() -> fineractClient().mappingFinancialActivitiesToAccounts().retrieveAll()); + + configureBusinessSteps(); + + assetAccount = accountHelper.createAssetAccount("TrialBal"); + feePenaltyAccount = accountHelper.createAssetAccount("TrialBalFP"); + transferAccount = accountHelper.createAssetAccount("TrialBalTR"); + expenseAccount = accountHelper.createExpenseAccount("TrialBalEXP"); + incomeAccount = accountHelper.createIncomeAccount("TrialBalINC"); + overpaymentAccount = accountHelper.createLiabilityAccount("TrialBalOP"); + + setupFinancialActivityMapping(); + } + + @AfterAll + public void tearDown() { + executeVoid(() -> fineractClient().businessStepConfiguration().updateJobBusinessStepConfig("LOAN_CLOSE_OF_BUSINESS", + new BusinessStepRequest().businessSteps(originalBusinessSteps))); + List<GetFinancialActivityAccountsResponse> currentMappings = ok( + () -> fineractClient().mappingFinancialActivitiesToAccounts().retrieveAll()); + for (GetFinancialActivityAccountsResponse mapping : currentMappings) { + executeVoid(() -> fineractClient().mappingFinancialActivitiesToAccounts().deleteGLAccount(mapping.getId())); + } + for (GetFinancialActivityAccountsResponse mapping : originalFinancialMappings) { + ok(() -> fineractClient().mappingFinancialActivitiesToAccounts() + .createGLAccount(new PostFinancialActivityAccountsRequest() + .financialActivityId(Long.valueOf(mapping.getFinancialActivityData().getId())) + .glAccountId(mapping.getGlAccountData().getId()))); + } + schedulerHelper.startScheduler(); + } + + @Test + @Order(1) + public void testReportReturnsExpectedColumns() { + RunReportsResponse response = runReport("2020-01-01"); + + assertNotNull(response); + assertNotNull(response.getColumnHeaders()); + assertFalse(response.getColumnHeaders().isEmpty()); + + List<String> expectedColumns = List.of("postingdate", "product", "glacct", "description", "assetowner", "beginningbalance", + "debitmovement", "creditmovement", "endingbalance"); + List<String> actualColumns = response.getColumnHeaders().stream().map(ResultsetColumnHeaderData::getColumnName).toList(); + Assertions.assertEquals(expectedColumns, actualColumns); + } + + @Test + @Order(2) + public void testExternalAssetOwnerEntriesAppearInReport() { + runWithBusinessDate("2020-03-02", () -> { + Long clientId = createClient("01 March 2020"); + Long loanId = createAndDisburseLoan(clientId, "01 March 2020", "02 March 2020", null); + + String ownerExternalId = UUID.randomUUID().toString(); + PostInitiateTransferResponse saleResponse = ok(() -> fineractClient().externalAssetOwners().transferRequestWithLoanId(loanId, + new ExternalAssetOwnerRequest().settlementDate("2020-03-02").dateFormat("yyyy-MM-dd").locale(LoanTestData.LOCALE) + .transferExternalId(UUID.randomUUID().toString()).ownerExternalId(ownerExternalId).purchasePriceRatio("1.0"), + "sale")); + assertNotNull(saleResponse); + + advanceBusinessDateAndRunCob("2020-03-03"); + + RunReportsResponse report = runReport("2020-03-02"); + assertNotNull(report.getData()); + + int assetOwnerColIdx = findColumnIndex(report, "assetowner"); + boolean hasExternalOwnerEntry = report.getData().stream() + .anyMatch(row -> ownerExternalId.equals(String.valueOf(row.getRow().get(assetOwnerColIdx)))); + + assertTrue(hasExternalOwnerEntry, + "Report must contain entries for external asset owner '" + ownerExternalId + "'. Actual rows: " + report.getData()); + }); + } + + @Test + @Order(3) + public void testBalanceFormulaIsConsistent() { + runWithBusinessDate("2020-06-01", () -> { + Long clientId = createClient("01 June 2020"); + Long chargeId = createFlatFeeCharge(500.0); + Long loanId = createAndDisburseLoan(clientId, "01 June 2020", "01 June 2020", chargeId); + + String productName = loanHelper.getLoanDetails(loanId).getLoanProductName(); + + transactionHelper.addRepayment(loanId, new PostLoansLoanIdTransactionsRequest().transactionDate("01 June 2020") + .transactionAmount(1500.0).locale(LoanTestData.LOCALE).dateFormat(LoanTestData.DATETIME_PATTERN)); + + RunReportsResponse report = runReport("2020-06-01"); + assertNotNull(report.getData()); + + int productIdx = findColumnIndex(report, "product"); + int assetOwnerIdx = findColumnIndex(report, "assetowner"); + int beginBalIdx = findColumnIndex(report, "beginningbalance"); + int debitIdx = findColumnIndex(report, "debitmovement"); + int creditIdx = findColumnIndex(report, "creditmovement"); + int endingBalIdx = findColumnIndex(report, "endingbalance"); + int glAcctIdx = findColumnIndex(report, "glacct"); + + List<ResultsetRowData> productRows = report.getData().stream() + .filter(row -> productName.equals(String.valueOf(row.getRow().get(productIdx)))).toList(); + + assertFalse(productRows.isEmpty(), "Report must contain entries for product '" + productName + "'."); + + boolean allSelfOwned = productRows.stream().allMatch(row -> "self".equals(String.valueOf(row.getRow().get(assetOwnerIdx)))); + assertTrue(allSelfOwned, "All entries should be 'self' when no external asset owner transfer exists."); + + assertTrue(productRows.size() >= 2, "Report must contain at least 2 GL account rows for a product with charges."); + + String assetGlCode = accountHelper.getGlCode(assetAccount); + String feeGlCode = accountHelper.getGlCode(feePenaltyAccount); + boolean hasAssetRow = productRows.stream().anyMatch(row -> assetGlCode.equals(String.valueOf(row.getRow().get(glAcctIdx)))); + boolean hasFeeRow = productRows.stream().anyMatch(row -> feeGlCode.equals(String.valueOf(row.getRow().get(glAcctIdx)))); + assertTrue(hasAssetRow, "Report must contain asset account row."); + assertTrue(hasFeeRow, "Report must contain fee/penalty receivable row."); + + for (ResultsetRowData row : productRows) { + BigDecimal beginBal = parseBigDecimal(row.getRow().get(beginBalIdx)); + BigDecimal debit = parseBigDecimal(row.getRow().get(debitIdx)); + BigDecimal credit = parseBigDecimal(row.getRow().get(creditIdx)); + BigDecimal endBal = parseBigDecimal(row.getRow().get(endingBalIdx)); + + Assertions.assertEquals(0, BigDecimal.ZERO.compareTo(beginBal), + "Beginning balance should be 0 when no prior year data exists. Row: " + row.getRow()); + + BigDecimal expectedEndBal = beginBal.add(debit).add(credit); + Assertions.assertEquals(0, expectedEndBal.compareTo(endBal), + "Ending balance must equal beginning + debit + credit. Expected: " + expectedEndBal + ", Actual: " + endBal); + } + }); + } + + @Test + @Order(4) + public void testNoRetainedEarningsWithoutAnnualSummary() { + RunReportsResponse report = runReport("2020-06-01"); + assertNotNull(report); + assertNotNull(report.getData()); + + int glAcctIdx = findColumnIndex(report, "glacct"); + boolean hasRetainedEarningsRow = report.getData().stream() + .anyMatch(row -> "320000".equals(String.valueOf(row.getRow().get(glAcctIdx)))); + assertFalse(hasRetainedEarningsRow, "Report should not contain Retained Earnings rows when no annual summary data exists."); + } + + private void configureBusinessSteps() { + List<String> stepNames = List.of("APPLY_CHARGE_TO_OVERDUE_LOANS", "LOAN_DELINQUENCY_CLASSIFICATION", "CHECK_LOAN_REPAYMENT_DUE", + "CHECK_LOAN_REPAYMENT_OVERDUE", "UPDATE_LOAN_ARREARS_AGING", "ADD_PERIODIC_ACCRUAL_ENTRIES", + "EXTERNAL_ASSET_OWNER_TRANSFER"); + List<BusinessStep> steps = IntStream.range(0, stepNames.size()) + .mapToObj(i -> new BusinessStep().stepName(stepNames.get(i)).order((long) (i + 1))).collect(Collectors.toList()); + executeVoid(() -> fineractClient().businessStepConfiguration().updateJobBusinessStepConfig("LOAN_CLOSE_OF_BUSINESS", + new BusinessStepRequest().businessSteps(steps))); + } + + private void setupFinancialActivityMapping() { + List<GetFinancialActivityAccountsResponse> mappings = ok( + () -> fineractClient().mappingFinancialActivitiesToAccounts().retrieveAll()); + for (GetFinancialActivityAccountsResponse mapping : mappings) { + executeVoid(() -> fineractClient().mappingFinancialActivitiesToAccounts().deleteGLAccount(mapping.getId())); + } + ok(() -> fineractClient().mappingFinancialActivitiesToAccounts().createGLAccount( + new PostFinancialActivityAccountsRequest().financialActivityId(100L).glAccountId((long) transferAccount.getAccountID()))); + } + + private void runWithBusinessDate(String date, Runnable action) { + try { + globalConfigHelper.updateConfigurationByName("enable-business-date", true); + globalConfigHelper.updateConfigurationByName("enable-auto-generated-external-id", true); + businessDateHelper.updateBusinessDate("BUSINESS_DATE", date); + action.run(); + } finally { + businessDateHelper.updateBusinessDate("BUSINESS_DATE", todaysDate); + globalConfigHelper.updateConfigurationByName("enable-business-date", false); + globalConfigHelper.updateConfigurationByName("enable-auto-generated-external-id", false); + } + } + + private void advanceBusinessDateAndRunCob(String date) { + businessDateHelper.updateBusinessDate("BUSINESS_DATE", date); + schedulerHelper.executeAndAwaitJob("Loan COB"); + } + + private RunReportsResponse runReport(String endDate) { + return ok(() -> fineractClient().runReports().runReportGetData(REPORT_NAME, Map.of("R_endDate", endDate, "R_officeId", "1"))); + } + + private int findColumnIndex(RunReportsResponse report, String columnName) { + List<ResultsetColumnHeaderData> headers = report.getColumnHeaders(); + for (int i = 0; i < headers.size(); i++) { + if (columnName.equals(headers.get(i).getColumnName())) { + return i; + } + } + throw new IllegalArgumentException("Column '" + columnName + "' not found. Available: " + + headers.stream().map(ResultsetColumnHeaderData::getColumnName).toList()); + } + + private BigDecimal parseBigDecimal(Object value) { + if (value == null) { + return BigDecimal.ZERO; + } + String str = String.valueOf(value); + if (str.isEmpty() || "null".equalsIgnoreCase(str)) { + return BigDecimal.ZERO; + } + return new BigDecimal(str); + } + + private Long createClient(String activationDate) { + return clientHelper.createClient(new PostClientsRequest()// + .officeId(1L)// + .legalFormId(1L)// + .firstname(Utils.randomFirstNameGenerator())// + .lastname(Utils.randomLastNameGenerator())// + .externalId(Utils.randomStringGenerator("EXT_", 7))// + .active(true)// + .activationDate(activationDate)// + .dateFormat(LoanTestData.DATETIME_PATTERN)// + .locale(LoanTestData.LOCALE)); + } + + private Long createAndDisburseLoan(Long clientId, String submitDate, String disburseDate, Long chargeId) { + Long loanProductId = createLoanProduct(); + assertNotNull(loanProductId); + + PostLoansRequest loanRequest = new PostLoansRequest()// + .clientId(clientId)// + .productId(loanProductId)// + .loanType("individual")// + .submittedOnDate(submitDate)// + .expectedDisbursementDate(submitDate)// + .principal(LOAN_PRINCIPAL)// + .loanTermFrequency(4)// + .loanTermFrequencyType(2)// + .numberOfRepayments(4)// + .repaymentEvery(1)// + .repaymentFrequencyType(2)// + .interestRatePerPeriod(BigDecimal.valueOf(2.0))// + .amortizationType(1)// + .interestType(0)// + .interestCalculationPeriodType(1)// + .transactionProcessingStrategyCode("mifos-standard-strategy")// + .locale(LoanTestData.LOCALE)// + .dateFormat(LoanTestData.DATETIME_PATTERN); + + Long loanId = loanHelper.applyForLoan(loanRequest); + assertNotNull(loanId); + + if (chargeId != null) { + ok(() -> fineractClient().loanCharges().executeLoanCharge(loanId, new PostLoansLoanIdChargesRequest().chargeId(chargeId) + .amount(500.0).dueDate(disburseDate).dateFormat(LoanTestData.DATETIME_PATTERN).locale(LoanTestData.LOCALE), + (String) null)); + } + + loanHelper.approveLoan(loanId, new PostLoansLoanIdRequest()// + .approvedLoanAmount(LOAN_PRINCIPAL)// + .approvedOnDate(submitDate)// + .locale(LoanTestData.LOCALE)// + .dateFormat(LoanTestData.DATETIME_PATTERN)); + + var loanDetails = loanHelper.getLoanDetails(loanId); + loanHelper.disburseLoan(loanId, new PostLoansLoanIdRequest()// + .actualDisbursementDate(disburseDate)// + .transactionAmount(loanDetails.getNetDisbursalAmount())// + .locale(LoanTestData.LOCALE)// + .dateFormat(LoanTestData.DATETIME_PATTERN)); + + return loanId; + } + + private Long createFlatFeeCharge(double amount) { + return ok(() -> fineractClient().charges().createCharge(new ChargeRequest()// + .name("TB Fee " + System.currentTimeMillis())// + .currencyCode("USD")// + .chargeAppliesTo(1)// + .chargeTimeType(2)// + .chargeCalculationType(1)// + .chargePaymentMode(0)// + .amount(amount)// + .active(true)// + .locale(LoanTestData.LOCALE))).getResourceId(); + } + + private Long createLoanProduct() { + return loanHelper.createLoanProduct(new PostLoanProductsRequest()// + .name("TrialBal Product " + System.currentTimeMillis())// + .shortName(UUID.randomUUID().toString().substring(0, 4).toUpperCase())// + .currencyCode("USD")// + .digitsAfterDecimal(2)// + .inMultiplesOf(1)// + .principal(LOAN_PRINCIPAL.doubleValue())// + .numberOfRepayments(4)// + .repaymentEvery(1)// + .repaymentFrequencyType(2L)// + .interestRatePerPeriod(1.0)// + .interestRateFrequencyType(2)// + .amortizationType(1)// + .interestType(0)// + .interestCalculationPeriodType(1)// + .transactionProcessingStrategyCode("mifos-standard-strategy")// + .daysInYearType(365)// + .daysInMonthType(30)// + .isInterestRecalculationEnabled(false)// + .accountingRule(3)// + .loanPortfolioAccountId((long) assetAccount.getAccountID())// + .transfersInSuspenseAccountId((long) assetAccount.getAccountID())// + .interestOnLoanAccountId((long) incomeAccount.getAccountID())// + .incomeFromFeeAccountId((long) incomeAccount.getAccountID())// + .incomeFromPenaltyAccountId((long) incomeAccount.getAccountID())// + .writeOffAccountId((long) expenseAccount.getAccountID())// + .overpaymentLiabilityAccountId((long) overpaymentAccount.getAccountID())// + .receivableInterestAccountId((long) feePenaltyAccount.getAccountID())// + .receivableFeeAccountId((long) feePenaltyAccount.getAccountID())// + .receivablePenaltyAccountId((long) feePenaltyAccount.getAccountID())// + .fundSourceAccountId((long) assetAccount.getAccountID())// + .incomeFromRecoveryAccountId((long) incomeAccount.getAccountID())// + .locale(LoanTestData.LOCALE)// + .dateFormat(LoanTestData.DATETIME_PATTERN)); + } +}
