truncate table vouchers;
truncate table repayments;
truncate table disbursements;
truncate table applications;
truncate table borrowers;
truncate table blacklist_reasons;
delete from ledgers where borrower_id != '0';
delete from ledgers where book_id != '0' and name NOT IN('Cash Book', 'Bank Book', 'Bad Debt Book');

ALTER TABLE  `_client_data` ADD INDEX (  `NRIC` );
ALTER TABLE  `_loan_data` ADD INDEX (  `LOAN NUMBER` );
ALTER TABLE  `_paid_repayment_data` ADD INDEX (  `LOAN NUMBER` );

ALTER TABLE  `_bad_debts_data` 
CHANGE  `LOAN NUMBER`  `LOAN NUMBER` VARCHAR( 9 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `BAD DEBT REMARKS`  `BAD DEBT REMARKS` text CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `BAD DEBT DATE`  `BAD DEBT DATE` VARCHAR( 11 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL;

ALTER TABLE `_loan_data` 
CHANGE `A` `A` VARCHAR(128) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL, 
CHANGE `LOAN NUMBER` `LOAN NUMBER` VARCHAR(32) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL, 
CHANGE `BORROWER 1` `BORROWER 1` VARCHAR(32) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL, 
CHANGE `BORROWER 2` `BORROWER 2` VARCHAR(32) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL, 
CHANGE `SURETY 1` `SURETY 1` VARCHAR(32) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL, 
CHANGE `SURETY 2` `SURETY 2` 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 `VOUCHER NO.` `VOUCHER NO.` VARCHAR(16) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL, 
CHANGE `TERM` `TERM` VARCHAR(16) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL, 
CHANGE `PACKAGE` `PACKAGE` VARCHAR(16) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL, 
CHANGE `PURPOSE OF LOAN` `PURPOSE OF LOAN` VARCHAR(32) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL, 
CHANGE `LOAN REMARK` `LOAN REMARK` text CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL, 
CHANGE `EMPLOYEE ID` `EMPLOYEE ID` VARCHAR(32) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL;

ALTER TABLE  `_loan_data` CHANGE  `LOAN DATE`  `LOAN DATE` VARCHAR( 32 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL;

ALTER TABLE  `_paid_repayment_data` 
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;

ALTER TABLE  `_unpaid_repayment_data` 
CHANGE  `LOAN NUMBER`  `LOAN NUMBER` VARCHAR( 9 ) 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 from _loan_data WHERE `LOAN AMOUNT` = 0;
delete urd.* from _unpaid_repayment_data urd LEFT JOIN _loan_data sld ON urd.`LOAN NUMBER` = sld.`LOAN NUMBER` WHERE sld.`LOAN NUMBER` IS NULL;
delete prd.* from _paid_repayment_data prd LEFT JOIN _loan_data sld ON prd.`LOAN NUMBER` = sld.`LOAN NUMBER` WHERE sld.`LOAN NUMBER` IS NULL;


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

ALTER TABLE  `_client_data` 
CHANGE  `NRIC`  `NRIC` VARCHAR( 32 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `GENDER`  `GENDER` VARCHAR( 16 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `NAME`  `NAME` VARCHAR( 50 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `Date of Birth`  `Date of Birth` VARCHAR( 32 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `Main Address`  `Main Address` VARCHAR( 49 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL;

-- ALTER TABLE  `borrowers` DROP INDEX  `name`;

INSERT INTO borrowers(uid, name, address1, hand_phone, dob) SELECT NRIC, NAME, `Main Address`, `Main Phone Number`, concat(right(trim(`Date of Birth`), 4), '-', substring(trim(`Date of Birth`), 4, 2), '-', left(trim(`Date of Birth`), 2)) FROM _client_data;

update borrowers set borrower_type_id = '1';

-- UPDATE borrowers SET dob = (SELECT concat(right(trim(`Date of Birth`), 4), '-', substring(trim(`Date of Birth`), 4, 2), '-', left(trim(`Date of Birth`), 2)) FROM _client_data WHERE uid = NRIC) WHERE (SELECT concat(right(trim(`Date of Birth`), 4), '-', substring(trim(`Date of Birth`), 4, 2), '-', left(trim(`Date of Birth`), 2)) FROM _client_data WHERE uid = NRIC) IS NOT NULL;

-- SELECT b.uid, b.name, b.dob FROM borrowers b LEFT JOIN _client_data ON uid = NRIC WHERE NRIC IS NULL
-- SELECT * FROM projects_loan_abm.borrowers b1 LEFT JOIN aaa.borrowers b2 ON b1.uid = b2.uid WHERE b1.uid IS NULL

INSERT INTO blacklist_reasons(id, name) SELECT null, `BLACKLIST REMARKS` FROM _blacklisted_clients_data WHERE `BLACKLIST REMARKS` != '' AND `BLACKLIST REMARKS` IS NOT NULL;

ALTER TABLE  `_blacklisted_clients_data` 
CHANGE  `BLACKLISTED NRIC`  `BLACKLISTED NRIC` VARCHAR( 9 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `BLACKLIST REMARKS`  `BLACKLIST REMARKS` VARCHAR( 8 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL;


update borrowers left join _blacklisted_clients_data ON borrowers.uid = _blacklisted_clients_data.`BLACKLISTED NRIC` LEFT JOIN blacklist_reasons ON blacklist_reasons.name = _blacklisted_clients_data.`BLACKLIST REMARKS` SET borrowers.blacklisted = '1' , borrowers.blacklist_reason_id = blacklist_reasons.id WHERE _blacklisted_clients_data.`BLACKLISTED NRIC` IS NOT NULL;


-- select * FROM borrowers left join _blacklisted_clients_data ON borrowers.uid = _blacklisted_clients_data.`BLACKLISTED NRIC`;

update _loan_data SET `PURPOSE OF LOAN` = 'Others' WHERE `PURPOSE OF LOAN` = 'OTHER';
update _loan_data SET `PURPOSE OF LOAN` = 'Domestic' WHERE `PURPOSE OF LOAN` = 'Home / Family Needs';
update _loan_data SET `PURPOSE OF LOAN` = 'Others' WHERE `PURPOSE OF LOAN` = 'Housing';


-- SELECT * FROM _loan_data LEFT JOIN _client_data ON _loan_data.`BORROWER 1` = _client_data.`NRIC` WHERE _client_data.NAME IS NULL;




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





-- ALTER TABLE  `applications` DROP INDEX  `account_number`;

-- INSERT INTO applications(account_number, date, borrower_id, amount, loan_purpose_id, vt_user_id, new_surety_id) SELECT `LOAN NUMBER`, DATE_ADD('1900-01-01', INTERVAL `LOAN DATE`-2 DAY), (SELECT id FROM borrowers WHERE uid = `BORROWER 1`), `LOAN AMOUNT`, (SELECT id FROM loan_purposes WHERE name = `PURPOSE OF LOAN`), '1', (SELECT id FROM borrowers WHERE uid = `SURETY 1`) FROM _loan_data;

INSERT INTO applications(account_number, date, borrower_id, amount, loan_purpose_id, vt_user_id, new_surety_id) SELECT `LOAN NUMBER`, concat(right(trim(`LOAN DATE`), 4), '-', substring(trim(`LOAN DATE`), 4, 2), '-', left(trim(`LOAN DATE`), 2)), (SELECT id FROM borrowers WHERE uid = `BORROWER 1` ORDER BY id LIMIT 1), `LOAN AMOUNT`, (SELECT id FROM loan_purposes WHERE name = `PURPOSE OF LOAN`), '1', (SELECT id FROM borrowers WHERE uid = `SURETY 1` ORDER BY id LIMIT 1) FROM _loan_data WHERE `BORROWER 1` != '' AND `A` = 'PC';


update _loan_data SET PACKAGE = 'bi-weekly' WHERE PACKAGE = 'B/Weekly';
update _loan_data SET PACKAGE = 'monthly' WHERE PACKAGE = 'Monthly';
update _loan_data SET PACKAGE = 'weekly' WHERE PACKAGE = 'Weekly';

-- SELECT account_number, COUNT(id) FROM applications GROUP BY account_number HAVING COUNT(id) > 1
-- SELECT `LOAN NUMBER`, COUNT(*) FROM _loan_data GROUP BY `LOAN NUMBER` HAVING COUNT(*) > 1

ALTER TABLE  `books` ADD UNIQUE (`name`);
INSERT IGNORE INTO books(name, Bank) SELECT `PAYMENT MODE`, '1' FROM _paid_repayment_data;
INSERT IGNORE INTO books(name, Bank) SELECT `PAYMENT MODE/ACCOUNT`, '1' FROM _debit_credit_transfer_data;
insert into ledgers(name, ledger_type_id, book_id) select name, '4', id FROM books where name != 'Cash Book' AND name != 'Bank Book';

INSERT INTO disbursements(date, application_id, account_number, amount, interest_rate, interest_frequency_id, installment_count, payment_frequency_id, payment_type_id, Remarks, book_id) 
SELECT date, id, account_number, amount, (SELECT INTEREST FROM _loan_data WHERE `LOAN NUMBER` = ap.account_number AND `LOAN AMOUNT` != 0), '5', (SELECT IF(TERM = 'OPEN', 0, TERM) FROM _loan_data WHERE `LOAN NUMBER` = ap.account_number AND `LOAN AMOUNT` != 0), (SELECT tf.id FROM _loan_data sbd LEFT JOIN time_frequencies tf ON sbd.`PACKAGE` = tf.name WHERE `LOAN NUMBER` = ap.account_number AND `LOAN AMOUNT` != 0), '1', (SELECT `LOAN REMARK` FROM _loan_data WHERE `LOAN NUMBER` = ap.account_number AND `LOAN AMOUNT` != 0), (SELECT books.id FROM _loan_data LEFT JOIN books ON books.name = _loan_data.`PAYMENT MODE` WHERE `LOAN NUMBER` = ap.account_number AND `LOAN AMOUNT` != 0) FROM applications ap;

update disbursements SET status = '0';
update disbursements SET interest_calculation_method_id = (SELECT id FROM interest_calculation_methods WHERE name = 'Simple Interest') WHERE installment_count > 0;
update disbursements SET interest_calculation_method_id = (SELECT id FROM interest_calculation_methods WHERE name = 'Rest') WHERE installment_count = 0 AND (interest_calculation_method_id = '0' OR interest_calculation_method_id = '');

update disbursements set first_payment_date = date + interval 1 month where payment_frequency_id = (SELECT id FROM time_frequencies WHERE name = 'monthly');
update disbursements set first_payment_date = date + interval 1 week where payment_frequency_id = (SELECT id FROM time_frequencies WHERE name = 'weekly');
update disbursements set first_payment_date = date + interval 2 week where payment_frequency_id = (SELECT id FROM time_frequencies WHERE name = 'bi-weekly');
update disbursements set first_payment_date = date + interval 1 day where payment_frequency_id = (SELECT id FROM time_frequencies WHERE name = 'daily');

update disbursements set installment_count = '12' where interest_calculation_method_id = '2' AND payment_frequency_id = '4';
update disbursements set installment_count = '26' where interest_calculation_method_id = '2' AND payment_frequency_id = '3';
update disbursements set installment_count = '52' where interest_calculation_method_id = '2' AND payment_frequency_id = '2';
update disbursements set installment_count = '1' where interest_calculation_method_id = '2' AND payment_frequency_id = '5';

UPDATE _paid_repayment_data SET `REPAYMENT DUE DATE` = REPLACE(`REPAYMENT DUE DATE`, ' Jan ', '/01/');
UPDATE _paid_repayment_data SET `REPAYMENT DUE DATE` = REPLACE(`REPAYMENT DUE DATE`, ' Feb ', '/02/');
UPDATE _paid_repayment_data SET `REPAYMENT DUE DATE` = REPLACE(`REPAYMENT DUE DATE`, ' Mar ', '/03/');
UPDATE _paid_repayment_data SET `REPAYMENT DUE DATE` = REPLACE(`REPAYMENT DUE DATE`, ' Apr ', '/04/');
UPDATE _paid_repayment_data SET `REPAYMENT DUE DATE` = REPLACE(`REPAYMENT DUE DATE`, ' May ', '/05/');
UPDATE _paid_repayment_data SET `REPAYMENT DUE DATE` = REPLACE(`REPAYMENT DUE DATE`, ' Jun ', '/06/');
UPDATE _paid_repayment_data SET `REPAYMENT DUE DATE` = REPLACE(`REPAYMENT DUE DATE`, ' Jul ', '/07/');
UPDATE _paid_repayment_data SET `REPAYMENT DUE DATE` = REPLACE(`REPAYMENT DUE DATE`, ' Aug ', '/08/');
UPDATE _paid_repayment_data SET `REPAYMENT DUE DATE` = REPLACE(`REPAYMENT DUE DATE`, ' Sep ', '/09/');
UPDATE _paid_repayment_data SET `REPAYMENT DUE DATE` = REPLACE(`REPAYMENT DUE DATE`, ' Oct ', '/10/');
UPDATE _paid_repayment_data SET `REPAYMENT DUE DATE` = REPLACE(`REPAYMENT DUE DATE`, ' Nov ', '/11/');
UPDATE _paid_repayment_data 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, prd.`REPAYMENT DUE DATE`, 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 prd LEFT JOIN applications ap ON prd.`LOAN NUMBER` = ap.account_number LEFT JOIN disbursements d ON ap.id = d.application_id;

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 prd LEFT JOIN applications ap ON prd.`LOAN NUMBER` = ap.account_number LEFT JOIN disbursements d ON ap.id = d.application_id WHERE ap.account_number IS NOT NULL;

update repayments set late_fee = permit_fee;
update repayments set amount = late_fee + principal + interest WHERE amount != late_fee + principal + interest;




-- -----------------------------
-- Non loan related transactions
-- -----------------------------

INSERT INTO ledgers(name, ledger_type_id) SELECT `LEDGER CODE`, '3' FROM  `_debit_credit_transfer_data` GROUP BY  `LEDGER CODE`;

ALTER TABLE  `_debit_credit_transfer_data` ADD  `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;

ALTER TABLE  `_debit_credit_transfer_data` 
CHANGE  `PAYMENT MODE/ACCOUNT`  `PAYMENT MODE/ACCOUNT` VARCHAR( 32 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE `VOUCHER NO.` `VOUCHER NO.` VARCHAR(32) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL, 
CHANGE  `CHEQUE NO.`  `CHEQUE NO.` VARCHAR( 32 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `TRANSACTION`  `TRANSACTION` VARCHAR( 32 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `PAYER / PAYEE`  `PAYER / PAYEE` VARCHAR( 50 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `LEDGER CODE`  `LEDGER CODE` VARCHAR( 30 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `DESCRIPTION`  `DESCRIPTION` VARCHAR( 512 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `TRANSACTION REMARKS`  `TRANSACTION REMARKS` VARCHAR( 512 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL;


ALTER TABLE  `_debit_credit_transfer_data` CHANGE  `TRANSACTION DATE`  `TRANSACTION DATE` VARCHAR( 32 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;

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

-- --------
-- payments
-- --------

INSERT INTO vouchers(date, transaction_type, pay_type, ledger_id, book_id, book_ledger_id, payment_type_id, amount, narration, entry_mode) 
SELECT 
	concat(right(trim(`TRANSACTION DATE`), 4), '-', substring(trim(`TRANSACTION DATE`), 4, 2), '-', left(trim(`TRANSACTION DATE`), 2)), 
	'P', 
	'O', 
	(SELECT id FROM ledgers WHERE name = dctd.`LEDGER CODE` ORDER BY id ASC LIMIT 1), 
	(SELECT id FROM books WHERE name = dctd.`PAYMENT MODE/ACCOUNT`), 
	(SELECT id FROM ledgers WHERE name = dctd.`PAYMENT MODE/ACCOUNT` AND book_id != '0'), 
	IF(`CHEQUE NO.` IS NULL, (SELECT id FROM payment_types WHERE name = 'Cash'), (SELECT id FROM payment_types WHERE name = 'Cheque')), 
	AMOUNT, 
	`TRANSACTION REMARKS`, 
	IF(TRANSACTION = 'Debit', 'P', 'R') 
FROM _debit_credit_transfer_data dctd WHERE `TRANSACTION` = 'Debit' AND dctd.`PAYMENT MODE/ACCOUNT` IN ('Cash - PC', 'CIMB - PC', 'UOB - PC');

-- --------
-- receipts
-- --------

INSERT INTO vouchers(date, transaction_type, pay_type, ledger_id, book_id, book_ledger_id, payment_type_id, amount, narration, entry_mode) 
SELECT 
	concat(right(trim(`TRANSACTION DATE`), 4), '-', substring(trim(`TRANSACTION DATE`), 4, 2), '-', left(trim(`TRANSACTION DATE`), 2)), 
	'R', 
	'O', 
	(SELECT id FROM ledgers WHERE name = dctd.`LEDGER CODE` ORDER BY id ASC LIMIT 1), 
	(SELECT id FROM books WHERE name = dctd.`PAYMENT MODE/ACCOUNT`), 
	(SELECT id FROM ledgers WHERE name = dctd.`PAYMENT MODE/ACCOUNT` AND book_id != '0'), 
	IF(`CHEQUE NO.` IS NULL, (SELECT id FROM payment_types WHERE name = 'Cash'), (SELECT id FROM payment_types WHERE name = 'Cheque')), 
	AMOUNT*-1, 
	`TRANSACTION REMARKS`, 
	IF(TRANSACTION = 'Debit', 'P', 'R') 
FROM _debit_credit_transfer_data dctd WHERE `TRANSACTION` = 'Credit' AND dctd.`PAYMENT MODE/ACCOUNT` IN ('Cash - PC', 'CIMB - PC', 'UOB - PC');


-- --------------
-- book transfers
-- --------------



-- SELECT CONCAT(IFNULL(`CHEQUE NO.`, ''), `TRANSACTION DATE`, ABS(`AMOUNT`)) as `key`, count(*), `PAYMENT MODE/ACCOUNT`, ABS(`AMOUNT`), DATE_ADD('1900-01-01', INTERVAL `TRANSACTION DATE`-2 DAY) as date, `TRANSACTION DATE`, `CHEQUE NO.`, `TRANSACTION`, `PAYER / PAYEE`, `LEDGER CODE` FROM _debit_credit_transfer_data WHERE `TRANSACTION` LIKE '%transfer%' GROUP BY CONCAT(IFNULL(`CHEQUE NO.`, ''), `TRANSACTION DATE`, ABS(`AMOUNT`));

-- SELECT * FROM vouchers WHERE transaction_type = 'P' AND pay_type = 'O' AND entry_mode = 'T';

INSERT INTO vouchers(
	date, transaction_type, pay_type, entry_mode, book_ledger_id, ledger_id, book_id, amount, transaction_number, narration
) 
SELECT 
	concat(right(trim(dctd.`TRANSACTION DATE`), 4), '-', substring(trim(dctd.`TRANSACTION DATE`), 4, 2), '-', left(trim(dctd.`TRANSACTION DATE`), 2)), 
	'P', 
	'O', 
	'T', 
	(SELECT id FROM ledgers WHERE name = dctd.`PAYMENT MODE/ACCOUNT`) as `from`, 
	(SELECT id FROM ledgers WHERE name = dctd1.`PAYMENT MODE/ACCOUNT`) as `to`, 
	(SELECT id FROM books WHERE name = dctd.`PAYMENT MODE/ACCOUNT`) as `book_id`, 
	dctd.`AMOUNT`, 
	dctd.`CHEQUE NO.`, 
	dctd.`TRANSACTION REMARKS`
FROM _debit_credit_transfer_data dctd 
LEFT JOIN _debit_credit_transfer_data dctd1 ON 
	dctd.`TRANSACTION DATE` = dctd1.`TRANSACTION DATE` AND 
	dctd.`TRANSACTION` != dctd1.`TRANSACTION` AND 
	ABS(dctd.`AMOUNT`) = ABS(dctd1.`AMOUNT`) 
WHERE dctd.`TRANSACTION` LIKE '%transfer%' AND dctd.`AMOUNT` > 0  AND dctd.`PAYMENT MODE/ACCOUNT` IN ('Cash - PC', 'CIMB - PC', 'UOB - PC')
GROUP BY dctd.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';










-- os principal reset in repayments
update repayments r left join (SELECT r1.id as pk, d.amount - SUM(r2.principal) as amount FROM disbursements d LEFT JOIN repayments as r1 ON d.id = r1.disbursement_id LEFT JOIN repayments as r2 ON r1.disbursement_id = r2.disbursement_id WHERE r2.date <= r1.date GROUP BY r1.id ORDER BY r1.id) as tx on r.id = tx.pk set r.outstanding_principal = tx.amount WHERE tx.amount != r.outstanding_principal;








-- payment_type_id for disbursements, amount for repayments


-- update disbursements d set interest_rate = (SELECT `INTEREST` FROM _loan_data WHERE `LOAN NUMBER` = d.account_number) WHERE (SELECT `INTEREST` FROM _loan_data WHERE `LOAN NUMBER` = d.account_number) IS NOT NULL;

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';


-- update vouchers SET book_ledger_id = (SELECT id FROM ledgers WHERE book_id = vouchers.book_id) where book_ledger_id = '0' AND book_id != '0'

update applications set loan_type_id = '2';


UPDATE disbursements d SET status = '1' WHERE amount = (SELECT SUM(principal) FROM repayments WHERE disbursement_id = d.id);

-- -------------------------------
-- Borrower company and employment
-- -------------------------------
ALTER TABLE  `_client_employment_data` 
CHANGE  `NRIC`  `NRIC` VARCHAR( 10 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `Company Name`  `Company Name` VARCHAR( 40 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `Company Phone Number`  `Company Phone Number` VARCHAR( 9 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `Company Registration No.`  `Company Registration No.` VARCHAR( 15 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `Job Period`  `Job Period` VARCHAR( 14 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `Type of Business`  `Type of Business` VARCHAR( 40 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `Basic Employment`  `Basic Employment` VARCHAR( 13 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `Job Title`  `Job Title` VARCHAR( 30 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL;

ALTER TABLE  `_client_employment_data` ADD INDEX (  `NRIC` );

update borrowers b SET company = (SELECT `Company Name` FROM _client_employment_data WHERE NRIC = b.uid LIMIT 1);
update borrowers b SET designation = (SELECT `Job Title` FROM _client_employment_data WHERE NRIC = b.uid LIMIT 1);



-- ---------------
-- Borrower stuffs
-- ---------------

ALTER TABLE  `_income_pa_data` 
CHANGE  `LOAN NUMBER`  `LOAN NUMBER` VARCHAR( 9 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `NRIC`  `NRIC` VARCHAR( 10 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL;

UPDATE borrowers SET annual_income = (SELECT `INCOME P.A.` FROM `_income_pa_data` WHERE `NRIC` = borrowers.uid LIMIT 1) WHERE annual_income = '0';


ALTER TABLE `_client_address_data` 
CHANGE  `NRIC`  `NRIC` VARCHAR( 10 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `LABEL`  `LABEL` VARCHAR( 12 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `BLOCK`  `BLOCK` VARCHAR( 10 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `UNIT`  `UNIT` VARCHAR( 8 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `FULL ADDRESS`  `FULL ADDRESS` VARCHAR( 50 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL;

UPDATE borrowers SET address1 = (SELECT `FULL ADDRESS` FROM _client_address_data WHERE `LABEL` = 'Address [1]' AND `NRIC` = borrowers.uid) where address1 = '';
UPDATE borrowers SET address2 = (SELECT `FULL ADDRESS` FROM _client_address_data WHERE `LABEL` = 'Firm Address' AND `NRIC` = borrowers.uid) where address2 = '';


UPDATE borrowers SET pincode = (SELECT `POSTAL CODE` FROM _client_address_data WHERE `LABEL` = 'Address [1]' AND `NRIC` = borrowers.uid) where pincode = '';
UPDATE borrowers SET blk = (SELECT `BLOCK` FROM _client_address_data WHERE `LABEL` = 'Address [1]' AND `NRIC` = borrowers.uid) where blk = '';
UPDATE borrowers SET unit = (SELECT `UNIT` FROM _client_address_data WHERE `LABEL` = 'Address [1]' AND `NRIC` = borrowers.uid) where unit = '';

update borrowers b set country_id = '1' WHERE (SELECT `Nationality Status` FROM _client_data WHERE NRIC = b.uid LIMIT 1) = "Singaporean" AND b.country_id = '0';

-- Identification types
update borrowers b set identification_type_id = '5' WHERE (SELECT `Nationality` FROM _client_data WHERE NRIC = b.uid LIMIT 1) = 'Business Pass' AND b.identification_type_id = '';
update borrowers b set identification_type_id = '3' WHERE (SELECT `Nationality` FROM _client_data WHERE NRIC = b.uid LIMIT 1) = 'E-Pass' AND b.identification_type_id = '';
update borrowers b set identification_type_id = '3' WHERE (SELECT `Nationality` FROM _client_data WHERE NRIC = b.uid LIMIT 1) = 'Foreigner' AND b.identification_type_id = '';
update borrowers b set identification_type_id = '3' WHERE (SELECT `Nationality` FROM _client_data WHERE NRIC = b.uid LIMIT 1) = 'Identity Card' AND b.identification_type_id = '';
update borrowers b set identification_type_id = '2' WHERE (SELECT `Nationality` FROM _client_data WHERE NRIC = b.uid LIMIT 1) = 'Permanent Resident' AND b.identification_type_id = '';
update borrowers b set identification_type_id = '5' WHERE (SELECT `Nationality` FROM _client_data WHERE NRIC = b.uid LIMIT 1) = 'S-Pass' AND b.identification_type_id = '';
update borrowers b set identification_type_id = '1' WHERE (SELECT `Nationality` FROM _client_data WHERE NRIC = b.uid LIMIT 1) = 'Singapore Citizen' AND b.identification_type_id = '';
update borrowers b set identification_type_id = '1' WHERE (SELECT `Nationality` FROM _client_data WHERE NRIC = b.uid LIMIT 1) = 'Singaporean' AND b.identification_type_id = '';
update borrowers b set identification_type_id = '3' WHERE (SELECT `Nationality` FROM _client_data WHERE NRIC = b.uid LIMIT 1) = 'Work Permit' AND b.identification_type_id = '';

-- Singapore NRIC - Singapore Citizen
-- Singapore PR - Permanent Resident
-- Foreign Identification Number -E-Pass, Foreigner E-Pass, Work Permit
-- Others - Business Pass

ALTER TABLE  `nationality` 
CHANGE  `NRIC`  `NRIC` VARCHAR( 10 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `NATIONALITY STATUS`  `NATIONALITY STATUS` VARCHAR( 14 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `NATIONALITY`  `NATIONALITY` VARCHAR( 19 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL;


UPDATE borrowers SET country_id = '1' WHERE (SELECT `NATIONALITY STATUS` FROM nationality WHERE NRIC = borrowers.uid) = 'Singapore NRIC' AND country_id = '';
UPDATE borrowers SET country_id = (SELECT id FROM countries WHERE name = 'Foreigner') WHERE (SELECT `NATIONALITY STATUS` FROM nationality WHERE NRIC = borrowers.uid) = 'Foreigner' AND country_id = '';

UPDATE borrowers SET identification_type_id = '1' WHERE (SELECT `NATIONALITY` FROM nationality WHERE NRIC = borrowers.uid) = 'Singaporean Citizen' AND identification_type_id = '';
UPDATE borrowers SET identification_type_id = '2' WHERE (SELECT `NATIONALITY` FROM nationality WHERE NRIC = borrowers.uid) = 'Permanent Resident' AND identification_type_id = '';

UPDATE borrowers SET identification_type_id = '3' WHERE (SELECT `NATIONALITY` FROM nationality WHERE NRIC = borrowers.uid) = 'E-Pass' AND (SELECT `NATIONALITY STATUS` FROM nationality WHERE NRIC = borrowers.uid) = 'Foreigner' AND identification_type_id = '';
UPDATE borrowers SET identification_type_id = '3' WHERE (SELECT `NATIONALITY` FROM nationality WHERE NRIC = borrowers.uid) = 'S-Pass' AND (SELECT `NATIONALITY STATUS` FROM nationality WHERE NRIC = borrowers.uid) = 'Foreigner' AND identification_type_id = '';
UPDATE borrowers SET identification_type_id = '3' WHERE (SELECT `NATIONALITY` FROM nationality WHERE NRIC = borrowers.uid) = 'Work Permit' AND (SELECT `NATIONALITY STATUS` FROM nationality WHERE NRIC = borrowers.uid) = 'Foreigner' AND identification_type_id = '';
UPDATE borrowers SET identification_type_id = '3' WHERE (SELECT `NATIONALITY` FROM nationality WHERE NRIC = borrowers.uid) = 'Identity Card' AND (SELECT `NATIONALITY STATUS` FROM nationality WHERE NRIC = borrowers.uid) = 'Foreigner' AND identification_type_id = '';

UPDATE borrowers SET identification_type_id = '5' WHERE (SELECT `NATIONALITY` FROM nationality WHERE NRIC = borrowers.uid) = 'S-Pass' AND (SELECT `NATIONALITY STATUS` FROM nationality WHERE NRIC = borrowers.uid) = 'Singapore NRIC' AND identification_type_id = '';





