// fetch all rows from table into new List of Contracts, filtered by any column
// links:
// docLink: http://sql2x.org/documentationLink/ce01ef4a-5cd0-4e51-b211-9c0a15b791a0
public List <CrudeFlightContract> FetchWithFilter(System.Guid flightId, System.Guid becameFlightId, System.Guid bindingFlightId, System.Guid airlineId, System.Guid aircraftId, string aircraftTypeRcd, System.Guid departureAirportId, System.Guid arrivalAirportId, System.DateTime fromDateTime, System.DateTime untilDateTime, string comment, System.Guid userId, System.DateTime dateTime)
{
var list = new List <CrudeFlightContract>();
List <CrudeFlightData> dataList = CrudeFlightData.FetchWithFilter(
flightId: flightId,
becameFlightId: becameFlightId,
bindingFlightId: bindingFlightId,
airlineId: airlineId,
aircraftId: aircraftId,
aircraftTypeRcd: aircraftTypeRcd,
departureAirportId: departureAirportId,
arrivalAirportId: arrivalAirportId,
fromDateTime: fromDateTime,
untilDateTime: untilDateTime,
comment: comment,
userId: userId,
dateTime: dateTime
);
foreach (CrudeFlightData data in dataList)
{
var crudeFlightContract = new CrudeFlightContract();
DataToContract(data, crudeFlightContract);
list.Add(crudeFlightContract);
}
return(list);
}
// fetch all from table into new List of class instances, filtered by any column
// links:
// docLink: http://sql2x.org/documentationLink/db27658d-4d23-46d7-9970-7bbaef8634b0
public List <CrudeFlightModel> FetchWithFilter(System.Guid flightId, System.Guid becameFlightId, System.Guid bindingFlightId, System.Guid airlineId, System.Guid aircraftId, string aircraftTypeRcd, System.Guid departureAirportId, System.Guid arrivalAirportId, System.DateTime fromDateTime, System.DateTime untilDateTime, string comment, System.Guid userId, System.DateTime dateTime)
{
var list = new List <CrudeFlightModel>();
List <CrudeFlightData> dataList = CrudeFlightData.FetchWithFilter(flightId, becameFlightId, bindingFlightId, airlineId, aircraftId, aircraftTypeRcd, departureAirportId, arrivalAirportId, fromDateTime, untilDateTime, comment, userId, dateTime);
foreach (CrudeFlightData data in dataList)
{
var crudeFlightBusinessModel = new CrudeFlightModel();
DataToModel(data, crudeFlightBusinessModel);
list.Add(crudeFlightBusinessModel);
}
return(list);
}
/// <summary>Create all flights based on schedule record</summary>
/// <summary>Schedule main dates will be used if segments does not exist</summary>
/// <summary>Identifiers are taken from schedule</summary>
/// <summary>Segments are taken from schedule if existing, default segment created if not</summary>
/// <summary>Flight and segment 'created' events inserted</summary>
/// <summary>Action log event inserted, exceptions logged</summary>
public void MakeFlightsFromSchedule(
Guid flightScheduleId,
Guid userId
)
{
Logging.ActionLog("SolutionNorSolutionPort.BusinessLogicLayer.ScheduleService.MakeFlightsFromSchedule",
userId
);
// get schedule
ScheduleContract scheduleContract = GetSchedule(flightScheduleId, userId);
// make sure there are segments
if (scheduleContract.CrudeFlightScheduleSegments.Length == 0)
{
Logging.ErrorLog("Schedule",
"ScheduleService",
"MakeFlightsFromSchedule",
"Schedule.MakeFlightsFromSchedule: There are no segments on schedule",
string.Empty,
userId
);
throw new Exception("Schedule.MakeFlightsFromSchedule: There are no segments on schedule");
}
// make sure there is at least one identifier
if (scheduleContract.FlightScheduleIdentifier == null ||
scheduleContract.FlightScheduleIdentifier.FlightScheduleIdentifierId == Guid.Empty)
{
Logging.ErrorLog("Schedule",
"ScheduleService",
"MakeFlightsFromSchedule",
"Schedule.MakeFlightsFromSchedule: There are no flight identifiers",
string.Empty,
userId
);
throw new Exception("Schedule.MakeFlightsFromSchedule: There are no flight identifiers");
}
// get first aircraft available, TODO
List <CrudeAircraftContract> aircrafts = new CrudeAircraftService().FetchAll();
if (aircrafts.Count == 0)
{
Logging.ErrorLog("Schedule",
"ScheduleService",
"MakeFlightsFromSchedule",
"Schedule.MakeFlightsFromSchedule: There are no aircrafts available",
string.Empty,
userId
);
throw new Exception("Schedule.MakeFlightsFromSchedule: There are no aircrafts available");
}
// start transaction
using (var connection = new SqlConnection(Conn.ConnectionString)) {
connection.Open();
SqlTransaction transaction = connection.BeginTransaction();
try {
// iterate all dates between from / until
DateTime currentDate = scheduleContract.FlightSchedule.FromDateTime.Date;
while (currentDate >= scheduleContract.FlightSchedule.FromDateTime.Date &&
currentDate <= scheduleContract.FlightSchedule.UntilDateTime)
{
// make new flight
var flight = new CrudeFlightData();
flight.FlightId = Guid.NewGuid();
flight.BindingFlightId = flight.FlightId;
flight.AirlineId = scheduleContract.FlightSchedule.AirlineId;
flight.AircraftId = aircrafts[0].AircraftId;
flight.AircraftTypeRcd = scheduleContract.FlightSchedule.AircraftTypeRcd;
flight.DepartureAirportId = scheduleContract.FlightSchedule.DepartureAirportId;
flight.ArrivalAirportId = scheduleContract.FlightSchedule.ArrivalAirportId;
// compose flight date time departure / arrival time from first and last segment
// todo, what about day shift +-
flight.FromDateTime = currentDate.Add(
scheduleContract.FlightScheduleSegments[0].DepartureTime
);
flight.UntilDateTime = currentDate.Add(
scheduleContract.FlightScheduleSegments[scheduleContract.FlightScheduleSegments.Length - 1].ArrivalTime
);
flight.Comment = scheduleContract.FlightSchedule.Comment;
flight.DateTime = DateTime.UtcNow;
flight.UserId = userId;
// make sure flight does not exist
List <CrudeFlightData> flightDataList =
CrudeFlightData.FetchWithFilter(
flightId: Guid.Empty,
becameFlightId: Guid.Empty,
bindingFlightId: Guid.Empty,
airlineId: flight.AirlineId,
aircraftId: flight.AircraftId,
aircraftTypeRcd: string.Empty,
departureAirportId: flight.DepartureAirportId,
arrivalAirportId: flight.ArrivalAirportId,
fromDateTime: flight.FromDateTime,
untilDateTime: flight.UntilDateTime,
comment: string.Empty,
userId: Guid.Empty,
dateTime: DateTime.MinValue
);
if (flightDataList.Count != 0)
{
continue;
}
flight.Insert(connection, transaction);
// flight identifier
// todo, can be more than one
var identifier = new CrudeFlightIdentifierData();
identifier.FlightIdentifierTypeRcd = scheduleContract.FlightScheduleIdentifier.FlightIdentifierTypeRcd;
identifier.FlightIdentifierCode = scheduleContract.FlightScheduleIdentifier.FlightIdentifierCode;
identifier.FlightId = flight.FlightId;
identifier.UserId = userId;
identifier.DateTime = DateTime.UtcNow;
identifier.Insert(connection, transaction);
// events
AddFlightEvent(connection, transaction, flight.FlightId, DateTimeTypeRef.Created, DateTime.UtcNow, userId);
// planned / estimated times
AddFlightEvent(connection, transaction, flight.FlightId, DateTimeTypeRef.PlannedDeparture, flight.FromDateTime, userId);
AddFlightEvent(connection, transaction, flight.FlightId, DateTimeTypeRef.PlannedArrival, flight.UntilDateTime, userId);
AddFlightEvent(connection, transaction, flight.FlightId, DateTimeTypeRef.EstimatedDeparture, flight.FromDateTime, userId);
AddFlightEvent(connection, transaction, flight.FlightId, DateTimeTypeRef.EstimatedArrival, flight.UntilDateTime, userId);
// open for booking
AddFlightEvent(connection, transaction, flight.FlightId, DateTimeTypeRef.BookingOpen, DateTime.UtcNow, userId);
// check rules
// create flight segments for that day
foreach (CrudeFlightScheduleSegmentContract scheduleSegment in scheduleContract.CrudeFlightScheduleSegments)
{
var flightSegment = new CrudeFlightSegmentData();
flightSegment.FlightSegmentId = Guid.NewGuid();
flightSegment.FlightId = flight.FlightId;
flightSegment.DepartureAirportId = scheduleSegment.DepartureAirportId;
flightSegment.ArrivalAirportId = scheduleSegment.ArrivalAirportId;
flightSegment.LogicalSegmentNumber = scheduleSegment.LogicalSegmentNumber;
flightSegment.PhysicalSegmentNumber = scheduleSegment.PhysicalSegmentNumber;
flightSegment.FromDateTime = currentDate.Add(scheduleSegment.DepartureTime);
flightSegment.UntilDateTime = currentDate.Add(scheduleSegment.ArrivalTime);
flightSegment.DepartureGate = scheduleSegment.DepartureGate;
flightSegment.ArrivalGate = scheduleSegment.ArrivalGate;
flightSegment.UserId = userId;
flightSegment.DateTime = DateTime.UtcNow;
flightSegment.Insert(connection, transaction);
// events
AddFlightSegmentEvent(connection, transaction, flightSegment.FlightSegmentId, DateTimeTypeRef.Created, DateTime.UtcNow, userId);
// planned / estimated times
AddFlightSegmentEvent(connection, transaction, flightSegment.FlightSegmentId, DateTimeTypeRef.PlannedDeparture, flightSegment.FromDateTime, userId);
AddFlightSegmentEvent(connection, transaction, flightSegment.FlightSegmentId, DateTimeTypeRef.PlannedArrival, flightSegment.UntilDateTime, userId);
AddFlightSegmentEvent(connection, transaction, flightSegment.FlightSegmentId, DateTimeTypeRef.EstimatedDeparture, flightSegment.FromDateTime, userId);
AddFlightSegmentEvent(connection, transaction, flightSegment.FlightSegmentId, DateTimeTypeRef.EstimatedArrival, flightSegment.UntilDateTime, userId);
// open for booking
AddFlightSegmentEvent(connection, transaction, flightSegment.FlightSegmentId, DateTimeTypeRef.BookingOpen, DateTime.UtcNow, userId);
}
currentDate = currentDate.AddDays(1);
break; // break here since we are testing with a fresh database where from date is todays date
}
// commit transaction
transaction.Commit();
} catch (Exception ex) {
transaction.Rollback();
Logging.ErrorLog("Schedule",
"ScheduleService",
"MakeFlightsFromSchedule",
ex.Message,
ex.StackTrace,
userId
);
throw ex;
}
}
}