public List <Flight> GetInternalFlights(DateTime dateTime) { List <Flight> internals = new List <Flight>(); using (SQLiteConnection cnn = new SQLiteConnection(LoadConnectionString())) { //open the connection cnn.Open(); SQLiteCommand sqlComm = cnn.CreateCommand(); sqlComm = new SQLiteCommand("SELECT * FROM FlightPlans", cnn); SQLiteDataReader reader = sqlComm.ExecuteReader(); MyFlightPlanManager planManager = new MyFlightPlanManager(); // For each flightPlan in the database, we will create p1 Flight object while (reader.Read()) { string flightPlanID = reader["flight_id"].ToString(); FlightPlan flightPlan = planManager.GetFlightPlanByID(flightPlanID).Result; if (OnTime(flightPlan, dateTime)) { InitialLocation flightLocation = GetPosition(flightPlan, dateTime); DateTime dateTime1 = dateTime; string date1 = dateTime1.ToString(); internals.Add(new Flight() { passengers = flightPlan.passengers, flight_id = flightPlanID, Company_name = flightPlan.company_name, isExternal = false, Longitude = flightLocation.longitude, Latitude = flightLocation.latitude, date_time = date1 }); } } reader.Close(); } return(internals); }
public async Task <FlightPlan> GetFlightPlanByID(string flightID) { using (IDbConnection cnn = new SQLiteConnection(LoadConnectionString())) { // If we get the ID with {} if (flightID.Contains('{')) { int k = flightID.Length - 1; flightID = flightID.Remove(flightID.Length - 1); flightID = flightID.Remove(0, 1); } // Checking if the Flight is in the DB using var con = new SQLiteConnection(LoadConnectionString()); con.Open(); SQLiteCommand cmd = new SQLiteCommand("SELECT * FROM FlightPlans WHERE flight_ID = " + "\"" + flightID + "\"", con); SQLiteDataReader reader = cmd.ExecuteReader(); reader.Read(); if (reader.HasRows) // The FlightPlan we want is in the database { // When we are asked to retrieve a flight plan, we will fetch it from the data base string company_name = reader["company_name"].ToString(); int passengers = Int32.Parse(reader["passengers"].ToString()); string location_ID = reader["location_ID"].ToString(); reader.Close(); // Retrieving the the location cmd = new SQLiteCommand("SELECT * FROM Locations WHERE id = " + "\"" + location_ID + "\"", con); reader = cmd.ExecuteReader(); reader.Read(); InitialLocation initialLocation = new InitialLocation { longitude = Double.Parse(reader["longitude"].ToString()), latitude = Double.Parse(reader["latitude"].ToString()), date_time = reader["date"].ToString() }; reader.Close(); // Retrieving the segments List <Segment> segments = new List <Segment>(); cmd = new SQLiteCommand("SELECT * FROM Segments WHERE flight_ID = " + "\"" + flightID + "\"", con); reader = cmd.ExecuteReader(); while (reader.Read()) { segments.Add(new Segment() { longitude = Double.Parse(reader["longitude"].ToString()), latitude = Double.Parse(reader["latitude"].ToString()), timespan_seconds = Double.Parse(reader["timespan_seconds"].ToString()) }); } // Creating the new Flight Plan from the data we gathered return(new FlightPlan() { company_name = company_name, passengers = passengers, initial_location = initialLocation, segments = segments }); } else // If the flight is not in the database, we will check if its in any of the servers { SQLiteCommand sqlComm = con.CreateCommand(); sqlComm = new SQLiteCommand("SELECT * FROM Servers", con); reader = sqlComm.ExecuteReader(); MyFlightPlanManager planManager = new MyFlightPlanManager(); if (reader.HasRows) // there are servers { while (reader.Read()) // iterating on all the servers { Server server = new Server { ServerID = reader["ServerID"].ToString(), ServerURL = reader["ServerURL"].ToString() }; HttpClient client = new HttpClient(); try { string msg = server.ServerURL + "/api/FlightPlan/" + flightID; HttpResponseMessage response = await client.GetAsync(msg); response.EnsureSuccessStatusCode(); string responseBody = await response.Content.ReadAsStringAsync(); if (response.IsSuccessStatusCode && !responseBody.Contains("fail")) { return(Newtonsoft.Json.JsonConvert.DeserializeObject <FlightPlan>(responseBody)); } else { continue; // the flightplan is not in this server } } catch (HttpRequestException e) { Console.WriteLine("\nException Caught!"); Console.WriteLine("Message :{0} ", e.Message); } client.Dispose(); } // There is no flight with that ID in the servers return(null); } else // no servers - the flight id requested is not in the db nor the servers { return(null); } } } }
public async Task <List <Flight> > GetAllFlights(DateTime date) { string date1 = date.ToString("yyyy-MM-ddTHH':'mm':'ss") + "Z"; Debug.WriteLine(date1); List <Flight> list = GetInternalFlights(date); //add to list from external servers using (SQLiteConnection cnn = new SQLiteConnection(LoadConnectionString())) { //open the connection cnn.Open(); SQLiteCommand sqlComm = cnn.CreateCommand(); sqlComm = new SQLiteCommand("SELECT * FROM Servers", cnn); SQLiteDataReader reader = sqlComm.ExecuteReader(); MyFlightPlanManager planManager = new MyFlightPlanManager(); while (reader.Read()) // iterating on all the servers { Server server = new Server { ServerID = reader["ServerID"].ToString(), ServerURL = reader["ServerURL"].ToString() }; List <Flight> externals = new List <Flight>(); HttpClient client = new HttpClient(); try { string msg = server.ServerURL + "/api/Flights?relative_to=" + date1; HttpResponseMessage response = await client.GetAsync(msg); response.EnsureSuccessStatusCode(); string responseBody = await response.Content.ReadAsStringAsync(); Debug.WriteLine(responseBody); try { if (response.IsSuccessStatusCode && !responseBody.Contains("fail")) { externals = Newtonsoft.Json.JsonConvert.DeserializeObject <List <Flight> >(responseBody); } else { Debug.WriteLine("\nError in getting flights from server"); } } catch (JsonReaderException e1) { Console.WriteLine("\nException Caught!" + e1); } } catch (HttpRequestException e) { Console.WriteLine("\nException Caught!"); Console.WriteLine("Message :{0} ", e.Message); } client.Dispose(); foreach (Flight f in externals.ToList()) { f.isExternal = true; } list = list.Concat(externals).ToList(); } } return(list); }