<commit_before>﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Globalization;
using System.Linq;
using System.Threading.Tasks;
using Accidis.Sjoslaget.WebService.Models;
using Accidis.WebServices.Db;
using Dapper;
using NLog;
using KeyValuePair = System.Collections.Generic.KeyValuePair<string, int>;

namespace Accidis.Sjoslaget.WebService.Services
{
	public sealed class ReportingService
	{
		const int HighestAgeTreshold = 85;

		readonly CruiseRepository _cruiseRepository;
		readonly Logger _log = LogManager.GetLogger(typeof(ReportingService).Name);
		readonly ReportRepository _reportRepository;

		public ReportingService(CruiseRepository cruiseRepository, ReportRepository reportRepository)
		{
			_cruiseRepository = cruiseRepository;
			_reportRepository = reportRepository;
		}

		public async Task GenerateReportsAsync()
		{
			try
			{
				var cruise = await _cruiseRepository.GetActiveAsync();
				if(null == cruise || cruise.IsLocked)
					return; // nothing to do

				Report report = await CreateReportForDate(cruise, DateTime.Now);
				await _reportRepository.CreateOrUpdateAsync(report);
			}
			catch(Exception ex)
			{
				_log.Error(ex, "An unexpected exception occurred while generating a report.");
			}
		}

		public async Task<KeyValuePair[]> GetAgeDistribution(SqlConnection db, Cruise cruise)
		{
			var result = await db.QueryAsync<KeyValuePair>("select substring([Dob], 1, 2) [Key], count(*) [Value] from [BookingPax] " +
														   "where [BookingCabinId] in (select [Id] from [BookingCabin] where [CruiseId] = @CruiseId) " +
														   "group by substring([Dob], 1, 2) " +
														   "order by substring([Dob], 1, 2)", new {CruiseId = cruise.Id});
			KeyValuePair[] sourceList = result.ToArray();
			if(!sourceList.Any())
				return new KeyValuePair[0];

			int thisYear = DateTime.Now.Year % 100;
			int maxYear = int.Parse(sourceList.Last().Key, NumberStyles.Integer);

			var buckets = new Dictionary<int, int>();
			for(int year = HighestAgeTreshold; year <= maxYear; year++)
				buckets[year] = 0;

			foreach(KeyValuePair pair in sourceList)
			{
				int year = int.Parse(pair.Key, NumberStyles.None);
				if(year > thisYear && year <= HighestAgeTreshold)
					year = HighestAgeTreshold;

				buckets[year] += pair.Value;
			}

			return buckets.Select(k => new KeyValuePair(
				k.Key == HighestAgeTreshold ? $"≤{HighestAgeTreshold}" : k.Key.ToString(),
				k.Value)).ToArray();
		}

		public async Task<KeyValuePair[]> GetGenders(SqlConnection db, Cruise cruise)
		{
			var result = await db.QueryAsync<KeyValuePair>("select BP.[Gender] [Key], COUNT(*) [Value] " +
														   "from [BookingPax] BP " +
														   "left join [BookingCabin] BC on BP.[BookingCabinId] = BC.[Id] " +
														   "where BC.[CruiseId] = @CruiseId " +
														   "group by BP.[Gender]", new {CruiseId = cruise.Id});
			return result.ToArray();
		}

		public async Task<KeyValuePair[]> GetNumberOfBookingsByPaymentStatus(SqlConnection db, Cruise cruise)
		{<commit_msg>Fix crash in GetAgeDistribution and made a TODO to fix sometime soon.<commit_after>﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Globalization;
using System.Linq;
using System.Threading.Tasks;
using Accidis.Sjoslaget.WebService.Models;
using Accidis.WebServices.Db;
using Dapper;
using NLog;
using KeyValuePair = System.Collections.Generic.KeyValuePair<string, int>;

namespace Accidis.Sjoslaget.WebService.Services
{
	public sealed class ReportingService
	{
		const int HighestAgeTreshold = 85;

		readonly CruiseRepository _cruiseRepository;
		readonly Logger _log = LogManager.GetLogger(typeof(ReportingService).Name);
		readonly ReportRepository _reportRepository;

		public ReportingService(CruiseRepository cruiseRepository, ReportRepository reportRepository)
		{
			_cruiseRepository = cruiseRepository;
			_reportRepository = reportRepository;
		}

		public async Task GenerateReportsAsync()
		{
			try
			{
				var cruise = await _cruiseRepository.GetActiveAsync();
				if(null == cruise || cruise.IsLocked)
					return; // nothing to do

				Report report = await CreateReportForDate(cruise, DateTime.Now);
				await _reportRepository.CreateOrUpdateAsync(report);
			}
			catch(Exception ex)
			{
				_log.Error(ex, "An unexpected exception occurred while generating a report.");
			}
		}

		public async Task<KeyValuePair[]> GetAgeDistribution(SqlConnection db, Cruise cruise)
		{
			// TODO This will no longer work once we start seeing participants born >99 and needs a refactor
			var result = await db.QueryAsync<KeyValuePair>("select substring([Dob], 1, 2) [Key], count(*) [Value] from [BookingPax] " +
														   "where [BookingCabinId] in (select [Id] from [BookingCabin] where [CruiseId] = @CruiseId) " +
														   "group by substring([Dob], 1, 2) " +
														   "order by substring([Dob], 1, 2)", new {CruiseId = cruise.Id});
			KeyValuePair[] sourceList = result.ToArray();
			if(!sourceList.Any())
				return new KeyValuePair[0];

			int thisYear = DateTime.Now.Year % 100;
			int maxYear = int.Parse(sourceList.Last().Key, NumberStyles.Integer);

			var buckets = new Dictionary<int, int>();
			for(int year = HighestAgeTreshold; year <= maxYear; year++)
				buckets[year] = 0;

			foreach(KeyValuePair pair in sourceList)
			{
				int year = int.Parse(pair.Key, NumberStyles.None);

				// Put everyone who is older than HighestAgeTreshold into one bucket
				if(year > thisYear && year <= HighestAgeTreshold)
					year = HighestAgeTreshold;

				// Don't crash if we encounter a year that is out of range
				if(!buckets.ContainsKey(year))
					continue;

				buckets[year] += pair.Value;
			}

			return buckets.Select(k => new KeyValuePair(
				k.Key == HighestAgeTreshold ? $"≤{HighestAgeTreshold}" : k.Key.ToString(),
				k.Value)).ToArray();
		}

		public async Task<KeyValuePair[]> GetGenders(SqlConnection db, Cruise cruise)
		{
			var result = await db.QueryAsync<KeyValuePair>("select BP.[Gender] [Key], COUNT(*) [Value] " +
														   "from [BookingPax] BP " +
														   "left join [BookingCabin] BC on BP.[BookingCabinId] = BC.[Id] " +
														   "where BC.[CruiseId] = @CruiseId " +
														   "group by BP.[Gender]", new {CruiseId = cruise.Id});
			return result.ToArray();
		}

		public async Task<KeyValuePair[]> GetNumberOfBookingsByPaymentStatus(SqlConnection db, Cruise cruise)
		{
{'fraction_non_alphanumeric': 0.11349693251533742, 'fraction_numerical': 0.004294478527607362, 'mean_word_length': 3.766812865497076, 'pattern_counts': {'":': 0, '<': 21, '<?xml version=': 0, '>': 22, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '1853', 'n_tokens_mistral': 2498, 'n_tokens_neox': 1970, 'n_words': 506}