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);
}
/// <summary>
/// Query the available data sources of an aiport, regardless year.
/// </summary>
public static string QueryAirportAvailableDataSource(string airportCode, string locale)
{
Airport airport = AirportData.Query(airportCode, locale);
if (airport == null)
{
return("");
}
NpgsqlConnection conn = null;
try {
conn = new NpgsqlConnection(ASTDatabase.connStr2);
conn.Open();
string sql = string.Format(@"SELECT ""DATA_SOURCE"" FROM ""AirportAvailability"" WHERE ""CODE"" = {0}", Utils.SingleQuoteStr(airportCode));
NpgsqlCommand command = new NpgsqlCommand(sql, conn);
NpgsqlDataReader dr = command.ExecuteReader();
List <string> lstResult = new List <string>();
// Determine the primate data source of the given airport
string primaryDataSrc = DataSourceRegister.QueryCountryByDataSrc(airport.Country);
if (primaryDataSrc != "")
{
lstResult.Add(primaryDataSrc);
}
while (dr.Read())
{
string dataSrc = dr["DATA_SOURCE"].ToString();
if (dataSrc == "ConnectionData")
{
continue; //TODO
}
if (dataSrc != primaryDataSrc)
{
lstResult.Add(dataSrc);
}
}
return(new JavaScriptSerializer().Serialize(lstResult));
} 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("");
}