Jump to content

[SOLVED] How to export detailed orders data


Recommended Posts

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 :ph34r: . 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 by Nandos (see edit history)

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites
  • 3 weeks later...

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 :)

Share this post


Link to post
Share on other sites
  • 8 months later...

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

Share this post


Link to post
Share on other sites
  • 3 years later...
  • 1 month later...
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!

Share this post


Link to post
Share on other sites
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

 

Share this post


Link to post
Share on other sites
  • 1 month later...
Posted (edited)

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

Share this post


Link to post
Share on other sites
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

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
×
×
  • Create New...

Important Information

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