SELECT 
o.order_id orders_id,
o.order_user_id customers_id, 
coalesce(Concat(coalesce(bt.address_title,''),' ',coalesce(bt.address_firstname,''),' ', coalesce(bt.address_lastname,'')), usr.user_email)  customers_name, 
bt.address_company customers_company, 
bt.address_street customers_street_address, 
bt.address_street2 customers_suburb, 
bt.address_city customers_city, 
bt.address_post_code customers_postcode, 
bts.zone_name customers_state, 
btc.zone_name  customers_country, 
bt.address_telephone customers_telephone, 
usr.user_email customers_email_address, 
1 customers_address_format_id, 
Concat(coalesce(del.address_title,''),' ',coalesce(del.address_firstname,''),' ', coalesce(del.address_lastname,'')) delivery_name, 
del.address_company delivery_company, 
del.address_street delivery_street_address, 
del.address_street2 delivery_suburb, 
del.address_city delivery_city, 
del.address_post_code delivery_postcode, 
dels.zone_name delivery_state, 
delc.zone_name delivery_country, 
1 delivery_address_format_id, 
Concat(bt.address_title,' ',bt.address_firstname,' ', bt.address_lastname) billing_name, 
bt.address_company billing_company, 
bt.address_street billing_street_address, 
bt.address_street2 billing_suburb, 
bt.address_city billing_city, 
bt.address_post_code billing_postcode, 
bts.zone_name billing_state, 
btc.zone_name billing_country, 
1 billing_address_format_id, 
o.order_payment_method payment_method, 
1 cc_type, 
'' cc_owner, 
'' cc_number, 
'' cc_expires, 
from_unixtime(o.order_modified) last_modified, 
from_unixtime(o.order_created) date_purchased, 
o.order_status orders_status, 
from_unixtime(o.order_created) orders_date_finished, 
'EUR' currency, 
1 currency_value,
'' remark1,
'' remark2,
'' remark3
FROM <@PREFIX>hikashop_order o
 join <@PREFIX>hikashop_address bt  
   on (o.order_billing_address_id = bt.address_id )
 join <@PREFIX>hikashop_address del  
   on (o.order_shipping_address_id =  del.address_id )
join <@PREFIX>hikashop_user usr on usr.user_id = o.order_user_id
join <@PREFIX>hikashop_category ct on ct.category_namekey = o.order_status
join <@PREFIX>hikashop_zone btc on bt.address_country =  btc.zone_namekey
left join <@PREFIX>hikashop_zone bts on bt.address_state =  bts.zone_namekey
join <@PREFIX>hikashop_zone delc on del.address_country =  delc.zone_namekey
left join <@PREFIX>hikashop_zone dels on del.address_state =  dels.zone_namekey
where ((from_unixtime(o.order_created) >= %s and from_unixtime(o.order_modified) is null) or from_unixtime(o.order_created) >= %s ) and ct.category_id in (%s)
and ((from_unixtime(o.order_created) <= %s and from_unixtime(o.order_modified) is null) or from_unixtime(o.order_created) <= %s )
