SignUpSQL.java

import java.sql.*;
import java.sql.Date;
import java.text.ParseException;
import java.sql.SQLException;

public class SignUpSQL {
    //This connects to the database by calling the login file 
    static Connection connection = Login.connection;
    static Statement statement = Login.statement;
    static ResultSet result = Login.result;

    /**
     * Method is called in SignUp file where user wants to see all the SignUps in the database. This method
     * will perform the sql statement and generate a result set of all the SignUps in the database.
     * @return ResultSet
     * @throws ClassNotFoundException
     * @throws SQLException
     * @throws ParseException
     */
    public static ResultSet viewAllSignUps() throws ClassNotFoundException, SQLException, ParseException{
        //A table of data representing a database result set, which is usually generated by executing a 
        //statement that queries the database.
        ResultSet returnSet = null;
        //Object that represents a precompiled SQL statement
        PreparedStatement ps = null;

        try {
            ps = connection.prepareStatement("SELECT * FROM SignUp;");
            returnSet = ps.executeQuery();
            ps.close();
        } catch (SQLException e) {
            System.out.println("SQL Exception");
            e.printStackTrace();
            return null;
        }
        return returnSet;
    }

    /**
     * Method is called in the SignUp file and is used to find a specific SignUp.  
     * @param memberID
     * @return ResultSet
     * @throws ClassNotFoundException
     * @throws SQLException
     * @throws ParseException
     */
    public static ResultSet viewSignUp(int memberID) throws ClassNotFoundException, SQLException, ParseException{
        //A table of data representing a database result set, which is usually generated by executing a 
        //statement that queries the database.
        ResultSet returnSet = null;
        //Object that represents a precompiled SQL statement
        PreparedStatement ps = null;

        try {
            ps = connection.prepareStatement("SELECT * FROM SignUp WHERE memberID = ?;");
            ps.setInt(1, memberID);
            returnSet = ps.executeQuery();
            ps.close();
        } catch (SQLException e) {
            System.out.println("SQL Exception");
            e.getStackTrace();
            return null;
        }
        return returnSet;
    }

    /**
     * Method is called from SignUp file and is called after the user has finished filling all the attributes for a SignUp
     * @param storeID
     * @param signUpDate
     * @param staffID
     * @param memberID
     * @throws SQLException
     * @throws ParseException
     */
    static void addSignUp(int storeID, Date signUpDate, int staffID, int memberID) throws SQLException, ParseException{
        //Object that represents a precompiled SQL statement
        PreparedStatement ps = null;
        int id = 0;
        try{
            ps = connection.prepareStatement("INSERT INTO SignUp (storeID, signUpDate, staffID, memberID) VALUES (?,?,?,?);");
            ps.setInt(1,storeID);
            ps.setDate(2,signUpDate);
            ps.setInt(3,staffID);
            ps.setInt(4,memberID);
            
            id = ps.executeUpdate();
            connection.commit();
            ps.close();
            System.out.println(id);
            
            if(id > 0){
                System.out.println("SignUp added successfully");
            } else{
                System.out.println("SignUp not added");
            }
        }
        catch (SQLException e) {
            System.out.println("SQL Exception");
            connection.rollback();
            e.printStackTrace();
        }
    }

    /**
     * Method is called from SignUp file and is called after the user has finished filling all the attributes that they want to edit 
     * for a SignUp
     * @param storeID
     * @param signUpDate
     * @param staffID
     * @param memberID
     * @throws SQLException
     * @throws ParseException
     */
    static void editSignUp(int storeID, Date signUpDate, int staffID, int memberID) throws SQLException, ParseException{
        //Object that represents a precompiled SQL statement
        PreparedStatement ps = null;
        int id = 0;
        try{
            ps = connection.prepareStatement("UPDATE SignUp SET storeID = ?, signUpDate = ?, staffID = ? WHERE memberID = ?;");
            
            ps.setInt(1,storeID);
            ps.setDate(2,signUpDate);
            ps.setInt(3,staffID);
            ps.setInt(4,memberID);

            id = ps.executeUpdate();
            connection.commit();
            ps.close();
            System.out.println(id);

            
            if(id > 0){
                System.out.println("SignUp updated successfully");
            } else{
                System.out.println("SignUp not updated");
            }
        }
        catch (SQLException e) {
            System.out.println("SQL Exception");
            connection.rollback();
            e.printStackTrace();
        }
    }

    /**
     * Method is called from SignUp file and is called to delete a SignUp from the database
     * @param memberID
     */
    static void deleteSignUp(int memberID){
        try {
            PreparedStatement ps = connection.prepareStatement("DELETE FROM SignUp WHERE memberID = ?;");
            ps.setInt(1, memberID);
            int id = ps.executeUpdate();

            System.out.println(id);

            if (id > 0) {
                System.out.println("SignUp deleted");
            } else {
                System.out.println("SignUp not deleted");
            }
        } catch (SQLException e) {
            System.out.println("SQL Exception");
            e.printStackTrace();
        }
    }

    public static ResultSet growthReport(boolean isStore, int storeID, String start, String end) throws ClassNotFoundException, SQLException, ParseException
    {
        ResultSet returnSet = null;
        PreparedStatement ps = null;

        try {
            if (isStore) {
                ps = connection.prepareStatement("SELECT COUNT(memberID) FROM SignUp WHERE storeID = ? AND signUpDate BETWEEN ? AND ?;");
                ps.setInt(1, storeID);
                ps.setString(2, start);
                ps.setString(3, end);
            } else {
                ps = connection.prepareStatement("SELECT COUNT(memberID) FROM SignUp WHERE signUpDate BETWEEN ? AND ?;");
                ps.setString(1, start);
                ps.setString(2, end);
            }
            returnSet = ps.executeQuery();
            ps.close();
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
        return returnSet;
    }
}