private void GetResults()
{
Query searchCardTransferQuery = new Query();
List<Q> QueryConditionList = new List<Q>();
searchCardTransferQuery.OrderBy = new OrderBy("dateadd(day, 0, datediff(day, 0, [Transfer].[DateTimeComplete]))");
searchCardTransferQuery.GroupBy = new GroupBy("dateadd(day, 0, datediff(day, 0, [Transfer].[DateTimeComplete]))");
searchCardTransferQuery.ExtraSelectElements.Add("SumAmount", "SUM([Transfer].[Amount])");
searchCardTransferQuery.ExtraSelectElements.Add("CountTransfers", "COUNT([Transfer].[K])");
searchCardTransferQuery.ExtraSelectElements.Add("Date", "dateadd(day, 0, datediff(day, 0, [Transfer].[DateTimeComplete]))");
searchCardTransferQuery.Columns = new ColumnSet();
QueryConditionList.Add(new Q(Bobs.Transfer.Columns.Method, Bobs.Transfer.Methods.Card));
QueryConditionList.Add(new Q(Bobs.Transfer.Columns.Status, Bobs.Transfer.StatusEnum.Success));
if (this.FromDateCal.Date != DateTime.MinValue)
QueryConditionList.Add(new Q(Bobs.Transfer.Columns.DateTimeComplete, QueryOperator.GreaterThanOrEqualTo, this.FromDateCal.Date));
if (this.ToDateCal.Date != DateTime.MinValue)
QueryConditionList.Add(new Q(Bobs.Transfer.Columns.DateTimeComplete, QueryOperator.LessThan, this.ToDateCal.Date.AddDays(1)));
searchCardTransferQuery.QueryCondition = new And(QueryConditionList.ToArray());
TransferSet searchCardTransfers = new TransferSet(searchCardTransferQuery);
this.CardnetAccountGridView.DataSource = searchCardTransfers;
this.CardnetAccountGridView.DataBind();
SumAmounts(searchCardTransfers);
}
private void SumAmounts(TransferSet searchCardTransfers)
{
decimal sum = 0;
int count = 0;
searchCardTransfers.Reset();
foreach (Transfer t in searchCardTransfers)
{
count += (int)t.ExtraSelectElements["CountTransfers"];
sum += (decimal)t.ExtraSelectElements["SumAmount"];
}
SumAccountLabel.Text = sum.ToString("c");
SumTransferCountLabel.Text = count.ToString();
}
/// <summary>
/// This calculates the total balance of the user's account based on (Invoices + Credit) totals - successful Transfer amounts.
/// Note: Pending refunds are considered the same as successful refunds
/// </summary>
/// <returns></returns>
public decimal GetBalance()
{
Query qInvoiceBalance = new Query();
qInvoiceBalance.Columns = new ColumnSet();
qInvoiceBalance.ExtraSelectElements.Add("sum", "SUM([Invoice].[Total])");
qInvoiceBalance.QueryCondition = new And(new Q(Invoice.Columns.UsrK, this.K),
new Q(Invoice.Columns.PromoterK, 0));
InvoiceSet isInvoiceBalance = new InvoiceSet(qInvoiceBalance);
Query qTransferBalance = new Query();
qTransferBalance.Columns = new ColumnSet();
qTransferBalance.ExtraSelectElements.Add("sum", "SUM(ISNULL([Transfer].[Amount], 0))");
qTransferBalance.QueryCondition = new And(new Or(new Q(Transfer.Columns.Status, Transfer.StatusEnum.Success),
new And(new Q(Transfer.Columns.Status, Transfer.StatusEnum.Pending),
new Q(Transfer.Columns.Type, Transfer.TransferTypes.Refund))),
new Q(Transfer.Columns.UsrK, this.K),
new Q(Transfer.Columns.PromoterK, 0));
TransferSet tsTransferBalance = new TransferSet(qTransferBalance);
decimal invoiceBalance = 0;
decimal transferBalance = 0;
if (isInvoiceBalance[0].ExtraSelectElements["sum"] != DBNull.Value)
invoiceBalance = Convert.ToDecimal(isInvoiceBalance[0].ExtraSelectElements["sum"]);
if (tsTransferBalance[0].ExtraSelectElements["sum"] != DBNull.Value)
transferBalance = Convert.ToDecimal(tsTransferBalance[0].ExtraSelectElements["sum"]);
return Math.Round(transferBalance - invoiceBalance, 2);
}
private List<PromoterAccountItem> GetPromoterAccountItemList(DateTime startDate, DateTime endDate, IBob referenceBob)
{
int startingRecord = this.RecordsPerPage * (this.pageNumber -1);
List<PromoterAccountItem> promoterAccountItems = new List<PromoterAccountItem>();
InvoiceSet invoices;
TransferSet transfers;
Query invoiceSummaryQuery = new Query();
Query transferSummaryQuery = new Query();
// Descending date order: Lastest first, oldest last
// Replacing CreatedDateTime with TaxDateTime, as per Gee's request for OASIS v1.5
// Order By CreatedDateTime instead of TaxDateTime, as per Dave's request 7/2/07
invoiceSummaryQuery.OrderBy = new OrderBy(Invoice.Columns.CreatedDateTime, OrderBy.OrderDirection.Descending);
invoiceSummaryQuery.Paging.RecordsPerPage = 0;
invoiceSummaryQuery.Paging.RequestedPage = 1;
transferSummaryQuery.OrderBy = new OrderBy("IsNull([Transfer].[DateTimeComplete], [Transfer].[DateTimeCreated]) DESC");
transferSummaryQuery.Paging.RecordsPerPage = 0;
transferSummaryQuery.Paging.RequestedPage = 1;
if (referenceBob != null)
{
if (referenceBob is Invoice)
{
Invoice invoice = (Invoice)referenceBob;
if (invoice.Type.Equals(Invoice.Types.Invoice))
{
invoiceSummaryQuery.QueryCondition = new Q(InvoiceCredit.Columns.InvoiceK, invoice.K);
invoiceSummaryQuery.TableElement = new Join(Invoice.Columns.K, InvoiceCredit.Columns.CreditInvoiceK);
}
else
{
invoiceSummaryQuery.QueryCondition = new Q(InvoiceCredit.Columns.CreditInvoiceK, invoice.K);
invoiceSummaryQuery.TableElement = new Join(Invoice.Columns.K, InvoiceCredit.Columns.InvoiceK);
}
// Replacing TaxDateTime with CreatedDateTime, as per Dave's request 7/2/07
invoiceSummaryQuery.Columns = new ColumnSet(Invoice.Columns.K, Invoice.Columns.Paid, Invoice.Columns.PromoterK, Invoice.Columns.CreatedDateTime, Invoice.Columns.DueDateTime,
Invoice.Columns.Total, Invoice.Columns.Type, InvoiceCredit.Columns.Amount);
invoiceSummaryQuery.Paging.RecordsPerPage = 0;
invoiceSummaryQuery.Paging.RequestedPage = 1;
transferSummaryQuery.QueryCondition = new And(new Q(InvoiceTransfer.Columns.InvoiceK, invoice.K),
new Q(Transfer.Columns.Amount, QueryOperator.NotEqualTo, 0),
new Or(new Q(Transfer.Columns.Status, Transfer.StatusEnum.Success),
new And(new Q(Transfer.Columns.Type, Transfer.TransferTypes.Refund),
new Q(Transfer.Columns.Status, Transfer.StatusEnum.Pending))));
transferSummaryQuery.TableElement = new Join(Transfer.Columns.K, InvoiceTransfer.Columns.TransferK);
transferSummaryQuery.Columns = new ColumnSet(Transfer.Columns.K, Transfer.Columns.Status, Transfer.Columns.PromoterK, Transfer.Columns.DateTimeComplete, Transfer.Columns.DateTimeCreated, Transfer.Columns.Method,
Transfer.Columns.TransferRefundedK, Transfer.Columns.Amount, Transfer.Columns.Type, InvoiceTransfer.Columns.Amount);
transferSummaryQuery.Paging.RecordsPerPage = 0;
transferSummaryQuery.Paging.RequestedPage = 1;
}
else if (referenceBob is Transfer)
{
Transfer transfer = (Transfer)referenceBob;
if (transfer.Type.Equals(Transfer.TransferTypes.Payment))
{
transferSummaryQuery.QueryCondition = new Q(Transfer.Columns.TransferRefundedK, transfer.K);
}
else
{
transferSummaryQuery.QueryCondition = new Q(Transfer.Columns.K, transfer.TransferRefundedK);
}
transferSummaryQuery.Paging.RecordsPerPage = 0;
transferSummaryQuery.Paging.RequestedPage = 1;
invoiceSummaryQuery.QueryCondition = new Q(InvoiceTransfer.Columns.TransferK, transfer.K);
invoiceSummaryQuery.TableElement = new Join(Invoice.Columns.K, InvoiceTransfer.Columns.InvoiceK);
// Replacing TaxDateTime with CreatedDateTime, as per Dave's request 7/2/07
invoiceSummaryQuery.Columns = new ColumnSet(Invoice.Columns.K, Invoice.Columns.Paid, Invoice.Columns.PromoterK, Invoice.Columns.CreatedDateTime, Invoice.Columns.DueDateTime,
Invoice.Columns.Total, Invoice.Columns.Type, InvoiceTransfer.Columns.Amount);
invoiceSummaryQuery.Paging.RecordsPerPage = 0;
invoiceSummaryQuery.Paging.RequestedPage = 1;
}
}
else
{
Q InvoiceQ = new Q(Invoice.Columns.PromoterK, CurrentPromoter.K);
// Replacing TaxDateTime with CreatedDateTime, as per Dave's request 7/2/07
Q DateQ = new And(new Q(Invoice.Columns.CreatedDateTime, QueryOperator.GreaterThanOrEqualTo, startDate),
new Q(Invoice.Columns.CreatedDateTime, QueryOperator.LessThanOrEqualTo, endDate));
// Outstanding only applies to Invoices
if (this.FilterDropDownList.SelectedValue.Equals(Invoice.Statuses.Outstanding.ToString() + " Invoices"))
{
invoiceSummaryQuery.QueryCondition = new And(InvoiceQ, DateQ, new Q(Invoice.Columns.Paid, false), new Q(Invoice.Columns.Type, Invoice.Types.Invoice));//, new Q(Invoice.Columns.Type, Invoice.Types.Invoice));
transferSummaryQuery.QueryCondition = new Q(Transfer.Columns.K, -1);
}
else
{
invoiceSummaryQuery.QueryCondition = new And(InvoiceQ, DateQ);
transferSummaryQuery.QueryCondition = new And(new Q(Transfer.Columns.PromoterK, CurrentPromoter.K),
new Q(Transfer.Columns.Amount, QueryOperator.NotEqualTo, 0),
new Or(new And(new Q(Transfer.Columns.Status, Transfer.StatusEnum.Success),
new Q(Transfer.Columns.DateTimeComplete, QueryOperator.GreaterThanOrEqualTo, startDate),
new Q(Transfer.Columns.DateTimeComplete, QueryOperator.LessThanOrEqualTo, endDate)),
new And(new Q(Transfer.Columns.Type, Transfer.TransferTypes.Refund),
new Q(Transfer.Columns.DateTimeCreated, QueryOperator.GreaterThanOrEqualTo, startDate),
new Q(Transfer.Columns.DateTimeCreated, QueryOperator.LessThanOrEqualTo, endDate),
new Or(new Q(Transfer.Columns.Status, Transfer.StatusEnum.Pending),
new Q(Transfer.Columns.Status, Transfer.StatusEnum.Success)))));
}
}
invoiceSummaryQuery.FillMaxRecords = this.pageNumber * this.RecordsPerPage + 1;
transferSummaryQuery.FillMaxRecords = this.pageNumber * this.RecordsPerPage + 1;
invoices = new InvoiceSet(invoiceSummaryQuery);
transfers = new TransferSet(transferSummaryQuery);
if (referenceBob == null)
{
if (this.GetAllOutstanding)
{
this.PaginationPanel.Visible = false;
}
else
{
this.PaginationPanel.Visible = true;
this.PrevPageLinkButton.Enabled = this.pageNumber != 1;
this.NextPageLinkButton.Enabled = invoices.Count + transfers.Count > this.pageNumber * this.RecordsPerPage;
}
}
// Merge into one Bob List
int invoiceCounter = 0;
int transferCounter = 0;
while (startingRecord > invoices.Count + transfers.Count)
{
startingRecord -= this.RecordsPerPage;
}
int endingRecord = startingRecord + this.RecordsPerPage;
while ((invoices.Count > invoiceCounter || transfers.Count > transferCounter) && invoiceCounter + transferCounter < endingRecord)
{
if (invoices.Count > invoiceCounter && transfers.Count > transferCounter)
{
// Replacing TaxDateTime with CreatedDateTime, as per Dave's request 7/2/07
if (invoices[invoiceCounter].CreatedDateTime > (transfers[transferCounter].DateTimeComplete > transfers[transferCounter].DateTimeCreated ? transfers[transferCounter].DateTimeComplete : transfers[transferCounter].DateTimeCreated))
{
if (invoiceCounter + transferCounter >= startingRecord)
{
if (referenceBob != null)
{
FixFiguresForSubItem(invoices[invoiceCounter], referenceBob);
}
promoterAccountItems.Add(new PromoterAccountItem(invoices[invoiceCounter]));
}
invoiceCounter++;
}
else
{
if (invoiceCounter + transferCounter >= startingRecord)
{
if (referenceBob != null)
{
FixFiguresForSubItem(transfers[transferCounter], referenceBob);
}
promoterAccountItems.Add(new PromoterAccountItem(transfers[transferCounter]));
}
transferCounter++;
}
}
else if (invoices.Count > invoiceCounter)
{
if (invoiceCounter + transferCounter >= startingRecord)
{
if (referenceBob != null)
{
FixFiguresForSubItem(invoices[invoiceCounter], referenceBob);
}
promoterAccountItems.Add(new PromoterAccountItem(invoices[invoiceCounter]));
}
invoiceCounter++;
}
else
{
if (invoiceCounter + transferCounter >= startingRecord)
{
if (referenceBob != null)
{
FixFiguresForSubItem(transfers[transferCounter], referenceBob);
}
promoterAccountItems.Add(new PromoterAccountItem(transfers[transferCounter]));
}
transferCounter++;
}
}
return promoterAccountItems;
}
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 SetupAvailableTransfersDropDownList()
{
if (this.InvoiceK == 0 || CurrentInvoice.Paid == false)
{
LinkButton newTransferLinkButton = (LinkButton)InvoiceTransferGridView.FooterRow.FindControl("AddLinkButton");
TextBox newAmountTextBox = (TextBox)InvoiceTransferGridView.FooterRow.FindControl("NewAmountTextBox");
newTransferLinkButton.Visible = true;
Q transfersForPromotersUsersQueryConditions = null;
//if(PromoterDbCombo.Value != "" && UserDbCombo.Value != "")
// transfersForPromotersUsersQueryConditions = new Or(new Q(Transfer.Columns.PromoterK, Convert.ToInt32(PromoterDbCombo.Value)), new Q(Transfer.Columns.UsrK, Convert.ToInt32(UserDbCombo.Value)));
//else if(PromoterDbCombo.Value != "")
// transfersForPromotersUsersQueryConditions = new Q(Transfer.Columns.PromoterK, Convert.ToInt32(PromoterDbCombo.Value));
//else if(UserDbCombo.Value != "")
// transfersForPromotersUsersQueryConditions = new Q(Transfer.Columns.UsrK, Convert.ToInt32(UserDbCombo.Value));
if (this.uiPromotersAutoComplete.Value != "")
transfersForPromotersUsersQueryConditions = new Q(Transfer.Columns.PromoterK, Convert.ToInt32(this.uiPromotersAutoComplete.Value));
Query availableTransfersQuery = new Query();
string queryForAvailableTransfers = @"Round([Transfer].[Amount],2) > (CASE WHEN ((SELECT Sum([InvoiceTransfer].[Amount]) FROM [InvoiceTransfer] WHERE [InvoiceTransfer].[TransferK] = [Transfer].[K]) IS NULL) THEN 0 ELSE Round((SELECT Sum([InvoiceTransfer].[Amount]) FROM [InvoiceTransfer] WHERE [InvoiceTransfer].[TransferK] = [Transfer].[K]),2) END)";
List<ListItem> availableTransferListItems = new List<ListItem>();
decimal promoterAvailableCredit = 0;
if (this.uiPromotersAutoComplete.Value.Length > 0)
{
promoterAvailableCredit = CurrentPromoter.GetAvailableMoney() + CurrentPromoter.CreditLimit;
}
if (transfersForPromotersUsersQueryConditions != null)
{
availableTransfersQuery.QueryCondition = new And(
new StringQueryCondition(queryForAvailableTransfers),
transfersForPromotersUsersQueryConditions,
new Q(Transfer.Columns.Status, QueryOperator.NotEqualTo, Transfer.StatusEnum.Cancelled),
new Q(Transfer.Columns.Status, QueryOperator.NotEqualTo, Transfer.StatusEnum.Failed),
new Q(Transfer.Columns.Type, Transfer.TransferTypes.Payment),
new Q(Transfer.Columns.IsFullyApplied, false));
availableTransfersQuery.Columns = new ColumnSet(Transfer.Columns.K, Transfer.Columns.Amount, Transfer.Columns.Method, Transfer.Columns.DateTimeCreated, Transfer.Columns.Status);
//TransferSet availableTransfers = new TransferSet();
//if (promoterAvailableMoney > 0)
//{
TransferSet availableTransfers = new TransferSet(availableTransfersQuery);
//}
decimal pendingTransferMoney = 0;
for (int i = 0; i < availableTransfers.Count; i++)
{
decimal amountRemaining = availableTransfers[i].AmountRemaining();
string pending = availableTransfers[i].Status.Equals(Transfer.StatusEnum.Pending) ? " (P)" : "";
if (amountRemaining > 0)
{
availableTransferListItems.Add(new ListItem("K=" + availableTransfers[i].K.ToString() + " | "
+ amountRemaining.ToString("c") + " | "
+ Utilities.CamelCaseToString(availableTransfers[i].Method.ToString()) + " | "
+ availableTransfers[i].DateTimeCreated.ToString("dd/MM/yy") + pending,
availableTransfers[i].K.ToString()));
if (availableTransfers[i].Status.Equals(Transfer.StatusEnum.Pending))
pendingTransferMoney += amountRemaining;
}
}
newTransferLinkButton.Visible = true;
newAmountTextBox.Visible = true;
if (availableTransferListItems.Count == 0)
{
availableTransferListItems.Clear();
availableTransferListItems.Add(new ListItem("<NO AVAILABLE TRANSFERS>"));
newTransferLinkButton.Visible = false;
newAmountTextBox.Visible = false;
}
}
else
{
availableTransferListItems.Add(new ListItem("<SELECT PROMOTER FIRST>"));
newTransferLinkButton.Visible = false;
newAmountTextBox.Visible = false;
}
DropDownList newTransferKDropDownList = (DropDownList)InvoiceTransferGridView.FooterRow.FindControl("NewTransferKDropDownList");
newTransferKDropDownList.DataSource = availableTransferListItems.ToArray();
newTransferKDropDownList.DataTextField = "Text";
newTransferKDropDownList.DataValueField = "Value";
newTransferKDropDownList.DataBind();
}
}
public decimal AmountRefunded()
{
decimal amountRefunded = 0;
TransferSet refundTransferSet;
if (refundTransfers != null)
{
refundTransferSet = refundTransfers;
}
else
{
Query refundTransferQuery = new Query(new And(new Q(Transfer.Columns.TransferRefundedK, this.K),
new Or(new Q(Transfer.Columns.Status, Transfer.StatusEnum.Pending),
new Q(Transfer.Columns.Status, Transfer.StatusEnum.Success))));
refundTransferQuery.Columns = new ColumnSet(Transfer.Columns.Amount);
refundTransferSet = new TransferSet(refundTransferQuery);
}
foreach (Transfer refundTransfer in refundTransferSet)
amountRefunded += Math.Abs(refundTransfer.Amount);
return Math.Round(amountRefunded, 2);
}
public static void EmailAccountsTicketFundsReserveAmount()
{
double ticketMoney = 0;
double ticketMoneyCencelled = 0;
double ticketMoneyWithCardnetDelay = 0;
double ticketMoneyToPromoterBankAccounts = 0;
double ticketMoneyAppliedToInvoices = 0;
double ticketMoneyReserve = 0;
/* money earnt */
Query sumTicketMoneyQuery = new Query();
//sumTicketMoneyQuery.ExtraSelectElements.Add("SumTotalFunds", "SUM([TicketPromoterEvent].[TotalFunds])");
//sumTicketMoneyQuery.OverideSql = "SELECT SUM(TotalFunds) AS SumAmount FROM TicketPromoterEvent";
sumTicketMoneyQuery.OverideSql = "SELECT SUM([Ticket].[Price]) AS SumAmount FROM [Ticket] WHERE [Ticket].[Enabled] = 1 AND [Ticket].[Quantity] > 0 AND [Ticket].[Cancelled] = 0 AND [Ticket].[K] >= 82 ";
Query sumTicketMoneyCancelledQuery = new Query();
sumTicketMoneyCancelledQuery.OverideSql = "SELECT SUM([Ticket].[Price]) AS SumAmount FROM [Ticket] WHERE [Ticket].[Enabled] = 1 AND [Ticket].[Quantity] > 0 AND [Ticket].[Cancelled] = 1 AND [Ticket].[K] >= 82 ";
/* money to promoter bank accounts */
Query sumMoneyToPromoterBankAccountsQuery = new Query();
sumMoneyToPromoterBankAccountsQuery.OverideSql = @"SELECT SUM(RefundTransfer.Amount) AS SumAmount FROM Transfer PaymentTransfer INNER JOIN Transfer RefundTransfer ON PaymentTransfer.K = RefundTransfer.TransferRefundedK
WHERE PaymentTransfer.Method = 5 AND RefundTransfer.Type = 2 AND PaymentTransfer.Type = 1 AND RefundTransfer.Method = 2 AND RefundTransfer.Status = 2";
/* money applied to DSI invoices */
Query sumTicketMoneyAppliedToInvoicesQuery = new Query();
sumTicketMoneyAppliedToInvoicesQuery.OverideSql = "SELECT SUM(IT.Amount) AS SumAmount FROM InvoiceTransfer IT INNER JOIN Transfer T ON IT.TransferK = T.K WHERE T.Method = 5 AND T.Type = 1 AND T.Status = 2";
/* Addition of CardNet delay to figures, as requested by Gee on Jan 7, 08 */
Query sumTicketMoneyCardnetDelayQuery = new Query();
sumTicketMoneyCardnetDelayQuery.OverideSql = "SELECT SUM([Ticket].[Price]) AS SumAmount FROM [Ticket] WHERE [Ticket].[Enabled] = 1 AND [Ticket].[Quantity] > 0 AND ([Ticket].[Cancelled] = 0 OR [Ticket].[CancelledDateTime] >= " + Cambro.Misc.Db.Dt(Utilities.CardnetDelay()) + ") AND [Ticket].[K] >= 82 AND [Ticket].[BuyDateTime] < " + Cambro.Misc.Db.Dt(Utilities.CardnetDelay());
try
{
TicketSet ts = new TicketSet(sumTicketMoneyQuery);
if (ts.Count == 1)
{
ticketMoney = Convert.ToDouble(ts[0].ExtraSelectElements["SumAmount"]);
}
}
catch { }
try
{
TicketSet ts = new TicketSet(sumTicketMoneyCancelledQuery);
if (ts.Count == 1)
{
ticketMoneyCencelled = Convert.ToDouble(ts[0].ExtraSelectElements["SumAmount"]);
}
}
catch { }
try
{
TicketPromoterEventSet tpes = new TicketPromoterEventSet(sumTicketMoneyCardnetDelayQuery);
if (tpes.Count == 1)
{
ticketMoneyWithCardnetDelay = Convert.ToDouble(tpes[0].ExtraSelectElements["SumAmount"]);
}
}
catch { }
try
{
TransferSet trans = new TransferSet(sumMoneyToPromoterBankAccountsQuery);
if (trans.Count == 1)
{
ticketMoneyToPromoterBankAccounts = -1 * Convert.ToDouble(trans[0].ExtraSelectElements["SumAmount"]);
}
}
catch { }
try
{
InvoiceTransferSet its = new InvoiceTransferSet(sumTicketMoneyAppliedToInvoicesQuery);
if (its.Count == 1)
{
ticketMoneyAppliedToInvoices = Convert.ToDouble(its[0].ExtraSelectElements["SumAmount"]);
}
}
catch { }
ticketMoneyReserve = ticketMoneyWithCardnetDelay - ticketMoneyToPromoterBankAccounts - ticketMoneyAppliedToInvoices;
System.Net.Mail.SmtpClient c = new System.Net.Mail.SmtpClient();
c.Host = Common.Properties.GetDefaultSmtpServer();
System.Net.Mail.MailMessage m = new System.Net.Mail.MailMessage();
m.Body = "<p>Ticket money earnt: " + ticketMoney.ToString("c") + "</p>"
+ "<p>Ticket money cancelled: " + ticketMoneyCencelled.ToString("c") + "</p>"
+ "<p>Ticket money earnt with cardnet delay: " + ticketMoneyWithCardnetDelay.ToString("c") + "</p>"
+ "<p>Ticket money to promoter bank accounts: " + ticketMoneyToPromoterBankAccounts.ToString("c") + "</p>"
+ "<p>Ticket money applied to invoices: " + ticketMoneyAppliedToInvoices.ToString("c") + "</p>"
+ "<p><br><b>Ticket money to be held in reserve: " + ticketMoneyReserve.ToString("c") + "</b></p>";
m.From = new System.Net.Mail.MailAddress(Vars.EMAIL_ADDRESS_MAIL);
if (Vars.DevEnv || Vars.IsBeta)
{
m.Subject = "Test - " + m.Subject;
m.Subject += " (" + Time.Now.ToString() + ")";
m.To.Add(Vars.EMAIL_ADDRESS_DEV_TEAM);
}
else
{
m.To.Add(Vars.EMAIL_ADDRESS_ACCOUNTS);
}
// m.To.Add(new System.Net.Mail.MailAddress(Vars.EMAIL_ADDRESS_ACCOUNTS));
m.IsBodyHtml = true;
m.Subject = "Money in reserve for ticket funds: " + ticketMoneyReserve.ToString("c");
c.Send(m);
Log.Increment(Log.Items.EmailsSent);
}
/// <summary>
/// Get all outstanding invoices for the given month / year. Also provide total amount of all overdue invoices.
/// Then produce a report detailing the outstanding invoices summed with the overdue amount and provide details on how to pay
/// </summary>
/// <param name="month">Calendar month as integer: Jan = 1 ... Dec = 12</param>
/// <param name="year">Calendar year as integer</param>
/// <returns></returns>
public StringBuilder GenerateMonthlyStatementStringBuilder(int month, int year, bool linksEnabled)
{
DateTime startDate = new DateTime(year, month, 1);
DateTime endDate = startDate.AddMonths(1);
StringBuilder sb = new StringBuilder();
sb.Append(@"<form id='form1' runat='server'><div style='font-family:Verdana;'>
<table width='100%' border='0' cellspacing='0' cellpadding='0' height='100%'>
<tr><td valign='top'>
<table width='100%'>");
sb.Append(Utilities.GenerateHTMLHeaderRowString("STATEMENT"));
//<tr>
// <td align='left' valign='middle'><img src='/gfx/dsi-1-126.gif'/></td>
// <td colspan=2 align='right' valign='middle'><br><h1>STATEMENT</h1></td>
//</tr>
//<tr><td colspan=3><hr><br></td></tr>
sb.Append(@"<tr>
<td align='left' valign='top' width='380' style='padding-left:48px;'>");
if (this.PrimaryUsr != null && this.PrimaryUsr.Name.Length > 0)
{
sb.Append(this.PrimaryUsr.FirstName);
sb.Append(" ");
sb.Append(this.PrimaryUsr.LastName);
sb.Append("<br>");
}
else if (this.AccountsName.Length > 0)
{
sb.Append(this.AccountsName);
sb.Append("<br>");
}
else
{
if (this.ContactPersonalTitle.Length > 0)
{
sb.Append(this.ContactPersonalTitle);
sb.Append(" ");
}
sb.Append(this.ContactName);
sb.Append("<br>");
}
if (this.Name.Length > 0)
{
sb.Append(this.Name);
sb.Append("<br>");
}
sb.Append(this.AddressHtml);
sb.Append(@"</td><td width='340'></td><td valign='top' width='145'><nobr>Acc No.</nobr><br><br><nobr>Statement Date</nobr></td>
<td align='right' valign='top' width='145'>");
sb.Append(this.K.ToString());
sb.Append("<br><br><b>");
sb.Append(startDate.ToString("MMMM") + " " + startDate.Year.ToString());
sb.Append("</b></td></tr>");
sb.Append(@"</table>
<table width='100%'><tr><td align='center'><br><br>");
// Get all Outstanding Invoices
// Replacing CreatedDateTime with TaxDateTime, as per Gee's request for OASIS v1.5
Query InvoiceQuery = new Query(new And(new Q(Invoice.Columns.TaxDateTime, QueryOperator.GreaterThanOrEqualTo, new DateTime(1900,1,1)),
new Q(Invoice.Columns.TaxDateTime, QueryOperator.LessThan, endDate),
new Q(Invoice.Columns.Paid, false),
new Q(Invoice.Columns.PromoterK, this.K),
new Q(Invoice.Columns.Type, Invoice.Types.Invoice)));
InvoiceQuery.OrderBy = new OrderBy(Invoice.Columns.TaxDateTime, OrderBy.OrderDirection.Ascending);
InvoiceSet invoiceSet = new InvoiceSet(InvoiceQuery);
Query TransferQuery = new Query(new Or(new And(new Q(Transfer.Columns.IsFullyApplied, false),
new Q(Transfer.Columns.Type, Transfer.TransferTypes.Payment),
new Q(Transfer.Columns.Status, Transfer.StatusEnum.Success),
new Q(Transfer.Columns.PromoterK, this.K)),
new And(new Q(Transfer.Columns.Type, Transfer.TransferTypes.Refund),
new Q(Transfer.Columns.PromoterK, this.K),
new Q(Transfer.Columns.TransferRefundedK, 0),
new Or(new Q(Transfer.Columns.Status, Transfer.StatusEnum.Pending),
new Q(Transfer.Columns.Status, Transfer.StatusEnum.Success)))));
TransferQuery.OrderBy = new OrderBy(Transfer.Columns.DateTimeCreated, OrderBy.OrderDirection.Ascending);
TransferSet transferSet = new TransferSet(TransferQuery);
decimal currentAmountDue = 0;
if (invoiceSet.Count > 0 || transferSet.Count > 0)
{
sb.Append(@"<table width='600' border='0' cellspacing='0' cellspadding='3' class='BorderBlack Top Bottom Right'>
<tr>
<td style='vertical-align:bottom;' class='BorderBlack Bottom Left' align='center' width='90'><nobr><b>Tax Date</b></nobr></td>
<td style='vertical-align:bottom;' class='BorderBlack Bottom Left' align='center' width='90'><nobr><b>Due Date</b></nobr></td>
<td style='vertical-align:bottom;' class='BorderBlack Bottom Left' align='center' width='150'><nobr><b>Ref #</b></nobr></td>
<td style='vertical-align:bottom;' class='BorderBlack Bottom Left' align='center' width='130'><nobr><b>Total</b></nobr></td>
<td style='vertical-align:bottom;' class='BorderBlack Bottom Left' align='center' width='140'><b>Amount Outstanding</b></td>
</tr>");
foreach (Invoice invoice in invoiceSet)
{
decimal totalPaid = invoice.AmountPaid;
// If this invoice is in the current month, then add it to the current total
// Replacing CreatedDateTime with TaxDateTime, as per Gee's request for OASIS v1.5
if (invoice.TaxDateTime >= startDate)
currentAmountDue += invoice.Total - totalPaid;
// Amount due of zero means its been paid, but for some reason the invoice has not been set to Paid.
if (invoice.Total - totalPaid != 0)
{
// Replacing CreatedDateTime with TaxDateTime, as per Gee's request for OASIS v1.5
sb.Append(@"<tr>
<td class='BorderBlack Left' align='center'>" + invoice.TaxDateTime.ToString("dd/MM/yy") + @"</td>
<td class='BorderBlack Left' align='center'>" + invoice.DueDateTime.ToString("dd/MM/yy") + @"</td>
<td class='BorderBlack Left' align='left'>");
if (linksEnabled)
sb.Append(Utilities.Link(invoice.UrlReport(), "Invoice #" + invoice.K.ToString()));
else
sb.Append("Invoice #" + invoice.K.ToString());
sb.Append(@"</td>
<td class='BorderBlack Left' align='right'>" + Utilities.MoneyToHTML(invoice.Total) + @"</td>
<td class='BorderBlack Left' align='right'>" + Utilities.MoneyToHTML(invoice.Total - totalPaid) + @"</td>
</tr>");
}
}
foreach (Transfer transfer in transferSet)
{
decimal amountRemaining = transfer.Amount;
// Amount remaining for payments. Refunds amount remaining will be counted as total refund amount
if (transfer.Type.Equals(Transfer.TransferTypes.Payment))
amountRemaining = transfer.AmountRemaining();
currentAmountDue -= amountRemaining;
// Amount remaining of zero means a payment been fully applied, but for some reason the transfer has not been set to isFullyApplied.
if (amountRemaining != 0)
{
sb.Append(@"<tr>
<td class='BorderBlack Left' align='center'>" + transfer.DateTimeCreated.ToString("dd/MM/yy") + @"</td>
<td class='BorderBlack Left' align='center'> </td>
<td class='BorderBlack Left' align='left'>");
if (linksEnabled)
sb.Append(Utilities.Link(transfer.UrlReport(), transfer.Type.ToString() + " #" + transfer.K.ToString()));
else
sb.Append(transfer.Type.ToString() + " #" + transfer.K.ToString());
sb.Append(@"</td>
<td class='BorderBlack Left' align='right'>" + Utilities.MoneyToHTML(-1 * transfer.Amount) + @"</td>
<td class='BorderBlack Left' align='right'>" + Utilities.MoneyToHTML(-1 * amountRemaining) + @"</td>
</tr>");
}
}
sb.Append("</table>");
}
// Now get sum for outstanding invoices for previous months ago
var previousMonthsAmountOwed = new decimal[] { 0, 0, 0, 0 };
for (int i = 1; i <= 3; i++)
{
startDate = startDate.AddMonths(-1);
endDate = startDate.AddMonths(1);
previousMonthsAmountOwed[i - 1] = AmountDue(startDate, endDate);
}
// Now get sum for outstanding invoices for 3+ months ago
endDate = startDate.AddMilliseconds(-1);
startDate = new DateTime(1900,1,1);
previousMonthsAmountOwed[3] = AmountDue(startDate, endDate);
decimal total = previousMonthsAmountOwed[0] + previousMonthsAmountOwed[1] + previousMonthsAmountOwed[2] + previousMonthsAmountOwed[3] + currentAmountDue;
sb.Append(@"<br><table border='0' cellspacing='0' cellpadding='3' width='600' class='BorderBlack Right Top Bottom'>
<tr><td style='vertical-align:bottom;' class='BorderBlack Bottom Left' align='center'><b>3+ MONTHS</b></td>
<td style='vertical-align:bottom;' class='BorderBlack Bottom Left' align='center'><b>3 MONTHS</b></td>
<td style='vertical-align:bottom;' class='BorderBlack Bottom Left' align='center'><b>2 MONTHS</b></td>
<td style='vertical-align:bottom;' class='BorderBlack Bottom Left' align='center'><b>1 MONTH</b></td>
<td style='vertical-align:bottom;' class='BorderBlack Bottom Left' align='center'><b>CURRENT</b></td>
</tr>
<tr><td class='BorderBlack Left' align='right'>" + Utilities.MoneyToHTML(previousMonthsAmountOwed[3]) + @"</td>
<td class='BorderBlack Left' align='right'>" + Utilities.MoneyToHTML(previousMonthsAmountOwed[2]) + @"</td>
<td class='BorderBlack Left' align='right'>" + Utilities.MoneyToHTML(previousMonthsAmountOwed[1]) + @"</td>
<td class='BorderBlack Left' align='right'>" + Utilities.MoneyToHTML(previousMonthsAmountOwed[0]) + @"</td>
<td class='BorderBlack Left' align='right'>" + Utilities.MoneyToHTML(currentAmountDue) + @"</td>
</tr></table>
<br><table border='0' cellspacing='0' cellpadding='3' width='600'>
<tr><td width=460></td>
<td style='vertical-align:bottom;' class='BorderBlack All' align='center'><b>TOTAL</b></td></tr>
<tr><td width=460></td>
<td class='BorderBlack Bottom Left Right' align='right'>" + total.ToString("c") + @"</td></tr></table>");
//sb.Append("<br><br><a href='http://");
//sb.Append(Vars.DomainName);
//sb.Append("/promoters/");
//sb.Append(this.UrlName);
//sb.Append("/invoices'><font size='+1'><b>Click Here To Pay Invoices</b></font></a></td></tr></table>");
sb.Append(@"</td></tr></table></td></tr>");
// DSI Registration Footer
sb.Append(Utilities.GenerateHTMLFooterRowString());
// sb.Append(@"<tr><td valign='bottom'><hr>
// <table width='100%' cellpadding='0' cellspacing='0'><tr>
// <td style='height:1' valign='bottom' align='left'><font size=1>Greenhill House, Thorpe Road, Peterborough, PE3 6RU</font></td>
// <td style='height:1' valign='bottom' align='left'><font size=1><b>T</b>: <br><b>F</b>: <br><br><b>E</b>: </font></td>
// <td style='height:1' valign='bottom' align='left'><font size=1>0207 835 5599<br>0870 068 8822<br><br><a href='mailto:[email protected]'>[email protected]</a></font></td>
// <td style='height:1' align='right' valign='bottom'><font size=1>Development Hell Limited<br>VAT Reg. No. 796 5005 04<br>Registered in England No. 04333049<br>Greenhill House, Thorpe Road, Peterborough, PE3 6RU<br>All business is undertaken subject to our standard terms and conditions</font></td></tr>
// </table>");
sb.Append(@"</table></div></form>");
return sb;
}
public TransferSet GetAvailableTicketFundTransfers()
{
Query qTicketFundTransferWithUnappliedMoney = new Query();
qTicketFundTransferWithUnappliedMoney.QueryCondition = new And(new Q(Transfer.Columns.Method, Transfer.Methods.TicketSales),
new Q(Transfer.Columns.Status, Transfer.StatusEnum.Success),
new Q(Transfer.Columns.PromoterK, this.K),
new Q(Transfer.Columns.Amount, QueryOperator.NotEqualTo, 0),
new Q(Transfer.Columns.Type, Transfer.TransferTypes.Payment),
new Or(new Q(Transfer.Columns.IsFullyApplied, 0),
new Q(Transfer.Columns.IsFullyApplied, QueryOperator.IsNull, null)));
TransferSet tsTicketFundTransferWithUnappliedMoney = new TransferSet(qTicketFundTransferWithUnappliedMoney);
return tsTicketFundTransferWithUnappliedMoney;
}
public decimal GetAvailableTicketFunds(DateTime onlyUseItemsFromBefore)
{
var availableTicketFunds = 0m;
try
{
Query qTicketFundTransferWithUnappliedMoney = new Query();
qTicketFundTransferWithUnappliedMoney.Columns = new ColumnSet(Transfer.Columns.K, Transfer.Columns.Amount);
qTicketFundTransferWithUnappliedMoney.QueryCondition =
new And(
new Q(Transfer.Columns.Method, Transfer.Methods.TicketSales),
new Q(Transfer.Columns.Status, Transfer.StatusEnum.Success),
new Q(Transfer.Columns.PromoterK, this.K),
new Q(Transfer.Columns.Amount, QueryOperator.NotEqualTo, 0),
new Q(Transfer.Columns.Type, Transfer.TransferTypes.Payment),
new Or(
new Q(Transfer.Columns.IsFullyApplied, 0),
new Q(Transfer.Columns.IsFullyApplied, QueryOperator.IsNull, null)
),
onlyUseItemsFromBefore == DateTime.MinValue ? new Q(true) : new Q(Transfer.Columns.DateTimeCreated, QueryOperator.LessThan, onlyUseItemsFromBefore)
);
TransferSet tsTicketFundTransferWithUnappliedMoney = new TransferSet(qTicketFundTransferWithUnappliedMoney);
foreach (Transfer t in tsTicketFundTransferWithUnappliedMoney)
availableTicketFunds += t.AmountRemaining();
}
catch { }
return Math.Round(availableTicketFunds, 2);
}
public decimal GetBalance(DateTime onlyUseItemsFromBefore)
{
decimal invoiceBalance = 0;
decimal transferBalance = 0;
Query qInvoiceBalance = new Query();
qInvoiceBalance.Columns = new ColumnSet();
qInvoiceBalance.ExtraSelectElements.Add("sum", "SUM([Invoice].[Total])");
qInvoiceBalance.QueryCondition =
new And(
new Q(Invoice.Columns.PromoterK, this.K),
onlyUseItemsFromBefore == DateTime.MinValue ? new Q(true) : new Q(Invoice.Columns.TaxDateTime, QueryOperator.LessThan, onlyUseItemsFromBefore)
);
InvoiceSet isInvoiceBalance = new InvoiceSet(qInvoiceBalance);
if (isInvoiceBalance.Count > 0 && isInvoiceBalance[0].ExtraSelectElements["sum"] != DBNull.Value)
invoiceBalance = Convert.ToDecimal(isInvoiceBalance[0].ExtraSelectElements["sum"]);
Query qTransferBalance = new Query();
qTransferBalance.Columns = new ColumnSet();
qTransferBalance.ExtraSelectElements.Add("sum", "SUM(ISNULL([Transfer].[Amount], 0))");
qTransferBalance.QueryCondition =
new And(
new Or(
new Q(Transfer.Columns.Status, Transfer.StatusEnum.Success),
new And(new Q(Transfer.Columns.Status, Transfer.StatusEnum.Pending),new Q(Transfer.Columns.Type, Transfer.TransferTypes.Refund))
),
new Q(Transfer.Columns.PromoterK, this.K),
onlyUseItemsFromBefore == DateTime.MinValue ? new Q(true) : new Q(Transfer.Columns.DateTimeCreated, QueryOperator.LessThan, onlyUseItemsFromBefore)
);
if (this.OverrideApplyTicketFundsToInvoices)
{
qTransferBalance.TableElement =
new Join(
new TableElement(TablesEnum.Transfer),
new TableElement(new Column(Transfer.Columns.TransferRefundedK, Transfer.Columns.K)),
QueryJoinType.Left,
Transfer.Columns.TransferRefundedK,
new Column(Transfer.Columns.TransferRefundedK, Transfer.Columns.K)
);
qTransferBalance.QueryCondition =
new And(
qTransferBalance.QueryCondition,
new Q(Transfer.Columns.Method, QueryOperator.NotEqualTo, Transfer.Methods.TicketSales),
new Or(
new Q(new Column(Transfer.Columns.TransferRefundedK, Transfer.Columns.K), QueryOperator.IsNull, null),
new Q(new Column(Transfer.Columns.TransferRefundedK, Transfer.Columns.Method), QueryOperator.NotEqualTo, Transfer.Methods.TicketSales)
)
);
Query qTicketSalesAppliedBalance = new Query();
qTicketSalesAppliedBalance.Columns = new ColumnSet();
qTicketSalesAppliedBalance.ExtraSelectElements.Add("sum", "SUM(ISNULL([InvoiceTransfer].[Amount], 0))");
qTicketSalesAppliedBalance.QueryCondition =
new And(
new Or(
new Q(Transfer.Columns.Status, Transfer.StatusEnum.Success),
new And(new Q(Transfer.Columns.Status, Transfer.StatusEnum.Pending), new Q(Transfer.Columns.Type, Transfer.TransferTypes.Refund))
),
new Q(Transfer.Columns.PromoterK, this.K),
new Q(Transfer.Columns.Method, Transfer.Methods.TicketSales),
onlyUseItemsFromBefore == DateTime.MinValue ? new Q(true) : new Q(Transfer.Columns.DateTimeCreated, QueryOperator.LessThan, onlyUseItemsFromBefore)
);
qTicketSalesAppliedBalance.TableElement = new Join(InvoiceTransfer.Columns.TransferK, Transfer.Columns.K);
InvoiceTransferSet itsTicketSalesAppliedBalance = new InvoiceTransferSet(qTicketSalesAppliedBalance);
if (itsTicketSalesAppliedBalance.Count > 0 && itsTicketSalesAppliedBalance[0].ExtraSelectElements["sum"] != DBNull.Value)
transferBalance += Convert.ToDecimal(itsTicketSalesAppliedBalance[0].ExtraSelectElements["sum"]);
}
TransferSet tsTransferBalance = new TransferSet(qTransferBalance);
if (tsTransferBalance.Count > 0 && tsTransferBalance[0].ExtraSelectElements["sum"] != DBNull.Value)
transferBalance += Convert.ToDecimal(tsTransferBalance[0].ExtraSelectElements["sum"]);
return Math.Round(transferBalance - invoiceBalance, 2);
}
public void ApplyTransfersToThisInvoice(TransferSet transferSet)
{
List<Transfer> transfers = new List<Transfer>();
transferSet.Reset();
foreach (Transfer t in transferSet)
{
transfers.Add(t);
}
ApplyTransfersToThisInvoice(transfers);
}
//private InvoiceTransferSet successfulInvoiceTransfers;
#endregion
#region UpdateAndAutoApplySuccessfulTransfersWithAvailableMoney
public void UpdateAndAutoApplySuccessfulTransfersWithAvailableMoney()
{
if (this.Type.Equals(Invoice.Types.Invoice) && this.Paid == false)
{
decimal amountDue = this.AmountDue;
if (amountDue > 0)
{
decimal availableMoney = 0m;
if (this.Promoter != null)
availableMoney = this.Promoter.GetAvailableMoney();
else if (this.Usr != null)
availableMoney = this.Usr.GetBalance();
// Only apply if they have positive available money
if (Math.Round(availableMoney, 2) > 0)
{
Query transferQuery = new Query();
if (this.Promoter != null)
{
transferQuery = new Query(new And(new Q(Transfer.Columns.IsFullyApplied, false),
new Q(Transfer.Columns.PromoterK, this.PromoterK),
new Q(Transfer.Columns.Status, Transfer.StatusEnum.Success),
new Q(Transfer.Columns.Type, Transfer.TransferTypes.Payment)));
if (this.Promoter.OverrideApplyTicketFundsToInvoices)
{
transferQuery.QueryCondition = new And(transferQuery.QueryCondition,
new Q(Transfer.Columns.Method, QueryOperator.NotEqualTo, Transfer.Methods.TicketSales));
}
}
else if (this.Usr != null)
{
transferQuery = new Query(new And(new Q(Transfer.Columns.IsFullyApplied, false),
new Q(Transfer.Columns.PromoterK, 0),
new Q(Transfer.Columns.UsrK, this.UsrK),
new Q(Transfer.Columns.Status, Transfer.StatusEnum.Success),
new Q(Transfer.Columns.Type, Transfer.TransferTypes.Payment)));
}
transferQuery.OrderBy = new OrderBy(Transfer.Columns.DateTimeComplete, OrderBy.OrderDirection.Ascending);
TransferSet transferSet = new TransferSet(transferQuery);
if (transferSet.Count > 0)
{
bool complete = false;
ApplyTransfersToThisInvoice(transferSet);
this.UpdateAndSetPaidStatus();
// Remove or decrease amount of any Pending Invoice Transfers that, if successful, would bring the amount paid over the total invoice cost
Query invoiceTransferPendingQuery = new Query();
invoiceTransferPendingQuery.QueryCondition = new And(new Q(InvoiceTransfer.Columns.InvoiceK, this.K),
new Q(Transfer.Columns.Status, Transfer.StatusEnum.Pending));
invoiceTransferPendingQuery.TableElement = new Join(InvoiceTransfer.Columns.TransferK, Transfer.Columns.K);
InvoiceTransferSet invoiceTransferSet = new InvoiceTransferSet(invoiceTransferPendingQuery);
var amountPaid = this.AmountPaid;
var amountPending = 0m;
foreach (InvoiceTransfer invoiceTransfer in invoiceTransferSet)
{
var invoiceTransferAmount = Math.Round(invoiceTransfer.Amount, 2);
amountPending += Math.Round(invoiceTransfer.Amount, 2);
Transfer transfer = new Transfer(invoiceTransfer.TransferK);
if (Math.Round(amountPaid, 2) >= Math.Round(Total, 2))
{
invoiceTransfer.Delete();
this.AddNote("Transfer #" + invoiceTransfer.TransferK.ToString() + " has been unapplied from this invoice", "System");
}
else if (Math.Round(amountPaid + amountPending, 2) > Math.Round(Total, 2))
{
invoiceTransfer.Amount += Math.Round((Total - amountPaid) - amountPending, 2);
amountPending += Math.Round(invoiceTransfer.Amount - invoiceTransferAmount, 2);
if (Math.Round(invoiceTransfer.Amount, 2) == 0)
{
this.AddNote("Transfer #" + invoiceTransfer.TransferK.ToString() + " has been unapplied from this invoice", "System");
invoiceTransfer.Delete();
}
else
{
this.AddNote("Transfer #" + invoiceTransfer.TransferK.ToString() + " amount applied has been updated", "System");
invoiceTransfer.Update();
}
}
if (invoiceTransferAmount != 0 && transfer.IsFullyApplied == true)
{
transfer.IsFullyApplied = false;
transfer.Update();
}
}
}
}
}
}
this.UpdateAndSetPaidStatus();
}
/// <summary>
/// Checks if there are any transfers with the DuplicateGUID from the current page ViewState. This prevents transfers being issued multiple times from user error.
/// </summary>
/// <returns></returns>
private int GetDuplicateGuidTransferK()
{
TransferSet ts = new TransferSet(new Query(new Q(Transfer.Columns.DuplicateGuid, (Guid)this.ViewState["DuplicateGuidTransfer"])));
if (ts.Count > 0)
return ts[0].K;
else
return 0;
}
/// <summary>
/// Checks if there are any invoices or transfers with the DuplicateGUID from the current page ViewState. This prevents invoices and transfers being issued multiple times from user error.
/// </summary>
/// <returns></returns>
private bool DoesDuplicateGuidExistInDb()
{
bool transferSuccess = false;
bool duplicateInvoice = Invoice.DoesDuplicateGuidExistInDb((Guid)this.ViewState["DuplicateGuidInvoice"]);
Query duplicateTransferQuery = new Query(new Q(Transfer.Columns.DuplicateGuid, (Guid)this.ViewState["DuplicateGuidTransfer"]));
duplicateTransferQuery.Columns = new ColumnSet(Transfer.Columns.K, Transfer.Columns.Status);
TransferSet tset = new TransferSet(duplicateTransferQuery);
if (tset.Count > 0)
{
foreach (Transfer t in tset)
{
if (t.Status.Equals(Transfer.StatusEnum.Success))
{
transferSuccess = true;
break;
}
}
//throw new Exception("Payment already processed successfully.");
//else if (tset[0].Status.Equals(Transfer.StatusEnum.Failed))
// throw new Exception("Payment process " + tset[0].Status.ToString().ToLower() + ". Please try again. If payment is not successful, please contact an administrator for assistance.");
//else
// throw new Exception("Payment process was not successful. Please try again. If payment is not successful, please contact an administrator for assistance.");}
}
if (!duplicateInvoice && tset.Count == 0)
DuplicateState = DuplicateStateEnum.None;
else if (duplicateInvoice && transferSuccess)
DuplicateState = DuplicateStateEnum.InvoiceProcessedAndTransferSuccess;
else if (duplicateInvoice && !transferSuccess)
DuplicateState = DuplicateStateEnum.InvoiceProcessedAndTransferFail;
else if (!duplicateInvoice && transferSuccess)
DuplicateState = DuplicateStateEnum.InvoiceFailAndTransferSuccess;
else if (!duplicateInvoice && !transferSuccess)
DuplicateState = DuplicateStateEnum.InvoiceFailAndTransferFail;
return duplicateInvoice || tset.Count > 0;
}
private void SetupAvailableTransfersDropDownList()
{
if (this.CreditK == 0 || CurrentCredit.Paid == false)
{
LinkButton newTransferLinkButton = (LinkButton)CreditTransferGridView.FooterRow.FindControl("AddLinkButton");
TextBox newAmountTextBox = (TextBox)CreditTransferGridView.FooterRow.FindControl("NewAmountTextBox");
newTransferLinkButton.Visible = true;
Q transfersForPromotersUsersQueryConditions = null;
//if(this.PromoterKHiddenTextBox.Text != "" && this.UserKHiddenTextBox.Text != "")
// transfersForPromotersUsersQueryConditions = new Or(new Q(Transfer.Columns.PromoterK, Convert.ToInt32(PromoterKHiddenTextBox.Text)), new Q(Transfer.Columns.UsrK, Convert.ToInt32(UserKHiddenTextBox.Text)));
//else if (PromoterKHiddenTextBox.Text != "")
// transfersForPromotersUsersQueryConditions = new Q(Transfer.Columns.PromoterK, Convert.ToInt32(PromoterKHiddenTextBox.Text));
//else if (UserKHiddenTextBox.Text != "")
// transfersForPromotersUsersQueryConditions = new Q(Transfer.Columns.UsrK, Convert.ToInt32(UserKHiddenTextBox.Text));
if (PromoterKHiddenTextBox.Text != "")
transfersForPromotersUsersQueryConditions = new Q(Transfer.Columns.PromoterK, Convert.ToInt32(PromoterKHiddenTextBox.Text));
Query availableTransfersQuery = new Query();
// Credit Transfers must be repayments, thus negative amounts
string queryForAvailableTransfers = @"-1 * Round([Transfer].[Amount],2) < -1 * (CASE WHEN ((SELECT Sum([InvoiceTransfer].[Amount]) FROM [InvoiceTransfer] WHERE [InvoiceTransfer].[TransferK] = [Transfer].[K]) IS NULL) THEN 0 ELSE Round((SELECT Sum([InvoiceTransfer].[Amount]) FROM [InvoiceTransfer] WHERE [InvoiceTransfer].[TransferK] = [Transfer].[K]),2) END)";
ListItem[] availableTransferListItems = new ListItem[0];
if (transfersForPromotersUsersQueryConditions != null)
{
availableTransfersQuery.QueryCondition = new And(
new StringQueryCondition(queryForAvailableTransfers),
transfersForPromotersUsersQueryConditions,
new Q(Transfer.Columns.Status, QueryOperator.NotEqualTo, Transfer.StatusEnum.Cancelled),
new Q(Transfer.Columns.Status, QueryOperator.NotEqualTo, Transfer.StatusEnum.Failed),
new Q(Transfer.Columns.Type, Transfer.TransferTypes.Refund));
availableTransfersQuery.Columns = new ColumnSet(Transfer.Columns.K, Transfer.Columns.Amount, Transfer.Columns.Method, Transfer.Columns.DateTimeCreated);
TransferSet availableTransfers = new TransferSet(availableTransfersQuery);
availableTransferListItems = new ListItem[availableTransfers.Count];
for (int i = 0; i < availableTransfers.Count; i++)
{
availableTransferListItems[i] = new ListItem("K=" + availableTransfers[i].K.ToString() + " | "
+ availableTransfers[i].Amount.ToString("c") + " | "
+ Utilities.CamelCaseToString(availableTransfers[i].Method.ToString()) + " | "
+ availableTransfers[i].DateTimeCreated.ToShortDateString(),
availableTransfers[i].K.ToString());
}
if (availableTransfers.Count == 0)
{
availableTransferListItems = new ListItem[] { new ListItem("<NO AVAILABLE TRANSFERS>") };
newTransferLinkButton.Visible = false;
newAmountTextBox.Visible = false;
}
}
else
availableTransferListItems = new ListItem[] { new ListItem("<SELECT PROMOTER FIRST>") };
DropDownList newTransferKDropDownList = (DropDownList)CreditTransferGridView.FooterRow.FindControl("NewTransferKDropDownList");
newTransferKDropDownList.DataSource = availableTransferListItems;
newTransferKDropDownList.DataTextField = "Text";
newTransferKDropDownList.DataValueField = "Value";
newTransferKDropDownList.DataBind();
}
}
public void MakePayment(List<InvoiceDataHolder> invoices, decimal amount, Usr usr, int promoterK, int actionUsrK, string cardFullName, string cardAddressStreet, string cardAddressArea, string cardAddressTown, string cardAddressCounty, int cardAddressCountryK, string cardAddressPostCode, string cardAddressCountry, string cardNumber, DateTime cardExpiryDate, string cardCV2, Transfer.FraudCheckEnum fraudCheckEnum, bool saveCard, Guid duplicateGuid, DateTime cardStartDate, string cardIssueNumber)
{
this.amount = Math.Round(amount, 2);
if (this.amount <= 0)
{
throw new Exception("Cannot make a payment for " + this.amount.ToString("c") + ". It must be a positive amount.");
}
else
{
this.cardNumber = cardNumber.Trim().Replace(" ", "");
this.cardAddressCountry = cardAddressCountry.Trim();
this.fraudCheckEnum = fraudCheckEnum;
this.invoiceDataHolders = invoices;
this.transfer.Guid = Guid.NewGuid();
this.transfer.DuplicateGuid = duplicateGuid;
this.transfer.Amount = this.amount;
this.transfer.Type = Transfer.TransferTypes.Payment;
this.transfer.Method = Transfer.Methods.Card;
this.transfer.Company = Model.Entities.Transfer.CompanyEnum.DH;
this.transfer.CardAddress1 = cardAddressStreet.Trim();
this.transfer.CardAddressArea = cardAddressArea;
this.transfer.CardAddressCounty = cardAddressCounty;
this.transfer.CardAddressTown = cardAddressTown;
this.transfer.CardAddressCountryK = cardAddressCountryK;
this.transfer.CardPostcode = cardAddressPostCode.Trim();
this.transfer.CardName = cardFullName.Trim();
this.transfer.CardCV2 = cardCV2.Trim();
this.transfer.CardExpires = cardExpiryDate;
this.transfer.CardStart = cardStartDate;
if (cardIssueNumber.Length > 0)
this.transfer.CardIssue = Convert.ToInt32(cardIssueNumber);
this.transfer.SetUsrAndActionUsr(usr);
this.transfer.DateTimeCreated = DateTime.Now;
this.transfer.PromoterK = promoterK;
this.usr = usr;
this.transfer.StoreCardEndAndHashAndCardType(this.cardNumber);
ValidateMinimumDetails();
try
{
response = secVpnService.validateCardFull(
userName,
password,
transfer.Guid.ToString(), // DSI created SecPay Transaction Id "tran0001"
ipAddress, // Credit Card user IP address "127.0.0.1"
cardFullName, // User Name "Mr Cardholder"
cardNumber, // User card number "4444333322221111"
transfer.Amount.ToString(MONEY_FORMAT), // because of deferred=true in Options, will only do shadow payment of just £1 while checking details validate
DateToString(cardExpiryDate), // expiry date "1208"
cardIssueNumber, // Card Issue Number
DateToString(cardStartDate), // start date "0102"
OrderToString(),
ShippingToString(),
BillingToString(),
OptionsToString()); // options from Options() "name=Fred+Bloggs,company=Online+Shop+Ltd,addr_1=Dotcom+House,addr_2=London+Road,city=Townville,state=Countyshire,post_code=AB1+C23,tel=01234+567+890,fax=09876+543+210,email=somebody%40secpay.com,url=http%3A%2F%2Fwww.somedomain.com,test_status=true,dups=false,card_type=Visa,mand_cv2avs=[false|true,deferred=true]"
// Log SecPay Response to transfer and save transfer details
ResponseParser(this.PerformSecurityCheck);
if (this.transfer.Status.Equals(Transfer.StatusEnum.Success))
{
this.transfer.CardSaved = saveCard;
// Make sure there is only one saved transfer for each card
if (saveCard == true)
{
TransferSet savedTransferSet = new TransferSet(new Query(new And(new Q(Transfer.Columns.CardNumberHash, this.transfer.CardNumberHash),
new Q(Transfer.Columns.CardSaved, true))));
foreach (Transfer savedTransfer in savedTransferSet)
{
savedTransfer.CardSaved = false;
savedTransfer.Update();
}
}
if (this.PerformSecurityCheck)
{
response = secVpnService.releaseCardFull(
userName,
password,
transfer.Guid.ToString(),
transfer.Amount.ToString(MONEY_FORMAT),
password,
transfer.Guid.ToString());
// run this through again with the new response
ResponseParser();
}
}
ProcessPaymentResults();
}
catch (Exception ex)
{
EmailSecPayException(ex);
throw new Exception("SecPay Payment failed. See transfer #" + this.transfer.K.ToString() + " for details");
}
}
}
public static string ExportToSage(DateTime startDate, DateTime endDate, ExportToSageType type)
{
StringBuilder sb = new StringBuilder();
startDate = new DateTime(startDate.Year, startDate.Month, startDate.Day);
endDate = new DateTime(endDate.Year, endDate.Month, endDate.Day).AddDays(1);
if (type.Equals(ExportToSageType.SalesInvoices))
{
#region SalesInvoices
// Replacing CreatedDateTime with TaxDateTime, as per Gee's request for OASIS v1.5
Query InvoiceQuery = new Query(new And(new Q(Invoice.Columns.TaxDateTime, QueryOperator.GreaterThanOrEqualTo, startDate),
new Q(Invoice.Columns.TaxDateTime, QueryOperator.LessThan, endDate),
new Q(Invoice.Columns.Type, Invoice.Types.Invoice),
new Q(Invoice.Columns.IsImmediateCreditCardPayment, false)));
InvoiceQuery.OrderBy = new OrderBy(new OrderBy(Invoice.Columns.TaxDateTime, OrderBy.OrderDirection.Ascending),
new OrderBy(Invoice.Columns.K, OrderBy.OrderDirection.Ascending));
InvoiceSet invoiceSet = new InvoiceSet(InvoiceQuery);
string invoiceItemVatCodeToString = "";
string itemDescription = "";
// Sales Invoices
foreach (Invoice invoice in invoiceSet)
{
foreach (InvoiceItem invoiceItem in invoice.Items)
{
invoiceItemVatCodeToString = invoiceItem.VatCode.ToString();
itemDescription = GetAsciiOnly(invoiceItem.Description, GetAsciiOptions.SageApprovedChars).Replace(DELIMITER, ";");
if (!invoice.VatCode.Equals(Invoice.VATCodes.T1))
invoiceItemVatCodeToString = invoice.VatCode.ToString();
sb.Append(INVOICE_TRANSACTION_TYPE);
sb.Append(DELIMITER);
sb.Append(invoice.PromoterK);
sb.Append(DELIMITER);
sb.Append(invoiceItem.NominalCode);
sb.Append(DELIMITER);
sb.Append(DEPT_NBR);
sb.Append(DELIMITER);
// Replacing CreatedDateTime with TaxDateTime, as per Gee's request for OASIS v1.5
sb.Append(invoice.TaxDateTime.ToString("dd/MM/yy"));
sb.Append(DELIMITER);
sb.Append(INVOICE_K_PREFIX); // Prefix + K.ToString() <= 8 characters
sb.Append(invoice.K.ToString("000000")); // Up to 6 digit K number. for K of 10^6 or greater, reduce the prefix size
sb.Append(DELIMITER);
sb.Append(CropString(itemDescription, DESC_MAX_LENGTH));
sb.Append(DELIMITER);
sb.Append(invoiceItem.Price.ToString("0.00"));
sb.Append(DELIMITER);
sb.Append(invoiceItemVatCodeToString);
sb.Append(DELIMITER);
sb.Append(invoiceItem.Vat);
sb.Append(NEWLINE);
}
}
#endregion
}
else if (type.Equals(ExportToSageType.SalesCredits))
{
#region SalesCredits
// Replacing CreatedDateTime with TaxDateTime, as per Gee's request for OASIS v1.5
Query CreditQuery = new Query(new And(new Q(Invoice.Columns.TaxDateTime, QueryOperator.GreaterThanOrEqualTo, startDate),
new Q(Invoice.Columns.TaxDateTime, QueryOperator.LessThan, endDate),
new Q(Invoice.Columns.Type, Invoice.Types.Credit)));
CreditQuery.OrderBy = new OrderBy(new OrderBy(Invoice.Columns.TaxDateTime, OrderBy.OrderDirection.Ascending),
new OrderBy(Invoice.Columns.K, OrderBy.OrderDirection.Ascending));
InvoiceSet creditSet = new InvoiceSet(CreditQuery);
string creditItemVatCodeToString = "";
string itemDescription = "";
// Sales Invoices
foreach (Invoice credit in creditSet)
{
foreach (InvoiceItem creditItem in credit.Items)
{
creditItemVatCodeToString = creditItem.VatCode.ToString();
itemDescription = GetAsciiOnly(creditItem.Description, GetAsciiOptions.SageApprovedChars).Replace(DELIMITER, ";");
if (!credit.VatCode.Equals(Invoice.VATCodes.T1))
creditItemVatCodeToString = credit.VatCode.ToString();
sb.Append(CREDIT_TRANSACTION_TYPE);
sb.Append(DELIMITER);
sb.Append(credit.PromoterK);
sb.Append(DELIMITER);
sb.Append(creditItem.NominalCode);
sb.Append(DELIMITER);
sb.Append(DEPT_NBR);
sb.Append(DELIMITER);
// Replacing CreatedDateTime with TaxDateTime, as per Gee's request for OASIS v1.5
sb.Append(credit.TaxDateTime.ToString("dd/MM/yy"));
sb.Append(DELIMITER);
sb.Append(CREDIT_K_PREFIX); // Prefix + K.ToString() <= 8 characters
sb.Append(credit.K.ToString("000000")); // Up to 6 digit K number. for K of 10^6 or greater, reduce the prefix size
sb.Append(DELIMITER);
sb.Append(CropString(itemDescription, DESC_MAX_LENGTH));
sb.Append(DELIMITER);
sb.Append(Convert.ToDouble(Math.Abs(creditItem.Price)).ToString("0.00"));
sb.Append(DELIMITER);
sb.Append(creditItemVatCodeToString);
sb.Append(DELIMITER);
sb.Append(Convert.ToDouble(Math.Abs(creditItem.Vat)).ToString("0.00"));
sb.Append(NEWLINE);
}
}
#endregion
}
else if (type.Equals(ExportToSageType.BankReceipts))
{
#region BankReceipts
// Replacing CreatedDateTime with TaxDateTime, as per Gee's request for OASIS v1.5
Query InvoiceQuery = new Query(new And(new Q(Invoice.Columns.TaxDateTime, QueryOperator.GreaterThanOrEqualTo, startDate),
new Q(Invoice.Columns.TaxDateTime, QueryOperator.LessThan, endDate),
new Q(Invoice.Columns.Type, Invoice.Types.Invoice),
new Q(Invoice.Columns.IsImmediateCreditCardPayment, true)));
InvoiceQuery.OrderBy = new OrderBy(new OrderBy(Invoice.Columns.TaxDateTime, OrderBy.OrderDirection.Ascending),
new OrderBy(Invoice.Columns.K, OrderBy.OrderDirection.Ascending));
InvoiceSet invoiceSet = new InvoiceSet(InvoiceQuery);
string invoiceItemVatCodeToString = "";
string itemDescription = "";
// Sales Invoices
foreach (Invoice invoice in invoiceSet)
{
foreach (InvoiceItem invoiceItem in invoice.Items)
{
invoiceItemVatCodeToString = invoiceItem.VatCode.ToString();
itemDescription = GetAsciiOnly(invoiceItem.Description, GetAsciiOptions.SageApprovedChars).Replace(DELIMITER, ";");
if (!invoice.VatCode.Equals(Invoice.VATCodes.T1))
invoiceItemVatCodeToString = invoice.VatCode.ToString();
sb.Append(BANK_RECEIPT_TRANSACTION_TYPE);
sb.Append(DELIMITER);
sb.Append("1220");
sb.Append(DELIMITER);
sb.Append(invoiceItem.NominalCode);
sb.Append(DELIMITER);
sb.Append(DEPT_NBR);
sb.Append(DELIMITER);
// Replacing CreatedDateTime with TaxDateTime, as per Gee's request for OASIS v1.5
sb.Append(invoice.TaxDateTime.ToString("dd/MM/yy"));
sb.Append(DELIMITER);
sb.Append(BANK_RECEIPT_K_PREFIX); // Prefix + K.ToString() <= 8 characters
sb.Append(invoice.K.ToString("000000")); // Up to 6 digit K number. for K of 10^6 or greater, reduce the prefix size
sb.Append(DELIMITER);
sb.Append(CropString(itemDescription, DESC_MAX_LENGTH));
sb.Append(DELIMITER);
sb.Append(invoiceItem.Price.ToString("0.00"));
sb.Append(DELIMITER);
sb.Append(invoiceItemVatCodeToString);
sb.Append(DELIMITER);
sb.Append(invoiceItem.Vat);
sb.Append(NEWLINE);
}
}
#endregion
}
else if (type.Equals(ExportToSageType.PrepaidIncome))
{
#region PrepaidIncome
// Replacing CreatedDateTime with TaxDateTime, as per Gee's request for OASIS v1.5
Query InvoiceQuery = new Query(new And(new Q(Invoice.Columns.TaxDateTime, QueryOperator.GreaterThanOrEqualTo, startDate),
new Q(Invoice.Columns.TaxDateTime, QueryOperator.LessThan, endDate)));
InvoiceSet invoiceSet = new InvoiceSet(InvoiceQuery);
List<NameValueCollection> MonthsNominalcodesMoney = new List<NameValueCollection>();
MonthsNominalcodesMoney.Add(new NameValueCollection());
decimal sumPriceAll = 0;
NameValueCollection sumPriceNominalCode = new NameValueCollection();
foreach (Invoice invoice in invoiceSet)
{
// Sum for each month, but dividing item price over its revenue period
foreach (InvoiceItem invoiceItem in invoice.Items)
{
sumPriceAll += Math.Round(invoiceItem.Price, 2);
sumPriceNominalCode.Set(invoiceItem.NominalCode.ToString(), ((decimal)(invoiceItem.Price + Convert.ToDecimal(sumPriceNominalCode[invoiceItem.NominalCode.ToString()]))).ToString());
var revenueMonthSpread = SpreadRevenueOverMonths(invoiceItem);
DateTime sageStartDate = startDate;
DateTime monthOfInvoiceItemRevenueStartDate = new DateTime(invoiceItem.RevenueStartDate.Year, invoiceItem.RevenueStartDate.Month, 1);
DateTime monthOfInvoiceItemRevenueEndDate = new DateTime(invoiceItem.RevenueEndDate.Year, invoiceItem.RevenueEndDate.Month, 1);
DateTime monthOfReportStartDate = new DateTime(startDate.Year, startDate.Month, 1);
DateTime earliestMonth = monthOfReportStartDate < monthOfInvoiceItemRevenueStartDate ? monthOfReportStartDate : monthOfInvoiceItemRevenueStartDate;
DateTime latestMonth = monthOfReportStartDate > monthOfInvoiceItemRevenueEndDate ? monthOfReportStartDate : monthOfInvoiceItemRevenueEndDate;
int outputCounter = 0;
int revenueMonthSpreadCounter = 0;
for (DateTime thisMonth = earliestMonth; thisMonth <= latestMonth; thisMonth = thisMonth.AddMonths(1))
{
if (MonthsNominalcodesMoney.Count <= outputCounter || MonthsNominalcodesMoney[outputCounter] == null)
MonthsNominalcodesMoney.Add(new NameValueCollection());
if (thisMonth >= monthOfInvoiceItemRevenueStartDate && thisMonth <= monthOfInvoiceItemRevenueEndDate)
{
MonthsNominalcodesMoney[outputCounter].Set(invoiceItem.NominalCode.ToString(), ((double)((double)revenueMonthSpread[revenueMonthSpreadCounter] + Convert.ToDouble(MonthsNominalcodesMoney[outputCounter][invoiceItem.NominalCode.ToString()]))).ToString());
revenueMonthSpreadCounter++;
}
if (thisMonth >= monthOfReportStartDate)
outputCounter++;
}
//while (invoiceItem.RevenueStartDate.Month > sageStartDate.Month || invoiceItem.RevenueStartDate.Year > sageStartDate.Year)
//{
// sageStartDate = sageStartDate.AddMonths(1);
// if (MonthsNominalcodesMoney.Count <= counter || MonthsNominalcodesMoney[counter] == null)
// MonthsNominalcodesMoney.Add(new NameValueCollection());
// counter++;
//}
//foreach (double d in revenueMonthSpread)
//{
// if (MonthsNominalcodesMoney.Count <= counter || MonthsNominalcodesMoney[counter] == null)
// MonthsNominalcodesMoney.Add(new NameValueCollection());
// MonthsNominalcodesMoney[counter].Set(invoiceItem.NominalCode.ToString(), ((double)(d + Convert.ToDouble(MonthsNominalcodesMoney[counter][invoiceItem.NominalCode.ToString()]))).ToString());
// counter++;
//}
}
}
// First JC credit the code 2110 for sum of all invoices created in the month.
if (Math.Round(sumPriceAll, 2) >= 0)
sb.Append(JOURNAL_CREDIT_TRANSACTION_TYPE);
else
sb.Append(JOURNAL_DEBIT_TRANSACTION_TYPE);
sb.Append(DELIMITER);
sb.Append(DELIMITER); // null between delimiters
sb.Append("2110");
sb.Append(DELIMITER);
sb.Append(DEPT_NBR);
sb.Append(DELIMITER);
sb.Append(startDate.ToString("dd/MM/yy"));
sb.Append(DELIMITER);
sb.Append(startDate.Month.ToString("00"));
sb.Append(startDate.ToString("yy"));
sb.Append(DELIMITER);
sb.Append(PREPAID_INCOME_PREFIX);
sb.Append(startDate.ToString("MM"));
sb.Append(@"/");
sb.Append(startDate.ToString("yy"));
sb.Append(" - 2110");
sb.Append(DELIMITER);
sb.Append(Convert.ToDouble(Math.Abs(sumPriceAll)).ToString("0.00"));
sb.Append(DELIMITER);
sb.Append(InvoiceItem.VATCodes.T9.ToString());
sb.Append(DELIMITER);
sb.Append("0");
sb.Append(NEWLINE);
// Then JD debit the sum of each nominal code for all invoices created in the month
string[] sumPriceNominalCodeAllKeys = sumPriceNominalCode.AllKeys;
// Sort by Nominal Code Ascending
Array.Sort(sumPriceNominalCodeAllKeys);
for (int i = 0; i < sumPriceNominalCodeAllKeys.Length; i++)
{
double sumPrice = Convert.ToDouble(sumPriceNominalCode[sumPriceNominalCodeAllKeys[i]]);
if (Math.Round(sumPrice, 2) >= 0)
sb.Append(JOURNAL_DEBIT_TRANSACTION_TYPE);
else
sb.Append(JOURNAL_CREDIT_TRANSACTION_TYPE);
sb.Append(DELIMITER);
sb.Append(DELIMITER); // null between delimiters
sb.Append(sumPriceNominalCodeAllKeys[i]);
sb.Append(DELIMITER);
sb.Append(DEPT_NBR);
sb.Append(DELIMITER);
sb.Append(startDate.ToString("dd/MM/yy"));
sb.Append(DELIMITER);
sb.Append(startDate.Month.ToString("00"));
sb.Append(startDate.ToString("yy"));
sb.Append(DELIMITER);
sb.Append(PREPAID_INCOME_PREFIX);
sb.Append(startDate.ToString("MM"));
sb.Append(@"/");
sb.Append(startDate.ToString("yy"));
sb.Append(" - ");
sb.Append(sumPriceNominalCodeAllKeys[i]);
sb.Append(DELIMITER);
sb.Append(Convert.ToDouble(Math.Abs(sumPrice)).ToString("0.00"));
sb.Append(DELIMITER);
sb.Append(InvoiceItem.VATCodes.T9.ToString());
sb.Append(DELIMITER);
sb.Append("0");
sb.Append(NEWLINE);
}
// Now JD debit the code 2110 for sum of all revenues for each month individually.
for (int i = 0; i < MonthsNominalcodesMoney.Count; i++)
{
double sum = 0;
for (int j = 0; j < MonthsNominalcodesMoney[i].Count; j++)
{
sum += Convert.ToDouble(MonthsNominalcodesMoney[i][j]);
}
if (Math.Round(sum, 2) != 0)
{
if (Math.Round(sum, 2) > 0)
sb.Append(JOURNAL_DEBIT_TRANSACTION_TYPE);
else
sb.Append(JOURNAL_CREDIT_TRANSACTION_TYPE);
sb.Append(DELIMITER);
sb.Append(DELIMITER); // null between delimiters
sb.Append("2110");
sb.Append(DELIMITER);
sb.Append(DEPT_NBR);
sb.Append(DELIMITER);
sb.Append(startDate.AddMonths(i).ToString("dd/MM/yy"));
sb.Append(DELIMITER);
sb.Append(startDate.AddMonths(i).Month.ToString("00"));
sb.Append(startDate.AddMonths(i).ToString("yy"));
sb.Append(DELIMITER);
sb.Append(PREPAID_INCOME_PREFIX);
sb.Append(startDate.AddMonths(i).ToString("MM"));
sb.Append(@"/");
sb.Append(startDate.AddMonths(i).ToString("yy"));
sb.Append(" - 2110");
sb.Append(DELIMITER);
sb.Append(Convert.ToDouble(Math.Abs(sum)).ToString("0.00"));
sb.Append(DELIMITER);
sb.Append(InvoiceItem.VATCodes.T9.ToString());
sb.Append(DELIMITER);
sb.Append("0");
sb.Append(NEWLINE);
}
// Then JC credit each nominal code sum prices for that nominal codes for each month individually.
string[] MonthsNominalcodesMoneyAllKeys = MonthsNominalcodesMoney[i].AllKeys;
// Sort by Nominal Code Ascending
Array.Sort(MonthsNominalcodesMoneyAllKeys);
double monthNominalCodeAmount = 0;
for (int j = 0; j < MonthsNominalcodesMoneyAllKeys.Length; j++)
{
monthNominalCodeAmount = Convert.ToDouble(MonthsNominalcodesMoney[i][MonthsNominalcodesMoneyAllKeys[j]]);
if (Math.Round(monthNominalCodeAmount, 2) != 0)
{
if (Math.Round(monthNominalCodeAmount, 2) > 0)
sb.Append(JOURNAL_CREDIT_TRANSACTION_TYPE);
else
sb.Append(JOURNAL_DEBIT_TRANSACTION_TYPE);
sb.Append(DELIMITER);
sb.Append(DELIMITER); // null between delimiters
sb.Append(MonthsNominalcodesMoneyAllKeys[j]);
sb.Append(DELIMITER);
sb.Append(DEPT_NBR);
sb.Append(DELIMITER);
sb.Append(startDate.AddMonths(i).ToString("dd/MM/yy"));
sb.Append(DELIMITER);
sb.Append(startDate.AddMonths(i).Month.ToString("00"));
sb.Append(startDate.AddMonths(i).ToString("yy"));
sb.Append(DELIMITER);
sb.Append(PREPAID_INCOME_PREFIX);
sb.Append(startDate.AddMonths(i).ToString("MM"));
sb.Append(@"/");
sb.Append(startDate.AddMonths(i).ToString("yy"));
sb.Append(" - ");
sb.Append(MonthsNominalcodesMoneyAllKeys[j]);
sb.Append(DELIMITER);
sb.Append(Convert.ToDouble(Math.Abs(monthNominalCodeAmount)).ToString("0.00"));
sb.Append(DELIMITER);
sb.Append(InvoiceItem.VATCodes.T9.ToString());
sb.Append(DELIMITER);
sb.Append("0");
sb.Append(NEWLINE);
}
}
}
#endregion
}
else if (type.Equals(ExportToSageType.SalesReceipts))
{
#region SalesReceipts (InvoiceTransfer)
Query TransferQuery = new Query(new And(new Q(Transfer.Columns.DateTimeComplete, QueryOperator.GreaterThanOrEqualTo, startDate),
new Q(Transfer.Columns.DateTimeComplete, QueryOperator.LessThan, endDate),
new Q(Transfer.Columns.PromoterK, QueryOperator.GreaterThan, 0),
new Q(Transfer.Columns.Status, Transfer.StatusEnum.Success)));
TransferQuery.OrderBy = new OrderBy(Transfer.Columns.DateTimeComplete, OrderBy.OrderDirection.Ascending);
//TransferQuery.TableElement = new JoinLeft(Transfer.Columns.K, InvoiceTransfer.Columns.TransferK);
TransferSet transferSet = new TransferSet(TransferQuery);
// Transfers
foreach (Transfer t in transferSet)
{
if (t.InvoiceTransfers.Count == 0)
appendTransferAndInvoiceTransfer(sb, t, null);
else
{
foreach (InvoiceTransfer it in t.InvoiceTransfers)
appendTransferAndInvoiceTransfer(sb, t, it);
}
}
#endregion
}
else if (type.Equals(ExportToSageType.CampaignCredits))
{
#region CampaignCredit
Query q = new Query(new And(new Q(CampaignCredit.Columns.ActionDateTime, QueryOperator.GreaterThanOrEqualTo, startDate),
new Q(CampaignCredit.Columns.ActionDateTime, QueryOperator.LessThan, endDate),
new Q(CampaignCredit.Columns.Enabled, true)));
q.OrderBy = new OrderBy(
new OrderBy(CampaignCredit.Columns.ActionDateTime,OrderBy.OrderDirection.Ascending),
new OrderBy(CampaignCredit.Columns.DisplayOrder,OrderBy.OrderDirection.Ascending),
new OrderBy(CampaignCredit.Columns.K, OrderBy.OrderDirection.Ascending));
CampaignCreditSet ccs = new CampaignCreditSet(q);
// Transfers
foreach (CampaignCredit cc in ccs)
{
sb.Append(CAMPAIGN_CREDIT_TRANSACTION_TYPE);
sb.Append(DELIMITER);
sb.Append(cc.PromoterK);
sb.Append(DELIMITER);
sb.Append(cc.BuyableObjectType.ToString());
//Event
//Banner
//GuestlistCredit
//CampaignCredit
//Invoice
sb.Append(DELIMITER);
sb.Append(cc.InvoiceItemType.ToString());
//Misc
//EventDonate
//BannerTop
//BannerHotbox
//BannerPhoto
//BannerEmail
//BannerSkyscraper
//DesignBannerJpg
//DesignBannerAnimatedGif
//DesignBannerFlash
//CampaignCredits
//Combinations:
//Event - EventDonate (event hilights) [-ve]
//Banner - Misc (campaign credit refunds for underperforming banners) [+ve]
//Banner - BannerTop, BannerHotbox, BannerPhoto, BannerEmail or BannerSkyscraper (booking banners, occasionally adjustments) [-ve, occasionally +ve]
//Banner - DesignBannerJpg, DesignBannerAnimatedGif or DesignBannerFlash (booking banner design) [-ve]
//GuestlistCredit - Misc (refunds of old guestlist credit balances as campaign credits) [+ve]
//CampaignCredits - Misc (occasional adjustmants) [+ve and -ve]
//Invoice - Misc (bulk purchased campaign credits or adjustmants) [+ve and -ve]
//Invoice - CampaignCredits (instant banner purchases, 2-for-1 offer on ticket funds, manual invoices) [+ve and -ve]
sb.Append(DELIMITER);
if (cc.BuyableObjectType == Model.Entities.ObjectType.Invoice)
{
try
{
if (cc.BuyableObject != null)
{
sb.Append(INVOICE_K_PREFIX);
sb.Append(cc.BuyableObjectK.ToString("000000"));
}
}
catch { }
}
sb.Append(DELIMITER);
sb.Append(cc.ActionDateTime.ToString("dd/MM/yy"));
sb.Append(DELIMITER);
sb.Append(cc.Credits.ToString());
sb.Append(NEWLINE);
}
#endregion
}
return sb.ToString();
}
public StringBuilder GenerateReportStringBuilder(bool linksEnabled)
{
StringBuilder sb = new StringBuilder();
sb.Append(@"<form id='form1' runat='server'><div style='font-family:Verdana;'><table width='100%' border='0' cellspacing='0' cellpadding='0' height='100%'><tr><td valign='top'>
<table width='100%'>");
sb.Append(Utilities.GenerateHTMLHeaderRowString(this.Type.Equals(TransferTypes.Payment) ? "RECEIPT" : "REMITTANCE ADVICE"));
sb.Append(@"<tr>
<td align='left' valign='top' style='padding-left:48px;'>");
if (this.Promoter != null)
{
if (this.Promoter.AccountsName.Length > 0)
{
sb.Append(this.Promoter.AccountsName);
sb.Append("<br>");
}
else if (this.Usr != null && this.Usr.FullName.Length > 0 && this.Usr.IsPromoter && this.Usr.IsPromoterK(this.K))
{
sb.Append(this.Usr.FullName);
sb.Append("<br>");
}
else if (this.Promoter.PrimaryUsr != null && this.Promoter.PrimaryUsr.FullName.Length > 0)
{
sb.Append(this.Promoter.PrimaryUsr.FullName);
sb.Append("<br>");
}
if (this.Promoter.Name.Length > 0)
{
sb.Append(this.Promoter.Name);
sb.Append("<br>");
}
sb.Append(this.Promoter.AddressHtml);
}
else if (this.Usr != null)
{
if (this.Usr.FullName.Length > 0)
{
sb.Append(this.Usr.FullName);
sb.Append("<br>");
}
sb.Append(this.Usr.AddressHtml());
}
sb.Append(@"</td><td width='350'></td><td valign='top' width='100'>Transfer No.<br><br>Acc No.<br><br>Date</td>
<td align='right' valign='top' width='125'>");
sb.Append("TRN");
sb.Append(this.K.ToString());
sb.Append("<br><br>");
if (this.Promoter != null)
sb.Append(this.PromoterK.ToString());
else if (this.Usr != null)
sb.Append(this.UsrK.ToString());
else
sb.Append(" ");
sb.Append("<br><br>");
sb.Append(this.DateTimeCreated.ToString("dd/MM/yy"));
sb.Append("</td></tr>");
//if (this.DateTimeComplete != null && this.DateTimeComplete > DateTime.MinValue)
//{
// sb.Append(@"<br><br><b>Date Completed</b><br>" + this.DateTimeComplete.ToShortDateString());
//}
sb.Append(@"</table><br><br>
<table width='100%' cellspacing='0' cellpadding='3' class='BorderBlack Top Bottom Right'>
<tr>
<td style='vertical-align:bottom;' class='BorderBlack Bottom Left' width='250'><b>Transfer</b></td>
<td style='vertical-align:bottom;' class='BorderBlack Bottom Left' width='85'><b>Method</b></td>
<td style='vertical-align:bottom;' class='BorderBlack Bottom Left' width='160'><b>Method Ref#</b></td>
<td style='vertical-align:bottom;' class='BorderBlack Bottom Left' width='90'><b>Status</b></td>
<td style='vertical-align:bottom;' class='BorderBlack Bottom Left' align='left' width='85'><b>Amount</b></td>
</tr>");
sb.Append(@"<tr><td class='BorderBlack Left'>" + this.TypeToString + @"</td>" +
"<td class='BorderBlack Left'>" + Utilities.CamelCaseToString(this.Method.ToString()).Replace(" ", " ") + @"</td>
<td class='BorderBlack Left'>");
sb.Append(this.ReferenceNumberToHtml());
sb.Append(@"</td>
<td class='BorderBlack Left'>" + this.Status.ToString() + @"</td>
<td class='BorderBlack Left' align='right'>" + Utilities.MoneyToHTML(this.Amount) + @"</td></tr></table><br><br>");
if (InvoiceTransfers.Count > 0)
{ // Rename "Date" to "Tax Date", as per Dave's request 7/2/07
sb.Append(@"<table width='100%' cellspacing='0' cellpadding='3' class='BorderBlack Top Bottom Right'>
<tr>
<td style='vertical-align:bottom;' class='BorderBlack Bottom Left' width='305'><b>Items Applied To</b></td>
<td style='vertical-align:bottom;' class='BorderBlack Bottom Left' width='65'><b>Tax Date</b></td>
<td style='vertical-align:bottom;' class='BorderBlack Bottom Left' width='150'><b>Total Invoice Amount</b></td>
<td style='vertical-align:bottom;' class='BorderBlack Bottom Left' width='150'><b>Amount Applied To Invoice</b></td>
</tr>");
foreach (InvoiceTransfer invoiceTransfer in InvoiceTransfers)
{
Invoice invoice = new Invoice(invoiceTransfer.InvoiceK);
sb.Append(@"<tr>
<td class='BorderBlack Left'>");
if (linksEnabled)
{
sb.Append(invoice.Link());
}
else
sb.Append(invoice.TypeAndK);
// Replacing CreatedDateTime with TaxDateTime, as per Gee's request for OASIS v1.5
sb.Append(@"</td>
<td class='BorderBlack Left'>" + invoice.TaxDateTime.ToString("dd/MM/yy") + @"</td>
<td class='BorderBlack Left' align='right'>" + Utilities.MoneyToHTML(invoice.Total) + @"</td>
<td class='BorderBlack Left' align='right'>" + Utilities.MoneyToHTML(invoiceTransfer.Amount) + @"</td>
</tr>");
}
sb.Append(@"</table><br>");
}
Query refundTransferQuery = new Query();
if(this.Type.Equals(TransferTypes.Payment))
refundTransferQuery.QueryCondition = new And(new Q(Transfer.Columns.TransferRefundedK, this.K),
new Or(new Q(Transfer.Columns.Status, Transfer.StatusEnum.Pending),
new Q(Transfer.Columns.Status, Transfer.StatusEnum.Success)));
else
refundTransferQuery.QueryCondition = new And(new Q(Transfer.Columns.K, this.TransferRefundedK),
new Or(new Q(Transfer.Columns.Status, Transfer.StatusEnum.Pending),
new Q(Transfer.Columns.Status, Transfer.StatusEnum.Success)));
TransferSet refundTransferSet = new TransferSet(refundTransferQuery);
if (refundTransferSet.Count > 0)
{
sb.Append(@"<br><table width='100%' cellspacing='0' cellpadding='3' class='BorderBlack Top Bottom Right'>
<tr>
<td style='vertical-align:bottom;' class='BorderBlack Bottom Left' width='250'><b>");
if (this.Type.Equals(TransferTypes.Payment))
{
if (this.Method == Methods.TicketSales)
sb.Append(Utilities.CamelCaseToString(Methods.TicketSales.ToString()) + " Release");
else
sb.Append(TransferTypes.Refund.ToString());
}
else if (this.Type.Equals(TransferTypes.Refund))
sb.Append(TransferTypes.Payment.ToString());
sb.Append(@"</b></td>
<td style='vertical-align:bottom;' class='BorderBlack Bottom Left' width='85'><b>Method</b></td>
<td style='vertical-align:bottom;' class='BorderBlack Bottom Left' width='160'><b>Method Ref#</b></td>
<td style='vertical-align:bottom;' class='BorderBlack Bottom Left' width='90'><b>Status</b></td>
<td style='vertical-align:bottom;' class='BorderBlack Bottom Left' align='left' width='85'><b>Amount</b></td>
</tr>");
foreach (Transfer refundTransfer in refundTransferSet)
{
sb.Append(@"<tr>
<td class='BorderBlack Left'>");
if (linksEnabled)
{
if (refundTransfer.TicketPromoterEvent != null)
sb.Append(Utilities.Link(refundTransfer.TicketPromoterEvent.UrlReport(), refundTransfer.TypeAndK));
else
sb.Append(refundTransfer.Link());
}
else
sb.Append(refundTransfer.TypeAndK);
sb.Append(@"</td>
<td class='BorderBlack Left'>" + Utilities.CamelCaseToString(refundTransfer.Method.ToString()) + @"</td>" +
"<td class='BorderBlack Left'>");
sb.Append(refundTransfer.ReferenceNumberToHtml());
sb.Append(@"</td>
<td class='BorderBlack Left'>" + refundTransfer.Status.ToString()
+ @"</td>
<td class='BorderBlack Left' align='right'>" + Utilities.MoneyToHTML(refundTransfer.Amount) + @"</td></tr>");
}
sb.Append(@"</table><br>");
}
// DSI Registration Footer
sb.Append(Utilities.GenerateHTMLFooterRowString());
sb.Append(@"</table></div></form>");
return sb;
}