Jan 17, 2012
kalpesh

Magento: Adding column to sales_flat_order_item, sales_flat_invoice_item and sales_flat_shipment_item

Suppose you want to add column to some table before it gets save in Magento. Example, Magento doesn’t save regular price of product when an order is placed, it only saves the selling price. So if your product have some special price in it, then Magento only saves it’s special price when an order is placed, so there is no track of regular price of that product in order item table. Same it goes to invoice item and shipment item. When creating invoice and shipment, Magento doesn’t have any track on the regular price of invoiced item’s and shipment item’s regular price.

So here I show you how you will add a column “product_mrp” in each of 3 tables and update the information without firing any query!

First of all, make an installer script in your module that will alter these three tables and add column “product_mrp” or any of your choice.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
<?php
$installer = $this;

$installer->startSetup();

$installer->run("
    ALTER TABLE sales_flat_order_item ADD COLUMN product_mrp DECIMAL(12,4) NULL;
    ALTER TABLE sales_flat_invoice_item ADD COLUMN product_mrp DECIMAL(12,4) NULL;
    ALTER TABLE sales_flat_shipment_item ADD COLUMN product_mrp DECIMAL(12,4) NULL;
");

$installer->endSetup();

?>

After this is executed, you will find your columns added at the end of your tables.
Now we will catch the event before order is placed, before invoice is created and before shipment is saved.

config.xml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
<sales_order_place_before>
                <observers>
                    <adding_product_mrp_order>
                        <type>singleton</type>
                        <class>Namespace_Module_Model_Observer</class>
                        <method>saveProductMrpInOrder</method>
                    </adding_product_mrp_order>
                </observers>
            </sales_order_place_before>

           <sales_order_invoice_save_before>
                <observers>
                    <adding_product_mrp_invoice>
                        <type>singleton</type>
                        <class>Namespace_Module_Model_Observer</class>
                        <method>saveProductMrpInInvoice</method>
                    </adding_product_mrp_invoice>
                </observers>
            </sales_order_invoice_save_before>

            <sales_order_shipment_save_before>
                <observers>
                    <adding_product_mrp_shipment>
                        <type>singleton</type>
                        <class>Namespace_Module_Model_Observer</class>
                        <method>saveProductMrpInShipment</method>
                    </adding_product_mrp_shipment>
                </observers>
            </sales_order_shipment_save_before>

Now comes the Observer part that will add our column data before data is actually saved in table.

Observer.php

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
public function saveProductMrpInOrder(Varien_Event_Observer $observer) {
            $order = $observer->getEvent()->getOrder();
            foreach($order->getAllItems() as $item) {
                $price = Mage::getModel('catalog/product')->load($item->getId())->getPrice();
                $item->setProductMrp($price);
            }
          return $this;
        }

        public function saveProductMrpInInvoice(Varien_Event_Observer $observer) {
            $invoice = $observer->getEvent()->getInvoice();
            foreach($invoice->getAllItems() as $item) {
                $price = Mage::getModel('catalog/product')->load($item->getProductId())->getPrice();
                $item->setProductMrp($price);
            }
          return $this;
        }

        public function saveProductMrpInShipment(Varien_Event_Observer $observer)
    {
            $shipment = $observer->getEvent()->getShipment();
            foreach($shipment->getAllItems() as $item) {               
                $product = Mage::getModel('catalog/product')->load($item->getProductId());
                $price = $product->getPrice();
                $item->product_mrp = $price;
            }
        }

Now clear the cache, and place order and create it’s invoice and shipment. You will find the regular price of each of your products in all these three useful tables and hence you can track the original price information even if your product’s price has been changed afterwards.

Happy coding!

27 Comments

  • Hi,I’m not a geek with Magento but I have to do a similar task.
    I have to generate a random code in an additional column in sales_flat_order_item (one code for every product sold). I have created the column in the database table.
    I need an help to undertand which config.xml and observer.php edit.
    I don’t have an additional module. Can I edit:
    – app/code/core/Mage/Sales/Model/Observer.php
    and
    – app/code/core/Mage/Sales/etc/config.xml
    ???
    If yes, which line should I write instead of
    “Namespace_Module_Model_Observer”?
    Is “Mage_Sales_Model_Observer” right?
    Thanks in advance

    • Hi Michael. Editing core is not recommended, but if you want to edit it anyway, you will need to edit config.xml and add the event sales_order_place_before as per the given code. Instead of Namespace_Module_Model_Observer, you will need to write Mage_Sales_Model_Observer and inside that file you will have to create a method with name saveProductMrpInOrder if you are following my code. Don’t forget to clear cache before testing your changes.

      • Here is my code:
        in “Mage/Sales/etc/config.xml” I added this:

        singleton
        Mage_Sales_Model_Observer
        saveProductSpecialcodeInOrder

        in tags.

        in “Mage/Sales/Model/Observer.php” I added this:
        public function saveProductSpecialcodeInOrder(Varien_Event_Observer $observer) {
        Mage::log(“it works”.time().$test.PHP_EOL);
        }

        and developer mode is enabled, but id doesn’t seems to work. Nothing is written in var/log/system.log…
        any idea?

        • oh, it removes tags 🙂

          • Ok, I got it.
            I forgot the $test variable which was null and so the function crashed.
            Now it’s all ok, thanks a lot!

          • nice to hear everything is working in the end 🙂

  • hi I need to insert a column name called subscriber_name in the table newsletter_subscriber. I have tried your way. But its not working for me. Please suggest me what can be done.

    • You need to
      – write sql script to alter newsletter_subscriber table in your module’s sql directory
      – change the config.xml to match the new upgraded version of your sql upgrade script

      Let me know what you tried if it’s still not working.

  • It’s verry useful.
    Thanks for your tip!

  • hi , i want to get data of sales_flat_order_item on product view page … i am new in magento ..will u please explain me how to fetch sales_flat_order_item data on product view page ?
    i don’t know how to fetch data

    Thanks…

  • Hello Kalpesh,

    As per above instructions, I successfully able to add MRP value order but I am NOT able raise event for Invoice.
    \————————————————————————–
    In app/code/core/Mage/Sales/etc/config.xml
    \————————————————————————–

    singleton
    sales/observer
    saveProductMrpInInvoice

    \————————————————————————–
    In app/code/core/Mage/Sales/Model/Observer.php
    \————————————————————————–
    public function saveProductMrpInInvoice(Varien_Event_Observer $observer) {
    Mage::log(“saveProductMrpInInvoice–>”);
    return $this;
    }

    Am I missing anything? Please help me.

    • Sorry your code is not posted correctly. You can enclose your code inside {code} to format it properly. Also are you trying to modify core file? Don’t do it!

      • Hello Kalpesh,

        I sent details via email to you. I have followed every step you mentioned and it is working only for order and NOT for Invoice and Shipment.

  • Hello Kalpesh,

    Yet I could not find solution. Can you please help me here?

  • Hi kalpesh,
    can you give me the folder(module) or provide download link

    • Avijit, you will have to place the above code in your module. If you don’t have module you can create it in local and then put the code.

  • Hi kalpesh,
    It is not working for me.
    I have done following
    1)manually updated table with product_mrp field(3 tables)
    2)In /app/code/core/Mage/Sales/etc/config.xml updated the xml that you say in above just changed the Namespace_Module_Model_Observer to Mage_Sales_Model_Observer
    3)updated /app/code/core/Mage/Sales/Model/Observer.php with your code above
    4)refreshed cache

    But when order submit and going to paypal,i’ve checked db product_mrp is Null
    Am I missing anything?

  • Hi,

    i know it is wrong to change is base code,But provide some download link everyone will be benefited

    • I would put the echo’s and exit’s in Observer.php file to check if the observer method is being called or not. Also make sure your XML code is correct as Magento will not give you error if something is incorrect in XML. Better to create a new module with just config.xml and Observer.php, no need of sql script as you already ran it directly in DB. Send me your code if it still doesn’t work.

  • Hi kalpesh,

    It is working now.but “product_mrp” taking wrong price (randon price)

  • Hi Kalpesh,

    I am working on a website in magento 1.7. I have a requirement where the number of products available is restricted for a given delivery date, since the products are being made manually and only once the order is placed. So I have created an Attribute delivery_date_capacity on the product, set to 5. And I created another table deliverycapacity in magento. Here I will store the productId, DeliveryDate and a CapacityCounter. This counter will be incremented everytime an order is placed for a given date, or it will make an entry in here.
    Now I have created an observer class for event sales_order_place_before .

    singleton
    TruffleStreet_Observer_Model_Observer
    saveDeliveryCapacity

    Now in the method saveDeliveryCapacity, I want it to check if orders are available for the given delivery date from the deliverycapacity table.
    My function is not completed yet.
    public function saveDeliveryCapacity(Varien_Event_Observer $observer) {
    $order = $observer->getEvent()->getOrder();
    foreach($order->getAllItems() as $item) {
    $product = Mage::getModel(‘catalog/product’)->load($item->getId());
    Mage:log(“Product name : “);
    }
    return $this;
    }
    But when I place my order in magento, the entry in my log is:

    2013-12-18T10:51:07+00:00 ERR (3): Warning: log() expects parameter 1 to be double, string given in C:wampwwwcsappcodelocalTruffleStreetObserverModelObserver.php on line 28

    What is going wrong here? Also is this the right approach to take for my requirements?

    Thanks,
    Neet

    • Hi Neet. The problem is you are using Mage:log instead of Mage::log

  • Hi Kalpesh,
    Thanks for your help :). I had one more question regarding the Observer. I have created an Observer class with 2 methods:
    1) saveDeliveryCapacity is called when sales_order_place_after event is fired. This is working fine.
    2) checkDeliveryCapacity which is to be called when sales_order_place_before event is fired.
    I want to add a check so that if this method returns false, then we should not place the order an appropriate message should be sent to the customer to update the delivery date .
    How can i go about doing that? Is it possible?

    Thanks,
    Neet

    • Hi Neet. You can set Mage::register(‘checked’,true); if it’s good in checkDeliveryCapacity and retrieve it on saveDeliveryCapacity with $isChked = Mage::registry(‘checked’); Then based on $isChked value you can go ahead with what you want.

  • Hello, Thx your article is very nice

  • Hi kalpesh,

    i need one small help, how to add mrp price and special price column in magento pdf inovice

    pls help me!

    Regards,
    VINOTH

  • Thanks it works

    But in saveProductMrpInOrder

    $price is empty.

Leave a comment

 

Welcome to my Blog

Kalpesh MehtaHelping Magento developers in their day-to-day development problems since 2011. Most of the problems and solutions here are my own experiences while working on different projects. Enjoy the blog and don't forget to throw comments and likes/+1's/tweets on posts you like. Thanks for visiting!

Certifications