进销存管理.sql

vfp开发的进销存管理系统,运行相应系统的Setup安装程序

		[货号]
	)
GO

ALTER TABLE [dbo].[调拨单历史] ADD 
	CONSTRAINT [调拨单历史_货号_fk] FOREIGN KEY 
	(
		[货号]
	) REFERENCES [dbo].[商品清单] (
		[货号]
	)
GO

ALTER TABLE [dbo].[调货申请] ADD 
	CONSTRAINT [调货申请_货号_fk] FOREIGN KEY 
	(
		[货号]
	) REFERENCES [dbo].[商品清单] (
		[货号]
	)
GO

ALTER TABLE [dbo].[进货单] ADD 
	CONSTRAINT [进货单_供货商_fk] FOREIGN KEY 
	(
		[供货商号]
	) REFERENCES [dbo].[供货商清单] (
		[供货商号]
	)
GO

ALTER TABLE [dbo].[进货单历史] ADD 
	CONSTRAINT [进货单历史_供货商_fk] FOREIGN KEY 
	(
		[供货商号]
	) REFERENCES [dbo].[供货商清单] (
		[供货商号]
	)
GO

ALTER TABLE [dbo].[进货单明细] ADD 
	CONSTRAINT [FK_进货单明细_进货单] FOREIGN KEY 
	(
		[进货单号]
	) REFERENCES [dbo].[进货单] (
		[编号]
	),
	CONSTRAINT [进货单明细_货号_fk] FOREIGN KEY 
	(
		[货号]
	) REFERENCES [dbo].[商品清单] (
		[货号]
	)
GO

ALTER TABLE [dbo].[进货单明细历史] ADD 
	CONSTRAINT [进货单明细历史_货号_fk] FOREIGN KEY 
	(
		[货号]
	) REFERENCES [dbo].[商品清单] (
		[货号]
	)
GO

ALTER TABLE [dbo].[进销存汇总表] ADD 
	CONSTRAINT [进销存汇总表_货号_fk] FOREIGN KEY 
	(
		[货号]
	) REFERENCES [dbo].[商品清单] (
		[货号]
	)
GO

ALTER TABLE [dbo].[采购合同] ADD 
	CONSTRAINT [采购合同_供货商_fk] FOREIGN KEY 
	(
		[供货商号]
	) REFERENCES [dbo].[供货商清单] (
		[供货商号]
	),
	CONSTRAINT [采购合同_货号_fk] FOREIGN KEY 
	(
		[货号]
	) REFERENCES [dbo].[商品清单] (
		[货号]
	)
GO

ALTER TABLE [dbo].[采购订单] ADD 
	CONSTRAINT [采购订单_供货商_fk] FOREIGN KEY 
	(
		[供货商号]
	) REFERENCES [dbo].[供货商清单] (
		[供货商号]
	)
GO

ALTER TABLE [dbo].[采购订单历史] ADD 
	CONSTRAINT [采购订单历史_供货商_fk] FOREIGN KEY 
	(
		[供货商号]
	) REFERENCES [dbo].[供货商清单] (
		[供货商号]
	)
GO

ALTER TABLE [dbo].[采购订单明细] ADD 
	CONSTRAINT [FK_采购订单明细_采购订单] FOREIGN KEY 
	(
		[订单号]
	) REFERENCES [dbo].[采购订单] (
		[编号]
	),
	CONSTRAINT [采购订单明细_货号_fk] FOREIGN KEY 
	(
		[货号]
	) REFERENCES [dbo].[商品清单] (
		[货号]
	)
GO

ALTER TABLE [dbo].[采购订单明细历史] ADD 
	CONSTRAINT [采购订单明细历史_货号_fk] FOREIGN KEY 
	(
		[货号]
	) REFERENCES [dbo].[商品清单] (
		[货号]
	)
GO

ALTER TABLE [dbo].[销售单] ADD 
	CONSTRAINT [销售单_客户编号_fk] FOREIGN KEY 
	(
		[客户编号]
	) REFERENCES [dbo].[客户清单] (
		[客户编号]
	)
GO

ALTER TABLE [dbo].[销售单历史] ADD 
	CONSTRAINT [销售单历史_客户编号_fk] FOREIGN KEY 
	(
		[客户编号]
	) REFERENCES [dbo].[客户清单] (
		[客户编号]
	)
GO

ALTER TABLE [dbo].[销售单明细] ADD 
	CONSTRAINT [FK_销售单明细_销售单] FOREIGN KEY 
	(
		[销售单号]
	) REFERENCES [dbo].[销售单] (
		[编号]
	),
	CONSTRAINT [销售单明细_货号_fk] FOREIGN KEY 
	(
		[货号]
	) REFERENCES [dbo].[商品清单] (
		[货号]
	)
GO

ALTER TABLE [dbo].[销售单明细历史] ADD 
	CONSTRAINT [销售单明细历史_货号_fk] FOREIGN KEY 
	(
		[货号]
	) REFERENCES [dbo].[商品清单] (
		[货号]
	)
GO

ALTER TABLE [dbo].[销售合同] ADD 
	CONSTRAINT [销售合同_货号_fk] FOREIGN KEY 
	(
		[货号]
	) REFERENCES [dbo].[商品清单] (
		[货号]
	),
	CONSTRAINT [销售合同_客户编号_fk] FOREIGN KEY 
	(
		[客户编号]
	) REFERENCES [dbo].[客户清单] (
		[客户编号]
	)
GO

ALTER TABLE [dbo].[销售日报] ADD 
	CONSTRAINT [销售日报_货号_fk] FOREIGN KEY 
	(
		[货号]
	) REFERENCES [dbo].[商品清单] (
		[货号]
	)
GO

ALTER TABLE [dbo].[销售订单] ADD 
	CONSTRAINT [销售订单_客户编号_fk] FOREIGN KEY 
	(
		[客户编号]
	) REFERENCES [dbo].[客户清单] (
		[客户编号]
	)
GO

ALTER TABLE [dbo].[销售订单历史] ADD 
	CONSTRAINT [销售订单历史_客户编号_fk] FOREIGN KEY 
	(
		[客户编号]
	) REFERENCES [dbo].[客户清单] (
		[客户编号]
	)
GO

ALTER TABLE [dbo].[销售订单明细] ADD 
	CONSTRAINT [FK_销售订单明细_销售订单] FOREIGN KEY 
	(
		[订单号]
	) REFERENCES [dbo].[销售订单] (
		[编号]
	),
	CONSTRAINT [销售订单明细_货号_fk] FOREIGN KEY 
	(
		[货号]
	) REFERENCES [dbo].[商品清单] (
		[货号]
	)
GO

ALTER TABLE [dbo].[销售订单明细历史] ADD 
	CONSTRAINT [销售订单明细历史_货号_fk] FOREIGN KEY 
	(
		[货号]
	) REFERENCES [dbo].[商品清单] (
		[货号]
	)
GO

ALTER TABLE [dbo].[销退货单] ADD 
	CONSTRAINT [销退货单_货号_fk] FOREIGN KEY 
	(
		[货号]
	) REFERENCES [dbo].[商品清单] (
		[货号]
	)
GO

ALTER TABLE [dbo].[销退货单历史] ADD 
	CONSTRAINT [销退货单历史_货号_fk] FOREIGN KEY 
	(
		[货号]
	) REFERENCES [dbo].[商品清单] (
		[货号]
	)
GO

ALTER TABLE [dbo].[预付款] ADD 
	CONSTRAINT [预付款_供货商_fk] FOREIGN KEY 
	(
		[供货商号]
	) REFERENCES [dbo].[供货商清单] (
		[供货商号]
	)
GO

ALTER TABLE [dbo].[预收款] ADD 
	CONSTRAINT [预收款_供货商_fk] FOREIGN KEY 
	(
		[供货商号]
	) REFERENCES [dbo].[供货商清单] (
		[供货商号]
	)
GO




--- 下面是存储过程

CREATE PROCEDURE sf_进货单
AS
begin transaction
	-- 库存库中没有,增加记录
	INSERT INTO 库存库(货号,仓库,库存数量,库存金额,库存单价) SELECT DISTINCT j.货号, j.仓库, 0,0,0
			FROM 进货单明细 AS J left join 库存库 as k on ( j.仓库=k.仓库 and j.货号=k.货号 )
			where k.货号 is null
	-- 修改库存信息
	UPDATE 库存库 SET 库存单价=case when 库存数量<=0 or (库存数量+数量ALL)<=0 then 进价
					else (库存金额+税价合计ALL)/(库存数量+数量ALL) end ,
			库存数量=库存数量+数量ALL,
			库存金额=case when 库存数量<=0 or (库存数量+数量ALL)<=0 
				then 进价*(库存数量+数量ALL) else (库存金额+税价合计ALL) end ,
			最新进价=进价
		FROM 
		(SELECT 仓库,货号,'数量ALL'=sum(进货数量), '进价' = sum(税价合计)/sum(进货数量), 
			'税价合计ALL'=sum(税价合计) FROM 进货单明细 GROUP BY 仓库,货号) AS LSJ
		WHERE  库存库.仓库=LSj.仓库 AND 库存库.货号=LSj.货号
	-- 加入应付款
	INSERT INTO 应付款(编号, 进货单号, 货号, 供货商号, 数量, 进货单价, 金额, 进货日期, 状态)
		SELECT 	'付'+a.编号,b.编号,a.货号,b.供货商号,进货数量,进价,a.税价合计,进货日期,'应付' 
		FROM 进货单明细 as a, 进货单 as b
		where a.进货单号=b.编号 
	-- 加入历史
	insert into 进货单历史 select * from 进货单
	insert into 进货单明细历史 select * from 进货单明细
	-- 清除进货单
	delete from 进货单明细
	delete from 进货单
commit
go

CREATE PROCEDURE sf_销售单
AS
begin transaction
	-- 修改库存信息
	UPDATE 库存库 SET 库存数量=库存数量-数量ALL, 库存金额=库存单价*(库存数量-数量ALL)
		FROM (SELECT 仓库,货号,'数量ALL'=sum(销售数量) FROM 销售单明细 
		GROUP BY 仓库,货号) AS LSJ
		WHERE  库存库.仓库=LSj.仓库 AND 库存库.货号=LSj.货号
	-- 加入应收款
	INSERT INTO 应收款(编号, 销售单号, 货号, 客户编号, 数量, 销售价, 金额, 销售日期, 状态)
		SELECT 	'收'+a.编号,b.编号,a.货号,b.客户编号,销售数量,销售价,a.税价合计,销售日期,'应收' 
		FROM 销售单明细 as a, 销售单 as b
		where a.销售单号=b.编号 
	-- 加入历史
	insert into 销售单历史 select * from 销售单
	insert into 销售单明细历史 select * from 销售单明细
	-- 清除销售单
	delete from 销售单明细
	delete from 销售单
commit
go


--- 初始化数据
insert into 用户清单 values('1','系统管理','sys','M','sys')
insert into 用户清单 values('zs','采购部','张三','M','sys')
insert into 用户清单 values('ls','销售部','李四','M','sys')
insert into 用户清单 values('ww','财务部','王五','M','sys')
go
insert into 仓库清单(仓库号,仓库名) values('1','1库')
insert into 仓库清单(仓库号,仓库名) values('2','2库')
insert into 仓库清单(仓库号,仓库名) values('3','3库')
insert into 仓库清单(仓库号,仓库名) values('4','4库')
insert into 仓库清单(仓库号,仓库名) values('5','5库')
go
insert into 业务员清单(业务员号,姓名,性别) values('zs','张三','M')
insert into 业务员清单(业务员号,姓名,性别)  values('ls','李四','M')
insert into 业务员清单(业务员号,姓名,性别)  values('ww','王五','M')
go