ASP.NET MVC ADO.NET: Apartment Rental Management
ASP.NET MVC ADO.NET: 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>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<ApartmentsRentalManagement1.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 ApartmentsRentalManagement1.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 ApartmentsRentalManagement1.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 ApartmentsRentalManagement1.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 ApartmentsRentalManagement1.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<ApartmentsRentalManagement1.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 ApartmentsRentalManagement1.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 ApartmentsRentalManagement1.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 ApartmentsRentalManagement1.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 ApartmentsRentalManagement1.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>@{
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>
| 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/2020 | Ann | Sanders | Married | 1 | 109 | 7/1/2020 |
| 1002 | 20448 | 6/15/2020 | Mahty | Shaoul | 2 | 104 | 9/1/2020 | |
| 1003 | 40685 | 6/22/2020 | Frank | Ulm | Single | 0 | 302 | 7/1/2020 |
| 1004 | 93947 | 6/22/2020 | Elise | Provowski | Separated | 1 | 305 | 8/1/2020 |
| 1005 | 93947 | 7/23/2020 | Grace | Curryan | 1 | 105 | 9/1/2020 | |
| 1006 | 38408 | 7/25/2020 | Tracy | Warrens | Divorced | 2 | 307 | 8/1/2020 |
| 1007 | 38408 | 8/1/2020 | Paul | Yamo | Married | 3 | 204 | 10/1/2020 |
| 1008 | 40685 | 8/10/2020 | Nancy | Shermann | Single | 1 | 108 | 9/1/2020 |
| 1009 | 20448 | 9/12/2020 | Michael | Tiernan | 0 | 209 | 11/1/2020 | |
| 1010 | 38408 | 10/5/2020 | Phillippe | Anderson | Single | 0 | 202 | 11/1/2020 |
INSERT INTO Management.RentalContracts(ContractNumber, EmployeeID, ContractDate, FirstName, LastName, MaritalStatus, NumberOfChildren, ApartmentID, RentStartDate)
VALUES(1001, 5, N'6/12/2020', N'Ann', N'Sanders', N'Married', 1, 9, N'7/1/2020'),
(1002, 6, N'6/15/2020', N'Mahty', N'Shaoul', NULL, 2, 4, N'9/1/2020'),
(1003, 2, N'6/22/2020', N'Frank', N'Ulm', N'Single', 0, 3, N'7/1/2020'),
(1004, 1, N'6/22/2020', N'Elise', N'Provowski', N'Separated', 1, 29, N'8/1/2020'),
(1005, 1, N'7/23/2020', N'Grace', N'Curryan', NULL, 1, 5, N'9/1/2020'),
(1006, 5, N'7/25/2020', N'Tracy', N'Warrens', N'Divorced', 2, 31, N'8/1/2020'),
(1007, 5, N'8/1/2020', N'Paul', N'Yamo', N'Married', 3, 16, N'10/1/2020'),
(1008, 2, N'8/10/2020', N'Nancy', N'Shermann', N'Single', 1, 8, N'9/1/2020'),
(1009, 6, N'9/12/2020', N'Michael', N'Tiernan', NULL, 0, 21, N'11/1/2020'),
(1010, 5, N'10/5/2020', N'Phillippe', N'Anderson', N'Single', 0, 14, N'11/1/2020');
GO
| Receipt # | Employee # | Contract # | Payment Date | Amount | Notes |
| 100001 | 20448 | 1002 | 06/15/2020 | 1250 | This is the payment of the security deposit |
| 100002 | 38408 | 1001 | 06/17/2020 | 1100 | This is the first payment of the tenant. It is for the security deposit. |
| 100003 | 40685 | 1004 | 06/22/2020 | 1000 | Security deposit payment |
| 100004 | 93947 | 1003 | 06/25/2020 | 850 | This was the security deposit payment |
| 100005 | 40685 | 1003 | 07/26/2020 | 1100 | This was the July 2018 rent payment |
| 100006 | 38408 | 1001 | 07/31/2020 | 1350 | Rent payment for July 2020 |
| 100007 | 40685 | 1004 | 08/25/2020 | 1100 | August 2018 rent payment |
| 100008 | 20448 | 1003 | 08/30/2020 | 1100 | August 2018 rent payment |
| 100009 | 20448 | 1001 | 08/31/2020 | 1350 | Rent payment for August 2020 |
| 100010 | 93947 | 1002 | 09/26/2020 | 1500 | Rent payment - September 2020 |
| 100011 | 38408 | 1003 | 09/27/2020 | 1100 | Rent payment for September 2020 |
| 100012 | 93947 | 1001 | 09/28/2020 | 1350 | This is the rent payment for September 2020 |
| 100013 | 20448 | 1004 | 09/29/2020 | 1100 | September 2018 monthly payment |
| 100014 | 93947 | 1003 | 10/28/2020 | 1100 | October 2018 Rent |
| 100015 | 20448 | 1002 | 10/29/2020 | 1500 | This was the payment for October 2020 |
INSERT INTO Management.Payments(ReceiptNumber, EmployeeID, RentalContractID, PaymentDate, Amount, Notes)
VALUES(100001, 6, 2, N'06/15/2020', 1250, N'This is the payment of the security deposit'),
(100002, 5, 1, N'06/17/2020', 1100, N'This is the payment of the security deposit'),
(100003, 2, 4, N'06/22/2020', 1000, N'Security deposit payment'),
(100004, 1, 3, N'06/25/2020', 850, N'This was the security deposit payment'),
(100005, 2, 3, N'07/26/2020', 1100, N'This was the July 2018 rent payment'),
(100006, 5, 1, N'07/31/2020', 1350, N'Rent payment for July 2018'),
(100007, 2, 4, N'08/25/2020', 1100, N'August 2018 rent payment'),
(100008, 6, 3, N'08/30/2020', 1100, N'August 2018 rent payment'),
(100009, 6, 1, N'08/31/2020', 1350, N'Rent payment for August 2018'),
(100010, 1, 2, N'09/26/2020', 1500, N'Rent payment - September 2018'),
(100011, 5, 3, N'09/27/2020', 1100, N'Rent payment for September 2018'),
(100012, 1, 1, N'09/28/2020', 1350, N'This is the rent payment for September 2018'),
(100013, 6, 4, N'09/29/2020', 1100, N'September 2018 monthly payment'),
(100014, 1, 3, N'10/28/2020', 1100, N'October 2018 Rent'),
(100015, 6, 2, N'10/29/2020', 1500, N'This was the payment for October 2018'),
(100016, 1, 1, N'10/30/2020', 1350, N'Rent for October 2018'),
(100017, 5, 4, N'10/30/2020', 1100, N'Rent payment for October 2018'),
(100018, 6, 1, N'11/26/2020', 1350, N'November 2018 Rent'),
(100019, 2, 3, N'11/29/2020', 1100, N'November 2018 Rent Payment'),
(100020, 6, 2, N'11/30/2020', 1500, N'This is the rent payment for November 2018'),
(100021, 2, 4, N'11/30/2020', 1100, N'Rent payment for November 2018'),
(100022, 1, 2, N'12/27/2020', 1500, N'Rent payment - December 2018'),
(100023, 6, 4, N'12/30/2020', 1100, N'December 2018 rent payment'),
(100024, 5, 3, N'12/31/2020', 1100, N'Rent payment for December 2018'),
(100025, 5, 1, N'01/04/2021', 1350, N'This was rent for December 2018'),
(100026, 2, 4, N'01/20/2021', 1100, N'January 2019 rent payment'),
(100027, 5, 3, N'01/26/2021', 1100, N'January 2019 rent payment'),
(100028, 5, 2, N'01/28/2021', 1500, N'January 2019 - Rent Payment'),
(100029, 6, 1, N'02/02/2021', 1350, N'This was rent for January 2018'),
(100030, 5, 4, N'02/25/2021', 1100, N'February 2019 rent payment'),
(100031, 6, 3, N'02/26/2021', 1100, N'Rent payment for February 2021'),
(100032, 6, 2, N'02/27/2021', 1500, N'February 2019 Rent'),
(100033, 5, 1, N'03/01/2021', 1350, N'Rent for February 2021'),
(100034, 2, 3, N'03/27/2021', 1100, N'Rent payment for March 2021'),
(100035, 6, 4, N'03/29/2021', 1100, N'March 2019 rent payment'),
(100036, 5, 2, N'03/30/2021', 1500, N'Rent payment for March 2021'),
(100037, 5, 1, N'03/31/2021', 1350, N'March 2018 Rent');
GO
|
|
||
| Home | Copyright © 2007-2019, FunctionX | Home |
|
|
||