// 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();
}