ActiveXperts

---

Send, receive and automate SMS messages

---

SMS Music Voting System (Hotradio)

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

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

1. Background

HotRadio is a non-commercial radio station. It has been broadcasting since 1998 and plays a wide variety of popular music. It is on air 24 hours a day, 7 days a week on the FM frequency and on the Internet. HotRadio has a wide variety of programs, from non-stop music programs to news programs.

This case study discusses two popular services available with HotRadio

• We Play Your Music This is a program where listeners can request their favorite song.
• Play and Win This is a contest where users can participate and win prices.

2. Problem Statement

To run both services there's a panel of two people who accept calls from listeners who either want to request a song or want to play the game and try to win a price.

This system has some drawbacks:

• It requires two full time panel members.
• Phone Lines are often engaged.
• It is very time consuming, an average calls takes approx. 1.5 minute;
• All requests are entered manually by the panel into a database. This database is accessed by the DJ's.

3. Goals of the new System

The current phone system must be replaced by a new, more efficient system. Goals of the new system:

• Listeners can only contact HotRadio by SMS message.
• There should be two SMS numbers; one for the 'We Play Your Music' program and one for the 'Play and Win' item.
• The system should be 100% automatic, without a need for a phone panel.
• Requests for songs 'We Play Your Music' should be registered in a database automatically.
• Answers to the 'Play and Win' questions should be registered in a separate database, automatically.
• The system should reply to any incoming message, telling the listener of the radio station that their message was received and processed.
• All SMS requests should be logged, including SMS number of the sender and date/time of the request;

4. ActiveXperts SMS Messaging Server Solution

The HotRadio team decided to replace their current system where the activities of both panel members where completely automated.

HotRadio reserved two numbers for SMS messages, for this example we will use: +31638740160 and +31638740161. Request for songs can be sent to +31638740160. Listeners can send 'Play and Win' answers to +31638740161.

On the 'Play and Win' number, any SMS message is accepted, because the answer to the question can be anything.

On 'We Play Your Music', the SMS message must match a predefined message format. If listeners do not know the message format, they can send a help command ('?') to ask for the message syntax. The system will reply and tell the listener what message syntax to use.

SMS message format

The system handles SMS messages as follows:

SMS Number	SMS message body (syntax)	Sample	Description
+31638740160  ('We Play Your Music')	Vote [1-1000]	Vote 114	Vote for a song. In this sample, the listener sends a request for song 114
	?	?	Help command. A Help message is returned to the listener
	<blanc>		Unrecognized command. A Help message is returned to the listener
+31638740161  ('Play and Win')	<any message>	This is my answer to the question on Play and Win	Answer to the question of the Play and Win item
	<blanc>		Blanc answers are not accepted. A Help message is returned to the listener

Hardware and Software

The system consists of the following:

• A single Windows 2003 Server
• The systems runs the ActiveXperts SMS Messaging Server as well as the HotRadio Songs databases
• Two WaveCom Fastrack GSM Modems to handle all incoming/outgoing request and replies. One modem is dedicated for the 'We Play Your Music' program (+31638740160), the other modem is dedicated for the 'Play and Win' item (+31638740161).

HotRadio had to choose out of two SMS communication techniques: SMPP or GSM modem. Since SMPP is designed for high volume SMS messaging (hundreds of SMS messages per hour) this would be a lot more than what they need. The GSM modem throughput (10 messages per minute) is sufficient and more economical for this solution. To have both phone numbers available they needed to purchase two modems which they needed to fit with two SIM cards with corresponding SMS bundles.

HotRadio databases

There are two databases involved:

• WePlayYourMusic.mdb
• PlayAndWin.mdb

WePlayYourMusic, SongTitles table:

• 'ID' This is the actual ID of the song that listeners use in an SMS request to select a song.
• 'Artist' Artist or group of the song.
• 'Title' Title of the song.

WePlayYourMusic, SongRequests table:

• 'ID' field Unique ID (autonumber).
• 'SmsNumber' SMS number of the listener that sent the request.
• 'DateVal' Date of request.
• 'TimeVal' Time of request.
• 'SongID' The song that was selected by the listener. It refers to the ID field in the SongTitles database.

Triggers

A Trigger is called when a new messages arrives in the system. HotRadio only accepts messages via SMS. Since HotRadio uses two different incoming SMS numbers with two different SMS number, two triggers are defined. One trigger for incoming messages on +31638740160 'We Play Your Music' and one trigger for incoming messages on +31638740160 'Play and Win'.

Enabled	Description	Condition	Script
YES	Process incoming song requests	To = '+31638740160'	\Projects\HotRadio Radio Station\Triggers\WePlayYourMusic.vbs
YES	Process incoming answers	To = '+31638740161'	\Projects\HotRadio Radio Station\Triggers\PlayAndWin.vbs

Each trigger will be called for every new incoming message, as long as the condition is matched.

The WePlayYourMusic.vbs trigger does the following in pseudo code:

  - Set incoming message to 'processed', so it will not be processed again by the system
  - If message is empty                       => send an SMS reply to the listener that message 
                                                 is not accepted
  - If message is a valid song request        => Update the SongRequests table in the 
                                                 WePlayYourMusic.mdb, send an SMS reply to the 
                                                 listener with the number of votes for that song 
                                                 and total number of votes
  - If message is not a valid song request    => send an SMS reply to the listener that message 
                                                 is not accepted

The PlayAndWin.vbs trigger does the following:

  - Set incoming message to 'processed', so it will not be processed again by the system
  - If message is empty                       => send an SMS reply to the listener that answer 
                                                 is not accepted
  - Any other message                         => Update the Answers table in the PlayAndWin.mdb 
                                                 database, send an SMS reply to the listener 
                                                 that answer has been processed.
  - If message is not a valid song request    => send an SMS reply to the listener that message 
                                                 is not accepted

WePlayYourMusic.vbs (full code)

 Option Explicit

 CONST STR_WEPLAYYOURMUSICDBFILE = "Projects\HotRadio Radio Station\Database\WePlayYourMusic.mdb"
 CONST STR_DEBUGFILE             = "Tmp\WePlayYourMusic.txt"

 CONST STR_USAGE                 = "To send a request, type: Vote [1-1000]"

 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
 g_objDebugger.DebugFile = STR_DEBUGFILE


 ' // ========================================================================
 ' // ProcessMessage
 ' // ------------------------------------------------------------------------
 ' // ProcessMessage trigger function
 ' // ========================================================================

 Function ProcessMessage( numMessageID )
   Dim objMessageIn, objMessageOut, arrMessage
   Dim strMessageOutBody
   Dim numSongID, strCommand

   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,  unable to find message"
      Exit Function
   End If

   ' Set incoming SMS message status to: SUCCESS (previous state was: PENDING)
   objMessageIn.Status = g_objConstants.MESSAGESTATUS_SUCCESS
   g_objMessageDB.Save objMessageIn
   g_objDebugger.WriteLine " Incoming message saved, result: [" & g_objMessageDB.LastError & "]"

   ' Split received message body into pieces (separated by spaces)
   arrMessage = Split( UCase( objMessageIn.Body ), " " )

   If( UBound( arrMessage ) = 0 ) Then
      ' Just 1 param, so ? is expected. But on any other single word, also show help
      strMessageOutBody = ShowHelp()
   ElseIf( UBound( arrMessage ) = 1 ) Then
      strCommand = GetCommand( arrMessage(0) ) ' Converts any word starting with v or V to V
      numSongID  = GetSongID( arrMessage(1) )  ' Converts string to number, if possible.

      If( strCommand <> "V" ) Then
         strMessageOutBody = ShowWrongParam( arrMessage(0) )
      ElseIf( numSongID = 0 ) Then
         strMessageOutBody = ShowWrongParam( arrMessage(1) )
      Else
         strMessageOutBody = UpdateSongRequests( objMessageIn.Sender, numSongID )
      End If
   Else
      strMessageOutBody = ShowSyntaxError()
   End If

   ' Create a new reply message
   Set objMessageOut = g_objMessageDB.Create
   g_objDebugger.WriteLine " New message created, result: [" & g_objMessageDB.LastError & "]"
   If( g_objMessageDB.LastError = 0 ) Then
      objMessageOut.Direction = g_objConstants.MESSAGEDIRECTION_OUT
      objMessageOut.Type      = g_objConstants.MESSAGETYPE_SMS
      objMessageOut.Status    = g_objConstants.MESSAGESTATUS_PENDING
      objMessageOut.To        = objMessageIn.From 
      objMessageOut.ChannelID = objMessageIn.ChannelID  ' Use the incoming message channel
      objMessageOut.Body      = strMessageOutBody
      g_objMessageDB.Save objMessageOut
      g_objDebugger.WriteLine " New message saved, result: [" & g_objMessageDB.LastError & "]"
   End If

   g_objDebugger.WriteLine "<< ProcessMessage"
 End Function


 ' // ========================================================================
 ' // UpdateSongRequests
 ' // ------------------------------------------------------------------------
 ' // Update the SongRequests table with the request that was just received
 ' // ========================================================================
 Function UpdateSongRequests( strSenderNumber, numSongID )
 On Error Resume Next
   Dim objConn, strQuery, RS, hexError, strResult
   Dim cnt1, cnt2
   Dim strSong

   g_objDebugger.WriteLine( ">> UpdateSongRequests( " & strSenderNumber & "," & numSongID & ")" )

   ' Create database object; open the database
   Set objConn = CreateObject("ADODB.Connection")
   objConn.Open "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & STR_WEPLAYYOURMUSICDBFILE & ";"

   ' Retrieve Song title, to display it later in the reply
   ' If it does not exist then return with an error
   strQuery = "SELECT * FROM SongTitles WHERE ID=" & numSongID
   Err.Clear ' Clear a previous error
   Set RS = objConn.Execute( strQuery )
   hexError = Hex( Err.Number )
   If( hexError <> "0" ) Then
      g_objDebugger.WriteLine( "<< UpdateSongRequests, Err.Number: " & Err.Number )
      UpdateSongRequests = "Request could not be processed (database error #" & _
        hexError & " occured ). "
      objConn.Close
      Set objConn = Nothing
      Exit Function
   End If
   If( RS.EOF ) Then
      g_objDebugger.WriteLine( "<< UpdateSongRequests, song does not exist" )
      UpdateSongRequests = "Request could not be processed (song #" & _
        numSongID & " does not exist). "
      objConn.Close
      Set objConn = Nothing
      Exit Function
   End If
   strSong = RS( "Artist" ) & "-" & RS( "Title" )
   strSong = Trim( strSong )

   ' Execute INSERT statement
   strQuery = "INSERT INTO SongRequests( SmsNumber, DateVal, TimeVal, SongID ) VALUES ( '" & _
              strSenderNumber & "', '" & Date() & "', '" & Time() & "', " & numSongID & " )"
   Err.Clear ' Clear a previous error
   objConn.Execute( strQuery )

   ' Catch error - if any
   hexError = Hex( Err.Number )
   If( hexError <> "0" ) Then
      g_objDebugger.WriteLine( "Err.Number: " & Err.Number & "; Err.Description: " & _
        Err.Description )
      UpdateSongRequests = "Request could not be processed. "
      objConn.Close
      Set objConn = Nothing
      Exit Function
   End If
   strResult = "Request successfully processed. "

   ' Now, calculate #request for this particular song (cnt1) and totla requests(cnt2). 
   ' If both can be calculated then append it to the reponse
   cnt1 = 0
   cnt2 = 0
   strQuery = "Select Count(*) As cnt1 FROM SongRequests WHERE SongID = " & numSongID
   g_objDebugger.WriteLine( strQuery )
   Set RS = objConn.Execute( strQuery )
   hexError = Hex( Err.Number )
   If( hexError = "0" ) Then
      cnt1 = RS( "cnt1" )

      strQuery = "Select Count(*) As cnt2 FROM SongRequests"
      Set RS = objConn.Execute( strQuery )

      hexError = Hex( Err.Number )
      If( hexError = "0" ) Then
         cnt2 = RS( "cnt2" )
         strResult = strResult & "#Votes for [" & strSong & "]: " & cnt1 & "; #Total Votes: " & _
          cnt2
      End If
   End If

   objConn.Close
   Set objConn = Nothing

   UpdateSongRequests = strResult

   g_objDebugger.WriteLine( "<< UpdateSongRequests, result: " & UpdateSongRequests )

 End Function


 ' // ========================================================================
 ' // Show... functions
 ' // ------------------------------------------------------------------------
 ' // Some error handling Show errors
 ' // ========================================================================

 Function ShowWrongParam( strParam )
   ShowWrongParam = "Wrong parameter: " & strParam
 End Function

 Function ShowWrongCommand( strCommand )
   ShowWrongCommand = "Wrong command: " & strCommand
 End Function

 Function ShowSyntaxError()
   ShowSyntaxError = "Syntax error. " & STR_USAGE
 End Function

 Function ShowHelp()
   ShowHelp = STR_USAGE
 End Function

  
 ' // ========================================================================
 ' // GetCommand
 ' // ------------------------------------------------------------------------
 ' // Get the command. The only supported command is: Vote (or 'V' or 'V', or 
 ' // anything else  that starts with 'v'
 ' // ========================================================================

 Function GetCommand( strCommand )
   Dim strUCommand

   g_objDebugger.WriteLine( ">> GetCommand ( " & strCommand & " )" )

   GetCommand = ""

   strUCommand = UCase( strCommand )
   If( Left( strUCommand, 1 ) <> "V" ) Then
      GetCommand = ""
      g_objDebugger.WriteLine( "<< GetCommand, first character is not a 'v' or 'V'" )
      Exit Function
   End If

   GetCommand = "V"

   g_objDebugger.WriteLine( "<< GetCommand, return: V" )

 End Function


 ' // ========================================================================
 ' // GetSongID
 ' // ------------------------------------------------------------------------
 ' // Function returns the song ID. 
 ' // If paramter cannot be ocnverted to a number, or if number not in
 ' // range [1..1000] then 0 is returned
 ' // ========================================================================

 Function GetSongID( strSongID )
 On Error Resume Next ' Necessary to catch error because of a non-numeric value

   Dim numSongID

   g_objDebugger.WriteLine( ">> GetSongID ( " & strSongID & " )" )

   numSongID = 0 ' do this because next call may fail upon non-integer
   numSongID = CInt( strSongID )
   If( numSongID = 0 ) Then
      GetSongID = 0
      Exit Function
   End If

   If( numSongID < 1 Or numSongID > 1000 ) Then
      GetSongID = 0
      Exit Function
   End If
   
   GetSongID = numSongID

   g_objDebugger.WriteLine( "<< GetSongID, return: " & numSongID )

 End Function

PlayAndWin.vbs (full code)

 ' // ========================================================================
 ' // PlayAndWin.vbs
 ' // ------------------------------------------------------------------------
 ' // HotRadio Case Study
 ' // For more details, go to www.activexperts.com/sms-messaging-server/casestudies/hotradio
 ' // ========================================================================

 Option Explicit

 CONST STR_PLAYANDWINDBFILE    = "Projects\HotRadio Radio Station\Database\PlayAndWin.mdb"
 CONST STR_DEBUGFILE           = "Tmp\PlayAndWin.txt"

 CONST STR_USAGE               = "Type the answer of the question in the body of the message"

 Dim g_objMessageDB, g_objConstants, g_objDebugger

 ' 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
 g_objDebugger.DebugFile = STR_DEBUGFILE



 ' // ========================================================================
 ' // ProcessMessage
 ' // ------------------------------------------------------------------------
 ' // ProcessMessage trigger function
 ' // ========================================================================

 Function ProcessMessage( numMessageID )
   Dim strMessageOutBody, objMessageIn, objMessageOut
   Dim numSongID, strCommand

   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,  unable to find record"
      Exit Function
   End If 

   ' Set incoming SMS message status to: SUCCESS (previous state was: PENDING)
   objMessageIn.Status = g_objConstants.MESSAGESTATUS_SUCCESS
   g_objMessageDB.Save objMessageIn
   g_objDebugger.WriteLine "Incoming message saved, result: [" & g_objMessageDB.LastError & "]"

   If( Len( Trim( objMessageIn.Body ) ) <= 0 ) Then
      strMessageOutBody = ShowHelp()
   Else
      strMessageOutBody = AddAnswer( objMessageIn.Sender, objMessageIn.Body )
   End If

  ' Create a new reply message
  Set objMessageOut = g_objMessageDB.Create
  g_objDebugger.WriteLine "New message created, result: [" & g_objMessageDB.LastError & "]"
  If( g_objMessageDB.LastError = 0 ) Then
     objMessageOut.Direction = g_objConstants.MESSAGEDIRECTION_OUT
     objMessageOut.Type      = g_objConstants.MESSAGETYPE_SMS
     objMessageOut.Status    = g_objConstants.MESSAGESTATUS_PENDING
     objMessageOut.To        = objMessageIn.From 
     objMessageOut.ChannelID = objMessageIn.ChannelID  ' Use the incoming message channel.
     objMessageOut.Body      = strMessageOutBody
     g_objMessageDB.Save objMessageOut
     g_objDebugger.WriteLine " New message saved, result: [" & g_objMessageDB.LastError & "]"
  End If

  ' Close the Database
  g_objMessageDB.Close

  g_objDebugger.WriteLine "<< ProcessMessage"
 End Function



 ' // ========================================================================
 ' // AddAnswer
 ' // ------------------------------------------------------------------------
 ' // Adds the answer to the PlayAndWin.mdb-Answers table
 ' // ========================================================================
 Function AddAnswer( strSenderNumber, strAnswer )
 On Error Resume Next
   Dim objConn, strQuery, RS, hexError

   g_objDebugger.WriteLine( ">> AddAnswer( " & strSenderNumber & "," & strAnswer & ")" )

   ' Create database object; open the database
   Set objConn = CreateObject("ADODB.Connection")
   objConn.Open "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & STR_PLAYANDWINDBFILE & ";"

   ' Execute INSERT statement
   strQuery = "INSERT INTO Answers( SmsNumber, DateVal, TimeVal, Answer ) VALUES ( '" & _
              strSenderNumber & "', '" & Date() & "', '" & Time() & "', '" & strAnswer & "' )"
   Err.Clear ' Clear a previous error
   objConn.Execute( strQuery )

   ' Catch error - if any
   hexError = Hex( Err.Number )
   If( hexError <> "0" ) Then
      g_objDebugger.WriteLine( "Err.Number: " & Err.Number & "; Err.Description: " & _
            Err.Description )
      AddAnswer = "Answer could not be processed. "
      objConn.Close
      Set objConn = Nothing
      Exit Function
   End If
   AddAnswer = "Answer successfully processed."

   objConn.Close
   Set objConn = Nothing

   g_objDebugger.WriteLine( "<< AddAnswer, result: " & AddAnswer)

 End Function


 ' // ========================================================================
 ' // Show... functions
 ' // ------------------------------------------------------------------------
 ' // Some error handling Show errors
 ' // ========================================================================

 Function ShowHelp()
   ShowHelp = STR_USAGE
 End Function

Footer Navigation

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