public async Task <List <sbc_scrape.Joining.ExportRow> > Evaluate()
{
var startYear = 2017;
var years = Enumerable.Range(startYear, DateTime.Now.Year - startYear + 1);
//var invoices = await main.MediusFlow.LoadInvoiceSummaries(fl => years.Contains(fl.RegisteredDate.Year));
var invoices = await main.MediusFlow.LoadInvoices(fl => years.Contains(fl.RegisteredDate.Year));
var sieFiles = new System.IO.DirectoryInfo(settings.StorageFolderSIE)
.GetFiles($"output_*.se")
.Where(o => years.Any(p => o.Name.Contains(p.ToString())))
.Select(o => o.FullName);
var sie = await SIE.SBCExtensions.ReadSIEFiles(sieFiles);
var sbcInvoices = new InvoiceSource().ReadAll(settings.StorageFolderSbcHtml).Where(o => years.Contains(o.RegisteredDate.Year)).ToList();
var sbcReceipts = new ReceiptsSource().ReadAll(settings.StorageFolderSbcHtml).Where(o => years.Contains(o.Date.Year)).ToList();
var sbcTransactions = new BankTransactionSource().ReadAll(settings.StorageFolderSbcHtml).Where(o => years.Contains(o.AccountingDate.Year)).ToList();
//return sbc_scrape.Joining.JoinSbcSieMediusFlow.MatchMediusFlowWithSIE(years, invoices, sie.ToList(), sbcInvoices);
var joined = sbc_scrape.Joining.JoinSbcSieMediusFlow.Testing(years, invoices, sie.ToList(), sbcInvoices, sbcReceipts, sbcTransactions);
//var accountNames = sie.First().Children.OfType<AccountRecord>().ToDictionary(o => o.AccountId, o => o.AccountName);
var accountNamesMF = invoices.Select(o => InvoiceSummary.Summarize(o)).GroupBy(o => o.AccountId).ToDictionary(o => o.Key, o => o.First().AccountName);
var accountNames = sbcInvoices.GroupBy(o => o.AccountId).ToDictionary(o => o.Key, o => o.First().AccountName);
foreach (var accountId in accountNames.Keys)
{
if (accountNamesMF.TryGetValue(accountId, out var val))
{
accountNames[accountId] = val;
}
}
return(joined.Select(o => o.ToExportRow(accountNames)).ToList());
}
public async Task <List <JoinedRow> > EvaluateOld()
{
Console.WriteLine("0");
var invoices = (await main.LoadInvoices(includeOCRd: false, (i, l) => {
if (l < 100 || i % 20 == 0)
{
Console.RewriteLine($"{i}/{l}");
}
})).Where(o => o.DueDate.HasValue).ToList();
//var invoices = (await main.LoadInvoices(false)).Where(o => o.DueDate.HasValue).ToList();
var receipts = new ReceiptsSource().ReadAll(settings.StorageFolderSbcHtml);
var transactions = new BankTransactionSource().ReadAll(settings.StorageFolderSbcHtml);
//TODO: parms date from
var dateRange = (Min : new DateTime(2016, 1, 1), Max : DateTime.Today);
bool inRange(DateTime d) => d >= dateRange.Min && d.Date <= dateRange.Max;
invoices = invoices.Where(o => inRange(o.DueDate.Value)).ToList();
receipts = receipts.Where(o => inRange(o.Date)).ToList();
transactions = transactions?.Where(o => inRange(o.AccountingDate)).ToList();
var trxOrderWithinDate = new Dictionary <DateTime, List <decimal> >();
foreach (var o in transactions)
{
if (!trxOrderWithinDate.TryGetValue(o.CurrencyDate, out var list))
{
list = new List <decimal>();
trxOrderWithinDate.Add(o.CurrencyDate, list);
}
list.Add(o.TotalAccountAmount);
}
var joined = new sbc_scrape.DataJoiner().Join(invoices, receipts, transactions, out var unmatched);
var sortedUnmatched = unmatched.invoicesAndReceipts.Select(o => new {
o.Date,
Output = $"{o.Amount}\t{o.Supplier}\t{o.ContainsCode}"
})
.Concat(unmatched.transactions.Where(o => o.Reference != "6091 BGINB").Select(o => new {
Date = o.AccountingDate,
Output = $"{o.Amount}\t{o.Text}\t{o.Reference}"
}))
.OrderByDescending(o => o.Date)
.Select(o => $"{o.Date.ToShortDateString()}\t{o.Output}").ToList();
var dbg = string.Join("\n", sortedUnmatched);
private async Task <(int[], List <InvoiceFull>, List <RootRecord>, List <Invoice>, List <Receipt>, List <BankTransaction>)> Prepare(int[] years)
{
var store = new FileSystemKVStore(Tools.GetOutputFolder());
var invoiceStore = new SBCScan.InvoiceStore(store);
var files = await invoiceStore.GetKeysParsed();
var invoices = (await Task.WhenAll(files.Where(o => years.Contains(o.RegisteredDate.Year)).Select(async o => await invoiceStore.Get(o)).ToList())).ToList();
var sieFolder = Tools.GetOutputFolder("SIE");
var sieFiles = new DirectoryInfo(sieFolder).GetFiles($"output_*.se").Select(o => o.Name).Where(o => years.Any(p => o.Contains(p.ToString())));
var sie = await SBCExtensions.ReadSIEFiles(sieFiles.Select(file => Tools.GetOutputFolder("SIE", file)));
var dir = Tools.GetOutputFolder("sbc_html");
var sbcInvoices = new InvoiceSource().ReadAll(dir).Where(o => years.Contains(o.RegisteredDate.Year)).ToList();
var sbcReceipts = new ReceiptsSource().ReadAll(dir).Where(o => years.Contains(o.Date.Year)).ToList();
var sbcTransactions = new BankTransactionSource().ReadAll(dir).Where(o => years.Contains(o.CurrencyDate.Year)).ToList();
return(years, invoices, sie.ToList(), sbcInvoices, sbcReceipts, sbcTransactions);
}
public async Task MatchSbcHtmlAndSIE()
{
var year = 2020;
var roots = await SBCExtensions.ReadSIEFiles(new[] { "output_20201209.se" }.Select(file => Tools.GetOutputFolder("SIE", file)), SBCExtensions.ProcessCompanyNameMode.SeparateIdAndName);
var allVouchers = roots.SelectMany(o => o.Children).OfType <VoucherRecord>();
var resultRecords = roots.SelectMany(o => o.Children).OfType <ResultRecord>();
var accountChanged = allVouchers.Where(o => o.Transactions.Where(
t => t.AccountId >= 40000 && t.AccountId < 70000 && t.Amount != 0).GroupBy(t => Math.Sign(t.Amount)).Count() > 1).ToList();
// From here, ignore AV
allVouchers = allVouchers.Where(o => o.VoucherType != VoucherType.AV);
//var byType = allVouchers.GroupBy(v => v.VoucherTypeCode).ToDictionary(g => g.Key, g => g.ToList());
var htmlFolder = Tools.GetOutputFolder("sbc_html");
var transactions = new BankTransactionSource().ReadAll(htmlFolder).Where(r => r.AccountingDate.Year == year).ToList();
bool IsIncomeAccount(int accountId) => accountId >= 30110 && accountId <= 32910;
// BGINB
// AG
// IT-A06 - 16899 OBS Konto?
// KI - 16410 "Skattefordran"?
var bankgiroSum = transactions.Where(o => o.Amount > 0 && o.Text.Any()).Sum(o => o.Amount); // o.Reference.EndsWith("BGINB") || o.Reference.EndsWith(" AG"))
var incomes = allVouchers.SelectMany(o => o.Transactions.Where(t => IsIncomeAccount(t.AccountId)));
var incomesSum = incomes.Sum(o => o.Amount);
var resIncomedSum = resultRecords.Where(o => IsIncomeAccount(o.AccountId)).Sum(o => o.Amount);
//var soso = transactions.Where(o => o.Amount > 0).ToList();
var unusedVouchers = allVouchers.ToList();
var result = MatchTransactions(transactions, unusedVouchers, (0, 0));
var matched = result.matched;
transactions = result.unmatched;
//var unmatchedTxStrings = matchTx.Where(o => !o.Item2.Any()).Select(o => o.Item1.ToString());
//transactions = transactions.Where(tx => unmatchedTxStrings.Contains(tx.ToString())).ToList();
result = MatchTransactions(transactions, unusedVouchers, (-3, 3));
matched.AddRange(result.matched);
transactions = result.unmatched;
var withDate = transactions.Select(o => (LocalDate.FromDateTime(o.AccountingDate), $"TX\t{o}"))
.Concat(unusedVouchers.Select(o => (o.Date, FullVoucher(o))))
.OrderBy(o => o.Item1).ToList();
string FullVoucher(VoucherRecord v)
{
return($"{v}\n\t\t" + string.Join("\n\t\t", v.Transactions.Select(o => $"{o.Amount} {o.CompanyName}")));
}
var dbg = string.Join("\n", withDate.Select(o => $"{o.Item1.ToSimpleDateString()}\t{o.Item2}"));
var invoices = new InvoiceSource().ReadAll(htmlFolder).Where(r => r.RegisteredDate.Year == year).ToList();
var receipts = new ReceiptsSource().ReadAll(htmlFolder).Where(r => r.Date.Year == year).ToList();
var recInv = receipts.Select(receipt => {
return(new { Receipt = receipt, Invoices = invoices.Where(o => o.Amount == receipt.Amount && o.PaymentDate == receipt.Date).ToList() });
}).ToList();
var triedMatchedInvoices = invoices.Select(invoice => {
var matchedVouchers = unusedVouchers
.Where(o => o.Series == invoice.VerSeries)
.Where(o => o.SerialNumber == invoice.VerNum)
//.Where(o => o.GetTransactionsMaxAmount() == invoice.Amount && o.Date == NodaTime.LocalDate.FromDateTime(invoice.RegisteredDate))
//.Where(o => o.CompanyName == invoice.Supplier)
.ToList();
if (matchedVouchers.Count() == 1)
{
RemoveVouchers(matchedVouchers, unusedVouchers);
}
return(new { Invoice = invoice, Vouchers = matchedVouchers });
}).ToList();
var matchedInvoices = triedMatchedInvoices.Where(o => o.Vouchers.Count == 1).Select(o => new { o.Invoice, Voucher = o.Vouchers.Single() }).ToList();
var mismatch = triedMatchedInvoices.Where(o => o.Vouchers.Count != 1).ToList();
var matchedReceipts = MatchReceipts(receipts, unusedVouchers);
var doubleUse = matchedReceipts.SelectMany(o => o.Item2).GroupBy(o => o.Id).Where(o => o.Count() > 1).ToList();
//var sieDir = Path.Join(Tools.GetCurrentOrSolutionDirectory(), "sbc_scrape", "scraped", "SIE");
//var tmp = File.ReadAllText(Path.Combine(sieDir, "accountsexport.txt"));
}
