Woocommerce Product Addons Configuration notes

Important Tip #1: Make the Group name as generic as Possible so it can be used across as many products as possible:

  • Each different group is a separate meta_key in
    woocommerce_order_itemmeta so the more of these different options the more back end work

Tip#2: If the same options are across multiple products be sure to name the group and options identically, (this helps with later reporting)

  • as for Tip 1, reduces back end work

Woocommerce: non numeric value encountered in .. class-wc-shipping-flat-rate-boxes.php

After PHP 7 upgrade

Warning: A non-numeric value encountered in /home/mecha4ma/public_html/wp-content/plugins/woocommerce-shipping-flat-rate-boxes/includes/class-wc-shipping-flat-rate-boxes.php on line 344

Line 344:

// add fee
$cost += $this->get_fee( $this->fee, $package->value );

Change to:

        // add fee
$cost += (float)$this->get_fee( $this->fee, $package->value );

Woocommerce – Editing Orders

Paypal orders are sorta locked

xx

— wp1_postmeta

update wp1_postmeta
set meta_value = ‘0.00’
where post_id = 3387
and meta_key = ‘_order_shipping’;

update wp1_postmeta
set meta_value = ‘0.00’
where post_id = 3387
and meta_key = ‘_order_shipping_tax’;

update wp1_postmeta
set meta_value = ‘10.36’
where post_id = 3387
and meta_key = ‘_order_total’;

— wp1_woocommerce_order_itemmeta

update wp1_woocommerce_order_itemmeta oim
join wp1_woocommerce_order_items oi on oi.order_item_id = oim.order_item_id
set meta_value = ‘0.00’
where oi.order_id = 3387
and oi.order_item_type = ‘shipping’
and meta_key = ‘cost’;

update wp1_woocommerce_order_itemmeta oim
join wp1_woocommerce_order_items oi on oi.order_item_id = oim.order_item_id
set meta_value = ‘ a:1:{i:1;s:6:”0.00″;}’
where oi.order_id = 3387
and oi.order_item_type = ‘shipping’
and meta_key = ‘taxes’;

xx

Woocommerce -> Sales by Product analysis options

Potential dashboard / tabular reports for Woocommerce product to sales analysis


Top view = ‘Sales by product’

  • Product centric view
    • Columns:
      • General Meta:
        • Sku
        • Name
        • Category count = how many categories product appears in
        • Category, (1st lowest only)
      • Sales
        • Net $ total
        • Net $ per prev 12 months
        • Net $ per prev months 13-24
        • Net $ prev months 25 and earlier
        • Net $ per month last 13 months
        • Unit $ per month last 13 months (where sold by length is per meter)
        • Unit $ per year (where sold by length is per meter)
        • Qty $ per month last 13 months (where sold by length is meters)
        • Qty $ per year (where sold by length is meters)
        • Unit Min $
        • Unit Max $
        • Unit Avg $
      • Costs
        • Now: Cost per unit or as close as we can get
        • Later:
          • Show purchasing history
          • Reconcile Stock levels vs sold vs purchased to identify gaps

To consider: version of ‘Sales by Product’ but is ‘Sales by Length’ for products sold per custom lengths


Top view = ‘Sales by category‘:

  • Product category centric view
    • Columns:
      • Category
        • Issue: Which category level? Highest or lowest?
        • Now:
          • Lowest
      • Net $ per month last 13 months
      • Net $ per year
      • Net $ total
    • Issue: products in multiple categories, and currently no mechanism to identify primary category
      • Now:
        • show the product in each category, will skew any totals but will assume is acceptable for now
        • Include smaller table showing products in multiple categories so reminded they exist
      • Later:
        • Add method to identify primary category and use that only per product
    • Click on category:
      • Takes us to Sales by product filtered to target category

View for creating a monthly ‘snapshot’ into a table

Note, used https://codebeautify.org/sqlformatter thank you very much

alter VIEW `vProductSales` AS 
-- 20181110 LSR
-- Initial version
-- 20181111 Added sku, stock (on hand)
select 
  now() AS `snapped`, 
  `pr`.`ID` AS `ID`,
  pm_sku,
  `oim`.`order_item_type` AS `order_item_type`, 
  coalesce(
    `pr`.`post_title`, `oim`.`order_item_name`
  ) AS `coalesce(pr.post_title, order_item_name)`,
  pm_stock,
  sum(
    `oim`.`oim->line_item->_line_total`
  ) AS `net_forever`, 
  round(
    sum(
      coalesce(
        (
          (
            `oim`.`oim->line_item->length` * `oim`.`oim->line_item->_qty`
          ) / 1000
        ), 
        `oim`.`oim->line_item->_qty`
      )
    ), 
    2
  ) AS `qty_forever`, 
  sum(
    (
      case when (`om`.`monthdiff` = 0) then `oim`.`oim->line_item->_line_total` else 0 end
    )
  ) AS `net_current_mth`, 
  sum(
    (
      case when (`om`.`monthdiff` = 1) then `oim`.`oim->line_item->_line_total` else 0 end
    )
  ) AS `net_prev_m1`, 
  sum(
    (
      case when (`om`.`monthdiff` = 2) then `oim`.`oim->line_item->_line_total` else 0 end
    )
  ) AS `net_prev_m2`, 
  sum(
    (
      case when (`om`.`monthdiff` = 3) then `oim`.`oim->line_item->_line_total` else 0 end
    )
  ) AS `net_prev_m3`, 
  sum(
    (
      case when (`om`.`monthdiff` = 4) then `oim`.`oim->line_item->_line_total` else 0 end
    )
  ) AS `net_prev_m4`, 
  sum(
    (
      case when (`om`.`monthdiff` = 5) then `oim`.`oim->line_item->_line_total` else 0 end
    )
  ) AS `net_prev_m5`, 
  sum(
    (
      case when (`om`.`monthdiff` = 6) then `oim`.`oim->line_item->_line_total` else 0 end
    )
  ) AS `net_prev_m6`, 
  sum(
    (
      case when (`om`.`monthdiff` = 7) then `oim`.`oim->line_item->_line_total` else 0 end
    )
  ) AS `net_prev_m7`, 
  sum(
    (
      case when (`om`.`monthdiff` = 8) then `oim`.`oim->line_item->_line_total` else 0 end
    )
  ) AS `net_prev_m8`, 
  sum(
    (
      case when (`om`.`monthdiff` = 9) then `oim`.`oim->line_item->_line_total` else 0 end
    )
  ) AS `net_prev_m9`, 
  sum(
    (
      case when (`om`.`monthdiff` = 10) then `oim`.`oim->line_item->_line_total` else 0 end
    )
  ) AS `net_prev_m10`, 
  sum(
    (
      case when (`om`.`monthdiff` = 11) then `oim`.`oim->line_item->_line_total` else 0 end
    )
  ) AS `net_prev_m11`, 
  sum(
    (
      case when (`om`.`monthdiff` = 12) then `oim`.`oim->line_item->_line_total` else 0 end
    )
  ) AS `net_prev_m12`, 
  sum(
    (
      case when (
        `om`.`monthdiff` between 1 
        and 12
      ) then `oim`.`oim->line_item->_line_total` else 0 end
    )
  ) AS `net_prev_Y1`, 
  sum(
    (
      case when (
        `om`.`monthdiff` between 13 
        and 24
      ) then `oim`.`oim->line_item->_line_total` else 0 end
    )
  ) AS `net_prev_Y2`, 
  sum(
    (
      case when (`om`.`monthdiff` > 24) then `oim`.`oim->line_item->_line_total` else 0 end
    )
  ) AS `net_prev_Y3+`, 
  round(
    sum(
      (
        case when (`om`.`monthdiff` = 0) then coalesce(
          (
            (
              `oim`.`oim->line_item->length` * `oim`.`oim->line_item->_qty`
            ) / 1000
          ), 
          `oim`.`oim->line_item->_qty`
        ) else 0 end
      )
    ), 
    2
  ) AS `qty_mnow`, 
  round(
    sum(
      (
        case when (`om`.`monthdiff` = 1) then coalesce(
          (
            (
              `oim`.`oim->line_item->length` * `oim`.`oim->line_item->_qty`
            ) / 1000
          ), 
          `oim`.`oim->line_item->_qty`
        ) else 0 end
      )
    ), 
    2
  ) AS `qty_prev_m1`, 
  round(
    sum(
      (
        case when (`om`.`monthdiff` = 2) then coalesce(
          (
            (
              `oim`.`oim->line_item->length` * `oim`.`oim->line_item->_qty`
            ) / 1000
          ), 
          `oim`.`oim->line_item->_qty`
        ) else 0 end
      )
    ), 
    2
  ) AS `qty_prev_m2`, 
  round(
    sum(
      (
        case when (`om`.`monthdiff` = 3) then coalesce(
          (
            (
              `oim`.`oim->line_item->length` * `oim`.`oim->line_item->_qty`
            ) / 1000
          ), 
          `oim`.`oim->line_item->_qty`
        ) else 0 end
      )
    ), 
    2
  ) AS `qty_prev_m3`, 
  round(
    sum(
      (
        case when (`om`.`monthdiff` = 4) then coalesce(
          (
            (
              `oim`.`oim->line_item->length` * `oim`.`oim->line_item->_qty`
            ) / 1000
          ), 
          `oim`.`oim->line_item->_qty`
        ) else 0 end
      )
    ), 
    2
  ) AS `qty_prev_m4`, 
  round(
    sum(
      (
        case when (`om`.`monthdiff` = 5) then coalesce(
          (
            (
              `oim`.`oim->line_item->length` * `oim`.`oim->line_item->_qty`
            ) / 1000
          ), 
          `oim`.`oim->line_item->_qty`
        ) else 0 end
      )
    ), 
    2
  ) AS `qty_prev_m5`, 
  round(
    sum(
      (
        case when (`om`.`monthdiff` = 6) then coalesce(
          (
            (
              `oim`.`oim->line_item->length` * `oim`.`oim->line_item->_qty`
            ) / 1000
          ), 
          `oim`.`oim->line_item->_qty`
        ) else 0 end
      )
    ), 
    2
  ) AS `qty_prev_m6`, 
  round(
    sum(
      (
        case when (`om`.`monthdiff` = 7) then coalesce(
          (
            (
              `oim`.`oim->line_item->length` * `oim`.`oim->line_item->_qty`
            ) / 1000
          ), 
          `oim`.`oim->line_item->_qty`
        ) else 0 end
      )
    ), 
    2
  ) AS `qty_prev_m7`, 
  round(
    sum(
      (
        case when (`om`.`monthdiff` = 8) then coalesce(
          (
            (
              `oim`.`oim->line_item->length` * `oim`.`oim->line_item->_qty`
            ) / 1000
          ), 
          `oim`.`oim->line_item->_qty`
        ) else 0 end
      )
    ), 
    2
  ) AS `qty_prev_m8`, 
  round(
    sum(
      (
        case when (`om`.`monthdiff` = 9) then coalesce(
          (
            (
              `oim`.`oim->line_item->length` * `oim`.`oim->line_item->_qty`
            ) / 1000
          ), 
          `oim`.`oim->line_item->_qty`
        ) else 0 end
      )
    ), 
    2
  ) AS `qty_prev_m9`, 
  round(
    sum(
      (
        case when (`om`.`monthdiff` = 10) then coalesce(
          (
            (
              `oim`.`oim->line_item->length` * `oim`.`oim->line_item->_qty`
            ) / 1000
          ), 
          `oim`.`oim->line_item->_qty`
        ) else 0 end
      )
    ), 
    2
  ) AS `qty_prev_m10`, 
  round(
    sum(
      (
        case when (`om`.`monthdiff` = 11) then coalesce(
          (
            (
              `oim`.`oim->line_item->length` * `oim`.`oim->line_item->_qty`
            ) / 1000
          ), 
          `oim`.`oim->line_item->_qty`
        ) else 0 end
      )
    ), 
    2
  ) AS `qty_prev_m11`, 
  round(
    sum(
      (
        case when (`om`.`monthdiff` = 12) then coalesce(
          (
            (
              `oim`.`oim->line_item->length` * `oim`.`oim->line_item->_qty`
            ) / 1000
          ), 
          `oim`.`oim->line_item->_qty`
        ) else 0 end
      )
    ), 
    2
  ) AS `qty_prev_m12`, 
  round(
    sum(
      (
        case when (
          `om`.`monthdiff` between 1 
          and 12
        ) then coalesce(
          (
            (
              `oim`.`oim->line_item->length` * `oim`.`oim->line_item->_qty`
            ) / 1000
          ), 
          `oim`.`oim->line_item->_qty`
        ) else 0 end
      )
    ), 
    2
  ) AS `qty_prev_Y1`, 
  round(
    sum(
      (
        case when (
          `om`.`monthdiff` between 13 
          and 24
        ) then coalesce(
          (
            (
              `oim`.`oim->line_item->length` * `oim`.`oim->line_item->_qty`
            ) / 1000
          ), 
          `oim`.`oim->line_item->_qty`
        ) else 0 end
      )
    ), 
    2
  ) AS `qty_prev_Y2`, 
  round(
    sum(
      (
        case when (`om`.`monthdiff` > 24) then coalesce(
          (
            (
              `oim`.`oim->line_item->length` * `oim`.`oim->line_item->_qty`
            ) / 1000
          ), 
          `oim`.`oim->line_item->_qty`
        ) else 0 end
      )
    ), 
    2
  ) AS `qty_prev_Y3+` 
from 
  (
    (
      `vProductMetaData` `pr` 
      left join `vOrderItemMetaDataV3` `oim` on(
        (
          `pr`.`ID` = `oim`.`oim->line_item->_product_id`
        )
      )
    ) 
    left join `vOrderMetaData` `om` on(
      (`om`.`ID` = `oim`.`order_id`)
    )
  ) 
where 
  (
    (1 = 1) 
    and (
      `oim`.`order_item_type` = 'line_item'
    ) 
    and (
      `om`.`post_status` like '%complete%'
    )
  ) 
group by 
  `oim`.`oim->line_item->_product_id`, 
  coalesce(
    `pr`.`post_title`, `oim`.`order_item_name`
  );

 

xx

Woocommerce -> MySql -> vTaxonomyChained vTaxonomyPaths vProductCatsSummary

  • Product categories have parents…
  • Products can be in more than 1 category

xx

 create view vProductCatsSummary
 as
 select ID
	,count(*) as cat_cnt
    ,min(tt.term_taxonomy_id) as cat_min
    ,max(tt.term_taxonomy_id) as cat_max
    ,(	select group_concat( tex.name separator ' | ' )
		from wp1_term_relationships trx
		JOIN wp1_term_taxonomy ttx ON trx.term_taxonomy_id = ttx.term_taxonomy_id
		JOIN wp1_terms tex ON ttx.term_id = tex.term_id
        where trx.object_id = ID
        and ttx.taxonomy = 'product_cat') as cats
from wp1_posts po
JOIN wp1_term_relationships tr ON po.ID = tr.object_id
join wp1_term_taxonomy tt on tr.term_taxonomy_id = tt.term_taxonomy_id
where  po.post_type = 'product'
	aND tt.taxonomy = 'product_cat'
group by ID;

xx

alter view vTaxonomyPaths
 as
 select tt_id
	,concat(
		case when tt3_id is not null then concat(te3_name, ' -> ') else '' end
        ,case when tt2_id is not null then concat(te2_name, ' -> ') else '' end
        ,case when tt1_id is not null then concat(te1_name, ' -> ') else '' end
        ,te_name) as cats
	,concat(
		case when tt3_id is not null then concat(te3_slug, '/') else '' end
        ,case when tt2_id is not null then concat(te2_slug, '/') else '' end
        ,case when tt1_id is not null then concat(te1_slug, '/') else '' end
        ,te_slug) as path
 from vTaxonomyChainedUp
where tt_taxonomy = 'product_cat';

xx

 create view vTaxonomyChainedUp
 as
 -- 20180808 LSR
 -- product categories
 -- daisychained up +3 levels via parents
 select tt0.term_taxonomy_id as tt_id
	,tt0.term_id as te_id
    ,tt0.taxonomy as tt_taxonomy
    ,tt0.description as tt_desc
    ,te0.name as te_name
    ,te0.slug as te_slug
--
	,tt1.term_taxonomy_id as tt1_id
	,tt1.term_id as te1_id
    ,tt1.taxonomy as tt1_taxonomy
    ,tt1.description as tt1_desc
    ,te1.name as te1_name
    ,te1.slug as te1_slug
--
	,tt2.term_taxonomy_id as tt2_id
	,tt2.term_id as te2_id
    ,tt2.taxonomy as tt2_taxonomy
    ,tt2.description as tt2_desc
    ,te2.name as te2_name
    ,te2.slug as te2_slug
--
	,tt3.term_taxonomy_id as tt3_id
	,tt3.term_id as te3_id
    ,tt3.taxonomy as tt3_taxonomy
    ,tt3.description as tt3_desc
    ,te3.name as te3_name
    ,te3.slug as te3_slug
--
 from  wp1_term_taxonomy tt0
 JOIN wp1_terms te0 ON tt0.term_id = te0.term_id
--
 left join wp1_term_taxonomy tt1 on tt1.term_taxonomy_id = tt0.parent
 left JOIN wp1_terms te1 ON tt1.term_id = te1.term_id
--
 left join wp1_term_taxonomy tt2 on tt2.term_taxonomy_id = tt1.parent
 left JOIN wp1_terms te2 ON tt2.term_id = te2.term_id
--
 left join wp1_term_taxonomy tt3 on tt3.term_taxonomy_id = tt2.parent
 left JOIN wp1_terms te3 ON tt3.term_id = te3.term_id;

xx

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

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

Woocommerce -> MySql -> vOrderAddresses

xx

Todo: change from  br to p tags

alter view vOrderAddresses
as
-- 20180822 LSR
-- Added Post_excerpt first 20 chars(Customer comment added at checkout)
-- 20180807 LSR
-- Added maint <a> link
-- 20180805 LSR
-- Original
select  ID
    ,concat('<a href="addressedit.php?order=',ID,'" target="_blank">OL', ID, '</a>') as edit_shipping_href
	,case when concat(
			coalesce(billing_first_name, '')
			,coalesce(billing_last_name, '')
			,coalesce(billing_company, '')
			,coalesce(billing_address_1, '')
			,coalesce(billing_address_2, '')
			,coalesce(billing_city, '')
			,coalesce(billing_postcode, '')
			,coalesce(billing_state, '')
			,coalesce(billing_country), '')
		<> concat(
			coalesce(shipping_first_name, '')
			,coalesce(shipping_last_name, '')
			,coalesce(shipping_company, '')
            ,coalesce(shipping_address_1, '')
            ,coalesce(shipping_address_2, '')
            ,coalesce(shipping_city, '')
            ,coalesce(shipping_postcode, '')
            ,coalesce(shipping_state, '')
            ,coalesce(shipping_country, '')) then 1 else 0 end as hasshiptoaddress
	,concat(
		case when coalesce(billing_first_name, '') <> '' then concat(billing_first_name, ' ') else '' end
		,case when coalesce(billing_last_name, '') <> '' then concat(billing_last_name, '</br>') else '</br>' end
		,case when coalesce(billing_company, '') <> '' then concat(billing_company, '</br>') else '' end
		,case when coalesce(billing_address_1, '') <> '' then concat(billing_address_1, '</br>') else '' end
		,case when coalesce(billing_address_2, '') <> '' then concat(billing_address_2, '</br>') else '' end
		,case when coalesce(billing_city, '') <> '' then concat(billing_city, ' ') else '' end
		,case when coalesce(shipping_postcode, '') <> '' then concat(shipping_postcode, '</br>') else '</br>' end
        -- no state for now
		,case when (coalesce(billing_country, '') <> ''
			and billing_country not like 'nz'
			and billing_country not like '%zealand') then billing_country else '' end) as billingaddresshtml
	,concat(
		case when coalesce(shipping_first_name, '') <> '' then concat(shipping_first_name, ' ') else '' end
		,case when coalesce(shipping_last_name, '') <> '' then concat(shipping_last_name, '</br>') else '</br>' end
		,case when coalesce(shipping_company, '') <> '' then concat(shipping_company, '</br>') else '' end
		,case when coalesce(shipping_address_1, '') <> '' then concat(shipping_address_1, '</br>') else '' end
		,case when coalesce(shipping_address_2, '') <> '' then concat(shipping_address_2, '</br>') else '' end
		,case when coalesce(shipping_city, '') <> '' then concat(shipping_city, ' ') else '' end
		,case when coalesce(shipping_postcode, '') <> '' then concat(shipping_postcode, '</br>') else '</br>' end
        -- no state for now
		,case when (coalesce(shipping_country, '') <> ''
			and shipping_country not like 'nz'
			and shipping_country not like '%zealand') then shipping_country else '' end) as shippingaddresshtml
	,concat(left(post_excerpt, 20), case when char_length(post_excerpt) > 20 then '...' else '' end) as post_excerpt20
from vOrderMetaData;

xx

Woocommerce -> MySQL -> vProductMetaData

xx

alter view vProductMetaData
as
	/*
	Author: LSR 20180402
	Woocommerce products are stored as Posts
	Products have multiple attributes.
	- Each attribute has it's own record in table ..postmeta
	- so if a product has 5 attributes = at least 5 records in this table for that product
	- Custom attributes generated by custom product options/plugins
	  also generate additional records in this table
	This View flatterns those attributes 1 one record per product:
	- returns 1 record per post_type = 'product'
	- each attribute is in its own column

	Assumes integrity in that an attribute as identified by meta_key exists
	only once per parent Product
	*/
select po.ID, po.post_title
	,max(case when meta_key = '_area' then meta_value else null end) as pm_area
	,max(case when meta_key = '_backorders' then meta_value else null end) as pm_backorders
	,max(case when meta_key = '_crosssell_ids' then meta_value else null end) as pm_crosssell_ids
	,max(case when meta_key = '_default_attributes' then meta_value else null end) as pm_default_attributes
	,max(case when meta_key = '_downloadable' then meta_value else null end) as pm_downloadable
	,max(case when meta_key = '_edit_last' then meta_value else null end) as pm_edit_last
	,max(case when meta_key = '_edit_lock' then meta_value else null end) as pm_edit_lock
	,max(case when meta_key = '_featured' then meta_value else null end) as pm_featured
	,max(case when meta_key = '_height' then meta_value else null end) as pm_height
	,max(case when meta_key = '_length' then meta_value else null end) as pm_length
	,max(case when meta_key = '_m4m_cost' then meta_value else null end) as pm_m4m_cost
	,max(case when meta_key = '_m4m_ok' then meta_value else null end) as pm_m4m_ok
	,max(case when meta_key = '_m4m_pnp_cost' then meta_value else null end) as pm_m4m_pnp_cost
	,max(case when meta_key = '_m4m_purchase_note' then meta_value else null end) as pm_m4m_purchase_note
	,max(case when meta_key = '_m4m_qty_bought' then meta_value else null end) as pm_m4m_qty_bought
	,max(case when meta_key = '_m4m_qty_inv' then meta_value else null end) as pm_m4m_qty_inv
	,max(case when meta_key = '_m4m_repl_cost' then meta_value else null end) as pm_m4m_repl_cost
	,max(case when meta_key = '_m4m_shrinkage' then meta_value else null end) as pm_m4m_shrinkage
	,max(case when meta_key = '_m4m_stocktake20180331' then meta_value else null end) as pm_m4m_stocktake20180331
	,max(case when meta_key = '_manage_stock' then meta_value else null end) as pm_manage_stock
	,max(case when meta_key = '_max_price_variation_id' then meta_value else null end) as pm_max_price_variation_id
	,max(case when meta_key = '_max_regular_price_variation_id' then meta_value else null end) as pm_max_regular_price_variation_id
	,max(case when meta_key = '_max_sale_price_variation_id' then meta_value else null end) as pm_max_sale_price_variation_id
	,max(case when meta_key = '_max_variation_price' then meta_value else null end) as pm_max_variation_price
	,max(case when meta_key = '_max_variation_regular_price' then meta_value else null end) as pm_max_variation_regular_price
	,max(case when meta_key = '_max_variation_sale_price' then meta_value else null end) as pm_max_variation_sale_price
	,max(case when meta_key = '_min_price_variation_id' then meta_value else null end) as pm_min_price_variation_id
	,max(case when meta_key = '_min_regular_price_variation_id' then meta_value else null end) as pm_min_regular_price_variation_id
	,max(case when meta_key = '_min_sale_price_variation_id' then meta_value else null end) as pm_min_sale_price_variation_id
	,max(case when meta_key = '_min_variation_price' then meta_value else null end) as pm_min_variation_price
	,max(case when meta_key = '_min_variation_regular_price' then meta_value else null end) as pm_min_variation_regular_price
	,max(case when meta_key = '_min_variation_sale_price' then meta_value else null end) as pm_min_variation_sale_price
	,max(case when meta_key = '_price' then meta_value else null end) as pm_price
	,max(case when meta_key = '_product_addons' then meta_value else null end) as pm_product_addons
	,max(case when meta_key = '_product_addons_exclude_global' then meta_value else null end) as pm_product_addons_exclude_global
	,max(case when meta_key = '_product_attributes' then meta_value else null end) as pm_product_attributes
	,max(case when meta_key = '_product_image_gallery' then meta_value else null end) as pm_product_image_gallery
	,max(case when meta_key = '_product_version' then meta_value else null end) as pm_product_version
	,max(case when meta_key = '_psad_featured_order' then meta_value else null end) as pm_psad_featured_order
	,max(case when meta_key = '_psad_onsale_order' then meta_value else null end) as pm_psad_onsale_order
	,max(case when meta_key = '_purchase_note' then meta_value else null end) as pm_purchase_note
	,max(case when meta_key = '_regular_price' then meta_value else null end) as pm_regular_price
	,max(case when meta_key = '_sale_price' then meta_value else null end) as pm_sale_price
	,max(case when meta_key = '_sale_price_dates_from' then meta_value else null end) as pm_sale_price_dates_from
	,max(case when meta_key = '_sale_price_dates_to' then meta_value else null end) as pm_sale_price_dates_to
	,max(case when meta_key = '_sku' then meta_value else null end) as pm_sku
	,max(case when meta_key = '_sold_individually' then meta_value else null end) as pm_sold_individually
	,max(case when meta_key = '_stock' then meta_value else null end) as pm_stock
	,max(case when meta_key = '_stock_status' then meta_value else null end) as pm_stock_status
	,max(case when meta_key = '_tax_class' then meta_value else null end) as pm_tax_class
	,max(case when meta_key = '_tax_status' then meta_value else null end) as pm_tax_status
	,max(case when meta_key = '_thumbnail_id' then meta_value else null end) as pm_thumbnail_id
	,max(case when meta_key = '_upsell_ids' then meta_value else null end) as pm_upsell_ids
	,max(case when meta_key = '_virtual' then meta_value else null end) as pm_virtual
	,max(case when meta_key = '_visibility' then meta_value else null end) as pm_visibility
	,max(case when meta_key = '_volume' then meta_value else null end) as pm_volume
	,max(case when meta_key = '_wc_average_rating' then meta_value else null end) as pm_wc_average_rating
	,max(case when meta_key = '_wc_measurement_price_calculator_min_price' then meta_value else null end) as pm_wc_measurement_price_calculator_min_price
	,max(case when meta_key = '_wc_price_calculator' then meta_value else null end) as pm_wc_price_calculator
	,max(case when meta_key = '_wc_price_calculator_pricing_rules' then meta_value else null end) as pm_wc_price_calculator_pricing_rules
	,max(case when meta_key = '_wc_rating_count' then meta_value else null end) as pm_wc_rating_count
	,max(case when meta_key = '_weight' then meta_value else null end) as pm_weight
	,max(case when meta_key = '_width' then meta_value else null end) as pm_width
	,max(case when meta_key = '_woocommerce_add_fees_product' then meta_value else null end) as pm_woocommerce_add_fees_product
	,max(case when meta_key = '_wp_old_slug' then meta_value else null end) as pm_wp_old_slug
	,max(case when meta_key = 'total_sales' then meta_value else null end) as pmtotal_sales
from wp1_posts po
left join wp1_postmeta pm
	on pm.post_id = po.ID
where post_type = 'product'
group by po.ID

xx