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("<script type='text/javascript'>alert(' Ticket " + var + " is sucessfully Cancelled'); </script>");
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("<script type='text/javascript'>alert(' Ticket " + s + " is Cancelled'); </script>");
}
else if ((dr.GetInt32(0) <= 4) && (dr.GetInt32(0) >= 1))
{
get_Refund(s, 50);
Response.Write("<script type='text/javascript'>alert(' Ticket " + s + " is Cancelled'); </script>");
}
else if (dr.GetInt32(0) <= 1)
{
get_Refund(s, 0);
Response.Write("<script type='text/javascript'>alert(' Ticket " + s + " is Cancelled'); </script>");
}
}
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("<script type='text/javascript'>alert(' Ticket " + pnr + " is Cancelled'); </script>");
Load_data l = new Load_data();
l.send_mail(dr[0].ToString(), "Hello Sir/Madam<br><br> This mail is Regarding cancelation of your Ticket with Pnr " + pnr + ", and " + percentage + "% of your total amount will be refunded. thanks");
Response.Write("<script type='text/javascript'>alert('cancelation of your Ticket with Pnr " + pnr + ", and " + percentage + "% of your total amount should/Will be refunded'); </script>");
}
catch (Exception ex)
{
Response.Write("<script type='text/javascript'>alert('There is some Error in Sending the ticket to mail'); </script>");
}
}
}
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("<script type='text/javascript'>alert(' Ticket " + pnr + " is Cancelled'); </script>");
Load_data l = new Load_data();
l.send_mail(dr[0].ToString(), "Hello Sir/Madam<br><br> This mail is Regarding cancelation of your Ticket with Pnr " + pnr + ", and " + percentage + "% of your total amount will be refunded. thanks");
Response.Write("<script type='text/javascript'>alert('cancelation of your Ticket with Pnr " + pnr + ", and " + percentage + "% of your total amount should/Will be refunded'); </script>");
}
catch (Exception ex)
{
Response.Write("<script type='text/javascript'>alert('There is some Error in Sending the ticket to mail'); </script>");
}
}
}
}
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;
}
}
}