// Robert Irwin
// mcaiyri3
// Main.java Exercise 4.2

import java.sql.*;

public class Main
{
  public static void main(String[] args)
  {
    Connection con = null;
    int iRowCt = 0;
    Statement state = null;
    ResultSet rs;

    String sURL = "jdbc:odbc:repository";

    try // Try to load the JDBC sun driver
    {
    Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
    }
      catch( Exception e )
      {
      System.out.println( "Failed to load current driver.");
      return;
      } // End catch

    try
    {
    con = DriverManager.getConnection (sURL);
    state = con.createStatement();
    }
      catch ( Exception e)
      {
      System.out.println( "problems connecting to " + sURL + ":" );
      System.out.println( e.getMessage() );

      if( con != null)
        {
        try { con.close(); }
        catch( Exception e2 ) {}
        }
      return;
      } // End catch

    // Orders the database by cups field in descending order
    try{
    String query = "SELECT * FROM JJJJData ORDER BY Cups desc";
    state = con.createStatement();
    rs = state.executeQuery(query);

      // For loop
      for (int j = 0; j < 15; j++)
      {
      rs.next();
      System.out.println(rs.getInt(1) + "\t" + rs.getString(2)  + "\t" + rs.getString(3) + "\t" +
      rs.getString(4)  + "\t" + rs.getString(5));
      } // End for loop
    }
      catch(Exception e)
      {
      System.out.println("problem with SQL sent to jdbc:odbc:repository: \n" + e.getMessage());
      System.exit(1);}

    try{
    String query = "SELECT SUM(Cups) FROM JJJJData";
    state = con.createStatement();
    rs = state.executeQuery(query);
    rs.next();
    int abc = rs.getInt(1);

    System.out.println("\nCafe Total Weekly Sales " + abc + " cups of coffee");
    }
    catch(Exception e)
    {
    System.out.println("problem with SQL sent to jdbc:odbc:repository: \n" + e.getMessage());
    } // End catch

      finally {
        try { state.close(); }
        catch( Exception e ) {}
        try { con.close(); }
        catch( Exception e ) {} } // End finally clause

  } // End main
} // End class

