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); }