Hot Radio SMS trivia quiz – A case study

Introduction

In this case study we use Auron Omni to set up a small SMS trivia example project.

The trivia project was originally set up for the Hot Radio radio station to engage listeners. If a listener answers three questions successfully, they receive a free coupon.

However, a short trivia quiz like this is useful in many more situations, and not only for entertainment. We have since applied it to:

• Increase engagement in radio shows
• Study knowledge on specific subjects in focus groups related to disease prevention
• Check in automatically with elderly patients to ensure they take their medication correctly
• Help citizens get in contact with emergency responders during a natural disaster
• Etc.

All of these situations require a dialogue with a mobile user. Sometimes the mobile user initiates the conversation and sometimes the system initiates it.

Use case

In this case we focus on a straightforward trivia example that can serve as a template for the use cases above.

HotRadio Trivia

The Hot Radio DJs let listeners know they can send the text “Trivia” to start a game. Winners receive a coupon and may be invited to join live on air.

Prerequisites

• The Auron Omni
• A SQL Server Express installation or the built in LocalDB database
• A GSM modem, SMPP subscription or the built in demonstration gateway

If you do not currently have a way to send SMS messages, you can test this using the built in SMPP client settings.

Installation

The use case starts with the installation of Auron Omni. Refer to the knowledge base for system requirements and installation instructions.

You can use the built in LocalDB database to get started right away. Before going live, it is recommended to install SQL Server Express. Make sure to install the free SQL Server Management Studio for easy database access.

Right after installation, configure an SMS channel. This channel is used to send and receive SMS messages.

Database

The Hot Radio database is a simple Microsoft Access mdb file. This approach makes it easy for non technical users to create or edit quiz content.

Another advantage is that changes to the trivia database are applied immediately.

Two example databases are:

• Music Trivia
• World Cup Football Trivia

Trivia SMS trigger

In this case the trivia SMS trigger is a VBScript trigger. You can find more information on how to set up an empty VBScript trigger here.

The trigger should match any SMS with StatusID Received.

After creating the trigger, edit the script and update it to the following:

CONST STR_DEBUGFILE     = "C:\ProgramData\Auron\Auron Omni\Log\TRIVIA.txt"
CONST STR_TRIVIADBFILE  = "C:\ProgramData\Auron\Auron Omni\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

After setting up the script file, you can test the full solution.

Start by sending the text “Trivia” to Auron Omni. From there follow the prompts.

Hot Radio Messages

At the end of the trivia quiz you should see an exchange similar to the screenshot above.