﻿if exists (select * from sysobjects where id = object_id(N'[dnt_createpost1]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dnt_createpost1]

~

if exists (select * from sysobjects where id = object_id(N'[dnt_getpostlist]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dnt_getpostlist]

~

if exists (select * from sysobjects where id = object_id(N'[dnt_getpostlistbycondition]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dnt_getpostlistbycondition]

~

if exists (select * from sysobjects where id = object_id(N'[dnt_getsinglepost1]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dnt_getsinglepost1]

~
if exists (select * from sysobjects where id = object_id(N'[dnt_gettopiclist]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dnt_gettopiclist]

~

if exists (select * from sysobjects where id = object_id(N'[dnt_gettopiclistbydate]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dnt_gettopiclistbydate]


~

if exists (select * from sysobjects where id = object_id(N'[dnt_gettopiclistbynumber]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dnt_gettopiclistbynumber]


~

if exists (select * from sysobjects where id = object_id(N'[dnt_gettopiclistbytype]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dnt_gettopiclistbytype]


~


if exists (select * from sysobjects where id = object_id(N'[dnt_gettopiclistbytypedate]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dnt_gettopiclistbytypedate]

~

if exists (select * from sysobjects where id = object_id(N'[dnt_gettoptopiclist]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dnt_gettoptopiclist]

~

if exists (select * from sysobjects where id = object_id(N'[dnt_getalltopiccount]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dnt_getalltopiccount]

~
if exists (select * from sysobjects where id = object_id(N'[dnt_gettopiccount]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dnt_gettopiccount]

~
if exists (select * from sysobjects where id = object_id(N'[dnt_updatetopic]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dnt_updatetopic]

~



CREATE PROCEDURE dnt_createpost1
@fid int,
@tid int,
@parentid int,
@layer int,
@poster varchar(20),
@posterid int,
@title nvarchar(80),
@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 + 1 
												 ELSE 1 
												 END,
									[lasttid]=@tid,	
									[lasttitle]=@title,
									[lastpost]=@postdatetime,
									[lastposter]=@poster,
									[lastposterid]=@posterid 
							
							WHERE (CHARINDEX(',' + RTRIM([fid]) + ',', ',' + (SELECT @fidlist AS [fid]) + ',') > 0)
			
			
			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

	END
	
SELECT @postid AS postid



~

CREATE PROCEDURE dnt_getsinglepost1
@pid int
AS
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].[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_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




~

CREATE PROCEDURE dnt_getpostlist
@tid int,
@pagesize int,
@pageindex int,
@posttablename varchar(20)
AS
DECLARE @pagetop int

SET @pagetop = (@pageindex-1)*@pagesize

IF @pageindex = 1

EXEC('SELECT TOP ' + @pagesize + '
									[' + @posttablename + '].[pid], 
									[' + @posttablename + '].[fid], 
									[' + @posttablename + '].[title], 
									[' + @posttablename + '].[layer],
									[' + @posttablename + '].[message], 
									[' + @posttablename + '].[ip], 
									[' + @posttablename + '].[lastedit], 
									[' + @posttablename + '].[postdatetime], 
									[' + @posttablename + '].[attachment], 
									[' + @posttablename + '].[poster], 
									[' + @posttablename + '].[posterid], 
									[' + @posttablename + '].[invisible], 
									[' + @posttablename + '].[usesig], 
									[' + @posttablename + '].[htmlon], 
									[' + @posttablename + '].[smileyoff], 
									[' + @posttablename + '].[parseurloff], 
									[' + @posttablename + '].[bbcodeoff], 
									[' + @posttablename + '].[rate], 
									[' + @posttablename + '].[ratetimes], 
									[dnt_users].[nickname],  
									[dnt_users].[username], 
									[dnt_users].[groupid], 
									[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_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 [' + @posttablename + '] LEFT JOIN [dnt_users] ON [dnt_users].[uid]=[' + @posttablename + '].[posterid] LEFT JOIN [dnt_userfields] ON [dnt_userfields].[uid]=[dnt_users].[uid] WHERE [' + @posttablename + '].[tid]=' + @tid + 'ORDER BY [' + @posttablename + '].[pid]')

ELSE

EXEC('SELECT TOP ' + @pagesize + ' 
									[' + @posttablename + '].[pid], 
									[' + @posttablename + '].[fid], 
									[' + @posttablename + '].[title], 
									[' + @posttablename + '].[layer],
									[' + @posttablename + '].[message], 
									[' + @posttablename + '].[ip], 
									[' + @posttablename + '].[lastedit], 
									[' + @posttablename + '].[postdatetime], 
									[' + @posttablename + '].[attachment], 
									[' + @posttablename + '].[poster], 
									[' + @posttablename + '].[posterid], 
									[' + @posttablename + '].[invisible], 
									[' + @posttablename + '].[usesig], 
									[' + @posttablename + '].[htmlon], 
									[' + @posttablename + '].[smileyoff], 
									[' + @posttablename + '].[parseurloff], 
									[' + @posttablename + '].[bbcodeoff], 
									[' + @posttablename + '].[rate], 
									[' + @posttablename + '].[ratetimes],
									[dnt_users].[nickname],  
									[dnt_users].[username], 
									[dnt_users].[groupid], 
									[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_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 [' + @posttablename + '] LEFT JOIN [dnt_users] ON [dnt_users].[uid]=[' + @posttablename + '].[posterid] LEFT JOIN [dnt_userfields] ON [dnt_userfields].[uid]=[dnt_users].[uid] WHERE [' + @posttablename + '].[tid]=' + @tid + ' AND [pid] > (SELECT MAX([pid]) FROM (SELECT TOP ' + @pagetop + ' [' + @posttablename + '].[pid] FROM [' + @posttablename + '] LEFT JOIN [dnt_users] ON [dnt_users].[uid]=[' + @posttablename + '].[posterid] LEFT JOIN [dnt_userfields] ON [dnt_userfields].[uid]=[dnt_users].[uid] WHERE [' + @posttablename + '].[tid]=' + @tid + ' ORDER BY [' + @posttablename + '].[pid]) AS tblTmp) ORDER BY [' + @posttablename + '].[pid]')




~


CREATE PROCEDURE dnt_getpostlistbycondition
@tid int,
@pagesize int,
@pageindex int,
@condition varchar(100),
@posttablename varchar(20)
AS
DECLARE @pagetop int

SET @pagetop = (@pageindex-1)*@pagesize

IF @pageindex = 1

EXEC('SELECT TOP ' + @pagesize + '
									[' + @posttablename + '].[pid], 
									[' + @posttablename + '].[fid], 
									[' + @posttablename + '].[title], 
									[' + @posttablename + '].[layer],
									[' + @posttablename + '].[message], 
									[' + @posttablename + '].[ip], 
									[' + @posttablename + '].[lastedit], 
									[' + @posttablename + '].[postdatetime], 
									[' + @posttablename + '].[attachment], 
									[' + @posttablename + '].[poster], 
									[' + @posttablename + '].[posterid], 
									[' + @posttablename + '].[invisible], 
									[' + @posttablename + '].[usesig], 
									[' + @posttablename + '].[htmlon], 
									[' + @posttablename + '].[smileyoff], 
									[' + @posttablename + '].[parseurloff], 
									[' + @posttablename + '].[bbcodeoff], 
									[' + @posttablename + '].[rate], 
									[' + @posttablename + '].[ratetimes], 
									[dnt_users].[nickname],  
									[dnt_users].[username], 
									[dnt_users].[groupid], 
									[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_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 [' + @posttablename + '] LEFT JOIN [dnt_users] ON [dnt_users].[uid]=[' + @posttablename + '].[posterid] LEFT JOIN [dnt_userfields] ON [dnt_userfields].[uid]=[dnt_users].[uid] WHERE [' + @posttablename + '].[tid]=' + @tid + ' AND '+@condition+' ORDER BY [' + @posttablename + '].[pid]')

ELSE

EXEC('SELECT TOP ' + @pagesize + ' 
									[' + @posttablename + '].[pid], 
									[' + @posttablename + '].[fid], 
									[' + @posttablename + '].[title], 
									[' + @posttablename + '].[layer],
									[' + @posttablename + '].[message], 
									[' + @posttablename + '].[ip], 
									[' + @posttablename + '].[lastedit], 
									[' + @posttablename + '].[postdatetime], 
									[' + @posttablename + '].[attachment], 
									[' + @posttablename + '].[poster], 
									[' + @posttablename + '].[posterid], 
									[' + @posttablename + '].[invisible], 
									[' + @posttablename + '].[usesig], 
									[' + @posttablename + '].[htmlon], 
									[' + @posttablename + '].[smileyoff], 
									[' + @posttablename + '].[parseurloff], 
									[' + @posttablename + '].[bbcodeoff], 
									[' + @posttablename + '].[rate], 
									[' + @posttablename + '].[ratetimes],
									[dnt_users].[nickname],  
									[dnt_users].[username], 
									[dnt_users].[groupid], 
									[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_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 [' + @posttablename + '] LEFT JOIN [dnt_users] ON [dnt_users].[uid]=[' + @posttablename + '].[posterid] LEFT JOIN [dnt_userfields] ON [dnt_userfields].[uid]=[dnt_users].[uid] WHERE [' + @posttablename + '].[tid]=' + @tid + '  AND '+ @condition+'  AND [pid] > (SELECT MAX([pid]) FROM (SELECT TOP ' + @pagetop + ' [' + @posttablename + '].[pid] FROM [' + @posttablename + '] LEFT JOIN [dnt_users] ON [dnt_users].[uid]=[' + @posttablename + '].[posterid] LEFT JOIN [dnt_userfields] ON [dnt_userfields].[uid]=[dnt_users].[uid] WHERE [' + @posttablename + '].[tid]=' + @tid + '  AND '+@condition+'  ORDER BY [' + @posttablename + '].[pid]) AS tblTmp) ORDER BY [' + @posttablename + '].[pid]')




~




CREATE PROCEDURE dnt_gettoptopiclist
@fid int,
@pagesize int,
@pageindex int,
@tids varchar(500)
AS
DECLARE @strSQL varchar(5000)

IF @pageindex = 1
	BEGIN
		SET @strSQL = 'SELECT TOP ' + STR(@pagesize)+' [tid],[iconid],[title],[price],[hide],[readperm], [poll],[poster],[posterid],[replies],[views],[postdatetime],[lastpost],[lastposter],[lastpostid],[lastposterid],[replies],[highlight],[digest],[displayorder],[closed],[attachment] FROM [dnt_topics] WHERE [displayorder]>0 AND [tid] IN (' + @tids + ') ORDER BY [lastpost] DESC'
	END
ELSE
	BEGIN
		SET @strSQL = 'SELECT TOP ' + STR(@pagesize) + ' [tid], [iconid], [title], [price], [hide], [readperm], [poll], [poster], [posterid], [replies], [views], 
      [postdatetime], [lastpost], [lastposter], [lastpostid], [lastposterid], [replies] AS Expr1, [highlight], 
      [digest], [displayorder], [closed], [attachment] FROM dnt_topics
WHERE (tid not in (SELECT TOP ' + STR((@pageindex-1)*@pagesize) + ' [tid]
                 FROM [dnt_topics]
                 WHERE [displayorder] > 0 AND [tid] IN (' + @tids + ')
                 ORDER BY [lastpost] DESC)) AND (displayorder > 0) AND 
      (tid IN (' + @tids + '))
ORDER BY lastpost DESC'
	
	END

EXEC(@strSQL)











~


CREATE  PROCEDURE dnt_gettopiclistbytypedate
@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],[poll],[price],[hide],[readperm],[poster],[posterid],[replies],[views],[postdatetime],[lastpost],[lastposter],[lastpostid],[lastposterid],[replies],[highlight],[digest],[displayorder],[closed],[attachment] 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],[title],[poll],[price],[hide],[readperm],[poster],[posterid],[replies],[views],[postdatetime],[lastpost],[lastposter],[lastpostid],[lastposterid],[replies],[highlight],[digest],[displayorder],[closed],[attachment] 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],[title],[poll],[price],[hide],[readperm],[poster],[posterid],[replies],[views],[postdatetime],[lastpost],[lastposter],[lastpostid],[lastposterid],[replies],[highlight],[digest],[displayorder],[closed],[attachment] 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)



~



CREATE  PROCEDURE dnt_gettopiclistbytype
@pagesize int,
@pageindex int,
@startnum int,
@condition varchar(80)
AS
DECLARE @strSQL varchar(5000)

IF @pageindex = 1
	BEGIN
		SET @strSQL = 'SELECT TOP ' + STR(@pagesize) +' [tid],[iconid],[title],[price],[hide],[readperm],

[poll],[poster],[posterid],[replies],[views],[postdatetime],[lastpost],[lastposter],

[lastpostid],[lastposterid],[replies],[highlight],[digest],[displayorder],[attachment],[closed] FROM 

[dnt_topics] WHERE  [displayorder]>=0' + @condition + ' ORDER BY [tid] DESC ,  [lastpostid] DESC'
	END
ELSE
	BEGIN
		SET @strSQL = 'SELECT TOP ' + STR(@pagesize) +' [tid],[iconid],[title],[price],[hide],[readperm],

[poll],[poster],[posterid],[replies],[views],[postdatetime],[lastpost],[lastposter],

[lastpostid],[lastposterid],[replies],[highlight],[digest],[displayorder],[attachment],[closed] 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] DESC , [lastpostid] DESC) AS tblTmp ) 

AND  [displayorder]>=0' + @condition +'ORDER BY [tid] DESC ,  [lastpostid] DESC'
	END
EXEC(@strSQL)





~



CREATE PROCEDURE dnt_gettopiclistbynumber
( 
@orderfield varchar(100),
@pageindex int,
@pagesize int=16,
@strwhere varchar(200),
@ordertype bit=1
) 
AS 
Declare @tablename varchar(100);
set @tablename='dnt_topics'
Declare @fieldlist varchar(500);
set  @fieldlist=' [tid],[iconid],[title],[price],[hide],[readperm],[poll],[poster],[posterid],[replies],[views],[postdatetime],[lastpost],[lastposter],[lastpostid],[lastposterid],[replies],[highlight],[digest],[displayorder],[attachment],[closed]'
Declare @keyfield varchar(100);
set  @keyfield=' tid'


 SET NOCOUNT ON 
    declare @sqlstr varchar(6000) 
    set @strwhere=replace(@strwhere,'''','''''') 
    set @strwhere=replace(@strwhere,'--','') 
    set @strwhere=replace(@strwhere,';','') 
    set @sqlstr='declare @CurPageNum int;' 
    set @sqlstr=@sqlstr+'declare @nextpagenum int;' 
    set @sqlstr=@sqlstr+'set @curpagenum='+cast(@PageIndex as varchar)+'*'+cast(@Pagesize as varchar)+';' 
    set @sqlstr=@sqlstr+'set @nextpagenum='+cast(@PageIndex+1 as varchar)+'*'+cast(@Pagesize as varchar)+';' 
    set @sqlstr=@sqlstr+'declare @sqlstr varchar(6000);' 
    if @ordertype=1 
    begin 
    set @sqlstr=@sqlstr+'set @sqlstr=''select '+@fieldlist+' from ( select top ''+cast(@nextpagenum as varchar)+'' * from  
 
'+@tablename+'  where '+@strwhere+' order by '+@orderfield+' desc ) as a where '+@keyfield+' not in (  
 
select top ''+cast(@curpagenum as varchar)+'' '+@keyfield+' from '+@tablename+' where '+@strwhere+'  
 
order by '+@orderfield+' desc) order by '+@orderfield+' desc'';' 
    end 
    else 
    begin 
    set @sqlstr=@sqlstr+'set @sqlstr=''select '+@fieldlist+' from ( select top ''+cast(@nextpagenum as varchar)+'' * from  
 
'+@tablename+'  where '+@strwhere+' order by '+@orderfield+' asc ) as a where '+@keyfield+' not in (  
 
select top ''+cast(@curpagenum as varchar)+'' '+@keyfield+' from '+@tablename+' where '+@strwhere+'  
 
order by '+@orderfield+' asc) order by '+@orderfield+' asc'';' 
    end 
    set @sqlstr=@sqlstr+'execute( @sqlstr)' 
    execute(@sqlstr)



~




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],[readperm],[hide],[poll],[poster],[posterid],[replies],[views],[postdatetime],[lastpost],[lastposter],[lastpostid],[lastposterid],[replies],[highlight],[digest],[displayorder],[attachment],[closed] 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],[hide],[readperm],[poll],[poster],[posterid],[replies],[views],[postdatetime],[lastpost],[lastposter],[lastpostid],[lastposterid],[replies],[highlight],[digest],[displayorder],[attachment],[closed] 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],[readperm],[poll],[poster],[posterid],[replies],[views],[postdatetime],[lastpost],[lastposter],[lastpostid],[lastposterid],[replies],[highlight],[digest],[displayorder],[closed] 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)



~



CREATE PROCEDURE dnt_gettopiclist
@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) +' [tid],[iconid],[title],[price],[hide],[readperm],

[poll],[poster],[posterid],[replies],[views],[postdatetime],[lastpost],[lastposter],

[lastpostid],[lastposterid],[replies],[highlight],[digest],[displayorder],[attachment],[closed] FROM 

[dnt_topics] WHERE [fid]=' +STR(@fid) + ' AND [displayorder]=0' + @condition + ' ORDER BY [lastpostid] DESC'
	END
ELSE
	BEGIN
		SET @strSQL = 'SELECT TOP ' + STR(@pagesize) +' [tid],[iconid],[title],[price],[hide],[readperm],

[poll],[poster],[posterid],[replies],[views],[postdatetime],[lastpost],[lastposter],

[lastpostid],[lastposterid],[replies],[highlight],[digest],[displayorder],[attachment],[closed] 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)


~


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) + ',', ',' + RTRIM(parentidlist) + ',') > 0))  AND [displayorder]>=0


~


CREATE PROCEDURE dnt_gettopiccount
@fid int
AS
SELECT COUNT(tid) FROM [dnt_topics] WHERE [fid]=@fid AND [displayorder]>=0


~

CREATE PROCEDURE dnt_updatetopic
	@tid int,
	@fid smallint,
	@iconid smallint,
	@title nchar(80),
	@typeid smallint,
	@readperm int,
	@price smallint,
	@poster char(20),
	@posterid int,
	@postdatetime smalldatetime,
	@lastpost smalldatetime,
	@lastposter char(20),
	@views int,
	@replies int,
	@displayorder int,
	@highlight varchar(500),
	@digest int,
	@rate int,
	@hide int,
	@poll 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,
	[lastpost]=@lastpost,
	[lastposter]=@lastposter,
	[views]=@views,
	[replies]=@replies,
	[displayorder]=@displayorder,
	[highlight]=@highlight,
	[digest]=@digest,
	[rate]=@rate,
	[hide]=@hide,
	[poll]=@poll,
	[attachment]=@attachment,
	[moderated]=@moderated,
	[closed]=@closed,
	[magic]=@magic WHERE [tid]=@tid
