private static void QueryDestByOriginInternal(string year, string origin, string dest, string airline, string locale, string tableName, List <DestInfo> destList)
{
NpgsqlConnection conn = null;
try {
conn = new NpgsqlConnection(ASTDatabase.connStr2);
conn.Open();
string where = ASTDatabase.MakeWhere(year, airline, origin, dest);
string groupby = " \"GEOM\", " + (origin != "" ? "\"DEST\"" : "\"ORIGIN\"");
string fields = (origin != "" ? "\"DEST\"" : "\"ORIGIN\"");
fields += ", ST_AsText(\"GEOM\") AS \"GEOM\" ";
string sql = string.Format(@"SELECT {0} FROM ""{1}"" WHERE {2} GROUP BY {3}", fields, tableName, where, groupby);
NpgsqlCommand command = new NpgsqlCommand(sql, conn);
NpgsqlDataReader dr = command.ExecuteReader();
while (dr.Read())
{
DestInfo destInfo = new DestInfo();
destInfo.Airport = dr[origin != "" ? "DEST" : "ORIGIN"].ToString();
destInfo.TotalPax = null;
destInfo.TotalFreight = null;
destInfo.RouteGeometry = Utils.ProcessWktGeometryString(dr["GEOM"].ToString());
destInfo.PartialData = false;
destInfo.NoData = true;
destInfo.DataSource = "ConnectionData";
destList.Add(destInfo);
}
} catch (NpgsqlException e) {
} finally {
conn.Close();
}
}
public static string QueryByRoute(string year, string origin, string dest, string locale)
{
NpgsqlConnection conn = null;
string res = "";
Airport originAirport = AirportData.Query(origin);
Airport destAirport = AirportData.Query(dest);
if (originAirport == null || destAirport == null)
{
return("");
}
double distKm = Utils.DistEarth(originAirport.Geometry.x, originAirport.Geometry.y, destAirport.Geometry.x, destAirport.Geometry.y);
double distMile = Math.Round(distKm * 0.621371, 0);
double distNm = Math.Round(distKm * 0.539957, 0);
distKm = Math.Round(distKm, 0);
try {
conn = new NpgsqlConnection(ASTDatabase.connStr2);
conn.Open();
string where = ASTDatabase.MakeWhere(year, "", origin, dest);
string[] fields = new string[] { Utils.DoubleQuoteStr("AIRLINE"), Utils.DoubleQuoteStr("DEPARTURE"),
Utils.DoubleQuoteStr("PAX"),
Utils.DoubleQuoteStr("MONTH_DEPARTURE"), Utils.DoubleQuoteStr("MONTH_PAX") };
string fieldStr = String.Join(",", fields);
string sql = string.Format(@"SELECT {0} FROM ""{1}"" WHERE {2}", fieldStr, MetaData.SummaryTableName, where);
NpgsqlCommand command = new NpgsqlCommand(sql, conn);
NpgsqlDataReader dr = command.ExecuteReader();
while (dr.Read())
{
if (res != "")
{
res += ",";
}
Dictionary <string, string> item = new Dictionary <string, string>();
IDataRecord record = ( IDataRecord )dr;
for (int i = 0; i < record.FieldCount; i++)
{
item.Add(record.GetName(i), record[i].ToString());
}
Airline carrier = AirlineData.Query(item["AIRLINE"], locale);
item.Add("AIRLINE_NAME", carrier.FullName);
string json = new JavaScriptSerializer().Serialize(item);
res += json;
}
} catch (NpgsqlException e) {
} finally {
conn.Close();
}
res = "\"routes\":[" + res + "],";
res += "\"distKm\":" + distKm.ToString() + ",";
res += "\"distMile\":" + distMile.ToString() + ",";
res += "\"distNm\":" + distNm.ToString();
res = "{" + res + "}";
return(res);
}
public static List <DestInfo> QueryDestByOrigin(string year, string origin, string dest, string airline, string dataSource, string locale)
{
HashSet <string> validSrc = new HashSet <string>(dataSource.Split(','));
List <DestInfo> res = new List <DestInfo>();
NpgsqlConnection conn = null;
try {
conn = new NpgsqlConnection(ASTDatabase.connStr2);
conn.Open();
// Query T100 Data
string where = ASTDatabase.MakeWhere(year, airline, origin, dest);
string groupby = " \"GEOM\", " + (origin != "" ? "\"DEST\"" : "\"ORIGIN\"");
string fields = origin != "" ? "\"DEST\"" : "\"ORIGIN\"";
fields += ", ST_AsText(\"GEOM\") AS \"GEOM\", SUM(\"PAX\") AS \"SUM_PAX\", SUM(\"FREIGHT\") AS \"SUM_FREIGHT\" ";
string sql = string.Format(@"SELECT {0} FROM ""{1}"" WHERE {2} GROUP BY {3}", fields, MetaData.SummaryTableName, where, groupby);
NpgsqlCommand command = new NpgsqlCommand(sql, conn);
NpgsqlDataReader dr = command.ExecuteReader();
while (dr.Read())
{
DestInfo destInfo = new DestInfo();
destInfo.Airport = dr[origin != "" ? "DEST" : "ORIGIN"].ToString();
destInfo.TotalPax = Convert.ToInt32(dr["SUM_PAX"].ToString());
destInfo.TotalFreight = Convert.ToInt32(dr["SUM_FREIGHT"].ToString());
// Determine the dataSource of airport
Airport airport1 = AirportData.Query(destInfo.Airport);
Airport airport2 = AirportData.Query(origin != "" ? origin : dest);
destInfo.RouteGeometry = Utils.ProcessWktGeometryString(dr["GEOM"].ToString());
if (airport1.Country != DataSourceRegister.GetDataSrc("T100Data").Country&&
airport2.Country != DataSourceRegister.GetDataSrc("T100Data").Country)
{
destInfo.PartialData = true;
destInfo.DataSource = "T100FF";
}
else
{
destInfo.DataSource = "T100Data";
}
if (validSrc.Contains(destInfo.DataSource) || dataSource == "")
{
res.Add(destInfo);
}
}
} finally {
conn.Close();
}
return(res);
}
public static List <DestInfo> QueryDestByOrigin(string year, string origin, string dest, string airline, string locale)
{
List <DestInfo> res = new List <DestInfo>();
NpgsqlConnection conn = null;
try {
conn = new NpgsqlConnection(ASTDatabase.connStr2);
conn.Open();
string where = ASTDatabase.MakeWhere(year, airline, origin, dest);
string fields = origin != "" ? "\"DEST\"" : "\"ORIGIN\"";
fields += ", \"PAX\"";
fields += ", ST_AsText(\"GEOM\") AS \"GEOM\"";
string sql = string.Format(@"SELECT {0} FROM ""{1}"" WHERE {2} ", fields, MetaData.SummaryTableName, where);
NpgsqlCommand command = new NpgsqlCommand(sql, conn);
NpgsqlDataReader dr = command.ExecuteReader();
while (dr.Read())
{
DestInfo destInfo = new DestInfo();
destInfo.Airport = dr[origin != "" ? "DEST" : "ORIGIN"].ToString();
destInfo.TotalPax = Convert.ToInt32(dr["PAX"].ToString());
destInfo.TotalFreight = null;
destInfo.DataSource = "JapanData";
destInfo.RouteGeometry = Utils.ProcessWktGeometryString(dr["GEOM"].ToString());
destInfo.PartialData = false;
// Now we don't support freight data
if (destInfo.TotalPax > 0)
{
res.Add(destInfo);
}
}
} finally {
conn.Close();
}
return(res);
}
private static void QueryByRouteInternal(string tableName, string year, string origin, string dest, ref string jsonResult)
{
NpgsqlConnection conn = null;
try {
conn = new NpgsqlConnection(ASTDatabase.connStr2);
conn.Open();
string where = " WHERE " + ASTDatabase.MakeWhere(year, "", origin, dest);
string[] fields = new string[] { Utils.DoubleQuoteStr("AIRLINE"), Utils.DoubleQuoteStr("SEASONAL") };
string fieldStr = String.Join(",", fields);
string sql = "SELECT " + fieldStr + " FROM \"" + tableName + "\"" + where;
NpgsqlCommand command = new NpgsqlCommand(sql, conn);
NpgsqlDataReader dr = command.ExecuteReader();
while (dr.Read())
{
if (jsonResult != "")
{
jsonResult += ",";
}
Dictionary <string, string> item = new Dictionary <string, string>();
IDataRecord record = ( IDataRecord )dr;
for (int i = 0; i < record.FieldCount; i++)
{
item.Add(record.GetName(i), record[i].ToString());
}
string json = new JavaScriptSerializer().Serialize(item);
jsonResult += json;
}
} catch (NpgsqlException e) {
} finally {
conn.Close();
}
}
static public string QueryRouteAircraftStat(string year, string origin, string dest, string locale)
{
NpgsqlConnection conn = null;
JavaScriptSerializer jsoner = new JavaScriptSerializer();
try {
int totalPax = 0;
int totalFreight = 0;
conn = new NpgsqlConnection(ASTDatabase.connStr2);
conn.Open();
string where = ASTDatabase.MakeWhere(year, "", origin, dest);
string[] fields = new string[] { Utils.DoubleQuoteStr("AIRLINE"), Utils.DoubleQuoteStr("AIRCRAFT_PAX"), Utils.DoubleQuoteStr("AIRCRAFT_FREIGHT"),
Utils.DoubleQuoteStr("AIRCRAFT_DEPARTURE"), Utils.DoubleQuoteStr("PAX"), Utils.DoubleQuoteStr("FREIGHT"), Utils.DoubleQuoteStr("DEPARTURE") };
string fieldStr = String.Join(",", fields);
string sql = "SELECT " + fieldStr + " FROM \"T100DataSummary\" WHERE " + where;
NpgsqlCommand command = new NpgsqlCommand(sql, conn);
NpgsqlDataReader dr = command.ExecuteReader();
Dictionary <string, int> paxSummary = new Dictionary <string, int>();
Dictionary <string, int> freightSummary = new Dictionary <string, int>();
Dictionary <string, int> departureSummary = new Dictionary <string, int>();
Dictionary <string, object> dictRouteRes = new Dictionary <string, object>();
Dictionary <string, Aircraft> aircraftDict = new Dictionary <string, Aircraft>();
while (dr.Read())
{
Dictionary <string, object> route = new Dictionary <string, object>();
route["airline"] = dr["AIRLINE"].ToString();
Airline carrier = AirlineData.Query(dr["AIRLINE"].ToString(), locale);
route["airlineName"] = carrier.FullName;
route["aircraftPax"] = ProcessAirlineItem(paxSummary, jsoner.Deserialize <Dictionary <string, int> >(dr["AIRCRAFT_PAX"].ToString()));
route["aircraftFreight"] = ProcessAirlineItem(freightSummary, jsoner.Deserialize <Dictionary <string, int> >(dr["AIRCRAFT_FREIGHT"].ToString()));
route["aircraftDeparture"] = ProcessAirlineItem(departureSummary, jsoner.Deserialize <Dictionary <string, int> >(dr["AIRCRAFT_DEPARTURE"].ToString()));
totalPax += Convert.ToInt32(dr["PAX"]);
totalFreight += Convert.ToInt32(dr["FREIGHT"]);
Dictionary <string, int> localAircraft = jsoner.Deserialize <Dictionary <string, int> >(dr["AIRCRAFT_DEPARTURE"].ToString());
foreach (KeyValuePair <string, int> item in localAircraft)
{
string aircraft = item.Key;
if (aircraft != "Other")
{
aircraftDict[aircraft] = AircraftData.Query(aircraft, locale);
}
}
dictRouteRes[dr["AIRLINE"].ToString()] = route;
}
Dictionary <string, object> total = new Dictionary <string, object>();
total["airline"] = "All";
total["airlineName"] = "";
total["aircraftPax"] = ProcessSummaryDict(paxSummary);
total["aircraftFreight"] = ProcessSummaryDict(freightSummary);
total["aircraftDeparture"] = ProcessSummaryDict(departureSummary);
dictRouteRes["All"] = total;
Airport originAirport = AirportData.Query(origin, locale);
Airport destAirport = AirportData.Query(dest, locale);
Dictionary <string, object> dictRes = new Dictionary <string, object>()
{
{ "routes", dictRouteRes },
{ "aircrafts", aircraftDict },
{ "origin", originAirport },
{ "dest", destAirport },
{ "totalPax", totalPax },
{ "totalFreight", totalFreight }
};
return(jsoner.Serialize(dictRes));
} finally {
conn.Close();
}
return("");
}
public static string QueryByAirlines(string year, string airline, string region, string locale, int limit = 100)
{
NpgsqlConnection conn = null;
string currentCountry = DataSourceRegister.GetDataSrc("T100Data").Country;
try {
string res = "";
conn = new NpgsqlConnection(ASTDatabase.connStr2);
conn.Open();
JavaScriptSerializer jsoner = new JavaScriptSerializer();
string where = ASTDatabase.MakeWhere(year, airline, "", "");
Airline carrier = AirlineData.Query(airline, locale);
if (carrier == null)
{
return("");
}
string flowField = carrier.Type == "Passenger" ? "PAX" : "FREIGHT";
// TODO: Fix table name
string[] fields = new string[] { Utils.DoubleQuoteStr("ORIGIN"), Utils.DoubleQuoteStr("DEST"),
Utils.DoubleQuoteStr(flowField), Utils.DoubleQuoteStr("DEPARTURE"), @"ST_AsText(""T100DataSummary"".""GEOM"") AS ""GEOM""",
@"""AP1"".""COUNTRY"" as ""ORIGIN_COUNTRY""", @"""AP2"".""COUNTRY"" as ""DEST_COUNTRY""" };
string fieldStr = String.Join(",", fields);
if (region == "US")
{
where += string.Format(@" AND ( ""AP1"".""COUNTRY"" = '{0}' AND ""AP2"".""COUNTRY"" = '{1}' ) ", currentCountry, currentCountry);
}
else if (region == "Intl")
{
where += string.Format(@" AND ( ""AP1"".""COUNTRY"" <> '{0}' OR ""AP2"".""COUNTRY"" <> '{1}' ) ", currentCountry, currentCountry);
}
where += " AND \"AP1\".\"CODE\" = \"T100DataSummary\".\"ORIGIN\" AND \"AP2\".\"CODE\" = \"T100DataSummary\".\"DEST\" ";
string sql = string.Format(@"SELECT {0} FROM ""T100DataSummary"" , ""CommonData_Airport"" AS ""AP1"", ""CommonData_Airport"" AS ""AP2"" WHERE {1} ORDER BY ""{2}"" DESC LIMIT {3}",
fieldStr, where, flowField, limit);
NpgsqlCommand command = new NpgsqlCommand(sql, conn);
NpgsqlDataReader dr = command.ExecuteReader();
while (dr.Read())
{
Dictionary <string, object> route = new Dictionary <string, object>();
Airport oAirport = AirportData.Query(dr["ORIGIN"].ToString(), locale);
Airport dAirport = AirportData.Query(dr["DEST"].ToString(), locale);
int flow = Convert.ToInt32(dr[flowField]);
if (flow == 0)
{
continue;
}
route["origin"] = dr["ORIGIN"].ToString();
route["originCity"] = oAirport.ServeCity[0];
route["originCountry"] = oAirport.Country;
route["originGeom"] = oAirport.Geometry;
route["dest"] = dr["DEST"].ToString();
route["destCity"] = dAirport.ServeCity[0];
route["destCountry"] = dAirport.Country;
route["destGeom"] = dAirport.Geometry;
route["flow"] = Convert.ToInt32(dr[flowField]);
route["departure"] = dr["DEPARTURE"].ToString();
string geom = dr["GEOM"].ToString();
geom = geom.Replace("MULTILINESTRING", "");
geom = geom.Replace("(", "[");
geom = geom.Replace(")", "]");
route["geom"] = geom;
if (res != "")
{
res += ",";
}
res += jsoner.Serialize(route);
}
return("[" + res + "]");
} finally {
conn.Close();
}
return("");
}
/// <summary>
/// Query the airport statistics: top destinations and airline share
/// </summary>
/// <param name="dataSrc">The data source for the query: T100, TwData, KrData, etc.</param>
public static string QueryAirportStat(string dataSrc, string year, string airport, string locale)
{
// Get the meta data of specific data source
ADataSourceMetaData metaData = null;
if (!DataSourceRegister.Register.TryGetValue(dataSrc, out metaData))
{
return("");
}
NpgsqlConnection conn = null;
string res = "";
try {
conn = new NpgsqlConnection(ASTDatabase.connStr2);
conn.Open();
string[] fields = new string[] { Utils.DoubleQuoteStr("AIRLINE"), Utils.DoubleQuoteStr("DEST"), Utils.DoubleQuoteStr("PAX"), Utils.DoubleQuoteStr("FREIGHT") };
string fieldStr = String.Join(",", fields);
string sql = String.Format(@"SELECT {0} FROM ""{1}"" WHERE {2}", fieldStr, metaData.SummaryTableName, ASTDatabase.MakeWhere(year, "", airport, ""));
NpgsqlCommand command = new NpgsqlCommand(sql, conn);
NpgsqlDataReader dr = command.ExecuteReader();
List <RouteStat> routes = new List <RouteStat>();
while (dr.Read())
{
RouteStat route = new RouteStat();
route.Airline = dr["AIRLINE"].ToString();
route.Dest = dr["DEST"].ToString();
route.Pax = metaData.HasPaxData ? Convert.ToInt32(dr["PAX"]) : 0;
route.Freight = metaData.HasFreightData ? Convert.ToInt32(dr["FREIGHT"]) : 0;
routes.Add(route);
}
string[] regions = new string[] { "All", "International", "Domestic" };
foreach (string region in regions)
{
if (res != "")
{
res += ",";
}
res += Utils.DoubleQuoteStr(region) + ":" + CreateRankByRegion(routes, region, metaData, locale);
}
return("{" + res + "}");
} finally {
conn.Close();
}
return("");
}
