-- ==============================
-- 长登科技 www.ppfor.com
-- 唐镇波   2010-04-21
-- ------------------------------
-- 评论表
-- ==============================

--创建评论表
if not exists(select name from sysobjects where name='PPFOR_TZB_Comment' and type='U')
CREATE TABLE [PPFOR_TZB_Comment] (
	[ID] int IDENTITY (1, 1) NOT NULL PRIMARY KEY,			--评论ID
	[GroupID] int NOT NULL REFERENCES [PPFOR_TZB_CommentGroup]([ID]) ON DELETE CASCADE,	--评论组ID
	[MemberID] int NOT NULL,	 							--会员ID	
	[ObjectID] int NOT NULL,		 						--评论对象ID	    
	[IsAudited] bit NOT NULL,								--是否已通过审核
	[SubmitTime] datetime NOT NULL,							--提交时间
	[AuditedTime] datetime NOT NULL, 						--通过审核时间
	[Name] nvarchar(20) null,								--评论人名称
	[Subject] nvarchar(50) null,							--评论主题
	[Content] nvarchar(1000) null,							--评论内容
	[Email] varchar(100) null,								--联系邮箱
	[Address] varchar(150) null,							--联系地址
	[Website] varchar(150) null,							--网址
	[Phone] varchar(20) null,								--电话	
	[Chat] varchar(50) null									--其他
)
PPFOR_GO;

--添加评论
if exists(select name from sysobjects where name='PPFOR_TZB_Comment_Add' and type='P')
	drop procedure PPFOR_TZB_Comment_Add
PPFOR_GO;
CREATE PROCEDURE PPFOR_TZB_Comment_Add(@ID int output,@GroupID int,@MemberID int,@ObjectID int,
	@IsAudited bit,@SubmitTime datetime,@AuditedTime datetime,@Name nvarchar(20),@Subject nvarchar(50),
	@Content nvarchar(1000),@Email varchar(100),@Address varchar(150),@Website varchar(150),
	@Phone varchar(20),@Chat varchar(50))
 AS 
	INSERT INTO [PPFOR_TZB_Comment]([GroupID],[MemberID],[ObjectID],[IsAudited],[SubmitTime],[AuditedTime],
		[Name],[Subject],[Content],[Email],[Address],[Website],[Phone],[Chat]) VALUES (@GroupID,@MemberID,
		@ObjectID,@IsAudited,@SubmitTime,@AuditedTime,@Name,@Subject,@Content,@Email,@Address,@Website,
		@Phone,@Chat)
	if @@ROWCOUNT>0	
		SET @ID = @@IDENTITY
	else
		SET @ID = 0
PPFOR_GO;

--更新评论
if exists(select name from sysobjects where name='PPFOR_TZB_Comment_SetAudited' and type='P')
	drop procedure PPFOR_TZB_Comment_SetAudited
PPFOR_GO;
CREATE  PROCEDURE PPFOR_TZB_Comment_SetAudited(@ID int,@AuditedTime datetime)
 AS 	
	UPDATE [PPFOR_TZB_Comment] SET [IsAudited]=1,[AuditedTime]=@AuditedTime
		WHERE [ID]=@ID and [IsAudited]=0
PPFOR_GO;
if exists(select name from sysobjects where name='PPFOR_TZB_Comment_CancelAudited' and type='P')
	drop procedure PPFOR_TZB_Comment_CancelAudited
PPFOR_GO;
CREATE  PROCEDURE PPFOR_TZB_Comment_CancelAudited(@ID int)
 AS 	
	UPDATE [PPFOR_TZB_Comment] SET [IsAudited]=0 WHERE [ID]=@ID and [IsAudited]=1
PPFOR_GO;
--删除评论
if exists(select name from sysobjects where name='PPFOR_TZB_Comment_Delete' and type='P')
	drop procedure PPFOR_TZB_Comment_Delete
PPFOR_GO;
CREATE PROCEDURE PPFOR_TZB_Comment_Delete(@ID int)
 AS 
	DELETE [PPFOR_TZB_Comment] WHERE [ID]=@ID
PPFOR_GO;

--获取评论
if exists(select name from sysobjects where name='PPFOR_TZB_Comment_GetModel' and type='P')
	drop procedure PPFOR_TZB_Comment_GetModel
PPFOR_GO;
CREATE   PROCEDURE PPFOR_TZB_Comment_GetModel(@ID int)
 AS 
	SELECT * from [PPFOR_TZB_Comment] where [ID]=@ID  
PPFOR_GO;

if exists(select name from sysobjects where name='PPFOR_TZB_Comment_GetReplyCount' and type='P')
	drop procedure PPFOR_TZB_Comment_GetReplyCount
PPFOR_GO;
CREATE PROCEDURE PPFOR_TZB_Comment_GetReplyCount(@CommentID int)
 AS 	
	select ReplyCount=count([ID]),AdminCount=sum(cast([IsAdmin] as int)),
		AuditedCount=sum(cast([IsAudited] as int)) from [PPFOR_TZB_Reply] 
		where [CommentID]=@CommentID		
PPFOR_GO;

if exists(select name from sysobjects where name='PPFOR_TZB_Comment_GetModelCount' and type='P')
	drop procedure PPFOR_TZB_Comment_GetModelCount
PPFOR_GO;
CREATE PROCEDURE PPFOR_TZB_Comment_GetModelCount(@GroupID int,@ObjectType char(1),@ObjectID int)
 AS
	if @GroupID>0  	
		select count([ID]) from [PPFOR_TZB_Comment] where [GroupID]=@GroupID and [IsAudited]=1	
	else
		select count([ID]) from [PPFOR_TZB_Comment] where [ObjectID]=@ObjectID and [IsAudited]=1
			and [GroupID]=any (select [ID] from [PPFOR_TZB_CommentGroup] where [GroupType]=@ObjectType)
PPFOR_GO;

--分页评论资料--
if exists(select name from sysobjects where name='PPFOR_TZB_Comment_GetModelNewList' and type='P')
	drop procedure PPFOR_TZB_Comment_GetModelNewList
PPFOR_GO;
create procedure PPFOR_TZB_Comment_GetModelNewList(@number int)
as
	if @number>0
		set ROWCOUNT @number
	select * from [PPFOR_TZB_Comment] order by [SubmitTime] desc,[AuditedTime] desc
PPFOR_GO;

--分页返回评论资料--
if exists(select name from sysobjects where name='PPFOR_TZB_Comment_GetModelListByGroupID' and type='P')
	drop procedure PPFOR_TZB_Comment_GetModelListByGroupID
PPFOR_GO;
create procedure PPFOR_TZB_Comment_GetModelListByGroupID(@GroupID int,@pageSize int,@pageIndex int)
as
	begin
		declare @rc int
		declare @pc int		
		declare @sqlStr nvarchar(2000)
		declare @sqlStr2 nvarchar(1000)
		
		if @pageSize<1
			set @pageSize=20

		select @rc=count([ID]) from [PPFOR_TZB_Comment] where [GroupID]=@GroupID
		if @rc=0				
			set @pc=0				
		else			
			set @pc = ceiling(@rc * 1.0 / @pageSize)				

		if @pageIndex>@pc
			set @pageIndex=@pc
		else if @pageIndex<1
			set @pageIndex=1
		
		set @sqlStr2=' * from [PPFOR_TZB_Comment] where [GroupID]=' + str(@GroupID) + ' '
	
		if @pageIndex <= 1 or @pc <= 1
			set @sqlStr='select top ' + str(@pageSize) + @sqlStr2 + ' order by [AuditedTime] desc, [SubmitTime] desc'
		else if @pageIndex = @pc
			set @sqlStr ='select * from (select top ' + str( @rc - @pageSize * (@pageIndex-1) ) 
				+ @sqlStr2 + ' order by [AuditedTime] asc, [SubmitTime] asc) Temptable order by [AuditedTime] desc, [SubmitTime] desc'
		else  
			set @sqlStr ='select top ' + str(@pageSize) + ' * from (select top '
				+ str( @rc - @pageSize * (@pageIndex-1) ) + @sqlStr2 + ' order by [AuditedTime] asc, [SubmitTime] asc) Temptable order by [AuditedTime] desc, [SubmitTime] desc'
		
		exec (@sqlStr)
		select @rc as recordCount,@pc as pageCount
	end
PPFOR_GO;

--分页返回评论资料--
if exists(select name from sysobjects where name='PPFOR_TZB_Comment_GetModelListByStatus' and type='P')
	drop procedure PPFOR_TZB_Comment_GetModelListByStatus
PPFOR_GO;
create procedure PPFOR_TZB_Comment_GetModelListByStatus(@GroupID int,@IsAudited bit,@pageSize int,@pageIndex int)
as
	begin
		declare @rc int
		declare @pc int
		declare @sqlStr nvarchar(2000)
		declare @sqlStr2 nvarchar(1000)
		
		if @pageSize<1
			set @pageSize=20

		select @rc=count([ID]) from [PPFOR_TZB_Comment] where [GroupID]=@GroupID and [IsAudited]=@IsAudited
		if @rc=0
			set @pc=0
		else
			set @pc = ceiling(@rc * 1.0 / @pageSize)

		if @pageIndex>@pc
			set @pageIndex=@pc
		else if @pageIndex<1
			set @pageIndex=1
		
		set @sqlStr2=' * from [PPFOR_TZB_Comment] where [GroupID]=' + str(@GroupID) + ' and [IsAudited]='+str(@IsAudited)+' '
	
		if @pageIndex <= 1 or @pc <= 1
			set @sqlStr='select top ' + str(@pageSize) + @sqlStr2 + ' order by [AuditedTime] desc, [SubmitTime] desc'
		else if @pageIndex = @pc
			set @sqlStr ='select * from (select top ' + str( @rc - @pageSize * (@pageIndex-1) ) 
				+ @sqlStr2 + ' order by [AuditedTime] asc, [SubmitTime] asc) Temptable order by [AuditedTime] desc, [SubmitTime] desc'
		else  
			set @sqlStr ='select top ' + str(@pageSize) + ' * from (select top '
				+ str( @rc - @pageSize * (@pageIndex-1) ) + @sqlStr2 + ' order by [AuditedTime] asc, [SubmitTime] asc) Temptable order by [AuditedTime] desc, [SubmitTime] desc'
		
		exec (@sqlStr)
		select @rc as recordCount,@pc as pageCount
	end
PPFOR_GO;
if exists(select name from sysobjects where name='PPFOR_TZB_Comment_GetModelListForLabel' and type='P')
	drop procedure PPFOR_TZB_Comment_GetModelListForLabel
PPFOR_GO;
--获取评论列表
--未排序
create procedure PPFOR_TZB_Comment_GetModelListForLabel(@belongLanguage int,@belongMenu int,@GroupID int,@ObjectID int,@ObjectType char(1),@memberDegree int,@number int)
as
	begin
		declare @sql varchar(1000)
		declare @sqlStr2 varchar(255)		
		if @number>=0			
		begin
			if @GroupID>0
				set	@sql= 'select top ' + str(@number) + ' * from [PPFOR_TZB_Comment] where [IsAudited]=1 and [GroupID]=(select [ID] from PPFOR_TZB_CommentGroup where IsShow=1 and [ID]=' + str(@GroupID)			
			else if len(@ObjectType)=1
				set @sql= 'select top ' + str(@number) + ' * from [PPFOR_TZB_Comment] where [IsAudited]=1 and [ObjectID]='+str(@ObjectID)+' and [GroupID]=any(select [ID] from [PPFOR_TZB_CommentGroup] where [IsShow]=1 and [GroupType]='''+@ObjectType+''''			
			else if @belongMenu>0
				set	@sql= 'select top ' + str(@number) + ' * from [PPFOR_TZB_Comment] where [IsAudited]=1 and [GroupID]=any (select [ID] from PPFOR_TZB_CommentGroup where IsShow=1 and BelongMenu=' + str(@belongMenu)
			else if @belongLanguage>0
				set	@sql= 'select top ' + str(@number) + ' * from [PPFOR_TZB_Comment] where [IsAudited]=1 and [GroupID]=any (select [ID] from PPFOR_TZB_CommentGroup where IsShow=1 and BelongMenu= any(select [ID] from PPFOR_TZB_Menu where show=1 and belongLanguage=' + str(@belongLanguage) + ')'
			if @sql is not null
			begin	
				if @memberDegree=0 
					set	@sql=@sql + ' and [IsPublic]=1'
				else if @memberDegree>0
					set	@sql=@sql + ' and ([IsPublic]=1 or [AllowDegreeIds] like ''%,'+ rtrim(ltrim(str(@memberDegree)))+',%'')'			
				set	@sql=@sql+')'					

				set @sql=@sql + ' order by [AuditedTime] desc, [SubmitTime] desc'				
				exec (@sql)
			end
		end
	end
PPFOR_GO;

--返回单页列表
if exists(select name from sysobjects where name='PPFOR_TZB_Comment_GetModelListInTypeByPage' and type='P')
	drop procedure PPFOR_TZB_Comment_GetModelListInTypeByPage
PPFOR_GO;
create procedure PPFOR_TZB_Comment_GetModelListInTypeByPage(@belongLanguage int,@belongMenu int,@GroupID int,@ObjectID int,@ObjectType char(1),@memberDegree int,@pageSize int,@pageIndex int) 
as
	begin
		declare @rc int
		declare @pc int
		declare @sqlStr nvarchar(2000)
		declare @sqlStr2 nvarchar(1000)

		if @pageSize<1
			set @pageSize=20

		set @sqlStr2='select @rc=count([ID]) from [PPFOR_TZB_Comment] where [IsAudited]=1'
		set @sqlStr=')'
		if @GroupID>0
			set	@sqlStr2=@sqlStr2+' and [GroupID]=(select [ID] from PPFOR_TZB_CommentGroup where IsShow=1 and [ID]=' + str(@GroupID)
		else if len(@ObjectType)=1	
			set @sqlStr2=@sqlStr2+' and [ObjectID]='+str(@ObjectID)+' and [GroupID]=any(select [ID] from [PPFOR_TZB_CommentGroup] where [IsShow]=1 and [GroupType]='''+@ObjectType+''''
		else if @belongMenu>0
			set	@sqlStr2=@sqlStr2+' and [GroupID]=any (select [ID] from PPFOR_TZB_CommentGroup where IsShow=1 and BelongMenu=' + str(@belongMenu)
		else if @belongLanguage>0
			set	@sqlStr2=@sqlStr2+' and [GroupID]=any (select [ID] from PPFOR_TZB_CommentGroup where IsShow=1 and BelongMenu= any(select [ID] from PPFOR_TZB_Menu where show=1 and belongLanguage=' + str(@belongLanguage) + ')'
		else 
			set @sqlStr=''
		if @memberDegree=0 
			set	@sqlStr2=@sqlStr2 + ' and IsPublic=1'
		else if @memberDegree>0
			set	@sqlStr2=@sqlStr2 + ' and (IsPublic=1 or AllowDegreeIds like ''%,'+ rtrim(ltrim(str(@memberDegree)))+',%'')'
		set @sqlStr2=@sqlStr2+@sqlStr
		execute sp_executesql @sqlStr2, N'@rc int output', @rc output		

		if @rc=0
			set @pc=0
		else
			set @pc = ceiling(@rc * 1.0 / @pageSize)

		if @pageIndex>@pc
			set @pageIndex=@pc
		else if @pageIndex<1
			set @pageIndex=1

		set @sqlStr2=' * from [PPFOR_TZB_Comment] where [IsAudited]=1'
		if @GroupID>0
			set	@sqlStr2=@sqlStr2+' and [GroupID]=(select [ID] from PPFOR_TZB_CommentGroup where IsShow=1 and [ID]=' + str(@GroupID)
		else if len(@ObjectType)=1	
			set @sqlStr2=@sqlStr2+' and [ObjectID]='+str(@ObjectID)+' and [GroupID]=any(select [ID] from [PPFOR_TZB_CommentGroup] where [IsShow]=1 and [GroupType]='''+@ObjectType+''''
		else if @belongMenu>0
			set	@sqlStr2=@sqlStr2+' and [GroupID]=any (select [ID] from PPFOR_TZB_CommentGroup where IsShow=1 and BelongMenu=' + str(@belongMenu)
		else if @belongLanguage>0
			set	@sqlStr2=@sqlStr2+' and [GroupID]=any (select [ID] from PPFOR_TZB_CommentGroup where IsShow=1 and BelongMenu= any(select [ID] from PPFOR_TZB_Menu where show=1 and belongLanguage=' + str(@belongLanguage) + ')'

		if @memberDegree=0 
			set	@sqlStr2=@sqlStr2 + ' and IsPublic=1'
		else if @memberDegree>0
			set	@sqlStr2=@sqlStr2 + ' and (IsPublic=1 or AllowDegreeIds like ''%,'+ rtrim(ltrim(str(@memberDegree)))+',%'')'
		set @sqlStr2=@sqlStr2+@sqlStr	

		if @pageIndex <= 1 or @pc <= 1
			set @sqlStr='select top ' + str(@pageSize) + @sqlStr2 + ' order by [AuditedTime] desc, [SubmitTime] desc'
		else if @pageIndex = @pc
			set @sqlStr = 'select * from (select top ' + str( @rc - @pageSize * (@pageIndex-1) ) 
				+ @sqlStr2 + ' order by [AuditedTime] asc, [SubmitTime] asc) Temptable order by [AuditedTime] desc, [SubmitTime] desc'
		else  
			set @sqlStr ='select top ' + str(@pageSize) + ' * from (select top '
				+ str( @rc - @pageSize * (@pageIndex-1) ) + @sqlStr2 + ' order by [AuditedTime] asc, [SubmitTime] asc) Temptable order by [AuditedTime] desc, [SubmitTime] desc'
		exec (@sqlStr)
		select @rc as recordCount,@pc as pageCount
	end
PPFOR_GO;