private void updateRunningBalance(Long officeId, Date entityDate) { Map<Long, BigDecimal> runningBalanceMap = new HashMap<>(5); List<Map<String, Object>> list = jdbcTemplate.queryForList( officeRunningBalanceSql, officeId, entityDate, officeId, entityDate); for (Map<String, Object> entries : list) { Long accountId = (Long) entries.get("accountId"); if (!runningBalanceMap.containsKey(accountId)) { runningBalanceMap.put(accountId, (BigDecimal) entries.get("runningBalance")); } } List<JournalEntryData> entryDatas = jdbcTemplate.query( entryMapper.officeRunningBalanceSchema(), entryMapper, new Object[] {officeId, entityDate}); String[] updateSql = new String[entryDatas.size()]; int i = 0; for (JournalEntryData entryData : entryDatas) { BigDecimal runningBalance = calculateRunningBalance(entryData, runningBalanceMap); String sql = "UPDATE acc_gl_journal_entry je SET je.office_running_balance=" + runningBalance + " WHERE je.id=" + entryData.getId(); updateSql[i++] = sql; } this.jdbcTemplate.batchUpdate(updateSql); }
@Override public JournalEntryData retrieveGLJournalEntryById( final long glJournalEntryId, JournalEntryAssociationParametersData associationParametersData) { try { final GLJournalEntryMapper rm = new GLJournalEntryMapper(associationParametersData); final String sql = "select " + rm.schema() + " where journalEntry.id = ?"; final JournalEntryData glJournalEntryData = this.jdbcTemplate.queryForObject(sql, rm, new Object[] {glJournalEntryId}); return glJournalEntryData; } catch (final EmptyResultDataAccessException e) { throw new JournalEntriesNotFoundException(glJournalEntryId); } }
@Override public Page<JournalEntryData> retrieveJournalEntriesByEntityId( String transactionId, Long entityId, Integer entityType) { JournalEntryAssociationParametersData associationParametersData = new JournalEntryAssociationParametersData(true, true); try { final GLJournalEntryMapper rm = new GLJournalEntryMapper(associationParametersData); final String sql = "select " + rm.schema() + " where journalEntry.transaction_id = ? and journalEntry.entity_id = ? and journalEntry.entity_type_enum = ?"; final String sqlCountRows = "SELECT FOUND_ROWS()"; Object[] data = {transactionId, entityId, entityType}; return this.paginationHelper.fetchPage(this.jdbcTemplate, sqlCountRows, sql, data, rm); } catch (final EmptyResultDataAccessException e) { throw new JournalEntriesNotFoundException(entityId); } }
@Override public Page<JournalEntryData> retrieveAll( final SearchParameters searchParameters, final Long glAccountId, final Boolean onlyManualEntries, final Date fromDate, final Date toDate, final String transactionId, final Integer entityType, final JournalEntryAssociationParametersData associationParametersData) { GLJournalEntryMapper rm = new GLJournalEntryMapper(associationParametersData); final StringBuilder sqlBuilder = new StringBuilder(200); sqlBuilder.append("select SQL_CALC_FOUND_ROWS "); sqlBuilder.append(rm.schema()); final Object[] objectArray = new Object[15]; int arrayPos = 0; String whereClose = " where "; if (StringUtils.isNotBlank(transactionId)) { sqlBuilder.append(whereClose + " journalEntry.transaction_id = ?"); objectArray[arrayPos] = transactionId; arrayPos = arrayPos + 1; whereClose = " and "; } if (entityType != null && entityType != 0 && (onlyManualEntries == null)) { sqlBuilder.append(whereClose + " journalEntry.entity_type_enum = ?"); objectArray[arrayPos] = entityType; arrayPos = arrayPos + 1; whereClose = " and "; } if (searchParameters.isOfficeIdPassed()) { sqlBuilder.append(whereClose + " journalEntry.office_id = ?"); objectArray[arrayPos] = searchParameters.getOfficeId(); arrayPos = arrayPos + 1; whereClose = " and "; } if (searchParameters.isCurrencyCodePassed()) { sqlBuilder.append(whereClose + " journalEntry.currency_code = ?"); objectArray[arrayPos] = searchParameters.getCurrencyCode(); arrayPos = arrayPos + 1; whereClose = " and "; } if (glAccountId != null && glAccountId != 0) { sqlBuilder.append(whereClose + " journalEntry.account_id = ?"); objectArray[arrayPos] = glAccountId; arrayPos = arrayPos + 1; whereClose = " and "; } if (fromDate != null || toDate != null) { final DateFormat df = new SimpleDateFormat("yyyy-MM-dd"); String fromDateString = null; String toDateString = null; if (fromDate != null && toDate != null) { sqlBuilder.append(whereClose + " journalEntry.entry_date between ? and ? "); whereClose = " and "; fromDateString = df.format(fromDate); toDateString = df.format(toDate); objectArray[arrayPos] = fromDateString; arrayPos = arrayPos + 1; objectArray[arrayPos] = toDateString; arrayPos = arrayPos + 1; } else if (fromDate != null) { sqlBuilder.append(whereClose + " journalEntry.entry_date >= ? "); fromDateString = df.format(fromDate); objectArray[arrayPos] = fromDateString; arrayPos = arrayPos + 1; whereClose = " and "; } else if (toDate != null) { sqlBuilder.append(whereClose + " journalEntry.entry_date <= ? "); toDateString = df.format(toDate); objectArray[arrayPos] = toDateString; arrayPos = arrayPos + 1; whereClose = " and "; } } if (onlyManualEntries != null) { if (onlyManualEntries) { sqlBuilder.append(whereClose + " journalEntry.manual_entry = 1"); whereClose = " and "; } } if (searchParameters.isLoanIdPassed()) { sqlBuilder.append( whereClose + " journalEntry.loan_transaction_id in (select id from m_loan_transaction where loan_id = ?)"); objectArray[arrayPos] = searchParameters.getLoanId(); arrayPos = arrayPos + 1; whereClose = " and "; } if (searchParameters.isSavingsIdPassed()) { sqlBuilder.append( whereClose + " journalEntry.savings_transaction_id in (select id from m_savings_account_transaction where savings_account_id = ?)"); objectArray[arrayPos] = searchParameters.getSavingsId(); arrayPos = arrayPos + 1; whereClose = " and "; } if (searchParameters.isOrderByRequested()) { sqlBuilder.append(" order by ").append(searchParameters.getOrderBy()); if (searchParameters.isSortOrderProvided()) { sqlBuilder.append(' ').append(searchParameters.getSortOrder()); } } else { sqlBuilder.append(" order by journalEntry.entry_date, journalEntry.id"); } if (searchParameters.isLimited()) { sqlBuilder.append(" limit ").append(searchParameters.getLimit()); if (searchParameters.isOffset()) { sqlBuilder.append(" offset ").append(searchParameters.getOffset()); } } final Object[] finalObjectArray = Arrays.copyOf(objectArray, arrayPos); final String sqlCountRows = "SELECT FOUND_ROWS()"; return this.paginationHelper.fetchPage( this.jdbcTemplate, sqlCountRows, sqlBuilder.toString(), finalObjectArray, rm); }
private void updateOrganizationRunningBalance(Date entityDate) { Map<Long, BigDecimal> runningBalanceMap = new HashMap<>(5); Map<Long, Map<Long, BigDecimal>> officesRunningBalance = new HashMap<>(); List<Map<String, Object>> list = jdbcTemplate.queryForList(organizationRunningBalanceSql, entityDate, entityDate); for (Map<String, Object> entries : list) { Long accountId = (Long) entries.get("accountId"); if (!runningBalanceMap.containsKey(accountId)) { runningBalanceMap.put(accountId, (BigDecimal) entries.get("runningBalance")); } } List<Map<String, Object>> officesRunningBalanceList = jdbcTemplate.queryForList(officesRunningBalanceSql, entityDate, entityDate); for (Map<String, Object> entries : officesRunningBalanceList) { Long accountId = (Long) entries.get("accountId"); Long officeId = (Long) entries.get("officeId"); Map<Long, BigDecimal> runningBalance = null; if (officesRunningBalance.containsKey(officeId)) { runningBalance = officesRunningBalance.get(officeId); } else { runningBalance = new HashMap<>(); officesRunningBalance.put(officeId, runningBalance); } if (!runningBalance.containsKey(accountId)) { runningBalance.put(accountId, (BigDecimal) entries.get("runningBalance")); } } List<JournalEntryData> entryDatas = jdbcTemplate.query( entryMapper.organizationRunningBalanceSchema(), entryMapper, new Object[] {entityDate}); if (entryDatas.size() > 0) { // run a batch update of 1000 SQL statements at a time final Integer batchUpdateSize = 1000; final Integer batchUpdateSizeMinusOne = batchUpdateSize - 1; String[] updateSql = new String[batchUpdateSize]; int i = 0; for (JournalEntryData entryData : entryDatas) { Map<Long, BigDecimal> officeRunningBalanceMap = null; if (officesRunningBalance.containsKey(entryData.getOfficeId())) { officeRunningBalanceMap = officesRunningBalance.get(entryData.getOfficeId()); } else { officeRunningBalanceMap = new HashMap<>(); officesRunningBalance.put(entryData.getOfficeId(), officeRunningBalanceMap); } BigDecimal officeRunningBalance = calculateRunningBalance(entryData, officeRunningBalanceMap); BigDecimal runningBalance = calculateRunningBalance(entryData, runningBalanceMap); String sql = "UPDATE acc_gl_journal_entry je SET je.is_running_balance_calculated=1, je.organization_running_balance=" + runningBalance + ",je.office_running_balance=" + officeRunningBalance + " WHERE je.id=" + entryData.getId(); updateSql[i++] = sql; if (i == batchUpdateSizeMinusOne) { // run a batch update of the 1000 update SQL statements this.jdbcTemplate.batchUpdate(updateSql); // reset counter and string array i = 0; updateSql = new String[batchUpdateSize]; } } this.jdbcTemplate.batchUpdate(updateSql); } }