/// <summary>
/// Query the time series of an airport
/// </summary>
/// <returns></returns>
public static string QueryAirportTimeSeries(string dataSrc, string origin)
{
// 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[] 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}", fieldStr, metaData.AirportTimeSeriesTableName, Utils.SingleQuoteStr(origin));
NpgsqlCommand command = new NpgsqlCommand(sql, conn);
NpgsqlDataReader dr = command.ExecuteReader();
string jsonPax = "";
string jsonFreight = "";
while (dr.Read())
{
if (dr["FLOW_TYPE"].ToString() == "Pax")
{
if (jsonPax != "")
{
jsonPax += ",";
}
jsonPax += string.Format("{0}:{1}", Utils.DoubleQuoteStr(dr["AIRLINE"].ToString()), dr["TIME_SERIES"].ToString());
}
else if (dr["FLOW_TYPE"].ToString() == "Freight")
{
if (jsonFreight != "")
{
jsonFreight += ",";
}
jsonFreight += string.Format("{0}:{1}", Utils.DoubleQuoteStr(dr["AIRLINE"].ToString()), dr["TIME_SERIES"].ToString());
}
}
return(String.Format(@"{{""pax"": {{ {0} }}, ""freight"": {{ {1} }} }}", jsonPax, jsonFreight));
} catch (NpgsqlException e) {
} 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("");
}
private static void RegisterDataSource(string name, ADataSourceMetaData dataSrc)
{
Register[name] = dataSrc;
CountryToDataSrc[dataSrc.Country] = name;
}
/// <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("");
}
/// <summary>
/// Create the dest rank for the given region.
/// </summary>
private static string CreateRankByRegion(List <RouteStat> routes, string region, ADataSourceMetaData meteData, string locale)
{
Dictionary <string, int> dictPaxDest = new Dictionary <string, int>();
Dictionary <string, int> dictFreightDest = new Dictionary <string, int>();
Dictionary <string, int> dictPaxAirline = new Dictionary <string, int>();
Dictionary <string, int> dictFreightAirline = new Dictionary <string, int>();
foreach (RouteStat route in routes)
{
Airport airport = AirportData.Query(route.Dest, locale);
if (region == "International" && airport.Country != meteData.Country ||
region == "Domestic" && airport.Country == meteData.Country ||
region == "All")
{
AddFlowToDict(dictPaxDest, dictPaxAirline, route, item => item.Pax);
AddFlowToDict(dictFreightDest, dictFreightAirline, route, item => item.Freight);
}
}
List <KeyValuePair <string, int> > lstPaxDest = dictPaxDest.ToList();
List <KeyValuePair <string, int> > lstFreightDest = dictFreightDest.ToList();
List <KeyValuePair <string, int> > lstPaxAirline = dictPaxAirline.ToList();
List <KeyValuePair <string, int> > lstFreightAirline = dictFreightAirline.ToList();
lstPaxDest.Sort(ASTDatabase.CmpRankItem);
lstFreightDest.Sort(ASTDatabase.CmpRankItem);
lstPaxAirline.Sort(ASTDatabase.CmpRankItem);
lstFreightAirline.Sort(ASTDatabase.CmpRankItem);
// Create the destination rank for passenger and freight
string paxDestRank = DestRankToJson(lstPaxDest, locale);
string freightDestRank = DestRankToJson(lstFreightDest, locale);
// Create the airline rank for passenger and freight
int totalPax = lstPaxAirline.Sum(item => item.Value);
string paxAirlineRank = meteData.HasAirlineInfo ? AirlineRankToJson(lstPaxAirline, totalPax, locale) : "[]";
int totalFreight = lstFreightAirline.Sum(item => item.Value);
string freightAirlineRank = meteData.HasAirlineInfo ? AirlineRankToJson(lstFreightAirline, totalFreight, locale) : "[]";
return(string.Format(@"{{""paxDestRank"" : {0}, ""freightDestRank"" : {1}, ""paxAirlineRank"" : {2}, ""freightAirlineRank"" : {3}, ""totalPax"" : {4}, ""totalFreight"" : {5} }}",
paxDestRank, freightDestRank, paxAirlineRank, freightAirlineRank,
Utils.DoubleQuoteStr(Utils.FormatLargeNumber(totalPax, locale)),
Utils.DoubleQuoteStr(Utils.FormatLargeNumber(totalFreight, locale))));
}