20180401 Woocommerce Stocktake Notes

20180104 Stocktake notes:

Products for stocktake by activity (data drivers for quries are in bold

1. Simple products fully managed by Woocommerce inventory where have a stocktake value
   • Fields available & checked to be populated:
     • postmeta -> manage_stock
     • postmeta -> stock
     • postmeta -> cost (custom field)
       • Checked that all fields in this category have a value
   • Other fields available for reasonableness checks:
     • postmeta -> total_sales
     • postmeta -> qty bought (custom field)
2. Simple products fully managed by Woocommerce inventory where have NO stocktake value
   • Fields available:
     • postmeta -> cost (custom field) = 0
       • checked that is 0 only for simple products in this category
     • Two fields to try to capture cost primarily so we can set sale price:
       • postmeta -> pnp_cost  (custom field) <> 0
       • postmeta -> repl_cost (custom field) <> 0
     • Ok ideally the above 2 fields should have one or both of populated to indicate products in this category but not all are populated so we will leave this as an aspirational goal and will manually check:
       • When postmeta -> cost = 0 then the product is correctly in this category
3. Simple products NOT fully managed by Woocommerce inventory where have a stocktake value
   • These products still have inventory managed in Woocommerce, it is just that not all product available is included in Woocomerce inventory quantity for various reasons:
     • Poor quality so some held back
     • Some held back in anticipation of corporate orders that need to be met promptly
     • doubt that Woocomerce is up to date
   • Fields available:
     • postmeta -> qty_inv (custom field ‘means quantity to inventory’) = -1
     • checked that is -1 only for simple products in this category
   • These products require manual inventory the outcome for each will one of two options:
     1. Product will still have stock outside woocommerce inventory
        • Ok so added a new custom field stocktake20180331 where we will record actual stock as at 31/3/2018 for these products for this stocktake
     2. Product stock will be updated into Woocommerce inventory so will move into category 1 above
        • requires qty_inv to be updated to > 0
4. Variable Products not managed by Woocommerce
   • These are ‘cut to length’ products

Query for checking values for products perhaps with issues:

select * from (
	SELECT po.ID
		,pt.product_type
		,pm_sku
		,po.post_title
		,pm_price
		,pm_regular_price
		,pm_manage_stock
		,pm_stock
		,pm_m4m_stocktake20180331
		,pm_stock_status  
		,pm_m4m_qty_bought
		,pm_m4m_qty_inv
		,pm_m4m_cost
		,pm_m4m_pnp_cost
		,pm_m4m_repl_cost
		,pmtotal_sales
	FROM wp1_posts po
	left join vProductType pt on pt.ID = po.ID
	left join vProductMetaData pm on pm.ID = po.ID
	WHERE po.post_type = 'product'
	 and product_type <> 'grouped'
	and (
			not isnumeric(pm_stock)
		or pm_stock = 0
		or not isnumeric(pm_m4m_qty_bought)
		or not isnumeric(pm_m4m_qty_inv)
		or pm_m4m_qty_inv = -1
		or not isnumeric(pm_m4m_cost)
		or pm_m4m_cost = 0
		)
	order by
		case when product_type = 'variable' then 0 else 1 end
		,case when coalesce(pm_m4m_qty_inv,0) = -1 then 0 else 1 end
		,case when coalesce(pm_stock,0) = 0 then 0 else 1 end
		,pm_sku
	) t
union select 'po.ID'
	,'pt.product_type'
    ,'pm_sku'
    ,'po.post_title'
    ,'pm_price'
    ,'pm_regular_price'
    ,'pm_manage_stock'
    ,'pm_stock'
    ,'pm_m4m_stocktake20180331'
    ,'pm_stock_status'
    ,'pm_m4m_qty_bought'
    ,'pm_m4m_qty_inv'
    ,'pm_m4m_cost'
    ,'pm_m4m_pnp_cost'
    ,'pm_m4m_repl_cost'
    ,'pmtotal_sales';

xx

Once all counts completed and data entered:

select t.*
 ,calc_stock_on_hand * coalesce(pm_m4m_cost,0) as value
from (
 SELECT po.ID
 ,pt.product_type
 ,pm_sku
 ,po.post_title
 ,pm_price
 ,pm_regular_price
 ,pm_manage_stock
 ,pm_stock
 ,pm_m4m_stocktake20180331
 ,pm_stock_status 
 ,pm_m4m_qty_bought
 ,pm_m4m_qty_inv
 ,pm_m4m_cost
 ,pm_m4m_pnp_cost
 ,pm_m4m_repl_cost
 ,pmtotal_sales
 ,case when coalesce(pm_m4m_stocktake20180331,0) = 0 then pm_stock else pm_m4m_stocktake20180331 end as calc_stock_on_hand
 FROM wp1_posts po
 left join vProductType pt on pt.ID = po.ID
 left join vProductMetaData pm on pm.ID = po.ID
 WHERE po.post_type = 'product'
 and product_type <> 'grouped'
 order by
 case when product_type = 'variable' then 0 else 1 end
 ,case when coalesce(pm_m4m_qty_inv,0) = -1 then 0 else 1 end
 ,case when coalesce(pm_stock,0) = 0 then 0 else 1 end
 ,pm_sku
 ) t
 
union select 'po.ID'
 ,'pt.product_type'
 ,'pm_sku'
 ,'po.post_title'
 ,'pm_price'
 ,'pm_regular_price'
 ,'pm_manage_stock'
 ,'pm_stock'
 ,'pm_m4m_stocktake20180331'
 ,'pm_stock_status'
 ,'pm_m4m_qty_bought'
 ,'pm_m4m_qty_inv'
 ,'pm_m4m_cost'
 ,'pm_m4m_pnp_cost'
 ,'pm_m4m_repl_cost'
 ,'pmtotal_sales'
 ,'calc_stock_on_hand'
 ,'value'

xx