Gready_Poang/Gready_Poang.DataLayer/DataClasses/CombinedRepository.cs

using Gready_Poang.DataLayer.Database;
using Gready_Poang.EntityLayer;
using Microsoft.EntityFrameworkCore;

namespace Gready_Poang.DataLayer;

public class CombinedRepository : ICombinedRepository
{
    private readonly DataContext _context;

    public CombinedRepository(DataContext context)
    {
        _context = context;
    }


    public IEnumerable<RoundBuilderElement> roundBuilderElements()
    {
        var latestGamePoints = _context.GamePoints
            .AsEnumerable()
            .GroupBy(gp => new { gp.GameRoundId, gp.ParticipantId })
            .Select(g => g.OrderByDescending(gp => gp.GamePointId).First())
            .ToList();

        var gamerounds = _context.GameRounds.AsEnumerable();
        var participants = _context.Participants.AsEnumerable();

        var result = (from gameRound in gamerounds
                      join gamePoint in latestGamePoints on gameRound.GameRoundId equals gamePoint.GameRoundId
                      join participant in participants on gamePoint.ParticipantId equals participant.ParticipantId
                      orderby gameRound.GameRoundStartDate descending, participant.LastName, participant.FirstName, gamePoint.GameRoundRegNr
                      select new RoundBuilderElement
                      {
                          ParticipantId = participant.ParticipantId,
                          ParticipantName = participant.LastNameFirstName,
                          GamePointId = gamePoint.GamePointId,
                          GameRoundRegNr = gamePoint.GameRoundRegNr,
                          GameRegPoints = gamePoint.GameRegPoints,
                          GameRoundId = gameRound.GameRoundId,
                          GameRoundStartDate = gameRound.GameRoundStartDate,
                          Status = gameRound.GameStatus
                      }).ToList();
        return result;

    }

    public IEnumerable<RoundBuilderElement> roundBuilderElementsDb()
    {
        var result = _context.RoundBuilderElements
            .FromSqlRaw(@"
                SELECT
                    gp.GamePointId,
                    gp.GameRoundId,
                    gp.GameRoundRegNr,
                    latest.totPoints as GameRegPoints,
                    gr.GameRoundStartDate,
                    gr.GameStatus AS Status,
                    p.ParticipantId,
                    (p.LastName || ' ' || p.FirstName) AS ParticipantName
                FROM GamePoints gp
                INNER JOIN (
                    SELECT ParticipantId, GameRoundId, MAX(GamePointId) AS MaxGamePointId , sum(GameRegPoints) AS totPoints
                    FROM GamePoints
                    GROUP BY ParticipantId, GameRoundId
                ) latest ON gp.GamePointId = latest.MaxGamePointId
                INNER JOIN GameRounds gr ON gp.GameRoundId = gr.GameRoundId
                INNER JOIN Participants p ON gp.ParticipantId = p.ParticipantId
                ORDER BY gr.GameRoundStartDate DESC, p.LastName, p.FirstName, gp.GameRoundRegNr
            ")
            .ToList();
        return result;
    }

    public IEnumerable<RoundBuilderElement> roundBuilderElementsDbById(int GameId)
    {
        var result = _context.RoundBuilderElements
            .FromSql($@"
                SELECT
                    gp.GamePointId,
                    gp.GameRoundId,
                    gp.GameRoundRegNr,
                    gp.GameRegPoints,
                    gr.GameRoundStartDate,
                    gr.GameStatus AS Status,
                    p.ParticipantId,
                    (p.LastName || ' ' || p.FirstName) AS ParticipantName
                FROM GamePoints gp
                INNER JOIN (
                    SELECT ParticipantId, GameRoundId, MAX(GamePointId) AS MaxGamePointId
                    FROM GamePoints
                    GROUP BY ParticipantId, GameRoundId
                ) latest ON gp.GamePointId = latest.MaxGamePointId
                INNER JOIN GameRounds gr ON gp.GameRoundId = gr.GameRoundId
                INNER JOIN Participants p ON gp.ParticipantId = p.ParticipantId
                WHERE gp.GameRoundId = {GameId.ToString()}
                ORDER BY gr.GameRoundStartDate DESC, p.LastName, p.FirstName, gp.GameRoundRegNr
            ")
            .ToList();
        return result;
    }

    public IEnumerable<RoundBuilderElement> roundBuilderElementsTotal()
    {

        var result = (from gameRound in _context.GameRounds
                      join gamePoint in _context.GamePoints on gameRound.GameRoundId equals gamePoint.GameRoundId
                      join participant in _context.Participants on gamePoint.ParticipantId equals participant.ParticipantId
                      orderby gameRound.GameRoundStartDate descending, participant.LastName, participant.FirstName, gamePoint.GameRoundRegNr
                      select new RoundBuilderElement
                      {
                          ParticipantId = participant.ParticipantId,
                          ParticipantName = participant.LastNameFirstName,
                          GamePointId = gamePoint.GamePointId,
                          GameRoundRegNr = gamePoint.GameRoundRegNr,
                          GameRegPoints = gamePoint.GameRegPoints,
                          GameRoundId = gameRound.GameRoundId,
                          GameRoundStartDate = gameRound.GameRoundStartDate,
                          Status = gameRound.GameStatus
                      }).ToList();

        return result;
    }

    public IEnumerable<RoundBuilderElement> roundBuilderElementsTotalById(int roundId)
    {

        var result = (from gameRound in _context.GameRounds
                      join gamePoint in _context.GamePoints on gameRound.GameRoundId equals gamePoint.GameRoundId
                      join participant in _context.Participants on gamePoint.ParticipantId equals participant.ParticipantId
                      where gameRound.GameRoundId == roundId
                      orderby gameRound.GameRoundStartDate descending, participant.LastName, participant.FirstName, gamePoint.GameRoundRegNr
                      select new RoundBuilderElement
                      {
                          ParticipantId = participant.ParticipantId,
                          ParticipantName = participant.LastNameFirstName,
                          GamePointId = gamePoint.GamePointId,
                          GameRoundRegNr = gamePoint.GameRoundRegNr,
                          GameRegPoints = gamePoint.GameRegPoints,
                          GameRoundId = gameRound.GameRoundId,
                          GameRoundStartDate = gameRound.GameRoundStartDate,
                          Status = gameRound.GameStatus
                      }).ToList();

        return result;
    }


}