setup2.2.sql
上传用户:wenllgg125
上传日期:2020-04-09
资源大小:7277k
文件大小:179k
源码类别:

SCSI/ASPI

开发平台:

Others

  1. SET @pagetop = (@pageindex-1)*@pagesize
  2. IF @pageindex = 1
  3. EXEC('SELECT TOP ' + @pagesize + '
  4. [dnt_posts1].[pid], 
  5. [dnt_posts1].[fid], 
  6. [dnt_posts1].[title], 
  7. [dnt_posts1].[layer],
  8. [dnt_posts1].[message], 
  9. [dnt_posts1].[ip], 
  10. [dnt_posts1].[lastedit], 
  11. [dnt_posts1].[postdatetime], 
  12. [dnt_posts1].[attachment], 
  13. [dnt_posts1].[poster], 
  14. [dnt_posts1].[posterid], 
  15. [dnt_posts1].[invisible], 
  16. [dnt_posts1].[usesig], 
  17. [dnt_posts1].[htmlon], 
  18. [dnt_posts1].[smileyoff], 
  19. [dnt_posts1].[parseurloff], 
  20. [dnt_posts1].[bbcodeoff], 
  21. [dnt_posts1].[rate], 
  22. [dnt_posts1].[ratetimes], 
  23. [dnt_users].[nickname],  
  24. [dnt_users].[username], 
  25. [dnt_users].[groupid], 
  26. [dnt_users].[spaceid],
  27. [dnt_users].[gender],
  28. [dnt_users].[bday],
  29. [dnt_users].[email], 
  30. [dnt_users].[showemail], 
  31. [dnt_users].[digestposts], 
  32. [dnt_users].[credits], 
  33. [dnt_users].[extcredits1], 
  34. [dnt_users].[extcredits2], 
  35. [dnt_users].[extcredits3], 
  36. [dnt_users].[extcredits4], 
  37. [dnt_users].[extcredits5], 
  38. [dnt_users].[extcredits6], 
  39. [dnt_users].[extcredits7], 
  40. [dnt_users].[extcredits8], 
  41. [dnt_users].[posts], 
  42. [dnt_users].[joindate], 
  43. [dnt_users].[onlinestate],
  44. [dnt_users].[lastactivity], 
  45. [dnt_users].[invisible],
  46. [dnt_users].[oltime],
  47. [dnt_users].[lastvisit], 
  48. [dnt_userfields].[avatar], 
  49. [dnt_userfields].[avatarwidth], 
  50. [dnt_userfields].[avatarheight],
  51. [dnt_userfields].[medals],
  52. [dnt_userfields].[sightml] AS signature, 
  53. [dnt_userfields].[location], 
  54. [dnt_userfields].[customstatus], 
  55. [dnt_userfields].[website], 
  56. [dnt_userfields].[icq], 
  57. [dnt_userfields].[qq], 
  58. [dnt_userfields].[msn], 
  59. [dnt_userfields].[yahoo], 
  60. [dnt_userfields].[skype] 
  61.  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]')
  62. ELSE
  63. EXEC('SELECT TOP ' + @pagesize + ' 
  64. [dnt_posts1].[pid], 
  65. [dnt_posts1].[fid], 
  66. [dnt_posts1].[title], 
  67. [dnt_posts1].[layer],
  68. [dnt_posts1].[message], 
  69. [dnt_posts1].[ip], 
  70. [dnt_posts1].[lastedit], 
  71. [dnt_posts1].[postdatetime], 
  72. [dnt_posts1].[attachment], 
  73. [dnt_posts1].[poster], 
  74. [dnt_posts1].[posterid], 
  75. [dnt_posts1].[invisible], 
  76. [dnt_posts1].[usesig], 
  77. [dnt_posts1].[htmlon], 
  78. [dnt_posts1].[smileyoff], 
  79. [dnt_posts1].[parseurloff], 
  80. [dnt_posts1].[bbcodeoff], 
  81. [dnt_posts1].[rate], 
  82. [dnt_posts1].[ratetimes],
  83. [dnt_users].[nickname],  
  84. [dnt_users].[username], 
  85. [dnt_users].[groupid], 
  86. [dnt_users].[spaceid],
  87. [dnt_users].[gender],
  88. [dnt_users].[bday],
  89. [dnt_users].[email], 
  90. [dnt_users].[showemail], 
  91. [dnt_users].[digestposts], 
  92. [dnt_users].[credits], 
  93. [dnt_users].[extcredits1], 
  94. [dnt_users].[extcredits2], 
  95. [dnt_users].[extcredits3], 
  96. [dnt_users].[extcredits4], 
  97. [dnt_users].[extcredits5], 
  98. [dnt_users].[extcredits6], 
  99. [dnt_users].[extcredits7], 
  100. [dnt_users].[extcredits8], 
  101. [dnt_users].[posts], 
  102. [dnt_users].[joindate], 
  103. [dnt_users].[onlinestate],
  104. [dnt_users].[lastactivity],
  105. [dnt_users].[oltime],
  106. [dnt_users].[lastvisit],  
  107. [dnt_users].[invisible] AS [userinvisible], 
  108. [dnt_userfields].[avatar], 
  109. [dnt_userfields].[avatarwidth], 
  110. [dnt_userfields].[avatarheight], 
  111. [dnt_userfields].[medals],
  112. [dnt_userfields].[sightml] AS [signature], 
  113. [dnt_userfields].[location], 
  114. [dnt_userfields].[customstatus], 
  115. [dnt_userfields].[website], 
  116. [dnt_userfields].[icq], 
  117. [dnt_userfields].[qq], 
  118. [dnt_userfields].[msn], 
  119. [dnt_userfields].[yahoo], 
  120. [dnt_userfields].[skype] 
  121. 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]')
  122. GO
  123. IF OBJECT_ID('[dnt_getpostlistbycondition1]','P') IS NOT NULL
  124. DROP PROC [dnt_getpostlistbycondition1]
  125. GO
  126. CREATE PROCEDURE [dnt_getpostlistbycondition1]
  127. @tid int,
  128. @pagesize int,
  129. @pageindex int,
  130. @posterid int
  131. AS
  132. DECLARE @pagetop int
  133. SET @pagetop = (@pageindex-1)*@pagesize
  134. IF @pageindex = 1
  135. EXEC('SELECT TOP ' + @pagesize + '
  136. [dnt_posts1].[pid], 
  137. [dnt_posts1].[fid], 
  138. [dnt_posts1].[title], 
  139. [dnt_posts1].[layer],
  140. [dnt_posts1].[message], 
  141. [dnt_posts1].[ip], 
  142. [dnt_posts1].[lastedit], 
  143. [dnt_posts1].[postdatetime], 
  144. [dnt_posts1].[attachment], 
  145. [dnt_posts1].[poster], 
  146. [dnt_posts1].[posterid], 
  147. [dnt_posts1].[invisible], 
  148. [dnt_posts1].[usesig], 
  149. [dnt_posts1].[htmlon], 
  150. [dnt_posts1].[smileyoff], 
  151. [dnt_posts1].[parseurloff], 
  152. [dnt_posts1].[bbcodeoff], 
  153. [dnt_posts1].[rate], 
  154. [dnt_posts1].[ratetimes], 
  155. [dnt_users].[nickname],  
  156. [dnt_users].[username], 
  157. [dnt_users].[groupid], 
  158. [dnt_users].[spaceid],
  159. [dnt_users].[gender],
  160. [dnt_users].[bday],
  161. [dnt_users].[email], 
  162. [dnt_users].[showemail], 
  163. [dnt_users].[digestposts], 
  164. [dnt_users].[credits], 
  165. [dnt_users].[extcredits1], 
  166. [dnt_users].[extcredits2], 
  167. [dnt_users].[extcredits3], 
  168. [dnt_users].[extcredits4], 
  169. [dnt_users].[extcredits5], 
  170. [dnt_users].[extcredits6], 
  171. [dnt_users].[extcredits7], 
  172. [dnt_users].[extcredits8], 
  173. [dnt_users].[posts], 
  174. [dnt_users].[joindate], 
  175. [dnt_users].[onlinestate],
  176. [dnt_users].[lastactivity],
  177. [dnt_users].[oltime],
  178. [dnt_users].[lastvisit],  
  179. [dnt_users].[invisible], 
  180. [dnt_userfields].[avatar], 
  181. [dnt_userfields].[avatarwidth],
  182. [dnt_userfields].[avatarheight],
  183. [dnt_userfields].[medals],
  184. [dnt_userfields].[sightml] AS signature, 
  185. [dnt_userfields].[location], 
  186. [dnt_userfields].[customstatus], 
  187. [dnt_userfields].[website], 
  188. [dnt_userfields].[icq], 
  189. [dnt_userfields].[qq], 
  190. [dnt_userfields].[msn], 
  191. [dnt_userfields].[yahoo], 
  192. [dnt_userfields].[skype] 
  193.  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]')
  194. ELSE
  195. EXEC('SELECT TOP ' + @pagesize + ' 
  196. [dnt_posts1].[pid], 
  197. [dnt_posts1].[fid], 
  198. [dnt_posts1].[title], 
  199. [dnt_posts1].[layer],
  200. [dnt_posts1].[message], 
  201. [dnt_posts1].[ip], 
  202. [dnt_posts1].[lastedit], 
  203. [dnt_posts1].[postdatetime], 
  204. [dnt_posts1].[attachment], 
  205. [dnt_posts1].[poster], 
  206. [dnt_posts1].[posterid], 
  207. [dnt_posts1].[invisible], 
  208. [dnt_posts1].[usesig], 
  209. [dnt_posts1].[htmlon], 
  210. [dnt_posts1].[smileyoff], 
  211. [dnt_posts1].[parseurloff], 
  212. [dnt_posts1].[bbcodeoff], 
  213. [dnt_posts1].[rate], 
  214. [dnt_posts1].[ratetimes],
  215. [dnt_users].[nickname],  
  216. [dnt_users].[username], 
  217. [dnt_users].[groupid], 
  218. [dnt_users].[spaceid],
  219. [dnt_users].[gender],
  220. [dnt_users].[bday],
  221. [dnt_users].[email], 
  222. [dnt_users].[showemail], 
  223. [dnt_users].[digestposts], 
  224. [dnt_users].[credits], 
  225. [dnt_users].[extcredits1], 
  226. [dnt_users].[extcredits2], 
  227. [dnt_users].[extcredits3], 
  228. [dnt_users].[extcredits4], 
  229. [dnt_users].[extcredits5], 
  230. [dnt_users].[extcredits6], 
  231. [dnt_users].[extcredits7], 
  232. [dnt_users].[extcredits8], 
  233. [dnt_users].[posts], 
  234. [dnt_users].[joindate], 
  235. [dnt_users].[onlinestate], 
  236. [dnt_users].[lastactivity], 
  237. [dnt_users].[invisible],
  238. [dnt_users].[oltime],
  239. [dnt_users].[lastvisit], 
  240. [dnt_userfields].[avatar], 
  241. [dnt_userfields].[avatarwidth], 
  242. [dnt_userfields].[avatarheight], 
  243. [dnt_userfields].[medals],
  244. [dnt_userfields].[sightml] AS signature, 
  245. [dnt_userfields].[location], 
  246. [dnt_userfields].[customstatus], 
  247. [dnt_userfields].[website], 
  248. [dnt_userfields].[icq], 
  249. [dnt_userfields].[qq], 
  250. [dnt_userfields].[msn], 
  251. [dnt_userfields].[yahoo], 
  252. [dnt_userfields].[skype] 
  253. 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]')
  254. GO
  255. IF OBJECT_ID('dnt_getattachpaymentlogbyaid','P') IS NOT NULL
  256. DROP PROC [dnt_getattachpaymentlogbyaid]
  257. GO
  258. CREATE PROCEDURE [dnt_getattachpaymentlogbyaid]
  259. @aid int
  260. AS
  261. SELECT 
  262. [id],
  263. [uid],
  264. [username],
  265. [aid],
  266. [authorid],
  267. [postdatetime],
  268. [amount],
  269. [netamount]
  270. FROM [dnt_attachpaymentlog]
  271. WHERE [aid] = @aid
  272. GO
  273. IF OBJECT_ID('dnt_getonlineuercount','P') IS NOT NULL
  274. DROP PROC [dnt_getonlineuercount]
  275. GO
  276. CREATE PROCEDURE [dnt_getonlineuercount]
  277. AS
  278. SELECT COUNT(olid) FROM [dnt_online]
  279. GO
  280. IF OBJECT_ID('dnt_getalltoptopiclist','P') IS NOT NULL
  281. DROP PROC [dnt_getalltoptopiclist]
  282. GO
  283. CREATE PROCEDURE [dnt_getalltoptopiclist]
  284. AS
  285. SELECT 
  286. [tid],
  287. [displayorder],
  288. [fid] 
  289. FROM [dnt_topics] 
  290. WHERE [displayorder] > 0 ORDER BY [fid]
  291. GO
  292. IF OBJECT_ID('dnt_getnousedforumattachment','P') IS NOT NULL
  293. DROP PROC [dnt_getnousedforumattachment]
  294. GO
  295. CREATE PROCEDURE [dnt_getnousedforumattachment]
  296. AS
  297. SELECT 
  298. [aid],
  299. [uid],
  300. [tid],
  301. [pid],
  302. [postdatetime],
  303. [readperm],
  304. [filename],
  305. [description],
  306. [filetype],
  307. [filesize],
  308. [attachment],
  309. [downloads],
  310. [attachprice],
  311. [width],
  312. [height]
  313. FROM [dnt_attachments] 
  314. WHERE [tid]= 0 AND [pid]=0 AND DATEDIFF(n, postdatetime, GETDATE()) > 30 
  315. GO
  316. IF OBJECT_ID('dnt_deletenousedforumattachment','P') IS NOT NULL
  317. DROP PROC [dnt_deletenousedforumattachment]
  318. GO
  319. CREATE PROCEDURE [dnt_deletenousedforumattachment]
  320. AS
  321. DELETE FROM [dnt_attachments] 
  322. WHERE [tid]= 0 AND [pid]=0 AND DATEDIFF(n, postdatetime, GETDATE()) > 30
  323. GO
  324. IF OBJECT_ID('dnt_getnousedattachmentlistbytid','P') IS NOT NULL
  325. DROP PROC [dnt_getnousedattachmentlistbytid]
  326. GO
  327. CREATE PROCEDURE [dnt_getnousedattachmentlistbytid]
  328. @uid INT
  329. AS
  330. SELECT 
  331. [aid], 
  332. [attachment] 
  333. FROM [dnt_attachments] 
  334. WHERE [uid]= @uid AND [tid]=0 AND [pid]=0
  335. GO
  336. IF OBJECT_ID('dnt_updateattachmenttidtoanothertopic','P') IS NOT NULL
  337. DROP PROC [dnt_updateattachmenttidtoanothertopic]
  338. GO
  339. CREATE PROCEDURE [dnt_updateattachmenttidtoanothertopic]
  340. @tid INT,
  341. @oldtid INT
  342. AS
  343. UPDATE [dnt_attachments] SET [tid]=@tid WHERE [tid]=@oldtid
  344. GO
  345. IF OBJECT_ID('dnt_getfirstimageattachbytid','P') IS NOT NULL
  346. DROP PROC [dnt_getfirstimageattachbytid]
  347. GO
  348. CREATE PROCEDURE [dnt_getfirstimageattachbytid]
  349. @tid INT
  350. AS
  351. SELECT TOP 1 
  352. [aid],
  353. [uid],
  354. [tid],
  355. [pid],
  356. [postdatetime],
  357. [readperm],
  358. [filename],
  359. [description],
  360. [filetype],
  361. [filesize],
  362. [attachment],
  363. [downloads],
  364. [attachprice],
  365. [width],
  366. [height] 
  367. FROM [dnt_attachments] 
  368. WHERE [tid]=@tid AND LEFT([filetype], 5)='image' ORDER BY [aid]
  369. GO
  370. IF OBJECT_ID('dnt_getattachmentlistbypid','P') IS NOT NULL
  371. DROP PROC [dnt_getattachmentlistbypid]
  372. GO
  373. CREATE PROCEDURE [dnt_getattachmentlistbypid]
  374. @pidlist varchar(500)
  375. AS
  376. SELECT 
  377. [aid],
  378. [uid],
  379. [tid],
  380. [pid],
  381. [postdatetime],
  382. [readperm],
  383. [filename],
  384. [description],
  385. [filetype],
  386. [filesize],
  387. [attachment],
  388. [downloads],
  389. [attachprice],
  390. [width],
  391. [height] 
  392. FROM [dnt_attachments] 
  393. WHERE CHARINDEX(','+RTRIM([dnt_attachments].[pid])+',', ','+@pidlist+',')>0
  394. GO
  395. IF OBJECT_ID('dnt_getattachmentlistbyaid','P') IS NOT NULL
  396. DROP PROC [dnt_getattachmentlistbyaid]
  397. GO
  398. CREATE PROCEDURE [dnt_getattachmentlistbyaid]
  399. @aidlist varchar(500)
  400. AS
  401. SELECT 
  402. [aid],
  403. [tid],
  404. [pid],
  405. [filename]
  406. FROM [dnt_attachments] 
  407. WHERE CHARINDEX(','+RTRIM([dnt_attachments].[aid])+',', ','+@aidlist+',')>0
  408. GO
  409. IF OBJECT_ID('dnt_updateattachment','P') IS NOT NULL
  410. DROP PROC [dnt_updateattachment]
  411. GO
  412. CREATE PROCEDURE [dnt_updateattachment]
  413. @readperm INT,
  414. @description NCHAR(100),
  415. @attachprice INT,
  416. @aid INT
  417. AS
  418. UPDATE [dnt_attachments] 
  419. SET [readperm] = @readperm, [description] = @description, [attachprice] = @attachprice 
  420. WHERE [aid] = @aid
  421. GO
  422. IF OBJECT_ID('dnt_updateattachmentinfo','P') IS NOT NULL
  423. DROP PROC [dnt_updateattachmentinfo]
  424. GO
  425. CREATE PROCEDURE [dnt_updateattachmentinfo]
  426. @readperm INT,
  427. @description NCHAR(100),
  428. @aid INT
  429. AS
  430. UPDATE [dnt_attachments] 
  431. SET [readperm] = @readperm, [description] = @description 
  432. WHERE [aid] = @aid
  433. GO
  434. IF OBJECT_ID('dnt_updateallfieldattachmentinfo','P') IS NOT NULL
  435. DROP PROC [dnt_updateallfieldattachmentinfo]
  436. GO
  437. CREATE PROCEDURE [dnt_updateallfieldattachmentinfo]
  438. @postdatetime DATETIME,
  439. @readperm INT,
  440. @filename NCHAR(100),
  441. @filetype NCHAR(50),
  442. @description NCHAR(100),
  443. @filesize INT,
  444. @attachment NCHAR(100),
  445. @downloads INT,
  446. @tid INT,
  447. @pid INT,
  448. @aid INT,
  449. @attachprice INT,
  450. @width INT,
  451. @height INT
  452. AS
  453. UPDATE [dnt_attachments] 
  454. SET 
  455. [postdatetime] = @postdatetime, 
  456. [readperm] = @readperm, 
  457. [filename] = @filename, 
  458. [description] = @description, 
  459. [filetype] = @filetype, 
  460. [filesize] = @filesize, 
  461. [attachment] = @attachment, 
  462. [downloads] = @downloads, 
  463. [tid]=@tid, 
  464. [pid]=@pid, 
  465. [attachprice]=@attachprice, 
  466. [width]=@width, 
  467. [height]=@height 
  468. WHERE [aid]=@aid
  469. GO
  470. IF OBJECT_ID('dnt_deleteattachmentbypid','P') IS NOT NULL
  471. DROP PROC [dnt_deleteattachmentbypid]
  472. GO
  473. CREATE PROCEDURE [dnt_deleteattachmentbypid]
  474. @pid INT
  475. AS
  476. DELETE FROM [dnt_attachments] 
  477. WHERE [pid]=@pid
  478. GO
  479. IF OBJECT_ID('dnt_deleteattachmentbyaidlist','P') IS NOT NULL
  480. DROP PROC [dnt_deleteattachmentbyaidlist]
  481. GO
  482. CREATE PROCEDURE [dnt_deleteattachmentbyaidlist]
  483. @aidlist VARCHAR(500)
  484. AS
  485. DELETE FROM [dnt_attachments] WHERE CHARINDEX(','+RTRIM([aid])+',',','+@aidlist+',') > 0
  486. GO
  487. IF OBJECT_ID('dnt_deleteattachmentbyaid','P') IS NOT NULL
  488. DROP PROC [dnt_deleteattachmentbyaid]
  489. GO
  490. CREATE PROCEDURE [dnt_deleteattachmentbyaid]
  491. @aid INT
  492. AS
  493. DELETE FROM [dnt_attachments] 
  494. WHERE [aid]=@aid
  495. GO
  496. IF OBJECT_ID('dnt_deleteattachmentbytid','P') IS NOT NULL
  497. DROP PROC [dnt_deleteattachmentbytid]
  498. GO
  499. CREATE PROCEDURE [dnt_deleteattachmentbytid]
  500. @tid INT
  501. AS
  502. DELETE FROM [dnt_attachments] WHERE [tid] = @tid
  503. GO
  504. IF OBJECT_ID('dnt_deleteattachmentbytidlist','P') IS NOT NULL
  505. DROP PROC [dnt_deleteattachmentbytidlist]
  506. GO
  507. CREATE PROCEDURE [dnt_deleteattachmentbytidlist]
  508. @tidlist VARCHAR(500)
  509. AS
  510. DELETE FROM [dnt_attachments] WHERE CHARINDEX(','+RTRIM([tid])+',',','+@tidlist+',') > 0
  511. GO
  512. IF OBJECT_ID('dnt_getattachmentlistbytidlist','P') IS NOT NULL
  513. DROP PROC [dnt_getattachmentlistbytidlist]
  514. GO
  515. CREATE PROCEDURE [dnt_getattachmentlistbytidlist]
  516. @tidlist VARCHAR(500)
  517. AS
  518. SELECT 
  519. [aid],
  520. [filename] 
  521. FROM [dnt_attachments] 
  522. WHERE CHARINDEX(','+RTRIM([tid])+',',''+@tidlist+',') > 0
  523. GO
  524. IF OBJECT_ID('dnt_getattachmentlistbytid','P') IS NOT NULL
  525. DROP PROC [dnt_getattachmentlistbytid]
  526. GO
  527. CREATE PROCEDURE [dnt_getattachmentlistbytid]
  528. @tid INT
  529. AS
  530. SELECT 
  531. [aid],
  532. [filename] 
  533. FROM [dnt_attachments] 
  534. WHERE [tid] = @tid
  535. GO
  536. IF OBJECT_ID('dnt_updateattachmentdownloads','P') IS NOT NULL
  537. DROP PROC [dnt_updateattachmentdownloads]
  538. GO
  539. CREATE PROCEDURE [dnt_updateattachmentdownloads]
  540. @aid INT
  541. AS
  542. UPDATE [dnt_attachments] SET [downloads]=[downloads]+1 WHERE [aid]=@aid
  543. GO
  544. IF OBJECT_ID('dnt_getattachenmtlistbypid','P') IS NOT NULL
  545. DROP PROC [dnt_getattachenmtlistbypid]
  546. GO
  547. CREATE PROCEDURE [dnt_getattachenmtlistbypid]
  548. @pid INT
  549. AS
  550. SELECT 
  551. [aid],
  552. [uid],
  553. [tid],
  554. [pid],
  555. [postdatetime],
  556. [readperm],
  557. [filename],
  558. [description],
  559. [filetype],
  560. [filesize],
  561. [attachment],
  562. [downloads],
  563. [attachprice],
  564. [width],
  565. [height] 
  566. FROM [dnt_attachments] 
  567. WHERE [pid]=@pid
  568. GO
  569. IF OBJECT_ID('dnt_getattachmentcountbytid','P') IS NOT NULL
  570. DROP PROC [dnt_getattachmentcountbytid]
  571. GO
  572. CREATE PROCEDURE [dnt_getattachmentcountbytid]
  573. @tid INT
  574. AS
  575. SELECT COUNT([aid]) AS [acount] FROM [dnt_attachments] WHERE [tid]=@tid
  576. GO
  577. IF OBJECT_ID('dnt_getattachmentcountbypid','P') IS NOT NULL
  578. DROP PROC [dnt_getattachmentcountbypid]
  579. GO
  580. CREATE PROCEDURE [dnt_getattachmentcountbypid]
  581. @pid INT
  582. AS
  583. SELECT COUNT([aid]) AS [acount] FROM [dnt_attachments] WHERE [pid]=@pid
  584. GO
  585. IF OBJECT_ID('dnt_getattachenmtinfobyaid','P') IS NOT NULL
  586. DROP PROC [dnt_getattachenmtinfobyaid]
  587. GO
  588. CREATE PROCEDURE [dnt_getattachenmtinfobyaid]
  589. @aid INT
  590. AS
  591. SELECT TOP 1
  592. [aid],
  593. [uid],
  594. [tid],
  595. [pid],
  596. [postdatetime],
  597. [readperm],
  598. [filename],
  599. [description],
  600. [filetype],
  601. [filesize],
  602. [attachment],
  603. [downloads],
  604. [attachprice],
  605. [width],
  606. [height] 
  607. FROM [dnt_attachments] 
  608. WHERE [aid]=@aid
  609. GO
  610. IF OBJECT_ID('dnt_getpolllist','P') IS NOT NULL
  611. DROP PROC [dnt_getpolllist]
  612. GO
  613. CREATE PROCEDURE [dnt_getpolllist]
  614. @tid int
  615. AS
  616. SELECT 
  617. [pollid],
  618. [tid],
  619. [displayorder],
  620. [multiple],
  621. [visible],
  622. [maxchoices],
  623. [expiration],
  624. [uid],
  625. [voternames] 
  626. FROM [dnt_polls] 
  627. WHERE [tid]=@tid
  628. GO
  629. IF OBJECT_ID('dnt_gettaginfo','P') IS NOT NULL
  630. DROP PROC [dnt_gettaginfo]
  631. GO
  632. CREATE PROCEDURE [dnt_gettaginfo]
  633. @tagid int
  634. AS
  635. SELECT 
  636. [tagid],
  637. [tagname],
  638. [userid],
  639. [postdatetime],
  640. [orderid],
  641. [color],
  642. [count],
  643. [fcount],
  644. [pcount],
  645. [scount],
  646. [vcount],
  647. [gcount] 
  648. FROM [dnt_tags] 
  649. WHERE [tagid]=@tagid
  650. GO
  651. IF OBJECT_ID('dnt_setcurrenttopics','P') IS NOT NULL
  652. DROP PROC [dnt_setcurrenttopics]
  653. GO
  654. CREATE PROCEDURE [dnt_setcurrenttopics]
  655. @fid int
  656. AS
  657. UPDATE 
  658. [dnt_forums] 
  659. SET [curtopics] = (SELECT COUNT(tid) FROM [dnt_topics] WHERE [displayorder] >= 0 AND [fid]=@fid) WHERE [fid]=@fid
  660. GO
  661. IF OBJECT_ID('[dnt_createinvitecode]','P') IS NOT NULL
  662. DROP PROC [dnt_createinvitecode]
  663. GO
  664. CREATE PROCEDURE [dnt_createinvitecode]
  665.                     @code char(8),
  666.                     @creatorid int,
  667.                     @creator nchar(20),
  668.                     @createtime smalldatetime,
  669.                     @expiretime smalldatetime,
  670.                     @maxcount int,
  671.                     @invitetype int
  672.                     AS
  673.                     
  674.                     INSERT INTO [dnt_invitation]([invitecode],[creatorid],[creator],[createdtime],[expiretime],[maxcount],[invitetype])
  675.                      VALUES(@code,@creatorid,@creator,@createtime,@expiretime,@maxcount,@invitetype);SELECT SCOPE_IDENTITY()
  676. GO
  677. IF OBJECT_ID('[dnt_deleteinvitecode]','P') IS NOT NULL
  678. DROP PROC [dnt_deleteinvitecode]
  679. GO
  680. CREATE PROCEDURE [dnt_deleteinvitecode]
  681. @id int
  682. AS
  683. BEGIN
  684. UPDATE [dnt_invitation] SET [isdeleted]=1 WHERE [inviteid]=@id
  685. END
  686. GO
  687. IF OBJECT_ID('[dnt_clearexpireinvitecode]','P') IS NOT NULL
  688. DROP PROC [dnt_clearexpireinvitecode]
  689. GO
  690. CREATE PROCEDURE [dnt_clearexpireinvitecode]
  691. AS
  692. BEGIN
  693. DELETE [dnt_invitation] WHERE [invitetype]=3 AND [createdtime]<>[expiretime] AND ([expiretime]-GETDATE())<=0;
  694. DELETE [dnt_invitation] WHERE [isdeleted]=1 AND [createdtime]<(GETDATE()-1)
  695. END
  696. GO
  697. if exists (select * from sysobjects where id = object_id(N'[dnt_clearexpirecreditorders]') and OBJECTPROPERTY(id,N'IsProcedure') = 1)
  698. drop procedure [dnt_clearexpirecreditorders]
  699. GO
  700. CREATE PROCEDURE [dnt_clearexpirecreditorders]
  701. AS
  702. BEGIN
  703.    DELETE [dnt_orders] WHERE [orderstatus] < 1 AND [createdtime] < (GETDATE()-15)
  704. END
  705. GO
  706. --2009-10-30 后台优化----------------------------------------------
  707. IF OBJECT_ID('dnt_getmaxandmintid','P') IS NOT NULL
  708. DROP PROC [dnt_getmaxandmintid]
  709. GO
  710. CREATE PROC [dnt_getmaxandmintid]
  711. @fid INT
  712. AS
  713. SELECT 
  714. MAX([tid]) AS [maxtid],
  715. MIN([tid]) AS [mintid] 
  716. FROM [dnt_topics] 
  717. WHERE 
  718. [fid] IN (SELECT [fid] 
  719.   FROM [dnt_forums] 
  720.   WHERE [fid]=@fid 
  721.   OR (CHARINDEX(',' + RTRIM(@fid) + ',', ',' + RTRIM(parentidlist) + ',') > 0))
  722. GO
  723. IF OBJECT_ID('dnt_getdesignatepostcount','P') IS NOT NULL
  724. DROP PROC [dnt_getdesignatepostcount]
  725. GO
  726. CREATE PROC [dnt_getdesignatepostcount]
  727. @fid INT,
  728. @posttablename VARCHAR(50)
  729. AS
  730. EXEC ('SELECT COUNT([pid]) AS [postcount] FROM [' + @posttablename + '] WHERE [fid] = ' + @fid)
  731. GO
  732. IF OBJECT_ID('dnt_getpostcountbyuid','P') IS NOT NULL
  733. DROP PROC [dnt_getpostcountbyuid]
  734. GO
  735. CREATE PROC [dnt_getpostcountbyuid]
  736. @uid INT,
  737. @posttablename VARCHAR(50)
  738. AS
  739. EXEC ('SELECT COUNT([pid]) AS [postcount] FROM [' + @posttablename + '] WHERE [posterid] = ' + @uid)
  740. GO
  741. IF OBJECT_ID('dnt_gettodaypostcountbyuid','P') IS NOT NULL
  742. DROP PROC [dnt_gettodaypostcountbyuid]
  743. GO
  744. CREATE PROC [dnt_gettodaypostcountbyuid]
  745. @uid INT,
  746. @posttablename VARCHAR(50)
  747. AS
  748. EXEC ('SELECT COUNT([pid]) AS [postcount] 
  749. FROM [' + @posttablename + '] 
  750. WHERE [posterid] = ' + @uid + ' AND DATEDIFF(day, [postdatetime], GETDATE()) = 0')
  751. GO
  752. IF OBJECT_ID('dnt_gettotaltopiccount','P') IS NOT NULL
  753. DROP PROC [dnt_gettotaltopiccount]
  754. GO
  755. CREATE PROC [dnt_gettotaltopiccount]
  756. AS
  757. SELECT COUNT([tid]) AS [topicscount] FROM [dnt_topics]
  758. GO
  759. IF OBJECT_ID('dnt_resetstatistic','P') IS NOT NULL
  760. DROP PROC [dnt_resetstatistic]
  761. GO
  762. CREATE PROC [dnt_resetstatistic]
  763. @totaltopic INT,
  764. @totalpost INT,
  765. @totalusers INT,
  766. @lastusername VARCHAR(20),
  767. @lastuserid INT
  768. AS
  769. UPDATE [dnt_statistics] 
  770. SET 
  771. [totaltopic]=@totaltopic,[totalpost]=@totalpost,[totalusers]=@totalusers,[lastusername]=@lastusername,[lastuserid]=@lastuserid
  772. GO
  773. IF OBJECT_ID('dnt_gettopusers','P') IS NOT NULL
  774. DROP PROC [dnt_gettopusers]
  775. GO
  776. CREATE PROC [dnt_gettopusers]
  777. @statcont INT,
  778. @lastuid INT
  779. AS
  780. EXEC('
  781. SELECT TOP '+@statcont+' [uid] FROM [dnt_users] WHERE [uid] > @lastuid
  782. ')
  783. GO
  784. IF OBJECT_ID('dnt_resetuserdigestposts','P') IS NOT NULL
  785. DROP PROC [dnt_resetuserdigestposts]
  786. GO
  787. CREATE PROC [dnt_resetuserdigestposts]
  788. @userid INT
  789. AS
  790. UPDATE [dnt_users] 
  791. 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
  792. GO
  793. IF OBJECT_ID('dnt_getusers','P') IS NOT NULL
  794. DROP PROC [dnt_getusers]
  795. GO
  796. CREATE PROC [dnt_getusers]
  797. @start_uid INT,
  798. @end_uid INT
  799. AS
  800. SELECT [uid] FROM [dnt_users] WHERE [uid] >= @start_uid AND [uid]<=@end_uid
  801. GO
  802. IF OBJECT_ID('dnt_updateuserpostcount','P') IS NOT NULL
  803. DROP PROC [dnt_updateuserpostcount]
  804. GO
  805. CREATE PROC [dnt_updateuserpostcount]
  806. @postcount INT,
  807. @userid INT
  808. AS
  809. UPDATE [dnt_users] SET [posts]=@postcount WHERE [dnt_users].[uid] = @userid
  810. GO
  811. IF OBJECT_ID('dnt_getonlineregistercount','P') IS NOT NULL
  812. DROP PROC [dnt_getonlineregistercount]
  813. GO
  814. CREATE PROC [dnt_getonlineregistercount]
  815. AS
  816. SELECT COUNT(olid) FROM [dnt_online] WHERE [userid]>0
  817. GO
  818. IF OBJECT_ID('dnt_gettopictids','P') IS NOT NULL
  819. DROP PROC [dnt_gettopictids]
  820. GO
  821. CREATE PROC [dnt_gettopictids]
  822. @statcount INT,
  823. @lasttid INT
  824. AS
  825. EXEC('
  826. SELECT TOP '+@statcount+' [tid] FROM [dnt_topics] WHERE [tid] > @lasttid ORDER BY [tid]
  827. ')
  828. GO
  829. IF OBJECT_ID('dnt_updatelastpostoftopic','P') IS NOT NULL
  830. DROP PROC [dnt_updatelastpostoftopic]
  831. GO
  832. CREATE PROC [dnt_updatelastpostoftopic]
  833. @tid INT,
  834. @postcount INT,
  835. @lastpostid INT,
  836. @lastpost VARCHAR(20),
  837. @lastposterid INT,
  838. @lastposter VARCHAR(20)
  839. AS
  840. UPDATE [dnt_topics] 
  841. SET [lastpost]=@lastpost, [lastposterid]=@lastposterid, [lastposter]=@lastposter, [replies]=@postcount, [lastpostid]=@lastpostid 
  842. WHERE [tid] = @tid
  843. GO
  844. IF OBJECT_ID('dnt_updatetopiclastposterid','P') IS NOT NULL
  845. DROP PROC [dnt_updatetopiclastposterid]
  846. GO
  847. CREATE PROC [dnt_updatetopiclastposterid]
  848. @tid INT
  849. AS
  850. UPDATE [dnt_topics] 
  851. SET [lastposterid]=(SELECT ISNULL(MIN(lastpostid), -1)-1 FROM [dnt_topics] WHERE [tid] = @tid)
  852. GO
  853. IF OBJECT_ID('dnt_gettopics','P') IS NOT NULL
  854. DROP PROC [dnt_gettopics]
  855. GO
  856. CREATE PROC [dnt_gettopics]
  857. @start_tid INT,
  858. @end_tid INT
  859. AS
  860. SELECT [tid] 
  861. FROM [dnt_topics] 
  862. WHERE [tid] >= @start_tid AND [tid]<=@end_tid 
  863. ORDER BY [tid]
  864. GO
  865. IF OBJECT_ID('dnt_gettopforumfids','P') IS NOT NULL
  866. DROP PROC [dnt_gettopforumfids]
  867. GO
  868. CREATE PROC [dnt_gettopforumfids]
  869. @lastfid INT,
  870. @statcount INT
  871. AS
  872. EXEC('
  873. SELECT TOP(@statcount) [fid] FROM [dnt_forums] WHERE [fid] > @lastfid
  874. ')
  875. GO
  876. IF OBJECT_ID('dnt_getlastpostbyfid','P') IS NOT NULL
  877. DROP PROC [dnt_getlastpostbyfid]
  878. GO
  879. CREATE PROC [dnt_getlastpostbyfid]
  880. @fid INT,
  881. @posttablename VARCHAR(50)
  882. AS
  883. EXEC ('SELECT TOP 1 [tid], [title], [postdatetime], [posterid], [poster], [pid] 
  884. FROM [' + @posttablename + '] 
  885. WHERE [fid] = ' + @fid +
  886. 'ORDER BY [pid] DESC')
  887. GO
  888. IF OBJECT_ID('dnt_updateforum','P') IS NOT NULL
  889. DROP PROC [dnt_updateforum]
  890. GO
  891. CREATE PROC [dnt_updateforum]
  892. @lastfid INT,
  893. @topiccount INT,
  894. @postcount INT,
  895. @lasttid INT,
  896. @lasttitle NCHAR(80),
  897. @lastpost VARCHAR(20),
  898. @lastposterid INT,
  899. @lastposter NCHAR(20),
  900. @todaypostcount INT 
  901. AS
  902. UPDATE [dnt_forums] 
  903. SET [topics] = @topiccount, [posts]=@postcount, [todayposts] = @todaypostcount, [lasttid] = @lasttid, [lasttitle] = @lasttitle, [lastpost]=@lastpost, [lastposterid] = @lastposterid, [lastposter]=@lastposter 
  904. WHERE [dnt_forums].[fid] = @lastfid
  905. GO
  906. IF OBJECT_ID('dnt_resetclearmove','P') IS NOT NULL
  907. DROP PROC [dnt_resetclearmove]
  908. GO
  909. CREATE PROC [dnt_resetclearmove]
  910. AS
  911. DELETE FROM [dnt_topics] WHERE [closed] > 1
  912. GO
  913. IF OBJECT_ID('dnt_updatemytopic','P') IS NOT NULL
  914. DROP PROC [dnt_updatemytopic]
  915. GO
  916. CREATE PROC [dnt_updatemytopic]
  917. AS
  918. DELETE FROM [dnt_mytopics]
  919. INSERT INTO [dnt_mytopics]([uid], [tid], [dateline]) 
  920. SELECT [posterid],[tid],[postdatetime] 
  921. FROM [dnt_topics] 
  922. WHERE [posterid]<>-1
  923. GO
  924. IF OBJECT_ID('dnt_updatemypost','P') IS NOT NULL
  925. DROP PROC [dnt_updatemypost]
  926. GO
  927. CREATE PROC [dnt_updatemypost]
  928. @tablename VARCHAR(50)
  929. AS
  930. DELETE FROM [dnt_myposts]
  931. EXEC ('
  932. INSERT INTO [dnt_myposts]([uid], [tid], [pid], [dateline])
  933. SELECT [posterid],[tid],[pid],[postdatetime] FROM [' + @tablename + '] WHERE [posterid]<>-1
  934. ')
  935. GO
  936. IF OBJECT_ID('dnt_updateforumsinfo','P') IS NOT NULL
  937. DROP PROC [dnt_updateforumsinfo]
  938. GO
  939. CREATE PROC [dnt_updateforumsinfo]
  940. @parentid SmallInt,
  941. @layer Int,
  942. @pathlist NChar(3000),
  943. @parentidlist NChar(300),
  944. @subforumcount Int,
  945. @name NChar(50),
  946. @status Int,
  947. @colcount SmallInt,
  948. @displayorder Int,
  949. @templateid SmallInt,
  950. @topics Int,
  951. @curtopics Int,
  952. @posts Int,
  953. @todayposts Int,
  954. @lasttid Int,
  955. @lasttitle NChar(60),
  956. @lastpost DateTime,
  957. @lastposterid Int,
  958. @lastposter NChar(20),
  959. @allowsmilies Int,
  960. @allowrss Int,
  961. @allowhtml Int,
  962. @allowbbcode Int,
  963. @allowimgcode Int,
  964. @allowblog Int,
  965. @istrade Int,
  966. @allowpostspecial Int,
  967. @allowspecialonly Int,
  968. @alloweditrules Int,
  969. @allowthumbnail Int,
  970. @allowtag Int,
  971. @recyclebin Int,
  972. @modnewposts Int,
  973. @jammer Int,
  974. @disablewatermark Int,
  975. @inheritedmod Int,
  976. @autoclose SmallInt,
  977. @password NVarChar(16),
  978. @icon VarChar(255),
  979. @postcredits VarChar(255),
  980. @replycredits VarChar(255),
  981. @redirect VarChar(255),
  982. @attachextensions VarChar(255),
  983. @rules NText,
  984. @topictypes Text,
  985. @viewperm Text,
  986. @postperm Text,
  987. @replyperm Text,
  988. @getattachperm Text,
  989. @postattachperm Text,
  990. @moderators Text,
  991. @description NText,
  992. @applytopictype TinyInt,
  993. @postbytopictype TinyInt,
  994. @viewbytopictype TinyInt,
  995. @topictypeprefix TinyInt,
  996. @permuserlist NText,
  997. @seokeywords NVarChar(500),
  998. @seodescription NVarChar(500),
  999. @rewritename NVarChar(20),
  1000. @fid Int
  1001. AS
  1002. UPDATE [dnt_forums] 
  1003. SET [parentid]=@parentid, [layer]=@layer, [pathlist]=@pathlist, 
  1004. [parentidlist]=@parentidlist, [subforumcount]=@subforumcount, [name]=@name, [status]=@status,
  1005. [colcount]=@colcount, [displayorder]=@displayorder,[templateid]=@templateid,[topics]=@topics,
  1006. [curtopics]=@curtopics,[posts]=@posts,[todayposts]=@todayposts,[lasttid]=@lasttid,[lasttitle]=@lasttitle,
  1007. [lastpost]=@lastpost,[lastposterid]=@lastposterid,[lastposter]=@lastposter,
  1008. [allowsmilies]=@allowsmilies ,[allowrss]=@allowrss, [allowhtml]=@allowhtml, [allowbbcode]=@allowbbcode, [allowimgcode]=@allowimgcode, 
  1009. [allowblog]=@allowblog,[istrade]=@istrade,[allowpostspecial]=@allowpostspecial,[allowspecialonly]=@allowspecialonly,
  1010. [alloweditrules]=@alloweditrules ,[allowthumbnail]=@allowthumbnail ,[allowtag]=@allowtag,
  1011. [recyclebin]=@recyclebin, [modnewposts]=@modnewposts,[jammer]=@jammer,[disablewatermark]=@disablewatermark,[inheritedmod]=@inheritedmod,
  1012. [autoclose]=@autoclose 
  1013. WHERE [fid]=@fid
  1014. UPDATE [dnt_forumfields] 
  1015. SET [password]=@password,[icon]=@icon,[postcredits]=@postcredits,
  1016.  [replycredits]=@replycredits,[redirect]=@redirect,[attachextensions]=@attachextensions,[rules]=@rules,[topictypes]=@topictypes,
  1017.  [viewperm]=@viewperm,[postperm]=@postperm,[replyperm]=@replyperm,[getattachperm]=@getattachperm,[postattachperm]=@postattachperm,
  1018.  [moderators]=@moderators,[description]=@description,[applytopictype]=@applytopictype,[postbytopictype]=@postbytopictype,
  1019.  [viewbytopictype]=@viewbytopictype,[topictypeprefix]=@topictypeprefix,[permuserlist]=@permuserlist,[seokeywords]=@seokeywords,
  1020.  [seodescription]=@seodescription,[rewritename]=@rewritename 
  1021.  WHERE [fid]=@fid
  1022. GO
  1023. IF OBJECT_ID('dnt_insertforumsinfo','P') IS NOT NULL
  1024. DROP PROC [dnt_insertforumsinfo]
  1025. GO
  1026. CREATE PROC [dnt_insertforumsinfo]
  1027. @parentid SmallInt,
  1028. @layer Int,
  1029. @pathlist NChar(3000),
  1030. @parentidlist NChar(300),
  1031. @subforumcount Int,
  1032. @name NChar(50),
  1033. @status Int,
  1034. @colcount SmallInt,
  1035. @displayorder Int,
  1036. @templateid SmallInt,
  1037. @allowsmilies Int,
  1038. @allowrss Int,
  1039. @allowhtml Int,
  1040. @allowbbcode Int,
  1041. @allowimgcode Int,
  1042. @allowblog Int,
  1043. @istrade Int,
  1044. @alloweditrules Int,
  1045. @allowthumbnail Int,
  1046. @allowtag Int,
  1047. @recyclebin Int,
  1048. @modnewposts Int,
  1049. @jammer Int,
  1050. @disablewatermark Int,
  1051. @inheritedmod Int,
  1052. @autoclose SmallInt,
  1053. @allowpostspecial Int,
  1054. @allowspecialonly Int,
  1055. @description NText,
  1056. @password VarChar(16),
  1057. @icon VarChar(255),
  1058. @postcredits VarChar(255),
  1059. @replycredits VarChar(255),
  1060. @redirect VarChar(255),
  1061. @attachextensions VarChar(255),
  1062. @moderators Text,
  1063. @rules NText,
  1064. @topictypes Text,
  1065. @viewperm Text,
  1066. @postperm Text,
  1067. @replyperm Text,
  1068. @getattachperm Text,
  1069. @postattachperm Text,
  1070. @seokeywords NVarChar(500),
  1071. @seodescription NVarChar(500),
  1072. @rewritename NVarChar(20)
  1073. AS
  1074. DECLARE @fid INT
  1075. INSERT INTO [dnt_forums] ([parentid],[layer],[pathlist],[parentidlist],[subforumcount],[name],
  1076. [status],[colcount],[displayorder],[templateid],[allowsmilies],[allowrss],[allowhtml],[allowbbcode],[allowimgcode],[allowblog],
  1077. [istrade],[alloweditrules],[recyclebin],[modnewposts],[jammer],[disablewatermark],[inheritedmod],[autoclose],[allowthumbnail],
  1078. [allowtag],[allowpostspecial],[allowspecialonly]) 
  1079. VALUES (@parentid,@layer,@pathlist,@parentidlist,@subforumcount,@name,@status, @colcount, @displayorder,
  1080. @templateid,@allowsmilies,@allowrss,@allowhtml,@allowbbcode,@allowimgcode,@allowblog,@istrade,@alloweditrules,@recyclebin,
  1081. @modnewposts,@jammer,@disablewatermark,@inheritedmod,@autoclose,@allowthumbnail,@allowtag,@allowpostspecial,@allowspecialonly)
  1082. --SET @fid=@@IDENTITY
  1083. SELECT @fid = ISNULL(MAX(fid), 0) FROM [dnt_forums]
  1084. INSERT INTO [dnt_forumfields] ([fid],[description],[password],[icon],[postcredits],[replycredits],[redirect],
  1085. [attachextensions],[moderators],[rules],[topictypes],[viewperm],[postperm],[replyperm],[getattachperm],[postattachperm],[seokeywords],[seodescription],[rewritename]) 
  1086. VALUES (@fid,@description,@password,@icon,@postcredits,@replycredits,@redirect,@attachextensions,@moderators,@rules,@topictypes,@viewperm,
  1087. @postperm,@replyperm,@getattachperm,@postattachperm,@seokeywords,@seodescription,@rewritename) 
  1088. GO
  1089. IF OBJECT_ID('dnt_getsubforumcount','P') IS NOT NULL
  1090. DROP PROC [dnt_getsubforumcount]
  1091. GO
  1092. CREATE PROC [dnt_getsubforumcount]
  1093. @fid INT
  1094. AS
  1095. SELECT COUNT(fid) FROM [dnt_forums] WHERE [parentid]=@fid
  1096. GO
  1097. IF OBJECT_ID('dnt_deleteforumsbyfid','P') IS NOT NULL
  1098. DROP PROC [dnt_deleteforumsbyfid]
  1099. GO
  1100. CREATE PROC [dnt_deleteforumsbyfid]
  1101. @fid INT,
  1102. @postname NVARCHAR(50)
  1103. AS
  1104. DECLARE @parentid INT,
  1105. @displayorder INT
  1106. SELECT TOP 1 @parentid=[parentid],@displayorder=[displayorder] FROM [dnt_forums] WHERE [fid]=@fid
  1107. UPDATE [dnt_forums] SET [displayorder]=[displayorder]-1 WHERE [displayorder]>@displayorder
  1108. UPDATE [dnt_forums] SET [subforumcount]=[subforumcount]-1 WHERE  [fid]=@parentid
  1109. DELETE FROM [dnt_forumfields] WHERE [fid]=@fid
  1110. DELETE FROM [dnt_polls] WHERE [tid] IN (SELECT [tid] FROM [dnt_topics] WHERE [fid]=@fid)
  1111. 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 + ')')
  1112. EXEC ('DELETE FROM [' + @postname + '] WHERE [fid]=' + @fid)
  1113. DELETE FROM [dnt_topics] WHERE [fid]=@fid
  1114. DELETE FROM [dnt_forums] WHERE  [fid]=@fid
  1115. DELETE FROM [dnt_moderators] WHERE  [fid]=@fid
  1116. GO
  1117. IF OBJECT_ID('dnt_getparentidbyfid','P') IS NOT NULL
  1118. DROP PROC [dnt_getparentidbyfid]
  1119. GO
  1120. CREATE PROC [dnt_getparentidbyfid]
  1121. @fid INT
  1122. AS
  1123. SELECT [parentid] From [dnt_forums] WHERE [inheritedmod]=1 AND [fid]=@fid
  1124. GO
  1125. IF OBJECT_ID('dnt_updateforumsmoderators','P') IS NOT NULL
  1126. DROP PROC [dnt_updateforumsmoderators]
  1127. GO
  1128. CREATE PROC [dnt_updateforumsmoderators]
  1129. @displayorder INT,
  1130. @moderators VARCHAR(500),
  1131. @fid INT,
  1132. @inherited INT
  1133. AS
  1134. DECLARE @usernamelist VARCHAR(255),
  1135. @username VARCHAR(255),
  1136. @uid INT,
  1137. @len INT,
  1138. @b BIT,
  1139. @begin INT,
  1140. @end INT
  1141. SELECT @len=0,@begin=1,@end=1,@b=1,@usernamelist=''
  1142. WHILE @b=1
  1143. BEGIN
  1144. IF @end=1
  1145. SET @begin=@end
  1146. ELSE
  1147. SET @begin=@end+1
  1148. SET @end = CHARINDEX(',',@moderators,@begin)
  1149. IF @end=0
  1150. SET @end=LEN(@moderators)+1
  1151. SET @len=@end-@begin
  1152. IF @len>0
  1153. BEGIN
  1154. SET @username=SUBSTRING(@moderators,@begin,@len)
  1155. IF @username<>''
  1156. BEGIN
  1157. SELECT @uid=[uid] FROM [dnt_users] WHERE [groupid]<>7 AND [groupid]<>8 AND [username]=@username
  1158. IF @uid IS NOT NULL
  1159. BEGIN
  1160. INSERT INTO [dnt_moderators] ([uid],[fid],[displayorder],[inherited]) VALUES (@uid,@fid,@displayorder,@inherited)
  1161. SET @usernamelist=@usernamelist+@username+','
  1162. SET @displayorder=@displayorder+1
  1163. SET @uid=NULL
  1164. END
  1165. END
  1166. END
  1167. ELSE
  1168. SET @b=0
  1169. END
  1170. IF RIGHT(@usernamelist,1)=','
  1171. SET @usernamelist=LEFT(@usernamelist,LEN(@usernamelist)-1)
  1172. UPDATE [dnt_forumfields] SET [moderators]=@usernamelist WHERE [fid] =@fid
  1173. GO
  1174. IF OBJECT_ID('dnt_getforumstable','P') IS NOT NULL
  1175. DROP PROC [dnt_getforumstable]
  1176. GO
  1177. CREATE PROC [dnt_getforumstable]
  1178. AS
  1179. SELECT
  1180. [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] 
  1181. FROM [dnt_forums] AS [f] 
  1182. LEFT JOIN [dnt_forumfields] AS [ff] 
  1183. ON [f].[fid]=[ff].[fid] 
  1184. ORDER BY [f].[displayorder]
  1185. GO
  1186. IF OBJECT_ID('dnt_getmainforum','P') IS NOT NULL
  1187. DROP PROC [dnt_getmainforum]
  1188. GO
  1189. CREATE PROC [dnt_getmainforum]
  1190. AS
  1191. SELECT
  1192. [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] 
  1193. FROM [dnt_forums] 
  1194. WHERE [layer]=0 
  1195. Order By [displayorder] ASC
  1196. GO
  1197. IF OBJECT_ID('dnt_getlastposttid','P') IS NOT NULL
  1198. DROP PROC [dnt_getlastposttid]
  1199. GO
  1200. CREATE PROC [dnt_getlastposttid]
  1201. @visibleforums VARCHAR(4000),
  1202. @fid INT
  1203. AS
  1204. IF @visibleforums=''
  1205. SELECT TOP 1 [tid] FROM [dnt_topics] AS t LEFT JOIN [dnt_forums] AS f  ON [t].[fid] = [f].[fid] 
  1206. WHERE [t].[closed]<>1 AND  [t].[displayorder] >=0  AND ([t].[fid] = @fid 
  1207. OR CHARINDEX(',' + CONVERT(NVARCHAR(10), @fid) + ',' , ',' + RTRIM([f].[parentidlist]) + ',') > 0 )  
  1208. ORDER BY [t].[lastpost] DESC
  1209. ELSE
  1210. EXEC('SELECT TOP 1 [tid] FROM [dnt_topics] AS t LEFT JOIN [dnt_forums] AS f  ON [t].[fid] = [f].[fid] 
  1211. WHERE [t].[closed]<>1 AND  [t].[displayorder] >=0  AND ([t].[fid] = ' + @fid +
  1212. 'OR CHARINDEX('','' + CONVERT(NVARCHAR(10), ' + @fid + ') + '','' , '','' + RTRIM([f].[parentidlist]) + '','') > 0 )  
  1213. AND [t].[fid] IN ('+@visibleforums+')  ORDER BY [t].[lastpost] DESC')
  1214. GO
  1215. IF OBJECT_ID('dnt_deluserallinf','P') IS NOT NULL
  1216. DROP PROC [dnt_deluserallinf]
  1217. GO
  1218. CREATE PROC [dnt_deluserallinf]
  1219. @uid INT,
  1220. @delPosts BIT,
  1221. @delPms BIT
  1222. AS
  1223. DECLARE @existuid INT
  1224. SELECT @existuid = COUNT([uid]) FROM [dnt_users] WHERE [uid]=@uid
  1225. IF @existuid <>0
  1226. BEGIN
  1227. DELETE FROM [dnt_users] WHERE [uid]=@uid                 
  1228. DELETE FROM [dnt_userfields] WHERE [uid]=@uid                 
  1229. DELETE FROM [dnt_onlinetime] WHERE [uid]=@uid                
  1230. DELETE FROM [dnt_polls] WHERE [uid]=@uid                 
  1231. DELETE FROM [dnt_favorites] WHERE [uid]=@uid
  1232. DECLARE @tableid INT,
  1233. @tablename NVARCHAR(20)
  1234. IF @delPosts = 1
  1235. BEGIN
  1236. DELETE FROM [dnt_topics] WHERE [posterid]=@uid
  1237. DECLARE tables_cursor CURSOR FOR SELECT [id] FROM [dnt_tablelist]
  1238. OPEN tables_cursor
  1239. FETCH NEXT FROM tables_cursor INTO @tableid
  1240. WHILE @@FETCH_STATUS = 0
  1241. BEGIN
  1242. SET @tablename = 'dnt_posts' + LTRIM(STR(@tableid))
  1243. EXEC ('DELETE FROM ' + @tablename + ' WHERE [posterid]='+@uid)
  1244. FETCH NEXT FROM tables_cursor INTO @tableid
  1245. END
  1246. CLOSE tables_cursor
  1247. DEALLOCATE tables_cursor
  1248. SET @tableid = 0
  1249. SET @tablename = ''
  1250. END
  1251. ELSE
  1252. BEGIN
  1253. UPDATE [dnt_topics] SET [poster]='该用户已被删除' Where [posterid]=@uid
  1254. UPDATE [dnt_topics] SET [lastposter]='该用户已被删除' Where [lastpostid]=@uid
  1255. DECLARE tables_cursor CURSOR FOR SELECT [id] FROM [dnt_tablelist]
  1256. OPEN tables_cursor
  1257. FETCH NEXT FROM tables_cursor INTO @tableid
  1258. WHILE @@FETCH_STATUS = 0
  1259. BEGIN
  1260. SET @tablename = 'dnt_posts' + LTRIM(STR(@tableid))
  1261. EXEC ('UPDATE ['+@tablename+'] SET [poster]=''该用户已被删除'' WHERE [posterid]='+@uid)
  1262. FETCH NEXT FROM tables_cursor INTO @tableid
  1263. END
  1264. CLOSE tables_cursor
  1265. DEALLOCATE tables_cursor
  1266. SET @tableid = 0
  1267. SET @tablename = ''
  1268. END
  1269. IF @delPms = 1
  1270. BEGIN
  1271. DELETE FROM [dnt_pms] WHERE [msgfromid]=@uid
  1272. END
  1273. ELSE
  1274. BEGIN
  1275. UPDATE [dnt_pms] SET [msgfrom]='该用户已被删除' WHERE [msgfromid]=@uid
  1276. UPDATE [dnt_pms] SET [msgto]='该用户已被删除' WHERE [msgtoid]=@uid
  1277. END
  1278. DELETE FROM [dnt_moderators] WHERE [uid]=@uid
  1279. UPDATE [dnt_statistics] SET [totalusers]=[totalusers]-1
  1280. DECLARE @lastuserid INT,@lastusername VARCHAR(50)
  1281. SELECT TOP 1 @lastuserid=[uid],@lastusername=[username] FROM [dnt_users] ORDER BY [uid] DESC
  1282. IF @lastuserid IS NOT NULL
  1283. UPDATE [dnt_Statistics] SET [lastuserid]=@lastuserid, [lastusername]=@lastusername
  1284. END
  1285. GO
  1286. IF OBJECT_ID('dnt_addusergroup','P') IS NOT NULL
  1287. DROP PROC [dnt_addusergroup]
  1288. GO
  1289. CREATE PROC [dnt_addusergroup]
  1290. @Radminid INT,
  1291. @Grouptitle NVARCHAR(50),
  1292. @Creditshigher INT,
  1293. @Creditslower INT,
  1294. @Stars INT,
  1295. @Color CHAR(7),
  1296. @Groupavatar NVARCHAR(60), 
  1297. @Readaccess INT,
  1298. @Allowvisit INT,
  1299. @Allowpost INT,
  1300. @Allowreply INT,
  1301. @Allowpostpoll INT,
  1302. @Allowdirectpost INT,
  1303. @Allowgetattach INT,
  1304. @Allowpostattach INT,
  1305. @Allowvote INT,
  1306. @Allowmultigroups INT, 
  1307. @Allowsearch INT,
  1308. @Allowavatar INT,
  1309. @Allowcstatus INT,
  1310. @Allowuseblog INT,
  1311. @Allowinvisible INT,
  1312. @Allowtransfer INT,
  1313. @Allowsetreadperm INT, 
  1314. @Allowsetattachperm INT,
  1315. @Allowhidecode INT,
  1316. @Allowhtml INT,
  1317. @Allowcusbbcode INT,
  1318. @Allownickname INT,
  1319. @Allowsigbbcode INT,
  1320. @Allowsigimgcode INT,
  1321. @Allowviewpro INT,
  1322. @Allowviewstats INT,
  1323. @Allowtrade INT,
  1324. @Allowdiggs INT,
  1325. @Allowdebate INT,
  1326. @Allowbonus INT,
  1327. @Minbonusprice INT,
  1328. @Maxbonusprice INT,
  1329. @Disableperiodctrl INT, 
  1330. @Reasonpm INT,
  1331. @Maxprice SMALLINT,
  1332. @Maxpmnum SMALLINT,
  1333. @Maxsigsize SMALLINT,
  1334. @Maxattachsize INT,
  1335. @Maxsizeperday INT,
  1336. @Attachextensions CHAR(100),
  1337. @Maxspaceattachsize INT,
  1338. @Maxspacephotosize INT, 
  1339. @Raterange CHAR(100)
  1340. AS
  1341. INSERT INTO [dnt_usergroups]  ([radminid],[grouptitle],[creditshigher],[creditslower],
  1342. [stars] ,[color], [groupavatar],[readaccess], [allowvisit],[allowpost],[allowreply],
  1343. [allowpostpoll], [allowdirectpost],[allowgetattach],[allowpostattach],[allowvote],[allowmultigroups],
  1344. [allowsearch],[allowavatar],[allowcstatus],[allowuseblog],[allowinvisible],[allowtransfer],
  1345. [allowsetreadperm],[allowsetattachperm],[allowhidecode],[allowhtml],[allowcusbbcode],[allownickname],
  1346. [allowsigbbcode],[allowsigimgcode],[allowviewpro],[allowviewstats],[allowtrade],[allowdiggs],[disableperiodctrl],[reasonpm],
  1347. [maxprice],[maxpmnum],[maxsigsize],[maxattachsize],[maxsizeperday],[attachextensions],[raterange],[maxspaceattachsize],
  1348. [maxspacephotosize],[allowdebate],[allowbonus],[minbonusprice],[maxbonusprice]) 
  1349. VALUES(
  1350. @Radminid,@Grouptitle,@Creditshigher,@Creditslower,@Stars,@Color,@Groupavatar,@Readaccess,@Allowvisit,@Allowpost,@Allowreply,
  1351. @Allowpostpoll,@Allowdirectpost,@Allowgetattach,@Allowpostattach,@Allowvote,@Allowmultigroups,@Allowsearch,@Allowavatar,@Allowcstatus,
  1352. @Allowuseblog,@Allowinvisible,@Allowtransfer,@Allowsetreadperm,@Allowsetattachperm,@Allowhidecode,@Allowhtml,@Allowcusbbcode,@Allownickname,
  1353. @Allowsigbbcode,@Allowsigimgcode,@Allowviewpro,@Allowviewstats,@Allowtrade,@Allowdiggs,@Disableperiodctrl,@Reasonpm,@Maxprice,@Maxpmnum,@Maxsigsize,@Maxattachsize, 
  1354. @Maxsizeperday,@Attachextensions,@Raterange,@Maxspaceattachsize,@Maxspacephotosize,@Allowdebate,@Allowbonus,@Minbonusprice,@Maxbonusprice)
  1355. GO
  1356. IF OBJECT_ID('dnt_updateusergroup','P') IS NOT NULL
  1357. DROP PROC [dnt_updateusergroup]
  1358. GO
  1359. CREATE PROC [dnt_updateusergroup]
  1360. @Radminid INT,
  1361. @Grouptitle NVARCHAR(50),
  1362. @Creditshigher INT,
  1363. @Creditslower INT, 
  1364. @Stars INT,
  1365. @Color CHAR(7),
  1366. @Groupavatar NVARCHAR(60),
  1367. @Readaccess INT,
  1368. @Allowvisit INT,
  1369. @Allowpost INT,
  1370. @Allowreply INT,
  1371. @Allowpostpoll INT,
  1372. @Allowdirectpost INT,
  1373. @Allowgetattach INT,
  1374. @Allowpostattach INT,
  1375. @Allowvote INT,
  1376. @Allowmultigroups INT,
  1377. @Allowsearch INT,
  1378. @Allowavatar INT,
  1379. @Allowcstatus INT,
  1380. @Allowuseblog INT,
  1381. @Allowinvisible INT,
  1382. @Allowtransfer INT,
  1383. @Allowsetreadperm INT,
  1384. @Allowsetattachperm INT,
  1385. @Allowhidecode INT,
  1386. @Allowhtml INT,
  1387. @Allowcusbbcode INT,
  1388. @Allownickname INT,
  1389. @Allowsigbbcode INT,
  1390. @Allowsigimgcode INT,
  1391. @Allowviewpro INT,
  1392. @Allowviewstats INT,
  1393. @Allowtrade INT,
  1394. @Allowdiggs INT,
  1395. @Disableperiodctrl INT,
  1396. @Allowdebate INT,
  1397. @Allowbonus INT,
  1398. @Minbonusprice INT,
  1399. @Maxbonusprice INT,
  1400. @Reasonpm INT,
  1401. @Maxprice SMALLINT,
  1402. @Maxpmnum SMALLINT,
  1403. @Maxsigsize SMALLINT,
  1404. @Maxattachsize INT,
  1405. @Maxsizeperday INT,
  1406. @Attachextensions CHAR(100),
  1407. @Maxspaceattachsize INT,
  1408. @Maxspacephotosize INT,
  1409. @Groupid INT
  1410. AS
  1411. UPDATE [dnt_usergroups]  
  1412. SET [radminid]=@Radminid,[grouptitle]=@Grouptitle,[creditshigher]=@Creditshigher,[creditslower]=@Creditslower,
  1413. [stars]=@Stars,[color]=@Color,[groupavatar]=@Groupavatar,[readaccess]=@Readaccess, 
  1414. [allowvisit]=@Allowvisit,[allowpost]=@Allowpost,[allowreply]=@Allowreply,[allowpostpoll]=@Allowpostpoll, 
  1415. [allowdirectpost]=@Allowdirectpost,[allowgetattach]=@Allowgetattach,[allowpostattach]=@Allowpostattach,
  1416. [allowvote]=@Allowvote,[allowmultigroups]=@Allowmultigroups,[allowsearch]=@Allowsearch,[allowavatar]=@Allowavatar,
  1417. [allowcstatus]=@Allowcstatus,[allowuseblog]=@Allowuseblog,[allowinvisible]=@Allowinvisible,
  1418. [allowtransfer]=@Allowtransfer,[allowsetreadperm]=@Allowsetreadperm,[allowsetattachperm]=@Allowsetattachperm,
  1419. [allowhidecode]=@Allowhidecode,[allowhtml]=@Allowhtml,[allowcusbbcode]=@Allowcusbbcode,
  1420. [allownickname]=@Allownickname,[allowsigbbcode]=@Allowsigbbcode,[allowsigimgcode]=@Allowsigimgcode,
  1421. [allowviewpro]=@Allowviewpro,[allowviewstats]=@Allowviewstats,[allowtrade]=@Allowtrade,[allowdiggs]=@Allowdiggs,
  1422. [disableperiodctrl]=@Disableperiodctrl,[allowdebate]=@Allowdebate,[allowbonus]=@Allowbonus,
  1423. [minbonusprice]=@Minbonusprice,[maxbonusprice]=@Maxbonusprice,[reasonpm]=@Reasonpm,[maxprice]=@Maxprice,
  1424. [maxpmnum]=@Maxpmnum,[maxsigsize]=@Maxsigsize,[maxattachsize]=@Maxattachsize,[maxsizeperday]=@Maxsizeperday,
  1425. [attachextensions]=@Attachextensions,[maxspaceattachsize]=@Maxspaceattachsize,
  1426. [maxspacephotosize]=@Maxspacephotosize  
  1427. WHERE [groupid]=@Groupid
  1428. GO
  1429. IF OBJECT_ID('dnt_getonlinegroupicontable','P') IS NOT NULL
  1430. DROP PROC [dnt_getonlinegroupicontable]
  1431. GO
  1432. CREATE PROC [dnt_getonlinegroupicontable]
  1433. AS
  1434. SELECT [groupid], [displayorder], [title], [img] FROM [dnt_onlinelist] WHERE [img] <> '' ORDER BY [displayorder]
  1435. GO
  1436. IF OBJECT_ID('dnt_getuserlistbygroupid','P') IS NOT NULL
  1437. DROP PROC [dnt_getuserlistbygroupid]
  1438. GO
  1439. CREATE PROC [dnt_getuserlistbygroupid]
  1440. @groupIdList VARCHAR(500)
  1441. AS
  1442. SELECT 
  1443. [uid],
  1444. [username],
  1445. [nickname],
  1446. [password],
  1447. [secques],
  1448. [spaceid],
  1449. [gender],
  1450. [adminid],
  1451. [groupid],
  1452. [groupexpiry],
  1453. [extgroupids],
  1454. [regip],
  1455. [joindate],
  1456. [lastip],
  1457. [lastvisit],
  1458. [lastactivity],
  1459. [lastpost],
  1460. [lastpostid],
  1461. [lastposttitle],
  1462. [posts],
  1463. [digestposts],
  1464. [oltime],
  1465. [pageviews],
  1466. [credits],
  1467. [extcredits1],
  1468. [extcredits2],
  1469. [extcredits3],
  1470. [extcredits4],
  1471. [extcredits5],
  1472. [extcredits6],
  1473. [extcredits7],
  1474. [extcredits8],
  1475. [avatarshowid],
  1476. [email],
  1477. [bday],
  1478. [sigstatus],
  1479. [tpp],
  1480. [ppp],
  1481. [templateid],
  1482. [pmsound],
  1483. [showemail],
  1484. [invisible],
  1485. [newpm],
  1486. [newpmcount],
  1487. [accessmasks],
  1488. [onlinestate],
  1489. [newsletter],
  1490. [salt]
  1491. FROM [dnt_users] WHERE [groupid] IN (@groupIdList)
  1492. GO
  1493. IF OBJECT_ID('dnt_passpost','P') IS NOT NULL 
  1494. DROP PROC [dnt_passpost]
  1495. GO
  1496. CREATE PROC [dnt_passpost]
  1497. @currentPostTableId INT,
  1498. @postcount INT,
  1499. @pidList NVARCHAR(500)
  1500. AS
  1501. DECLARE @count INT,
  1502. @begin INT,
  1503. @postdatetime DATETIME,
  1504. @poster NVARCHAR(50),
  1505. @posterid INT,
  1506. @fid INT,
  1507. @title NVARCHAR(20),
  1508. @tid INT
  1509.  
  1510. SET @begin=1
  1511. EXEC('UPDATE  [dnt_posts'+@currentPostTableId+']   SET [invisible]=0 WHERE [pid] IN ('+@pidList+')')
  1512. UPDATE  [dnt_statistics] SET [totalpost]= [totalpost] + @postcount
  1513. DECLARE @tempposttable TABLE 
  1514. (
  1515. [ROWID] INT IDENTITY(1,1),
  1516. [postdatetime] DATETIME,
  1517. [poster] NVARCHAR(50),
  1518. [posterid] INT,
  1519. [fid] INT,
  1520. [title] NVARCHAR(20),
  1521. [tid] INT
  1522. )
  1523. INSERT INTO @tempposttable 
  1524. EXEC('SELECT [postdatetime],[poster],[posterid],[fid],[title],[tid] FROM [dnt_posts'+@currentPostTableId+'] WHERE [pid] IN ('+@pidList+')')
  1525. SELECT @count=COUNT(1) FROM @tempposttable
  1526. WHILE @begin <= @count
  1527. BEGIN
  1528. SELECT @postdatetime = [postdatetime],
  1529. @poster = [poster],
  1530. @posterid = [posterid],
  1531. @fid = [fid],
  1532. @title = [title],
  1533. @tid = [tid]
  1534. FROM @tempposttable
  1535. UPDATE [dnt_forums] 
  1536. SET [posts]=[posts] + 1, 
  1537. [todayposts]=CASE WHEN datediff(day,'2008-10-22',getdate())=0 
  1538. THEN [todayposts] + 1
  1539. ELSE [todayposts] END,
  1540. [lastpost]=@postdatetime,
  1541. [lastposter]=@poster,
  1542. [lastposterid]=@posterid 
  1543. WHERE [fid]=@fid
  1544. UPDATE [dnt_users] 
  1545. SET [lastpost] = @postdatetime, 
  1546. [lastpostid] = @posterid, 
  1547. [lastposttitle] = @title, 
  1548. [posts] = [posts] + 1  
  1549. WHERE [uid] = @posterid
  1550. UPDATE [dnt_topics] 
  1551. SET [replies]=[replies]+1,
  1552. [lastposter]=@poster,
  1553. [lastposterid]=@posterid,
  1554. [lastpost]=@postdatetime 
  1555. WHERE [tid]=@tid
  1556. SET @begin = @begin + 1
  1557. END
  1558. GO
  1559. IF OBJECT_ID('dnt_getunauditnewtopic','P') IS NOT NULL
  1560. DROP PROC [dnt_getunauditnewtopic]
  1561. GO
  1562. CREATE PROC [dnt_getunauditnewtopic]
  1563. AS
  1564. SELECT 
  1565. [tid],
  1566. [fid],
  1567. [iconid],
  1568. [typeid],
  1569. [readperm],
  1570. [price],
  1571. [poster],
  1572. [posterid],
  1573. [title],
  1574. [attention],
  1575. [postdatetime],
  1576. [lastpost],
  1577. [lastpostid],
  1578. [lastposter],
  1579. [lastposterid],
  1580. [views],
  1581. [replies],
  1582. [displayorder],
  1583. [highlight],
  1584. [digest],
  1585. [rate],
  1586. [hide],
  1587. [attachment],
  1588. [moderated],
  1589. [closed],
  1590. [magic],
  1591. [identify],
  1592. [special]
  1593. FROM dnt_topics
  1594. WHERE [displayorder] = -2 OR [displayorder] = -3 
  1595. ORDER BY [tid] DESC
  1596. GO
  1597. IF OBJECT_ID('dnt_getunauditnewtopicbycondition','P') IS NOT NULL
  1598. DROP PROC [dnt_getunauditnewtopicbycondition]
  1599. GO
  1600. CREATE PROCEDURE [dnt_getunauditnewtopicbycondition]
  1601. @fidlist NVARCHAR(500),
  1602. @pagesize int,
  1603. @displayorder INT,
  1604. @pageindex int
  1605. AS
  1606. DECLARE @startRow NVARCHAR(3)
  1607. SET @startRow = (@pageindex - 1) * @pagesize
  1608. IF @fidlist = '0'
  1609. BEGIN
  1610. IF @pageindex = 1
  1611. BEGIN
  1612. EXEC('
  1613. SELECT 
  1614. TOP '+@pagesize+'
  1615. [dnt_topics].[rate], 
  1616. [dnt_topics].[fid], 
  1617. [dnt_topics].[tid],
  1618. [dnt_topics].[iconid],
  1619. [dnt_topics].[typeid],
  1620. [dnt_topics].[title],
  1621. [dnt_topics].[price],
  1622. [dnt_topics].[hide],
  1623. [dnt_topics].[readperm],
  1624. [dnt_topics].[poster],
  1625. [dnt_topics].[posterid],
  1626. [dnt_topics].[replies],
  1627. [dnt_topics].[views],
  1628. [dnt_topics].[postdatetime],
  1629. [dnt_topics].[lastpost],
  1630. [dnt_topics].[lastposter],
  1631. [dnt_topics].[lastpostid],
  1632. [dnt_topics].[lastposterid],
  1633. [dnt_topics].[highlight],
  1634. [dnt_topics].[digest],
  1635. [dnt_topics].[displayorder],
  1636. [dnt_topics].[attachment],
  1637. [dnt_topics].[closed],
  1638. [dnt_topics].[magic],
  1639. [dnt_topics].[special]
  1640. FROM [dnt_topics]
  1641. WHERE [displayorder] = @displayorder
  1642. ')
  1643. END
  1644. ELSE
  1645. BEGIN
  1646. EXEC('
  1647. SELECT 
  1648. TOP '+@pagesize+'
  1649. [dnt_topics].[rate], 
  1650. [dnt_topics].[fid], 
  1651. [dnt_topics].[tid],
  1652. [dnt_topics].[iconid],
  1653. [dnt_topics].[typeid],
  1654. [dnt_topics].[title],
  1655. [dnt_topics].[price],
  1656. [dnt_topics].[hide],
  1657. [dnt_topics].[readperm],
  1658. [dnt_topics].[poster],
  1659. [dnt_topics].[posterid],
  1660. [dnt_topics].[replies],
  1661. [dnt_topics].[views],
  1662. [dnt_topics].[postdatetime],
  1663. [dnt_topics].[lastpost],
  1664. [dnt_topics].[lastposter],
  1665. [dnt_topics].[lastpostid],
  1666. [dnt_topics].[lastposterid],
  1667. [dnt_topics].[highlight],
  1668. [dnt_topics].[digest],
  1669. [dnt_topics].[displayorder],
  1670. [dnt_topics].[attachment],
  1671. [dnt_topics].[closed],
  1672. [dnt_topics].[magic],
  1673. [dnt_topics].[special]
  1674. FROM [dnt_topics]
  1675. WHERE [displayorder] = '+@displayorder+' 
  1676. AND tid < (SELECT MIN(tid) FROM (SELECT TOP '+@startRow+' tid FROM [dnt_topics] WHERE displayorder='+@displayorder+') AS T)
  1677. ORDER BY tid DESC
  1678. ')
  1679. END
  1680. END
  1681. ELSE
  1682. BEGIN
  1683. IF @pageindex = 1
  1684. BEGIN
  1685. EXEC('
  1686. SELECT 
  1687. TOP '+@pagesize+'
  1688. [dnt_topics].[rate], 
  1689. [dnt_topics].[fid], 
  1690. [dnt_topics].[tid],
  1691. [dnt_topics].[iconid],
  1692. [dnt_topics].[typeid],
  1693. [dnt_topics].[title],
  1694. [dnt_topics].[price],
  1695. [dnt_topics].[hide],
  1696. [dnt_topics].[readperm],
  1697. [dnt_topics].[poster],
  1698. [dnt_topics].[posterid],
  1699. [dnt_topics].[replies],
  1700. [dnt_topics].[views],
  1701. [dnt_topics].[postdatetime],
  1702. [dnt_topics].[lastpost],
  1703. [dnt_topics].[lastposter],
  1704. [dnt_topics].[lastpostid],
  1705. [dnt_topics].[lastposterid],
  1706. [dnt_topics].[highlight],
  1707. [dnt_topics].[digest],
  1708. [dnt_topics].[displayorder],
  1709. [dnt_topics].[attachment],
  1710. [dnt_topics].[closed],
  1711. [dnt_topics].[magic],
  1712. [dnt_topics].[special]
  1713. FROM [dnt_topics]
  1714. WHERE [displayorder] = @displayorder AND fid IN ('+@fidlist+')
  1715. ')
  1716. END
  1717. ELSE
  1718. BEGIN
  1719. EXEC('
  1720. SELECT 
  1721. TOP '+@pagesize+'
  1722. [dnt_topics].[rate], 
  1723. [dnt_topics].[fid], 
  1724. [dnt_topics].[tid],
  1725. [dnt_topics].[iconid],
  1726. [dnt_topics].[typeid],
  1727. [dnt_topics].[title],
  1728. [dnt_topics].[price],
  1729. [dnt_topics].[hide],
  1730. [dnt_topics].[readperm],
  1731. [dnt_topics].[poster],
  1732. [dnt_topics].[posterid],
  1733. [dnt_topics].[replies],
  1734. [dnt_topics].[views],
  1735. [dnt_topics].[postdatetime],
  1736. [dnt_topics].[lastpost],
  1737. [dnt_topics].[lastposter],
  1738. [dnt_topics].[lastpostid],
  1739. [dnt_topics].[lastposterid],
  1740. [dnt_topics].[highlight],
  1741. [dnt_topics].[digest],
  1742. [dnt_topics].[displayorder],
  1743. [dnt_topics].[attachment],
  1744. [dnt_topics].[closed],
  1745. [dnt_topics].[magic],
  1746. [dnt_topics].[special]
  1747. FROM [dnt_topics]
  1748. WHERE [displayorder] = '+@displayorder+' AND fid IN ('+@fidlist+')
  1749. AND tid < (SELECT MIN(tid) FROM (SELECT TOP '+@startRow+' tid FROM [dnt_topics] WHERE displayorder='+@displayorder+' fid IN ('+@fidlist+')) AS T)
  1750. ORDER BY tid DESC
  1751. ')
  1752. END
  1753. END
  1754. IF OBJECT_ID('dnt_getunauditpost','P') IS NOT NULL
  1755. DROP PROC [dnt_getunauditpost]
  1756. GO
  1757. CREATE PROCEDURE [dnt_getunauditpost]
  1758. @lastposter NVARCHAR(20),
  1759. @lastposterid INT
  1760. AS
  1761. UPDATE [dnt_topics] 
  1762. SET [lastposter]=@lastposter  
  1763. WHERE [lastposterid]=@lastposterid
  1764. GO
  1765. IF OBJECT_ID('dnt_updatetopicposter','P') IS NOT NULL
  1766. DROP PROC [dnt_updatetopicposter]
  1767. GO
  1768. CREATE PROCEDURE [dnt_updatetopicposter]
  1769. @poster NVARCHAR(20),
  1770. @posterid INT
  1771. AS
  1772. UPDATE [dnt_topics] 
  1773. SET [poster]=@poster 
  1774. WHERE [posterid]=@posterid
  1775. GO
  1776. IF OBJECT_ID('dnt_updatepostposter','P') IS NOT NULL
  1777. DROP PROC [dnt_updatepostposter]
  1778. GO
  1779. CREATE PROCEDURE [dnt_updatepostposter]
  1780. @poster NVARCHAR(20),
  1781. @posterid INT
  1782. AS
  1783. DECLARE @count INT,
  1784. @begin INT,
  1785. @tableid INT
  1786. SET @begin = 1
  1787. DECLARE @tempposttable TABLE 
  1788. (
  1789. [ROWID] INT IDENTITY(1,1),
  1790. [tableid] INT
  1791. )
  1792. INSERT INTO @tempposttable 
  1793. SELECT id FROM dnt_tablelist
  1794. SELECT @count=COUNT(1) FROM @tempposttable
  1795. WHILE @begin <= @count
  1796. BEGIN
  1797. SELECT  @tableid = [tableid] FROM @tempposttable WHERE [ROWID] = @begin
  1798. EXEC('UPDATE [dnt_posts' + @tableid + '] SET [poster]=''' + @poster + ''' WHERE [posterid]=' + @posterid )
  1799. SET @begin = @begin + 1
  1800. END
  1801. GO
  1802. IF OBJECT_ID('dnt_updatemoderatorname','P') IS NOT NULL
  1803. DROP PROC [dnt_updatemoderatorname]
  1804. GO
  1805. CREATE PROC [dnt_updatemoderatorname]
  1806. @oldname NVARCHAR(20),
  1807. @newname VARCHAR(20)
  1808. AS
  1809. IF @newname <> ''
  1810. UPDATE dnt_forumfields 
  1811. SET MODERATORS=REPLACE(LTRIM(RTRIM(REPLACE(' '+REPLACE(CONVERT(NVARCHAR(500),MODERATORS),',',' ')+' ',' '+@oldname+' ',' '+@newname+' '))),' ',',')
  1812. WHERE CHARINDEX(','+@oldname+',',','+CONVERT(NVARCHAR(500),MODERATORS)+',') > 0
  1813. ELSE
  1814. UPDATE dnt_forumfields 
  1815. SET MODERATORS = REPLACE(LTRIM(RTRIM(REPLACE(REPLACE(','+CONVERT(NVARCHAR(500),MODERATORS)+',',','+@oldname+',',','),',',' '))),' ',',')
  1816. WHERE CHARINDEX(','+@oldname+',',','+CONVERT(NVARCHAR(500),MODERATORS)+',') > 0
  1817. GO
  1818. IF OBJECT_ID('dnt_updateonlinelist','P') IS NOT NULL
  1819. DROP PROC [dnt_updateonlinelist]
  1820. GO
  1821. CREATE PROC [dnt_updateonlinelist]
  1822. @title NVARCHAR(50),
  1823. @groupid INT
  1824. AS
  1825. UPDATE [dnt_onlinelist] SET [title]=@title WHERE [groupid]=@groupid
  1826. GO
  1827. --此存储过程的作用是更新总帖子数
  1828. IF OBJECT_ID('dnt_resetforumsposts','P') IS NOT NULL
  1829. DROP PROC [dnt_resetforumsposts]
  1830. GO
  1831. CREATE PROC [dnt_resetforumsposts]
  1832. AS
  1833. --清空forums表中的帖子数,以便重新统计
  1834. UPDATE dnt_forums SET posts=0
  1835. DECLARE @i INT,
  1836. @maxlayer INT,
  1837. @maxtableid INT,
  1838. @tablename NVARCHAR(50)
  1839. SET @i = 1
  1840. --取分表数
  1841. SELECT @maxtableid = MAX(id) FROM dnt_tablelist
  1842. --依次处理分表
  1843. WHILE @i <= @maxtableid
  1844. BEGIN
  1845. SET @tablename = 'dnt_posts' + CONVERT(NVARCHAR(2),@i)
  1846. --如果分表不存在则继续处理下一个
  1847. IF NOT EXISTS(SELECT * FROM SYSOBJECTS WHERE id = OBJECT_ID(@tablename) AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
  1848. CONTINUE
  1849. --取得每个版块在当前分表中的帖子数,累加并更新forums表中版块帖子数
  1850. EXEC('
  1851. UPDATE dnt_forums 
  1852. SET posts=posts+rightTable.fidcount 
  1853. FROM dnt_forums 
  1854. INNER JOIN (SELECT fid,COUNT(fid) fidcount FROM '+ @tablename +' GROUP BY fid) AS rightTable
  1855. ON dnt_forums.fid=rightTable.fid
  1856. WHERE dnt_forums.fid=rightTable.fid
  1857. ')
  1858. SET @i = @i + 1
  1859. END
  1860. --以下操作将子版块中的帖子总数累加并更新到父版块中
  1861. --取得版块的最大层数
  1862. SELECT @maxlayer = MAX(layer) FROM dnt_forums
  1863. --倒序更新,从最底层版块更新到最顶层
  1864. WHILE @maxlayer > 0
  1865. BEGIN
  1866. --将子版块中的帖子总数累加并更新到父版块中
  1867. UPDATE dnt_forums 
  1868. SET posts=posts+rightTable.sumposts 
  1869. FROM dnt_forums AS leftTable 
  1870. INNER JOIN (SELECT parentid,SUM(posts) sumposts FROM dnt_forums WHERE layer=@maxlayer GROUP BY parentid) AS rightTable
  1871. ON leftTable.fid=rightTable.parentid
  1872. WHERE leftTable.fid=rightTable.parentid
  1873. SET @maxlayer = @maxlayer - 1
  1874. END
  1875. GO
  1876. --此存储过程的作用是更新最后发帖
  1877. --测试代码:
  1878. --SELECT parentid,fid,lasttid,lasttitle,lastpost,lastposterid,lastposter FROM #tempTable2 ORDER BY parentid
  1879. --SELECT fid,lasttid,lasttitle,lastpost,parentid,lastposterid,lastposter FROM dnt_forums WHERE layer = 6 AND fid IN (245,256,260,259,273)
  1880. IF OBJECT_ID('dnt_resetlastpostinfo','P') IS NOT NULL
  1881. DROP PROC [dnt_resetlastpostinfo]
  1882. GO
  1883. CREATE PROC [dnt_resetlastpostinfo]
  1884. AS
  1885. --创建两个临时表#tempTable1,#tempTable2
  1886. CREATE TABLE #tempTable1 (
  1887. fid INT,
  1888. lasttid INT,
  1889. lasttitle NVARCHAR(60),
  1890. lastpost DATETIME,
  1891. parentid INT,
  1892. lastposterid INT,
  1893. lastposter NVARCHAR(30)
  1894.  )
  1895. CREATE table #tempTable2 (
  1896. fid INT,
  1897. lasttid INT,
  1898. lasttitle NVARCHAR(60),
  1899. lastpost DATETIME,
  1900. parentid INT,
  1901. lastposterid INT,
  1902. lastposter NVARCHAR(30)
  1903. )
  1904. DECLARE @maxlayer INT  -- 用来存放最大layer的变量
  1905. SELECT @maxlayer = MAX(layer) FROM dnt_forums;
  1906. --先初始化dnt_forums表中最后发帖等的信息
  1907. UPDATE dnt_forums SET lasttid = 0, lasttitle = '从未',lastpost = '1900-1-1',lastposterid=0,lastposter=''
  1908. --更新所有有主题的版块的最后发帖人等信息
  1909. UPDATE dnt_forums 
  1910. SET lasttid = rightTable.tid, lasttitle = rightTable.title, lastpost = rightTable.lastpost, lastposterid=rightTable.lastposterid, lastposter=rightTable.lastposter
  1911. FROM dnt_forums AS leftTable
  1912. 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
  1913. ON leftTable.fid = rightTable.fid
  1914. WHERE leftTable.fid = rightTable.fid
  1915. WHILE @maxlayer > 0
  1916. BEGIN
  1917. --清空临时表#tempTable1,为了保证第二次循环时,表是空的
  1918. DELETE FROM #tempTable1;
  1919. --首先以dnt_forums表中的parentid字段分组,查询出每组最后回复时间最晚的记录;
  1920. --然后LEFT JOIN表dnt_forums,以两个表中parentid相等并且lastpost相等为条件,进行查询,
  1921. --并把结果插入到临时表#tempTable1中,以备临时表#tempTable2用
  1922. INSERT INTO #tempTable1 
  1923. SELECT fid,lasttid,lasttitle,a.lastpost,a.parentid,a.lastposterid,a.lastposter 
  1924. FROM (
  1925. SELECT parentid,MAX(lastpost) lastpost FROM dnt_forums a WHERE layer = @maxlayer GROUP BY parentid
  1926.  ) b 
  1927. LEFT JOIN dnt_forums a
  1928. ON a.parentid=b.parentid and a.lastpost=b.lastpost
  1929. --清空临时表#tempTable2,为了保证第二次循环时,表是空的
  1930. DELETE FROM #tempTable2;
  1931. --首先以#tempTable1表中的parentid分组,查询出组中的MAX(fid);
  1932. --然后以fid IN (子查询中查出的MAX(fid))为条件,查询出结果并插入到#tempTable2中
  1933. INSERT INTO #tempTable2 
  1934. SELECT * FROM #tempTable1
  1935. WHERE fid IN (SELECT MAX(fid) FROM #tempTable1 GROUP BY parentid,lastpost)
  1936. --根据临时表#tempTable2中的内容,更新dnt_forums表的最后发帖人等信息
  1937. UPDATE dnt_forums 
  1938. SET lastpost = CASE WHEN #tempTable2.lastpost>dnt_forums.lastpost THEN #tempTable2.lastpost
  1939.    ELSE dnt_forums.lastpost END,
  1940. lasttitle = CASE WHEN #tempTable2.lastpost>dnt_forums.lastpost THEN #tempTable2.lasttitle
  1941. ELSE dnt_forums.lasttitle END,
  1942. lasttid = CASE WHEN #tempTable2.lastpost>dnt_forums.lastpost THEN #tempTable2.lasttid
  1943. ELSE dnt_forums.lasttid END,
  1944. lastposterid = CASE WHEN #tempTable2.lastpost>dnt_forums.lastpost THEN #tempTable2.lastposterid
  1945. ELSE dnt_forums.lastposterid END,
  1946. lastposter = CASE WHEN #tempTable2.lastpost>dnt_forums.lastpost THEN #tempTable2.lastposter
  1947. ELSE dnt_forums.lastposter END
  1948. FROM dnt_forums 
  1949. INNER JOIN #tempTable2 
  1950. ON dnt_forums.fid=#tempTable2.parentid 
  1951. WHERE dnt_forums.fid=#tempTable2.parentid;
  1952. SET @maxlayer = @maxlayer - 1;
  1953. END
  1954. DROP TABLE #tempTable2;
  1955. DROP TABLE #tempTable1;
  1956. GO
  1957. /*
  1958. 此存储过程的作用:
  1959. 根据参数中传递过来的分表名更新主题的最后回复等信息
  1960. */
  1961. IF OBJECT_ID('dnt_resetLastRepliesInfoOftopics','P') IS NOT NULL
  1962. DROP PROC [dnt_resetLastRepliesInfoOftopics]
  1963. GO
  1964. CREATE PROC [dnt_resetLastRepliesInfoOftopics]
  1965. @posttable NVARCHAR(20)
  1966. AS
  1967. EXEC('
  1968. UPDATE dnt_topics 
  1969. SET dnt_topics.replies = lastPostTable.posts, 
  1970. lastpost = lastPostTable.postdatetime, 
  1971. lastposterid = lastPostTable.posterid, 
  1972. lastposter = lastPostTable.poster, 
  1973. lastpostid = lastPostTable.pid 
  1974. FROM dnt_topics
  1975. INNER JOIN  (
  1976. SELECT leftTable.tid,leftTable.posterid,leftTable.pid,leftTable.poster,leftTable.postdatetime,rightTable.posts FROM '+@posttable+' AS leftTable 
  1977. LEFT JOIN (SELECT tid,COUNT(*)-1 posts,MAX(pid) maxpid FROM '+@posttable+' GROUP BY tid) AS rightTable
  1978. ON leftTable.pid = rightTable.maxpid
  1979. WHERE leftTable.pid = rightTable.maxpid
  1980. ) AS lastPostTable
  1981. ON dnt_topics.tid = lastPostTable.tid
  1982. WHERE dnt_topics.tid = lastPostTable.tid AND dnt_topics.lastpostid <> lastPostTable.pid
  1983. ')
  1984. GO
  1985. /*
  1986. 此存储过程的作用:
  1987. 更新所有用户的精华帖数
  1988. */
  1989. IF OBJECT_ID('dnt_resetuserdigestposts','P') IS NOT NULL
  1990. DROP PROC [dnt_resetuserdigestposts]
  1991. GO
  1992. CREATE PROC [dnt_resetuserdigestposts]
  1993. AS
  1994. UPDATE dnt_users
  1995. SET [digestposts] = leftTable.topiccount 
  1996. FROM dnt_users 
  1997. INNER JOIN (SELECT COUNT(tid) topiccount,posterid FROM dnt_topics WHERE [digest] > 0 GROUP BY posterid) AS leftTable
  1998. ON dnt_users.[uid] = leftTable.posterid
  1999. WHERE dnt_users.[uid] = leftTable.posterid AND dnt_users.posts <> 0
  2000. GO
  2001. /*
  2002. 此存储过程的作用:
  2003. 根据参数中传递过来的分表名更新用户的帖子数
  2004. 后台更新所有用户帖子数时使用
  2005. */
  2006. IF OBJECT_ID('dnt_resetuserspostcount','P') IS NOT NULL
  2007. DROP PROC [dnt_resetuserspostcount]
  2008. GO
  2009. CREATE PROC [dnt_resetuserspostcount]
  2010. @posttablename NVARCHAR(50)
  2011. AS
  2012. EXEC('
  2013. UPDATE dnt_users
  2014. SET posts = posts + leftTable.postcount 
  2015. FROM dnt_users 
  2016. INNER JOIN (SELECT posterid,COUNT(pid) postcount FROM '+@posttablename+' GROUP BY posterid) AS leftTable
  2017. ON dnt_users.[uid] = leftTable.posterid
  2018. WHERE dnt_users.[uid] = leftTable.posterid
  2019. ')
  2020. GO
  2021. CREATE PROCEDURE [dnt_createinvitecode]
  2022.                     @code char(8),
  2023.                     @creatorid int,
  2024.                     @creator nchar(20),
  2025.                     @createtime smalldatetime,
  2026.                     @expiretime smalldatetime,
  2027.                     @maxcount int,
  2028.                     @invitetype int
  2029.                     AS
  2030.                     
  2031.                     INSERT INTO [dnt_invitation]([invitecode],[creatorid],[creator],[createdtime],[expiretime],[maxcount],[invitetype])
  2032.                      VALUES(@code,@creatorid,@creator,@createtime,@expiretime,@maxcount,@invitetype);SELECT SCOPE_IDENTITY()
  2033. GO
  2034. CREATE PROCEDURE [dnt_deleteinvitecode]
  2035. @id int
  2036. AS
  2037. BEGIN
  2038. UPDATE [dnt_invitation] SET [isdeleted]=1 WHERE [inviteid]=@id
  2039. END
  2040. GO
  2041. CREATE PROCEDURE [dnt_clearexpireinvitecode]
  2042. AS
  2043. BEGIN
  2044. DELETE [dnt_invitation] WHERE [invitetype]=3 AND [createdtime]<>[expiretime] AND ([expiretime]-GETDATE())<=0;
  2045. DELETE [dnt_invitation] WHERE [isdeleted]=1 AND [createdtime]<(GETDATE()-1)
  2046. END
  2047. GO
  2048. CREATE PROCEDURE [dnt_getinvitecode]
  2049. @searchtype nchar(10),
  2050. @searchkey nchar(20)
  2051. AS
  2052. BEGIN
  2053. IF @searchtype='uid'
  2054.    SELECT TOP 1 [inviteid],[invitecode],[invitetype],[createdtime],[creator],[creatorid],[expiretime],[maxcount],[successcount] 
  2055. FROM [dnt_invitation] WHERE [creatorid]=@searchkey AND [invitetype]=2 AND [isdeleted]=0 ORDER BY [inviteid] DESC
  2056. ELSE IF @searchtype='id'
  2057.    SELECT [inviteid],[invitecode],[invitetype],[createdtime],[creator],[creatorid],[expiretime],[maxcount],[successcount] FROM 
  2058. [dnt_invitation] WHERE [inviteid]=@searchkey AND [isdeleted]=0
  2059. ELSE IF @searchtype='code'
  2060.    SELECT [inviteid],[invitecode],[invitetype],[createdtime],[creator],[creatorid],[expiretime],[maxcount],[successcount] FROM 
  2061. [dnt_invitation] WHERE [invitecode]=@searchkey AND [isdeleted]=0
  2062. ELSE
  2063.    SELECT (1)
  2064. END
  2065. GO
  2066. IF OBJECT_ID('dnt_getinvitecodelistbyuid','P') IS NOT NULL
  2067. DROP PROC [dnt_getinvitecodelistbyuid]
  2068. GO
  2069. CREATE PROCEDURE [dnt_getinvitecodelistbyuid]
  2070. @creatorid int,
  2071. @pageindex int
  2072. AS
  2073. DECLARE @startRow int,
  2074. @endRow int
  2075. SET @startRow = (@pageIndex - 1) * 10
  2076. IF @pageindex = 1
  2077. BEGIN
  2078. SELECT 
  2079. TOP 10
  2080. [inviteid],
  2081. [invitecode],
  2082. [invitetype],
  2083. [createdtime],
  2084. [creator],
  2085. [creatorid],
  2086. [expiretime],
  2087. [maxcount],
  2088. [successcount]
  2089. FROM [dnt_invitation]
  2090. WHERE [creatorid]=@creatorid AND [invitetype]=3 AND [isdeleted]=0 ORDER BY [inviteid] DESC
  2091. END
  2092. ELSE
  2093. BEGIN
  2094. EXEC('
  2095. SELECT 
  2096. TOP 10
  2097. [inviteid],
  2098. [invitecode],
  2099. [invitetype],
  2100. [createdtime],
  2101. [creator],
  2102. [creatorid],
  2103. [expiretime],
  2104. [maxcount],
  2105. [successcount]
  2106. FROM [dnt_invitation]
  2107. WHERE [creatorid]=@creatorid AND [invitetype]=3 AND [isdeleted]=0
  2108. AND inviteid < (SELECT MIN(inviteid) FROM ( SELECT TOP '+@startRow+' inviteid 
  2109. FROM [dnt_invitation] 
  2110. WHERE [creatorid]=@creatorid AND [invitetype]=3 AND [isdeleted]=0
  2111. RDER BY [inviteid] DESC
  2112.    ) AS T
  2113. )
  2114. ORDER BY [inviteid] DESC
  2115. ')
  2116. END
  2117. GO
  2118. CREATE PROCEDURE [dnt_isinvitecodeexist]
  2119. @code nchar(8)
  2120. AS
  2121. BEGIN
  2122. SELECT COUNT(1) FROM [dnt_invitation] WHERE [invitecode]=@code AND [isdeleted] =0
  2123. END
  2124. GO
  2125. CREATE PROCEDURE [dnt_updateinvitecodesuccesscount]
  2126. @id int
  2127. AS
  2128. BEGIN
  2129. UPDATE [dnt_invitation] SET [successcount]=[successcount]+1 WHERE [inviteid]=@id
  2130. END
  2131. GO
  2132. CREATE PROCEDURE [dnt_getuserinvitecodecount]
  2133. @creatorid int
  2134. AS
  2135. BEGIN
  2136. SELECT COUNT(1) FROM [dnt_invitation] WHERE [creatorid]=@creatorid AND [invitetype]=3 AND [isdeleted]=0
  2137. END
  2138. GO
  2139. CREATE PROCEDURE [dnt_gettodayusercreatedinvitecode]
  2140. @creatorid int
  2141. AS
  2142. BEGIN
  2143. SELECT COUNT(1) FROM [dnt_invitation] WHERE [creatorid]=@creatorid AND [invitetype]=2 AND (GETDATE()- [createdtime])<1
  2144. END
  2145. GO
  2146. IF OBJECT_ID('dnt_getattachmentlistbypid','P') IS NOT NULL
  2147. DROP PROC [dnt_getattachmentlistbypid]
  2148. GO
  2149. CREATE PROCEDURE [dnt_getattachmentlistbypid]
  2150. @pidlist varchar(500)
  2151. AS
  2152. EXEC('SELECT 
  2153. [aid],
  2154. [uid],
  2155. [tid],
  2156. [pid],
  2157. [postdatetime],
  2158. [readperm],
  2159. [filename],
  2160. [description],
  2161. [filetype],
  2162. [filesize],
  2163. [attachment],
  2164. [downloads],
  2165. [attachprice],
  2166. [width],
  2167. [height] 
  2168. FROM [dnt_attachments] 
  2169. WHERE [pid] IN ('+@pidlist+')')
  2170. GO
  2171. IF OBJECT_ID('dnt_getattachmentlistbyaid','P') IS NOT NULL
  2172. DROP PROC [dnt_getattachmentlistbyaid]
  2173. GO
  2174. CREATE PROCEDURE [dnt_getattachmentlistbyaid]
  2175. @aidlist varchar(500)
  2176. AS
  2177. SELECT [aid], [tid], [pid], [filename] FROM [dnt_attachments] WITH (NOLOCK) WHERE [aid] IN (SELECT [item] FROM [dnt_split](@aidlist, ',') )
  2178. GO
  2179. IF OBJECT_ID('dnt_deleteattachmentbyaidlist','P') IS NOT NULL
  2180. DROP PROC [dnt_deleteattachmentbyaidlist]
  2181. GO
  2182. CREATE PROCEDURE [dnt_deleteattachmentbyaidlist]
  2183. @aidlist VARCHAR(500)
  2184. AS
  2185. EXEC('DELETE FROM [dnt_attachments] WHERE [aid] IN ('+@aidlist+')')
  2186. GO
  2187. IF OBJECT_ID('dnt_deleteattachmentbytidlist','P') IS NOT NULL
  2188. DROP PROC [dnt_deleteattachmentbytidlist]
  2189. GO
  2190. CREATE PROCEDURE [dnt_deleteattachmentbytidlist]
  2191. @tidlist VARCHAR(500)
  2192. AS
  2193. EXEC('DELETE FROM [dnt_attachments] WHERE [tid] IN ('+@tidlist+')')
  2194. GO
  2195. IF OBJECT_ID('dnt_getattachmentlistbytidlist','P') IS NOT NULL
  2196. DROP PROC [dnt_getattachmentlistbytidlist]
  2197. GO
  2198. CREATE PROCEDURE [dnt_getattachmentlistbytidlist]
  2199. @tidlist VARCHAR(500)
  2200. AS
  2201. SELECT 
  2202. [aid],
  2203. [filename] 
  2204. FROM [dnt_attachments] WITH (NOLOCK)
  2205. WHERE [tid] IN (SELECT [item]FROM [dnt_split](@tidlist, ','))
  2206. GO
  2207. IF OBJECT_ID('dnt_updateuseronlinestates','P') IS NOT NULL
  2208. DROP PROCEDURE [dnt_updateuseronlinestates]
  2209. GO
  2210. CREATE PROCEDURE [dnt_updateuseronlinestates] 
  2211. @uidlist varchar(5000) = '' 
  2212. AS
  2213. EXEC ('UPDATE [dnt_users] SET [onlinestate]=0,[lastactivity]=GETDATE() WHERE [uid] IN ('+@uidlist+')')
  2214. GO
  2215. /*
  2216. 修改时间:2009-11-23
  2217. 修改原因:将CHARINDEX替换成了IN
  2218. 测试代码:
  2219. exec [dnt_deletetopicbytidlist1] '1246471,1246472',1
  2220. */
  2221. IF OBJECT_ID('dnt_deletetopicbytidlist1','P') IS NOT NULL
  2222. DROP PROCEDURE [dnt_deletetopicbytidlist1]
  2223. GO
  2224. CREATE PROCEDURE [dnt_deletetopicbytidlist1]
  2225.     @tidlist AS VARCHAR(2000),
  2226.     @chanageposts AS BIT
  2227. AS
  2228.     DECLARE @postcount int
  2229.     DECLARE @topiccount int
  2230.     DECLARE @todaycount int
  2231.     DECLARE @sqlstr nvarchar(4000)
  2232.     DECLARE @fid varchar(2000)
  2233.     DECLARE @posterid varchar(200)
  2234.     DECLARE @tempFid int
  2235.     DECLARE @tempPosterid int
  2236.     DECLARE @tempLayer int
  2237.     DECLARE @temppostdatetime datetime
  2238.     DECLARE @tempfidlist AS VARCHAR(1000)
  2239.     SET @fid = ''
  2240.     SET @posterid = ''
  2241.     SET @postcount=0
  2242.     SET @topiccount=0
  2243.     SET @todaycount=0
  2244.     SET @tempfidlist = '';
  2245.     IF @tidlist<>''
  2246.         BEGIN
  2247.             EXEC('DECLARE cu_dnt_posts CURSOR FOR SELECT [fid],[posterid],[layer],[postdatetime] FROM [dnt_posts1] WHERE [dnt_posts1].[tid] IN ('+@tidlist+')')
  2248.             OPEN cu_dnt_posts
  2249.             FETCH NEXT FROM cu_dnt_posts into @tempFid,@tempPosterid,@tempLayer,@temppostdatetime
  2250.             WHILE @@FETCH_STATUS = 0
  2251.                 BEGIN
  2252.                     SET @postcount = @postcount + 1
  2253.                     IF @tempLayer = 0
  2254.                     BEGIN
  2255.                     SET @topiccount = @topiccount + 1
  2256.                     END
  2257.                     IF DATEDIFF(d,@temppostdatetime,GETDATE()) = 0
  2258.                     BEGIN
  2259.                     SET @todaycount = @todaycount + 1
  2260.                     END
  2261.                     IF CHARINDEX(',' + LTRIM(STR(@tempFid)) + ',',@fid + ',') = 0
  2262.                     BEGIN
  2263.                     --SET @fid = @fid + ',' + LTRIM(STR(@tempFid))
  2264.                     SELECT @tempfidlist = ISNULL([parentidlist],'') FROM [dnt_forums] WHERE [fid] = @tempFid
  2265.                     IF RTRIM(@tempfidlist)<>''
  2266.                     BEGIN
  2267.                     SET @fid = RTRIM(@fid) + ',' +  RTRIM(@tempfidlist) + ',' + CAST(@tempFid AS VARCHAR(10))
  2268.                     END
  2269.                     ELSE
  2270.                     BEGIN
  2271.                     SET @fid =RTRIM(@fid) + ',' +  CAST(@tempFid AS VARCHAR(10))
  2272.                     END
  2273.                     END
  2274.                     IF @chanageposts = 1
  2275.                     BEGIN
  2276.                     UPDATE [dnt_users] SET [posts] = [posts] - 1 WHERE [uid] = @tempPosterid
  2277.                     END
  2278.                     FETCH NEXT FROM cu_dnt_posts into @tempFid,@tempPosterid,@tempLayer,@temppostdatetime
  2279.                 END
  2280.             CLOSE cu_dnt_posts
  2281.             DEALLOCATE cu_dnt_posts
  2282.             IF LEN(@fid)>0
  2283.                 BEGIN
  2284.                     SET @fid = SUBSTRING(@fid,2,LEN(@fid)-1)
  2285.                     IF @chanageposts = 1
  2286.                     BEGIN
  2287.                     UPDATE [dnt_statistics] SET [totaltopic]=[totaltopic] - @topiccount, [totalpost]=[totalpost] - @postcount
  2288.                     EXEC('UPDATE [dnt_forums] 
  2289.                     SET [posts]=[posts] - ' + @postcount + ',  
  2290.                     [topics]=[topics] - ' + @topiccount + ', 
  2291.                     [todayposts] = [todayposts] - ' + @todaycount + ' 
  2292.                     WHERE [fid] IN ('+@fid+')')
  2293.                     END
  2294.                     EXEC('DELETE FROM [dnt_favorites] WHERE [tid] IN ('+@tidlist+') AND [typeid]=0')
  2295.                     EXEC('DELETE FROM [dnt_polls] WHERE [tid] IN ('+@tidlist+')')
  2296.                     EXEC('DELETE FROM [dnt_posts1] WHERE [tid] IN ('+@tidlist+')')
  2297.                     EXEC('DELETE FROM [dnt_mytopics] WHERE [tid] IN ('+@tidlist+')')
  2298.                 END
  2299.             EXEC('DELETE FROM [dnt_topics] WHERE [closed] IN ('+@tidlist+') OR [tid] IN ('+@tidlist+')')
  2300.             EXEC('UPDATE [dnt_tags] SET [count]=[count]-1, [fcount]=[fcount]-1 WHERE [tagid] IN (SELECT [tagid] FROM [dnt_topictags] WHERE [tid] IN ('+@tidlist+'))') 
  2301.             EXEC('DELETE FROM [dnt_topictags] WHERE [tid] IN ('+@tidlist+')')
  2302.             EXEC('DELETE FROM [dnt_topictagcaches] WHERE [tid] IN ('+@tidlist+') OR [linktid] IN ('+@tidlist+')')
  2303. END
  2304. GO
  2305. /*修改存储过程的名字*/
  2306. IF OBJECT_ID('dnt_getattachmentlistbypid','P') IS NOT NULL
  2307. DROP PROC [dnt_getattachmentlistbypid]
  2308. GO
  2309. IF OBJECT_ID('dnt_getattachmentlistbypidlist','P') IS NOT NULL
  2310. DROP PROC [dnt_getattachmentlistbypidlist]
  2311. GO
  2312. CREATE PROCEDURE [dnt_getattachmentlistbypidlist]
  2313. @pidlist varchar(500)
  2314. AS
  2315. SELECT 
  2316. [aid],
  2317. [uid],
  2318. [tid],
  2319. [pid],
  2320. [postdatetime],
  2321. [readperm],
  2322. [filename],
  2323. [description],
  2324. [filetype],
  2325. [filesize],
  2326. [attachment],
  2327. [downloads],
  2328. [attachprice],
  2329. [width],
  2330. [height] 
  2331. FROM [dnt_attachments] WITH (NOLOCK)
  2332. WHERE [dnt_attachments].[pid] IN (SELECT [item]FROM [dnt_split](@pidlist, ',') )
  2333. GO
  2334. /*修改存储过程的名字*/
  2335. IF OBJECT_ID('dnt_getattachenmtlistbypid','P') IS NOT NULL
  2336. DROP PROC [dnt_getattachenmtlistbypid]
  2337. GO
  2338. IF OBJECT_ID('dnt_getattachmentlistbypid','P') IS NOT NULL
  2339. DROP PROC [dnt_getattachmentlistbypid]
  2340. GO
  2341. CREATE PROCEDURE [dnt_getattachmentlistbypid]
  2342. @pid INT
  2343. AS
  2344. SELECT 
  2345. [aid],
  2346. [uid],
  2347. [tid],
  2348. [pid],
  2349. [postdatetime],
  2350. [readperm],
  2351. [filename],
  2352. [description],
  2353. [filetype],
  2354. [filesize],
  2355. [attachment],
  2356. [downloads],
  2357. [attachprice],
  2358. [width],
  2359. [height] 
  2360. FROM [dnt_attachments] 
  2361. WHERE [pid]=@pid
  2362. GO
  2363. IF OBJECT_ID('dnt_getattachpaymentlogbyuid','P') IS NOT NULL
  2364. DROP PROC [dnt_getattachpaymentlogbyuid]
  2365. GO
  2366. CREATE PROCEDURE [dnt_getattachpaymentlogbyuid]
  2367. @attachidlist varchar(500),
  2368. @uid int
  2369. AS
  2370. EXEC('
  2371. SELECT 
  2372. [aid]
  2373. FROM [dnt_attachpaymentlog] 
  2374. WHERE [uid] = '+@uid+' AND [dnt_attachpaymentlog].[aid] IN ('+@attachidlist+')
  2375. ')
  2376. GO
  2377. IF OBJECT_ID('dnt_getindexforumlist','P') IS NOT NULL
  2378. DROP PROC [dnt_getindexforumlist]
  2379. GO
  2380. CREATE PROCEDURE [dnt_getindexforumlist]
  2381. AS
  2382. SELECT CASE WHEN DATEDIFF(n, [lastpost], GETDATE())<600 THEN 'new' ELSE 'old' END AS [havenew],
  2383. [dnt_forums].[allowbbcode],
  2384. [dnt_forums].[allowblog],
  2385. [dnt_forums].[alloweditrules],
  2386. [dnt_forums].[allowhtml],
  2387. [dnt_forums].[allowimgcode],
  2388. [dnt_forums].[allowpostspecial],
  2389. [dnt_forums].[allowrss],
  2390. [dnt_forums].[allowsmilies],
  2391. [dnt_forums].[allowspecialonly],
  2392. [dnt_forums].[allowtag],
  2393. [dnt_forums].[allowthumbnail],
  2394. [dnt_forums].[autoclose],
  2395. [dnt_forums].[colcount],
  2396. [dnt_forums].[curtopics],
  2397. [dnt_forums].[disablewatermark],
  2398. [dnt_forums].[displayorder],
  2399. [dnt_forums].[fid],
  2400. [dnt_forums].[inheritedmod],
  2401. [dnt_forums].[istrade],
  2402. [dnt_forums].[jammer],
  2403. [dnt_forums].[lastpost],
  2404. [dnt_forums].[lastposter],
  2405. [dnt_forums].[lastposterid],
  2406. [dnt_forums].[lasttid],
  2407. [dnt_forums].[lasttitle],
  2408. [dnt_forums].[layer],
  2409. [dnt_forums].[modnewposts],
  2410. [dnt_forums].[name],
  2411. [dnt_forums].[parentid],
  2412. [dnt_forums].[parentidlist],
  2413. [dnt_forums].[pathlist],
  2414. [dnt_forums].[posts],
  2415. [dnt_forums].[recyclebin],
  2416. [dnt_forums].[status],
  2417. [dnt_forums].[subforumcount],
  2418. [dnt_forums].[templateid],
  2419. [dnt_forums].[todayposts],
  2420. [dnt_forums].[topics],
  2421. [dnt_forumfields].[applytopictype],
  2422. [dnt_forumfields].[attachextensions],
  2423. [dnt_forumfields].[description],
  2424. [dnt_forumfields].[fid],
  2425. [dnt_forumfields].[getattachperm],
  2426. [dnt_forumfields].[icon],
  2427. [dnt_forumfields].[moderators],
  2428. [dnt_forumfields].[password],
  2429. [dnt_forumfields].[permuserlist],
  2430. [dnt_forumfields].[postattachperm],
  2431. [dnt_forumfields].[postbytopictype],
  2432. [dnt_forumfields].[postcredits],
  2433. [dnt_forumfields].[postperm],
  2434. [dnt_forumfields].[redirect],
  2435. [dnt_forumfields].[replycredits],
  2436. [dnt_forumfields].[replyperm],
  2437. [dnt_forumfields].[rewritename],
  2438. [dnt_forumfields].[rules],
  2439. [dnt_forumfields].[seodescription],
  2440. [dnt_forumfields].[seokeywords],
  2441. [dnt_forumfields].[topictypeprefix],
  2442. [dnt_forumfields].[topictypes],
  2443. [dnt_forumfields].[viewbytopictype],
  2444. [dnt_forumfields].[viewperm]
  2445. FROM [dnt_forums] 
  2446. LEFT JOIN [dnt_forumfields] ON [dnt_forums].[fid]=[dnt_forumfields].[fid] 
  2447. WHERE [dnt_forums].[status] > 0 AND [layer] <= 1 
  2448. AND [dnt_forums].[parentid] NOT IN (SELECT [fid] FROM [dnt_forums] WHERE [status] < 1 AND [layer] = 0) ORDER BY [displayorder]
  2449. GO
  2450. IF OBJECT_ID('dnt_resetforumstopics','P') IS NOT NULL
  2451. DROP PROC [dnt_resetforumstopics]
  2452. GO
  2453. CREATE PROC [dnt_resetforumstopics]
  2454. AS
  2455. --更新所有版块的主题数,不包含子版块
  2456. UPDATE dnt_forums 
  2457. SET curtopics=rightTable.sumtopics,topics = rightTable.sumtopics
  2458. FROM dnt_forums 
  2459. INNER JOIN (SELECT fid,count(tid) sumtopics FROM dnt_topics GROUP BY fid) AS rightTable
  2460. ON dnt_forums.fid=rightTable.fid
  2461. WHERE dnt_forums.fid=rightTable.fid
  2462. DECLARE @maxlayer INT
  2463. SELECT @maxlayer = MAX(layer) FROM dnt_forums
  2464. --倒序更新,从最底层版块更新到最顶层
  2465. WHILE @maxlayer > 0
  2466. BEGIN
  2467. --将子版块中的帖子总数累加并更新到父版块中
  2468. UPDATE dnt_forums 
  2469. SET topics=topics+rightTable.sumtopics 
  2470. FROM dnt_forums AS leftTable 
  2471. INNER JOIN (SELECT parentid,SUM(topics) sumtopics FROM dnt_forums WHERE layer=@maxlayer GROUP BY parentid) AS rightTable
  2472. ON leftTable.fid=rightTable.parentid
  2473. WHERE leftTable.fid=rightTable.parentid
  2474. SET @maxlayer = @maxlayer - 1
  2475. END
  2476. GO
  2477. if exists (select * from sysobjects where id = object_id(N'[dnt_createorder]') and OBJECTPROPERTY(id,N'IsProcedure') = 1)
  2478. drop procedure [dnt_createorder]
  2479. GO
  2480. CREATE PROCEDURE [dnt_createorder]
  2481. @ordercode char(32),
  2482. @uid int,
  2483. @buyer char(20),
  2484. @paytype tinyint,
  2485. @price decimal(18,2),
  2486. @orderstatus tinyint,
  2487. @credit tinyint,
  2488. @amount int
  2489. AS
  2490. BEGIN
  2491. INSERT INTO [dnt_orders]([ordercode],[uid],[buyer],[paytype],[price],[orderstatus],
  2492. [credit],[amount]) VALUES(@ordercode,@uid,@buyer,@paytype,@price,@orderstatus,@credit,@amount)
  2493. END
  2494. GO
  2495. if exists (select * from sysobjects where id = object_id(N'[dnt_getorderbyordercode]') and OBJECTPROPERTY(id,N'IsProcedure') = 1)
  2496. drop procedure [dnt_getorderbyordercode]
  2497. GO
  2498. CREATE PROCEDURE [dnt_getorderbyordercode]
  2499. @ordercode char(32)
  2500. AS
  2501. BEGIN
  2502. SELECT [orderid],[ordercode],[uid],[buyer],[paytype],[tradeno],[price],[orderstatus],[createdtime],[confirmedtime]
  2503. ,[credit],[amount] FROM [dnt_orders] WHERE [ordercode]=@ordercode
  2504. END
  2505. GO
  2506. if exists (select * from sysobjects where id = object_id(N'[dnt_getorderlist]') and OBJECTPROPERTY(id,N'IsProcedure') = 1)
  2507. drop procedure [dnt_getorderlist]
  2508. GO
  2509. CREATE PROCEDURE [dnt_getorderlist]
  2510. @searchcondition varchar(1000)
  2511. AS
  2512. BEGIN
  2513. DECLARE @script VARCHAR(1000)
  2514. SET @script= 'SELECT TOP 20 [orderid],[ordercode],[uid],[buyer],[paytype],[tradeno],[price],[orderstatus],[createdtime],[confirmedtime]
  2515. ,[credit],[amount] FROM [dnt_orders] '+@searchcondition+' ORDER BY [orderid] DESC'
  2516. EXEC(@script)
  2517. END
  2518. GO
  2519. if exists (select * from sysobjects where id = object_id(N'[dnt_getorderscount]') and OBJECTPROPERTY(id,N'IsProcedure') = 1)
  2520. drop procedure [dnt_getorderscount]
  2521. GO
  2522. CREATE PROCEDURE [dnt_getorderscount]
  2523. @searchcondition varchar(1000)
  2524. AS
  2525. BEGIN
  2526. DECLARE @script VARCHAR(1000)
  2527. SET @script= 'SELECT COUNT([orderid]) FROM [dnt_orders] '+@searchcondition
  2528. EXEC(@script)
  2529. END
  2530. GO
  2531. if exists (select * from sysobjects where id = object_id(N'[dnt_updateorderinfo]') and OBJECTPROPERTY(id,N'IsProcedure') = 1)
  2532. drop procedure [dnt_updateorderinfo]
  2533. GO
  2534. CREATE PROCEDURE [dnt_updateorderinfo]
  2535. @orderid int,
  2536. @tradeno char(32),
  2537. @orderstatus tinyint,
  2538. @confirmedtime smalldatetime
  2539. AS
  2540. BEGIN
  2541.     UPDATE [dnt_orders] SET [tradeno]=@tradeno,[orderstatus]=@orderstatus,[confirmedtime]=@confirmedtime WHERE [orderid]=@orderid
  2542. END
  2543. GO
  2544. if exists (select * from sysobjects where id = object_id(N'[dnt_clearexpirecreditorders]') and OBJECTPROPERTY(id,N'IsProcedure') = 1)
  2545. drop procedure [dnt_clearexpirecreditorders]
  2546. GO
  2547. CREATE PROCEDURE [dnt_clearexpirecreditorders]
  2548. AS
  2549. BEGIN
  2550.    DELETE [dnt_orders] WHERE [orderstatus] < 1 AND [createdtime] < (GETDATE()-15)
  2551. END
  2552. GO
  2553. IF OBJECT_ID('dnt_addparentforumtopics','P') IS NOT NULL
  2554. DROP PROC [dnt_addparentforumtopics]
  2555. GO
  2556. CREATE PROCEDURE [dnt_addparentforumtopics]
  2557. @topics int,
  2558. @fpidlist nvarchar(100)
  2559. AS
  2560.    UPDATE [dnt_forums] SET [topics] = [topics] + @topics WHERE [fid] IN (SELECT [item]FROM [dnt_split](@fpidlist, ',') )
  2561. GO
  2562. IF OBJECT_ID('dnt_updateusercredits','P') IS NOT NULL
  2563. DROP PROC [dnt_updateusercredits]
  2564. GO
  2565. CREATE PROCEDURE [dnt_updateusercredits]
  2566. @uid INT
  2567. AS
  2568. UPDATE [dnt_users] SET [credits] = extcredits1 + posts + digestposts* 5 WHERE [uid] = @uid
  2569. GO
  2570. IF OBJECT_ID('[dnt_gethottopicscount]','P')  IS NOT NULL
  2571. DROP PROC [dnt_gethottopicscount]
  2572. GO
  2573. CREATE PROCEDURE [dnt_gethottopicscount]
  2574. @fid int,
  2575. @timebetween int
  2576. AS
  2577. DECLARE @strSQL varchar(4000)
  2578. DECLARE @strSQLByFid nvarchar(200)
  2579. DECLARE @strSQLByDate nvarchar(200)
  2580. DECLARE @pagetop int
  2581. IF @fid<>0
  2582. SET @strSQLByFid=' AND [fid]='+STR(@fid)+'' 
  2583. IF @timebetween<>0
  2584. SET @strSQLByDate=' AND DATEDIFF(DAY,[postdatetime],GETDATE())<=' + STR(@timebetween)
  2585. SET @strSQL = 'SELECT COUNT(1) FROM [dnt_topics] WHERE 1=1 '+@strSQLByFid+@strSQLByDate
  2586. EXEC(@strSQL)
  2587. GO
  2588. IF OBJECT_ID('[dnt_gethottopicslist]','P')  IS NOT NULL
  2589. DROP PROC [dnt_gethottopicslist]
  2590. GO
  2591. CREATE PROCEDURE [dnt_gethottopicslist]
  2592. @pagesize int,
  2593. @pageindex int, 
  2594. @fid int,
  2595. @showtype varchar(100) , 
  2596. @timebetween int
  2597. AS
  2598. DECLARE @strSQL varchar(4000)
  2599. DECLARE @strSQLByFid nvarchar(200)=''
  2600. DECLARE @strSQLByDate nvarchar(200)=''
  2601. DECLARE @pagetop int
  2602. SET @pagetop = (@pageindex-1)*@pagesize
  2603. IF @fid<>0
  2604. BEGIN
  2605. SET @strSQLByFid='AND t.[fid]='+STR(@fid)
  2606. END
  2607. IF @timebetween<>0
  2608. SET @strSQLByDate=' AND DATEDIFF(DAY,[postdatetime],GETDATE())<=' + STR(@timebetween)
  2609. IF @pageindex=1
  2610. BEGIN
  2611. SET @strSQL = 'SELECT TOP ' + STR(@pagesize) +'  [t].[tid],[t].[fid],[t].[iconid],[t].[typeid],
  2612. [t].[readperm],[t].[price],[t].[poster],[t].[posterid],[t].[title],
  2613. [t].[attention],[t].[postdatetime],[t].[lastpost],[t].[lastpostid],[t].[lastposter],[t].[lastposterid],
  2614. [t].[views],[t].[replies],[t].[displayorder],[t].[highlight],[t].[digest],[t].[rate],[t].[hide],
  2615. [t].[attachment],[t].[moderated],[t].[closed],[t].[magic],[t].[identify],[t].[special],
  2616. f.[name] FROM [dnt_topics] t LEFT JOIN [dnt_forums] f ON t.[fid] = f.[fid] 
  2617. WHERE 1=1 '+@strSQLByFid+@strSQLByDate+' ORDER BY ['+@showtype+'] DESC'
  2618. END
  2619. ELSE
  2620. BEGIN
  2621. SET @strSQL = 'SELECT TOP ' + STR(@pagesize) +' [t].[tid],[t].[fid],[t].[iconid],[t].[typeid],[t].[readperm],[t].[price],
  2622. [t].[poster],[t].[posterid],[t].[title],[t].[attention],[t].[postdatetime],[t].[lastpost],[t].[lastpostid],
  2623. [t].[lastposter],[t].[lastposterid],[t].[views],[t].[replies],[t].[displayorder],[t].[highlight],[t].[digest],
  2624. [t].[rate],[t].[hide],[t].[attachment],[t].[moderated],[t].[closed],[t].[magic],[t].[identify],[t].[special],
  2625. f.[name] FROM [dnt_topics] t LEFT JOIN [dnt_forums] f ON t.[fid] = f.[fid] 
  2626. WHERE 1=1 ' +@strSQLByFid +@strSQLByDate+' AND [tid] NOT IN (SELECT TOP '+ STR(@pagetop)+' [tid] FROM [dnt_topics] 
  2627. WHERE 1=1 ' +@strSQLByFid +@strSQLByDate+' ORDER BY ['+@showtype+'] DESC) ORDER BY ['+@showtype+'] DESC'
  2628. END
  2629. EXEC(@strSQL)
  2630. GO
  2631. IF OBJECT_ID('dnt_resettodayposts','P') IS NOT NULL
  2632. DROP PROC [dnt_resettodayposts]
  2633. GO
  2634. CREATE PROC [dnt_resettodayposts]
  2635. AS
  2636. UPDATE dnt_forums SET todayposts = 0;
  2637. DECLARE @tableid int;
  2638. SELECT @tableid = MAX(id) FROM dnt_tablelist;
  2639. EXEC('
  2640. UPDATE dnt_forums 
  2641. SET todayposts=rightTable.pidcount 
  2642. FROM dnt_forums 
  2643. INNER JOIN (SELECT fid,COUNT(pid) pidcount FROM dnt_posts'+ @tableid +' WHERE DATEDIFF(DAY,postdatetime,GETDATE())=0 GROUP BY fid ) AS rightTable
  2644. ON dnt_forums.fid=rightTable.fid
  2645. WHERE dnt_forums.fid=rightTable.fid
  2646. ')