Configurable attribute option pricing

 如上图所见的配置。

 

说明这个可配置商品的可变价格属性是数量。和颜色属性无关。当数量是2个的时候它会价格有变化。

 

它是Magento内置的功能，这样配置后前台可以再选择不同的option value的时候使得价格发生变化。

 

而控制这部分数据的。

是这张表：

SELECT * FROM  catalog_product_super_attribute_pricing

 表数据如下：

 

其他字段很明显，product_super_attribute_id来自于关联表catalog_product_super_attribute

select * from catalog_product_super_attribute

 

 

我们的脚本是需要通过产品ID和属性ID找到product_super_attribute_id

然后插入价格数据到catalog_product_super_attribute_pricing表。

下面的范例是只针对2维1变维的产品数据

class Mage_Shell_UpdateOptionPricing extends Mage_Shell_Abstract
{
   public function run(){
   	
   	$productConfig = Mage::getResourceModel('catalog/product_collection')
   	->addAttributeToFilter('type_id', 'configurable')
   	->addAttributeToSelect(array('name', 'price'));
   	//->addAttributeToFilter('entity_id','3763');
   	$write = Mage::getSingleton('core/resource')->getConnection('core_write');
   	foreach ($productConfig as $_product)
   	{
   		// load the configurable product
   		$_product = Mage::getModel('catalog/product')->load($_product->getId());
   		var_dump ($_product->getName());
   		var_dump ($_product->getPrice());
   		// Collect options applicable to the configurable product
   		$productAttributeOptions = $_product->getTypeInstance(true)->getConfigurableAttributesAsArray($_product);
   		$attributeOptions = array();
   		if(count($productAttributeOptions) == 1){ 
	   		foreach ($productAttributeOptions as $productAttribute) {
   				//只有一个选项
	   			var_dump('on option:' . $productAttribute['label']);
	   			foreach ($productAttribute['values'] as $attribute)
	   			{
	   				$product_id = (int)$_product->getId();
	   				$data = array();
	   				$change_price = 0;
	   				$data['product_id'] = substr($_product->getSku(),2);
	   				$value_index = $attribute['value_index'];
	   				$label = $attribute['label'];
	   				$data['option_value_name'] = $label;
	   				$price = $this->getOnePrice($data);
	   				if ($price != $_product->getPrice()) {
	   					$change_price = $price - $_product->getPrice();
	   					$website_id = (int)Mage::app()->getWebsite()->getId();
	   					$sql = "insert into catalog_product_super_attribute_pricing(`product_super_attribute_id`,`value_index`,`is_percent`,`pricing_value`,`website_id`) values((select product_super_attribute_id from  catalog_product_super_attribute where product_id={$product_id}),{$value_index},0,{$change_price},{$website_id})";
	   					$write->query($sql);
	   					var_dump ('change price:' . $change_price . ' on attribute:' . $label);
	   				}
	   			}
	   		}
	   	} else {
	   		$one_attribute_option_name = $productAttributeOptions[0]['label'];//变量x
	   		$sec_attribute_option_name = $productAttributeOptions[1]['label'];//变量y
	   		
	   		if ($this->isChangeVar($one_attribute_option_name,(int)substr($_product->getSku(),2))) {
	   			//第一套是变量
	   			foreach ($productAttributeOptions[0]['values'] as $attribute) {
	   				$data = array();
	   				$change_price = 0;
	   				$product_super_attribute_id = $attribute['product_super_attribute_id'];
	   				$value_index = $attribute['value_index'];
	   				$data['product_id'] = substr($_product->getSku(),2);
	   				$label = $attribute['label'];
	   				$data['option_value_name'] = $label;
	   				$price = $this->getFirstPrice($data);
	   				if ($price != $_product->getPrice()) {
	   					$change_price = $price-$_product->getPrice();
	   					$website_id = (int)Mage::app()->getWebsite()->getId();	   						
	   					if (!$this->existsRow(array('product_super_attribute_id'=>$product_super_attribute_id,'value_index'=>$value_index))) {
	   						$sql = "insert into catalog_product_super_attribute_pricing(`product_super_attribute_id`,`value_index`,`is_percent`,`pricing_value`,`website_id`) values({$product_super_attribute_id},{$value_index},0,{$change_price},{$website_id})";	
	   						$write->query($sql);
	   						var_dump ('change price:' . $change_price . ' on attribute:' . $label);
	   					} else {
	   						var_dump('exits price:' . $change_price . ' on attribute:' . $label);
	   					}
	   					
	   					
	   				}
	   			}
	   			
	   		} else if ($this->isChangeVar($sec_attribute_option_name,(int)substr($_product->getSku(),2))) {
	   			//第二套是变量
	   			foreach ($productAttributeOptions[1]['values'] as $attribute) {
	   				$data = array();
	   				$change_price = 0;
	   				$product_super_attribute_id = $attribute['product_super_attribute_id'];
	   				$value_index = $attribute['value_index'];
	   				$data['product_id'] = substr($_product->getSku(),2);
	   				$label = $attribute['label'];
	   				$data['option_value_name'] = $label;
	   				$price = $this->getFirstPrice($data);
	   				if ($price != $_product->getPrice()) {
	   					$change_price = $price-$_product->getPrice();
	   					$website_id = (int)Mage::app()->getWebsite()->getId();
	   					if (!$this->existsRow(array('product_super_attribute_id'=>$product_super_attribute_id,'value_index'=>$value_index))) {
	   						$sql = "insert into catalog_product_super_attribute_pricing(`product_super_attribute_id`,`value_index`,`is_percent`,`pricing_value`,`website_id`) values({$product_super_attribute_id},{$value_index},0,{$change_price},{$website_id})";
	   						$write->query($sql);
	   						var_dump ('change price:' . $change_price . ' on attribute:' . $label);
	   					} else {
	   						var_dump('exits price:' . $change_price . ' on attribute:' . $label);
	   					}
	   			    }
	   			}
	   		}
	   	}
   	}
   	echo '~fin';
   }
   
   public function getFirstPrice($data) {
   	$read= Mage::getSingleton('core/resource')->getConnection('core_read');
   	$sql = "select * from old_product_option_value_two where product_id = ? and option_value_name = ? limit 1";
   	$results = $read->fetchAll($sql,array($data['product_id'],$data['option_value_name']));
   	foreach ($results as $row) {
   		return $row['price'];
   	}
   }
   
   public function getOnePrice($data) {
   	$read= Mage::getSingleton('core/resource')->getConnection('core_read');
   	$sql = "select * from old_product_option_value where product_id = ? and option_value_name = ?";
   	$results = $read->fetchAll($sql,array($data['product_id'],$data['option_value_name']));
   	foreach ($results as $row) {
   		return $row['price'];
   	}
   }
   public function existsRow($data) {
   	$read= Mage::getSingleton('core/resource')->getConnection('core_read');
   	$sql = "select * from catalog_product_super_attribute_pricing where product_super_attribute_id = ? and value_index = ?";
   	$results = $read->fetchAll($sql,array($data['product_super_attribute_id'],$data['value_index']));
   	return count($results);
   	
   	
   }
   
   /**
    * 判断是不是一个有决定性作用的变量。
    * @param unknown $option_name
    * @param unknown $product_id
    */
   public function isChangeVar($option_name,$product_id) {
   	$read= Mage::getSingleton('core/resource')->getConnection('core_read');
   	//先找任意1个非该属性的值出来
   	$sql = "select * from old_product_option_value_two where product_id={$product_id} and option_name != '{$option_name}' limit 1";
   	$results = $read->fetchAll($sql);
   	$y = array();
   	foreach ($results as $row) {
   		//取得对比值。y
   		$y['option_name'] = $row['option_name'];
   		$y['option_value_name'] = $row['option_value_name'];
   		
   	}
   	
   	$sql = "select * from old_product_option_value_two where product_id={$product_id} and option_name_2='{$y['option_name']}' and option_value_name_2 = '{$y['option_value_name']}'";
   	$results = $read->fetchAll($sql);
   	$base_price = $results[0]['price'];
   	foreach($results as $row_res) {
   		if ($row_res['price']!=$base_price) {
   			return true;
   		}
   	}
   	return false;
   }
}