R - Airline Booking Engine

Here, in this article, I am going to explain the functionality of an online booking engine like we have in goibibo, yatra.com etc. In this project, you can book your airline ticket, can see your booking history, can make notifications, and can copy your previous travel details.

I developed this application by using ASP.NET, C#, SQL Server, JavaScript, & jQuery. Now, I am going to explain the database. 

Below is my table’s explanation.

Table 1# Employee (To keep information of registered user)



Table 2# BookingDetail (To keep booking information)



Table 3# Airport (To keep Airport information)



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



Given below is the Script file of my Database.

/****** 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 a three-layer application.

1. R-AirlineBookingEngine.DAL
2. R-AirlineBookingEngine.Models
3. R-AirlineBookingEngine.Web

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



Here, I am going to expose the Database Entity with my own Request/Response Model. So, I added an interface and its 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 ROnlineBookingEngine.Models.ROnlineBookingEngine1;

namespace ROnlineBookingEngine.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);
    }
}

UnitOfWork.cs

using ROnlineBookingEngine.Models.ROnlineBookingEngine1;
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 ROnlineBookingEngine.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();
                       &nbisp;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("[email protected]");
                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
                ("[email protected]", "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,
                [email protected]
            </td>
        </tr>
    </table>
</body>
</html>

Now, I am going to explain the working of my project. When you run this project, the login page will appear.



Registration Page



After making registration or login, you will be redirected to Booking airline page. Here, in source and destination, I am using auto suggestion feature.









After this, the message page will redirect to booking history page.



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