381 lines
14 KiB
C#
381 lines
14 KiB
C#
using System.Globalization;
|
|
using System.Security.Claims;
|
|
using System.Text;
|
|
using System.Text.Json;
|
|
using line_gestao_api.Data;
|
|
using line_gestao_api.Models;
|
|
using Microsoft.AspNetCore.Http;
|
|
using Microsoft.EntityFrameworkCore;
|
|
|
|
namespace line_gestao_api.Services;
|
|
|
|
public sealed class DashboardKpiSnapshotService
|
|
{
|
|
private const string SnapshotEntityName = "DashboardKpiSnapshot";
|
|
|
|
private static readonly JsonSerializerOptions JsonOptions = new(JsonSerializerDefaults.Web);
|
|
|
|
private readonly AppDbContext _db;
|
|
private readonly ITenantProvider _tenantProvider;
|
|
|
|
public DashboardKpiSnapshotService(AppDbContext db, ITenantProvider tenantProvider)
|
|
{
|
|
_db = db;
|
|
_tenantProvider = tenantProvider;
|
|
}
|
|
|
|
public async Task CaptureAfterSpreadsheetImportAsync(
|
|
ClaimsPrincipal user,
|
|
HttpContext httpContext,
|
|
CancellationToken cancellationToken = default)
|
|
{
|
|
var tenantId = _tenantProvider.TenantId;
|
|
if (!tenantId.HasValue)
|
|
{
|
|
return;
|
|
}
|
|
|
|
var userId = TryParseUserId(user);
|
|
var userName = user.FindFirst("name")?.Value
|
|
?? user.FindFirst(ClaimTypes.Name)?.Value
|
|
?? user.Identity?.Name
|
|
?? "USUARIO";
|
|
var userEmail = user.FindFirst(ClaimTypes.Email)?.Value
|
|
?? user.FindFirst("email")?.Value;
|
|
|
|
foreach (var scope in EnumerateScopes())
|
|
{
|
|
var snapshot = await BuildSnapshotAsync(scope.Operadora, cancellationToken);
|
|
|
|
_db.AuditLogs.Add(new AuditLog
|
|
{
|
|
TenantId = tenantId.Value,
|
|
ActorUserId = userId,
|
|
ActorTenantId = tenantId.Value,
|
|
TargetTenantId = tenantId.Value,
|
|
OccurredAtUtc = DateTime.UtcNow,
|
|
UserId = userId,
|
|
UserName = userName,
|
|
UserEmail = userEmail,
|
|
Action = "SNAPSHOT",
|
|
Page = AuditLogBuilder.SpreadsheetImportPageName,
|
|
EntityName = SnapshotEntityName,
|
|
EntityId = snapshot.Scope,
|
|
EntityLabel = $"Snapshot Dashboard {snapshot.Scope}",
|
|
ChangesJson = "[]",
|
|
MetadataJson = JsonSerializer.Serialize(snapshot, JsonOptions),
|
|
RequestPath = httpContext.Request.Path.Value,
|
|
RequestMethod = httpContext.Request.Method,
|
|
IpAddress = httpContext.Connection.RemoteIpAddress?.ToString()
|
|
});
|
|
}
|
|
|
|
await _db.SaveChangesAsync(cancellationToken);
|
|
}
|
|
|
|
public async Task<Dictionary<string, string>> GetTrendMapAsync(
|
|
string? operadora = null,
|
|
CancellationToken cancellationToken = default)
|
|
{
|
|
var scope = NormalizeScope(operadora);
|
|
var logs = await _db.AuditLogs
|
|
.AsNoTracking()
|
|
.Where(x =>
|
|
x.Page == AuditLogBuilder.SpreadsheetImportPageName &&
|
|
x.EntityName == SnapshotEntityName &&
|
|
x.EntityId == scope)
|
|
.OrderByDescending(x => x.OccurredAtUtc)
|
|
.Take(8)
|
|
.Select(x => new { x.OccurredAtUtc, x.MetadataJson })
|
|
.ToListAsync(cancellationToken);
|
|
|
|
if (logs.Count < 2)
|
|
{
|
|
return new Dictionary<string, string>(StringComparer.OrdinalIgnoreCase);
|
|
}
|
|
|
|
var latest = DeserializeSnapshot(logs[0].MetadataJson);
|
|
if (latest == null)
|
|
{
|
|
return new Dictionary<string, string>(StringComparer.OrdinalIgnoreCase);
|
|
}
|
|
|
|
var baselineThreshold = logs[0].OccurredAtUtc.AddHours(-24);
|
|
var previous = logs
|
|
.Skip(1)
|
|
.Select(x => DeserializeSnapshot(x.MetadataJson))
|
|
.FirstOrDefault(x => x != null && x.CapturedAtUtc >= baselineThreshold);
|
|
|
|
if (previous == null)
|
|
{
|
|
return new Dictionary<string, string>(StringComparer.OrdinalIgnoreCase);
|
|
}
|
|
|
|
var keys = latest.Metrics.Keys
|
|
.Concat(previous.Metrics.Keys)
|
|
.Distinct(StringComparer.OrdinalIgnoreCase);
|
|
|
|
var result = new Dictionary<string, string>(StringComparer.OrdinalIgnoreCase);
|
|
foreach (var key in keys)
|
|
{
|
|
latest.Metrics.TryGetValue(key, out var currentValue);
|
|
previous.Metrics.TryGetValue(key, out var previousValue);
|
|
result[key] = currentValue > previousValue
|
|
? "up"
|
|
: currentValue < previousValue
|
|
? "down"
|
|
: "stable";
|
|
}
|
|
|
|
return result;
|
|
}
|
|
|
|
private async Task<DashboardKpiSnapshotPayload> BuildSnapshotAsync(
|
|
string? operadora,
|
|
CancellationToken cancellationToken)
|
|
{
|
|
var todayUtcStart = DateTime.SpecifyKind(DateTime.UtcNow.Date, DateTimeKind.Utc);
|
|
var tomorrowUtcStart = todayUtcStart.AddDays(1);
|
|
var last30UtcStart = todayUtcStart.AddDays(-30);
|
|
var limit30ExclusiveUtcStart = todayUtcStart.AddDays(31);
|
|
|
|
var qLines = OperadoraContaResolver.ApplyOperadoraFilter(_db.MobileLines.AsNoTracking(), operadora);
|
|
var qReserva = ApplyReservaContextFilter(qLines);
|
|
var qOperacionais = ExcludeReservaContext(qLines);
|
|
var qOperacionaisWithClient = qOperacionais.Where(x => x.Cliente != null && x.Cliente != "");
|
|
var qNonReserva = ExcludeReservaContext(qLines);
|
|
|
|
var totalLinhas = await qLines.CountAsync(cancellationToken);
|
|
var linhasAtivas = await qOperacionais.CountAsync(x =>
|
|
EF.Functions.ILike((x.Status ?? "").Trim(), "%ativo%"), cancellationToken);
|
|
var linhasBloqueadas = await qOperacionaisWithClient.CountAsync(x =>
|
|
EF.Functions.ILike((x.Status ?? "").Trim(), "%bloque%") ||
|
|
EF.Functions.ILike((x.Status ?? "").Trim(), "%perda%") ||
|
|
EF.Functions.ILike((x.Status ?? "").Trim(), "%roubo%"), cancellationToken);
|
|
var linhasReserva = await qReserva.CountAsync(cancellationToken);
|
|
|
|
var franquiaVivoTotal = await qLines.SumAsync(x => x.FranquiaVivo ?? 0m, cancellationToken);
|
|
var franquiaLineTotal = await qLines.SumAsync(x => x.FranquiaLine ?? 0m, cancellationToken);
|
|
|
|
var vigVencidos = await _db.VigenciaLines.AsNoTracking()
|
|
.CountAsync(x =>
|
|
x.DtTerminoFidelizacao != null &&
|
|
x.DtTerminoFidelizacao.Value < todayUtcStart, cancellationToken);
|
|
|
|
var vigencia30 = await _db.VigenciaLines.AsNoTracking()
|
|
.CountAsync(x =>
|
|
x.DtTerminoFidelizacao != null &&
|
|
x.DtTerminoFidelizacao.Value >= todayUtcStart &&
|
|
x.DtTerminoFidelizacao.Value < limit30ExclusiveUtcStart, cancellationToken);
|
|
|
|
var mureg30 = await _db.MuregLines.AsNoTracking()
|
|
.CountAsync(x =>
|
|
x.DataDaMureg != null &&
|
|
x.DataDaMureg.Value >= last30UtcStart &&
|
|
x.DataDaMureg.Value < tomorrowUtcStart, cancellationToken);
|
|
|
|
var troca30 = await _db.TrocaNumeroLines.AsNoTracking()
|
|
.CountAsync(x =>
|
|
x.DataTroca != null &&
|
|
x.DataTroca.Value >= last30UtcStart &&
|
|
x.DataTroca.Value < tomorrowUtcStart, cancellationToken);
|
|
|
|
var qLineItems = qLines
|
|
.Where(x => x.Item > 0)
|
|
.Select(x => x.Item);
|
|
var qLineNumbers = qLines
|
|
.Where(x => x.Linha != null && x.Linha != "")
|
|
.Select(x => x.Linha!);
|
|
|
|
var qUserData = _db.UserDatas.AsNoTracking()
|
|
.Where(x =>
|
|
(x.Item > 0 && qLineItems.Contains(x.Item)) ||
|
|
(x.Linha != null && x.Linha != "" && qLineNumbers.Contains(x.Linha)));
|
|
|
|
var cadastrosTotal = await qUserData.CountAsync(cancellationToken);
|
|
|
|
var travelCom = await qLines.CountAsync(x => (x.VivoTravelMundo ?? 0m) > 0m, cancellationToken);
|
|
var adicionalPago = await qLines.CountAsync(x =>
|
|
(x.GestaoVozDados ?? 0m) > 0m ||
|
|
(x.Skeelo ?? 0m) > 0m ||
|
|
(x.VivoNewsPlus ?? 0m) > 0m ||
|
|
(x.VivoTravelMundo ?? 0m) > 0m ||
|
|
(x.VivoSync ?? 0m) > 0m ||
|
|
(x.VivoGestaoDispositivo ?? 0m) > 0m, cancellationToken);
|
|
|
|
var planosContratados = await qNonReserva
|
|
.Where(x => x.PlanoContrato != null && x.PlanoContrato != "")
|
|
.Select(x => (x.PlanoContrato ?? "").Trim())
|
|
.Distinct()
|
|
.CountAsync(cancellationToken);
|
|
|
|
var usuariosRaw = await qNonReserva
|
|
.Where(x => x.Usuario != null && x.Usuario != "")
|
|
.Select(x => new
|
|
{
|
|
Usuario = (x.Usuario ?? "").Trim(),
|
|
Status = (x.Status ?? "").Trim()
|
|
})
|
|
.ToListAsync(cancellationToken);
|
|
|
|
var usuariosComLinha = usuariosRaw
|
|
.Where(x => ShouldIncludeUsuario(x.Usuario, x.Status))
|
|
.Select(x => x.Usuario)
|
|
.Distinct(StringComparer.OrdinalIgnoreCase)
|
|
.Count();
|
|
|
|
return new DashboardKpiSnapshotPayload
|
|
{
|
|
Scope = NormalizeScope(operadora),
|
|
CapturedAtUtc = DateTime.UtcNow,
|
|
Metrics = new Dictionary<string, decimal>(StringComparer.OrdinalIgnoreCase)
|
|
{
|
|
["linhas_total"] = totalLinhas,
|
|
["linhas_ativas"] = linhasAtivas,
|
|
["linhas_bloqueadas"] = linhasBloqueadas,
|
|
["linhas_reserva"] = linhasReserva,
|
|
["franquia_vivo_total"] = franquiaVivoTotal,
|
|
["franquia_line_total"] = franquiaLineTotal,
|
|
["vig_vencidos"] = vigVencidos,
|
|
["vig_30"] = vigencia30,
|
|
["mureg_30"] = mureg30,
|
|
["troca_30"] = troca30,
|
|
["cadastros_total"] = cadastrosTotal,
|
|
["travel_com"] = travelCom,
|
|
["adicional_pago"] = adicionalPago,
|
|
["planos_contratados"] = planosContratados,
|
|
["usuarios_com_linha"] = usuariosComLinha
|
|
}
|
|
};
|
|
}
|
|
|
|
private static Guid? TryParseUserId(ClaimsPrincipal user)
|
|
{
|
|
var claim = user.FindFirst(ClaimTypes.NameIdentifier)?.Value;
|
|
return Guid.TryParse(claim, out var userId) ? userId : null;
|
|
}
|
|
|
|
private static IEnumerable<(string Scope, string? Operadora)> EnumerateScopes()
|
|
{
|
|
yield return ("TODOS", null);
|
|
yield return ("VIVO", "VIVO");
|
|
yield return ("CLARO", "CLARO");
|
|
yield return ("TIM", "TIM");
|
|
}
|
|
|
|
private static string NormalizeScope(string? operadora)
|
|
{
|
|
var token = NormalizeToken(operadora);
|
|
if (string.IsNullOrWhiteSpace(token))
|
|
{
|
|
return "TODOS";
|
|
}
|
|
|
|
return token;
|
|
}
|
|
|
|
private static DashboardKpiSnapshotPayload? DeserializeSnapshot(string? metadataJson)
|
|
{
|
|
if (string.IsNullOrWhiteSpace(metadataJson))
|
|
{
|
|
return null;
|
|
}
|
|
|
|
try
|
|
{
|
|
return JsonSerializer.Deserialize<DashboardKpiSnapshotPayload>(metadataJson, JsonOptions);
|
|
}
|
|
catch
|
|
{
|
|
return null;
|
|
}
|
|
}
|
|
|
|
private static IQueryable<MobileLine> ApplyReservaContextFilter(IQueryable<MobileLine> query)
|
|
{
|
|
return query.Where(x =>
|
|
EF.Functions.ILike((x.Usuario ?? "").Trim(), "RESERVA") ||
|
|
EF.Functions.ILike((x.Skil ?? "").Trim(), "RESERVA") ||
|
|
EF.Functions.ILike((x.Cliente ?? "").Trim(), "RESERVA"));
|
|
}
|
|
|
|
private static IQueryable<MobileLine> ExcludeReservaContext(IQueryable<MobileLine> query)
|
|
{
|
|
return query.Where(x =>
|
|
!EF.Functions.ILike((x.Usuario ?? "").Trim(), "RESERVA") &&
|
|
!EF.Functions.ILike((x.Skil ?? "").Trim(), "RESERVA") &&
|
|
!EF.Functions.ILike((x.Cliente ?? "").Trim(), "RESERVA"));
|
|
}
|
|
|
|
private static bool ShouldIncludeUsuario(string usuario, string status)
|
|
{
|
|
var usuarioKey = NormalizeToken(usuario);
|
|
if (string.IsNullOrWhiteSpace(usuarioKey))
|
|
{
|
|
return false;
|
|
}
|
|
|
|
var invalidUserTokens = new[]
|
|
{
|
|
"SEMUSUARIO",
|
|
"AGUARDANDOUSUARIO",
|
|
"AGUARDANDO",
|
|
"BLOQUEAR",
|
|
"BLOQUEAD",
|
|
"BLOQUEADO",
|
|
"BLOQUEIO",
|
|
"BLOQ120",
|
|
"RESERVA",
|
|
"NAOATRIBUIDO",
|
|
"PENDENTE",
|
|
"COBRANCA",
|
|
"FATURAMENTO",
|
|
"FINANCEIRO",
|
|
"BACKOFFICE",
|
|
"ADMINISTRATIVO",
|
|
};
|
|
|
|
if (invalidUserTokens.Any(usuarioKey.Contains))
|
|
{
|
|
return false;
|
|
}
|
|
|
|
var statusKey = NormalizeToken(status);
|
|
var blockedStatusTokens = new[] { "BLOQUE", "PERDA", "ROUBO", "SUSPEN", "CANCEL", "AGUARD" };
|
|
return !blockedStatusTokens.Any(statusKey.Contains);
|
|
}
|
|
|
|
private static string NormalizeToken(string? value)
|
|
{
|
|
if (string.IsNullOrWhiteSpace(value))
|
|
{
|
|
return string.Empty;
|
|
}
|
|
|
|
var normalized = value.Normalize(NormalizationForm.FormD);
|
|
var builder = new StringBuilder(normalized.Length);
|
|
foreach (var ch in normalized)
|
|
{
|
|
var category = CharUnicodeInfo.GetUnicodeCategory(ch);
|
|
if (category == UnicodeCategory.NonSpacingMark)
|
|
{
|
|
continue;
|
|
}
|
|
|
|
if (char.IsLetterOrDigit(ch))
|
|
{
|
|
builder.Append(char.ToUpperInvariant(ch));
|
|
}
|
|
}
|
|
|
|
return builder.ToString();
|
|
}
|
|
|
|
private sealed class DashboardKpiSnapshotPayload
|
|
{
|
|
public string Scope { get; set; } = "TODOS";
|
|
public DateTime CapturedAtUtc { get; set; }
|
|
public Dictionary<string, decimal> Metrics { get; set; } = new(StringComparer.OrdinalIgnoreCase);
|
|
}
|
|
}
|