20180402 Woocommerce Stored Procs backup

Views

create 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
	,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

xxx

create view vOrderItemMetaDataV2
 /*
 Author: LSR 20171218
 Woocommerce orders lines (items) have multiple (metadata) attributes.
 */
as
select oi.order_item_id
--
 ,max(case when order_item_type = 'line_item' and meta_key = '_line_subtotal' then meta_value else null end) as 'oim->line_item->_line_subtotal'
 ,max(case when order_item_type = 'fee' and meta_key = '_line_subtotal_tax' then meta_value else null end) as 'oim->fee->_line_subtotal_tax'
 ,max(case when order_item_type = 'line_item' and meta_key = '_line_subtotal_tax' then meta_value else null end) as 'oim->line_item->_line_subtotal_tax'
 ,max(case when order_item_type = 'fee' and meta_key = '_line_tax' then meta_value else null end) as 'oim->fee->_line_tax'
 ,max(case when order_item_type = 'line_item' and meta_key = '_line_tax' then meta_value else null end) as 'oim->line_item->_line_tax'
 ,max(case when order_item_type = 'shipping' and meta_key = '_line_tax' then meta_value else null end) as 'oim->shipping->_line_tax'
 ,max(case when order_item_type = 'fee' and meta_key = '_line_tax_data' then meta_value else null end) as 'oim->fee->_line_tax_data'
 ,max(case when order_item_type = 'line_item' and meta_key = '_line_tax_data' then meta_value else null end) as 'oim->line_item->_line_tax_data'
 ,max(case when order_item_type = 'shipping' and meta_key = '_line_tax_data' then meta_value else null end) as 'oim->shipping->_line_tax_data'
 ,max(case when order_item_type = 'fee' and meta_key = '_line_total' then meta_value else null end) as 'oim->fee->_line_total'
 ,max(case when order_item_type = 'line_item' and meta_key = '_line_total' then meta_value else null end) as 'oim->line_item->_line_total'
 ,max(case when order_item_type = 'line_item' and meta_key = '_measurement_data' then meta_value else null end) as 'oim->line_item->_measurement_data'
 ,max(case when order_item_type = 'line_item' and meta_key = '_product_id' then meta_value else null end) as 'oim->line_item->_product_id'
 ,max(case when order_item_type = 'line_item' and meta_key = '_qty' then meta_value else null end) as 'oim->line_item->_qty'
 ,max(case when order_item_type = 'line_item' and meta_key = '_refunded_item_id' then meta_value else null end) as 'oim->line_item->_refunded_item_id'
 ,max(case when order_item_type = 'shipping' and meta_key = '_refunded_item_id' then meta_value else null end) as 'oim->shipping->_refunded_item_id'
 ,max(case when order_item_type = 'fee' and meta_key = '_tax_class' then meta_value else null end) as 'oim->fee->_tax_class'
 ,max(case when order_item_type = 'line_item' and meta_key = '_tax_class' then meta_value else null end) as 'oim->line_item->_tax_class'
 ,max(case when order_item_type = 'line_item' and meta_key = '_variation_id' then meta_value else null end) as 'oim->line_item->_variation_id'
 ,max(case when order_item_type = 'tax' and meta_key = 'compound' then meta_value else null end) as 'oim->tax->compound'
 ,max(case when order_item_type = 'shipping' and meta_key = 'cost' then meta_value else null end) as 'oim->shipping->cost'
 ,max(case when order_item_type = 'shipping' and meta_key = 'Items' then meta_value else null end) as 'oim->shipping->Items'
 ,max(case when order_item_type = 'tax' and meta_key = 'label' then meta_value else null end) as 'oim->tax->label'
 ,max(case when order_item_type = 'line_item' and meta_key = 'length' then meta_value else null end) as 'oim->line_item->length'
 ,max(case when order_item_type = 'shipping' and meta_key = 'method_id' then meta_value else null end) as 'oim->shipping->method_id'
 ,max(case when order_item_type = 'tax' and meta_key = 'rate_id' then meta_value else null end) as 'oim->tax->rate_id'
 ,max(case when order_item_type = 'line_item' and meta_key = 'Required Length (mm)' then meta_value else null end) as 'oim->line_item->Required Length (mm)'
 ,max(case when order_item_type = 'tax' and meta_key = 'shipping_tax_amount' then meta_value else null end) as 'oim->tax->shipping_tax_amount'
 ,max(case when order_item_type = 'tax' and meta_key = 'tax_amount' then meta_value else null end) as 'oim->tax->tax_amount'
 ,max(case when order_item_type = 'shipping' and meta_key = 'taxes' then meta_value else null end) as 'oim->shipping->taxes'
 -- Modified
 ,max(case when order_item_type = 'line_item' and meta_key like 'Cutting options%' then meta_value else null end) as 'oim->line_item->Cutting options'
 ,max(case when order_item_type = 'line_item' and meta_key like 'End threading options M6%' then meta_value else null end) as 'oim->line_item->End threading options M6'
 ,max(case when order_item_type = 'line_item' and meta_key like 'Milling options%' then meta_value else null end) as 'oim->line_item->Milling options'
--
from wp1_woocommerce_order_items oi
join wp1_woocommerce_order_itemmeta oim
 on oim.order_item_id = oi.order_item_id
group by order_item_id;

xxx