---在倉儲管理中經常會碰到的一個問題

一、關于LIFO與FIFO的簡單說明
---FIFO: First in, First out.先進先出。
---LIFO: Last in, First out.后進先出。

--如貨物A：本月1日購買10件，單價10元/件，3日購買20件，單價15元/件；10日購買10件，單價8元/件。
--本月15日發貨35件。

--按FIFO先進先出，就是先購入的存貨先發出，所以，先發1日進貨的10件，再發3日進貨的20件，最后發10日進貨的5件，發出成本共為：10*10+20*15+5*8=440元。
--按LIFO后進先出，就是后購入的存貨先發出，所以，先發10日進貨的10件，再發3日進貨的20件，最后發1日進貨的5件，發出成本共為：10*8+20*15+5*10=430元

--------
Create table stock
(Id int not null primary key,
articleno varchar(20) not null,
rcvdate datetime not null,
qty int not null,
unitprice money not null
)
go
----
insert stock
select 1,'10561122','2011-1-1',15,10 union
select 2,'10561122','2011-2-2',25,12 union
select 3,'10561122','2011-3-3',35,15 union
select 4,'10561122','2011-4-4',45,20 union
select 5,'10561122','2011-5-5',55,10 union
select 6,'10561122','2011-6-6',65,30 union
select 7,'10561122','2011-7-7',75,17 union
select 8,'10561122','2011-8-8',110,8

go
----此時如果在2011-8-8賣出300件產品，那么應該如何計算庫存銷售的價值呢？
----1使用當前的替換成本，2011-8-8時每件產品的成本為8，就是說你這300件產品，成本價值為2400
----2使用當前的平均成本單價，一共有420，總成本為6530，平均每件的成本為15.55
----1.LIFO （后進先出）
----2011-8-8 110 *8
----2011-7-7 75*17
----2011-6-6 65*30
----2011-5-5 50*10
-----總成本為 4605
-----2.FIFO(先進先出)
---- '2011-1-1',15*10
--- '2011-2-2',25*12
-----'2011-3-3',35*15
-----'2011-4-4',45*20
-----'2011-5-5',55*10
-----'2011-6-6',65*30
-----'2011-7-7',65*17
----總成本為5480

---成本視圖
create view costLIFO
as
select unitprice from stock
where rcvdate= (select MAX(rcvdate) from stock)
go
create view costFIFO
as
select sum(unitprice*qty)/SUM(qty) as unitprice from stock

go
-----找出滿足訂單的、足夠存貨的最近日期。如果運氣好的話，某一天的庫存數量正好與訂單要求的數字完全一樣
-----就可以將總成本作為答案返回。如果訂單止的數量比庫存的多，什么也不返回。如果某一天的庫存數量比訂單數量多
---則看一下當前的單價，乘以多出來的數量，并減去它。
---下面這些查詢和視圖只是告訴我們庫存商品的庫存價值，注意，這些查詢與視圖并沒有實際從庫存中向外發貨。
create view LIFO
as
select s1.rcvdate,s1.unitprice,sum(s2.qty) as qty,sum(s2.qty*s2.unitprice) as totalcost
from stock s1 ,stock s2
where s2.rcvdate>=s1.rcvdate
group by s1.rcvdate,s1.unitprice

go
select (totalcost-((qty-300)*unitprice )) as cost
from lifo as l
where rcvdate=(select max(rcvdate) from lifo as l2 where qty>=300)
go

create view FIFO
as
select s1.rcvdate,s1.unitprice,sum(s2.qty) as qty,sum(s2.qty*s2.unitprice) as totalcost
from stock s1 ,stock s2
where s2.rcvdate=s1.rcvdate
group by s1.rcvdate,s1.unitprice

go
select (totalcost-((qty-300)*unitprice )) as cost
from fifo as l
where rcvdate=(select min(rcvdate) from lifo as l2 where qty>=300)
--------
go
-----
-----在發貨之后，實時更新庫存表
create view CurrStock
as
select s1.rcvdate,SUM(case when s2.rcvdate>s1.rcvdate then s2.qty else 0 end) as PrvQty
,SUM(case when s2.rcvdate=s1.rcvdate then s2.qty else 0 end) as CurrQty
from stock s1 ,stock s2
where s2.rcvdate=s1.rcvdate
group by s1.rcvdate,s1.unitprice
go
create proc RemoveQty
@orderqty int
as
if(@orderqty>0)
begin
update stock set qty =case when @orderqty>=(select currqty from CurrStock as c where c.rcvdate=stock.rcvdate)
then 0
when @orderqty(select prvqty from CurrStock c2 where c2.rcvdate=stock.rcvdate)
then stock.qty
else (select currqty from CurrStock as c3 where c3.rcvdate=stock.rcvdate)
-@orderqty end
end
--
delete from stock where qty=0
---
go
exec RemoveQty 20
go
---------------

-------還有一個問題，如何使用空間最小或最大的倉庫中的貨物來滿足訂單，假設倉庫不是順序排列，你可以按鈕希望的順序任意選擇滿足訂單。
---使用最小的倉庫可以為訂單的裝卸工人帶來最小的工作量，使用最大的倉庫，則可以在倉庫中清理出更多的空間
-------例如：對于這組數據，你可以使用（1，2，3，4，5，6，7）號倉庫也可以使用（5，6，7，8）號倉庫中的貨物來滿足訂單的需求。
----這個就是裝箱問題，它屬于NP完全系統問題。對于一般情況來說，這種問題很難解決，因為要嘗試所有的組合情況，而且如果數據量大的話，
----計算機也很難很快處理。
---所以有了“貪婪算法”，這個算法算出來的常常是近乎最優的。這個算法的核心就是“咬最大的一口”直到達到或超越目標。
---
--1. 第一個技巧，要在表中插入一些空的啞倉庫，如果你最多需要n次挑選，則增加n-1個啞倉庫
insert stock
select -1,'10561122','1900-1-1',0,0 union
select -2,'10561122','1900-1-1',0,0
--select -3,'1900-1-1',0,0
----
go
create view pickcombos
as
select distinct (w1.qty+w2.qty+w3.qty) as totalpick
,case when w1.id0 then 0 else w1.id end as bin1 ,w1.qty as qty1,
case when w2.id0 then 0 else w2.id end as bin2,w2.qty as qty2
,case when w3.id0 then 0 else w3.id end as bin3 ,w3.qty as qty3
from stock w1,stock w2, stock w3
where w1.id not in (w2.id,w3.id)
and w2.id not in (w1.id,w3.id)
and w1.qty>=w2.qty
and w2.qty>=w3.qty
----
---1.使用存儲過程來找出滿足或接近某一數量的挑選組合
--------
go
create proc OverPick
@pickqty int
as
if(@pickqty>0)
begin
select @pickqty,totalpick,bin1,qty1,bin2,qty2,bin3,qty3
from pickcombos
where totalpick=(select MIN(totalpick) from pickcombos where totalpick>=@pickqty)
end
go
exec OverPick 180

----------
select * from stock
drop table stock
drop view lifo
drop view fifo
drop view costfifo
drop view costlifo
drop view CurrStock
drop proc OverPick
drop proc RemoveQty
drop view pickcombos