Magento custom reports: Get data from database

Question

I need some help.

I created a custom report in Magento. Now I want to list all products wich have been ordered in a month in my grid. In my report are the following columns: SKU, Name, Ordered Quantity and Base Cost.

In the column "Ordered Quantity" I want to show how often a product has been ordered. In the column "Base Cost" I want to show the total base costs (ordered quantity * base cost).

With the following code I get so correct product names and skus. The other columns are not correct.

Can someone help me?

$this->getSelect()->reset()
        ->from(
            array('order_items' => $this->getTable('sales/order_item')),
            array(
                'ordered_qty' => 'SUM(order_items.qty_ordered)',
                'order_items_name' => 'order_items.name',
                'base_cost' => 'SUM(order_items.base_cost)',
                'sku' => 'order_items.sku'
            ))
        ->where("created_at BETWEEN '".$from."' AND '".$to."'")
        ->where('parent_item_id IS NULL')
        ->group('order_items.product_id')
        ->having('SUM(order_items.qty_ordered) > ?', 0)
        ->order(
            array(
                'SUM(order_items.qty_ordered) DESC'
            ));

The quantity of the ordered products is too high and the sum of the base costs is wrong (base cost of one item is correct). — user3684098, May 28 '14 at 15:13
Try outputting the raw SQL query see http://stackoverflow.com/questions/23834183/output-raw-sql-query-from-magento-collection/23834270#23834270 — MagePal Extensions, May 28 '14 at 15:17
Thank you R.S for the evidence! Now I get the correct sum. How can I format the costs? This is in my Grid.php: $this->addColumn('base_cost', array( 'header' =>Mage::helper('reports')->__('Cost'), 'width' =>'120px', 'align' =>'right', 'index' =>'base_cost', 'total' =>'sum', 'type' =>'price' )); — user3684098, Jun 05 '14 at 12:50

score 1 · Answer 1 · answered Jun 06 '14 at 09:12

Here is my solution:

$this->getSelect()->reset()
        ->from(
            array('order_items' => $this->getTable('sales/order_item')),
            array(
                'ordered_qty' => 'order_items.qty_ordered',
                'order_items_name' => 'order_items.name',
                'vendor' => 'attrval.value',
                'base_cost' => '(SUM(order_items.qty_ordered) * order_items.base_cost)',
                'sku' => 'order_items.sku'
            ))
        ->joinLeft(array('p' => 'catalog_product_entity'), 'order_items.product_id = p.entity_id')
        ->joinLeft(array('eav' => 'eav_attribute'), 'p.entity_type_id = eav.entity_type_id')
        ->joinLeft(array('attr' =>'eav_attribute_option'), 'attr.attribute_id = eav.attribute_id')
        ->joinLeft(array('attrval' =>'eav_attribute_option_value'), 'attrval.option_id = attr.option_id')
        ->where("eav.attribute_code='vendor'")
        ->where("order_items.created_at BETWEEN '".$from."' AND '".$to."'")
        ->where('parent_item_id IS NULL')
        ->group('order_items.product_id')
        ->having('SUM(order_items.qty_ordered) > ?', 0)
        ->order(
            array(
                'SUM(order_items.qty_ordered) DESC'
            ));

It includes an additional custom attribute called 'vendor'.

score 0 · Answer 2 · edited May 23 '17 at 11:58

0

To outputting the raw SQL query see Output raw SQL query from Magento collection

Format field, you can use price or currency

see http://code007.wordpress.com/2012/07/16/grid-column-types-in-magento/

  $this->addColumn('some_column_id', array(
          'header' => Mage::helper('core')->__('Some column name'),
          'index' => 'some_column_index',
          'type' => '???',
  ));

Types

action
checkbox
concat
country
currency
date
datetime
input
interface
ip
longtext
massaction
number
options
price
radio
select
store
text
theme
wrapline

See /app/code/core/Mage/Adminhtml/Block/Widget/Grid/Column/Renderer folder.

To make your own grid types see http://mydons.com/how-to-create-custom-column-renderer-in-magento-grid/.

edited May 23 '17 at 11:58

Community

1
1

answered Jun 05 '14 at 13:49

MagePal Extensions

17,646
2
47
62

I've already done it by the code I commented above. I get a number like 2618.00000000 and I want to format that like 2618,00 $ – user3684098 Jun 05 '14 at 15:35
You may need to add the `currency_code` see http://stackoverflow.com/questions/4897310/add-a-new-column-for-special-price-in-grid-in-admin-panel-in-magento – MagePal Extensions Jun 05 '14 at 16:10
Thanks! 'currency_code' => Mage::app()->getStore()->getCurrentCurrencyCode() was what I needed! – user3684098 Jun 06 '14 at 08:24

Magento custom reports: Get data from database

2 Answers2