private void Page_Load(object sender, System.EventArgs e)
{
Usr.KickUserIfNotAdmin("Must be admin");
string[] dateAry = Request.QueryString["date"].Split('/');
DateTime d1, d2;
if (dateAry.Length == 1)
{
d1 = new DateTime(int.Parse(dateAry[0]), 1, 1);
d2 = d1.AddYears(1);
}
else if (dateAry.Length == 2)
{
d1 = new DateTime(int.Parse(dateAry[0]), int.Parse(dateAry[1]), 1);
d2 = d1.AddMonths(1);
}
else
{
d1 = new DateTime(int.Parse(dateAry[0]), int.Parse(dateAry[1]), int.Parse(dateAry[2]));
d2 = d1.AddDays(1);
}
StringBuilder s = new StringBuilder();
for (DateTime d = d1; d < d2; d = d.AddDays(1))
{
Query q = new Query();
q.Columns = new ColumnSet(
InvoiceItem.Columns.K,
InvoiceItem.Columns.InvoiceK,
InvoiceItem.Columns.KeyData,
InvoiceItem.Columns.Price,
InvoiceItem.Columns.Type,
InvoiceItem.Columns.Vat,
Banner.Columns.Position);
q.TableElement = new Join(
new Join(InvoiceItem.Columns.KeyData, Banner.Columns.K, QueryJoinType.Left, new Q(InvoiceItem.Columns.Type, InvoiceItem.Types.Banner)),
Bobs.Invoice.Columns.K,
InvoiceItem.Columns.InvoiceK);
q.QueryCondition = new And(
new Q(Bobs.Invoice.Columns.Paid, true),
new Q(Bobs.Invoice.Columns.PaidDateTime, QueryOperator.GreaterThanOrEqualTo, d),
new Q(Bobs.Invoice.Columns.PaidDateTime, QueryOperator.LessThan, d.AddDays(1)));
InvoiceItemSet iis = new InvoiceItemSet(q);
Hashtable total = new Hashtable();
Hashtable vat = new Hashtable();
Hashtable taxCodes = new Hashtable();
foreach (InvoiceItem ii in iis)
{
if (taxCodes[ii.NominalCode] == null)
taxCodes[ii.NominalCode] = ii.TaxCode;
if (total[ii.NominalCode] == null)
{
total[ii.NominalCode] = ii.Price;
vat[ii.NominalCode] = ii.Vat;
}
else
{
total[ii.NominalCode] = (decimal)total[ii.NominalCode] + ii.Price;
vat[ii.NominalCode] = (decimal)vat[ii.NominalCode] + ii.Vat;
}
}
foreach (object ob in total.Keys)
{
int nominal = (int)ob;
decimal vatTot = (decimal)vat[ob];
decimal totalTot = (decimal)total[ob];
int taxCode = (int)taxCodes[ob];
s.Append("BR"); // Bank Receipt
s.Append(",");
s.Append("1220"); // Cardnet account
s.Append(",");
s.Append(nominal.ToString()); // Nominal code
s.Append(",");
s.Append("0"); // Department number
s.Append(",");
s.Append(d.ToString("dd/MM/yyyy")); // Date
s.Append(",");
s.Append("ref"); // Transaction reference
s.Append(",");
s.Append("Web sales summary - "); // Transaction details
s.Append(d.ToString("yyyy-MM-dd"));
s.Append(" - n/c ");
s.Append(nominal.ToString());
s.Append(",");
s.Append(totalTot.ToString("0.00")); // Total
s.Append(",");
s.Append("T");
s.Append(taxCode.ToString()); // Tax code
s.Append(",");
s.Append(vatTot.ToString("0.00")); // Vat
s.Append("\n");
}
}
Response.Clear();
Response.Write(s.ToString());
Response.Flush();
Response.End();
//SpotterLetterRepeater.ItemTemplate=this.LoadTemplate("~/ucAdm/ucSpotterLetter.ascx");
//SpotterLetterRepeater.DataSource=us;
//SpotterLetterRepeater.DataBind();
}
protected void Page_Load(object sender, EventArgs e)
{
Stats.Controls.Add(new LiteralControl("<table cellpadding=5 cellspacing=0 border=1 xstyle=\"border:1px solid #000000;\"><tr><td>Date</td><td>Invoiced</td><td>-6</td><td>-5</td><td>-4</td><td>-3</td><td>-2</td><td>-1</td><td>+0</td><td>+1</td><td>+2</td><td>+3</td><td>+4</td><td>+5</td><td>+6</td><td>+7</td><td>+8</td><td>+9</td><td>+10</td><td>+11</td><td>+12</td></tr>"));
//for (DateTime dtMonth = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1); dtMonth <= new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1); dtMonth = dtMonth.AddMonths(1))
for (DateTime dtMonth = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1).AddMonths(-12); dtMonth <= new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1); dtMonth = dtMonth.AddMonths(1))
{
Stats.Controls.Add(new LiteralControl("<tr><td>" + dtMonth.Year.ToString() + "-" + dtMonth.Month.ToString("00") + "</td>"));
decimal Total = 0.0m;
{
Query q = new Query();
q.Columns = new ColumnSet();
q.ExtraSelectElements.Add("sum", "SUM([Invoice].[Price])");
q.QueryCondition = new And(
new Q(Invoice.Columns.BuyerType, BuyerType),
new Q(Invoice.Columns.CreatedDateTime, QueryOperator.GreaterThanOrEqualTo, dtMonth),
new Q(Invoice.Columns.CreatedDateTime, QueryOperator.LessThan, dtMonth.AddMonths(1)));
InvoiceSet ins = new InvoiceSet(q);
if (ins.Count > 0 && !string.IsNullOrEmpty(ins[0].ExtraSelectElements["sum"].ToString()))
Total = (decimal) ins[0].ExtraSelectElements["sum"];
}
Stats.Controls.Add(new LiteralControl("<td>£ " + Total.ToString("#,##0") + "</td>"));
Hashtable revenueBins = new Hashtable();
{
Query q = new Query();
q.TableElement = new Join(InvoiceItem.Columns.InvoiceK, Invoice.Columns.K);
q.QueryCondition = new And(
new Q(Invoice.Columns.BuyerType, BuyerType),
new Q(Invoice.Columns.CreatedDateTime, QueryOperator.GreaterThanOrEqualTo, dtMonth),
new Q(Invoice.Columns.CreatedDateTime, QueryOperator.LessThan, dtMonth.AddMonths(1)));
InvoiceItemSet iis = new InvoiceItemSet(q);
foreach (InvoiceItem ii in iis)
{
int totalDays = ((TimeSpan)(ii.RevenueEndDate - ii.RevenueStartDate)).Days;
if (totalDays > 0)
{
for (DateTime month = new DateTime(ii.RevenueStartDate.Year, ii.RevenueStartDate.Month, 1); month < ii.RevenueEndDate; month = month.AddMonths(1))
{
DateTime start = ii.RevenueStartDate > month ? ii.RevenueStartDate : month;
DateTime end = ii.RevenueEndDate < month.AddMonths(1) ? ii.RevenueEndDate : month.AddMonths(1);
int thisBinDays = ((TimeSpan)(end - start)).Days;
decimal revenueInThisBin = ii.Price * (decimal)thisBinDays / (decimal)totalDays;
AddToBin(revenueBins, month, dtMonth, revenueInThisBin);
}
}
else
{
AddToBin(revenueBins, ii.RevenueStartDate, dtMonth, ii.Price);
}
}
}
for (int bin = -6; bin <= 12; bin++)
{
try
{
//Stats.Controls.Add(new LiteralControl("<td>" + ((double)revenueBins[bin]).ToString() + "</td>"));
Stats.Controls.Add(new LiteralControl("<td>" + (bin == 0 ? "<b>" : "") + Math.Round(100 * (decimal)revenueBins[bin] / Total).ToString("0") + "%" + (bin == 0 ? "</b>" : "") + "</td>"));
}
catch
{
Stats.Controls.Add(new LiteralControl("<td> </td>"));
}
}
Stats.Controls.Add(new LiteralControl("</tr>"));
}
Stats.Controls.Add(new LiteralControl("</table>"));
}
public StringBuilder GenerateReportStringBuilder(bool linksEnabled)
{
DateTime taxDate = DateTime.Now;
if (this.TaxDateTime != null && this.TaxDateTime > DateTime.MinValue)
taxDate = this.TaxDateTime;
StringBuilder sb = new StringBuilder();
decimal amountPaid = 0;
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(Invoice.Types.Credit) ? this.TypeToString.ToUpper() + " NOTE" : this.TypeToString.ToUpper()));
sb.Append(@"<tr>
<td colspan=1 align='left' valign='top' width='450' 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());
}
// Addition of Created and renaming of "Date" to "Tax Date", as per Dave's request 7/2/07
sb.Append(@"</td><td width='350'></td><td valign='top' width='100'>" + this.TypeToString + " No.");
if (this.PurchaseOrderNumber.Length > 0)
sb.Append("<br><br>Purchase Order No.");
sb.Append("<br><br>Acc No.<br><br>Created<br><br>Tax Date");
if (this.Type.Equals(Invoice.Types.Invoice))
sb.Append("<br><br>Due Date");
sb.Append(@"</td><td align='right' valign='top' width='125'>");
if (this.Type.Equals(Invoice.Types.Invoice))
sb.Append("INV");
else
sb.Append("CRD");
sb.Append(this.K.ToString());
if (this.PurchaseOrderNumber.Length > 0)
sb.Append("<br><br><nobr>" + this.PurchaseOrderNumber.Replace("-", "‑"));
sb.Append("</nobr><br><br>");
if (this.Promoter != null)
sb.Append(this.PromoterK.ToString());
else if (this.Usr != null)
sb.Append(this.UsrK.ToString());
else
sb.Append(" ");
// Addition of Created, as per Dave's request 7/2/07
sb.Append("<br><br>");
sb.Append(this.CreatedDateTime.ToString("dd/MM/yy"));
sb.Append("<br><br>");
// Replacing CreatedDateTime with TaxDateTime, as per Gee's request for OASIS v1.5
//sb.Append(this.CreatedDateTime.ToString("dd/MM/yy"));
sb.Append(this.TaxDateTime.ToString("dd/MM/yy"));
if (this.Type.Equals(Invoice.Types.Invoice))
{
sb.Append("<br><br>");
sb.Append(this.DueDateTime.ToString("dd/MM/yy"));
}
sb.Append("</td></tr>");
//if(this.PaidDateTime != null && this.PaidDateTime > DateTime.MinValue)
//{
// sb.Append(@"<br><br><b>Date Completed</b><br>" + this.PaidDateTime.ToShortDateString());
//}
sb.Append(@"</table><br><br>
<table width='100%' cellspacing='0' cellpadding='3' class='BorderBlack Top Right Bottom'>
<tr>
<td style='vertical-align:bottom;' class='BorderBlack Bottom Left' width='350'><b>Item</b></td>
<td style='vertical-align:bottom;' class='BorderBlack Bottom Left' width='55' align='left'><b>Tax Code</b></td>
<td style='vertical-align:bottom;' class='BorderBlack Bottom Left' width='90' align='left'><b>Price</b></td>
<td style='vertical-align:bottom;' class='BorderBlack Bottom Left' width='85' align='left'><b>VAT</b></td>
<td style='vertical-align:bottom;' class='BorderBlack Bottom Left' width='90' align='left'><b>Total</b></td>
</tr>");
Query InvoiceItemQuery = new Query(new Q(InvoiceItem.Columns.InvoiceK, this.K));
InvoiceItemSet invoiceItemSet = new InvoiceItemSet(InvoiceItemQuery);
List<InvoiceItem.VATCodes> invoiceItemVatCodes = new List<InvoiceItem.VATCodes>();
foreach (InvoiceItem invoiceItem in invoiceItemSet)
{
if (!invoiceItemVatCodes.Contains(invoiceItem.VatCode))
invoiceItemVatCodes.Add(invoiceItem.VatCode);
sb.Append(@"<tr>
<td class='BorderBlack Left'>" + invoiceItem.Description +
(invoiceItem.Discount > 0 ? " <small>@ " + invoiceItem.Discount.ToString("P2") + " discount</small>" : "") +
@"</td>");
if (!this.VatCode.Equals(Invoice.VATCodes.T1))
{
sb.Append(@"<td class='BorderBlack Left' width='55' align='left'>" + this.VatCode.ToString() + @"</td>");
}
else
{
sb.Append(@"<td class='BorderBlack Left' width='55' align='left'>" + invoiceItem.VatCode.ToString() + @"</td>");
}
sb.Append(@"<td class='BorderBlack Left' width='90' align='right'>" + Utilities.MoneyToHTML(invoiceItem.Price) + @"</td>
<td class='BorderBlack Left' width='85' align='right'>" + Utilities.MoneyToHTML(invoiceItem.Vat) + @"</td>
<td class='BorderBlack Left' width='90' align='right'>" + Utilities.MoneyToHTML(invoiceItem.Total) + @"</td>
</tr>");
}
sb.Append(@"<tr>
<td class='BorderBlack Top Left' colspan='2' align='right'><b>TOTAL:</b></td>
<td class='BorderBlack Top Left' width='90' align='right'><b>" + Utilities.MoneyToHTML(this.Price) + @"</b></td>
<td class='BorderBlack Top Left' width='85' align='right'><b>" + Utilities.MoneyToHTML(this.Vat) + @"</b></td>
<td class='BorderBlack Top Left' width='90' align='right'><b>" + Utilities.MoneyToHTML(this.Total) + @"</b></td>
</tr></table>");
InvoiceItem.VATCodes[] invoiceItemVatCodeArray = invoiceItemVatCodes.ToArray();
Array.Sort(invoiceItemVatCodeArray);
sb.Append("<small><i><b>VAT Rate:</b> ");
if (this.VatCode.Equals(Invoice.VATCodes.T1) && invoiceItemVatCodeArray.Length > 0)
{
foreach (InvoiceItem.VATCodes vatCode in invoiceItemVatCodeArray)
{
sb.Append(vatCode.ToString());
sb.Append("=");
sb.Append(InvoiceItem.VATRate(vatCode, taxDate).ToString("0.0%"));
sb.Append(" ");
}
}
else
{
sb.Append(this.VatCode.ToString());
sb.Append("=");
sb.Append(Invoice.VATRate(this.VatCode, taxDate).ToString("0.0%"));
}
sb.Append("</i></small><br>");
sb.Append("<br>");
Query InvoiceTransferQuery = new Query(new And(new Q(InvoiceTransfer.Columns.InvoiceK, this.K),
new Or(new Q(Transfer.Columns.Status, Transfer.StatusEnum.Pending),
new Q(Transfer.Columns.Status, Transfer.StatusEnum.Success),
new Q(Transfer.Columns.Status, Transfer.StatusEnum.Cancelled))));
InvoiceTransferQuery.TableElement = new Join(InvoiceTransfer.Columns.TransferK, Transfer.Columns.K);
InvoiceTransferSet invoiceTransferSet = new InvoiceTransferSet(InvoiceTransferQuery);
bool nonSuccessfulTransfer = false;
if (invoiceTransferSet.Count > 0)
{
sb.Append(@"<br><table width='100%' cellspacing='0' cellpadding='3' class='BorderBlack Top Right Bottom'>
<tr>
<td style='vertical-align:bottom;' class='BorderBlack Bottom Left' width='205'><b>Transfer</b></td>
<td style='vertical-align:bottom;' class='BorderBlack Bottom Left' width='65' align='left'><b>Date</b></td>
<td style='vertical-align:bottom;' class='BorderBlack Bottom Left' width='75' align='left'><b>Method</b></td>
<td style='vertical-align:bottom;' class='BorderBlack Bottom Left' width='150' align='left'><b>Method Ref#</b></td>
<td style='vertical-align:bottom;' class='BorderBlack Bottom Left' width='90' align='left'><b>Status</b></td>
<td style='vertical-align:bottom;' class='BorderBlack Bottom Left' width='85' align='left'><b>Amount</b></td>
</tr>");
foreach (InvoiceTransfer invoiceTransfer in invoiceTransferSet)
{
Transfer transfer = new Transfer(invoiceTransfer.TransferK);
sb.Append(@"<tr>
<td class='BorderBlack 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' width='65' align='left'>" + transfer.DateTimeCreated.ToString("dd/MM/yy") + @"</td>
<td class='BorderBlack Left' width='75' align='left'><nobr>" + Utilities.CamelCaseToString(transfer.Method.ToString()) + @"</nobr></td>
<td class='BorderBlack Left' width='150' align='left'>");
sb.Append(transfer.ReferenceNumberToHtml());
sb.Append(@"</td>
<td class='BorderBlack Left' width='90' align='left'>" + transfer.Status.ToString() + @"</td>
<td class='BorderBlack Left' width='85' align='right'>" + Utilities.MoneyToHTML(invoiceTransfer.Amount) + @"</td>
</tr>");
if (!transfer.Status.Equals(Transfer.StatusEnum.Success))
nonSuccessfulTransfer = true;
else
amountPaid += invoiceTransfer.Amount;
}
sb.Append(@"</table>");
// For now, this note only pertains to Invoices
if (this.Type.Equals(Invoice.Types.Invoice) && nonSuccessfulTransfer == true)
sb.Append(@"<small><i>(<b>Note:</b> Only successful transfers will be applied to the payment total. Pending and cancelled transfers will not be applied)</i></small><br>");
sb.Append("<br>");
}
Query InvoiceCreditQuery = new Query();
if (this.Type.Equals(Invoice.Types.Invoice))
{
InvoiceCreditQuery = new Query(new Q(InvoiceCredit.Columns.InvoiceK, this.K));
}
else
{
InvoiceCreditQuery = new Query(new Q(InvoiceCredit.Columns.CreditInvoiceK, this.K));
}
InvoiceCreditSet invoiceCreditSet = new InvoiceCreditSet(InvoiceCreditQuery);
if (invoiceCreditSet.Count > 0)
{
string invoiceHeader = "Invoice";
Invoice invoice = new Invoice();
if (this.Type.Equals(Invoice.Types.Invoice))
invoiceHeader = "Credit";
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='335'><b>" + invoiceHeader + @"</b></td>
<td style='vertical-align:bottom;' class='BorderBlack Bottom Left' width='70' align='left'><b>Date</b></td>
<td style='vertical-align:bottom;' class='BorderBlack Bottom Left' width='90' align='left'><b>Price</b></td>
<td style='vertical-align:bottom;' class='BorderBlack Bottom Left' width='85' align='left'><b>VAT</b></td>
<td style='vertical-align:bottom;' class='BorderBlack Bottom Left' width='90' align='left'><b>Total</b></td>
</tr>");
foreach (InvoiceCredit invoiceCredit in invoiceCreditSet)
{
if (this.Type.Equals(Invoice.Types.Invoice))
{
// Credit amounts are negative
amountPaid -= invoiceCredit.Amount;
invoice = new Invoice(invoiceCredit.CreditInvoiceK);
}
else
invoice = new Invoice(invoiceCredit.InvoiceK);
sb.Append(@"<tr>
<td class='BorderBlack Left'>");
if (linksEnabled)
sb.Append(Utilities.Link(invoice.UrlReport(), invoiceHeader + " #" + invoice.K.ToString()));
else
sb.Append(invoiceHeader + " #" + invoice.K.ToString());
// Replacing CreatedDateTime with TaxDateTime, as per Gee's request for OASIS v1.5
sb.Append(@"</td>
<td class='BorderBlack Left' width='70' align='left'>" + invoice.TaxDateTime.ToString("dd/MM/yy") + @"</td>
<td class='BorderBlack Left' width='90' align='right'>" + Utilities.MoneyToHTML(invoice.Price) + @"</td>
<td class='BorderBlack Left' width='85' align='right'>" + Utilities.MoneyToHTML(invoice.Vat) + @"</td>
<td class='BorderBlack Left' width='90' align='right'>" + Utilities.MoneyToHTML(invoice.Total) + @"</td>
</tr>");
}
sb.Append(@"</table><br><br>");
}
if (this.Type.Equals(Invoice.Types.Invoice))
{
// Invoice Summary
sb.Append(@"<br><table width='250'>
<tr>
<td colspan=2><b>Summary</b></td>
</tr>
<tr>
<td width='135'>Invoice Total:</td>
<td width='115' align='right'>" + Utilities.MoneyToHTML(this.Total) + @"</td>
</tr>
<tr>
<td width='135'>Payment Total:</td>
<td width='115' align='right'>" + Utilities.MoneyToHTML(amountPaid) + @"</td>
</tr>
<tr>
<td width='135'><b>Outstanding:</b></td>
<td width='115' align='right'><b>" + Utilities.MoneyToHTML(this.Total - amountPaid) + @"</b></td>
</tr></table><br><br>");
}
sb.Append(@"</td></tr>");
sb.Append(@"
<tr>
<td valign='bottom' align='center'>
<div style='width:50%;padding:20px;border:2px solid #000000;text-align:left;'>
Bank details for payments:<br>
Development Hell Limited<br>
Barlcays Bank PLC, Commercial Bank Basingstoke<br>
Sort Code: 20-37-63<br>
Account number: 00478377<br>
For international payments:<br>
IBAN - GB04BARC20376300478377<br>
Swift - BARCGB22
</div>
</td>
</tr>");
// DSI Registration Footer
sb.Append(Utilities.GenerateHTMLFooterRowString());
sb.Append(@"</table></div></form>");
return sb;
}
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 List<InvoiceItemDataHolder> CreateCreditInvoiceItems()
{
Query InvoiceItemQuery = new Query(new Q(InvoiceItem.Columns.InvoiceK, this.K));
InvoiceItemSet InvoiceItems = new InvoiceItemSet(InvoiceItemQuery);
List<InvoiceItemDataHolder> creditItems = new List<InvoiceItemDataHolder>();
foreach (InvoiceItem ii in InvoiceItems)
{
InvoiceItemDataHolder creditItem = new InvoiceItemDataHolder();
creditItem.BuyableObjectK = ii.BuyableObjectK;
creditItem.BuyableObjectType = ii.BuyableObjectType;
creditItem.Description = CREDIT + " INV#" + this.K.ToString() + ": " + ii.Description;
creditItem.ShortDescription = "CRD for item #" + ii.K.ToString();
//creditItem.Price = -1 * ii.Price;
//creditItem.Vat = -1 * ii.Vat;
creditItem.RevenueStartDate = DateTime.Now;
creditItem.RevenueEndDate = DateTime.Now;
creditItem.Type = ii.Type;
creditItem.VatCode = ii.VatCode;
creditItem.SetTotal(-1 * ii.Total);
creditItems.Add(creditItem);
}
return creditItems;
}
