SMS ordering system (The Green Bar)

1. Background

The Green Bar is Music bar in the center of San José, Costa Rica, they started back in 2004. It's a trendy bar where you can take your own music CD's and have the played. The playlist is visible on a large screen. 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

Like any trendy bar it can get very crowded later in the evening. This can cause long waiting times for the customer at the bar. Besides the long wait time they were also looking for a way to differentiate themselfes from other bars and increase general customer satisfaction.

This is why The Green Bar had decided to enable their customers to order their drinks through SMS. The customers would be able to send an SMS message to a special bar number specifying their order and one of the waiters would bring them their drinks.

3. Goals of the new System

The Green Bar had set the following goals for their new system:

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.

Greenbar Menu
Figure 1: The Green Bar menu

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