/// <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>
/// Disposes of or finalises the object. Note that the object is sealed.
/// </summary>
/// <param name="disposing"></param>
private void Dispose(bool disposing)
{
if (disposing)
{
_TransactionHelper.Abandon();
CloseConnection();
if (_AircraftTable != null)
{
_AircraftTable.Dispose();
_AircraftTable = null;
}
if (_FlightTable != null)
{
_FlightTable.Dispose();
_FlightTable = null;
}
if (_DbHistoryTable != null)
{
_DbHistoryTable.Dispose();
_DbHistoryTable = null;
}
if (_DbInfoTable != null)
{
_DbInfoTable.Dispose();
_DbInfoTable = null;
}
if (_LocationsTable != null)
{
_LocationsTable.Dispose();
_LocationsTable = null;
}
if (_SessionsTable != null)
{
_SessionsTable.Dispose();
_SessionsTable = null;
}
if (_SystemEventsTable != null)
{
_SystemEventsTable.Dispose();
_SystemEventsTable = null;
}
if (_ConfigurationStorage != null)
{
_ConfigurationStorage.ConfigurationChanged -= ConfigurationStorage_ConfigurationChanged;
_ConfigurationStorage = null;
}
if (_DatabaseLog != null)
{
_DatabaseLog.Flush();
_DatabaseLog.Dispose();
_DatabaseLog = null;
}
}
}
/// <summary>
/// Disposes of or finalises the object. Note that the object is sealed.
/// </summary>
/// <param name="disposing"></param>
private void Dispose(bool disposing)
{
if(disposing) {
_TransactionHelper.Abandon();
CloseConnection();
if(_AircraftTable != null) {
_AircraftTable.Dispose();
_AircraftTable = null;
}
if(_FlightTable != null) {
_FlightTable.Dispose();
_FlightTable = null;
}
if(_DbHistoryTable != null) {
_DbHistoryTable.Dispose();
_DbHistoryTable = null;
}
if(_DbInfoTable != null) {
_DbInfoTable.Dispose();
_DbInfoTable = null;
}
if(_LocationsTable != null) {
_LocationsTable.Dispose();
_LocationsTable = null;
}
if(_SessionsTable != null) {
_SessionsTable.Dispose();
_SessionsTable = null;
}
if(_SystemEventsTable != null) {
_SystemEventsTable.Dispose();
_SystemEventsTable = null;
}
if(_ConfigurationStorage != null) {
_ConfigurationStorage.ConfigurationChanged -= ConfigurationStorage_ConfigurationChanged;
_ConfigurationStorage = null;
}
if(_DatabaseLog != null) {
_DatabaseLog.Flush();
_DatabaseLog.Dispose();
_DatabaseLog = null;
}
}
}
/// <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);
}
