public ScenarioCampaignMetricItem CalculateCampaignKPIs(CampaignReducedModel campaign, IEnumerable <Recommendation> campaignRecommendations, decimal nominalPrice)
        {
            int    totalSpotsBooked = 0;
            int    zeroRatedSpots   = 0;
            double nominalValue     = 0d;

            foreach (var campaignRecommendation in campaignRecommendations)
            {
                nominalValue += campaignRecommendation.NominalPrice;

                if (campaignRecommendation.Action != KPICalculationHelpers.SpotTags.Booked)
                {
                    continue;
                }

                totalSpotsBooked++;

                if (campaignRecommendation.SpotRating == 0)
                {
                    zeroRatedSpots++;
                }
            }

            var campaignRevenue                    = campaign.RevenueBudget;
            var totalNominalValue                  = nominalValue + (double)nominalPrice;
            var differenceValueDelivered           = totalNominalValue - campaignRevenue;
            var differenceValueDeliveredPercentage = campaignRevenue == 0 ? 0 : Math.Round(differenceValueDelivered / campaignRevenue, 2);

            return(new ScenarioCampaignMetricItem
            {
                CampaignExternalId = campaign.ExternalId,
                TotalSpots = totalSpotsBooked,
                ZeroRatedSpots = zeroRatedSpots,
                NominalValue = nominalValue,
                TotalNominalValue = totalNominalValue,
                DifferenceValueDelivered = differenceValueDelivered,
                DifferenceValueDeliveredPercentage = differenceValueDeliveredPercentage
            });
        }
        public SpotsReqmOutput ProcessFile(Guid scenarioId, string folder)
        {
            string spotFile     = FileHelpers.GetPathToFileIfExists(folder, FileName);
            string mainSpotFile = FileHelpers.GetPathToFileIfExists(folder, OutputFileNames.MainSpotTable);
            var    output       = new SpotsReqmOutput
            {
                ScenarioId = scenarioId
            };

            if (String.IsNullOrEmpty(spotFile) || String.IsNullOrEmpty(mainSpotFile))
            {
                return(output);
            }

            _audit.Insert(AuditEventFactory.CreateAuditEventForInformationMessage(0,
                                                                                  0, $"Processing output file {spotFile}"));

            _audit.Insert(AuditEventFactory.CreateAuditEventForInformationMessage(0,
                                                                                  0, $"Processing output file {mainSpotFile}"));

            // Process file
            DateTime processorDateTime = DateTime.UtcNow;

            var importSettings = CSVImportSettings.GetImportSettings(mainSpotFile, typeof(SpotHeaderMap), typeof(SpotIndexMap));

            // Get main spots if path passed in
            ISpotImportRepository    spotImportRepository = new CSVSpotImportRepository(importSettings);
            IEnumerable <SpotImport> spotImports          = spotImportRepository.GetAll();

            importSettings = CSVImportSettings.GetImportSettings(spotFile, typeof(SpotReqmHeaderMap), typeof(SpotReqmIndexMap));

            ISpotReqmImportRepository spotReqmImportRepository = new CSVSpotReqmImportRepository(importSettings);
            var spotReqmImports = spotReqmImportRepository.GetAll();

            // Index for lookup performance
            var campaignsByCustomId       = CampaignReducedModel.IndexListByCustomId(_campaignRepository.GetAllFlat());
            var spotsByCustomId           = SpotHelper.IndexListById(_dataSnapshot.SpotsForRun.Value);
            var demographicsById          = Demographic.IndexListById(_dataSnapshot.AllDemographics.Value);
            var programmeDictionariesById = ProgrammeDictionary.IndexListById(_dataSnapshot.AllProgrammeDictionaries.Value);

            IEnumerable <Pass> scenarioPasses = _dataSnapshot.ScenarioPasses.Value;

            foreach (var currentSalesArea in _dataSnapshot.AllSalesAreas.Value)
            {
                foreach (SpotReqmImport spotReqmImport in spotReqmImports.Where(sri => sri.sare_no == currentSalesArea.CustomId))
                {
                    output.CountTotalSpots++;

                    try
                    {
                        Recommendation recommendation = _mapper.Map <Recommendation>(Tuple.Create(spotReqmImport, new List <ProgrammeDictionary>(), _dataSnapshot.BreakTypes.Value));
                        recommendation.ScenarioId        = scenarioId;
                        recommendation.ProcessorDateTime = processorDateTime;

                        bool isCancelledSpot = !String.IsNullOrEmpty(spotReqmImport.status) && spotReqmImport.status == "C";

                        if (isCancelledSpot)
                        {
                            output.CountCancelledSpots++;
                            recommendation.SpotSequenceNumber = default;

                            if (spotsByCustomId.TryGetValue((int)spotReqmImport.spot_no, out var spot))
                            {
                                recommendation.ExternalSpotRef       = spot.ExternalSpotRef;
                                recommendation.Sponsored             = spot.Sponsored;
                                recommendation.Preemptable           = spot.Preemptable;
                                recommendation.Preemptlevel          = spot.Preemptlevel;
                                recommendation.MultipartSpot         = spot.MultipartSpot;
                                recommendation.MultipartSpotPosition = spot.MultipartSpotPosition;
                                recommendation.MultipartSpotRef      = spot.MultipartSpotRef;
                            }
                            else
                            {
                                _audit.Insert(
                                    AuditEventFactory.CreateAuditEventForWarningMessage(
                                        0,
                                        0,
                                        $"Unable to set cancelled spot details for recommendations" +
                                        $" because spot {spotReqmImport.spot_no} does not exist")
                                    );
                            }
                        }
                        else
                        {
                            recommendation.SpotSequenceNumber = spotReqmImport.spot_no;
                        }

                        Pass pass;

                        if (scenarioPasses != null && scenarioPasses.Any() && (pass = scenarioPasses.FirstOrDefault(p => p.Id == spotReqmImport.abdn_no)) != null)
                        {
                            recommendation.PassName = pass.Name;
                            recommendation.OptimiserPassSequenceNumber = spotReqmImport.pass_sequence_no;
                        }

                        recommendation.CampaignPassPriority = spotReqmImport.campaign_pass_priority;
                        recommendation.RankOfEfficiency     = spotReqmImport.rank_of_efficiency;
                        recommendation.RankOfCampaign       = spotReqmImport.rank_of_campaign;
                        recommendation.CampaignWeighting    = spotReqmImport.campaign_weighting;

                        recommendation.SalesArea = recommendation.GroupCode = currentSalesArea.Name;

                        recommendation.NominalPrice = spotReqmImport.nominal_price;

                        recommendation.ExternalCampaignNumber = campaignsByCustomId.TryGetValue((int)spotReqmImport.camp_no, out var campaign)
                            ? campaign.ExternalId
                            : string.Empty;

                        recommendation.Demographic = demographicsById.TryGetValue(spotReqmImport.demo_no, out var demographic)
                            ? demographic.ExternalRef
                            : string.Empty;

                        if (programmeDictionariesById.TryGetValue((int)spotReqmImport.prog_no, out var programmeDictionary))
                        {
                            recommendation.ExternalProgrammeReference = programmeDictionary.ExternalReference;
                            recommendation.ProgrammeName = programmeDictionary.ProgrammeName;
                        }
                        else
                        {
                            recommendation.ExternalProgrammeReference = recommendation.ProgrammeName = string.Empty;
                        }

                        recommendation.ClientPicked    = spotReqmImport.client_picked.ToUpper() == "Y";
                        recommendation.ExternalBreakNo = spotReqmImport.brek_external_ref;

                        output.Recommendations.Add(recommendation);
                    }
                    catch (System.Exception exception)
                    {
                        // Log exception, continue
                        _audit.Insert(AuditEventFactory.CreateAuditEventForException(0, 0, string.Format("Error generating recommendation for spot no {0}", spotReqmImport.spot_no), exception));
                    }
                }
            }

            // TODO: Remove this when it has been confirmed that cancellations appear in Spot Req file
            // Process cancelled spots if they weren't present in the Spot Req
            // file above
            if (output.CountCancelledSpots == 0)
            {
                string scheduleIndexKey = string.Empty;

                var scheduleBreaksBySalesAreaAndDate = _dataSnapshot
                                                       .BreaksForRun
                                                       .Value
                                                       .GroupBy(k => GetScheduleIndexKey(k.SalesAreaName, k.ScheduleDate.Date))
                                                       .ToDictionary(c => c.Key, c => c.ToDictionary(v => v.CustomId));

                _audit.Insert(AuditEventFactory.CreateAuditEventForWarningMessage(0, 0, string.Format("Processing {0} for cancellations because no cancelled spots were found in {1}", Path.GetFileName(mainSpotFile), Path.GetFileName(spotFile))));

                foreach (var currentSalesArea in _dataSnapshot.AllSalesAreas.Value)
                {
                    foreach (SpotImport spotImport2 in spotImports.Where(s => s.status == "C" && s.sare_no == currentSalesArea.CustomId))
                    {
                        try
                        {
                            scheduleIndexKey = string.Empty;
                            bool isCancelledSpot = !String.IsNullOrEmpty(spotImport2.status) && spotImport2.status == "C";    // Should all be cancelled

                            // Update statistics for Spot Performance, Campaign
                            // Performance, New Efficiency, cancelled spots
                            // should be excluded
                            output.CountTotalSpots++;
                            if (isCancelledSpot)
                            {
                                output.CountCancelledSpots++;
                            }

                            // Create recommendation
                            Recommendation recommendation = _mapper.Map <Recommendation>(Tuple.Create(spotImport2, new List <ProgrammeDictionary>()));
                            recommendation.ScenarioId        = scenarioId;
                            recommendation.ProcessorDateTime = processorDateTime;

                            if (spotsByCustomId.TryGetValue((int)spotImport2.spot_no, out var spot))
                            {
                                recommendation.Sponsored             = spot.Sponsored;
                                recommendation.Preemptable           = spot.Preemptable;
                                recommendation.Preemptlevel          = spot.Preemptlevel;
                                recommendation.ExternalSpotRef       = spot.ExternalSpotRef;
                                recommendation.MultipartSpot         = spot.MultipartSpot;
                                recommendation.MultipartSpotPosition = spot.MultipartSpotPosition;
                                recommendation.MultipartSpotRef      = spot.MultipartSpotRef;
                            }
                            else
                            {
                                _audit.Insert(
                                    AuditEventFactory.CreateAuditEventForWarningMessage(
                                        0,
                                        0,
                                        $"Unable to set multipart spot details for recommendations because spot {spotImport2.spot_no} does not exist"
                                        )
                                    );
                            }

                            recommendation.SalesArea = recommendation.GroupCode = currentSalesArea.Name;

                            if (campaignsByCustomId.TryGetValue((int)spotImport2.camp_no, out CampaignReducedModel campaign))
                            {
                                recommendation.ExternalCampaignNumber = campaign.ExternalId;
                            }

                            if (demographicsById.TryGetValue(spotImport2.demo_no, out Demographic demographic))
                            {
                                recommendation.Demographic = demographic.ExternalRef;
                            }

                            scheduleIndexKey = GetScheduleIndexKey(recommendation.SalesArea, recommendation.StartDateTime.Date);

                            string breakNotSetReason = "Unknown reason";

                            if (spotImport2 == null)
                            {
                                breakNotSetReason = $"Spot not found in {Path.GetFileName(mainSpotFile)}";
                            }
                            else
                            {
                                if (!scheduleBreaksBySalesAreaAndDate.TryGetValue(scheduleIndexKey, out var scheduleBreakCache))
                                {
                                    breakNotSetReason = string.Format("Schedule document not found (SalesArea={0}, StartDateTime={1})", recommendation.SalesArea, recommendation.StartDateTime.ToString());
                                }
                                else
                                {
                                    recommendation.ExternalBreakNo = !scheduleBreakCache.TryGetValue(spotImport2.break_no, out var @break)
                                        ? recommendation.ExternalBreakNo
                                        : @break.ExternalBreakRef;

                                    // Set reason why ExternalBreakNo isn't set
                                    if (String.IsNullOrEmpty(recommendation.ExternalBreakNo))
                                    {
                                        if (!(scheduleBreakCache?.Any() ?? false))
                                        {
                                            breakNotSetReason = $"Schedule document exists but it contains no breakss (BreakNo={spotImport2.break_no}, Breaks=0)";
                                        }
                                        else if (@break == null)
                                        {
                                            breakNotSetReason = $"Schedule document exists but the break was not found (BreakNo={spotImport2.break_no}, Breaks={scheduleBreakCache.Count})";
                                        }
                                        else    // Break found but ExternalBreakRef is not set
                                        {
                                            breakNotSetReason = $"Schedule document exists and the break was found but the ExternalBreakRef is not set (BreakNo={spotImport2.break_no}, Breaks={scheduleBreakCache.Count})";
                                        }
                                    }
                                }

                                recommendation.ClientPicked = (spotImport2.client_picked.ToUpper() == "Y");
                            }

                            if (String.IsNullOrEmpty(recommendation.ExternalBreakNo))
                            {
                                _audit.Insert(
                                    AuditEventFactory.CreateAuditEventForWarningMessage(
                                        0,
                                        0,
                                        $"Unable to determine External Break No for recommendation for spot no {spotImport2.spot_no}: {breakNotSetReason}"
                                        )
                                    );
                            }

                            output.Recommendations.Add(recommendation);
                        }
                        catch (Exception exception)
                        {
                            // Log exception, continue
                            _audit.Insert(AuditEventFactory.CreateAuditEventForException(0, 0, string.Format("Error generating cancellation recommendation for spot no {0}", spotImport2.spot_no), exception));
                        }
                    }
                }
            }

            _audit.Insert(AuditEventFactory.CreateAuditEventForInformationMessage(0,
                                                                                  0, $"Processed output file {spotFile}"));

            _audit.Insert(AuditEventFactory.CreateAuditEventForInformationMessage(0,
                                                                                  0, $"Processed output file {mainSpotFile}"));

            return(output);
        }