You are here:

ActiveXperts.com > SMS Messaging Server > Case Studies > SMS Trivia for Worldcup Football 2010
ActiveXperts SMS Messaging Server Send, receive and automate SMS messages

Quicklinks

SMS Worldcup Footbal Trivia software

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

1. Background

For the upcoming Football World Cup 2010 in South Africa a popular TV broadcast company wants to provide SMS based games for its customers.

2. Problem Statement

The TV company still manually processes the large number of incoming SMS messages. Employees look through a database of message and evaluate each message individually. This takes a lot of time.

3. Goals of the new System

Interactive cell phone trivia game that allows anyone with a cell phone to join this Worldcup Football trivia. All questions have 3 possible answers: a, b or c. After each answer you will receive an SMS with the correct answer followed by the next question. After the last question, the final score is sent to you.

4. ActiveXperts SMS Messaging Server Solution

 ' // ========================================================================
' // Projects\Trivia Demo (Worldcup Football)\Triggers\Default Trigger.vbs
' // ------------------------------------------------------------------------
' // 
' // 
' // ========================================================================


Option Explicit

CONST STR_TRIVIADBFILE = "C:\Program Files\ActiveXperts\SMS Messaging Server\" & _ 
                         "Projects\Trivia Demo (Worldcup Football)\Database\Trivia.mdb"
CONST STR_DEBUGFILE    = "C:\Program Files\ActiveXperts\SMS Messaging Server\" & _
                         "Sys\Tmp\Trivia Demo (Worldcup Football).txt"

CONST MAXQUESTIONS              = 9

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


' Declaration of global objects
Dim 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_objDebugger.DebugFile = STR_DEBUGFILE
g_objDebugger.Enabled	= True


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

Function ProcessMessage( numMessageID )
  Dim objMessageIn, objMessageOut
  Dim bIsNewUser, bNoMoreQuestions
  Dim nResult, nUserID, strReply, strScore, strNextQuestion, 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
  strReply    = ""
  
  If( nResult = 0 ) Then
    nResult = GetUserID( objMessageIn.FromAddress, nUserID, bIsNewUser, strError )  
  End If
  
  If( nResult = 0 And bIsNewUser ) Then
    strReply = "Welcome to the World Cup Football SMS Game! "
  End If
  
  If( nResult = 0 And Not bIsNewUser ) Then
    nResult = ProcessAnswer( nUserID,  objMessageIn.Body, strReply, strError )
  End If
   
  If( nResult = 0 ) Then
    nResult = GetNextQuestion( nUserID, strNextQuestion, bNoMoreQuestions, strError )   
    If( nResult = 0 ) Then
      strReply = strReply & strNextQuestion
    End If
  End If
 
  If( nResult <> 0 ) Then
    ReplyMessage objMessageIn, strError, 0
  ElseIf( bIsNewUser ) Then
    ReplyMessage objMessageIn, strReply, 0
  ElseIf( bNoMoreQuestions ) Then
    If( GetScore( nUserID, strScore, strError ) = 0 ) Then  
      strReply = strReply & strScore    
      ReplyMessage objMessageIn, strReply, 0 
      ReplyMessage objMessageIn, "Like SMS trivia games? Try the new Movies Quiz, " & _
          "it's fun! SMS 'Movies' to 1234 to join.", 1
    Else
      ReplyMessage objMessageIn, strError, 0
    End If
  Else
    ReplyMessage objMessageIn, strReply, 0
  End If     
  
  ' Close the Message Database
  g_objMessageDB.Close

  g_objDebugger.WriteLine "<< ProcessMessage"

End Function



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

Function GetUserID( strMobileNumber, BYREF nUserID, BYREF bIsNewUser, BYREF strError ) 
  Dim objConn, RS, strQuery, strInsert
  Dim nQ1, nQ2, nQ3

  g_objDebugger.WriteLine( ">> GetUserID" )

  nUserID     = 0 
  bIsNewUser  = False
  strError    = ""
  
  Set objConn = CreateObject("ADODB.Connection")
  objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & STR_TRIVIADBFILE & ";"

  strQuery = "SELECT ID FROM TriviaUsers WHERE strMobileNumber='" & strMobileNumber & _
      "' AND ( nA1 = 0 OR nA2 = 0 OR nA3 = 0 ) ORDER BY ID Desc"
  g_objDebugger.WriteLine( "Execute: [" & strQuery & "]..." )   
  Set RS = objConn.Execute( strQuery )
  If RS.EOF Then

    ' Try to find out the last question of the previous question
     strQuery = "SELECT nQ3 FROM TriviaUsers WHERE strMobileNumber='" & strMobileNumber & _
          "' ORDER BY ID Desc"
     g_objDebugger.WriteLine( "Execute: [" & strQuery & "]..." )   
    Set RS = objConn.Execute( strQuery )   
    If( Not RS.EOF ) Then
      nQ1 = CInt( RS( "nQ3" ) ) + 1
      If( nQ1 > MAXQUESTIONS ) Then
        nQ1 = 1
      End If
      nQ2 = nQ1 + 1
      If( nQ2 > MAXQUESTIONS ) Then
        nQ2 = 1
      End If   
      nQ3 = nQ2 + 1
      If( nQ3 > MAXQUESTIONS ) Then
        nQ3 = 1
      End If  
    Else
      nQ1 = 1
      nQ2 = 2
      nQ3 = 3
    End If
    
    strInsert = "INSERT INTO TriviaUsers ( strMobileNumber, nQ1, nQ2, nQ3 ) VALUES ( '" & _
          strMobileNumber & "', " & nQ1 & ", " & nQ2 & ", " & nQ3 & " )"
    Err.Clear ' Clear a previous error
    g_objDebugger.WriteLine( "Execute: [" & strInsert & "]..." )       
    objConn.Execute( strInsert )
    
    bIsNewUser = True
    
    strQuery = "SELECT ID FROM TriviaUsers WHERE strMobileNumber='" & strMobileNumber & _
          "' ORDER BY ID Desc"
    g_objDebugger.WriteLine( "Execute: [" & strQuery & "]..." )      
    Set RS = objConn.Execute( strQuery )    
    
  End If
  
  If RS.EOF Then
    GetUserID = -1
    strError = "Failed to signup user as a Trivia user."
  Else
    nUserID = RS( "ID" ) 
    GetUserID = 0    
  End If
  
  objConn.Close
  Set objConn = Nothing

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



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

Function GetScore( nUserID, BYREF strScore, BYREF strError ) 
  Dim objConn, RS, strQuery, strInsert
  Dim arrQuestions( 99 )
  Dim arrAnswers( 99 )
  Dim nAnswerID, strFormalAnswerString, iQuestion, nPositive, nTotal

  g_objDebugger.WriteLine( ">> GetScore( " & nUserID & ")" )

  strScore    = ""
  strError    = ""
  
  nPositive   = 0
  nTotal      = 3
  
  Set objConn = CreateObject("ADODB.Connection")
  objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & STR_TRIVIADBFILE & ";"

  strQuery = "SELECT * FROM TriviaUsers WHERE ID=" & nUserID & " ORDER BY ID Desc"
  g_objDebugger.WriteLine( "Execute: [" & strQuery & "]" )
  Set RS = objConn.Execute( strQuery )
  If RS.EOF Then
    GetScore = -1
    strError = "Failed to retrieve user."
  Else
    arrQuestions( 0 ) = CInt( RS( "nQ1" ) )
    arrQuestions( 1 ) = CInt( RS( "nQ2" ) )
    arrQuestions( 2 ) = CInt( RS( "nQ3" ) )
    arrAnswers( 0 ) = CInt( RS( "nA1" ) )
    arrAnswers( 1 ) = CInt( RS( "nA2" ) )
    arrAnswers( 2 ) = CInt( RS( "nA3" ) )
    
    For iQuestion = 0 To 2
      If( arrQuestions( iQuestion ) <= 0 Or arrAnswers( iQuestion ) <= 0 ) Then
        GetScore = -1
        strError = "Error: One or more questions are not answered."
        Exit For
      End If
                
      If( GetAnswer( objConn, arrQuestions( iQuestion ), _
          nAnswerID, strFormalAnswerString ) <> 0 ) Then
        GetScore = -1
        strError = "Error: Failed to find answer for question " & arrQuestions( i )
        Exit For
      End If
        
      If( arrAnswers( iQuestion ) = nAnswerID ) Then
        g_objDebugger.WriteLine(  "YES, Answer on Question " & arrQuestions( iQuestion ) & _
            " = " & nAnswerID & " (" & strFormalAnswerString & ")" )    
        nPositive = nPositive + 1
      Else
        g_objDebugger.WriteLine(  "NO, Answer on Question " & arrQuestions( iQuestion ) & _
            " <> " & nAnswerID & " (" & strFormalAnswerString & ")" )           
      End If
    Next
  End If
  
  objConn.Close
  Set objConn = Nothing
  
  If( GetScore = 0 ) Then
    strScore = "Trivia completed. Score " & nPositive & " out of 3."
    
    If( nPositive = 3 ) Then
      strScore = strScore & " Well done, you are an expert! You will receive a coupon for " & _
          "a free hamburger at McDonald’s. Enjoy!"
    Else
      strScore = strScore & " Thanks for joining! You will receive a coupon for a free " & _
          "hamburger at McDonald’s. Enjoy!"
    End If
  End If
  
  g_objDebugger.WriteLine( "<< GetScore, result: " &  GetScore )
End Function


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

Function GetAnswer( objConn, nQuestionID, BYREF nFormalAnswerID, BYREF strFormalAnswerString )

  Dim strQuery, RS
  
  g_objDebugger.WriteLine( ">> GetAnswer( " & nQuestionID & ")" )

  nFormalAnswerID       = 0
  strFormalAnswerString = ""
        
  strQuery   = "SELECT * FROM TriviaQuestions WHERE ID=" & nQuestionID     
  g_objDebugger.WriteLine( "Execute: [" & strQuery & "]..." )
  Set RS = objConn.Execute( strQuery ) 
  
  If( RS.EOF ) Then
    GetAnswer = -1
    strError = "Error: Failed to find answer for question " &nQuestionID
  Else  
    nFormalAnswerID = CInt( RS( "nTriviaAnswerID" ) )
    If( nFormalAnswerID = 1 ) Then
      strFormalAnswerString = RS( "strAnswerA" )
    ElseIf( nFormalAnswerID = 2 ) Then
      strFormalAnswerString = RS( "strAnswerB" )
    ElseIf( nFormalAnswerID = 3 ) Then
      strFormalAnswerString = RS( "strAnswerC" )    
    End If
    
    g_objDebugger.WriteLine(  "nAnswerID: " & nFormalAnswerID ) 
    g_objDebugger.WriteLine(  "strFormalAnswerString: " & strFormalAnswerString ) 
    GetAnswer = 0
  End If
   
  g_objDebugger.WriteLine( "<< GetAnswer, result: " &  GetAnswer )
End Function



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

Function ProcessAnswer( nUserID, strAnswer, BYREF strExplanation, BYREF strError )  

  Dim objConn, RS, strQuery, strUpdate, strAField
  Dim strUAnswer, nQuestionID, nAnswer, nFormalAnswerID, strFormalAnswerString
  
  g_objDebugger.WriteLine( ">> ProcessAnswer" )
  
  ProcessAnswer         = 0
  strExplanation        = ""
  strError              = ""
  strFormalAnswerString = ""
  
  ' Translate the answer into A, B or C
  strUAnswer = Left( UCase( Trim( strAnswer ) ), 1 )
  If( strUAnswer = "A" ) Then
    nAnswer = 1
  ElseIf( strUAnswer = "B" ) Then
    nAnswer = 2
  ElseIf( strUAnswer = "C" ) Then
    nAnswer = 3
  Else  
    ProcessAnswer = -1
    strError = "Invalid answer, please type a, b or c."
    Exit Function
  End If
 
  Set objConn = CreateObject("ADODB.Connection")
  objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & STR_TRIVIADBFILE & ";"
  
  ' Find out which answer it was
  strQuery = "SELECT * FROM TriviaUsers WHERE ID=" & nUserID
  g_objDebugger.WriteLine( "Execute: [" & strQuery & "]..." )  
  Set RS = objConn.Execute( strQuery )
  If RS.EOF Then
    strError = "Error: Unable to process answer."
    ProcessAnswer = -1
  Else
    If( CInt( RS( "nA1" ) ) = 0 ) Then 
      nQuestionID = RS( "nQ1" )
      strAField = "nA1"
    ElseIf( CInt( RS( "nA2" ) ) = 0 ) Then 
      nQuestionID = RS( "nQ2" )    
      strAField = "nA2"     
    ElseIf( CInt( RS( "nA3" ) ) = 0 ) Then 
      nQuestionID = RS( "nQ3" )    
      strAField = "nA3"  
    Else
      nQuestionID = 0   
      strAField = "" 
    End If 
    
    If( strAField = "" ) Then
      strError = "Error: Cannot find question associated with this answer."
      ProcessAnswer = -1     
    End If
  End If
      
  If( ProcessAnswer = 0 ) Then      
    strUpdate = "Update TriviaUsers SET " & strAField & " = " & nAnswer & _
        " WHERE ID=" & nUserID
    g_objDebugger.WriteLine( "Execute: [" & strUpdate & "]..." )    
    Err.Clear ' Clear a previous error    
    objConn.Execute strUpdate  
  End If
  
  If( GetAnswer( objConn, nQuestionID, nFormalAnswerID, strFormalAnswerString ) = 0 ) Then
    If( nFormalAnswerID = nAnswer ) Then
      strExplanation = "Answer is correct! "
    Else
       strExplanation = "Oops, wrong answer, the correct answer was '" & _
          strFormalAnswerString & "'. "
    End If
  End If
  
  objConn.Close
  Set objConn = Nothing

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


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

Function GetNextQuestion( nUserID, BYREF strNextQuestion, BYREF bNoMoreQuestions, BYREF strError ) 
  Dim objConn, RS, strQuery, nSeq, nQuestionID

  g_objDebugger.WriteLine( ">> GetNextQuestion" )
  
  GetNextQuestion   = -1
  strNextQuestion   = ""
  bNoMoreQuestions  = False
  strError          = ""
  
  nSeq              = 0
  nQuestionID       = 0

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

  ' Find the User and its next question to be answered
  strQuery = "SELECT * FROM TriviaUsers WHERE ID=" & nUserID & " ORDER BY ID Desc"
  g_objDebugger.WriteLine( "Execute: [" & strQuery & "]..." )     
  Set RS = objConn.Execute( strQuery )  
  If( RS.EOF ) Then
    GetNextQuestion = -1
    strError        = "Error: Lookup user failed."
    objConn.Close    
    Exit Function  
  ElseIf( CInt( RS( "nA1" ) ) = 0 ) Then
    nSeq        = 1
    nQuestionID = CInt( RS( "nQ1" ) )
  ElseIf( CInt( RS( "nA2" ) ) = 0 ) Then
    nSeq        = 2
    nQuestionID = CInt( RS( "nQ2" ) )
  ElseIf( CInt( RS( "nA3" ) ) = 0 ) Then
    nSeq        = 3  
    nQuestionID = CInt( RS( "nQ3" ) )
  Else
    GetNextQuestion   = 0
    bNoMoreQuestions  = True
    objConn.Close    
    Exit Function
  End If
  
  ' Lookup the Question Text
  strQuery = "SELECT * FROM TriviaQuestions WHERE ID = " & nQuestionID
  g_objDebugger.WriteLine( "Execute: [" & strQuery & "]..." )   
  Set RS = objConn.Execute( strQuery )  
  If( RS.EOF ) Then
    GetNextQuestion = -1
    strError        = "Error: Failed to retrieve question."
    objConn.Close    
    Exit Function
  End If

  strNextQuestion = "Question "& nSeq & " of 3: " & RS( "strQuestion" ) & " a) " & _
      RS( "strAnswerA" ) & " b) " & RS( "strAnswerB" )  & " c) " & RS( "strAnswerC" ) & _
      ". Reply with a, b or c."
  GetNextQuestion = 0
  
  objConn.Close
  Set objConn = Nothing

  g_objDebugger.WriteLine( "<< GetNextQuestion, result: " &  GetNextQuestion )
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