Home

C# Application: Watts A Loan

   

Creating the Database

Start Microsoft SQL Server. Open a Query window and type the following code:

USE master;
GO

IF EXISTS (
  SELECT * 
    FROM sys.databases 
   WHERE name = N'WattsALoan1'
)
  DROP DATABASE WattsALoan1
GO
CREATE DATABASE WattsALoan1;
GO
USE WattsALoan1;
GO

CREATE SCHEMA Loans;
GO
CREATE SCHEMA Personnel;
GO
CREATE SCHEMA Accounts;
GO
CREATE TABLE Personnel.Employees
(
	EmployeeNumber nchar(7) Not Null, 
	FirstName nvarchar(25) NULL,
	LastName nvarchar(25) Not Null,
	FullName AS CONCAT(LastName, ', ', FirstName),
    	Title nvarchar(100),
	WorkPhone nvarchar(20),
	Address nvarchar(100),
    	City nvarchar(50),
	State nvarchar(50),
    	ZIPCode nvarchar(10),
    	Constraint PK_Employees Primary Key(EmployeeNumber)
);
GO

CREATE TABLE Accounts.Customers
(
	AccountNumber nchar(12) Not Null,
	AccountDate nvarchar(50), 
	FirstName nvarchar(24) NULL,
	MiddleName nvarchar(24),
	LastName nvarchar(25) Not Null,
    	CustomerName nvarchar(75),
	WorkPhone nvarchar(20),
    	HomePhone nvarchar(20), 
	Address nvarchar(100),
    	City nvarchar(50),
	State nvarchar(50),
    	ZIPCode nvarchar(10),
	EmailAddress nvarchar(100),
    	Constraint PK_Customers Primary Key(AccountNumber)
);
GO
CREATE TABLE Loans.Types
(
	LoanType nvarchar(40),
	TypeDescription nvarchar(max),
    	Constraint PK_LoansTypes Primary Key(LoanType)
);
GO

CREATE TABLE Loans.Allocations
(
	LoanNumber int identity(100001, 1) Not Null,
    	DateAllocated date,
	EmployeeNumber nchar(7),
    	AccountNumber nchar(12),
    	LoanType nvarchar(40),
    	LoanAmount money Not Null,
	InterestRate decimal(6, 2) Not Null,
    	Periods decimal(6,2) Not Null,
    	InterestAmount money,		-- AS ((LoanAmount * (InterestRate / 100)) * (Periods / 12)),
    	FutureValue money,		-- AS (LoanAmount + (LoanAmount * (InterestRate / 100)) * (Periods / 12)),
    	MonthlyPayment money,		-- AS ((LoanAmount + (LoanAmount * (InterestRate / 100)) * (Periods / 12)) / Periods),
    	Notes nvarchar(max),
    	Constraint PK_LoanAllocations Primary Key(LoanNumber),
	Constraint FK_AllocationsClerks Foreign Key(EmployeeNumber) References Personnel.Employees(EmployeeNumber),
	Constraint FK_Customers Foreign Key(AccountNumber) References Accounts.Customers(AccountNumber),
	Constraint FK_LoanTypes Foreign Key(LoanType) References Loans.Types(LoanType)
);
GO
CREATE TABLE Loans.Payments
(
	PaymentID int identity(1001, 1) Not Null,
	PaymentDate date Not Null,
	EmployeeNumber nchar(7),
    	LoanNumber int Not Null,
    	PaymentAmount money Not Null,
	Balance money,
	Notes nvarchar(max),
    	Constraint PK_Payments Primary Key(PaymentID),
	Constraint FK_PaymentsClerks Foreign Key(EmployeeNumber) References Personnel.Employees(EmployeeNumber),
    	Constraint FK_LoanAllocations Foreign Key(LoanNumber) References Loans.Allocations(LoanNumber)
);
GO

INSERT INTO Loans.Types(LoanType, TypeDescription)
VALUES(N'Personal Loan', N'This is loan given as a cashier check to a customer who wants a cash loan'),
	  (N'Car Financing', N'This loan will be processed by our partners as car dealers');
GO
INSERT INTO Loans.Types(LoanType)
VALUES(N'Boat Purchase'),(N'Furniture');
GO
INSERT INTO Loans.Types(LoanType, TypeDescription)
VALUES(N'Musical Instrument', N'We have some partnerships in musical instruments stores. This is the type of loan we will make available to the customers they find for us');
GO

INSERT INTO Personnel.Employees(EmployeeNumber, FirstName, LastName, Title, State)
VALUES(N'836-486', N'Ernest', N'Thomas', N'Accounts Representative', N'MD');
GO
INSERT INTO Personnel.Employees(EmployeeNumber, FirstName, LastName, Title, WorkPhone, Address, City, State)
VALUES(N'492-947', N'Sandrine', N'Ethridge', N'Assistant Manager', N'(410) 781-6238', N'408 Dorsey Rd', N'Columbia', N'MD');
GO
INSERT INTO Personnel.Employees(EmployeeNumber, FirstName, LastName, Title, State) VALUES(N'240-750', N'Helene', N'Gustman', N'Accounts Representative', N'MD');
GO
INSERT INTO Personnel.Employees(EmployeeNumber, FirstName, LastName, Title, WorkPhone, City, State)
VALUES(N'804-685', N'Melissa', N'Browns', N'Customer Accounts Representative', N'(410) 781-6238', N'Owings Mills', N'MD');
GO
INSERT INTO Personnel.Employees(EmployeeNumber, FirstName, LastName, Title, WorkPhone, City, State, ZIPCode)
VALUES(N'429-374', N'Jake', N'Leighton', N'Accounts Manager', N'(410) 781-6238', N'Laurel', N'MD', N'20707');
GO
INSERT INTO Personnel.Employees(EmployeeNumber, FirstName, LastName, Title, WorkPhone, Address, City, State, ZIPCode)
VALUES(N'293-747', N'Catherine', N'Watts', N'Owner', N'(410) 781-6238', N'8012 New Castle Blvd', N'Baltimore', N'MD', N'21212');
GO

INSERT INTO Accounts.Customers(AccountNumber, AccountDate, FirstName, MiddleName, LastName, CustomerName, Address, City, State, ZIPCode, WorkPhone, HomePhone, EmailAddress)
VALUES(N'29-750024-82', N'20140216', N'Joanne', N'Lucille', N'Sinton', N'Sinton, Joanne Lucille', N'482 Bayless Rd', N'Timonium', 'MD', N'21212', N'(410) 789-8000', N'(410) 647-8000', N'sintonj@umd.edu');
GO
INSERT INTO Accounts.Customers(AccountNumber, AccountDate, FirstName, MiddleName, LastName,  CustomerName, Address, City, State, ZIPCode, WorkPhone, HomePhone)
VALUES(N'64-827095-07', N'20140422', N'Helene', N'Felicia', N'Fuchs', N'Fuchs, Helene Felicia', N'2208 Hiss Ave', N'Towson', 'MD', N'21208', N'(301) 621-1001', N'(410) 665-8118');
GO
INSERT INTO Accounts.Customers(AccountNumber, AccountDate, FirstName, LastName,  CustomerName, Address, City, State, ZIPCode, HomePhone, EmailAddress) 
VALUES(N'83-401857-88', N'20140424', N'Alexander', N'Lozanski', N'Lozanski, Alexander', N'7382 Lilas Drive', N'Baltimore', 'MD', N'21202', N'(410) 740-0074)', N'lozanfriend@yahoo.com');
GO
INSERT INTO Accounts.Customers(AccountNumber, AccountDate, FirstName, MiddleName, LastName,  CustomerName, Address, City, State, ZIPCode, HomePhone) 
VALUES(N'92-738024-35', N'20140615', N'Gerard', N'Lowell', N'Valleys', N'Valleys, Gerard Lowell', N'308 Avondale Drive', N'Baltimore', 'MD', N'21204', N'(410) 296-1072');
GO

CREATE VIEW Loans.ShowAllocations
AS
SELECT la.LoanNumber AS [Loan #],
       la.DateAllocated AS [Date Prepared],
       clerks.EmployeeNumber + N': ' + clerks.LastName + N', ' + clerks.FirstName AS [Processed By],
       clients.AccountNumber AS [Processed For],
       clients.CustomerName AS [Customer Name],
       categories.LoanType AS [Loan Type],
       la.LoanAmount AS Amount,
       la.InterestRate AS [Interest Rate],
       la.Periods AS [Loan Length],
       la.InterestAmount AS [Interest Amount],
       la.FutureValue AS [Future Value],
       la.MonthlyPayment AS [Monthly Payment],
       la.Notes
FROM   Loans.Allocations AS la
INNER JOIN Personnel.Employees AS clerks
       ON la.EmployeeNumber = clerks.EmployeeNumber
INNER JOIN Loans.Types AS categories
       ON la.LoanType = categories.LoanType
INNER JOIN Accounts.Customers AS clients
       ON la.AccountNumber = clients.AccountNumber;
GO

CREATE VIEW Loans.ShowPayments
AS
SELECT  pmts.PaymentID,
        pmts.PaymentDate AS [Payment Date],
        clerks.EmployeeNumber + N' - ' + clerks.FullName AS [Processed By],
        pmts.LoanNumber AS [Loan #],
        la.AccountNumber + N' - ' + clients.CustomerName AS [Loan For Customer],
	pmts.PaymentAmount AS [Pmt Amount],
        pmts.Balance,
        pmts.Notes
FROM 	Loans.Payments AS pmts
INNER JOIN Personnel.Employees AS clerks
	ON pmts.EmployeeNumber = clerks.EmployeeNumber
INNER JOIN Loans.Allocations AS la
	ON pmts.LoanNumber = la.LoanNumber AND pmts.LoanNumber = la.LoanNumber
INNER JOIN Accounts.Customers AS clients
	ON la.AccountNumber = clients.AccountNumber;
GO

Execute it to create the database and its objects (tables and views)

Crating the Visual Application

In one of your hard drives, such as the C: drive, create a folder named wal. Copy the Customer.cs, the Employees.cs, the LoanAllocationNew.cs, the LoanAllocationEditor.cs, the LoansAllocations.cs, the LoanPaymentNew.cs, the LoansPayments.cs, and the WattsALoan.cs C# files to the wal folder. Open the Command Prompt. Switch to the wal folder that contains the C# files:

Command Prompt

To compile, type:

C:\Windows\Microsoft.NET\Framework\v4.0.30319\csc Customers.cs Employees.cs 
LoanAllocationNew.cs LoanAllocationEditor.cs LoansAllocations.cs 
LoanPaymentNew.cs LoansPayments.cs WattsALoan.cs

To execute, type WattsALoan

Command Prompt

  1. Click the Loans Allocations button
  2. Click the New Loan Allocation button and enter the following values:
    Date Allocated: 2/6/2014
    Employee #: 429-374
    Account #: 29-750024-82
    Loan Type: Personal Loan
    Loan Amount: 6500
    Interest Rate: 12.65
    Periods: 36.00
    Notes: The customer simply walked to the office and applied for a personal loan. The loan was granted.
  3. Click Calculate
  4. Click Submit
  5. Create the following additional allocations (click Calculate whenever you have entered the information, then click Submit):
     
    Date Allocated Employee # Account # Loan Type Loan Amount Interest Rate Periods
    4/22/2014 492-947 64-827095-07 Boat Purchase 16500 10.20 60.00
    4/25/2014 429-374 83-401857-88 Furniture 2455 14.88 36
    6/4/2014 836-486 92-738024-35 Car Financing 20545 16.25 48
  6. Click Loan Allocation Editor button and change the following allocations (the values to change are in bold characters) (click Calculate when the numeric values have changed, then click Submit):
     
    Loan # Date Allocated Processed By Account # Loan Type Loan Amount Interest Rate Periods Notes
    100003 4/25/2014 429-374 83-401857-88 Musical Instrument 2258.75 12.25 36 This loan was originally processed through the Washington Music Center.
    100004 6/24/2014 293-747 92-738024-35 Car Financing 22748 10.25 60 This loan is to finance a Honda Civic with a Silver Spring Honda dealer.
  7. Close the Loan Allocation Editor button
  1. Click the Loans Payments button
  2. Click the New Payment button and create the following records:
     
    Payment Date Employee # Loan # Pmt Amt Notes
    3/25/2014 240-750 100001 249.08 This is the first payment
    4/28/2014 804-685 100001 249.08  
    5/26/2014 804-685 100002 415.25 This is the first payment
    5/30/2014 492-947 100003 85.80 This is the first payment
    6/2/2014 429-374 100001 249.08  
    6/26/2014 836-486 100001 249.08  
    6/26/2014 836-486 100002 415.25  
    6/26/2014 804-685 100003 85.80  
    6/26/2014 836-486 100004 573.44 This is the first payment
    7/25/2014 836-486 100002 415.25  
    7/31/2014 240-750 100003 85.80  
    7/31/2014 492-947 100004 573.44  
    8/5/2014 804-685 100001 498.16 The customer sent a double-payment for July and August.
    8/10/2014 836-486 100002 450.25 This payment includes the regular monthly payment of $415.25 and a $35 payment for late fee.
    8/27/2014 240-750 100003 85.80  
    8/28/2014 804-685 100004 573.44  
    8/31/2014 836-486 100002 450.25 This payment includes the regular monthly payment of $415.25 and a $35 payment for late fee.
    9/3/2014 429-374 100002 415.25  
    9/22/2014 492-947 100002 415.25  
    9/25/2014 240-750 100004 573.44  
    9/30/2014 492-947 100001 249.08  
    10/2/2014 240-750 100002 415.25  
    10/25/2014 240-750 100003 204.24 This is a double-payment
    10/26/2014 240-750 100001 249.08  
    10/26/2014 429-374 100002 415.25  
    10/30/2014 429-374 100004 573.44  
    10/31/2014 804-685 100002 415.25  
    11/26/2014 836-486 100004 573.44  
    11/28/2014 240-750 100001 249.08  
    11/28/2014 240-750 100002 415.25  
    11/30/2014 804-685 100003 85.80  
    12/22/2014 240-750 100002 890.50 This payment include two-month payment and 2 * $30 for late payments.
    12/28/2014 492-947 100001 249.08  
    12/28/2014 429-374 100004 573.44  
    1/4/2015 836-486 100003 85.80  
    1/25/2015 804-685 100004 573.44  
    1/31/2015 836-486 100003 85.80  
    2/2/2015 836-486 100002 415.25  
    2/5/2015 836-486 100001 498.16 We received a double-payment for January 2015 and February 2015.
    2/28/2015 836-486 100002 415.25  
    3/30/2015 429-374 100001 249.08  
    5/24/2015 492-947 100001 249.08  
  3. Close the form(s) and return to your programming environment

Home Copyright © 2013-2014 FunctionX