setup2.2 - 2005.sql
上传用户:wenllgg125
上传日期:2020-04-09
资源大小:7277k
文件大小:192k
- IF OBJECT_ID('[dnt_checkemailandsecques]','P') IS NOT NULL
- DROP PROC [dnt_checkemailandsecques]
- GO
- CREATE PROCEDURE [dnt_checkemailandsecques]
- @username nchar(20),
- @email char(32),
- @secques char(8)
- AS
- SELECT TOP 1 [uid] FROM [dnt_users] WHERE [username]=@username AND [email]=@email AND [secques]=@secques
- GO
- IF OBJECT_ID('[dnt_checkpasswordandsecques]','P') IS NOT NULL
- DROP PROC [dnt_checkpasswordandsecques]
- GO
- CREATE PROCEDURE [dnt_checkpasswordandsecques]
- @username nchar(20),
- @password char(32),
- @secques char(8)
- AS
- SELECT TOP 1 [uid] FROM [dnt_users] WHERE [username]=@username AND [password]=@password AND [secques]=@secques
- GO
- IF OBJECT_ID('[dnt_checkpasswordbyuid]','P') IS NOT NULL
- DROP PROC [dnt_checkpasswordbyuid]
- GO
- CREATE PROCEDURE [dnt_checkpasswordbyuid]
- @uid int,
- @password char(32)
- AS
- SELECT TOP 1 [uid], [groupid], [adminid] FROM [dnt_users] WHERE [uid]=@uid AND [password]=@password
- GO
- IF OBJECT_ID('[dnt_checkpasswordbyusername]','P') IS NOT NULL
- DROP PROC [dnt_checkpasswordbyusername]
- GO
- CREATE PROCEDURE [dnt_checkpasswordbyusername]
- @username nchar(20),
- @password char(32)
- AS
- SELECT TOP 1 [uid], [groupid], [adminid] FROM [dnt_users] WHERE [username]=@username AND [password]=@password
- GO
- IF OBJECT_ID('[dnt_createadmingroup]','P') IS NOT NULL
- DROP PROC [dnt_createadmingroup]
- GO
- CREATE PROCEDURE [dnt_createadmingroup]
- @admingid smallint,
- @alloweditpost tinyint,
- @alloweditpoll tinyint,
- @allowstickthread tinyint,
- @allowmodpost tinyint,
- @allowdelpost tinyint,
- @allowmassprune tinyint,
- @allowrefund tinyint,
- @allowcensorword tinyint,
- @allowviewip tinyint,
- @allowbanip tinyint,
- @allowedituser tinyint,
- @allowmoduser tinyint,
- @allowbanuser tinyint,
- @allowpostannounce tinyint,
- @allowviewlog tinyint,
- @disablepostctrl tinyint,
- @allowviewrealname tinyint
- AS
- INSERT INTO dnt_admingroups
- ([admingid],[alloweditpost],[alloweditpoll],[allowstickthread],[allowmodpost],[allowdelpost],[allowmassprune],[allowrefund],[allowcensorword],[allowviewip],[allowbanip],[allowedituser],[allowmoduser],[allowbanuser],[allowpostannounce],[allowviewlog],[disablepostctrl],[allowviewrealname])
- VALUES
- (@admingid,@alloweditpost,@alloweditpoll,@allowstickthread,@allowmodpost,@allowdelpost,@allowmassprune,@allowrefund,@allowcensorword,@allowviewip,@allowbanip,@allowedituser,@allowmoduser,@allowbanuser,@allowpostannounce,@allowviewlog,@disablepostctrl,@allowviewrealname)
- GO
- IF OBJECT_ID('[dnt_createattachment]','P') IS NOT NULL
- DROP PROC [dnt_createattachment]
- GO
- CREATE PROCEDURE [dnt_createattachment]
- @uid int,
- @tid int,
- @pid int,
- @postdatetime datetime,
- @readperm int,
- @filename nchar(200),
- @description nchar(200),
- @filetype nchar(100),
- @filesize int,
- @attachment nchar(200),
- @downloads int,
- @extname nvarchar(50),
- @attachprice int,
- @width int,
- @height int
- AS
- DECLARE @aid int
- INSERT INTO [dnt_attachments]([uid],[tid], [pid], [postdatetime], [readperm], [filename], [description], [filetype], [filesize], [attachment], [downloads],[attachprice],[width],[height]) VALUES(@uid, @tid, @pid, @postdatetime, @readperm, @filename, @description, @filetype, @filesize, @attachment, @downloads, @attachprice,@width,@height)
- SELECT SCOPE_IDENTITY() AS 'aid'
- set @aid=(SELECT SCOPE_IDENTITY() AS 'aid')
- UPDATE [dnt_posts1] SET [attachment]=1 WHERE [pid]=@pid
- INSERT INTO [dnt_myattachments]([aid],[uid],[attachment],[description],[postdatetime],[downloads],[filename],[pid],[tid],[extname]) VALUES(@aid,@uid,@attachment,@description,@postdatetime,@downloads,@filename,@pid,@tid,@extname)
- GO
- IF OBJECT_ID('[dnt_createdebatepostexpand]','P') IS NOT NULL
- DROP PROC [dnt_createdebatepostexpand]
- GO
- CREATE PROCEDURE [dnt_createdebatepostexpand]
- @tid int,
- @pid int,
- @opinion int,
- @diggs int
- AS
- BEGIN
- INSERT INTO [dnt_postdebatefields] VALUES(@tid, @pid, @opinion, @diggs)
- IF @opinion = 1
- UPDATE [dnt_debates] SET [positivediggs] = [positivediggs] + 1 WHERE [tid] = @tid
- ELSE IF @opinion = 2
- UPDATE [dnt_debates] SET [negativediggs] = [negativediggs] + 1 WHERE [tid] = @tid
- END
- GO
- IF OBJECT_ID('[dnt_createfavorite]','P') IS NOT NULL
- DROP PROC [dnt_createfavorite]
- GO
- CREATE PROCEDURE [dnt_createfavorite]
- @uid int,
- @tid int,
- @type tinyint
- AS
-
- INSERT INTO [dnt_favorites] ([uid],[tid],[typeid]) VALUES(@uid,@tid,@type)
- RETURN
- GO
- IF OBJECT_ID('[dnt_createpm]','P') IS NOT NULL
- DROP PROC [dnt_createpm]
- GO
- CREATE PROCEDURE [dnt_createpm]
- @pmid int,
- @msgfrom nvarchar(20),
- @msgto nvarchar(20),
- @msgfromid int,
- @msgtoid int,
- @folder smallint=0,
- @new int=0,
- @subject nvarchar(60),
- @postdatetime datetime,
- @message ntext,
- @savetosentbox smallint=1
- AS
- IF @folder<>0
- BEGIN
- SET @msgfrom=@msgto
- END
- ELSE
- BEGIN
- UPDATE [dnt_users] SET [newpmcount]=ABS(ISNULL([newpmcount],0)*1)+1,[newpm] = 1 WHERE [uid]=@msgtoid
- END
-
- INSERT INTO [dnt_pms]
- ([msgfrom],[msgfromid],[msgto],[msgtoid],[folder],[new],[subject],[postdatetime],[message])
- VALUES
- (@msgfrom,@msgfromid,@msgto,@msgtoid,@folder,@new,@subject,@postdatetime,@message)
-
- SELECT SCOPE_IDENTITY() AS 'pmid'
- IF @savetosentbox=1 AND @folder=0
- BEGIN
- INSERT INTO [dnt_pms]
- ([msgfrom],[msgfromid],[msgto],[msgtoid],[folder],[new],[subject],[postdatetime],[message])
- VALUES
- (@msgfrom,@msgfromid,@msgto,@msgtoid,1,@new,@subject,@postdatetime,@message)
- END
- GO
- IF OBJECT_ID('dnt_createpost1','P') IS NOT NULL
- DROP PROC [dnt_createpost1]
- GO
- CREATE PROCEDURE [dnt_createpost1]
- @fid int,
- @tid int,
- @parentid int,
- @layer int,
- @poster varchar(20),
- @posterid int,
- @title nvarchar(60),
- @topictitle nvarchar(60),
- @postdatetime char(20),
- @message ntext,
- @ip varchar(15),
- @lastedit varchar(50),
- @invisible int,
- @usesig int,
- @htmlon int,
- @smileyoff int,
- @bbcodeoff int,
- @parseurloff int,
- @attachment int,
- @rate int,
- @ratetimes int
- AS
- DEClARE @postid int
- DELETE FROM [dnt_postid] WHERE DATEDIFF(n, postdatetime, GETDATE()) >5
- INSERT INTO [dnt_postid] ([postdatetime]) VALUES(GETDATE())
- SELECT @postid=SCOPE_IDENTITY()
- INSERT INTO [dnt_posts1]([pid], [fid], [tid], [parentid], [layer], [poster], [posterid], [title], [postdatetime], [message], [ip], [lastedit], [invisible], [usesig], [htmlon], [smileyoff], [bbcodeoff], [parseurloff], [attachment], [rate], [ratetimes]) VALUES(@postid, @fid, @tid, @parentid, @layer, @poster, @posterid, @title, @postdatetime, @message, @ip, @lastedit, @invisible, @usesig, @htmlon, @smileyoff, @bbcodeoff, @parseurloff, @attachment, @rate, @ratetimes)
- IF @parentid=0
- BEGIN
-
- UPDATE [dnt_posts1] SET [parentid]=@postid WHERE [pid]=@postid
- END
- IF @@ERROR=0
- BEGIN
- IF @invisible = 0
- BEGIN
-
- UPDATE [dnt_statistics] SET [totalpost]=[totalpost] + 1
-
-
-
- DECLARE @fidlist AS VARCHAR(1000)
- DECLARE @strsql AS VARCHAR(4000)
-
- SET @fidlist = '';
-
- SELECT @fidlist = ISNULL([parentidlist],'') FROM [dnt_forums] WHERE [fid] = @fid
- IF RTRIM(@fidlist)<>''
- BEGIN
- SET @fidlist = RTRIM(@fidlist) + ',' + CAST(@fid AS VARCHAR(10))
- END
- ELSE
- BEGIN
- SET @fidlist = CAST(@fid AS VARCHAR(10))
- END
- UPDATE [dnt_forums] SET
- [posts]=[posts] + 1,
- [todayposts]=CASE
- WHEN DATEDIFF(day, [lastpost], GETDATE())=0 THEN [todayposts] + 1
- ELSE 1
- END,
- [lasttid]=@tid,
- [lasttitle]=@topictitle,
- [lastpost]=@postdatetime,
- [lastposter]=@poster,
- [lastposterid]=@posterid
- WHERE fid IN (SELECT [item] FROM
- [dnt_split](@fidlist, ','))
-
- UPDATE [dnt_users] SET
- [lastpost] = @postdatetime,
- [lastpostid] = @postid,
- [lastposttitle] = @title,
- [posts] = [posts] + 1,
- [lastactivity] = GETDATE()
- WHERE [uid] = @posterid
- IF @layer<=0
- BEGIN
- UPDATE [dnt_topics] SET [replies]=0,[lastposter]=@poster,[lastpost]=@postdatetime,[lastposterid]=@posterid WHERE [tid]=@tid
- END
- ELSE
- BEGIN
- UPDATE [dnt_topics] SET [replies]=[replies] + 1,[lastposter]=@poster,[lastpost]=@postdatetime,[lastposterid]=@posterid WHERE [tid]=@tid
- END
- END
- UPDATE [dnt_topics] SET [lastpostid]=@postid WHERE [tid]=@tid
- IF @posterid <> -1
- BEGIN
- INSERT [dnt_myposts]([uid], [tid], [pid], [dateline]) VALUES(@posterid, @tid, @postid, @postdatetime)
- END
- END
- SELECT @postid AS postid
- GO
- IF OBJECT_ID('[dnt_createsearchcache]','P') IS NOT NULL
- DROP PROC [dnt_createsearchcache]
- GO
- CREATE PROCEDURE [dnt_createsearchcache]
- @keywords varchar(255),
- @searchstring varchar(255),
- @ip varchar(15),
- @uid int,
- @groupid int,
- @postdatetime varchar(19),
- @expiration varchar(19),
- @topics int,
- @tids text
- AS
- INSERT INTO dnt_searchcaches
- ([keywords],[searchstring],[ip],[uid],[groupid],[postdatetime],[expiration],[topics],[tids])
- VALUES
- (@keywords,@searchstring,@ip,@uid,@groupid,@postdatetime,@expiration,@topics,@tids)
- SELECT SCOPE_IDENTITY() AS 'searchid'
- GO
- IF OBJECT_ID('[dnt_createtags]','P') IS NOT NULL
- DROP PROC [dnt_createtags]
- GO
- CREATE PROCEDURE [dnt_createtags]
- @tags nvarchar(55),
- @userid int,
- @postdatetime datetime
- AS
- BEGIN
- INSERT INTO [dnt_tags]([tagname], [userid], [postdatetime], [orderid], [color], [count], [fcount], [pcount], [scount], [vcount])
- SELECT [item], @userid, @postdatetime, 0, '', 0, 0, 0, 0, 0 FROM [dnt_split](@tags, ' ') AS [newtags]
- WHERE NOT EXISTS (SELECT [tagname] FROM [dnt_tags] WHERE [newtags].[item] = [tagname])
- END
- GO
- IF OBJECT_ID('[dnt_createtopic]','P') IS NOT NULL
- DROP PROC [dnt_createtopic]
- GO
- CREATE PROCEDURE [dnt_createtopic]
- @fid smallint,
- @iconid smallint,
- @title nchar(80),
- @typeid smallint,
- @readperm int,
- @price smallint,
- @poster char(20),
- @posterid int,
- @postdatetime smalldatetime,
- @lastpost smalldatetime,
- @lastpostid int,
- @lastposter char(20),
- @views int,
- @replies int,
- @displayorder int,
- @highlight varchar(500),
- @digest int,
- @rate int,
- @hide int,
- @attachment int,
- @moderated int,
- @closed int,
- @magic int,
- @special tinyint,
- @attention int
- AS
- DECLARE @topicid int
- DELETE FROM [dnt_topics] WHERE [tid]>(SELECT ISNULL(max(tid),0)-100 FROM [dnt_topics]) AND [lastpostid]=0
- INSERT INTO [dnt_topics]([fid], [iconid], [title], [typeid], [readperm], [price], [poster], [posterid], [postdatetime], [lastpost], [lastpostid], [lastposter], [views], [replies], [displayorder], [highlight], [digest], [rate], [hide], [attachment], [moderated], [closed], [magic], [special],[attention]) VALUES(@fid, @iconid, @title, @typeid, @readperm, @price, @poster, @posterid, @postdatetime, @lastpost, @lastpostid, @lastposter, @views, @replies, @displayorder, @highlight, @digest, @rate, @hide, @attachment, @moderated, @closed, @magic, @special,@attention)
- SET @topicid=SCOPE_IDENTITY()
- IF @@ERROR=0 AND @displayorder=0
- BEGIN
- UPDATE [dnt_statistics] SET [totaltopic]=[totaltopic] + 1
- UPDATE [dnt_forums] SET [topics] = [topics] + 1,[curtopics] = [curtopics] + 1 WHERE [fid] = @fid
-
- IF @posterid <> -1
- BEGIN
- INSERT INTO [dnt_mytopics]([tid],[uid],[dateline]) VALUES(@topicid, @posterid, @postdatetime)
- END
-
- END
-
- SELECT @topicid as topicid
- GO
- IF OBJECT_ID('[dnt_createuser]','P') IS NOT NULL
- DROP PROC [dnt_createuser]
- GO
- CREATE PROCEDURE [dnt_createuser]
- @username nchar(20),
- @nickname nchar(20),
- @password char(32),
- @secques char(8),
- @gender int,
- @adminid int,
- @groupid smallint,
- @groupexpiry int,
- @extgroupids char(60),
- @regip char(15),
- @joindate char(19),
- @lastip char(15),
- @lastvisit char(19),
- @lastactivity char(19),
- @lastpost char(19),
- @lastpostid int,
- @lastposttitle nchar(60),
- @posts int,
- @digestposts smallint,
- @oltime int,
- @pageviews int,
- @credits int,
- @extcredits1 float,
- @extcredits2 float,
- @extcredits3 float,
- @extcredits4 float,
- @extcredits5 float,
- @extcredits6 float,
- @extcredits7 float,
- @extcredits8 float,
- @avatarshowid int,
- @email char(50),
- @bday char(19),
- @sigstatus int,
- @salt nchar(6),
- @tpp int,
- @ppp int,
- @templateid smallint,
- @pmsound int,
- @showemail int,
- @newsletter int,
- @invisible int,
- @newpm int,
- @accessmasks int,
- @website varchar(80),
- @icq varchar(12),
- @qq varchar(12),
- @yahoo varchar(40),
- @msn varchar(40),
- @skype varchar(40),
- @location nvarchar(30),
- @customstatus varchar(30),
- @avatar varchar(255),
- @avatarwidth int,
- @avatarheight int,
- @medals varchar(300),
- @bio nvarchar(500),
- @signature nvarchar(500),
- @sightml nvarchar(1000),
- @authstr varchar(20),
- @realname nvarchar(10),
- @idcard varchar(20),
- @mobile varchar(20),
- @phone varchar(20)
- AS
- DECLARE @uid int
- INSERT INTO [dnt_users]([username],[nickname], [password], [secques], [gender], [adminid], [groupid], [groupexpiry], [extgroupids], [regip], [joindate], [lastip], [lastvisit], [lastactivity], [lastpost], [lastpostid], [lastposttitle], [posts], [digestposts], [oltime], [pageviews], [credits], [extcredits1], [extcredits2], [extcredits3], [extcredits4], [extcredits5], [extcredits6], [extcredits7], [extcredits8], [avatarshowid], [email], [bday], [sigstatus], [salt], [tpp], [ppp], [templateid], [pmsound], [showemail], [newsletter], [invisible], [newpm], [accessmasks]) VALUES(@username,@nickname, @password, @secques, @gender, @adminid, @groupid, @groupexpiry, @extgroupids, @regip, @joindate, @lastip, @lastvisit, @lastactivity, @lastpost, @lastpostid, @lastposttitle, @posts, @digestposts, @oltime, @pageviews, @credits, @extcredits1, @extcredits2, @extcredits3, @extcredits4, @extcredits5, @extcredits6, @extcredits7, @extcredits8, @avatarshowid, @email, @bday, @sigstatus, @salt, @tpp, @ppp, @templateid, @pmsound, @showemail, @newsletter, @invisible, @newpm, @accessmasks)
- SELECT SCOPE_IDENTITY() AS 'userid'
- SET @uid = SCOPE_IDENTITY()
- IF @@ERROR=0
- BEGIN
- UPDATE [dnt_statistics] SET [totalusers]=[totalusers] + 1,[lastusername]=@username,[lastuserid]=@uid
- END
- INSERT INTO dnt_userfields
- ([uid],[website],[icq],[qq],[yahoo],[msn],[skype],[location],[customstatus],[avatar],[avatarwidth],[avatarheight],[medals],[bio],[signature],[sightml],[authstr],[realname],[idcard],[mobile],[phone])
- VALUES
- (@uid,@website,@icq,@qq,@yahoo,@msn,@skype,@location,@customstatus,@avatar,@avatarwidth,@avatarheight,@medals,@bio,@signature,@sightml,@authstr,@realname,@idcard,@mobile,@phone)
- GO
- IF OBJECT_ID('[dnt_deletepost1bypid]','P') IS NOT NULL
- DROP PROC [dnt_deletepost1bypid]
- GO
- CREATE PROCEDURE [dnt_deletepost1bypid]
- @pid int,
- @chanageposts AS BIT
- AS
- DECLARE @fid int
- DECLARE @tid int
- DECLARE @posterid int
- DECLARE @lastforumposterid int
- DECLARE @layer int
- DECLARE @postdatetime smalldatetime
- DECLARE @poster varchar(50)
- DECLARE @postcount int
- DECLARE @title nchar(60)
- DECLARE @lasttid int
- DECLARE @postid int
- DECLARE @todaycount int
-
-
- SELECT @fid = [fid],@tid = [tid],@posterid = [posterid],@layer = [layer], @postdatetime = [postdatetime] FROM [dnt_posts1] WHERE pid = @pid
- DECLARE @fidlist AS VARCHAR(1000)
-
- SET @fidlist = '';
-
- SELECT @fidlist = ISNULL([parentidlist],'') FROM [dnt_forums] WHERE [fid] = @fid
- IF RTRIM(@fidlist)<>''
- BEGIN
- SET @fidlist = RTRIM(@fidlist) + ',' + CAST(@fid AS VARCHAR(10))
- END
- ELSE
- BEGIN
- SET @fidlist = CAST(@fid AS VARCHAR(10))
- END
- IF @layer<>0
- BEGIN
-
- IF @chanageposts = 1
- BEGIN
- UPDATE [dnt_statistics] SET [totalpost]=[totalpost] - 1
- UPDATE [dnt_forums] SET
- [posts]=[posts] - 1,
- [todayposts]=CASE
- WHEN DATEPART(yyyy, @postdatetime)=DATEPART(yyyy,GETDATE()) AND DATEPART(mm, @postdatetime)=DATEPART(mm,GETDATE()) AND DATEPART(dd, @postdatetime)=DATEPART(dd,GETDATE()) THEN [todayposts] - 1
- ELSE [todayposts]
- END
- WHERE (CHARINDEX(',' + RTRIM([fid]) + ',', ',' +
- (SELECT @fidlist AS [fid]) + ',') > 0)
-
- UPDATE [dnt_users] SET [posts] = [posts] - 1 WHERE [uid] = @posterid
- UPDATE [dnt_topics] SET [replies]=[replies] - 1 WHERE [tid]=@tid
- END
-
- DELETE FROM [dnt_posts1] WHERE [pid]=@pid
-
- END
- ELSE
- BEGIN
-
- SELECT @postcount = COUNT([pid]) FROM [dnt_posts1] WHERE [tid] = @tid
- SELECT @todaycount = COUNT([pid]) FROM [dnt_posts1] WHERE [tid] = @tid AND DATEDIFF(d, [postdatetime], GETDATE()) = 0
-
- IF @chanageposts = 1
- BEGIN
- UPDATE [dnt_statistics] SET [totaltopic]=[totaltopic] - 1, [totalpost]=[totalpost] - @postcount
-
- UPDATE [dnt_forums] SET [posts]=[posts] - @postcount, [topics]=[topics] - 1,[todayposts]=[todayposts] - @todaycount WHERE (CHARINDEX(',' + RTRIM([fid]) + ',', ',' +(SELECT @fidlist AS [fid]) + ',') > 0)
-
- UPDATE [dnt_users] SET [posts] = [posts] - @postcount WHERE [uid] = @posterid
-
- END
- DELETE FROM [dnt_posts1] WHERE [tid] = @tid
-
- DELETE FROM [dnt_topics] WHERE [tid] = @tid
-
- END
-
- IF @layer<>0
- BEGIN
- SELECT TOP 1 @pid = [pid], @posterid = [posterid], @postdatetime = [postdatetime], @title = [title], @poster = [poster] FROM [dnt_posts1] WHERE [tid]=@tid ORDER BY [pid] DESC
- UPDATE [dnt_topics] SET [lastposter]=@poster,[lastpost]=@postdatetime,[lastpostid]=@pid,[lastposterid]=@posterid WHERE [tid]=@tid
- END
- SELECT @lasttid = [lasttid] FROM [dnt_forums] WHERE [fid] = @fid
-
- IF @lasttid = @tid
- BEGIN
-
-
- SELECT TOP 1 @pid = [pid], @tid = [tid],@lastforumposterid = [posterid], @title = [title], @postdatetime = [postdatetime], @poster = [poster] FROM [dnt_posts1] WHERE [fid] = @fid ORDER BY [pid] DESC
-
-
-
- UPDATE [dnt_forums] SET
-
- [lastpost]=@postdatetime,
- [lastposter]=ISNULL(@poster,''),
- [lastposterid]=ISNULL(@lastforumposterid,'0')
- WHERE (CHARINDEX(',' + RTRIM([fid]) + ',', ',' +
- (SELECT @fidlist AS [fid]) + ',') > 0)
-
- SELECT TOP 1 @pid = [pid], @tid = [tid],@posterid = [posterid], @postdatetime = [postdatetime], @title = [title], @poster = [poster] FROM [dnt_posts1] WHERE [posterid]=@posterid ORDER BY [pid] DESC
-
- UPDATE [dnt_users] SET
-
- [lastpost] = @postdatetime,
- [lastpostid] = @pid,
- [lastposttitle] = ISNULL(@title,'')
-
- WHERE [uid] = @posterid
-
- END
- GO
- IF OBJECT_ID('[dnt_deletetopictags]','P') IS NOT NULL
- DROP PROC [dnt_deletetopictags]
- GO
- CREATE PROCEDURE [dnt_deletetopictags]
- @tid int
- AS
- BEGIN
- UPDATE [dnt_tags] SET [count]=[count]-1,[fcount]=[fcount]-1
- WHERE EXISTS (SELECT [tagid] FROM [dnt_topictags] WHERE [tid] = @tid AND [tagid] = [dnt_tags].[tagid])
- DELETE FROM [dnt_topictags] WHERE [tid] = @tid
- END
- GO
- IF OBJECT_ID('[dnt_getalltopiccount]','P') IS NOT NULL
- DROP PROC [dnt_getalltopiccount]
- GO
- CREATE PROCEDURE [dnt_getalltopiccount]
- @fid int
- AS
- SELECT COUNT(tid) FROM [dnt_topics] WHERE ([fid]=@fid OR [fid] IN ( SELECT fid FROM [dnt_forums] WHERE CHARINDEX(',' + RTRIM(@fid) + ',', ',' + parentidlist + ',') > 0)) AND [displayorder]>=0
- GO
- IF EXISTS (SELECT * FROM SYSOBJECTS WHERE ID = OBJECT_ID(N'[dnt_getdebatepostlist1]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
- DROP PROCEDURE [dnt_getdebatepostlist1]
- GO
- CREATE PROCEDURE [dnt_getdebatepostlist1]
- @tid int,
- @opinion int,
- @pagesize int,
- @pageindex int
- AS
- DECLARE @startRow int,
- @endRow int
- SET @startRow = (@pageindex - 1) * @pagesize + 1
- SET @endRow = @startRow + @pagesize - 1
- SELECT
- [DEBATEPOST].[attachment],
- [DEBATEPOST].[bbcodeoff],
- [DEBATEPOST].[fid],
- [DEBATEPOST].[htmlon],
- [DEBATEPOST].[invisible],
- [DEBATEPOST].[ip],
- [DEBATEPOST].[lastedit],
- [DEBATEPOST].[layer],
- [DEBATEPOST].[message],
- [DEBATEPOST].[parentid],
- [DEBATEPOST].[parseurloff],
- [DEBATEPOST].[pid],
- [DEBATEPOST].[postdatetime],
- [DEBATEPOST].[poster],
- [DEBATEPOST].[posterid],
- [DEBATEPOST].[rate],
- [DEBATEPOST].[ratetimes],
- [DEBATEPOST].[smileyoff],
- [DEBATEPOST].[tid],
- [DEBATEPOST].[title],
- [DEBATEPOST].[usesig],
- [DEBATEPOST].[accessmasks],
- [DEBATEPOST].[adminid],
- [DEBATEPOST].[avatarshowid],
- [DEBATEPOST].[bday],
- [DEBATEPOST].[credits],
- [DEBATEPOST].[digestposts],
- [DEBATEPOST].[email],
- [DEBATEPOST].[extcredits1],
- [DEBATEPOST].[extcredits2],
- [DEBATEPOST].[extcredits3],
- [DEBATEPOST].[extcredits4],
- [DEBATEPOST].[extcredits5],
- [DEBATEPOST].[extcredits6],
- [DEBATEPOST].[extcredits7],
- [DEBATEPOST].[extcredits8],
- [DEBATEPOST].[extgroupids],
- [DEBATEPOST].[gender],
- [DEBATEPOST].[groupexpiry],
- [DEBATEPOST].[groupid],
- [DEBATEPOST].[joindate],
- [DEBATEPOST].[lastactivity],
- [DEBATEPOST].[lastip],
- [DEBATEPOST].[lastpost],
- [DEBATEPOST].[lastpostid],
- [DEBATEPOST].[lastposttitle],
- [DEBATEPOST].[lastvisit],
- [DEBATEPOST].[newpm],
- [DEBATEPOST].[newpmcount],
- [DEBATEPOST].[newsletter],
- [DEBATEPOST].[nickname],
- [DEBATEPOST].[oltime],
- [DEBATEPOST].[onlinestate],
- [DEBATEPOST].[pageviews],
- [DEBATEPOST].[password],
- [DEBATEPOST].[pmsound],
- [DEBATEPOST].[posts],
- [DEBATEPOST].[ppp],
- [DEBATEPOST].[regip],
- [DEBATEPOST].[secques],
- [DEBATEPOST].[showemail],
- [DEBATEPOST].[sigstatus],
- [DEBATEPOST].[spaceid],
- [DEBATEPOST].[templateid],
- [DEBATEPOST].[tpp],
- [DEBATEPOST].[uid],
- [DEBATEPOST].[username],
- [DEBATEPOST].[authflag],
- [DEBATEPOST].[authstr],
- [DEBATEPOST].[authtime],
- [DEBATEPOST].[avatar],
- [DEBATEPOST].[avatarheight],
- [DEBATEPOST].[avatarwidth],
- [DEBATEPOST].[bio],
- [DEBATEPOST].[customstatus],
- [DEBATEPOST].[icq],
- [DEBATEPOST].[idcard],
- [DEBATEPOST].[ignorepm],
- [DEBATEPOST].[location],
- [DEBATEPOST].[medals],
- [DEBATEPOST].[mobile],
- [DEBATEPOST].[msn],
- [DEBATEPOST].[phone],
- [DEBATEPOST].[qq],
- [DEBATEPOST].[realname],
- [DEBATEPOST].[sightml],
- [DEBATEPOST].[signature],
- [DEBATEPOST].[skype],
- [DEBATEPOST].[website],
- [DEBATEPOST].[yahoo]
- FROM ( SELECT ROW_NUMBER() OVER(ORDER BY [pid]) AS ROWID,
- [dnt_posts1].[attachment],
- [dnt_posts1].[bbcodeoff],
- [dnt_posts1].[fid],
- [dnt_posts1].[htmlon],
- [dnt_posts1].[invisible],
- [dnt_posts1].[ip],
- [dnt_posts1].[lastedit],
- [dnt_posts1].[layer],
- [dnt_posts1].[message],
- [dnt_posts1].[parentid],
- [dnt_posts1].[parseurloff],
- [dnt_posts1].[pid],
- [dnt_posts1].[postdatetime],
- [dnt_posts1].[poster],
- [dnt_posts1].[posterid],
- [dnt_posts1].[rate],
- [dnt_posts1].[ratetimes],
- [dnt_posts1].[smileyoff],
- [dnt_posts1].[tid],
- [dnt_posts1].[title],
- [dnt_posts1].[usesig],
- [dnt_users].[accessmasks],
- [dnt_users].[adminid],
- [dnt_users].[avatarshowid],
- [dnt_users].[bday],
- [dnt_users].[credits],
- [dnt_users].[digestposts],
- [dnt_users].[email],
- [dnt_users].[extcredits1],
- [dnt_users].[extcredits2],
- [dnt_users].[extcredits3],
- [dnt_users].[extcredits4],
- [dnt_users].[extcredits5],
- [dnt_users].[extcredits6],
- [dnt_users].[extcredits7],
- [dnt_users].[extcredits8],
- [dnt_users].[extgroupids],
- [dnt_users].[gender],
- [dnt_users].[groupexpiry],
- [dnt_users].[groupid],
- [dnt_users].[joindate],
- [dnt_users].[lastactivity],
- [dnt_users].[lastip],
- [dnt_users].[lastpost],
- [dnt_users].[lastpostid],
- [dnt_users].[lastposttitle],
- [dnt_users].[lastvisit],
- [dnt_users].[newpm],
- [dnt_users].[newpmcount],
- [dnt_users].[newsletter],
- [dnt_users].[nickname],
- [dnt_users].[oltime],
- [dnt_users].[onlinestate],
- [dnt_users].[pageviews],
- [dnt_users].[password],
- [dnt_users].[pmsound],
- [dnt_users].[posts],
- [dnt_users].[ppp],
- [dnt_users].[regip],
- [dnt_users].[secques],
- [dnt_users].[showemail],
- [dnt_users].[sigstatus],
- [dnt_users].[spaceid],
- [dnt_users].[templateid],
- [dnt_users].[tpp],
- [dnt_users].[uid],
- [dnt_users].[username],
- [dnt_userfields].[authflag],
- [dnt_userfields].[authstr],
- [dnt_userfields].[authtime],
- [dnt_userfields].[avatar],
- [dnt_userfields].[avatarheight],
- [dnt_userfields].[avatarwidth],
- [dnt_userfields].[bio],
- [dnt_userfields].[customstatus],
- [dnt_userfields].[icq],
- [dnt_userfields].[idcard],
- [dnt_userfields].[ignorepm],
- [dnt_userfields].[location],
- [dnt_userfields].[medals],
- [dnt_userfields].[mobile],
- [dnt_userfields].[msn],
- [dnt_userfields].[phone],
- [dnt_userfields].[qq],
- [dnt_userfields].[realname],
- [dnt_userfields].[sightml],
- [dnt_userfields].[signature],
- [dnt_userfields].[skype],
- [dnt_userfields].[website],
- [dnt_userfields].[yahoo]
- FROM [dnt_posts1]
- LEFT JOIN [dnt_users] ON [dnt_users].[uid] = [dnt_posts1].[posterid]
- LEFT JOIN [dnt_userfields] ON [dnt_userfields].[uid] = [dnt_posts1].[posterid]
- WHERE [dnt_posts1].invisible=0
- AND [dnt_posts1].pid IN (SELECT pid FROM dnt_postdebatefields WHERE opinion=@opinion AND tid=@tid)) AS DEBATEPOST
- WHERE ROWID BETWEEN @startRow AND @endRow
- GO
- IF OBJECT_ID('[dnt_getfavoritescount]','P') IS NOT NULL
- DROP PROC [dnt_getfavoritescount]
- GO
- CREATE PROCEDURE [dnt_getfavoritescount]
- @uid int,
- @typeid smallint
- AS
- SELECT COUNT(uid) FROM [dnt_favorites] WHERE [uid]=@uid AND [typeid]=@typeid
- GO
- IF OBJECT_ID ('dnt_getfavoriteslist','P') IS NOT NULL
- DROP PROCEDURE [dnt_getfavoriteslist]
- GO
- CREATE PROCEDURE [dnt_getfavoriteslist]
- @uid int,
- @pagesize int,
- @pageindex int
- AS
- --SELECT [f].[uid],[f].[tid],[topics].[title],[topics].[poster],[topics].[postdatetime],[topics].[replies],[topics].[views],[topics].[posterid] FROM [dnt_favorites] [f] LEFT JOIN [dnt_topics] [topics] ON [f].[tid]=[topics].[tid] WHERE [f].[typeid]=0 AND [f].[uid]=@uid
- IF @pageindex = 1
- BEGIN
- SELECT TOP(@pagesize) [uid],[tid],[title],[poster],[postdatetime],[replies],[views],[posterid]
- FROM (SELECT [f].[uid],[f].[tid],[topics].[title],[topics].[poster],[topics].[postdatetime],[topics].[replies],[topics].[views],[topics].[posterid] FROM [dnt_favorites] [f] LEFT JOIN [dnt_topics] [topics] ON [f].[tid]=[topics].[tid] WHERE [f].[typeid]=0 AND [f].[uid]=@uid) favorites ORDER BY [tid] DESC
- END
- ELSE
- BEGIN
- SELECT TOP(@pagesize) [uid],[tid],[title],[poster],[postdatetime],[replies],[views],[posterid]
- FROM (SELECT [f].[uid],[f].[tid],[topics].[title],[topics].[poster],[topics].[postdatetime],[topics].[replies],[topics].[views],[topics].[posterid] FROM [dnt_favorites] [f] LEFT JOIN [dnt_topics] [topics] ON [f].[tid]=[topics].[tid] WHERE [f].[typeid]=0 AND [f].[uid]=@uid) f1 WHERE [tid] < (SELECT MIN([tid]) FROM (SELECT TOP((@pageindex-1)*@pagesize) [tid]
- FROM (SELECT [f].[uid],[f].[tid],[topics].[title],[topics].[poster],[topics].[postdatetime],[topics].[replies],[topics].[views],[topics].[posterid] FROM [dnt_favorites] [f] LEFT JOIN [dnt_topics] [topics] ON [f].[tid]=[topics].[tid] WHERE [f].[typeid]=0 AND [f].[uid]=@uid) f2 ORDER BY [tid] DESC) AS tblTmp) ORDER BY [tid] DESC
- END
- GO
- IF OBJECT_ID('[dnt_getlastexecutescheduledeventdatetime]','P') IS NOT NULL
- DROP PROC [dnt_getlastexecutescheduledeventdatetime]
- GO
- CREATE PROCEDURE [dnt_getlastexecutescheduledeventdatetime]
- (
- @key VARCHAR(100),
- @servername VARCHAR(100),
- @lastexecuted DATETIME OUTPUT
- )
- AS
- SELECT @lastexecuted = MAX([lastexecuted]) FROM [dnt_scheduledevents] WHERE [key] = @key AND [servername] = @servername
- IF(@lastexecuted IS NULL)
- BEGIN
- SET @lastexecuted = DATEADD(YEAR,-1,GETDATE())
- END
- GO
- IF OBJECT_ID ('dnt_getlastpostlist1','P') IS NOT NULL
- DROP PROCEDURE [dnt_getlastpostlist1]
- GO
- CREATE PROCEDURE [dnt_getlastpostlist1]
- @tid int,
- @pageindex int,
- @postnum int
- --@posttablename varchar(20)
- AS
- DECLARE @startRow int,
- @endRow int
- SET @startRow = (@pageIndex - 1) * @postnum +1
- SET @endRow = @startRow + @postnum -1
- SELECT
- [POST].[pid],
- [POST].[fid],
- [POST].[layer],
- [POST].[posterid],
- [POST].[title],
- [POST].[message],
- [POST].[postdatetime],
- [POST].[attachment],
- [POST].[poster],
- [POST].[invisible],
- [POST].[usesig],
- [POST].[htmlon],
- [POST].[smileyoff],
- [POST].[parseurloff],
- [POST].[bbcodeoff],
- [POST].[rate],
- [POST].[ratetimes],
- [POST].[username],
- [POST].[email],
- [POST].[showemail],
- [POST].[avatar],
- [POST].[avatarwidth],
- [POST].[avatarheight],
- [POST].[signature],
- [POST].[location],
- [POST].[customstatus]
- FROM (SELECT ROW_NUMBER() OVER(ORDER BY [pid]) AS ROWID,
- [dnt_posts1].[pid],
- [dnt_posts1].[fid],
- [dnt_posts1].[layer],
- [dnt_posts1].[posterid],
- [dnt_posts1].[title],
- [dnt_posts1].[message],
- [dnt_posts1].[postdatetime],
- [dnt_posts1].[attachment],
- [dnt_posts1].[poster],
- [dnt_posts1].[invisible],
- [dnt_posts1].[usesig],
- [dnt_posts1].[htmlon],
- [dnt_posts1].[smileyoff],
- [dnt_posts1].[parseurloff],
- [dnt_posts1].[bbcodeoff],
- [dnt_posts1].[rate],
- [dnt_posts1].[ratetimes],
- [dnt_users].[username],
- [dnt_users].[email],
- [dnt_users].[showemail],
- [dnt_userfields].[avatar],
- [dnt_userfields].[avatarwidth],
- [dnt_userfields].[avatarheight],
- [dnt_userfields].[sightml] AS [signature],
- [dnt_userfields].[location],
- [dnt_userfields].[customstatus]
- FROM [dnt_posts1]
- LEFT JOIN [dnt_users] ON [dnt_users].[uid]=[dnt_posts1].[posterid]
- LEFT JOIN [dnt_userfields] ON [dnt_userfields].[uid]=[dnt_users].[uid]
- WHERE [dnt_posts1].[tid] = @tid AND [dnt_posts1].[invisible] <=0 AND [dnt_posts1].layer <> 0) AS POST
- WHERE ROWID BETWEEN @startRow AND @endRow
- GO
- IF OBJECT_ID ('dnt_getmyattachments','P') IS NOT NULL
- DROP PROCEDURE [dnt_getmyattachments]
- GO
- CREATE PROCEDURE [dnt_getmyattachments]
- @uid int,
- @pageindex int,
- @pagesize int
- AS
- DECLARE @startRow int,
- @endRow int
- SET @startRow=(@pageindex-1) * @pagesize + 1
- SET @endRow = @startRow + @pagesize - 1
- SELECT
- [ATTACHMENTS].[aid],
- [ATTACHMENTS].[uid],
- [ATTACHMENTS].[attachment],
- [ATTACHMENTS].[description],
- [ATTACHMENTS].[downloads],
- [ATTACHMENTS].[extname],
- [ATTACHMENTS].[filename],
- [ATTACHMENTS].[pid],
- [ATTACHMENTS].[postdatetime],
- [ATTACHMENTS].[tid]
- FROM (SELECT ROW_NUMBER() OVER(ORDER BY [aid] DESC) AS ROWID,
- [dnt_myattachments].[aid],
- [dnt_myattachments].[uid],
- [dnt_myattachments].[attachment],
- [dnt_myattachments].[description],
- [dnt_myattachments].[downloads],
- [dnt_myattachments].[extname],
- [dnt_myattachments].[filename],
- [dnt_myattachments].[pid],
- [dnt_myattachments].[postdatetime],
- [dnt_myattachments].[tid]
- FROM [dnt_myattachments]
- WHERE [dnt_myattachments].[uid] = @uid ) AS ATTACHMENTS
- WHERE ROWID BETWEEN @startRow AND @endRow
- GO
- IF OBJECT_ID ('dnt_getmyattachmentsbytype','P') IS NOT NULL
- DROP PROCEDURE [dnt_getmyattachmentsbytype]
- GO
- CREATE PROCEDURE [dnt_getmyattachmentsbytype]
- @uid int,
- @pageindex int,
- @pagesize int,
- @extlist as nvarchar(100)
- AS
- DECLARE @startRow int,
- @endRow int
- SET @startRow=(@pageindex - 1) * @pagesize + 1
- SET @endRow = @startRow + @pagesize - 1
- SELECT
- [ATTACHMENTS].[aid],
- [ATTACHMENTS].[uid],
- [ATTACHMENTS].[attachment],
- [ATTACHMENTS].[description],
- [ATTACHMENTS].[downloads],
- [ATTACHMENTS].[extname],
- [ATTACHMENTS].[filename],
- [ATTACHMENTS].[pid],
- [ATTACHMENTS].[postdatetime],
- [ATTACHMENTS].[tid]
- FROM (SELECT ROW_NUMBER() OVER(ORDER BY [aid] DESC) AS ROWID,
- [dnt_myattachments].[aid],
- [dnt_myattachments].[uid],
- [dnt_myattachments].[attachment],
- [dnt_myattachments].[description],
- [dnt_myattachments].[downloads],
- [dnt_myattachments].[extname],
- [dnt_myattachments].[filename],
- [dnt_myattachments].[pid],
- [dnt_myattachments].[postdatetime],
- [dnt_myattachments].[tid]
- FROM [dnt_myattachments]
- WHERE [uid] = @uid AND CHARINDEX([dnt_myattachments].[extname], @extlist) > 0) AS ATTACHMENTS
- WHERE ROWID BETWEEN @startRow AND @endRow
- GO
- IF OBJECT_ID ('dnt_getmyposts','P') IS NOT NULL
- DROP PROCEDURE [dnt_getmyposts]
- GO
- CREATE PROCEDURE [dnt_getmyposts]
- @uid int,
- @pageindex int,
- @pagesize int
- AS
- DECLARE @startRow int,
- @endRow int
- SET @startRow = ( @pageindex - 1 ) * @pagesize + 1
- SET @endRow = @startRow + @pagesize - 1
- SELECT
- [MYPOST].[tid],
- [MYPOST].[fid],
- [MYPOST].[iconid],
- [MYPOST].[typeid],
- [MYPOST].[readperm],
- [MYPOST].[price],
- [MYPOST].[poster],
- [MYPOST].[posterid],
- [MYPOST].[title],
- [MYPOST].[postdatetime],
- [MYPOST].[lastpost],
- [MYPOST].[lastpostid],
- [MYPOST].[lastposter],
- [MYPOST].[lastposterid],
- [MYPOST].[views],
- [MYPOST].[replies],
- [MYPOST].[displayorder],
- [MYPOST].[highlight],
- [MYPOST].[digest],
- [MYPOST].[rate],
- [MYPOST].[hide],
- [MYPOST].[special],
- [MYPOST].[attachment],
- [MYPOST].[moderated],
- [MYPOST].[closed],
- [MYPOST].[magic]
- FROM (SELECT ROW_NUMBER() OVER(ORDER BY [dnt_myposts].[tid] DESC) AS ROWID,
- [dnt_topics].[tid],
- [dnt_topics].[fid],
- [dnt_topics].[iconid],
- [dnt_topics].[typeid],
- [dnt_topics].[readperm],
- [dnt_topics].[price],
- [dnt_topics].[poster],
- [dnt_topics].[posterid],
- [dnt_topics].[title],
- [dnt_topics].[postdatetime],
- [dnt_topics].[lastpost],
- [dnt_topics].[lastpostid],
- [dnt_topics].[lastposter],
- [dnt_topics].[lastposterid],
- [dnt_topics].[views],
- [dnt_topics].[replies],
- [dnt_topics].[displayorder],
- [dnt_topics].[highlight],
- [dnt_topics].[digest],
- [dnt_topics].[rate],
- [dnt_topics].[hide],
- [dnt_topics].[special],
- [dnt_topics].[attachment],
- [dnt_topics].[moderated],
- [dnt_topics].[closed],
- [dnt_topics].[magic]
- FROM [dnt_topics]
- INNER JOIN [dnt_myposts] ON ([dnt_topics].[tid] = [dnt_myposts].[tid] AND [dnt_myposts].[uid] = @uid)) AS MYPOST
- WHERE ROWID BETWEEN @startRow AND @endRow
- GO
- IF OBJECT_ID ('dnt_getmytopics','P') IS NOT NULL
- DROP PROCEDURE [dnt_getmytopics]
- GO
- CREATE PROCEDURE [dnt_getmytopics]
- @uid int,
- @pageindex int,
- @pagesize int
- AS
- DECLARE @startRow int,
- @endRow int
- SET @startRow = ( @pageindex - 1 ) * @pagesize + 1
- SET @endRow = @startRow + @pagesize - 1
- SELECT
- [MYTOPIC].[tid],
- [MYTOPIC].[fid],
- [MYTOPIC].[iconid],
- [MYTOPIC].[typeid],
- [MYTOPIC].[readperm],
- [MYTOPIC].[price],
- [MYTOPIC].[poster],
- [MYTOPIC].[posterid],
- [MYTOPIC].[title],
- [MYTOPIC].[postdatetime],
- [MYTOPIC].[lastpost],
- [MYTOPIC].[lastpostid],
- [MYTOPIC].[lastposter],
- [MYTOPIC].[lastposterid],
- [MYTOPIC].[views],
- [MYTOPIC].[replies],
- [MYTOPIC].[displayorder],
- [MYTOPIC].[highlight],
- [MYTOPIC].[digest],
- [MYTOPIC].[rate],
- [MYTOPIC].[hide],
- [MYTOPIC].[special],
- [MYTOPIC].[attachment],
- [MYTOPIC].[moderated],
- [MYTOPIC].[closed],
- [MYTOPIC].[magic]
- FROM (SELECT ROW_NUMBER() OVER(ORDER BY [dnt_topics].[lastpost] DESC) AS ROWID,
- [dnt_topics].[tid],
- [dnt_topics].[fid],
- [dnt_topics].[iconid],
- [dnt_topics].[typeid],
- [dnt_topics].[readperm],
- [dnt_topics].[price],
- [dnt_topics].[poster],
- [dnt_topics].[posterid],
- [dnt_topics].[title],
- [dnt_topics].[postdatetime],
- [dnt_topics].[lastpost],
- [dnt_topics].[lastpostid],
- [dnt_topics].[lastposter],
- [dnt_topics].[lastposterid],
- [dnt_topics].[views],
- [dnt_topics].[replies],
- [dnt_topics].[displayorder],
- [dnt_topics].[highlight],
- [dnt_topics].[digest],
- [dnt_topics].[rate],
- [dnt_topics].[hide],
- [dnt_topics].[special],
- [dnt_topics].[attachment],
- [dnt_topics].[moderated],
- [dnt_topics].[closed],
- [dnt_topics].[magic]
- FROM [dnt_topics]
- INNER JOIN [dnt_mytopics] ON ([dnt_topics].[tid] = [dnt_mytopics].[tid] AND [dnt_mytopics].[uid] = @uid)) AS MYTOPIC
- WHERE ROWID BETWEEN @startRow AND @endRow
- GO
- IF OBJECT_ID('[dnt_getpmcount]','P') IS NOT NULL
- DROP PROC [dnt_getpmcount]
- GO
- CREATE PROCEDURE [dnt_getpmcount]
- @userid int,
- @folder int=0,
- @state int=-1
- AS
- IF @folder=-1
- BEGIN
- SELECT COUNT(pmid) AS [pmcount] FROM [dnt_pms] WHERE ([msgtoid] = @userid AND [folder]=0) OR ([msgfromid] = @userid AND [folder] = 1) OR ([msgfromid] = @userid AND [folder] = 2)
- END
- ELSE
- BEGIN
- IF @folder=0
- BEGIN
- IF @state=-1
- BEGIN
- SELECT COUNT(pmid) AS [pmcount] FROM [dnt_pms] WHERE [msgtoid] = @userid AND [folder] = @folder
- END
- ELSE IF @state=2
- BEGIN
- SELECT COUNT(pmid) AS [pmcount] FROM [dnt_pms] WHERE [msgtoid] = @userid AND [folder] = @folder AND [new]=1 AND GETDATE()-[postdatetime]<3
- END
- ELSE
- BEGIN
- SELECT COUNT(pmid) AS [pmcount] FROM [dnt_pms] WHERE [msgtoid] = @userid AND [folder] = @folder AND [new] = @state
- END
- END
- ELSE
- BEGIN
- IF @state=-1
- BEGIN
- SELECT COUNT(pmid) AS [pmcount] FROM [dnt_pms] WHERE [msgfromid] = @userid AND [folder] = @folder
- END
- ELSE IF @state=2
- BEGIN
- SELECT COUNT(pmid) AS [pmcount] FROM [dnt_pms] WHERE [msgfromid] = @userid AND [folder] = @folder AND [new]=1 AND GETDATE()-[postdatetime]<3
- END
- ELSE
- BEGIN
- SELECT COUNT(pmid) AS [pmcount] FROM [dnt_pms] WHERE [msgfromid] = @userid AND [folder] = @folder AND [new] = @state
- END
- END
- END
- GO
- IF OBJECT_ID('[dnt_getnoticecount]','P') IS NOT NULL
- DROP PROC [dnt_getnoticecount]
- GO
- CREATE PROCEDURE [dnt_getnoticecount]
- @userid int,
- @type int = -1,
- @state int=-1
- AS
- IF @type = -1
- BEGIN
- IF @state = -1
- BEGIN
- SELECT COUNT(nid) AS [pmcount] FROM [dnt_notices] WHERE [uid]=@userid
- END
- ELSE
- BEGIN
- SELECT COUNT(nid) AS [pmcount] FROM [dnt_notices] WHERE [uid]=@userid AND [new]=@state
- END
- END
- ELSE
- BEGIN
- IF @state = -1
- BEGIN
- SELECT COUNT(nid) AS [pmcount] FROM [dnt_notices] WHERE [uid]=@userid AND [type]=@type
- END
- ELSE
- BEGIN
- SELECT COUNT(nid) AS [pmcount] FROM [dnt_notices] WHERE [uid]=@userid AND [new]=@state AND [type]=@type
- END
- END
- GO
- IF OBJECT_ID('dnt_getpmlist','P') IS NOT NULL
- DROP PROC [dnt_getpmlist]
- GO
- CREATE PROCEDURE [dnt_getpmlist]
- @userid int,
- @folder int,
- @pagesize int,
- @pageindex int,
- @inttype int
- AS
- DECLARE @startRow int,
- @endRow int
- SET @startRow = (@pageindex - 1) * @pagesize + 1
- SET @endRow = @startRow + @pagesize - 1
- IF (@folder <> 0)
- BEGIN
- IF (@inttype <> 1)
- BEGIN
- SELECT
- [PMS].[pmid],
- [PMS].[msgfrom],
- [PMS].[msgfromid],
- [PMS].[msgto],
- [PMS].[msgtoid],
- [PMS].[folder],
- [PMS].[new],
- [PMS].[subject],
- [PMS].[postdatetime],
- [PMS].[message]
- FROM(SELECT ROW_NUMBER() OVER(ORDER BY [pmid] DESC) AS ROWID,
- [pmid],
- [msgfrom],
- [msgfromid],
- [msgto],
- [msgtoid],
- [folder],
- [new],
- [subject],
- [postdatetime],
- [message]
- FROM [dnt_pms]
- WHERE [msgfromid] = @userid AND [folder] = @folder) AS PMS
- WHERE ROWID BETWEEN @startRow AND @endRow
- END
-
- ELSE
- BEGIN
- SELECT
- [PMS].[pmid],
- [PMS].[msgfrom],
- [PMS].[msgfromid],
- [PMS].[msgto],
- [PMS].[msgtoid],
- [PMS].[folder],
- [PMS].[new],
- [PMS].[subject],
- [PMS].[postdatetime],
- [PMS].[message]
- FROM(SELECT ROW_NUMBER() OVER(ORDER BY [pmid] DESC) AS ROWID,
- [pmid],
- [msgfrom],
- [msgfromid],
- [msgto],
- [msgtoid],
- [folder],
- [new],
- [subject],
- [postdatetime],
- [message]
- FROM [dnt_pms]
- WHERE [msgfromid] = @userid AND [folder] = @folder AND [new] = 1) AS PMS
- WHERE ROWID BETWEEN @startRow AND @endRow
- END
- END
- ELSE
- BEGIN
- IF (@inttype <> 1)
- BEGIN
- SELECT
- [PMS].[pmid],
- [PMS].[msgfrom],
- [PMS].[msgfromid],
- [PMS].[msgto],
- [PMS].[msgtoid],
- [PMS].[folder],
- [PMS].[new],
- [PMS].[subject],
- [PMS].[postdatetime],
- [PMS].[message]
- FROM(SELECT ROW_NUMBER() OVER(ORDER BY [pmid] DESC) AS ROWID,
- [pmid],
- [msgfrom],
- [msgfromid],
- [msgto],
- [msgtoid],
- [folder],
- [new],
- [subject],
- [postdatetime],
- [message]
- FROM [dnt_pms]
- WHERE [msgtoid] = @userid AND [folder] = @folder) AS PMS
- WHERE ROWID BETWEEN @startRow AND @endRow
- END
-
- ELSE
- BEGIN
- SELECT
- [PMS].[pmid],
- [PMS].[msgfrom],
- [PMS].[msgfromid],
- [PMS].[msgto],
- [PMS].[msgtoid],
- [PMS].[folder],
- [PMS].[new],
- [PMS].[subject],
- [PMS].[postdatetime],
- [PMS].[message]
- FROM(SELECT ROW_NUMBER() OVER(ORDER BY [pmid] DESC) AS ROWID,
- [pmid],
- [msgfrom],
- [msgfromid],
- [msgto],
- [msgtoid],
- [folder],
- [new],
- [subject],
- [postdatetime],
- [message]
- FROM [dnt_pms]
- WHERE [msgtoid] = @userid AND [folder] = @folder AND [new] = 1) AS PMS
- WHERE ROWID BETWEEN @startRow AND @endRow
- END
- END
- GO
- IF OBJECT_ID('[dnt_getpost1count]','P') IS NOT NULL
- DROP PROC [dnt_getpost1count]
- GO
- CREATE PROCEDURE [dnt_getpost1count]
- @tid int
- AS
- SELECT COUNT(pid) FROM [dnt_posts1] WHERE [tid] = @tid AND [invisible]=0 AND layer>0
- GO
- IF OBJECT_ID('dnt_getpost1tree','P') IS NOT NULL
- DROP PROC dnt_getpost1tree
- GO
- CREATE PROCEDURE dnt_getpost1tree
- @tid int
- AS
- SELECT [pid], [layer], [title], [poster], [posterid],[postdatetime],[message] FROM [dnt_posts1] WHERE [tid] = @tid AND [invisible]=0 ORDER BY [parentid];
- GO
- IF OBJECT_ID('dnt_getpostcountbycondition1','P') IS NOT NULL
- DROP PROC [dnt_getpostcountbycondition1]
- GO
- CREATE PROCEDURE [dnt_getpostcountbycondition1]
- @tid int,
- @posterid int
- AS
- SELECT COUNT(pid) FROM [dnt_posts1] WHERE [tid] = @tid AND [posterid] = @posterid AND [layer]>=0
- GO
- IF OBJECT_ID('[dnt_getshortuserinfo]','P') IS NOT NULL
- DROP PROC [dnt_getshortuserinfo]
- GO
- CREATE PROCEDURE [dnt_getshortuserinfo]
- @uid int
- AS
- SELECT TOP 1 * FROM [dnt_users] WHERE [uid]=@uid
- GO
- if exists (select * from sysobjects where id = object_id(N'[dnt_getsinglepost1]') and OBJECTPROPERTY(id,N'IsProcedure') = 1)
- drop procedure [dnt_getsinglepost1]
- GO
- CREATE PROCEDURE [dnt_getsinglepost1]
- @tid int,
- @pid int
- AS
- SELECT [aid], [tid], [pid], [postdatetime], [readperm], [filename], [description], [filetype], [filesize], [attachment], [downloads], [attachprice], [uid], [width], [height] FROM [dnt_attachments] WHERE [tid]=@tid
- SELECT TOP 1
- [dnt_posts1].[pid],
- [dnt_posts1].[fid],
- [dnt_posts1].[title],
- [dnt_posts1].[layer],
- [dnt_posts1].[message],
- [dnt_posts1].[ip],
- [dnt_posts1].[lastedit],
- [dnt_posts1].[postdatetime],
- [dnt_posts1].[attachment],
- [dnt_posts1].[poster],
- [dnt_posts1].[invisible],
- [dnt_posts1].[usesig],
- [dnt_posts1].[htmlon],
- [dnt_posts1].[smileyoff],
- [dnt_posts1].[parseurloff],
- [dnt_posts1].[bbcodeoff],
- [dnt_posts1].[rate],
- [dnt_posts1].[ratetimes],
- [dnt_posts1].[posterid],
- [dnt_users].[nickname],
- [dnt_users].[username],
- [dnt_users].[groupid],
- [dnt_users].[spaceid],
- [dnt_users].[gender],
- [dnt_users].[bday],
- [dnt_users].[email],
- [dnt_users].[showemail],
- [dnt_users].[digestposts],
- [dnt_users].[credits],
- [dnt_users].[extcredits1],
- [dnt_users].[extcredits2],
- [dnt_users].[extcredits3],
- [dnt_users].[extcredits4],
- [dnt_users].[extcredits5],
- [dnt_users].[extcredits6],
- [dnt_users].[extcredits7],
- [dnt_users].[extcredits8],
- [dnt_users].[posts],
- [dnt_users].[joindate],
- [dnt_users].[onlinestate],
- [dnt_users].[lastactivity],
- [dnt_users].[invisible],
- [dnt_users].[oltime],
- [dnt_users].[lastvisit],
- [dnt_userfields].[avatar],
- [dnt_userfields].[avatarwidth],
- [dnt_userfields].[avatarheight],
- [dnt_userfields].[medals],
- [dnt_userfields].[sightml] AS signature,
- [dnt_userfields].[location],
- [dnt_userfields].[customstatus],
- [dnt_userfields].[website],
- [dnt_userfields].[icq],
- [dnt_userfields].[qq],
- [dnt_userfields].[msn],
- [dnt_userfields].[yahoo],
- [dnt_userfields].[skype]
- FROM [dnt_posts1] LEFT JOIN [dnt_users] ON [dnt_users].[uid]=[dnt_posts1].[posterid] LEFT JOIN [dnt_userfields] ON [dnt_userfields].[uid]=[dnt_users].[uid] WHERE [dnt_posts1].[pid]=@pid
- GO
- IF OBJECT_ID('dnt_getsitemapnewtopics','P') IS NOT NULL
- DROP PROC [dnt_getsitemapnewtopics]
- GO
- CREATE PROCEDURE [dnt_getsitemapnewtopics]
- @fidlist VARCHAR(500)
- AS
- IF @fidlist<>''
- BEGIN
- DECLARE @strSQL VARCHAR(5000)
- SET @strSQL = 'SELECT TOP 20 [tid], [fid], [title], [poster], [postdatetime], [lastpost], [replies], [views], [digest] FROM [dnt_topics] WHERE [fid]
- NOT IN ('+@fidlist +') ORDER BY [tid] DESC'
- END
- ELSE
- BEGIN
- SET @strSQL = 'SELECT TOP 20 [tid], [fid], [title], [poster], [postdatetime], [lastpost], [replies], [views], [digest] FROM [dnt_topics] ORDER BY [tid]
- DESC'
- END
- EXEC(@strSQL)
- GO
- IF OBJECT_ID('[dnt_gettopiccount]','P') IS NOT NULL
- DROP PROC [dnt_gettopiccount]
- GO
- CREATE PROCEDURE [dnt_gettopiccount]
- @fid int
- AS
- SELECT [curtopics] FROM [dnt_forums] WHERE [fid] = @fid
- GO
- IF OBJECT_ID('dnt_gettopiccountbycondition','P') IS NOT NULL
- DROP PROC [dnt_gettopiccountbycondition]
- GO
- CREATE PROCEDURE [dnt_gettopiccountbycondition]
- @fid int,
- @state int=0,
- @condition varchar(80)=null
- AS
- DECLARE @sql varchar(500)
- IF @state=-1
- BEGIN
- set @sql ='SELECT COUNT(tid) FROM [dnt_topics] WHERE [fid]='+str(@fid)+' AND [displayorder]>-1 AND [closed]<=1'+@condition
- END
- ELSE
- BEGIN
- set @sql ='SELECT COUNT(tid) FROM [dnt_topics] WHERE [fid]='+str(@fid)+' AND [displayorder]>-1 AND [closed]='+str(@state)+' AND [closed]<=1'+@condition
- END
- exec(@sql)
- GO
- IF OBJECT_ID('[dnt_gettopiccountbytype]','P') IS NOT NULL
- DROP PROC [dnt_gettopiccountbytype]
- GO
- CREATE PROCEDURE [dnt_gettopiccountbytype]
- @condition varchar(4000)
- AS
- DECLARE @sql varchar(4100)
- set @sql ='SELECT COUNT(tid) FROM [dnt_topics] WHERE [displayorder]>-1 AND [closed]<=1 '+@condition
- exec(@sql)
- GO
- IF OBJECT_ID('dnt_gettopiclistbycondition','P') IS NOT NULL
- DROP PROC [dnt_gettopiclistbycondition]
- GO
- CREATE PROCEDURE [dnt_gettopiclistbycondition]
- @fid int,
- @pagesize int,
- @pageindex int,
- @startnum int,
- @condition varchar(80)
- AS
- DECLARE @strSQL varchar(5000)
- IF @pageindex = 1
- BEGIN
- SET @strSQL = 'SELECT TOP ' + STR(@pagesize) +' [rate],[tid],[iconid],[typeid],[title],[price],[hide],[readperm],
- [special],[poster],[posterid],[replies],[views],[postdatetime],[lastpost],[lastposter],
- [lastpostid],[lastposterid],[replies],[highlight],[digest],[displayorder],[attachment],[closed],[magic],[special] FROM
- [dnt_topics] WHERE [fid]=' +STR(@fid) + ' AND [displayorder]=0' + @condition + ' ORDER BY [lastpostid] DESC'
- END
- ELSE
- BEGIN
- SET @strSQL = 'SELECT TOP ' + STR(@pagesize) +'[rate], [tid],[iconid],[typeid],[title],[price],[hide],[readperm],
- [special],[poster],[posterid],[replies],[views],[postdatetime],[lastpost],[lastposter],
- [lastpostid],[lastposterid],[replies],[highlight],[digest],[displayorder],[attachment],[closed],[magic],[special] FROM
- [dnt_topics] WHERE [lastpostid] < (SELECT min([lastpostid]) FROM (SELECT TOP ' + STR
- ((@pageindex-1)*@pagesize-@startnum) + ' [lastpostid] FROM [dnt_topics] WHERE [fid]=' +STR
- (@fid) + ' AND [displayorder]=0' + @condition + ' ORDER BY [lastpostid] DESC) AS tblTmp )
- AND [fid]=' +STR(@fid) + ' AND [displayorder]=0' + @condition + ' ORDER BY [lastpostid] DESC'
- END
- EXEC(@strSQL)
- GO
- IF OBJECT_ID('dnt_gettopiclist','P') IS NOT NULL
- DROP PROC [dnt_gettopiclist]
- GO
- CREATE PROCEDURE [dnt_gettopiclist]
- @fid int,
- @pagesize int,
- @startnum int,
- @pageindex int
- AS
- DECLARE @startRow int,
- @endRow int
- SET @startRow = (@pageindex - 1) * @pagesize + 1 - @startnum
- SET @endRow = @startRow + @pagesize -1
- SELECT
- [TOPICS].[rate],
- [TOPICS].[tid],
- [TOPICS].[iconid],
- [TOPICS].[typeid],
- [TOPICS].[title],
- [TOPICS].[price],
- [TOPICS].[hide],
- [TOPICS].[readperm],
- [TOPICS].[poster],
- [TOPICS].[posterid],
- [TOPICS].[replies],
- [TOPICS].[views],
- [TOPICS].[postdatetime],
- [TOPICS].[lastpost],
- [TOPICS].[lastposter],
- [TOPICS].[lastpostid],
- [TOPICS].[lastposterid],
- [TOPICS].[highlight],
- [TOPICS].[digest],
- [TOPICS].[displayorder],
- [TOPICS].[attachment],
- [TOPICS].[closed],
- [TOPICS].[magic],
- [TOPICS].[special]
- FROM(SELECT ROW_NUMBER() OVER(ORDER BY [lastpostid] DESC)AS ROWID,
- [dnt_topics].[rate],
- [dnt_topics].[tid],
- [dnt_topics].[iconid],
- [dnt_topics].[typeid],
- [dnt_topics].[title],
- [dnt_topics].[price],
- [dnt_topics].[hide],
- [dnt_topics].[readperm],
- [dnt_topics].[poster],
- [dnt_topics].[posterid],
- [dnt_topics].[replies],
- [dnt_topics].[views],
- [dnt_topics].[postdatetime],
- [dnt_topics].[lastpost],
- [dnt_topics].[lastposter],
- [dnt_topics].[lastpostid],
- [dnt_topics].[lastposterid],
- [dnt_topics].[highlight],
- [dnt_topics].[digest],
- [dnt_topics].[displayorder],
- [dnt_topics].[attachment],
- [dnt_topics].[closed],
- [dnt_topics].[magic],
- [dnt_topics].[special]
- FROM [dnt_topics]
- WHERE [fid]=@fid AND [displayorder]=0) AS TOPICS
- WHERE ROWID BETWEEN @startRow AND @endRow
- GO
- IF OBJECT_ID('dnt_gettopiclistbydate','P') IS NOT NULL
- DROP PROC [dnt_gettopiclistbydate]
- GO
- CREATE PROCEDURE [dnt_gettopiclistbydate]
- @fid int,
- @pagesize int,
- @pageindex int,
- @startnum int,
- @condition varchar(100),
- @orderby varchar(100),
- @ascdesc int
- AS
- DECLARE @strsql varchar(5000)
- DECLARE @sorttype varchar(5)
- IF @ascdesc=0
- SET @sorttype='ASC'
- ELSE
- SET @sorttype='DESC'
- IF @pageindex = 1
- BEGIN
- SET @strsql = 'SELECT TOP ' + STR(@pagesize) +' [tid],[iconid],[title],[price],[typeid],[readperm],[hide],[special],[poster],[posterid],[replies],[views],[postdatetime],[lastpost],[lastposter],[lastpostid],[lastposterid],[replies],[highlight],[digest],[displayorder],[attachment],[closed],[magic],[rate] FROM [dnt_topics] WHERE [fid]=' +STR(@fid) + ' AND [displayorder]=0'+@condition+' ORDER BY '+@orderby+' '+@sorttype
- END
- ELSE
- IF @sorttype='DESC'
- BEGIN
- SET @strSQL = 'SELECT TOP ' + STR(@pagesize) +' [tid],[iconid],[title],[price],[typeid],[hide],[readperm],[special],[poster],[posterid],[replies],[views],[postdatetime],[lastpost],[lastposter],[lastpostid],[lastposterid],[replies],[highlight],[digest],[displayorder],[attachment],[closed],[magic],[rate] FROM [dnt_topics] WHERE ['+@orderby+'] < (SELECT min(['+@orderby+']) FROM (SELECT TOP ' + STR((@pageindex-1)*@pagesize-@startnum) + ' ['+@orderby+'] FROM [dnt_topics] WHERE [fid]=' +STR(@fid) + ' AND [displayorder]=0'+@condition+' ORDER BY '+@orderby+' '+@sorttype+')AS tblTmp ) AND [fid]=' +STR(@fid) + ' AND [displayorder]=0'+@condition+' ORDER BY '+@orderby+' '+@sorttype
- END
- ELSE
- BEGIN
- SET @strSQL = 'SELECT TOP ' + STR(@pagesize) +' [tid],[iconid],[title],[price],[hide],[typeid],[readperm],[special],[poster],[posterid],[replies],[views],[postdatetime],[lastpost],[lastposter],[lastpostid],[lastposterid],[replies],[highlight],[digest],[displayorder],[attachment],[closed],[magic],[rate] FROM [dnt_topics] WHERE ['+@orderby+'] > (SELECT MAX(['+@orderby+']) FROM (SELECT TOP ' + STR((@pageindex-1)*@pagesize-@startnum) + ' ['+@orderby+'] FROM [dnt_topics] WHERE [fid]=' +STR(@fid) + ' AND [displayorder]=0'+@condition+' ORDER BY '+@orderby+' '+@sorttype+')AS tblTmp ) AND [fid]=' +STR(@fid) + ' AND [displayorder]=0'+@condition+' ORDER BY '+@orderby+' '+@sorttype
- END
- EXEC(@strsql)
- GO
- IF OBJECT_ID('dnt_gettopiclistbytag','P') IS NOT NULL
- DROP PROC [dnt_gettopiclistbytag]
- GO
- CREATE PROCEDURE [dnt_gettopiclistbytag]
- @tagid int,
- @pageindex int,
- @pagesize int
- AS
- DECLARE @startRow int,
- @endRow int
- SET @startRow = (@pageindex - 1) * @pagesize + 1
- SET @endRow = @startRow + @pagesize -1
- SELECT
- [TOPICTAGS].[tid],
- [TOPICTAGS].[title],
- [TOPICTAGS].[poster],
- [TOPICTAGS].[posterid],
- [TOPICTAGS].[fid],
- [TOPICTAGS].[postdatetime],
- [TOPICTAGS].[replies],
- [TOPICTAGS].[views],
- [TOPICTAGS].[lastposter],
- [TOPICTAGS].[lastposterid],
- [TOPICTAGS].[lastpost]
- FROM(SELECT ROW_NUMBER() OVER(ORDER BY [lastpostid]) AS ROWID,
- [t].[tid],
- [t].[title],
- [t].[poster],
- [t].[posterid],
- [t].[fid],
- [t].[postdatetime],
- [t].[replies],
- [t].[views],
- [t].[lastposter],
- [t].[lastposterid],
- [t].[lastpost]
- FROM [dnt_topictags] AS [tt], [dnt_topics] AS [t]
- WHERE [t].[tid] = [tt].[tid] AND [t].[displayorder] >=0 AND [tt].[tagid] = @tagid) AS TOPICTAGS
- WHERE ROWID BETWEEN @startRow AND @endRow
- GO
- IF OBJECT_ID('[dnt_gettopiclistbytype]','P') IS NOT NULL
- DROP PROC [dnt_gettopiclistbytype]
- GO
- CREATE PROCEDURE [dnt_gettopiclistbytype]
- @pagesize int,
- @pageindex int,
- @startnum int,
- @condition varchar(1000),
- @ascdesc int
- AS
- DECLARE @strSQL varchar(5000)
- DECLARE @sorttype varchar(5)
- IF @ascdesc=0
- SET @sorttype='ASC'
- ELSE
- SET @sorttype='DESC'
- IF @pageindex = 1
- BEGIN
- SET @strSQL = 'SELECT TOP ' + STR(@pagesize) +' [tid],[iconid],[typeid],[title],[price],[hide],[readperm],
- [special],[poster],[posterid],[replies],[views],[postdatetime],[lastpost],[lastposter],
- [lastpostid],[lastposterid],[replies],[highlight],[digest],[displayorder],[attachment],[closed],[magic],[rate] FROM
- [dnt_topics] WHERE [displayorder]>=0' + @condition + ' ORDER BY [lastpostid] '+@sorttype +', [tid] '+@sorttype
- END
- ELSE
- BEGIN
- IF @sorttype='DESC'
- BEGIN
- SET @strSQL = 'SELECT TOP ' + STR(@pagesize) +' [tid],[iconid],[typeid],[title],[price],[hide],[readperm],
-
- [special],[poster],[posterid],[replies],[views],[postdatetime],[lastpost],[lastposter],
-
- [lastpostid],[lastposterid],[replies],[highlight],[digest],[displayorder],[attachment],[closed],[magic],[rate] FROM
-
- [dnt_topics] WHERE [lastpostid] < (SELECT min([lastpostid]) FROM (SELECT TOP ' + STR
-
- ((@pageindex-1)*@pagesize-@startnum) + ' [lastpostid] FROM [dnt_topics] WHERE [displayorder]>=0' + @condition + ' ORDER BY [tid] ' + @sorttype + ' , [lastpostid] ' + @sorttype + ') AS tblTmp )
-
- AND [displayorder]>=0' + @condition +' ORDER BY [lastpostid] '+@sorttype +', [tid] '+@sorttype
- END
- ELSE
- BEGIN
- SET @strSQL = 'SELECT TOP ' + STR(@pagesize) +' [tid],[iconid],[typeid],[title],[price],[hide],[readperm],
-
- [special],[poster],[posterid],[replies],[views],[postdatetime],[lastpost],[lastposter],
-
- [lastpostid],[lastposterid],[replies],[highlight],[digest],[displayorder],[attachment],[closed],[magic] FROM
-
- [dnt_topics] WHERE [lastpostid] > (SELECT MAX([lastpostid]) FROM (SELECT TOP ' + STR
-
- ((@pageindex-1)*@pagesize-@startnum) + ' [lastpostid] FROM [dnt_topics] WHERE [displayorder]>=0' + @condition + ' ORDER BY [tid] ' + @sorttype + ' , [lastpostid] ' + @sorttype + ') AS tblTmp )
-
- AND [displayorder]>=0' + @condition +' ORDER BY [lastpostid] '+@sorttype +', [tid] '+@sorttype
- END
- END
- EXEC(@strSQL)
- GO
- IF OBJECT_ID('[dnt_gettopiclistbytypedate]','P') IS NOT NULL
- DROP PROC [dnt_gettopiclistbytypedate]
- GO
- CREATE PROCEDURE [dnt_gettopiclistbytypedate]
- @pagesize int,
- @pageindex int,
- @startnum int,
- @condition varchar(1000),
- @orderby varchar(100),
- @ascdesc int
- AS
- DECLARE @strsql varchar(5000)
- DECLARE @sorttype varchar(5)
- IF @ascdesc=0
- SET @sorttype='ASC'
- ELSE
- SET @sorttype='DESC'
- IF @pageindex = 1
- BEGIN
- SET @strsql = 'SELECT TOP ' + STR(@pagesize) +' [tid],[iconid],[typeid],[title],[special],[price],[hide],[readperm],[poster],[posterid],[replies],[views],[postdatetime],[lastpost],[lastposter],[lastpostid],[lastposterid],[highlight],[digest],[displayorder],[closed],[attachment],[magic],[rate] FROM [dnt_topics] WHERE [displayorder]>=0'+@condition+' ORDER BY '+@orderby+' '+@sorttype
- END
- ELSE
- IF @sorttype='DESC'
- BEGIN
- SET @strSQL = 'SELECT TOP ' + STR(@pagesize) +' [tid],[iconid],[typeid],[title],[special],[price],[hide],[readperm],[poster],[posterid],[replies],[views],[postdatetime],[lastpost],[lastposter],[lastpostid],[lastposterid],[highlight],[digest],[displayorder],[closed],[attachment],[magic],[rate] FROM [dnt_topics] WHERE ['+@orderby+'] < (SELECT min(['+@orderby+']) FROM (SELECT TOP ' + STR((@pageindex-1)*@pagesize-@startnum) + ' ['+@orderby+'] FROM [dnt_topics] WHERE [displayorder]>=0'+@condition+' ORDER BY '+@orderby+' '+@sorttype+')AS tblTmp ) AND [displayorder]>=0'+@condition+' ORDER BY '+@orderby+' '+@sorttype
- END
- ELSE
- BEGIN
- SET @strSQL = 'SELECT TOP ' + STR(@pagesize) +' [tid],[iconid],[typeid],[title],[special],[price],[hide],[readperm],[poster],[posterid],[replies],[views],[postdatetime],[lastpost],[lastposter],[lastpostid],[lastposterid],[highlight],[digest],[displayorder],[closed],[attachment],[magic],[rate] FROM [dnt_topics] WHERE ['+@orderby+'] > (SELECT MAX(['+@orderby+']) FROM (SELECT TOP ' + STR((@pageindex-1)*@pagesize-@startnum) + ' ['+@orderby+'] FROM [dnt_topics] WHERE [displayorder]>=0'+@condition+' ORDER BY '+@orderby+' '+@sorttype+')AS tblTmp ) AND [displayorder]>=0'+@condition+' ORDER BY '+@orderby+' '+@sorttype
- END
- EXEC(@strsql)
- GO
- IF OBJECT_ID('dnt_gettoptopiclist','P') IS NOT NULL
- DROP PROCEDURE [dnt_gettoptopiclist]
- GO
- CREATE PROCEDURE [dnt_gettoptopiclist]
- @fid int,
- @pagesize int,
- @pageindex int,
- @tids varchar(500)
- AS
- DECLARE @startRow int,
- @endRow int
- SET @startROW = ( @pageindex - 1 ) * @pagesize + 1
- SET @endRow = @startRow + @pagesize -1
- SELECT
- [TOPICS].[rate],
- [TOPICS].[tid],
- [TOPICS].[fid],
- [TOPICS].[typeid],
- [TOPICS].[iconid],
- [TOPICS].[title],
- [TOPICS].[price],
- [TOPICS].[hide],
- [TOPICS].[readperm],
- [TOPICS].[special],
- [TOPICS].[poster],
- [TOPICS].[posterid],
- [TOPICS].[views],
- [TOPICS].[postdatetime],
- [TOPICS].[lastpost],
- [TOPICS].[lastposter],
- [TOPICS].[lastpostid],
- [TOPICS].[lastposterid],
- [TOPICS].[replies],
- [TOPICS].[highlight],
- [TOPICS].[digest],
- [TOPICS].[displayorder],
- [TOPICS].[closed],
- [TOPICS].[attachment],
- [TOPICS].[magic]
- FROM (SELECT ROW_NUMBER() OVER(ORDER BY [dnt_topics].[displayorder] DESC,[dnt_topics].[lastpost] DESC) AS ROWID,
- [dnt_topics].[rate],
- [dnt_topics].[tid],
- [dnt_topics].[fid],
- [dnt_topics].[typeid],
- [dnt_topics].[iconid],
- [dnt_topics].[title],
- [dnt_topics].[price],
- [dnt_topics].[hide],
- [dnt_topics].[readperm],
- [dnt_topics].[special],
- [dnt_topics].[poster],
- [dnt_topics].[posterid],
- [dnt_topics].[views],
- [dnt_topics].[postdatetime],
- [dnt_topics].[lastpost],
- [dnt_topics].[lastposter],
- [dnt_topics].[lastpostid],
- [dnt_topics].[lastposterid],
- [dnt_topics].[replies],
- [dnt_topics].[highlight],
- [dnt_topics].[digest],
- [dnt_topics].[displayorder],
- [dnt_topics].[closed],
- [dnt_topics].[attachment],
- [dnt_topics].[magic]
- FROM [dnt_topics]
- WHERE [displayorder]>0 AND CHARINDEX(','+RTRIM([dnt_topics].[tid])+',', ','+@tids+',')>0)AS TOPICS
- WHERE ROWID BETWEEN @startRow AND @endRow
- GO
- IF OBJECT_ID ('dnt_getuserinfo','P') IS NOT NULL
- DROP PROCEDURE [dnt_getuserinfo]
- GO
- CREATE PROCEDURE [dnt_getuserinfo]
- @uid int
- AS
- SELECT TOP 1
- [dnt_users].[accessmasks],
- [dnt_users].[adminid],
- [dnt_users].[avatarshowid],
- [dnt_users].[bday],
- [dnt_users].[credits],
- [dnt_users].[digestposts],
- [dnt_users].[email],
- [dnt_users].[extcredits1],
- [dnt_users].[extcredits2],
- [dnt_users].[extcredits3],
- [dnt_users].[extcredits4],
- [dnt_users].[extcredits5],
- [dnt_users].[extcredits6],
- [dnt_users].[extcredits7],
- [dnt_users].[extcredits8],
- [dnt_users].[extgroupids],
- [dnt_users].[gender],
- [dnt_users].[groupexpiry],
- [dnt_users].[groupid],
- [dnt_users].[invisible],
- [dnt_users].[joindate],
- [dnt_users].[lastactivity],
- [dnt_users].[lastip],
- [dnt_users].[lastpost],
- [dnt_users].[lastpostid],
- [dnt_users].[lastposttitle],
- [dnt_users].[lastvisit],
- [dnt_users].[newpm],
- [dnt_users].[newpmcount],
- [dnt_users].[newsletter],
- [dnt_users].[nickname],
- [dnt_users].[oltime],
- [dnt_users].[onlinestate],
- [dnt_users].[pageviews],
- [dnt_users].[password],
- [dnt_users].[pmsound],
- [dnt_users].[posts],
- [dnt_users].[ppp],
- [dnt_users].[regip],
- [dnt_users].[secques],
- [dnt_users].[showemail],
- [dnt_users].[sigstatus],
- [dnt_users].[spaceid],
- [dnt_users].[templateid],
- [dnt_users].[tpp],
- [dnt_users].[uid],
- [dnt_users].[username],
- [dnt_users].[salt],
- [dnt_userfields].[authflag],
- [dnt_userfields].[authstr],
- [dnt_userfields].[authtime],
- [dnt_userfields].[avatar],
- [dnt_userfields].[avatarheight],
- [dnt_userfields].[avatarwidth],
- [dnt_userfields].[bio],
- [dnt_userfields].[customstatus],
- [dnt_userfields].[icq],
- [dnt_userfields].[idcard],
- [dnt_userfields].[ignorepm],
- [dnt_userfields].[location],
- [dnt_userfields].[medals],
- [dnt_userfields].[mobile],
- [dnt_userfields].[msn],
- [dnt_userfields].[phone],
- [dnt_userfields].[qq],
- [dnt_userfields].[realname],
- [dnt_userfields].[sightml],
- [dnt_userfields].[signature],
- [dnt_userfields].[skype],
- [dnt_userfields].[uid],
- [dnt_userfields].[website],
- [dnt_userfields].[yahoo]
- FROM [dnt_users] LEFT JOIN [dnt_userfields] ON [dnt_users].[uid]=[dnt_userfields].[uid]
- WHERE [dnt_users].[uid]=@uid
- GO
- IF OBJECT_ID('dnt_getuserlist','P') IS NOT NULL
- DROP PROCEDURE [dnt_getuserlist]
- GO
- CREATE PROCEDURE [dnt_getuserlist]
- @pagesize int,
- @pageindex int,
- @column varchar(20),
- @ordertype AS varchar(5)
- AS
- DECLARE @startRow int,
- @endRow int
- SET @startRow = (@pageindex - 1) * @pagesize + 1
- SET @endRow = @startRow + @pagesize - 1
- IF (@ordertype = 'DESC')
- BEGIN
- IF (@column = 'username')
- BEGIN
- SELECT
- [USERS].[uid],
- [USERS].[groupid],
- [USERS].[username],
- [USERS].[nickname],
- [USERS].[joindate],
- [USERS].[credits],
- [USERS].[posts],
- [USERS].[email],
- [USERS].[lastactivity],
- [USERS].[oltime],
- [USERS].[location]
- FROM (SELECT ROW_NUMBER() OVER(ORDER BY [dnt_users].[username] DESC ) AS ROWID,
- [dnt_users].[uid],
- [dnt_users].[groupid],
- [dnt_users].[username],
- [dnt_users].[nickname],
- [dnt_users].[joindate],
- [dnt_users].[credits],
- [dnt_users].[posts],
- [dnt_users].[email],
- [dnt_users].[lastactivity],
- [dnt_users].[oltime],
- [dnt_userfields].[location]
- FROM [dnt_users]
- LEFT JOIN [dnt_userfields] ON [dnt_userfields].[uid] = [dnt_users].[uid]) AS USERS
- WHERE ROWID BETWEEN @startRow AND @endRow
- END
- ELSE IF (@column = 'credits')
- BEGIN
- SELECT
- [USERS].[uid],
- [USERS].[groupid],
- [USERS].[username],
- [USERS].[nickname],
- [USERS].[joindate],
- [USERS].[credits],
- [USERS].[posts],
- [USERS].[email],
- [USERS].[lastactivity],
- [USERS].[oltime],
- [USERS].[location]
- FROM (SELECT ROW_NUMBER() OVER(ORDER BY [dnt_users].[credits] DESC ) AS ROWID,
- [dnt_users].[uid],
- [dnt_users].[groupid],
- [dnt_users].[username],
- [dnt_users].[nickname],
- [dnt_users].[joindate],
- [dnt_users].[credits],
- [dnt_users].[posts],
- [dnt_users].[email],
- [dnt_users].[lastactivity],
- [dnt_users].[oltime],
- [dnt_userfields].[location]
- FROM [dnt_users]
- LEFT JOIN [dnt_userfields] ON [dnt_userfields].[uid] = [dnt_users].[uid]) AS USERS
- WHERE ROWID BETWEEN @startRow AND @endRow
- END
- ELSE IF (@column = 'posts')
- BEGIN
- SELECT
- [USERS].[uid],
- [USERS].[groupid],
- [USERS].[username],
- [USERS].[nickname],
- [USERS].[joindate],
- [USERS].[credits],
- [USERS].[posts],
- [USERS].[email],
- [USERS].[lastactivity],
- [USERS].[oltime],
- [USERS].[location]
- FROM (SELECT ROW_NUMBER() OVER(ORDER BY [dnt_users].[posts] DESC ) AS ROWID,
- [dnt_users].[uid],
- [dnt_users].[groupid],
- [dnt_users].[username],
- [dnt_users].[nickname],
- [dnt_users].[joindate],
- [dnt_users].[credits],
- [dnt_users].[posts],
- [dnt_users].[email],
- [dnt_users].[lastactivity],
- [dnt_users].[oltime],
- [dnt_userfields].[location]
- FROM [dnt_users]
- LEFT JOIN [dnt_userfields] ON [dnt_userfields].[uid] = [dnt_users].[uid]) AS USERS
- WHERE ROWID BETWEEN @startRow AND @endRow
- END
- ELSE IF (@column = 'admin')
- BEGIN
- SELECT
- [USERS].[uid],
- [USERS].[groupid],
- [USERS].[username],
- [USERS].[nickname],
- [USERS].[joindate],
- [USERS].[credits],
- [USERS].[posts],
- [USERS].[email],
- [USERS].[lastactivity],
- [USERS].[oltime],
- [USERS].[location]
- FROM (SELECT ROW_NUMBER() OVER(ORDER BY [dnt_users].[adminid] ASC ) AS ROWID,
- [dnt_users].[uid],
- [dnt_users].[groupid],
- [dnt_users].[username],
- [dnt_users].[nickname],
- [dnt_users].[joindate],
- [dnt_users].[credits],
- [dnt_users].[posts],
- [dnt_users].[email],
- [dnt_users].[lastactivity],
- [dnt_users].[oltime],
- [dnt_userfields].[location]
- FROM [dnt_users]
- LEFT JOIN [dnt_userfields] ON [dnt_userfields].[uid] = [dnt_users].[uid] WHERE [dnt_users].[adminid] > 0) AS USERS
- WHERE ROWID BETWEEN @startRow AND @endRow
- END
- ELSE IF (@column = 'lastactivity')
- BEGIN
- SELECT
- [USERS].[uid],
- [USERS].[groupid],
- [USERS].[username],
- [USERS].[nickname],
- [USERS].[joindate],
- [USERS].[credits],
- [USERS].[posts],
- [USERS].[email],
- [USERS].[lastactivity],
- [USERS].[oltime],
- [USERS].[location]
- FROM (SELECT ROW_NUMBER() OVER(ORDER BY [dnt_users].[lastactivity] DESC ) AS ROWID,
- [dnt_users].[uid],
- [dnt_users].[groupid],
- [dnt_users].[username],
- [dnt_users].[nickname],
- [dnt_users].[joindate],
- [dnt_users].[credits],
- [dnt_users].[posts],
- [dnt_users].[email],
- [dnt_users].[lastactivity],
- [dnt_users].[oltime],
- [dnt_userfields].[location]
- FROM [dnt_users]
- LEFT JOIN [dnt_userfields] ON [dnt_userfields].[uid] = [dnt_users].[uid]) AS USERS
- WHERE ROWID BETWEEN @startRow AND @endRow
- END
- ELSE IF (@column = 'joindate')
- BEGIN
- SELECT
- [USERS].[uid],
- [USERS].[groupid],
- [USERS].[username],
- [USERS].[nickname],
- [USERS].[joindate],
- [USERS].[credits],
- [USERS].[posts],
- [USERS].[email],
- [USERS].[lastactivity],
- [USERS].[oltime],
- [USERS].[location]
- FROM (SELECT ROW_NUMBER() OVER(ORDER BY [dnt_users].[joindate] DESC ) AS ROWID,
- [dnt_users].[uid],
- [dnt_users].[groupid],
- [dnt_users].[username],
- [dnt_users].[nickname],
- [dnt_users].[joindate],
- [dnt_users].[credits],
- [dnt_users].[posts],
- [dnt_users].[email],
- [dnt_users].[lastactivity],
- [dnt_users].[oltime],
- [dnt_userfields].[location]
- FROM [dnt_users]
- LEFT JOIN [dnt_userfields] ON [dnt_userfields].[uid] = [dnt_users].[uid]) AS USERS
- WHERE ROWID BETWEEN @startRow AND @endRow
- END
- ELSE IF (@column = 'oltime')
- BEGIN
- SELECT
- [USERS].[uid],
- [USERS].[groupid],
- [USERS].[username],
- [USERS].[nickname],
- [USERS].[joindate],
- [USERS].[credits],
- [USERS].[posts],
- [USERS].[email],
- [USERS].[lastactivity],
- [USERS].[oltime],
- [USERS].[location]
- FROM (SELECT ROW_NUMBER() OVER(ORDER BY [dnt_users].[oltime] DESC ) AS ROWID,
- [dnt_users].[uid],
- [dnt_users].[groupid],
- [dnt_users].[username],
- [dnt_users].[nickname],
- [dnt_users].[joindate],
- [dnt_users].[credits],
- [dnt_users].[posts],
- [dnt_users].[email],
- [dnt_users].[lastactivity],
- [dnt_users].[oltime],
- [dnt_userfields].[location]
- FROM [dnt_users]
- LEFT JOIN [dnt_userfields] ON [dnt_userfields].[uid] = [dnt_users].[uid]) AS USERS
- WHERE ROWID BETWEEN @startRow AND @endRow
- END
- ELSE
- BEGIN
- SELECT
- [USERS].[uid],
- [USERS].[groupid],
- [USERS].[username],
- [USERS].[nickname],
- [USERS].[joindate],
- [USERS].[credits],
- [USERS].[posts],
- [USERS].[email],
- [USERS].[lastactivity],
- [USERS].[oltime],
- [USERS].[location]
- FROM (SELECT ROW_NUMBER() OVER(ORDER BY [dnt_users].[uid] DESC ) AS ROWID,
- [dnt_users].[uid],
- [dnt_users].[groupid],
- [dnt_users].[username],
- [dnt_users].[nickname],
- [dnt_users].[joindate],
- [dnt_users].[credits],
- [dnt_users].[posts],
- [dnt_users].[email],
- [dnt_users].[lastactivity],
- [dnt_users].[oltime],
- [dnt_userfields].[location]
- FROM [dnt_users]
- LEFT JOIN [dnt_userfields] ON [dnt_userfields].[uid] = [dnt_users].[uid]) AS USERS
- WHERE ROWID BETWEEN @startRow AND @endRow
- END
- END
- ELSE
- BEGIN
- IF (@column = 'username')
- BEGIN
- SELECT
- [USERS].[uid],
- [USERS].[groupid],
- [USERS].[username],
- [USERS].[nickname],
- [USERS].[joindate],
- [USERS].[credits],
- [USERS].[posts],
- [USERS].[email],
- [USERS].[lastactivity],
- [USERS].[oltime],
- [USERS].[location]
- FROM (SELECT ROW_NUMBER() OVER(ORDER BY [dnt_users].[username] ASC ) AS ROWID,
- [dnt_users].[uid],
- [dnt_users].[groupid],
- [dnt_users].[username],
- [dnt_users].[nickname],
- [dnt_users].[joindate],
- [dnt_users].[credits],
- [dnt_users].[posts],
- [dnt_users].[email],
- [dnt_users].[lastactivity],
- [dnt_users].[oltime],
- [dnt_userfields].[location]
- FROM [dnt_users]
- LEFT JOIN [dnt_userfields] ON [dnt_userfields].[uid] = [dnt_users].[uid]) AS USERS
- WHERE ROWID BETWEEN @startRow AND @endRow
- END
- ELSE IF (@column = 'credits')
- BEGIN
- SELECT
- [USERS].[uid],
- [USERS].[groupid],
- [USERS].[username],
- [USERS].[nickname],
- [USERS].[joindate],
- [USERS].[credits],
- [USERS].[posts],
- [USERS].[email],
- [USERS].[lastactivity],
- [USERS].[oltime],
- [USERS].[location]
- FROM (SELECT ROW_NUMBER() OVER(ORDER BY [dnt_users].[credits] ASC ) AS ROWID,
- [dnt_users].[uid],
- [dnt_users].[groupid],
- [dnt_users].[username],
- [dnt_users].[nickname],
- [dnt_users].[joindate],
- [dnt_users].[credits],
- [dnt_users].[posts],
- [dnt_users].[email],
- [dnt_users].[lastactivity],
- [dnt_users].[oltime],
- [dnt_userfields].[location]
- FROM [dnt_users]
- LEFT JOIN [dnt_userfields] ON [dnt_userfields].[uid] = [dnt_users].[uid]) AS USERS
- WHERE ROWID BETWEEN @startRow AND @endRow
- END
- ELSE IF (@column = 'posts')
- BEGIN
- SELECT
- [USERS].[uid],
- [USERS].[groupid],
- [USERS].[username],
- [USERS].[nickname],
- [USERS].[joindate],
- [USERS].[credits],
- [USERS].[posts],
- [USERS].[email],
- [USERS].[lastactivity],
- [USERS].[oltime],
- [USERS].[location]
- FROM (SELECT ROW_NUMBER() OVER(ORDER BY [dnt_users].[posts] ASC ) AS ROWID,
- [dnt_users].[uid],
- [dnt_users].[groupid],
- [dnt_users].[username],
- [dnt_users].[nickname],
- [dnt_users].[joindate],
- [dnt_users].[credits],
- [dnt_users].[posts],
- [dnt_users].[email],
- [dnt_users].[lastactivity],
- [dnt_users].[oltime],
- [dnt_userfields].[location]
- FROM [dnt_users]
- LEFT JOIN [dnt_userfields] ON [dnt_userfields].[uid] = [dnt_users].[uid]) AS USERS
- WHERE ROWID BETWEEN @startRow AND @endRow
- END
- ELSE IF (@column = 'admin')
- BEGIN
- SELECT
- [USERS].[uid],
- [USERS].[groupid],
- [USERS].[username],
- [USERS].[nickname],
- [USERS].[joindate],
- [USERS].[credits],
- [USERS].[posts],
- [USERS].[email],
- [USERS].[lastactivity],
- [USERS].[oltime],
- [USERS].[location]
- FROM (SELECT ROW_NUMBER() OVER(ORDER BY [dnt_users].[adminid] DESC ) AS ROWID,
- [dnt_users].[uid],
- [dnt_users].[groupid],
- [dnt_users].[username],
- [dnt_users].[nickname],
- [dnt_users].[joindate],
- [dnt_users].[credits],
- [dnt_users].[posts],
- [dnt_users].[email],
- [dnt_users].[lastactivity],
- [dnt_users].[oltime],
- [dnt_userfields].[location]
- FROM [dnt_users]
- LEFT JOIN [dnt_userfields] ON [dnt_userfields].[uid] = [dnt_users].[uid] WHERE [dnt_users].[adminid] > 0) AS USERS
- WHERE ROWID BETWEEN @startRow AND @endRow
- END
- ELSE IF (@column = 'lastactivity')
- BEGIN
- SELECT
- [USERS].[uid],
- [USERS].[groupid],
- [USERS].[username],
- [USERS].[nickname],
- [USERS].[joindate],
- [USERS].[credits],
- [USERS].[posts],
- [USERS].[email],
- [USERS].[lastactivity],
- [USERS].[oltime],
- [USERS].[location]
- FROM (SELECT ROW_NUMBER() OVER(ORDER BY [dnt_users].[lastactivity] ASC ) AS ROWID,
- [dnt_users].[uid],
- [dnt_users].[groupid],
- [dnt_users].[username],
- [dnt_users].[nickname],
- [dnt_users].[joindate],
- [dnt_users].[credits],
- [dnt_users].[posts],
- [dnt_users].[email],
- [dnt_users].[lastactivity],
- [dnt_users].[oltime],
- [dnt_userfields].[location]
- FROM [dnt_users]
- LEFT JOIN [dnt_userfields] ON [dnt_userfields].[uid] = [dnt_users].[uid]) AS USERS
- WHERE ROWID BETWEEN @startRow AND @endRow
- END
- ELSE IF (@column = 'joindate')
- BEGIN
- SELECT
- [USERS].[uid],
- [USERS].[groupid],
- [USERS].[username],
- [USERS].[nickname],
- [USERS].[joindate],
- [USERS].[credits],
- [USERS].[posts],
- [USERS].[email],
- [USERS].[lastactivity],
- [USERS].[oltime],
- [USERS].[location]
- FROM (SELECT ROW_NUMBER() OVER(ORDER BY [dnt_users].[joindate] ASC ) AS ROWID,
- [dnt_users].[uid],
- [dnt_users].[groupid],
- [dnt_users].[username],
- [dnt_users].[nickname],
- [dnt_users].[joindate],
- [dnt_users].[credits],
- [dnt_users].[posts],
- [dnt_users].[email],
- [dnt_users].[lastactivity],
- [dnt_users].[oltime],
- [dnt_userfields].[location]
- FROM [dnt_users]
- LEFT JOIN [dnt_userfields] ON [dnt_userfields].[uid] = [dnt_users].[uid]) AS USERS
- WHERE ROWID BETWEEN @startRow AND @endRow
- END
- ELSE IF (@column = 'oltime')
- BEGIN
- SELECT
- [USERS].[uid],
- [USERS].[groupid],
- [USERS].[username],
- [USERS].[nickname],
- [USERS].[joindate],
- [USERS].[credits],
- [USERS].[posts],
- [USERS].[email],
- [USERS].[lastactivity],
- [USERS].[oltime],
- [USERS].[location]
- FROM (SELECT ROW_NUMBER() OVER(ORDER BY [dnt_users].[oltime] ASC ) AS ROWID,
- [dnt_users].[uid],
- [dnt_users].[groupid],
- [dnt_users].[username],
- [dnt_users].[nickname],
- [dnt_users].[joindate],
- [dnt_users].[credits],
- [dnt_users].[posts],
- [dnt_users].[email],
- [dnt_users].[lastactivity],
- [dnt_users].[oltime],
- [dnt_userfields].[location]
- FROM [dnt_users]
- LEFT JOIN [dnt_userfields] ON [dnt_userfields].[uid] = [dnt_users].[uid]) AS USERS
- WHERE ROWID BETWEEN @startRow AND @endRow
- END
- ELSE
- BEGIN
- SELECT
- [USERS].[uid],
- [USERS].[groupid],
- [USERS].[username],
- [USERS].[nickname],
- [USERS].[joindate],
- [USERS].[credits],
- [USERS].[posts],
- [USERS].[email],
- [USERS].[lastactivity],
- [USERS].[oltime],
- [USERS].[location]
- FROM (SELECT ROW_NUMBER() OVER(ORDER BY [dnt_users].[uid] ASC ) AS ROWID,
- [dnt_users].[uid],
- [dnt_users].[groupid],
- [dnt_users].[username],
- [dnt_users].[nickname],
- [dnt_users].[joindate],
- [dnt_users].[credits],
- [dnt_users].[posts],
- [dnt_users].[email],
- [dnt_users].[lastactivity],
- [dnt_users].[oltime],
- [dnt_userfields].[location]
- FROM [dnt_users]
- LEFT JOIN [dnt_userfields] ON [dnt_userfields].[uid] = [dnt_users].[uid]) AS USERS
- WHERE ROWID BETWEEN @startRow AND @endRow
- END
- END
- GO
- IF OBJECT_ID('[dnt_neatenrelatetopic]','P') IS NOT NULL
- DROP PROC [dnt_neatenrelatetopic]
- GO
- CREATE PROCEDURE [dnt_neatenrelatetopic]
- AS
- BEGIN
- DECLARE @tagid int
- DECLARE [tag_cursor] CURSOR FOR
- SELECT DISTINCT [tagid] FROM [dnt_topictags]
- OPEN [tag_cursor]
- FETCH NEXT FROM [tag_cursor] INTO @tagid
- WHILE @@FETCH_STATUS = 0
- BEGIN
- INSERT INTO [dnt_topictagcaches]
- SELECT [t1].[tid],[t2].[tid],[t2].[title] FROM (SELECT [tid] FROM [dnt_topictags]
- WHERE [tagid] = @tagid) AS [t1],(SELECT [t].[tid],[t].[title] FROM [dnt_topics] AS [t],[dnt_topictags] AS [tt]
- WHERE [tt].[tagid] = @tagid AND [t].[tid] = [tt].[tid] AND [t].[displayorder] >=0) AS [t2]
- WHERE [t1].[tid] <> [t2].[tid] AND NOT EXISTS (SELECT 1 FROM [dnt_topictagcaches] WHERE [tid]=[t1].[tid] AND [linktid]=[t2].[tid])
-
- FETCH NEXT FROM [tag_cursor] INTO @tagid
- END
- CLOSE [tag_cursor]
- DEALLOCATE [tag_cursor]
- END
- GO
- IF OBJECT_ID('[dnt_revisedebatetopicdiggs]','P') IS NOT NULL
- DROP PROC [dnt_revisedebatetopicdiggs]
- GO
- CREATE PROCEDURE [dnt_revisedebatetopicdiggs]
- @tid int,
- @opinion int,
- @count int out
- AS
- BEGIN
- SELECT @count=COUNT(1) FROM [dnt_postdebatefields] WHERE [tid] = @tid AND [opinion] = @opinion
-
- IF @opinion=1
- BEGIN
- UPDATE [dnt_debates] SET [positivediggs]=(SELECT SUM(diggs + 1) FROM [dnt_postdebatefields] WHERE [tid] = @tid AND [opinion] = @opinion) WHERE [tid] = @tid
- END
- ELSE
- BEGIN
- UPDATE [dnt_debates] SET [negativediggs]=(SELECT SUM(diggs + 1) FROM [dnt_postdebatefields] WHERE [tid] = @tid AND [opinion] = @opinion) WHERE [tid] = @tid
- END
- END
- GO
- IF OBJECT_ID('[dnt_setlastexecutescheduledeventdatetime]','P') IS NOT NULL
- DROP PROC [dnt_setlastexecutescheduledeventdatetime]
- GO
- CREATE PROCEDURE [dnt_setlastexecutescheduledeventdatetime]
- (
- @key VARCHAR(100),
- @servername VARCHAR(100),
- @lastexecuted DATETIME
- )
- AS
- DELETE FROM [dnt_scheduledevents] WHERE ([key]=@key) AND ([lastexecuted] < DATEADD([day], - 7, GETDATE()))
- INSERT [dnt_scheduledevents] ([key], [servername], [lastexecuted]) Values (@key, @servername, @lastexecuted)
- GO
- IF OBJECT_ID('[dnt_shrinklog]','P') IS NOT NULL
- DROP PROC [dnt_shrinklog]
- GO
- CREATE PROCEDURE [dnt_shrinklog]
- @DBName nchar(50)
- AS
- Begin
- exec('BACKUP LOG ['+@DBName+'] WITH NO_LOG')
- exec('DBCC SHRINKDATABASE(['+@DBName+'])')
- End
- GO
- IF OBJECT_ID('[dnt_updateadmingroup]','P') IS NOT NULL
- DROP PROC [dnt_updateadmingroup]
- GO
- CREATE PROCEDURE [dnt_updateadmingroup]
- @admingid smallint,
- @alloweditpost tinyint,
- @alloweditpoll tinyint,
- @allowstickthread tinyint,
- @allowmodpost tinyint,
- @allowdelpost tinyint,
- @allowmassprune tinyint,
- @allowrefund tinyint,
- @allowcensorword tinyint,
- @allowviewip tinyint,
- @allowbanip tinyint,
- @allowedituser tinyint,
- @allowmoduser tinyint,
- @allowbanuser tinyint,
- @allowpostannounce tinyint,
- @allowviewlog tinyint,
- @disablepostctrl tinyint,
- @allowviewrealname tinyint
- AS
- UPDATE [dnt_admingroups] SET
- [alloweditpost]=@alloweditpost,
- [alloweditpoll]=@alloweditpoll,
- [allowstickthread]=@allowstickthread,
- [allowmodpost]=@allowmodpost,
- [allowdelpost]=@allowdelpost,
- [allowmassprune]=@allowmassprune,
- [allowrefund]=@allowrefund,
- [allowcensorword]=@allowcensorword,
- [allowviewip]=@allowviewip,
- [allowbanip]=@allowbanip,
- [allowedituser]=@allowedituser,
- [allowmoduser]=@allowmoduser,
- [allowbanuser]=@allowbanuser,
- [allowpostannounce]=@allowpostannounce,
- [allowviewlog]=@allowviewlog,
- [disablepostctrl]=@disablepostctrl,
- [allowviewrealname]=@allowviewrealname
- WHERE [admingid]=@admingid
- GO
- IF OBJECT_ID('[dnt_updatepost1]','P') IS NOT NULL
- DROP PROC [dnt_updatepost1]
- GO
- CREATE PROCEDURE [dnt_updatepost1]
- @pid int,
- @title nvarchar(160),
- @message ntext,
- @lastedit nvarchar(50),
- @invisible int,
- @usesig int,
- @htmlon int,
- @smileyoff int,
- @bbcodeoff int,
- @parseurloff int
- AS
- UPDATE [dnt_posts1] SET
- [title]=@title,
- [message]=@message,
- [lastedit]=@lastedit,
- [invisible]=@invisible,
- [usesig]=@usesig,
- [htmlon]=@htmlon,
- [smileyoff]=@smileyoff,
- [bbcodeoff]=@bbcodeoff,
- [parseurloff]=@parseurloff WHERE [pid]=@pid
- GO
- IF OBJECT_ID('[dnt_updatetopic]','P') IS NOT NULL
- DROP PROC [dnt_updatetopic]
- GO
- CREATE PROCEDURE [dnt_updatetopic]
- @tid int,
- @fid smallint,
- @iconid smallint,
- @title nchar(60),
- @typeid smallint,
- @readperm int,
- @price smallint,
- @poster char(20),
- @posterid int,
- @postdatetime smalldatetime,
- @lastpostid int,
- @lastpost smalldatetime,
- @lastposter char(20),
- @replies int,
- @displayorder int,
- @highlight varchar(500),
- @digest int,
- @rate int,
- @hide int,
- @special int,
- @attachment int,
- @moderated int,
- @closed int,
- @magic int
- AS
- UPDATE [dnt_topics] SET
- [fid]=@fid,
- [iconid]=@iconid,
- [title]=@title,
- [typeid]=@typeid,
- [readperm]=@readperm,
- [price]=@price,
- [poster]=@poster,
- [posterid]=@posterid,
- [postdatetime]=@postdatetime,
- [lastpostid]=@lastpostid,
- [lastpost]=@lastpost,
- [lastposter]=@lastposter,
- [replies]=@replies,
- [displayorder]=@displayorder,
- [highlight]=@highlight,
- [digest]=@digest,
- [rate]=@rate,
- [hide]=@hide,
- [special]=@special,
- [attachment]=@attachment,
- [moderated]=@moderated,
- [closed]=@closed,
- [magic]=@magic WHERE [tid]=@tid
- GO
- IF OBJECT_ID('[dnt_updatetopicviewcount]','P') IS NOT NULL
- DROP PROC [dnt_updatetopicviewcount]
- GO
- CREATE PROCEDURE [dnt_updatetopicviewcount]
- @tid int,
- @viewcount int
- AS
- UPDATE [dnt_topics] SET [views]= [views] + @viewcount WHERE [tid]=@tid
- GO
- IF OBJECT_ID('[dnt_updateuserauthstr]','P') IS NOT NULL
- DROP PROC [dnt_updateuserauthstr]
- GO
- CREATE PROCEDURE [dnt_updateuserauthstr]
- @uid int,
- @authstr char(20),
- @authflag int =1
- AS
- UPDATE [dnt_userfields] SET [authstr]=@authstr, [authtime] = GETDATE(), [authflag]=@authflag WHERE [uid]=@uid
- GO
- IF OBJECT_ID('[dnt_updateuserforumsetting]','P') IS NOT NULL
- DROP PROC [dnt_updateuserforumsetting]
- GO
- CREATE PROCEDURE [dnt_updateuserforumsetting]
- @uid int,
- @tpp int,
- @ppp int,
- @invisible int,
- @customstatus varchar(30)
- AS
- UPDATE [dnt_users] SET [tpp]=@tpp, [ppp]=@ppp, [invisible]=@invisible WHERE [uid]=@uid
- UPDATE [dnt_userfields] SET [customstatus]=@customstatus WHERE [uid]=@uid
- GO
- IF OBJECT_ID('[dnt_updateuserpassword]','P') IS NOT NULL
- DROP PROC [dnt_updateuserpassword]
- GO
- CREATE PROCEDURE [dnt_updateuserpassword]
- @uid int,
- @password char(44)
- AS
- UPDATE [dnt_users] SET [password]=@password WHERE [uid]=@uid
- GO
- IF OBJECT_ID('[dnt_updateuserpreference]','P') IS NOT NULL
- DROP PROC [dnt_updateuserpreference]
- GO
- CREATE PROCEDURE [dnt_updateuserpreference]
- @uid int,
- @avatar varchar(255),
- @avatarwidth int,
- @avatarheight int,
- @templateid int
- AS
- UPDATE [dnt_userfields] SET [avatar]=@avatar, [avatarwidth]=@avatarwidth, [avatarheight]=@avatarheight WHERE [uid]=@uid
- UPDATE [dnt_users] SET [templateid]=@templateid WHERE [uid]=@uid
- GO
- IF OBJECT_ID('[dnt_updateuserprofile]','P') IS NOT NULL
- DROP PROC [dnt_updateuserprofile]
- GO
- CREATE PROCEDURE [dnt_updateuserprofile]
- @uid int,
- @nickname nchar(20),
- @gender int,
- @email char(50),
- @bday char(10),
- @showemail int,
- @website nvarchar(80),
- @icq varchar(12),
- @qq varchar(12),
- @yahoo varchar(40),
- @msn varchar(40),
- @skype varchar(40),
- @location nvarchar(30),
- @bio nvarchar(500),
- @signature nvarchar(500),
- @sigstatus int,
- @sightml nvarchar(1000),
- @realname nvarchar(10),
- @idcard varchar(20),
- @mobile varchar(20),
- @phone varchar(20)
- AS
- UPDATE [dnt_users] SET [nickname]=@nickname, [gender]=@gender , [email]=@email , [bday]=@bday, [sigstatus]=@sigstatus, [showemail]=@showemail WHERE [uid]=@uid
- UPDATE [dnt_userfields] SET [website]=@website , [icq]=@icq , [qq]=@qq , [yahoo]=@yahoo , [msn]=@msn , [skype]=@skype , [location]=@location , [bio]=@bio, [signature]=@signature, [sightml]=@sightml, [realname]=@realname,[idcard]=@idcard,[mobile]=@mobile,[phone]=@phone WHERE [uid]=@uid
- GO
- IF OBJECT_ID('dnt_getforumnewtopics','P') IS NOT NULL
- DROP PROC dnt_getforumnewtopics
- GO
- CREATE PROCEDURE dnt_getforumnewtopics
- @fid int
- AS
- DECLARE @strSQL VARCHAR(5000)
- DECLARE @strMaxPostTableId VARCHAR(3)
- SELECT @strMaxPostTableId=max([id]) FROM [dnt_tablelist]
-
- SET @strSQL = 'SELECT TOP 20 [dnt_topics].[tid],[dnt_topics].[title],[dnt_topics].[poster],[dnt_topics].[postdatetime],[dnt_posts'+@strMaxPostTableId+'].[message] FROM [dnt_topics] LEFT JOIN [dnt_posts'+@strMaxPostTableId+'] ON [dnt_topics].[tid]=[dnt_posts'+@strMaxPostTableId+'].[tid] WHERE [dnt_posts'+@strMaxPostTableId+'].[layer]=0 AND [dnt_topics].[fid]='+LTRIM(STR(@fid))+' ORDER BY [lastpost] DESC'
- EXEC(@strSQL)
- GO
- IF OBJECT_ID('[dnt_createtopictags]','P') IS NOT NULL
- DROP PROC [dnt_createtopictags]
- GO
- CREATE PROCEDURE [dnt_createtopictags]
- @tags nvarchar(55),
- @tid int,
- @userid int,
- @postdatetime datetime
- AS
- BEGIN
- EXEC [dnt_createtags] @tags, @userid, @postdatetime
- UPDATE [dnt_tags] SET [fcount]=[fcount]+1,[count]=[count]+1
- WHERE EXISTS (SELECT [item] FROM [dnt_split](@tags, ' ') AS [newtags] WHERE [newtags].[item] = [tagname])
- INSERT INTO [dnt_topictags] (tagid, tid)
- SELECT tagid, @tid FROM [dnt_tags] WHERE EXISTS (SELECT [item] FROM [dnt_split](@tags, ' ') WHERE [item] = [dnt_tags].[tagname])
- END
- GO
- IF OBJECT_ID('[dnt_getfavoriteslistbyalbum]','P') IS NOT NULL
- DROP PROC [dnt_getfavoriteslistbyalbum]
- GO
- CREATE PROCEDURE [dnt_getfavoriteslistbyalbum]
- @uid int,
- @pagesize int,
- @pageindex int
- AS
- DECLARE @strSQL varchar(5000)
- SET @strSQL='SELECT [f].[tid], [f].[uid], [albumid], [albumcateid], [userid] AS [posterid], [username] AS [poster], [title], [description], [logo], [password], [imgcount], [views], [type], [createdatetime] AS [postdatetime] FROM [dnt_favorites] [f],[dnt_albums] [albums] WHERE [f].[tid]=[albums].[albumid] AND [f].[typeid]=1 AND [f].[uid]=' + STR(@uid)
- IF @pageindex = 1
- BEGIN
- SET @strSQL = 'SELECT TOP ' + STR(@pagesize) +' [tid], [uid], [albumid], [albumcateid], [posterid], [poster], [title], [description], [logo], [password], [imgcount], [views], [type], [postdatetime] FROM (' + @strSQL + ') f' + ' ORDER BY [tid] DESC'
- END
- ELSE
- BEGIN
- SET @strSQL = 'SELECT TOP ' + STR(@pagesize) +' [tid], [uid], [albumid], [albumcateid], [posterid], [poster], [title], [description], [logo], [password], [imgcount], [views], [type], [postdatetime] FROM (' + @strSQL + ') f1 WHERE [tid] < (SELECT MIN([tid]) FROM (SELECT TOP ' + STR((@pageindex-1)*@pagesize) + ' [tid] FROM (' + @strSQL + ') f2' + ' ORDER BY [tid] DESC) AS tblTmp)' + ' ORDER BY [tid] DESC'
- END
- EXEC(@strSQL)
- GO
- IF OBJECT_ID('dnt_getnewtopics1','P') IS NOT NULL
- DROP PROCEDURE [dnt_getnewtopics1]
- GO
- CREATE PROCEDURE [dnt_getnewtopics1]
- @fidlist VARCHAR(500)
- AS
- IF @fidlist <> ''
- BEGIN
- SELECT TOP(20)
- [dnt_posts1].[tid],
- [dnt_posts1].[title],
- [dnt_posts1].[poster],
- [dnt_posts1].[postdatetime],
- [dnt_posts1].[message],
- [dnt_forums].[name]
- FROM [dnt_posts1]
- LEFT JOIN [dnt_forums] ON [dnt_posts1].[fid]=[dnt_forums].[fid]
- LEFT JOIN [dnt_topics] ON [dnt_posts1].[tid]=[dnt_topics].[tid]
- WHERE CHARINDEX(','+RTRIM([dnt_forums].[fid])+',', ','+@fidlist+',') > 0
- AND [dnt_posts1].[layer]=0 AND [dnt_topics].[displayorder] >= 0
- ORDER BY [dnt_posts1].[tid] DESC
- END
- ELSE
- BEGIN
- SELECT TOP(20)
- [dnt_posts1].[tid],
- [dnt_posts1].[title],
- [dnt_posts1].[poster],
- [dnt_posts1].[postdatetime],
- [dnt_posts1].[message],
- [dnt_forums].[name]
- FROM [dnt_posts1]
- LEFT JOIN [dnt_forums] ON [dnt_posts1].[fid]=[dnt_forums].[fid]
- LEFT JOIN [dnt_topics] ON [dnt_posts1].[tid]=[dnt_topics].[tid]
- WHERE [dnt_posts1].[layer]=0 AND [dnt_topics].[displayorder] >= 0
- ORDER BY [dnt_posts1].[tid] DESC
- END
- GO
- IF OBJECT_ID('dnt_getpostlistbycondition1','P') IS NOT NULL
- DROP PROC [dnt_getpostlistbycondition1]
- GO
- CREATE PROCEDURE [dnt_getpostlistbycondition1]
- @tid int,
- @pagesize int,
- @pageindex int,
- @posterid int
- AS
- DECLARE @startRow int,
- @endRow int
- SET @startRow = (@pageindex-1)*@pagesize
- SET @endRow = @startRow + @pagesize - 1
- SELECT
- POSTS.[pid],
- POSTS.[fid],
- POSTS.[title],
- POSTS.[layer],
- POSTS.[message],
- POSTS.[ip],
- POSTS.[lastedit],
- POSTS.[postdatetime],
- POSTS.[attachment],
- POSTS.[poster],
- POSTS.[posterid],
- POSTS.[invisible],
- POSTS.[usesig],
- POSTS.[htmlon],
- POSTS.[smileyoff],
- POSTS.[parseurloff],
- POSTS.[bbcodeoff],
- POSTS.[rate],
- POSTS.[ratetimes],
- POSTS.[nickname],
- POSTS.[username],
- POSTS.[groupid],
- POSTS.[spaceid],
- POSTS.[gender],
- POSTS.[bday],
- POSTS.[email],
- POSTS.[showemail],
- POSTS.[digestposts],
- POSTS.[credits],
- POSTS.[extcredits1],
- POSTS.[extcredits2],
- POSTS.[extcredits3],
- POSTS.[extcredits4],
- POSTS.[extcredits5],
- POSTS.[extcredits6],
- POSTS.[extcredits7],
- POSTS.[extcredits8],
- POSTS.[posts],
- POSTS.[joindate],
- POSTS.[onlinestate],
- POSTS.[lastactivity],
- POSTS.[invisible] AS usersinvisible,
- POSTS.[avatar],
- POSTS.[avatarwidth],
- POSTS.[avatarheight],
- POSTS.[medals],
- POSTS.[signature],
- POSTS.[location],
- POSTS.[customstatus],
- POSTS.[website],
- POSTS.[icq],
- POSTS.[qq],
- POSTS.[msn],
- POSTS.[yahoo],
- POSTS.[oltime],
- POSTS.[lastvisit],
- POSTS.[skype]
- FROM(SELECT ROW_NUMBER() OVER(ORDER BY [pid]) AS ROWID,
- [dnt_posts1].[pid],
- [dnt_posts1].[fid],
- [dnt_posts1].[title],
- [dnt_posts1].[layer],
- [dnt_posts1].[message],
- [dnt_posts1].[ip],
- [dnt_posts1].[lastedit],
- [dnt_posts1].[postdatetime],
- [dnt_posts1].[attachment],
- [dnt_posts1].[poster],
- [dnt_posts1].[posterid],
- [dnt_posts1].[invisible],
- [dnt_posts1].[usesig],
- [dnt_posts1].[htmlon],
- [dnt_posts1].[smileyoff],
- [dnt_posts1].[parseurloff],
- [dnt_posts1].[bbcodeoff],
- [dnt_posts1].[rate],
- [dnt_posts1].[ratetimes],
- [dnt_users].[nickname],
- [dnt_users].[username],
- [dnt_users].[groupid],
- [dnt_users].[spaceid],
- [dnt_users].[gender],
- [dnt_users].[bday],
- [dnt_users].[email],
- [dnt_users].[showemail],
- [dnt_users].[digestposts],
- [dnt_users].[credits],
- [dnt_users].[extcredits1],
- [dnt_users].[extcredits2],
- [dnt_users].[extcredits3],
- [dnt_users].[extcredits4],
- [dnt_users].[extcredits5],
- [dnt_users].[extcredits6],
- [dnt_users].[extcredits7],
- [dnt_users].[extcredits8],
- [dnt_users].[posts],
- [dnt_users].[joindate],
- [dnt_users].[onlinestate],
- [dnt_users].[lastactivity],
- [dnt_users].[oltime],
- [dnt_users].[lastvisit],
- [dnt_users].[invisible] AS usersinvisible,
- [dnt_userfields].[avatar],
- [dnt_userfields].[avatarwidth],
- [dnt_userfields].[avatarheight],
- [dnt_userfields].[medals],
- [dnt_userfields].[sightml] AS signature,
- [dnt_userfields].[location],
- [dnt_userfields].[customstatus],
- [dnt_userfields].[website],
- [dnt_userfields].[icq],
- [dnt_userfields].[qq],
- [dnt_userfields].[msn],
- [dnt_userfields].[yahoo],
- [dnt_userfields].[skype]
- FROM [dnt_posts1]
- LEFT JOIN [dnt_users] ON [dnt_users].[uid]=[dnt_posts1].[posterid]
- LEFT JOIN [dnt_userfields] ON [dnt_userfields].[uid]=[dnt_users].[uid]
- WHERE [dnt_posts1].[tid]=@tid AND [dnt_posts1].[invisible]=0 AND [posterid]=@posterid) AS POSTS
- WHERE ROWID BETWEEN @startROW AND @endROW
- GO
- IF OBJECT_ID('[dnt_getattentiontopics]','P') IS NOT NULL
- DROP PROC [dnt_getattentiontopics]
- GO
- CREATE PROCEDURE [dnt_getattentiontopics]
- @fid varchar(255)='',
- @tpp int,
- @pageid int,
- @condition nvarchar(255)=''
- AS
- DECLARE @pagetop int,@strSQL varchar(5000)
- SET @pagetop = (@pageid-1)*@tpp
- IF @pageid = 1
- BEGIN
- SET @strSQL = 'SELECT TOP ' +STR( @tpp) + ' * FROM [dnt_topics] WHERE [displayorder]>=0 AND [attention]=1'
-
- IF @fid<>'0'
- SELECT @strSQL=@strSQL+' AND [fid] IN ('+@fid+')'
- IF @condition<>''
- SELECT @strSQL=@strSQL+@condition
- SELECT @strSQL=@strSQL+' ORDER BY [lastpost] DESC'
-
-
- END
- ELSE
- BEGIN
- SET @strSQL = 'SELECT TOP ' +STR( @tpp) + ' * FROM [dnt_topics] WHERE [tid] < (SELECT MIN([tid]) FROM (SELECT TOP '+STR(@pagetop)+' [tid] FROM [dnt_topics] WHERE [displayorder]>=0 AND [attention]=1'
-
- IF @fid<>'0'
-
- SELECT @strSQL=@strSQL+' AND [fid] IN ('+@fid+')'
-
- IF @condition<>''
- SELECT @strSQL=@strSQL+@condition
-
- SELECT @strSQL=@strSQL+' ORDER BY [tid] DESC'
- SELECT @strSQL=@strSQL+' ) AS T) '
- IF @fid<>'0'
-
- SELECT @strSQL=@strSQL+' AND [fid] IN ('+@fid+')'
- IF @condition<>''
- SELECT @strSQL=@strSQL+@condition
- SELECT @strSQL=@strSQL+' AND [displayorder]>=0 AND [attention]=1 ORDER BY [tid] DESC'
-
- END
- EXEC(@strSQL)
- GO
- IF OBJECT_ID('[dnt_updateuser]','P') IS NOT NULL
- DROP PROC [dnt_updateuser]
- GO
- CREATE PROCEDURE [dnt_updateuser]
- @username nchar(20),
- @nickname nchar(20),
- @password char(32),
- @secques char(8),
- @spaceid int,
- @gender int,
- @adminid int,
- @groupid smallint,
- @groupexpiry int,
- @extgroupids char(60),
- @regip char(15),
- @joindate char(19),
- @lastip char(15),
- @lastvisit char(19),
- @lastactivity char(19),
- @lastpost char(19),
- @lastpostid int,
- @lastposttitle nchar(60),
- @posts int,
- @digestposts smallint,
- @oltime int,
- @pageviews int,
- @credits int,
- @extcredits1 float,
- @extcredits2 float,
- @extcredits3 float,
- @extcredits4 float,
- @extcredits5 float,
- @extcredits6 float,
- @extcredits7 float,
- @extcredits8 float,
- @avatarshowid int,
- @email char(50),
- @bday char(19),
- @sigstatus int,
- @tpp int,
- @ppp int,
- @templateid smallint,
- @pmsound int,
- @showemail int,
- @newsletter int,
- @invisible int,
- @newpm int,
- @newpmcount int,
- @accessmasks int,
- @onlinestate int,
- @website varchar(80),
- @icq varchar(12),
- @qq varchar(12),
- @yahoo varchar(40),
- @msn varchar(40),
- @skype varchar(40),
- @location nvarchar(30),
- @customstatus varchar(30),
- @avatar varchar(255),
- @avatarwidth int,
- @avatarheight int,
- @medals varchar(300),
- @bio nvarchar(500),
- @signature nvarchar(500),
- @sightml nvarchar(1000),
- @authstr varchar(20),
- @authtime smalldatetime,
- @authflag tinyint,
- @realname nvarchar(10),
- @idcard varchar(20),
- @mobile varchar(20),
- @phone varchar(20),
- @ignorepm nvarchar(1000),
- @uid int
- AS
- UPDATE [dnt_users] SET [username]=@username,[nickname]=@nickname, [password]=@password, [secques]=@secques, [spaceid]=@spaceid, [gender]=@gender, [adminid]=@adminid, [groupid]=@groupid, [groupexpiry]=@groupexpiry,
- [extgroupids]=@extgroupids, [regip]= @regip, [joindate]= @joindate, [lastip]=@lastip, [lastvisit]=@lastvisit, [lastactivity]=@lastactivity, [lastpost]=@lastpost,
- [lastpostid]=@lastpostid, [lastposttitle]=@lastposttitle, [posts]=@posts, [digestposts]=@digestposts, [oltime]=@oltime, [pageviews]=@pageviews, [credits]=@credits,
- [extcredits1]=@extcredits1, [extcredits2]=@extcredits2, [extcredits3]=@extcredits3, [extcredits4]=@extcredits4, [extcredits5]=@extcredits5, [extcredits6]=@extcredits6,
- [extcredits7]=@extcredits7, [extcredits8]=@extcredits8, [avatarshowid]=@avatarshowid, [email]=@email, [bday]=@bday, [sigstatus]=@sigstatus, [tpp]=@tpp, [ppp]=@ppp,
- [templateid]=@templateid, [pmsound]=@pmsound, [showemail]=@showemail, [newsletter]=@newsletter, [invisible]=@invisible, [newpm]=@newpm, [newpmcount]=@newpmcount, [accessmasks]=@accessmasks, [onlinestate]=@onlinestate
- WHERE [uid]=@uid
- UPDATE [dnt_userfields] SET [website]=@website,[icq]=@icq,[qq]=@qq,[yahoo]=@yahoo,[msn]=@msn,[skype]=@skype,[location]=@location,[customstatus]=@customstatus,
- [avatar]=@avatar,[avatarwidth]=@avatarwidth,[avatarheight]=@avatarheight,[medals]=@medals,[bio]=@bio,[signature]=@signature,[sightml]=@sightml,[authstr]=@authstr,
- [authtime]=@authtime,[authflag]=@authflag,[realname]=@realname,[idcard]=@idcard,[mobile]=@mobile,[phone]=@phone,[ignorepm]=@ignorepm
- WHERE [uid]=@uid
- GO
- IF OBJECT_ID('[dnt_split]') IS NOT NULL
- DROP FUNCTION [dnt_split]
- GO
- CREATE FUNCTION [dnt_split]
- (
- @splitstring NVARCHAR(4000),
- @separator CHAR(1) = ','
- )
- RETURNS @splitstringstable TABLE
- (
- [item] NVARCHAR(200)
- )
- AS
- BEGIN
- DECLARE @currentindex INT
- DECLARE @nextindex INT
- DECLARE @returntext NVARCHAR(200)
- SELECT @currentindex=1
- WHILE(@currentindex<=datalength(@splitstring)/2)
- BEGIN
- SELECT @nextindex=charindex(@separator,@splitstring,@currentindex)
- IF(@nextindex=0 OR @nextindex IS NULL)
- SELECT @nextindex=datalength(@splitstring)/2+1
-
- SELECT @returntext=substring(@splitstring,@currentindex,@nextindex-@currentindex)
- INSERT INTO @splitstringstable([item])
- VALUES(@returntext)
-
- SELECT @currentindex=@nextindex+1
- END
- RETURN
- END
- GO
- if exists (select * from sysobjects where id = object_id(N'[dnt_getindexforumlist]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
- drop procedure [dnt_getindexforumlist]
- GO
- if exists (select * from sysobjects where id = object_id(N'[dnt_getonlineuser]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
- drop procedure [dnt_getonlineuser]
- GO
- if exists (select * from sysobjects where id = object_id(N'[dnt_getonlineuserlist]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
- drop procedure [dnt_getonlineuserlist]
- GO
- if exists (select * from sysobjects where id = object_id(N'[dnt_getonlineuserlistbyfid]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
- drop procedure [dnt_getonlineuserlistbyfid]
- GO
- if exists (select * from sysobjects where id = object_id(N'[dnt_getrelatedtopics]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
- drop procedure [dnt_getrelatedtopics]
- GO
- if exists (select * from sysobjects where id = object_id(N'[dnt_gettopicinfo]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
- drop procedure [dnt_gettopicinfo]
- GO
- if exists (select * from sysobjects where id = object_id(N'[dnt_createonlineuser]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
- drop procedure [dnt_createonlineuser]
- GO
- if exists (select * from sysobjects where id = object_id(N'[dnt_deleteonlineusers]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
- drop procedure [dnt_deleteonlineusers]
- GO
- if exists (select * from sysobjects where id = object_id(N'[dnt_updateonlineaction]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
- drop procedure [dnt_updateonlineaction]
- GO
- CREATE PROCEDURE [dnt_updateonlineaction]
- @action smallint,
- @lastupdatetime datetime,