/* Originators */
SELECT od.organisation_name                              as 'Name',
       od.organisation_trade_name                        as 'Trade Name',
	   od.organisation_reference_id                      as 'Exchange Ref. Id',
       od.organisation_id                                as 'ShortId',
       oc.account_name                                   as 'BankName',
       CONCAT('\'', oc.iban_code)                        as 'Acc Number',
       oc.swift_code									 as 'SWIFT',
       c.currency_code                                   as 'Currency',
       od.tax_number			                         as 'Registration Number',
       od.vat_number			                         as 'VAT Numb',
       CONCAT(ad.address_line_one, ' ', ad.address_line_two) as 'Street',
       ad.address_city								 as 'City',
       ad.address_zipcode							 as 'Post code',
       ad.address_state								 as 'State',
       cm.country_name								 as 'Country',
       CONCAT(ad.address_phone_country,ad.address_phone_code,ad.address_phone) as 'Phone number',
       CONCAT('\'', LPAD(od.organisation_id, 5, 0))      as 'Spool Id Dec',
       CONCAT('\'', LPAD(HEX(od.organisation_id), 4, 0)) as 'Spool Id Hex',
       'Originator'										 as 'Role',
       '0'												 as 'Parent Id' 
FROM organisation_details od
         JOIN temp_registration tr
              ON tr.user_id = od.user_id
         JOIN organisation_currencies oc ON oc.organisation_id = od.organisation_id
         JOIN currencies c ON c.currency_id = oc.currency_id
         LEFT JOIN address_details ad ON ad.address_id = od.mail_address_id
         LEFT JOIN country_mas cm ON cm.country_id = ad.address_country
WHERE tr.user_type IN (3, 4)
  AND tr.status <> 6
ORDER BY od.organisation_id, c.currency_id;

/* Credebtors */
SELECT dd.debtor_name                                                                   as 'Name',
	   dd.trade_name                                                                    as 'Trade Name',
       dd.debtor_reference_id                                                           as 'Organization Id',
	   dd.debtor_id                                                                     as 'ShortId',
       dd.debtor_name                                                                   as 'BankName',
       IF(dbd.iban_code = '' OR dbd.iban_code IS NULL, '', CONCAT('\'', dbd.iban_code)) as 'Acc Number',
       IF(dbd.swift_code = '' OR dbd.swift_code IS NULL, '', dbd.swift_code) 			as 'SWIFT',
       c.currency_code                                                                  as 'Currency',
       dd.debtor_company_registration_number											as 'Registration Number',
       ''																				as 'VAT Numb',
       CONCAT(ad.address_line_one,' ',ad.address_line_two)				as 'Street',
       ad.address_city																as 'City',
       ad.address_zipcode															as 'Post code',
       ad.address_state																as 'State',
       cm.country_name																as 'Country',
       CONCAT(ad.address_phone_country, ad.address_phone_code,ad.address_phone) as 'Phone number',
       ''																				as 'Spool Id Dec',
       ''																				as 'Spool Id Hex',
       IF(dd.type = 'debtor', 'Debtor', 'Creditor')							 			as 'Role',
       od.organisation_reference_id                                                     as 'Parent Id'
FROM debtors_detail dd
         JOIN debtor_relation dr ON dr.debtor_id = dd.debtor_id
         LEFT JOIN organisation_details od ON od.user_id = dr.trader_id
         JOIN currencies c ON c.currency_id = dd.currency_id
         LEFT JOIN debtor_bank_details dbd ON dbd.debtor_id = dd.debtor_id
         LEFT JOIN address_details ad ON ad.address_id = dd.address_id
         LEFT JOIN country_mas cm ON cm.country_id = ad.address_country
ORDER BY od.organisation_id, dd.debtor_id;

/* Intermediaries */
SELECT od.organisation_name                   as 'Name',
	   ''									  as 'Trade Name',
       tr.ref_id                              as 'Organization Id',
	   od.organisation_id                     as 'ShortId',
       od.organisation_name                   as 'BankName',
       IFNULL(bacr.iban_code, obad.iban_code) as 'Acc Number',
       IFNULL(bacr.swift_code, obad.swift_code) as 'SWIFT',
       'EUR'                                  as 'Currency',
       od.tax_number	                      as 'Registration Number',
       od.vat_number	                      as 'VAT Numb',
       CONCAT(ad.address_line_one,' ',ad.address_line_two) as 'Street',
       ad.address_city					  as 'City',
       ad.address_zipcode				  as 'Post code',
       ad.address_state					  as 'State',
       cm.country_name					  as 'Country',
       CONCAT(ad.address_phone_country,ad.address_phone_code,ad.address_phone) as 'Phone number',
       ''									  as 'Spool Id Dec',
       ''									  as 'Spool Id Hex',
       'Intermediary'						  as 'Role',
       '0'									  as 'Parent Id'
FROM organisation_details od
         JOIN temp_registration tr ON tr.user_id = od.user_id
         LEFT JOIN organisation_bank_account_details obad ON obad.organisation_id = od.organisation_id
         LEFT JOIN bank_account_change_request bacr ON bacr.account_id = obad.account_id AND obad.`change` = 1
         LEFT JOIN address_details ad ON ad.address_id = tr.organization_address_id
         LEFT JOIN country_mas cm ON cm.country_id = ad.address_country
WHERE tr.user_type = 5
order by od.organisation_id;

/* Investors */
/*--------- VALID ------------*/
SELECT 
		tr2.ref_id              as "Ex. 2.0 Related Org ID",
		IF(od_i.organisation_name = '', "Credebt Exchange Limited", od_i.organisation_name) 
		                        as "Ex. 2.0 Related Org",
		IF(tr.register_type = '1', "Organisation","People") 
		                        as "Ex. 2.0 Type",
		IF(tr2.ref_id = '','Ledger Owner','Intermediary') 
		                        as 'Ex. 2.0 Related Role', 
		"Investor"              as "Ex. 2.0 Role",
		tr.ref_id               as 'Ex. 2.0 LID',
	    od_r.organisation_id    as 'Ex. 2.0 SID',
		""                      as "Salutation",
		IF(tr.register_type = '1', "",
			GROUP_CONCAT(cd.first_name SEPARATOR ' & ')) as "First Name",
		IF(tr.register_type = '1', "",
			cd.last_name)      as "Last Name",
		""					   as "Full Name",
		IF(tr.register_type = '1', tr.organization_name, "") 
		                       as "Organisation Name", 
		""                     as "Title",
		cd.birth_date          as "Date of Birth",
		cm.country_name	       as 'Registration Country',
       ''                      as 'Registration Number',
       ''                      as 'VAT Number',
	   CONCAT(ad.address_phone_country,ad.address_phone_code,ad.address_phone) 
	                           as 'Phone',
	   ""                      as "Mobile",
	   ad.address_email        as "Email",
	   ""                      as "Former last name",
	   ""                      as "Notes",
	   ""                      as "PPS Number",
	   ""                      as "PPSN Document",
	   ""                      as "Photo URL",
	   ""                      as "Pronounced",
	   "Billing"               as "Address Type",
	   ad.address_line_one     as 'Address_1',
	   ad.address_line_two     as "Address_2",
	   ""                      as "Address_3",
	   ad.address_city	       as 'City',
	   ad.address_state        as 'State/County',
       ad.address_zipcode      as 'Postal Code',
       cm.country_name	       as 'Country',
	   ""                      as "CRM ID",
	   "New"                   as "Status",
	   "EUR"                   as "People Currency",
       /*IF(tr.register_type = '1', tr.organization_name,
          GROUP_CONCAT(DISTINCT CONCAT(cd.first_name, ' ', cd.last_name) SEPARATOR
	                       ' & '))						   
						       as 'Bank Account Name',*/
	   IF(bank_names.bank_name = "","Other",bank_names.bank_name) as "Bank Name",
	   "Current"			   as "Bank Account Type",
	   account_name			   as 'Bank Account Name',
	   account_no			   as 'Account Number',
	   sort_code			   as 'Sort Code',
       replace(acc.iban_code,' ','') as 'IBAN',
       replace(acc.swift_code,' ','') as 'Swift/BIC Code',
       ''					   as 'Spool Id Dec',
       ''					   as 'Spool Id Hex' 
       /*od_i.organisation_id    as 'Intermediary short ID',*/
FROM temp_registration tr
         LEFT JOIN organisation_details od_r ON od_r.user_id = tr.user_id
         LEFT JOIN investor_relation ir ON ir.investor_id = tr.temp_reg_id
         LEFT JOIN organisation_details od_i ON od_i.user_id = ir.intermediary_id
         LEFT JOIN temp_registration tr2 ON tr2.user_id = od_i.user_id
         LEFT JOIN contact_details cd ON cd.temp_reg_id = tr.temp_reg_id AND
                                         (cd.contact_flag = 3 OR (cd.contact_flag = 0 AND cd.bank_id = 0))
         LEFT JOIN address_details ad ON ad.address_id = cd.work_address_id
         LEFT JOIN country_mas cm ON cm.country_id = ad.address_country 
		 LEFT JOIN contact_details cb ON tr.temp_reg_id = cb.temp_reg_id AND cb.position = 1
		 LEFT JOIN organisation_bank_details bank ON cb.contact_id = bank.contact_id 
		 LEFT JOIN organisation_bank_account_details acc
                   ON acc.status = 0 AND bank.organisation_bank_id = acc.organisation_bank_id
		 LEFT JOIN bank_details bank_names ON bank_names.bank_id = bank.bank_id 
WHERE tr.user_type = 6
  AND tr.status IN (2, 4, 5, 6)
GROUp BY od_r.user_id
ORDER BY od_r.organisation_id;


/* separate */
SELECT b.ref_id as 'Organization Id', acc.iban_code as 'Acc Number', acc.swift_code as 'SWIFT' 
FROM temp_registration b
         LEFT JOIN contact_details c ON b.temp_reg_id = c.temp_reg_id AND c.position = 1
         LEFT JOIN organisation_bank_details bank ON c.contact_id = bank.contact_id
         LEFT JOIN organisation_bank_account_details acc
                   ON acc.status = 0 AND bank.organisation_bank_id = acc.organisation_bank_id
WHERE b.user_type = 6