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

--创建产品类型表
if not exists(select name from sysobjects where name='PPFOR_TZB_ProductType' and type='U')
CREATE TABLE [PPFOR_TZB_ProductType] (
	[ID] int IDENTITY (1, 1) NOT NULL PRIMARY KEY,
	[belongType] int NOT NULL ,
	[productTypeName] nvarchar (200) COLLATE Chinese_PRC_CS_AS NOT NULL ,
	[productTemplate] nvarchar (255) NULL ,
	[listTemplate] nvarchar (255) NOT NULL ,
	[updateDate] datetime NOT NULL ,
	[sort] int not NULL ,
	[show] bit NOT NULL ,
	[pageKeyWord] nvarchar (255) COLLATE Chinese_PRC_CI_AS NULL ,
	[pageDescription] nvarchar (255) COLLATE Chinese_PRC_CI_AS NULL ,
	[belongMenu] int NOT NULL ,
	[typeDescription] text NULL ,
	[image] nvarchar(255) NULL ,
	[paramIDs] text NULL ,
	[contentIDs] text NULL,
	[brandIDs] text null,
	[specIDs] text null,
	[htmlName] nvarchar(255) not null
)
PPFOR_GO;

--获取所有子类型ID，并以临时表格存储
if exists (select name from sysobjects where name='PPFOR_TZB_ProductType_GetChildIdsTable' and type='TF')
drop function PPFOR_TZB_ProductType_GetChildIdsTable
PPFOR_GO;
create function PPFOR_TZB_ProductType_GetChildIdsTable(@belongType int)   
	returns  @ChildIdsTable  table([ID] int,[show] bit)   
as   
  	begin 
		insert into @ChildIdsTable select [ID],[show] from [PPFOR_TZB_ProductType] where [ID]=@belongType   
  		insert into @ChildIdsTable select [ID],[show] from [PPFOR_TZB_ProductType] where [belongType]=@belongType   
		while @@rowcount>0   
		  	insert into @ChildIdsTable select a.[ID],a.[show] from [PPFOR_TZB_ProductType] a,@ChildIdsTable b
			   where a.[belongType]=b.[ID] and a.[ID] not in(select [ID] from @ChildIdsTable)   
		return
	end   
PPFOR_GO; 

--获取所有类型
if exists(select name from sysobjects where name='PPFOR_TZB_ProductType_GetTypeList' and type='P')
	drop procedure PPFOR_TZB_ProductType_GetTypeList
PPFOR_GO;
CREATE  PROCEDURE PPFOR_TZB_ProductType_GetTypeList(@belongMenu int)
 AS 
	select [ID],belongType,productTypeName,[htmlName] FROM [PPFOR_TZB_ProductType] 
		where belongMenu=@belongMenu
PPFOR_GO;

--添加类型
if exists(select name from sysobjects where name='PPFOR_TZB_ProductType_Add' and type='P')
	drop procedure PPFOR_TZB_ProductType_Add
PPFOR_GO;
CREATE PROCEDURE PPFOR_TZB_ProductType_Add(@ID int output,@belongType int,
	@productTypeName nvarchar(200),@productTemplate nvarchar(255),@listTemplate nvarchar(255),
	@updateDate datetime,@sort int,@show bit,@pageKeyWord nvarchar(255),
	@pageDescription nvarchar(255),@belongMenu int,@typeDescription text,@image nvarchar(255),
	@paramIDs text,@contentIDs text,@brandIDs text,@specIDs text,@htmlName nvarchar(255))
 AS 
	begin
		INSERT INTO [PPFOR_TZB_ProductType]([belongType],[productTypeName],[productTemplate],
			[listTemplate],[updateDate],[sort],[show],[pageKeyWord],[pageDescription],[belongMenu],
			[typeDescription],[image],[paramIDs],[contentIDs],[brandIDs],[specIDs],[htmlName])VALUES(@belongType,@productTypeName,
			@productTemplate,@listTemplate,@updateDate,@sort,@show,@pageKeyWord,@pageDescription,
			@belongMenu,@typeDescription,@image,@paramIDs,@contentIDs,@brandIDs,@specIDs,@htmlName)
		SET @ID = @@IDENTITY
	end
PPFOR_GO;

--删除类型
if exists(select name from sysobjects where name='PPFOR_TZB_ProductType_Delete' and type='P')
	drop procedure PPFOR_TZB_ProductType_Delete
PPFOR_GO;
CREATE PROCEDURE PPFOR_TZB_ProductType_Delete(@ID int)
 AS 
	DELETE [PPFOR_TZB_ProductType] WHERE [ID]=@ID 
PPFOR_GO;

--是否存在类型
if exists(select name from sysobjects where name='PPFOR_TZB_ProductType_IsExists' and type='P')
	drop procedure PPFOR_TZB_ProductType_IsExists
PPFOR_GO;
CREATE PROCEDURE PPFOR_TZB_ProductType_IsExists(@productTypeName nvarchar (200),
	@belongTypeID int,@belongMenuID int,@retValue bit out)
AS
	begin
		DECLARE @TempID int
		SELECT @TempID = count(1) FROM [PPFOR_TZB_ProductType] WHERE productTypeName =@productTypeName
			and belongType=@belongTypeID and belongmenu=@belongMenuID
		IF @TempID = 0
			set @retValue= 0
		ELSE
			set @retValue= 1
	end
PPFOR_GO;

--获取顶级父类型ID
if exists(select name from sysobjects where name='PPFOR_TZB_ProductType_GetRootID' and type='P')
	drop procedure PPFOR_TZB_ProductType_GetRootID
PPFOR_GO;
CREATE PROCEDURE PPFOR_TZB_ProductType_GetRootID(@ID int out,@belongMenuID int)
AS
	begin
		DECLARE @TempID int
		set @TempID=@ID
		while @TempID>0
		begin
			SELECT @TempID = belongType FROM PPFOR_TZB_ProductType where [ID]=@ID and belongMenu=@belongMenuID			
			if @ID<>@TempID and @TempID>0
				set @ID=@TempID
			else
				set @TempID=0
		end
	end
PPFOR_GO;

--获取所有类型名称
if exists(select name from sysobjects where name='PPFOR_TZB_ProductType_GetNameList' and type='P')
	drop procedure PPFOR_TZB_ProductType_GetNameList
PPFOR_GO;
CREATE PROCEDURE PPFOR_TZB_ProductType_GetNameList(@productParentID int, @belongMenu int)
 AS 
	SELECT [ID],[productTypeName],[show],[htmlName] FROM [PPFOR_TZB_ProductType] where 
		[belongType]=@productParentID and [belongMenu]=@belongMenu order by sort desc,[ID] asc
PPFOR_GO;

--获取所有子类型
if exists(select name from sysobjects where name='PPFOR_TZB_ProductType_GetListParent' and type='P')
	drop procedure PPFOR_TZB_ProductType_GetListParent
PPFOR_GO;
CREATE   PROCEDURE PPFOR_TZB_ProductType_GetListParent(@belongType int,@belongMenu int)
 AS 
	SELECT [ID],[belongType],[productTypeName],[htmlName] FROM [PPFOR_TZB_ProductType]
	 	WHERE [belongType]=@belongType and [belongMenu]=@belongMenu order by [sort] desc,[ID] asc
PPFOR_GO;

--获取所有类型
if exists(select name from sysobjects where name='PPFOR_TZB_ProductType_GetModelList' and type='P')
	drop procedure PPFOR_TZB_ProductType_GetModelList
PPFOR_GO;
create PROCEDURE PPFOR_TZB_ProductType_GetModelList(@productParentID int,@belongMenu int)
 AS 	
	SELECT [ID],belongType,productTypeName,productTemplate,listTemplate,updateDate,sort,show,
		pageKeyWord,pageDescription,belongMenu,typeDescription,[image],[htmlName] FROM [PPFOR_TZB_ProductType]
		where belongType=@productParentID and belongMenu=@belongMenu order by sort desc,[ID] asc
PPFOR_GO;

--获取所有子类型
if exists(select name from sysobjects where name='PPFOR_TZB_ProductType_GetListChild' and type='P')
	drop procedure PPFOR_TZB_ProductType_GetListChild
PPFOR_GO;
CREATE PROCEDURE PPFOR_TZB_ProductType_GetListChild(@belongType int)
 AS 
	SELECT [ID],[belongType],[htmlName] FROM [PPFOR_TZB_ProductType] where belongType=@belongType
PPFOR_GO;

--获取类型
if exists(select name from sysobjects where name='PPFOR_TZB_ProductType_GetModel' and type='P')
	drop procedure PPFOR_TZB_ProductType_GetModel
PPFOR_GO;
CREATE PROCEDURE PPFOR_TZB_ProductType_GetModel(@ID int)
 AS 
	SELECT [ID],belongType,productTypeName,productTemplate,listTemplate,[updateDate],sort,show,
		pageKeyWord,pageDescription,belongMenu,typeDescription,[image],paramIDs,contentIDs,
		brandIDs,specIDs,[htmlName] FROM [PPFOR_TZB_ProductType] WHERE [ID]=@ID 
PPFOR_GO;

--更新类型
if exists(select name from sysobjects where name='PPFOR_TZB_ProductType_Update' and type='P')
	drop procedure PPFOR_TZB_ProductType_Update
PPFOR_GO;
CREATE PROCEDURE PPFOR_TZB_ProductType_Update(@ID int,@belongType int,
	@productTypeName nvarchar(200),@productTemplate nvarchar(255),@listTemplate nvarchar(255),
	@updateDate datetime,@sort int,@show bit,@pageKeyWord nvarchar(255),
	@pageDescription nvarchar(255),@typeDescription text,@image nvarchar(255),
	@paramIDs text,@contentIDs text,@brandIDs text,@specIDs text,@htmlName nvarchar(255))
 AS 
	UPDATE [PPFOR_TZB_ProductType] SET [belongType] = @belongType,
		[productTypeName] = @productTypeName,[productTemplate] = @productTemplate,
		[listTemplate] = @listTemplate,[updateDate] = @updateDate,[sort] = @sort,
		[show] = @show,[pageKeyWord] = @pageKeyWord,[pageDescription] = @pageDescription,
		[typeDescription] = @typeDescription,[image] = @image, paramIDs=@paramIDs,
		contentIDs=@contentIDs,[brandIDs]=@brandIDs,[specIDs]=@specIDs,[htmlName]=@htmlName WHERE [ID]=@ID 
PPFOR_GO;

--更新类型列表信息
if exists(select name from sysobjects where name='PPFOR_TZB_ProductType_UpdateList' and type='P')
	drop procedure PPFOR_TZB_ProductType_UpdateList
PPFOR_GO;
CREATE PROCEDURE PPFOR_TZB_ProductType_UpdateList(@ID int,@updateDate datetime,@sort int,@show bit)
 AS 
	UPDATE [PPFOR_TZB_ProductType] SET [updateDate] = @updateDate,[sort] = @sort,[show] = @show
		WHERE [ID]=@ID 
PPFOR_GO;

--获取所有产品分类列表
if exists(select name from sysobjects where name='PPFOR_TZB_ProductType_GetModelListForLabel' and type='P')
	drop procedure PPFOR_TZB_ProductType_GetModelListForLabel
PPFOR_GO;
create procedure PPFOR_TZB_ProductType_GetModelListForLabel(@belongMenuId int,@belongType int)
as	
	if(@belongMenuId<=0)
		select * from [PPFOR_TZB_ProductType] where show=1 and belongType=@belongType 
			order by [sort] desc,[ID] asc
	else
		select * from [PPFOR_TZB_ProductType] where show=1 and belongType=@belongType 
			and belongMenu=@belongMenuId order by [sort] desc,[ID] asc
PPFOR_GO;

--获取产品栏目列表
if exists(select name from sysobjects where name='PPFOR_TZB_ProductType_GetShortListForLabel' and type='P')
	drop procedure PPFOR_TZB_ProductType_GetShortListForLabel
PPFOR_GO;
create procedure PPFOR_TZB_ProductType_GetShortListForLabel(@belongLanguage int,@belongType int)
as
	begin
	if(@belongLanguage=0)
		select [ID],productTypeName,belongMenu from PPFOR_TZB_ProductType 
			where [belongType]=@belongType 
	else if @belongLanguage>0
		select [ID],productTypeName,belongMenu from PPFOR_TZB_ProductType 
			where belongType=@belongType and belongMenu
		 		in (select [ID] from PPFOR_TZB_Menu where belongLanguage=@belongLanguage 
					and belongMenu=0)
	end
PPFOR_GO;

--获取路径产品类型
if exists(select name from sysobjects where name='PPFOR_TZB_ProductType_GetShortInfo' and type='P')
	drop procedure PPFOR_TZB_ProductType_GetShortInfo
PPFOR_GO;
create procedure PPFOR_TZB_ProductType_GetShortInfo(@TypeID int)
as
	select [ID],[belongType],[productTypeName],[belongMenu],[htmlName] from [PPFOR_TZB_ProductType]
	 	where [show]=1 and [ID]=@TypeID
PPFOR_GO;

--返回产品类型动态信息
if exists(select name from sysobjects where name='PPFOR_TZB_ProductType_GetVisitInfo' and type='P')
	drop procedure PPFOR_TZB_ProductType_GetVisitInfo
PPFOR_GO;
create procedure PPFOR_TZB_ProductType_GetVisitInfo(@ID int)
as
	select [ID],[belongType] from [PPFOR_TZB_ProductType] where [show]=1 and [ID]=@ID
PPFOR_GO;

--更新产品类型静态页文件名
if exists(select name from sysobjects where name='PPFOR_TZB_ProductType_UpdateHtmlName' and type='P')
	drop procedure PPFOR_TZB_ProductType_UpdateHtmlName
PPFOR_GO;

CREATE PROCEDURE PPFOR_TZB_ProductType_UpdateHtmlName(@ID int,@htmlName nvarchar(255))
 AS 
	UPDATE [PPFOR_TZB_ProductType] SET [htmlName] = @htmlName WHERE [ID]=@ID 
PPFOR_GO;

--获取产品类型静态文件名
if exists(select name from sysobjects where name='PPFOR_TZB_ProductType_GetHtmlName' and type='P')
	drop procedure PPFOR_TZB_ProductType_GetHtmlName
PPFOR_GO;
create procedure PPFOR_TZB_ProductType_GetHtmlName(@ID int)
as
	select [htmlName] from [PPFOR_TZB_ProductType] where [ID]=@ID 
PPFOR_GO;