1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
| SELECT
d.id_order,
o.date_add,
CONCAT_WS(' ', g.firstname, g.lastname) AS customer,
g.email,
os.name AS state,
d.product_name,
d.product_reference,
d.product_quantity,
d.product_price,
o.payment,
ad.phone,
c.name AS carrier_name,
CONCAT_WS(' ', a.lastname, a.firstname, a.address1, a.address2, a.postcode, a.city) AS address_delivery,
CONCAT_WS(' ', a.lastname, a.firstname, a.address1, a.address2, a.postcode, a.city) AS address_invoice,
REPLACE(IFNULL(GROUP_CONCAT(cd.value), ''), '"', '\'') AS customized_data
FROM
ps_order_detail d
LEFT JOIN ps_orders o ON (d.id_order = o.id_order)
LEFT JOIN ps_customer g ON (o.id_customer = g.id_customer)
LEFT JOIN ps_carrier c ON (o.id_carrier = c.id_carrier)
LEFT JOIN ps_order_state_lang os ON (o.current_state = os.id_order_state)
LEFT JOIN ps_address a ON (a.id_address = o.id_address_delivery)
LEFT JOIN ps_address ab ON (ab.id_address = o.id_address_invoice)
LEFT JOIN ps_address ad ON (ad.phone = ad.phone)
LEFT JOIN ps_customization cu ON (cu.id_cart = o.id_cart)
LEFT JOIN ps_customized_data cd ON (cd.id_customization = cu.id_customization)
GROUP BY
d.id_order
ORDER BY
d.id_order DESC |
