Option Explicit

 CONST STR_DEBUGFILE     = "Sys\Tmp\Vienna Sanitation.txt"
 CONST STR_DATABASEFILE	 = "Projects\Vienna Sanitation\Database\Vienna Sanitation.mdb"

 Const SEPERATOR         = "."

 ' Declaration of global objects
 im 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 - for troubleshooting purposes
 g_objDebugger.DebugFile = STR_DEBUGFILE
 g_objDebugger.Enabled   = True


 ' // ========================================================================
 ' // Function: ProcessMessage
 ' // ------------------------------------------------------------------------
 ' // ProcessMessage trigger function to process incoming messages
 ' // ========================================================================

 Function ProcessMessage( numMessageID )
   Dim objMessageIn, objMessageOut
   Dim numEmployeeID, numStationID, numTellerStand 
   Dim strEmployeeName, strErrorMessage 
   Dim bResult
   Dim strMessageOutBody
   Dim arrMessage, i
   Dim strInvoerMethode

   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,  FindFirstMessage failed: [" & g_objMessageDB.LastError & "]"
      Exit Function
   End If

   ' Change Status to from Pending to Success. 
   objMessageIn.Status = g_objConstants.MESSAGESTATUS_SUCCESS
   g_objMessageDB.Save objMessageIn   

   g_objDebugger.WriteLine "Incoming message saved, result: [" & g_objMessageDB.LastError & "]"

   ' Retrieve Member's name
   bResult = CheckEmployee( objMessageIn.From, numEmployeeID, strEmployeeName, strErrorMessage )
   
   ' Convert the message to an array
   arrMessage = MessageToArray( objMessageIn.Body )
   
   ' Check whether the user input is valid or not
   If( objMessageIn.Body <> "" ) Then
     If( IsValidMessage( arrMessage ) ) Then
   
       ' Loop throught the results and process them
       For i = 0 To UBound( arrMessage )
         If( bResult ) Then
           bResult = InsertCollected( numEmployeeID, arrMessage(i,0), arrMessage(i,1), strErrorMessage )  
         Else
           Exit For
         End If
       Next
     
       ' Reply to the customer
       If( bResult = False ) Then
        strMessageOutBody = strErrorMessage
       Else
        strMessageOutBody = "Dear " & strEmployeeName & ", we received your counter info " & _
                            PrintResults( arrMessage ) & " successfully."
       End If
     
     Else
       strMessageOutBody  = "Invalid message. Provide toilet-id, followed by a blank, " & _
                            "followed by the counter info"
     End If
   End If
      
   If( strMessageOutBody <> "" ) Then
     ReplyMessage objMessageIn.From, strMessageOutBody
   End If
 
   ' Close the Message Database
   g_objMessageDB.Close

   g_objDebugger.WriteLine "<< ProcessMessage"

 End Function



 ' // ========================================================================

 Function ReplyMessage( strRecipient, strBody )

   Dim objMessageOut

   g_objDebugger.WriteLine ">> ReplyMessage"
      
   ' Create the reply message
   Set objMessageOut = g_objMessageDB.Create
   
   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        = strRecipient
     objMessageOut.ChannelID = 0
     objMessageOut.Body      = strBody

     g_objMessageDB.Save objMessageOut
   End If

   g_objDebugger.WriteLine "<< ReplyMessage"

 End Function


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

 Function CheckEmployee( strInputNumber, ByRef numEmployeeID, ByRef strEmployeeName, ByRef strErrorMessage )
   Dim objConn, RS, strQuery

   g_objDebugger.WriteLine( ">> CheckEmployee" )

   CheckEmployee = False
   numEmployeeID = 0
   strEmployeeName = ""

   Set objConn = GetDatabase()

   Set RS = objConn.Execute( "SELECT * FROM tblEmployees WHERE Cell='" & strInputNumber & "'"  )
   If RS.EOF Then
      CheckEmployee   = False
      numEmployeeID   = 0
      strEmployeeName = ""
      strErrorMessage = "Error: Unknown user!"
   Else
      CheckEmployee   = True
      numEmployeeID   = RS( "ID" )
      strEmployeeName = RS( "Name" )
      strErrorMessage = ""
   End If

   objConn.Close
   Set objConn = Nothing

   g_objDebugger.WriteLine( "<< CheckEmployee" )

 End Function


 ' // ========================================================================
 ' // Query InsertOpgave
 ' // ------------------------------------------------------------------------
 ' // Lookup the current price of the selected stock ( use ticker symbol )
 ' // ========================================================================

 Function InsertCollected( numEmployeeID, numStationID, numCounter, ByRef strErrorMessage  )

   g_objDebugger.WriteLine( ">> InsertCollected" )

   Dim objConn, RS
   Dim strQuery

   InsertCollected    = False
   strErrorMessage = ""

   Set objConn = GetDatabase()

   strQuery = "INSERT INTO tblCounter( EmployeeID, StationID, CoinsCounter ) VALUES( " & _
              numEmployeeID & ", " & numStationID & ", " & numCounter & ")" 
   g_objDebugger.WriteLine( ">> Inserting into database: " & strQuery )
   objConn.Execute ( strQuery )
   
   ' Close database
   objConn.Close
   Set objConn = Nothing

   InsertCollected = True

   g_objDebugger.WriteLine( "<< InsertCollected" )

 End Function


 ' // ========================================================================
 ' // MessageToArray
 ' // ------------------------------------------------------------------------
 ' // Change the current message in a 2 dimentional array
 ' // ========================================================================

 Function MessageToArray( strMessageBody )

   ' Declare array to store results in
   Dim arrResult()
   Dim arrValues, arrMessage, i


   ' In case 2 or more counter results are submitted a 2 dimentional
   ' array needs to be reserved
      
   ' Split the messages into seperate results
   arrMessage = Split( strMessageBody, SEPERATOR )
          
   ' Count how many results are returned and redeclare an array
   ReDim arrResult( UBound(arrMessage), 1 )
          
   ' Read the message and store it into an array
   For i = 0 To UBound( arrMessage )
      If( InStr( arrMessage( i ), " " ) ) Then
         arrValues = Split( Trim(arrMessage( i )), " " )

         On Error Resume Next
           arrResult(i,0) = arrValues(0)
           arrResult(i,1) = arrValues(1)
         On Error Goto 0
      End If 
   Next

   ' Return this array
   MessageToArray = arrResult
 End Function


 ' // ========================================================================
 ' // PrintResultaten
 ' // ------------------------------------------------------------------------
 ' // Show the results
 ' // ========================================================================

 Function PrintResults( arrResult )
  Dim strResults, i
  For i = 0 To UBound( arrResult )
    strResults = strResults & arrResult(i,1) & " for station " & arrResult(i,0)
    If( i <= ( UBound( arrResult ) -1 ) ) Then
       strResults = strResults & " and "
    End If
  Next
  PrintResults = strResults
 End Function



 ' // ========================================================================
 ' // IsValidMessage
 ' // ------------------------------------------------------------------------
 ' // Check whether the user input is correct or not
 ' // ========================================================================

 Function IsValidMessage( arrResultaten )
   
   Dim bResult, objConn, RS, i
   
   bResult = True
   
   ' Op the database
   Set objConn = GetDatabase()
   
   For i = 0 To UBound( arrResultaten )
     
     ' Check wheter the station exists or not
     If( Not IsNumeric(arrResultaten(i,0) ) ) Then
       bResult = False
       Exit For
     End If

     Set RS = objConn.Execute( "SELECT Count(*) FROM tblStations WHERE ID=" & arrResultaten(i,0) )
     If( RS(0) < 1 ) Then
       bResult = False
       Exit For
     End If
     
     ' Check whether the counter value is correctly    
     If( Not IsNumeric( arrResultaten(i,1) ) ) Then
       bResult = False
       Exit For
     End If
     
     If( Trim(arrResultaten(i,1)) = "" ) Then
       bResult = False
       Exit For
     End If
     
     If( arrResultaten(i,1) < 0 ) Then
       bResult = False
       Exit For
     End If
     
   Next
   
   ' Database closen
       objConn.Close
   Set objConn = Nothing
   
   ' Return the results
   IsValidMessage = bResult
 End Function



 ' // ========================================================================
 ' // GetDatabase
 ' // ------------------------------------------------------------------------
 ' // Try to connect to the project database
 ' // ========================================================================

 Function GetDatabase()

   g_objDebugger.WriteLine( ">> GetDatabase" )

   'Declare variables
   Dim numErrNo
   Dim objDB
   
   'Set the FileSystemObject and the databaseobject
   Set objDB  = CreateObject("ADODB.Connection")
   
   'Try to open the database
   On Error Resume Next
     objDB.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & STR_DATABASEFILE & ";"
     numErrNo = Err.Number   
   On Error Goto 0
   
   'If unable to succesfully do so, quit the script!
   If( numErrNo <> 0 ) Then
      g_objDebugger.WriteLine( ">> GetDatabase: Cannot find database.." )
      WScript.Quit
      
   'Otherwise, return the databaseobject
   Else
      Set GetDatabase = objDB
   End If
   
   g_objDebugger.WriteLine( "<< GetDatabase" )

 End Function