Hot Radio SMS trivia quiz – A case study

Introduction

In this case study we’ll use the SMS Server to setup a small SMS Trivia example project.

The trivia project was originally setup for the Hot Radio radio station to engage with listeners. If a listener could answer three questions successfully they receive a free coupon.

However, the concept of a short trivia quiz can be useful in a lot more situations. And not just to entertain. We’ve since applied it to:

Created engagement in radio shows.
Study the knowledge on a specific subject in certain focus groups with regard to disease prevention.
Automatically checking in on elderly patients to make sure they take their medications properly.
Help citizens get in contact with emergency responders during a natural disaster.
Etc…

All of these situations call for a dialogue with the mobile user. The difference between these situations is that sometimes the mobile users initiates and sometimes system will automatically initiate the dialog.

Use case

In this case we’ll just look at a straight forward trivia example. Which can serve as a template for the use-cases above.

HotRadio – Trivia

The Hot Radio DJ’s let their listener know that they can send the text ‘Triva’ to start a game of trivia. Winners of this game will receive a coupon and may be called back live on-air.

Prerequisites

The Auron SMS Server.
A SQL Server (express) installation or the built-in LocalDB database.
A GSM modem, SMPP subscription or the built-in demonstration gateway.

If you don’t currently have any means of sending SMS messages you can just test this using the built-in SMPP client settings.

Installation

The use case starts with the installation of the SMS Server. Have a look in the knowledge base to find out the system requirements and the installation instructions.

You can use the built-in LocalDB database to get started right-away. But it makes sense to install to a SQL Server Express database before going live. In any case. Make sure to install the free SQL Server management studio for easy access to your database.

Right after installation make sure to configure an SMS channel. This SMS channel will be used to send and receive SMS messages.

Database

The Hot Radio database is a simple .mdb file (Microsoft Access) file. This is because ideally even non-technical persons should be able to create a new quiz or just edit / replace questions in the quiz.

Another advantage of this approach is that any changes to the Trivia database are immediately live.

Two example databases are:

Music Trivia
Worldcup Football

Trivia SMS Trigger

In this case the Trivia SMS trigger is a VBScript trigger. Find more information on how to setup an empty VBScript trigger here.

The triggers should match any SMS with the StatusID Received.

After creating the trigger edit the script and change it to the following:

CONST STR_DEBUGFILE     = "C:\ProgramData\Auron\SMS Server\Log\TRIVIA.txt"
CONST STR_TRIVIADBFILE  = "C:\ProgramData\Auron\SMS Server\Triggers\SMSTrivia\database\Trivia.mdb"
CONST B_ENABLE_DEBUG    = True

' // ========================================================================
' // Function: ProcessMessageEx
' // ------------------------------------------------------------------------
' // ProcessMessageEx trigger function to process incoming messages
' // ========================================================================
Function ProcessMessageEx(objMessageIn, objMessageDB, dctContext)

  Log ">> ProcessMessageEx"
  
  bIsNewUser = False  
  bNoMoreQuestions = False
  strReply = ""
  strNextQuestion = ""
  strScore = ""
  strError = ""
  nUserID = 0
  
  nResult = GetUserID(objMessageIn.FromAddress, nUserID, bIsNewUser, strError)
  
  If (nResult = 0 And bIsNewUser) Then
    strReply = "Welcome to the Music Trivia 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 objMessageDB, objMessageIn, strError
  ElseIf (bIsNewUser) Then
    ReplyMessage objMessageDB, objMessageIn, strReply
  ElseIf (bNoMoreQuestions) Then
    If (GetScore(nUserID, strScore, strError) = 0) Then  
      strReply = strReply & strScore
      ReplyMessage objMessageDB, objMessageIn, strReply
      ReplyMessage objMessageDB, objMessageIn, "Like SMS trivia games? " & _ 
       "Try the new Movies Quiz, it's fun! SMS 'Movies' to 1234 to join."
    Else
      ReplyMessage objMessageDB, objMessageIn, strError
    End If
  Else
    ReplyMessage objMessageDB, objMessageIn, strReply
  End If  
  
  Log "<< ProcessMessageEx"

End Function

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

Function GetUserID(strMobileNumber, BYREF nUserID, BYREF bIsNewUser, BYREF strError)
  Log ">> GetUserID" 

  nQ1 = 0: nQ2 = 0: nQ3 = 0
  nUserID     = 0 
  bIsNewUser  = False
  strError    = ""
  
  Set objConn = CreateObject("ADODB.Connection")
  objConn.Open "Provider=Microsoft.ACE.OLEDB.12.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"
  Log "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"
    Log "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 & " )"
    Log "Execute: [" & strInsert & "]..." 
    objConn.Execute(strInsert)
    
    bIsNewUser = True
    
    strQuery = "SELECT ID FROM TriviaUsers WHERE strMobileNumber='" & strMobileNumber & _
      "' ORDER BY ID Desc"
    Log "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

  Log "<< GetUserID, result: " &  GetUserID
End Function

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

Function GetScore(nUserID, BYREF strScore, BYREF strError)
  Dim arrQuestions(99)
  Dim arrAnswers(99)

  Log ">> GetScore( " & nUserID & ")"

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

  strQuery = "SELECT * FROM TriviaUsers WHERE ID=" & nUserID & " ORDER BY ID Desc"
  Log "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
        Log("YES, Answer on Question " & arrQuestions(iQuestion) & " = " & nAnswerID & _
          " (" & strFormalAnswerString & ")")
        nPositive = nPositive + 1
      Else
        Log("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
  
  Log("<< GetScore, result: " &  GetScore)
End Function

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

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

  Log ">> GetAnswer( " & nQuestionID & ")"

  nFormalAnswerID       = 0
  strFormalAnswerString = ""
        
  strQuery   = "SELECT * FROM TriviaQuestions WHERE ID=" & nQuestionID     
  Log "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
    
    Log "nAnswerID: " & nFormalAnswerID
    Log "strFormalAnswerString: " & strFormalAnswerString
    
    GetAnswer = 0
  End If
   
  Log "<< GetAnswer, result: " &  GetAnswer
End Function

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

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

  Dim strUAnswer, nQuestionID, nAnswer, nFormalAnswerID, strFormalAnswerString
  
  Log ">> ProcessAnswer"
  
  nAnswer               = 0
  nQuestionID           = 0
  ProcessAnswer         = 0
  nFormalAnswerID       = 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.ACE.OLEDB.12.0;Data Source=" & STR_TRIVIADBFILE & ";"
  
  ' Find out which answer it was
  strQuery = "SELECT * FROM TriviaUsers WHERE ID=" & nUserID
  Log "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
    Log "Execute: [" & strUpdate & "]..."
    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

  Log "<< ProcessAnswer, result: " &  ProcessAnswer
  
End Function

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

Function GetNextQuestion(nUserID, BYREF strNextQuestion, BYREF bNoMoreQuestions, BYREF strError) 
  Dim objConn, RS, strQuery, nSeq, nQuestionID
 
  GetNextQuestion   = -1
  strNextQuestion   = ""
  bNoMoreQuestions  = False
  strError          = ""
  
  nSeq              = 0
  nQuestionID       = 0

  Set objConn = CreateObject("ADODB.Connection")
  objConn.Open "Provider=Microsoft.ACE.OLEDB.12.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"
  Log "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
  Log "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

  Log "<< GetNextQuestion, result: " &  GetNextQuestion
End Function

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

Function ReplyMessage(objMessageDB, objMessageIn, strResponse)

   Log ">> ReplyMessage"

   Set objMessageOut = objMessageDB.Create("SMS")
   objMessageOut.StatusID     = g_objConstants.MESSAGESTATUS_SCHEDULED
   objMessageOut.ToAddress    = objMessageIn.FromAddress
   objMessageOut.ChannelID    = objMessageIn.ChannelID
   objMessageOut.Body         = strResponse
   
   objMessageDB.Save objMessageOut

   Log "<< ReplyMessage"

End Function

The final test

Finally, after setting up the script file, we can test the entire solution.

To start we’ll need to send the text ‘Trivia’ the SMS Server. From there we just need to follow the prompts.

Hot Radio – Messages

At the end of the trivia quiz you should have an exchange like the one in the screenshot above.

How can we help?