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