Woocommerce -> MySQL -> vVariationMetaData vProductVariation

20181220 Product variations (attribute = length only currently)

alter view vVariationMetaData
as
-- 20181220 LSR
-- Product Variations meta data
-- Note is selected metadata only (not quite all)
select po.post_parent, pm.post_id
,max(case when meta_key = 'attribute_length' then meta_value else null end) as pvm_attribute_length
,max(case when meta_key = '_sku' then meta_value else null end) as pvm_sku
,max(case when meta_key = '_weight' then meta_value else null end) as pvm_weight
,max(case when meta_key = '_length' then meta_value else null end) as pvm_length
,max(case when meta_key = '_width' then meta_value else null end) as pvm_width
,max(case when meta_key = '_height' then meta_value else null end) as pvm_height
,max(case when meta_key = '_manage_stock' then meta_value else null end) as pvm_manage_stock
,max(case when meta_key = '_regular_price' then meta_value else null end) as pvm_regular_price
,max(case when meta_key = '_price' then meta_value else null end) as pvm_price
,max(case when meta_key = '_variation_description' then meta_value else null end) as pvm_variation_description
from wp1_postmeta pm
join wp1_posts po on po.ID = pm.post_id
where po.post_type = 'product_variation'
group by po.post_parent, pm.post_id;


alter view vProductVariations
as
-- 20181220 LSR
-- Product meta data
select ID, post_title, pm_sku, pvm.*
from vProductMetaData pm
join vVariationMetaData pvm on pvm.post_parent = pm.id
order by pm.ID, pvm_attribute_length*0;

xx

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

Woocommerce MySQL Views General notes

Precision of $ values stored as LONGTEXT

Since most $$ are stored as meta_keys in:

  • ..post_meta
  • ..order_itemmeta

and because meta_keys is of data type LONGTEXT

When using the values as numbers there is an implicit numeric conversion to floating point, we get values like this from say, SUM(x):

  • 7564.5999999999985

Where we expect this:

  • 7564.60

Updated 20180808: Totals calculated directly from product prices (assuming product prices are always no more than 2dp) are always 2dp, it is tax cacualtions that are 4dp

Solution:

  1. In the lowest level views, where we typically flatten meta_key/Meta_values per a parent record, we will cast them as:
    1. DECIMAL(12,4) for TAX
    2. DECIMAL (10,2) for product price derived values (e.g. order_line_item->line_subtotal)
  2. In any other query that accesses what is expected to be a $ value, we will do the same cast every time
  3. Note we are casting as 4dp as this seems to be the accuracy used to populate these values.  As we have had rounding discrepancies with with tax, i.e. calced & stored at order line (item) level vs calculated across the order, we are leaving these at 4dp until we make progress on that issue

When doing data clenaup in mySQL, Safe updtae is turned on by default, solution example:

SET SQL_SAFE_UPDATES=0;
 update wp1_woocommerce_order_itemmeta
 set meta_key = 'length'
 where meta_key like '%required%';
SET SQL_SAFE_UPDATES=1;
 

xx