903 lines
38 KiB
C#
903 lines
38 KiB
C#
using ClosedXML.Excel;
|
|
using line_gestao_api.Data;
|
|
using line_gestao_api.Dtos;
|
|
using line_gestao_api.Models;
|
|
using Microsoft.AspNetCore.Mvc;
|
|
using Microsoft.EntityFrameworkCore;
|
|
using System.Globalization;
|
|
using System.Text;
|
|
|
|
namespace line_gestao_api.Controllers
|
|
{
|
|
[ApiController]
|
|
[Route("api/[controller]")]
|
|
//[Authorize]
|
|
public class LinesController : ControllerBase
|
|
{
|
|
private readonly AppDbContext _db;
|
|
|
|
public LinesController(AppDbContext db)
|
|
{
|
|
_db = db;
|
|
}
|
|
|
|
public class ImportExcelForm
|
|
{
|
|
public IFormFile File { get; set; } = default!;
|
|
}
|
|
|
|
// ==========================================================
|
|
// ✅ 1. ENDPOINT: AGRUPAR POR CLIENTE
|
|
// ==========================================================
|
|
[HttpGet("groups")]
|
|
public async Task<ActionResult<PagedResult<ClientGroupDto>>> GetClientGroups(
|
|
[FromQuery] string? skil,
|
|
[FromQuery] string? search,
|
|
[FromQuery] int page = 1,
|
|
[FromQuery] int pageSize = 10)
|
|
{
|
|
page = page < 1 ? 1 : page;
|
|
pageSize = pageSize < 1 ? 10 : pageSize;
|
|
|
|
var query = _db.MobileLines.AsNoTracking().Where(x => !string.IsNullOrEmpty(x.Cliente));
|
|
|
|
// Filtro SKIL
|
|
if (!string.IsNullOrWhiteSpace(skil))
|
|
{
|
|
var sSkil = skil.Trim();
|
|
if (sSkil.Equals("RESERVA", StringComparison.OrdinalIgnoreCase))
|
|
query = query.Where(x => x.Skil == "RESERVA" || EF.Functions.ILike(x.Skil ?? "", "%RESERVA%"));
|
|
else
|
|
query = query.Where(x => EF.Functions.ILike(x.Skil ?? "", $"%{sSkil}%"));
|
|
}
|
|
|
|
// Filtro SEARCH (Busca pelo Nome do Cliente)
|
|
if (!string.IsNullOrWhiteSpace(search))
|
|
{
|
|
var s = search.Trim();
|
|
query = query.Where(x => EF.Functions.ILike(x.Cliente ?? "", $"%{s}%"));
|
|
}
|
|
|
|
var groupedQuery = query
|
|
.GroupBy(x => x.Cliente)
|
|
.Select(g => new ClientGroupDto
|
|
{
|
|
Cliente = g.Key!,
|
|
TotalLinhas = g.Count(),
|
|
Ativos = g.Count(x => EF.Functions.ILike(x.Status ?? "", "%ativo%")),
|
|
Bloqueados = g.Count(x => EF.Functions.ILike(x.Status ?? "", "%bloque%") ||
|
|
EF.Functions.ILike(x.Status ?? "", "%perda%") ||
|
|
EF.Functions.ILike(x.Status ?? "", "%roubo%"))
|
|
});
|
|
|
|
var totalGroups = await groupedQuery.CountAsync();
|
|
|
|
var items = await groupedQuery
|
|
.OrderBy(x => x.Cliente)
|
|
.Skip((page - 1) * pageSize)
|
|
.Take(pageSize)
|
|
.ToListAsync();
|
|
|
|
return Ok(new PagedResult<ClientGroupDto>
|
|
{
|
|
Page = page,
|
|
PageSize = pageSize,
|
|
Total = totalGroups,
|
|
Items = items
|
|
});
|
|
}
|
|
|
|
// ==========================================================
|
|
// ✅ 2. ENDPOINT: LISTAR NOMES DE CLIENTES (CORRIGIDO PARA ACEITAR SKIL)
|
|
// ==========================================================
|
|
[HttpGet("clients")]
|
|
public async Task<ActionResult<List<string>>> GetClients([FromQuery] string? skil)
|
|
{
|
|
var query = _db.MobileLines.AsNoTracking();
|
|
|
|
// APLICA O FILTRO DE SKIL ANTES DE SELECIONAR OS NOMES
|
|
if (!string.IsNullOrWhiteSpace(skil))
|
|
{
|
|
var sSkil = skil.Trim();
|
|
if (sSkil.Equals("RESERVA", StringComparison.OrdinalIgnoreCase))
|
|
query = query.Where(x => x.Skil == "RESERVA" || EF.Functions.ILike(x.Skil ?? "", "%RESERVA%"));
|
|
else
|
|
query = query.Where(x => EF.Functions.ILike(x.Skil ?? "", $"%{sSkil}%"));
|
|
}
|
|
|
|
var clients = await query
|
|
.Where(x => !string.IsNullOrEmpty(x.Cliente))
|
|
.Select(x => x.Cliente)
|
|
.Distinct()
|
|
.OrderBy(x => x)
|
|
.ToListAsync();
|
|
|
|
return Ok(clients);
|
|
}
|
|
|
|
// ==========================================================
|
|
// ✅ NOVO: ENDPOINTS DO FATURAMENTO (PF/PJ)
|
|
// ==========================================================
|
|
[HttpGet("billing")]
|
|
public async Task<ActionResult<PagedResult<BillingClient>>> GetBilling(
|
|
[FromQuery] string? tipo, // "PF", "PJ" ou null (todos)
|
|
[FromQuery] string? search, // busca por cliente
|
|
[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 q = _db.BillingClients.AsNoTracking();
|
|
|
|
if (!string.IsNullOrWhiteSpace(tipo))
|
|
{
|
|
var t = tipo.Trim().ToUpperInvariant();
|
|
if (t == "PF" || t == "PJ") q = q.Where(x => x.Tipo == t);
|
|
}
|
|
|
|
if (!string.IsNullOrWhiteSpace(search))
|
|
{
|
|
var s = search.Trim();
|
|
q = q.Where(x => EF.Functions.ILike(x.Cliente ?? "", $"%{s}%"));
|
|
}
|
|
|
|
var total = await q.CountAsync();
|
|
|
|
var sb = (sortBy ?? "cliente").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),
|
|
"tipo" => desc ? q.OrderByDescending(x => x.Tipo).ThenBy(x => x.Cliente) : q.OrderBy(x => x.Tipo).ThenBy(x => x.Cliente),
|
|
"qtdlinhas" => desc ? q.OrderByDescending(x => x.QtdLinhas ?? 0).ThenBy(x => x.Cliente) : q.OrderBy(x => x.QtdLinhas ?? 0).ThenBy(x => x.Cliente),
|
|
"franquiavivo" => desc ? q.OrderByDescending(x => x.FranquiaVivo ?? 0).ThenBy(x => x.Cliente) : q.OrderBy(x => x.FranquiaVivo ?? 0).ThenBy(x => x.Cliente),
|
|
"valorcontratovivo" => desc ? q.OrderByDescending(x => x.ValorContratoVivo ?? 0).ThenBy(x => x.Cliente) : q.OrderBy(x => x.ValorContratoVivo ?? 0).ThenBy(x => x.Cliente),
|
|
"franquialine" => desc ? q.OrderByDescending(x => x.FranquiaLine ?? 0).ThenBy(x => x.Cliente) : q.OrderBy(x => x.FranquiaLine ?? 0).ThenBy(x => x.Cliente),
|
|
"valorcontratoline" => desc ? q.OrderByDescending(x => x.ValorContratoLine ?? 0).ThenBy(x => x.Cliente) : q.OrderBy(x => x.ValorContratoLine ?? 0).ThenBy(x => x.Cliente),
|
|
"lucro" => desc ? q.OrderByDescending(x => x.Lucro ?? 0).ThenBy(x => x.Cliente) : q.OrderBy(x => x.Lucro ?? 0).ThenBy(x => x.Cliente),
|
|
_ => desc ? q.OrderByDescending(x => x.Cliente).ThenBy(x => x.Item) : q.OrderBy(x => x.Cliente).ThenBy(x => x.Item),
|
|
};
|
|
|
|
var items = await q
|
|
.Skip((page - 1) * pageSize)
|
|
.Take(pageSize)
|
|
.ToListAsync();
|
|
|
|
return Ok(new PagedResult<BillingClient>
|
|
{
|
|
Page = page,
|
|
PageSize = pageSize,
|
|
Total = total,
|
|
Items = items
|
|
});
|
|
}
|
|
|
|
[HttpGet("billing/clients")]
|
|
public async Task<ActionResult<List<string>>> GetBillingClients([FromQuery] string? tipo)
|
|
{
|
|
var q = _db.BillingClients.AsNoTracking();
|
|
|
|
if (!string.IsNullOrWhiteSpace(tipo))
|
|
{
|
|
var t = tipo.Trim().ToUpperInvariant();
|
|
if (t == "PF" || t == "PJ") q = q.Where(x => x.Tipo == t);
|
|
}
|
|
|
|
var clients = await q
|
|
.Where(x => !string.IsNullOrEmpty(x.Cliente))
|
|
.Select(x => x.Cliente)
|
|
.Distinct()
|
|
.OrderBy(x => x)
|
|
.ToListAsync();
|
|
|
|
return Ok(clients);
|
|
}
|
|
|
|
// ==========================================================
|
|
// ✅ 3. GET ALL (TABELA / DETALHES DO GRUPO)
|
|
// ==========================================================
|
|
[HttpGet]
|
|
public async Task<ActionResult<PagedResult<MobileLineListDto>>> GetAll(
|
|
[FromQuery] string? search,
|
|
[FromQuery] string? skil,
|
|
[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.MobileLines.AsNoTracking();
|
|
|
|
if (!string.IsNullOrWhiteSpace(skil))
|
|
{
|
|
var sSkil = skil.Trim();
|
|
if (sSkil.Equals("RESERVA", StringComparison.OrdinalIgnoreCase))
|
|
q = q.Where(x => x.Skil == "RESERVA" || EF.Functions.ILike(x.Skil ?? "", "%RESERVA%"));
|
|
else
|
|
q = q.Where(x => EF.Functions.ILike(x.Skil ?? "", $"%{sSkil}%"));
|
|
}
|
|
|
|
if (!string.IsNullOrWhiteSpace(client))
|
|
{
|
|
q = q.Where(x => EF.Functions.ILike(x.Cliente ?? "", client.Trim()));
|
|
}
|
|
|
|
if (!string.IsNullOrWhiteSpace(search))
|
|
{
|
|
var s = search.Trim();
|
|
q = q.Where(x =>
|
|
EF.Functions.ILike(x.Linha ?? "", $"%{s}%") ||
|
|
EF.Functions.ILike(x.Chip ?? "", $"%{s}%") ||
|
|
EF.Functions.ILike(x.Cliente ?? "", $"%{s}%") ||
|
|
EF.Functions.ILike(x.Usuario ?? "", $"%{s}%") ||
|
|
EF.Functions.ILike(x.Conta ?? "", $"%{s}%") ||
|
|
EF.Functions.ILike(x.Status ?? "", $"%{s}%"));
|
|
}
|
|
|
|
var total = await q.CountAsync();
|
|
|
|
var sb = (sortBy ?? "item").Trim().ToLowerInvariant();
|
|
var desc = string.Equals((sortDir ?? "asc").Trim(), "desc", StringComparison.OrdinalIgnoreCase);
|
|
|
|
if (sb == "plano") sb = "planocontrato";
|
|
if (sb == "contrato") sb = "vencconta";
|
|
|
|
q = sb switch
|
|
{
|
|
"conta" => desc ? q.OrderByDescending(x => x.Conta ?? "").ThenBy(x => x.Item) : q.OrderBy(x => x.Conta ?? "").ThenBy(x => x.Item),
|
|
"linha" => desc ? q.OrderByDescending(x => x.Linha ?? "").ThenBy(x => x.Item) : q.OrderBy(x => x.Linha ?? "").ThenBy(x => x.Item),
|
|
"chip" => desc ? q.OrderByDescending(x => x.Chip ?? "").ThenBy(x => x.Item) : q.OrderBy(x => x.Chip ?? "").ThenBy(x => x.Item),
|
|
"cliente" => desc ? q.OrderByDescending(x => x.Cliente ?? "").ThenBy(x => x.Item) : q.OrderBy(x => x.Cliente ?? "").ThenBy(x => x.Item),
|
|
"usuario" => desc ? q.OrderByDescending(x => x.Usuario ?? "").ThenBy(x => x.Item) : q.OrderBy(x => x.Usuario ?? "").ThenBy(x => x.Item),
|
|
"planocontrato" => desc ? q.OrderByDescending(x => x.PlanoContrato ?? "").ThenBy(x => x.Item) : q.OrderBy(x => x.PlanoContrato ?? "").ThenBy(x => x.Item),
|
|
"vencconta" => desc ? q.OrderByDescending(x => x.VencConta ?? "").ThenBy(x => x.Item) : q.OrderBy(x => x.VencConta ?? "").ThenBy(x => x.Item),
|
|
"status" => desc ? q.OrderByDescending(x => x.Status ?? "").ThenBy(x => x.Item) : q.OrderBy(x => x.Status ?? "").ThenBy(x => x.Item),
|
|
"skil" => desc ? q.OrderByDescending(x => x.Skil ?? "").ThenBy(x => x.Item) : q.OrderBy(x => x.Skil ?? "").ThenBy(x => x.Item),
|
|
"modalidade" => desc ? q.OrderByDescending(x => x.Modalidade ?? "").ThenBy(x => x.Item) : q.OrderBy(x => x.Modalidade ?? "").ThenBy(x => x.Item),
|
|
_ => desc ? q.OrderByDescending(x => x.Item) : q.OrderBy(x => x.Item)
|
|
};
|
|
|
|
var items = await q
|
|
.Skip((page - 1) * pageSize)
|
|
.Take(pageSize)
|
|
.Select(x => new MobileLineListDto
|
|
{
|
|
Id = x.Id,
|
|
Item = x.Item,
|
|
Conta = x.Conta,
|
|
Linha = x.Linha,
|
|
Chip = x.Chip,
|
|
Cliente = x.Cliente,
|
|
Usuario = x.Usuario,
|
|
PlanoContrato = x.PlanoContrato,
|
|
Status = x.Status,
|
|
Skil = x.Skil,
|
|
Modalidade = x.Modalidade,
|
|
VencConta = x.VencConta
|
|
})
|
|
.ToListAsync();
|
|
|
|
return Ok(new PagedResult<MobileLineListDto>
|
|
{
|
|
Page = page,
|
|
PageSize = pageSize,
|
|
Total = total,
|
|
Items = items
|
|
});
|
|
}
|
|
|
|
// ==========================================================
|
|
// ✅ 4. GET BY ID
|
|
// ==========================================================
|
|
[HttpGet("{id:guid}")]
|
|
public async Task<ActionResult<MobileLineDetailDto>> GetById(Guid id)
|
|
{
|
|
var x = await _db.MobileLines.AsNoTracking().FirstOrDefaultAsync(a => a.Id == id);
|
|
if (x == null) return NotFound();
|
|
return Ok(ToDetailDto(x));
|
|
}
|
|
|
|
// ==========================================================
|
|
// ✅ 5. CREATE (AUTO-INCREMENTO DE ID/ITEM)
|
|
// ==========================================================
|
|
[HttpPost]
|
|
public async Task<ActionResult<MobileLineDetailDto>> Create([FromBody] CreateMobileLineDto req)
|
|
{
|
|
if (string.IsNullOrWhiteSpace(req.Cliente))
|
|
return BadRequest(new { message = "O nome do Cliente é obrigatório." });
|
|
|
|
if (string.IsNullOrWhiteSpace(req.Linha))
|
|
return BadRequest(new { message = "O número da Linha é obrigatório." });
|
|
|
|
var linhaLimpa = OnlyDigits(req.Linha);
|
|
var chipLimpo = OnlyDigits(req.Chip);
|
|
|
|
if (string.IsNullOrWhiteSpace(linhaLimpa))
|
|
return BadRequest(new { message = "Número de linha inválido." });
|
|
|
|
var exists = await _db.MobileLines.AsNoTracking().AnyAsync(x => x.Linha == linhaLimpa);
|
|
if (exists)
|
|
return Conflict(new { message = $"A linha {req.Linha} já está cadastrada no sistema." });
|
|
|
|
var maxItem = await _db.MobileLines.MaxAsync(x => (int?)x.Item) ?? 0;
|
|
var nextItem = maxItem + 1;
|
|
|
|
var newLine = new MobileLine
|
|
{
|
|
Id = Guid.NewGuid(),
|
|
Item = nextItem,
|
|
Cliente = req.Cliente.Trim().ToUpper(),
|
|
Linha = linhaLimpa,
|
|
Chip = chipLimpo,
|
|
Usuario = req.Usuario?.Trim(),
|
|
Status = req.Status?.Trim(),
|
|
Skil = req.Skil?.Trim(),
|
|
Modalidade = req.Modalidade?.Trim(),
|
|
PlanoContrato = req.PlanoContrato?.Trim(),
|
|
Conta = req.Conta?.Trim(),
|
|
VencConta = req.VencConta?.Trim(),
|
|
|
|
DataBloqueio = ToUtc(req.DataBloqueio),
|
|
DataEntregaOpera = ToUtc(req.DataEntregaOpera),
|
|
DataEntregaCliente = ToUtc(req.DataEntregaCliente),
|
|
|
|
Cedente = req.Cedente?.Trim(),
|
|
Solicitante = req.Solicitante?.Trim(),
|
|
|
|
FranquiaVivo = req.FranquiaVivo,
|
|
ValorPlanoVivo = req.ValorPlanoVivo,
|
|
GestaoVozDados = req.GestaoVozDados,
|
|
Skeelo = req.Skeelo,
|
|
VivoNewsPlus = req.VivoNewsPlus,
|
|
VivoTravelMundo = req.VivoTravelMundo,
|
|
VivoGestaoDispositivo = req.VivoGestaoDispositivo,
|
|
ValorContratoVivo = req.ValorContratoVivo,
|
|
FranquiaLine = req.FranquiaLine,
|
|
FranquiaGestao = req.FranquiaGestao,
|
|
LocacaoAp = req.LocacaoAp,
|
|
ValorContratoLine = req.ValorContratoLine,
|
|
Desconto = req.Desconto,
|
|
Lucro = req.Lucro,
|
|
|
|
CreatedAt = DateTime.UtcNow,
|
|
UpdatedAt = DateTime.UtcNow
|
|
};
|
|
|
|
ApplyReservaRule(newLine);
|
|
|
|
_db.MobileLines.Add(newLine);
|
|
|
|
try
|
|
{
|
|
await _db.SaveChangesAsync();
|
|
}
|
|
catch (DbUpdateException)
|
|
{
|
|
return StatusCode(500, new { message = "Erro ao salvar no banco de dados." });
|
|
}
|
|
|
|
return CreatedAtAction(nameof(GetById), new { id = newLine.Id }, ToDetailDto(newLine));
|
|
}
|
|
|
|
// ==========================================================
|
|
// ✅ 6. UPDATE (ITEM PROTEGIDO)
|
|
// ==========================================================
|
|
[HttpPut("{id:guid}")]
|
|
public async Task<IActionResult> Update(Guid id, [FromBody] UpdateMobileLineRequest req)
|
|
{
|
|
var x = await _db.MobileLines.FirstOrDefaultAsync(a => a.Id == id);
|
|
if (x == null) return NotFound();
|
|
|
|
var newLinha = OnlyDigits(req.Linha);
|
|
if (!string.IsNullOrWhiteSpace(newLinha) && !string.Equals((x.Linha ?? ""), newLinha, StringComparison.Ordinal))
|
|
{
|
|
var exists = await _db.MobileLines.AsNoTracking().AnyAsync(m => m.Linha == newLinha && m.Id != id);
|
|
if (exists) return Conflict(new { message = "Já existe registro com essa LINHA.", linha = newLinha });
|
|
}
|
|
|
|
x.Conta = req.Conta?.Trim();
|
|
x.Linha = newLinha;
|
|
x.Chip = OnlyDigits(req.Chip);
|
|
x.Cliente = req.Cliente?.Trim();
|
|
x.Usuario = req.Usuario?.Trim();
|
|
x.PlanoContrato = req.PlanoContrato?.Trim();
|
|
x.FranquiaVivo = req.FranquiaVivo;
|
|
x.ValorPlanoVivo = req.ValorPlanoVivo;
|
|
x.GestaoVozDados = req.GestaoVozDados;
|
|
x.Skeelo = req.Skeelo;
|
|
x.VivoNewsPlus = req.VivoNewsPlus;
|
|
x.VivoTravelMundo = req.VivoTravelMundo;
|
|
x.VivoGestaoDispositivo = req.VivoGestaoDispositivo;
|
|
x.ValorContratoVivo = req.ValorContratoVivo;
|
|
x.FranquiaLine = req.FranquiaLine;
|
|
x.FranquiaGestao = req.FranquiaGestao;
|
|
x.LocacaoAp = req.LocacaoAp;
|
|
x.ValorContratoLine = req.ValorContratoLine;
|
|
x.Desconto = req.Desconto;
|
|
x.Lucro = req.Lucro;
|
|
x.Status = req.Status?.Trim();
|
|
x.DataBloqueio = ToUtc(req.DataBloqueio);
|
|
x.Skil = req.Skil?.Trim();
|
|
x.Modalidade = req.Modalidade?.Trim();
|
|
x.Cedente = req.Cedente?.Trim();
|
|
x.Solicitante = req.Solicitante?.Trim();
|
|
x.DataEntregaOpera = ToUtc(req.DataEntregaOpera);
|
|
x.DataEntregaCliente = ToUtc(req.DataEntregaCliente);
|
|
x.VencConta = req.VencConta?.Trim();
|
|
|
|
ApplyReservaRule(x);
|
|
x.UpdatedAt = DateTime.UtcNow;
|
|
|
|
try { await _db.SaveChangesAsync(); }
|
|
catch (DbUpdateException) { return Conflict(new { message = "Conflito ao salvar." }); }
|
|
|
|
return NoContent();
|
|
}
|
|
|
|
// ==========================================================
|
|
// ✅ 7. DELETE
|
|
// ==========================================================
|
|
[HttpDelete("{id:guid}")]
|
|
public async Task<IActionResult> Delete(Guid id)
|
|
{
|
|
var x = await _db.MobileLines.FirstOrDefaultAsync(a => a.Id == id);
|
|
if (x == null) return NotFound();
|
|
_db.MobileLines.Remove(x);
|
|
await _db.SaveChangesAsync();
|
|
return NoContent();
|
|
}
|
|
|
|
// ==========================================================
|
|
// ✅ 8. IMPORT EXCEL (GERAL + MUREG + FATURAMENTO PF/PJ no mesmo upload)
|
|
// ==========================================================
|
|
[HttpPost("import-excel")]
|
|
[Consumes("multipart/form-data")]
|
|
[RequestSizeLimit(50_000_000)]
|
|
public async Task<ActionResult<ImportResultDto>> ImportExcel([FromForm] ImportExcelForm form)
|
|
{
|
|
var file = form.File;
|
|
if (file == null || file.Length == 0) return BadRequest("Arquivo inválido.");
|
|
|
|
using var stream = file.OpenReadStream();
|
|
using var wb = new XLWorkbook(stream);
|
|
|
|
// =========================
|
|
// ✅ IMPORTA GERAL (igual)
|
|
// =========================
|
|
var ws = wb.Worksheets.FirstOrDefault(w => w.Name.Trim().Equals("GERAL", StringComparison.OrdinalIgnoreCase));
|
|
if (ws == null) return BadRequest("Aba 'GERAL' não encontrada.");
|
|
|
|
var headerRow = ws.RowsUsed().FirstOrDefault(r => r.CellsUsed().Any(c => NormalizeHeader(c.GetString()) == "ITEM"));
|
|
if (headerRow == null) return BadRequest("Cabeçalho 'ITEM' não encontrado.");
|
|
|
|
var map = new Dictionary<string, int>(StringComparer.OrdinalIgnoreCase);
|
|
foreach (var cell in headerRow.CellsUsed())
|
|
{
|
|
var k = NormalizeHeader(cell.GetString());
|
|
if (!string.IsNullOrWhiteSpace(k) && !map.ContainsKey(k)) map[k] = cell.Address.ColumnNumber;
|
|
}
|
|
|
|
int colItem = GetCol(map, "ITEM");
|
|
if (colItem == 0) return BadRequest("Coluna 'ITEM' não encontrada.");
|
|
|
|
var startRow = headerRow.RowNumber() + 1;
|
|
|
|
await _db.MobileLines.ExecuteDeleteAsync();
|
|
|
|
var buffer = new List<MobileLine>(600);
|
|
var imported = 0;
|
|
|
|
// ✅ FIX: ws.LastRowUsed() pode ser null
|
|
var lastRow = ws.LastRowUsed()?.RowNumber() ?? startRow;
|
|
|
|
for (int r = startRow; r <= lastRow; r++)
|
|
{
|
|
var itemStr = GetCellString(ws, r, colItem);
|
|
if (string.IsNullOrWhiteSpace(itemStr)) break;
|
|
|
|
var e = new MobileLine
|
|
{
|
|
Id = Guid.NewGuid(), // ✅ recomendado
|
|
Item = TryInt(itemStr),
|
|
Conta = GetCellByHeader(ws, r, map, "CONTA"),
|
|
Linha = OnlyDigits(GetCellByHeader(ws, r, map, "LINHA")),
|
|
Chip = OnlyDigits(GetCellByHeader(ws, r, map, "CHIP")),
|
|
Cliente = GetCellByHeader(ws, r, map, "CLIENTE"),
|
|
Usuario = GetCellByHeader(ws, r, map, "USUARIO"),
|
|
PlanoContrato = GetCellByHeader(ws, r, map, "PLANO CONTRATO"),
|
|
FranquiaVivo = TryDecimal(GetCellByHeader(ws, r, map, "FRAQUIA")),
|
|
ValorPlanoVivo = TryDecimal(GetCellByHeader(ws, r, map, "VALOR DO PLANO R$")),
|
|
GestaoVozDados = TryDecimal(GetCellByHeader(ws, r, map, "GESTAO VOZ E DADOS R$")),
|
|
Skeelo = TryDecimal(GetCellByHeader(ws, r, map, "SKEELO")),
|
|
VivoNewsPlus = TryDecimal(GetCellByHeader(ws, r, map, "VIVO NEWS PLUS")),
|
|
VivoTravelMundo = TryDecimal(GetCellByHeader(ws, r, map, "VIVO TRAVEL MUNDO")),
|
|
VivoGestaoDispositivo = TryDecimal(GetCellByHeader(ws, r, map, "VIVO GESTAO DISPOSITIVO")),
|
|
ValorContratoVivo = TryDecimal(GetCellByHeader(ws, r, map, "VALOR CONTRATO VIVO")),
|
|
FranquiaLine = TryDecimal(GetCellByHeader(ws, r, map, "FRANQUIA LINE")),
|
|
FranquiaGestao = TryDecimal(GetCellByHeader(ws, r, map, "FRANQUIA GESTAO")),
|
|
LocacaoAp = TryDecimal(GetCellByHeader(ws, r, map, "LOCACAO AP.")),
|
|
ValorContratoLine = TryDecimal(GetCellByHeader(ws, r, map, "VALOR CONTRATO LINE")),
|
|
Desconto = TryDecimal(GetCellByHeader(ws, r, map, "DESCONTO")),
|
|
Lucro = TryDecimal(GetCellByHeader(ws, r, map, "LUCRO")),
|
|
Status = GetCellByHeader(ws, r, map, "STATUS"),
|
|
DataBloqueio = TryDate(ws, r, map, "DATA DO BLOQUEIO"),
|
|
Skil = GetCellByHeader(ws, r, map, "SKIL"),
|
|
Modalidade = GetCellByHeader(ws, r, map, "MODALIDADE"),
|
|
Cedente = GetCellByHeader(ws, r, map, "CEDENTE"),
|
|
Solicitante = GetCellByHeader(ws, r, map, "SOLICITANTE"),
|
|
DataEntregaOpera = TryDate(ws, r, map, "DATA DA ENTREGA OPERA."),
|
|
DataEntregaCliente = TryDate(ws, r, map, "DATA DA ENTREGA CLIENTE"),
|
|
VencConta = GetCellByHeader(ws, r, map, "VENC. DA CONTA"),
|
|
CreatedAt = DateTime.UtcNow,
|
|
UpdatedAt = DateTime.UtcNow
|
|
};
|
|
|
|
ApplyReservaRule(e);
|
|
|
|
buffer.Add(e);
|
|
imported++;
|
|
|
|
if (buffer.Count >= 500)
|
|
{
|
|
await _db.MobileLines.AddRangeAsync(buffer);
|
|
await _db.SaveChangesAsync();
|
|
buffer.Clear();
|
|
}
|
|
}
|
|
|
|
if (buffer.Count > 0)
|
|
{
|
|
await _db.MobileLines.AddRangeAsync(buffer);
|
|
await _db.SaveChangesAsync();
|
|
}
|
|
|
|
// =========================
|
|
// ✅ IMPORTA MUREG (igual ao seu)
|
|
// =========================
|
|
await ImportMuregFromWorkbook(wb);
|
|
|
|
// =========================
|
|
// ✅ NOVO: IMPORTA FATURAMENTO PF/PJ
|
|
// =========================
|
|
await ImportBillingFromWorkbook(wb);
|
|
|
|
return Ok(new ImportResultDto { Imported = imported });
|
|
}
|
|
|
|
// ==========================================================
|
|
// ✅ IMPORTAÇÃO DA ABA MUREG (mesmo upload)
|
|
// ==========================================================
|
|
private async Task ImportMuregFromWorkbook(XLWorkbook wb)
|
|
{
|
|
var wsM = wb.Worksheets.FirstOrDefault(w => w.Name.Trim().Equals("MUREG", StringComparison.OrdinalIgnoreCase))
|
|
?? wb.Worksheets.FirstOrDefault(w => w.Name.Trim().ToUpperInvariant().Contains("MUREG"));
|
|
|
|
if (wsM == null) return;
|
|
|
|
var headerRow = wsM.RowsUsed().FirstOrDefault(r => r.CellsUsed().Any(c => NormalizeHeader(c.GetString()) == "ITEM"));
|
|
if (headerRow == null) return;
|
|
|
|
var map = new Dictionary<string, int>(StringComparer.OrdinalIgnoreCase);
|
|
foreach (var cell in headerRow.CellsUsed())
|
|
{
|
|
var k = NormalizeHeader(cell.GetString());
|
|
if (!string.IsNullOrWhiteSpace(k) && !map.ContainsKey(k)) map[k] = cell.Address.ColumnNumber;
|
|
}
|
|
|
|
int colItem = GetCol(map, "ITEM");
|
|
if (colItem == 0) return;
|
|
|
|
var startRow = headerRow.RowNumber() + 1;
|
|
|
|
await _db.MuregLines.ExecuteDeleteAsync();
|
|
|
|
var buffer = new List<MuregLine>(600);
|
|
|
|
var lastRow = wsM.LastRowUsed()?.RowNumber() ?? startRow;
|
|
for (int r = startRow; r <= lastRow; r++)
|
|
{
|
|
var itemStr = GetCellString(wsM, r, colItem);
|
|
if (string.IsNullOrWhiteSpace(itemStr)) break;
|
|
|
|
var e = new MuregLine
|
|
{
|
|
Id = Guid.NewGuid(),
|
|
Item = TryInt(itemStr),
|
|
|
|
LinhaAntiga = OnlyDigits(GetCellByHeader(wsM, r, map, "LINHA ANTIGA")),
|
|
LinhaNova = OnlyDigits(GetCellByHeader(wsM, r, map, "LINHA NOVA")),
|
|
ICCID = OnlyDigits(GetCellByHeader(wsM, r, map, "ICCID")),
|
|
DataDaMureg = TryDate(wsM, r, map, "DATA DA MUREG"),
|
|
Cliente = GetCellByHeader(wsM, r, map, "CLIENTE"),
|
|
};
|
|
|
|
buffer.Add(e);
|
|
|
|
if (buffer.Count >= 500)
|
|
{
|
|
await _db.MuregLines.AddRangeAsync(buffer);
|
|
await _db.SaveChangesAsync();
|
|
buffer.Clear();
|
|
}
|
|
}
|
|
|
|
if (buffer.Count > 0)
|
|
{
|
|
await _db.MuregLines.AddRangeAsync(buffer);
|
|
await _db.SaveChangesAsync();
|
|
}
|
|
}
|
|
|
|
// ==========================================================
|
|
// ✅ NOVO: IMPORTAÇÃO DO FATURAMENTO (PF/PJ)
|
|
// Tabs: "FATURAMENTO PF" e "FATURAMENTO PJ"
|
|
// ==========================================================
|
|
private async Task ImportBillingFromWorkbook(XLWorkbook wb)
|
|
{
|
|
await _db.BillingClients.ExecuteDeleteAsync();
|
|
|
|
// PF
|
|
var wsPf = wb.Worksheets.FirstOrDefault(w => w.Name.Trim().Equals("FATURAMENTO PF", StringComparison.OrdinalIgnoreCase))
|
|
?? wb.Worksheets.FirstOrDefault(w => w.Name.Trim().ToUpperInvariant().Contains("FATURAMENTO") && w.Name.Trim().ToUpperInvariant().Contains("PF"));
|
|
if (wsPf != null)
|
|
await ImportBillingSheet(wsPf, "PF");
|
|
|
|
// PJ
|
|
var wsPj = wb.Worksheets.FirstOrDefault(w => w.Name.Trim().Equals("FATURAMENTO PJ", StringComparison.OrdinalIgnoreCase))
|
|
?? wb.Worksheets.FirstOrDefault(w => w.Name.Trim().ToUpperInvariant().Contains("FATURAMENTO") && w.Name.Trim().ToUpperInvariant().Contains("PJ"));
|
|
if (wsPj != null)
|
|
await ImportBillingSheet(wsPj, "PJ");
|
|
}
|
|
|
|
private async Task ImportBillingSheet(IXLWorksheet ws, string tipo)
|
|
{
|
|
var headerRow =
|
|
ws.RowsUsed().FirstOrDefault(r =>
|
|
r.CellsUsed().Any(c => NormalizeHeader(c.GetString()) == "CLIENTE"));
|
|
|
|
if (headerRow == null) return;
|
|
|
|
var map = new Dictionary<string, int>(StringComparer.OrdinalIgnoreCase);
|
|
foreach (var cell in headerRow.CellsUsed())
|
|
{
|
|
var k = NormalizeHeader(cell.GetString());
|
|
if (!string.IsNullOrWhiteSpace(k) && !map.ContainsKey(k))
|
|
map[k] = cell.Address.ColumnNumber;
|
|
}
|
|
|
|
var colCliente = GetCol(map, "CLIENTE");
|
|
if (colCliente == 0) return;
|
|
|
|
var colItem = GetCol(map, "ITEM");
|
|
if (colItem == 0 && colCliente > 1) colItem = colCliente - 1;
|
|
|
|
var startRow = headerRow.RowNumber() + 1;
|
|
var lastRow = ws.LastRowUsed()?.RowNumber() ?? startRow;
|
|
|
|
var buffer = new List<BillingClient>(400);
|
|
var seqItem = 0;
|
|
|
|
for (int r = startRow; r <= lastRow; r++)
|
|
{
|
|
var cliente = GetCellString(ws, r, colCliente);
|
|
if (string.IsNullOrWhiteSpace(cliente)) break;
|
|
|
|
seqItem++;
|
|
|
|
var itemStr = colItem > 0 ? GetCellString(ws, r, colItem) : "";
|
|
var item = !string.IsNullOrWhiteSpace(itemStr) ? TryInt(itemStr) : seqItem;
|
|
|
|
var qtdStr = GetCellByAnyHeader(ws, r, map, "QTD DE LINHAS", "QTDDLINHAS", "QTD LINHAS");
|
|
var qtd = TryNullableInt(qtdStr);
|
|
|
|
var franquiaVivoStr = GetCellByAnyHeader(ws, r, map, "FRAQUIA VIVO", "FRANQUIA VIVO");
|
|
var valorContratoVivoStr = GetCellByAnyHeader(ws, r, map, "VALOR CONTRATO VIVO", "VALOR DO CONTRATO VIVO", "VALOR CONTRATO VIVO R$");
|
|
|
|
var franquiaLineStr = GetCellByAnyHeader(ws, r, map, "FRAQUIA LINE", "FRANQUIA LINE");
|
|
var valorContratoLineStr = GetCellByAnyHeader(ws, r, map, "VALOR CONTRATO LINE", "VALOR DO CONTRATO LINE", "VALOR CONTRATO LINE R$");
|
|
|
|
var lucroStr = GetCellByAnyHeader(ws, r, map, "LUCRO");
|
|
|
|
var aparelho = GetCellByAnyHeader(ws, r, map, "APARELHO");
|
|
var formaPagto = GetCellByAnyHeader(ws, r, map, "FORMA DE PAGAMENTO", "FORMA PAGAMENTO", "FORMAPAGAMENTO");
|
|
|
|
var now = DateTime.UtcNow;
|
|
|
|
var e = new BillingClient
|
|
{
|
|
Id = Guid.NewGuid(),
|
|
Tipo = tipo,
|
|
Item = item,
|
|
Cliente = cliente.Trim(),
|
|
QtdLinhas = qtd,
|
|
|
|
FranquiaVivo = TryDecimal(franquiaVivoStr),
|
|
ValorContratoVivo = TryDecimal(valorContratoVivoStr),
|
|
|
|
FranquiaLine = TryDecimal(franquiaLineStr),
|
|
ValorContratoLine = TryDecimal(valorContratoLineStr),
|
|
|
|
Lucro = TryDecimal(lucroStr),
|
|
|
|
Aparelho = string.IsNullOrWhiteSpace(aparelho) ? null : aparelho.Trim(),
|
|
FormaPagamento = string.IsNullOrWhiteSpace(formaPagto) ? null : formaPagto.Trim(),
|
|
|
|
CreatedAt = now,
|
|
UpdatedAt = now
|
|
};
|
|
|
|
buffer.Add(e);
|
|
|
|
if (buffer.Count >= 300)
|
|
{
|
|
await _db.BillingClients.AddRangeAsync(buffer);
|
|
await _db.SaveChangesAsync();
|
|
buffer.Clear();
|
|
}
|
|
}
|
|
|
|
if (buffer.Count > 0)
|
|
{
|
|
await _db.BillingClients.AddRangeAsync(buffer);
|
|
await _db.SaveChangesAsync();
|
|
}
|
|
}
|
|
|
|
// ==========================================================
|
|
// HELPERS
|
|
// ==========================================================
|
|
private static DateTime? ToUtc(DateTime? dt)
|
|
{
|
|
if (dt == null) return null;
|
|
var v = dt.Value;
|
|
return v.Kind == DateTimeKind.Utc ? v :
|
|
(v.Kind == DateTimeKind.Local ? v.ToUniversalTime() : DateTime.SpecifyKind(v, DateTimeKind.Utc));
|
|
}
|
|
|
|
private static MobileLineDetailDto ToDetailDto(MobileLine x) => new()
|
|
{
|
|
Id = x.Id,
|
|
Item = x.Item,
|
|
Conta = x.Conta,
|
|
Linha = x.Linha,
|
|
Chip = x.Chip,
|
|
Cliente = x.Cliente,
|
|
Usuario = x.Usuario,
|
|
PlanoContrato = x.PlanoContrato,
|
|
FranquiaVivo = x.FranquiaVivo,
|
|
ValorPlanoVivo = x.ValorPlanoVivo,
|
|
GestaoVozDados = x.GestaoVozDados,
|
|
Skeelo = x.Skeelo,
|
|
VivoNewsPlus = x.VivoNewsPlus,
|
|
VivoTravelMundo = x.VivoTravelMundo,
|
|
VivoGestaoDispositivo = x.VivoGestaoDispositivo,
|
|
ValorContratoVivo = x.ValorContratoVivo,
|
|
FranquiaLine = x.FranquiaLine,
|
|
FranquiaGestao = x.FranquiaGestao,
|
|
LocacaoAp = x.LocacaoAp,
|
|
ValorContratoLine = x.ValorContratoLine,
|
|
Desconto = x.Desconto,
|
|
Lucro = x.Lucro,
|
|
Status = x.Status,
|
|
DataBloqueio = x.DataBloqueio,
|
|
Skil = x.Skil,
|
|
Modalidade = x.Modalidade,
|
|
Cedente = x.Cedente,
|
|
Solicitante = x.Solicitante,
|
|
DataEntregaOpera = x.DataEntregaOpera,
|
|
DataEntregaCliente = x.DataEntregaCliente,
|
|
VencConta = x.VencConta
|
|
};
|
|
|
|
private static void ApplyReservaRule(MobileLine x)
|
|
{
|
|
if ((x.Cliente ?? "").Trim().ToUpper() == "RESERVA" || (x.Usuario ?? "").Trim().ToUpper() == "RESERVA")
|
|
{
|
|
x.Cliente = "RESERVA";
|
|
x.Usuario = "RESERVA";
|
|
x.Skil = "RESERVA";
|
|
}
|
|
}
|
|
|
|
private static int GetCol(Dictionary<string, int> map, string name)
|
|
=> map.TryGetValue(NormalizeHeader(name), out var c) ? c : 0;
|
|
|
|
private static string GetCellByHeader(IXLWorksheet ws, int row, Dictionary<string, int> map, string header)
|
|
{
|
|
var k = NormalizeHeader(header);
|
|
return map.TryGetValue(k, out var c) ? GetCellString(ws, row, c) : "";
|
|
}
|
|
|
|
private static string GetCellByAnyHeader(IXLWorksheet ws, int row, Dictionary<string, int> map, params string[] headers)
|
|
{
|
|
foreach (var h in headers)
|
|
{
|
|
var k = NormalizeHeader(h);
|
|
if (map.TryGetValue(k, out var c))
|
|
return GetCellString(ws, row, c);
|
|
}
|
|
return "";
|
|
}
|
|
|
|
private static string GetCellString(IXLWorksheet ws, int row, int col)
|
|
{
|
|
return (ws.Cell(row, col).GetValue<string>() ?? "").Trim();
|
|
}
|
|
|
|
private static DateTime? TryDate(IXLWorksheet ws, int row, Dictionary<string, int> map, string header)
|
|
{
|
|
var k = NormalizeHeader(header);
|
|
if (!map.TryGetValue(k, out var c)) return null;
|
|
|
|
var cell = ws.Cell(row, c);
|
|
|
|
if (cell.DataType == XLDataType.DateTime)
|
|
return ToUtc(cell.GetDateTime());
|
|
|
|
var s = cell.GetValue<string>()?.Trim();
|
|
if (string.IsNullOrWhiteSpace(s)) return null;
|
|
|
|
if (DateTime.TryParse(s, new CultureInfo("pt-BR"), DateTimeStyles.None, out var d))
|
|
return ToUtc(d);
|
|
|
|
return null;
|
|
}
|
|
|
|
private static decimal? TryDecimal(string? s)
|
|
{
|
|
if (string.IsNullOrWhiteSpace(s)) return null;
|
|
s = s.Replace("R$", "").Trim();
|
|
|
|
if (decimal.TryParse(s, NumberStyles.Any, new CultureInfo("pt-BR"), out var d)) return d;
|
|
if (decimal.TryParse(s, NumberStyles.Any, CultureInfo.InvariantCulture, out d)) return d;
|
|
|
|
return null;
|
|
}
|
|
|
|
private static int TryInt(string s)
|
|
=> int.TryParse(OnlyDigits(s), out var n) ? n : 0;
|
|
|
|
private static int? TryNullableInt(string? s)
|
|
{
|
|
if (string.IsNullOrWhiteSpace(s)) return null;
|
|
var d = OnlyDigits(s);
|
|
if (string.IsNullOrWhiteSpace(d)) return null;
|
|
return int.TryParse(d, out var n) ? n : null;
|
|
}
|
|
|
|
private static string OnlyDigits(string? s)
|
|
{
|
|
if (string.IsNullOrWhiteSpace(s)) return "";
|
|
var sb = new StringBuilder();
|
|
foreach (var c in s) if (char.IsDigit(c)) sb.Append(c);
|
|
return sb.ToString();
|
|
}
|
|
|
|
private static string NormalizeHeader(string? s)
|
|
{
|
|
if (string.IsNullOrWhiteSpace(s)) return "";
|
|
s = s.Trim().ToUpperInvariant().Normalize(NormalizationForm.FormD);
|
|
|
|
var sb = new StringBuilder();
|
|
foreach (var c in s)
|
|
if (CharUnicodeInfo.GetUnicodeCategory(c) != UnicodeCategory.NonSpacingMark)
|
|
sb.Append(c);
|
|
|
|
return sb.ToString()
|
|
.Normalize(NormalizationForm.FormC)
|
|
.Replace("ITEM", "ITEM")
|
|
.Replace("USUARIO", "USUARIO")
|
|
.Replace("GESTAO", "GESTAO")
|
|
.Replace("LOCACAO", "LOCACAO")
|
|
.Replace(" ", "");
|
|
}
|
|
}
|
|
}
|