Download ActiveXperts SMS Messaging Server 5.0

  Case studies - How SMS Messaging Server is used by existing customers

 
Problem statement
PhixionFinancial is a commercial bank established as a share company and started operation in the middle eighties. PhixionFinancial’s employees spend a lot of time processing accountholders payments. Currently no payments are submitted digitally, so PhixionFinancial’s employees need to process them by hand. The management decided that payments should be processed in a much more efficient way and decided to start working on a solution for this issue.

 
Goals:
To solve the issue the management decided to create a solution to achieves the following goals:

• The solution should reduce the paperwork
• The solutions should reduce the amount of errors made by employees
• The employees should spend less time on processing payments.
• No need to visit the bank to do transactions.

 
ActiveXperts SMS Messaging Solution
The bank will maintain processing transactions by transaction forms but will also start offering so called “transaction scratch-cards”. Such a card has an amount printed on it and is used to deposit this amount of money to a bank account. A complex 15 character key, of which the last character is a “-”, is printed on each card. The key is not visible, until it is scratched by for instance a coin or fingernail. The keys, printed on the scratch card, are stored in a database on a server inside the bank’s local area network. The customer is supposed to send this key to the bank via SMS, followed by the bank account number where the money should be deposited.

Transaction scratch-cards are offered in the following categories:

• 5 USD
• 10 USD
• 20 USD
• 50 USD
• 100 USD

To achieve this, ActiveXperts SMS Messaging Server is installed on a dedicated machine. There’s a database to hold all bank account information; is located on the same server. *. All scratch card key’s are also stored in this database. Account holders are not allowed to upgrade a bank account twice; therefore, every key has a Boolean “available” property. As soon as somebody uses his or her scratch card key, this value will be changed. The table looks like this:

We created a new project in SMS Messaging Server. This project contains a so called “trigger” which processes every incoming SMS Message which is received on the telephone number associated to the server.

Whenever an SMS is received, the computer will scan the body of it, for account numbers and scratch card keys. The computer assumes the first 15 characters is the scratch card key and the last 10 characters is the bank account number. Every scratch card key ends with a “-“, this makes a scratch card code looks like “1A3B5C7D9E11F1-1234567890”.

In any case the account holder gets a reply via SMS.

* If you plan to implement this sample in your production environment, because of security reasons, it is strongly recommended to use a dedicated MSSQL or MySQL server to store you’re bank account information.

 
The code

' // ========================================================================
' // C:\Program Files\ActiveXperts\SMS Messaging Server\Projects\The_Bank\Triggers\The_Bank.vbs
' // ------------------------------------------------------------------------
' // 
' // 
' // ========================================================================


Option Explicit

Const STR_DEBUGFILE     = "C:\Program Files\ActiveXperts\SMS Messaging Server\Projects\The_Bank\LOG\The_Bank_Log.txt"
Const STR_DATABASE      = "C:\Program Files\ActiveXperts\SMS Messaging Server\Projects\The_Bank\Database\bank.mdb"

' 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 - for troubleshooting purposes
g_objDebugger.DebugFile = STR_DEBUGFILE
g_objDebugger.Enabled	= True


' // ========================================================================
' // Function: loadDatabase
' // ------------------------------------------------------------------------
' // Loads the bank database, you can migrate to MySql or MSSql if you like..
' // just change the connection string if you do
' // ========================================================================

Function loadDatabase()

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

End Function

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

Function ProcessMessage( numMessageID )
   Dim objMessageIn, objMessageOut

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


   ' Change Status to from Pending to Success. If you don't do it, the message will be processed by subsequent triggers (if defined) because message is still pending
   objMessageIn.Status = g_objConstants.MESSAGESTATUS_SUCCESS
   g_objMessageDB.Save objMessageIn  

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

   readmessage( objMessageIn )
 
   ' Close the Message Database
   g_objMessageDB.Close

   g_objDebugger.WriteLine "<< ProcessMessage"

End Function


' // ========================================================================
' // readmessage
' // ------------------------------------------------------------------------
' // Your custom function
' // ========================================================================

Function readmessage( objMessageIn )

   'Declare variables
   Dim objConn
   Dim strMessage, strBankaccount, strSender, strKey
   Dim strQuery, RS, strUpdateQuery

   'Load the database object
   Set objConn = loadDatabase
   
   'Get some info from the sms
   'I've created 8 character keys in the database, so I only
   'need to know the first 8 characters of the sms because I
   'asume that will contain the update key
   strMessage     = objMessageIn.Body
   strSender      = objMessageIn.Sender
   strKey         = Left(strMessage, 15)
   strBankAccount = Mid(strMessage, 16, 10)
   
   'Perhaps the customer sends a question mark because he/she
   'does not know how to use the system. If so, reply with a short howto
   If( Left( strMessage, 1 ) <> "?" ) Then
   
     'Try to fetch the upgrade key from the database
     strQuery   = "SELECT * FROM upgrades WHERE key='" & strKey & "' AND available=true"
     Set RS     = objConn.Execute( strQuery )
   
     'When unable to find the key in the database, notify the sender
     'When able to find the key, upgrade the customers bank account
     If( Not RS.EOF ) Then
       g_objDebugger.WriteLine ">> Start processing message: "
       updateCustomersCredit strKey, strBankAccount, strSender
     Else
       g_objDebugger.WriteLine ">> Invalid upgrade key.. script ended...."
       strMessage =  "Invalid key: Your key was: " & strKey & "."
       reply strSender, strMessage
     End If
   Else
   
     reply strSender, "Please SMS your 8-character upgrade key. The system will upgrade your bank account."
   End If

  g_objDebugger.WriteLine "<< Done processing"

End Function


' // ========================================================================
' // Function: Update
' // ------------------------------------------------------------------------
' // Function to update the database and the customer his credit
' // ========================================================================

Function updateCustomersCredit(strKey, strBankAccount, strSender)

    'Declare variables
    Dim objConn
    Dim strQuery, RSAccount

    'Load the database object
    Set objConn = loadDatabase

    'try to associate the senders phonenumber with his bank account
    strQuery      = "SELECT * FROM accounts WHERE customerAccount=" & strBankAccount
    Set RSAccount = objConn.Execute( strQuery )
    
    'When able to find associate:
    If ( Not RSAccount.EOF ) Then
      
      Dim RSUpgrade
      strQuery      = "SELECT * FROM upgrades WHERE key='" & strKey & "'"
      Set RSUpgrade = objConn.Execute( strQuery )
      
      If ( Not RSUpgrade.EOF ) Then

         g_objDebugger.WriteLine ">> Upgrade valid.."

         'Declare variables
         Dim varCurrentValue, varValue, varNewValue
         Dim errNo, errDescr

         'Calculate the customers new credit
         varCurrentValue = CInt( RSAccount("customerCredit") )
         varValue        = CInt( RSUpgrade("Value") )
         varNewValue     = varCurrentValue + varValue
         
         'Create an update query to update the customers credit
         strQuery = "UPDATE accounts SET customerCredit=" & varNewValue & " WHERE customerAccount=" & strBankAccount

         'Execute the query and try to fetch errors if they occure
         On Error Resume Next
           objConn.Execute( strQuery )
           errNo    = Err.Number
           errDescr = Err.Description
         On Error Goto 0
         
         'if the database was updated properly:
         If ( errNo = 0 ) Then
            
            'Update the database, disable the key. Otherwise the 
            'customer is able to update his credits twice
            'Deleting the record is probably a better idea, but you will not see
            'what exactly happens
            strQuery = "UPDATE upgrades SET available=false WHERE key='" & strKey & "'"
            objConn.Execute( strQuery )
            
            'notify the sender the key was processed succesfully
            reply strSender, "Key processed succesfully, account's credit is upgraded." 
            
            'Update the logfile
            g_objDebugger.WriteLine ">> Key succesfully processed!!"
         Else
            'notify the sender the message wasn't processed properly
            reply strSender, "Sorry.. Error processing message. Please try again or try contacting our servicedesk."
            
            'Update the logfile
             g_objDebugger.WriteLine ">> Error processing message: " & errDescr
         End If 
      End If
    Else
    
       'otherwise, notify the sender
       g_objDebugger.WriteLine ">> Unable to process "
       reply strSender, "Unable to process your message. Could not find account number. Please try again! AccountNo.:" & strBankAccount
    End If
End Function


' // ========================================================================
' // Function: Reply
' // ------------------------------------------------------------------------
' // used to reply to the sender
' // ========================================================================

Function reply(strRecipient, strMessage)

   Dim objMessageOut
   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  ' Any available SMTP channel
      objMessageOut.Body      = strMessage
      g_objMessageDB.Save objMessageOut
      g_objDebugger.WriteLine ">>>> Message succesfully sent"
   Else
      g_objDebugger.WriteLine ">>>> !!! ERROR PROCESSING MESSAGE !!! ERR.NO: " & g_objMessageDB.LastError
   End If

End Function