private BankExportSet GetBatchOfUncompletedBankExports()
{
Query uncompletedBankExportsQuery = new Query(new Or(new Q(BankExport.Columns.Status, BankExport.Statuses.Added),
new Q(BankExport.Columns.Status, BankExport.Statuses.AwaitingConfirmation),
new Q(BankExport.Columns.Status, BankExport.Statuses.Failed)));
BankExportSet uncompletedBankExports = new BankExportSet(uncompletedBankExportsQuery);
return uncompletedBankExports;
}
private void ValidateTransfer()
{
if (this.Transfer != null)
{
if (this.Type == Types.ExternalBACSRefundToPromoter || this.Type == Types.ExternalBACSTicketFundsToPromoter || this.Type == Types.InternalTransferTicketFundsToPromoter)
{
if (this.Transfer.Type != Transfer.TransferTypes.Refund)
throw new DsiUserFriendlyException("Validation error on " + this.PaymentRef + ". " + this.Transfer.TypeAndK + " is not a refund.");
if (this.Transfer.Method != Transfer.Methods.BankTransfer)
throw new DsiUserFriendlyException("Validation error on " + this.PaymentRef + ". " + this.Transfer.TypeAndK + " is not a bank transfer.");
}
if (this.Type == Types.ExternalBACSTicketFundsToPromoter || this.Type == Types.InternalTransferTicketFundsToPromoter)
{
if (this.Transfer.TransferRefunded == null || this.Transfer.TransferRefunded.Method != Transfer.Methods.TicketSales)
throw new DsiUserFriendlyException("Validation error on " + this.PaymentRef + ". " + this.Transfer.TypeAndK + " is not paying out for ticket funds.");
}
// Sum all bank exports for this transfer and ensure that it doesnt exceed the amount of the transfer.
Query bookingFeeBankExportQuery = new Query(new And(new Q(BankExport.Columns.TransferK, this.Transfer.K),
new Q(BankExport.Columns.Status, QueryOperator.NotEqualTo, BankExport.Statuses.Cancelled)));
BankExportSet bankExports = new BankExportSet(bookingFeeBankExportQuery);
decimal amount = 0;
if (this.Type == Types.InternalTransferTicketFundsUsed)
{
foreach (BankExport be in bankExports)
{
if (be.BankAccountNumber == Vars.DSI_BANK_ACCOUNT_NUMBER && be.BankAccountSortCode == Vars.DSI_BANK_SORT_CODE)
amount += be.Amount;
else
amount -= be.Amount;
}
amount = Math.Round(amount, 2);
if (amount < 0 || amount > this.Transfer.Amount)
throw new DsiUserFriendlyException("Validation error on " + this.PaymentRef + ". BankExport summation does not match amount for " + this.Transfer.TypeAndK);
}
else
{
foreach (BankExport be in bankExports)
{
amount -= be.Amount;
}
amount = Math.Round(amount, 2);
if (amount > 0 || amount < this.Transfer.Amount)
throw new DsiUserFriendlyException("Validation error on " + this.PaymentRef + ". BankExport summation does not match amount for " + this.Transfer.TypeAndK);
}
}
else
{
throw new DsiUserFriendlyException("Validation error on " + this.PaymentRef + ". Cannot validate a transfer that is null.");
}
}
public static string BarclaysExport(BankExportSet bankExports)
{
StringBuilder sb = new StringBuilder();
if (bankExports.Count > 0)
{
string batchRef = GetNextBatchRef();
foreach (BankExport be in bankExports)
{
if (be.Type == Types.ExternalBACSTicketFundsToPromoter || be.Type == Types.ExternalBACSRefundToPromoter)
{
be.Validate();
be.BatchRef = batchRef;
be.OutputDateTime = DateTime.Now;
be.UpdateStatus(Statuses.AwaitingConfirmation);
sb.Append("\"" + be.BankAccountSortCode + "\",");
sb.Append("\"" + be.BeneficiaryName.StripAllNonAlphaNumeric().Truncate(18) + "\",");
sb.Append("\"" + be.BankAccountNumber + "\",");
sb.Append("\"" + be.Amount.ToString("0.00") + "\",");
sb.Append("\"" + be.PaymentRef + "\",");
sb.Append("\"99\"\r\n");
}
else
throw new Exception("Invalid BankExport type!!!");
}
}
return sb.ToString();
}
public static DateTime GetDateOfLastNonTicketFundsBankExport()
{
// Get last bacth ref for today
// increment the batch #
DateTime lastNonTicketFunds = new DateTime(2007, 8, 13);
Query getLastNonTicketFundsQuery = new Query(new And(new Q(BankExport.Columns.Type, BankExport.Types.InternalTransferNonTicketFunds)));
getLastNonTicketFundsQuery.Columns = new ColumnSet();
getLastNonTicketFundsQuery.ExtraSelectElements.Add("LastNonTicketFunds", "MAX([BankExport].[AddedDateTime])");
BankExportSet bankExports = new BankExportSet(getLastNonTicketFundsQuery);
if (bankExports.Count > 0 && bankExports[0].ExtraSelectElements["LastNonTicketFunds"] != DBNull.Value)
lastNonTicketFunds = Convert.ToDateTime(bankExports[0].ExtraSelectElements["LastNonTicketFunds"]);
return new DateTime(lastNonTicketFunds.Year, lastNonTicketFunds.Month, lastNonTicketFunds.Day);
}
public static string GetNextBatchRef()
{
// Get last bacth ref for today
// increment the batch #
char batchNumber = 'A';
string batchRef = "";
Query getLastBatchRefQuery = new Query(new Q(BankExport.Columns.Status, BankExport.Statuses.Successful));
getLastBatchRefQuery.Columns = new ColumnSet();
getLastBatchRefQuery.ExtraSelectElements.Add("LastBatchRef", "MAX([BankExport].[BatchRef])");
BankExportSet bankExports = new BankExportSet(getLastBatchRefQuery);
if (bankExports.Count > 0 && bankExports[0].ExtraSelectElements["LastBatchRef"] != DBNull.Value)
batchRef = bankExports[0].ExtraSelectElements["LastBatchRef"].ToString();
if(batchRef.IndexOf(DateTime.Today.ToString("yyyyMMdd")) >= 0)
{
batchNumber = batchRef[8];
batchNumber++;
}
return DateTime.Today.ToString("yyyyMMdd") + batchNumber.ToString();
}
private void ValidateNonTicketFunds()
{
// Validate the summation is correct
Query nonTicketCardMoneyQuery = new Query(new And(new Q(Transfer.Columns.Method, Transfer.Methods.Card),
new Q(Transfer.Columns.Status, Transfer.StatusEnum.Success),
new Q(Transfer.Columns.DateTimeComplete, QueryOperator.GreaterThanOrEqualTo, Utilities.GetStartOfDay(this.ReferenceDateTime)),
new Q(Transfer.Columns.DateTimeComplete, QueryOperator.LessThan, Utilities.GetStartOfDay(this.ReferenceDateTime).AddDays(1))));
nonTicketCardMoneyQuery.ExtraSelectElements.Add("SumAmounts", "SUM([Transfer].[Amount])");
nonTicketCardMoneyQuery.ExtraSelectElements.Add("TheDate", "dateadd(day, 0, datediff(day, 0, [Transfer].[DateTimeComplete]))");
nonTicketCardMoneyQuery.GroupBy = new GroupBy("dateadd(day, 0, datediff(day, 0, [Transfer].[DateTimeComplete]))");
nonTicketCardMoneyQuery.Columns = new ColumnSet();
Query ticketCardMoneyQuery = new Query(new And(new Q(InvoiceItem.Columns.Type, InvoiceItem.Types.EventTickets),
new Q(InvoiceItem.Columns.RevenueStartDate, QueryOperator.GreaterThanOrEqualTo, Utilities.GetStartOfDay(this.ReferenceDateTime)),
new Q(InvoiceItem.Columns.RevenueStartDate, QueryOperator.LessThan, Utilities.GetStartOfDay(this.ReferenceDateTime).AddDays(1))));
ticketCardMoneyQuery.ExtraSelectElements.Add("SumAmounts", "SUM([InvoiceItem].[Total])");
ticketCardMoneyQuery.ExtraSelectElements.Add("TheDate", "dateadd(day, 0, datediff(day, 0, [InvoiceItem].[RevenueStartDate]))");
ticketCardMoneyQuery.GroupBy = new GroupBy("dateadd(day, 0, datediff(day, 0, [InvoiceItem].[RevenueStartDate]))");
ticketCardMoneyQuery.Columns = new ColumnSet();
int signMultiplier = 1;
if (this.BankAccountNumber == Vars.DSI_BANK_ACCOUNT_NUMBER && this.BankAccountSortCode == Vars.DSI_BANK_SORT_CODE)
{
nonTicketCardMoneyQuery.QueryCondition = new And(nonTicketCardMoneyQuery.QueryCondition,
new Q(Transfer.Columns.Amount, QueryOperator.GreaterThan, 0));
ticketCardMoneyQuery.QueryCondition = new And(ticketCardMoneyQuery.QueryCondition,
new Q(InvoiceItem.Columns.Total, QueryOperator.GreaterThan, 0));
}
else
{
signMultiplier = -1;
nonTicketCardMoneyQuery.QueryCondition = new And(nonTicketCardMoneyQuery.QueryCondition,
new Q(Transfer.Columns.Amount, QueryOperator.LessThan, 0));
ticketCardMoneyQuery.QueryCondition = new And(ticketCardMoneyQuery.QueryCondition,
new Q(InvoiceItem.Columns.Total, QueryOperator.LessThan, 0));
}
TransferSet nonTicketCardMoneyTransfers = new TransferSet(nonTicketCardMoneyQuery);
InvoiceItemSet ticketCardMoneyItems = new InvoiceItemSet(ticketCardMoneyQuery);
decimal sumAmounts = 0;
if (nonTicketCardMoneyTransfers.Count > 0 && nonTicketCardMoneyTransfers[0].ExtraSelectElements["SumAmounts"] != DBNull.Value)
sumAmounts = Convert.ToDecimal(nonTicketCardMoneyTransfers[0].ExtraSelectElements["SumAmounts"]);
if (ticketCardMoneyItems.Count > 0 && ticketCardMoneyItems[0].ExtraSelectElements["SumAmounts"] != DBNull.Value)
sumAmounts -= Convert.ToDecimal(ticketCardMoneyItems[0].ExtraSelectElements["SumAmounts"]);
if (Math.Round(sumAmounts, 2) != Math.Round(signMultiplier * this.Amount, 2))
throw new DsiUserFriendlyException("Validation error on " + this.PaymentRef + ". Non ticket funds do not add up.");
// Validate that no other Bank Export covers non ticket funds for that day
Query nonTicketFundsBankExportQuery = new Query(new And(new Q(BankExport.Columns.Type, BankExport.Types.InternalTransferNonTicketFunds),
new Q(BankExport.Columns.BankAccountNumber, this.BankAccountNumber),
new Q(BankExport.Columns.BankAccountSortCode, this.BankAccountSortCode),
new Q(BankExport.Columns.ReferenceDateTime, QueryOperator.GreaterThanOrEqualTo, Utilities.GetStartOfDay(this.ReferenceDateTime)),
new Q(BankExport.Columns.ReferenceDateTime, QueryOperator.LessThan, Utilities.GetStartOfDay(this.ReferenceDateTime).AddDays(1))));
BankExportSet bankExports = new BankExportSet(nonTicketFundsBankExportQuery);
if (!((this.K == 0 && bankExports.Count == 0) || (this.K > 0 && bankExports.Count == 1)))
throw new DsiUserFriendlyException("Validation error on " + this.PaymentRef + ". Conflict with bank export records for non ticket funds for " + this.ReferenceDateTime.ToString("ddd dd/MM/yyyy"));
}
private void SumBankExports(BankExportSet bankExports)
{
//decimal clientToCurrent = 0;
//decimal currentToClient = 0;
//decimal clientToPromoter = 0;
decimal currentToPromoter = 0;
if (bankExports != null)
{
bankExports.Reset();
foreach (Bobs.BankExport be in bankExports)
{
currentToPromoter += be.Amount;
//if (be.Type == Bobs.BankExport.Types.ExternalBACSRefundToPromoter || be.Type == Bobs.BankExport.Types.InternalTransferRefundToPromoter)
// currentToPromoter += be.Amount;
//else if (be.Type == Bobs.BankExport.Types.ExternalBACSTicketFundsToPromoter || be.Type == Bobs.BankExport.Types.InternalTransferTicketFundsToPromoter)
// clientToPromoter += be.Amount;
//else
//{
// if (be.BankAccountNumber == Vars.DSI_CLIENT_BANK_ACCOUNT_NUMBER && be.BankAccountSortCode == Vars.DSI_CLIENT_BANK_SORT_CODE)
// currentToClient += be.Amount;
// else
// clientToCurrent += be.Amount;
//}
}
}
//this.FundsClientToCurrentLabel.Text = clientToCurrent.ToString("c");
//this.FundsClientToPromoterLabel.Text = clientToPromoter.ToString("c");
//this.FundsCurrentToClientLabel.Text = currentToClient.ToString("c");
this.FundsCurrentToPromoterLabel.Text = currentToPromoter.ToString("c");
}
private void LoadGridViewOfSearchBankExport()
{
Query searchBankExportQuery = new Query();
List<Q> QueryConditionList = new List<Q>();
searchBankExportQuery.OrderBy = new OrderBy(new OrderBy(Bobs.BankExport.Columns.Type, OrderBy.OrderDirection.Descending), new OrderBy(Bobs.BankExport.Columns.ReferenceDateTime), new OrderBy(Bobs.BankExport.Columns.K, OrderBy.OrderDirection.Descending));
if (this.BankExportRadioButtonList.SelectedValue == Convert.ToInt32(Options.NextBatch).ToString())
{
QueryConditionList.Add(new Or(new Q(Bobs.BankExport.Columns.Status, Bobs.BankExport.Statuses.Added),
new Q(Bobs.BankExport.Columns.Status, Bobs.BankExport.Statuses.AwaitingConfirmation),
new Q(Bobs.BankExport.Columns.Status, Bobs.BankExport.Statuses.Failed)));
}
else if (this.BankExportRadioButtonList.SelectedValue == Convert.ToInt32(Options.AwaitingConfirmation).ToString())
{
QueryConditionList.Add(new Q(Bobs.BankExport.Columns.Status, Bobs.BankExport.Statuses.AwaitingConfirmation));
}
else
{
searchBankExportQuery.OrderBy = new OrderBy(new OrderBy(Bobs.BankExport.Columns.ReferenceDateTime), new OrderBy(Bobs.BankExport.Columns.K, OrderBy.OrderDirection.Descending));
if (this.uiPromoterHtmlAutoComplete.Value != null && !uiPromoterHtmlAutoComplete.Value.Equals(""))
QueryConditionList.Add(new Q(Bobs.BankExport.Columns.PromoterK, Convert.ToInt32(uiPromoterHtmlAutoComplete.Value)));
if (this.StatusDropDownList.SelectedValue != "")
{
QueryConditionList.Add(new Q(Bobs.BankExport.Columns.Status, Convert.ToInt32(this.StatusDropDownList.SelectedValue)));
}
if (this.TypeDropDownList.SelectedValue != "")
QueryConditionList.Add(new Q(Bobs.BankExport.Columns.Type, Convert.ToInt32(this.TypeDropDownList.SelectedValue)));
if (this.ExportDateCal.Date != DateTime.MinValue)
{
QueryConditionList.Add(new And(new Q(Bobs.BankExport.Columns.OutputDateTime, QueryOperator.GreaterThanOrEqualTo, this.ExportDateCal.Date),
new Q(Bobs.BankExport.Columns.OutputDateTime, QueryOperator.LessThan, this.ExportDateCal.Date.AddDays(1))));
}
if (this.BatchRefTextBox.Text.Trim().Length > 0)
QueryConditionList.Add(new Q(Bobs.BankExport.Columns.BatchRef, QueryOperator.TextContains, this.BatchRefTextBox.Text.Trim()));
}
searchBankExportQuery.QueryCondition = new And(QueryConditionList.ToArray());
BankExportSet searchBankExports = new BankExportSet(searchBankExportQuery);
if (this.BankExportRadioButtonList.SelectedValue == Convert.ToInt32(Options.NextBatch).ToString())
{
try
{
foreach (Bobs.BankExport be in searchBankExports)
{
be.Validate();
}
}
catch (Exception ex)
{
SearchBankExportHeader.InnerHtml = ex.Message;
SearchBankExportHeader.Style["color"] = "Red";
BankExportLinkP.Visible = false;
}
searchBankExports.Reset();
}
this.SearchBankExportGridView.DataSource = searchBankExports;
this.SearchBankExportGridView.DataBind();
SearchResultsMessageLabel.Visible = searchBankExports.Count == 0;
if (searchBankExports.Count == 0)
{
SearchResultsMessageLabel.Text = "<br>* Zero search results returned.";
}
SumBankExports(searchBankExports);
}
