
-- ---------------
-- merge borrowers
-- ---------------

-- fields: id, ledger_id, name, borrower_type_id, blacklisted, blacklist_date, 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, borrower_remarks, bank_account_number, home_status, home_status_others, giro_bank, giro_bank_id, giro_branch, giro_account_number, giro_approval_date, dda_number, gender, marital_status, site_company, site_address, site_phone, rating, business_pincode, property_ownership

-- check for dupes
-- select * from borrowers nb LEFT JOIN projects_loan_rml_old.borrowers ob ON nb.uid = ob.uid WHERE ob.uid IS NOT NULL;
insert into project_loan.borrowers SELECT NULL, '', name, borrower_type_id, blacklisted, blacklist_date, 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, borrower_remarks, bank_account_number, home_status, home_status_others, giro_bank, giro_bank_id, giro_branch, giro_account_number, giro_approval_date, dda_number, gender, marital_status, site_company, site_address, site_phone, rating, business_pincode, property_ownership FROM projects_loan_rml_old.borrowers;










-- merge applications
-- fields: id, 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
insert into project_loan.applications SELECT NULL, account_number, date, LenderId, (SELECT id FROM project_loan.borrowers WHERE uid = (SELECT uid FROM projects_loan_rml_old.borrowers WHERE id = a.borrower_id)), amount, surety_id, (SELECT id FROM borrowers WHERE uid = (SELECT uid FROM borrowers WHERE id = a.new_surety_id)), loan_purpose_id, loan_type_id, SecureType, SecureValue, SecureInfo, Remarks, loan_status_id, loan_status_date, Purpose, BrokerId, agent_id, (SELECT id FROM project_loan.vt_users WHERE username = 'rml_old'), mlas_submission, dp_ref FROM projects_loan_rml_old.applications a;






-- merge disbursements
-- fields: id, voucher_number, date, application_id, mlcb_application_id, account_number, account_number_prefix, account_number_suffix, amount, PaymentMode, PMDate, transaction_number, transaction_number1, PMBankName, interest_rate, interest_frequency_id, InterestFrom, eir, irr, GracePeriod, GraceType, interest_calculation_method_id, installment_count, FreqencyNo, payment_frequency_id, ModeOfPayment, OverPayment, late_interest_rate, first_payment_date, second_payment_date, last_payment_date, Remarks, LenderId, Approved, status, payment_type_id, EMICal, book_id, Rest, MailAddress, language_code, terms_and_conditions, book_amount, book_amount1, book_id1, payment_type_id1, voucher_number1, PMDate1, PMTransNo1, PMBankName1, deprecated_agent_principal_share, deprecated_agent_interest_share, deprecated_agent_late_fee_share, deprecated_agent_late_interest_share, 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, CollectionFees_unknown, CollectionFeesP_unknown, late_repayment_fee_percent, borrower_annual_income, security_details, un_search_remarks, un_search_datetime, borrower_bank_account_number, giro_bank, giro_bank_id, giro_branch, giro_account_number, giro_approval_date, dda_number, giro_approval_time
insert into project_loan.disbursements SELECT NULL, '', date, (SELECT id FROM project_loan.applications WHERE account_number = d.account_number), mlcb_application_id, account_number, account_number_prefix, account_number_suffix, amount, PaymentMode, PMDate, transaction_number, transaction_number1, PMBankName, interest_rate, interest_frequency_id, InterestFrom, eir, irr, GracePeriod, GraceType, interest_calculation_method_id, installment_count, FreqencyNo, payment_frequency_id, ModeOfPayment, OverPayment, late_interest_rate, first_payment_date, second_payment_date, last_payment_date, Remarks, LenderId, Approved, status, payment_type_id, EMICal, book_id, Rest, MailAddress, language_code, terms_and_conditions, book_amount, book_amount1, book_id1, payment_type_id1, '', PMDate1, PMTransNo1, PMBankName1, deprecated_agent_principal_share, deprecated_agent_interest_share, deprecated_agent_late_fee_share, deprecated_agent_late_interest_share, 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, CollectionFees_unknown, CollectionFeesP_unknown, late_repayment_fee_percent, borrower_annual_income, security_details, un_search_remarks, un_search_datetime, borrower_bank_account_number, giro_bank, giro_bank_id, giro_branch, giro_account_number, giro_approval_date, dda_number, giro_approval_time FROM projects_loan_rml_old.disbursements d;










-- merge repayments
-- fields: id, disbursement_id, application_id, account_number, date, voucher_number, 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, compound_interest, LenderID, RegularPrincipal, RegularInterest, AmountPrincipal, late_interest, receipt_number, outstanding_principal, NoofLateDays, NoofLatePayment, cheque_dishonour, payment_type_id, book_id, CollectionFees
insert into project_loan.repayments SELECT NULL, (SELECT id FROM project_loan.disbursements WHERE account_number = r.account_number), (SELECT id FROM project_loan.applications WHERE account_number = r.account_number), 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, compound_interest, LenderID, RegularPrincipal, RegularInterest, AmountPrincipal, late_interest, receipt_number, outstanding_principal, NoofLateDays, NoofLatePayment, cheque_dishonour, payment_type_id, book_id, CollectionFees FROM projects_loan_rml_old.repayments r;










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







