Introduction to Data Joins
Introduction to Data Joins
Data Relationships Fundamentals
Introduction
A relational database is a system of two or more lists, such as tables, that share records. The relationship can be explicit or implicit. A data relationship is explicit if a clear relationship has been established between two tables. We already know how to explicitly create a relationship, which is done using primary keys and foreign keys. When an explicit relationship has been created, every time a value is created (or added) to a foreign key in the child table, that value must be found in the primary key of the parent table. Otherwise
USE master;
GO
CREATE DATABASE CollegeParkAutoRepair;
GO
USE CollegeParkAutoRepair;
GO
CREATE SEQUENCE ReceiptNumbers
AS INT
START WITH 100001
INCREMENT BY 1;
GO
CREATE TABLE RepairOrders
(
ReceiptNumber integer,
RepairDate nvarchar(50),
CustomerName nvarchar(60),
PhoneNumber nvarchar(32),
[Address] nvarchar(50),
City nvarchar(40),
[State] nvarchar(40),
ZIPCode nvarchar(20),
Make nvarchar(30),
Model nvarchar(32),
CarYear int,
ProblemDescription nvarchar(max),
TotalParts decimal(5, 2),
TotalLabor decimal(5, 2),
TaxRate decimal(5, 2) default 7.75,
TaxAmount decimal(5, 2),
OrderTotal decimal(5, 2),
Recommendations nvarchar(max),
CONSTRAINT PK_RepairOrders PRIMARY KEY(ReceiptNumber)
);
GO
CREATE TABLE PartsUsed
(
PartID int identity(1, 1) not null,
ReceiptNumber int not null,
PartName nvarchar(50) not null,
Price decimal(5, 2),
CONSTRAINT PK_PartsUsed PRIMARY KEY(PartID)
);
GO
CREATE TABLE JobsPerformed
(
JobID int identity(1, 1) not null,
ReceiptNumber int not null,
JobName nvarchar(80),
Cost decimal(5, 2),
CONSTRAINT PK_JobsPerformed PRIMARY KEY(JobID)
);
GO
INSERT INTO RepairOrders(ReceiptNumber, RepairDate, CustomerName, PhoneNumber, [Address], City, [State], ZIPCode, Make, Model, CarYear, ProblemDescription, TotalParts, TotalLabor, TaxRate, TaxAmount, OrderTotal, Recommendations)
VALUES(NEXT VALUE FOR ReceiptNumbers, N'12-05-2019', N'Harrold Duncan', N'202-620-5814', N'9246 Eulaw Drive N.W.', N'Washington', N'DC', N'20018', N'Honda', N'Accord', 2012, N'The customer requested tune up on the car.', 92.50, 70, 7.75, 12.62, 175.52, N'The whole tune up was done.');
GO
INSERT INTO PartsUsed(ReceiptNumber, PartName, Price)
VALUES(100001, N'Air Filter', 24.95),
(100001, N'Fuel Filter', 50.55),
(100001, N'Wire Set', 4.35 * 4);
GO
INSERT INTO JobsPerformed(ReceiptNumber, JobName, Cost)
VALUES(100001, N'Replaced the air filter, the fuel filter, and the spark plugs.', 70);
GO
INSERT INTO RepairOrders(ReceiptNumber, RepairDate, CustomerName, PhoneNumber, [Address], City, [State], ZIPCode, Make, Model, CarYear, ProblemDescription, TotalParts, TotalLabor, TaxRate, TaxAmount, OrderTotal, Recommendations)
VALUES(NEXT VALUE FOR ReceiptNumbers, N'01-10-2019', N'Eugenie Sanders', N'(301) 283-8074', N'3057 Daventry Rd', N'Upper Marlboro', N'MD', N'20772', N'Dodge', N'Sprinter 2500', 2014, N'The customer is complaining of a noise whenever she applies the brakes.', 159.50, 195, 7.75, 27.47, 381.97, N'The whole tune up was done.');
GO
INSERT INTO PartsUsed(ReceiptNumber, PartName, Price)
VALUES(100002, N'Replacement Front Brake Disc', 21.50),
(100002, N'Replacement Rear Brake Disc', 40.50),
(100002, N'Front Brake Pad Set', 32.35),
(100002, N'Rear Brake Pad Set', 65.15);
GO
INSERT INTO JobsPerformed(ReceiptNumber, JobName, Cost)
VALUES(100002, N'Changed the front and rear brakes.', 85),
(100002, N'Installed a new brake booster.', 110);
GO
, the value would be rejected.
An implicit relationship is one you make up or figure out. The relationship is not formally established between the tables but in one table (considered a child table), there is a field that represents the records of another table (considered the parent table).
A Common Field for a Relationship
Probably the most important aspect of a relationship between two tables is a field they share. This means that you don't have to formally establish a relationship between two tables. Once they share a column (both columns must have the same data type but they don't have to have the same name), the relationship is implicit. Here is an example that contains three tables that share a common 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:
<!DOCTYPE html>
<html>
<head>
<style>
.accent { font-weight: 600; }
.large { width: 300px; }
.large-x { width: 400px; }
.medium { width: 150px; }
.left-column { width: 150px; }
.right { text-align: right; }
.underline { border-bottom: 1px solid black; }
.underline2 { border-bottom: 2px solid black; }
.column { width: 50%;
float: left; }
.row { clear: both;
display: table;}
</style>
<title>College Park Auto-Repair</title>
</head>
<body>
@{
int receiptNumber = 0;
double subTotal = 0.00;
string year = string.Empty;
string city = string.Empty;
string make = string.Empty;
string state = string.Empty;
string model = string.Empty;
string address = string.Empty;
string ZIPCode = string.Empty;
string taxRate = string.Empty;
string taxAmount = string.Empty;
string totalParts = string.Empty;
string totalLabor = string.Empty;
string repairDate = string.Empty;
string phoneNumber = string.Empty;
string repairTotal = string.Empty;
string customerName = string.Empty;
string recommendations = string.Empty;
string problemDescription = string.Empty;
Dictionary<string, string> partsUsed = new Dictionary<string, string>();
Dictionary<string, string> jobsPerformed = new Dictionary<string, string>();
if (IsPost)
{
if (!string.IsNullOrEmpty(Request["txtReceiptNumber"].ToString()))
{
receiptNumber = Request["txtReceiptNumber"].AsInt();
using (System.Data.SqlClient.SqlConnection scCPAR =
new System.Data.SqlClient.SqlConnection("Data Source=(local);Integrated Security=SSPI;Initial Catalog='CollegeParkAutoRepair';"))
{
System.Data.SqlClient.SqlCommand cmdRepairOrders =
new System.Data.SqlClient.SqlCommand("SELECT RepairDate, CustomerName, PhoneNumber, Address, City, State, " +
" ZIPCode, Make, Model, CarYear, ProblemDescription, TotalParts, " +
" TotalLabor, TaxRate, TaxAmount, OrderTotal, Recommendations " +
"FROM RepairOrders " +
"WHERE ReceiptNumber = " + receiptNumber, scCPAR);
scCPAR.Open();
System.Data.SqlClient.SqlDataReader rdrRepairOrders = cmdRepairOrders.ExecuteReader();
while (rdrRepairOrders.Read())
{
repairDate = DateTime.Parse(rdrRepairOrders[0].ToString()).ToShortDateString();
customerName = Html.Encode(rdrRepairOrders[1].ToString());
phoneNumber = rdrRepairOrders[2].ToString();
address = rdrRepairOrders[3].ToString();
city = rdrRepairOrders[4].ToString();
state = rdrRepairOrders[5].ToString();
ZIPCode = rdrRepairOrders[6].ToString();
make = rdrRepairOrders[7].ToString();
model = rdrRepairOrders[8].ToString();
year = rdrRepairOrders[9].ToString();
problemDescription = rdrRepairOrders[10].ToString();
totalParts = rdrRepairOrders[11].ToString();
totalLabor = rdrRepairOrders[12].ToString();
taxRate = rdrRepairOrders[13].ToString();
taxAmount = rdrRepairOrders[14].ToString();
repairTotal = rdrRepairOrders[15].ToString();
recommendations = rdrRepairOrders[16].ToString();
subTotal = double.Parse(totalParts) + double.Parse(totalLabor);
}
}
using (System.Data.SqlClient.SqlConnection scCPAR =
new System.Data.SqlClient.SqlConnection("Data Source=(local);Integrated Security=SSPI;Initial Catalog='CollegeParkAutoRepair';"))
{
System.Data.SqlClient.SqlCommand cmdPartsUsed =
new System.Data.SqlClient.SqlCommand("SELECT PartName, Price " +
"FROM PartsUsed " +
"WHERE ReceiptNumber = " + receiptNumber, scCPAR);
scCPAR.Open();
System.Data.SqlClient.SqlDataReader rdrPartsUsed = cmdPartsUsed.ExecuteReader();
while (rdrPartsUsed.Read())
{
partsUsed.Add(rdrPartsUsed[0].ToString(), rdrPartsUsed[1].ToString());
}
}
using (System.Data.SqlClient.SqlConnection scCPAR =
new System.Data.SqlClient.SqlConnection("Data Source=(local);Integrated Security=SSPI;Initial Catalog='CollegeParkAutoRepair';"))
{
System.Data.SqlClient.SqlCommand cmdJobsPerformed =
new System.Data.SqlClient.SqlCommand("SELECT JobName, Cost " +
"FROM JobsPerformed " +
"WHERE ReceiptNumber = " + receiptNumber, scCPAR);
scCPAR.Open();
System.Data.SqlClient.SqlDataReader rdrJobsPerformed = cmdJobsPerformed.ExecuteReader();
while (rdrJobsPerformed.Read())
{
jobsPerformed.Add(rdrJobsPerformed[0].ToString(), rdrJobsPerformed[1].ToString());
}
}
}
}
}
<h1>College Park Auto-Repair</h1>
<form name="frmRepairOrderReview" method="post">
<table>
<tr>
<td class="left-column accent">Receipt #:</td>
<td><input type="text" name="txtReceiptNumber" value="@receiptNumber" />
<input type="submit" name="btnFind" value="Find" /></td>
</tr>
<tr>
<td class="accent">Repair Date:</td>
<td><input type="text" id="RepairDate" value=@repairDate /></td>
</tr>
</table>
<p>
<fieldset>
<legend class="accent">Customer and Vehicle Information</legend>
<table>
<tr>
<td class="left-column accent">Customer Name:</td>
<td class="underline large">@customerName</td>
<td class="accent">Phone #:</td>
<td class="underline">@phoneNumber</td>
</tr>
</table>
<table>
<tr>
<td class="left-column accent">Address:</td>
<td class="underline">@address</td>
</tr>
</table>
<table>
<tr>
<td class="left-column accent">City:</td>
<td class="underline medium">@city</td>
<td class="accent">State:</td>
<td class="underline">@state</td>
<td class="accent">ZIP Code:</td>
<td class="underline">@ZIPCode</td>
</tr>
<tr>
<td class="accent">Make:</td>
<td class="underline medium">@make</td>
<td class="accent">Model:</td>
<td class="underline">@model</td>
<td class="accent">Year:</td>
<td class="underline">@year</td>
</tr>
</table>
<table>
<tr>
<td class="left-column accent">Problem Description:</td>
<td><textarea id="ProblemDescription" cols="50" rows="3">@problemDescription</textarea></td>
</tr>
</table>
</fieldset>
<div class="row">
<div class="column">
<fieldset>
<legend class="accent">Parts Used</legend>
<table>
<tr>
<td class="accent large-x underline2">Part Name</td>
<td class="accent right">Price</td>
</tr>
@foreach (KeyValuePair<string, string> partUsed in partsUsed)
{
<tr>
<td class="underline">@partUsed.Key</td>
<td class="underline right">@partUsed.Value</td>
</tr>
}
</table>
</fieldset>
</div>
<div class="column">
<fieldset>
<legend class="accent">Jobs Performed</legend>
<table>
<tr>
<td class="underline2 accent large-x">Job Performed</td>
<td class="underline2 accent right">Cost</td>
</tr>
@foreach (KeyValuePair<string, string> jobPerformed in jobsPerformed)
{
<tr>
<td class="underline">@jobPerformed.Key</td>
<td class="underline right">@jobPerformed.Value</td>
</tr>
}
</table>
</fieldset>
</div>
</div>
<div class="row">
<div class="column">
<fieldset>
<legend class="accent">Repair Summary</legend>
<table>
<tr>
<td class="accent underline">Total Parts:</td>
<td class="underline right">@totalParts</td>
<td style="width: 80px"> </td>
<td class="accent underline">Tax Rate:</td>
<td class="right underline">@taxRate</td>
</tr>
<tr>
<td class="accent underline">Total Labor:</td>
<td class="right underline">@totalLabor</td>
<td> </td>
<td class="right accent underline">Tax Amount:</td>
<td class="right underline">@taxAmount</td>
</tr>
<tr>
<td class="accent underline">Sub-Total:</td>
<td class="right underline">@subTotal</td>
<td> </td>
<td class="accent underline">Repair Total:</td>
<td class="right underline">@repairTotal</td>
</tr>
</table>
</fieldset>
</div>
<div class="column">
<fieldset>
<legend class="accent">Recommendations</legend>
<textarea id="ProblemDescription" cols="50" rows="5">@recommendations</textarea>
</fieldset>
</div>
</div>
</form>
</body>
</html>
Practical Learning: Introducing Joins
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), InvoiceNumber INT UNIQUE, 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"));
}
}
}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><!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("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("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> 
| Meter # | Make | Model | Meter Size | Date Last Update | Counter Value |
| 392-44-572 | Constance Technologies | TG-4822 | 5/8 Inches | 07/02/2019 | 109992 |
| 938-75-869 | Standard Trend | 266G | 1 1/2 Inches | 07/05/2019 | 137926 |
| 799-28-461 | Constance Technologies | BD-7000 | 3/4 Inches | 7/5/2019 | 6268 |
| 207-94-835 | Constance Technologies | TG-6220 | 5/8 Inches | 7/2/2019 | 96 |
| 592-84-957 | Standard Trend | 428T | 3/4 Inches | 9/3/2019 | 49 |
INSERT WaterMeters(MeterNumber, Make, Model, MeterSize, DateLastUpdate, CounterValue)
VALUES(N'392-44-572', N'Constance Technologies', N'TG-4822', N'5/8 Inches', N'07/02/2019', 109992),
(N'938-75-869', N'Standard Trend', N'266G', N'1 1/2 Inches', N'07/05/2019', 137926),
(N'799-28-461', N'Constance Technologies', N'BD-7000', N'3/4 Inches', N'7/5/2019', 6268),
(N'207-94-835', N'Constance Technologies', N'TG-6220', N'5/8 Inches', N'7/2/2019', 96),
(N'592-84-957', N'Standard Trend', N'428T', N'3/4 Inches', N'9/3/2019', 49);
GOusing 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; }
}
}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, 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 |
INSERT Customers(AccountNumber, WaterMeterID, FirstName, LastName, [Address], City, County, [State], ZIPCode)
VALUES(N'9279-570-8394', 3, N'Thomas', N'Stones', N'10252 Broward Ave #D4', N'Frederick', N'Frederick', N'MD', N'21703'),
(N'4820-375-2842', 2, N'Akhil', N'Koumari', N'748 Red Hills Rd', N'Roanoke', NULL, N'VA', N'24012'),
(N'7518-302-6895', 4, N'Grace', N'Brenner', N'4299 Peachtree Court', N'Rockville', N'Montgomery', N'MD', N'20853'),
(N'2038-413-9680', 1, N'Amidou', N'Gomah', N'2075 Rose Hills Ave', N'Washington', NULL, N'DC', N'20004'),
(N'5938-074-5293', 5, N'Marie', N'Rath', N'582G Dunhill Ave', N'Lanham', N'Prince Georges', N'MD', N'20706');
GOusing 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, LocalTaxes, " +
" 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.LocalTaxes = 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, LocalTaxes, " +
" 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.LocalTaxes = 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 LocalTaxes = totalCharges * 0.005M;
decimal stateTaxes = totalCharges * 0.0152M;
decimal amountDue = totalCharges + LocalTaxes + 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 = LocalTaxes.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, LocalTaxes, 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["LocalTaxes"] + ", " + 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", "StartMeterReading", 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.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>@{
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="LocalTaxes" class="control-label col-md-125 caption">Local Taxes:</label>
<div class="col-md-125">
@Html.TextBox("LocalTaxes", ViewBag.LocalTaxes as string,
htmlAttributes: new { @class = "form-control", id = "LocalTaxes" })
</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/2019 | 114 |
| 4820-375-2842 | 07/31/2019 | 109998 |
| 2038-413-9680 | 07/30/2019 | 137975 |
| 9279-570-8394 | 7-Aug-19 | 6275 |
| 7518-302-6895 | 7-Nov-19 | 118 |
| 2038-413-9680 | 27-Oct-19 | 137958 |
| 9279-570-8394 | 3-Nov-19 | 6295 |
| 4820-375-2842 | 29-Oct-19 | 110012 |
| 9279-570-8394 | 2/2/2019 | 6312 |
| 7518-302-6895 | 2/19/2019 | 126 |
| 5938-074-5293 | 10/2/2019 | 52 |
| 4820-375-2842 | 2/2/2019 | 110027 |
| 2038-413-9680 | 1/28/2019 | 138054 |
Joins Fundamentals
Introduction
A data join is a technique of creating a list of records from more than one table, using all columns from all tables involved, or selecting only the desired columns from one or all of the tables involved. This means that a data join is essentially created in three steps:
Using a Shared Field to Join Tables
One of the most important features of a relational database consists of combining records from various tables to get a single list. The SQL provides two main options: Applying a condition on a common field or creating a join.
The primary way to join two or more tables to create a common list that combines their records is to match the records they have in common. Before doing this, the lists must have a field used as the primary key on one table and a foreign key on the other table. The formula to follow is:
SELECT field(s) [, field(s)] FROM table_1, table_2 WHERE condition
Create a SELECT statement to select fields from one or all tables. Then use a WHERE condition to specify how the records will be matched. Here is an example:
CREATE TABLE Departments
(
DeptCode varchar(7),
DepartmentName varchar(50)
);
GO
CREATE TABLE Employees
(
EmployeeNumber varchar(10),
FirstName varchar(24),
LastName varchar(24),
DeptCode varchar(7),
);
GO
INSERT INTO Departments
VALUES(N'HMNRS', N'Human Resources'),
(N'ITECH', N'Information Technology'),
(N'RESDV', N'Research and Development');
GO
INSERT INTO Employees
VALUES(N'937-497', N'Frank', N'Salomons', N'ITECH'),
(N'284-957', N'Chrissie', N'Siegel', NULL),
(N'204-805', N'Violet', N'Calhoun', N'HMNRS'),
(N'927-394', N'James', N'Waller', N'RESDV'),
(N'294-793', N'John', N'Harding', N'HMNRS'),
(N'920-794', N'Joan', N'Thomason', NULL),
(N'395-004', N'Alexander', N'Lamm', N'HMNRS'),
(N'305-804', N'Anna', N'Danielson', NULL);
GO
SELECT EmployeeNumber,
FirstName,
LastName,
DepartmentName
FROM Employees, Departments
WHERE Employees.DeptCode = Departments.DeptCode;
GO
Practical Learning: Using a Shared Field to Join Tables
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, " +
" 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>
Introduction to Conditional Joins
When creating a join, if you are using a WHERE clause, you can add one or more conditions to select only the necessary records.
Practical Learning: Introducing Conditional Joins
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>
|
|
||
| Previous | Copyright © 2007-2021, FunctionX | Next |
|
|
||