| Author |
 Topic  |
|
|
jfitz
Junior Member
USA
345 Posts |
 Posted - 28 June 2003 : 01:06:07
|
MEMBER AWARDS MOD
Latest Change: 28 Jun 2003
DESCRIPTION
This mod allows you to create and attach awards to members. For
example, in our exercise forums, we have a monthly fitness challenge,
and awards are shown as in the example below:
As written and shown in the example, an award consists of three
parts - an award name, an award level, and an award color. The award
name is always written in the default forum font color,
strForumFontColor; The award level is written in the color specified
in its data base entry.
This mod works by adding an outer left join to the SQL statements
that select information about the topic and member to pick up award
information.
This mod requires making changes in 7 places to one file: TOPIC.ASP, adding one
new field to the MEMBERS table, and creating and populating a new
database table.
NOTES
1. I did not create an administrative interface for this mod. Data
base changes and maintenance of the award information must be done
directly to the data base.
2. I have multiple mods to my Snitz code, so your line numbers may
not correspond exactly to the ones shown, and your code may vary
slightly as well.
3. Because the changes shown are not shown as changes to Snitz
baseline code, but to my previously altered code, I strongly
recommend that you do not just wholesale copy and paste from
my code, but only make the changes indicated.
4. This code is for MSSQL data bases only. It has not been
tried with any other data bases. It uses a non-ANSI standard syntax
for the required left outer join (*=) which may not be supported by
other data bases.
DIFFICULTY
Easy Difficult
1 2 3 4 5 6 7 8 9 10
^
^
IMPLEMENTATION
TOPIC.ASP - 7 Changes
1. Around line 130:
Modify the SELECT SQL by adding the lines shown in red. Be sure that
the previous lines include the line continuation marker % _
at the end. Note that the construct *= is not a
mistake. This is MSSQL alternate syntax for a left outer join,
without which the SQL will not behave properly. Note the comma
added after MEMBERS M.
'## MY LOCATION MOD added M.M_CITY, M.M_STATE to SQL
'## MY AVATAR MOD ## Added sixth line, below
'## MY AWARDS MOD Added A.AWARDNAME, A.AWARDLEVEL, A.AWARDCOLOR just above M.M_SIG
'############################### Poll Mod ##################################
' Added T.T_ISPOLL, T.T_POLLSTATUS and F.F_POLLS in SQL Statment below.
strSql = "SELECT M.M_NAME, M.M_RECEIVE_EMAIL, M.M_AIM, M.M_ICQ, M.M_MSN, M.M_YAHOO" & _
", M.M_TITLE, M.M_HOMEPAGE, M.MEMBER_ID, M.M_LEVEL, M.M_POSTS, M.M_CITY, M.M_STATE, M.M_COUNTRY" & _
", M.A_HASPHOTO, M.A_USEPHOTO, M.A_PHOTOLOC" & _
", T.T_DATE, T.T_SUBJECT, T.T_AUTHOR, T.TOPIC_ID, T.T_STATUS, T.T_ISPOLL, T.T_POLLSTATUS, T.T_LAST_EDIT" & _
", T.T_LAST_EDITBY, T.T_LAST_POST, T.T_SIG, T.T_REPLIES" & _
", C.CAT_STATUS, C.CAT_ID, C.CAT_NAME, C.CAT_SUBSCRIPTION, C.CAT_MODERATION" & _
", F.F_STATUS, F.F_POLLS, F.FORUM_ID, F.F_SUBSCRIPTION, F.F_SUBJECT, F.F_MODERATION, T.T_MESSAGE" & _
", A.AWARDNAME, A.AWARDLEVEL, A.AWARDCOLOR"
'###########################################################################
if CanShowSignature = 1 then
strSql = strSql & ", M.M_SIG"
end if
strSql = strSql & " FROM " & strActivePrefix & "TOPICS T, " & strTablePrefix & "FORUM F, " & _
strTablePrefix & "CATEGORY C, " & strMemberTablePrefix & "MEMBERS M, " & _
strTablePrefix & "CHALLENGE_AWARDS A " & _
" WHERE T.TOPIC_ID = " & Topic_ID & _
" AND F.FORUM_ID = T.FORUM_ID " & _
" AND C.CAT_ID = T.CAT_ID " & _
" AND M.MEMBER_ID = T.T_AUTHOR " & _
" AND M.A_AWARDS *= A.AWARDNUMBER "
2. Around line 200:
Add the lines shown in red.
Forum_ID = rsTopic("FORUM_ID")
Forum_Subject = rsTopic("F_SUBJECT")
Forum_Subscription = rsTopic("F_SUBSCRIPTION")
Forum_Moderation = rsTopic("F_MODERATION")
Topic_Message = rsTopic("T_MESSAGE")
'## MY AWARDS MOD Added next 3 lines
Award_Name = rsTopic("AwardName")
Award_Level = rsTopic("AwardLevel")
Award_Color = rsTopic("AwardColor")
if CanShowSignature = 1 then
Topic_MemberSig = trim(rsTopic("M_SIG"))
end if
3. Around line 425:
Add the code show in red. Note that the construct *= is
not a mistake. This is MSSQL alternate syntax for a left outer join,
without which the SQL will not behave properly. Note the added
comma after MEMBERS M in the first line defining strSql2.
'## Forum_SQL
'## MY LOCATION MOD added M.M_CITY, M.M_STATE to SQL
'## MY AWARDS MOD Added A.AWARDNAME, A.AWARDLEVEL, A.AWARDCOLOR just above M.M_SIG
'## MY AVATAR MOD ## added third line below
strSql = "SELECT M.M_NAME, M.M_RECEIVE_EMAIL, M.M_AIM, M.M_ICQ, M.M_MSN, M.M_YAHOO"
strSql = strSql & ", M.M_TITLE, M.MEMBER_ID, M.M_HOMEPAGE, M.M_LEVEL, M.M_POSTS, M.M_CITY, M.M_STATE, M.M_COUNTRY"
strSql = strSql & ", M.A_HASPHOTO, M.A_USEPHOTO, M.A_PHOTOLOC"
strSql = strSql & ", R.REPLY_ID, R.FORUM_ID, R.R_AUTHOR, R.TOPIC_ID, R.R_MESSAGE, R.R_LAST_EDIT"
strSql = strSql & ", R.R_LAST_EDITBY, R.R_SIG, R.R_STATUS, R.R_DATE"
strSql = strSql & ", A.AWARDNAME, A.AWARDLEVEL, A.AWARDCOLOR"
if CanShowSignature = 1 then
strSql = strSql & ", M.M_SIG"
end if
strSql2 = " FROM " & strMemberTablePrefix & "MEMBERS M, " & strActivePrefix & "REPLY R, "
strSql2 = strSql2 & strMemberTablePrefix & "CHALLENGE_AWARDS A "
strSql3 = " WHERE M.MEMBER_ID = R.R_AUTHOR "
strSql3 = strSql3 & " AND R.TOPIC_ID = " & Topic_ID & " "
'## MY AWARDS MOD Added left outer join
strSql3 = strSql3 & " AND M.A_AWARDS *= A.AWARDNUMBER"
' DEM --> if not a Moderator, all unapproved posts should not be viewed.
if AdminAllowed = 0 then
strSql3 = strSql3 & " AND (R.R_STATUS < "
if Moderation = "Y" then
' Ignore unapproved/rejected posts
strSql3 = strSql3 & "2"
else
' Ignore any previously rejected topic
strSql3 = strSql3 & "3"
end if
strSql3 = strSql3 & " OR R.R_AUTHOR = " & MemberID & ")"
end if
strSql4 = " ORDER BY R.R_DATE ASC"
4. Around line 765:
Add the lines shown in red at the position indicated. Your field
reference numbers may be different. The end result must be that field
reference numbers start with 1, be consecutive, contiguous, and have
no duplicates.
rR_MESSAGE = 21
rR_LAST_EDIT = 22
rR_LAST_EDITBY = 23
rR_SIG = 24
rR_STATUS = 25
rR_DATE = 26
'## MY AWARDS MOD added three lines, renumbered subsequent refs
rA_NAME = 27
rA_LEVEL = 28
rA_COLOR = 29
if CanShowSignature = 1 then
rM_SIG = 30
end if
5. Around line 809:
Add the lines shown in red.
Reply_LastEdit = arrReplyData(rR_LAST_EDIT, iForum)
Reply_LastEditBy = arrReplyData(rR_LAST_EDITBY, iForum)
Reply_Sig = arrReplyData(rR_SIG, iForum)
Reply_Status = arrReplyData(rR_STATUS, iForum)
Reply_Date = arrReplyData(rR_DATE, iForum)
' ## MY AWARDS MOD
Reply_AwardName = arrReplyData(rA_NAME, iForum)
Reply_AwardLevel = arrReplyData(rA_LEVEL, iForum)
Reply_AwardColor = arrReplyData(rA_COLOR, iForum)
if CanShowSignature = 1 then
Reply_MemberSig = trim(arrReplyData(rM_SIG, iForum))
end if
6. Around Line 850:
Add the lines shown in red.
Response.Write " <tr>" & vbNewLine & _
" <td bgcolor=""" & CColor & """ valign=""top"" width=""" & strTopicWidthLeft & """"
if lcase(strTopicNoWrapLeft) = "1" then Response.Write(" nowrap")
Response.Write ">" & vbNewLine & _
" <p><font face=""" & strDefaultFontFace & """ size=""" & strDefaultFontSize & """><b><span class=""spnMessageText"">" & profileLink(ChkString(Reply_MemberName,"display"),Reply_Author) & "</span></b></font><br />" & vbNewLine
if strShowRank = 1 or strShowRank = 3 then
Response.Write " <font color=""" & strForumFontColor & """ face=""" & strDefaultFontFace & """ size=""" & strFooterFontSize & """><small>" & ChkString(getMember_Level(Reply_MemberTitle, Reply_MemberLevel, Reply_MemberPosts),"display") & "</small></font><br />" & vbNewLine
end if
if strShowRank = 2 or strShowRank = 3 then
Response.Write " " & getStar_Level(Reply_MemberLevel, Reply_MemberPosts) & "<br />" & vbNewLine
end if
'## MY AWARDS MOD ## add following lines
if Reply_AwardName <> "" then
if Reply_AwardColor = "" then
theaward_color = strForumFontColor
else
theaward_color = Reply_AwardColor
end if
Response.Write " <br><font color=""" & strForumFontColor & """ face=""" & strDefaultFontFace & """ size=""" & strFooterFontSize & """><small><center>"
Response.Write " " & Reply_AwardName & "</center></small></font>" & vbNewLine
Response.Write " <font color=""" & theaward_color & """ face=""" & strDefaultFontFace & """ size=""" & strFooterFontSize & """><small><center>"
Response.Write " " & Reply_AwardLevel & "</center></small></font><br />" & vbNewLine
end if
7. Around line 1110:
Add the lines shown in red.
if strShowRank = 1 or strShowRank = 3 then
Response.Write " <font color=""" & strForumFontColor & """ face=""" & strDefaultFontFace & """ size=""" & strFooterFontSize & """><small>" & ChkString(getMember_Level(Member_Title, Member_Level, Member_Posts),"display") & "</small></font><br />" & vbNewLine
end if
if strShowRank = 2 or strShowRank = 3 then
Response.Write " " & getStar_Level(Member_Level, Member_Posts) & "<br />" & vbNewLine
end if
Response.Write " </p>" & vbNewLine & _
" <p>" & vbNewLine
'## MY AWARDS MOD ## add next segment
if Award_Name <> "" then
if Award_Color = "" then
theaward_color = strForumFontColor
else
theaward_color = Award_Color
end if
Response.Write " <font color=""" & strForumFontColor & """ face=""" & strDefaultFontFace & """ size=""" & strFooterFontSize & """><small><center>"
Response.Write Award_Name & "</center></small></font>" & vbNewLine
Response.Write " <font color=""" & theaward_color & """ face=""" & strDefaultFontFace & """ size=""" & strFooterFontSize & """><small><center>"
Response.Write Award_Level & "</center></small></font><br />" & vbNewLine
end if
MEMBERS TABLE
Add a new field named A_AWARDS int default 0
CREATE A NEW DATABASE TABLE
CREATE TABLE CHALLENGE_AWARDS
(AWARDNUMBER int
AWARDNANE nvarchar(50),
AWARDLEVEL nvarchar(50),
AWARDCOLOR nvarchar(50))
Notes:
1. Make AWARDNUMBER the primary key. Doing this will enforce the
uniqueness restriction, and is needed to make the join with the
MEMBERS table more efficient.
2. The AWARDCOLOR value is used directly to assign a font color, so
the string should be a color name already recognized by the browser
or defined within Snitz code.
Populate the CHALLENGE_AWARDS table as desired. WARNING - AWARDNUMBER
must be unique in each row. Here is an example of the way I populated
a portion of my table:
AWARDNUMBER AWARDNAME AWARDLEVEL AWARDCOLOR
20030501 May Garden Buddies Challenge Champion Red
20030502 May Garden Buddies Challenge Blue Ribbon Blue
20030503 May Garden Buddies Challenge Honorable Mention Green
Assign matching AWARDNUMBER values to the new A_AWARDS fields in the
MEMBERS table. Setting the field to an unmatched value causes the
award to be null when the SQL select statement is executed.
Update the CHALLENGE_AWARDS and MEMBER tables periodically, as
required.
As implemented, only a single award is shown. To maintain multiple
awards to an individual, an additional table, MEMBER_AWARDS would be
needed to match members with awards, and dates awarded. That feature
is planned for later.
|
--Jördan
It's a wasted day if you don't spend at least part of it flying upside down. |
Edited by - jfitz on 28 June 2003 10:00:32 |
|
|
Etymon
Advanced Member
United States
2385 Posts |
Posted - 28 June 2003 : 02:06:24
|
Thank you for the MOD, jfitz!
When I get the time, I'll try it out. 
Etymon
|
 |
|
|
jeffery
Junior Member
USA
176 Posts |
Posted - 28 June 2003 : 14:18:47
|
| jfitz, I installed the mod and everything went fine. I am trying now to decide exactly how I want to populate the tables i.e. what to name my awards and what they will be given for. Anyway, it appears to work fine given that you pay close attention to the sql statements i.e. the commas ','. I am thinking about doing an admin panel for it. Ill let you know. Thanks for this. |
www.thomasforum.com |
|
|
|
jfitz
Junior Member
USA
345 Posts |
Posted - 28 June 2003 : 15:13:30
|
Jeff,
Glad it works for you. Our members really like having the awards.
Each month we have a fitness self-challenge, and award daily points
for meeting exercise and diet goals. Our members post their daily
results, using the honor system, and the challenge monitor computes
the totals, and emails me the award designations at the end of the
month, and then I update the data base.
One month, we had a Back to BootCamp theme, and the award names were
based on military ranks. Another month was Earn Your Fitness
Diploma, and the award names were based on advanced degrees. In May,
as in the example, we had a Flower Show theme, with awards like
Champion, Blue Ribbon, and so on. This month, we are running a beach
King of the Sand Castle theme, and the award titles will be King,
Duke, Knight, etc. Next month it will be a Beach Volleyball
theme...don't know the award names yet, but we have a month to figure
them out.
As a nice side effect, the daily posting keeps the members involved,
and adds to the site traffic. We've done team challanges before, and
that went over extrememely well, as the forum members teamed up,
reported to each other, and got to know one another better. It's
been good all around for our forums.
Maybe this post will give you some ideas!
An admin panel would be good. It would need to allow Award updates,
as well as member-award selections. Currently, I use SQL Query
Analyzer with a couple of simple procedures I wrote to handle the DB
updates.
Let me know if I can offer any assistance.
|
--Jördan
It's a wasted day if you don't spend at least part of it flying upside down. |
Edited by - jfitz on 28 June 2003 15:15:33 |
|
|
|
mortioli
Average Member
United Kingdom
898 Posts |
Posted - 08 July 2003 : 13:37:34
|
Sounds good, but would love to see an admin interface if anyone could !?  |
|
|
|
OneWayMule
Dev. Team Member & Support Moderator
Austria
4969 Posts |
|
|
bcorso
Starting Member
6 Posts |
Posted - 25 July 2003 : 22:53:24
|
I would love to use this, I am using SQL, but is there anyway to re-write this code for plain old ASP?
TIA
B |
|
|
|
Astralis
Senior Member
USA
1218 Posts |
Posted - 30 July 2003 : 23:18:02
|
| Is anyone working on an admin interface for this? |
Edited by - Astralis on 30 July 2003 23:18:40 |
|
|
|
mortioli
Average Member
United Kingdom
898 Posts |
Posted - 06 August 2003 : 21:05:09
|
| *bump* |
|
|
|
jfitz
Junior Member
USA
345 Posts |
Posted - 07 August 2003 : 01:13:43
|
| I'm planning to do an admin interface, but it may take a bit - my son is being married this month, so I'll be a bit tied up. |
--Jördan
It's a wasted day if you don't spend at least part of it flying upside down. |
|
|
| |
Topic |
|
|
|
Topic Locked
