' 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

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