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.
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.
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 or Windows 10
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.
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 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).
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).
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
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.
