Unserialize Data from Sales_Flat_Order_Payment to Display in Custom Grid

adminadminhtmlgridpaymentsales

I am looking for a way to display payment information in a Grid.

I have noticed that there is a column additional_information inside sales_flat_order_payment table that contains most of the data I need but is serialized.

a:10:{s:19:"paypal_ec_create_ba";N;s:8:"CC_BRAND";...

The problem is I need to show some of this data in the grid in a filterable way!

Is there a way to unserialize these values?

I would appreciate any suggestions on how to get this data into an eligible format.

I've added a Renderer.php file to my module

class Custom_Module_Block_Adminhtml_Order_Grid_Renderer_Payment extends

Mage_Adminhtml_Block_Widget_Grid_Column_Renderer_Abstract{

    public function render(Varien_Object $row)
    {
        $value = unserialize($row->getData('additional_information'));

        return $value;
    }
}

And here is my Grid

<?php

class Custom_Module_Block_Adminhtml_Order_Grid extends Mage_Adminhtml_Block_Widget_Grid
{

    public function __construct()
    {
        parent::__construct();
        $this->setId('sales_order_grid');
        $this->setUseAjax(true);
        $this->setDefaultSort('created_at');
        $this->setDefaultDir('DESC');
        $this->setSaveParametersInSession(true);
    }

    protected function _getCollectionClass()
    {
        return 'sales/order_grid_collection';
    }

    protected function _prepareCollection()
    {
        $collection = Mage::getResourceModel($this->_getCollectionClass());

        $collection->getSelect()->joinLeft(array('sales_flat_order_payment'),
            'parent_id=main_table.entity_id',array('additional_information'));

        $collection->getSelect()->joinLeft(array('spt'=>'sales_payment_transaction'),
            'spt.transaction_id=main_table.entity_id',array('spt.payment_id', 'spt.txn_id'));

        $collection->getSelect()->joinLeft(array('sfo'=>'sales_flat_order'),
            'sfo.entity_id=main_table.entity_id',array('sfo.customer_email',
            'sfo.weight','sfo.discount_description','sfo.increment_id','sfo.store_id',
            'sfo.created_at','sfo.status','sfo.base_grand_total','sfo.grand_total'));

        $collection->getSelect()->joinLeft(array('ops' => 'ops_alias'),
            'ops.customer_id=main_table.entity_id', array('alias','brand', 'payment_method'));

        $this->setCollection($collection);
        return parent::_prepareCollection();
    }

    protected function _paymentFilter($collection, $column)
    {
        if (!$value = $column->getFilter()->getValue()) {
            return $this;
        }
        $value = ($column->getFilter()->getValue());
        $this->getCollection()->getSelect()->where(
            "sales_flat_order_payment.additional_information like ?"
            , "%$value%");
        return $this;
    }

    protected function _prepareColumns()
    {
        $this->addColumn('order_id', array(
            'header' => Mage::helper('sales')->__('Order Id'),
            'align' =>'left',
            'index' => 'increment_id',
            'filter_index'=>'sfo.increment_id',
        ));

        $this->addColumn("created_at", array(
            "header" => Mage::helper("sales")->__("Order Date"),
            "index" => "created_at",
            'filter_index'=>'sfo.created_at',
            "type" => "date",
        ));

        $this->addColumn("billing_name", array(
            "header" => Mage::helper("sales")->__("Billing Name"),
            "index" => "billing_name",
        ));

        $this->addColumn('customer_email', array(
            'header' => Mage::helper('sales')->__('Customer Email'),
            'index' => 'customer_email',
            'filter_index' => 'sfo.customer_email',
            'width' => '50px',
        ));

        /* Additional Payment Information*/

        $this->addColumn('additional_information', array(
            'header'    => Mage::helper('sales')->__('Payment Information'),
            'filter_condition_callback' => array($this, '_paymentFilter'),
            'renderer' => 'Custom_Module_Block_Adminhtml_Order_Grid_Renderer_Payment'
        ));

        $this->addColumn('payment_id', array(
            'header'        => Mage::helper('sales')->__('Payment ID'),
            'align'         => 'right',
            'index'         => 'payment_id',
            'filter_index' => 'spt.payment_id',
        ));

        $this->addColumn('grand_total', array(
            'header' => Mage::helper('sales')->__('Order Total'),
            'index' => 'grand_total',
            'filter_index'=>'sfo.grand_total',
            'type'  => 'currency',
            'currency' => 'order_currency_code',
        ));

        $this->addColumn('status', array(
            'header' => Mage::helper('sales')->__('Status'),
            'index' => 'status',
            'filter_index'=>'sfo.status',
            'type'  => 'options',
            'width' => '70px',
            'options' => Mage::getSingleton('sales/order_config')->getStatuses(),
        ));

        if (Mage::getSingleton('admin/session')->isAllowed('sales/order/actions/view')) {
            $this->addColumn('action',
                array(
                    'header'    => Mage::helper('sales')->__('Action'),
                    'width'     => '50px',
                    'type'      => 'action',
                    'getter'     => 'getId',
                    'actions'   => array(
                        array(
                            'caption' => Mage::helper('sales')->__('View'),
                            'url'     => array('base'=>'adminhtml/sales_order/view'),
                            'field'   => 'order_id'
                        )
                    ),
                    'filter'    => false,
                    'sortable'  => false,
                    'index'     => 'stores',
                    'is_system' => true,
                ));
        }

        $this->addExportType('*/*/exportCsv', Mage::helper('sales')->__('CSV'));
        $this->addExportType('*/*/exportExcel', Mage::helper('sales')->__('Excel'));

        return parent::_prepareColumns();
    }



    public function getRowUrl($row)
    {
        //return $this->getUrl("*/*/edit", array("id" => $row->getId()));
    }

    public function getGridUrl()
    {
        return $this->getUrl('*/*/grid', array('_current' => true));
    }

}

Using the renderer I have been unable to unserialize the data to show in my Grid.php although the data is not being displayed correctly and needs to be separated into it own columns:

class Custom_Module_Block_Adminhtml_Order_Grid_Renderer_Payment extends

Mage_Adminhtml_Block_Widget_Grid_Column_Renderer_Abstract{

    public function render(Varien_Object $row)
    {
        $value = ($row->getData('additional_information'));

        print_r(unserialize($value));
    }
}

Data returned in Grid.php

 Array ( [paypal_express_checkout_shipping_method] => [paypal_payer_id] =>

Best Answer

This issue you can solve by customizing the renderer file. You can fetch the format from database by custom query as well.

For eg. Prodid.php

<?php
class Mage_Adminhtml_Block_Sales_Order_Renderer_Prodid extendsMage_Adminhtml_Block_Widget_Grid_Column_Renderer_Abstract
{

    public function render(Varien_Object $row){
        $url = $this->getUrl('*/*/edit', array(
            'store'=>$this->getRequest()->getParam('store'),
            'id'=>$row->getId())
        );
        $exploding = explode('sales_order/edit/id/',$url);
        //print_r($exploding);
        $id = explode('/',$exploding[1]);
        //print_r($id);
        $myId = $id[0];
        //echo $myId;
        $write = Mage::getSingleton('core/resource')->getConnection('core_write');

        // now $write is an instance of Zend_Db_Adapter_Abstract
        $readresult=$write->query("SELECT product_id FROM `sales_flat_order_item` WHERE `order_id` =$myId "); 
        while ($row = $readresult->fetch() ) {
            $productid = $row['product_id'];
             $myprodid .= $productid.',';

        }
        $myprodid2 = substr($myprodid,0,-1);
            return $myprodid2;
    }

}
?>

This will give you the desired result in my case it was product id

Then calling it in the grid file would be like

$this->addColumn('ids', array(
        'header'    => Mage::helper('sales')->__('Product ID'),
        'width'     => '100px',
        'index'     => 'ids',
        'type'      => 'text',
        'filter'    => false,
        'sortable'  => false,
        'renderer'  => 'Mage_Adminhtml_Block_Sales_Order_Renderer_Prodid'
    ));

The filter=false and sortable false will remove the a href from the headers and will only show you the data.

If you want to make them sortable, then you will need to pass the corresponding unique index id.

Please share if anything is unclear here.

Related Solutions

Display Data in Custom Column in Magento 1.9 Admin – How To

I've finally did it that way:

app/code/local/Mypackage/Customgrid/etc/Config.xml:

<?xml version="1.0"?>
<config>
    <modules>
        <Mypackage_Customgrid>
            <version>0.4.0</version>
        </Mypackage_Customgrid>
    </modules>

    <global>
        <blocks>
            <adminhtml>
                <rewrite>
                    <catalog_product_grid>Mypackage_Customgrid_Block_Adminhtml_Catalog_Product_Grid</catalog_product_grid>
                </rewrite>
            </adminhtml>
        </blocks>
    </global>
</config>

app/code/local/Mypackage/Customgrid/Block/Adminhtml/Catalog/Product/Grid.php:

class Mypackage_Customgrid_Block_Adminhtml_Catalog_Product_Grid extends Mage_Adminhtml_Block_Catalog_Product_Grid
{
    protected function _prepareCollection()
    {
        parent::_prepareCollection();
        $collection = $this->getCollection();

        $collection->addAttributeToSelect('udropship_vendor');

        $this->setCollection($collection);
        $this->getCollection()->addWebsiteNamesToResult();

        return $this;
    }

    protected function _prepareColumns()
    {
        $attributeId = Mage::getResourceModel('eav/entity_attribute')->getIdByCode('catalog_product','udropship_vendor');
        $attribute = Mage::getModel('catalog/resource_eav_attribute')->load($attributeId);
        $attributeData = $attribute->getData();
        $attributeOptions = $attribute->getSource()->getAllOptions();

        $attributeOptions2 = array();
        foreach ($attributeOptions as $value) {
            if(!empty($value['value'])) {
                $attributeOptions2[$value['value']] = $value['label'];
            }
        }

        $this->addColumnAfter('udropship_vendor',
            array(
                'header'=> Mage::helper('catalog')->__('Vendor Name'),
                'width' => '150px',
                'index' => 'udropship_vendor',
                'type'  => 'options',
                'options' => $attributeOptions2,
        ), 'price');

        return parent::_prepareColumns();
    }
}

Magento Admin – Running AJAX Script from Admin Area

Looks like your admin route declaration is incorrect. Try this:

<admin>
    <routers>
        <adminhtml>
            <args>
                <modules>
                    <TwilitGrotto_AutoCustomerLookup after="Mage_Adminhtml">TwilitGrotto_AutoCustomerLookup</TwilitGrotto_AutoCustomerLookup>
                </modules>
            </args>
        </adminhtml>
    </routers>
</admin>

Here is a good article on the topic: Magento Admin Hello World Revisited

EDIT:

I'm posting a working solution below:

File: TwilitGrotto\AutoCustomerLookup\etc\adminhtml.xml

<?xml version="1.0"?>
<config>
    <acl>
        <resources>
            <admin>
                <children>
                    <sales>
                        <children>
                            <twilitgrotto_autocustomerlookup translate="title" module="twilitgrotto_autocustomerlookup">
                                <title>Auto Customer Lookup</title>
                            </twilitgrotto_autocustomerlookup>
                        </children>
                    </sales>
                </children>
            </admin>
        </resources>
    </acl>
</config>

File: TwilitGrotto\AutoCustomerLookup\etc\config.xml

<?xml version="1.0"?>
<config>
    <modules>
        <TwilitGrotto_AutoCustomerLookup>
            <version>1.0.0.0</version>
        </TwilitGrotto_AutoCustomerLookup>
    </modules>
    <global>
        <helpers>
            <twilitgrotto_autocustomerlookup>
                <class>TwilitGrotto_AutoCustomerLookup_Helper</class>
            </twilitgrotto_autocustomerlookup>
        </helpers>
    </global>
    <adminhtml>
        <layout>
            <updates>
                <twilitgrotto>
                    <file>twilitgrotto_autocustomerlookup_layout.xml</file>
                </twilitgrotto>
            </updates>
        </layout>
    </adminhtml>
    <admin>
        <routers>
            <adminhtml>
                <args>
                    <modules>
                        <TwilitGrotto_AutoCustomerLookup after="Mage_Adminhtml">TwilitGrotto_AutoCustomerLookup_Adminhtml</TwilitGrotto_AutoCustomerLookup>
                    </modules>
                </args>
            </adminhtml>
        </routers>
    </admin>
</config>

File: TwilitGrotto\AutoCustomerLookup\controllers\Adminhtml\Autocustomerlookup\AddressController.php

<?php

class TwilitGrotto_AutoCustomerLookup_Adminhtml_Autocustomerlookup_AddressController
    extends Mage_Adminhtml_Controller_Action
{
    public function checkEmailExistsAction()
    {
        $emailAddress = $this->getRequest()->getParam('emailAddress');
        $result = Mage::helper('twilitgrotto_autocustomerlookup')->checkEmailExists($emailAddress);
        $this->getResponse()->setBody($result);
    }

    protected function _isAllowed()
    {
        return Mage::getSingleton('admin/session')->isAllowed('sales/twilitgrotto_autocustomerlookup');
    }
}

Controller URL: Mage::getUrl('adminhtml/autocustomerlookup_address/checkEmailExists');

Best Answer

Related Solutions

Display Data in Custom Column in Magento 1.9 Admin – How To

Magento Admin – Running AJAX Script from Admin Area

Related Topic