-- Disbursement vouchers
INSERT INTO vouchers(date, voucher_number, transaction_type, transaction_number, pay_type, reference_number, amount, borrower_id, surety_id, account_number, ledger_id, book_ledger_id, book_id) SELECT date, '', 'P', '', 'D', id, amount, (SELECT b.id FROM applications ap LEFT JOIN borrowers b ON ap.borrower_id = b.id WHERE ap.id = d.application_id), '', account_number, (SELECT ledger_id FROM borrowers b LEFT JOIN applications ap ON b.id = ap.borrower_id WHERE ap.id = d.application_id), (SELECT id FROM ledgers WHERE book_id = d.book_id), book_id FROM disbursements d;

-- disbursement voucher creation for only the ones which are missing vouchers
-- INSERT INTO vouchers(date, voucher_number, transaction_type, transaction_number, pay_type, reference_number, amount, borrower_id, surety_id, account_number, ledger_id, book_ledger_id, book_id) SELECT d.date, '', 'P', '', 'D', d.id, d.amount, (SELECT b.id FROM applications ap LEFT JOIN borrowers b ON ap.borrower_id = b.id WHERE ap.id = d.application_id), '', d.account_number, (SELECT ledger_id FROM borrowers b LEFT JOIN applications ap ON b.id = ap.borrower_id WHERE ap.id = d.application_id), (SELECT id FROM ledgers WHERE book_id = d.book_id), d.book_id FROM disbursements d LEFT JOIN vouchers v ON d.id = v.reference_number and v.pay_type = 'D' WHERE v.id IS NULL;

-- Repayment vouchers
INSERT INTO vouchers(date, voucher_number, transaction_type, pay_type, reference_number, amount, borrower_id, account_number, payment_type_id, ledger_id, book_ledger_id, book_id) 
SELECT date, '', 'R', 'R', id, amount, (SELECT b.id FROM applications ap LEFT JOIN borrowers b ON ap.borrower_id = b.id WHERE ap.id = r.application_id limit 1), account_number, payment_type_id, (SELECT b.ledger_id FROM applications ap LEFT JOIN borrowers b ON ap.borrower_id = b.id WHERE ap.id = r.application_id limit 1), (SELECT id FROM ledgers WHERE book_id = r.book_id limit 1), book_id FROM repayments r;


-- Repayment voucher creation for only the ones which are missing vouchers
-- INSERT INTO vouchers(date, voucher_number, transaction_type, pay_type, reference_number, amount, borrower_id, account_number, payment_type_id, ledger_id, book_ledger_id, book_id) SELECT r.date, '', 'R', 'R', r.id, r.amount, (SELECT b.id FROM applications ap LEFT JOIN borrowers b ON ap.borrower_id = b.id WHERE ap.id = r.application_id), r.account_number, r.payment_type_id, (SELECT b.ledger_id FROM applications ap LEFT JOIN borrowers b ON ap.borrower_id = b.id WHERE ap.id = r.application_id), (SELECT id FROM ledgers WHERE book_id = r.book_id), r.book_id FROM repayments r left join vouchers v ON r.id = v.reference_number and v.pay_type = 'R' where v.id is null;


-- Permit fee vouchers
-- INSERT INTO vouchers(date, transaction_type, pay_type, reference_number, amount, borrower_id, account_number, payment_type_id, ledger_id, book_ledger_id, book_id, entry_mode) SELECT date, 'R', 'R', id, late_fee+acceptance_fee, (SELECT b.id FROM applications ap LEFT JOIN borrowers b ON ap.borrower_id = b.id WHERE ap.id = r.application_id), account_number, payment_type_id, (SELECT id FROM ledgers WHERE name = 'PermitFees'), (SELECT b.ledger_id FROM applications ap LEFT JOIN borrowers b ON ap.borrower_id = b.id WHERE ap.id = r.application_id), '0', 'M' FROM repayments r WHERE late_fee+acceptance_fee != 0;

-- INSERT INTO vouchers(date, transaction_type, pay_type, reference_number, amount, borrower_id, account_number, payment_type_id, ledger_id, book_ledger_id, book_id, entry_mode) SELECT date, 'P', 'R', id, late_fee+acceptance_fee, (SELECT b.id FROM applications ap LEFT JOIN borrowers b ON ap.borrower_id = b.id WHERE ap.id = r.application_id), account_number, payment_type_id, (SELECT b.ledger_id FROM applications ap LEFT JOIN borrowers b ON ap.borrower_id = b.id WHERE ap.id = r.application_id), (SELECT id FROM ledgers WHERE name = 'PermitFees'), '0', 'M' FROM repayments r WHERE late_fee+acceptance_fee != 0;

-- Permit fee vouchers
INSERT INTO vouchers(date, transaction_type, pay_type, reference_number, amount, borrower_id, account_number, payment_type_id, ledger_id, book_ledger_id, book_id, entry_mode) 
SELECT date, 'R', 'R', id, legal_fee + acceptance_fee + contract_variation_fee + cheque_dishonoured_fee + termination_fee + renewal_fee + late_fee, (SELECT b.id FROM applications ap LEFT JOIN borrowers b ON ap.borrower_id = b.id WHERE ap.id = r.application_id), account_number, payment_type_id, (SELECT id FROM ledgers WHERE name = 'PermitFees'), (SELECT b.ledger_id FROM applications ap LEFT JOIN borrowers b ON ap.borrower_id = b.id WHERE ap.id = r.application_id), '0', 'M' FROM repayments r WHERE legal_fee + acceptance_fee + contract_variation_fee + cheque_dishonoured_fee + termination_fee + renewal_fee + late_fee != 0;

INSERT INTO vouchers(date, transaction_type, pay_type, reference_number, amount, borrower_id, account_number, payment_type_id, ledger_id, book_ledger_id, book_id, entry_mode) 
SELECT date, 'P', 'R', id, legal_fee + acceptance_fee + contract_variation_fee + cheque_dishonoured_fee + termination_fee + renewal_fee + late_fee, (SELECT b.id FROM applications ap LEFT JOIN borrowers b ON ap.borrower_id = b.id WHERE ap.id = r.application_id), account_number, payment_type_id, (SELECT b.ledger_id FROM applications ap LEFT JOIN borrowers b ON ap.borrower_id = b.id WHERE ap.id = r.application_id), (SELECT id FROM ledgers WHERE name = 'PermitFees'), '0', 'M' FROM repayments r WHERE legal_fee + acceptance_fee + contract_variation_fee + cheque_dishonoured_fee + termination_fee + renewal_fee + late_fee != 0;

-- Interest vouchers
INSERT INTO vouchers(date, transaction_type, pay_type, reference_number, amount, borrower_id, account_number, payment_type_id, ledger_id, book_ledger_id, book_id, entry_mode) 
SELECT date, 'R', 'R', id, interest, (SELECT b.id FROM applications ap LEFT JOIN borrowers b ON ap.borrower_id = b.id WHERE ap.id = r.application_id), account_number, payment_type_id, (SELECT id FROM ledgers WHERE name = 'Interest'), (SELECT b.ledger_id FROM applications ap LEFT JOIN borrowers b ON ap.borrower_id = b.id WHERE ap.id = r.application_id), '0', 'M' FROM repayments r WHERE interest != 0;

INSERT INTO vouchers(date, transaction_type, pay_type, reference_number, amount, borrower_id, account_number, payment_type_id, ledger_id, book_ledger_id, book_id, entry_mode) 
SELECT date, 'P', 'R', id, interest, (SELECT b.id FROM applications ap LEFT JOIN borrowers b ON ap.borrower_id = b.id WHERE ap.id = r.application_id), account_number, payment_type_id, (SELECT b.ledger_id FROM applications ap LEFT JOIN borrowers b ON ap.borrower_id = b.id WHERE ap.id = r.application_id), (SELECT id FROM ledgers WHERE name = 'Interest'), '0', 'M' FROM repayments r WHERE interest != 0;

-- Late Interest vouchers - newly added
INSERT INTO vouchers(date, transaction_type, pay_type, reference_number, amount, borrower_id, account_number, payment_type_id, ledger_id, book_ledger_id, book_id, entry_mode) 
SELECT date, 'R', 'R', id, late_interest, (SELECT b.id FROM applications ap LEFT JOIN borrowers b ON ap.borrower_id = b.id WHERE ap.id = r.application_id), account_number, payment_type_id, (SELECT id FROM ledgers WHERE name = 'LateInterest'), (SELECT b.ledger_id FROM applications ap LEFT JOIN borrowers b ON ap.borrower_id = b.id WHERE ap.id = r.application_id), '0', 'M' FROM repayments r WHERE late_interest != 0;

INSERT INTO vouchers(date, transaction_type, pay_type, reference_number, amount, borrower_id, account_number, payment_type_id, ledger_id, book_ledger_id, book_id, entry_mode) 
SELECT date, 'P', 'R', id, late_interest, (SELECT b.id FROM applications ap LEFT JOIN borrowers b ON ap.borrower_id = b.id WHERE ap.id = r.application_id), account_number, payment_type_id, (SELECT b.ledger_id FROM applications ap LEFT JOIN borrowers b ON ap.borrower_id = b.id WHERE ap.id = r.application_id), (SELECT id FROM ledgers WHERE name = 'LateInterest'), '0', 'M' FROM repayments r WHERE late_interest != 0;


-- delete from vouchers where entry_mode = 'M';
-- delete from vouchers where pay_type = 'R' and transaction_type = 'R';

-- update vouchers set ledger_id = (SELECT ledger_id from borrowers where id = vouchers.borrower_id) where ledger_id = borrower_id and book_ledger_id = '130';
-- update vouchers set book_ledger_id = (SELECT ledger_id from borrowers where id = vouchers.borrower_id) where book_ledger_id = borrower_id and ledger_id = '130';
-- update vouchers set ledger_id = (SELECT ledger_id from borrowers where id = vouchers.borrower_id) where ledger_id = borrower_id and book_ledger_id = '128';
-- update vouchers set book_ledger_id = (SELECT ledger_id from borrowers where id = vouchers.borrower_id) where book_ledger_id = borrower_id and ledger_id = '128';


