ActiveXperts

---

Send, receive and automate SMS messages

---

SMS Music Trivia system (Hotradio)

• 1. Background
• 2. Problem Statement
• 3. Goals of the new System
• 4. ActiveXperts SMS Messaging Server Solution

1. Background

A famous radio station wants to have a new game for its listeners where people can win games. During the whole day the radio station will provide some hints regarding the songs they are playing at that moment. In the afternoon during a specified amount of time users can answer some questions.

2. Problem Statement

In the current situation the speaker has to read out the questions and listeners sometimes complaine that they haven't understood the question. To solve these problems the radio station wants to send questions through SMS.

3. Goals of the new System

The goals of the system is to allow users to register to the game. Send random questions to the registered users. Check if the answer the users provide is the correct one for the related question that was sent before.

4. ActiveXperts SMS Messaging Server Solution

Interactive cell phone trivia game that allows anyone with a cell phone to join this music 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.

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


Option Explicit

CONST STR_TRIVIADBFILE          = "C:\Program Files\ActiveXperts\SMS Messaging Server\Projects\Trivia Demo (Music)\Database\Trivia.mdb"
CONST STR_DEBUGFILE             = "C:\Program Files\ActiveXperts\SMS Messaging Server\Sys\Tmp\Trivia Demo (Music).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 Music SMS Trivia! "
  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

Footer Navigation

• © 2012 ActiveXperts Software B.V. All rights reserved.
• Site Map
• Contact Us
• Terms of Use
• Privacy Policy