public String schema() {
StringBuilder sb = new StringBuilder();
sb.append(" journalEntry.id as id, glAccount.classification_enum as classification ,")
.append("journalEntry.transaction_id,")
.append(
" glAccount.name as glAccountName, glAccount.gl_code as glAccountCode,glAccount.id as glAccountId, ")
.append(
" journalEntry.office_id as officeId, office.name as officeName, journalEntry.ref_num as referenceNumber, ")
.append(
" journalEntry.manual_entry as manualEntry,journalEntry.entry_date as transactionDate, ")
.append(
" journalEntry.type_enum as entryType,journalEntry.amount as amount, journalEntry.transaction_id as transactionId,")
.append(
" journalEntry.entity_type_enum as entityType, journalEntry.entity_id as entityId, creatingUser.id as createdByUserId, ")
.append(
" creatingUser.username as createdByUserName, journalEntry.description as comments, ")
.append(" journalEntry.created_date as createdDate, journalEntry.reversed as reversed, ")
.append(
" journalEntry.currency_code as currencyCode, curr.name as currencyName, curr.internationalized_name_code as currencyNameCode, ")
.append(
" curr.display_symbol as currencyDisplaySymbol, curr.decimal_places as currencyDigits, curr.currency_multiplesof as inMultiplesOf ");
if (associationParametersData.isRunningBalanceRequired()) {
sb.append(" ,journalEntry.is_running_balance_calculated as runningBalanceComputed, ")
.append(" journalEntry.office_running_balance as officeRunningBalance, ")
.append(" journalEntry.organization_running_balance as organizationRunningBalance ");
}
if (associationParametersData.isTransactionDetailsRequired()) {
sb.append(" ,pd.receipt_number as receiptNumber, ")
.append(" pd.check_number as checkNumber, ")
.append(" pd.account_number as accountNumber, ")
.append(" pt.value as paymentTypeName, ")
.append(" pd.payment_type_id as paymentTypeId,")
.append(" pd.bank_number as bankNumber, ")
.append(" pd.routing_code as routingCode, ")
.append(" note.id as noteId, ")
.append(" note.note as transactionNote, ")
.append(" lt.transaction_type_enum as loanTransactionType, ")
.append(" st.transaction_type_enum as savingsTransactionType ");
}
sb.append(" from acc_gl_journal_entry as journalEntry ")
.append(
" left join acc_gl_account as glAccount on glAccount.id = journalEntry.account_id")
.append(" left join m_office as office on office.id = journalEntry.office_id")
.append(
" left join m_appuser as creatingUser on creatingUser.id = journalEntry.createdby_id ")
.append(" join m_currency curr on curr.code = journalEntry.currency_code ");
if (associationParametersData.isTransactionDetailsRequired()) {
sb.append(
" left join m_loan_transaction as lt on journalEntry.loan_transaction_id = lt.id ")
.append(
" left join m_savings_account_transaction as st on journalEntry.savings_transaction_id = st.id ")
.append(
" left join m_payment_detail as pd on lt.payment_detail_id = pd.id or st.payment_detail_id = pd.id or journalEntry.payment_details_id = pd.id")
.append(" left join m_payment_type as pt on pt.id = pd.payment_type_id ")
.append(
" left join m_note as note on lt.id = note.loan_transaction_id or st.id = note.savings_account_transaction_id ");
}
return sb.toString();
}
@Override
public JournalEntryData mapRow(final ResultSet rs, @SuppressWarnings("unused") final int rowNum)
throws SQLException {
final Long id = rs.getLong("id");
final Long officeId = rs.getLong("officeId");
final String officeName = rs.getString("officeName");
final String glCode = rs.getString("glAccountCode");
final String glAccountName = rs.getString("glAccountName");
final Long glAccountId = rs.getLong("glAccountId");
final int accountTypeId = JdbcSupport.getInteger(rs, "classification");
final EnumOptionData accountType = AccountingEnumerations.gLAccountType(accountTypeId);
final LocalDate transactionDate = JdbcSupport.getLocalDate(rs, "transactionDate");
final Boolean manualEntry = rs.getBoolean("manualEntry");
final BigDecimal amount = rs.getBigDecimal("amount");
final int entryTypeId = JdbcSupport.getInteger(rs, "entryType");
final EnumOptionData entryType = AccountingEnumerations.journalEntryType(entryTypeId);
final String transactionId = rs.getString("transactionId");
final Integer entityTypeId = JdbcSupport.getInteger(rs, "entityType");
EnumOptionData entityType = null;
if (entityTypeId != null) {
entityType = AccountingEnumerations.portfolioProductType(entityTypeId);
}
final Long entityId = JdbcSupport.getLong(rs, "entityId");
final Long createdByUserId = rs.getLong("createdByUserId");
final LocalDate createdDate = JdbcSupport.getLocalDate(rs, "createdDate");
final String createdByUserName = rs.getString("createdByUserName");
final String comments = rs.getString("comments");
final Boolean reversed = rs.getBoolean("reversed");
final String referenceNumber = rs.getString("referenceNumber");
BigDecimal officeRunningBalance = null;
BigDecimal organizationRunningBalance = null;
Boolean runningBalanceComputed = null;
final String currencyCode = rs.getString("currencyCode");
final String currencyName = rs.getString("currencyName");
final String currencyNameCode = rs.getString("currencyNameCode");
final String currencyDisplaySymbol = rs.getString("currencyDisplaySymbol");
final Integer currencyDigits = JdbcSupport.getInteger(rs, "currencyDigits");
final Integer inMultiplesOf = JdbcSupport.getInteger(rs, "inMultiplesOf");
final CurrencyData currency =
new CurrencyData(
currencyCode,
currencyName,
currencyDigits,
inMultiplesOf,
currencyDisplaySymbol,
currencyNameCode);
if (associationParametersData.isRunningBalanceRequired()) {
officeRunningBalance = rs.getBigDecimal("officeRunningBalance");
organizationRunningBalance = rs.getBigDecimal("organizationRunningBalance");
runningBalanceComputed = rs.getBoolean("runningBalanceComputed");
}
TransactionDetailData transactionDetailData = null;
if (associationParametersData.isTransactionDetailsRequired()) {
PaymentDetailData paymentDetailData = null;
final Long paymentTypeId = JdbcSupport.getLong(rs, "paymentTypeId");
if (paymentTypeId != null) {
final String typeName = rs.getString("paymentTypeName");
final PaymentTypeData paymentType = PaymentTypeData.instance(paymentTypeId, typeName);
final String accountNumber = rs.getString("accountNumber");
final String checkNumber = rs.getString("checkNumber");
final String routingCode = rs.getString("routingCode");
final String receiptNumber = rs.getString("receiptNumber");
final String bankNumber = rs.getString("bankNumber");
paymentDetailData =
new PaymentDetailData(
id,
paymentType,
accountNumber,
checkNumber,
routingCode,
receiptNumber,
bankNumber);
}
NoteData noteData = null;
final Long noteId = JdbcSupport.getLong(rs, "noteId");
if (noteId != null) {
final String note = rs.getString("transactionNote");
noteData =
new NoteData(
noteId, null, null, null, null, null, null, null, note, null, null, null, null,
null, null);
}
Long transaction = null;
if (entityType != null) {
transaction = Long.parseLong(transactionId.substring(1).trim());
}
TransactionTypeEnumData transactionTypeEnumData = null;
if (PortfolioAccountType.fromInt(entityTypeId).isLoanAccount()) {
final LoanTransactionEnumData loanTransactionType =
LoanEnumerations.transactionType(JdbcSupport.getInteger(rs, "loanTransactionType"));
transactionTypeEnumData =
new TransactionTypeEnumData(
loanTransactionType.id(),
loanTransactionType.getCode(),
loanTransactionType.getValue());
} else if (PortfolioAccountType.fromInt(entityTypeId).isSavingsAccount()) {
final SavingsAccountTransactionEnumData savingsTransactionType =
SavingsEnumerations.transactionType(
JdbcSupport.getInteger(rs, "savingsTransactionType"));
transactionTypeEnumData =
new TransactionTypeEnumData(
savingsTransactionType.getId(),
savingsTransactionType.getCode(),
savingsTransactionType.getValue());
}
transactionDetailData =
new TransactionDetailData(
transaction, paymentDetailData, noteData, transactionTypeEnumData);
}
return new JournalEntryData(
id,
officeId,
officeName,
glAccountName,
glAccountId,
glCode,
accountType,
transactionDate,
entryType,
amount,
transactionId,
manualEntry,
entityType,
entityId,
createdByUserId,
createdDate,
createdByUserName,
comments,
reversed,
referenceNumber,
officeRunningBalance,
organizationRunningBalance,
runningBalanceComputed,
transactionDetailData,
currency);
}
