[貨號]
	)
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