public void BaseStationFlight_Initialises_To_Known_State_And_Properties_Work()
{
var baseStationFlight = new BaseStationFlight();
TestUtilities.TestProperty(baseStationFlight, r => r.Aircraft, null, new BaseStationAircraft());
TestUtilities.TestProperty(baseStationFlight, r => r.AircraftID, 0, 1224);
TestUtilities.TestProperty(baseStationFlight, r => r.Callsign, null, "Aa");
TestUtilities.TestProperty(baseStationFlight, r => r.EndTime, null, DateTime.Today);
TestUtilities.TestProperty(baseStationFlight, r => r.FirstAltitude, null, 1212);
TestUtilities.TestProperty(baseStationFlight, r => r.FirstGroundSpeed, null, 123.132f);
TestUtilities.TestProperty(baseStationFlight, r => r.FirstIsOnGround, false);
TestUtilities.TestProperty(baseStationFlight, r => r.FirstLat, null, 1239.85);
TestUtilities.TestProperty(baseStationFlight, r => r.FirstLon, null, 93.556);
TestUtilities.TestProperty(baseStationFlight, r => r.FirstSquawk, null, 59);
TestUtilities.TestProperty(baseStationFlight, r => r.FirstTrack, null, 95457.423f);
TestUtilities.TestProperty(baseStationFlight, r => r.FirstVerticalRate, null, 123);
TestUtilities.TestProperty(baseStationFlight, r => r.FlightID, 0, 1543);
TestUtilities.TestProperty(baseStationFlight, r => r.HadAlert, false);
TestUtilities.TestProperty(baseStationFlight, r => r.HadEmergency, false);
TestUtilities.TestProperty(baseStationFlight, r => r.HadSpi, false);
TestUtilities.TestProperty(baseStationFlight, r => r.LastAltitude, null, 1212);
TestUtilities.TestProperty(baseStationFlight, r => r.LastGroundSpeed, null, 123.132f);
TestUtilities.TestProperty(baseStationFlight, r => r.LastIsOnGround, false);
TestUtilities.TestProperty(baseStationFlight, r => r.LastLat, null, 1239.85);
TestUtilities.TestProperty(baseStationFlight, r => r.LastLon, null, 93.556);
TestUtilities.TestProperty(baseStationFlight, r => r.LastSquawk, null, 59);
TestUtilities.TestProperty(baseStationFlight, r => r.LastTrack, null, 95457.423f);
TestUtilities.TestProperty(baseStationFlight, r => r.LastVerticalRate, null, 123);
TestUtilities.TestProperty(baseStationFlight, r => r.NumADSBMsgRec, null, int.MaxValue);
TestUtilities.TestProperty(baseStationFlight, r => r.NumModeSMsgRec, null, int.MaxValue);
TestUtilities.TestProperty(baseStationFlight, r => r.NumIDMsgRec, null, int.MaxValue);
TestUtilities.TestProperty(baseStationFlight, r => r.NumSurPosMsgRec, null, int.MaxValue);
TestUtilities.TestProperty(baseStationFlight, r => r.NumAirPosMsgRec, null, int.MaxValue);
TestUtilities.TestProperty(baseStationFlight, r => r.NumAirVelMsgRec, null, int.MaxValue);
TestUtilities.TestProperty(baseStationFlight, r => r.NumSurAltMsgRec, null, int.MaxValue);
TestUtilities.TestProperty(baseStationFlight, r => r.NumSurIDMsgRec, null, int.MaxValue);
TestUtilities.TestProperty(baseStationFlight, r => r.NumAirToAirMsgRec, null, int.MaxValue);
TestUtilities.TestProperty(baseStationFlight, r => r.NumAirCallRepMsgRec, null, int.MaxValue);
TestUtilities.TestProperty(baseStationFlight, r => r.NumPosMsgRec, null, int.MaxValue);
TestUtilities.TestProperty(baseStationFlight, r => r.SessionID, 0, int.MaxValue);
TestUtilities.TestProperty(baseStationFlight, r => r.StartTime, DateTime.MinValue, DateTime.Now);
}
/// <summary>
/// Copies the object's values to a flight object.
/// </summary>
/// <param name="flight"></param>
public void ApplyTo(BaseStationFlight flight)
{
flight.Aircraft = Aircraft;
flight.AircraftID = AircraftID;
flight.Callsign = Callsign;
flight.EndTime = EndTime;
flight.FirstAltitude = FirstAltitude;
flight.FirstGroundSpeed = FirstGroundSpeed;
flight.FirstIsOnGround = FirstIsOnGround;
flight.FirstLat = FirstLat;
flight.FirstLon = FirstLon;
flight.FirstSquawk = FirstSquawk;
flight.FirstTrack = FirstTrack;
flight.FirstVerticalRate = FirstVerticalRate;
flight.HadAlert = HadAlert;
flight.HadEmergency = HadEmergency;
flight.HadSpi = HadSpi;
flight.LastAltitude = LastAltitude;
flight.LastGroundSpeed = LastGroundSpeed;
flight.LastIsOnGround = LastIsOnGround;
flight.LastLat = LastLat;
flight.LastLon = LastLon;
flight.LastSquawk = LastSquawk;
flight.LastTrack = LastTrack;
flight.LastVerticalRate = LastVerticalRate;
flight.NumADSBMsgRec = NumADSBMsgRec;
flight.NumModeSMsgRec = NumModeSMsgRec;
flight.NumIDMsgRec = NumIDMsgRec;
flight.NumSurPosMsgRec = NumSurPosMsgRec;
flight.NumAirPosMsgRec = NumAirPosMsgRec;
flight.NumAirVelMsgRec = NumAirVelMsgRec;
flight.NumSurAltMsgRec = NumSurAltMsgRec;
flight.NumSurIDMsgRec = NumSurIDMsgRec;
flight.NumAirToAirMsgRec = NumAirToAirMsgRec;
flight.NumAirCallRepMsgRec = NumAirCallRepMsgRec;
flight.NumPosMsgRec = NumPosMsgRec;
flight.SessionID = SessionID;
flight.StartTime = StartTime;
flight.UserNotes = UserNotes;
}
/// <summary>
/// Creates a new object.
/// </summary>
/// <param name="flight"></param>
public BaseStationFlightUpsert(BaseStationFlight flight)
{
Aircraft = flight.Aircraft;
AircraftID = flight.AircraftID;
Callsign = flight.Callsign;
EndTime = flight.EndTime;
FirstAltitude = flight.FirstAltitude;
FirstGroundSpeed = flight.FirstGroundSpeed;
FirstIsOnGround = flight.FirstIsOnGround;
FirstLat = flight.FirstLat;
FirstLon = flight.FirstLon;
FirstSquawk = flight.FirstSquawk;
FirstTrack = flight.FirstTrack;
FirstVerticalRate = flight.FirstVerticalRate;
HadAlert = flight.HadAlert;
HadEmergency = flight.HadEmergency;
HadSpi = flight.HadSpi;
LastAltitude = flight.LastAltitude;
LastGroundSpeed = flight.LastGroundSpeed;
LastIsOnGround = flight.LastIsOnGround;
LastLat = flight.LastLat;
LastLon = flight.LastLon;
LastSquawk = flight.LastSquawk;
LastTrack = flight.LastTrack;
LastVerticalRate = flight.LastVerticalRate;
NumADSBMsgRec = flight.NumADSBMsgRec;
NumModeSMsgRec = flight.NumModeSMsgRec;
NumIDMsgRec = flight.NumIDMsgRec;
NumSurPosMsgRec = flight.NumSurPosMsgRec;
NumAirPosMsgRec = flight.NumAirPosMsgRec;
NumAirVelMsgRec = flight.NumAirVelMsgRec;
NumSurAltMsgRec = flight.NumSurAltMsgRec;
NumSurIDMsgRec = flight.NumSurIDMsgRec;
NumAirToAirMsgRec = flight.NumAirToAirMsgRec;
NumAirCallRepMsgRec = flight.NumAirCallRepMsgRec;
NumPosMsgRec = flight.NumPosMsgRec;
SessionID = flight.SessionID;
StartTime = flight.StartTime;
UserNotes = flight.UserNotes;
}
/// <summary>
/// Returns a mock flight record with values filled from a spreadsheet row, starting from the column number passed across.
/// </summary>
/// <param name="expected"></param>
/// <param name="firstOrdinal"></param>
/// <returns></returns>
/// <remarks>
/// The columns are read in the following order:
/// AircraftID, Callsign, EndTime, FirstAltitude, FirstGroundSpeed, FirstIsOnGround, FirstLat, FirstLon, FirstSquawk, FirstTrack, FirstVerticalRate, HadAlert,
/// HadEmergency, HadSpi, LastAltitude, LastGroundSpeed, LastIsOnGround, LastLat, LastLon, LastSquawk, LastTrack, LastVerticalRate, NumADSBMsgRec, NumModeSMsgRec,
/// NumIDMsgRec, NumSurPosMsgRec, NumAirPosMsgRec, NumAirVelMsgRec, NumSurAltMsgRec, NumSurIDMsgRec, NumAirToAirMsgRec, NumAirCallRepMsgRec, NumPosMsgRec
/// and StartTime
/// </remarks>
private BaseStationFlight LoadFlightFromSpreadsheet(ExcelWorksheetData worksheet, int firstOrdinal = 0, BaseStationFlight copyIntoFlight = null)
{
int ordinal = firstOrdinal;
var aircraft = CreateAircraft();
aircraft.AircraftID = worksheet.Int(ordinal++);
var result = copyIntoFlight == null ? CreateFlight(aircraft) : copyIntoFlight;
result.AircraftID = aircraft.AircraftID;
result.Callsign = worksheet.EString(ordinal++);
result.EndTime = worksheet.DateTime(ordinal++);
result.FirstAltitude = worksheet.Int(ordinal++);
result.FirstGroundSpeed = worksheet.Float(ordinal++);
result.FirstIsOnGround = worksheet.Bool(ordinal++);
result.FirstLat = worksheet.Float(ordinal++);
result.FirstLon = worksheet.Float(ordinal++);
result.FirstSquawk = worksheet.Int(ordinal++);
result.FirstTrack = worksheet.Float(ordinal++);
result.FirstVerticalRate = worksheet.Int(ordinal++);
result.HadAlert = worksheet.Bool(ordinal++);
result.HadEmergency = worksheet.Bool(ordinal++);
result.HadSpi = worksheet.Bool(ordinal++);
result.LastAltitude = worksheet.Int(ordinal++);
result.LastGroundSpeed = worksheet.Float(ordinal++);
result.LastIsOnGround = worksheet.Bool(ordinal++);
result.LastLat = worksheet.Float(ordinal++);
result.LastLon = worksheet.Float(ordinal++);
result.LastSquawk = worksheet.Int(ordinal++);
result.LastTrack = worksheet.Float(ordinal++);
result.LastVerticalRate = worksheet.Int(ordinal++);
result.NumADSBMsgRec = worksheet.Int(ordinal++);
result.NumModeSMsgRec = worksheet.Int(ordinal++);
result.NumIDMsgRec = worksheet.Int(ordinal++);
result.NumSurPosMsgRec = worksheet.Int(ordinal++);
result.NumAirPosMsgRec = worksheet.Int(ordinal++);
result.NumAirVelMsgRec = worksheet.Int(ordinal++);
result.NumSurAltMsgRec = worksheet.Int(ordinal++);
result.NumSurIDMsgRec = worksheet.Int(ordinal++);
result.NumAirToAirMsgRec = worksheet.Int(ordinal++);
result.NumAirCallRepMsgRec = worksheet.Int(ordinal++);
result.NumPosMsgRec = worksheet.Int(ordinal++);
result.StartTime = worksheet.DateTime(ordinal++);
return result;
}
/// <summary>
/// Creates a flight object.
/// </summary>
/// <param name="actual"></param>
/// <returns></returns>
private BaseStationFlight CreateFlight(BaseStationAircraft aircraft = null, string id = null)
{
var result = new BaseStationFlight() { Aircraft = new BaseStationAircraft() };
if(id != null) result.Callsign = id;
if(aircraft != null) result.Aircraft = aircraft;
if(result.Aircraft != null) result.AircraftID = result.Aircraft.AircraftID;
return result;
}
public void BaseStationDatabase_DeleteFlight_Throws_If_Writes_Disabled()
{
var flight = new BaseStationFlight() {
AircraftID = (int)AddAircraft(new BaseStationAircraft() { ModeS = "Z" }),
SessionID = (int)AddSession(new BaseStationSession() { StartTime = DateTime.Now }),
};
flight.FlightID = (int)AddFlight(flight);
_Database.DeleteFlight(flight);
}
/// <summary>
/// Inserts a new record and returns its ID.
/// </summary>
/// <param name="connection"></param>
/// <param name="transaction"></param>
/// <param name="log"></param>
/// <param name="flight"></param>
/// <returns></returns>
public int Insert(IDbConnection connection, IDbTransaction transaction, TextWriter log, BaseStationFlight flight)
{
var preparedCommand = PrepareInsert(connection, transaction, "Insert", "FlightID",
"AircraftID",
"Callsign",
"EndTime",
"FirstAltitude",
"FirstGroundSpeed",
"FirstIsOnGround",
"FirstLat",
"FirstLon",
"FirstSquawk",
"FirstTrack",
"FirstVerticalRate",
"HadAlert",
"HadEmergency",
"HadSpi",
"LastAltitude",
"LastGroundSpeed",
"LastIsOnGround",
"LastLat",
"LastLon",
"LastSquawk",
"LastTrack",
"LastVerticalRate",
"NumADSBMsgRec",
"NumModeSMsgRec",
"NumIDMsgRec",
"NumSurPosMsgRec",
"NumAirPosMsgRec",
"NumAirVelMsgRec",
"NumSurAltMsgRec",
"NumSurIDMsgRec",
"NumAirToAirMsgRec",
"NumAirCallRepMsgRec",
"NumPosMsgRec",
"SessionID",
"StartTime");
return (int)Sql.ExecuteInsert(preparedCommand, log,
flight.AircraftID,
flight.Callsign,
flight.EndTime,
flight.FirstAltitude,
flight.FirstGroundSpeed,
flight.FirstIsOnGround,
flight.FirstLat,
flight.FirstLon,
flight.FirstSquawk,
flight.FirstTrack,
flight.FirstVerticalRate,
flight.HadAlert,
flight.HadEmergency,
flight.HadSpi,
flight.LastAltitude,
flight.LastGroundSpeed,
flight.LastIsOnGround,
flight.LastLat,
flight.LastLon,
flight.LastSquawk,
flight.LastTrack,
flight.LastVerticalRate,
flight.NumADSBMsgRec,
flight.NumModeSMsgRec,
flight.NumIDMsgRec,
flight.NumSurPosMsgRec,
flight.NumAirPosMsgRec,
flight.NumAirVelMsgRec,
flight.NumSurAltMsgRec,
flight.NumSurIDMsgRec,
flight.NumAirToAirMsgRec,
flight.NumAirCallRepMsgRec,
flight.NumPosMsgRec,
flight.SessionID,
flight.StartTime);
}
public void UpdateFlight(BaseStationFlight flight)
{
;
}
private void AddFlightAndAircraft(BaseStationFlight flight)
{
flight.AircraftID = (int)AddAircraft(flight.Aircraft);
AddFlight(flight);
}
/// <summary>
/// Deletes the record passed across.
/// </summary>
/// <param name="connection"></param>
/// <param name="transaction"></param>
/// <param name="log"></param>
/// <param name="flight"></param>
public void Delete(IDbConnection connection, IDbTransaction transaction, TextWriter log, BaseStationFlight flight)
{
var preparedCommand = PrepareCommand(connection, transaction, "Delete", _DeleteCommandText, 1);
Sql.SetParameters(preparedCommand, flight.FlightID);
Sql.LogCommand(log, preparedCommand.Command);
preparedCommand.Command.ExecuteNonQuery();
}
/// <summary>
/// Sets up the flights passed across for an equality criteria test.
/// </summary>
/// <param name="criteriaProperty"></param>
/// <param name="defaultFlight"></param>
/// <param name="notEqualFlight"></param>
/// <param name="equalsFlight"></param>
/// <returns>Returns true if the criteria property is an equality criteria.</returns>
private bool SetEqualityCriteria(PropertyInfo criteriaProperty, BaseStationFlight defaultFlight, BaseStationFlight notEqualFlight, BaseStationFlight equalsFlight)
{
bool result = true;
string flightPropertyName = criteriaProperty.Name;
string aircraftPropertyName = null;
object criteriaValue = null;
object defaultValue = null;
object notEqualValue = null;
object equalValue = null;
switch(criteriaProperty.Name) {
case "Callsign":
criteriaValue = "A";
defaultValue = aircraftPropertyName == "ModeS" ? "" : null;
notEqualValue = "AA";
equalValue = "A";
break;
case "Registration":
case "Icao":
case "Operator":
case "Country":
flightPropertyName = null;
switch(criteriaProperty.Name) {
case "Icao": aircraftPropertyName = "ModeS"; break;
case "Operator": aircraftPropertyName = "RegisteredOwners"; break;
case "Country": aircraftPropertyName = "ModeSCountry"; break;
default: aircraftPropertyName = criteriaProperty.Name; break;
}
goto case "Callsign";
case "FromDate":
case "ToDate":
result = false;
break;
case "IsEmergency":
criteriaValue = true;
defaultValue = false;
notEqualValue = false;
equalValue = true;
flightPropertyName = "HadEmergency";
break;
default:
throw new NotImplementedException();
}
if(result) {
criteriaProperty.SetValue(_Criteria, criteriaValue, null);
if(aircraftPropertyName != null) {
var aircraftProperty = typeof(BaseStationAircraft).GetProperty(aircraftPropertyName);
aircraftProperty.SetValue(defaultFlight.Aircraft, defaultValue, null);
aircraftProperty.SetValue(notEqualFlight.Aircraft, notEqualValue, null);
aircraftProperty.SetValue(equalsFlight.Aircraft, equalValue, null);
} else {
var flightProperty = typeof(BaseStationFlight).GetProperty(flightPropertyName);
flightProperty.SetValue(defaultFlight, defaultValue, null);
flightProperty.SetValue(notEqualFlight, notEqualValue, null);
flightProperty.SetValue(equalsFlight, equalValue, null);
}
}
return result;
}
/// <summary>
/// Creates, inserts and returns a new flight record for the aircraft and callsign passed across.
/// </summary>
/// <param name="localNow"></param>
/// <param name="aircraftId"></param>
/// <param name="callsign"></param>
/// <returns></returns>
private BaseStationFlight CreateFlight(DateTime localNow, int aircraftId, string callsign)
{
var result = new BaseStationFlight() {
FlightID = 0,
AircraftID = aircraftId,
SessionID = _Session.SessionID,
Callsign = String.IsNullOrEmpty(callsign) ? "" : callsign,
StartTime = localNow,
};
_Database.InsertFlight(result);
result.NumADSBMsgRec = 0;
result.NumAirCallRepMsgRec = 0;
result.NumAirPosMsgRec = 0;
result.NumAirToAirMsgRec = 0;
result.NumAirVelMsgRec = 0;
result.NumIDMsgRec = 0;
result.NumModeSMsgRec = 0;
result.NumPosMsgRec = 0;
result.NumSurAltMsgRec = 0;
result.NumSurIDMsgRec = 0;
result.NumSurPosMsgRec = 0;
return result;
}
/// <summary>
/// Updates the counters of total messages for a flight.
/// </summary>
/// <param name="message"></param>
/// <param name="flight"></param>
private static void UpdateMessageCounters(BaseStationMessage message, BaseStationFlight flight)
{
switch(message.TransmissionType) {
case BaseStationTransmissionType.IdentificationAndCategory: ++flight.NumIDMsgRec; break;
case BaseStationTransmissionType.SurfacePosition: ++flight.NumSurPosMsgRec; break;
case BaseStationTransmissionType.AirbornePosition: ++flight.NumAirPosMsgRec; break;
case BaseStationTransmissionType.AirborneVelocity: ++flight.NumAirVelMsgRec; break;
case BaseStationTransmissionType.SurveillanceAlt: ++flight.NumSurAltMsgRec; break;
case BaseStationTransmissionType.SurveillanceId: ++flight.NumSurIDMsgRec; break;
case BaseStationTransmissionType.AirToAir: ++flight.NumAirToAirMsgRec; break;
case BaseStationTransmissionType.AllCallReply: ++flight.NumAirCallRepMsgRec; break;
}
if(message.Latitude == null && message.Longitude == null && message.GroundSpeed == null && message.Track == null && message.VerticalRate == null) {
++flight.NumModeSMsgRec;
} else {
++flight.NumADSBMsgRec;
if(message.Latitude != null && message.Longitude != null) ++flight.NumPosMsgRec;
}
}
/// <summary>
/// Updates the FirstX / LastX pairs of values of an in-store flight record.
/// </summary>
/// <param name="message"></param>
/// <param name="flight"></param>
/// <param name="flightRecords"></param>
private static void UpdateFirstLastValues(BaseStationMessage message, BaseStationFlight flight, FlightRecords flightRecords)
{
bool isLocationZeroZero = message.Latitude.GetValueOrDefault() == 0F && message.Longitude.GetValueOrDefault() == 0F;
if(message.Altitude != null) {
if(flight.FirstAltitude == null) flight.FirstAltitude = message.Altitude;
flight.LastAltitude = message.Altitude;
}
if(message.GroundSpeed != null) {
if(flight.FirstGroundSpeed == null) flight.FirstGroundSpeed = message.GroundSpeed;
flight.LastGroundSpeed = message.GroundSpeed;
}
if(message.OnGround != null) {
if(flightRecords.OnGround == null) flightRecords.OnGround = flight.FirstIsOnGround = message.OnGround.Value;
flight.LastIsOnGround = message.OnGround.Value;
}
if(message.Latitude != null && !isLocationZeroZero) {
if(flight.FirstLat == null) flight.FirstLat = message.Latitude;
flight.LastLat = message.Latitude;
}
if(message.Longitude != null && !isLocationZeroZero) {
if(flight.FirstLon == null) flight.FirstLon = message.Longitude;
flight.LastLon = message.Longitude;
}
if(message.Squawk != null) {
if(flight.FirstSquawk == null) flight.FirstSquawk = message.Squawk;
flight.LastSquawk = message.Squawk;
}
if(message.Track != null) {
if(flight.FirstTrack == null) flight.FirstTrack = message.Track;
flight.LastTrack = message.Track;
}
if(message.VerticalRate != null) {
if(flight.FirstVerticalRate == null) flight.FirstVerticalRate = message.VerticalRate;
flight.LastVerticalRate = message.VerticalRate;
}
}
/// <summary>
/// Creates a JSON representation of the database flight and adds it to an existing list of flights.
/// </summary>
/// <param name="flight"></param>
/// <param name="flightList"></param>
/// <param name="rowNumber"></param>
/// <returns></returns>
private ReportFlightJson AddReportFlightJson(BaseStationFlight flight, List<ReportFlightJson> flightList, ref int rowNumber)
{
var result = new ReportFlightJson() {
RowNumber = rowNumber++,
Callsign = flight.Callsign,
StartTime = flight.StartTime,
EndTime = flight.EndTime.GetValueOrDefault(),
FirstAltitude = flight.FirstAltitude.GetValueOrDefault(),
FirstGroundSpeed = (int)flight.FirstGroundSpeed.GetValueOrDefault(),
FirstIsOnGround = flight.FirstIsOnGround,
FirstLatitude = flight.FirstLat.GetValueOrDefault(),
FirstLongitude = flight.FirstLon.GetValueOrDefault(),
FirstSquawk = flight.FirstSquawk.GetValueOrDefault(),
FirstTrack = flight.FirstTrack.GetValueOrDefault(),
FirstVerticalRate = flight.FirstVerticalRate.GetValueOrDefault(),
HadAlert = flight.HadAlert,
HadEmergency = flight.HadEmergency,
HadSpi = flight.HadSpi,
LastAltitude = flight.LastAltitude.GetValueOrDefault(),
LastGroundSpeed = (int)flight.LastGroundSpeed.GetValueOrDefault(),
LastIsOnGround = flight.LastIsOnGround,
LastLatitude = flight.LastLat.GetValueOrDefault(),
LastLongitude = flight.LastLon.GetValueOrDefault(),
LastSquawk = flight.LastSquawk.GetValueOrDefault(),
LastTrack = flight.LastTrack.GetValueOrDefault(),
LastVerticalRate = flight.LastVerticalRate.GetValueOrDefault(),
NumADSBMsgRec = flight.NumADSBMsgRec.GetValueOrDefault(),
NumModeSMsgRec = flight.NumModeSMsgRec.GetValueOrDefault(),
NumPosMsgRec = flight.NumPosMsgRec.GetValueOrDefault(),
};
flightList.Add(result);
return result;
}
public void BaseStationDatabase_CreateDatabaseIfMissing_Creates_Aircraft_Trigger_To_Delete_Flights()
{
_Database.CreateDatabaseIfMissing(_CreateDatabaseFileName);
_Database.FileName = _CreateDatabaseFileName;
_Database.WriteSupportEnabled = true;
var session = new BaseStationSession() { StartTime = DateTime.Now };
_Database.InsertSession(session);
var aircraft = new BaseStationAircraft() { ModeS = "K" };
_Database.InsertAircraft(aircraft);
var flight = new BaseStationFlight() { AircraftID = aircraft.AircraftID, SessionID = session.SessionID };
_Database.InsertFlight(flight);
_Database.DeleteAircraft(aircraft);
Assert.IsNull(_Database.GetFlightById(flight.FlightID));
Assert.AreEqual(session.SessionID, _Database.GetSessions()[0].SessionID);
}
private void SetSortColumnValue(BaseStationFlight flight, string sortColumn, bool isDefault, bool isHigh)
{
var stringValue = isDefault ? sortColumn == "reg" || sortColumn == "icao" ? "" : null : isHigh ? "B" : "A";
var dateValue = isDefault ? default(DateTime) : isHigh ? new DateTime(2001, 1, 2) : new DateTime(2001, 1, 1);
switch(sortColumn) {
case "callsign": flight.Callsign = stringValue; break;
case "country": flight.Aircraft.ModeSCountry = stringValue; break;
case "date": flight.StartTime = dateValue; break;
case "model": flight.Aircraft.Type = stringValue; break;
case "type": flight.Aircraft.ICAOTypeCode = stringValue; break;
case "operator": flight.Aircraft.RegisteredOwners = stringValue; break;
case "reg": flight.Aircraft.Registration = stringValue; break;
case "icao": flight.Aircraft.ModeS = stringValue; break;
default: throw new NotImplementedException();
}
}
private long AddFlight(BaseStationFlight flight)
{
long result = 0;
using(var connection = new SQLiteConnection(_ConnectionStringBuilder.ConnectionString)) {
connection.Open();
using(var command = connection.CreateCommand()) {
var fieldNames = new StringBuilder();
var parameters = new StringBuilder();
foreach(var property in typeof(BaseStationFlight).GetProperties()) {
var fieldName = property.Name;
object value = property.GetValue(flight, null);
if(fieldName == "FlightID") continue;
else if(fieldName == "Aircraft") continue;
if(fieldNames.Length > 0) fieldNames.Append(',');
if(parameters.Length > 0) parameters.Append(',');
fieldNames.AppendFormat("[{0}]", fieldName);
parameters.Append('?');
var parameter = command.CreateParameter();
parameter.Value = value;
command.Parameters.Add(parameter);
}
command.CommandText = String.Format("INSERT INTO [Flights] ({0}) VALUES ({1}); SELECT last_insert_rowid();", fieldNames, parameters);
result = (long)command.ExecuteScalar();
flight.FlightID = (int)result;
}
}
return result;
}
/// <summary>
/// Writes the flight record back to the database.
/// </summary>
/// <param name="connection"></param>
/// <param name="transaction"></param>
/// <param name="log"></param>
/// <param name="flight"></param>
public void Update(IDbConnection connection, IDbTransaction transaction, TextWriter log, BaseStationFlight flight)
{
var preparedCommand = PrepareCommand(connection, transaction, "Update", _UpdateCommandText, 36);
Sql.SetParameters(preparedCommand,
flight.AircraftID,
flight.Callsign,
flight.EndTime,
flight.FirstAltitude,
flight.FirstGroundSpeed,
flight.FirstIsOnGround,
flight.FirstLat,
flight.FirstLon,
flight.FirstSquawk,
flight.FirstTrack,
flight.FirstVerticalRate,
flight.HadAlert,
flight.HadEmergency,
flight.HadSpi,
flight.LastAltitude,
flight.LastGroundSpeed,
flight.LastIsOnGround,
flight.LastLat,
flight.LastLon,
flight.LastSquawk,
flight.LastTrack,
flight.LastVerticalRate,
flight.NumADSBMsgRec,
flight.NumModeSMsgRec,
flight.NumIDMsgRec,
flight.NumSurPosMsgRec,
flight.NumAirPosMsgRec,
flight.NumAirVelMsgRec,
flight.NumSurAltMsgRec,
flight.NumSurIDMsgRec,
flight.NumAirToAirMsgRec,
flight.NumAirCallRepMsgRec,
flight.NumPosMsgRec,
flight.SessionID,
flight.StartTime,
flight.FlightID);
Sql.LogCommand(log, preparedCommand.Command);
preparedCommand.Command.ExecuteNonQuery();
}
/// <summary>
/// Sets up the flights for a range criteria test.
/// </summary>
/// <param name="criteriaProperty"></param>
/// <param name="belowRangeFlight"></param>
/// <param name="startRangeFlight"></param>
/// <param name="inRangeFlight"></param>
/// <param name="endRangeFlight"></param>
/// <param name="aboveRangeFlight"></param>
/// <returns></returns>
private bool SetRangeCriteria(PropertyInfo criteriaProperty, BaseStationFlight belowRangeFlight, BaseStationFlight startRangeFlight, BaseStationFlight inRangeFlight, BaseStationFlight endRangeFlight, BaseStationFlight aboveRangeFlight)
{
bool result = true;
switch(criteriaProperty.Name) {
case "FromDate":
var startTime = new DateTime(2001, 2, 3, 4, 5, 6);
belowRangeFlight.StartTime = startTime.AddSeconds(-1);
_Criteria.FromDate = startRangeFlight.StartTime = startTime;
inRangeFlight.StartTime = startTime.AddSeconds(1);
_Criteria.ToDate = endRangeFlight.StartTime = startTime.AddSeconds(2);
aboveRangeFlight.StartTime = startTime.AddSeconds(3);
break;
case "Callsign":
case "ToDate":
case "IsEmergency":
case "Operator":
case "Registration":
case "Icao":
case "Country":
result = false;
break;
default:
throw new NotImplementedException();
}
return result;
}
/// <summary>
/// See interface docs.
/// </summary>
/// <param name="flight"></param>
public void UpdateFlight(BaseStationFlight flight)
{
if(!WriteSupportEnabled) throw new InvalidOperationException("You cannot update flights when writes are disabled");
flight.StartTime = SQLiteDateHelper.Truncate(flight.StartTime);
flight.EndTime = SQLiteDateHelper.Truncate(flight.EndTime);
lock(_ConnectionLock) {
OpenConnection();
if(_Connection != null) _FlightTable.Update(_Connection, _TransactionHelper.Transaction, _DatabaseLog, flight);
}
}
private static void AssertFlightsAreEqual(BaseStationFlight expected, BaseStationFlight actual, bool expectAircraftFilled, int expectedAircraftId)
{
Assert.AreEqual(expectedAircraftId, actual.AircraftID);
if(expectAircraftFilled) Assert.AreEqual(expectedAircraftId, actual.Aircraft.AircraftID);
else Assert.IsNull(actual.Aircraft);
Assert.AreEqual(expected.Callsign, actual.Callsign);
Assert.AreEqual(expected.EndTime, actual.EndTime);
Assert.AreEqual(expected.FirstAltitude, actual.FirstAltitude);
Assert.AreEqual(expected.FirstGroundSpeed, actual.FirstGroundSpeed);
Assert.AreEqual(expected.FirstIsOnGround, actual.FirstIsOnGround);
Assert.AreEqual(expected.FirstLat, actual.FirstLat);
Assert.AreEqual(expected.FirstLon, actual.FirstLon);
Assert.AreEqual(expected.FirstSquawk, actual.FirstSquawk);
Assert.AreEqual(expected.FirstTrack, actual.FirstTrack);
Assert.AreEqual(expected.FirstVerticalRate, actual.FirstVerticalRate);
Assert.AreEqual(expected.HadAlert, actual.HadAlert);
Assert.AreEqual(expected.HadEmergency, actual.HadEmergency);
Assert.AreEqual(expected.HadSpi, actual.HadSpi);
Assert.AreEqual(expected.LastAltitude, actual.LastAltitude);
Assert.AreEqual(expected.LastGroundSpeed, actual.LastGroundSpeed);
Assert.AreEqual(expected.LastIsOnGround, actual.LastIsOnGround);
Assert.AreEqual(expected.LastLat, actual.LastLat);
Assert.AreEqual(expected.LastLon, actual.LastLon);
Assert.AreEqual(expected.LastSquawk, actual.LastSquawk);
Assert.AreEqual(expected.LastTrack, actual.LastTrack);
Assert.AreEqual(expected.LastVerticalRate, actual.LastVerticalRate);
Assert.AreEqual(expected.NumADSBMsgRec, actual.NumADSBMsgRec);
Assert.AreEqual(expected.NumModeSMsgRec, actual.NumModeSMsgRec);
Assert.AreEqual(expected.NumIDMsgRec, actual.NumIDMsgRec);
Assert.AreEqual(expected.NumSurPosMsgRec, actual.NumSurPosMsgRec);
Assert.AreEqual(expected.NumAirPosMsgRec, actual.NumAirPosMsgRec);
Assert.AreEqual(expected.NumAirVelMsgRec, actual.NumAirVelMsgRec);
Assert.AreEqual(expected.NumSurAltMsgRec, actual.NumSurAltMsgRec);
Assert.AreEqual(expected.NumSurIDMsgRec, actual.NumSurIDMsgRec);
Assert.AreEqual(expected.NumAirToAirMsgRec, actual.NumAirToAirMsgRec);
Assert.AreEqual(expected.NumAirCallRepMsgRec, actual.NumAirCallRepMsgRec);
Assert.AreEqual(expected.NumPosMsgRec, actual.NumPosMsgRec);
Assert.AreEqual(expected.StartTime, actual.StartTime);
Assert.AreEqual(expected.SessionID, actual.SessionID);
}
/// <summary>
/// See interface docs.
/// </summary>
/// <param name="flight"></param>
public void DeleteFlight(BaseStationFlight flight)
{
if(!WriteSupportEnabled) throw new InvalidOperationException("You cannot delete flights when writes are disabled");
lock(_ConnectionLock) {
OpenConnection();
if(_Connection != null) _FlightTable.Delete(_Connection, _TransactionHelper.Transaction, _DatabaseLog, flight);
}
}
public void InsertFlight(BaseStationFlight flight)
{
;
}
public void BaseStationDatabase_UpdateFlight_Truncates_Milliseconds_From_Date()
{
_Database.WriteSupportEnabled = true;
var aircraftId = (int)AddAircraft(new BaseStationAircraft() { ModeS = "Y" });
var sessionId = (int)AddSession(new BaseStationSession() { StartTime = DateTime.Now });
var flight = new BaseStationFlight() { AircraftID = aircraftId, SessionID = sessionId };
_Database.InsertFlight(flight);
var time1 = new DateTime(2001, 2, 3, 4, 5, 6, 789);
var time2 = new DateTime(2009, 8, 7, 6, 5, 4, 321);
var update = _Database.GetFlightById(flight.FlightID);
update.StartTime = time1;
update.EndTime = time2;
_Database.UpdateFlight(update);
var readBack = _Database.GetFlightById(flight.FlightID);
Assert.AreEqual(TruncateDate(time1), readBack.StartTime);
Assert.AreEqual(TruncateDate(time2), readBack.EndTime);
}
public void DeleteFlight(BaseStationFlight flight)
{
;
}
/// <summary>
/// Creates an object describing a single field and copies content of the IDataReader into it.
/// </summary>
/// <param name="reader"></param>
/// <param name="ordinal"></param>
/// <returns></returns>
public static BaseStationFlight DecodeFullFlight(IDataReader reader, ref int ordinal)
{
BaseStationFlight result = new BaseStationFlight();
result.AircraftID = Sql.GetInt32(reader, ordinal++);
result.SessionID = Sql.GetInt32(reader, ordinal++);
result.Callsign = Sql.GetString(reader, ordinal++);
result.EndTime = Sql.GetDateTime(reader, ordinal++);
result.FirstAltitude = Sql.GetInt32(reader, ordinal++);
result.FirstGroundSpeed = Sql.GetFloat(reader, ordinal++);
result.FirstIsOnGround = Sql.GetBool(reader, ordinal++);
result.FirstLat = Sql.GetFloat(reader, ordinal++);
result.FirstLon = Sql.GetFloat(reader, ordinal++);
result.FirstSquawk = Sql.GetInt32(reader, ordinal++);
result.FirstTrack = Sql.GetFloat(reader, ordinal++);
result.FirstVerticalRate = Sql.GetInt32(reader, ordinal++);
result.FlightID = Sql.GetInt32(reader, ordinal++);
result.HadAlert = Sql.GetBool(reader, ordinal++);
result.HadEmergency = Sql.GetBool(reader, ordinal++);
result.HadSpi = Sql.GetBool(reader, ordinal++);
result.LastAltitude = Sql.GetInt32(reader, ordinal++);
result.LastGroundSpeed = Sql.GetFloat(reader, ordinal++);
result.LastIsOnGround = Sql.GetBool(reader, ordinal++);
result.LastLat = Sql.GetFloat(reader, ordinal++);
result.LastLon = Sql.GetFloat(reader, ordinal++);
result.LastSquawk = Sql.GetInt32(reader, ordinal++);
result.LastTrack = Sql.GetFloat(reader, ordinal++);
result.LastVerticalRate = Sql.GetInt32(reader, ordinal++);
result.NumADSBMsgRec = Sql.GetInt32(reader, ordinal++);
result.NumModeSMsgRec = Sql.GetInt32(reader, ordinal++);
result.NumIDMsgRec = Sql.GetInt32(reader, ordinal++);
result.NumSurPosMsgRec = Sql.GetInt32(reader, ordinal++);
result.NumAirPosMsgRec = Sql.GetInt32(reader, ordinal++);
result.NumAirVelMsgRec = Sql.GetInt32(reader, ordinal++);
result.NumSurAltMsgRec = Sql.GetInt32(reader, ordinal++);
result.NumSurIDMsgRec = Sql.GetInt32(reader, ordinal++);
result.NumAirToAirMsgRec = Sql.GetInt32(reader, ordinal++);
result.NumAirCallRepMsgRec = Sql.GetInt32(reader, ordinal++);
result.NumPosMsgRec = Sql.GetInt32(reader, ordinal++);
result.StartTime = Sql.GetDateTime(reader, ordinal++);
return result;
}
