Stock management quantity is not synced with Product quantity

[alex_ivascu]

Hello comunity, 

 

I'm writing here with a big problem we're facing with our Prestashop 1.6.1.9 version. We've been using Advanced Stock Management successfully for ome while, and all was fine untill some weeks ago, when all of a sudden it seems that the quantity of the products we seell is not the same in Advanced Stock Management when comparing with the quantity available in Product/Quantities, after any kind of order placed by our customers. This obviously is very wrong, and when you have only one product on stock, it is ordered, but not correctly updated in Product Quantity, it allows other users to order it, when we actually don't have it anymore in stock.

The problem is like this: after each order, it seems that the quantity of the products is correctly updated in the advanced stock management, but not in product quantities. It seems random... and is happening for some products, but is very annoying and uncertain, as after each order we have to check manaully if the product was taken out of stock correctly... and sometime it causes inconsistencies...

Somehow it seems a syncronization issue, because to sync them we manually search the product that was not updated correctly, go on the Catalog -> Products -> Quantities and first select "I want to specify available quantities manually." and unckeck "I want to use the advanced stock management system for this product.", then Save. After this operation to turn back Advanced Stock Management for the mentioned product we check again "I want to use the advanced stock management system for this product." and also "The available quantities for the current product and its combinations are based on the stock in your warehouse (using the advanced stock management system)."

After this,the problem MIRACULOSELY dissapearas, and product quantity is synced correctly, appearing the same as in Advanced Stock Management.
As you can imagine this is extremely unpleasant as it is consuming a lot of time, and it might leave room for human errors... 

Tried to clear cache several times, moved even to mysql and  not on disk, worked for a while and then appeared again....

Please, did somenone experienced this? Searched through Google, but couldn't find anything related to this topic.

We're becoming desperate here, any help / idea would be much appreciated.

 

Thank you in advance,

Alex

[alex_ivascu]

Hello, 

 

Anybody faced this issue? Is becoming more and more hard to manage the shop with this issue.

Any answer would be much appreciated.

 

Thank you

[vcopleutre]

Hi everybody,

 

Important answer for everyone using ASM and having troubles between available stock for sale and real stock, the answer lies in the "refund" order status.

The orders in this status are not taken into account for increasing the real stock but are taken into account for decreasing stock for sale (lol). Even if you specifically said that the product HAS BEEN returned.

 

So to solve your issues, first create a new refund status with the same parameters as shipped for exemple and then set your refunded orders to this new status.

 

Then you can correct the stock by doing an inventory and using advanced stock management manually to adjust the stock (through backoffice, not database).

 

BEWARE : in some cases, you'll have to dig in ps_order_detail to change values in refunded or returned products to be able to recreate a return of a product (through order detail back office page, not return !!). It will help you to "add" a product available to sale, without changing the real stock. 

Backup your table before doing this, one never knows...

 

Damned. Two years to find the root of the problem and find solution.

[nicoX]

It's hard to understand what happens, as I don't use ASM, but is still related to any specific version of PS, as 1.6.1.9 is quite old?

As I understand:

1. Customer return item
   1. Item is not increased back again in Stock
   2. Item is increased back in Advanced Stock Management
2. To fix it so no error occurs:
   Create a new refund status with the same parameters as shipped and then set your refunded orders to this new status
3. Then you can correct the stock by doing an inventory and using advanced stock management manually to adjust the stock
   1. Why would you now need to correct the stock if you use solution nr, 2?

Edited September 27, 2018 by nicoX (see edit history)

[vcopleutre]

Hi NicoX,

Thank your for your concern. Here is the procedure

 

1. Customer return item

1. Nothing happend for the stock
2. I use refund to return product and add it back to stock
3. Everything's fine for the stock

2. I want a refund statuts

1. I set my order in "refund" statuts
2. Physical stock remains the same
3. For Sales stock decrease (issue)

3. I don't know how to fix it so I change the stock

1. I have one for sale (real stock 2)
2. I want two for sale
3. I add one for sale with ASM and i have to know that my real stock is not 3 but 2

4. How to come back to normal

1. I set my oder in a new status
2. I manually correct my stock (due to 3.3 !)

 

So yes, I change the stock because after "fixing" (4.1) the issue, I have 3 for sale and 3 in stock. So it's better to check my real stock

If you touch nothing at all, you don't have to correct the stock.

Btw we use Prestashop 1.6.17 and we faced this problem. This version is not "that" old. For many e-sellers, 1.7 is not the way to go !

 

[nicoX]

Thanks.

Do you consider this to be a bug? If so could you please raise a ticket in github, or if you wish I can do it:

https://github.com/PrestaShop/PrestaShop/issues

[nicoX]

N/A

Edited October 1, 2018 by nicoX (see edit history)

[kenoffice]

Hi folks
I have spent 1 day digging for solution...
In my case, I didnt had refounding system activated but still, I encountered similar situations like you (the quantities from Product -> Quantities was different than qty from Advanced stock Management)
After many hours of digging through database, changing some classes in /classes/stock folder, I have found that the difference (Product->Qty reported 5 items less comparing with advanced stock management; more specific, product qty show 14 when AMS show 19 items) is coming from some old cancelled orders !

In StockManagerInterface.php says:
"    /**
     * For a given product, returns its real quantity
     * If the given product has combinations and $id_product_attribute is null, returns the sum for all combinations
     * Real quantity : (physical_qty + supply_orders_qty - client_orders_qty)
     * If $usable is defined, real quantity: usable_qty + supply_orders_qty - client_orders_qty
     *
     ....
     */
    public function getProductRealQuantities($id_product, $id_product_attribute, $ids_warehouse = null, $usable = false);
"

I REACH HERE starting from StockAvailable.php, where it write:
$quantity = $manager->getProductRealQuantities($id_product, $id_product_attribute, $allowed_warehouse_for_combination_clean, true);

so I checked the $client_orders_qty variable from file mentioned bellow.

In StockManager.php, it says:
==========
...
public function getProductRealQuantities($id_product, $id_product_attribute, $ids_warehouse = null, $usable = false)
...
$client_orders_qty += ($row['product_quantity'] - $row['product_quantity_refunded']);
...
$qty = $this->getProductPhysicalQuantities($id_product, $id_product_attribute, $ids_warehouse, $usable);
...
return ($qty - $client_orders_qty + $supply_orders_qty);
==========

This means that from quantities which he found on table ps_stock for an specific product/combination (in my case 19), it substract what he found on tables with orders (products received back and not added automatically in stock by ASM) and add eventual orders to suppliers. Finally, the script pushes in my ps_stock_available table the value "14" and this means on table with orders, 5 of them are not seen as products came back in stock after unsuccessful order... Like CANCELLED...

Looking very careful to "$client_orders_qty += ($row['product_quantity'] - $row['product_quantity_refunded']);" from file StockManager.php I decided to add the missing value (difference in my case between 19 and 14) to last row in column product_quantity_refunded, from table ps_order_details

And WOW, problem solved, now quantities are the same, in Product->Quantities and ASM.
But this is an trick. And can be solved only altering table ps_order_details in database.

Normally, in my opinion, PrestaShop must do itself, I mean when an order receive status "cancelled" to bring back in stock the products from that order... But from what I have seen, when adding/modifying statuses in BO, there is no such option regarding add back in stock.

I am on PS 1.6.1.18

have fun !

Edited March 27, 2020 by kenoffice (see edit history)

[kenoffice]

Ok, I am on next day of struggling

I have made some tests, placing an order and after that setting it as "Cancelled"
It seems that PS put back in stock (table ps_stock_available) while ps_stock is not touched (because I didn't set status "Shipped")
I think is an normal behavior. Concluding, if an order is set at "Cancelled" the quantitites get back to stock. Nice !

As you can see bellow, some problems can appear NOT ONLY because of refounded, returned or cancelled orders...

Continuing, I look again on StockManager.php
Somewhere inside  public function getProductRealQuantities (...) says:

// Gets client_orders_qty
            $query = new DbQuery();
            $query->select('od.product_quantity, od.product_quantity_refunded');
            $query->from('order_detail', 'od');
            $query->leftjoin('orders', 'o', 'o.id_order = od.id_order');
            $query->where('od.product_id = '.(int)$id_product);
            if (0 != $id_product_attribute) {
                $query->where('od.product_attribute_id = '.(int)$id_product_attribute);
            }
            $query->leftJoin('order_history', 'oh', 'oh.id_order = o.id_order AND oh.id_order_state = o.current_state');
            $query->leftJoin('order_state', 'os', 'os.id_order_state = oh.id_order_state');
            $query->where('os.shipped != 1');
            $query->where('o.valid = 1 OR (os.id_order_state != '.(int)Configuration::get('PS_OS_ERROR').'
                           AND os.id_order_state != '.(int)Configuration::get('PS_OS_CANCELED').')');
            $query->groupBy('od.id_order_detail');

===The result (retun) of this function is the value (items quantity) which PS will WRITE on ps_stock_available table when you go to that product and choose advanced stock management and check first option and press Save or Save & Stay===
            
So, in my opinion, the variable $client_orders_qty (this is THE ONE which causes differences between Product->Qty and ASM) it is related to other tables like ps_order_detail, ps_orders, ps_order_history, ps_order_state

I have put the sql from above into a readable format in order to run the query to mysql, taking into account 3 things:
- (int)$id_product -> should be replaced with an real numeric value, according to product or attribute which you are tracing...
- PS_OS_ERROR -> 20 (status of payment error in my case)
- PS_OS_CANCELED -> 11 (status of cancelled order in my case)
(these statuses you can find them in ps_order_state_lang table)

So, the query which I ran was:
1. For products without combinations/atributes:

SELECT od.`product_quantity`, od.`product_quantity_refunded` 
FROM `ps_order_detail` AS od 
LEFT JOIN `ps_orders` AS o ON (o.`id_order` = od.`id_order`) 
LEFT JOIN `ps_order_history` AS oh ON (oh.`id_order` = o.`id_order` AND oh.`id_order_state` = o.`current_state`) 
LEFT JOIN `ps_order_state` AS os ON (os.`id_order_state` = oh.`id_order_state`)
WHERE od.`product_id` = 50 AND 
os.`shipped` != 1 AND 
(o.`valid` = 1 OR
(os.id_order_state != 20 AND os.id_order_state != 11))
GROUP BY od.`id_order_detail`

2. for product attributes:

SELECT od.`product_quantity`, od.`product_quantity_refunded` 
FROM `ps_order_detail` AS od 
LEFT JOIN `ps_orders` AS o ON (o.`id_order` = od.`id_order`) 
LEFT JOIN `ps_order_history` AS oh ON (oh.`id_order` = o.`id_order` AND oh.`id_order_state` = o.`current_state`) 
LEFT JOIN `ps_order_state` AS os ON (os.`id_order_state` = oh.`id_order_state`)
WHERE od.`product_attribute_id` = 62 AND 
os.`shipped` != 1 AND 
(o.`valid` = 1 OR
(os.id_order_state != 20 AND os.id_order_state != 11))
GROUP BY od.`id_order_detail`

After you run this query, If the SUM of column product_quantity - SUM of column product_quantity_refunded = 0 this means that there are no differences between quantity in stock available (Proct->Quantities) is equal with quantits from ASM. So that product/attribute is ok.
Otherwise, if there are any differences, it means that the stocks will be different and yo need to make adjustments.
(In my case there was 1 pcs difference, ASM showing 7 pcs in stock while Product->Quantities showing 6 products in stock)

1. One way is that table ps_order_detail in column product_quantity_refunded to type there the difference.
2. Another way is to change the status of an one order which cause the difference of quantities... but this is pretty difficult to find WHICH ONE...
Let us try this extended sql, in order to receive more info, maybe we "catch" the order which give the wrong info about history of quantities ordered and their status:

SELECT od.`product_quantity`, od.`product_quantity_refunded`, o.`id_order`, o.`current_state` 
FROM `ps_order_detail` AS od 
LEFT JOIN `ps_orders` AS o ON (o.`id_order` = od.`id_order`) 
LEFT JOIN `ps_order_history` AS oh ON (oh.`id_order` = o.`id_order` AND oh.`id_order_state` = o.`current_state`) 
LEFT JOIN `ps_order_state` AS os ON (os.`id_order_state` = oh.`id_order_state`)
WHERE od.`product_id` = 50 AND 
os.`shipped` != 1 AND 
(o.`valid` = 1 OR
(os.id_order_state != 20 AND os.id_order_state != 11))
GROUP BY od.`id_order_detail`

WOW ! In my case I have found that ONE order has different status than "Shipped".
Because, IN MY LOGICAL FLOW, I designed the platform to set an order as "Shipped" BUT AFTER THAT, in case of orders with cash on delivery, after I receive the payment from courier I change the status to "Paid" or some kind of "Payment Received" which of course, the ID of this status is different than ID of status "Shipped"; this will result in sql query to throw an "bad" response, responding to PrestaShop stock management classes that the item has a different status than "Shipped". Because the sql look ONLY FOR LAST ROW OF RECORDS and if the last one HAVE NOT id for "Shipped" give wrong response.
My PARTIAL solution: in table ps_order_history I search for the order indicated by last sql query above and I have modified the LAST ROW with status history, to have the same ID as "Shipped" (I my case and I think to most of you, the ID is 13). After that, I checked again and now the quantities are showed correctly in both places

Overall, what user "vcopleutre" said above (adding an new kind of order status and apply it to an order which make problems) is very nice.
The only thing which comes into discussion is about "how we can find the order with problem" ?
I think the above sql can help most of us. Others, who cannot deal with sql probably will need to check every order for that product/attribute manually

ON SHORT:
1. when edit an product, going to Quantities tab and choose advanced stock management, first checkmark and first radio button, the PS will apply the function "synchronize($id_product, $order_id_shop = null)" from file StockAvailable.php WHICH AT HIS TURN look into FUNCTION: "getProductRealQuantities($id_product, $id_product_attribute, $ids_warehouse = null, $usable = false)" from file StockManager.php
2. in this function, the result depend of quantities previously ordered and the STATUS of their orders which came from, as:
return ($qty - $client_orders_qty + $supply_orders_qty);
3. if variable $client_orders_qty is not well obtained, will throw wrong value and this way we reach to different quantities in ASM and Product-Quantities
4. the variable $client_orders_qty is obtained by sql query above (short version), and after that it loops in foreach, as we can see in:
....
foreach ($res as $row) {
    $client_orders_qty += ($row['product_quantity'] - $row['product_quantity_refunded']);
....
5. Let assume that the ID of status of order "Shipped" is 13, ID of "Cancelled" is 11, and "Payment error" is 20 (most of you have the same if you don't touch too much the default settings)
The sql query will look into database, on many tables in same time (I already mentioned them above) and if ONE ORDER FOR AN SPECIFIC PRODUCT/ATTRIBUTE has an different status/ID than these 3 (13,20,11) will throw an response which will go, OF COURSE into $client_orders_qty variable, and so on, the $qty variable will be written into ps_stock_available table, being different than quantities from ps_stock table
A. For example, if one order have LAST status 17 (Payment Received) after 13 (Shipped), the sql will read only last value (17) and will return value: 1
In this case, if you have 55 items in ASM (table ps_stock), you will see 54 items in Product-Quantities (table ps_stock_available)
B. For example, if 3 orders have LAST status 17 (Payment Received) after 13 (Shipped), the sql will read only last value (17) and will return value: 3
In this case, if you have 55 items in ASM (table ps_stock), you will see 52 items in Product-Quantities (table ps_stock_available)

The problem is caused by the sql query which look ONLY FOR LAST VALUE in table ps_order_history, while, in my opinion should look in that table for that specific order at ALL ROWS and check if ID 13 (Shipped) is between them.
Because if one order have status "Shipped"at CERTAIN POINT IN TIME, can also receive LATER STATUSES like "Payment received", "Subject of possible return" etc etc.

The main point in my opinion is that if you give to orders other statuses after you already give "Shipped" you will face troubles with difference between quantities showed in Product-Quantities and Advanced Stock Management page

Solution 1: But you can "trick" the system, modifying all statuses which you might apply after status "Shipped" to check "mark this item as shipped".
Solution 2: Or, you can define an new status something like "Completed (shipped,paid,closed)" and check "mark this item as shipped" and apply to all orders after you close them (no further alteration)

[kenoffice]

Now, on third day..
I was looking for an solution to CLEAN my database and regulate stocks.
If for the future we know what to do, an problem still persist: how we can find the products which have stock problems, showing one value into an table(ps_stock) and different value to another table (ps_stock_available) ?
I have created some queries which you can run inside of an php file.
In order to not interfere with front office of your site (customers see as regular what they see) you can make an clone of index.php, let say "index2testqty.php" and upload it in root folder of your site, near to index.php
Inside that file, comment the Dispatcher line and add an include to include another php file (where you will write the code suggested by me bellow)
include_once(dirname(__FILE__).'/index2include.php');
Upload this file too, like previous one.
Now type in your browser http://yoursite.com/index2testqty.php and normally you should see the result of sql queries as:
1. the products with ID which have one value in ps_stock table and different values in ps_stock_available table
2. same as above, but only for attributes (products with combinations)
3. % 4. two checks, FIRST one to see if between physical_quantity and usable_quantity are any differences, SECOND one if there any differences between shop 1 and shop 2 (suposing you have MULTI STORE configuration). If you don't have multi-store configurtation I suggest you to comment that block because otherwise will stress a lot the mysql server.

Launching this file will display you the products who have problems.
After that you need to enter manually on every product which have problems and go to Quantities TAB, verify if ASM check-mark is checked and first radio button too, and press save & stay.
The page will refresh and you will see modified quantity, which most of the time will be correctly, taken from ASM.
BUT also is possible that sometimes the quantities will not arrive correctly from ASM, because you have some orders related to this product/attribute which have not proper statuses and cause the difference.
For this you need to dig more, looking at history of orders for that product/attribute and adjust the order, it will be pretty easy to identify it because you will se that have different statuses...
Me personally, I run the extended sql query (suggested in my previous post) into phpMyAdmin and from there I have easily found the orders who caused quantity problems; knowing the order ID I was looking to order and set the correct status (I explained bellow that I have created a new status) and after 1 day of hard working all my stock is ok

After you clear everything you can:
1. create an NEW status, like "Completed (shipped,paid,closed)" as I said in my previous post and apply to all orders which was shipped, paid, etc.
2. do not touch again with advanced stock management check mark and radio buttons from product page -> quantities tab. Once you set it up, leave that way. If you have employees then you can adjust their permissions not to see those settings; 

Note: in clearing process I have found that some mistakes appeared in the past because some products which initially were added on shop as single product and after a while the product was developed, having some combinations/attributes. If at that moment there was some items in stock BEFORE, the presta shop will continue to count them to total qty is ASM (but when you enter on Details you see only the quantities according to combinations); in ps_stock table can be found with attribute ZERO

Bellow is the code which should be put in index2include.php

<?php
/*

This file was created for helping when we need to check if there are any differences of quantities between table ps_stock and table ps_stock_available (for example when in back office, Product->quantites is displayed an value (like 5) and in advanced stock management it display different value (like 7)
This file should be called from AN CLONE of index.php ONLY temporary, like:
include_once(dirname(__FILE__).'/index2testqty.php');
For this:
1. make an copy of index.php, name it something like: index2testqty.php
2. there put an inlude line like:
include_once(dirname(__FILE__).'/index2include.php');
AND YOU CAN DISABLE (comment) teh Dispatcher line

You can alter directly the index.php without creating an clone but if your site is live, in production, visitors will see ugly texts at bottom of each page; but after that DO NOT FORGET to delete/comment the include_once line !
*/

echo '<br><br>hellow world (for testing...) !<br><br><br><br>';

// database connection (need to fill with yours data:)
$addressDB = "localhost";
$passwdDB = "";
$userDB = "";
$NameOfBD = "";
$conexionwithDB = mysqli_connect($addressDB,$userDB,$passwdDB,$NameOfBD); 

if (!$conexionwithDB) {
    echo "Error: cannot connect to MySQL." . PHP_EOL;
    echo "Valoarea errno: " . mysqli_connect_errno() . PHP_EOL;
    echo "Valoarea error: " . mysqli_connect_error() . PHP_EOL;
    exit;
}

//first we check products WITHOUT attributes:
echo '<span style="color:#F00"; style="font-weight:bold; font-size:14px">FIRST we check the products WITHOUT attributes<br>********************************************</span><br><br>';

//suposing you have ONLY 1 warehouse, otherwise the sql need to be adjusted...
//suposing the physical quantity is the same as usable_quantity, so we look ONLY FOR physical_quantity
$interogare_tbl_stoc = "SELECT `id_product`, SUM(`physical_quantity`), `reference` FROM `ps_stock` WHERE `id_product_attribute` = 0 GROUP BY `id_product` ORDER BY `id_product` ";
$interogare_tbl_stoc_ok = mysqli_query($conexionwithDB, $interogare_tbl_stoc) or die (mysqli_error($conexionwithDB) );

while ( $matrix_1 = mysqli_fetch_assoc($interogare_tbl_stoc_ok) ) {
$id_product_1 = $matrix_1['id_product'] ;
$sum_total_qty_1 = $matrix_1['SUM(`physical_quantity`)'] ;
$reference_1 = $matrix_1['reference'] ;

//this echo is only for testing the output, normally you should leave it commentes as it you fill up your screen :)
//echo '$id_product_1 IS: '.$id_product_1.' AND $sum_total_qty_1 IS: '.$sum_total_qty_1.' -=[reference: '.$reference_1.']=-<br>';

    //suposing you have only one shop (PS is not running in multistore mode), otherwise the sql need to be adjusted, or you can check for every shop, one by one, changing into WHERE clause ...WHERE `id_shop` = 1...
    $sql_stock_available = "SELECT `id_product`, `quantity` FROM `ps_stock_available` WHERE `id_shop` = 1 AND `id_product` = ".$id_product_1." ";
    $query_stock_available_sql = mysqli_query($conexionwithDB, $sql_stock_available) or die (mysqli_error($conexionwithDB) );
    foreach($query_stock_available_sql as $row) {
        $id_product_2 = $row['id_product'];
        $quantity_2 = $row['quantity'];
        if ($sum_total_qty_1 != $quantity_2) {
            echo '*** PAY ATTENTION ! product with ID '.$id_product_2.' have different values in ps_stock and ps_stock_available !!! ***<br>';
            echo '*** The value from "ps_stock" IS: <span style="color:#F00"> <b>'.$sum_total_qty_1.'</b></span>, WHILE the value from "ps_stock_available" IS: <span style="color:#F00"> <b>'.$quantity_2.'</b></span> ***<br><br>';
        }//end if
    }//end foreach
}//end while

//==============================================================

//secondly we check products WITH attributes:
echo '<br><br><br><br><span style="color:#F00"; style="font-weight:bold; font-size:14px">SECONDLY we check the products WITH attributes<br>********************************************</span><br><br>';

//suposing you have ONLY 1 warehouse, otherwise the sql need to be adjusted...
//suposing the physical quantity is the same as usable_quantity, so we look ONLY FOR physical_quantity
$interogare_tbl_stoc_3 = "SELECT `id_product`, `id_product_attribute`, SUM(`physical_quantity`), `reference` FROM `ps_stock` WHERE `id_product_attribute` != 0 GROUP BY `id_product_attribute` ORDER BY `id_product_attribute` ";
$interogare_tbl_stoc_ok_3 = mysqli_query($conexionwithDB, $interogare_tbl_stoc_3) or die (mysqli_error($conexionwithDB) );

while ( $matrix_3 = mysqli_fetch_assoc($interogare_tbl_stoc_ok_3) ) {
$id_product_3 = $matrix_3['id_product'] ;
$id_product_attrib_3 = $matrix_3['id_product_attribute'] ;
$sum_total_qty_3 = $matrix_3['SUM(`physical_quantity`)'] ;
$reference_3 = $matrix_3['reference'] ;

//this echo is only for testing the output, normally you should leave it commentes as it you fill up your screen :)
//echo '$id_product_1 IS: '.$id_product_1.' AND $sum_total_qty_1 IS: '.$sum_total_qty_1.' -=[reference: '.$reference_1.']=-<br>';

    //suposing you have only one shop (PS is not running in multistore mode), otherwise the sql need to be adjusted, or you can check for every shop, one by one, changing into WHERE clause ...WHERE `id_shop` = 1...
    $sql_stock_available_4 = "SELECT `id_product`, `id_product_attribute`, `quantity` FROM `ps_stock_available` WHERE `id_shop` = 1 AND `id_product_attribute` = ".$id_product_attrib_3." ";
    $query_stock_available_sql_4 = mysqli_query($conexionwithDB, $sql_stock_available_4) or die (mysqli_error($conexionwithDB) );
    foreach($query_stock_available_sql_4 as $row_4) {
        $id_product_4 = $row_4['id_product'];
        $id_product_attribute_4 = $row_4['id_product_attribute'];
        $quantity_4 = $row_4['quantity'];
        if ($sum_total_qty_3 != $quantity_4) {
            echo '*** PAY ATTENTION ! Item with ATTRIBUTE ID: '.$id_product_attribute_4.' (combination of product with ID '.$id_product_4.') have different values in ps_stock and ps_stock_available !!! ***<br>';
            echo '*** The value from "ps_stock" IS: <span style="color:#F00"> <b>'.$sum_total_qty_3.'</b></span>, WHILE the value from "ps_stock_available" IS: <span style="color:#F00"> <b>'.$quantity_4.'</b></span> ***<br><br>';
        }//end if
    }//end foreach
}//end while

//*****************************************
//additional checks... maybe someone with better php/sql knowledge can improve these check's as I am not very experienced :(
echo '<br><br>ADDTIONAL CHECKS<br><br>';
echo '<br>1. check into ps_stock table, if between physical_quantity and usable_quantity are any differences<br>';

$sql_check_physic_usable = "SELECT `id_product`, `id_product_attribute`, `reference`, `physical_quantity`, `usable_quantity` FROM `ps_stock` ORDER BY `id_product`, `id_product_attribute` ";
$q_sql_check_physic_usable = mysqli_query($conexionwithDB, $sql_check_physic_usable) or die (mysqli_error($conexionwithDB) );

while ( $matrix_5 = mysqli_fetch_assoc($q_sql_check_physic_usable) ) {
$id_product_phus = $matrix_5['id_product'] ;
$id_product_attrib_phus = $matrix_5['id_product_attribute'] ;
$reference_phus = $matrix_5['reference'] ;
$phys_qty = $matrix_5['physical_quantity'] ;
$usable_qty = $matrix_5['usable_quantity'] ;
if ($phys_qty != $usable_qty) {
    echo '<br>ATTENTION !!! physical qty and usable qty for product ID: '.$id_product_phus.' and attribute ID: '.$id_product_attrib_phus.' are NOT equal...<br>
    The physical qty IS: '.$phys_qty.' WHILE the usable qty IS: '.$usable_qty.'<br>';
} else {
    //we don't output as everything is ok, so comment next line
    //echo '<br>physical qty and usable qty for product ID: '.$id_product_phus.' and attribute ID: '.$id_product_attrib_phus.' are equal...<br>';    
}//end if-else
}//end while


echo '<br>2. check into ps_stock_available IN CASE OF MULTISTORE configuration, if there any differences between shop 1 and shop 2 (suposing you have 2 shops)<br>';

$sql_check_stock_shops = "SELECT `id_stock_available`, `id_product`, `id_product_attribute`, `id_shop`, `quantity` FROM `ps_stock_available` WHERE `id_shop` = 1 ";

$q_sql_check_stock_shops = mysqli_query($conexionwithDB, $sql_check_stock_shops) or die (mysqli_error($conexionwithDB) );

$row_cnt = mysqli_num_rows($q_sql_check_stock_shops);
printf("Result set has %d rows.\n", $row_cnt);
    
while ( $matrix_6 = mysqli_fetch_assoc($q_sql_check_stock_shops) ) {
$id_stkav_shps = $matrix_6['id_stock_available'] ;
$id_product_shps = $matrix_6['id_product'] ;
$id_product_attrib_shps = $matrix_6['id_product_attribute'] ;
$id_shop_shps = $matrix_6['id_shop'] ;
$qty_shps = $matrix_6['quantity'] ;
//echo '<br>'.$id_stkav_shps.' | '.$id_product_shps.' | '.$id_product_attrib_shps.' | '.$id_shop_shps.' | '.$qty_shps.' <br>';

    $sql_check_stock_shops_2 = "SELECT `id_stock_available`, `id_product`, `id_product_attribute`, `id_shop`, `quantity` FROM `ps_stock_available` WHERE `id_product` = '$id_product_shps' AND `id_product_attribute` = '$id_product_attrib_shps' AND `id_shop` = 2";
    $q_sql_check_stock_shops_2 = mysqli_query($conexionwithDB, $sql_check_stock_shops_2) or die (mysqli_error($conexionwithDB) );
    while ( $matrix_7 = mysqli_fetch_assoc($q_sql_check_stock_shops_2) ) {
        $id_product_attrib_shps_2 = $matrix_7['id_product_attribute'] ;
        $id_shop_shps_2 = $matrix_7['id_shop'] ;
        $qty_shps_2 = $matrix_7['quantity'] ;
        if ($qty_shps_2 != $qty_shps) {
            echo '<br>houston, we have an problem at product ID: '.$id_product_shps.', attribute ID: '.$id_product_attrib_shps_2.', stock in shop 1: '.$qty_shps.' <> stock in shop 2: '.$qty_shps_2.'<br>';
        }
    }//end while
}//end while

echo '<br><br><br><br>';
?>

 

[vcopleutre]

Hi, 

Thank you for dedicating so much time to this issue.

I'm glad people still trust ASM in PS 1.6 as this has became a priced feature in 1.7 (oh i'm watching you, greedy prestashop company.).

You found the culprit in the differences of stock in ASM regarding "real stock" and "available for sales stock".

I fully agree with you regarding your "solution 1" : 

Quote

Solution 1: But you can "trick" the system, modifying all statuses which you might apply after status "Shipped" to check "mark this item as shipped".

I think that it's the best way to solve your problems and it's not "tricking" the system, just using it in a good way. 2 is a way too but "cleaner" for database. It forces users to create a process for updating orders (as everything else, it can be fully automated).

But maybe the best solution for solving all these problems would be to create a property for order status named "valid" and to set this property to 0 for all non valid status, ex : cancelled, payment cancelled, etc...

Then change the StockManager method line 478

$query->where('os.shipped != 1');
$query->where('o.valid = 1 OR (os.id_order_state != '.(int)Configuration::get('PS_OS_ERROR').'
	AND os.id_order_state != '.(int)Configuration::get('PS_OS_CANCELED').')');

to

$query->where('os.shipped != 1 AND os.valid == 1');

What do you think about this ? 🙂

Vianney

P.S. : I'm currently updating a full shop to ASM for over 900 products and my goal is to have a correct stock after having (automatically) updating all the products to ASM. Funny job

[kenoffice]

hi Vianney

I think your suggestion is good too. But I have looked not to affect the PS code in that class because I wasn't sure how it will behave in the future.

Everyone who reads here is free to try any suggestion and choose which one fits into his project.

Regarding your project with 900 products, I think at certain point in time, after you create ASM, you will need the index2include.php posted by me above;

cheers ! (and stay@home)