ActiveXperts

---

Send, receive and automate SMS messages

---

SMS money registration system (Vienna Sanitation)

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

1. Background

Vienna Sanitation is a company that rents restrooms in public areas. To use one of their restrooms a 50 cent coin needs to be inserted in a counter. This will open the door to the restroom.

Every restroom has its own ID and a counter which counts how many coins have been inserted into the restroom. Every day the counters value is logged into a database and the money is stored somewhere safe. By comparing today's value with yesterday's value the finance department is able to calculate the yield.

This case study describes how Vienna Sanitation implemented an SMS-based solution to register the collected money on a central location, automatically, using SMS.

2. Problem Statement

Currently the employees need to read out the counter status for all of the restrooms and send out a form to the finance department every day. This form contains the name of the employee, the restroom id and the ammount of money that was deposited. It usually takes at least two days for this form to arrive at the finance department. After which the employees at the finance department fill in the form details into a shared Excel sheet.

Vienna Sanitation wanted to improve the speed as well as the quality of the process by automating as much of it as was possible. To do this the restroom information should be sent to the finance department using SMS. This way the finance department does not need to wait two days to process the yield of the day. Sending the toilet information using SMS even allows a computer to process the submitted data automatically.

3. Goals of the new System

Goals of the new system:

• Ability to accept incoming SMS requests to register the daily turn-over
• Verify if SMS request is coming from a valid toilet location
• Verify if SMS request is indeed from a mobile number of one of the cleaners of Vienna Sanitation
• Process incoming SMS request into a database

4. ActiveXperts SMS Messaging Server Solution

The Vienna Sanitation cleaning employees report the counter value to Vienna Sanitation by sending a SMS message to a Vienna Sanitation server. This server processes the messages and stores the submitted counter values into the database. The message sender is checked first before the message itself is being processed. If the message is received from a known phone number, it will be processed. The employee should be able to report several results in only one SMS message. The submitted values will be entered into a Access database.

The solution is implemented using the ActiveXperts SMS Messaging Server There's a script to process incoming messages, a simple Access database and a properly configured GSM-modem with a SIM card.

The project database contains three tables:

• tblStations The first table contains the toilet information. Every toilet has an ID, a location and perhaps even a name.
• tblEmployees All employees who are allowed to submit data are listed in this table.
• tblCounter The counter value of the day, submitted by who and from which toilet is stored in this table. Every time an employee submits a counter value, a new entry is written into the database.

A counter value should be sent in a fixed format. The first characters should indicate the toilet ID followed by a space and the counter value. For example counter value 365 for toilet number 5 should be entered this way: "5 365". The cleaning-woman is able to send several counter values by separating them with a dot. For example counter value 365 for station 5 and counter value 654 for station 6 should be entered this way: "5 356. 6 654".

SMS message format

The system handles SMS messages as follows:

SMS syntax	Sample	Explanation
<toilet-number><turnover>	5 356	Turn-over of 356 for toilet 5
<toilet-number 1><turnover 1>. <toilet-number 2><turnover 2>. <toilet-number n><turnover n>	5 356.6 611.8 55.9.303	Turn-over of 356 for toilet 5; Turn-over of 611 for toilet 6, etc.
<any other message>	Please help	Unrecognized command. A Help message is replied

Trigger

A Trigger is called when a new messages arrives in the system. Vienna Sanitation handles only one type of SMS messages: incoming turn-over registrations. Therefore, only one trigger is required:

Enabled	Description	Condition	Script
YES	Process incoming commands	ANY MESSAGE	\Projects\SupportIT\Triggers\SupportIT.vbs

SupportIT.vbs (full code)

 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( "Axsms-messaging-server.Constants" )
 Set g_objMessageDB      = CreateObject( "Axsms-messaging-server.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

Footer Navigation

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