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);
}
void BindPromoterItems()
{
if (CurrentBuyer is Promoter)
{
this.UserItemListPanel.Visible = false;
this.BuyerItemListPanel.Visible = true;
if (PaymentTypeIsCampaignCredits)
{
this.TotalCreditsRow.Visible = true;
this.TotalCreditsAsMoneyRow.Visible = true;
this.BuyerItemListColumn1HeaderLabel.Text = "Items";
this.BuyerItemListColumn2HeaderLabel.Text = "";
this.BuyerItemListColumn3HeaderLabel.Text = "Credits";
VatRow.Visible = true;
VatLabel.Text = Utilities.MoneyToHTML(TotalVatOnCampaignCreditsAsMoney);
invoiceTotalAsCampaignCredits = 0;
bool showPerItemDiscount = false;
double? fixedDiscount = null;
foreach (CampaignCredit cc in CampaignCredits)
{
invoiceTotalAsCampaignCredits += -cc.Credits;
if (fixedDiscount == null)
fixedDiscount = cc.FixedDiscount;
if (cc.FixedDiscount >= 0)
{
if (Math.Round(fixedDiscount.Value * 100, 2) != Math.Round(cc.FixedDiscount * 100, 2))
showPerItemDiscount = true;
}
}
double nonFixedCampaignCreditDiscount = CampaignCredit.GetDiscountForCredits(invoiceTotalAsCampaignCredits, CurrentPromoter);
foreach (CampaignCredit cc in CampaignCredits)
{
HtmlTableRow invoiceTr = new HtmlTableRow();
InvoicesBody.Controls.Add(invoiceTr);
HtmlTableCell invoiceNameTd = new HtmlTableCell();
invoiceNameTd.Style["background-color"] = "transparent";
string discountHtml = "";
if (showPerItemDiscount)
{
double discount = cc.FixedDiscount > 0 ? cc.FixedDiscount : nonFixedCampaignCreditDiscount;
string percentageFormat = Math.Round(discount * 100, 2) == Convert.ToInt32(discount * 100) ? "P0" : "P2";
discountHtml = " <small>(@ <nobr>" + discount.ToString(percentageFormat) + "</nobr> discount)</small>";
}
invoiceNameTd.InnerHtml = HttpUtility.HtmlEncode(cc.Description) + discountHtml;
HtmlTableCell invoicePriceTd = new HtmlTableCell();
invoicePriceTd.Align = "right";
invoicePriceTd.Style["background-color"] = "transparent";
HtmlTableCell emptyTd = new HtmlTableCell();
emptyTd.Style["background-color"] = "transparent";
emptyTd.InnerHtml = " ";
// credits are negative on the campaign credit list
invoicePriceTd.InnerHtml = "<nobr>" + HttpUtility.HtmlEncode((-cc.Credits).ToString("N0")) + "</nobr>";
invoiceTr.Cells.Add(invoiceNameTd);
invoiceTr.Cells.Add(emptyTd);
invoiceTr.Cells.Add(invoicePriceTd);
}
}
else if (Invoices.Count == 1 && Invoices[0].K == 0)
{
this.TotalCreditsRow.Visible = false;
this.TotalCreditsAsMoneyRow.Visible = false;
this.BuyerItemListColumn1HeaderLabel.Text = "Invoice";
this.BuyerItemListColumn2HeaderLabel.Text = "";
this.BuyerItemListColumn3HeaderLabel.Text = "Price";
VatLabel.Text = Utilities.MoneyToHTML(Invoices[0].Vat);
VatRow.Visible = true;
foreach (InvoiceItemDataHolder iidh in Invoices[0].InvoiceItemDataHolderList)
{
HtmlTableRow invoiceTr = new HtmlTableRow();
InvoicesBody.Controls.Add(invoiceTr);
HtmlTableCell invoiceNameTd = new HtmlTableCell();
invoiceNameTd.Style["background-color"] = "transparent";
string percentageFormat = Math.Round(iidh.Discount * 100, 2) == Convert.ToInt32(iidh.Discount * 100) ? "P0" : "P2";
string discountHtml = " <small>(@ <nobr>" + iidh.Discount.ToString(percentageFormat) + "</nobr> discount)</small>";
invoiceNameTd.InnerHtml = HttpUtility.HtmlEncode(iidh.ShortDescription) + discountHtml;
HtmlTableCell invoicePriceTd = new HtmlTableCell();
invoicePriceTd.Align = "right";
invoicePriceTd.Style["background-color"] = "transparent";
HtmlTableCell emptyTd = new HtmlTableCell();
emptyTd.Style["background-color"] = "transparent";
emptyTd.InnerHtml = " ";
invoicePriceTd.InnerHtml = Utilities.MoneyToHTML(iidh.Price);
invoiceTr.Cells.Add(invoiceNameTd);
invoiceTr.Cells.Add(emptyTd);
invoiceTr.Cells.Add(invoicePriceTd);
}
}
else
{
this.TotalCreditsRow.Visible = false;
this.TotalCreditsAsMoneyRow.Visible = false;
foreach (InvoiceDataHolder idh in Invoices)
{
idh.Type = Invoice.Types.Invoice;
HtmlTableRow itemsTr = new HtmlTableRow();
HtmlTableRow invoiceTr = new HtmlTableRow();
InvoicesBody.Controls.Add(invoiceTr);
InvoicesBody.Controls.Add(itemsTr);
itemsTr.ID = "PaymentItem" + idh.K;
itemsTr.Style["display"] = "none";
#region Name
HtmlTableCell invoiceNameTd = new HtmlTableCell();
invoiceNameTd.Style["background-color"] = "transparent";
string invoiceHtml = "New invoice";
if (idh.K > 0 && idh.PromoterK > 0)
invoiceHtml = Utilities.LinkNewWindow(idh.UrlReport(), "Invoice #" + idh.K.ToString());
invoiceNameTd.InnerHtml = "<a href=\"#\" onclick=\"var elem = document.getElementById('" + itemsTr.ClientID + "'); var img = document.getElementById('" + this.ClientID + "_PaymentPlusMinus" + idh.K + "'); img.src = elem.style.display == 'none' ? '/gfx/minus.gif' : '/gfx/plus.gif'; elem.style.display = elem.style.display == 'none' ? '' : 'none'; return false;\"><img id=\"" + this.ClientID + "_PaymentPlusMinus" + idh.K + "\" src=\"/gfx/plus.gif\" alt=\"Show items\" border=\"0\" align=\"absmiddle\" style=\"margin-right:4px;\" /></a>" + invoiceHtml;
#endregion
#region Total
HtmlTableCell invoiceTotalTd = new HtmlTableCell();
invoiceTotalTd.Align = "right";
invoiceTotalTd.Style["background-color"] = "transparent";
invoiceTotalTd.InnerHtml = Utilities.MoneyToHTML(idh.Total);
#endregion
#region Due
HtmlTableCell invoiceDueTd = new HtmlTableCell();
invoiceDueTd.Align = "right";
invoiceDueTd.Style["background-color"] = "transparent";
invoiceDueTd.InnerHtml = Utilities.MoneyToHTML(idh.AmountDue);
#endregion
invoiceTr.Cells.Add(invoiceNameTd);
invoiceTr.Cells.Add(invoiceTotalTd);
invoiceTr.Cells.Add(invoiceDueTd);
HtmlTableCell itemsTd = new HtmlTableCell();
itemsTd.Style["background-color"] = "transparent";
itemsTd.Style["padding"] = "0px";
itemsTd.Style["padding-left"] = "14px";
itemsTd.Style["width"] = "279px";
itemsTd.ColSpan = 3;
itemsTd.Align = "right";
#region Items table
StringBuilder sb = new StringBuilder();
sb.Append("<table border=\"0\" cellpadding=\"0\" cellspacing=\"0\" width=\"100%\">");
foreach (InvoiceItemDataHolder iidh in idh.InvoiceItemDataHolderList)
{
sb.Append("<tr><td align=\"left\" style=\"background-color:transparent;padding-top:1px;padding-bottom:1px;\"><small>");
sb.Append(iidh.Description);
sb.Append("</small></td><td align=\"right\" style=\"background-color:transparent;padding-top:1px;padding-bottom:1px;\"><small>");
// Show IncVat for Promoters and other users. As per David Brophy on Oct 24, 2006
// Show ExVat for all, then add VAT as its own line item after all invoice items. this is for OASIS v1.5, Dec 12, 2006
sb.Append(Utilities.MoneyToHTML(iidh.Price));
sb.Append("</small></td></tr>");
}
VatLabel.Text = Utilities.MoneyToHTML(idh.Vat);
//sb.Append("<tr><td align=\"left\" style=\"background-color:transparent;padding-top:1px;padding-bottom:1px;\"><small>VAT</small></td><td align=\"right\" style=\"background-color:transparent;padding-top:1px;padding-bottom:1px;\"><small><nobr>");
//sb.Append();
//sb.Append("</nobr></small></td></tr>");
// Only if the some money has been paid or credited, then we go to DB to get successful transfer applied
if (idh.AmountDue != idh.Total)
{
Query invoiceTransferQuery = new Query();
invoiceTransferQuery.QueryCondition = new And(new Q(InvoiceTransfer.Columns.InvoiceK, idh.K),
new Q(Transfer.Columns.Status, Transfer.StatusEnum.Success));
invoiceTransferQuery.TableElement = new Join(InvoiceTransfer.Columns.TransferK, Transfer.Columns.K, QueryJoinType.Inner);
invoiceTransferQuery.Columns = new ColumnSet(InvoiceTransfer.Columns.Amount, InvoiceTransfer.Columns.TransferK, Transfer.Columns.Type);
InvoiceTransferSet invoiceTransferSet = new InvoiceTransferSet(invoiceTransferQuery);
decimal invoiceTransferTotal = 0;
foreach (InvoiceTransfer invoiceTransfer in invoiceTransferSet)
{
decimal amount = invoiceTransfer.Amount;
if (((Transfer.TransferTypes)invoiceTransfer.ExtraSelectElements["Transfer_Type"]).Equals(Transfer.TransferTypes.Payment))
amount = -1 * Math.Abs(amount);
else
amount = Math.Abs(amount);
sb.Append("<tr><td align=\"left\" style=\"background-color:transparent;padding-top:1px;padding-bottom:1px;\"><small><nobr>Transfer #");
sb.Append(invoiceTransfer.TransferK.ToString());
sb.Append("</nobr></td><td align=\"right\" style=\"background-color:transparent;padding-top:1px;padding-bottom:1px;\"><small>");
sb.Append(Utilities.MoneyToHTML(amount));
sb.Append("</small></td></tr>");
invoiceTransferTotal += invoiceTransfer.Amount;
}
// Test if there is still money unaccounted for, then go to DB for credits applied
if (invoiceTransferTotal + idh.AmountDue < idh.Total)
{
Query invoiceCreditQuery = new Query();
invoiceCreditQuery.QueryCondition = new Q(InvoiceCredit.Columns.InvoiceK, idh.K);
invoiceCreditQuery.Columns = new ColumnSet(InvoiceCredit.Columns.Amount, InvoiceCredit.Columns.CreditInvoiceK);
InvoiceCreditSet invoiceCreditSet = new InvoiceCreditSet(invoiceCreditQuery);
foreach (InvoiceCredit invoiceCredit in invoiceCreditSet)
{
sb.Append("<tr><td align=\"left\" style=\"background-color:transparent;padding-top:1px;padding-bottom:1px;\"><small><nobr>Credit #");
sb.Append(invoiceCredit.CreditInvoiceK.ToString());
sb.Append("</nobr></td><td align=\"right\" style=\"background-color:transparent;padding-top:1px;padding-bottom:1px;\"><small>");
sb.Append(Utilities.MoneyToHTML(invoiceCredit.Amount));
sb.Append("</small></td></tr>");
}
}
}
sb.Append("</table>");
#endregion
//if (idh.K == 0)
itemsTd.InnerHtml = sb.ToString();
itemsTr.Cells.Add(itemsTd);
}
}
}
}
//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();
}
public void UpdateAndResolveOverapplied()
{
decimal amountRemaining = AmountRemaining();
if (Math.Round(amountRemaining,2) < 0)
{
Query invoiceTransfersAppliedQuery = new Query(new And(new Q(InvoiceTransfer.Columns.TransferK, this.K),
new Q(Invoice.Columns.Type, Invoice.Types.Invoice)));
invoiceTransfersAppliedQuery.TableElement = new Join(InvoiceTransfer.Columns.InvoiceK, Invoice.Columns.K);
invoiceTransfersAppliedQuery.OrderBy = new OrderBy(Invoice.Columns.DueDateTime, OrderBy.OrderDirection.Ascending);
InvoiceTransferSet invoiceTransferSet = new InvoiceTransferSet(invoiceTransfersAppliedQuery);
string transferNote = "Transfer overapplied by " + ((double)(-1 * amountRemaining)).ToString("c") + ".";
decimal amountToUnApply = 0;
for (int i = invoiceTransferSet.Count - 1; i >= 0; i--)
{
if (Math.Round(amountRemaining,2) < 0)
{
Invoice invoice = new Invoice(invoiceTransferSet[i].InvoiceK);
if (invoiceTransferSet[i].Amount <= -1 * amountRemaining)
{
amountToUnApply = invoiceTransferSet[i].Amount;
invoiceTransferSet[i].Delete();
}
else
{
amountToUnApply = -1 * amountRemaining;
invoiceTransferSet[i].Amount -= amountToUnApply;
invoiceTransferSet[i].Amount = Math.Round(invoiceTransferSet[i].Amount, 2);
invoiceTransferSet[i].Update();
}
transferNote += string.Format("\nAutomatically unapplied {0} from invoice #{1}.", amountToUnApply.ToString("c"), invoice.K);
amountRemaining += amountToUnApply;
//if (this.Method == Methods.TicketSales)
// BankExport.GenerateBankExportForTicketFundsUsed(this, -1 * amountToUnApply, new Invoice(invoiceTransferSet[i].InvoiceK));
invoice.AddNote(string.Format("Transfer #{0} was overapplied. {1} has been unapplied from transfer #{0}.", this.K, amountToUnApply.ToString("c")), "System");
invoice.UpdateAndSetPaidStatus();
}
}
this.AddNote(transferNote, "System");
if (Math.Round(amountRemaining, 2) == 0)
this.IsFullyApplied = true;
else
this.IsFullyApplied = false;
this.Update();
}
}
protected void SetupTransferButton_Click(object sender, EventArgs e)
{
SetupAllOutstandingInvoices();
SetupPayment.Reset();
List<int> invoiceKList = new List<int>();
// Loop through Repeater dataset, get all checked, and pass those K's to the payment screen
foreach (RepeaterItem rpi in this.PromoterAccountItemRepeater.Items)
{
CheckBox outstandingCheckBox = (CheckBox)rpi.FindControl("OutstandingCheckBox");
if (outstandingCheckBox.Checked == true)
{
Label invoiceKLabel = (Label)rpi.FindControl("InvoiceKLabel");
invoiceKList.Add(Convert.ToInt32(invoiceKLabel.Text));
}
}
foreach (int invoiceK in invoiceKList)
{
Invoice invoice = new Invoice(invoiceK);
InvoiceCreditSet invoiceCreditSet = new InvoiceCreditSet(new Query(new Q(InvoiceCredit.Columns.InvoiceK, invoice.K)));
InvoiceTransferSet invoiceTransferSet = new InvoiceTransferSet(new Query(new Q(InvoiceTransfer.Columns.InvoiceK, invoice.K)));
var total = invoice.Total;
var vat = invoice.Vat;
var price = invoice.Price;
foreach (InvoiceCredit invoiceCredit in invoiceCreditSet)
{
Invoice credit = new Invoice(invoiceCredit.CreditInvoiceK);
//price += credit.Price;
//vat += credit.Vat;
total += credit.Total;
}
foreach (InvoiceTransfer invoiceTransfer in invoiceTransferSet)
{
Transfer transfer = new Transfer(invoiceTransfer.TransferK);
if (transfer.Status.Equals(Transfer.StatusEnum.Success))
{
total -= transfer.Amount;
}
}
InvoiceDataHolder idh = new InvoiceDataHolder(invoice);
//idh.K = 0;
this.SetupPayment.Invoices.Add(idh);
}
this.SetupPayment.PromoterK = CurrentPromoter.K;
this.SummaryPanel.Visible = false;
this.SetupTransferPanel.Visible = true;
// this.SetupPayment.Initialize();
}
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 AmountApplied()
{
decimal amountApplied = 0;
Query invoiceTransferQuery = new Query(new Q(InvoiceTransfer.Columns.TransferK, this.K));
invoiceTransferQuery.Columns = new ColumnSet(InvoiceTransfer.Columns.Amount);
InvoiceTransferSet invoiceTransferSet = new InvoiceTransferSet(invoiceTransferQuery);
foreach (InvoiceTransfer invoiceTransfer in invoiceTransferSet)
amountApplied += invoiceTransfer.Amount;
return Math.Round(amountApplied, 2);
}
public StringBuilder GenerateReportStringBuilder(bool linksEnabled)
{
DateTime taxDate = DateTime.Now;
if (this.TaxDateTime != null && this.TaxDateTime > DateTime.MinValue)
taxDate = this.TaxDateTime;
StringBuilder sb = new StringBuilder();
decimal amountPaid = 0;
sb.Append(@"<form id='form1' runat='server'><div style='font-family:Verdana;'><table width='100%' border='0' cellspacing='0' cellpadding='0' height='100%'><tr><td valign='top'>
<table width='100%'>");
sb.Append(Utilities.GenerateHTMLHeaderRowString(this.Type.Equals(Invoice.Types.Credit) ? this.TypeToString.ToUpper() + " NOTE" : this.TypeToString.ToUpper()));
sb.Append(@"<tr>
<td colspan=1 align='left' valign='top' width='450' style='padding-left:48px;'>");
if (this.Promoter != null)
{
if (this.Promoter.AccountsName.Length > 0)
{
sb.Append(this.Promoter.AccountsName);
sb.Append("<br>");
}
else if (this.Usr != null && this.Usr.FullName.Length > 0 && this.Usr.IsPromoter && this.Usr.IsPromoterK(this.K))
{
sb.Append(this.Usr.FullName);
sb.Append("<br>");
}
else if (this.Promoter.PrimaryUsr != null && this.Promoter.PrimaryUsr.FullName.Length > 0)
{
sb.Append(this.Promoter.PrimaryUsr.FullName);
sb.Append("<br>");
}
if (this.Promoter.Name.Length > 0)
{
sb.Append(this.Promoter.Name);
sb.Append("<br>");
}
sb.Append(this.Promoter.AddressHtml);
}
else if (this.Usr != null)
{
if (this.Usr.FullName.Length > 0)
{
sb.Append(this.Usr.FullName);
sb.Append("<br>");
}
sb.Append(this.Usr.AddressHtml());
}
// Addition of Created and renaming of "Date" to "Tax Date", as per Dave's request 7/2/07
sb.Append(@"</td><td width='350'></td><td valign='top' width='100'>" + this.TypeToString + " No.");
if (this.PurchaseOrderNumber.Length > 0)
sb.Append("<br><br>Purchase Order No.");
sb.Append("<br><br>Acc No.<br><br>Created<br><br>Tax Date");
if (this.Type.Equals(Invoice.Types.Invoice))
sb.Append("<br><br>Due Date");
sb.Append(@"</td><td align='right' valign='top' width='125'>");
if (this.Type.Equals(Invoice.Types.Invoice))
sb.Append("INV");
else
sb.Append("CRD");
sb.Append(this.K.ToString());
if (this.PurchaseOrderNumber.Length > 0)
sb.Append("<br><br><nobr>" + this.PurchaseOrderNumber.Replace("-", "‑"));
sb.Append("</nobr><br><br>");
if (this.Promoter != null)
sb.Append(this.PromoterK.ToString());
else if (this.Usr != null)
sb.Append(this.UsrK.ToString());
else
sb.Append(" ");
// Addition of Created, as per Dave's request 7/2/07
sb.Append("<br><br>");
sb.Append(this.CreatedDateTime.ToString("dd/MM/yy"));
sb.Append("<br><br>");
// Replacing CreatedDateTime with TaxDateTime, as per Gee's request for OASIS v1.5
//sb.Append(this.CreatedDateTime.ToString("dd/MM/yy"));
sb.Append(this.TaxDateTime.ToString("dd/MM/yy"));
if (this.Type.Equals(Invoice.Types.Invoice))
{
sb.Append("<br><br>");
sb.Append(this.DueDateTime.ToString("dd/MM/yy"));
}
sb.Append("</td></tr>");
//if(this.PaidDateTime != null && this.PaidDateTime > DateTime.MinValue)
//{
// sb.Append(@"<br><br><b>Date Completed</b><br>" + this.PaidDateTime.ToShortDateString());
//}
sb.Append(@"</table><br><br>
<table width='100%' cellspacing='0' cellpadding='3' class='BorderBlack Top Right Bottom'>
<tr>
<td style='vertical-align:bottom;' class='BorderBlack Bottom Left' width='350'><b>Item</b></td>
<td style='vertical-align:bottom;' class='BorderBlack Bottom Left' width='55' align='left'><b>Tax Code</b></td>
<td style='vertical-align:bottom;' class='BorderBlack Bottom Left' width='90' align='left'><b>Price</b></td>
<td style='vertical-align:bottom;' class='BorderBlack Bottom Left' width='85' align='left'><b>VAT</b></td>
<td style='vertical-align:bottom;' class='BorderBlack Bottom Left' width='90' align='left'><b>Total</b></td>
</tr>");
Query InvoiceItemQuery = new Query(new Q(InvoiceItem.Columns.InvoiceK, this.K));
InvoiceItemSet invoiceItemSet = new InvoiceItemSet(InvoiceItemQuery);
List<InvoiceItem.VATCodes> invoiceItemVatCodes = new List<InvoiceItem.VATCodes>();
foreach (InvoiceItem invoiceItem in invoiceItemSet)
{
if (!invoiceItemVatCodes.Contains(invoiceItem.VatCode))
invoiceItemVatCodes.Add(invoiceItem.VatCode);
sb.Append(@"<tr>
<td class='BorderBlack Left'>" + invoiceItem.Description +
(invoiceItem.Discount > 0 ? " <small>@ " + invoiceItem.Discount.ToString("P2") + " discount</small>" : "") +
@"</td>");
if (!this.VatCode.Equals(Invoice.VATCodes.T1))
{
sb.Append(@"<td class='BorderBlack Left' width='55' align='left'>" + this.VatCode.ToString() + @"</td>");
}
else
{
sb.Append(@"<td class='BorderBlack Left' width='55' align='left'>" + invoiceItem.VatCode.ToString() + @"</td>");
}
sb.Append(@"<td class='BorderBlack Left' width='90' align='right'>" + Utilities.MoneyToHTML(invoiceItem.Price) + @"</td>
<td class='BorderBlack Left' width='85' align='right'>" + Utilities.MoneyToHTML(invoiceItem.Vat) + @"</td>
<td class='BorderBlack Left' width='90' align='right'>" + Utilities.MoneyToHTML(invoiceItem.Total) + @"</td>
</tr>");
}
sb.Append(@"<tr>
<td class='BorderBlack Top Left' colspan='2' align='right'><b>TOTAL:</b></td>
<td class='BorderBlack Top Left' width='90' align='right'><b>" + Utilities.MoneyToHTML(this.Price) + @"</b></td>
<td class='BorderBlack Top Left' width='85' align='right'><b>" + Utilities.MoneyToHTML(this.Vat) + @"</b></td>
<td class='BorderBlack Top Left' width='90' align='right'><b>" + Utilities.MoneyToHTML(this.Total) + @"</b></td>
</tr></table>");
InvoiceItem.VATCodes[] invoiceItemVatCodeArray = invoiceItemVatCodes.ToArray();
Array.Sort(invoiceItemVatCodeArray);
sb.Append("<small><i><b>VAT Rate:</b> ");
if (this.VatCode.Equals(Invoice.VATCodes.T1) && invoiceItemVatCodeArray.Length > 0)
{
foreach (InvoiceItem.VATCodes vatCode in invoiceItemVatCodeArray)
{
sb.Append(vatCode.ToString());
sb.Append("=");
sb.Append(InvoiceItem.VATRate(vatCode, taxDate).ToString("0.0%"));
sb.Append(" ");
}
}
else
{
sb.Append(this.VatCode.ToString());
sb.Append("=");
sb.Append(Invoice.VATRate(this.VatCode, taxDate).ToString("0.0%"));
}
sb.Append("</i></small><br>");
sb.Append("<br>");
Query InvoiceTransferQuery = new Query(new And(new Q(InvoiceTransfer.Columns.InvoiceK, this.K),
new Or(new Q(Transfer.Columns.Status, Transfer.StatusEnum.Pending),
new Q(Transfer.Columns.Status, Transfer.StatusEnum.Success),
new Q(Transfer.Columns.Status, Transfer.StatusEnum.Cancelled))));
InvoiceTransferQuery.TableElement = new Join(InvoiceTransfer.Columns.TransferK, Transfer.Columns.K);
InvoiceTransferSet invoiceTransferSet = new InvoiceTransferSet(InvoiceTransferQuery);
bool nonSuccessfulTransfer = false;
if (invoiceTransferSet.Count > 0)
{
sb.Append(@"<br><table width='100%' cellspacing='0' cellpadding='3' class='BorderBlack Top Right Bottom'>
<tr>
<td style='vertical-align:bottom;' class='BorderBlack Bottom Left' width='205'><b>Transfer</b></td>
<td style='vertical-align:bottom;' class='BorderBlack Bottom Left' width='65' align='left'><b>Date</b></td>
<td style='vertical-align:bottom;' class='BorderBlack Bottom Left' width='75' align='left'><b>Method</b></td>
<td style='vertical-align:bottom;' class='BorderBlack Bottom Left' width='150' align='left'><b>Method Ref#</b></td>
<td style='vertical-align:bottom;' class='BorderBlack Bottom Left' width='90' align='left'><b>Status</b></td>
<td style='vertical-align:bottom;' class='BorderBlack Bottom Left' width='85' align='left'><b>Amount</b></td>
</tr>");
foreach (InvoiceTransfer invoiceTransfer in invoiceTransferSet)
{
Transfer transfer = new Transfer(invoiceTransfer.TransferK);
sb.Append(@"<tr>
<td class='BorderBlack Left'>");
if (linksEnabled)
sb.Append(Utilities.Link(transfer.UrlReport(), transfer.Type.ToString() + " #" + transfer.K.ToString()));
else
sb.Append(transfer.Type.ToString() + " #" + transfer.K.ToString());
sb.Append(@"</td>
<td class='BorderBlack Left' width='65' align='left'>" + transfer.DateTimeCreated.ToString("dd/MM/yy") + @"</td>
<td class='BorderBlack Left' width='75' align='left'><nobr>" + Utilities.CamelCaseToString(transfer.Method.ToString()) + @"</nobr></td>
<td class='BorderBlack Left' width='150' align='left'>");
sb.Append(transfer.ReferenceNumberToHtml());
sb.Append(@"</td>
<td class='BorderBlack Left' width='90' align='left'>" + transfer.Status.ToString() + @"</td>
<td class='BorderBlack Left' width='85' align='right'>" + Utilities.MoneyToHTML(invoiceTransfer.Amount) + @"</td>
</tr>");
if (!transfer.Status.Equals(Transfer.StatusEnum.Success))
nonSuccessfulTransfer = true;
else
amountPaid += invoiceTransfer.Amount;
}
sb.Append(@"</table>");
// For now, this note only pertains to Invoices
if (this.Type.Equals(Invoice.Types.Invoice) && nonSuccessfulTransfer == true)
sb.Append(@"<small><i>(<b>Note:</b> Only successful transfers will be applied to the payment total. Pending and cancelled transfers will not be applied)</i></small><br>");
sb.Append("<br>");
}
Query InvoiceCreditQuery = new Query();
if (this.Type.Equals(Invoice.Types.Invoice))
{
InvoiceCreditQuery = new Query(new Q(InvoiceCredit.Columns.InvoiceK, this.K));
}
else
{
InvoiceCreditQuery = new Query(new Q(InvoiceCredit.Columns.CreditInvoiceK, this.K));
}
InvoiceCreditSet invoiceCreditSet = new InvoiceCreditSet(InvoiceCreditQuery);
if (invoiceCreditSet.Count > 0)
{
string invoiceHeader = "Invoice";
Invoice invoice = new Invoice();
if (this.Type.Equals(Invoice.Types.Invoice))
invoiceHeader = "Credit";
sb.Append(@"<br><table width='100%' cellspacing='0' cellpadding='3' class='BorderBlack Top Bottom Right'>
<tr>
<td style='vertical-align:bottom;' class='BorderBlack Bottom Left' width='335'><b>" + invoiceHeader + @"</b></td>
<td style='vertical-align:bottom;' class='BorderBlack Bottom Left' width='70' align='left'><b>Date</b></td>
<td style='vertical-align:bottom;' class='BorderBlack Bottom Left' width='90' align='left'><b>Price</b></td>
<td style='vertical-align:bottom;' class='BorderBlack Bottom Left' width='85' align='left'><b>VAT</b></td>
<td style='vertical-align:bottom;' class='BorderBlack Bottom Left' width='90' align='left'><b>Total</b></td>
</tr>");
foreach (InvoiceCredit invoiceCredit in invoiceCreditSet)
{
if (this.Type.Equals(Invoice.Types.Invoice))
{
// Credit amounts are negative
amountPaid -= invoiceCredit.Amount;
invoice = new Invoice(invoiceCredit.CreditInvoiceK);
}
else
invoice = new Invoice(invoiceCredit.InvoiceK);
sb.Append(@"<tr>
<td class='BorderBlack Left'>");
if (linksEnabled)
sb.Append(Utilities.Link(invoice.UrlReport(), invoiceHeader + " #" + invoice.K.ToString()));
else
sb.Append(invoiceHeader + " #" + invoice.K.ToString());
// Replacing CreatedDateTime with TaxDateTime, as per Gee's request for OASIS v1.5
sb.Append(@"</td>
<td class='BorderBlack Left' width='70' align='left'>" + invoice.TaxDateTime.ToString("dd/MM/yy") + @"</td>
<td class='BorderBlack Left' width='90' align='right'>" + Utilities.MoneyToHTML(invoice.Price) + @"</td>
<td class='BorderBlack Left' width='85' align='right'>" + Utilities.MoneyToHTML(invoice.Vat) + @"</td>
<td class='BorderBlack Left' width='90' align='right'>" + Utilities.MoneyToHTML(invoice.Total) + @"</td>
</tr>");
}
sb.Append(@"</table><br><br>");
}
if (this.Type.Equals(Invoice.Types.Invoice))
{
// Invoice Summary
sb.Append(@"<br><table width='250'>
<tr>
<td colspan=2><b>Summary</b></td>
</tr>
<tr>
<td width='135'>Invoice Total:</td>
<td width='115' align='right'>" + Utilities.MoneyToHTML(this.Total) + @"</td>
</tr>
<tr>
<td width='135'>Payment Total:</td>
<td width='115' align='right'>" + Utilities.MoneyToHTML(amountPaid) + @"</td>
</tr>
<tr>
<td width='135'><b>Outstanding:</b></td>
<td width='115' align='right'><b>" + Utilities.MoneyToHTML(this.Total - amountPaid) + @"</b></td>
</tr></table><br><br>");
}
sb.Append(@"</td></tr>");
sb.Append(@"
<tr>
<td valign='bottom' align='center'>
<div style='width:50%;padding:20px;border:2px solid #000000;text-align:left;'>
Bank details for payments:<br>
Development Hell Limited<br>
Barlcays Bank PLC, Commercial Bank Basingstoke<br>
Sort Code: 20-37-63<br>
Account number: 00478377<br>
For international payments:<br>
IBAN - GB04BARC20376300478377<br>
Swift - BARCGB22
</div>
</td>
</tr>");
// DSI Registration Footer
sb.Append(Utilities.GenerateHTMLFooterRowString());
sb.Append(@"</table></div></form>");
return sb;
}
//private InvoiceTransferSet successfulInvoiceTransfers;
#endregion
#region UpdateAndAutoApplySuccessfulTransfersWithAvailableMoney
public void UpdateAndAutoApplySuccessfulTransfersWithAvailableMoney()
{
if (this.Type.Equals(Invoice.Types.Invoice) && this.Paid == false)
{
decimal amountDue = this.AmountDue;
if (amountDue > 0)
{
decimal availableMoney = 0m;
if (this.Promoter != null)
availableMoney = this.Promoter.GetAvailableMoney();
else if (this.Usr != null)
availableMoney = this.Usr.GetBalance();
// Only apply if they have positive available money
if (Math.Round(availableMoney, 2) > 0)
{
Query transferQuery = new Query();
if (this.Promoter != null)
{
transferQuery = new Query(new And(new Q(Transfer.Columns.IsFullyApplied, false),
new Q(Transfer.Columns.PromoterK, this.PromoterK),
new Q(Transfer.Columns.Status, Transfer.StatusEnum.Success),
new Q(Transfer.Columns.Type, Transfer.TransferTypes.Payment)));
if (this.Promoter.OverrideApplyTicketFundsToInvoices)
{
transferQuery.QueryCondition = new And(transferQuery.QueryCondition,
new Q(Transfer.Columns.Method, QueryOperator.NotEqualTo, Transfer.Methods.TicketSales));
}
}
else if (this.Usr != null)
{
transferQuery = new Query(new And(new Q(Transfer.Columns.IsFullyApplied, false),
new Q(Transfer.Columns.PromoterK, 0),
new Q(Transfer.Columns.UsrK, this.UsrK),
new Q(Transfer.Columns.Status, Transfer.StatusEnum.Success),
new Q(Transfer.Columns.Type, Transfer.TransferTypes.Payment)));
}
transferQuery.OrderBy = new OrderBy(Transfer.Columns.DateTimeComplete, OrderBy.OrderDirection.Ascending);
TransferSet transferSet = new TransferSet(transferQuery);
if (transferSet.Count > 0)
{
bool complete = false;
ApplyTransfersToThisInvoice(transferSet);
this.UpdateAndSetPaidStatus();
// Remove or decrease amount of any Pending Invoice Transfers that, if successful, would bring the amount paid over the total invoice cost
Query invoiceTransferPendingQuery = new Query();
invoiceTransferPendingQuery.QueryCondition = new And(new Q(InvoiceTransfer.Columns.InvoiceK, this.K),
new Q(Transfer.Columns.Status, Transfer.StatusEnum.Pending));
invoiceTransferPendingQuery.TableElement = new Join(InvoiceTransfer.Columns.TransferK, Transfer.Columns.K);
InvoiceTransferSet invoiceTransferSet = new InvoiceTransferSet(invoiceTransferPendingQuery);
var amountPaid = this.AmountPaid;
var amountPending = 0m;
foreach (InvoiceTransfer invoiceTransfer in invoiceTransferSet)
{
var invoiceTransferAmount = Math.Round(invoiceTransfer.Amount, 2);
amountPending += Math.Round(invoiceTransfer.Amount, 2);
Transfer transfer = new Transfer(invoiceTransfer.TransferK);
if (Math.Round(amountPaid, 2) >= Math.Round(Total, 2))
{
invoiceTransfer.Delete();
this.AddNote("Transfer #" + invoiceTransfer.TransferK.ToString() + " has been unapplied from this invoice", "System");
}
else if (Math.Round(amountPaid + amountPending, 2) > Math.Round(Total, 2))
{
invoiceTransfer.Amount += Math.Round((Total - amountPaid) - amountPending, 2);
amountPending += Math.Round(invoiceTransfer.Amount - invoiceTransferAmount, 2);
if (Math.Round(invoiceTransfer.Amount, 2) == 0)
{
this.AddNote("Transfer #" + invoiceTransfer.TransferK.ToString() + " has been unapplied from this invoice", "System");
invoiceTransfer.Delete();
}
else
{
this.AddNote("Transfer #" + invoiceTransfer.TransferK.ToString() + " amount applied has been updated", "System");
invoiceTransfer.Update();
}
}
if (invoiceTransferAmount != 0 && transfer.IsFullyApplied == true)
{
transfer.IsFullyApplied = false;
transfer.Update();
}
}
}
}
}
}
this.UpdateAndSetPaidStatus();
}
public void UpdateAndSetPaidStatus()
{
decimal totalPaid = this.AmountPaid;
if (Math.Round(this.Total, 2) == Math.Round(totalPaid, 2))
{
this.SetPaidAndPaidDateTime(true);
}
// If Invoice has been overpaid, remove InvoiceTransfer link amounts until totalPaid == this.Total
else if (Math.Round(this.Total, 2) < Math.Round(totalPaid, 2) && this.Type.Equals(Invoice.Types.Invoice))
{
Query invoiceTransfersAppliedQuery = new Query(new And(new Q(InvoiceTransfer.Columns.InvoiceK, this.K),
new Q(Transfer.Columns.Type, Transfer.TransferTypes.Payment)));
invoiceTransfersAppliedQuery.TableElement = new Join(InvoiceTransfer.Columns.TransferK, Transfer.Columns.K);
invoiceTransfersAppliedQuery.OrderBy = new OrderBy(Transfer.Columns.DateTimeComplete, OrderBy.OrderDirection.Ascending);
InvoiceTransferSet invoiceTransferSet = new InvoiceTransferSet(invoiceTransfersAppliedQuery);
string invoiceNote = "Invoice overpaid by " + ((double)(totalPaid - this.Total)).ToString("c") + ".";
decimal amountToUnApply = 0;
for (int i = invoiceTransferSet.Count - 1; i >= 0; i--)
{
if (totalPaid > this.Total)
{
Transfer transfer = new Transfer(invoiceTransferSet[i].TransferK);
if (invoiceTransferSet[i].Amount <= totalPaid - this.Total)
{
amountToUnApply = invoiceTransferSet[i].Amount;
invoiceTransferSet[i].Delete();
}
else
{
amountToUnApply = totalPaid - this.Total;
invoiceTransferSet[i].Amount -= amountToUnApply;
invoiceTransferSet[i].Update();
}
// if(transfer.Method == Transfer.Methods.TicketSales)
// BankExport.GenerateBankExportForTicketFundsUsed(transfer, -1 * amountToUnApply, this);
invoiceNote += string.Format("\nAutomatically unapplied {0} from transfer #{1}.", amountToUnApply.ToString("c"), transfer.K);
totalPaid -= amountToUnApply;
transfer.AddNote(string.Format("Invoice #{0} was overpaid. {1:} has been unapplied from this transfer.", this.K, amountToUnApply.ToString("c")), "System");
transfer.IsFullyApplied = false;
transfer.Update();
}
}
this.AddNote(invoiceNote, "System");
this.SetPaidAndPaidDateTime(true);
//this.AddNote("This invoice has been overpaid by the sum of " + ((decimal)Math.Round(totalPaid - this.Total, 2)).ToString("0.00"), "Admin");
}
else
this.SetPaidAndPaidDateTime(false);
this.Update();
}
private void RollbackTransfer()
{
try
{
if (CurrentTransfer != null && CurrentTransfer.K > 0)
{
InvoiceTransferSet invoiceTransferSet = new InvoiceTransferSet(new Query(new Q(InvoiceTransfer.Columns.TransferK, CurrentTransfer.K)));
for (int i = invoiceTransferSet.Count - 1; i >= 0; i--)
{
invoiceTransferSet[i].Delete();
}
CurrentTransfer.Delete();
}
}
catch (Exception ex)
{
// rollback failed. email admins
Utilities.AdminEmailAlert("<p>Exception occurred during rollback of transfer in SetupPayment control</p><p>Transfer K=" + CurrentTransfer.K.ToString() + "</p>",
"Exception occurred during rollback of transfer (K=" + CurrentTransfer.K.ToString() + ") in SetupPayment control", ex, CurrentTransfer);
}
}
void BindPromoterItems()
{
if (PromoterK > 0)
{
this.PromoterItemListPanel.Visible = true;
if (Invoices.Count == 1 && Invoices[0].K == 0)
{
this.PromoterItemListColumn2HeaderLabel.Text = "";
this.PromoterItemListColumn3HeaderLabel.Text = "Price";
foreach (InvoiceItemDataHolder iidh in Invoices[0].InvoiceItemDataHolderList)
{
//HtmlTableRow itemsTr = new HtmlTableRow();
HtmlTableRow invoiceTr = new HtmlTableRow();
InvoicesBody.Controls.Add(invoiceTr);
//InvoicesBody.Controls.Add(itemsTr);
//itemsTr.ID = "PaymentItem" + idh.K;
//itemsTr.Style["display"] = "none";
#region Name
HtmlTableCell invoiceNameTd = new HtmlTableCell();
invoiceNameTd.Style["background-color"] = "transparent";
invoiceNameTd.InnerHtml = iidh.Description;
#endregion
#region Price
HtmlTableCell invoicePriceTd = new HtmlTableCell();
invoicePriceTd.Align = "right";
invoicePriceTd.Style["background-color"] = "transparent";
invoicePriceTd.InnerHtml = iidh.Price.ToString("c");
#endregion
#region Empty HtmlTableCell
HtmlTableCell emptyTd = new HtmlTableCell();
emptyTd.Style["background-color"] = "transparent";
emptyTd.InnerHtml = " ";
#endregion
invoiceTr.Cells.Add(invoiceNameTd);
invoiceTr.Cells.Add(emptyTd);
invoiceTr.Cells.Add(invoicePriceTd);
//if (idh.K == 0)
// itemsTd.InnerHtml = sb.ToString();
//itemsTr.Cells.Add(itemsTd);
}
HtmlTableRow vatTr = new HtmlTableRow();
InvoicesBody.Controls.Add(vatTr);
vatTr.ID = "PaymentVATTotal";
#region Empty HtmlTableCell
HtmlTableCell emptyVatTd = new HtmlTableCell();
emptyVatTd.Style["background-color"] = "transparent";
emptyVatTd.InnerHtml = " ";
#endregion
#region VAT Name
HtmlTableCell vatNameTd = new HtmlTableCell();
vatNameTd.Style["background-color"] = "transparent";
vatNameTd.Align = "right";
vatNameTd.InnerHtml = "VAT";
#endregion
#region VAT Total
HtmlTableCell vatTotalTd = new HtmlTableCell();
vatTotalTd.Align = "right";
vatTotalTd.Style["background-color"] = "transparent";
vatTotalTd.InnerHtml = Invoices[0].Vat.ToString("c");
#endregion
vatTr.Cells.Add(emptyVatTd);
vatTr.Cells.Add(vatNameTd);
vatTr.Cells.Add(vatTotalTd);
}
else
{
foreach (InvoiceDataHolder idh in Invoices)
{
HtmlTableRow itemsTr = new HtmlTableRow();
HtmlTableRow invoiceTr = new HtmlTableRow();
InvoicesBody.Controls.Add(invoiceTr);
InvoicesBody.Controls.Add(itemsTr);
itemsTr.ID = "PaymentItem" + idh.K;
itemsTr.Style["display"] = "none";
#region Name
HtmlTableCell invoiceNameTd = new HtmlTableCell();
invoiceNameTd.Style["background-color"] = "transparent";
string invoiceHtml = "New invoice";
if (idh.K > 0 && idh.PromoterK > 0)
invoiceHtml = Utilities.LinkNewWindow(idh.UrlReport(), "Invoice #" + idh.K.ToString());
invoiceNameTd.InnerHtml = "<a href=\"#\" onclick=\"var elem = document.getElementById('" + itemsTr.ClientID + "'); var img = document.getElementById('" + this.ClientID + "_PaymentPlusMinus" + idh.K + "'); img.src = elem.style.display == 'none' ? '/gfx/minus.gif' : '/gfx/plus.gif'; elem.style.display = elem.style.display == 'none' ? '' : 'none'; return false;\"><img id=\"" + this.ClientID + "_PaymentPlusMinus" + idh.K + "\" src=\"/gfx/plus.gif\" alt=\"Show items\" border=\"0\" align=\"absmiddle\" style=\"margin-right:4px;\" /></a>" + invoiceHtml;
#endregion
#region Total
HtmlTableCell invoiceTotalTd = new HtmlTableCell();
invoiceTotalTd.Align = "right";
invoiceTotalTd.Style["background-color"] = "transparent";
invoiceTotalTd.InnerHtml = idh.Total.ToString("c");
#endregion
#region Due
HtmlTableCell invoiceDueTd = new HtmlTableCell();
invoiceDueTd.Align = "right";
invoiceDueTd.Style["background-color"] = "transparent";
invoiceDueTd.InnerHtml = idh.AmountDue.ToString("c");
#endregion
invoiceTr.Cells.Add(invoiceNameTd);
invoiceTr.Cells.Add(invoiceTotalTd);
invoiceTr.Cells.Add(invoiceDueTd);
HtmlTableCell itemsTd = new HtmlTableCell();
itemsTd.Style["background-color"] = "transparent";
itemsTd.Style["padding"] = "0px";
itemsTd.Style["padding-left"] = "14px";
itemsTd.Style["width"] = "376px";
itemsTd.ColSpan = 3;
itemsTd.Align = "right";
#region Items table
StringBuilder sb = new StringBuilder();
sb.Append("<table border=\"0\" cellpadding=\"0\" cellspacing=\"0\" width=\"100%\">");
foreach (InvoiceItemDataHolder iidh in idh.InvoiceItemDataHolderList)
{
sb.Append("<tr><td align=\"left\" style=\"background-color:transparent;padding-top:1px;padding-bottom:1px;\"><small>");
sb.Append(iidh.Description);
sb.Append("</small></td><td align=\"right\" style=\"background-color:transparent;padding-top:1px;padding-bottom:1px;\"><small><nobr>");
// Show IncVat for Promoters and other users. As per David Brophy on Oct 24, 2006
// Show ExVat for all, then add VAT as its own line item after all invoice items. this is for OASIS v1.5, Dec 12, 2006
sb.Append(iidh.Price.ToString("c"));
sb.Append("</nobr></small></td></tr>");
}
sb.Append("<tr><td align=\"left\" style=\"background-color:transparent;padding-top:1px;padding-bottom:1px;\"><small>VAT</small></td><td align=\"right\" style=\"background-color:transparent;padding-top:1px;padding-bottom:1px;\"><small><nobr>");
sb.Append(idh.Vat.ToString("c"));
sb.Append("</nobr></small></td></tr>");
// Only if the some money has been paid or credited, then we go to DB to get successful transfer applied
if (idh.AmountDue != idh.Total)
{
Query invoiceTransferQuery = new Query();
invoiceTransferQuery.QueryCondition = new And(new Q(InvoiceTransfer.Columns.InvoiceK, idh.K),
new Q(Transfer.Columns.Status, Transfer.StatusEnum.Success));
invoiceTransferQuery.TableElement = new Join(InvoiceTransfer.Columns.TransferK, Transfer.Columns.K, QueryJoinType.Inner);
invoiceTransferQuery.Columns = new ColumnSet(InvoiceTransfer.Columns.Amount, InvoiceTransfer.Columns.TransferK, Transfer.Columns.Type);
InvoiceTransferSet invoiceTransferSet = new InvoiceTransferSet(invoiceTransferQuery);
decimal invoiceTransferTotal = 0;
foreach (InvoiceTransfer invoiceTransfer in invoiceTransferSet)
{
decimal amount = invoiceTransfer.Amount;
if (((Transfer.TransferTypes)invoiceTransfer.ExtraSelectElements["Transfer_Type"]).Equals(Transfer.TransferTypes.Payment))
amount = -1 * Math.Abs(amount);
else
amount = Math.Abs(amount);
sb.Append("<tr><td align=\"left\" style=\"background-color:transparent;padding-top:1px;padding-bottom:1px;\"><small><nobr>Transfer #");
sb.Append(invoiceTransfer.TransferK.ToString());
sb.Append("</nobr></td><td align=\"right\" style=\"background-color:transparent;padding-top:1px;padding-bottom:1px;\"><small><nobr>");
sb.Append(amount.ToString("c"));
sb.Append("</nobr></small></td></tr>");
invoiceTransferTotal += invoiceTransfer.Amount;
}
// Test if there is still money unaccounted for, then go to DB for credits applied
if (invoiceTransferTotal + idh.AmountDue < idh.Total)
{
Query invoiceCreditQuery = new Query();
invoiceCreditQuery.QueryCondition = new Q(InvoiceCredit.Columns.InvoiceK, idh.K);
invoiceCreditQuery.Columns = new ColumnSet(InvoiceCredit.Columns.Amount, InvoiceCredit.Columns.CreditInvoiceK);
InvoiceCreditSet invoiceCreditSet = new InvoiceCreditSet(invoiceCreditQuery);
foreach (InvoiceCredit invoiceCredit in invoiceCreditSet)
{
sb.Append("<tr><td align=\"left\" style=\"background-color:transparent;padding-top:1px;padding-bottom:1px;\"><small><nobr>Credit #");
sb.Append(invoiceCredit.CreditInvoiceK.ToString());
sb.Append("</nobr></td><td align=\"right\" style=\"background-color:transparent;padding-top:1px;padding-bottom:1px;\"><small><nobr>");
sb.Append(invoiceCredit.Amount.ToString("c"));
sb.Append("</nobr></small></td></tr>");
}
}
}
sb.Append("</table>");
#endregion
//if (idh.K == 0)
itemsTd.InnerHtml = sb.ToString();
itemsTr.Cells.Add(itemsTd);
}
}
InvoiceTotalLabel.Text = InvoiceTotal.ToString("c");
}
}