ASP.NET MVC - ADO.NET - Watts' A Loan
ASP.NET MVC - ADO.NET - Watts' A Loan
Introduction
Watts' A Loan is a fictitious private company that lends money to individuals and small businesses. To support this business, we will create a database in Microsoft SQL Server.
The databas we want for this application should be installed in a server. It can then be accessed using a client application such as a desktop application or a web-based application. In some cases, especially, if you are primarily creating the application to test it, you can create it as a local application, a Windows Forms application or else.
Practical Learning: Introducing Views
using System.Web.Optimization;
namespace WattsALoan1
{
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/WattsALoan.css"));
}
}
}The Watts' A Loan Database
To keep track of the loans issued by the company, we will create a Microsoft SQL Server database.
Practical Learning: Creating the Database
USE master; GO CREATE DATABASE WattsALoan; GO USE WattsALoan1; GO
CREATE SCHEMA HumanResources;
GO
CREATE SCHEMA Management;
GO
CREATE TABLE HumanResources.Employees
(
EmployeeID INT IDENTITY(1, 1),
EmployeeNumber NVARCHAR(10) UNIQUE,
FirstName NVARCHAR(20),
LastName NVARCHAR(20),
EmploymentTitle NVARCHAR(50),
CONSTRAINT PK_Employees PRIMARY KEY(EmployeeID)
);
GO
CREATE TABLE Management.LoanContracts
(
LoanContractID INT IDENTITY(1, 1),
LoanNumber INT UNIQUE NOT NULL,
DateAllocated DATETIME2,
EmployeeID INT,
CustomerFirstName NVARCHAR(20),
CustomerLastName NVARCHAR(20),
LoanType NVARCHAR(20),
LoanAmount DECIMAL(8, 2),
InterestRate DECIMAL(8, 2),
[Periods] INT,
InterestAmount DECIMAL(8, 2),
FutureValue DECIMAL(8, 2),
MonthlyPayment DECIMAL(8, 2),
CurrentBalance DECIMAL(8, 2),
PaymentStartDate DATETIME2,
CONSTRAINT FK_LoansProcessors FOREIGN KEY(EmployeeID) REFERENCES HumanResources.Employees(EmployeeID),
CONSTRAINT PK_LoansContracts PRIMARY KEY(LoanContractID)
);
GO
CREATE TABLE Management.Payments
(
PaymentID INT IDENTITY(1, 1),
PaymentNumber INT,
PaymentDate DATETIME2,
EmployeeID INT,
LoanContractID INT,
AmountPaid DECIMAL(8, 2),
CONSTRAINT FK_PaymentsReceivers FOREIGN KEY(EmployeeID) REFERENCES HumanResources.Employees(EmployeeID),
CONSTRAINT FK_LoansPayments FOREIGN KEY(LoanContractID) REFERENCES Management.LoanContracts(LoanContractID),
CONSTRAINT PK_Payments PRIMARY KEY(PaymentID)
);
GO
CREATE VIEW Management.Clients
AS
SELECT LoanNumber,
LoanType,
CustomerFirstName,
CustomerLastName,
LoanAmount,
MonthlyPayment,
PaymentStartDate
FROM LoanContracts;
GO
-------------------------------------------------------------
TOGO TOGO
CREATE VIEW Management.LoansContracts
WITH SCHEMABINDING
AS
SELECT LoanNumber,
DateAllocated,
EmployeeNumber + N': ' + FirstName + N' ' + LastName AS [Processed By],
CustomerFirstName + N' ' + CustomerLastName AS Customer,
LoanType,
LoanAmount,
InterestRate,
[Periods],
MonthlyPayment,
FutureValue,
InterestAmount,
PaymentStartDate
FROM Management.LoanContracts INNER JOIN HumanResources.Employees
ON Management.LoanContracts.EmployeeID LIKE HumanResources.Employees.EmployeeID;
GO
CREATE VIEW Management.LoanPayment
WITH SCHEMABINDING
AS
SELECT ReceiptNumber,
EmployeeID,
LoanContractID,
PaymentDate,
PaymentAmount,
Balance
FROM Management.Payments;
GO
CREATE VIEW Management.PaymentsSummary
AS
SELECT LoanNumber,
HumanResources.Employees.EmployeeNumber + N': ' + HumanResources.Employees.FirstName + N' ' + HumanResources.Employees.LastName AS [Received By],
PaymentDate,
Customer = CustomerFirstName + N' ' + CustomerLastName,
LoanType,
LoanAmount,
PaymentAmount,
Balance
FROM Management.Payments
INNER JOIN HumanResources.Employees ON Payments.EmployeeID LIKE HumanResources.Employees.EmployeeID
INNER JOIN Management.LoanContracts ON Management.LoanContracts.LoanContractID LIKE Management.Payments.LoanContractID;
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.7.2"/>
<httpRuntime targetFramework="4.7.2"/>
<httpModules>
<add name="ApplicationInsightsWebTracking" type="Microsoft.ApplicationInsights.Web.ApplicationInsightsHttpModule, Microsoft.AI.Web"/>
</httpModules>
</system.web>
<connectionStrings>
<add name="csWattsALoan"
connectionString="Data Source=(local); Database='WattsALoan1'; 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="csWattsALoan"
connectionString="Data Source=(LocalDb)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\WattsALoan1.mdf;Initial Catalog=WattsALoan1;Integrated Security=True"
providerName="System.Data.SqlClient" />
</connectionStrings>
. . . No Change
</configuration>The Employees of the Company
The employees of the company will be in charge of dailay operations.
Practical Learning: Creating Employees
using System.ComponentModel.DataAnnotations;
namespace WattsALoan.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.Net;
using System.Data;
using System.Web.Mvc;
using WattsALoan1.Models;
using System.Data.SqlClient;
using System.Collections.Generic;
namespace WattsALoan1.Controllers
{
public class EmployeesController : Controller
{
public List<Employee> GetEmployees()
{
List<Employee> employees = new List<Employee>();
using (SqlConnection scWattsALoan = new SqlConnection(System.Configuration.
ConfigurationManager.
ConnectionStrings["csWattsALoan"].
ConnectionString))
{
SqlCommand cmdEmployees = new SqlCommand("SELECT EmployeeID, EmployeeNumber, " +
" FirstName, LastName, EmploymentTitle " +
"FROM Employees;",
scWattsALoan);
scWattsALoan.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;
}
// GET: Employees
public ActionResult Index()
{
return View(GetEmployees());
}
// GET: Employees/Details/5
public ActionResult Details(int id)
{
Employee employee = null;
if (id == 0)
{
return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
}
foreach (var staff in GetEmployees())
{
if (staff.EmployeeID == id)
{
employee = staff;
break;
}
}
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["csWattsALoan"].
ConnectionString))
{
SqlCommand cmdEmployees = new SqlCommand("INSERT INTO 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 = null;
foreach (var staff in GetEmployees())
{
if (staff.EmployeeID == id)
{
employee = staff;
break;
}
}
return employee == null ? HttpNotFound() : (ActionResult)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["csWattsALoan"].
ConnectionString))
{
SqlCommand cmdEmployees = new SqlCommand("UPDATE 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 = null;
foreach (var staff in GetEmployees())
{
if (staff.EmployeeID == id)
{
employee = staff;
break;
}
}
return employee == null ? HttpNotFound() : (ActionResult)View(employee);
}
// POST: Employees/Delete/5
[HttpPost]
public ActionResult Delete(int id, FormCollection collection)
{
try
{
// TODO: Add delete logic here
using (SqlConnection scRentManagement = new SqlConnection(System.Configuration.
ConfigurationManager.
ConnectionStrings["csWattsALoan"].
ConnectionString))
{
SqlCommand cmdEmployees = new SqlCommand("DELETE FROM Employees " +
"WHERE EmployeeID = " + id + ";",
scRentManagement);
scRentManagement.Open();
cmdEmployees.ExecuteNonQuery();
}
return RedirectToAction("Index");
}
catch
{
return View();
}
}
}
}@model IEnumerable<WattsALoan1.Models.Employee>
@{
ViewBag.Title = "Employees";
}
<h2 class="bold maroon common-font text-center">Employees</h2>
<hr />
<table class="table table-hover common-font">
<tr>
<th class="text-center">@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 WattsALoan1.Models.Employee
@{
ViewBag.Title = "Employee Details";
}
<h2 class="bold text-center common-font maroon">Employee Details</h2>
<hr />
<div class="containment">
<dl class="dl-horizontal common-font">
<dt>@Html.DisplayNameFor(model => model.EmployeeID)</dt>
<dd>@Html.DisplayFor(model => model.EmployeeID)</dd>
<dt>@Html.DisplayNameFor(model => model.EmployeeNumber)</dt>
<dd>@Html.DisplayFor(model => model.EmployeeNumber)</dd>
<dt>@Html.DisplayNameFor(model => model.FirstName)</dt>
<dd>@Html.DisplayFor(model => model.FirstName)</dd>
<dt>@Html.DisplayNameFor(model => model.LastName)</dt>
<dd>@Html.DisplayFor(model => model.LastName)</dd>
<dt>@Html.DisplayNameFor(model => model.EmploymentTitle)</dt>
<dd>@Html.DisplayFor(model => model.EmploymentTitle)</dd>
</dl>
</div>
<p class="text-center">
@Html.ActionLink("Edit/Update Employee Record", "Edit", new { id = Model.EmployeeID }) ::
@Html.ActionLink("Employees", "Index")
</p>@model WattsALoan1.Models.Employee
@{
ViewBag.Title = "Create Employee";
}
<h2 class="bold maroon common-font text-center">Employment Application</h2>
<hr />
@using (Html.BeginForm())
{
<div class="form-horizontal common-font">
<div class="form-group">
@Html.LabelFor(model => model.EmployeeNumber, htmlAttributes: new { @class = "control-label col-md-5 blue" })
<div class="col-md-7">
@Html.EditorFor(model => model.EmployeeNumber, new { htmlAttributes = new { @class = "form-control" } })
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.FirstName, htmlAttributes: new { @class = "control-label col-md-5 blue" })
<div class="col-md-7">
@Html.EditorFor(model => model.FirstName, new { htmlAttributes = new { @class = "form-control" } })
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.LastName, htmlAttributes: new { @class = "control-label col-md-5 blue" })
<div class="col-md-7">
@Html.EditorFor(model => model.LastName, new { htmlAttributes = new { @class = "form-control" } })
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.EmployeeNumber, htmlAttributes: new { @class = "control-label col-md-5 blue" })
<div class="col-md-7">
@Html.EditorFor(model => model.EmploymentTitle, new { htmlAttributes = new { @class = "form-control" } })
</div>
</div>
<div class="form-group">
<label class="control-label col-md-6">@Html.ActionLink("Employees", "Index")</label>
<div class="col-md-6">
<input type="submit" value="Hire this Employee" class="btn btn-primary" />
</div>
</div>
</div>
}@model WattsALoan1.Models.Employee
@{
ViewBag.Title = "Edit/Update Employee Information";
}
<h2 class=" bold maroon common-font text-center">Edit/Update Employee Information</h2>
<hr />
@using (Html.BeginForm())
{
<div class="form-horizontal common-font">
<div class="form-group">
@Html.LabelFor(model => model.EmployeeNumber, htmlAttributes: new { @class = "control-label col-md-5" })
<div class="col-md-7">
@Html.EditorFor(model => model.EmployeeNumber, new { htmlAttributes = new { @class = "form-control" } })
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.FirstName, htmlAttributes: new { @class = "control-label col-md-5" })
<div class="col-md-7">
@Html.EditorFor(model => model.FirstName, new { htmlAttributes = new { @class = "form-control" } })
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.LastName, htmlAttributes: new { @class = "control-label col-md-5" })
<div class="col-md-7">
@Html.EditorFor(model => model.LastName, new { htmlAttributes = new { @class = "form-control" } })
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.EmploymentTitle, htmlAttributes: new { @class = "control-label col-md-5" })
<div class="col-md-7">
@Html.EditorFor(model => model.EmploymentTitle, new { htmlAttributes = new { @class = "form-control" } })
</div>
</div>
<div class="form-group">
<label class="control-label col-md-5">@Html.ActionLink("Employees", "Index")</label>
<div class="col-md-7">
<input type="submit" value="Update Employee Record" class="btn btn-primary" />
</div>
</div>
</div>
}@model WattsALoan1.Models.Employee
@{
ViewBag.Title = "Deleting Employee Record";
}
<h2 class="bold common-font text-center maroon">Deleting Employee Record</h2>
<hr />
<h3 class="common-font maroon text-center">Are you sure you want to delete this employee's record?</h3>
<div class="containment">
<dl class="dl-horizontal common-font">
<dt>@Html.DisplayNameFor(model => model.EmployeeID)</dt>
<dd>@Html.DisplayFor(model => model.EmployeeID)</dd>
<dt>@Html.DisplayNameFor(model => model.EmployeeNumber)</dt>
<dd>@Html.DisplayFor(model => model.EmployeeNumber)</dd>
<dt>@Html.DisplayNameFor(model => model.FirstName)</dt>
<dd>@Html.DisplayFor(model => model.FirstName)</dd>
<dt>@Html.DisplayNameFor(model => model.LastName)</dt>
<dd>@Html.DisplayFor(model => model.LastName)</dd>
<dt>@Html.DisplayNameFor(model => model.EmploymentTitle)</dt>
<dd>@Html.DisplayFor(model => model.EmploymentTitle)</dd>
</dl>
@using (Html.BeginForm())
{
@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>Loans Contracts
The company needs some type of contract with a customer who receives a loan.
Practical Learning: Creating Loans Contracts
using System;
using System.ComponentModel.DataAnnotations;
namespace WattsALoan1.Models
{
public class LoanContract
{
[Display(Name = "Loan Contract ID")]
public int LoanContractID { get; set; }
[Display(Name = "Loan #")]
public int LoanNumber { get; set; }
[DataType(DataType.Date)]
[Display(Name = "Date Allocated")]
public DateTime DateAllocated { get; set; }
[Display(Name = "Employee ID")]
public int EmployeeID { get; set; }
[Display(Name = "First Name")]
public string CustomerFirstName { get; set; }
[Display(Name = "Last Name")]
public string CustomerLastName { get; set; }
[Display(Name = "Loan Type")]
public string LoanType { get; set; } // => "Personal Loan";
[Display(Name = "Loan Amount")]
public decimal LoanAmount { get; set; }
[Display(Name = "Interest Rate")]
public decimal InterestRate { get; set; }
public int Periods { get; set; }
[Display(Name = "Monthly Payment")]
public decimal MonthlyPayment { get; set; }
[Display(Name = "Future Value")]
public decimal FutureValue { get; set; }
[Display(Name = "Interest Amount")]
public decimal InterestAmount { get; set; }
[DataType(DataType.Date)]
[Display(Name = "Payment Start Date")]
public DateTime PaymentStartDate { get; set; }
}
}using System;
using System.Data;
using System.Web.Mvc;
using WattsALoan1.Models;
using System.Data.SqlClient;
using System.Collections.Generic;
namespace WattsALoan1.Controllers
{
public class LoansContractsController : Controller
{
public List<LoanContract> GetLoanContracts()
{
List<LoanContract> contracts = new List<LoanContract>();
using (SqlConnection scWattsALoan = new SqlConnection(System.Configuration.
ConfigurationManager.
ConnectionStrings["csWattsALoan"].
ConnectionString))
{
SqlCommand cmdLoanContracts = new SqlCommand("SELECT LoanContractID, LoanNumber, DateAllocated, EmployeeID, " +
" CustomerFirstName, CustomerLastName, LoanType, " +
" LoanAmount, InterestRate, Periods, MonthlyPayment, " +
" FutureValue, InterestAmount, PaymentStartDate " +
"FROM LoanContracts;",
scWattsALoan);
scWattsALoan.Open();
cmdLoanContracts.ExecuteNonQuery();
SqlDataAdapter sdaLoanContracts = new SqlDataAdapter(cmdLoanContracts);
DataSet dsLoanContracts = new DataSet("loans-contracts");
sdaLoanContracts.Fill(dsLoanContracts);
LoanContract contract = null;
for (int i = 0; i < dsLoanContracts.Tables[0].Rows.Count; i++)
{
DataRow drLoanContract = dsLoanContracts.Tables[0].Rows[i];
contract = new LoanContract()
{
LoanContractID = int.Parse(drLoanContract[0].ToString()),
LoanNumber = int.Parse(drLoanContract[1].ToString()),
DateAllocated = DateTime.Parse(drLoanContract[2].ToString()),
EmployeeID = int.Parse(drLoanContract[3].ToString()),
CustomerFirstName = drLoanContract[4].ToString(),
CustomerLastName = drLoanContract[5].ToString(),
LoanType = drLoanContract[6].ToString(),
LoanAmount = decimal.Parse(drLoanContract[7].ToString()),
InterestRate = decimal.Parse(drLoanContract[8].ToString()),
Periods = int.Parse(drLoanContract[9].ToString()),
MonthlyPayment = decimal.Parse(drLoanContract[10].ToString()),
FutureValue = decimal.Parse(drLoanContract[11].ToString()),
InterestAmount = decimal.Parse(drLoanContract[12].ToString()),
PaymentStartDate = DateTime.Parse(drLoanContract[13].ToString())
};
contracts.Add(contract);
}
}
return contracts;
}
// GET: LoansContracts
public ActionResult Index()
{
return View(GetLoanContracts());
}
// GET: LoansContracts/Details/5
public ActionResult Details(int id)
{
return View();
}
// GET: LoansContracts/ReviewCustomersAccounts
public ActionResult ReviewCustomersAccounts()
{
return View();
}
// GET: LoansContracts/LoanContractStartUp
public ActionResult LoanContractStartUp()
{
return View();
}
// GET: LoansContracts/LoanContractPreparation
public ActionResult LoanContractPreparation(string EmployeeID, string LoanAmount, string InterestRate, string Periods,
FormCollection collection)
{
//int loanNbr = 100000;
Random rndNumber = new Random();
ViewBag.LoanNumber = rndNumber.Next(100001, 999999).ToString();
// Create a list of loans types for a combo box
List<SelectListItem> loanTypes = new List<SelectListItem>();
loanTypes.Add(new SelectListItem() { Text = "Personal Loan", Value = "Personal Loan" });
loanTypes.Add(new SelectListItem() { Text = "Car Financing", Value = "Car Financing" });
loanTypes.Add(new SelectListItem() { Text = "Boat Financing", Value = "Boat Financing" });
loanTypes.Add(new SelectListItem() { Text = "Furniture Purchase", Value = "Furniture Purchase" });
loanTypes.Add(new SelectListItem() { Text = "Musical Instrument", Value = "Musical Instrument" });
// Store the list in a View Bag so it can be access by a combo box
ViewBag.LoanType = loanTypes;
if(!string.IsNullOrEmpty(EmployeeID))
{
EmployeesController ec = new EmployeesController();
using (SqlConnection scWattsALoan = new SqlConnection(System.Configuration.
ConfigurationManager.
ConnectionStrings["csWattsALoan"].
ConnectionString))
{
foreach (var staff in ec.GetEmployees())
{
if (staff.EmployeeID == int.Parse(EmployeeID))
{
ViewBag.EmployeeDetails = staff.EmployeeNumber + " - " +
staff.FirstName + " " + staff.LastName +
" (" + staff.EmploymentTitle + ")";
break;
}
}
}
}
int periods = 0;
decimal principal = 0, interestRate = 0;
if (!string.IsNullOrEmpty(LoanAmount))
{
principal = decimal.Parse(LoanAmount);
}
if (!string.IsNullOrEmpty(InterestRate))
{
interestRate = decimal.Parse(InterestRate) / 100;
}
if (!string.IsNullOrEmpty(Periods))
{
periods = int.Parse(Periods);
}
decimal interestAmount = principal * interestRate * periods / 12;
decimal futureValue = principal + interestAmount;
decimal monthlyPayment = futureValue / periods;
ViewBag.FutureValue = futureValue.ToString("F");
ViewBag.InterestAmount = interestAmount.ToString("F");
ViewBag.MonthlyPayment = monthlyPayment.ToString("F");
return View();
}
// GET: LoansContracts/Create
public ActionResult Create()
{
return View();
}
// POST: LoansContracts/Create
[HttpPost]
public ActionResult Create(string LoanNumber, string EmployeeID,
string InterestRate, string Periods, string FutureValue,
string MonthlyPayment, string InterestAmount,
FormCollection collection)
{
try
{
// TODO: Add insert logic here
if(!string.IsNullOrEmpty(EmployeeID))
{
using (SqlConnection scWattsALoan = new SqlConnection(System.Configuration.
ConfigurationManager.
ConnectionStrings["csWattsALoan"].
ConnectionString))
{
SqlCommand cmdLoanAllocation =
new SqlCommand("INSERT LoanContracts(LoanNumber, DateAllocated, EmployeeID, " +
" CustomerFirstName, CustomerLastName, LoanType, " +
" LoanAmount, InterestRate, Periods, MonthlyPayment, " +
" FutureValue, InterestAmount, PaymentStartDate) " +
"VALUES(" + int.Parse(LoanNumber) + ", N'" + collection["DateAllocated"] +
"', " + int.Parse(collection["EmployeeID"]) + ", N'" +
collection["CustomerFirstName"] + "', N'" + collection["CustomerLastName"] +
"', N'" + collection["LoanType"] + "', " + decimal.Parse(collection["LoanAmount"]) +
", " + decimal.Parse(collection["InterestRate"]) + ", " +
int.Parse(collection["Periods"]) + ", " + decimal.Parse(MonthlyPayment) +
", " + decimal.Parse(FutureValue) + ", " + decimal.Parse(InterestAmount) +
", N'" + collection["PaymentStartDate"] + "');");
cmdLoanAllocation.Connection = scWattsALoan;
scWattsALoan.Open();
cmdLoanAllocation.ExecuteNonQuery();
}
}
return RedirectToAction("Index");
}
catch
{
return View();
}
}
. . . No Change
}
}@model IEnumerable<WattsALoan1.Models.LoanContract>
@{
ViewBag.Title = "Loans Contracts";
}
<h2 class="bold maroon common-font text-center">Loan Contracts</h2>
<table class="table table-hover common-font">
<tr>
<th class="text-center">@Html.DisplayNameFor(model => model.LoanContractID)</th>
<th class="text-center">@Html.DisplayNameFor(model => model.LoanNumber)</th>
<th>@Html.DisplayNameFor(model => model.DateAllocated)</th>
<th class="text-center">@Html.DisplayNameFor(model => model.EmployeeID)</th>
<th>@Html.DisplayNameFor(model => model.CustomerFirstName)</th>
<th>@Html.DisplayNameFor(model => model.CustomerLastName)</th>
<th>@Html.DisplayNameFor(model => model.LoanType)</th>
<th>@Html.DisplayNameFor(model => model.LoanAmount)</th>
<th>@Html.DisplayNameFor(model => model.InterestRate)</th>
<th>@Html.DisplayNameFor(model => model.Periods)</th>
<th>@Html.DisplayNameFor(model => model.MonthlyPayment)</th>
<th>@Html.DisplayNameFor(model => model.FutureValue)</th>
<th>@Html.DisplayNameFor(model => model.InterestAmount)</th>
<th>@Html.DisplayNameFor(model => model.PaymentStartDate)</th>
<th>@Html.ActionLink("New Loan Contract", "LoanContractStartUp")</th>
</tr>
@foreach (var item in Model)
{
<tr>
<td class="text-center">@Html.DisplayFor(modelItem => item.LoanContractID)</td>
<td class="text-center">@Html.DisplayFor(modelItem => item.LoanNumber)</td>
<td class="text-center">@Html.DisplayFor(modelItem => item.DateAllocated)</td>
<td class="text-center">@Html.DisplayFor(modelItem => item.EmployeeID)</td>
<td>@Html.DisplayFor(modelItem => item.CustomerFirstName)</td>
<td>@Html.DisplayFor(modelItem => item.CustomerLastName)</td>
<td>@Html.DisplayFor(modelItem => item.LoanType)</td>
<td class="text-center">@Html.DisplayFor(modelItem => item.LoanAmount)</td>
<td class="text-center">@Html.DisplayFor(modelItem => item.InterestRate)</td>
<td class="text-center">@Html.DisplayFor(modelItem => item.Periods)</td>
<td class="text-center">@Html.DisplayFor(modelItem => item.MonthlyPayment)</td>
<td class="text-center">@Html.DisplayFor(modelItem => item.FutureValue)</td>
<td class="text-center">@Html.DisplayFor(modelItem => item.InterestAmount)</td>
<td class="text-center">@Html.DisplayFor(modelItem => item.PaymentStartDate)</td>
<td>
@Html.ActionLink("Edit", "Edit", new { id = item.LoanContractID }) |
@Html.ActionLink("Details", "Details", new { id = item.LoanContractID }) |
@Html.ActionLink("Delete", "Delete", new { id = item.LoanContractID })
</td>
</tr>
}
</table>@model WattsALoan1.Models.LoansContract
@{
ViewBag.Title = "Loan Contract Details";
}
<h2 class="bold text-center common-font maroon">Loan Contract Details</h2>
<hr />
<div class="containment">
<dl class="dl-horizontal common-font">
<dt>@Html.DisplayNameFor(model => model.LoanContractID)</dt>
<dd>@Html.DisplayFor(model => model.LoanContractID)</dd>
<dt>@Html.DisplayNameFor(model => model.LoanNumber)</dt>
<dd>@Html.DisplayFor(model => model.LoanNumber)</dd>
<dt>@Html.DisplayNameFor(model => model.DateAllocated)</dt>
<dd>@ViewData["DateAllocated"]</dd>
<dt>@Html.DisplayNameFor(model => model.ProcessedBy)</dt>
<dd>@Html.DisplayFor(model => model.ProcessedBy)</dd>
<dt>@Html.DisplayNameFor(model => model.Customer)</dt>
<dd>@Html.DisplayFor(model => model.Customer)</dd>
<dt>@Html.DisplayNameFor(model => model.LoanType)</dt>
<dd>@Html.DisplayFor(model => model.LoanType)</dd>
<dt>@Html.DisplayNameFor(model => model.LoanAmount)</dt>
<dd>@Html.DisplayFor(model => model.LoanAmount)</dd>
<dt>@Html.DisplayNameFor(model => model.InterestRate)</dt>
<dd>@Html.DisplayFor(model => model.InterestRate)</dd>
<dt>@Html.DisplayNameFor(model => model.Periods)</dt>
<dd>@Html.DisplayFor(model => model.Periods)</dd>
<dt>@Html.DisplayNameFor(model => model.MonthlyPayment)</dt>
<dd>@Html.DisplayFor(model => model.MonthlyPayment)</dd>
<dt>@Html.DisplayNameFor(model => model.FutureValue)</dt>
<dd>@Html.DisplayFor(model => model.FutureValue)</dd>
<dt>@Html.DisplayNameFor(model => model.InterestAmount)</dt>
<dd>@Html.DisplayFor(model => model.InterestAmount)</dd>
<dt>@Html.DisplayNameFor(model => model.PaymentStartDate)</dt>
<dd>@ViewData["PaymentStartDate"]</dd>
</dl>
</div>
<p class="text-center">
@Html.ActionLink("Edit/Update Employee Record", "Edit", new { id = Model.LoanContractID }) ::
@Html.ActionLink("Employees", "Index")
</p>@model WattsALoan1.Models.LoanContract
@{
ViewBag.Title = "Loan Contract Preparation";
}
<h2 class="text-center common-font maroon bold">Loan Contract Preparation</h2>
@using (Html.BeginForm("Create", "LoansContracts", FormMethod.Post))
{
<div class="containment1">
<div class="form-horizontal common-font">
<div class="heading"><p class="text-center">Contract Setup</p></div>
<div class="form-group">
@Html.LabelFor(model => model.EmployeeID, htmlAttributes: new { @class = "control-label col-md-3 blue" })
<div class="col-md-2">
@Html.EditorFor(model => model.EmployeeID, new { htmlAttributes = new { @class = "form-control" } })
</div>
<div class="col-md-6">@ViewBag.EmployeeDetails</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.DateAllocated, htmlAttributes: new { @class = "control-label col-md-5 blue" })
<div class="col-md-7">
@Html.EditorFor(model => model.DateAllocated, new { htmlAttributes = new { @class = "form-control" } })
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.LoanNumber, htmlAttributes: new { @class = "control-label col-md-5 blue" })
<div class="col-md-7">
@Html.TextBox("LoanNumber", ViewBag.LoanNumber as string, htmlAttributes: new { @class = "form-control" })
</div>
</div>
<div class="heading text-center"><p class="text-center">Customer Information</p></div>
<div class="form-group">
@Html.LabelFor(model => model.CustomerFirstName, htmlAttributes: new { @class = "control-label col-md-5 blue" })
<div class="col-md-7">
@Html.EditorFor(model => model.CustomerFirstName, new { htmlAttributes = new { @class = "form-control" } })
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.CustomerLastName, htmlAttributes: new { @class = "control-label col-md-5 blue" })
<div class="col-md-7">
@Html.EditorFor(model => model.CustomerLastName, new { htmlAttributes = new { @class = "form-control" } })
</div>
</div>
<div class="heading text-center"><p class="text-center">Loan Details</p></div>
<div class="form-group">
@Html.LabelFor(model => model.LoanType, htmlAttributes: new { @class = "control-label col-md-5 blue" })
<div class="col-md-7">
@Html.DropDownList("LoanType", ViewBag.LoansTypes as SelectList,
htmlAttributes: new { @class = "form-control col-md-5 blue" })
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.LoanAmount, htmlAttributes: new { @class = "control-label col-md-5 blue" })
<div class="col-md-7">
@Html.EditorFor(model => model.LoanAmount, new { htmlAttributes = new { @class = "form-control" } })
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.InterestRate, htmlAttributes: new { @class = "control-label col-md-5 blue" })
<div class="col-md-7">
@Html.EditorFor(model => model.InterestRate, new { htmlAttributes = new { @class = "form-control" } })
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.Periods, htmlAttributes: new { @class = "control-label col-md-5 blue" })
<div class="col-md-7">
@Html.EditorFor(model => model.Periods, new { htmlAttributes = new { @class = "form-control" } })
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.FutureValue, htmlAttributes: new { @class = "control-label col-md-5 blue" })
<div class="col-md-7">
@Html.TextBox("FutureValue", @ViewBag.FutureValue as string, htmlAttributes: new { @class = "form-control" })
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.InterestAmount, htmlAttributes: new { @class = "control-label col-md-5 blue" })
<div class="col-md-7">
@Html.TextBox("InterestAmount", @ViewBag.InterestAmount as string, htmlAttributes: new { @class = "form-control" })
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.MonthlyPayment, htmlAttributes: new { @class = "control-label col-md-5 blue" })
<div class="col-md-7">
@Html.TextBox("MonthlyPayment", @ViewBag.MonthlyPayment as string, htmlAttributes: new { @class = "form-control" })
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.PaymentStartDate, htmlAttributes: new { @class = "control-label col-md-5 blue" })
<div class="col-md-7">
@Html.EditorFor(model => model.PaymentStartDate, new { htmlAttributes = new { @class = "form-control" } })
</div>
</div>
</div>
</div>
<hr />
<p class="text-center"><input type="submit" value="Save Loan Contract" class="btn btn-primary" /></p>
}@model WattsALoan1.Models.LoanContract
@{
ViewBag.Title = "Loan Contract Start-Up";
}
<h2 class="bold maroon common-font text-center">Loan Contract Start-Up</h2>
@using (Html.BeginForm("LoanContractPreparation", "LoansContracts", FormMethod.Post))
{
<div class="containment">
<div class="form-horizontal common-font">
<div class="heading"><p class="text-center">Contract Setup</p></div>
<div class="form-group">
@Html.LabelFor(model => model.EmployeeID, htmlAttributes: new { @class = "control-label col-md-5 blue" })
<div class="col-md-7">
@Html.EditorFor(model => model.EmployeeID, new { htmlAttributes = new { @class = "form-control" } })
</div>
</div>
<div class="heading"><p class="text-center">Preliminary Loan Information</p></div>
<div class="form-group">
@Html.LabelFor(model => model.LoanAmount, htmlAttributes: new { @class = "control-label col-md-5 blue" })
<div class="col-md-7">
@Html.EditorFor(model => model.LoanAmount, new { htmlAttributes = new { @class = "form-control" } })
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.InterestRate, htmlAttributes: new { @class = "control-label col-md-5 blue" })
<div class="col-md-7">
@Html.EditorFor(model => model.InterestRate, new { htmlAttributes = new { @class = "form-control" } })
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.Periods, htmlAttributes: new { @class = "control-label col-md-5 blue" })
<div class="col-md-7">
@Html.EditorFor(model => model.Periods, new { htmlAttributes = new { @class = "form-control" } })
</div>
</div>
<hr />
<p class="text-center">
<input type="submit" value="Review Loan Contract"
name="btnReviewLoanContract" class="btn btn-primary" />
</p>
</div>
</div>
}@{
ViewBag.Title = "Summary of Customers Accounts";
}
<h2 class="bold maroon common-font text-center">Summary of Customers Accounts</h2>
<div align="center">
@using (System.Data.SqlClient.SqlConnection scWattsALoan = new System.Data.SqlClient.SqlConnection(System.Configuration.
ConfigurationManager.
ConnectionStrings["csWattsALoan"].
ConnectionString))
{
System.Data.SqlClient.SqlCommand cmdCustomers =
new System.Data.SqlClient.SqlCommand("SELECT LoanNumber, LoanType, CustomerFirstName, CustomerLastName, " +
" LoanAmount, MonthlyPayment, PaymentStartDate " +
"FROM Clients;");
cmdCustomers.Connection = scWattsALoan;
scWattsALoan.Open();
System.Data.SqlClient.SqlDataAdapter sdaCustomers = new System.Data.SqlClient.SqlDataAdapter(cmdCustomers);
System.Data.DataSet dsCustomers = new System.Data.DataSet("clients");
sdaCustomers.Fill(dsCustomers);
if (dsCustomers.Tables[0].Rows.Count > 0)
{
<table class="table table-hover common-font">
<tr>
<td class="bold text-center">Loan #</td>
<td class="bold">Loan Type</td>
<td class="bold">Customer Name</td>
<td class="bold">Amount of Loan</td>
<td class="bold">Payment</td>
<td class="bold text-center">Start Date</td>
</tr>
@foreach (System.Data.DataRow drCustomer in dsCustomers.Tables[0].Rows)
{
<tr>
<td class="text-center">@drCustomer["LoanNumber"].ToString()</td>
<td>@drCustomer["LoanType"].ToString()</td>
<td>@drCustomer["CustomerFirstName"].ToString() @drCustomer["CustomerLastName"].ToString()</td>
<td>@drCustomer["LoanAmount"].ToString()</td>
<td>@drCustomer["MonthlyPayment"].ToString()/Month</td>
<td class="text-center">@DateTime.Parse(drCustomer["PaymentStartDate"].ToString()).ToShortDateString()</td>
</tr>
}
</table>
}
}
</div>Payments
A view is a query (a list of columns or a series of records retrieved) from one or more existing tables, or as a combination of one or more views and one or more tables, that is saved so it can be used one or more times. This means that, unlike a regular query, a query is a database object that has a name and is saved in the database. Before creating a view, you must first decide where its columns and records would come from. The easiest view is one whose columns and records come from one table.
Practical Learning: Creating Payments
using System;
using System.ComponentModel.DataAnnotations;
namespace WattsALoan1.Models
{
public class Payment
{
[Display(Name = "Payment ID")]
public int PaymentID { get; set; }
[Display(Name = "Receipt #")]
public int ReceiptNumber { get; set; }
[DataType(DataType.Date)]
[Display(Name = "Payment Date")]
public DateTime PaymentDate { get; set; }
[Display(Name = "Employee ID")]
public int EmployeeID { get; set; }
[Display(Name = "Loan Contract ID")]
public int LoanContractID { get; set; }
[Display(Name = "Payment Amount")]
public decimal PaymentAmount { get; set; }
public decimal Balance { get; set; }
}
}using System;
using System.Net;
using System.Data;
using System.Linq;
using System.Web.Mvc;
using WattsALoan1.Models;
using System.Data.SqlClient;
using System.Collections.Generic;
namespace WattsALoan1.Controllers
{
public class PaymentsController : Controller
{
private List<Payment> payments = new List<Payment>();
public List<Payment> GetPayments()
{
using (SqlConnection scWattsALoan = new SqlConnection(System.Configuration.
ConfigurationManager.
ConnectionStrings["csWattsALoan"].
ConnectionString))
{
SqlCommand cmdPayments = new SqlCommand("SELECT PaymentID, ReceiptNumber, " +
" PaymentDate, EmployeeID, " +
" LoanContractID, PaymentAmount, Balance " +
"FROM Management.Payments;",
scWattsALoan);
scWattsALoan.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++)
{
DataRow drPayment = dsPayments.Tables[0].Rows[i];
payments.Add(new Payment()
{
PaymentID = int.Parse(drPayment[0].ToString()),
ReceiptNumber = int.Parse(drPayment[1].ToString()),
PaymentDate = DateTime.Parse(drPayment[2].ToString()),
EmployeeID = int.Parse(drPayment[3].ToString()),
LoanContractID = int.Parse(drPayment[4].ToString()),
PaymentAmount = decimal.Parse(drPayment[5].ToString()),
Balance = decimal.Parse(drPayment[6].ToString())
});
}
}
return payments;
}
// GET: Payments
public ActionResult Index()
{
return View(GetPayments());
}
// GET: Payments/Details/5
public ActionResult Details(int id)
{
PaymentDetail payment = null;
if (id == 0)
{
return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
}
using (SqlConnection scWattsALoan = new SqlConnection(System.Configuration.
ConfigurationManager.
ConnectionStrings["csWattsALoan"].
ConnectionString))
{
SqlCommand cmdPayments = new SqlCommand("SELECT PaymentID, ReceiptNumber, ReceivedBy, " +
" PaymentDate, Customer, LoanType, " +
" LoanAmount, PaymentAmount, Balance " +
"FROM Management.PaymentsSummary;", scWattsALoan);
scWattsALoan.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++)
{
DataRow drPayment = dsPayments.Tables[0].Rows[i];
if (int.Parse(drPayment[0].ToString()) == id)
{
payment = new PaymentDetail()
{
PaymentID = int.Parse(drPayment[0].ToString()),
ReceiptNumber = int.Parse(drPayment[1].ToString()),
ReceivedBy = drPayment[2].ToString(),
PaymentDate = DateTime.Parse(drPayment[3].ToString()),
Customer = drPayment[4].ToString(),
LoanType = drPayment[5].ToString(),
LoanAmount = decimal.Parse(drPayment[6].ToString()),
PaymentAmount = decimal.Parse(drPayment[7].ToString()),
Balance = decimal.Parse(drPayment[8].ToString())
};
break;
}
}
}
if (payment == null)
{
return HttpNotFound();
}
ViewData["PaymentDate"] = payment.PaymentDate.ToLongDateString();
return View();
}
// GET: LoansContracts/PaymentStartUp
public ActionResult PaymentStartUp()
{
return View();
}
// GET: LoansContracts/LoanContractPreparation
public ActionResult PaymentPreparation(string EmployeeID, string LoanContractID)
{
decimal paymentAmount = 0;
decimal previousBalance = 0;
Random rndNumber = new Random();
ViewBag.ReceiptNumber = rndNumber.Next(100001, 999999);
using (SqlConnection scWattsALoan = new SqlConnection(System.Configuration.
ConfigurationManager.
ConnectionStrings["csWattsALoan"].
ConnectionString))
{
// Locate the employee based on the employee number that was entered
SqlCommand cmdEmployees = new SqlCommand("SELECT FirstName, LastName, EmploymentTitle " +
"FROM HumanResources.Employees " +
"WHERE EmployeeID = " + EmployeeID + ";")
{
Connection = scWattsALoan
};
scWattsALoan.Open();
// Store the employee in a data set.
SqlDataAdapter sdaEmployees = new SqlDataAdapter(cmdEmployees);
DataSet dsEmployees = new DataSet("employees");
sdaEmployees.Fill(dsEmployees);
// If there is an employee record for the employee number that was provided, ...
if (dsEmployees.Tables[0].Rows.Count > 0)
{
// ... create a string using that employee information and store that string in a view bag.
ViewBag.EmployeeDetails = dsEmployees.Tables[0].Rows[0][0].ToString() + " " +
dsEmployees.Tables[0].Rows[0][1].ToString() + " (" +
dsEmployees.Tables[0].Rows[0][2].ToString() + ")";
}
else
{
// If there is no employee for the employee number that was provided, don't sweat.
return RedirectToAction("LoanContractStartUp");
}
}
// Open a contract based on the loan number that was provided
using (SqlConnection scWattsALoan = new SqlConnection(System.Configuration.
ConfigurationManager.
ConnectionStrings["csWattsALoan"].
ConnectionString))
{
SqlCommand cmdContracts = new SqlCommand("SELECT CustomerFirstName, CustomerLastName, " +
" LoanType, LoanAmount, " +
" InterestRate, Periods, MonthlyPayment, " +
" EmployeeID, FutureValue, " +
" InterestAmount, PaymentStartDate " +
"FROM Management.LoanContracts " +
"WHERE LoanContractID = " + LoanContractID + ";");
cmdContracts.Connection = scWattsALoan;
scWattsALoan.Open();
SqlDataAdapter sdaContracts = new SqlDataAdapter(cmdContracts);
DataSet dsContracts = new DataSet("loans-contracts");
sdaContracts.Fill(dsContracts);
// If the loan number exists, ...
if (dsContracts.Tables[0].Rows.Count > 0)
{
// Prepare some information that will be displayed on a form
ViewBag.LoanDetails = "Granted to " + dsContracts.Tables[0].Rows[0]["CustomerFirstName"].ToString() + " " +
dsContracts.Tables[0].Rows[0]["CustomerLastName"].ToString() + " for a " +
dsContracts.Tables[0].Rows[0]["LoanType"].ToString() + " loan of " +
dsContracts.Tables[0].Rows[0]["LoanAmount"].ToString() + " (" +
dsContracts.Tables[0].Rows[0]["InterestRate"].ToString() + "% interest rate for " +
dsContracts.Tables[0].Rows[0]["Periods"].ToString() + " months).";
paymentAmount = decimal.Parse(dsContracts.Tables[0].Rows[0]["MonthlyPayment"].ToString());
/* We need the future value of the loan.
* It could be used as the previous balance if no payment has even been made on the loan. */
previousBalance = decimal.Parse(dsContracts.Tables[0].Rows[0]["FutureValue"].ToString());
}
}
// Open the list of payments if it contains some records
using (SqlConnection scWattsALoan = new SqlConnection(System.Configuration.
ConfigurationManager.
ConnectionStrings["csWattsALoan"].
ConnectionString))
{
// Get the list of payments that use the provided loan number
SqlCommand cmdContracts = new SqlCommand("SELECT Balance " +
"FROM Management.Payments " +
"WHERE LoanContractID = " + LoanContractID)
{
Connection = scWattsALoan
};
scWattsALoan.Open();
// Store the list of payments in a data set
SqlDataAdapter sdaPayments = new SqlDataAdapter(cmdContracts);
DataSet dsPayments = new DataSet("payments");
sdaPayments.Fill(dsPayments);
// If there is at least one payment made for the provided loan number, ...
if (dsPayments.Tables[0].Rows.Count > 0)
{
// ... scan the list of record from begining to end
for (int i = 0; i < dsPayments.Tables[0].Rows.Count; i++)
{
// The goal is to get the last balance that was set for the loan
previousBalance = decimal.Parse(dsPayments.Tables[0].Rows[i]["Balance"].ToString());
}
}
/* If no payment was ever made for the loan, then the previous balance is the future value.
* If there was at least one payment made for the loan, then a balance had been set.
* That balance will be used as the previous balance. */
// Calculate the ne balance by monthly payment from the previous balance
// Prepare the values to be sent to a form
ViewBag.Balance = previousBalance - paymentAmount;
ViewBag.PaymentAmount = paymentAmount;
ViewBag.PreviousBalance = previousBalance;
}
return View();
}
// GET: Payments/Create
public ActionResult Create()
{
return View();
}
// POST: Payments/Create
[HttpPost]
public ActionResult Create(FormCollection collection)
{
try
{
// TODO: Add insert logic here
if (!string.IsNullOrEmpty(collection["EmployeeID"]))
{
using (SqlConnection scWattsALoan = new SqlConnection(System.Configuration.
ConfigurationManager.
ConnectionStrings["csWattsALoan"].
ConnectionString))
{
SqlCommand cmdLoanPayment =
new SqlCommand("INSERT Management.LoanPayment " +
"VALUES(" + collection["ReceiptNumber"] + ", " + collection["EmployeeID"] +
", " + collection["LoanContractID"] + ", N'" + collection["PaymentDate"] +
"', " + collection["PaymentAmount"] + ", " + collection["Balance"] + ");");
cmdLoanPayment.Connection = scWattsALoan;
scWattsALoan.Open();
cmdLoanPayment.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 payment = null;
foreach (Payment pmt in GetPayments())
{
if (pmt.PaymentID == id)
{
payment = pmt;
break;
}
}
if (payment == null)
return HttpNotFound();
return View(payment);
}
// 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["csWattsALoan"].
ConnectionString))
{
SqlCommand cmdPayments = new SqlCommand("UPDATE Management.Payments " +
"SET ReceiptNumber = N'" + collection["ReceiptNumber"] + "', " +
" PaymentDate = N'" + collection["PaymentDate"] + "', " +
" EmployeeID = N'" + collection["EmployeeID"] + "', " +
" LoanContractID = N'" + collection["LoanContractID"] + "', " +
" PaymentAmount = N'" + collection["PaymentAmount"] + "', " +
" Balance = N'" + collection[" Balance"] + "' " +
"WHERE PaymentID = N'" + id + "';",
scRentManagement);
scRentManagement.Open();
cmdPayments.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 payment = null;
foreach (Payment pmt in GetPayments())
{
if (pmt.PaymentID == id)
{
payment = pmt;
break;
}
}
if (payment == null)
return HttpNotFound();
return View(payment);
}
// 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["csWattsALoan"].
ConnectionString))
{
SqlCommand cmdPayments = new SqlCommand("DELETE Management.Payments " +
"WHERE PaymentID = " + id + ";",
scRentManagement);
scRentManagement.Open();
cmdPayments.ExecuteNonQuery();
}
return RedirectToAction("Index");
}
catch
{
return View();
}
}
}
}@model IEnumerable<WattsALoan1.Models.Payment>
@{
/**/
ViewBag.Title = "Loans Payments";
}
<h2 class="bold maroon common-font text-center">Loans Payments</h2>
<table class="table table-hover common-font">
<tr>
<th class="text-center">@Html.DisplayNameFor(model => model.PaymentID)</th>
<th>@Html.DisplayNameFor(model => model.ReceiptNumber)</th>
<th>@Html.DisplayNameFor(model => model.PaymentDate)</th>
<th>@Html.DisplayNameFor(model => model.EmployeeID)</th>
<th>@Html.DisplayNameFor(model => model.LoanContractID)</th>
<th>@Html.DisplayNameFor(model => model.PaymentAmount)</th>
<th>@Html.DisplayNameFor(model => model.Balance)</th>
<th>@Html.ActionLink("Make Loan Payment", "PaymentStartUp")</th>
</tr>
@foreach (var item in Model)
{
<tr>
<td class="text-center">@Html.DisplayFor(modelItem => item.PaymentID)</td>
<td>@Html.DisplayFor(modelItem => item.ReceiptNumber)</td>
<td>@Html.DisplayFor(modelItem => item.PaymentDate)</td>
<td class="text-center">@Html.DisplayFor(modelItem => item.EmployeeID)</td>
<td class="text-center">@Html.DisplayFor(modelItem => item.LoanContractID)</td>
<td>@Html.DisplayFor(modelItem => item.PaymentAmount)</td>
<td>@Html.DisplayFor(modelItem => item.Balance)</td>
<td>
@Html.ActionLink("Edit", "Edit", new { id = item.PaymentID }) |
@Html.ActionLink("Details", "Details", new { id = item.PaymentID }) |
@Html.ActionLink("Delete", "Delete", new { id = item.PaymentID })
</td>
</tr>
}
</table>@model WattsALoan1.Models.Payment
@{
ViewBag.Title = "Payment Start-Up";
}
<h2 class="bold maroon common-font text-center">Payment Start-Up</h2>
@using (Html.BeginForm("PaymentPreparation", "Payments", FormMethod.Post))
{
<div class="containment">
<div class="form-horizontal common-font">
<div class="heading"><p class="text-center">Payment Received By</p></div>
<div class="form-group">
@Html.LabelFor(model => model.EmployeeID, htmlAttributes: new { @class = "control-label col-md-5 blue" })
<div class="col-md-7">
@Html.EditorFor(model => model.EmployeeID, new { htmlAttributes = new { @class = "form-control" } })
</div>
</div>
<div class="heading"><p class="text-center">Contract Information</p></div>
<div class="form-group">
@Html.LabelFor(model => model.LoanContractID, htmlAttributes: new { @class = "control-label col-md-5 blue" })
<div class="col-md-7">
@Html.EditorFor(model => model.LoanContractID, new { htmlAttributes = new { @class = "form-control" } })
</div>
</div>
</div>
<hr />
<p class="text-center">
<input type="submit" value="Prepare Loan Payment"
name="btnPrepareLoanPayment" class="btn btn-primary" />
</p>
</div>
}@model WattsALoan1.Models.Payment
@{
ViewBag.Title = "Loan Payment Preparation";
}
<h2 class="bold maroon common-font text-center">Loan Payment Preparation</h2>
@using (Html.BeginForm("Create", "Payments", FormMethod.Post))
{
<div class="containment1">
<div class="form-horizontal common-font">
<div class="heading"><p class="text-center">Payment Setup</p></div>
<div class="form-group">
@Html.LabelFor(model => model.EmployeeID, htmlAttributes: new { @class = "control-label col-md-3 blue" })
<div class="col-md-2">
@Html.EditorFor(model => model.EmployeeID, new { htmlAttributes = new { @class = "form-control" } })
</div>
<div class="col-md-6">@ViewBag.EmployeeDetails</div>
</div>
<div class="form-group">
<label class="control-label col-md-5 blue">Receipt #</label>
<div class="col-md-7">
@Html.TextBox("ReceiptNumber", ViewBag.ReceiptNumber as string, htmlAttributes: new { @class = "form-control" })
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.PaymentDate, htmlAttributes: new { @class = "control-label col-md-5 blue" })
<div class="col-md-7">
@Html.EditorFor(model => model.PaymentDate, new { htmlAttributes = new { @class = "form-control" } })
</div>
</div>
<div class="heading"><p class="text-center">Loan Information</p></div>
<div class="form-group">
<label class="control-label col-md-5 blue">Loan Contract ID</label>
<div class="col-md-7">
@Html.TextBox("LoanContractID", ViewBag.LoanContractID as string, htmlAttributes: new { @class = "form-control" })
</div>
</div>
<div class="form-group">
<label class="control-label col-md-5 blue">Loan Details</label>
<div class="col-md-7">
@Html.TextArea("LoanDetails", ViewBag.LoanDetails as string, htmlAttributes: new { @class = "form-control", cols = 60, rows = 4 })
</div>
</div>
<div class="form-group">
<label class="control-label col-md-5 blue">Previous Balance</label>
<div class="col-md-7">
@Html.TextBox("PreviousBalance", ViewBag.PreviousBalance as string, htmlAttributes: new { @class = "form-control" })
</div>
</div>
<div class="form-group">
<label class="control-label col-md-5 blue">Amount Paid</label>
<div class="col-md-3">
@Html.TextBox("PaymentAmount", ViewBag.PaymentAmount as string, htmlAttributes: new { @class = "form-control" })
</div>
<div class="col-md-3">
<div class="top-padding">/Month</div>
</div>
</div>
<div class="form-group">
<label class="control-label col-md-5 blue">New Balance</label>
<div class="col-md-7">
@Html.TextBox("Balance", ViewBag.Balance as string, htmlAttributes: new { @class = "form-control", cols = 60, rows = 4 })
</div>
</div>
<hr />
</div>
<p class="text-center"><input type="submit" value="Save Loan Contract" class="btn btn-primary" /></p>
</div>
}@model WattsALoan1.Models.PaymentDetail
@{
ViewBag.Title = "Payment Details";
}
<h2 class="bold text-center common-font maroon">Payment Summary</h2>
<hr />
<div class="containment">
<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.ReceivedBy)</dt>
<dd>@Html.DisplayFor(model => model.ReceivedBy)</dd>
<dt>@Html.DisplayNameFor(model => model.PaymentDate)</dt>
<dd>@ViewData["PaymentDate"]</dd>
<dt>@Html.DisplayNameFor(model => model.Customer)</dt>
<dd>@Html.DisplayFor(model => model.Customer)</dd>
<dt>@Html.DisplayNameFor(model => model.LoanType)</dt>
<dd>@Html.DisplayFor(model => model.LoanType)</dd>
<dt>@Html.DisplayNameFor(model => model.LoanAmount)</dt>
<dd>@Html.DisplayFor(model => model.LoanAmount)</dd>
<dt>@Html.DisplayNameFor(model => model.PaymentAmount)</dt>
<dd>@Html.DisplayFor(model => model.PaymentAmount)</dd>
<dt>@Html.DisplayNameFor(model => model.Balance)</dt>
<dd>@Html.DisplayFor(model => model.Balance)</dd>
</dl>
</div>
<p class="text-center">
@Html.ActionLink("Edit/Update this Payment", "Edit", new { id = Model.PaymentID }) ::
@Html.ActionLink("Payments", "Index")
</p>@model WattsALoan1.Models.Payment
@{
ViewBag.Title = "Delete Payment";
}
<h2 class="bold common-font text-center maroon">Delete Payment</h2>
<hr />
<h3 class="common-font maroon text-center">Are you sure you want to delete this employee's record?</h3>
<div class="containment">
<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.PaymentDate)</dt>
<dd>@Html.DisplayFor(model => model.PaymentDate)</dd>
<dt>@Html.DisplayNameFor(model => model.EmployeeID)</dt>
<dd>@Html.DisplayFor(model => model.EmployeeID)</dd>
<dt>@Html.DisplayNameFor(model => model.LoanContractID)</dt>
<dd>@Html.DisplayFor(model => model.LoanContractID)</dd>
<dt>@Html.DisplayNameFor(model => model.PaymentAmount)</dt>
<dd>@Html.DisplayFor(model => model.PaymentAmount)</dd>
<dt>@Html.DisplayNameFor(model => model.Balance)</dt>
<dd>@Html.DisplayFor(model => model.Balance)</dd>
</dl>
@using (Html.BeginForm())
{
@Html.AntiForgeryToken()
<div class="form-actions no-color">
@Html.ActionLink("Payments", "Index") ::
<input type="submit" value="Delete Payment" class="btn btn-primary" />
</div>
}
</div>@model WattsALoan1.Models.Payment
@{
ViewBag.Title = "Edit/Update Loan Payment";
}
<h2 class=" bold maroon common-font text-center">Edit/Update Loan 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-5" })
<div class="col-md-7">
@Html.EditorFor(model => model.ReceiptNumber, new { htmlAttributes = new { @class = "form-control" } })
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.PaymentDate, htmlAttributes: new { @class = "control-label col-md-5" })
<div class="col-md-7">
@Html.EditorFor(model => model.PaymentDate, new { htmlAttributes = new { @class = "form-control", type = "date" } })
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.EmployeeID, htmlAttributes: new { @class = "control-label col-md-5" })
<div class="col-md-7">
@Html.EditorFor(model => model.EmployeeID, new { htmlAttributes = new { @class = "form-control" } })
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.LoanContractID, htmlAttributes: new { @class = "control-label col-md-5" })
<div class="col-md-7">
@Html.EditorFor(model => model.LoanContractID, new { htmlAttributes = new { @class = "form-control" } })
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.PaymentAmount, htmlAttributes: new { @class = "control-label col-md-5" })
<div class="col-md-7">
@Html.EditorFor(model => model.PaymentAmount, new { htmlAttributes = new { @class = "form-control" } })
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.Balance, htmlAttributes: new { @class = "control-label col-md-5" })
<div class="col-md-7">
@Html.EditorFor(model => model.Balance, new { htmlAttributes = new { @class = "form-control" } })
</div>
</div>
<div class="form-group">
<label class="control-label col-md-5">@Html.ActionLink("Payments", "Index")</label>
<div class="col-md-7">
<input type="submit" value="Update Loan Payment" class="btn btn-primary" />
</div>
</div>
</div>
}Application Startup
Whenever you have created an application, you should provide a clear way for users to know where to start. For a website, you should take care of the layout page and the Index document of the Home controller.
Practical Learning: Performing Data Entry Using a View
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Watts' A Loan :: @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("Watts' A Loan", "Index", "Home", new { area = "" }, new { @class = "navbar-brand" })
</div>
<div class="navbar-collapse collapse">
<ul class="nav navbar-nav">
<li>@Html.ActionLink("Employees", "Index", "Employees")</li>
<li>@Html.ActionLink("Loans Contracts", "Index", "LoansContracts")</li>
<li>@Html.ActionLink("Payments", "Index", "Payments")</li>
<li>@Html.ActionLink("About Watts' A Loan", "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">© @DateTime.Now.Year - Watts' A Loan</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>
<p><a href="https://asp.net" class="btn btn-primary btn-lg">Learn more »</a></p>
</div>
<div class="row">
<div class="col-md-4">
<h2>Getting started</h2>
<p>
ASP.NET MVC gives you a powerful, patterns-based way to build dynamic websites that
enables a clean separation of concerns and gives you full control over markup
for enjoyable, agile development.
</p>
<p><a class="btn btn-default" href="https://go.microsoft.com/fwlink/?LinkId=301865">Learn more »</a></p>
</div>
<div class="col-md-4">
<h2>Get more libraries</h2>
<p>NuGet is a free Visual Studio extension that makes it easy to add, remove, and update libraries and tools in Visual Studio projects.</p>
<p><a class="btn btn-default" href="https://go.microsoft.com/fwlink/?LinkId=301866">Learn more »</a></p>
</div>
<div class="col-md-4">
<h2>Web Hosting</h2>
<p>You can easily find a web hosting company that offers the right mix of features and price for your applications.</p>
<p><a class="btn btn-default" href="https://go.microsoft.com/fwlink/?LinkId=301867">Learn more »</a></p>
</div>
</div>
| Employee # | First Name | Last Name | Employment Title |
| 293-747 | Catherine | Watts | Owner - General Manager |
| 836-486 | Thomas | Felton | Accounts Representative |
| 492-947 | Caroline | Wernick | Assistant Manager |
| 240-750 | Catherine | Donato | Accounts Representative |
| 804-685 | Melissa | Browns | Customer Accounts Representative |
| 429-374 | Denise | Leighton | Accounts Manager |
INSERT Employees
VALUES(N'293-747', N'Catherine', N'Watts', N'Owner'),
(N'836-486', N'Thomas', N'Felton', N'Accounts Representative'),
(N'492-947', N'Caroline', N'Wernick', N'Assistant Manager'),
(N'240-750', N'Catherine', N'Donato', N'Accounts Representative'),
(N'804-685', N'Melissa', N'Browns', N'Customer Accounts Representative'),
(N'429-374', N'Denise', N'Leighton', N'Accounts Manager');
GO
Processed By: 6 Loan Amount: 2500 Interest Rate: 14.65 Periods: 36
Allocation Date: 01/18/2018 First Name: Joanne Last Name: Kennan Payment Start Date: 02/01/2018
| Processed By | Loan Amount | Interest Rate | Periods | Allocation Date | First Name | Last Name | Type of Loan | Payment Start Date |
| 3 | 16500 | 12.25 | 60 | 01/22/2018 | Stephanie | Haller | Boat Financing | 03/01/2018 |
| 6 | 2258.75 | 16.15 | 36 | 03/12/18 | Annette | Vargas | Furniture Purchase | 01-May-2018 |
| 2 | 22748 | 10.25 | 60 | 12-Mar-18 | Gérard | Maloney | Car Financing | 05-01-2018 |
Employee ID: 6 Loan Contract ID: 1
| Employee ID | Loa Contract ID | Payment Date |
| 3 | 2 | 03/30/2018 |
| 4 | 1 | 04/01/18 |
| 6 | 1 | 04-30-2018 |
| 2 | 2 | 04-30-2018 |
| 2 | 4 | 05-22-2018 |
| 2 | 1 | 25-May-2018 |
| 6 | 3 | 05/28/2018 |
| 6 | 2 | 30-May-2018 |
| 3 | 4 | 31-May-18 |
| 2 | 3 | 30-Jun-2018 |
| 2 | 1 | 02-Jul-18 |
|
|
|||
| Home | Copyright © 2001-2022, FunctionX | Thursday 05 May 2022 | Home |
|
|
|||