You are here:

ActiveXperts.com > SMS Messaging Server > Case Studies > SMS Reservation Demo (Squash Court)
ActiveXperts SMS Messaging Server Send, receive and automate SMS messages

Quicklinks

SMS reservation system (Action Studio)

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

1. Background

The Action Studio Health and Fitness Club company is a health and fitness club, founded in 2001. Action Studio has a large range of facilities, including aerobics, fully equipped gyms, swimming and squash. The club has around 1200 active members; it is opened 7 days a week, from 7am until 10pm. Members of the club pay a monthly fee. Members have unlimited access to all facilities.

Action Studio has two squash courts, available to all members. This Case Study concentrates on the reservation system of the two squash courts at Action Studio Health and Fitness Club.

2. Problem Statement

Action Studio has 2 squash courts that can be booked by any member of the club. The squash courts can be booked from 7am-10pm. Members can only book a squash court max. 7 days in advance.

The club uses a large manual planning board to manage squash court reservations. To make a new reservation, a member has to go there to make a reservation on a planning board, manually. As an alternative, members can call up to the club to ask an employee to make that reservation for them.

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 Action Studio 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 running Windows 2003 Server.
  • The systems runs ActiveXperts SMS Messaging Server as well as the Action Studio MS Access database.
  • A WaveCom Fastrack GSM Modem to handle all incoming/outgoing reservation request and replies

Action Studio 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, it would not make sense for Action Studio to use SMPP. The GSM modem throughput, about 10 messages per minute, is sufficient for this solution.

They ended up purchasing a WaveCom GSM modem. At their telecoms provider they purchased a SIM card with an SMS bundle.

For this example we will assume that the SIM card is associated with the following number: +3162234329

Action Studio database

Reservations are registered in a separate database. The Action Studio company decided to choose an MS Access database for it. When the application needs to scale up they will be able to migrate the database to Microsoft SQL Server.

The database consists of two tables:

  • Members Each record 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\Action Studio\Triggers\ActionStudio.vbs

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

  
- Set incoming message to 'processed', so it will not be processed again by the system
- Look up Action Studio member-ID by incoming SMS number. 
- Is sender's mobile unknown ?        => send a reply SMS to the sender that he/she is not a 
                                         member
- Is there a syntax error ?           => 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 Action Studio'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_ACTIONSTUDIODBFILE = "Projects\Action Studio\Database\ActionStudio.mdb"
CONST STR_DEBUGFILE          = "Tmp\ActionStudio.txt"

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

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



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

Function ProcessMessage( numMessageID )
   Dim objMessageIn, objMessageOut, arrMessage
   Dim strMessageOutBody
   Dim numMemberID, wDay, dtDate, dtTime

   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 locate the 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 ), " " )

   ' Retrieve ActionStudio'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 = ShowWrongTimeParam( 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 = 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      = objMessageIn.Type 
      objMessageOut.Status    = g_objConstants.MESSAGESTATUS_PENDING
      objMessageOut.To        = objMessageIn.From 
      objMessageOut.ChannelID = 0  ' Any available SMS 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


' // ========================================================================
' // 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

   g_objDebugger.WriteLine( ">> CheckMember" )

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

   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

   g_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

   g_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."
      g_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_ACTIONSTUDIODBFILE & ";"

   For iCourt = 1 To 2
      g_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
      g_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
      g_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

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

End Function


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

   Dim objConn, strQuery, numReservation, RS

   g_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."
      g_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_ACTIONSTUDIODBFILE & ";"

   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
   g_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

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

End Function


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

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

   QueryReservations = ""
   strReservations   = ""

   Set objConn = CreateObject("ADODB.Connection")
   objConn.Open "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & STR_ACTIONSTUDIODBFILE & ";"
   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

   If( strReservations = "" ) Then
     QueryReservations = "No reservations"
   Else
     QueryReservations = strReservations
   End If

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

End Function


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

Function ShowWrongParam( strParam )
   ShowWrongParam = "Invalid value: " & strParam
End Function

Function ShowWrongTimeParam( strParam )
   ShowWrongTimeParam = "Wrong time value: " & strParam & _
      ". Only valid time values are: 7am - 9pm"
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