public void Insert(AmadeusSyncTaskModel amadeusSyncTaskModel)
        {
            if (amadeusSyncTaskModel == null) throw new ArgumentNullException("amadeusSyncTaskModel");
            const string query = @"INSERT INTO [AmadeusSyncTask] ([Id], [ExecutionTime], [Completed], [CompletedSuccessfully], [Error] ) VALUES (@Id, @ExecutionTime, @Completed ,@CompletedSuccessfully, @Error )";
            ExecuteQuery(amadeusSyncTaskModel, query);

        }
        private void ExecuteQuery(AmadeusSyncTaskModel amadeusSyncTaskModel, string query)
        {
            var parameters = new DynamicParameters();
            parameters.Add("Id", amadeusSyncTaskModel.Id);
            parameters.Add("ExecutionTime", amadeusSyncTaskModel.ExecutionTime);
            parameters.Add("CompletedSuccessfully", amadeusSyncTaskModel.CompletedSuccessfully);
            parameters.Add("Completed", amadeusSyncTaskModel.Completed);
            parameters.Add("Error", amadeusSyncTaskModel.Error);

            using (var connection = _connectionFactory.GetConnection())
            {
                connection.Execute(query, parameters);
            }
        }
        private void UpdateReservations(DateTime executionTime)
        {

            // enable logging with log4net by adding the log4net reference
            // (http://logging.apache.org/log4net/) to the path
            string config = Directory.GetCurrentDirectory() + @"\..\..\resources\log4net_config.xml";
            XmlConfigurator.Configure(new FileInfo(config));

            // To log XML Requests and responses, see app.config

            ServiceHandler serviceHandler = new ServiceHandler(ConfigurationManager.AppSettings["wsap"], ConfigurationManager.AppSettings["enpointName"]);


            var connectionFactory = new ConnectionFactory(ConfigurationManager.AppSettings["DatabaseServer"], ConfigurationManager.AppSettings["DatabaseName"], ConfigurationManager.AppSettings["DatabaseParameters"]);
            var amadeusQueueRepository = new AmadeusQueueRepository(connectionFactory);
            var categoryRepository = new CategoryRepository(connectionFactory);
            var amadeusSyncTaskRepository = new AmadeusSyncTaskRepository(connectionFactory);

            if (amadeusSyncTaskRepository.IsExecuting())
            {
                return;
            }

            var pnrRepository = new PassengerNameRecordRepository(connectionFactory);
            var queues = amadeusQueueRepository.GetEnabledQueues();
            var categories = categoryRepository.GetAll();

            var amadeusSyncTaskId = Guid.NewGuid();
            var amadeusSyncTaskModel = new AmadeusSyncTaskModel() { Id = amadeusSyncTaskId, ExecutionTime = executionTime, Completed = false, CompletedSuccessfully = false, Error= String.Empty };
            amadeusSyncTaskRepository.Insert(amadeusSyncTaskModel);

            try
            {
                foreach (var queue in queues)
                {
                    var reservations = serviceHandler.GetReservations(queue.OfficeId, queue.Code.ToString(), SessionHandler.TransactionStatusCode.None, TransactionFlowLinkHandler.TransactionFlowLinkAction.None);

                    if (reservations != null)
                    {
                        foreach (var reservation in reservations)
                        {
                            var s = "";
                            var error = "";

                            PNR_Reply pnr = null;
                            try
                            {
                                pnr = serviceHandler.PNR_Retrieve(SessionHandler.TransactionStatusCode.None, TransactionFlowLinkHandler.TransactionFlowLinkAction.None, reservation.recLoc.reservation.controlNumber);
                                s = GetXMLFromObject(pnr);
                            }
                            catch (Exception ex)
                            {
                                error = ex.Message;
                            }
                            var agentId = String.Empty;
                            var iataCode = String.Empty;
                            var officeId = String.Empty;
                            var typeOfPnrElement = String.Empty;
                            var queueingOfficeId = String.Empty;
                            var location = String.Empty;

                            var creationOfficeId = String.Empty;
                            var agentSignature = String.Empty;
                            var creatorIataCode = String.Empty;
                            var creationDate = new DateTime(1900, 1, 1);

                            Guid? categoryId = null;

                            var departureDate = DEPARTURE_DATE_IS_MISSING;
                            var departureAirport = AIRPORT_IS_MISSING;
                            var arrivalAirport = AIRPORT_IS_MISSING;

                            if (pnr != null)
                            {

                                var existingPnrs = pnrRepository.Get(queue.Id, reservation.recLoc.reservation.controlNumber);
                                if (existingPnrs.Count > 1)
                                {
                                    throw new Exception(string.Format("Unexpected Error. Too many pnrs were found. QueueId:{0}, ControlNumber:{1}", queue.Id, reservation.recLoc.reservation.controlNumber));
                                }

                                if (existingPnrs.Count == 1)
                                {
                                    if (existingPnrs.First().IsHandled)
                                    {
                                        continue;
                                    }

                                    pnrRepository.Delete(queue.Id, reservation.recLoc.reservation.controlNumber);
                                }

                                departureDate = GetDepartureDate(pnr, reservation.recLoc.reservation.controlNumber);
                                if (departureDate == DEPARTURE_DATE_IS_MISSING)
                                {
                                    if (!string.IsNullOrEmpty(error))
                                    {
                                        error = error + ", ";
                                    }
                                    error = error + " No Departure date found";
                                }

                                departureAirport = GetDepartureAirport(pnr, reservation.recLoc.reservation.controlNumber);
                                if (departureAirport == AIRPORT_IS_MISSING)
                                {
                                    if (!string.IsNullOrEmpty(error))
                                    {
                                        error = error + ", ";
                                    }
                                    error = error + " No Departure airport found";
                                }

                                arrivalAirport = GetArrivalAirport(pnr, reservation.recLoc.reservation.controlNumber);
                                if (arrivalAirport == AIRPORT_IS_MISSING)
                                {
                                    if (!string.IsNullOrEmpty(error))
                                    {
                                        error = error + ", ";
                                    }
                                    error = error + " No Arrival airport found";
                                }


                                if (pnr.securityInformation != null)
                                {
                                    location = pnr.securityInformation.cityCode;

                                    if (pnr.securityInformation.responsibilityInformation != null)
                                    {
                                        agentId = pnr.securityInformation.responsibilityInformation.agentId;
                                        iataCode = pnr.securityInformation.responsibilityInformation.iataCode;
                                        officeId = pnr.securityInformation.responsibilityInformation.officeId;
                                        typeOfPnrElement = pnr.securityInformation.responsibilityInformation.typeOfPnrElement;
                                    }

                                    if (pnr.securityInformation.queueingInformation != null)
                                    {
                                        queueingOfficeId = pnr.securityInformation.queueingInformation.queueingOfficeId;
                                    }

                                    if (pnr.securityInformation.secondRpInformation != null)
                                    {
                                        creationOfficeId = pnr.securityInformation.secondRpInformation.creationOfficeId;
                                        agentSignature = pnr.securityInformation.secondRpInformation.agentSignature;
                                        creatorIataCode = pnr.securityInformation.secondRpInformation.creatorIataCode;

                                        DateTime datePart;
                                        if (DateTime.TryParseExact(pnr.securityInformation.secondRpInformation.creationDate + ":" + pnr.securityInformation.secondRpInformation.creationTime, "ddMMyy:HHmm", CultureInfo.InvariantCulture, DateTimeStyles.None, out datePart))
                                        {
                                            creationDate = datePart;
                                        }
                                        else if (DateTime.TryParseExact(pnr.securityInformation.secondRpInformation.creationDate, "ddMMyy", CultureInfo.InvariantCulture, DateTimeStyles.None, out datePart))
                                        {
                                            creationDate = datePart;
                                        }
                                    }

                                    if (!string.IsNullOrEmpty(officeId))
                                    {
                                        var matchingCategories = categories.Where(c => c.OfficeId == officeId).ToList();
                                        if (matchingCategories.Count() != 1)
                                        {
                                            if (!string.IsNullOrEmpty(error))
                                            {
                                                error = error + ", ";
                                            }
                                            error = error + string.Format("No category match fo officeId:{0}", officeId);
                                        }
                                        else
                                        {
                                            categoryId = matchingCategories.First().Id;
                                        }
                                    }

                                }

                            }

                            var pnrModel = 
                                new Repository.Models.PassengerNameRecordModel()
                                {
                                    Id = Guid.NewGuid(),
                                    QueueId = queue.Id,
                                    Number = reservation.recLoc.reservation.controlNumber,
                                    Quantity = reservation.paxName.paxDetails.quantity,
                                    SurName = reservation.paxName.paxDetails.surname,
                                    Type = reservation.paxName.paxDetails.type,
                                    Date1 = new DateTime(string.IsNullOrEmpty(reservation.pnrdates[0].dateTime.year) ? 0 : int.Parse(reservation.pnrdates[0].dateTime.year), string.IsNullOrEmpty(reservation.pnrdates[0].dateTime.month) ? 0 : int.Parse(reservation.pnrdates[0].dateTime.month), string.IsNullOrEmpty(reservation.pnrdates[0].dateTime.day) ? 0 : int.Parse(reservation.pnrdates[0].dateTime.day), string.IsNullOrEmpty(reservation.pnrdates[0].dateTime.hour) ? 0 : int.Parse(reservation.pnrdates[0].dateTime.hour), string.IsNullOrEmpty(reservation.pnrdates[0].dateTime.minutes) ? 0 : int.Parse(reservation.pnrdates[0].dateTime.minutes), 0),
                                    Date2 = new DateTime(string.IsNullOrEmpty(reservation.pnrdates[1].dateTime.year) ? 0 : int.Parse(reservation.pnrdates[1].dateTime.year), string.IsNullOrEmpty(reservation.pnrdates[1].dateTime.month) ? 0 : int.Parse(reservation.pnrdates[1].dateTime.month), string.IsNullOrEmpty(reservation.pnrdates[1].dateTime.day) ? 0 : int.Parse(reservation.pnrdates[1].dateTime.day), string.IsNullOrEmpty(reservation.pnrdates[1].dateTime.hour) ? 0 : int.Parse(reservation.pnrdates[1].dateTime.hour), string.IsNullOrEmpty(reservation.pnrdates[1].dateTime.minutes) ? 0 : int.Parse(reservation.pnrdates[1].dateTime.minutes), 0),
                                    Date3 = new DateTime(string.IsNullOrEmpty(reservation.pnrdates[2].dateTime.year) ? 0 : int.Parse(reservation.pnrdates[2].dateTime.year), string.IsNullOrEmpty(reservation.pnrdates[2].dateTime.month) ? 0 : int.Parse(reservation.pnrdates[2].dateTime.month), string.IsNullOrEmpty(reservation.pnrdates[2].dateTime.day) ? 0 : int.Parse(reservation.pnrdates[2].dateTime.day), string.IsNullOrEmpty(reservation.pnrdates[2].dateTime.hour) ? 0 : int.Parse(reservation.pnrdates[2].dateTime.hour), string.IsNullOrEmpty(reservation.pnrdates[2].dateTime.minutes) ? 0 : int.Parse(reservation.pnrdates[2].dateTime.minutes), 0),
                                    DepartureDate = departureDate,
                                    DepartureAirport = departureAirport,
                                    ArrivalAirport = arrivalAirport,
                                    IsGroupBooking = IsGroupBooking(reservation.paxName.paxDetails.type),
                                    Info = s,
                                    Error = error,
                                    TimeStamp = executionTime,
                                    IsHandled = false,
                                    AmadeusSyncTaskId = amadeusSyncTaskId,

                                    AgentId = agentId,
                                    IataCode = iataCode,
                                    OfficeId = officeId,
                                    TypeOfPnrElement = typeOfPnrElement,
                                    QueueingOfficeId = queueingOfficeId,
                                    Location = location,

                                    CreationOfficeId = creationOfficeId,
                                    AgentSignature = agentSignature,
                                    CreatorIataCode = creatorIataCode,
                                    CreationDate = creationDate,
                                    CategoryId = categoryId

                                };

                            pnrRepository.Insert(pnrModel);
                            
                        }
                    }
                }
                amadeusSyncTaskModel.Completed = true;
                amadeusSyncTaskModel.CompletedSuccessfully = true;
                amadeusSyncTaskModel.Error = String.Empty;
                amadeusSyncTaskRepository.Update(amadeusSyncTaskModel);
            }
            catch (Exception ex)
            {
                amadeusSyncTaskModel.Completed = true;
                amadeusSyncTaskModel.CompletedSuccessfully = false;
                amadeusSyncTaskModel.Error = ex.Message;
                amadeusSyncTaskRepository.Update(amadeusSyncTaskModel);
            }
        }
 public void Update(AmadeusSyncTaskModel amadeusSyncTaskModel)
 {
     if (amadeusSyncTaskModel == null) throw new ArgumentNullException("amadeusSyncTaskModel");
     const string query = "UPDATE [AmadeusSyncTask] SET ExecutionTime= @ExecutionTime, Completed = @Completed,  CompletedSuccessfully = @CompletedSuccessfully, Error = @Error WHERE Id = @Id;";
     ExecuteQuery(amadeusSyncTaskModel, query);
 }