Woocommerce Queries as at 20171018

Base Views

create view vProductMetaData
as
	/*
	Author: LSR 20170726
	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
	,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 = '_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;

create view vOrderItemMetaData
as
	/*
	Author: LSR 20170725
	Woocommerce order line items have multiple (metadata) attributes.
	- Each attribute has it's own record in table ..order_itemmeta
	- so if a line item has 5 attributes = 5 records in this table for that line item
	- Custom attributes generated by custom produst options (I.e. product variations)
	  also generate additional records in this table
	This View flatterns those attributes per order line item:
	- returns 1 record per line item select
	- each attribute is in its own column
    
	Assumes integrity in that an attribute as identified by meta_key exists
	only once per parent order item line
	*/
select order_item_id as oim_order_item_id
-- most of these are not used in this query
	,max(case when meta_key = '_qty' then meta_value end) as oim__qty
	,max(case when meta_key = '_tax_class' then meta_value end) as oim__tax_class
	,max(case when meta_key = '_product_id' then meta_value end) as oim__product_id
	,max(case when meta_key = '_variation_id' then meta_value end) as oim__variation_id
	,max(case when meta_key = '_line_subtotal' then meta_value end) as oim__line_subtotal
	,max(case when meta_key = '_line_total' then meta_value end) as oim__line_total
	,max(case when meta_key = '_line_subtotal_tax' then meta_value end) as oim__line_subtotal_tax
	,max(case when meta_key = '_line_tax' then meta_value end) as oim__line_tax
	,max(case when meta_key = '_line_tax_data' then meta_value end) as oim__line_tax_data
	,max(case when meta_key = '_measurement_data' then meta_value end) as oim__measurement_data
	,max(case when meta_key = 'method_id' then meta_value end) as oim_method_id
	,max(case when meta_key = 'cost' then meta_value end) as oim_cost
	,max(case when meta_key = 'taxes' then meta_value end) as oim_taxes
	,max(case when meta_key = 'rate_id' then meta_value end) as oim_rate_id
	,max(case when meta_key = 'label' then meta_value end) as oim_label
	,max(case when meta_key = 'compound' then meta_value end) as oim_compound
	,max(case when meta_key = 'tax_amount' then meta_value end) as oim_tax_amount
	,max(case when meta_key = 'shipping_tax_amount' then meta_value end) as oim_shipping_tax_amount
	,max(case when meta_key = 'Items' then meta_value end) as oim_Items
	-- m4m custom product variations / addons
	,max(case when meta_key = 'length' then meta_value end) as oim_length
	,max(case when meta_key = 'Required Length (mm)' then meta_value end) as oim_m4mRequiredLength
	,max(case when meta_key = 'Cutting options' then meta_value end) as oim_m4mCuttingOptions
	,max(case when meta_key = 'End threading options M6' then meta_value end) as oim_m4mEndThreadingOptions
	,max(case when meta_key = 'Milling options' then meta_value end) as oim_m4mMillingOptions
from wp1_woocommerce_order_itemmeta oim
group by order_item_id;

Order lines in my format for paste into Excel invoice template

select pm_sku
	,coalesce(inv_title, order_item_name)
	,null
	,oim_length
	,null
	,oim__qty
	,unitPrice
from (
	select *
		,case
			when oim__qty is not null
				and oim__line_subtotal is not null
				then round(oim__line_subtotal/oim__qty,2)
			when oim_cost is not null
				then oim_cost
			when oim_tax_amount is not null
				then oim_tax_amount + coalesce(oim_shipping_tax_amount, 0)
			else null
			end as unitprice
	from wp1_woocommerce_order_items oi
	join vOrderItemMetaData oim
		on oim_order_item_id = oi.order_item_id
	-- Get inventory ID & inventory title
	left join (
		select po.ID as inv_id
			,post_title as inv_title
			,pm_sku
		from wp1_posts po
		join vProductMetaData pm
			on pm.ID = po.ID
	) inv on inv.inv_id = oim.oim__product_id 
	-- ************************ 
	where order_id = 1910
	-- ************************ 
	order by oi.order_item_id
 ) oi;

xxx