Sample Application: Water Distribution Company
Sample Application: Water Distribution Company
Introduction
This is an exampl application for a fictitious company for water utility. Customers have accounts that help to track their water consumptions, for which they receive quarterly bills that they must then pay.
mon field named ReceiptNumber:
The easiest way to check a relationship between two tables is to match their records. This can be done using the WHERE clause of a SELECT statement. Here are examples:
Practical Learning: Introducing the Application
USE master; GO CREATE DATABASE WaterDistribution; GO USE WaterDistribution; GO
CREATE TABLE WaterMeters ( WaterMeterID INT IDENTITY(1, 1), MeterNumber NVARCHAR(20), Make NVARCHAR(30), Model NVARCHAR(20), MeterSize NVARCHAR(20), DateLastUpdate DATE, CounterValue INT, CONSTRAINT PK_WaterMeters PRIMARY KEY(WaterMeterID) ); GO CREATE TABLE Customers ( CustomerID INT IDENTITY(1, 1), AccountNumber NVARCHAR(20) UNIQUE, WaterMeterID INT, FirstName NVARCHAR(20), LastName NVARCHAR(20), [Address] NVARCHAR(100), City NVARCHAR(40), County NVARCHAR(40), [State] NVARCHAR(2), ZIPCode NVARCHAR(10), CONSTRAINT FK_WaterMeters FOREIGN KEY(WaterMeterID) REFERENCES WaterMeters(WaterMeterID), CONSTRAINT PK_Customers PRIMARY KEY(CustomerID) ); GO CREATE TABLE WaterBills ( WaterBillID INT IDENTITY(1, 1), CustomerID INT, MeterReadingStartDate NVARCHAR(40), MeterReadingEndDate NVARCHAR(40) BillingDays INT, CounterReadingStart DECIMAL, CounterReadingEnd DECIMAL, TotalHCF INT, TotalGallons INT, First15HCF DECIMAL(6, 2), Next10HCF DECIMAL(6, 2), RemainingHCF DECIMAL(6, 2), SewerCharges DECIMAL(6, 2), StormCharges DECIMAL(6, 2), WaterUsageCharges DECIMAL(6, 2), TotalCharges DECIMAL(6, 2), LocalTaxes DECIMAL(6, 2), StateTaxes DECIMAL(6, 2), PaymentDueDate NVARCHAR(40), AmountDue DECIMAL(6, 2), LatePaymentDueDate NVARCHAR(40), LateAmountDue DECIMAL(6, 2), CONSTRAINT FK_Customers FOREIGN KEY(CustomerID) REFERENCES Customers(CustomerID), CONSTRAINT PK_WaterBills PRIMARY KEY(WaterBillID) ); GO CREATE TABLE Payments ( PaymentID INT IDENTITY(1, 1), WaterBillID INT, PaymentDate DATE, PaymentAmount DECIMAL(8, 2), CONSTRAINT FK_WaterBills FOREIGN KEY(WaterBillID) REFERENCES WaterBills(WaterBillID), CONSTRAINT PK_Payments PRIMARY KEY(PaymentID) ); GO
<?xml version="1.0" encoding="utf-8"?>
<!--
For more information on how to configure your ASP.NET application, please visit
https://go.microsoft.com/fwlink/?LinkId=301880
-->
<configuration>
<appSettings>
<add key="webpages:Version" value="3.0.0.0"/>
<add key="webpages:Enabled" value="false"/>
<add key="ClientValidationEnabled" value="true"/>
<add key="UnobtrusiveJavaScriptEnabled" value="true"/>
</appSettings>
<system.web>
<compilation debug="true" targetFramework="4.6.1"/>
<httpRuntime targetFramework="4.6.1"/>
<httpModules>
<add name="ApplicationInsightsWebTracking" type="Microsoft.ApplicationInsights.Web.ApplicationInsightsHttpModule, Microsoft.AI.Web"/>
</httpModules>
</system.web>
<connectionStrings>
<add name="csWaterDistribution"
connectionString="Data Source=(local); Initial Catalog='WaterDistribution'; Integrated Security=True;MultipleActiveResultSets=True"
providerName="System.Data.SqlClient" />
</connectionStrings>
. . . No Change
</configuration><?xml version="1.0" encoding="utf-8"?>
<!--
For more information on how to configure your ASP.NET application, please visit
https://go.microsoft.com/fwlink/?LinkId=301880
-->
<configuration>
<appSettings>
<add key="webpages:Version" value="3.0.0.0"/>
<add key="webpages:Enabled" value="false"/>
<add key="ClientValidationEnabled" value="true"/>
<add key="UnobtrusiveJavaScriptEnabled" value="true"/>
</appSettings>
<system.web>
<compilation debug="true" targetFramework="4.6.1"/>
<httpRuntime targetFramework="4.6.1"/>
<httpModules>
<add name="ApplicationInsightsWebTracking" type="Microsoft.ApplicationInsights.Web.ApplicationInsightsHttpModule, Microsoft.AI.Web"/>
</httpModules>
</system.web>
<connectionStrings>
<add name="csWaterDistribution"
connectionString="Data Source=(LocalDb)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\WaterDistribution.mdf;Initial Catalog=WaterDistribution;Integrated Security=True"
providerName="System.Data.SqlClient" />
</connectionStrings>
. . . No Change
</configuration>
body {
background-color: #2b5b8f;
}
.top-bar { top: 0px;
z-index: 1000;
width: 100%;
position: fixed;
height: 6.85em;
background-color: #203864; }
.containment { margin: auto;
width: 460px; }
.navbar-inverse { background-color: #001132;
border-top: 3px solid #cfdde0;
border-bottom: 3px solid #cfdde0; }
.navbar-fixed-top { top: 6.75em; }
.jumbotron { padding-bottom: 4px;
background-color: #153a62; }
.col-md-3 h2 { color: #abcbd9;
border-bottom: 1px solid #cfdde0; }
.lead { color: #cfdde0; }
.col-md-3 p { color: #d5d4c2; }
.caption { color: aliceblue; }
.copyright { color: #beeeab; }
.push-down { margin-top: 8em; }
.push-down h2 { color: #d5d4c2;
font-weight: 600;
font-size: 26px;
text-align: center;
font-family: Garamond, Georgia, 'Times New Roman', serif; }
.push-down h3 { color: #abcbd9; }
.push-down p { color: #cfdde0; }
.water-nav { text-decoration: none;
color: yellow; }
.water-nav:link { color: orange; }
.water-nav:visited { color: aliceblue; }
.water-nav:active { color: #a8c3ce; }
.water-nav:hover { color: yellow; }
.common-font { font-family: Garamond, Georgia, 'Times New Roman', serif; }
.table-striped > tbody > tr:nth-of-type(even) {
color: navy;
background-color: azure;
}
.table-striped > tbody > tr:nth-of-type(odd) {
color: aliceblue;
background-color: cornflowerblue;
}using System.Web.Optimization;
namespace WaterDistributionCompany1
{
public class BundleConfig
{
// For more information on bundling, visit https://go.microsoft.com/fwlink/?LinkId=301862
public static void RegisterBundles(BundleCollection bundles)
{
bundles.Add(new ScriptBundle("~/bundles/jquery").Include(
"~/Scripts/jquery-{version}.js"));
bundles.Add(new ScriptBundle("~/bundles/jqueryval").Include(
"~/Scripts/jquery.validate*"));
// Use the development version of Modernizr to develop with and learn from. Then, when you're
// ready for production, use the build tool at https://modernizr.com to pick only the tests you need.
bundles.Add(new ScriptBundle("~/bundles/modernizr").Include(
"~/Scripts/modernizr-*"));
bundles.Add(new ScriptBundle("~/bundles/bootstrap").Include(
"~/Scripts/bootstrap.js"));
bundles.Add(new StyleBundle("~/Content/css").Include(
"~/Content/bootstrap.css",
"~/Content/site.css",
"~/Content/WaterDistribution.css"));
}
}
}<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>@ViewBag.Title - Water for a Shining Life</title>
@Styles.Render("~/Content/css")
@Scripts.Render("~/bundles/modernizr")
</head>
<body>
<div class="top-bar">
<div class="containment"><img src="~/Images/wsl1.png" alt="Water for a Shining Life" width="490" height="92" /></div>
</div>
<div class="navbar navbar-inverse navbar-fixed-top">
<div class="container">
<div class="navbar-header">
<button type="button" class="navbar-toggle" data-toggle="collapse" data-target=".navbar-collapse">
<span class="icon-bar"></span>
<span class="icon-bar"></span>
<span class="icon-bar"></span>
</button>
@Html.ActionLink("Water Distribution Company", "Index", "Home", new { area = "" }, new { @class = "navbar-brand" })
</div>
<div class="navbar-collapse collapse">
<ul class="nav navbar-nav">
<li>@Html.ActionLink("Emergency Services", "Index", "Home")</li>
<li>@Html.ActionLink("Cummunity", "Index", "Home")</li>
<li>@Html.ActionLink("Environment", "Index", "Home")</li>
<li>@Html.ActionLink("Resources", "Index", "Home")</li>
<li>@Html.ActionLink("Projects", "Index", "Home")</li>
<li>@Html.ActionLink("Customer Service", "Index", "Home")</li>
<li>@Html.ActionLink("Employment", "Index", "Home")</li>
<li>@Html.ActionLink("Questions?", "Index", "Home")</li>
<li>@Html.ActionLink("About", "About", "Home")</li>
<li>@Html.ActionLink("Contact", "Contact", "Home")</li>
</ul>
</div>
</div>
</div>
<div class="container body-content">
@RenderBody()
<hr />
<footer>
<p class="copyright text-center common-font">© @DateTime.Now.Year - Water for a Shining Life</p>
</footer>
</div>
@Scripts.Render("~/bundles/jquery")
@Scripts.Render("~/bundles/bootstrap")
@RenderSection("scripts", required: false)
</body>
</html><div class="jumbotron">
<h2>.</h2>
<p class="lead">
Our water utility company provides energy, greatness, and warmth
for a everyday life, a shining life. We provide solutions to families, businesses,
and the community.
</p>
<p class="lead">
This is the employees portal section of the company. From here,
employees can register a new water meter, manage a customer account, or
create a water bill.
</p>
</div>
<div class="row">
<div class="col-md-3">
<h2>Water Meters</h2>
<p>
Our company uses the most accurate, sophisticated, and environment-friendly
water meters on the market.
</p>
<p>@Html.ActionLink("Water Meters", "Index", "WaterMeters", null, new { @class = "btn btn-primary" })</p>
</div>
<div class="col-md-3">
<h2>Customers</h2>
<p>
We supply water to individuals, families, small
businesses, as well as enterprises or government agencies.
</p>
<p>@Html.ActionLink("Customers", "Index", "Customers", null, new { @class = "btn btn-primary" })</p>
</div>
<div class="col-md-3">
<h2>Water Bills</h2>
<p>
Our water rates are very competitive nationwide. We use precise,
effective, and strict algorithms when calculating our bills.
</p>
<p>@Html.ActionLink("Bills/Invoices", "Index", "WaterBills", null, new { @class = "btn btn-primary" })</p>
</div>
<div class="col-md-3">
<h2>Payments</h2>
<p>
Our payment system is the simplest, the fairest, and the fastest. Our custiomer's service
is highly rated.
</p>
<p>@Html.ActionLink("Bills Payments", "Index", "Payments", null, new { @class = "btn btn-primary" })</p>
</div>
</div> 
Water Meters
A water meter is an electro-mechanical device that tracks the consumption of water. Every customer has one.
Practical Learning: Creating Water Meters
using System;
using System.Collections.Generic;
using System.Linq;
using System.ComponentModel.DataAnnotations;
namespace WaterDistributionCompany1.Models
{
public class WaterMeter
{
[Display(Name = "Water Meter ID")]
public int WaterMeterID { get; set; }
[Display(Name = "Meter #")]
public string MeterNumber { get; set; }
public string Make { get; set; }
public string Model { get; set; }
[Display(Name = "Meter Size")]
public string MeterSize { get; set; }
[DataType(DataType.Date)]
[Display(Name = "Date Last Update")]
public DateTime DateLastUpdate { get; set; }
[Display(Name = "Counter Value")]
public int CounterValue { get; set; }
public string Description
{
get
{
return Make + " " + Model +
" (Model #: " + MeterNumber + ", " +
MeterSize + ")";
}
}
}
}using System; using System.Data; using System.Web.Mvc; using System.Data.SqlClient; using System.Collections.Generic; using WaterDistributionCompany1.Models; namespace WaterDistributionCompany1.Controllers { public class WaterMetersController : Controller { List<WaterMeter> waterMeters = new List<WaterMeter>(); // GET: WaterMeters public ActionResult Index() { using (SqlConnection scWaterDistribution = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["csWaterDistribution"].ConnectionString)) { SqlCommand cmdWaterMeters = new SqlCommand("SELECT WaterMeterID, MeterNumber, Make, Model, MeterSize, DateLastUpdate, CounterValue " + "FROM WaterMeters;", scWaterDistribution); scWaterDistribution.Open(); cmdWaterMeters.ExecuteNonQuery(); SqlDataAdapter sdaWaterMeters = new SqlDataAdapter(cmdWaterMeters); DataSet dsWaterMeters = new DataSet("water-meters"); sdaWaterMeters.Fill(dsWaterMeters); WaterMeter meter = null; for (int i = 0; i < dsWaterMeters.Tables[0].Rows.Count; i++) { meter = new WaterMeter(); meter.WaterMeterID = int.Parse(dsWaterMeters.Tables[0].Rows[i][0].ToString()); meter.MeterNumber = dsWaterMeters.Tables[0].Rows[i][1].ToString(); meter.Make = dsWaterMeters.Tables[0].Rows[i][2].ToString(); meter.Model = dsWaterMeters.Tables[0].Rows[i][3].ToString(); meter.MeterSize = dsWaterMeters.Tables[0].Rows[i][4].ToString(); meter.DateLastUpdate = DateTime.Parse(dsWaterMeters.Tables[0].Rows[i][5].ToString()); meter.CounterValue = int.Parse(dsWaterMeters.Tables[0].Rows[i][6].ToString()); waterMeters.Add(meter); } } return View(waterMeters); } // GET: WaterMeters/Details/5 public ActionResult Details(int id) { WaterMeter meter = null; using (SqlConnection scWaterDistribution = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["csWaterDistribution"].ConnectionString)) { SqlCommand cmdWaterMeters = new SqlCommand("SELECT WaterMeterID, MeterNumber, Make, Model, MeterSize, DateLastUpdate, CounterValue " + "FROM WaterMeters " + "WHERE WaterMeterID = " + id + ";", scWaterDistribution); scWaterDistribution.Open(); cmdWaterMeters.ExecuteNonQuery(); SqlDataAdapter sdaWaterMeters = new SqlDataAdapter(cmdWaterMeters); DataSet dsWaterMeters = new DataSet("water-meters"); sdaWaterMeters.Fill(dsWaterMeters); if (dsWaterMeters.Tables[0].Rows.Count > 0) { meter = new WaterMeter(); meter.WaterMeterID = int.Parse(dsWaterMeters.Tables[0].Rows[0][0].ToString()); meter.MeterNumber = dsWaterMeters.Tables[0].Rows[0][1].ToString(); meter.Make = dsWaterMeters.Tables[0].Rows[0][2].ToString(); meter.Model = dsWaterMeters.Tables[0].Rows[0][3].ToString(); meter.MeterSize = dsWaterMeters.Tables[0].Rows[0][4].ToString(); meter.DateLastUpdate = DateTime.Parse(dsWaterMeters.Tables[0].Rows[0][5].ToString()); meter.CounterValue = int.Parse(dsWaterMeters.Tables[0].Rows[0][6].ToString()); } } return View(meter); } // GET: WaterMeters/Create public ActionResult Create() { return View(); } // POST: WaterMeters/Create [HttpPost] public ActionResult Create(FormCollection collection) { try { // TODO: Add insert logic here using (SqlConnection scWaterDistribution = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["csWaterDistribution"].ConnectionString)) { SqlCommand cmdWaterMeters = new SqlCommand("INSERT INTO WaterMeters(MeterNumber, Make, " + " Model, MeterSize, " + " DateLastUpdate, CounterValue) " + "VALUES(N'" + collection["MeterNumber"] + "', N'" + collection["Make"] + "', N'" + collection["Model"] + "', N'" + collection["MeterSize"] + "', N'" + collection["DateLastUpdate"] + "', " + collection["CounterValue"] + ");", scWaterDistribution); scWaterDistribution.Open(); cmdWaterMeters.ExecuteNonQuery(); } return RedirectToAction("Index"); } catch { return View("Create"); } } // GET: WaterMeters/Edit/5 public ActionResult Edit(int id) { WaterMeter meter = null; using (SqlConnection scWaterDistribution = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["csWaterDistribution"].ConnectionString)) { SqlCommand cmdWaterMeters = new SqlCommand("SELECT WaterMeterID, MeterNumber, Make, Model, MeterSize, DateLastUpdate, CounterValue " + "FROM WaterMeters " + "WHERE WaterMeterID = " + id + ";", scWaterDistribution); scWaterDistribution.Open(); cmdWaterMeters.ExecuteNonQuery(); SqlDataAdapter sdaWaterMeters = new SqlDataAdapter(cmdWaterMeters); DataSet dsWaterMeters = new DataSet("water-meters"); sdaWaterMeters.Fill(dsWaterMeters); if (dsWaterMeters.Tables[0].Rows.Count > 0) { meter = new WaterMeter { WaterMeterID = int.Parse(dsWaterMeters.Tables[0].Rows[0][0].ToString()), MeterNumber = dsWaterMeters.Tables[0].Rows[0][1].ToString(), Make = dsWaterMeters.Tables[0].Rows[0][2].ToString(), Model = dsWaterMeters.Tables[0].Rows[0][3].ToString(), MeterSize = dsWaterMeters.Tables[0].Rows[0][4].ToString(), DateLastUpdate = DateTime.Parse(dsWaterMeters.Tables[0].Rows[0][5].ToString()), CounterValue = int.Parse(dsWaterMeters.Tables[0].Rows[0][6].ToString()) }; } } ViewBag.DateLastUpdate = meter.DateLastUpdate.ToShortDateString(); return View(meter); } // POST: WaterMeters/Edit/5 [HttpPost] public ActionResult Edit(int id, FormCollection collection) { try { // TODO: Add update logic here using (SqlConnection scWaterDistribution = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["csWaterDistribution"].ConnectionString)) { SqlCommand cmdWaterMeters = new SqlCommand("UPDATE WaterMeters " + "SET MeterNumber = N'" + collection["MeterNumber"] + "', " + " Make = N'" + collection["Make"] + "', " + " Model = N'" + collection["Model"] + "', " + " MeterSize = N'" + collection["MeterSize"] + "', " + " DateLastUpdate = N'" + collection["DateLastUpdate"] + "', " + " CounterValue = " + collection["CounterValue"] + " " + "WHERE WaterMeterID = " + id + ";", scWaterDistribution); scWaterDistribution.Open(); cmdWaterMeters.ExecuteNonQuery(); } return RedirectToAction("Index"); } catch { return View(); } } // GET: WaterMeters/Delete/5 public ActionResult Delete(int id) { WaterMeter meter = null; using (SqlConnection scWaterDistribution = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["csWaterDistribution"].ConnectionString)) { SqlCommand cmdWaterMeters = new SqlCommand("SELECT WaterMeterID, MeterNumber, Make, Model, MeterSize, DateLastUpdate, CounterValue " + "FROM WaterMeters " + "WHERE WaterMeterID = " + id + ";", scWaterDistribution); scWaterDistribution.Open(); cmdWaterMeters.ExecuteNonQuery(); SqlDataAdapter sdaWaterMeters = new SqlDataAdapter(cmdWaterMeters); DataSet dsWaterMeters = new DataSet("water-meters"); sdaWaterMeters.Fill(dsWaterMeters); if (dsWaterMeters.Tables[0].Rows.Count > 0) { meter = new WaterMeter { WaterMeterID = int.Parse(dsWaterMeters.Tables[0].Rows[0][0].ToString()), MeterNumber = dsWaterMeters.Tables[0].Rows[0][1].ToString(), Make = dsWaterMeters.Tables[0].Rows[0][2].ToString(), Model = dsWaterMeters.Tables[0].Rows[0][3].ToString(), MeterSize = dsWaterMeters.Tables[0].Rows[0][4].ToString(), DateLastUpdate = DateTime.Parse(dsWaterMeters.Tables[0].Rows[0][5].ToString()), CounterValue = int.Parse(dsWaterMeters.Tables[0].Rows[0][6].ToString()) }; } } return View(meter); } // POST: WaterMeters/Delete/5 [HttpPost] public ActionResult Delete(int id, FormCollection collection) { try { // TODO: Add delete logic here using (SqlConnection scWaterDistribution = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["csWaterDistribution"].ConnectionString)) { SqlCommand cmdWaterMeters = new SqlCommand("DELETE WaterMeters " + "WHERE WaterMeterID = " + id + ";", scWaterDistribution); scWaterDistribution.Open(); cmdWaterMeters.ExecuteNonQuery(); } return RedirectToAction("Index"); } catch { return View(); } } } }
@model IEnumerable<WaterDistributionCompany1.Models.WaterMeter>
@{
ViewBag.Title = "Water Meters";
}
<div class="push-down">
<h2 class="common-font bold text-center">Water Meters</h2>
</div>
<hr />
<table class="table table-striped common-font">
<tr>
<th class="bold">Water Meter ID</th>
<th class="bold">Meter #</th>
<th class="bold">Make</th>
<th class="bold">Model</th>
<th class="bold">Meter Size</th>
<th class="bold">Date Last Update</th>
<th class="bold">Counter Value</th>
<th>@Html.ActionLink("New Water Meter", "Create", null, htmlAttributes: new { @class = "water-nav" })</th>
</tr>
@foreach (var item in Model)
{
<tr>
<td class="text-center">@Html.DisplayFor(modelItem => item.WaterMeterID)</td>
<td>@Html.DisplayFor(modelItem => item.MeterNumber)</td>
<td>@Html.DisplayFor(modelItem => item.Make)</td>
<td>@Html.DisplayFor(modelItem => item.Model)</td>
<td>@Html.DisplayFor(modelItem => item.MeterSize)</td>
<td>@Html.DisplayFor(modelItem => item.DateLastUpdate)</td>
<td>@Html.DisplayFor(modelItem => item.CounterValue)</td>
<td>
@Html.ActionLink("Update", "Edit", new { id = item.WaterMeterID }) |
@Html.ActionLink("Review", "Details", new { id = item.WaterMeterID }) |
@Html.ActionLink("Remove", "Delete", new { id = item.WaterMeterID })
</td>
</tr>
}
</table>@model WaterDistributionCompany1.Models.WaterMeter
@{
ViewBag.Title = "Water Meter Details";
}
<div class="push-down">
<h2>Water Meter Details</h2>
</div>
<hr />
<div class="containment">
<dl class="dl-horizontal common-font caption">
<dt>@Html.DisplayNameFor(model => model.WaterMeterID)</dt>
<dd>@Html.DisplayFor(model => model.WaterMeterID)</dd>
<dt>Water Meter #</dt>
<dd>@Html.DisplayFor(model => model.MeterNumber)</dd>
<dt>@Html.DisplayNameFor(model => model.Make)</dt>
<dd>@Html.DisplayFor(model => model.Make)</dd>
<dt>@Html.DisplayNameFor(model => model.Model)</dt>
<dd>@Html.DisplayFor(model => model.Model)</dd>
<dt>@Html.DisplayNameFor(model => model.MeterSize)</dt>
<dd>@Html.DisplayFor(model => model.MeterSize)</dd>
<dt>@Html.DisplayNameFor(model => model.DateLastUpdate)</dt>
<dd>@Html.DisplayFor(model => model.DateLastUpdate)</dd>
<dt>@Html.DisplayNameFor(model => model.CounterValue)</dt>
<dd>@Html.DisplayFor(model => model.CounterValue)</dd>
</dl>
</div>
<p class="text-center">
@Html.ActionLink("Edit/Update Water Meter Information", "Edit",
new { id = Model.WaterMeterID, @class = "water-nav" }) |
@Html.ActionLink("Water Meters", "Index",
null, new { @class = "water-nav" })
</p>@{
ViewBag.Title = "New Water Meter";
}
<div class="push-down">
<h2>New Water Meter</h2>
</div>
<hr />
@using (Html.BeginForm())
{
<div class="form-horizontal">
<div class="form-group">
<label for="mtrNbr" class="control-label col-md-4 caption">Meter #:</label>
<div class="col-md-8">
@Html.TextBox("MeterNumber", null, htmlAttributes: new { @class = "form-control", id = "mtrNbr" })
</div>
</div>
<div class="form-group">
<label for="make" class="control-label col-md-4 caption">Make:</label>
<div class="col-md-8">
@Html.TextBox("Make", null, htmlAttributes: new { @class = "form-control", id = "make" })
</div>
</div>
<div class="form-group">
<label for="model" class="control-label col-md-4 caption">Model:</label>
<div class="col-md-8">
@Html.TextBox("Model", null, htmlAttributes: new { @class = "form-control", id = "model" })
</div>
</div>
<div class="form-group">
<label for="mtrSize" class="control-label col-md-4 caption">Meter Size:</label>
<div class="col-md-8">
@Html.TextBox("MeterSize", null, htmlAttributes: new { @class = "form-control", id = "mtrSize" })
</div>
</div>
<div class="form-group">
<label for="dlu" class="control-label col-md-4 caption">Date Last Update:</label>
<div class="col-md-8">
@Html.TextBox("DateLastUpdate",
null, htmlAttributes: new { @class = "form-control", type = "date", id = "dlu" })
</div>
</div>
<div class="form-group">
<label for="cntVal" class="control-label col-md-4 caption">Counter Value:</label>
<div class="col-md-8">
@Html.TextBox("CounterValue", null, htmlAttributes: new { @class = "form-control", id = "cntVal" })
</div>
</div>
<div class="form-group">
<label class="control-label col-md-5">
@Html.ActionLink("Water Meters", "Index", null,
htmlAttributes: new { @class = "water-nav" })
</label>
<div class="col-md-7">
<input type="submit" value="Create Water Meter" class="btn btn-primary" />
</div>
</div>
</div>
}@model WaterDistributionCompany1.Models.WaterMeter
@{
ViewBag.Title = "Edit/Update Water Meter";
string strDateLastUpdate = ViewBag.DateLastUpdate;
}
<div class="push-down">
<h2>Edit/Update Water Meter</h2>
</div>
<hr />
@using (Html.BeginForm())
{
<div class="form-horizontal common-font containment">
@Html.HiddenFor(model => model.WaterMeterID)
<div class="form-group">
@Html.LabelFor(model => model.MeterNumber, htmlAttributes: new { @class = "control-label col-md-4 caption" })
<div class="col-md-8">
@Html.EditorFor(model => model.MeterNumber, new { htmlAttributes = new { @class = "form-control" } })
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.Make, htmlAttributes: new { @class = "control-label col-md-4 caption" })
<div class="col-md-8">
@Html.EditorFor(model => model.Make, new { htmlAttributes = new { @class = "form-control" } })
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.Model, htmlAttributes: new { @class = "control-label col-md-4 caption" })
<div class="col-md-8">
@Html.EditorFor(model => model.Model, new { htmlAttributes = new { @class = "form-control" } })
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.MeterSize, htmlAttributes: new { @class = "control-label col-md-4 caption" })
<div class="col-md-8">
@Html.EditorFor(model => model.MeterSize, new { htmlAttributes = new { @class = "form-control" } })
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.DateLastUpdate, htmlAttributes: new { @class = "control-label col-md-4 caption" })
<div class="col-md-3"><label class="control-label caption">@strDateLastUpdate</label></div>
<div class="col-md-4">
@Html.EditorFor(model => model.DateLastUpdate, "date", new { htmlAttributes = new { @class = "form-control" } })
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.CounterValue, htmlAttributes: new { @class = "control-label col-md-4 caption" })
<div class="col-md-8">
@Html.EditorFor(model => model.CounterValue, new { htmlAttributes = new { @class = "form-control" } })
</div>
</div>
<div class="form-group">
<label class="control-label col-md-4 caption">
@Html.ActionLink("Water Meters", "Index", null, new { @class = "water-nav" })
</label>
<div class="col-md-8">
<input type="submit" value="Update Water Meter Information" class="btn btn-primary" />
</div>
</div>
</div>
}@model WaterDistributionCompany1.Models.WaterMeter
@{
ViewBag.Title = "Delete Water Meter";
}
<div class="push-down">
<h2>Delete Water Meter</h2>
</div>
<hr />
<div class="containment">
<dl class="dl-horizontal common-font caption">
<dt>@Html.DisplayNameFor(model => model.WaterMeterID)</dt>
<dd>@Html.DisplayFor(model => model.WaterMeterID)</dd>
<dt>@Html.DisplayNameFor(model => model.MeterNumber)</dt>
<dd>@Html.DisplayFor(model => model.MeterNumber)</dd>
<dt>@Html.DisplayNameFor(model => model.Make)</dt>
<dd>@Html.DisplayFor(model => model.Make)</dd>
<dt>@Html.DisplayNameFor(model => model.Model)</dt>
<dd>@Html.DisplayFor(model => model.Model)</dd>
<dt>@Html.DisplayNameFor(model => model.MeterSize)</dt>
<dd>@Html.DisplayFor(model => model.MeterSize)</dd>
<dt>@Html.DisplayNameFor(model => model.DateLastUpdate)</dt>
<dd>@Html.DisplayFor(model => model.DateLastUpdate)</dd>
<dt>@Html.DisplayNameFor(model => model.CounterValue)</dt>
<dd>@Html.DisplayFor(model => model.CounterValue)</dd>
</dl>
<h3 class="common-font caption">Are you sure you want to remove this water from the database?</h3>
@using (Html.BeginForm())
{
<div class="form-actions no-color">
<input type="submit" value="Delete this Water Meter" class="btn btn-primary" /> |
@Html.ActionLink("Water Meters", "Index", null, new { @class = "water-nav" })
</div>
}
</div>
| Meter # | Make | Model | Meter Size | Date Last Update | Counter Value |
| 392-44-572 | Constance Technologies | TG-4822 | 5/8 Inches | 07/02/2018 | 109992 |
| 938-75-869 | Standard Trend | 266G | 1 1/2 Inches | 07/05/2018 | 137926 |
| 799-28-461 | Constance Technologies | BD-7000 | 3/4 Inches | 7/5/2018 | 6268 |
| 207-94-835 | Constance Technologies | TG-6220 | 5/8 Inches | 7/2/2018 | 96 |
| 592-84-957 | Standard Trend | 428T | 3/4 Inches | 9/3/2018 | 49 |
Customers
Customers are those who consume water. In our database, every customer has an account. Beside the general information that identifies a customer, every account must specify the water meter that a customer is using.
Practical Learning: Creating Customers Accounts
using System.ComponentModel.DataAnnotations;
namespace WaterDistributionCompany1.Models
{
public class Customer
{
[Display(Name = "Customer ID")]
public int CustomerID { get; set; }
[Display(Name = "Account #")]
public string AccountNumber { get; set; }
[Display(Name = "Water Meter")]
public int WaterMeterID { get; set; }
[Display(Name = "First Name")]
public string FirstName { get; set; }
[Display(Name = "Last Name")]
public string LastName { get; set; }
public string Address { get; set; }
public string City { get; set; }
public string County { get; set; }
public string State { get; set; }
[Display(Name = "ZIP Code")]
public string ZIPCode { get; set; }
}
public class Client : Customer
{
public string WaterMeter { get; set; }
}
}using System; using System.Data; using System.Web.Mvc; using System.Data.SqlClient; using System.Collections.Generic; using WaterDistributionCompany1.Models; namespace WaterDistributionCompany1.Controllers { public class CustomersController : Controller { List<Client> clients = new List<Client>(); List<Customer> customers = new List<Customer>(); // GET: Customers public ActionResult Index() { using (SqlConnection scWaterDistribution = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["csWaterDistribution"].ConnectionString)) { SqlCommand cmdCustomers = new SqlCommand("SELECT CustomerID, AccountNumber, WaterMeterID, " + " FirstName, LastName, Address, City, " + " County, State, ZIPCode " + "FROM Customers; ", scWaterDistribution); scWaterDistribution.Open(); cmdCustomers.ExecuteNonQuery(); SqlDataAdapter sdaCustomers = new SqlDataAdapter(cmdCustomers); DataSet dsCustomers = new DataSet("customers"); sdaCustomers.Fill(dsCustomers); DataTable dtCustomers = dsCustomers.Tables[0]; for (int i = 0; i < dtCustomers.Rows.Count; i++) { DataRow drCustomer = dtCustomers.Rows[i]; customers.Add(new Customer() { CustomerID = int.Parse(drCustomer[0].ToString()), AccountNumber = drCustomer[1].ToString(), WaterMeterID = int.Parse(drCustomer[2].ToString()), FirstName = drCustomer[3].ToString(), LastName = drCustomer[4].ToString(), Address = drCustomer[5].ToString(), City = drCustomer[6].ToString(), County = drCustomer[7].ToString(), State = drCustomer[8].ToString(), ZIPCode = drCustomer[9].ToString() }); } } return View(customers); } // GET: Customers/Details/5 public ActionResult Details(int id) { Customer client = null; using (SqlConnection scWaterDistribution = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["csWaterDistribution"].ConnectionString)) { SqlCommand cmdCustomers = new SqlCommand("SELECT CustomerID, AccountNumber, WaterMeterID, " + " FirstName, LastName, Address, City, " + " County, State, ZIPCode " + "FROM Customers " + "WHERE CustomerID = " + id + ";", scWaterDistribution); scWaterDistribution.Open(); cmdCustomers.ExecuteNonQuery(); SqlDataAdapter sdaCustomers = new SqlDataAdapter(cmdCustomers); DataSet dsCustomers = new DataSet("customers"); sdaCustomers.Fill(dsCustomers); DataTable dtCustomers = dsCustomers.Tables[0]; if (dtCustomers.Rows.Count > 0) { DataRow drCustomer =dtCustomers.Rows[0]; client = new Customer { CustomerID = int.Parse(drCustomer[0].ToString()), AccountNumber = drCustomer[1].ToString(), WaterMeterID = int.Parse(drCustomer[2].ToString()), FirstName = drCustomer[3].ToString(), LastName = drCustomer[4].ToString(), Address = drCustomer[5].ToString(), City = drCustomer[6].ToString(), County = drCustomer[7].ToString(), State = drCustomer[8].ToString(), ZIPCode = drCustomer[9].ToString() }; } } return View(client); } // GET: Customers/Create public ActionResult Create() { List<WaterMeter> waterMeters = new List<WaterMeter>(); using (SqlConnection scWaterDistribution = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["csWaterDistribution"].ConnectionString)) { SqlCommand cmdWaterMeters = new SqlCommand("SELECT WaterMeterID, MeterNumber, Make, Model, MeterSize, DateLastUpdate, CounterValue " + "FROM WaterMeters;", scWaterDistribution); scWaterDistribution.Open(); cmdWaterMeters.ExecuteNonQuery(); SqlDataAdapter sdaWaterMeters = new SqlDataAdapter(cmdWaterMeters); DataSet dsWaterMeters = new DataSet("water-meters"); sdaWaterMeters.Fill(dsWaterMeters); WaterMeter meter = null; for (int i = 0; i < dsWaterMeters.Tables[0].Rows.Count; i++) { meter = new WaterMeter { WaterMeterID = int.Parse(dsWaterMeters.Tables[0].Rows[i][0].ToString()), MeterNumber = dsWaterMeters.Tables[0].Rows[i][1].ToString(), Make = dsWaterMeters.Tables[0].Rows[i][2].ToString(), Model = dsWaterMeters.Tables[0].Rows[i][3].ToString(), MeterSize = dsWaterMeters.Tables[0].Rows[i][4].ToString(), DateLastUpdate = DateTime.Parse(dsWaterMeters.Tables[0].Rows[i][5].ToString()), CounterValue = int.Parse(dsWaterMeters.Tables[0].Rows[i][6].ToString()) }; waterMeters.Add(meter); } } ViewBag.WaterMeterID = new SelectList(waterMeters, "WaterMeterID ", "Description"); return View(); } // POST: Customers/Create [HttpPost] public ActionResult Create(FormCollection collection) { try { // TODO: Add insert logic here using (SqlConnection scWaterDistribution = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["csWaterDistribution"].ConnectionString)) { SqlCommand cmdCustomers = new SqlCommand("INSERT INTO Customers(AccountNumber, WaterMeterID, " + " FirstName, LastName, Address, City, " + " County, State, ZIPCode) " + "VALUES(N'" + collection["AccountNumber"] + "', " + collection["WaterMeterID"] + ", N'" + collection["FirstName"] + "', N'" + collection["LastName"] + "', N'" + collection["Address"] + "', N'" + collection["City"] + "', N'" + collection["County"] + "', N'" + collection["State"] + "', N'" + collection["ZIPCode"] + "');", scWaterDistribution); scWaterDistribution.Open(); cmdCustomers.ExecuteNonQuery(); } return RedirectToAction("Index"); } catch { return View(); } } // GET: Customers/Edit/5 public ActionResult Edit(int id) { Customer client = null; WaterMeter meter = null; List<WaterMeter> waterMeters = new List<WaterMeter>(); using (SqlConnection scWaterDistribution = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["csWaterDistribution"].ConnectionString)) { SqlCommand cmdCustomers = new SqlCommand("SELECT CustomerID, AccountNumber, WaterMeterID, " + " FirstName, LastName, Address, City, " + " County, State, ZIPCode " + "FROM Customers " + "WHERE CustomerID = " + id + ";", scWaterDistribution); scWaterDistribution.Open(); cmdCustomers.ExecuteNonQuery(); SqlDataAdapter sdaCustomers = new SqlDataAdapter(cmdCustomers); DataSet dsCustomers = new DataSet("customers"); sdaCustomers.Fill(dsCustomers); if (dsCustomers.Tables[0].Rows.Count > 0) { DataRow drCustomer = dsCustomers.Tables[0].Rows[0]; client = new Customer { CustomerID = int.Parse(drCustomer[0].ToString()), AccountNumber = drCustomer[1].ToString(), WaterMeterID = int.Parse(drCustomer[2].ToString()), FirstName = drCustomer[3].ToString(), LastName = drCustomer[4].ToString(), Address = drCustomer[5].ToString(), City = drCustomer[6].ToString(), County = drCustomer[7].ToString(), State = drCustomer[8].ToString(), ZIPCode = drCustomer[9].ToString() }; } } using (SqlConnection scWaterDistribution = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["csWaterDistribution"].ConnectionString)) { SqlCommand cmdWaterMeters = new SqlCommand("SELECT WaterMeterID, MeterNumber, Make, Model, " + " MeterSize, DateLastUpdate, CounterValue " + "FROM WaterMeters;", scWaterDistribution); scWaterDistribution.Open(); cmdWaterMeters.ExecuteNonQuery(); SqlDataAdapter sdaWaterMeters = new SqlDataAdapter(cmdWaterMeters); DataSet dsWaterMeters = new DataSet("water-meters"); sdaWaterMeters.Fill(dsWaterMeters); for (int i = 0; i < dsWaterMeters.Tables[0].Rows.Count; i++) { meter = new WaterMeter { WaterMeterID = int.Parse(dsWaterMeters.Tables[0].Rows[i][0].ToString()), MeterNumber = dsWaterMeters.Tables[0].Rows[i][1].ToString(), Make = dsWaterMeters.Tables[0].Rows[i][2].ToString(), Model = dsWaterMeters.Tables[0].Rows[i][3].ToString(), MeterSize = dsWaterMeters.Tables[0].Rows[i][4].ToString(), DateLastUpdate = DateTime.Parse(dsWaterMeters.Tables[0].Rows[i][5].ToString()), CounterValue = int.Parse(dsWaterMeters.Tables[0].Rows[i][6].ToString()) }; waterMeters.Add(meter); } } ViewBag.WaterMeterID = new SelectList(waterMeters, "WaterMeterID ", "Description", client.WaterMeterID); return View(client); } // POST: Customers/Edit/5 [HttpPost] public ActionResult Edit(int id, FormCollection collection) { try { // TODO: Add update logic here using (SqlConnection scWaterDistribution = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["csWaterDistribution"].ConnectionString)) { SqlCommand cmdCustomers = new SqlCommand("UPDATE Customers " + "SET AccountNumber = N'" + collection["AccountNumber"] + "'," + " WaterMeterID = " + collection["WaterMeterID"] + "," + " FirstName = N'" + collection["FirstName"] + "'," + " LastName = N'" + collection["LastName"] + "'," + " Address = N'" + collection["Address"] + "'," + " City = N'" + collection["City"] + "'," + " County = N'" + collection["County"] + "'," + " State = N'" + collection["State"] + "'," + " ZIPCode = N'" + collection["ZIPCode"] + "' " + "WHERE CustomerID = " + id + ";", scWaterDistribution); scWaterDistribution.Open(); cmdCustomers.ExecuteNonQuery(); } return RedirectToAction("Index"); } catch { return View(); } } // GET: Customers/Delete/5 public ActionResult Delete(int id) { Customer client = null; using (SqlConnection scWaterDistribution = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["csWaterDistribution"].ConnectionString)) { SqlCommand cmdCustomers = new SqlCommand("SELECT CustomerID, AccountNumber, WaterMeterID, " + " FirstName, LastName, Address, City, " + " County, State, ZIPCode " + "FROM Customers " + "WHERE CustomerID = " + id + ";", scWaterDistribution); scWaterDistribution.Open(); cmdCustomers.ExecuteNonQuery(); SqlDataAdapter sdaCustomers = new SqlDataAdapter(cmdCustomers); DataSet dsCustomers = new DataSet("customers"); sdaCustomers.Fill(dsCustomers); if (dsCustomers.Tables[0].Rows.Count > 0) { DataRow drCustomer = dsCustomers.Tables[0].Rows[0]; client = new Customer { CustomerID = int.Parse(drCustomer[0].ToString()), AccountNumber = drCustomer[1].ToString(), WaterMeterID = int.Parse(drCustomer[2].ToString()), FirstName = drCustomer[3].ToString(), LastName = drCustomer[4].ToString(), Address = drCustomer[5].ToString(), City = drCustomer[6].ToString(), County = drCustomer[7].ToString(), State = drCustomer[8].ToString(), ZIPCode = drCustomer[9].ToString() }; } } return View(client); } // POST: Customers/Delete/5 [HttpPost] public ActionResult Delete(int id, FormCollection collection) { try { // TODO: Add delete logic here using (SqlConnection scWaterDistribution = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["csWaterDistribution"].ConnectionString)) { SqlCommand cmdCustomers = new SqlCommand("DELETE FROM Customers " + "WHERE CustomerID = " + id + ";", scWaterDistribution); scWaterDistribution.Open(); cmdCustomers.ExecuteNonQuery(); } return RedirectToAction("Index"); } catch { return View(); } } } }
@model WaterDistributionCompany1.Models.Customer
@{
ViewBag.Title = "Customer Details";
}
<div class="push-down">
<h2>Customer Details</h2>
</div>
<hr />
<div class="containment">
<dl class="dl-horizontal common-font caption">
<dt>@Html.DisplayNameFor(model => model.CustomerID)</dt>
<dd>@Html.DisplayFor(model => model.CustomerID)</dd>
<dt>@Html.DisplayNameFor(model => model.AccountNumber)</dt>
<dd>@Html.DisplayFor(model => model.AccountNumber)</dd>
<dt>@Html.DisplayNameFor(model => model.WaterMeterID)</dt>
<dd>@Html.DisplayFor(model => model.WaterMeterID)</dd>
<dt>@Html.DisplayNameFor(model => model.FirstName)</dt>
<dd>@Html.DisplayFor(model => model.FirstName)</dd>
<dt>@Html.DisplayNameFor(model => model.LastName)</dt>
<dd>@Html.DisplayFor(model => model.LastName)</dd>
<dt>@Html.DisplayNameFor(model => model.Address)</dt>
<dd>@Html.DisplayFor(model => model.Address)</dd>
<dt>@Html.DisplayNameFor(model => model.City)</dt>
<dd>@Html.DisplayFor(model => model.City)</dd>
<dt>@Html.DisplayNameFor(model => model.County)</dt>
<dd>@Html.DisplayFor(model => model.County)</dd>
<dt>@Html.DisplayNameFor(model => model.State)</dt>
<dd>@Html.DisplayFor(model => model.State)</dd>
<dt>@Html.DisplayNameFor(model => model.ZIPCode)</dt>
<dd>@Html.DisplayFor(model => model.ZIPCode)</dd>
</dl>
</div>
<p class="text-center">
@Html.ActionLink("Edit/Update Customer Information", "Edit",
new { id = Model.CustomerID, @class = "water-nav" },
new { @class = "water-nav" }) |
@Html.ActionLink("Customers Records", "Index",
null, new { @class = "water-nav" })
</p>
@{
ViewBag.Title = "New Customer Account";
}
<div class="push-down">
<h2>New Customer Account</h2>
</div>
<hr />
@using (Html.BeginForm())
{
<div class="form-horizontal">
<div class="form-group">
<label for="mtrNbr" class="control-label col-md-4 caption">Account #:</label>
<div class="col-md-8">
@Html.TextBox("AccountNumber", null, htmlAttributes: new { @class = "form-control", id = "mtrNbr" })
</div>
</div>
<div class="form-group">
<label for="mtr" class="control-label col-md-4 caption">Water Meter:</label>
<div class="col-md-8">
@Html.DropDownList("WaterMeterID", null, htmlAttributes: new { @class = "form-control", id = "mtr" })
</div>
</div>
<div class="form-group">
<label for="fName" class="control-label col-md-4 caption">First Name:</label>
<div class="col-md-8">
@Html.TextBox("FirstName", null, htmlAttributes: new { @class = "form-control", id = "fName" })
</div>
</div>
<div class="form-group">
<label for="lName" class="control-label col-md-4 caption">Last Name:</label>
<div class="col-md-8">
@Html.TextBox("LastName", null, htmlAttributes: new { @class = "form-control", id = "lName" })
</div>
</div>
<div class="form-group">
<label for="adrs" class="control-label col-md-4 caption">Address:</label>
<div class="col-md-8">
@Html.TextBox("Address", null, htmlAttributes: new { @class = "form-control", id = "adrs" })
</div>
</div>
<div class="form-group">
<label for="ct" class="control-label col-md-4 caption">City:</label>
<div class="col-md-8">
@Html.TextBox("City", null, htmlAttributes: new { @class = "form-control", id = "ct" })
</div>
</div>
<div class="form-group">
<label for="county" class="control-label col-md-4 caption">County:</label>
<div class="col-md-8">
@Html.TextBox("County", null, htmlAttributes: new { @class = "form-control", id = "county" })
</div>
</div>
<div class="form-group">
<label for="state" class="control-label col-md-4 caption">State:</label>
<div class="col-md-8">
@Html.TextBox("State", null, htmlAttributes: new { @class = "form-control", id = "state" })
</div>
</div>
<div class="form-group">
<label for="zip" class="control-label col-md-4 caption">ZIP Code:</label>
<div class="col-md-8">
@Html.TextBox("ZIPCode", null, htmlAttributes: new { @class = "form-control", id = "zip" })
</div>
</div>
<div class="form-group">
<label class="control-label col-md-5">
@Html.ActionLink("Customers", "Index", null,
htmlAttributes: new { @class = "water-nav" })
</label>
<div class="col-md-7">
<input type="submit" value="Create Customer Account" class="btn btn-primary" />
</div>
</div>
</div>
}@model WaterDistributionCompany1.Models.Customer
@{
ViewBag.Title = "Edit/Update Customer Information";
}
<div class="push-down">
<h2>Edit/Update Customer Information</h2>
</div>
<hr />
@using (Html.BeginForm())
{
<div class="form-horizontal common-font containment">
@Html.HiddenFor(model => model.CustomerID)
<div class="form-group">
@Html.LabelFor(model => model.AccountNumber, htmlAttributes: new { @class = "control-label col-md-4 caption" })
<div class="col-md-8">
@Html.EditorFor(model => model.AccountNumber, new { htmlAttributes = new { @class = "form-control" } })
</div>
</div>
<div class="form-group">
<label for="mtr" class="control-label col-md-4 caption">Water Meter:</label>
<div class="col-md-8">
@Html.DropDownList("WaterMeterID", null, htmlAttributes: new { @class = "form-control", id = "mtr" })
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.FirstName, htmlAttributes: new { @class = "control-label col-md-4 caption" })
<div class="col-md-8">
@Html.EditorFor(model => model.FirstName, new { htmlAttributes = new { @class = "form-control" } })
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.LastName, htmlAttributes: new { @class = "control-label col-md-4 caption" })
<div class="col-md-8">
@Html.EditorFor(model => model.LastName, new { htmlAttributes = new { @class = "form-control" } })
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.Address, htmlAttributes: new { @class = "control-label col-md-4 caption" })
<div class="col-md-8">
@Html.EditorFor(model => model.Address, new { htmlAttributes = new { @class = "form-control" } })
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.City, htmlAttributes: new { @class = "control-label col-md-4 caption" })
<div class="col-md-8">
@Html.EditorFor(model => model.City, new { htmlAttributes = new { @class = "form-control" } })
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.County, htmlAttributes: new { @class = "control-label col-md-4 caption" })
<div class="col-md-8">
@Html.EditorFor(model => model.County, new { htmlAttributes = new { @class = "form-control" } })
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.State, htmlAttributes: new { @class = "control-label col-md-4 caption" })
<div class="col-md-8">
@Html.EditorFor(model => model.State, new { htmlAttributes = new { @class = "form-control" } })
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.ZIPCode, htmlAttributes: new { @class = "control-label col-md-4 caption" })
<div class="col-md-8">
@Html.EditorFor(model => model.ZIPCode, new { htmlAttributes = new { @class = "form-control" } })
</div>
</div>
<div class="form-group">
<label class="control-label col-md-4 caption">
@Html.ActionLink("Customers", "Index", null, new { @class = "water-nav" })
</label>
<div class="col-md-8">
<input type="submit" value="Update Customer Information" class="btn btn-primary" />
</div>
</div>
</div>
}@model WaterDistributionCompany1.Models.Customer
@{
ViewBag.Title = "Remove Customer Account";
}
<div class="push-down">
<h2>Cancelling Customer Account</h2>
</div>
<hr />
<div class="containment">
<dl class="dl-horizontal common-font caption">
<dt>@Html.DisplayNameFor(model => model.CustomerID)</dt>
<dd>@Html.DisplayFor(model => model.CustomerID)</dd>
<dt>@Html.DisplayNameFor(model => model.AccountNumber)</dt>
<dd>@Html.DisplayFor(model => model.AccountNumber)</dd>
<dt>@Html.DisplayNameFor(model => model.WaterMeterID)</dt>
<dd>@Html.DisplayFor(model => model.WaterMeterID)</dd>
<dt>@Html.DisplayNameFor(model => model.FirstName)</dt>
<dd>@Html.DisplayFor(model => model.FirstName)</dd>
<dt>@Html.DisplayNameFor(model => model.LastName)</dt>
<dd>@Html.DisplayFor(model => model.LastName)</dd>
<dt>@Html.DisplayNameFor(model => model.Address)</dt>
<dd>@Html.DisplayFor(model => model.Address)</dd>
<dt>@Html.DisplayNameFor(model => model.City)</dt>
<dd>@Html.DisplayFor(model => model.City)</dd>
<dt>@Html.DisplayNameFor(model => model.County)</dt>
<dd>@Html.DisplayFor(model => model.County)</dd>
<dt>@Html.DisplayNameFor(model => model.State)</dt>
<dd>@Html.DisplayFor(model => model.State)</dd>
<dt>@Html.DisplayNameFor(model => model.ZIPCode)</dt>
<dd>@Html.DisplayFor(model => model.ZIPCode)</dd>
</dl>
<h3 class="common-font caption">Are you sure you want to cancel this customer's account?</h3>
@using (Html.BeginForm())
{
<div class="form-actions no-color">
<input type="submit" value="Delete this Customer's Account" class="btn btn-primary" /> |
@Html.ActionLink("Customers Accounts", "Index", null, new { @class = "water-nav" })
</div>
}
</div>@model IEnumerable<WaterDistributionCompany1.Models.Customer>
@{
ViewBag.Title = "Customers Accounts";
}
<div class="push-down">
<h2 class="common-font bold text-center">Customers Accounts</h2>
</div>
<hr />
<table class="table table-striped common-font">
<tr>
<th class="bold">Customer ID</th>
<th class="bold">Account #</th>
<th class="bold">Meter #</th>
<th class="bold">First Name</th>
<th class="bold">Last Name</th>
<th class="bold">Address</th>
<th class="bold">City</th>
<th class="bold">County</th>
<th class="bold">State</th>
<th class="bold">ZIP Code</th>
<th>@Html.ActionLink("New Customer Account", "Create", null, htmlAttributes: new { @class = "water-nav" })</th>
</tr>
@foreach (var item in Model)
{
<tr>
<td class="text-center">@Html.DisplayFor(modelItem => item.CustomerID)</td>
<td>@Html.DisplayFor(modelItem => item.AccountNumber)</td>
<td>@Html.DisplayFor(modelItem => item.WaterMeterID)</td>
<td>@Html.DisplayFor(modelItem => item.FirstName)</td>
<td>@Html.DisplayFor(modelItem => item.LastName)</td>
<td>@Html.DisplayFor(modelItem => item.Address)</td>
<td>@Html.DisplayFor(modelItem => item.City)</td>
<td>@Html.DisplayFor(modelItem => item.County)</td>
<td>@Html.DisplayFor(modelItem => item.State)</td>
<td>@Html.DisplayFor(modelItem => item.ZIPCode)</td>
<td>
@Html.ActionLink("Update", "Edit", new { id = item.CustomerID }) |
@Html.ActionLink("Review", "Details", new { id = item.CustomerID }) |
@Html.ActionLink("Remove", "Delete", new { id = item.CustomerID })
</td>
</tr>
}
</table>
| Account # | Meter # | First Name | Last Name | Address | City | County | State | ZIP Code |
| 9279-570-8394 | 799-28-461 | Thomas | Stones | 10252 Broward Ave #D4 | Frederick | Frederick | MD | 21703 |
| 4820-375-2842 | 392-44-572 | Akhil | Koumari | 748 Red Hills Rd | Roanoke | VA | 24012 | |
| 7518-302-6895 | 207-94-835 | Grace | Brenner | 4299 Peachtree Court | Rockville | Montgomery | MD | 20853 |
| 2038-413-9680 | 938-75-869 | Amidou | Gomah | 2075 Rose Hills Ave | Washington | DC | 20004 | |
| 5938-074-5293 | 592-84-957 | Marie | Rath | 582G Dunhill Ave | Lanham | Prince Georges | MD | 20706 |
Water Bills
A water bill is issued every three-month to each customer. The bill specifies the amount of water that was used and when a customer is expected to pay.
Practical Learning: Creating Water Bills
using System;
using System.ComponentModel.DataAnnotations;
namespace WaterDistributionCompany1.Models
{
public class WaterBill
{
[Display(Name = "Water Bill ID")]
public int WaterBillID { get; set; }
[Display(Name = "Invoice #")]
public int InvoiceNumber { get; set; }
[Display(Name = "Customer ID")]
public int CustomerID { get; set; }
[DataType(DataType.Date)]
[Display(Name = "Meter Reading Start Date")]
public DateTime MeterReadingStartDate { get; set; }
[DataType(DataType.Date)]
[Display(Name = "Meter Reading End Date")]
public DateTime MeterReadingEndDate { get; set; }
[Display(Name = "Billing Days")]
public int BillingDays { get; set; }
[Display(Name = "Counter Reading Start")]
public int CounterReadingStart { get; set; }
[Display(Name = "Counter Reading End")]
public int CounterReadingEnd { get; set; }
[Display(Name = "Total HCF")]
public int TotalHCF { get; set; }
[Display(Name = "Total Gallons")]
public int TotalGallons { get; set; }
[Display(Name = "1st 15 HCF")]
public decimal First15HCF { get; set; }
[Display(Name = "Next 10 HCF")]
public decimal Next10HCF { get; set; }
[Display(Name = "Remaining 10 HCF")]
public decimal RemainingHCF { get; set; }
[Display(Name = "Sewer Charges")]
public decimal SewerCharges { get; set; }
[Display(Name = "Storm Charges")]
public decimal StormCharges { get; set; }
[Display(Name = "Water Usage Charges")]
public decimal WaterUsageCharges { get; set; }
[Display(Name = "Total Charges")]
public decimal TotalCharges { get; set; }
[Display(Name = "Local Taxes")]
public decimal LocalTaxes { get; set; }
[Display(Name = "State Taxes")]
public decimal StateTaxes { get; set; }
[DataType(DataType.Date)]
[Display(Name = "Pmt Due Date")]
public DateTime PaymentDueDate { get; set; }
[Display(Name = "Amt Due")]
public decimal AmountDue { get; set; }
[DataType(DataType.Date)]
[Display(Name = "Late Pmt Date")]
public DateTime LatePaymentDueDate { get; set; }
[Display(Name = "Late Pmt Amt")]
public decimal LateAmountDue { get; set; }
}
}using System;
using System.Data;
using System.Web.Mvc;
using System.Data.SqlClient;
using System.Collections.Generic;
using WaterDistributionCompany1.Models;
namespace WaterDistributionCompany1.Controllers
{
public class WaterBillsController : Controller
{
List<WaterBill> waterBills = new List<WaterBill>();
// GET: WaterBills
public ActionResult Index()
{
using (SqlConnection scWaterDistribution = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["csWaterDistribution"].ConnectionString))
{
SqlCommand cmdWaterBills = new SqlCommand("SELECT WaterBillID, CustomerID, InvoiceNumber, " +
" MeterReadingStartDate, MeterReadingEndDate, " +
" BillingDays, CounterReadingStart, CounterReadingEnd, " +
" TotalHCF, TotalGallons, First15HCF, Next10HCF, " +
" RemainingHCF, SewerCharges, StormCharges, " +
" WaterUsageCharges, TotalCharges, CountyTaxes, " +
" StateTaxes, PaymentDueDate, AmountDue, " +
" LatePaymentDueDate, LateAmountDue " +
"FROM WaterBills; ",
scWaterDistribution);
scWaterDistribution.Open();
cmdWaterBills.ExecuteNonQuery();
SqlDataAdapter sdaWaterBills = new SqlDataAdapter(cmdWaterBills);
DataSet dsWaterBills = new DataSet("water-bill");
sdaWaterBills.Fill(dsWaterBills);
foreach (DataRow drWaterBill in dsWaterBills.Tables[0].Rows)
{
WaterBill bill = new WaterBill();
bill.WaterBillID = int.Parse(drWaterBill[0].ToString());
bill.CustomerID = int.Parse(drWaterBill[1].ToString());
bill.InvoiceNumber = int.Parse(drWaterBill[2].ToString());
bill.MeterReadingStartDate = DateTime.Parse(drWaterBill[3].ToString());
bill.MeterReadingEndDate = DateTime.Parse(drWaterBill[4].ToString());
bill.BillingDays = int.Parse(drWaterBill[5].ToString());
bill.CounterReadingStart = int.Parse(drWaterBill[6].ToString());
bill.CounterReadingEnd = int.Parse(drWaterBill[7].ToString());
bill.TotalHCF = int.Parse(drWaterBill[8].ToString());
bill.TotalGallons = int.Parse(drWaterBill[9].ToString());
bill.First15HCF = decimal.Parse(drWaterBill[10].ToString());
bill.Next10HCF = decimal.Parse(drWaterBill[11].ToString());
bill.RemainingHCF = decimal.Parse(drWaterBill[12].ToString());
bill.SewerCharges = decimal.Parse(drWaterBill[13].ToString());
bill.StormCharges = decimal.Parse(drWaterBill[14].ToString());
bill.WaterUsageCharges = decimal.Parse(drWaterBill[15].ToString());
bill.TotalCharges = decimal.Parse(drWaterBill[16].ToString());
bill.CountyTaxes = decimal.Parse(drWaterBill[17].ToString());
bill.StateTaxes = decimal.Parse(drWaterBill[18].ToString());
bill.PaymentDueDate = DateTime.Parse(drWaterBill[19].ToString());
bill.AmountDue = decimal.Parse(drWaterBill[20].ToString());
bill.LatePaymentDueDate = DateTime.Parse(drWaterBill[21].ToString());
bill.LateAmountDue = decimal.Parse(drWaterBill[22].ToString());
waterBills.Add(bill);
}
}
return View(waterBills);
}
// GET: WaterBills/Details/5
public ActionResult Details(int id)
{
WaterBill bill = new WaterBill();
using (SqlConnection scWaterDistribution = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["csWaterDistribution"].ConnectionString))
{
SqlCommand cmdWaterBills = new SqlCommand("SELECT WaterBillID, CustomerID, InvoiceNumber, " +
" MeterReadingStartDate, MeterReadingEndDate, " +
" BillingDays, CounterReadingStart, CounterReadingEnd, " +
" TotalHCF, TotalGallons, First15HCF, Next10HCF, " +
" RemainingHCF, SewerCharges, StormCharges, " +
" WaterUsageCharges, TotalCharges, CountyTaxes, " +
" StateTaxes, PaymentDueDate, AmountDue, " +
" LatePaymentDueDate, LateAmountDue " +
"WHERE WaterBillID = " + id + " " +
"FROM WaterBills; ",
scWaterDistribution);
scWaterDistribution.Open();
cmdWaterBills.ExecuteNonQuery();
SqlDataAdapter sdaWaterBills = new SqlDataAdapter(cmdWaterBills);
DataSet dsWaterBills = new DataSet("water-bill");
sdaWaterBills.Fill(dsWaterBills);
if (dsWaterBills.Tables[0].Rows.Count > 0)
{
bill.WaterBillID = int.Parse(dsWaterBills.Tables[0].Rows[0].ToString());
bill.CustomerID = int.Parse(dsWaterBills.Tables[0].Rows[1].ToString());
bill.InvoiceNumber = int.Parse(dsWaterBills.Tables[0].Rows[2].ToString());
bill.MeterReadingStartDate = DateTime.Parse(dsWaterBills.Tables[0].Rows[3].ToString());
bill.MeterReadingEndDate = DateTime.Parse(dsWaterBills.Tables[0].Rows[4].ToString());
bill.BillingDays = int.Parse(dsWaterBills.Tables[0].Rows[5].ToString());
bill.CounterReadingStart = int.Parse(dsWaterBills.Tables[0].Rows[6].ToString());
bill.CounterReadingEnd = int.Parse(dsWaterBills.Tables[0].Rows[7].ToString());
bill.TotalHCF = int.Parse(dsWaterBills.Tables[0].Rows[8].ToString());
bill.TotalGallons = int.Parse(dsWaterBills.Tables[0].Rows[9].ToString());
bill.First15HCF = decimal.Parse(dsWaterBills.Tables[0].Rows[10].ToString());
bill.Next10HCF = decimal.Parse(dsWaterBills.Tables[0].Rows[11].ToString());
bill.RemainingHCF = decimal.Parse(dsWaterBills.Tables[0].Rows[12].ToString());
bill.SewerCharges = decimal.Parse(dsWaterBills.Tables[0].Rows[13].ToString());
bill.StormCharges = decimal.Parse(dsWaterBills.Tables[0].Rows[14].ToString());
bill.WaterUsageCharges = decimal.Parse(dsWaterBills.Tables[0].Rows[15].ToString());
bill.TotalCharges = decimal.Parse(dsWaterBills.Tables[0].Rows[16].ToString());
bill.CountyTaxes = decimal.Parse(dsWaterBills.Tables[0].Rows[17].ToString());
bill.StateTaxes = decimal.Parse(dsWaterBills.Tables[0].Rows[18].ToString());
bill.PaymentDueDate = DateTime.Parse(dsWaterBills.Tables[0].Rows[19].ToString());
bill.AmountDue = decimal.Parse(dsWaterBills.Tables[0].Rows[20].ToString());
bill.LatePaymentDueDate = DateTime.Parse(dsWaterBills.Tables[0].Rows[21].ToString());
bill.LateAmountDue = decimal.Parse(dsWaterBills.Tables[0].Rows[22].ToString());
waterBills.Add(bill);
}
}
return View(bill);
}
// GET: WaterBills/StartMeterReading
public ActionResult StartMeterReading()
{
return View();
}
// GET: WaterBills/PrepareMeterReading
public ActionResult PrepareMeterReading(string AccountNumber, string MeterReadingEndDate, string CounterReadingEnd)
{
int numberOfDays = -1;
Customer client = null;
WaterMeter meter = null;
Random rndNumber = new Random();
bool validAccountNumber = false;
int previousCounterReading = -1;
DateTime previousReadingDate = new DateTime();
// Open the table of Customers and check that the account number the user typed is valid
using (SqlConnection scWaterDistribution = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["csWaterDistribution"].ConnectionString))
{
SqlCommand cmdCustomers = new SqlCommand("SELECT CustomerID, AccountNumber, WaterMeterID, " +
" FirstName, LastName, Address, City, " +
" County, State, ZIPCode " +
"FROM Customers " +
"WHERE AccountNumber = N'" + AccountNumber + "';",
scWaterDistribution);
scWaterDistribution.Open();
cmdCustomers.ExecuteNonQuery();
SqlDataAdapter sdaCustomers = new SqlDataAdapter(cmdCustomers);
DataSet dsCustomers = new DataSet("customers");
sdaCustomers.Fill(dsCustomers);
if (dsCustomers.Tables[0].Rows.Count > 0)
{
validAccountNumber = true;
DataRow drCustomer = dsCustomers.Tables[0].Rows[0];
client = new Customer
{
CustomerID = int.Parse(drCustomer[0].ToString()),
AccountNumber = drCustomer[1].ToString(),
WaterMeterID = int.Parse(drCustomer[2].ToString()),
FirstName = drCustomer[3].ToString(),
LastName = drCustomer[4].ToString(),
Address = drCustomer[5].ToString(),
City = drCustomer[6].ToString(),
County = drCustomer[7].ToString(),
State = drCustomer[8].ToString(),
ZIPCode = drCustomer[9].ToString()
};
}
}
// If the user provided a valid customer account number, ...
if (validAccountNumber == true)
{
// ... get the water meter used by the customer
using (SqlConnection scWaterDistribution = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["csWaterDistribution"].ConnectionString))
{
SqlCommand cmdWaterMeters = new SqlCommand("SELECT WaterMeterID, MeterNumber, Make, Model, " +
" MeterSize, DateLastUpdate, CounterValue " +
"FROM WaterMeters " +
"WHERE WaterMeterID = " + client.WaterMeterID + ";",
scWaterDistribution);
scWaterDistribution.Open();
cmdWaterMeters.ExecuteNonQuery();
SqlDataAdapter sdaWaterMeters = new SqlDataAdapter(cmdWaterMeters);
DataSet dsWaterMeters = new DataSet("water-meters");
sdaWaterMeters.Fill(dsWaterMeters);
if (dsWaterMeters.Tables[0].Rows.Count > 0)
{
meter = new WaterMeter
{
WaterMeterID = int.Parse(dsWaterMeters.Tables[0].Rows[0][0].ToString()),
MeterNumber = dsWaterMeters.Tables[0].Rows[0][1].ToString(),
Make = dsWaterMeters.Tables[0].Rows[0][2].ToString(),
Model = dsWaterMeters.Tables[0].Rows[0][3].ToString(),
MeterSize = dsWaterMeters.Tables[0].Rows[0][4].ToString(),
DateLastUpdate = DateTime.Parse(dsWaterMeters.Tables[0].Rows[0][5].ToString()),
CounterValue = int.Parse(dsWaterMeters.Tables[0].Rows[0][6].ToString())
};
/* From the table of water meters, we want the counter value of the water meter that
* * is used by the customer. We will use that value as the meter reading start in
* * case this is the first time a water bill is created for the customer. */
previousReadingDate = DateTime.Parse(dsWaterMeters.Tables[0].Rows[0][5].ToString());
previousCounterReading = int.Parse(dsWaterMeters.Tables[0].Rows[0][6].ToString());
}
}
}
/* If the table of water bills contains at least one record, open it;
* but most importantly, if there are any records, get the record(s)
* of the account number the user provided in the form. */
using (SqlConnection scWaterDistribution = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["csWaterDistribution"].ConnectionString))
{
SqlCommand cmdWaterBills = new SqlCommand("SELECT WaterBillID, CustomerID, MeterReadingEndDate, CounterReadingEnd " +
"FROM WaterBills " +
"WHERE CustomerID = " + client.CustomerID + "; ",
scWaterDistribution);
scWaterDistribution.Open();
cmdWaterBills.ExecuteNonQuery();
SqlDataAdapter sdaWaterBills = new SqlDataAdapter(cmdWaterBills);
DataSet dsCustomers = new DataSet("water-bill");
sdaWaterBills.Fill(dsCustomers);
/* If at one water bill was previously created for the customer,
* get the last CounterReadingEnd and make it the previous counter reading value. */
foreach (DataRow drWaterBill in dsCustomers.Tables[0].Rows)
{
previousReadingDate = DateTime.Parse(drWaterBill[2].ToString());
previousCounterReading = int.Parse(drWaterBill[3].ToString());
}
}
TimeSpan tsDays = new TimeSpan();
DateTime dtMeterReadingStartDate = previousReadingDate;
DateTime dtMeterReadingEndDate = DateTime.Parse(MeterReadingEndDate);
tsDays = dtMeterReadingEndDate - dtMeterReadingStartDate;
numberOfDays = tsDays.Days;
int totalHCF = int.Parse(CounterReadingEnd) - previousCounterReading;
decimal first15HCF = totalHCF * 3.612M;
decimal next10HCF = 0, remainingHCF = 0;
if (totalHCF <= 15)
{
first15HCF = totalHCF * 3.612M;
next10HCF = 0;
remainingHCF = 0;
}
else if (totalHCF <= 25M)
{
first15HCF = 15M * 3.612M;
next10HCF = (totalHCF - 15M) * 3.918M;
remainingHCF = 0;
}
else
{
first15HCF = 15M * 3.612M;
next10HCF = 10M * 3.918M;
remainingHCF = (totalHCF - 25M) * 2.2763M;
}
decimal waterUsageCharges = first15HCF + next10HCF + remainingHCF;
decimal sewerCharges = waterUsageCharges * 0.252M;
decimal stormCharges = waterUsageCharges * 0.0025M;
decimal totalCharges = waterUsageCharges + sewerCharges + stormCharges;
decimal countyTaxes = totalCharges * 0.005M;
decimal stateTaxes = totalCharges * 0.0152M;
decimal amountDue = totalCharges + countyTaxes + stateTaxes;
ViewData["CustomerID"] = client.CustomerID;
ViewBag.City = client.City;
ViewBag.State = client.State;
ViewBag.County = client.County;
ViewBag.ZIPCode = client.ZIPCode;
ViewBag.MeterDetails = meter.Description;
ViewBag.CustomerAddress = client.Address;
ViewBag.CustomerName = client.FirstName + " " + client.LastName;
ViewBag.InvoiceNumber = rndNumber.Next(100001, 999999).ToString();
ViewBag.TotalHCF = totalHCF;
ViewBag.AmountDue = amountDue.ToString("F");
ViewBag.Next10HCF = next10HCF.ToString("F");
ViewBag.StateTaxes = stateTaxes.ToString("F");
ViewBag.First15HCF = first15HCF.ToString("F");
ViewBag.LocalTaxes = countyTaxes.ToString("F");
ViewBag.SewerCharges = sewerCharges.ToString("F");
ViewBag.StormCharges = stormCharges.ToString("F");
ViewBag.RemainingHCF = remainingHCF.ToString("F");
ViewBag.TotalCharges = totalCharges.ToString("F");
ViewBag.LateAmountDue = (amountDue + 8.95M).ToString("F");
ViewBag.WaterUsageCharges = waterUsageCharges.ToString("F");
ViewBag.BillingDays = numberOfDays.ToString();
ViewBag.CounterReadingStart = previousCounterReading.ToString();
ViewBag.MeterReadingStartDate = previousReadingDate.ToShortDateString();
ViewBag.PaymentDueDate = dtMeterReadingEndDate.AddDays(28).ToShortDateString();
ViewBag.LatePaymentDueDate = dtMeterReadingEndDate.AddDays(45).ToShortDateString();
ViewBag.TotalGallons = (int.Parse(CounterReadingEnd) - previousCounterReading) * 748; // 748.05
return View();
}
// GET: WaterBills/Create
public ActionResult Create()
{
return View();
}
// POST: WaterBills/Create
[HttpPost]
public ActionResult Create(FormCollection collection)
{
try
{
// TODO: Add insert logic here
// Save the water bill based on the values from the form
using (SqlConnection scWaterDistribution = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["csWaterDistribution"].ConnectionString))
{
SqlCommand cmdWaterBills = new SqlCommand("INSERT INTO WaterBills(CustomerID, InvoiceNumber, MeterReadingStartDate, " +
" MeterReadingEndDate, BillingDays, CounterReadingStart, " +
" CounterReadingEnd, TotalHCF, TotalGallons, First15HCF, " +
" Next10HCF, RemainingHCF, SewerCharges, StormCharges, " +
" WaterUsageCharges, TotalCharges, CountyTaxes, StateTaxes, " +
" PaymentDueDate, AmountDue, LatePaymentDueDate, LateAmountDue) " +
"VALUES(" + collection["CustomerID"] + ", " + collection["InvoiceNumber"] + ", N'" +
collection["MeterReadingStartDate"] + "', N'" + collection["MeterReadingEndDate"] +
"', " + collection["BillingDays"] + ", " + collection["CounterReadingStart"] +
", " + collection["CounterReadingEnd"] + ", " + collection["TotalHCF"] + ", " +
collection["TotalGallons"] + ", " + collection["First15HCF"] + ", " +
collection["Next10HCF"] + ", " + collection["RemainingHCF"] + ", " +
collection["SewerCharges"] + ", " + collection["StormCharges"] + ", " +
collection["WaterUsageCharges"] + ", " + collection["TotalCharges"] + ", " +
collection["CountyTaxes"] + ", " + collection["StateTaxes"] + ", N'" +
collection["PaymentDueDate"] + "', " + collection["AmountDue"] + ", N'" +
collection["LatePaymentDueDate"] + "', " + collection["LateAmountDue"] + ");",
scWaterDistribution);
scWaterDistribution.Open();
cmdWaterBills.ExecuteNonQuery();
}
return RedirectToAction("Index");
}
catch
{
return View("Index");
}
}
// GET: WaterBills/Edit/5
public ActionResult Edit(int id)
{
return View();
}
// POST: WaterBills/Edit/5
[HttpPost]
public ActionResult Edit(int id, FormCollection collection)
{
try
{
// TODO: Add update logic here
return RedirectToAction("Index");
}
catch
{
return View();
}
}
// GET: WaterBills/Delete/5
public ActionResult Delete(int id)
{
return View();
}
// POST: WaterBills/Delete/5
[HttpPost]
public ActionResult Delete(int id, FormCollection collection)
{
try
{
// TODO: Add delete logic here
return RedirectToAction("Index");
}
catch
{
return View();
}
}
}
}
@model IEnumerable<WaterDistributionCompany1.Models.WaterBill>
@{
ViewBag.Title = "Water Bills";
}
<div class="push-down">
<h2>Water Bills</h2>
</div>
<hr />
<table class="table table-striped common-font">
<tr>
<th class="bold text-center">Water Bill ID</th>
<th class="bold text-center">Invoice #</th>
<th class="bold text-center">Customer ID</th>
<th class="bold text-center">Meter Reading Start Date</th>
<th class="bold text-center">Meter Reading End Date</th>
<th class="bold">Billing Days</th>
<th class="bold">Counter Reading Start</th>
<th class="bold">Counter Reading End</th>
<th class="bold">Total HCF</th>
<th class="bold">Total Gallons</th>
<th class="bold">First 15 HCF</th>
<th class="bold text-center">Next 10 HCF</th>
<th class="bold text-center">Remaining HCF</th>
<th class="bold text-center">Sewer Charges</th>
<th class="bold text-center">Storm Charges</th>
<th class="bold text-center">Water Usage Charges</th>
<th class="bold text-center">Total Charges</th>
<th class="bold text-center">Local Taxes</th>
<th class="bold text-center">State Taxes</th>
<th class="bold text-center">Payment Due Date</th>
<th class="bold text-center">Amount Due</th>
<th class="bold text-center">Late Payment Due Date</th>
<th class="bold text-center">Late Amount Due</th>
<th>@Html.ActionLink("New Water Bill", "Create", null, htmlAttributes: new { @class = "water-nav" })</th>
</tr>
@foreach (var item in Model)
{
<tr>
<td class="text-center">@Html.DisplayFor(modelItem => item.WaterBillID)</td>
<td class="text-center">@Html.DisplayFor(modelItem => item.InvoiceNumber)</td>
<td class="text-center">@Html.DisplayFor(modelItem => item.CustomerID)</td>
<td class="text-center">@Html.DisplayFor(modelItem => item.MeterReadingStartDate)</td>
<td class="text-center">@Html.DisplayFor(modelItem => item.MeterReadingEndDate)</td>
<td class="text-center">@Html.DisplayFor(modelItem => item.BillingDays)</td>
<td class="text-center">@Html.DisplayFor(modelItem => item.CounterReadingStart)</td>
<td class="text-center">@Html.DisplayFor(modelItem => item.CounterReadingEnd)</td>
<td class="text-center">@Html.DisplayFor(modelItem => item.TotalHCF)</td>
<td class="text-center">@Html.DisplayFor(modelItem => item.TotalGallons)</td>
<td class="text-center">@Html.DisplayFor(modelItem => item.First15HCF)</td>
<td class="text-center">@Html.DisplayFor(modelItem => item.Next10HCF)</td>
<td class="text-center">@Html.DisplayFor(modelItem => item.RemainingHCF)</td>
<td class="text-center">@Html.DisplayFor(modelItem => item.SewerCharges)</td>
<td class="text-center">@Html.DisplayFor(modelItem => item.StormCharges)</td>
<td class="text-center">@Html.DisplayFor(modelItem => item.WaterUsageCharges)</td>
<td class="text-center">@Html.DisplayFor(modelItem => item.TotalCharges)</td>
<td class="text-center">@Html.DisplayFor(modelItem => item.LocalTaxes)</td>
<td class="text-center">@Html.DisplayFor(modelItem => item.StateTaxes)</td>
<td class="text-center">@Html.DisplayFor(modelItem => item.PaymentDueDate)</td>
<td class="text-center">@Html.DisplayFor(modelItem => item.AmountDue)</td>
<td class="text-center">@Html.DisplayFor(modelItem => item.LatePaymentDueDate)</td>
<td class="text-center">@Html.DisplayFor(modelItem => item.LateAmountDue)</td>
<td>
@Html.ActionLink("Update", "Edit", new { id = item.WaterBillID }) |
@Html.ActionLink("Review", "Details", new { id = item.WaterBillID }) |
@Html.ActionLink("Remove", "Delete", new { id = item.WaterBillID })
</td>
</tr>
}
</table>@model WaterDistributionCompany1.Models.WaterBill
@{
ViewBag.Title = "Water Bill Details";
}
<div class="push-down">
<h2>Water Bill Details</h2>
</div>
<hr />
<div class="containment">
<dl class="dl-horizontal common-font caption">
<dt>@Html.DisplayNameFor(model => model.WaterBillID)</dt>
<dd>@Html.DisplayFor(model => model.WaterBillID)</dd>
<dt>@Html.DisplayNameFor(model => model.InvoiceNumber)</dt>
<dd>@Html.DisplayFor(model => model.InvoiceNumber)</dd>
<dt>@Html.DisplayNameFor(model => model.CustomerID)</dt>
<dd>@Html.DisplayFor(model => model.CustomerID)</dd>
<dt>@Html.DisplayNameFor(model => model.MeterReadingStartDate)</dt>
<dd>@Html.DisplayFor(model => model.MeterReadingStartDate)</dd>
<dt>@Html.DisplayNameFor(model => model.MeterReadingEndDate)</dt>
<dd>@Html.DisplayFor(model => model.MeterReadingEndDate)</dd>
<dt>@Html.DisplayNameFor(model => model.BillingDays)</dt>
<dd>@Html.DisplayFor(model => model.BillingDays)</dd>
<dt>@Html.DisplayNameFor(model => model.CounterReadingStart)</dt>
<dd>@Html.DisplayFor(model => model.CounterReadingStart)</dd>
<dt>@Html.DisplayNameFor(model => model.CounterReadingEnd)</dt>
<dd>@Html.DisplayFor(model => model.CounterReadingEnd)</dd>
<dt>@Html.DisplayNameFor(model => model.TotalHCF)</dt>
<dd>@Html.DisplayFor(model => model.TotalHCF)</dd>
<dt>@Html.DisplayNameFor(model => model.TotalGallons)</dt>
<dd>@Html.DisplayFor(model => model.TotalGallons)</dd>
<dt>@Html.DisplayNameFor(model => model.First15HCF)</dt>
<dd>@Html.DisplayFor(model => model.First15HCF)</dd>
<dt>@Html.DisplayNameFor(model => model.Next10HCF)</dt>
<dd>@Html.DisplayFor(model => model.Next10HCF)</dd>
<dt>@Html.DisplayNameFor(model => model.RemainingHCF)</dt>
<dd>@Html.DisplayFor(model => model.RemainingHCF)</dd>
<dt>@Html.DisplayNameFor(model => model.SewerCharges)</dt>
<dd>@Html.DisplayFor(model => model.SewerCharges)</dd>
<dt>@Html.DisplayNameFor(model => model.StormCharges)</dt>
<dd>@Html.DisplayFor(model => model.StormCharges)</dd>
<dt>@Html.DisplayNameFor(model => model.WaterUsageCharges)</dt>
<dd>@Html.DisplayFor(model => model.WaterUsageCharges)</dd>
<dt>@Html.DisplayNameFor(model => model.TotalCharges)</dt>
<dd>@Html.DisplayFor(model => model.TotalCharges)</dd>
<dt>@Html.DisplayNameFor(model => model.LocalTaxes)</dt>
<dd>@Html.DisplayFor(model => model.CountyTaxes)</dd>
<dt>@Html.DisplayNameFor(model => model.StateTaxes)</dt>
<dd>@Html.DisplayFor(model => model.StateTaxes)</dd>
<dt>@Html.DisplayNameFor(model => model.PaymentDueDate)</dt>
<dd>@Html.DisplayFor(model => model.PaymentDueDate)</dd>
<dt>@Html.DisplayNameFor(model => model.AmountDue)</dt>
<dd>@Html.DisplayFor(model => model.AmountDue)</dd>
<dt>@Html.DisplayNameFor(model => model.LatePaymentDueDate)</dt>
<dd>@Html.DisplayFor(model => model.LatePaymentDueDate)</dd>
<dt>@Html.DisplayNameFor(model => model.LateAmountDue)</dt>
<dd>@Html.DisplayFor(model => model.LateAmountDue)</dd>
</dl>
</div>
<p class="text-center">
@Html.ActionLink("Edit/Update Water Bill Information", "Edit",
new { id = Model.WaterBillID, @class = "water-nav" },
new { @class = "water-nav" }) |
@Html.ActionLink("Water Meters", "Index",
null, new { @class = "water-nav" })
</p>@{
ViewBag.Title = "Meter Reading Start-Up";
}
<div class="push-down">
<h2>Meter Reading Start-Up</h2>
</div>
@using (Html.BeginForm("PrepareMeterReading", "WaterBills", FormMethod.Post))
{
<div class="form-horizontal">
<div class="form-group">
<label for="acntNbr" class="control-label col-md-4 caption">Customer Account #:</label>
<div class="col-md-8">
@Html.TextBox("AccountNumber", null, htmlAttributes: new { @class = "form-control", id = "acntNbr" })
</div>
</div>
<div class="form-group">
<label for="mred" class="control-label col-md-4 caption">Meter Reading Date:</label>
<div class="col-md-8">
@Html.TextBox("MeterReadingEndDate", null,
htmlAttributes: new { @class = "form-control", type = "date", id = "mred" })
</div>
</div>
<div class="form-group">
<label for="cre" class="control-label col-md-4 caption">Current Meter Reading:</label>
<div class="col-md-8">
@Html.TextBox("CounterReadingEnd", null, htmlAttributes: new { @class = "form-control", id = "cre" })
</div>
</div>
<div class="form-group">
<label class="control-label col-md-5">
@Html.ActionLink("Water Meters", "Index", null,
htmlAttributes: new { @class = "water-nav" })
</label>
<div class="col-md-7">
<input type="submit" value="Prepare Meter Reading" class="btn btn-primary" />
</div>
</div>
</div>
}@{
ViewBag.Title = "Prepare Meter Reading";
}
<div class="push-down">
<h2>Prepare Meter Reading</h2>
</div>
<hr />
@using (Html.BeginForm("Create", "WaterBills", FormMethod.Post))
{
<div class="form-horizontal">
<div class="form-group">
<label for="billNbr" class="control-label col-md-4 caption">Water Bill #:</label>
<div class="col-md-8">
@Html.TextBox("InvoiceNumber", null, htmlAttributes: new { @class = "form-control", id = "billNbr" })
</div>
</div>
<div class="form-group">
<label for="acntNbr" class="control-label col-md-4 caption">Customer Account #:</label>
<div class="col-md-8">
@Html.TextBox("AccountNumber", null, htmlAttributes: new { @class = "form-control", id = "acntNbr", disabled = "disabled" })
@Html.Hidden("CustomerID", ViewData["CustomerID"] as string)
</div>
</div>
<div class="form-group">
<label for="mred" class="control-label col-md-4 caption">Customer Name:</label>
<div class="col-md-8">
@Html.TextBox("CustomerName", @ViewBag.CustomerName as string, new { @class = "form-control", disabled = "disabled" })
</div>
</div>
<div class="form-group">
<label class="control-label col-md-4 caption">Customer Address:</label>
<div class="col-md-8">
@Html.TextBox("CustomerAddress", @ViewBag.CustomerAddress as string, new { @class = "form-control", disabled = "disabled" })
</div>
</div>
<div class="form-group">
<label class="control-label col-md-4"> </label>
<div class="col-md-125">
@Html.TextBox("CustomerCity", @ViewBag.City as string, new { @class = "form-control", disabled = "disabled" })
</div>
<div class="col-md-125">
@Html.TextBox("CustomerCounty", @ViewBag.County as string, new { @class = "form-control", disabled = "disabled" })
</div>
<div class="col-md-125">
@Html.TextBox("CustomerState", @ViewBag.State as string, new { @class = "form-control", disabled = "disabled" })
</div>
<div class="col-md-125">
@Html.TextBox("CustomerZIPCode", @ViewBag.ZIPCode as string, new { @class = "form-control", disabled = "disabled" })
</div>
</div>
<div class="form-group">
<label class="control-label col-md-4 caption">Meter Details:</label>
<div class="col-md-8">
@Html.TextBox("MeterDetails", ViewBag.MeterDetails as string, htmlAttributes: new { @class = "form-control", disabled = "disabled" })
</div>
</div>
<hr />
<div class="form-group">
<label for="mrsd" class="control-label col-md-4 caption">Meter Reading Start Date:</label>
<div class="col-md-125">
@Html.TextBox("MeterReadingStartDate", ViewBag.MeterReadingStartDate as string,
htmlAttributes: new { @class = "form-control", id = "mrsd" })
</div>
<label for="mred" class="control-label col-md-125 caption">Meter Reading End Date:</label>
<div class="col-md-125">
@Html.TextBox("MeterReadingEndDate", null,
htmlAttributes: new { @class = "form-control", type = "date", id = "mred" })
</div>
<label for="days" class="control-label col-md-125 caption">Billing Days:</label>
<div class="col-md-125">
@Html.TextBox("BillingDays", ViewBag.BillingDays as string, htmlAttributes: new { @class = "form-control", id = "days" })
</div>
</div>
<div class="form-group">
<label for="crs" class="control-label col-md-4 caption">Counter Reading Start:</label>
<div class="col-md-125">
@Html.TextBox("CounterReadingStart", ViewBag.CounterReadingStart as string, htmlAttributes: new { @class = "form-control", id = "crs" })
</div>
<label for="cre" class="control-label col-md-125 caption">Current Meter Reading:</label>
<div class="col-md-125">
@Html.TextBox("CounterReadingEnd", null, htmlAttributes: new { @class = "form-control", id = "cre" })
</div>
<label for="thcf" class="control-label col-md-125 caption">Total HCF:</label>
<div class="col-md-125">
@Html.TextBox("TotalHCF", ViewBag.TotalHCF as string, htmlAttributes: new { @class = "form-control", id = "thcf" })
</div>
</div>
<div class="form-group">
<label class="control-label col-md-4"> </label>
<div class="col-md-125"> </div>
<label class="control-label col-md-125"> </label>
<div class="col-md-125"> </div>
<label for="gallons" class="control-label col-md-125 caption">Total Gallons:</label>
<div class="col-md-125">
@Html.TextBox("TotalGallons", ViewBag.TotalGallons as string, htmlAttributes: new { @class = "form-control", id = "gallons" })
</div>
</div>
<div class="form-group">
<label for="f15HCF" class="control-label col-md-4 caption">1st 15 HCF at $3.6121:</label>
<div class="col-md-125">
@Html.TextBox("First15HCF", ViewBag.First15HCF as string,
htmlAttributes: new { @class = "form-control", id = "f15HCF" })
</div>
<label for="next10HCF" class="control-label col-md-125 caption">Next 10 HCF at $3.9180:</label>
<div class="col-md-125">
@Html.TextBox("Next10HCF", ViewBag.Next10HCF as string,
htmlAttributes: new { @class = "form-control", id = "next10HCF" })
</div>
<label for="days" class="control-label col-md-125 caption">Remaining HCF at $4.2763:</label>
<div class="col-md-125">
@Html.TextBox("RemainingHCF", ViewBag.RemainingHCF as string, htmlAttributes: new { @class = "form-control", id = "days" })
</div>
</div>
<div class="form-group">
<label for="sewerCharges" class="control-label col-md-4 caption">Sewer Charges:</label>
<div class="col-md-125">
@Html.TextBox("SewerCharges", ViewBag.SewerCharges as string,
htmlAttributes: new { @class = "form-control", id = "sewerCharges" })
</div>
<label for="stormCharges" class="control-label col-md-125 caption">Storm Charges:</label>
<div class="col-md-125">
@Html.TextBox("StormCharges", ViewBag.StormCharges as string,
htmlAttributes: new { @class = "form-control", id = "stormCharges" })
</div>
<label for="wuc" class="control-label col-md-125 caption">Water Usage Charges:</label>
<div class="col-md-125">
@Html.TextBox("WaterUsageCharges", ViewBag.WaterUsageCharges as string, htmlAttributes: new { @class = "form-control", id = "wuc" })
</div>
</div>
<div class="form-group">
<label class="control-label col-md-4"> </label>
<div class="col-md-125"> </div>
<label class="control-label col-md-125"> </label>
<div class="col-md-125"> </div>
<label for="totalCharges" class="control-label col-md-125 caption">Total Charges:</label>
<div class="col-md-125">
@Html.TextBox("TotalCharges", ViewBag.TotalCharges as string, htmlAttributes: new { @class = "form-control", id = "totalCharges" })
</div>
</div>
<hr />
<div class="form-group">
<label class="control-label col-md-4"> </label>
<div class="col-md-125"> </div>
<label for="countyTaxes" class="control-label col-md-125 caption">Local Taxes:</label>
<div class="col-md-125">
@Html.TextBox("LocalTaxes", ViewBag.CountyTaxes as string,
htmlAttributes: new { @class = "form-control", id = "countyTaxes" })
</div>
<label for="stateTaxes" class="control-label col-md-125 caption">State Taxes:</label>
<div class="col-md-125">
@Html.TextBox("StateTaxes", null, htmlAttributes: new { @class = "form-control", id = "stateTaxes" })
</div>
</div>
<div class="form-group">
<label class="control-label col-md-4"> </label>
<div class="col-md-125"> </div>
<label for="pdd" class="control-label col-md-125 caption">Payment Due Date:</label>
<div class="col-md-125">
@Html.TextBox("PaymentDueDate", null,
htmlAttributes: new { @class = "form-control", id = "pdd" })
</div>
<label for="amtDue" class="control-label col-md-125 caption">Amount Due:</label>
<div class="col-md-125">
@Html.TextBox("AmountDue", null,
htmlAttributes: new { @class = "form-control", id = "amtDue" })
</div>
</div>
<div class="form-group">
<label class="control-label col-md-4 caption"> </label>
<div class="col-md-125"> </div>
<label for="lpdd" class="control-label col-md-125 caption">Late Payment Due Date:</label>
<div class="col-md-125">
@Html.TextBox("LatePaymentDueDate", null,
htmlAttributes: new { @class = "form-control", id = "lpdd" })
</div>
<label for="lateAmtDue" class="control-label col-md-125 caption">Late Amount Due:</label>
<div class="col-md-125">
@Html.TextBox("LateAmountDue", null, htmlAttributes: new { @class = "form-control", id = "lateAmtDue" })
</div>
</div>
<div class="form-group text-center">
<label class="control-label col-md-5">
@Html.ActionLink("Water Meters", "Index", null, htmlAttributes: new { @class = "water-nav" })
</label>
<div class="col-md-7">
<input type="submit" value="Save Water Bill" class="btn btn-primary" />
</div>
</div>
</div>
}using System.Web.Mvc;
namespace WaterDistributionCompany1.Controllers
{
public class HomeController : Controller
{
public ActionResult Index()
{
return View();
}
public ActionResult About()
{
ViewBag.Message = "We supply water to the whole region in a friendly and responsible manner.";
return View();
}
public ActionResult Contact()
{
ViewBag.Message = "Contact us for all your water concerns or issues.";
return View();
}
}
}
| Customer Account # | Meter Reading Date | Current Meter Reading |
| 7518-302-6895 | 07/30/2018 | 114 |
| 4820-375-2842 | 07/31/2018 | 109998 |
| 2038-413-9680 | 07/30/2018 | 137975 |
| 9279-570-8394 | 7-Aug-18 | 6275 |
| 7518-302-6895 | 7-Nov-18 | 118 |
| 2038-413-9680 | 27-Oct-18 | 137958 |
| 9279-570-8394 | 3-Nov-18 | 6295 |
| 4820-375-2842 | 29-Oct-18 | 110012 |
| 9279-570-8394 | 2/2/2019 | 6312 |
| 7518-302-6895 | 2/19/2019 | 126 |
| 5938-074-5293 | 10/2/2018 | 52 |
| 4820-375-2842 | 2/2/2019 | 110027 |
| 2038-413-9680 | 1/28/2019 | 138054 |
Water Bills Payments
Bill payment is the act of paying one's bill. To make this happen, customers receive regular bills that they must then pay.
Practical Learning: Creating Bills Payments
using System.ComponentModel.DataAnnotations;
using System;
using System.Data;
using System.Web.Mvc;
using System.Data.SqlClient;
using System.Collections.Generic;
using WaterDistributionCompany1.Models;
namespace WaterDistributionCompany1.Controllers
{
public class CustomersController : Controller
{
List<Customer> customers = new List<Customer>();
// GET: Customers
public ActionResult Index()
{
using (SqlConnection scWaterDistribution = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["csWaterDistribution"].ConnectionString))
{
SqlCommand cmdCustomers = new SqlCommand("SELECT CustomerID, AccountNumber, " +
" WaterMeters.MeterNumber + N' - ' + WaterMeters.Make + N' ' + WaterMeters.Model + N' (' + WaterMeters.MeterSize + N')', " +
" FirstName, LastName, Address, City, " +
" County, State, ZIPCode " +
"FROM Customers, WaterMeters " +
"WHERE Customers.WaterMeterID = WaterMeters.WaterMeterID; ",
scWaterDistribution);
scWaterDistribution.Open();
cmdCustomers.ExecuteNonQuery();
SqlDataAdapter sdaCustomers = new SqlDataAdapter(cmdCustomers);
DataSet dsCustomers = new DataSet("customers");
sdaCustomers.Fill(dsCustomers);
DataTable dtCustomers = dsCustomers.Tables[0];
for (int i = 0; i < dtCustomers.Rows.Count; i++)
{
DataRow drCustomer = dtCustomers.Rows[i];
clients.Add(new Client()
{
CustomerID = int.Parse(drCustomer[0].ToString()),
AccountNumber = drCustomer[1].ToString(),
WaterMeter = drCustomer[2].ToString(),
FirstName = drCustomer[3].ToString(),
LastName = drCustomer[4].ToString(),
Address = drCustomer[5].ToString(),
City = drCustomer[6].ToString(),
County = drCustomer[7].ToString(),
State = drCustomer[8].ToString(),
ZIPCode = drCustomer[9].ToString()
});
}
}
return View(clients);
}
. . . No Change
}
}@model IEnumerable<WaterDistributionCompany1.Models.Client>
@{
ViewBag.Title = "Customers Accounts";
}
<div class="push-down">
<h2 class="common-font bold text-center">Customers Accounts</h2>
</div>
<hr />
<table class="table table-striped common-font">
<tr>
<th class="bold">Customer ID</th>
<th class="bold">Account #</th>
<th class="bold">Water Meter</th>
<th class="bold">First Name</th>
<th class="bold">Last Name</th>
<th class="bold">Address</th>
<th class="bold">City</th>
<th class="bold">County</th>
<th class="bold">State</th>
<th class="bold">ZIP Code</th>
<th>@Html.ActionLink("New Customer Account", "Create", null, htmlAttributes: new { @class = "water-nav" })</th>
</tr>
@foreach (var item in Model)
{
<tr>
<td class="text-center">@Html.DisplayFor(modelItem => item.CustomerID)</td>
<td>@Html.DisplayFor(modelItem => item.AccountNumber)</td>
<td>@Html.DisplayFor(modelItem => item.WaterMeter)</td>
<td>@Html.DisplayFor(modelItem => item.FirstName)</td>
<td>@Html.DisplayFor(modelItem => item.LastName)</td>
<td>@Html.DisplayFor(modelItem => item.Address)</td>
<td>@Html.DisplayFor(modelItem => item.City)</td>
<td>@Html.DisplayFor(modelItem => item.County)</td>
<td>@Html.DisplayFor(modelItem => item.State)</td>
<td>@Html.DisplayFor(modelItem => item.ZIPCode)</td>
<td>
@Html.ActionLink("Edit", "Edit", new { id = item.CustomerID }) |
@Html.ActionLink("Review", "Details", new { id = item.CustomerID }) |
@Html.ActionLink("Delete", "Delete", new { id = item.CustomerID })
</td>
</tr>
}
</table>
using System;
using System.Data;
using System.Web.Mvc;
using System.Data.SqlClient;
using System.Collections.Generic;
using WaterDistributionCompany1.Models;
namespace WaterDistributionCompany1.Controllers
{
public class CustomersController : Controller
{
List<Client> clients = new List<Client>();
// GET: Customers
public ActionResult Index()
{
using (SqlConnection scWaterDistribution = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["csWaterDistribution"].ConnectionString))
{
SqlCommand cmdCustomers = new SqlCommand("SELECT CustomerID, AccountNumber, " +
" WaterMeters.MeterNumber + N' - ' + WaterMeters.Make + N' ' + WaterMeters.Model + N' (' + WaterMeters.MeterSize + N')', " +
" FirstName, LastName, Address, City, " +
" County, State, ZIPCode " +
"FROM Customers, WaterMeters " +
"WHERE Customers.WaterMeterID = WaterMeters.WaterMeterID;",
scWaterDistribution);
scWaterDistribution.Open();
cmdCustomers.ExecuteNonQuery();
SqlDataAdapter sdaCustomers = new SqlDataAdapter(cmdCustomers);
DataSet dsCustomers = new DataSet("customers");
sdaCustomers.Fill(dsCustomers);
DataTable dtCustomers = dsCustomers.Tables[0];
for (int i = 0; i < dtCustomers.Rows.Count; i++)
{
DataRow drCustomer = dtCustomers.Rows[i];
clients.Add(new Client()
{
CustomerID = int.Parse(drCustomer[0].ToString()),
AccountNumber = drCustomer[1].ToString(),
WaterMeter = drCustomer[2].ToString(),
FirstName = drCustomer[3].ToString(),
LastName = drCustomer[4].ToString(),
Address = drCustomer[5].ToString(),
City = drCustomer[6].ToString(),
County = drCustomer[7].ToString(),
State = drCustomer[8].ToString(),
ZIPCode = drCustomer[9].ToString()
});
}
}
return View(clients);
}
// GET: Customers/Details/5
public ActionResult Details(int id)
{
Client client = null;
using (SqlConnection scWaterDistribution = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["csWaterDistribution"].ConnectionString))
{
SqlCommand cmdCustomers = new SqlCommand("SELECT CustomerID, AccountNumber, " +
" WaterMeters.MeterNumber + N' - ' + WaterMeters.Make + N' ' + WaterMeters.Model + N' (' + WaterMeters.MeterSize + N')', " +
" FirstName, LastName, Address, City, " +
" County, State, ZIPCode " +
"FROM Customers, WaterMeters " +
"WHERE (CustomerID = " + id + ") AND (Customers.WaterMeterID = WaterMeters.WaterMeterID);",
scWaterDistribution);
scWaterDistribution.Open();
cmdCustomers.ExecuteNonQuery();
SqlDataAdapter sdaCustomers = new SqlDataAdapter(cmdCustomers);
DataSet dsCustomers = new DataSet("customers");
sdaCustomers.Fill(dsCustomers);
DataTable dtCustomers = dsCustomers.Tables[0];
if (dtCustomers.Rows.Count > 0)
{
DataRow drCustomer = dtCustomers.Rows[0];
client = new Client
{
CustomerID = int.Parse(drCustomer[0].ToString()),
AccountNumber = drCustomer[1].ToString(),
WaterMeter = drCustomer[2].ToString(),
FirstName = drCustomer[3].ToString(),
LastName = drCustomer[4].ToString(),
Address = drCustomer[5].ToString(),
City = drCustomer[6].ToString(),
County = drCustomer[7].ToString(),
State = drCustomer[8].ToString(),
ZIPCode = drCustomer[9].ToString()
};
}
}
return View(client);
}
// GET: Customers/Create
public ActionResult Create()
{
List<WaterMeter> waterMeters = new List<WaterMeter>();
using (SqlConnection scWaterDistribution = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["csWaterDistribution"].ConnectionString))
{
SqlCommand cmdWaterMeters = new SqlCommand("SELECT WaterMeterID, MeterNumber, Make, Model, MeterSize, DateLastUpdate, CounterValue " +
"FROM WaterMeters;",
scWaterDistribution);
scWaterDistribution.Open();
cmdWaterMeters.ExecuteNonQuery();
SqlDataAdapter sdaWaterMeters = new SqlDataAdapter(cmdWaterMeters);
DataSet dsWaterMeters = new DataSet("water-meters");
sdaWaterMeters.Fill(dsWaterMeters);
WaterMeter meter = null;
for (int i = 0; i < dsWaterMeters.Tables[0].Rows.Count; i++)
{
meter = new WaterMeter
{
WaterMeterID = int.Parse(dsWaterMeters.Tables[0].Rows[i][0].ToString()),
MeterNumber = dsWaterMeters.Tables[0].Rows[i][1].ToString(),
Make = dsWaterMeters.Tables[0].Rows[i][2].ToString(),
Model = dsWaterMeters.Tables[0].Rows[i][3].ToString(),
MeterSize = dsWaterMeters.Tables[0].Rows[i][4].ToString(),
DateLastUpdate = DateTime.Parse(dsWaterMeters.Tables[0].Rows[i][5].ToString()),
CounterValue = int.Parse(dsWaterMeters.Tables[0].Rows[i][6].ToString())
};
waterMeters.Add(meter);
}
}
ViewBag.WaterMeterID = new SelectList(waterMeters, "WaterMeterID ", "Description");
return View();
}
// POST: Customers/Create
[HttpPost]
public ActionResult Create(FormCollection collection)
{
try
{
// TODO: Add insert logic here
using (SqlConnection scWaterDistribution = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["csWaterDistribution"].ConnectionString))
{
SqlCommand cmdCustomers = new SqlCommand("INSERT INTO Customers(AccountNumber, WaterMeterID, " +
" FirstName, LastName, Address, City, " +
" County, State, ZIPCode) " +
"VALUES(N'" + collection["AccountNumber"] + "', " +
collection["WaterMeterID"] + ", N'" + collection["FirstName"] +
"', N'" + collection["LastName"] + "', N'" +
collection["Address"] + "', N'" + collection["City"] + "', N'" +
collection["County"] + "', N'" + collection["State"] + "', N'" +
collection["ZIPCode"] + "');",
scWaterDistribution);
scWaterDistribution.Open();
cmdCustomers.ExecuteNonQuery();
}
return RedirectToAction("Index");
}
catch
{
return View();
}
}
// GET: Customers/Edit/5
public ActionResult Edit(int id)
{
Customer client = null;
WaterMeter meter = null;
List<WaterMeter> waterMeters = new List<WaterMeter>();
using (SqlConnection scWaterDistribution = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["csWaterDistribution"].ConnectionString))
{
SqlCommand cmdCustomers = new SqlCommand("SELECT CustomerID, AccountNumber, WaterMeterID, " +
" FirstName, LastName, Address, City, " +
" County, State, ZIPCode " +
"FROM Customers " +
"WHERE CustomerID = " + id + ";",
scWaterDistribution);
scWaterDistribution.Open();
cmdCustomers.ExecuteNonQuery();
SqlDataAdapter sdaCustomers = new SqlDataAdapter(cmdCustomers);
DataSet dsCustomers = new DataSet("customers");
sdaCustomers.Fill(dsCustomers);
if (dsCustomers.Tables[0].Rows.Count > 0)
{
DataRow drCustomer = dsCustomers.Tables[0].Rows[0];
client = new Customer
{
CustomerID = int.Parse(drCustomer[0].ToString()),
AccountNumber = drCustomer[1].ToString(),
WaterMeterID = int.Parse(drCustomer[2].ToString()),
FirstName = drCustomer[3].ToString(),
LastName = drCustomer[4].ToString(),
Address = drCustomer[5].ToString(),
City = drCustomer[6].ToString(),
County = drCustomer[7].ToString(),
State = drCustomer[8].ToString(),
ZIPCode = drCustomer[9].ToString()
};
}
}
using (SqlConnection scWaterDistribution = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["csWaterDistribution"].ConnectionString))
{
SqlCommand cmdWaterMeters = new SqlCommand("SELECT WaterMeterID, MeterNumber, Make, Model, " +
" MeterSize, DateLastUpdate, CounterValue " +
"FROM WaterMeters;",
scWaterDistribution);
scWaterDistribution.Open();
cmdWaterMeters.ExecuteNonQuery();
SqlDataAdapter sdaWaterMeters = new SqlDataAdapter(cmdWaterMeters);
DataSet dsWaterMeters = new DataSet("water-meters");
sdaWaterMeters.Fill(dsWaterMeters);
for (int i = 0; i < dsWaterMeters.Tables[0].Rows.Count; i++)
{
meter = new WaterMeter
{
WaterMeterID = int.Parse(dsWaterMeters.Tables[0].Rows[i][0].ToString()),
MeterNumber = dsWaterMeters.Tables[0].Rows[i][1].ToString(),
Make = dsWaterMeters.Tables[0].Rows[i][2].ToString(),
Model = dsWaterMeters.Tables[0].Rows[i][3].ToString(),
MeterSize = dsWaterMeters.Tables[0].Rows[i][4].ToString(),
DateLastUpdate = DateTime.Parse(dsWaterMeters.Tables[0].Rows[i][5].ToString()),
CounterValue = int.Parse(dsWaterMeters.Tables[0].Rows[i][6].ToString())
};
waterMeters.Add(meter);
}
}
ViewBag.WaterMeterID = new SelectList(waterMeters, "WaterMeterID ", "Description", client.WaterMeterID);
return View(client);
}
// POST: Customers/Edit/5
[HttpPost]
public ActionResult Edit(int id, FormCollection collection)
{
try
{
// TODO: Add update logic here
using (SqlConnection scWaterDistribution = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["csWaterDistribution"].ConnectionString))
{
SqlCommand cmdCustomers = new SqlCommand("UPDATE Customers " +
"SET AccountNumber = N'" + collection["AccountNumber"] + "'," +
" WaterMeterID = " + collection["WaterMeterID"] + "," +
" FirstName = N'" + collection["FirstName"] + "'," +
" LastName = N'" + collection["LastName"] + "'," +
" Address = N'" + collection["Address"] + "'," +
" City = N'" + collection["City"] + "'," +
" County = N'" + collection["County"] + "'," +
" State = N'" + collection["State"] + "'," +
" ZIPCode = N'" + collection["ZIPCode"] + "' " +
"WHERE CustomerID = " + id + ";",
scWaterDistribution);
scWaterDistribution.Open();
cmdCustomers.ExecuteNonQuery();
}
return RedirectToAction("Index");
}
catch
{
return View();
}
}
// GET: Customers/Delete/5
public ActionResult Delete(int id)
{
Client client = null;
using (SqlConnection scWaterDistribution = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["csWaterDistribution"].ConnectionString))
{
SqlCommand cmdCustomers = new SqlCommand("SELECT CustomerID, AccountNumber, " +
" WaterMeters.MeterNumber + N' - ' + WaterMeters.Make + N' ' + WaterMeters.Model + N' (' + WaterMeters.MeterSize + N')', " +
" FirstName, LastName, Address, City, " +
" County, State, ZIPCode " +
"FROM Customers, WaterMeters " +
"WHERE (CustomerID = " + id + ") AND (Customers.WaterMeterID = WaterMeters.WaterMeterID);",
scWaterDistribution);
scWaterDistribution.Open();
cmdCustomers.ExecuteNonQuery();
SqlDataAdapter sdaCustomers = new SqlDataAdapter(cmdCustomers);
DataSet dsCustomers = new DataSet("customers");
sdaCustomers.Fill(dsCustomers);
if (dsCustomers.Tables[0].Rows.Count > 0)
{
DataRow drCustomer = dsCustomers.Tables[0].Rows[0];
client = new Client
{
CustomerID = int.Parse(drCustomer[0].ToString()),
AccountNumber = drCustomer[1].ToString(),
WaterMeter = drCustomer[2].ToString(),
FirstName = drCustomer[3].ToString(),
LastName = drCustomer[4].ToString(),
Address = drCustomer[5].ToString(),
City = drCustomer[6].ToString(),
County = drCustomer[7].ToString(),
State = drCustomer[8].ToString(),
ZIPCode = drCustomer[9].ToString()
};
}
}
return View(client);
}
// POST: Customers/Delete/5
[HttpPost]
public ActionResult Delete(int id, FormCollection collection)
{
try
{
// TODO: Add delete logic here
using (SqlConnection scWaterDistribution = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["csWaterDistribution"].ConnectionString))
{
SqlCommand cmdCustomers = new SqlCommand("DELETE FROM Customers " +
"WHERE CustomerID = " + id + ";",
scWaterDistribution);
scWaterDistribution.Open();
cmdCustomers.ExecuteNonQuery();
}
return RedirectToAction("Index");
}
catch
{
return View();
}
}
}
}@model WaterDistributionCompany1.Models.Client @{ ViewBag.Title = "Customer Details"; } <div class="push-down"> <h2>Customer Details</h2> </div> <hr /> <div class="containment"> <dl class="dl-horizontal common-font caption"> <dt>@Html.DisplayNameFor(model => model.CustomerID)</dt> <dd>@Html.DisplayFor(model => model.CustomerID)</dd> <dt>@Html.DisplayNameFor(model => model.AccountNumber)</dt> <dd>@Html.DisplayFor(model => model.AccountNumber)</dd> <dt>Water Meter</dt> <dd>@Html.DisplayFor(model => model.WaterMeter)</dd> <dt>@Html.DisplayNameFor(model => model.FirstName)</dt> <dd>@Html.DisplayFor(model => model.FirstName)</dd> <dt>@Html.DisplayNameFor(model => model.LastName)</dt> <dd>@Html.DisplayFor(model => model.LastName)</dd> <dt>@Html.DisplayNameFor(model => model.Address)</dt> <dd>@Html.DisplayFor(model => model.Address)</dd> <dt>@Html.DisplayNameFor(model => model.City)</dt> <dd>@Html.DisplayFor(model => model.City)</dd> <dt>@Html.DisplayNameFor(model => model.County)</dt> <dd>@Html.DisplayFor(model => model.County)</dd> <dt>@Html.DisplayNameFor(model => model.State)</dt> <dd>@Html.DisplayFor(model => model.State)</dd> <dt>@Html.DisplayNameFor(model => model.ZIPCode)</dt> <dd>@Html.DisplayFor(model => model.ZIPCode)</dd> </dl> </div> <p class="text-center"> @Html.ActionLink("Edit/Update Customer Information", "Edit", new { id = Model.CustomerID, @class = "water-nav" }, new { @class = "water-nav" }) | @Html.ActionLink("Customers Records", "Index", null, new { @class = "water-nav" }) </p>
@model WaterDistributionCompany1.Models.Client @{ ViewBag.Title = "Remove Customer Account"; } <div class="push-down"> <h2>Cancelling Customer Account</h2> </div> <hr /> <div class="containment"> <dl class="dl-horizontal common-font caption"> <dt>@Html.DisplayNameFor(model => model.CustomerID)</dt> <dd>@Html.DisplayFor(model => model.CustomerID)</dd> <dt>@Html.DisplayNameFor(model => model.AccountNumber)</dt> <dd>@Html.DisplayFor(model => model.AccountNumber)</dd> <dt>Water Meter</dt> <dd>@Html.DisplayFor(model => model.WaterMeter)</dd> <dt>@Html.DisplayNameFor(model => model.FirstName)</dt> <dd>@Html.DisplayFor(model => model.FirstName)</dd> <dt>@Html.DisplayNameFor(model => model.LastName)</dt> <dd>@Html.DisplayFor(model => model.LastName)</dd> <dt>@Html.DisplayNameFor(model => model.Address)</dt> <dd>@Html.DisplayFor(model => model.Address)</dd> <dt>@Html.DisplayNameFor(model => model.City)</dt> <dd>@Html.DisplayFor(model => model.City)</dd> <dt>@Html.DisplayNameFor(model => model.County)</dt> <dd>@Html.DisplayFor(model => model.County)</dd> <dt>@Html.DisplayNameFor(model => model.State)</dt> <dd>@Html.DisplayFor(model => model.State)</dd> <dt>@Html.DisplayNameFor(model => model.ZIPCode)</dt> <dd>@Html.DisplayFor(model => model.ZIPCode)</dd> </dl> <h3 class="common-font caption">Are you sure you want to cancel this customer's account?</h3> @using (Html.BeginForm()) { <div class="form-actions no-color"> <input type="submit" value="Delete this Customer's Account" class="btn btn-primary" /> | @Html.ActionLink("Customers Accounts", "Index", null, new { @class = "water-nav" }) </div> } </div>
using System;
using System.Collections.Generic;
using System.Linq;
using System.ComponentModel.DataAnnotations;
namespace WaterDistributionCompany1.Models
{
public class WaterBill
{
[Display(Name = "Water Bill ID")]
public int WaterBillID { get; set; }
[Display(Name = "Invoice #")]
public int InvoiceNumber { get; set; }
[Display(Name = "Customer ID")]
public int CustomerID { get; set; }
[DataType(DataType.Date)]
[Display(Name = "Meter Reading Start Date")]
public DateTime MeterReadingStartDate { get; set; }
[DataType(DataType.Date)]
[Display(Name = "Meter Reading End Date")]
public DateTime MeterReadingEndDate { get; set; }
[Display(Name = "Billing Days")]
public int BillingDays { get; set; }
[Display(Name = "Counter Reading Start")]
public int CounterReadingStart { get; set; }
[Display(Name = "Counter Reading End")]
public int CounterReadingEnd { get; set; }
[Display(Name = "Total HCF")]
public int TotalHCF { get; set; }
[Display(Name = "Total Gallons")]
public int TotalGallons { get; set; }
[Display(Name = "1st 15 HCF")]
public decimal First15HCF { get; set; }
[Display(Name = "Next 10 HCF")]
public decimal Next10HCF { get; set; }
[Display(Name = "Remaining 10 HCF")]
public decimal RemainingHCF { get; set; }
[Display(Name = "Sewer Charges")]
public decimal SewerCharges { get; set; }
[Display(Name = "Storm Charges")]
public decimal StormCharges { get; set; }
[Display(Name = "Water Usage Charges")]
public decimal WaterUsageCharges { get; set; }
[Display(Name = "Total Charges")]
public decimal TotalCharges { get; set; }
[Display(Name = "Local Taxes")]
public decimal LocalTaxes { get; set; }
[Display(Name = "State Taxes")]
public decimal StateTaxes { get; set; }
[DataType(DataType.Date)]
[Display(Name = "Pmt Due Date")]
public DateTime PaymentDueDate { get; set; }
[Display(Name = "Amt Due")]
public decimal AmountDue { get; set; }
[DataType(DataType.Date)]
[Display(Name = "Late Pmt Date")]
public DateTime LatePaymentDueDate { get; set; }
[Display(Name = "Late Pmt Amt")]
public decimal LateAmountDue { get; set; }
}
public class CustomerInvoice : WaterBill
{
public string CustomerAccount { get; set; }
}
}using System;
using System.Data;
using System.Web.Mvc;
using System.Data.SqlClient;
using System.Collections.Generic;
using WaterDistributionCompany1.Models;
namespace WaterDistributionCompany1.Controllers
{
public class WaterBillsController : Controller
{
List<WaterBill> waterBills = new List<WaterBill>();
. . . No Change
// GET: WaterBills/Details/5
public ActionResult Details(int id)
{
CustomerInvoice bill = new CustomerInvoice();
using (SqlConnection scWaterDistribution = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["csWaterDistribution"].ConnectionString))
{
SqlCommand cmdWaterBills = new SqlCommand("SELECT WaterBillID, InvoiceNumber, " +
" Customers.AccountNumber + N' - ' + Customers.LastName + N', ' + Customers.FirstName + N' (' + Customers.City + N', ' + Customers.State + N')', " +
" MeterReadingStartDate, MeterReadingEndDate, BillingDays, " +
" CounterReadingStart, CounterReadingEnd, TotalHCF, TotalGallons, " +
" First15HCF, Next10HCF, RemainingHCF, SewerCharges, StormCharges, " +
" WaterUsageCharges, TotalCharges, LocalTaxes, StateTaxes, " +
" PaymentDueDate, AmountDue, LatePaymentDueDate, LateAmountDue " +
"FROM WaterBills, Customers " +
"WHERE(WaterBills.WaterBillID = " + id + ") AND (WaterBills.CustomerID = Customers.CustomerID);",
scWaterDistribution);
scWaterDistribution.Open();
cmdWaterBills.ExecuteNonQuery();
SqlDataAdapter sdaWaterBills = new SqlDataAdapter(cmdWaterBills);
DataSet dsWaterBills = new DataSet("water-bills");
sdaWaterBills.Fill(dsWaterBills);
if (dsWaterBills.Tables[0].Rows.Count > 0)
{
bill.WaterBillID = int.Parse(dsWaterBills.Tables[0].Rows[0][0].ToString());
bill.InvoiceNumber = int.Parse(dsWaterBills.Tables[0].Rows[0][1].ToString());
bill.CustomerAccount = dsWaterBills.Tables[0].Rows[0][2].ToString();
bill.MeterReadingStartDate = DateTime.Parse(dsWaterBills.Tables[0].Rows[0][3].ToString());
bill.MeterReadingEndDate = DateTime.Parse(dsWaterBills.Tables[0].Rows[0][4].ToString());
bill.BillingDays = int.Parse(dsWaterBills.Tables[0].Rows[0][5].ToString());
bill.CounterReadingStart = int.Parse(dsWaterBills.Tables[0].Rows[0][6].ToString());
bill.CounterReadingEnd = int.Parse(dsWaterBills.Tables[0].Rows[0][7].ToString());
bill.TotalHCF = int.Parse(dsWaterBills.Tables[0].Rows[0][8].ToString());
bill.TotalGallons = int.Parse(dsWaterBills.Tables[0].Rows[0][9].ToString());
bill.First15HCF = decimal.Parse(dsWaterBills.Tables[0].Rows[0][10].ToString());
bill.Next10HCF = decimal.Parse(dsWaterBills.Tables[0].Rows[0][11].ToString());
bill.RemainingHCF = decimal.Parse(dsWaterBills.Tables[0].Rows[0][12].ToString());
bill.SewerCharges = decimal.Parse(dsWaterBills.Tables[0].Rows[0][13].ToString());
bill.StormCharges = decimal.Parse(dsWaterBills.Tables[0].Rows[0][14].ToString());
bill.WaterUsageCharges = decimal.Parse(dsWaterBills.Tables[0].Rows[0][15].ToString());
bill.TotalCharges = decimal.Parse(dsWaterBills.Tables[0].Rows[0][16].ToString());
bill.LocalTaxes = decimal.Parse(dsWaterBills.Tables[0].Rows[0][17].ToString());
bill.StateTaxes = decimal.Parse(dsWaterBills.Tables[0].Rows[0][18].ToString());
bill.PaymentDueDate = DateTime.Parse(dsWaterBills.Tables[0].Rows[0][19].ToString());
bill.AmountDue = decimal.Parse(dsWaterBills.Tables[0].Rows[0][20].ToString());
bill.LatePaymentDueDate = DateTime.Parse(dsWaterBills.Tables[0].Rows[0][21].ToString());
bill.LateAmountDue = decimal.Parse(dsWaterBills.Tables[0].Rows[0][22].ToString());
waterBills.Add(bill);
}
}
return View(bill);
}
. . . No Change
}
}
@model WaterDistributionCompany1.Models.CustomerInvoice @{ ViewBag.Title = "Water Bill Details"; } <div class="push-down"> <h2>Water Bill Details</h2> </div> <hr /> <div class="containment"> <dl class="dl-horizontal common-font caption"> <dt>@Html.DisplayNameFor(model => model.WaterBillID)</dt> <dd>@Html.DisplayFor(model => model.WaterBillID)</dd> <dt>@Html.DisplayNameFor(model => model.InvoiceNumber)</dt> <dd>@Html.DisplayFor(model => model.InvoiceNumber)</dd> <dt>Customer</dt> <dd>@Html.DisplayFor(model => model.CustomerAccount)</dd> <dt>@Html.DisplayNameFor(model => model.MeterReadingStartDate)</dt> <dd>@Html.DisplayFor(model => model.MeterReadingStartDate)</dd> <dt>@Html.DisplayNameFor(model => model.MeterReadingEndDate)</dt> <dd>@Html.DisplayFor(model => model.MeterReadingEndDate)</dd> <dt>@Html.DisplayNameFor(model => model.BillingDays)</dt> <dd>@Html.DisplayFor(model => model.BillingDays)</dd> <dt>@Html.DisplayNameFor(model => model.CounterReadingStart)</dt> <dd>@Html.DisplayFor(model => model.CounterReadingStart)</dd> <dt>@Html.DisplayNameFor(model => model.CounterReadingEnd)</dt> <dd>@Html.DisplayFor(model => model.CounterReadingEnd)</dd> <dt>@Html.DisplayNameFor(model => model.TotalHCF)</dt> <dd>@Html.DisplayFor(model => model.TotalHCF)</dd> <dt>@Html.DisplayNameFor(model => model.TotalGallons)</dt> <dd>@Html.DisplayFor(model => model.TotalGallons)</dd> <dt>@Html.DisplayNameFor(model => model.First15HCF)</dt> <dd>@Html.DisplayFor(model => model.First15HCF)</dd> <dt>@Html.DisplayNameFor(model => model.Next10HCF)</dt> <dd>@Html.DisplayFor(model => model.Next10HCF)</dd> <dt>@Html.DisplayNameFor(model => model.RemainingHCF)</dt> <dd>@Html.DisplayFor(model => model.RemainingHCF)</dd> <dt>@Html.DisplayNameFor(model => model.SewerCharges)</dt> <dd>@Html.DisplayFor(model => model.SewerCharges)</dd> <dt>@Html.DisplayNameFor(model => model.StormCharges)</dt> <dd>@Html.DisplayFor(model => model.StormCharges)</dd> <dt>@Html.DisplayNameFor(model => model.WaterUsageCharges)</dt> <dd>@Html.DisplayFor(model => model.WaterUsageCharges)</dd> <dt>@Html.DisplayNameFor(model => model.TotalCharges)</dt> <dd>@Html.DisplayFor(model => model.TotalCharges)</dd> <dt>@Html.DisplayNameFor(model => model.LocalTaxes)</dt> <dd>@Html.DisplayFor(model => model.LocalTaxes)</dd> <dt>@Html.DisplayNameFor(model => model.StateTaxes)</dt> <dd>@Html.DisplayFor(model => model.StateTaxes)</dd> <dt>@Html.DisplayNameFor(model => model.PaymentDueDate)</dt> <dd>@Html.DisplayFor(model => model.PaymentDueDate)</dd> <dt>@Html.DisplayNameFor(model => model.AmountDue)</dt> <dd>@Html.DisplayFor(model => model.AmountDue)</dd> <dt>@Html.DisplayNameFor(model => model.LatePaymentDueDate)</dt> <dd>@Html.DisplayFor(model => model.LatePaymentDueDate)</dd> <dt>@Html.DisplayNameFor(model => model.LateAmountDue)</dt> <dd>@Html.DisplayFor(model => model.LateAmountDue)</dd> </dl> </div> <p class="text-center"> @Html.ActionLink("Edit/Update Water Bill Information", "Edit", new { id = Model.WaterBillID, @class = "water-nav" }, new { @class = "water-nav" }) | @Html.ActionLink("Water Meters", "Index", null, new { @class = "water-nav" }) </p>
|
|
||
| Home | Copyright © 2007-2019, FunctionX | Home |
|
|
||