/// <summary>
/// Creates the saved account.
/// </summary>
/// <param name="parameters">The parameters.</param>
/// <param name="paymentDetail">The payment detail.</param>
/// <param name="financialGateway">The financial gateway.</param>
/// <param name="person">The person.</param>
/// <param name="rockContext">The rock context.</param>
/// <returns></returns>
private FinancialPersonSavedAccount CreateSavedAccount(PaymentParameters parameters, FinancialPaymentDetail paymentDetail, FinancialGateway financialGateway, Person person, RockContext rockContext)
{
var lastFour = paymentDetail.AccountNumberMasked.Substring(paymentDetail.AccountNumberMasked.Length - 4);
var name = string.Empty;
if (parameters.AccountType.ToLower() != "credit")
{
if (string.IsNullOrWhiteSpace(parameters.RoutingNumber))
{
GenerateResponse(HttpStatusCode.BadRequest, "RoutingNumber is required for ACH transactions");
return(null);
}
if (string.IsNullOrWhiteSpace(parameters.AccountNumber))
{
GenerateResponse(HttpStatusCode.BadRequest, "AccountNumber is required");
return(null);
}
name = "Bank card ***" + lastFour;
var bankAccountService = new FinancialPersonBankAccountService(rockContext);
var accountNumberSecured = FinancialPersonBankAccount.EncodeAccountNumber(parameters.RoutingNumber, parameters.AccountNumber);
var bankAccount = bankAccountService.Queryable().Where(a =>
a.AccountNumberSecured == accountNumberSecured &&
a.PersonAliasId == person.PrimaryAliasId.Value).FirstOrDefault();
if (bankAccount == null)
{
bankAccount = new FinancialPersonBankAccount();
bankAccount.PersonAliasId = person.PrimaryAliasId.Value;
bankAccount.AccountNumberMasked = paymentDetail.AccountNumberMasked;
bankAccount.AccountNumberSecured = accountNumberSecured;
bankAccountService.Add(bankAccount);
}
}
else
{
name = "Credit card ***" + lastFour;
}
var savedAccount = new FinancialPersonSavedAccount {
PersonAliasId = person.PrimaryAliasId,
FinancialGatewayId = financialGateway.Id,
Name = name,
FinancialPaymentDetailId = paymentDetail.Id
};
new FinancialPersonSavedAccountService(rockContext).Add(savedAccount);
rockContext.SaveChanges();
return(savedAccount);
}
/// <summary>
/// Binds the grid.
/// </summary>
private void BindGrid()
{
var rockContext = new RockContext();
var financialPersonBankAccountService = new FinancialPersonBankAccountService(rockContext);
var qry = financialPersonBankAccountService.Queryable();
if (this.Person != null && this.Person.PrimaryAliasId.HasValue)
{
qry = qry.Where(a => a.PersonAliasId == this.Person.PrimaryAliasId.Value);
SortProperty sortProperty = gList.SortProperty;
if (sortProperty != null)
{
gList.DataSource = qry.Sort(sortProperty).ToList();
}
else
{
gList.DataSource = qry.OrderBy(s => s.AccountNumberMasked).ToList();
}
gList.DataBind();
}
}
/// <summary>
/// Handles the Click event of the btnNext control.
/// </summary>
/// <param name="sender">The source of the event.</param>
/// <param name="e">The <see cref="EventArgs"/> instance containing the event data.</param>
protected void btnNext_Click(object sender, EventArgs e)
{
var changes = new List <string>();
var rockContext = new RockContext();
var financialTransactionService = new FinancialTransactionService(rockContext);
var financialTransactionDetailService = new FinancialTransactionDetailService(rockContext);
var financialPersonBankAccountService = new FinancialPersonBankAccountService(rockContext);
int txnId = hfTransactionId.Value.AsInteger();
var financialTransaction = financialTransactionService
.Queryable("AuthorizedPersonAlias.Person,ProcessedByPersonAlias.Person")
.FirstOrDefault(t => t.Id == txnId);
// set the AuthorizedPersonId (the person who wrote the check, for example) to the if the SelectNew person (if selected) or person selected in the drop down (if there is somebody selected)
int?authorizedPersonId = ppSelectNew.PersonId ?? ddlIndividual.SelectedValue.AsIntegerOrNull();
var accountNumberSecured = hfCheckMicrHashed.Value;
if (cbTotalAmount.Text.AsDecimalOrNull().HasValue&& !authorizedPersonId.HasValue)
{
nbSaveError.Text = "Transaction must be matched to a person when the amount is specified.";
nbSaveError.Visible = true;
return;
}
// if the transaction was previously matched, but user unmatched it, save it as an unmatched transaction and clear out the detail records (we don't want an unmatched transaction to have detail records)
if (financialTransaction != null &&
financialTransaction.AuthorizedPersonAliasId.HasValue &&
!authorizedPersonId.HasValue)
{
financialTransaction.AuthorizedPersonAliasId = null;
foreach (var detail in financialTransaction.TransactionDetails)
{
History.EvaluateChange(changes, detail.Account != null ? detail.Account.Name : "Unknown", detail.Amount.FormatAsCurrency(), string.Empty);
financialTransactionDetailService.Delete(detail);
}
changes.Add("Unmatched transaction");
HistoryService.SaveChanges(
rockContext,
typeof(FinancialBatch),
Rock.SystemGuid.Category.HISTORY_FINANCIAL_TRANSACTION.AsGuid(),
financialTransaction.BatchId.Value,
changes,
string.Format("Transaction Id: {0}", financialTransaction.Id),
typeof(FinancialTransaction),
financialTransaction.Id,
false);
rockContext.SaveChanges();
// if the transaction was unmatched, clear out the ProcessedBy fields since we didn't match the transaction and are moving on to process another transaction
MarkTransactionAsNotProcessedByCurrentUser(hfTransactionId.Value.AsInteger());
}
// if the transaction is matched to somebody, attempt to save it. Otherwise, if the transaction was previously matched, but user unmatched it, save it as an unmatched transaction
if (financialTransaction != null && authorizedPersonId.HasValue)
{
if (cbTotalAmount.Text.AsDecimalOrNull() == null)
{
nbSaveError.Text = "Total amount must be allocated to accounts.";
nbSaveError.Visible = true;
return;
}
var personAlias = new PersonAliasService(rockContext).GetPrimaryAlias(authorizedPersonId.Value);
int?personAliasId = personAlias != null ? personAlias.Id : (int?)null;
// if this transaction has an accountnumber associated with it (in other words, it's a valid scanned check), ensure there is a financialPersonBankAccount record
if (financialTransaction.MICRStatus == MICRStatus.Success && !string.IsNullOrWhiteSpace(accountNumberSecured))
{
var financialPersonBankAccount = financialPersonBankAccountService.Queryable().Where(a => a.AccountNumberSecured == accountNumberSecured && a.PersonAlias.PersonId == authorizedPersonId.Value).FirstOrDefault();
if (financialPersonBankAccount == null)
{
if (personAliasId.HasValue)
{
financialPersonBankAccount = new FinancialPersonBankAccount();
financialPersonBankAccount.PersonAliasId = personAliasId.Value;
financialPersonBankAccount.AccountNumberSecured = accountNumberSecured;
var checkMicrClearText = Encryption.DecryptString(financialTransaction.CheckMicrParts);
var parts = checkMicrClearText.Split('_');
if (parts.Length >= 2)
{
financialPersonBankAccount.AccountNumberMasked = parts[1].Masked();
}
if (string.IsNullOrWhiteSpace(financialPersonBankAccount.AccountNumberMasked))
{
financialPersonBankAccount.AccountNumberMasked = "************????";
}
financialPersonBankAccountService.Add(financialPersonBankAccount);
}
}
}
string prevPerson = (financialTransaction.AuthorizedPersonAlias != null && financialTransaction.AuthorizedPersonAlias.Person != null) ?
financialTransaction.AuthorizedPersonAlias.Person.FullName : string.Empty;
string newPerson = string.Empty;
if (personAliasId.HasValue)
{
newPerson = personAlias.Person.FullName;
financialTransaction.AuthorizedPersonAliasId = personAliasId;
}
History.EvaluateChange(changes, "Person", prevPerson, newPerson);
// just in case this transaction is getting re-edited either by the same user, or somebody else, clean out any existing TransactionDetail records
foreach (var detail in financialTransaction.TransactionDetails.ToList())
{
financialTransactionDetailService.Delete(detail);
History.EvaluateChange(changes, detail.Account != null ? detail.Account.Name : "Unknown", detail.Amount.FormatAsCurrency(), string.Empty);
}
foreach (var accountBox in rptAccounts.ControlsOfTypeRecursive <CurrencyBox>())
{
var amount = accountBox.Text.AsDecimalOrNull();
if (amount.HasValue && amount.Value >= 0)
{
var financialTransactionDetail = new FinancialTransactionDetail();
financialTransactionDetail.TransactionId = financialTransaction.Id;
financialTransactionDetail.AccountId = accountBox.Attributes["data-account-id"].AsInteger();
financialTransactionDetail.Amount = amount.Value;
financialTransactionDetailService.Add(financialTransactionDetail);
History.EvaluateChange(changes, accountBox.Label, 0.0M.FormatAsCurrency(), amount.Value.FormatAsCurrency());
}
}
financialTransaction.Summary = tbSummary.Text;
financialTransaction.ProcessedByPersonAliasId = this.CurrentPersonAlias.Id;
financialTransaction.ProcessedDateTime = RockDateTime.Now;
changes.Add("Matched transaction");
HistoryService.SaveChanges(
rockContext,
typeof(FinancialBatch),
Rock.SystemGuid.Category.HISTORY_FINANCIAL_TRANSACTION.AsGuid(),
financialTransaction.BatchId.Value,
changes,
personAlias != null && personAlias.Person != null ? personAlias.Person.FullName : string.Format("Transaction Id: {0}", financialTransaction.Id),
typeof(FinancialTransaction),
financialTransaction.Id,
false);
rockContext.SaveChanges();
}
else
{
// if the transaction was not matched, clear out the ProcessedBy fields since we didn't match the transaction and are moving on to process another transaction
MarkTransactionAsNotProcessedByCurrentUser(hfTransactionId.Value.AsInteger());
}
NavigateToTransaction(Direction.Next);
}
/// <summary>
/// Navigates to the next (or previous) transaction to edit
/// </summary>
private void NavigateToTransaction(Direction direction)
{
// put a lock around the entire NavigateToTransaction logic so that the navigation and "other person editing" logic will work consistently even if multiple people are editing the same batch
lock ( transactionMatchingLockObject )
{
hfDoFadeIn.Value = "1";
nbSaveError.Visible = false;
int? fromTransactionId = hfTransactionId.Value.AsIntegerOrNull();
int? toTransactionId = null;
List <int> historyList = hfBackNextHistory.Value.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries).AsIntegerList().Where(a => a > 0).ToList();
int position = hfHistoryPosition.Value.AsIntegerOrNull() ?? -1;
if (direction == Direction.Prev)
{
position--;
}
else
{
position++;
}
if (historyList.Count > position)
{
if (position >= 0)
{
toTransactionId = historyList[position];
}
else
{
// if we trying to go previous when we are already at the start of the list, wrap around to the last item in the list
toTransactionId = historyList.Last();
position = historyList.Count - 1;
}
}
hfHistoryPosition.Value = position.ToString();
int batchId = hfBatchId.Value.AsInteger();
var rockContext = new RockContext();
var financialPersonBankAccountService = new FinancialPersonBankAccountService(rockContext);
var financialTransactionService = new FinancialTransactionService(rockContext);
var qryTransactionsToMatch = financialTransactionService.Queryable()
.Where(a => a.AuthorizedPersonAliasId == null && a.ProcessedByPersonAliasId == null);
if (batchId != 0)
{
qryTransactionsToMatch = qryTransactionsToMatch.Where(a => a.BatchId == batchId);
}
// if a specific transactionId was specified (because we are navigating thru history), load that one. Otherwise, if a batch is specified, get the first unmatched transaction in that batch
if (toTransactionId.HasValue)
{
qryTransactionsToMatch = financialTransactionService
.Queryable("AuthorizedPersonAlias.Person,ProcessedByPersonAlias.Person")
.Where(a => a.Id == toTransactionId);
}
if (historyList.Any() && !toTransactionId.HasValue)
{
// since we are looking for a transaction we haven't viewed or matched yet, look for the next one in the database that we haven't seen yet
qryTransactionsToMatch = qryTransactionsToMatch.Where(a => !historyList.Contains(a.Id));
}
qryTransactionsToMatch = qryTransactionsToMatch.OrderBy(a => a.CreatedDateTime).ThenBy(a => a.Id);
FinancialTransaction transactionToMatch = qryTransactionsToMatch.FirstOrDefault();
if (transactionToMatch == null)
{
// we exhausted the transactions that aren't processed and aren't in our history list, so remove those those restrictions and show all transactions that haven't been matched yet
var qryRemainingTransactionsToMatch = financialTransactionService
.Queryable("AuthorizedPersonAlias.Person,ProcessedByPersonAlias.Person")
.Where(a => a.AuthorizedPersonAliasId == null);
if (batchId != 0)
{
qryRemainingTransactionsToMatch = qryRemainingTransactionsToMatch.Where(a => a.BatchId == batchId);
}
// get the first transaction that we haven't visited yet, or the next one we have visited after one we are on, or simple the first unmatched one
transactionToMatch = qryRemainingTransactionsToMatch.Where(a => a.Id > fromTransactionId && !historyList.Contains(a.Id)).FirstOrDefault()
?? qryRemainingTransactionsToMatch.Where(a => a.Id > fromTransactionId).FirstOrDefault()
?? qryRemainingTransactionsToMatch.FirstOrDefault();
if (transactionToMatch != null)
{
historyList.Add(transactionToMatch.Id);
position = historyList.LastIndexOf(transactionToMatch.Id);
hfHistoryPosition.Value = position.ToString();
}
}
else
{
if (!toTransactionId.HasValue)
{
historyList.Add(transactionToMatch.Id);
}
}
nbNoUnmatchedTransactionsRemaining.Visible = transactionToMatch == null;
pnlEdit.Visible = transactionToMatch != null;
nbIsInProcess.Visible = false;
if (transactionToMatch != null)
{
if (transactionToMatch.ProcessedByPersonAlias != null)
{
if (transactionToMatch.AuthorizedPersonAliasId.HasValue)
{
nbIsInProcess.Text = string.Format("Warning. This transaction was matched by {0} at {1} ({2})", transactionToMatch.ProcessedByPersonAlias.Person, transactionToMatch.ProcessedDateTime.ToString(), transactionToMatch.ProcessedDateTime.ToRelativeDateString());
nbIsInProcess.Visible = true;
}
else
{
// display a warning if some other user has this marked as InProcess (and it isn't matched)
if (transactionToMatch.ProcessedByPersonAliasId != CurrentPersonAliasId)
{
nbIsInProcess.Text = string.Format("Warning. This transaction is getting processed by {0} as of {1} ({2})", transactionToMatch.ProcessedByPersonAlias.Person, transactionToMatch.ProcessedDateTime.ToString(), transactionToMatch.ProcessedDateTime.ToRelativeDateString());
nbIsInProcess.Visible = true;
}
}
}
// Unless somebody else is processing it, immediately mark the transaction as getting processed by the current person so that other potentional transaction matching sessions will know that it is currently getting looked at
if (!transactionToMatch.ProcessedByPersonAliasId.HasValue)
{
transactionToMatch.ProcessedByPersonAlias = null;
transactionToMatch.ProcessedByPersonAliasId = CurrentPersonAliasId;
transactionToMatch.ProcessedDateTime = RockDateTime.Now;
rockContext.SaveChanges();
}
hfTransactionId.Value = transactionToMatch.Id.ToString();
// get the first 2 images (should be no more than 2, but just in case)
var transactionImages = transactionToMatch.Images.OrderBy(a => a.Order).Take(2).ToList();
ddlIndividual.Items.Clear();
ddlIndividual.Items.Add(new ListItem(null, null));
// clear any previously shown badges
ddlIndividual.Attributes.Remove("disabled");
badgeIndividualCount.InnerText = string.Empty;
// if this transaction has a CheckMicrParts, try to find matching person(s)
string checkMicrHashed = null;
if (!string.IsNullOrWhiteSpace(transactionToMatch.CheckMicrParts))
{
try
{
var checkMicrClearText = Encryption.DecryptString(transactionToMatch.CheckMicrParts);
var parts = checkMicrClearText.Split('_');
if (parts.Length >= 2)
{
checkMicrHashed = FinancialPersonBankAccount.EncodeAccountNumber(parts[0], parts[1]);
}
}
catch
{
// intentionally ignore exception when decripting CheckMicrParts since we'll be checking for null below
}
}
hfCheckMicrHashed.Value = checkMicrHashed;
if (!string.IsNullOrWhiteSpace(checkMicrHashed))
{
var matchedPersons = financialPersonBankAccountService.Queryable().Where(a => a.AccountNumberSecured == checkMicrHashed).Select(a => a.PersonAlias.Person).Distinct();
foreach (var person in matchedPersons.OrderBy(a => a.LastName).ThenBy(a => a.NickName))
{
ddlIndividual.Items.Add(new ListItem(person.FullNameReversed, person.Id.ToString()));
}
}
if (ddlIndividual.Items.Count == 2)
{
// only one person (and the None selection) are in the list, so init to the person
ddlIndividual.SelectedIndex = 1;
}
else
{
// either zero or multiple people are in the list, so default to none so they are forced to choose
ddlIndividual.SelectedIndex = 0;
}
if (transactionToMatch.AuthorizedPersonAlias != null && transactionToMatch.AuthorizedPersonAlias.Person != null)
{
var person = transactionToMatch.AuthorizedPersonAlias.Person;
// if the drop down does not contains the AuthorizedPerson of this transaction, add them to the drop down
// note, this can easily happen for non-check transactions
if (!ddlIndividual.Items.OfType <ListItem>().Any(a => a.Value == person.Id.ToString()))
{
ddlIndividual.Items.Add(new ListItem(person.FullNameReversed, person.Id.ToString()));
}
ddlIndividual.SelectedValue = person.Id.ToString();
}
if (ddlIndividual.Items.Count != 1)
{
badgeIndividualCount.InnerText = (ddlIndividual.Items.Count - 1).ToStringSafe();
}
else
{
ddlIndividual.Attributes["disabled"] = "disabled";
_focusControl = ppSelectNew;
}
ddlIndividual_SelectedIndexChanged(null, null);
ppSelectNew.SetValue(null);
if (transactionToMatch.TransactionDetails.Any())
{
cbTotalAmount.Text = transactionToMatch.TotalAmount.ToString();
}
else
{
cbTotalAmount.Text = string.Empty;
}
// update accountboxes
foreach (var accountBox in rptAccounts.ControlsOfTypeRecursive <CurrencyBox>())
{
accountBox.Text = string.Empty;
}
foreach (var detail in transactionToMatch.TransactionDetails)
{
var accountBox = rptAccounts.ControlsOfTypeRecursive <CurrencyBox>().Where(a => a.Attributes["data-account-id"].AsInteger() == detail.AccountId).FirstOrDefault();
if (accountBox != null)
{
accountBox.Text = detail.Amount.ToString();
}
}
tbSummary.Text = transactionToMatch.Summary;
if (transactionToMatch.Images.Any())
{
var primaryImage = transactionToMatch.Images
.OrderBy(i => i.Order)
.FirstOrDefault();
imgPrimary.ImageUrl = string.Format("~/GetImage.ashx?id={0}", primaryImage.BinaryFileId);
imgPrimary.Visible = true;
nbNoTransactionImageWarning.Visible = false;
rptrImages.DataSource = transactionToMatch.Images
.Where(i => !i.Id.Equals(primaryImage.Id))
.OrderBy(i => i.Order)
.ToList();
rptrImages.DataBind();
}
else
{
imgPrimary.Visible = false;
rptrImages.DataSource = null;
rptrImages.DataBind();
nbNoTransactionImageWarning.Visible = true;
}
}
else
{
hfTransactionId.Value = string.Empty;
}
// display how many unmatched transactions are remaining
var qryTransactionCount = financialTransactionService.Queryable();
if (batchId != 0)
{
qryTransactionCount = qryTransactionCount.Where(a => a.BatchId == batchId);
}
// get count of transactions that haven't been matched (not including the one we are currently editing)
int currentTranId = hfTransactionId.Value.AsInteger();
int matchedRemainingCount = qryTransactionCount.Count(a => a.AuthorizedPersonAliasId != null && a.Id != currentTranId);
int totalBatchItemCount = qryTransactionCount.Count();
int percentComplete = (int)Math.Round((double)(100 * matchedRemainingCount) / totalBatchItemCount);
lProgressBar.Text = string.Format(
@"<div class='progress'>
<div class='progress-bar progress-bar-info' role='progressbar' aria-valuenow='{0}' aria-valuemin='0' aria-valuemax='100' style='width: {0}%;'>
{0}%
</div>
</div>",
percentComplete);
hfBackNextHistory.Value = historyList.AsDelimited(",");
if (_focusControl == null)
{
_focusControl = rptAccounts.ControlsOfTypeRecursive <Rock.Web.UI.Controls.CurrencyBox>().FirstOrDefault();
}
}
}
/// <summary>
/// Handles the Click event of the btnNext control.
/// </summary>
/// <param name="sender">The source of the event.</param>
/// <param name="e">The <see cref="EventArgs"/> instance containing the event data.</param>
protected void btnNext_Click(object sender, EventArgs e)
{
var rockContext = new RockContext();
var financialTransactionService = new FinancialTransactionService(rockContext);
var financialTransactionDetailService = new FinancialTransactionDetailService(rockContext);
var financialPersonBankAccountService = new FinancialPersonBankAccountService(rockContext);
int txnId = hfTransactionId.Value.AsInteger();
var financialTransaction = financialTransactionService
.Queryable("AuthorizedPersonAlias.Person,ProcessedByPersonAlias.Person")
.FirstOrDefault(t => t.Id == txnId);
// set the AuthorizedPersonId (the person who wrote the check, for example) to the if the SelectNew person (if selected) or person selected in the drop down (if there is somebody selected)
int?authorizedPersonId = ppSelectNew.PersonId ?? ddlIndividual.SelectedValue.AsIntegerOrNull();
var accountNumberSecured = hfCheckMicrHashed.Value;
if (cbTotalAmount.Text.AsDecimalOrNull().HasValue&& !authorizedPersonId.HasValue)
{
nbSaveError.Text = "Transaction must be matched to a person when the amount is specified.";
nbSaveError.Visible = true;
return;
}
// if the transaction was previously matched, but user unmatched it, save it as an unmatched transaction and clear out the detail records (we don't want an unmatched transaction to have detail records)
if (financialTransaction != null &&
financialTransaction.AuthorizedPersonAliasId.HasValue &&
!authorizedPersonId.HasValue)
{
financialTransaction.AuthorizedPersonAliasId = null;
foreach (var detail in financialTransaction.TransactionDetails)
{
financialTransactionDetailService.Delete(detail);
}
rockContext.SaveChanges();
// if the transaction was unmatched, clear out the ProcessedBy fields since we didn't match the transaction and are moving on to process another transaction
MarkTransactionAsNotProcessedByCurrentUser(hfTransactionId.Value.AsInteger());
}
// if the transaction is matched to somebody, attempt to save it. Otherwise, if the transaction was previously matched, but user unmatched it, save it as an unmatched transaction
if (financialTransaction != null && authorizedPersonId.HasValue)
{
bool requiresMicr = financialTransaction.CurrencyTypeValue.Guid == Rock.SystemGuid.DefinedValue.CURRENCY_TYPE_CHECK.AsGuid();
if (requiresMicr && string.IsNullOrWhiteSpace(accountNumberSecured))
{
// should be showing already, but just in case
nbNoMicrWarning.Visible = true;
return;
}
if (cbTotalAmount.Text.AsDecimalOrNull() == null)
{
nbSaveError.Text = "Total amount must be allocated to accounts.";
nbSaveError.Visible = true;
return;
}
int?personAliasId = new PersonAliasService(rockContext).GetPrimaryAliasId(authorizedPersonId.Value);
// if this transaction has an accountnumber associated with it (in other words, it's a scanned check), ensure there is a financialPersonBankAccount record
if (!string.IsNullOrWhiteSpace(accountNumberSecured))
{
var financialPersonBankAccount = financialPersonBankAccountService.Queryable().Where(a => a.AccountNumberSecured == accountNumberSecured && a.PersonAlias.PersonId == authorizedPersonId.Value).FirstOrDefault();
if (financialPersonBankAccount == null)
{
if (personAliasId.HasValue)
{
financialPersonBankAccount = new FinancialPersonBankAccount();
financialPersonBankAccount.PersonAliasId = personAliasId.Value;
financialPersonBankAccount.AccountNumberSecured = accountNumberSecured;
var checkMicrClearText = Encryption.DecryptString(financialTransaction.CheckMicrEncrypted);
var parts = checkMicrClearText.Split('_');
if (parts.Length >= 2)
{
financialPersonBankAccount.AccountNumberMasked = parts[1].Masked();
}
financialPersonBankAccountService.Add(financialPersonBankAccount);
}
}
}
if (personAliasId.HasValue)
{
financialTransaction.AuthorizedPersonAliasId = personAliasId;
}
// just in case this transaction is getting re-edited either by the same user, or somebody else, clean out any existing TransactionDetail records
foreach (var detail in financialTransaction.TransactionDetails.ToList())
{
financialTransactionDetailService.Delete(detail);
}
foreach (var accountBox in rptAccounts.ControlsOfTypeRecursive <CurrencyBox>())
{
var amount = accountBox.Text.AsDecimalOrNull();
if (amount.HasValue && amount.Value >= 0)
{
var financialTransactionDetail = new FinancialTransactionDetail();
financialTransactionDetail.TransactionId = financialTransaction.Id;
financialTransactionDetail.AccountId = accountBox.Attributes["data-account-id"].AsInteger();
financialTransactionDetail.Amount = amount.Value;
financialTransactionDetailService.Add(financialTransactionDetail);
}
}
financialTransaction.ProcessedByPersonAliasId = this.CurrentPersonAlias.Id;
financialTransaction.ProcessedDateTime = RockDateTime.Now;
rockContext.SaveChanges();
}
else
{
// if the transaction was not matched, clear out the ProcessedBy fields since we didn't match the transaction and are moving on to process another transaction
MarkTransactionAsNotProcessedByCurrentUser(hfTransactionId.Value.AsInteger());
}
NavigateToTransaction(Direction.Next);
}
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
string connString = System.Configuration.ConfigurationManager.ConnectionStrings["TellerContext"].ConnectionString;
string sqlStmt = @"
SELECT TOP 100000 * FROM
(
SELECT DISTINCT CheckAcctNo, RoutingNo
FROM General_Fund.dbo.Docs
WHERE CheckAcctNo <> '' AND RoutingNo <> ''
UNION
SELECT DISTINCT CheckAcctNo, RoutingNo
FROM Building_Fund.dbo.Docs
WHERE CheckAcctNo <> '' AND RoutingNo <> ''
) A
";
DataTable dtTellerAcctRtng = new DataTable();
using (SqlConnection conn = new SqlConnection(connString))
{
using (SqlCommand comm = new SqlCommand(sqlStmt, conn))
{
conn.Open();
using (SqlDataReader dr = comm.ExecuteReader())
{
dtTellerAcctRtng.Load(dr);
}
}
}
dtTellerAcctRtng.Columns.Add("Hashed", typeof(String));
foreach (DataRow dr in dtTellerAcctRtng.Rows)
{
dr["Hashed"] = EncodeAccountNumber(dr["RoutingNo"].ToString(), dr["CheckAcctNo"].ToString());
}
var qry = dtTellerAcctRtng.AsEnumerable()
.Select(i => new {
RoutingNo = i.Field <string>("RoutingNo"),
CheckAcctNo = i.Field <string>("CheckAcctNo"),
Hashed = i.Field <string>("Hashed")
});
RockContext rockContext = new RockContext();
var fpbaService = new FinancialPersonBankAccountService(rockContext);
var lstRockAcctRtng = fpbaService.Queryable()
.Select(a => new { a.AccountNumberSecured, a.PersonAliasId })
.ToList();
var matchingRows = lstRockAcctRtng
.Join(qry, x => x.AccountNumberSecured, y => y.Hashed, (x, y) => new { y.RoutingNo, y.CheckAcctNo, x.AccountNumberSecured, x.PersonAliasId })
.GroupBy(g => new { g.RoutingNo, g.CheckAcctNo, g.AccountNumberSecured })
.Select(s => new { s.Key.RoutingNo, s.Key.CheckAcctNo, s.Key.AccountNumberSecured, PersonAliasIds = string.Join("|", s.Select(i => i.PersonAliasId)) });
DataTable dt = new DataTable();
dt.TableName = "BankAcctRtng";
dt.Columns.Add("AccountNumberSecured", typeof(String));
dt.Columns.Add("RoutingNo", typeof(String));
dt.Columns.Add("CheckAcctNo", typeof(String));
dt.Columns.Add("PersonAliasIds", typeof(String));
foreach (var row in matchingRows)
{
dt.Rows.Add(row.AccountNumberSecured, row.RoutingNo, row.CheckAcctNo, row.PersonAliasIds);
}
try {
string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["RockContext"].ConnectionString;
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
{
foreach (DataColumn c in dt.Columns)
{
bulkCopy.ColumnMappings.Add(c.ColumnName, c.ColumnName);
}
bulkCopy.DestinationTableName = dt.TableName;
try
{
bulkCopy.WriteToServer(dt);
lblExportStatus.Text = "Rows were appended to BankAcctRtng table.";
}
catch (Exception ex)
{
lblExportStatus.Text = "Table was not created: " + ex.Message;
}
}
}
/*
* string path = Server.MapPath( "/Content" ) + "\\BankAcctRtng.csv";
* File.WriteAllText( path, String.Format( "{0},{1},{2}", "RoutingNo", "CheckAcctNo", "PersonAliasIds\r\n" ) );
* File.AppendAllLines( path, matchingRows.Select( x => String.Format( "{0},{1},{2}", x.RoutingNo, x.CheckAcctNo, x.PersonAliasIds ) ) );
* lblExportStatus.Text = "File exported at " + path;
*/
}
catch (Exception ex) {
lblExportStatus.Text = "File was not exported: " + ex.Message;
}
}
}
