public static void EmailAllEndedTicketRuns()//DateTime fromDateTime, DateTime toDateTime)
{
Query getAllEndedTicketRunsQuery = new Query(new And(new Or(new Q(TicketRun.Columns.EmailSent, 0),
new Q(TicketRun.Columns.EmailSent, QueryOperator.IsNull, null)),
new Q(TicketRun.Columns.EndDateTime, QueryOperator.LessThanOrEqualTo, DateTime.Now)));
getAllEndedTicketRunsQuery.TableElement = new Join(new TableElement(TablesEnum.TicketPromoterEvent), new TableElement(TablesEnum.TicketRun), QueryJoinType.Inner,
new And(new Q(TicketPromoterEvent.Columns.PromoterK, TicketRun.Columns.PromoterK, true),
new Q(TicketPromoterEvent.Columns.EventK, TicketRun.Columns.EventK, true)));
//getAllEndedTicketRunsQuery.Columns = new ColumnSet(TicketPromoterEvent.Columns.EventK, TicketPromoterEvent.Columns.PromoterK);
TicketPromoterEventSet ticketPromoterEventsWithRecentlyEndedTicketRuns = new TicketPromoterEventSet(getAllEndedTicketRunsQuery);
foreach (TicketPromoterEvent tpe in ticketPromoterEventsWithRecentlyEndedTicketRuns)
{
EmailTicketRunStatusUpdate(tpe);
}
}
//#region ReleaseFundsOptionsDropDownListSetup
//public void ReleaseFundsOptionsDropDownListSetup()
//{
// string releaseFundsOptionsJavascript = "this.options[this.selectedIndex].value == "
// + Convert.ToInt32(ReleaseFundsOptions.AssignExistingTransferK).ToString() + " ? document.getElementById('" + ReleaseFundsTransferKTextBox.UniqueID.Replace("$", "_") + "').style.display = '' : document.getElementById('" + ReleaseFundsTransferKTextBox.UniqueID.Replace("$", "_") + "').style.display = 'none';"
// + "this.options[this.selectedIndex].value == "
// + Convert.ToInt32(ReleaseFundsOptions.CreateReleaseTransfer).ToString() + " ? document.getElementById('" + TransferMethodsDropDownList.UniqueID.Replace("$", "_") + "').style.display = '' : document.getElementById('" + TransferMethodsDropDownList.UniqueID.Replace("$", "_") + "').style.display = 'none';";
// if (!this.IsPostBack)
// {
// ReleaseFundsOptionsDropDownList.Items.Clear();
// //ReleaseFundsOptionsDropDownList.Items.Add(new ListItem("<Release Funds Options>", "-1"));
// ReleaseFundsOptionsDropDownList.Items.Add(new ListItem(Utilities.CamelCaseToString(ReleaseFundsOptions.AssignExistingTransferK.ToString()), Convert.ToInt32(ReleaseFundsOptions.AssignExistingTransferK).ToString()));
// ReleaseFundsOptionsDropDownList.Items.Add(new ListItem(Utilities.CamelCaseToString(ReleaseFundsOptions.CreateReleaseTransfer.ToString()), Convert.ToInt32(ReleaseFundsOptions.CreateReleaseTransfer).ToString()));
// ReleaseFundsOptionsDropDownList.Attributes.Remove("onchange");
// ReleaseFundsOptionsDropDownList.Attributes.Add("onchange", "javascript:" + releaseFundsOptionsJavascript);
// }
// ReleaseFundsOptionsJavascriptLabel.Visible = true;
// ReleaseFundsOptionsJavascriptLabel.Text = "<script type=\"text/javascript\">" + releaseFundsOptionsJavascript.Replace("this.", "document.getElementById('" + ReleaseFundsOptionsDropDownList.UniqueID.Replace("$", "_") + "').") + "</script>";
//}
//#endregion
//#region TransferTypesDropDownListSetup
//public void TransferMethodsDropDownListSetup()
//{
// this.TransferMethodsDropDownList.Items.Clear();
// this.TransferMethodsDropDownList.Items.Add(new ListItem(Utilities.CamelCaseToString(Transfer.Methods.BankTransfer.ToString()), Convert.ToInt32(Transfer.Methods.BankTransfer).ToString()));
// this.TransferMethodsDropDownList.Items.Add(new ListItem(Transfer.Methods.Cheque.ToString(), Convert.ToInt32(Transfer.Methods.Cheque).ToString()));
//}
//#endregion
#region GetTicketPromoterEvents
public void GetTicketPromoterEvents()
{
Query ticketPromoterEventFundsQuery = new Query();
List<Q> QueryConditionList = new List<Q>();
ticketPromoterEventFundsQuery.TableElement = new Join(TicketPromoterEvent.Columns.EventK, Event.Columns.K);
ticketPromoterEventFundsQuery.OrderBy = new OrderBy(Event.Columns.DateTime, OrderBy.OrderDirection.Descending);
if (this.uiPromotersAutoComplete.Value != null && !uiPromotersAutoComplete.Value.Equals(""))
QueryConditionList.Add(new Q(TicketPromoterEvent.Columns.PromoterK, Convert.ToInt32(uiPromotersAutoComplete.Value)));
if (this.StatusDropDownList.SelectedValue != "")
{
if (this.StatusDropDownList.SelectedValue == "Locked")
QueryConditionList.Add(TicketPromoterEvent.LockedFundsQ);
else if (this.StatusDropDownList.SelectedValue == "Funds Released")
QueryConditionList.Add(TicketPromoterEvent.FundsReleasedQ);
else if (this.StatusDropDownList.SelectedValue == "Funds Not Released")
QueryConditionList.Add(TicketPromoterEvent.AwaitingFundsReleaseQ);
else if (this.StatusDropDownList.SelectedValue == "Funds Released With Money Remaining")
{
QueryConditionList.Add(TicketPromoterEvent.FundsReleasedQ);
ticketPromoterEventFundsQuery.TableElement = new Join(ticketPromoterEventFundsQuery.TableElement, Transfer.Columns.K, TicketPromoterEvent.Columns.FundsTransferK);
QueryConditionList.Add(new Q(Transfer.Columns.IsFullyApplied, false));
}
else if (this.StatusDropDownList.SelectedValue == "No Plus Account")
{
QueryConditionList.Add(new Or(new Q(Promoter.Columns.EnableTickets, QueryOperator.IsNull, null),
new Q(Promoter.Columns.EnableTickets, false)));
}
}
if (this.SalesUsrDropDownList.SelectedValue != "")
QueryConditionList.Add(new Q(Promoter.Columns.SalesUsrK, Convert.ToInt32(SalesUsrDropDownList.SelectedValue)));
if (this.SalesUsrDropDownList.SelectedValue != "" || this.StatusDropDownList.SelectedValue == "No Plus Account")
ticketPromoterEventFundsQuery.TableElement = new Join(ticketPromoterEventFundsQuery.TableElement, Promoter.Columns.K, TicketPromoterEvent.Columns.PromoterK);
if (this.FromDateCal.Date != DateTime.MinValue)
QueryConditionList.Add(new Q(Event.Columns.DateTime, QueryOperator.GreaterThanOrEqualTo, this.FromDateCal.Date));
if (this.ToDateCal.Date != DateTime.MinValue)
QueryConditionList.Add(new Q(Event.Columns.DateTime, QueryOperator.LessThanOrEqualTo, this.ToDateCal.Date));
ticketPromoterEventFundsQuery.QueryCondition = new And(QueryConditionList.ToArray());
//ticketPromoterEventFundsQuery.Paging.RecordsPerPage = this.RecordsPerPage;
//ticketPromoterEventFundsQuery.Paging.RequestedPage = this.PageNumber;
//ticketPromoterEventFundsQuery.TopRecords = (this.PageNumber * this.RecordsPerPage) + 1;
var ticketPromoterEventSet = new TicketPromoterEventSet(ticketPromoterEventFundsQuery);
TicketPromoterEvent.CalculateTicketsAndRunFundLocksChecksAndReleaseFunds(ticketPromoterEventSet);
ticketPromoterEventSet.Reset();
List<TicketPromoterEvent> ticketPromoterEvents = ticketPromoterEventSet.ToList().FindAll(tpe => tpe.TotalFunds > 0 || this.ShowZeroMoneyTicketRunsCheckBox.Checked);
if (this.StatusDropDownList.SelectedValue == "Funds Released With Money Remaining")
{
for (int i = ticketPromoterEvents.Count - 1; i >= 0; i--)
{
if (ticketPromoterEvents[i].FundsTransfer != null && ticketPromoterEvents[i].FundsTransfer.AmountRemaining() == 0)
ticketPromoterEvents.RemoveAt(i);
}
}
this.TicketPromoterEventFundsGridView.DataSource = ticketPromoterEvents;
this.TicketPromoterEventFundsGridView.DataBind();
TotalsP.Visible = ticketPromoterEvents.Count > 0;
if (ticketPromoterEvents.Count > 0)
{
decimal totalFunds = 0;
decimal totalBookingFees = 0;
int totalTickets = 0;
int totalRefundTickets = 0;
foreach (TicketPromoterEvent tpe in ticketPromoterEvents)
{
totalFunds += tpe.TotalFunds;
totalBookingFees += tpe.TotalBookingFees;
totalTickets += tpe.SoldTickets;
totalRefundTickets += tpe.CancelledTickets;
}
this.TotalFundsLabel.Text = totalFunds.ToString("c");
this.TotalBookingFeesLabel.Text = totalBookingFees.ToString("c");
this.TotalTicketsLabel.Text = totalTickets.ToString() + (totalRefundTickets > 0 ? " (" + totalRefundTickets.ToString() + ")" : "");
}
//this.SelectAllTicketFundsLinkButton.Enabled = !FundsSelection.Equals(FundsSelectionEnum.All);
//this.SelectAwaitingFundsReleaseLinkButton.Enabled = !FundsSelection.Equals(FundsSelectionEnum.AwaitingFundsRelease);
//this.SelectLockedFundsLinkButton.Enabled = !FundsSelection.Equals(FundsSelectionEnum.LockedFunds);
//H1Title.InnerHtml = Utilities.CamelCaseToString(FundsSelection.ToString());
//this.NextPageLinkButton.Enabled = ticketPromoterEventSet.Paging.ShowNextPageLink;
//this.PrevPageLinkButton.Enabled = ticketPromoterEventSet.Paging.ShowPrevPageLink;
}
public static void EmailAccountsTicketFundsReserveAmount()
{
double ticketMoney = 0;
double ticketMoneyCencelled = 0;
double ticketMoneyWithCardnetDelay = 0;
double ticketMoneyToPromoterBankAccounts = 0;
double ticketMoneyAppliedToInvoices = 0;
double ticketMoneyReserve = 0;
/* money earnt */
Query sumTicketMoneyQuery = new Query();
//sumTicketMoneyQuery.ExtraSelectElements.Add("SumTotalFunds", "SUM([TicketPromoterEvent].[TotalFunds])");
//sumTicketMoneyQuery.OverideSql = "SELECT SUM(TotalFunds) AS SumAmount FROM TicketPromoterEvent";
sumTicketMoneyQuery.OverideSql = "SELECT SUM([Ticket].[Price]) AS SumAmount FROM [Ticket] WHERE [Ticket].[Enabled] = 1 AND [Ticket].[Quantity] > 0 AND [Ticket].[Cancelled] = 0 AND [Ticket].[K] >= 82 ";
Query sumTicketMoneyCancelledQuery = new Query();
sumTicketMoneyCancelledQuery.OverideSql = "SELECT SUM([Ticket].[Price]) AS SumAmount FROM [Ticket] WHERE [Ticket].[Enabled] = 1 AND [Ticket].[Quantity] > 0 AND [Ticket].[Cancelled] = 1 AND [Ticket].[K] >= 82 ";
/* money to promoter bank accounts */
Query sumMoneyToPromoterBankAccountsQuery = new Query();
sumMoneyToPromoterBankAccountsQuery.OverideSql = @"SELECT SUM(RefundTransfer.Amount) AS SumAmount FROM Transfer PaymentTransfer INNER JOIN Transfer RefundTransfer ON PaymentTransfer.K = RefundTransfer.TransferRefundedK
WHERE PaymentTransfer.Method = 5 AND RefundTransfer.Type = 2 AND PaymentTransfer.Type = 1 AND RefundTransfer.Method = 2 AND RefundTransfer.Status = 2";
/* money applied to DSI invoices */
Query sumTicketMoneyAppliedToInvoicesQuery = new Query();
sumTicketMoneyAppliedToInvoicesQuery.OverideSql = "SELECT SUM(IT.Amount) AS SumAmount FROM InvoiceTransfer IT INNER JOIN Transfer T ON IT.TransferK = T.K WHERE T.Method = 5 AND T.Type = 1 AND T.Status = 2";
/* Addition of CardNet delay to figures, as requested by Gee on Jan 7, 08 */
Query sumTicketMoneyCardnetDelayQuery = new Query();
sumTicketMoneyCardnetDelayQuery.OverideSql = "SELECT SUM([Ticket].[Price]) AS SumAmount FROM [Ticket] WHERE [Ticket].[Enabled] = 1 AND [Ticket].[Quantity] > 0 AND ([Ticket].[Cancelled] = 0 OR [Ticket].[CancelledDateTime] >= " + Cambro.Misc.Db.Dt(Utilities.CardnetDelay()) + ") AND [Ticket].[K] >= 82 AND [Ticket].[BuyDateTime] < " + Cambro.Misc.Db.Dt(Utilities.CardnetDelay());
try
{
TicketSet ts = new TicketSet(sumTicketMoneyQuery);
if (ts.Count == 1)
{
ticketMoney = Convert.ToDouble(ts[0].ExtraSelectElements["SumAmount"]);
}
}
catch { }
try
{
TicketSet ts = new TicketSet(sumTicketMoneyCancelledQuery);
if (ts.Count == 1)
{
ticketMoneyCencelled = Convert.ToDouble(ts[0].ExtraSelectElements["SumAmount"]);
}
}
catch { }
try
{
TicketPromoterEventSet tpes = new TicketPromoterEventSet(sumTicketMoneyCardnetDelayQuery);
if (tpes.Count == 1)
{
ticketMoneyWithCardnetDelay = Convert.ToDouble(tpes[0].ExtraSelectElements["SumAmount"]);
}
}
catch { }
try
{
TransferSet trans = new TransferSet(sumMoneyToPromoterBankAccountsQuery);
if (trans.Count == 1)
{
ticketMoneyToPromoterBankAccounts = -1 * Convert.ToDouble(trans[0].ExtraSelectElements["SumAmount"]);
}
}
catch { }
try
{
InvoiceTransferSet its = new InvoiceTransferSet(sumTicketMoneyAppliedToInvoicesQuery);
if (its.Count == 1)
{
ticketMoneyAppliedToInvoices = Convert.ToDouble(its[0].ExtraSelectElements["SumAmount"]);
}
}
catch { }
ticketMoneyReserve = ticketMoneyWithCardnetDelay - ticketMoneyToPromoterBankAccounts - ticketMoneyAppliedToInvoices;
System.Net.Mail.SmtpClient c = new System.Net.Mail.SmtpClient();
c.Host = Common.Properties.GetDefaultSmtpServer();
System.Net.Mail.MailMessage m = new System.Net.Mail.MailMessage();
m.Body = "<p>Ticket money earnt: " + ticketMoney.ToString("c") + "</p>"
+ "<p>Ticket money cancelled: " + ticketMoneyCencelled.ToString("c") + "</p>"
+ "<p>Ticket money earnt with cardnet delay: " + ticketMoneyWithCardnetDelay.ToString("c") + "</p>"
+ "<p>Ticket money to promoter bank accounts: " + ticketMoneyToPromoterBankAccounts.ToString("c") + "</p>"
+ "<p>Ticket money applied to invoices: " + ticketMoneyAppliedToInvoices.ToString("c") + "</p>"
+ "<p><br><b>Ticket money to be held in reserve: " + ticketMoneyReserve.ToString("c") + "</b></p>";
m.From = new System.Net.Mail.MailAddress(Vars.EMAIL_ADDRESS_MAIL);
if (Vars.DevEnv || Vars.IsBeta)
{
m.Subject = "Test - " + m.Subject;
m.Subject += " (" + Time.Now.ToString() + ")";
m.To.Add(Vars.EMAIL_ADDRESS_DEV_TEAM);
}
else
{
m.To.Add(Vars.EMAIL_ADDRESS_ACCOUNTS);
}
// m.To.Add(new System.Net.Mail.MailAddress(Vars.EMAIL_ADDRESS_ACCOUNTS));
m.IsBodyHtml = true;
m.Subject = "Money in reserve for ticket funds: " + ticketMoneyReserve.ToString("c");
c.Send(m);
Log.Increment(Log.Items.EmailsSent);
}
public decimal TicketFundsAwaitingReleaseAtDate(DateTime date)
{
decimal ticketFundsAwaitingRelease = 0;
Query q = new Query();
//q.ExtraSelectElements.Add("SumTicketFundsAwaitingRelease", "SUM([TicketPromoterEvent].[TotalFunds])");
//q.Columns = new ColumnSet();
q.QueryCondition = new Q(TicketPromoterEvent.Columns.PromoterK, this.K);
TicketPromoterEventSet ticketPromoterEvents = new TicketPromoterEventSet(q);
foreach (TicketPromoterEvent tpe in ticketPromoterEvents)
{
if (!tpe.FundsReleased || (tpe.FundsTransfer != null && tpe.FundsTransfer.DateTimeCreated >= date))
ticketFundsAwaitingRelease += tpe.GetTotalFundsAtDate(date);
}
return ticketFundsAwaitingRelease;
}
