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.
  1. /****** Object:  Table [dbo].[Employee]    Script Date: 11/19/2016 14:04:23 ******/  
  2. SET ANSI_NULLS ON  
  3. GO  
  4. SET QUOTED_IDENTIFIER ON  
  5. GO  
  6. SET ANSI_PADDING ON  
  7. GO  
  8. CREATE TABLE [dbo].[Employee](  
  9.     [EMP_CODE] [int] IDENTITY(1,1) NOT NULL,  
  10.     [Email] [varchar](500) NOT NULL,  
  11.     [First_Name] [varchar](50) NOT NULL,  
  12.     [Last_Name] [varchar](50) NULL,  
  13.     [Password] [varchar](50) NOT NULL,  
  14.     [Supervisor_Code] [varchar](500) NULL,  
  15.     [DOB] [varchar](50) NULL,  
  16.     [PreferredCarrier] [varchar](200) NULL,  
  17.  CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED   
  18. (  
  19.     [EMP_CODE] ASC,  
  20.     [Email] ASC  
  21. )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ONON [PRIMARY]  
  22. ON [PRIMARY]  
  23. GO  
  24. SET ANSI_PADDING OFF  
  25. GO  
  26. /****** Object:  Table [dbo].[Carrier]    Script Date: 11/19/2016 14:04:23 ******/  
  27. SET ANSI_NULLS ON  
  28. GO  
  29. SET QUOTED_IDENTIFIER ON  
  30. GO  
  31. SET ANSI_PADDING ON  
  32. GO  
  33. CREATE TABLE [dbo].[Carrier](  
  34.     [CarrierID] [int] IDENTITY(1,1) NOT NULL,  
  35.     [Airline] [varchar](500) NULL,  
  36.     [Country] [varchar](50) NULL,  
  37.  CONSTRAINT [PK_Carrier] PRIMARY KEY CLUSTERED   
  38. (  
  39.     [CarrierID] ASC  
  40. )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ONON [PRIMARY]  
  41. ON [PRIMARY]  
  42. GO  
  43. SET ANSI_PADDING OFF  
  44. GO  
  45. /****** Object:  Table [dbo].[BookingDetail]    Script Date: 11/19/2016 14:04:23 ******/  
  46. SET ANSI_NULLS ON  
  47. GO  
  48. SET QUOTED_IDENTIFIER ON  
  49. GO  
  50. SET ANSI_PADDING ON  
  51. GO  
  52. CREATE TABLE [dbo].[BookingDetail](  
  53.     [Booking_ID] [int] IDENTITY(1,1) NOT NULL,  
  54.     [Emp_Code] [varchar](500) NOT NULL,  
  55.     [Source] [varchar](50) NULL,  
  56.     [Destination] [varchar](50) NULL,  
  57.     [Travelling_Date] [varchar](50) NULL,  
  58.     [NoOfPassenger] [intNOT NULL,  
  59.     [Booking_Date] [varchar](50) NULL,  
  60.     [TravelClass] [varchar](10) NULL,  
  61.     [PreferredCarrier] [varchar](100) NULL,  
  62.     [Remarks] [text] NULL,  
  63.  CONSTRAINT [PK_BookingDetail] PRIMARY KEY CLUSTERED   
  64. (  
  65.     [Booking_ID] ASC  
  66. )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ONON [PRIMARY]  
  67. ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]  
  68. GO  
  69. SET ANSI_PADDING OFF  
  70. GO  
  71. /****** Object:  Table [dbo].[Airport]    Script Date: 11/19/2016 14:04:23 ******/  
  72. SET ANSI_NULLS ON  
  73. GO  
  74. SET QUOTED_IDENTIFIER ON  
  75. GO  
  76. SET ANSI_PADDING ON  
  77. GO  
  78. CREATE TABLE [dbo].[Airport](  
  79.     [AirportID] [int] IDENTITY(1,1) NOT NULL,  
  80.     [AirportCD] [varchar](50) NULL,  
  81.     [AirportName] [varchar](50) NULL,  
  82.  CONSTRAINT [PK_Airport] PRIMARY KEY CLUSTERED   
  83. (  
  84.     [AirportID] ASC  
  85. )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ONON [PRIMARY]  
  86. ON [PRIMARY]  
  87. GO  
  88. SET ANSI_PADDING OFF  
  89. 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
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Text;  
  5. using System.Threading.Tasks;  
  6. using System.Data;  
  7. using System.Collections.Generic;  
  8. using ROnlineBookingEngine.Models.ROnlineBookingEngine1;  
  9.   
  10. namespace ROnlineBookingEngine.DAL.UnitOfWork  
  11. {  
  12.     public interface IUnitOfWork  
  13.     {  
  14.         string validateLogin(string email, string password);  
  15.   
  16.         string NewRegistration(string email, string password, string firstName,   
  17.             string lastName, string supervisorCode, string dob, string preferedCarrier);  
  18.   
  19.         List<BookingHistory> ManageBooking(string TravelType, string Source,string Destination,   
  20.             string TxtDepartDate, string TxtReturnDate, int TxtNoOfTravellers, string TravelClass,   
  21.             string UserID, string Remarks);  
  22.           
  23.         List<BookingHistory> BindBookingHistory(string UserID);  
  24.         string GetSupervisorInformation(string prefix);  
  25.         List<KeyValuePair<string, string>> GetPreferedCarrier(string prefix);  
  26.         List<KeyValuePair<string, string>> GetSourceDestination(string prefix);  
  27.     }  
  28. }  


UnitOfWork.cs
  1. using ROnlineBookingEngine.Models.ROnlineBookingEngine1;  
  2. using System;  
  3. using System.Collections.Generic;  
  4. using System.IO;  
  5. using System.Linq;  
  6. using System.Net.Mail;  
  7. using System.Text;  
  8. using System.Threading.Tasks;  
  9. using System.Web;  
  10.   
  11. namespace ROnlineBookingEngine.DAL.UnitOfWork  
  12. {  
  13.     public class UnitOfWork : IUnitOfWork  
  14.     {  
  15.         private TravelTrackerEntities context = null;  
  16.         public UnitOfWork()  
  17.         {  
  18.             context = new TravelTrackerEntities();  
  19.         }  
  20.   
  21.         public string validateLogin(string email, string password)  
  22.         {  
  23.             UserLogin loginView = new UserLogin();  
  24.             using (TravelTrackerEntities context = new TravelTrackerEntities())  
  25.             {  
  26.                 var query = from a in context.Employee.Where(rec => rec.Email.Equals(email) && rec.Password.Equals(password))  
  27.                             select new  
  28.                             {  
  29.                                 a.EMP_CODE,  
  30.                                 a.First_Name,  
  31.                                 a.Last_Name,  
  32.                                 a.Supervisor_Code,  
  33.                                 a.Email  
  34.                             };  
  35.                 if (query != null)  
  36.                 {  
  37.                     if (query.Count() > 0)  
  38.                         return "Login Success-" + query.FirstOrDefault().First_Name + " " + query.FirstOrDefault().Last_Name;  
  39.                 }  
  40.             }  
  41.             return "Login Failed";  
  42.         }  
  43.   
  44.         public string NewRegistration(string email, string password, string firstName, string lastName, string supervisorCode, string dob, string preferedCarrier)  
  45.         {  
  46.             using (TravelTrackerEntities context = new TravelTrackerEntities())  
  47.             {  
  48.                 var query = from a in context.Employee.Where(rec => rec.Email.Equals(email) && rec.Password.Equals(password))  
  49.                             select new  
  50.                             {  
  51.                                 a.EMP_CODE,  
  52.                                 a.First_Name,  
  53.                                 a.Last_Name,  
  54.                                 a.Supervisor_Code,  
  55.                                 a.Email  
  56.                             };  
  57.                 if (query != null)  
  58.                 {  
  59.                     if (query.Count() > 0)  
  60.                     {  
  61.                         return "User already exist";  
  62.                     }  
  63.                     else  
  64.                     {  
  65.                         Employee emp = new Employee();  
  66.                        &nbisp;emp.Email = email;  
  67.                         emp.Password = password;  
  68.                         emp.First_Name = firstName;  
  69.                         emp.Last_Name = lastName;  
  70.                         emp.Supervisor_Code = supervisorCode;  
  71.                         emp.DOB = dob;  
  72.                         emp.PreferredCarrier = preferedCarrier;  
  73.                         context.Employee.Add(emp);  
  74.                         context.SaveChanges();  
  75.                         return "Registration Success";  
  76.                     }  
  77.                 }  
  78.             }  
  79.             return "Registration Failed";  
  80.         }  
  81.   
  82.         public List<BookingHistory> ManageBooking(string TravelType, string Source, string Destination, string TxtDepartDate, string TxtReturnDate, int TxtNoOfTravellers, string TravelClass, string UserID, string Remarks)  
  83.         {  
  84.   
  85.             List<BookingHistory> details = new List<BookingHistory>();  
  86.             using (TravelTrackerEntities context = new TravelTrackerEntities())  
  87.             {  
  88.                 var query = from a in context.Employee.Where(rec => rec.Email.Equals(UserID))  
  89.                             select new  
  90.                             {  
  91.                                 a.EMP_CODE,  
  92.                                 a.First_Name,  
  93.                                 a.Last_Name,  
  94.                                 a.Supervisor_Code,  
  95.                                 a.Email  
  96.                             };  
  97.                 if (query != null)  
  98.                 {  
  99.                     if (query.Count() > 0)  
  100.                     {  
  101.                         BookingDetail book = new BookingDetail();  
  102.   
  103.                         book.Emp_Code = UserID;  
  104.                         book.Source = Source;  
  105.                         book.Destination = Destination;  
  106.                         book.Travelling_Date = TxtDepartDate;  
  107.                         book.NoOfPassenger = TxtNoOfTravellers;  
  108.                         book.Booking_Date = System.DateTime.Now.ToString();  
  109.                         book.TravelClass = TravelClass;  
  110.                         book.Remarks = Remarks;  
  111.                         context.BookingDetail.Add(book);  
  112.                         context.SaveChanges();  
  113.   
  114.                         if (TravelType == "Round Trip")  
  115.                         {  
  116.                             BookingDetail bookReturn = new BookingDetail();  
  117.                             bookReturn.Emp_Code = UserID;  
  118.                             bookReturn.Source = Destination;  
  119.                             bookReturn.Destination = Source;  
  120.                             bookReturn.Travelling_Date = TxtReturnDate;  
  121.                             bookReturn.NoOfPassenger = TxtNoOfTravellers;  
  122.                             bookReturn.Booking_Date = System.DateTime.Now.ToString();  
  123.                             bookReturn.TravelClass = TravelClass;  
  124.                             bookReturn.Remarks = Remarks;  
  125.                             context.BookingDetail.Add(bookReturn);  
  126.                             context.SaveChanges();  
  127.                         }  
  128.   
  129.                         //Getting Supervisor Informaiton  
  130.                         //======================================  
  131.                         int SuperVisorCode = Convert.ToInt32(query.FirstOrDefault().Supervisor_Code);  
  132.                         var supervisorInfo = from a in context.Employee.Where(rec => rec.EMP_CODE.Equals(SuperVisorCode))  
  133.                                              select new  
  134.                                              {  
  135.                                                  a.EMP_CODE,  
  136.                                                  a.First_Name,  
  137.                                                  a.Last_Name,  
  138.                                                  a.Supervisor_Code,  
  139.                                                  a.Email  
  140.                                              };  
  141.                         string supervisorName = supervisorInfo.FirstOrDefault().First_Name + " " + supervisorInfo.FirstOrDefault().Last_Name;  
  142.                         string bookUserName = query.FirstOrDefault().First_Name + " " + query.FirstOrDefault().Last_Name;  
  143.                         string supervisorEmail = supervisorInfo.FirstOrDefault().Email;  
  144.                         SendMail(supervisorName, bookUserName, supervisorEmail, Source, Destination, TxtDepartDate);  
  145.                         //======================================  
  146.   
  147.                     }  
  148.                 }  
  149.                 //==========================================================================================  
  150.                 var bookingHistory = from a in context.BookingDetail.Where(rec => rec.Emp_Code.Equals(UserID))  
  151.                                      select new  
  152.                                      {  
  153.                                          a.Booking_ID,  
  154.                                          a.Emp_Code,  
  155.                                          a.Source,  
  156.                                          a.Destination,  
  157.                                          a.Travelling_Date,  
  158.                                          a.NoOfPassenger,  
  159.                                          a.Booking_Date,  
  160.                                          a.TravelClass  
  161.                                      };  
  162.   
  163.   
  164.                 foreach (var item in bookingHistory)  
  165.                 {  
  166.                     BookingHistory bookObj = new BookingHistory();  
  167.                     bookObj.Booking_ID = item.Booking_ID;  
  168.                     bookObj.Emp_Code = item.Emp_Code;  
  169.                     bookObj.Source = item.Source;  
  170.                     bookObj.Destination = item.Destination;  
  171.                     bookObj.Travelling_Date = item.Travelling_Date;  
  172.                     bookObj.NoOfPassenger = item.NoOfPassenger;  
  173.                     bookObj.Booking_Date = item.Booking_Date;  
  174.                     bookObj.TravelClass = item.TravelClass;  
  175.                     details.Add(bookObj);  
  176.                 }  
  177.             }  
  178.             return details;  
  179.         }  
  180.   
  181.         public List<BookingHistory> BindBookingHistory(string UserID)  
  182.         {  
  183.             List<BookingHistory> details = new List<BookingHistory>();  
  184.             using (TravelTrackerEntities context = new TravelTrackerEntities())  
  185.             {  
  186.                 var bookingHistory = from a in context.BookingDetail.Where(rec => rec.Emp_Code.Equals(UserID))  
  187.                                      select new  
  188.                                      {  
  189.                                          a.Booking_ID,  
  190.                                          a.Emp_Code,  
  191.                                          a.Source,  
  192.                                          a.Destination,  
  193.                                          a.Travelling_Date,  
  194.                                          a.NoOfPassenger,  
  195.                                          a.Booking_Date,  
  196.                                          a.TravelClass  
  197.                                      };  
  198.   
  199.                 foreach (var item in bookingHistory)  
  200.                 {  
  201.                     BookingHistory bookObj = new BookingHistory();  
  202.                     bookObj.Booking_ID = item.Booking_ID;  
  203.                     bookObj.Emp_Code = item.Emp_Code;  
  204.                     bookObj.Source = item.Source;  
  205.                     bookObj.Destination = item.Destination;  
  206.                     bookObj.Travelling_Date = item.Travelling_Date;  
  207.                     bookObj.NoOfPassenger = item.NoOfPassenger;  
  208.                     bookObj.Booking_Date = item.Booking_Date;  
  209.                     bookObj.TravelClass = item.TravelClass;  
  210.                     details.Add(bookObj);  
  211.                 }  
  212.             }  
  213.             return details;  
  214.         }  
  215.   
  216.         public void SendMail(string SupervisorName, string name, string supervisorEmail, string source, string destination, string travellingDate)  
  217.         {  
  218.             try  
  219.             {  
  220.                 //Sending Mail  
  221.                 MailMessage mail = new MailMessage();  
  222.                 mail.To.Add(supervisorEmail);  
  223.                 mail.From = new MailAddress("[email protected]");  
  224.                 mail.Subject = "New Ticket Booking";  
  225.                 string Body = MailBody(SupervisorName, name, source, destination, travellingDate);  
  226.                 mail.Body = Body;  
  227.                 mail.IsBodyHtml = true;  
  228.                 SmtpClient smtp = new SmtpClient();  
  229.                 smtp.Host = "smtp.gmail.com";  
  230.                 smtp.Port = 587;  
  231.                 smtp.UseDefaultCredentials = false;  
  232.                 smtp.Credentials = new System.Net.NetworkCredential  
  233.                 ("[email protected]""Password");// Enter seders User name and password  
  234.                 smtp.EnableSsl = true;  
  235.                 smtp.Send(mail);  
  236.             }  
  237.             catch (Exception ex)  
  238.             {  
  239.             }  
  240.         }  
  241.   
  242.         public string MailBody(string SupervisorName, string name, string source, string destination, string travellingDate)  
  243.         {  
  244.             StreamReader reader = new StreamReader(HttpContext.Current.Server.MapPath("~/MailContent.html"));  
  245.             string readFile = reader.ReadToEnd();  
  246.             string StrContent = "";  
  247.             StrContent = readFile;  
  248.             StrContent = StrContent.Replace("[MyName]", SupervisorName).Replace("[UserName]", name).Replace("[Source]", source).Replace("[Destination]", destination).Replace("[TravelDate]", travellingDate);  
  249.             return StrContent;  
  250.         }  
  251.   
  252.         public string GetSupervisorInformation(string prefix)  
  253.         {  
  254.             string record = string.Empty;  
  255.             int SuperVisorCode = Convert.ToInt32(prefix);  
  256.             using (TravelTrackerEntities context = new TravelTrackerEntities())  
  257.             {  
  258.                 var matches = from m in context.Employee  
  259.                               where m.EMP_CODE.Equals(SuperVisorCode)  
  260.                               select new  
  261.                               {  
  262.                                   m.First_Name,  
  263.                                   m.Email  
  264.                               };  
  265.   
  266.                 foreach (var item in matches)  
  267.                 {  
  268.                     record = item.First_Name + "-" + item.Email;  
  269.                 }  
  270.             }  
  271.             return record;  
  272.         }  
  273.   
  274.         public List<KeyValuePair<string, string>> GetPreferedCarrier(string prefix)  
  275.         {  
  276.             var list = new List<KeyValuePair<string, string>>();  
  277.             using (TravelTrackerEntities context = new TravelTrackerEntities())  
  278.             {  
  279.                 var matches = from m in context.Carrier  
  280.                               where m.Airline.StartsWith(prefix)  
  281.                               select new  
  282.                               {  
  283.                                   m.CarrierID,  
  284.                                   m.Airline,  
  285.                                   m.Country  
  286.                               };  
  287.   
  288.                 foreach (var item in matches)  
  289.                 {  
  290.                     list.Add(new KeyValuePair<string, string>(item.Airline.ToString(), item.Country));  
  291.                 }  
  292.             }  
  293.             return list;  
  294.         }  
  295.   
  296.         public List<KeyValuePair<string, string>> GetSourceDestination(string prefix)  
  297.         {  
  298.             var list = new List<KeyValuePair<string, string>>();  
  299.             using (TravelTrackerEntities context = new TravelTrackerEntities())  
  300.             {  
  301.                 var matches = from m in context.Airport  
  302.                               where m.AirportName.StartsWith(prefix)  
  303.                               select new  
  304.                               {  
  305.                                   m.AirportName,  
  306.                                   m.AirportCD  
  307.                               };  
  308.   
  309.                 foreach (var item in matches)  
  310.                 {  
  311.                     list.Add(new KeyValuePair<string, string>(item.AirportName.ToString(), item.AirportCD));  
  312.                 }  
  313.             }  
  314.             return list;  
  315.         }  
  316.     }  
  317. }  
Below is my Mail Content Page.
  1. <!DOCTYPE html>  
  2. <html xmlns="http://www.w3.org/1999/xhtml">  
  3. <head>  
  4.     <title>R- Airline Booking Engine</title>  
  5. </head>  
  6. <body>  
  7.     <table style="font-family:Calibri;background:#E6E6E6">   
  8.         <tr>  
  9.             <td>  
  10.                 <p>Dear [SupervisorName]</p>  
  11.                 <p>[UserName] booked a new ticket from [Source] to [Destination]   
  12.                 using R- Airline Booking Engine.</p>  
  13.                 <p>Travel Date [TravelDate]</p>  
  14.             </td>  
  15.         </tr>  
  16.         <tr>  
  17.             <td>  
  18.                 Thanks & Regards,  
  19.                 [email protected]  
  20.             </td>  
  21.         </tr>  
  22.     </table>  
  23. </body>  
  24. </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.


Similar Articles