-- all individual borrowers
-- no blacklisted
-- no sureties


ALTER TABLE bad_debts RENAME TO _bad_debts;
ALTER TABLE bad_debts_clear RENAME TO _bad_debts_clear;
ALTER TABLE client_address_list RENAME TO _client_address_list;
ALTER TABLE client_contact_list RENAME TO _client_contact_list;
ALTER TABLE client_particular RENAME TO _client_particular;
ALTER TABLE employment_details RENAME TO _employment_details;
ALTER TABLE loan RENAME TO _loan;
ALTER TABLE loan_details RENAME TO _loan_details;
ALTER TABLE paymentmode RENAME TO _paymentmode;
ALTER TABLE repayment RENAME TO _repayment;
ALTER TABLE accounts RENAME TO _accounts;
ALTER TABLE company RENAME TO _company;
ALTER TABLE repay_list RENAME TO _repay_list;
ALTER TABLE variation_list RENAME TO _variation_list;
ALTER TABLE goodwill RENAME TO _goodwill;
ALTER TABLE chat_list RENAME TO _chat_list;




ALTER TABLE `_client_particular` 
CHANGE `marital` `marital` VARCHAR(128) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL, 
CHANGE `gender` `gender` VARCHAR(128) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL;


update _client_particular SET marital = 'Other' WHERE marital = 'Separated';
update _client_particular SET gender = 'Female' WHERE gender IN('Mdm.', 'Miss.', 'Mrs.');
update _client_particular SET gender = 'Male' WHERE gender IN('Mr.');

INSERT INTO borrowers(uid, name, dob, marital_status, email, gender, identification_type_id, borrower_type_id, country_id)
SELECT nric, name, dob, marital, email, gender, (SELECT id FROM identification_types WHERE name = ictype) as identification_type_id, '1', (SELECT id FROM countries WHERE name = nationality) as country_id FROM _client_particular;

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


ALTER TABLE `_client_address_list` ADD INDEX(`nric`);
ALTER TABLE `_bad_debts` ADD INDEX(`loanno`);

-- firm address - company address
-- Home 1 - mailing address
update borrowers b LEFT JOIN _client_address_list cal ON b.uid = cal.nric SET b.pincode = cal.postalcode, b.building = cal.buildingnumber, b.unit = cal.unit;
-- update borrowers b LEFT JOIN _client_address_list cal ON b.uid = cal.nric SET b.address1 = cal.fulladdress WHERE cal.label = 'Home [1]';
update borrowers b LEFT JOIN _client_address_list cal ON b.uid = cal.nric SET b.address1 = cal.fulladdress WHERE cal.label = 'Address [1]';
update borrowers b LEFT JOIN _client_address_list cal ON b.uid = cal.nric SET b.address2 = cal.fulladdress WHERE cal.label = 'Firm Address';

-- address1b
-- address2
-- city
-- state
-- country
-- work_phone
-- home_phone
-- hand_phone
-- sms_phone
-- alternate_email
-- blk
-- level
-- street
-- company
-- designation
-- remarks
-- borrower_remarks
-- business_pincode
-- property_ownership
-- monthly_income
-- past_6m_income

ALTER TABLE `_loan` CHANGE `typeofloan` `typeofloan` VARCHAR(128) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL;
update _loan SET typeofloan = 'Unsecured' WHERE typeofloan = 'Unsecure';


ALTER TABLE `_loan` ADD INDEX(`loanno`);
ALTER TABLE `_loan_details` ADD INDEX(`loanid`);
ALTER TABLE `_loan_details` ADD INDEX(`nric`);



INSERT INTO applications(account_number, date, borrower_id, amount, new_surety_id, loan_purpose_id, loan_type_id, agent_id, vt_user_id)
SELECT loanno, loandate, (SELECT id FROM borrowers WHERE uid = ld.nric) as borrower_id, ABS(loanamount), (SELECT id FROM borrowers WHERE uid = lds.nric) as new_surety_id, (SELECT id FROM loan_purposes WHERE name = l.purposeofloan) as loan_purpose_id, (SELECT id FROM loan_types WHERE name = typeofloan) as loan_type_id, '1', '1' 
FROM _loan l 
LEFT JOIN _loan_details ld ON l.loanid = ld.loanid AND ld.status = 'BORROWER' 
LEFT JOIN _loan_details lds ON l.loanid = lds.loanid AND lds.status = 'SURETY' 
WHERE l.hidden = 0 AND ABS(loanamount) > 0;


INSERT IGNORE INTO books(name, Bank) SELECT paymentmode, '1' FROM _paymentmode;
-- INSERT IGNORE INTO books(name, Bank) SELECT `type_of_pay`, '1' FROM _accounts WHERE type_of_pay != '' GROUP BY type_of_pay;
-- INSERT IGNORE INTO books(name, Bank) SELECT ledgercode, '1' FROM _accounts WHERE transaction = 'Transfer Out' OR transaction = 'Transfer In' GROUP BY ledgercode;
INSERT IGNORE INTO books(name, Bank) SELECT account, '1' FROM _accounts GROUP BY account;
insert into ledgers(name, ledger_type_id, book_id) select name, '4', id FROM books where name NOT IN('Cash Book', 'Bank Book', 'Bad Debt Book', 'Goodwill Book');
UPDATE books SET type = 'cash' WHERE name LIKE '%cash%';
UPDATE books SET type = 'bank' WHERE name LIKE '%bank%';

insert into disbursements(date, application_id, account_number, amount, first_payment_date, payment_type_id, interest_rate, interest_frequency_id, installment_count, payment_frequency_id, late_fee, acceptance_fee, late_interest_rate, transaction_number, contract_variation_fee, cheque_dishonour_fee, renewal_fee, borrower_annual_income, book_id, book_amount)
SELECT loandate, (SELECT id FROM applications WHERE account_number = loanno) as application_id, loanno, ABS(loanamount), firstrepaymentdate, (SELECT id FROM payment_types WHERE name = IF(paymentmode = 'Bank', 'Cheque', paymentmode)) as payment_type_id, interestratepa, '5', term, (SELECT id FROM time_frequencies WHERE name = package) as payment_frequency_id, latefee, acceptance, lateinterest, CONCAT(voucherno, ' ', chequeno) as transaction_number, variationfee, chequedishonourfee, renewalfee, incomepa, (SELECT id FROM books WHERE name = paymentmode) as book_id, ABS(loanamount) as book_amount
FROM _loan l LEFT JOIN _loan_details ld ON l.loanid = ld.loanid AND ld.status = 'BORROWER'
WHERE l.hidden = 0 AND ABS(loanamount) > 0;

update disbursements SET acceptance_fee_percent = ROUND(acceptance_fee/amount*100, 2);

-- account_number_prefix
-- account_number_suffix
-- eir
-- irr
-- interest_calculation_method_id
-- second_payment_date
-- last_payment_date
-- Remarks
-- status
-- language_code
-- terms_and_conditions
-- late_repayment_fee
-- legal_fee
-- termination_fee

-- security_details
-- borrower_bank_account_number

-- acceptance_fee_percent
-- renewal_fee_percent
-- late_fee_percent
-- contract_variation_fee_percent
-- cheque_dishonour_fee_percent
-- termination_fee_percent
-- legal_fee_percent
-- late_repayment_fee_percent



insert into repayments(disbursement_id, application_id, account_number, date, amount, principal, interest, book_id, permit_fee, receipt_number, remarks, late_interest, payment_type_id, acceptance_fee, late_fee)
SELECT (SELECT d.id FROM disbursements d WHERE d.account_number = l.loanno) as disbursement_id, (SELECT d.application_id FROM disbursements d WHERE d.account_number = l.loanno) as application_id, l.loanno, actualrepaydate, actualrepayamount, principal, interest, (SELECT id FROM books WHERE name = r.paymentmode) as book_id, otherpermit, receiptno, remarks, r.lateinterest, (SELECT id FROM payment_types WHERE name = IF(r.paymentmode = 'Bank', 'Cheque', r.paymentmode)) as payment_type_id, IF(remarks = 'Administrative Fee', otherpermit, '') as acceptance_fee, IF(remarks = 'Administrative Fee', '', otherpermit) as late_fee
FROM _repayment r LEFT JOIN _loan l ON r.loanid = l.loanid 
WHERE actualrepaydate != '0000-00-00' AND r.hidden = 0 
HAVING application_id IS NOT NULL;



-- voucher_number
-- payer_type_id
-- other_payer_name
-- other_payer_address
-- other_payer_uid
-- transaction_number

-- paid_principal
-- paid_interest

-- acceptance_fee
-- renewal_fee
-- late_fee
-- contract_variation_fee
-- cheque_dishonoured_fee
-- termination_fee
-- legal_fee

-- compound_interest
-- interest_growth

-- outstanding_principal
-- cheque_dishonour





insert into disbursement_contact_details(disbursement_id, code, pincode, building, unit, address1, company, designation)
SELECT d.id, IF(ld.status = 'BORROWER', 'b1', IF(ld.status = 'SURETY', 's1', '')), postalcode, buildingnumber, unit, fulladdress, firmname, jobtitle 
FROM _loan_details ld 
LEFT JOIN _loan l ON ld.loanid = l.loanid
LEFT JOIN disbursements d ON d.account_number = l.loanno
WHERE l.hidden = 0 AND ABS(loanamount) > 0;

-- address1b
-- address2
-- work_phone
-- home_phone
-- hand_phone
-- sms_phone
-- email
-- alternate_email
-- blk
-- level
-- street

update disbursements SET interest_calculation_method_id = (SELECT id FROM interest_calculation_methods WHERE name = 'Compound Interest') WHERE date >= '2015-10-01';
update disbursements SET interest_calculation_method_id = (SELECT id FROM interest_calculation_methods WHERE name = 'Simple Interest') WHERE installment_count > 0 AND date < '2015-10-01'; 
update disbursements SET interest_calculation_method_id = (SELECT id FROM interest_calculation_methods WHERE name = 'Rest Schedule') WHERE installment_count = 0 AND date < '2015-10-01' AND (interest_calculation_method_id = '0' OR interest_calculation_method_id = '');





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









INSERT INTO ledgers(name, ledger_type_id) SELECT ledgercode, '3' FROM  `_accounts` WHERE transaction NOT IN('Transfer In', 'Transfer Out') GROUP BY `ledgercode` ;

INSERT INTO vouchers(date, transaction_type, pay_type, ledger_id, book_id, book_ledger_id, payment_type_id, amount, narration, entry_mode) 
SELECT 
	transactiondate, 'P', 'O', 
	(SELECT id FROM ledgers WHERE name = TRIM(dctd.`ledgercode`) ORDER BY id ASC LIMIT 1) as ledger_id, 
	(SELECT id FROM books WHERE name = TRIM(dctd.`account`)) as book_id, 
	(SELECT id FROM ledgers WHERE name = TRIM(dctd.`account`) AND book_id != '0') as book_ledger_id, 
	IF(chequeno IS NULL, (SELECT id FROM payment_types WHERE name = 'Cash'), (SELECT id FROM payment_types WHERE name = 'Cheque')) as payment_type_id, 
	ABS(amount), 
	remarks, 
	IF(transaction = 'Debit', 'P', 'R') 
FROM _accounts dctd WHERE `transaction` = 'Debit';

INSERT INTO vouchers(date, transaction_type, pay_type, ledger_id, book_id, book_ledger_id, payment_type_id, amount, narration, entry_mode) 
SELECT 
	transactiondate, 'R', 'O', 
	(SELECT id FROM ledgers WHERE name = dctd.`ledgercode` ORDER BY id ASC LIMIT 1) as ledger_id, 
	(SELECT id FROM books WHERE name = dctd.`account`) as book_id, 
	(SELECT id FROM ledgers WHERE name = dctd.`account` AND book_id != '0') as book_ledger_id, 
	IF(`chequeno` IS NULL, (SELECT id FROM payment_types WHERE name = 'Cash'), (SELECT id FROM payment_types WHERE name = 'Cheque')) as payment_type_id, 
	ABS(AMOUNT), 
	remarks, 
	IF(transaction = 'Debit', 'P', 'R') 
FROM _accounts dctd WHERE `transaction` = 'Credit';




INSERT INTO vouchers(
	date, transaction_type, pay_type, entry_mode, book_ledger_id, ledger_id, book_id, amount, transaction_number, narration
) 
SELECT 
	dctd.transactiondate, 'P', 'O', 'T', 
	(SELECT id FROM ledgers WHERE name = dctd.`account` LIMIT 1) as `from`, 
	(SELECT id FROM ledgers WHERE name = dctd1.`account` LIMIT 1) as `to`, 
	(SELECT id FROM books WHERE name = dctd.`account` LIMIT 1) as `book_id`, 
	ABS(dctd.`amount`), 
	dctd.`chequeno`, 
	dctd.`remarks`
FROM _accounts dctd 
LEFT JOIN _accounts dctd1 ON 
	dctd.`transactiondate` = dctd1.`transactiondate` AND 
	dctd.`transaction` != dctd1.`transaction` AND 
	ABS(dctd.`amount`) = ABS(dctd1.`amount`) 
WHERE dctd.`transaction` LIKE '%transfer%' AND dctd.`transaction` = 'Transfer Out'
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';






insert into bulk_bad_debts(account_number, date) SELECT loanno, bad_debt_date FROM _bad_debts;
create table bulk_goodwills LIKE bulk_bad_debts;
insert into bulk_goodwills(account_number, date) SELECT l.loanno, g.date FROM _goodwill g LEFT JOIN _loan l ON g.loanid = l.loanid;



-- slow
update disbursements d SET Remarks = (SELECT loanremark FROM _loan WHERE loanno = d.account_number AND hidden = 0 AND ABS(loanamount) > 0) WHERE Remarks = '';


update repayments r LEFT JOIN disbursements d ON r.disbursement_id = d.id SET r.remarks = (SELECT bad_debt_remarks FROM _bad_debts WHERE loanno = d.account_number) WHERE r.remarks = '' AND r.book_id = (SELECT id FROM books WHERE name = 'Bad Debt Book');






-- slow
update disbursements SET imported_LonAppNo = (SELECT loan_app_no FROM _loan WHERE loanno = account_number AND hidden = 0 AND ABS(loanamount) > 0);

delete from `options` where name IN('application_title', 'terms_and_conditions', 'lender_name', 'registration_number', 'reference_number', 'license_number', 'address1', 'address2', 'city', 'state', 'country', 'postalcode', 'service_phone', 'hand_phone', 'other_phone', 'fax', 'email', 'blk', 'level', 'unit', 'contact_person', 'designation', 'opening_date');

INSERT INTO `options` (name, value) VALUES('lender_name', (SELECT companyname FROM _company));
INSERT INTO `options` (name, value) VALUES('application_title', (SELECT companyname FROM _company));
INSERT INTO `options` (name, value) VALUES('reference_number', (SELECT referenceno FROM _company));
INSERT INTO `options` (name, value) VALUES('license_number', (SELECT licenceno FROM _company));
INSERT INTO `options` (name, value) VALUES('address1', (SELECT companyaddress FROM _company));
-- INSERT INTO `options` (name, value) VALUES('address2', (SELECT IFNULL(Address2, '') FROM _company));

-- INSERT INTO `options` (name, value) VALUES('city', (SELECT IFNULL(City, '') FROM _company));
-- INSERT INTO `options` (name, value) VALUES('state', (SELECT IFNULL(State, '') FROM _company));
-- INSERT INTO `options` (name, value) VALUES('country', (SELECT IFNULL(Country, '') FROM _company));
INSERT INTO `options` (name, value) VALUES('postalcode', (SELECT postalcode FROM _company));

-- INSERT INTO `options` (name, value) VALUES('service_phone', (SELECT IFNULL(ServicePhone, '') FROM _company));
INSERT INTO `options` (name, value) VALUES('hand_phone', (SELECT phoneno FROM _company));
-- INSERT INTO `options` (name, value) VALUES('other_phone', (SELECT IFNULL(OtherPhone, '') FROM _company));
-- INSERT INTO `options` (name, value) VALUES('fax', (SELECT IFNULL(Fax, '') FROM _company));

-- INSERT INTO `options` (name, value) VALUES('email', (SELECT IFNULL(EMailID, '') FROM _company));
INSERT INTO `options` (name, value) VALUES('blk', (SELECT blk FROM _company));
-- INSERT INTO `options` (name, value) VALUES('level', (SELECT IFNULL(LevelNo, '') FROM _company));
INSERT INTO `options` (name, value) VALUES('unit', (SELECT unit FROM _company));
INSERT INTO `options` (name, value) VALUES('contact_person', (SELECT licensee FROM _company));
INSERT INTO `options` (name, value) VALUES('designation', (SELECT designation FROM _company));



update borrowers set email = '' where email = 'address@hostname.com';

update disbursements SET late_fee = 60 WHERE late_fee = 0 AND date >= '2015-10-01';

ALTER TABLE `_chat_list` ADD INDEX(`loanid`);
insert into calls(date, remarks, application_id, disbursement_id) SELECT cl.date, cl.remark, d.application_id, d.id FROM _chat_list cl LEFT JOIN _loan l ON cl.loanid = l.loanid LEFT JOIN disbursements d ON d.account_number = l.loanno;






-- SELECT v.id, v.parent_voucher_id, v.date, v.amount, a.id, a.transactiondate, v.narration, a.payer, a.ledgercode, a.amount, a.remarks FROM vouchers v LEFT JOIN _accounts a ON v.amount = ABS(a.amount) AND a.transactiondate = v.date AND a.remarks = v.narration WHERE a.id IS NOT NULL AND entry_mode = 'T' GROUP BY v.parent_voucher_id;
-- SELECT v.id, a.payer FROM vouchers v LEFT JOIN _accounts a ON v.amount = ABS(a.amount) AND a.transactiondate = v.date AND a.remarks = v.narration WHERE a.id IS NOT NULL AND entry_mode = 'T' GROUP BY v.parent_voucher_id;

-- book transfer particulars
update vouchers v LEFT JOIN (SELECT v.id, a.payer FROM vouchers v LEFT JOIN _accounts a ON v.amount = ABS(a.amount) AND a.transactiondate = v.date AND a.remarks = v.narration WHERE a.id IS NOT NULL AND entry_mode = 'T' GROUP BY v.parent_voucher_id) as a ON v.id = a.id SET v.particulars = a.payer WHERE a.payer IS NOT NULL AND v.particulars = '';

-- payment particulars
update vouchers v LEFT JOIN (SELECT v.id, a.payer FROM vouchers v LEFT JOIN _accounts a ON v.amount = ABS(a.amount) AND a.transactiondate = v.date AND a.remarks = v.narration WHERE a.id IS NOT NULL AND entry_mode = 'P') as a ON v.id = a.id SET v.particulars = a.payer WHERE a.payer IS NOT NULL AND v.particulars = '';

-- receipt particulars
update vouchers v LEFT JOIN (SELECT v.id, a.payer FROM vouchers v LEFT JOIN _accounts a ON v.amount = ABS(a.amount) AND a.transactiondate = v.date AND a.remarks = v.narration WHERE a.id IS NOT NULL AND entry_mode = 'R') as a ON v.id = a.id SET v.particulars = a.payer WHERE a.payer IS NOT NULL AND v.particulars = '';




