(Magento 1.7) How to modify the report module?

Question

I followed a tutorial to create report module and the tutorial show me exactly what I wanted. Now, I would like to modify the database into my database. There are two tables that I want to join (member_download and sales_payment). What should I modify from this code to link it into my database?

Here is the code:

<?php
class Wcl_ReportNewOrders_Model_Reportneworders extends Mage_Reports_Model_Mysql4_Product_Ordered_Collection
{
    function __construct() {
        parent::__construct();
    } 

    protected function _joinFields($from = '', $to = '')
    {
        $this->addAttributeToSelect('name')
            ->addAttributeToSelect('increment_id')
            ->addOrderedQty($from, $to)
            ->setOrder('sku', self::SORT_ORDER_ASC);

        //Mage::log('SQL: '.$this->getSelect()->__toString());
        return $this;
    } 

    public function addOrderedQty($from = '', $to = '')
    {
        $adapter              = $this->getConnection();
        $compositeTypeIds     = Mage::getSingleton('catalog/product_type')->getCompositeTypes();
        $orderTableAliasName  = $adapter->quoteIdentifier('order');
        $addressTableAliasName  = 'a';
        $downloadTableAliasName = 'download';
        $orderJoinCondition   = array(
                $orderTableAliasName . '.entity_id = order_items.order_id',
                $adapter->quoteInto("{$orderTableAliasName}.state = ?", Mage_Sales_Model_Order::STATE_PROCESSING),

        );

        $addressJoinCondition = array(
                $addressTableAliasName . '.entity_id = order.shipping_address_id'
        );

        $downloadJoinCondition = array(
                $downloadTableAliasName . '.order_id = order_items.order_id'
        );

        $productJoinCondition = array(
                //$adapter->quoteInto('(e.type_id NOT IN (?))', $compositeTypeIds),
                'e.entity_id = order_items.product_id',
                $adapter->quoteInto('e.entity_type_id = ?', $this->getProductEntityTypeId())
        );

        if ($from != '' && $to != '') {
            $fieldName            = $orderTableAliasName . '.created_at';
            $orderJoinCondition[] = $this->_prepareBetweenSql($fieldName, $from, $to);
        }

        $this->getSelect()->reset()
        ->from(
                array('order_items' => $this->getTable('sales/order_item')),
                array(
                        'qty_ordered' => 'order_items.qty_ordered',
                        'order_items_name' => 'order_items.name',
                        'order_increment_id' => 'order.increment_id',
                        'sku' => 'order_items.sku',
                        'type_id' => 'order_items.product_type',
                        'shipping_address_id' => 'order.shipping_address_id'
                ))
                ->joinInner(
                        array('order' => $this->getTable('sales/order')),
                        implode(' AND ', $orderJoinCondition),
                        array())

                        ->joinLeft(
                                array('a' => $this->getTable('sales/order_address')),
                                implode(' AND ', $addressJoinCondition),
                                array(
                                        'shipto_name' => "CONCAT(COALESCE(a.firstname, ''), ' ', COALESCE(a.lastname, ''))"
                                ),
                        array())


                        ->joinLeft(
                                array('e' => $this->getProductEntityTableName()),
                                implode(' AND ', $productJoinCondition),
                                array(
                                        'created_at' => 'e.created_at',
                                        'updated_at' => 'e.updated_at'
                                )) 


                        ->where('parent_item_id IS NULL')
                        //->group('order_items.product_id')
                        ->having('order_items.qty_ordered > ?', 0);
        return $this;
    } 

    public function addItem(Varien_Object $item)
    {
        $itemId = $this->_getItemId($item);

        if (!is_null($itemId)) {
            if (isset($this->_items[$itemId])) {
                // Unnecessary exception - http://www.magentocommerce.com/boards/viewthread/10634/P0/
                //throw new Exception('Item ('.get_class($item).') with the same id "'.$item->getId().'" already exist');
            }
            $this->_items[$itemId] = $item;
        } else {
            $this->_items[] = $item;
        }
        return $this;
    }
}

Please tell me how to modify the database. I am new with magento. Thanks!

Answer 1

i checked your link that you provided. open file at below location

Wcl_ReportNewOrders_Block_Adminhtml_ReportNewOrders_Grid

below is the function where you can modify the database

protected function _prepareCollection() {
    parent::_prepareCollection();
    // Get the data collection from the model
    $this->getCollection()->initReport('reportneworders/reportneworders');
    return $this;
}

Replace the above code with below code

protected function _prepareCollection() {
    parent::_prepareCollection();

    $collection = $this->getCollection()->initReport('reportneworders/reportneworders');
    /*
        perform your desired operation here
       // print_r((string)$collection->getSelect(); 
    */
    $this->setCollection($collection);
    return $this;

}