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