magento order report

列出区间内每天的总数据:

比如 2015年11月1日到11月30日的, status 为 complete 或者 processing 的.
就列出:

  • 日期(Period)
  • 当天总共多少个订单(Orders)
  • 总共多少个 item (Sales Items) - 这个是总数量,比如2个订单,订单A有产品甲3件,产品乙2件,然后订单B有产品甲1件,产品丙5件,那么总数量就是 3+2+1+5 = 11, 也就是忽略产品是什么,只管把 qty 加总
  • 订单总额(Sales Total) - 也就是 order 的 base_grand_total 的值
  • 发票总额 (Invoiced) - 和订单总额永远一样,因为 kaari 支付是直接 invoice 的.所以把订单总额的数字直接写这里就行
  • 退款总额(Refunded) - 获取 order 的 base_total_refunded 的值
  • 总税额 (Sales Tax) - 获取 order 的 base_tax_amount 的值
  • 总运费额 (Sales Shipping) - 获取 order 的 base_shipping_amount 的值
  • 总打折额度 (Sales Discount) - 获取 order 的 base_discount_amount 的值
  • 总取消额度 (Canceled) - 获取 order 的 base_total_canceled 的值

 

核心代码:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
 protected function _prepareCollection()
{
$filterData = $this->getFilterData();

// print_r($filterData);die;
if ($filterData->getData('from') == null || $filterData->getData('to') == null) {
$this->setCountTotals(false);
$this->setCountSubTotals(false);
return parent::_prepareCollection();
}
$this->setCollection(Mage::getResourceModel($this->_getCollectionClass()));
$storeIds = $this->_getStoreIds();
$orderStatuses = $filterData->getData('order_statuses');
if (is_array($orderStatuses)) {
if (count($orderStatuses) == 1 && strpos($orderStatuses[0],',')!== false) {
$filterData->setData('order_statuses', explode(',',$orderStatuses[0]));
}
$this->getCollection()->addFieldToFilter('status',array('in'=>$filterData->getData('order_statuses')));
}
$this->getCollection()->addFieldToFilter('store_id',array('in'=>$storeIds));
$this->getCollection()->addFieldToFilter('created_at', array(
'from' => $filterData->getData('from').' 00:00:00',
'to' => $filterData->getData('to').' 23:59:59',
'date' => true, // specifies conversion of comparison values
));

$kaari = $filterData->getData('kaari');
$kaariFilter = false;
if($kaari == 'exclude'){
$kaariFilter = 'p.method != "cashkaari"';
}elseif ($kaari == 'only'){
$kaariFilter = 'p.method = "cashkaari"';
}
if($kaariFilter){
$table = Mage::getSingleton('core/resource')->getTableName("sales_flat_order_payment");
$this->getCollection()->getSelect()
->where($kaariFilter)
->join(array('p' => $table), 'main_table.entity_id = p.parent_id', array(
'payment_method' => 'method'
));

}

$defined_magento_tz = Mage::getStoreConfig('general/locale/timezone',0);
$gmt = new DateTimeZone('GMT');
$tz = new DateTimeZone($defined_magento_tz);
$tz_diff = $tz->getOffset(new DateTime('now',$gmt))/3600;

$mysql_tz = (($tz_diff < 0) ? '-' : '+') . abs($tz_diff) . ':00';
$this->getCollection()->getSelect()
->reset(Zend_Db_Select::COLUMNS)
->columns( 'SUM(IFNULL(main_table.total_qty_ordered,0)) AS total_qty_ordered' )
->columns( 'SUM(IFNULL(main_table.base_grand_total,0)) AS base_grand_total' )
->columns( 'SUM(IFNULL(main_table.base_total_refunded,0)) AS base_total_refunded' )
->columns( 'SUM(IFNULL(main_table.base_tax_amount,0)) AS base_tax_amount' )
->columns( 'SUM(IFNULL(main_table.base_shipping_amount,0)) AS base_shipping_amount' )
->columns( 'SUM(IFNULL(main_table.base_discount_amount,0)) AS base_discount_amount' )
->columns( 'SUM(IFNULL(main_table.base_total_canceled,0)) AS base_total_canceled' )
->columns( 'COUNT(*) AS orders_count' )
->columns( 'DATE_FORMAT(created_at, "%Y-%m-%d") AS period' )
->columns( "CONVERT_TZ(created_at,'+0:00','".$mysql_tz."') AS created_at" )
->group( 'DATE_FORMAT(created_at, "%d-%m-%y")' )
->order('created_at');

// echo $this->getCollection()->getSelect();
if ($this->_isExport) {
$this->setCollection($this->getCollection());
return $this;
}
if ($this->getCountSubTotals()) {
$this->getSubTotals();
}
if ($this->getCountTotals()) {
$totalsCollection = $this->getCollection();
$data = array(
'total_qty_ordered' => 0,
'base_grand_total' => 0,
'base_total_refunded' => 0,
'base_tax_amount' => 0,
'base_shipping_amount' => 0,
'base_discount_amount' => 0,
'base_total_canceled' => 0,
'orders_count' => 0,
);
$totalItem = new Mage_Sales_Model_Order;
foreach ($totalsCollection->getItems() as $item) {
$totalItem->setHasItems(true);
foreach ($data as $key=>$value){
$data[$key] += $item->getData($key);
}
}
if($totalItem->getHasItems()){
$data['period'] = '';
foreach ($data as $key=>$value){
if($value == 0){
$data[$key] = '0.0000';
}
}
$totalItem->setData($data);
// print_r($data);
$this->setTotals($totalItem);
}else{
$this->setCountTotals(false);
}

}

return parent::_prepareCollection();
}

 

 

关于Total部分,本来打算用mysql的WITH ROLLUP来做,但是magento 提供的ORM不支持,只能foreach累加。

并且ROLLUO不能与ORDER BY连用。因为Order与ROLLUO是相斥的。

坚持原创技术分享,您的支持将鼓励我继续创作!