分类： MySql

批量更新库存，价格等简单属性的字段值，magento自带的import/export就可以实现。

Group Price是没有自带功能或者免费插件可以实现批量更新的字段。（其实tier price也是）

下面是整理出可以用的脚本文件，收集整理来源与网络。

主要参考的有：

https://community.magento.com/t5/Programming-Questions/update-group-prices-from-CSV-file-programmatically/m-p/43281#

stackoverflow准备用SOAP API

API方法二

 

对SOAP不是很熟悉，还是采用更直接的PHP脚本：

<?php
define('MAGENTO', realpath(dirname(__FILE__))); 
 require_once MAGENTO . '/app/Mage.php'; 
 Mage::setIsDeveloperMode(true);
ini_set('display_errors', 1);
 umask(0); 
 Mage::app()->setCurrentStore(Mage_Core_Model_App::ADMIN_STORE_ID); 
 $count = 0; 
 

 set_time_limit(0);
ini_set('memory_limit','1024M');
 
/***************** UTILITY FUNCTIONS ********************/
function _getConnection($type = 'core_read'){
    return Mage::getSingleton('core/resource')->getConnection($type);
}
 
function _getTableName($tableName){
    return Mage::getSingleton('core/resource')->getTableName($tableName);
}
 
function _getAttributeId($attribute_code = 'price'){
    $connection = _getConnection('core_read');
    $sql = "SELECT attribute_id
                FROM " . _getTableName('eav_attribute') . "
            WHERE
                entity_type_id = ?
                AND attribute_code = ?";
    $entity_type_id = _getEntityTypeId();
    return $connection->fetchOne($sql, array($entity_type_id, $attribute_code));
}
 
function _getEntityTypeId($entity_type_code = 'catalog_product'){
    $connection = _getConnection('core_read');
    $sql        = "SELECT entity_type_id FROM " . _getTableName('eav_entity_type') . " WHERE entity_type_code = ?";
    return $connection->fetchOne($sql, array($entity_type_code));
}
 
function _checkIfSkuExists($sku){
    $connection = _getConnection('core_read');
    $sql        = "SELECT COUNT(*) AS count_no FROM " . _getTableName('catalog_product_entity') . " WHERE sku = ?";
    $count      = $connection->fetchOne($sql, array($sku));
    if($count > 0){
        return true;
    }else{
        return false;
    }
}
 
function _getIdFromSku($sku){
    $connection = _getConnection('core_read');
    $sql        = "SELECT entity_id FROM " . _getTableName('catalog_product_entity') . " WHERE sku = ?";
    return $connection->fetchOne($sql, array($sku));
}
 
function _updateStocks($data){
    $connection     = _getConnection('core_write');
    $sku            = $data[0];
    $newQty         = $data[1];
    $productId      = _getIdFromSku($sku);
    $attributeId    = _getAttributeId();
 
    $sql            = "UPDATE " . _getTableName('cataloginventory_stock_item') . " csi,
                       " . _getTableName('cataloginventory_stock_status') . " css
                       SET
                       csi.qty = ?,
                       csi.is_in_stock = ?,
                       css.qty = ?,
                       css.stock_status = ?
                       WHERE
                       csi.product_id = ?
                       AND csi.product_id = css.product_id";
					   
					   ;
    $isInStock      = $newQty > 0 ? 1 : 0;
    $stockStatus    = $newQty > 0 ? 1 : 0;
    $connection->query($sql, array($newQty, $isInStock, $newQty, $stockStatus, $productId));
}


function _updatePrices($data){
	
	
    $connection     = _getConnection('core_write');
    $sku            = $data[0];

	$entity_id = _getIdFromSku($sku);
	
	$newPrice1       = $data[2];
     $newPrice2       = $data[3];
     $newPrice4       = $data[4];
     $newPrice5       = $data[6];
	
	$id_newPrice1 = '4' ;
	 $id_newPrice2 = '5';
	 $id_newPrice4='6' ;
	 $id_newPrice5 = '3';
	 
	 $combind_groupid_groupprice = array(
											array($newPrice1 ,$id_eco_3pb),
											array($newPrice2,$id_newPrice2),
											array($newPrice4,$id_newPrice4),
											array($newPrice5 ,$id_newPrice5 )
											);
	
		foreach ($combind_groupid_groupprice as $groupid_groupprice){
    /*  catalog_product_entity_group_price table */
		if($groupid_groupprice[0] > 0){
		
		$sql_price_one = "INSERT INTO " . _getTableName('catalog_product_entity_group_price') . " 
		(entity_id,all_groups,customer_group_id,value,website_id)
							VALUES (?,?,?,?,?) ON DUPLICATE KEY UPDATE 
					  value = ?,customer_group_id =?";
		$connection->query($sql_price_one, array($entity_id,0,$groupid_groupprice[1],$groupid_groupprice[0],0,
									$groupid_groupprice[0],$groupid_groupprice[1]));
		
		}
		else{}
		
		}
		
}

/***************** UTILITY FUNCTIONS ********************/
 
$csv                = new Varien_File_Csv();
$data               = $csv->getData(MAGENTO . '/var/import/updateGP.csv'); //path to csv
array_shift($data);
 
$message = '';
$count   = 1;
foreach($data as $_data){
    if(_checkIfSkuExists($_data[0])){
        try{
            //_updateStocks($_data);  //update qty
			
			_updatePrices($_data);
            $message .= $count . '> Success:: Qty (' . $_data[1] . ') of Sku (' . $_data[0] . ') has been updated.
<br />';
 
        }catch(Exception $e){
            $message .=  $count .'> Error:: while Upating  Qty (' . $_data[1] . ') of Sku (' . $_data[0] . ') => '.$e->getMessage().'
<br />';
        }
    }else{
        $message .=  $count .'>>> Error:: Product with Sku (' . $_data[0] . ') does\'t exist.
<br />';
    }
    $count++;
}
echo $message;

 

csv文件放在对应的path然后第一列是sku，第二列是qty，第三group pirce，注意id的对应关系。

tier price大致类似，找到对应数据库就好。

在magento论坛里的增加后面几个保存价格index特tmp的表格，我没有些，还是交给自带的index更新吧