Exporting all details from all orders using SQL

[Minimonkay]

Hey,

I'm on a steep learning curve with Thirty Bees, it's been great so far but I've hit a snag!

I want to export all my orders into one Spreadsheet where I can see all the different details, including private notes, delivery messages and personalised text.
Along with all the normal bits and pieces like order number and name and address etc.

I've found this from another post and I've altered it a little, but can't work out how to add private notes, delivery messages and personalised text...

The other issue is it's currently giving me 9 lines per individual product order. Is there a way to stop that happening without losing any info? For example I would like to see 3 lines if someone ordered 3 of exactly the same product. currently I would essentially see the same line 27 times. (I only say this because I could easily delete duplicate lines in my spreadsheet, but I don't want to accidentally delete orders if they do exist but appear the same on the spreadsheet)

Quote

    SELECT d.id_order, os.name AS payment, 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_name, g.id_customer AS CustomerID, CONCAT_WS(' ', ad.address1, ad.address2, ad.city, ad.postcode, ad.other, 'Mobile: ', ad.phone_mobile) AS Delivery_Address, CONCAT_WS(' ', ai.address1, ai.address2, ai.city, ai.postcode, ai.other, 'Mobile: ', ai.phone_mobile) AS Invoice_Address, gl.name AS group_name, s.quantity AS quantity_in_stock, g.email
    FROM order_detail d
    LEFT JOIN orders o ON (d.id_order = o.id_order)
    LEFT JOIN address ad ON (o.id_address_delivery = ad.id_address)
    LEFT JOIN address ai ON (o.id_address_invoice = ai.id_address)
    LEFT JOIN stock_available s ON (d.product_id = s.id_product)
    LEFT JOIN customer g ON (o.id_customer = g.id_customer)
    LEFT JOIN group_lang gl ON (g.id_default_group = gl.id_group) AND gl.name LIKE 'piiri%'
    LEFT JOIN order_state_lang os ON (o.current_state = os.id_order_state)
    WHERE os.id_lang = 1

I've searched a lot about SQL and what it means but I think I'd learn quicker if someone was able to help me build my query in relation to Thirtybees.

The forum has been a great source of info fo me so far! It's a great piece of software!

[haylau]

Sorry, can’t help with SQL, but you could look at this fab module

https://www.getdatakick.com/

A free trial means you will be able to see if it will export what you need

[Mediacom87]

Hi,

i made an article about this last year : https://www.mediacom87.fr/en/tip-csv-export-of-order-details-on-prestashop-and-thirtybees/

[Mark]

5 hours ago, haylau said:

Sorry, can’t help with SQL, but you could look at this fab module

https://www.getdatakick.com/

A free trial means you will be able to see if it will export what you need

Yes the getdatakick module is essential for all data management really, importing, exporting, mass updates, reporting, 3rd party integrations, Without it you could only run a limited website.

[Minimonkay]

These are all fantastic suggestions thank you.
I'm having a look at datakick now.

 

7 hours ago, Mediacom87 said:

Hi,

i made an article about this last year : https://www.mediacom87.fr/en/tip-csv-export-of-order-details-on-prestashop-and-thirtybees/

I've just tried your code Mediacom87 and it returns 'FROM does not exist'
I've renamed it order_detail instead of the prefix ps_ as I'm running thirtybees instead of prestashop. I've also changed the other table names. But still no luck, any suggestions?

 

Thanks all!

[datakick]

    SELECT d.id_order, os.name AS payment, 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_name, g.id_customer AS CustomerID, CONCAT_WS(' ', ad.address1, ad.address2, ad.city, ad.postcode, ad.other, 'Mobile: ', ad.phone_mobile) AS Delivery_Address, CONCAT_WS(' ', ai.address1, ai.address2, ai.city, ai.postcode, ai.other, 'Mobile: ', ai.phone_mobile) AS Invoice_Address, gl.name AS group_name, s.quantity AS quantity_in_stock, g.email
    FROM order_detail d
    LEFT JOIN orders o ON (d.id_order = o.id_order)
    LEFT JOIN address ad ON (o.id_address_delivery = ad.id_address)
    LEFT JOIN address ai ON (o.id_address_invoice = ai.id_address)
    LEFT JOIN stock_available s ON (d.product_id = s.id_product)
    LEFT JOIN customer g ON (o.id_customer = g.id_customer)
    LEFT JOIN group_lang gl ON (g.id_default_group = gl.id_group) AND gl.name LIKE 'piiri%'
    LEFT JOIN order_state_lang os ON (o.current_state = os.id_order_state)
    WHERE os.id_lang = 1

The tables that are causing you issues are group_lang and stock_available.

Table group_lang has compound primary key  (`id_group`,`id_lang`), but  you join in only using gl.id_group. You need to include language condition as well:

LEFT JOIN group_lang gl ON (g.id_default_group = gl.id_group and gl.id_lang = 1)

Stock available is more complicated. Depending on you multistore setup, there can be a lot of entries for each product. First of all, for every combination there is a separate entry, and there is also an entry for combination with id = 0. If you use multistore, and each store has it's own quantity, then there are other records identified by id_shop or id_shop_group as well.

If you don't use multistore, then you should just add id_product_attribute into the join clause:

LEFT JOIN stock_available s ON (d.product_id = s.id_product AND s.id_product_attribute = d.product_attribute_id)

Alternatively, you can get rid of this table from the 'FROM', and use subquery. Instead of s.quantity AS quantity_in_stock, you would put this expression instead (and remove LEFT JOIN stock_available s ON from FROM clause):

// this to calculate quantity for product

(SELECT sum(s.quantity) FROM stock_available s WHERE d.product_id = s.id_product) as quantity_in_stock

// or this to calculate quantity available per combination

(SELECT sum(s.quantity) FROM stock_available s WHERE d.product_id = s.id_product AND s.id_product_attribute = d.product_attribute_id) as quantity_in_stock

[Minimonkay]

19 minutes ago, datakick said:

    SELECT d.id_order, os.name AS payment, 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_name, g.id_customer AS CustomerID, CONCAT_WS(' ', ad.address1, ad.address2, ad.city, ad.postcode, ad.other, 'Mobile: ', ad.phone_mobile) AS Delivery_Address, CONCAT_WS(' ', ai.address1, ai.address2, ai.city, ai.postcode, ai.other, 'Mobile: ', ai.phone_mobile) AS Invoice_Address, gl.name AS group_name, s.quantity AS quantity_in_stock, g.email
    FROM order_detail d
    LEFT JOIN orders o ON (d.id_order = o.id_order)
    LEFT JOIN address ad ON (o.id_address_delivery = ad.id_address)
    LEFT JOIN address ai ON (o.id_address_invoice = ai.id_address)
    LEFT JOIN stock_available s ON (d.product_id = s.id_product)
    LEFT JOIN customer g ON (o.id_customer = g.id_customer)
    LEFT JOIN group_lang gl ON (g.id_default_group = gl.id_group) AND gl.name LIKE 'piiri%'
    LEFT JOIN order_state_lang os ON (o.current_state = os.id_order_state)
    WHERE os.id_lang = 1

The tables that are causing you issues are group_lang and stock_available.

Table group_lang has compound primary key  (`id_group`,`id_lang`), but  you join in only using gl.id_group. You need to include language condition as well:

LEFT JOIN group_lang gl ON (g.id_default_group = gl.id_group and gl.id_lang = 1)

Stock available is more complicated. Depending on you multistore setup, there can be a lot of entries for each product. First of all, for every combination there is a separate entry, and there is also an entry for combination with id = 0. If you use multistore, and each store has it's own quantity, then there are other records identified by id_shop or id_shop_group as well.

If you don't use multistore, then you should just add id_product_attribute into the join clause:

LEFT JOIN stock_available s ON (d.product_id = s.id_product AND s.id_product_attribute = d.product_attribute_id)

Alternatively, you can get rid of this table from the 'FROM', and use subquery. Instead of s.quantity AS quantity_in_stock, you would put this expression instead (and remove LEFT JOIN stock_available s ON from FROM clause):

// this to calculate quantity for product

(SELECT sum(s.quantity) FROM stock_available s WHERE d.product_id = s.id_product) as quantity_in_stock

// or this to calculate quantity available per combination

(SELECT sum(s.quantity) FROM stock_available s WHERE d.product_id = s.id_product AND s.id_product_attribute = d.product_attribute_id) as quantity_in_stock

Thanks!
The stock available change helped with the 9 lines per order problem. I don't understand what the language condition would fix? It seems to generate ok without changing that line, and when I change it it returns the error:

1. When multiple tables are used, each attribute must refer back to a table.
2. Undefined "checkedFrom" error

My next issue is getting the private note I've added to some orders..

I'm also building a list in datakick as this seems like a very useful module, but again I can't find the private note option in any of the data sets?

[Mark]

6 hours ago, Minimonkay said:

I'm also building a list in datakick as this seems like a very useful module, but again I can't find the private note option in any of the data sets?

The customer data isn't in any datasets, I too would love to see this, particularly notes also, in a dataset.

[datakick]

2 minutes ago, Mark said:

The customer data isn't in any datasets, I too would love to see this, particularly notes also, in a dataset.

Customer data are exposed. Customer service messages aren't. 

For some reason, order notes are stored as a customer service message/thread.

[Minimonkay]

Hey Petr,

Thanks for the info!
So am I right in thinking there's no way for me to pull the notes I add to people's orders into a spreadsheet?

Also I have a custom field on the product that the customer fills in, is there a way of bringing that in?

Thank you for your help!

[Minimonkay]

On 12/22/2021 at 3:45 PM, Mark said:

The customer data isn't in any datasets, I too would love to see this, particularly notes also, in a dataset.

Hey Mark!

I don't know if this helps you at all but i managed to pull the note for customer data with an SQL query. It's in the table 'customer' and the attribute is 'note'.

Hope this helps!

[Mark]

Great thanks, Within the datakick module or just using by using db directly?

[Minimonkay]

Unfortunately I couldn't find it in the datakick module it was in the SQL manager within the thirty bees back office. 

[Mark]

On 12/30/2021 at 10:32 PM, Minimonkay said:

Unfortunately I couldn't find it in the datakick module it was in the SQL manager within the thirty bees back office. 

Hopefully at some point @datakick will add it to his module as I use the Datakick module for everything and not the db directly. He's estimated to me it involves a couple hours work to do it.With at least a couple of us wanting it , it may yet make it on the the list 🙂