ERP及进销存软件库存管理设计要点

在ERP或进销存软件设计时，常用常用需要知道某时的期初库存及期末库存，因而需要记录库存的实时进出。

设计一个表用于记录库存的实时进出。

--库存表，用于存放当前库存.

IF OBJECT_ID ('Storage',N'U') IS NOT NULL
drop table Storage
create table Storage
(
 ID  int identity(1,1),
 ProductID int not null,--物品ID
 StorageID  int not null,--仓库ID
 Amount    decimal(8,2)--当前数量
primary key(ProductID,StorageID)
)

--库存进出记录表，用于记录仓库的进出流水帐

IF OBJECT_ID ('StorageInOut',N'U') IS NOT NULL
drop table StorageInOut
create table StorageInOut
(
 ID int identity(1,1),
 InOutDate  datetime default getdate() not null,--进出时间
 ProductID  int not null,--物品ID
 StorageID  int not Null,--仓库ID
 StorageIn  decimal(8,2) default 0 not null,--进库数量
 StorageOut decimal(8,2) default 0 not null,--出库数量
 Amount     decimal(8,2) default 0 not null--结存数量
)

 

再在库存表Storage上创建一个触发器。用于把进出数据写入StorageInOut表中.

IF OBJECT_ID ('StorageLog','TR') IS NOT NULL
   DROP TRIGGER StorageLog
GO
CREATE TRIGGER StorageLog
ON Storage
AFTER INSERT, UPDATE
AS
declare   @ProductID int,
 @StorageID int,
 @Old  decimal(8,2),
 @New  decimal(8,2),
 @Amount decimal(8,2)
select @Old=Amount from deleted
select @ProductID=ProductID,@StorageID=StorageID,@New=Amount from inserted

select @Amount=Amount from StorageInOut where ID = (select Max(ID)
from StorageInOut where ProductID=@ProductID and StorageID=@StorageID)
if @Amount is null
   set @Amount=0
if @Old is not null
begin
if @New >@Old
begin
 insert StorageInOut(ProductID, StorageID,StorageIn, StorageOut,Amount)
              values(@ProductID,@StorageID,@New-@Old,0,@Amount+@New-@Old)
end else
begin
 insert StorageInOut(ProductID, StorageID,StorageIn, StorageOut,Amount)
              values(@ProductID,@StorageID,0,         @Old-@New,@Amount-@Old+@New)
end
end else
begin
insert StorageInOut(ProductID, StorageID,StorageIn, StorageOut,Amount)
              values(@ProductID,@StorageID,@New,      0,@Amount+@New)
end
GO

取某时段的期初库存量:

select * from StorageInOut where id=(select max(id) from StorageInOut where InOutDate<'2012-10-11 22:36:44')

取某时段的出入库数量：

select sum(storageOut) '出库量',sum(StorageIn)  入库量 where where InOutDate between '2012-10-11 22:36:44' and '2012-12 :23:59:59'

取某时段结存（期末）库存量:

select * from StorageInOut where id=(select max(id) from StorageInOut where between '2012-10-11 22:36:44' and '2012-12 :23:59:59')

 

这样就可以查询到任何时间的出入库数量及结存数，对移动盘点，库存管理就方便多了.