Dec 31, 2011
kalpesh

Magento: Joining/group by – order, invoice, shipment tables

Ever tried to join multiple tables in Magento? Joining and grouping tables is not that easy in Magento ORM than in simple MySql way we did for years.

Here is a piece of code where I was required to grab all the data from orders, invoices, shipments to generate reports for warehouse people to make their life easier.

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
$collection = Mage::getResourceModel('sales/order_shipment_item_collection');
$collection->addFieldToSelect('section_id');
$collection->addFieldToSelect('shelf_id');
$collection->addFieldToSelect('rack_id');
$collection->addFieldToSelect('box_id');
//$collection->addFieldToSelect('GROUP_CONCAT(DISTINCT main_table.product_id SEPARATOR ",")','product_ids');</p>
$collection->getSelect()->joinLeft('sales_flat_shipment_grid', 'main_table.parent_id = sales_flat_shipment_grid.entity_id', array('increment_id as shipment_increment_id', 'created_at as shipment_created_at', 'order_increment_id','total_qty'));
$collection->getSelect()->joinLeft('sales_flat_shipment_track', 'sales_flat_shipment_grid.entity_id = sales_flat_shipment_track.parent_id', array('number as track_id', 'carrier_code as carrier_name'));
$collection->getSelect()->joinLeft('sales_flat_order', 'sales_flat_shipment_track.order_id = sales_flat_order.entity_id', array('created_at as order_created_at','grand_total as ord_grand_total'));
$collection->getSelect()->joinLeft('sales_flat_order_item', 'main_table.order_item_id = sales_flat_order_item.item_id', array('product_type'));
$collection->getSelect()->joinLeft('sales_flat_order_payment', 'sales_flat_shipment_track.order_id = sales_flat_order_payment.parent_id', array('method as payment_method'));
$collection->getSelect()->joinLeft('sales_flat_order_address', 'sales_flat_shipment_track.order_id = sales_flat_order_address.parent_id', array('firstname','lastname','street','city','postcode','region','telephone'));
$collection->getSelect()->joinLeft('sales_flat_invoice', 'sales_flat_shipment_track.order_id = sales_flat_invoice.order_id', array('grand_total'));
//$collection->addAttributeToFilter('main_table.price', array('gt' => 0));
if($pickup_ids!='' &#038;&#038; is_array($pickup_ids))
    $collection->addAttributeToFilter('sales_flat_shipment_grid.entity_id', array('in' => $pickup_ids));
$collection->getSelect()->columns(
    array('product_ids' => new Zend_Db_Expr(
        "IFNULL(GROUP_CONCAT(DISTINCT main_table.product_id SEPARATOR ';'), '')"
)));
$collection->getSelect()->columns(
    array('weight' => new Zend_Db_Expr(
        "IFNULL(SUM(main_table.weight)/2, '')"
)));
$collection->getSelect()->group('shipment_increment_id');
//$collection->getSelect()->group('track_id');
//Mage::log($collection->getSelect()->__toString());
$this->setCollection($collection);
//$this->getCollection()->getSelect()->limit();

Although the query is so expensive, I have not yet tried to optimize it due to lack of time.

Dec 31, 2011
kalpesh

Magento: Getting more than one records from core_config_data table

To select any particular record from core_config_data table in magento is quite simple.

1
Mage::getStoreConfig('sales_email/order/template');

But what if you want all the data pertaining to sales order email or for any other thing stored in magento’s config table? You may need other data like “enabled”, “identity”, “guest_template”, “copy_to” which can accomplish otherwise in 5 requests.
So here is how you should write the code to get all the data for some particular expression as per your need.

1
2
$qrySalesOrder = Mage::getModel('core/config_data')->getCollection()->addFieldToFilter('path',array('like'=>'sales_email/order/%'));
//Mage::log($qrySalesOrder);
Dec 31, 2011
kalpesh

Magento Admin – Forcing Invoice and Ship button together

Ever wondered what if you want to do invoice and shipment with just one click in your website admin? Yes, Magento allows you to integrate both these in one step.

Edit your module’s config.xml and Observer.php for this to happen.

config.xml snippet:

1
2
3
4
5
6
7
8
9
10
11
<events>
            <sales_order_place_after>
                <observers>
                    <namespace_module>
                        <type>singleton</type>
                        <class>Namespace_Module_Model_Observer</class>
                        <method>doForceInvoiceWithShipment</method>
                    </namespace_module>
                </observers>
            </sales_order_place_after>
</events>

Every time a order is placed, frontend as well as backend, your observer method will be called which will force invoice and shipment to show in one button in Manage Orders for particular order at backend.

Observer.php snippet:

1
2
3
4
5
public function doForceInvoiceWithShipment($observer) {
        $order = $observer->getOrder();
        $orderId = $order->getIncrementId();
        Mage::getModel('sales/order')->loadByIncrementId($orderId)->setForcedDoShipmentWithInvoice(true)->save();
}

Now you can place a order and check at backend under Sales->Orders clicking on latest order to see “Invoice & Ship” button integrated rather than “Invoice” and “Ship” button separated.

Oct 10, 2011
kalpesh

Magento: Get checkout cart total details | Subtotal/Grandtotal/Discount/Tax

In Magento, if you want to get shopping cart totals details anywhere across the site, you can do so by following piece of code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
$totalItemsInCart = Mage::helper('checkout/cart')->getItemsCount(); //total items in cart
$totals = Mage::getSingleton('checkout/session')->getQuote()->getTotals(); //Total object
$subtotal = round($totals["subtotal"]->getValue()); //Subtotal value
$grandtotal = round($totals["grand_total"]->getValue()); //Grandtotal value
if(isset($totals['discount']) &#038;&#038; $totals['discount']->getValue()) {
    $discount = round($totals['discount']->getValue()); //Discount value if applied
} else {
    $discount = '';
}
if(isset($totals['tax']) &#038;&#038; $totals['tax']->getValue()) {
    $tax = round($totals['tax']->getValue()); //Tax value if present
} else {
    $tax = '';
}
Sep 18, 2011
kalpesh

Upgrading Android 2.2 (Froyo) to Gingerbread (2.3.4) – Samsung Galaxy Fit S5670

If you have purchased Samsung galaxy fit S5670 mobile phone, you must have noticed that it has very short battery life. Also you may have experienced sudden switch off of your mobile and weird behavior. This is due to some bugs in Android OS version 2.2 (froyo).

To fix this, you will have to upgrade your android OS from 2.2 to 2.3.4 version which will increase your battery life to some extent. Before proceeding backup all your important data (contacts, messages, etc..) so that if something goes wrong in between, you are on your safer side. So here are the steps to follow:

1. Download ODIN Multi-downloader v4.38 – 444 KB
2. Download Gingerbread Firmware – 113 MB
3. Turn off you samsung mobile phone Continue reading »

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