/*-----------------------------------------*/
/* Export All Credebtors from Exchange 2.0 */
/*-----------------------------------------*/
/*--------- VALID ------------*/
SELECT 
org.organisation_reference_id as "Ex. 2.0 Related Org ID",
org.organisation_name as "Ex. 2.0 Related Org",
"Originator" as "Ex. 2.0 Related Role",
IF(d.type="debtor","Debtor","Creditor") as "Ex. 2.0 Role",
d.debtor_id as "Ex. 2.0 SID",
d.debtor_reference_id as "Ex. 2.0 LID",
dm.debtor_master_id as "Ex. 2.0 Master SID",
dm.master_reference_id as "Ex. 2.0 Master LID",
cat.nace_code as "Ex. 2.0 NACE Code",
dm.debtor_name as "Parent Organisation",
d.debtor_name as "Organisation Name",
d.trade_name as "Trade Name",
cntry.country_name as "Registration Country",
d.debtor_company_registration_number as "Registration Number",
"" as "Vat Number",
"" as "FY Start Month",
"" as "Website",
d.phone1 as "Phone",
"Billing" as "Address Type",
addr.address_line_one as "Address_1",
addr.address_line_two as "Address_2",
"" as "Address_3",
addr.address_city as "City",
addr.address_state as "State/County",
addr.address_zipcode as "Postal Code",
cntry.country_name as "Country",
"" as "CRM ID",
"" as "Logo URL",
"New" as "Status",
c.currency_code as "Organisation Currency Code",
"" as "Organisation Description",
"" as "Industry",
CONCAT(cat.nace_code, ' - ', cat.nace_title) AS "NACE Code",
d.customer_since as "Counterparty Since",
d.no_credit_note_issued as "Credit Notes Issued",
d.average_payment_cycle as "DSO",
d.avg_invoice_predicted_annum as "Invoices Predicted Per Annum",
d.avg_revenue_predicted_annum as "Predicted Annual Revenue",
IF(d.founded!="",YEAR(founded),"") as "Year of Registration" 
FROM `debtor_relation` as r 
LEFT JOIN debtors_detail as d ON d.debtor_id = r.debtor_id 
LEFT JOIN organisation_details as org ON org.organisation_id = r.organisation_id 
LEFT JOIN debtors_invoices_stat ON debtors_invoices_stat.debtor_id = r.debtor_id 
LEFT JOIN currencies as c ON c.currency_id = d.currency_id 
LEFT JOIN debtors_master as dm ON dm.debtor_master_id = d.parent_id 
LEFT JOIN address_details addr ON addr.address_id = d.address_id
LEFT JOIN country_mas as cntry ON cntry.country_id = addr.address_country
LEFT JOIN categories as cat ON cat.nace_id = d.nace_id 
LEFT JOIN category_group ON category_group.nace_group_id = d.nace_group_id 
LEFT JOIN temp_registration as t ON t.user_id=r.trader_id 
LEFT JOIN user_login as u ON u.user_id=t.user_id 
WHERE 1=1 
group by d.debtor_id ASC;

/*------------------------------------------*/
/* Export All Originators from Exchange 2.0 */
/*------------------------------------------*/
/*--------- VALID ------------*/
SELECT 
"11801001156" as "Ex. 2.0 Related Org ID",
"Credebt Exchange Limited" as "Ex. 2.0 Related Org",
"Ledger Owner" as "Ex. 2.0 Related Role",
"Originator" as "Ex. 2.0 Role",
org.organisation_id as "Ex. 2.0 SID",
org.organisation_reference_id as "Ex. 2.0 LID",
cat.nace_code as "Ex. 2.0 NACE Code",
org.parent_company_name as "Parent Organisation",
org.organisation_name as "Organisation Name",
org.organisation_trade_name as "Trade Name",
cntry.country_name as "Registration Country",
org.tax_number as "Registration Number",
org.vat_number as "Vat Number",
"" as "FY Start Month",
org.website as "Website",
"" as "Phone",
"Billing" as "Address Type",
addr.address_line_one as "Address_1",
addr.address_line_two as "Address_2",
"" as "Address_3",
addr.address_city as "City",
addr.address_state as "State/County",
addr.address_zipcode as "Postal Code",
cntry.country_name as "Country",
"" as "CRM ID",
"" as "Logo URL",
"New" as "Status",
c.currency_code as "Organisation Currency Code",
"" as "Organisation Description",
"" as "Industry",
CONCAT(cat.nace_code, ' - ', cat.nace_title) AS "NACE Code",
"" as "Tax Clearance Access Number",
"" as "Counterparty Since",
"" as "Credit Notes Issued",
"" as "DSO",
"" as "Invoices Predicted Per Annum",
"" as "Predicted Annual Revenue",
IF(org.formation_year!="",org.formation_year,"") as "Year of Registration" 
FROM organisation_details as org 
LEFT JOIN temp_registration tr ON tr.user_id = org.user_id 
LEFT JOIN organisation_bank_details as obd ON obd.organisation_id = org.organisation_id 
/*LEFT JOIN organisation_currencies as occy ON occy.currency_id = obd.currency_id AND occy.organisation_id = obd.organisation_id*/
LEFT JOIN currencies as c ON c.currency_id = obd.currency_id 
LEFT JOIN address_details bil_addr ON bil_addr.address_id = org.billing_address_id 
LEFT JOIN address_details reg_addr ON reg_addr.address_id = org.organisation_regoffice_id 
LEFT JOIN country_mas as cntry ON cntry.country_id = addr.address_country
LEFT JOIN categories as cat ON cat.nace_id = org.nace_id 
LEFT JOIN category_group ON category_group.nace_group_id = org.nace_group_id 
WHERE tr.user_type=3 AND tr.status <> 6 
group by org.organisation_id ASC;

/*---------------------------------------------*/
/* Export All Intermediaries from Exchange 2.0 */
/*---------------------------------------------*/
/*--------- VALID ------------*/
SELECT 
"11801001156" as "Ex. 2.0 Related Org ID",
"Credebt Exchange Limited" as "Ex. 2.0 Related Org",
"Ledger Owner" as "Ex. 2.0 Related Role",
"Intermediary" as "Ex. 2.0 Role",
org.organisation_id as "Ex. 2.0 SID",
tr.ref_id as "Ex. 2.0 LID",
org.organisation_reference_id as "Ex. 2.0 LID #2",
cat.nace_code as "Ex. 2.0 NACE Code",
org.parent_company_name as "Parent Organisation",
org.organisation_name as "Organisation Name",
org.organisation_trade_name as "Trade Name",
cntry.country_name as "Registration Country",
org.tax_number as "Registration Number",
org.vat_number as "Vat Number",
"" as "FY Start Month",
org.website as "Website",
"" as "Phone",
"Billing" as "Address Type",
addr.address_line_one as "Address_1",
addr.address_line_two as "Address_2",
"" as "Address_3",
addr.address_city as "City",
addr.address_state as "State/County",
addr.address_zipcode as "Postal Code",
cntry.country_name as "Country",
"" as "CRM ID",
"" as "Logo URL",
"New" as "Status",
c.currency_code as "Organisation Currency Code",
"" as "Organisation Description",
"" as "Industry",
CONCAT(cat.nace_code, ' - ', cat.nace_title) AS "NACE Code",
/* from here all items are considered as additional information */
"" as "Counterparty Since",
"" as "Credit Notes Issued",
"" as "DSO",
"" as "Invoices Predicted Per Annum",
"" as "Predicted Annual Revenue",
IF(org.formation_year!="",org.formation_year,"") as "Year of Registration" 
FROM organisation_details as org 
LEFT JOIN temp_registration tr ON tr.user_id = org.user_id 
/*LEFT JOIN organisation_bank_details as obd ON obd.organisation_id = org.organisation_id */
LEFT JOIN organisation_currency as occy ON occy.organisation_id = org.organisation_id AND occy.is_main=1 
LEFT JOIN currencies as c ON c.currency_id = occy.currency_id 
LEFT JOIN address_details addr ON addr.address_id = org.organisation_regoffice_id 
LEFT JOIN country_mas as cntry ON cntry.country_id = addr.address_country
LEFT JOIN categories as cat ON cat.nace_id = org.nace_id 
LEFT JOIN category_group ON category_group.nace_group_id = org.nace_group_id 
WHERE tr.user_type = 5 AND org.type <> 6 
group by org.organisation_id ASC;