stock.txt

一款关于库存管理的信息系统,不成熟,希望大家多交流

CREATE DATABASE Stock
GO

USE Stock
GO
CREATE TABLE Client
  (Cid	int PRIMARY KEY IDENTITY,
   Cname	varchar(50) NOT NULL,
   Ctype	tinyint,
   Contact 	varchar(30),
   Address	varchar(50),
   Postcode	varchar(10),
   Phone	varchar(30),
   Fax	varchar(30),
   Memo	varchar(100)
  )
GO
USE Stock
GO
CREATE TABLE Product
  (Pid 		int PRIMARY KEY IDENTITY,
   Pname 	varchar(50) NOT NULL,
   TypeId	int,
   Pstyle	varchar(50),
   Punit	varchar(10),
   Pprice	decimal(15, 2),
   Plow	int,
   Phigh	int,
   Valid	int,
   AlarmDays	int
  )
GO
USE Stock
GO
CREATE TABLE ProInStore
  (SpId	int PRIMARY KEY IDENTITY,
   Pid	int NOT NULL,
   Pprice	decimal(15, 2),
   Pnum	int,
   MakeDate	char(10),
   Sid	int
  )
GO
USE Stock
GO
CREATE TABLE ProType
  (TypeId	int PRIMARY KEY IDENTITY,
   TypeName	varchar(50) NOT NULL,
   UpperId	int
  )
GO
USE Stock
GO
CREATE TABLE Storehouse
  (Sid	int PRIMARY KEY IDENTITY,
   Sname    	varchar(50) NOT NULL,
   Memo	varchar(100)
  )
GO
USE Stock
GO
CREATE TABLE StoreIn
  (SiType	varchar(20),
   Pid	int,
   MakeDate	char(10),
   Pprice	decimal(15,2),
   Pnum	int,
   Cid	int,
   Sid	int,
   EmpName	varchar(50),
   OptDate	char(10)
  )
GO
USE Stock
GO
CREATE TABLE TakeOut
  (Ttype	varchar(20) NOT NULL,
   Pid	int,
   Pprice	decimal(15,2),
   Pnum	int,
   Cid	int,
   Sid	int,
   EmpName	varchar(50),
   OptDate	char(20)
  )
GO






USE Stock
GO
CREATE TABLE Users
  (UserName  	varchar(40) PRIMARY KEY,
   Pwd	varchar(40) NOT NULL,
   User_Type  	tinyint NOT NULL
  )
GO
INSERT INTO Users Values('Admin', '888888', 1)
GO


USE Stock
GO
CREATE VIEW dbo.Total_Num
AS
SELECT dbo.ProInStore.Pid, SUM(dbo.ProInStore.Pnum) AS Total
FROM dbo.ProInStore INNER JOIN
      dbo.Product ON dbo.ProInStore.Pid = dbo.Product.Pid
GROUP BY dbo.ProInStore.Pid 
GO

USE Stock
GO
CREATE VIEW dbo.Report2
AS
SELECT SiType, Pid, Pprice, Pnum, (Pprice * Pnum) As Amount, Cid, Sid, EmpName, OptDate FROM StoreIn
UNION 
SELECT Ttype, Pid, Pprice, Pnum, (Pprice * Pnum) As Amount, Cid, Sid, EmpName, OptDate FROM TakeOut
GO

USE Stock
GO
CREATE VIEW dbo.v_ProInStore
AS
SELECT p.Pid, SUM(s.Pnum) AS SumNum
FROM dbo.Product p INNER JOIN
      dbo.ProInStore s ON p.Pid = s.Pid
GROUP BY p.Pid
GO

USE Stock
GO
CREATE VIEW dbo.v_storein
AS
SELECT p.Pid, SUM(i.Pnum) AS SumNum, SUM(i.Pnum * i.Pprice) 
      AS SumPrice
FROM dbo.Product p INNER JOIN
      dbo.StoreIn i ON p.Pid = i.Pid
GROUP BY p.Pid
GO
USE Stock
GO
CREATE VIEW dbo.v_takeout
AS
SELECT p.Pid, SUM(t.Pnum) AS SumNum, SUM(t.Pnum * t.Pprice) 
      AS SumPrice
FROM dbo.Product p INNER JOIN
      dbo.TakeOut t ON p.Pid = t.Pid
GROUP BY p.Pid
GO
USE Stock
GO
CREATE VIEW dbo.Valid
AS
SELECT dbo.ProInStore.SpId AS 库存记录编号, dbo.Product.Pname AS 产品名称, 
      dbo.ProInStore.Pprice AS 产品价格, dbo.ProInStore.Pnum AS 产品数量, 
      dbo.ProInStore.MakeDate AS 生产日期, dbo.Storehouse.Sname AS 仓库名称, 
      ROUND(DATEDIFF(day, DATEADD(day, dbo.Product.Valid, dbo.ProInStore.MakeDate), 
      GETDATE()), 0) AS 距离失效期的天数
FROM dbo.ProInStore INNER JOIN
      dbo.Product ON dbo.ProInStore.Pid = dbo.Product.Pid AND DATEDIFF(day, 
      GETDATE(), dbo.ProInStore.MakeDate) 
      >= dbo.Product.Valid - dbo.Product.AlarmDays INNER JOIN
      dbo.Storehouse ON dbo.ProInStore.Sid = dbo.Storehouse.Sid
GO