-- ==============================
-- 长登科技 www.ppfor.com
-- 唐镇波   2010-04-20
-- ------------------------------
-- 产品扩展参数值表
-- ==============================

--创建产品扩展参数值表
if not exists (select * from sysobjects where id = object_id(N'[PPFOR_TZB_ProductParamValues]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
CREATE TABLE [PPFOR_TZB_ProductParamValues] (
	[pvID] int identity(1,1) NOT NULL primary key,
	[proID] int NOT NULL ,
	[paramID] int NOT NULL REFERENCES [PPFOR_TZB_ProductParameters](paramID) ON DELETE CASCADE,
	[customValue] nvarchar(255) NULL
)
PPFOR_GO;

--设置参数值
if exists (select * from sysobjects where id = object_id(N'[PPFOR_TZB_ProductParamValues_SetValue]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [PPFOR_TZB_ProductParamValues_SetValue]
PPFOR_GO;
create procedure PPFOR_TZB_ProductParamValues_SetValue(@proID int,@paramID int,
	@customValue nvarchar(255),@pvID int out)
as
	begin
		declare @tempID int
		select @tempID=pvID from [PPFOR_TZB_ProductParamValues] where proID=@proID 
			and paramID=@paramID
		if @tempID is null
			begin
				insert into [PPFOR_TZB_ProductParamValues] (proID,paramID,customValue)
					values (@proID,@paramID,@customValue)
				set @pvID=@@identity
			end
		else
			begin
				update [PPFOR_TZB_ProductParamValues] set customValue=@customValue 
					where pvID=@tempID
				set @pvID=@tempID
			end
	end
PPFOR_GO;

--删除参数值
if exists (select * from sysobjects where id = object_id(N'[PPFOR_TZB_ProductParamValues_Delete]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [PPFOR_TZB_ProductParamValues_Delete]
PPFOR_GO;
create procedure PPFOR_TZB_ProductParamValues_Delete(@pvID int)
as
	begin
		delete from [PPFOR_TZB_ProductParamValues] where pvID=@pvID
	end
PPFOR_GO;

--根据产品ID删除参数值
if exists (select * from sysobjects where id = object_id(N'[PPFOR_TZB_ProductParamValues_DeleteByProductID]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [PPFOR_TZB_ProductParamValues_DeleteByProductID]
PPFOR_GO;
create procedure PPFOR_TZB_ProductParamValues_DeleteByProductID(@proID int)
as
	begin
		delete from [PPFOR_TZB_ProductParamValues] where proID=@proID
	end
PPFOR_GO;

--根据产品ID获取所有参数值
if exists (select * from sysobjects where id = object_id(N'[PPFOR_TZB_ProductParamValues_GetModelList]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [PPFOR_TZB_ProductParamValues_GetModelList]
PPFOR_GO;
create procedure PPFOR_TZB_ProductParamValues_GetModelList(@proID int)
as
	begin
		select * from [PPFOR_TZB_ProductParamValues] where proID=@proID and paramID= any 
			(select paramID from [PPFOR_TZB_ProductParameters] where paramType=0)
	end
PPFOR_GO;
