insert ignore into projects_loan_gt_old.borrowers(`name`, `borrower_type_id`, `blacklisted`, `blacklist_reason_id`, `country_id`, `uid`, `annual_income`, `dob`, `identification_type_id`, `address1`, `address1b`, `address2`, `city`, `state`, `country`, `pincode`, `work_phone`, `home_phone`, `hand_phone`, `sms_phone`, `email`, `alternate_email`, `blk`, `level`, `unit`, `street`, `building`, `company`, `designation`, `sms_subscription`, `image`, `nok_name`, `nok_contact`, `nok_relation`, `remarks`) SELECT `name`, `borrower_type_id`, `blacklisted`, `blacklist_reason_id`, `country_id`, `uid`, `annual_income`, `dob`, `identification_type_id`, `address1`, `address1b`, `address2`, `city`, `state`, `country`, `pincode`, `work_phone`, `home_phone`, `hand_phone`, `sms_phone`, `email`, `alternate_email`, `blk`, `level`, `unit`, `street`, `building`, `company`, `designation`, `sms_subscription`, `image`, `nok_name`, `nok_contact`, `nok_relation`, `remarks` FROM projects_loan_gt_new.borrowers;

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 projects_loan_gt_old.applications(`account_number`, `date`, `LenderId`, `borrower_id`, `amount`, `surety_id`, `new_surety_id`, `loan_purpose_id`, `loan_type_id`, `SecureType`, `SecureValue`, `SecureInfo`, `Remarks`, `loan_status_id`, `loan_status_date`, `Purpose`, `BrokerId`, `agent_id`, `vt_user_id`, `mlas_submission`, `dp_ref`) SELECT `account_number`, `date`, `LenderId`, (SELECT id FROM borrowers WHERE uid = b.uid ORDER BY id ASC LIMIT 1) as borrower_id, `amount`, `surety_id`, `new_surety_id`, `loan_purpose_id`, `loan_type_id`, `SecureType`, `SecureValue`, `SecureInfo`, ap.`Remarks`, `loan_status_id`, `loan_status_date`, `Purpose`, `BrokerId`, `agent_id`, (SELECT id FROM projects_loan_gt_old.vt_users WHERE first_name = (SELECT first_name FROM projects_loan_gt_new.vt_users WHERE id = ap.vt_user_id)) as vt_user_id, `mlas_submission`, `dp_ref` FROM projects_loan_gt_new.applications ap LEFT JOIN projects_loan_gt_new.borrowers b ON ap.borrower_id = b.id WHERE ap.date >= '2013-04-16';

insert into projects_loan_gt_old.disbursements(`date`, `application_id`, `account_number`, `account_number_prefix`, `account_number_suffix`, `amount`, `transaction_number`, `interest_rate`, `interest_frequency_id`, `eir`, `irr`, `interest_calculation_method_id`, `installment_count`, `payment_frequency_id`, `late_interest_rate`, `first_payment_date`, `second_payment_date`, `last_payment_date`, `Remarks`, `LenderId`, `Approved`, `status`, `payment_type_id`, `book_id`, `Rest`, `language_code`, `terms_and_conditions`, `book_amount`, `book_amount1`, `book_id1`, `payment_type_id1`, `voucher_number1`, `acceptance_fee`, `late_repayment_fee`, `legal_fee`, `renewal_fee`, `contract_variation_fee`, `cheque_dishonour_fee`, `termination_fee`, `acceptance_fee_percent`, `renewal_fee_percent`, `late_fee`, `late_fee_percent`, `contract_variation_fee_percent`, `cheque_dishonour_fee_percent`, `termination_fee_percent`, `legal_fee_percent`, `late_repayment_fee_percent`, `borrower_annual_income`) SELECT d.`date`, (SELECT id FROM projects_loan_gt_old.applications WHERE account_number = d.account_number) as application_id , d.`account_number`, `account_number_prefix`, `account_number_suffix`, d.`amount`, `transaction_number`, `interest_rate`, `interest_frequency_id`, `eir`, `irr`, `interest_calculation_method_id`, `installment_count`, `payment_frequency_id`, `late_interest_rate`, `first_payment_date`, `second_payment_date`, `last_payment_date`, d.`Remarks`, d.`LenderId`, `Approved`, `status`, `payment_type_id`, `book_id`, `Rest`, `language_code`, `terms_and_conditions`, `book_amount`, `book_amount1`, `book_id1`, `payment_type_id1`, `voucher_number1`, `acceptance_fee`, `late_repayment_fee`, `legal_fee`, `renewal_fee`, `contract_variation_fee`, `cheque_dishonour_fee`, `termination_fee`, `acceptance_fee_percent`, `renewal_fee_percent`, `late_fee`, `late_fee_percent`, `contract_variation_fee_percent`, `cheque_dishonour_fee_percent`, `termination_fee_percent`, `legal_fee_percent`, `late_repayment_fee_percent`, `borrower_annual_income` FROM projects_loan_gt_new.disbursements d LEFT JOIN projects_loan_gt_new.applications ap ON d.application_id = ap.id WHERE ap.date >= '2013-04-16';

insert into projects_loan_gt_old.payment_schedule(application_id, disbursement_id, date, amount, principal, interest, late_fee, legal_fee, renewal_fee, contract_variation_fee, cheque_dishonour_fee, termination_fee, google_calendar_url, skip) SELECT (SELECT id FROM projects_loan_gt_old.applications WHERE account_number = ap.account_number) as application_id, (SELECT id FROM projects_loan_gt_old.disbursements WHERE account_number = ap.account_number) as disbursement_id, ps.date, ps.amount, ps.principal, ps.interest, ps.late_fee, ps.legal_fee, ps.renewal_fee, ps.contract_variation_fee, ps.cheque_dishonour_fee, ps.termination_fee, ps.google_calendar_url, ps.skip FROM projects_loan_gt_new.payment_schedule ps LEFT JOIN projects_loan_gt_new.applications ap ON ps.application_id = ap.id WHERE ap.date >= '2013-04-16';


insert into projects_loan_gt_old.repayments(`disbursement_id`, `application_id`, `account_number`, `date`, `payer_type_id`, `other_payer_name`, `other_payer_address`, `other_payer_uid`, `amount`, `PaymentMode`, `PayDetDate`, `transaction_number`, `PayDetBank`, `remarks`, `RegularPay`, `paid_principal`, `paid_interest`, `PrincipalAdj`, `InterestAdj`, `AmountAdj`, `permit_fee`, `acceptance_fee`, `renewal_fee`, `late_fee`, `contract_variation_fee`, `cheque_dishonoured_fee`, `termination_fee`, `legal_fee`, `principal`, `interest`, `LenderID`, `RegularPrincipal`, `RegularInterest`, `AmountPrincipal`, `late_interest`, `receipt_number`, `outstanding_principal`, `NoofLateDays`, `NoofLatePayment`, `cheque_dishonour`, `payment_type_id`, `book_id`, `CollectionFees`) SELECT (SELECT id FROM projects_loan_gt_old.disbursements WHERE TRIM(LEADING '0' FROM account_number) = TRIM(LEADING '0' FROM r.account_number)) as disbursement_id, (SELECT id FROM projects_loan_gt_old.applications WHERE TRIM(LEADING '0' FROM account_number) = TRIM(LEADING '0' FROM r.account_number)) as application_id, TRIM(LEADING '0' FROM r.`account_number`), r.`date`, `payer_type_id`, `other_payer_name`, `other_payer_address`, `other_payer_uid`, r.`amount`, r.`PaymentMode`, `PayDetDate`, r.`transaction_number`, `PayDetBank`, r.`remarks`, `RegularPay`, `paid_principal`, `paid_interest`, `PrincipalAdj`, `InterestAdj`, `AmountAdj`, `permit_fee`, r.`acceptance_fee`, r.`renewal_fee`, r.`late_fee`, r.`contract_variation_fee`, r.`cheque_dishonoured_fee`, r.`termination_fee`, r.`legal_fee`, r.`principal`, r.`interest`, r.`LenderID`, `RegularPrincipal`, `RegularInterest`, `AmountPrincipal`, `late_interest`, `receipt_number`, `outstanding_principal`, `NoofLateDays`, `NoofLatePayment`, `cheque_dishonour`, r.`payment_type_id`, r.`book_id`, `CollectionFees` FROM projects_loan_gt_new.repayments r WHERE r.date >= '2013-04-16' HAVING application_id IS NOT NULL;



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 WHERE date >= '2013-04-16';

-- 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 >= '2013-04-16';

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 >= '2013-04-16';

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 >= '2013-04-16';

-- 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 >= '2013-04-16';

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 >= '2013-04-16';

-- 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 >= '2013-04-16';

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 >= '2013-04-16';

