/*************************************************************************/
/* */
/*procedure : up_GetPostedTopicList*/
/* */
/*Description:精华区贴子列表 */
/* */
/*Parameters: @a_intForumID : 版面id */
/*@a_intPageNo: 页号 */
/*@a_intPageSize: 每页显示数，以根贴为准 */
/* */
/*Use table:bbs , forum*/
/* */
/*Author: bigeagle@163.net */
/* */
/*Date: 2000/2/14*/
/* */
/*History: */
/* */
/*************************************************************************/
if exists(select * from sysobjects where id = object_id('up_GetPostedTopicList'))
 drop proc up_GetPostedTopicList
go

create proc up_GetPostedTopicList
 @a_intForumID int ,
 @a_intPageNo int,
 @a_intPageSize int
as
 /*定义局部变量*/
 declare @intBeginID int
 declare @intEndID int
 declare @intRootRecordCount int
 declare @intPageCount int
 declare @intRowCountint
 /*关闭计数*/
 set nocount on
 
 /*检测是否有这个版面*/
 if not exists(select * from forum where id = @a_intForumID)
return (-1)

 /*求总共根贴数*/
 select @intRootRecordCount = count(*) from bbs where posted=1 and forumid=@a_intForumID
 if (@intRootRecordCount = 0)--如果没有贴子，则返回零
 return 0
 
 /*判断页数是否正确*/
 if (@a_intPageNo - 1) * @a_intPageSize > @intRootRecordCount
return (-1)

 /*求开始rootID*/
 set @intRowCount = (@a_intPageNo - 1) * @a_intPageSize + 1
 /*限制条数*/
 set rowcount @intRowCount
 select @intBeginID = rootid from bbs where posted=1 and forumid=@a_intForumID
order by id desc

 /*结束rootID*/
 set @intRowCount = @a_intPageNo * @a_intPageSize
 /*限制条数*/
 set rowcount @intRowCount
 select @intEndID = rootid from bbs where posted=1 and forumid=@a_intForumID
order by id desc

 /*恢复系统变量*/
 set rowcount 0
 set nocount off 

 select a.id , a.layer , a.forumid , a.subject , a.faceid , a.hits , a.time , a.UserID , a.fatherid , a.rootid ,
 'Bytes' = datalength(a.content) , b.UserName , b.Email , b.HomePage , b.Signature , b.Point
 from bbs as a join BBSUser as b on a.UserID = b.ID
 where posted=1 and Forumid=@a_intForumID and a.rootid between @intEndID and @intBeginID
 order by a.rootid desc , a.ordernum desc
 return(@@rowcount)
 --select @@rowcount
go
select id , rootid , fatherid , forumid , posted from bbs
up_getpostedtopiclist 3 ,1 , 20
/*************************************************************************/
/* */
/*procedure : up_GetTopic*/
/* */
/*Description:取贴子 */
/* */
/*Parameters: @a_intTopicID : 贴子id */
/* */
/*Use table:bbs*/
/* */
/*Author: bigeagle@163.net */
/* */
/*Date: 2000/2/16*/
/* */
/*History: */
/* */
/*************************************************************************/
if exists(select * from sysobjects where id = object_id('up_GetTopic'))
 drop proc up_GetTopic
go

create proc up_GetTopic @a_intTopicID int
as
 /*如果没有这贴子*/
 if not exists (select * from bbs where id = @a_intTopicID)
return (-1)

 /*更新该贴的点击数*/
 update bbs set hits = hits + 1 where id = @a_intTopicID

 select a.* , 'Bytes' = datalength(a.content) ,
b.UserName , b.Email , b.Homepage , b.point , b.Signature
 from bbs as a join BBSUser as b on a.UserID = b.id
 where a.id = @a_intTopicID
go

up_getTopic 11

/*************************************************************************/
/* */
/*procedure : up_DeleTopic*/
/* */
/*Description:删除贴子及子贴，更新发贴人信息 */
/* */
/*Parameters: @a_intTopicID : 贴子id */
/* */
/*Use table:bbs*/
/* */
/*Author: bigeagle@163.net */
/* */
/*Date: 2000/2/24*/
/* */
/*History: */
/* */
/*************************************************************************/

if exists(select * from sysobjects where id = object_id('up_DeleTopic'))
 drop proc up_DeleTopic
go

create proc up_DeleTopic @a_intTopicID int
 as

/*定义局部变量*/
declare @intRootID int
declare @intLayerint
declare @floatOrderNum float(53)
declare @floatNextOrderNum float(53)
declare @intCounts int
declare @intForumID int
 
/*取消计数*/
set nocount on

/*首先查找这个贴子的rootid和ordernum，没有则返回*/
 select @intRootID = RootID ,
@floatOrderNum = OrderNum ,
@intLayer = layer ,
@intForumID = forumid
from bbs where id = @a_intTopicID
 if @@rowcount = 0
return (-1)

 /*取下一个同层贴子的ordernum*/
 select @FloatNextOrderNum = isnull(max(ordernum) , 0)
 from bbs
 where RootID=@intRootID
 and layer=@intLayer and ordernum < @floatOrderNum

 /*多表操作，用事务*/
 begin transaction

 /*首先删贴*/

 deletefrom bbs
 whererootid=@intRootID and ordernum > @floatNextOrderNum
 and ordernum <= @floatOrderNum
 select @intCounts = @@rowcount
 if (@@error != 0)
goto Error


 
 /*论坛贴子数减少*/
 update forum set topiccount = topiccount - @intCounts where id=@intForumID
 if (@@error != 0)
goto Error

 /*完成事务，返回*/
 commit transaction
 set nocount off
 return(0)

 Error:
 rollback transaction
 set nocount off
 return (-1)
go

select forumid from bbs
update bbs set forumid=4

/*************************************************************************/
/* */
/*procedure : up_GetUserInfo */
/* */
/*Description:取得发贴人信息 */
/* */
/*Parameters: @a_strUserName : 用户笔名*/
/* */
/*Use table:bbsuser*/
/* */
/*Author: bigeagle@163.net */
/* */
/*Date: 2000/4/16*/
/* */
/*History: */
/* */
/*************************************************************************/
if exists(select * from sysobjects where id = object_id('up_GetUserInfo'))
 drop proc up_GetUserInfo
go

create proc up_GetUserInfo @a_strUserName varchar(20)
as
declare @m_intOrder int--排名
declare @m_intPoint int--积分

set nocount on

/*如果没有找到该用户，则返回-1*/
select @m_intPoint = point from bbsuser where username=@a_strUserName
if(@@rowcount = 0)
return(-1)

/*求排名*/
select @m_intOrder = count(*) + 1 from bbsuser where point > @m_intPoint

select * , 'order' = @m_intOrder from bbsuser where username=@a_strUserName

set nocount off
go
up_getuserinfo '廖家远'

/*************************************************************************/
/* */
/*procedure : up_PostedTopic */
/* */
/*Description:将贴子转入精华区 */
/* */
/*Parameters: @a_intTopicID 贴子id */
/* */
/*Use table:bbs, postedtopic */
/* */
/*Author: bigeagle@163.net */
/* */
/*Date: 2000/4/17*/
/* */
/*History: */
/* */
/*************************************************************************/

if exists(select * from sysobjects where id= object_id('up_postedtopic'))
 drop proc up_postedtopic
go

create proc up_PostedTopic @a_intTopicID int
as
/*定义局部变量*/
declare @m_intUserID int--发贴人ID

/*查找是否有这个贴子*/
select @m_intUserID = userid from bbs where id = @a_intTopicID
if(@@rowcount != 1)
 return -1

/*因为对两个表操作所以用事务*/
begin transaction
update bbs set posted = 1 where id = @a_intTopicID
if(@@error <> 0)
 goto Error
update bbsuser set point = point + 3 where id = @m_intUserID
if(@@error <> 0)
 goto Error
Commit transaction
return (0)
Error:
rollback transaction
go