detachtable.config

基于Windows平台的ASP.net框架


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

~

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

~

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

~

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

~

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

~

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

~

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

~

CREATE PROCEDURE dnt_createpost
@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_getfirstpostid
@tid int
AS
SELECT TOP 1 [pid] FROM [dnt_posts1] WHERE [tid]=@tid ORDER BY [pid]



~


CREATE PROCEDURE dnt_getpostcount
@tid int
AS
SELECT COUNT(pid) FROM [dnt_posts1] WHERE [tid]=@tid AND [invisible]=0 AND layer>0

~


CREATE  PROCEDURE dnt_deletepostbypid
@pid int
AS

	DECLARE @fid int
	DECLARE @tid int
	DECLARE @posterid int
	DECLARE @lastforumposterid int
	DECLARE @layer int
	DECLARE @postdatetime varchar(10)
	DECLARE @poster varchar(50)
	DECLARE @postcount int
	DECLARE @title nchar(80)
	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
			
			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,
				[lastactivity] = GETDATE()				
			WHERE [uid] = @posterid

			UPDATE [dnt_topics] SET [replies]=[replies] - 1 WHERE [tid]=@tid
			
			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
			

			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,
				[lastactivity] = GETDATE()						
			WHERE [uid] = @posterid
			

			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], @postdatetime = [postdatetime], @title = [title], @postdatetime = [postdatetime], @poster = [poster] FROM [dnt_posts1] WHERE [fid] = @fid ORDER BY [pid] DESC
			
        
        
			UPDATE [dnt_forums] SET 
			
				[lasttid]=@tid,
				[lasttitle]=ISNULL(@title,''),
				[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], @postdatetime = [postdatetime], @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
~


CREATE PROCEDURE dnt_getposttree
@tid int
AS
SELECT [pid], [layer], [title], [poster], [posterid],[postdatetime],[message] FROM [dnt_posts1] WHERE [tid]=@tid AND [invisible]=0 ORDER BY [parentid];
~




CREATE PROCEDURE dnt_getsinglepost
@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_updatepost
	@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