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

--创建规格表
if not exists (select * from sysobjects where id = object_id(N'[PPFOR_TZB_Specification]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
CREATE TABLE [PPFOR_TZB_Specification] (
	[ID] int identity(1,1) not null primary key,						--ID
	[Name] nvarchar (20) COLLATE Chinese_PRC_CS_AS not null,			--名称
	[Description] nvarchar(255) null,		--备注描述
	[IsImage] bit not null,					--是否图片显示类型
	[IsDownList] bit not null,				--是否下拉显示
	[Order] int not null default(0),		--排序
	[BelongLanguage] int not null REFERENCES [PPFOR_TZB_Language]([ID]) ON DELETE CASCADE, --所属语言
)
PPFOR_GO;

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

--添加规格
if exists(select name from sysobjects where name='PPFOR_TZB_Specification_Add' and type='P')
	drop procedure [PPFOR_TZB_Specification_Add]
PPFOR_GO;
create procedure PPFOR_TZB_Specification_Add(@ID int out, @Name nvarchar (20),
	@Description nvarchar (255),@IsImage bit,@IsDownList bit,@Order int,@BelongLanguage int)
as		 
	begin
		if not exists ( select [ID] from [PPFOR_TZB_Specification] where [Name]=@Name 
			and [BelongLanguage]=@BelongLanguage) 
			begin
				insert into [PPFOR_TZB_Specification] ([Name],[Description],[IsImage],
					[IsDownList],[Order],[BelongLanguage]) values (@Name,@Description,
					@IsImage,@IsDownList,@Order,@BelongLanguage)
				SET @ID = @@IDENTITY
			end
		else
			SET @ID = 0
	end
PPFOR_GO;

--更新规格
if exists(select name from sysobjects where name='PPFOR_TZB_Specification_Update' and type='P')
	drop procedure [PPFOR_TZB_Specification_Update]
PPFOR_GO;
create procedure PPFOR_TZB_Specification_Update(@ID int, @Name nvarchar (20),
	@Description nvarchar (255),@IsImage bit,@IsDownList bit,@Order int,@BelongLanguage int)
as
	begin	
		if not exists ( select [ID] from [PPFOR_TZB_Specification] where [Name]=@Name 
			and [ID]<>@ID and [BelongLanguage]=@BelongLanguage) 
			begin
				update [PPFOR_TZB_Specification] set [Name]=@Name,[Description]=@Description,
				[IsImage]=@IsImage,[IsDownList]=@IsDownList,[Order]=@Order where [ID]=@ID 
				and [BelongLanguage]=@BelongLanguage
			end
	end
PPFOR_GO;

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

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

--获取规格
if exists(select name from sysobjects where name='PPFOR_TZB_Specification_GetModel' and type='P')
	drop procedure [PPFOR_TZB_Specification_GetModel]
PPFOR_GO;
create procedure PPFOR_TZB_Specification_GetModel(@ID int)
as
	begin		
		select [ID],[Name],[Description],[IsImage],[IsDownList],[Order],[BelongLanguage]
			from [PPFOR_TZB_Specification] where [ID]=@ID
	end
PPFOR_GO;

--获取所有规格
if exists(select name from sysobjects where name='PPFOR_TZB_Specification_GetModelList' and type='P')
	drop procedure [PPFOR_TZB_Specification_GetModelList]
PPFOR_GO;
create procedure PPFOR_TZB_Specification_GetModelList(@BelongLanguage int)
as
	begin		
		select [ID],[Name],[Description],[IsImage],[IsDownList],[Order],[BelongLanguage]
			from [PPFOR_TZB_Specification] where [BelongLanguage]=@BelongLanguage 
			order by [Order] asc, [ID] asc
	end
PPFOR_GO;

--是否允许删除该规格
if exists(select name from sysobjects where name='PPFOR_TZB_Specification_IsEnableDelete' and type='P')
	drop procedure [PPFOR_TZB_Specification_IsEnableDelete]
PPFOR_GO;
create procedure PPFOR_TZB_Specification_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;
