Esempio n. 1
0
        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);
        }
Esempio n. 2
0
        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 + "]");
        }
Esempio n. 4
0
        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("");
        }
Esempio n. 5
0
        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("");
        }
Esempio n. 6
0
        /// <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("");
        }