Here's the code (VB.Net). Paste this into a file called birthdayrss.ashx and make any modifications needed (for example, my custom DoB property was called 'DateOfBirth' - yours might be different, and will require changes to the SQL statement). You may find problems with the code (esp. the SQL statement) after pasting - it might need removal of blank lines etc. Obviously this best done in Visual Studio so you can check syntax. It needs the connection string in user.config and the style files for browser viewing. Drop the file into the root of your mojoPortal site. You should be able to browse to it and see the results. Then you can add it to a page using the Feed Manager, switching off settings like author and date. Or you could use the XML/XSLT module to display the data in a table, list, etc (obviously requiring some work to write the XSLT).
NB no guarantees this code sample will work for you etc etc.
A separate issue, but I'm finding the behaviour of the Feed Manager puzzling with this feed - on my site it is showing birthdays that are no longer present in the RSS feed, even though I've set its cache duration to 60 minutes and "Show Posts up to this many days old" to 1 day, and cleared the feed cache. One for another post.
<%@ WebHandler Language="VB" Class="RssCustomHandler" %>
Imports System
Imports System.Web
Imports System.Data
Imports System.Xml
Imports System.IO
Imports System.Data.SqlClient
Public Class RssCustomHandler : Implements IHttpHandler
Public Sub ProcessRequest(ByVal context As HttpContext) Implements IHttpHandler.ProcessRequest
Dim rssXML As String
Dim dt As DataTable, sql As String
Dim BlogTitle As String = "Born on this day..."
Dim BlogLink As String = "http://breconfans.org.uk"
Dim BlogDescr As String = "RSS feed of members born today"
' fetch all DoBs here - trying to work out today's birthdays in SQL is a thankless task
' when they are stored in an ntext field without rigorous input validation
' NB this is maybe not sensible if there are large numbers of users, but works fine for my site with about 400
sql = "Select U.UserId," +
" U.LoginName AS PersonName, " +
" CAST(PropertyValueString AS VARCHAR(50)) AS DoB " +
" FROM dbo.mp_UserProperties UP " +
" INNER JOIN dbo.mp_Users U " +
" ON UP.UserGuid = U.UserGuid " +
" WHERE UP.PropertyName = 'DateOfBirth'"
dt = GetDataTable(sql)
If dt IsNot Nothing Then
rssXML = CreateRSSXMLstring(BlogTitle, BlogLink, BlogDescr, dt)
context.Response.ContentType = "application/xml"
context.Response.Write(rssXML)
Else
context.Response.ContentType = "text/plain"
context.Response.Write("No data")
End If
End Sub
Public ReadOnly Property IsReusable() As Boolean Implements IHttpHandler.IsReusable
Get
Return False
End Get
End Property
Private Function CreateRSSXMLstring(title As String, link As String, description As String, dt As DataTable) As String
Dim txtResult As String = ""
Dim DoB As Date, BirthdayThisYear As Date, Age As Integer
Dim itemTitle As String = String.Empty
Dim itemDescr As String = String.Empty
Dim itemLink As String = String.Empty
Dim memory_stream As New MemoryStream ' , datePub As Date
Dim xml_text_writer As New XmlTextWriter(memory_stream, System.Text.Encoding.UTF8)
xml_text_writer.WriteStartDocument(True)
' add processing instr, like this example from mojoPortal blog rss
' <?xml-stylesheet type="text/css" href="http://flowerbunch.org/Data/style/rss1.css" ?> <?xml-stylesheet type="text/xsl" href="http://flowerbunch.org/Data/style/rss1.xsl" ?>
Dim uri As Uri = HttpContext.Current.Request.Url
Dim RssStyleHome As String = uri.Scheme + uri.SchemeDelimiter + uri.Host + ":" + uri.Port.ToString
If RssStyleHome.Contains("localhost") Then
' dev location of my styling files - allows the RSS to be viewed in browser
RssStyleHome = RssStyleHome + "/WebSite3"
End If
xml_text_writer.WriteProcessingInstruction("xml-stylesheet", "type=""text/css"" href=""" + RssStyleHome + "/Data/style/rss1.css""")
xml_text_writer.WriteProcessingInstruction("xml-stylesheet", "type=""text/xsl"" href=""" + RssStyleHome + "/Data/style/rss1.xsl""")
xml_text_writer.WriteStartElement("rss")
xml_text_writer.WriteAttributeString("version", "2.0")
xml_text_writer.WriteAttributeString("xmlns:atom", "http://www.w3.org/2005/Atom")
' metadata
xml_text_writer.WriteStartElement("channel")
xml_text_writer.WriteElementString("title", title)
xml_text_writer.WriteElementString("link", link)
xml_text_writer.WriteStartElement("atom:link", String.Empty)
xml_text_writer.WriteAttributeString("href", uri.AbsoluteUri)
xml_text_writer.WriteAttributeString("rel", "self")
xml_text_writer.WriteAttributeString("type", "application/rss+xml")
xml_text_writer.WriteEndElement() ' atom:link
xml_text_writer.WriteElementString("language", "en")
xml_text_writer.WriteElementString("description", description)
xml_text_writer.WriteElementString("docs", "http://www.rssboard.org/rss-specification")
xml_text_writer.WriteElementString("generator", "mojoPortal custom RSS feed")
xml_text_writer.WriteElementString("ttl", "60") ' time to live, i.e. cache minutes at client
' now add the data
For Each dr As DataRow In dt.Rows
' check whether born in relevant timespan, and add to RSS feed if so
DateTime.TryParse(dr("DoB").ToString(), DoB)
Age = GetAge(DoB).ToString
BirthdayThisYear = DateAdd(DateInterval.Year, Age, DoB)
' can easily adapt this test to show birthdays today or within last week/month etc by changing the number
' for today's birthdays only set this to 0
If Math.Abs((DateDiff("d", DateTime.Now, BirthdayThisYear))) <= 30 Then
xml_text_writer.WriteStartElement("item")
itemTitle = dr("PersonName").ToString() + " is " + Age.ToString
xml_text_writer.WriteElementString("title", itemTitle)
' link to user's profile, assuming link is website base address
' if it's not, could use HttpContext.Current.Request.Url properties
itemLink = link + "/ProfileView.aspx?userid=" + dr("UserId").ToString()
xml_text_writer.WriteElementString("link", itemLink)
'rss spec requires a guid
xml_text_writer.WriteElementString("guid", itemLink)
' author?
xml_text_writer.WriteElementString("author", String.Empty)
' conceal real date of birth for this demo
'itemDescr = dr("PersonName").ToString() + " was " + Age.ToString + " on " + "32nd of Movember (real date concealed)"
' replace with this to show the real DoB
itemDescr = dr("PersonName").ToString() + " was " + Age.ToString + " on " + BirthdayThisYear.ToString("M")
itemDescr += ". Click the link to view more details about " + dr("PersonName").ToString() + "."
xml_text_writer.WriteStartElement("description")
' wrap in CDATA just in case we want to include markup etc
xml_text_writer.WriteCData(itemDescr)
xml_text_writer.WriteEndElement()
' for some kinds of feed pubDate could come from the data
' for this example, just set to today
'DateTime.TryParse(dr("pubDate").ToString(), datePub)
'xml_text_writer.WriteElementString("pubDate", datePub.ToString("R")) ' RFC1123Pattern
xml_text_writer.WriteElementString("pubDate", DateTime.Now.ToString("R"))
' category tag
xml_text_writer.WriteElementString("category", "birthdays")
xml_text_writer.WriteEndElement() ' item
End If
Next
xml_text_writer.WriteEndElement() ' channel
xml_text_writer.WriteEndElement() ' rss
xml_text_writer.WriteEndDocument()
xml_text_writer.Flush()
Dim buffer As [Byte]() = New [Byte](memory_stream.Length - 1) {}
buffer = memory_stream.ToArray()
txtResult = System.Text.Encoding.UTF8.GetString(buffer)
xml_text_writer.Close()
Return txtResult
End Function
Private Function GetAge(DateOfBirth As Date) As Integer
' get the difference in years
Dim years As Integer = DateTime.Now.Year - DateOfBirth.Year
' subtract another year if before the birth date in the current year
If DateTime.Now.Month < DateOfBirth.Month Or (DateTime.Now.Month = DateOfBirth.Month And DateTime.Now.Day < DateOfBirth.Day) Then
years = years - 1
End If
Return years
End Function
#Region "DAL"
Private Function GetDataTable(SQLCommand As String) As DataTable
Dim DT As New DataTable
Dim CONNECTION_STRING As String = ConfigurationManager.AppSettings("MSSQLConnectionString")
Try
Using conn As New SqlConnection(CONNECTION_STRING)
Using com As New SqlCommand(SQLCommand, conn)
Using sqlAdpt As New SqlDataAdapter(com)
conn.Open()
sqlAdpt.Fill(DT)
End Using
End Using
End Using
Catch ex As Exception
'LogSqlException(SQLCommand, ex, params)
End Try
Return DT
End Function
#End Region
End Class