ShruthiRajaram closed pull request #458: FINERACT-637: Implementing Trial balance job URL: https://github.com/apache/fineract/pull/458
This is a PR merged from a forked repository. As GitHub hides the original diff on merge, it is displayed below for the sake of provenance: As this is a foreign pull request (from a fork), the diff is supplied below (as it won't show otherwise due to GitHub magic): diff --git a/fineract-provider/src/main/java/org/apache/fineract/accounting/glaccount/domain/TrialBalance.java b/fineract-provider/src/main/java/org/apache/fineract/accounting/glaccount/domain/TrialBalance.java new file mode 100644 index 000000000..398354dba --- /dev/null +++ b/fineract-provider/src/main/java/org/apache/fineract/accounting/glaccount/domain/TrialBalance.java @@ -0,0 +1,108 @@ +/** + * 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.accounting.glaccount.domain; + + +import org.apache.fineract.infrastructure.core.domain.AbstractPersistableCustom; + + +import javax.persistence.*; +import java.math.BigDecimal; +import java.util.Date; + +@Entity +@Table(name = "m_trial_balance") +public class TrialBalance extends AbstractPersistableCustom<Long> { + + @Column(name = "office_id", nullable = false) + private Long officeId; + + @Column(name = "account_id", nullable = false) + private Long glAccountId; + + @Column(name = "amount", nullable = false) + private BigDecimal amount; + + @Column(name = "entry_date", nullable = false) + @Temporal(TemporalType.DATE) + private Date entryDate; + + @Column(name = "created_date", nullable = true) + @Temporal(TemporalType.DATE) + private Date transactionDate; + + @Column(name = "closing_balance", nullable = false) + private BigDecimal closingBalance; + + public static TrialBalance getInstance(final Long officeId, final Long glAccountId, + final BigDecimal amount, final Date entryDate, final Date transactionDate) { + return new TrialBalance(officeId, glAccountId, amount, entryDate, transactionDate); + } + + private TrialBalance(final Long officeId, final Long glAccountId, + final BigDecimal amount, final Date entryDate, final Date transactionDate) { + this.officeId = officeId; + this.glAccountId = glAccountId; + this.amount = amount; + this.entryDate = entryDate; + this.transactionDate = transactionDate; + } + + protected TrialBalance() { + + } + + public Long getOfficeId() { + return officeId; + } + + public Date getTransactionDate() { + return transactionDate; + } + + public BigDecimal getAmount() { + return amount; + } + + public void setClosingBalance(final BigDecimal closingBalance) { + this.closingBalance = closingBalance; + } + + public Date getEntryDate() { + return entryDate; + } + + public Long getGlAccountId() { + return glAccountId; + } + + @Override + public boolean equals(Object obj) { + if (!obj.getClass().equals(getClass())) return false; + TrialBalance trialBalance = (TrialBalance) obj; + return trialBalance.getOfficeId().equals(this.getOfficeId()) + && trialBalance.getGlAccountId().equals(this.getGlAccountId()) + && trialBalance.getEntryDate().equals(this.getEntryDate()) + && trialBalance.getTransactionDate().equals(this.getTransactionDate()); + } + + + +} \ No newline at end of file diff --git a/fineract-provider/src/main/java/org/apache/fineract/accounting/glaccount/domain/TrialBalanceRepository.java b/fineract-provider/src/main/java/org/apache/fineract/accounting/glaccount/domain/TrialBalanceRepository.java new file mode 100644 index 000000000..455986cf5 --- /dev/null +++ b/fineract-provider/src/main/java/org/apache/fineract/accounting/glaccount/domain/TrialBalanceRepository.java @@ -0,0 +1,33 @@ +/** + * 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.accounting.glaccount.domain; + +import org.springframework.data.jpa.repository.JpaRepository; +import org.springframework.data.jpa.repository.JpaSpecificationExecutor; +import org.springframework.data.jpa.repository.Query; +import org.springframework.data.repository.query.Param; + +import java.util.List; + +public interface TrialBalanceRepository extends JpaRepository<TrialBalance, Long>, JpaSpecificationExecutor<TrialBalance> { + + @Query(value = "select * from m_trial_balance where office_id=:officeId and account_id=:accountId and closing_balance is null order by created_date, entry_date", nativeQuery = true) + List<TrialBalance> findNewByOfficeAndAccount(@Param("officeId") final Long officeId, + @Param("accountId") final Long accountId); +} \ No newline at end of file diff --git a/fineract-provider/src/main/java/org/apache/fineract/accounting/glaccount/domain/TrialBalanceRepositoryWrapper.java b/fineract-provider/src/main/java/org/apache/fineract/accounting/glaccount/domain/TrialBalanceRepositoryWrapper.java new file mode 100644 index 000000000..359bf0abb --- /dev/null +++ b/fineract-provider/src/main/java/org/apache/fineract/accounting/glaccount/domain/TrialBalanceRepositoryWrapper.java @@ -0,0 +1,47 @@ +/** + * 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.accounting.glaccount.domain; + +import org.apache.fineract.accounting.trialbalance.exception.TrialBalanceNotFoundException; +import org.springframework.beans.factory.annotation.Autowired; +import org.springframework.stereotype.Service; + +import java.util.List; + +@Service +public class TrialBalanceRepositoryWrapper { + private final TrialBalanceRepository repository; + + @Autowired + public TrialBalanceRepositoryWrapper(final TrialBalanceRepository repository){ + this.repository=repository; + } + + public List<TrialBalance> findNewByOfficeAndAccount(final Long officeId, final Long accountId){ + final List<TrialBalance> trialBalanceList=this.repository.findNewByOfficeAndAccount(officeId,accountId); + if (trialBalanceList==null) + throw new TrialBalanceNotFoundException(officeId,accountId); + return trialBalanceList; + + } + + public void save(final List<TrialBalance> tbRows){ + this.repository.save(tbRows); + } +} diff --git a/fineract-provider/src/main/java/org/apache/fineract/accounting/trialbalance/exception/TrialBalanceNotFoundException.java b/fineract-provider/src/main/java/org/apache/fineract/accounting/trialbalance/exception/TrialBalanceNotFoundException.java new file mode 100644 index 000000000..9660405c1 --- /dev/null +++ b/fineract-provider/src/main/java/org/apache/fineract/accounting/trialbalance/exception/TrialBalanceNotFoundException.java @@ -0,0 +1,28 @@ +/** + * 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.accounting.trialbalance.exception; + +import org.apache.fineract.infrastructure.core.exception.AbstractPlatformResourceNotFoundException; + +public class TrialBalanceNotFoundException extends AbstractPlatformResourceNotFoundException{ + public TrialBalanceNotFoundException(final Long officeId,final Long accountId){ + super("error.msg.trial.balance.invalid", "Trial balance with officeId " + officeId +"and accountId"+accountId+" does not exist"); + + } +} diff --git a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/jobs/service/JobName.java b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/jobs/service/JobName.java index 19ad8e14c..3d76a3e20 100755 --- a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/jobs/service/JobName.java +++ b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/jobs/service/JobName.java @@ -49,7 +49,8 @@ GENERATE_ADHOCCLIENT_SCEHDULE("Generate AdhocClient Schedule"), SEND_MESSAGES_TO_EMAIL_GATEWAY("Send messages to Email gateway"), UPDATE_EMAIL_OUTBOUND_WITH_CAMPAIGN_MESSAGE("Update Email Outbound with campaign message"), - EXECUTE_EMAIL("Execute Email"); + EXECUTE_EMAIL("Execute Email"), + UPDATE_TRAIL_BALANCE_DETAILS("Update Trial Balance Details"); private final String name; diff --git a/fineract-provider/src/main/java/org/apache/fineract/scheduledjobs/service/ScheduledJobRunnerService.java b/fineract-provider/src/main/java/org/apache/fineract/scheduledjobs/service/ScheduledJobRunnerService.java index 6be117ed5..5199b7514 100644 --- a/fineract-provider/src/main/java/org/apache/fineract/scheduledjobs/service/ScheduledJobRunnerService.java +++ b/fineract-provider/src/main/java/org/apache/fineract/scheduledjobs/service/ScheduledJobRunnerService.java @@ -20,6 +20,8 @@ import org.apache.fineract.infrastructure.jobs.exception.JobExecutionException; +import java.util.Map; + public interface ScheduledJobRunnerService { void updateLoanSummaryDetails(); @@ -37,4 +39,6 @@ void generateRDSchedule(); void postDividends() throws JobExecutionException; + + void updateTrialBalanceDetails() throws JobExecutionException; } diff --git a/fineract-provider/src/main/java/org/apache/fineract/scheduledjobs/service/ScheduledJobRunnerServiceImpl.java b/fineract-provider/src/main/java/org/apache/fineract/scheduledjobs/service/ScheduledJobRunnerServiceImpl.java index 26aac81b3..29a337cd6 100644 --- a/fineract-provider/src/main/java/org/apache/fineract/scheduledjobs/service/ScheduledJobRunnerServiceImpl.java +++ b/fineract-provider/src/main/java/org/apache/fineract/scheduledjobs/service/ScheduledJobRunnerServiceImpl.java @@ -20,11 +20,18 @@ import java.math.BigDecimal; import java.math.BigInteger; +import java.text.SimpleDateFormat; import java.util.Collection; import java.util.Date; import java.util.List; import java.util.Map; +import org.apache.fineract.accounting.glaccount.domain.TrialBalance; +import org.apache.fineract.accounting.glaccount.domain.TrialBalanceRepositoryWrapper; +import org.apache.fineract.portfolio.loanaccount.api.LoanApiConstants; +import org.joda.time.LocalDate; +import org.joda.time.DateTime; + import org.apache.fineract.infrastructure.core.data.ApiParameterError; import org.apache.fineract.infrastructure.core.exception.PlatformApiDataValidationException; import org.apache.fineract.infrastructure.core.service.DateUtils; @@ -43,6 +50,7 @@ import org.apache.fineract.portfolio.savings.service.SavingsAccountWritePlatformService; import org.apache.fineract.portfolio.shareaccounts.service.ShareAccountDividendReadPlatformService; import org.apache.fineract.portfolio.shareaccounts.service.ShareAccountSchedularService; +import org.joda.time.Days; import org.joda.time.LocalDate; import org.joda.time.format.DateTimeFormat; import org.joda.time.format.DateTimeFormatter; @@ -52,6 +60,7 @@ import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.stereotype.Service; import org.springframework.transaction.annotation.Transactional; +import org.springframework.util.CollectionUtils; @Service(value = "scheduledJobRunnerService") public class ScheduledJobRunnerServiceImpl implements ScheduledJobRunnerService { @@ -67,6 +76,7 @@ private final DepositAccountWritePlatformService depositAccountWritePlatformService; private final ShareAccountDividendReadPlatformService shareAccountDividendReadPlatformService; private final ShareAccountSchedularService shareAccountSchedularService; + private final TrialBalanceRepositoryWrapper trialBalanceRepositoryWrapper; @Autowired public ScheduledJobRunnerServiceImpl(final RoutingDataSourceServiceFactory dataSourceServiceFactory, @@ -75,7 +85,7 @@ public ScheduledJobRunnerServiceImpl(final RoutingDataSourceServiceFactory dataS final DepositAccountReadPlatformService depositAccountReadPlatformService, final DepositAccountWritePlatformService depositAccountWritePlatformService, final ShareAccountDividendReadPlatformService shareAccountDividendReadPlatformService, - final ShareAccountSchedularService shareAccountSchedularService) { + final ShareAccountSchedularService shareAccountSchedularService, final TrialBalanceRepositoryWrapper trialBalanceRepositoryWrapper) { this.dataSourceServiceFactory = dataSourceServiceFactory; this.savingsAccountWritePlatformService = savingsAccountWritePlatformService; this.savingsAccountChargeReadPlatformService = savingsAccountChargeReadPlatformService; @@ -83,6 +93,7 @@ public ScheduledJobRunnerServiceImpl(final RoutingDataSourceServiceFactory dataS this.depositAccountWritePlatformService = depositAccountWritePlatformService; this.shareAccountDividendReadPlatformService = shareAccountDividendReadPlatformService; this.shareAccountSchedularService = shareAccountSchedularService; + this.trialBalanceRepositoryWrapper=trialBalanceRepositoryWrapper; } @Transactional @@ -409,4 +420,67 @@ public void postDividends() throws JobExecutionException { if (errorMsg.length() > 0) { throw new JobExecutionException(errorMsg.toString()); } } + @CronTarget(jobName = JobName.UPDATE_TRAIL_BALANCE_DETAILS) + public void updateTrialBalanceDetails() throws JobExecutionException { + final JdbcTemplate jdbcTemplate = new JdbcTemplate(this.dataSourceServiceFactory.determineDataSourceService().retrieveDataSource()); + final StringBuilder tbGapSqlBuilder = new StringBuilder(500); + tbGapSqlBuilder.append("select distinct(je.transaction_date) ") + .append("from acc_gl_journal_entry je ") + .append("where je.transaction_date > (select IFNULL(MAX(created_date),'2010-01-01') from m_trial_balance)"); + + final List<Date> tbGaps = jdbcTemplate.queryForList(tbGapSqlBuilder.toString(), Date.class); + + for(Date tbGap : tbGaps) { + LocalDate convDate = new DateTime(tbGap).toLocalDate(); + int days = Days.daysBetween(convDate, DateUtils.getLocalDateOfTenant()).getDays(); + if(days < 1) + continue; + final String formattedDate = new SimpleDateFormat("yyyy-MM-dd").format(tbGap); + final StringBuilder sqlBuilder = new StringBuilder(600); + sqlBuilder.append("Insert Into m_trial_balance(office_id, account_id, Amount, entry_date, created_date,closing_balance) ") + .append("Select je.office_id, je.account_id, sum(if(je.type_enum=1, (-1) * je.amount, je.amount)) ") + .append("as Amount, Date(je.entry_date) as 'Entry_Date', je.transaction_date as 'Created_Date',sum(je.amount) as closing_balance ") + .append("from acc_gl_journal_entry je WHERE je.transaction_date = ? ") + .append("group by je.account_id, je.office_id, je.transaction_date, Date(je.entry_date)"); + + final int result = jdbcTemplate.update(sqlBuilder.toString(), new Object[] { + formattedDate + }); + logger.info(ThreadLocalContextUtil.getTenant().getName() + ": Results affected by update: " + result); + } + + // Updating closing balance + String distinctOfficeQuery = "select distinct(office_id) from m_trial_balance where closing_balance is null group by office_id"; + final List<Long> officeIds = jdbcTemplate.queryForList(distinctOfficeQuery, new Object[] {}, Long.class); + + + for(Long officeId : officeIds) { + String distinctAccountQuery = "select distinct(account_id) from m_trial_balance where office_id=? and closing_balance is null group by account_id"; + final List<Long> accountIds = jdbcTemplate.queryForList(distinctAccountQuery, new Object[] {officeId}, Long.class); + for(Long accountId : accountIds) { + final String closingBalanceQuery = "select closing_balance from m_trial_balance where office_id=? and account_id=? and closing_balance " + + "is not null order by created_date desc, entry_date desc limit 1"; + List<BigDecimal> closingBalanceData = jdbcTemplate.queryForList(closingBalanceQuery, new Object[] {officeId, accountId}, BigDecimal.class); + List<TrialBalance> tbRows = this.trialBalanceRepositoryWrapper.findNewByOfficeAndAccount(officeId, accountId); + BigDecimal closingBalance = null; + if(!CollectionUtils.isEmpty(closingBalanceData)) + closingBalance = closingBalanceData.get(0); + if(CollectionUtils.isEmpty(closingBalanceData)) { + closingBalance = BigDecimal.ZERO; + for(TrialBalance row : tbRows) { + closingBalance = closingBalance.add(row.getAmount()); + row.setClosingBalance(closingBalance); + } + } else { + for(TrialBalance tbRow : tbRows) { + closingBalance = closingBalance.add(tbRow.getAmount()); + tbRow.setClosingBalance(closingBalance); + } + } + this.trialBalanceRepositoryWrapper.save(tbRows); + } + } + + } + } diff --git a/fineract-provider/src/main/resources/sql/migrations/core_db/V348__m_trial_balance_table.sql b/fineract-provider/src/main/resources/sql/migrations/core_db/V348__m_trial_balance_table.sql new file mode 100644 index 000000000..a5db8d59c --- /dev/null +++ b/fineract-provider/src/main/resources/sql/migrations/core_db/V348__m_trial_balance_table.sql @@ -0,0 +1,35 @@ +-- +-- 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. +-- +CREATE TABLE `m_trial_balance` ( + `office_id` BIGINT(20) NOT NULL, + `account_id` BIGINT(20) NOT NULL, + `amount` DECIMAL(19,6) NOT NULL, + `entry_date` DATE NOT NULL, + `created_date` DATE NULL, + `closing_balance` DECIMAL(19,6) NOT NULL +); + + +INSERT INTO `job` (`name`, `display_name`, `cron_expression`, `create_time`, `task_priority`, `group_name`, `previous_run_start_time`, `next_run_time`, `job_key`, `initializing_errorlog`, `is_active`, `currently_running`, `updates_allowed`, `scheduler_group`, `is_misfired`) VALUES ('Update Trial Balance Details', 'Update Trial Balance Details', '0 1 0 1/1 * ? *', now(), 5, NULL, NULL, NULL, NULL, NULL, 1, 0, 1, 0, 0); + +ALTER TABLE acc_gl_journal_entry ADD transaction_date date; + +ALTER TABLE acc_gl_journal_entry ADD INDEX transaction_date_index (transaction_date); + +UPDATE `acc_gl_journal_entry` SET transaction_date=DATE(created_date); \ No newline at end of file ---------------------------------------------------------------- This is an automated message from the Apache Git Service. To respond to the message, please log on GitHub and use the URL above to go to the specific comment. For queries about this service, please contact Infrastructure at: us...@infra.apache.org With regards, Apache Git Services
