Jump to content
Beluga

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

Recommended Posts

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 by Beluga (see edit history)

Share this post


Link to post
Share on other sites

Thank you you are my HERO.

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

 

 

 

Share this post


Link to post
Share on other sites

 

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

  • Like 1

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

If you want more info write PM thanks

  • Like 1

Share this post


Link to post
Share on other sites

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 by Beluga (see edit history)

Share this post


Link to post
Share on other sites

How can I export:

 

 

Firstname,

Lastname,
Address1,

Address2,
Cyti,

Zip code,

Country,

Product_name, 

Quantity,

Order number

 

Help Me Please! 

 

Share this post


Link to post
Share on other sites

How can I export:

 

 

Firstname,

Lastname,

Address1,

Address2,

Cyti,

Zip code,

Country,

Product_name, 

Quantity,

Order number

 

Help Me Please! 

 

write PM please

Share this post


Link to post
Share on other sites

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 by Beluga (see edit history)
  • Like 1

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

 

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.

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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 by FERMB (see edit history)
  • Like 1

Share this post


Link to post
Share on other sites

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 by cooliyev (see edit history)

Share this post


Link to post
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

×

Important Information

Cookies ensure the smooth running of our services. Using these, you accept the use of cookies. Learn More