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" : "")}") ); }