Custom script to update stock levels

Question

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

Answer 1

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.

Answer 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.

Answer 3

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());

}