Unbale to export orders with amount of products per order

3 years 11 months ago #346629 by maartenxbosmans@gmail.com

Unbale to export orders with amount of products per order was created by maartenxbosmans@gmail.com

-- HikaShop version -- : 4.6.2
-- Joomla version -- : 4.2.5
-- PHP version -- : 8.1.11

I am trying to look for a way to perform an export of our orders which shows per order, how many of each product was chosen.
I am currently doing this with a Mass Action, using "order" as the datasource and only exporting order_id, order_product_code and order_product_quantity

Let's say I am only selling 4 items: Coke, Sprite, Milk and Juice.

The first customer places an order for 3 cokes
The next customer orders 1 juice and 1 sprite
The next customer orders 4 times milk
The next customer orders 1 Sprite + 1 Coke + 2 Juices and 5 times Milk
The last customer orders 2 times Sprite and 1 Milk

In an ideal world, I would have an export showing the following: (see attached screenshot 1)

With Hikashop, the best I seem to get is: (see attached screenshot 2)

ID 1 only has data for the fields relating to item1 since the customer ordered 1 product
ID 2 has data for the fields item1 and item2 since the customer ordered 2 products
...
ID 4 has data for all the fields since the customer ordered 4 products

Hikashop uses the order in which a customer adds products to his cart to populate the fields item1_productcode, item2_productcode etc..
Item1_productcode can, in my example, contain 4 possible values..
Since the amount of columns is different per row and since the values in the productcode or set based on the ordere a product is added, it is impossible with Excel to transform the data into my example above 'for the ideal world'.

What I would need is the 4 possible values (Coke, Sprite, Milk, Juice) as the column header and per order (row) the amount of articles that was chosen for that product.

Does anybody have an idea on how to create this export which seems to be rather logical functionality...
Any help would be much appreciated!

Please Log in or Create an account to join the conversation.

nicolas
Offline

3 years 11 months ago #346635 by nicolas

Replied by nicolas on topic Unbale to export orders with amount of products per order

Hi,

I don't see a way to do that inside HikaShop.
It's possible to generate a report to get the number of sales of each products (one per row) like this:

Code:

Name Quantity
prod1  1
prod2  5
prod3  4
...

But there is no option to keep it split by order.

That would require a complex MySQL query like this:

Code:

SELECT o.order_id,
       SUM(CASE WHEN op.product_id = XX THEN op.order_product_quantity ELSE '' END) as Coke,
       SUM(CASE WHEN op.product_id = YY THEN op.order_product_quantity ELSE '' END) as Sprite,
       SUM(CASE WHEN op.product_id = ZZ THEN op.order_product_quantity ELSE '' END) as Milk,
       SUM(CASE WHEN op.product_id = AA THEN op.order_product_quantity ELSE '' END) as Juice,
FROM  #__hikashop_order o JOIN 
      #__hikashop_order_product op
      ON op.order_id = o.order_id
GROUP BY o.order_id
ORDER BY o.order_id;

where XX, YY, ZZ and AA would be the id of the products Coke, Sprite, Milk and Juice, and #__ the prefix of your tables (setting in the joomla configuration).
You could run that MySQL query in your PHPMyAdmin to get the export you want.

Please Log in or Create an account to join the conversation.

Time to create page: 0.130 seconds