/* Author: Sridhar Narayan
           Dept. of Computer Science
           UNCW
*/
import javabook.*;
import java.awt.*;

import java.sql.*;



public class DatabaseApp {

  static Connection dbCon;
  static Statement stmt;
  static final String quote = "\"";

  public static void main(String []args)
  {
     MainWindow mw = new MainWindow("Employee Management System");

     mw.show();
     InputBox ib = new InputBox(mw);



     ListBox lb = new ListBox(mw,"Select an option");
     lb.addItem("Add employee");
     lb.addItem("Find employee");
     lb.addItem("Delete employee");
     lb.addItem("Exit");

     String [] labels = new String[4];
      labels[0] = "Host name ?";
      labels[1] = "Database name ?";
      labels[2] = "Username ?";
      labels[3] = "Password ?";
      MultiInputBox mib  = new MultiInputBox(mw, labels);
      mib.setTitle("Connect to database");
      String [] response = mib.getInputs();
     dbCon = getDatabaseConnection(response[0],response[1],response[2],response[3]);
      

     
     final int EXIT = 3;
     int choice;
     while ((choice = lb.getSelectedIndex()) != EXIT )
     {

        switch (choice) {
     
        case 0:  // Add employee
         addEmployee(mw);
         break;
        case 1: 
         findEmployee(mw);
         break;
        case 2:
        deleteEmployee(mw);
          break;
        default:
         break;
        }
     }

     // Done. Clean up
     try {
 dbCon.close();
    }
    catch (SQLException E) {
 System.out.println("SQLException: " + E.getMessage());
 System.out.println("SQLState:     " + E.getSQLState());
 System.out.println("VendorError:  " + E.getErrorCode());
    }

  }

  private static ResultSet executeQuery(String s)
  {  ResultSet rs = null;
     try {
      stmt = dbCon.createStatement();
      rs = stmt.executeQuery(s);
      stmt.close();
      }
 catch (SQLException E) {
 System.out.println("SQLException: " + E.getMessage());
 System.out.println("SQLState:     " + E.getSQLState());
 System.out.println("VendorError:  " + E.getErrorCode());
      }


      return rs;
  }
  private static void executeUpdate(String s)
  {
     try {
      stmt = dbCon.createStatement();

      stmt.executeUpdate(s);
      stmt.close();
      }
 catch (SQLException E) {
 System.out.println("SQLException: " + E.getMessage());
 System.out.println("SQLState:     " + E.getSQLState());
 System.out.println("VendorError:  " + E.getErrorCode());
      }


  }
  private static void addEmployee(MainWindow mw)
  {
      String [] labels = new String[3];
      labels[0] = "SS# ?";
      labels[1] = "Name ?";
      labels[2] = "Salary ?";
      MultiInputBox mib  = new MultiInputBox(mw, labels);
      mib.setTitle("Enter employee data");
      String [] response = mib.getInputs();

      String s = "insert into emp values("+quote+response[0]+quote+","+quote+response[1]+quote+","+quote+response[2]+quote+")" ;
      executeUpdate(s);

  }
  private static void findEmployee(MainWindow mw)
  {
      String [] labels = new String[2];
      labels[0] = "Min Salary ?";
      labels[1] = "Max Salary ?";


      MultiInputBox mib  = new MultiInputBox(mw, labels);
      mib.setTitle("Enter salary limits");
      String [] response = mib.getInputs();

      String s = "select * from emp where salary between "+response[0]+" and "+response[1];
      ResultSet rs = executeQuery(s);


      OutputBox ob = new OutputBox(mw);

      try {
        ob.show();
        if ( rs == null )
           ob.printLine("Search produced no results");
        else
          while ( rs.next()  ) // results left to be processed
          {
          ob.printLine("SSN: "+rs.getString(1));
          ob.printLine("Name: "+rs.getString(2));
          ob.printLine("Salary: "+rs.getString(3));
          }
      }
      catch (SQLException E) {
 System.out.println("SQLException: " + E.getMessage());
 System.out.println("SQLState:     " + E.getSQLState());
 System.out.println("VendorError:  " + E.getErrorCode());
      }

      ob.waitUntilClose();

  }
  private static void deleteEmployee(MainWindow mw)
  {
      InputBox ib  = new InputBox(mw);
      ib.setTitle("Firing Line");
      String ssn = ib.getString("Enter employee id to terminate: ");

      String s = "delete from emp where ssn = "+quote+ssn+quote;
      executeUpdate(s);

  }
  private static Connection getDatabaseConnection(String machine, String database, String user, String password)
  {
    Connection dbaseConn = null;
    try {
    // The newInstance() call is a work around for some
    // broken Java implementations
    Class.forName("org.gjt.mm.mysql.Driver").newInstance();
    }
    catch (Exception E) {
 System.err.println("Unable to load driver.");
 E.printStackTrace();
    }

    try {
 dbaseConn =
    DriverManager.getConnection("jdbc:mysql://"+machine+"/"+database+"?"+"user="+user+"&password="+password); 
    }
    catch (SQLException E) {
 System.out.println("SQLException: " + E.getMessage());
 System.out.println("SQLState:     " + E.getSQLState());
 System.out.println("VendorError:  " + E.getErrorCode());
    }

    // success
    return dbaseConn;
   }



}