public static List <Joined> Testing(IEnumerable <int> years, List <InvoiceFull> invoices, List <RootRecord> sie, List <SBC.Invoice> sbcInvoices, List <SBC.Receipt> receipts, List <SBC.BankTransaction> transactions) { var(invoiceSummaries, sieVouchers) = PrepareFiltered(years, invoices, sie); // 1. Join SLR with SBC Invoices - the SBC always have an SLR Id // 2. Join LB with above result (they have the same Date as SBC PaymentDate and same SupplierId). // 3. Join MediusFlow with above result, using Date, Amount and (normalized) Company // 4. // 1. Join SLR -> SBC var mainTransactionAccount = 24400; var slrs = sieVouchers.Where(voucher => voucher.VoucherType == VoucherType.SLR || voucher.VoucherType == VoucherType.TaxAndExpense); // Note: VoucherType.TaxAndExpense are always without other references { var missingMainAccount = slrs.Where(o => !o.Transactions.Any(p => p.AccountId == mainTransactionAccount)); if (missingMainAccount.Any()) { throw new Exception($"Algorithm requires all SLR/LRs having {mainTransactionAccount}"); } } var tmp = slrs.Select(o => new { Item = o, Names = o.Transactions.Select(p => $"{p.CompanyId}{p.CompanyName}").Distinct().ToList() }) .Where(o => o.Names.Count > 1).ToList(); //var aaa = CreateSet(new[] { 1, 2, 3, 4, 5, 5 }, new[] { 0, 3, 4, 4, 5, 6, 7, 7 }, k => k, k => k); //var set = CreateSet(new[] { 1, 2, 3, 4, 5, 5 }, new[] { 0, 3, 4, 4, 5, 6, 7, 7 }, k => k, k => k); var slrToSbc = CreateSet(slrs, sbcInvoices, slr => slr.Id, sbc => sbc.IdSLR); if (slrToSbc.ManyA_To_ManyBs.Any(o => o.Item1.Count > 1)) { throw new Exception("SLR/SBC Multimatch"); } if (slrToSbc.OnlyInB.Any()) { throw new Exception("SBC without SLR"); } var result = slrToSbc.OneA_To_NoneOrAnyBs .Select(o => new Joined { SLR = o.A, SBC = o.Bs }).ToList(); // 2. Join LB -> above result var lbs = sieVouchers.Where(voucher => voucher.VoucherType == VoucherType.LB).ToList(); var lbToResult = CreateSet(lbs, result.Where(o => o.SBC.Any()), lb => new InvoiceKey { Amount = lb.GetAmountTransactionAccount(mainTransactionAccount), Company = lb.Transactions.First().CompanyId, Date = lb.Date }, sbc => new InvoiceKey { Amount = sbc.SBC.First().Amount, Company = sbc.SBC.First().LevNr, Date = sbc.SBC.FirstOrDefault(o => o.PaymentDate != null)?.PaymentDate.Value.ToLocalDate() ?? LocalDate.MaxIsoValue }); if (lbToResult.OneA_To_ManyBs.Any()) { throw new Exception("SBC/LB Multimatch"); } lbToResult.OneA_To_OneB.ForEach(o => o.B.LB = o.A); lbToResult.OnlyInA.ForEach(o => result.Add(new Joined { LB = o })); if (lbToResult.ManyA_To_ManyBs.Any()) { if (lbToResult.ManyA_To_ManyBs.Any(o => o.As.Count != o.Bs.Count)) { throw new Exception("Unsalvageble ManyA_To_ManyBs"); } foreach (var item in lbToResult.ManyA_To_ManyBs) { for (int i = 0; i < item.As.Count; i++) { item.Bs[i].LB = item.As[i]; } } } var replacer = new NameNormalizer(); // Join MediusFlow -> above result var mfToResult = CreateSet(invoiceSummaries, result.Where(o => o.SLR != null), inv => new InvoiceKey { Date = inv.InvoiceDate.ToLocalDate().Value, Amount = inv.GrossAmount, Company = replacer.Normalize(inv.Supplier) }, r => new InvoiceKey { Date = r.SLR.Date, Amount = -r.SLR.GetAmountTransactionAccount(mainTransactionAccount), Company = replacer.Normalize(r.SLR.CompanyName) }); //r.SLR.GetTransactionsMaxAmount() if (mfToResult.OneA_To_ManyBs.Any()) { throw new Exception("OneA_To_ManyBs"); } mfToResult.OneA_To_OneB.ForEach(o => o.B.MF = o.A); mfToResult.OnlyInA.ForEach(o => result.Add(new Joined { MF = o })); if (mfToResult.ManyA_To_ManyBs.Any()) { if (mfToResult.ManyA_To_ManyBs.Any(o => o.As.Count != o.Bs.Count)) { throw new Exception("Unsalvageble ManyA_To_ManyBs"); } // separate into pairs foreach (var item in mfToResult.ManyA_To_ManyBs) { for (int i = 0; i < item.As.Count; i++) { item.Bs[i].MF = item.As[i]; } } } // Try to pair unmatched LB/SLR (no SBCInvoice was found that could provide link between them) // TODO: If there's a MediusFlow, we could use that to get Payment/Invoice dates which should match LB/SLR records // Ignore before/after plausible period var earliestSLR = result.Where(o => o.SLR != null).Min(o => o.SortDate); var latestLB = result.Where(o => o.LB != null).Max(o => o.SortDate); var fuzzyMatchSelection = result.Where(o => o.SortDate > earliestSLR && o.SortDate < latestLB).ToList(); var noSLR = fuzzyMatchSelection.Where(o => o.SLR == null && o.LB != null).ToList(); var noLB = fuzzyMatchSelection.Where(o => o.SLR != null && o.LB == null).ToList(); var byAmountAndName = noSLR.Concat(noLB) .GroupBy(o => $"{replacer.Normalize(o.CompanyName)}/{o.Amount}") .Where(o => o.Count() >= 2) .ToDictionary(o => o.Key, o => o.ToList()); foreach (var kv in byAmountAndName) { var withLb = kv.Value.Where(o => o.LB != null).ToList(); var withSlr = kv.Value.Where(o => o.SLR != null).ToList(); while (withLb.Any() && withSlr.Any()) { var diffs = withLb.Select(o => new { WithLB = o, Diffs = withSlr.Select(p => new { WithSLR = p, Diff = Math.Abs(Period.Between( p.PaymentDate ?? p.InvoiceDate.Value.PlusDays(30), o.PaymentDate.Value, PeriodUnits.Days) .Days) }).OrderBy(p => p.Diff).ToList() }).ToList(); var withMinDiff = diffs.OrderBy(o => o.Diffs.Select(p => p.Diff).Min()).First(); var other = withMinDiff.Diffs.First().WithSLR; withMinDiff.WithLB.Merge(other); other.MakeEmpty(); withLb.Remove(withMinDiff.WithLB); withSlr.Remove(other); } } result = result.Where(o => !o.IsEmpty()).ToList(); { // Join with Receipts - very strange data from SBC though, same transaction may have MANY rows with different names?! var withLB = result.Where(o => o.LB != null).ToList(); var keyToLB = withLB.GroupBy(o => new { CompanyId = o.LB.Transactions.First().CompanyId, Amount = o.LB.GetTransactionsMaxAmount() }) .ToDictionary(o => o.Key, o => o.ToList()); var groupedReceipts = receipts.GroupBy(o => new { Amount = o.Amount, Date = o.Date, SupplierId = o.SupplierId }).ToDictionary(o => o.Key, o => o.ToList()); foreach (var groupedItem in groupedReceipts) { // SBC Admins often have strange chars in them, also numbers - probably not the name we want var rxBad1 = new Regex(@"[\/!\""]"); var rxBad2 = new Regex(@"\d"); var sorted = groupedItem.Value.OrderBy(o => (rxBad1.IsMatch(o.Supplier) ? 2 : 0) + (rxBad2.IsMatch(o.Supplier) ? 1 : 0)).ToList(); var item = sorted.First(); // seems LB CompanyId starts with some variation of BRF id (e.g. 0xxxx02 where xxxx is SIE CompanyIdRecord) var found = keyToLB.Where(o => o.Key.Amount == item.Amount && o.Key.CompanyId.EndsWith(item.SupplierId)) .SelectMany(o => o.Value) .Where(o => !o.IsEmpty()) // we might have rendered it empty (below) .ToList(); if (found.Count() > 1) { found = found.Select(o => new { Item = o, DateDiff = Math.Abs(Period.Between(o.LB.Date, item.Date.ToLocalDate()).Days) }) .OrderBy(o => o.DateDiff).Select(o => o.Item).Take(1).ToList(); } if (found.Any()) { var first = found.First(); first.Receipt = item; var potential = result.Where(r => r.Amount == item.Amount && r.SLR != null && r.LB == null && (r.SBC == null || !r.SBC.Any())).ToList(); if (potential.Count == 1) { potential.Single().Merge(first); first.MakeEmpty(); } } } } result = result.Where(o => !o.IsEmpty()).ToList(); { // match BankTransactions var byLbDate = result.Where(o => o.LB != null).GroupBy(o => o.LB.Date).ToDictionary(o => o.Key, o => o.ToList()); for (int i = transactions.Count - 1; i >= 0; i--) { var row = transactions[i]; if (byLbDate.TryGetValue(row.AccountingDate.ToLocalDate(), out var onDate)) { var combo = FindCombo(onDate, -row.Amount, mainTransactionAccount); if (combo != null) { combo.ForEach(o => o.BankTransactionReference = row); combo.ForEach(o => onDate.Remove(o)); transactions.RemoveAt(i); } } } // TODO: we can probably match some more var tmpXX = result.Where(o => o.LB == null).ToList(); } { // Split items with multiple SLR transactions into separate rows var withMultiSLRTransactions = result.Where(o => o.SLR != null && o.SLR.TransactionsNonAdminOrCorrections.Count() > 1).ToList(); foreach (var item in withMultiSLRTransactions) { var trans = item.SLR.TransactionsNonAdminOrCorrections.ToList(); for (int i = 0; i < trans.Count; i++) { //var foundSBC = item.SBC?.Where(o => o.Amount == row.Amount).ToList(); var copy = item.Copy(); copy.SLRTransactionRow = trans[i]; result.Add(copy); } result.Remove(item); } } result = result.OrderByDescending(o => o.SortDate) .ThenBy(o => o.CompanyName) .ThenBy(o => o.Amount).ToList(); return(result); }
public static void seed(this IApplicationBuilder app) { INormalizer <string> norm = new NameNormalizer(); IAuthManager authRepository = (IAuthManager)GetAppService <IAuthManager>(app); var orgs = new Collection <Organization>(); var acts = new Collection <Activity>(); var A = new Activity { Name = "A", EarlyStart = DateTime.Parse("2019-05-12T08:00:00"), Duration = 10 }; var B = new Activity { Name = "B", Duration = 12 }; var C = new Activity { Name = "C", Duration = 9 }; var D = new Activity { Name = "D", Duration = 16 }; var E = new Activity { Name = "E", Duration = 11 }; var F = new Activity { Name = "F", Duration = 4 }; var G = new Activity { Name = "G", Duration = 14, }; var H = new Activity { Name = "H", Duration = 16 }; var AC = new Arrow { FromActivity = A, ToActivity = C, Value = 3, Type = ArrowType.START_TO_START }; A.OutArrows.Add(AC); var AB = new Arrow { FromActivity = A, ToActivity = B, Value = 4, Type = ArrowType.FINISH_TO_START }; A.OutArrows.Add(AB); var BF = new Arrow { FromActivity = B, ToActivity = F, Value = 8, Type = ArrowType.FINISH_TO_FINISH }; B.OutArrows.Add(BF); var CE = new Arrow { FromActivity = C, ToActivity = E, Value = 17, Type = ArrowType.START_TO_START }; C.OutArrows.Add(CE); var CD = new Arrow { FromActivity = C, ToActivity = D, Value = 0, Type = ArrowType.FINISH_TO_START }; C.OutArrows.Add(CD); var EF = new Arrow { FromActivity = E, ToActivity = F, Value = 3, Type = ArrowType.START_TO_START }; E.OutArrows.Add(EF); var FH = new Arrow { FromActivity = F, ToActivity = H, Value = 0, Type = ArrowType.FINISH_TO_START }; F.OutArrows.Add(FH); var DG = new Arrow { FromActivity = D, ToActivity = G, Value = 0, Type = ArrowType.FINISH_TO_START }; D.OutArrows.Add(DG); var GH = new Arrow { FromActivity = G, ToActivity = H, Value = 3, Type = ArrowType.FINISH_TO_FINISH }; G.OutArrows.Add(GH); acts.Add(A); acts.Add(B); acts.Add(C); acts.Add(D); acts.Add(E); acts.Add(F); acts.Add(G); acts.Add(H); var projects = new Collection <Project>(); var project = new Project { Name = "Dating App", NormalizedName = norm.Normalize("Dating App"), Activities = acts }; projects.Add(project); var teams = new Collection <Team>(); teams.Add(new Team { NormalizedName = "developers", Name = norm.Normalize("developers") }); var org = new Organization { Name = "phoenix", NormalizedName = norm.Normalize("phoenix"), Projects = projects, Teams = teams }; orgs.Add(org); var noti = new Notification { Title = "run" }; var notis = new Collection <Notification>(); notis.Add(noti); User user = new User { UserName = "******", Country = "Syria", City = "Homs", Email = "*****@*****.**", PhoneNumber = "0993456585", Notifications = notis }; user.Members.Add(new Member { Type = @short.OWNER, Organiztion = org }); authRepository.Register(user, "P@$$w0rd"); var user2 = new User { UserName = "******", Country = "Syria", City = "Homs", Email = "*****@*****.**", PhoneNumber = "0993456585", }; // var access = new Access{} }
public void ReturnsEmptyStringWhenEmpty() { Assert.Equal("", normalizer.Normalize("")); }