insert ignore into books(name, type, Bank) SELECT name, IF(for_report = 'Cash In Bank', 'bank', 'cash'), '1' FROM projects_loan_rc_extract.payment_mode;
insert into ledgers(name, book_id, ledger_type_id) SELECT name, id, '4' FROM books WHERE (SELECT id FROM ledgers WHERE book_id = books.id) IS NULL;

ALTER TABLE  `borrowers` DROP INDEX  `uid`;
ALTER TABLE  projects_loan_rc_extract.`client` CHANGE  `nric`  `nric` VARCHAR( 256 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;

ALTER TABLE  projects_loan_rc_extract.`loan` CHANGE  `type`  `type` VARCHAR( 512 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
CHANGE  projects_loan_rc_extract.`purpose_of_loan`  `purpose_of_loan` VARCHAR( 512 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;

ALTER TABLE  `loan` CHANGE  `term`  `term` VARCHAR( 512 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
CHANGE  `term_type`  `term_type` VARCHAR( 512 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;

ALTER TABLE  `loan` CHANGE  `acct_no`  `acct_no` VARCHAR( 512 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
CHANGE  `payment_mode`  `payment_mode` VARCHAR( 512 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;

ALTER TABLE  `loan` CHANGE  `remarks`  `remarks` TEXT CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `company_id`  `company_id` VARCHAR( 256 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;

ALTER TABLE  `loan` CHANGE  `id`  `id` VARCHAR( 40 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;

ALTER TABLE  `payment_types` CHANGE  `name`  `name` VARCHAR( 64 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
CHANGE  `code`  `code` VARCHAR( 16 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;

ALTER TABLE  `loan` CHANGE  `id`  `id` VARCHAR( 40 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;

insert into borrowers(name, uid, dob, annual_income, company, designation) SELECT name, nric, dob, income_pa, coy_name, coy_job_title FROM projects_loan_rc_extract.client c ON duplicate key update name = c.name, dob = IFNULL(c.dob, ''), annual_income = c.income_pa, company = c.coy_name, designation = c.coy_job_title;
update borrowers b set work_phone = (SELECT contact_no FROM projects_loan_rc_extract.client2contact c2c LEFT JOIN projects_loan_rc_extract.client c ON c2c.client_id = c.id WHERE contact_type = 'Work' AND c.nric = b.uid LIMIT 1);
update borrowers b set home_phone = (SELECT contact_no FROM projects_loan_rc_extract.client2contact c2c LEFT JOIN projects_loan_rc_extract.client c ON c2c.client_id = c.id WHERE contact_type = 'Home' AND c.nric = b.uid LIMIT 1);
update borrowers b set hand_phone = (SELECT contact_no FROM projects_loan_rc_extract.client2contact c2c LEFT JOIN projects_loan_rc_extract.client c ON c2c.client_id = c.id WHERE contact_type = 'Mobile' AND c.nric = b.uid LIMIT 1);

update borrowers b set blk = (SELECT blk FROM projects_loan_rc_extract.client2address c2a LEFT JOIN projects_loan_rc_extract.client c ON c2a.client_id = c.id WHERE c.nric = b.uid LIMIT 1);
update borrowers b set unit = (SELECT unit FROM projects_loan_rc_extract.client2address c2a LEFT JOIN projects_loan_rc_extract.client c ON c2a.client_id = c.id WHERE c.nric = b.uid LIMIT 1);
update borrowers b set street = (SELECT street FROM projects_loan_rc_extract.client2address c2a LEFT JOIN projects_loan_rc_extract.client c ON c2a.client_id = c.id WHERE c.nric = b.uid LIMIT 1);
update borrowers b set pincode = (SELECT postalcode FROM projects_loan_rc_extract.client2address c2a LEFT JOIN projects_loan_rc_extract.client c ON c2a.client_id = c.id WHERE c.nric = b.uid LIMIT 1);

-- borrower ledgers
INSERT INTO ledgers(name, borrower_id, ledger_type_id) SELECT name, id, '1' FROM borrowers WHERE ledger_id = '0';
UPDATE borrowers SET ledger_id = (SELECT id FROM ledgers WHERE borrower_id = borrowers.id) WHERE ledger_id = '0';


insert into applications(date, account_number, borrower_id, amount, vt_user_id, loan_type_id, loan_purpose_id) 
SELECT date, acct_no, b.id, amount, '1', (SELECT id FROM projects_loan_rc.loan_types WHERE name = l.type), (SELECT id FROM projects_loan_rc.loan_purposes WHERE name = l.purpose_of_loan) FROM projects_loan_rc_extract.loan l LEFT JOIN projects_loan_rc_extract.loan2client l2c ON l.id = l2c.loan_id AND client_type = 'Borrower' LEFT JOIN projects_loan_rc_extract.client c ON l2c.client_id = c.id LEFT JOIN projects_loan_rc.borrowers b ON c.nric = b.uid WHERE l.is_delete = '0';


update projects_loan_rc_extract.loan SET term_type = 'monthly' WHERE term_type = 'Monthly';
update projects_loan_rc_extract.loan SET term_type = 'bi-weekly' WHERE term_type = 'B/Weekly';
update projects_loan_rc_extract.loan SET term_type = 'weekly' WHERE term_type = 'Weekly';
update projects_loan_rc_extract.loan SET term_type = 'monthly' WHERE term_type = 'Pay Day';
update projects_loan_rc_extract.loan SET term_type = 'Open monthly' WHERE term_type = 'Open Monthly';
update projects_loan_rc_extract.loan SET term_type = 'Open daily' WHERE term_type = 'Open Daily';


insert into disbursements(date, account_number, amount, application_id, interest_rate, first_payment_date, acceptance_fee, book_id, payment_type_id, installment_count, payment_frequency_id, interest_frequency_id, interest_calculation_method_id, late_interest_rate, Remarks, status, book_amount, acceptance_fee_percent, late_repayment_fee) 
SELECT date, acct_no, amount, (SELECT id FROM projects_loan_rc.applications WHERE account_number = acct_no), interest_pa, firstrepaymentdate, acceptance, (SELECT id FROM projects_loan_rc.books WHERE name = payment_mode), (SELECT id FROM projects_loan_rc.payment_types WHERE name = payment_mode), term, (SELECT id FROM projects_loan_rc.time_frequencies tf WHERE name = replace(l.term_type, 'Open ', '')), '5', IF(locate('Open', l.term_type) != 0, '3', '1'), late_interest, remarks, '0', amount, IF(acceptance_type = 'Percent', acceptance_value, ''), late_payment 
FROM projects_loan_rc_extract.loan l WHERE l.is_delete = '0'


update projects_loan_rc_extract.loan_repayment set mode_of_payment = 'DBS' Where mode_of_payment = 'DBS Bank';

ALTER TABLE  `loan_repayment` CHANGE  `loan_id`  `loan_id` VARCHAR( 256 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
CHANGE  `mode_of_payment`  `mode_of_payment` VARCHAR( 256 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL;
ALTER TABLE  `loan_repayment` CHANGE  `loan_id`  `loan_id` VARCHAR( 256 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;

-- SELECT * FROM loan_repayment r LEFT JOIN loan l ON r.loan_id = l.id WHERE l.acct_no IS NULL;

insert into repayments(date, disbursement_id, application_id, amount, account_number, remarks, principal, interest, receipt_number, permit_fee, book_id) 
SELECT due_date, (SELECT id FROM projects_loan_rc.disbursements WHERE account_number = (SELECT acct_no FROM projects_loan_rc_extract.loan WHERE id = r.loan_id)) as disbursement_id, (SELECT id FROM projects_loan_rc.applications WHERE account_number = (SELECT acct_no FROM projects_loan_rc_extract.loan WHERE id = r.loan_id)) as application_id, total_amt_paid, (SELECT acct_no FROM projects_loan_rc_extract.loan WHERE id = r.loan_id) as account_number, payment_remarks, principal_paid, interest_paid, receipt_no, other_permitted_fees, (SELECT id FROM projects_loan_rc.books WHERE name = mode_of_payment)
FROM projects_loan_rc_extract.loan_repayment r LEFT JOIN projects_loan_rc_extract.loan ON projects_loan_rc_extract.loan.id =r.loan_id WHERE projects_loan_rc_extract.loan.is_delete != '1' AND r.is_deleted != '1';

update repayments set late_fee = permit_fee;


insert into payment_schedule(date, principal, interest, amount, application_id, disbursement_id) 
SELECT due_date, capital, interest, capital+interest, d.application_id, d.id FROM projects_loan_rc_extract.loan_repayment_plan lrp LEFT JOIN projects_loan_rc_extract.loan l ON l.id = lrp.loan_id LEFT JOIN disbursements d ON d.account_number = l.acct_no WHERE l.is_delete != '1'



-- migration from supplementary excel file
-- migration from supplementary excel file
-- migration from supplementary excel file

ALTER TABLE  `_repayments` CHANGE  `Loan No.`  `Loan No.` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `Payment Mode`  `Payment Mode` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL;

INSERT INTO repayments(date, disbursement_id, application_id, amount, account_number, remarks, book_id) 
SELECT r.Date, d.id, d.application_id, r.`In`, d.account_number, r.Remarks, (SELECT id FROM books WHERE name = `Payment Mode`) FROM projects_loan_rc_excel._repayments r LEFT JOIN disbursements d ON d.account_number = r.`Loan No.` WHERE d.id IS NOT NULL

-- migration from supplementary excel file
-- migration from supplementary excel file
-- migration from supplementary excel file




-- creating ledgers for non loan txns
INSERT INTO ledgers(name, ledger_type_id) SELECT `LEDGER CODE`, '3' FROM  projects_loan_rc_excel._expenses GROUP BY `LEDGER CODE`;
INSERT INTO ledgers(name, ledger_type_id) SELECT `LEDGER CODE`, '3' FROM  projects_loan_rc_excel._deposits GROUP BY `LEDGER CODE`;

ALTER TABLE  `_expenses` 
CHANGE  `Date`  `Date` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `Loan No.`  `Loan No.` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `Voucher No.`  `Voucher No.` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `Payer/Payee`  `Payer/Payee` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `Remarks`  `Remarks` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `Payment Mode`  `Payment Mode` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `Ledger Code`  `Ledger Code` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `Transaction`  `Transaction` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL;

-- non loan transactions
-- payments
INSERT INTO vouchers(date, transaction_type, pay_type, ledger_id, book_id, book_ledger_id, amount, narration, entry_mode) 
SELECT 
	e.Date, 
	'P', 
	'O', 
	(SELECT id FROM ledgers WHERE name = TRIM(e.`LEDGER CODE`) ORDER BY id ASC LIMIT 1), 
	(SELECT id FROM books WHERE name = TRIM(e.`PAYMENT MODE`)), 
	(SELECT id FROM ledgers WHERE name = TRIM(e.`PAYMENT MODE`) AND book_id != '0'), 
	e.`Out`, 
	e.`Remarks`, 
	'P'
FROM projects_loan_rc_excel._expenses e;


ALTER TABLE  `_deposits` CHANGE  `Date`  `Date` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `Loan No.`  `Loan No.` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `Payer/Payee`  `Payer/Payee` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `Cheque / Receipt No.`  `Cheque / Receipt No.` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `Remarks`  `Remarks` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `Payment Mode`  `Payment Mode` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `Ledger Code`  `Ledger Code` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `Transaction`  `Transaction` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL;

-- receipts
INSERT INTO vouchers(date, transaction_type, pay_type, ledger_id, book_id, book_ledger_id, amount, narration, entry_mode) 
SELECT 
	d.Date, 
	'R', 
	'O', 
	(SELECT id FROM ledgers WHERE name = d.`LEDGER CODE` ORDER BY id ASC LIMIT 1), 
	(SELECT id FROM books WHERE name = d.`PAYMENT MODE`), 
	(SELECT id FROM ledgers WHERE name = d.`PAYMENT MODE` AND book_id != '0'), 
	d.`In`, 
	d.`Remarks`, 
	'R'
FROM projects_loan_rc_excel._deposits d;


ALTER TABLE  `_book_transfers` CHANGE  `Date`  `Date` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `Loan No.`  `Loan No.` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `Payer/Payee`  `Payer/Payee` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `Remarks`  `Remarks` VARCHAR( 1024 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `Payment Mode`  `Payment Mode` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `Ledger Code`  `Ledger Code` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `Transaction`  `Transaction` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL;

-- book transfers

INSERT INTO vouchers(
	date, transaction_type, pay_type, entry_mode, book_ledger_id, ledger_id, book_id, amount, transaction_number, narration
) 
SELECT 
	bt.Date, 
	'P', 
	'O', 
	'T', 
	(SELECT id FROM ledgers WHERE name = bt.`PAYMENT MODE` LIMIT 1) as `from`, 
	(SELECT id FROM ledgers WHERE name = bt1.`PAYMENT MODE` LIMIT 1) as `to`, 
	(SELECT id FROM books WHERE name = bt.`PAYMENT MODE` LIMIT 1) as `book_id`, 
	bt.`In`, 
	bt.`Cheque / Receipt No.`, 
	bt.`Remarks`
FROM projects_loan_rc_excel._book_transfers bt 
LEFT JOIN projects_loan_rc_excel._book_transfers bt1 ON 
	bt.`Date` = bt1.`Date` AND 
	bt.`PAYMENT MODE` != bt1.`PAYMENT MODE` AND 
	bt.`In` = bt1.`Out` 
WHERE bt.`In` > 0 
GROUP BY bt.`No.`;


-- to be done on the server directly 
-- to be done on the server directly 
-- to be done on the server directly 
-- setting parent voucher id
UPDATE vouchers SET parent_voucher_id = id WHERE transaction_type = 'P' AND pay_type = 'O' AND entry_mode = 'T';

-- reverse book transfer entries
INSERT INTO vouchers(date, voucher_number, transaction_type, pay_type, amount, narration, entry_mode, ledger_id, book_ledger_id, parent_voucher_id, book_id) 
SELECT date, voucher_number, 'R', pay_type, amount, narration, entry_mode, book_ledger_id, ledger_id, parent_voucher_id, (SELECT book_id FROM ledgers WHERE id = vouchers.ledger_id) 
FROM vouchers WHERE transaction_type = 'P' AND pay_type = 'O' AND entry_mode = 'T';
-- to be done on the server directly 
-- to be done on the server directly 
-- to be done on the server directly 



-- mapping borrowers annual income to disbursements
update disbursements d LEFT JOIN applications ap ON d.application_id = ap.id set d.borrower_annual_income = (select annual_income FROM borrowers where id = ap.borrower_id) WHERE d.borrower_annual_income = '0';


