Backorders Report Magento Extension

By: Dejan Radic, Last updated: Mar 12, 2020 | Published: Jan 30, 2012
Backorders Report Magento Extension

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.

Published in:
Leave a comment

You made it all the way down here so you must have enjoyed this post! You may also like:

Add custom image field for custom options Antun Martinovic
Antun Martinovic, | 5

Add custom image field for custom options

Adding gallery tab to a CMS page Antun Martinovic
Antun Martinovic, | 5

Adding gallery tab to a CMS page

Experiences of running Magento 1 on PHP 7 Ivan Curdinjakovic
Ivan Curdinjakovic, | 6

Experiences of running Magento 1 on PHP 7

26 comments

  2. Im also getting table not exisits.

    I didnt see replies from inchoo regarding this.. Please suggest anyone.

    Im using latest version of magento 1.9.2

    Reply

  4. Hi all, to get the current ‘manufacturer’ attribute id I defined the following function in block Backorder.php:


    public function getManufacturerAttId()
    {
    $product = Mage::getModel('catalog/product');

    $attributes = Mage::getResourceModel('eav/entity_attribute_collection')
    ->setEntityTypeFilter($product->getResource()->getTypeId())
    ->addFieldToFilter('attribute_code', 'manufacturer')
    ->load(false);

    $att = $attributes->getFirstItem();
    $id = $att->getData('attribute_id');
    return $id;
    }

    and then replaced all occurrences of $conditionsVarchar with:


    $conditionsVarchar = array(
    'main_table.product_id = sales_product_manufacturer_varchar.entity_id',
    'sales_product_manufacturer_varchar.attribute_id = '
    . $this->getManufacturerAttId()
    );

    Works perfect till Magento CE version 1.7.0.2.
    All in all a useful pugin!!

    Reply

  5. I get Base table or view not found: 1146 Table ‘magento.catalog_product_entity_varchar’ doesn’t exist

    Does this script take into account table prefixes?

    thanks

    Reply

  7. You used this line in backorder.php

    sales_product_manufacturer_varchar.attribute_id = 102' (4 times you use this attribute_id ‘102

    This value (102) should be the value of column ‘attribute_id’ for the attribute ‘manufacturer’ in table ‘eav_attribute, right?

    If so, mine has value 81 but after changing this (4 x) it doesn’t work, I get an error ‘The requested payment method isn’t available’
    .
    My manufacturer attribute is a dropdown attribute, does this could be a problem (instead of the default input field?)

    Reply

  9. 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}

    Reply

  10. 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.

    Reply

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

    Reply

  12. 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

    Reply

  13. 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 !!!

    Reply

  14. 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

    Reply

  15. 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

    =)

    Reply

  16. 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

    Reply

  17. 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. =)

    Reply

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

    Reply

  19. 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,

    Reply

  22. 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 🙁

    Reply

  24. @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.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <blockquote cite=""> <code> <del datetime=""> <em> <s> <strike> <strong>. You may use following syntax for source code: <pre><code>$current = "Inchoo";</code></pre>.

Tell us about your project

Drop us a line. We'd love to know more about your project.