Sample ASP script for SMS Order System

The following script is called by Ozeki NG when a new SMS message arrives. The script processes the text of the incoming SMS messages and creates an SQL INSERT statement that is used to place a record into the orders database table. This script is an attachment of the ASP SMS Order System case study.

Download: asp_sms_example_order_system.zip

The script should be saved into the following director:

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

<%@ Page Language="C#"%>
<%@ Import Namespace="System" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>

<%
//************************************************************
//Copy the properties of the incoming SMS into local variables
//************************************************************

string sender = Request.QueryString["sender"];
string receiver = Request.QueryString["receiver"];
string messagedata = Request.QueryString["messagedata"];
string messageid = Request.QueryString["messageid"];
string messagetype = Request.QueryString["messagetype"];
string senttime = Request.QueryString["senttime"];
string receivedtime = Request.QueryString["receivedtime"];
string serviceprovider = Request.QueryString["operatornames"];

//************************************************************
//Process incoming message
//example: "Order: Lipton 1 box, Nescafe 2 box, Sugar 10 kg";
//************************************************************
bool messageFormatOk = true;


string columnames = "";
string values = "";
string sql = "";
//messagedata = "Order: Lipton 1 box, Nescafe 2 box, Sugar 10 kg";

messagedata = messagedata.ToLower();
if (messagedata.StartsWith("order:")) messagedata = messagedata.Substring(6);
try
{
        string[] items = messagedata.Trim().Split(new char[] { ',', ';' });
        for (int x = 0; x < items.Length; x++)
        {
                string[] itementry = items[x].Trim().Split(new char[] { ' ' }, 2);
                string itemname = itementry[0];
                string itemquantity = itementry[1];

                if ((itemname == "lipton") || (itemname == "nescafe") || (itemname == "sugar"))
                {
                        if (columnames.Length > 0) columnames += ",";
                        columnames += itemname;
                        if (values.Length > 0) values += ",";
                        values += "'" + itemquantity + "'";
                }
                else
                {
                        //invalid item name
                        messageFormatOk = false;
                }
        }

        if ((columnames.Length > 0) && (messageFormatOk)) {
                sql = "INSERT INTO orders (shop,orderdate," + columnames + ") " +
                          "VALUES ('"+sender+"',getdate()," + values + ")";
        }
}
catch
{
        messageFormatOk = false;
}

//************************************************************
//Connect to the database and insert the record
//************************************************************
if (messageFormatOk)
{
        string sServer = ".\\SQLEXPRESS";
        string sUser="ozekiuser";
        string sPwd="ozekipass";
        string sDB="ozeki";
        string sConStr = "Server="+sServer+";User ID=" + sUser + ";"+
        "password=" + sPwd + ";Database=" + sDB        +";Persist Security Info=True";

        SqlConnection conn = new SqlConnection();
        conn.ConnectionString = sConStr;
        conn.Open();
        SqlCommand cmd = conn.CreateCommand();
        cmd.CommandText = sql;
        cmd.ExecuteNonQuery();
        conn.Close();
}

//************************************************************
//create the response message(s) in the following format:
//http://www.ozekisms.com/index.php?owpn=355
//************************************************************
string respmsg = "";
if (messageFormatOk) {
    respmsg = "Your order has been accepted at " + receivedtime;
} else {
    respmsg = "Invalid format. Please send you order again. Correct "+
    "format example: Lipton 1 box, Nescafe 2 box, Sugar 10 kg";
}

string destnum = sender;
string resptype = "SMS:TEXT";
string resp = "{"+resptype+"}{}{}{"+destnum+"}{"+respmsg+"}";

Response.Write(resp);

%>