SCSI/ASPI

开发平台：

Others

upgradeprocedure_2000.sql：源码内容

IF @pageindex = 1
EXEC('SELECT TOP ' + @pagesize + '
[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].[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].[tid]=' + @tid + ' AND [dnt_posts1].[invisible]<=0 ORDER BY [dnt_posts1].[pid]')
ELSE
EXEC('SELECT TOP ' + @pagesize + '
[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 [userinvisible],
[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 [pid] > (SELECT MAX([pid]) FROM (SELECT TOP ' + @pagetop + ' [dnt_posts1].[pid] FROM [dnt_posts1] WHERE [dnt_posts1].[tid]=' + @tid + ' AND [dnt_posts1].[invisible]<=0 ORDER BY [dnt_posts1].[pid]) AS tblTmp) AND [dnt_posts1].[invisible]<=0 ORDER BY [dnt_posts1].[pid]')
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 @pagetop int
SET @pagetop = (@pageindex-1)*@pagesize
IF @pageindex = 1
EXEC('SELECT TOP ' + @pagesize + '
[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],
[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+' ORDER BY [dnt_posts1].[pid]')
ELSE
EXEC('SELECT TOP ' + @pagesize + '
[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].[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].[tid]=' + @tid + ' AND [dnt_posts1].[invisible]=0 AND [posterid] = '+@posterid+' AND [pid] > (SELECT MAX([pid]) FROM (SELECT TOP ' + @pagetop + ' [dnt_posts1].[pid] 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 [posterid] = '+@posterid+' ORDER BY [dnt_posts1].[pid]) AS tblTmp) ORDER BY [dnt_posts1].[pid]')
GO
IF OBJECT_ID('dnt_getattachpaymentlogbyaid','P') IS NOT NULL
DROP PROC [dnt_getattachpaymentlogbyaid]
GO
CREATE PROCEDURE [dnt_getattachpaymentlogbyaid]
@aid int
AS
SELECT
[id],
[uid],
[username],
[aid],
[authorid],
[postdatetime],
[amount],
[netamount]
FROM [dnt_attachpaymentlog]
WHERE [aid] = @aid
GO
IF OBJECT_ID('dnt_getonlineuercount','P') IS NOT NULL
DROP PROC [dnt_getonlineuercount]
GO
CREATE PROCEDURE [dnt_getonlineuercount]
AS
SELECT COUNT(olid) FROM [dnt_online]
GO
IF OBJECT_ID('dnt_getalltoptopiclist','P') IS NOT NULL
DROP PROC [dnt_getalltoptopiclist]
GO
CREATE PROCEDURE [dnt_getalltoptopiclist]
AS
SELECT
[tid],
[displayorder],
[fid]
FROM [dnt_topics]
WHERE [displayorder] > 0 ORDER BY [fid]
GO
IF OBJECT_ID('dnt_getnousedforumattachment','P') IS NOT NULL
DROP PROC [dnt_getnousedforumattachment]
GO
CREATE PROCEDURE [dnt_getnousedforumattachment]
AS
SELECT
[aid],
[uid],
[tid],
[pid],
[postdatetime],
[readperm],
[filename],
[description],
[filetype],
[filesize],
[attachment],
[downloads],
[attachprice],
[width],
[height]
FROM [dnt_attachments]
WHERE [tid]= 0 AND [pid]=0 AND DATEDIFF(n, postdatetime, GETDATE()) > 30
GO
IF OBJECT_ID('dnt_deletenousedforumattachment','P') IS NOT NULL
DROP PROC [dnt_deletenousedforumattachment]
GO
CREATE PROCEDURE [dnt_deletenousedforumattachment]
AS
DELETE FROM [dnt_attachments]
WHERE [tid]= 0 AND [pid]=0 AND DATEDIFF(n, postdatetime, GETDATE()) > 30
GO
IF OBJECT_ID('dnt_getnousedattachmentlistbytid','P') IS NOT NULL
DROP PROC [dnt_getnousedattachmentlistbytid]
GO
CREATE PROCEDURE [dnt_getnousedattachmentlistbytid]
@uid INT
AS
SELECT
[aid],
[attachment]
FROM [dnt_attachments]
WHERE [uid]= @uid AND [tid]=0 AND [pid]=0
GO
IF OBJECT_ID('dnt_updateattachmenttidtoanothertopic','P') IS NOT NULL
DROP PROC [dnt_updateattachmenttidtoanothertopic]
GO
CREATE PROCEDURE [dnt_updateattachmenttidtoanothertopic]
@tid INT,
@oldtid INT
AS
UPDATE [dnt_attachments] SET [tid]=@tid WHERE [tid]=@oldtid
GO
IF OBJECT_ID('dnt_getfirstimageattachbytid','P') IS NOT NULL
DROP PROC [dnt_getfirstimageattachbytid]
GO
CREATE PROCEDURE [dnt_getfirstimageattachbytid]
@tid INT
AS
SELECT TOP 1
[aid],
[uid],
[tid],
[pid],
[postdatetime],
[readperm],
[filename],
[description],
[filetype],
[filesize],
[attachment],
[downloads],
[attachprice],
[width],
[height]
FROM [dnt_attachments]
WHERE [tid]=@tid AND LEFT([filetype], 5)='image' ORDER BY [aid]
GO
IF OBJECT_ID('dnt_getattachmentlistbypid','P') IS NOT NULL
DROP PROC [dnt_getattachmentlistbypid]
GO
CREATE PROCEDURE [dnt_getattachmentlistbypid]
@pidlist varchar(500)
AS
SELECT
[aid],
[uid],
[tid],
[pid],
[postdatetime],
[readperm],
[filename],
[description],
[filetype],
[filesize],
[attachment],
[downloads],
[attachprice],
[width],
[height]
FROM [dnt_attachments]
WHERE CHARINDEX(','+RTRIM([dnt_attachments].[pid])+',', ','+@pidlist+',')>0
GO
IF OBJECT_ID('dnt_getattachmentlistbyaid','P') IS NOT NULL
DROP PROC [dnt_getattachmentlistbyaid]
GO
CREATE PROCEDURE [dnt_getattachmentlistbyaid]
@aidlist varchar(500)
AS
SELECT
[aid],
[tid],
[pid],
[filename]
FROM [dnt_attachments]
WHERE CHARINDEX(','+RTRIM([dnt_attachments].[aid])+',', ','+@aidlist+',')>0
GO
IF OBJECT_ID('dnt_updateattachment','P') IS NOT NULL
DROP PROC [dnt_updateattachment]
GO
CREATE PROCEDURE [dnt_updateattachment]
@readperm INT,
@description NCHAR(100),
@attachprice INT,
@aid INT
AS
UPDATE [dnt_attachments]
SET [readperm] = @readperm, [description] = @description, [attachprice] = @attachprice
WHERE [aid] = @aid
GO
IF OBJECT_ID('dnt_updateattachmentinfo','P') IS NOT NULL
DROP PROC [dnt_updateattachmentinfo]
GO
CREATE PROCEDURE [dnt_updateattachmentinfo]
@readperm INT,
@description NCHAR(100),
@aid INT
AS
UPDATE [dnt_attachments]
SET [readperm] = @readperm, [description] = @description
WHERE [aid] = @aid
GO
IF OBJECT_ID('dnt_updateallfieldattachmentinfo','P') IS NOT NULL
DROP PROC [dnt_updateallfieldattachmentinfo]
GO
CREATE PROCEDURE [dnt_updateallfieldattachmentinfo]
@postdatetime DATETIME,
@readperm INT,
@filename NCHAR(100),
@filetype NCHAR(50),
@description NCHAR(100),
@filesize INT,
@attachment NCHAR(100),
@downloads INT,
@tid INT,
@pid INT,
@aid INT,
@attachprice INT,
@width INT,
@height INT
AS
UPDATE [dnt_attachments]
SET
[postdatetime] = @postdatetime,
[readperm] = @readperm,
[filename] = @filename,
[description] = @description,
[filetype] = @filetype,
[filesize] = @filesize,
[attachment] = @attachment,
[downloads] = @downloads,
[tid]=@tid,
[pid]=@pid,
[attachprice]=@attachprice,
[width]=@width,
[height]=@height
WHERE [aid]=@aid
GO
IF OBJECT_ID('dnt_deleteattachmentbypid','P') IS NOT NULL
DROP PROC [dnt_deleteattachmentbypid]
GO
CREATE PROCEDURE [dnt_deleteattachmentbypid]
@pid INT
AS
DELETE FROM [dnt_attachments]
WHERE [pid]=@pid
GO
IF OBJECT_ID('dnt_deleteattachmentbyaidlist','P') IS NOT NULL
DROP PROC [dnt_deleteattachmentbyaidlist]
GO
CREATE PROCEDURE [dnt_deleteattachmentbyaidlist]
@aidlist VARCHAR(500)
AS
DELETE FROM [dnt_attachments] WHERE CHARINDEX(','+RTRIM([aid])+',',','+@aidlist+',') > 0
GO
IF OBJECT_ID('dnt_deleteattachmentbyaid','P') IS NOT NULL
DROP PROC [dnt_deleteattachmentbyaid]
GO
CREATE PROCEDURE [dnt_deleteattachmentbyaid]
@aid INT
AS
DELETE FROM [dnt_attachments]
WHERE [aid]=@aid
GO
IF OBJECT_ID('dnt_deleteattachmentbytid','P') IS NOT NULL
DROP PROC [dnt_deleteattachmentbytid]
GO
CREATE PROCEDURE [dnt_deleteattachmentbytid]
@tid INT
AS
DELETE FROM [dnt_attachments] WHERE [tid] = @tid
GO
IF OBJECT_ID('dnt_deleteattachmentbytidlist','P') IS NOT NULL
DROP PROC [dnt_deleteattachmentbytidlist]
GO
CREATE PROCEDURE [dnt_deleteattachmentbytidlist]
@tidlist VARCHAR(500)
AS
DELETE FROM [dnt_attachments] WHERE CHARINDEX(','+RTRIM([tid])+',',','+@tidlist+',') > 0
GO
IF OBJECT_ID('dnt_getattachmentlistbytidlist','P') IS NOT NULL
DROP PROC [dnt_getattachmentlistbytidlist]
GO
CREATE PROCEDURE [dnt_getattachmentlistbytidlist]
@tidlist VARCHAR(500)
AS
SELECT
[aid],
[filename]
FROM [dnt_attachments]
WHERE CHARINDEX(','+RTRIM([tid])+',',''+@tidlist+',') > 0
GO
IF OBJECT_ID('dnt_getattachmentlistbytid','P') IS NOT NULL
DROP PROC [dnt_getattachmentlistbytid]
GO
CREATE PROCEDURE [dnt_getattachmentlistbytid]
@tid INT
AS
SELECT
[aid],
[filename]
FROM [dnt_attachments]
WHERE [tid] = @tid
GO
IF OBJECT_ID('dnt_updateattachmentdownloads','P') IS NOT NULL
DROP PROC [dnt_updateattachmentdownloads]
GO
CREATE PROCEDURE [dnt_updateattachmentdownloads]
@aid INT
AS
UPDATE [dnt_attachments] SET [downloads]=[downloads]+1 WHERE [aid]=@aid
GO
IF OBJECT_ID('dnt_getattachenmtlistbypid','P') IS NOT NULL
DROP PROC [dnt_getattachenmtlistbypid]
GO
CREATE PROCEDURE [dnt_getattachenmtlistbypid]
@pid INT
AS
SELECT
[aid],
[uid],
[tid],
[pid],
[postdatetime],
[readperm],
[filename],
[description],
[filetype],
[filesize],
[attachment],
[downloads],
[attachprice],
[width],
[height]
FROM [dnt_attachments]
WHERE [pid]=@pid
GO
IF OBJECT_ID('dnt_getattachmentcountbytid','P') IS NOT NULL
DROP PROC [dnt_getattachmentcountbytid]
GO
CREATE PROCEDURE [dnt_getattachmentcountbytid]
@tid INT
AS
SELECT COUNT([aid]) AS [acount] FROM [dnt_attachments] WHERE [tid]=@tid
GO
IF OBJECT_ID('dnt_getattachmentcountbypid','P') IS NOT NULL
DROP PROC [dnt_getattachmentcountbypid]
GO
CREATE PROCEDURE [dnt_getattachmentcountbypid]
@pid INT
AS
SELECT COUNT([aid]) AS [acount] FROM [dnt_attachments] WHERE [pid]=@pid
GO
IF OBJECT_ID('dnt_getattachenmtinfobyaid','P') IS NOT NULL
DROP PROC [dnt_getattachenmtinfobyaid]
GO
CREATE PROCEDURE [dnt_getattachenmtinfobyaid]
@aid INT
AS
SELECT TOP 1
[aid],
[uid],
[tid],
[pid],
[postdatetime],
[readperm],
[filename],
[description],
[filetype],
[filesize],
[attachment],
[downloads],
[attachprice],
[width],
[height]
FROM [dnt_attachments]
WHERE [aid]=@aid
GO
IF OBJECT_ID('dnt_getpolllist','P') IS NOT NULL
DROP PROC [dnt_getpolllist]
GO
CREATE PROCEDURE [dnt_getpolllist]
@tid int
AS
SELECT
[pollid],
[tid],
[displayorder],
[multiple],
[visible],
[maxchoices],
[expiration],
[uid],
[voternames]
FROM [dnt_polls]
WHERE [tid]=@tid
GO
IF OBJECT_ID('dnt_gettaginfo','P') IS NOT NULL
DROP PROC [dnt_gettaginfo]
GO
CREATE PROCEDURE [dnt_gettaginfo]
@tagid int
AS
SELECT
[tagid],
[tagname],
[userid],
[postdatetime],
[orderid],
[color],
[count],
[fcount],
[pcount],
[scount],
[vcount],
[gcount]
FROM [dnt_tags]
WHERE [tagid]=@tagid
GO
IF OBJECT_ID('dnt_setcurrenttopics','P') IS NOT NULL
DROP PROC [dnt_setcurrenttopics]
GO
CREATE PROCEDURE [dnt_setcurrenttopics]
@fid int
AS
UPDATE
[dnt_forums]
SET [curtopics] = (SELECT COUNT(tid) FROM [dnt_topics] WHERE [displayorder] >= 0 AND [fid]=@fid) WHERE [fid]=@fid
GO
IF OBJECT_ID('[dnt_createinvitecode]','P') IS NOT NULL
DROP PROC [dnt_createinvitecode]
GO
CREATE PROCEDURE [dnt_createinvitecode]
@code char(8),
@creatorid int,
@creator nchar(20),
@createtime smalldatetime,
@expiretime smalldatetime,
@maxcount int,
@invitetype int
AS
INSERT INTO [dnt_invitation]([invitecode],[creatorid],[creator],[createdtime],[expiretime],[maxcount],[invitetype])
VALUES(@code,@creatorid,@creator,@createtime,@expiretime,@maxcount,@invitetype);SELECT SCOPE_IDENTITY()
GO
IF OBJECT_ID('[dnt_deleteinvitecode]','P') IS NOT NULL
DROP PROC [dnt_deleteinvitecode]
GO
CREATE PROCEDURE [dnt_deleteinvitecode]
@id int
AS
BEGIN
UPDATE [dnt_invitation] SET [isdeleted]=1 WHERE [inviteid]=@id
END
GO
IF OBJECT_ID('[dnt_clearexpireinvitecode]','P') IS NOT NULL
DROP PROC [dnt_clearexpireinvitecode]
GO
CREATE PROCEDURE [dnt_clearexpireinvitecode]
AS
BEGIN
DELETE [dnt_invitation] WHERE [invitetype]=3 AND [createdtime]<>[expiretime] AND ([expiretime]-GETDATE())<=0;
DELETE [dnt_invitation] WHERE [isdeleted]=1 AND [createdtime]<(GETDATE()-1)
END
GO
if exists (select * from sysobjects where id = object_id(N'[dnt_clearexpirecreditorders]') and OBJECTPROPERTY(id,N'IsProcedure') = 1)
drop procedure [dnt_clearexpirecreditorders]
GO
CREATE PROCEDURE [dnt_clearexpirecreditorders]
AS
BEGIN
DELETE [dnt_orders] WHERE [orderstatus] < 1 AND [createdtime] < (GETDATE()-15)
END
GO
--2009-10-30 后台优化----------------------------------------------
IF OBJECT_ID('dnt_getmaxandmintid','P') IS NOT NULL
DROP PROC [dnt_getmaxandmintid]
GO
CREATE PROC [dnt_getmaxandmintid]
@fid INT
AS
SELECT
MAX([tid]) AS [maxtid],
MIN([tid]) AS [mintid]
FROM [dnt_topics]
WHERE
[fid] IN (SELECT [fid]
FROM [dnt_forums]
WHERE [fid]=@fid
OR (CHARINDEX(',' + RTRIM(@fid) + ',', ',' + RTRIM(parentidlist) + ',') > 0))
GO
IF OBJECT_ID('dnt_getdesignatepostcount','P') IS NOT NULL
DROP PROC [dnt_getdesignatepostcount]
GO
CREATE PROC [dnt_getdesignatepostcount]
@fid INT,
@posttablename VARCHAR(50)
AS
EXEC ('SELECT COUNT([pid]) AS [postcount] FROM [' + @posttablename + '] WHERE [fid] = ' + @fid)
GO
IF OBJECT_ID('dnt_getpostcountbyuid','P') IS NOT NULL
DROP PROC [dnt_getpostcountbyuid]
GO
CREATE PROC [dnt_getpostcountbyuid]
@uid INT,
@posttablename VARCHAR(50)
AS
EXEC ('SELECT COUNT([pid]) AS [postcount] FROM [' + @posttablename + '] WHERE [posterid] = ' + @uid)
GO
IF OBJECT_ID('dnt_gettodaypostcountbyuid','P') IS NOT NULL
DROP PROC [dnt_gettodaypostcountbyuid]
GO
CREATE PROC [dnt_gettodaypostcountbyuid]
@uid INT,
@posttablename VARCHAR(50)
AS
EXEC ('SELECT COUNT([pid]) AS [postcount]
FROM [' + @posttablename + ']
WHERE [posterid] = ' + @uid + ' AND DATEDIFF(day, [postdatetime], GETDATE()) = 0')
GO
IF OBJECT_ID('dnt_gettotaltopiccount','P') IS NOT NULL
DROP PROC [dnt_gettotaltopiccount]
GO
CREATE PROC [dnt_gettotaltopiccount]
AS
SELECT COUNT([tid]) AS [topicscount] FROM [dnt_topics]
GO
IF OBJECT_ID('dnt_resetstatistic','P') IS NOT NULL
DROP PROC [dnt_resetstatistic]
GO
CREATE PROC [dnt_resetstatistic]
@totaltopic INT,
@totalpost INT,
@totalusers INT,
@lastusername VARCHAR(20),
@lastuserid INT
AS
UPDATE [dnt_statistics]
SET
[totaltopic]=@totaltopic,[totalpost]=@totalpost,[totalusers]=@totalusers,[lastusername]=@lastusername,[lastuserid]=@lastuserid
GO
IF OBJECT_ID('dnt_gettopusers','P') IS NOT NULL
DROP PROC [dnt_gettopusers]
GO
CREATE PROC [dnt_gettopusers]
@statcont INT,
@lastuid INT
AS
EXEC('
SELECT TOP '+@statcont+' [uid] FROM [dnt_users] WHERE [uid] > @lastuid
')
GO
IF OBJECT_ID('dnt_resetuserdigestposts','P') IS NOT NULL
DROP PROC [dnt_resetuserdigestposts]
GO
CREATE PROC [dnt_resetuserdigestposts]
@userid INT
AS
UPDATE [dnt_users]
SET [digestposts]=(SELECT COUNT(tid) AS [digestposts] FROM [dnt_topics] WHERE [dnt_topics].[posterid] = [dnt_users].[uid] AND [digest] > 0) WHERE [dnt_users].[uid] = @userid
GO
IF OBJECT_ID('dnt_getusers','P') IS NOT NULL
DROP PROC [dnt_getusers]
GO
CREATE PROC [dnt_getusers]
@start_uid INT,
@end_uid INT
AS
SELECT [uid] FROM [dnt_users] WHERE [uid] >= @start_uid AND [uid]<=@end_uid
GO
IF OBJECT_ID('dnt_updateuserpostcount','P') IS NOT NULL
DROP PROC [dnt_updateuserpostcount]
GO
CREATE PROC [dnt_updateuserpostcount]
@postcount INT,
@userid INT
AS
UPDATE [dnt_users] SET [posts]=@postcount WHERE [dnt_users].[uid] = @userid
GO
IF OBJECT_ID('dnt_getonlineregistercount','P') IS NOT NULL
DROP PROC [dnt_getonlineregistercount]
GO
CREATE PROC [dnt_getonlineregistercount]
AS
SELECT COUNT(olid) FROM [dnt_online] WHERE [userid]>0
GO
IF OBJECT_ID('dnt_gettopictids','P') IS NOT NULL
DROP PROC [dnt_gettopictids]
GO
CREATE PROC [dnt_gettopictids]
@statcount INT,
@lasttid INT
AS
EXEC('
SELECT TOP '+@statcount+' [tid] FROM [dnt_topics] WHERE [tid] > @lasttid ORDER BY [tid]
')
GO
IF OBJECT_ID('dnt_updatelastpostoftopic','P') IS NOT NULL
DROP PROC [dnt_updatelastpostoftopic]
GO
CREATE PROC [dnt_updatelastpostoftopic]
@tid INT,
@postcount INT,
@lastpostid INT,
@lastpost VARCHAR(20),
@lastposterid INT,
@lastposter VARCHAR(20)
AS
UPDATE [dnt_topics]
SET [lastpost]=@lastpost, [lastposterid]=@lastposterid, [lastposter]=@lastposter, [replies]=@postcount, [lastpostid]=@lastpostid
WHERE [tid] = @tid
GO
IF OBJECT_ID('dnt_updatetopiclastposterid','P') IS NOT NULL
DROP PROC [dnt_updatetopiclastposterid]
GO
CREATE PROC [dnt_updatetopiclastposterid]
@tid INT
AS
UPDATE [dnt_topics]
SET [lastposterid]=(SELECT ISNULL(MIN(lastpostid), -1)-1 FROM [dnt_topics] WHERE [tid] = @tid)
GO
IF OBJECT_ID('dnt_gettopics','P') IS NOT NULL
DROP PROC [dnt_gettopics]
GO
CREATE PROC [dnt_gettopics]
@start_tid INT,
@end_tid INT
AS
SELECT [tid]
FROM [dnt_topics]
WHERE [tid] >= @start_tid AND [tid]<=@end_tid
ORDER BY [tid]
GO
IF OBJECT_ID('dnt_gettopforumfids','P') IS NOT NULL
DROP PROC [dnt_gettopforumfids]
GO
CREATE PROC [dnt_gettopforumfids]
@lastfid INT,
@statcount INT
AS
EXEC('
SELECT TOP(@statcount) [fid] FROM [dnt_forums] WHERE [fid] > @lastfid
')
GO
IF OBJECT_ID('dnt_getlastpostbyfid','P') IS NOT NULL
DROP PROC [dnt_getlastpostbyfid]
GO
CREATE PROC [dnt_getlastpostbyfid]
@fid INT,
@posttablename VARCHAR(50)
AS
EXEC ('SELECT TOP 1 [tid], [title], [postdatetime], [posterid], [poster], [pid]
FROM [' + @posttablename + ']
WHERE [fid] = ' + @fid +
'ORDER BY [pid] DESC')
GO
IF OBJECT_ID('dnt_updateforum','P') IS NOT NULL
DROP PROC [dnt_updateforum]
GO
CREATE PROC [dnt_updateforum]
@lastfid INT,
@topiccount INT,
@postcount INT,
@lasttid INT,
@lasttitle NCHAR(80),
@lastpost VARCHAR(20),
@lastposterid INT,
@lastposter NCHAR(20),
@todaypostcount INT
AS
UPDATE [dnt_forums]
SET [topics] = @topiccount, [posts]=@postcount, [todayposts] = @todaypostcount, [lasttid] = @lasttid, [lasttitle] = @lasttitle, [lastpost]=@lastpost, [lastposterid] = @lastposterid, [lastposter]=@lastposter
WHERE [dnt_forums].[fid] = @lastfid
GO
IF OBJECT_ID('dnt_resetclearmove','P') IS NOT NULL
DROP PROC [dnt_resetclearmove]
GO
CREATE PROC [dnt_resetclearmove]
AS
DELETE FROM [dnt_topics] WHERE [closed] > 1
GO
IF OBJECT_ID('dnt_updatemytopic','P') IS NOT NULL
DROP PROC [dnt_updatemytopic]
GO
CREATE PROC [dnt_updatemytopic]
AS
DELETE FROM [dnt_mytopics]
INSERT INTO [dnt_mytopics]([uid], [tid], [dateline])
SELECT [posterid],[tid],[postdatetime]
FROM [dnt_topics]
WHERE [posterid]<>-1
GO
IF OBJECT_ID('dnt_updatemypost','P') IS NOT NULL
DROP PROC [dnt_updatemypost]
GO
CREATE PROC [dnt_updatemypost]
@tablename VARCHAR(50)
AS
DELETE FROM [dnt_myposts]
EXEC ('
INSERT INTO [dnt_myposts]([uid], [tid], [pid], [dateline])
SELECT [posterid],[tid],[pid],[postdatetime] FROM [' + @tablename + '] WHERE [posterid]<>-1
')
GO
IF OBJECT_ID('dnt_updateforumsinfo','P') IS NOT NULL
DROP PROC [dnt_updateforumsinfo]
GO
CREATE PROC [dnt_updateforumsinfo]
@parentid SmallInt,
@layer Int,
@pathlist NChar(3000),
@parentidlist NChar(300),
@subforumcount Int,
@name NChar(50),
@status Int,
@colcount SmallInt,
@displayorder Int,
@templateid SmallInt,
@topics Int,
@curtopics Int,
@posts Int,
@todayposts Int,
@lasttid Int,
@lasttitle NChar(60),
@lastpost DateTime,
@lastposterid Int,
@lastposter NChar(20),
@allowsmilies Int,
@allowrss Int,
@allowhtml Int,
@allowbbcode Int,
@allowimgcode Int,
@allowblog Int,
@istrade Int,
@allowpostspecial Int,
@allowspecialonly Int,
@alloweditrules Int,
@allowthumbnail Int,
@allowtag Int,
@recyclebin Int,
@modnewposts Int,
@jammer Int,
@disablewatermark Int,
@inheritedmod Int,
@autoclose SmallInt,
@password NVarChar(16),
@icon VarChar(255),
@postcredits VarChar(255),
@replycredits VarChar(255),
@redirect VarChar(255),
@attachextensions VarChar(255),
@rules NText,
@topictypes Text,
@viewperm Text,
@postperm Text,
@replyperm Text,
@getattachperm Text,
@postattachperm Text,
@moderators Text,
@description NText,
@applytopictype TinyInt,
@postbytopictype TinyInt,
@viewbytopictype TinyInt,
@topictypeprefix TinyInt,
@permuserlist NText,
@seokeywords NVarChar(500),
@seodescription NVarChar(500),
@rewritename NVarChar(20),
@fid Int
AS
UPDATE [dnt_forums]
SET [parentid]=@parentid, [layer]=@layer, [pathlist]=@pathlist,
[parentidlist]=@parentidlist, [subforumcount]=@subforumcount, [name]=@name, [status]=@status,
[colcount]=@colcount, [displayorder]=@displayorder,[templateid]=@templateid,[topics]=@topics,
[curtopics]=@curtopics,[posts]=@posts,[todayposts]=@todayposts,[lasttid]=@lasttid,[lasttitle]=@lasttitle,
[lastpost]=@lastpost,[lastposterid]=@lastposterid,[lastposter]=@lastposter,
[allowsmilies]=@allowsmilies ,[allowrss]=@allowrss, [allowhtml]=@allowhtml, [allowbbcode]=@allowbbcode, [allowimgcode]=@allowimgcode,
[allowblog]=@allowblog,[istrade]=@istrade,[allowpostspecial]=@allowpostspecial,[allowspecialonly]=@allowspecialonly,
[alloweditrules]=@alloweditrules ,[allowthumbnail]=@allowthumbnail ,[allowtag]=@allowtag,
[recyclebin]=@recyclebin, [modnewposts]=@modnewposts,[jammer]=@jammer,[disablewatermark]=@disablewatermark,[inheritedmod]=@inheritedmod,
[autoclose]=@autoclose
WHERE [fid]=@fid
UPDATE [dnt_forumfields]
SET [password]=@password,[icon]=@icon,[postcredits]=@postcredits,
[replycredits]=@replycredits,[redirect]=@redirect,[attachextensions]=@attachextensions,[rules]=@rules,[topictypes]=@topictypes,
[viewperm]=@viewperm,[postperm]=@postperm,[replyperm]=@replyperm,[getattachperm]=@getattachperm,[postattachperm]=@postattachperm,
[moderators]=@moderators,[description]=@description,[applytopictype]=@applytopictype,[postbytopictype]=@postbytopictype,
[viewbytopictype]=@viewbytopictype,[topictypeprefix]=@topictypeprefix,[permuserlist]=@permuserlist,[seokeywords]=@seokeywords,
[seodescription]=@seodescription,[rewritename]=@rewritename
WHERE [fid]=@fid
GO
IF OBJECT_ID('dnt_insertforumsinfo','P') IS NOT NULL
DROP PROC [dnt_insertforumsinfo]
GO
CREATE PROC [dnt_insertforumsinfo]
@parentid SmallInt,
@layer Int,
@pathlist NChar(3000),
@parentidlist NChar(300),
@subforumcount Int,
@name NChar(50),
@status Int,
@colcount SmallInt,
@displayorder Int,
@templateid SmallInt,
@allowsmilies Int,
@allowrss Int,
@allowhtml Int,
@allowbbcode Int,
@allowimgcode Int,
@allowblog Int,
@istrade Int,
@alloweditrules Int,
@allowthumbnail Int,
@allowtag Int,
@recyclebin Int,
@modnewposts Int,
@jammer Int,
@disablewatermark Int,
@inheritedmod Int,
@autoclose SmallInt,
@allowpostspecial Int,
@allowspecialonly Int,
@description NText,
@password VarChar(16),
@icon VarChar(255),
@postcredits VarChar(255),
@replycredits VarChar(255),
@redirect VarChar(255),
@attachextensions VarChar(255),
@moderators Text,
@rules NText,
@topictypes Text,
@viewperm Text,
@postperm Text,
@replyperm Text,
@getattachperm Text,
@postattachperm Text,
@seokeywords NVarChar(500),
@seodescription NVarChar(500),
@rewritename NVarChar(20)
AS
DECLARE @fid INT
INSERT INTO [dnt_forums] ([parentid],[layer],[pathlist],[parentidlist],[subforumcount],[name],
[status],[colcount],[displayorder],[templateid],[allowsmilies],[allowrss],[allowhtml],[allowbbcode],[allowimgcode],[allowblog],
[istrade],[alloweditrules],[recyclebin],[modnewposts],[jammer],[disablewatermark],[inheritedmod],[autoclose],[allowthumbnail],
[allowtag],[allowpostspecial],[allowspecialonly])
VALUES (@parentid,@layer,@pathlist,@parentidlist,@subforumcount,@name,@status, @colcount, @displayorder,
@templateid,@allowsmilies,@allowrss,@allowhtml,@allowbbcode,@allowimgcode,@allowblog,@istrade,@alloweditrules,@recyclebin,
@modnewposts,@jammer,@disablewatermark,@inheritedmod,@autoclose,@allowthumbnail,@allowtag,@allowpostspecial,@allowspecialonly)
--SET @fid=@@IDENTITY
SELECT @fid = ISNULL(MAX(fid), 0) FROM [dnt_forums]
INSERT INTO [dnt_forumfields] ([fid],[description],[password],[icon],[postcredits],[replycredits],[redirect],
[attachextensions],[moderators],[rules],[topictypes],[viewperm],[postperm],[replyperm],[getattachperm],[postattachperm],[seokeywords],[seodescription],[rewritename])
VALUES (@fid,@description,@password,@icon,@postcredits,@replycredits,@redirect,@attachextensions,@moderators,@rules,@topictypes,@viewperm,
@postperm,@replyperm,@getattachperm,@postattachperm,@seokeywords,@seodescription,@rewritename)
GO
IF OBJECT_ID('dnt_getsubforumcount','P') IS NOT NULL
DROP PROC [dnt_getsubforumcount]
GO
CREATE PROC [dnt_getsubforumcount]
@fid INT
AS
SELECT COUNT(fid) FROM [dnt_forums] WHERE [parentid]=@fid
GO
IF OBJECT_ID('dnt_deleteforumsbyfid','P') IS NOT NULL
DROP PROC [dnt_deleteforumsbyfid]
GO
CREATE PROC [dnt_deleteforumsbyfid]
@fid INT,
@postname NVARCHAR(50)
AS
DECLARE @parentid INT,
@displayorder INT
SELECT TOP 1 @parentid=[parentid],@displayorder=[displayorder] FROM [dnt_forums] WHERE [fid]=@fid
UPDATE [dnt_forums] SET [displayorder]=[displayorder]-1 WHERE [displayorder]>@displayorder
UPDATE [dnt_forums] SET [subforumcount]=[subforumcount]-1 WHERE [fid]=@parentid
DELETE FROM [dnt_forumfields] WHERE [fid]=@fid
DELETE FROM [dnt_polls] WHERE [tid] IN (SELECT [tid] FROM [dnt_topics] WHERE [fid]=@fid)
EXEC ('DELETE FROM [dnt_attachments] WHERE [tid] IN(SELECT [tid] FROM [dnt_topics] WHERE [fid]=' + @fid + ') OR [pid] IN(SELECT [pid] FROM [' + @postname + '] WHERE [fid]=' + @fid + ')')
EXEC ('DELETE FROM [' + @postname + '] WHERE [fid]=' + @fid)
DELETE FROM [dnt_topics] WHERE [fid]=@fid
DELETE FROM [dnt_forums] WHERE [fid]=@fid
DELETE FROM [dnt_moderators] WHERE [fid]=@fid
GO
IF OBJECT_ID('dnt_getparentidbyfid','P') IS NOT NULL
DROP PROC [dnt_getparentidbyfid]
GO
CREATE PROC [dnt_getparentidbyfid]
@fid INT
AS
SELECT [parentid] From [dnt_forums] WHERE [inheritedmod]=1 AND [fid]=@fid
GO
IF OBJECT_ID('dnt_updateforumsmoderators','P') IS NOT NULL
DROP PROC [dnt_updateforumsmoderators]
GO
CREATE PROC [dnt_updateforumsmoderators]
@displayorder INT,
@moderators VARCHAR(500),
@fid INT,
@inherited INT
AS
DECLARE @usernamelist VARCHAR(255),
@username VARCHAR(255),
@uid INT,
@len INT,
@b BIT,
@begin INT,
@end INT
SELECT @len=0,@begin=1,@end=1,@b=1,@usernamelist=''
WHILE @b=1
BEGIN
IF @end=1
SET @begin=@end
ELSE
SET @begin=@end+1
SET @end = CHARINDEX(',',@moderators,@begin)
IF @end=0
SET @end=LEN(@moderators)+1
SET @len=@end-@begin
IF @len>0
BEGIN
SET @username=SUBSTRING(@moderators,@begin,@len)
IF @username<>''
BEGIN
SELECT @uid=[uid] FROM [dnt_users] WHERE [groupid]<>7 AND [groupid]<>8 AND [username]=@username
IF @uid IS NOT NULL
BEGIN
INSERT INTO [dnt_moderators] ([uid],[fid],[displayorder],[inherited]) VALUES (@uid,@fid,@displayorder,@inherited)
SET @usernamelist=@usernamelist+@username+','
SET @displayorder=@displayorder+1
SET @uid=NULL
END
END
END
ELSE
SET @b=0
END
IF RIGHT(@usernamelist,1)=','
SET @usernamelist=LEFT(@usernamelist,LEN(@usernamelist)-1)
UPDATE [dnt_forumfields] SET [moderators]=@usernamelist WHERE [fid] =@fid
GO
IF OBJECT_ID('dnt_getforumstable','P') IS NOT NULL
DROP PROC [dnt_getforumstable]
GO
CREATE PROC [dnt_getforumstable]
AS
SELECT
[f].[fid],[f].[parentid],[f].[layer],[f].[pathlist],[f].[parentidlist],[f].[subforumcount],[f].[name],[f].[status],[f].[colcount],[f].[displayorder],[f].[templateid],[f].[topics],[f].[curtopics],[f].[posts],[f].[todayposts],[f].[lasttid],[f].[lasttitle],[f].[lastpost],[f].[lastposterid],[f].[lastposter],[f].[allowsmilies],[f].[allowrss],[f].[allowhtml],[f].[allowbbcode],[f].[allowimgcode],[f].[allowblog],[f].[istrade],[f].[allowpostspecial],[f].[allowspecialonly],[f].[alloweditrules],[f].[allowthumbnail],[f].[allowtag],[f].[recyclebin],[f].[modnewposts],[f].[jammer],[f].[disablewatermark],[f].[inheritedmod],[f].[autoclose],[ff].[password],[ff].[icon],[ff].[postcredits],[ff].[replycredits],[ff].[redirect],[ff].[attachextensions],[ff].[rules],[ff].[topictypes],[ff].[viewperm],[ff].[postperm],[ff].[replyperm],[ff].[getattachperm],[ff].[postattachperm],[ff].[moderators],[ff].[description],[ff].[applytopictype],[ff].[postbytopictype],[ff].[viewbytopictype],[ff].[topictypeprefix],[ff].[permuserlist],[ff].[seokeywords],[ff].[seodescription],[ff].[rewritename]
FROM [dnt_forums] AS [f]
LEFT JOIN [dnt_forumfields] AS [ff]
ON [f].[fid]=[ff].[fid]
ORDER BY [f].[displayorder]
GO
IF OBJECT_ID('dnt_getmainforum','P') IS NOT NULL
DROP PROC [dnt_getmainforum]
GO
CREATE PROC [dnt_getmainforum]
AS
SELECT
[fid],[parentid],[layer],[pathlist],[parentidlist],[subforumcount],[name],[status],[colcount],[displayorder],[templateid],[topics],[curtopics],[posts],[todayposts],[lasttid],[lasttitle],[lastpost],[lastposterid],[lastposter],[allowsmilies],[allowrss],[allowhtml],[allowbbcode],[allowimgcode],[allowblog],[istrade],[allowpostspecial],[allowspecialonly],[alloweditrules],[allowthumbnail],[allowtag],[recyclebin],[modnewposts],[jammer],[disablewatermark],[inheritedmod],[autoclose]
FROM [dnt_forums]
WHERE [layer]=0
Order By [displayorder] ASC
GO
IF OBJECT_ID('dnt_getlastposttid','P') IS NOT NULL
DROP PROC [dnt_getlastposttid]
GO
CREATE PROC [dnt_getlastposttid]
@visibleforums VARCHAR(4000),
@fid INT
AS
IF @visibleforums=''
SELECT TOP 1 [tid] FROM [dnt_topics] AS t LEFT JOIN [dnt_forums] AS f ON [t].[fid] = [f].[fid]
WHERE [t].[closed]<>1 AND [t].[displayorder] >=0 AND ([t].[fid] = @fid
OR CHARINDEX(',' + CONVERT(NVARCHAR(10), @fid) + ',' , ',' + RTRIM([f].[parentidlist]) + ',') > 0 )
ORDER BY [t].[lastpost] DESC
ELSE
EXEC('SELECT TOP 1 [tid] FROM [dnt_topics] AS t LEFT JOIN [dnt_forums] AS f ON [t].[fid] = [f].[fid]
WHERE [t].[closed]<>1 AND [t].[displayorder] >=0 AND ([t].[fid] = ' + @fid +
'OR CHARINDEX('','' + CONVERT(NVARCHAR(10), ' + @fid + ') + '','' , '','' + RTRIM([f].[parentidlist]) + '','') > 0 )
AND [t].[fid] IN ('+@visibleforums+') ORDER BY [t].[lastpost] DESC')
GO
IF OBJECT_ID('dnt_deluserallinf','P') IS NOT NULL
DROP PROC [dnt_deluserallinf]
GO
CREATE PROC [dnt_deluserallinf]
@uid INT,
@delPosts BIT,
@delPms BIT
AS
DECLARE @existuid INT
SELECT @existuid = COUNT([uid]) FROM [dnt_users] WHERE [uid]=@uid
IF @existuid <>0
BEGIN
DELETE FROM [dnt_users] WHERE [uid]=@uid
DELETE FROM [dnt_userfields] WHERE [uid]=@uid
DELETE FROM [dnt_onlinetime] WHERE [uid]=@uid
DELETE FROM [dnt_polls] WHERE [uid]=@uid
DELETE FROM [dnt_favorites] WHERE [uid]=@uid
DECLARE @tableid INT,
@tablename NVARCHAR(20)
IF @delPosts = 1
BEGIN
DELETE FROM [dnt_topics] WHERE [posterid]=@uid
DECLARE tables_cursor CURSOR FOR SELECT [id] FROM [dnt_tablelist]
OPEN tables_cursor
FETCH NEXT FROM tables_cursor INTO @tableid
WHILE @@FETCH_STATUS = 0
BEGIN
SET @tablename = 'dnt_posts' + LTRIM(STR(@tableid))
EXEC ('DELETE FROM ' + @tablename + ' WHERE [posterid]='+@uid)
FETCH NEXT FROM tables_cursor INTO @tableid
END
CLOSE tables_cursor
DEALLOCATE tables_cursor
SET @tableid = 0
SET @tablename = ''
END
ELSE
BEGIN
UPDATE [dnt_topics] SET [poster]='该用户已被删除' Where [posterid]=@uid
UPDATE [dnt_topics] SET [lastposter]='该用户已被删除' Where [lastpostid]=@uid
DECLARE tables_cursor CURSOR FOR SELECT [id] FROM [dnt_tablelist]
OPEN tables_cursor
FETCH NEXT FROM tables_cursor INTO @tableid
WHILE @@FETCH_STATUS = 0
BEGIN
SET @tablename = 'dnt_posts' + LTRIM(STR(@tableid))
EXEC ('UPDATE ['+@tablename+'] SET [poster]=''该用户已被删除'' WHERE [posterid]='+@uid)
FETCH NEXT FROM tables_cursor INTO @tableid
END
CLOSE tables_cursor
DEALLOCATE tables_cursor
SET @tableid = 0
SET @tablename = ''
END
IF @delPms = 1
BEGIN
DELETE FROM [dnt_pms] WHERE [msgfromid]=@uid
END
ELSE
BEGIN
UPDATE [dnt_pms] SET [msgfrom]='该用户已被删除' WHERE [msgfromid]=@uid
UPDATE [dnt_pms] SET [msgto]='该用户已被删除' WHERE [msgtoid]=@uid
END
DELETE FROM [dnt_moderators] WHERE [uid]=@uid
UPDATE [dnt_statistics] SET [totalusers]=[totalusers]-1
DECLARE @lastuserid INT,@lastusername VARCHAR(50)
SELECT TOP 1 @lastuserid=[uid],@lastusername=[username] FROM [dnt_users] ORDER BY [uid] DESC
IF @lastuserid IS NOT NULL
UPDATE [dnt_Statistics] SET [lastuserid]=@lastuserid, [lastusername]=@lastusername
END
GO
IF OBJECT_ID('dnt_addusergroup','P') IS NOT NULL
DROP PROC [dnt_addusergroup]
GO
CREATE PROC [dnt_addusergroup]
@Radminid INT,
@Grouptitle NVARCHAR(50),
@Creditshigher INT,
@Creditslower INT,
@Stars INT,
@Color CHAR(7),
@Groupavatar NVARCHAR(60),
@Readaccess INT,
@Allowvisit INT,
@Allowpost INT,
@Allowreply INT,
@Allowpostpoll INT,
@Allowdirectpost INT,
@Allowgetattach INT,
@Allowpostattach INT,
@Allowvote INT,
@Allowmultigroups INT,
@Allowsearch INT,
@Allowavatar INT,
@Allowcstatus INT,
@Allowuseblog INT,
@Allowinvisible INT,
@Allowtransfer INT,
@Allowsetreadperm INT,
@Allowsetattachperm INT,
@Allowhidecode INT,
@Allowhtml INT,
@Allowcusbbcode INT,
@Allownickname INT,
@Allowsigbbcode INT,
@Allowsigimgcode INT,
@Allowviewpro INT,
@Allowviewstats INT,
@Allowtrade INT,
@Allowdiggs INT,
@Allowdebate INT,
@Allowbonus INT,
@Minbonusprice INT,
@Maxbonusprice INT,
@Disableperiodctrl INT,
@Reasonpm INT,
@Maxprice SMALLINT,
@Maxpmnum SMALLINT,
@Maxsigsize SMALLINT,
@Maxattachsize INT,
@Maxsizeperday INT,
@Attachextensions CHAR(100),
@Maxspaceattachsize INT,
@Maxspacephotosize INT,
@Raterange CHAR(100)
AS
INSERT INTO [dnt_usergroups] ([radminid],[grouptitle],[creditshigher],[creditslower],
[stars] ,[color], [groupavatar],[readaccess], [allowvisit],[allowpost],[allowreply],
[allowpostpoll], [allowdirectpost],[allowgetattach],[allowpostattach],[allowvote],[allowmultigroups],
[allowsearch],[allowavatar],[allowcstatus],[allowuseblog],[allowinvisible],[allowtransfer],
[allowsetreadperm],[allowsetattachperm],[allowhidecode],[allowhtml],[allowcusbbcode],[allownickname],
[allowsigbbcode],[allowsigimgcode],[allowviewpro],[allowviewstats],[allowtrade],[allowdiggs],[disableperiodctrl],[reasonpm],
[maxprice],[maxpmnum],[maxsigsize],[maxattachsize],[maxsizeperday],[attachextensions],[raterange],[maxspaceattachsize],
[maxspacephotosize],[allowdebate],[allowbonus],[minbonusprice],[maxbonusprice])
VALUES(
@Radminid,@Grouptitle,@Creditshigher,@Creditslower,@Stars,@Color,@Groupavatar,@Readaccess,@Allowvisit,@Allowpost,@Allowreply,
@Allowpostpoll,@Allowdirectpost,@Allowgetattach,@Allowpostattach,@Allowvote,@Allowmultigroups,@Allowsearch,@Allowavatar,@Allowcstatus,
@Allowuseblog,@Allowinvisible,@Allowtransfer,@Allowsetreadperm,@Allowsetattachperm,@Allowhidecode,@Allowhtml,@Allowcusbbcode,@Allownickname,
@Allowsigbbcode,@Allowsigimgcode,@Allowviewpro,@Allowviewstats,@Allowtrade,@Allowdiggs,@Disableperiodctrl,@Reasonpm,@Maxprice,@Maxpmnum,@Maxsigsize,@Maxattachsize,
@Maxsizeperday,@Attachextensions,@Raterange,@Maxspaceattachsize,@Maxspacephotosize,@Allowdebate,@Allowbonus,@Minbonusprice,@Maxbonusprice)
GO
IF OBJECT_ID('dnt_updateusergroup','P') IS NOT NULL
DROP PROC [dnt_updateusergroup]
GO
CREATE PROC [dnt_updateusergroup]
@Radminid INT,
@Grouptitle NVARCHAR(50),
@Creditshigher INT,
@Creditslower INT,
@Stars INT,
@Color CHAR(7),
@Groupavatar NVARCHAR(60),
@Readaccess INT,
@Allowvisit INT,
@Allowpost INT,
@Allowreply INT,
@Allowpostpoll INT,
@Allowdirectpost INT,
@Allowgetattach INT,
@Allowpostattach INT,
@Allowvote INT,
@Allowmultigroups INT,
@Allowsearch INT,
@Allowavatar INT,
@Allowcstatus INT,
@Allowuseblog INT,
@Allowinvisible INT,
@Allowtransfer INT,
@Allowsetreadperm INT,
@Allowsetattachperm INT,
@Allowhidecode INT,
@Allowhtml INT,
@Allowcusbbcode INT,
@Allownickname INT,
@Allowsigbbcode INT,
@Allowsigimgcode INT,
@Allowviewpro INT,
@Allowviewstats INT,
@Allowtrade INT,
@Allowdiggs INT,
@Disableperiodctrl INT,
@Allowdebate INT,
@Allowbonus INT,
@Minbonusprice INT,
@Maxbonusprice INT,
@Reasonpm INT,
@Maxprice SMALLINT,
@Maxpmnum SMALLINT,
@Maxsigsize SMALLINT,
@Maxattachsize INT,
@Maxsizeperday INT,
@Attachextensions CHAR(100),
@Maxspaceattachsize INT,
@Maxspacephotosize INT,
@Groupid INT
AS
UPDATE [dnt_usergroups]
SET [radminid]=@Radminid,[grouptitle]=@Grouptitle,[creditshigher]=@Creditshigher,[creditslower]=@Creditslower,
[stars]=@Stars,[color]=@Color,[groupavatar]=@Groupavatar,[readaccess]=@Readaccess,
[allowvisit]=@Allowvisit,[allowpost]=@Allowpost,[allowreply]=@Allowreply,[allowpostpoll]=@Allowpostpoll,
[allowdirectpost]=@Allowdirectpost,[allowgetattach]=@Allowgetattach,[allowpostattach]=@Allowpostattach,
[allowvote]=@Allowvote,[allowmultigroups]=@Allowmultigroups,[allowsearch]=@Allowsearch,[allowavatar]=@Allowavatar,
[allowcstatus]=@Allowcstatus,[allowuseblog]=@Allowuseblog,[allowinvisible]=@Allowinvisible,
[allowtransfer]=@Allowtransfer,[allowsetreadperm]=@Allowsetreadperm,[allowsetattachperm]=@Allowsetattachperm,
[allowhidecode]=@Allowhidecode,[allowhtml]=@Allowhtml,[allowcusbbcode]=@Allowcusbbcode,
[allownickname]=@Allownickname,[allowsigbbcode]=@Allowsigbbcode,[allowsigimgcode]=@Allowsigimgcode,
[allowviewpro]=@Allowviewpro,[allowviewstats]=@Allowviewstats,[allowtrade]=@Allowtrade,[allowdiggs]=@Allowdiggs,
[disableperiodctrl]=@Disableperiodctrl,[allowdebate]=@Allowdebate,[allowbonus]=@Allowbonus,
[minbonusprice]=@Minbonusprice,[maxbonusprice]=@Maxbonusprice,[reasonpm]=@Reasonpm,[maxprice]=@Maxprice,
[maxpmnum]=@Maxpmnum,[maxsigsize]=@Maxsigsize,[maxattachsize]=@Maxattachsize,[maxsizeperday]=@Maxsizeperday,
[attachextensions]=@Attachextensions,[maxspaceattachsize]=@Maxspaceattachsize,
[maxspacephotosize]=@Maxspacephotosize
WHERE [groupid]=@Groupid
GO
IF OBJECT_ID('dnt_getonlinegroupicontable','P') IS NOT NULL
DROP PROC [dnt_getonlinegroupicontable]
GO
CREATE PROC [dnt_getonlinegroupicontable]
AS
SELECT [groupid], [displayorder], [title], [img] FROM [dnt_onlinelist] WHERE [img] <> '' ORDER BY [displayorder]
GO
IF OBJECT_ID('dnt_getuserlistbygroupid','P') IS NOT NULL
DROP PROC [dnt_getuserlistbygroupid]
GO
CREATE PROC [dnt_getuserlistbygroupid]
@groupIdList VARCHAR(500)
AS
SELECT
[uid],
[username],
[nickname],
[password],
[secques],
[spaceid],
[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],
[tpp],
[ppp],
[templateid],
[pmsound],
[showemail],
[invisible],
[newpm],
[newpmcount],
[accessmasks],
[onlinestate],
[newsletter],
[salt]
FROM [dnt_users] WHERE [groupid] IN (@groupIdList)
GO
IF OBJECT_ID('dnt_passpost','P') IS NOT NULL
DROP PROC [dnt_passpost]
GO
CREATE PROC [dnt_passpost]
@currentPostTableId INT,
@postcount INT,
@pidList NVARCHAR(500)
AS
DECLARE @count INT,
@begin INT,
@postdatetime DATETIME,
@poster NVARCHAR(50),
@posterid INT,
@fid INT,
@title NVARCHAR(20),
@tid INT
SET @begin=1
EXEC('UPDATE [dnt_posts'+@currentPostTableId+'] SET [invisible]=0 WHERE [pid] IN ('+@pidList+')')
UPDATE [dnt_statistics] SET [totalpost]= [totalpost] + @postcount
DECLARE @tempposttable TABLE
(
[ROWID] INT IDENTITY(1,1),
[postdatetime] DATETIME,
[poster] NVARCHAR(50),
[posterid] INT,
[fid] INT,
[title] NVARCHAR(20),
[tid] INT
)
INSERT INTO @tempposttable
EXEC('SELECT [postdatetime],[poster],[posterid],[fid],[title],[tid] FROM [dnt_posts'+@currentPostTableId+'] WHERE [pid] IN ('+@pidList+')')
SELECT @count=COUNT(1) FROM @tempposttable
WHILE @begin <= @count
BEGIN
SELECT @postdatetime = [postdatetime],
@poster = [poster],
@posterid = [posterid],
@fid = [fid],
@title = [title],
@tid = [tid]
FROM @tempposttable
UPDATE [dnt_forums]
SET [posts]=[posts] + 1,
[todayposts]=CASE WHEN datediff(day,'2008-10-22',getdate())=0
THEN [todayposts] + 1
ELSE [todayposts] END,
[lastpost]=@postdatetime,
[lastposter]=@poster,
[lastposterid]=@posterid
WHERE [fid]=@fid
UPDATE [dnt_users]
SET [lastpost] = @postdatetime,
[lastpostid] = @posterid,
[lastposttitle] = @title,
[posts] = [posts] + 1
WHERE [uid] = @posterid
UPDATE [dnt_topics]
SET [replies]=[replies]+1,
[lastposter]=@poster,
[lastposterid]=@posterid,
[lastpost]=@postdatetime
WHERE [tid]=@tid
SET @begin = @begin + 1
END
GO
IF OBJECT_ID('dnt_getunauditnewtopic','P') IS NOT NULL
DROP PROC [dnt_getunauditnewtopic]
GO
CREATE PROC [dnt_getunauditnewtopic]
AS
SELECT
[tid],
[fid],
[iconid],
[typeid],
[readperm],
[price],
[poster],
[posterid],
[title],
[attention],
[postdatetime],
[lastpost],
[lastpostid],
[lastposter],
[lastposterid],
[views],
[replies],
[displayorder],
[highlight],
[digest],
[rate],
[hide],
[attachment],
[moderated],
[closed],
[magic],
[identify],
[special]
FROM dnt_topics
WHERE [displayorder] = -2 OR [displayorder] = -3
ORDER BY [tid] DESC
GO
IF OBJECT_ID('dnt_getunauditnewtopicbycondition','P') IS NOT NULL
DROP PROC [dnt_getunauditnewtopicbycondition]
GO
CREATE PROCEDURE [dnt_getunauditnewtopicbycondition]
@fidlist NVARCHAR(500),
@pagesize int,
@displayorder INT,
@pageindex int
AS
DECLARE @startRow NVARCHAR(3)
SET @startRow = (@pageindex - 1) * @pagesize
IF @fidlist = '0'
BEGIN
IF @pageindex = 1
BEGIN
EXEC('
SELECT
TOP '+@pagesize+'
[dnt_topics].[rate],
[dnt_topics].[fid],
[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 [displayorder] = @displayorder
')
END
ELSE
BEGIN
EXEC('
SELECT
TOP '+@pagesize+'
[dnt_topics].[rate],
[dnt_topics].[fid],
[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 [displayorder] = '+@displayorder+'
AND tid < (SELECT MIN(tid) FROM (SELECT TOP '+@startRow+' tid FROM [dnt_topics] WHERE displayorder='+@displayorder+') AS T)
ORDER BY tid DESC
')
END
END
ELSE
BEGIN
IF @pageindex = 1
BEGIN
EXEC('
SELECT
TOP '+@pagesize+'
[dnt_topics].[rate],
[dnt_topics].[fid],
[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 [displayorder] = @displayorder AND fid IN ('+@fidlist+')
')
END
ELSE
BEGIN
EXEC('
SELECT
TOP '+@pagesize+'
[dnt_topics].[rate],
[dnt_topics].[fid],
[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 [displayorder] = '+@displayorder+' AND fid IN ('+@fidlist+')
AND tid < (SELECT MIN(tid) FROM (SELECT TOP '+@startRow+' tid FROM [dnt_topics] WHERE displayorder='+@displayorder+' fid IN ('+@fidlist+')) AS T)
ORDER BY tid DESC
')
END
END
IF OBJECT_ID('dnt_getunauditpost','P') IS NOT NULL
DROP PROC [dnt_getunauditpost]
GO
CREATE PROCEDURE [dnt_getunauditpost]
@lastposter NVARCHAR(20),
@lastposterid INT
AS
UPDATE [dnt_topics]
SET [lastposter]=@lastposter
WHERE [lastposterid]=@lastposterid
GO
IF OBJECT_ID('dnt_updatetopicposter','P') IS NOT NULL
DROP PROC [dnt_updatetopicposter]
GO
CREATE PROCEDURE [dnt_updatetopicposter]
@poster NVARCHAR(20),
@posterid INT
AS
UPDATE [dnt_topics]
SET [poster]=@poster
WHERE [posterid]=@posterid
GO
IF OBJECT_ID('dnt_updatepostposter','P') IS NOT NULL
DROP PROC [dnt_updatepostposter]
GO
CREATE PROCEDURE [dnt_updatepostposter]
@poster NVARCHAR(20),
@posterid INT
AS
DECLARE @count INT,
@begin INT,
@tableid INT
SET @begin = 1
DECLARE @tempposttable TABLE
(
[ROWID] INT IDENTITY(1,1),
[tableid] INT
)
INSERT INTO @tempposttable
SELECT id FROM dnt_tablelist
SELECT @count=COUNT(1) FROM @tempposttable
WHILE @begin <= @count
BEGIN
SELECT @tableid = [tableid] FROM @tempposttable WHERE [ROWID] = @begin
EXEC('UPDATE [dnt_posts' + @tableid + '] SET [poster]=''' + @poster + ''' WHERE [posterid]=' + @posterid )
SET @begin = @begin + 1
END
GO
IF OBJECT_ID('dnt_updatemoderatorname','P') IS NOT NULL
DROP PROC [dnt_updatemoderatorname]
GO
CREATE PROC [dnt_updatemoderatorname]
@oldname NVARCHAR(20),
@newname VARCHAR(20)
AS
IF @newname <> ''
UPDATE dnt_forumfields
SET MODERATORS=REPLACE(LTRIM(RTRIM(REPLACE(' '+REPLACE(CONVERT(NVARCHAR(500),MODERATORS),',',' ')+' ',' '+@oldname+' ',' '+@newname+' '))),' ',',')
WHERE CHARINDEX(','+@oldname+',',','+CONVERT(NVARCHAR(500),MODERATORS)+',') > 0
ELSE
UPDATE dnt_forumfields
SET MODERATORS = REPLACE(LTRIM(RTRIM(REPLACE(REPLACE(','+CONVERT(NVARCHAR(500),MODERATORS)+',',','+@oldname+',',','),',',' '))),' ',',')
WHERE CHARINDEX(','+@oldname+',',','+CONVERT(NVARCHAR(500),MODERATORS)+',') > 0
GO
IF OBJECT_ID('dnt_updateonlinelist','P') IS NOT NULL
DROP PROC [dnt_updateonlinelist]
GO
CREATE PROC [dnt_updateonlinelist]
@title NVARCHAR(50),
@groupid INT
AS
UPDATE [dnt_onlinelist] SET [title]=@title WHERE [groupid]=@groupid
GO
--此存储过程的作用是更新总帖子数
IF OBJECT_ID('dnt_resetforumsposts','P') IS NOT NULL
DROP PROC [dnt_resetforumsposts]
GO
CREATE PROC [dnt_resetforumsposts]
AS
--清空forums表中的帖子数，以便重新统计
UPDATE dnt_forums SET posts=0
DECLARE @i INT,
@maxlayer INT,
@maxtableid INT,
@tablename NVARCHAR(50)
SET @i = 1
--取分表数
SELECT @maxtableid = MAX(id) FROM dnt_tablelist
--依次处理分表
WHILE @i <= @maxtableid
BEGIN
SET @tablename = 'dnt_posts' + CONVERT(NVARCHAR(2),@i)
--如果分表不存在则继续处理下一个
IF NOT EXISTS(SELECT * FROM SYSOBJECTS WHERE id = OBJECT_ID(@tablename) AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
CONTINUE
--取得每个版块在当前分表中的帖子数，累加并更新forums表中版块帖子数
EXEC('
UPDATE dnt_forums
SET posts=posts+rightTable.fidcount
FROM dnt_forums
INNER JOIN (SELECT fid,COUNT(fid) fidcount FROM '+ @tablename +' GROUP BY fid) AS rightTable
ON dnt_forums.fid=rightTable.fid
WHERE dnt_forums.fid=rightTable.fid
')
SET @i = @i + 1
END
--以下操作将子版块中的帖子总数累加并更新到父版块中
--取得版块的最大层数
SELECT @maxlayer = MAX(layer) FROM dnt_forums
--倒序更新，从最底层版块更新到最顶层
WHILE @maxlayer > 0
BEGIN
--将子版块中的帖子总数累加并更新到父版块中
UPDATE dnt_forums
SET posts=posts+rightTable.sumposts
FROM dnt_forums AS leftTable
INNER JOIN (SELECT parentid,SUM(posts) sumposts FROM dnt_forums WHERE layer=@maxlayer GROUP BY parentid) AS rightTable
ON leftTable.fid=rightTable.parentid
WHERE leftTable.fid=rightTable.parentid
SET @maxlayer = @maxlayer - 1
END
GO
--此存储过程的作用是更新最后发帖
--测试代码：
--SELECT parentid,fid,lasttid,lasttitle,lastpost,lastposterid,lastposter FROM #tempTable2 ORDER BY parentid
--SELECT fid,lasttid,lasttitle,lastpost,parentid,lastposterid,lastposter FROM dnt_forums WHERE layer = 6 AND fid IN (245,256,260,259,273)
IF OBJECT_ID('dnt_resetlastpostinfo','P') IS NOT NULL
DROP PROC [dnt_resetlastpostinfo]
GO
CREATE PROC [dnt_resetlastpostinfo]
AS
--创建两个临时表#tempTable1，#tempTable2
CREATE TABLE #tempTable1 (
fid INT,
lasttid INT,
lasttitle NVARCHAR(60),
lastpost DATETIME,
parentid INT,
lastposterid INT,
lastposter NVARCHAR(30)
)
CREATE table #tempTable2 (
fid INT,
lasttid INT,
lasttitle NVARCHAR(60),
lastpost DATETIME,
parentid INT,
lastposterid INT,
lastposter NVARCHAR(30)
)
DECLARE @maxlayer INT -- 用来存放最大layer的变量
SELECT @maxlayer = MAX(layer) FROM dnt_forums;
--先初始化dnt_forums表中最后发帖等的信息
UPDATE dnt_forums SET lasttid = 0, lasttitle = '从未',lastpost = '1900-1-1',lastposterid=0,lastposter=''
--更新所有有主题的版块的最后发帖人等信息
UPDATE dnt_forums
SET lasttid = rightTable.tid, lasttitle = rightTable.title, lastpost = rightTable.lastpost, lastposterid=rightTable.lastposterid, lastposter=rightTable.lastposter
FROM dnt_forums AS leftTable
INNER JOIN (SELECT fid,tid,title,lastpost,lastposter,lastposterid FROM dnt_topics WHERE tid IN( SELECT MAX(tid) FROM dnt_topics GROUP BY fid)) AS rightTable
ON leftTable.fid = rightTable.fid
WHERE leftTable.fid = rightTable.fid
WHILE @maxlayer > 0
BEGIN
--清空临时表#tempTable1，为了保证第二次循环时，表是空的
DELETE FROM #tempTable1;
--首先以dnt_forums表中的parentid字段分组，查询出每组最后回复时间最晚的记录；
--然后LEFT JOIN表dnt_forums，以两个表中parentid相等并且lastpost相等为条件，进行查询，
--并把结果插入到临时表#tempTable1中，以备临时表#tempTable2用
INSERT INTO #tempTable1
SELECT fid,lasttid,lasttitle,a.lastpost,a.parentid,a.lastposterid,a.lastposter
FROM (
SELECT parentid,MAX(lastpost) lastpost FROM dnt_forums a WHERE layer = @maxlayer GROUP BY parentid
) b
LEFT JOIN dnt_forums a
ON a.parentid=b.parentid and a.lastpost=b.lastpost
--清空临时表#tempTable2，为了保证第二次循环时，表是空的
DELETE FROM #tempTable2;
--首先以#tempTable1表中的parentid分组，查询出组中的MAX(fid)；
--然后以fid IN （子查询中查出的MAX(fid)）为条件，查询出结果并插入到#tempTable2中
INSERT INTO #tempTable2
SELECT * FROM #tempTable1
WHERE fid IN (SELECT MAX(fid) FROM #tempTable1 GROUP BY parentid,lastpost)
--根据临时表#tempTable2中的内容，更新dnt_forums表的最后发帖人等信息
UPDATE dnt_forums
SET lastpost = CASE WHEN #tempTable2.lastpost>dnt_forums.lastpost THEN #tempTable2.lastpost
ELSE dnt_forums.lastpost END,
lasttitle = CASE WHEN #tempTable2.lastpost>dnt_forums.lastpost THEN #tempTable2.lasttitle
ELSE dnt_forums.lasttitle END,
lasttid = CASE WHEN #tempTable2.lastpost>dnt_forums.lastpost THEN #tempTable2.lasttid
ELSE dnt_forums.lasttid END,
lastposterid = CASE WHEN #tempTable2.lastpost>dnt_forums.lastpost THEN #tempTable2.lastposterid
ELSE dnt_forums.lastposterid END,
lastposter = CASE WHEN #tempTable2.lastpost>dnt_forums.lastpost THEN #tempTable2.lastposter
ELSE dnt_forums.lastposter END
FROM dnt_forums
INNER JOIN #tempTable2
ON dnt_forums.fid=#tempTable2.parentid
WHERE dnt_forums.fid=#tempTable2.parentid;
SET @maxlayer = @maxlayer - 1;
END
DROP TABLE #tempTable2;
DROP TABLE #tempTable1;
GO
/*
此存储过程的作用：
根据参数中传递过来的分表名更新主题的最后回复等信息
*/
IF OBJECT_ID('dnt_resetLastRepliesInfoOftopics','P') IS NOT NULL
DROP PROC [dnt_resetLastRepliesInfoOftopics]
GO
CREATE PROC [dnt_resetLastRepliesInfoOftopics]
@posttable NVARCHAR(20)
AS
EXEC('
UPDATE dnt_topics
SET dnt_topics.replies = lastPostTable.posts,
lastpost = lastPostTable.postdatetime,
lastposterid = lastPostTable.posterid,
lastposter = lastPostTable.poster,
lastpostid = lastPostTable.pid
FROM dnt_topics
INNER JOIN (
SELECT leftTable.tid,leftTable.posterid,leftTable.pid,leftTable.poster,leftTable.postdatetime,rightTable.posts FROM '+@posttable+' AS leftTable
LEFT JOIN (SELECT tid,COUNT(*)-1 posts,MAX(pid) maxpid FROM '+@posttable+' GROUP BY tid) AS rightTable
ON leftTable.pid = rightTable.maxpid
WHERE leftTable.pid = rightTable.maxpid
) AS lastPostTable
ON dnt_topics.tid = lastPostTable.tid
WHERE dnt_topics.tid = lastPostTable.tid AND dnt_topics.lastpostid <> lastPostTable.pid
')
GO
/*
此存储过程的作用：
更新所有用户的精华帖数
*/
IF OBJECT_ID('dnt_resetuserdigestposts','P') IS NOT NULL
DROP PROC [dnt_resetuserdigestposts]
GO
CREATE PROC [dnt_resetuserdigestposts]
AS
UPDATE dnt_users
SET [digestposts] = leftTable.topiccount
FROM dnt_users
INNER JOIN (SELECT COUNT(tid) topiccount,posterid FROM dnt_topics WHERE [digest] > 0 GROUP BY posterid) AS leftTable
ON dnt_users.[uid] = leftTable.posterid
WHERE dnt_users.[uid] = leftTable.posterid AND dnt_users.posts <> 0
GO
/*
此存储过程的作用：
根据参数中传递过来的分表名更新用户的帖子数
后台更新所有用户帖子数时使用
*/
IF OBJECT_ID('dnt_resetuserspostcount','P') IS NOT NULL
DROP PROC [dnt_resetuserspostcount]
GO
CREATE PROC [dnt_resetuserspostcount]
@posttablename NVARCHAR(50)
AS
EXEC('
UPDATE dnt_users
SET posts = posts + leftTable.postcount
FROM dnt_users
INNER JOIN (SELECT posterid,COUNT(pid) postcount FROM '+@posttablename+' GROUP BY posterid) AS leftTable
ON dnt_users.[uid] = leftTable.posterid
WHERE dnt_users.[uid] = leftTable.posterid
')
GO
CREATE PROCEDURE [dnt_createinvitecode]
@code char(8),
@creatorid int,
@creator nchar(20),
@createtime smalldatetime,
@expiretime smalldatetime,
@maxcount int,
@invitetype int
AS
INSERT INTO [dnt_invitation]([invitecode],[creatorid],[creator],[createdtime],[expiretime],[maxcount],[invitetype])
VALUES(@code,@creatorid,@creator,@createtime,@expiretime,@maxcount,@invitetype);SELECT SCOPE_IDENTITY()
GO
CREATE PROCEDURE [dnt_deleteinvitecode]
@id int
AS
BEGIN
UPDATE [dnt_invitation] SET [isdeleted]=1 WHERE [inviteid]=@id
END
GO
CREATE PROCEDURE [dnt_clearexpireinvitecode]
AS
BEGIN
DELETE [dnt_invitation] WHERE [invitetype]=3 AND [createdtime]<>[expiretime] AND ([expiretime]-GETDATE())<=0;
DELETE [dnt_invitation] WHERE [isdeleted]=1 AND [createdtime]<(GETDATE()-1)
END
GO
CREATE PROCEDURE [dnt_getinvitecode]
@searchtype nchar(10),
@searchkey nchar(20)
AS
BEGIN
IF @searchtype='uid'
SELECT TOP 1 [inviteid],[invitecode],[invitetype],[createdtime],[creator],[creatorid],[expiretime],[maxcount],[successcount]
FROM [dnt_invitation] WHERE [creatorid]=@searchkey AND [invitetype]=2 AND [isdeleted]=0 ORDER BY [inviteid] DESC
ELSE IF @searchtype='id'
SELECT [inviteid],[invitecode],[invitetype],[createdtime],[creator],[creatorid],[expiretime],[maxcount],[successcount] FROM
[dnt_invitation] WHERE [inviteid]=@searchkey AND [isdeleted]=0
ELSE IF @searchtype='code'
SELECT [inviteid],[invitecode],[invitetype],[createdtime],[creator],[creatorid],[expiretime],[maxcount],[successcount] FROM
[dnt_invitation] WHERE [invitecode]=@searchkey AND [isdeleted]=0
ELSE
SELECT (1)
END
GO
IF OBJECT_ID('dnt_getinvitecodelistbyuid','P') IS NOT NULL
DROP PROC [dnt_getinvitecodelistbyuid]
GO
CREATE PROCEDURE [dnt_getinvitecodelistbyuid]
@creatorid int,
@pageindex int
AS
DECLARE @startRow int,
@endRow int
SET @startRow = (@pageIndex - 1) * 10
IF @pageindex = 1
BEGIN
SELECT
TOP 10
[inviteid],
[invitecode],
[invitetype],
[createdtime],
[creator],
[creatorid],
[expiretime],
[maxcount],
[successcount]
FROM [dnt_invitation]
WHERE [creatorid]=@creatorid AND [invitetype]=3 AND [isdeleted]=0 ORDER BY [inviteid] DESC
END
ELSE
BEGIN
EXEC('
SELECT
TOP 10
[inviteid],
[invitecode],
[invitetype],
[createdtime],
[creator],
[creatorid],
[expiretime],
[maxcount],
[successcount]
FROM [dnt_invitation]
WHERE [creatorid]=@creatorid AND [invitetype]=3 AND [isdeleted]=0
AND inviteid < (SELECT MIN(inviteid) FROM ( SELECT TOP '+@startRow+' inviteid
FROM [dnt_invitation]
WHERE [creatorid]=@creatorid AND [invitetype]=3 AND [isdeleted]=0
RDER BY [inviteid] DESC
) AS T
)
ORDER BY [inviteid] DESC
')
END
GO
CREATE PROCEDURE [dnt_isinvitecodeexist]
@code nchar(8)
AS
BEGIN
SELECT COUNT(1) FROM [dnt_invitation] WHERE [invitecode]=@code AND [isdeleted] =0
END
GO
CREATE PROCEDURE [dnt_updateinvitecodesuccesscount]
@id int
AS
BEGIN
UPDATE [dnt_invitation] SET [successcount]=[successcount]+1 WHERE [inviteid]=@id
END
GO
CREATE PROCEDURE [dnt_getuserinvitecodecount]
@creatorid int
AS
BEGIN
SELECT COUNT(1) FROM [dnt_invitation] WHERE [creatorid]=@creatorid AND [invitetype]=3 AND [isdeleted]=0
END
GO
CREATE PROCEDURE [dnt_gettodayusercreatedinvitecode]
@creatorid int
AS
BEGIN
SELECT COUNT(1) FROM [dnt_invitation] WHERE [creatorid]=@creatorid AND [invitetype]=2 AND (GETDATE()- [createdtime])<1
END
GO
IF OBJECT_ID('dnt_getattachmentlistbypid','P') IS NOT NULL
DROP PROC [dnt_getattachmentlistbypid]
GO
CREATE PROCEDURE [dnt_getattachmentlistbypid]
@pidlist varchar(500)
AS
EXEC('SELECT
[aid],
[uid],
[tid],
[pid],
[postdatetime],
[readperm],
[filename],
[description],
[filetype],
[filesize],
[attachment],
[downloads],
[attachprice],
[width],
[height]
FROM [dnt_attachments]
WHERE [pid] IN ('+@pidlist+')')
GO
IF OBJECT_ID('dnt_getattachmentlistbyaid','P') IS NOT NULL
DROP PROC [dnt_getattachmentlistbyaid]
GO
CREATE PROCEDURE [dnt_getattachmentlistbyaid]
@aidlist varchar(500)
AS
SELECT [aid], [tid], [pid], [filename] FROM [dnt_attachments] WITH (NOLOCK) WHERE [aid] IN (SELECT [item] FROM [dnt_split](@aidlist, ',') )
GO
IF OBJECT_ID('dnt_deleteattachmentbyaidlist','P') IS NOT NULL
DROP PROC [dnt_deleteattachmentbyaidlist]
GO
CREATE PROCEDURE [dnt_deleteattachmentbyaidlist]
@aidlist VARCHAR(500)
AS
EXEC('DELETE FROM [dnt_attachments] WHERE [aid] IN ('+@aidlist+')')
GO
IF OBJECT_ID('dnt_deleteattachmentbytidlist','P') IS NOT NULL
DROP PROC [dnt_deleteattachmentbytidlist]
GO
CREATE PROCEDURE [dnt_deleteattachmentbytidlist]
@tidlist VARCHAR(500)
AS
EXEC('DELETE FROM [dnt_attachments] WHERE [tid] IN ('+@tidlist+')')
GO
IF OBJECT_ID('dnt_getattachmentlistbytidlist','P') IS NOT NULL
DROP PROC [dnt_getattachmentlistbytidlist]
GO
CREATE PROCEDURE [dnt_getattachmentlistbytidlist]
@tidlist VARCHAR(500)
AS
SELECT
[aid],
[filename]
FROM [dnt_attachments] WITH (NOLOCK)
WHERE [tid] IN (SELECT [item]FROM [dnt_split](@tidlist, ','))
GO
IF OBJECT_ID('dnt_updateuseronlinestates','P') IS NOT NULL
DROP PROCEDURE [dnt_updateuseronlinestates]
GO
CREATE PROCEDURE [dnt_updateuseronlinestates]
@uidlist varchar(5000) = ''
AS
EXEC ('UPDATE [dnt_users] SET [onlinestate]=0,[lastactivity]=GETDATE() WHERE [uid] IN ('+@uidlist+')')
GO
/*
修改时间：2009-11-23
修改原因：将CHARINDEX替换成了IN
测试代码：
exec [dnt_deletetopicbytidlist1] '1246471,1246472',1
*/
IF OBJECT_ID('dnt_deletetopicbytidlist1','P') IS NOT NULL
DROP PROCEDURE [dnt_deletetopicbytidlist1]
GO
CREATE PROCEDURE [dnt_deletetopicbytidlist1]
@tidlist AS VARCHAR(2000),
@chanageposts AS BIT
AS
DECLARE @postcount int
DECLARE @topiccount int
DECLARE @todaycount int
DECLARE @sqlstr nvarchar(4000)
DECLARE @fid varchar(2000)
DECLARE @posterid varchar(200)
DECLARE @tempFid int
DECLARE @tempPosterid int
DECLARE @tempLayer int
DECLARE @temppostdatetime datetime
DECLARE @tempfidlist AS VARCHAR(1000)
SET @fid = ''
SET @posterid = ''
SET @postcount=0
SET @topiccount=0
SET @todaycount=0
SET @tempfidlist = '';
IF @tidlist<>''
BEGIN
EXEC('DECLARE cu_dnt_posts CURSOR FOR SELECT [fid],[posterid],[layer],[postdatetime] FROM [dnt_posts1] WHERE [dnt_posts1].[tid] IN ('+@tidlist+')')
OPEN cu_dnt_posts
FETCH NEXT FROM cu_dnt_posts into @tempFid,@tempPosterid,@tempLayer,@temppostdatetime
WHILE @@FETCH_STATUS = 0
BEGIN
SET @postcount = @postcount + 1
IF @tempLayer = 0
BEGIN
SET @topiccount = @topiccount + 1
END
IF DATEDIFF(d,@temppostdatetime,GETDATE()) = 0
BEGIN
SET @todaycount = @todaycount + 1
END
IF CHARINDEX(',' + LTRIM(STR(@tempFid)) + ',',@fid + ',') = 0
BEGIN
--SET @fid = @fid + ',' + LTRIM(STR(@tempFid))
SELECT @tempfidlist = ISNULL([parentidlist],'') FROM [dnt_forums] WHERE [fid] = @tempFid
IF RTRIM(@tempfidlist)<>''
BEGIN
SET @fid = RTRIM(@fid) + ',' + RTRIM(@tempfidlist) + ',' + CAST(@tempFid AS VARCHAR(10))
END
ELSE
BEGIN
SET @fid =RTRIM(@fid) + ',' + CAST(@tempFid AS VARCHAR(10))
END
END
IF @chanageposts = 1
BEGIN
UPDATE [dnt_users] SET [posts] = [posts] - 1 WHERE [uid] = @tempPosterid
END
FETCH NEXT FROM cu_dnt_posts into @tempFid,@tempPosterid,@tempLayer,@temppostdatetime
END
CLOSE cu_dnt_posts
DEALLOCATE cu_dnt_posts
IF LEN(@fid)>0
BEGIN
SET @fid = SUBSTRING(@fid,2,LEN(@fid)-1)
IF @chanageposts = 1
BEGIN
UPDATE [dnt_statistics] SET [totaltopic]=[totaltopic] - @topiccount, [totalpost]=[totalpost] - @postcount
EXEC('UPDATE [dnt_forums]
SET [posts]=[posts] - ' + @postcount + ',
[topics]=[topics] - ' + @topiccount + ',
[todayposts] = [todayposts] - ' + @todaycount + '
WHERE [fid] IN ('+@fid+')')
END
EXEC('DELETE FROM [dnt_favorites] WHERE [tid] IN ('+@tidlist+') AND [typeid]=0')
EXEC('DELETE FROM [dnt_polls] WHERE [tid] IN ('+@tidlist+')')
EXEC('DELETE FROM [dnt_posts1] WHERE [tid] IN ('+@tidlist+')')
EXEC('DELETE FROM [dnt_mytopics] WHERE [tid] IN ('+@tidlist+')')
END
EXEC('DELETE FROM [dnt_topics] WHERE [closed] IN ('+@tidlist+') OR [tid] IN ('+@tidlist+')')
EXEC('UPDATE [dnt_tags] SET [count]=[count]-1, [fcount]=[fcount]-1 WHERE [tagid] IN (SELECT [tagid] FROM [dnt_topictags] WHERE [tid] IN ('+@tidlist+'))')
EXEC('DELETE FROM [dnt_topictags] WHERE [tid] IN ('+@tidlist+')')
EXEC('DELETE FROM [dnt_topictagcaches] WHERE [tid] IN ('+@tidlist+') OR [linktid] IN ('+@tidlist+')')
END
GO
/*修改存储过程的名字*/
IF OBJECT_ID('dnt_getattachmentlistbypid','P') IS NOT NULL
DROP PROC [dnt_getattachmentlistbypid]
GO
IF OBJECT_ID('dnt_getattachmentlistbypidlist','P') IS NOT NULL
DROP PROC [dnt_getattachmentlistbypidlist]
GO
CREATE PROCEDURE [dnt_getattachmentlistbypidlist]
@pidlist varchar(500)
AS
SELECT
[aid],
[uid],
[tid],
[pid],
[postdatetime],
[readperm],
[filename],
[description],
[filetype],
[filesize],
[attachment],
[downloads],
[attachprice],
[width],
[height]
FROM [dnt_attachments] WITH (NOLOCK)
WHERE [dnt_attachments].[pid] IN (SELECT [item]FROM [dnt_split](@pidlist, ',') )
GO
/*修改存储过程的名字*/
IF OBJECT_ID('dnt_getattachenmtlistbypid','P') IS NOT NULL
DROP PROC [dnt_getattachenmtlistbypid]
GO
IF OBJECT_ID('dnt_getattachmentlistbypid','P') IS NOT NULL
DROP PROC [dnt_getattachmentlistbypid]
GO
CREATE PROCEDURE [dnt_getattachmentlistbypid]
@pid INT
AS
SELECT
[aid],
[uid],
[tid],
[pid],
[postdatetime],
[readperm],
[filename],
[description],
[filetype],
[filesize],
[attachment],
[downloads],
[attachprice],
[width],
[height]
FROM [dnt_attachments]
WHERE [pid]=@pid
GO
IF OBJECT_ID('dnt_getattachpaymentlogbyuid','P') IS NOT NULL
DROP PROC [dnt_getattachpaymentlogbyuid]
GO
CREATE PROCEDURE [dnt_getattachpaymentlogbyuid]
@attachidlist varchar(500),
@uid int
AS
EXEC('
SELECT
[aid]
FROM [dnt_attachpaymentlog]
WHERE [uid] = '+@uid+' AND [dnt_attachpaymentlog].[aid] IN ('+@attachidlist+')
')
GO
IF OBJECT_ID('dnt_getindexforumlist','P') IS NOT NULL
DROP PROC [dnt_getindexforumlist]
GO
CREATE PROCEDURE [dnt_getindexforumlist]
AS
SELECT CASE WHEN DATEDIFF(n, [lastpost], GETDATE())<600 THEN 'new' ELSE 'old' END AS [havenew],
[dnt_forums].[allowbbcode],
[dnt_forums].[allowblog],
[dnt_forums].[alloweditrules],
[dnt_forums].[allowhtml],
[dnt_forums].[allowimgcode],
[dnt_forums].[allowpostspecial],
[dnt_forums].[allowrss],
[dnt_forums].[allowsmilies],
[dnt_forums].[allowspecialonly],
[dnt_forums].[allowtag],
[dnt_forums].[allowthumbnail],
[dnt_forums].[autoclose],
[dnt_forums].[colcount],
[dnt_forums].[curtopics],
[dnt_forums].[disablewatermark],
[dnt_forums].[displayorder],
[dnt_forums].[fid],
[dnt_forums].[inheritedmod],
[dnt_forums].[istrade],
[dnt_forums].[jammer],
[dnt_forums].[lastpost],
[dnt_forums].[lastposter],
[dnt_forums].[lastposterid],
[dnt_forums].[lasttid],
[dnt_forums].[lasttitle],
[dnt_forums].[layer],
[dnt_forums].[modnewposts],
[dnt_forums].[name],
[dnt_forums].[parentid],
[dnt_forums].[parentidlist],
[dnt_forums].[pathlist],
[dnt_forums].[posts],
[dnt_forums].[recyclebin],
[dnt_forums].[status],
[dnt_forums].[subforumcount],
[dnt_forums].[templateid],
[dnt_forums].[todayposts],
[dnt_forums].[topics],
[dnt_forumfields].[applytopictype],
[dnt_forumfields].[attachextensions],
[dnt_forumfields].[description],
[dnt_forumfields].[fid],
[dnt_forumfields].[getattachperm],
[dnt_forumfields].[icon],
[dnt_forumfields].[moderators],
[dnt_forumfields].[password],
[dnt_forumfields].[permuserlist],
[dnt_forumfields].[postattachperm],
[dnt_forumfields].[postbytopictype],
[dnt_forumfields].[postcredits],
[dnt_forumfields].[postperm],
[dnt_forumfields].[redirect],
[dnt_forumfields].[replycredits],
[dnt_forumfields].[replyperm],
[dnt_forumfields].[rewritename],
[dnt_forumfields].[rules],
[dnt_forumfields].[seodescription],
[dnt_forumfields].[seokeywords],
[dnt_forumfields].[topictypeprefix],
[dnt_forumfields].[topictypes],
[dnt_forumfields].[viewbytopictype],
[dnt_forumfields].[viewperm]
FROM [dnt_forums]
LEFT JOIN [dnt_forumfields] ON [dnt_forums].[fid]=[dnt_forumfields].[fid]
WHERE [dnt_forums].[status] > 0 AND [layer] <= 1
AND [dnt_forums].[parentid] NOT IN (SELECT [fid] FROM [dnt_forums] WHERE [status] < 1 AND [layer] = 0) ORDER BY [displayorder]
GO
IF OBJECT_ID('dnt_resetforumstopics','P') IS NOT NULL
DROP PROC [dnt_resetforumstopics]
GO
CREATE PROC [dnt_resetforumstopics]
AS
--更新所有版块的主题数,不包含子版块
UPDATE dnt_forums
SET curtopics=rightTable.sumtopics,topics = rightTable.sumtopics
FROM dnt_forums
INNER JOIN (SELECT fid,count(tid) sumtopics FROM dnt_topics GROUP BY fid) AS rightTable
ON dnt_forums.fid=rightTable.fid
WHERE dnt_forums.fid=rightTable.fid
DECLARE @maxlayer INT
SELECT @maxlayer = MAX(layer) FROM dnt_forums
--倒序更新，从最底层版块更新到最顶层
WHILE @maxlayer > 0
BEGIN
--将子版块中的帖子总数累加并更新到父版块中
UPDATE dnt_forums
SET topics=topics+rightTable.sumtopics
FROM dnt_forums AS leftTable
INNER JOIN (SELECT parentid,SUM(topics) sumtopics FROM dnt_forums WHERE layer=@maxlayer GROUP BY parentid) AS rightTable
ON leftTable.fid=rightTable.parentid
WHERE leftTable.fid=rightTable.parentid
SET @maxlayer = @maxlayer - 1
END
GO
if exists (select * from sysobjects where id = object_id(N'[dnt_createorder]') and OBJECTPROPERTY(id,N'IsProcedure') = 1)
drop procedure [dnt_createorder]
GO
CREATE PROCEDURE [dnt_createorder]
@ordercode char(32),
@uid int,
@buyer char(20),
@paytype tinyint,
@price decimal(18,2),
@orderstatus tinyint,
@credit tinyint,
@amount int
AS
BEGIN
INSERT INTO [dnt_orders]([ordercode],[uid],[buyer],[paytype],[price],[orderstatus],
[credit],[amount]) VALUES(@ordercode,@uid,@buyer,@paytype,@price,@orderstatus,@credit,@amount)
END
GO
if exists (select * from sysobjects where id = object_id(N'[dnt_getorderbyordercode]') and OBJECTPROPERTY(id,N'IsProcedure') = 1)
drop procedure [dnt_getorderbyordercode]
GO
CREATE PROCEDURE [dnt_getorderbyordercode]
@ordercode char(32)
AS
BEGIN
SELECT [orderid],[ordercode],[uid],[buyer],[paytype],[tradeno],[price],[orderstatus],[createdtime],[confirmedtime]
,[credit],[amount] FROM [dnt_orders] WHERE [ordercode]=@ordercode
END
GO
if exists (select * from sysobjects where id = object_id(N'[dnt_getorderlist]') and OBJECTPROPERTY(id,N'IsProcedure') = 1)
drop procedure [dnt_getorderlist]
GO
CREATE PROCEDURE [dnt_getorderlist]
@searchcondition varchar(1000)
AS
BEGIN
DECLARE @script VARCHAR(1000)
SET @script= 'SELECT TOP 20 [orderid],[ordercode],[uid],[buyer],[paytype],[tradeno],[price],[orderstatus],[createdtime],[confirmedtime]
,[credit],[amount] FROM [dnt_orders] '+@searchcondition+' ORDER BY [orderid] DESC'
EXEC(@script)
END
GO
if exists (select * from sysobjects where id = object_id(N'[dnt_getorderscount]') and OBJECTPROPERTY(id,N'IsProcedure') = 1)
drop procedure [dnt_getorderscount]
GO
CREATE PROCEDURE [dnt_getorderscount]
@searchcondition varchar(1000)
AS
BEGIN
DECLARE @script VARCHAR(1000)
SET @script= 'SELECT COUNT([orderid]) FROM [dnt_orders] '+@searchcondition
EXEC(@script)
END
GO
if exists (select * from sysobjects where id = object_id(N'[dnt_updateorderinfo]') and OBJECTPROPERTY(id,N'IsProcedure') = 1)
drop procedure [dnt_updateorderinfo]
GO
CREATE PROCEDURE [dnt_updateorderinfo]
@orderid int,
@tradeno char(32),
@orderstatus tinyint,
@confirmedtime smalldatetime
AS
BEGIN
UPDATE [dnt_orders] SET [tradeno]=@tradeno,[orderstatus]=@orderstatus,[confirmedtime]=@confirmedtime WHERE [orderid]=@orderid
END
GO
if exists (select * from sysobjects where id = object_id(N'[dnt_clearexpirecreditorders]') and OBJECTPROPERTY(id,N'IsProcedure') = 1)
drop procedure [dnt_clearexpirecreditorders]
GO
CREATE PROCEDURE [dnt_clearexpirecreditorders]
AS
BEGIN
DELETE [dnt_orders] WHERE [orderstatus] < 1 AND [createdtime] < (GETDATE()-15)
END
GO
IF OBJECT_ID('dnt_addparentforumtopics','P') IS NOT NULL
DROP PROC [dnt_addparentforumtopics]
GO
CREATE PROCEDURE [dnt_addparentforumtopics]
@topics int,
@fpidlist nvarchar(100)
AS
UPDATE [dnt_forums] SET [topics] = [topics] + @topics WHERE [fid] IN (SELECT [item]FROM [dnt_split](@fpidlist, ',') )
GO
IF OBJECT_ID('dnt_updateusercredits','P') IS NOT NULL
DROP PROC [dnt_updateusercredits]
GO
CREATE PROCEDURE [dnt_updateusercredits]
@uid INT
AS
UPDATE [dnt_users] SET [credits] = extcredits1 + posts + digestposts* 5 WHERE [uid] = @uid
GO
IF OBJECT_ID('[dnt_gethottopicscount]','P') IS NOT NULL
DROP PROC [dnt_gethottopicscount]
GO
CREATE PROCEDURE [dnt_gethottopicscount]
@fid int,
@timebetween int
AS
DECLARE @strSQL varchar(4000)
DECLARE @strSQLByFid nvarchar(200)
DECLARE @strSQLByDate nvarchar(200)
DECLARE @pagetop int
IF @fid<>0
SET @strSQLByFid=' AND [fid]='+STR(@fid)+''
IF @timebetween<>0
SET @strSQLByDate=' AND DATEDIFF(DAY,[postdatetime],GETDATE())<=' + STR(@timebetween)
SET @strSQL = 'SELECT COUNT(1) FROM [dnt_topics] WHERE 1=1 '+@strSQLByFid+@strSQLByDate
EXEC(@strSQL)
GO
IF OBJECT_ID('[dnt_gethottopicslist]','P') IS NOT NULL
DROP PROC [dnt_gethottopicslist]
GO
CREATE PROCEDURE [dnt_gethottopicslist]
@pagesize int,
@pageindex int,
@fid int,
@showtype varchar(100) ,
@timebetween int
AS
DECLARE @strSQL varchar(4000)
DECLARE @strSQLByFid nvarchar(200)=''
DECLARE @strSQLByDate nvarchar(200)=''
DECLARE @pagetop int
SET @pagetop = (@pageindex-1)*@pagesize
IF @fid<>0
BEGIN
SET @strSQLByFid='AND t.[fid]='+STR(@fid)
END
IF @timebetween<>0
SET @strSQLByDate=' AND DATEDIFF(DAY,[postdatetime],GETDATE())<=' + STR(@timebetween)
IF @pageindex=1
BEGIN
SET @strSQL = 'SELECT TOP ' + STR(@pagesize) +' [t].[tid],[t].[fid],[t].[iconid],[t].[typeid],
[t].[readperm],[t].[price],[t].[poster],[t].[posterid],[t].[title],
[t].[attention],[t].[postdatetime],[t].[lastpost],[t].[lastpostid],[t].[lastposter],[t].[lastposterid],
[t].[views],[t].[replies],[t].[displayorder],[t].[highlight],[t].[digest],[t].[rate],[t].[hide],
[t].[attachment],[t].[moderated],[t].[closed],[t].[magic],[t].[identify],[t].[special],
f.[name] FROM [dnt_topics] t LEFT JOIN [dnt_forums] f ON t.[fid] = f.[fid]
WHERE 1=1 '+@strSQLByFid+@strSQLByDate+' ORDER BY ['+@showtype+'] DESC'
END
ELSE
BEGIN
SET @strSQL = 'SELECT TOP ' + STR(@pagesize) +' [t].[tid],[t].[fid],[t].[iconid],[t].[typeid],[t].[readperm],[t].[price],
[t].[poster],[t].[posterid],[t].[title],[t].[attention],[t].[postdatetime],[t].[lastpost],[t].[lastpostid],
[t].[lastposter],[t].[lastposterid],[t].[views],[t].[replies],[t].[displayorder],[t].[highlight],[t].[digest],
[t].[rate],[t].[hide],[t].[attachment],[t].[moderated],[t].[closed],[t].[magic],[t].[identify],[t].[special],
f.[name] FROM [dnt_topics] t LEFT JOIN [dnt_forums] f ON t.[fid] = f.[fid]
WHERE 1=1 ' +@strSQLByFid +@strSQLByDate+' AND [tid] NOT IN (SELECT TOP '+ STR(@pagetop)+' [tid] FROM [dnt_topics]
WHERE 1=1 ' +@strSQLByFid +@strSQLByDate+' ORDER BY ['+@showtype+'] DESC) ORDER BY ['+@showtype+'] DESC'
END
EXEC(@strSQL)
GO
IF OBJECT_ID('dnt_resettodayposts','P') IS NOT NULL
DROP PROC [dnt_resettodayposts]
GO
CREATE PROC [dnt_resettodayposts]
AS
UPDATE dnt_forums SET todayposts = 0;
DECLARE @tableid int;
SELECT @tableid = MAX(id) FROM dnt_tablelist;
EXEC('
UPDATE dnt_forums
SET todayposts=rightTable.pidcount
FROM dnt_forums
INNER JOIN (SELECT fid,COUNT(pid) pidcount FROM dnt_posts'+ @tableid +' WHERE DATEDIFF(DAY,postdatetime,GETDATE())=0 GROUP BY fid ) AS rightTable
ON dnt_forums.fid=rightTable.fid
WHERE dnt_forums.fid=rightTable.fid
')