362 lines
14 KiB
C#
362 lines
14 KiB
C#
using line_gestao_api.Data;
|
|
using line_gestao_api.Dtos;
|
|
using line_gestao_api.Models;
|
|
using line_gestao_api.Services;
|
|
using Microsoft.AspNetCore.Authorization;
|
|
using Microsoft.AspNetCore.Mvc;
|
|
using Microsoft.EntityFrameworkCore;
|
|
using System.Linq;
|
|
|
|
namespace line_gestao_api.Controllers
|
|
{
|
|
[ApiController]
|
|
[Route("api/lines/vigencia")]
|
|
[Authorize]
|
|
public class VigenciaController : ControllerBase
|
|
{
|
|
private readonly AppDbContext _db;
|
|
|
|
public VigenciaController(AppDbContext db)
|
|
{
|
|
_db = db;
|
|
}
|
|
|
|
// GET /api/lines/vigencia (Linhas - Tabela Interna)
|
|
[HttpGet]
|
|
public async Task<ActionResult<PagedResult<VigenciaLineListDto>>> GetVigencia(
|
|
[FromQuery] string? search,
|
|
[FromQuery] string? client,
|
|
[FromQuery] int page = 1,
|
|
[FromQuery] int pageSize = 20,
|
|
[FromQuery] string? sortBy = "item",
|
|
[FromQuery] string? sortDir = "asc")
|
|
{
|
|
page = page < 1 ? 1 : page;
|
|
pageSize = pageSize < 1 ? 20 : pageSize;
|
|
|
|
var q = _db.VigenciaLines.AsNoTracking();
|
|
|
|
if (!string.IsNullOrWhiteSpace(client))
|
|
{
|
|
var c = client.Trim();
|
|
q = q.Where(x => x.Cliente == c);
|
|
}
|
|
|
|
if (!string.IsNullOrWhiteSpace(search))
|
|
{
|
|
var s = search.Trim();
|
|
q = q.Where(x =>
|
|
EF.Functions.ILike(x.Conta ?? "", $"%{s}%") ||
|
|
EF.Functions.ILike(x.Linha ?? "", $"%{s}%") ||
|
|
EF.Functions.ILike(x.Cliente ?? "", $"%{s}%") ||
|
|
EF.Functions.ILike(x.Usuario ?? "", $"%{s}%") ||
|
|
EF.Functions.ILike(x.PlanoContrato ?? "", $"%{s}%"));
|
|
}
|
|
|
|
var total = await q.CountAsync();
|
|
|
|
var sb = (sortBy ?? "item").Trim().ToLowerInvariant();
|
|
var desc = string.Equals((sortDir ?? "asc").Trim(), "desc", StringComparison.OrdinalIgnoreCase);
|
|
|
|
q = sb switch
|
|
{
|
|
"item" => desc ? q.OrderByDescending(x => x.Item) : q.OrderBy(x => x.Item),
|
|
"linha" => desc ? q.OrderByDescending(x => x.Linha) : q.OrderBy(x => x.Linha),
|
|
"total" => desc ? q.OrderByDescending(x => x.Total) : q.OrderBy(x => x.Total),
|
|
"dttermino" => desc ? q.OrderByDescending(x => x.DtTerminoFidelizacao) : q.OrderBy(x => x.DtTerminoFidelizacao),
|
|
_ => desc ? q.OrderByDescending(x => x.Item) : q.OrderBy(x => x.Item),
|
|
};
|
|
|
|
var items = await q
|
|
.Skip((page - 1) * pageSize)
|
|
.Take(pageSize)
|
|
.Select(x => new VigenciaLineListDto
|
|
{
|
|
Id = x.Id,
|
|
Item = x.Item,
|
|
Conta = x.Conta,
|
|
Linha = x.Linha,
|
|
Cliente = x.Cliente,
|
|
Usuario = x.Usuario,
|
|
PlanoContrato = x.PlanoContrato,
|
|
DtEfetivacaoServico = x.DtEfetivacaoServico,
|
|
DtTerminoFidelizacao = x.DtTerminoFidelizacao,
|
|
Total = x.Total
|
|
})
|
|
.ToListAsync();
|
|
|
|
return Ok(new PagedResult<VigenciaLineListDto>
|
|
{
|
|
Page = page,
|
|
PageSize = pageSize,
|
|
Total = total,
|
|
Items = items
|
|
});
|
|
}
|
|
|
|
// ==========================================================
|
|
// GET /api/lines/vigencia/groups (Cards + KPIs GERAIS)
|
|
// ==========================================================
|
|
[HttpGet("groups")]
|
|
public async Task<ActionResult<VigenciaGroupResponse>> GetVigenciaGroups(
|
|
[FromQuery] string? search,
|
|
[FromQuery] int page = 1,
|
|
[FromQuery] int pageSize = 20,
|
|
[FromQuery] string? sortBy = "cliente",
|
|
[FromQuery] string? sortDir = "asc")
|
|
{
|
|
page = page < 1 ? 1 : page;
|
|
pageSize = pageSize < 1 ? 20 : pageSize;
|
|
|
|
var today = DateTime.UtcNow.Date; // UTC para evitar erro no PostgreSQL
|
|
var limit30 = today.AddDays(30);
|
|
|
|
// Query Base (Linhas)
|
|
var q = _db.VigenciaLines.AsNoTracking()
|
|
.Where(x => x.Cliente != null && x.Cliente != "");
|
|
|
|
if (!string.IsNullOrWhiteSpace(search))
|
|
{
|
|
var s = search.Trim();
|
|
q = q.Where(x => EF.Functions.ILike(x.Cliente ?? "", $"%{s}%"));
|
|
}
|
|
|
|
// ✅ CÁLCULO DOS KPIS GERAIS (Antes do agrupamento/paginação)
|
|
// Isso garante que os KPIs mostrem o total do banco (ou do filtro), não só da página.
|
|
var kpis = new VigenciaKpis
|
|
{
|
|
TotalLinhas = await q.CountAsync(),
|
|
// Clientes distintos
|
|
TotalClientes = await q.Select(x => x.Cliente).Distinct().CountAsync(),
|
|
TotalVencidos = await q.CountAsync(x => x.DtTerminoFidelizacao != null && x.DtTerminoFidelizacao.Value.Date < today),
|
|
ValorTotal = await q.SumAsync(x => x.Total ?? 0m)
|
|
};
|
|
|
|
// Agrupamento para a lista paginada
|
|
var grouped = q
|
|
.GroupBy(x => x.Cliente!)
|
|
.Select(g => new VigenciaClientGroupDto
|
|
{
|
|
Cliente = g.Key,
|
|
Linhas = g.Count(),
|
|
Total = g.Sum(x => x.Total ?? 0m),
|
|
Vencidos = g.Count(x => x.DtTerminoFidelizacao != null && x.DtTerminoFidelizacao.Value.Date < today),
|
|
AVencer30 = g.Count(x => x.DtTerminoFidelizacao != null && x.DtTerminoFidelizacao.Value.Date >= today && x.DtTerminoFidelizacao.Value.Date <= limit30),
|
|
ProximoVencimento = g.Where(x => x.DtTerminoFidelizacao >= today).Min(x => x.DtTerminoFidelizacao),
|
|
UltimoVencimento = g.Where(x => x.DtTerminoFidelizacao < today).Max(x => x.DtTerminoFidelizacao)
|
|
});
|
|
|
|
// Contagem para paginação
|
|
var totalGroups = await grouped.CountAsync();
|
|
|
|
// Ordenação
|
|
var desc = string.Equals((sortDir ?? "asc").Trim(), "desc", StringComparison.OrdinalIgnoreCase);
|
|
if (sortBy?.ToLower() == "linhas")
|
|
grouped = desc ? grouped.OrderByDescending(x => x.Linhas) : grouped.OrderBy(x => x.Linhas);
|
|
else
|
|
grouped = desc ? grouped.OrderByDescending(x => x.Cliente) : grouped.OrderBy(x => x.Cliente);
|
|
|
|
// Paginação
|
|
var items = await grouped
|
|
.Skip((page - 1) * pageSize)
|
|
.Take(pageSize)
|
|
.ToListAsync();
|
|
|
|
// ✅ Retorna objeto composto
|
|
return Ok(new VigenciaGroupResponse
|
|
{
|
|
Data = new PagedResult<VigenciaClientGroupDto>
|
|
{
|
|
Page = page,
|
|
PageSize = pageSize,
|
|
Total = totalGroups, // Total de Clientes na paginação
|
|
Items = items
|
|
},
|
|
Kpis = kpis // KPIs Globais
|
|
});
|
|
}
|
|
|
|
[HttpGet("clients")]
|
|
public async Task<ActionResult<List<string>>> GetVigenciaClients()
|
|
{
|
|
return await _db.VigenciaLines.AsNoTracking()
|
|
.Where(x => !string.IsNullOrEmpty(x.Cliente))
|
|
.Select(x => x.Cliente!)
|
|
.Distinct()
|
|
.OrderBy(x => x)
|
|
.ToListAsync();
|
|
}
|
|
|
|
[HttpGet("{id:guid}")]
|
|
public async Task<ActionResult<VigenciaLineDetailDto>> GetById(Guid id)
|
|
{
|
|
var x = await _db.VigenciaLines.AsNoTracking().FirstOrDefaultAsync(a => a.Id == id);
|
|
if (x == null) return NotFound();
|
|
|
|
return Ok(new VigenciaLineDetailDto
|
|
{
|
|
Id = x.Id,
|
|
Item = x.Item,
|
|
Conta = x.Conta,
|
|
Linha = x.Linha,
|
|
Cliente = x.Cliente,
|
|
Usuario = x.Usuario,
|
|
PlanoContrato = x.PlanoContrato,
|
|
DtEfetivacaoServico = x.DtEfetivacaoServico,
|
|
DtTerminoFidelizacao = x.DtTerminoFidelizacao,
|
|
Total = x.Total,
|
|
CreatedAt = x.CreatedAt,
|
|
UpdatedAt = x.UpdatedAt
|
|
});
|
|
}
|
|
|
|
[HttpPost]
|
|
[Authorize(Roles = "admin")]
|
|
public async Task<ActionResult<VigenciaLineDetailDto>> Create([FromBody] CreateVigenciaRequest req)
|
|
{
|
|
var now = DateTime.UtcNow;
|
|
|
|
var linha = TrimOrNull(req.Linha);
|
|
var conta = TrimOrNull(req.Conta);
|
|
var cliente = TrimOrNull(req.Cliente);
|
|
var usuario = TrimOrNull(req.Usuario);
|
|
var plano = TrimOrNull(req.PlanoContrato);
|
|
|
|
MobileLine? mobile = null;
|
|
|
|
if (!string.IsNullOrWhiteSpace(linha))
|
|
{
|
|
var linhaDigits = OnlyDigits(linha);
|
|
|
|
if (!string.IsNullOrWhiteSpace(linhaDigits))
|
|
{
|
|
mobile = await _db.MobileLines.AsNoTracking()
|
|
.FirstOrDefaultAsync(x => x.Linha == linhaDigits);
|
|
}
|
|
else
|
|
{
|
|
var raw = linha.Trim();
|
|
mobile = await _db.MobileLines.AsNoTracking()
|
|
.FirstOrDefaultAsync(x => EF.Functions.ILike(x.Linha ?? "", raw));
|
|
}
|
|
|
|
if (mobile != null)
|
|
{
|
|
conta ??= mobile.Conta;
|
|
cliente ??= mobile.Cliente;
|
|
usuario ??= mobile.Usuario;
|
|
plano ??= mobile.PlanoContrato;
|
|
}
|
|
}
|
|
|
|
decimal? total = req.Total;
|
|
if (!total.HasValue && mobile?.ValorPlanoVivo != null)
|
|
{
|
|
total = mobile.ValorPlanoVivo;
|
|
}
|
|
if (!total.HasValue && !string.IsNullOrWhiteSpace(plano))
|
|
{
|
|
var planSuggestion = await AutoFillRules.ResolvePlanSuggestionAsync(_db, plano);
|
|
total = planSuggestion?.ValorPlano;
|
|
}
|
|
|
|
var item = req.Item;
|
|
if (!item.HasValue || item.Value <= 0)
|
|
{
|
|
var maxItem = await _db.VigenciaLines.MaxAsync(x => (int?)x.Item) ?? 0;
|
|
item = maxItem + 1;
|
|
}
|
|
|
|
var e = new VigenciaLine
|
|
{
|
|
Id = Guid.NewGuid(),
|
|
Item = item.Value,
|
|
Conta = conta,
|
|
Linha = linha,
|
|
Cliente = cliente,
|
|
Usuario = usuario,
|
|
PlanoContrato = plano,
|
|
DtEfetivacaoServico = req.DtEfetivacaoServico.HasValue ? ToUtc(req.DtEfetivacaoServico.Value) : null,
|
|
DtTerminoFidelizacao = req.DtTerminoFidelizacao.HasValue ? ToUtc(req.DtTerminoFidelizacao.Value) : null,
|
|
Total = total,
|
|
CreatedAt = now,
|
|
UpdatedAt = now
|
|
};
|
|
|
|
_db.VigenciaLines.Add(e);
|
|
await _db.SaveChangesAsync();
|
|
|
|
return CreatedAtAction(nameof(GetById), new { id = e.Id }, new VigenciaLineDetailDto
|
|
{
|
|
Id = e.Id,
|
|
Item = e.Item,
|
|
Conta = e.Conta,
|
|
Linha = e.Linha,
|
|
Cliente = e.Cliente,
|
|
Usuario = e.Usuario,
|
|
PlanoContrato = e.PlanoContrato,
|
|
DtEfetivacaoServico = e.DtEfetivacaoServico,
|
|
DtTerminoFidelizacao = e.DtTerminoFidelizacao,
|
|
Total = e.Total,
|
|
CreatedAt = e.CreatedAt,
|
|
UpdatedAt = e.UpdatedAt
|
|
});
|
|
}
|
|
|
|
[HttpPut("{id:guid}")]
|
|
[Authorize(Roles = "admin")]
|
|
public async Task<IActionResult> Update(Guid id, [FromBody] UpdateVigenciaRequest req)
|
|
{
|
|
var x = await _db.VigenciaLines.FirstOrDefaultAsync(a => a.Id == id);
|
|
if (x == null) return NotFound();
|
|
|
|
if (req.Item.HasValue) x.Item = req.Item.Value;
|
|
if (req.Conta != null) x.Conta = TrimOrNull(req.Conta);
|
|
if (req.Linha != null) x.Linha = TrimOrNull(req.Linha);
|
|
if (req.Cliente != null) x.Cliente = TrimOrNull(req.Cliente);
|
|
if (req.Usuario != null) x.Usuario = TrimOrNull(req.Usuario);
|
|
if (req.PlanoContrato != null) x.PlanoContrato = TrimOrNull(req.PlanoContrato);
|
|
|
|
if (req.DtEfetivacaoServico.HasValue) x.DtEfetivacaoServico = ToUtc(req.DtEfetivacaoServico.Value);
|
|
if (req.DtTerminoFidelizacao.HasValue) x.DtTerminoFidelizacao = ToUtc(req.DtTerminoFidelizacao.Value);
|
|
|
|
if (req.Total.HasValue) x.Total = req.Total.Value;
|
|
|
|
x.UpdatedAt = DateTime.UtcNow;
|
|
|
|
await _db.SaveChangesAsync();
|
|
return NoContent();
|
|
}
|
|
|
|
[HttpDelete("{id:guid}")]
|
|
[Authorize(Roles = "admin")]
|
|
public async Task<IActionResult> Delete(Guid id)
|
|
{
|
|
var x = await _db.VigenciaLines.FirstOrDefaultAsync(a => a.Id == id);
|
|
if (x == null) return NotFound();
|
|
|
|
_db.VigenciaLines.Remove(x);
|
|
await _db.SaveChangesAsync();
|
|
return NoContent();
|
|
}
|
|
|
|
private static string? TrimOrNull(string? s)
|
|
{
|
|
if (string.IsNullOrWhiteSpace(s)) return null;
|
|
return s.Trim();
|
|
}
|
|
|
|
private static DateTime ToUtc(DateTime dt)
|
|
{
|
|
return dt.Kind == DateTimeKind.Utc ? dt
|
|
: (dt.Kind == DateTimeKind.Local ? dt.ToUniversalTime() : DateTime.SpecifyKind(dt, DateTimeKind.Utc));
|
|
}
|
|
|
|
private static string OnlyDigits(string? s)
|
|
{
|
|
if (string.IsNullOrWhiteSpace(s)) return "";
|
|
return new string(s.Where(char.IsDigit).ToArray());
|
|
}
|
|
}
|
|
}
|