public List <Flight> GetInternalFlights(string date)
{
string query = "SELECT * FROM FlightPlans";
var rowsFromFlightPlans = DataMan.ExcuteQuery(query);
List <Flight> internalFlights = new List <Flight>();
DateTime dt = DateTime.Parse(date);
foreach (var row in rowsFromFlightPlans)
{
Initial_Location location = getLocation(dt, row.ID,
(int)row.Initial_Location_ID);
if (location != null) // means that this FlightPlan is active
{
internalFlights.Add(new Flight()
{
flight_id = row.ID,
latitude = location.latitude,
longitude = location.longitude,
passengers = (int)row.Passengers,
company_name = row.Company,
date_time = dt,
is_external = false
});
}
}
return(internalFlights);
}
public FlightPlan addFlightPlan(FlightPlan flightPlan)
{
// generate ID function
string uniqueID = generateUniqueID(flightPlan.company_name);
// post into Initial_Locations table
Initial_Location initial_Location = flightPlan.initial_location;
string dateString = "'" + initial_Location.date_time
.ToString("yyyy-MM-ddTHH:mm:ssZ") + "'";
string postQuery_Initial_Locations = "INSERT INTO Initial_Locations(Longitude, " +
"Latitude, Date) VALUES(@longitude, @latitude, " + dateString + ")";
DataMan.ExcuteQuery(postQuery_Initial_Locations, initial_Location);
// post into FlightPlans table
string getQuery_extractInitialLocationID = "SELECT ID FROM " +
"Initial_Locations ORDER BY ID DESC LIMIT 1";
var x = DataMan.ExcuteQuery(getQuery_extractInitialLocationID);
int lastIDFromInitialLocations = (int)x.ToList()[0].ID;
string postQuery_FlightPlans = "INSERT INTO " +
"FlightPlans(ID, Company, Passengers, Initial_Location_ID)" +
" VALUES('" + uniqueID + "',@company_name, @passengers, " + lastIDFromInitialLocations + ")";
DataMan.ExcuteQuery(postQuery_FlightPlans, flightPlan);
// post into Segments table
string postQuery_Segment = "";
foreach (Segment segment in flightPlan.segments)
{
postQuery_Segment = "INSERT INTO Segments(Longitude, Latitude, TimeSpan, " +
"Flight_ID) VALUES(@longitude, @latitude, @timespan_seconds, '" + uniqueID + "')";
DataMan.ExcuteQuery(postQuery_Segment, segment);
}
return(flightPlan);
}
public async Task <FlightPlan> getFlightPlan(string id)
{
// get FlightPlan by id
string getQuery_fromFlightPlans = "SELECT * FROM FlightPlans WHERE ID ='"
+ id + "'";
// var result = cnn.Query(getQuery_fromFlightPlans, new DynamicParameters());
var result = DataMan.ExcuteQuery(getQuery_fromFlightPlans);
if (result.Count() == 0)
{
// this FlightPlan doesnt exist In DB - We will try find it in external servers
var servers = (dynamic)null;
string query = "SELECT URL FROM Servers";
var results_servers = DataMan.ExcuteQuery(query);
if (results_servers.Count() == 0)
{
// doesnt have servers
return(null);
}
servers = results_servers.ToList();
foreach (var server in servers) // fetch from each server
{
string uri = server.URL + "/api/FlightPlan/" + id;
HttpClient client = new HttpClient();
try
{
HttpWebRequest request = (HttpWebRequest)WebRequest.Create(uri);
HttpResponseMessage response = await client.GetAsync(uri);
response.EnsureSuccessStatusCode();
string responseBody = await response.Content.ReadAsStringAsync();
if (response.IsSuccessStatusCode && !responseBody.Contains("fail"))
{
// get all flights into list and then copy the elements into flightList
FlightPlan externalFlightPlan = Newtonsoft.Json.JsonConvert
.DeserializeObject <FlightPlan>(responseBody);
return(externalFlightPlan);
}
}
catch (HttpRequestException e)
{
Console.WriteLine(e);
}
catch (UriFormatException e)
{
Console.WriteLine(e);
}
client.Dispose();
}
}
//var flightPlan = result.ToList()[0];
var flightPlan = result[0];
// get Initial_Location by flightPlan.Initial_Location_ID
string getQuery_fromInitialLocations = "SELECT Longitude,Latitude," +
"Date FROM Initial_Locations WHERE ID ='" + flightPlan.Initial_Location_ID + "'";
var initialLocationOb = DataMan.ExcuteQuery(getQuery_fromInitialLocations)[0];
Initial_Location initial_Location = new Initial_Location()
{
latitude = initialLocationOb.Latitude,
longitude = initialLocationOb.Longitude,
date_time = DateTime.Parse(initialLocationOb.Date)
};
// get segments by id and create Segments list
string getQuery_fromSegments = "SELECT Longitude,Latitude,Timespan " +
"FROM Segments WHERE Flight_ID ='" + id + "'";
var allSegmentsOb = DataMan.ExcuteQuery(getQuery_fromSegments);
List <Segment> segments = new List <Segment>();
foreach (var segmentOb in allSegmentsOb)
{
segments.Add(new Segment()
{
latitude = segmentOb.Latitude,
longitude = segmentOb.Longitude,
timespan_seconds = (int)segmentOb.Timespan
});
}
return(new FlightPlan()
{
company_name = flightPlan.Company,
passengers = (int)flightPlan.Passengers,
initial_location = initial_Location,
segments = segments.ToArray()
});
}
