I have been struggling with this issue for a few days now. I have inherited a website built on Magento. This website sells products that can be all customized, and every product is built to order. My problem occurs when a customer wants to add a particular logo to a product, and then chooses a thread color that they want the logo to be stitched with. There are a maximum of 15 logos available and each logo has it's own set of available thread colors. Lets say the customer chooses logo #6, and then chooses a thread from the available colors for logo #6 and adds it to the cart. When the item is quoted it shows "Thread Color 6" in the cart for the thread options. This is fine and the order is processed correctly. However when you view the order on the admin side it will show all the thread color options including the one they chose. So if the item had 6 logo options and they choose logo five as the option, on the admin side it shows like this:
Thread Color 1: No Thread
Thread Color 2: No Thread
Thread Color 3: No Thread
Thread Color 4: No Thread
Thread Color 5: Name of Thread color
Thread Color 6: No Thread
Like I stated above the order is processed correctly it is just confusing to the customer service department when looking at orders.
So what I was trying to do was find the thread color that has a value set, then change the label of it to something generic like "Thread Color", then remove all other thread color options from the quote dynamically. I found a possible solution here, and Vinai does a great job explaining the code. I have tried this out and have even stepped through line by line and I know it is adding and removing the options as needed. However when I remove any die() statements after debugging I get a SQL error. Below is my config, observer, and SQL error.
config.xml
<events>
<checkout_cart_product_add_after>
<observers>
<customoptions>
<type>singleton</type>
<class>Lmc_Threadcolor_Model_Product_Observer</class>
<method>checkoutCartProductAddAfter</method>
</customoptions>
</observers>
</checkout_cart_product_add_after>
<sales_convert_quote_item_to_order_item>
<observers>
<customoptions>
<type>singleton</type>
<class>Lmc_Threadcolor_Model_Product_Observer</class>
<method>salesConvertQuoteItemToOrderItem</method>
</customoptions>
</observers>
</sales_convert_quote_item_to_order_item>
</events>
Observer.php
public function salesConvertQuoteItemToOrderItem(Varien_Event_Observer $observer)
{
$quoteItem = $observer->getItem();
if ($additionalOptions = $quoteItem->getOptionByCode('additional_options'))
{
$orderItem = $observer->getOrderItem();
$options = $orderItem->getProductOptions();
$options['additional_options'] = unserialize($additionalOptions->getValue());
$orderItem->setProductOptions($options);
}
}
public function checkoutCartProductAddAfter(Varien_Event_Observer $observer)
{
$item = $observer->getQuoteItem();
$infoArr = array();
if ($info = $item->getProduct()->getCustomOption('info_buyRequest'))
{
$infoArr = unserialize($info->getValue());
}
// Set additional options in case of a reorder
if ($infoArr && isset($infoArr['additional_options']))
{
// An additional options array is set on the buy request - this is a reorder
$item->addOption(array(
'code' => 'additional_options',
'value' => serialize($infoArr['additional_options'])
));
return;
}
$options = Mage::helper('catalog/product_configuration')->getCustomOptions($item);
foreach ($options as $option)
{
// The only way to identify a custom option without
// hardcoding ID's is the label :-(
// But manipulating options this way is hackish anyway
if(preg_match('/select thread color/', strtolower($option['label'])) && strtolower($option['value']) != 'no thread')
{
$optId = $option['option_id'];
// Add replacement custom option with modified value
$additionalOptions = array(array(
'code' => 'lmc_custom_thread',
'label' => 'Thread Color',
'value' => $option['value'],
'print_value' => $option['print_value'],
'option_id' => $option['option_id'],
'option_type' => $option['option_type'],
'custom_view' => '',
'image_path' => $option['image_path'],
));
$item->addOption(array(
'code' => 'additional_options',
'value' => serialize($additionalOptions),
));
// Update info_buyRequest to reflect changes
if ($infoArr &&isset($infoArr['options']) && isset($infoArr['options'][$optId]))
{
// Remove real custom option
unset($infoArr['options'][$optId]);
// Add replacement additional option for reorder (see above)
$infoArr['additional_options'] = $additionalOptions;
$info->setValue(serialize($infoArr));
$item->addOption($info);
}
// Remove real custom option id from option_ids list
if ($optionIdsOption = $item->getProduct()->getCustomOption('option_ids'))
{
$optionIds = explode(',', $optionIdsOption->getValue());
if (false !== ($idx = array_search($optId, $optionIds)))
{
unset($optionIds[$idx]);
$optionIdsOption->setValue(implode(',', $optionIds));
$item->addOption($optionIdsOption);
}
}
// Remove real custom option
$item->removeOption('option_' . $optId);
}
}
}
SQL Error
SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':1:{i:0;a:8:{s:4:"code";s:17:"lmc_custom_thread";s:5:"label";s:12:"Thread Color"' at line 1
Trace:
#0 /var/www/html/lib/Zend/Db/Statement.php(300): Zend_Db_Statement_Pdo->_execute(Array)
#1 /var/www/html/lib/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Statement->execute(Array)
#2 /var/www/html/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('SELECT * FROM `...', Array)
#3 /var/www/html/lib/Varien/Db/Adapter/Pdo/Mysql.php(333): Zend_Db_Adapter_Pdo_Abstract->query('SELECT * FROM `...', Array)
#4 /var/www/html/lib/Zend/Db/Adapter/Abstract.php(753): Varien_Db_Adapter_Pdo_Mysql->query('SELECT * FROM `...', Array)
#5 /var/www/html/app/code/local/Mage/Sales/Model/Quote/Item.php(281): Zend_Db_Adapter_Abstract->fetchRow('SELECT * FROM `...')
#6 /var/www/html/app/code/local/Deg/Sales/Model/Quote/Item.php(17): Mage_Sales_Model_Quote_Item->setProduct(Object(Mage_Catalog_Model_Product))
#7 /var/www/html/app/code/core/Mage/Sales/Model/Mysql4/Quote/Item/Collection.php(224): Deg_Sales_Model_Quote_Item->setProduct(Object(Mage_Catalog_Model_Product))
#8 /var/www/html/app/code/core/Mage/Sales/Model/Mysql4/Quote/Item/Collection.php(136): Mage_Sales_Model_Mysql4_Quote_Item_Collection->_assignProducts()
#9 /var/www/html/lib/Varien/Data/Collection/Db.php(632): Mage_Sales_Model_Mysql4_Quote_Item_Collection->_afterLoad()
#10 /var/www/html/lib/Varien/Data/Collection.php(729): Varien_Data_Collection_Db->load()
#11 /var/www/html/app/code/core/Mage/Sales/Model/Quote.php(589): Varien_Data_Collection->getIterator()
#12 /var/www/html/app/code/core/Mage/Sales/Model/Quote.php(620): Mage_Sales_Model_Quote->getAllItems()
#13 /var/www/html/app/code/core/Mage/Checkout/Model/Cart.php(123): Mage_Sales_Model_Quote->hasItems()
#14 /var/www/html/app/code/core/Mage/Checkout/controllers/CartController.php(123): Mage_Checkout_Model_Cart->init()
#15 /var/www/html/app/code/core/Mage/Core/Controller/Varien/Action.php(418): Mage_Checkout_CartController->indexAction()
#16 /var/www/html/app/code/core/Mage/Core/Controller/Varien/Router/Standard.php(253): Mage_Core_Controller_Varien_Action->dispatch('index')
#17 /var/www/html/app/code/core/Mage/Core/Controller/Varien/Front.php(176): Mage_Core_Controller_Varien_Router_Standard->match(Object(Mage_Core_Controller_Request_Http))
#18 /var/www/html/app/code/core/Mage/Core/Model/App.php(340): Mage_Core_Controller_Varien_Front->dispatch()
#19 /var/www/html/app/Mage.php(627): Mage_Core_Model_App->run(Array)
#20 /var/www/html/index.php(80): Mage::run('', 'store')
#21 {main}
Any help would be greatly appreciated. Thank You.
