public void RecordEmail(InvoiceForPosting invoice, DateTime?date = null)
{
if (!date.HasValue)
{
date = DateTime.Now;
}
invoice.LastSentToCustomer = date.Value;
using (var conn = Connection)
{
conn.Open();
using (var trx = conn.BeginTransaction())
{
try
{
conn.Execute(@"UPDATE invoices
SET last_sent_to_customer = @LastSentToCustomer
WHERE invoice_id=@InvoiceId",
invoice, trx);
trx.Commit();
}
catch
{
trx.Rollback();
throw;
}
finally
{
conn.Close();
}
}
}
}
public void Insert(InvoiceForPosting invoice)
{
invoice.InvoiceId = invoice.InvoiceId.GetId();
using (var conn = Connection)
{
conn.Open();
using (var trx = conn.BeginTransaction())
{
try
{
var balance = invoice.LineItems.Sum(li => li.Quantity * li.UnitAmount);
conn.Execute(
@"INSERT INTO invoices (invoice_id, date, customer_id, invoice_number, balance, is_paid_in_full) VALUES
(@InvoiceId, @Date, @CustomerId, @InvoiceNumber, @Balance, false)",
new
{
InvoiceId = invoice.InvoiceId,
Date = invoice.Date,
CustomerId = invoice.CustomerId,
InvoiceNumber = invoice.InvoiceNumber,
Balance = balance
}, trx);
foreach (var item in invoice.LineItems)
{
item.LineItemId = item.LineItemId.GetId();
conn.Execute(
@"INSERT INTO invoice_line_items (invoice_line_item_id, invoice_id, quantity, unit_amount, description) VALUES
(@InvoiceLineItemId, @InvoiceId, @Quantity, @UnitAmount, @Description)",
new
{
InvoiceLineItemId = item.LineItemId,
InvoiceId = invoice.InvoiceId,
Quantity = item.Quantity,
UnitAmount = item.UnitAmount,
Description = item.Description
}
, trx
);
}
trx.Commit();
conn.Close();
}
catch (Exception e)
{
trx.Rollback();
conn.Close();
throw e;
}
}
}
}
public IActionResult PostTransaction([FromBody] TransactionForDisplay transaction)
{
var transactions = new List <TransactionForDisplay>();
if (transaction.TransactionId == null || transaction.TransactionId == Guid.Empty)
{
transaction.TransactionId = Guid.NewGuid();
}
if (transaction.AccountId == null)
{
transaction.AccountId = _accountRepo.GetAll().Single(a => a.Name.Equals(transaction.AccountName, StringComparison.CurrentCultureIgnoreCase)).AccountId;
}
transaction.SetAmount();
transaction.EnteredDate = DateTime.Now;
if (transaction.CategoryId.IsNullOrEmpty() && !string.IsNullOrWhiteSpace(transaction.CategoryName))
{
var category = _categoryRepo.GetAll().SingleOrDefault(c => (c.Name.Equals(transaction.CategoryName, StringComparison.CurrentCultureIgnoreCase)));
if (category == null)
{
category = new Category
{
CategoryId = Guid.NewGuid(),
Name = transaction.CategoryName,
Type = "Expenses"
};
_categoryRepo.Insert(category);
}
transaction.CategoryId = category.CategoryId;
}
var bankFeeTransactions = TransactionHelpers.GetBankFeeTransactions(transaction, _categoryRepo, _accountRepo);
transactions.Add(transaction);
transactions.AddRange(bankFeeTransactions);
foreach (var trx in transactions)
{
_transactionRepo.Insert(trx);
}
var accountBalances = _accountRepo.GetAccountBalances().Select(a => new { a.AccountId, a.CurrentBalance });
InvoiceForPosting invoice = null;
if (transaction.InvoiceId.HasValue)
{
invoice = _invoiceRepo.Get(transaction.InvoiceId.Value);
}
var vendor = _vendorRepo.GetAll().SingleOrDefault(v => v.Name == transaction.Vendor);
return(CreatedAtAction("PostTransaction", new { id = transaction.TransactionId }, new { transactions, accountBalances, invoice, vendor }));
}
public IActionResult Post([FromBody] InvoiceForPosting invoice)
{
if (!ModelState.IsValid)
{
return(BadRequest(ModelState));
}
if (invoice.InvoiceId == null || invoice.InvoiceId == Guid.Empty)
{
invoice.InvoiceId = Guid.NewGuid();
}
_invoiceRepo.Insert(invoice);
invoice = _invoiceRepo.Get(invoice.InvoiceId);
return(CreatedAtAction("PostInvoice", new { id = invoice.InvoiceId }, invoice));
}
private string GetInvoiceHtml(InvoiceForPosting invoice)
{
var template = _configRepo.GetInvoiceTemplate();
if (string.IsNullOrWhiteSpace(template))
{
throw new Exception("No invoice template found");
}
if (invoice == null)
{
return(template);
}
// var contentPath = _hostingEnvironment.WebRootPath;
// var value = System.IO.File.ReadAllText(Path.Combine(contentPath, "InvoiceTemplate.html"))
var value = template
.Replace("{{InvoiceNumber}}", invoice.InvoiceNumber)
.Replace("{{Balance}}", invoice.Balance.ToString("C"))
.Replace("{{CustomerName}}", invoice.CustomerName)
.Replace("{{CustomerAddress}}", StringExtensions.GetAddress(invoice.CustomerStreetAddress, invoice.CustomerCity, invoice.CustomerRegion).Replace("\n", "<br/>"))
.Replace("{{InvoiceDate}}", invoice.Date.ToString("MMM dd, yyyy"))
.Replace("{{DueDate}}", invoice.Date.AddDays(30).ToString("MMM dd, yyyy"));
var lineItemTemplate = value.Substring(value.IndexOf("{{StartInvoiceLineItem}}"));
lineItemTemplate = lineItemTemplate.Substring(0, lineItemTemplate.IndexOf("{{EndInvoiceLineItem}}")).Replace("{{StartInvoiceLineItem}}", "");
var lineItemNumber = 0;
var lineItemSection = "";
foreach (var item in invoice.LineItems)
{
lineItemNumber++;
var section = lineItemTemplate
.Replace("{{ItemNumber}}", lineItemNumber.ToString())
.Replace("{{Description}}", item.Description)
.Replace("{{Quantity}}", item.Quantity.ToString("n2"))
.Replace("{{UnitAmount}}", item.UnitAmount.ToString("n2"))
.Replace("{{ItemTotal}}", (item.Quantity * item.UnitAmount).ToString("n2"));
lineItemSection += section;
}
var pos1 = value.IndexOf("{{StartInvoiceLineItem}}");
var pos2 = value.IndexOf("{{EndInvoiceLineItem}}") + "{{EndInvoiceLineItem}}".Length;
value = value.Remove(pos1, pos2 - pos1).Insert(pos1, lineItemSection);
return(value);
}
public IActionResult Put([FromRoute] Guid id, [FromBody] InvoiceForPosting invoice)
{
if (!ModelState.IsValid)
{
return(BadRequest(ModelState));
}
if (id != invoice.InvoiceId)
{
return(BadRequest());
}
_invoiceRepo.Update(invoice);
invoice = _invoiceRepo.Get(invoice.InvoiceId);
return(Ok(invoice));
}
public IActionResult DeleteTransaction([FromRoute] Guid id)
{
if (!ModelState.IsValid)
{
return(BadRequest(ModelState));
}
var transaction = _transactionRepo.Get(id);
_transactionRepo.Delete(id);
InvoiceForPosting invoice = null;
if (transaction.InvoiceId.HasValue)
{
invoice = _invoiceRepo.Get(transaction.InvoiceId.Value);
}
return(Ok(new { transaction, accountBalances = _accountRepo.GetAccountBalances(), invoice }));
}
public IActionResult UpdateTransaction([FromRoute] Guid id,
[FromBody] TransactionForDisplay transaction)
{
if (id != transaction.TransactionId)
{
return(BadRequest());
}
var originalAmount = _transactionRepo.Get(transaction.TransactionId).Amount;
transaction.SetAmount();
_transactionRepo.Update(transaction);
InvoiceForPosting invoice = null;
if (transaction.InvoiceId.HasValue)
{
invoice = _invoiceRepo.Get(transaction.InvoiceId.Value);
}
return(Ok(new { transaction, originalAmount, accountBalances = _accountRepo.GetAccountBalances(), invoice }));
}
private async Task SendInvoice(InvoiceForPosting invoice)
{
var apiKey = Environment.GetEnvironmentVariable("SENDGRID_API_KEY");
var client = new SendGridClient(apiKey);
var from = new EmailAddress("*****@*****.**", "Kyle Baley");
var to = new EmailAddress(invoice.CustomerEmail, invoice.CustomerName);
var subject = $"Invoice {invoice.InvoiceNumber} from Kyle Baley Consulting Ltd.";
var cc = new EmailAddress("*****@*****.**", "Kyle Baley");
var htmlContent = $"Invoice {invoice.InvoiceNumber} for {invoice.Balance.ToString("C")} from Kyle Baley Consulting Ltd is attached";
var plainTextContent = htmlContent;
var msg = new SendGridMessage
{
From = from,
Subject = subject,
PlainTextContent = plainTextContent,
HtmlContent = htmlContent
};
msg.AddTo(to);
msg.AddCc(cc);
var attachment = new Attachment();
using (var webClient = new WebClient())
{
var pdfApiKey = _config.GetValue <string>("Html2PdfRocketKey");
var options = new NameValueCollection();
options.Add("apikey", pdfApiKey);
options.Add("value", GetInvoiceHtml(invoice));
var attachmentContent = webClient.UploadValues("http://api.html2pdfrocket.com/pdf", options);
var file = Convert.ToBase64String(attachmentContent);
msg.AddAttachment($"Invoice {invoice.InvoiceNumber}.pdf", file);
}
var response = await client.SendEmailAsync(msg);
await Task.Run(() => _invoiceRepo.RecordEmail(invoice, null)).ConfigureAwait(false);
}
public void Update(InvoiceForPosting invoice)
{
using (var conn = Connection)
{
conn.Open();
using (var trx = conn.BeginTransaction())
{
try
{
var existingLineItems = conn.Query <InvoiceLineItemsForPosting>(
@"SELECT invoice_line_item_id as line_item_id, quantity, unit_amount, description FROM invoice_line_items
WHERE invoice_id = @InvoiceId", new { InvoiceId = invoice.InvoiceId });
var oldBalance = existingLineItems.Sum(li => (li.Quantity * li.UnitAmount));
var newBalance = invoice.LineItems.Sum(li => li.Quantity * li.UnitAmount);
invoice.Balance = newBalance;
var newLineItems = invoice.LineItems.Where(li => existingLineItems.All(li2 => li2.LineItemId != li.LineItemId));
var removedLineItems = existingLineItems.Where(li => invoice.LineItems.All(li2 => li2.LineItemId != li.LineItemId));
var updatedLineItems = invoice.LineItems.Where(li => existingLineItems.Any(li2 => li2.LineItemId == li.LineItemId));
foreach (var item in newLineItems)
{
conn.Execute(@"INSERT INTO invoice_line_items (invoice_line_item_id, invoice_id, quantity, unit_amount, description)
VALUES (@InvoiceLineItemId, @InvoiceId, @Quantity, @UnitAmount, @Description)",
new
{
InvoiceLineItemId = Guid.NewGuid(),
InvoiceId = invoice.InvoiceId,
Quantity = item.Quantity,
UnitAmount = item.UnitAmount,
Description = item.Description
}, trx);
}
foreach (var item in removedLineItems)
{
conn.Execute(@"DELETE FROM invoice_line_items WHERE invoice_line_item_id=@InvoiceLineItemId",
new { InvoiceLineItemId = item.LineItemId }, trx);
}
foreach (var item in updatedLineItems)
{
conn.Execute(@"UPDATE invoice_line_items
SET quantity = @Quantity, unit_amount = @UnitAmount, description = @Description
WHERE invoice_line_item_id = @LineItemId", item, trx);
}
// TODO: Incorporate payments
conn.Execute(@"UPDATE invoices
SET date = @Date, customer_id = @CustomerId, invoice_number = @InvoiceNumber, balance = @Balance
WHERE invoice_id=@InvoiceId",
invoice, trx);
trx.Commit();
}
catch
{
trx.Rollback();
throw;
}
finally
{
conn.Close();
}
}
}
}
