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
The following commit(s) were added to refs/heads/develop by this push:
new ceb9f0f783 FINERACT-2386: Update read query to support other products
ceb9f0f783 is described below
commit ceb9f0f7832d57d02f7ef81647090ee9a9d792e2
Author: Adam Saghy <[email protected]>
AuthorDate: Sat Oct 4 00:09:28 2025 +0200
FINERACT-2386: Update read query to support other products
---
.../JournalEntryAggregationJobReader.java | 82 +++++++++---
.../0200_add_journal_entry_aggregation_tables.xml | 140 ++++++++++++++++++---
..._with_asset_owner_journal_entry_aggregation.xml | 6 +-
3 files changed, 187 insertions(+), 41 deletions(-)
diff --git
a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/jobs/service/aggregationjob/JournalEntryAggregationJobReader.java
b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/jobs/service/aggregationjob/JournalEntryAggregationJobReader.java
index 111e2b8047..3b6d18e976 100644
---
a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/jobs/service/aggregationjob/JournalEntryAggregationJobReader.java
+++
b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/jobs/service/aggregationjob/JournalEntryAggregationJobReader.java
@@ -79,25 +79,75 @@ public class JournalEntryAggregationJobReader extends
JdbcCursorItemReader<Journ
private String buildAggregationQuery() {
return """
- SELECT lp.id AS productId,
- acc_gl_account.id AS glAccountId,
- acc_gl_journal_entry.entity_type_enum AS entityTypeEnum,
- acc_gl_journal_entry.office_id AS officeId,
- aw.owner_id AS externalOwner,
- 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,
- acc_gl_journal_entry.submitted_on_date as
aggregatedOnDate,
- acc_gl_journal_entry.currency_code as currencyCode
+ SELECT
+ COALESCE(
+ loan_product.id,
+ savings_product.id,
+ prov_product.id,
+ share_product.id
+ ) AS productId,
+ acc_gl_account.id AS glAccountId,
+ acc_gl_journal_entry.entity_type_enum AS entityTypeEnum,
+ acc_gl_journal_entry.office_id AS officeId,
+ aw.owner_id AS externalOwner,
+ 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,
+ acc_gl_journal_entry.submitted_on_date AS aggregatedOnDate,
+ acc_gl_journal_entry.currency_code AS currencyCode
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
+ JOIN acc_gl_journal_entry
+ ON acc_gl_account.id = acc_gl_journal_entry.account_id
+
+ -- entity_type_enum = 1 → LOAN
+ LEFT JOIN m_loan loan
+ ON loan.id = acc_gl_journal_entry.entity_id
+ AND acc_gl_journal_entry.entity_type_enum = 1
+ LEFT JOIN m_product_loan loan_product
+ ON loan_product.id = loan.product_id
+ AND acc_gl_journal_entry.entity_type_enum = 1
+
+ -- entity_type_enum = 2 → SAVING
+ LEFT JOIN m_savings_account savings
+ ON savings.id = acc_gl_journal_entry.entity_id
+ AND acc_gl_journal_entry.entity_type_enum = 2
+ LEFT JOIN m_savings_product savings_product
+ ON savings_product.id = savings.product_id
+ AND acc_gl_journal_entry.entity_type_enum = 2
+
+ -- entity_type_enum = 3 → PROVISIONING
+ LEFT JOIN m_provisioning_history prov
+ ON prov.id = acc_gl_journal_entry.entity_id
+ AND acc_gl_journal_entry.entity_type_enum = 3
+ LEFT JOIN m_loanproduct_provisioning_entry prov_entry
+ ON prov_entry.history_id = prov.id
+ AND acc_gl_journal_entry.entity_type_enum = 3
+ LEFT JOIN m_product_loan prov_product
+ ON prov_product.id = prov_entry.product_id
+ AND acc_gl_journal_entry.entity_type_enum = 3
+
+ -- entity_type_enum = 4 → SHARED
+ LEFT JOIN m_share_account share
+ ON share.id = acc_gl_journal_entry.entity_id
+ AND acc_gl_journal_entry.entity_type_enum = 4
+ LEFT JOIN m_share_product share_product
+ ON share_product.id = share.product_id
+ AND acc_gl_journal_entry.entity_type_enum = 4
+
+ -- external owner
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.submitted_on_date > ?
+ ON aw.journal_entry_id = acc_gl_journal_entry.id
+
+ WHERE acc_gl_journal_entry.submitted_on_date > ?
AND acc_gl_journal_entry.submitted_on_date <= ?
- GROUP BY productId, glAccountId, externalOwner,
aggregatedOnDate, currencyCode, entityTypeEnum, officeId
+
+ GROUP BY
+ productId,
+ glAccountId,
+ externalOwner,
+ aggregatedOnDate,
+ currencyCode,
+ entityTypeEnum,
+ officeId
""";
}
}
diff --git
a/fineract-provider/src/main/resources/db/changelog/tenant/parts/0200_add_journal_entry_aggregation_tables.xml
b/fineract-provider/src/main/resources/db/changelog/tenant/parts/0200_add_journal_entry_aggregation_tables.xml
index d690b5c6f7..f31806412e 100644
---
a/fineract-provider/src/main/resources/db/changelog/tenant/parts/0200_add_journal_entry_aggregation_tables.xml
+++
b/fineract-provider/src/main/resources/db/changelog/tenant/parts/0200_add_journal_entry_aggregation_tables.xml
@@ -112,26 +112,6 @@
<addForeignKeyConstraint baseColumnNames="created_by"
baseTableName="m_journal_entry_aggregation_tracking"
constraintName="FK_GL_JOURNAL_ENTRY_AGGREGATION_TRACKING_ON_CREATED_BY"
referencedColumnNames="id" referencedTableName="m_appuser"/>
<addForeignKeyConstraint baseColumnNames="last_modified_by"
baseTableName="m_journal_entry_aggregation_tracking"
constraintName="FK_GL_JOURNAL_ENTRY_AGGREGATION_TRACKING_ON_LAST_MODIFIED_BY"
referencedColumnNames="id" referencedTableName="m_appuser"/>
</changeSet>
- <changeSet id="4" author="fineract" runInTransaction="false"
context="postgresql">
- <preConditions onFail="MARK_RAN">
- <not>
- <indexExists tableName="m_journal_entry_aggregation_tracking"
columnNames="submitted_on_date"/>
- </not>
- </preConditions>
- <sql>
- create index concurrently idx_m_journal_entry_aggregation_tracking
on m_journal_entry_aggregation_tracking(submitted_on_date);
- </sql>
- </changeSet>
- <changeSet id="5" author="fineract" runInTransaction="false"
context="postgresql">
- <preConditions onFail="MARK_RAN">
- <not>
- <indexExists tableName="m_journal_entry_aggregation_tracking"
columnNames="aggregated_on_date_to"/>
- </not>
- </preConditions>
- <sql>
- create unique index concurrently
idx2_m_journal_entry_aggregation_tracking on
m_journal_entry_aggregation_tracking(aggregated_on_date_to);
- </sql>
- </changeSet>
<changeSet id="6" author="fineract" context="mysql">
<preConditions onFail="MARK_RAN">
@@ -223,6 +203,26 @@
<addForeignKeyConstraint baseColumnNames="created_by"
baseTableName="m_journal_entry_aggregation_tracking"
constraintName="FK_GL_JOURNAL_ENTRY_AGGREGATION_TRACKING_ON_CREATED_BY"
referencedColumnNames="id" referencedTableName="m_appuser"/>
<addForeignKeyConstraint baseColumnNames="last_modified_by"
baseTableName="m_journal_entry_aggregation_tracking"
constraintName="FK_GL_JOURNAL_ENTRY_AGGREGATION_TRACKING_ON_LAST_MODIFIED_BY"
referencedColumnNames="id" referencedTableName="m_appuser"/>
</changeSet>
+ <changeSet id="4" author="fineract" runInTransaction="false"
context="postgresql">
+ <preConditions onFail="MARK_RAN">
+ <not>
+ <indexExists tableName="m_journal_entry_aggregation_tracking"
columnNames="submitted_on_date"/>
+ </not>
+ </preConditions>
+ <sql>
+ create index concurrently idx_m_journal_entry_aggregation_tracking
on m_journal_entry_aggregation_tracking(submitted_on_date);
+ </sql>
+ </changeSet>
+ <changeSet id="5" author="fineract" runInTransaction="false"
context="postgresql">
+ <preConditions onFail="MARK_RAN">
+ <not>
+ <indexExists tableName="m_journal_entry_aggregation_tracking"
columnNames="aggregated_on_date_to"/>
+ </not>
+ </preConditions>
+ <sql>
+ create unique index concurrently
idx2_m_journal_entry_aggregation_tracking on
m_journal_entry_aggregation_tracking(aggregated_on_date_to);
+ </sql>
+ </changeSet>
<changeSet id="9" author="fineract" runInTransaction="false"
context="mysql">
<preConditions onFail="MARK_RAN">
<not>
@@ -243,4 +243,104 @@
create unique index idx2_m_journal_entry_aggregation_tracking on
m_journal_entry_aggregation_tracking(aggregated_on_date_to);
</sql>
</changeSet>
+ <changeSet id="11" author="fineract" runInTransaction="false"
context="postgresql">
+ <preConditions onFail="MARK_RAN">
+ <not>
+ <indexExists tableName="m_journal_entry_aggregation_tracking"
columnNames="job_execution_id"/>
+ </not>
+ </preConditions>
+ <sql>
+ create index concurrently idx_m_jour_ent_aggr_trac_job_exec_id on
m_journal_entry_aggregation_tracking(job_execution_id);
+ </sql>
+ </changeSet>
+ <changeSet id="12" author="fineract" runInTransaction="false"
context="mysql">
+ <preConditions onFail="MARK_RAN">
+ <not>
+ <indexExists tableName="m_journal_entry_aggregation_tracking"
columnNames="job_execution_id"/>
+ </not>
+ </preConditions>
+ <sql>
+ create index idx_m_jour_ent_aggr_trac_job_exec_id on
m_journal_entry_aggregation_tracking(job_execution_id);
+ </sql>
+ </changeSet>
+ <changeSet id="13" author="fineract" runInTransaction="false"
context="postgresql">
+ <preConditions onFail="MARK_RAN">
+ <not>
+ <indexExists tableName="m_journal_entry_aggregation_summary"
columnNames="job_execution_id"/>
+ </not>
+ </preConditions>
+ <sql>
+ create index concurrently idx_m_jour_ent_aggr_sum_job_exec_id on
m_journal_entry_aggregation_summary(job_execution_id);
+ </sql>
+ </changeSet>
+ <changeSet id="14" author="fineract" runInTransaction="false"
context="mysql">
+ <preConditions onFail="MARK_RAN">
+ <not>
+ <indexExists tableName="m_journal_entry_aggregation_summary"
columnNames="job_execution_id"/>
+ </not>
+ </preConditions>
+ <sql>
+ create index idx_m_jour_ent_aggr_sum_job_exec_id on
m_journal_entry_aggregation_summary(job_execution_id);
+ </sql>
+ </changeSet>
+ <changeSet id="15" author="fineract" runInTransaction="false"
context="postgresql">
+ <preConditions onFail="MARK_RAN">
+ <not>
+ <indexExists tableName="m_journal_entry_aggregation_summary"
columnNames="product_id,entity_type_enum"/>
+ </not>
+ </preConditions>
+ <sql>
+ create index concurrently
idx_m_jour_ent_aggr_sum_prod_id_entity_type on
m_journal_entry_aggregation_summary(product_id, entity_type_enum);
+ </sql>
+ </changeSet>
+ <changeSet id="16" author="fineract" runInTransaction="false"
context="mysql">
+ <preConditions onFail="MARK_RAN">
+ <not>
+ <indexExists tableName="m_journal_entry_aggregation_summary"
columnNames="product_id,entity_type_enum"/>
+ </not>
+ </preConditions>
+ <sql>
+ create index idx_m_jour_ent_aggr_sum_prod_id_entity_type on
m_journal_entry_aggregation_summary(product_id, entity_type_enum);
+ </sql>
+ </changeSet>
+ <changeSet id="17" author="fineract" runInTransaction="false"
context="postgresql">
+ <preConditions onFail="MARK_RAN">
+ <not>
+ <indexExists tableName="m_journal_entry_aggregation_summary"
columnNames="aggregated_on_date"/>
+ </not>
+ </preConditions>
+ <sql>
+ create index concurrently idx_m_jour_ent_aggr_sum_aggr_date on
m_journal_entry_aggregation_summary(aggregated_on_date);
+ </sql>
+ </changeSet>
+ <changeSet id="18" author="fineract" runInTransaction="false"
context="mysql">
+ <preConditions onFail="MARK_RAN">
+ <not>
+ <indexExists tableName="m_journal_entry_aggregation_summary"
columnNames="aggregated_on_date"/>
+ </not>
+ </preConditions>
+ <sql>
+ create index idx_m_jour_ent_aggr_sum_aggr_date on
m_journal_entry_aggregation_summary(aggregated_on_date);
+ </sql>
+ </changeSet>
+ <changeSet id="19" author="fineract" runInTransaction="false"
context="postgresql">
+ <preConditions onFail="MARK_RAN">
+ <not>
+ <indexExists tableName="m_journal_entry_aggregation_summary"
columnNames="office_id"/>
+ </not>
+ </preConditions>
+ <sql>
+ create index concurrently idx_m_jour_ent_aggr_sum_office_id on
m_journal_entry_aggregation_summary(office_id);
+ </sql>
+ </changeSet>
+ <changeSet id="20" author="fineract" runInTransaction="false"
context="mysql">
+ <preConditions onFail="MARK_RAN">
+ <not>
+ <indexExists tableName="m_journal_entry_aggregation_summary"
columnNames="office_id"/>
+ </not>
+ </preConditions>
+ <sql>
+ create index idx_m_jour_ent_aggr_sum_office_id on
m_journal_entry_aggregation_summary(office_id);
+ </sql>
+ </changeSet>
</databaseChangeLog>
diff --git
a/fineract-provider/src/main/resources/db/changelog/tenant/parts/0202_trial_balance_summary_with_asset_owner_journal_entry_aggregation.xml
b/fineract-provider/src/main/resources/db/changelog/tenant/parts/0202_trial_balance_summary_with_asset_owner_journal_entry_aggregation.xml
index c3ee28ddb7..902aca2959 100644
---
a/fineract-provider/src/main/resources/db/changelog/tenant/parts/0202_trial_balance_summary_with_asset_owner_journal_entry_aggregation.xml
+++
b/fineract-provider/src/main/resources/db/changelog/tenant/parts/0202_trial_balance_summary_with_asset_owner_journal_entry_aggregation.xml
@@ -23,7 +23,7 @@
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-1">
+ <changeSet author="fineract"
id="trial-balance-summary-with-asset-owner-update-2">
<update tableName="stretchy_report">
<column name="report_sql" value="WITH retained_earning AS (
SELECT DISTINCT '${endDate}' AS postingdate,
@@ -46,7 +46,6 @@
FROM m_journal_entry_aggregation_tracking
),
summary_snapshot_baseline_data AS (
- --using m_journal_entry_aggregation_summary to fetch aggregated journal
entry data
SELECT lp.NAME AS productname,
acc_gl_account.gl_code AS glcode,
acc_gl_account.NAME AS glname,
@@ -63,7 +62,6 @@
GROUP BY productname, glcode, glname, assetowner
),
post_snapshot_delta_data AS (
- -- still using acc_gl_journal_entry for dates after last aggregated data
and before the cob date
SELECT lp.NAME AS productname,
acc_gl_account.gl_code AS glcode,
acc_gl_account.NAME AS glname,
@@ -82,7 +80,6 @@
AND (acc_gl_journal_entry.office_id = ${officeId})
GROUP BY productname, glcode, glname, assetowner
),
- -- fetch all data before cob date
merged_historical_data AS (
SELECT summary_snapshot_baseline_data.productname,
summary_snapshot_baseline_data.glcode,
@@ -95,7 +92,6 @@
AND summary_snapshot_baseline_data.productname =
post_snapshot_delta_data.productname
AND summary_snapshot_baseline_data.assetowner =
post_snapshot_delta_data.assetowner
),
- -- fetch data for cob date
current_cob_data AS (
SELECT lp.name AS productname,
account_id,
