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
| SELECT o.`id_order` AS `id`,
CONCAT(LEFT(c.`firstname`, 1), '. ', c.`lastname`) AS `Customer`,
ca.`name` AS `Carrier`,
cu.`name` AS `Currency`,
od.`product_quantity`,
od.`unit_price_tax_excl`,
o.`date_add` AS `Date`,
od.`product_id`,
od.`product_name`,
od.`product_weight`,
od.`product_supplier_reference`,
oc.`shipping_cost_tax_excl`,
ps.`product_supplier_price_te`,
/*(SELECT(SUM(product_quantity) FROM `ma_order_detail` GROUP BY id_order)),*/
(od.`unit_price_tax_excl` * od.`product_quantity`)+(oc.`shipping_cost_tax_excl` - (ps.`product_supplier_price_te` * od.`product_quantity`)) AS BENEFIT
FROM `ma_orders` o
LEFT JOIN `ma_customer` c ON (o.`id_customer` = c.`id_customer`)
LEFT JOIN `ma_carrier` ca ON (o.id_carrier = ca.id_carrier)
LEFT JOIN `ma_currency` cu ON (o.`id_currency` = cu.`id_currency`)
LEFT JOIN `ma_order_detail` od ON (o.`id_order` = od.`id_order`)
LEFT JOIN `ma_order_carrier` oc ON (o.`id_order` = oc.`id_order`)
LEFT JOIN `ma_product_supplier` ps ON (od.`product_supplier_reference` = ps.`product_supplier_reference`)
WHERE o.`date_add`>'2016-07-01 00:00:00' |
Partager