【正文】 DEFAULT(getdate()),StoremanId int NOT NULL,CustomerId int NOT NULL,FOREIGN KEY (GoodsId) REFERENCES Goods (GoodsId),FOREIGN KEY (StoremanId) REFERENCES Storeman (StoremanId),FOREIGN KEY (CustomerId) REFERENCES Customer (CustomerId))h) 貨品盤點(diǎn)信息表建立CREATE TABLE CheckInfo (CheckId int IDENTITY(1,1) NOT NULL PRIMARY KEY,GoodsId int NOT NULL,CheckAmount int NOT NULL,CheckDate datetime NOT NULL DEFAULT(getdate()),StoremanId int NOT NULL,CustomerId int NOT NULL,FOREIGN KEY (GoodsId) REFERENCES Goods (GoodsId),FOREIGN KEY (StoremanId) REFERENCES Storeman (StoremanId),FOREIGN KEY (CustomerId) REFERENCES Customer (CustomerId))i) 庫存上下限信息表建立CREATE TABLE StorehouseAlert (GoodsId int NOT NULL PRIMARY KEY,UpAlert int NOT NULL,DownAlert int NOT NULL,FOREIGN KEY (GoodsId) REFERENCES Goods (GoodsId))j) 供貨客戶視圖建立CREATE VIEW View_SupplyCustomer ASSelect CustomerId,CustomerName,Address,Contact,ContactPhone,MemoFROM Customer Where CustomerType=1 or CustomerType=3k) 收貨客戶視圖建立CREATE VIEW View_BuyCustomer ASSelect CustomerId,CustomerName,Address,Contact,ContactPhone,MemoFROM Customer Where CustomerType=2 or CustomerType=3l) 庫存盤點(diǎn)存儲過程建立CREATE PROCEDURE Procedure_storecheck storemanid intASDeclare lastcheckdate datetime Select lastcheckdate=max(Checkdate) from CheckInfoIf ROWCOUNT =0 Insert into CheckInfo(GoodsId,CheckAmount,CheckDate,StoremanId) Select Goodsid,CheckAmount=(select isnull(sum(EntryAmount),0) from EntryInfo where GoodsId=)(select isnull(sum(ExportAmount),0) from ExportInfo where GoodsId=),getdate(),storemanid from Goods aElse Insert into CheckInfo(GoodsId,CheckAmount,CheckDate,StoremanId) Select Goodsid,CheckAmount=(select isnull(CheckAmount,0) from Checkinfo where GoodsId= and Checkdate=lastcheckdate)+(select isnull(sum(EntryAmount),0) from EntryInfo where GoodsId= and EntryDate=lastcheckdate)(select isnull(sum(ExportAmount),0) from ExportInfowhere GoodsId= and ExportDate=lastcheckdate),getdate(),storemanid from Goods am) 實(shí)時(shí)庫存情況存儲過程建立CREATE PROCEDURE Procedure_storereal ASDeclare lastcheckdate datetime Select lastcheckdate=max(Checkdate) from CheckInfoIf ROWCOUNT =0 Select ,CheckAmount=(select isnull(sum(EntryAmount),0) from EntryInfo where GoodsId=)(select isnull(sum(ExportAmount),0) from ExportInfo where GoodsId=) from Goods a,Measure b where =ElseSelect ,CheckAmount=(select isnull(CheckAmount,0) from Checkinfo where GoodsId= and Checkdate=lastcheckdate)+(select isnull(sum(EntryAmount),0) from EntryInfo where GoodsId= and EntryDate=lastcheckdate)(select isnull(sum(ExportAmount),0) from ExportInfo where GoodsId= and ExportDate=lastcheckdate) from Goods a,Measure b where =n) 查詢庫存異常情況存儲過程建立CREATE PROCEDURE Procedure_storealert ASCreate TABLE 1(Goodsid int,CheckAmount int,UpAlert int,DownAlert int)Declare lastcheckdate datetime Select lastcheckdate=max(Checkdate) from CheckInfoIf ROWCOUNT =0 Insert into 1(Goodsid,CheckAmount,UpAlert,DownAlert) Select ,CheckAmount=(select isnull(sum(EntryAmount),0) from EntryInfo where GoodsId=)(select isnull(sum(ExportAmount),0) from ExportInfo where GoodsIs=), from Goods a,StorehouseAlert b where = Else Insert into 1(Goodsid,CheckAmount,UpAlert,DownAlert) Select ,CheckAmount=(select isnull(CheckAmount,0) from Checkinfo where GoodsId= and Checkdate=lastcheckdate)+(select isnull(sum(EntryAmount),0) from EntryInfo where GoodsId= and EntryDate=lastcheckdate)(select isnull(sum(ExportAmount),0) from ExportInfo where GoodsId= and ExportDate=lastcheckdate) , from