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 |
—