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