MFM Purchases

Initial versions 20181117

—

Keep track of purchases

' Snapshot in case ever lost

CREATE TABLE `m4m_wpProducts`
	(`wp_post_id` INT
	,`wp_post_title` TEXT
	,`wp_sku` TEXT
	,
KEY `iPostID` (`wp_post_id`) USING BTREE
);

insert into m4m_wpProducts (`wp_post_id`, `wp_post_title`, `wp_sku`)
select ID, post_title, pm_sku from vProductMetaData;

Purchases

CREATE TABLE `m4m_purchases`
	(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
    ,`wp_post_id` INT NOT NULL
	,`site` TEXT
	,`who` TEXT
	,`placed` DATETIME
	,`received` DATETIME
	,`qty_ordered` INT
	,`qty_received` INT
	,`qty_into_stock` INT
	,`issues` TEXT
	,`cost_total` DECIMAL(10,2)
	,`cost_unit_as_received` DECIMAL(10,4)
	,`cost_unit_as_into_stock` DECIMAL(10,4)
	,`cost_replacement` DECIMAL(10,2)
    ,KEY `iPostID` (`wp_post_id`) USING BTREE
);

xx

alter view vPurchasesSummary as
-- 20181118 LSR
-- Initial
-- 20181207 LSR
-- Added firstorders & orders
select wp_post_id,
    round( sum(cost_total) / sum(qty_received), 4) as cost_wavg,
    min(placed) as firstorder,
    count(*) as orders
from  m4m_purchases
where qty_received <> 0
group by wp_post_id;

alter view vPurchases
-- 20181117 LSR
-- Initial version for stock purchases CRUD
as
select 
	concat('<a href="productbuy.php?wp_post_id=',wp_post_id,'&id=',id,'">OL',wp_post_id,'/',ID, '</a>') as edit_href,
	id,
    wp_post_id,
    site,
    who,
    placed,
    received,
    qty_ordered,
    qty_received,
    qty_into_stock,
    issues,
    cost_total,
    cost_unit_as_received,
    cost_unit_as_into_stock,
    cost_replacement
from m4m_purchases pu;

xx
ProductListInitial

alter view vProductSimple
-- 20181117 LSR
-- Initial version for stock management entry point
-- 20181229 LSR
-- Added 'left' to join on vPurchasesSummary
as
select pm.ID as wp_post_id, pm_sku, post_title, pm_stock,
	concat('<a href="productbuy.php?wp_post_id=',pm.ID,'" >OL',pm.ID, '</a>') as productbuy_href,
	pm_m4m_cost,
    pm_m4m_ok,
    pm_m4m_pnp_cost,
    pm_m4m_purchase_note,
    pm_m4m_qty_bought,
    pm_m4m_qty_inv,
    pm_m4m_repl_cost,
    pm_m4m_shrinkage,
    pm_m4m_stocktake20180331,
    ps.cost_wavg
from vProductMetaData pm
join vProductType pt on pt.ID = pm.ID
left join vPurchasesSummary ps on pm.ID = ps.wp_post_id
where product_type = 'simple';

xx