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