Woocommerce -> MySql -> vOrderSummaryByMonth

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