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);
}
}
