-- ==============================
-- 长登科技 www.ppfor.com
-- 唐镇波   2010-04-20
-- ------------------------------
-- 栏目表
-- ==============================

--创建栏目表
if not exists (select * from sysobjects where name='PPFOR_TZB_Menu')
CREATE TABLE [PPFOR_TZB_Menu] (
	[ID] int IDENTITY (1, 1) NOT NULL PRIMARY KEY,
	[menuName] nvarchar (30) COLLATE Chinese_PRC_CS_AS NOT NULL ,		--栏目名称
	[belongMenu] int NOT NULL ,											--所属父栏目
	[belongLanguage] int REFERENCES [PPFOR_TZB_Language]([ID]) ON DELETE CASCADE NOT NULL , --所属语言
	[belongModel] int REFERENCES [PPFOR_TZB_Model]([ID]) ON DELETE CASCADE NOT NULL ,       --所属模块
	[menuTemplate] nvarchar (255) NOT NULL ,							--栏目模板
	[subListTemplate] nvarchar (255) NULL ,								--子类型列表页模板
	[subDetailTemplate] nvarchar (255) NULL ,							--子类型详细页模板
	[menuIntroduction] text NULL ,										--栏目介绍
	[openNew] bit NOT NULL ,											--是否打开新窗口
	[sort] int NOT NULL ,												--排序
	[show] bit NOT NULL ,												--是否显示
	[pageKeyWord] nvarchar (255) COLLATE Chinese_PRC_CI_AS NULL ,		--Meta关键字
	[pageDescription] nvarchar (255) COLLATE Chinese_PRC_CI_AS NULL,	--Meta描述	
	[htmlName] nvarchar(255) NOT NULL				--HTML文件名
)
PPFOR_GO;

----添加栏目
if exists(select name from sysobjects where name='PPFOR_TZB_Menu_Add' and type='P')
	drop procedure PPFOR_TZB_Menu_Add
PPFOR_GO;
CREATE PROCEDURE PPFOR_TZB_Menu_Add(@ID int output,@menuName nvarchar(30),@belongMenu int,
	@belongLanguage int,@belongModel int,@menuTemplate nvarchar(255),@subListTemplate nvarchar(255),
	@subDetailTemplate nvarchar(255),@menuIntroduction text,@openNew bit,@sort int,@show bit,
	@pageKeyWord nvarchar (255),@pageDescription nvarchar (255),@htmlName nvarchar (255))
 AS
	begin 
		INSERT INTO [PPFOR_TZB_Menu]([menuName],[belongMenu],[belongLanguage],[belongModel],
		[menuTemplate],[subListTemplate],[subDetailTemplate],[menuIntroduction],[openNew],[sort],
		[show],[pageKeyWord],[pageDescription],[htmlName])VALUES(@menuName,@belongMenu,@belongLanguage,
		@belongModel,@menuTemplate,@subListTemplate,@subDetailTemplate,@menuIntroduction,@openNew,
		@sort,@show,@pageKeyWord,@pageDescription,@htmlName)
		
		SET @ID = @@IDENTITY
	end
PPFOR_GO;

---删除栏目
if exists(select name from sysobjects where name='PPFOR_TZB_Menu_Delete' and type='P')
	drop procedure PPFOR_TZB_Menu_Delete
PPFOR_GO;

CREATE PROCEDURE PPFOR_TZB_Menu_Delete(@ID int)
 AS 
	DELETE [PPFOR_TZB_Menu] WHERE [ID]=@ID 
PPFOR_GO;

---是否已经存在栏目
if exists(select name from sysobjects where name='PPFOR_TZB_Menu_IsExists' and type='P')
	drop procedure PPFOR_TZB_Menu_IsExists
PPFOR_GO;

CREATE  PROCEDURE PPFOR_TZB_Menu_IsExists(@belongLanguage int,@belongMenu int,
	@menuName nvarchar(30),@retValue bit output)
AS
	begin
		DECLARE @TempID int
		SELECT @TempID = count(1) FROM [PPFOR_TZB_Menu] WHERE menuName=@menuName 
			and belongLanguage=@belongLanguage and belongMenu=@belongMenu
		IF @TempID = 0
			set @retValue=0
		ELSE
			set @retValue=1
	end
PPFOR_GO;

--查询栏目保存位置
if exists(select name from sysobjects where name='PPFOR_TZB_Menu_GetLanguagePath' and type='P')
	drop procedure PPFOR_TZB_Menu_GetLanguagePath
PPFOR_GO;

create procedure PPFOR_TZB_Menu_GetLanguagePath(@belongMenu int)
as
	begin
		select [fileName] from [PPFOR_TZB_Language] where [ID]=
			(select belongLanguage from PPFOR_TZB_Menu where [ID]=@belongMenu)
	end
PPFOR_GO;

--查询所有栏目
if exists(select name from sysobjects where name='PPFOR_TZB_Menu_GetModelList' and type='P')
	drop procedure PPFOR_TZB_Menu_GetModelList
PPFOR_GO;

CREATE   PROCEDURE PPFOR_TZB_Menu_GetModelList
 AS 
	SELECT * FROM [PPFOR_TZB_Menu] order by sort desc,[ID] asc 
PPFOR_GO;

--按语言查询所有栏目部分信息
if exists(select name from sysobjects where name='PPFOR_TZB_Menu_GetShortListByLanguage' and type='P')
	drop procedure PPFOR_TZB_Menu_GetShortListByLanguage
PPFOR_GO;

CREATE PROCEDURE PPFOR_TZB_Menu_GetShortListByLanguage(@belongLanguage int)
 AS 
	select b.[ID],b.menuName,a.modelUrl,b.sort from PPFOR_TZB_Model as a
	  left join PPFOR_TZB_Menu as b on a.id=b.belongModel
		where b.belongLanguage=@belongLanguage and b.belongMenu=0 
			order by b.sort desc,b.[ID] asc
PPFOR_GO;

--查询所有子栏目
if exists(select name from sysobjects where name='PPFOR_TZB_Menu_GetChildList' and type='P')
	drop procedure PPFOR_TZB_Menu_GetChildList
PPFOR_GO;

CREATE  PROCEDURE PPFOR_TZB_Menu_GetChildList(@ID int)
 AS
	SELECT * FROM [PPFOR_TZB_Menu] WHERE belongMenu=@ID 
PPFOR_GO;


--按语言查询所有栏目信息
if exists(select name from sysobjects where name='PPFOR_TZB_Menu_GetModelListByLanguage' and type='P')
	drop procedure PPFOR_TZB_Menu_GetModelListByLanguage
PPFOR_GO;

CREATE  PROCEDURE PPFOR_TZB_Menu_GetModelListByLanguage(@languageID int)
 AS 
	SELECT * FROM [PPFOR_TZB_Menu] where belongLanguage=@languageID order by sort desc,[ID] asc
PPFOR_GO;

--查询栏目信息
if exists(select name from sysobjects where name='PPFOR_TZB_Menu_GetModel' and type='P')
	drop procedure PPFOR_TZB_Menu_GetModel
PPFOR_GO;

CREATE PROCEDURE PPFOR_TZB_Menu_GetModel(@ID int)
 AS 
	SELECT * FROM [PPFOR_TZB_Menu] WHERE [ID]=@ID 
PPFOR_GO;

--获取栏目静态文件名
if exists(select name from sysobjects where name='PPFOR_TZB_Menu_GetHtmlName' and type='P')
	drop procedure PPFOR_TZB_Menu_GetHtmlName
PPFOR_GO;
create procedure PPFOR_TZB_Menu_GetHtmlName(@ID int)
as
	select [htmlName] from [PPFOR_TZB_Menu] where [ID]=@ID 
PPFOR_GO;

--查询栏目模块目录
if exists(select name from sysobjects where name='PPFOR_TZB_Menu_GetModelPath' and type='P')
	drop procedure PPFOR_TZB_Menu_GetModelPath
PPFOR_GO;

create procedure PPFOR_TZB_Menu_GetModelPath(@belongMenu int)
as
	select modelFile from [PPFOR_TZB_Model] where [ID]=
		(select belongModel from PPFOR_TZB_Menu where [ID]=@belongMenu)
PPFOR_GO;

--更新栏目
if exists(select name from sysobjects where name='PPFOR_TZB_Menu_Update' and type='P')
	drop procedure PPFOR_TZB_Menu_Update
PPFOR_GO;

CREATE PROCEDURE PPFOR_TZB_Menu_Update(@ID int,@menuName nvarchar(30),@belongMenu int,
	@belongLanguage int,@belongModel int,@menuTemplate nvarchar(255),@subListTemplate nvarchar(255),
	@subDetailTemplate nvarchar(255),@menuIntroduction text,@openNew bit,@sort int,@show bit,
	@pageKeyWord nvarchar (255),@pageDescription nvarchar (255),@htmlName nvarchar (255))
 AS 
	UPDATE [PPFOR_TZB_Menu] SET	[menuName] = @menuName,[belongMenu]=@belongMenu,
	[belongLanguage] = @belongLanguage,[belongModel] = @belongModel,[menuTemplate] = @menuTemplate,
	[subListTemplate] = @subListTemplate,[subDetailTemplate] = @subDetailTemplate,
	[menuIntroduction] = @menuIntroduction,[openNew] = @openNew,[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_Menu_UpdateList' and type='P')
	drop procedure PPFOR_TZB_Menu_UpdateList
PPFOR_GO;

CREATE PROCEDURE PPFOR_TZB_Menu_UpdateList(@ID int,@openNew bit,@sort int,@show bit)
 AS 
	UPDATE [PPFOR_TZB_Menu] SET [openNew] = @openNew, [sort] = @sort,[show] = @show	WHERE [ID]=@ID 
PPFOR_GO;

--更新列表栏目静态页文件名
if exists(select name from sysobjects where name='PPFOR_TZB_Menu_UpdateHtmlName' and type='P')
	drop procedure PPFOR_TZB_Menu_UpdateHtmlName
PPFOR_GO;

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

--获取路径栏目
if exists(select name from sysobjects where name='PPFOR_TZB_Menu_GetShortInfo' and type='P')
	drop procedure PPFOR_TZB_Menu_GetShortInfo
PPFOR_GO;
create procedure PPFOR_TZB_Menu_GetShortInfo(@MenuID int)
as
	select [PPFOR_TZB_Menu].[ID],[PPFOR_TZB_Menu].[menuName],[menuPath]=[PPFOR_TZB_Model].[modelFile],
		[PPFOR_TZB_Menu].[htmlName] from [PPFOR_TZB_Menu],[PPFOR_TZB_Model] where [PPFOR_TZB_Menu].[show]=1 
		and [PPFOR_TZB_Menu].[ID]=@MenuID and [PPFOR_TZB_Menu].[belongModel]=[PPFOR_TZB_Model].[ID]
PPFOR_GO;

if exists(select name from sysobjects where name='PPFOR_TZB_Menu_GetCreateType' and type='P')
	drop procedure PPFOR_TZB_Menu_GetCreateType
PPFOR_GO;
--获取栏目所属语言静态页命名方式
create procedure PPFOR_TZB_Menu_GetCreateType(@MenuID int)	
as
	select [createType] from [PPFOR_TZB_Language] where [ID]=(select [belongLanguage] from [PPFOR_TZB_Menu]
		where [ID]=@MenuID)
PPFOR_GO;