Skip to main content Link Menu Expand (external link) Document Search Copy Copied

Finance Tracker

This was a historic assignment on Databases. While we won't have time for an assignment like this, you may wish to use this one as practice for interactions with a database. This is NOT a homework for this class, it is provided as an option for anyone who would like more practice.

Purpose:

  • Gain experience writing in PHP with databases

Overview

For this homework, you may work alone or with another student in this course. You will implement a finance tracker (i.e., checkbook register) using a front-controller design pattern with classes in PHP. The finance tracker will store user data in a MySQL database and provide users the ability to add financial transactions and view a list of all prior transactions. Users will log in with their email and a password, with sessions tracked using $_SESSION.

Financial Management Software Requirements

Move over Mint and Quicken, we’re writing our own finance-tracking software! Specifically, our software will require the users to log in (or implicitly create an account) and will then show them their transaction history. While logged in, the user can add a new transaction, including a name, amount, date, category (i.e., groceries, electronics, etc), and whether it was a credit or debit. They can also see how much they have spent in each category, and view their current balance to see if they should purchase that new Mac Studio!

For this assignment, you must implement the following components. (You may implement more functionality if you wish, but remember that we’ll be grading on effort in implementing these components.)

  1. Front controller
    • Create an index.php file, which will receive all of the requests for your application. It should instantiate and run your finance software’s controller as well as handling any necessary session management.
    • Create a FinanceController class that contains the controller to handle all of the logic for your application.
  2. Your application should have at least the following 3 views/templates available to the user. You may choose to include more.
    1. Login page. It must ask the user for at least their name, email, and password. You may use the login page you created for Homework 4 or our example from the Trivia game.
    2. Transaction history. This template will display all of the user’s prior transaction history in a <table> in reverse order, with the newest at the top. At the top of the page (such as in the nav bar), display the user’s name and email, as well as options to log out and add a new transaction. You must also display the current balance (i.e., the sum of all their transactions), as well as their totals per category (i.e., a table of categories with the sum of all transactions for each category).
    3. New transaction. This template will include a form with the following inputs:
      • Name - the name of the transaction (usually where they spent the money)
      • Category - the overall category of this transaction (for credits, this could be paychecks, repayments, etc; for debits, this could be groceries, rent, electronics, etc.). You should determine the best way to implement the category; for example, it could be a text box for the user to type their own category or a select box with pre-defined categories stored in a database table for the user to choose.
      • Date - the date the transaction occurred
      • Amount - the amount of the transaction.
      • Type - the type of the transaction. This should have two options: Debit (withdrawal) or Credit (deposit).
  3. You must implement the following logic for each of your views/templates:
    1. Login functionality. The user must be able to log in and their session must be tracked until they log out.
      • The user must be able to enter at least their name, email, and a password.
      • If the user is in the database, then verify their password. If the password succeeds, then display their transaction history.
      • If the user is not in the database, add them as a new user and display the transaction history view.
    2. New transaction functionality. The user must be able to enter new transactions into their history.
      • When the user submits a new transaction, it must be entered into the database. If the Type is Debit, then the amount must be entered as a negative number. If the Type is Credit, then the amount must be entered as a positive number.
      • After submitting a new transaction, the transaction history should be displayed (and should include the new transaction).
  4. For portability between Docker and the cs4640 server, you should use query string variables ($_GET variables) to pass commands to the controller rather than using Apache’s mod_rewrite and a .htaccess config file. That is, you will not need the .htaccess file we mentioned in class.
    For example, you may wish to use the command variable to determine the current view; sending the user to index.php?command=transactions would then display the transaction history. The Controller may then determine logic to take based on the $_GET["command"] value.
    Note: you may use mod_rewrite, if you wish, but it may lead to unexpected results. It is discouraged for this assignment.
  5. User information and transaction history should be stored in your MySQL database. You may choose to determine the organization of your own database or use the provided schema and SQL queries.
  6. Create any other classes that you need to help with your application. For example, you may wish to implement a Database wrapper class or incorporate the Database class from our in-class Trivia Game.
  7. Session tracking
    • The user should not be able to access any other views except the login page unless they have logged in.
    • You must keep track of the current session with $_SESSION. When the user logs out, destroy the current session and display the login page again.

Note: We have explicitly left some requirements of the finance tracker vague to provide freedom of design. You are encouraged to be creative in your implementations!

PostgresSQL examples

The following (incomplete) SQL examples may be helpful as a model when creating your database and displaying the transaction history to the user. This is only an example. You should write a setup script that creates the necessary tables for your schema.

Create some tables:

create sequence hw5user_seq;
create sequence hw5transaction_seq;

create table hw5_user (
    id int primary key default nextval('hw5user_seq'),
    email text not null
);

-- Note: double hyphens are comments in SQL
create table hw5_transaction (
    id int primary key default nextval('hw5transaction_seq'),
    user_id int not null, -- the user id who inserted this transaction
    category text not null,
    t_date date not null, -- date is a reserved word
    amount decimal(10,2) not null -- two decimal places
);

Return the transaction list for a particular user (given by $1) in descending order by date:

select * from hw5_transaction where user_id = $1 order by t_date desc;

Return the current balance for a particular user (given by $1):

select sum(amount) as balance from hw5_transaction where user_id = $1;

Return the balance for all categories (does not take into account users):

select category, sum(amount) as balance from hw5_transaction group by category;

Note: You are encouraged to use prefixes (i.e., “hw5_”) for your tables for this homework so that they do not conflict with other homework assignments, in-class examples, or your sprint.