/*
* [STAThread]
* [HttpGet]
* [Route("mvinvoice/{id}.pdf")]
* public async Task<IActionResult> MvPdfInvoice(int id)
* {
* var invoice = GetMakersVillageInvoice(id);
*
* var html = await this.RenderViewAsync("mvinvoice", invoice);
*
* var converter = new SynchronizedConverter(new PdfTools());
*
* var document = new HtmlToPdfDocument {
* GlobalSettings = {
* ColorMode = ColorMode.Grayscale,
* Orientation = Orientation.Portrait,
* PaperSize = PaperKind.Letter,
* },
* Objects = {
* new ObjectSettings() {
* PagesCount = true,
* HtmlContent = html,
* },
* },
* };
*
* var pdf = converter.Convert(document);
*
* return new FileContentResult(pdf, "application/pdf");
* }
*/
private MakersVillageInvoice GetMakersVillageInvoice(int id)
{
using var db = new BillingDatabase();
var sql = @"
SELECT
details
FROM
makers_village_invoice
WHERE
makers_village_invoice_id = @0" ;
var serializedInvoice = db.ExecuteScalar <string>(sql, id);
var invoice = JsonConvert.DeserializeObject <MakersVillageInvoice>(serializedInvoice);
return(invoice);
}
public List <MakersVillageInvoice> ListMVInvoices()
{
using var db = new BillingDatabase();
var sql = @"
SELECT
makers_village_invoice_id AS Id,
title AS Title,
total_mv_paid AS TotalMvPaid,
total_mv_outstanding AS TotalMvOutstanding,
created_date AS CreatedDate
FROM
makers_village_invoice
ORDER BY
created_date DESC" ;
var list = db.Fetch <MakersVillageInvoice>(sql);
return(list);
}
public async Task <object> GenerateMVInvoice(int year, int month)
{
// Don't use .AddMonth(), gets weird with the 28/29/30/31.
var endYear = year;
var endMonth = month + 1;
if (month == 12)
{
endYear = year + 1;
endMonth = 1;
}
var startDate = new DateTime(year, month, 1);
var endDate = new DateTime(endYear, endMonth, 1);
var invoice = new MakersVillageInvoice();
invoice.Title = $"Makers Village Sublet Invoice - {year}-{month:00}";
invoice.Start = startDate;
invoice.End = endDate;
invoice.CreatedDate = DateTime.Now;
using var db = new BillingDatabase();
var sql = @"
SELECT
i.invoice_id,
i.document_number,
i.created_date,
i.contact_name,
i.public_notes,
i.private_notes,
i.amount AS billed_amount,
p.amount AS payment_amount,
i.paid_amount AS paid_amount,
s.notes
FROM
invoice i
INNER JOIN invoice_line il
ON i.invoice_id = il.invoice_id
LEFT JOIN (
SELECT
invoice_id,
SUM(amount) AS amount
FROM
payment_allocation
WHERE
payment_date >= @0
AND payment_date < @1
GROUP BY
invoice_id) p
ON i.invoice_id = p.invoice_id
LEFT JOIN (
SELECT
invoice_id,
notes
FROM
storage_notes
GROUP BY
invoice_id
ORDER BY
snapshot_date ASC) s
ON i.invoice_id = s.invoice_id
WHERE
il.notes LIKE '%Makers Village%'
AND (p.amount > 0
OR i.paid_amount < i.amount)
AND voided_date < '2000-01-01'
GROUP BY
i.invoice_id
ORDER BY
i.invoice_date ASC" ;
var members = await db.FetchAsync <DbInvoiceResult>(sql, startDate, endDate);
invoice.Invoices = new List <MakersVillageInvoice.MemberInvoice>(members.Count);
foreach (var item in members)
{
var member = new MakersVillageInvoice.MemberInvoice {
Id = item.document_number,
Created = item.created_date,
Name = item.contact_name,
PublicNotes = item.public_notes,
PrivateNotes = item.private_notes,
MmsBilled = item.billed_amount,
MmsPrePaid = item.paid_amount - item.payment_amount,
MmsPaid = item.payment_amount,
MmsOutstanding = item.billed_amount - item.paid_amount,
StorageNotes = item.notes,
};
var sql2 = @"
SELECT
notes,
amount
FROM
invoice_line
WHERE
invoice_id = @0
AND notes LIKE '%Makers Village%'
ORDER BY
invoice_line_id ASC" ;
var lines = await db.FetchAsync <MakersVillageInvoice.InvoiceLine>(sql2, item.invoice_id);
foreach (var line in lines)
{
member.MvOwed += Math.Round(line.Amount * 0.96m, 2);
}
member.Lines = lines;
member.MvPrePaid = Math.Round(member.MvOwed * member.MmsPrePaid / member.MmsBilled, 2);
member.MvPaid = Math.Round(member.MvOwed * member.MmsPaid / member.MmsBilled, 2);
member.MvOutstanding = Math.Round(member.MvOwed * member.MmsOutstanding / member.MmsBilled, 2);
invoice.TotalMmsBilled += member.MmsBilled;
invoice.TotalMmsPrePaid += member.MmsPrePaid;
invoice.TotalMmsPaid += member.MmsPaid;
invoice.TotalMmsOutstanding += member.MmsOutstanding;
invoice.TotalMvOwed += member.MvOwed;
invoice.TotalMvPrePaid += member.MvPrePaid;
invoice.TotalMvPaid += member.MvPaid;
invoice.TotalMvOutstanding += member.MvOutstanding;
invoice.Invoices.Add(member);
}
var sql3 = @"
SELECT
makers_village_invoice_adjustments_id,
date,
reason,
amount
FROM
makers_village_invoice_adjustments
WHERE
date >= @0
AND date < @1
ORDER BY
date ASC" ;
var adjustments = await db.FetchAsync <MakersVillageInvoice.Adjustment>(sql3, startDate, endDate);
foreach (var adjustment in adjustments)
{
invoice.TotalMvAdjustments += adjustment.Amount;
invoice.TotalMvPaid += adjustment.Amount;
}
invoice.Adjustments = adjustments;
var serializedInvoice = JsonConvert.SerializeObject(invoice);
var dbInvoice = new makers_village_invoice {
title = invoice.Title,
year = year,
month = month,
total_mms_billed = invoice.TotalMmsBilled,
total_mms_prepaid = invoice.TotalMmsPrePaid,
total_mms_paid = invoice.TotalMmsPaid,
total_mms_outstanding = invoice.TotalMmsOutstanding,
total_mv_owed = invoice.TotalMvOwed,
total_mv_prepaid = invoice.TotalMvPrePaid,
total_mv_adjustments = invoice.TotalMvAdjustments,
total_mv_paid = invoice.TotalMvPaid,
total_mv_outstanding = invoice.TotalMvOutstanding,
created_date = invoice.CreatedDate,
details = serializedInvoice,
};
var result = await db.InsertAsync(dbInvoice);
return(result);
}
public async Task Load(DateTimeOffset start, DateTimeOffset end)
{
var wildApricot = new WildApricotClient();
loadStatus.range = 1;
loadStatus.progress = 0;
loadStatus.status = "Loading Invoice List";
// Pad the loading, because of time zone offset stupidity.
var invoiceIdList = await wildApricot.GetInvoicesListAsync(
accountId : wildApricot.accountId,
startDate : start.AddDays(-1),
endDate : end.AddDays(1),
idsOnly : true
);
loadStatus.status = "Loading Voidable Invoices";
var sql = @"
SELECT
invoice_id
FROM
invoice
WHERE
is_paid = 0
AND created_date > @0
AND voided_date < '2000-01-01'" ;
using var db = new BillingDatabase();
var voidableInvoices = await db.FetchAsync <int>(sql, start.AddMonths(-2));
foreach (var voidableId in voidableInvoices)
{
if (!invoiceIdList.InvoiceIdentifiers.Contains(voidableId))
{
invoiceIdList.InvoiceIdentifiers.Add(voidableId);
loadStatus.range += 1;
}
}
loadStatus.status = "Loading Payment List";
var paymentIdList = await wildApricot.GetPaymentsListAsync(
accountId : wildApricot.accountId,
startDate : start.AddDays(-1),
endDate : end.AddDays(1),
idsOnly : true
);
loadStatus.range = invoiceIdList.InvoiceIdentifiers.Count + paymentIdList.PaymentIdentifiers.Count;
var count = 0;
foreach (var item in paymentIdList.PaymentIdentifiers)
{
loadStatus.progress += 1;
count += 1;
loadStatus.status = $"Loading Payment {count} of {paymentIdList.PaymentIdentifiers.Count}";
var waPaymentAllocations = await wildApricot.GetPaymentAllocationsListAsync(wildApricot.accountId, paymentId : item);
foreach (var waAllocation in waPaymentAllocations)
{
if (waAllocation.Invoice?.Id == null)
{
continue;
}
if (waAllocation.InvoiceDate < start)
{
var id = waAllocation.Invoice.Id ?? -1;
if (!invoiceIdList.InvoiceIdentifiers.Contains(id))
{
invoiceIdList.InvoiceIdentifiers.Add(id);
loadStatus.range += 1;
}
}
var dbPaymentAllocation = new payment_allocation {
payment_allocation_id = waAllocation.Id.Value,
amount = (decimal)waAllocation.Value.Value,
invoice_id = waAllocation.Invoice.Id.Value,
payment_id = waAllocation.Payment.Id.Value,
payment_date = waAllocation.PaymentDate.Value.LocalDateTime,
};
db.Delete <payment_allocation>(dbPaymentAllocation.payment_allocation_id);
db.Insert(dbPaymentAllocation);
}
}
count = 0;
foreach (var item in invoiceIdList.InvoiceIdentifiers)
{
loadStatus.progress += 1;
count += 1;
loadStatus.status = $"Loading Invoice Heading {count} of {invoiceIdList.InvoiceIdentifiers.Count}";
var waInvoice = await wildApricot.GetInvoiceDetailsAsync(wildApricot.accountId, item);
var dbInvoice = new invoice {
invoice_id = waInvoice.Id.Value,
document_number = waInvoice.DocumentNumber,
invoice_date = waInvoice.DocumentDate.Value.LocalDateTime,
amount = (decimal)waInvoice.Value.Value,
paid_amount = (decimal)waInvoice.PaidAmount.Value,
is_paid = (sbyte)((waInvoice.IsPaid ?? false) ? 1 : 0),
type = waInvoice.OrderType.ToString(),
private_notes = waInvoice.Memo ?? "",
public_notes = waInvoice.PublicMemo ?? "",
created_date = waInvoice.CreatedDate.Value.LocalDateTime,
updated_date = waInvoice.UpdatedDate.GetValueOrDefault().LocalDateTime,
voided_date = waInvoice.VoidedDate.GetValueOrDefault().LocalDateTime,
contact_id = waInvoice.Contact.Id.Value,
contact_name = waInvoice.Contact.Name,
creator_id = waInvoice.CreatedBy?.Id ?? -1,
updater_id = waInvoice.UpdatedBy?.Id ?? -1,
};
db.Delete <invoice>(dbInvoice.invoice_id);
db.Insert(dbInvoice);
var i = 0;
var makersVillageInvoice = false;
foreach (var waLine in waInvoice.OrderDetails)
{
i += 1;
loadStatus.status = $"Loading Invoice Lines {count} of {loadStatus.range}, Line {i}";
var dbLine = new invoice_line {
invoice_line_id = ((long)dbInvoice.invoice_id * 100) + i,
invoice_id = dbInvoice.invoice_id,
amount = (decimal)waLine.Value.Value,
type = waLine.OrderDetailType.ToString(),
notes = waLine.Notes,
};
db.Delete <invoice_line>(dbLine.invoice_line_id);
db.Insert(dbLine);
if (dbLine.notes.Contains("Makers Village"))
{
makersVillageInvoice = true;
}
}
if (makersVillageInvoice)
{
var waContact = await wildApricot.GetContactDetailsAsync(wildApricot.accountId, (int)dbInvoice.contact_id);
var note = "";
foreach (var field in waContact.FieldValues)
{
if (field.FieldName == "Makers Village Storage Description")
{
note = field.Value.ToString();
break;
}
}
var dbNote = new storage_note {
invoice_id = dbInvoice.invoice_id,
contact_id = dbInvoice.contact_id,
snapshot_date = DateTime.Now,
notes = note,
};
db.Insert(dbNote);
}
}
loadStatus.range = 0;
loadStatus.progress = 0;
loadStatus.status = "Load Completed";
}
