/// <summary>
/// Adds a flight item
/// </summary>
/// <param name="source">New item to be added.</param>
/// <returns>The id of the newly created item.</returns>
public async Task <Bm> AddAsync(Bm source)
{
#region SQL-query: INSERT INTO flights
const string sqlQuery =
"INSERT INTO " +
"flights (name, \"from\", \"to\", airline_id, flight_type_id, tourist_info_edit_lock, return_flight_same_airline_required, alternative_search, flight_duration, airplane_model_id, status_id) " +
"VALUES (@Name, @From, @To, @AirlineId, @FlightTypeId, @TouristInfoEditLock, @ReturnFlightSameAirlineRequired, @AlternativeSearch, @FlightDuration, @AirplaneModelId, @StatusId) " +
"RETURNING id AS Id";
#endregion
var entity = _mapper.Map <Entity>(source);
using var conn = _conn.GetConnection();
entity.Id = await conn.QuerySingleAsync <long>(sqlQuery, entity);
return(_mapper.Map <Bm>(entity));
}
/// <summary>
/// Partial update of a record
/// </summary>
/// <param name="id">Id of a record.</param>
/// <param name="property">Column name in database.</param>
/// <param name="model">New value.</param>
/// <returns>Updated item.</returns>
public async Task <Bm> PatchRecordAsync(long id, string property, Bm model)
{
var tablePropertyName = AllowedColumns.GetValueOrDefault(property);
#region SQL-query: UPDATE flights SET
string sqlQueryUpdate = $"UPDATE flights SET {tablePropertyName}=@{property} WHERE id=@id;";
#endregion
#region SQL-query: SELECT FROM flights
const string sqlQuerySelect =
"WITH aggtab AS( " +
"SELECT * " +
"FROM flight_dates fd " +
"INNER JOIN flights fl ON fd.flight_id = @soughtId " +
"WHERE current_timestamp<fd.departure_date " +
"ORDER BY fd.departure_date-current_timestamp ASC " +
"), " +
"nearest_dates AS( " +
"SELECT fd.flight_id, fd.departure_date, fd.arrival_date " +
"FROM flight_dates fd " +
"INNER JOIN (SELECT aggtab.flight_id, MIN(aggtab.departure_date) AS NearestDepDate " +
"FROM aggtab " +
"GROUP BY aggtab.flight_id) nd ON nd.flight_id = fd.flight_id " +
"WHERE fd.departure_date = nd.NearestDepDate " +
") " +
"SELECT " +
"fl.id AS Id, " +
"fl.name AS Name, " +
"fl.from AS \"From\", " +
"fl.to AS \"To\", " +
"nd.departure_date AS NearestDepartureDate, " +
"nd.arrival_date AS NearestArrivalDate, " +
"fl.airline_id AS AirLineId, " +
"fl.flight_type_id AS FlightTypeId, " +
"fl.tourist_info_edit_lock AS TouristInfoEditLock, " +
"fl.return_flight_same_airline_required AS ReturnFlightSameAirlineRequired, " +
"fl.alternative_search AS AlternativeSearch, " +
"fl.flight_duration AS FlightDuration, " +
"fl.airplane_model_id AS AirplaneModelId, " +
"fl.status_id AS StatusId, " +
"false AS IsReturnFlight, " +
"fl.id, " +
"rf.flight_from_id AS ReturnId " +
"FROM flights fl " +
"LEFT JOIN return_flights rf ON fl.id = rf.flight_to_id " +
"LEFT JOIN nearest_dates nd ON nd.flight_id = fl.id " +
"WHERE fl.id=@soughtId;";
#endregion
var dateDictionary = new Dictionary <long?, Bm>();
var entity = _mapper.Map <Entity>(model, opt => { opt.AfterMap((model, dest) => dest.Id = id); });
using var conn = _conn.GetConnection();
await conn.ExecuteAsync(sqlQueryUpdate, entity);
return((await conn.QueryAsync <Bm, ReturnFlightIdsHelper, Bm>(sqlQuerySelect, (flight, retFlightId) =>
{
if (!dateDictionary.TryGetValue(flight.Id, out Bm flightEntry))
{
flightEntry = flight;
flightEntry.ReturnFlightIds = new List <long>();
dateDictionary.Add(flightEntry.Id, flightEntry);
}
flightEntry.ReturnFlightIds.Add(retFlightId.ReturnId);
return flightEntry;
}, new { soughtId = id })).Distinct().SingleOrDefault());
}