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 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);
}