public List <PlaneInfo> PlaneInfoGetAll(DateTime newerthan)
{
List <PlaneInfo> l = new List <PlaneInfo>();
int i = SupportFunctions.DateTimeToUNIXTime(newerthan);
// SELECT max(AircraftPositions.Lastupdated) AS LastUpdated, Call, Reg, AircraftPositions.Hex, Lat, Lon, Track, Alt, Speed, TypeCode, Manufacturer, Model, Category FROM AircraftPositions INNER JOIN Aircrafts ON AircraftPositions.Hex = Aircrafts.Hex INNER JOIN AircraftTypes ON AircraftTypes.ICAO = Aircrafts.TypeCode WHERE AircraftPositions.LastUpdated > 1500000 GROUP BY AircraftPositions.Hex
DataTable Result = db.Select("SELECT max(AircraftPositions.Lastupdated) AS LastUpdated, Call, Reg, AircraftPositions.Hex, Lat, Lon, Track, Alt, Speed, TypeCode, Manufacturer, Model, Category FROM AircraftPositions INNER JOIN Aircrafts ON AircraftPositions.Hex = Aircrafts.Hex INNER JOIN AircraftTypes ON AircraftTypes.ICAO = Aircrafts.TypeCode WHERE AircraftPositions.LastUpdated > " + i.ToString() + " GROUP BY AircraftPositions.Hex");
if (!IsValid(Result) || (Result.Rows.Count == 0))
{
return(l);
}
foreach (DataRow row in Result.Rows)
{
PlaneInfo info = new PlaneInfo(row);
try
{
l.Add(info);
}
catch (Exception ex)
{
Log.WriteMessage("Error inserting PlaneInfo[" + info.ToString() + "]: " + ex.ToString(), LogLevel.Error);
}
}
return(l);
}
///<summary>
/// Gets a list of aircraft positions at a time.
/// Querying the latest position entry per aircraft but not older than ttl back in history
/// and estimating the position at given time
/// <param name="at">The given time. </param>
/// <param name="ttl">"Time To Live": discard positions which are older than ttl [min]. </param>
///
/// </summary>
public List <AircraftPositionDesignator> AircraftPositionGetAllLatest(DateTime at, int ttl)
{
List <AircraftPositionDesignator> l = new List <AircraftPositionDesignator>();
int to = SupportFunctions.DateTimeToUNIXTime(at);
int from = to - ttl * 60;
DataTable Result = db.Select("SELECT Hex, Call, Lat, Lon, Alt, Track, Speed, max(Lastupdated) AS LastUpdated FROM " + AircraftPositionDesignator.TableName + " WHERE LastUpdated >= " + from.ToString() + " AND LastUpdated <= " + to.ToString() + " GROUP BY Hex");
if (!IsValid(Result) || (Result.Rows.Count == 0))
{
return(l);
}
foreach (DataRow row in Result.Rows)
{
AircraftPositionDesignator ap = new AircraftPositionDesignator(row);
//estimate new position
// change speed to km/h
double speed = ap.Speed * 1.852;
// calculate distance after timespan
double dist = speed * (at - ap.LastUpdated).TotalHours;
// estimate new position
LatLon.GPoint newpos = LatLon.DestinationPoint(ap.Lat, ap.Lon, ap.Track, dist);
ap.Lat = newpos.Lat;
ap.Lon = newpos.Lon;
l.Add(ap);
}
return(l);
}
public List <AircraftPositionDesignator> AircraftPositionGetAllLatest(DateTime from)
{
List <AircraftPositionDesignator> l = new List <AircraftPositionDesignator>();
int i = SupportFunctions.DateTimeToUNIXTime(from);
DataTable Result = db.Select("SELECT Hex, Call, Lat, Lon, Alt, Track, Speed, max(Lastupdated) AS LastUpdated FROM " + AircraftPositionDesignator.TableName + " WHERE LastUpdated > " + i.ToString() + " GROUP BY Hex");
if (!IsValid(Result) || (Result.Rows.Count == 0))
{
return(l);
}
foreach (DataRow row in Result.Rows)
{
l.Add(new AircraftPositionDesignator(row));
}
return(l);
}
public List <AircraftPositionDesignator> AircraftPositionGetAll(DateTime from, DateTime to)
{
List <AircraftPositionDesignator> l = new List <AircraftPositionDesignator>();
DataTable Result = db.Select("SELECT * FROM " + AircraftPositionDesignator.TableName + " WHERE LastUpdated >= " + SupportFunctions.DateTimeToUNIXTime(from).ToString() + " AND LastUpdated <= " + SupportFunctions.DateTimeToUNIXTime(to).ToString() + " ORDER BY LastUpdated ASC");
if (!IsValid(Result) || (Result.Rows.Count == 0))
{
return(l);
}
foreach (DataRow row in Result.Rows)
{
l.Add(new AircraftPositionDesignator(row));
}
return(l);
}
public List <string> AircraftPositionGetAllCalls(DateTime from, DateTime to)
{
List <string> l = new List <string>();
DataTable Result = db.Select("SELECT Call FROM " + AircraftPositionDesignator.TableName + " WHERE LastUpdated >= " + SupportFunctions.DateTimeToUNIXTime(from).ToString() + " AND LastUpdated <= " + SupportFunctions.DateTimeToUNIXTime(to).ToString() + " GROUP BY Call");
if (!IsValid(Result) || (Result.Rows.Count == 0))
{
return(l);
}
foreach (DataRow row in Result.Rows)
{
l.Add(row[0].ToString());
}
return(l);
}
private void dgv_Main_CellValidating(object sender, DataGridViewCellValidatingEventArgs e)
{
try
{
// update timestamp, if column "LastUpdated" is available
dgv_Main.Rows[dgv_Main.CurrentCell.RowIndex].Cells["LastUpdated"].Value = SupportFunctions.DateTimeToUNIXTime(DateTime.UtcNow);
}
catch (Exception ex)
{
// do nothing
}
}