cancel ticket on seat wise basis in a PNR

the below code is for cancel ticket as PNR basis. in this cancellation process all the seat in this PNR are cancelled. i want a particular seat is cancelled in the pNR which is taken many seat.

please help me build this code:







using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Configuration;


public partial class Admin_Cancel_Ticket : System.Web.UI.Page
{
    SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["Connection"].ConnectionString);
    SqlCommand cmd = null;
    SqlDataReader dr = null;
    string query = string.Empty;
    protected void Page_Load(object sender, EventArgs e)
    {

        Check c = new Check();
        
        c.check_admin_state();
        if (con.State == System.Data.ConnectionState.Open)
        {
            con.Close();
        }
        con.Open();
        if (!IsPostBack)
        {
            if (Request.Cookies["LOGIN"].Value == "ADMIN")
            {
                query = "SELECT DISTINCT OrderDetails.Pnr, OrderDetails.TotalAmount, OrderDetails.BookedDate, OrderDetails.seatinfo FROM         OrderDetails INNER JOIN                       orders ON OrderDetails.Pnr = orders.Pnr WHERE     (OrderDetails.Flag = '1')";
                SqlDataSource1.SelectCommand = query;
                GridView1.DataBind();
            }
            else if(Request.Cookies["LOGIN"].Value == "AGENT")
            {
                query = "SELECT DISTINCT OrderDetails.Pnr, OrderDetails.TotalAmount, OrderDetails.BookedDate, OrderDetails.seatinfo FROM         OrderDetails INNER JOIN                       orders ON OrderDetails.Pnr = orders.Pnr WHERE     (OrderDetails.Flag = '1') AND ( Bookedtype ='" + Request.Cookies["USERNAME"].Value + "') And orders.orderstatus ='Active'";
                SqlDataSource1.SelectCommand = query;
                GridView1.DataBind();
            }
        }
    }

    /*protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)
    {
       
        if (e.CommandName == "pnr")
        {
            int index = Convert.ToInt32(e.CommandArgument);
            GridViewRow row = GridView1.Rows[index];
            string var = Server.HtmlEncode(row.Cells[1].Text);
            cmd = new SqlCommand("delete_agent", con);
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.Parameters.Add("agent_id", System.Data.SqlDbType.VarChar).Value = var;
            cmd.ExecuteNonQuery();
            Response.Write("");
            query = "select Pnr,BookedDate as Booked_Date_Time,TotalAmount,seatinfo from orderdetails where Flag = '1'";
            SqlDataSource1.SelectCommand = query;
            GridView1.DataBind();


        }
    }*/


    protected void GridView1_SelectedIndexChanging(object sender, GridViewSelectEventArgs e)
    {
          GridView1.PageIndex = e.NewSelectedIndex;
          GridView1.DataBind();
    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        if (Request.Cookies["LOGIN"].Value == "ADMIN")
        {
            query = "SELECT DISTINCT OrderDetails.Pnr, OrderDetails.TotalAmount, OrderDetails.BookedDate, OrderDetails.seatinfo FROM         OrderDetails INNER JOIN                       orders ON OrderDetails.Pnr = orders.Pnr WHERE     (OrderDetails.Flag = '1') and orderdetails.pnr='" + PNR.Text + "'";
            SqlDataSource1.SelectCommand = query;
            GridView1.DataBind();
        }
        else if (Request.Cookies["LOGIN"].Value == "AGENT")
        {
            query = "SELECT DISTINCT OrderDetails.Pnr, OrderDetails.TotalAmount, OrderDetails.BookedDate, OrderDetails.seatinfo FROM         OrderDetails INNER JOIN                       orders ON OrderDetails.Pnr = orders.Pnr WHERE     (OrderDetails.Flag = '1') and orderdetails.pnr='" + PNR.Text + "' and orders.Bookedtype ='" + Request.Cookies["USERNAME"].Value + "'";
            SqlDataSource1.SelectCommand = query;
            GridView1.DataBind();
        }

    }
    protected void GridView1_SelectedIndexChanged(object sender, EventArgs e)
    {
        GridView1.DataBind();
    }
    protected void Show_All_Click(object sender, EventArgs e)
    {
        if (Request.Cookies["LOGIN"].Value == "ADMIN")
        {
            query = "SELECT DISTINCT OrderDetails.Pnr, OrderDetails.TotalAmount, OrderDetails.BookedDate, OrderDetails.seatinfo FROM         OrderDetails INNER JOIN                       orders ON OrderDetails.Pnr = orders.Pnr WHERE     (OrderDetails.Flag = '1')";
            SqlDataSource1.SelectCommand = query;
            GridView1.DataBind();
        }
        else if (Request.Cookies["LOGIN"].Value == "AGENT")
        {
            query = "SELECT DISTINCT OrderDetails.Pnr, OrderDetails.TotalAmount, OrderDetails.BookedDate, OrderDetails.seatinfo FROM         OrderDetails INNER JOIN                       orders ON OrderDetails.Pnr = orders.Pnr WHERE     (OrderDetails.Flag = '1') and (orders.Bookedtype ='" + Request.Cookies["USERNAME"].Value + "') And orders.orderstatus ='Active'";
            SqlDataSource1.SelectCommand = query;
            GridView1.DataBind();
        }
    }
    protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
    {
        string s = GridView1.Rows[e.RowIndex].Cells[1].Text;
        SqlDataSource1.DeleteCommand = "dummy";

        cmd = new SqlCommand("cancelation_refund", con);
        cmd.CommandType = System.Data.CommandType.StoredProcedure;
        cmd.Parameters.Add("pnr", System.Data.SqlDbType.VarChar).Value = s;
               dr = cmd.ExecuteReader();
        if (dr.Read())
        {
            if (dr.GetInt32(0) >= 4)
            {
                get_Refund(s, 85);
                Response.Write("");

            }
            else if ((dr.GetInt32(0) <= 4) && (dr.GetInt32(0) >= 1))
            {
                get_Refund(s, 50);
                Response.Write("");

            }
            else if (dr.GetInt32(0) <= 1)
            {
                get_Refund(s, 0);
                Response.Write("");

            }
        }

        Response.Redirect("Cancel_Ticket.aspx");
    }

    protected void get_Refund(string pnr, int percentage)
    {
        if (!check_customer(pnr))
        {

            cmd = new SqlCommand("select distinct(emailid) from orders where pnr='" + pnr + "'", con);
            dr = cmd.ExecuteReader();
            if (dr.Read())
            {

                try
                {
                    SqlCommand canc_cmd = new SqlCommand("generate_canceled_tickets_time", con);
                    canc_cmd.CommandType = System.Data.CommandType.StoredProcedure;
                    canc_cmd.Parameters.Add("pnr", System.Data.SqlDbType.VarChar).Value = pnr;
                    canc_cmd.Parameters.Add("percentage", System.Data.SqlDbType.Int).Value = percentage;
                    canc_cmd.ExecuteNonQuery();

                    cmd = new SqlCommand("rollback_on_failure", con);
                    cmd.CommandType = System.Data.CommandType.StoredProcedure;
                    cmd.Parameters.Add("pnr", System.Data.SqlDbType.VarChar).Value = pnr;
                    cmd.ExecuteNonQuery();
                    Response.Write("");

                    Load_data l = new Load_data();
                    l.send_mail(dr[0].ToString(), "Hello Sir/Madam

This mail is Regarding cancelation of your Ticket with Pnr " + pnr + ", and " + percentage + "% of your total amount will be refunded. thanks");
                    Response.Write("");

                }
                catch (Exception ex)
                {
                    Response.Write("");

                }

            }
        }
        else
        {
            cmd = new SqlCommand("select distinct(emailid) from orders where pnr='" + pnr + "'", con);
            dr = cmd.ExecuteReader();
            if (dr.Read())
            {

                try
                {
                    SqlCommand canc_cmd = new SqlCommand("generate_canceled_tickets_time", con);
                    canc_cmd.CommandType = System.Data.CommandType.StoredProcedure;
                    canc_cmd.Parameters.Add("pnr", System.Data.SqlDbType.VarChar).Value = pnr;
                    canc_cmd.Parameters.Add("percentage", System.Data.SqlDbType.Int).Value = percentage;
                    canc_cmd.ExecuteNonQuery();

                    cmd = new SqlCommand("rollback_on_failure", con);
                    cmd.CommandType = System.Data.CommandType.StoredProcedure;
                    cmd.Parameters.Add("pnr", System.Data.SqlDbType.VarChar).Value = pnr;
                    cmd.ExecuteNonQuery();
                    Response.Write("");

                    Load_data l = new Load_data();
                    l.send_mail(dr[0].ToString(), "Hello Sir/Madam

This mail is Regarding cancelation of your Ticket with Pnr " + pnr + ", and " + percentage + "% of your total amount will be refunded. thanks");
                    Response.Write("");

                }
                catch (Exception ex)
                {
                    Response.Write("");

                }

            }
        }


    }

    protected bool check_customer(string pnr)
    {
        cmd = new SqlCommand("select distinct(bookedtype) from orders where pnr='" + pnr + "' group by BookedType", con);
        dr = cmd.ExecuteReader();
        if (dr.Read())
        {
            if ((dr[0].ToString() != "CUSTOMER"))
            {
                return false;
            }
            else
            {
                return true;
            }

        }
        else
        {
            return false;
        }

    }
}