ALTER TABLE  `_paid_repayment_data_aug_sept_2011` ADD INDEX (  `LOAN NUMBER` );
ALTER TABLE  `_paid_repayment_data_aug_sept_2011` ADD INDEX (  `RECEIPT NO.` );

ALTER TABLE  `_paid_repayment_data_aug_sept_2011` 
CHANGE  `LOAN NUMBER`  `LOAN NUMBER` VARCHAR( 32 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `REPAYMENT DUE DATE`  `REPAYMENT DUE DATE` VARCHAR( 32 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `PAYMENT MODE`  `PAYMENT MODE` VARCHAR( 64 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `REPAYMENT REMARKS`  `REPAYMENT REMARKS` text CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL;

delete prd.* from _paid_repayment_data_aug_sept_2011 prd LEFT JOIN _loan_data sld ON prd.`LOAN NUMBER` = sld.`LOAN NUMBER` WHERE sld.`LOAN NUMBER` IS NULL;

update _paid_repayment_data_aug_sept_2011 set `REPAYMENT DUE DATE` = REPLACE(`REPAYMENT DUE DATE`, '-', '/');
update _paid_repayment_data_aug_sept_2011 set `REPAYMENT DUE DATE` = REPLACE(`REPAYMENT DUE DATE`, '/11', '/2011');
update _paid_repayment_data_aug_sept_2011 set `REPAYMENT DUE DATE` = REPLACE(`REPAYMENT DUE DATE`, '/', ' ');

UPDATE _paid_repayment_data_aug_sept_2011 SET `REPAYMENT DUE DATE` = REPLACE(`REPAYMENT DUE DATE`, ' Jan ', '/01/');
UPDATE _paid_repayment_data_aug_sept_2011 SET `REPAYMENT DUE DATE` = REPLACE(`REPAYMENT DUE DATE`, ' Feb ', '/02/');
UPDATE _paid_repayment_data_aug_sept_2011 SET `REPAYMENT DUE DATE` = REPLACE(`REPAYMENT DUE DATE`, ' Mar ', '/03/');
UPDATE _paid_repayment_data_aug_sept_2011 SET `REPAYMENT DUE DATE` = REPLACE(`REPAYMENT DUE DATE`, ' Apr ', '/04/');
UPDATE _paid_repayment_data_aug_sept_2011 SET `REPAYMENT DUE DATE` = REPLACE(`REPAYMENT DUE DATE`, ' May ', '/05/');
UPDATE _paid_repayment_data_aug_sept_2011 SET `REPAYMENT DUE DATE` = REPLACE(`REPAYMENT DUE DATE`, ' Jun ', '/06/');
UPDATE _paid_repayment_data_aug_sept_2011 SET `REPAYMENT DUE DATE` = REPLACE(`REPAYMENT DUE DATE`, ' Jul ', '/07/');
UPDATE _paid_repayment_data_aug_sept_2011 SET `REPAYMENT DUE DATE` = REPLACE(`REPAYMENT DUE DATE`, ' Aug ', '/08/');
UPDATE _paid_repayment_data_aug_sept_2011 SET `REPAYMENT DUE DATE` = REPLACE(`REPAYMENT DUE DATE`, ' Sep ', '/09/');
UPDATE _paid_repayment_data_aug_sept_2011 SET `REPAYMENT DUE DATE` = REPLACE(`REPAYMENT DUE DATE`, ' Oct ', '/10/');
UPDATE _paid_repayment_data_aug_sept_2011 SET `REPAYMENT DUE DATE` = REPLACE(`REPAYMENT DUE DATE`, ' Nov ', '/11/');
UPDATE _paid_repayment_data_aug_sept_2011 SET `REPAYMENT DUE DATE` = REPLACE(`REPAYMENT DUE DATE`, ' Dec ', '/12/');


INSERT INTO repayments(application_id, disbursement_id, account_number, date, principal, interest, amount, remarks, book_id, permit_fee, receipt_number) SELECT ap.id, d.id, d.account_number, concat(right(trim(prd.`REPAYMENT DUE DATE`), 4), '-', substring(trim(prd.`REPAYMENT DUE DATE`), 4, 2), '-', left(trim(prd.`REPAYMENT DUE DATE`), 2)), prd.`PRINCIPAL`, prd.`INTEREST`, prd.`TOTAL AMOUNT PAID`, prd.`REPAYMENT REMARKS`, (SELECT id FROM books WHERE name = prd.`PAYMENT MODE`), prd.`OTHER PERMIT`, prd.`RECEIPT NO.` FROM _paid_repayment_data_aug_sept_2011 prd LEFT JOIN applications ap ON prd.`LOAN NUMBER` = ap.account_number LEFT JOIN disbursements d ON ap.id = d.application_id;

update repayments set late_fee = permit_fee where date between '2011-08-01' and '2011-09-30';
update repayments set amount = late_fee + principal + interest WHERE amount != late_fee + principal + interest and date between '2011-08-01' and '2011-09-30';

update repayments r set acceptance_fee = late_fee, late_fee = 0 WHERE date = (SELECT date FROM disbursements WHERE id = r.disbursement_id) AND late_fee > 0 and date between '2011-08-01' and '2011-09-30';
update disbursements d set acceptance_fee = (SELECT acceptance_fee FROM repayments WHERE disbursement_id = d.id AND date = d.date and acceptance_fee > 0) where (SELECT COUNT(id) FROM repayments r WHERE acceptance_fee > 0 AND disbursement_id = d.id and r.date between '2011-08-01' and '2011-09-30') = 1;




-- 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), 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 book_id = r.book_id), book_id FROM repayments r where date between '2011-08-01' and '2011-09-30';




-- 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 AND date between '2011-08-01' and '2011-09-30';

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 AND date between '2011-08-01' and '2011-09-30';

-- 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 AND date between '2011-08-01' and '2011-09-30';

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 AND date between '2011-08-01' and '2011-09-30';

-- 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 AND date between '2011-08-01' and '2011-09-30';

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 AND date between '2011-08-01' and '2011-09-30';


