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.

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.

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:

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.

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)
);

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).

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

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.

More information