ActiveXperts

---

Send, receive and automate SMS messages

---

SMS Text Ordering System: SMS Waiter at the Green Bar, San Jose, Costa Rica

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

1. Background

The Green Bar is Music bar in the Center of San José, Costa Rica, that started back in 2004. It is a trendy bar; you can take your own music CD's and the DJ's play them. On a large screen you can see the playlist. On the 1st floor, there's a podium where a band performs live every Saturday and Sunday. The bar is open 7 days a week, from 4pm until.

2. Problem Statement

During the evening, most customers are server in a timely manner. But some clients have to wait long before a waiter takes the order, and delivers the ordered beverages to the clients. There are enough waiters; they are all hard workers and customer focussed. However, at busy times some clients get less attention and get frustated because they have to wait much longer than others.

Besides the long wait time, the bar wants to maintain its trendy state, and is seeking for new ways to increase client satisfaction.

3. Goals of the new System

The management of the Green Bar has the following requirements:

• Reduced avarage order time
• More efficient way of ordering, preferably via a mobile device
• Customers can still orders drinks by a waiter or at the bar
• Solution must fit in the bar's trendy policy

4. ActiveXperts SMS Messaging Server Solution

ActiveXperts SMS Messaging Server will reduce order time, where clients can immediately order drinks via SMS.

New menu

On every table, there will be a new style of menu that can be used to order drinks via SMS.

Click on image to enlarge

SMS message format

The SMS Message most consist of a table number, and one or more beverage codes and the amount of the beverages.

Example:

Client at table 9 wants to orders 3 Tonic and 2 Coca Cola

The SMS message:

9 TC3 CC2

SMS Waiter database

The Green Bar company decided to use an MS Access database. MS Access performs well for this solution. If a faster database is required at a later stage, it can be migrated to for instance MS SQL or MySQL without changing the logic of the application.

Triggers

To make an order, a custumer can send an SMS message to a dedicated GSM number: +506 12345678. 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 +506 12345678, the following trigger is defined:

Enabled	Description	Condition	Script
YES	Process incoming order requests	To = '+506 12345678'	\Projects\SMS Waiter\Triggers\SmsWaiter.vbs

Full Trigger Code

SmsWaiter.vbs (full code)

Option Explicit

Dim g_objMessageDB, g_objConstants, objMessage,objMessageIn,g_objDebugger


' Create global objects
Set g_objMessageDB = CreateObject( "AxMmServer.MessageDB" )
Set g_objConstants = CreateObject( "AxMmServer.Constants" )
Set g_objDebugger   = CreateObject( "ActiveXperts.VbDebugger" )

'Set Debug file
g_objDebugger.DebugFile = STR_DEBUGFILE
g_objDebugger.Enabled   = True 

' Open the databASe
g_objMessageDB.Open

CONST STR_DATABASE 			 = "C:\UseRS\Dearon\Desktop\stage\VBScript\SmsOrder.mdb"
CONST STR_DEBUGFILE          = "C:\UseRS\Dearon\Desktop\stage\VBScript\Debugfile.TXT"

CONST STR_USAGE              = "First table number then product code and then the amount: (12 HS2 RB2 CC7)  " 
CONST MAX_ORDER				 = 20
   
   
' // ========================================================================
' // ProcessMessage
' // ------------------------------------------------------------------------
' // ProcessMessage trigger function
' // ========================================================================
Function ProcessMessage( numMessageID )
   Dim objMessageIn, objMessageOut, arrMessage
   Dim strMessageOutBody
   g_objDebugger.WriteLine ">> ProcessMessage"

 ' 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 & "]"

   If( g_objMessageDB.LastError = 0 ) Then
    Set objMessage   = g_objMessageDB.Create
		strMessageOutBody = InputSms(objMessageIn.Body)
    If ( g_objMessageDB.LastError = 0 ) Then
       objMessage.Direction     = g_objConstants.MESSAGEDIRECTION_OUT
       objMessage.Type          = g_objConstants.MESSAGETYPE_SMS
       objMessage.Status        = g_objConstants.MESSAGESTATUS_PENDING
       objMessage.BodyFormat    = g_objConstants.MESSAGEBODYFORMAT_SMS_TEXT
       objMessage.ChannelID     = 0  ' Any available SMS channel
       objMessage.FromAddress   = objMessageIn.FromAddress
       objMessage.Body          = strMessageOutBody 
	   g_objMessageDB.Save ( objMessage )
	   g_objDebugger.WriteLine "Save message, result: " & g_objMessageDB.LastError & ": " & _
	   g_objMessageDB.GetErrorDescription ( g_objMessageDB.LastError )
	End If
		g_objMessageDB.Close
	End If
End Function


' // ========================================================================
' // InputSms
' // ------------------------------------------------------------------------
' // Process input message
' // ========================================================================
Function InputSms(SmsText)
Dim   strOrder,arrSmsText,strOrderCheck,strTable,strOrderError,strAmountError
		
g_objDebugger.WriteLine ">> ProcessMessage,   Inputmessage."

If  IsEmpty(SmsText) Then
			g_objDebugger.WriteLine " ProcessMessage,  Message is empty "
			InputSms =" message is empty " 
			Exit Function
End If

		SmsText= (UCASe(SmsText))			
		
if	 	Checktable (SmsText,strTable) = false then
		InputSms = "Table number doesn't exist:" & strTable
		g_objDebugger.WriteLine InputSms

elseif  CheckItems (SmsText,strOrderError) = false then	
		InputSms =   strOrderError
		g_objDebugger.WriteLine InputSms
		
elseif  CheckAmount (SmsText,strAmountError)  = false then
		InputSms = strAmountError
		g_objDebugger.WriteLine InputSms
		
else	
   	 strOrder = GetOrder()
	 InputOrder SmsText,strOrder,strTable
	 InputSms = ConfirmOrder(strOrder)
 end if 
g_objDebugger.WriteLine "<< ProcessMessage,   Inputmessage."

 End Function


' // ========================================================================
' // Check Table
' // ------------------------------------------------------------------------
' // Search of a tablenumber of it exist of the order.
' // ========================================================================
Function Checktable(strInputSms,ByRef strTable)

Dim objConn,RS,ArrInputSms 
g_objDebugger.WriteLine ">> Checktable."

ArrInputSms= Split(strInputSms, " ")
strTable = ArrInputSms(0)
   
If IsNumeric(strTable) = false then
	strTable = "Number is incorrect: " & strTable
		g_objDebugger.WriteLine "<< Checktable. "& strTable
	Exit Function
End If
	
Set objConn = CreateObject("ADODB.Connection")
objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & STR_DATABASE & ";"
Set RS= objConn.Execute( "SELECT TableNR From TableNumber WHERE TableNR = " & strTable & " " )
	If RS.EOF or RS.EOF Then

	g_objDebugger.WriteLine "<< Checktable. Table doesn't exist: "  & strTable

		Checktable = False 
		objConn.close
		Exit Function
	End If
		Checktable = True 
		g_objDebugger.WriteLine "<< Checktable. Table number exist: "   & strTable
        objConn.close

	End Function 


' // ========================================================================
' // Check Items
' // ------------------------------------------------------------------------
' // Checked if  the items exist.
' // ========================================================================
Function CheckItems(strInputSms,ByRef strOrderError)
Dim i,objConn, RS,ArrCheckItem 

g_objDebugger.WriteLine ">> CheckItems."

ArrCheckItem = Split(strInputSms, " ")

For i = 1 to Ubound(ArrCheckItem)
	strOrderError = "Order Code doesn't exist: " & left(ArrCheckItem(i),2) 
 	Set objConn = CreateObject("ADODB.Connection")  
	objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & STR_DATABASE & ";"
	Set RS= objConn.Execute("SELECT Code from ProductCode WHERE Code = '" & _
	(left(ArrCheckItem(i),2)) & "' " )
 
 If RS.EOF or RS.EOF Then
	g_objDebugger.WriteLine "<< CheckItems. " &strOrderError		
	CheckItems = False
	objConn.close
	Exit Function
 Else 
   End If
next
CheckItems = True 
g_objDebugger.WriteLine "<< CheckItems. Besteling Check"
objConn.close
End Function


' // ========================================================================
' // Check Amount
' // ------------------------------------------------------------------------
' // Checked if the item amount exist
' // and check if the item is smaller then Max_Count 
' // ========================================================================
Function CheckAmount(strOrder,ByRef strAmountError)
Dim arrCheckAmount,i

g_objDebugger.WriteLine ">> CheckAmount"

arrCheckAmount = Split(strOrder, " ")
For i = 1 to Ubound(arrCheckAmount)
	
If 		(Mid(arrCheckAmount(i),3)) = null then
		g_objDebugger.WriteLine "<< CheckAmount.   The item amount is empty"		
		CheckAmount = false
		Exit Function
ElseIf IsNumeric(Mid(arrCheckAmount(i),3))  Then
		If	CInt(Mid(arrCheckAmount(i),3)) > MAX_ORDER  Then
			strAmountError =  " The maximum amount is "&  MAX_ORDER & ": " & arrCheckAmount(i)
			g_objDebugger.WriteLine "<< CheckAmount.   "& strAmountError 
			CheckAmount = False
			Exit Function
		ElseIf (Mid(arrCheckAmount(i),3)) = 0  Then		
			strAmountError = " Amount is 0"
			g_objDebugger.WriteLine "<< CheckAmount.   "& strAmountError 
			CheckAmount = False
			Exit Function
		Else 	
			CheckAmount = True
			End If
Else
		strAmountError = (Mid(arrCheckAmount(i),3)) & " is incorrect"	
		
		g_objDebugger.WriteLine "<< CheckAmount.   Doesn't exist "& strAmountError 
		CheckAmount = false
		Exit Function
		End If
next
CheckAmount = true 
g_objDebugger.WriteLine "<< CheckAmount. Amount Check."
End Function 


' // ========================================================================
' // Get Order
' // ------------------------------------------------------------------------
' // Find the highste OrderNR for making a new OrderNR.
' // ========================================================================
Function GetOrder()
Dim objConn, RS 

g_objDebugger.WriteLine ">> GetOrder."

Set objConn = CreateObject("ADODB.Connection")
objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & STR_DATABASE & ";"
Set RS = objConn.Execute( "SELECT MAX(OrderNR) AS OrderNR FROM OrderEntries"  )
    
If RS.EOF Then
     GetOrder = 0
Else
    GetOrder = RS("OrderNR") 
End If
GetOrder = GetOrder  + 1
g_objDebugger.WriteLine "<< GetOrder. Order Id = " & GetOrder
objConn.Close
End Function    


' // ========================================================================
' // InputOrder 
' // ------------------------------------------------------------------------
' // Insert the message into the database.
' // ========================================================================
Function InputOrder (arrSmsText,strOrder,strTable)
Dim objConn ,strQuery ,countOrderArraye ,i ,arrCheckAmount

g_objDebugger.WriteLine ">> InputOrder."

arrCheckAmount = Split(arrSmsText, " ")
For i = 1 to Ubound(arrCheckAmount)
    Set objConn = CreateObject("ADODB.Connection")
    objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & STR_DATABASE & ";"
	strQuery = "INSERT INTO OrderEntries (TableNR,OrderNR,Amount,CodeDrink ) VALUES ( " & _
	           strTable & "," & strOrder & "," & (Mid(arrCheckAmount(i),3)) & _
			   ", '" & (left(arrCheckAmount(i),2)) & " ')"
	objConn.Execute( strQuery )
next
   objConn.Close
   Set objConn = Nothing
   g_objDebugger.WriteLine "<< InputOrder.   Order complete" 

End Function 


' // ========================================================================
' // Confirm Message
' // ------------------------------------------------------------------------
' // Get the messenge form the database
' // ========================================================================
Function ConfirmOrder(CheckOrder)
Dim  objConn ,RS ,strReservations ,strTotal ,strCalculateTotal ,strOrderNumber,strQuery

g_objDebugger.WriteLine ">> ConfirmOrder. " 


Set objConn = CreateObject("ADODB.Connection")
objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & STR_DATABASE & ";"
strQuery = "SELECT OrderEntries.TableNR AS TableNR, OrderEntries.Amount AS Amount, " & _
           ProductCode.CodeName AS CodeName, ProductCode.Price AS Price " &_   
           "FROM  (OrderEntries INNER JOIN ProductCode ON OrderEntries.CodeDrink = ProductCode.Code)" &_
           "WHERE(OrderEntries.OrderNR = "& CheckOrder & " )" &_
           "ORDER BY OrderEntries.CodeDrink"    
Set RS = objConn.Execute( strQuery )
strOrderNumber = " Order: " & CheckOrder &" TableNumber: "  & RS("TableNR") 
While  not RS.EOF    
	 strCalculateTotal = (RS("Amount") * RS( "Price" ))
     strReservations = strReservations &  RS("Amount") & " " & RS( "CodeName" ) & " Cost per Each: " & _
	 RS( "Price" ) & ". Total: "& strCalculateTotal &"  "
     strTotal = strTotal + strCalculateTotal
	 RS.MoveNext
WEnd
objConn.Close
ConfirmOrder = strOrderNumber & "   " & strReservations & " Order Total " & strTotal
g_objDebugger.WriteLine "<< ConfirmOrder. " &  ConfirmOrder

End Function 

Footer Navigation

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