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