public List <DestinationFlights> GetDestinationFlights()
{
List <DestinationFlights> destinationFlights = new List <DestinationFlights>();
try
{
var conn = _dbContext.ConnectToDB();
var rdr = _dbContext.ExecuteQuery("SELECT dest, COUNT(dest) FROM `flights` GROUP BY dest ORDER BY COUNT(dest) DESC LIMIT 10", conn);
while (rdr.Read())
{
var destinationFlight = new DestinationFlights
{
Destination = rdr.GetString(0),
count = rdr.GetInt16(1)
};
destinationFlights.Add(destinationFlight);
}
_dbContext.CloseConnections(rdr, conn);
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
}
return(destinationFlights);
}
public List <DestinationOrigin> GetDestinationOrigins()
{
List <DestinationOrigin> originFlights = new List <DestinationOrigin>();
try
{
var conn = _dbContext.ConnectToDB();
var rdr = _dbContext.ExecuteQuery("SELECT dest , COUNT(dest), origin FROM flights WHERE dest IN (select * FROM (Select dest FROM flights GROUP BY dest ORDER BY COUNT(dest) DESC LIMIT 0,10) AS t1) GROUP BY dest, origin ORDER BY origin, dest;", conn);
var j = 0;
while (rdr.Read() && j < 3)
{
var originFlight = new DestinationOrigin {
Origin = rdr.GetString(2)
};
var origin = originFlight.Origin;
var flights = new List <DestinationFlights>();
do
{
var flight = new DestinationFlights
{
Destination = rdr.GetString(0),
count = rdr.GetInt16(1)
};
flights.Add(flight);
origin = rdr.GetString(2);
} while (rdr.Read() && rdr.GetString(2) == origin);
originFlight.Flights = flights;
originFlights.Add(originFlight);
j++;
}
_dbContext.CloseConnections(rdr, conn);
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
}
return(originFlights);
}
