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_idand 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