protected override void ImportRows(TextFieldParser csvReader)
{
var movimentos = new List <Movimento>();
var movimentoAdapter = new MovimentoAdapter(ConnectionString);
var existentMovimentos = movimentoAdapter.GetMovimentos();
OnNumberOfRowsToImportDetermined(new NumberOfRowsEventArgs {
NumberOfRows = File.ReadAllLines(filePath).Length - 4
});
while (!csvReader.EndOfData)
{
var movimento = new Movimento();
var fieldData = csvReader.ReadFields();
if (fieldData == null)
{
OnRowTreated(EventArgs.Empty);
continue;
}
if (AllFieldsEmpty(fieldData))
{
break;
}
foreach (var columnToRead in columnsToRead)
{
CopyExcelFileEntityColumnFromFile(ref movimento, columnToRead.Key, fieldData[columnToRead.Value]);
}
OnRowTreated(EventArgs.Empty);
if (string.IsNullOrEmpty(movimento.IBAN) || !IsNewMovimento(existentMovimentos, movimento))
{
continue;
}
movimentos.Add(movimento);
}
movimentoAdapter.StoreMovimentos(movimentos);
}
private bool IsNewMovimento(List <Movimento> existentMovimentos, Movimento newMovimento)
{
if (existentMovimentos.Count == 0)
{
return(true);
}
var movsToSearch = existentMovimentos
.Where(m => m.DtValor == newMovimento.DtValor && m.DtContab == newMovimento.DtContab).ToList();
var existent = movsToSearch.FirstOrDefault(m => m.Descricao1 == newMovimento.Descricao1 &&
m.Descricao2 == newMovimento.Descricao2 && m.Descricao3 == newMovimento.Descricao3 &&
(!m.SubTotal.HasValue && !newMovimento.SubTotal.HasValue ||
m.SubTotal.HasValue && newMovimento.SubTotal.HasValue && m.SubTotal == newMovimento.SubTotal) &&
(!m.Debito.HasValue && !newMovimento.Debito.HasValue ||
m.Debito.HasValue && newMovimento.Debito.HasValue && m.Debito == newMovimento.Debito) &&
(!m.Credito.HasValue && !newMovimento.Credito.HasValue ||
m.Credito.HasValue && newMovimento.Credito.HasValue && m.Credito == newMovimento.Credito) &&
(!m.Saldo.HasValue && !newMovimento.Saldo.HasValue ||
m.Saldo.HasValue && newMovimento.Saldo.HasValue && m.Saldo == newMovimento.Saldo));
return(existent == null);
}
private void CopyExcelFileEntityColumnFromFile(ref Movimento movimento, string columnName, string cellValue)
{
if (string.IsNullOrEmpty(cellValue))
{
return;
}
switch (columnName)
{
case Constants.colDateEval:
if (!DateTime.TryParseExact(cellValue, "dd.MM.yyyy", null, DateTimeStyles.None, out var dateEVal))
{
if (!DateTime.TryParseExact(cellValue, "dd-MM-yy", null, DateTimeStyles.None, out dateEVal))
{
dateEVal = DateTime.ParseExact(cellValue, "dd/MM/yyyy HH:mm:ss", CultureInfo.InvariantCulture);
}
}
movimento.DtEval = dateEVal;
break;
case Constants.colRelBancaire:
if (double.TryParse(cellValue.Replace("'", string.Empty).Replace(",", ".").Replace(".", string.Empty), out var relBanc))
{
movimento.RelBancaria = relBanc;
}
break;
case Constants.colPortfeuille:
movimento.Portofolio = cellValue;
break;
case Constants.colProduit:
movimento.Produto = cellValue;
break;
case Constants.colIBAN:
movimento.IBAN = cellValue;
break;
case Constants.colMonn:
movimento.Moeda = cellValue;
break;
case Constants.colDateDu:
if (!DateTime.TryParseExact(cellValue, "dd.MM.yyyy", null, DateTimeStyles.None, out var dateDu))
{
if (!DateTime.TryParseExact(cellValue, "dd-MM-yy", null, DateTimeStyles.None, out dateDu))
{
dateDu = DateTime.ParseExact(cellValue, "dd/MM/yyyy HH:mm:ss", CultureInfo.InvariantCulture);
}
}
movimento.DtInicio = dateDu;
break;
case Constants.colDateAu:
if (!DateTime.TryParseExact(cellValue, "dd.MM.yyyy", null, DateTimeStyles.None, out var dateAu))
{
if (!DateTime.TryParseExact(cellValue, "dd-MM-yy", null, DateTimeStyles.None, out dateAu))
{
dateAu = DateTime.ParseExact(cellValue, "dd/MM/yyyy HH:mm:ss", CultureInfo.InvariantCulture);
}
}
movimento.DtFim = dateAu;
break;
case Constants.colDescription:
movimento.Descricao = cellValue;
break;
case Constants.colDateTrans:
break;
case Constants.colDateComptab:
if (!DateTime.TryParseExact(cellValue, "dd.MM.yyyy", null, DateTimeStyles.None, out var dateCont))
{
if (!DateTime.TryParseExact(cellValue, "dd-MM-yy", null, DateTimeStyles.None, out dateCont))
{
dateCont = DateTime.ParseExact(cellValue, "dd/MM/yyyy HH:mm:ss", CultureInfo.InvariantCulture);
}
}
movimento.DtContab = dateCont;
break;
case Constants.colDateValeur:
if (!DateTime.TryParseExact(cellValue, "dd.MM.yyyy", null, DateTimeStyles.None, out var dateVal))
{
if (!DateTime.TryParseExact(cellValue, "dd-MM-yy", null, DateTimeStyles.None, out dateVal))
{
dateVal = DateTime.ParseExact(cellValue, "dd/MM/yyyy HH:mm:ss", CultureInfo.InvariantCulture);
}
}
movimento.DtValor = dateVal;
break;
case Constants.colDescription1:
movimento.Descricao1 = cellValue;
break;
case Constants.colDescription2:
movimento.Descricao2 = cellValue;
break;
case Constants.colDescription3:
movimento.Descricao3 = cellValue;
break;
case Constants.colNoTransaction:
movimento.NumTrans = cellValue;
break;
case Constants.colCoursDevises:
movimento.CursDevis = cellValue;
break;
case Constants.colSousMontant:
if (double.TryParse(cellValue.Replace("'", "").Replace(",", "."), out var subTot))
{
movimento.SubTotal = subTot;
}
break;
case Constants.colDebit:
if (double.TryParse(cellValue.Replace("'", "").Replace(",", "."), out var debit))
{
movimento.Debito = debit;
}
break;
case Constants.colCredit:
if (double.TryParse(cellValue.Replace("'", "").Replace(",", "."), out var credit))
{
movimento.Credito = credit;
}
break;
case Constants.colSolde:
if (double.TryParse(cellValue.Replace("'", "").Replace(",", "."), out var saldo))
{
movimento.Saldo = saldo;
}
break;
default:
throw new NotSupportedException();
}
}