// invoices are calculated whenever a meterreading is created or changed
public void CalculateInvoice(MeterReading meterReading)
{
// rates are caled from Application state rather than querying DB
RatesObject rates = (RatesObject)HttpContext.Application["Rates"];
Invoice newInvoice = new Invoice();
newInvoice.CustomerID = meterReading.CustomerID;
newInvoice.Year = meterReading.Year;
// calculations of usage per band and subtotals; currently hardcoded for 4 bands
if (meterReading.Quantity > 0)
{
if (meterReading.Quantity >= rates.BandA)
{
newInvoice.QtyRateA = rates.BandA; // default 90
}
else
{
newInvoice.QtyRateA = meterReading.Quantity;
}
newInvoice.SubtotalA = newInvoice.QtyRateA * rates.RateA; //usually zero unless rate for Band A changes
}
if (meterReading.Quantity > rates.BandA)
{
if (meterReading.Quantity >= rates.BandB)
{
newInvoice.QtyRateB = rates.BandB - rates.BandA; // maximum for band B 400-90 = 310
}
else
{
newInvoice.QtyRateB = meterReading.Quantity - rates.BandA;
}
newInvoice.SubtotalB = newInvoice.QtyRateB * rates.RateB;
}
if (meterReading.Quantity > rates.BandB)
{
if (meterReading.Quantity >= rates.BandC)
{
newInvoice.QtyRateC = rates.BandC - rates.BandB; // 800-400 = 400
}
else
{
newInvoice.QtyRateC = meterReading.Quantity - rates.BandB;
}
newInvoice.SubtotalC = newInvoice.QtyRateC * rates.RateC;
}
if (meterReading.Quantity > rates.BandC)
{
newInvoice.QtyRateD = meterReading.Quantity - rates.BandC;
newInvoice.SubtotalD = newInvoice.QtyRateD * rates.RateD;
}
newInvoice.Total = newInvoice.SubtotalA + newInvoice.SubtotalB + newInvoice.SubtotalC + newInvoice.SubtotalD;
newInvoice.GrandTotal = newInvoice.Total + newInvoice.Arrears;
db.Invoices.Add(newInvoice);
db.SaveChanges();
}
public ActionResult ImportCsv(HttpPostedFileBase file)
{
string path = null;
try
{
if (file.ContentLength > 0)
{
var fileName = Path.GetFileName(file.FileName);
path = AppDomain.CurrentDomain.BaseDirectory + "upload/" + fileName;
file.SaveAs(path);
var csv = new CsvReader(new StreamReader(path));
csv.Configuration.IgnoreHeaderWhiteSpace = true;
var customers = csv.GetRecords<ImportCustomer>();
//clear the database before saving imported records
var customersToDelete = from m in db.Customers
select m;
foreach (var customer in customersToDelete)
{
db.Customers.Remove(customer);
}
//save imported records to DB
foreach (var customer in customers)
{
Customer newCust = new Customer();
newCust.InvoiceNumber = int.Parse(customer.InvNo);
newCust.LastName = customer.Surname;
newCust.FirstName = customer.Name;
newCust.Address1 = customer.Address1;
newCust.Address2 = customer.Address2;
newCust.Address3 = customer.Address3;
newCust.Address4 = customer.Address4;
db.Customers.Add(newCust);
//convert empty strings to "0" so they can be parsed as numerical data
if (customer.Arrears2008 == "")
{
customer.Arrears2008 = "0";
}
if (customer.Arrears2009 == "")
{
customer.Arrears2009 = "0";
}
if (customer.Arrears2010 == "")
{
customer.Arrears2010 = "0";
}
if (customer.Arrears2011 == "")
{
customer.Arrears2011 = "0";
}
if (customer.QtyRateA == "")
{
customer.QtyRateA = "0";
}
if (customer.QtyRateB == "")
{
customer.QtyRateB = "0";
}
if (customer.QtyRateC == "")
{
customer.QtyRateC = "0";
}
if (customer.QtyRateD == "")
{
customer.QtyRateD = "0";
}
if (customer.QtyRateE == "")
{
customer.QtyRateE = "0";
}
if (customer.BSubTotal == "")
{
customer.BSubTotal = "0";
}
if (customer.CSubTotal == "")
{
customer.CSubTotal = "0";
}
if (customer.DSubTotal == "")
{
customer.DSubTotal = "0";
}
if (customer.ESubTotal == "")
{
customer.ESubTotal = "0";
}
if (customer.Total == "")
{
customer.Total = "0";
}
if (customer.TotalArrears == "")
{
customer.TotalArrears = "0";
}
if (customer.GrandTotal == "")
{
customer.GrandTotal = "0";
}
if (customer.Paid == "")
{
customer.Paid = "0";
}
db.SaveChanges(); //necessary to save here to generate an ID for the customer
if (float.Parse(customer.Arrears2008) > 0)
{
Invoice arrears08 = new Invoice();
arrears08.CustomerID = newCust.ID;
arrears08.Year = 2008;
arrears08.Total = float.Parse(customer.Arrears2008);
db.Invoices.Add(arrears08);
}
if (float.Parse(customer.Arrears2009) > 0)
{
Invoice arrears09 = new Invoice();
arrears09.CustomerID = newCust.ID;
arrears09.Year = 2009;
arrears09.Total = float.Parse(customer.Arrears2009);
db.Invoices.Add(arrears09);
}
if (float.Parse(customer.Arrears2010) > 0)
{
Invoice arrears10 = new Invoice();
arrears10.CustomerID = newCust.ID;
arrears10.Year = 2010;
arrears10.Total = float.Parse(customer.Arrears2010);
db.Invoices.Add(arrears10);
}
if (float.Parse(customer.Arrears2011) > 0)
{
Invoice arrears11 = new Invoice();
arrears11.CustomerID = newCust.ID;
arrears11.Year = 2011;
arrears11.Total = float.Parse(customer.Arrears2011);
db.Invoices.Add(arrears11);
}
// not sure about 2012 arrears
if (float.Parse(customer.QtyRateA) > 0) // if customer used any water in last year
{
MeterReading reading = new MeterReading();
reading.CustomerID = newCust.ID;
var quantity = float.Parse(customer.QtyRateA)
+ float.Parse(customer.QtyRateB)
+ float.Parse(customer.QtyRateC)
+ float.Parse(customer.QtyRateD)
+ float.Parse(customer.QtyRateE);
reading.Quantity = (int)quantity;
reading.Year = 2013;
db.MeterReadings.Add(reading);
// import 2013 invoice
Invoice invoice13 = new Invoice();
invoice13.CustomerID = newCust.ID;
invoice13.Year = 2013;
var rateA = float.Parse(customer.QtyRateA);
invoice13.QtyRateA = (int)rateA;
var rateB = float.Parse(customer.QtyRateB);
invoice13.QtyRateB = (int)rateB;
var rateC = float.Parse(customer.QtyRateC);
invoice13.QtyRateC = (int)rateC;
var rateD = float.Parse(customer.QtyRateD);
invoice13.QtyRateD = (int)rateD;
var rateE = float.Parse(customer.QtyRateE);
invoice13.QtyRateE = (int)rateE;
invoice13.SubtotalA = 0;
invoice13.SubtotalB = float.Parse(customer.BSubTotal);
invoice13.SubtotalC = float.Parse(customer.CSubTotal);
invoice13.SubtotalD = float.Parse(customer.DSubTotal);
invoice13.SubtotalE = float.Parse(customer.ESubTotal);
invoice13.Total = float.Parse(customer.Total);
invoice13.Arrears = float.Parse(customer.TotalArrears);
invoice13.GrandTotal = float.Parse(customer.GrandTotal);
invoice13.AmountPaid = float.Parse(customer.Paid);
db.Invoices.Add(invoice13);
}
}
db.SaveChanges();
ViewBag.FileName = fileName;
return View(customers);
}
}
catch
{
ViewData["error"] = "Upload Failed";
}
return View();
}
public ActionResult Create([Bind(Include = "ID,CustomerID,Year,Quantity")] MeterReading meterReading)
{
if (ModelState.IsValid)
{
// check to ensure there is not already a reading for that customer and year
var readings = from c in db.MeterReadings
select c;
readings = readings.Where(s => s.CustomerID.Equals(meterReading.CustomerID));
if (readings.Where(t => t.Year.Equals(meterReading.Year)).Count() > 0)
{
return RedirectToAction("Index", new { error = "error" });
//return RedirectToAction("Index");
}
db.MeterReadings.Add(meterReading);
db.SaveChanges();
// generate invoice using usage
// rates are caled from Application state rather than querying DB
RatesObject rates = (RatesObject)HttpContext.Application["Rates"];
Invoice newInvoice = new Invoice();
newInvoice.CustomerID = meterReading.CustomerID;
newInvoice.Year = meterReading.Year;
// calculations of usage per band and subtotals; currently hardcoded for 5 bands
// calculation method based on spreadsheet sheet 2 billing rates, not 2013 data
if (meterReading.Quantity > 0 )
{
if (meterReading.Quantity >= rates.BandA)
{
newInvoice.QtyRateA = rates.BandA; // default 115
}
else
{
newInvoice.QtyRateA = meterReading.Quantity;
}
newInvoice.SubtotalA = newInvoice.QtyRateA * rates.RateA; //usually zero unless rate for Band A changes
}
if (meterReading.Quantity > rates.BandA)
{
if (meterReading.Quantity >= rates.BandB)
{
newInvoice.QtyRateB = rates.BandB - rates.BandA; // maximum for band B 400-115 = 285
}
else
{
newInvoice.QtyRateB = meterReading.Quantity - rates.BandA;
}
newInvoice.SubtotalB = newInvoice.QtyRateB * rates.RateB;
}
if (meterReading.Quantity > rates.BandB)
{
if (meterReading.Quantity >= rates.BandC)
{
newInvoice.QtyRateC = rates.BandC - rates.BandB; // 800-400 = 400
}
else
{
newInvoice.QtyRateC = meterReading.Quantity - rates.BandB;
}
newInvoice.SubtotalC = newInvoice.QtyRateC * rates.RateC;
}
if (meterReading.Quantity > rates.BandC)
{
if (meterReading.Quantity >= rates.BandD)
{
newInvoice.QtyRateD = rates.BandD - rates.BandC; // 1500-800 = 700
}
else
{
newInvoice.QtyRateD = meterReading.Quantity - rates.BandC;
}
newInvoice.SubtotalD = newInvoice.QtyRateD * rates.RateD;
}
if (meterReading.Quantity > rates.BandD)
{
newInvoice.QtyRateE = meterReading.Quantity - rates.BandD;
newInvoice.SubtotalE = newInvoice.QtyRateE * rates.RateE;
}
newInvoice.Total = newInvoice.SubtotalA + newInvoice.SubtotalB + newInvoice.SubtotalC + newInvoice.SubtotalD + newInvoice.SubtotalE;
newInvoice.GrandTotal = newInvoice.Total + newInvoice.Arrears;
db.Invoices.Add(newInvoice);
// create a new note
Note newNote = new Note();
newNote.CustomerID = meterReading.CustomerID;
newNote.NoteText = "Meter Reading added and Invoice Calculated at " + DateTime.Now + ".";
db.Notes.Add(newNote);
db.SaveChanges();
return RedirectToAction("Index");
}
ViewBag.CustomerID = new SelectList(db.Customers, "ID", "FullName", meterReading.CustomerID);
return View(meterReading);
}
// invoice pdf generator, called from pdf and mail actions
public bool MakePdfInvoice(Invoice invoice)
{
Customer customer = db.Customers.Find(invoice.CustomerID);
RatesObject rates = (RatesObject)HttpContext.Application["Rates"];
try
{
Document doc = new Document(iTextSharp.text.PageSize.A4, 50, 50, 25, 25);
var path = AppDomain.CurrentDomain.BaseDirectory + "pdf/" + customer.ID + customer.FullName + " Invoice " + invoice.Year + ".pdf";
var output = new FileStream(path, FileMode.Create);
PdfWriter wri = PdfWriter.GetInstance(doc, output);
doc.Open();
var titleFont = FontFactory.GetFont("Cambria", 16, Font.BOLD);
var subTitleFont = FontFactory.GetFont("Cambria", 12, Font.BOLD);
var boldTableFont = FontFactory.GetFont("Cambria", 10, Font.BOLD);
var endingMessageFont = FontFactory.GetFont("Cambria", 9, Font.ITALIC);
var bodyFont = FontFactory.GetFont("Cambria", 11, Font.NORMAL);
var tableFont = FontFactory.GetFont("CAmbria", 10, Font.NORMAL);
doc.Add(new Paragraph("Lavey-Billis Co-Op Water Group Society", bodyFont));
doc.Add(Chunk.NEWLINE);
var invheader = new Paragraph("Invoice " + invoice.Year, titleFont);
invheader.Alignment = Element.ALIGN_RIGHT;
doc.Add(invheader);
var invno = new Paragraph("Invoice Number: " + customer.InvoiceNumber, bodyFont);
invno.Alignment = Element.ALIGN_RIGHT;
doc.Add(invno);
var date = new Paragraph("Date: " + String.Format("{0:dddd, MMMM d, yyyy}", DateTime.Now), bodyFont);
date.Alignment = Element.ALIGN_RIGHT;
doc.Add(date);
doc.Add(new Paragraph("Phone", bodyFont));
doc.Add(new Paragraph("S. McDermott 087-2368653", bodyFont));
doc.Add(new Paragraph("T. Owens 087-9881967", bodyFont));
doc.Add(new Paragraph("P. McMahon 086-8138286", bodyFont));
doc.Add(new Paragraph("S. Smith", bodyFont));
doc.Add(Chunk.NEWLINE);
PdfPTable invisible = new PdfPTable(2); //invisible table for formatting
invisible.TotalWidth = 500f;
invisible.LockedWidth = true;
invisible.DefaultCell.BorderWidth = 0;
invisible.AddCell(new Phrase("To:", boldTableFont));
PdfPCell invisCell = new PdfPCell(new Phrase("Subject: Water Rates for " + invoice.Year, boldTableFont));
invisCell.BorderWidth = 0;
invisCell.HorizontalAlignment = 2;
invisible.AddCell(invisCell);
doc.Add(invisible);
doc.Add(new Paragraph(customer.FullName, tableFont));
doc.Add(new Paragraph(customer.Address1, tableFont));
doc.Add(new Paragraph(customer.Address2, tableFont));
doc.Add(new Paragraph(customer.Address3, tableFont));
doc.Add(new Paragraph(customer.Address4, tableFont));
doc.Add(Chunk.NEWLINE);
PdfPTable table = new PdfPTable(3);
table.TotalWidth = 500f;
table.LockedWidth = true;
table.DefaultCell.BorderWidth = 1;
table.AddCell(new Phrase("Rate Type", boldTableFont));
table.AddCell(new Phrase("Cubic Metres", boldTableFont));
table.AddCell(new Phrase("€Euro", boldTableFont));
table.AddCell(new Phrase("Rate A", tableFont));
table.AddCell(new Phrase("0-" + rates.BandA.ToString(), tableFont));
table.AddCell(new Phrase(rates.RateA.ToString(), tableFont));
table.AddCell(new Phrase("Rate B", tableFont));
int lowerLimitB = rates.BandA + 1;
table.AddCell(new Phrase(lowerLimitB.ToString() + "-" + rates.BandB.ToString(), tableFont));
table.AddCell(new Phrase(rates.RateB.ToString(), tableFont));
table.AddCell(new Phrase("Rate C", tableFont));
int lowerLimitC = rates.BandB + 1;
table.AddCell(new Phrase(lowerLimitC.ToString() + "-" + rates.BandC.ToString(), tableFont));
table.AddCell(new Phrase(rates.RateC.ToString(), tableFont));
table.AddCell(new Phrase("Rate D", tableFont));
int lowerLimitD = rates.BandC + 1;
table.AddCell(new Phrase(lowerLimitD.ToString() + "+", tableFont));
table.AddCell(new Phrase(rates.RateD.ToString(), tableFont));
table.AddCell(new Phrase("Rate E", tableFont));
/*int lowerLimitE = rates.BandD + 1;
table.AddCell(new Phrase(lowerLimitE.ToString() + "+", tableFont));
table.AddCell(new Phrase(rates.RateE.ToString(), tableFont)); */
doc.Add(table);
doc.Add(Chunk.NEWLINE);
PdfPTable table2 = new PdfPTable(4);
table2.SpacingBefore = 10;
table2.SpacingAfter = 10;
table2.TotalWidth = 500f;
table2.LockedWidth = true;
table2.AddCell(new Phrase("Description", boldTableFont));
table2.AddCell(new Phrase("Quantity", boldTableFont));
table2.AddCell(new Phrase("Unit Price", boldTableFont));
table2.AddCell(new Phrase("Total", boldTableFont));
table2.AddCell(new Phrase("Water Rate A", tableFont));
table2.AddCell(new Phrase(invoice.QtyRateA.ToString(), tableFont));
table2.AddCell(new Phrase(rates.RateA.ToString(), tableFont));
table2.AddCell(new Phrase(invoice.SubtotalA.ToString(), tableFont));
table2.AddCell(new Phrase("Water Rate B", tableFont));
table2.AddCell(new Phrase(invoice.QtyRateB.ToString(), tableFont));
table2.AddCell(new Phrase(rates.RateB.ToString(), tableFont));
table2.AddCell(new Phrase(invoice.SubtotalB.ToString(), tableFont));
table2.AddCell(new Phrase("Water Rate C", tableFont));
table2.AddCell(new Phrase(invoice.QtyRateC.ToString(), tableFont));
table2.AddCell(new Phrase(rates.RateC.ToString(), tableFont));
table2.AddCell(new Phrase(invoice.SubtotalC.ToString(), tableFont));
table2.AddCell(new Phrase("Water Rate D", tableFont));
table2.AddCell(new Phrase(invoice.QtyRateD.ToString(), tableFont));
table2.AddCell(new Phrase(rates.RateD.ToString(), tableFont));
table2.AddCell(new Phrase(invoice.SubtotalD.ToString(), tableFont));
/* table2.AddCell(new Phrase("Water Rate E", tableFont));
table2.AddCell(new Phrase(invoice.QtyRateE.ToString(), tableFont));
table2.AddCell(new Phrase(rates.RateE.ToString(), tableFont));
table2.AddCell(new Phrase(invoice.SubtotalE.ToString(), tableFont)); */
PdfPCell cell1 = new PdfPCell();
cell1.BorderWidth = 0;
PdfPCell cell2 = new PdfPCell();
cell2.BorderWidth = 0;
PdfPCell cell3 = new PdfPCell(new Phrase("Total:", boldTableFont));
cell3.BorderWidth = 1;
PdfPCell cell4 = new PdfPCell(new Phrase(invoice.Total.ToString(), tableFont));
cell4.BorderWidth = 1;
PdfPCell cell5 = new PdfPCell();
cell5.BorderWidth = 0;
PdfPCell cell6 = new PdfPCell();
cell6.BorderWidth = 0;
PdfPCell cell7 = new PdfPCell(new Phrase("Arrears:", boldTableFont));
cell7.BorderWidth = 1;
PdfPCell cell8 = new PdfPCell(new Phrase(invoice.Arrears.ToString(), tableFont));
cell8.BorderWidth = 1;
PdfPCell cell9 = new PdfPCell();
cell9.BorderWidth = 0;
PdfPCell cell10 = new PdfPCell();
cell10.BorderWidth = 0;
PdfPCell cell11 = new PdfPCell(new Phrase("Amount Due:", boldTableFont));
cell11.BorderWidth = 1;
PdfPCell cell12 = new PdfPCell(new Phrase(invoice.GrandTotal.ToString(), tableFont));
cell12.BorderWidth = 1;
table2.AddCell(cell1);
table2.AddCell(cell2);
table2.AddCell(cell3);
table2.AddCell(cell4);
table2.AddCell(cell5);
table2.AddCell(cell6);
table2.AddCell(cell7);
table2.AddCell(cell8);
table2.AddCell(cell9);
table2.AddCell(cell10);
table2.AddCell(cell11);
table2.AddCell(cell12);
doc.Add(table2);
doc.Add(Chunk.NEWLINE);
doc.Add(Chunk.NEWLINE);
doc.Add(Chunk.NEWLINE);
doc.Add(Chunk.NEWLINE);
doc.Add(new Paragraph("Payment due within 30 days from invoice date.", endingMessageFont));
doc.Add(new Paragraph("Please use Giro Enclosed only.", endingMessageFont));
doc.Add(new Paragraph("Management reserve right to withdraw supply for non payment.", endingMessageFont));
doc.Add(new Paragraph("1 Cubic Metre = 220 Gallons", endingMessageFont));
doc.Close();
return (true);
}
catch
{
//pdf generation failed
return (false);
}
}