Stock Level Import Script

Last modified by Discovery 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

<?php
////////////////GLOBAL DEFINITIONS/////////////////////
//DATABASE SETTINGS 
   $dbConfig = array(
            'host'      => 'HOSTNAME',
            'username'  => 'USERNAME',
            'password'  => 'PASSWORD',
            'dbname'    => 'MAGENTO DB NAME',
            'driver_options'=> array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES UTF8')
    );

///// Path to Your CSV file
$path = '/var/www/html/MAGENTO/import/WebStockInput.csv';

// Path to Mage.php  
   require_once '/var/www/html/MAGENTO/app/Mage.php';
// Connect to the databases
   $db_magento = Zend_Db::factory('Pdo_Mysql', $dbConfig);


   updateTempTableFromFile($db_magento, $path);    
    
   $query = $db_magento->select()->from('temp_table');
   $contents_array = $db_magento->fetchAll($query);
  
// Run for each line
foreach($contents_array as $line) {
    
        $sku = $line['sku'];
        $qty = $line['qty'];
            
    $exists = $db_magento->query("SELECT COUNT(sku) cnt FROM catalog_product_entity WHERE sku = '$sku' LIMIT 1");
    $find_product = (($exists->fetchObject()->cnt) > 0) ? true : false;

        if ($find_product == true) { 
            $entity_id = getEntityID_bySKU($db_magento, $sku);   
            updateQTY ($db_magento, $entity_id, $qty); 
        }
}

function getEntityID_bySKU($db_magento, $sku) {
    $entity_row = $db_magento->query("SELECT entity_id FROM catalog_product_entity p_e WHERE p_e.sku = '$sku'")->fetchObject();
    $entity_id  = $entity_row->entity_id;
    return $entity_id;
}


function updateQTY ($db_magento, $entity_id, $qty) {
     $db_magento->query("UPDATE cataloginventory_stock_item s_i, cataloginventory_stock_status s_s 
                    SET     s_i.qty = '$qty', s_i.is_in_stock = IF('$qty'>0, 1,0), 
                    s_s.qty = '$qty', s_s.stock_status = IF('$qty'>0, 1,0)
              WHERE s_i.product_id = '$entity_id' AND s_i.product_id = s_s.product_id ");
}

function updateTempTableFromFile($db_magento, $path){
  $db_magento->query("TRUNCATE TABLE temp_table");
  
  $db_magento->query("LOAD DATA INFILE '$path'
                      INTO TABLE temp_table
                      CHARACTER SET cp1251
                      FIELDS TERMINATED BY ','
                      LINES TERMINATED BY 'n'
                      (`store`,`sku`, `qty`,`is_in_stock`)");
}

function getAttributeEAV_id ($db_magento, $attribute_name)
{
  $result = $db_magento->query("SELECT attribute_id FROM eav_attribute WHERE entity_type_id = 4 AND attribute_code = '$attribute_name'")->fetchObject()->attribute_id;
  return $result;
}