Untitled

import javax.swing.JFrame;
import java.sql.*;
import java.util.Calendar;

public class Bookstore {

    private Connection conDB;
    private String url;
    private JFrame frame;

    /**
     * Open the connections and calls the GUI to be constructed.
     * Closes the connection using the closeConnection method
     * upon clicking the close button using the GUI.
     */
    public Bookstore() throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException {
        url = "jdbc:db2:c3421m";

        //has 3 exceptions
        try {
            Class.forName("com.ibm.db2.jcc.DB2Driver").newInstance();
        } catch (Exception e) {
            System.exit(0);
        }

        //has 1 exception
        try {
            conDB = DriverManager.getConnection(url);
        } catch (Exception e) {
            System.exit(0);
        }

        BookstoreBuilder b = new BookstoreBuilder(frame);
        b.createGui(frame, this);
   }

    /**
     * This method is used in the GUI when the GUI override
     * the close operation of the problem to do nothing.
     * This allows us to manually close the window and the program
     * which gives us the benefit of closing the connection right before
     * we close the window.
     */
    public void closeConnection() {
        try {
            conDB.commit();
        } catch (Exception e) {
            System.exit(0);
        }
        try {
            conDB.close();
        } catch (Exception e) {
            System.exit(0);
        }
    }

    /**
     * This method simply finds the customer's name and
     * is used to determine whether a customer exists or not.
     * @param id as an int of the customer's ID retrieved from the GUI.
     * @return true if the customer's name is found. False, otherwise.
     * @GUI Makes the cid input field to be uneditable if true
     * until the user selects the 'Reset' button or the transaction
     * is made. If false, the gui outputs the customer
     * to retry. Affiliated with the 'Find' button.
     */
    public boolean find_customer(int id) throws SQLException {
        String query = "";
        PreparedStatement statement = null;
        ResultSet answer = null;
        boolean result = false;

        query = "SELECT name FROM yrb_customer WHERE cid = ?";


        try {
            statement = conDB.prepareStatement(query);
        } catch (SQLException e) {
            System.exit(0);
        }

        try {
            statement.setInt(1, id);    // I DON'T KNOW WHAT THIS DOES AND I HATE IT
            answer = statement.executeQuery();
        } catch (SQLException e) {

            System.exit(0);
        }

        try {
            result = answer.next();
        } catch (SQLException e) {

            System.exit(0);
        }

        try {
            answer.close();
        } catch(SQLException e) {

            System.exit(0);
        }

        // We're done with the handle.
        try {
            statement.close();
        } catch(SQLException e) {

            System.exit(0);
        }

        if (!result) {
            System.out.println("Not found!");
        } else {

        }

        return result;
    }

    /**
     * This method retrieves the information of a customer
     * (their name, cid, and city) and is requested only if the method
     * find_customer() returns true.
     * @param id is an int of the customer's ID retrieved from the GUI.
     * @return a String of the customers information.
     * @GUI outputs the customer's information in the first text area of the
     * GUI. Affiliated with the 'Find' button.
     */
    public String find_customerInfo(int id) {
        String query = "";
        PreparedStatement statement = null;
        ResultSet answer = null;
        StringBuffer result = new StringBuffer("Customer: ");

        query = "SELECT * FROM yrb_customer WHERE cid = ?";

        try {
            statement = conDB.prepareStatement(query);
        } catch (SQLException e) {
            System.out.println(query);
            System.exit(0);
        }


        try {
            statement.setInt(1, id);    // I DON'T KNOW WHAT THIS DOES AND I HATE IT
            answer = statement.executeQuery();
        } catch (SQLException e) {
            System.exit(0);
        }

        try {
            answer.next();
            result.append(answer.getString("cid"));
            result.append(", ");
            result.append(answer.getString("name"));
            result.append(", ");
            result.append(answer.getString("city"));
            result.append(".");
        } catch (SQLException e) {
            System.exit(0);
        }

        try {
            answer.close();
        } catch(SQLException e) {
            System.exit(0);
        }

        // We're done with the handle.
        try {
            statement.close();
        } catch(SQLException e) {
            System.exit(0);
        }

        this.fetch_categories();    //REMOVE TESTER

        return result.toString();
    }

    /**
     * This method freshly returns all the categories from the database
     * at the current moment and puts them out for the user to choose.
     * @return an array of String each containing the name of a category.
     * @GUI modifies the empty dropdown list for categories and adds all
     * the categories to it. Affiliated with the 'Find' button.
     */
    public String[] fetch_categories() {
        String query = "";
        PreparedStatement statement = null;
        ResultSet answer = null;
        String[] result = new String[this.category_Count()];

        query = "SELECT * FROM yrb_category";

        try {
            statement = conDB.prepareStatement(query);
        } catch (SQLException e) {
            System.out.println(e);
            System.exit(0);
        }


        try {
            answer = statement.executeQuery();
        } catch (SQLException e) {
            System.out.println(e);
            System.exit(0);
        }

        try {
            answer.next();
            int i = 0;
            while (i < this.category_Count()) {
                result[i] = answer.getString("cat");
                i++;
                answer.next();
            }
        } catch (SQLException e) {
            System.out.println(e);
            System.exit(0);
        }

        try {
            answer.close();
        } catch(SQLException e) {
            System.out.println("1");
            System.exit(0);
        }

        // We're done with the handle.
        try {
            statement.close();
        } catch(SQLException e) {
            System.out.println("1");
            System.exit(0);
        }

        return result;
    }

    /**
     * This method is a helper method for fetch_categories() to
     * determine how many categories there are to initalize the array
     * of Strings (category names).
     * @return the number of categories in the yrb_category database.
     */
    private int category_Count() {
        String query = "";
        PreparedStatement statement = null;
        ResultSet answer = null;
        int result = 0;

        query = "SELECT COUNT(*) AS count FROM yrb_category";

        try {
            statement = conDB.prepareStatement(query);
        } catch (SQLException e) {
            System.out.println(query);
            System.exit(0);
        }

        try {
            answer = statement.executeQuery();
        } catch (SQLException e) {
            System.exit(0);
        }

        try {
            answer.next();
            result = Integer.parseInt(answer.getString("count"));
        } catch (SQLException e) {
            System.exit(0);
        }

        try {
            answer.close();
        } catch(SQLException e) {
            System.exit(0);
        }

        // We're done with the handle.
        try {
            statement.close();
        } catch(SQLException e) {
            System.exit(0);
        }

        return result;

    }

    /**
     * Upon selection of a category, the user may enter a book title and
     * search for a book. This method returns a String with the title, year,
     * language and weight of a book (separated by '.' in a StringBuffer).
     * This method ONLY returns books that match the category and title
     * entered by the user AND ALSO makes sure that the customer is a
     * member of the club that offers the book in the output.
     * @param title is a String from the title text input of the GUI.
     * @param category is a String from the category dropdown menu of the GUI.
     * @param cid is an int from the CID text input of the GUI.
     * @return a string of the book containing all the information in the
     * yrb_book table.
     */
    public String find_book(String title, String category, int cid) {
        String query = "";
        PreparedStatement statement = null;
        ResultSet answer = null;
        //String[] result = new String[this.book_count(title)];
        StringBuffer result = new StringBuffer();

        query = "SELECT B.title, B.year, B.language, B.weight FROM yrb_offer O, yrb_member M, yrb_book B WHERE B.title = ? AND B.cat = ?"
                + " AND M.cid = ? AND M.club = O.club GROUP BY B.title, B.year, B.weight, B.language";

        try {
            statement = conDB.prepareStatement(query);
        } catch (SQLException e) {
            System.out.println(e);
            System.exit(0);
        }

        try {
            statement.setString(1, title);
            statement.setString(2, category);
            statement.setInt(3, cid);
            answer = statement.executeQuery();
        } catch (SQLException e) {
            System.out.println(e);
            System.exit(0);
        }

        try {
            answer.next();
            int i = 0;
            if (this.book_count(title, category, cid) == 0) {
                result.delete(0, result.length());
                result.append("");
            } else {
                while (i < this.book_count(title, category, cid)) {
                    result.append(answer.getString("title") + ". " + answer.getString("year")
                    + ". " + answer.getString("language") + ". " + answer.getString("weight")
                    + "." + "\n");
                    i++;
                    answer.next();
                }
            }
        } catch (SQLException e) {
            System.out.println(e);
            System.exit(0);
        }

        try {
            answer.close();
        } catch(SQLException e) {
            System.exit(0);
        }

        // We're done with the handle.
        try {
            statement.close();
        } catch(SQLException e) {
            System.exit(0);
        }

        return result.toString();

    }

    /**
     * This is a helper method for the dropdown menu of books in the GUI.
     * Allows the gui to know how many options to have in the menu.
     * @param title is a String that is extracted from the title text
     * input from the GUI.
     * @param category is a string that is retrieved from the dropdown
     * category menu.
     * @return a String from a StringBuffer where the Strings are
     * seperated by '\n' (the new line character)
     * @GUI splits the String into an array using the split method
     * on the new line character symbol to put them in the dropdown
     * menu of books found. After this button is pressed the dropdown
     * menu becomes uneditable until the user selects the 'Reset' button
     * or a transaction is made.
     */
    private int book_count(String title, String category, int cid) {
        String query = "";
        PreparedStatement statement = null;
        ResultSet answer = null;
        int result = 0;

        query = "SELECT COUNT(*) AS COUNT FROM (SELECT B.title, B.year, B.weight, B.language AS count FROM yrb_offer O, yrb_member M, yrb_book B WHERE B.title = ? AND B.cat = ?"
                + " AND M.cid = ? AND M.club = O.club GROUP BY B.title, B.year, B.weight, B.language)";

        try {
            statement = conDB.prepareStatement(query);
        } catch (SQLException e) {
            System.out.println(query);
            System.exit(0);
        }

        try {
            statement.setString(1, title);
            statement.setString(2, category);
            statement.setInt(3, cid);
            answer = statement.executeQuery();
        } catch (SQLException e) {
            System.exit(0);
        }

        try {
            answer.next();
            result = Integer.parseInt(answer.getString("count"));
        } catch (SQLException e) {
            System.exit(0);
        }

        try {
            answer.close();
        } catch(SQLException e) {
            System.exit(0);
        }

        // We're done with the handle.
        try {
            statement.close();
        } catch(SQLException e) {
            System.exit(0);
        }

        return result;

    }

    /**
     * This method returns the minimum price of the book with the
     * year and title extracted from the dropdown menu ALONG with
     * making sure that the minimum price recieved is from a club
     * that the customer with the given CID is a member of.
     * @param title is a String that is extracted from the GUI and
     * is the title of the book.
     * @param year is an int that is extracted from the GUI and
     * is the year of the book.
     * @param cid is an int taht is extracted from the GUI and
     * is the CID of the customer.
     * @return a double with the minimum price of the book.
     * @GUI outputs the minimum price and makes the book dropdown
     * menu uneditable until the 'Reset' button is clicked or
     * the transaction is completed.
     */
    public double min_price(String title, int year, int cid) {
        String query = "";
        PreparedStatement statement = null;
        ResultSet answer = null;
        double result = 0.0;

        query = "SELECT min(price) AS price FROM yrb_offer, yrb_member "
                + "WHERE yrb_offer.title = ? AND yrb_offer.year = ? AND yrb_offer.club = yrb_member.club"
                + " AND yrb_member.cid = ?";

        try {
            statement = conDB.prepareStatement(query);
        } catch (SQLException e) {
            System.out.println(e);
            System.exit(0);
        }

        try {
            statement.setString(1, title);
            statement.setInt(2, year);
            statement.setInt(3, cid);
            answer = statement.executeQuery();
        } catch (SQLException e) {
            System.out.println(e);
            System.exit(0);

        }
        try {
            answer.next();
            result = answer.getDouble("price");

        } catch (SQLException e) {
            System.out.println(e);
            System.exit(0);
        }

        try {
            answer.close();
        } catch(SQLException e) {
            System.out.println(e);
            System.exit(0);
        }

        // We're done with the handle.
        try {
            statement.close();
        } catch(SQLException e) {
            System.out.println(e);
            System.exit(0);
        }

        return result;

    }

    /**
     * This method is responsible for the insertion of a tuple
     * into the database (specifically the yrb_database) table.
     * It takes in the CID, club name, title of the book, year
     * of the book and the quantity the user wants to buy. The
     * timestamp is based of Java's date at the current moment
     * and then converted into the Database's timestamp type.
     * @param customerCid is an int and the CID of the customer
     * in the CID input field.
     * @param clubName is a String and the name of the club
     * that is retrieved from a method called find_club()
     * and it is just a helper method that: makes sure the customer
     * is a member of the club dealt with and the club offers the book
     * the customer wants to buy.
     * @param title is a String and the name of the book the customer
     * wants to purchase. It is retrieved from the GUI dropdown menu.
     * @param year is an int and it is the year of the book being
     * purchased. It is retrieved from the GUI dropdown menu.
     * @param qnty is an int and it the quantity the user
     * wants to purchase. It is retrieved from the quantity input.
     * @GUI this method is affiliated with the 'Approve' button
     * in the GUI. After this button is pressed, it will let the user
     * know if the transaction went through or not. After approval,
     * the fields are reset and the user can start from scratch.
     */
    public void insert_purchase(int customerCid, String clubName, String title, int year, int qnty) {
        String query = "";
        PreparedStatement statement = null;

        query = "INSERT INTO yrb_purchase VALUES (?, ?, ?, ?, ?, ?)";

        try {
            statement = conDB.prepareStatement(query);
        } catch (SQLException e) {
            System.out.println(e);
            System.exit(0);
        }

        try {
            Calendar c = Calendar.getInstance();
            java.util.Date now = c.getTime();
            java.sql.Timestamp currentTimestamp = new java.sql.Timestamp(now.getTime());
            statement.setInt(1, customerCid);
            statement.setString(2, clubName);
            statement.setString(3, title);
            statement.setInt(4, year);
            statement.setTimestamp(5, currentTimestamp);
            statement.setInt(6, qnty);

            statement.execute();
        } catch (SQLException e) {
            System.out.println("2" + e);
            System.exit(0);

        }

        // We're done with the handle.
        try {
            statement.close();
        } catch(SQLException e) {
            System.exit(0);
        }

    }

    public static void main(String[] args) throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException {
        Bookstore p = new Bookstore();
    }

    /**
     * This is a helper method for the insert_purchase method as it
     * finds the club that the book is to be purchased from, as a String
     * in order to insert the correct information into the database.
     * @param title is a String and is the title of the book.
     * It is retrieved from the book input from the GUI.
     * @param year is an int and is the year that identifies
     * the edition of the book. Is is retrieved from the dropdown
     * menu selected String in the GUI.
     * @param cid is an int and is the customer CID. It is retrieved
     * from the CID input in the GUI.
     * @return the name of the club as a String.
     * @GUI is affiliated with the 'Approve' button.
     */
    public String find_club(String title, int year, int cid, double price) {
        String query = "";
        PreparedStatement statement = null;
        ResultSet answer = null;
        String result = "";

        query = "SELECT O.club as club FROM yrb_offer O"
                + " WHERE O.title = ? AND O.year = ? AND O.price = ? AND club IN (SELECT club FROM yrb_member WHERE cid = ?)";

        try {
            statement = conDB.prepareStatement(query);
        } catch (SQLException e) {
            System.out.println("1");
            System.out.println(e);
            System.exit(0);
        }

        try {
            statement.setString(1, title);
            statement.setInt(2, year);
            statement.setDouble(3, price);
            statement.setInt(4, cid);
            answer = statement.executeQuery();
        } catch (SQLException e) {
            System.out.println("2");
            System.out.println(e);
            System.exit(0);
        }

        try {
            answer.next();
            result = answer.getString("club");
        } catch (SQLException e) {
            System.out.println("3");
            System.out.println(e);
            System.exit(0);
        }

        try {
            answer.close();
        } catch(SQLException e) {
            System.exit(0);
        }

        // We're done with the handle.
        try {
            statement.close();
        } catch(SQLException e) {
            System.exit(0);
        }

        return result;
    }

    /**
     * This method returns a boolean that is true if the cid entered
     * by the user exists in the database and false if it does not.
     * It checks this by running a query and determining whether
     * an output is returned (a tuple) or not.
     * @param cid is an int and the customer CID that is retrieved
     * from the GUI.
     * @return a boolean whether or not the customer exists.
     */
    public boolean cid_exists(int cid) {
        String query = "";
        PreparedStatement statement = null;
        ResultSet answer = null;
        boolean result = false;
        query = "SELECT name FROM yrb_customer WHERE cid = ?";

        try {
            statement = conDB.prepareStatement(query);
        } catch (SQLException e) {
            System.out.println(e);
            System.exit(0);
        }


        try {
            statement.setInt(1, cid);
            answer = statement.executeQuery();
        } catch (SQLException e) {
            System.out.println(e);
            System.exit(0);
        }

        try {
            if (answer.next()) {
                result = true;
            }
        } catch (SQLException e) {
            System.out.println(e);
            System.exit(0);
        }

        try {
            answer.close();
        } catch(SQLException e) {
            System.out.println("1");
            System.exit(0);
        }

        // We're done with the handle.
        try {
            statement.close();
        } catch(SQLException e) {
            System.out.println("1");
            System.exit(0);
        }
        return result;
    }

    /**
     * This method makes sure a book exists to let the GUI know
     * whether to let the customer know that there are no books
     * found.
     * @param title is a String and is the title of the book.
     * @param category is a String and is the category of the book.
     * @param cid is an int and it is the CID of a customer.
     * @return a boolean whether or not the book exists.
     */
    public boolean book_exists(String title, String category, int cid) {
        String query = "";
        PreparedStatement statement = null;
        ResultSet answer = null;
        boolean result = false;
        //String[] result = new String[this.book_count(title)];

        query = "SELECT B.title, B.year, B.language, B.weight FROM yrb_offer O, yrb_member M, yrb_book B WHERE B.title = ? AND B.cat = ?"
                + " AND M.cid = ? AND M.club = O.club GROUP BY B.title, B.year, B.weight, B.language";

        try {
            statement = conDB.prepareStatement(query);
        } catch (SQLException e) {
            System.out.println(e);
            System.exit(0);
        }

        try {
            statement.setString(1, title);
            statement.setString(2, category);
            statement.setInt(3, cid);
            answer = statement.executeQuery();
        } catch (SQLException e) {
            System.out.println(e);
            System.exit(0);
        }

        try {
            if (answer.next()) {
                result = true;
            }
        } catch (SQLException e) {
            System.out.println(e);
            System.exit(0);
        }

        try {
            answer.close();
        } catch(SQLException e) {
            System.exit(0);
        }

        // We're done with the handle.
        try {
            statement.close();
        } catch(SQLException e) {
            System.exit(0);
        }

        return result;

    }
}