0

got an issue when adding a field to the "Total Ordered Report" I can add the column section without issue but pulling the data is what is giving me an issue.

I keep getting this error. Both when loading the report page and when trying to rebuild the index - if the index rebuilds successfully then the report page will fix as well.

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'o.order_cost_total' in 'field list'' in /var/www/html/coopersdev/lib/Zend/Db/Statement/Pdo.php:228

And I can see it is running through the trace where it sees my /Model/Resource/Sales/Report/Order/Createdat.php and in this file I overwrite _aggregateByField, I also overwrite Model_Resource_Sales_Report_Order_Collection and include the column in my _getSelectedColumns function so I know it is there but this error keeps appearing and my lack of understanding is killing me here - I don't see what I am missing, I can open the database and see that my column is there, the last column in the sales_flat_order table but it doesn't seem to find it. Here is my code for both below. (No need to read all of it just look for 'order_cost_total)

The Createdat

class XXX_XXX_Model_Resource_Sales_Report_Order_Createdat extends Mage_Sales_Model_Resource_Report_Order_Createdat {

    protected function _aggregateByField($aggregationField, $from, $to)
    {
        Mage::log("createdat Rewrite Fired");
        // convert input dates to UTC to be comparable with DATETIME fields in DB
        $from = $this->_dateToUtc($from);
        $to   = $this->_dateToUtc($to);

        $this->_checkDates($from, $to);
        $adapter = $this->_getWriteAdapter();

        $adapter->beginTransaction();
        try {

            if ($from !== null || $to !== null) {
                $subSelect = $this->_getTableDateRangeSelect(
                    $this->getTable('sales/order'),
                    $aggregationField, $aggregationField, $from, $to
                );
            } else {
                $subSelect = null;
            }
            $this->_clearTableByDateRange($this->getMainTable(), $from, $to, $subSelect);

            $periodExpr = $adapter->getDatePartSql($this->getStoreTZOffsetQuery(
                array('o' => $this->getTable('sales/order')),
                'o.' . $aggregationField,
                $from, $to
            ));

            // Columns list
            $columns = array(
                // convert dates from UTC to current admin timezone
                'period'                         => $periodExpr,
                'store_id'                       => 'o.store_id',
                'order_status'                   => 'o.status',
                'order_cost_total'               => new Zend_Db_Expr('SUM(o.order_cost_total)'),
                'orders_count'                   => new Zend_Db_Expr('COUNT(o.entity_id)'),
                'total_qty_ordered'              => new Zend_Db_Expr('SUM(oi.total_qty_ordered)'),
                'total_qty_invoiced'             => new Zend_Db_Expr('SUM(oi.total_qty_invoiced)'),
                'total_income_amount'            => new Zend_Db_Expr(
                    sprintf('SUM((%s - %s) * %s)',
                        $adapter->getIfNullSql('o.base_grand_total', 0),
                        $adapter->getIfNullSql('o.base_total_canceled',0),
                        $adapter->getIfNullSql('o.base_to_global_rate',0)
                    )
                ),
                'total_revenue_amount'           => new Zend_Db_Expr(
                    sprintf('SUM((%s - %s - %s - (%s - %s - %s)) * %s)',
                        $adapter->getIfNullSql('o.base_total_invoiced', 0),
                        $adapter->getIfNullSql('o.base_tax_invoiced', 0),
                        $adapter->getIfNullSql('o.base_shipping_invoiced', 0),
                        $adapter->getIfNullSql('o.base_total_refunded', 0),
                        $adapter->getIfNullSql('o.base_tax_refunded', 0),
                        $adapter->getIfNullSql('o.base_shipping_refunded', 0),
                        $adapter->getIfNullSql('o.base_to_global_rate', 0)
                    )
                ),
                'total_profit_amount'            => new Zend_Db_Expr(
                    sprintf('SUM(((%s - %s) - (%s - %s) - (%s - %s) - %s) * %s)',
                        $adapter->getIfNullSql('o.base_total_paid', 0),
                        $adapter->getIfNullSql('o.base_total_refunded', 0),
                        $adapter->getIfNullSql('o.base_tax_invoiced', 0),
                        $adapter->getIfNullSql('o.base_tax_refunded', 0),
                        $adapter->getIfNullSql('o.base_shipping_invoiced', 0),
                        $adapter->getIfNullSql('o.base_shipping_refunded', 0),
                        $adapter->getIfNullSql('o.base_total_invoiced_cost', 0),
                        $adapter->getIfNullSql('o.base_to_global_rate', 0)
                    )
                ),
                'total_invoiced_amount'          => new Zend_Db_Expr(
                    sprintf('SUM(%s * %s)',
                        $adapter->getIfNullSql('o.base_total_invoiced', 0),
                        $adapter->getIfNullSql('o.base_to_global_rate', 0)
                    )
                ),
                'total_canceled_amount'          => new Zend_Db_Expr(
                    sprintf('SUM(%s * %s)',
                        $adapter->getIfNullSql('o.base_total_canceled', 0),
                        $adapter->getIfNullSql('o.base_to_global_rate', 0)
                    )
                ),
                'total_paid_amount'              => new Zend_Db_Expr(
                    sprintf('SUM(%s * %s)',
                        $adapter->getIfNullSql('o.base_total_paid', 0),
                        $adapter->getIfNullSql('o.base_to_global_rate', 0)
                    )
                ),
                'total_refunded_amount'          => new Zend_Db_Expr(
                    sprintf('SUM(%s * %s)',
                        $adapter->getIfNullSql('o.base_total_refunded', 0),
                        $adapter->getIfNullSql('o.base_to_global_rate', 0)
                    )
                ),
                'total_tax_amount'               => new Zend_Db_Expr(
                    sprintf('SUM((%s - %s) * %s)',
                        $adapter->getIfNullSql('o.base_tax_amount', 0),
                        $adapter->getIfNullSql('o.base_tax_canceled', 0),
                        $adapter->getIfNullSql('o.base_to_global_rate', 0)
                    )
                ),
                'total_tax_amount_actual'        => new Zend_Db_Expr(
                    sprintf('SUM((%s -%s) * %s)',
                        $adapter->getIfNullSql('o.base_tax_invoiced', 0),
                        $adapter->getIfNullSql('o.base_tax_refunded', 0),
                        $adapter->getIfNullSql('o.base_to_global_rate', 0)
                    )
                ),
                'total_shipping_amount'          => new Zend_Db_Expr(
                    sprintf('SUM((%s - %s) * %s)',
                        $adapter->getIfNullSql('o.base_shipping_amount', 0),
                        $adapter->getIfNullSql('o.base_shipping_canceled', 0),
                        $adapter->getIfNullSql('o.base_to_global_rate', 0)
                    )
                ),
                'total_shipping_amount_actual'   => new Zend_Db_Expr(
                    sprintf('SUM((%s - %s) * %s)',
                        $adapter->getIfNullSql('o.base_shipping_invoiced', 0),
                        $adapter->getIfNullSql('o.base_shipping_refunded', 0),
                        $adapter->getIfNullSql('o.base_to_global_rate', 0)
                    )
                ),
                'total_discount_amount'          => new Zend_Db_Expr(
                    sprintf('SUM((ABS(%s) - %s) * %s)',
                        $adapter->getIfNullSql('o.base_discount_amount', 0),
                        $adapter->getIfNullSql('o.base_discount_canceled', 0),
                        $adapter->getIfNullSql('o.base_to_global_rate', 0)
                    )
                ),
                'total_discount_amount_actual'   => new Zend_Db_Expr(
                    sprintf('SUM((%s - %s) * %s)',
                        $adapter->getIfNullSql('o.base_discount_invoiced', 0),
                        $adapter->getIfNullSql('o.base_discount_refunded', 0),
                        $adapter->getIfNullSql('o.base_to_global_rate', 0)
                    )
                )
            );

            $select          = $adapter->select();
            $selectOrderItem = $adapter->select();

            $qtyCanceledExpr = $adapter->getIfNullSql('qty_canceled', 0);
            $cols            = array(
                'order_id'           => 'order_id',
                'total_qty_ordered'  => new Zend_Db_Expr("SUM(qty_ordered - {$qtyCanceledExpr})"),
                'total_qty_invoiced' => new Zend_Db_Expr('SUM(qty_invoiced)'),
            );
            $selectOrderItem->from($this->getTable('sales/order_item'), $cols)
                ->where('parent_item_id IS NULL')
                ->group('order_id');

            $select->from(array('o' => $this->getTable('sales/order')), $columns)
                ->join(array('oi' => $selectOrderItem), 'oi.order_id = o.entity_id', array())
                ->where('o.state NOT IN (?)', array(
                    Mage_Sales_Model_Order::STATE_PENDING_PAYMENT,
                    Mage_Sales_Model_Order::STATE_NEW
                ));

            if ($subSelect !== null) {
                $select->having($this->_makeConditionFromDateRangeSelect($subSelect, 'period'));
            }

            $select->group(array(
                $periodExpr,
                'o.store_id',
                'o.status',
            ));

            $adapter->query($select->insertFromSelect($this->getMainTable(), array_keys($columns)));

            // setup all columns to select SUM() except period, store_id and order_status
            foreach ($columns as $k => $v) {
                $columns[$k] = new Zend_Db_Expr('SUM(' . $k . ')');
            }
            $columns['period']         = 'period';
            $columns['store_id']       = new Zend_Db_Expr(Mage_Core_Model_App::ADMIN_STORE_ID);
            $columns['order_status']   = 'order_status';

            $select->reset();
            $select->from($this->getMainTable(), $columns)
                ->where('store_id <> 0');

            if ($subSelect !== null) {
                $select->where($this->_makeConditionFromDateRangeSelect($subSelect, 'period'));
            }

            $select->group(array(
                'period',
                'order_status'
            ));
            $adapter->query($select->insertFromSelect($this->getMainTable(), array_keys($columns)));
            $adapter->commit();
        } catch (Exception $e) {
            $adapter->rollBack();
            throw $e;
        }

        return $this;
    }
}

The Collection

class XXX_XXX_Model_Resource_Sales_Report_Order_Collection extends Mage_Sales_Model_Resource_Report_Order_Collection {

        protected function _getSelectedColumns()
        {

            $adapter = $this->getConnection();
            if ('month' == $this->_period) {
                $this->_periodFormat = $adapter->getDateFormatSql('period', '%Y-%m');
            } elseif ('year' == $this->_period) {
                $this->_periodFormat = $adapter->getDateExtractSql('period', Varien_Db_Adapter_Interface::INTERVAL_YEAR);
            } else {
                $this->_periodFormat = $adapter->getDateFormatSql('period', '%Y-%m-%d');
            }

            if (!$this->isTotals()) {
                $this->_selectedColumns = array(
                    'period'                         => $this->_periodFormat,
                    'orders_count'                   => 'SUM(orders_count)',
                    'order_cost_total'               => 'SUM(order_cost_total)',
                    'total_qty_ordered'              => 'SUM(total_qty_ordered)',
                    'total_qty_invoiced'             => 'SUM(total_qty_invoiced)',
                    'total_income_amount'            => 'SUM(total_income_amount)',
                    'total_revenue_amount'           => 'SUM(total_revenue_amount)',
                    'total_profit_amount'            => 'SUM(total_profit_amount)',
                    'total_invoiced_amount'          => 'SUM(total_invoiced_amount)',
                    'total_canceled_amount'          => 'SUM(total_canceled_amount)',
                    'total_paid_amount'              => 'SUM(total_paid_amount)',
                    'total_refunded_amount'          => 'SUM(total_refunded_amount)',
                    'total_tax_amount'               => 'SUM(total_tax_amount)',
                    'total_tax_amount_actual'        => 'SUM(total_tax_amount_actual)',
                    'total_shipping_amount'          => 'SUM(total_shipping_amount)',
                    'total_shipping_amount_actual'   => 'SUM(total_shipping_amount_actual)',
                    'total_discount_amount'          => 'SUM(total_discount_amount)',
                    'total_discount_amount_actual'   => 'SUM(total_discount_amount_actual)',
                );
            }

            if ($this->isTotals()) {
                $this->_selectedColumns = $this->getAggregatedColumns();
            }

            return $this->_selectedColumns;
        }
    }

Is anyone able to help me at all or point me in the right direction?

1

The Answer? Don't be a moron.

I forgot to push my SQL installer update that added the column to the sales_order_aggregated_created and the sales_order_aggregated_updated tables.

I just assumed it must not be reading it from the sales/order table since the installer was in there when I double checked I noticed the column wasn't actually there.

So if you ever wish to add a column to this report. Update the collection.php the createdat.php, make an observer to do what you wish and ensure your column creations on the sales/order (or order item if you are using that), sales/order_aggregated_created, and sales/order_aggregated_updated are all in place.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.