Woocommerce FIFO Cost of Sales

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