The Tables of a Join
The Tables of a Join
Introducing the Tables of a Join
Introduction
Before creating a join, you must have the tables that would be involved. The tables are created using the techniques we have seen in previous lessons. It is also important to create a primary key for each table. The parent table would usually need only this primary key that would be used to "link" it to a child table. If needed, you can then create the necessary records for the table. Here is an example:
CREATE TABLE Majors ( MajorCode nchar(4) not null, MajorName nvarchar(50), CONSTRAINT PK_Majors PRIMARY KEY(MajorCode) ); GO INSERT INTO Majors(MajorCode, MajorName) VALUES(N'CMSC', N'Computer Science'),(N'BIOL', N'Biology'), (N'ACCT', N'Accounting'), (N'ENGL', N'English'); GO
When creating the child table, remember to create a column that would serve as the link with the parent table. By a (good) habit as we saw when studying relationships, the name and the data type of this column are the same as the primary key of the parent table. Here is an example:
CREATE TABLE Majors
(
MajorCode nchar(4) not null,
MajorName nvarchar(50),
CONSTRAINT PK_Majors PRIMARY KEY(MajorCode)
);
GO
CREATE TABLE Students
(
StudentNumber int identity not null,
FirstName nvarchar(20),
LastName nvarchar(20),
MajorCode nchar(4),
CONSTRAINT PK_Students PRIMARY KEY(StudentNumber)
);
GO
INSERT INTO Majors(MajorCode, MajorName)
VALUES(N'CMSC', N'Computer Science'),(N'BIOL', N'Biology'),
(N'ACCT', N'Accounting'), (N'ENGL', N'English');
GO
INSERT INTO Students(FirstName, LastName, MajorCode)
VALUES(N'John', N'Franks', N'ACCT'), (N'Peter', N'Sonnens', N'BIOL'),
(N'Leslie', N'Aronson', NULL), (N'Mary', N'Shamberg', N'CMSC'),
(N'Chryssa', N'Lurie', N'CMSC'), (N'Hellah', N'Zanogh', N'ENGL'),
(N'Olympia', N'Sumners', N'CMSC'), (N'Roberta', N'Jerseys', N'CMSC'),
(N'Helène', N'Campo', NULL), (NULL, N'Millam', N'ACCT'),
(NULL, N'Hessia', N'ENGL'), (N'Stanley', N'Webbs', N'CMSC'),
(N'Arnie', N'Ephron', N'ENGL'), (N'Mike', N'Pastore', N'ACCT'),
(NULL, N'Salim', NULL), (N'Mary', N'Shamberg', N'CMSC'),
(N'Chryssa', N'Lurie', N'CMSC'), (NULL, N'Millers', NULL),
(N'Robert', NULL, N'BIOL');
GO
Notice that the table of Students contains 19 records including studing who have no major:
Practical Learning: Introducing the Tables of a Join
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.RentContracts ( RentContractID 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 FK_Registrars FOREIGN KEY(EmployeeID) REFERENCES HumanResources.Employees(EmployeeID), CONSTRAINT FK_Apartments FOREIGN KEY(ApartmentID) REFERENCES Management.Apartments(ApartmentID), CONSTRAINT PK_Contracts PRIMARY KEY(RentContractID) ); GO CREATE TABLE Management.Payments ( PaymentID INT IDENTITY(1, 1), ReceiptNumber INT, EmployeeID INT, RentContractID INT, PaymentDate DATE, Amount INT, Notes NVARCHAR(MAX) NOT NULL, CONSTRAINT FK_ProcessedBy FOREIGN KEY(EmployeeID) REFERENCES HumanResources.Employees(EmployeeID), CONSTRAINT FK_Contracts FOREIGN KEY(RentContractID) REFERENCES Management.RentContracts(RentContractID), CONSTRAINT PK_Payments PRIMARY KEY(PaymentID) ); GO
<?xml version="1.0" encoding="utf-8"?>
<!--
For more information on how to configure your ASP.NET application, please visit
https://go.microsoft.com/fwlink/?LinkId=301880
-->
<configuration>
<appSettings>
<add key="webpages:Version" value="3.0.0.0"/>
<add key="webpages:Enabled" value="false"/>
<add key="ClientValidationEnabled" value="true"/>
<add key="UnobtrusiveJavaScriptEnabled" value="true"/>
</appSettings>
<system.web>
<compilation debug="true" targetFramework="4.6.1"/>
<httpRuntime targetFramework="4.6.1"/>
<httpModules>
<add name="ApplicationInsightsWebTracking" type="Microsoft.ApplicationInsights.Web.ApplicationInsightsHttpModule, Microsoft.AI.Web"/>
</httpModules>
</system.web>
<connectionStrings>
<add name="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); }
.jumbotron { background-color: white; }
.common-font { font-family: Georgia, Garamond, 'Times New Roman', serif; }
.navbar-top-fixed { left: 0;
right: 0;
top: 20em;
z-index: 1100;
position: fixed;
border-width: 0 0 1px; }
.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.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.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.Collections.Generic;
using System.Linq;
using System.ComponentModel.DataAnnotations;
namespace ApartmentsRentalManagement1.Models
{
public class RentContract
{
[Display(Name = "Rent Contract ID")]
public int RentContractID { 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.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 RentContractID { 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<RentContract> GetRentContracts()
{
List<RentContract> rentContracts = new List<RentContract>();
using (SqlConnection scApartmentsManagement = new SqlConnection(System.
Configuration.
ConfigurationManager.
ConnectionStrings["csApartmentsRentalManagement"].
ConnectionString))
{
SqlCommand cmdRentContracts = new SqlCommand("SELECT RentContractID, ContractNumber, EmployeeID, " +
" ContractDate, FirstName, LastName, " +
" MaritalStatus, NumberOfChildren, " +
" ApartmentID, RentStartDate " +
"FROM Management.RentContracts;",
scApartmentsManagement);
scApartmentsManagement.Open();
cmdRentContracts.ExecuteNonQuery();
SqlDataAdapter sdaRentContracts = new SqlDataAdapter(cmdRentContracts);
DataSet dsRentContracts = new DataSet("rent-contracts");
sdaRentContracts.Fill(dsRentContracts);
for (int i = 0; i < dsRentContracts.Tables[0].Rows.Count; i++)
{
RentContract contract = new RentContract()
{
RentContractID = int.Parse(dsRentContracts.Tables[0].Rows[i][0].ToString()),
ContractNumber = int.Parse(dsRentContracts.Tables[0].Rows[i][1].ToString()),
EmployeeID = int.Parse(dsRentContracts.Tables[0].Rows[i][2].ToString()),
ContractDate = DateTime.Parse(dsRentContracts.Tables[0].Rows[i][3].ToString()),
FirstName = dsRentContracts.Tables[0].Rows[i][4].ToString(),
LastName = dsRentContracts.Tables[0].Rows[i][5].ToString(),
MaritalStatus = dsRentContracts.Tables[0].Rows[i][6].ToString(),
NumberOfChildren = int.Parse(dsRentContracts.Tables[0].Rows[i][7].ToString()),
ApartmentID = int.Parse(dsRentContracts.Tables[0].Rows[i][8].ToString()),
RentStartDate = DateTime.Parse(dsRentContracts.Tables[0].Rows[i][9].ToString())
};
rentContracts.Add(contract);
}
}
return rentContracts;
}
public RentContract FindRentContract(int? id)
{
RentContract contract = null;
foreach (var rent in GetRentContracts())
{
if (rent.RentContractID == 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, " +
" RentContractID, 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()),
RentContractID = 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;
}
}
}<!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", "RentContracts")</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", "RentContracts")</li>
<li>@Html.ActionLink("Payments", "Index", "Payments")</li>
<li>@Html.ActionLink("About ARM", "About", "Home")</li>
<li>@Html.ActionLink("Contact Us", "Contact", "Home")</li>
</ul>
</div>
</div>
</div>
<div class="container body-content">
@RenderBody()
<hr />
<footer>
<p class="text-center common-font blue">© @DateTime.Now.Year - Apartment Rental Management</p>
</footer>
</div>
@Scripts.Render("~/bundles/jquery")
@Scripts.Render("~/bundles/bootstrap")
@RenderSection("scripts", required: false)
</body>
</html>@{
ViewBag.Title = "Home Page";
}
<div class="jumbotron">
<h1>ASP.NET</h1>
<p class="lead">ASP.NET is a free web framework for building great Web sites and Web applications using HTML, CSS and JavaScript.</p>
</div>
<div class="row">
<div class="col-md-4">
<h2>Getting started</h2>
<p>
ASP.NET MVC gives you a powerful, patterns-based way to build dynamic websites that
enables a clean separation of concerns and gives you full control over markup
for enjoyable, agile development.
</p>
<p><a class="btn btn-default" href="https://go.microsoft.com/fwlink/?LinkId=301865">Learn more »</a></p>
</div>
<div class="col-md-4">
<h2>Get more libraries</h2>
<p>NuGet is a free Visual Studio extension that makes it easy to add, remove, and update libraries and tools in Visual Studio projects.</p>
<p><a class="btn btn-default" href="https://go.microsoft.com/fwlink/?LinkId=301866">Learn more »</a></p>
</div>
<div class="col-md-4">
<h2>Web Hosting</h2>
<p>You can easily find a web hosting company that offers the right mix of features and price for your applications.</p>
<p><a class="btn btn-default" href="https://go.microsoft.com/fwlink/?LinkId=301867">Learn more »</a></p>
</div>
</div>using System.Net;
using System.Web.Mvc;
using System.Data.SqlClient;
using ApartmentsRentalManagement1.Models;
namespace ApartmentsRentalManagement1.Controllers
{
public class EmployeesController : Controller
{
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();
}
// 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 common-font blue">Employee Details</h2>
<hr />
<div>
<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>
@Html.ActionLink("Edit/Update Employee Record", "Edit", new { id = Model.EmployeeID }) ::
@Html.ActionLink("Employees", "Index")
</p>@{
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="form-horizontal common-font">
<div class="form-group">
<label for="emplNbr" class="control-label col-md-2">Employee #</label>
<div class="col-md-10">
@Html.TextBox("EmployeeNumber", null, htmlAttributes: new { @class = "form-control", id = "emplNbr" })
</div>
</div>
<div class="form-group">
<label for="fName" class="control-label col-md-2">First Name</label>
<div class="col-md-10">
@Html.TextBox("FirstName", null, htmlAttributes: new { @class = "form-control", id = "fName" })
</div>
</div>
<div class="form-group">
<label for="lName" class="control-label col-md-2">Last Name</label>
<div class="col-md-10">
@Html.TextBox("LastName", null, htmlAttributes: new { @class = "form-control", id = "lName" })
</div>
</div>
<div class="form-group">
<label for="title" class="control-label col-md-2">Employment Title</label>
<div class="col-md-10">
@Html.TextBox("EmploymentTitle", null, htmlAttributes: new { @class = "form-control", id = "title" })
</div>
</div>
<div class="form-group">
<label class="control-label col-md-2">@Html.ActionLink("Employees", "Index")</label>
<div class="col-md-10">
<input type="submit" value="Hire this Employee" class="btn btn-primary" />
</div>
</div>
</div>
}@model ApartmentsRentalManagement1.Models.Employee
@{
Layout = "~/Views/Shared/_Management.cshtml";
ViewBag.Title = "Edit/Update Employee Information";
}
<h2 class=" bold blue common-font">Edit/Update Employee Information</h2>
<hr />
@using (Html.BeginForm())
{
<div class="form-horizontal common-font">
@Html.Hidden("EmployeeID")
<div class="form-group">
@Html.LabelFor(model => model.EmployeeNumber, htmlAttributes: new { @class = "control-label col-md-2" })
<div class="col-md-10">
@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-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">
@Html.LabelFor(model => model.EmploymentTitle, htmlAttributes: new { @class = "control-label col-md-2" })
<div class="col-md-10">
@Html.EditorFor(model => model.EmploymentTitle, new { htmlAttributes = new { @class = "form-control" } })
</div>
</div>
<div class="form-group">
<label class="control-label col-md-2">@Html.ActionLink("Employees", "Index")</label>
<div class="col-md-10">
<input type="submit" value="Update Employee Record" class="btn btn-primary" />
</div>
</div>
</div>
}@model ApartmentsRentalManagement1.Models.Employee
@{
ViewBag.Title = "Deleting Employee Record";
Layout = "~/Views/Shared/_Management.cshtml";
}
<h2 class="bold common-font blue">Deleting Employee Record</h2>
<hr />
<h3 class="common-font blue">Are you sure you want to delete this employee's record?</h3>
<div>
<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())
{
@Html.AntiForgeryToken()
<div class="form-actions no-color">
@Html.ActionLink("Employees", "Index") ::
<input type="submit" value="Delete Employee Record" class="btn btn-primary" />
</div>
}
</div>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"));
}
}
}
| 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
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 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>
<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>
@Html.ActionLink("Edit Rent Contract", "Edit", new { id = Model.ApartmentID }) |
@Html.ActionLink("Rental Contracts", "Index")
</p>@{
ViewBag.Title = "New Apartment";
Layout = "~/Views/Shared/_Management.cshtml";
}
<h2 class="bold common-font blue">New Apartment</h2>
<hr />
@using (Html.BeginForm())
{
<div class="form-horizontal common-font">
<div class="form-group">
<label for="unitNbr" class="control-label col-md-2">Unit #:</label>
<div class="col-md-10">
@Html.TextBox("UnitNumber", null, htmlAttributes: new { @class = "form-control", id = "unitNbr" })
</div>
</div>
<div class="form-group">
<label for="beds" class="control-label col-md-2">Bedrooms:</label>
<div class="col-md-10">
@Html.TextBox("Bedrooms", null, htmlAttributes: new { @class = "form-control", id = "beds" })
</div>
</div>
<div class="form-group">
<label for="baths" class="control-label col-md-2">Bathrooms:</label>
<div class="col-md-10">
@Html.TextBox("Bathrooms", null, htmlAttributes: new { @class = "form-control", id = "baths" })
</div>
</div>
<div class="form-group">
<label for="rentPerMonth" class="control-label col-md-2">Monthly Rate:</label>
<div class="col-md-10">
@Html.TextBox("MonthlyRate", null, htmlAttributes: new { @class = "form-control", id = "rentPerMonth" })
</div>
</div>
<div class="form-group">
<label for="deposit" class="control-label col-md-2">Security Deposit:</label>
<div class="col-md-10">
@Html.TextBox("SecurityDeposit", null, htmlAttributes: new { @class = "form-control", id = "deposit" })
</div>
</div>
<div class="form-group">
<label for="occupancyStatus" class="control-label col-md-2">Occupancy Status:</label>
<div class="col-md-10">
@Html.DropDownList("OccupancyStatus", null, htmlAttributes: new { @class = "form-control", id = "occupancyStatus" })
</div>
</div>
<div class="form-group">
<label class="control-label col-md-2">@Html.ActionLink("Apartments Inventory", "Index")</label>
<div class="col-md-10">
<input type="submit" value="Create Apartment Record" class="btn btn-primary" />
</div>
</div>
</div>
}@model ApartmentsRentalManagement1.Models.Apartment
@{
Layout = "~/Views/Shared/_Management.cshtml";
ViewBag.Title = "Edit/Update Apartment Information";
}
<h2 class="bold common-font blue">Edit/Update Residence Details</h2>
<hr />
@using (Html.BeginForm())
{
<div class="form-horizontal common-font">
<div class="form-group">
<label for="unitNbr" class="control-label col-md-2">Unit #:</label>
<div class="col-md-10">
@Html.TextBox("UnitNumber", null, htmlAttributes: new { @class = "form-control", id = "unitNbr" })
</div>
</div>
<div class="form-group">
<label for="beds" class="control-label col-md-2">Bedrooms:</label>
<div class="col-md-10">
@Html.TextBox("Bedrooms", null, htmlAttributes: new { @class = "form-control", id = "beds" })
</div>
</div>
<div class="form-group">
<label for="baths" class="control-label col-md-2">Bathrooms:</label>
<div class="col-md-10">
@Html.TextBox("Bathrooms", null, htmlAttributes: new { @class = "form-control", id = "baths" })
</div>
</div>
<div class="form-group">
<label for="rentPerMonth" class="control-label col-md-2">Monthly Rate:</label>
<div class="col-md-10">
@Html.TextBox("MonthlyRate", null, htmlAttributes: new { @class = "form-control", id = "rentPerMonth" })
</div>
</div>
<div class="form-group">
<label for="deposit" class="control-label col-md-2">Security Deposit:</label>
<div class="col-md-10">
@Html.TextBox("SecurityDeposit", null, htmlAttributes: new { @class = "form-control", id = "deposit" })
</div>
</div>
<div class="form-group">
<label for="occupancyStatus" class="control-label col-md-2">Occupancy Status:</label>
<div class="col-md-10">
@Html.DropDownList("OccupancyStatus", null, htmlAttributes: new { @class = "form-control", id = "occupancyStatus" })
</div>
</div>
<div class="form-group">
<label class="control-label col-md-2">@Html.ActionLink("Apartments Inventory", "Index")</label>
<div class="col-md-10">
<input type="submit" value="Update Apartment Information" class="btn btn-primary" />
</div>
</div>
</div>
}@model ApartmentsRentalManagement1.Models.Apartment
@{
Layout = "~/Views/Shared/_Management.cshtml";
ViewBag.Title = "Deleting Apartment/Residence";
}
<h2 class="bold common-font blue">Deleting Apartment/Residence</h2>
<hr />
<h3 class="common-font blue">Are you sure you want to delete this apartment's record?</h3>
<div>
<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>@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>
| 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
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 RentContractsController : Controller
{
BusinessObjects objects = new BusinessObjects();
// GET: RentContracts
public ActionResult Index()
{
return View(objects.GetRentContracts());
}
// GET: RentContracts/Details/5
public ActionResult Details(int id)
{
if (id == 0)
{
return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
}
RentContract contract = objects.FindRentContract(id);
if (contract == null)
{
return HttpNotFound();
}
return View(contract);
}
// GET: RentContracts/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: RentContracts/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 cmdRentContracts = new SqlCommand("INSERT INTO Management.RentContracts(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();
cmdRentContracts.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: RentContracts/Edit/5
public ActionResult Edit(int id)
{
if (id == 0)
{
return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
}
RentContract contract = objects.FindRentContract(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: RentContracts/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.RentContracts " +
"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 RentContractID = " + id + ";";
if (DateTime.Parse(collection["ContractDate"]) != new DateTime(1900, 1, 1))
strUpdate += "UPDATE Management.RentContracts " +
"SET ContractDate = N'" + collection["ContractDate"] + "' " +
"WHERE RentContractID = " + id + ";";
if (DateTime.Parse(collection["RentStartDate"]) != new DateTime(1900, 1, 1))
strUpdate += "UPDATE Management.RentContracts " +
"SET RentStartDate = N'" + collection["RentStartDate"] + "' " +
"WHERE RentContractID = " + id + ";";
SqlCommand cmdRentContracts = new SqlCommand(strUpdate,
scApartmentsManagement);
scApartmentsManagement.Open();
cmdRentContracts.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");
}
RentContract contract = objects.FindRentContract(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: RentContracts/Delete/5
public ActionResult Delete(int id)
{
RentContract contract = null;
if (id == 0)
{
return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
}
using (SqlConnection scRentManagement = new SqlConnection(System.Configuration.
ConfigurationManager.
ConnectionStrings["csApartmentsRentalManagement"].
ConnectionString))
{
SqlCommand cmdRentContracts = new SqlCommand("SELECT RentContractID, ContractNumber, " +
" EmployeeID, ContractDate, " +
" FirstName, LastName, " +
" MaritalStatus, NumberOfChildren, " +
" ApartmentID, RentStartDate " +
"FROM Management.RentContracts " +
"WHERE RentContractID = " + id + ";",
scRentManagement);
scRentManagement.Open();
SqlDataAdapter sdaRentContracts = new SqlDataAdapter(cmdRentContracts);
DataSet dsRentContracts = new DataSet("rent-contracts");
sdaRentContracts.Fill(dsRentContracts);
if (dsRentContracts.Tables[0].Rows.Count > 0)
{
contract = new RentContract()
{
RentContractID = int.Parse(dsRentContracts.Tables[0].Rows[0][0].ToString()),
ContractNumber = int.Parse(dsRentContracts.Tables[0].Rows[0][1].ToString()),
EmployeeID = int.Parse(dsRentContracts.Tables[0].Rows[0][2].ToString()),
ContractDate = DateTime.Parse(dsRentContracts.Tables[0].Rows[0][3].ToString()),
FirstName = dsRentContracts.Tables[0].Rows[0][4].ToString(),
LastName = dsRentContracts.Tables[0].Rows[0][5].ToString(),
MaritalStatus = dsRentContracts.Tables[0].Rows[0][6].ToString(),
NumberOfChildren = int.Parse(dsRentContracts.Tables[0].Rows[0][7].ToString()),
ApartmentID = int.Parse(dsRentContracts.Tables[0].Rows[0][8].ToString()),
RentStartDate = DateTime.Parse(dsRentContracts.Tables[0].Rows[0][9].ToString())
};
}
}
return contract == null ? HttpNotFound() : (ActionResult)View(contract);
}
// POST: RentContracts/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 cmdRentContracts = new SqlCommand("DELETE Management.RentContracts " +
"WHERE RentContractID = " + id + ";",
scApartmentsManagement);
scApartmentsManagement.Open();
cmdRentContracts.ExecuteNonQuery();
}
return RedirectToAction("Index");
}
catch
{
return View();
}
}
}
}@model ApartmentsRentalManagement1.Models.RentContract
@{
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.RentContractID)</dt>
<dd>@Html.DisplayFor(model => model.RentContractID)</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.RentContractID }) |
@Html.ActionLink("Rental Contracts", "Index")
</p>@model ApartmentsRentalManagement1.Models.RentContract
@{
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.RentContract
@{
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.RentContract
@{
ViewBag.Title = "Delete Rent 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.RentContractID)</dt>
<dd>@Html.DisplayFor(model => model.RentContractID)</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>@model IEnumerable<ApartmentsRentalManagement1.Models.RentContract>
@{
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.RentContractID)</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.RentContractID)</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.RentContractID }) |
@Html.ActionLink("Details", "Details", new { id = item.RentContractID }) |
@Html.ActionLink("Delete", "Delete", new { id = item.RentContractID })
</td>
</tr>
}
</table>
| Contract # | Processed By | Contract Date | First Name | Last Name | Marital Status | # of Children | Unit # | Rent Start Date |
| 1001 | 38408 | 6/12/2019 | Ann | Sanders | Married | 1 | 109 | 7/1/2019 |
| 1002 | 20448 | 6/15/2019 | Mahty | Shaoul | 2 | 104 | 9/1/2019 | |
| 1003 | 40685 | 6/22/2019 | Frank | Ulm | Single | 0 | 302 | 7/1/2019 |
| 1004 | 93947 | 6/22/2019 | Elise | Provowski | Separated | 1 | 305 | 8/1/2019 |
| 1005 | 93947 | 7/23/2019 | Grace | Curryan | 1 | 105 | 9/1/2019 | |
| 1006 | 38408 | 7/25/2019 | Tracy | Warrens | Divorced | 2 | 307 | 8/1/2019 |
| 1007 | 38408 | 8/1/2019 | Paul | Yamo | Married | 3 | 204 | 10/1/2019 |
| 1008 | 40685 | 8/10/2019 | Nancy | Shermann | Single | 1 | 108 | 9/1/2019 |
| 1009 | 20448 | 9/12/2019 | Michael | Tiernan | 0 | 209 | 11/1/2019 | |
| 1010 | 38408 | 10/5/2019 | Phillippe | Anderson | Single | 0 | 202 | 11/1/2019 |
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.RentContractID = new SelectList(objects.GetRentContracts(), "RentContractID", "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, " +
" RentContractID, PaymentDate, " +
" Amount, Notes) " +
"VALUES(" + collection["ReceiptNumber"] + ", " +
collection["EmployeeID"] + ", " + collection["RentContractID"] +
", 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.RentContractID = new SelectList(objects.GetRentContracts(), "RentContractID", "Description", pmt.RentContractID);
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"] + ", " +
" RentContractID = " + collection["RentContractID"] + ", " +
" 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 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.RentContractID)</dt>
<dd>@Html.DisplayFor(model => model.RentContractID)</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("RentContractID", 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("RentContractID", 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.RentContractID)</dt>
<dd>@Html.DisplayFor(model => model.RentContractID)</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>@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.RentContractID)</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.RentContractID)</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>
| Receipt # | Employee # | Contract # | Payment Date | Amount | Notes |
| 100001 | 20448 | 1002 | 06/15/2019 | 1250 | This is the payment of the security deposit |
| 100002 | 38408 | 1001 | 06/17/2019 | 1100 | This is the first payment of the tenant. It is for the security deposit. |
| 100003 | 40685 | 1004 | 06/22/2019 | 1000 | Security deposit payment |
| 100004 | 93947 | 1003 | 06/25/2019 | 850 | This was the security deposit payment |
| 100005 | 40685 | 1003 | 07/26/2019 | 1100 | This was the July 2019 rent payment |
| 100006 | 38408 | 1001 | 07/31/2019 | 1350 | Rent payment for July 2019 |
| 100007 | 40685 | 1004 | 08/25/2019 | 1100 | August 2019 rent payment |
| 100008 | 20448 | 1003 | 08/30/2019 | 1100 | August 2019 rent payment |
| 100009 | 20448 | 1001 | 08/31/2019 | 1350 | Rent payment for August 2019 |
| 100010 | 93947 | 1002 | 09/26/2019 | 1500 | Rent payment - September 2019 |
| 100011 | 38408 | 1003 | 09/27/2019 | 1100 | Rent payment for September 2019 |
| 100012 | 93947 | 1001 | 09/28/2019 | 1350 | This is the rent payment for September 2019 |
| 100013 | 20448 | 1004 | 09/29/2019 | 1100 | September 2019 monthly payment |
| 100014 | 93947 | 1003 | 10/28/2019 | 1100 | October 2019 Rent |
| 100015 | 20448 | 1002 | 10/29/2019 | 1500 | This was the payment for October 2019 |
INSERT INTO Management.Payments(ReceiptNumber, EmployeeID, RentContractID, PaymentDate, Amount, Notes)
VALUES(100001, 6, 2, N'06/15/2019', 1250, N'This is the payment of the security deposit'),
(100002, 5, 1, N'06/17/2019', 1100, N'This is the payment of the security deposit'),
(100003, 2, 4, N'06/22/2019', 1000, N'Security deposit payment'),
(100004, 1, 3, N'06/25/2019', 850, N'This was the security deposit payment'),
(100005, 2, 3, N'07/26/2019', 1100, N'This was the July 2019 rent payment'),
(100006, 5, 1, N'07/31/2019', 1350, N'Rent payment for July 2019'),
(100007, 2, 4, N'08/25/2019', 1100, N'August 2019 rent payment'),
(100008, 6, 3, N'08/30/2019', 1100, N'August 2019 rent payment'),
(100009, 6, 1, N'08/31/2019', 1350, N'Rent payment for August 2019'),
(100010, 1, 2, N'09/26/2019', 1500, N'Rent payment - September 2019'),
(100011, 5, 3, N'09/27/2019', 1100, N'Rent payment for September 2019'),
(100012, 1, 1, N'09/28/2019', 1350, N'This is the rent payment for September 2019'),
(100013, 6, 4, N'09/29/2019', 1100, N'September 2019 monthly payment'),
(100014, 1, 3, N'10/28/2019', 1100, N'October 2019 Rent'),
(100015, 6, 2, N'10/29/2019', 1500, N'This was the payment for October 2019'),
(100016, 1, 1, N'10/30/2019', 1350, N'Rent for October 2019'),
(100017, 5, 4, N'10/30/2019', 1100, N'Rent payment for October 2019'),
(100018, 6, 1, N'11/26/2019', 1350, N'November 2019 Rent'),
(100019, 2, 3, N'11/29/2019', 1100, N'November 2019 Rent Payment'),
(100020, 6, 2, N'11/30/2019', 1500, N'This is the rent payment for November 2019'),
(100021, 2, 4, N'11/30/2019', 1100, N'Rent payment for November 2019'),
(100022, 1, 2, N'12/27/2019', 1500, N'Rent payment - December 2019'),
(100023, 6, 4, N'12/30/2019', 1100, N'December 2019 rent payment'),
(100024, 5, 3, N'12/31/2019', 1100, N'Rent payment for December 2019'),
(100025, 5, 1, N'01/04/2019', 1350, N'This was rent for December 2019'),
(100026, 2, 4, N'01/20/2019', 1100, N'January 2020 rent payment'),
(100027, 5, 3, N'01/26/2019', 1100, N'January 2020 rent payment'),
(100028, 5, 2, N'01/28/2019', 1500, N'January 2020 - Rent Payment'),
(100029, 6, 1, N'02/02/2019', 1350, N'This was rent for January 2020'),
(100030, 5, 4, N'02/25/2019', 1100, N'February 2020 rent payment'),
(100031, 6, 3, N'02/26/2019', 1100, N'Rent payment for February 2020'),
(100032, 6, 2, N'02/27/2019', 1500, N'February 2020 Rent'),
(100033, 5, 1, N'03/01/2019', 1350, N'Rent for February 2020'),
(100034, 2, 3, N'03/27/2019', 1100, N'Rent payment for March 2020'),
(100035, 6, 4, N'03/29/2019', 1100, N'March 2020 rent payment'),
(100036, 5, 2, N'03/30/2019', 1500, N'Rent payment for March 2020'),
(100037, 5, 1, N'03/31/2019', 1350, N'March 2020 Rent');
GO
Creating a Join
Once you have the necessary tables and their columns, you can create a join. To do this visually, in the Object Explorer of Microsoft SQL Server Management Studio, right-click the connection or any of its nodes and click New Query. The basic formula to create a join is:
SELECT column(s) FROM child-table type-of-join parent-table ON condition
The child-table specifies the table that holds the records that will be retrieved. It can be represented as follows:
SELECT column(s)
FROM Students
type-of-join parent-table
ON condition
The parent-table specifies the table that holds the column with the primary key that will control what records, related to the child table, will be selected. This would be represented as follows:
SELECT column(s)
FROM Students
type-of-join Majors
ON condition
The condition is a logical expression used to validate the records that will be isolated. To create the condition, you should assign the primary key column of the parent table to the foreign key column of the child table. Because both columns likely have the same name, to distinguish them, their names should be qualified. This would be done as follows:
SELECT column(s)
FROM Students
type-of-join Majors
ON Students.MajorCode = Majors.MajorCode
The column(s) of our formula allow(s) you to create a list of the columns you want to include in your statement. As you should be aware, you can include all columns by using the * operator. Here is an example:
SELECT * FROM Students type-of-join MajorCode ON Students.MajorCode = Majors.MajorCode
In this case, all columns from all tables would be included in the result. Instead of all columns, you may want a restricted list. In this case, create the list after the SELECT keyword separating them with commas. You can use the name of a column normally if that name is not duplicated in more than one column. Here is an example:
SELECT LastName, FirstName, Gender
FROM Students
type-of-Join EmploymentsStatus
ON Students.StatusCode = Majors.MajorCode
If the same name of a column is found in more than one table, as is the case for a primary-foreign key combination, you should qualify the name of the column by preceding it with the name of its parent table followed by a period. Here are examples:
SELECT LastName,
FirstName,
Students.MajorCode,
Majors.MajorCode
FROM Students
type-of-join MajorCode
ON Students.MajorCode = Majors.MajorCode
In fact, to make your code easier to read, you should qualify the name of each column of your SELECT statement. Here are examples:
SELECT Students.LastName,
Students.FirstName,
Students.MajorCode,
Majors.MajorCode
FROM Students
type-of-join MajorCode
ON Students.MajorCode = Majors.MajorCode
Cross and Inner Joins
Introduction
When studying data relationships, we saw the roles of the primary and foreign keys in maintaining the exchange of information between two tables. This technique of linking tables plays a major part when creating a join. It allows you to decide whether you want to include all records or only isolate some of them. To respect the direction of a relationship between two tables as it is applied to a query, Transact-SQL supports three types of joins.
Cross Joins
A cross join creates a list of all records from both tables as follows: the first record from the parent table is associated to each record from the child table, then the second record from the parent table is associated to each record from the child table, and so on. In this case also, there is no need of a common column between both tables. In other words, you will not use the ON clause.
To create a cross join, you can replace the type-of-join of our formula with CROSS JOIN or CROSS OUTER JOIN. Here is an example:
SELECT ContractNumber AS [Contract #],
HumanResources.Employees.EmployeeNumber + N': ' +
HumanResources.Employees.FirstName + N' ' +
HumanResources.Employees.LastName AS [Processed By],
ContractDate [Contract Date],
Management.RentContracts.FirstName + N' ' + Management.RentContracts.LastName AS Tenant,
MaritalStatus AS [Marital Status],
NumberOfChildren As Children,
ApartmentID AS [Unit #],
RentStartDate AS [Contract Start Date]
FROM HumanResources.Employees
CROSS JOIN Management.RentContracts;;
GO
This would produce:
Inner Joins
Imagine you have two tables that can be linked through one's primary key and another's foreign key. Some records in the child table may not have an entry for the foreign key column and would be marked with NULL by the database engine. When creating a query of records of the child table, if you want your list to include only records that have an entry, you can create it as an inner join.
To create an inner join, specify the type-of-join of our formula with the expression INNER JOIN. Here is an example:
SELECT Students.StudentNumber,
Students.FirstName,
Students.LastName,
Students.MajorCode,
Majors.MajorCode AS [Major]
FROM Students INNER JOIN Majors ON Students.MajorCode = Majors.MajorCode;
GO
This would produce:
Notice that, because the query doesn't include students who don't have a major, those are students whose MajorCode is NULL, it (the query) produces 15 records.
An alternative to the INNER JOIN expression is to simply type JOIN. Here is an example:
SELECT Students.StudentNumber,
Students.FirstName,
Students.LastName,
Students.MajorCode,
Majors.MajorCode AS [Major]
FROM Students JOIN Majors ON Students.MajorCode = Majors.MajorCode;
GO
We mentioned earlier that you could include all columns in the query. In our result, since we are more interested in the major of each Students record, we would not need the MajorCode column from the Majors table. Instead, we can access the MajorName. Here is an example:
SELECT Students.StudentNumber,
Students.FirstName,
Students.LastName,
Majors.MajorName AS [Major]
FROM Students JOIN Majors ON Students.MajorCode = Majors.MajorCode;
GO
This would produce:
Obviously, once again, we get 15 records.
Outer Joins
Introduction
Instead of showing only records that have entries in the child table, you may want your query to get all records, including those that are null. To get this result, you would create an outer join. You have three options.
A Left Outer Join
A left outer join produces all records of the child table, also called the right table. The records of the child table that do not have an entry in the foreign key column are marked as NULL.
To create a left outer join, replace the type-of-join our formula with the LEFT OUTER JOIN expression. Here is an example:
SELECT Students.StudentNumber,
Students.FirstName,
Students.LastName,
Majors.MajorCode
FROM Students
LEFT OUTER JOIN Majors
ON Students.MajorCode = Majors.MajorCode;
GO
This would produce:
You can omit the OUTER keyword and get the same result. Here is an example:
SELECT StudentNumber,
FirstName,
LastName,
MajorName
FROM Students
LEFT JOIN Majors
ON Students.MajorCode = Majors.MajorCode;
GO
This would produce:
Notice that the result includes all records of the Students (also called the right) table and the records that don't have an entry in the MajorCode column of the Students (the right) table are marked with NULL.
Right Outer Joins
A right outer join considers all records from the parent table and finds a matching record in the child table. To do this, it starts with the first record of the parent table (in our example the Majors table) and shows each record of the child table (in this case the Students table) that has a corresponding entry. This means that, in our example, a right outer join would first create a list of the Students records that have a CMSC value for the MajorCode column. After the first record, the right outer join moves to the second record, and so on, each time listing the records of the child table that have a corresponding entry for the primary key of the parent table.
To create a right outer join in SQL, replace the type-of-join factor of our formula with the RIGHT OUTER JOIN expression. Here is an example:
SELECT Students.StudentNumber,
Students.FirstName,
Students.LastName,
Majors.MajorCode
FROM Students
RIGHT OUTER JOIN Majors
ON Students.MajorCode = Majors.MajorCode;
GO
This would produce:
You can omit the OUTER keyword and get the same result. Here is an example:
SELECT StudentNumber,
FirstName,
LastName,
MajorName
FROM Students
RIGHT JOIN Majors
ON Students.MajorCode = Majors.MajorCode;
GO
This would produce:
A Full Outer Join
A full outer join produces all records from both the parent and the child tables. If a record from one table does not have a value in the other value, the value of that record is marked as NULL.
To create a full outer join in SQL, replace the type-of-join of our formula with the FULL OUTER JOIN expression. Here is an example:
SELECT Students.StudentNumber,
Students.FirstName,
Students.LastName,
Majors.MajorCode
FROM Students
FULL OUTER JOIN Majors
ON Students.MajorCode = Majors.MajorCode;
GO
This would produce:
You can omit the OUTER keyword and get the same result. Here is an example:
SELECT StudentNumber,
FirstName,
LastName,
MajorName
FROM Students
FULL JOIN Majors
ON Students.MajorCode = Majors.MajorCode;
GO
This would produce:
Just as we have involved only two tables in our joins so far, you can create a join that includes many tables.
Practical Learning:
Ending the Lesson
|
|
||
| Previous | Copyright © 2007-2021, FunctionX | Next |
|
|
||