public static async Task <List <RootRecord> > ReadSIEFiles(IEnumerable <int> years)
{
var sieDir = Path.Join(GetCurrentOrSolutionDirectory(), "sbc_scrape", "scraped", "SIE");
var files = years.Select(year => $"output_{year}.se");
return(await SBCExtensions.ReadSIEFiles(files.Select(file => Path.Combine(sieDir, file))));
}
public static async Task <List <RootRecord> > ReadSIEFiles(IEnumerable <string>?files = null)
{
var sieDir = Path.Join(GetCurrentOrSolutionDirectory(), "sbc_scrape", "scraped", "SIE");
if (files == null)
{
files = new DirectoryInfo(sieDir).GetFiles("*.se").Select(o => o.Name);
}
return(await SBCExtensions.ReadSIEFiles(files.Select(file => Path.Combine(sieDir, file))));
}
public async Task SbcInvoiceIntegrity()
{
var years = new[] { 2019, 2020 };
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 vouchers = sie.SelectMany(o => o.Children).OfType <VoucherRecord>().ToList();
{
var slrsWithout24400 = vouchers.Where(o => o.VoucherType == VoucherType.SLR || o.VoucherType == VoucherType.TaxAndExpense).Where(o => o.Transactions.Any(t => t.AccountId == 24400) == false);
Assert.IsFalse(slrsWithout24400.Any());
}
{
// SLR/LB shouldn't have different companynames on transactions (only if shortened)
// LR however has one for 24400 (person) and purpose on the other accounts
foreach (var item in vouchers.Where(o => o.VoucherType == VoucherType.SLR || o.VoucherType == VoucherType.LB))
{
var names = item.Transactions.Select(o => o.CompanyName).Distinct().OrderBy(o => o.Length).ToList();
for (int i = 1; i < names.Count(); i++)
{
Assert.IsTrue(names[i].StartsWith(names[i - 1]));
}
}
}
var lbs = vouchers.Where(o => o.VoucherType == VoucherType.LB).ToList();
{
// Integrity
var lbNo24400 = lbs.Where(o => o.Transactions.Any(t => t.AccountId == 24400) == false);
Assert.IsFalse(lbNo24400.Any());
}
var dir = Tools.GetOutputFolder("sbc_html");
var sbcInvoices = new sbc_scrape.SBC.InvoiceSource().ReadAll(dir).Where(o => years.Contains(o.RegisteredDate.Year));
var byVerNum = sbcInvoices.GroupBy(o => o.IdSLR).ToDictionary(g => g.Key, g => g.ToList());
var differentLinksSameVerNum = byVerNum.Where(o => o.Value.Select(p => p.InvoiceLink).Distinct().Count() > 2);
Assert.IsFalse(differentLinksSameVerNum.Any());
var invoiceKeys = byVerNum.Select(o => o.Key).ToList();
var slrs = vouchers.Where(o => o.VoucherType == VoucherType.SLR).ToList();
var slrKeys = slrs.Select(o => o.Id).ToList();
// All Invoices should exist as SLRs:
Assert.IsFalse(invoiceKeys.Except(slrKeys).Any());
// Note: Invoices correspondeing to SLRs are created only when payed, so no need to check the other way around
}
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"));
}
public async Task TestMethod1()
{
Func <int, bool> accountFilter = accountId => accountId.ToString().StartsWith("45");
//Load SBC invoices
var fromSBC = (await Tools.LoadSBCInvoices(accountFilter)).Select(o => new SBCVariant {
AccountId = o.AccountId,
Amount = o.Amount,
CompanyName = o.Supplier,
DateRegistered = NodaTime.LocalDate.FromDateTime(o.RegisteredDate),
DateFinalized = o.PaymentDate.HasValue ? NodaTime.LocalDate.FromDateTime(o.PaymentDate.Value) : (NodaTime.LocalDate?)null,
Source = o,
}).ToList();
//Load SIE vouchers
List <TransactionMatched> fromSIE;
{
var files = Enumerable.Range(2010, 10).Select(o => $"output_{o}.se");
var sieDir = Tools.GetOutputFolder("SIE");
var roots = await SBCExtensions.ReadSIEFiles(files.Select(file => Path.Combine(sieDir, file)));
var allVouchers = roots.SelectMany(o => o.Children).OfType <VoucherRecord>();
var matchResult = MatchSLRResult.MatchSLRVouchers(allVouchers, VoucherRecord.DefaultIgnoreVoucherTypes);
fromSIE = TransactionMatched.FromVoucherMatches(matchResult, TransactionMatched.RequiredAccountIds).Where(o => accountFilter(o.AccountId)).ToList();
}
var sbcByName = fromSBC.GroupBy(o => o.CompanyName).ToDictionary(o => o.Key, o => o.ToList());
var sieByName = fromSIE.GroupBy(o => o.CompanyName).ToDictionary(o => o.Key, o => o.ToList());
//Create name lookup (can be truncated in one source but not the other):
Dictionary <string, string> nameLookup;
{
var(Intersection, OnlyInA, OnlyInB) = IntersectInfo(sbcByName.Keys, sieByName.Keys);
nameLookup = Intersection.ToDictionary(o => o, o => o);
AddLookups(OnlyInA, OnlyInB);
AddLookups(OnlyInB, OnlyInA);
void AddLookups(List <string> enumA, List <string> enumB)
{
for (int i = enumA.Count - 1; i >= 0; i--)
{
var itemA = enumA[i];
var itemB = enumB.FirstOrDefault(o => o.StartsWith(itemA));
if (itemB != null)
{
enumB.Remove(itemB);
enumA.Remove(itemA);
nameLookup.Add(itemB, itemA);
nameLookup.Add(itemA, itemB);
}
}
}
//Non-matched: intersectInfo.OnlyInA and intersectInfo.OnlyInB
}
var matches = new List <(TransactionMatched, SBCVariant)>();
foreach (var(sieName, sieList) in sieByName)
{
if (nameLookup.ContainsKey(sieName))
{
var inSbc = sbcByName[nameLookup[sieName]].GroupBy(o => o.Amount).ToDictionary(o => o.Key, o => o.ToList());
//Multiple passes of the following until no more matches
while (true)
{
var newMatches = new List <(TransactionMatched, SBCVariant)>();
for (int sieIndex = sieList.Count - 1; sieIndex >= 0; sieIndex--)
{
var item = sieList[sieIndex];
if (inSbc.TryGetValue(item.Amount, out var sbcSameAmount))
{
var sbcSameAmountAccount = sbcSameAmount.Where(o => o.AccountId == item.AccountId);
//Find those with same register date (could be many)
//If multiple or none, take those with closest payment date.
//Remove match from inSbc so it can't be matched again
var found = new List <SBCVariant>();
if (item.DateRegistered is NodaTime.LocalDate dateRegistered)
{
found = sbcSameAmountAccount.Where(o => (dateRegistered - item.DateRegistered.Value).Days <= 1).ToList();
}
else
{
found = sbcSameAmountAccount.Where(o => (o.DateFinalized.HasValue && item.DateFinalized.HasValue) &&
(o.DateFinalized.Value - item.DateFinalized.Value).Days <= 1).ToList();
}
if (found.Count > 1)
{
var orderByDateDiff = found
.Where(o => (o.DateFinalized.HasValue && item.DateFinalized.HasValue))
.Select(o => new
{
#pragma warning disable CS8629 // Nullable value type may be null.
Diff = Math.Abs((o.DateFinalized.Value - item.DateFinalized.Value).Days),
#pragma warning restore CS8629 // Nullable value type may be null.
Object = o
})
.OrderBy(o => o.Diff);
var minDiff = orderByDateDiff.First().Diff;
if (orderByDateDiff.Count(o => o.Diff == minDiff) == 1)
{
found = orderByDateDiff.Take(1).Select(o => o.Object).ToList();
}
}
if (found.Count == 1)
{
newMatches.Add((item, found.Single()));
sieList.RemoveAt(sieIndex);
sbcSameAmount.Remove(found.First());
}
}
}
if (!newMatches.Any())
{
break;
}
matches.AddRange(newMatches);
}
}
}
var nonmatchedSBC = sbcByName.Values.SelectMany(o => o).Except(matches.Select(o => o.Item2));
//Remove cancelled-out pairs (same everything but opposite amount):
var cancelling = nonmatchedSBC.GroupBy(o => $"{o.CompanyName} {Math.Abs(o.Amount)} {o.DateRegistered?.ToSimpleDateString()} {o.DateFinalized?.ToSimpleDateString()}")
.Where(o => o.Count() == 2 && o.Sum(o => o.Amount) == 0).SelectMany(o => o);
nonmatchedSBC = nonmatchedSBC.Except(cancelling);
var nonmatched = nonmatchedSBC.Concat(sieByName.Values.SelectMany(o => o).Except(matches.Select(o => o.Item1)));
var all = matches.Select(o => o.Item1).Concat(nonmatched);
var sss = string.Join("\n",
all
.OrderBy(o => o.CompanyName).ThenBy(o => o.DateRegistered)
.Select(o => $"{o.CompanyName}\t{o.Amount}\t{o.AccountId}\t{o.DateRegistered?.ToSimpleDateString()}\t{o.DateFinalized?.ToSimpleDateString()}\t{(nonmatched.Contains(o) ? "X" : "")}\t{((o is SBCVariant) ? "SBC" : "")}")
);
}
