/* export all b-ETR trades from Exchange 2.0 */
/*--------- VALID ------------*/
SELECT 
organisation_details.organisation_reference_id as 'Related Entity ID',
IF(organisation_details.organisation_trade_name,organisation_details.organisation_trade_name,organisation_details.organisation_name) as 'Originator Name',
debtors_detail.debtor_reference_id as 'Entity Id',
IF(debtors_detail.trade_name,debtors_detail.trade_name,debtors_detail.debtor_name) as 'Entity Name',
debtors_master.master_reference_id as 'Parent Entity Id',
IF(debtors_master.debtor_trade_name, debtors_master.debtor_trade_name, debtors_master.debtor_name) as 'Parent Entity Name',
DATE(invoice_master.actual_date) as 'Issued On',
invoice_master.trade_reference_id as 'Exchange Ref ID',
convert(binary convert(invoice_master.invoice_no using latin1) using utf8) as 'Reference',
DATE(invoice_master.created_at) as 'Posted On',
DATE(invoice_master.logapprove_date) as 'Traded On',
IFNULL(DATEDIFF(DATE(expected_date), DATE(logapprove_date)),0) as 'EDSO',
1 as 'Period Type',
DATEDIFF(IF(payment_trans.transaction_date IS NOT NULL AND payment_trans.transaction_date<='2021-06-30',payment_trans.transaction_date,'2021-06-30'),DATE(actual_date)) as RDSO,
currencies.currency_code as 'Ccy',
invoice_master.face_value as 'Face Value',
IF(currencies.currency_code='EUR',1.000, currency_rates.currency_rate) as 'Traded Fx',
IF(
	IF(invoice_master.face_value_charge_applied=0,0,IFNULL(invoice_master.face_value_charge,IFNULL(debtors_detail.face_value_charge,IF(debtors_detail.type='debtor',organisation_details.face_value_charge,organisation_details.creditor_face_value_charge)))/100)>0,
	IF(invoice_master.face_value_charge_applied=0,0,IFNULL(invoice_master.face_value_charge,IFNULL(debtors_detail.face_value_charge,IF(debtors_detail.type='debtor',organisation_details.face_value_charge,organisation_details.creditor_face_value_charge)))/100),
	IFNULL(invoice_closed.max_thirty_reserve,0)/100) as 'Sell Rate',
IF(
	IF(invoice_master.face_value_charge_applied=0,0,IFNULL(invoice_master.face_value_charge, IFNULL(debtors_detail.face_value_charge, IF(debtors_detail.type = 'debtor', organisation_details.face_value_charge, organisation_details.creditor_face_value_charge)))/100)>0,
	2,
	1) as 'Finance Type',
IF(invoice_master.face_value_charge_applied=0,0,IFNULL(invoice_master.face_value_charge, IFNULL(debtors_detail.face_value_charge, IF(debtors_detail.type = 'debtor', organisation_details.face_value_charge, organisation_details.creditor_face_value_charge)))/100) as 'Fixed Charge',
IF(invoice_master.payment_discount/100=0,1.0,invoice_master.payment_discount/100) as 'Purchased',
IFNULL(invoice_closed_attributes.processing_fee,0) as 'Trade Commission',
IFNULL(invoice_master.traded_processing_fee,0) as 'Processing Commission',
IF(main_trans.transaction_type=20, DATE(payment_trans.transaction_date) ,'') as 'Settled On',
IF(main_trans.transaction_type=20, DATE(payment_trans.transaction_date) ,'') as 'Closed On',
IF(currencies.currency_code='EUR',1.000, IF(payment_trans.transaction_date IS NULL,NULL,reserve_rates.currency_rate)) as 'Settled Fx',
IF(invoice_master.reserve IS NULL,0.00,invoice_master.reserve) as 'Commission',
IF(invoice_master.ldc_premium IS NULL,0.00,invoice_master.ldc_premium) as 'LDC Premium',
IF(debtor_address_details.address_country=104 OR debtor_address_details_bill.address_country=104,IF(originator_address_details.address_country=104,1,0),0) as 'Tax Type Inc or Exc',
3 as 'Tax ID On Purchase',
3 as 'Tax ID on Sales',
/*IF(invoice_master.is_aetr = 1, 'a', invoice_master.etr_type) as 'ETR Type',*/
/*address_details.address_country as 'Debtor Country', */
'b' as 'ETR Type' 
FROM invoice_master 
LEFT JOIN organisation_details ON organisation_details.user_id = invoice_master.user_id 
LEFT JOIN debtors_detail ON debtors_detail.debtor_id = invoice_master.debtor_id 
LEFT JOIN address_details as debtor_address_details ON debtor_address_details.address_id=debtors_detail.address_id 
LEFT JOIN address_details as debtor_address_details_bill ON debtor_address_details_bill.address_id=debtors_detail.billing_address_id 
LEFT JOIN address_details as originator_address_details ON originator_address_details.address_id=organisation_details.organisation_regoffice_id 
LEFT JOIN debtors_master ON debtors_master.debtor_master_id = debtors_detail.parent_id 
LEFT JOIN invoice_closed ON invoice_closed.invoice_id = invoice_master.invoice_id 
LEFT JOIN invoice_closed_attributes ON invoice_closed_attributes.invoice_id = invoice_master.invoice_id 
LEFT JOIN manual_transactions as main_trans ON main_trans.manual_transaction_id = invoice_master.manual_transaction_id AND main_trans.status=1 
LEFT JOIN manual_transactions as payment_trans ON main_trans.reconcile_payment = payment_trans.manual_transaction_id AND payment_trans.status=1 
LEFT JOIN currencies ON currencies.currency_id = debtors_detail.currency_id 
LEFT JOIN currency_rates ON currency_rates.currency_id = debtors_detail.currency_id AND currency_rates.currency_date = DATE(invoice_master.logapprove_date) 
LEFT JOIN currency_rates as reserve_rates ON reserve_rates.currency_id = debtors_detail.currency_id AND reserve_rates.currency_date = payment_trans.transaction_date 
LEFT JOIN currency_rates as r_trade_rates ON r_trade_rates.currency_id = debtors_detail.currency_id AND r_trade_rates.currency_date = main_trans.transaction_date 
LEFT JOIN organisation_currencies ON organisation_currencies.user_id = invoice_master.user_id AND currencies.currency_id = organisation_currencies.currency_id 
LEFT JOIN organisation_currencies as oc_eur ON oc_eur.user_id = invoice_master.user_id AND oc_eur.currency_code='EUR' 
LEFT JOIN site_settings ON site_settings.site_setting_id=1 
WHERE invoice_master.root_invoice_id>0 AND invoice_master.log_status=1 AND invoice_master.etr_type='b' AND invoice_master.is_aetr = 0 
AND debtors_detail.type='creditor' 
ORDER BY 'Originator Name', 'Creditor Name';

/*-------*/

IF(currencies.currency_code='EUR',1.000,
                            IF(main_trans.transaction_type =".RECONCILE_DEBTOR_ACCOUNT.",
                                IF(invoice_master.reserve IS NULL,NULL,
                                    reserve_rates.currency_rate),
                                IF(payment_trans.transaction_date IS NULL,NULL,
                                reserve_rates.currency_rate))) as 'settled_fx',