/// <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>
/// Builds up the criteria and properties for all alternate callsigns.
/// </summary>
/// <param name="command"></param>
/// <param name="parameters"></param>
/// <param name="criteria"></param>
/// <param name="callsignField"></param>
private static void GetAlternateCallsignCriteria(StringBuilder command, DynamicParameters parameters, FilterString criteria, string callsignField)
{
if (criteria != null && !String.IsNullOrEmpty(criteria.Value))
{
var callsignParser = Factory.Resolve <ICallsignParser>();
var alternates = callsignParser.GetAllAlternateCallsigns(criteria.Value);
for (var i = 0; i < alternates.Count; ++i)
{
var isFirst = i == 0;
var isLast = i + 1 == alternates.Count;
var callsign = alternates[i];
var parameterName = String.Format("callsign{0}", i + 1);
DynamicSqlBuilder.AddWhereClause(command, callsignField,
String.Format(" {0} @{1}", !criteria.ReverseCondition ? "=" : "<>", parameterName),
useOR: !isFirst && !criteria.ReverseCondition,
openParenthesis: isFirst,
closeParenthesis: isLast);
parameters.Add(parameterName, callsign);
}
}
}