Adding a new attribute to sales_flat_order_item table can be achieved through the XML config file and creating an install script. There is a particular method for doing this which can be seen in any of the core upgrade and install scripts of core modules.
You have to add the column though to many tables, not just the order item. When a customer is adding items to their basket they are building up a quote. You will need the need column on the sales_flat_quote_item table first so when the customer adds the item to the basket the data gets stored against the quote.
When the customer places the order, the quote gets converted to an order, so you need all the item data from the quote carried over to the order item as well. This is where XML in your module config can be used to achieve your desired result. You will also need an observer to put the data against the quote item when the customer clicks add to basket.
Consider the following example;
You have a product which has a deposit custom attribute set against it.
You then want this value stored against the quote item and the order item when a customer wants or buys this item. So you create an install script similar to the following in your custom module;
<?php
$installer = $this;
$installer->installEntities();
$setup = new Mage_Sales_Model_Mysql4_Setup('core_setup');
$setup->startSetup();
$setup->addAttribute(
'order_item',
'base_deposit_price',
array(
'type' => Varien_Db_Ddl_Table::TYPE_DECIMAL,'default' => 0,'visible' => true
)
);
$setup->addAttribute(
'quote_item',
'base_deposit_price',
array(
'type' => Varien_Db_Ddl_Table::TYPE_DECIMAL,'default' => 0,'visible' => true
)
);
$setup->addAttribute(
'invoice_item',
'base_deposit_price',
array(
'type' => Varien_Db_Ddl_Table::TYPE_DECIMAL,'default' => 0,'visible' => true,
)
);
$setup->addAttribute(
'creditmemo_item',
'base_deposit_price',
array(
'type' => Varien_Db_Ddl_Table::TYPE_DECIMAL,'default' => 0,'visible' => true
)
);
$setup->endSetup();
What this does is adds the columns to your tables nicely as part of your module install script.
Then in your modules config.xml file you need something like the following XML defining;
<?xml version="1.0"?>
<config>
<global>
<sales>
<quote>
<item>
<product_attributes>
<base_deposit_price />
</product_attributes>
</item>
</quote>
<order>
<item>
<product_attributes>
<base_deposit_price />
</product_attributes>
</item>
</order>
</sales>
<fieldsets>
<sales_convert_quote_item>
<base_deposit_price>
<to_order_item>*</to_order_item>
</base_deposit_price>
</sales_convert_quote_item>
<sales_convert_order_item>
<base_deposit_price>
<to_cm_item>*</to_cm_item>
<to_invoice_item>*</to_invoice_item>
</base_deposit_price>
</sales_convert_order_item>
</fieldsets>
<events>
<sales_quote_item_set_product>
<observers>
<quoteitem_set_deposit_data>
<type>singleton</type>
<class>YourNameSpace_YourModule_Model_Observer</class>
<method>setDepositOnQuoteItem</method>
</quoteitem_set_deposit_data>
</observers>
</sales_quote_item_set_product>
</events>
</global>
</config>
The config file has some definitions which basically allow magento to automatically copy the values over from the quote_item table to the order_item table. The example above also copies the values to credit memos items and invoice items from the order as well.
The magic bit is the observer which sets the data on the quote item in the first place when the customer adds the item to their basket.
<?php
class YourNameSpace_YpurModule_Model_Observer {
/**
* Flag to stop observer executing more than once
*
* @var static bool
*/
static protected $_singletonFlag = false;
/*
* Gets Deposit Price Values For Quote Product Items. These Will Later Be
* Be Converted To Order Items, Invoice Items, & Credit Memo Items.
*
*/
public function setDepositOnQuoteItem($oObserver) {
$oProduct = $oObserver->getProduct();
$oQuoteItem = $oObserver->getQuoteItem();
$deposit = $oProduct->getData('deposit_price', null);
if ($deposit > 0) {
$oQuoteItem->setData('base_deposit_price', $deposit);
}
}
}
You could add your product attribute through the admin Product Attributes system, or you could code your module to also setup the custom product attribute you want to put on the quote item and order item as well by using an EAV setup script;
class YourNameSpace_YourModule_Model_Resource_Eav_Mysql4_Setup extends Mage_Eav_Model_Entity_Setup
{
public function getDefaultEntities()
{
return array(
'catalog_product' => array(
'entity_model' => 'catalog/product',
'attribute_model' => 'catalog/resource_eav_attribute',
'table' => 'catalog/product',
'additional_attribute_table' => 'catalog/eav_attribute',
'entity_attribute_collection' => 'catalog/product_attribute_collection',
'attributes' => array(
'deposit_price' => array(
'group' => 'Prices',
'label' => 'Deposit Price',
'type' => 'decimal',
'input' => 'price',
'default' => '0',
'class' => 'validate-number',
'frontend' => '',
'source' => '',
'global' => Mage_Catalog_Model_Resource_Eav_Attribute::SCOPE_STORE,
'visible' => true,
'required' => false,
'user_defined' => false,
'searchable' => false,
'filterable' => false,
'comparable' => true,
'visible_on_front' => true,
'visible_in_advanced_search' => false,
'unique' => false,
'backend' => 'catalog/product_attribute_backend_price',
),
)
)
);
}
}
Hopefully that should help you achieve what you want to do in the correct way. Just build it all as a custom module.
