— DB Checks — select * from vProducts where 1 = 1 and product_type = ‘simple’ and pm_manage_stock != ‘no’ and (pm_stock_status != ‘instock’ and coalesce(pm_stock, 0) > 0); select count(*) as chk from wp1_posts where post_status not in (select post_status from m4m_refOrderStates) and post_type = ‘shop_order’; select id, pmd.post_title, pmd.pm_price, pm_regular_price from vProductMetaData pmd […]
Category: Woocommerce MySQL Views
20200512 Warning: This page is just a StreamOfConsciousness(tm) Issue: managing 1000 sku’s is hard work vProduct sales shows totals for %complete% orders we want to be able to set stock levels whilst picking orders we do not differentiate in woocommerce states orders that are picked vs unpicked.. BUT when picking, and stock checking, I want […]
20191218 As our hosting plan is somewhat restrictive, I don’t wanna fire off complex queries unnecessarily so maybe creation of like a mini DW snapshots Sample questions: Who bought product xyz Click on an order and see everything the customer has ever bought in a single view – different aggregations in case not registered: name […]
20181220 Product variations (attribute = length only currently) alter view vVariationMetaData as — 20181220 LSR — Product Variations meta data — Note is selected metadata only (not quite all) select po.post_parent, pm.post_id ,max(case when meta_key = ‘attribute_length’ then meta_value else null end) as pvm_attribute_length ,max(case when meta_key = ‘_sku’ then meta_value else null end) as […]
Paypal orders are sorta locked xx — wp1_postmeta update wp1_postmeta set meta_value = ‘0.00’ where post_id = 3387 and meta_key = ‘_order_shipping’; update wp1_postmeta set meta_value = ‘0.00’ where post_id = 3387 and meta_key = ‘_order_shipping_tax’; update wp1_postmeta set meta_value = ‘10.36’ where post_id = 3387 and meta_key = ‘_order_total’; — wp1_woocommerce_order_itemmeta update wp1_woocommerce_order_itemmeta oim […]
Potential dashboard / tabular reports for Woocommerce product to sales analysis Top view = ‘Sales by product’ Product centric view Columns: General Meta: Sku Name Category count = how many categories product appears in Category, (1st lowest only) Sales Net $ total Net $ per prev 12 months Net $ per prev months 13-24 Net […]
Product categories have parents… Products can be in more than 1 category xx create view vProductCatsSummary as select ID ,count(*) as cat_cnt ,min(tt.term_taxonomy_id) as cat_min ,max(tt.term_taxonomy_id) as cat_max ,( select group_concat( tex.name separator ‘ | ‘ ) from wp1_term_relationships trx JOIN wp1_term_taxonomy ttx ON trx.term_taxonomy_id = ttx.term_taxonomy_id JOIN wp1_terms tex ON ttx.term_id = tex.term_id where […]
xx alter view vOrderSummaryByMonth as — 20190217 LSR — Add refund into cancelled total — Exclude refund from incompleted — 20180829 LSR — Split by Trademe not trademe — 20180803 0907 LSR — Order counts & sums by month — 20181128 1226 LSR — Added cancel_oimnet — Updated incomp_oimnet to be all except completed / […]
xx alter view vOrdersList as — 20180807 LSR — Added posthaste edit A link — 20180802 1019 select concat(‘<a href=”invoice.php?order=’,ID,’©invoice=N” target=”_blank”>OL’, ID, ‘</a>’) as NewInv ,concat(‘<a href=”invoiceold.php?order=’,ID,'” target=”_blank”>OL’, ID, ‘</a>’) as OldInv ,post_date ,order_total ,payment_method_title as pay_method ,post_status ,concat(‘<a href=”mailto:’ ,coalesce(billing_email, ‘xxx@xxx.xxx’) ,’?Subject=MMMMM FFFF MMMM Order #’ — Company name here ,ID ,’&body=Thank%20you%20very%20much%20for%20your%20order%20which%20is%20with%20Post%20Haste.’ ,’%20%20An%20invoice%20is%20included%20in%20the%20package%0D%0A%0D%0A’ ,coalesce(ph_httpurlencoded, […]
xx Todo: change from br to p tags alter view vOrderAddresses as — 20180822 LSR — Added Post_excerpt first 20 chars(Customer comment added at checkout) — 20180807 LSR — Added maint <a> link — 20180805 LSR — Original select ID ,concat(‘<a href=”addressedit.php?order=’,ID,'” target=”_blank”>OL’, ID, ‘</a>’) as edit_shipping_href ,case when concat( coalesce(billing_first_name, ”) ,coalesce(billing_last_name, ”) ,coalesce(billing_company, […]