LogScoring/ResultsQueryHelper.cs - master - AstroJohn/MailRobot

LogScoring/ResultsQueryHelper.cs
1 117 строк35 KB
AstroJohn
18 июн 2026, 13:11
18 июн 2026, 13:1137598af
using DbContext;
using DbContext.Models;
using System;
using System.Collections.Generic;
using System.Linq;
using Common;

namespace LogScoring;

public class ResultsQueryHelper
{
    public CrossCheckDb Context { get; }

    protected IEnumerable<SuperCategoryCountry> Entities { get; }

    protected IEnumerable<SuperCategoryCallsigns> Callsigns { get; }

    protected IEnumerable<SuperCategory> SuperCategories { get; }

    protected IEnumerable<Category> Categories { get; }

    protected IEnumerable<CategoryHierarchy> CategoryHierarchy { get; }

    protected IEnumerable<CategoryBands> CategoryBands { get; }

    protected IEnumerable<Bands> Bands { get; }

    public ResultsQueryHelper(CrossCheckDb context)
    {
        Context = context;

        Entities = Context.SuperCategoryCountry.ToList();
        Callsigns = Context.SuperCategoryCallsigns.ToList();
        SuperCategories = Context.SuperCategory.ToList();
        Bands = Context.Bands.ToList();
        Categories = Context.Category.ToList();
        CategoryBands = Context.CategoryBands.ToList();
        CategoryHierarchy = Context.CategoryHierarchy.ToList();
    }

    public virtual IQueryable<LogApplication> GetAllApplications()
    {
        var query =
            from logFile in Context.LogFilesCategory

            group logFile by new LogApplication
            {
                Call = logFile.Call,
                Category = logFile.Category,
                Location = logFile.Location,
                Month = logFile.Date != null ? logFile.Date.Value.Month : null
            }

            into g

            select g.Key;

        return query;
    }

    public IEnumerable<ResultBase> GetResultsBase(bool need5QsosOn23And70)
    {

        var applications = GetAllApplications();

        var query =
            from logFile in Context.LogFiles

            join logFileCategory in applications
                on logFile.Call equals logFileCategory.Call

            join audio in Context.LogFilesAudio
                on logFile.Call equals audio.Call
            into audioItems
            from audio in audioItems.DefaultIfEmpty()

            join category in Context.Category
                on logFileCategory.Category equals category.CategoryName

            join categoryHierarchy in Context.CategoryHierarchy
                on category.Id equals categoryHierarchy.CategoryId

            join superCategory in Context.SuperCategory
                on categoryHierarchy.SuperCategoryId equals superCategory.Id

            join claimed in Context.LogFilesBandResults
                on logFile.Call equals claimed.Call

            join confirmed in Context.LogFilesBandResults
                on logFile.Call equals confirmed.Call

            from mult1 in Context.LogFilesBandResultsMults.Where
            (
                x =>
                    x.LogFilesBandResultsId == confirmed.Id
                    && x.Type == 1
                    && x.ModeId == superCategory.Mode
            )
            .DefaultIfEmpty()

            from mult2 in Context.LogFilesBandResultsMults.Where
            (
                x =>
                    x.LogFilesBandResultsId == confirmed.Id
                    && x.Type == 2
                    && x.ModeId == superCategory.Mode
            )
            .DefaultIfEmpty()

            from mult3 in Context.LogFilesBandResultsMults.Where
            (
                x =>
                    x.LogFilesBandResultsId == confirmed.Id
                    && x.Type == 3
                    && x.ModeId == superCategory.Mode
            )
            .DefaultIfEmpty()

            from mult1Claimed in Context.LogFilesBandResultsMults.Where
            (
                x =>
                    x.LogFilesBandResultsId == claimed.Id
                    && x.Type == 1
                    && x.ModeId == superCategory.Mode
            )
            .DefaultIfEmpty()

            from mult2Claimed in Context.LogFilesBandResultsMults.Where
            (
                x =>
                    x.LogFilesBandResultsId == claimed.Id
                    && x.Type == 2
                    && x.ModeId == superCategory.Mode
            )
            .DefaultIfEmpty()

            from mult3Claimed in Context.LogFilesBandResultsMults.Where
            (
                x =>
                    x.LogFilesBandResultsId == claimed.Id
                    && x.Type == 3
                    && x.ModeId == superCategory.Mode
            )
            .DefaultIfEmpty()

            where claimed.BandId == 0
            && claimed.ModeId == superCategory.Mode
            && claimed.Type == ResultsType.Claimed
            && confirmed.BandId == 0
            && confirmed.ModeId == superCategory.Mode
            && confirmed.Type == ResultsType.Confirmed
            && (claimed.Category ?? "") == (superCategory.UniteCategories == true ? "" : (logFileCategory.Category ?? ""))
            && (confirmed.Category ?? "") == (superCategory.UniteCategories == true ? "" : (logFileCategory.Category ?? ""))

            orderby
                superCategory.SortOrder,
                category.Description,
                confirmed.Score descending,
                confirmed.Qsos

            select new ResultBase
            {
                SuperCategoryId = superCategory.Id,
                SuperCategory = superCategory.Name,
                Category = superCategory.UniteCategories == true ? null : category.Description,
                Call = logFileCategory.Call,
                Name = logFile.Name,
                CountryId = logFile.CountryId,
                RegionId = logFile.TeamRegionId ?? logFile.RegionId,
                Place = null,
                AudioExists = audio.AudioExists ?? false,
                QsosClaimed = claimed.Qsos,
                PointsClaimed = claimed.Points,
                Mult1Claimed = mult1Claimed.Mult,
                Mult2Claimed = mult2Claimed.Mult,
                Mult3Claimed = mult3Claimed.Mult,
                ScoreClaimed = claimed.Score,
                Qsos = confirmed.Qsos,
                Points = confirmed.Points,
                Mult1 = mult1.Mult,
                Mult2 = mult2.Mult,
                Mult3 = mult3.Mult,
                Score = confirmed.Score,
                Link = null,
                Location = logFileCategory.Location ?? logFile.Location,
                Soapbox = logFile.Soapbox,
                BandId = category.Band,
                CategoryId = superCategory.UniteCategories == true ? null : category.Id,
                Month = category.Month,
                SuperCategorySortOrder = superCategory.SortOrder,
            };

        var results = query.ToList();

        CheckSuperCategorySatisfaction(results);

        var doNotSetPlaceCategoryIds = 
            Context.Category
            .Where(x => x.DoNotSetPlaces == true)
            .Select(x => (int?) x.Id)
            .ToArray();

        if (need5QsosOn23And70)
            PlacesArrangement5Qsos23And70(results, doNotSetPlaceCategoryIds);
        else
            PlacesArrangement(results, doNotSetPlaceCategoryIds);

        return 
            results
            .OrderBy(x => x.SuperCategorySortOrder)
            .ThenBy(x => x.SuperCategoryId)
            .ThenBy(x => x.Category)
            .ThenBy(x => x.Place);
    }



    public void CheckSuperCategorySatisfaction<T>(List<T> results) where T : ICanSuperCategorySatisfactionChecked
    {
        var toDelete = new List<T>();

        var includeAny = Callsigns.Where(x => x.Exclude == false).GroupBy(x => x.SuperCategoryId).Select(x => x.Key).ToArray();

        foreach (var item in results)
        {
            var exclude = Callsigns.FirstOrDefault(x => x.Callsign == item.Call && x.SuperCategoryId == item.SuperCategoryId)?.Exclude;

            if (includeAny.Contains(item.SuperCategoryId) && exclude == null)
            {
                toDelete.Add(item);
                continue;
            }

            if (exclude == true)
            {
                toDelete.Add(item);
                continue;
            }

            if (exclude == false)
            {
                continue;
            }

            var superCategory = SuperCategories.FirstOrDefault(x => x.Id == item.SuperCategoryId);
            var selectCountry = superCategory?.SelectCountry ?? false;
            var selectRegion = superCategory?.SelectRegion ?? false;

            if (!selectCountry && !selectRegion)
                continue;

            if (selectCountry)
            {
                var countriesToIncludeExists = Entities.Any(x => x.SuperCategoryId == item.SuperCategoryId && x.CountryId != null && (x.Exclude ?? false) == false);
                var includedCountryExists = Entities.Any(x => x.SuperCategoryId == item.SuperCategoryId && x.CountryId == item.CountryId && (x.Exclude ?? false) == false);

                if (!includedCountryExists && countriesToIncludeExists)
                {
                    toDelete.Add(item);
                    continue;
                }

                var excludedCountryExists = Entities.Any(x => x.SuperCategoryId == item.SuperCategoryId && x.CountryId == item.CountryId && x.Exclude == true);

                if (excludedCountryExists)
                {
                    toDelete.Add(item);
                    continue;
                }
            }

            if (selectRegion)
            {
                var regionsToIncludeExists = Entities.Any(x => x.SuperCategoryId == item.SuperCategoryId && x.RegionId != null && (x.Exclude ?? false) == false);
                var includedRegionExists = Entities.Any(x => x.SuperCategoryId == item.SuperCategoryId && x.RegionId == item.RegionId && (x.Exclude ?? false) == false);

                if (!includedRegionExists && regionsToIncludeExists)
                {
                    toDelete.Add(item);
                    continue;
                }

                var excludedRegionExists = Entities.Any(x => x.SuperCategoryId == item.SuperCategoryId && x.RegionId == item.RegionId && x.Exclude == true);

                if (excludedRegionExists)
                {
                    toDelete.Add(item);
                    // continue;
                }
            }
        }

        foreach (var item in toDelete)
            results.Remove(item);

    }

    public void PlacesArrangement<T>(IList<T> results, int? [] doNotSetPlaceCategoryIds) where T : class, ICanSetPlace
    {
        var groups =
            from x in results

            where !doNotSetPlaceCategoryIds.Contains(x.CategoryId)

            group x by new
            {
                x.SuperCategoryId,
                x.CategoryId
            }

            into g

            select new
            {
                g.Key.SuperCategoryId,
                g.Key.CategoryId,
                Results = g.OrderByDescending(y => y.Score).ThenBy(y => y.QsosClaimed == 0 ? 1 : 0).ThenBy(y => y.QsosClaimed).ToList()
            };

        foreach (var group in groups)
        {
            var place = 1;
            foreach (var item in group.Results)
            {
                item.Place = place++;
            }
        }
    }

    private int GetNon2MQsosCount(int superCategoryId)
    {
        return SuperCategories.FirstOrDefault(x => x.Id == superCategoryId)?.NeedNon2MQsosCount ?? 0;
    }

    public void PlacesArrangement5Qsos23And70<T>(IList<T> results, int?[] doNotSetPlaceCategoryIds) where T : class, ICanSetPlace
    {
        var non2MQsosStat = GetNon2MQsosStat();
            
        var groups =
            from x in results

            where !doNotSetPlaceCategoryIds.Contains(x.CategoryId)

            join y in non2MQsosStat
                on x.Call equals y.Key
            into yItems
            from y in yItems.DefaultIfEmpty()

            let non2MQsosCount = GetNon2MQsosCount(x.SuperCategoryId.GetValueOrDefault())

            let enoughNon2MQsos = y.Value >= non2MQsosCount  // связей на высоких диапазонах не менее скольки-то

            group new
            {
                Result = x,
                EnoughNon2MQsos = enoughNon2MQsos
            } 
            by new
            {
                x.SuperCategoryId,
                x.CategoryId
            }

            into g

            select new
            {
                g.Key.SuperCategoryId,
                g.Key.CategoryId,
                Results = g.OrderByDescending(y => y.Result.Score).ThenBy(y => y.Result.QsosClaimed).ToList()
            };

        foreach (var group in groups)
        {

            // Сначала первые три места у тех, у кого есть 5 подтвержденных связей на 23 и 70
            var placedResults =
                group.Results
                .Where(x => x.EnoughNon2MQsos) 
                .OrderByDescending(x => x.Result.Score)
                .ThenBy(x => x.Result.QsosClaimed)
                .ToList();

            var place = 1;
            foreach (var item in placedResults)
            {
                item.Result.Place = place++;
                if (place == 4)
                    break;
            }

            // Потом все остальные
            placedResults = 
                group.Results
                .Where(x => x.Result.Place == null)
                .OrderByDescending(x => x.Result.Score)
                .ThenBy(x => x.Result.QsosClaimed)
                .ToList();

            foreach (var item in placedResults)
            {
                item.Result.Place = place++;
            }
        }
    }

    public Dictionary<string, int> GetNon2MQsosStat()
    {
        var query =
            from qso in Context.QSOs

            where qso.ErrType == "+"
            && qso.Band != "144"

            group 1 by qso.Call1

            into g

            select new
            {
                Call = g.Key,
                Non2MQsosCount = g.Count()
            };

        return query.ToDictionary(x => x.Call, y => y.Non2MQsosCount);
    }

    private IEnumerable<EvskRange> GetBaseEvskRanges()
    {
        var query =
            from logFile in Context.LogFiles

            join logFileCategory in GetAllApplications()
                on logFile.Call equals logFileCategory.Call

            join category in Context.Category
                on logFileCategory.Category equals category.CategoryName

            join categoryHierarchy in Context.CategoryHierarchy
                on category.Id equals categoryHierarchy.CategoryId

            join superCategory in Context.SuperCategory
                on categoryHierarchy.SuperCategoryId equals superCategory.Id

            join confirmed in Context.LogFilesBandResults
                on logFile.Call equals confirmed.Call

            where confirmed.BandId == 0
                    && confirmed.ModeId == superCategory.Mode
                    && confirmed.Type == ResultsType.Confirmed
                    && ((confirmed.Category ?? "") == (superCategory.UniteCategories == true ? "" : (logFileCategory.Category ?? "")))
                    && category.DoNotSetPlaces == false
                    && superCategory.CalcRanks == true

            select new EvskRange
            {
                SuperCategoryId = superCategory.Id,
                CountryId = logFile.CountryId,
                RegionId = logFile.TeamRegionId ?? logFile.RegionId,
                CategoryId = superCategory.UniteCategories == true ? null : category.Id,
                Call = logFile.Call,
                EvskPoints = confirmed.Score ?? 0,
                Vip = 0
            };

        var items = query.ToList();

        CheckSuperCategorySatisfaction(items);

        return items;

    }

    public IEnumerable<EvskRange> GetVipEvskRanges(РангСоревнований ранг)
    {
        var items = GetBaseEvskRanges();

        var vip = GetVipEvsk();

        var items2 =
        (
            from x in items

            join y in vip
                on new { x.SuperCategoryId, x.CategoryId } equals new { y.SuperCategoryId, y.CategoryId }

            orderby
                x.SuperCategoryId,
                x.CategoryId,
                x.EvskPoints descending

            select new EvskRange
            {
                SuperCategoryId = x.SuperCategoryId,
                CategoryId = x.CategoryId,
                Call = x.Call,
                EvskPoints = y.Vip == 0 ? 0 : Convert.ToInt32(Math.Floor(Convert.ToDecimal(x.EvskPoints) / y.Vip * 100m)),
                Vip = y.Vip,
                KmsCount = y.KmsCount,
                MsCount = y.MsCount,
                R1Count = y.R1Count,
                TotalCount = y.TotalCount
            }
        )
        .ToList();

        РассчитатьВыполнениеРазрядов(items2, ранг);

        return items2;
    }

    private void РассчитатьВыполнениеРазрядов(IEnumerable<EvskRange> items, РангСоревнований ранг)
    {
        foreach (var item in items)
        {
            if 
            (
                ранг == РангСоревнований.МожноВыполнитьМс
                && item.EvskPoints >= 95 
                && item.MsCount >= 8
            )
                item.Evsk = "МС";
            else if 
            (
                ранг <= РангСоревнований.МожноВыполнитьКмс
                && item.EvskPoints >= 80
                && item.KmsCount >= 5
            )
                item.Evsk = "КМС";
            else if
            (
                ранг <= РангСоревнований.МожноВыполнить1Разряд
                && item.EvskPoints >= 60
                && item.R1Count >= 5
            )
                item.Evsk = "1";
            else if 
            (
                item.EvskPoints >= 50
                && item.TotalCount >= 8
            )
                item.Evsk = "2";
            else if 
            (
                item.EvskPoints >= 40
                && item.TotalCount >= 8
            )
                item.Evsk = "3"; 
            else
                item.Evsk = null;
        }
    }

    public enum РангСоревнований
    {
        МожноВыполнитьМс = 1,
        МожноВыполнитьКмс = 2,
        МожноВыполнить1Разряд = 3
    }

    public IEnumerable<EvskVip> GetVipEvsk()
    {
        var queryKmsCount =
            from op in Context.Operators

            join r in Context.Ranks
                on op.Разряд equals r.Rank
                into rItems
            from r in rItems.DefaultIfEmpty()

            join logFile in Context.LogFiles
                on op.Радиостанция equals logFile.Call

            join logFileCategory in GetAllApplications()
                on logFile.Call equals logFileCategory.Call

            join cat in Context.Category
                on logFileCategory.Category equals cat.CategoryName

            join catH in Context.CategoryHierarchy
                on cat.Id equals catH.CategoryId

            join sCat in Context.SuperCategory
                on catH.SuperCategoryId equals sCat.Id

            where sCat.CalcRanks == true
                    && (op.Тренер == null || op.Тренер == "")

            select new CanSuperCategorySatisfactionCheckedBase
            {
                SuperCategoryId = sCat.Id,
                CategoryId      = sCat.UniteCategories == true ? null : cat.Id,
                Call            = logFile.Call,
                CountryId       = logFile.CountryId,
                RegionId        = logFile.TeamRegionId ?? logFile.RegionId,
                RankId          = r.Id
            };

        var itemsKmsCount = queryKmsCount.ToList();

        CheckSuperCategorySatisfaction(itemsKmsCount);

        var kmsCount =
        (
            from x in itemsKmsCount
            group x by new { x.SuperCategoryId, x.CategoryId }
            into g
            select new
            {
                g.Key.SuperCategoryId,
                g.Key.CategoryId,
                KmsCount        = g.Count(x => x.RankId <= Ranks.Kms),
                MsCount         = g.Count(x => x.RankId <= Ranks.Ms),
                R1Count         = g.Count(x => x.RankId <= Ranks.R1),
                TotalCount      = g.Count()
            }
        )
        .ToList();

        var queryVip =
            from logFile in Context.LogFiles

            join logFileCategory in GetAllApplications()
                on logFile.Call equals logFileCategory.Call

            join cat in Context.Category
                on logFileCategory.Category equals cat.CategoryName

            join catH in Context.CategoryHierarchy
                on cat.Id equals catH.CategoryId

            join sCat in Context.SuperCategory
                on catH.SuperCategoryId equals sCat.Id

            join confirmed in Context.LogFilesBandResults
                on logFile.Call equals confirmed.Call

            join claimed in Context.LogFilesBandResults
                on logFile.Call equals claimed.Call

            where confirmed.BandId == 0
            && ((confirmed.Category ?? "") == (sCat.UniteCategories == true ? "" : (logFileCategory.Category ?? "")))
            && confirmed.ModeId == sCat.Mode
            && confirmed.Type == ResultsType.Confirmed
            && claimed.BandId == 0
            && ((claimed.Category ?? "") == (sCat.UniteCategories == true ? "" : (logFileCategory.Category ?? "")))
            && claimed.ModeId == sCat.Mode
            && claimed.Type == ResultsType.Claimed

            select new CanSetPlaceBase
            {
                SuperCategoryId = sCat.Id,
                CategoryId = sCat.UniteCategories == true ? null : cat.Id,
                CountryId = logFile.CountryId,
                RegionId = logFile.TeamRegionId ?? logFile.RegionId,
                Call = logFile.Call,
                Score = confirmed.Score,
                QsosClaimed = claimed.Qsos,
                Place = null
            };

        var itemsVip = queryVip.ToList();

        CheckSuperCategorySatisfaction(itemsVip);
        PlacesArrangement(itemsVip, []);

        var itemsVipGrouped =
            from x in itemsVip
            from y in kmsCount.Where(z => z.SuperCategoryId == x.SuperCategoryId && z.CategoryId == x.CategoryId).DefaultIfEmpty()

            group new
            {
                x.SuperCategoryId,
                x.Score,
                x.Place
            } 
            by new
            {
                x.SuperCategoryId,
                x.CategoryId,
                KmsCount = y?.KmsCount ?? 0,
                MsCount = y?.MsCount ?? 0,
                R1Count = y?.R1Count ?? 0,
                TotalCount = y?.TotalCount ?? 0
            }

            into g

            let stnCount = g.Count()
            let count234 = g.Count(z => new int?[] { 2, 3, 4 }.Contains(z.Place))
            let sum234 = g.Where(z => new int?[] { 2, 3, 4 }.Contains(z.Place)).Sum(z => z.Score ?? 0)

            orderby
                g.Key.SuperCategoryId,
                g.Key.CategoryId

            select new EvskVip
            {
                SuperCategoryId = g.Key.SuperCategoryId,
                CategoryId = g.Key.CategoryId,
                Vip = stnCount == 0 || count234 == 0 ? 0 : Convert.ToInt32(Math.Floor(sum234 / Convert.ToDecimal(count234))),
                KmsCount = g.Key.KmsCount,
                MsCount = g.Key.MsCount,
                R1Count = g.Key.R1Count,
                TotalCount = g.Key.TotalCount
            };

        return itemsVipGrouped;
    }

    public IQueryable<BandSummary> GetBandSummary()
    {
        var query =
            from lfc in GetAllApplications()

            join claimed in Context.LogFilesBandResults
                on new { lfc.Call, lfc.Category } equals new { claimed.Call, claimed.Category }

            join band in Context.Bands
                on claimed.BandId equals band.Id

            join confirmed in Context.LogFilesBandResults
                on new { lfc.Call, lfc.Category, claimed.BandId } equals new { confirmed.Call, confirmed.Category, confirmed.BandId }
            into confirmedItems
            from confirmed in confirmedItems.DefaultIfEmpty()

            join claimedMult1 in Context.LogFilesBandResultsMults
                on new { LogFilesBandResultsId = claimed.Id, Type = 1, ModeId = 0 } equals new { claimedMult1.LogFilesBandResultsId, claimedMult1.Type, claimedMult1.ModeId }
            into claimedMult1Items
            from claimedMult1 in claimedMult1Items.DefaultIfEmpty()

            join confirmedMult1 in Context.LogFilesBandResultsMults
                on new { LogFilesBandResultsId = confirmed.Id, Type = 1, ModeId = 0 } equals new { confirmedMult1.LogFilesBandResultsId, confirmedMult1.Type, confirmedMult1.ModeId }
            into confirmedMult1Items
            from confirmedMult1 in confirmedMult1Items.DefaultIfEmpty()

            where claimed.Type == ResultsType.Claimed
            && claimed.ModeId == 0
            && confirmed.Type == ResultsType.Confirmed
            && confirmed.ModeId == 0
            && band.Enabled
            && claimed.Qsos > 0

            select new BandSummary
            {
                Category = lfc.Category,
                Call = lfc.Call,
                BandId = band.Id,
                Band = band.Band,
                Place = null,
                ClaimedQsos = claimed.Qsos,
                ClaimedPoints = claimed.Points,
                ClaimedMult = claimedMult1.Mult,
                ConfirmedQsos = confirmed.Qsos,
                ConfirmedPoints = confirmed.Points,
                ConfirmedMult = confirmedMult1.Mult
            };

        return query;
    }

    public IEnumerable<BandCount> GetBandCount(IEnumerable<BandSummary> bandSummary)
    {
        var query =
            from x in bandSummary

            group x by new
            {
                x.Call,
                x.Category
            }
            into g

            select new BandCount
            {
                Call = g.Key.Call,
                Category = g.Key.Category,
                Count = g.Count()
            };

        return query;
    }

    public IEnumerable<Odx> GetOdxByBand()
    {
        var query =
            from qso in Context.QSOs

            join pts in Context.QSOsPoints
                on qso.Id equals pts.QsoId

            join bnd in Context.Bands
                on qso.Band equals bnd.Band

            where qso.ErrType == "+"
            && pts.ResType == ResultsType.Confirmed

            group new {pts.Points, bnd.BandCoef, qso.Call2, qso.NrRcvd} 
            by new { qso.Call1, BandId = bnd.Id, qso.Date.Value.Month }
            into g

            let first = g.OrderByDescending(x => x.Points).FirstOrDefault()

            select new
            {
                Call = g.Key.Call1,
                g.Key.Month,
                g.Key.BandId,
                OdxCall = first.Call2,
                first.NrRcvd,
                first.Points,
                first.BandCoef
            };

        var odx = query.ToArray();

        var odx2 =
            from x in odx

            select new Odx
            {
                Call = x.Call,
                BandId = x.BandId,
                Month = x.Month,
                OdxCall = x.OdxCall,
                OdxLoc = x.NrRcvd.Right(6),
                Distance = Convert.ToInt32(Math.Round(Convert.ToDecimal(x.Points) / Convert.ToDecimal(x.BandCoef), MidpointRounding.AwayFromZero))
            };

        return odx2;
    }

    private decimal GetDistanceEvskBandCoef(int bandId)
    {           
        switch (bandId)
        {
            case 7:
                return 1;
            case 8:
                return 1.5m;
            case 9:
                return 2;
            default:
                var band = Bands.FirstOrDefault(x => x.Id == bandId);
                return (band?.Freq ?? 0) >= 2000000 ? 3 : 0;
        }
    }

    public IEnumerable<EvskRange> GetDistanceEvskRanges()
    {
        var items = GetBaseEvskRanges();

        var query =
            from qso in Context.QSOs

            join pts in Context.QSOsPoints
                on qso.Id equals pts.QsoId

            join bnd in Context.Bands
                on qso.Band equals bnd.Band

            where qso.ErrType == "+"
            && pts.ResType == ResultsType.Confirmed

            select new
            {
                qso.Call1,
                qso.Call2,
                qso.NrRcvd,
                BandId = bnd.Id,
                pts.Points,
                bnd.BandCoef
            };

        var points = query.ToArray();

        var points2 =
            from pts in points

            group pts by pts.Call1

            into g

            select new
            {
                Call = g.Key,
                Points = 
                    g.Select
                    (
                        x =>
                        {
                            var distance   = Convert.ToDecimal(x.Points) / Convert.ToDecimal(x.BandCoef);
                            var coef       = GetDistanceEvskBandCoef(x.BandId);
                            var evskPoints = distance * coef;
                            var band       = Bands.FirstOrDefault(y => y.Id == x.BandId)?.Band;
                            var result = new
                            {
                                EvskPoints = evskPoints,
                                Info = $"{band}: {x.Call2} ({distance:N0} км, {coef}x)"
                            };
                            return result;
                        }
                    )
                    .OrderByDescending(x => x.EvskPoints)
                    .Take(10)
            };

        var points3 =
            from pts in points2

            let evskPoints = Convert.ToInt32(Math.Round(pts.Points.Sum(x => x.EvskPoints), MidpointRounding.AwayFromZero))

            select new
            {
                pts.Call,
                EvskPoints = evskPoints,
                Info = string.Join("<br>\n", pts.Points.Select(x => x.Info))
            };


        var itemsCount = GetVipEvsk();

        var items2 =
        (
            from x in items

            join y in itemsCount
                on new {x.SuperCategoryId, x.CategoryId} equals new {y.SuperCategoryId, y.CategoryId}

            join z in points3
                on x.Call equals z.Call

            orderby
                x.SuperCategoryId,
                x.CategoryId,
                x.EvskPoints descending

            select new EvskRange
            {
                SuperCategoryId = x.SuperCategoryId,
                CategoryId = x.CategoryId,
                Call = x.Call,
                EvskPoints = z.EvskPoints,
                Vip = y.Vip,
                KmsCount = y.KmsCount,
                MsCount = y.MsCount,
                R1Count = y.R1Count,
                TotalCount = y.TotalCount,
                Info = z.Info
            }
        )
        .ToList();

        foreach (var item in items2)
        {
            if (item.TotalCount < 8)
                continue;

            if (item.EvskPoints >= 6000)
                item.Evsk = item.KmsCount >= 5 ? "КМС" : "1";
            else if (item.EvskPoints >= 3000)
                item.Evsk = "1";
            else if (item.EvskPoints >= 2000)
                item.Evsk = "2";
            else if (item.EvskPoints >= 1000)
                item.Evsk = "3";
            else
                item.Evsk = null;
        }

        return items2;
    }
}

#region PMs

public class ResultBase : ICanSuperCategorySatisfactionChecked, ICanSetPlace
{
    public int? SuperCategoryId { get; set; }
    public string SuperCategory { get; set; }
    public int? CategoryId { get; set; }
    public string Category { get; set; }
    public string Call { get; set; }
    public string Name { get; set; }
    public string CountryId { get; set; }
    public string RegionId { get; set; }
    public int? Place { get; set; }
    public bool AudioExists { get; set; }
    public int? QsosClaimed { get; set; }
    public int? PointsClaimed { get; set; }
    public int? Mult1Claimed { get; set; }
    public int? Mult2Claimed { get; set; }
    public int? Mult3Claimed { get; set; }
    public int? ScoreClaimed { get; set; }
    public int? Qsos { get; set; }
    public int? Points { get; set; }
    public int? Mult1 { get; set; }
    public int? Mult2 { get; set; }
    public int? Mult3 { get; set; }
    public int? Score { get; set; }
    public string Link { get; set; }
    public string Location { get; set; }
    public string Soapbox { get; set; }
    public int? BandId { get; set; }
    public int? Month { get; set; }
    public int? SuperCategorySortOrder { get; set; }
}

public interface ICanSuperCategorySatisfactionChecked
{
    int? SuperCategoryId { get; set; }
    string Call { get; set; }
    string CountryId { get; set; }
    string RegionId { get; set; }
}

public class CanSuperCategorySatisfactionCheckedBase : ICanSuperCategorySatisfactionChecked
{
    public int? SuperCategoryId { get; set; }
    public int? CategoryId { get; set; }
    public string Call { get; set; }
    public string CountryId { get; set; }
    public string RegionId { get; set; }
    public int? RankId { get; set; }
}

public interface ICanSetPlace
{
    int? SuperCategoryId { get; set; }
    int? CategoryId { get; set; }
    string Call { get; set; }
    int? QsosClaimed { get; set; }
    int? Score { get; set; }
    int? Place { get; set; }
}

public class CanSetPlaceBase : ICanSetPlace, ICanSuperCategorySatisfactionChecked
{
    public int? SuperCategoryId { get; set; }
    public int? CategoryId { get; set; }
    public string CountryId { get; set; }
    public string RegionId { get; set; }
    public string Call { get; set; }
    public int? QsosClaimed { get; set; }
    public int? Score { get; set; }
    public int? Place { get; set; }
}

public class LogApplication
{
    public string Call { get; set; }
    public string Category { get; set; }
    public string Location { get; set; }
    public int? Month { get; set; }
}

public class EvskRange : ICanSuperCategorySatisfactionChecked
{
    public int? SuperCategoryId { get; set; }
    public int? CategoryId { get; set; }
    public string Call { get; set; }
    public string CountryId { get; set; }
    public string RegionId { get; set; }
    public int EvskPoints { get; set; }
    public string Evsk { get; set; }
    public int Vip { get; set; }
    public int KmsCount { get; set; }
    public int MsCount { get; set; }
    public int R1Count { get; set; }
    public int TotalCount { get; set; }
    public string Info { get; set; }
}

public class EvskVip
{
    public int? SuperCategoryId { get; set; }
    public int? CategoryId { get; set; }
    public int Vip { get; set; }
    public int KmsCount { get; set; }
    public int MsCount { get; set; }
    public int R1Count { get; set; }
    public int TotalCount { get; set; }
}

public class Odx
{
    public string Call { get; set; }
    public int? BandId { get; set; }
    public int? Month { get; set; }
    public string OdxCall { get; set; }
    public string OdxLoc { get; set; }
    public int Distance { get; set; }
}

public class BandSummary
{
    public string Category { get; set; }
    public string Call { get; set; }
    public int BandId { get; set; }
    public string Band { get; set; }
    public int? Place { get; set; }
    public int? ClaimedQsos { get; set; }
    public int? ClaimedPoints { get; set; }
    public int? ClaimedMult { get; set; }
    public int? ConfirmedQsos { get; set; }
    public int? ConfirmedPoints { get; set; }
    public int? ConfirmedMult { get; set; }
}

public class BandCount
{
    public string Call { get; set; }
    public string Category { get; set; }
    public int Count { get; set; }
}

#endregion