private void LimitDatesWhenNoStrongCriteriaPresent(SearchBaseStationCriteria criteria, bool isInternetRequest)
{
if (String.IsNullOrEmpty(criteria.Callsign) && String.IsNullOrEmpty(criteria.Registration) && String.IsNullOrEmpty(criteria.Icao))
{
const int defaultDayCount = 7;
DateTime now = Provider.UtcNow;
bool fromIsMissing = criteria.FromDate.Year == DateTime.MinValue.Year;
bool toIsMissing = criteria.ToDate.Year == DateTime.MaxValue.Year;
if (fromIsMissing && toIsMissing)
{
criteria.ToDate = now.Date;
toIsMissing = false;
}
if (fromIsMissing)
{
criteria.FromDate = criteria.ToDate.AddDays(-defaultDayCount);
}
else if (toIsMissing)
{
criteria.ToDate = criteria.FromDate.AddDays(defaultDayCount);
}
else if (isInternetRequest && (criteria.ToDate - criteria.FromDate).TotalDays > defaultDayCount)
{
criteria.ToDate = criteria.FromDate.AddDays(defaultDayCount);
}
}
}
/// <summary>
/// Builds a select statement from criteria.
/// </summary>
/// <param name="aircraft"></param>
/// <param name="criteria"></param>
/// <param name="justCount"></param>
/// <returns></returns>
private string CreateSelectFrom(BaseStationAircraft aircraft, SearchBaseStationCriteria criteria, bool justCount)
{
StringBuilder result = new StringBuilder();
if (aircraft != null)
{
result.AppendFormat("SELECT {0} FROM [Flights] AS f", justCount ? "COUNT(*)" : FieldList());
}
else
{
result.AppendFormat("SELECT {0}{1}{2} FROM ",
justCount ? "COUNT(*)" : FieldList(),
justCount ? "" : ", ",
justCount ? "" : AircraftTable.FieldList());
if (FilterByAircraftFirst(criteria))
{
result.Append("[Aircraft] AS a LEFT JOIN [Flights] AS f");
}
else
{
result.Append("[Flights] AS f LEFT JOIN [Aircraft] AS a");
}
result.Append(" ON (a.[AircraftID] = f.[AircraftID])");
}
return(result.ToString());
}
/// <summary>
/// Imports flight records.
/// </summary>
/// <param name="source"></param>
/// <param name="dest"></param>
private void ProcessFlights(IBaseStationDatabaseSQLite source, IBaseStationDatabase dest)
{
if (!ImportFlights)
{
WriteLog("Flight import skipped");
}
else if (!ImportSessions || !ImportLocations)
{
WriteLog("Flight import skipped because session import was skipped");
}
else if (!ImportAircraft)
{
WriteLog("Flight import skipped because aircraft import was skipped");
}
else
{
WriteLog("Importing flight records");
var criteria = new SearchBaseStationCriteria();
var countFlights = source.GetCountOfFlights(new SearchBaseStationCriteria());
var countSource = 0;
var countDest = 0;
var startRow = 0;
var pageSize = 30000;
while (startRow < countFlights)
{
var allSource = source.GetFlights(criteria, startRow, startRow + (pageSize - 1), "DATE", true, null, false);
countSource += allSource.Count;
var upsertCandidates = new List <BaseStationFlightUpsert>();
var upsertKeys = new HashSet <string>();
foreach (var candidate in allSource)
{
var key = $"{candidate.AircraftID}-{candidate.StartTime}";
if (!upsertKeys.Contains(key) && _AircraftMap.TryGetValue(candidate.AircraftID, out var aircraftID) && _SessionMap.TryGetValue(candidate.SessionID, out var sessionID))
{
upsertCandidates.Add(new BaseStationFlightUpsert(candidate)
{
AircraftID = aircraftID,
SessionID = sessionID,
});
upsertKeys.Add(key);
}
}
var upserted = dest.UpsertManyFlights(upsertCandidates);
countDest += upserted.Length;
startRow += pageSize;
}
WriteLog($" Imported {countDest:N0} / {countSource:N0} flights");
}
}
public void SearchBaseStationCriteria_Constructor_Initialises_To_Known_State_And_Properties_Work()
{
var criteria = new SearchBaseStationCriteria();
TestUtilities.TestProperty(criteria, "Callsign", null, "Ab");
TestUtilities.TestProperty(criteria, "Country", null, "Hh");
TestUtilities.TestProperty(criteria, "FromDate", DateTime.MinValue, DateTime.Now);
TestUtilities.TestProperty(criteria, "Icao", null, "Gg");
TestUtilities.TestProperty(criteria, "IsEmergency", false);
TestUtilities.TestProperty(criteria, "Operator", null, "Jj");
TestUtilities.TestProperty(criteria, "Registration", null, "Ll");
TestUtilities.TestProperty(criteria, "ToDate", DateTime.MinValue, DateTime.Now);
}
/// <summary>
/// Normalises strings in the criteria object.
/// </summary>
/// <remarks>
/// Previous versions of VRS would implement case insensitivity in searches by using COLLATE NOCASE
/// statements in the SQL. This had the unfortunate side-effect of producing very slow searches, so
/// it has been removed. All searches are consequently case-sensitive but it is assumed that some
/// fields are always written in upper case. This method converts the criteria for those fields to
/// upper-case.
/// </remarks>
/// <param name="criteria"></param>
public static void NormaliseCriteria(SearchBaseStationCriteria criteria)
{
if (criteria.Callsign != null)
{
criteria.Callsign = criteria.Callsign.ToUpper(CultureInfo.InvariantCulture);
}
if (criteria.Icao != null)
{
criteria.Icao = criteria.Icao.ToUpper(CultureInfo.InvariantCulture);
}
if (criteria.Registration != null)
{
criteria.Registration = criteria.Registration.ToUpper(CultureInfo.InvariantCulture);
}
}
public void SearchBaseStationCriteria_Constructor_Initialises_To_Known_State_And_Properties_Work()
{
var criteria = new SearchBaseStationCriteria();
TestUtilities.TestProperty(criteria, r => r.Callsign, null, new FilterString());
TestUtilities.TestProperty(criteria, r => r.UseAlternateCallsigns, false);
TestUtilities.TestProperty(criteria, r => r.Country, null, new FilterString());
TestUtilities.TestProperty(criteria, r => r.Date, null, new FilterRange <DateTime>());
TestUtilities.TestProperty(criteria, r => r.Icao, null, new FilterString());
TestUtilities.TestProperty(criteria, r => r.IsEmergency, null, new FilterBool());
TestUtilities.TestProperty(criteria, r => r.Operator, null, new FilterString());
TestUtilities.TestProperty(criteria, r => r.Registration, null, new FilterString());
TestUtilities.TestProperty(criteria, r => r.Type, null, new FilterString());
TestUtilities.TestProperty(criteria, r => r.FirstAltitude, null, new FilterRange <int>());
TestUtilities.TestProperty(criteria, r => r.LastAltitude, null, new FilterRange <int>());
}
/// <summary>
/// Returns the WHERE portion of an SQL statement contains the fields describing the criteria passed across.
/// </summary>
/// <param name="aircraft"></param>
/// <param name="criteria"></param>
/// <returns></returns>
private string GetFlightsCriteria(BaseStationAircraft aircraft, SearchBaseStationCriteria criteria)
{
StringBuilder result = new StringBuilder();
if (aircraft != null)
{
AddCriteria(result, "f.[AircraftID]", " = ?");
}
if (!String.IsNullOrEmpty(criteria.Callsign))
{
AddCriteria(result, "f.[Callsign]", " = ?");
}
if (criteria.FromDate.Year != DateTime.MinValue.Year)
{
AddCriteria(result, "f.[StartTime]", " >= ?");
}
if (criteria.ToDate.Year != DateTime.MaxValue.Year)
{
AddCriteria(result, "f.[StartTime]", " <= ?");
}
if (!String.IsNullOrEmpty(criteria.Operator))
{
AddCriteria(result, "a.[RegisteredOwners]", " = ?");
}
if (!String.IsNullOrEmpty(criteria.Registration))
{
AddCriteria(result, "a.[Registration]", " = ?");
}
if (!String.IsNullOrEmpty(criteria.Icao))
{
AddCriteria(result, "a.[ModeS]", " = ?");
}
if (!String.IsNullOrEmpty(criteria.Country))
{
AddCriteria(result, "a.[ModeSCountry]", " = ?");
}
if (criteria.IsEmergency)
{
AddCriteria(result, "f.[HadEmergency]", " <> 0");
}
return(result.ToString());
}
/// <summary>
/// See interface docs.
/// </summary>
/// <param name="criteria"></param>
/// <returns></returns>
public int GetCountOfFlights(SearchBaseStationCriteria criteria)
{
if (criteria == null)
{
throw new ArgumentNullException("criteria");
}
FlightsTable.NormaliseCriteria(criteria);
int result = 0;
lock (_ConnectionLock) {
OpenConnection();
if (_Connection != null)
{
result = _FlightTable.GetCount(_Connection, null, _DatabaseLog, criteria);
}
}
return(result);
}
/// <summary>
/// See interface docs.
/// </summary>
/// <param name="criteria"></param>
/// <param name="fromRow"></param>
/// <param name="toRow"></param>
/// <param name="sortField1"></param>
/// <param name="sortField1Ascending"></param>
/// <param name="sortField2"></param>
/// <param name="sortField2Ascending"></param>
/// <returns></returns>
public List <BaseStationFlight> GetFlights(SearchBaseStationCriteria criteria, int fromRow, int toRow, string sortField1, bool sortField1Ascending, string sortField2, bool sortField2Ascending)
{
if (criteria == null)
{
throw new ArgumentNullException("criteria");
}
FlightsTable.NormaliseCriteria(criteria);
List <BaseStationFlight> result = new List <BaseStationFlight>();
lock (_ConnectionLock) {
OpenConnection();
if (_Connection != null)
{
result = _FlightTable.GetFlights(_Connection, null, _DatabaseLog, criteria, fromRow, toRow, sortField1, sortField1Ascending, sortField2, sortField2Ascending);
}
}
return(result);
}
private void LimitDatesWhenNoStrongCriteriaPresent(SearchBaseStationCriteria criteria, bool isInternetRequest)
{
if (criteria.Callsign == null && criteria.Registration == null && criteria.Icao == null)
{
if (criteria.Date == null)
{
criteria.Date = new FilterRange <DateTime>()
{
Condition = FilterCondition.Between
}
}
;
const int defaultDayCount = 7;
DateTime now = Provider.UtcNow;
bool fromIsMissing = criteria.Date.LowerValue == null;
bool toIsMissing = criteria.Date.UpperValue == null;
if (fromIsMissing && toIsMissing)
{
criteria.Date.UpperValue = now.Date;
toIsMissing = false;
}
if (fromIsMissing)
{
criteria.Date.LowerValue = criteria.Date.UpperValue.Value.AddDays(-defaultDayCount);
}
else if (toIsMissing)
{
criteria.Date.UpperValue = criteria.Date.LowerValue.Value.AddDays(defaultDayCount);
}
else if (isInternetRequest && (criteria.Date.UpperValue.Value - criteria.Date.LowerValue.Value).TotalDays > defaultDayCount)
{
criteria.Date.UpperValue = criteria.Date.LowerValue.Value.AddDays(defaultDayCount);
}
}
}
public int GetCountOfFlightsForAircraft(BaseStationAircraft aircraft, SearchBaseStationCriteria criteria)
{
return(0);
}
/// <summary>
/// Performs the work for the Get***Count methods.
/// </summary>
/// <param name="connection"></param>
/// <param name="transaction"></param>
/// <param name="log"></param>
/// <param name="aircraft"></param>
/// <param name="criteria"></param>
/// <returns></returns>
private int DoGetCount(IDbConnection connection, IDbTransaction transaction, TextWriter log, BaseStationAircraft aircraft, SearchBaseStationCriteria criteria)
{
int result = 0;
StringBuilder commandText = new StringBuilder();
commandText.Append(CreateSelectFrom(aircraft, criteria, true));
string criteriaText = GetFlightsCriteria(aircraft, criteria);
if (criteriaText.Length > 0)
{
commandText.AppendFormat(" WHERE {0}", criteriaText);
}
using (IDbCommand command = connection.CreateCommand()) {
command.Transaction = transaction;
command.CommandText = commandText.ToString();
AddFlightsCriteriaParameters(command, aircraft, criteria);
Sql.LogCommand(log, command);
result = (int)(long)command.ExecuteScalar();
}
return(result);
}
public int GetCountOfFlights(SearchBaseStationCriteria criteria)
{
return(0);
}
public List <BaseStationFlight> GetFlights(SearchBaseStationCriteria criteria, int fromRow, int toRow, string sortField1, bool sortField1Ascending, string sortField2, bool sortField2Ascending)
{
return(new List <BaseStationFlight>());
}
/// <summary>
/// Imports flight records.
/// </summary>
private void ProcessFlights()
{
if (ImportFlights)
{
OnTableChanged(new EventArgs <string>("Flights"));
var progress = new ProgressEventArgs()
{
Caption = "Importing flights",
TotalItems = -1,
};
OnProgressChanged(progress);
var criteria = new SearchBaseStationCriteria();
if (EarliestFlightCriteria != null || LatestFlightCriteria != null)
{
criteria.Date = new FilterRange <DateTime>()
{
Condition = FilterCondition.Between,
LowerValue = EarliestFlightCriteria,
UpperValue = LatestFlightCriteria,
};
}
var countFlights = Source.GetCountOfFlights(criteria);
var countSource = 0;
var countDest = 0;
var startRow = 0;
progress.TotalItems = countFlights;
progress.CurrentItem = 0;
OnProgressChanged(progress);
while (startRow < countFlights)
{
var allSource = Source.GetFlights(criteria, startRow, startRow + (FlightPageSize - 1), "DATE", true, null, false);
countSource += allSource.Count;
var upsertCandidates = new List <BaseStationFlightUpsert>();
var upsertKeys = new HashSet <string>();
foreach (var candidate in allSource)
{
var key = $"{candidate.AircraftID}-{candidate.StartTime}";
if (!upsertKeys.Contains(key))
{
if (!_AircraftMap.TryGetValue(candidate.AircraftID, out var aircraftID) || !_SessionMap.TryGetValue(candidate.SessionID, out var sessionID))
{
++SkippedFlights;
}
else
{
upsertCandidates.Add(new BaseStationFlightUpsert(candidate)
{
AircraftID = aircraftID,
SessionID = sessionID,
});
upsertKeys.Add(key);
}
}
}
var upserted = Target.UpsertManyFlights(upsertCandidates);
countDest += upserted.Length;
startRow += FlightPageSize;
progress.CurrentItem += allSource.Count;
OnProgressChanged(progress);
}
progress.CurrentItem = progress.TotalItems;
OnProgressChanged(progress);
}
}
/// <summary>
/// Returns the WHERE portion of an SQL statement contains the fields describing the criteria passed across.
/// </summary>
/// <param name="aircraft"></param>
/// <param name="criteria"></param>
/// <returns></returns>
public static CriteriaAndProperties GetFlightsCriteria(BaseStationAircraft aircraft, SearchBaseStationCriteria criteria)
{
var result = new CriteriaAndProperties();
StringBuilder command = new StringBuilder();
if (aircraft != null)
{
DynamicSqlBuilder.AddWhereClause(command, "[Flights].[AircraftID]", " = @aircraftID");
result.Parameters.Add("aircraftID", aircraft.AircraftID);
}
if (criteria.UseAlternateCallsigns && criteria.Callsign != null && criteria.Callsign.Condition == FilterCondition.Equals && !String.IsNullOrEmpty(criteria.Callsign.Value))
{
GetAlternateCallsignCriteria(command, result.Parameters, criteria.Callsign, "[Flights].[Callsign]");
}
else
{
DynamicSqlBuilder.AddCriteria(command, criteria.Callsign, result.Parameters, "[Flights].[Callsign]", "callsign");
}
DynamicSqlBuilder.AddCriteria(command, criteria.Date, result.Parameters, "[Flights].[StartTime]", "fromStartTime", "toStartTime");
DynamicSqlBuilder.AddCriteria(command, criteria.Operator, result.Parameters, "[Aircraft].[RegisteredOwners]", "registeredOwners");
DynamicSqlBuilder.AddCriteria(command, criteria.Registration, result.Parameters, "[Aircraft].[Registration]", "registration");
DynamicSqlBuilder.AddCriteria(command, criteria.Icao, result.Parameters, "[Aircraft].[ModeS]", "icao");
DynamicSqlBuilder.AddCriteria(command, criteria.Country, result.Parameters, "[Aircraft].[ModeSCountry]", "modeSCountry");
DynamicSqlBuilder.AddCriteria(command, criteria.IsEmergency, "[Flights].[HadEmergency]");
DynamicSqlBuilder.AddCriteria(command, criteria.Type, result.Parameters, "[Aircraft].[ICAOTypeCode]", "modelIcao");
DynamicSqlBuilder.AddCriteria(command, criteria.FirstAltitude, result.Parameters, "[Flights].[FirstAltitude]", "fromFirstAltitude", "toFirstAltitude");
DynamicSqlBuilder.AddCriteria(command, criteria.LastAltitude, result.Parameters, "[Flights].[LastAltitude]", "fromLastAltitude", "toLastAltitude");
result.SqlChunk = command.ToString();
return(result);
}
/// <summary>
/// Returns the count of flight records that match the criteria passed across.
/// </summary>
/// <param name="connection"></param>
/// <param name="transaction"></param>
/// <param name="log"></param>
/// <param name="criteria"></param>
/// <returns></returns>
public int GetCount(IDbConnection connection, IDbTransaction transaction, TextWriter log, SearchBaseStationCriteria criteria)
{
return(DoGetCount(connection, transaction, log, null, criteria));
}
/// <summary>
/// Attaches ADO.NET parameters to the command passed across to carry the criteria values to the database engine.
/// </summary>
/// <param name="command"></param>
/// <param name="aircraft"></param>
/// <param name="criteria"></param>
private void AddFlightsCriteriaParameters(IDbCommand command, BaseStationAircraft aircraft, SearchBaseStationCriteria criteria)
{
if (aircraft != null)
{
Sql.AddParameter(command, aircraft.AircraftID);
}
if (!String.IsNullOrEmpty(criteria.Callsign))
{
Sql.AddParameter(command, criteria.Callsign);
}
if (criteria.FromDate.Year != DateTime.MinValue.Year)
{
Sql.AddParameter(command, criteria.FromDate);
}
if (criteria.ToDate.Year != DateTime.MaxValue.Year)
{
Sql.AddParameter(command, criteria.ToDate);
}
if (!String.IsNullOrEmpty(criteria.Operator))
{
Sql.AddParameter(command, criteria.Operator);
}
if (!String.IsNullOrEmpty(criteria.Registration))
{
Sql.AddParameter(command, criteria.Registration);
}
if (!String.IsNullOrEmpty(criteria.Icao))
{
Sql.AddParameter(command, criteria.Icao);
}
if (!String.IsNullOrEmpty(criteria.Country))
{
Sql.AddParameter(command, criteria.Country);
}
}
/// <summary>
/// Returns true if the criteria attempts to restrict the search to a single aircraft.
/// </summary>
/// <param name="criteria"></param>
/// <returns></returns>
private bool FilterByAircraftFirst(SearchBaseStationCriteria criteria)
{
return(!String.IsNullOrEmpty(criteria.Icao) ||
!String.IsNullOrEmpty(criteria.Registration));
}
/// <summary>
/// Returns a count of flight records for a single aircraft that match the criteria passed across.
/// </summary>
/// <param name="connection"></param>
/// <param name="transaction"></param>
/// <param name="log"></param>
/// <param name="aircraft"></param>
/// <param name="criteria"></param>
/// <returns></returns>
public int GetCountForAircraft(IDbConnection connection, IDbTransaction transaction, TextWriter log, BaseStationAircraft aircraft, SearchBaseStationCriteria criteria)
{
return(DoGetCount(connection, transaction, log, aircraft, criteria));
}
/// <summary>
/// Performs the work for the Get***Flights methods.
/// </summary>
/// <param name="connection"></param>
/// <param name="transaction"></param>
/// <param name="log"></param>
/// <param name="aircraft"></param>
/// <param name="criteria"></param>
/// <param name="fromRow"></param>
/// <param name="toRow"></param>
/// <param name="sort1"></param>
/// <param name="sort1Ascending"></param>
/// <param name="sort2"></param>
/// <param name="sort2Ascending"></param>
/// <returns></returns>
private List <BaseStationFlight> DoGetFlights(IDbConnection connection, IDbTransaction transaction, TextWriter log, BaseStationAircraft aircraft, SearchBaseStationCriteria criteria, int fromRow, int toRow, string sort1, bool sort1Ascending, string sort2, bool sort2Ascending)
{
List <BaseStationFlight> result = new List <BaseStationFlight>();
sort1 = ConvertSortFieldToColumnName(sort1);
sort2 = ConvertSortFieldToColumnName(sort2);
StringBuilder commandText = new StringBuilder();
commandText.Append(CreateSelectFrom(aircraft, criteria, false));
string criteriaText = GetFlightsCriteria(aircraft, criteria);
if (criteriaText.Length > 0)
{
commandText.AppendFormat(" WHERE {0}", criteriaText);
}
if (sort1 != null || sort2 != null)
{
commandText.Append(" ORDER BY ");
if (sort1 != null)
{
commandText.AppendFormat("{0} {1}", sort1, sort1Ascending ? "ASC" : "DESC");
}
if (sort2 != null)
{
commandText.AppendFormat("{0}{1} {2}", sort1 == null ? "" : ", ", sort2, sort2Ascending ? "ASC" : "DESC");
}
}
commandText.Append(" LIMIT ? OFFSET ?");
bool decodeFlightsFirst = aircraft != null || !FilterByAircraftFirst(criteria);
using (IDbCommand command = connection.CreateCommand()) {
command.Transaction = transaction;
command.CommandText = commandText.ToString();
int limit = toRow == -1 || toRow < fromRow ? int.MaxValue : (toRow - Math.Max(0, fromRow)) + 1;
int offset = fromRow < 0 ? 0 : fromRow;
AddFlightsCriteriaParameters(command, aircraft, criteria);
Sql.AddParameter(command, limit);
Sql.AddParameter(command, offset);
Sql.LogCommand(log, command);
using (IDataReader reader = command.ExecuteReader()) {
Dictionary <int, BaseStationAircraft> aircraftMap = new Dictionary <int, BaseStationAircraft>();
while (reader.Read())
{
int ordinal = 0;
BaseStationFlight flight = DecodeFullFlight(reader, ref ordinal);
if (aircraft != null)
{
flight.Aircraft = aircraft;
}
else
{
if (aircraftMap.ContainsKey(flight.AircraftID))
{
flight.Aircraft = aircraftMap[flight.AircraftID];
}
else
{
flight.Aircraft = AircraftTable.DecodeFullAircraft(reader, ref ordinal);
aircraftMap.Add(flight.AircraftID, flight.Aircraft);
}
}
result.Add(flight);
}
}
}
return(result);
}
public List <BaseStationFlight> GetFlightsForAircraft(BaseStationAircraft aircraft, SearchBaseStationCriteria criteria, int fromRow, int toRow, string sort1, bool sort1Ascending, string sort2, bool sort2Ascending)
{
return(new List <BaseStationFlight>());
}
/// <summary>
/// Returns an ordered subset of flight records for a single aircraft that match the criteria passed across.
/// </summary>
/// <param name="connection"></param>
/// <param name="transaction"></param>
/// <param name="log"></param>
/// <param name="aircraft"></param>
/// <param name="criteria"></param>
/// <param name="fromRow"></param>
/// <param name="toRow"></param>
/// <param name="sort1"></param>
/// <param name="sort1Ascending"></param>
/// <param name="sort2"></param>
/// <param name="sort2Ascending"></param>
/// <returns></returns>
public List <BaseStationFlight> GetForAircraft(IDbConnection connection, IDbTransaction transaction, TextWriter log, BaseStationAircraft aircraft, SearchBaseStationCriteria criteria, int fromRow, int toRow, string sort1, bool sort1Ascending, string sort2, bool sort2Ascending)
{
return(DoGetFlights(connection, transaction, log, aircraft, criteria, fromRow, toRow, sort1, sort1Ascending, sort2, sort2Ascending));
}
