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

--创建新闻类型表
if not exists(select name from sysobjects where name='PPFOR_TZB_NewsType' and type='U')
CREATE TABLE [PPFOR_TZB_NewsType] (
	[ID] int IDENTITY (1, 1) NOT NULL PRIMARY KEY,
	[newsParentID] int NOT NULL ,
	[typeName] nvarchar(200) COLLATE Chinese_PRC_CS_AS NOT NULL ,
	[newsTemplate] 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,
	[htmlName] nvarchar(255) not null
)
PPFOR_GO;

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

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

--是否存在类型
if exists(select name from sysobjects where name='PPFOR_TZB_NewsType_IsExists' and type='P')
	drop procedure PPFOR_TZB_NewsType_IsExists
PPFOR_GO;
CREATE PROCEDURE PPFOR_TZB_NewsType_IsExists(@typeName varchar (200),@newsParentID int,
	@belongMenuID int,@retValue bit out)
AS
	begin
		DECLARE @TempID int
		SELECT @TempID = count(1) FROM PPFOR_TZB_NewsType WHERE typeName =@typeName 
			and newsParentID=@newsParentID 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_NewsType_GetRootID' and type='P')
	drop procedure PPFOR_TZB_NewsType_GetRootID
PPFOR_GO;
CREATE PROCEDURE PPFOR_TZB_NewsType_GetRootID(@ID int out,@belongMenuID int)
AS
	begin
		DECLARE @TempID int
		set @TempID=@ID
		while @TempID>0
		begin
			SELECT @TempID = newsParentID FROM PPFOR_TZB_NewsType 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_NewsType_GetNameList' and type='P')
	drop procedure PPFOR_TZB_NewsType_GetNameList
PPFOR_GO;
CREATE PROCEDURE PPFOR_TZB_NewsType_GetNameList(@newsParentID int, @belongMenu int)
 AS 
	SELECT 	[ID],[typeName],[show],[htmlName] FROM [PPFOR_TZB_NewsType] where [newsParentID]=@newsParentID 
		and [belongMenu]=@belongMenu order by sort desc,[ID] asc
PPFOR_GO;

--获取所有类型
if exists(select name from sysobjects where name='PPFOR_TZB_NewsType_GetModelList' and type='P')
	drop procedure PPFOR_TZB_NewsType_GetModelList
PPFOR_GO;
CREATE PROCEDURE PPFOR_TZB_NewsType_GetModelList(@newsParentID int,@belongMenu int)
 AS 
	SELECT [ID],newsParentID,typeName,newsTemplate,listTemplate,updateDate,sort,show,pageKeyWord,
		pageDescription,belongMenu,typeDescription,[image],[htmlName] FROM [PPFOR_TZB_NewsType] 
		where newsParentID=@newsParentID and belongMenu=@belongMenu order by sort desc,[ID] asc
PPFOR_GO;

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

--获取所有类型名和父类型ID
if exists(select name from sysobjects where name='PPFOR_TZB_NewsType_GetTypeList' and type='P')
	drop procedure PPFOR_TZB_NewsType_GetTypeList
PPFOR_GO;
CREATE  PROCEDURE PPFOR_TZB_NewsType_GetTypeList(@belongMenu int)
 AS 
	SELECT [ID],newsParentID,typeName,htmlName FROM [PPFOR_TZB_NewsType] where belongMenu=@belongMenu
PPFOR_GO;

--获取类型
if exists(select name from sysobjects where name='PPFOR_TZB_NewsType_GetModel' and type='P')
	drop procedure PPFOR_TZB_NewsType_GetModel
PPFOR_GO;
CREATE PROCEDURE PPFOR_TZB_NewsType_GetModel(@ID int)
 AS 
	SELECT [ID],newsParentID,typeName,newsTemplate,listTemplate,updateDate,sort,show,pageKeyWord,
		pageDescription,belongMenu,typeDescription,[image],htmlName FROM [PPFOR_TZB_NewsType]
	 	WHERE [ID]=@ID
PPFOR_GO;

--更新类型
if exists(select name from sysobjects where name='PPFOR_TZB_NewsType_Update' and type='P')
	drop procedure PPFOR_TZB_NewsType_Update
PPFOR_GO;
CREATE  PROCEDURE PPFOR_TZB_NewsType_Update(@ID int,@newsParentID int,@typeName nvarchar(200),
	@newsTemplate nvarchar(255),@listTemplate nvarchar(255),@updateDate datetime,@sort int,
	@show bit,@pageKeyWord nvarchar(255),@pageDescription nvarchar(255),@typeDescription text,
	@image nvarchar(255),@htmlName nvarchar(255))
 AS 
	UPDATE [PPFOR_TZB_NewsType] SET	[newsParentID] = @newsParentID,[typeName] = @typeName,
		[newsTemplate] = @newsTemplate,[listTemplate] = @listTemplate,updateDate = @updateDate,
		[sort] = @sort,[show] = @show,[pageKeyWord] = @pageKeyWord,	[pageDescription] = @pageDescription,
		[typeDescription] = @typeDescription,[image] = @image,[htmlName]=@htmlName
		WHERE [ID]=@ID 
PPFOR_GO;

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

--获取子类型列表
if exists(select name from sysobjects where name='PPFOR_TZB_NewsType_GetListParent' and type='P')
	drop procedure PPFOR_TZB_NewsType_GetListParent
PPFOR_GO;
CREATE PROCEDURE PPFOR_TZB_NewsType_GetListParent(@newsParentID int,@belongMenu int)
 AS 
	SELECT [ID],newsParentID,typeName,htmlName FROM [PPFOR_TZB_NewsType]
	 	WHERE newsParentID=@newsParentID and belongMenu=@belongMenu order by [sort] desc,[ID] asc
PPFOR_GO;

--获取所有新闻栏目列表
if exists(select name from sysobjects where name='PPFOR_TZB_NewsType_GetModelListForLabel' and type='P')
	drop procedure PPFOR_TZB_NewsType_GetModelListForLabel
PPFOR_GO;
create procedure PPFOR_TZB_NewsType_GetModelListForLabel(@belongMenuId int,@newsParentID int)
as
	if(@belongMenuId<=0)
		select * from [PPFOR_TZB_NewsType] where show=1 and newsParentID=@newsParentID 
			order by [sort] desc,[ID] asc
	else
		select * from [PPFOR_TZB_NewsType] where show=1 and newsParentID=@newsParentID 
			and belongMenu=@belongMenuId order by [sort] desc,[ID] asc
PPFOR_GO;

--获取新闻栏目列表
if exists(select name from sysobjects where name='PPFOR_TZB_NewsType_GetShortListForLabel' and type='P')
	drop procedure PPFOR_TZB_NewsType_GetShortListForLabel
PPFOR_GO;
create procedure PPFOR_TZB_NewsType_GetShortListForLabel(@belongLanguage int,@newsTypeParentID int)
as
	begin
	if(@belongLanguage=0)
		select [ID],typeName,belongMenu from [PPFOR_TZB_NewsType] 
			where newsParentID=@newsTypeParentID
	else if @belongLanguage>0
		select [ID],typeName,belongMenu from [PPFOR_TZB_NewsType] 
			where newsParentID=@newsTypeParentID 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_NewsType_GetShortInfo' and type='P')
	drop procedure PPFOR_TZB_NewsType_GetShortInfo
PPFOR_GO;
create procedure PPFOR_TZB_NewsType_GetShortInfo(@TypeID int)
as
	select [ID],[newsParentID],[typeName],[belongMenu],[htmlName] from [PPFOR_TZB_NewsType] 
		where [show]=1 and [ID]=@TypeID
PPFOR_GO;

--返回新闻类型动态信息
if exists(select name from sysobjects where name='PPFOR_TZB_NewsType_GetVisitInfo' and type='P')
	drop procedure PPFOR_TZB_NewsType_GetVisitInfo
PPFOR_GO;
create procedure PPFOR_TZB_NewsType_GetVisitInfo(@ID int)
as
	select [ID],[newsParentID] from [PPFOR_TZB_NewsType] where [show]=1 and [ID]=@ID
PPFOR_GO;

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

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

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