2

I need to write a cron job which will import stock levels every 15 minutes from a wholesaler using SKU numbers as the unique identifier. My previous shopping cart had a table containing both the qty and sku fields which made life easy as there was just one table to refer to in the code, but obviously Magento doesn't have this. Can anyone offer any advice please? My head is literally spinning now as I have read so much stuff and am becoming confused. Thank you in advance

7
  • What form of access do you have to the wholesaler data? Flat file? MySQL? API?
    – davidalger
    Aug 19 '13 at 12:01
  • u want to use cron script which will update product qty,price etc automatically every 15mints.right?How u will fetch the stock levels from wholesaler means which method u want to use? Aug 19 '13 at 12:04
  • How you fetch from wholesaler can dictate what methods you can use to a degree, yes. If it's only available via API, for example, you couldn't use a scheduled uRapidFlow import to load stock data. Or rather you could, but you're already forced to write custom code to talk to the API, so it wouldn't make any sense to do it that way.
    – davidalger
    Aug 19 '13 at 13:39
  • Another good question is how many SKUs will you have, and what percentage of the set will have updated SKUs on a given cycle?
    – davidalger
    Aug 19 '13 at 13:40
  • I've tried to post the original code but the site won't allow me too. It uses SOAP protocol to access this xml: [webservices.smiffys.com/services/products.asmx/… and update the database with the "Product Code" in the xml being the sku number.
    – user2957
    Aug 19 '13 at 15:28
6

The model that Magento uses to maintain inventory levels is:

Mage_CatalogInventory_Model_Stock_Item

Which is represented in the database by:

cataloginventory_stock_item

While it is entirely possible to update the inventory level by directly writing to the database, there are a number of processes that depend on the stock level that the model looks after in its code when an item quantity is changed, including:

  • Stock status index
  • Low stock notifications
  • Back orders

If you want to go the direct db write route, you'll need to determine which of these processes are impacted and need modification (eg whether you'll need to trigger a stock status index update after your write or whether you also need to update that table as well).

There is no guarantee that Magento will maintain backward compatibility between releases with its db structure (in fact you can assume it won't). You should consider direct db writes to be akin to core edits. However, sometimes it does make the most sense to go this route for speed and efficiently, especially if you're dealing with a large catalog (100K+ skus), but it's not for the faint of heart.

Beware, db deadlocks are entirely possible if you're writing to multiple tables inside a transaction (especially index tables).

Take a look at:

cataloginventory_stock_item::_beforeSave()
cataloginventory_stock_item::_afterSave()
cataloginventory_stock_item::subtractQty()
cataloginventory_stock_item::addQty()

If you want to get an idea what Magento is doing when an item's inventory quantity is changing.

If the size of your inventory doesn't necessitate direct db writes, you can use the model to make your inventory changes in a stand alone script. Something along the lines of:

include_once "Mage.php";  //Adjust for current path to Mage.php
Mage::app()->setCurrentStore(0);


//Some db calls to get $sku and $qty and start the loop

$product = Mage::getModel('catalog/product')->load($sku, 'sku'); 
$stockItem = $product->getStockItem();
$stockItem->setData('qty', $qty);
$stockItem->save();

//End of the loop

Set within a loop should do the trick. Standard disclaimers about testing in a non-production environment apply. The second method may start to bog down for large numbers of skus, you'll need to test.

2

Magento save the stock information in two table :

  • cataloginventory_stock_item
  • cataloginventory_stock_status

Any update related to product stock/inventory should update these two table. Use query as below:

UPDATE 
cataloginventory_stock_item item_stock, 
cataloginventory_stock_status status_stock
SET 
item_stock.qty = '$new_quantity', 
item_stock.is_in_stock = IF('$new_quantity'>0, 1,0),
status_stock.qty = '$new_quantity', 
status_stock.stock_status = IF('$new_quantity'>0, 1,0)
WHERE 
item_stock.product_id = '$product_id' AND
item_stock.product_id = status_stock.product_id

Product ID for a particular SKU can be fetched from catalog_product_entity table in magento.

Column name is entity_id in catalog_product_entity table

which is Column product_id in cataloginventory_stock_item and cataloginventory_stock_status table

PHP Query for the same will be like :

if ($someCondition) {
$sql = "UPDATE {$cataloginventory_stock_item} SET qty = '{$new_quantity}', is_in_stock = '{$status}' WHERE product_id = $id";
$connectionWrite->query ( $sql );
$sql = "UPDATE {$cataloginventory_stock_status} SET qty = '{$new_quantity}', stock_status = '{$status}' WHERE product_id = $id";
$connectionWrite->query ( $sql );
}

As requested, I am adding the details of the variable used in the above code :

$resource = Mage::getSingleton('core/resource');
$cataloginventory_stock_item = $resource->getTableName('cataloginventory/stock_item');
$catalog_product_entity = $resource->getTableName('catalog/product');
$cataloginventory_stock_status = $resource->getTableName('cataloginventory/stock_status');              

Hope it will help you. Let me know if more detail required.

4
  • What about indexing ? How you will run indexing to make sure that magento is using correct quantity and status ? May 31 '16 at 7:40
  • Did you face any problem after reindexing. May 31 '16 at 8:06
  • Your answer is incomplete without indexing May 31 '16 at 8:46
  • Just now i have tested/checked with reindexing ... it is working fine ... let me know if you are facing any problem. Jun 1 '16 at 7:23
0

you can sue this query to do direct injection in database :

it is fatest way to update inventry and disable enable products as per inventory :

$database = mysql_query("UPDATE cataloginventory_stock_item item_stock, cataloginventory_stock_status status_stock SET item_stock.qty = '$qty', item_stock.is_in_stock = $stockstatus, status_stock.qty = '$qty', status_stock.stock_status = $stockstatus WHERE item_stock.product_id = '$magentoid' AND item_stock.product_id = status_stock.product_id") or die(mysql_error());

if($qty == "0") {

$disableproduct = "UPDATE catalog_product_entity_int SET value = '2' WHERE catalog_product_entity_int.attribute_id =96 and catalog_product_entity_int.entity_id = '".$magentoid."'"; $querydata = mysql_query($disableproduct) or die(mysql_error()); }

else {

$disableproduct = "UPDATE catalog_product_entity_int SET value = '1' WHERE catalog_product_entity_int.attribute_id =96 and catalog_product_entity_int.entity_id = '".$magentoid."'"; $querydata = mysql_query($disableproduct) or die(mysql_error());

}

4
  • 1
    Might answer the question but firing queries into the magento database is a dangerous thing. Be careful, and NOW WHAT YOU DO. Dec 19 '13 at 0:35
  • you know he want to run inventory every 15 minutes and if he run cron job by Mage.php it takes long time. and if sku's are more then not possible to update inventory with Mage.php we have to do Inventory update with direct database injection Dec 20 '13 at 1:11
  • I understand the motivation and it is totally ok. I just want to warn, because editing the magento database is risky :) Dec 20 '13 at 12:40
  • I am totally agree with you... Dec 20 '13 at 22:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.