Just a WIP dump
See private post for Host reminder
select po.ID, po.post_title, po.post_name ,max(case when meta_key = '_sku' then meta_value else null end) as psku ,max(case when meta_key = '_weight' then meta_value else null end) as pweight ,max(case when meta_key = '_length' then meta_value else null end) as plength ,max(case when meta_key = '_width' then meta_value else null end) as pwidth ,max(case when meta_key = '_height' then meta_value else null end) as pheight -- ,count(po.meta_id) as check from wp1_posts po left outer join wp1_postmeta pm on pm.post_id = po.ID where post_type = 'product' group by po.ID, po.post_title, po.post_name order by psku
Order line items for invoices
- Is all line items & all found line item meta values as at 20170417 with my custom attributes last (..as m4mxxxx)
select * from wp1_woocommerce_order_items oi join ( select order_item_id ,max(case when meta_key = '_qty' then meta_value end) as _qty ,max(case when meta_key = '_tax_class' then meta_value end) as _tax_class ,max(case when meta_key = '_product_id' then meta_value end) as _product_id ,max(case when meta_key = '_variation_id' then meta_value end) as _variation_id ,max(case when meta_key = '_line_subtotal' then meta_value end) as _line_subtotal ,max(case when meta_key = '_line_total' then meta_value end) as _line_total ,max(case when meta_key = '_line_subtotal_tax' then meta_value end) as _line_subtotal_tax ,max(case when meta_key = '_line_tax' then meta_value end) as _line_tax ,max(case when meta_key = '_line_tax_data' then meta_value end) as _line_tax_data ,max(case when meta_key = '_measurement_data' then meta_value end) as _measurement_data ,max(case when meta_key = 'method_id' then meta_value end) as method_id ,max(case when meta_key = 'cost' then meta_value end) as cost ,max(case when meta_key = 'taxes' then meta_value end) as taxes ,max(case when meta_key = 'rate_id' then meta_value end) as rate_id ,max(case when meta_key = 'label' then meta_value end) as label ,max(case when meta_key = 'compound' then meta_value end) as compound ,max(case when meta_key = 'tax_amount' then meta_value end) as tax_amount ,max(case when meta_key = 'shipping_tax_amount' then meta_value end) as shipping_tax_amount ,max(case when meta_key = 'Items' then meta_value end) as Items ,max(case when meta_key = 'length' then meta_value end) as length ,max(case when meta_key = 'Required Length (mm)' then meta_value end) as m4mRequiredLength ,max(case when meta_key = 'Cutting options' then meta_value end) as m4mCuttingOptions ,max(case when meta_key = 'End threading options M6' then meta_value end) as m4mEndThreadingOptions ,max(case when meta_key = 'Milling options' then meta_value end) as m4mMillingOptions from wp1_woocommerce_order_itemmeta oim group by order_item_id ) oim on oim.order_item_id = oi.order_item_id where order_id = 907 order by oi.order_item_id;
xxx
20170417 Getting my order lines for paste into Excel invoice template
select 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 ( 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 ) oim on oim_order_item_id = oi.order_item_id -- Get inventory ID & inventory title left join ( select po.ID as inv_id ,max(post_title) as inv_title ,max(case when meta_key = '_sku' then meta_value end) as sku from wp1_posts po join wp1_postmeta pm on pm.post_id = po.ID where pm.meta_key = '_sku' group by po.id ) inv on inv.inv_id = oim.oim__product_id -- ************************ where order_id = 907 -- ************************ order by oi.order_item_id ) oi
Actual output (but SKU & Product names edited)
1111 | khjh | 1050 | 5 | 12.67 | ||
2222 | iuyiuyuy | 30 | 0.49 | |||
2333 | gfsgffd | 200 | 0.15 | |||
2444 | vbcvbcvc | 60 | 0.16 | |||
15245 | lklkj | 60 | 0.17 | |||
65454 | twreew | 4 | 15.95 | |||
987 | ,b,mnnbmn | 5 | 1.75 | |||
65448 | rewrerew | 2 | 1.95 | |||
1. Courier | 10.67 | |||||
NZ-GST-1 | 32.2455 |
20170725 Turning the above into Reusable Views
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 I am always a little uneasy about using max in this manner. In the instance there is a duplicate attribute we won't know.. */ 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;
Using above to get Invoice detail
xxx