SQL Magento Sales Report

Question

I am trying to add new columns to the report / sales / delivery. The columns are now ok, but I cannot display the values ​​coming from another table.

in app / code / local / Mage / Sales / Model / Mysql4 / Report / Shipping.php

After

protected function _aggregateByOrderCreatedAt($from, $to)
{
    try {
        $tableName = $this->getTable('sales/shipping_aggregated_order');
        $writeAdapter = $this->_getWriteAdapter();

        $writeAdapter->beginTransaction();

        if (is_null($from) && is_null($to)) {
            $writeAdapter->query("TRUNCATE TABLE {$tableName}");
        } else {
            $where = (!is_null($from)) ? "so.updated_at >= '{$from}'" : '';
            if (!is_null($to)) {
                $where .= (!empty($where)) ? " AND so.updated_at <= '{$to}'" : "so.updated_at <= '{$to}'";
            }

            $subQuery = $writeAdapter->select();
            $subQuery->from(array('so'=>$this->getTable('sales/order')), array('DISTINCT DATE(so.created_at)'))
                ->where($where);

I add these 4 lines: $ subQuery-> joinInner:

            $subQuery->joinInner(array('sd'=> $this->getTable('sales/order_datetime')), 
            "`sd`.`entity_id` = `so`.`entity_id`",
            array()
        );


            $deleteCondition = 'DATE(period) IN (' . new Zend_Db_Expr($subQuery) . ')';
            $writeAdapter->delete($tableName, $deleteCondition);
        }


        $columns = array(
            'period'                => "DATE(created_at)",
            'shipping_description'  => 'shipping_description',
            'orders_count'          => 'COUNT(entity_id)',
            'total_shipping'        => 'SUM(`base_shipping_amount` * `base_to_global_rate`)',

            'store_id'              => 'store_id',
            'order_status'          => 'status',
            'point_relais'                => 'shipping_description',
            'date_commande'               => "DATE(created_at)",
            'date_livraison'              => "DATE(value)"

        $select = $writeAdapter->select()
            ->from($this->getTable('sales/order'), $columns)
            ->where('state NOT IN (?)', array(
                Mage_Sales_Model_Order::STATE_PENDING_PAYMENT,
                Mage_Sales_Model_Order::STATE_NEW
            ))
            ->where('is_virtual = 0');

            if (!is_null($from) || !is_null($to)) {
                $select->where("DATE(created_at) IN(?)", new Zend_Db_Expr($subQuery));
            }

            $select->group(array(
                "DATE(created_at)",
                'store_id',
                'order_status',
                'shipping_description'
                'point_relais',
                'date_commande',
                'date_livraison',
                'client'
            ));

At the end of "value" in "date_livraison" is the only variable coming from the sales / order_datetime table, and I cannot display it. Other variables come from the sales / order table and display well.

I think something is wrong or not in

            $subQuery->joinInner(array('sd'=> $this->getTable('sales/order_datetime')), 
            "`sd`.`entity_id` = `so`.`entity_id`",
            array()
        );

Ive declared the sales_order_datetime table in app / code / Core / Mage / Sales / etc / config.xml with:

                <order_datetime><table>sales_order_datetime</table></order_datetime>

And the date_livraison database is in the appropriate format

If you could help me. Thanks

Answer 1

.

 $subQuery->joinInner(array('sd'=> $this->getTable('sales/order_datetime')), 
        "`sd`.`entity_id` = `so`.`entity_id`",
        array('name_of_column')  //   <---This is what is missing.
    );

.