Musings on how to calculate close to actual cost of sales in Woocommerce 2.6.14 using FIFO
(If I manage to achieve it per order line then I can aggregate up for reporting totals etc, so initially focus on per order item)
I have a custom inwards goods table, for initial thoughts I will ignore credits/returns, shrinkage & stockcounts, etc (80/20 rule)
The records available can readily be reduced down to:
- Inwards (Inventory inwards movements): Product, When, Qty, Cost per
- Outwards (Sales): Product, When, Qty, Price per
I feel running totals will work here if I calculate:
- Inwards: Per product, running total of Qty always accumulative from day 1 [rtotal]
- Outwards: Per product, running total of Qty always accumulative from day 1 [rtotal]
So assuming we have the above records available a final pseudo query to get the cost for a product sale:
select out.orderID ,out.productID ,out.price ,in.cost from ( Select out.orderID ,out.productID ,out.price ,max(in.when) as inwhen from out join in on in.productID = out.productID where out.rtotal <= in.rtotal group by out.orderID, out.productID, out.price ) out join in on in.productID = out.productID and in.when= out.inwhen
Inwards goods running total
Hmm, our hosting plan is MySQL 5.6.40 currently no later MySQL SUM OVER and no CTE’s
Ok, then use a self join and we have a running total for inwards movements
- wp_post_id is the woocommerce product id
- to_inventory is the dateTime the stock went into inventory
- qty_counted is the receipted stock
select t1.id ,t1.wp_post_id as pid ,t1.cost_unit_as_counted ,t1.cost_unit_as_into_stock ,t1.cost_replacement ,sum(t2.qty_counted) as rt from m4m_stockmovement t1 join m4m_stockmovement t2 on t1.wp_post_id = t2.wp_post_id and t1.to_inventory >= t2.to_inventory where t1.type = 'BUY' and t2.type = 'BUY' group by t1.id ,t1.wp_post_id ,t1.cost_unit_as_counted ,t1.cost_unit_as_into_stock ,t1.cost_replacement;
Yep seems fine and < 2k records so no worries re speed
Order line items running total
As order items id is auto increment we can use that for chronological running total
We already have a view where we have flattened order item metadata so that is the main query source:
select t1.order_id ,t1.order_item_id ,t1.oim->line_item->_product_id
,sum(t2.oim->line_item->_qty
) as rt from vOrderItemMetaDataV3 t1 join vOrderItemMetaDataV3 t2 on t1.oim->line_item->_product_id
= t2.oim->line_item->_product_id
and t1.order_item_id <= t2.order_item_id join wp1_posts p1 on p1.ID = t1.order_id join wp1_posts p2 on p2.ID = t2.order_id where p1.post_type = 'shop_order' and p1.post_status in ('wc-completed' , 'wc-on-hold', 'wc-processing') and p2.post_type = 'shop_order' and p2.post_status in ('wc-completed' , 'wc-on-hold', 'wc-processing') group by t1.order_id ,t1.order_item_id ,t1.oim->line_item->_product_id
;
Result: that is wayyy too inefficient.. has full table scan in the query plan, so what about if we work directly off the underlying tables:
select oi1.order_id ,oi1.order_item_id ,p1 ,q1 ,sum(q2) as rt from wp1_woocommerce_order_items oi1 join wp1_posts p1 on oi1.order_id = p1.ID join ( select order_item_id, meta_value as p1 from wp1_woocommerce_order_itemmeta oim where meta_key = '_product_id' ) op1 on op1.order_item_id = oi1.order_item_id join ( select order_item_id, meta_value as q1 from wp1_woocommerce_order_itemmeta oim where meta_key = '_qty' ) oq1 on oq1.order_item_id = oi1.order_item_id join ( select oi2.order_item_id ,p2 ,q2 from wp1_woocommerce_order_items oi2 join wp1_posts p2 on oi2.order_id = p2.ID join ( select order_item_id, meta_value as p2 from wp1_woocommerce_order_itemmeta oim where meta_key = '_product_id' ) op2 on op2.order_item_id = oi2.order_item_id join ( select order_item_id, meta_value as q2 from wp1_woocommerce_order_itemmeta oim where meta_key = '_qty' ) oq2 on oq2.order_item_id = oi2.order_item_id where p2.post_type = 'shop_order' and p2.post_status in ('wc-completed' , 'wc-on-hold', 'wc-processing') ) oi2 on p2 = p1 and oi2.order_item_id <= oi1.order_item_id where p1.post_type = 'shop_order' and p1.post_status in ('wc-completed' , 'wc-on-hold', 'wc-processing');
Result: Nope still wayyyy to inefficient. <rant>Well WordPress and Woocommerce totally ignores basic database design rules with all these fucking key value pairs so what can we expect..</rant>
(There are only 40k order line records in wp1_woocommerce_order_items but unfortunately there are 300k associated key/value pairs in wp1_woocommerce_order_itemmeta)
So we will use temporary / permanent tables, most of our data will be historical anyway.. note we cast pid * qty from longtext to more sensible types to prevent issues later
So, a simple flattened table:
CREATE TABLE m4m_temp_stocksold AS select oi.order_id ,oi.order_item_id ,cast(p as unsigned integer) as pid ,cast(q as DECIMAL(12,4)) as qty ,cast(coalesce(l, 1) as DECIMAL(12,4)) as length from wp1_woocommerce_order_items oi join wp1_posts p on oi.order_id = p.ID join ( select order_item_id, meta_value as p from wp1_woocommerce_order_itemmeta oim where meta_key = '_product_id' ) op on op.order_item_id = oi.order_item_id join ( select order_item_id, meta_value as q from wp1_woocommerce_order_itemmeta oim where meta_key = '_qty' ) oq on oq.order_item_id = oi.order_item_id left join ( select order_item_id, meta_value as l from wp1_woocommerce_order_itemmeta oim where meta_key = 'length' ) ol on ol.order_item_id = oi.order_item_id where p.post_type = 'shop_order' and p.post_status in ('wc-completed' , 'wc-on-hold', 'wc-processing'); create index pid_idx on m4m_temp_stocksold (pid); create unique index oid_idx on m4m_temp_stocksold (order_item_id); create index o_idx on m4m_temp_stocksold (order_id);
Result = looks ok so far
- note I included length (for a handful of products where that is applicable (in mm) and coalesced to ‘1’ for those where it isn’t a factor (so I can do do (qty*length) for the running total for all products in following steps)
now to query it:
select t1.order_id ,t1.order_item_id ,sum(t2.qty * t2.lenght) as rt from m4m_temp_stocksold t1 join m4m_temp_stocksold t2 on t1.pid = t2.pid and t1.order_item_id >= t2.order_item_id group by t1.order_id ,t1.order_item_id;
Looks right including products sold by length