CREATE TABLE `0_fifo_allocs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `type` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  `trans_no` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  `to_id` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  `from_id` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  `from_type` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  `from_trans_no` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  `qty_cleared` double DEFAULT NULL,
  `detail_id` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  `cost` double DEFAULT NULL,
  `timestamp` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;



ALTER TABLE `0_stock_moves` 
ADD COLUMN `not_cleared` DOUBLE NULL DEFAULT 0 AFTER `standard_cost`;
ALTER TABLE `0_stock_moves` 
ADD COLUMN `purchasing_price` DOUBLE NULL DEFAULT 0 AFTER `not_cleared`;

-- set @param:=152;
-- select purchasing.purchase_number, purchasing.po_qty,purchasing.unit_cost,
-- case when @param <= purchasing.po_qty then @param else purchasing.po_qty end  this_qty,
-- case when @param <= purchasing.po_qty then @param else purchasing.po_qty end * unit_cost this_value,
-- @param := @param - purchasing.po_qty as remaining
-- from ( select purchase_number,sum(last_qty) po_qty,unit_cost
--         from tbl_purchasing where item_id = 'A2' group by unit_cost order by date_purchase,purchase_number
--      ) purchasing having (this_value > 0);
