192 lines
7.3 KiB
C#
192 lines
7.3 KiB
C#
using System.Globalization;
|
|
using ExpenseTracker.Api.Data;
|
|
using ExpenseTracker.Api.DTOs;
|
|
using ExpenseTracker.Api.Models;
|
|
using Microsoft.EntityFrameworkCore;
|
|
|
|
namespace ExpenseTracker.Api.Services;
|
|
|
|
/// <summary>
|
|
/// Parses a bank-export CSV (semicolon-separated, French locale) and imports
|
|
/// the rows as <see cref="Entry"/> records for the given month.
|
|
///
|
|
/// Expected columns (1-based):
|
|
/// 1 Date de comptabilisation → EntryDate (dd/MM/yyyy)
|
|
/// 2 Libelle simplifie → (ignored)
|
|
/// 3 Libelle operation → Label
|
|
/// 4 Reference → (ignored)
|
|
/// 5 Informations complementaires → (ignored)
|
|
/// 6 Type operation → (ignored)
|
|
/// 7 Categorie → (ignored)
|
|
/// 8 Sous categorie → Category name (optional lookup)
|
|
/// 9 Debit → Amount when negative → EntryType.EXPENSE
|
|
/// 10 Credit → Amount when present → EntryType.INCOME
|
|
/// 11+ (remaining columns ignored)
|
|
///
|
|
/// Amount values use a comma as the decimal separator (French locale).
|
|
/// Rows whose Debit and Credit are both empty are silently skipped.
|
|
/// </summary>
|
|
public class CsvImportService
|
|
{
|
|
private const int ColDate = 0; // Date de comptabilisation
|
|
private const int ColLabel = 2; // Libelle operation
|
|
private const int ColSousCategorie = 7; // Sous categorie
|
|
private const int ColDebit = 8; // Debit (negative value or empty)
|
|
private const int ColCredit = 9; // Credit (positive value or empty)
|
|
private const int MinColumns = 10;
|
|
|
|
private static readonly CultureInfo FrenchCulture =
|
|
CultureInfo.GetCultureInfo("fr-FR");
|
|
|
|
private readonly ILogger<CsvImportService> _logger;
|
|
|
|
public CsvImportService(ILogger<CsvImportService> logger)
|
|
{
|
|
_logger = logger;
|
|
}
|
|
|
|
public async Task<ImportResultDto> ImportAsync(Guid monthId, Stream csvStream, AppDbContext db)
|
|
{
|
|
var errors = new List<ImportErrorDto>();
|
|
var validEntries = new List<Entry>();
|
|
|
|
// Load all categories once for case-insensitive lookup.
|
|
// "Autre" is the fallback category used when no Sous categorie matches.
|
|
var categories = await db.Categories
|
|
.AsNoTracking()
|
|
.ToListAsync();
|
|
|
|
var fallbackCategory = categories.FirstOrDefault(c =>
|
|
c.Name.Equals("Autre", StringComparison.OrdinalIgnoreCase));
|
|
|
|
using var reader = new StreamReader(csvStream);
|
|
|
|
// Skip header line
|
|
var header = await reader.ReadLineAsync();
|
|
if (header is null)
|
|
return new ImportResultDto(0, 0, errors);
|
|
|
|
int lineNumber = 1; // header is line 1
|
|
string? line;
|
|
|
|
while ((line = await reader.ReadLineAsync()) is not null)
|
|
{
|
|
lineNumber++;
|
|
|
|
// Skip blank lines and lines that contain only semicolons (bank file padding)
|
|
if (string.IsNullOrWhiteSpace(line) || line.Replace(";", "").Trim().Length == 0)
|
|
continue;
|
|
|
|
var columns = line.Split(';');
|
|
|
|
if (columns.Length < MinColumns)
|
|
{
|
|
errors.Add(new ImportErrorDto(lineNumber,
|
|
$"La ligne ne contient pas les {MinColumns} colonnes attendues."));
|
|
continue;
|
|
}
|
|
|
|
var dateRaw = columns[ColDate].Trim();
|
|
var labelRaw = columns[ColLabel].Trim();
|
|
var sousCategorieRaw = columns[ColSousCategorie].Trim();
|
|
var debitRaw = columns[ColDebit].Trim();
|
|
var creditRaw = columns[ColCredit].Trim();
|
|
|
|
// Determine type and raw amount string
|
|
bool hasDebit = !string.IsNullOrEmpty(debitRaw);
|
|
bool hasCredit = !string.IsNullOrEmpty(creditRaw);
|
|
|
|
if (!hasDebit && !hasCredit)
|
|
{
|
|
// Neither column filled — silently skip (padding row)
|
|
continue;
|
|
}
|
|
|
|
var entryType = hasCredit ? EntryType.INCOME : EntryType.EXPENSE;
|
|
var amountRaw = hasCredit ? creditRaw : debitRaw;
|
|
|
|
// Parse date (dd/MM/yyyy)
|
|
if (!DateOnly.TryParseExact(dateRaw, "dd/MM/yyyy", FrenchCulture,
|
|
DateTimeStyles.None, out var entryDate))
|
|
{
|
|
errors.Add(new ImportErrorDto(lineNumber,
|
|
$"Date invalide '{dateRaw}'. Format attendu : JJ/MM/AAAA."));
|
|
continue;
|
|
}
|
|
|
|
// Parse amount — French locale uses comma as decimal separator.
|
|
// Debit values may carry a leading minus sign; we store the absolute value.
|
|
if (!decimal.TryParse(amountRaw, NumberStyles.Number, FrenchCulture, out var amount))
|
|
{
|
|
errors.Add(new ImportErrorDto(lineNumber,
|
|
$"Montant invalide '{amountRaw}'."));
|
|
continue;
|
|
}
|
|
|
|
amount = Math.Abs(amount);
|
|
|
|
if (amount <= 0)
|
|
{
|
|
errors.Add(new ImportErrorDto(lineNumber,
|
|
$"Le montant doit être supérieur à zéro (valeur lue : '{amountRaw}')."));
|
|
continue;
|
|
}
|
|
|
|
// Validate label
|
|
if (string.IsNullOrWhiteSpace(labelRaw))
|
|
{
|
|
errors.Add(new ImportErrorDto(lineNumber, "Le libellé de l'opération est vide."));
|
|
continue;
|
|
}
|
|
|
|
if (labelRaw.Length > 200)
|
|
{
|
|
errors.Add(new ImportErrorDto(lineNumber,
|
|
"Le libellé dépasse 200 caractères."));
|
|
continue;
|
|
}
|
|
|
|
// Category lookup — the CSV "Sous categorie" value (e.g. "Banque et assurance - autre")
|
|
// is matched against stored category names (e.g. "Banque et assurance") using a
|
|
// Contains check: the stored name must appear as a substring of the CSV value.
|
|
// This handles the " - <qualifier>" suffixes present in bank exports.
|
|
// If no category matches, the entry falls back to the "Autre" category.
|
|
Guid? categoryId = null;
|
|
if (!string.IsNullOrWhiteSpace(sousCategorieRaw))
|
|
{
|
|
var category = categories.FirstOrDefault(c =>
|
|
!c.Name.Equals("Autre", StringComparison.OrdinalIgnoreCase) &&
|
|
sousCategorieRaw.Contains(c.Name, StringComparison.OrdinalIgnoreCase));
|
|
|
|
categoryId = category?.Id ?? fallbackCategory?.Id;
|
|
}
|
|
else
|
|
{
|
|
categoryId = fallbackCategory?.Id;
|
|
}
|
|
|
|
validEntries.Add(new Entry
|
|
{
|
|
Id = Guid.NewGuid(),
|
|
MonthId = monthId,
|
|
Type = entryType,
|
|
Amount = amount,
|
|
Label = labelRaw,
|
|
CategoryId = categoryId,
|
|
EntryDate = entryDate,
|
|
CreatedAt = DateTime.UtcNow
|
|
});
|
|
}
|
|
|
|
if (validEntries.Count > 0)
|
|
{
|
|
db.Entries.AddRange(validEntries);
|
|
await db.SaveChangesAsync();
|
|
_logger.LogInformation(
|
|
"Imported {Count} entries for month {MonthId}.", validEntries.Count, monthId);
|
|
}
|
|
|
|
return new ImportResultDto(validEntries.Count, errors.Count, errors);
|
|
}
|
|
}
|