455 lines
16 KiB
C#
455 lines
16 KiB
C#
using System.Globalization;
|
|
using System.Text;
|
|
using ClosedXML.Excel;
|
|
using line_gestao_api.Data;
|
|
using line_gestao_api.Dtos;
|
|
using line_gestao_api.Models;
|
|
using Microsoft.EntityFrameworkCore;
|
|
|
|
namespace line_gestao_api.Services;
|
|
|
|
public sealed class ParcelamentosImportService
|
|
{
|
|
private readonly AppDbContext _db;
|
|
|
|
public ParcelamentosImportService(AppDbContext db)
|
|
{
|
|
_db = db;
|
|
}
|
|
|
|
public async Task<ParcelamentosImportSummaryDto> ImportFromWorkbookAsync(XLWorkbook wb, bool replaceAll, CancellationToken cancellationToken = default)
|
|
{
|
|
var ws = FindWorksheet(wb);
|
|
if (ws == null)
|
|
{
|
|
return new ParcelamentosImportSummaryDto
|
|
{
|
|
Erros =
|
|
{
|
|
new ParcelamentosImportErrorDto
|
|
{
|
|
LinhaExcel = 0,
|
|
Motivo = "Aba 'PARCELAMENTOS DE APARELHOS' ou 'PARCELAMENTOS' não encontrada."
|
|
}
|
|
}
|
|
};
|
|
}
|
|
|
|
if (replaceAll)
|
|
{
|
|
await _db.ParcelamentoMonthValues.ExecuteDeleteAsync(cancellationToken);
|
|
await _db.ParcelamentoLines.ExecuteDeleteAsync(cancellationToken);
|
|
}
|
|
|
|
var headerRowIndex = FindHeaderRow(ws);
|
|
if (headerRowIndex == 0)
|
|
{
|
|
return new ParcelamentosImportSummaryDto
|
|
{
|
|
Erros =
|
|
{
|
|
new ParcelamentosImportErrorDto
|
|
{
|
|
LinhaExcel = 0,
|
|
Motivo = "Cabeçalho 'LINHA' não encontrado na aba de parcelamentos."
|
|
}
|
|
}
|
|
};
|
|
}
|
|
|
|
var yearRowIndex = headerRowIndex - 1;
|
|
var headerRow = ws.Row(headerRowIndex);
|
|
var map = BuildHeaderMap(headerRow);
|
|
|
|
var colLinha = GetCol(map, "LINHA");
|
|
var colAnoRef = GetColAny(map, "ANO REF", "ANOREF", "ANO REF.", "ANO REFERENCIA", "ANO REFERÊNCIA");
|
|
var colItem = GetCol(map, "ITEM");
|
|
var colCliente = GetCol(map, "CLIENTE");
|
|
var colQtParcelas = GetColAny(map, "QT PARCELAS", "QT. PARCELAS", "QT PARCELAS (NN/TT)", "QTDE PARCELAS");
|
|
var colValorCheio = GetColAny(map, "VALOR CHEIO");
|
|
var colDesconto = GetColAny(map, "DESCONTO");
|
|
var colValorComDesconto = GetColAny(map, "VALOR C/ DESCONTO", "VALOR COM DESCONTO");
|
|
|
|
if (colLinha == 0 || colValorComDesconto == 0 || colAnoRef == 0 || colItem == 0)
|
|
{
|
|
return new ParcelamentosImportSummaryDto
|
|
{
|
|
Erros =
|
|
{
|
|
new ParcelamentosImportErrorDto
|
|
{
|
|
LinhaExcel = headerRowIndex,
|
|
Motivo = "Colunas obrigatórias não encontradas (LINHA / ANO REF / ITEM / VALOR C/ DESCONTO)."
|
|
}
|
|
}
|
|
};
|
|
}
|
|
|
|
var yearMap = BuildYearMap(ws, yearRowIndex, headerRow);
|
|
var monthColumns = BuildMonthColumns(headerRow, colValorComDesconto + 1);
|
|
|
|
var existing = await _db.ParcelamentoLines
|
|
.AsNoTracking()
|
|
.ToListAsync(cancellationToken);
|
|
var existingByKey = existing
|
|
.Where(x => x.AnoRef.HasValue && x.Item.HasValue)
|
|
.ToDictionary(x => (x.AnoRef!.Value, x.Item!.Value), x => x.Id);
|
|
|
|
var summary = new ParcelamentosImportSummaryDto();
|
|
var lastRow = ws.LastRowUsed()?.RowNumber() ?? headerRowIndex;
|
|
|
|
for (int row = headerRowIndex + 1; row <= lastRow; row++)
|
|
{
|
|
var linhaValue = GetCellString(ws, row, colLinha);
|
|
var itemStr = GetCellString(ws, row, colItem);
|
|
if (string.IsNullOrWhiteSpace(itemStr) && string.IsNullOrWhiteSpace(linhaValue))
|
|
{
|
|
break;
|
|
}
|
|
|
|
summary.Lidos++;
|
|
|
|
var anoRef = TryNullableInt(GetCellString(ws, row, colAnoRef));
|
|
var item = TryNullableInt(itemStr);
|
|
|
|
if (!item.HasValue)
|
|
{
|
|
summary.Erros.Add(new ParcelamentosImportErrorDto
|
|
{
|
|
LinhaExcel = row,
|
|
Motivo = "Item inválido ou vazio.",
|
|
Valor = itemStr
|
|
});
|
|
continue;
|
|
}
|
|
|
|
if (!anoRef.HasValue)
|
|
{
|
|
summary.Erros.Add(new ParcelamentosImportErrorDto
|
|
{
|
|
LinhaExcel = row,
|
|
Motivo = "AnoRef inválido ou vazio.",
|
|
Valor = GetCellString(ws, row, colAnoRef)
|
|
});
|
|
continue;
|
|
}
|
|
|
|
var qtParcelas = GetCellString(ws, row, colQtParcelas);
|
|
ParseParcelas(qtParcelas, out var parcelaAtual, out var totalParcelas);
|
|
|
|
var parcelamento = new ParcelamentoLine
|
|
{
|
|
AnoRef = anoRef,
|
|
Item = item,
|
|
Linha = string.IsNullOrWhiteSpace(linhaValue) ? null : linhaValue.Trim(),
|
|
Cliente = NormalizeText(GetCellString(ws, row, colCliente)),
|
|
QtParcelas = string.IsNullOrWhiteSpace(qtParcelas) ? null : qtParcelas.Trim(),
|
|
ParcelaAtual = parcelaAtual,
|
|
TotalParcelas = totalParcelas,
|
|
ValorCheio = TryDecimal(GetCellString(ws, row, colValorCheio)),
|
|
Desconto = TryDecimal(GetCellString(ws, row, colDesconto)),
|
|
ValorComDesconto = TryDecimal(GetCellString(ws, row, colValorComDesconto)),
|
|
UpdatedAt = DateTime.UtcNow
|
|
};
|
|
|
|
if (existingByKey.TryGetValue((anoRef.Value, item.Value), out var existingId))
|
|
{
|
|
var existingEntity = await _db.ParcelamentoLines
|
|
.FirstOrDefaultAsync(x => x.Id == existingId, cancellationToken);
|
|
if (existingEntity == null)
|
|
{
|
|
existingByKey.Remove((anoRef ?? 0, item.Value));
|
|
}
|
|
else
|
|
{
|
|
existingEntity.AnoRef = parcelamento.AnoRef;
|
|
existingEntity.Item = parcelamento.Item;
|
|
existingEntity.Linha = parcelamento.Linha;
|
|
existingEntity.Cliente = parcelamento.Cliente;
|
|
existingEntity.QtParcelas = parcelamento.QtParcelas;
|
|
existingEntity.ParcelaAtual = parcelamento.ParcelaAtual;
|
|
existingEntity.TotalParcelas = parcelamento.TotalParcelas;
|
|
existingEntity.ValorCheio = parcelamento.ValorCheio;
|
|
existingEntity.Desconto = parcelamento.Desconto;
|
|
existingEntity.ValorComDesconto = parcelamento.ValorComDesconto;
|
|
existingEntity.UpdatedAt = parcelamento.UpdatedAt;
|
|
|
|
await _db.ParcelamentoMonthValues
|
|
.Where(x => x.ParcelamentoLineId == existingEntity.Id)
|
|
.ExecuteDeleteAsync(cancellationToken);
|
|
|
|
var monthValues = BuildMonthValues(ws, headerRowIndex, row, monthColumns, yearMap, existingEntity.Id, summary);
|
|
if (monthValues.Count > 0)
|
|
{
|
|
await _db.ParcelamentoMonthValues.AddRangeAsync(monthValues, cancellationToken);
|
|
}
|
|
|
|
summary.Atualizados++;
|
|
continue;
|
|
}
|
|
}
|
|
|
|
parcelamento.CreatedAt = DateTime.UtcNow;
|
|
parcelamento.MonthValues = BuildMonthValues(ws, headerRowIndex, row, monthColumns, yearMap, parcelamento.Id, summary);
|
|
summary.Inseridos++;
|
|
|
|
await _db.ParcelamentoLines.AddAsync(parcelamento, cancellationToken);
|
|
}
|
|
|
|
await _db.SaveChangesAsync(cancellationToken);
|
|
return summary;
|
|
}
|
|
|
|
private static IXLWorksheet? FindWorksheet(XLWorkbook wb)
|
|
{
|
|
return wb.Worksheets.FirstOrDefault(w => NormalizeHeader(w.Name) == NormalizeHeader("PARCELAMENTOS DE APARELHOS"))
|
|
?? wb.Worksheets.FirstOrDefault(w => NormalizeHeader(w.Name) == NormalizeHeader("PARCELAMENTOS"))
|
|
?? wb.Worksheets.FirstOrDefault(w =>
|
|
{
|
|
var normalized = NormalizeHeader(w.Name);
|
|
return normalized.Contains("PARCELAMENTO") || normalized.Contains("PARCELAMENTOS") || normalized.Contains("PARECALEMENTO");
|
|
});
|
|
}
|
|
|
|
private static int FindHeaderRow(IXLWorksheet ws)
|
|
{
|
|
var firstRow = ws.FirstRowUsed()?.RowNumber() ?? 1;
|
|
var lastRow = Math.Min(firstRow + 20, ws.LastRowUsed()?.RowNumber() ?? firstRow);
|
|
|
|
for (int r = firstRow; r <= lastRow; r++)
|
|
{
|
|
var row = ws.Row(r);
|
|
foreach (var cell in row.CellsUsed())
|
|
{
|
|
if (NormalizeHeader(cell.GetString()) == NormalizeHeader("LINHA"))
|
|
{
|
|
return r;
|
|
}
|
|
}
|
|
}
|
|
|
|
return 0;
|
|
}
|
|
|
|
private static Dictionary<int, int> BuildYearMap(IXLWorksheet ws, int yearRowIndex, IXLRow headerRow)
|
|
{
|
|
var yearMap = new Dictionary<int, int>();
|
|
var lastCol = headerRow.LastCellUsed()?.Address.ColumnNumber ?? 1;
|
|
|
|
if (yearRowIndex <= 0)
|
|
{
|
|
return yearMap;
|
|
}
|
|
|
|
for (int col = 1; col <= lastCol; col++)
|
|
{
|
|
var yearCell = ws.Cell(yearRowIndex, col);
|
|
var yearText = yearCell.GetString();
|
|
if (string.IsNullOrWhiteSpace(yearText))
|
|
{
|
|
var merged = ws.MergedRanges.FirstOrDefault(r =>
|
|
r.RangeAddress.FirstAddress.RowNumber == yearRowIndex &&
|
|
r.RangeAddress.FirstAddress.ColumnNumber <= col &&
|
|
r.RangeAddress.LastAddress.ColumnNumber >= col);
|
|
|
|
if (merged != null)
|
|
{
|
|
yearText = merged.FirstCell().GetString();
|
|
}
|
|
}
|
|
|
|
if (int.TryParse(OnlyDigits(yearText), out var year))
|
|
{
|
|
yearMap[col] = year;
|
|
}
|
|
}
|
|
|
|
return yearMap;
|
|
}
|
|
|
|
private static List<int> BuildMonthColumns(IXLRow headerRow, int startCol)
|
|
{
|
|
var lastCol = headerRow.LastCellUsed()?.Address.ColumnNumber ?? startCol;
|
|
var months = new List<int>();
|
|
for (int col = startCol; col <= lastCol; col++)
|
|
{
|
|
var header = NormalizeHeader(headerRow.Cell(col).GetString());
|
|
if (ToMonthNumber(header).HasValue)
|
|
{
|
|
months.Add(col);
|
|
}
|
|
}
|
|
|
|
return months;
|
|
}
|
|
|
|
private static List<ParcelamentoMonthValue> BuildMonthValues(
|
|
IXLWorksheet ws,
|
|
int headerRowIndex,
|
|
int row,
|
|
List<int> monthColumns,
|
|
Dictionary<int, int> yearMap,
|
|
Guid parcelamentoId,
|
|
ParcelamentosImportSummaryDto summary)
|
|
{
|
|
var monthValues = new List<ParcelamentoMonthValue>();
|
|
foreach (var col in monthColumns)
|
|
{
|
|
if (!yearMap.TryGetValue(col, out var year))
|
|
{
|
|
continue;
|
|
}
|
|
|
|
var header = NormalizeHeader(ws.Cell(headerRowIndex, col).GetString());
|
|
var monthNumber = ToMonthNumber(header);
|
|
if (!monthNumber.HasValue)
|
|
{
|
|
continue;
|
|
}
|
|
|
|
var valueStr = ws.Cell(row, col).GetString();
|
|
var value = TryDecimal(valueStr);
|
|
if (!value.HasValue)
|
|
{
|
|
continue;
|
|
}
|
|
|
|
monthValues.Add(new ParcelamentoMonthValue
|
|
{
|
|
ParcelamentoLineId = parcelamentoId,
|
|
Competencia = new DateOnly(year, monthNumber.Value, 1),
|
|
Valor = value,
|
|
CreatedAt = DateTime.UtcNow
|
|
});
|
|
summary.ParcelasInseridas++;
|
|
}
|
|
|
|
return monthValues;
|
|
}
|
|
|
|
private static void ParseParcelas(string? qtParcelas, out int? parcelaAtual, out int? totalParcelas)
|
|
{
|
|
parcelaAtual = null;
|
|
totalParcelas = null;
|
|
if (string.IsNullOrWhiteSpace(qtParcelas))
|
|
{
|
|
return;
|
|
}
|
|
|
|
var parts = qtParcelas.Split('/', StringSplitOptions.RemoveEmptyEntries | StringSplitOptions.TrimEntries);
|
|
if (parts.Length >= 1 && int.TryParse(OnlyDigits(parts[0]), out var atual))
|
|
{
|
|
parcelaAtual = atual;
|
|
}
|
|
|
|
if (parts.Length >= 2 && int.TryParse(OnlyDigits(parts[1]), out var total))
|
|
{
|
|
totalParcelas = total;
|
|
}
|
|
}
|
|
|
|
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 int GetCol(Dictionary<string, int> map, string name)
|
|
=> map.TryGetValue(NormalizeHeader(name), out var c) ? c : 0;
|
|
|
|
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 GetCellString(IXLWorksheet ws, int row, int col)
|
|
{
|
|
if (col <= 0) return "";
|
|
return (ws.Cell(row, col).GetValue<string>() ?? "").Trim();
|
|
}
|
|
|
|
private static string? NormalizeText(string value)
|
|
=> string.IsNullOrWhiteSpace(value) ? null : value.Trim();
|
|
|
|
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? 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 int? ToMonthNumber(string? month)
|
|
{
|
|
if (string.IsNullOrWhiteSpace(month)) return null;
|
|
return NormalizeHeader(month) switch
|
|
{
|
|
"JAN" => 1,
|
|
"FEV" => 2,
|
|
"MAR" => 3,
|
|
"ABR" => 4,
|
|
"MAI" => 5,
|
|
"JUN" => 6,
|
|
"JUL" => 7,
|
|
"AGO" => 8,
|
|
"SET" => 9,
|
|
"OUT" => 10,
|
|
"NOV" => 11,
|
|
"DEZ" => 12,
|
|
_ => null
|
|
};
|
|
}
|
|
|
|
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", "");
|
|
}
|
|
}
|