protected void Page_Load(object sender, EventArgs e)
{
Query q = new Query();
q.ExtraSelectElements.Add("Sum", "SUM([Invoice].[Price])");
q.ExtraSelectElements.Add("Day", "DATENAME(DW,[Invoice].[CreatedDateTime])");
q.QueryCondition = new And(
new Q(Invoice.Columns.Type, Invoice.Types.Invoice),
new Q(Invoice.Columns.Price, QueryOperator.LessThan, 1000.0));
q.GroupBy = new GroupBy("DATENAME(DW,[Invoice].[CreatedDateTime])");
q.Columns = new ColumnSet();
InvoiceSet ins = new InvoiceSet(q);
Dictionary<DayOfWeek, decimal> weight = new Dictionary<DayOfWeek, decimal>();
decimal total = 0.0m;
foreach (Invoice i in ins)
{
total += (decimal)i.ExtraSelectElements["Sum"];
}
foreach (Invoice i in ins)
{
decimal fraction = (decimal)i.ExtraSelectElements["Sum"] / total;
switch ((string)i.ExtraSelectElements["Day"])
{
case "Monday": weight[DayOfWeek.Monday] = fraction; break;
case "Tuesday": weight[DayOfWeek.Tuesday] = fraction; break;
case "Wednesday": weight[DayOfWeek.Wednesday] = fraction; break;
case "Thursday": weight[DayOfWeek.Thursday] = fraction; break;
case "Friday": weight[DayOfWeek.Friday] = fraction; break;
case "Saturday": weight[DayOfWeek.Saturday] = fraction; break;
case "Sunday": weight[DayOfWeek.Sunday] = fraction; break;
default: break;
}
}
Stats.Controls.Add(new LiteralControl("<table><tr><td>Month</td><td>Year</td><td>Weight</td><td>Actual revenue</td><td>Weighted revenue</td></tr>"));
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))
{
try
{
decimal monthWeight = 0.0m;
for (DateTime dtDay = dtMonth; dtDay < dtMonth.AddMonths(1) && dtDay < DateTime.Today; dtDay = dtDay.AddDays(1))
{
monthWeight += weight[dtDay.DayOfWeek];
}
Query qMonth = new Query();
qMonth.ExtraSelectElements.Add("Count", "SUM([Invoice].[Price])");
qMonth.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)),
new Q(Invoice.Columns.CreatedDateTime, QueryOperator.LessThan, DateTime.Today));
qMonth.Columns = new ColumnSet();
InvoiceSet isMonth = new InvoiceSet(qMonth);
decimal actualRevenue = (decimal)isMonth[0].ExtraSelectElements["Count"];
decimal revenuePerWeek = actualRevenue / monthWeight;
decimal revenuePerMonth = revenuePerWeek * 4.345238095m;
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>+0</td><td>+1</td><td>+2</td><td>+3</td><td>+4</td><td>Unpaid</td></tr>"));
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"];
}
public bool Invoice(OrderSet order)
{
try
{
InvoiceSet _invoice = new InvoiceSet();
GenericImplement <InvoiceSet> _GenericImplement = new GenericImplement <InvoiceSet>();
_invoice.OrderSet = db.OrderSet.Find(order.IdOrder);
_invoice.InvoiceNumber = db.InvoiceSet.Count() + 1;
_invoice.InvoiceDate = DateTime.Now;
_invoice.HTAmount = order.HTAmount;
_invoice.TTCAmount = order.TTCAmount;
_invoice.TotalOfLines = (short)_invoice.InvoiceLineSet.Count();
_invoice.Order_Id = order.IdOrder;
_GenericImplement.Insert(_invoice);
return(true);
}
catch (Exception)
{
return(false);
}
}
private static void AddAppliedInvoices(Mailer mailer, Transfer transfer)
{
Query invoiceQuery = new Query(new Q(InvoiceTransfer.Columns.TransferK, transfer.K));
invoiceQuery.TableElement = new Join(InvoiceTransfer.Columns.InvoiceK, Invoice.Columns.K);
invoiceQuery.OrderBy = new OrderBy(Invoice.Columns.K, OrderBy.OrderDirection.Ascending);
InvoiceSet invoices = new InvoiceSet(invoiceQuery);
if (invoices.Count > 0)
{
mailer.Body += "<p>Applied to the following:</p>";
AddInvoicesToEmail(mailer, invoices);
}
}
public static void AddInvoicesToEmail(Mailer mailer, InvoiceSet invoiceSet)
{
List<Invoice> invoices = new List<Invoice>();
foreach (Invoice invoice in invoiceSet)
invoices.Add(invoice);
AddInvoicesToEmail(mailer, invoices);
}
private void GenerateTeamBonusTargetWeek(DateTime dt, HtmlGenericControl control, List<UsrDataHolder> salesUsrs)
{
DateTime weekStart = new DateTime(dt.Year, dt.Month, dt.Day, 0, 0, 0);
if (dt.DayOfWeek == DayOfWeek.Saturday)
weekStart = weekStart;
else if (dt.DayOfWeek == DayOfWeek.Sunday)
weekStart = weekStart.AddDays(-1);
else if (dt.DayOfWeek == DayOfWeek.Monday)
weekStart = weekStart.AddDays(-2);
else if (dt.DayOfWeek == DayOfWeek.Tuesday)
weekStart = weekStart.AddDays(-3);
else if (dt.DayOfWeek == DayOfWeek.Wednesday)
weekStart = weekStart.AddDays(-4);
else if (dt.DayOfWeek == DayOfWeek.Thursday)
weekStart = weekStart.AddDays(-5);
else if (dt.DayOfWeek == DayOfWeek.Friday)
weekStart = weekStart.AddDays(-6);
DateTime weekEnd = weekStart.AddDays(7);
#region Sales: Total Money
Query totalSalesAmountQuery = new Query(new And(new Q(Usr.Columns.IsSalesPerson, true),
new Or(new Q(Usr.Columns.SalesTeam, Usr.SalesTeams.PromoterSalesTeam), new Q(Usr.Columns.SalesTeam, Usr.SalesTeams.SmallBusinessSalesTeam)),
new Q(Invoice.Columns.SalesUsrAmount, QueryOperator.NotEqualTo, 0),
new Or(new And(new Q(Invoice.Columns.Type, Invoice.Types.Invoice),
new Q(Invoice.Columns.Paid, true),
new Q(Invoice.Columns.PaidDateTime, QueryOperator.GreaterThanOrEqualTo, weekStart),
new Q(Invoice.Columns.PaidDateTime, QueryOperator.LessThan, weekEnd)),
new And(new Q(Invoice.Columns.Type, Invoice.Types.Credit),
new Q(Invoice.Columns.CreatedDateTime, QueryOperator.GreaterThanOrEqualTo, weekStart),
new Q(Invoice.Columns.CreatedDateTime, QueryOperator.LessThan, weekEnd)))));
totalSalesAmountQuery.TableElement = new Join(Usr.Columns.K, Invoice.Columns.SalesUsrK, QueryJoinType.Left);
totalSalesAmountQuery.ExtraSelectElements.Add("TotalSales", "SUM(ISNULL([Invoice].[SalesUsrAmount],0))");
totalSalesAmountQuery.Columns = new ColumnSet(Invoice.Columns.SalesUsrK, Usr.Columns.FirstName);
totalSalesAmountQuery.OrderBy = new OrderBy("SUM(ISNULL([Invoice].[SalesUsrAmount],0)) desc, [Usr].[FirstName]");
totalSalesAmountQuery.GroupBy = new GroupBy("[Usr].[FirstName], [Invoice].[SalesUsrK]");
InvoiceSet invoices = new InvoiceSet(totalSalesAmountQuery);
#endregion
#region Table
HtmlTable salesMonthTable = new HtmlTable();
salesMonthTable.Rows.Clear();
#endregion
//width="100%" cellpadding="3" cellspacing="0" class="dataGrid"
salesMonthTable.Width = "100%";
salesMonthTable.CellPadding = 3;
salesMonthTable.CellSpacing = 0;
salesMonthTable.Attributes["class"] = "dataGrid";
#region Header Row
HtmlTableRow headerRow = new HtmlTableRow();
headerRow.Attributes.Add("class", "dataGridHeader");
salesMonthTable.Rows.Add(headerRow);
HtmlTableCell[] headerTableCells = new HtmlTableCell[2];
for (int i = 0; i < headerTableCells.Length; i++)
{
headerTableCells[i] = new HtmlTableCell("th");
headerTableCells[i].Align = "right";
headerTableCells[i].InnerHtml = " ";
headerRow.Cells.Add(headerTableCells[i]);
}
headerTableCells[0].Style.Add("border-left", "0px;");
//headerTableCells[1].InnerHtml = "<img src=\"/gfx/money-gbp-small.gif\" alt=\"Money\" align=\"right\" height=\"11\" width=\"13\"/>";
#endregion
#region Data Rows
HtmlTableRow[] dataRows = new HtmlTableRow[salesUsrs.Count];
for (int i = 0; i < dataRows.Length; i++)
{
dataRows[i] = new HtmlTableRow();
HtmlTableCell[] dataTableCells = new HtmlTableCell[2];
dataTableCells[0] = new HtmlTableCell();
dataTableCells[1] = new HtmlTableCell();
dataTableCells[1].Align = "right";
dataTableCells[1].InnerHtml = "£0";
dataTableCells[0].Style.Add("border-left", "0px;");
if (i == dataRows.Length - 1)
{
dataTableCells[0].Style.Add("border-bottom", "0px;");
dataTableCells[1].Style.Add("border-bottom", "0px;");
}
if (invoices.Count > i)
{
dataTableCells[0].InnerHtml = invoices[i].ExtraSelectElements["Usr_FirstName"].ToString();
dataTableCells[1].InnerHtml = "£" + Convert.ToDouble(invoices[i].ExtraSelectElements["TotalSales"]).ToString("#,##0");
}
//for (int m = 0; m < invoices.Count; m++)
//{
// if (invoices[m].SalesUsrK == salesUsrs[i].K)
// {
// break;
// }
//}
salesMonthTable.Rows.Add(dataRows[i]);
for (int j = 0; j < dataTableCells.Length; j++)
{
dataRows[i].Cells.Add(dataTableCells[j]);
}
}
// Populate remaining names to table. Since not all sales people will have made sales for the given month, we need to add the remaining sales people with total sales of zero
if (invoices.Count < salesUsrs.Count)
{
bool exists;
foreach (UsrDataHolder udh in salesUsrs)
{
exists = false;
foreach (HtmlTableRow row in salesMonthTable.Rows)
{
if (row.Cells[0].InnerHtml.Equals(udh.FirstName))
{
exists = true;
break;
}
}
if (!exists)
{
foreach (HtmlTableRow row in salesMonthTable.Rows)
{
if (row.Cells[0].InnerHtml.Length == 0)
{
row.Cells[0].InnerHtml = udh.FirstName;
break;
}
}
}
}
}
#endregion
control.Controls.Add(salesMonthTable);
#region Sales: Total
Query totalSalesAmountQueryTotal = new Query(new And(new Q(Usr.Columns.IsSalesPerson, true),
new Or(new Q(Usr.Columns.SalesTeam, Usr.SalesTeams.PromoterSalesTeam), new Q(Usr.Columns.SalesTeam, Usr.SalesTeams.SmallBusinessSalesTeam)),
new Q(Invoice.Columns.SalesUsrAmount, QueryOperator.NotEqualTo, 0),
new Or(new And(new Q(Invoice.Columns.Type, Invoice.Types.Invoice),
new Q(Invoice.Columns.Paid, true),
new Q(Invoice.Columns.PaidDateTime, QueryOperator.GreaterThanOrEqualTo, weekStart),
new Q(Invoice.Columns.PaidDateTime, QueryOperator.LessThan, weekEnd)),
new And(new Q(Invoice.Columns.Type, Invoice.Types.Credit),
new Q(Invoice.Columns.CreatedDateTime, QueryOperator.GreaterThanOrEqualTo, weekStart),
new Q(Invoice.Columns.CreatedDateTime, QueryOperator.LessThan, weekEnd)))));
totalSalesAmountQueryTotal.TableElement = new Join(Usr.Columns.K, Invoice.Columns.SalesUsrK, QueryJoinType.Left);
totalSalesAmountQueryTotal.ExtraSelectElements.Add("TotalSales", "SUM(ISNULL([Invoice].[SalesUsrAmount],0))");
totalSalesAmountQueryTotal.Columns = new ColumnSet();
InvoiceSet inv = new InvoiceSet(totalSalesAmountQueryTotal);
#endregion
double tot = 0.0;
double.TryParse(inv[0].ExtraSelectElements["TotalSales"].ToString(), out tot);
control.Controls.Add(new LiteralControl(@"<div style=""padding-left:4px;padding-right:4px;""><p><b>Total: " + tot.ToString("#,##0") + @"</b></p></div>"));
}
private void GenerateSalesTodayTable(List<UsrDataHolder> salesUsrs)
{
DateTime fromDate = DateTime.Today;
DateTime toDate = fromDate.AddDays(1);
if (salesUsrs.Count > 0)
{
#region Sales: Total Money
Query totalSalesAmountQuery = new Query(new And(new Q(Usr.Columns.SalesTeam, QueryOperator.GreaterThan, 0),
new Q(Invoice.Columns.SalesUsrAmount, QueryOperator.NotEqualTo, 0),
new Or(new And(new Q(Invoice.Columns.Type, Invoice.Types.Invoice),
new Q(Invoice.Columns.Paid, true),
new Q(Invoice.Columns.PaidDateTime, QueryOperator.GreaterThanOrEqualTo, fromDate),
new Q(Invoice.Columns.PaidDateTime, QueryOperator.LessThan, toDate),
new Q(Usr.Columns.SalesTeam, QueryOperator.NotEqualTo, Usr.SalesTeams.CorporateSalesTeam)),
new And(new Q(Invoice.Columns.Type, Invoice.Types.Invoice),
new Q(Invoice.Columns.TaxDateTime, QueryOperator.GreaterThanOrEqualTo, fromDate),
new Q(Invoice.Columns.TaxDateTime, QueryOperator.LessThan, toDate),
new Q(Usr.Columns.SalesTeam, Usr.SalesTeams.CorporateSalesTeam)),
new And(new Q(Invoice.Columns.Type, Invoice.Types.Credit),
new Q(Invoice.Columns.CreatedDateTime, QueryOperator.GreaterThanOrEqualTo, fromDate),
new Q(Invoice.Columns.CreatedDateTime, QueryOperator.LessThan, toDate)))));
totalSalesAmountQuery.TableElement = new Join(Usr.Columns.K, Invoice.Columns.SalesUsrK, QueryJoinType.Left);
totalSalesAmountQuery.ExtraSelectElements.Add("TotalSales", "SUM(ISNULL([Invoice].[SalesUsrAmount],0))");
totalSalesAmountQuery.Columns = new ColumnSet(Invoice.Columns.SalesUsrK, Usr.Columns.FirstName);
totalSalesAmountQuery.OrderBy = new OrderBy("[Usr].[FirstName], SUM(SalesUsrAmount)");
totalSalesAmountQuery.GroupBy = new GroupBy("[Usr].[FirstName], [Invoice].[SalesUsrK]");
InvoiceSet invoices = new InvoiceSet(totalSalesAmountQuery);
#endregion
#region Sales Calls: SalesUsrK, Minutes, and Total calls
Query salesCallMinutesQuery = new Query(new And(new Q(SalesCall.Columns.IsCall, true),
new Q(SalesCall.Columns.DateTimeStart, QueryOperator.GreaterThanOrEqualTo, fromDate),
new Q(SalesCall.Columns.DateTimeStart, QueryOperator.LessThan, toDate),
new Q(Usr.Columns.IsSalesPerson, true)));
//salesCallMinutesQuery.ExtraSelectElements.Add("Minutes", "SUM(ISNULL([SalesCall].[Duration],0))");
salesCallMinutesQuery.ExtraSelectElements.Add("TotalCalls", "COUNT([SalesCall].[Duration])");
salesCallMinutesQuery.TableElement = new Join(Usr.Columns.K, SalesCall.Columns.UsrK, QueryJoinType.Left);
salesCallMinutesQuery.Columns = new ColumnSet(SalesCall.Columns.UsrK);
salesCallMinutesQuery.OrderBy = new OrderBy("[Usr].[FirstName]");
salesCallMinutesQuery.GroupBy = new GroupBy("[Usr].[FirstName], [SalesCall].[UsrK]");
SalesCallSet salesCallsMinutes = new SalesCallSet(salesCallMinutesQuery);
#endregion
#region Table
HtmlTable salesTodayTable = this.SalesTodayTable;
salesTodayTable.Rows.Clear();
#endregion
#region Header Row
HtmlTableRow headerRow = new HtmlTableRow();
headerRow.Attributes.Add("class", "dataGridHeader");
salesTodayTable.Rows.Add(headerRow);
HtmlTableCell[] headerTableCells = new HtmlTableCell[3];
for (int i = 0; i < headerTableCells.Length; i++)
{
headerTableCells[i] = new HtmlTableCell("th");
headerTableCells[i].Align = "right";
headerTableCells[i].InnerHtml = " ";
headerRow.Cells.Add(headerTableCells[i]);
}
headerTableCells[0].Style.Add("border-left", "0px;");
//headerTableCells[1].InnerHtml = "£";
//headerTableCells[2].InnerHtml = "Ca-<br>lls";
#endregion
#region Data Rows
HtmlTableRow[] dataRows = new HtmlTableRow[salesUsrs.Count];
for (int i = 0; i < dataRows.Length; i++)
{
dataRows[i] = new HtmlTableRow();
HtmlTableCell[] dataTableCells = new HtmlTableCell[3];
for (int k = 0; k < dataTableCells.Length; k++)
{
dataTableCells[k] = new HtmlTableCell();
dataRows[i].Cells.Add(dataTableCells[k]);
}
dataTableCells[0].InnerHtml = salesUsrs[i].FirstName;
dataTableCells[0].Style.Add("border-left", "0px;");
dataTableCells[1].Align = "right";
dataTableCells[2].Align = "right";
//dataTableCells[3].Align = "right";
dataTableCells[1].InnerHtml = "£0";
dataTableCells[2].InnerHtml = "0 calls";
//dataTableCells[3].InnerHtml = "0";
if (i == dataRows.Length - 1)
{
dataTableCells[0].Style.Add("border-bottom", "0px;");
dataTableCells[1].Style.Add("border-bottom", "0px;");
dataTableCells[2].Style.Add("border-bottom", "0px;");
//dataTableCells[3].Style.Add("border-bottom", "0px;");
}
for (int m = 0; m < invoices.Count; m++)
{
if (invoices[m].SalesUsrK == salesUsrs[i].K)
{
dataTableCells[1].InnerHtml = "£" + Convert.ToDouble(invoices[m].ExtraSelectElements["TotalSales"]).ToString("#,##0");
break;
}
}
for (int n = 0; n < salesCallsMinutes.Count; n++)
{
if (salesCallsMinutes[n].UsrK == salesUsrs[i].K)
{
//dataTableCells[2].InnerHtml = Convert.ToDouble(salesCallsMinutes[n].ExtraSelectElements["Minutes"]).ToString("#,##0");
dataTableCells[2].InnerHtml = Convert.ToDouble(salesCallsMinutes[n].ExtraSelectElements["TotalCalls"]).ToString("#,##0") + " calls";
break;
}
}
salesTodayTable.Rows.Add(dataRows[i]);
}
#endregion
}
}
/// <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);
}
public void ApplyAvailableMoneyToUnpaidInvoices()
{
Query unpaidInvoiceQuery = new Query(new And(new Q(Invoice.Columns.Paid, false),
new Q(Invoice.Columns.PromoterK, this.K),
new Q(Invoice.Columns.Type, Invoice.Types.Invoice)));
unpaidInvoiceQuery.OrderBy = new OrderBy(new OrderBy(Invoice.Columns.DueDateTime), new OrderBy(Invoice.Columns.K));
InvoiceSet unpaidInvoices = new InvoiceSet(unpaidInvoiceQuery);
foreach (Invoice unpaidInvoice in unpaidInvoices)
{
unpaidInvoice.UpdateAndAutoApplySuccessfulTransfersWithAvailableMoney();
if(unpaidInvoice.Paid == true)
Utilities.EmailInvoice(unpaidInvoice, false);
// If unable to pay off invoice, then wont have any available money for other invoices
else
break;
}
}
/// <summary>
/// This calculates the total amount of money on the promoter's account that has not been applied
/// </summary>
/// <returns></returns>
public decimal GetAvailableMoney()
{
var balance = GetBalance();
Query unpaidInvoiceQuery = new Query(new And(new Q(Invoice.Columns.Paid, false),
new Q(Invoice.Columns.PromoterK, this.K)));
InvoiceSet unpaidInvoices = new InvoiceSet(unpaidInvoiceQuery);
foreach (Invoice unpaidInvoice in unpaidInvoices)
{
balance += unpaidInvoice.AmountDue;
}
return Math.Round(balance, 2);
}
public AccountStatus GetAccountStatus()
{
decimal balance = this.GetBalance();
if (balance > 0)
return AccountStatus.InCredit;
if (balance < 0)
{
Query outstandingInvoiceQuery = new Query();
outstandingInvoiceQuery.QueryCondition = new And(new Q(Invoice.Columns.PromoterK, this.K),
new Q(Invoice.Columns.Type, Invoice.Types.Invoice),
new Q(Invoice.Columns.Paid, false));
outstandingInvoiceQuery.Columns = new ColumnSet();
outstandingInvoiceQuery.ExtraSelectElements.Add("MinDueDate", "MIN(DueDateTime)");
InvoiceSet outstandingInvoices = new InvoiceSet(outstandingInvoiceQuery);
if (outstandingInvoices.Count > 0 && outstandingInvoices[0].ExtraSelectElements["MinDueDate"] != DBNull.Value)
{
if (((DateTime)outstandingInvoices[0].ExtraSelectElements["MinDueDate"]) < DateTime.Today)
return AccountStatus.Overdue;
else
return AccountStatus.Outstanding;
}
}
return AccountStatus.ZeroBalance;
}
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 decimal AmountDue(DateTime startDate, DateTime endDate)
{
decimal amountDue = 0;
// 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.Paid, false),
new Q(Invoice.Columns.PromoterK, this.K),
new Q(Invoice.Columns.Type, Invoice.Types.Invoice)));
InvoiceSet invoiceSet = new InvoiceSet(InvoiceQuery);
foreach (Invoice invoice in invoiceSet)
{
amountDue += invoice.Total - invoice.AmountPaid;
}
return amountDue;
}
public static void CreatePromoterXml(int promoterK)
{
string fileSystemPath = "";
Query qu = new Query();
qu.QueryCondition = new Q(Promoter.Columns.Status, QueryOperator.NotEqualTo, Promoter.StatusEnum.Disabled);
qu.OrderBy = new OrderBy(Promoter.Columns.Name);
if (promoterK > 0)
{
qu.QueryCondition = new And(qu.QueryCondition, new Q(Promoter.Columns.K, promoterK));
fileSystemPath = @"\\jabba\d$\Shared\Maximizer\Promoters\" + promoterK.ToString() + ".mxi";
string fileSystemPathBatch = "";
fileSystemPathBatch = @"\\jabba\d$\Shared\Maximizer\Promoters\import-" + promoterK.ToString() + ".bat";
File.Delete(fileSystemPathBatch);
using (StreamWriter swBatch = new StreamWriter(fileSystemPathBatch))
{
swBatch.Write(@"""c:\Program Files\Maximizer\Maxwin.exe"" /DATABASE ""DontStayIn"" /USERID ""MASTER"" /PASSWORD ""author72insert"" /FILE=""Z:\Maximizer\Promoters\" + promoterK.ToString() + @".mxi""");
}
}
else
{
if (Vars.DevEnv)
fileSystemPath = @"C:\inetpub\DontStayIn\PromoterXmlTmp\promoters.mxi";
else
fileSystemPath = @"\\" + Vars.ExtraIp + @"\d$\DontStayIn\Live\DontStayInTemp\promoters.mxi";
}
PromoterSet ps = new PromoterSet(qu);
File.Delete(fileSystemPath);
using (FileStream fs = File.OpenWrite(fileSystemPath))
{
XmlTextWriter x = new XmlTextWriter(fs, System.Text.Encoding.UTF8);
x.Formatting = Formatting.Indented;
#region AllData
x.WriteStartElement("AllData");
#region DetailDef NickName
x.WriteStartElement("DetailDef");
x.WriteAttributeString("Name", "NickName");
x.WriteAttributeString("Type", "String");
x.WriteAttributeString("Length", "20");
x.WriteAttributeString("Companies", "No");
x.WriteAttributeString("Individuals", "No");
x.WriteAttributeString("Contacts", "Yes");
x.WriteEndElement(); //DetailDef
#endregion
#region DetailDef Brands
x.WriteStartElement("DetailDef");
x.WriteAttributeString("Name", "Brands");
x.WriteAttributeString("Type", "String");
x.WriteAttributeString("Length", "255");
x.WriteAttributeString("Companies", "Yes");
x.WriteAttributeString("Individuals", "No");
x.WriteAttributeString("Contacts", "No");
x.WriteEndElement(); //DetailDef
#endregion
#region DetailDef Venues
x.WriteStartElement("DetailDef");
x.WriteAttributeString("Name", "Venues");
x.WriteAttributeString("Type", "String");
x.WriteAttributeString("Length", "255");
x.WriteAttributeString("Companies", "Yes");
x.WriteAttributeString("Individuals", "No");
x.WriteAttributeString("Contacts", "No");
x.WriteEndElement(); //DetailDef
#endregion
#region DetailDef Discount
x.WriteStartElement("DetailDef");
x.WriteAttributeString("Name", "Discount");
x.WriteAttributeString("Type", "Number");
x.WriteAttributeString("Length", "0");
x.WriteAttributeString("Companies", "Yes");
x.WriteAttributeString("Individuals", "No");
x.WriteAttributeString("Contacts", "No");
x.WriteEndElement(); //DetailDef
#endregion
#region DetailDef Enabled
x.WriteStartElement("DetailDef");
x.WriteAttributeString("Name", "Enabled");
x.WriteAttributeString("Type", "Number");
x.WriteAttributeString("Length", "0");
x.WriteAttributeString("Companies", "Yes");
x.WriteAttributeString("Individuals", "No");
x.WriteAttributeString("Contacts", "No");
x.WriteEndElement(); //DetailDef
#endregion
#region DetailDef SignedUp
x.WriteStartElement("DetailDef");
x.WriteAttributeString("Name", "SignedUp");
x.WriteAttributeString("Type", "Date");
x.WriteAttributeString("Length", "0");
x.WriteAttributeString("Companies", "Yes");
x.WriteAttributeString("Individuals", "No");
x.WriteAttributeString("Contacts", "No");
x.WriteEndElement(); //DetailDef
#endregion
#region DetailDef Revenue
x.WriteStartElement("DetailDef");
x.WriteAttributeString("Name", "Revenue");
x.WriteAttributeString("Type", "Number");
x.WriteAttributeString("Length", "2");
x.WriteAttributeString("Companies", "Yes");
x.WriteAttributeString("Individuals", "No");
x.WriteAttributeString("Contacts", "No");
x.WriteEndElement(); //DetailDef
#endregion
#region DetailDef ClientsPerMonth
x.WriteStartElement("DetailDef");
x.WriteAttributeString("Name", "ClientsPerMonth");
x.WriteAttributeString("Type", "Number");
x.WriteAttributeString("Length", "0");
x.WriteAttributeString("Companies", "Yes");
x.WriteAttributeString("Individuals", "No");
x.WriteAttributeString("Contacts", "No");
x.WriteEndElement(); //DetailDef
#endregion
for (DateTime d = new DateTime(2005, 1, 1); d < new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1); d = d.AddMonths(1))
{
#region DetailDef Sales\Revenue YYYY-MM
x.WriteStartElement("DetailDef");
x.WriteAttributeString("Name", "Sales\\Revenue " + d.ToString("yyyy-MM"));
x.WriteAttributeString("Type", "Number");
x.WriteAttributeString("Length", "2"); // ********
x.WriteAttributeString("Companies", "Yes");
x.WriteAttributeString("Individuals", "No");
x.WriteAttributeString("Contacts", "No");
x.WriteEndElement(); //DetailDef
#endregion
}
for (int count = 0; count < ps.Count; count++)
{
Promoter p = ps[count];
//if (count % 10 == 0)
Cambro.Web.Helpers.WriteAlert("Processing promoter (" + count + "/" + ps.Count + "): " + p.UrlName, 1);
#region Company
x.WriteStartElement("Company");
x.WriteElementString("Name", Cambro.Misc.Utility.Snip(p.Name, 41));
x.WriteElementString("Id", "Promoter-" + p.K);
x.WriteElementString("Website", "http://www.dontstayin.com" + p.Url());
#region Phone
x.WriteStartElement("Phone");
x.WriteElementString("Number", PromoterXmlFixPhone(p.PhoneNumber));
x.WriteEndElement(); //Phone
#endregion
try
{
string email = p.PrimaryUsr.Email;
#region Email
x.WriteStartElement("Email");
x.WriteElementString("Address", email);
x.WriteEndElement(); //Email
#endregion
}
catch
{
}
#region Address
x.WriteStartElement("Address");
x.WriteElementString("AddressLine1", p.AddressStreet);
x.WriteElementString("AddressLine2", p.AddressArea);
x.WriteElementString("City", p.AddressTown);
x.WriteElementString("StateProvince", p.AddressCounty);
x.WriteElementString("Country", p.AddressCountry.Name);
x.WriteElementString("ZipCode", p.AddressPostcode.ToUpper());
x.WriteEndElement(); //Address
#endregion
#region DetailDate SignedUp
x.WriteStartElement("DetailDate");
x.WriteAttributeString("Name", "SignedUp");
x.WriteString(p.DateTimeSignUp.ToString("yyyy-MM-dd"));
x.WriteEndElement(); //DetailDate
#endregion
#region DetailNumber Discount
x.WriteStartElement("DetailNumber");
x.WriteAttributeString("Name", "Discount");
double discount = (1 - p.PricingMultiplier) * 100;
x.WriteString(discount.ToString("0"));
x.WriteEndElement(); //DetailString
#endregion
int ClientsPerMonth = 0;
if (true)
{
//First event...
Query qFirstEvent = new Query();
qFirstEvent.TableElement = Event.PromoterJoinWithVenue;
qFirstEvent.TopRecords = 1;
qFirstEvent.QueryCondition = new Q(Promoter.Columns.K, p.K);
qFirstEvent.OrderBy = new OrderBy(Event.Columns.DateTime);
EventSet esFirstEvent = new EventSet(qFirstEvent);
DateTime FirstEventDateTime = DateTime.Now;
if (esFirstEvent.Count > 0)
{
Query q = new Query();
q.Columns = new ColumnSet();
q.ExtraSelectElements.Add("Capacity", "sum([Event].[Capacity])");
q.TableElement = Event.PromoterJoinWithVenue;
q.QueryCondition = new And(new Q(Promoter.Columns.K, p.K), new Q(Event.Columns.DateTime, QueryOperator.LessThanOrEqualTo, DateTime.Now));
if (esFirstEvent[0].DateTime < DateTime.Now.AddMonths(-3))
{
FirstEventDateTime = DateTime.Now.AddMonths(-3);
q.QueryCondition = new And(q.QueryCondition, new Q(Event.Columns.DateTime, QueryOperator.GreaterThan, DateTime.Now.AddMonths(-3)));
}
else
FirstEventDateTime = esFirstEvent[0].DateTime;
EventSet es = new EventSet(q);
if (es.Count > 0)
{
try
{
int capacity = (int)es[0].ExtraSelectElements["Capacity"];
TimeSpan ts = DateTime.Now - FirstEventDateTime;
double capPerDay = (double)capacity / ts.TotalDays;
double capPerMonth = capPerDay * 30;
ClientsPerMonth = (int)capPerMonth;
}
catch
{
}
}
}
}
#region DetailNumber
x.WriteStartElement("DetailNumber");
x.WriteAttributeString("Name", "ClientsPerMonth");
x.WriteString(ClientsPerMonth.ToString());
x.WriteEndElement(); //DetailNumber
#endregion
#region DetailNumber
x.WriteStartElement("DetailNumber");
x.WriteAttributeString("Name", "Enabled");
x.WriteString(p.Status.Equals(Promoter.StatusEnum.Active) ? "1" : "0");
x.WriteEndElement(); //DetailNumber
#endregion
double totalRevenue = 0.0;
if (true)
{
Query q = new Query();
q.ExtraSelectElements.Add("sum", "SUM(Total)");
q.ExtraSelectElements.Add("year", "YEAR(PaidDateTime)");
q.ExtraSelectElements.Add("month", "MONTH(PaidDateTime)");
q.QueryCondition = new Q(Invoice.Columns.PromoterK, p.K);
q.GroupBy = new GroupBy("YEAR(PaidDateTime), MONTH(PaidDateTime)");
q.OrderBy = new OrderBy("YEAR(PaidDateTime) DESC, MONTH(PaidDateTime) DESC");
q.Columns = new ColumnSet();
InvoiceSet ins = new InvoiceSet(q);
foreach (Invoice i in ins)
{
try
{
double revenue = (double)i.ExtraSelectElements["sum"];
totalRevenue += revenue;
int month = (int)i.ExtraSelectElements["month"];
int year = (int)i.ExtraSelectElements["year"];
#region DetailNumber
x.WriteStartElement("DetailNumber");
x.WriteAttributeString("Name", "Sales\\Revenue " + year.ToString("0000") + "-" + month.ToString("00"));
x.WriteString(revenue.ToString("0.00"));
x.WriteEndElement(); //DetailNumber
#endregion
}
catch { }
}
}
#region DetailNumber
x.WriteStartElement("DetailNumber");
x.WriteAttributeString("Name", "Revenue");
x.WriteString(totalRevenue.ToString("0.00"));
x.WriteEndElement(); //DetailNumber
#endregion
if (p.ConfirmedBrands.Count > 0)
{
#region DetailString
x.WriteStartElement("DetailString");
x.WriteAttributeString("Name", "Brands");
bool done = false;
foreach (Brand b in p.ConfirmedBrands)
{
x.WriteString((done ? ", " : "") + b.Name);
done = true;
}
x.WriteEndElement(); //DetailString
#endregion
}
if (p.ConfirmedVenues.Count > 0)
{
#region DetailString
x.WriteStartElement("DetailString");
x.WriteAttributeString("Name", "Venues");
bool done = false;
foreach (Venue v in p.ConfirmedVenues)
{
x.WriteString((done ? ", " : "") + v.FriendlyName);
done = true;
}
x.WriteEndElement(); //DetailString
#endregion
}
foreach (Usr u in p.AdminUsrs)
{
#region Contact
x.WriteStartElement("Contact");
x.WriteElementString("FirstName", u.FirstName);
x.WriteElementString("LastName", u.LastName);
x.WriteElementString("Website", "http://www.dontstayin.com" + u.Url());
if (u.Mobile.Length > 0)
{
#region Phone
x.WriteStartElement("Phone");
x.WriteElementString("Number", "+" + u.Mobile);
x.WriteElementString("Description", "Mobile");
x.WriteEndElement(); //Phone
#endregion
}
#region Email
x.WriteStartElement("Email");
x.WriteElementString("Address", u.Email);
x.WriteEndElement(); //Email
#endregion
#region DetailString
x.WriteStartElement("DetailString");
x.WriteAttributeString("Name", "NickName");
x.WriteString(u.NickName);
x.WriteEndElement(); //DetailString
#endregion
x.WriteEndElement(); //Contact
#endregion
}
x.WriteEndElement(); //Company
#endregion
ps.Kill(count);
}
x.WriteEndElement(); //AllData
#endregion
x.Flush();
x.Close();
}
}
public void PromoterOutstandingAccountPanel()
{
if (Usr.Current != null && !Usr.Current.IsAdmin && !Usr.Current.IsSuperAdmin && Usr.Current.IsEnabledPromoter())
{
PromoterSet promoters = Usr.Current.Promoters(new ColumnSet(Promoter.Columns.UrlName, Promoter.Columns.Name, Promoter.Columns.K, Promoter.Columns.OverrideApplyTicketFundsToInvoices));
Query outstandingInvoiceQuery = new Query();
Q promoterOr = new Q(Invoice.Columns.PromoterK, -1);
foreach (Promoter promoter in promoters)
{
promoterOr = new Or(promoterOr, new Q(Invoice.Columns.PromoterK, promoter.K));
}
outstandingInvoiceQuery.QueryCondition = new And(promoterOr,
new Q(Invoice.Columns.Type, Invoice.Types.Invoice),
new Q(Invoice.Columns.Paid, false),
new Q(Invoice.Columns.DueDateTime, QueryOperator.LessThanOrEqualTo, DateTime.Today.AddDays(8).AddMilliseconds(-1)));
outstandingInvoiceQuery.Columns = new ColumnSet(Invoice.Columns.PromoterK);
outstandingInvoiceQuery.ExtraSelectElements.Add("MinDueDate", "MIN(DueDateTime)");
outstandingInvoiceQuery.OrderBy = new OrderBy("MIN([Invoice].[DueDateTime])");
outstandingInvoiceQuery.GroupBy = new GroupBy("[Invoice].[PromoterK]");
InvoiceSet invoices = new InvoiceSet(outstandingInvoiceQuery);
bool isRedirectPage = false;
PromoterAccountsOutstandingPanel.Visible = invoices.Count > 0;
this.Visible = invoices.Count > 0;
if (invoices.Count > 0)
{
string tableClass = "dataGrid";
string headerText = "";
string messageText = "";
// Set urgency
DateTime minDueDate = Utilities.GetStartOfDay((DateTime)invoices[0].ExtraSelectElements["MinDueDate"]);
DateTime startOfToday = DateTime.Today;
this.PromoterAccountsOutstandingMessage.Visible = true;
this.AccountLockoutHelp.Visible = true;
// If SuperAdmin has set "DisableOverdueRedirect" then it prevents the suspension of the promoter's access to the site, but will still show wanring message with all outstanding / overdue accounts.
if (minDueDate < startOfToday.AddDays(-7) && !invoices[0].Promoter.DisableOverdueRedirect)
{
// Far overdue - redirect
headerText = "WARNING! Your promoter account invoices are overdue. You must pay now!";
messageText = "Your access to DontStayIn has been suspended due to invoices that have been overdue for an extended period.<br>You must pay the account balance immediately.";
if (!this.IsPostBack && !Usr.Current.IsAdmin && !Usr.Current.IsSuperAdmin && !this.IsAllowedPage(invoices))
{
HttpContext.Current.Response.Redirect(REDIRECT_PAGE);
}
else
{
// Hide panel if they are redirected to pay immediately
if (IsOnPromoterPayPage(invoices))
{
PromoterAccountsOutstandingPanel.Visible = false;
this.Visible = false;
return;
}
isRedirectPage = true;
}
}
else if (minDueDate < startOfToday)
{
// Overdue
headerText = "WARNING! Your promoter account invoices are overdue. You must pay now!";
messageText = "Your promoter account invoices are now overdue.<br>You must pay the account balance immediately to prevent a suspension of your DontStayIn account.";
}
else if (minDueDate < startOfToday.AddDays(3))
{
// Almost overdue
headerText = "URGENT! Your promoter account invoices are almost due. Please pay now.";
messageText = "Your promoter account invoices are almost overdue.";
}
else
{
// Outstanding
headerText = "Your promoter account invoices are nearly due. Please pay now.";
//messageText = "Your promoter account invoices are nearing the due dates. Please pay the account balance.";
this.PromoterAccountsOutstandingMessage.Visible = false;
this.AccountLockoutHelp.Visible = false;
}
this.PromoterAccountsOutstandingHeader.InnerHtml = headerText;
this.PromoterAccountsOutstandingMessage.InnerHtml = messageText;
//this.AccountLockoutHelp.Visible = isRedirectPage;
this.PromoterAccountsOutstandingTable.Rows.Clear();
this.PromoterAccountsOutstandingTable.Attributes.Add("class", tableClass);
#region Header Row
HtmlTableRow headerRow = new HtmlTableRow();
headerRow.Attributes.Add("class", "dataGridHeader");
this.PromoterAccountsOutstandingTable.Rows.Add(headerRow);
HtmlTableCell[] headerTableCells = new HtmlTableCell[4];
for (int i = 0; i < headerTableCells.Length; i++)
{
headerTableCells[i] = new HtmlTableCell("th");
headerTableCells[i].Align = "left";
headerRow.Cells.Add(headerTableCells[i]);
}
headerTableCells[0].InnerHtml = "<nobr>Promoter account</nobr>";
headerTableCells[1].InnerHtml = "Balance";
headerTableCells[2].InnerHtml = "<nobr>Payment due</nobr>";
headerTableCells[3].InnerHtml = "<nobr>Pay now</nobr>";
#endregion
#region Data Rows
HtmlTableRow[] dataRows = new HtmlTableRow[invoices.Count];
for (int i = 0; i < invoices.Count; i++)
{
dataRows[i] = new HtmlTableRow();
if (i % 2 == 0)
dataRows[i].Attributes.Add("class", "dataGridItem");
else
dataRows[i].Attributes.Add("class", "dataGridAltItem");
this.PromoterAccountsOutstandingTable.Rows.Add(dataRows[i]);
HtmlTableCell[] dataTableCells = new HtmlTableCell[4];
for (int j = 0; j < dataTableCells.Length; j++)
{
dataTableCells[j] = new HtmlTableCell();
dataRows[i].Cells.Add(dataTableCells[j]);
}
promoters.Reset();
string[] urlAppParams = new string[]{ "PayOutstanding", "true" };
foreach (Promoter promoter in promoters)
{
if (invoices[i].PromoterK == promoter.K)
{
// if isRedirect remove link, cause they are only allowed to go to the payment page
if(isRedirectPage)
dataTableCells[0].InnerHtml = "<nobr>" + promoter.Name + "</nobr>";
else
dataTableCells[0].InnerHtml = "<nobr><a href=\"" + promoter.Url() + "\">" + promoter.Name + "</a></nobr>";
dataTableCells[1].InnerHtml = "<nobr><font color=\"#ff0000;\"><b>" + Math.Abs(promoter.GetBalance()).ToString("c") + "</b></font></nobr>";
if (Utilities.GetStartOfDay((DateTime)invoices[i].ExtraSelectElements["MinDueDate"]) >= DateTime.Today)
urlAppParams = new string[] { "PayOutstanding", "true"};
else
urlAppParams = new string[] { "PayOutstanding", "true", "Overdue", "true" };
dataTableCells[3].InnerHtml = "<button onclick=\"window.location.href='" + promoter.UrlApp("invoices", urlAppParams) + "';return false;\">Pay now</button>";
break;
}
}
dataTableCells[1].Align = "right";
dataTableCells[2].InnerHtml = Utilities.DateToString((DateTime)invoices[i].ExtraSelectElements["MinDueDate"]);
}
#endregion
}
}
else
{
PromoterAccountsOutstandingPanel.Visible = false;
this.Visible = false;
}
}
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;
}
/// <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;
}
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>"));
}
private void GenerateSalesCallsReport(DateTime fromDate, DateTime toDate, DateGrouping dateGrouping, string salesUsrK)
{
Page.Validate("");
if (Page.IsValid)
{
this.DateRangeValueLabel.Text = fromDate.ToString("dd/MM/yy") + " to " + toDate.ToString("dd/MM/yy");
this.DateRangeLabel.Visible = true;
this.DateRangeValueLabel.Visible = true;
int startOfWeek = 1; // Sunday = 0, Monday = 1
fromDate = new DateTime(fromDate.Year, fromDate.Month, fromDate.Day);
toDate = new DateTime(toDate.Year, toDate.Month, toDate.Day);
if (toDate >= fromDate)
{
int numberOfDateGroupings = 0;
if (dateGrouping.Equals(DateGrouping.Daily))
{
toDate = new DateTime(toDate.Year, toDate.Month, toDate.Day).AddDays(1);
numberOfDateGroupings = ((TimeSpan)(toDate - fromDate)).Days;
}
else if (dateGrouping.Equals(DateGrouping.Weekly))
{
fromDate = Utilities.GetStartOfWeek(fromDate);
toDate = Utilities.GetEndOfWeek(toDate);
toDate = new DateTime(toDate.Year, toDate.Month, toDate.Day).AddDays(1);
numberOfDateGroupings = Convert.ToInt32(Math.Ceiling(Convert.ToDouble(((TimeSpan)(toDate - fromDate)).Days) / 7d));
}
else if (dateGrouping.Equals(DateGrouping.Monthly))
{
fromDate = Utilities.GetStartOfMonth(fromDate);
toDate = Utilities.GetEndOfMonth(toDate);
toDate = new DateTime(toDate.Year, toDate.Month, toDate.Day).AddDays(1);
numberOfDateGroupings = (toDate.AddDays(-1).Year - fromDate.Year) * 12 + (toDate.AddDays(-1).Month - fromDate.Month) + 1;
}
List<UsrDataHolder> selectedSalesUsrs = SalesUsrs;
if (salesUsrK != "0")
{
selectedSalesUsrs = new List<UsrDataHolder>();
if (salesUsrK.IndexOf("team") == 0)
{
UsrSet salesTeam = Usr.GetCurrentSalesUsrsNameAndK(Convert.ToInt32(salesUsrK.Replace("team", "")));
foreach (Usr salesUsr in salesTeam)
{
selectedSalesUsrs.Add(new UsrDataHolder(salesUsr));
}
}
else
{
selectedSalesUsrs.Add(SalesUsrs[GetSalesPersonIndexNumber(SalesUsrs, Convert.ToInt32(salesUsrK))]);
}
}
if (selectedSalesUsrs.Count > 0)
{
Q salesUsrsQueryCondition = new Q(SalesCall.Columns.UsrK, selectedSalesUsrs[0].K);
Q invoiceSalesUsrsQueryCondition = new Q(Invoice.Columns.SalesUsrK, selectedSalesUsrs[0].K);
Q promoterAddedByQueryCondition = new Q(Promoter.Columns.AddedByUsrK, selectedSalesUsrs[0].K);
for (int i = 1; i < selectedSalesUsrs.Count; i++)
{
promoterAddedByQueryCondition = new Or(promoterAddedByQueryCondition,
new Q(Promoter.Columns.AddedByUsrK, selectedSalesUsrs[i].K));
salesUsrsQueryCondition = new Or(salesUsrsQueryCondition,
new Q(SalesCall.Columns.UsrK, selectedSalesUsrs[i].K));
invoiceSalesUsrsQueryCondition = new Or(invoiceSalesUsrsQueryCondition,
new Q(Invoice.Columns.SalesUsrK, selectedSalesUsrs[i].K));
}
Q salesCallDateRangeQueryCondition = new And(new Q(SalesCall.Columns.IsCall, true),
new Q(SalesCall.Columns.DateTimeStart, QueryOperator.GreaterThanOrEqualTo, fromDate),
new Q(SalesCall.Columns.DateTimeStart, QueryOperator.LessThan, toDate));
Q promoterDateRangeQueryCondition = new And(
new Q(Promoter.Columns.AddedMethod, Promoter.AddedMedhods.SalesUser),
new Q(Promoter.Columns.DateTimeSignUp, QueryOperator.GreaterThanOrEqualTo, fromDate),
new Q(Promoter.Columns.DateTimeSignUp, QueryOperator.LessThan, toDate));
#region Sales Calls: Total Money
Query totalSalesAmountQuery = new Query(new And(invoiceSalesUsrsQueryCondition,
new Q(Invoice.Columns.SalesUsrAmount, QueryOperator.NotEqualTo, 0),
new Or(new And(new Q(Invoice.Columns.Type, Invoice.Types.Invoice),
new Q(Invoice.Columns.Paid, true),
new Q(Invoice.Columns.PaidDateTime, QueryOperator.GreaterThanOrEqualTo, fromDate),
new Q(Invoice.Columns.PaidDateTime, QueryOperator.LessThan, toDate),
new Q(Usr.Columns.SalesTeam, QueryOperator.NotEqualTo, Usr.SalesTeams.CorporateSalesTeam)),
new And(new Q(Invoice.Columns.Type, Invoice.Types.Invoice),
new Q(Invoice.Columns.TaxDateTime, QueryOperator.GreaterThanOrEqualTo, fromDate),
new Q(Invoice.Columns.TaxDateTime, QueryOperator.LessThan, toDate),
new Q(Usr.Columns.SalesTeam, Usr.SalesTeams.CorporateSalesTeam)),
new And(new Q(Invoice.Columns.Type, Invoice.Types.Credit),
new Q(Invoice.Columns.CreatedDateTime, QueryOperator.GreaterThanOrEqualTo, fromDate),
new Q(Invoice.Columns.CreatedDateTime, QueryOperator.LessThan, toDate)))));
totalSalesAmountQuery.TableElement = new Join(Invoice.Columns.SalesUsrK, Usr.Columns.K);
totalSalesAmountQuery.ExtraSelectElements.Add("TotalSales", "SUM(SalesUsrAmount)");
totalSalesAmountQuery.Columns = new ColumnSet(Invoice.Columns.SalesUsrK);
if (dateGrouping.Equals(DateGrouping.Daily))
{
totalSalesAmountQuery.ExtraSelectElements.Add("Date", "CONVERT(datetime,CONVERT(varchar(2),DAY(IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime))) + '/' + CONVERT(varchar(2),MONTH(IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime))) + '/' + CONVERT(varchar(4),Year(IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime))))");
totalSalesAmountQuery.OrderBy = new OrderBy("[Usr].[FirstName], YEAR(IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime)) desc, MONTH(IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime)) desc, DAY(IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime)) desc");
totalSalesAmountQuery.GroupBy = new GroupBy("[Usr].[FirstName], DAY(IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime)), MONTH(IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime)), YEAR(IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime)), [Invoice].[SalesUsrK]");
}
else if (dateGrouping.Equals(DateGrouping.Weekly))
{
totalSalesAmountQuery.ExtraSelectElements.Add("Date", "CONVERT(dateTime,(CONVERT(varchar(2),DAY(DateAdd(day, -1 * datepart(dw, IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime)) + 1, IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime)))) + '/' + CONVERT(varchar(2),MONTH(DateAdd(day, -1 * datepart(dw, IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime)) + 1, IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime)))) + '/' + CONVERT(varchar(4),YEAR(DateAdd(day, -1 * datepart(dw, IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime)) + 1, IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime))))))");
totalSalesAmountQuery.OrderBy = new OrderBy("[Usr].[FirstName], CONVERT(dateTime,(CONVERT(varchar(2),DAY(DateAdd(day, -1 * datepart(dw, IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime)) + 1, IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime)))) + '/' + CONVERT(varchar(2),MONTH(DateAdd(day, -1 * datepart(dw, IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime)) + 1, IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime)))) + '/' + CONVERT(varchar(4),YEAR(DateAdd(day, -1 * datepart(dw, IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime)) + 1, IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime)))))) desc");
totalSalesAmountQuery.GroupBy = new GroupBy("[Usr].[FirstName], CONVERT(dateTime,(CONVERT(varchar(2),DAY(DateAdd(day, -1 * datepart(dw, IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime)) + 1, IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime)))) + '/' + CONVERT(varchar(2),MONTH(DateAdd(day, -1 * datepart(dw, IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime)) + 1, IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime)))) + '/' + CONVERT(varchar(4),YEAR(DateAdd(day, -1 * datepart(dw, IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime)) + 1, IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime)))))), [Invoice].[SalesUsrK]");
}
else if (dateGrouping.Equals(DateGrouping.Monthly))
{
totalSalesAmountQuery.ExtraSelectElements.Add("Date", "CONVERT(datetime,'1/' + CONVERT(varchar(2),MONTH(IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime))) + '/' + CONVERT(varchar(4),Year(IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime))))");
totalSalesAmountQuery.OrderBy = new OrderBy("[Usr].[FirstName], YEAR(IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime)) desc, MONTH(IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime)) desc");
totalSalesAmountQuery.GroupBy = new GroupBy("[Usr].[FirstName], MONTH(IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime)), YEAR(IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime)), [Invoice].[SalesUsrK]");
}
InvoiceSet salesInvoices = new InvoiceSet(totalSalesAmountQuery);
#endregion
#region Sales Calls: SalesUsrK, Minutes, and Total calls
Query salesCallMinutesQuery = new Query(new And(salesCallDateRangeQueryCondition,
salesUsrsQueryCondition));
salesCallMinutesQuery.ExtraSelectElements.Add("Minutes", "SUM([SalesCall].[Duration])");
salesCallMinutesQuery = PopulateSalesCallQuery(salesCallMinutesQuery, dateGrouping, startOfWeek);
SalesCallSet salesCallsMinutes = new SalesCallSet(salesCallMinutesQuery);
#endregion
#region Promoters: New leads
Query promotersNewLeadsQuery = new Query(new And(promoterDateRangeQueryCondition,
promoterAddedByQueryCondition));
promotersNewLeadsQuery = PopulatePromotersQuery(promotersNewLeadsQuery, dateGrouping, startOfWeek);
PromoterSet promotersNewLeads = new PromoterSet(promotersNewLeadsQuery);
#endregion
#region Sales Calls: New leads
Query salesCallNewLeadsQuery = new Query(new And(salesCallDateRangeQueryCondition,
salesUsrsQueryCondition,
new Q(SalesCall.Columns.IsCallToNewLead, true)));
salesCallNewLeadsQuery = PopulateSalesCallQuery(salesCallNewLeadsQuery, dateGrouping, startOfWeek);
SalesCallSet salesCallsNewLeads = new SalesCallSet(salesCallNewLeadsQuery);
#endregion
#region Sales Calls: Effective
Query salesCallEffectiveQuery = new Query(new And(salesCallDateRangeQueryCondition,
salesUsrsQueryCondition,
new Q(SalesCall.Columns.Effective, true)));
salesCallEffectiveQuery = PopulateSalesCallQuery(salesCallEffectiveQuery, dateGrouping, startOfWeek);
SalesCallSet salesCallsEffective = new SalesCallSet(salesCallEffectiveQuery);
#endregion
#region Sales Calls: Cold
Query salesCallColdQuery = new Query(new And(salesCallDateRangeQueryCondition,
salesUsrsQueryCondition,
new Q(SalesCall.Columns.Type, SalesCall.Types.Cold)));
salesCallColdQuery = PopulateSalesCallQuery(salesCallColdQuery, dateGrouping, startOfWeek);
SalesCallSet salesCallsCold = new SalesCallSet(salesCallColdQuery);
#endregion
#region Sales Calls: Followup
Query salesCallFollowupQuery = new Query(new And(salesCallDateRangeQueryCondition,
salesUsrsQueryCondition,
new Q(SalesCall.Columns.Type, SalesCall.Types.ProactiveFollowUp)));
salesCallFollowupQuery = PopulateSalesCallQuery(salesCallFollowupQuery, dateGrouping, startOfWeek);
SalesCallSet salesCallsFollowup = new SalesCallSet(salesCallFollowupQuery);
#endregion
#region Sales Calls: Active
Query salesCallActiveQuery = new Query(new And(salesCallDateRangeQueryCondition,
salesUsrsQueryCondition,
new Q(SalesCall.Columns.Type, SalesCall.Types.Active)));
salesCallActiveQuery = PopulateSalesCallQuery(salesCallActiveQuery, dateGrouping, startOfWeek);
SalesCallSet salesCallsActive = new SalesCallSet(salesCallActiveQuery);
#endregion
#region Table
this.SalesStatsResultsTable.Visible = false;
this.SalesCallsResultsTable.Visible = true;
HtmlTable salesCallsTable = this.SalesCallsResultsTable;
salesCallsTable.Rows.Clear();
#endregion
#region Header Rows
HtmlTableRow headerRow1 = new HtmlTableRow();
headerRow1.Attributes.Add("class", "dataGrid1stHeader");
salesCallsTable.Rows.Add(headerRow1);
HtmlTableCell[] header1TableCells = new HtmlTableCell[6];
header1TableCells[0] = new HtmlTableCell("td");
header1TableCells[0].RowSpan = 2;
header1TableCells[0].InnerHtml = "Date";
for (int i = 1; i < header1TableCells.Length; i++)
{
header1TableCells[i] = new HtmlTableCell("th");
header1TableCells[i].ColSpan = selectedSalesUsrs.Count;
header1TableCells[i].Align = "center";
header1TableCells[i].Attributes.Add("class", "dataGridColumnDivider");
}
header1TableCells[1].InnerHtml = "Total sales";
header1TableCells[2].InnerHtml = "<b>Total calls</b>";
header1TableCells[3].InnerHtml = "Minutes per call";
header1TableCells[4].InnerHtml = "<b>New leads</b>";
header1TableCells[5].InnerHtml = "Calls to new leads";
//header1TableCells[4].InnerHtml = "<nobr>Effective calls</nobr>";
//header1TableCells[7].InnerHtml = "<nobr>Cold (%)</nobr>";
//header1TableCells[8].InnerHtml = "<nobr>Followup (%)</nobr>";
//header1TableCells[9].InnerHtml = "<nobr>Active (%)</nobr>";
foreach (HtmlTableCell tc in header1TableCells)
headerRow1.Cells.Add(tc);
HtmlTableRow headerRow2 = new HtmlTableRow();
headerRow2.Attributes.Add("class", "dataGrid2ndHeader");
salesCallsTable.Rows.Add(headerRow2);
HtmlTableCell[] header2TableCells = new HtmlTableCell[selectedSalesUsrs.Count * (header1TableCells.Length - 1)];
for (int i = 0; i < header2TableCells.Length; i++)
{
header2TableCells[i] = new HtmlTableCell("th");
header2TableCells[i].InnerHtml = "<b>" + selectedSalesUsrs[i % selectedSalesUsrs.Count].FirstName + "</b>";
header2TableCells[i].Align = "right";
header2TableCells[i].Width = "10";
if (i % selectedSalesUsrs.Count == 0)
{
header2TableCells[i].Attributes.Add("class", "dataGridColumnDivider");
}
headerRow2.Cells.Add(header2TableCells[i]);
}
#endregion
#region Data Rows
HtmlTableCell[,] dataTableCells = new HtmlTableCell[numberOfDateGroupings, selectedSalesUsrs.Count * (header1TableCells.Length - 1) + 1];
for (int i = 0; i < numberOfDateGroupings; i++)
{
dataTableCells[i, 0] = new HtmlTableCell();
dataTableCells[i, 0].InnerHtml = "<nobr>";
if (dateGrouping.Equals(DateGrouping.Daily))
dataTableCells[i, 0].InnerHtml += String.Format("{0:ddd' 'd' 'MMM}", toDate.AddDays(-1 * (i + 1)));
else if (dateGrouping.Equals(DateGrouping.Weekly))
{
dataTableCells[i, 0].InnerHtml += toDate.AddDays(-7 * (i + 1)).ToString("dd/MM/yy") + " - " + toDate.AddDays(-7 * (i + 1) + 6).ToString("dd/MM/yy");
}
else if (dateGrouping.Equals(DateGrouping.Monthly))
{
dataTableCells[i, 0].InnerHtml += String.Format("{0:MMM' 'yy}", toDate.AddMonths(-1 * (i + 1)));
}
dataTableCells[i, 0].InnerHtml += "</nobr>";
}
foreach (Invoice salesInvoice in salesInvoices)
{
try
{
int i = GetSalesPersonIndexNumber(selectedSalesUsrs, salesInvoice.SalesUsrK);
int dateGroupingsFromTop = 0;
if (dateGrouping.Equals(DateGrouping.Daily))
dateGroupingsFromTop = ((TimeSpan)(toDate - ((DateTime)salesInvoice.ExtraSelectElements["Date"]))).Days;
else if (dateGrouping.Equals(DateGrouping.Weekly))
dateGroupingsFromTop = Convert.ToInt32(Math.Ceiling(Convert.ToDouble(((TimeSpan)(toDate - ((DateTime)salesInvoice.ExtraSelectElements["Date"]))).Days) / 7d));
else if (dateGrouping.Equals(DateGrouping.Monthly))
{
dateGroupingsFromTop = (toDate.AddDays(-1).Year - ((DateTime)salesInvoice.ExtraSelectElements["Date"]).Year) * 12 + (toDate.AddDays(-1).Month - ((DateTime)salesInvoice.ExtraSelectElements["Date"]).Month) + 1;
}
dataTableCells[dateGroupingsFromTop - 1, i + 1] = new HtmlTableCell();
//protected void GenBannerStats_Click(object sender, EventArgs eventArgs)
//{
// Banner.GeneratePositionStats();
//}
#endregion
#region UpdateOldDBInvoices_Click
public void UpdateOldDBInvoices_Click(object o, System.EventArgs e)
{
Cambro.Web.Helpers.WriteAlertHeader();
Cambro.Web.Helpers.WriteAlert("Selecting xxx...", 1);
Query InvoiceQuery = new Query();
InvoiceQuery.OrderBy = new OrderBy(Invoice.Columns.K, OrderBy.OrderDirection.Ascending);
InvoiceSet bs = new InvoiceSet(InvoiceQuery);
for (int count = 0; count < bs.Count; count++)
{
Invoice invoice = bs[count];
try
{
invoice.Type = Invoice.Types.Invoice;
invoice.IsImmediateCreditCardPayment = true;
invoice.ActionUsrK = invoice.UsrK;
invoice.VatCode = Invoice.VATCodes.T1;
decimal invoicePrice = 0;
decimal invoiceVat = 0;
decimal invoiceTotal = 0;
foreach (InvoiceItem invoiceItem in invoice.Items)
{
//invoiceItem.Price = Math.Round(invoiceItem.Price, 2);
//invoiceItem.Vat = Math.Round(invoiceItem.Vat, 2);
//invoiceItem.Total = Math.Round(invoiceItem.Total, 2);
invoicePrice += invoiceItem.Price;
invoiceVat += invoiceItem.Vat;
invoiceTotal += invoiceItem.Total;
if (invoiceItem.Type == InvoiceItem.Types.CharityDonation)
invoiceItem.VatCode = InvoiceItem.VATCodes.T9;
else if(invoiceItem.Vat.Equals(0))
invoiceItem.VatCode = InvoiceItem.VATCodes.T0;
else
invoiceItem.VatCode = InvoiceItem.VATCodes.T1;
if (invoiceItem.Type == InvoiceItem.Types.Banner || invoiceItem.Type == InvoiceItem.Types.BannerEmail || invoiceItem.Type == InvoiceItem.Types.BannerHotbox
|| invoiceItem.Type == InvoiceItem.Types.BannerPhoto || invoiceItem.Type == InvoiceItem.Types.BannerSkyscraper || invoiceItem.Type == InvoiceItem.Types.BannerTop)
{
try
{
Banner banner = new Banner(invoiceItem.KeyData);
invoiceItem.RevenueStartDate = banner.FirstDay;
invoiceItem.RevenueEndDate = banner.LastDay;
}
catch (Exception)
{ }
}
else
{
invoiceItem.RevenueStartDate = invoice.CreatedDateTime;
invoiceItem.RevenueEndDate = invoice.CreatedDateTime;
}
invoiceItem.Update();
}
invoice.Price = Math.Round(invoicePrice,2);
invoice.Vat = Math.Round(invoiceVat,2);
invoice.Total = Math.Round(invoiceTotal, 2);
if (invoice.DueDateTime.Equals(DateTime.MinValue))
invoice.DueDateTime = invoice.CreatedDateTime.AddDays(30);
invoice.UpdateAndSetPaidStatus();
// If there arent transfers paying for this invoices yet, then create them.
decimal amountPaid = invoice.AmountPaid;
if (Math.Round(invoice.Total,2) > Math.Round(amountPaid,2))
{
InvoiceTransferSet invoiceTransferSet = new InvoiceTransferSet(new Query(new Q(InvoiceTransfer.Columns.InvoiceK, invoice.K)));
if (invoiceTransferSet.Count == 0)
{
Transfer transfer = new Transfer();
transfer.ActionUsrK = invoice.UsrK;
transfer.Amount = Math.Round(invoice.Total - amountPaid, 2);
transfer.DateTimeComplete = invoice.CreatedDateTime;
transfer.DateTimeCreated = invoice.CreatedDateTime;
transfer.Method = Transfer.Methods.Card;
transfer.AddNote("Autogenerated Transfer to create transfer for old system invoice.", "System");
transfer.PromoterK = invoice.PromoterK;
transfer.Status = Transfer.StatusEnum.Success;
transfer.Type = Transfer.TransferTypes.Payment;
transfer.UsrK = invoice.UsrK;
// Will be fully applied
transfer.IsFullyApplied = true;
transfer.CardAddress1 = invoice.Address;
transfer.CardName = invoice.Name;
transfer.CardPostcode = invoice.Postcode;
transfer.Update();
InvoiceTransfer invoiceTransfer = new InvoiceTransfer();
invoiceTransfer.InvoiceK = invoice.K;
invoiceTransfer.TransferK = transfer.K;
invoiceTransfer.Amount = transfer.Amount;
invoiceTransfer.Update();
invoice.UpdateAndSetPaidStatus();
}
}
invoice.PaidDateTime = invoice.CreatedDateTime;
invoice.Update();
if (count % 10 == 0)
Cambro.Web.Helpers.WriteAlert("Done " + count + "/" + bs.Count, 2);
}
catch (Exception ex)
{
Cambro.Web.Helpers.WriteAlert("Exception " + count + "/" + bs.Count + " - " + ex.ToString(), 3);
}
bs.Kill(count);
}
Cambro.Web.Helpers.WriteAlert("Done!", 3);
Cambro.Web.Helpers.WriteAlertFooter();
}
private void GenerateSalesTodayTable(List<UsrDataHolder> salesUsrs)
{
DateTime fromDate = DateTime.Today;
DateTime toDate = fromDate.AddDays(1);
if (salesUsrs.Count > 0)
{
#region Sales: Total Money
Query totalSalesAmountQuery = new Query(new And(new Q(Usr.Columns.SalesTeam, QueryOperator.GreaterThan, 0),
new Q(Invoice.Columns.SalesUsrAmount, QueryOperator.NotEqualTo, 0),
new Or(new And(new Q(Invoice.Columns.Type, Invoice.Types.Invoice),
new Q(Invoice.Columns.Paid, true),
new Q(Invoice.Columns.PaidDateTime, QueryOperator.GreaterThanOrEqualTo, fromDate),
new Q(Invoice.Columns.PaidDateTime, QueryOperator.LessThan, toDate),
new Q(Usr.Columns.SalesTeam, QueryOperator.NotEqualTo, Usr.SalesTeams.CorporateSalesTeam)),
new And(new Q(Invoice.Columns.Type, Invoice.Types.Invoice),
new Q(Invoice.Columns.TaxDateTime, QueryOperator.GreaterThanOrEqualTo, fromDate),
new Q(Invoice.Columns.TaxDateTime, QueryOperator.LessThan, toDate),
new Q(Usr.Columns.SalesTeam, Usr.SalesTeams.CorporateSalesTeam)),
new And(new Q(Invoice.Columns.Type, Invoice.Types.Credit),
new Q(Invoice.Columns.CreatedDateTime, QueryOperator.GreaterThanOrEqualTo, fromDate),
new Q(Invoice.Columns.CreatedDateTime, QueryOperator.LessThan, toDate)))));
totalSalesAmountQuery.TableElement = new Join(Usr.Columns.K, Invoice.Columns.SalesUsrK, QueryJoinType.Left);
totalSalesAmountQuery.ExtraSelectElements.Add("TotalSales", "SUM(ISNULL([Invoice].[SalesUsrAmount],0))");
totalSalesAmountQuery.Columns = new ColumnSet(Invoice.Columns.SalesUsrK, Usr.Columns.FirstName);
totalSalesAmountQuery.OrderBy = new OrderBy("[Usr].[FirstName], SUM(SalesUsrAmount)");
totalSalesAmountQuery.GroupBy = new GroupBy("[Usr].[FirstName], [Invoice].[SalesUsrK]");
InvoiceSet invoices = new InvoiceSet(totalSalesAmountQuery);
#endregion
#region Sales Calls: SalesUsrK, Minutes, and Total calls
Query salesCallMinutesQuery = new Query(new And(new Q(SalesCall.Columns.IsCall, true),
new Q(SalesCall.Columns.DateTimeStart, QueryOperator.GreaterThanOrEqualTo, fromDate),
new Q(SalesCall.Columns.DateTimeStart, QueryOperator.LessThan, toDate),
new Q(Usr.Columns.IsSalesPerson, true)));
//salesCallMinutesQuery.ExtraSelectElements.Add("Minutes", "SUM(ISNULL([SalesCall].[Duration],0))");
salesCallMinutesQuery.ExtraSelectElements.Add("TotalCalls", "COUNT([SalesCall].[Duration])");
salesCallMinutesQuery.TableElement = new Join(Usr.Columns.K, SalesCall.Columns.UsrK, QueryJoinType.Left);
salesCallMinutesQuery.Columns = new ColumnSet(SalesCall.Columns.UsrK);
salesCallMinutesQuery.OrderBy = new OrderBy("[Usr].[FirstName]");
salesCallMinutesQuery.GroupBy = new GroupBy("[Usr].[FirstName], [SalesCall].[UsrK]");
SalesCallSet salesCallsMinutes = new SalesCallSet(salesCallMinutesQuery);
#endregion
#region Table
HtmlTable salesTodayTable = this.SalesTodayTable;
salesTodayTable.Rows.Clear();
#endregion
#region Header Row
HtmlTableRow headerRow = new HtmlTableRow();
headerRow.Attributes.Add("class", "dataGridHeader");
salesTodayTable.Rows.Add(headerRow);
HtmlTableCell[] headerTableCells = new HtmlTableCell[3];
for (int i = 0; i < headerTableCells.Length; i++)
{
headerTableCells[i] = new HtmlTableCell("th");
headerTableCells[i].Align = "right";
headerTableCells[i].InnerHtml = " ";
headerRow.Cells.Add(headerTableCells[i]);
}
headerTableCells[0].Style.Add("border-left", "0px;");
private void GenerateSalesMonthTable(List<UsrDataHolder> salesUsrs)
{
DateTime fromDate = Utilities.GetStartOfMonth(new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day));
//DateTime fromDate = Utilities.GetStartOfMonth(new DateTime(2006, 11, 23));
DateTime toDate = Utilities.GetEndOfMonth(fromDate);
toDate = new DateTime(toDate.Year, toDate.Month, toDate.Day).AddDays(1);
if (salesUsrs.Count > 0)
{
#region Sales: Total Money
Query totalSalesAmountQuery = new Query(new And(new Q(Usr.Columns.IsSalesPerson, true),
new Or(new Q(Usr.Columns.SalesTeam, Usr.SalesTeams.PromoterSalesTeam), new Q(Usr.Columns.SalesTeam, Usr.SalesTeams.SmallBusinessSalesTeam)),
new Q(Invoice.Columns.SalesUsrAmount, QueryOperator.NotEqualTo, 0),
new Or(new And(new Q(Invoice.Columns.Type, Invoice.Types.Invoice),
new Q(Invoice.Columns.Paid, true),
new Q(Invoice.Columns.PaidDateTime, QueryOperator.GreaterThanOrEqualTo, fromDate),
new Q(Invoice.Columns.PaidDateTime, QueryOperator.LessThan, toDate),
new Q(Usr.Columns.SalesTeam, QueryOperator.NotEqualTo, Usr.SalesTeams.CorporateSalesTeam)),
new And(new Q(Invoice.Columns.Type, Invoice.Types.Invoice),
new Q(Invoice.Columns.TaxDateTime, QueryOperator.GreaterThanOrEqualTo, fromDate),
new Q(Invoice.Columns.TaxDateTime, QueryOperator.LessThan, toDate),
new Q(Usr.Columns.SalesTeam, Usr.SalesTeams.CorporateSalesTeam)),
new And(new Q(Invoice.Columns.Type, Invoice.Types.Credit),
new Q(Invoice.Columns.CreatedDateTime, QueryOperator.GreaterThanOrEqualTo, fromDate),
new Q(Invoice.Columns.CreatedDateTime, QueryOperator.LessThan, toDate)))));
totalSalesAmountQuery.TableElement = new Join(Usr.Columns.K, Invoice.Columns.SalesUsrK, QueryJoinType.Left);
totalSalesAmountQuery.ExtraSelectElements.Add("TotalSales", "SUM(ISNULL([Invoice].[SalesUsrAmount],0))");
totalSalesAmountQuery.Columns = new ColumnSet(Invoice.Columns.SalesUsrK, Usr.Columns.FirstName);
totalSalesAmountQuery.OrderBy = new OrderBy("SUM(ISNULL([Invoice].[SalesUsrAmount],0)) desc, [Usr].[FirstName]");
totalSalesAmountQuery.GroupBy = new GroupBy("[Usr].[FirstName], [Invoice].[SalesUsrK]");
InvoiceSet invoices = new InvoiceSet(totalSalesAmountQuery);
#endregion
#region Table
HtmlTable salesMonthTable = this.SalesMonthTable;
salesMonthTable.Rows.Clear();
#endregion
#region Header Row
HtmlTableRow headerRow = new HtmlTableRow();
headerRow.Attributes.Add("class", "dataGridHeader");
salesMonthTable.Rows.Add(headerRow);
HtmlTableCell[] headerTableCells = new HtmlTableCell[2];
for (int i = 0; i < headerTableCells.Length; i++)
{
headerTableCells[i] = new HtmlTableCell("th");
headerTableCells[i].Align = "right";
headerTableCells[i].InnerHtml = " ";
headerRow.Cells.Add(headerTableCells[i]);
}
headerTableCells[0].Style.Add("border-left", "0px;");
//headerTableCells[1].InnerHtml = "<img src=\"/gfx/money-gbp-small.gif\" alt=\"Money\" align=\"right\" height=\"11\" width=\"13\"/>";
#endregion
#region Data Rows
HtmlTableRow[] dataRows = new HtmlTableRow[salesUsrs.Count];
for (int i = 0; i < dataRows.Length; i++)
{
dataRows[i] = new HtmlTableRow();
HtmlTableCell[] dataTableCells = new HtmlTableCell[2];
dataTableCells[0] = new HtmlTableCell();
dataTableCells[1] = new HtmlTableCell();
dataTableCells[1].Align = "right";
dataTableCells[1].InnerHtml = "£0";
dataTableCells[0].Style.Add("border-left", "0px;");
if (i == dataRows.Length - 1)
{
dataTableCells[0].Style.Add("border-bottom", "0px;");
dataTableCells[1].Style.Add("border-bottom", "0px;");
}
if (invoices.Count > i)
{
dataTableCells[0].InnerHtml = invoices[i].ExtraSelectElements["Usr_FirstName"].ToString();
dataTableCells[1].InnerHtml = "£" + Convert.ToDouble(invoices[i].ExtraSelectElements["TotalSales"]).ToString("#,##0");
}
//for (int m = 0; m < invoices.Count; m++)
//{
// if (invoices[m].SalesUsrK == salesUsrs[i].K)
// {
// break;
// }
//}
salesMonthTable.Rows.Add(dataRows[i]);
for (int j = 0; j < dataTableCells.Length; j++)
{
dataRows[i].Cells.Add(dataTableCells[j]);
}
}
// Populate remaining names to table. Since not all sales people will have made sales for the given month, we need to add the remaining sales people with total sales of zero
if (invoices.Count < salesUsrs.Count)
{
bool exists;
foreach (UsrDataHolder udh in salesUsrs)
{
exists = false;
foreach (HtmlTableRow row in salesMonthTable.Rows)
{
if (row.Cells[0].InnerHtml.Equals(udh.FirstName))
{
exists = true;
break;
}
}
if (!exists)
{
foreach (HtmlTableRow row in salesMonthTable.Rows)
{
if (row.Cells[0].InnerHtml.Length == 0)
{
row.Cells[0].InnerHtml = udh.FirstName;
break;
}
}
}
}
}
#endregion
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (!Request.UserHostAddress.Equals("84.45.14.60") && !Vars.DevEnv)
throw new Exception("");
Phone p = Phone.GetFromMac(ContainerPage.Url[0].Raw);
string menu = ContainerPage.Url[1].Raw;
string xml = "";
if (menu.Equals(""))
{
#region Default
xml = @"
<?xml version=""1.0"" encoding=""UTF-8""?>
<SnomIPPhoneMenu>
<Title>Menu</Title>
<MenuItem>
<Name>Personal sales</Name>
<URL>http://" + Vars.DomainName + @"/popup/phoneidlescreen/" + p.Mac + @"/personal</URL>
</MenuItem>
<MenuItem>
<Name>Team sales</Name>
<URL>http://" + Vars.DomainName + @"/popup/phoneidlescreen/" + p.Mac + @"/team</URL>
</MenuItem>
</SnomIPPhoneMenu>
";
#endregion
}
else if (menu.Equals("personal"))
{
#region Personal
string salesToday = "0";
string salesYesterday = "0";
string salesThisMonth = "0";
string salesLastMonth = "0";
try
{
Query q = new Query();
q.ExtraSelectElements.Add("sum", "sum([Invoice].[SalesUsrAmount])");
q.QueryCondition = new And(
new Q(Bobs.Invoice.Columns.SalesUsrK, p.UsrK),
new Q(Bobs.Invoice.Columns.PaidDateTime, QueryOperator.GreaterThanOrEqualTo, DateTime.Today),
new Q(Bobs.Invoice.Columns.PaidDateTime, QueryOperator.LessThan, DateTime.Today.AddDays(1))
);
q.Columns = new ColumnSet();
InvoiceSet ins = new InvoiceSet(q);
salesToday = ((double)ins[0].ExtraSelectElements["sum"]).ToString("#,##0");
}
catch { }
try
{
Query q = new Query();
q.ExtraSelectElements.Add("sum", "sum([Invoice].[SalesUsrAmount])");
q.QueryCondition = new And(
new Q(Bobs.Invoice.Columns.SalesUsrK, p.UsrK),
new Q(Bobs.Invoice.Columns.PaidDateTime, QueryOperator.GreaterThanOrEqualTo, DateTime.Today.AddDays(-1)),
new Q(Bobs.Invoice.Columns.PaidDateTime, QueryOperator.LessThan, DateTime.Today)
);
q.Columns = new ColumnSet();
InvoiceSet ins = new InvoiceSet(q);
salesYesterday = ((double)ins[0].ExtraSelectElements["sum"]).ToString("#,##0");
}
catch { }
try
{
Query q = new Query();
q.ExtraSelectElements.Add("sum", "sum([Invoice].[SalesUsrAmount])");
q.QueryCondition = new And(
new Q(Bobs.Invoice.Columns.SalesUsrK, p.UsrK),
new Q(Bobs.Invoice.Columns.PaidDateTime, QueryOperator.GreaterThanOrEqualTo, new DateTime(DateTime.Today.Year, DateTime.Today.Month, 1)),
new Q(Bobs.Invoice.Columns.PaidDateTime, QueryOperator.LessThan, new DateTime(DateTime.Today.Year, DateTime.Today.Month, 1).AddMonths(1))
);
q.Columns = new ColumnSet();
InvoiceSet ins = new InvoiceSet(q);
salesThisMonth = ((double)ins[0].ExtraSelectElements["sum"]).ToString("#,##0");
}
catch { }
try
{
Query q = new Query();
q.ExtraSelectElements.Add("sum", "sum([Invoice].[SalesUsrAmount])");
q.QueryCondition = new And(
new Q(Bobs.Invoice.Columns.SalesUsrK, p.UsrK),
new Q(Bobs.Invoice.Columns.PaidDateTime, QueryOperator.GreaterThanOrEqualTo, new DateTime(DateTime.Today.Year, DateTime.Today.Month, 1).AddMonths(-1)),
new Q(Bobs.Invoice.Columns.PaidDateTime, QueryOperator.LessThan, new DateTime(DateTime.Today.Year, DateTime.Today.Month, 1))
);
q.Columns = new ColumnSet();
InvoiceSet ins = new InvoiceSet(q);
salesLastMonth = ((double)ins[0].ExtraSelectElements["sum"]).ToString("#,##0");
}
catch { }
xml = @"
<?xml version=""1.0"" encoding=""UTF-8""?>
<SnomIPPhoneMenu>
<Title>Menu</Title>
<MenuItem>
<Name>Today: " + salesToday + @"</Name>
<URL>http://" + Vars.DomainName + @"/popup/phoneidlescreen/" + p.Mac + @"</URL>
</MenuItem>
<MenuItem>
<Name>Yesterday: " + salesYesterday + @"</Name>
<URL>http://" + Vars.DomainName + @"/popup/phoneidlescreen/" + p.Mac + @"</URL>
</MenuItem>
<MenuItem>
<Name>" + DateTime.Now.ToString("MMM") + @": " + salesThisMonth + @"</Name>
<URL>http://" + Vars.DomainName + @"/popup/phoneidlescreen/" + p.Mac + @"</URL>
</MenuItem>
<MenuItem>
<Name>" + DateTime.Now.AddMonths(-1).ToString("MMM") + @": " + salesLastMonth + @"</Name>
<URL>http://" + Vars.DomainName + @"/popup/phoneidlescreen/" + p.Mac + @"</URL>
</MenuItem>
</SnomIPPhoneMenu>
";
#endregion
}
else if (menu.Equals("team"))
{
#region Team
string salesToday = "0";
string salesYesterday = "0";
string salesThisMonth = "0";
string salesLastMonth = "0";
try
{
Query q = new Query();
q.ExtraSelectElements.Add("sum", "sum([Invoice].[SalesUsrAmount])");
q.QueryCondition = new And(
new Q(Bobs.Invoice.Columns.PaidDateTime, QueryOperator.GreaterThanOrEqualTo, DateTime.Today),
new Q(Bobs.Invoice.Columns.PaidDateTime, QueryOperator.LessThan, DateTime.Today.AddDays(1))
);
q.Columns = new ColumnSet();
InvoiceSet ins = new InvoiceSet(q);
salesToday = ((double)ins[0].ExtraSelectElements["sum"]).ToString("#,##0");
}
catch { }
try
{
Query q = new Query();
q.ExtraSelectElements.Add("sum", "sum([Invoice].[SalesUsrAmount])");
q.QueryCondition = new And(
new Q(Bobs.Invoice.Columns.PaidDateTime, QueryOperator.GreaterThanOrEqualTo, DateTime.Today.AddDays(-1)),
new Q(Bobs.Invoice.Columns.PaidDateTime, QueryOperator.LessThan, DateTime.Today)
);
q.Columns = new ColumnSet();
InvoiceSet ins = new InvoiceSet(q);
salesYesterday = ((double)ins[0].ExtraSelectElements["sum"]).ToString("#,##0");
}
catch { }
try
{
Query q = new Query();
q.ExtraSelectElements.Add("sum", "sum([Invoice].[SalesUsrAmount])");
q.QueryCondition = new And(
new Q(Bobs.Invoice.Columns.PaidDateTime, QueryOperator.GreaterThanOrEqualTo, new DateTime(DateTime.Today.Year, DateTime.Today.Month, 1)),
new Q(Bobs.Invoice.Columns.PaidDateTime, QueryOperator.LessThan, new DateTime(DateTime.Today.Year, DateTime.Today.Month, 1).AddMonths(1))
);
q.Columns = new ColumnSet();
InvoiceSet ins = new InvoiceSet(q);
salesThisMonth = ((double)ins[0].ExtraSelectElements["sum"]).ToString("#,##0");
}
catch { }
try
{
Query q = new Query();
q.ExtraSelectElements.Add("sum", "sum([Invoice].[SalesUsrAmount])");
q.QueryCondition = new And(
new Q(Bobs.Invoice.Columns.PaidDateTime, QueryOperator.GreaterThanOrEqualTo, new DateTime(DateTime.Today.Year, DateTime.Today.Month, 1).AddMonths(-1)),
new Q(Bobs.Invoice.Columns.PaidDateTime, QueryOperator.LessThan, new DateTime(DateTime.Today.Year, DateTime.Today.Month, 1))
);
q.Columns = new ColumnSet();
InvoiceSet ins = new InvoiceSet(q);
salesLastMonth = ((double)ins[0].ExtraSelectElements["sum"]).ToString("#,##0");
}
catch { }
xml = @"
<?xml version=""1.0"" encoding=""UTF-8""?>
<SnomIPPhoneMenu>
<Title>Menu</Title>
<MenuItem>
<Name>Today: " + salesToday + @"</Name>
<URL>http://" + Vars.DomainName + @"/popup/phoneidlescreen/" + p.Mac + @"</URL>
</MenuItem>
<MenuItem>
<Name>Yesterday: " + salesYesterday + @"</Name>
<URL>http://" + Vars.DomainName + @"/popup/phoneidlescreen/" + p.Mac + @"</URL>
</MenuItem>
<MenuItem>
<Name>" + DateTime.Now.ToString("MMM") + @": " + salesThisMonth + @"</Name>
<URL>http://" + Vars.DomainName + @"/popup/phoneidlescreen/" + p.Mac + @"</URL>
</MenuItem>
<MenuItem>
<Name>" + DateTime.Now.AddMonths(-1).ToString("MMM") + @": " + salesLastMonth + @"</Name>
<URL>http://" + Vars.DomainName + @"/popup/phoneidlescreen/" + p.Mac + @"</URL>
</MenuItem>
</SnomIPPhoneMenu>
";
#endregion
}
Response.ContentType = "text/xml";
Response.Write(xml);
Response.End();
}
private void GetSalesSummary()
{
DateTime fromDate = Utilities.GetStartOfMonth(DateTime.Now.AddMonths(-6));
DateTime toDate = Utilities.GetStartOfMonth(DateTime.Now.AddMonths(1));
Query salesSummaryQuery = new Query(new And(new Q(Invoice.Columns.PromoterK, this.CurrentPromoter.K),
new Q(Invoice.Columns.CreatedDateTime, QueryOperator.GreaterThanOrEqualTo, fromDate),
new Q(Invoice.Columns.CreatedDateTime, QueryOperator.LessThan, toDate)));
salesSummaryQuery.Columns = new ColumnSet(Invoice.Columns.PromoterK);
salesSummaryQuery.ExtraSelectElements.Add("Amount", "SUM(Price)");
salesSummaryQuery.ExtraSelectElements.Add("Date", "CONVERT(datetime,'1/' + CONVERT(varchar(2),MONTH(CreatedDateTime)) + '/' + CONVERT(varchar(4),Year(CreatedDateTime)))");
salesSummaryQuery.OrderBy = new OrderBy("YEAR(CreatedDateTime) desc, MONTH(CreatedDateTime) desc");
salesSummaryQuery.GroupBy = new GroupBy("MONTH(CreatedDateTime), YEAR(CreatedDateTime), PromoterK");
InvoiceSet invoices = new InvoiceSet(salesSummaryQuery);
//toDate = toDate.AddMonths(-1);
#region Table
SalesSummaryTable.Rows.Clear();
#endregion
#region Header Row
HtmlTableRow headerRow = new HtmlTableRow();
headerRow.Attributes.Add("class", "dataGridHeader");
SalesSummaryTable.Rows.Add(headerRow);
List<HtmlTableCell> headerTableCells = new List<HtmlTableCell>();
headerTableCells.Add(new HtmlTableCell("th"));
headerTableCells.Add(new HtmlTableCell("th"));
headerTableCells[0].InnerHtml = "Month";
headerTableCells[1].InnerHtml = "<nobr>Amount (ex VAT)</nobr>";
foreach (HtmlTableCell tc in headerTableCells)
headerRow.Cells.Add(tc);
#endregion
#region Data Rows
int numberOfMonths = (toDate.Year - fromDate.Year) * 12 + toDate.Month - fromDate.Month;
for (int i = 0; i < numberOfMonths; i++)
{
HtmlTableRow dataRow = new HtmlTableRow();
SalesSummaryTable.Rows.Add(dataRow);
if (i % 2 == 1)
dataRow.Attributes.Add("class", "dataGridItem");
else
dataRow.Attributes.Add("class", "dataGridAltItem");
List<HtmlTableCell> dataTableCells = new List<HtmlTableCell>();
dataTableCells.Add(new HtmlTableCell("td"));
dataTableCells.Add(new HtmlTableCell("td"));
foreach (HtmlTableCell tc in dataTableCells)
dataRow.Cells.Add(tc);
dataTableCells[0].InnerHtml = "";
dataTableCells[1].InnerHtml = "";
if (Utilities.GetStartOfMonth(fromDate.AddMonths(i)) == Utilities.GetStartOfMonth(DateTime.Now))
{
dataTableCells[0].InnerHtml = "<small>";
dataTableCells[1].InnerHtml = "<small>";
}
dataTableCells[0].InnerHtml += fromDate.AddMonths(i).ToString("MMM yyyy");
dataTableCells[1].Align = "right";
invoices.Reset();
bool found = false;
foreach (Invoice invoice in invoices)
{
if (Utilities.GetStartOfMonth((DateTime)invoice.ExtraSelectElements["Date"]) == Utilities.GetStartOfMonth(fromDate.AddMonths(i)))
{
dataTableCells[1].InnerHtml += Convert.ToDouble(invoice.ExtraSelectElements["Amount"]).ToString("c");
found = true;
break;
}
}
if(!found)
dataTableCells[1].InnerHtml += "£0.00";
if (Utilities.GetStartOfMonth(fromDate.AddMonths(i)) == Utilities.GetStartOfMonth(DateTime.Now))
{
dataTableCells[0].InnerHtml += "</small>";
dataTableCells[1].InnerHtml += "</small>";
}
}
#endregion
}
/// <summary>
/// UpdateAffectedInvoices finds all invoices that this transfer is applied to, then compares the invoice total against all successful transfers and credits and updates the invoice paid status
/// </summary>
public void UpdateAffectedInvoices()
{
InvoicesAppliedTo = null;
// Update Invoices that are affected by this transfer
foreach (Invoice invoice in InvoicesAppliedTo)
{
invoice.UpdateAndSetPaidStatus();
}
bool prevIsFullyApplied = this.IsFullyApplied;
SetIsFullyApplied();
if(prevIsFullyApplied != this.IsFullyApplied)
this.Update();
}
private static void EmailUnpaidPromotersInvoicesToPromotersAndAccounts(DateTime fromDueDate, DateTime toDueDate)
{
try
{
// Get all promoterK's who have outstanding invoices within the date range
Query promoterKQuery = new Query();
promoterKQuery.QueryCondition = new And(new Q(Invoice.Columns.Type, Invoice.Types.Invoice),
new Q(Invoice.Columns.Paid, false),
new Q(Invoice.Columns.PromoterK, QueryOperator.NotEqualTo, 0),
new Q(Invoice.Columns.DueDateTime, QueryOperator.GreaterThanOrEqualTo, fromDueDate),
new Q(Invoice.Columns.DueDateTime, QueryOperator.LessThanOrEqualTo, toDueDate));
promoterKQuery.Columns = new ColumnSet(Invoice.Columns.PromoterK);
promoterKQuery.GroupBy = new GroupBy(Invoice.Columns.PromoterK);
InvoiceSet promoterKInvoices = new InvoiceSet(promoterKQuery);
Promoter promoter = null;
// Foreach promoter, group together all their outstanding invoices within the date range and send one email with all invoices as attachments
foreach (Invoice promoterKInvoice in promoterKInvoices)
{
try
{
promoter = new Promoter(promoterKInvoice.PromoterK);
bool containsOverdue = false;
promoter.ApplyAvailableMoneyToUnpaidInvoices();
Query outstandingInvoicesQuery = new Query(new And(new Q(Invoice.Columns.Type, Invoice.Types.Invoice),
new Q(Invoice.Columns.Paid, false),
new Q(Invoice.Columns.DueDateTime, QueryOperator.GreaterThanOrEqualTo, fromDueDate),
new Q(Invoice.Columns.DueDateTime, QueryOperator.LessThanOrEqualTo, toDueDate),
new Q(Invoice.Columns.PromoterK, promoterKInvoice.PromoterK)));
outstandingInvoicesQuery.OrderBy = new OrderBy(Invoice.Columns.DueDateTime, OrderBy.OrderDirection.Ascending);
InvoiceSet outstandingInvoices = new InvoiceSet(outstandingInvoicesQuery);
Mailer mailer = new Mailer();
mailer.TemplateType = Mailer.TemplateTypes.AnotherSiteUser;
decimal totalAmountDue = 0;
foreach (Invoice invoice in outstandingInvoices)
{
totalAmountDue += Math.Round(invoice.AmountDue, 2);
if (invoice.Paid == false && new DateTime(invoice.DueDateTime.Year, invoice.DueDateTime.Month, invoice.DueDateTime.Day) <= new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day))
{
containsOverdue = true;
}
}
string nonUserEmailBody = "";
// If there are no outstanding/overdue invoices, then do not send warning message
if (outstandingInvoices.Count > 0)
{
// For overdue invoices
if (containsOverdue == true)
{
mailer.Subject = "DontStayIn overdue invoices require payment of " + totalAmountDue.ToString("c");
mailer.Body = "<p>DontStayIn overdue invoices. Amount due " + Utilities.MoneyToHTML(totalAmountDue) + ". Payment required immediately for the following invoices:</p>";
}
// For outstanding invoices
else
{
mailer.Subject = "DontStayIn outstanding invoices require payment of " + totalAmountDue.ToString("c");
mailer.Body = "<p>DontStayIn outstanding invoices. Amount due " + Utilities.MoneyToHTML(totalAmountDue) + ". Payment required before due date for the following invoices:</p>";
}
nonUserEmailBody = mailer.Body + "<p>Promoter: " + promoter.Name + "</p><p>Please pay invoices now</p>";
mailer.Body += promoter.LinkEmailFull;
Utilities.AddInvoicesToEmail(mailer, outstandingInvoices);
mailer.Body += @"<p><a href=""[LOGIN(" + promoter.UrlApp("invoices", "pay", "true") + "\")]>Please pay invoices now</a>.</p>";
if (!Vars.DevEnv)
{
if (!promoter.SuspendReminderEmails)
{
foreach (Usr usr in promoter.AdminUsrs)
{
mailer.UsrRecipient = usr;
mailer.RedirectUrl = promoter.UrlApp("invoices");
mailer.Send();
}
}
}
if (!Vars.DevEnv && promoter.AccountsEmail != null && promoter.AccountsEmail.Length > 0)
{
try
{
Utilities.EmailToNonUser(promoter.AccountsEmail, mailer.Subject, nonUserEmailBody, mailer.Attachments.ToArray());
}
catch (Exception ex)
{
string additionalDetails = "Occurred in Utilities.EmailAllPromoterOutstandingStatements(): Promoter.AccountsEmail = " + promoter.AccountsEmail;
EmailException(ex, additionalDetails, promoter);
}
}
// Change for internal use
mailer.UsrRecipient = null;
mailer.TemplateType = Mailer.TemplateTypes.AdminNote;
mailer.Subject += " (" + promoter.Name + ")";
// now send to our accounts email address
mailer.To = "*****@*****.**";
mailer.Send();
}
}
catch (Exception ex)
{
string additionalDetails = "Occurred in Utilities.EmailUnpaidPromotersInvoicesToPromotersAndAccounts(): Promoter K= ";
if (promoterKInvoice != null)
additionalDetails += promoterKInvoice.PromoterK.ToString();
else
additionalDetails += "null";
EmailException(ex, additionalDetails, promoter);
}
}
}
catch (Exception ex)
{
EmailException(ex, "Occurred in Utilities.EmailUnpaidPromotersInvoicesToPromotersAndAccounts()");
}
}
public bool VerifyTicketPurchase()
{
List<IBobAsHTML> bobsAsHTML = new List<IBobAsHTML>();
if ((this.Enabled || this.Cancelled) && (this.Invoice == null || this.CardNumberEnd.Length == 0 || this.FirstName.Length == 0 || this.LastName.Length == 0))
{
string oldTicketHTML = this.AsHTML();
string ticketFromDataBaseHTML = "Unable to retrieve ticket from database.";
try
{
// Get fresh data from database and compare to MemCached Ticket
TicketSet ticketsFromDatabase = new TicketSet(new Query(new Q(Ticket.Columns.K, this.K)));
if (ticketsFromDatabase.Count == 1)
{
ticketFromDataBaseHTML = ticketsFromDatabase[0].AsHTML();
this.InvoiceItemK = ticketsFromDatabase[0].InvoiceItemK;
this.FirstName = ticketsFromDatabase[0].FirstName;
this.LastName = ticketsFromDatabase[0].LastName;
this.CardNumberEnd = ticketsFromDatabase[0].CardNumberEnd;
this.CardCV2 = ticketsFromDatabase[0].CardCV2;
this.Update();
//if (this.InvoiceItemK != ticketsFromDatabase[0].InvoiceItemK || this.Enabled != ticketsFromDatabase[0].Enabled || this.Cancelled != ticketsFromDatabase[0].Cancelled || Math.Round(this.Price, 2) != Math.Round(ticketsFromDatabase[0].Price, 2))
//{
// bobsAsHTML.Add(this);
// bobsAsHTML.Add(ticketsFromDatabase[0]);
// Utilities.AdminEmailAlert("<p>MemCache and database do not match for ticket.</p><p>TicketK= " + this.K.ToString() + ", InvoiceItemK= " + this.InvoiceItemK.ToString() + "</p>", "Error with MemCache", new DSIUserFriendlyException("Error with MemCache"), bobsAsHTML);
//}
}
if (this.InvoiceItemK == 0 || this.CardNumberEnd.Length == 0 || this.FirstName.Length == 0 || this.LastName.Length == 0)
{
bobsAsHTML.Clear();
Query ticketInvoiceQuery = new Query(new And(new Q(Invoice.Columns.UsrK, this.BuyerUsrK),
//new Q(InvoiceItem.Columns.Total, this.Price),
new Q(InvoiceItem.Columns.BuyableObjectType, Convert.ToInt32(Model.Entities.ObjectType.Ticket)),
new Q(InvoiceItem.Columns.BuyableObjectK, this.K),
new Q(Invoice.Columns.Paid, 1),
new Q(Invoice.Columns.PaidDateTime, QueryOperator.GreaterThanOrEqualTo, this.BuyDateTime.AddMinutes(-8)),
new Q(Invoice.Columns.PaidDateTime, QueryOperator.LessThanOrEqualTo, this.BuyDateTime.AddMinutes(8))));
ticketInvoiceQuery.TableElement = new Join(Invoice.Columns.K, InvoiceItem.Columns.InvoiceK);
InvoiceSet ticketInvoice = new InvoiceSet(ticketInvoiceQuery);
if (ticketInvoice.Count == 1 && ticketInvoice[0].Items.Count > 0)
{
if (this.InvoiceItemK == 0)
{
foreach (InvoiceItem ii in ticketInvoice[0].Items)
{
if (ii.Type == InvoiceItem.Types.EventTickets && ii.KeyData == this.K && ii.BuyableObjectType == Model.Entities.ObjectType.Ticket && ii.BuyableObjectK == this.K && Math.Round(ii.Total, 2) == Math.Round(this.Price))
this.InvoiceItemK = ii.K;
}
}
bobsAsHTML.Add(this);
bobsAsHTML.Add(ticketInvoice[0]);
if (ticketInvoice[0].SuccessfulAppliedTransfers.Count == 1)
{
bobsAsHTML.Add(ticketInvoice[0].SuccessfulAppliedTransfers[0]);
if (this.CardNumberEnd.Length == 0)
{
this.CardNumberEnd = ticketInvoice[0].SuccessfulAppliedTransfers[0].CardNumberEnd;
this.CardNumberDigits = ticketInvoice[0].SuccessfulAppliedTransfers[0].CardDigits;
this.CardCV2 = ticketInvoice[0].SuccessfulAppliedTransfers[0].CardCV2;
}
if (this.AddressPostcode.Length == 0)
{
this.AddressPostcode = ticketInvoice[0].SuccessfulAppliedTransfers[0].CardPostcode;
}
if (this.AddressStreet.Length == 0)
{
this.AddressStreet = ticketInvoice[0].SuccessfulAppliedTransfers[0].CardAddress1;
}
if (this.FirstName != Cambro.Misc.Utility.Snip(Utilities.GetFirstName(ticketInvoice[0].SuccessfulAppliedTransfers[0].CardName), 100))
{
this.FirstName = Cambro.Misc.Utility.Snip(Utilities.GetFirstName(ticketInvoice[0].SuccessfulAppliedTransfers[0].CardName), 100);
}
if (this.LastName != Cambro.Misc.Utility.Snip(Utilities.GetLastName(ticketInvoice[0].SuccessfulAppliedTransfers[0].CardName), 100))
{
this.LastName = Cambro.Misc.Utility.Snip(Utilities.GetLastName(ticketInvoice[0].SuccessfulAppliedTransfers[0].CardName), 100);
}
this.Update();
}
Utilities.AdminEmailAlert("<p>Ticket and invoice did not match, but have been auto fixed.</p><p>Please verify manually.</p><p>TicketK= " + this.K.ToString() + ", InvoiceItemK= " + this.InvoiceItemK.ToString() + "</p>" + oldTicketHTML + ticketFromDataBaseHTML,
"Ticket auto fixed #" + this.K.ToString(), new DsiUserFriendlyException("Ticket auto fixed"), bobsAsHTML, new string[] { Vars.EMAIL_ADDRESS_TIMI });
}
else
{
Utilities.AdminEmailAlert("<p>Ticket and invoice did not match, and have not been fixed. Unable to find invoice from database</p><p>Please verify manually.</p><p>TicketK= " + this.K.ToString() + ", InvoiceItemK= " + this.InvoiceItemK.ToString() + "</p>" + oldTicketHTML + ticketFromDataBaseHTML,
"Error not fixed with ticket #" + this.K.ToString(), new DsiUserFriendlyException("Ticket not fixed"), bobsAsHTML, new string[] { Vars.EMAIL_ADDRESS_TIMI });
}
}
}
catch { }
if (this.InvoiceItemK == 0 || this.CardNumberEnd.Length == 0 || this.FirstName.Length == 0 || this.LastName.Length == 0)
{
Utilities.AdminEmailAlert("<p>Ticket information missing.</p><p>Please verify manually.</p><p>TicketK= " + this.K.ToString() + ", InvoiceItemK= " + this.InvoiceItemK.ToString() + "</p>",
"Error with Ticket #" + this.K.ToString(), new DsiUserFriendlyException("Error with ticket"), new List<IBobAsHTML>(){this}, new string[] { Vars.EMAIL_ADDRESS_TIMI });
}
}
if ((this.Enabled || this.Cancelled) && this.Invoice != null && !this.Invoice.Paid)
{
bobsAsHTML.Clear();
bobsAsHTML.Add(this);
bobsAsHTML.Add(this.Invoice);
Utilities.AdminEmailAlert("<p>Error occurred in VerifyTicketPurchase. Invoice was not fully paid for Ticket #" + this.K.ToString() + "</p>",
"Error occurred in VerifyTicketPurchase. Invoice was not fully paid for Ticket #" + this.K.ToString(), new Exception(), bobsAsHTML);
}
if (!this.Enabled)
throw new DsiUserFriendlyException("Ticket not valid.");
if (this.Cancelled)
throw new DsiUserFriendlyException("Ticket has been cancelled.");
return true;
}
public static void AddInvoicesToEmail(Mailer mailer, List<Invoice> invoices)
{
foreach (Invoice invoice in invoices)
{
if (invoice.Promoter != null && invoice.PromoterK != invoices[0].PromoterK)
throw new Exception("Trying to email invoices for different promoters.");
mailer.Attachments.Add(new System.Net.Mail.Attachment(Utilities.GenerateReportMemoryStream(false, invoice), "DontStayIn " + invoice.TypeToString + " #" + invoice.K.ToString() + ".doc", "application/word"));
mailer.Body += "<p>";
if (invoice.Promoter != null)
mailer.Body += @"<a href=""[LOGIN(" + invoice.UrlReport() + "\")]>";
mailer.Body += invoice.TypeToString + " #" + invoice.K.ToString() + " for " + Utilities.MoneyToHTML(Math.Abs(invoice.Total));
if (invoice.Type.Equals(Invoice.Types.Invoice))
{
mailer.Body += ", - status: " + invoice.Status.ToString();
if (invoice.AmountDue > 0)
mailer.Body += ", amount due: " + Utilities.MoneyToHTML(invoice.AmountDue);
//mailer.Body += "</b>";
}
if (invoice.Promoter != null)
mailer.Body += "</a>";
if (invoice.Usr != null)
{
mailer.Body += @", user: "******"</p>";
if (invoice.Type.Equals(Invoice.Types.Credit))
{
Query invoicesCreditedQuery = new Query(new And(new Q(InvoiceCredit.Columns.CreditInvoiceK, invoice.K),
new Q(Invoice.Columns.Type, Invoice.Types.Invoice)));
invoicesCreditedQuery.TableElement = new Join(Invoice.Columns.K, InvoiceCredit.Columns.InvoiceK);
InvoiceSet invoicesCredited = new InvoiceSet(invoicesCreditedQuery);
foreach (Invoice invoiceCredited in invoicesCredited)
{
mailer.Body += @"<p>";
if (invoice.Promoter != null)
mailer.Body += @"<a href=""[LOGIN(" + invoiceCredited.UrlReport() + "\")]>Crediting invoice #" + invoiceCredited.K.ToString() + "</a>";
else
mailer.Body += "Crediting invoice #" + invoiceCredited.K.ToString();
mailer.Body += "</p>";
}
}
foreach (InvoiceItem ii in invoice.Items)
{
mailer.Body += "<ul><li>" + HttpUtility.HtmlEncode(ii.Description) + " - " + Utilities.MoneyToHTML(Math.Abs(ii.Total)) + "</li></ul>";
}
}
}
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>+0</td><td>+1</td><td>+2</td><td>+3</td><td>+4</td><td>Unpaid</td></tr>"));
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>"));
for (int offset = 0; offset <= 4; offset++)
{
if (dtMonth.AddMonths(offset) > DateTime.Now)
{
Stats.Controls.Add(new LiteralControl("<td> </td>"));
}
else
{
try
{
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)),
new Q(Invoice.Columns.Price, QueryOperator.GreaterThan, 0),
new Q(Invoice.Columns.Paid, true),
new Q(Invoice.Columns.PaidDateTime, QueryOperator.GreaterThanOrEqualTo, dtMonth.AddMonths(offset)),
new Q(Invoice.Columns.PaidDateTime, QueryOperator.LessThan, dtMonth.AddMonths(offset + 1))
);
InvoiceSet ins = new InvoiceSet(q);
decimal subTotal = (decimal)ins[0].ExtraSelectElements["sum"];
decimal subTotalCredits = 0.0m;
//credits are always paid when they are created
if (offset == 0)
{
Query qCredits = new Query();
qCredits.Columns = new ColumnSet();
qCredits.ExtraSelectElements.Add("sum", "SUM([Invoice].[Price])");
qCredits.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)),
new Q(Invoice.Columns.Price, QueryOperator.LessThan, 0)
);
InvoiceSet insCredits = new InvoiceSet(qCredits);
try
{
subTotalCredits = (decimal)insCredits[0].ExtraSelectElements["sum"];
}
catch { }
}
subTotal += subTotalCredits;
decimal fraction = subTotal / Total;
if (dtMonth.AddMonths(offset + 1) > DateTime.Now)
Stats.Controls.Add(new LiteralControl("<td><span style=color:#999999;>" + Math.Round(fraction * 100).ToString("0") + "%</span></td>"));
else
Stats.Controls.Add(new LiteralControl("<td>" + Math.Round(fraction * 100).ToString("0") + "%</td>"));
}
catch
{
if (dtMonth.AddMonths(offset + 1) > DateTime.Now)
Stats.Controls.Add(new LiteralControl("<td><span style=color:#999999;>0%</span></td>"));
else
Stats.Controls.Add(new LiteralControl("<td>0%</td>"));
}
}
}
try
{
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)),
new Q(Invoice.Columns.Price, QueryOperator.GreaterThan, 0),
new Q(Invoice.Columns.Paid, false)
);
InvoiceSet ins = new InvoiceSet(q);
decimal subTotal = (decimal)ins[0].ExtraSelectElements["sum"];
decimal fraction = subTotal / Total;
Stats.Controls.Add(new LiteralControl("<td>" + Math.Round(fraction * 100).ToString("0") + "%</td>"));
}
catch
{
Stats.Controls.Add(new LiteralControl("<td>0%</td>"));
}
Stats.Controls.Add(new LiteralControl("</tr>"));
}
Stats.Controls.Add(new LiteralControl("</table>"));
}
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();
}
private bool IsOnPromoterPayPage(InvoiceSet invoices)
{
invoices.Reset();
foreach (Invoice invoice in invoices)
{
if (Utilities.GetStartOfDay((DateTime)invoice.ExtraSelectElements["MinDueDate"]) >= DateTime.Today)
{
if (HttpContext.Current.Request.Url.ToString().IndexOf(invoice.Promoter.UrlApp("invoices", "PayOutstanding", "true")) >= 0)
return true;
}
else
{
if (HttpContext.Current.Request.Url.ToString().IndexOf(invoice.Promoter.UrlApp("invoices", "PayOutstanding", "true", "Overdue", "true")) >= 0)
return true;
}
}
return false;
}
