Jump to content

Stock Levels Updated From an API - Cache Problems


disco_ii_disco

Recommended Posts

I have built an API for my CubeCart store to update the stock levels from my till and stock management system. It is working well.

However, sometimes old stock levels are being displayed on product pages. I have to keep going in to clear the cache from the Cubecart admin to fix it.

Does anyone know a way around this?

Is there a way to clear the cache for an individual product? (I am not completely sure how the cache works!) If so, I could do this in the API.

 

Link to comment
Share on other sites

set a boolean in your update process and flip it if something has actually changed/updated in your feeds. if it has, then call the clear cache process via the scheduled job. you need to integrate with the CubeCart classes and admin classes to do this. 

 

Link to comment
Share on other sites

Thank you for the reply Noodleman.

I have found the code that the admin system uses for clearing the entire cache:

if (isset($_GET['clear_cache']) && $_GET['clear_cache'] == 'true') {
    $GLOBALS['cache']->clear();
    $GLOBALS['session']->delete('CLEAR_CACHE');
    $GLOBALS['main']->successMessage($GLOBALS['language']->maintain['notify_cache_cleared'], false);
    httpredir(currentPage(array('clear_cache')));
}

Is it possible to clear the cache for a single product? I am guessing not - otherwise this would happen after updating a product in admin.

 

Link to comment
Share on other sites

It is possible to clear the cache file (PHP's unlink() function) for a single and distinct SQL query. But one would have to know the exact string that was constructed and sent to the database to recalculate the encoded filename of the cached query file. CubeCart has a debug mode that will give you the encoded filename for each query made.

In /classes/catalogue.class.php, line 1406, getProductStock(), there is:

if (is_numeric($product_id) && ($products = $GLOBALS['db']->select('CubeCart_inventory', array('stock_level'), array('product_id' => (int)$product_id), false, 1, false, false)) !== false) {

The last (7th) argument above is false, which says to not use the cache for this query: not to look in it and to not cache the results sent back from the database. (So, thus, there is no file in the cache for this.)

Which suggests that the "old" stock level on the storefront that you are complaining about is to be found elsewhere than on the View Product page. We need to know where that location is so we can go look for the query that is permitted to use the cache.

Because stock levels are very dynamic, queries that ask for it are coded to not use the cache. So, your observation is strange indeed. Maybe a plugin?

 

Link to comment
Share on other sites

Thank you for your reply bsmither. That has given me a lot more understanding of what is going on.

You are right, the text on the product page is coming from a code snippet. The cope snippet:

  1. Looks up stock levels in different locations (I have double checked to make sure this query has the cache value set to false)
  2. Assigns some smarty variables based on the stock status
  3. The text then changes on the product template changes based on the stock status (i.e whether the product is in stock at our shop, our warehouse or only with our suppliers).

I am guessing it is the smarty part that is being cached.

Is there a way to prevent this, or do you think I'd be better using the unlink method?
 

 

I have tried assigning the variables as nocache variables and have also tried adding {nocache} {/nocache} to the relevent section of the template. Neither of these solved my problem. 

https://www.smarty.net/docs/en/caching.cacheable.tpl

Link to comment
Share on other sites

This concerns me: "Looks up stock levels in different locations". (I can read this two ways: several separate blocks of code queries the same single source, or one custom block of code queries several separate sources.)

Please let us know where these other locations are at. You have said that clearing the cache solves this issue, so I am hesitant to suggest that these 'different locations' might not be getting properly updated with new stock levels. (Or do you mean that "At different locations (blocks of custom code), I am looking up the stock level in CubeCart_inventory"?)

Smarty does cache compiled templates, but the compile does not permanently put in place any dynamic data, nor is any section of template code permanently modified to a specific result based on that data. So, I think using Smarty's 'cache' commands will not get the desired results.

You said: "whether the product is in stock at our shop, our warehouse or only with our suppliers". The shop stock should be available in CubeCart_inventory. The warehouse stock has its own CubeCart database table? As for querying the supplier stock, could that be getting done via a CURL request?

If via CURL, then CubeCart does also cache these requests but the instantiation of CubeCart's Request class has its request cache disabled by default. One needs to specifically enable the request cache when the class is instantiated, or can be enabled by a specific call to the appropriate class method, to cache the request and the response to that request. (Which is cleared when the general cache gets cleared.)

Link to comment
Share on other sites

Nothing concerning! "Different locations" meant a table I have that stores the stock levels at each physical location: e.g: location_id, product_id, stock_level.

The query to get the data, in my code snippet, is the same as the Cubecart getProductStock() code but it looks in my stock_levels table rather than the inventory table. The overall stock level is still stored in the inventory table.

Link to comment
Share on other sites

Ok, a distinct table that holds these stock levels. And the query constructed such that the $cache parameter is set to false.

In Cubecart's admin, Store Settings, Advanced tab, enable the Debug feature and enter your local IP address in the next field (www.showmyip.com).

Request the product page.

In the debug section at the bottom of the page, scan the list of queries for the specific query that targets this custom inventory table. The list item will have either 'Cache Read' in green, 'Not Cached' in black, or 'Cache Write' in orange.

If 'Not Cached', then we can say the query has been processed as needed - cache not used.

We can then look in the /cache/skin/ folder for a file that ends with 'file.content.product.php.php'. Scan through the file looking for the section that has the code that displays the custom data regarding stock levels. Does it look legit?

 

Link to comment
Share on other sites

The query is not being cached:

Duration: 425 µs [NOT CACHED]
SELECT `stock_level` FROM `stock_levels` WHERE stock_levels.product_id = '11262' AND stock_levels.location_id = '1';

The cache/skin file looks fine. It must be the Smarty template variables that are being cached(?) from the code snippet.

Link to comment
Share on other sites

The next test would be to manually adjust the stock level using an external database utility, such as phpMyAdmin.

Find the record in 'stock_levels' where product_id is 11262 and location_id is 1, and change the stock_level value by one.

Then request that page and see if the stock level changed any.

 

 

Link to comment
Share on other sites

My next test would be to examine the database result as close as possible to the code that fetches the recordset from the database connection. (Near line 224 in /classes/db/mysqli.class.php)

That would take a bit to set up, and would need to look specifically for the target table name in the query - or get a dumped listing of every database recordset.

 

Link to comment
Share on other sites

That sounds like a legitimate approach, except CubeCart is saying the query and its results are not being cached. Thus, if there is a file that is holding some sort of cached data from the database regarding this, then CubeCart doesn't know about it.

We haven't reviewed the exact code in your customization that makes the query against the database looking for stock levels.

 

Link to comment
Share on other sites

I am presuming my code snippet, which runs at class.cubecart.display_product, is saving the variables, and not refreshing:

$GLOBALS['smarty']->assign('STOCK_LEVEL_SUPPLIER', $stock_level_supplier, true); 
$GLOBALS['smarty']->assign('STOCK_LEVEL_SHOP', $stock_level_shop, true);       
$GLOBALS['smarty']->assign('STOCK_LEVEL_WAREHOUSE', $stock_level_warehouse, true);

I added the true part recently, as this is supposed to save as a nocache variable:

https://www.smarty.net/docs/en/caching.cacheable.tpl

Link to comment
Share on other sites

Here is the code snippet:

<?php 

    if ((bool)$product['use_stock_level']) {
                    
        $stock_level_supplier = $product['available_to_order'];
        $stock_level_warehouse = (int)$GLOBALS['chris']->getProductLocationStockLevel ($product['product_id'], 1);
        
        //(int)$stock_level already defined in catalogue.class.php
        $stock_level_shop = $stock_level - (int)$stock_level_warehouse;
        //there could be a higher stock level at shop than the actual stock_level (due to stock ready to be transferred for web orders) - so reduce the stock level accordingly...
        if ($stock_level_shop > $stock_level) $stock_level_shop = $stock_level;
          
        //$stock_level already defined in catalogue.class.php
        if ($stock_level <= 0) {
            // Out of Stock
            if (!$GLOBALS['config']->get('config', 'basket_out_of_stock_purchase')) {
            
                // Not Allowed
                $allow_purchase = false;
                $out = true;
            }
            $stock_level_status = ($product['available_to_order']>0 || $stock_level_supplier>0) ? 1 : 0;
            
            $in_stock_at_warehouse = 0;
            $in_stock_at_shop = 0;
            
        } else {
            
            $stock_level_status = 1;
            $in_stock_at_warehouse = ($stock_level_warehouse>0) ? 1 : 0;
            $in_stock_at_shop = ($stock_level_shop>0) ? 1 : 0;
           
        }
        
        //added true to prevent caching 4/7/21
          
        $GLOBALS['smarty']->assign('STOCK_STATUS', $stock_status, true);
        
        $GLOBALS['smarty']->assign('STOCK_LEVEL', $stock_level, true);
        
        $GLOBALS['smarty']->assign('STOCK_LEVEL_SUPPLIER', $stock_level_supplier, true); 
        $GLOBALS['smarty']->assign('STOCK_LEVEL_SHOP', $stock_level_shop, true);       
        $GLOBALS['smarty']->assign('STOCK_LEVEL_WAREHOUSE', $stock_level_warehouse, true);
        
        $GLOBALS['smarty']->assign('IN_STOCK_AT_WAREHOUSE', $in_stock_at_warehouse, true);
        $GLOBALS['smarty']->assign('IN_STOCK_AT_SHOP', $in_stock_at_shop, true);   
          
    }

?>

And the function to get the location stock level is just:

public function getProductLocationStockLevel ($product_id, $location_id=1) {
  
    if (($stock_level_at_location = $GLOBALS['db']->select('stock_levels', array('stock_level'), array('product_id' => (int)$product_id, 'location_id' => $location_id), 'location_id', false, 1, false)) !== false) {  
      $qty_at_location	= $stock_level_at_location[0]['stock_level'];
    } else {
      $qty_at_location = 0;   
    } 
    
    return $qty_at_location;
    
  }

 

Link to comment
Share on other sites

I don't see anything obviously wrong, but I need to ask this:

In examining the database table 'stock_levels', do you find that there might be more than one record where product_id is A and location_id is B?

Your query is asking for records with these WHERE conditions, then having the database engine sort on the column 'location_id'.

The utility phpMyAdmin makes it easy to sort on multiple columns: sort first on product_id then on location_id and see if there is more than one of any of the combinations.

However, again, with cache not being used, clearing the cache would have no effect at resolving this kind of scenario.

Link to comment
Share on other sites

The problem I am having must be due to this part of the code:

$GLOBALS['smarty']->assign('STOCK_STATUS', $stock_status, true);
        
$GLOBALS['smarty']->assign('STOCK_LEVEL', $stock_level, true);

$GLOBALS['smarty']->assign('STOCK_LEVEL_SUPPLIER', $stock_level_supplier, true); 
$GLOBALS['smarty']->assign('STOCK_LEVEL_SHOP', $stock_level_shop, true);       
$GLOBALS['smarty']->assign('STOCK_LEVEL_WAREHOUSE', $stock_level_warehouse, true);

There shouldn't be a situation where where product_id is A and location_id is B. I wrote it like this so you can query by different location ids. (Most of the code was reused from parts of the catalagoue class).

$product['available_to_order'] is an integer field I have in my inventory table, that gets updated through my API.

 

 

 

Link to comment
Share on other sites

Working with this theory, let's explore what the values of the various variables are.

We will use a function to show these values:

$GLOBALS['debug']->debugMessage("The stock_status is: ".($stock_status?"True":"False"));
$GLOBALS['smarty']->assign('STOCK_STATUS', $stock_status, true);
$GLOBALS['debug']->debugMessage("The stock_level is: ".$stock_level);
$GLOBALS['smarty']->assign('STOCK_LEVEL', $stock_level, true);
$GLOBALS['debug']->debugMessage("The stock_level_supplier is: ".$stock_level_supplier);
$GLOBALS['smarty']->assign('STOCK_LEVEL_SUPPLIER', $stock_level_supplier, true);
$GLOBALS['debug']->debugMessage("The stock_level_shop is: ".$stock_level_shop);
$GLOBALS['smarty']->assign('STOCK_LEVEL_SHOP', $stock_level_shop, true);
$GLOBALS['debug']->debugMessage("The stock_level_warehouse is: ".$stock_level_warehouse);
$GLOBALS['smarty']->assign('STOCK_LEVEL_WAREHOUSE', $stock_level_warehouse, true);

When I said: "there might be more than one record where product_id is A and location_id is B", I fear you misunderstood. The following query illustrates my question:

SELECT `stock_level` FROM `stock_levels` WHERE stock_levels.product_id = '11262' AND stock_levels.location_id = '1';

where 'A' is 11262 and 'B' is 1.

But I will not pursue this any longer because, as you say, clearing the cache seems to clear out the stale data.

By using the debugMessage() function, we will see the values being assigned to the Smarty template variables. If the source values are stale, we can eliminate Smarty as the cause, and start looking where the source variables become stale.

With CubeCart's Debug mode enabled, these messages will appear just below the list of SQL queries.

Link to comment
Share on other sites

Thank you for the debug code tip - I'll use this a lot going forward.

Here is an example output:

[0] The stock_status is: True
[1] The stock_level is: 17
[2] The stock_level_supplier is: 0
[3] The stock_level_shop is: 17
[4] The stock_level_warehouse is: 0

Then...

  1. I made a change to the stock level for this product, in my system;
  2. I updated Cubecart via my API;
  3. I checked the stock change had been applied in database (it was also showing correctly in Cubecart admin);
  4. I reloaded the product page (on two different computers).

No change to the stock levels were shown on either computer.

The debug information was exactly the same as above (i.e before the change).

I cleared the cache and refreshed the page. Stock level change shown.

 

Link to comment
Share on other sites

Ok, then we must assume that there is some code, somewhere, that has cached a previous query and is using the result from the cache.

Here is a desperate test: in admin, Store Settings, Advanced tab, "Enable Caching", set to Disabled and Save. This is supposed to completely shut off the cache no matter how badly something in the code wants to use it.

Perform the same set of steps as detailed above.

 

 

Link to comment
Share on other sites

Yes, when "Enabling Caching" is set to "disabled" (in admin) it fixes the problem: The debug information and the stock level information on the page display correctly.

Once caching is turned back on, the out of date information is then displayed - until the cache is cleared.

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...