-- ==============================
-- 长登科技 www.ppfor.com
-- 唐镇波   2010-04-19
-- ------------------------------
-- 会员消息表
-- ==============================

--创建消息表
if not exists (select * from sysobjects where id = object_id(N'[PPFOR_TZB_Message]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
CREATE TABLE [PPFOR_TZB_Message] (
	[msgID] int identity(1,1) not null primary key,
	[msgSendMemberID] int not null,			--发送方ID
	[msgSendIsAdmin] bit not null,			--发送方是否管理员
	[msgReceiveMemberID] int not null,		--接收方ID
	[msgReceiveIsAdmin] bit not null,		--接收方是否管理员
	[msgType] tinyint not null, 			--消息类型， 0发送， 1接收, 2草稿
	[msgBodyID] int not null REFERENCES [PPFOR_TZB_MessageBody]([msgBodyID]) ON DELETE CASCADE,	--消息内容ID
	[msgSendTime] datetime not null,		--消息发送时间
	[msgHasRead] bit not null default(0)	--是否已读	
)
PPFOR_GO;

--添加消息
if exists (select * from sysobjects where id = object_id(N'[PPFOR_TZB_Message_Add]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [PPFOR_TZB_Message_Add]
PPFOR_GO;
create procedure PPFOR_TZB_Message_Add(@msgSendMemberID int,@msgSendIsAdmin bit,
	@msgReceiveMemberID int,@msgReceiveIsAdmin bit,@msgType tinyint,@msgBodyID int,
	@msgSendTime datetime,@msgID int out)
as
	begin
		insert into [PPFOR_TZB_Message] (msgSendMemberID,msgSendIsAdmin,msgReceiveMemberID,
			msgReceiveIsAdmin,msgType,msgBodyID,msgSendTime,msgHasRead) values (@msgSendMemberID,
			@msgSendIsAdmin,@msgReceiveMemberID,@msgReceiveIsAdmin,@msgType,@msgBodyID,@msgSendTime,0)
		set @msgID=@@identity
	end
PPFOR_GO;

--更改接收方ID
if exists (select * from sysobjects where id = object_id(N'[PPFOR_TZB_Message_UpdateReceiveMember]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [PPFOR_TZB_Message_UpdateReceiveMember]
PPFOR_GO;
create procedure PPFOR_TZB_Message_UpdateReceiveMember(@msgID int,@memberID int,@msgType tinyint)
as
	begin
		update [PPFOR_TZB_Message] set msgReceiveMemberID=@memberID where msgID=@msgID 
			and msgType=@msgType
	end
PPFOR_GO;

--设为已读
if exists (select * from sysobjects where id = object_id(N'[PPFOR_TZB_Message_SetHadRead]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [PPFOR_TZB_Message_SetHadRead]
PPFOR_GO;
create procedure PPFOR_TZB_Message_SetHadRead(@msgID int,@msgType tinyint)
as
	begin
		update [PPFOR_TZB_Message] set msgHasRead=1 where msgID=@msgID and msgType=@msgType
	end
PPFOR_GO;

--获取未读消息
if exists (select * from sysobjects where id = object_id(N'[PPFOR_TZB_Message_GetNoneReadCount]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [PPFOR_TZB_Message_GetNoneReadCount]
PPFOR_GO;
create procedure PPFOR_TZB_Message_GetNoneReadCount(@memberID int,@isAdmin bit,@msgType tinyint)
as
	begin
		select count(msgID) from [PPFOR_TZB_Message] where 
			msgReceiveIsAdmin=@isAdmin and msgReceiveMemberID=@memberID and msgHasRead=0 
				and msgType=@msgType
	end
PPFOR_GO;

--删除消息
if exists (select * from sysobjects where id = object_id(N'[PPFOR_TZB_Message_Delete]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [PPFOR_TZB_Message_Delete]
PPFOR_GO;
create procedure PPFOR_TZB_Message_Delete(@msgID int)
as
	begin
		delete from [PPFOR_TZB_Message] where msgID=@msgID
	end
PPFOR_GO;

--删除会员的消息
if exists (select * from sysobjects where id = object_id(N'[PPFOR_TZB_Message_DeleteByMember]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [PPFOR_TZB_Message_DeleteByMember]
PPFOR_GO;
create procedure PPFOR_TZB_Message_DeleteByMember(@memberID int,@isAdmin bit)
as
	begin
		delete from [PPFOR_TZB_Message] where msgSendIsAdmin=@isAdmin and msgSendMemberID=@memberID
		delete from [PPFOR_TZB_Message] where msgReceiveIsAdmin=@isAdmin and msgReceiveMemberID=@memberID
	end
PPFOR_GO;

--获取消息
if exists (select * from sysobjects where id = object_id(N'[PPFOR_TZB_Message_GetModel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [PPFOR_TZB_Message_GetModel]
PPFOR_GO;
create procedure PPFOR_TZB_Message_GetModel(@msgID int)
as
	begin
		select [PPFOR_TZB_Message].*,[PPFOR_TZB_MessageBody].[msgTitle],
			[PPFOR_TZB_MessageBody].[msgContent] from [PPFOR_TZB_Message],[PPFOR_TZB_MessageBody]
			where [PPFOR_TZB_Message].[msgID]=@msgID 
			and [PPFOR_TZB_MessageBody].[msgBodyID]=[PPFOR_TZB_Message].[msgBodyID]
	end
PPFOR_GO;

--获取会员发送出去的所有消息
if exists (select * from sysobjects where id = object_id(N'[PPFOR_TZB_Message_GetModelListCaseSend]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [PPFOR_TZB_Message_GetModelListCaseSend]
PPFOR_GO;
create procedure PPFOR_TZB_Message_GetModelListCaseSend(@memberID int,@isAdmin bit,@msgType tinyint)
as
	begin
		select * from [PPFOR_TZB_Message] where msgSendIsAdmin=@isAdmin 
			and msgSendMemberID=@memberID and msgType=@msgType
			order by msgHasRead asc, msgSendTime desc,msgID desc
	end
PPFOR_GO;

--获取会员接收到的所有消息
if exists (select * from sysobjects where id = object_id(N'[PPFOR_TZB_Message_GetModelListCaseReceive]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [PPFOR_TZB_Message_GetModelListCaseReceive]
PPFOR_GO;
create procedure PPFOR_TZB_Message_GetModelListCaseReceive(@memberID int,@isAdmin bit,@msgType tinyint)
as
	begin
		select * from [PPFOR_TZB_Message] where msgReceiveIsAdmin=@isAdmin 
			and msgReceiveMemberID=@memberID and msgType=@msgType
			order by msgHasRead asc, msgSendTime desc,msgID desc
	end
PPFOR_GO;

--分页获取会员发送出去的所有消息
if exists(select name from sysobjects where name='PPFOR_TZB_Message_GetModelListCaseSendByPage' and type='P')
	drop procedure PPFOR_TZB_Message_GetModelListCaseSendByPage
PPFOR_GO;
create procedure PPFOR_TZB_Message_GetModelListCaseSendByPage(@sendMemberID int, @isAdmin bit,
	@msgType tinyint,@pageSize int,@pageIndex int)
as
	begin
		declare @rc int
		declare @pc int
		declare @sqlStr nvarchar(1000)
		declare @sqlStr2 nvarchar(1000)
		
		if @pageSize<1
			set @pageSize=20
		if @sendMemberID>0
			select @rc=count([msgID]) from [PPFOR_TZB_Message] where msgSendMemberID=@sendMemberID
			 	and msgSendIsAdmin=@isAdmin and msgType=@msgType
		else
			select @rc=count([msgID]) from [PPFOR_TZB_Message] where msgSendIsAdmin=@isAdmin 
				and msgType=@msgType 			
		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

		if @sendMemberID>0
			set @sqlStr2=' * from [PPFOR_TZB_Message] where msgSendMemberID=' + str(@sendMemberID) + ' and msgSendIsAdmin=' + str(@isAdmin) + ' and msgType=' + str(@msgType) + ' '
		else
			set @sqlStr2=' * from [PPFOR_TZB_Message] where msgSendIsAdmin=' + str(@isAdmin) + ' and msgType=' + str(@msgType) + ' '
	
		if @pageIndex <= 1 or @pc <= 1
			set @sqlStr='select top ' + str(@pageSize) + @sqlStr2 + ' order by [msgHasRead] asc, [msgSendTime] desc,[msgID] desc'
		else if @pageIndex = @pc
			set @sqlStr ='select * from (select top ' + str( @rc - @pageSize * (@pageIndex-1) ) 
				+ @sqlStr2 + ' order by [msgHasRead] desc, [msgSendTime] asc,[msgID] asc) Temptable order by [msgHasRead] asc, [msgSendTime] desc,[msgID] desc'
		else  
			set @sqlStr ='select top ' + str(@pageSize) + ' * from (select top '
				+ str( @rc - @pageSize * (@pageIndex-1) ) + @sqlStr2 + ' order by 
				[msgHasRead] desc, [msgSendTime] asc,[msgID] asc) Temptable order by [msgHasRead] asc, [msgSendTime] desc,[msgID] desc'
		
		exec (@sqlStr)
		select @rc as recordCount,@pc as pageCount
	end
PPFOR_GO;

--分页获取会员接收到的所有消息
if exists(select name from sysobjects where name='PPFOR_TZB_Message_GetModelListCaseReceiveByPage' and type='P')
	drop procedure PPFOR_TZB_Message_GetModelListCaseReceiveByPage
PPFOR_GO;
create procedure PPFOR_TZB_Message_GetModelListCaseReceiveByPage(@receiveMemberID int,@isAdmin bit,
	@msgType tinyint,@pageSize int,@pageIndex int)
as
	begin
		declare @rc int
		declare @pc int
		declare @sqlStr nvarchar(1000)
		declare @sqlStr2 nvarchar(1000)
		
		if @pageSize<1
			set @pageSize=20
		if @receiveMemberID>0
			select @rc=count([msgID]) from [PPFOR_TZB_Message] where msgReceiveMemberID=@receiveMemberID
			 	and msgReceiveIsAdmin=@isAdmin and msgType=@msgType
		else
			select @rc=count([msgID]) from [PPFOR_TZB_Message] where msgReceiveIsAdmin=@isAdmin 
				and msgType=@msgType
		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
		
		if @receiveMemberID>0
			set @sqlStr2=' * from [PPFOR_TZB_Message] where msgReceiveMemberID=' + str(@receiveMemberID) + ' and msgReceiveIsAdmin=' + str(@isAdmin) + ' and msgType=' + str(@msgType) + ' '
		else
			set @sqlStr2=' * from [PPFOR_TZB_Message] where msgReceiveIsAdmin=' + str(@isAdmin) + ' and msgType=' + str(@msgType) + ' '
		if @pageIndex <= 1 or @pc <= 1
			set @sqlStr='select top ' + str(@pageSize) + @sqlStr2 + ' order by [msgHasRead] asc, [msgSendTime] desc,[msgID] desc'
		else if @pageIndex = @pc
			set @sqlStr ='select * from (select top ' + str( @rc - @pageSize * (@pageIndex-1) ) 
				+ @sqlStr2 + ' order by [msgHasRead] desc, [msgSendTime] asc,[msgID] asc) Temptable order by [msgHasRead] asc, [msgSendTime] desc,[msgID] desc'
		else  
			set @sqlStr ='select top ' + str(@pageSize) + ' * from (select top '
				+ str( @rc - @pageSize * (@pageIndex-1) ) + @sqlStr2 + ' order by 
				[msgHasRead] desc, [msgSendTime] asc,[msgID] asc) Temptable order by [msgHasRead] asc, [msgSendTime] desc,[msgID] desc'
		
		exec (@sqlStr)
		select @rc as recordCount,@pc as pageCount
	end
PPFOR_GO;

--根据是否已读分页返回消息
if exists(select name from sysobjects where name='PPFOR_TZB_Message_GetModelListCaseReadByPage' and type='P')
	drop procedure PPFOR_TZB_Message_GetModelListCaseReadByPage
PPFOR_GO;
create procedure PPFOR_TZB_Message_GetModelListCaseReadByPage(@msgType tinyint, @hasRead bit,
	@pageSize int,@pageIndex int)
as
	begin
		declare @rc int
		declare @pc int
		declare @sqlStr nvarchar(1000)
		declare @sqlStr2 nvarchar(1000)
		
		if @pageSize<1
			set @pageSize=20
		select @rc=count([msgID]) from [PPFOR_TZB_Message] where msgHasRead=@hasRead and msgType=@msgType 			
		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_Message] where msgHasRead=' + str(@hasRead) + ' and msgType=' + str(@msgType) + ' '
	
		if @pageIndex <= 1 or @pc <= 1
			set @sqlStr='select top ' + str(@pageSize) + @sqlStr2 + ' order by [msgSendTime] desc,[msgID] desc'
		else if @pageIndex = @pc
			set @sqlStr ='select * from (select top ' + str( @rc - @pageSize * (@pageIndex-1) ) 
				+ @sqlStr2 + ' order by [msgSendTime] asc,[msgID] asc) Temptable order by [msgSendTime] desc,[msgID] desc'
		else  
			set @sqlStr ='select top ' + str(@pageSize) + ' * from (select top '
				+ str( @rc - @pageSize * (@pageIndex-1) ) + @sqlStr2 + ' order by 
				[msgSendTime] asc,[msgID] asc) Temptable order by [msgSendTime] desc,[msgID] desc'
		
		exec (@sqlStr)
		select @rc as recordCount,@pc as pageCount
	end
PPFOR_GO;

--分页返回消息
if exists(select name from sysobjects where name='PPFOR_TZB_Message_GetModelListByPage' and type='P')
	drop procedure PPFOR_TZB_Message_GetModelListByPage
PPFOR_GO;
create procedure PPFOR_TZB_Message_GetModelListByPage(@msgType tinyint, @pageSize int,@pageIndex int)
as
	begin
		declare @rc int
		declare @pc int
		declare @sqlStr nvarchar(1000)
		declare @sqlStr2 nvarchar(1000)
		
		if @pageSize<1
			set @pageSize=20
		select @rc=count([msgID]) from [PPFOR_TZB_Message] where msgType=@msgType
		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_Message] where msgType=' + str(@msgType) + ' '
	
		if @pageIndex <= 1 or @pc <= 1
			set @sqlStr='select top ' + str(@pageSize) + @sqlStr2 + ' order by [msgHasRead] asc, [msgSendTime] desc,[msgID] desc'
		else if @pageIndex = @pc
			set @sqlStr ='select * from (select top ' + str( @rc - @pageSize * (@pageIndex-1) ) 
				+ @sqlStr2 + ' order by [msgHasRead] desc, [msgSendTime] asc,[msgID] asc) Temptable order by [msgHasRead] asc, [msgSendTime] desc,[msgID] desc'
		else  
			set @sqlStr ='select top ' + str(@pageSize) + ' * from (select top '
				+ str( @rc - @pageSize * (@pageIndex-1) ) + @sqlStr2 + ' order by 
				[msgHasRead] desc, [msgSendTime] asc,[msgID] asc) Temptable order by [msgHasRead] asc, [msgSendTime] desc,[msgID] desc'
		
		exec (@sqlStr)
		select @rc as recordCount,@pc as pageCount
	end
PPFOR_GO;