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

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

--设置参数内容值
if exists (select * from sysobjects where id = object_id(N'[PPFOR_TZB_ProductContentValues_SetValue]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [PPFOR_TZB_ProductContentValues_SetValue]
PPFOR_GO;
create procedure PPFOR_TZB_ProductContentValues_SetValue(@proID int,@paramID int,
	@customContent text,@pcID int out)
as
	begin
		declare @tempID int
		select @tempID=pcID from [PPFOR_TZB_ProductContentValues] where proID=@proID 
			and paramID=@paramID
		if @tempID is null
			begin
				insert into [PPFOR_TZB_ProductContentValues] (proID,paramID,customContent)
					values (@proID,@paramID,@customContent)
				set @pcID=@@identity
			end
		else
			begin
				update [PPFOR_TZB_ProductContentValues] set customContent=@customContent
				 	where pcID=@tempID
				set @pcID=@tempID
			end
	end
PPFOR_GO;

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

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

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

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