-- ==============================
-- 长登科技 www.ppfor.com
-- 唐镇波   2010-04-21
-- ------------------------------
-- 评论组表
-- =================================
	--CommentStatus	参数说明
	--[IsNameNotNull] bit NOT NULL,				--名称是否必填 1	
	--[IsSubjectNotNull] bit NOT NULL,			--主题是否必填 2
	--[IsContentNotNull] bit NOT NULL,			--内容是否必填 4
	--[IsEmailNotNull] bit NOT NULL,			--邮箱是否必填 8 
	--[IsAddressNotNull] bit NOT NULL,			--地址是否必填 16 	
	--[IsWebsiteNotNull] bit NOT NULL,			--网址是否必填 32	
	--[IsPhoneNotNull] bit NOT NULL,			--电话是否必填 64	
	--[IsChatNotNull] bit NOT NULL,				--聊天号是否必填	128
-- ==============================
	--ReplyStatus	参数说明
	--[IsNameNotNull] bit NOT NULL,				--名称是否必填 1	
	--[IsEmailNotNull] bit NOT NULL,			--邮箱是否必填 2 
	--[IsAddressNotNull] bit NOT NULL,			--地址是否必填 4 	
	--[IsWebsiteNotNull] bit NOT NULL,			--网址是否必填 8	
	--[IsPhoneNotNull] bit NOT NULL,			--电话是否必填 16	
	--[IsChatNotNull] bit NOT NULL,				--聊天号是否必填	32
-- ==============================

--创建评论组表
if not exists(select name from sysobjects where name='PPFOR_TZB_CommentGroup' and type='U')
CREATE TABLE [PPFOR_TZB_CommentGroup] (
	[ID] int IDENTITY (1, 1) NOT NULL PRIMARY KEY,
	[BelongMenu] int NOT NULL,					--所属栏目
	[GroupType] char(1) COLLATE Chinese_PRC_CI_AS not null, 		--评论组类型 O不限，P单页面，N新闻，G产品，I图片，J招聘
	[GroupName] nvarchar(200) COLLATE Chinese_PRC_CS_AS NOT NULL ,	--评论组名称	
	[GroupContent] text NULL ,					--评论组内容
	[GroupImage] varchar (255) NULL ,			--图片
	[GroupOrder] int NOT NULL DEFAULT (0),		--排序
	[IsShow] bit NOT NULL DEFAULT (1),			--是否显示
	[IsCheckCode] bit NOT NULL,					--是否需要验证码
	[IsNeedAudit] bit NOT NULL,					--是否需要审核 
	[IsAllowReply] bit NOT NULL,				--是否允许回复
	[CommentStatus] int NOT NULL,				--评论参数状态
	[ReplyStatus] int NOT NULL,					--回复参数状态
	[UpdateTime] datetime NOT NULL ,			--更新时间
	[GroupKeyWord] nvarchar (255) COLLATE Chinese_PRC_CI_AS NULL ,		--META关键字
	[GroupDescription] nvarchar (255) COLLATE Chinese_PRC_CI_AS NULL ,	--META描述
	[GroupTemplate] varchar (255) Not NULL ,	--评论组模板		
	[IsPublic] bit not null,					--是否允许所有人浏览
	[AllowDegreeIds] text null,					--限制允许浏览的会员等级
	[IsSendEmail] bit not null,					--是否发送邮件
	[EmailAddress] varchar(100) null,			--邮箱地址
	[HtmlName] nvarchar (255) not null			--静态页名称
)
PPFOR_GO;

--添加评论组
if exists(select name from sysobjects where name='PPFOR_TZB_CommentGroup_Add' and type='P')
	drop procedure PPFOR_TZB_CommentGroup_Add
PPFOR_GO;
CREATE  PROCEDURE PPFOR_TZB_CommentGroup_Add(@ID int output,@BelongMenu int,@GroupType char(1),@GroupName nvarchar(200),
	@GroupContent text,@GroupImage varchar(255),@GroupOrder int,@IsShow bit,@IsCheckCode bit,@IsNeedAudit bit,
	@IsAllowReply bit,@CommentStatus int,@ReplyStatus int,@UpdateTime datetime,@GroupKeyWord nvarchar(255),
	@GroupDescription nvarchar(255),@GroupTemplate varchar(255),@IsPublic bit,@AllowDegreeIds text,
	@IsSendEmail bit,@EmailAddress varchar(100),@HtmlName nvarchar (255))
 AS 
	begin
		if EXISTS (SELECT [ID] FROM [PPFOR_TZB_CommentGroup] WHERE [GroupName] = @GroupName and [BelongMenu]=@BelongMenu)
			SET @ID = 0 
		else
		begin
			INSERT INTO [PPFOR_TZB_CommentGroup]([BelongMenu],[GroupType],[GroupName],[GroupContent],
				[GroupImage],[GroupOrder],[IsShow],[IsCheckCode],[IsNeedAudit],[IsAllowReply],[CommentStatus],
				[ReplyStatus],[UpdateTime],[GroupKeyWord],[GroupDescription],[GroupTemplate],[IsPublic],
				[AllowDegreeIds],[IsSendEmail],[EmailAddress],[HtmlName]) VALUES (@BelongMenu,@GroupType,@GroupName,
				@GroupContent,@GroupImage,@GroupOrder,@IsShow,@IsCheckCode,@IsNeedAudit,@IsAllowReply,
				@CommentStatus,@ReplyStatus,@UpdateTime,@GroupKeyWord,@GroupDescription,@GroupTemplate,
				@IsPublic,@AllowDegreeIds,@IsSendEmail,@EmailAddress,@HtmlName)
			if @@ROWCOUNT>0
				SET @ID = @@IDENTITY
			else
				SET @ID = 0 
		end
	end
PPFOR_GO;

--更新评论组
if exists(select name from sysobjects where name='PPFOR_TZB_CommentGroup_Update' and type='P')
	drop procedure PPFOR_TZB_CommentGroup_Update
PPFOR_GO;
CREATE   PROCEDURE PPFOR_TZB_CommentGroup_Update(@ID int output,@BelongMenu int,@GroupType char(1),@GroupName nvarchar(200),
	@GroupContent text,@GroupImage varchar(255),@GroupOrder int,@IsShow bit,@IsCheckCode bit,@IsNeedAudit bit,
	@IsAllowReply bit,@CommentStatus int,@ReplyStatus int,@UpdateTime datetime,@GroupKeyWord nvarchar(255),
	@GroupDescription nvarchar(255),@GroupTemplate varchar(255),@IsPublic bit,@AllowDegreeIds text,
	@IsSendEmail bit,@EmailAddress varchar(100),@HtmlName nvarchar (255))
 AS 	
	if NOT EXISTS (SELECT [ID] FROM [PPFOR_TZB_CommentGroup] WHERE [GroupName] = @GroupName 
		and [BelongMenu]=@BelongMenu and [ID]<>@ID)			
		UPDATE [PPFOR_TZB_CommentGroup] SET [GroupName]=@GroupName,[GroupContent]=@GroupContent,
		[GroupImage]=@GroupImage,[GroupOrder]=@GroupOrder,[IsShow]=@IsShow,[IsCheckCode]=@IsCheckCode,
		[IsNeedAudit]=@IsNeedAudit,[IsAllowReply]=@IsAllowReply,[CommentStatus]=@CommentStatus,
		[ReplyStatus]=@ReplyStatus,[UpdateTime]=@UpdateTime,[GroupKeyWord]=@GroupKeyWord,
		[GroupDescription]=@GroupDescription,[GroupTemplate]=@GroupTemplate,[IsPublic]=@IsPublic,
		[AllowDegreeIds]=@AllowDegreeIds,[IsSendEmail]=@IsSendEmail,[EmailAddress]=@EmailAddress,
		[HtmlName]=@HtmlName WHERE [ID]=@ID and [BelongMenu]=@BelongMenu
	if NOT EXISTS (SELECT [ID] FROM [PPFOR_TZB_Comment] WHERE [GroupID] = @ID) 
		UPDATE [PPFOR_TZB_CommentGroup] SET [GroupType]=@GroupType where [ID]=@ID	
PPFOR_GO;

--更新评论组列表信息
if exists(select name from sysobjects where name='PPFOR_TZB_CommentGroup_UpdateList' and type='P')
	drop procedure PPFOR_TZB_CommentGroup_UpdateList
PPFOR_GO;
CREATE PROCEDURE PPFOR_TZB_CommentGroup_UpdateList(@ID int,@GroupOrder int,@IsShow bit,@UpdateTime datetime)
 AS 
	UPDATE [PPFOR_TZB_CommentGroup] SET [GroupOrder] = @GroupOrder,[IsShow] = @IsShow,[UpdateTime] = @UpdateTime
		WHERE [ID]=@ID 
PPFOR_GO;

--删除评论组
if exists(select name from sysobjects where name='PPFOR_TZB_CommentGroup_Delete' and type='P')
	drop procedure PPFOR_TZB_CommentGroup_Delete
PPFOR_GO;
CREATE PROCEDURE PPFOR_TZB_CommentGroup_Delete(@ID int)
 AS 
	DELETE [PPFOR_TZB_CommentGroup] WHERE [ID]=@ID 
PPFOR_GO;

--是否存在评论组
if exists(select name from sysobjects where name='PPFOR_TZB_CommentGroup_IsExists' and type='P')
	drop procedure PPFOR_TZB_CommentGroup_IsExists
PPFOR_GO;
CREATE PROCEDURE PPFOR_TZB_CommentGroup_IsExists(@GroupName nvarchar (200),@BelongMenu int,@retValue bit out)
AS
	begin
		DECLARE @TempID int
		SELECT @TempID = count(1) FROM [PPFOR_TZB_CommentGroup] WHERE [GroupName] = @GroupName 
			and [BelongMenu]=@BelongMenu
		IF @TempID = 0
			set @retValue= 0
		ELSE
			set @retValue= 1
	end
PPFOR_GO;

----查询出所有的评论组--------------
if exists (select * from sysobjects where name='PPFOR_TZB_CommentGroup_GetModelList')
drop procedure PPFOR_TZB_CommentGroup_GetModelList
PPFOR_GO;
create procedure PPFOR_TZB_CommentGroup_GetModelList(@BelongMenu int)
as 
     select [ID],[GroupType],[GroupName],[UpdateTime],[GroupOrder],[IsShow],[BelongMenu],[HtmlName]
     	from [PPFOR_TZB_CommentGroup] where [BelongMenu]=@BelongMenu order by [GroupOrder] desc,[ID] asc
PPFOR_GO;

----查询出所有的评论组--------------
if exists (select * from sysobjects where name='PPFOR_TZB_CommentGroup_GetAllModelList')
drop procedure PPFOR_TZB_CommentGroup_GetAllModelList
PPFOR_GO;
create procedure PPFOR_TZB_CommentGroup_GetAllModelList(@BelongMenu int)
as 
     select * from [PPFOR_TZB_CommentGroup] where [BelongMenu]=@BelongMenu order by [GroupOrder] desc,[ID] asc
PPFOR_GO;

----查询出所有的评论组ID和名称----------------
if exists (select * from sysobjects where name='PPFOR_TZB_CommentGroup_GetNameList')
drop procedure PPFOR_TZB_CommentGroup_GetNameList
PPFOR_GO;
create procedure PPFOR_TZB_CommentGroup_GetNameList(@GroupType char(1),@BelongMenu int)
as	
     select [ID],[GroupType],[GroupName],[IsShow],[HtmlName] from [PPFOR_TZB_CommentGroup] 
		where [GroupType]=@GroupType and [BelongMenu]=@BelongMenu order by [GroupOrder] desc,[ID] asc
PPFOR_GO;

----查询出栏目下所有的评论组ID和名称----------------
if exists (select * from sysobjects where name='PPFOR_TZB_CommentGroup_GetNameListByMenu')
drop procedure PPFOR_TZB_CommentGroup_GetNameListByMenu
PPFOR_GO;
create procedure PPFOR_TZB_CommentGroup_GetNameListByMenu(@BelongMenu int)
as	
     select [ID],[GroupName],[IsShow],[HtmlName] from [PPFOR_TZB_CommentGroup] 
		where [BelongMenu]=@BelongMenu order by [GroupOrder] desc,[ID] asc
PPFOR_GO;

----获取评论组详细-------------
if exists (select * from sysobjects where name='PPFOR_TZB_CommentGroup_GetModel')
drop procedure PPFOR_TZB_CommentGroup_GetModel
PPFOR_GO;
create procedure PPFOR_TZB_CommentGroup_GetModel(@ID int)
as
     select * from [PPFOR_TZB_CommentGroup] where [ID]=@ID
PPFOR_GO;

-------------------------------
-----标签
-------------------------------
----获取评论组列表-------------
if exists(select name from sysobjects where name='PPFOR_TZB_CommentGroup_GetModelListForLabel' and type='P')
	drop procedure PPFOR_TZB_CommentGroup_GetModelListForLabel
PPFOR_GO;
create procedure PPFOR_TZB_CommentGroup_GetModelListForLabel(@BelongLanguage int,@GroupType char(1))
as
	if(@BelongLanguage<=0)
		select [ID],[BelongMenu],[GroupType],[GroupName],[IsCheckCode],[IsNeedAudit],[IsAllowReply],
			[CommentStatus],[ReplyStatus],[IsPublic],[AllowDegreeIds],[HtmlName] from [PPFOR_TZB_CommentGroup] 
				where [IsShow]=1 and [GroupType]=@GroupType order by [GroupOrder] desc,[ID] asc
	else
		select [ID],[BelongMenu],[GroupType],[GroupName],[IsCheckCode],[IsNeedAudit],[IsAllowReply],
			[CommentStatus],[ReplyStatus],[IsPublic],[AllowDegreeIds],[HtmlName] from [PPFOR_TZB_CommentGroup] 
			where [IsShow]=1 and [GroupType]=@GroupType and [BelongMenu] =any 
			(select [ID] from [PPFOR_TZB_Menu] where [belongLanguage]=@BelongLanguage and [belongMenu]=0)
			order by [GroupOrder] desc,[ID] asc
PPFOR_GO;

----获取评论组列表-------------
if exists(select name from sysobjects where name='PPFOR_TZB_CommentGroup_GetShortListForLabel' and type='P')
	drop procedure PPFOR_TZB_CommentGroup_GetShortListForLabel
PPFOR_GO;
create procedure PPFOR_TZB_CommentGroup_GetShortListForLabel(@BelongLanguage int)
as
	begin
	if(@BelongLanguage=0)
		select [ID],[GroupType],[GroupName],[BelongMenu],[HtmlName] from [PPFOR_TZB_CommentGroup] 
	else if @BelongLanguage>0
		select [ID],[GroupType],[GroupName],[BelongMenu],[HtmlName] from [PPFOR_TZB_CommentGroup] where [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_CommentGroup_GetShortInfo' and type='P')
	drop procedure PPFOR_TZB_CommentGroup_GetShortInfo
PPFOR_GO;
create procedure PPFOR_TZB_CommentGroup_GetShortInfo(@ID int)
as
	select [ID],[GroupType],[GroupName],[BelongMenu],[HtmlName] from [PPFOR_TZB_CommentGroup] 
		where [IsShow]=1 and [ID]=@ID
PPFOR_GO;

--更新评论组静态页文件名
if exists(select name from sysobjects where name='PPFOR_TZB_CommentGroup_UpdateHtmlName' and type='P')
	drop procedure PPFOR_TZB_CommentGroup_UpdateHtmlName
PPFOR_GO;

CREATE PROCEDURE PPFOR_TZB_CommentGroup_UpdateHtmlName(@ID int,@HtmlName nvarchar(255))
 AS 
	UPDATE [PPFOR_TZB_CommentGroup] SET [HtmlName] = @HtmlName WHERE [ID]=@ID 
PPFOR_GO;

--获取评论组静态文件名
if exists(select name from sysobjects where name='PPFOR_TZB_CommentGroup_GetHtmlName' and type='P')
	drop procedure PPFOR_TZB_CommentGroup_GetHtmlName
PPFOR_GO;
create procedure PPFOR_TZB_CommentGroup_GetHtmlName(@ID int)
as
	select [HtmlName] from [PPFOR_TZB_CommentGroup] where [ID]=@ID 
PPFOR_GO;