-- ==============================
-- 长登科技 www.ppfor.com
-- 唐镇波   2010-05-08
-- ------------------------------
-- 规格值表
-- ==============================

--创建规格值表
if not exists (select * from sysobjects where id = object_id(N'[PPFOR_TZB_SpecValue]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
CREATE TABLE [PPFOR_TZB_SpecValue] (
	[ID] int identity(1,1) not null primary key,						--ID
	[SID] int not null REFERENCES [PPFOR_TZB_Specification]([ID]) ON DELETE CASCADE, --规格ID
	[Value] nvarchar (30) COLLATE Chinese_PRC_CS_AS not null,			--规格值
	[Image] nvarchar(255) null,				--规格图片
	[Order] int not null default(0)			--排序
)
PPFOR_GO;

--是否存在规格值名
if exists(select name from sysobjects where name='PPFOR_TZB_SpecValue_IsExists' and type='P')
	drop procedure [PPFOR_TZB_SpecValue_IsExists]
PPFOR_GO;
create procedure PPFOR_TZB_SpecValue_IsExists(@SID int,@Value nvarchar (30),@retValue bit out)
as
	begin			
		if exists ( select [ID] from [PPFOR_TZB_SpecValue] where [SID]=@SID and [Value]=@Value) 
			set @retValue=1
		else
			set @retValue=0
	end
PPFOR_GO;

--添加规格值
if exists(select name from sysobjects where name='PPFOR_TZB_SpecValue_Add' and type='P')
	drop procedure [PPFOR_TZB_SpecValue_Add]
PPFOR_GO;
create procedure PPFOR_TZB_SpecValue_Add(@ID int out,@SID int, @Value nvarchar (30),
	@Image nvarchar (255),@Order int)
as		 
	begin
		if not exists ( select [ID] from [PPFOR_TZB_SpecValue] where [SID]=@SID
			and [Value]=@Value) 			
			begin			
				insert into [PPFOR_TZB_SpecValue] ([SID],[Value],[Image],[Order])
					 values (@SID,@Value,@Image,@Order)
				SET @ID = @@IDENTITY
			end
		else
			SET @ID = 0
	end
PPFOR_GO;

--更新规格值
if exists(select name from sysobjects where name='PPFOR_TZB_SpecValue_Update' and type='P')
	drop procedure [PPFOR_TZB_SpecValue_Update]
PPFOR_GO;
create procedure PPFOR_TZB_SpecValue_Update(@ID int,@SID int, @Value nvarchar (30),
	@Image nvarchar (255),@Order int)
as
	begin	
		if not exists ( select [ID] from [PPFOR_TZB_SpecValue] where [SID]=@SID 
				and [ID]<>@ID and [Value]=@Value) 	
			begin
				update [PPFOR_TZB_SpecValue] set [Value]=@Value,[Image]=@Image,
				[Order]=@Order where [SID]=@SID and [ID]=@ID	
			end		
	end
PPFOR_GO;

--更新规格值排序
if exists(select name from sysobjects where name='PPFOR_TZB_SpecValue_UpdateOrder' and type='P')
	drop procedure [PPFOR_TZB_SpecValue_UpdateOrder]
PPFOR_GO;
create procedure PPFOR_TZB_SpecValue_UpdateOrder(@ID int,@Order int)
as
	begin	
		update [PPFOR_TZB_SpecValue] set [Order]=@Order where [ID]=@ID	
	end
PPFOR_GO;

--删除规格值
if exists(select name from sysobjects where name='PPFOR_TZB_SpecValue_Delete' and type='P')
	drop procedure [PPFOR_TZB_SpecValue_Delete]
PPFOR_GO;
create procedure PPFOR_TZB_SpecValue_Delete(@ID int)
as
	begin		
		delete [PPFOR_TZB_SpecValue] where [ID]=@ID
	end
PPFOR_GO;

--获取规格值
if exists(select name from sysobjects where name='PPFOR_TZB_SpecValue_GetModel' and type='P')
	drop procedure [PPFOR_TZB_SpecValue_GetModel]
PPFOR_GO;
create procedure PPFOR_TZB_SpecValue_GetModel(@ID int)
as
	begin		
		select [ID],[SID],[Value],[Image],[Order]
			from [PPFOR_TZB_SpecValue] where [ID]=@ID
	end
PPFOR_GO;

--获取所有规格值
if exists(select name from sysobjects where name='PPFOR_TZB_SpecValue_GetModelList' and type='P')
	drop procedure [PPFOR_TZB_SpecValue_GetModelList]
PPFOR_GO;
create procedure PPFOR_TZB_SpecValue_GetModelList(@SID int)
as
	begin		
		select [ID],[SID],[Value],[Image],[Order]
			from [PPFOR_TZB_SpecValue] where [SID]=@SID order by [Order] asc, [ID] asc
	end
PPFOR_GO;

--是否允许删除该规格值
if exists(select name from sysobjects where name='PPFOR_TZB_SpecValue_IsEnableDelete' and type='P')
	drop procedure [PPFOR_TZB_SpecValue_IsEnableDelete]
PPFOR_GO;
create procedure PPFOR_TZB_SpecValue_IsEnableDelete(@ID int,@retValue bit out)
as
	begin		
		declare @Temp int				
		select @Temp=count([ID]) from [PPFOR_TZB_Product] where [spec] like 
			('%[,:]'+ rtrim(ltrim(str(@ID)))+'[,;]%')
		if @Temp>0
			set @retValue=0
		else
			set @retValue=1
	end
PPFOR_GO;
