List of Products sold today

[iclubbers]

Hello, and thanks everybody for this forum!

 

Im running a prestashop POS module for the cash counter in my physical shop.

 

When the staff sell the items, they have to restock the racks.

Several times every day, when they have some free time, lets say every hour, 

they go to the warehouse and take the clothes.

 

I cant find a fast way to get the last products sold in a list, arranged in columns to make it easier

for the staff to collect the clothes depending the area of the warehouse they are, or the categories.

 

Exporting that list to a html, or excel would be nice, anything that can let them tick the lines they already 

restocked to dont get confused next time they do that task.

 

At the moment they write down the items they sell in a piece of paper, and is a bit annoying because 

a lot of mistakes and , well, having this powerfull tool and then writting down, when we could have 

a tablet to restock easily is... a bit embarassing

 

Thanks a lot for your time, 

Regards

[gabdara]

You could use the SQL Manager to create a query.

 

SQL query name: Products sold today

SQL query:

SELECT w.id_warehouse AS "Warehouse ID", w.name AS "Warehouse Name",  o.id_order AS "Order ID", od.product_id AS "Product ID",od.product_attribute_id AS "Combination ID", od.product_name AS "Product Name", od.product_quantity AS Quantity
FROM ps_order_detail od
INNER JOIN ps_orders o ON (o.id_order = od.id_order)
LEFT JOIN ps_warehouse w ON (w.id_warehouse = od.id_warehouse)
WHERE o.date_add > DATE_FORMAT(NOW(),'%Y-%m-%d')
ORDER BY w.id_warehouse ASC, o.id_order ASC;

After you Save, you can click on the query and get the list of products sold today in browser or you can click on Export and get a CSV file that can be opened with any "excel" app.

 

This query can be further adapted for your needs, now it lists all the products sold today from all the warehouses ordered by the warehouse id and order id.

[iclubbers]

hello!

Thankyou again for your help, but i get this error...

 

 

La tabla "ps_order_detail" no existe.

[gabdara]

You might have another database prefix, the default is ps_. Where you create/edit a SQL query, look under for section List of MySQL Tables. There you can see what form your tables have and adjust the query.

[iclubbers]

now is working, thank you so much!

i didnt know about this sql tool ... it sems to be really powerful, 

but difficult to learn...

 

just one more question... if i want to see a longer list.. lets say since 2 days ago until now...

what should i change?

 

thanks a lot

[gabdara]

For 2 or more days ago modify the WHERE line and adjust the date comparison:

WHERE o.date_add > DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 2 DAY),'%Y-%m-%d')

Also you could add on the SELECT line o.date_add AS "Date" to list the date when the order was placed.

[mktm20111]

On 9/20/2016 at 8:06 PM, gabdara said:

You could use the SQL Manager to create a query.

 

SQL query name: Products sold today

SQL query:

SELECT w.id_warehouse AS "Warehouse ID", w.name AS "Warehouse Name",  o.id_order AS "Order ID", od.product_id AS "Product ID",od.product_attribute_id AS "Combination ID", od.product_name AS "Product Name", od.product_quantity AS Quantity
FROM ps_order_detail od
INNER JOIN ps_orders o ON (o.id_order = od.id_order)
LEFT JOIN ps_warehouse w ON (w.id_warehouse = od.id_warehouse)
WHERE o.date_add > DATE_FORMAT(NOW(),'%Y-%m-%d')
ORDER BY w.id_warehouse ASC, o.id_order ASC;

After you Save, you can click on the query and get the list of products sold today in browser or you can click on Export and get a CSV file that can be opened with any "excel" app.

 

This query can be further adapted for your needs, now it lists all the products sold today from all the warehouses ordered by the warehouse id and order id.

this is great thank you for this but is there a way to make products stack ? to make a shopping list? lik ei have 3 customers who ordered the same product but instead of have 1 product on each seperate lines to have them stack to see exatcly what you have.

for example

Product A which was ordered by 3 different people will be like this at the moment

name                  quantity

product A               2
product A               5
product A               1

but instead i need it to be like this

name                  quantity

product A                8

as i don't have stock and have to go shopping for my customers i am trying to find a way to make a shopping list, what you did above is great but isn't easy whren having to go shopping 

hope i was clear in what i am looking for

[mktm20111]

On 9/20/2016 at 8:06 PM, gabdara said:

You could use the SQL Manager to create a query.

 

SQL query name: Products sold today

SQL query:

SELECT w.id_warehouse AS "Warehouse ID", w.name AS "Warehouse Name",  o.id_order AS "Order ID", od.product_id AS "Product ID",od.product_attribute_id AS "Combination ID", od.product_name AS "Product Name", od.product_quantity AS Quantity
FROM ps_order_detail od
INNER JOIN ps_orders o ON (o.id_order = od.id_order)
LEFT JOIN ps_warehouse w ON (w.id_warehouse = od.id_warehouse)
WHERE o.date_add > DATE_FORMAT(NOW(),'%Y-%m-%d')
ORDER BY w.id_warehouse ASC, o.id_order ASC;

After you Save, you can click on the query and get the list of products sold today in browser or you can click on Export and get a CSV file that can be opened with any "excel" app.

 

This query can be further adapted for your needs, now it lists all the products sold today from all the warehouses ordered by the warehouse id and order id.

here is a screen shot to show what i mean
 

[PietroElle]

On 8/12/2019 at 5:32 AM, mktm20111 dice:

SELEZIONA w . id_warehouse AS "ID magazzino" , w . nome AS "Nome magazzino" , o . id_order AS "ID ordine" , od . product_id AS "ID prodotto" , od . product_attribute_id AS "ID combinazione" , od . product_name AS "Nome prodotto" , od . product_quantity AS Quantity DA ps_order_detail od INNER JOIN ps_orders o ON ( o . Id_order = od . Id_order ) LEFT JOIN ps_warehouse w ON ( w . Id_warehouse = od . Id_warehouse ) dove o . date_add > DATE_FORMAT ( NOW (), '% Y-% m-% d' ) ORDINA PER w . id_warehouse ASC , o . id_order ASC ;

 

On 8/12/2019 at 5:57 AM, mktm20111 dice:

here is a screen shot to show what i mean
 

 

On 8/12/2019 at 5:32 AM, mktm20111 dice:

this is great thank you for this but is there a way to make products stack ? to make a shopping list? lik ei have 3 customers who ordered the same product but instead of have 1 product on each seperate lines to have them stack to see exatcly what you have.

for example

Product A which was ordered by 3 different people will be like this at the moment

name                  quantity

product A               2
product A               5
product A               1

but instead i need it to be like this

name                  quantity

product A                8

as i don't have stock and have to go shopping for my customers i am trying to find a way to make a shopping list, what you did above is great but isn't easy whren having to go shopping 

hope i was clear in what i am looking for

 

On 8/12/2019 at 5:32 AM, mktm20111 dice:

this is great thank you for this but is there a way to make products stack ? to make a shopping list? lik ei have 3 customers who ordered the same product but instead of have 1 product on each seperate lines to have them stack to see exatcly what you have.

for example

Product A which was ordered by 3 different people will be like this at the moment

name                  quantity

product A               2
product A               5
product A               1

but instead i need it to be like this

name                  quantity

product A                8

as i don't have stock and have to go shopping for my customers i am trying to find a way to make a shopping list, what you did above is great but isn't easy whren having to go shopping 

hope i was clear in what i am looking for

Hello

Please I would be grateful if you could give me this order table by entering the customer's name.

However, the table must contain the orders of the week.

Can you help me?

[PietroElle]

1 minuto fa, pietroelle dice:

 

 

 

Hello

Please I would be grateful if you could give me this order table by entering the customer's name.

However, the table must contain the orders of the week.

Can you help me?

Sorry I intend to add the name of the product purchased to this table, the customer's name is already there