Backorders Report Magento Extension

30th JAN 2012 | Posted by in Administration, Extensions, Magento, Orders
Featured Image

Recently, i had a request to display a list of all backorders. Backorders have to be grouped by Manufacturers, also should be option to filter backorders by manufacturers.

In this article, I will present you the extension, which solved the above requirement.

Backorder report is placed under Reports menu.

Backorder report contains information about order such as: Order id, Purchased on, Customer, Order total. Backorder report also contains information about the order items such as: Name, Quantity on stock, Quantity in order, Undelivered quantity etc.

public function getBackorderItems($orderId)
{
    $backOrderItems = Mage::getModel('sales/order_item')->getCollection()
        ->addFieldToFilter('order_id', $orderId);
    $backOrderItems->getSelect()->join(
        array('sales_product_stock' => 'cataloginventory_stock_item'),
        'main_table.product_id = sales_product_stock.product_id',
        'qty'
        );
    $conditionsVarchar = array(
        'main_table.product_id = sales_product_manufacturer_varchar.entity_id',
        'sales_product_manufacturer_varchar.attribute_id = 102'
    );
    $backOrderItems->getSelect()->joinLeft(
        array('sales_product_manufacturer_varchar' => 'catalog_product_entity_varchar'),
        implode(' AND ', $conditionsVarchar),
        array('value_varchar' => 'value')
        );
    $conditionsInt = array(
        'main_table.product_id = sales_product_manufacturer_int.entity_id',
        'sales_product_manufacturer_int.attribute_id = 102'
    );
    $backOrderItems->getSelect()->joinLeft(
        array('sales_product_manufacturer_int' => 'catalog_product_entity_int'),
        implode(' AND ', $conditionsInt),
        array('value_int' => 'value')
        );
    return $backOrderItems;
}

For example:
The customer has made an order that contains:
Product name: “a”; Manufacturer: “x”; Qty: 10 (Qty on stock 20);
Product name: “b”; Manufacturer: “y”; Qty: 10 (Qty on stock 5);
Product name: “c”; Manufacturer: “z”; Qty: 10 (Qty on stock 15);

Backorder will be displayed on three place. In the group of manufacturer “x”, in the group of manufacturer “y” and in the group of manufacturer “z”.

backorder_report

Note: As you can assume, this extension will only work if your store allows backorders. To allow backorders go to: System > Configuration > Catalog > Inventory > Product Stock Options, and set Backorders => “Allow Qty bellow 0” or “Allow Qty bellow 0 and notify Customer”.

Extension is tested on Magento 1.6.2.0 CE. Download extension.

Cheers.

18
Top

Enjoyed this post?

Subscribe to our RSS Feed, Follow us on Twitter and spread it to your friends!

Author

Dejan Radic

Magento Certified Developer

At Inchoo, Dejan is a Magento back-end developer.

Other posts from this author

Discussion 18 Comments

Add Comment
  1. Angelica
    02-02-2012 at 18:57 | #

    I installed and configured the backorder, I try to buy by the “google”(the status of the purchase as “pending”)and nothing appears in the report, what am I doing wrong?

    images below to check:

    http://www.softwarestore.com.br/pendente.jpg
    http://www.softwarestore.com.br/relatorio.jpg

    thanks for all..

  2. Angelica
    07-02-2012 at 13:15 | #

    Can anyone help me? please *-*

  3. Dejan Radic
    07-02-2012 at 21:46 | #

    @Angelica try this:
    1. Add new product ex. “abc” – select Manufacturer, set Qty to 5, set stock Availability to “In stock”.
    2. Allow backorders: System > Configuration > Catalog > Inventory > Product Stock Options, and set Backorders => “Allow Qty bellow 0” or “Allow Qty bellow 0 and notify Customer”
    3. Make an order: add to cart product “abc”, qty = 10

    For me that works.

  4. Angelica
    08-02-2012 at 13:08 | #

    Dejan Radic, thank you … did not know how to operate the module

  5. Marcus
    14-03-2012 at 15:27 | #

    could it be that magento changed the manufacturer attribute to brand attribute? How can I change manufacturer in your code to my brand-attribute? I only have brands and not manufacturers :-(

  6. Zard
    28-03-2012 at 14:08 | #

    Just for information :
    Tested on Magento CE 1.5.1 but not working.

  7. bdg
    29-04-2012 at 10:49 | #

    Tested on Magento CE 1.4.1.1 –> Not working. Thanks anyway.

  8. Abed
    02-07-2012 at 22:24 | #

    Hi,

    I am using Magneto Ver. 1.6.1. I installed the extension but I got this message:

    “Your search did not find any backorder. Please try again with other manufacturer filter!”

    Please help on solving this issue.

    Thanks,

  9. Angelica
    03-07-2012 at 13:04 | #

    Marcus, registers an attribute with code “manufacturer”, I think magento will get the name of the code. Good luck!

  10. Angelica
    03-07-2012 at 13:09 | #

    Abed,

    Have you registered a product with missing inventory and placed an order? This report shows the products that were ordered and that is with stock equal to or below zero. I hope you understand my english because I am Brazilian. =)

  11. Abed
    03-07-2012 at 18:36 | #

    Hi Angelica,

    Thanks for the reply. I am wondering if you know any Magneto extension or software that group product by manufacture on the order or on the invoice.

    Example,

    Manufacture X:
    Product A
    Product B
    Product C

    Manufacture Y:
    Product D
    Product E
    Product F

  12. Angelica
    04-07-2012 at 13:33 | #

    Hello Abed, view my report: http://softwarestore.com.br/report.jpg

    In case you have the product sold by the manufacturer X.

    The report will show orders of the products sold out from manufacturer X, so you have control of that order with the manufacturer and can complete the purchase with the client.

    Take this test that Dejan Radic cited above:

    1. Add new product ex. “Abc” – select Manufacturer, set Qty to 5, set to stock Availability “In stock”.
    2. Allow backorders: System> Configuration> Catalog> Inventory> Product Stock Options, and Set backorders => “Allow Qty bellow 0″ or “Allow Qty bellow 0 and notify Customer”
    3. Make an order: add to cart product “abc”, qty = 10

    =)

  13. Joseph
    14-07-2012 at 6:13 | #

    I have try to add this extension to magento ver 1.12
    and i have got this message “Your search did not find any backorder. Please try again with other manufacturer filter!”

    I did allow back order and i have 3 product with different manufacturer that are on back order qty:-10,-5,-8

    any idea? i would love to have this feature working in my site.

    thank you
    Joseph

  14. bdg
    15-07-2012 at 12:39 | #

    Same issue as Joseph.
    I’d really like to use this great extension on my Magento 1.4.1.1 platform…
    There might be something different in those old versions (database ?) because your function doesn’t retrieve anything :(
    Could someone help us ? Has anyone modified this script to make it work with older Magento versions ?
    Thank you !!!

  15. Rod
    16-09-2012 at 7:33 | #

    Hi,

    I didn’t test the extension, but the “on stock” information is actually not the physical stock in the shop right? It has already been deduced by the quantity ordered?

    For instance:
    Product name: “b”; Manufacturer: “y”; Qty: 10 (Qty on stock 5);
    You actually have 10+5=15 on stock in the shop right?
    So the order can be shipped.
    Because what Magento calls stock is actually “the available stock for selling”

    Thanks
    rod

  16. Brian Conrey
    31-01-2013 at 18:25 | #

    I have the module installed, but i am not getting anything in the reports on the admin side. Is there something i am missing?

  17. Ales
    06-03-2013 at 17:06 | #

    No orders showing in the backend
    (no errors, backorders in the Report menu)

    1) Go to

    app/code/local/Inchoo/Backorderreport/Block/Adminhtml/Backorderreport/Backorder.php

    2) Find (4x)

    ‘sales_product_manufacturer_int.attribute_id = 102′

    3) Change it to

    ‘sales_product_manufacturer_int.attribute_id = XX’

    where XX is the “attribute ID” for your “manufacturer” attribute (in database in “eav_attribute” table)

    That worked for me.

  18. Henk de Gier
    17-04-2013 at 12:42 | #

    Hi,

    When i install it, and go to Reports > Backorder Report, i get the following error. How can i fix this? Thanks in advance!

    SQLSTATE[42S02]: Base table or view not found: 1146 Table ‘merit_cms.catalog_product_entity_varchar’ doesn’t exist

    Trace:
    #0 /home/merit/domains/meritracing.buro210.nl/public_html/lib/Varien/Db/Statement/Pdo/Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array)
    #1 /home/merit/domains/meritracing.buro210.nl/public_html/lib/Zend/Db/Statement.php(300): Varien_Db_Statement_Pdo_Mysql->_execute(Array)
    #2 /home/merit/domains/meritracing.buro210.nl/public_html/lib/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Statement->execute(Array)
    #3 /home/merit/domains/meritracing.buro210.nl/public_html/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query(‘SELECT `main_ta…’, Array)
    #4 /home/merit/domains/meritracing.buro210.nl/public_html/lib/Varien/Db/Adapter/Pdo/Mysql.php(419): Zend_Db_Adapter_Pdo_Abstract->query(‘SELECT `main_ta…’, Array)
    #5 /home/merit/domains/meritracing.buro210.nl/public_html/lib/Zend/Db/Adapter/Abstract.php(734): Varien_Db_Adapter_Pdo_Mysql->query(‘SELECT `main_ta…’, Array)
    #6 /home/merit/domains/meritracing.buro210.nl/public_html/lib/Varien/Data/Collection/Db.php(734): Zend_Db_Adapter_Abstract->fetchAll(‘SELECT `main_ta…’, Array)
    #7 /home/merit/domains/meritracing.buro210.nl/public_html/app/code/core/Mage/Core/Model/Resource/Db/Collection/Abstract.php(521): Varien_Data_Collection_Db->_fetchAll(‘SELECT `main_ta…’, Array)
    #8 /home/merit/domains/meritracing.buro210.nl/public_html/lib/Varien/Data/Collection/Db.php(566): Mage_Core_Model_Resource_Db_Collection_Abstract->getData()
    #9 /home/merit/domains/meritracing.buro210.nl/public_html/lib/Varien/Data/Collection.php(741): Varien_Data_Collection_Db->load()
    #10 /home/merit/domains/meritracing.buro210.nl/public_html/app/code/local/Inchoo/Backorderreport/Block/Adminhtml/Backorderreport/Backorder.php(45): Varien_Data_Collection->getIterator()
    #11 /home/merit/domains/meritracing.buro210.nl/public_html/app/design/adminhtml/default/default/template/inchoo/backorderreport/backorder.phtml(48): Inchoo_Backorderreport_Block_Adminhtml_Backorderreport_Backorder->getBackorders(Object(Mage_Sales_Model_Resource_Order_Item_Collection))
    #12 /home/merit/domains/meritracing.buro210.nl/public_html/app/code/core/Mage/Core/Block/Template.php(241): include(‘/home/merit/dom…’)
    #13 /home/merit/domains/meritracing.buro210.nl/public_html/app/code/core/Mage/Core/Block/Template.php(272): Mage_Core_Block_Template->fetchView(‘adminhtml/defau…’)
    #14 /home/merit/domains/meritracing.buro210.nl/public_html/app/code/core/Mage/Core/Block/Template.php(286): Mage_Core_Block_Template->renderView()
    #15 /home/merit/domains/meritracing.buro210.nl/public_html/app/code/core/Mage/Core/Block/Abstract.php(863): Mage_Core_Block_Template->_toHtml()
    #16 /home/merit/domains/meritracing.buro210.nl/public_html/app/code/core/Mage/Core/Block/Text/List.php(43): Mage_Core_Block_Abstract->toHtml()
    #17 /home/merit/domains/meritracing.buro210.nl/public_html/app/code/core/Mage/Core/Block/Abstract.php(863): Mage_Core_Block_Text_List->_toHtml()
    #18 /home/merit/domains/meritracing.buro210.nl/public_html/app/code/core/Mage/Core/Block/Abstract.php(582): Mage_Core_Block_Abstract->toHtml()
    #19 /home/merit/domains/meritracing.buro210.nl/public_html/app/code/core/Mage/Core/Block/Abstract.php(526): Mage_Core_Block_Abstract->_getChildHtml(‘content’, true)
    #20 /home/merit/domains/meritracing.buro210.nl/public_html/app/design/adminhtml/default/default/template/page.phtml(74): Mage_Core_Block_Abstract->getChildHtml(‘content’)
    #21 /home/merit/domains/meritracing.buro210.nl/public_html/app/code/core/Mage/Core/Block/Template.php(241): include(‘/home/merit/dom…’)
    #22 /home/merit/domains/meritracing.buro210.nl/public_html/app/code/core/Mage/Core/Block/Template.php(272): Mage_Core_Block_Template->fetchView(‘adminhtml/defau…’)
    #23 /home/merit/domains/meritracing.buro210.nl/public_html/app/code/core/Mage/Core/Block/Template.php(286): Mage_Core_Block_Template->renderView()
    #24 /home/merit/domains/meritracing.buro210.nl/public_html/app/code/core/Mage/Adminhtml/Block/Template.php(81): Mage_Core_Block_Template->_toHtml()
    #25 /home/merit/domains/meritracing.buro210.nl/public_html/app/code/core/Mage/Core/Block/Abstract.php(863): Mage_Adminhtml_Block_Template->_toHtml()
    #26 /home/merit/domains/meritracing.buro210.nl/public_html/app/code/core/Mage/Core/Model/Layout.php(555): Mage_Core_Block_Abstract->toHtml()
    #27 /home/merit/domains/meritracing.buro210.nl/public_html/app/code/core/Mage/Core/Controller/Varien/Action.php(390): Mage_Core_Model_Layout->getOutput()
    #28 /home/merit/domains/meritracing.buro210.nl/public_html/app/code/local/Inchoo/Backorderreport/controllers/Adminhtml/Backorderreport/BackorderController.php(12): Mage_Core_Controller_Varien_Action->renderLayout()
    #29 /home/merit/domains/meritracing.buro210.nl/public_html/app/code/core/Mage/Core/Controller/Varien/Action.php(419): Inchoo_Backorderreport_Adminhtml_Backorderreport_BackorderController->indexAction()
    #30 /home/merit/domains/meritracing.buro210.nl/public_html/app/code/core/Mage/Core/Controller/Varien/Router/Standard.php(250): Mage_Core_Controller_Varien_Action->dispatch(‘index’)
    #31 /home/merit/domains/meritracing.buro210.nl/public_html/app/code/core/Mage/Core/Controller/Varien/Front.php(176): Mage_Core_Controller_Varien_Router_Standard->match(Object(Mage_Core_Controller_Request_Http))
    #32 /home/merit/domains/meritracing.buro210.nl/public_html/app/code/core/Mage/Core/Model/App.php(354): Mage_Core_Controller_Varien_Front->dispatch()
    #33 /home/merit/domains/meritracing.buro210.nl/public_html/app/Mage.php(683): Mage_Core_Model_App->run(Array)
    #34 /home/merit/domains/meritracing.buro210.nl/public_html/index.php(87): Mage::run(”, ‘store’)
    #35 {main}

Add Your Comment

Please wrap all source codes with [code][/code] tags.
Top