// 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; } } }