public List <AircraftPositionDesignator> AircraftPositionGetAll(BackgroundWorker caller)
{
// gets all aircraftpositions from database
// supports abort calculation if called from background worker and cancellation requested
List <AircraftPositionDesignator> l = new List <AircraftPositionDesignator>();
int i = 0;
SQLiteCommand cmd = new SQLiteCommand(db.DBConnection);
cmd.CommandText = "SELECT * FROM " + AircraftPositionDesignator.TableName + " ORDER BY LastUpdated ASC";
SQLiteDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
AircraftPositionDesignator ap = new AircraftPositionDesignator((IDataRecord)reader);
l.Add(ap);
i++;
// abort calculation if called from background worker and cancellation pending
if (caller != null)
{
if (caller.WorkerSupportsCancellation && caller.CancellationPending)
{
return(new List <AircraftPositionDesignator>());
}
if (caller.WorkerReportsProgress && (i % 1000 == 0))
{
caller.ReportProgress(0, "Getting position " + i.ToString() + " of");
}
}
}
reader.Close();
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 int AircraftPositionBulkDeleteOlderThan(DateTime olderthan)
{
// deletes all entries from data table older than xxx
AircraftPositionDesignator ap = new AircraftPositionDesignator();
ap.LastUpdated = olderthan;
int i = 0;
try
{
lock (db)
{
db.BeginTransaction();
lock (db.DBCommand)
{
db.DBCommand.CommandText = "DELETE FROM " + AircraftPositionDesignator.TableName + " WHERE LastUpdated < @LastUpdated";
db.DBCommand.Parameters.Clear();
db.DBCommand.Parameters.Add(ap.AsUNIXTime("LastUpdated"));
i = db.ExecuteNonQuery(db.DBCommand);
}
db.Commit();
}
}
catch (Exception ex)
{
Log.WriteMessage(ex.ToString(), LogLevel.Error);
}
return(i);
}
public AircraftPositionDesignator(AircraftPositionDesignator ap)
{
Hex = ap.Hex;
Call = ap.Call;
Lat = ap.Lat;
Lon = ap.Lon;
Alt = ap.Alt;
Track = ap.Track;
Speed = ap.Speed;
LastUpdated = ap.LastUpdated;
}
public int AircraftPositionDelete(AircraftPositionDesignator ap)
{
lock (db.DBCommand)
{
db.DBCommand.CommandText = "DELETE FROM " + AircraftPositionDesignator.TableName + " WHERE Hex = @Hex AND LastUpdated = @LastUpdated";
db.DBCommand.Parameters.Clear();
db.DBCommand.Parameters.Add(ap.AsString("Hex"));
db.DBCommand.Parameters.Add(ap.AsUNIXTime("LastUpdated"));
return(db.ExecuteNonQuery(db.DBCommand));
}
}
public int AircraftPositionDeleteOlderThan(DateTime olderthan)
{
// deletes all entries from data table older than xxx
AircraftPositionDesignator ap = new AircraftPositionDesignator();
ap.LastUpdated = olderthan;
lock (db.DBCommand)
{
db.DBCommand.CommandText = "DELETE FROM " + AircraftPositionDesignator.TableName + " WHERE LastUpdated < @LastUpdated";
db.DBCommand.Parameters.Clear();
db.DBCommand.Parameters.Add(ap.AsUNIXTime("LastUpdated"));
return(db.ExecuteNonQuery(db.DBCommand));
}
}
public int AircraftPositionInsertOrUpdateIfNewer(AircraftPositionDesignator ap)
{
AircraftPositionDesignator an = AircraftPositionFind(ap);
if (an == null)
{
int result = AircraftPositionInsert(ap);
// Console.WriteLine("Aircraft position inserted: " + ap.Hex + "," + ap.LastUpdated.ToString("yyyy-MM-dd HH:mm:ss") + ", " + result.ToString());
return(result);
}
if (ap.LastUpdated > an.LastUpdated)
{
return(AircraftPositionUpdate(ap));
}
return(0);
}
public bool AircraftPositionExists(AircraftPositionDesignator ap)
{
lock (db.DBCommand)
{
db.DBCommand.CommandText = "SELECT EXISTS (SELECT LastUpdated FROM " + AircraftPositionDesignator.TableName + " WHERE Hex = @Hex AND LastUpdated = @LastUpdated";
db.DBCommand.Parameters.Clear();
db.DBCommand.Parameters.Add(ap.AsString("Hex"));
db.DBCommand.Parameters.Add(ap.AsUNIXTime("LastUpdated"));
object result = db.DBCommand.ExecuteScalar();
if (IsValid(result) && ((long)result > 0))
{
return(true);
}
}
return(false);
}
public int AircraftPositionUpdate(AircraftPositionDesignator ap)
{
lock (db.DBCommand)
{
db.DBCommand.CommandText = "UPDATE " + AircraftPositionDesignator.TableName + " SET Hex = @Hex, Call = @Call, Lat = @Lat, Lon = @Lon, Alt = @Alt, Track = @Track, Speed = @Speed, LastUpdated = @LastUpdated WHERE Hex = @Hex AND LastUpdated = @LastUpdated";
db.DBCommand.Parameters.Clear();
db.DBCommand.Parameters.Add(ap.AsString("Hex"));
db.DBCommand.Parameters.Add(ap.AsString("Call"));
db.DBCommand.Parameters.Add(ap.AsDouble("Lat"));
db.DBCommand.Parameters.Add(ap.AsDouble("Lon"));
db.DBCommand.Parameters.Add(ap.AsDouble("Alt"));
db.DBCommand.Parameters.Add(ap.AsDouble("Track"));
db.DBCommand.Parameters.Add(ap.AsDouble("Speed"));
db.DBCommand.Parameters.Add(ap.AsUNIXTime("LastUpdated"));
return(db.ExecuteNonQuery(db.DBCommand));
}
}
public int AircraftPositionInsert(AircraftPositionDesignator ap)
{
lock (db.DBCommand)
{
db.DBCommand.CommandText = "INSERT INTO " + AircraftPositionDesignator.TableName + " (Hex, Call, Lat, Lon, Alt, Track, Speed, LastUpdated) VALUES (@Hex, @Call, @Lat, @Lon, @Alt, @Track, @Speed, @LastUpdated)";
db.DBCommand.Parameters.Clear();
db.DBCommand.Parameters.Add(ap.AsString("Hex"));
db.DBCommand.Parameters.Add(ap.AsString("Call"));
db.DBCommand.Parameters.Add(ap.AsDouble("Lat"));
db.DBCommand.Parameters.Add(ap.AsDouble("Lon"));
db.DBCommand.Parameters.Add(ap.AsDouble("Alt"));
db.DBCommand.Parameters.Add(ap.AsDouble("Track"));
db.DBCommand.Parameters.Add(ap.AsDouble("Speed"));
db.DBCommand.Parameters.Add(ap.AsUNIXTime("LastUpdated"));
return(db.ExecuteNonQuery(db.DBCommand));
}
}
public AircraftPositionDesignator AircraftPositionFind(AircraftPositionDesignator ap)
{
lock (db.DBCommand)
{
db.DBCommand.CommandText = "SELECT * FROM " + AircraftPositionDesignator.TableName + " WHERE Hex = @Hex AND LastUpdated = @LastUpdated";
db.DBCommand.Parameters.Clear();
db.DBCommand.Parameters.Add(ap.AsString("Hex"));
db.DBCommand.Parameters.Add(ap.AsUNIXTime("LastUpdated"));
try
{
DataTable Result = db.Select(db.DBCommand);
if (IsValid(Result) && (Result.Rows.Count > 0))
{
return(new AircraftPositionDesignator(Result.Rows[0]));
}
}
catch (Exception ex)
{
Log.WriteMessage(ex.ToString(), LogLevel.Error);
}
}
return(null);
}
public int AircraftPositionDelete(string hex, DateTime lastupdated)
{
AircraftPositionDesignator ap = new AircraftPositionDesignator(hex, lastupdated);
return(AircraftPositionDelete(ap));
}
public AircraftPositionDesignator AircraftPositionFind(string hex, DateTime lastupdated)
{
AircraftPositionDesignator ad = new AircraftPositionDesignator(hex, lastupdated);
return(AircraftPositionFind(ad));
}
public bool AircraftPositionExists(string hex, DateTime lastupdated)
{
AircraftPositionDesignator ap = new AircraftPositionDesignator(hex, lastupdated);
return(AircraftPositionExists(ap));
}