-- ==============================
-- 长登科技 www.ppfor.com
-- 唐镇波   2010-04-21
-- ------------------------------
-- 货品表
-- ==============================

--创建货品表
if not exists(select name from sysobjects where name='PPFOR_TZB_Goods' and type='U')
CREATE TABLE [PPFOR_TZB_Goods] (
	[ID] int IDENTITY (1, 1) NOT NULL PRIMARY KEY,
	[productID] int NOT NULL REFERENCES [PPFOR_TZB_Product]([ID]) ON DELETE CASCADE,--所属产品	
	[specValues] text not null,								--规格值	
	[code] nvarchar(18) COLLATE Chinese_PRC_CS_AS not null unique,					--货品货号
	[cost] money not null default(0),						--成本价	
	[price] money not null default(0),						--销售价	
	[store] int not null default(0),						--库存	
	[weight] decimal(19,3) not null default(0),				--重量	
	[degreePrice] text null									--会员价格
)
PPFOR_GO;

--添加货品
if exists(select name from sysobjects where name='PPFOR_TZB_Goods_Add' and type='P')
	drop procedure PPFOR_TZB_Goods_Add
PPFOR_GO;
CREATE PROCEDURE PPFOR_TZB_Goods_Add(@ID int output,@productID int,@specValues text,@code nvarchar(18),
	@cost money,@price money,@store int,@weight decimal(19,3),@degreePrice text)
 AS 
	begin
		if not exists(select [ID] from [PPFOR_TZB_Goods] where [code]=@code) 
			and not exists(select [ID] from [PPFOR_TZB_Product] where [code]=@code) 
			begin
				INSERT INTO [PPFOR_TZB_Goods]([productID],[specValues],[code],[cost],[price],[store],
					[weight],[degreePrice]) 
				VALUES (@productID,@specValues,@code,@cost,@price,@store,@weight,@degreePrice)
				SET @ID = @@IDENTITY
			end
		else
			SET @ID = 0
	end
PPFOR_GO;

--删除货品
if exists(select name from sysobjects where name='PPFOR_TZB_Goods_Delete' and type='P')
	drop procedure PPFOR_TZB_Goods_Delete
PPFOR_GO;
CREATE PROCEDURE PPFOR_TZB_Goods_Delete(@ID int)
 AS 
	DELETE [PPFOR_TZB_Goods] WHERE [ID]=@ID
PPFOR_GO;

--获取货品
if exists(select name from sysobjects where name='PPFOR_TZB_Goods_GetModel' and type='P')
	drop procedure PPFOR_TZB_Goods_GetModel
PPFOR_GO;
CREATE   PROCEDURE PPFOR_TZB_Goods_GetModel(@ID int)
 AS 
	SELECT * FROM [PPFOR_TZB_Goods] WHERE [ID]=@ID 
PPFOR_GO;

--获取货品库存
if exists(select name from sysobjects where name='PPFOR_TZB_Goods_GetStore' and type='P')
	drop procedure PPFOR_TZB_Goods_GetStore
PPFOR_GO;
CREATE   PROCEDURE PPFOR_TZB_Goods_GetStore(@ID int)
 AS 
	SELECT [store] FROM [PPFOR_TZB_Goods] WHERE [ID]=@ID 
PPFOR_GO;

--获取货品
if exists(select name from sysobjects where name='PPFOR_TZB_Goods_GetModelList' and type='P')
	drop procedure PPFOR_TZB_Goods_GetModelList
PPFOR_GO;
CREATE   PROCEDURE PPFOR_TZB_Goods_GetModelList(@productID int)
 AS 
	SELECT * FROM [PPFOR_TZB_Goods] WHERE [productID]=@productID 
PPFOR_GO;

--更新货品
if exists(select name from sysobjects where name='PPFOR_TZB_Goods_Update' and type='P')
	drop procedure PPFOR_TZB_Goods_Update
PPFOR_GO;
CREATE  PROCEDURE PPFOR_TZB_Goods_Update(@ID int,@productID int,@specValues text,@code nvarchar(18),
	@cost money,@price money,@store int,@weight decimal(19,3),@degreePrice text)
 AS 
	begin
		if not exists(select [ID] from [PPFOR_TZB_Goods] where [code]=@code and [ID]<>@ID) 
			and not exists(select [ID] from [PPFOR_TZB_Product] where [code]=@code) 
				UPDATE [PPFOR_TZB_Goods] SET [productID]=@productID,[specValues]=@specValues,
					[code]=@code,[cost]=@cost,[price]=@price,[store]=@store,[weight]=@weight,
					[degreePrice]=@degreePrice WHERE [ID]=@ID 
	end
PPFOR_GO;