xx
alter view vOrderSummaryByMonth
as
-- 20190217 LSR
-- Add refund into cancelled total
-- Exclude refund from incompleted
-- 20180829 LSR
-- Split by Trademe not trademe
-- 20180803 0907 LSR
-- Order counts & sums by month
-- 20181128 1226 LSR
-- Added cancel_oimnet
-- Updated incomp_oimnet to be all except completed / cancelled
-- Removed incomp_om_gross
select max(LAST_DAY(concat(year(post_date), '-', month(post_date), '-','1'))) as month
,vOIMS.trademeflag
-- counts
,sum(case when post_status like '%cancel%'
or post_status like '%refund%' then 1 else 0 end ) as fail
,sum(case when post_status like '%completed' then 1 else 0 end) as complete
,sum(case when post_status like '%hold%' then 1 else 0 end) as hold
,sum(case when post_status like '%process%' then 1 else 0 end) as processing
,sum(case when post_status not like '%cancel%'
and post_status not like '%refund%'
and post_status not like '%completed'
and post_status not like '%hold%'
and post_status not like '%process%' then 1 else 0 end) as UNKNOWN
-- $ from orderitemmeta
,sum(case when post_status like '%completed' then
`sum_oim->line_item->_line_total` else 0 end) as 'comp_oimnet'
,sum(case when post_status like '%completed' then
`sum_oim->shipping->cost` else 0 end) as 'comp_ship'
,sum(case when post_status like '%completed' then
`sum_oim->fee->_line_total` else 0 end) as 'comp_fee'
,sum(case when post_status like '%completed' then
`sum_oim->line_item->_line_tax` else 0 end) as 'comp_tax'
,sum(case when post_status like '%completed' then
`sum_oim->tax->shipping_tax_amount` else 0 end) as 'comp_shiptax'
,sum(case when post_status like '%completed' then
`sum_oim->fee->_line_tax` else 0 end) as 'comp_feetax'
-- $ from order meta & tax from orderitemmeta
,sum(case when post_status like '%completed' then
coalesce(order_total,0) else 0 end) as completed_om_gross
,sum(case when post_status like '%completed' then
coalesce(order_total,0)
- (coalesce(order_tax,0) + coalesce(order_shipping_tax,0)) else 0 end) as completed_om_netofgst
,sum(case when post_status not like '%completed'
and post_status not like '%cancelled'
and post_status not like '%refund%' then
`sum_oim->line_item->_line_total` else 0 end) as 'incomp_oimnet'
,sum(case when post_status like '%cancel%'
or post_status like '%refund%' then
`sum_oim->line_item->_line_total` else 0 end) as 'cancel_oimnet'
from vOrderMetaData om
join vOrderItemMetaSUM vOIMS on vOIMS.order_id = om.ID
group by concat(year(post_date), '-', lpad(month(post_date),2,'0'))
,vOIMS.trademeflag;
xx