Stock correction between Prestashop and POS system

[Frank Begbie]

We have our own checkout system with an interface to Prestashop to correct the stock in Prestashop when something is sold at the checkout. Now we want to debit the quantities from a Prestashop order from the inventory in the POS system. The order reading works so far, but how do I determine whether the order has been deleted. What is the best way to proceed? Which table does it make sense to go through so that you only debit the amount once or add it to the cancellation?

 

Wir haben ein eigenes Kassensystem mit einer Schnittstelle zum Prestashop um den Bestand in Prestashop zu korrigieren wenn an der Kasse was verkauft wird. Nun möchten wir die Mengen  aus einer Order Prestashop von dem Bestand im Kassensystem abbuchen. Die Orderauslesen klappt schon soweit aber wie stelle ich fest ob die Order  gelöscht wurde. Wie ist da ambesten die Vorgehensweise? Welche Tabelle ist das sinnvoll diese durchzulaufen damit man die Menge nur einmal abbbucht bzw. beim Storno dazubucht?

[s4lvozesta]

to me, this is interesting. Not directly related by maybe some idea : use hook

 

https://stackoverflow.com/questions/36904111/avoid-decrease-of-product-quantity-when-the-order-status-is-still-process

[Frank Begbie]

On 6/23/2023 at 8:30 PM, s4lvozesta said:

to me, this is interesting. Not directly related by maybe some idea : use hook

 

https://stackoverflow.com/questions/36904111/avoid-decrease-of-product-quantity-when-the-order-status-is-still-process

Thank you for information.I should have written that I have to implement the whole thing with web api (rest) with Vb6.

[ps8modules]

Hi.

I see a more unreasonable solution to program a small module and communicate via vb6. Several hooks and Prestashop functions can be used in the module. It is nothing complicated and within an hour, rather two hours, the module will be programmed.

[Frank Begbie]

13 hours ago, ps8moduly.cz said:

Hi.

I see a more unreasonable solution to program a small module and communicate via vb6. Several hooks and Prestashop functions can be used in the module. It is nothing complicated and within an hour, rather two hours, the module will be programmed.

The connection to the Prestashop already exists. Basically I wanted to know which statuses I have to check to be able to determine whether the order has been canceled or not. I may also need to iterate over another table like Order History. Working with vb6 and the Prestashop API must prevent the order from being debited twice, the program runs every 15 minutes for the comparison. Going through all orders works so far. My problem is how do I recognize that it is a credit, cancellation?

[ps8modules]

Hi.

In the ps_orders table it is current_state, in ps_order_history it is the id_order_state column. You can find the ID of the order states and names in the administration in the order settings, or in the table ps_order_state and ps_order_state_lang.

[Frank Begbie]

2 hours ago, ps8moduly.cz said:

Hi.

In the ps_orders table it is current_state, in ps_order_history it is the id_order_state column. You can find the ID of the order states and names in the administration in the order settings, or in the table ps_order_state and ps_order_state_lang.

Hello
Thanks for the answer. Can't access the tables directly but only through Prestashop's WebAPI service

[ps8modules]

You originally asked "Which table does it make sense to go through so that you only debit the amount once or add it to the cancellation?". Now something else. If you know the order number, you can find out all the information via the web service. You will get the current_state response in the XML response. You can find the ID current state as I wrote to you.

[s4lvozesta]

Hi @Frank Begbie ,

In any case, we should be able to 'extend' the webservice
https://devdocs.prestashop-project.org/8/modules/concepts/webservice/

Anyway, in one order, there could be many products. Hence, we are looking at several scenarios here : 
- cancelling the whole order
- cancelling one product 
- reducing product qty (e.g. order 3 but cancel 1)

I am sure you have thought about this and I think it is not that simple.
Maybe this fields can start your day : 
- product_quantity_return
- product_quantity_refunded
https://devdocs.prestashop-project.org/8/webservice/resources/order_details/

 

Cheers!

[ps8modules]

You wrote this in another forum: "I would like to write off the quantity from the orders from the inventory in our cash register system. So far it works, but how do I know that the order or position has been deleted. When I delete an order, does it say cancel the order? How should I proceed?" Prestashop has a mechanism that returns the unsold quantity of products from the canceled order back to the warehouse when the order is cancelled. So, if you do an inventory in a store and you need to compare the quantity in an e-shop, that's a completely different matter. Please state exactly what you need.

[Frank Begbie]

On 6/30/2023 at 7:30 AM, ps8moduly.cz said:

You wrote this in another forum: "I would like to write off the quantity from the orders from the inventory in our cash register system. So far it works, but how do I know that the order or position has been deleted. When I delete an order, does it say cancel the order? How should I proceed?" Prestashop has a mechanism that returns the unsold quantity of products from the canceled order back to the warehouse when the order is cancelled. So, if you do an inventory in a store and you need to compare the quantity in an e-shop, that's a completely different matter. Please state exactly what you need.

Hello
thank you very much for your answers. Our checkout system should be in charge when it comes to article inventory. What I would like is that the inventory corrections in the shop are debited by the orders in the POS system or additionally booked if canceled and the whole thing only once per order. But now I've found that you can change the quantity of existing orders even though an invoice has already been written and paid. Now that makes it difficult.

[ps8modules]

Hi.

That's what I'm trying to explain to you. I also program for Windows, Android and have already programmed cash register systems. I created a Prestashop module and everything is then simple. In the e-shop, a customer with a store address was created and an order was sent from the checkout system to the module and the id_order, reference and status were returned. If the order was canceled in the store, a request was sent to the module and the result was returned Ok, Ko ... As for the inventory, an XML file was generated from the checkout system and the number of products was compared. In the same way, you can also generate XML in Prestashop and have canceled orders listed for a certain period and the like. We don't know how your entire checkout system works, what makes everything possible.

[Frank Begbie]

15 hours ago, ps8moduly.cz said:

Hi.

That's what I'm trying to explain to you. I also program for Windows, Android and have already programmed cash register systems. I created a Prestashop module and everything is then simple. In the e-shop, a customer with a store address was created and an order was sent from the checkout system to the module and the id_order, reference and status were returned. If the order was canceled in the store, a request was sent to the module and the result was returned Ok, Ko ... As for the inventory, an XML file was generated from the checkout system and the number of products was compared. In the same way, you can also generate XML in Prestashop and have canceled orders listed for a certain period and the like. We don't know how your entire checkout system works, what makes everything possible.

Hello
we do not send any orders to the shop. I only have to debit the orders in the shop from the inventory in our cash register system. Which table do I have to go through via the web service api to get to the canceled orders. I can go through the current orders, but the whole thing can only happen once. At the moment I can only see that the canceled orders are only marked by a status. To me it looks like I have to read out all the stock levels in the shop and compare them with the cash register.

[Frank Begbie]

2 hours ago, Frank Begbie said:

Hello
we do not send any orders to the shop. I only have to debit the orders in the shop from the inventory in our cash register system. Which table do I have to go through via the web service api to get to the canceled orders. I can go through the current orders, but the whole thing can only happen once. At the moment I can only see that the canceled orders are only marked by a status. To me it looks like I have to read out all the stock levels in the shop and compare them with the cash register.

The module you keep talking about is it in vb6 and does it work with mysql or is it an addon for prestashop?

[Frank Begbie]

I found that the stock movements table contains everything I need to correct the stock in our cash register. Only when I read the table via the api functions is there no product ID in the xml, no stock movement type, etc. it only contains the stock quantities. That's no use to me either. Read that in via the ID. The data can be seen in the shop. Do I have to import the table differently?

[ps8modules]

Hi.

How would you like us to advise if we don't have any information about your POS system and support.
Each product in the e-shop has a unique identifier and that is the product ID.
Furthermore, it can be used as a unique reference identifier (can be set for combinations) or EAN 13 (can be set for combinations).
Can your POS system do it?

Export us an XML file with products from your POS system (two are enough, where one will be a combination).

Show us your XML that you import into Prestashop via VB6.

I really can't advise you that way.

You give us little information or no information and documents.

This is the same as if you write an email to a car repair shop and tell them that you have a car and the car is red and you have a remote control and a door in it and they won't open and close.

 

Edited July 13, 2023 by ps8moduly.cz (see edit history)

[Frank Begbie]

On 7/13/2023 at 3:45 PM, ps8moduly.cz said:

Hi.

How would you like us to advise if we don't have any information about your POS system and support.
Each product in the e-shop has a unique identifier and that is the product ID.
Furthermore, it can be used as a unique reference identifier (can be set for combinations) or EAN 13 (can be set for combinations).
Can your POS system do it?

Export us an XML file with products from your POS system (two are enough, where one will be a combination).

Show us your XML that you import into Prestashop via VB6.

I really can't advise you that way.

You give us little information or no information and documents.

This is the same as if you write an email to a car repair shop and tell them that you have a car and the car is red and you have a remote control and a door in it and they won't open and close.

Hello again, thank you very much for your replies. My problem is not about creating articles. The items from our checkout are imported as CSV files using the import function. That all works.
The cash register program itself reduces the stock in the shop when a sale takes place at the cash register.
Since the leading merchandise management system is our cash register, the quantities sold that result from the orders in the shop must be corrected in the cash register.
Since I don't notice when an order takes place in the shop, I have to run a program that carries out this correction at regular intervals. I stumbled across the stock_movemnets, which I can address via the API in which all stock changes are stored. Can also read them out, but the product ID and the movement type are missing in the data, so this does not help me. Get me all Movemts and then read the table via the corresponding ID. But as I said, the product ID etc. are missing.

On 7/13/2023 at 3:45 PM, ps8moduly.cz said:

 

 

[Frank Begbie]

2 hours ago, Frank Begbie said:

Hello again, thank you very much for your replies. My problem is not about creating articles. The items from our checkout are imported as CSV files using the import function. That all works.
The cash register program itself reduces the stock in the shop when a sale takes place at the cash register.
Since the leading merchandise management system is our cash register, the quantities sold that result from the orders in the shop must be corrected in the cash register.
Since I don't notice when an order takes place in the shop, I have to run a program that carries out this correction at regular intervals. I stumbled across the stock_movemnets, which I can address via the API in which all stock changes are stored. Can also read them out, but the product ID and the movement type are missing in the data, so this does not help me. Get me all Movemts and then read the table via the corresponding ID. But as I said, the product ID etc. are missing.

 

To make that clear. It is about the following table in which the product_id is not included after reading and also not the management_type to distinguish the bookings. All inventory changes from Prestahop are updated here.

<?xml version="1.0" encoding="UTF-8"?>
<prestashop xmlns:xlink="http://www.w3.org/1999/xlink">
<stock_mvt>
     <id><![CDATA[1]]></id>
     <id_product><![CDATA[]]></id_product>
     <id_product_attribute><![CDATA[]]></id_product_attribute>
     <id_warehouse><![CDATA[]]></id_warehouse>
     <id_currency><![CDATA[]]></id_currency>
     <management_type><![CDATA[]]></management_type>
     <id_employee xlink:href="http://dl.miami-kassen.de/api/employees/1"><![CDATA[1]]></id_employee>
     <id_stock xlink:href="http://dl.miami-kassen.de/api/stocks/1"><![CDATA[1]]></id_stock>
     <id_stock_mvt_reason xlink:href="http://dl.miami-kassen.de/api/stock_movement_reasons/11"><![CDATA[11]]></id_stock_mvt_reason>
     <id_order><![CDATA[]]></id_order>
     <id_supply_order><![CDATA[]]></id_supply_order>
     <product_name><language id="1" xlink:href="http://dl.miami-kassen.de/api/languages/1"><![CDATA[]]></language><language id= "2" xlink:href="http://dl.miami-kassen.de/api/languages/2"><![CDATA[]]></language></product_name>
     <ean13><![CDATA[]]></ean13>
     <upc><![CDATA[]]></upc>
     <reference><![CDATA[]]></reference>
     <mpn><![CDATA[]]></mpn>
     <physical_quantity><![CDATA[1387]]></physical_quantity>
     <sign><![CDATA[1]]></sign>
     <last_wa><![CDATA[0.000000]]></last_wa>
     <current_wa><![CDATA[0.000000]]></current_wa>
     <price_te><![CDATA[0.000000]]></price_te>
     <date_add><![CDATA[2023-06-07 14:09:47]]></date_add>
</stock_mvt>
</prestashop>

[s4lvozesta]

18 hours ago, Frank Begbie said:

Since I don't notice when an order takes place in the shop

I think it is possible to make the cash register notice this. cmiiw.

I designed my POS to receive a callback when transaction happen in my prestashop. A simple example, when there is a sales transaction on prestashop, it sends a callback to my URL, then the URL runs logic to deduct product qty accordingly. Do you think such design would work for your case?

[Frank Begbie]

3 hours ago, s4lvozesta said:

I think it is possible to make the cash register notice this. cmiiw.

I designed my POS to receive a callback when transaction happen in my prestashop. A simple example, when there is a sales transaction on prestashop, it sends a callback to my URL, then the URL runs logic to deduct product qty accordingly. Do you think such design would work for your case?

Our cash register program is a Windows program and not a web application. So how should Prestashop tell the checkout that an order has been placed or canceled? It would be nice if someone could just tell me why I'm missing data in the mvts table, then I think it would be easier to track the inventory changes.

 

[ps8modules]

https://docs.prestashop-project.org/1.7-documentation/user-guide/selling/managing-catalog/managing-stock/stock-movements

[Frank Begbie]

11 minutes ago, ps8moduly.cz said:

https://docs.prestashop-project.org/1.7-documentation/user-guide/selling/managing-catalog/managing-stock/stock-movements

Thank you very much. I mentioned this in a previous post, but unfortunately I'm missing the product ID and the management_type, which are not included for reasons I don't know.

[s4lvozesta]

18 hours ago, Frank Begbie said:

Our cash register program is a Windows program and not a web application. So how should Prestashop tell the checkout that an order has been placed or canceled?

by making a web service that update to the VB desktop program (either directly to its database or via another desktop service)

[ps8modules]

20 hours ago, Frank Begbie said:

Thank you very much. I mentioned this in a previous post, but unfortunately I'm missing the product ID and the management_type, which are not included for reasons I don't know.

Hi.

You do not have a warehouse created in the e-shop.
You need to create a warehouse and add all the products to it. Then everything will work.

But the new versions of Prestashop lack advanced stock management.

Edited July 20, 2023 by ps8moduly.cz (see edit history)

[ps8modules]

Custom sample Query.

Shows orders, order statuses, etc. for a period of 1 year (INTERVAL 365 DAY).

SELECT 
o.id_order AS 'ID Order', 
o.valid AS 'Finalized',
o.date_add AS 'Order date add',
o.date_upd AS 'Order date updated',
o.current_state AS 'ID current state',
sl.name AS 'Current state',
od.product_id AS 'ID Product', 
od.product_reference AS 'Product reference',
od.product_attribute_id AS 'ID Combination', 
od.product_name AS 'Product name', 
od.product_quantity AS 'Buy quantity',
sa.quantity AS 'Stock quantity'
FROM ps_order_detail od
LEFT JOIN ps_orders o ON (o.id_order = od.id_order)
LEFT JOIN ps_order_state_lang sl ON (o.current_state = sl.id_order_state)
LEFT JOIN ps_stock_available sa ON (od.product_id = sa.id_product AND od.product_attribute_id = sa.id_product_attribute)
WHERE 
sl.id_lang = o.id_lang AND 
o.date_add > DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 365 DAY),'%Y-%m-%d')  
GROUP BY od.product_id, od.product_attribute_id, o.id_order  
ORDER BY o.id_order ASC;

 

[Frank Begbie]

2 hours ago, ps8moduly.cz said:

Hi.

You do not have a warehouse created in the e-shop.
You need to create a warehouse and add all the products to it. Then everything will work.

But the new versions of Prestashop lack advanced stock management.

Hello
Thank you for the info. But where should I create a warehouse? Under Catalog Stock, all the stocks and their stock movements are displayed to me, just as I expect in the stock_movments table to be offered to me there.

[ps8modules]

Full query for id_shop = 1, id_lang = 1.

You can insert the SQl query into your phpMyAdmin and you will see the result.

The sign column is the + or - sign of the reaction.
So the result is physical_quantity column * sign column.

 

SELECT SQL_CALC_FOUND_ROWS
              sm.id_stock_mvt,
              sm.id_stock,
              sm.id_order,
              sm.id_employee,
              sm.employee_lastname,
              sm.employee_firstname,
              sm.physical_quantity,
              sm.date_add,
              sm.sign,
              smrl.id_stock_mvt_reason,
              smrl.name                                   AS movement_reason,
              p.id_product                                AS product_id,
              COALESCE(pa.id_product_attribute, 0)        AS combination_id,
              IF(
                  LENGTH(COALESCE(pa.reference, "")) = 0,
                  IF(LENGTH(TRIM(p.reference)) > 0, p.reference, "N/A"),
                  CONCAT(p.reference, " ", pa.reference)
              )                                           AS product_reference,
              pl.name                                     AS product_name,
              p.id_supplier                               AS supplier_id,
              COALESCE(s.name, "N/A")                     AS supplier_name,
              COALESCE(ic.id_image, 0)                    AS product_cover_id,
              (SELECT GROUP_CONCAT(
                        DISTINCT CONCAT(agl.name, " - ", al.name)
                        SEPARATOR ", "
                    )
                    FROM ps_product_attribute pa2
                    JOIN ps_product_attribute_combination pac ON (
                        pac.id_product_attribute = pa2.id_product_attribute
                    )
                    JOIN ps_attribute a ON (
                        a.id_attribute = pac.id_attribute
                    )
                    JOIN ps_attribute_lang al ON (
                        a.id_attribute = al.id_attribute
                        AND al.id_lang = 1
                    )
                    JOIN ps_attribute_group ag ON (
                        ag.id_attribute_group = a.id_attribute_group
                    )
                    JOIN ps_attribute_group_lang agl ON (
                        ag.id_attribute_group = agl.id_attribute_group
                        AND agl.id_lang = 1
                    )
                    WHERE pa2.id_product=p.id_product AND pa2.id_product_attribute=pa.id_product_attribute)
                    AS combination_name,
              (SELECT GROUP_CONCAT(
                        DISTINCT CONCAT(agl.name)
                        SEPARATOR ", "
                    )
                    FROM ps_product_attribute pa2
                    JOIN ps_product_attribute_combination pac ON (
                        pac.id_product_attribute = pa2.id_product_attribute
                    )
                    JOIN ps_attribute a ON (
                        a.id_attribute = pac.id_attribute
                    )
                    JOIN ps_attribute_group ag ON (
                        ag.id_attribute_group = a.id_attribute_group
                    )
                    JOIN ps_attribute_group_lang agl ON (
                        ag.id_attribute_group = agl.id_attribute_group
                        AND agl.id_lang = 1
                    )
                    WHERE pa2.id_product=p.id_product AND pa2.id_product_attribute=pa.id_product_attribute)
                    AS attribute_name
           FROM ps_stock_mvt sm
            INNER JOIN ps_stock_mvt_reason_lang smrl ON (
              smrl.id_stock_mvt_reason = sm.id_stock_mvt_reason
              AND smrl.id_lang = 1)
            INNER JOIN ps_stock_available sa ON (sa.id_stock_available = sm.id_stock)
            LEFT JOIN ps_product p ON (p.id_product = sa.id_product)
            LEFT JOIN ps_product_attribute pa ON (pa.id_product_attribute = sa.id_product_attribute)
            LEFT JOIN ps_product_lang pl ON (
                p.id_product = pl.id_product AND
                pl.id_lang = 1
            )
            INNER JOIN ps_product_shop ps ON (
                p.id_product = ps.id_product AND
                ps.id_shop = 1
            )
            LEFT JOIN ps_image ic ON (
                p.id_product = ic.id_product AND
                ic.cover = 1
            )
            LEFT JOIN ps_image_shop ims ON (
                p.id_product = ims.id_product AND
                ic.id_image  = ims.id_image AND
                ims.id_shop = 1 AND
                ims.cover = 1
            )
            LEFT JOIN ps_supplier s ON (p.id_supplier = s.id_supplier)
            LEFT JOIN ps_product_attribute_combination pac ON (
                pac.id_product_attribute = pa.id_product_attribute
            )
            LEFT JOIN ps_product_attribute_shop pas ON (
                pas.id_product = pa.id_product AND
                pas.id_product_attribute = pa.id_product_attribute AND
                pas.id_shop = 1
            )
            WHERE
            sa.id_shop = 1 AND
            sa.id_shop_group = 0 AND
            sa.id_product_attribute = COALESCE(pa.id_product_attribute, 0)
            
            GROUP BY sm.id_stock_mvt
            HAVING 1 
            ORDER BY date_add DESC 
        

 

Edited July 20, 2023 by ps8moduly.cz (see edit history)

[ps8modules]

You can't rely on the Prestashop API to do everything.
Let's go back a few posts above, when I advised you to run your own scripts with your own queries to the database.
It is about programming your own API or creating a new webservice.

[Frank Begbie]

On 7/20/2023 at 11:00 AM, ps8moduly.cz said:

You can't rely on the Prestashop API to do everything.
Let's go back a few posts above, when I advised you to run your own scripts with your own queries to the database.
It is about programming your own API or creating a new webservice.

Hello, thank you for your effort. I asked again in which area of the shop can I put the warehouse. Can't find anything in the shop settings.

[Frank Begbie]

Wollte die Selektion der Orders nach invoice_date einschränken bekomme aber alles orders angeboten obwohl in diesem Fall keine Orders da sein dürften. Mein Filter sieht so aus

orders/?filter[invoice_date]=[2023-07-25 10:56.45,2023-07-26 10:58.43]date=1

Was ist daran falsch?