Accounting scripts

loantransactions.sql

select transactions.id as 'Transaction ID', 
transactions.transactionType as 'Transaction Type',
transactions.reversed as reversed,
accounts.amount as amount,
accounts.transactionType as transactionType,
accounts.accountName as accountName,
accounts.accountType as accountType,
transactions.principal as principal,
transactions.interest as interest,
transactions.fee as fee,
transactions.penalty as penalty,
transactions.transaction_date as TransactionDate
from
(
select m_loan_transaction.transaction_date as transaction_date, 
id as id,
case 
  when m_loan_transaction.transaction_type_enum = 1 then 'DISBURSEMENT'
    when m_loan_transaction.transaction_type_enum = 2 then 'REPAYMENT'
    when m_loan_transaction.transaction_type_enum = 3 then 'CONTRA'
    when m_loan_transaction.transaction_type_enum = 4 then 'WAIVE INTEREST'
    when m_loan_transaction.transaction_type_enum = 5 then 'REPAYMENT AT DISBURSEMENT'
    when m_loan_transaction.transaction_type_enum = 6 then 'WRITEOFF'
    when m_loan_transaction.transaction_type_enum = 7 then 'MARKED FOR RESCHEDULING'
    when m_loan_transaction.transaction_type_enum = 8 then 'RECOVERY REPAYMENT'
    when m_loan_transaction.transaction_type_enum = 9 then 'WAIVE CHARGES'
    when m_loan_transaction.transaction_type_enum = 10 then 'APPLY CHARGES'
end as transactionType,
amount,
principal_portion_derived as principal,
interest_portion_derived as interest,
fee_charges_portion_derived as fee,
penalty_charges_portion_derived as penalty,
is_reversed as reversed,
loan_id as loanId
from m_loan_transaction
) transactions
left join
( 
select acc_gl_account.name as accountName, 
case 
  when acc_gl_account.classification_enum = 1 then 'ASSET'
	when acc_gl_account.classification_enum = 2 then 'LIABILITY'
	when acc_gl_account.classification_enum = 3  then 'EQUITY'
	when acc_gl_account.classification_enum = 4 then 'INCOME'
	when acc_gl_account.classification_enum = 5 then 'EXPENSE'
end as accountType,
case 
  when acc_product_mapping.financial_account_type = 1 then 'FUND SOURCE'
	when acc_product_mapping.financial_account_type = 2 then 'LOAN PORTFOLIO'
	when acc_product_mapping.financial_account_type = 3  then 'INTEREST ON LOANS'
	when acc_product_mapping.financial_account_type = 4 then 'INCOME FROM FEES'
	when acc_product_mapping.financial_account_type = 5 then 'INCOME FROM PENALTIES'
	when acc_product_mapping.financial_account_type = 6 then 'LOSSES WRITTEN OFF'
end as 'Mapping Placeholder',
IF(acc_gl_journal_entry.type_enum = 1,'CREDIT','DEBIT') as transactionType,
acc_gl_journal_entry.amount as amount,
acc_gl_journal_entry.transaction_id as transactionId,
acc_gl_journal_entry.entry_date as 'transactionDate',
acc_gl_journal_entry.entity_id as loanId
from acc_gl_account, acc_gl_journal_entry,acc_product_mapping,m_loan
where acc_gl_account.id=acc_gl_journal_entry.account_id
and acc_gl_journal_entry.entity_id=m_loan.id 
and m_loan.product_id=acc_product_mapping.product_id
and acc_product_mapping.gl_account_id = acc_gl_account.id
order by acc_gl_journal_entry.transaction_id,acc_gl_journal_entry.account_id
) accounts
on accounts.transactionId = transactions.id
order by transactions.id,accounts.accountName;