Magento add a column to sales_flat_quote table and add data

Question

I am coming from a previous enviornment where doing things like modifying queries and adding columns was just a matter of writing the sql and executing it. However, now that I'm working in Magento I want to do things "the Magento way".

Scenario: we use paypal express, and before the controller redirects to paypal, I would really like to add a field (if not there already) in sales_flat_quote, called paypal_status - and set the value = 1 (we'll call it, sent to paypal).

On return I want to update that to either 2 or 3 (returned and pending transaction, or returned and captured transaction).

So there are two things I need to know how to do:

1. have something like $db->addColumn('paypal_status') where it will only add if not exists, and

2. write UPDATE sales_flat_quote SET paypal_status = 1 WHERE entity_id = {whatever}

This will be inside the ...Paypal_Express class.

Answer 1

Open database and fire this SQL: Alter table sales_flat_quote Add paypal_status tinyint(1) NOT NULL DEFAULT 1;

Alternatively, you can write following in your SQL file (located at CompanyName\MyModuleName\sql\companyname_modulename_setup) of your custom module. This file will get executed only one time , that is the first time when the module is installed. At that time your custom column will not be there in database so it will create one.

$installer = $this;
$installer->startSetup();
$installer->run("ALTER TABLE `{$installer->getTable('sales/quote')}` ADD `paypal_status` tinyint(1) NOT NULL DEFAULT 1 COMMENT 'My Custom Paypal Status';");
$installer->endSetup();

Clear all cahces.

To save data :

$myValue = 2;
Mage::getSingleton("checkout/cart")->getQuote()->setPaypalStatus($myValue)->save();

Mage::getSingleton("checkout/cart")->getQuote() will give you current quote.

Answer 2

In your sql file at CompanyName\MyModuleName\sql\companyname_modulename_setup copy the following code in order to create the column.

$installer = $this;
$installer->startSetup();
$installer->getConnection()
->addColumn($installer->getTable('sales/quote'),
    'paypal_status',
    array(
        'type' => Varien_Db_Ddl_Table::TYPE_INTEGER,
        'nullable'  => true,
        'comment' => 'Paypal Status',
    )
);
$installer->endSetup();

Logout and login, and flush magento cache in order to add the column to the table.

The Express Checkout controller is in app/code/core/Mage/Paypal/Controller/Express/Abstract.php. If you want to add a field before the controller redirects to paypal you can modify the _initCheckout() method like this:

protected function _initCheckout()
$quote = $this->_getQuote();
    if (!$quote->hasItems() || $quote->getHasError()) {
        $this->getResponse()->setHeader('HTTP/1.1','403 Forbidden');
        Mage::throwException(Mage::helper('paypal')->__('Unable to initialize Express Checkout.'));
    }
 $quote->setPaymentStatus(1); // Here is your change
 $this->_checkout = Mage::getSingleton($this->_checkoutType, array(
        'config' => $this->_config,
        'quote'  => $quote,
    ));
 return $this->_checkout;
}