Odense Bulldogs Ice Hockey Club / Danish Ice Hockey Federation
Date added: March 15, 2012



1. Background

The Odense Bulldogs Ice Hockey Club is located in Odense, Denmark and has 30 officials, 30 referees and hundreds of players.

Officials, referees and players are notified via e-mail when there's a new ice hokey event, or when details of an ice hockey game have changed. The Odense Bulldogs club gathers this information from the national Danish Ice Hockey Federation, through a SOAP/XML interface.

To send out the e-mail notifications, the Odense Ice Hockey club is using a web based notification system. A representative logs in to the system, selects one or more officials, referees and players associated with the specific ice hockey game. Then, the representative manually sends and e-mail to the selected persons.

The representative makes sure that there's a total of 5 officials and 2-4 referees attending every ice hockey game.

The referees, officials and players receive an e-mail message:

2. Problem Statement

The main problem is that not every referee, official or player reads the e-mail message on time. That's a problem since the details of an ice hockey game may change on the same day the ice hockey game is scheduled. Therefore it is important that anyone associated with this ice hockey game is notified on time.

Besides that, the current process of sending out notifications is slow, because it is done manually by a representative.

3. Goals of the new System

The goal of the system is notify referees, officials and players in a quick way. This will be done through SMS.
Another goal is to let the new system send out the notifications automatically, rather than having a representative doing it manually.

4. ActiveXperts SMS Messaging Server Solution

ActiveXperts SMS Messaging Server was chosen for this project, because it’s very easy to use, configuration is easy and it can make use a MySQL database.

The reason why an SMS based solution was chosen is because everyone and specially the younger ones carry mobile phones with them. This means when an SMS message has been send to them they will be able to read the SMS message within minutes. This also eliminates the need for the admin to check upon the referees, officials and the players to see if they actually have read their SMS messages.

The solution was fairly simple to be implemented in the current notification system, only a few lines of code where needed to be added to the existing code in the ASP web pages of the notification system.

Odense Bulldogs scheme

Figure 1

Figure 1 shows a new game. No officials or referees where added to this game yet. Choosing who to send the SMS message to, and what text to send, bring up Figure2.

SMS Text scheme

Figure 2

When pressing the Send SMS button the webpage creates a new record in the SMS Messaging Server’s Message database for each recipient chosen. The SMS Messaging Server will pick up these newly created records and sends out SMS messages corresponding to each record.

5. Source code

Small snapshot of source code:

  ' DNSLess connection to MySQL
  Set mySMS = Server.CreateObject("ADODB.Connection")
  mySMS.open "Driver={MySQL ODBC 5.1 Driver};Server=hostname;Port=3306;" & _ 
             "Database=messages;User=username; Password=password;Option=3;"

  if request.form("send") = "Send SMS" then
    sSeg = split(request.form("recipients"),";")
    for n = 0 to ubound(sSeg)
      if sSeg(n) <> "" then
        strSQL = "SELECT * FROM user WHERE ID = " & sSeg(n)
        set rsHent = myConn.execute(strSQL)
        response.write "Sending to Mobile: " & rsGet("Mobile") & "<br>"
        rQry = "INSERT INTO messages SET"
        rQry = rQry & " Direction = 2"
        rQry = rQry & ", Type = 2"
        rQry = rQry & ", Status = 1"
        rQry = rQry & ", Recipient = '+45" & rsGet("Mobile") & "'"
        rQry = rQry & ", ChannelID = 1001"

        Body = vbcrlf & "*** Ice hockey - Officials ***"
        Body = Body & vbcrlf & request.form("body") & vbcrlf & "This sms can't bed answered"

        rQry = rQry & ", Body = '" & Body & "'"
        mySMS.execute rQry
      end if
  end if