import java.util.ArrayList;
import java.sql.*;

public class JDBCPractice
{
    private String selectStatement, reportStatement, updateStatement;
    private DatabaseAccess da;
    private ResultSet rs;

    private String driver = "sun.jdbc.odbc.JdbcOdbcDriver";
    private String jdbc = "jdbc:odbc:JDBC4600";
    private String username = "4600";
    private String password = "student";

    private int todaysDate;
    double price;
    int QOH;
    String productName;
    int vendorID;
    int reorderLevel;

    /**
     * Constructor just sets the date.
     */
    public JDBCPractice()
    {
        todaysDate = 20081126;
    }


    /**
     * Return the Status Report for one Customer. Includes CustomerID, CustomerName,
     * LateFees and a List of Movies Currently Rented to that Customer.
     *
     */
    public void customerStatusReport(int custID)
    {
        System.out.println();
        System.out.println("Customer Status Report");


        /** Connect to the database
         */
        DatabaseAccess da = new DatabaseAccess();
        da.loadDriver(driver);
        da.connectDatabase(jdbc,username,password);

        /** Write the SQL, execute it, and print contents of resultSet
         */
        selectStatement = "Select CustomerID, CustomerName, LateFees FROM Customers where CustomerID = " + custID;
        rs = da.executeSQL(selectStatement);

        try
        {
            while(rs.next())
            {
                int custName = rs.getInt(2);
                double lateFees = rs.getDouble(3);
                System.out.println("Customer ID:  " + custID + "  Customer Name:  " + custName);
                System.out.println("Outstanding Late Fees:  " + lateFees );
            }
        }
        catch(SQLException error)
        {
            System.err.println("Error Accessing Customer Table: " + error.toString());
            System.exit(4);
        }


           System.out.println();
           System.out.println("Movies Currently Checked Out");
           System.out.println("ID    Movie Name            Due Date  Overdue?   ");



        /** Write the SQL, execute it, and print contents of resultSet
        */
        //selectStatement = "Select MovieID, MovieName, DueDate, RentedToID from Movie where RentedToID = " + custID;
           selectStatement = "Select * from Movie where RentedToID = " + custID;
           rs = da.executeSQL(selectStatement);



        da.closeDatabase();
    }

     /**
     * List all Products in the Database
     */
    public void listAllProducts()
    {
        System.out.println();
        System.out.println("List Of All Products");
        System.out.println("ID    Description            Price    QOH  ReorderLevel  etc.");

        /** Connect to the database
         */
        DatabaseAccess da = new DatabaseAccess();
        da.loadDriver(driver);
        da.connectDatabase(jdbc,username,password);

        /** Write the SQL, execute it, and print contents of resultSet
         */
        selectStatement = "Select * from Inventory";
        rs = da.executeSQL(selectStatement);
        da.reportSQL(rs);
        da.closeDatabase();
    }

    public void listAllMovies()
    {
        System.out.println();
        System.out.println("List Of All Movies");
        System.out.println("ID    Title                 Due Date  RenterID  Price  ");

        /** Connect to the database
         */
         DatabaseAccess test = new DatabaseAccess();
        test.loadDriver(driver);
        test.connectDatabase(jdbc,username,password);

        selectStatement = "SELECT * FROM MOVIE";
        rs = test.executeSQL(selectStatement);
        test.reportSQL(rs);
        test.closeDatabase();
    }

    public void listAllFieldsForMoviesForOneCustomer(int custID)
    {
        System.out.println();
        System.out.println("List Of All Movie Fields for one Customer");
        System.out.println("ID    Movie Name            Due Date  etc.   ");

        /** Connect to the database
         */
        DatabaseAccess da = new DatabaseAccess();
        da.loadDriver(driver);
        da.connectDatabase(jdbc,username,password);

        /** Write the SQL, execute it, and print contents of resultSet
         */
        //selectStatement = "Select MovieID, MovieName, DueDate, RentedToID from Movie where RentedToID = " + custID;
       selectStatement = "Select * from Movie where RentedToID = " + custID;
        rs = da.executeSQL(selectStatement);

        da.reportSQL(rs);
        da.closeDatabase();
    }


    public void listMovieNamesDueDatesForOneCustomer(int custID)
    {
        System.out.println();
        System.out.println("List Of All Movie Names and DueDates for one Customer");


        /** Connect to the database
         */
        DatabaseAccess da = new DatabaseAccess();
        da.loadDriver(driver);
        da.connectDatabase(jdbc,username,password);

        /** Write the SQL, execute it, and print contents of resultSet
         */
        selectStatement = "Select MovieID, MovieName, DueDate from Movie where RentedToID = " + custID;
        rs = da.executeSQL(selectStatement);

        try
        {
            while(rs.next())
            {
                String movieName = rs.getString(2);
                int dueDate = rs.getInt(3);
                System.out.println("Movie Name:  " + movieName + "  DueDate:  " + dueDate);
            }
        }
        catch(SQLException error)
        {
            System.err.println("Error Accessing Movie Table: " + error.toString());
            System.exit(4);
        }

        da.closeDatabase();
    }
    }