sse698.3


 * Course:** SSE698
 * Project:** 3
 * Subject:** Building Simple Flight Booking Application With ASP.NET MVC Extension
 * Student:** Dmitriy Slipak
 * E-mail:** dslipak@gmail.com

=Table Of Contents=


 * 1) Introduction
 * 2) Data Preparation
 * 3) myFlight Project Structure
 * 4) myFlight Application Layout
 * 5) myFlight Application Settings
 * 6) myFlight Application Models
 * 7) myFlight Application Controllers
 * 8) myFlight Application Views
 * 9) myFlight Application Functionality
 * 10) Implementing Airport Search
 * 11) Seats View Implementation
 * 12) Conclusion

 =Introduction=

In this project I will demonstrate how to build a simple web application utilizing ASP.NET with MVC extension. I created a simple flight booking application myFlight with basic functionality. The general idea for the application functionality is similar to commonly known travel reservation services such as Expedia.com or Orbitz.com. The application includes different techniques for data processing such as AJAX, database communication, etc. Every file, including scripts and graphics created by myself. I used GIMP to create graphics, Microsoft Visual Web Developer 2008 Express Edition as IDE for ASP.NET coding, and PostgreSQL database for data storage. This project is also my first usage of ASP.NET with MVC extension.  =Data Preparation=

An excellent source of initial data for myFlight project was a data freely available from @Openflights.org team. I used three data files prepared by Openflights people – airlines.dat, airports.dat, and routes.dat. These files were initial source for future flights generation data. Below is a tables structure for myFlight project.

Contains untouched, complete set of data from airlines.dat file. code format="sql" CREATE TABLE airlines (   al_id integer NOT NULL,      "name" character varying(100) NOT NULL,      alias character varying(100) NOT NULL,      iata character(2) NOT NULL,      icao character(3) NOT NULL,      callsign character varying(100) NOT NULL,      country character(100) NOT NULL,      active character(1) NOT NULL ) WITH (     OIDS=FALSE ); ALTER TABLE airlines OWNER TO "pgdbusr"; GRANT ALL ON TABLE airlines TO "pgdbusr"; GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE airlines TO public;
 * Table Airlines**

CREATE INDEX inx_alias ON airlines USING btree (alias) WITH (FILLFACTOR=100); code

Contains untouched, complete set of data from airports.dat file. code format="sql" CREATE TABLE airports (   ap_id integer NOT NULL,      "name" character varying(255) NOT NULL,      city character varying(100) NOT NULL,      country character(100) NOT NULL,      iata_faa character(3) NOT NULL,      icao character(4) NOT NULL,      latitude numeric(11,6) NOT NULL,      longitude numeric(11,6) NOT NULL,      altitude smallint NOT NULL,     timezone numeric(5,2) NOT NULL,      dst character(1) NOT NULL ) WITH (     OIDS=FALSE ); ALTER TABLE airports OWNER TO "pgdbusr"; GRANT ALL ON TABLE airports TO "pgdbusr"; GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE airports TO public;
 * Table Airports**

CREATE UNIQUE INDEX inx_ap_id ON airports USING btree (ap_id) WITH (FILLFACTOR=100);

CREATE INDEX inx_city ON airports USING btree (city) WITH (FILLFACTOR=100);

CREATE INDEX inx_country ON airports USING btree (country) WITH (FILLFACTOR=100);

CREATE INDEX inx_name ON airports USING btree (name) WITH (FILLFACTOR=100); code

Contains untouched, complete set of data from routes.dat file. code format="sql" CREATE TABLE routes (     al_iata_icao character varying(3) NOT NULL,      al_id integer,      src_ap_iata_icao character varying(4) NOT NULL,      src_ap_id integer,      dst_ap_iata_icao character varying(4) NOT NULL,      dst_ap_id integer,      codeshare character(1),      stops smallint NOT NULL,      equipment character varying(50) ) WITH (     OIDS=FALSE ); ALTER TABLE routes OWNER TO "pgdbusr"; GRANT ALL ON TABLE routes TO "pgdbusr"; GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE routes TO public; code
 * Table Routes**

Contains data for all major civil airplanes. The source of information is flugzeuginfo.net code format="sql" CREATE TABLE aircrafts (     iata character varying(3) NOT NULL,      manufacturer character varying(100) NOT NULL,      model character varying(100) NOT NULL,      wake character(3) NOT NULL ) WITH (   OIDS=FALSE ); ALTER TABLE aircrafts OWNER TO "pgdbusr"; GRANT ALL ON TABLE aircrafts TO "pgdbusr"; GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE aircrafts TO public; code
 * Table Aircrafts**

Contains flights information, generated by a script. I will explain later how flights data was generated. code format="sql" CREATE TABLE flights (     flight_id integer NOT NULL,      dep_date date NOT NULL,      dep_time character varying(10) NOT NULL,      al_id integer NOT NULL,      src_ap_id integer NOT NULL,      dst_ap_id integer NOT NULL,      equipment character varying(4) NOT NULL,      CONSTRAINT pk_flight_id PRIMARY KEY (flight_id) ) WITH (     OIDS=FALSE ); ALTER TABLE flights OWNER TO "pgdbusr"; GRANT ALL ON TABLE flights TO "pgdbusr"; GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE flights TO public;
 * Table Flights**

CREATE INDEX inx_al_id ON flights USING btree (al_id) WITH (FILLFACTOR=100);

CREATE INDEX inx_dep_date ON flights USING btree (dep_date) WITH (FILLFACTOR=100);

CREATE INDEX inx_dep_time ON flights USING btree (dep_time) WITH (FILLFACTOR=100);

CREATE INDEX inx_dst_ap_id ON flights USING btree (dst_ap_id) WITH (FILLFACTOR=100);

CREATE INDEX inx_src_ap_id ON flights USING btree (src_ap_id) WITH (FILLFACTOR=100); code

Contains seat availability information, generated by a script. code format="sql" CREATE TABLE seats (     flight_id integer NOT NULL,      seat_id integer NOT NULL,      available character(1) NOT NULL, CONSTRAINT fk_flight_id FOREIGN KEY (flight_id) REFERENCES flights (flight_id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE CASCADE ) WITH (   OIDS=FALSE ); ALTER TABLE seats OWNER TO "pgdbusr";
 * Table Seats**

CREATE INDEX inx_flight_id ON seats USING btree (flight_id) WITH (FILLFACTOR=100); code

Contains user account related information. code format="sql" CREATE TABLE users (     usr_id serial NOT NULL,      usr_l_name character(100) NOT NULL,      usr_m_name character(2),      usr_f_name character(100) NOT NULL,      usr_e_mail character varying(100) NOT NULL,      passwd character varying(150) NOT NULL, CONSTRAINT con_e_mail UNIQUE (usr_e_mail) ) WITH (     OIDS=FALSE ); ALTER TABLE users OWNER TO "pgdbusr"; GRANT ALL ON TABLE users TO "pgdbusr"; GRANT ALL ON TABLE users TO public; code
 * Table Users**

Contains user's booked flights information. code format="sql" CREATE TABLE itineraries (     it_id serial NOT NULL,      usr_id integer NOT NULL,      flight_id integer NOT NULL ) WITH (     OIDS=FALSE ); ALTER TABLE itineraries OWNER TO "pgdbusr"; GRANT ALL ON TABLE itineraries TO "pgdbusr"; GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE itineraries TO public; code I generated flights information for Flights table for entire month of July 2010 using Python script. I used Python for simplicity. Below is a script. code format="python" from datetime import date, time import random
 * Table Itineraries**

fr = open('c:\\temp\\flights_dump.dat', 'r') fw = open('c:\\temp\\flights.dat', 'a') fn = 1

for line in fr: d = 1 tok = line.split(',') tok_e = tok[3].split(' ')

while d <= 31: if d < 10: sd = int('0') + d           else: sd = d

fd = date(2010, 07, sd) p = ('AM','PM') t = time(random.randrange(1,12), random.randrange(1,60), 00, 000000)

if len(tok_e) > 1: line = tok[0].replace('\n','') + ',' + tok[1].replace('\n', ) + ',' + tok[2].replace('\n',) + ',' + tok_e[random.randrange(len(tok_e))].replace('\n','') + '\n'

fw.write(str(fn) + ',' + str(fd) + ',' + str(t)                       + p[random.randrange(0,2)] + ',' + line) fn += 1 d += 1

fw.close fr.close code Where, flights_dump.dat is a dump of the following query: code format="sql" copy (select a.al_id, r.src_ap_id, r.dst_ap_id, trim(r.equipment) from airlines a, routes r where a.al_id = r.al_id) to 'c://temp//flights_dump.dat' with csv delimiter ','; code The above script generated 1622974 fights for July 2010. Similar python script was used to generate seats availability data for the Seats table. Once the data is ready, we can start create our ASP.NET application.  =myFlight Project Structure=

As mentioned above, the myFlight project is ASP.NET project with MVC extension. Below is project structure from IDE. Here, Content folder contains css files, Controllers folder contains project's controllers, Models folder contains project's models, Views folder contains project's views, Scripts folder contains javascript libraries, and WS folder contains web services files.  =MyFlight Application Layout=

The application's layout presented on screen shot below. The root of any view in the application is Master page which contains header section, menu section, footer section, and container for view content. Below is a Site.Master's file source. code format="asp" <%@ Master Language="C#" AutoEventWireup="true" CodeBehind="Site.master.cs" Inherits="myFlight.Views.Shared.Site" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

     My Flight     ");                                   Response.Write(" Account                                                    <%                                if (Convert.ToInt32(Session["usr_id"]) != 0)                                    Response.Write("Hello, " + Session["usr_name"]);                            %>                                                    <%                                if (Convert.ToInt32(Session["usr_id"]) != 0)                                    Response.Write("Sign Out</a>");                                else                                    Response.Write("Sign In</a>");                            %>        </asp:ContentPlaceHolder>         </asp:ContentPlaceHolder> code There are several includes in the Site.Master file, such as common.js – set of javascript functions for application, for example buttons click event handlers, controls validation, etc., ws_wrapper.js – set of javascript functions for web services communication, and Site.css – set of styles applied to html elements in application's views. Let's take a closer look at application's major files.  =myFlight Application Settings=

The Web.config file contains major application settings, such as database connection string, predefined SQL queries, etc. Predefined queries are sql statements that contains a variable, or set of variables. Those variables will be replaced in application's models upon request with required arguments. For example, predefined query code format="xml"  code will be handled in model as following code format="c string query = ConfigurationManager.AppSettings["ff_get_itinerary"].ToString. Replace("$usr_id", Convert.ToString(usr_id)); code Global.asax file contains untouched default MVC routes maps code format="asp" routes.MapRoute(               "Default",                "{controller}/{action}/{id}",                new { controller = "Home", action = "Index", id = UrlParameter.Optional } ); code as well as session variables code format="asp" protected void Session_Start {           Session["usr_id"] = 0; Session["usr_name"] = ""; } code  =myFlight Application Models=

The myFlightModel.cs file contains entire functionality for database communication. It contains Flight and Account structures represented below, as well as application's model – class myFlightModel. code format="c public class Flight {       public int flight_id; public string dep_date; public string dep_time; public string airline; public string aircraft; public string src_airport; public string src_city; public string src_country; public string dst_airport; public string dst_city; public string dst_country; }

public class Account {       public int usr_id; public string usr_f_name; public string usr_m_name; public string usr_l_name; public string usr_e_mail; public string usr_passwd; public string itineraries; } code The rest of model contains set of functions for application functionality. For example, function for flights search looks as below. code format="c public IQueryable<Flight> FindFlights(List args) {           NpgsqlConnection conn; NpgsqlCommand command; NpgsqlDataReader dr; List<Flight> fl = new List<Flight>;

using (conn = new NpgsqlConnection(ConfigurationManager. ConnectionStrings["pgsql_conn"].ConnectionString)) {               conn.Open;

string query = ""; string query_name = ""; string time = "";

if (Convert.ToInt16(args[0]) == 1) query_name = "ff_oneway"; if (Convert.ToInt16(args[0]) == 2) query_name = "ff_roundtrip";

query = ConfigurationManager.AppSettings[query_name].ToString. Replace("$src_airport", args[1]). Replace("$src_date", args[2]). Replace("$dst_airport", args[4]). Replace("$dst_date", args[5]);

if (args[3] != "Anytime") time = args[3];

if (time != "") {                   if (time.Length < 4) time = "%" + time[0] + ":%" + time[1] + time[2]; else time = "%" + time[0] + time[1] + ":%" + time[2] + time[3];

query = query.Replace("$src_time", time); }               else query = query.Replace("$src_time", "%");

if (args[6] != "Anytime") time = args[6];

if (time != "") {                   if (time.Length < 4) time = "%" + time[0] + ":%" + time[1] + time[2]; else time = "%" + time[0] + time[1] + ":%" + time[2] + time[3];

query = query.Replace("$dst_time", time); }               else query = query.Replace("$dst_time", "%");

using (command = new NpgsqlCommand(query, conn)) {                   try {                       using (dr = command.ExecuteReader) {                           Flight f;                            while (dr.Read) {                               f = new Flight; f.flight_id = Convert.ToInt32(dr["flight_id"]); f.dep_date = dr["dep_date"].ToString. Substring(0, dr["dep_date"].ToString.                                  IndexOf(" ")); f.dep_time = dr["dep_time"].ToString; f.airline = dr["airline"].ToString; f.aircraft = dr["aircraft"].ToString; f.src_airport = dr["src_airport"].ToString; f.src_city = dr["src_city"].ToString; f.src_country = dr["src_country"].ToString; f.dst_airport = dr["dst_airport"].ToString; f.dst_city = dr["dst_city"].ToString; f.dst_country = dr["dst_country"].ToString;

fl.Add(f); }                       }                    }                    finally {                       conn.Close; }               }            }

return fl.AsQueryable<Flight>; } code  =myFlight Application Controllers=

There are three application controllers at the moment.


 * **HomeController** – to handle application start-up;
 * **FlightsController** – to handle flight search, flight details functionality;
 * **UsersController** – to handle user/account functionality.

code format="c public class HomeController : Controller {       //        // GET: /Home/
 * HomeController**

public ActionResult Index {           List tl = new List ; tl.Add("Anytime"); for (int i = 1; i <= 12; i++) tl.Add(i.ToString + "AM"); for (int i = 1; i < 12; i++) tl.Add(i.ToString + "PM");

ViewData["flight_time"] = new SelectList(tl);

return View("Index"); }   } code code format="c public class FlightsController : Controller {       private static IQueryable<Flight> temp_flights;
 * FlightsController**

//       // GET: /Flights/

public ActionResult Search {           return RedirectToAction("Index", "Home"); }

[AcceptVerbs(HttpVerbs.Post)] public ActionResult Search(FormCollection formValues) {           string trip = Request.Form["rbTripType"]; string src_airport = Request.Form["txtSrcPlace"]; string src_date = Request.Form["txtSrcDate"]; string src_time = Request.Form["cboSrcTime"]; string dst_airport = Request.Form["txtDstPlace"]; string dst_date = Request.Form["txtDstDate"]; string dst_time = Request.Form["cboDstTime"]; myFlightModel model = new myFlightModel; List args = new List ;

args.Add(trip); args.Add(src_airport); args.Add(src_date); args.Add(src_time); args.Add(dst_airport); args.Add(dst_date); args.Add(dst_time);

temp_flights = model.FindFlights(args); return RedirectToAction("List"); }

public ActionResult List {           ViewData["flights"] = temp_flights.ToList<Flight>; temp_flights = null; return View("List", ViewData["flights"]); }

public ActionResult Details(string id) {           myFlightModel model = new myFlightModel; List<Flight> flights = model.GetFlights(id); ViewData["flights"] = flights;

return View("Details", ViewData["flights"]); }

public ActionResult SetItinerary(string id) {           myFlightModel model = new myFlightModel; model.SetItinerary(Convert.ToInt32(Session["usr_id"]), id); return Redirect("/Users/GetAccount"); }   } code code format="c public class UsersController : Controller {       //        // GET: /Users/
 * UsersController**

public ActionResult Index {           if (Convert.ToInt16(Session["usr_id"]) == 0) return View("SignIn"); return View("Account"); }

public ActionResult SignIn {           return View("SignIn"); }

[AcceptVerbs(HttpVerbs.Post)] public ActionResult SignIn(FormCollection formValues) {           myFlightModel model = new myFlightModel; Session["usr_id"] = Convert.ToString(               model.GetUsrId(Request.Form["txtEMail"], Request.Form["txtPasswd"]));

if (Convert.ToInt16(Session["usr_id"]) == 0) {               ViewData["error"] = "Invalid user"; return View("SignIn", ViewData["error"]); }

Account acct = model.GetAccount(Convert.ToInt32(Session["usr_id"])); Session["usr_name"] = acct.usr_f_name;

return Redirect("/Home/"); }

public ActionResult SignOut {           Session["usr_id"] = 0; Session["usr_name"] = ""; return Redirect("/Home/"); }

public ActionResult SetAccount {           return View("SignUp"); }

[AcceptVerbs(HttpVerbs.Post)] public ActionResult SetAccount(FormCollection formValues) {           myFlightModel model = new myFlightModel; Account acct = new Account;

acct.usr_id = Convert.ToInt32(Session["usr_id"]); acct.usr_l_name = Request.Form["txtLastName"]; acct.usr_m_name = Request.Form["txtMI"]; acct.usr_f_name = Request.Form["txtFirstName"]; acct.usr_e_mail = Request.Form["txtEMail"]; acct.usr_passwd = Request.Form["txtPasswd"];

Session["usr_id"] = Convert.ToString(model.SetAccount(acct));

if (Convert.ToInt16(Session["usr_id"]) == 0) {               ViewData["error"] = "An error had occured"; return View("SignUp", ViewData["error"]); }

Session["usr_name"] = acct.usr_f_name;

ViewData["account"] = acct; return View("Account", ViewData["account"]); }

public ActionResult GetAccount {           myFlightModel model = new myFlightModel; Account acct = model.GetAccount(Convert.ToInt32(Session["usr_id"])); ViewData["account"] = acct; return View("Account", ViewData["account"]); }   } code  =myFlight Application Views=

Application contains following views at present
 * /Home/Index.aspx – application's default view;
 * /Flights/List.aspx – shows available flights per user search;
 * /Flights/Details.aspx – shows details for selected flight(s);
 * /Users/Account.aspx – shows user's information;
 * /Users/SignIn.aspx – application log in screen;
 * /Users/SignUp.aspx – user registration screen.

In general, views will display some information received from responsible controller. Below is List view to display flights per user's search. code format="asp" <%@ Page Title="" Language="C#" MasterPageFile="~/Views/Shared/Site.Master" Inherits="System.Web.Mvc.ViewPage" %>



<td height="30" colspan="3"> <td align="center" colspan="3">

</asp:Content> code  =myFlight Application Functionality=

Lets take a look at application functionality. Application starts with default search form, where user can provide source and destination information for flights look-up. Lets plan a trip from New York to Paris.



I entered airport name in NY and airport in Paris. I also selected dates for my trip. And I selected round-trip option. Below is result of search.



One-way result would be as below.



Lets get back to round-trip. I selected following trip



and click "Details" button to see detailed information for selected flights.



Ok, I am satisfied with selected flights, and I want to book them. I click "Register" button. Since I didn't sign in application will be redirected to SignIn view.



I have to register first.



Once, I've entered required information and registered, application will be redirected to user's acount page. Please note that menu changed.



Now, lets get back to selected flights again, and reserve them.



I have booked selected flights, and application redirected to user's account view, where there are my itineraries.



Lets book some more flights. I want to book one-way trip from New York to South Africa.



I found flights below.



And added to my itineraries.



Now, lets sign out, and sign in again to make sure everything is correct.



After sign in account view shows proper information below.



From this point myFlight application can be extended and improved in any direction.  =Implementing Airport Search=

It is hard to know all airports names. That is why most of travel services provide source and destination airport search via so-called suggestions. Lets add it to myFlight application. Below is demonstrated such suggestions list when I typed into source place.



Probably, using JQuery is the simplest option for suggestions implementation. I did in my way, via javascript functions and web services. In previous project I demonstrated how to implement simple AJAX functionality. The same approach can be applied for suggestions implementation. Below is the web service function which calls function in the model to receive a data. code format="c [WebMethod] public string SearchAirport(string q) { myFlightModel model = new myFlightModel; return model.SearchAirport(q); } code Then, I created javascript function which will call this web service. code format="javascript" function searchAirport(id, q) { if (q == '') return;

var data = document.getElementById('divAirportSearch'); var ws = new WS('http://localhost/', 'ajax', 'WS/AJAX.asmx',        'SearchAirport', data); ws.addArgument('q', 'string', q); ws.execute;

if (data.style.visibility == 'hidden') data.style.visibility = 'visible'; } code The rest is to create hidden element in the view and attach above javascript function to airport search text box in the view. code format="html" code

code format="asp" <%= Html.TextBox("txtSrcPlace", "", new { onkeypress = "searchAirport(this.id, this.value)" })%> code  =Seats View Implementation=

Another good option for flight booking application is ability to view and select particular seat on the flight. The myFlight application contains Seats table ready to use. General idea for seats view implementation is create images map from template graphics file such as below.



Once images map created, we can store selected seats in the Seats table.

Complete source code for the project as well as database dump is available upon request.  =Conclusion=

MVC extension is a very nice feature in ASP.NET. I did find it very good option for rapid web development. Using MVC extension it took just a few days to code myFlight application. I see some advantages of MVC over standard Web Forms. More over, .NET framework itself get extended dramatically since version 2.0. That is the last version I used to work with. These days web development utilizing ASP.NET is very enjoyable.

Last revision date: {$revisiondate}