Mage_Reports_Model_Mysql4_Order_Collection Class Reference

Inheritance diagram for Mage_Reports_Model_Mysql4_Order_Collection:

List of all members.

Public Member Functions
	prepareSummary ($range, $customStart, $customEnd, $isFilter=0)
	getDateRange ($range, $customStart, $customEnd, $returnObjects=false)
	addItemCountExpr ()
	calculateTotals ($isFilter=0)
	calculateSales ($isFilter=0)
	setDateRange ($from, $to)
	setStoreIds ($storeIds)
	groupByCustomer ()
	joinCustomerName ()
	addOrdersCount ()
	addSumAvgTotals ($storeId=0)
	orderByTotalAmount ($dir= 'desc')
	orderByOrdersCount ($dir= 'desc')
	orderByCustomerRegistration ($dir= 'desc')
	getSelectCountSql ()
Protected Member Functions
	_getRangeExpression ($range)

---

Detailed Description

Definition at line 35 of file Collection.php.

---

Member Function Documentation

_getRangeExpression

(

$

range

)

[protected]

Definition at line 61 of file Collection.php.

    {
        // dont need of this offset bc we are format date in block
        //$timeZoneOffset = Mage::getModel('core/date')->getGmtOffset();

        switch ($range)
        {
            case '24h':
                $expression = 'DATE_FORMAT({{attribute}}, \'%Y-%m-%d %H:00\')';

                break;
            case '7d':
            case '1m':
               $expression = 'DATE_FORMAT({{attribute}}, \'%Y-%m-%d\')';
               break;
            case '1y':
            case '2y':
            case 'custom':
            default:
                $expression = 'DATE_FORMAT({{attribute}}, \'%Y-%m\')';
                break;
        }

        return $expression;
    }

addItemCountExpr

(

)

Reimplemented from Mage_Sales_Model_Entity_Order_Collection.

Definition at line 143 of file Collection.php.

    {
//        $orderItemEntityTypeId = Mage::getResourceSingleton('sales/order_item')->getTypeId();
//        $this->getSelect()->join(
//                array('items'=>Mage::getResourceSingleton('sales/order_item')->getEntityTable()),
//                'items.parent_id=e.entity_id and items.entity_type_id='.$orderItemEntityTypeId,
//                array('items_count'=>new Zend_Db_Expr('COUNT(items.entity_id)'))
//            )
//            ->group('e.entity_id');
        $this->getSelect()->join(
                array('items'=>$this->getTable('sales/order_item')),
                'items.order_id=e.entity_id',
                array('items_count'=>new Zend_Db_Expr('COUNT(items.item_id)', 'parent_item'))
            )
            ->where('items.parent_item_id is NULL')
            ->group('e.entity_id');
        return $this;
    }

addOrdersCount

(

)

Add Order count field to select

Returns:

Mage_Reports_Model_Mysql4_Order_Collection

Definition at line 365 of file Collection.php.

    {
        $this->addAttributeToFilter('state', array('neq' => Mage_Sales_Model_Order::STATE_CANCELED));
        $this->getSelect()
            ->from('', array("orders_count" => "COUNT(e.entity_id)"));

        return $this;
    }

addSumAvgTotals

(

$

storeId = 0

)

Add summary average totals

Parameters:

int

$storeId

Returns:

Mage_Reports_Model_Mysql4_Order_Collection

Join store_to_base_rate attribute

calculate average and total amount

calculate average and total amount

Definition at line 380 of file Collection.php.

    {
        if ($storeId == 0) {
            /**
             * Join store_to_base_rate attribute
             */
            $order = Mage::getResourceSingleton('sales/order');
            /* @var $order Mage_Sales_Model_Entity_Order */

            $attr = $order->getAttribute('base_to_global_rate');
            /* @var $attr Mage_Eav_Model_Entity_Attribute_Abstract */
            $attrId = $attr->getAttributeId();
            $attributeTableName = $attr->getBackend()->getTable();
            $baseToGlobalRateTableName = $attr->getBackend()->isStatic() ? 'base_to_global_rate' : 'value';

            $this->getSelect()
                ->joinLeft(array('_b2gr_'.$baseToGlobalRateTableName => $attributeTableName),
                    "_b2gr_{$baseToGlobalRateTableName}.entity_id=e.entity_id AND ".
                    "_b2gr_{$baseToGlobalRateTableName}.attribute_id={$attrId}", array());

            /**
             * calculate average and total amount
             */
            $expr = "(e.base_subtotal-IFNULL(e.base_subtotal_refunded,0)-IFNULL(e.base_subtotal_canceled,0))*_b2gr_{$baseToGlobalRateTableName}.{$baseToGlobalRateTableName}";

        } else {

            /**
             * calculate average and total amount
             */
            $expr = "e.base_subtotal-IFNULL(e.base_subtotal_canceled,0)-IFNULL(e.base_subtotal_refunded,0)";
        }

        $this->getSelect()
            ->from('', array("orders_avg_amount" => "AVG({$expr})"))
            ->from('', array("orders_sum_amount" => "SUM({$expr})"));

        return $this;
    }

calculateSales

(

$

isFilter = 0

)

Definition at line 199 of file Collection.php.

    {
        if ($isFilter == 0) {
            $expr = "({{base_subtotal}}-IFNULL({{base_subtotal_refunded}},0)-IFNULL({{base_subtotal_canceled}},0)-IFNULL({{base_discount_amount}},0)+IFNULL({{base_discount_refunded}},0))*{{base_to_global_rate}}";
            $attrs = array('base_subtotal', 'base_to_global_rate', 'base_subtotal_refunded', 'base_subtotal_canceled','base_discount_amount','base_discount_refunded');
            $this->addExpressionAttributeToSelect('lifetime', "SUM({$expr})", $attrs)
                ->addExpressionAttributeToSelect('average', "AVG({$expr})", $attrs);
        } else {
            $expr = "({{base_subtotal}}-IFNULL({{base_subtotal_refunded}},0)-IFNULL({{base_subtotal_canceled}},0)-IFNULL({{base_discount_amount}},0)+IFNULL({{base_discount_amount}},0))";
            $attrs = array('base_subtotal', 'base_subtotal_refunded', 'base_subtotal_canceled','base_discount_amount','base_discount_refunded');
            $this->addExpressionAttributeToSelect('lifetime', "SUM($expr)", $attrs)
                ->addExpressionAttributeToSelect('average', "AVG($expr)", $attrs);
        }

        $this->addAttributeToFilter('state', array('neq' => Mage_Sales_Model_Order::STATE_CANCELED))
            ->groupByAttribute('entity_type_id');
        return $this;
    }

calculateTotals

(

$

isFilter = 0

)

Definition at line 162 of file Collection.php.

    {
        if ($isFilter == 0) {
            $this->addExpressionAttributeToSelect(
                    'revenue',
                     'SUM(({{base_subtotal}}-IFNULL({{base_subtotal_refunded}},0)-IFNULL({{base_subtotal_canceled}},0)-IFNULL({{base_discount_amount}},0)+IFNULL({{base_discount_refunded}},0))*{{base_to_global_rate}})',
                     array('base_subtotal', 'base_to_global_rate', 'base_subtotal_refunded', 'base_subtotal_canceled','base_discount_amount','base_discount_refunded'))
                ->addExpressionAttributeToSelect(
                    'tax',
                    'SUM(({{base_tax_amount}}-IFNULL({{base_tax_refunded}},0)-IFNULL({{base_tax_canceled}},0))*{{base_to_global_rate}})',
                    array('base_tax_amount', 'base_tax_canceled', 'base_tax_refunded', 'base_to_global_rate'))
                ->addExpressionAttributeToSelect(
                    'shipping',
                    'SUM(({{base_shipping_amount}}-IFNULL({{base_shipping_refunded}},0)-IFNULL({{base_shipping_canceled}},0))*{{base_to_global_rate}})',
                    array('base_shipping_amount', 'base_shipping_refunded', 'base_shipping_canceled', 'base_to_global_rate'));
        } else {
            $this->addExpressionAttributeToSelect(
                    'revenue',
                     'SUM({{base_subtotal}}-IFNULL({{base_subtotal_refunded}},0)-IFNULL({{base_subtotal_canceled}},0)-IFNULL({{base_discount_amount}},0)+IFNULL({{base_discount_refunded}},0))',
                     array('base_subtotal', 'base_subtotal_refunded', 'base_subtotal_canceled','base_discount_amount','base_discount_refunded'))
                ->addExpressionAttributeToSelect(
                    'tax',
                    'SUM({{base_tax_amount}}-IFNULL({{base_tax_refunded}},0)-IFNULL({{base_tax_canceled}},0))',
                    array('base_tax_amount', 'base_tax_refunded', 'base_tax_canceled'))
                ->addExpressionAttributeToSelect(
                    'shipping',
                    'SUM({{base_shipping_amount}}-IFNULL({{base_shipping_refunded}},0)-IFNULL({{base_shipping_canceled}},0))',
                    array('base_shipping_amount', 'base_shipping_refunded', 'base_shipping_canceled'));
        }

        $this->addExpressionAttributeToSelect('quantity', 'COUNT({{entity_id}})', array('entity_id'))
            ->addAttributeToFilter('state', array('neq' => Mage_Sales_Model_Order::STATE_CANCELED))
            ->groupByAttribute('entity_type_id');

        return $this;
    }

getDateRange	(	$	range,
		$	customStart,
		$	customEnd,
		$	returnObjects = false
	)

Definition at line 87 of file Collection.php.

    {
        $dateEnd = new Zend_Date(Mage::getModel('core/date')->gmtTimestamp());
        $dateStart = clone $dateEnd;

        // go to the end of a day
        $dateEnd->setHour(23);
        $dateEnd->setMinute(59);
        $dateEnd->setSecond(59);

        $dateStart->setHour(0);
        $dateStart->setMinute(0);
        $dateStart->setSecond(0);

        switch ($range)
        {
            case '24h':
                $dateEnd = new Zend_Date(Mage::getModel('core/date')->gmtTimestamp());
                $dateEnd->addHour(1);
                $dateStart = clone $dateEnd;
                $dateStart->subDay(1);
                break;

            case '7d':
                // substract 6 days we need to include
                // only today and not hte last one from range
                $dateStart->subDay(6);
                break;

            case '1m':
                $dateStart->setDay(1);
                break;

            case 'custom':
                $dateStart = $customStart ? $customStart : $dateEnd;
                $dateEnd   = $customEnd ? $customEnd : $dateEnd;
                break;

            case '1y':
                $dateStart->setMonth(1);
                $dateStart->setDay(1);
                break;
            case '2y':
                $dateStart->setMonth(1);
                $dateStart->setDay(1);
                $dateStart->subYear(1);
                break;
        }

        if ($returnObjects) {
            return array($dateStart, $dateEnd);
        } else {
            return array('from'=>$dateStart, 'to'=>$dateEnd, 'datetime'=>true);
        }
    }

getSelectCountSql

(

)

Get SQL for get record count

Returns:

Varien_Db_Select

Reimplemented from Varien_Data_Collection_Db.

Definition at line 446 of file Collection.php.

    {
        $countSelect = clone $this->getSelect();
        $countSelect->reset(Zend_Db_Select::ORDER);
        $countSelect->reset(Zend_Db_Select::LIMIT_COUNT);
        $countSelect->reset(Zend_Db_Select::LIMIT_OFFSET);
        $countSelect->reset(Zend_Db_Select::COLUMNS);
        $countSelect->reset(Zend_Db_Select::GROUP);
        $countSelect->reset(Zend_Db_Select::HAVING);
        $countSelect->from("", "count(DISTINCT e.entity_id)");

        $sql = $countSelect->__toString();

        return $sql;
    }

groupByCustomer

(

)

Add group By customer attribute

Returns:

Mage_Reports_Model_Mysql4_Order_Collection

Definition at line 339 of file Collection.php.

    {
        $this->groupByAttribute('customer_id');

        return $this;
    }

joinCustomerName

(

)

Join Customer Name (concat)

Returns:

Mage_Reports_Model_Mysql4_Order_Collection

Definition at line 351 of file Collection.php.

    {
        //TODO: add full name logic
        $this->joinAttribute('firstname', 'customer/firstname', 'customer_id');
        $this->joinAttribute('lastname', 'customer/lastname', 'customer_id');
        $this->getSelect()->from("", array('name' => 'CONCAT(_table_firstname.value," ", _table_lastname.value)'));
        return $this;
    }

orderByCustomerRegistration

(

$

dir = 'desc'

)

Definition at line 440 of file Collection.php.

    {
        $this->addAttributeToSort('customer_id', $dir);
        return $this;
    }

orderByOrdersCount

(

$

dir = 'desc'

)

Definition at line 433 of file Collection.php.

    {
        $this->getSelect()
            ->order("orders_count {$dir}");
        return $this;
    }

orderByTotalAmount

(

$

dir = 'desc'

)

Sort order by total amount

Parameters:

string

$dir

Returns:

Mage_Reports_Model_Mysql4_Order_Collection

Definition at line 426 of file Collection.php.

    {
        $this->getSelect()
            ->order("orders_sum_amount {$dir}");
        return $this;
    }

prepareSummary	(	$	range,
		$	customStart,
		$	customEnd,
		$	isFilter = 0
	)

Definition at line 38 of file Collection.php.

    {

        if ($isFilter==0) {
            $this->addExpressionAttributeToSelect('revenue',
                'SUM({{base_grand_total}}*{{base_to_global_rate}})',
                array('base_grand_total', 'base_to_global_rate'));
        } else{
            $this->addExpressionAttributeToSelect('revenue',
                'SUM({{base_grand_total}})',
                array('base_grand_total'));
        }

        $this->addExpressionAttributeToSelect('quantity', 'COUNT({{attribute}})', 'entity_id')
            ->addExpressionAttributeToSelect('range', $this->_getRangeExpression($range), 'created_at')
            ->addAttributeToFilter('created_at', $this->getDateRange($range, $customStart, $customEnd))
            ->groupByAttribute('range')
            ->addAttributeToFilter('state', array('neq' => Mage_Sales_Model_Order::STATE_CANCELED))
            ->getSelect()->order('range', 'asc');

        return $this;
    }

setDateRange	(	$	from,
		$	to
	)

getting qty count for each order

Reimplemented in Mage_Reports_Model_Mysql4_Customer_Orders_Collection, and Mage_Reports_Model_Mysql4_Customer_Totals_Collection.

Definition at line 218 of file Collection.php.

    {
        $this->_reset()
            ->addAttributeToSelect('*')
            ->addAttributeToFilter('created_at', array('from' => $from, 'to' => $to))
            ->addExpressionAttributeToSelect('orders', 'COUNT(DISTINCT({{entity_id}}))', array('entity_id'))
            ->addAttributeToFilter('state', array('neq' => Mage_Sales_Model_Order::STATE_CANCELED))
            ->getSelect()->group('("*")');

        /**
         * getting qty count for each order
         */

//        $orderItem = Mage::getResourceSingleton('sales/order_item');
//        /* @var $orderItem Mage_Sales_Model_Entity_Quote */
//        $attr = $orderItem->getAttribute('parent_id');
//        /* @var $attr Mage_Eav_Model_Entity_Attribute_Abstract */
//        $attrId = $attr->getAttributeId();
//        $tableName = $attr->getBackend()->getTable();
//
//        $this->getSelect()
//            ->joinLeft(array("order_items" => $tableName),
//                "order_items.parent_id = e.entity_id and order_items.entity_type_id=".$orderItem->getTypeId(), array());
//
//        $attr = $orderItem->getAttribute('qty_ordered');
//        /* @var $attr Mage_Eav_Model_Entity_Attribute_Abstract */
//        $attrId = $attr->getAttributeId();
//        $tableName = $attr->getBackend()->getTable();
//        $fieldName = $attr->getBackend()->isStatic() ? 'qty_ordered' : 'value';
//
//        $this->getSelect()
//            ->joinLeft(array("order_items2" => $tableName),
//                "order_items2.entity_id = `order_items`.entity_id and order_items2.attribute_id = {$attrId}", array())
//            ->from("", array("items" => "sum(order_items2.{$fieldName})"));

        $countSql = clone $this->getSelect();
        $countSql->reset();

        $countSql->from(array("order_items" => $this->getTable('sales/order_item')), array("sum(`order_items2`.`qty_ordered`)"))
            ->joinLeft(array("order_items2" => $this->getTable('sales/order_item')),
                "order_items2.item_id = `order_items`.item_id", array())
            ->where("`order_items`.`order_id` = `e`.`entity_id`")
            ->where("`order_items2`.`parent_item_id` is NULL");

        $this->getSelect()->from("", array("items" => "SUM((".$countSql."))"));

        return $this;
    }

setStoreIds

(

$

storeIds

)

Reimplemented in Mage_Reports_Model_Mysql4_Customer_Orders_Collection, and Mage_Reports_Model_Mysql4_Customer_Totals_Collection.

Definition at line 267 of file Collection.php.

    {
        $vals = array_values($storeIds);
        if (count($storeIds) >= 1 && $vals[0] != '') {
            $this->addAttributeToFilter('store_id', array('in' => (array)$storeIds))
                ->addExpressionAttributeToSelect(
                    'subtotal',
                    'SUM({{base_subtotal}})',
                    array('base_subtotal'))
                ->addExpressionAttributeToSelect(
                    'tax',
                    'SUM({{base_tax_amount}})',
                    array('base_tax_amount'))
                ->addExpressionAttributeToSelect(
                    'shipping',
                    'SUM({{base_shipping_amount}})',
                    array('base_shipping_amount'))
                ->addExpressionAttributeToSelect(
                    'discount',
                    'SUM({{base_discount_amount}})',
                    array('base_discount_amount'))
                ->addExpressionAttributeToSelect(
                    'total',
                    'SUM({{base_grand_total}})',
                    array('base_grand_total'))
                ->addExpressionAttributeToSelect(
                    'invoiced',
                    'SUM({{base_total_paid}})',
                    array('base_total_paid'))
                ->addExpressionAttributeToSelect(
                    'refunded',
                    'SUM({{base_total_refunded}})',
                    array('base_total_refunded'));
        } else {
            $this->addExpressionAttributeToSelect(
                    'subtotal',
                    'SUM({{base_subtotal}}*{{base_to_global_rate}})',
                    array('base_subtotal', 'base_to_global_rate'))
                ->addExpressionAttributeToSelect(
                    'tax',
                    'SUM({{base_tax_amount}}*{{base_to_global_rate}})',
                    array('base_tax_amount', 'base_to_global_rate'))
                ->addExpressionAttributeToSelect(
                    'shipping',
                    'SUM({{base_shipping_amount}}*{{base_to_global_rate}})',
                    array('base_shipping_amount', 'base_to_global_rate'))
                ->addExpressionAttributeToSelect(
                    'discount',
                    'SUM({{base_discount_amount}}*{{base_to_global_rate}})',
                    array('base_discount_amount', 'base_to_global_rate'))
                ->addExpressionAttributeToSelect(
                    'total',
                    'SUM({{base_grand_total}}*{{base_to_global_rate}})',
                    array('base_grand_total', 'base_to_global_rate'))
                ->addExpressionAttributeToSelect(
                    'invoiced',
                    'SUM({{base_total_paid}}*{{base_to_global_rate}})',
                    array('base_total_paid', 'base_to_global_rate'))
                ->addExpressionAttributeToSelect(
                    'refunded',
                    'SUM({{base_total_refunded}}*{{base_to_global_rate}})',
                    array('base_total_refunded', 'base_to_global_rate'));
        }

        return $this;
    }

---

The documentation for this class was generated from the following file:

• app/code/core/Mage/Reports/Model/Mysql4/Order/Collection.php