2896 lines
118 KiB
C#
2896 lines
118 KiB
C#
using ClosedXML.Excel;
|
|
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.Http;
|
|
using Microsoft.AspNetCore.Mvc;
|
|
using Microsoft.EntityFrameworkCore;
|
|
using System;
|
|
using System.Collections.Generic;
|
|
using System.Globalization;
|
|
using System.Linq;
|
|
using System.Text;
|
|
using System.Threading.Tasks;
|
|
|
|
namespace line_gestao_api.Controllers
|
|
{
|
|
[ApiController]
|
|
[Route("api/[controller]")]
|
|
//[Authorize]
|
|
public class LinesController : ControllerBase
|
|
{
|
|
private readonly AppDbContext _db;
|
|
private readonly ParcelamentosImportService _parcelamentosImportService;
|
|
private static readonly List<AccountCompanyDto> AccountCompanies = new()
|
|
{
|
|
new AccountCompanyDto
|
|
{
|
|
Empresa = "CLARO LINE MÓVEL",
|
|
Contas = new List<string> { "172593311", "172593840" }
|
|
},
|
|
new AccountCompanyDto
|
|
{
|
|
Empresa = "VIVO MACROPHONY",
|
|
Contas = new List<string> { "0430237019", "0437488125", "0449508564", "0454371844" }
|
|
},
|
|
new AccountCompanyDto
|
|
{
|
|
Empresa = "VIVO LINE MÓVEL",
|
|
Contas = new List<string> { "0435288088" }
|
|
},
|
|
new AccountCompanyDto
|
|
{
|
|
Empresa = "TIM LINE MÓVEL",
|
|
Contas = new List<string> { "0072046192" }
|
|
}
|
|
};
|
|
|
|
public LinesController(AppDbContext db, ParcelamentosImportService parcelamentosImportService)
|
|
{
|
|
_db = db;
|
|
_parcelamentosImportService = parcelamentosImportService;
|
|
}
|
|
|
|
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();
|
|
var reservaFilter = false;
|
|
|
|
// Filtro SKIL
|
|
if (!string.IsNullOrWhiteSpace(skil))
|
|
{
|
|
var sSkil = skil.Trim();
|
|
if (sSkil.Equals("RESERVA", StringComparison.OrdinalIgnoreCase))
|
|
{
|
|
reservaFilter = true;
|
|
query = query.Where(x =>
|
|
EF.Functions.ILike((x.Cliente ?? "").Trim(), "%RESERVA%") ||
|
|
EF.Functions.ILike((x.Usuario ?? "").Trim(), "%RESERVA%") ||
|
|
EF.Functions.ILike((x.Skil ?? "").Trim(), "%RESERVA%"));
|
|
}
|
|
else
|
|
query = query.Where(x => EF.Functions.ILike(x.Skil ?? "", $"%{sSkil}%"));
|
|
}
|
|
|
|
if (!reservaFilter)
|
|
{
|
|
query = query.Where(x => !string.IsNullOrEmpty(x.Cliente));
|
|
}
|
|
|
|
// 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 = reservaFilter
|
|
? query.GroupBy(_ => "RESERVA")
|
|
.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%"))
|
|
})
|
|
: 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 (ACEITA SKIL)
|
|
// ==========================================================
|
|
[HttpGet("clients")]
|
|
public async Task<ActionResult<List<string>>> GetClients([FromQuery] string? skil)
|
|
{
|
|
var query = _db.MobileLines.AsNoTracking();
|
|
|
|
if (!string.IsNullOrWhiteSpace(skil))
|
|
{
|
|
var sSkil = skil.Trim();
|
|
if (sSkil.Equals("RESERVA", StringComparison.OrdinalIgnoreCase))
|
|
query = query.Where(x =>
|
|
EF.Functions.ILike((x.Cliente ?? "").Trim(), "%RESERVA%") ||
|
|
EF.Functions.ILike((x.Usuario ?? "").Trim(), "%RESERVA%") ||
|
|
EF.Functions.ILike((x.Skil ?? "").Trim(), "%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);
|
|
}
|
|
|
|
// ==========================================================
|
|
// ✅ 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),
|
|
"aparelho" => desc ? q.OrderByDescending(x => x.Aparelho ?? "").ThenBy(x => x.Cliente) : q.OrderBy(x => x.Aparelho ?? "").ThenBy(x => x.Cliente),
|
|
"formapagamento" => desc ? q.OrderByDescending(x => x.FormaPagamento ?? "").ThenBy(x => x.Cliente) : q.OrderBy(x => x.FormaPagamento ?? "").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);
|
|
}
|
|
|
|
[HttpGet("account-companies")]
|
|
public ActionResult<List<AccountCompanyDto>> GetAccountCompanies()
|
|
{
|
|
var items = AccountCompanies
|
|
.Select(x => new AccountCompanyDto
|
|
{
|
|
Empresa = x.Empresa,
|
|
Contas = x.Contas.ToList()
|
|
})
|
|
.ToList();
|
|
|
|
return Ok(items);
|
|
}
|
|
|
|
[HttpGet("accounts")]
|
|
public ActionResult<List<string>> GetAccounts([FromQuery] string? empresa)
|
|
{
|
|
if (string.IsNullOrWhiteSpace(empresa))
|
|
return Ok(new List<string>());
|
|
|
|
var target = empresa.Trim();
|
|
|
|
var contas = AccountCompanies
|
|
.FirstOrDefault(x => string.Equals(x.Empresa, target, StringComparison.OrdinalIgnoreCase))
|
|
?.Contas
|
|
?.ToList()
|
|
?? new List<string>();
|
|
|
|
return Ok(contas);
|
|
}
|
|
|
|
// ==========================================================
|
|
// ✅ 2.1 ENDPOINT: LINHAS POR CLIENTE (para SELECT do MUREG)
|
|
// GET: /api/lines/by-client?cliente=...
|
|
// ==========================================================
|
|
[HttpGet("by-client")]
|
|
public async Task<ActionResult<List<LineOptionDto>>> GetLinesByClient([FromQuery] string cliente)
|
|
{
|
|
if (string.IsNullOrWhiteSpace(cliente))
|
|
return Ok(new List<LineOptionDto>());
|
|
|
|
var c = cliente.Trim();
|
|
|
|
// ⚠️ use ILike para não depender de maiúscula/minúscula
|
|
var items = await _db.MobileLines
|
|
.AsNoTracking()
|
|
.Where(x => x.Cliente != null && EF.Functions.ILike(x.Cliente, c))
|
|
.Where(x => x.Linha != null && x.Linha != "")
|
|
.OrderBy(x => x.Item)
|
|
.Select(x => new LineOptionDto
|
|
{
|
|
Id = x.Id,
|
|
Item = x.Item,
|
|
Linha = x.Linha,
|
|
Chip = x.Chip,
|
|
Cliente = x.Cliente,
|
|
Usuario = x.Usuario,
|
|
Skil = x.Skil
|
|
})
|
|
.ToListAsync();
|
|
|
|
return Ok(items);
|
|
}
|
|
|
|
// ==========================================================
|
|
// ✅ 3. GET ALL (GERAL)
|
|
// ==========================================================
|
|
[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 =>
|
|
EF.Functions.ILike((x.Cliente ?? "").Trim(), "%RESERVA%") ||
|
|
EF.Functions.ILike((x.Usuario ?? "").Trim(), "%RESERVA%") ||
|
|
EF.Functions.ILike((x.Skil ?? "").Trim(), "%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
|
|
// ==========================================================
|
|
[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 now = DateTime.UtcNow;
|
|
|
|
var planSuggestion = await AutoFillRules.ResolvePlanSuggestionAsync(_db, req.PlanoContrato);
|
|
var franquiaVivo = req.FranquiaVivo ?? planSuggestion?.FranquiaGb;
|
|
var valorPlanoVivo = req.ValorPlanoVivo ?? planSuggestion?.ValorPlano;
|
|
|
|
var newLine = new MobileLine
|
|
{
|
|
Id = Guid.NewGuid(),
|
|
Item = nextItem,
|
|
Cliente = req.Cliente.Trim().ToUpper(),
|
|
Linha = linhaLimpa,
|
|
Chip = string.IsNullOrWhiteSpace(chipLimpo) ? null : 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 = franquiaVivo,
|
|
ValorPlanoVivo = valorPlanoVivo,
|
|
GestaoVozDados = req.GestaoVozDados,
|
|
Skeelo = req.Skeelo,
|
|
VivoNewsPlus = req.VivoNewsPlus,
|
|
VivoTravelMundo = req.VivoTravelMundo,
|
|
VivoSync = req.VivoSync,
|
|
VivoGestaoDispositivo = req.VivoGestaoDispositivo,
|
|
ValorContratoVivo = req.ValorContratoVivo,
|
|
FranquiaLine = req.FranquiaLine,
|
|
FranquiaGestao = req.FranquiaGestao,
|
|
LocacaoAp = req.LocacaoAp,
|
|
ValorContratoLine = req.ValorContratoLine,
|
|
Desconto = req.Desconto,
|
|
Lucro = req.Lucro,
|
|
TipoDeChip = req.TipoDeChip?.Trim(),
|
|
|
|
CreatedAt = now,
|
|
UpdatedAt = now
|
|
};
|
|
|
|
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
|
|
// ==========================================================
|
|
[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 = string.IsNullOrWhiteSpace(newLinha) ? null : newLinha;
|
|
|
|
var newChip = OnlyDigits(req.Chip);
|
|
x.Chip = string.IsNullOrWhiteSpace(newChip) ? null : newChip;
|
|
|
|
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.VivoSync = req.VivoSync;
|
|
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();
|
|
x.TipoDeChip = req.TipoDeChip?.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
|
|
// ==========================================================
|
|
[HttpPost("import-excel")]
|
|
[Authorize]
|
|
[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.");
|
|
|
|
await using var tx = await _db.Database.BeginTransactionAsync();
|
|
|
|
try
|
|
{
|
|
using var stream = file.OpenReadStream();
|
|
using var wb = new XLWorkbook(stream);
|
|
|
|
// =========================
|
|
// ✅ IMPORTA GERAL
|
|
// =========================
|
|
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 na aba GERAL.");
|
|
|
|
var map = BuildHeaderMap(headerRow);
|
|
|
|
int colItem = GetCol(map, "ITEM");
|
|
if (colItem == 0) return BadRequest("Coluna 'ITEM' não encontrada na aba GERAL.");
|
|
|
|
var startRow = headerRow.RowNumber() + 1;
|
|
|
|
// limpa tudo antes (idempotente)
|
|
// ⚠️ limpa dependências primeiro (evita FK Restrict da MUREG)
|
|
await _db.MuregLines.ExecuteDeleteAsync();
|
|
await _db.MobileLines.ExecuteDeleteAsync();
|
|
|
|
var buffer = new List<MobileLine>(600);
|
|
var imported = 0;
|
|
|
|
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 linhaDigits = OnlyDigits(GetCellByHeader(ws, r, map, "LINHA"));
|
|
var chipDigits = OnlyDigits(GetCellByHeader(ws, r, map, "CHIP"));
|
|
|
|
// ✅ se vier vazio, vira null (evita duplicidade de "")
|
|
var linhaVal = string.IsNullOrWhiteSpace(linhaDigits) ? null : linhaDigits;
|
|
var chipVal = string.IsNullOrWhiteSpace(chipDigits) ? null : chipDigits;
|
|
|
|
var now = DateTime.UtcNow;
|
|
|
|
var e = new MobileLine
|
|
{
|
|
Id = Guid.NewGuid(),
|
|
Item = TryInt(itemStr),
|
|
Conta = GetCellByHeader(ws, r, map, "CONTA"),
|
|
Linha = linhaVal,
|
|
Chip = chipVal,
|
|
Cliente = GetCellByHeader(ws, r, map, "CLIENTE"),
|
|
Usuario = GetCellByHeader(ws, r, map, "USUARIO"),
|
|
PlanoContrato = GetCellByHeader(ws, r, map, "PLANO CONTRATO"),
|
|
|
|
FranquiaVivo = TryDecimal(GetCellByHeaderAny(ws, r, map, "FRAQUIA", "FRANQUIA", "FRANQUIA VIVO", "FRAQUIA VIVO")),
|
|
ValorPlanoVivo = TryDecimal(GetCellByHeaderAny(ws, r, map, "VALOR DO PLANO R$", "VALOR DO PLANO", "VALORPLANO")),
|
|
GestaoVozDados = TryDecimal(GetCellByHeaderAny(ws, r, map, "GESTAO VOZ E DADOS R$", "GESTAO VOZ E DADOS", "GESTAOVOZEDADOS")),
|
|
Skeelo = TryDecimal(GetCellByHeaderAny(ws, r, map, "SKEELO")),
|
|
VivoNewsPlus = TryDecimal(GetCellByHeaderAny(ws, r, map, "VIVO NEWS PLUS")),
|
|
VivoTravelMundo = TryDecimal(GetCellByHeaderAny(ws, r, map, "VIVO TRAVEL MUNDO")),
|
|
VivoSync = TryDecimal(GetCellByHeaderAny(ws, r, map, "VIVO SYNC")),
|
|
VivoGestaoDispositivo = TryDecimal(GetCellByHeaderAny(ws, r, map, "VIVO GESTAO DISPOSITIVO")),
|
|
ValorContratoVivo = TryDecimal(GetCellByHeaderAny(ws, r, map, "VALOR CONTRATO VIVO", "VALOR DO CONTRATO VIVO")),
|
|
FranquiaLine = TryDecimal(GetCellByHeaderAny(ws, r, map, "FRANQUIA LINE", "FRAQUIA LINE")),
|
|
FranquiaGestao = TryDecimal(GetCellByHeaderAny(ws, r, map, "FRANQUIA GESTAO", "FRAQUIA GESTAO")),
|
|
LocacaoAp = TryDecimal(GetCellByHeaderAny(ws, r, map, "LOCACAO AP.", "LOCACAO AP", "LOCACAOAP")),
|
|
ValorContratoLine = TryDecimal(GetCellByHeaderAny(ws, r, map, "VALOR CONTRATO LINE", "VALOR DO CONTRATO LINE")),
|
|
Desconto = TryDecimal(GetCellByHeaderAny(ws, r, map, "DESCONTO")),
|
|
Lucro = TryDecimal(GetCellByHeaderAny(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"),
|
|
TipoDeChip = GetCellByHeaderAny(ws, r, map, "TIPO DE CHIP", "TIPO CHIP"),
|
|
CreatedAt = now,
|
|
UpdatedAt = now
|
|
};
|
|
|
|
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 (ALTERADO: NÃO ESTOURA ERRO SE LINHANOVA JÁ EXISTIR)
|
|
// =========================
|
|
await ImportMuregFromWorkbook(wb);
|
|
|
|
// =========================
|
|
// ✅ IMPORTA FATURAMENTO PF/PJ
|
|
// =========================
|
|
await ImportBillingFromWorkbook(wb);
|
|
|
|
// =========================
|
|
// ✅ IMPORTA DADOS DOS USUÁRIOS (UserDatas)
|
|
// =========================
|
|
await ImportUserDatasFromWorkbook(wb);
|
|
|
|
// =========================
|
|
// ✅ IMPORTA VIGÊNCIA
|
|
// =========================
|
|
await ImportVigenciaFromWorkbook(wb);
|
|
|
|
// =========================
|
|
// ✅ IMPORTA TROCA DE NÚMERO
|
|
// =========================
|
|
await ImportTrocaNumeroFromWorkbook(wb);
|
|
|
|
// =========================
|
|
// ✅ IMPORTA CHIPS VIRGENS
|
|
// =========================
|
|
await ImportChipsVirgensFromWorkbook(wb);
|
|
|
|
// =========================
|
|
// ✅ IMPORTA CONTROLE DE RECEBIDOS
|
|
// =========================
|
|
await ImportControleRecebidosFromWorkbook(wb);
|
|
|
|
// =========================
|
|
// ✅ IMPORTA RESUMO
|
|
// =========================
|
|
await ImportResumoFromWorkbook(wb);
|
|
|
|
// =========================
|
|
// ✅ IMPORTA PARCELAMENTOS
|
|
// =========================
|
|
var parcelamentosSummary = await _parcelamentosImportService.ImportFromWorkbookAsync(wb, replaceAll: true);
|
|
|
|
await tx.CommitAsync();
|
|
return Ok(new ImportResultDto { Imported = imported, Parcelamentos = parcelamentosSummary });
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
await tx.RollbackAsync();
|
|
return StatusCode(500, new { message = "Erro ao importar Excel.", detail = ex.Message });
|
|
}
|
|
}
|
|
|
|
// ==========================================================
|
|
// ✅ IMPORTAÇÃO DA ABA MUREG
|
|
// ✅ NOVA REGRA:
|
|
// - Se LinhaNova já existir em OUTRA linha da GERAL => NÃO atualiza a GERAL, NÃO dá erro
|
|
// - Mesmo assim salva o registro na MUREG normalmente
|
|
// - Evita duplicidade na coluna Linha da GERAL
|
|
// ==========================================================
|
|
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 = BuildHeaderMap(headerRow);
|
|
|
|
int colItem = GetCol(map, "ITEM");
|
|
if (colItem == 0) return;
|
|
|
|
var startRow = headerRow.RowNumber() + 1;
|
|
|
|
// limpa MUREG antes (idempotente)
|
|
await _db.MuregLines.ExecuteDeleteAsync();
|
|
|
|
// ✅ dicionários para resolver MobileLineId por Linha/Chip
|
|
var mobilePairs = await _db.MobileLines
|
|
.AsNoTracking()
|
|
.Select(x => new { x.Id, x.Linha, x.Chip })
|
|
.ToListAsync();
|
|
|
|
var mobileByLinha = new Dictionary<string, Guid>(StringComparer.Ordinal);
|
|
var mobileByChip = new Dictionary<string, Guid>(StringComparer.Ordinal);
|
|
|
|
foreach (var m in mobilePairs)
|
|
{
|
|
if (!string.IsNullOrWhiteSpace(m.Linha))
|
|
{
|
|
var k = OnlyDigits(m.Linha);
|
|
if (!string.IsNullOrWhiteSpace(k) && !mobileByLinha.ContainsKey(k))
|
|
mobileByLinha[k] = m.Id;
|
|
}
|
|
|
|
if (!string.IsNullOrWhiteSpace(m.Chip))
|
|
{
|
|
var k = OnlyDigits(m.Chip);
|
|
if (!string.IsNullOrWhiteSpace(k) && !mobileByChip.ContainsKey(k))
|
|
mobileByChip[k] = m.Id;
|
|
}
|
|
}
|
|
|
|
// ✅ cache de entidades tracked para atualizar a GERAL sem consultar toda hora
|
|
var mobileCache = new Dictionary<Guid, MobileLine>();
|
|
|
|
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 linhaAntiga = NullIfEmptyDigits(GetCellByHeader(wsM, r, map, "LINHA ANTIGA"));
|
|
var linhaNova = NullIfEmptyDigits(GetCellByHeader(wsM, r, map, "LINHA NOVA"));
|
|
var iccid = NullIfEmptyDigits(GetCellByHeader(wsM, r, map, "ICCID"));
|
|
var dataMureg = TryDate(wsM, r, map, "DATA DA MUREG");
|
|
|
|
// ✅ resolve MobileLineId (prioridade: LinhaAntiga, depois ICCID)
|
|
Guid mobileLineId = Guid.Empty;
|
|
|
|
if (!string.IsNullOrWhiteSpace(linhaAntiga) && mobileByLinha.TryGetValue(linhaAntiga, out var idPorLinha))
|
|
mobileLineId = idPorLinha;
|
|
else if (!string.IsNullOrWhiteSpace(iccid) && mobileByChip.TryGetValue(iccid, out var idPorChip))
|
|
mobileLineId = idPorChip;
|
|
|
|
// Se não encontrou correspondência na GERAL, não dá pra salvar (MobileLineId é obrigatório)
|
|
if (mobileLineId == Guid.Empty)
|
|
continue;
|
|
|
|
// ✅ snapshot da linha antiga: se vier vazia na planilha, pega a linha atual da GERAL
|
|
string? linhaAntigaSnapshot = linhaAntiga;
|
|
if (string.IsNullOrWhiteSpace(linhaAntigaSnapshot))
|
|
{
|
|
if (!mobileCache.TryGetValue(mobileLineId, out var mobTmp))
|
|
{
|
|
mobTmp = await _db.MobileLines.FirstOrDefaultAsync(x => x.Id == mobileLineId);
|
|
if (mobTmp != null) mobileCache[mobileLineId] = mobTmp;
|
|
}
|
|
|
|
linhaAntigaSnapshot = mobTmp?.Linha;
|
|
}
|
|
|
|
var now = DateTime.UtcNow;
|
|
|
|
// ✅ salva MUREG sempre
|
|
var e = new MuregLine
|
|
{
|
|
Id = Guid.NewGuid(),
|
|
Item = TryInt(itemStr),
|
|
MobileLineId = mobileLineId,
|
|
LinhaAntiga = linhaAntigaSnapshot,
|
|
LinhaNova = linhaNova,
|
|
ICCID = iccid,
|
|
DataDaMureg = dataMureg,
|
|
CreatedAt = now,
|
|
UpdatedAt = now
|
|
};
|
|
|
|
buffer.Add(e);
|
|
|
|
// ✅ REFLETE NA GERAL (somente se NÃO houver conflito)
|
|
if (!string.IsNullOrWhiteSpace(linhaNova))
|
|
{
|
|
// Se LinhaNova já existe na GERAL em OUTRA MobileLine => ignora update (não duplica)
|
|
if (mobileByLinha.TryGetValue(linhaNova, out var idJaExiste) && idJaExiste != mobileLineId)
|
|
{
|
|
// ignora update da GERAL
|
|
}
|
|
else
|
|
{
|
|
// carrega entity tracked (cache) e atualiza
|
|
if (!mobileCache.TryGetValue(mobileLineId, out var mobile))
|
|
{
|
|
mobile = await _db.MobileLines.FirstOrDefaultAsync(x => x.Id == mobileLineId);
|
|
if (mobile != null) mobileCache[mobileLineId] = mobile;
|
|
}
|
|
|
|
if (mobile != null)
|
|
{
|
|
// valida conflito de ICCID também (evita duplicidade de CHIP)
|
|
var iccidConflita = false;
|
|
if (!string.IsNullOrWhiteSpace(iccid) &&
|
|
mobileByChip.TryGetValue(iccid, out var chipJaExiste) &&
|
|
chipJaExiste != mobileLineId)
|
|
{
|
|
iccidConflita = true;
|
|
}
|
|
|
|
// atualiza Linha
|
|
mobile.Linha = linhaNova;
|
|
|
|
// atualiza Chip se ICCID vier e NÃO conflitar
|
|
if (!string.IsNullOrWhiteSpace(iccid) && !iccidConflita)
|
|
mobile.Chip = iccid;
|
|
|
|
mobile.UpdatedAt = DateTime.UtcNow;
|
|
|
|
// atualiza os dicionários para próximas linhas do MUREG
|
|
mobileByLinha[linhaNova] = mobileLineId;
|
|
|
|
if (!string.IsNullOrWhiteSpace(iccid) && !iccidConflita)
|
|
mobileByChip[iccid] = mobileLineId;
|
|
}
|
|
}
|
|
}
|
|
|
|
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();
|
|
}
|
|
}
|
|
|
|
// ==========================================================
|
|
// ✅ IMPORTAÇÃO DO FATURAMENTO (PF/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 headerRowIndex = headerRow.RowNumber();
|
|
var map = BuildHeaderMap(headerRow);
|
|
|
|
// linha acima (grupos VIVO / LINE)
|
|
var groupRowIndex = Math.Max(1, headerRowIndex - 1);
|
|
var groupRow = ws.Row(groupRowIndex);
|
|
|
|
var lastCol = GetLastUsedColumn(ws, headerRowIndex);
|
|
|
|
var colItem = FindColByAny(headerRow, lastCol, "ITEM");
|
|
var colCliente = FindColByAny(headerRow, lastCol, "CLIENTE");
|
|
if (colCliente == 0) return;
|
|
|
|
var colQtd = FindColByAny(headerRow, lastCol, "QTD DE LINHAS", "QTD LINHAS", "QTDDLINHAS");
|
|
var colLucro = FindColByAny(headerRow, lastCol, "LUCRO");
|
|
var colAparelho = FindColByAny(headerRow, lastCol, "APARELHO");
|
|
var colForma = FindColByAny(headerRow, lastCol, "FORMA DE PAGAMENTO", "FORMA PAGAMENTO", "FORMAPAGAMENTO");
|
|
|
|
var hasAnyGroup = RowHasAnyText(groupRow);
|
|
|
|
int colFranquiaVivo = 0;
|
|
int colValorVivo = 0;
|
|
int colFranquiaLine = 0;
|
|
int colValorLine = 0;
|
|
|
|
if (hasAnyGroup)
|
|
{
|
|
colFranquiaVivo = FindColInGroup(groupRow, headerRow, lastCol, "VIVO",
|
|
"FRANQUIA", "FRAQUIA", "FRANQUIAVIVO", "FRAQUIAVIVO");
|
|
|
|
colValorVivo = FindColInGroup(groupRow, headerRow, lastCol, "VIVO",
|
|
"VALOR CONTRATO VIVO", "VALOR DO CONTRATO VIVO", "VALOR VIVO", "VALOR",
|
|
"R$", "RS", "");
|
|
|
|
colFranquiaLine = FindColInGroup(groupRow, headerRow, lastCol, "LINE",
|
|
"FRANQUIA LINE", "FRAQUIA LINE", "FRANQUIA", "FRAQUIA", "FRANQUIALINE", "FRAQUIALINE");
|
|
|
|
colValorLine = FindColInGroup(groupRow, headerRow, lastCol, "LINE",
|
|
"VALOR CONTRATO LINE", "VALOR DO CONTRATO LINE", "VALOR LINE", "VALOR",
|
|
"R$", "RS", "");
|
|
|
|
if (colValorVivo == 0 && colFranquiaVivo > 0)
|
|
{
|
|
var cand = colFranquiaVivo + 1;
|
|
if (cand <= lastCol)
|
|
{
|
|
var g = GetMergedGroupKeyAt(groupRow, cand);
|
|
if (!string.IsNullOrWhiteSpace(g) && g.Contains(NormalizeHeader("VIVO")))
|
|
colValorVivo = cand;
|
|
}
|
|
}
|
|
|
|
if (colValorLine == 0 && colFranquiaLine > 0)
|
|
{
|
|
var cand = colFranquiaLine + 1;
|
|
if (cand <= lastCol)
|
|
{
|
|
var g = GetMergedGroupKeyAt(groupRow, cand);
|
|
if (!string.IsNullOrWhiteSpace(g) && g.Contains(NormalizeHeader("LINE")))
|
|
colValorLine = cand;
|
|
}
|
|
}
|
|
}
|
|
|
|
if (colFranquiaVivo == 0 || colValorVivo == 0 || colFranquiaLine == 0 || colValorLine == 0)
|
|
{
|
|
if (colFranquiaLine == 0)
|
|
colFranquiaLine = GetColAny(map, "FRAQUIA LINE", "FRANQUIA LINE", "FRANQUIALINE", "FRAQUIALINE");
|
|
|
|
if (colFranquiaVivo == 0)
|
|
{
|
|
colFranquiaVivo = GetColAny(map, "FRAQUIA VIVO", "FRANQUIA VIVO", "FRANQUIAVIVO", "FRAQUIAVIVO");
|
|
if (colFranquiaVivo == 0)
|
|
{
|
|
var colFranquia = GetColAny(map, "FRAQUIA", "FRANQUIA");
|
|
if (colFranquia != 0 && colFranquia != colFranquiaLine) colFranquiaVivo = colFranquia;
|
|
}
|
|
}
|
|
|
|
if (colValorVivo == 0)
|
|
colValorVivo = GetColAny(map,
|
|
"VALOR CONTRATO VIVO",
|
|
"VALOR DO CONTRATO VIVO",
|
|
"VALOR CONTRATO VIVO R$",
|
|
"VALOR VIVO");
|
|
|
|
if (colValorLine == 0)
|
|
colValorLine = GetColAny(map,
|
|
"VALOR CONTRATO LINE",
|
|
"VALOR DO CONTRATO LINE",
|
|
"VALOR CONTRATO LINE R$",
|
|
"VALOR LINE");
|
|
}
|
|
|
|
var colRazao = GetColAny(map, "RAZAO SOCIAL", "RAZÃO SOCIAL", "RAZAOSOCIAL");
|
|
var colNome = GetColAny(map, "NOME", "NOME COMPLETO");
|
|
|
|
var startRow = headerRowIndex + 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 = colCliente > 0 ? GetCellString(ws, r, colCliente) : "";
|
|
var nome = colNome > 0 ? GetCellString(ws, r, colNome) : "";
|
|
var razao = colRazao > 0 ? GetCellString(ws, r, colRazao) : "";
|
|
|
|
if (string.IsNullOrWhiteSpace(cliente))
|
|
{
|
|
if (!string.IsNullOrWhiteSpace(razao)) cliente = razao;
|
|
else if (!string.IsNullOrWhiteSpace(nome)) cliente = nome;
|
|
}
|
|
|
|
if (string.IsNullOrWhiteSpace(cliente)) break;
|
|
|
|
seqItem++;
|
|
|
|
var itemStr = colItem > 0 ? GetCellString(ws, r, colItem) : "";
|
|
var item = !string.IsNullOrWhiteSpace(itemStr) ? TryInt(itemStr) : seqItem;
|
|
|
|
int? qtd = null;
|
|
if (colQtd > 0)
|
|
{
|
|
var qtdStr = GetCellString(ws, r, colQtd);
|
|
qtd = TryNullableInt(qtdStr);
|
|
}
|
|
|
|
var franquiaVivoStr = colFranquiaVivo > 0 ? GetCellString(ws, r, colFranquiaVivo) : "";
|
|
var franquiaLineStr = colFranquiaLine > 0 ? GetCellString(ws, r, colFranquiaLine) : "";
|
|
|
|
var valorContratoVivoStr = colValorVivo > 0 ? GetCellString(ws, r, colValorVivo) : "";
|
|
var valorContratoLineStr = colValorLine > 0 ? GetCellString(ws, r, colValorLine) : "";
|
|
|
|
var lucroStr = colLucro > 0 ? GetCellString(ws, r, colLucro) : "";
|
|
var aparelho = colAparelho > 0 ? GetCellString(ws, r, colAparelho) : "";
|
|
var formaPagto = colForma > 0 ? GetCellString(ws, r, colForma) : "";
|
|
|
|
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();
|
|
}
|
|
}
|
|
|
|
// ==========================================================
|
|
// ✅ IMPORTAÇÃO: DADOS DOS USUÁRIOS (UserDatas)
|
|
// ==========================================================
|
|
private async Task ImportUserDatasFromWorkbook(XLWorkbook wb)
|
|
{
|
|
var ws = wb.Worksheets.FirstOrDefault(w => NormalizeHeader(w.Name) == NormalizeHeader("DADOS DOS USUÁRIOS"))
|
|
?? wb.Worksheets.FirstOrDefault(w => NormalizeHeader(w.Name) == NormalizeHeader("DADOS DOS USUARIOS"))
|
|
?? wb.Worksheets.FirstOrDefault(w =>
|
|
NormalizeHeader(w.Name).Contains("DADOS") &&
|
|
NormalizeHeader(w.Name).Contains("USUAR"));
|
|
|
|
if (ws == null) return;
|
|
|
|
var headerRow = ws.RowsUsed().FirstOrDefault(r =>
|
|
r.CellsUsed().Any(c =>
|
|
NormalizeHeader(c.GetString()) == "ITEM" ||
|
|
NormalizeHeader(c.GetString()) == "CLIENTE" ||
|
|
NormalizeHeader(c.GetString()) == "RAZAO SOCIAL" ||
|
|
NormalizeHeader(c.GetString()) == "NOME"));
|
|
|
|
if (headerRow == null) return;
|
|
|
|
var map = BuildHeaderMap(headerRow);
|
|
|
|
var colItem = GetCol(map, "ITEM");
|
|
var colCliente = GetCol(map, "CLIENTE");
|
|
var colRazao = GetColAny(map, "RAZAO SOCIAL", "RAZÃO SOCIAL", "RAZAOSOCIAL");
|
|
var colNome = GetColAny(map, "NOME", "NOME COMPLETO");
|
|
var colLinha = GetCol(map, "LINHA");
|
|
|
|
if (colCliente == 0 && colRazao == 0 && colNome == 0) return;
|
|
|
|
await _db.UserDatas.ExecuteDeleteAsync();
|
|
|
|
var startRow = headerRow.RowNumber() + 1;
|
|
var lastRow = ws.LastRowUsed()?.RowNumber() ?? startRow;
|
|
|
|
var buffer = new List<UserData>(500);
|
|
var seq = 0;
|
|
|
|
var colCpf = GetColAny(map, "CPF");
|
|
var colCnpj = GetColAny(map, "CNPJ");
|
|
var colRg = GetColAny(map, "RG");
|
|
var colEmail = GetColAny(map, "EMAIL", "E-MAIL");
|
|
var colEndereco = GetColAny(map, "ENDERECO", "ENDEREÇO");
|
|
var colCelular = GetColAny(map, "CELULAR", "CEL");
|
|
var colFixo = GetColAny(map, "TELEFONE FIXO", "TELEFONEFIXO", "FIXO", "TELEFONE");
|
|
|
|
var colDataNasc = GetColAny(map,
|
|
"DATA DE NASCIMENTO",
|
|
"DATADENASCIMENTO",
|
|
"DATA NASCIMENTO",
|
|
"DATANASCIMENTO",
|
|
"NASCIMENTO",
|
|
"DTNASC");
|
|
|
|
for (int r = startRow; r <= lastRow; r++)
|
|
{
|
|
var cliente = colCliente > 0 ? GetCellString(ws, r, colCliente) : "";
|
|
var razao = colRazao > 0 ? GetCellString(ws, r, colRazao) : "";
|
|
var nome = colNome > 0 ? GetCellString(ws, r, colNome) : "";
|
|
|
|
if (string.IsNullOrWhiteSpace(cliente) && string.IsNullOrWhiteSpace(razao) && string.IsNullOrWhiteSpace(nome)) break;
|
|
|
|
if (string.IsNullOrWhiteSpace(cliente))
|
|
{
|
|
cliente = !string.IsNullOrWhiteSpace(razao) ? razao : nome;
|
|
}
|
|
|
|
seq++;
|
|
|
|
int item;
|
|
if (colItem > 0)
|
|
{
|
|
var itemStr = GetCellString(ws, r, colItem);
|
|
item = !string.IsNullOrWhiteSpace(itemStr) ? TryInt(itemStr) : seq;
|
|
}
|
|
else item = seq;
|
|
|
|
var linha = colLinha > 0 ? NullIfEmptyDigits(GetCellString(ws, r, colLinha)) : null;
|
|
|
|
var cpf = colCpf > 0 ? NullIfEmptyDigits(GetCellString(ws, r, colCpf)) : null;
|
|
var cnpj = colCnpj > 0 ? NullIfEmptyDigits(GetCellString(ws, r, colCnpj)) : null;
|
|
var rg = colRg > 0 ? NullIfEmptyDigits(GetCellString(ws, r, colRg)) : null;
|
|
|
|
DateTime? dataNascimento = null;
|
|
if (colDataNasc > 0)
|
|
dataNascimento = TryDateCell(ws, r, colDataNasc);
|
|
|
|
var email = colEmail > 0 ? GetCellString(ws, r, colEmail) : "";
|
|
var endereco = colEndereco > 0 ? GetCellString(ws, r, colEndereco) : "";
|
|
|
|
var celular = colCelular > 0 ? NullIfEmptyDigits(GetCellString(ws, r, colCelular)) : null;
|
|
var fixo = colFixo > 0 ? NullIfEmptyDigits(GetCellString(ws, r, colFixo)) : null;
|
|
|
|
var now = DateTime.UtcNow;
|
|
|
|
var tipoPessoa = !string.IsNullOrWhiteSpace(cnpj) || !string.IsNullOrWhiteSpace(razao)
|
|
? "PJ"
|
|
: "PF";
|
|
|
|
var nomeFinal = string.IsNullOrWhiteSpace(nome) ? cliente : nome.Trim();
|
|
var razaoFinal = string.IsNullOrWhiteSpace(razao) ? cliente : razao.Trim();
|
|
|
|
var e = new UserData
|
|
{
|
|
Id = Guid.NewGuid(),
|
|
Item = item,
|
|
Linha = linha,
|
|
Cliente = cliente.Trim(),
|
|
|
|
TipoPessoa = tipoPessoa,
|
|
Nome = tipoPessoa == "PF" ? nomeFinal : null,
|
|
RazaoSocial = tipoPessoa == "PJ" ? razaoFinal : null,
|
|
Cnpj = tipoPessoa == "PJ" ? cnpj : null,
|
|
Cpf = cpf,
|
|
Rg = rg,
|
|
DataNascimento = ToUtc(dataNascimento),
|
|
|
|
Email = string.IsNullOrWhiteSpace(email) ? null : email.Trim(),
|
|
Endereco = string.IsNullOrWhiteSpace(endereco) ? null : endereco.Trim(),
|
|
|
|
Celular = celular,
|
|
TelefoneFixo = fixo,
|
|
|
|
CreatedAt = now,
|
|
UpdatedAt = now
|
|
};
|
|
|
|
buffer.Add(e);
|
|
|
|
if (buffer.Count >= 400)
|
|
{
|
|
await _db.UserDatas.AddRangeAsync(buffer);
|
|
await _db.SaveChangesAsync();
|
|
buffer.Clear();
|
|
}
|
|
}
|
|
|
|
if (buffer.Count > 0)
|
|
{
|
|
await _db.UserDatas.AddRangeAsync(buffer);
|
|
await _db.SaveChangesAsync();
|
|
}
|
|
}
|
|
|
|
// ==========================================================
|
|
// ✅ IMPORTAÇÃO: VIGÊNCIA
|
|
// ==========================================================
|
|
private async Task ImportVigenciaFromWorkbook(XLWorkbook wb)
|
|
{
|
|
var ws = wb.Worksheets.FirstOrDefault(w => NormalizeHeader(w.Name) == NormalizeHeader("VIGÊNCIA"))
|
|
?? wb.Worksheets.FirstOrDefault(w => NormalizeHeader(w.Name) == NormalizeHeader("VIGENCIA"))
|
|
?? wb.Worksheets.FirstOrDefault(w => NormalizeHeader(w.Name).Contains("VIGEN"));
|
|
|
|
if (ws == null) return;
|
|
|
|
var headerRow = ws.RowsUsed().FirstOrDefault(r =>
|
|
r.CellsUsed().Any(c =>
|
|
{
|
|
var k = NormalizeHeader(c.GetString());
|
|
return k == "ITEM" || k == "ITEM(ID)" || k == "ITEMID";
|
|
}));
|
|
|
|
if (headerRow == null) return;
|
|
|
|
var map = BuildHeaderMap(headerRow);
|
|
|
|
var colItem = GetColAny(map, "ITEM", "ITEM(ID)", "ITEMID", "ITEM (ID)", "ITÉM (ID)");
|
|
if (colItem == 0) return;
|
|
|
|
var startRow = headerRow.RowNumber() + 1;
|
|
var lastRow = ws.LastRowUsed()?.RowNumber() ?? startRow;
|
|
|
|
var tenantId = GetTenantIdFromClaims();
|
|
var notificationsQuery = _db.Notifications
|
|
.IgnoreQueryFilters()
|
|
.Where(n => n.VigenciaLineId != null);
|
|
if (tenantId.HasValue)
|
|
{
|
|
notificationsQuery = notificationsQuery.Where(n => n.TenantId == tenantId.Value);
|
|
}
|
|
await notificationsQuery.ExecuteUpdateAsync(setters =>
|
|
setters.SetProperty(n => n.VigenciaLineId, n => null));
|
|
|
|
var vigenciaQuery = _db.VigenciaLines.IgnoreQueryFilters();
|
|
if (tenantId.HasValue)
|
|
{
|
|
vigenciaQuery = vigenciaQuery.Where(v => v.TenantId == tenantId.Value);
|
|
}
|
|
await vigenciaQuery.ExecuteDeleteAsync();
|
|
|
|
var buffer = new List<VigenciaLine>(600);
|
|
|
|
for (int r = startRow; r <= lastRow; r++)
|
|
{
|
|
var itemStr = GetCellString(ws, r, colItem);
|
|
if (string.IsNullOrWhiteSpace(itemStr)) break;
|
|
|
|
var conta = GetCellByHeader(ws, r, map, "CONTA");
|
|
var linha = NullIfEmptyDigits(GetCellByHeader(ws, r, map, "LINHA"));
|
|
var cliente = GetCellByHeader(ws, r, map, "CLIENTE");
|
|
var usuario = GetCellByHeader(ws, r, map, "USUÁRIO");
|
|
if (string.IsNullOrWhiteSpace(usuario))
|
|
usuario = GetCellByHeader(ws, r, map, "USUARIO");
|
|
|
|
var plano = GetCellByHeader(ws, r, map, "PLANO CONTRATO");
|
|
|
|
var dtEfet = TryDateNoUtc(ws, r, map, "DT. DE EFETIVAÇÃO DO SERVIÇO");
|
|
if (dtEfet == null) dtEfet = TryDateNoUtc(ws, r, map, "DT. DE EFETIVACAO DO SERVICO");
|
|
|
|
var dtFim = TryDateNoUtc(ws, r, map, "DT. DE TÉRMINO DA FIDELIZAÇÃO");
|
|
if (dtFim == null) dtFim = TryDateNoUtc(ws, r, map, "DT. DE TERMINO DA FIDELIZACAO");
|
|
|
|
var totalStr = GetCellByHeader(ws, r, map, "TOTAL");
|
|
var now = DateTime.UtcNow;
|
|
|
|
var e = new VigenciaLine
|
|
{
|
|
Id = Guid.NewGuid(),
|
|
Item = TryInt(itemStr),
|
|
Conta = string.IsNullOrWhiteSpace(conta) ? null : conta.Trim(),
|
|
Linha = linha,
|
|
Cliente = string.IsNullOrWhiteSpace(cliente) ? null : cliente.Trim(),
|
|
Usuario = string.IsNullOrWhiteSpace(usuario) ? null : usuario.Trim(),
|
|
PlanoContrato = string.IsNullOrWhiteSpace(plano) ? null : plano.Trim(),
|
|
DtEfetivacaoServico = dtEfet,
|
|
DtTerminoFidelizacao = dtFim,
|
|
Total = TryDecimal(totalStr),
|
|
CreatedAt = now,
|
|
UpdatedAt = now
|
|
};
|
|
|
|
buffer.Add(e);
|
|
|
|
if (buffer.Count >= 500)
|
|
{
|
|
await _db.VigenciaLines.AddRangeAsync(buffer);
|
|
await _db.SaveChangesAsync();
|
|
buffer.Clear();
|
|
}
|
|
}
|
|
|
|
if (buffer.Count > 0)
|
|
{
|
|
await _db.VigenciaLines.AddRangeAsync(buffer);
|
|
await _db.SaveChangesAsync();
|
|
}
|
|
}
|
|
|
|
// ==========================================================
|
|
// ✅ IMPORTAÇÃO: TROCA DE NÚMERO
|
|
// ==========================================================
|
|
private async Task ImportTrocaNumeroFromWorkbook(XLWorkbook wb)
|
|
{
|
|
var ws = wb.Worksheets.FirstOrDefault(w => NormalizeHeader(w.Name) == NormalizeHeader("TROCA DE NÚMERO"))
|
|
?? wb.Worksheets.FirstOrDefault(w => NormalizeHeader(w.Name) == NormalizeHeader("TROCA DE NUMERO"))
|
|
?? wb.Worksheets.FirstOrDefault(w =>
|
|
NormalizeHeader(w.Name).Contains("TROCA") &&
|
|
NormalizeHeader(w.Name).Contains("NUMER"));
|
|
|
|
if (ws == null) return;
|
|
|
|
var headerRow = ws.RowsUsed().FirstOrDefault(r =>
|
|
r.CellsUsed().Any(c => NormalizeHeader(c.GetString()) == "ITEM"));
|
|
|
|
if (headerRow == null) return;
|
|
|
|
var map = BuildHeaderMap(headerRow);
|
|
|
|
var colItem = GetCol(map, "ITEM");
|
|
if (colItem == 0) return;
|
|
|
|
var startRow = headerRow.RowNumber() + 1;
|
|
var lastRow = ws.LastRowUsed()?.RowNumber() ?? startRow;
|
|
|
|
await _db.TrocaNumeroLines.ExecuteDeleteAsync();
|
|
|
|
var buffer = new List<TrocaNumeroLine>(600);
|
|
|
|
for (int r = startRow; r <= lastRow; r++)
|
|
{
|
|
var itemStr = GetCellString(ws, r, colItem);
|
|
if (string.IsNullOrWhiteSpace(itemStr)) break;
|
|
|
|
var linhaAntiga = NullIfEmptyDigits(GetCellByHeader(ws, r, map, "LINHA ANTIGA"));
|
|
var linhaNova = NullIfEmptyDigits(GetCellByHeader(ws, r, map, "LINHA NOVA"));
|
|
var iccid = NullIfEmptyDigits(GetCellByHeader(ws, r, map, "ICCID"));
|
|
|
|
var dataTroca = TryDate(ws, r, map, "DATA TROCA");
|
|
if (dataTroca == null) dataTroca = TryDate(ws, r, map, "DATA DA TROCA");
|
|
|
|
var motivo = GetCellByHeader(ws, r, map, "MOTIVO");
|
|
var obs = GetCellByHeader(ws, r, map, "OBSERVAÇÃO");
|
|
if (string.IsNullOrWhiteSpace(obs)) obs = GetCellByHeader(ws, r, map, "OBSERVACAO");
|
|
|
|
var now = DateTime.UtcNow;
|
|
|
|
var e = new TrocaNumeroLine
|
|
{
|
|
Id = Guid.NewGuid(),
|
|
Item = TryInt(itemStr),
|
|
LinhaAntiga = linhaAntiga,
|
|
LinhaNova = linhaNova,
|
|
ICCID = iccid,
|
|
DataTroca = dataTroca,
|
|
Motivo = string.IsNullOrWhiteSpace(motivo) ? null : motivo.Trim(),
|
|
Observacao = string.IsNullOrWhiteSpace(obs) ? null : obs.Trim(),
|
|
CreatedAt = now,
|
|
UpdatedAt = now
|
|
};
|
|
|
|
buffer.Add(e);
|
|
|
|
if (buffer.Count >= 500)
|
|
{
|
|
await _db.TrocaNumeroLines.AddRangeAsync(buffer);
|
|
await _db.SaveChangesAsync();
|
|
buffer.Clear();
|
|
}
|
|
}
|
|
|
|
if (buffer.Count > 0)
|
|
{
|
|
await _db.TrocaNumeroLines.AddRangeAsync(buffer);
|
|
await _db.SaveChangesAsync();
|
|
}
|
|
}
|
|
|
|
// ==========================================================
|
|
// ✅ IMPORTAÇÃO CHIPS VIRGENS
|
|
// ==========================================================
|
|
private async Task ImportChipsVirgensFromWorkbook(XLWorkbook wb)
|
|
{
|
|
var ws = wb.Worksheets.FirstOrDefault(w => NormalizeHeader(w.Name) == NormalizeHeader("CHIP VIRGENS E CONTROLE DE RECEBIDOS"))
|
|
?? wb.Worksheets.FirstOrDefault(w => NormalizeHeader(w.Name) == NormalizeHeader("CHIPS VIRGENS"))
|
|
?? wb.Worksheets.FirstOrDefault(w =>
|
|
{
|
|
var name = NormalizeHeader(w.Name);
|
|
return name.Contains("CHIP") && name.Contains("VIRGEN");
|
|
});
|
|
|
|
if (ws == null) return;
|
|
|
|
var headers = new List<IXLRow>();
|
|
foreach (var rowIndex in new[] { 7, 8 })
|
|
{
|
|
var row = ws.Row(rowIndex);
|
|
if (IsChipsVirgensHeader(row))
|
|
{
|
|
headers.Add(row);
|
|
}
|
|
}
|
|
|
|
if (headers.Count == 0)
|
|
{
|
|
headers = ws.RowsUsed()
|
|
.Where(IsChipsVirgensHeader)
|
|
.OrderBy(r => r.RowNumber())
|
|
.ToList();
|
|
}
|
|
if (headers.Count == 0) return;
|
|
|
|
await _db.ChipVirgemLines.ExecuteDeleteAsync();
|
|
|
|
var buffer = new List<ChipVirgemLine>(500);
|
|
var lastRow = ws.LastRowUsed()?.RowNumber() ?? 1;
|
|
|
|
for (int i = 0; i < headers.Count; i++)
|
|
{
|
|
var headerRow = headers[i];
|
|
var lastCol = headerRow.LastCellUsed()?.Address.ColumnNumber
|
|
?? ws.LastColumnUsed()?.ColumnNumber()
|
|
?? headerRow.LastCellUsed()?.Address.ColumnNumber
|
|
?? 1;
|
|
var itemColumns = headerRow.CellsUsed()
|
|
.Where(c => NormalizeHeader(c.GetString()) == "ITEM")
|
|
.Select(c => c.Address.ColumnNumber)
|
|
.OrderBy(c => c)
|
|
.ToList();
|
|
if (itemColumns.Count == 0) continue;
|
|
|
|
var startRow = headerRow.RowNumber() + 1;
|
|
var endRow = lastRow;
|
|
|
|
for (int tableIndex = 0; tableIndex < itemColumns.Count; tableIndex++)
|
|
{
|
|
var startCol = itemColumns[tableIndex];
|
|
var endCol = tableIndex + 1 < itemColumns.Count
|
|
? itemColumns[tableIndex + 1] - 1
|
|
: lastCol;
|
|
|
|
int colItem = startCol;
|
|
int colChip = FindHeaderColumn(headerRow, startCol, endCol, "CHIP");
|
|
int colObs = FindHeaderColumn(headerRow, startCol, endCol, "OBS");
|
|
if (colItem == 0 || colChip == 0 || colObs == 0) continue;
|
|
|
|
for (int r = startRow; r <= endRow; r++)
|
|
{
|
|
var itemStr = GetCellString(ws, r, colItem);
|
|
if (string.IsNullOrWhiteSpace(itemStr)) break;
|
|
|
|
var chipRaw = GetCellString(ws, r, colChip);
|
|
var numeroChip = NullIfEmptyDigits(chipRaw);
|
|
if (string.IsNullOrWhiteSpace(numeroChip) && !string.IsNullOrWhiteSpace(chipRaw))
|
|
{
|
|
numeroChip = chipRaw.Trim();
|
|
}
|
|
var observacoes = GetCellString(ws, r, colObs);
|
|
if (string.IsNullOrWhiteSpace(numeroChip) && string.IsNullOrWhiteSpace(observacoes))
|
|
{
|
|
continue;
|
|
}
|
|
|
|
var now = DateTime.UtcNow;
|
|
|
|
var e = new ChipVirgemLine
|
|
{
|
|
Id = Guid.NewGuid(),
|
|
Item = TryInt(itemStr),
|
|
NumeroDoChip = numeroChip,
|
|
Observacoes = string.IsNullOrWhiteSpace(observacoes) ? null : observacoes.Trim(),
|
|
CreatedAt = now,
|
|
UpdatedAt = now
|
|
};
|
|
|
|
buffer.Add(e);
|
|
|
|
if (buffer.Count >= 500)
|
|
{
|
|
await _db.ChipVirgemLines.AddRangeAsync(buffer);
|
|
await _db.SaveChangesAsync();
|
|
buffer.Clear();
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
if (buffer.Count > 0)
|
|
{
|
|
await _db.ChipVirgemLines.AddRangeAsync(buffer);
|
|
await _db.SaveChangesAsync();
|
|
}
|
|
}
|
|
|
|
// ==========================================================
|
|
// ✅ IMPORTAÇÃO CONTROLE DE RECEBIDOS (2022-2025)
|
|
// ==========================================================
|
|
private async Task ImportControleRecebidosFromWorkbook(XLWorkbook wb)
|
|
{
|
|
await _db.ControleRecebidoLines.ExecuteDeleteAsync();
|
|
|
|
var years = new[] { 2022, 2023, 2024, 2025 };
|
|
var importedYears = new HashSet<int>();
|
|
|
|
foreach (var info in GetControleRecebidosWorksheets(wb))
|
|
{
|
|
await ImportControleRecebidosSheet(info.Sheet, info.Year);
|
|
importedYears.Add(info.Year);
|
|
}
|
|
|
|
foreach (var year in years)
|
|
{
|
|
if (importedYears.Contains(year)) continue;
|
|
|
|
var ws = FindControleRecebidosWorksheet(wb, year);
|
|
if (ws == null) continue;
|
|
|
|
await ImportControleRecebidosSheet(ws, year);
|
|
}
|
|
}
|
|
|
|
// ==========================================================
|
|
// ✅ IMPORTAÇÃO DA ABA RESUMO
|
|
// ==========================================================
|
|
private async Task ImportResumoFromWorkbook(XLWorkbook wb)
|
|
{
|
|
var ws = wb.Worksheets.FirstOrDefault(w => NormalizeHeader(w.Name) == NormalizeHeader("RESUMO"));
|
|
if (ws == null) return;
|
|
|
|
await _db.ResumoMacrophonyPlans.ExecuteDeleteAsync();
|
|
await _db.ResumoMacrophonyTotals.ExecuteDeleteAsync();
|
|
await _db.ResumoVivoLineResumos.ExecuteDeleteAsync();
|
|
await _db.ResumoVivoLineTotals.ExecuteDeleteAsync();
|
|
await _db.ResumoClienteEspeciais.ExecuteDeleteAsync();
|
|
await _db.ResumoPlanoContratoResumos.ExecuteDeleteAsync();
|
|
await _db.ResumoPlanoContratoTotals.ExecuteDeleteAsync();
|
|
await _db.ResumoLineTotais.ExecuteDeleteAsync();
|
|
await _db.ResumoReservaLines.ExecuteDeleteAsync();
|
|
await _db.ResumoReservaTotals.ExecuteDeleteAsync();
|
|
|
|
var now = DateTime.UtcNow;
|
|
|
|
await ImportResumoTabela1(ws, now);
|
|
await ImportResumoTabela2(ws, now);
|
|
await ImportResumoTabela3(ws, now);
|
|
await ImportResumoTabela4(ws, now);
|
|
await ImportResumoTabela5(ws, now);
|
|
await ImportResumoTabela6(ws, now);
|
|
}
|
|
|
|
private async Task ImportResumoTabela1(IXLWorksheet ws, DateTime now)
|
|
{
|
|
var lastRowUsed = ws.LastRowUsed()?.RowNumber() ?? 1;
|
|
var headerRow = FindHeaderRowForMacrophonyPlans(ws, 1, lastRowUsed);
|
|
if (headerRow == 0) return;
|
|
|
|
var map = BuildHeaderMap(ws.Row(headerRow));
|
|
var colPlano = GetCol(map, "PLANO CONTRATO");
|
|
var colGb = GetCol(map, "GB");
|
|
var colValorIndividual = GetColAny(map, "VALOR INDIVIDUAL C/ SVAs", "VALOR INDIVIDUAL C/ SVAS", "VALOR INDIVIDUAL");
|
|
var colFranquiaGb = GetColAny(map, "FRANQUIA GB", "FRAQUIA GB");
|
|
var colTotalLinhas = GetColAny(map, "TOTAL DE LINHAS", "TOTAL LINHAS");
|
|
var colValorTotal = GetCol(map, "VALOR TOTAL");
|
|
|
|
var buffer = new List<ResumoMacrophonyPlan>(200);
|
|
string? lastPlanoContrato = null;
|
|
decimal? lastGb = null;
|
|
var dataStarted = false;
|
|
var emptyDataStreak = 0;
|
|
int? totalRowIndex = null;
|
|
var missingPlanoCount = 0;
|
|
var missingGbCount = 0;
|
|
|
|
for (int r = headerRow + 1; r <= lastRowUsed; r++)
|
|
{
|
|
var plano = GetCellString(ws, r, colPlano);
|
|
var gb = GetCellString(ws, r, colGb);
|
|
var valorInd = GetCellString(ws, r, colValorIndividual);
|
|
var franquia = GetCellString(ws, r, colFranquiaGb);
|
|
var totalLinhas = GetCellString(ws, r, colTotalLinhas);
|
|
var valorTotal = GetCellString(ws, r, colValorTotal);
|
|
|
|
var isPlanoTotal = !string.IsNullOrWhiteSpace(plano)
|
|
&& NormalizeHeader(plano) == NormalizeHeader("TOTAL");
|
|
|
|
if (isPlanoTotal)
|
|
{
|
|
totalRowIndex = r;
|
|
break;
|
|
}
|
|
|
|
var hasAnyValue = !(string.IsNullOrWhiteSpace(plano)
|
|
&& string.IsNullOrWhiteSpace(gb)
|
|
&& string.IsNullOrWhiteSpace(valorInd)
|
|
&& string.IsNullOrWhiteSpace(franquia)
|
|
&& string.IsNullOrWhiteSpace(totalLinhas)
|
|
&& string.IsNullOrWhiteSpace(valorTotal));
|
|
|
|
if (!hasAnyValue)
|
|
{
|
|
if (dataStarted)
|
|
{
|
|
emptyDataStreak++;
|
|
if (emptyDataStreak >= 2) break;
|
|
}
|
|
continue;
|
|
}
|
|
|
|
emptyDataStreak = 0;
|
|
|
|
var franquiaValue = TryDecimal(franquia);
|
|
var totalLinhasValue = TryNullableInt(totalLinhas);
|
|
var isDataRow = franquiaValue.HasValue || totalLinhasValue.HasValue;
|
|
if (isDataRow) dataStarted = true;
|
|
if (!isDataRow && dataStarted)
|
|
{
|
|
break;
|
|
}
|
|
if (!isDataRow)
|
|
{
|
|
continue;
|
|
}
|
|
|
|
var planoNormalized = NormalizeHeader(plano);
|
|
if (!string.IsNullOrWhiteSpace(plano)
|
|
&& planoNormalized != NormalizeHeader("PLANO CONTRATO")
|
|
&& planoNormalized != NormalizeHeader("TOTAL"))
|
|
{
|
|
lastPlanoContrato = plano.Trim();
|
|
}
|
|
|
|
var gbValue = TryDecimal(gb);
|
|
if (gbValue.HasValue)
|
|
{
|
|
lastGb = gbValue;
|
|
}
|
|
|
|
var resolvedPlano = isDataRow
|
|
? (string.IsNullOrWhiteSpace(plano) ? lastPlanoContrato : plano.Trim())
|
|
: (string.IsNullOrWhiteSpace(plano) ? null : plano.Trim());
|
|
|
|
var resolvedGb = isDataRow
|
|
? (gbValue ?? lastGb)
|
|
: gbValue;
|
|
|
|
if (isDataRow && string.IsNullOrWhiteSpace(resolvedPlano))
|
|
{
|
|
missingPlanoCount++;
|
|
}
|
|
|
|
if (isDataRow && !resolvedGb.HasValue)
|
|
{
|
|
missingGbCount++;
|
|
}
|
|
|
|
var vivoTravelCell = ws.Cell(r, 8).GetString();
|
|
var vivoTravel = !string.IsNullOrWhiteSpace(vivoTravelCell)
|
|
&& vivoTravelCell.Contains("VIVO TRAVEL", StringComparison.OrdinalIgnoreCase);
|
|
|
|
buffer.Add(new ResumoMacrophonyPlan
|
|
{
|
|
PlanoContrato = string.IsNullOrWhiteSpace(resolvedPlano) ? null : resolvedPlano,
|
|
Gb = resolvedGb,
|
|
ValorIndividualComSvas = TryDecimal(valorInd),
|
|
FranquiaGb = franquiaValue,
|
|
TotalLinhas = totalLinhasValue,
|
|
ValorTotal = TryDecimal(valorTotal),
|
|
VivoTravel = vivoTravel,
|
|
CreatedAt = now,
|
|
UpdatedAt = now
|
|
});
|
|
}
|
|
|
|
if (buffer.Count > 0)
|
|
{
|
|
await _db.ResumoMacrophonyPlans.AddRangeAsync(buffer);
|
|
await _db.SaveChangesAsync();
|
|
}
|
|
|
|
if (missingPlanoCount > 0 || missingGbCount > 0)
|
|
{
|
|
throw new InvalidOperationException($"Import RESUMO/MACROPHONY: {missingPlanoCount} linhas sem PLANO CONTRATO e {missingGbCount} linhas sem GB.");
|
|
}
|
|
|
|
if (totalRowIndex == null)
|
|
{
|
|
return;
|
|
}
|
|
|
|
var total = new ResumoMacrophonyTotal
|
|
{
|
|
FranquiaGbTotal = TryDecimal(GetCellString(ws, totalRowIndex.Value, colFranquiaGb)),
|
|
TotalLinhasTotal = TryNullableInt(GetCellString(ws, totalRowIndex.Value, colTotalLinhas)),
|
|
ValorTotal = TryDecimal(GetCellString(ws, totalRowIndex.Value, colValorTotal)),
|
|
CreatedAt = now,
|
|
UpdatedAt = now
|
|
};
|
|
|
|
await _db.ResumoMacrophonyTotals.AddAsync(total);
|
|
await _db.SaveChangesAsync();
|
|
}
|
|
|
|
private static int FindHeaderRowForMacrophonyPlans(IXLWorksheet ws, int startRow, int lastRow)
|
|
{
|
|
for (int r = startRow; r <= lastRow; r++)
|
|
{
|
|
var row = ws.Row(r);
|
|
if (!row.CellsUsed().Any()) continue;
|
|
|
|
var map = BuildHeaderMap(row);
|
|
var hasPlano = GetCol(map, "PLANO CONTRATO") > 0;
|
|
var hasGb = GetCol(map, "GB") > 0;
|
|
var hasTotalLinhas = GetColAny(map, "TOTAL DE LINHAS", "TOTAL LINHAS") > 0;
|
|
|
|
if (hasPlano && hasGb && hasTotalLinhas)
|
|
{
|
|
return r;
|
|
}
|
|
}
|
|
|
|
return 0;
|
|
}
|
|
|
|
private async Task ImportResumoTabela2(IXLWorksheet ws, DateTime now)
|
|
{
|
|
const int headerRow = 5;
|
|
const int totalRow = 219;
|
|
var lastRow = Math.Min(totalRow - 1, ws.LastRowUsed()?.RowNumber() ?? totalRow - 1);
|
|
|
|
var map = BuildHeaderMap(ws.Row(headerRow));
|
|
var colSkil = GetCol(map, "SKIL");
|
|
var colCliente = GetCol(map, "CLIENTE");
|
|
var colQtdLinhas = GetColAny(map, "QTD DE LINHAS", "QTD. DE LINHAS", "QTD LINHAS");
|
|
var colFranquiaTotal = GetColAny(map, "FRANQUIA TOTAL", "FRAQUIA TOTAL");
|
|
var colValorContratoVivo = GetColAny(map, "VALOR CONTRATO VIVO", "VALOR DO CONTRATO VIVO");
|
|
var colFranquiaLine = GetColAny(map, "FRANQUIA LINE", "FRAQUIA LINE");
|
|
var colValorContratoLine = GetColAny(map, "VALOR CONTRATO LINE", "VALOR DO CONTRATO LINE");
|
|
var colLucro = GetCol(map, "LUCRO");
|
|
|
|
var buffer = new List<ResumoVivoLineResumo>(400);
|
|
|
|
for (int r = headerRow + 1; r <= lastRow; r++)
|
|
{
|
|
var skil = GetCellString(ws, r, colSkil);
|
|
var cliente = GetCellString(ws, r, colCliente);
|
|
var qtdLinhas = GetCellString(ws, r, colQtdLinhas);
|
|
var franquiaTotal = GetCellString(ws, r, colFranquiaTotal);
|
|
var valorContratoVivo = GetCellString(ws, r, colValorContratoVivo);
|
|
var franquiaLine = GetCellString(ws, r, colFranquiaLine);
|
|
var valorContratoLine = GetCellString(ws, r, colValorContratoLine);
|
|
var lucro = GetCellString(ws, r, colLucro);
|
|
|
|
if (string.IsNullOrWhiteSpace(skil)
|
|
&& string.IsNullOrWhiteSpace(cliente)
|
|
&& string.IsNullOrWhiteSpace(qtdLinhas)
|
|
&& string.IsNullOrWhiteSpace(franquiaTotal)
|
|
&& string.IsNullOrWhiteSpace(valorContratoVivo)
|
|
&& string.IsNullOrWhiteSpace(franquiaLine)
|
|
&& string.IsNullOrWhiteSpace(valorContratoLine)
|
|
&& string.IsNullOrWhiteSpace(lucro))
|
|
{
|
|
continue;
|
|
}
|
|
|
|
buffer.Add(new ResumoVivoLineResumo
|
|
{
|
|
Skil = string.IsNullOrWhiteSpace(skil) ? null : skil.Trim(),
|
|
Cliente = string.IsNullOrWhiteSpace(cliente) ? null : cliente.Trim(),
|
|
QtdLinhas = TryNullableInt(qtdLinhas),
|
|
FranquiaTotal = TryDecimal(franquiaTotal),
|
|
ValorContratoVivo = TryDecimal(valorContratoVivo),
|
|
FranquiaLine = TryDecimal(franquiaLine),
|
|
ValorContratoLine = TryDecimal(valorContratoLine),
|
|
Lucro = TryDecimal(lucro),
|
|
CreatedAt = now,
|
|
UpdatedAt = now
|
|
});
|
|
}
|
|
|
|
if (buffer.Count > 0)
|
|
{
|
|
await _db.ResumoVivoLineResumos.AddRangeAsync(buffer);
|
|
await _db.SaveChangesAsync();
|
|
}
|
|
|
|
var total = new ResumoVivoLineTotal
|
|
{
|
|
QtdLinhasTotal = TryNullableInt(GetCellString(ws, totalRow, colQtdLinhas)),
|
|
FranquiaTotal = TryDecimal(GetCellString(ws, totalRow, colFranquiaTotal)),
|
|
ValorContratoVivo = TryDecimal(GetCellString(ws, totalRow, colValorContratoVivo)),
|
|
FranquiaLine = TryDecimal(GetCellString(ws, totalRow, colFranquiaLine)),
|
|
ValorContratoLine = TryDecimal(GetCellString(ws, totalRow, colValorContratoLine)),
|
|
Lucro = TryDecimal(GetCellString(ws, totalRow, colLucro)),
|
|
CreatedAt = now,
|
|
UpdatedAt = now
|
|
};
|
|
|
|
await _db.ResumoVivoLineTotals.AddAsync(total);
|
|
await _db.SaveChangesAsync();
|
|
}
|
|
|
|
private async Task ImportResumoTabela3(IXLWorksheet ws, DateTime now)
|
|
{
|
|
const int headerStartRow = 223;
|
|
const int headerEndRow = 225;
|
|
const int valuesRow = 227;
|
|
|
|
var headerColumns = new Dictionary<int, string>();
|
|
for (int row = headerStartRow; row <= headerEndRow; row++)
|
|
{
|
|
var rowData = ws.Row(row);
|
|
var lastCol = rowData.LastCellUsed()?.Address.ColumnNumber ?? 1;
|
|
for (int col = 1; col <= lastCol; col++)
|
|
{
|
|
var name = rowData.Cell(col).GetString();
|
|
if (string.IsNullOrWhiteSpace(name)) continue;
|
|
if (!headerColumns.ContainsKey(col))
|
|
{
|
|
headerColumns[col] = name.Trim();
|
|
}
|
|
}
|
|
}
|
|
|
|
if (headerColumns.Count == 0)
|
|
{
|
|
return;
|
|
}
|
|
|
|
var buffer = new List<ResumoClienteEspecial>(headerColumns.Count);
|
|
foreach (var entry in headerColumns)
|
|
{
|
|
var valueStr = ws.Cell(valuesRow, entry.Key).GetString();
|
|
buffer.Add(new ResumoClienteEspecial
|
|
{
|
|
Nome = entry.Value,
|
|
Valor = TryDecimal(valueStr),
|
|
CreatedAt = now,
|
|
UpdatedAt = now
|
|
});
|
|
}
|
|
|
|
await _db.ResumoClienteEspeciais.AddRangeAsync(buffer);
|
|
await _db.SaveChangesAsync();
|
|
}
|
|
|
|
private async Task ImportResumoTabela4(IXLWorksheet ws, DateTime now)
|
|
{
|
|
var lastRowUsed = ws.LastRowUsed()?.RowNumber() ?? 1;
|
|
var headerRow = FindHeaderRowForPlanoContratoResumo(ws, 1, lastRowUsed);
|
|
if (headerRow == 0) return;
|
|
|
|
var map = BuildHeaderMap(ws.Row(headerRow));
|
|
var colPlano = GetCol(map, "PLANO CONTRATO");
|
|
var colGb = GetCol(map, "GB");
|
|
var colValorIndividual = GetColAny(map, "VALOR INDIVIDUAL C/ SVAs", "VALOR INDIVIDUAL C/ SVAS", "VALOR INDIVIDUAL");
|
|
var colFranquiaGb = GetColAny(map, "FRANQUIA GB", "FRAQUIA GB");
|
|
var colTotalLinhas = GetColAny(map, "TOTAL DE LINHAS", "TOTAL LINHAS");
|
|
var colValorTotal = GetCol(map, "VALOR TOTAL");
|
|
var colCliente = GetCol(map, "CLIENTE");
|
|
var colQtdLinhas = GetColAny(map, "QTD DE LINHAS", "QTD. DE LINHAS", "QTD LINHAS");
|
|
|
|
var buffer = new List<ResumoPlanoContratoResumo>(200);
|
|
string? lastPlanoContrato = null;
|
|
var dataStarted = false;
|
|
var emptyDataStreak = 0;
|
|
int? totalRowIndex = null;
|
|
var missingPlanoCount = 0;
|
|
|
|
for (int r = headerRow + 1; r <= lastRowUsed; r++)
|
|
{
|
|
var plano = GetCellString(ws, r, colPlano);
|
|
var gb = GetCellString(ws, r, colGb);
|
|
var valorInd = GetCellString(ws, r, colValorIndividual);
|
|
var franquia = GetCellString(ws, r, colFranquiaGb);
|
|
var totalLinhas = GetCellString(ws, r, colTotalLinhas);
|
|
var valorTotal = GetCellString(ws, r, colValorTotal);
|
|
var cliente = GetCellString(ws, r, colCliente);
|
|
var qtdLinhas = GetCellString(ws, r, colQtdLinhas);
|
|
|
|
var isPlanoTotal = !string.IsNullOrWhiteSpace(plano)
|
|
&& NormalizeHeader(plano) == NormalizeHeader("TOTAL");
|
|
|
|
if (isPlanoTotal)
|
|
{
|
|
totalRowIndex = r;
|
|
break;
|
|
}
|
|
|
|
var hasAnyValue = !(string.IsNullOrWhiteSpace(plano)
|
|
&& string.IsNullOrWhiteSpace(gb)
|
|
&& string.IsNullOrWhiteSpace(valorInd)
|
|
&& string.IsNullOrWhiteSpace(franquia)
|
|
&& string.IsNullOrWhiteSpace(totalLinhas)
|
|
&& string.IsNullOrWhiteSpace(valorTotal)
|
|
&& string.IsNullOrWhiteSpace(cliente)
|
|
&& string.IsNullOrWhiteSpace(qtdLinhas));
|
|
|
|
if (!hasAnyValue)
|
|
{
|
|
if (dataStarted)
|
|
{
|
|
emptyDataStreak++;
|
|
if (emptyDataStreak >= 2) break;
|
|
}
|
|
continue;
|
|
}
|
|
|
|
emptyDataStreak = 0;
|
|
|
|
var isDataRow = !string.IsNullOrWhiteSpace(cliente) || TryNullableInt(qtdLinhas).HasValue;
|
|
|
|
var planoNormalized = NormalizeHeader(plano);
|
|
if (!string.IsNullOrWhiteSpace(plano)
|
|
&& planoNormalized != NormalizeHeader("PLANO CONTRATO")
|
|
&& planoNormalized != NormalizeHeader("TOTAL"))
|
|
{
|
|
lastPlanoContrato = plano.Trim();
|
|
}
|
|
|
|
if (!isDataRow && dataStarted)
|
|
{
|
|
break;
|
|
}
|
|
|
|
if (isDataRow) dataStarted = true;
|
|
|
|
var resolvedPlano = isDataRow
|
|
? (string.IsNullOrWhiteSpace(plano) ? lastPlanoContrato : plano.Trim())
|
|
: (string.IsNullOrWhiteSpace(plano) ? null : plano.Trim());
|
|
|
|
if (isDataRow && string.IsNullOrWhiteSpace(resolvedPlano))
|
|
{
|
|
missingPlanoCount++;
|
|
}
|
|
|
|
buffer.Add(new ResumoPlanoContratoResumo
|
|
{
|
|
PlanoContrato = string.IsNullOrWhiteSpace(resolvedPlano) ? null : resolvedPlano,
|
|
Gb = TryDecimal(gb),
|
|
ValorIndividualComSvas = TryDecimal(valorInd),
|
|
FranquiaGb = TryDecimal(franquia),
|
|
TotalLinhas = TryNullableInt(totalLinhas),
|
|
ValorTotal = TryDecimal(valorTotal),
|
|
CreatedAt = now,
|
|
UpdatedAt = now
|
|
});
|
|
}
|
|
|
|
if (buffer.Count > 0)
|
|
{
|
|
await _db.ResumoPlanoContratoResumos.AddRangeAsync(buffer);
|
|
await _db.SaveChangesAsync();
|
|
}
|
|
|
|
if (missingPlanoCount > 0)
|
|
{
|
|
throw new InvalidOperationException($"Import RESUMO/PLANO CONTRATO: {missingPlanoCount} linhas de dados ficaram sem PLANO CONTRATO.");
|
|
}
|
|
|
|
if (totalRowIndex == null)
|
|
{
|
|
return;
|
|
}
|
|
|
|
var total = new ResumoPlanoContratoTotal
|
|
{
|
|
ValorTotal = TryDecimal(GetCellString(ws, totalRowIndex.Value, colValorTotal)),
|
|
CreatedAt = now,
|
|
UpdatedAt = now
|
|
};
|
|
|
|
await _db.ResumoPlanoContratoTotals.AddAsync(total);
|
|
await _db.SaveChangesAsync();
|
|
}
|
|
|
|
private static int FindHeaderRowForPlanoContratoResumo(IXLWorksheet ws, int startRow, int lastRow)
|
|
{
|
|
for (int r = startRow; r <= lastRow; r++)
|
|
{
|
|
var row = ws.Row(r);
|
|
if (!row.CellsUsed().Any()) continue;
|
|
|
|
var map = BuildHeaderMap(row);
|
|
var hasPlano = GetCol(map, "PLANO CONTRATO") > 0;
|
|
var hasCliente = GetCol(map, "CLIENTE") > 0;
|
|
var hasQtd = GetColAny(map, "QTD DE LINHAS", "QTD. DE LINHAS", "QTD LINHAS") > 0;
|
|
|
|
if (hasPlano && hasCliente && hasQtd)
|
|
{
|
|
return r;
|
|
}
|
|
}
|
|
|
|
return 0;
|
|
}
|
|
|
|
private async Task ImportResumoTabela5(IXLWorksheet ws, DateTime now)
|
|
{
|
|
const int headerRow = 83;
|
|
var map = BuildHeaderMap(ws.Row(headerRow));
|
|
var colValorTotalLine = GetColAny(map, "VALOR TOTAL LINE", "VALOR TOTAL LINE R$", "VALOR TOTAL LINE R$");
|
|
var colLucroTotalLine = GetColAny(map, "LUCRO TOTAL LINE", "LUCRO TOTAL LINE R$", "LUCRO TOTAL LINE R$");
|
|
var colQtdLinhas = GetColAny(map, "QTD. LINHAS", "QTD LINHAS", "QTD. DE LINHAS");
|
|
|
|
var buffer = new List<ResumoLineTotais>(3);
|
|
for (int r = headerRow + 1; r <= headerRow + 3; r++)
|
|
{
|
|
var tipo = ws.Cell(r, 2).GetString();
|
|
if (string.IsNullOrWhiteSpace(tipo))
|
|
{
|
|
continue;
|
|
}
|
|
|
|
buffer.Add(new ResumoLineTotais
|
|
{
|
|
Tipo = tipo.Trim(),
|
|
ValorTotalLine = TryDecimal(GetCellString(ws, r, colValorTotalLine)),
|
|
LucroTotalLine = TryDecimal(GetCellString(ws, r, colLucroTotalLine)),
|
|
QtdLinhas = TryNullableInt(GetCellString(ws, r, colQtdLinhas)),
|
|
CreatedAt = now,
|
|
UpdatedAt = now
|
|
});
|
|
}
|
|
|
|
if (buffer.Count > 0)
|
|
{
|
|
await _db.ResumoLineTotais.AddRangeAsync(buffer);
|
|
await _db.SaveChangesAsync();
|
|
}
|
|
}
|
|
|
|
private async Task ImportResumoTabela6(IXLWorksheet ws, DateTime now)
|
|
{
|
|
var lastRowUsed = ws.LastRowUsed()?.RowNumber() ?? 1;
|
|
var sectionRow = FindSectionRow(ws, "LINHAS NA RESERVA");
|
|
if (sectionRow == 0) return;
|
|
|
|
var headerRow = FindHeaderRowForReserva(ws, sectionRow + 1, lastRowUsed);
|
|
if (headerRow == 0) return;
|
|
|
|
var map = BuildHeaderMap(ws.Row(headerRow));
|
|
var colDdd = GetCol(map, "DDD");
|
|
var colFranquiaGb = GetColAny(map, "FRANQUIA GB", "FRAQUIA GB");
|
|
var colQtdLinhas = GetColAny(map, "QTD. DE LINHAS", "QTD DE LINHAS", "QTD. LINHAS", "QTDLINHAS");
|
|
var colTotal = GetCol(map, "TOTAL");
|
|
|
|
var buffer = new List<ResumoReservaLine>(200);
|
|
string? lastDddValid = null;
|
|
decimal? lastTotalForDdd = null;
|
|
var dataStarted = false;
|
|
var emptyRowStreak = 0;
|
|
int? totalRowIndex = null;
|
|
var totalsFromSheetByDdd = new Dictionary<string, decimal?>();
|
|
var sumQtdByDdd = new Dictionary<string, int>();
|
|
|
|
for (int r = headerRow + 1; r <= lastRowUsed; r++)
|
|
{
|
|
var ddd = GetCellString(ws, r, colDdd);
|
|
var franquia = GetCellString(ws, r, colFranquiaGb);
|
|
var qtdLinhas = GetCellString(ws, r, colQtdLinhas);
|
|
var total = GetCellString(ws, r, colTotal);
|
|
|
|
var hasAnyValue = !(string.IsNullOrWhiteSpace(ddd)
|
|
&& string.IsNullOrWhiteSpace(franquia)
|
|
&& string.IsNullOrWhiteSpace(qtdLinhas)
|
|
&& string.IsNullOrWhiteSpace(total));
|
|
|
|
if (!hasAnyValue)
|
|
{
|
|
if (dataStarted)
|
|
{
|
|
emptyRowStreak++;
|
|
if (emptyRowStreak >= 2) break;
|
|
}
|
|
continue;
|
|
}
|
|
|
|
emptyRowStreak = 0;
|
|
|
|
var dddCandidate = NullIfEmptyDigits(ddd);
|
|
var hasFranquiaText = !string.IsNullOrWhiteSpace(franquia);
|
|
var hasQtdText = !string.IsNullOrWhiteSpace(qtdLinhas);
|
|
var hasTotalText = !string.IsNullOrWhiteSpace(total);
|
|
|
|
// ✅ Rodapé "TOTAL GERAL" (DDD vazio + franquia vazia + qtd + total preenchidos)
|
|
var isTotalGeralRow = string.IsNullOrWhiteSpace(dddCandidate)
|
|
&& !hasFranquiaText
|
|
&& hasQtdText
|
|
&& hasTotalText;
|
|
|
|
if (isTotalGeralRow)
|
|
{
|
|
totalRowIndex = r;
|
|
break;
|
|
}
|
|
|
|
var franquiaValue = TryDecimal(franquia);
|
|
var qtdValue = TryNullableInt(qtdLinhas);
|
|
var isDataRow = franquiaValue.HasValue || qtdValue.HasValue;
|
|
|
|
if (!string.IsNullOrWhiteSpace(dddCandidate))
|
|
{
|
|
if (!string.Equals(lastDddValid, dddCandidate, StringComparison.OrdinalIgnoreCase))
|
|
{
|
|
lastTotalForDdd = null;
|
|
}
|
|
lastDddValid = dddCandidate;
|
|
}
|
|
|
|
if (!isDataRow && dataStarted)
|
|
{
|
|
break;
|
|
}
|
|
|
|
if (isDataRow) dataStarted = true;
|
|
|
|
var resolvedDdd = isDataRow
|
|
? (dddCandidate ?? lastDddValid)
|
|
: dddCandidate;
|
|
|
|
var totalValue = TryDecimal(total);
|
|
if (!string.IsNullOrWhiteSpace(resolvedDdd) && totalValue.HasValue)
|
|
{
|
|
lastTotalForDdd = totalValue;
|
|
totalsFromSheetByDdd[resolvedDdd] = totalValue;
|
|
}
|
|
|
|
if (!string.IsNullOrWhiteSpace(resolvedDdd) && qtdValue.HasValue)
|
|
{
|
|
if (sumQtdByDdd.TryGetValue(resolvedDdd, out var acc))
|
|
sumQtdByDdd[resolvedDdd] = acc + qtdValue.Value;
|
|
else
|
|
sumQtdByDdd[resolvedDdd] = qtdValue.Value;
|
|
}
|
|
|
|
buffer.Add(new ResumoReservaLine
|
|
{
|
|
Ddd = string.IsNullOrWhiteSpace(resolvedDdd) ? null : resolvedDdd,
|
|
FranquiaGb = franquiaValue,
|
|
QtdLinhas = qtdValue,
|
|
Total = totalValue ?? lastTotalForDdd,
|
|
CreatedAt = now,
|
|
UpdatedAt = now
|
|
});
|
|
}
|
|
|
|
var missingDddCount = buffer.Count(x => x.Ddd == null && (x.FranquiaGb.HasValue || x.QtdLinhas.HasValue));
|
|
if (missingDddCount > 0)
|
|
{
|
|
throw new InvalidOperationException($"Import RESUMO/RESERVA: {missingDddCount} linhas de dados ficaram sem DDD.");
|
|
}
|
|
|
|
if (buffer.Count > 0)
|
|
{
|
|
await _db.ResumoReservaLines.AddRangeAsync(buffer);
|
|
await _db.SaveChangesAsync();
|
|
}
|
|
|
|
if (totalsFromSheetByDdd.Count > 0)
|
|
{
|
|
foreach (var kv in totalsFromSheetByDdd)
|
|
{
|
|
if (!kv.Value.HasValue) continue;
|
|
if (!sumQtdByDdd.TryGetValue(kv.Key, out var sum)) continue;
|
|
var totalInt = (int)Math.Round(kv.Value.Value);
|
|
if (totalInt != sum)
|
|
{
|
|
Console.WriteLine($"[WARN] RESUMO/RESERVA DDD {kv.Key}: TOTAL(planilha)={totalInt} vs SOMA(QTD)={sum}");
|
|
}
|
|
}
|
|
}
|
|
|
|
var totalGeralLinhasReserva = totalRowIndex.HasValue
|
|
? TryNullableInt(GetCellString(ws, totalRowIndex.Value, colQtdLinhas))
|
|
: null;
|
|
|
|
if (totalGeralLinhasReserva.HasValue)
|
|
{
|
|
var somaPorDdd = sumQtdByDdd.Values.Sum();
|
|
if (somaPorDdd != totalGeralLinhasReserva.Value)
|
|
{
|
|
Console.WriteLine($"[WARN] RESUMO/RESERVA TOTAL GERAL: planilha={totalGeralLinhasReserva.Value} vs SOMA(DDD)={somaPorDdd}");
|
|
}
|
|
}
|
|
|
|
if (totalRowIndex == null)
|
|
{
|
|
return;
|
|
}
|
|
|
|
var totalEntity = new ResumoReservaTotal
|
|
{
|
|
TotalGeralLinhasReserva = totalGeralLinhasReserva,
|
|
QtdLinhasTotal = TryNullableInt(GetCellString(ws, totalRowIndex.Value, colQtdLinhas)),
|
|
Total = TryDecimal(GetCellString(ws, totalRowIndex.Value, colTotal)),
|
|
CreatedAt = now,
|
|
UpdatedAt = now
|
|
};
|
|
|
|
await _db.ResumoReservaTotals.AddAsync(totalEntity);
|
|
await _db.SaveChangesAsync();
|
|
}
|
|
|
|
private static int FindSectionRow(IXLWorksheet ws, string sectionName)
|
|
{
|
|
var normalizedTarget = NormalizeHeader(sectionName);
|
|
foreach (var row in ws.RowsUsed())
|
|
{
|
|
foreach (var cell in row.CellsUsed())
|
|
{
|
|
var key = NormalizeHeader(cell.GetString());
|
|
if (string.IsNullOrWhiteSpace(key)) continue;
|
|
if (key.Contains(normalizedTarget)) return row.RowNumber();
|
|
}
|
|
}
|
|
|
|
return 0;
|
|
}
|
|
|
|
private static int FindHeaderRowForReserva(IXLWorksheet ws, int startRow, int lastRow)
|
|
{
|
|
for (int r = startRow; r <= lastRow; r++)
|
|
{
|
|
var row = ws.Row(r);
|
|
if (!row.CellsUsed().Any()) continue;
|
|
|
|
var map = BuildHeaderMap(row);
|
|
var hasDdd = GetCol(map, "DDD") > 0;
|
|
var hasFranquia = GetColAny(map, "FRANQUIA GB", "FRAQUIA GB") > 0;
|
|
var hasQtd = GetColAny(map, "QTD. DE LINHAS", "QTD DE LINHAS", "QTD. LINHAS", "QTDLINHAS") > 0;
|
|
|
|
if (hasDdd && hasFranquia && hasQtd)
|
|
{
|
|
return r;
|
|
}
|
|
}
|
|
|
|
return 0;
|
|
}
|
|
|
|
private async Task ImportControleRecebidosSheet(IXLWorksheet ws, int year)
|
|
{
|
|
var buffer = new List<ControleRecebidoLine>(500);
|
|
|
|
var firstRow = ws.FirstRowUsed()?.RowNumber() ?? 1;
|
|
var lastRow = ws.LastRowUsed()?.RowNumber() ?? firstRow;
|
|
var rowIndex = firstRow;
|
|
decimal? lastValorDaNf = null;
|
|
decimal? lastValorUnit = null;
|
|
|
|
while (rowIndex <= lastRow)
|
|
{
|
|
var row = ws.Row(rowIndex);
|
|
if (!IsControleRecebidosHeader(row))
|
|
{
|
|
rowIndex++;
|
|
continue;
|
|
}
|
|
|
|
var map = BuildHeaderMap(row);
|
|
int colItem = GetCol(map, "ITEM");
|
|
if (colItem == 0)
|
|
{
|
|
rowIndex++;
|
|
continue;
|
|
}
|
|
|
|
var isResumo = GetColAny(map, "QTD.", "QTD", "QUANTIDADE") > 0
|
|
&& GetColAny(map, "CHIP") == 0
|
|
&& GetColAny(map, "SERIAL") == 0;
|
|
|
|
lastValorDaNf = null;
|
|
lastValorUnit = null;
|
|
rowIndex++;
|
|
|
|
for (; rowIndex <= lastRow; rowIndex++)
|
|
{
|
|
var currentRow = ws.Row(rowIndex);
|
|
if (IsControleRecebidosHeader(currentRow))
|
|
{
|
|
rowIndex--;
|
|
break;
|
|
}
|
|
|
|
var itemStr = GetCellString(ws, rowIndex, colItem);
|
|
if (string.IsNullOrWhiteSpace(itemStr)) break;
|
|
|
|
var notaFiscal = GetCellByHeaderAny(ws, rowIndex, map, "NOTA FISCAL", "NOTA", "NF");
|
|
var chip = NullIfEmptyDigits(GetCellByHeaderAny(ws, rowIndex, map, "CHIP"));
|
|
var serial = GetCellByHeaderAny(ws, rowIndex, map, "SERIAL");
|
|
var conteudo = GetCellByHeaderAny(ws, rowIndex, map, "CONTEÚDO DA NF", "CONTEUDO DA NF");
|
|
var numeroLinha = NullIfEmptyDigits(GetCellByHeaderAny(ws, rowIndex, map, "NÚMERO DA LINHA", "NUMERO DA LINHA"));
|
|
var valorUnit = TryDecimal(GetCellByHeaderAny(ws, rowIndex, map, "VALOR UNIT.", "VALOR UNIT", "VALOR UNITÁRIO", "VALOR UNITARIO"));
|
|
if (valorUnit.HasValue)
|
|
{
|
|
lastValorUnit = valorUnit;
|
|
}
|
|
else
|
|
{
|
|
valorUnit = lastValorUnit;
|
|
}
|
|
var valorDaNf = TryDecimal(GetCellByHeaderAny(ws, rowIndex, map, "VALOR DA NF", "VALOR DA N F"));
|
|
if (valorDaNf.HasValue)
|
|
{
|
|
lastValorDaNf = valorDaNf;
|
|
}
|
|
else
|
|
{
|
|
valorDaNf = lastValorDaNf;
|
|
}
|
|
var dataDaNf = TryDateNoUtc(ws, rowIndex, map, "DATA DA NF");
|
|
var dataReceb = TryDateNoUtc(ws, rowIndex, map, "DATA DO RECEBIMENTO");
|
|
var qtd = TryNullableInt(GetCellByHeaderAny(ws, rowIndex, map, "QTD.", "QTD", "QUANTIDADE"));
|
|
|
|
var now = DateTime.UtcNow;
|
|
|
|
var e = new ControleRecebidoLine
|
|
{
|
|
Id = Guid.NewGuid(),
|
|
Ano = year,
|
|
Item = TryInt(itemStr),
|
|
NotaFiscal = string.IsNullOrWhiteSpace(notaFiscal) ? null : notaFiscal.Trim(),
|
|
Chip = chip,
|
|
Serial = string.IsNullOrWhiteSpace(serial) ? null : serial.Trim(),
|
|
ConteudoDaNf = string.IsNullOrWhiteSpace(conteudo) ? null : conteudo.Trim(),
|
|
NumeroDaLinha = numeroLinha,
|
|
ValorUnit = valorUnit,
|
|
ValorDaNf = valorDaNf,
|
|
DataDaNf = dataDaNf,
|
|
DataDoRecebimento = dataReceb,
|
|
Quantidade = qtd,
|
|
IsResumo = isResumo,
|
|
CreatedAt = now,
|
|
UpdatedAt = now
|
|
};
|
|
|
|
buffer.Add(e);
|
|
|
|
if (buffer.Count >= 500)
|
|
{
|
|
await _db.ControleRecebidoLines.AddRangeAsync(buffer);
|
|
await _db.SaveChangesAsync();
|
|
buffer.Clear();
|
|
}
|
|
}
|
|
|
|
rowIndex++;
|
|
}
|
|
|
|
if (buffer.Count > 0)
|
|
{
|
|
await _db.ControleRecebidoLines.AddRangeAsync(buffer);
|
|
await _db.SaveChangesAsync();
|
|
}
|
|
}
|
|
|
|
private sealed class ControleRecebidosWorksheetInfo
|
|
{
|
|
public ControleRecebidosWorksheetInfo(IXLWorksheet sheet, int year)
|
|
{
|
|
Sheet = sheet;
|
|
Year = year;
|
|
}
|
|
|
|
public IXLWorksheet Sheet { get; }
|
|
public int Year { get; }
|
|
}
|
|
|
|
private static IEnumerable<ControleRecebidosWorksheetInfo> GetControleRecebidosWorksheets(XLWorkbook wb)
|
|
{
|
|
var years = new[] { 2022, 2023, 2024, 2025 };
|
|
|
|
foreach (var ws in wb.Worksheets)
|
|
{
|
|
var name = NormalizeHeader(ws.Name);
|
|
var isControleRecebidos = name.Contains("CONTROLE") && name.Contains("RECEBIDOS");
|
|
var isRomaneio = name.Contains("ROMANEIO");
|
|
|
|
if (!isControleRecebidos && !isRomaneio) continue;
|
|
|
|
var year = years.FirstOrDefault(y => name.Contains(y.ToString()));
|
|
if (year == 0) continue;
|
|
|
|
yield return new ControleRecebidosWorksheetInfo(ws, year);
|
|
}
|
|
}
|
|
|
|
private static IXLWorksheet? FindControleRecebidosWorksheet(XLWorkbook wb, int year)
|
|
{
|
|
var normalizedName = NormalizeHeader($"CONTROLE DE RECEBIDOS {year}");
|
|
|
|
var ws = wb.Worksheets.FirstOrDefault(w => NormalizeHeader(w.Name) == normalizedName);
|
|
if (ws != null) return ws;
|
|
|
|
ws = wb.Worksheets.FirstOrDefault(w =>
|
|
{
|
|
var name = NormalizeHeader(w.Name);
|
|
return name.Contains("CONTROLE") && name.Contains("RECEBIDOS") && name.Contains(year.ToString());
|
|
});
|
|
if (ws != null) return ws;
|
|
|
|
if (year == 2024)
|
|
{
|
|
ws = wb.Worksheets.FirstOrDefault(w => NormalizeHeader(w.Name) == NormalizeHeader("CONTROLE DE RECEBIDOS"));
|
|
}
|
|
|
|
return ws;
|
|
}
|
|
|
|
private static bool IsControleRecebidosHeader(IXLRow row)
|
|
{
|
|
var hasItem = false;
|
|
var hasNota = false;
|
|
|
|
foreach (var cell in row.CellsUsed())
|
|
{
|
|
var k = NormalizeHeader(cell.GetString());
|
|
if (k == "ITEM") hasItem = true;
|
|
if (k == "NOTAFISCAL") hasNota = true;
|
|
if (hasItem && hasNota) return true;
|
|
}
|
|
|
|
return false;
|
|
}
|
|
|
|
private static bool IsChipsVirgensHeader(IXLRow row)
|
|
{
|
|
var hasItem = false;
|
|
var hasNumeroChip = false;
|
|
var hasObs = false;
|
|
|
|
foreach (var cell in row.CellsUsed())
|
|
{
|
|
var k = NormalizeHeader(cell.GetString());
|
|
if (k == "ITEM") hasItem = true;
|
|
if (k.Contains("CHIP")) hasNumeroChip = true;
|
|
if (k.Contains("OBS")) hasObs = true;
|
|
}
|
|
|
|
return hasItem && hasNumeroChip && hasObs;
|
|
}
|
|
|
|
// ==========================================================
|
|
// HELPERS (SEUS)
|
|
// ==========================================================
|
|
private static Dictionary<string, int> BuildHeaderMap(IXLRow headerRow)
|
|
{
|
|
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;
|
|
}
|
|
return map;
|
|
}
|
|
|
|
private static Dictionary<string, int> BuildHeaderMapRange(IXLRow headerRow, int startCol, int endCol)
|
|
{
|
|
var map = new Dictionary<string, int>(StringComparer.OrdinalIgnoreCase);
|
|
foreach (var cell in headerRow.CellsUsed())
|
|
{
|
|
var col = cell.Address.ColumnNumber;
|
|
if (col < startCol || col > endCol) continue;
|
|
|
|
var k = NormalizeHeader(cell.GetString());
|
|
if (!string.IsNullOrWhiteSpace(k) && !map.ContainsKey(k))
|
|
map[k] = col;
|
|
}
|
|
return map;
|
|
}
|
|
|
|
private static int FindHeaderColumn(IXLRow headerRow, int startCol, int endCol, params string[] headerKeys)
|
|
{
|
|
var normalizedKeys = headerKeys.Select(NormalizeHeader).Where(k => !string.IsNullOrWhiteSpace(k)).ToArray();
|
|
if (normalizedKeys.Length == 0) return 0;
|
|
|
|
for (int col = startCol; col <= endCol; col++)
|
|
{
|
|
var key = NormalizeHeader(headerRow.Cell(col).GetString());
|
|
if (string.IsNullOrWhiteSpace(key)) continue;
|
|
|
|
foreach (var wanted in normalizedKeys)
|
|
{
|
|
if (key == wanted || key.Contains(wanted))
|
|
{
|
|
return col;
|
|
}
|
|
}
|
|
}
|
|
|
|
return 0;
|
|
}
|
|
|
|
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,
|
|
VivoSync = x.VivoSync,
|
|
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,
|
|
TipoDeChip = x.TipoDeChip
|
|
};
|
|
|
|
private static void ApplyReservaRule(MobileLine x)
|
|
{
|
|
if (IsReservaValue(x.Cliente) || IsReservaValue(x.Usuario) || IsReservaValue(x.Skil))
|
|
{
|
|
x.Cliente = "RESERVA";
|
|
x.Usuario = "RESERVA";
|
|
x.Skil = "RESERVA";
|
|
}
|
|
}
|
|
|
|
private static bool IsReservaValue(string? value)
|
|
=> string.Equals(value?.Trim(), "RESERVA", StringComparison.OrdinalIgnoreCase);
|
|
|
|
private static int GetCol(Dictionary<string, int> map, string name)
|
|
=> map.TryGetValue(NormalizeHeader(name), out var c) ? c : 0;
|
|
|
|
private Guid? GetTenantIdFromClaims()
|
|
{
|
|
var claim = User?.FindFirst("tenantId")?.Value
|
|
?? User?.FindFirst("tenant")?.Value;
|
|
return Guid.TryParse(claim, out var tenantId) ? tenantId : null;
|
|
}
|
|
|
|
private static int GetColAny(Dictionary<string, int> map, params string[] headers)
|
|
{
|
|
foreach (var h in headers)
|
|
{
|
|
var k = NormalizeHeader(h);
|
|
if (map.TryGetValue(k, out var c)) return c;
|
|
}
|
|
return 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 GetCellByHeaderAny(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)
|
|
{
|
|
if (col <= 0) return "";
|
|
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;
|
|
return TryDateCell(ws, row, c);
|
|
}
|
|
|
|
private static DateTime? TryDateNoUtc(IXLWorksheet ws, int row, Dictionary<string, int> map, string header)
|
|
{
|
|
var k = NormalizeHeader(header);
|
|
if (!map.TryGetValue(k, out var c)) return null;
|
|
return TryDateCellNoUtc(ws, row, c);
|
|
}
|
|
|
|
private static DateTime? TryDateCell(IXLWorksheet ws, int row, int col)
|
|
{
|
|
if (col <= 0) return null;
|
|
|
|
var cell = ws.Cell(row, col);
|
|
|
|
if (cell.DataType == XLDataType.DateTime)
|
|
return ToUtc(cell.GetDateTime());
|
|
|
|
if (cell.TryGetValue<DateTime>(out var dt))
|
|
return ToUtc(dt);
|
|
|
|
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 DateTime? TryDateCellNoUtc(IXLWorksheet ws, int row, int col)
|
|
{
|
|
if (col <= 0) return null;
|
|
|
|
var cell = ws.Cell(row, col);
|
|
|
|
if (cell.DataType == XLDataType.DateTime)
|
|
return ToUtcDateOnly(cell.GetDateTime());
|
|
|
|
if (cell.TryGetValue<DateTime>(out var dt))
|
|
return ToUtcDateOnly(dt);
|
|
|
|
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 ToUtcDateOnly(d);
|
|
|
|
return null;
|
|
}
|
|
|
|
private static DateTime ToUtcDateOnly(DateTime dt)
|
|
{
|
|
return new DateTime(dt.Year, dt.Month, dt.Day, 12, 0, 0, DateTimeKind.Utc);
|
|
}
|
|
|
|
private static decimal? TryDecimal(string? s)
|
|
{
|
|
if (string.IsNullOrWhiteSpace(s)) return null;
|
|
|
|
s = s.Replace("R$", "", StringComparison.OrdinalIgnoreCase).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;
|
|
|
|
var s2 = s.Replace(".", "").Replace(",", ".");
|
|
if (decimal.TryParse(s2, 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? NullIfEmptyDigits(string? s)
|
|
{
|
|
var d = OnlyDigits(s);
|
|
return string.IsNullOrWhiteSpace(d) ? null : d;
|
|
}
|
|
|
|
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(" ", "")
|
|
.Replace("\t", "")
|
|
.Replace("\n", "")
|
|
.Replace("\r", "");
|
|
}
|
|
|
|
// ==========================================================
|
|
// ✅ BILLING HELPERS
|
|
// ==========================================================
|
|
private static int GetLastUsedColumn(IXLWorksheet ws, int headerRowIndex)
|
|
{
|
|
var row = ws.Row(headerRowIndex);
|
|
var last = row.LastCellUsed()?.Address.ColumnNumber ?? 1;
|
|
var last2 = ws.LastColumnUsed()?.ColumnNumber() ?? last;
|
|
return Math.Max(last, last2);
|
|
}
|
|
|
|
private static bool RowHasAnyText(IXLRow row)
|
|
{
|
|
foreach (var c in row.CellsUsed())
|
|
{
|
|
if (!string.IsNullOrWhiteSpace(c.GetValue<string>()))
|
|
return true;
|
|
}
|
|
return false;
|
|
}
|
|
|
|
private static int FindColByAny(IXLRow headerRow, int lastCol, params string[] headers)
|
|
{
|
|
var wanted = new HashSet<string>(StringComparer.OrdinalIgnoreCase);
|
|
foreach (var h in headers) wanted.Add(NormalizeHeader(h));
|
|
|
|
for (int col = 1; col <= lastCol; col++)
|
|
{
|
|
var key = NormalizeHeader(headerRow.Cell(col).GetString());
|
|
if (string.IsNullOrWhiteSpace(key)) continue;
|
|
|
|
if (wanted.Contains(key)) return col;
|
|
|
|
foreach (var w in wanted)
|
|
{
|
|
if (!string.IsNullOrWhiteSpace(w) && key.Contains(w))
|
|
return col;
|
|
}
|
|
}
|
|
return 0;
|
|
}
|
|
|
|
private static string GetMergedGroupKeyAt(IXLRow groupRow, int col)
|
|
{
|
|
for (int c = col; c >= 1; c--)
|
|
{
|
|
var g = NormalizeHeader(groupRow.Cell(c).GetString());
|
|
if (!string.IsNullOrWhiteSpace(g))
|
|
return g;
|
|
}
|
|
return "";
|
|
}
|
|
|
|
private static int FindColInGroup(IXLRow groupRow, IXLRow headerRow, int lastCol, string groupKey, params string[] headerKeys)
|
|
{
|
|
var gk = NormalizeHeader(groupKey);
|
|
var wanted = headerKeys.Select(NormalizeHeader).ToArray();
|
|
|
|
for (int col = 1; col <= lastCol; col++)
|
|
{
|
|
var groupAtCol = GetMergedGroupKeyAt(groupRow, col);
|
|
if (string.IsNullOrWhiteSpace(groupAtCol)) continue;
|
|
|
|
if (!groupAtCol.Contains(gk)) continue;
|
|
|
|
var h = NormalizeHeader(headerRow.Cell(col).GetString());
|
|
|
|
if (string.IsNullOrWhiteSpace(h) && wanted.Any(w => w == ""))
|
|
return col;
|
|
|
|
foreach (var w in wanted)
|
|
{
|
|
if (string.IsNullOrWhiteSpace(w)) continue;
|
|
|
|
if (h == w) return col;
|
|
if (!string.IsNullOrWhiteSpace(h) && h.Contains(w)) return col;
|
|
}
|
|
}
|
|
|
|
return 0;
|
|
}
|
|
}
|
|
}
|