SMS Order System

This case study gives you an example on how you can use SMS messages to keep track of the stock of goods in your shops. It explains the simple idea to collect order information in SMS messages and to save this information in a database. The database can be used to do reports or automate business processes. The case study starts out by explaining the scenario, then it gives a step by step explanation on how to setup this system.

Introduction

The example presented here can be used to keep track of orders arriving from cafe shops around the country. In our example there are three cafe shops, one in Liverpool (tel: +44 703 111111), one in Glasgow (tel: +44 703 222222) and one in London (tel: +44 703 333333). Each of these cafe shops has a cellphone number that identifies the shop itself. If the stocks are running low in the shop, the shop owner can use the mobile phone to send an order in an SMS message (Figure 1). The order is received by the company SMS system. The SMS system processes the order and saves the data into a database.

usage scenario for the sms order system — Figure 1 - Usage scenario for SMS order system

The database layout for the order contains the shop telephone number, the time of the order and the quantities of the ordered products (Figure 2). This is a very simple layout to keep this example easy to understand. Of course you can change the database layout to accommodate your needs.

database layout for sms order system — Figure 2 - Database layout for SMS order system

The database table containing the orders can be stored in any database server, such as Oracle, Microsoft SQL Server, Access, Sybase, MySQL or other. You can even use Excel files.

Prerequisites

To setup this system you need the following:

A PC with Windows XP, Windows 2003, Windows Vista, Windows 2008, Windows 2008 R2,Windows 7, Windows 2012 R2, Windows 8, Windows 10 or Windows 11	We recommend to use a standard PC with Windows XP. The recommended hardware configuration can be found in the prerequisites section of our SMS Gateway product manual.
GSM Modem	A GSM Modem is simplified a mobile phone with a SIM card and an antenna. The GSM can be used to receive the SMS messages from the GSM network. It should be attached to your computer with a phone-to-pc data cable. The following webpage gives you information about the recommended models: recommended GSM modems. You can also use IP SMS connections, if you prefer to receive SMS messages over the Internet instead of a GSM modem. We recommend to use a GSM modem, because it is easy to setup and is very reliable.
Ozeki NG SMS Gateway	This software should be installed onto your PC. It will handle your GSM modem (or your IP SMS connection) and will do the SMS receiving. It can be downloaded from the following url: download SMS Gateway.
Database server	You can use Oracle, Microsoft SQL Server, Access, Sybase, MySQL or other. In the example we use Microsoft SQL Express, because it can be downloaded free of charge from Microsoft.

How does it work?

Once you have all the prerequisites in place the system will work the following way: The SMS message will be received by your GSM modem (Figure 3). Ozeki NG SMS Gateway will read the incoming SMS from the modem and will pass it to an ASP script. The ASP script will process the message. During the processing the ASP script will take the text of the SMS messages and will put the data in it into the database server.

system architecture for sms order system — Figure 3 - System architecture for SMS order system

Installation steps

To get this solution working you need to follow these steps. If everything goes well, you can get the system working in about 10 minutes. I assumed you have already downloaded and installed the latest version of Ozeki NG SMS Gateway. The steps here give you information on how you should configure Ozeki NG SMS Gateway to get your solution working.

Step 1 - Install and test your GSM Modem. The first step you should take is to attach your GSM modem to your computer with a phone-to-pc data cable. After this is done, you should configure the GSM Modem in Ozeki NG SMS Gateway and you should make sure it is working by sending a test SMS message from the user interface of Ozeki NG SMS Gateway.

Step 2 - Create your database layout In our example we use Microsoft SQL Express as our database servers. The database layout (Figure 4) consists of only a single database table. This database table can be created using an SQL CREATE TABLE definition in an SQL console. Detailed steps for creating the database table layout can be found in the SMS Order System Database Layout page.

CREATE TABLE orders (
 id int IDENTITY (1,1),
 shop varchar(30),
 orderdate varchar(160),
 nescafe varchar(160),
 lipton varchar(160),
 sugar varchar(160)
);

Figure 4 - Database table layout for SMS Order system

Step 3 - Configure the ASP script - The ASP script will process incoming SMS messages and will save data into the database. The script will be executed by Ozeki NG SMS Gateway whenever a new SMS comes in. To configure Ozeki NG SMS Gateway to execute this script you need to add an "ASP User" to the Ozeki NG configuration. This can be done by clicking on the "Add" menu item in the "Users and applications" menu, then you should click on "install" at the "ASP" section (Figure 5).

how to add an asp sms script to the sms gateway — Figure 5 - How to add an ASP SMS script to the SMS gateway

After the "ASP" application installation is started, you have to supply a name for the application. You can pick any name. In the example we use "ordersystem" (Figure 6).

how to set the asp sms application name — Figure 6 - How to set the ASP SMS application name

Step 4 - Create the ASP script - The final step is to edit the ASP script to implement the application logic, that will process the incoming SMS message. The ASP script that should be edited can be found at

C:\Program Files\Ozeki\OzekiNG - SMS
    Gateway\config\ordersystem\sample.aspx

33

you can edit this script with notepad or any other text editor. The source code of the script can be found in the sample ASP script for SMS Order System section.

How can I test the system?

The system can be tested easily. You can send an SMS message to the phone number of the GSM modem that is attached to your computer. After the message is sent, you should check your database table and hopefully the processed information from the received SMS messages will be there. :) Of course you will probably want to modify the ASP script, that does the processing according to your needs. If you modify the ASP script, you will find it useful during the development to see the raw script output. The script output that was generated when an SMS comes in can be found at the following location: "C:\Program Files\Ozeki\OzekiNG - SMS Gateway\config\ordersystem\output.htm";

Conclusion

The example presented in this chapter introduces a nice idea to use SMS messages to collect information from the field. It shows how you can use ASP to process the incoming information and save it in a database. It is useful if you wish to create similar solutions.