Magento自定义报告：从数据库中获取数据

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'
            ));

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

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