You are here:

ActiveXperts.com > SMS Messaging Server > Case Studies > SMS based Man of the Match system implemented using ActiveXperts SMS Messaging Server
ActiveXperts SMS Messaging Server Send, receive and automate SMS messages

Quicklinks

SMS Man of the Match software system

This case study is included in the evaluation version of the SMS Messaging Server

1. Background

Football Games is a company which is specialized in football interactive games. This company wants their users to be able to select the Man Of The Match for each competition. Previously this could only be done through their website but the company wants to make it easier for the supporters to vote during the game itself. This is when they started to think about an SMS based solution.

2. Problem Statement

Supporters watching the game in the stadium are not able to vote. Supporters watching the competition on their TV would have to stand up and walk to their PC in order to make their vote.

3. Goals of the new System

The Man of the Match SMS Voting Demo illustrates how the product can be used to setup a Man of the Match system. The project includes a database with all English and Dutch football clubs in the 1st division. Any mobile user can use the system (i.e. no subscription is required). To do so, select a football club first by sending sending the name of a club (for instance: Chelsea) to designated SMS number.

4. ActiveXperts SMS Messaging Server Solution

SMS Messaging Server provides a solution for "Football Games", allowing its customers to SMS the club's name to a designed number, after that they will get an answer and can vote their "Man Of the Match". Each vote is then saved by SMS Messaging Server into a database. "Football Games" has integrated this database into their website, so the results can be viewed there. 

' // ========================================================================
' // Projects\Voting Demo (Man of the Match)\Triggers\Default Trigger.vbs
' // ------------------------------------------------------------------------
' // 
' // 
' // ========================================================================


Option Explicit

CONST STR_MOTMDBFILE            = "C:\Program Files\ActiveXperts\SMS Messaging Server\Projects\Voting Demo (Man of the Match)\Database\ManOfTheMatch.mdb"
CONST STR_DEBUGFILE             = "C:\Program Files\ActiveXperts\SMS Messaging Server\Sys\Tmp\Voting Demo (Man of the Match).txt"

CONST MAXQUESTIONS              = 9

CONST PROGRESS_INVALID          = 0
CONST PROGRESS_NEWSESSION       = 1
CONST PROGRESS_ANSWER           = 2

CONST IDL_ENGLISH               = 0
CONST IDL_DUTCH                 = 1


' Declaration of global objects
Dim g_nlanguage, g_objMessageDB, g_objDebugger, g_objConstants

' Creation of global objects
Set g_objConstants      = CreateObject( "Axsms-messaging-server.Constants" )
Set g_objMessageDB      = CreateObject( "Axsms-messaging-server.MessageDB" ) 
Set g_objDebugger       = CreateObject( "ActiveXperts.VbDebugger" )

' Set Debug file - for troubleshooting purposes
g_nlanguage             = IDL_ENGLISH   ' Default
g_objDebugger.DebugFile = STR_DEBUGFILE
g_objDebugger.Enabled	= True


' // ========================================================================
' // Function: ProcessMessage
' // ------------------------------------------------------------------------
' // ProcessMessage trigger function to process incoming messages
' // ========================================================================

Function ProcessMessage( numMessageID )
  Dim objMessageIn, objMessageOut
  Dim nResult, bIsNewUser, nClubID, strClubName, strReply, strError

  g_objDebugger.WriteLine ">> ProcessMessage"

  ' Open the Message Database
  g_objMessageDB.Open
  If( g_objMessageDB.LastError <> 0 ) Then
    g_objDebugger.WriteLine "<< ProcessMessage,  unable to open database"
    Exit Function
  End If

  ' Retrieve the message that has just been received. If it fails then exit script 
  Set objMessageIn   = g_objMessageDB.FindFirstMessage( "ID = " & numMessageID ) 
  If g_objMessageDB.LastError <> 0 Then
    g_objMessageDB.Close
    g_objDebugger.WriteLine "<< ProcessMessage,  FindFirstMessage failed, error: [" & g_objMessageDB.LastError & "]"
    Exit Function
  End If

  ' Avoid loopback
  If( objMessageIn.FromAddress = objMessageIn.ToAddress ) Then
    objMessageIn.Status = g_objConstants.MESSAGESTATUS_FAILED
    Exit Function
  End If

  ' Change Status to from Pending to Success. If you don't do it, the message will be processed by subsequent triggers (if defined) because message is still pending
  objMessageIn.Status = g_objConstants.MESSAGESTATUS_SUCCESS
  g_objMessageDB.Save objMessageIn   

  g_objDebugger.WriteLine "Incoming message saved, result: [" & g_objMessageDB.LastError & "]"

  bIsNewUser  = False
  nResult     = 0
  nClubID     = 0
  strClubName = ""
  strReply    = ""
  
  
  GetClubID objMessageIn.Body, nClubID  
  If( nClubID <> 0 ) Then
    SetMyClubID objMessageIn.FromAddress, nClubID, strError  
    bIsNewUser = True
  End If
  
  If( nResult = 0 ) Then
    nResult = GetMyClubID( objMessageIn.FromAddress, nClubID, strError )  
    If( nResult <> 0 ) Then
       strError = GetStringEnterValidClubID()
    End If
  End If
  
  strClubName = GetClubInfo( nClubID, g_nlanguage )
  
  If( nResult = 0 And bIsNewUser ) Then
    strReply = strReply & GetStringWelcome( strClubName )    
  End If
  
  If( nResult = 0 And Not bIsNewUser ) Then
    nResult = ProcessVote( nClubID, strClubName, objMessageIn.Body, strReply, strError )
  End If
  
  If( nResult <> 0 ) Then
    ReplyMessage objMessageIn, strError, 0  
  Else 
    ReplyMessage objMessageIn, strReply, 0
  End If

  ' Close the Message Database
  g_objMessageDB.Close

  g_objDebugger.WriteLine "<< ProcessMessage"

End Function




' // ========================================================================

Function GetClubID( strShortName, BYREF nClubID ) 
  Dim objConn, RS, strQuery
  
  g_objDebugger.WriteLine( ">> GetClubID [" & strShortName & "]" )

  nClubID     = 0 

  Set objConn = CreateObject( "ADODB.Connection" )
  objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & STR_MOTMDBFILE & ";"

  strQuery = "SELECT ID FROM Clubs WHERE strShortName='" & LCase( strShortName ) & "'"
  g_objDebugger.WriteLine( "Execute: [" & strQuery & "]..." )   
  Set RS = objConn.Execute( strQuery )
  If RS.EOF Then
    GetClubID = -1
    g_objDebugger.WriteLine( "Club not found, but that's normal when it's a vote! Only for new users, we expect the shortname in the SMS message..." )
  Else
    nClubID     = RS( "ID" )
    g_objDebugger.WriteLine( "ID retrieved: [" & nClubID & "]" )    
  End If
  
  objConn.Close
  Set objConn = Nothing

  g_objDebugger.WriteLine( "<< GetClubID, result: " &  GetClubID )
  
End Function

' // ========================================================================

Function GetClubInfo( nClubID, nLanguage )
  Dim objConn, RS, strQuery, nCountryID
  
  g_objDebugger.WriteLine( ">> GetClubInfo [" & nClubID & "]" )

  nLanguage   = IDL_ENGLISH
  GetClubInfo = ""
  
  nCountryID  = 0
  
  Set objConn = CreateObject( "ADODB.Connection" )
  objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & STR_MOTMDBFILE & ";"

  strQuery = "SELECT * FROM Clubs WHERE ID=" & nClubID
  g_objDebugger.WriteLine( "Execute: [" & strQuery & "]..." )   
  Set RS = objConn.Execute( strQuery )
  If RS.EOF Then
    GetClubInfo = "UNKNOWN"
    g_objDebugger.WriteLine( "Club name not found." )
  Else
    nCountryID = RS( "IDCountry" )
    GetClubInfo     = RS( "strDescription" )
    g_objDebugger.WriteLine( "ClubName found: [" & RS( "strDescription" ) & "]" )    
  End If
  
  If( nCountryID = 1306 ) Then
    nLanguage = IDL_DUTCH
  Else
    nLanguage = IDL_ENGLISH
  End If
  
  objConn.Close
  Set objConn = Nothing

  g_objDebugger.WriteLine( "<< GetClubInfo, result: " &  GetClubInfo )  
End Function


' // ========================================================================

Function GetMyClubID( strMobileNumber, BYREF nClubID, BYREF strError ) 
  Dim objConn, RS, strQuery, strInsert
  Dim nQ1, nQ2, nQ3

  g_objDebugger.WriteLine( ">> GetMyClubID" )

  nClubID     = 0 
  strError    = ""
  
  Set objConn = CreateObject("ADODB.Connection")
  objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & STR_MOTMDBFILE & ";"

  strQuery = "SELECT IDClub FROM Voters WHERE IDMobileNumber='" & strMobileNumber & "'"
  g_objDebugger.WriteLine( "Execute: [" & strQuery & "]..." )   
  Set RS = objConn.Execute( strQuery )
  If RS.EOF Then
    GetMyClubID = -1
'   strError = "User not found" 
    strError = "Gebruiker niet gevonden." 
  Else
    GetMyClubID = 0
    nClubID     = RS( "IDClub" )
  End If
  
  objConn.Close
  Set objConn = Nothing

  g_objDebugger.WriteLine( "<< GetMyClubID, result: " &  GetMyClubID )
End Function


' // ========================================================================

Function SetMyClubID( strMobileNumber, nClubID, BYREF strError ) 
  Dim objConn, RS, strQuery, strCommand
  Dim nQ1, nQ2, nQ3

  g_objDebugger.WriteLine( ">> SetMyClubID( [" & strMobileNumber & "], [" & nClubID & "], ...)" )

  strError    = ""
  
  Set objConn = CreateObject("ADODB.Connection")
  objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & STR_MOTMDBFILE & ";"

  strQuery = "SELECT IDMobileNumber FROM Voters WHERE IDMobileNumber='" & strMobileNumber & "'"
  g_objDebugger.WriteLine( "Execute: [" & strQuery & "]..." )   
  Set RS = objConn.Execute( strQuery )
  If RS.EOF Then
    strCommand = "INSERT INTO Voters ( IDMobileNumber, IDClub ) VALUES ( '" & strMobileNumber & "', " & nClubID & " )"
  Else
    strCommand = "UPDATE Voters SET IDClub = " & nClubID & " WHERE IDMobileNumber='" & strMobileNumber & "'"
  End If
  
  Err.Clear ' Clear a previous error
  g_objDebugger.WriteLine( "Execute: [" & strCommand & "]..." )       
  objConn.Execute( strCommand )
  
  objConn.Close
  Set objConn = Nothing

  g_objDebugger.WriteLine( "<< SetMyClubID, result: " &  SetMyClubID )
End Function


' // ========================================================================

Function ProcessVote( nClubID, strClubName, strVote, BYREF strExplanation, BYREF strError )  

  Dim objConn, RS, strQuery
  Dim nBackNumber, nPlayerCount, nPlayerID
  
  g_objDebugger.WriteLine( ">> ProcessVote( [" & nClubID & "], [" & strClubName & "], [" & strVote & "])"  )
  
  ProcessVote           = 0
  strExplanation        = ""
  strError              = ""
  
  Set objConn = CreateObject("ADODB.Connection")
  objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & STR_MOTMDBFILE & ";"
  
  nBackNumber                 = -1
  On Error Resume Next
  Err.Number = 0
  nBackNumber = CInt( Trim( strVote ) )
  On Error Goto 0
  
  If( nBackNumber > 0 ) Then
    ' Apparently, the user sent a backnumber, let's see if it exists
    ' Find out which answer it was
    strQuery = "SELECT * FROM Players WHERE IDClub=" & nClubID & " AND numBackNumber = " &  nBackNumber
  Else
    strQuery = "SELECT * FROM Players WHERE IDClub=" & nClubID & " AND strLastName = '" &  Trim( strVote ) & "'"
  End If
  
  g_objDebugger.WriteLine( "Execute: [" & strQuery & "]..." )  
  Set RS = objConn.Execute( strQuery )  

  If RS.EOF Then
    strError = GetStringPlayerNotFound()
    ProcessVote = -1
  Else
    nPlayerID       = RS( "ID" )
    nPlayerCount    = RS( "numVotes" )
    g_objDebugger.WriteLine( "Current #Votes: " & nPlayerCount )  
    
    strQuery = "UPDATE Players SET numVotes=" & nPlayerCount+1 & " WHERE ID=" &  nPlayerID
    g_objDebugger.WriteLine( "Execute: [" & strQuery & "]..." )  
    Set RS = objConn.Execute( strQuery )   

    ProcessVote = 0
     
    strExplanation = GetStringVoteAccepted( GetPlayerDisplayName( objConn, nPlayerID ), nClubID )
  End If
  
  objConn.Close
  Set objConn = Nothing

  g_objDebugger.WriteLine( "<< ProcessVote, result: " &  ProcessVote )
  
End Function



Function GetPlayerDisplayName( objConn, numPlayerID )
  
  Dim strQuery, RS, strAltName, strFirstName, strTussen, strLastName
  
  GetPlayerDisplayName = ""
  
  g_objDebugger.WriteLine( ">> GetPlayerDisplayName( ..., [" & numPlayerID & "])" ) 
  
  strQuery = "SELECT * FROM Players WHERE ID=" & numPlayerID
  g_objDebugger.WriteLine( "Execute: [" & strQuery & "]..." )  
  Set RS = objConn.Execute( strQuery )  

  If( RS.EOF ) Then
    GetPlayerDisplayName = "UNKNOWN"
  Else
    strAltName    = Trim( RS( "strAltName" ) )
    strFirstName  = Trim( RS( "strFirstName" ) )
    strTussen     = Trim( RS( "strTussenvoegsel" ) )
    strLastName   = Trim( RS( "strLastName" ) )
    If( strAltName <> "" ) Then
      GetPlayerDisplayName = strAltName
    Else
      If( strFirstName <> "" ) Then
        GetPlayerDisplayName = strFirstName & " "
      End If
      If( strTussen <> "" ) Then
        GetPlayerDisplayName = GetPlayerDisplayName & strTussen & " "
      End If
      GetPlayerDisplayName = GetPlayerDisplayName & strLastName
    End If
  End If
  
  g_objDebugger.WriteLine( "<< GetPlayerDisplayName, result: " &  GetPlayerDisplayName )  
End Function



' // ========================================================================
' // ReplyMessage
' // ------------------------------------------------------------------------
' // Auto reply to every incoming SMS message
' // ========================================================================

Function ReplyMessage( objMessageIn, strResponse, nDelayMinutes )

   Dim objMessageOut

   g_objDebugger.WriteLine ">> ReplyMessage"

   ' WRITE YOUR CODE HERE (for instance: forward message to an e-mail address, see below)
   Set objMessageOut = g_objMessageDB.Create
   If( g_objMessageDB.LastError = 0 ) Then
     objMessageOut.Direction = g_objConstants.MESSAGEDIRECTION_OUT
     objMessageOut.Status    = g_objConstants.MESSAGESTATUS_PENDING
     objMessageOut.Type      = objMessageIn.Type 
     objMessageOut.ToAddress = objMessageIn.FromAddress
     objMessageOut.ChannelID = objMessageIn.ChannelID
     objMessageOut.BodyFormat= objMessageIn.BodyFormat
     objMessageOut.Body      = strResponse
     If( nDelayMinutes <> 0 ) Then
      objMessageOut.ScheduledTime = "+0d0h" & nDelayMinutes & "m"
     End If
     g_objMessageDB.Save objMessageOut
   End If

  g_objDebugger.WriteLine "<< ReplyMessage"

End Function




' // ========================================================================

Function GetStringEnterValidClubID()
  If( g_nlanguage = IDL_DUTCH ) Then
    GetStringEnterValidClubID = "U dient eerst een geldige club ID in te vullen."
  Else
    GetStringEnterValidClubID = "Please send a valid club ID first."  
  End If
End Function

Function GetStringWelcome( strClubName )
  If( g_nlanguage = IDL_DUTCH ) Then
    GetStringWelcome = "Welkom bij Man of the Match. Kies uw favoriete speler van de dag van " & strClubName & ". Stuur daartoe het rugnummer of achternaam van de speler via SMS naar dit nummer."
  Else
    GetStringWelcome = "Welcome to the Man of the Match. Choose your favorite player of the day of " & strClubName & ". Reply with his shirtnumber or his surname."
  End If
End Function

Function GetStringPlayerNotFound()
  If( g_nlanguage = IDL_DUTCH ) Then
    GetStringPlayerNotFound = "Fout: Speler niet gevonden. Probeer het nog eens door rugnummer of achternaam van de speler te sturen."
  Else
    GetStringPlayerNotFound = "Error: Unable to find player. Please try again by sending either the backnumber or the lastname of the player."
  End If
End Function

Function GetStringVoteAccepted( strPlayerDisplayName, nClubID )
  If( g_nlanguage = IDL_DUTCH ) Then
    GetStringVoteAccepted = "Stem voor " & strPlayerDisplayName & " is geaccepteerd. Bekijk de stem resultaten via http://intranet.activexperts-labs.com?c=" & nClubID
  Else
    GetStringVoteAccepted = "Vote for " & strPlayerDisplayName & " was accepted. Check out the results at http://intranet.activexperts-labs.com?c=" & nClubID
  End If
End Function