CREATE PROCEDURE [usp_updateCounts]
AS
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;

/* Update Forum Topic Counts */

CREATE TABLE #F_T_COUNT (
    FORUM_ID int,
    T_COUNT int
);

UPDATE FORUM_FORUM SET F_TOPICS = 0;

INSERT INTO #F_T_COUNT
  SELECT FORUM_ID, COUNT(FORUM_ID) FROM FORUM_TOPICS WHERE T_STATUS<=1 GROUP By FORUM_ID ;

UPDATE FORUM_FORUM SET F_TOPICS = T_COUNT FROM FORUM_FORUM F INNER JOIN #F_T_COUNT T ON T.FORUM_ID=F.FORUM_ID;
  

/* Update Forum Archived Topics Count  */

DELETE FROM #F_T_COUNT;

UPDATE FORUM_FORUM SET F_A_TOPICS = 0;

INSERT INTO #F_T_COUNT
  SELECT FORUM_ID, COUNT(FORUM_ID) FROM FORUM_A_TOPICS WHERE T_STATUS<=1 GROUP By FORUM_ID ;

UPDATE FORUM_FORUM SET F_A_TOPICS = T_COUNT FROM FORUM_FORUM F INNER JOIN #F_T_COUNT T ON T.FORUM_ID=F.FORUM_ID;

DROP TABLE #F_T_COUNT;


/* Update Topic Replies Counts now */

CREATE TABLE #T_R_COUNT (
   TOPIC_ID int,
   R_COUNT int
);

INSERT INTO #T_R_COUNT 
  SELECT TOPIC_ID, COUNT(REPLY_ID) FROM FORUM_REPLY WHERE R_STATUS<=1 GROUP BY TOPIC_ID ;

UPDATE FORUM_TOPICS SET T_REPLIES = 0 WHERE T_STATUS<=1;

UPDATE FORUM_TOPICS SET T_REPLIES = R_COUNT  FROM FORUM_TOPICS T INNER JOIN #T_R_COUNT TR ON T.TOPIC_ID = TR.TOPIC_ID
   WHERE T.T_STATUS<=1;

/* Update Archived Topics Replies Count Now */

DELETE FROM #T_R_COUNT;

INSERT INTO #T_R_COUNT 
  SELECT TOPIC_ID, COUNT(REPLY_ID) FROM FORUM_A_REPLY GROUP BY TOPIC_ID;

UPDATE FORUM_A_TOPICS SET T_REPLIES = 0;

UPDATE FORUM_A_TOPICS SET T_REPLIES = R_COUNT FROM FORUM_A_TOPICS T INNER JOIN #T_R_COUNT TR ON T.TOPIC_ID = TR.TOPIC_ID;

DROP TABLE #T_R_COUNT;

/* Update Last post Date */ 

CREATE TABLE #T_POST_DATA (
   TOPIC_ID int,
   LAST_POST nvarchar(14)
);

INSERT INTO #T_POST_DATA
  SELECT TOPIC_ID, MAX(R_DATE) FROM FORUM_REPLY WHERE R_STATUS<=1 GROUP BY TOPIC_ID;

UPDATE FORUM_TOPICS SET T_LAST_POST = LAST_POST FROM FORUM_TOPICS INNER JOIN #T_POST_DATA PT  ON FORUM_TOPICS.TOPIC_ID = PT.TOPIC_ID;

DELETE FROM #T_POST_DATA;

UPDATE FORUM_TOPICS SET T_LAST_POST=T_DATE, T_LAST_POST_AUTHOR=T_AUTHOR WHERE T_REPLIES=0;

/* Update Last post Date */ 

INSERT INTO #T_POST_DATA
  SELECT TOPIC_ID, MAX(R_DATE) FROM FORUM_A_REPLY GROUP BY TOPIC_ID;

UPDATE FORUM_A_TOPICS SET T_LAST_POST = LAST_POST FROM FORUM_A_TOPICS T INNER JOIN #T_POST_DATA TP  ON T.TOPIC_ID = TP.TOPIC_ID;

UPDATE FORUM_A_TOPICS SET T_LAST_POST=T_DATE WHERE T_REPLIES=0;

DROP TABLE #T_POST_DATA;
/* Now find the reply ID for the posts that have more than 0 replies */

CREATE TABLE #T_L_REPLY_ID (
   TOPIC_ID int,
   REPLY_ID int
);

CREATE TABLE #T_L_A_REPLY_ID (
   TOPIC_ID int,
   REPLY_ID int
);

INSERT INTO #T_L_REPLY_ID
  SELECT T.TOPIC_ID, MAX(REPLY_ID) FROM FORUM_REPLY R INNER JOIN FORUM_TOPICS T ON R.TOPIC_ID=T.TOPIC_ID
       WHERE T.T_LAST_POST=R_DATE AND T_STATUS<=1 GROUP BY T.TOPIC_ID;

UPDATE FORUM_TOPICS SET T_LAST_POST_REPLY_ID = REPLY_ID FROM FORUM_TOPICS T INNER JOIN #T_L_REPLY_ID TL ON TL.TOPIC_ID = T.TOPIC_ID;

DELETE FROM #T_L_REPLY_ID;

/* Now find the reply ID for the posts that have more than 0 replies in archived topics */

INSERT INTO #T_L_A_REPLY_ID
  SELECT T.TOPIC_ID, MAX(REPLY_ID) FROM FORUM_A_REPLY R INNER JOIN FORUM_A_TOPICS T ON R.TOPIC_ID=T.TOPIC_ID
       WHERE T.T_LAST_POST=R_DATE GROUP BY T.TOPIC_ID;

UPDATE FORUM_A_TOPICS SET T_LAST_POST_REPLY_ID = REPLY_ID FROM FORUM_A_TOPICS T INNER JOIN #T_L_REPLY_ID TL ON TL.TOPIC_ID = T.TOPIC_ID;


/* Now found the author ID for the last reply */

CREATE TABLE #T_L_REPLY_AUTHOR(
   TOPIC_ID int,
   AUTHOR int
);


INSERT INTO #T_L_REPLY_AUTHOR
  SELECT T.TOPIC_ID, R.R_AUTHOR FROM FORUM_TOPICS T INNER JOIN FORUM_REPLY R ON T.TOPIC_ID=R.TOPIC_ID
       INNER JOIN #T_L_REPLY_ID ON T.TOPIC_ID= #T_L_REPLY_ID.TOPIC_ID 
    WHERE #T_L_REPLY_ID.REPLY_ID = R.REPLY_ID AND T_STATUS<=1;

UPDATE FORUM_TOPICS SET T_LAST_POST_AUTHOR = AUTHOR FROM FORUM_TOPICS T INNER JOIN #T_L_REPLY_AUTHOR TL ON TL.TOPIC_ID = T.TOPIC_ID;

DELETE FROM #T_L_REPLY_AUTHOR;

INSERT INTO #T_L_REPLY_AUTHOR
  SELECT T.TOPIC_ID, R.R_AUTHOR FROM FORUM_A_TOPICS T INNER JOIN FORUM_A_REPLY R ON T.TOPIC_ID=R.TOPIC_ID
     INNER JOIN #T_L_A_REPLY_ID ON T.TOPIC_ID = #T_L_A_REPLY_ID.TOPIC_ID
    WHERE #T_L_A_REPLY_ID.REPLY_ID = R.REPLY_ID;

UPDATE FORUM_A_TOPICS SET T_LAST_POST_AUTHOR = AUTHOR FROM FORUM_A_TOPICS T INNER JOIN #T_L_REPLY_AUTHOR TL ON TL.TOPIC_ID = T.TOPIC_ID;

DROP TABLE #T_L_REPLY_AUTHOR;

DROP TABLE #T_L_REPLY_ID;

DROP TABLE #T_L_A_REPLY_ID;


/* Update Topic Replies Counts now */

CREATE TABLE #T_R_COUNT1 (
   TOPIC_ID int,
   R_COUNT int
);

INSERT INTO #T_R_COUNT1 
  SELECT TOPIC_ID, COUNT(REPLY_ID) FROM FORUM_REPLY WHERE R_STATUS=2 OR R_STATUS=3 GROUP BY TOPIC_ID ;

UPDATE FORUM_TOPICS SET T_UREPLIES = 0 WHERE T_STATUS<=1;

UPDATE FORUM_TOPICS SET T_UREPLIES = R_COUNT FROM FORUM_TOPICS T INNER JOIN #T_R_COUNT1 TR ON T.TOPIC_ID = TR.TOPIC_ID
   WHERE T.T_STATUS<=1;

DROP TABLE #T_R_COUNT1;

/* Now to step 4 */

/* Count replies per forum */

CREATE TABLE #F_R_COUNT (
    FORUM_ID int,
    R_COUNT int
);

INSERT INTO #F_R_COUNT 
  SELECT R.FORUM_ID, COUNT(REPLY_ID) FROM FORUM_TOPICS T INNER JOIN FORUM_REPLY R On T.TOPIC_ID=R.TOPIC_ID
     WHERE T.T_STATUS<=1 AND R_STATUS<=1 GROUP By R.FORUM_ID;

UPDATE FORUM_FORUM SET F_COUNT=F_TOPICS WHERE F_TYPE<>1;

UPDATE FORUM_FORUM SET F_COUNT = F_COUNT + R_COUNT FROM FORUM_FORUM F INNER JOIN #F_R_COUNT FR ON F.FORUM_ID = FR.FORUM_ID;

DELETE FROM #F_R_COUNT;

INSERT INTO #F_R_COUNT 
  SELECT R.FORUM_ID, COUNT(REPLY_ID) FROM FORUM_A_TOPICS T INNER JOIN FORUM_A_REPLY R On T.TOPIC_ID=R.TOPIC_ID GROUP By R.FORUM_ID;

UPDATE FORUM_FORUM SET F_A_COUNT=F_A_TOPICS WHERE F_TYPE<>1;

UPDATE FORUM_FORUM SET F_A_COUNT = F_A_COUNT + R_COUNT FROM FORUM_FORUM F INNER JOIN #F_R_COUNT FR ON F.FORUM_ID = FR.FORUM_ID
 
DROP TABLE  #F_R_COUNT;

/* Update Last Post Per Forum */

CREATE TABLE #F_POST_DATA (
   FORUM_ID int,
   LAST_POST varchar(50)
);


INSERT INTO #F_POST_DATA
  SELECT FORUM_ID, MAX(T_LAST_POST) FROM FORUM_TOPICS WHERE T_STATUS<=1 GROUP BY FORUM_ID;

UPDATE FORUM_FORUM SET F_LAST_POST = LAST_POST FROM FORUM_FORUM F INNER JOIN #F_POST_DATA FP  ON F.FORUM_ID = FP.FORUM_ID;

DROP TABLE #F_POST_DATA;


/* Update Last Post TOPIC_ID */


CREATE TABLE #F_TOPIC_ID (
   FORUM_ID int,
   TOPIC_ID int
);

INSERT INTO #F_TOPIC_ID
  SELECT F.FORUM_ID, MAX(T.TOPIC_ID) FROM FORUM_FORUM F INNER JOIN FORUM_TOPICS T On F.FORUM_ID=T.FORUM_ID
   WHERE F.F_LAST_POST = T.T_LAST_POST and T.T_STATUS<=1 GROUP BY F.FORUM_ID;

UPDATE FORUM_FORUM SET F_LAST_POST_TOPIC_ID = TOPIC_ID FROM FORUM_FORUM F INNER JOIN #F_TOPIC_ID FT ON F.FORUM_ID = FT.FORUM_ID;
   
/* Now Update for Author ID */

UPDATE FORUM_FORUM SET F_LAST_POST_AUTHOR=T_LAST_POST_AUTHOR 
FROm ((FORUM_FORUM F INNER JOIN #F_TOPIC_ID ON F.FORUM_ID=#F_TOPIC_ID.FORUM_ID) INNER JOIN FORUM_TOPICS T On #F_TOPIC_ID.TOPIC_ID=T.TOPIC_ID)

DROP TABLE #F_TOPIC_ID;

CREATE  TABLE #T_TOPICS (
   COUNT_ID int,
   TOPICS int,
   A_TOPICS int,
   POSTS int,
   A_POSTS int
);

INSERT INTO #T_TOPICS
  SELECT 1, SUM(F_TOPICS), SUM(F_A_TOPICS),SUM(F_COUNT),SUM(F_A_COUNT) FROM FORUM_FORUM WHERE F_TYPE<>1;

UPDATE FORUM_TOTALS SET T_COUNT = TOPICS, T_A_COUNT = A_TOPICS, P_COUNT=POSTS, P_A_COUNT=A_POSTS
  FROM FORUM_TOTALS FT INNER JOIN #T_TOPICS TT ON FT.COUNT_ID=TT.COUNT_ID;

DROP TABLE #T_TOPICS;

CREATE TABLE #T_MEMBERS (
   COUNT_ID int,
   MEMBERS int
);

INSERT INTO #T_MEMBERS
  SELECT 1, COUNT(MEMBER_ID) FROM FORUM_MEMBERS;

UPDATE FORUM_TOTALS SET U_COUNT = MEMBERS FROM FORUM_TOTALS FT INNER JOIN #T_MEMBERS TM ON FT.COUNT_ID=TM.COUNT_ID;

DROP TABLE #T_MEMBERS;

End

Sub subArchiveStuff(fdateolderthan)
 set Server2 = Server
 Server2.ScriptTimeout = 10000
 Dim fIDSQL
 Dim drs,delRep
 
 Set cmd = CreateObject("ADODB.Command")
 Set cmd.ActiveConnection = my_conn
 '#### create FORUM_ID clause
 rqID = request("id")
     on error resume next
 testID = cLng(rqID)
 if err.number = 0 then
  if rqID <> "-1" then 
   fIDSQL = " AND " & strTablePrefix & "TOPICS.FORUM_ID=" & rqID
  else
   fIDSQL = ""
  end if
  err.clear
 else
  fIDSQL = " AND " & strTablePrefix & "TOPICS.FORUM_ID IN (" & rqID & ")"
  err.clear
 end if
 'on error goto 0
 '#### Get the replies to Archive

    on error resume next

 strSql = "BEGIN TRAN"
 cmd.CommandText = strSql
 cmd.Execute

 If Err.Number <> 0 then

    Response.Write "<br> Error while initiating transactions. Archive failed.<br>"
    Response.End

 End If

 'Now archive replies

 strSQL = "INSERT INTO " & strArchiveTablePrefix & "REPLY (CAT_ID,FORUM_ID,TOPIC_ID,REPLY_ID,R_STATUS,R_MAIL,R_AUTHOR,R_MESSAGE,R_DATE," & _
          "R_IP,R_LAST_EDIT,R_LAST_EDITBY,R_SIG) "
 strSQL = strSql & "SELECT R.CAT_ID, R.FORUM_ID, R.TOPIC_ID,R.REPLY_ID,R.R_STATUS,R.R_MAIL,R.R_AUTHOR,R.R_MESSAGE," &_
          "R.R_DATE,R.R_IP,R.R_LAST_EDIT,R.R_LAST_EDITBY,R.R_SIG FROM " & strTablePrefix & "REPLY  R INNER JOIN " & strTablePrefix & _
     "TOPICS ON R.TOPIC_ID=" & strTablePrefix & "TOPICS.TOPIC_ID"
 strSQl = strSql & " WHERE T_LAST_POST <= '" & fdateolderthan & "'" & fIDSQL
 strSQL = strSQL & " AND T_ARCHIVE_FLAG <> 0"

    cmd.CommandText = strSql
 cmd.Execute lngRecs,, 1 + 128

 'Response.Write strsQL & "<br />"

    If Err.Number <> 0 then

       cmd.CommandText = "ROLLBACK TRAN"
    cmd.Execute
    Response.Write "<br> Error while inserting replies into archive table. Archive failed.<br>"
    Response.Write"<br> The actual error message was: <br>"
    Response.Write Err.Description 
    Response.End
 Else
    Response.Write "<br>" & lngRecs & " replies moved into the archive tables...<br>"
 End If

 'Delete these replies from the live table...

 strSql = "DELETE FROM " & strTablePrefix & "REPLY FROM " & strTablePrefix & "REPLY INNER JOIN " & strArchiveTablePrefix & "REPLY ON "
 strSql = strSql & strTablePrefix & "REPLY.REPLY_ID=" & strArchiveTablePrefix & "REPLY.REPLY_ID"

 cmd.CommandText = strSql
 cmd.Execute lngRecs,, 1 + 128


   'Response.Write strsQL & "<br />"

    If Err.Number <> 0 then

       cmd.CommandText = "ROLLBACK TRAN"
    cmd.Execute
    Response.Write "<br> Error while deleting replies from the live table. Archive failed.<br>"
    Response.Write"<br> The actual error message was: <br>"
    Response.Write Err.Description 
    Response.End
 Else
    Response.Write "<br>" & lngRecs & " replies deleted from the live tables...<br>"
 End If

   
   'ok, this can be done...

 '#### Update FORUM archive date
 strsql = "UPDATE " & strTablePrefix & "FORUM SET F_L_ARCHIVE= '" & fdateolderthan & "'"
 on error resume next
 testID = cLng(rqID)
 if err.number = 0 then
  if rqID <> "-1" then 
   strSQL = strSql & " WHERE FORUM_ID=" & rqID
  end if
  err.clear
 else
  strSQL = strSql & " WHERE FORUM_ID IN (" & rqID & ")"
  err.clear
 end if
 on error goto 0

 my_conn.execute(strsql),,adCmdText + adExecuteNoRecords

 '#### Archive the topics....

 strsql = "INSERT INTO " & strArchiveTablePrefix & "TOPICS SELECT * FROM " & strTablePrefix & "TOPICS "
    strsql = strSql & "WHERE T_LAST_POST < '" & fdateolderthan & "'" & fIDSQL
 strSQL = strSQL & " AND T_ARCHIVE_FLAG <> 0 "

   cmd.CommandText = strSql
   cmd.Execute lngRecs,, 1 + 128


   'Response.Write strsQL & "<br />"

    If Err.Number <> 0 then

       cmd.CommandText = "ROLLBACK TRAN"
    cmd.Execute
    Response.Write "<br> Error while moving topics into the archive table. Archive failed.<br>"
    Response.Write"<br> The actual error message was: <br>"
    Response.Write Err.Description
    Response.End
 Else
    Response.Write "<br>" & lngRecs & " topics moved into the archive tables...<br>"
 End If


    'Now delete the topics

 strsql = "DELETE FROM " & strTablePrefix & "TOPICS WHERE T_LAST_POST < '" & fdateolderthan & "'" & fIDSQL
 strSQL = strSQL & " AND T_ARCHIVE_FLAG <> 0 "

 cmd.CommandText = strSql
 cmd.Execute lngRecs,, 1 + 128


    'Response.Write strsQL & "<br />"

    If Err.Number <> 0 then

       cmd.CommandText = "ROLLBACK TRAN"
    cmd.Execute
    Response.Write "<br> Error while deleting topics from the live table. Archive failed.<br>"
    Response.Write"<br> The actual error message was: <br>"
    Response.Write Err.Description 
    Response.End
 Else
    Response.Write "<br>" & lngRecs & " topics deleted from the live tables...<br>"
 End If

 cmd.CommandText = "COMMIT TRAN"
 cmd.Execute


 'Now update counts

 strSQL = "EXEC usp_updateCounts"
   
 cmd.CommandText = strsQl
 cmd.Execute

 Response.Write "<br>Forum Counts Updated<br>"

 'response.write("                      <br /><center><a href=""admin_forums.asp"">Click Here</a> to return to Forums Delete/Archive Admin</center><br />" & vbNewLine)

 on error goto 0
End Sub