ActiveXperts

---

Send, receive and automate SMS messages

---

Case Study: Bar Dancing Breakaway

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

1. Background

Breakaway is a small Bar Dancing, with around 3000 people visiting every weekend. The Dancing is opened every friday (08pm-03am), saturday (08pm-03am) and sunday (07pm-01am). The audience age varies from 18 to 40 years.

The Bar/Dancing frequenctly organizes special events, like karaoke, song contests, cover bands, etc.

NOTE: A demo of this Case Study is included with the ActiveXperts SMS Messaging Server installation.

2. Problem Statement

The Breakaway Bar wants to notify their regular visitors for upcoming special events.

At this moment, the bar uses billboards to attend people that there's someting going on, but they're looking for a solution

This manual system has some drawbacks:

• Members are complaining that they need to go to the club to make a reservation; The alternative way - by phone - doesn't work well because the phone is often engaged, or club employees are busy;
• Cancelling a reservation is as time consuming as making a new reservation. As a result, the squash courts are very often booked while members don't actually show up.
• Some people make unauthorized changes on the planning board: they remove somebody else's label in order to make their own reservation.

3. Goals of the new System

The current planning board must be replaced by a new, better system. Goals of the new system:

• Reservations must be done primary by SMS message request;
• The system must always respond to a request, to inform the member about the result of a request;
• Members must receive a positive/negative acknowledgement after their reservation request;
• Members must be able to query their reservations, so they get an overview of all reservations they made;
• Members must be able to cancel a reservation;
• Members already have a unique TruLife Member-ID. An SMS Mobile Number will be associated with the Member-ID number;
• The sender's Mobile Number will be used to authenticate the member to the system.

4. ActiveXperts SMS Messaging Server Solution

ActiveXperts SMS Messaging Server will replace the manual squash courts reservation handling.

Members can send an SMS message to the central SMS Messaging Server. They can send reservation requests to a dedicated mobile number, associated with the SMS Messaging Server's reservation system.

The system only accepts messages that match a pre-defined message syntax. This messages format must be known by the members. If they 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 member what message syntax to use.

SMS message formats

The system is designed to accept the following SMS messages:

SMS message body (syntax)	Sample	Explanation
?	?	Help command. Member will get a reply about how to create or cancel a reservation
Q	Q	Query command. The member will receive an SMS message containing all reservations for the next 7 days
R [su|mo|..|sa] [7am|8am|..|9pm]	R tu 11am	Reservation command. The member will receive an SMS message containing information about the reservation: whether is was accepted or not, and the court number In the sample, a reservation is requested for next Tuesday, 11am.
C [su|mo|..|sa] [7am|8am|..|9pm]	C tu 11am	Cancel command. The member will receive an SMS message with cancel confirmation information In the sample, the reservation for next Tuesday is cancelled
<any other message>	Hello everybody	Any message that does not match the pre-defined syntax will be replied with an SMS message that tells how to use the system

Hardware and Software

The system consists of the following:

• A single server (Dell Dimension 2400 with 512 MB RAM, 80 GB Hard Drive, P4 processor, running Windows 2003 Server)
• The systems runs ActiveXperts SMS Messaging Server 4.0, with a TruLife MS Access database on the same computer
• A WaveCom Fastrack 1306B GSM Modem to handle all incoming/outgoing reservation request and replies

Basically, TruLife had to choose out of two SMS communication techniques: SMPP or GSM modem. Since SMPP is basically designed for high volume SMS messaging (hundreds of SMS messages per hour), it would be overkill for TruLife to use SMPP. The GSM modem throughput (10 messages per minute) is sufficient for this solution. So, they went for a GSM Modem, and purchased a WaveCom GSM Modem, model: 1306B. They also purchased a SIM card with an SMS bundle on it. This SIM card is inserted into the WaveCom GSM modem.
The SIM card is associated with the following number: +3162234329

TruLife database

Reservations are registered in a separate database. The TruLife company decided to choose an MS Access database for it. This solution is scalable; at any time, a migration to MS SQL can be done.

The database consists of two tables:

• Members - Each record in this table describes a unique club member. A record includes a unique membership ID and the member's mobile number. The mobile number will be used by SMS Messaging Server to authenticate a member when making a reservation;
• Reservations - Each record describes a reservation for a squash court. A record includes the member's ID, date and time of reservation, and the court number.

Triggers

To make a reservation, members can send an SMS message to +3162234329. This is the SIM card that is inserted into the GSM Modem which is connected to the SMS Messaging Server. To let SMS Messaging Server process all incoming requests on +3162234329, the following trigger is defined:

Enabled	Description	Condition	Script
YES	Process incoming reservation requests	To = '+3162234329'	Projects\TruLife Health Club\Triggers\TruLife.vbs

The trigger will be called for every new incoming message. Basically, the trigger will do the following:

  - Set incoming message to 'processed', so it will not be processed again by the system
  - Look up TruLife member-ID by incoming SMS number. 
  - Sender's mobile number is not registered ? => send a reply SMS to the sender that he/she is not a member
  - Verify syntax of message. If syntax is not OK => send a reply SMS to the sender how to make a reservation
  - If message is a Query request       => send a reply SMS to the sender with all reservations of the next 7 days
  - If message is a Cancel request      => Cancel the reservation (if possible), send a reply SMS 
                                           with the result of the cancel request
  - If message is a Reservation request => Make the reservation (if possible), send a reply SMS with 
                                           the result of the reservation request
 

Pseudo code of Trigger.vbs

  
' Set incoming SMS message status to: Processed
objMessageIn.Status = objConstants.MESSAGESTATUS_IN_PROCESSED
objMessageIn.Save

' Retrieve TruLife's MemberID that sent the message.
numMemberID = CheckMember( objMessageIn.Sender )

' Process the message
If( numMemberID <= 0 ) Then
   strMessageOutBody = ShowUnauthorizedMember()
ElseIf( objMessageIn.Body consists of only one word ) Then
   ' Just 1 param, so ? or Q expected
   If( objMessageIn.Body = "?" ) Then
      strMessageOutBody = ShowHelp()
   ElseIf( objMessageIn.Body = "Q" ) Then
      strMessageOutBody = QueryReservations( numMemberID )
   Else
      strMessageOutBody = ShowSyntaxError()
   End If
ElseIf( objMessageIn.Body consists of 3 words  ) Then

   Calculate Date of reservation
   Calculate Time of reservation

   If( Date of reservation could not be calculated ) Then
      strMessageOutBody = ShowWrongParam( .. )
   ElseIf( Time of reservation could not be calculated ) Then
      strMessageOutBody = ShowWrongParam( arrMessage(2) )
   ElseIf( objMessageIn.Body's first word is 'R' ) Then
      strMessageOutBody = MakeReservation( numMemberID, date, time )
   ElseIf( objMessageIn.Body's first word is 'C' ) Then
      strMessageOutBody = CancelReservation( numMemberID, date, time )
   Else
      strMessageOutBody = ShowWrongCommand( ... )
   End If
Else
   strMessageOutBody = ShowSyntaxError()
End If

' Create a new reply message
Set objMessageOut = objMessageDB.Create
objMessageOut.Direction = objConstants.MESSAGEDIRECTION_OUT
objMessageOut.To        = objMessageIn.From 
objMessageOut.Body      = strMessageOutBody
objMessageOut.Save

Trigger.vbs (full code)

Option Explicit

CONST STR_TRULIFEDBFILE = "C:\Projects\TruLife Health Club\TruLife.mdb"
CONST STR_DEBUGFILE     = "C:\Tmp\TruLife.txt"

CONST STR_USAGE         = "To query your reservations, type: Q  To make a new reservation, " & _
                          " type: R [su|mo|..|sa] [7am|8am|..|9pm]"

Dim objMessageDB, objConstants, objMessageIn, objMessageOut, objDebugger, arrMessage
Dim strMessageOutBody
Dim numMemberID, wDay, dtDate, dtTime

Set objConstants  = CreateObject( "AxMmServer.Constants" )
Set objMessageDB  = CreateObject( "AxMmServer.Messages" ) 
Set objDebugger   = CreateObject( "ActiveXperts.VbDebugger" )

objDebugger.DebugFile = STR_DEBUGFILE
objDebugger.WriteLine ">> TruLife.vbs"

' Retrieve the message that has just been received. If it fails then exit script 
Set objMessageIn   = objMessageDB.FindFirstMessage( "ID = " & %MESSAGE_ID% ) 
If objMessageDB.LastError <> 0 Then
   WScript.Quit
End If

' Set incoming SMS message status to: Processed
objMessageIn.Status = objConstants.MESSAGESTATUS_IN_PROCESSED
objMessageIn.Save
objDebugger.WriteLine " Incoming message saved, result: [" & objMessageIn.LastError & "]"

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

' Retrieve TruLife's MemberID that sent the message.
numMemberID = CheckMember( objMessageIn.Sender )

' Process the message
If( numMemberID <= 0 ) Then
   strMessageOutBody = ShowUnauthorizedMember()
ElseIf( UBound( arrMessage ) = 0 ) Then
   ' Just 1 param, so ? or Q expected
   If( arrMessage(0) = "?" ) Then
      strMessageOutBody = ShowHelp()
   ElseIf( Left( UCase( arrMessage(0) ), 1 ) = "Q" ) Then
      strMessageOutBody = QueryReservations( numMemberID )
   Else
      strMessageOutBody = ShowSyntaxError()
   End If
ElseIf( UBound( arrMessage ) = 2 ) Then

   wDay   = GetWDay( arrMessage(1) )
   dtDate = NextDate( wDay )
   dtTime = GetTimeValue( arrMessage(2) )

   If( wDay < 0 Or dtDate = "" ) Then
      strMessageOutBody = ShowWrongParam( arrMessage(1) )
   ElseIf( dtTime = "" ) Then
      strMessageOutBody = ShowWrongParam( arrMessage(2) )
   ElseIf( Left( UCase( arrMessage(0) ), 1 ) = "R" ) Then
      strMessageOutBody = MakeReservation( numMemberID, dtDate, dtTime )
   ElseIf( Left( UCase( arrMessage(0) ), 1 ) = "C" ) Then
      strMessageOutBody = CancelReservation( numMemberID, dtDate, dtTime )
   Else
      strMessageOutBody = ShowWrongCommand( arrMessage(0) )
   End If
Else
   strMessageOutBody = ShowSyntaxError()
End If

' Create a new reply message
Set objMessageOut = objMessageDB.Create
objDebugger.WriteLine " New message created, result: [" & objMessageDB.LastError & "]"
If( objMessageDB.LastError = 0 ) Then
   objMessageOut.Direction = objConstants.MESSAGEDIRECTION_OUT
   objMessageOut.Type      = objConstants.MESSAGETYPE_SMS
   objMessageOut.Status    = objConstants.MESSAGESTATUS_OUT_SCHEDULED
   objMessageOut.To        = objMessageIn.From 
   objMessageOut.ChannelID = 0  ' Any available SMS channel
   objMessageOut.Body      = strMessageOutBody
   objMessageOut.Save
   objDebugger.WriteLine " New message saved, result: [" & objMessageOut.LastError & "]"
End If

objDebugger.WriteLine "<< TruLife.vbs"


' // ========================================================================
' // CheckMember
' // ------------------------------------------------------------------------
' // Check if a mobile number is in the Members database to confirm that the
' // person is a member
' // ========================================================================

Function CheckMember( strInputNumber )
   Dim objConn, RS, strQuery

   objDebugger.WriteLine( ">> CheckMember" )

   Set objConn = CreateObject("ADODB.Connection")
   objConn.Open "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & STR_TRULIFEDBFILE & ";"

   Set RS = objConn.Execute( "SELECT * FROM Members WHERE MobileNumber='" & strInputNumber & "'"  )
   If RS.EOF Then
     CheckMember = 0
   Else
     CheckMember = RS( "ID" )
   End If

   objConn.Close
   Set objConn = Nothing

   objDebugger.WriteLine( "<< CheckMember, result: " &  CheckMember )

End Function


' // ========================================================================
' // MakeReservation
' // ------------------------------------------------------------------------
' // Check if a mobile number is in the Members database to confirm that the
' // person is a member
' // ========================================================================
Function MakeReservation( numMember, dtDate, dtTime )
On Error Resume Next
   Dim objConn, iCourt, strQuery, hexError

   objDebugger.WriteLine( ">> MakeReservation( " & numMember & "," & dtDate & "," & dtTime & ")" )

   If( dtDate < Date() OR ( dtDate = Date() AND dtTime < Time() ) ) Then
      MakeReservation = "You tried to make a reservation in history. Please try again."
      objDebugger.WriteLine( "<< MakeReservation, reservation in history not allowed" )
      Exit Function 
   End If

   Set objConn = CreateObject("ADODB.Connection")
   objConn.Open "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & STR_TRULIFEDBFILE & ";"

   For iCourt = 1 To 2
      objDebugger.WriteLine( "Try court " & iCourt & " ..." )

      strQuery = "INSERT INTO Reservations ( ReservationDate, ReservationTime, CourtID, MemberID ) " & _
	             " VALUES ( '" & dtDate & "', '" & dtTime & "', " & iCourt & ", " & numMember & " )"
     
      Err.Clear ' Clear a previous error
      objConn.Execute( strQuery )

      hexError = Hex( Err.Number )
      If( hexError <> "80040E14" ) Then 
         Exit For
      End If 
   Next

   If( hexError = "80040E14" ) Then
      objDebugger.WriteLine( "Err.Number: " & Err.Number & "; Err.Description: " & Err.Description )
      MakeReservation = "All courts are booked at the indicated date and time. Reservation failed."
   ElseIf( hexError <> "0" ) Then
      objDebugger.WriteLine( "Err.Number: " & Err.Number & "; Err.Description: " & Err.Description )
      MakeReservation = "Reservation could not be processed."
   Else
      MakeReservation = "Court " & iCourt & " is successfully reserved for you on " & dtDate & " at " & dtTime
   End If

   objConn.Close
   Set objConn = Nothing

   objDebugger.WriteLine( "<< MakeReservation, result: " & MakeReservation )

End Function


' // ========================================================================
' // CancelReservation
' // ------------------------------------------------------------------------
' // Cancel a reservation
' // ========================================================================
Function CancelReservation( numMember, dtDate, dtTime )

   Dim objConn, strQuery, numReservation, RS

   objDebugger.WriteLine( ">> CancelReservation( " & numMember & "," & dtDate & "," & dtTime & ")" )

   If( dtDate < Date() OR ( dtDate = Date() AND dtTime < Time() ) ) Then
      CancelReservation = "You cannot cancel reservations in history. Please try again."
      objDebugger.WriteLine( "<< CancelReservation, reservation in history not allowed" )
      Exit Function 
   End If

   Set objConn = CreateObject("ADODB.Connection")
   objConn.Open "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & STR_TRULIFEDBFILE & ";"

   strQuery = "SELECT * FROM Reservations WHERE MemberID=" & numMember & " AND ReservationDate = #" & dtDate & _
              "# AND ReservationTime=# " & dtTime & "#" 
   Set RS = objConn.Execute( strQuery )

   If Not RS.EOF Then
      numReservation = RS( "ID" )
   Else
      numReservation = 0
   End If
   objDebugger.WriteLine( "Reservation ID: " & numReservation )

   If( numReservation = 0 ) Then
     CancelReservation = "Unable to cancel reservation; reservation not found."
   Else
     strQuery = "Delete * FROM Reservations WHERE ID=" & numReservation
     objConn.Execute( strQuery )
     CancelReservation = "Reservation successfully cancelled."
   End If

   objConn.Close
   Set objConn = Nothing

   objDebugger.WriteLine( "<< CancelReservation, result: " & MakeReservation )

End Function


' // ========================================================================
' // QueryReservations
' // ------------------------------------------------------------------------
' // Query reservations for given member
' // ========================================================================
Function QueryReservations( numMemberID )
   Dim objConn, RS, strReservations, strQuery

   objDebugger.WriteLine( ">> QueryReservations( " & numMemberID & " )" )

   QueryReservations = ""

   Set objConn = CreateObject("ADODB.Connection")
   objConn.Open "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & STR_TRULIFEDBFILE & ";"
   strQuery =  "SELECT * FROM Reservations WHERE MemberID=" & numMemberID & _
               " AND ( ReservationDate > #" & Date() & "# OR ( ReservationDate = #" & Date() & _ 
			   "# AND ReservationTime >= #" & Time() & "# ) )" & _
               " ORDER BY ReservationDate ASC, ReservationTime ASC"
   Set RS = objConn.Execute( strQuery )
   While Not RS.EOF
     If( strReservations <> "" ) Then
       strReservations = strReservations & "; "
     End If
     strReservations = strReservations & RS("ReservationDate") & " " & RS( "ReservationTime" ) & _
	                   " Court " & RS( "CourtID" )
     RS.MoveNext
   WEnd

   objConn.Close
   Set objConn = Nothing

   QueryReservations = strReservations

   objDebugger.WriteLine( "<< QueryReservations, result: " & QueryReservations )

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 ShowUnauthorizedMember()
   ShowUnauthorizedMember = "You cannot make reservations using this mobile number."
End Function

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

Function ShowHelp()
   ShowHelp = STR_USAGE
End Function
  

' // ========================================================================
' // GetTimeValue
' // ------------------------------------------------------------------------
' // Convert any user time input to 
' // ========================================================================

Function GetTimeValue( strTime )
   Dim tmVal

' Define to resume after error, so tmVal will hold an empty string when input is not OK
On Error Resume Next

   tmVal = TimeValue( strTime )
   If( InStr( tmVal, "00:00" ) = 0 ) Then
      ' This indicates that user didn't enter a round hour. Only round hours are accepted
      tmVal = ""
   ElseIf( tmVal < TimeValue( "7am" ) ) Then
      ' No reservations allowed before 7am
      tmVal = ""
   ElseIf( tmVal > TimeValue( "9pm" ) ) Then
      ' No reservations allowed after 9pm
      tmVal = ""
   End If

   GetTimeValue = tmVal
End Function


' // ========================================================================
' // GetWDay
' // ------------------------------------------------------------------------
' // Function returns the VB day constant (vbSaturday, vbSunday, etc.)
' // based on the day passed by the user ("sa", "su", ...)
' // ========================================================================

Function GetWDay( strDay )
   Dim strUDay

   strUDay = UCase( strDay )

   If( Left( strUDay, 2 ) = "SA" ) Then 
      GetWDay = vbSaturday
   ElseIf( Left( strUDay, 2 ) = "SU" ) Then 
      GetWDay = vbSunday 
   ElseIf( Left( strUDay, 2 ) = "MO" ) Then 
      GetWDay = vbMonday
   ElseIf( Left( strUDay, 2 ) = "TU" ) Then 
      GetWDay = vbTuesday
   ElseIf( Left( strUDay, 2 ) = "WE" ) Then 
      GetWDay = vbWednesday
   ElseIf( Left( strUDay, 2 ) = "TH" ) Then 
      GetWDay = vbThursday
   ElseIf( Left( strUDay, 2 ) = "FR" ) Then 
      GetWDay = vbFriday
   Else
      GetWDay = -1
   End If
End Function


' // ========================================================================
' // WeekDayOffset
' // ------------------------------------------------------------------------
' // Function returns the number of days between today
' // and the next given wDay. Result: 0 <= result < 7
' // ========================================================================

Function WeekDayOffset( wDay )
   Dim dtToday, wToday 
   dtToday = Date()
   wToday = DatePart("W", dtToday )

   WeekDayOffset = wDay - wToday
   If( WeekDayOffset < 0 ) Then
      WeekDayOffset = WeekDayOffset + 7 
   End If
End Function


' // ========================================================================
' // NextDate
' // ------------------------------------------------------------------------
' // Function calculates the next wDay date.
' // ========================================================================

Function NextDate( wDay )
   Dim numIncrement
   numIncrement = WeekDayOffset( wDay )
   NextDate = DateAdd( "W", numIncrement, Date() ) 
End Function

Footer Navigation

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