Welcome to Brnfullstack

Step1->include google apic
<script type="text/javascript" src="https://www.google.com/jsapi"></script>

<script type="text/javascript">

    google.load("visualization", "1", {packages:["corechart"]});
    google.setOnLoadCallback(drawChart2);
    function drawChart2() {
      var data = new google.visualization.DataTable();
      data.addColumn('string', 'Date');
      data.addColumn('number', 'Sales Amount');
      data.addColumn('number', 'Quantity Sold');
      //["Sale date", sale amount,sale quantity]
      data.addRows([["2013-06-05", 14.95, 1],["2013-06-07", 29.9, 2],["2013-06-11", 14.95, 1],["2013-06-13", 30, 3],["2013-06-17", 19.9, 2],["2013-06-18", 14.95, 1],["2013-06-19", 0.2, 2],["2013-06-20", 88.85, 9],["2013-06-21", 117.1, 16],["2013-06-22", 71.8, 7],["2013-06-24", 164.45, 11],["2013-06-25", 492.95, 42],["2013-06-26", 271.85, 30],["2013-06-27", 95.95, 13],["2013-06-28", 52.9, 5],["2013-06-29", 14.95, 1]]);

      var chart = new google.visualization.AreaChart(document.getElementById('chart_div_2'));
      chart.draw(data, {width: 650, height: 240, title: 'Sale Amount and Quantity Sold by Date', colors:['#3366CC','#9AA2B4','#FFE1C9'],
                        hAxis: {title: 'Date', titleTextStyle: {color: 'black'}}
                       });
    }
    </script>
       <div id="chart_div_2"></div>
    <script type="text/javascript">
      // Load the Visualization API and the piechart package.
      google.load('visualization', '1.0', {'packages':['corechart']});

      // Set a callback to run when the Google Visualization API is loaded.
      google.setOnLoadCallback(drawChart);

      // Callback that creates and populates a data table,
      // instantiates the pie chart, passes in the data and
      // draws it.
      function drawChart() {

      // Create the data table.
      var data = new google.visualization.DataTable();
      data.addColumn('string', 'Products');
      data.addColumn('number', 'Numbers');
      data.addRows([
        [""The Map" Webinar Series", 3],["The “One Minute Manifestor” Audio Technique ", 1],[""Life On Planet Earth: A User's Manual" (E-Book)", 125],["The Map: To Our Responsive Universe, Where Dreams Really Do Come True!", 3],[""Conscious Creation" Inspiration Cards", 10],["Live a Life You Love Vneck TShirt", 48],      ]);

      // Set chart options
      var options = {'title':'Product Sale Quantity Stats',
                     'width':450,
                     'height':300};

      // Instantiate and draw our chart, passing in some options.
      var chart = new google.visualization.PieChart(document.getElementById('chart_div_1'));
      chart.draw(data, options);
    }
    </script>
       <div id="chart_div_1"></div>

Magento’s default back-end order grid doesn’t cut it. I recently had a customer that wanted to be able to sort orders by sku, shipping address, shipping state, and customer e-mail address on their magento site. Why did they want to do this? To quickly generate reports I guess, who knows? By default you can’t sort by these items. You have to add the fields to magento which can be some what tricky.

Anyway if you want to change the order grid from this:

To this on your magento web site:

Just follow these few easy steps.

Step one create a new directory on your server at app/code/local/Mage/Adminhtml/Block/Sales/Order

Here we will be creating a file called Grid.php.

First navigate to app/code/core/Mage/Adminhtml/Block/Sales/Order and copy\paste Grid.php into the directory you just created.

Now open up app/code/local/Mage/Adminhtml/Block/Sales/Order/Grid.php

Look for the following:

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

Now change it to :

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

Next find:

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

and make it:

protected function _prepareCollection()
{
$collection = Mage::getResourceModel($this->_getCollectionClass())
->join(
'sales/order_item',
'`sales/order_item`.order_id=`main_table`.entity_id',
array(
'skus' => new Zend_Db_Expr('group_concat(`sales/order_item`.sku SEPARATOR ",")'),
)
);

$collection->getSelect()->group('main_table.entity_id');

$collection->getSelect()->joinLeft(array('sfog' => 'sales_flat_order_grid'),
'main_table.entity_id = sfog.entity_id',array('sfog.shipping_name','sfog.billing_name'));

$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('sfoa'=>'sales_flat_order_address'),
'main_table.entity_id = sfoa.parent_id AND sfoa.address_type="shipping"',array('sfoa.street',
'sfoa.city','sfoa.region','sfoa.postcode','sfoa.telephone'));

$this->setCollection($collection);

return parent::_prepareCollection();
}

Finally find:

protected function _prepareColumns()
{

$this->addColumn('real_order_id', array(
'header'=> Mage::helper('sales')->__('Order #'),
'width' => '80px',
'type' => 'text',
'index' => 'sfo.increment_id',
));

if (!Mage::app()->isSingleStoreMode()) {
$this->addColumn('store_id', array(
'header' => Mage::helper('sales')->__('Purchased From (Store)'),
'index' => 'store_id',
'type' => 'store',
'store_view'=> true,
'display_deleted' => true,
));
}

$this->addColumn('created_at', array(
'header' => Mage::helper('sales')->__('Purchased On'),
'index' => 'created_at',
'type' => 'datetime',
'width' => '100px',
));

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

$this->addColumn('shipping_name', array(
'header' => Mage::helper('sales')->__('Ship to Name'),
'index' => 'shipping_name',
));

$this->addColumn('base_grand_total', array(
'header' => Mage::helper('sales')->__('G.T. (Base)'),
'index' => 'base_grand_total',
'type' => 'currency',
'currency' => 'base_currency_code',
));

$this->addColumn('grand_total', array(
'header' => Mage::helper('sales')->__('G.T. (Purchased)'),
'index' => 'grand_total',
'type' => 'currency',
'currency' => 'order_currency_code',
));

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

}

Change it to:

protected function _prepareColumns()
{

$this->addColumn('real_order_id', array(
'header'=> Mage::helper('sales')->__('Order #'),
'width' => '80px',
'type' => 'text',
'index' => 'increment_id',
'filter_index' => 'sfo.increment_id',
));

if (!Mage::app()->isSingleStoreMode()) {
$this->addColumn('store_id', array(
'header' => Mage::helper('sales')->__('Purchased From (Store)'),
'index' => 'store_id',
'type' => 'store',
'store_view'=> true,
'display_deleted' => true,
));
}

$this->addColumn('created_at', array(
'header' => Mage::helper('sales')->__('Purchased On'),
'index' => 'created_at',
'filter_index' => 'sfo.created_at',
'type' => 'datetime',
'width' => '50px',
));

$this->addColumn('skus', array(
'header' => Mage::helper('Sales')->__('Skus'),
'width' => '350px',
'index' => 'skus',
'filter_index' => 'sku',
'type' => 'text',

));
$this->addColumn('shipping_street', array(
'header' => Mage::helper('sales')->__('Shipping Street'),
'index' => 'street',
'type' => 'text',
'filter_index' => 'sfoa.street',
'width' => '100px',
));
$this->addColumn('shipping_city', array(
'header' => Mage::helper('sales')->__('Shipping City'),
'index' => 'city',
'type' => 'text',
'filter_index' => 'sfoa.city',
'width' => '50px',
));
$this->addColumn('shipping_region', array(
'header' => Mage::helper('sales')->__('Shipping Region'),
'index' => 'region',
'type' => 'text',
'filter_index' => 'sfoa.region',
'width' => '50px',
));
$this->addColumn('customer_email', array(
'header' => Mage::helper('sales')->__('Customer Email'),
'index' => 'customer_email',
'type' => 'text',
'filter_index' => 'sfo.customer_email',
));

$this->addColumn('grand_total', array(
'header' => Mage::helper('sales')->__('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(),
));

That is it, if you wanted to add lets say product weight you would use the following:

$this->addColumn('order_weight', array(
'header' => Mage::helper('sales')->__('Weight'),
'index' => 'order_weight',
'type' => 'text',
'filter_index' => 'sfo.weight',
));

}

Let’s say for some reason when you are doing searches in your brand new boxes and you get an error like

SQLSTATE[23000]: Integrity constraint violation: 1052 Column'status' in where clause is ambiguous

This means you did not add a filter type properly go back a line like this:

'filter_index' => 'sfo.itemname',

This should fix the error and fix your magento web site’s order grid! Now you should be able to sort by sku, address, name, e-mail address. Shoot you can easily add the telephone number to this order grid if you want!

In case you are wondering I referenced these posted. It took me a while to get the SKU’s to play nicely with all of the other information.

http://stackoverflow.com/questions/9027741/magento-sqlstate42s22-column-not-found-1054-unknown-column-billing-name

http://stackoverflow.com/questions/8764543/magento-sales-order-grid-shows-incorrect-number-of-records-when-added-names-and

Welcome to Brnfullstack

Pages

Friday, November 15, 2013

simple google chart api easily in integrate to application and example solve

Magento – Customize Backend Order Grid to have SKU, E-mail Address, and Phone Number