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);
}
private static string AirlineRankToJson(List <KeyValuePair <string, int> > rank, int totalFlow, string locale)
{
if (totalFlow == 0)
{
return("[]");
}
JavaScriptSerializer jsoner = new JavaScriptSerializer();
int otherFlow = 0;
string res = "";
for (int i = 0; i < rank.Count; i++)
{
if (i < MaxAirlineNumberInRank)
{
Dictionary <string, string> item = new Dictionary <string, string>();
Airline airline = AirlineData.Query(rank[i].Key, locale);
item["airline"] = airline.FullName + " (" + airline.Iata + ")";
item["flow"] = rank[i].Value.ToString();
item["per"] = (( double )rank[i].Value * 100.0 / totalFlow).ToString("N1") + "%";
if (i != 0)
{
res += ", ";
}
res += jsoner.Serialize(item);
}
else
{
otherFlow += rank[i].Value;
}
}
if (otherFlow > 0)
{
Dictionary <string, string> item = new Dictionary <string, string>();
item["airline"] = Localization.QueryLocale(locale)._other;
item["flow"] = otherFlow.ToString();
item["per"] = (( double )otherFlow * 100.0 / totalFlow).ToString("N1") + "%";
res += ", " + jsoner.Serialize(item);
}
return("[" + res + "]");
}
/// <summary>
/// Query the available airlines of given data sources and given year
/// </summary>
/// <param name="strListDataSrc">The required data sources. Empty string means all the data sources. Multiple data sources are splitted by ","</param>
/// <param name="year">The required year. Empty year means all the years</param>
/// <returns></returns>
public static string QueryAvailableAirlineByDataSource(string strListDataSrc, string year, string locale)
{
NpgsqlConnection conn = null;
string res = "";
try {
conn = new NpgsqlConnection(ASTDatabase.connStr2);
conn.Open();
string where = "";
if (strListDataSrc != "")
{
List <string> lstDataSrc = new List <string>(strListDataSrc.Split(','));
foreach (string dataSrc in lstDataSrc)
{
if (where != "")
{
where += " OR ";
}
where += string.Format(@" ""DATA_SOURCE""= '{0}'", dataSrc);
}
where = " ( " + where + " ) ";
}
if (year != "")
{
if (where != "")
{
where += " AND ";
}
where = string.Format(@" ""YEAR"" = '{0}' ", year);
}
if (where != "")
{
where = " WHERE " + where;
}
string sql = string.Format(@"SELECT DISTINCT ""CODE"" FROM ""AirlineAvailability"" {0} ", where);
NpgsqlCommand command = new NpgsqlCommand(sql, conn);
NpgsqlDataReader dr = command.ExecuteReader();
Dictionary <string, Airline> airlines = new Dictionary <string, Airline>();
while (dr.Read())
{
string airline = dr["CODE"].ToString();
if (airline == Airline.All_AIRLINE)
{
continue;
}
Airline item = AirlineData.Query(airline, locale);
if (res != "")
{
res += ",";
}
res += string.Format("[{0}, {1}, {2}, {3}, {4}, {5}]",
Utils.DoubleQuoteStr(item.Code),
Utils.DoubleQuoteStr(item.Iata),
Utils.DoubleQuoteStr(item.FullName),
Utils.DoubleQuoteStr(item.Country),
Utils.DoubleQuoteStr(item.Type),
Utils.DoubleQuoteStr(item.Note));
}
} finally {
conn.Close();
}
return("[" + res + "]");
}
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 time series of a route
/// </summary>
/// <param name="flowType">The query type: "pax;freight": query the flow and "seat": query the seat and load factor</param>
/// <returns></returns>
public static string QueryRouteTimeSeries(string dataSrc, string origin, string dest, string flowType, string locale)
{
// Get the meta data of specific data source
ADataSourceMetaData metaData = null;
if (!DataSourceRegister.Register.TryGetValue(dataSrc, out metaData))
{
return("");
}
NpgsqlConnection conn = null;
try {
conn = new NpgsqlConnection(ASTDatabase.connStr2);
conn.Open();
string condition = "";
if (flowType == "pax;freight")
{
condition = @" AND ( ""FLOW_TYPE"" = 'Pax' OR ""FLOW_TYPE"" = 'Freight') ";
}
else if (flowType == "seat")
{
condition = @" AND ( ""FLOW_TYPE"" = 'Pax' OR ""FLOW_TYPE"" = 'Seat') ";
}
string[] fields = new string[] { Utils.DoubleQuoteStr("AIRLINE"), Utils.DoubleQuoteStr("FLOW_TYPE"), Utils.DoubleQuoteStr("TIME_SERIES") };
string fieldStr = String.Join(",", fields);
string sql = string.Format(@"SELECT {0} FROM ""{1}"" WHERE ""ORIGIN""= {2} AND ""DEST""= {3} {4} ORDER BY ""SUMFLOW"" DESC",
fieldStr, metaData.RouteTimeSeriesTableName, Utils.SingleQuoteStr(origin), Utils.SingleQuoteStr(dest), condition);
NpgsqlCommand command = new NpgsqlCommand(sql, conn);
NpgsqlDataReader dr = command.ExecuteReader();
Dictionary <string, string> jsonRes = new Dictionary <string, string>()
{
{ "Pax", "" }, { "Freight", "" }, { "Seat", "" }
};
while (dr.Read())
{
Airline carrier = AirlineData.Query(dr["AIRLINE"].ToString(), locale);
string airlineName = dr["AIRLINE"].ToString();
if (airlineName == AnyAirline)
{
airlineName = Localization.QueryLocale(locale)._anyAirline;
}
if (carrier != null)
{
airlineName = carrier.FullName + " (" + dr["AIRLINE"].ToString() + ")";
}
string dbFlowType = dr["FLOW_TYPE"].ToString();
if (jsonRes[dbFlowType] != "")
{
jsonRes[dbFlowType] += ",";
}
jsonRes[dbFlowType] += Utils.DoubleQuoteStr(airlineName) + ":" + dr["TIME_SERIES"].ToString();
}
if (flowType == "pax;freight")
{
string res = String.Format("\"pax\": {{ {0} }}, \"freight\":{{ {1} }}", jsonRes["Pax"], jsonRes["Freight"]);
return("{" + res + "}");
}
else if (flowType == "seat")
{
string res = String.Format("\"pax\": {{ {0} }}, \"seat\":{{ {1} }}", jsonRes["Pax"], jsonRes["Seat"]);
return("{" + res + "}");
}
} catch (NpgsqlException e) {
} finally {
conn.Close();
}
return("");
}