Stock Level Import Script

Last modified by FerdiUSA on Thu, June 24, 2010 16:21
Source|Old Revisions  

This is an old revision of the document!


A popular problem with Magento is how to import stock levels from another system, e.g. an ERP system. Although Magento has the API for easily carrying out such tasks it is s-l-o-w. Hence, here is an example of how to do it quickly and easily. This exceptionally well documented, clean code is thanks to michael_g08, see thread here: http://www.magentocommerce.com/boards/viewthread/32491

  1. <?php
  2. ////////////////GLOBAL DEFINITIONS/////////////////////
  3. //DATABASE SETTINGS
  4. $dbConfig = array(
  5.     'host'      => 'HOSTNAME',
  6.     'username'  => 'USERNAME',
  7.     'password'  => 'PASSWORD',
  8.     'dbname'    => 'MAGENTO DB NAME',
  9.     'driver_options'=> array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES UTF8')
  10. );
  11.  
  12. ///// Path to Your CSV file
  13. $path = '/var/www/html/MAGENTO/import/WebStockInput.csv';
  14.  
  15. // Path to Mage.php 
  16. require_once '/var/www/html/MAGENTO/app/Mage.php';
  17. // Connect to the databases
  18. $db_magento = Zend_Db::factory('Pdo_Mysql', $dbConfig);
  19.  
  20. updateTempTableFromFile($db_magento, $path);   
  21.    
  22. $query = $db_magento->select()->from('temp_table');
  23. $contents_array = $db_magento->fetchAll($query);
  24.  
  25. // Run for each line
  26. foreach ($contents_array as $line) {
  27.    
  28.     $sku = $line['sku'];
  29.     $qty = $line['qty'];
  30.            
  31.     $exists = $db_magento->query("SELECT COUNT(sku) cnt FROM catalog_product_entity WHERE sku = '$sku' LIMIT 1");
  32.     $find_product = (($exists->fetchObject()->cnt) > 0) ? true : false;
  33.  
  34.     if ($find_product == true) {
  35.         $entity_id = getEntityID_bySKU($db_magento, $sku);   
  36.         updateQTY ($db_magento, $entity_id, $qty);
  37.     }
  38. }
  39.  
  40. function getEntityID_bySKU($db_magento, $sku) {
  41.     $entity_row = $db_magento->query("SELECT entity_id FROM catalog_product_entity p_e WHERE p_e.sku = '$sku'")->fetchObject();
  42.     $entity_id  = $entity_row->entity_id;
  43.     return $entity_id;
  44. }
  45.  
  46.  
  47. function updateQTY($db_magento, $entity_id, $qty) {
  48.      $db_magento->query("UPDATE cataloginventory_stock_item s_i, cataloginventory_stock_status s_s
  49.          SET   s_i.qty = '$qty', s_i.is_in_stock = IF('$qty'>0, 1,0),
  50.                s_s.qty = '$qty', s_s.stock_status = IF('$qty'>0, 1,0)
  51.          WHERE s_i.product_id = '$entity_id' AND s_i.product_id = s_s.product_id ");
  52. }
  53.  
  54. function updateTempTableFromFile($db_magento, $path){
  55.     $db_magento->query("TRUNCATE TABLE temp_table");
  56.  
  57.     $db_magento->query("LOAD DATA INFILE '$path'
  58.                       INTO TABLE temp_table
  59.                       CHARACTER SET cp1251
  60.                       FIELDS TERMINATED BY ','
  61.                       LINES TERMINATED BY 'n'
  62.                       (`store`,`sku`, `qty`,`is_in_stock`)");
  63. }
  64.  
  65. function getAttributeEAV_id ($db_magento, $attribute_name)
  66. {
  67.     $result = $db_magento->query("SELECT attribute_id FROM eav_attribute WHERE entity_type_id = 4 AND attribute_code = '$attribute_name'")->fetchObject()->attribute_id;
  68.     return $result;
  69. }



 

Magento 2 GitHub Repository

Magento Job Board - Some sort of tag line goes here

Latest Posts| View all Jobs