public void TestInitialise()
        {
            _OriginalClassFactory = Factory.TakeSnapshot();
            _RuntimeEnvironment = TestUtilities.CreateMockSingleton<IRuntimeEnvironment>();

            _ConfigurationStorage = TestUtilities.CreateMockSingleton<IConfigurationStorage>();
            _Configuration = new Configuration();
            _ConfigurationStorage.Setup(s => s.Load()).Returns(_Configuration);

            _CreateDatabaseFileName = Path.Combine(TestContext.TestDeploymentDir, "CreatedDatabase.sqb");
            if(File.Exists(_CreateDatabaseFileName)) File.Delete(_CreateDatabaseFileName);

            _EmptyDatabaseFileName = Path.Combine(TestContext.TestDeploymentDir, "TestCopyBaseStation.sqb");
            File.Copy(Path.Combine(TestContext.TestDeploymentDir, "BaseStation.sqb"), _EmptyDatabaseFileName, true);

            _Database = Factory.Singleton.Resolve<IBaseStationDatabase>();
            _Database.FileName = _EmptyDatabaseFileName;

            _Provider = new Mock<IBaseStationDatabaseProvider>() { DefaultValue = DefaultValue.Mock }.SetupAllProperties();
            _Database.Provider = _Provider.Object;
            _Provider.Setup(p => p.UtcNow).Returns(DateTime.UtcNow);

            _ConnectionStringBuilder = new SQLiteConnectionStringBuilder() { DataSource = _EmptyDatabaseFileName };

            _Criteria = new SearchBaseStationCriteria() {
                FromDate = DateTime.MinValue,
                ToDate = DateTime.MaxValue,
            };

            _FileNameChangingEvent = new EventRecorder<EventArgs>();
            _FileNameChangedEvent = new EventRecorder<EventArgs>();
        }
 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>
        /// See interface docs.
        /// </summary>
        /// <param name="aircraft"></param>
        /// <param name="criteria"></param>
        /// <returns></returns>
        public int GetCountOfFlightsForAircraft(BaseStationAircraft aircraft, SearchBaseStationCriteria criteria)
        {
            if(criteria == null) throw new ArgumentNullException("criteria");
            FlightsTable.NormaliseCriteria(criteria);

            int result = 0;

            if(aircraft != null) {
                lock(_ConnectionLock) {
                    OpenConnection();
                    if(_Connection != null) result = _FlightTable.GetCountForAircraft(_Connection, null, _DatabaseLog, aircraft, 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;
        }
 /// <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);
 }
        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>
 /// 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);
 }
 public List<BaseStationFlight> GetFlights(SearchBaseStationCriteria criteria, int fromRow, int toRow, string sortField1, bool sortField1Ascending, string sortField2, bool sortField2Ascending)
 {
     return new List<BaseStationFlight>();
 }
        /// <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;
        }
        /// <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>
        /// 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;
        }
 /// <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);
 }
 /// <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);
 }
 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 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();
        }
 public int GetCountOfFlightsForAircraft(BaseStationAircraft aircraft, SearchBaseStationCriteria criteria)
 {
     return 0;
 }
 /// <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);
 }
 public int GetCountOfFlights(SearchBaseStationCriteria criteria)
 {
     return 0;
 }
 /// <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);
 }