vb 进销存 系统实例

【实例截图】

【核心代码】

CREATE TABLE Dealing (
	DealingID int identity(1,1) Primary key ,
	DealingDate datetime NOT NULL ,
	DealingPrice money NOT NULL ,
	UserName varchar(25) NULL ,
	MemberCard varchar(20) NULL
)
<->
CREATE TABLE Factory (
	FactoryID int identity(1,1) Primary key ,
	FactoryName varchar(50) Unique NOT NULL ,
	FactoryAddress varchar(250) NULL ,
	FactoryPhone varchar(50) NULL
)
<->
CREATE TABLE Member (
	MemberID int identity(1,1) Primary key ,
	MemberCard varchar(20) Unique NOT NULL ,
	TotalCost money NOT NULL ,
	RegDate datetime NOT NULL
)
<->
CREATE TABLE MerchInfo (
	MerchID int identity(1,1) Primary key ,
	MerchName varchar(50) Unique NOT NULL ,
	MerchPrice money NOT NULL ,
	MerchNum int NOT NULL ,
	CautionNum int NOT NULL ,
	PlanNum int NOT NULL ,
	BarCode varchar(20) Unique NOT NULL ,
	SalesProPrice money NULL ,
	SalesProDateS datetime NULL ,
	SalesProDateE datetime NULL ,
	AllowAbate int NOT NULL ,
	AllowSale int NOT NULL ,
	FactoryID int NOT NULL ,
	ProvideID int NOT NULL
)
<->
CREATE TABLE Stock (
	StockID int identity(1,1) Primary key ,
	MerchID int NOT NULL ,
	MerchNum int NOT NULL ,
	MerchPrice money NULL ,
	TotalPrice money NULL ,
	PlanDate datetime NULL ,
	StockDate datetime NULL,
	StockState int NOT NULL
)
<->
CREATE TABLE StockTemp (
	ID int identity(1,1) Primary key ,
	MerchName varchar(50) NULL ,
	MerchNum int NULL ,
	PlanDate datetime NULL ,
	FactoryName varchar(50) NULL,
	ProvideName varchar(50) NULL
)
<->
CREATE TABLE Provide (
	ProvideID int identity(1,1) Primary key ,
	ProvideName varchar(50) Unique NOT NULL ,
	ProvideAddress varchar(250) NULL ,
	ProvidePhone varchar(25) NULL
)
<->
CREATE TABLE Sale (
	SaleID int identity(1,1) Primary key ,
	MerChID int NOT NULL ,
	SaleDate datetime NOT NULL ,
	SaleNum int NOT NULL,
	SalePrice money NOT NULL
)
<->
CREATE TABLE SaleTemp (
	ID int identity(1,1) Primary key ,
	SaleDate int NULL ,
	SaleNumT int NULL,
	SaleMoneyT money NULL
)
<->
CREATE TABLE [User] (
	UserID int identity(1,1) Primary key ,
	UserName varchar(25) Unique NOT NULL ,
	UserPW varchar(50) NOT NULL ,
	UserStyle int NOT NULL
)
<->
ALTER TABLE MerchInfo ADD
	CONSTRAINT [FK_MerchInfo_Factory] FOREIGN KEY
	(
		[FactoryID]
	) REFERENCES Factory (
		[FactoryID]
	) ON DELETE CASCADE ,
	CONSTRAINT [FK_MerchInfo_Provide] FOREIGN KEY
	(
		[ProvideID]
	) REFERENCES Provide (
		[ProvideID]
	) ON DELETE CASCADE 
<->
ALTER TABLE Sale ADD
	CONSTRAINT [FK_Sale_MerchInfo] FOREIGN KEY
	(
		[MerChID]
	) REFERENCES MerchInfo (
		[MerchID]
	) ON DELETE CASCADE 
<->
ALTER TABLE Stock ADD
	CONSTRAINT [FK_Stock_MerchInfo] FOREIGN KEY
	(
		[MerchID]
	) REFERENCES MerchInfo (
		[MerchID]
	) ON DELETE CASCADE 
<->
CREATE nonclustered INDEX IX_Dealing ON Dealing(DealingID, DealingDate)
<->
CREATE nonclustered INDEX IX_MerchInfo ON MerchInfo(MerchID)
<->
CREATE nonclustered INDEX IX_Sale ON Sale(SaleID, SaleDate)
<->
CREATE nonclustered INDEX IX_Stock ON Stock(StockID, StockDate, MerchID)
<->
CREATE VIEW v_Dealing
AS
SELECT DealingDate as 交易日期,
       UserName as 员工名称,
       MemberCard as 会员卡号,
       DealingPrice as 交易金额
FROM Dealing
<->
CREATE VIEW v_Sale
AS
SELECT MerchInfo.MerchName as 商品名称,
       MerchInfo.BarCode as 条形码,
       MerchInfo.MerchPrice as 商品价格,
       Sale.SalePrice as 销售价格,
       Sale.SaleNum as 销售数量,
       Sale.SaleDate as 销售日期
FROM Sale INNER JOIN
      MerchInfo ON Sale.MerChID = MerchInfo.MerchID
<->
CREATE VIEW v_Stock
AS
SELECT MerchInfo.MerchName as 商品名称,
       MerchInfo.BarCode as 条形码,
       Factory.FactoryName as 厂商,
       Provide.ProvideName as 供货商,
       Stock.MerchPrice as 入库价格,
       Stock.MerchNum as 入库数量,
       Stock.TotalPrice as 入库总额,
       Stock.StockDate as 入库日期
FROM Stock,MerchInfo,Provide,Factory
Where Stock.MerchID = MerchInfo.MerchID
      and Provide.ProvideID=MerchInfo.ProvideID
      and Factory.FactoryID=MerchInfo.FactoryID
      and Stock.StockState=1
<->
CREATE VIEW v_PlanStock
AS
SELECT Stock.StockID as SID,
       MerchInfo.MerchName as 商品名称,
       MerchInfo.BarCode as 条形码,
       Factory.FactoryName as 厂商,
       Provide.ProvideName as 供货商,
       Stock.MerchNum as 计划进货数量,
       Stock.PlanDate as 计划进货日期
FROM Stock,MerchInfo,Provide,Factory
Where Stock.MerchID = MerchInfo.MerchID
      and Provide.ProvideID=MerchInfo.ProvideID
      and Factory.FactoryID=MerchInfo.FactoryID
      and Stock.StockState=0