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

import java.sql.*;

public class Main
{

  public static void main(String[] args)
  {
    Connection con = null;
    int iRowCot = 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

    // Prints line "Table updated!"
    System.out.println("Table updated!");

    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

    try
    {
    // Adds five to the number of cups in the database
    state.execute("UPDATE JJJJData SET Cups = Cups +5");
    }
      catch(Exception e)
      {
      System.out.println("problem with SQL trying to update the table");
      } // End catch

    try
    {
    // Orders database so cups are in decending order
    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());
      } // End catch

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

    // Prints total sales
    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

