Sample Application: Apartment Rental Management
Sample Application: Apartment Rental Management
Introduction
In this exercise, we will create a web-based database application. It is for a fictitious company that owns an apartment building and rents those apartments to tenants.
Practical Learning: Introducing the Application
USE master; GO CREATE DATABASE ApartmentsRentalManagement; GO USE ApartmentsRentalManagement; GO
CREATE SCHEMA Management; GO CREATE SCHEMA HumanResources; GO CREATE TABLE HumanResources.Employees ( EmployeeID INT IDENTITY(1, 1), EmployeeNumber NVARCHAR(10), FirstName NVARCHAR(20), LastName NVARCHAR(20), EmploymentTitle NVARCHAR(50), CONSTRAINT PK_Employees PRIMARY KEY(EmployeeID) ); GO CREATE TABLE Management.Apartments ( ApartmentID INT IDENTITY(1, 1), UnitNumber NVARCHAR(5), Bedrooms TINYINT, Bathrooms TINYINT, MonthlyRate INT, SecurityDeposit INT, OccupancyStatus NVARCHAR(25), CONSTRAINT PK_Apartments PRIMARY KEY(ApartmentID) ); GO CREATE TABLE Management.RentalContracts ( RentalContractID INT IDENTITY(1, 1), ContractNumber INT, EmployeeID INT, ContractDate DATE, FirstName NVARCHAR(20), LastName NVARCHAR(20), MaritalStatus NVARCHAR(25), NumberOfChildren TINYINT, ApartmentID INT, RentStartDate DATE, CONSTRAINT PK_RentalContracts PRIMARY KEY(RentalContractID), CONSTRAINT FK_Registrars FOREIGN KEY(EmployeeID) REFERENCES HumanResources.Employees(EmployeeID), CONSTRAINT FK_Apartments FOREIGN KEY(ApartmentID) REFERENCES Management.Apartments(ApartmentID) ); GO CREATE TABLE Management.Payments ( PaymentID INT IDENTITY(1, 1), ReceiptNumber INT, EmployeeID INT, RentalContractID INT, PaymentDate DATE, Amount INT, Notes NVARCHAR(MAX), CONSTRAINT PK_Payments PRIMARY KEY(PaymentID), CONSTRAINT FK_ProcessedBy FOREIGN KEY(EmployeeID) REFERENCES HumanResources.Employees(EmployeeID), CONSTRAINT FK_Contracts FOREIGN KEY(RentalContractID) REFERENCES Management.RentalContracts(RentalContractID) ); 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="csApartmentsRentalManagement"
connectionString="Data Source=(local); Database='ApartmentsRentalManagement'; 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="csApartmentsRentalManagement"
connectionString="Data Source=(LocalDb)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\ApartmentsRentalManagement.mdf;Initial Catalog=ApartmentsRentalManagement;Integrated Security=True"
providerName="System.Data.SqlClient" />
</connectionStrings>
. . . No Change
</configuration>
body {
background-color: #FFF;
}
.bold { font-weight: 600; }
.blue { color: #286090; }
.top-padding { padding-top: 0.50em; }
.top-banner { top: 0;
left: 0;
right: 0;
z-index: 1050;
height: 20em;
position: fixed;
background-image: url(/Images/arm1.png); }
.containment { margin: auto;
width: 400px; }
.jumbotron { background-color: white; }
.form-control { color: #286090; }
.form-control:focus { color: dodgerblue; }
.navbar-top-fixed { left: 0;
right: 0;
top: 20em;
z-index: 1100;
position: fixed;
border-width: 0 0 1px; }
.common-font { font-family: Georgia, Garamond, 'Times New Roman', serif; }
.navbar-top-fixed .navbar-collapse { max-height: 340px; }
@media (min-width: 768px) {
.navbar-top-fixed .navbar-collapse {
padding-right: 0;
padding-left: 0;
}
}
@media (max-device-width: 480px) and (orientation: landscape) {
.navbar-top-fixed .navbar-collapse {
max-height: 200px;
}
}
@media (min-width: 768px) {
.navbar-top-fixed {
border-radius: 0;
}
}using System.Web.Optimization;
namespace ApartmentsRentalManagement1
{
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/RentManagement.css"));
}
}
}<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Apartment Rental Management :: @ViewBag.Title</title>
@Styles.Render("~/Content/css")
@Scripts.Render("~/bundles/modernizr")
</head>
<body>
<div class="top-banner"></div>
<div class="navbar navbar-inverse navbar-top-fixed">
<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("HOME", "Index", "Home", new { area = "" }, new { @class = "navbar-brand" })
</div>
<div class="navbar-collapse collapse">
<ul class="nav navbar-nav">
<li>@Html.ActionLink("LEASE APPLICATION", "Index", "Home")</li>
<li>@Html.ActionLink("COMMUNITY", "Index", "Home")</li>
<li>@Html.ActionLink("FLOOR PLANS", "Index", "Home")</li>
<li>@Html.ActionLink("CAREERS", "Index", "Home")</li>
<li>@Html.ActionLink("RENT MANAGEMENT", "Index", "RentalContracts")</li>
<li>@Html.ActionLink("ABOUT ARM", "About", "Home")</li>
<li>@Html.ActionLink("CONTACT US", "Contact", "Home")</li>
</ul>
</div>
</div>
</div>
<div class="container body-content">
@RenderBody()
<hr />
<footer>
<p class="text-center common-font blue">© @DateTime.Now.Year - Apartment Rental Management</p>
</footer>
</div>
@Scripts.Render("~/bundles/jquery")
@Scripts.Render("~/bundles/bootstrap")
@RenderSection("scripts", required: false)
</body>
</html>
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Apartment Rental Management :: @ViewBag.Title</title>
@Styles.Render("~/Content/css")
@Scripts.Render("~/bundles/modernizr")
</head>
<body>
<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("Apartment Rental Management", "Index", "Home", new { area = "" }, new { @class = "navbar-brand" })
</div>
<div class="navbar-collapse collapse">
<ul class="nav navbar-nav">
<li>@Html.ActionLink("Home", "Index", "Home")</li>
<li>@Html.ActionLink("Employees", "Index", "Employees")</li>
<li>@Html.ActionLink("Apartments", "Index", "Apartments")</li>
<li>@Html.ActionLink("Rent Contracts", "Index", "RentalContracts")</li>
<li>@Html.ActionLink("Payments", "Index", "Payments")</li>
<li>@Html.ActionLink("About ARM", "About", "Home")</li>
<li>@Html.ActionLink("Contact Us", "Contact", "Home")</li>
</ul>
</div>
</div>
</div>
<div class="container body-content">
@RenderBody()
<hr />
<footer>
<p class="text-center common-font blue">© @DateTime.Now.Year - Apartment Rental Management</p>
</footer>
</div>
@Scripts.Render("~/bundles/jquery")
@Scripts.Render("~/bundles/bootstrap")
@RenderSection("scripts", required: false)
</body>
</html>@{
ViewBag.Title = "Home Page";
}
<div class="jumbotron">
<h1>ASP.NET</h1>
<p class="lead">ASP.NET is a free web framework for building great Web sites and Web applications using HTML, CSS and JavaScript.</p>
</div>
<div class="row">
<div class="col-md-4">
<h2>Getting started</h2>
<p>
ASP.NET MVC gives you a powerful, patterns-based way to build dynamic websites that
enables a clean separation of concerns and gives you full control over markup
for enjoyable, agile development.
</p>
<p><a class="btn btn-default" href="https://go.microsoft.com/fwlink/?LinkId=301865">Learn more »</a></p>
</div>
<div class="col-md-4">
<h2>Get more libraries</h2>
<p>NuGet is a free Visual Studio extension that makes it easy to add, remove, and update libraries and tools in Visual Studio projects.</p>
<p><a class="btn btn-default" href="https://go.microsoft.com/fwlink/?LinkId=301866">Learn more »</a></p>
</div>
<div class="col-md-4">
<h2>Web Hosting</h2>
<p>You can easily find a web hosting company that offers the right mix of features and price for your applications.</p>
<p><a class="btn btn-default" href="https://go.microsoft.com/fwlink/?LinkId=301867">Learn more »</a></p>
</div>
</div>Employees
As is the case in every business, employees take care of daily interactions with potential customers and current tenants. Our database uses a table of employees for that purpose.
Practical Learning: Creating Employees
using System.ComponentModel.DataAnnotations;
namespace ApartmentsRentalManagement1.Models
{
public class Employee
{
[Display(Name = "Employee ID")]
public int EmployeeID { get; set; }
[Display(Name = "Employee #")]
public string EmployeeNumber { get; set; }
[Display(Name = "First Name")]
public string FirstName { get; set; }
[Display(Name = "Last Name")]
public string LastName { get; set; }
[Display(Name = "Employment Title")]
public string EmploymentTitle { get; set; }
public string Identification
{
get
{
return EmployeeNumber + " - " + FirstName + " " + LastName + " (" + EmploymentTitle + ")";
}
}
}
}using System; using System.Data; using System.Data.SqlClient; using System.Collections.Generic; namespace ApartmentsRentalManagement1.Models { public class BusinessObjects { public List<Employee> GetEmployees() { List<Employee> employees = new List<Employee>(); using (SqlConnection scApartmentsManagement = new SqlConnection(System. Configuration. ConfigurationManager. ConnectionStrings["csApartmentsRentalManagement"]. ConnectionString)) { SqlCommand cmdEmployees = new SqlCommand("SELECT EmployeeID, EmployeeNumber, " + " FirstName, LastName, EmploymentTitle " + "FROM HumanResources.Employees;", scApartmentsManagement); scApartmentsManagement.Open(); cmdEmployees.ExecuteNonQuery(); SqlDataAdapter sdaEmployees = new SqlDataAdapter(cmdEmployees); DataSet dsEmployees = new DataSet("employees"); sdaEmployees.Fill(dsEmployees); Employee staff = null; for (int i = 0; i < dsEmployees.Tables[0].Rows.Count; i++) { DataRow drEmployee = dsEmployees.Tables[0].Rows[i]; staff = new Employee() { EmployeeID = int.Parse(drEmployee[0].ToString()), EmployeeNumber = drEmployee[1].ToString(), FirstName = drEmployee[2].ToString(), LastName = drEmployee[3].ToString(), EmploymentTitle = drEmployee[4].ToString() }; employees.Add(staff); } } return employees; } public Employee FindEmployee(int? id) { Employee employee = null; foreach (var staff in GetEmployees()) { if (staff.EmployeeID == id) { employee = staff; break; } } return employee; } } }
using System.Net; using System.Web.Mvc; using System.Data.SqlClient; using ApartmentsRentalManagement1.Models; namespace ApartmentsRentalManagement1.Controllers { public class EmployeesController : Controller { private BusinessObjects objects = new BusinessObjects(); // GET: Employees public ActionResult Index() { return View(objects.GetEmployees()); } // GET: Employees/Details/5 public ActionResult Details(int id) { if (id == 0) { return new HttpStatusCodeResult(HttpStatusCode.BadRequest); } Employee employee = objects.FindEmployee(id); if (employee == null) { return HttpNotFound(); } return View(employee); } // GET: Employees/Create public ActionResult Create() { return View(); } // POST: Employees/Create [HttpPost] public ActionResult Create(FormCollection collection) { try { // TODO: Add insert logic here using (SqlConnection scRentManagement = new SqlConnection(System.Configuration. ConfigurationManager. ConnectionStrings["csApartmentsRentalManagement"]. ConnectionString)) { SqlCommand cmdEmployees = new SqlCommand("INSERT INTO HumanResources.Employees(EmployeeNumber, FirstName, LastName, EmploymentTitle) " + "VALUES(N'" + collection["EmployeeNumber"] + "', " + " N'" + collection["FirstName"] + "', " + " N'" + collection["LastName"] + "', " + " N'" + collection["EmploymentTitle"] + "');", scRentManagement); scRentManagement.Open(); cmdEmployees.ExecuteNonQuery(); } return RedirectToAction("Index"); } catch { return View(); } } // GET: Employees/Edit/5 public ActionResult Edit(int id) { if (id == 0) { return new HttpStatusCodeResult(HttpStatusCode.BadRequest); } Employee employee = objects.FindEmployee(id); if (employee == null) { return HttpNotFound(); } return View(employee); } // POST: Employees/Edit/5 [HttpPost] public ActionResult Edit(int id, FormCollection collection) { try { // TODO: Add update logic here using (SqlConnection scRentManagement = new SqlConnection(System.Configuration. ConfigurationManager. ConnectionStrings["csApartmentsRentalManagement"]. ConnectionString)) { SqlCommand cmdEmployees = new SqlCommand("UPDATE HumanResources.Employees " + "SET EmployeeNumber = N'" + collection["EmployeeNumber"] + "', " + " FirstName = N'" + collection["FirstName"] + "', " + " LastName = N'" + collection["LastName"] + "', " + " EmploymentTitle = N'" + collection["EmploymentTitle"] + "' " + "WHERE EmployeeID = " + id + ";", scRentManagement); scRentManagement.Open(); cmdEmployees.ExecuteNonQuery(); } return RedirectToAction("Index"); } catch { return View(); } } // GET: Employees/Delete/5 public ActionResult Delete(int id) { if (id == 0) { return new HttpStatusCodeResult(HttpStatusCode.BadRequest); } Employee employee = objects.FindEmployee(id); if (employee == null) { return HttpNotFound(); } return View(employee); } // POST: Employees/Delete/5 [HttpPost] public ActionResult Delete(int id, FormCollection collection) { try { // TODO: Add delete logic here using (SqlConnection scRentManagement = new SqlConnection(System.Configuration. ConfigurationManager. ConnectionStrings["csApartmentsRentalManagement"]. ConnectionString)) { SqlCommand cmdEmployees = new SqlCommand("DELETE FROM HumanResources.Employees " + "WHERE EmployeeID = " + id + ";", scRentManagement); scRentManagement.Open(); cmdEmployees.ExecuteNonQuery(); } return RedirectToAction("Index"); } catch { return View(); } } } }
@model IEnumerable<ApartmentsRentalManagement2.Models.Employee>
@{
ViewBag.Title = "Employees";
Layout = "~/Views/Shared/_Management.cshtml";
}
<h2 class="bold blue common-font text-center">Employees - Staff Members</h2>
<table class="table table-hover common-font">
<tr>
<th>@Html.DisplayNameFor(model => model.EmployeeID)</th>
<th>@Html.DisplayNameFor(model => model.EmployeeNumber)</th>
<th>@Html.DisplayNameFor(model => model.FirstName)</th>
<th>@Html.DisplayNameFor(model => model.LastName)</th>
<th>@Html.DisplayNameFor(model => model.EmploymentTitle)</th>
<th>@Html.ActionLink("Hire New Employee", "Create")</th>
</tr>
@foreach (var item in Model)
{
<tr>
<td class="text-center">@Html.DisplayFor(modelItem => item.EmployeeID)</td>
<td>@Html.DisplayFor(modelItem => item.EmployeeNumber)</td>
<td>@Html.DisplayFor(modelItem => item.FirstName)</td>
<td>@Html.DisplayFor(modelItem => item.LastName)</td>
<td>@Html.DisplayFor(modelItem => item.EmploymentTitle)</td>
<td>
@Html.ActionLink("Edit", "Edit", new { id = item.EmployeeID }) ::
@Html.ActionLink("Details", "Details", new { id = item.EmployeeID }) ::
@Html.ActionLink("Delete", "Delete", new { id = item.EmployeeID })
</td>
</tr>
}
</table>@model ApartmentsRentalManagement2.Models.Employee
@{
ViewBag.Title = "Employee Details";
Layout = "~/Views/Shared/_Management.cshtml";
}
<h2 class="bold blue common-font text-center">Employee Details</h2>
<hr />
<div class="containment">
<dl class="dl-horizontal common-font">
<dt>@Html.DisplayNameFor(model => model.EmployeeID)</dt>
<dd>@Html.DisplayFor(model => model.EmployeeID)</dd>
<dt>@Html.DisplayNameFor(model => model.EmployeeNumber)</dt>
<dd>@Html.DisplayFor(model => model.EmployeeNumber)</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.EmploymentTitle)</dt>
<dd>@Html.DisplayFor(model => model.EmploymentTitle)</dd>
</dl>
</div>
<p class="text-center">
@Html.ActionLink("Edit/Update Employee Record", "Edit", new { id = Model.EmployeeID }) ::
@Html.ActionLink("Delete Employee Record", "Delete", new { id = Model.EmployeeID }) ::
@Html.ActionLink("Employees", "Index")
</p>@model ApartmentsRentalManagement2.Models.Employee
@{
ViewBag.Title = "Employment Application";
Layout = "~/Views/Shared/_Management.cshtml";
}
<h2 class="bold blue common-font text-center">Employment Application</h2>
<hr />
@using (Html.BeginForm())
{
<div class="containment">
<div class="form-horizontal common-font">
<div class="form-group">
@Html.LabelFor(model => model.EmployeeNumber, htmlAttributes: new { @class = "control-label col-md-5 blue" })
<div class="col-md-7">
@Html.TextBox("EmployeeNumber", null, htmlAttributes: new { @class = "form-control", id = "emplNbr" })
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.FirstName, htmlAttributes: new { @class = "control-label col-md-5 blue" })
<div class="col-md-7">
@Html.TextBox("FirstName", null, htmlAttributes: new { @class = "form-control", id = "fName" })
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.LastName, htmlAttributes: new { @class = "control-label col-md-5 blue" })
<div class="col-md-7">
@Html.TextBox("LastName", null, htmlAttributes: new { @class = "form-control", id = "lName" })
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.EmploymentTitle, htmlAttributes: new { @class = "control-label col-md-5 blue" })
<div class="col-md-7">
@Html.TextBox("EmploymentTitle", null, htmlAttributes: new { @class = "form-control", id = "title" })
</div>
</div>
<div class="form-group">
<label class="control-label col-md-5">@Html.ActionLink("Employees", "Index")</label>
<div class="col-md-7">
<input type="submit" value="Hire this Employee" class="btn btn-primary" />
</div>
</div>
</div>
</div>
}@model ApartmentsRentalManagement2.Models.Employee
@{
Layout = "~/Views/Shared/_Management.cshtml";
ViewBag.Title = "Edit/Update Employee Information";
}
<h2 class=" bold blue common-font text-center">Edit/Update Employee Information</h2>
<hr />
@using (Html.BeginForm())
{
<div class="containment">
<div class="form-horizontal common-font">
<div class="form-group">
@Html.LabelFor(model => model.EmployeeNumber, htmlAttributes: new { @class = "control-label col-md-5 blue" })
<div class="col-md-7">
@Html.EditorFor(model => model.EmployeeNumber, new { htmlAttributes = new { @class = "form-control" } })
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.FirstName, htmlAttributes: new { @class = "control-label col-md-5 blue" })
<div class="col-md-7">
@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-5 blue" })
<div class="col-md-7">
@Html.EditorFor(model => model.LastName, new { htmlAttributes = new { @class = "form-control" } })
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.EmploymentTitle, htmlAttributes: new { @class = "control-label col-md-5 blue" })
<div class="col-md-7">
@Html.EditorFor(model => model.EmploymentTitle, new { htmlAttributes = new { @class = "form-control" } })
</div>
</div>
<div class="form-group">
<label class="control-label col-md-5">@Html.ActionLink("Employees", "Index")</label>
<div class="col-md-7">
<input type="submit" value="Update Employee Record" class="btn btn-primary" />
</div>
</div>
</div>
</div>
}@model ApartmentsRentalManagement2.Models.Employee
@{
ViewBag.Title = "Deleting Employee Record";
Layout = "~/Views/Shared/_Management.cshtml";
}
<h2 class="bold common-font blue text-center">Deleting Employee Record</h2>
<hr />
<h3 class="common-font blue text-center">Are you sure you want to let this employee go?</h3>
<div class="containment">
<dl class="dl-horizontal common-font">
<dt>@Html.DisplayNameFor(model => model.EmployeeID)</dt>
<dd>@Html.DisplayFor(model => model.EmployeeID)</dd>
<dt>@Html.DisplayNameFor(model => model.EmployeeNumber)</dt>
<dd>@Html.DisplayFor(model => model.EmployeeNumber)</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.EmploymentTitle)</dt>
<dd>@Html.DisplayFor(model => model.EmploymentTitle)</dd>
</dl>
@using (Html.BeginForm())
{
<div class="form-actions no-color">
@Html.ActionLink("Employees", "Index") ::
<input type="submit" value="Let this Employee Leave" class="btn btn-primary" />
</div>
}
</div>Apartments
Apartments are the main objects of our business. We will use a table to keep track of their description and status.
Practical Learning: Creating Apartments
using System.ComponentModel.DataAnnotations;
namespace ApartmentsRentalManagement1.Models
{
public class Apartment
{
[Display(Name = "Apartment ID")]
public int ApartmentID { get; set; }
[Display(Name = "Unit #")]
public string UnitNumber { get; set; }
public int Bedrooms { get; set; }
public int Bathrooms { get; set; }
[Display(Name = "Monthly Rate")]
public int MonthlyRate { get; set; }
[Display(Name = "Deposit")]
public int SecurityDeposit { get; set; }
[Display(Name = "Occupancy Status")]
public string OccupancyStatus { get; set; }
public string Residence
{
get
{
string beds = Bedrooms + " bedrooms";
string baths = Bathrooms + " bathrooms";
if (Bedrooms == 1)
beds = Bedrooms + " bedroom";
if (Bathrooms == 1)
baths = Bedrooms + " bathroom";
return UnitNumber + " - " + beds + ", " + baths + ", rent = " +
MonthlyRate.ToString() + "/month, deposit = " +
SecurityDeposit.ToString() + ", " + OccupancyStatus;
}
}
}
}using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
namespace ApartmentsRentalManagement1.Models
{
public class BusinessObjects
{
. . . No Change
public List<Apartment> GetApartments()
{
List<Apartment> apartments = new List<Apartment>();
using (SqlConnection scApartmentsManagement = new SqlConnection(System.
Configuration.
ConfigurationManager.
ConnectionStrings["csApartmentsRentalManagement"].
ConnectionString))
{
SqlCommand cmdApartments = new SqlCommand("SELECT ApartmentID, UnitNumber, Bedrooms, " +
" Bathrooms, MonthlyRate, " +
" SecurityDeposit, OccupancyStatus " +
"FROM Management.Apartments;",
scApartmentsManagement);
scApartmentsManagement.Open();
cmdApartments.ExecuteNonQuery();
SqlDataAdapter sdaApartments = new SqlDataAdapter(cmdApartments);
DataSet dsApartments = new DataSet("apartments");
sdaApartments.Fill(dsApartments);
for (int i = 0; i < dsApartments.Tables[0].Rows.Count; i++)
{
DataRow drApartment = dsApartments.Tables[0].Rows[i];
Apartment unit = new Apartment()
{
ApartmentID = int.Parse(drApartment[0].ToString()),
UnitNumber = drApartment[1].ToString(),
Bedrooms = int.Parse(drApartment[2].ToString()),
Bathrooms = int.Parse(drApartment[3].ToString()),
MonthlyRate = int.Parse(drApartment[4].ToString()),
SecurityDeposit = int.Parse(drApartment[5].ToString()),
OccupancyStatus = drApartment[6].ToString()
};
apartments.Add(unit);
}
}
return apartments;
}
. . . No Change
}
}using System.Net; using System.Data; using System.Web.Mvc; using System.Data.SqlClient; using System.Collections.Generic; using ApartmentsRentalManagement1.Models; namespace ApartmentsRentalManagement1.Controllers { public class ApartmentsController : Controller { BusinessObjects objects = new BusinessObjects(); // GET: Apartments public ActionResult Index() { List<Apartment> apartments = objects.GetApartments(); return View(apartments); } // GET: Apartments/Details/5 public ActionResult Details(int id) { Apartment residence = null; using (SqlConnection scApartmentsManagement = new SqlConnection(System. Configuration. ConfigurationManager. ConnectionStrings["csApartmentsRentalManagement"]. ConnectionString)) { SqlCommand cmdApartments = new SqlCommand("SELECT ApartmentID, UnitNumber, Bedrooms, " + " Bathrooms, MonthlyRate, " + " SecurityDeposit, OccupancyStatus " + "FROM Management.Apartments " + "WHERE ApartmentID = " + id + ";", scApartmentsManagement); scApartmentsManagement.Open(); cmdApartments.ExecuteNonQuery(); SqlDataAdapter sdaApartments = new SqlDataAdapter(cmdApartments); DataSet dsApartments = new DataSet("apartment"); sdaApartments.Fill(dsApartments); if (dsApartments.Tables[0].Rows.Count > 0) { residence = new Apartment() { ApartmentID = int.Parse(dsApartments.Tables[0].Rows[0][0].ToString()), UnitNumber = dsApartments.Tables[0].Rows[0][1].ToString(), Bedrooms = int.Parse(dsApartments.Tables[0].Rows[0][2].ToString()), Bathrooms = int.Parse(dsApartments.Tables[0].Rows[0][3].ToString()), MonthlyRate = int.Parse(dsApartments.Tables[0].Rows[0][4].ToString()), SecurityDeposit = int.Parse(dsApartments.Tables[0].Rows[0][5].ToString()), OccupancyStatus = dsApartments.Tables[0].Rows[0][6].ToString() }; } } return View(residence); } // GET: Apartments/Create public ActionResult Create() { List<SelectListItem> conditions = new List<SelectListItem>(); conditions.Add(new SelectListItem() { Text = "Unknown", Value = "Unknown" }); conditions.Add(new SelectListItem() { Text = "Occupied", Value = "Occupied" }); conditions.Add(new SelectListItem() { Text = "Available", Value = "Available" }); conditions.Add(new SelectListItem() { Text = "Not Ready", Value = "Not Ready" }); conditions.Add(new SelectListItem() { Text = "Needs Maintenance", Value = "Needs Maintenance" }); ViewBag.OccupancyStatus = conditions; return View(); } // POST: Apartments/Create [HttpPost] public ActionResult Create(FormCollection collection) { try { // TODO: Add insert logic here using (SqlConnection scRentManagement = new SqlConnection(System.Configuration. ConfigurationManager. ConnectionStrings["csApartmentsRentalManagement"]. ConnectionString)) { SqlCommand cmdApartments = new SqlCommand("INSERT INTO Management.Apartments(UnitNumber, Bedrooms, Bathrooms, " + " MonthlyRate, SecurityDeposit, " + " OccupancyStatus) " + "VALUES(N'" + collection["UnitNumber"] + "', " + collection["Bedrooms"] + ", " + collection["Bathrooms"] + ", " + collection["MonthlyRate"] + ", " + collection["SecurityDeposit"] + ", N'" + collection["OccupancyStatus"] + "');", scRentManagement); scRentManagement.Open(); cmdApartments.ExecuteNonQuery(); } return RedirectToAction("Index"); } catch { return View(); } } // GET: Apartments/Edit/5 public ActionResult Edit(int id) { Apartment residence = null; using (SqlConnection scApartmentsManagement = new SqlConnection(System. Configuration. ConfigurationManager. ConnectionStrings["csApartmentsRentalManagement"]. ConnectionString)) { SqlCommand cmdApartments = new SqlCommand("SELECT ApartmentID, UnitNumber, Bedrooms, " + " Bathrooms, MonthlyRate, " + " SecurityDeposit, OccupancyStatus " + "FROM Management.Apartments " + "WHERE ApartmentID = " + id + ";", scApartmentsManagement); scApartmentsManagement.Open(); cmdApartments.ExecuteNonQuery(); SqlDataAdapter sdaApartments = new SqlDataAdapter(cmdApartments); DataSet dsApartments = new DataSet("apartment"); sdaApartments.Fill(dsApartments); if (dsApartments.Tables[0].Rows.Count > 0) { residence = new Apartment() { ApartmentID = int.Parse(dsApartments.Tables[0].Rows[0][0].ToString()), UnitNumber = dsApartments.Tables[0].Rows[0][1].ToString(), Bedrooms = int.Parse(dsApartments.Tables[0].Rows[0][2].ToString()), Bathrooms = int.Parse(dsApartments.Tables[0].Rows[0][3].ToString()), MonthlyRate = int.Parse(dsApartments.Tables[0].Rows[0][4].ToString()), SecurityDeposit = int.Parse(dsApartments.Tables[0].Rows[0][5].ToString()), OccupancyStatus = dsApartments.Tables[0].Rows[0][6].ToString() }; } } List<SelectListItem> conditions = new List<SelectListItem> { new SelectListItem() { Text = "Unknown", Value = "Unknown", Selected = (residence.OccupancyStatus == "Unknown") }, new SelectListItem() { Text = "Occupied", Value = "Occupied", Selected = (residence.OccupancyStatus == "Occupied") }, new SelectListItem() { Text = "Available", Value = "Available", Selected = (residence.OccupancyStatus == "Available") }, new SelectListItem() { Text = "Not Ready", Value = "Not Ready", Selected = (residence.OccupancyStatus == "Not Ready") }, new SelectListItem() { Text = "Needs Maintenance", Value = "Needs Maintenance", Selected = (residence.OccupancyStatus == "Needs Maintenance") } }; ViewBag.OccupancyStatus = conditions; return View(residence); } // POST: Apartments/Edit/5 [HttpPost] public ActionResult Edit(int id, FormCollection collection) { try { // TODO: Add update logic here using (SqlConnection scRentManagement = new SqlConnection(System.Configuration. ConfigurationManager. ConnectionStrings["csApartmentsRentalManagement"]. ConnectionString)) { SqlCommand cmdApartments = new SqlCommand("UPDATE Management.Apartments " + "SET UnitNumber = N'" + collection["UnitNumber"] + "', " + " Bedrooms = " + collection["Bedrooms"] + ", " + " Bathrooms = " + collection["Bathrooms"] + ", " + " MonthlyRate = " + collection["MonthlyRate"] + ", " + " SecurityDeposit = " + collection["SecurityDeposit"] + ", " + " OccupancyStatus = N'" + collection["OccupancyStatus"] + "' " + "WHERE ApartmentID = " + id + ";", scRentManagement); scRentManagement.Open(); cmdApartments.ExecuteNonQuery(); } return RedirectToAction("Index"); } catch { return View(); } } // GET: Apartments/Delete/5 public ActionResult Delete(int id) { Apartment residence = null; if (id == 0) { return new HttpStatusCodeResult(HttpStatusCode.BadRequest); } using (SqlConnection scRentManagement = new SqlConnection(System.Configuration. ConfigurationManager. ConnectionStrings["csApartmentsRentalManagement"]. ConnectionString)) { SqlCommand cmdApartments = new SqlCommand("SELECT ApartmentID, UnitNumber, Bedrooms, " + " Bathrooms, MonthlyRate, " + " SecurityDeposit, OccupancyStatus " + "FROM Management.Apartments " + "WHERE ApartmentID = " + id + ";", scRentManagement); scRentManagement.Open(); SqlDataAdapter sdaApartments = new SqlDataAdapter(cmdApartments); DataSet dsApartments = new DataSet("apartments"); sdaApartments.Fill(dsApartments); if (dsApartments.Tables[0].Rows.Count > 0) { residence = new Apartment() { ApartmentID = int.Parse(dsApartments.Tables[0].Rows[0][0].ToString()), UnitNumber = dsApartments.Tables[0].Rows[0][1].ToString(), Bedrooms = int.Parse(dsApartments.Tables[0].Rows[0][2].ToString()), Bathrooms = int.Parse(dsApartments.Tables[0].Rows[0][3].ToString()), MonthlyRate = int.Parse(dsApartments.Tables[0].Rows[0][4].ToString()), SecurityDeposit = int.Parse(dsApartments.Tables[0].Rows[0][5].ToString()), OccupancyStatus = dsApartments.Tables[0].Rows[0][6].ToString() }; } } return residence == null ? HttpNotFound() : (ActionResult)View(residence); } // POST: Apartments/Delete/5 [HttpPost] public ActionResult Delete(int id, FormCollection collection) { try { // TODO: Add delete logic here using (SqlConnection scRentManagement = new SqlConnection(System.Configuration. ConfigurationManager. ConnectionStrings["csApartmentsRentalManagement"]. ConnectionString)) { SqlCommand cmdApartments = new SqlCommand("DELETE Management.Apartments " + "WHERE ApartmentID = " + id + ";", scRentManagement); scRentManagement.Open(); cmdApartments.ExecuteNonQuery(); } return RedirectToAction("Index"); } catch { return View(); } } } }
@model IEnumerable<ApartmentsRentalManagement2.Models.Apartment>
@{
ViewBag.Title = "Apartments";
Layout = "~/Views/Shared/_Management.cshtml";
}
<h2 class="bold blue common-font text-center">Apartments</h2>
<table class="table table-hover common-font">
<tr>
<th>@Html.DisplayNameFor(model => model.ApartmentID)</th>
<th>@Html.DisplayNameFor(model => model.UnitNumber)</th>
<th>@Html.DisplayNameFor(model => model.Bedrooms)</th>
<th>@Html.DisplayNameFor(model => model.Bathrooms)</th>
<th>@Html.DisplayNameFor(model => model.MonthlyRate)</th>
<th>@Html.DisplayNameFor(model => model.SecurityDeposit)</th>
<th>@Html.DisplayNameFor(model => model.OccupancyStatus)</th>
<th>@Html.ActionLink("New Apartment", "Create")</th>
</tr>
@foreach (var item in Model)
{
<tr>
<td class="text-center">@Html.DisplayFor(modelItem => item.ApartmentID)</td>
<td>@Html.DisplayFor(modelItem => item.UnitNumber)</td>
<td class="text-center">@Html.DisplayFor(modelItem => item.Bedrooms)</td>
<td class="text-center">@Html.DisplayFor(modelItem => item.Bathrooms)</td>
<td>@Html.DisplayFor(modelItem => item.MonthlyRate)</td>
<td>@Html.DisplayFor(modelItem => item.SecurityDeposit)</td>
<td>@Html.DisplayFor(modelItem => item.OccupancyStatus)</td>
<td>
@Html.ActionLink("Edit", "Edit", new { id = item.ApartmentID }) |
@Html.ActionLink("Details", "Details", new { id = item.ApartmentID }) |
@Html.ActionLink("Delete", "Delete", new { id = item.ApartmentID })
</td>
</tr>
}
</table>@model ApartmentsRentalManagement2.Models.Apartment
@{
ViewBag.Title = "Apartment Details";
Layout = "~/Views/Shared/_Management.cshtml";
}
<h2 class="bold blue common-font text-center">Apartment Details</h2>
<hr />
<div class="containment">
<dl class="dl-horizontal common-font">
<dt>@Html.DisplayNameFor(model => model.ApartmentID)</dt>
<dd>@Html.DisplayFor(model => model.ApartmentID)</dd>
<dt>@Html.DisplayNameFor(model => model.Bedrooms)</dt>
<dd>@Html.DisplayFor(model => model.Bedrooms)</dd>
<dt>@Html.DisplayNameFor(model => model.Bathrooms)</dt>
<dd>@Html.DisplayFor(model => model.Bathrooms)</dd>
<dt>@Html.DisplayNameFor(model => model.MonthlyRate)</dt>
<dd>@Html.DisplayFor(model => model.MonthlyRate)</dd>
<dt>@Html.DisplayNameFor(model => model.SecurityDeposit)</dt>
<dd>@Html.DisplayFor(model => model.SecurityDeposit)</dd>
<dt>@Html.DisplayNameFor(model => model.OccupancyStatus)</dt>
<dd>@Html.DisplayFor(model => model.OccupancyStatus)</dd>
</dl>
</div>
<p class="text-center">
@Html.ActionLink("Edit Rent Contract", "Edit", new { id = Model.ApartmentID }) ::
@Html.ActionLink("Cancel this Rent Contract", "Delete", new { id = Model.ApartmentID }) ::
@Html.ActionLink("Rental Contracts", "Index")
</p>@model ApartmentsRentalManagement2.Models.Apartment
@{
ViewBag.Title = "New Apartment";
Layout = "~/Views/Shared/_Management.cshtml";
}
<h2 class="bold common-font blue text-center">New Apartment</h2>
<hr />
@using (Html.BeginForm())
{
<div class="containment">
<div class="form-horizontal common-font">
<div class="form-group">
@Html.LabelFor(model => model.UnitNumber, htmlAttributes: new { @class = "control-label col-md-5 blue" })
<div class="col-md-7">
@Html.TextBox("UnitNumber", null, htmlAttributes: new { @class = "form-control", id = "unitNbr" })
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.Bedrooms, htmlAttributes: new { @class = "control-label col-md-5 blue" })
<div class="col-md-7">
@Html.TextBox("Bedrooms", null, htmlAttributes: new { @class = "form-control", id = "beds" })
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.Bathrooms, htmlAttributes: new { @class = "control-label col-md-5 blue" })
<div class="col-md-7">
@Html.TextBox("Bathrooms", null, htmlAttributes: new { @class = "form-control", id = "baths" })
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.MonthlyRate, htmlAttributes: new { @class = "control-label col-md-5 blue" })
<div class="col-md-7">
@Html.TextBox("MonthlyRate", null, htmlAttributes: new { @class = "form-control", id = "rentPerMonth" })
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.SecurityDeposit, htmlAttributes: new { @class = "control-label col-md-5 blue" })
<div class="col-md-7">
@Html.TextBox("SecurityDeposit", null, htmlAttributes: new { @class = "form-control", id = "deposit" })
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.OccupancyStatus, htmlAttributes: new { @class = "control-label col-md-5 blue" })
<div class="col-md-7">
@Html.DropDownList("OccupancyStatus", null, htmlAttributes: new { @class = "form-control", id = "occupancyStatus" })
</div>
</div>
<div class="form-group">
<label class="control-label col-md-5">@Html.ActionLink("Apartments", "Index")</label>
<div class="col-md-7">
<input type="submit" value="Create Apartment Record" class="btn btn-primary" />
</div>
</div>
</div>
</div>
}@model ApartmentsRentalManagement2.Models.Apartment
@{
Layout = "~/Views/Shared/_Management.cshtml";
ViewBag.Title = "Edit/Update Apartment Information";
}
<h2 class="bold common-font blue text-center">Edit/Update Apartment Information</h2>
<hr />
@using (Html.BeginForm())
{
<div class="containment">
<div class="form-horizontal common-font">
<div class="form-group">
@Html.LabelFor(model => model.UnitNumber, htmlAttributes: new { @class = "control-label col-md-5 blue" })
<div class="col-md-7">
@Html.TextBox("UnitNumber", null, htmlAttributes: new { @class = "form-control", id = "unitNbr" })
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.Bedrooms, htmlAttributes: new { @class = "control-label col-md-5 blue" })
<div class="col-md-7">
@Html.TextBox("Bedrooms", null, htmlAttributes: new { @class = "form-control", id = "beds" })
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.Bathrooms, htmlAttributes: new { @class = "control-label col-md-5 blue" })
<div class="col-md-7">
@Html.TextBox("Bathrooms", null, htmlAttributes: new { @class = "form-control", id = "baths" })
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.MonthlyRate, htmlAttributes: new { @class = "control-label col-md-5 blue" })
<div class="col-md-7">
@Html.TextBox("MonthlyRate", null, htmlAttributes: new { @class = "form-control", id = "rentPerMonth" })
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.SecurityDeposit, htmlAttributes: new { @class = "control-label col-md-5 blue" })
<div class="col-md-7">
@Html.TextBox("SecurityDeposit", null, htmlAttributes: new { @class = "form-control", id = "deposit" })
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.OccupancyStatus, htmlAttributes: new { @class = "control-label col-md-5 blue" })
<div class="col-md-7">
@Html.DropDownList("OccupancyStatus", null, htmlAttributes: new { @class = "form-control", id = "occupancyStatus" })
</div>
</div>
<div class="form-group">
<label class="control-label col-md-5">@Html.ActionLink("Apartments", "Index")</label>
<div class="col-md-7">
<input type="submit" value="Update Apartment Information" class="btn btn-primary" />
</div>
</div>
</div>
</div>
}@model ApartmentsRentalManagement2.Models.Apartment
@{
ViewBag.Title = "Deleting Apartment/Residence";
Layout = "~/Views/Shared/_Management.cshtml";
}
<h2 class="bold common-font blue text-center">Deleting Apartment/Residence</h2>
<hr />
<h3 class="common-font blue text-center">Are you sure you want to remove this apartment from our system?</h3>
<div class="containment">
<dl class="dl-horizontal common-font">
<dt>@Html.DisplayNameFor(model => model.ApartmentID)</dt>
<dd>@Html.DisplayFor(model => model.ApartmentID)</dd>
<dt>@Html.DisplayNameFor(model => model.UnitNumber)</dt>
<dd>@Html.DisplayFor(model => model.UnitNumber)</dd>
<dt>@Html.DisplayNameFor(model => model.Bedrooms)</dt>
<dd>@Html.DisplayFor(model => model.Bedrooms)</dd>
<dt>@Html.DisplayNameFor(model => model.Bathrooms)</dt>
<dd>@Html.DisplayFor(model => model.Bathrooms)</dd>
<dt>@Html.DisplayNameFor(model => model.MonthlyRate)</dt>
<dd>@Html.DisplayFor(model => model.MonthlyRate)</dd>
<dt>@Html.DisplayNameFor(model => model.SecurityDeposit)</dt>
<dd>@Html.DisplayFor(model => model.SecurityDeposit)</dd>
<dt>@Html.DisplayNameFor(model => model.OccupancyStatus)</dt>
<dd>@Html.DisplayFor(model => model.OccupancyStatus)</dd>
</dl>
@using (Html.BeginForm())
{
@Html.AntiForgeryToken()
<div class="form-actions no-color">
@Html.ActionLink("Apartments", "Index") ::
<input type="submit" value="Delete this Apartment's Record" class="btn btn-primary" />
</div>
}
</div>Rental Contracts
Tenants are people who rent apartments. Before they take possession of an apartment, they must have an account that contain information as a contract. That's why we will use a table for tenant registration.
Practical Learning: Creating Rental Contracts
using System;
using System.Collections.Generic;
using System.Linq;
using System.ComponentModel.DataAnnotations;
namespace ApartmentsRentalManagement1.Models
{
public class RentalContract
{
[Display(Name = "Rent Contract ID")]
public int RentalContractID { get; set; }
[Display(Name = "Contract #")]
public int ContractNumber { get; set; }
[Display(Name = "Employee ID")]
public int EmployeeID { get; set; }
[DataType(DataType.Date)]
[Display(Name = "Contract Date")]
public DateTime ContractDate { get; set; }
[Display(Name = "First Name")]
public string FirstName { get; set; }
[Display(Name = "Last Name")]
public string LastName { get; set; }
[Display(Name = "Marital Status")]
public string MaritalStatus { get; set; }
[Display(Name = "Children")]
public int NumberOfChildren { get; set; }
[Display(Name = "Apartment")]
public int ApartmentID { get; set; }
[DataType(DataType.Date)]
[Display(Name = "Rent Start Date")]
public DateTime RentStartDate { get; set; }
public string Description
{
get
{
return ContractNumber + " - " + FirstName + " " + LastName + " (renting since " + RentStartDate + ")";
}
}
}
}using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
namespace ApartmentsRentalManagement1.Models
{
public class BusinessObjects
{
. . . No Change
public List<RentalContract> GetRentalContracts()
{
List<RentalContract> rentalContracts = new List<RentalContract>();
using (SqlConnection scApartmentsManagement = new SqlConnection(System.
Configuration.
ConfigurationManager.
ConnectionStrings["csApartmentsRentalManagement"].
ConnectionString))
{
SqlCommand cmdRentalContracts = new SqlCommand("SELECT RentalContractID, ContractNumber, EmployeeID, " +
" ContractDate, FirstName, LastName, " +
" MaritalStatus, NumberOfChildren, " +
" ApartmentID, RentStartDate " +
"FROM Management.RentalContracts;",
scApartmentsManagement);
scApartmentsManagement.Open();
cmdRentalContracts.ExecuteNonQuery();
SqlDataAdapter sdaRentalContracts = new SqlDataAdapter(cmdRentalContracts);
DataSet dsRentalContracts = new DataSet("rent-contracts");
sdaRentalContracts.Fill(dsRentalContracts);
for (int i = 0; i < dsRentalContracts.Tables[0].Rows.Count; i++)
{
RentalContract contract = new RentalContract()
{
RentalContractID = int.Parse(dsRentalContracts.Tables[0].Rows[i][0].ToString()),
ContractNumber = int.Parse(dsRentalContracts.Tables[0].Rows[i][1].ToString()),
EmployeeID = int.Parse(dsRentalContracts.Tables[0].Rows[i][2].ToString()),
ContractDate = DateTime.Parse(dsRentalContracts.Tables[0].Rows[i][3].ToString()),
FirstName = dsRentalContracts.Tables[0].Rows[i][4].ToString(),
LastName = dsRentalContracts.Tables[0].Rows[i][5].ToString(),
MaritalStatus = dsRentalContracts.Tables[0].Rows[i][6].ToString(),
NumberOfChildren = int.Parse(dsRentalContracts.Tables[0].Rows[i][7].ToString()),
ApartmentID = int.Parse(dsRentalContracts.Tables[0].Rows[i][8].ToString()),
RentStartDate = DateTime.Parse(dsRentalContracts.Tables[0].Rows[i][9].ToString())
};
rentalContracts.Add(contract);
}
}
return rentalContracts;
}
public RentalContract FindRentalContract(int? id)
{
RentalContract contract = null;
foreach (var rent in GetRentalContracts())
{
if (rent.RentalContractID == id)
{
contract = rent;
break;
}
}
return contract;
}
. . . No Change
}
}using System;
using System.Net;
using System.Data;
using System.Web.Mvc;
using System.Data.SqlClient;
using System.Collections.Generic;
using ApartmentsRentalManagement1.Models;
namespace ApartmentsRentalManagement1.Controllers
{
public class RentalContractsController : Controller
{
BusinessObjects objects = new BusinessObjects();
// GET: RentalContracts
public ActionResult Index()
{
return View(objects.GetRentalContracts());
}
// GET: RentalContracts/Details/5
public ActionResult Details(int id)
{
if (id == 0)
{
return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
}
RentalContract contract = objects.FindRentalContract(id);
if (contract == null)
{
return HttpNotFound();
}
return View(contract);
}
// GET: RentalContracts/Create
public ActionResult Create()
{
List<SelectListItem> maritals = new List<SelectListItem>
{
new SelectListItem() { Text = "Unknown", Value = "Unknown" },
new SelectListItem() { Text = "Single", Value = "Single" },
new SelectListItem() { Text = "Widdow", Value = "Widdow" },
new SelectListItem() { Text = "Married", Value = "Married" },
new SelectListItem() { Text = "Divorced", Value = "Divorced" },
new SelectListItem() { Text = "Separated", Value = "Separated" }
};
ViewBag.MaritalStatus = maritals;
ViewBag.ApartmentID = new SelectList(objects.GetApartments(), "ApartmentID", "Residence");
ViewBag.EmployeeID = new SelectList(objects.GetEmployees(), "EmployeeID", "Identification");
return View();
}
// POST: RentalContracts/Create
[HttpPost]
public ActionResult Create(FormCollection collection)
{
try
{
// TODO: Add insert logic here
using (SqlConnection scApartmentsManagement = new SqlConnection(System.
Configuration.
ConfigurationManager.
ConnectionStrings["csApartmentsRentalManagement"].
ConnectionString))
{
// This command is used to create a rental contract.
SqlCommand cmdRentalContracts = new SqlCommand("INSERT INTO Management.RentalContracts(ContractNumber, EmployeeID, " +
" ContractDate, FirstName, " +
" LastName, MaritalStatus, " +
" NumberOfChildren, ApartmentID, " +
" RentStartDate) " +
"VALUES(" + collection["ContractNumber"] + ", " +
collection["EmployeeID"] + ", N'" + collection["ContractDate"] +
"', N'" + collection["FirstName"] + "', N'" + collection["LastName"] +
"', N'" + collection["MaritalStatus"] + "', " +
collection["NumberOfChildren"] + ", " + collection["ApartmentID"] +
", N'" + collection["RentStartDate"] + "');",
scApartmentsManagement);
scApartmentsManagement.Open();
cmdRentalContracts.ExecuteNonQuery();
}
/* When an apartment has been selected for a rental contract,
* we must change the status of that apartment from Available to Occupied. */
using (SqlConnection scRentManagement = new SqlConnection(System.Configuration.
ConfigurationManager.
ConnectionStrings["csApartmentsRentalManagement"].
ConnectionString))
{
SqlCommand cmdApartments = new SqlCommand("UPDATE Management.Apartments " +
"SET OccupancyStatus = N'Occupied' " +
"WHERE ApartmentID = " + collection["ApartmentID"] + ";",
scRentManagement);
scRentManagement.Open();
cmdApartments.ExecuteNonQuery();
}
return RedirectToAction("Index");
}
catch
{
return View();
}
}
// GET: RentalContracts/Edit/5
public ActionResult Edit(int id)
{
if (id == 0)
{
return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
}
RentalContract contract = objects.FindRentalContract(id);
if (contract == null)
{
return HttpNotFound();
}
List<SelectListItem> maritals = new List<SelectListItem>
{
new SelectListItem() { Text = "Single", Value = "Single", Selected = (contract.MaritalStatus == "Single") },
new SelectListItem() { Text = "Widdow", Value = "Widdow", Selected = (contract.MaritalStatus == "Widdow") },
new SelectListItem() { Text = "Married", Value = "Married", Selected = (contract.MaritalStatus == "Married") },
new SelectListItem() { Text = "Unknown", Value = "Unknown", Selected = (contract.MaritalStatus == "Unknown") },
new SelectListItem() { Text = "Divorced", Value = "Divorced", Selected = (contract.MaritalStatus == "Divorced") },
new SelectListItem() { Text = "Separated", Value = "Separated", Selected = (contract.MaritalStatus == "Separated") }
};
ViewBag.MaritalStatus = maritals;
ViewBag.EmployeeID = new SelectList(objects.GetEmployees(), "EmployeeID", "Identification", contract.EmployeeID);
ViewBag.ApartmentID = new SelectList(objects.GetApartments(), "ApartmentID", "Residence", contract.ApartmentID);
return View(contract);
}
// POST: RentalContracts/Edit/5
[HttpPost]
public ActionResult Edit(int id, FormCollection collection)
{
try
{
// TODO: Add update logic here
if (ModelState.IsValid)
{
using (SqlConnection scApartmentsManagement = new SqlConnection(System.
Configuration.
ConfigurationManager.
ConnectionStrings["csApartmentsRentalManagement"].
ConnectionString))
{
string strUpdate = "UPDATE Management.RentalContracts " +
"SET ContractNumber = " + collection["ContractNumber"] + ", " +
" EmployeeID = " + collection["EmployeeID"] + ", " +
" FirstName = N'" + collection["FirstName"] + "', " +
" LastName = N'" + collection["LastName"] + "', " +
" MaritalStatus = N'" + collection["MaritalStatus"] + "', " +
" NumberOfChildren = " + collection["NumberOfChildren"] + ", " +
" ApartmentID = " + collection["ApartmentID"] + " " +
"WHERE RentalContractID = " + id + ";";
if (DateTime.Parse(collection["ContractDate"]) != new DateTime(1900, 1, 1))
strUpdate += "UPDATE Management.RentalContracts " +
"SET ContractDate = N'" + collection["ContractDate"] + "' " +
"WHERE RentalContractID = " + id + ";";
if (DateTime.Parse(collection["RentStartDate"]) != new DateTime(1900, 1, 1))
strUpdate += "UPDATE Management.RentalContracts " +
"SET RentStartDate = N'" + collection["RentStartDate"] + "' " +
"WHERE RentalContractID = " + id + ";";
SqlCommand cmdRentalContracts = new SqlCommand(strUpdate,
scApartmentsManagement);
scApartmentsManagement.Open();
cmdRentalContracts.ExecuteNonQuery();
}
/* Change the status of the newly selected apartment (the apartment that has just been applied to the contract),
* to Occupied (from whatever was its status). */
using (SqlConnection scRentManagement = new SqlConnection(System.Configuration.
ConfigurationManager.
ConnectionStrings["csApartmentsRentalManagement"].
ConnectionString))
{
SqlCommand cmdApartments = new SqlCommand("UPDATE Management.Apartments " +
"SET OccupancyStatus = N'Occupied' " +
"WHERE ApartmentID = " + collection["ApartmentID"] + ";",
scRentManagement);
scRentManagement.Open();
cmdApartments.ExecuteNonQuery();
}
return RedirectToAction("Index");
}
RentalContract contract = objects.FindRentalContract(id);
List<SelectListItem> maritals = new List<SelectListItem>
{
new SelectListItem() { Text = "Single", Value = "Single" },
new SelectListItem() { Text = "Widdow", Value = "Widdow" },
new SelectListItem() { Text = "Married", Value = "Married" },
new SelectListItem() { Text = "Unknown", Value = "Unknown" },
new SelectListItem() { Text = "Divorced", Value = "Divorced" },
new SelectListItem() { Text = "Separated", Value = "Separated" }
};
ViewBag.MaritalStatus = maritals;
ViewBag.EmployeeID = new SelectList(objects.GetEmployees(), "EmployeeID", "Identification", contract.EmployeeID);
ViewBag.ApartmentID = new SelectList(objects.GetApartments(), "ApartmentID", "Residence", contract.ApartmentID);
return View(contract);
}
catch
{
return View();
}
}
// GET: RentalContracts/Delete/5
public ActionResult Delete(int id)
{
RentalContract contract = null;
if (id == 0)
{
return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
}
using (SqlConnection scRentManagement = new SqlConnection(System.Configuration.
ConfigurationManager.
ConnectionStrings["csApartmentsRentalManagement"].
ConnectionString))
{
SqlCommand cmdRentalContracts = new SqlCommand("SELECT RentalContractID, ContractNumber, " +
" EmployeeID, ContractDate, " +
" FirstName, LastName, " +
" MaritalStatus, NumberOfChildren, " +
" ApartmentID, RentStartDate " +
"FROM Management.RentalContracts " +
"WHERE RentalContractID = " + id + ";",
scRentManagement);
scRentManagement.Open();
SqlDataAdapter sdaRentalContracts = new SqlDataAdapter(cmdRentalContracts);
DataSet dsRentalContracts = new DataSet("rental-contracts");
sdaRentalContracts.Fill(dsRentalContracts);
if (dsRentalContracts.Tables[0].Rows.Count > 0)
{
contract = new RentalContract()
{
RentalContractID = int.Parse(dsRentalContracts.Tables[0].Rows[0][0].ToString()),
ContractNumber = int.Parse(dsRentalContracts.Tables[0].Rows[0][1].ToString()),
EmployeeID = int.Parse(dsRentalContracts.Tables[0].Rows[0][2].ToString()),
ContractDate = DateTime.Parse(dsRentalContracts.Tables[0].Rows[0][3].ToString()),
FirstName = dsRentalContracts.Tables[0].Rows[0][4].ToString(),
LastName = dsRentalContracts.Tables[0].Rows[0][5].ToString(),
MaritalStatus = dsRentalContracts.Tables[0].Rows[0][6].ToString(),
NumberOfChildren = int.Parse(dsRentalContracts.Tables[0].Rows[0][7].ToString()),
ApartmentID = int.Parse(dsRentalContracts.Tables[0].Rows[0][8].ToString()),
RentStartDate = DateTime.Parse(dsRentalContracts.Tables[0].Rows[0][9].ToString())
};
}
}
return contract == null ? HttpNotFound() : (ActionResult)View(contract);
}
// POST: RentalContracts/Delete/5
[HttpPost]
public ActionResult Delete(int id, FormCollection collection)
{
try
{
// TODO: Add delete logic here
using (SqlConnection scApartmentsManagement = new SqlConnection(System.
Configuration.
ConfigurationManager.
ConnectionStrings["csApartmentsRentalManagement"].
ConnectionString))
{
SqlCommand cmdRentalContracts = new SqlCommand("DELETE Management.RentalContracts " +
"WHERE RentalContractID = " + id + ";",
scApartmentsManagement);
scApartmentsManagement.Open();
cmdRentalContracts.ExecuteNonQuery();
}
return RedirectToAction("Index");
}
catch
{
return View();
}
}
}
}@model IEnumerable<ApartmentsRentalManagement1.Models.RentalContract>
@{
ViewBag.Title = "Rent Contracts";
Layout = "~/Views/Shared/_Management.cshtml";
}
<h2 class="bold blue common-font text-center">Rent Contracts</h2>
<hr />
<table class="table table-hover common-font">
<tr>
<th class="text-center">@Html.DisplayNameFor(model => model.RentalContractID)</th>
<th class="text-center">@Html.DisplayNameFor(model => model.ContractNumber)</th>
<th class="text-center">@Html.DisplayNameFor(model => model.EmployeeID)</th>
<th class="text-center">@Html.DisplayNameFor(model => model.ContractDate)</th>
<th>@Html.DisplayNameFor(model => model.FirstName)</th>
<th>@Html.DisplayNameFor(model => model.LastName)</th>
<th>@Html.DisplayNameFor(model => model.MaritalStatus)</th>
<th>@Html.DisplayNameFor(model => model.NumberOfChildren)</th>
<th>@Html.DisplayNameFor(model => model.ApartmentID)</th>
<th class="text-center">@Html.DisplayNameFor(model => model.RentStartDate)</th>
<th class="text-center">@Html.ActionLink("Start New Rent Contract", "Create")</th>
</tr>
@foreach (var item in Model)
{
<tr>
<td class="text-center">@Html.DisplayFor(modelItem => item.RentalContractID)</td>
<td class="text-center">@Html.DisplayFor(modelItem => item.ContractNumber)</td>
<td class="text-center">@Html.DisplayFor(modelItem => item.EmployeeID)</td>
<td class="text-center">@item.ContractDate.ToShortDateString()</td>
<td>@Html.DisplayFor(modelItem => item.FirstName)</td>
<td>@Html.DisplayFor(modelItem => item.LastName)</td>
<td>@Html.DisplayFor(modelItem => item.MaritalStatus)</td>
<td class="text-center">@Html.DisplayFor(modelItem => item.NumberOfChildren)</td>
<td class="text-center">@Html.DisplayFor(modelItem => item.ApartmentID)</td>
<td class="text-center">@item.RentStartDate.ToShortDateString()</td>
<td>
@Html.ActionLink("Edit", "Edit", new { id = item.RentalContractID }) |
@Html.ActionLink("Details", "Details", new { id = item.RentalContractID }) |
@Html.ActionLink("Delete", "Delete", new { id = item.RentalContractID })
</td>
</tr>
}
</table>@model ApartmentsRentalManagement1.Models.RentalContract
@{
ViewBag.Title = "Rental Contract Details";
Layout = "~/Views/Shared/_Management.cshtml";
}
<h2 class="bold blue common-font text-center">Rental Contract Details</h2>
<hr />
<div>
<dl class="dl-horizontal common-font">
<dt>@Html.DisplayNameFor(model => model.RentalContractID)</dt>
<dd>@Html.DisplayFor(model => model.RentalContractID)</dd>
<dt>@Html.DisplayNameFor(model => model.EmployeeID)</dt>
<dd>@Html.DisplayFor(model => model.EmployeeID)</dd>
<dt>@Html.DisplayNameFor(model => model.ContractDate)</dt>
<dd>@Html.DisplayFor(model => model.ContractDate)</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.MaritalStatus)</dt>
<dd>@Html.DisplayFor(model => model.MaritalStatus)</dd>
<dt>@Html.DisplayNameFor(model => model.NumberOfChildren)</dt>
<dd>@Html.DisplayFor(model => model.NumberOfChildren)</dd>
<dt>@Html.DisplayNameFor(model => model.ApartmentID)</dt>
<dd>@Html.DisplayFor(model => model.ApartmentID)</dd>
<dt>@Html.DisplayNameFor(model => model.RentStartDate)</dt>
<dd>@Html.DisplayFor(model => model.RentStartDate)</dd>
</dl>
</div>
<p>
@Html.ActionLink("Edit", "Edit", new { id = Model.RentalContractID }) |
@Html.ActionLink("Rental Contracts", "Index")
</p>@model ApartmentsRentalManagement1.Models.RentalContract
@{
ViewBag.Title = "New Rental Contract";
Layout = "~/Views/Shared/_Management.cshtml";
}
<h2 class="bold common-font blue">Create Rental Contract</h2>
<hr />
@using (Html.BeginForm())
{
<div class="form-horizontal common-font">
<div class="form-group">
@Html.LabelFor(model => model.ContractNumber, htmlAttributes: new { @class = "control-label col-md-2" })
<div class="col-md-10">
@Html.EditorFor(model => model.ContractNumber, new { htmlAttributes = new { @class = "form-control" } })
</div>
</div>
<div class="form-group">
<label for="employee" class="control-label col-md-2">Processed By</label>
<div class="col-md-10">
@Html.DropDownList("EmployeeID", null, htmlAttributes: new { @class = "form-control", id = "employee" })
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.ContractDate, htmlAttributes: new { @class = "control-label col-md-2" })
<div class="col-md-10">
@Html.EditorFor(model => model.ContractDate, new { htmlAttributes = new { @class = "form-control", type = "date" } })
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.FirstName, htmlAttributes: new { @class = "control-label col-md-2" })
<div class="col-md-10">
@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-2" })
<div class="col-md-10">
@Html.EditorFor(model => model.LastName, new { htmlAttributes = new { @class = "form-control" } })
</div>
</div>
<div class="form-group">
<label for="maritalStatus" class="control-label col-md-2">Marital Status</label>
<div class="col-md-10">
@Html.DropDownList("MaritalStatus", null, htmlAttributes: new { @class = "form-control", id = "maritalStatus" })
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.NumberOfChildren, htmlAttributes: new { @class = "control-label col-md-2" })
<div class="col-md-10">
@Html.EditorFor(model => model.NumberOfChildren, new { htmlAttributes = new { @class = "form-control" } })
</div>
</div>
<div class="form-group">
<label for="apart" class="control-label col-md-2">Apartment</label>
<div class="col-md-10">
@Html.DropDownList("ApartmentID", null, htmlAttributes: new { @class = "form-control", id = "apart" })
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.RentStartDate, htmlAttributes: new { @class = "control-label col-md-2" })
<div class="col-md-10">
@Html.EditorFor(model => model.RentStartDate, new { htmlAttributes = new { @class = "form-control", type = "date" } })
</div>
</div>
<div class="form-group">
<label class="control-label col-md-2">@Html.ActionLink("Rental Contracts", "Index")</label>
<div class="col-md-10">
<input type="submit" value="Create Rental Contract" class="btn btn-primary" />
</div>
</div>
</div>
}@model ApartmentsRentalManagement1.Models.RentalContract
@{
Layout = "~/Views/Shared/_Management.cshtml";
ViewBag.Title = "Edit/Update Rental Contract";
}
<h2 class="bold common-font blue">Edit/Update Rental Contract</h2>
<hr />
@using (Html.BeginForm())
{
<div class="form-horizontal common-font">
<div class="form-group">
@Html.LabelFor(model => model.ContractNumber, htmlAttributes: new { @class = "control-label col-md-2" })
<div class="col-md-10">
@Html.EditorFor(model => model.ContractNumber, new { htmlAttributes = new { @class = "form-control" } })
</div>
</div>
<div class="form-group">
<label for="employee" class="control-label col-md-2">Processed By</label>
<div class="col-md-10">
@Html.DropDownList("EmployeeID", null, htmlAttributes: new { @class = "form-control", id = "employee" })
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.ContractDate, htmlAttributes: new { @class = "control-label col-md-2" })
<div class="col-md-10">
@Html.EditorFor(model => model.ContractDate, new { htmlAttributes = new { @class = "form-control" } })
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.FirstName, htmlAttributes: new { @class = "control-label col-md-2" })
<div class="col-md-10">
@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-2" })
<div class="col-md-10">
@Html.EditorFor(model => model.LastName, new { htmlAttributes = new { @class = "form-control" } })
</div>
</div>
<div class="form-group">
<label for="maritalStatus" class="control-label col-md-2">Marital Status</label>
<div class="col-md-2">
@Html.DropDownList("MaritalStatus", null, htmlAttributes: new { @class = "form-control", id = "maritalStatus" })
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.NumberOfChildren, htmlAttributes: new { @class = "control-label col-md-2" })
<div class="col-md-10">
@Html.EditorFor(model => model.NumberOfChildren, new { htmlAttributes = new { @class = "form-control" } })
</div>
</div>
<div class="form-group">
<label for="apart" class="control-label col-md-2">Apartment</label>
<div class="col-md-10">
@Html.DropDownList("ApartmentID", null, htmlAttributes: new { @class = "form-control", id = "apart" })
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.RentStartDate, htmlAttributes: new { @class = "control-label col-md-2" })
<div class="col-md-10">
@Html.EditorFor(model => model.RentStartDate, new { htmlAttributes = new { @class = "form-control" } })
</div>
</div>
<div class="form-group">
<label class="control-label col-md-2">@Html.ActionLink("Rental Contracts", "Index")</label>
<div class="col-md-10">
<input type="submit" value="Update this Rental Contract" class="btn btn-primary" />
</div>
</div>
</div>
}@model ApartmentsRentalManagement1.Models.RentalContract
@{
ViewBag.Title = "Delete Rental Contract";
Layout = "~/Views/Shared/_Management.cshtml";
}
<h2 class="bold common-font blue">Delete Rent Contract</h2>
<hr />
<h3 class="common-font blue">Are you sure you want to delete this rent contract?</h3>
<div>
<dl class="dl-horizontal common-font">
<dt>@Html.DisplayNameFor(model => model.RentalContractID)</dt>
<dd>@Html.DisplayFor(model => model.RentalContractID)</dd>
<dt>@Html.DisplayNameFor(model => model.ContractNumber)</dt>
<dd>@Html.DisplayFor(model => model.ContractNumber)</dd>
<dt>@Html.DisplayNameFor(model => model.EmployeeID)</dt>
<dd>@Html.DisplayFor(model => model.EmployeeID)</dd>
<dt>@Html.DisplayNameFor(model => model.ContractDate)</dt>
<dd>@Html.DisplayFor(model => model.ContractDate)</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.MaritalStatus)</dt>
<dd>@Html.DisplayFor(model => model.MaritalStatus)</dd>
<dt>@Html.DisplayNameFor(model => model.NumberOfChildren)</dt>
<dd>@Html.DisplayFor(model => model.NumberOfChildren)</dd>
<dt>@Html.DisplayNameFor(model => model.ApartmentID)</dt>
<dd>@Html.DisplayFor(model => model.ApartmentID)</dd>
<dt>@Html.DisplayNameFor(model => model.RentStartDate)</dt>
<dd>@Html.DisplayFor(model => model.RentStartDate)</dd>
</dl>
@using (Html.BeginForm())
{
<div class="form-actions no-color">
@Html.ActionLink("Rent Contracts", "Index") ::
<input type="submit" value="Delete this Rent Contract" class="btn btn-primary" />
</div>
}
</div>Rent Payments
Tenants are asked to pay rent at the end of every mont. To manage this aspect of the business, we use a table.
Practical Learning: Managing Rent Payments
using System;
using System.ComponentModel.DataAnnotations;
namespace ApartmentsRentalManagement1.Models
{
public class Payment
{
[Display(Name = "Payment ID")]
public int PaymentID { get; set; }
[Display(Name = "Receipt #")]
public int ReceiptNumber { get; set; }
[Display(Name = "Employee ID")]
public int EmployeeID { get; set; }
[Display(Name = "Rent Contract ID")]
public int RentalContractID { get; set; }
[DataType(DataType.Date)]
[Display(Name = "Payment Date")]
public DateTime PaymentDate { get; set; }
public int Amount { get; set; }
public string Notes { get; set; }
}
}using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
namespace ApartmentsRentalManagement1.Models
{
public class BusinessObjects
{
public List<Apartment> GetApartments()
{
List<Apartment> apartments = new List<Apartment>();
using (SqlConnection scApartmentsManagement = new SqlConnection(System.
Configuration.
ConfigurationManager.
ConnectionStrings["csApartmentsRentalManagement"].
ConnectionString))
{
SqlCommand cmdApartments = new SqlCommand("SELECT ApartmentID, UnitNumber, Bedrooms, " +
" Bathrooms, MonthlyRate, " +
" SecurityDeposit, OccupancyStatus " +
"FROM Management.Apartments;",
scApartmentsManagement);
scApartmentsManagement.Open();
cmdApartments.ExecuteNonQuery();
SqlDataAdapter sdaApartments = new SqlDataAdapter(cmdApartments);
DataSet dsApartments = new DataSet("apartments");
sdaApartments.Fill(dsApartments);
for (int i = 0; i < dsApartments.Tables[0].Rows.Count; i++)
{
DataRow drApartment = dsApartments.Tables[0].Rows[i];
Apartment unit = new Apartment()
{
ApartmentID = int.Parse(drApartment[0].ToString()),
UnitNumber = drApartment[1].ToString(),
Bedrooms = int.Parse(drApartment[2].ToString()),
Bathrooms = int.Parse(drApartment[3].ToString()),
MonthlyRate = int.Parse(drApartment[4].ToString()),
SecurityDeposit = int.Parse(drApartment[5].ToString()),
OccupancyStatus = drApartment[6].ToString()
};
apartments.Add(unit);
}
}
return apartments;
}
public List<Employee> GetEmployees()
{
List<Employee> employees = new List<Employee>();
using (SqlConnection scApartmentsManagement = new SqlConnection(System.
Configuration.
ConfigurationManager.
ConnectionStrings["csApartmentsRentalManagement"].
ConnectionString))
{
SqlCommand cmdEmployees = new SqlCommand("SELECT EmployeeID, EmployeeNumber, " +
" FirstName, LastName, EmploymentTitle " +
"FROM HumanResources.Employees;",
scApartmentsManagement);
scApartmentsManagement.Open();
cmdEmployees.ExecuteNonQuery();
SqlDataAdapter sdaEmployees = new SqlDataAdapter(cmdEmployees);
DataSet dsEmployees = new DataSet("employees");
sdaEmployees.Fill(dsEmployees);
Employee staff = null;
for (int i = 0; i < dsEmployees.Tables[0].Rows.Count; i++)
{
DataRow drEmployee = dsEmployees.Tables[0].Rows[i];
staff = new Employee()
{
EmployeeID = int.Parse(drEmployee[0].ToString()),
EmployeeNumber = drEmployee[1].ToString(),
FirstName = drEmployee[2].ToString(),
LastName = drEmployee[3].ToString(),
EmploymentTitle = drEmployee[4].ToString()
};
employees.Add(staff);
}
}
return employees;
}
public Employee FindEmployee(int? id)
{
Employee employee = null;
foreach (var staff in GetEmployees())
{
if (staff.EmployeeID == id)
{
employee = staff;
break;
}
}
return employee;
}
public List<RentalContract> GetRentalContracts()
{
List<RentalContract> rentalContracts = new List<RentalContract>();
using (SqlConnection scApartmentsManagement = new SqlConnection(System.
Configuration.
ConfigurationManager.
ConnectionStrings["csApartmentsRentalManagement"].
ConnectionString))
{
SqlCommand cmdRentalContracts = new SqlCommand("SELECT RentalContractID, ContractNumber, EmployeeID, " +
" ContractDate, FirstName, LastName, " +
" MaritalStatus, NumberOfChildren, " +
" ApartmentID, RentStartDate " +
"FROM Management.RentalContracts;",
scApartmentsManagement);
scApartmentsManagement.Open();
cmdRentalContracts.ExecuteNonQuery();
SqlDataAdapter sdaRentalContracts = new SqlDataAdapter(cmdRentalContracts);
DataSet dsRentalContracts = new DataSet("rent-contracts");
sdaRentalContracts.Fill(dsRentalContracts);
for (int i = 0; i < dsRentalContracts.Tables[0].Rows.Count; i++)
{
RentalContract contract = new RentalContract()
{
RentalContractID = int.Parse(dsRentalContracts.Tables[0].Rows[i][0].ToString()),
ContractNumber = int.Parse(dsRentalContracts.Tables[0].Rows[i][1].ToString()),
EmployeeID = int.Parse(dsRentalContracts.Tables[0].Rows[i][2].ToString()),
ContractDate = DateTime.Parse(dsRentalContracts.Tables[0].Rows[i][3].ToString()),
FirstName = dsRentalContracts.Tables[0].Rows[i][4].ToString(),
LastName = dsRentalContracts.Tables[0].Rows[i][5].ToString(),
MaritalStatus = dsRentalContracts.Tables[0].Rows[i][6].ToString(),
NumberOfChildren = int.Parse(dsRentalContracts.Tables[0].Rows[i][7].ToString()),
ApartmentID = int.Parse(dsRentalContracts.Tables[0].Rows[i][8].ToString()),
RentStartDate = DateTime.Parse(dsRentalContracts.Tables[0].Rows[i][9].ToString())
};
rentalContracts.Add(contract);
}
}
return rentalContracts;
}
public RentalContract FindRentalContract(int? id)
{
RentalContract contract = null;
foreach (var rent in GetRentalContracts())
{
if (rent.RentalContractID == id)
{
contract = rent;
break;
}
}
return contract;
}
public List<Payment> GetPayments()
{
List<Payment> payments = new List<Payment>();
using (SqlConnection scApartmentsManagement = new SqlConnection(System.
Configuration.
ConfigurationManager.
ConnectionStrings["csApartmentsRentalManagement"].
ConnectionString))
{
SqlCommand cmdPayments = new SqlCommand("SELECT PaymentID, ReceiptNumber, EmployeeID, " +
" RentalContractID, PaymentDate, " +
" Amount, Notes " +
"FROM Management.Payments;",
scApartmentsManagement);
scApartmentsManagement.Open();
cmdPayments.ExecuteNonQuery();
SqlDataAdapter sdaPayments = new SqlDataAdapter(cmdPayments);
DataSet dsPayments = new DataSet("payments");
sdaPayments.Fill(dsPayments);
for (int i = 0; i < dsPayments.Tables[0].Rows.Count; i++)
{
payments.Add(new Payment()
{
PaymentID = int.Parse(dsPayments.Tables[0].Rows[i][0].ToString()),
ReceiptNumber = int.Parse(dsPayments.Tables[0].Rows[i][1].ToString()),
EmployeeID = int.Parse(dsPayments.Tables[0].Rows[i][2].ToString()),
RentalContractID = int.Parse(dsPayments.Tables[0].Rows[i][3].ToString()),
PaymentDate = DateTime.Parse(dsPayments.Tables[0].Rows[i][4].ToString()),
Amount = int.Parse(dsPayments.Tables[0].Rows[i][5].ToString()),
Notes = dsPayments.Tables[0].Rows[i][6].ToString()
});
}
}
return payments;
}
public Payment FindPayment(int? id)
{
Payment payment = null;
foreach (var invoice in GetPayments())
{
if (invoice.PaymentID == id)
{
payment = invoice;
break;
}
}
return payment;
}
}
}using System.Net;
using System.Collections.Generic;
using System.Data;
using System.Web.Mvc;
using System.Data.SqlClient;
using ApartmentsRentalManagement1.Models;
namespace ApartmentRentalManagement1.Controllers
{
public class PaymentsController : Controller
{
BusinessObjects objects = new BusinessObjects();
// GET: Payments
public ActionResult Index()
{
return View(objects.GetPayments());
}
// GET: Payments/Details/5
public ActionResult Details(int id)
{
if (id == 0)
{
return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
}
Payment rentPayment = objects.FindPayment(id);
if (rentPayment == null)
{
return HttpNotFound();
}
return View(rentPayment);
}
// GET: Payments/Create
public ActionResult Create()
{
ViewBag.EmployeeID = new SelectList(objects.GetEmployees(), "EmployeeID", "Identification");
ViewBag.RentalContractID = new SelectList(objects.GetRentalContracts(), "RentalContractID", "Description");
return View();
}
// POST: Payments/Create
[HttpPost]
public ActionResult Create(FormCollection collection)
{
try
{
// TODO: Add insert logic here
using (SqlConnection scApartmentsManagement = new SqlConnection(System.
Configuration.
ConfigurationManager.
ConnectionStrings["csApartmentsRentalManagement"].
ConnectionString))
{
SqlCommand cmdPayments = new SqlCommand("INSERT INTO Management.Payments(ReceiptNumber, EmployeeID, " +
" RentalContractID, PaymentDate, " +
" Amount, Notes) " +
"VALUES(" + collection["ReceiptNumber"] + ", " +
collection["EmployeeID"] + ", " + collection["RentalContractID"] +
", N'" + collection["PaymentDate"] + "', " + collection["Amount"] +
", N'" + collection["Notes"] + "');",
scApartmentsManagement);
scApartmentsManagement.Open();
cmdPayments.ExecuteNonQuery();
}
return RedirectToAction("Index");
}
catch
{
return View();
}
}
// GET: Payments/Edit/5
public ActionResult Edit(int id)
{
if (id == 0)
{
return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
}
Payment pmt = objects.FindPayment(id);
if (pmt == null)
{
return HttpNotFound();
}
ViewBag.EmployeeID = new SelectList(objects.GetEmployees(), "EmployeeID", "Identification", pmt.EmployeeID);
ViewBag.RentalContractID = new SelectList(objects.GetRentalContracts(), "RentalContractID", "Description", pmt.RentalContractID);
return View(pmt);
}
// POST: Payments/Edit/5
[HttpPost]
public ActionResult Edit(int id, FormCollection collection)
{
try
{
// TODO: Add update logic here
using (SqlConnection scRentManagement = new SqlConnection(System.Configuration.
ConfigurationManager.
ConnectionStrings["csApartmentsRentalManagement"].
ConnectionString))
{
SqlCommand cmdApartments = new SqlCommand("UPDATE Management.Payments " +
"SET ReceiptNumber = " + collection["ReceiptNumber"] + ", " +
" EmployeeID = " + collection["EmployeeID"] + ", " +
" RentalContractID = " + collection["RentalContractID"] + ", " +
" PaymentDate = N'" + collection["PaymentDate"] + "', " +
" Amount = " + collection["Amount"] + ", " +
" Notes = N'" + collection["Notes"] + "' " +
"WHERE PaymentID = " + id + "; ",
scRentManagement);
scRentManagement.Open();
cmdApartments.ExecuteNonQuery();
}
return RedirectToAction("Index");
}
catch
{
return View();
}
}
// GET: Payments/Delete/5
public ActionResult Delete(int id)
{
if (id == 0)
{
return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
}
Payment pmt = objects.FindPayment(id);
if (pmt == null)
{
return HttpNotFound();
}
return View(pmt);
}
// POST: Payments/Delete/5
[HttpPost]
public ActionResult Delete(int id, FormCollection collection)
{
try
{
// TODO: Add delete logic here
using (SqlConnection scRentManagement = new SqlConnection(System.Configuration.
ConfigurationManager.
ConnectionStrings["csApartmentsRentalManagement"].
ConnectionString))
{
SqlCommand cmdPayments = new SqlCommand("DELETE FROM Management.Payments " +
"WHERE PaymentID = " + id + ";",
scRentManagement);
scRentManagement.Open();
cmdPayments.ExecuteNonQuery();
}
return RedirectToAction("Index");
}
catch
{
return View();
}
}
}
}@model IEnumerable<ApartmentsRentalManagement1.Models.Payment>
@{
ViewBag.Title = "Rent Payments";
Layout = "~/Views/Shared/_Management.cshtml";
}
<h2 class="bold blue common-font text-center">Rent Payments</h2>
<table class="table table-hover common-font">
<tr>
<th>@Html.DisplayNameFor(model => model.PaymentID)</th>
<th>@Html.DisplayNameFor(model => model.ReceiptNumber)</th>
<th>@Html.DisplayNameFor(model => model.EmployeeID)</th>
<th>@Html.DisplayNameFor(model => model.RentalContractID)</th>
<th>@Html.DisplayNameFor(model => model.PaymentDate)</th>
<th>@Html.DisplayNameFor(model => model.Amount)</th>
<th>@Html.DisplayNameFor(model => model.Notes)</th>
<th>@Html.ActionLink("New Rent Payment", "Create")</th>
</tr>
@foreach (var item in Model)
{
//string strPaymentDate = ;
<tr>
<td class="text-center">@Html.DisplayFor(modelItem => item.PaymentID)</td>
<td>@Html.DisplayFor(modelItem => item.ReceiptNumber)</td>
<td class="text-center">@Html.DisplayFor(modelItem => item.EmployeeID)</td>
<td class="text-center">@Html.DisplayFor(modelItem => item.RentalContractID)</td>
<td>@item.PaymentDate.ToLongDateString()</td>
<td>@Html.DisplayFor(modelItem => item.Amount)</td>
<td>@Html.DisplayFor(modelItem => item.Notes)</td>
<td>
@Html.ActionLink("Edit", "Edit", new { id = item.PaymentID }) |
@Html.ActionLink("Details", "Details", new { id = item.PaymentID }) |
@Html.ActionLink("Delete", "Delete", new { id = item.PaymentID })
</td>
</tr>
}
</table>@model ApartmentsRentalManagement1.Models.Payment
@{
ViewBag.Title = "Rent Payment Details";
Layout = "~/Views/Shared/_Management.cshtml";
}
<h2 class="bold common-font blue">Rent Payment Details</h2>
<hr />
<div>
<dl class="dl-horizontal common-font">
<dt>@Html.DisplayNameFor(model => model.PaymentID)</dt>
<dd>@Html.DisplayFor(model => model.PaymentID)</dd>
<dt>@Html.DisplayNameFor(model => model.ReceiptNumber)</dt>
<dd>@Html.DisplayFor(model => model.ReceiptNumber)</dd>
<dt>@Html.DisplayNameFor(model => model.EmployeeID)</dt>
<dd>@Html.DisplayFor(model => model.EmployeeID)</dd>
<dt>@Html.DisplayNameFor(model => model.RentalContractID)</dt>
<dd>@Html.DisplayFor(model => model.RentalContractID)</dd>
<dt>@Html.DisplayNameFor(model => model.PaymentDate)</dt>
<dd>@Html.DisplayFor(model => model.PaymentDate)</dd>
<dt>@Html.DisplayNameFor(model => model.Amount)</dt>
<dd>@Html.DisplayFor(model => model.Amount)</dd>
<dt>@Html.DisplayNameFor(model => model.Notes)</dt>
<dd>@Html.DisplayFor(model => model.Notes)</dd>
</dl>
</div>
<p>
@Html.ActionLink("Edit/Update Employee Record", "Edit", new { id = Model.EmployeeID }) ::
@Html.ActionLink("Employees", "Index")
</p>@model ApartmentsRentalManagement1.Models.Payment
@{
ViewBag.Title = "Make Rent Payment";
Layout = "~/Views/Shared/_Management.cshtml";
}
<h2 class="bold common-font blue">Make Rent Payment</h2>
<hr />
@using (Html.BeginForm())
{
<div class="form-horizontal common-font">
<div class="form-group">
@Html.LabelFor(model => model.ReceiptNumber, htmlAttributes: new { @class = "control-label col-md-2" })
<div class="col-md-10">
@Html.EditorFor(model => model.ReceiptNumber, new { htmlAttributes = new { @class = "form-control" } })
</div>
</div>
<div class="form-group">
<label for="employee" class="control-label col-md-2">Processed By</label>
<div class="col-md-10">
@Html.DropDownList("EmployeeID", null, htmlAttributes: new { @class = "form-control", id = "employee" })
</div>
</div>
<div class="form-group">
<label for="contractNbr" class="control-label col-md-2">Rent Contract</label>
<div class="col-md-10">
@Html.DropDownList("RentalContractID", null, htmlAttributes: new { @class = "form-control", id = "contractNbr" })
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.PaymentDate, htmlAttributes: new { @class = "control-label col-md-2" })
<div class="col-md-10">
@Html.EditorFor(model => model.PaymentDate, new { htmlAttributes = new { @class = "form-control", type = "date" } })
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.Amount, htmlAttributes: new { @class = "control-label col-md-2" })
<div class="col-md-10">
@Html.EditorFor(model => model.Amount, new { htmlAttributes = new { @class = "form-control" } })
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.Notes, htmlAttributes: new { @class = "control-label col-md-2" })
<div class="col-md-10">
@Html.EditorFor(model => model.Notes, new { htmlAttributes = new { @class = "form-control" } })
</div>
</div>
<div class="form-group">
<label class="control-label col-md-2">@Html.ActionLink("Rent Payments", "Index")</label>
<div class="col-md-10">
<input type="submit" value="Make Rent Payment" class="btn btn-primary" />
</div>
</div>
</div>
}@model ApartmentsRentalManagement1.Models.Payment
@{
ViewBag.Title = "Edit/Update Rent Payment";
Layout = "~/Views/Shared/_Management.cshtml";
}
<h2 class="bold common-font blue">Edit/Update Rent Payment</h2>
<hr />
@using (Html.BeginForm())
{
<div class="form-horizontal common-font">
<div class="form-group">
@Html.LabelFor(model => model.ReceiptNumber, htmlAttributes: new { @class = "control-label col-md-2" })
<div class="col-md-10">
@Html.EditorFor(model => model.ReceiptNumber, new { htmlAttributes = new { @class = "form-control" } })
</div>
</div>
<div class="form-group">
<label for="employee" class="control-label col-md-2">Processed By</label>
<div class="col-md-10">
@Html.DropDownList("EmployeeID", null, htmlAttributes: new { @class = "form-control", id = "employee" })
</div>
</div>
<div class="form-group">
<label for="contractNbr" class="control-label col-md-2">Rent Contract</label>
<div class="col-md-10">
@Html.DropDownList("RentalContractID", null, htmlAttributes: new { @class = "form-control", id = "contractNbr" })
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.PaymentDate, htmlAttributes: new { @class = "control-label col-md-2" })
<div class="col-md-10">
@Html.EditorFor(model => model.PaymentDate, new { htmlAttributes = new { @class = "form-control", type = "date" } })
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.Amount, htmlAttributes: new { @class = "control-label col-md-2" })
<div class="col-md-10">
@Html.EditorFor(model => model.Amount, new { htmlAttributes = new { @class = "form-control" } })
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.Notes, htmlAttributes: new { @class = "control-label col-md-2" })
<div class="col-md-10">
@Html.EditorFor(model => model.Notes, new { htmlAttributes = new { @class = "form-control" } })
</div>
</div>
<div class="form-group">
<label class="control-label col-md-2">@Html.ActionLink("Rent Payments", "Index")</label>
<div class="col-md-10">
<input type="submit" value="Update Rent Payment" class="btn btn-primary" />
</div>
</div>
</div>
}@model ApartmentsRentalManagement1.Models.Payment
@{
ViewBag.Title = "Cancel Rent Payment";
Layout = "~/Views/Shared/_Management.cshtml";
}
<h2 class="bold common-font blue">Cancel Rent Payment</h2>
<hr />
<h3 class="common-font blue">Are you sure that you want to cancel this payment?</h3>
<div>
<dl class="dl-horizontal common-font">
<dt>@Html.DisplayNameFor(model => model.PaymentID)</dt>
<dd>@Html.DisplayFor(model => model.PaymentID)</dd>
<dt>@Html.DisplayNameFor(model => model.ReceiptNumber)</dt>
<dd>@Html.DisplayFor(model => model.ReceiptNumber)</dd>
<dt>@Html.DisplayNameFor(model => model.EmployeeID)</dt>
<dd>@Html.DisplayFor(model => model.EmployeeID)</dd>
<dt>@Html.DisplayNameFor(model => model.RentalContractID)</dt>
<dd>@Html.DisplayFor(model => model.RentalContractID)</dd>
<dt>@Html.DisplayNameFor(model => model.PaymentDate)</dt>
<dd>@Html.DisplayFor(model => model.PaymentDate)</dd>
<dt>@Html.DisplayNameFor(model => model.Amount)</dt>
<dd>@Html.DisplayFor(model => model.Amount)</dd>
<dt>@Html.DisplayNameFor(model => model.Notes)</dt>
<dd>@Html.DisplayFor(model => model.Notes)</dd>
</dl>
@using (Html.BeginForm())
{
@Html.AntiForgeryToken()
<div class="form-actions no-color">
@Html.ActionLink("Rent Payments", "Index") ::
<input type="submit" value="Delete Rent Payment" class="btn btn-primary" />
</div>
}
</div>
| Employee # | First Name | Last Name | Title |
| 93947 | Catherine | Watts | Owner - General Manager |
| 40685 | Justine | Sandt | Rent Manager |
| 73048 | Raymond | Wilkinson | Intern |
| 60949 | Mark | Reason | Maintenance Technician |
| 38408 | Marc | Knights | Rent Associate |
| 20448 | Nancy | Longhorn | Rent Associate |
INSERT INTO HumanResources.Employees(EmployeeNumber, FirstName,LastName, EmploymentTitle) VALUES(N'93947', N'Catherine', N'Watts', N'Owner - General Manager'), (N'40685', N'Justine', N'Sandt', N'Rent Manager'), (N'73048', N'Raymond', N'Wilkinson', N'Intern'), (N'60949', N'Mark', N'Reason', N'Maintenance Technician'), (N'38408', N'Marc', N'Knights', N'Rent Associate'), (N'20448', N'Nancy', N'Longhorn', N'Rent Associate'); GO
| Unit # | Bedrooms | Bathrooms | Monthly Rate | Security Deposit | Occupancy Status |
| 101 | 2 | 2 | 1350 | 1100 | Available |
| 102 | 1 | 1 | 1150 | 850 | Needs Maintenance |
| 103 | 1 | 1 | 1150 | 850 | Available |
| 104 | 3 | 2 | 1500 | 1250 | Available |
| 105 | 2 | 1 | 1250 | 1000 | Not Ready |
| 106 | 3 | 2 | 1550 | 1250 | Available |
| 107 | 3 | 2 | 1450 | 1250 | Needs Maintenance |
| 108 | 1 | 1 | 1100 | 850 | Available |
| 109 | 2 | 2 | 1350 | 1100 | Available |
| 110 | 1 | 1 | 1050 | 850 | Not Ready |
| 111 | 2 | 2 | 1350 | 1100 | Needs Maintenance |
| 112 | 2 | 1 | 1285 | 1000 | Available |
| 201 | 2 | 1 | 1185 | 1000 | Not Ready |
| 202 | 1 | 1 | 1150 | 850 | Available |
| 203 | 1 | 1 | 1150 | 850 | Available |
| 204 | 3 | 2 | 1600 | 1250 | Available |
| 205 | 2 | 1 | 1100 | 1000 | Needs Maintenance |
| 206 | 3 | 2 | 1500 | 1250 | Available |
| 207 | 3 | 2 | 1550 | 1250 | Available |
| 208 | 1 | 1 | 985 | 850 | Available |
| 209 | 2 | 2 | 1350 | 1100 | Available |
| 210 | 1 | 1 | 1150 | 850 | Not Ready |
| 211 | 2 | 2 | 1350 | 1100 | Available |
| 212 | 2 | 1 | 1075 | 1000 | Available |
| 301 | 2 | 2 | 1175 | 1000 | Available |
| 302 | 1 | 1 | 1150 | 850 | Needs Maintenance |
| 303 | 1 | 1 | 1100 | 850 | Available |
| 304 | 3 | 2 | 1250 | 1100 | Available |
| 305 | 2 | 1 | 1100 | 1000 | Available |
| 306 | 3 | 2 | 1250 | 1100 | Available |
| 307 | 3 | 2 | 1100 | 1250 | Available |
| 308 | 1 | 1 | 1100 | 850 | Available |
| 309 | 2 | 2 | 1100 | 950 | Available |
| 310 | 1 | 1 | 1100 | 850 | Available |
| 311 | 2 | 2 | 1100 | 1000 | Not Ready |
| 312 | 2 | 1 | 1100 | 1000 | Available |
INSERT INTO Management.Apartments(UnitNumber, Bedrooms, Bathrooms, MonthlyRate, SecurityDeposit, OccupancyStatus)
VALUES(N'101', 2, 2, 1350, 1100, N'Available'),
(N'102', 1, 1, 1150, 850, N'Needs Maintenance'),
(N'103', 1, 1, 1150, 850, N'Available'),
(N'104', 3, 2, 1500, 1250, N'Available'),
(N'105', 2, 1, 1250, 1000, N'Not Ready'),
(N'106', 3, 2, 1550, 1250, N'Available'),
(N'107', 3, 2, 1450, 1250, N'Needs Maintenance'),
(N'108', 1, 1, 1100, 850, N'Available'),
(N'109', 2, 2, 1350, 1100, N'Available'),
(N'110', 1, 1, 1050, 850, N'Unknown'),
(N'111', 2, 2, 1350, 1100, N'Needs Maintenance'),
(N'112', 2, 1, 1285, 1000, N'Available'),
(N'201', 2, 1, 1185, 1000, N'Unknown'),
(N'202', 1, 1, 1150, 850, N'Available'),
(N'203', 1, 1, 1150, 850, N'Available'),
(N'204', 3, 2, 1600, 1250, N'Available'),
(N'205', 2, 1, 1100, 1000, N'Needs Maintenance'),
(N'206', 3, 2, 1500, 1250, N'Available'),
(N'207', 3, 2, 1550, 1250, N'Available'),
(N'208', 1, 1, 985, 850, N'Available'),
(N'209', 2, 2, 1350, 1100, N'Available'),
(N'210', 1, 1, 1150, 850, N'Unknown'),
(N'211', 2, 2, 1350, 1100, N'Available'),
(N'212', 2, 1, 1075, 1000, N'Available'),
(N'301', 2, 2, 1175, 1000, N'Available'),
(N'302', 1, 1, 1150, 850, N'Needs Maintenance'),
(N'303', 1, 1, 1100, 850, N'Available'),
(N'304', 3, 2, 1250, 1100, N'Available'),
(N'305', 2, 1, 1100, 1000, N'Needs Maintenance'),
(N'306', 3, 2, 1250, 1100, N'Available'),
(N'307', 3, 2, 1100, 1250, N'Available'),
(N'308', 1, 1, 1100, 850, N'Available'),
(N'309', 2, 2, 1100, 950, N'Available'),
(N'310', 1, 1, 1100, 850, N'Available'),
(N'311', 2, 2, 1100, 1000, N'Unknown'),
(N'312', 2, 1, 1100, 1000, N'Available');
GO
| Contract # | Processed By | Contract Date | First Name | Last Name | Marital Status | # of Children | Unit # | Rent Start Date |
| 1001 | 38408 | 6/12/2018 | Ann | Sanders | Married | 1 | 109 | 7/1/2018 |
| 1002 | 20448 | 6/15/2018 | Mahty | Shaoul | 2 | 104 | 9/1/2018 | |
| 1003 | 40685 | 6/22/2018 | Frank | Ulm | Single | 0 | 302 | 7/1/2018 |
| 1004 | 93947 | 6/22/2018 | Elise | Provowski | Separated | 1 | 305 | 8/1/2018 |
| 1005 | 93947 | 7/23/2018 | Grace | Curryan | 1 | 105 | 9/1/2018 | |
| 1006 | 38408 | 7/25/2018 | Tracy | Warrens | Divorced | 2 | 307 | 8/1/2018 |
| 1007 | 38408 | 8/1/2018 | Paul | Yamo | Married | 3 | 204 | 10/1/2018 |
| 1008 | 40685 | 8/10/2018 | Nancy | Shermann | Single | 1 | 108 | 9/1/2018 |
| 1009 | 20448 | 9/12/2018 | Michael | Tiernan | 0 | 209 | 11/1/2018 | |
| 1010 | 38408 | 10/5/2018 | Phillippe | Anderson | Single | 0 | 202 | 11/1/2018 |
INSERT INTO Management.RentalContracts(ContractNumber, EmployeeID, ContractDate, FirstName, LastName, MaritalStatus, NumberOfChildren, ApartmentID, RentStartDate)
VALUES(1001, 5, N'6/12/2018', N'Ann', N'Sanders', N'Married', 1, 9, N'7/1/2018'),
(1002, 6, N'6/15/2018', N'Mahty', N'Shaoul', NULL, 2, 4, N'9/1/2018'),
(1003, 2, N'6/22/2018', N'Frank', N'Ulm', N'Single', 0, 3, N'7/1/2018'),
(1004, 1, N'6/22/2018', N'Elise', N'Provowski', N'Separated', 1, 29, N'8/1/2018'),
(1005, 1, N'7/23/2018', N'Grace', N'Curryan', NULL, 1, 5, N'9/1/2018'),
(1006, 5, N'7/25/2018', N'Tracy', N'Warrens', N'Divorced', 2, 31, N'8/1/2018'),
(1007, 5, N'8/1/2018', N'Paul', N'Yamo', N'Married', 3, 16, N'10/1/2018'),
(1008, 2, N'8/10/2018', N'Nancy', N'Shermann', N'Single', 1, 8, N'9/1/2018'),
(1009, 6, N'9/12/2018', N'Michael', N'Tiernan', NULL, 0, 21, N'11/1/2018'),
(1010, 5, N'10/5/2018', N'Phillippe', N'Anderson', N'Single', 0, 14, N'11/1/2018');
GO
| Receipt # | Employee # | Contract # | Payment Date | Amount | Notes |
| 100001 | 20448 | 1002 | 06/15/2018 | 1250 | This is the payment of the security deposit |
| 100002 | 38408 | 1001 | 06/17/2018 | 1100 | This is the first payment of the tenant. It is for the security deposit. |
| 100003 | 40685 | 1004 | 06/22/2018 | 1000 | Security deposit payment |
| 100004 | 93947 | 1003 | 06/25/2018 | 850 | This was the security deposit payment |
| 100005 | 40685 | 1003 | 07/26/2018 | 1100 | This was the July 2018 rent payment |
| 100006 | 38408 | 1001 | 07/31/2018 | 1350 | Rent payment for July 2018 |
| 100007 | 40685 | 1004 | 08/25/2018 | 1100 | August 2018 rent payment |
| 100008 | 20448 | 1003 | 08/30/2018 | 1100 | August 2018 rent payment |
| 100009 | 20448 | 1001 | 08/31/2018 | 1350 | Rent payment for August 2018 |
| 100010 | 93947 | 1002 | 09/26/2018 | 1500 | Rent payment - September 2018 |
| 100011 | 38408 | 1003 | 09/27/2018 | 1100 | Rent payment for September 2018 |
| 100012 | 93947 | 1001 | 09/28/2018 | 1350 | This is the rent payment for September 2018 |
| 100013 | 20448 | 1004 | 09/29/2018 | 1100 | September 2018 monthly payment |
| 100014 | 93947 | 1003 | 10/28/2018 | 1100 | October 2018 Rent |
| 100015 | 20448 | 1002 | 10/29/2018 | 1500 | This was the payment for October 2018 |
INSERT INTO Management.Payments(ReceiptNumber, EmployeeID, RentalContractID, PaymentDate, Amount, Notes)
VALUES(100001, 6, 2, N'06/15/2018', 1250, N'This is the payment of the security deposit'),
(100002, 5, 1, N'06/17/2018', 1100, N'This is the payment of the security deposit'),
(100003, 2, 4, N'06/22/2018', 1000, N'Security deposit payment'),
(100004, 1, 3, N'06/25/2018', 850, N'This was the security deposit payment'),
(100005, 2, 3, N'07/26/2018', 1100, N'This was the July 2018 rent payment'),
(100006, 5, 1, N'07/31/2018', 1350, N'Rent payment for July 2018'),
(100007, 2, 4, N'08/25/2018', 1100, N'August 2018 rent payment'),
(100008, 6, 3, N'08/30/2018', 1100, N'August 2018 rent payment'),
(100009, 6, 1, N'08/31/2018', 1350, N'Rent payment for August 2018'),
(100010, 1, 2, N'09/26/2018', 1500, N'Rent payment - September 2018'),
(100011, 5, 3, N'09/27/2018', 1100, N'Rent payment for September 2018'),
(100012, 1, 1, N'09/28/2018', 1350, N'This is the rent payment for September 2018'),
(100013, 6, 4, N'09/29/2018', 1100, N'September 2018 monthly payment'),
(100014, 1, 3, N'10/28/2018', 1100, N'October 2018 Rent'),
(100015, 6, 2, N'10/29/2018', 1500, N'This was the payment for October 2018'),
(100016, 1, 1, N'10/30/2018', 1350, N'Rent for October 2018'),
(100017, 5, 4, N'10/30/2018', 1100, N'Rent payment for October 2018'),
(100018, 6, 1, N'11/26/2018', 1350, N'November 2018 Rent'),
(100019, 2, 3, N'11/29/2018', 1100, N'November 2018 Rent Payment'),
(100020, 6, 2, N'11/30/2018', 1500, N'This is the rent payment for November 2018'),
(100021, 2, 4, N'11/30/2018', 1100, N'Rent payment for November 2018'),
(100022, 1, 2, N'12/27/2018', 1500, N'Rent payment - December 2018'),
(100023, 6, 4, N'12/30/2018', 1100, N'December 2018 rent payment'),
(100024, 5, 3, N'12/31/2018', 1100, N'Rent payment for December 2018'),
(100025, 5, 1, N'01/04/2019', 1350, N'This was rent for December 2018'),
(100026, 2, 4, N'01/20/2019', 1100, N'January 2019 rent payment'),
(100027, 5, 3, N'01/26/2019', 1100, N'January 2019 rent payment'),
(100028, 5, 2, N'01/28/2019', 1500, N'January 2019 - Rent Payment'),
(100029, 6, 1, N'02/02/2019', 1350, N'This was rent for January 2018'),
(100030, 5, 4, N'02/25/2019', 1100, N'February 2019 rent payment'),
(100031, 6, 3, N'02/26/2019', 1100, N'Rent payment for February 2019'),
(100032, 6, 2, N'02/27/2019', 1500, N'February 2019 Rent'),
(100033, 5, 1, N'03/01/2019', 1350, N'Rent for February 2019'),
(100034, 2, 3, N'03/27/2019', 1100, N'Rent payment for March 2019'),
(100035, 6, 4, N'03/29/2019', 1100, N'March 2019 rent payment'),
(100036, 5, 2, N'03/30/2019', 1500, N'Rent payment for March 2019'),
(100037, 5, 1, N'03/31/2019', 1350, N'March 2018 Rent');
GO
Creating a Join
Once you have the necessary tables and their columns, you can create a join. To do this visually, in the Object Explorer of Microsoft SQL Server Management Studio, right-click the connection or any of its nodes and click New Query. The basic formula to create a join is:
SELECT column(s) FROM child-table type-of-join parent-table ON condition
The child-table specifies the table that holds the records that will be retrieved. It can be represented as follows:
SELECT column(s)
FROM Students
type-of-join parent-table
ON condition
The parent-table specifies the table that holds the column with the primary key that will control what records, related to the child table, will be selected. This would be represented as follows:
SELECT column(s)
FROM Students
type-of-join Majors
ON condition
The condition is a logical expression used to validate the records that will be isolated. To create the condition, you should assign the primary key column of the parent table to the foreign key column of the child table. Because both columns likely have the same name, to distinguish them, their names should be qualified. This would be done as follows:
SELECT column(s)
FROM Students
type-of-join Majors
ON Students.MajorCode = Majors.MajorCode
The column(s) of our formula allow(s) you to create a list of the columns you want to include in your statement. As you should be aware, you can include all columns by using the * operator. Here is an example:
SELECT * FROM Students type-of-join MajorCode ON Students.MajorCode = Majors.MajorCode
In this case, all columns from all tables would be included in the result. Instead of all columns, you may want a restricted list. In this case, create the list after the SELECT keyword separating them with commas. You can use the name of a column normally if that name is not duplicated in more than one column. Here is an example:
SELECT LastName, FirstName, Gender
FROM Students
type-of-Join EmploymentsStatus
ON Students.StatusCode = Majors.MajorCode
If the same name of a column is found in more than one table, as is the case for a primary-foreign key combination, you should qualify the name of the column by preceding it with the name of its parent table followed by a period. Here are examples:
SELECT LastName,
FirstName,
Students.MajorCode Majors.MajorCode
FROM Students
type-of-join MajorCode
ON Students.MajorCode = Majors.MajorCode
In fact, to make your code easier to read, you should qualify the name of each column of your SELECT statement. Here are examples:
SELECT Students.LastName,
Students.FirstName,
Students.MajorCode,
Majors.MajorCode
FROM Students
type-of-join MajorCode
ON Students.MajorCode = Majors.MajorCode
Cross and Inner Joins
Introduction
When studying data relationships, we saw the roles of the primary and foreign keys in maintaining the exchange of information between two tables. This technique of linking tables plays a major part when creating a join. It allows you to decide whether you want to include all records or only isolate some of them. To respect the direction of a relationship between two tables as it is applied to a query, Transact-SQL supports three types of joins.
Cross Joins
A cross join creates a list of all records from both tables as follows: the first record from the parent table is associated to each record from the child table, then the second record from the parent table is associated to each record from the child table, and so on. In this case also, there is no need of a common column between both tables. In other words, you will not use the ON clause.
To create a cross join, you can replace the type-of-join of our formula with CROSS JOIN or CROSS OUTER JOIN. Here is an example:
SELECT ContractNumber AS [Contract #],
HumanResources.Employees.EmployeeNumber + N': ' +
HumanResources.Employees.FirstName + N' ' +
HumanResources.Employees.LastName AS [Processed By],
ContractDate [Contract Date],
Management.RentalContracts.FirstName + N' ' + Management.RentalContracts.LastName AS Tenant,
MaritalStatus AS [Marital Status],
NumberOfChildren As Children,
ApartmentID AS [Unit #],
RentStartDate AS [Contract Start Date]
FROM HumanResources.Employees
CROSS JOIN Management.RentalContracts;;
GO
This would produce:
Inner Joins
Imagine you have two tables that can be linked through one's primary key and another's foreign key. Some records in the child table may not have an entry for the foreign key column and would be marked with NULL by the database engine. When creating a query of records of the child table, if you want your list to include only records that have an entry, you can create it as an inner join.
To create an inner join, specify the type-of-join of our formula with the expression INNER JOIN. Here is an example:
SELECT Students.StudentNumber,
Students.FirstName,
Students.LastName,
Students.MajorCode,
Majors.MajorCode AS [Major]
FROM Students INNER JOIN Majors ON Students.MajorCode = Majors.MajorCode;
GO
This would produce:
Notice that, because the query doesn't include students who don't have a major, those are students whose MajorCode is NULL, it (the query) produces 15 records.
An alternative to the INNER JOIN expression is to simply type JOIN. Here is an example:
SELECT Students.StudentNumber,
Students.FirstName,
Students.LastName,
Students.MajorCode,
Majors.MajorCode AS [Major]
FROM Students JOIN Majors ON Students.MajorCode = Majors.MajorCode;
GO
We mentioned earlier that you could include all columns in the query. In our result, since we are more interested in the major of each Students record, we would not need the MajorCode column from the Majors table. Instead, we can access the MajorName. Here is an example:
SELECT Students.StudentNumber,
Students.FirstName,
Students.LastName,
Majors.MajorName AS [Major]
FROM Students JOIN Majors ON Students.MajorCode = Majors.MajorCode;
GO
This would produce:
Obviously, once again, we get 15 records.
Practical Learning: Creating an Inner Join
using System;
using System.Collections.Generic;
using System.Linq;
using System.ComponentModel.DataAnnotations;
namespace ApartmentsRentalManagement1.Models
{
public class RentalContract
{
[Display(Name = "Rent Contract ID")]
public int RentalContractID { get; set; }
[Display(Name = "Contract #")]
public int ContractNumber { get; set; }
[Display(Name = "Employee")]
public int EmployeeID { get; set; }
// [DataType(DataType.Date)]
[Display(Name = "Contract Date")]
public DateTime ContractDate { get; set; }
[Display(Name = "First Name")]
public string FirstName { get; set; }
[Display(Name = "Last Name")]
public string LastName { get; set; }
[Display(Name = "Marital Status")]
public string MaritalStatus { get; set; }
[Display(Name = "Children")]
public int NumberOfChildren { get; set; }
[Display(Name = "Apartment")]
public int ApartmentID { get; set; }
// [DataType(DataType.Date)]
[Display(Name = "Rent Start Date")]
public DateTime RentStartDate { get; set; }
public string Description
{
get
{
return ContractNumber + " - " + FirstName + " " + LastName + " (renting since " + RentStartDate + ")";
}
}
public virtual Employee Employee { get; set; }
}
public class ContractEvaluation : RentalContract
{
public string Clerk { get; set; }
public string Residence { get; set; }
}
}USE PropertiesRentalManagement; GO SELECT ContractNumber AS [Contract #], HumanResources.Employees.EmployeeNumber + N': ' + HumanResources.Employees.FirstName + N' ' + HumanResources.Employees.LastName AS [Processed By], ContractDate [Contract Date], Management.RentalContracts.FirstName + N' ' + Management.RentalContracts.LastName AS Tenant, MaritalStatus AS [Marital Status], NumberOfChildren As Children, PropertyNumber AS [Property #], RentStartDate AS [Contract Start Date] FROM HumanResources.Employees INNER JOIN Management.RentalContracts ; GO
Outer Joins
A Left Outer Join
A left outer join produces all records of the child table, also called the right table. The records of the child table that do not have an entry in the foreign key column are marked as NULL.
To create a left outer join, replace the type-of-join our formula with the LEFT OUTER JOIN expression. Here is an example:
SELECT Students.StudentNumber,
Students.FirstName,
Students.LastName,
Majors.MajorCode
FROM Students
LEFT OUTER JOIN Majors
ON Students.MajorCode = Majors.MajorCode;
GO
This would produce:
You can omit the OUTER keyword and get the same result. Here is an example:
SELECT StudentNumber,
FirstName,
LastName,
MajorName
FROM Students
LEFT JOIN Majors
ON Students.MajorCode = Majors.MajorCode;
GO
This would produce:
Notice that the result includes all records of the Students (also called the right) table and the records that don't have an entry in the MajorCode column of the Students (the right) table are marked with NULL.
USE PropertiesRentalManagement;
GO
SELECT ContractNumber AS [Contract #],
HumanResources.Employees.EmployeeNumber + N': ' +
HumanResources.Employees.FirstName + N' ' +
HumanResources.Employees.LastName AS [Processed By],
ContractDate [Contract Date],
Management.RentalContracts.FirstName + N' ' + Management.RentalContracts.LastName AS Tenant,
MaritalStatus AS [Marital Status],
NumberOfChildren As Children,
PropertyNumber AS [Property #],
RentStartDate AS [Contract Start Date]
FROM HumanResources.Employees
LEFT OUTER JOIN Management.RentalContracts
ON Management.RentalContracts.EmployeeNumber LIKE HumanResources.Employees.EmployeeNumber;
GO
To create a right outer join in SQL, replace the type-of-join factor of our formula with the RIGHT OUTER JOIN expression. Here is an example:
SELECT Students.StudentNumber,
Students.FirstName,
Students.LastName,
Majors.MajorCode
FROM Students
RIGHT OUTER JOIN Majors
ON Students.MajorCode = Majors.MajorCode;
GO
This would produce:
You can omit the OUTER keyword and get the same result. Here is an example:
SELECT StudentNumber,
FirstName,
LastName,
MajorName
FROM Students
RIGHT JOIN Majors
ON Students.MajorCode = Majors.MajorCode;
GO
This would produce:
Practical Learning:
Creating a Right Outer Join
To create a left outer join, change the SQL statement as
follows:
USE PropertiesRentalManagement;
GO
SELECT ContractNumber AS [Contract #],
HumanResources.Employees.EmployeeNumber + N': ' +
HumanResources.Employees.FirstName + N' ' +
HumanResources.Employees.LastName AS [Processed By],
ContractDate [Contract Date],
Management.RentalContracts.FirstName + N' ' + Management.RentalContracts.LastName AS Tenant,
MaritalStatus AS [Marital Status],
NumberOfChildren As Children,
PropertyNumber AS [Property #],
RentStartDate AS [Contract Start Date]
FROM HumanResources.Employees
RIGHT OUTER JOIN Management.RentalContracts
ON Management.RentalContracts.EmployeeNumber LIKE HumanResources.Employees.EmployeeNumber;
GO
To create a full outer join in SQL, NULL the FULL OUTER JOIN expression. Here is an example:
SELECT Students.StudentNumber,
Students.FirstName,
Students.LastName,
Majors.MajorCode
FROM Students
FULL OUTER JOIN Majors
ON Students.MajorCode = Majors.MajorCode;
GO
This would produce:
You can omit the OUTER keyword and get the same result. Here is an example:
SELECT StudentNumber,
FirstName,
LastName,
MajorName
FROM Students
FULL JOIN Majors
ON Students.MajorCode = Majors.MajorCode;
GO
This would produce:
USE PropertiesRentalManagement;
GO
SELECT ContractNumber AS [Contract #],
HumanResources.Employees.EmployeeNumber + N': ' +
HumanResources.Employees.FirstName + N' ' +
HumanResources.Employees.LastName AS [Processed By],
ContractDate [Contract Date],
Management.RentalContracts.FirstName + N' ' + Management.RentalContracts.LastName AS Tenant,
MaritalStatus AS [Marital Status],
NumberOfChildren As Children,
PropertyNumber AS [Property #],
RentStartDate AS [Contract Start Date]
FROM HumanResources.Employees
FULL OUTER JOIN Management.RentalContracts
ON Management.RentalContracts.EmployeeNumber LIKE HumanResources.Employees.EmployeeNumber;
GO
|
|
||
| Home | Copyright © 2007-2019, FunctionX | Home |
|
|
||