Exemple #1
0
        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());
        }
Exemple #2
0
        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);
Exemple #3
0
        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);
        }
Exemple #4
0
        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"));
        }