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

--创建回复表
if not exists(select name from sysobjects where name='PPFOR_TZB_Reply' and type='U')
CREATE TABLE [PPFOR_TZB_Reply] (
	[ID] int IDENTITY (1, 1) NOT NULL PRIMARY KEY,			--回复ID
	[CommentID] int NOT NULL REFERENCES [PPFOR_TZB_Comment]([ID]) ON DELETE CASCADE,	--评论ID
	[MemberID] int NOT NULL,	 							--会员ID
	[IsAdmin] bit NOT NULL,									--是否管理员  		
	[IsAudited] bit NOT NULL,								--是否已通过审核
	[ReplyTime] datetime NOT NULL,							--回复时间
	[AuditedTime] datetime NOT NULL, 						--通过审核时间
	[Name] nvarchar(20) NULL,								--回复人名称
	[Content] nvarchar(1000) NOT 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_Reply_Add' and type='P')
	drop procedure PPFOR_TZB_Reply_Add
PPFOR_GO;
CREATE PROCEDURE PPFOR_TZB_Reply_Add(@ID int output,@CommentID int,@MemberID int,@IsAdmin bit,
	@IsAudited bit,@ReplyTime datetime,@AuditedTime datetime,@Name nvarchar(20),
	@Content nvarchar(1000),@Email varchar(100),@Address varchar(150),@Website varchar(150),
	@Phone varchar(20),@Chat varchar(50))
 AS 
	INSERT INTO [PPFOR_TZB_Reply]([CommentID],[MemberID],[IsAdmin],[IsAudited],[ReplyTime],[AuditedTime],
		[Name],[Content],[Email],[Address],[Website],[Phone],[Chat]) VALUES (@CommentID,@MemberID,
		@IsAdmin,@IsAudited,@ReplyTime,@AuditedTime,@Name,@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_Reply_SetAudited' and type='P')
	drop procedure PPFOR_TZB_Reply_SetAudited
PPFOR_GO;
CREATE  PROCEDURE PPFOR_TZB_Reply_SetAudited(@ID int,@AuditedTime datetime)
 AS 	
	UPDATE [PPFOR_TZB_Reply] SET [IsAudited]=1,[AuditedTime]=@AuditedTime
		WHERE [ID]=@ID and [IsAudited]=0
PPFOR_GO;
--删除回复
if exists(select name from sysobjects where name='PPFOR_TZB_Reply_Delete' and type='P')
	drop procedure PPFOR_TZB_Reply_Delete
PPFOR_GO;
CREATE PROCEDURE PPFOR_TZB_Reply_Delete(@ID int)
 AS 
	DELETE [PPFOR_TZB_Reply] WHERE [ID]=@ID
PPFOR_GO;

--获取回复
if exists(select name from sysobjects where name='PPFOR_TZB_Reply_GetModel' and type='P')
	drop procedure PPFOR_TZB_Reply_GetModel
PPFOR_GO;
CREATE   PROCEDURE PPFOR_TZB_Reply_GetModel(@ID int)
 AS 
	SELECT * FROM [PPFOR_TZB_Reply] WHERE [ID]=@ID 
PPFOR_GO;

--分页返回回复资料--
if exists(select name from sysobjects where name='PPFOR_TZB_Reply_GetModelListByCommentID' and type='P')
	drop procedure PPFOR_TZB_Reply_GetModelListByCommentID
PPFOR_GO;
create procedure PPFOR_TZB_Reply_GetModelListByCommentID(@CommentID 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_Reply] where [CommentID]=@CommentID
		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_Reply] where [CommentID]=' + str(@CommentID) + ' '
	
		if @pageIndex <= 1 or @pc <= 1
			set @sqlStr='select top ' + str(@pageSize) + @sqlStr2 + ' order by [AuditedTime] desc, [ReplyTime] desc'
		else if @pageIndex = @pc
			set @sqlStr ='select * from (select top ' + str( @rc - @pageSize * (@pageIndex-1) ) 
				+ @sqlStr2 + ' order by [AuditedTime] asc, [ReplyTime] asc) Temptable order by [AuditedTime] desc, [ReplyTime] desc'
		else  
			set @sqlStr ='select top ' + str(@pageSize) + ' * from (select top '
				+ str( @rc - @pageSize * (@pageIndex-1) ) + @sqlStr2 + ' order by [AuditedTime] asc, [ReplyTime] asc) Temptable order by [AuditedTime] desc, [ReplyTime] desc'
		
		exec (@sqlStr)
		select @rc as recordCount,@pc as pageCount
	end
PPFOR_GO;
--分页返回回复资料--
if exists(select name from sysobjects where name='PPFOR_TZB_Reply_GetModelListByStatus' and type='P')
	drop procedure PPFOR_TZB_Reply_GetModelListByStatus
PPFOR_GO;
create procedure PPFOR_TZB_Reply_GetModelListByStatus(@CommentID 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_Reply] where [CommentID]=@CommentID 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_Reply] where [CommentID]=' + str(@CommentID) + ' and [IsAudited]='+str(@IsAudited)+' '
	
		if @pageIndex <= 1 or @pc <= 1
			set @sqlStr='select top ' + str(@pageSize) + @sqlStr2 + ' order by [AuditedTime] desc, [ReplyTime] desc'
		else if @pageIndex = @pc
			set @sqlStr ='select * from (select top ' + str( @rc - @pageSize * (@pageIndex-1) ) 
				+ @sqlStr2 + ' order by [AuditedTime] asc, [ReplyTime] asc) Temptable order by [AuditedTime] desc, [ReplyTime] desc'
		else  
			set @sqlStr ='select top ' + str(@pageSize) + ' * from (select top '
				+ str( @rc - @pageSize * (@pageIndex-1) ) + @sqlStr2 + ' order by [AuditedTime] asc, [ReplyTime] asc) Temptable order by [AuditedTime] desc, [ReplyTime] desc'
		
		exec (@sqlStr)
		select @rc as recordCount,@pc as pageCount
	end
PPFOR_GO;
if exists(select name from sysobjects where name='PPFOR_TZB_Reply_GetModelListForLabel' and type='P')
	drop procedure PPFOR_TZB_Reply_GetModelListForLabel
PPFOR_GO;
create procedure PPFOR_TZB_Reply_GetModelListForLabel(@CommentID int)
as
	begin		
		select * from [PPFOR_TZB_Reply] where [CommentID]=@CommentID and [IsAudited]=1
			order by [AuditedTime] desc, [ReplyTime] desc
	end
PPFOR_GO;
