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