﻿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_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]

~

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

~

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_getuserlist]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dnt_getuserlist]

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

~	


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

~

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

~

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

~

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

~

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

~

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

~

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],[fid],[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], [fid], [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_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 [curtopics] FROM [dnt_forums] WHERE [fid]=@fid

~

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
	
	
~
CREATE PROCEDURE dnt_gettopiccountbytype
@condition varchar(1000)
AS
DECLARE @sql varchar(1000)

set @sql ='SELECT COUNT(tid) FROM [dnt_topics] WHERE [closed]<=1 '+@condition
exec(@sql)


~
CREATE  PROCEDURE dnt_gettopiclistbytype
@pagesize int,
@pageindex int,
@startnum int,
@condition varchar(1000)
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_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],[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_getuserlist
@pagesize int,
@pageindex int,
@orderby AS varchar(200)
AS
DECLARE @pagetop int
DECLARE @sql varchar(1000)

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

IF @pageindex = 1
	BEGIN
	SET @sql = 'SELECT TOP ' + CAST(@pagesize AS varchar(10)) + ' [dnt_users].[uid], 
									[dnt_users].[username], 
									[dnt_users].[nickname], 
									[dnt_users].[joindate], 
									[dnt_users].[credits], 
									[dnt_users].[posts], 
									[dnt_users].[lastactivity], 
									[dnt_users].[email], 
									[dnt_userfields].[location] 
					FROM [dnt_users] LEFT JOIN [dnt_userfields] ON [dnt_userfields].[uid] = [dnt_users].[uid] ' + @orderby
	END
ELSE
	BEGIN
		SET @sql = 'SELECT TOP ' + CAST(@pagesize AS varchar(10)) + ' [dnt_users].[uid], [dnt_users].[username], [dnt_users].[nickname], [dnt_users].[joindate], [dnt_users].[credits], [dnt_users].[posts], [dnt_users].[email], [dnt_users].[lastactivity], [dnt_userfields].[location] FROM [dnt_users] LEFT JOIN [dnt_userfields] ON [dnt_userfields].[uid] = [dnt_users].[uid] WHERE [dnt_users].[uid] NOT IN (SELECT TOP ' + CAST(@pagetop AS varchar(10)) + ' [uid] FROM [dnt_users]  ' + @orderby + ') '+ replace(@orderby,'WHERE','AND')

	END
	
	print @sql
	EXEC(@sql)
	
	
	~
	
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,
@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

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], [lastposter], [views], [replies], [displayorder], [highlight], [digest], [rate], [hide], [poll], [attachment], [moderated], [closed], [magic]) VALUES(@fid, @iconid, @title, @typeid, @readperm, @price, @poster, @posterid, @postdatetime, @lastpost, @lastposter, @views, @replies, @displayorder, @highlight, @digest, @rate, @hide, @poll, @attachment, @moderated, @closed, @magic)

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
		
	END
	
SELECT @topicid as topicid


~

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

~




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

~


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

~


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

~


CREATE PROCEDURE dnt_getshortuserinfo
@uid int
AS
SELECT TOP 1 * FROM [dnt_users] WHERE [uid]=@uid

~


CREATE PROCEDURE dnt_getuserinfo
@uid int
AS
SELECT TOP 1 [dnt_users].*, [dnt_userfields].* FROM [dnt_users] LEFT JOIN [dnt_userfields] ON [dnt_users].[uid]=[dnt_userfields].[uid] WHERE [dnt_users].[uid]=@uid


