-- ==============================
-- 长登科技 www.ppfor.com
-- 唐镇波   2010-04-21
-- ------------------------------
-- 单页面表
-- ==============================

--创建单页面表
if not exists(select name from sysobjects where name='PPFOR_TZB_Page' and type='U')
CREATE TABLE [PPFOR_TZB_Page] (
	[ID] int IDENTITY (1, 1) NOT NULL PRIMARY KEY,
	[belongPage] int NOT NULL ,					--所属页面
	[pageName] nvarchar(200) COLLATE Chinese_PRC_CS_AS NOT NULL ,	--页面标题
	[isLink] bit NOT NULL ,						--是否外部链接
	[URLLink] nvarchar(255) NULL ,				--外部链接地址
	[pageContent] text NULL ,					--页面内容
	[image] nvarchar (255) NULL ,				--图片
	[sort] int NOT NULL DEFAULT (0),			--排序
	[show] bit NOT NULL DEFAULT (1),			--是否显示
	[updateDate] datetime NOT NULL ,			--更新时间
	[pageKeyWord] nvarchar (255) COLLATE Chinese_PRC_CI_AS NULL ,		--META关键字
	[pageDescription] nvarchar (255) COLLATE Chinese_PRC_CI_AS NULL ,	--META描述
	[pageTemplate] nvarchar (255) NULL ,		--页面模板
	[click] int NOT NULL DEFAULT (0),			--点击数
	[belongMenu] int NOT NULL,					--所属栏目
	[isPublic] bit not null,					--是否允许所有人浏览
	[allowDegreeIds] text null,					--限制允许浏览的会员等级
	[htmlName] nvarchar(255) not null			--静态页文件名
)
PPFOR_GO;

--添加单页面
if exists(select name from sysobjects where name='PPFOR_TZB_Page_Add' and type='P')
	drop procedure PPFOR_TZB_Page_Add
PPFOR_GO;
CREATE  PROCEDURE PPFOR_TZB_Page_Add(@ID int output,@belongPage int,@pageName nvarchar(200),
	@isLink bit,@URLLink nvarchar(255),@pageContent text,@image nvarchar(255),@sort int,
	@show bit,@updateDate datetime,@pageKeyWord nvarchar(255),@pageDescription nvarchar(255),
	@pageTemplate nvarchar(255),@click int,@belongMenu int,@isPublic bit,@allowDegreeIds text,@htmlName nvarchar(255))
 AS 
	begin
		INSERT INTO [PPFOR_TZB_Page]([belongPage],[pageName],[isLink],[URLLink],[pageContent],
			[image],[sort],[show],[updateDate],[pageKeyWord],[pageDescription],[pageTemplate],
			[click],[belongMenu],[isPublic],[allowDegreeIds],[htmlName])VALUES(@belongPage,@pageName,
			@isLink,@URLLink,@pageContent,@image,@sort,@show,@updateDate,@pageKeyWord,
			@pageDescription,@pageTemplate,@click,@belongMenu,@isPublic,@allowDegreeIds,@htmlName)
		SET @ID = @@IDENTITY
	end
PPFOR_GO;

--删除单页面
if exists(select name from sysobjects where name='PPFOR_TZB_Page_Delete' and type='P')
	drop procedure PPFOR_TZB_Page_Delete
PPFOR_GO;
CREATE PROCEDURE PPFOR_TZB_Page_Delete(@ID int)
 AS 
	DELETE [PPFOR_TZB_Page] WHERE [ID]=@ID 
PPFOR_GO;

--是否存在页面
if exists(select name from sysobjects where name='PPFOR_TZB_Page_IsExists' and type='P')
	drop procedure PPFOR_TZB_Page_IsExists
PPFOR_GO;
CREATE PROCEDURE PPFOR_TZB_Page_IsExists(@pageName nvarchar (100),@belongPageID int,
	@belongMenuID int,@retValue bit out)
AS
	begin
		DECLARE @TempID int
		SELECT @TempID = count(1) FROM [PPFOR_TZB_Page] WHERE pageName = @pageName 
			and belongPage=@belongPageID 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_Page_GetRootID' and type='P')
	drop procedure PPFOR_TZB_Page_GetRootID
PPFOR_GO;
CREATE PROCEDURE PPFOR_TZB_Page_GetRootID(@ID int out,@belongMenuID int)
AS
	begin
		DECLARE @TempID int		
		set @TempID=@ID
		while @TempID>0
		begin
			SELECT @TempID = [belongPage] FROM [PPFOR_TZB_Page] 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_Page_GetNameList' and type='P')
	drop procedure PPFOR_TZB_Page_GetNameList
PPFOR_GO;
CREATE PROCEDURE PPFOR_TZB_Page_GetNameList(@belongPage int, @belongMenu int)
 AS 
	SELECT 	[ID],[pageName],[show],[isLink],[URLLink],[htmlName] FROM [PPFOR_TZB_Page] where [belongPage]=@belongPage
 		and [belongMenu]=@belongMenu order by sort desc,[ID] asc
PPFOR_GO;

--获取所有子页面
if exists(select name from sysobjects where name='PPFOR_TZB_Page_GetListParent' and type='P')
	drop procedure PPFOR_TZB_Page_GetListParent
PPFOR_GO;
CREATE   PROCEDURE PPFOR_TZB_Page_GetListParent(@belongPage int,@belongMenu int)
 AS 
	SELECT [ID],belongPage,pageName,isLink,htmlName FROM [PPFOR_TZB_Page]
	 	WHERE belongPage=@belongPage and belongMenu=@belongMenu order by sort desc,[ID] asc
PPFOR_GO;

--获取所有页面
if exists(select name from sysobjects where name='PPFOR_TZB_Page_GetModelList' and type='P')
	drop procedure PPFOR_TZB_Page_GetModelList
PPFOR_GO;
CREATE PROCEDURE PPFOR_TZB_Page_GetModelList(@belongPage int,@belongMenu int)
 AS 
	SELECT [ID],belongPage,pageName,isLink,URLLink,pageContent,[image],sort,show,updateDate,
		pageKeyWord,pageDescription,pageTemplate,click,belongMenu,htmlName FROM [PPFOR_TZB_Page]
		where belongPage=@belongPage and belongMenu=@belongMenu order by sort desc,[ID] asc
PPFOR_GO;

--获取单页面
if exists(select name from sysobjects where name='PPFOR_TZB_Page_GetModel' and type='P')
	drop procedure PPFOR_TZB_Page_GetModel
PPFOR_GO;
CREATE  PROCEDURE PPFOR_TZB_Page_GetModel(@ID int)
 AS 
	SELECT * FROM [PPFOR_TZB_Page] WHERE [ID]=@ID 
PPFOR_GO;

--更新单页面
if exists(select name from sysobjects where name='PPFOR_TZB_Page_Update' and type='P')
	drop procedure PPFOR_TZB_Page_Update
PPFOR_GO;
CREATE   PROCEDURE PPFOR_TZB_Page_Update(@ID int,@belongPage int,@pageName nvarchar(200),
	@isLink bit,@URLLink nvarchar(255),@pageContent text,@image nvarchar(255),@sort int,
	@show bit,@updateDate datetime,@pageKeyWord nvarchar(255),@pageDescription nvarchar(255),
	@pageTemplate nvarchar(255),@click int,@isPublic bit,@allowDegreeIds text,@htmlName nvarchar(255))
 AS 
	UPDATE [PPFOR_TZB_Page] SET [belongPage] = @belongPage,[pageName] = @pageName, [isLink]=@isLink, 
		[URLLink] = @URLLink,[pageContent] = @pageContent,[image] = @image,[sort] = @sort,
		[show] = @show,[updateDate] = @updateDate,[pageKeyWord] = @pageKeyWord,
		[pageDescription] = @pageDescription,[pageTemplate] = @pageTemplate,[click] = @click,
		[isPublic]=@isPublic, [allowDegreeIds]=@allowDegreeIds,[htmlName]=@htmlName WHERE [ID]=@ID
PPFOR_GO;

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

--获取所有子页面
if exists(select name from sysobjects where name='PPFOR_TZB_Page_GetListChild' and type='P')
	drop procedure PPFOR_TZB_Page_GetListChild
PPFOR_GO;
CREATE PROCEDURE PPFOR_TZB_Page_GetListChild(@belongPage int)
 AS 
	SELECT [ID],[belongPage],[htmlName] FROM [PPFOR_TZB_Page] where belongPage=@belongPage
PPFOR_GO;

--获取所有页面ID
if exists(select name from sysobjects where name='PPFOR_TZB_Page_GetModelIDListByMenu' and type='P')
	drop procedure PPFOR_TZB_Page_GetModelIDListByMenu
PPFOR_GO;
CREATE PROCEDURE PPFOR_TZB_Page_GetModelIDListByMenu(@belongMenu int)
 AS 
	SELECT [ID] FROM [PPFOR_TZB_Page] where belongMenu=@belongMenu and [show]=1
PPFOR_GO;

--获取所有页面ID，父页面ID
if exists(select name from sysobjects where name='PPFOR_TZB_Page_GetTypeList' and type='P')
	drop procedure PPFOR_TZB_Page_GetTypeList
PPFOR_GO;
create PROCEDURE PPFOR_TZB_Page_GetTypeList(@belongMenu int)
 AS 
	SELECT [ID],[belongPage],[htmlName] FROM [PPFOR_TZB_Page]	where belongMenu=@belongMenu
PPFOR_GO;

--获取相关单页面列表
if exists(select name from sysobjects where name='PPFOR_TZB_Page_GetRelated' and type='P')
	drop procedure PPFOR_TZB_Page_GetRelated
PPFOR_GO;
create procedure PPFOR_TZB_Page_GetRelated(@id int,@belongMenu int,@keyword nvarchar(255),@number int)
as
	begin
		if @number>0
			set ROWCOUNT @number
	 	if @keyword is not null and len(@keyword)>0
			select [ID],[pageName],[htmlName] from [PPFOR_TZB_Page] where [ID]<>@id and belongMenu=@belongMenu 
				and pageKeyWord like @keyword order by [sort] desc, [ID] desc
		else 
			select [ID],[pageName],[htmlName] from [PPFOR_TZB_Page] where [ID]<>@id and belongMenu=@belongMenu 
				order by [sort] desc, [ID] desc
	end
PPFOR_GO;

--获取单页面点击数
if exists(select name from sysobjects where name='PPFOR_TZB_Page_GetClickCount' and type='P')
	drop procedure PPFOR_TZB_Page_GetClickCount
PPFOR_GO;
create procedure PPFOR_TZB_Page_GetClickCount(@ID int)
as
	select [click] from [PPFOR_TZB_Page] where [ID]=@ID
PPFOR_GO;
--获取单页面名称
if exists(select name from sysobjects where name='PPFOR_TZB_Page_GetModelName' and type='P')
	drop procedure PPFOR_TZB_Page_GetModelName
PPFOR_GO;
create procedure PPFOR_TZB_Page_GetModelName(@ID int)
as
	select [pageName] from [PPFOR_TZB_Page] where [ID]=@ID
PPFOR_GO;
--点击+1，并返回单页面动态信息
if exists(select name from sysobjects where name='PPFOR_TZB_Page_GetVisitInfo' and type='P')
	drop procedure PPFOR_TZB_Page_GetVisitInfo
PPFOR_GO;
create procedure PPFOR_TZB_Page_GetVisitInfo(@ID int)
as
	update [PPFOR_TZB_Page] set [click]=[click]+1 where [show]=1 and [ID]=@ID and isPublic=1
	select [ID],[belongPage],[click],[belongMenu] from [PPFOR_TZB_Page] where [show]=1 
	 	and isPublic=1 and [ID]=@ID
PPFOR_GO;

--点击+1，并返回单页面动态信息，权限
if exists(select name from sysobjects where name='PPFOR_TZB_Page_GetVisitContent' and type='P')
	drop procedure PPFOR_TZB_Page_GetVisitContent
PPFOR_GO;
create procedure PPFOR_TZB_Page_GetVisitContent(@ID int)
as
	update [PPFOR_TZB_Page] set [click]=[click]+1 where [show]=1 and [ID]=@ID
	select [ID],[belongPage],[click],[belongMenu],[pageContent],[isPublic],[allowDegreeIds]
		 from [PPFOR_TZB_Page] where [show]=1 and [ID]=@ID
PPFOR_GO;

--返回上一个单页面
if exists(select name from sysobjects where name='PPFOR_TZB_Page_GetPrevPage' and type='P')
	drop procedure PPFOR_TZB_Page_GetPrevPage
PPFOR_GO;
create procedure PPFOR_TZB_Page_GetPrevPage(@ID int) 
as
	declare @belongPage int
	declare @belongMenu int
	select @belongPage=[belongPage],@belongMenu=[belongMenu] from [PPFOR_TZB_Page] 
		where [show]=1 and [ID]=@ID 
	if @belongMenu>0
		select top 1 [ID],[pageName],[htmlName] from [PPFOR_TZB_Page] where [show]=1 and [ID]>@ID 
			and [belongPage]=@belongPage and [belongMenu]=@belongMenu order by [ID] asc
PPFOR_GO;

--返回下一个单页面
if exists(select name from sysobjects where name='PPFOR_TZB_Page_GetNextPage' and type='P')
	drop procedure PPFOR_TZB_Page_GetNextPage
PPFOR_GO;
create procedure PPFOR_TZB_Page_GetNextPage(@ID int) 
as
	declare @belongPage int
	declare @belongMenu int
	select @belongPage=[belongPage],@belongMenu=[belongMenu] from [PPFOR_TZB_Page] 
		where [show]=1 and [ID]=@ID 
	if @belongMenu>0
		select top 1 [ID],[pageName],[htmlName] from [PPFOR_TZB_Page] where [show]=1 and [ID]<@ID 
			and [belongPage]=@belongPage and [belongMenu]=@belongMenu order by [ID] desc
PPFOR_GO;	

--获取相关单页面列表
if exists(select name from sysobjects where name='PPFOR_TZB_Page_GetRelatedList' and type='P')
	drop procedure PPFOR_TZB_Page_GetRelatedList
PPFOR_GO;
create procedure PPFOR_TZB_Page_GetRelatedList(@ID int,@number int)
as
	begin
		declare @keyword nvarchar (255)
		declare @belongMenu int
		select @keyword=pageKeyWord, @belongMenu=belongMenu from [PPFOR_TZB_Page] where [show]=1 
			and [ID]=@ID 
		if @number>0
			set ROWCOUNT @number
	 	if @keyword is not null and len(@keyword)>0
			begin
				exec PPFOR_TZB_Meta_FormatKeyWord @keyword,@keyword out
				select [ID],[pageName],[htmlName] from [PPFOR_TZB_Page] where [ID]<>@ID and [show]=1 
					and belongMenu=@belongMenu and pageKeyWord like @keyword 
					order by [sort] desc, [ID] desc
			end
		else if @belongMenu>0
			select [ID],[pageName],[htmlName] from [PPFOR_TZB_Page] where [ID]<>@ID and [show]=1 
				and belongMenu=@belongMenu 	order by [sort] desc, [ID] desc
	end
PPFOR_GO;

--获取路径单页面
if exists(select name from sysobjects where name='PPFOR_TZB_Page_GetShortInfo' and type='P')
	drop procedure PPFOR_TZB_Page_GetShortInfo
PPFOR_GO;
create procedure PPFOR_TZB_Page_GetShortInfo(@PageID int)
as
	select [ID],[belongPage],[pageName],[belongMenu],[htmlName] from [PPFOR_TZB_Page] 
		where [show]=1 and [ID]=@PageID
PPFOR_GO;
--更新单页面静态页文件名
if exists(select name from sysobjects where name='PPFOR_TZB_Page_UpdateHtmlName' and type='P')
	drop procedure PPFOR_TZB_Page_UpdateHtmlName
PPFOR_GO;

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

--获取单页面静态文件名
if exists(select name from sysobjects where name='PPFOR_TZB_Page_GetHtmlName' and type='P')
	drop procedure PPFOR_TZB_Page_GetHtmlName
PPFOR_GO;
create procedure PPFOR_TZB_Page_GetHtmlName(@ID int)
as
	select [htmlName] from [PPFOR_TZB_Page] where [ID]=@ID 
PPFOR_GO;