/* Query for TK147272 to fix incorrectly setup Bank Account with GBP currency */
UPDATE `organisation_bank_account_details` SET `account_name` = 'Greenspan System Sales Ireland Ltd', `account_no` = '', `sort_code` = '', `iban_code` = 'GB47AIBK23859014838058', `swift_code` = 'AIBKGB41UND', `organisation_id` = '221' WHERE `organisation_bank_account_details`.`account_id` = 245;
UPDATE `contact_details` SET `organisation_id` = '221', `bank_id` = '3' WHERE `contact_details`.`contact_id` = 584;
INSERT INTO organisation_bank_details (organisation_bank_id, organisation_id, address_id, bank_id, contact_id, currency_id) 
VALUES (245, 221, 2863, 3, 584, 2);


/*  Test query to verify the mapping results
SELECT 
org.*,
b.bank_name, 
c.last_name, 
c.first_name, 
c.work_address_id, 
c.contact_id, 
IFNULL(bac.account_name, d.account_name) as account_name, 
IFNULL(bac.account_no, d.account_no) as account_no, 
IFNULL(bac.sort_code, d.sort_code) as sort_code, 
IFNULL(bac.iban_code, d.iban_code) as iban_code, 
IFNULL(bac.swift_code, d.swift_code) as swift_code, 
a.*, 
c2.country_name, 
a2.address_phone_country as contact_phone_country, 
a2.address_phone_code as contact_phone_code, 
a2.address_phone as contact_phone, 
a2.address_email as contact_email, 
d.account_id, 
IFNULL(c3.currency_code, '-') as currency_code 
FROM organisation_bank_details org 
	LEFT JOIN bank_details as b ON b.bank_id = org.bank_id 
	LEFT JOIN organisation_currencies ON organisation_currencies.currency_id = org.currency_id 
		AND organisation_currencies.organisation_id = org.organisation_id 
	LEFT JOIN currencies as c3 ON c3.currency_id = org.currency_id 
	LEFT JOIN contact_details as c ON c.contact_id = org.contact_id AND c.position = 1 
	LEFT JOIN organisation_bank_account_details as d 
		ON org.organisation_bank_id = d.organisation_bank_id  
	LEFT JOIN bank_account_change_request as bac ON bac.account_id = d.account_id AND d.change = 1 
	LEFT JOIN address_details as a ON a.address_id = org.address_id 
	LEFT JOIN country_mas as c2 ON c2.country_id = a.address_country 
	LEFT JOIN address_details as a2 ON a2.address_id = c.work_address_id 
WHERE org.organisation_id = 221 
AND c3.currency_code = 'GBP';
*/

/* report query to check bank currency mappings against the legacy organisation_currencies table data
SELECT 
	org.organisation_reference_id as exchange_ref_id, 
    TRIM(org.organisation_name) as organisation_name,
    TRIM(org.organisation_trade_name) as trade_name,
	oc.account_id as oc_account_id,
	oc.organisation_id as oc_organisation_id,
	oc.currency_id as oc_currency_id,
	oc.iban_code as oc_iban_code,
	oc.swift_code as oc_swift_code, 
	IFNULL(bac.iban_code, d.iban_code) as iban_code, 
	IFNULL(bac.swift_code, d.swift_code) as swift_code 
FROM 
	organisation_currencies oc 
LEFT JOIN organisation_details org 
		ON org.organisation_id = oc.organisation_id 
LEFT JOIN organisation_bank_details obd 
		ON obd.organisation_id = oc.organisation_id AND obd.currency_id = oc.currency_id 
LEFT JOIN organisation_bank_account_details as d 
		ON d.organisation_bank_id = obd.organisation_bank_id 
LEFT JOIN bank_account_change_request as bac 
		ON bac.account_id = d.account_id AND d.change = 1 
*/