public static AjaxUploadCallResult UploadBankTransactionData(string guid, string itemId)
{
AuthenticationData authData = GetAuthenticationDataAndCulture();
if (!authData.Authority.HasAccess(new Access(authData.CurrentOrganization, AccessAspect.BookkeepingDetails)))
{
throw new UnauthorizedAccessException();
}
string[] parts = itemId.Split('-');
FinancialAccount account = FinancialAccount.FromIdentity(Int32.Parse(parts[1]));
if (account.OrganizationId != authData.CurrentOrganization.Identity)
{
throw new UnauthorizedAccessException();
}
Documents documents = Documents.RecentFromDescription(guid);
// Safeguard 2019-Dec-23: Abort if more than one document (code below needs hardening against concurrent-threads race conditions)
if (documents.Count != 1)
{
throw new NotImplementedException();
}
// Load documents and process them as loaded strings, one by one
foreach (Document document in documents)
{
string documentData = document.GetReader().ReadToEnd();
ExternalBankDataProfile profile = account.ExternalBankDataProfile;
ExternalBankData loadedData = new ExternalBankData();
loadedData.Profile = profile;
try
{
loadedData.LoadData(documentData, authData.CurrentOrganization, account.Currency);
}
catch (Exception)
{
return(new AjaxUploadCallResult {
Success = false, DisplayMessage = "ERROR_FILEDATAFORMAT"
});
}
// Start async thread to import the data to the SQL database; the caller must
// check the status of the import
string identifier = guid + "-" + itemId + "-" + Guid.NewGuid().ToString();
/* Thread processThread = new Thread((ThreadStart) AsyncProcesses.ImportExternalTransactionDataThreadStart);
* processThread.Start(new AsyncProcesses.ImportExternalTransactionDataArgs {}); */
return(new AjaxUploadCallResult
{
Success = true,
StillProcessing = true,
Identifier = identifier
});
}
return(new AjaxUploadCallResult {
Success = true, StillProcessing = true
});
}
private static void ProcessUploadThread(object args)
{
string guid = ((ProcessThreadArguments)args).Guid;
Documents documents = Documents.RecentFromDescription(guid);
if (documents.Count != 1)
{
return; // abort
}
Document uploadedDoc = documents[0];
FinancialAccount account = ((ProcessThreadArguments)args).Account;
ExternalBankData externalData = new ExternalBankData();
externalData.Profile = ExternalBankDataProfile.FromIdentity(ExternalBankDataProfile.SESebId);
// TODO: HACK HACK HACK HACK LOAD
using (
StreamReader reader = new StreamReader(StorageRoot + uploadedDoc.ServerFileName,
Encoding.GetEncoding(1252)))
{
externalData.LoadData(reader, ((ProcessThreadArguments)args).Organization, account.ForeignCurrency);
}
_staticDataLookup[guid + "FirstTx"] = externalData.Records[0].DateTime.ToLongDateString();
_staticDataLookup[guid + "LastTx"] =
externalData.Records[externalData.Records.Length - 1].DateTime.ToLongDateString();
_staticDataLookup[guid + "TxCount"] = externalData.Records.Length.ToString("N0");
_staticDataLookup[guid + "Profile"] = externalData.Profile;
_staticDataLookup[guid + "PercentRead"] = 1;
DateTime timeWalker = externalData.Records[0].DateTime;
int currentRecordIndex = 0;
// Walk past the first identical time records, and verify that we have at least one balance record that matches
// our own for this timestamp. There may be several transactions in the master file, but at least one should have
// a post-transaction balance that matches our records, or something is much more broken.
long swarmopsCentsStart = account.GetDeltaCents(Constants.DateTimeLow, timeWalker.AddSeconds(1));
// At least one of the transactions for this timestamp should match.
bool foundMatch = false;
while (externalData.Records[currentRecordIndex].DateTime == timeWalker)
{
if (externalData.Records[currentRecordIndex].AccountBalanceCents == swarmopsCentsStart)
{
foundMatch = true;
// continue loop until on first record past initial conditions
}
currentRecordIndex++; // no test for array overrun in while
}
if (!foundMatch)
{
throw new InvalidOperationException("Unable to locate stable initial conditions for resynchronization");
}
// From here on out, every new timestamp should have the exact same delta in Swarmops as it has in the master file.
List <ExternalBankMismatchingDateTime> mismatchList = new List <ExternalBankMismatchingDateTime>();
while (currentRecordIndex < externalData.Records.Length)
{
DateTime lastTimestamp = timeWalker;
timeWalker = externalData.Records[currentRecordIndex].DateTime;
long swarmopsDeltaCents = account.GetDeltaCents(lastTimestamp.AddSeconds(1), timeWalker.AddSeconds(1));
// "AddSeconds" because DeltaCents operates on ">= lowbound, < highbound"
int timestampStartIndex = currentRecordIndex;
long masterDeltaCents = externalData.Records[currentRecordIndex++].TransactionNetCents;
int masterTransactionCount = 1;
while (currentRecordIndex < externalData.Records.Length &&
externalData.Records[currentRecordIndex].DateTime == timeWalker)
{
masterDeltaCents += externalData.Records[currentRecordIndex++].TransactionNetCents;
masterTransactionCount++;
}
if (masterDeltaCents != swarmopsDeltaCents)
{
// We have a mismatch. Add it to the list.
ExternalBankMismatchingDateTime newMismatch = new ExternalBankMismatchingDateTime();
newMismatch.DateTime = timeWalker;
newMismatch.MasterDeltaCents = masterDeltaCents;
newMismatch.MasterTransactionCount = masterTransactionCount;
newMismatch.SwarmopsDeltaCents = swarmopsDeltaCents;
newMismatch.SwarmopsTransactionCount = 0; // TODO
// Load transactions from both sources. First, create the interim construction object.
ExternalBankMismatchConstruction mismatchConstruction = new ExternalBankMismatchConstruction();
// Load from Master
for (int innerRecordIndex = timestampStartIndex;
innerRecordIndex < currentRecordIndex;
innerRecordIndex++)
{
string description = externalData.Records[innerRecordIndex].Description.Replace(" ", " ");
if (!mismatchConstruction.Master.ContainsKey(description))
{
mismatchConstruction.Master[description] =
new ExternalBankMismatchingRecordConstruction();
}
mismatchConstruction.Master[description].Cents.Add(
externalData.Records[innerRecordIndex].TransactionNetCents);
mismatchConstruction.Master[description].Transactions.Add(null);
// no dependencies on the master side, only on swarmops side
}
// Load from Swarmops
FinancialAccountRows swarmopsTransactionRows =
account.GetRowsFar(lastTimestamp, timeWalker);
// the "select far" is a boundary < x <= boundary selector. Default is boundary <= x < boundary.
Dictionary <int, FinancialTransaction> lookupTransactions =
new Dictionary <int, FinancialTransaction>();
// note all transaction IDs, then sum up per transaction
foreach (FinancialAccountRow swarmopsTransactionRow in swarmopsTransactionRows)
{
lookupTransactions[swarmopsTransactionRow.FinancialTransactionId] =
swarmopsTransactionRow.Transaction;
}
foreach (FinancialTransaction transaction in lookupTransactions.Values)
{
string description = transaction.Description.Replace(" ", " ");
// for legacy compatibility with new importer
if (!mismatchConstruction.Swarmops.ContainsKey(description))
{
mismatchConstruction.Swarmops[description] =
new ExternalBankMismatchingRecordConstruction();
}
long cents = transaction[account];
if (cents != 0) // only add nonzero records
{
mismatchConstruction.Swarmops[description].Cents.Add(transaction[account]);
mismatchConstruction.Swarmops[description].Transactions.Add(transaction);
}
}
// Then, parse the intermediate construction object to the presentation-and-action object.
Dictionary <string, ExternalBankMismatchingRecordDescription> mismatchingRecordList =
new Dictionary <string, ExternalBankMismatchingRecordDescription>();
foreach (string masterKey in mismatchConstruction.Master.Keys)
{
Dictionary <int, bool> checkMasterIndex = new Dictionary <int, bool>();
Dictionary <int, bool> checkSwarmopsIndex = new Dictionary <int, bool>();
// For each key and entry for each key;
// 1) locate an exact corresponding amount in swarmops records and log; failing that,
// 2) if exactly one record left in master and swarmops records, log; failing that,
// 3) log the rest of the master OR rest of swarmops records with no corresponding
// equivalent with counterpart. (May produce bad results if 2 consistent mismatches
// for every description.)
ExternalBankMismatchingRecordDescription newRecord =
new ExternalBankMismatchingRecordDescription();
newRecord.Description = masterKey;
List <long> masterCentsList = new List <long>();
List <long> swarmopsCentsList = new List <long>();
List <object> dependenciesList = new List <object>();
List <FinancialTransaction> transactionsList = new List <FinancialTransaction>();
List <ExternalBankMismatchResyncAction> actionsList =
new List <ExternalBankMismatchResyncAction>();
// STEP 1 - locate all identical matches
if (mismatchConstruction.Swarmops.ContainsKey(masterKey))
{
for (int masterIndex = 0;
masterIndex < mismatchConstruction.Master[masterKey].Cents.Count;
masterIndex++)
{
// no "continue" necessary on first run-through; nothing has been checked off yet
long findMasterCents = mismatchConstruction.Master[masterKey].Cents[masterIndex];
for (int swarmopsIndex = 0;
swarmopsIndex < mismatchConstruction.Swarmops[masterKey].Cents.Count;
swarmopsIndex++)
{
if (checkSwarmopsIndex.ContainsKey(swarmopsIndex))
{
continue;
// may have been checked off already in the rare case of twin identical amounts
}
if (findMasterCents == mismatchConstruction.Swarmops[masterKey].Cents[swarmopsIndex])
{
// There is a match as per case 1. Record both, mark both as used, continue.
masterCentsList.Add(findMasterCents);
swarmopsCentsList.Add(
mismatchConstruction.Swarmops[masterKey].Cents[swarmopsIndex]);
// should be equal, we're defensive here
transactionsList.Add(
mismatchConstruction.Swarmops[masterKey].Transactions[swarmopsIndex]);
dependenciesList.Add(
mismatchConstruction.Swarmops[masterKey].Transactions[swarmopsIndex]
.Dependency);
actionsList.Add(ExternalBankMismatchResyncAction.NoAction);
checkMasterIndex[masterIndex] = true;
checkSwarmopsIndex[swarmopsIndex] = true;
break;
}
}
}
}
// STEP 2 - if exactly one record left on both sides, connect and log as mismatching record
// TODO: improve logic to handle same number of records left on both sides
if (mismatchConstruction.Swarmops.ContainsKey(masterKey) &&
mismatchConstruction.Master[masterKey].Cents.Count - checkMasterIndex.Keys.Count == 1 &&
mismatchConstruction.Swarmops[masterKey].Cents.Count - checkSwarmopsIndex.Keys.Count == 1)
{
for (int masterIndex = 0;
masterIndex < mismatchConstruction.Master[masterKey].Cents.Count;
masterIndex++)
{
if (checkMasterIndex.ContainsKey(masterIndex))
{
continue; // This will fire for all but one indexes
}
long findMasterCents = mismatchConstruction.Master[masterKey].Cents[masterIndex];
for (int swarmopsIndex = 0;
swarmopsIndex < mismatchConstruction.Swarmops[masterKey].Cents.Count;
swarmopsIndex++)
{
if (checkSwarmopsIndex.ContainsKey(swarmopsIndex))
{
continue;
}
masterCentsList.Add(findMasterCents);
swarmopsCentsList.Add(mismatchConstruction.Swarmops[masterKey].Cents[swarmopsIndex]);
dependenciesList.Add(
mismatchConstruction.Swarmops[masterKey].Transactions[swarmopsIndex].Dependency);
transactionsList.Add(
mismatchConstruction.Swarmops[masterKey].Transactions[swarmopsIndex]);
actionsList.Add(ExternalBankMismatchResyncAction.RewriteSwarmops);
checkMasterIndex[masterIndex] = true;
checkSwarmopsIndex[swarmopsIndex] = true;
}
}
}
// STEP 3 - log remaining records on both sides as missing counterparts. Only one of these should fire.
// STEP 3a - log remaining on Master side
if (mismatchConstruction.Master[masterKey].Cents.Count > checkMasterIndex.Keys.Count)
{
for (int masterIndex = 0;
masterIndex < mismatchConstruction.Master[masterKey].Cents.Count;
masterIndex++)
{
if (checkMasterIndex.ContainsKey(masterIndex))
{
continue;
}
masterCentsList.Add(mismatchConstruction.Master[masterKey].Cents[masterIndex]);
swarmopsCentsList.Add(0); // null equivalent; invalid value
dependenciesList.Add(null);
transactionsList.Add(null);
actionsList.Add(ExternalBankMismatchResyncAction.CreateSwarmops);
checkMasterIndex[masterIndex] = true;
}
}
// STEP 3b - log remaining on Swarmops side
if (mismatchConstruction.Swarmops.ContainsKey(masterKey) &&
mismatchConstruction.Swarmops[masterKey].Cents.Count > checkSwarmopsIndex.Keys.Count)
{
for (int swarmopsIndex = 0;
swarmopsIndex < mismatchConstruction.Swarmops[masterKey].Cents.Count;
swarmopsIndex++)
{
if (checkSwarmopsIndex.ContainsKey(swarmopsIndex))
{
continue;
}
masterCentsList.Add(0); // null equivalent; invalid value
swarmopsCentsList.Add(mismatchConstruction.Swarmops[masterKey].Cents[swarmopsIndex]);
transactionsList.Add(
mismatchConstruction.Swarmops[masterKey].Transactions[swarmopsIndex]);
if (mismatchConstruction.Swarmops[masterKey].Transactions[swarmopsIndex].Dependency !=
null)
{
dependenciesList.Add(
mismatchConstruction.Swarmops[masterKey].Transactions[swarmopsIndex].Dependency);
actionsList.Add(ExternalBankMismatchResyncAction.ManualAction); // can't auto
}
else
{
dependenciesList.Add(null);
actionsList.Add(ExternalBankMismatchResyncAction.DeleteSwarmops);
}
checkMasterIndex[swarmopsIndex] = true;
}
}
newRecord.MasterCents = masterCentsList.ToArray();
newRecord.SwarmopsCents = swarmopsCentsList.ToArray();
newRecord.ResyncActions = actionsList.ToArray();
// newRecord.TransactionDependencies = dependenciesList.ToArray();
newRecord.Transactions = transactionsList.ToArray();
mismatchingRecordList[masterKey] = newRecord;
}
// Finally, add the transactions that were (described) in Swarmops but not in Master
foreach (string swarmopsKey in mismatchConstruction.Swarmops.Keys)
{
if (!mismatchingRecordList.ContainsKey(swarmopsKey))
{
mismatchingRecordList[swarmopsKey] = new ExternalBankMismatchingRecordDescription();
mismatchingRecordList[swarmopsKey].Description = swarmopsKey;
mismatchingRecordList[swarmopsKey].SwarmopsCents =
mismatchConstruction.Swarmops[swarmopsKey].Cents.ToArray();
mismatchingRecordList[swarmopsKey].Transactions =
mismatchConstruction.Swarmops[swarmopsKey].Transactions.ToArray();
mismatchingRecordList[swarmopsKey].MasterCents =
new long[mismatchConstruction.Swarmops[swarmopsKey].Cents.Count]; // inits to zero
mismatchingRecordList[swarmopsKey].ResyncActions =
new ExternalBankMismatchResyncAction[
mismatchConstruction.Swarmops[swarmopsKey].Cents.Count];
for (int index = 0;
index < mismatchingRecordList[swarmopsKey].ResyncActions.Length;
index++)
{
mismatchingRecordList[swarmopsKey].ResyncActions[index] =
ExternalBankMismatchResyncAction.DeleteSwarmops;
}
}
}
newMismatch.MismatchingRecords = mismatchingRecordList.Values.ToArray();
mismatchList.Add(newMismatch);
}
int percentProcessed = (int)(currentRecordIndex * 100L / externalData.Records.Length);
lock (_staticDataLookup)
{
if (percentProcessed > 1)
{
_staticDataLookup[guid + "PercentRead"] = percentProcessed;
// for the progress bar to update async
}
if (percentProcessed > 99)
{
// Placed inside loop to have a contiguous lock block, even though it decreases performance.
// Should normally be placed just outside.
_staticDataLookup[guid + "MismatchArray"] = mismatchList.ToArray();
_staticDataLookup[guid + "Account"] = account;
}
}
}
}
public static ImportExternalTransactionDataResults ImportExternalTransactionData(ExternalBankData import, ImportExternalTransactionDataArgs args)
{
FinancialAccount assetAccount = args.Account;
FinancialAccount autoDepositAccount = args.Organization.FinancialAccounts.IncomeDonations;
int autoDepositLimit = 0; // Disabled; TODO: this.CurrentOrganization.Parameters.AutoDonationLimit;
bool autosetInitialBalance = false;
ImportExternalTransactionDataResults result = new ImportExternalTransactionDataResults();
int count = 0;
int progressUpdateInterval = import.Records.Length / 40;
Int64 importedCentsTotal = 0;
if (progressUpdateInterval > 100)
{
progressUpdateInterval = 100;
}
ProgressBarBackend progressDisplay = new ProgressBarBackend(args.Guid);
Currency organizationCurrency = assetAccount.Organization.Currency;
Currency accountCurrency = assetAccount.ForeignCurrency;
if (accountCurrency == null)
{
accountCurrency = organizationCurrency;
}
FinancialAccountRows existingRows = assetAccount.GetRows(Constants.DateTimeLow, Constants.DateTimeHigh);
// gets all
if (existingRows.Count == 0)
{
autosetInitialBalance = true;
}
foreach (ExternalBankDataRecord row in import.Records)
{
// Update progress.
count++;
if (progressUpdateInterval < 2 || count % progressUpdateInterval == 0)
{
int percent = (count * 99) / import.Records.Length;
progressDisplay.Set(percent);
}
// Update high- and low-water marks.
if (row.DateTime < result.EarliestTransaction)
{
result.EarliestTransaction = row.DateTime;
}
if (row.DateTime > result.LatestTransaction)
{
result.LatestTransaction = row.DateTime;
}
string importKey = row.ImportHash;
Int64 amountCents = row.TransactionNetCents;
if (amountCents == 0)
// defensive programming - these _should_ be duplicated in the interpreter if no "fee" field
{
amountCents = row.TransactionGrossCents;
}
Int64 foreignCents = amountCents;
importedCentsTotal += amountCents;
if (accountCurrency.Identity != organizationCurrency.Identity)
{
amountCents =
new Money(amountCents, accountCurrency, row.DateTime).ToCurrency(organizationCurrency).Cents;
}
FinancialTransaction transaction = FinancialTransaction.ImportWithStub(args.Organization.Identity,
row.DateTime,
assetAccount.Identity, amountCents,
row.Description, importKey, Sha256.Compute(row.RawData),
args.CurrentUser.Identity);
if (transaction != null)
{
// The transaction was created.
result.TransactionsImported++;
// If non-presentation currency, log the account currency amount as well.
if (accountCurrency.Identity != organizationCurrency.Identity)
{
transaction.Rows[0].AmountForeignCents = new Money(foreignCents, accountCurrency);
}
if (row.Description.ToLowerInvariant().StartsWith(args.Organization.IncomingPaymentTag))
{
// Check for previously imported payment group
// TODO: MAKE FLEXIBLE - CALL PAYMENTREADERINTERFACE!
// HACK HACK HACK HACK
PaymentGroup group = PaymentGroup.FromTag(args.Organization,
"SEBGM" + DateTime.Today.Year + // TODO: Get tags from org
row.Description.Substring(args.Organization.IncomingPaymentTag.Length).Trim());
if (group != null && group.Open)
{
// There was a previously imported and not yet closed payment group matching this transaction
// Close the payment group and match the transaction against accounts receivable
transaction.Dependency = group;
group.Open = false;
transaction.AddRow(args.Organization.FinancialAccounts.AssetsOutboundInvoices, -amountCents,
args.CurrentUser);
}
}
else if (amountCents < 0)
{
// Autowithdrawal mechanisms removed, condition kept because of downstream else-if conditions
}
else if (amountCents > 0)
{
if (row.FeeCents < 0)
{
// This is always an autodeposit, if there is a fee (which is never > 0.0)
transaction.AddRow(args.Organization.FinancialAccounts.CostsBankFees, -row.FeeCents,
args.CurrentUser);
transaction.AddRow(autoDepositAccount, -row.TransactionGrossCents, args.CurrentUser);
}
else if (amountCents < autoDepositLimit * 100)
{
// Book against autoDeposit account.
transaction.AddRow(autoDepositAccount, -amountCents, args.CurrentUser);
}
}
}
else
{
// Transaction was not imported; assume duplicate
result.DuplicateTransactions++;
}
}
// Import complete. Return true if the bookkeeping account matches the bank data.
Int64 databaseAccountBalanceCents;
if (accountCurrency.Identity == organizationCurrency.Identity)
{
databaseAccountBalanceCents = assetAccount.BalanceTotalCents;
}
else
{
// foreign-currency account
databaseAccountBalanceCents = assetAccount.ForeignCurrencyBalance.Cents;
}
// Subtract any transactions made after the most recent imported transaction.
// This is necessary in case of Paypal and others which continuously feed the
// bookkeeping account with new transactions; it will already have fed transactions
// beyond the end-of-file.
Int64 beyondEofCents = assetAccount.GetDeltaCents(result.LatestTransaction.AddSeconds(1),
DateTime.Now.AddDays(2));
// Caution: the "AddSeconds(1)" is not foolproof, there may be other new txs on the same second.
if (databaseAccountBalanceCents - beyondEofCents == import.LatestAccountBalanceCents)
{
Payouts.AutomatchAgainstUnbalancedTransactions(args.Organization);
OutboundInvoices.AutomatchAgainstUnbalancedTransactions(args.Organization, args.CurrentUser);
result.AccountBalanceMatchesBank = true;
result.BalanceMismatchCents = 0;
}
else
{
result.AccountBalanceMatchesBank = false;
result.BalanceMismatchCents = (databaseAccountBalanceCents - beyondEofCents) -
import.LatestAccountBalanceCents;
if (autosetInitialBalance)
{
Int64 newInitialBalanceCents = -result.BalanceMismatchCents;
Money initialBalance = new Money(newInitialBalanceCents, accountCurrency);
assetAccount.InitialBalance = initialBalance;
result.InitialBalanceCents = newInitialBalanceCents;
result.InitialBalanceCurrencyCode = accountCurrency.Code;
// make an approximation of conversion rate set for initial balance in presentation to tell user
initialBalance.ValuationDateTime = new DateTime(assetAccount.Organization.FirstFiscalYear, 1, 1);
result.BalanceMismatchCents = initialBalance.ToCurrency(assetAccount.Organization.Currency).Cents;
}
}
result.CurrencyCode = args.Organization.Currency.Code;
GuidCache.Set(args.Guid + "-Results", result);
return(result);
}