Statistic items/customer

[kinling]

Hi,

I thought it should be possible but cannot find the way how:

 

I need a csv/excel with that layout:

 

Customername

    ordered items:

        item number: nnn, name of the item, total number, single price
        item number: nnn, name of the item, total number, single price

        item number: nnn, name of the item, total number, single price

Customername

    ordered items:

        item number: nnn, name of the item, total number, single price
        item number: nnn, name of the item, total number, single price

        item number: nnn, name of the item, total number, single price

Customername

    ordered items:

        item number: nnn, name of the item, total number, single price
        item number: nnn, name of the item, total number, single price

        item number: nnn, name of the item, total number, single price

.....

 

usw....

save as csv

 

is that possible without using sql (I dont know how to use sql :-(

 

Thanks a lot for reading my post, for tips, links or what ever!

Klaus Fuchs

German, living and working in Malaysia

 

[JeredBolton]

CSV files can't be structured like that, you have to repeat the customer's name (and other details) for each line.

 

This post contains a useful SQL statement which I've modified for you:

SELECT o.reference as order_ref, pl.name as product_name, od.product_quantity, cs.firstname, cs.lastname, DATE(o.date_add) AS order_date, ROUND (od.total_price_tax_incl, 2) AS price_with_tax,  cu.name AS curr, od.product_reference
FROM ps_product p
LEFT JOIN ps_product_lang pl ON p.id_product = pl.id_product
LEFT JOIN ps_order_detail od ON p.id_product = od.product_id
LEFT JOIN ps_orders o ON o.id_order = od.id_order
LEFT JOIN ps_currency cu ON cu.id_currency = o.id_currency
JOIN ps_order_detail_tax dt ON od.id_order_detail = dt.id_order_detail
JOIN ps_tax pt ON pt.id_tax = dt.id_tax
JOIN ps_customer cs on o.id_customer=cs.id_customer
JOIN ps_address ad on o.id_address_delivery=ad.id_address
JOIN ps_country_lang pc on ad.id_country=pc.id_country
 JOIN ps_country_lang ps on pl.id_lang=pc.id_lang
WHERE  o.current_state = 2 OR o.current_state =  3 OR o.current_state = 4 OR o.current_state = 5 OR o.current_state = 9 OR o.current_state = 12 OR o.current_state = 13 OR o.current_state = 16 
GROUP BY od.id_order_detail
ORDER BY cs.id_customer, o.date_add;

Add this to the SQL Manager in the Advanced Parameters section in the back office and you'll be able to extract the information you need.

 

Note, that in order to export the results of the query, your web server must have write permissions on the export directory in your admin directory.

[kinling]

Wow!

this is AMAZING!

Thanks a lot, I better learn a bit of sql....

It seems you filter those orders with 0 euro, isnt it?

Where can i change it, becasue in my special case i also need those who order free items too, 

Again:
a m a z i n g

 

Reg. Klaus

[JeredBolton]

In which case, try this:

 SELECT o.reference as order_ref,  pl.name as product_name, od.product_quantity, cs.firstname, cs.lastname, DATE(o.date_add) AS order_date, ROUND (od.total_price_tax_incl, 2) AS price_with_tax,  cu.name AS curr, od.product_reference
FROM ps_product p
LEFT JOIN ps_product_lang pl ON p.id_product = pl.id_product
LEFT JOIN ps_order_detail od ON p.id_product = od.product_id
LEFT JOIN ps_orders o ON o.id_order = od.id_order
LEFT JOIN ps_currency cu ON cu.id_currency = o.id_currency
JOIN ps_customer cs on o.id_customer=cs.id_customer
JOIN ps_address ad on o.id_address_delivery=ad.id_address
JOIN ps_country_lang pc on ad.id_country=pc.id_country
 JOIN ps_country_lang ps on pl.id_lang=pc.id_lang
WHERE  o.current_state = 2 OR o.current_state =  3 OR o.current_state = 4 OR o.current_state = 5 OR o.current_state = 9 OR o.current_state = 12 OR o.current_state = 13 OR o.curr ent_state = 16 
GROUP BY od.id_order_detail
ORDER BY cs.id_customer, o.date_add ;

The original query was including the level of tax, which on free products is zero, which is why those orders were being excluded.