public List <FlightPosition> FlightStat(String DSN, bool DemoMode = false, ADSBQuery QueryData = null) { var Data = new List <FlightPosition>(); isDemoMode = DemoMode; if (QueryData == null) { QueryData = new ADSBQuery(); } //connect to database for reading data using (CN = new SqlConnection(DSN)) { CN.Open(); setActiveFlights(); //get active positions Data = getLivePositions(QueryData); //Save changes to query data if updated. if (QueryData.IsQueryChanged == 1) { QueryData.SetDefaults(CN); } } return(Data); }
public void SetSummary(SqlConnection CN) { ADSBQuery adsb = new ADSBQuery(); adsb.GetDefaults(CN); String SQL1 = $"select Count(DISTINCT FromHexCode) from ADSBDetailHistory WHERE VerticalDistance <= {adsb.vBreach * adsb.FeetToKiloMeter} and HorizontalDistance <= {adsb.hBreach} and CreatedDate >=DATEADD(day, -1, GETDATE());"; Breach24H = GetDBInt(CN, SQL1); TotalRPAS = GetDBInt(CN, "select Count(*) from AdsbLive WHERE FlightSource='SkyCommander'"); Area = GetArea(CN); }
public void SetBreachFlights(SqlConnection CN, ADSBQuery QueryData) { String SQL = $"Select ToHexCode FROM ADSBDetail WHERE HorizontalDistance <= {QueryData.hBreach} AND VerticalDistance <= {QueryData.vBreach} AND FromHexCode = '{HexCode}'"; using (SqlCommand cmd = new SqlCommand(SQL, CN)) { using (SqlDataReader RS = cmd.ExecuteReader()) { BreachToFlights = new List <String>(); while (RS.Read()) { BreachToFlights.Add(RS["ToHexCode"].ToString().ToUpper()); } } //using(SqlDataReader RS) } //using(SqlCommand cmd) } //public void SetBreachFlights
} //public void SetBreachFlights public void SetAlertFlights(SqlConnection CN, ADSBQuery QueryData) { String SQL = $"Select ToHexCode FROM ADSBDetail WHERE HorizontalDistance <= {QueryData.hAlert} AND VerticalDistance <= {QueryData.vAlert} AND FromHexCode = '{HexCode}'"; if (BreachToFlights.Any()) { SQL = SQL + " AND ToHexCode NOT IN('" + String.Join("','", BreachToFlights) + "')"; } using (SqlCommand cmd = new SqlCommand(SQL, CN)) { using (SqlDataReader RS = cmd.ExecuteReader()) { AlertToFlights = new List <String>(); while (RS.Read()) { AlertToFlights.Add(RS["ToHexCode"].ToString().ToUpper()); } } //using(SqlDataReader RS) } //using(SqlCommand cmd) } //public void SetAlertFlights
private List <FlightPosition> getLivePositions(Exponent.ADSB.ADSBQuery QueryData = null) { if (QueryData == null) { QueryData = new ADSBQuery(); } if (QueryData.tracking_adsb_commercial == 0 & QueryData.tracking_adsb_rpas == 0 && QueryData.tracking_adsb_skycommander == 0) { QueryData.tracking_adsb_commercial = 1; } var PositionDatas = new List <FlightPosition>(); StringBuilder Filter = new StringBuilder(); StringBuilder WHERE = new StringBuilder(); StringBuilder SQL = new StringBuilder(@" select [FlightId], [Heading], [TailNumber], [CallSign], [Lon], [Lat], [Speed], HeadingHistory, [Altitude], [AdsbDate], FlightSource, HexCode from AdsbLive "); if (QueryData.adsb_omdb == 1 || QueryData.adsb_omdw == 1 || QueryData.adsb_omsj == 1) { if (QueryData.adsb_omdb == 1) { if (Filter.Length > 0) { Filter.AppendLine(" OR"); } Filter.Append("[OMDB] <= " + QueryData.ATCRadious); } if (QueryData.adsb_omdw == 1) { if (Filter.Length > 0) { Filter.AppendLine(" OR"); } Filter.Append("[OMDW] <= " + QueryData.ATCRadious); } if (QueryData.adsb_omsj == 1) { if (Filter.Length > 0) { Filter.AppendLine(" OR"); } Filter.Append("[OMSJ] <= " + QueryData.ATCRadious); } WHERE.Append("("); WHERE.Append(Filter); WHERE.Append(")"); Filter.Clear(); } Filter.Append(QueryData.getTrackingFilter()); if (Filter.Length > 0) { if (WHERE.Length > 0) { WHERE.AppendLine(" AND"); } WHERE.Append(Filter); Filter.Clear(); } if (WHERE.Length > 0) { SQL.AppendLine(" WHERE"); SQL.Append(WHERE); } using (var Cmd = new SqlCommand(SQL.ToString(), CN)) { var RS = Cmd.ExecuteReader(); while (RS.Read()) { int fSpeed = RS.GetOrdinal("Speed"); int fHeading = RS.GetOrdinal("Heading"); string FlightSource = RS["FlightSource"].ToString(); var Position = new FlightPosition { FlightID = RS["FlightId"].ToString(), Heading = RS.IsDBNull(fHeading) ? 0 : (Double)RS.GetDecimal(fHeading), TailNumber = RS["TailNumber"].ToString(), CallSign = RS["CallSign"].ToString(), Lon = (Double)RS.GetDecimal(RS.GetOrdinal("Lon")), Lat = (Double)RS.GetDecimal(RS.GetOrdinal("Lat")), Speed = RS.IsDBNull(fSpeed) ? 0 : (Double)RS.GetDecimal(fSpeed), Altitude = (Double)RS.GetDecimal(RS.GetOrdinal("Altitude")), ADSBDate = RS.GetDateTime(RS.GetOrdinal("AdsbDate")), History = getHistory(RS["HeadingHistory"].ToString()), FlightSource = FlightSource, HexCode = RS["HexCode"].ToString().ToUpper() }; if (FlightSource == "SkyCommander") { if (Position.Altitude < 0) { Position.Altitude = 0; } if ((Position.Altitude >= QueryData.minAltitude && Position.Altitude <= QueryData.maxAltitude) && (Position.Speed >= QueryData.minSpeed && Position.Speed <= QueryData.maxSpeed)) { PositionDatas.Add(Position); } } else { PositionDatas.Add(Position); } } //while RS.Close(); } //using //Find the breaches and alerts for SkyCommander foreach (var Position in PositionDatas.Where(e => e.FlightSource == "SkyCommander").ToList()) { Position.SetBreachFlights(CN, QueryData); Position.SetAlertFlights(CN, QueryData); } return(PositionDatas); }