Tip: using PrestaShop's SQL Manager to export detailed orders

Beluga · October 6, 2013

Thanks to Nemo's tutorial on exporting products, I was able to figure this out:

    FROM ps_order_detail d
    LEFT JOIN ps_orders o ON (d.id_order = o.id_order)
    LEFT JOIN ps_address ad ON (o.id_address_delivery = ad.id_address)
    LEFT JOIN ps_address ai ON (o.id_address_invoice = ai.id_address)
    LEFT JOIN ps_stock_available s ON (d.product_id = s.id_product)
    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 'piiri%'
    LEFT JOIN ps_order_state_lang os ON (o.current_state = os.id_order_state)
    WHERE os.id_lang = 1
GROUP BY gl.name, d.id_order, d.product_reference

Here you can see the CSV output (rename to .CSV and open in your spreadsheet program as UTF-8 and use semicolon as separator):
request_sql_2.txt
The .CSV contains test orders that are the result of my experimenting with payments.

This serves a specific use case, where the shop manager uses their own order and shipping management system and needs to get the order data as individual products. It also lists the group names for customer groups that use the naming scheme that starts with 'club'.

Edit: updated to fix group multiplication problem.

Edited February 16, 2014 by Beluga (see edit history)

zeus71 · October 23, 2013

Thank you you are my HERO.

I search it for days..and days...

ietax · October 24, 2013

SELECT d.id_order, os.name AS state, d.product_name, d.product_reference, d.product_price, d.product_quantity, o.payment, o.date_upd, CONCAT_WS(' ', g.firstname, g.lastname) AS customer, gl.name AS group_name
    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)
    WHERE os.id_lang = 1

How can I export complete order details in a file?

ex. for each order payed create single file TXT (file name = order numer) with complete address...

it is possible? thanks

Adria Laxson · January 14, 2014

That works beautifully but I really need to export the UPC as well. I try this and get an error that I haven't done the subquery correctly (The Table "DEPENDENT-SUBQUERY" doesn't exis). Here is my code...

select F.id_order,

F.InvoiceNumber,

F.DeliveryNumber,

F.InvoiceDate,

F.DeliveryDate,

F.ShipToCompany,

F.ShipToFirstName,

F.ShipToLastName,

F.ShipToAddress1,

F.ShipToAddress2,

F.ShipToCity,

F.ShipToState,

F.ShipToPostal,

F.ShipToCountryCode,

F.BillToCompany,

F.BillToFirstName,

F.BillToLastName,

F.BillToAddress1,

F.BillToAddress2,

F.BillToCity,

F.BillToState,

F.BillToPostal,

F.BillToCountryCode,

F.payment,

F.shipping_number,

F.email,

F.lastname,

F.firstname,

F.date_add,

F.product_name,

F.product_reference,

F.product_quantity,

F.product_price,

F.DiscountTotal,

F.PaidTotal,

F.PaidTotalActual,

F.MerchandiseTotal,

F.ShippingTotal,

F.WrappingTotal,

F.product_ean13,

F.product_supplier_reference,

/*

This sub-select is used to make one final attempt to go get the UPC from the product record in the event the UPC is not contained on the order,

and this uses 2 additional techniques where PrestaShop appears to place product info.

*/

IF(F.product_upc is null or F.product_upc = '',

(

IF((

SELECT distinct upc

FROM ps_product_attribute

WHERE reference = F.product_reference

AND id_product_attribute = F.product_attribute_id

) is null, (SELECT upc FROM ps_product WHERE id_product = F.product_id), (

SELECT distinct upc

FROM ps_product_attribute

WHERE reference = F.product_reference

AND id_product_attribute = F.product_attribute_id

))

)

,F.product_upc) as product_upc

from

(

SELECT O.id_order,

O.invoice_number as InvoiceNumber,

O.delivery_number as DeliveryNumber,

O.invoice_date as InvoiceDate,

O.delivery_date as DeliveryDate,

AD.company as ShipToCompany,

AD.firstname as ShipToFirstName,

AD.lastname as ShipToLastName,

AD.address1 as ShipToAddress1,

AD.address2 as ShipToAddress2,

AD.city as ShipToCity,

ADS.name ShipToState,

AD.postcode as ShipToPostal,

ADC.iso_code ShipToCountryCode,

AI.company as BillToCompany,

AI.firstname as BillToFirstName,

AI.lastname as BillToLastName,

AI.address1 as BillToAddress1,

AI.address2 as BillToAddress2,

AI.city as BillToCity,

AIS.name BillToState,

AI.postcode as BillToPostal,

AIC.iso_code BillToCountryCode,

O.payment,

O.shipping_number,

C.email,

C.lastname,

C.firstname,

O.date_add,

replace(replace(OD.product_name, '\n', ''), '\r', '') as product_name,

OD.product_reference,

OD.product_quantity,

OD.product_price,

O.total_discounts as DiscountTotal,

O.total_paid as PaidTotal,

O.total_paid_real as PaidTotalActual,

O.total_products as MerchandiseTotal,

O.total_shipping as ShippingTotal,

O.total_wrapping as WrappingTotal,

OD.product_ean13,

OD.product_supplier_reference,

/*This sub-select is used to go get the UPC from the product record in the event the UPC is not contained on the order.*/

IF(OD.product_upc is null or OD.product_upc = '',

(

select IFNULL(P.UPC_PA,P.UPC_P) as UPC

from

(

/*Products with combinations.*/

select p.reference as REF_P,

p.id_product,

pl.name AS NAME_PL,

agl.name AS NAME_G,

al.name AS NAME_A,

pa.reference as REF_A,

IFNULL(pa.upc,'') as UPC_PA,

IFNULL(p.upc,'') as UPC_P,

pa.id_product as id_product_PA,

ag.id_attribute_group ,

a.id_attribute ,

pa.id_product_attribute

FROM ps_product_attribute pa

LEFT JOIN ps_product_attribute_combination pac ON pac.id_product_attribute = pa.id_product_attribute

LEFT JOIN ps_attribute a ON a.id_attribute = pac.id_attribute

LEFT JOIN ps_attribute_group ag ON ag.id_attribute_group = a.id_attribute_group

LEFT JOIN ps_attribute_lang al ON a.id_attribute = al.id_attribute

LEFT JOIN ps_attribute_group_lang agl ON ag.id_attribute_group = agl.id_attribute_group

LEFT JOIN ps_product p ON pa.id_product = p.id_product

LEFT JOIN ps_product_lang pl ON p.id_product = pl.id_product

WHERE al.id_lang =1

AND agl.id_lang =1

and pl.id_lang =1

union

/*Products without combinations.*/

select p.reference as REF_P,

p.id_product,

pl.name AS NAME_PL,

null AS NAME_G,

null AS NAME_A,

pa.reference as REF_A,

IFNULL(pa.upc,'') as UPC_PA,

IFNULL(p.upc,'') as UPC_P,

pa.id_product as id_product_PA,

null as id_attribute_group,

null as id_attribute,

null as id_product_attribute

FROM ps_product p

LEFT JOIN ps_product_lang pl ON p.id_product = pl.id_product

LEFT JOIN ps_product_attribute pa ON pa.id_product = p.id_product

WHERE pl.id_lang =1

and p.id_product not in (

select p.id_product

FROM ps_product_attribute pa

LEFT JOIN ps_product_attribute_combination pac ON pac.id_product_attribute = pa.id_product_attribute

LEFT JOIN ps_attribute a ON a.id_attribute = pac.id_attribute

LEFT JOIN ps_attribute_group ag ON ag.id_attribute_group = a.id_attribute_group

LEFT JOIN ps_attribute_lang al ON a.id_attribute = al.id_attribute

LEFT JOIN ps_attribute_group_lang agl ON ag.id_attribute_group = agl.id_attribute_group

LEFT JOIN ps_product p ON pa.id_product = p.id_product

LEFT JOIN ps_product_lang pl ON p.id_product = pl.id_product

WHERE al.id_lang =1

AND agl.id_lang =1

and pl.id_lang =1

)

) P

where P.id_product = OD.product_id

and P.id_product_attribute = OD.product_attribute_id

),

OD.product_upc) as product_upc,

OD.product_attribute_id,

OD.product_id

FROM ps_orders AS O,

ps_order_detail AS OD,

ps_customer AS C,

ps_address AS AD,

ps_address AS AI,

ps_state AS ADS,

ps_state AS AIS,

ps_country AS ADC,

ps_country AS AIC

WHERE AI.id_country = AIC.id_country

and AD.id_country = ADC.id_country

and AI.id_state = AIS.id_state

and AD.id_state = ADS.id_state

and AI.id_address=id_address_invoice

AND AD.id_address=id_address_delivery

AND C.id_customer=O.id_customer

AND O.id_order = OD.id_order

ietax · January 14, 2014

I made PHP script for export txt file for each order.

If you want more info write PM thanks

Beluga · January 24, 2014

I'm having trouble with exporting orders from customer group members. An order with one product gets cloned 11 times, if the customer belongs to a group. I guess this would need some GROUP BY or...?
Here is my current export query:

    SELECT d.id_order, os.name AS tila, d.product_name, d.product_reference, d.product_price, d.product_quantity, o.payment, o.date_upd, CONCAT_WS(' ', g.lastname, g.firstname) AS asiakas, g.id_customer AS asiakasID, CONCAT_WS(' ', ad.address1, ad.address2, 'Kaupunki:', ad.postcode, ad.city, ad.other, 'Puhelin: ', ad.phone, 'Kännykkä: ', ad.phone_mobile) AS toimitusosoite, CONCAT_WS(' ', ai.address1, ai.address2,'Kaupunki:',  ai.postcode, ai.city, ai.other, 'Puhelin: ', ai.phone, 'Kännykkä: ', ai.phone_mobile) AS laskutusosoite, gl.name AS group_name, s.quantity AS quantity_in_stock, g.email
    FROM ps_order_detail d
    LEFT JOIN ps_orders o ON (d.id_order = o.id_order)
    LEFT JOIN ps_address ad ON (o.id_address_delivery = ad.id_address)
    LEFT JOIN ps_address ai ON (o.id_address_invoice = ai.id_address)
    LEFT JOIN ps_stock_available s ON (d.product_id = s.id_product)
    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 'piiri%'
    LEFT JOIN ps_order_state_lang os ON (o.current_state = os.id_order_state)
    WHERE os.id_lang = 1

~~Solved by adding GROUP BY d.id_order at the end.~~

Edited February 16, 2014 by Beluga (see edit history)

Axel444 · February 8, 2014

How can I export:

Firstname,

Lastname,
Address1,

Address2,
Cyti,

Zip code,

Country,

Product_name,

Quantity,

Order number

Help Me Please!

ietax · February 8, 2014

How can I export:

Firstname,

Lastname,

Address1,

Address2,

Cyti,

Zip code,

Country,

Product_name,

Quantity,

Order number

Help Me Please!

write PM please

Beluga · February 16, 2014

I'm having trouble with exporting orders from customer group members. An order with one product gets cloned 11 times, if the customer belongs to a group. I guess this would need some GROUP BY or...?

Here is my current export query:

    SELECT d.id_order, os.name AS tila, d.product_name, d.product_reference, d.product_price, d.product_quantity, o.payment, o.date_upd, CONCAT_WS(' ', g.lastname, g.firstname) AS asiakas, g.id_customer AS asiakasID, CONCAT_WS(' ', ad.address1, ad.address2, 'Kaupunki:', ad.postcode, ad.city, ad.other, 'Puhelin: ', ad.phone, 'Kännykkä: ', ad.phone_mobile) AS toimitusosoite, CONCAT_WS(' ', ai.address1, ai.address2,'Kaupunki:',  ai.postcode, ai.city, ai.other, 'Puhelin: ', ai.phone, 'Kännykkä: ', ai.phone_mobile) AS laskutusosoite, gl.name AS group_name, s.quantity AS quantity_in_stock, g.email
    FROM ps_order_detail d
    LEFT JOIN ps_orders o ON (d.id_order = o.id_order)
    LEFT JOIN ps_address ad ON (o.id_address_delivery = ad.id_address)
    LEFT JOIN ps_address ai ON (o.id_address_invoice = ai.id_address)
    LEFT JOIN ps_stock_available s ON (d.product_id = s.id_product)
    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 'piiri%'
    LEFT JOIN ps_order_state_lang os ON (o.current_state = os.id_order_state)
    WHERE os.id_lang = 1

~~Solved by adding GROUP BY d.id_order at the end.~~

I'm still unable to solve this. grouping by id_order or reference is not what I want, because then it displays only one entry per order or product. If I group by the group name and some other (GROUP BY gl.name, d.id_order), it doesn't display only one entry per group, but I don't have anything else to have as the second column that doesn't destroy the point of the query! I've tried CONCAT on the SELECT gl.name and GROUP_CONCAT, but it doesn't work. How on earth can this be solved?

EDIT: Finally seem to have solved it by adding this to the end

GROUP BY gl.name, d.id_order, d.product_reference

This

1) doesn't crazily multiply the customer groups

2) explodes the order contents like I want 'em

3) doesn't implode stuff based on product_reference

Edited February 16, 2014 by Beluga (see edit history)

lukasz_biovico · April 28, 2014

Hello

Tell my do you can export only manual orders?

orders that were added by sellers (admin panel - backoffice).

I would like to generate sales of separation on the person who added the order.

Beluga · April 29, 2014

Hello

Tell my do you can export only manual orders?

orders that were added by sellers (admin panel - backoffice).

I would like to generate sales of separation on the person who added the order.

When you have a need like this, you have to look inside the Prestashop database.

In the table ps_order_history we see there is a field id_employee. If this is other than 0, it means the order was placed from the backoffice. You can use this information to create the SQL query.

AnitaR · May 15, 2014

Hello, this is really interesting, but I am strugling with the way it exprts the amounts. Since we use 3 decimals in pricing, I get e.g. for an amount of 45 euro: (normally then 45,000)45.000.000. Cant do anything with this in excel.. not without some extensice formula to make normal currency of it. any idea how to handle this?

BoKr · December 8, 2014

I stumbled about the cool SQL features in PSM. I am desperately in need for a way to create a nice '
picking list' report. I just bought a module from CodeCanyon that claims to do this but it is constrained by fixed BO/Order statuses in order to filter export.

In a nutshell I need a SQL query to export orders from the last 7/14 days. Not all but those I choose by their order status (I got some custom statuses and could gather their ID in the DB...).

Important is of course that all products of an order are listed under productname

PS:

Are there any tutorials out there where I could start learning how to write a module based on these SQL queries?

Would be pretty cool to have all these and may be some more fields I could 'check' in a module to query them in a deliberatly way. Let's say all or some or combinations of fields as I like.... and choosable date frame as combinations of BO/order statuses (custom ones; order IDs)...

*****
payment ishavegift giftmessage totalpaid orderadd orderupd customerfirstname customerlastname statuscurrent productname *****

Sincerely
Boris

FERMB · June 30, 2015

I'm still unable to solve this. grouping by id_order or reference is not what I want, because then it displays only one entry per order or product. If I group by the group name and some other (GROUP BY gl.name, d.id_order), it doesn't display only one entry per group, but I don't have anything else to have as the second column that doesn't destroy the point of the query! I've tried CONCAT on the SELECT gl.name and GROUP_CONCAT, but it doesn't work. How on earth can this be solved?

EDIT: Finally seem to have solved it by adding this to the end

GROUP BY gl.name, d.id_order, d.product_reference

This

1) doesn't crazily multiply the customer groups

2) explodes the order contents like I want 'em

3) doesn't implode stuff based on product_reference

Thanks for your post Beluga.

I use it to write a similar sql, in spanish and group only by d.id_order.

In this form don´t duplicate and don´t erase any product. if you need to see all the products and all the orders, you can get it with a code like this:

SELECT d.id_order, os.name AS estado, o.date_upd AS fecha, d.product_name AS producto, d.product_reference AS ref_ReleMat, d.product_supplier_reference AS ref_proveedor, d.product_quantity AS uds,  d.product_price, s.quantity AS en_stock, o.payment,  CONCAT_WS(  ' ', g.firstname, g.lastname ) AS cliente,  CONCAT_WS(' ', ad.address1, ad.address2, ad.postcode, ad.city, ad.other, ad.phone, ad.phone_mobile) AS envio, CONCAT_WS(' ', ai.address1, ai.address2,  ai.postcode, ai.city, ai.other, ai.phone, ai.phone_mobile) AS facturacion, g.email, gl.name AS grupo
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_stock_available s ON (d.product_id = s.id_product)
LEFT JOIN ps_address ad ON (o.id_address_delivery = ad.id_address)
LEFT JOIN ps_address ai ON (o.id_address_invoice = ai.id_address)
LEFT JOIN ps_group_lang gl ON ( g.id_default_group = gl.id_group ) 
LEFT JOIN ps_order_state_lang os ON ( o.current_state = os.id_order_state ) 
WHERE os.id_lang =1
GROUP BY d.id_order, d.product_name
ORDER BY d.id_order DESC

I use it in PS1.5 and it´s ok.

Edited June 30, 2015 by FERMB (see edit history)

cooliyev · September 9, 2015

Hi, I need your help with SQL. This is my SQL

SELECT d.id_order, d.product_reference, d.product_attribute_id, d.product_quantity,  g.firstname, g.lastname,  ai.address1, ai.address2,  ai.postcode, ai.city, ai.other,  ai.phone_mobile
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_stock_available s ON (d.product_id = s.id_product)
LEFT JOIN ps_address ad ON (o.id_address_delivery = ad.id_address)
LEFT JOIN ps_address ai ON (o.id_address_invoice = ai.id_address)
LEFT JOIN ps_group_lang gl ON ( g.id_default_group = gl.id_group )
LEFT JOIN ps_order_state_lang os ON ( o.current_state = os.id_order_state )
WHERE os.id_lang =1
GROUP BY d.id_order
ORDER BY d.id_order DESC

How to add here color and size of the product?

This code is correct on phpmyadmin but i cannot and don't know how integrate this code to the upper code

SELECT ps_attribute_lang.name
FROM ps_attribute_lang
LEFT JOIN ps_product_attribute_combination
ON ps_product_attribute_combination.id_attribute = ps_attribute_lang.id_attribute
WHERE ps_product_attribute_combination.id_product_attribute = "2061" AND ps_attribute_lang.id_lang="1"

Edited September 9, 2015 by cooliyev (see edit history)

SfxGR · March 10, 2020

Hi guys,

Could you help me out. Looking for a SQL, to export

Delivery address (first name, last name, address 1, city, post code, country) together with the order id and order reference, where current order state is 2, Shop ID is 8.

Many thanks

Nickovitshj · April 30, 2021

On 6/30/2015 at 11:19 PM, FERMB said:

Thanks for your post Beluga.

I use it to write a similar sql, in spanish and group only by d.id_order.

In this form don´t duplicate and don´t erase any product. if you need to see all the products and all the orders, you can get it with a code like this:


SELECT d.id_order, os.name AS estado, o.date_upd AS fecha, d.product_name AS producto, d.product_reference AS ref_ReleMat, d.product_supplier_reference AS ref_proveedor, d.product_quantity AS uds,  d.product_price, s.quantity AS en_stock, o.payment,  CONCAT_WS(  ' ', g.firstname, g.lastname ) AS cliente,  CONCAT_WS(' ', ad.address1, ad.address2, ad.postcode, ad.city, ad.other, ad.phone, ad.phone_mobile) AS envio, CONCAT_WS(' ', ai.address1, ai.address2,  ai.postcode, ai.city, ai.other, ai.phone, ai.phone_mobile) AS facturacion, g.email, gl.name AS grupo
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_stock_available s ON (d.product_id = s.id_product)
LEFT JOIN ps_address ad ON (o.id_address_delivery = ad.id_address)
LEFT JOIN ps_address ai ON (o.id_address_invoice = ai.id_address)
LEFT JOIN ps_group_lang gl ON ( g.id_default_group = gl.id_group ) 
LEFT JOIN ps_order_state_lang os ON ( o.current_state = os.id_order_state ) 
WHERE os.id_lang =1
GROUP BY d.id_order, d.product_name
ORDER BY d.id_order DESC

I use it in PS1.5 and it´s ok.

How can I specify I want this list for a specific shop id?

I'm running multistore on PS 1.7.6.5

Tip: using PrestaShop's SQL Manager to export detailed orders

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Create an account or sign in to comment

Create an account

Sign in