' **********************************************************************************************
' Author: Bruno Alexandre <bruno [dot] in [dot] dk [at] gmail [dot] com>
' Date: April 2007
' MOD: Get last 10 messages in the Forum
' Function: 
'   Displays vertically and horizontally the Top messages in the Forum
'   it turns user own message in a different color
' Versions: 
'   1.0	            Original version
'   1.1  26.11.07   Added more string to the translate part 
'   		    Added Rows and Columns in order to display
'   1.2  01.03.08   Added block preventing private messages to show
'   1.3  02.03.08   Updated with TopSQL() so it runs in mySQL as well (>4.x)
'
' **********************************************************************************************

<!-- #include file="inc_topMessages.asp" -->

TABLE_TYPE_ROWS = 10			' Number of rows ( Can't be <= 0 ) >> please read note
TABLE_TYPE_COLS = 1 			' Number of columns ( Can't be <= 0 ) >> please read note
TABLE_TYPE_TOP  = TABLE_TYPE_ROWS * TABLE_TYPE_COLS

' NOTE: in order to mantain a reading layout, the SELECT TOP function is determinated by
'       multipling Rows by Columns.
'       SELECT TOP ( Rows x Columns ) ... 

MAX_MESSAGE_LEN = 70			' Display the first 70 characters
MAX_MESSAGE_BREAK_LEN = 40		' If in thoose 70 charcarters there is no white space, add one
OWN_MESSAGE_COLOR = "#E6EFF7"	' Background color of message if it belongs to the user logged in

on error resume next
'on error goto 0

'on error resume next
on error goto 0

<!--#include file="inc_func_common.asp"-->

<!--#INCLUDE FILE="config.asp"-->

<!--#include file="inc_func_common.asp"-->
<%
	set my_Conn = Server.CreateObject("ADODB.Connection")
	my_Conn.Errors.Clear
	Err.Clear
	my_Conn.Open strConnString
	

	TABLE_TYPE_ROWS = 2			' Number of rows ( Can't be <= 0 ) >> please read note
	TABLE_TYPE_COLS = 5			' Number of columns ( Can't be <= 0 ) >> please read note

	if len(request.QueryString("rows")) > 0 then
		if isNumeric(request.QueryString("rows")) then
			TABLE_TYPE_ROWS = cInt(request.QueryString("rows"))
		end if
	end if

	if len(request.QueryString("cols")) > 0 then
		if isNumeric(request.QueryString("cols")) then
			TABLE_TYPE_COLS = cInt(request.QueryString("cols"))
		end if
	end if
%>
<!--#include file="inc_topmessages2.asp"-->
<%
	my_Conn.close()
	set my_Conn = nothing
%>

SELECT 
a.CAT_ID, a.FORUM_ID, a.TOPIC_ID, a.T_SUBJECT, a.MSG_ID, a.MSG, a.MSG_DATE, a.MEMBER_ID, a.M_NAME 

FROM ( 
  SELECT t.CAT_ID, t.FORUM_ID, t.TOPIC_ID, t.T_SUBJECT, '' AS MSG_ID, t.T_MESSAGE AS MSG, t.T_DATE AS MSG_DATE, m.MEMBER_ID, m.M_NAME 
  FROM FORUM_MEMBERS m INNER JOIN FORUM_TOPICS t ON m.MEMBER_ID = t.T_AUTHOR 

  UNION ALL 

  SELECT t.CAT_ID, t.FORUM_ID, t.TOPIC_ID, t.T_SUBJECT, r.REPLY_ID AS MSG_ID, r.R_MESSAGE as MSG, r.R_DATE as MSG_DATE, m.MEMBER_ID, m.M_NAME 
  FROM (FORUM_TOPICS t INNER JOIN FORUM_REPLY r ON t.TOPIC_ID = r.TOPIC_ID) INNER JOIN FORUM_MEMBERS m ON r.R_AUTHOR = m.MEMBER_ID 
) a 

INNER JOIN FORUM_FORUM f ON f.FORUM_ID = a.FORUM_ID 

WHERE f.F_PRIVATEFORUMS = 0 
ORDER BY MSG_DATE DESC 
LIMIT 20

response.Write("<hr/>" & TopSQL(sqlstr, cStr(TABLE_TYPE_TOP)) & "<hr/>")
response.flush()

sqlstr = "SELECT " & _
		"a.CAT_ID, a.FORUM_ID, a.TOPIC_ID, a.T_SUBJECT, a.MSG_ID, a.MSG, a.MSG_DATE, a.MEMBER_ID, a.M_NAME " & _
		"FROM ( " & _
		"SELECT t.CAT_ID, t.FORUM_ID, t.TOPIC_ID, t.T_SUBJECT, '' AS MSG_ID, t.T_MESSAGE AS MSG, t.T_DATE AS MSG_DATE, m.MEMBER_ID, m.M_NAME " & _
		"FROM FORUM_MEMBERS m INNER JOIN FORUM_TOPICS t ON m.MEMBER_ID = t.T_AUTHOR " & _
		"UNION ALL " & _
		"SELECT t.CAT_ID, t.FORUM_ID, t.TOPIC_ID, t.T_SUBJECT, r.REPLY_ID AS MSG_ID, r.R_MESSAGE as MSG, r.R_DATE as MSG_DATE, m.MEMBER_ID, m.M_NAME " & _
		"FROM (FORUM_TOPICS t INNER JOIN FORUM_REPLY r ON t.TOPIC_ID = r.TOPIC_ID) INNER JOIN FORUM_MEMBERS m ON r.R_AUTHOR = m.MEMBER_ID " & _
		") a " & _
		"INNER JOIN FORUM_FORUM f ON f.FORUM_ID = a.FORUM_ID " & _
		"WHERE f.F_PRIVATEFORUMS = 0 " & _
		"ORDER BY MSG_DATE DESC;" 

sqlstr = "SELECT " & _
		"a.CAT_ID, a.FORUM_ID, a.TOPIC_ID, a.T_SUBJECT, a.MSG_ID, a.MSG, a.MSG_DATE, a.MEMBER_ID, a.M_NAME " & _
		"FROM ( " & _
		"SELECT t.CAT_ID, t.FORUM_ID, t.TOPIC_ID, t.T_SUBJECT, '' AS MSG_ID, t.T_MESSAGE AS MSG, t.T_DATE AS MSG_DATE, m.MEMBER_ID, m.M_NAME " & _
		"FROM FORUM_MEMBERS m INNER JOIN FORUM_TOPICS t ON m.MEMBER_ID = t.T_AUTHOR " & _
		") a " & _
		"INNER JOIN FORUM_FORUM f ON f.FORUM_ID = a.FORUM_ID " & _
		"WHERE f.F_PRIVATEFORUMS = 0 " & _
		"ORDER BY MSG_DATE DESC;"