In this article I am going to explain a project report
on R- Airline Booking Engine. Here in this project report I am going to explain
the functionality of an online booking engine like we have goibibo, yatra.com
etc...
Here in this project you can book your airline ticket,
can see your booking history, can make notification and can copy your previous
travel details.
I developed this application by using asp.net, c#, SQL
Server, Java Script & jQuery.
Now I am going to explain Data Base:
Below is my table's explanation:
Table
1# Employee (To keep information of registered
user)

Image 1.
Table
2# BookingDetail
(To keep booking information)

Image 2.
Table
3# Airport (To keep Airport information)

Image 3.
Table
4# Carrier (To keep Airline and their country
information)

Image 4.
Below
is the Script file of my Data Base:
/****** Object:
Table [dbo].[Employee] Script
Date: 11/19/2016 14:04:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Employee](
[EMP_CODE]
[int] IDENTITY(1,1) NOT NULL,
[Email]
[varchar](500) NOT NULL,
[First_Name]
[varchar](50) NOT NULL,
[Last_Name]
[varchar](50) NULL,
[Password]
[varchar](50) NOT NULL,
[Supervisor_Code]
[varchar](500) NULL,
[DOB]
[varchar](50) NULL,
[PreferredCarrier]
[varchar](200) NULL,
CONSTRAINT [PK_Employee] PRIMARY
KEY CLUSTERED
(
[EMP_CODE] ASC,
[Email] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS
= ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:
Table [dbo].[Carrier] Script
Date: 11/19/2016 14:04:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Carrier](
[CarrierID]
[int] IDENTITY(1,1) NOT NULL,
[Airline]
[varchar](500) NULL,
[Country]
[varchar](50) NULL,
CONSTRAINT [PK_Carrier] PRIMARY
KEY CLUSTERED
(
[CarrierID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS
= ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:
Table [dbo].[BookingDetail]
Script Date: 11/19/2016 14:04:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[BookingDetail](
[Booking_ID]
[int] IDENTITY(1,1) NOT NULL,
[Emp_Code]
[varchar](500) NOT NULL,
[Source]
[varchar](50) NULL,
[Destination]
[varchar](50) NULL,
[Travelling_Date]
[varchar](50) NULL,
[NoOfPassenger]
[int] NOT NULL,
[Booking_Date]
[varchar](50) NULL,
[TravelClass]
[varchar](10) NULL,
[PreferredCarrier]
[varchar](100) NULL,
[Remarks]
[text] NULL,
CONSTRAINT [PK_BookingDetail] PRIMARY KEY CLUSTERED
(
[Booking_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS
= ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON
[PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:
Table [dbo].[Airport] Script
Date: 11/19/2016 14:04:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Airport](
[AirportID]
[int] IDENTITY(1,1) NOT NULL,
[AirportCD]
[varchar](50) NULL,
[AirportName]
[varchar](50) NULL,
CONSTRAINT [PK_Airport] PRIMARY
KEY CLUSTERED
(
[AirportID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS
= ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
Now open Visual Studio
and create a new project:
I am going to create this project as 3 layer
application:
1.
R-AirlineBookingEngine.DAL
2.
R-AirlineBookingEngine.Models
3.
R-AirlineBookingEngine.Web

Image 5.
In R-AirlineBookingEngine.DAL
project I added Entity Framework with the name of TravelTrker.edmx

Image 6.
Here I am going to expose Data base entity with own
Request/Response Model:
So I added an interface and their class here:
IUnitOfWork.cs
using System;
using
System.Collections.Generic;
using System.Linq;
using System.Text;
using
System.Threading.Tasks;
using System.Data;
using
System.Collections.Generic;
using
NTL.Models.NTLTravel;
namespace NTL.DAL.UnitOfWork
{
public interface IUnitOfWork
{
string validateLogin(string email, string password);
string NewRegistration(string email, string password, string firstName,
string lastName, string supervisorCode, string dob, string preferedCarrier);
List<BookingHistory> ManageBooking(string TravelType, string Source,string Destination,
string TxtDepartDate, string TxtReturnDate, int TxtNoOfTravellers, string TravelClass,
string UserID, string Remarks);
List<BookingHistory>
BindBookingHistory(string UserID);
string GetSupervisorInformation(string prefix);
List<KeyValuePair<string, string>>
GetPreferedCarrier(string prefix);
List<KeyValuePair<string, string>>
GetSourceDestination(string prefix);
}
}

Image 7.
Now
UnitOfWork.cs
using
NTL.Models.NTLTravel;
using System;
using
System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Net.Mail;
using System.Text;
using
System.Threading.Tasks;
using System.Web;
namespace NTL.DAL.UnitOfWork
{
public class UnitOfWork : IUnitOfWork
{
private TravelTrackerEntities context = null;
public UnitOfWork()
{
context = new TravelTrackerEntities();
}
public string validateLogin(string email, string password)
{
UserLogin loginView = new UserLogin();
using (TravelTrackerEntities context = new TravelTrackerEntities())
{
var query = from a in
context.Employee.Where(rec => rec.Email.Equals(email) &&
rec.Password.Equals(password))
select new
{
a.EMP_CODE,
a.First_Name,
a.Last_Name,
a.Supervisor_Code,
a.Email
};
if (query != null)
{
if (query.Count() >
0)
return "Login
Success-"
+ query.FirstOrDefault().First_Name + " " +
query.FirstOrDefault().Last_Name;
}
}
return "Login Failed";
}
public string NewRegistration(string email, string password, string firstName, string lastName, string supervisorCode, string dob, string preferedCarrier)
{
using (TravelTrackerEntities context = new TravelTrackerEntities())
{
var query = from a in
context.Employee.Where(rec => rec.Email.Equals(email) &&
rec.Password.Equals(password))
select new
{
a.EMP_CODE,
a.First_Name,
a.Last_Name,
a.Supervisor_Code,
a.Email
};
if (query != null)
{
if (query.Count() >
0)
{
return "User already
exist";
}
else
{
Employee emp = new Employee();
emp.Email = email;
emp.Password =
password;
emp.First_Name =
firstName;
emp.Last_Name =
lastName;
emp.Supervisor_Code =
supervisorCode;
emp.DOB = dob;
emp.PreferredCarrier =
preferedCarrier;
context.Employee.Add(emp);
context.SaveChanges();
return "Registration
Success";
}
}
}
return "Registration Failed";
}
public List<BookingHistory> ManageBooking(string TravelType, string Source, string Destination,
string TxtDepartDate, string TxtReturnDate, int TxtNoOfTravellers, string TravelClass, string UserID, string Remarks)
{
List<BookingHistory> details = new List<BookingHistory>();
using (TravelTrackerEntities context = new TravelTrackerEntities())
{
var query = from a in
context.Employee.Where(rec => rec.Email.Equals(UserID))
select new
{
a.EMP_CODE,
a.First_Name,
a.Last_Name,
a.Supervisor_Code,
a.Email
};
if (query != null)
{
if (query.Count() >
0)
{
BookingDetail book = new BookingDetail();
book.Emp_Code = UserID;
book.Source = Source;
book.Destination =
Destination;
book.Travelling_Date =
TxtDepartDate;
book.NoOfPassenger =
TxtNoOfTravellers;
book.Booking_Date = System.DateTime.Now.ToString();
book.TravelClass =
TravelClass;
book.Remarks = Remarks;
context.BookingDetail.Add(book);
context.SaveChanges();
if (TravelType == "Round
Trip")
{
BookingDetail bookReturn = new BookingDetail();
bookReturn.Emp_Code
= UserID;
bookReturn.Source =
Destination;
bookReturn.Destination = Source;
bookReturn.Travelling_Date = TxtReturnDate;
bookReturn.NoOfPassenger = TxtNoOfTravellers;
bookReturn.Booking_Date = System.DateTime.Now.ToString();
bookReturn.TravelClass = TravelClass;
bookReturn.Remarks
= Remarks;
context.BookingDetail.Add(bookReturn);
context.SaveChanges();
}
//Getting Supervisor
Informaiton
//======================================
int SuperVisorCode = Convert.ToInt32(query.FirstOrDefault().Supervisor_Code);
var supervisorInfo = from a in
context.Employee.Where(rec => rec.EMP_CODE.Equals(SuperVisorCode))
select new
{
a.EMP_CODE,
a.First_Name,
a.Last_Name,
a.Supervisor_Code,
a.Email
};
string supervisorName =
supervisorInfo.FirstOrDefault().First_Name + " " + supervisorInfo.FirstOrDefault().Last_Name;
string bookUserName =
query.FirstOrDefault().First_Name + " " +
query.FirstOrDefault().Last_Name;
string supervisorEmail =
supervisorInfo.FirstOrDefault().Email;
SendMail(supervisorName,
bookUserName, supervisorEmail, Source, Destination, TxtDepartDate);
//======================================
}
}
var bookingHistory = from a in
context.BookingDetail.Where(rec => rec.Emp_Code.Equals(UserID))
select new
{
a.Booking_ID,
a.Emp_Code,
a.Source,
a.Destination,
a.Travelling_Date,
a.NoOfPassenger,
a.Booking_Date,
a.TravelClass
};
foreach (var item in bookingHistory)
{
BookingHistory bookObj = new BookingHistory();
bookObj.Booking_ID =
item.Booking_ID;
bookObj.Emp_Code =
item.Emp_Code;
bookObj.Source =
item.Source;
bookObj.Destination =
item.Destination;
bookObj.Travelling_Date =
item.Travelling_Date;
bookObj.NoOfPassenger =
item.NoOfPassenger;
bookObj.Booking_Date =
item.Booking_Date;
bookObj.TravelClass =
item.TravelClass;
details.Add(bookObj);
}
}
return details;
}
public List<BookingHistory>
BindBookingHistory(string UserID)
{
List<BookingHistory> details = new List<BookingHistory>();
using (TravelTrackerEntities context = new TravelTrackerEntities())
{
var bookingHistory = from a in
context.BookingDetail.Where(rec => rec.Emp_Code.Equals(UserID))
select new
{
a.Booking_ID,
a.Emp_Code,
a.Source,
a.Destination,
a.Travelling_Date,
a.NoOfPassenger,
a.Booking_Date,
a.TravelClass
};
foreach (var item in bookingHistory)
{
BookingHistory bookObj = new BookingHistory();
bookObj.Booking_ID =
item.Booking_ID;
bookObj.Emp_Code =
item.Emp_Code;
bookObj.Source =
item.Source;
bookObj.Destination =
item.Destination;
bookObj.Travelling_Date =
item.Travelling_Date;
bookObj.NoOfPassenger =
item.NoOfPassenger;
bookObj.Booking_Date =
item.Booking_Date;
bookObj.TravelClass =
item.TravelClass;
details.Add(bookObj);
}
}
return details;
}
public void SendMail(string SupervisorName, string name, string supervisorEmail, string source, string destination, string travellingDate)
{
try
{
//Sending Mail
MailMessage mail = new MailMessage();
mail.To.Add(supervisorEmail);
mail.From = new MailAddress("fromMail@gmail.com");
mail.Subject = "New Ticket
Booking";
string Body =
MailBody(SupervisorName, name, source, destination, travellingDate);
mail.Body = Body;
mail.IsBodyHtml = true;
SmtpClient smtp = new SmtpClient();
smtp.Host = "smtp.gmail.com";
smtp.Port = 587;
smtp.UseDefaultCredentials = false;
smtp.Credentials = new System.Net.NetworkCredential
("fromMail@gmail.com", "Password");// Enter seders User
name and password
smtp.EnableSsl = true;
smtp.Send(mail);
}
catch (Exception ex)
{
}
}
public string MailBody(string SupervisorName, string name, string source, string destination, string travellingDate)
{
StreamReader reader = new StreamReader(HttpContext.Current.Server.MapPath("~/MailContent.html"));
string readFile = reader.ReadToEnd();
string StrContent = "";
StrContent = readFile;
StrContent = StrContent.Replace("[MyName]",
SupervisorName).Replace("[UserName]", name).
Replace("[Source]", source).Replace("[Destination]",destination).Replace("[TravelDate]", travellingDate);
return StrContent;
}
public string GetSupervisorInformation(string prefix)
{
string record = string.Empty;
int SuperVisorCode = Convert.ToInt32(prefix);
using (TravelTrackerEntities context = new TravelTrackerEntities())
{
var matches = from m in context.Employee
where
m.EMP_CODE.Equals(SuperVisorCode)
select new
{
m.First_Name,
m.Email
};
foreach (var item in matches)
{
record = item.First_Name + "-" + item.Email;
}
}
return record;
}
public List<KeyValuePair<string, string>>
GetPreferedCarrier(string prefix)
{
var list = new List<KeyValuePair<string, string>>();
using (TravelTrackerEntities context = new TravelTrackerEntities())
{
var matches = from m in context.Carrier
where
m.Airline.StartsWith(prefix)
select new
{
m.CarrierID,
m.Airline,
m.Country
};
foreach (var item in matches)
{
list.Add(new KeyValuePair<string, string>(item.Airline.ToString(),
item.Country));
}
}
return list;
}
public List<KeyValuePair<string, string>>
GetSourceDestination(string prefix)
{
var list = new List<KeyValuePair<string, string>>();
using (TravelTrackerEntities context = new TravelTrackerEntities())
{
var matches = from m in context.Airport
where
m.AirportName.StartsWith(prefix)
select new
{
m.AirportName,
m.AirportCD
};
foreach (var item in matches
{
list.Add(new KeyValuePair<string, string>(item.AirportName.ToString(),
item.AirportCD));
}
}
return list;
}
}
}
Below
is my Mail Content Page:
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>R- Airline Booking Engine</title>
</head>
<body>
<table style="font-family:Calibri;background:#E6E6E6">
<tr>
<td>
<p>Dear
[SupervisorName]</p>
<p>[UserName] booked a
new ticket from [Source] to [Destination]
using R- Airline Booking
Engine.</p>
<p>Travel Date
[TravelDate]</p>
</td>
</tr>
<tr>
<td>
Thanks & Regards,
Admin@RAirlineBookingEngine.com
</td>
</tr>
</table>
</body>
</html>
Now I am going to explain the working of my project:
When you run this project then first page will be
login:

Image 8.
Registration Page:

Image 9.
After making registration or login you will redirect to
Booking airline page:
Here In source and destination I am auto suggest
feature…

Image 10.

Image 11.

Image 12.

Image 13.
After this message page will redirect to booking
history page:

Image 14.
From here you can copy any previous ticket and you can
also set reminder.
Now I am showing how data is storing in my tables:

Image 15.