Sales Report using Java and MySQL

SalesReport.java

/********************************************************************************************************************
  
   Author: Justin Tew
   Class: CSCD 327 
   Professor: Chris Peters
   Date: 3/11/2015
   
   Extra Credit Attempted (and Completed!)
   
   All code written by myself with the exception of the getConnection method, which was written by Dr. Dan Li
   
 

********************************************************************************************************************/

import java.io.*;
import java.sql.*;


public class SalesReport {

   public static void main(String[] args) {
      try {
      
                   
         Connection c = getConnection();   
         Statement statement = c.createStatement();
         Statement s2 = c.createStatement();
         Statement s3 = c.createStatement();
         Statement s4 = c.createStatement();
         ResultSet resultSet = null;
         ResultSet r2 = null;
         ResultSet r3 = null;
         ResultSet r4 = null;
         
         
         
         printAsciiArt();
         
         //part 1
         int TotalOrders = 0;
         float TotalSales = 0;
         int TotalItems = 0;
         float TotalWeight = 0;
         float TotalProfit = 0;
         float AvgOrderAmt = 0;
         float AvgProfit = 0;
         
         
         
         String query3  = "select * from customer natural join invoice;";
         r3 = s3.executeQuery(query3);
         while (r3.next()) {
         
            TotalOrders++;
         
            int orderNum = r3.getInt(7); 
         
         
            String q4 = "SELECT * FROM invoicelineitem NATURAL JOIN inventory WHERE OrderNum =" + orderNum + ";";
            r4 = s4.executeQuery(q4);
            
            
            
            float otot = 0;
            float totp = 0;
            
            
            while (r4.next()){
            
            
            
               TotalItems++;
               
               
               int quantity = r4.getInt(4); 
               float price = r4.getFloat(9);
               float extprice = 0;
               float weight = r4.getFloat(10);
               float profit = 0;
               float cost = r4.getFloat(8);
               
               profit = quantity *(price - cost);
               
               TotalWeight += weight;
               extprice = price * quantity;
               otot += extprice;
               totp += profit;
            
            
               
            
            }//end while loop
            
            TotalSales += otot;
            TotalProfit += totp;
            
            
            
            
         }//end loop
         
         AvgOrderAmt = TotalSales / TotalOrders;
         AvgProfit = TotalProfit / TotalOrders;
         String AvgOrderAmtF = String.format("$%.2f", AvgOrderAmt);
         String AvgProfitF = String.format("$%.2f", AvgProfit);
         
         
         String TotalSalesF = String.format("$%.2f", TotalSales);
         String TotalWeightF = String.format("%.2f", TotalWeight);
         String TotalProfitF = String.format("$%.2f", TotalProfit);
      
            
         System.out.println("Total Orders:      Total Sales:         Total Items:        Total Weight:       Total Profit:       Average Order Amount:      Average Profit:");
         System.out.println("     "+TotalOrders+"               " + TotalSalesF +"                "+TotalItems+"                  " +TotalWeightF+ "              "+TotalProfitF+"                "+AvgOrderAmtF+"                 "+AvgProfitF);
         System.out.println();
         System.out.println();
         
      
      
      
      
      
      
      
         //part 2
         
         //get all the orders and the customer info associated with each order
         System.out.println("______________________________________________________________________________________________________________________________________________");
         System.out.println("List of all Orders:");
         System.out.println("______________________________________________________________________________________________________________________________________________");
         String query  = "select * from customer natural join invoice;";
         resultSet = statement.executeQuery(query);
      
         while (resultSet.next()) {
            String street = resultSet.getString(3);
            String city = resultSet.getString(4);
            String state = resultSet.getString(5);
            String zip = resultSet.getString(6); 
            int orderNum = resultSet.getInt(7); 
            String custNum = resultSet.getString(1);
            String date = resultSet.getString(8);
            String custName = resultSet.getString(2);
            System.out.printf("Customer#: %-3s - %-17s\t\t", custNum, custName);
            System.out.printf("Order#: %-5s\t\t", orderNum);
            System.out.printf("   Date: %-20s\n\n", date);
            System.out.println(street);
            System.out.println(city + " " + state + " " + zip);
            System.out.println();
            
            //get all the items from that order
            String q2 = "SELECT * FROM invoicelineitem NATURAL JOIN inventory WHERE OrderNum =" + orderNum + ";";
         
            r2 = s2.executeQuery(q2);
            float otot = 0;
            String ototf = null;
            float totp = 0;
            System.out.println();
            System.out.println("SKU    DESCRIPTION                      PRICE        QUANTITY    WEIGHT     EXT. PRICE     EXT. WT.     PROFIT     ORDER PROFIT    ORDER TOTAL");
            while (r2.next()){
               String sku = r2.getString(1);
               String linenum = r2.getString(3);
               int quantity = r2.getInt(4); 
               float cost = r2.getFloat(8);
               float price = r2.getFloat(9);
               float weight = r2.getFloat(10);
               String desc = r2.getString(6);
               float extprice = 0;
               float extw = 0;
               float profit = 0;
               
               profit = quantity *(price - cost);
               totp += profit;
               extprice = price * quantity;
               extw = weight * quantity;
               String extp2 = String.format("$%.2f", extprice);
               String pricef = String.format("$%.2f", price);
               otot += extprice;
               ototf = String.format("$%.2f", otot);
               String extw2 = String.format("%.2f",extw);
               String profitf = String.format("$%.2f", profit);
               
               System.out.printf("%-4s   ", sku);
               System.out.printf("%-22s      ", desc);
               System.out.printf("%10s\t", pricef);
               System.out.print("     " + quantity +"\t");
               System.out.print("         " + weight);
               System.out.printf("\t    %10s", extp2);
               System.out.printf("        %5s", extw2);
               System.out.printf("%11s", profitf);
               System.out.println();
            }
            String totpf = String.format("$%2s", totp);
            System.out.println("                                                                                                                       " +  totpf +  "        " + ototf);    
            System.out.println("______________________________________________________________________________________________________________________________________________");
         }
         
         
      
         
         c.close();
      }
      
      
      
      //errors & exceptions
      catch (Exception e) {
         System.out.println("crap! something went wrong...");
         System.out.println(e.getMessage());
      }
   
   }
   
   public static void printAsciiArt(){
                         
         /*-------------------------------------------------------------------------
          Some ASCII ART to make sales report large and easy to read
          Credit to http://patorjk.com/software/taag/ for the ASCII ART generator
          ---------------------------------------------------------------------------*/

         
      System.out.println("______________________________________________________________________________________________________________________________________________");
      System.out.println("______________________________________________________________________________________________________________________________________________");
      System.out.println("______________________________________________________________________________________________________________________________________________");
      System.out.println("______________________________________________________________________________________________________________________________________________");
      System.out.println("______________________________________________________________________________________________________________________________________________");
      System.out.println(); 
      System.out.println();     
      System.out.println("                   .d8888b.           888                        8888888b.                                     888   ");     
      System.out.println("                  d88P  Y88b          888                        888   Y88b                                    888   ");     
      System.out.println("                  Y88b.               888                        888    888                                    888   ");     
      System.out.println("                  \"Y888b.     8888b.  888  .d88b.  .d8888b       888   d88P  .d88b.  88888b.   .d88b.  888d888 888888");     
      System.out.println("                      \"Y88b.     \"88b 888 d8P  Y8b 88K           8888888P\"  d8P  Y8b 888 \"88b d88\"88b  888P\"   888   ");     
      System.out.println("                        \"888 .d888888 888 88888888 \"Y8888b.      888 T88b   88888888 888  888 888  888 888     888   ");     
      System.out.println("                  Y88b  d88P 888  888 888 Y8b.          X88      888  T88b  Y8b.     888 d88P Y88..88P 888     Y88b. ");     
      System.out.println("                   \"Y8888P\"  \"Y888888 888  \"Y8888   88888P'      888   T88b  \"Y8888  88888P\"   \"Y88P\"  888      \"Y888");
      System.out.println("                                                                                     888                             ");
      System.out.println("                                                                                     888                             ");
      System.out.println("                                                                                     888                             ");
      
      System.out.println("______________________________________________________________________________________________________________________________________________");
         
   
   
   }
   
   /*This method and only this method was written by Dr. Dan Li*/
   public static Connection getConnection() throws SQLException{
      Connection connection; 
      try {
         Class.forName("com.mysql.jdbc.Driver").newInstance();
      } 
      catch (InstantiationException e1) {
         e1.printStackTrace();
      } 
      catch (IllegalAccessException e1) {
         e1.printStackTrace();
      } 
      catch (ClassNotFoundException e1) {
         e1.printStackTrace();
      }
            //Create a connection to the database
      String serverName = "localhost:3306";
      String mydatabase = "jtew_6";
      String url = "jdbc:mysql://" + serverName + "/" + mydatabase; // a JDBC url
      String username = "root";
      String password = "";
      connection = DriverManager.getConnection(url, username, password);
      System.out.println("Success! Your connected to the database named " + mydatabase); //my code
      System.out.println("At the address " + url);
      System.out.println();
      return connection;
   }
}