private OutstandingAccounts GetOutstandingExpenseClaims(bool renderPresentTime, DateTime targetDateTime) { OutstandingAccounts outstandingAccounts = new OutstandingAccounts(); if (renderPresentTime) { ExpenseClaims claims = ExpenseClaims.ForOrganization(_authenticationData.CurrentOrganization); Payouts payouts = Payouts.ForOrganization(_authenticationData.CurrentOrganization); foreach (ExpenseClaim claim in claims) { outstandingAccounts.Add(OutstandingAccount.FromExpenseClaim(claim, DateTime.MinValue)); } foreach (Payout payout in payouts) { foreach (ExpenseClaim claim in payout.DependentExpenseClaims) { outstandingAccounts.Add(OutstandingAccount.FromExpenseClaim(claim, payout.ExpectedTransactionDate)); } } } else { // This is a very expensive op. We need to load all expense claims and process them in logic, looking at their // payouts and end financial transactions, as the traceability was built to be efficient backwards (tracing money // paid out to its documentation), rather than forwards. // A possible optimization could be to load the payouts into a hash table initially instead of looking them up // with two dbroundtrips per expense claim. It should be more efficient to have three dbroundtrips to load expenses, // payouts, and the relevant transactions, then stuff it all into hash tables keyed by identity and process it // in-memory. // A future optimization involves adding "ClosedDateTime" to ExpenseClaims and Payouts at the database level. // Load all (ALL) expense claims for org ExpenseClaims allClaims = ExpenseClaims.ForOrganization(_authenticationData.CurrentOrganization, true); // includes closed // For each claim, determine whether it was open or not at targetDateTime foreach (ExpenseClaim claim in allClaims) { // if it wasn't opened until after target datetime, discard if (claim.CreatedDateTime > targetDateTime) { continue; } // At this point, we are iterating over full set of expense claims opened before targetDateTime. We want the // set of claims that were still open - as determined by the ledger account Expense Claims - on targetDateTime. // // For the expense claims that are still open, this is trivially true. // // However, for others, we need to look at the corresponding Payout and its FinancialTransaction to determine // whetherthe transaction's date is on the other side of targetDateTime. bool includeThisClaim = false; DateTime dateTimeClosed = DateTime.MinValue; if (claim.Open) { includeThisClaim = true; } else { // claim is closed. This is where we need to look first at payout then at financial transaction. Payout payout = claim.Payout; if (payout == null) { // TODO: Find outbound invoice item that depends on this expense claim continue; // some legacy from when Swarmops was primitive - earliest claims don't have payouts } if (payout.Open) { // Transaction is not yet closed. Include this claim, set closed to expected-closed. includeThisClaim = true; dateTimeClosed = payout.ExpectedTransactionDate; } else { FinancialTransaction transaction = payout.FinancialTransaction; if (transaction == null) { throw new InvalidOperationException( "This should not happen (transaction not found on closed payout)"); } if (transaction.DateTime > targetDateTime) { // yes, the claim was opened before targetDateTime and closed after it. This should be included. includeThisClaim = true; dateTimeClosed = transaction.DateTime; } } // TODO: An expense claim can also be closed through an outbound invoice, in case there was a larger cash // advance that wasn't fully covered, and where the two are added together. Check for this condition as well. // // OutboundInvoiceItem should have a dependency on the expense claim if this is the case. } if (includeThisClaim) { outstandingAccounts.Add(OutstandingAccount.FromExpenseClaim(claim, dateTimeClosed)); } } } return(outstandingAccounts); }
private OutstandingAccounts GetOutstandingCashAdvances(bool renderPresentTime, DateTime targetDateTime) { OutstandingAccounts outstandingAccounts = new OutstandingAccounts(); // This is a very expensive op. We need to load ALL the cash advances, and determine the opening date from its associated // payout. Then, we need to determine when it was paid pack through another associated payout (or invoice payment) which // I don't know how to find at the time of writing this comment, and if the target date is in between those two, then the // cash advance was outstanding on the target date (or is outstanding now) // A possible optimization could be to load the payouts into a hash table initially instead of looking them up // with two dbroundtrips per expense claim. It should be more efficient to have three dbroundtrips to load expenses, // payouts, and the relevant transactions, then stuff it all into hash tables keyed by identity and process it // in-memory. // A future optimization involves adding "ClosedDateTime" to some tables. // Load all (ALL) cash advances for org CashAdvances allCashAdvances = CashAdvances.ForOrganization(_authenticationData.CurrentOrganization, true); // includes closed // For each advance, determine whether it was open or not at targetDateTime foreach (CashAdvance cashAdvance in allCashAdvances) { // if it wasn't opened until after target datetime, discard (optimization) if (cashAdvance.CreatedDateTime > targetDateTime) { continue; } // At this point, we are iterating over full set of cash advances opened before targetDateTime, but not necessarily // paid out before targetDateTime. We want the set of advances that had been paid out, and had not been paid back, // as determined by the ledger account Cash Advances - on targetDateTime. bool includeThisAdvance = false; DateTime dateTimePaidBack = DateTime.MinValue; DateTime dateTimePaidOut = DateTime.MaxValue; if (!cashAdvance.PaidOut) { // This cash advance hasn't entered the ledger yet continue; } Payout payoutOut = cashAdvance.PayoutOut; Payout payoutBack = cashAdvance.PayoutBack; if (payoutOut == null) { continue; // This cash advance has not been paid out yet } try { dateTimePaidOut = payoutOut.FinancialTransaction.DateTime; } catch (ArgumentException) { // It's possible the payout exists, but hasn't found its transaction yet. If so, this will throw // an ArgumentException here. In either case, it's not in the ledger, so don't include it. continue; } if (dateTimePaidOut > targetDateTime) { // This cash advance falls outside the scope of our window, so ignore continue; } if (payoutBack != null) { try { dateTimePaidBack = payoutBack.FinancialTransaction.DateTime; } catch (ArgumentException) { // as above continue; } if (dateTimePaidBack > targetDateTime) { includeThisAdvance = true; } } else { // As there is no payback, this advance is paid out and still open. includeThisAdvance = true; // TODO: If there is no payout where the cash advance is deducted, it may be // invoiced to close it. // TODO: Find OutboundInvoiceItem that depends on this CashAdvance. Look at the invoice date. That's our PaidBack datetime. } if (includeThisAdvance) { outstandingAccounts.Add(OutstandingAccount.FromCashAdvance(cashAdvance, dateTimePaidOut)); } } return(outstandingAccounts); }