SELECT 
p.id orders_id,
 ordu.meta_value customers_id 
,concat(bfn.meta_value,' ',bln.meta_value )customers_name,
bc.meta_value customers_company 
,bs.meta_value customers_street_address
,bs2.meta_value customers_suburb
,bci.meta_value customers_city
,bpc.meta_value customers_postcode
,bst.meta_value customers_state
,bco.meta_value customers_country
,'' customers_telephone, 
'' customers_email_address, 
'' customers_address_format_id  
,concat(bfn.meta_value,' ',bln.meta_value )billing_name,
bc.meta_value billing_company 
,bs.meta_value billing_street_address
,bs2.meta_value billing_suburb
,bci.meta_value billing_city
,bpc.meta_value billing_postcode
,bst.meta_value billing_state
,bco.meta_value billing_country
,concat(dfn.meta_value,' ',dln.meta_value )delivery_name,
dc.meta_value delivery_company 
,ds.meta_value delivery_street_address
,ds2.meta_value delivery_suburb
,dci.meta_value delivery_city
,dpc.meta_value delivery_postcode
,dst.meta_value delivery_state
,dco.meta_value delivery_country 
,ordc.meta_value currency
,'' payment_method 
,'' cc_type 
,'' cc_owner 
,'' cc_number 
,null cc_expires 
,p.post_modified last_modified 
,p.post_date date_purchased
,p.post_status orders_status 
,p.post_modified orders_date_finished
,1 currency_value
 

FROM <@PREFIX>users us 
join <@PREFIX>posts p on p.post_author = us.ID  and p.post_type ='shop_order'
left join <@PREFIX>postmeta bfn
on p.id = bfn.post_id and bfn.meta_key= '_billing_first_name'
left join <@PREFIX>postmeta bln
on p.id = bln.post_id and bln.meta_key= '_billing_last_name'

left join <@PREFIX>postmeta bc
on p.id = bc.post_id and bc.meta_key= '_billing_company'
left join <@PREFIX>postmeta bs
on p.id = bs.post_id and bs.meta_key= '_billing_address_1'
left join <@PREFIX>postmeta bs2
on p.id = bs2.post_id and bs2.meta_key= '_billing_address_2'
left join <@PREFIX>postmeta bci
on p.id = bci.post_id and bci.meta_key= '_billing_city'
left join <@PREFIX>postmeta bpc
on p.id = bpc.post_id and bpc.meta_key= '_billing_postcode'
left join <@PREFIX>postmeta bco
on p.id = bco.post_id and bco.meta_key= '_billing_country'
left join <@PREFIX>postmeta bst
on p.id = bst.post_id and bst.meta_key= '_billing_state'

left join <@PREFIX>postmeta dfn
on p.id = dfn.post_id and dfn.meta_key= '_billing_first_name'
left join <@PREFIX>postmeta dln
on p.id = dln.post_id and dln.meta_key= '_billing_last_name'

left join <@PREFIX>postmeta dc
on p.id = dc.post_id and dc.meta_key= '_shipping_company'
left join <@PREFIX>postmeta ds
on p.id = ds.post_id and ds.meta_key= '_shipping_address_1'
left join <@PREFIX>postmeta ds2
on p.id = ds2.post_id and ds2.meta_key= '_shipping_address_2'
left join <@PREFIX>postmeta dci
on p.id = dci.post_id and dci.meta_key= '_shipping_city'
left join <@PREFIX>postmeta dpc
on p.id = dpc.post_id and dpc.meta_key= '_shipping_postcode'
left join <@PREFIX>postmeta dco
on p.id = dco.post_id and dco.meta_key= '_shipping_country'
left join <@PREFIX>postmeta dst
on p.id = dst.post_id and dst.meta_key= '_shipping_state'
left join <@PREFIX>postmeta ordc
on p.id = ordc.post_id and ordc.meta_key= '_order_currency'
left join <@PREFIX>postmeta ordu
on p.id = ordu.post_id and ordu.meta_key= '_customer_user'
 
where ((p.post_date >= %s and p.post_modified is null) or p.post_modified >= %s ) 
and p.post_status in ('%s')
and ((p.post_date <= %s and p.post_modified is null) or p.post_modified <= %s )
order by date_purchased

