Woocommerce -> MySQL -> VOrdersList

xx

alter view vOrdersList as
-- 20180807 LSR
-- Added posthaste edit A link
-- 20180802 1019
select concat('<a href="invoice.php?order=',ID,'&copyinvoice=N" target="_blank">OL', ID, '</a>') as NewInv
,concat('<a href="invoiceold.php?order=',ID,'" target="_blank">OL', ID, '</a>') as OldInv
,post_date
,order_total
,payment_method_title as pay_method
,post_status
,concat('<a href="mailto:'
,coalesce(billing_email, 'xxx@xxx.xxx')
,'?Subject=MMMMM FFFF MMMM Order #'  -- Company name here
,ID
,'&body=Thank%20you%20very%20much%20for%20your%20order%20which%20is%20with%20Post%20Haste.'
,'%20%20An%20invoice%20is%20included%20in%20the%20package%0D%0A%0D%0A'
,coalesce(ph_httpurlencoded, 'xxxxx')
,'%0D%0A%0D%0A---%0D%0A%0D%0A'
,'We%20appreciate%20your%20business.'
,'%20If%20you%20like%20our%20products%20%26%20prices%20please%2C%20spread%20the%20word%0D%0A%0D%0A---'
,'%0D%0A%0D%0ARegards%20%26%20thanks%20again%20for%20shopping%20with%20us%0D%0A%0D%0A'
,'LLLLLLl' -- Sender first name here
,'%20'
,'RRRR' -- Sender last name here
,'%0D%0A%0D%0A'
,'MMMMM%20FFF%20MMMMM%20LLLLL' -- URL encoded company name here
,'%0D%0A'
,'www.xxxxx.co.nz' -- URL encoded www...nz domain name here
,'" target="_top">Send Mail</a>') as On_Ship
,ph_href
,concat('<a href="posthasteedit.php?order=',ID, '" target="_blank">', ID, '</a>') as ph_edit
,concat(billing_first_name, ' ', billing_last_name) as who
-- $$ summaries from order items meta
,soim.`sum_oim->line_item->_line_total` as oim_Net
,soim.`sum_oim->shipping->cost` as oim_Ship
,soim.`sum_oim->fee->_line_total` as oim_PPal
,soim.`sum_oim->line_item->_line_tax`
+ soim.`sum_oim->tax->shipping_tax_amount`
+ soim.`sum_oim->fee->_line_tax` as oim_GST 
,soim.`sum_oim->line_item->_line_total`
+ soim.`sum_oim->shipping->cost`
+ soim.`sum_oim->fee->_line_total`
+ `sum_oim->line_item->_line_tax`
+ soim.`sum_oim->tax->shipping_tax_amount`
+ soim.`sum_oim->fee->_line_tax` as oim_Total
-- and finally a sanity check to help find some of those
-- annoying GST rounding descrepancies
,(soim.`sum_oim->line_item->_line_total`
+ soim.`sum_oim->shipping->cost`
+ soim.`sum_oim->fee->_line_total`
+ `sum_oim->line_item->_line_tax`
+ soim.`sum_oim->tax->shipping_tax_amount`
+ soim.`sum_oim->fee->_line_tax`) - cast(order_total as DECIMAL(12,4)) as oimT_less_omT
from vOrderMetaData omd
join vOrderItemMetaSUM soim on soim.order_id = omd.ID
order by ID desc;

xx