Amoritization Calculator (3/5/2012)

 This was a Java programming project from Phoenix. The goal was to create a loan amoritization calculator. There were a few change requests to add and remove features over the course of the project. This is a screen shot of the program in action:

As this flowchart of the program details, it is designed to create a loan amoritization table based on either user defined terms, or presets. It can even load new presets from a CSV file. CSV was chosen because it is a simple format supported by even the most basic spreadsheet programs.

And the Payment Loop:

This is the code for the project. Project guidelines required the code to be in a single file, so it may seem a little long and disorganized.

/**********************************************************************
*            Program:                Mortgage Payment

*            Purpose:                 Week 3 Individual Assignment

*            Programmer:           Dylan Sloboda                  

*            Class:                    PRG/421                        

*            Instructor:               Vince Anderson               

*            Creation Date:         March 5, 2012                      

***********************************************************************

*            Program Summary:  

*            Amoritization Calculator for change request 5.

*  

**********************************************************************/
import java.awt.BorderLayout;
import java.awt.Component;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import javax.swing.BoxLayout;
import javax.swing.JButton;
import javax.swing.JCheckBox;
import javax.swing.JFileChooser;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JPanel;
import javax.swing.JScrollPane;
import javax.swing.JTextArea;
import javax.swing.JTextField;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.text.DecimalFormat;
import java.util.StringTokenizer;

public class AmoritizationCalc extends JFrame implements ActionListener    // class definition
{
/**
     *
     */
    private static final long serialVersionUID = 1L;
    double [][] loanTIA;{
    loanTIA = new double[3][2]; //This array holds 2 arrays consisting of the loan terms and interest rates
    loanTIA[0][0] = 84;
    loanTIA[0][1] = 0.0535;
    loanTIA[1][0] = 180;
    loanTIA[1][1] = 0.055;
    loanTIA[2][0] = 360;
    loanTIA[2][1] = 0.0575;}
JTextField principleTxt = new JTextField(7);
JTextField termTxt = new JTextField(3);
JTextField interestTxt = new JTextField(4);
JCheckBox optionOne = new JCheckBox("7 Years at 5.35%");
JCheckBox optionTwo = new JCheckBox("15 Years at 5.5%");
JCheckBox optionThree = new JCheckBox("30 Years at 5.75%");
JButton calcButton = new JButton("Calculate");
JButton loadOpt = new JButton("Load terms from file");
JTextArea pymntAmtTxt = new JTextArea("Monthly payment");
JPanel principle = new JPanel();
JPanel term = new JPanel();
JPanel interest = new JPanel();
JPanel loanOptions = new JPanel();
JPanel loanTerms = new JPanel();
JScrollPane scrollingResult = new JScrollPane(pymntAmtTxt);
JPanel bottomPanel = new JPanel();
JPanel topPanel = new JPanel();
JPanel wholePane = new JPanel();

public AmoritizationCalc()    // constructor
    {
    principle.add(principleTxt);
    principle.add(new JLabel("Principle"), BorderLayout.WEST);
    term.add(termTxt);
    term.add(new JLabel("Loan Term (months)"), BorderLayout.WEST);
    interest.add(interestTxt);
    interest.add(new JLabel("Interest rate(as decimal)"), BorderLayout.WEST);
    loanOptions.add(optionOne);
    loanOptions.add(optionTwo);
    loanOptions.add(optionThree);
    loanOptions.add(loadOpt, BorderLayout.SOUTH);
    loanOptions.setLayout(new BoxLayout(loanOptions, BoxLayout.PAGE_AXIS));
    bottomPanel.setLayout(new BoxLayout(bottomPanel, BoxLayout.PAGE_AXIS));
    bottomPanel.add(scrollingResult, BorderLayout.SOUTH);
    loanTerms.add(principle, BorderLayout.LINE_START);
    loanTerms.add(term, BorderLayout.LINE_START);
    loanTerms.add(interest, BorderLayout.LINE_START);
    loanTerms.setLayout(new BoxLayout(loanTerms, BoxLayout.PAGE_AXIS));
    topPanel.add(loanTerms, BorderLayout.WEST);
    topPanel.add(loanOptions, BorderLayout.EAST);
    wholePane.add(topPanel, BorderLayout.NORTH);
    wholePane.add(calcButton);
    calcButton.setAlignmentX(Component.CENTER_ALIGNMENT);
    wholePane.add(bottomPanel, BorderLayout.SOUTH);
    wholePane.setLayout(new BoxLayout(wholePane, BoxLayout.PAGE_AXIS)); // Box layout requires arguments
    getContentPane().add(wholePane, BorderLayout.CENTER);
    calcButton.addActionListener(this);
    optionOne.addActionListener(this);
    optionTwo.addActionListener(this);
    optionThree.addActionListener(this);
    loadOpt.addActionListener(this);
    setDefaultCloseOperation(DISPOSE_ON_CLOSE);
    setTitle("Amoritization Calculator");
    }

public static void main(String[] args)    // main thread
     {
    AmoritizationCalc amoritizationApp = new AmoritizationCalc();
    amoritizationApp.setLocation(150, 100);
    amoritizationApp.setSize(425, 350);
    amoritizationApp.setVisible(true);
    amoritizationApp.setName("Amoritization Calculator");
   
    }

public void actionPerformed(ActionEvent e)    // event thread. This program is event driven.
    {
    boolean selected = false;
    double princAmt = 0;
    double numMon = 0;
    double intRate = 0;

    DecimalFormat money = new DecimalFormat("$0.00");
    if (e.getSource() == calcButton) {
    try    {
        princAmt = Double.parseDouble(principleTxt.getText());    // these lines collect the data entered
        numMon = Double.parseDouble(termTxt.getText());
        intRate = (Double.parseDouble(interestTxt.getText()))/12;    //interest rate per month   
        }
    catch (NumberFormatException x) {    // this code ensures that the data entered is the appropriate format
        principleTxt.setText("");
        termTxt.setText("");
        interestTxt.setText("");
        }
    optionOne.setEnabled(true); // these clear the options and make the boxes editable again
    optionOne.setSelected(false);
    optionTwo.setEnabled(true);
    optionTwo.setSelected(false);
    optionThree.setEnabled(true);
    optionThree.setSelected(false);
    termTxt.setEditable(true);
    interestTxt.setEditable(true);
    double monPymt = 0; // the monthly payment
    monPymt = (princAmt * (intRate * (Math.pow((1 + intRate), numMon)))/(Math.pow((1 + intRate), numMon) - 1)); // the amoritization formula for finding periodic payment amount
    pymntAmtTxt.setText("Term: " + numMon + " Rate: " + intRate + " Monthly payment amount: " + money.format(monPymt));
    double pymtInt=0;
    double pymtPrinc=0;
    double balance=princAmt;
    for (int n=0; n<numMon ; n++) {
        pymtInt=intRate * balance;
        pymtPrinc=monPymt - pymtInt;
        balance=balance-pymtPrinc;
        pymntAmtTxt.append("\n");
        pymntAmtTxt.append("Payment " + n + " : interest " + money.format(pymtInt) + ", principal " + money.format(pymtPrinc) + ", Balance " + money.format(balance));
        }
    }

    else if (e.getSource() == optionOne) { //each one of these disables the other boxes and sets the term and interest
        selected = optionOne.isSelected();
        if (selected) {
            optionTwo.setEnabled(false);
            optionThree.setEnabled(false);
            termTxt.setText("" + loanTIA[0][0]);
            interestTxt.setText("" + loanTIA[0][1]);
            termTxt.setEditable(false);
            interestTxt.setEditable(false);
            }
        else {
            optionOne.setEnabled(true); // these clear the options and make the boxes editable again
            optionOne.setSelected(false);
            optionTwo.setEnabled(true);
            optionTwo.setSelected(false);
            optionThree.setEnabled(true);
            optionThree.setSelected(false);
            termTxt.setEditable(true);
            interestTxt.setEditable(true);
            }
        }
    else if (e.getSource() == optionTwo) {
        selected = optionTwo.isSelected();
        if (selected) {
            optionOne.setEnabled(false);
            optionThree.setEnabled(false);
            termTxt.setText("" + loanTIA[1][0]);
            interestTxt.setText("" + loanTIA[1][1]);
            termTxt.setEditable(false);
            interestTxt.setEditable(false); }
        else {
            optionOne.setEnabled(true); // these clear the options and make the boxes editable again
            optionOne.setSelected(false);
            optionTwo.setEnabled(true);
            optionTwo.setSelected(false);
            optionThree.setEnabled(true);
            optionThree.setSelected(false);
            termTxt.setEditable(true);
            interestTxt.setEditable(true);
            }
    }
    else if (e.getSource() == optionThree) {
        selected = optionThree.isSelected();
        if (selected) {
            optionTwo.setEnabled(false);
            optionOne.setEnabled(false);
            termTxt.setText("" + loanTIA[2][0]);
            interestTxt.setText("" + loanTIA[2][1]);
            termTxt.setEditable(false);
            interestTxt.setEditable(false); }
        else {
            optionOne.setEnabled(true); // these clear the options and make the boxes editable again
            optionOne.setSelected(false);
            optionTwo.setEnabled(true);
            optionTwo.setSelected(false);
            optionThree.setEnabled(true);
            optionThree.setSelected(false);
            termTxt.setEditable(true);
            interestTxt.setEditable(true);
            }
        }
    else if (e.getSource() == loadOpt) { // this button event opens a jfile chooser to read CSV files with 3 rows and 2 columns
            JFileChooser optLoad = new JFileChooser();
            File selectedFile = null;
            BufferedReader in;
            FileReader fReader = null;
            if (optLoad.showOpenDialog(this)==JFileChooser.APPROVE_OPTION)
            {
                selectedFile = optLoad.getSelectedFile();
            }
            try {
                fReader = new FileReader(selectedFile);
            } catch (FileNotFoundException e2) {
                // TODO Auto-generated catch block
                e2.printStackTrace();
            }
            in = new BufferedReader(fReader);
            String[][] TempArray = new String[3][2]; // temporary string array for reading the data into
            int row = 0;
            int col = 0;
            String strLine;
            try {
                while ((strLine = in.readLine()) != null && row < 3)
                {
                    StringTokenizer st = new StringTokenizer(strLine,","); // divided as Comma Separated Values
                    while (st.hasMoreTokens())
                    {
                        TempArray[row][col] = st.nextToken();
                        col++;
                    }
                    col = 0;
                    row++;
                }
            } catch (IOException e1) {
                // TODO Auto-generated catch block
                e1.printStackTrace();
            }
            try {
                in.close();
            } catch (IOException e1) {
                // TODO Auto-generated catch block
                e1.printStackTrace();
            }
        for (int i=0; i < 3; i++)
        {
            for (int n=0; n<2; n++)
            {
                loanTIA[i][n] = Double.parseDouble(TempArray[i][n]); // parse each field from the temporary array as double for loanTIA array
            }
        }
        optionOne.setText((loanTIA[0][0]/12)+" Years at "+(loanTIA[0][1]*100)+"%"); // assign new labels to match
        optionTwo.setText((loanTIA[1][0]/12)+" Years at "+(loanTIA[1][1]*100)+"%");
        optionThree.setText((loanTIA[2][0]/12)+" Years at "+(loanTIA[2][1]*100)+"%");
       
        }
    }
}

SQL Reports (11/16/2011)

This was one of my later SQL projects at Phoenix. All things considered, I liked working with SQL. The syntax is flexible and uncomplicated... even in Microsoft's implementation of it. The project instructions were as follows:

• Sort all employees by payroll, in descending order.

• Calculate the total payroll of exempt and non-exempt employees (before & after the adjustments are made).

• Choose a specific job_title and decrease the salary of all individuals in this category by 3.5%. 

• Increase the remaining employee’s salaries by 5%. 

• Add a ‘Senior Accountant’ position to the Job_title table.

• You discovered that the newly created position should not have been added to your database. As a result, this information needs to be deleted from the table.

The HR Department recently submitted a change request. They want the following information extracted from the database.

• Calculate the number of exempt and non-exempt employees there are, by job title. The results should be sorted in descending order.

• Determine if you have any employees whose salary is below the minimum salary for their job classification. If so, who are they? 

• Calculate the average, minimum and maximum salary for exempt employees. Using the UNION command also display the same information for the non-exempt employees. 

• Calculate the difference between the maximum and minimum salaries for the non-exempt employees. 

• Create a query that shows all employees whose name has the second letter of either an ‘a’,’e’,’i’,’r’ or ‘u’.

 Using a CASE statement, illustrate:

• Assuming that today’s date is 12/16/2020, create a temporary field that calculates the total service time (in years) of each employee. In the same query, use a CASE statement to identify employees who are over 52 years old and have over 20 years of service time with the company. Identify them as “Potential Retirees”.

1-A. Sorting all employees by payroll went as expected.

2-A. Total payroll of exempt and non-exempt employees was calculated without issue.

3. The decrease in pay for one title and increase for all others were handled as one query using a CASE statement. Initially using the CASE statement in the update did not work. I forgot to add the END statement. After doing so it ran without issues.

1-B. The same query as 1-A but after the update.

2-B. The same query as 2-A, with updated data.

4. The Senior accountant position was easy to add.

5. And just as easy to delete.

6. When I initially wrote this query, it was only grouped by job title. For reasons I did not understand, it did not work until the exempt-nonexempt status was included with the GROUP BY clause. According to the error message, it could not be included in the SELECT statement without doing so.

7. There are no employees whose salary is below the minimum.

8. The first time around when using AVG, MIN and MAX I mixed up which part of the statement goes in the parentheses. After rearranging everything, it worked.

9. This query was easy to perform.

10. I assumed this task was referring to first names. I used a multiple ORs and the SUBSTRING selection statement to find the records.

11. This query did not work until single quotes were used around the date. There is no age data for the employees, so they all return as “Regular”.