You are here:

ActiveXperts.com > SMS Messaging Server > Case Studies > Action Studio Health and Fitness Club
ActiveXperts SMS Messaging ServerSend, receive and automate SMS messages

Quicklinks

SMS Text Messaging Computer System: Action Studio Health and Fitness Club

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 equiped 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.

ALT A working demo of this case study is included with the ActiveXperts SMS Messaging Server installation.

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 (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 Action Studio MS Access database on the same computer
    • A WaveCom Fastrack 1306B GSM Modem to handle all incoming/outgoing reservation request and replies
    Basically, Action Studio 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 Action Studio 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


  • Action Studio database

    • Reservations are registered in a separate database. The Action Studio 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\Action Studio\Triggers\ActionStudio.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 Action Studio 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 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( "AxMmServer.Constants" )
Set g_objMessageDB  = CreateObject( "AxMmServer.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