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

--创建消息内容表
if not exists (select * from sysobjects where id = object_id(N'[PPFOR_TZB_MessageBody]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
CREATE TABLE [PPFOR_TZB_MessageBody] (
	[msgBodyID] int identity(1,1) not null primary key,	
	[msgTitle] nvarchar (50) not null,			--消息标题
	[msgContent] nvarchar (500) not null,		--消息内容
	[msgNumber] int not null default(0),		--消息发送数量
	[msgBodySendTime] datetime not null			--消息发送时间
)
PPFOR_GO;

--添加消息内容
if exists (select * from sysobjects where id = object_id(N'[PPFOR_TZB_MessageBody_Add]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [PPFOR_TZB_MessageBody_Add]
PPFOR_GO;
create procedure PPFOR_TZB_MessageBody_Add(@msgTitle nvarchar(50),@msgContent nvarchar(500),
	@msgNumber int,	@msgSendTime datetime,@msgBodyID int out)
as
	begin
		insert into [PPFOR_TZB_MessageBody] (msgTitle,msgContent,msgNumber,msgBodySendTime)
			values (@msgTitle,@msgContent,@msgNumber,@msgSendTime)
		set @msgBodyID=@@identity
	end
PPFOR_GO;

--更新消息内容
if exists (select * from sysobjects where id = object_id(N'[PPFOR_TZB_MessageBody_Update]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [PPFOR_TZB_MessageBody_Update]
PPFOR_GO;
create procedure PPFOR_TZB_MessageBody_Update(@msgBodyID int,@msgTitle nvarchar(50),@msgContent nvarchar(500))
as
	begin
		update [PPFOR_TZB_MessageBody] set msgTitle=@msgTitle, msgContent=@msgContent 
			where msgBodyID=@msgBodyID 
	end
PPFOR_GO;

--更新发送数量
if exists (select * from sysobjects where id = object_id(N'[PPFOR_TZB_MessageBody_UpdateNumber]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [PPFOR_TZB_MessageBody_UpdateNumber]
PPFOR_GO;
create procedure PPFOR_TZB_MessageBody_UpdateNumber(@msgBodyID int,@msgNumber int)
as
	begin
		update [PPFOR_TZB_MessageBody] set msgNumber=@msgNumber where msgBodyID=@msgBodyID 
	end
PPFOR_GO;

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

--获取消息内容
if exists(select name from sysobjects where name='PPFOR_TZB_MessageBody_GetModel' and type='P')
	drop procedure PPFOR_TZB_MessageBody_GetModel
PPFOR_GO;
create procedure PPFOR_TZB_MessageBody_GetModel(@msgBodyID int)
as
	begin
		select * from [PPFOR_TZB_MessageBody] where msgBodyID=@msgBodyID		
	end
PPFOR_GO;

--获取消息标题
if exists(select name from sysobjects where name='PPFOR_TZB_MessageBody_GetTitle' and type='P')
	drop procedure PPFOR_TZB_MessageBody_GetTitle
PPFOR_GO;
create procedure PPFOR_TZB_MessageBody_GetTitle(@msgBodyID int)
as
	begin
		select [msgTitle] from [PPFOR_TZB_MessageBody] where msgBodyID=@msgBodyID		
	end
PPFOR_GO;

--分页返回所有消息内容
if exists(select name from sysobjects where name='PPFOR_TZB_MessageBody_GetModelListByPage' and type='P')
	drop procedure PPFOR_TZB_MessageBody_GetModelListByPage
PPFOR_GO;
create procedure PPFOR_TZB_MessageBody_GetModelListByPage(@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([msgBodyID]) from [PPFOR_TZB_MessageBody]		
		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=' [PPFOR_TZB_MessageBody].*,msgUseNumber=(select count([PPFOR_TZB_Message].[msgID]) from [PPFOR_TZB_Message] where [PPFOR_TZB_Message].[msgBodyID]=[PPFOR_TZB_MessageBody].[msgBodyID]) from [PPFOR_TZB_MessageBody] '
	
		if @pageIndex <= 1 or @pc <= 1
			set @sqlStr='select top ' + str(@pageSize) + @sqlStr2 + ' order by [msgUseNumber] desc, [msgBodySendTime] desc'
		else if @pageIndex = @pc
			set @sqlStr ='select * from (select top ' + str( @rc - @pageSize * (@pageIndex-1) ) 
				+ @sqlStr2 + ' order by [msgUseNumber] asc,[msgBodySendTime] asc) Temptable order by [msgUseNumber] desc,[msgBodySendTime] desc'
		else  
			set @sqlStr ='select top ' + str(@pageSize) + ' * from (select top '
				+ str( @rc - @pageSize * (@pageIndex-1) ) + @sqlStr2 + ' order by [msgUseNumber] asc,
				[msgBodySendTime] asc) Temptable order by [msgUseNumber] desc,[msgBodySendTime] desc'
		
		exec (@sqlStr)
		select @rc as recordCount,@pc as pageCount
	end
PPFOR_GO;
