AGEHAB.RelatorioSeadIpasgo/Infrastructure/Database/RelatorioRepository.cs

using System.Data;
using AGEHAB.RelatorioSeadIpasgo.Application.Abstractions;
using AGEHAB.RelatorioSeadIpasgo.Domain.Entities;
using Dapper;
using Microsoft.Data.SqlClient;

namespace AGEHAB.RelatorioSeadIpasgo.Infrastructure.Database;

public class RelatorioRepository : IRelatorioRepository
{
    private readonly string _connectionString;

    public RelatorioRepository(string connectionString) => _connectionString = connectionString;

    public async Task<IReadOnlyList<Competencia>> ObterCompetenciasAsync(CancellationToken cancellationToken)
    {
        using IDbConnection db = new SqlConnection(_connectionString);
        var command = new CommandDefinition("""
            SELECT TOP 12
                HANDLE AS Handle,
                COMPETENCIA AS CompetenciaData
            FROM FP_COMPETENCIAS
            ORDER BY COMPETENCIA DESC
            """, cancellationToken: cancellationToken);
        var resultado = await db.QueryAsync<Competencia>(command);

        return resultado.ToList();
    }

    public async Task<IReadOnlyList<dynamic>> ObterRelatorioAsync(int competenciaHandle, string tiposCalculo, CancellationToken cancellationToken)
    {
        using IDbConnection db = new SqlConnection(_connectionString);
        var parametros = new DynamicParameters();
        parametros.Add("@CompetenciaHandle", competenciaHandle, DbType.Int32);
        parametros.Add("@TiposCalculo", tiposCalculo, DbType.String);

        var command = new CommandDefinition("sp_AgehabRelatorioFinal", parametros, commandType: CommandType.StoredProcedure, cancellationToken: cancellationToken);
        var resultado = await db.QueryAsync(command);
        return resultado.ToList();
    }
}