/* queries to import credebt machine record ids into dedicated tables in Exchange 2.0 from a previously created backup */
/* first, import data from the sql backup file by executing the following command:
mysql -u root -p exchange < credebt-machine-ids-2022-10-23-backup.sql
*/

/* import IDs for originator, intermediary and investor records */
UPDATE 
	temp_registration t1, 
	temp_registration_cmids t2 
SET 
     t1.cm_sync_date = t2.cm_sync_date,
	 t1.cm_id = t2.cm_id 
WHERE
	t1.temp_reg_id = t2.temp_reg_id;
	
/* import IDs for debtor and creditor records */
UPDATE 
	debtors_detail t1, 
	debtors_detail_cmids t2 
SET 
     t1.cm_sync_date = t2.cm_sync_date,
	 t1.cm_id = t2.cm_id 
WHERE
	t1.debtor_id = t2.debtor_id;
	
/* import IDs for address records */
UPDATE 
	address_details t1, 
	address_details_cmids t2 
SET 
     t1.cm_sync_date = t2.cm_sync_date,
	 t1.cm_address_id = t2.cm_address_id,
	 t1.cm_phone_id = t2.cm_phone_id,
	 t1.cm_mobile_id = t2.cm_mobile_id,
	 t1.cm_fax_id = t2.cm_fax_id,
	 t1.cm_email_id = t2.cm_email_id 
WHERE
	t1.address_id = t2.address_id;

/* import IDs for originator, intermediary and investor bank account records */
UPDATE 
	organisation_bank_account_details t1, 
	organisation_bank_account_details_cmids t2 
SET 
     t1.cm_sync_date = t2.cm_sync_date,
	 t1.cm_account_id = t2.cm_account_id 
WHERE
	t1.account_id = t2.account_id;

/* import IDs for creditor bank account records */
UPDATE 
	debtor_bank_details t1, 
	debtor_bank_details_cmids t2 
SET 
     t1.cm_sync_date = t2.cm_sync_date,
	 t1.cm_account_id = t2.cm_account_id 
WHERE
	t1.debtor_bank_id = t2.debtor_bank_id;
	
/* import IDs for b/c/d-ETR Trade Invoice Transaction records */
UPDATE 
	invoice_master t1, 
	invoice_master_cmids t2 
SET 
     t1.cm_sync_date = t2.cm_sync_date,
	 t1.cm_id = t2.cm_id 
WHERE
	t1.invoice_id = t2.invoice_id;
	
/* import IDs for non-invoice Trade Transaction records */
UPDATE 
	manual_transactions t1, 
	manual_transactions_cmids t2 
SET 
     t1.cm_sync_date = t2.cm_sync_date,
	 t1.cm_id = t2.cm_id 
WHERE
	t1.manual_transaction_id = t2.manual_transaction_id;
	
/* import IDs for Investor Transaction records */
UPDATE 
	investor_investments t1, 
	investor_investments_cmids t2 
SET 
     t1.cm_sync_date = t2.cm_sync_date,
	 t1.cm_id = t2.cm_id,
	 t1.cm_id_investment = t2.cm_id_investment 
WHERE
	t1.investment_id = t2.investment_id;
	
DROP TABLE IF EXISTS temp_registration_cmids;
DROP TABLE IF EXISTS debtors_detail_cmids;
DROP TABLE IF EXISTS address_details_cmids;
DROP TABLE IF EXISTS organisation_bank_account_details_cmids;
DROP TABLE IF EXISTS debtor_bank_details_cmids;
DROP TABLE IF EXISTS invoice_master_cmids;
DROP TABLE IF EXISTS manual_transactions_cmids;
DROP TABLE IF EXISTS investor_investments_cmids;