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

--创建图片类型表
if not exists(select name from sysobjects where name='PPFOR_TZB_PictureType' and type='U')
CREATE TABLE [PPFOR_TZB_PictureType] (
	[ID] int IDENTITY (1, 1) NOT NULL PRIMARY KEY,
	[belongType] int NOT NULL ,
	[picTypeName] nvarchar(200) COLLATE Chinese_PRC_CS_AS NOT NULL ,
	[picTemplate] nvarchar(255) NULL ,
	[listTemplate] nvarchar(255) NOT NULL ,
	[picDescription] text NULL ,
	[image] nvarchar(255) 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,
	[htmlName] nvarchar(255) not null
)
PPFOR_GO;

--获取所有子类型ID，并以临时表格存储
if exists (select name from sysobjects where name='PPFOR_TZB_PictureType_GetChildIdsTable' and type='TF')
drop function PPFOR_TZB_PictureType_GetChildIdsTable
PPFOR_GO;
create function PPFOR_TZB_PictureType_GetChildIdsTable(@belongType int)   
	returns  @ChildIdsTable  table([ID] int,[show] bit)   
as   
  	begin 
		insert into @ChildIdsTable select [ID],[show] from [PPFOR_TZB_PictureType] where [ID]=@belongType   
  		insert into @ChildIdsTable select [ID],[show] from [PPFOR_TZB_PictureType] where [belongType]=@belongType   
		while @@rowcount>0   
		  	insert into @ChildIdsTable select a.[ID],a.[show] from [PPFOR_TZB_PictureType] 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_PictureType_Add' and type='P')
	drop procedure PPFOR_TZB_PictureType_Add
PPFOR_GO;
CREATE PROCEDURE PPFOR_TZB_PictureType_Add(@ID int output,@belongType int,@picTypeName nvarchar(200),
	@picTemplate nvarchar(255),@listTemplate nvarchar(255),@picDescription text,
	@image nvarchar(255),@updateDate datetime,@sort int,@show bit,@pageKeyWord nvarchar(255),
	@pageDescription nvarchar(255),@belongMenu int,@htmlName nvarchar(255))
 AS 
	begin
		INSERT INTO [PPFOR_TZB_PictureType]([belongType],[picTypeName],[picTemplate],[listTemplate],
			[picDescription],[image],[updateDate],[sort],[show],[pageKeyWord],[pageDescription],
			[belongMenu],[htmlName])VALUES(@belongType,@picTypeName,@picTemplate,@listTemplate,@picDescription,
			@image,@updateDate,@sort,@show,@pageKeyWord,@pageDescription,@belongMenu,@htmlName)
		SET @ID = @@IDENTITY
	end
PPFOR_GO;

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

--是否存在类型
if exists(select name from sysobjects where name='PPFOR_TZB_PictureType_Exists' and type='P')
	drop procedure PPFOR_TZB_PictureType_Exists
PPFOR_GO;
CREATE PROCEDURE PPFOR_TZB_PictureType_Exists(@picTypeName nvarchar(200),
	@belongTypeID int,@belongMenuID int,@retValue bit out)
AS
	begin
		DECLARE @TempID int
		SELECT @TempID = count(1) FROM [PPFOR_TZB_PictureType] WHERE picTypeName =@picTypeName 
			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_PictureType_GetRootID' and type='P')
	drop procedure PPFOR_TZB_PictureType_GetRootID
PPFOR_GO;
CREATE PROCEDURE PPFOR_TZB_PictureType_GetRootID(@ID int out,@belongMenuID int)
AS
	begin
		DECLARE @TempID int
		set @TempID=@ID
		while @TempID>0
		begin
			SELECT @TempID = belongType FROM PPFOR_TZB_PictureType 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_PictureType_GetNameList' and type='P')
	drop procedure PPFOR_TZB_PictureType_GetNameList
PPFOR_GO;
CREATE PROCEDURE PPFOR_TZB_PictureType_GetNameList(@picParentID int, @belongMenu int)
 AS 
	SELECT 	[ID],[picTypeName],[show],[htmlName] FROM [PPFOR_TZB_PictureType] where [belongType]=@picParentID 
		and [belongMenu]=@belongMenu order by sort desc,[ID] asc
PPFOR_GO;

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

--获取所有类型
if exists(select name from sysobjects where name='PPFOR_TZB_PictureType_GetModelList' and type='P')
	drop procedure PPFOR_TZB_PictureType_GetModelList
PPFOR_GO;
CREATE PROCEDURE PPFOR_TZB_PictureType_GetModelList(@picParentID int,@belongMenu int)
 AS 
	SELECT [ID],belongType,picTypeName,picTemplate,listTemplate,picDescription,[image],updateDate,
		sort,show,pageKeyWord,pageDescription,belongMenu,htmlName FROM [PPFOR_TZB_PictureType]
		where belongType=@picParentID and belongMenu=@belongMenu order by sort desc,[ID] asc
PPFOR_GO;

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

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

--获取类型
if exists(select name from sysobjects where name='PPFOR_TZB_PictureType_GetModel' and type='P')
	drop procedure PPFOR_TZB_PictureType_GetModel
PPFOR_GO;
CREATE   PROCEDURE PPFOR_TZB_PictureType_GetModel(@ID int)
 AS 
	SELECT [ID],belongType,picTypeName,picTemplate,listTemplate,picDescription,[image],updateDate,
		sort,show,pageKeyWord,pageDescription,belongMenu,htmlName FROM [PPFOR_TZB_PictureType]
	 	WHERE [ID]=@ID
PPFOR_GO;

--更新类型
if exists(select name from sysobjects where name='PPFOR_TZB_PictureType_Update' and type='P')
	drop procedure PPFOR_TZB_PictureType_Update
PPFOR_GO;
CREATE   PROCEDURE PPFOR_TZB_PictureType_Update(@ID int,@belongType int,@picTypeName nvarchar(100),
	@picTemplate nvarchar(255),@listTemplate nvarchar(255),@picDescription text,@image nvarchar(255),
	@updateDate datetime,@sort int,@show bit,@pageKeyWord nvarchar(255),@pageDescription nvarchar(255),
	@htmlName nvarchar(255))
 AS 
	UPDATE [PPFOR_TZB_PictureType] SET [belongType] = @belongType,[picTypeName] = @picTypeName,
		[picTemplate] = @picTemplate,[listTemplate] = @listTemplate,
		[picDescription] = @picDescription,[image] = @image,[updateDate] = @updateDate,[sort] =@sort,
		[show] = @show,[pageKeyWord] = @pageKeyWord,[pageDescription] = @pageDescription,[htmlName]=@htmlName
		WHERE [ID]=@ID
PPFOR_GO;

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

--获取所有图片分类列表
if exists(select name from sysobjects where name='PPFOR_TZB_PictureType_GetModelListForLabel' and type='P')
	drop procedure PPFOR_TZB_PictureType_GetModelListForLabel
PPFOR_GO;
create procedure PPFOR_TZB_PictureType_GetModelListForLabel(@belongMenuId int,@belongType int)
as
	if(@belongMenuId<=0)
		select * from [PPFOR_TZB_PictureType] where show=1 and belongType=@belongType 
			order by [sort] desc,[ID] asc
	else
		select * from [PPFOR_TZB_PictureType] 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_PictureType_GetShortListForLabel' and type='P')
	drop procedure PPFOR_TZB_PictureType_GetShortListForLabel
PPFOR_GO;
create procedure PPFOR_TZB_PictureType_GetShortListForLabel(@belongLanguage int,@belongType int)
as
	begin
	if(@belongLanguage=0)
		select [ID],picTypeName,belongMenu from PPFOR_TZB_PictureType where [belongType]=@belongType 
	else if @belongLanguage>0
		select [ID],picTypeName,belongMenu from PPFOR_TZB_PictureType 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_PictureType_GetShortInfo' and type='P')
	drop procedure PPFOR_TZB_PictureType_GetShortInfo
PPFOR_GO;
create procedure PPFOR_TZB_PictureType_GetShortInfo(@TypeID int)
as
	select [ID],[belongType],[picTypeName],[belongMenu],[htmlName] from [PPFOR_TZB_PictureType] 
		where [show]=1 and [ID]=@TypeID
PPFOR_GO;

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

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

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

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

