514 Project 4 CREATING AN AIRLINE RESERVATION PORTAL
lstClass.SelectedIndex=0; Cal1.SelectedDate=DateTime.Today; txtTNo.Text=””;
txtFare.Text=””; txtStatus.Text=””; txtOrg.Text=””; txtDest.Text=””; txtDepTime.Text=””; txtName.Text=””; txtEMail.Text=””; txtFltNo.Enabled=true; lstClass.Enabled=true; Cal1.Enabled=true;
Response.Redirect(“Ticket.aspx?TNo=” + TicketNo);
}
catch (Exception ex)
{
lblMessage.Text=ex.Message; sqlConnection1.Close(); txtFltNo.Enabled=true; lstClass.Enabled=true; Cal1.Enabled=true;
}
}
The CancelRes.aspx Form
The CancelRes.aspx form is used to perform cancellation of reservations. When a ticket is cancelled, the status of the flight needs to be updated in the dtFltStatus table. You also need to update the status of the passengers on the flight who are in the waiting list. In addition, you also need to compute the refund amount that is applicable to the passenger.
To perform cancellations, I have used a combination of stored procedures and programming logic. The steps to cancel a reservation are given as follows:
1.Retrieve the fare that the passenger had paid.
2.Compute the refund applicable to the customer, depending upon whether or not the flight has departed.
516 Project 4 CREATING AN AIRLINE RESERVATION PORTAL
dtReservations database. The code for the DeleteReservations stored procedure, which accomplishes these tasks, is given as follows:
CREATE PROCEDURE DeleteReservations
@ticketno char(10), @user char(15), @cancdate datetime, @refund int AS
Declare @fltno char(10)
Declare @date datetime
Declare @class char(10)
Declare @status int
select @fltno=FltNo, @date=DateOfJourney, @class=ClassOfRes , @status=Status from dtReservations where TicketNo=@ticketno
Update dtReservations
set Status=Status+1 where FltNo=@fltno and DateOfJourney=@date and ClassOfRes=@class and Status<@status
Update dtFltStatus
set Status=Status+1 where FltNo=@fltno and StatusDate=@date and StatusClass=@class
INSERT into dtCancellations
values (@ticketno, @refund, @user, @cancdate) Delete dtReservations where TicketNo=@ticketno GO
The QueryStat.aspx Form
The QueryStat.aspx page is used for querying the status of flights and tickets. LOB executives can either use the flight number to query the status of a flight or use the ticket number to query the status of a ticket. In both the cases, an error message is displayed if the number specified is invalid. Otherwise, the status of the flight or ticket is retrieved and displayed to the user.
The following code is used for querying the status of a flight:
private void Button2_Click(object sender, System.EventArgs e)
{
dataSet41.Clear(); lblMessage.Text=””; lblStatus.Text=””;
if (txtFltNo.Text==”” || txtFltNo.Text==null)
IMPLEMENTING THE BUSINESS LOGIC |
Chapter 21 |
517 |
|
|
|
|
|
{
lblMessage.Text=”Invalid flight number”; return;
}
else
{
sqlConnection1.Open(); sqlDataAdapter1.SelectCommand.Parameters[0].Value=txtFltNo.Text.Trim(); sqlDataAdapter1.SelectCommand.Parameters[1].Value=Cal1. SelectedDate
.ToShortDateString(); sqlDataAdapter1.SelectCommand.Parameters[2].Value= lstClass.SelectedItem
.Text;
sqlDataAdapter1.Fill(dataSet41, “FltStatus”); sqlConnection1.Close();
if (dataSet41.Tables[“FltStatus”].Rows.Count==0)
{
lblStatus.Text=”Status: Available”;
}
else
{
string strStatus; int status;
strStatus=dataSet41.Tables[“FltStatus”].Rows[0][0].ToString(); status=Convert.ToInt32(strStatus);
if (status >= 0)
{
lblStatus.Text=”Status: Available”;
}
else
{
lblStatus.Text=”Status: Overbooked (“ + strStatus + “)”;
}
}
}
}