public BankDataSheet TransformXml(XDocument xDocument, string accountName)
{
BankDataSheet sheet = new BankDataSheet();
foreach (XElement operation in xDocument.Descendants("Transaction"))
{
BankEntry entry = new BankEntry()
{
Account = accountName,
Date = this.GetDate(operation),
Amount = this.GetAmount(operation),
Balance = 0,
Currency = "PLN",
Note = this.GetDescription(operation)?.Replace(" ", ""),
FullDetails = this.GetDescription(operation),
PaymentType = this.mapper.Map(this.GetPaymentType(operation)),
};
entry.Payer = this.mapper.Map(this.GetPayer(entry, operation));
entry.Recipient = this.mapper.Map(this.GetRecipient(entry, operation));
sheet.Entries.Add(entry);
}
return(sheet);
}
public async Task Write(BankDataSheet data)
{
SheetsService service = this.GetSheetsService();
await this.ResetSorting(service.Spreadsheets);
List <BankEntry> entries = await this.GetEntriesToAppend(service, data);
if (entries.Any())
{
await this.AddEntries(service.Spreadsheets, entries);
}
int skippedCount = await this.VerifyAnyDataSkipped(service, data);
if (skippedCount > 0)
{
this.logger.Warning($"{skippedCount} entries were missing. Verifying they were re-added correctly.");
skippedCount = await this.VerifyAnyDataSkipped(service, data);
if (skippedCount > 0)
{
throw new InvalidOperationException($"Failed to re-add {skippedCount} entries.");
}
else
{
this.logger.Debug($"All entries re-added correctly.");
}
}
await this.AddCategories(service.Spreadsheets, data.Categories);
}
private void RemoveOldDataEntriesWhereFreshDataWasLoadedAnyway(BankDataSheet freshData, BankDataSheet oldData)
{
if (!freshData.Entries.Any() || !oldData.Entries.Any())
{
return;
}
List <BankEntry> oldEntriesForCurrentAccount = oldData.Entries.Where(x => x.Account == freshData.Entries[0].Account).ToList();
if (!oldEntriesForCurrentAccount.Any())
{
return;
}
List <BankEntry> redundantEntries = oldEntriesForCurrentAccount.Where(oldEntry =>
freshData.Entries.Any(freshDate => freshDate.Date.Date == oldEntry.Date.Date)).ToList();
int oldDataCountBefore = oldData.Entries.Count;
foreach (BankEntry redundantEntry in redundantEntries)
{
oldData.Entries.Remove(redundantEntry);
}
this.logger.Debug($"Removed {redundantEntries.Count} entries from old data list because it exists in the latest payload. " +
$"Old data entries before: {oldDataCountBefore}. After: {oldData.Entries.Count}");
}
public BankDataSheet TransformXml(XDocument xDocument)
{
BankDataSheet sheet = new BankDataSheet();
string account = this.GetAccount(xDocument);
foreach (XElement operation in xDocument.Descendants("operation"))
{
BankEntry entry = new BankEntry()
{
Account = account,
Date = this.GetDate(operation),
Amount = this.GetAmount(operation),
Balance = this.GetBalance(operation),
Currency = this.GetCurrency(operation),
FullDetails = this.GetDescription(operation),
PaymentType = this.mapper.Map(this.GetPaymentType(operation)),
};
entry.Payer = this.mapper.Map(this.GetPayer(entry, operation));
entry.Recipient = this.mapper.Map(this.GetRecipient(entry, operation));
entry.Note = this.mapper.Map(this.GetNote(entry, operation));
MakeSureRecipientNotEmpty(entry);
sheet.Entries.Add(entry);
}
return(sheet);
}
public async Task <BankDataSheet> GetData(DateTime startTime, DateTime endTime)
{
List <BankDataSheet> datasets = new List <BankDataSheet>();
BankDataSheet oldData = this.oldDataManager.GetOldData();
this.RemoveTooOldData(oldData, startTime);
foreach (Account account in this.serviceUserConfig.Accounts)
{
DateTime oldestEntryAdjusted = this.AdjustOldestEntryToDownloadBasedOnOldData(startTime, oldData, account.Number);
BankDataSheet data = await this.GetAccountData(account.Number, oldestEntryAdjusted, endTime);
this.RemoveTooOldData(oldData, startTime);
this.logger.Debug($"IPKO Account [{this.mapper.Map(account.Number)}] - Loaded {data.Entries.Count} entries.");
this.RemoveOldDataEntriesWhereFreshDataWasLoadedAnyway(data, oldData);
datasets.Add(data);
}
foreach (Card card in this.serviceUserConfig.Cards)
{
DateTime oldestEntryAdjusted = this.AdjustOldestEntryToDownloadBasedOnOldData(startTime, oldData, card.Number);
BankDataSheet data = await this.GetCardData(card.Number, oldestEntryAdjusted, endTime);
this.RemoveTooOldData(oldData, startTime);
this.logger.Debug($"IPKO Card [{this.mapper.Map(card.Number)}] - Loaded {data.Entries.Count} entries.");
this.RemoveOldDataEntriesWhereFreshDataWasLoadedAnyway(data, oldData);
datasets.Add(data);
}
datasets.Add(oldData);
return(BankDataSheet.Consolidate(datasets));
}
public void AnalyzeData(BankDataSheet data)
{
foreach (IBankDataAnalyzer bankDataEnricher in this.Enrichers)
{
bankDataEnricher.AssignCategories(data);
}
}
public Task Write(BankDataSheet data)
{
using StreamWriter writer = new StreamWriter(this.targetFilePath);
using CsvWriter csv = new CsvWriter(writer, CultureInfo.InvariantCulture);
csv.WriteRecords(data.Entries);
return(Task.CompletedTask);
}
private void AddCategoryList(BankDataSheet data)
{
foreach (CategoryMap category in this.categories.Concat(this.categories))
{
Category existingCategory = data.Categories.FirstOrDefault(x => x.Name == category.Name);
if (existingCategory != null)
{
foreach (SubcategoryMap subcategory in category.Subcategories)
{
Subcategory existingSubcategory =
existingCategory.Subcategories.FirstOrDefault(x => x.Name == subcategory.Name);
if (existingSubcategory == null)
{
existingCategory.Subcategories.Add(new Subcategory(subcategory.Name));
}
}
}
else
{
Category newCategory = new Category(category.Name);
foreach (SubcategoryMap subcategory in category.Subcategories)
{
newCategory.Subcategories.Add(new Subcategory(subcategory.Name));
}
data.Categories.Add(newCategory);
}
}
}
public async Task <BankDataSheet> DownloadData()
{
try
{
List <BankDataSheet> datasets = new List <BankDataSheet>();
logger.Info("SyncRunner started. Getting data from connected bank services...");
foreach (ServiceConfig configService in config.Services)
{
try
{
await ProcessServices(configService, datasets);
}
catch (Exception ex)
{
logger.Error($"Error while processing service: {configService.Name}", ex);
}
}
logger.Info("Data downloaded.");
var consolidated = BankDataSheet.Consolidate(datasets);
return(consolidated);
}
catch (Exception ex)
{
logger.Error("Process failed!", ex);
throw;
}
}
public BankDataSheet TransformTsv(FileInfo file)
{
BankDataSheet sheet = new BankDataSheet();
string account = this.GetAccount(file);
string[] lines = File.ReadAllLines(file.FullName);
foreach (string line in lines)
{
string[] data = line.Split('\t', StringSplitOptions.RemoveEmptyEntries);
BankEntry entry = new BankEntry()
{
Account = account,
Date = this.GetDate(data),
Amount = this.GetAmount(data),
Balance = 0,
Currency = this.GetCurrency(data),
FullDetails = this.GetDescription(data),
PaymentType = this.mapper.Map("Płatność kartą"),
};
entry.Payer = this.mapper.Map(this.GetPayer(entry));
entry.Recipient = this.mapper.Map(this.GetRecipient(data, entry));
entry.Note = this.mapper.Map(this.GetDescription(data));
sheet.Entries.Add(entry);
}
return(sheet);
}
public void AssignCategories(BankDataSheet data)
{
foreach (BankEntry bankEntry in data.Entries)
{
this.AssignIncomeCategories(bankEntry);
}
this.AddCategoryList(data);
}
public Task Write(BankDataSheet data)
{
data.LoadCategories();
string serialized = JsonConvert.SerializeObject(data);
File.WriteAllText(this.targetFilePath, serialized);
return(Task.CompletedTask);
}
private async Task ProcessServices(ServiceConfig configServiceConfig, List <BankDataSheet> datasets)
{
if (string.Equals(configServiceConfig.Name, "IPKO", StringComparison.OrdinalIgnoreCase))
{
foreach (ServiceUser configServiceUser in configServiceConfig.Users)
{
try
{
IBankDataExporter downloader = new IpkoDataDownloader(configServiceUser, mapper, logger);
BankDataSheet dataset = await downloader.GetData(startTime, endTime);
datasets.Add(dataset);
logger.Debug(
$"IPKO - Loaded total of {dataset.Entries.Count} entries for {configServiceUser.UserName}");
}
catch (LogInException)
{
MessageBox.Show($"Failed to log in {configServiceUser.UserName} to IPKO");
logger.Warning(
$"IPKO - {configServiceUser.UserName}. Could not log in.");
}
catch (Exception ex)
{
logger.Warning(
$"IPKO - Error while processing entries for {configServiceUser.UserName}. {ex}");
}
}
}
if (string.Equals(configServiceConfig.Name, "Citibank", StringComparison.OrdinalIgnoreCase))
{
foreach (ServiceUser configServiceUser in configServiceConfig.Users)
{
try
{
IBankDataExporter downloader = new CitibankDataDownloader(configServiceUser, mapper);
BankDataSheet dataset = await downloader.GetData(startTime, endTime);
datasets.Add(dataset);
logger.Debug(
$"Citibank - Loaded total of {dataset.Entries.Count} entries for {configServiceUser.UserName}");
}
catch (LogInException)
{
logger.Warning(
$"Citibank - {configServiceUser.UserName}. Could not log in.");
}
catch (Exception ex)
{
logger.Warning(
$"Citibank - Error while processing entries for {configServiceUser.UserName}. {ex}");
}
}
}
}
public BankDataSheet GetOldData()
{
List <BankDataSheet> sheets = new List <BankDataSheet>();
if (this.dataRetentionDirectory != null)
{
this.LoadOldDataFromXml(sheets);
}
return(BankDataSheet.Consolidate(sheets));
}
/// <summary>
/// This is not a fully ready downloader - more of a mock
/// It only takes local data and only supports Credit Card XML format
/// </summary>
public async Task <BankDataSheet> GetData(DateTime startTime, DateTime endTime)
{
await Task.Delay(0);
List <BankDataSheet> datasets = new List <BankDataSheet>();
BankDataSheet oldData = this.oldDataManager.GetOldData();
datasets.Add(oldData);
BankDataSheet dataset = BankDataSheet.Consolidate(datasets);
return(dataset);
}
public BankDataSheet GetOldData()
{
List <BankDataSheet> sheets = new List <BankDataSheet>();
if (this.dataRetentionDirectory != null)
{
this.LoadOldDataFromXml(sheets);
this.LoadOldDataFromTsv(sheets);
}
var consolidated = BankDataSheet.Consolidate(sheets);
this.logger.Debug($"Returning {consolidated.Entries.Count} old data entries.");
return(consolidated);
}
public void EnrichData(BankDataSheet data, DateTime startTime, DateTime endTime, Action <BankDataSheet> completionCallback)
{
foreach (IBankDataEnricher bankDataEnricher in this.enrichers)
{
try
{
bankDataEnricher.Enrich(data, startTime, endTime, completionCallback);
}
catch (Exception ex)
{
this.logger.Warning($"{bankDataEnricher.GetType().Name} - Failed to enrich data. {ex.Message}");
throw;
}
}
}
public void AssignCategories(BankDataSheet data)
{
foreach (BankEntry bankEntry in data.Entries)
{
if (bankEntry.Amount < 0)
{
this.AssignExpenseCategories(bankEntry);
}
else
{
this.AssignIncomeCategories(bankEntry);
}
}
this.AddCategoryList(data);
}
private void EnrichProvidedData(BankDataSheet data, List <AllegroDataContainer> allAllegroData,
Action <BankDataSheet> completionCallback)
{
List <BankEntry> allUpdatedEntries = new List <BankEntry>();
RefundEnricher refunds = new RefundEnricher(this.logger);
PurchaseEnricher purchases = new PurchaseEnricher(this.logger);
foreach (BankEntry entry in data.Entries)
{
List <BankEntry> newEntries = ExtractAllegroEntries(entry, refunds, allAllegroData, purchases);
allUpdatedEntries.AddRange(newEntries);
}
data.Entries = allUpdatedEntries;
completionCallback(data);
}
private async Task <int> VerifyAnyDataSkipped(SheetsService sheetsService, BankDataSheet data)
{
ValueRange response = await sheetsService.Spreadsheets.Values.Get(this.spreadsheetId, this.readRange).ExecuteAsync();
List <int> ids = response.Values
.Skip(1)
.Where(x => !string.IsNullOrEmpty(x[0]?.ToString()))
.Select(x => Convert.ToInt32(x[0]))
.ToList();
List <int> duplicates = ids
.GroupBy(i => i)
.Where(g => g.Count() > 1)
.Select(g => g.Key).ToList();
if (duplicates.Any())
{
this.logger.Warning($"Warning: found {duplicates.Count} duplicated IDs. {string.Join(", ", duplicates)}");
IEnumerable <BankEntry> entries = data.Entries.Where(x => duplicates.Contains(x.BankEntryId));
foreach (BankEntry bankEntry in entries)
{
this.logger.Warning($"Duplicated entry: {bankEntry}");
}
}
IEnumerable <BankEntry> entriesToVerify = data.Entries.Where(x => (DateTime.Today - x.Date).TotalDays < 30);
List <BankEntry> missingEntries = new List <BankEntry>();
foreach (BankEntry bankEntry in entriesToVerify)
{
if (ids.All(x => x != bankEntry.BankEntryId))
{
missingEntries.Add(bankEntry);
this.logger.Warning($"Missing entry: {bankEntry}");
}
}
if (missingEntries.Any())
{
await this.AddEntries(sheetsService.Spreadsheets, missingEntries);
}
return(missingEntries.Count);
}
public async Task AnalyzeAndSend(BankDataSheet bankDataSheet)
{
try
{
logger.Info("Data enriched. Proceeding to analysis and categorization...");
analyzersExecutor.AnalyzeData(bankDataSheet);
logger.Info("Data categorized. Proceeding to writing...");
await Write(bankDataSheet);
logger.Info("All done!");
}
catch (Exception ex)
{
logger.Error("Process failed!", ex);
}
}
public void EnrichData(BankDataSheet bankDataSheet, Action allegroDownloadFinishedCallback)
{
try
{
logger.Info("Starting data enriching...");
enricher.LoadEnrichers(config);
enricher.EnrichData(bankDataSheet, startTime, endTime, enrichedData => Task.Run(() =>
{
allegroDownloadFinishedCallback();
return(this.AnalyzeAndSend(enrichedData));
}));
}
catch (Exception ex)
{
logger.Error("Process failed!", ex);
throw;
}
}
private void RemoveTooOldData(BankDataSheet data, DateTime startTime)
{
List <BankEntry> redundantEntries = new List <BankEntry>();
foreach (BankEntry entry in data.Entries)
{
if (entry.Date.Date < startTime.Date)
{
redundantEntries.Add(entry);
}
}
int oldDataCountBefore = data.Entries.Count;
foreach (BankEntry redundantEntry in redundantEntries)
{
data.Entries.Remove(redundantEntry);
}
this.logger.Debug($"Removed {redundantEntries.Count} entries from old data list because it's older than required. " +
$"Before: {oldDataCountBefore}. After: {data.Entries.Count}");
}
private async Task Write(BankDataSheet ipkoData)
{
List <IBankDataWriter> writers = new List <IBankDataWriter>();
string path = GetOutputPath();
writers.Add(new ExcelBankDataWriter(path + ".xlsx"));
writers.Add(new JsonBankDataWriter(path + ".json"));
writers.Add(new GoogleSheetsBankDataWriter(googleWriterConfigFile, logger));
foreach (IBankDataWriter writer in writers)
{
try
{
await writer.Write(ipkoData);
logger.Info($"Data written with with {writer.GetType().Name}");
}
catch (Exception ex)
{
logger.Error($"Error while writing with {writer.GetType().Name}.", ex);
}
}
}
private async Task <List <BankEntry> > GetEntriesToAppend(SheetsService sheetsService, BankDataSheet data)
{
SpreadsheetsResource.ValuesResource.GetRequest request = sheetsService.Spreadsheets.Values.Get(this.spreadsheetId, this.readRange);
ValueRange response = await request.ExecuteAsync();
IList <object> firstDataRow = response.Values.Skip(1).FirstOrDefault(x => x[0] != null && !string.IsNullOrEmpty(x[0].ToString())); //skip header
if (firstDataRow == null)
{
return(data.Entries);
}
else
{
int latestId = Convert.ToInt32(firstDataRow[0]);
DateTime latestDate = Convert.ToDateTime(firstDataRow[2]);
List <(int entryId, DateTime date, IList <object> row)> entriesInTheSheet = new List <(int entryId, DateTime date, IList <object> row)>();
List <IList <object> > dataList = response.Values.Skip(1).ToList();
this.logger.Debug($"Total entries loaded: {dataList.Count}. Latest entry and date: {latestId} : {latestDate.Date}.");
this.logger.Debug($"Looking for entries to be added to the spreadsheet...");
foreach (IList <object> row in dataList)
{
string entryIdString = row[this.entryIdRow.Value]?.ToString();
if (!string.IsNullOrEmpty(entryIdString))
{
DateTime dateTime = Convert.ToDateTime(row[this.dateRow.Value]?.ToString());
try
{
int entryId = Convert.ToInt32(entryIdString);
entriesInTheSheet.Add((entryId, dateTime, row));
}
catch (Exception e)
{
this.logger.Warning($"Cannot convert: [{entryIdString}] to entry ID. {e}");
throw;
}
}
}
List <BankEntry> missingEntries = data.Entries.Where(newEntry =>
entriesInTheSheet.All(sheetEntry => sheetEntry.Item1 != newEntry.BankEntryId)).ToList();
missingEntries = this.FilterMissingEntries(missingEntries, entriesInTheSheet);
foreach (BankEntry bankEntry in missingEntries)
{
this.logger.Debug($"Entry to be added: {bankEntry}");
}
this.logger.Debug($"Total entries to be added: {missingEntries.Count}");
return(missingEntries);
}
}
public Task Write(BankDataSheet data)
{
Worksheet sheet = new Worksheet("Data");
sheet.FreezeTopRow();
sheet.Cells[0, 0] = "Entry ID";
sheet.Cells[0, 1] = "Account";
sheet.ColumnWidths[1] = 12;
sheet.Cells[0, 2] = "Date";
sheet.ColumnWidths[2] = 13;
sheet.Cells[0, 3] = "Currency";
sheet.Cells[0, 4] = "Amount";
sheet.Cells[0, 5] = "Balance";
sheet.Cells[0, 6] = "PaymentType";
sheet.ColumnWidths[6] = 25;
sheet.Cells[0, 7] = "Recipient";
sheet.ColumnWidths[7] = 35;
sheet.Cells[0, 8] = "Payer";
sheet.ColumnWidths[8] = 20;
sheet.Cells[0, 9] = "Note";
sheet.ColumnWidths[9] = 55;
sheet.Cells[0, 10] = "Category";
sheet.ColumnWidths[10] = 25;
sheet.Cells[0, 11] = "Subcategory";
sheet.ColumnWidths[11] = 25;
sheet.Cells[0, 12] = "Tags";
sheet.Cells[0, 13] = "Full Details";
sheet.ColumnWidths[13] = 70;
for (int i = 0; i < sheet.Cells.ColumnCount; i++)
{
sheet.Cells[0, i].Bold = true;
sheet.Cells[0, i].Fill.BackgroundColor = Color.Gray;
}
for (int rowIndex = 0; rowIndex < data.Entries.Count; rowIndex++)
{
BankEntry bankEntry = data.Entries[rowIndex];
sheet.Cells[rowIndex + 1, 0] = bankEntry.BankEntryId;
if (rowIndex + 1 % 2 == 0)
{
for (int columnIndex = 0; columnIndex < sheet.Cells.ColumnCount; columnIndex++)
{
if (columnIndex == 10 || columnIndex == 11)
{
continue;
}
sheet.Cells[rowIndex + 1, columnIndex].Fill.BackgroundColor = Color.WhiteSmoke;
}
}
sheet.Cells[rowIndex + 1, 1] = bankEntry.Account;
sheet.Cells[rowIndex + 1, 2] = new Cell(CellType.Date, bankEntry.Date, "dd/MM/yyyy");
sheet.Cells[rowIndex + 1, 3] = bankEntry.Currency;
sheet.Cells[rowIndex + 1, 4] = bankEntry.Amount;
if (bankEntry.Amount < 0)
{
sheet.Cells[rowIndex + 1, 4].TextColor = Color.Red;
sheet.Cells[rowIndex + 1, 4].Bold = true;
}
else
{
sheet.Cells[rowIndex + 1, 4].TextColor = Color.Green;
sheet.Cells[rowIndex + 1, 4].Bold = true;
}
sheet.Cells[rowIndex + 1, 5] = bankEntry.Balance;
sheet.Cells[rowIndex + 1, 6] = bankEntry.PaymentType;
sheet.Cells[rowIndex + 1, 7] = bankEntry.Recipient;
sheet.Cells[rowIndex + 1, 7].Border = CellBorder.Right | CellBorder.Left;
sheet.Cells[rowIndex + 1, 7].Bold = true;
sheet.Cells[rowIndex + 1, 8] = bankEntry.Payer;
sheet.Cells[rowIndex + 1, 9] = bankEntry.Note;
sheet.Cells[rowIndex + 1, 10] = bankEntry.Category;
sheet.Cells[rowIndex + 1, 10].Fill.BackgroundColor = Color.MediumSeaGreen;
sheet.Cells[rowIndex + 1, 11] = bankEntry.Subcategory;
sheet.Cells[rowIndex + 1, 11].Fill.BackgroundColor = Color.DarkSeaGreen;
sheet.Cells[rowIndex + 1, 12] = string.Join(";", bankEntry.Tags);
sheet.Cells[rowIndex + 1, 13] = bankEntry.FullDetails;
}
Workbook workbook = new Workbook();
workbook.Add(sheet);
workbook.Save(this.targetFilePath);
return(Task.CompletedTask);
}
public async Task Enrich(BankDataSheet data, DateTime startTime, DateTime endTime,
Action <BankDataSheet> completionCallback)
{
await this.dataLoader.LoadAllData(startTime, list => EnrichProvidedData(data, list, completionCallback));
}
/// <summary>
/// Don't download old data if older or equal data is already stored
/// </summary>
/// <param name="oldestEntryToBeFetched"></param>
/// <param name="oldData"></param>
/// <param name="accountNumber"></param>
/// <returns></returns>
private DateTime AdjustOldestEntryToDownloadBasedOnOldData(DateTime oldestEntryToBeFetched, BankDataSheet oldData, string accountNumber)
{
if (oldData != null)
{
DateTime oldestAvailable = oldData.GetOldestEntryFor(this.mapper.Map(accountNumber));
if (oldestAvailable != default && oldestAvailable <= oldestEntryToBeFetched)
{
oldestEntryToBeFetched = oldData.GetNewestEntryFor(this.mapper.Map(accountNumber));
}
}
return(oldestEntryToBeFetched);
}
