<%@ Page Language="C#"%>
<%@ Import Namespace="System" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.Common" %>
<%@ 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"];

    string respmsg = "Hello, the following message was received: "+messagedata+
                     ". The message was sent at " + senttime;

/************************************************************
* Connect to the database
***********************************************************/
    bool connectedToDb;
    SqlConnection conn = new SqlConnection();;

    try
    {
        string sServer = ".\\SQLEXPRESS";
        string sUser = "ozekiuser";
        string sPwd = "ozekipass";
        string sDB = "ozeki";
        string connectionString = "Server=" + sServer + ";User ID=" + sUser + ";" +
        "password=" + sPwd + ";Database=" + sDB + ";Persist Security Info=True";

        conn.ConnectionString = connectionString;
        conn.Open();
        connectedToDb = true;
    }
    catch (Exception e)
    {
        respmsg = "Cannot connect to database." + e.Message;
        connectedToDb = false;
    }

/************************************************************
* Create database tables if they do not exist
***********************************************************/

    if (connectedToDb)
    {
        string sql1 =
        "if not exists(select * from INFORMATION_SCHEMA.tables where TABLE_NAME = 'pincode') "+
            "  CREATE TABLE pincode ( "+
                "id int IDENTITY (1,1),"+
                "pincode varchar(30),"+
                "response varchar(160),"+
                "used varchar(1)"+
                ")";

        try {
            SqlCommand cmd1 = conn.CreateCommand();
            cmd1.CommandText = sql1;
            cmd1.ExecuteNonQuery();
        } catch (Exception exp) {
            respmsg = "Cannot create database table: "+exp.Message;
        }
    }

/************************************************************
* Put some test data into the database
***********************************************************/
    if (connectedToDb)
    {
        string sql2 =
        "if not exists(select * from pincode) " +
            "begin " +
            "  INSERT INTO pincode (pincode,response,used ) VALUES ('3345','The treasure is behind the picture on the wall','0');" +
            "  INSERT INTO pincode (pincode,response,used ) VALUES ('4578','The treasure is under the pillow','0');" +
            "end";

        try {
            SqlCommand cmd2 = conn.CreateCommand();
            cmd2.CommandText = sql2;
            cmd2.ExecuteNonQuery();
        } catch (Exception exp) {
            respmsg = "Cannot create test data: "+exp.Message;
        }
    }

/************************************************************
* Process incoming message
***********************************************************/
    if (connectedToDb)
    {
        string pincode = messagedata.ToLower().Trim();

        string sql3 = "select used,response from pincode where pincode='" + pincode + "'";
        try
        {
            SqlCommand cmd3 = conn.CreateCommand();
            cmd3.CommandText = sql3;
            DbDataReader reader = cmd3.ExecuteReader();
            if (!reader.Read())
            {
                respmsg = "Invalid pin code '" + pincode + "'.";
                reader.Close();
            }
            else
            {
                string used = reader.GetValue(0).ToString();
                string treasurelocation = reader.GetValue(1).ToString();
                reader.Close();

                if (used != "0")
                {
                    respmsg = "The PIN code is correct, but this treasure has been taken.";
                }
                else
                {
                    respmsg = treasurelocation;
                    //update the database to mark the treasure as taken
                    SqlCommand cmd4 = conn.CreateCommand();
                    cmd4.CommandText = "update pincode set used='1' where pincode='" + pincode + "'";
                    cmd4.ExecuteNonQuery();
                }
            }
            //Close database connection
            conn.Close();

        }
        catch (Exception exp)
        {
            respmsg = "Invalid pin code. It could not be processed by the database. " + exp.Message;
        }
    }


//************************************************************
//create the response message(s). Format description:
//http://www.ozekisms.com/index.php?owpn=355
//************************************************************

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

Response.Write(resp);

%>