Woocommerce -> MySQL -> vOrderMetaData

20190217

create view vOrderMetaData as
 -- 20190217 1055 LSR
 -- Added PostHaste2 to allow entering a followup trackingnumber or for when 2 packages
 -- 20180810 0813
 -- Added 'monthdiff'
 -- 20180806 0940
 -- Added billing_company / shipping_company
 -- 20180803 0844
 -- Customer notes are stored in posts.post_excerpt
 /*
 View to flattern Order attributes from meta table
 20180731 0946
 Discrete values
 Post Haste tracking url generated from relevnat metavalue
 Post Haste tracking URL generated in URL encoded format for including in emails
 20180803 Cast expected $ meta_values as DECIMAL either 2dp or 4dp to reflect
 dp of values originally populated
 / select po.ID, po.post_status, po.post_excerpt ,po.post_date, DATE_FORMAT(po.post_date, "%e %b %Y") as dateformatted ,(year(curdate())-2000)12 + month(curdate()) - ((year(post_date)-2000)*12 + month(post_date)) as monthdiff
 ,max( CASE WHEN pm.meta_key = '_billing_email' THEN pm.meta_value END ) as billing_email
 ,max( CASE WHEN pm.meta_key = '_billing_phone' THEN pm.meta_value END ) as billing_phone
 ,max( CASE WHEN pm.meta_key = '_billing_first_name' THEN pm.meta_value END ) as billing_first_name
 ,max( CASE WHEN pm.meta_key = '_billing_last_name' THEN pm.meta_value END ) as billing_last_name
 ,max( CASE WHEN pm.meta_key = '_billing_company' THEN pm.meta_value END ) as billing_company
 ,max( CASE WHEN pm.meta_key = '_billing_address_1' THEN pm.meta_value END ) as billing_address_1
 ,max( CASE WHEN pm.meta_key = '_billing_address_2' THEN pm.meta_value END ) as billing_address_2
 ,max( CASE WHEN pm.meta_key = '_billing_city' THEN pm.meta_value END ) as billing_city
 ,max( CASE WHEN pm.meta_key = '_billing_state' THEN pm.meta_value END ) as billing_state
 ,max( CASE WHEN pm.meta_key = '_billing_postcode' THEN pm.meta_value END ) as billing_postcode
 ,max( CASE WHEN pm.meta_key = '_billing_country' THEN pm.meta_value END ) as billing_country
 ,max( CASE WHEN pm.meta_key = '_shipping_first_name' THEN pm.meta_value END ) as shipping_first_name
 ,max( CASE WHEN pm.meta_key = '_shipping_last_name' THEN pm.meta_value END ) as shipping_last_name
 ,max( CASE WHEN pm.meta_key = '_shipping_company' THEN pm.meta_value END ) as shipping_company
 ,max( CASE WHEN pm.meta_key = '_shipping_address_1' THEN pm.meta_value END ) as shipping_address_1
 ,max( CASE WHEN pm.meta_key = '_shipping_address_2' THEN pm.meta_value END ) as shipping_address_2
 ,max( CASE WHEN pm.meta_key = '_shipping_city' THEN pm.meta_value END ) as shipping_city
 ,max( CASE WHEN pm.meta_key = '_shipping_state' THEN pm.meta_value END ) as shipping_state
 ,max( CASE WHEN pm.meta_key = '_shipping_postcode' THEN pm.meta_value END ) as shipping_postcode
 ,max( CASE WHEN pm.meta_key = '_shipping_country' THEN pm.meta_value END ) as shipping_country
 ,max( cast(CASE WHEN pm.meta_key = '_order_total' THEN pm.meta_value END as DECIMAL(12,2) )) as order_total
 ,max( cast(CASE WHEN pm.meta_key = '_order_tax' THEN pm.meta_value END as DECIMAL(12,4) )) as order_tax
 ,max( cast(CASE WHEN pm.meta_key = '_order_shipping_tax' THEN pm.meta_value END as DECIMAL(12,4) )) as order_shipping_tax
 ,max( CASE WHEN pm.meta_key = '_paid_date' THEN pm.meta_value END ) as paid_date
 ,max( CASE WHEN pm.meta_key = '_payment_method_title' THEN pm.meta_value END ) as payment_method_title 
 
 -- Custom fields for Post Haste ticket
 
 -- **** Ticket product & serial for 'post_haste' *****
 ,max( CASE WHEN pm.meta_key = 'post_haste' THEN pm.meta_value END ) as posthaste
 ,max( CASE WHEN pm.meta_key = 'post_haste' THEN
 trim(left(pm.meta_value, position(' ' IN pm.meta_value))) END ) as posthaste_product
 ,max( CASE WHEN pm.meta_key = 'post_haste' THEN
 right(pm.meta_value, length(pm.meta_value) - position(' ' IN pm.meta_value)) END ) as posthaste_serial
 -- Poste Hast tracking html link
 -- http://www.posthaste.co.nz/phl/servlet/TAndTServlet?page=1&product_code=PD&serial_number=00105005&request_id=1
 ,max( CASE WHEN pm.meta_key = 'post_haste' THEN
 concat('' ,pm.meta_value ,'')
 end ) as ph_href
 -- Poste Hast tracking html link URLencoded for use in email templates
 ,max( CASE WHEN pm.meta_key = 'post_haste' THEN
 concat('http%3A%2F%2Fwww.posthaste.co.nz%2Fphl%2Fservlet%2FTAndTServlet%3Fpage%3D1%26product_code%3D'
 ,trim(left(pm.meta_value, position(' ' IN pm.meta_value)))
 ,'%26serial_number%3D'
 ,right(pm.meta_value, length(pm.meta_value) - position(' ' IN pm.meta_value))
 ,"%26request_id%3D1"
 ) end ) as ph_httpurlencoded
 -- **** Ticket product & serial for 'post_haste2' *
 ,max( CASE WHEN pm.meta_key = 'post_haste2' THEN pm.meta_value END ) as posthaste2
 ,max( CASE WHEN pm.meta_key = 'post_haste2' THEN
 trim(left(pm.meta_value, position(' ' IN pm.meta_value))) END ) as posthaste_product2
 ,max( CASE WHEN pm.meta_key = 'post_haste2' THEN
 right(pm.meta_value, length(pm.meta_value) - position(' ' IN pm.meta_value)) END ) as posthaste_serial2
 -- Poste Hast tracking html link
 -- http://www.posthaste.co.nz/phl/servlet/TAndTServlet?page=1&product_code=PD&serial_number=00105005&request_id=1
 ,max( CASE WHEN pm.meta_key = 'post_haste2' THEN
 concat('' ,pm.meta_value ,'')
 end ) as ph_href2
 -- Poste Hast tracking html link URLencoded for use in email templates
 ,max( CASE WHEN pm.meta_key = 'post_haste2' THEN
 concat('http%3A%2F%2Fwww.posthaste.co.nz%2Fphl%2Fservlet%2FTAndTServlet%3Fpage%3D1%26product_code%3D'
 ,trim(left(pm.meta_value, position(' ' IN pm.meta_value)))
 ,'%26serial_number%3D'
 ,right(pm.meta_value, length(pm.meta_value) - position(' ' IN pm.meta_value))
 ,"%26request_id%3D1"
 ) end ) as ph2_httpurlencoded
 
 -- Post Haste end
 
 from wp1_postmeta pm
 join wp1_posts po on po.ID = pm.post_id
 where po.post_type = 'shop_order'
 group by po.ID;

20180803

vOrderMetaData: Cast $ values to decimal:

alter view vOrderMetaData as
-- 20180810 0813
-- Added 'monthdiff'
-- 20180806 0940
-- Added billing_company / shipping_company
-- 20180803 0844
-- Customer notes are stored in posts.post_excerpt
/*
View to flattern Order attributes from meta table
- 20180731 0946
- Discrete values
- Post Haste tracking url generated from relevnat metavalue
- Post Haste tracking URL generated in URL encoded format for including in emails

- 20180803 Cast expected $ meta_values as DECIMAL either 2dp or 4dp to reflect
- dp of values originally populated
*/
select po.ID, po.post_status, po.post_excerpt
,po.post_date, DATE_FORMAT(po.post_date, "%e %b %Y") as dateformatted
,(year(curdate())-2000)*12 + month(curdate()) - ((year(post_date)-2000)*12 + month(post_date)) as monthdiff
,max( CASE WHEN pm.meta_key = '_billing_email' THEN pm.meta_value END ) as billing_email
,max( CASE WHEN pm.meta_key = '_billing_phone' THEN pm.meta_value END ) as billing_phone
,max( CASE WHEN pm.meta_key = '_billing_first_name' THEN pm.meta_value END ) as billing_first_name
,max( CASE WHEN pm.meta_key = '_billing_last_name' THEN pm.meta_value END ) as billing_last_name
,max( CASE WHEN pm.meta_key = '_billing_company' THEN pm.meta_value END ) as billing_company
,max( CASE WHEN pm.meta_key = '_billing_address_1' THEN pm.meta_value END ) as billing_address_1
,max( CASE WHEN pm.meta_key = '_billing_address_2' THEN pm.meta_value END ) as billing_address_2
,max( CASE WHEN pm.meta_key = '_billing_city' THEN pm.meta_value END ) as billing_city
,max( CASE WHEN pm.meta_key = '_billing_state' THEN pm.meta_value END ) as billing_state
,max( CASE WHEN pm.meta_key = '_billing_postcode' THEN pm.meta_value END ) as billing_postcode
,max( CASE WHEN pm.meta_key = '_billing_country' THEN pm.meta_value END ) as billing_country
,max( CASE WHEN pm.meta_key = '_shipping_first_name' THEN pm.meta_value END ) as shipping_first_name
,max( CASE WHEN pm.meta_key = '_shipping_last_name' THEN pm.meta_value END ) as shipping_last_name
,max( CASE WHEN pm.meta_key = '_shipping_company' THEN pm.meta_value END ) as shipping_company
,max( CASE WHEN pm.meta_key = '_shipping_address_1' THEN pm.meta_value END ) as shipping_address_1
,max( CASE WHEN pm.meta_key = '_shipping_address_2' THEN pm.meta_value END ) as shipping_address_2
,max( CASE WHEN pm.meta_key = '_shipping_city' THEN pm.meta_value END ) as shipping_city
,max( CASE WHEN pm.meta_key = '_shipping_state' THEN pm.meta_value END ) as shipping_state
,max( CASE WHEN pm.meta_key = '_shipping_postcode' THEN pm.meta_value END ) as shipping_postcode
,max( CASE WHEN pm.meta_key = '_shipping_country' THEN pm.meta_value END ) as shipping_country
,max( cast(CASE WHEN pm.meta_key = '_order_total' THEN pm.meta_value END as DECIMAL(12,2) )) as order_total
,max( cast(CASE WHEN pm.meta_key = '_order_tax' THEN pm.meta_value END as DECIMAL(12,4) )) as order_tax
,max( cast(CASE WHEN pm.meta_key = '_order_shipping_tax' THEN pm.meta_value END as DECIMAL(12,4) )) as order_shipping_tax
,max( CASE WHEN pm.meta_key = '_paid_date' THEN pm.meta_value END ) as paid_date
,max( CASE WHEN pm.meta_key = '_payment_method_title' THEN pm.meta_value END ) as payment_method_title
-- -----------------------------------------------------
-- Custom fields for Post Haste ticket
-- -----------------------------------------------------
-- Ticket product & serial
,max( CASE WHEN pm.meta_key like '%aste%' THEN pm.meta_value END ) as posthaste
,max( CASE WHEN pm.meta_key like '%aste%' THEN
trim(left(pm.meta_value, position(' ' IN pm.meta_value))) END ) as posthaste_product
,max( CASE WHEN pm.meta_key like '%aste%' THEN
right(pm.meta_value, length(pm.meta_value) - position(' ' IN pm.meta_value)) END ) as posthaste_serial
-- Poste Hast tracking html link
-- http://www.posthaste.co.nz/phl/servlet/TAndTServlet?page=1&product_code=PD&serial_number=00105005&request_id=1
,max( CASE WHEN pm.meta_key like '%aste%' THEN
concat('<a href="http://www.posthaste.co.nz/phl/servlet/TAndTServlet?page=1&product_code='
,trim(left(pm.meta_value, position(' ' IN pm.meta_value)))
,'&serial_number='
,right(pm.meta_value, length(pm.meta_value) - position(' ' IN pm.meta_value))
,'&request_id=1" target="_blank">'
,pm.meta_value
,'</a>')
end ) as ph_href
-- Poste Hast tracking html link URLencoded for use in email templates
,max( CASE WHEN pm.meta_key like '%aste%' THEN
concat('http%3A%2F%2Fwww.posthaste.co.nz%2Fphl%2Fservlet%2FTAndTServlet%3Fpage%3D1%26product_code%3D'
,trim(left(pm.meta_value, position(' ' IN pm.meta_value)))
,'%26serial_number%3D'
,right(pm.meta_value, length(pm.meta_value) - position(' ' IN pm.meta_value))
,"%26request_id%3D1"
) end ) as ph_httpurlencoded
-- ------------------
-- Post Haste end
-- ------------------
from wp1_postmeta pm
join wp1_posts po on po.ID = pm.post_id
where po.post_type = 'shop_order'
group by po.ID;

xx

20180730

select distinct post_type
from wp1_posts;

Result:

  post_type
  attachment
  page
  product
  product_variation
  revision
  shop_order
  shop_order_refund 
select distinct meta_key
from wp1_postmeta pm
join wp1_posts po on po.ID = pm.post_id
where po.post_type = 'shop_order';

Result:

_order_key
_order_currency
_prices_include_tax
_customer_ip_address
_customer_user_agent
_customer_user
_created_via
_order_version
_order_shipping
_billing_first_name
_billing_last_name
_billing_company
_billing_email
_billing_phone
_billing_country
_billing_address_1
_billing_address_2
_billing_city
_billing_state
_billing_postcode
_shipping_first_name
_shipping_last_name
_shipping_company
_shipping_country
_shipping_address_1
_shipping_address_2
_shipping_city
_shipping_state
_shipping_postcode
_payment_method
_payment_method_title
_cart_discount
_cart_discount_tax
_order_tax
_order_shipping_tax
_order_total
_recorded_sales
_order_stock_reduced
_download_permissions_granted
_edit_lock
_edit_last
_transaction_id
_cart_hash
_completed_date
_woocommerce_add_fees_order
_ga_tracked
Payer PayPal address
Payer first name
Payer last name
Payment type
_paid_date
PayPal Transaction Fee

—

Updated: February 17, 2019 at 1:01 pm