Jump to content

Requête SQL export commande avec table ps_message


Recommended Posts

Bonjour,

Je cherche à créer une requête SQL, exécutable depuis le BO, sortant les table ps_orders et ps_message combinées.

Cela permettant d'exporter les commandes avec les messages qui y sont liés, plus précisément, la mention commande manuelle.

Si quelqu'un à la syntaxe en tête, je suis preneur.

Merci d'avance à tous ceux qui se pencheront sur ma demande.

Link to comment
Share on other sites

Au cas où cela intéresse quelqu'un, je laisse la requête ci-dessous :

 

SELECT 

d.id_order, 
o.reference,

o.total_discounts_tax_incl,
o.total_shipping_tax_incl,
os.name AS state, 
d.product_name, 
d.product_reference, 
d.product_price,
d.product_price - d.reduction_amount AS final_price,
d.product_quantity, 
o.payment, 
o.date_upd,
g.email,
g.birthday,
address.phone,
address.phone_mobile,
CONCAT_WS(' ', g.firstname, g.lastname) AS customer, 
CONCAT_WS(' ',address.`address1`,address.`address2`, address.`postcode`, address.`city`, statelocation.`name`) AS `address`,
carrier.name,
sp.price,
me.message


    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_group_lang gl ON (g.id_default_group = gl.id_group) AND gl.name LIKE 'club%'
    LEFT JOIN ps_order_state_lang os ON (o.current_state = os.id_order_state)


LEFT JOIN `ps_address` address ON (o.id_address_delivery = address.id_address)
LEFT JOIN ps_carrier carrier ON (o.id_carrier = carrier.id_carrier  )
LEFT JOIN ps_state statelocation ON (address.id_state = statelocation.id_state)
LEFT JOIN ps_product oproduct ON (d.product_id= oproduct.id_product  )
LEFT JOIN ps_message me ON (o.id_order = me.id_order)
LEFT JOIN ps_product_shop sp ON (oproduct.id_product = sp.id_product)

    WHERE os.id_lang = 1
 ORDER BY d.`id_order` DESC

Cela permet d'exporter les commandes avec les messages qui y sont liés, notamment, la mention "commande manuelle".

Bonne soirée.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...