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
