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