[SOLVED] How to export detailed orders data

[Nandos]

Hello everyone, i'm new here 

We're checking PS features to decide if this will be part of our new ecommerce, and so far it looks very nice and complete, but we need an important functionality we didn't find in the back office  . Let me explain better.

Periodically, we send the details of our orders to a research institute for data mining. They analyze those info and we receive a service from them in terms of market and business decisions and so on...

Anyway, we need to send them weekly only the quantities of orders (how many orders we do in a week) and the associated products codes into the orders.

So far we only find an export for numbers of orders, not complete. Is there a way to make a customizable exporting of orders? Some coding involved? If i have to install a module for that, link it to me please, since i didn't find what i need.

Thanks in advance 

Edited June 17, 2016 by Nandos (see edit history)

[shokinro]

I think you can write specific SQL statement to run on your store to get what ever results you want.

[Nandos]

I think you can write specific SQL statement to run on your store to get what ever results you want.

 

Can you help me with that? Since i'm new with PS, i still don't know where it stores these kind of data, in which fields and in which tables etc... This "hypothetical" query should generate results for how many orders we had in a week and the associated products codes (description and EAN) in each order. For example, a table with these fields:

 

 

       columns

line

       product order data | product code | EAN | product name | quantity | price no vat for single product | price no vat for multiple of that product if more are present *| total order price with vat

              26/05/2016            xxxxx                  xxx             item xxx            yyy                            zz,zz€                                                                  zz,zz€                                                                zz,zz€

              26/05/2016            xxxxx                  xxx             item xxx            yyy                            zz,zz€                                                                  zz,zz€                                                                zz,zz€

                     ...   

*if 1, show the same price as single product

 

Then i want to export it in a csv file.

It would be great an help Thanks in advance!

Edited May 26, 2016 by Nandos (see edit history)

[shokinro]

I am sorry, this is not easy to explain and write the SQL in a few minutes. detailed requirements of the CSV export needs to be discusses.

I guess you  you will need to hire someone to help with that if you are not able to coding by yourself.

[Nandos]

I wrote a kind of solution, using the integrated sql manager into the back office: http://pastebin.com/NFNgMP2G (the date window is just an example)

[shokinro]

it looks like a very good solution.congratulations!

for the date, you can use CURDATE() and DATEDIFF() to calculate.

[Nandos]

it looks like a very good solution.congratulations!

for the date, you can use CURDATE() and DATEDIFF() to calculate.

Thanks

Ah ok, i will look at them. In the meanwhile, can you explain me how do they work exactly? Just an example if you can

[shokinro]

CURDATE() will get current date time.  you can find more info here

http://www.w3schools.com/sql/func_curdate.asp

 

You can use DATE_ADD() to get future data of past data of specified data.

http://www.w3schools.com/sql/func_date_add.asp

[Nandos]

CURDATE() will get current date time.  you can find more info here

http://www.w3schools.com/sql/func_curdate.asp

 

You can use DATE_ADD() to get future data of past data of specified data.

http://www.w3schools.com/sql/func_date_add.asp

Thank you, very appreciated 

[shokinro]

you are welcome, hope that helped

[danplant]

Hi guys,

i'm very interested this functionality !

How integrate this in the BO ?

P.S. 1.6.1.7

[Nandos]

Hi guys,

i'm very interested this functionality !

How integrate this in the BO ?

P.S. 1.6.1.7

Ok, so login into your BO, go to the Advanced Parameters menu, and from there click on "SQL Manager". Into SQL Manager, add a new SQL Query with the button on the top, give it a title and paste there the code i linked before in this thread. Then save it and in the list you will have a new voice (if no queries, you will have just one ID=1), click on it and you will get the result directly into the BO. If you want to export the results, from the list click on the "Export" button and the browser will download a .csv file with the data.

Hope it helps. Cya o/

Edited April 21, 2017 by Nandos (see edit history)

[danplant]

Sounds good !!!!

Very thanks, very helpful !

Regards

[Surffari]

Thanks @Nandos

Just the droids ... I mean the SQL I was looking for!

Few modifications to get what I need (works with 1.7)

[anth55]

On 1/11/2021 at 6:52 PM, Surffari said:

Thanks @Nandos

Just the droids ... I mean the SQL I was looking for!

Few modifications to get what I need (works with 1.7)

Hi, would you please let me know how did you modified the SQL statement so it works in prestashop 1.7 ?

 

Best regards!

[GoPure Kratom]

On 2/27/2021 at 7:30 PM, anth55 said:

Hi, would you please let me know how did you modified the SQL statement so it works in prestashop 1.7 ?

 

Best regards!

Here's SQL script I made to export in a format I needed for a particular purpose.

You can modify the WHERE clause and the output fields according to your needs.

SELECT 
	 o.id_order AS WebshopOrderId,
    ad.firstname "ShippingFirstName",
    "" AS "ShippingMiddleName",
    ad.LastName "ShippingLastName",
	ad.address1 AS "ShippingStreet",
	ad.address2  AS "ShippinghouseNo",
	"" AS "ShippingHouseNoAddition",
    ad.postcode "ShippingPostalCode",
    ad.city "ShippingCity",
	"" AS "ShippingState",
    cn.iso_code AS "ShippingCountry",
    g.email AS "ShippingEmail",
	ad.phone AS "ShippingPhone",
	"" AS "ShippingComment",
	"0" AS "B2B",
	d.product_reference AS "ArticleSKU",
    d.product_quantity AS "OrderedQty",
    o.reference AS "Reference",
    "" AS "Comment",
    "" AS "ShipmentDate",
    "" AS "ShipperCode",
    "" AS "ShipperRef",
    "0" AS "IsQuarantaineOrder",
    "" AS "InvoiceCompany",
    "" AS "InvoiceFirstName",
    "" AS "InvoiceMiddleName",
    "" AS "InvoiceLastName",
	"" AS "InvoiceStreet",
	"" AS "InvoicehouseNo",
	"" AS "InvoiceHouseNoAddition",
    "" AS "InvoicePostalCode",
    "" AS "InvoiceCity",
	"" AS "InvoiceState",
    "" AS "InvoiceCountry",
    "" AS "InvoiceEmail",
    "" AS "InvoicePhone",
    "" AS "InvoiceDebtorNumber",
    "" AS "InvoiceComment",
    "" AS "ShippingCompany"
FROM
    pscw_order_detail d
LEFT JOIN pscw_orders o ON
    (d.id_order = o.id_order)
LEFT JOIN pscw_address ad ON
    (o.id_address_delivery = ad.id_address)
LEFT JOIN pscw_address ai ON
    (o.id_address_invoice = ai.id_address)
LEFT JOIN pscw_country cn ON
    (ad.id_country = cn.id_country)
LEFT JOIN pscw_stock_available s ON
    (d.product_id = s.id_product)
LEFT JOIN pscw_customer g ON
    (o.id_customer = g.id_customer)
LEFT JOIN pscw_group_lang gl ON
    (g.id_default_group = gl.id_group)
LEFT JOIN pscw_order_state_lang os ON
    (o.current_state = os.id_order_state)
WHERE
    1
GROUP BY
    o.reference,
    d.product_reference
ORDER BY
    d.id_order
DESC

 

[DoGGoD]

Hi,

The original code kind of works. I have this issue that it does not give me the orders, where the customer has not made the payment yet. 

How can I modify the code so it gives me those orders as well, where the payment (invoice) is not yet made?

Thanks!

Edit:

I removed the d.delivery_date, c.date_add and INNER JOIN ps_order_invoice AS c ON c.id_order = b.id_order so it gives me all the sold products, even when there is no payment involved.

SELECT a.id_order, a.date_add, c.delivery_date, c.date_add, b.product_reference, b.product_name, b.product_quantity, b.product_price, a.total_products_wt, a.total_paid
FROM ps_orders AS a
INNER JOIN ps_order_detail AS b ON b.id_order = a.id_order
INNER JOIN ps_order_invoice AS c ON c.id_order = b.id_order
WHERE a.date_add BETWEEN "2016-05-31 08:30:00" AND "2016-06-11 18:30:00"

Edited April 14, 2021 by DoGGoD (see edit history)

[GoPure Kratom]

17 minutes ago, DoGGoD said:

Hi,

The original code kind of works. I have this issue that it does not give me the orders, where the customer has not made the payment yet. 

How can I modify the code so it gives me those orders as well, where the payment (invoice) is not yet made?

Thanks!

Edit:

I removed the d.delivery_date, c.date_add and INNER JOIN ps_order_invoice AS c ON c.id_order = b.id_order so it gives me all the sold products, even when there is no payment involved.

SELECT a.id_order, a.date_add, c.delivery_date, c.date_add, b.product_reference, b.product_name, b.product_quantity, b.product_price, a.total_products_wt, a.total_paid
FROM ps_orders AS a
INNER JOIN ps_order_detail AS b ON b.id_order = a.id_order
INNER JOIN ps_order_invoice AS c ON c.id_order = b.id_order
WHERE a.date_add BETWEEN "2016-05-31 08:30:00" AND "2016-06-11 18:30:00"

It's been a while since I did some SQL, but I think when you include hte ps_order_invoice, the not paid orders are not included since they don't have an invoice yet.

Maybe check LEFT JOIN to join the tables in an order that includes also the ones without an invoice