/**
 *
 *  FILE:  JobSelect.java
 *
 *  The JobSelect application is a JDBC application that makes use of
 *  prepared statements and stored procedures.
 *
 *  The application allows the user to search the database for consultants
 *  with a given maximum hourly rate and job category.  The user can also
 *  update the hourly rate for consultants in a given field.
 *
 *  Compiled and tested with JDK 1.1.4
 *
 *  @author Chad (shod) Darby, darby@j-nine.com
 *  @version v4.34am, 18 Feb 1998
 *
 */

import java.awt.*;
import java.awt.event.*;
import java.sql.*;

import DataDialog;
import SearchActionListener;
import UpdateActionListener;

public class JobSelect extends Frame implements ActionListener, WindowListener
{
  // GUI data members
  Panel toolBarPanel, editPanel, statusBarPanel;
  Button searchButton, updateButton, exitButton;
  TextArea displayTextArea;
  Label statusLabel;

  // data members
  protected String rate;

  final static int RATE_POSITION = 1, JOBCATEGORY_POSITION = 2;

  // database data members
  protected Connection myConnection;
  protected PreparedStatement searchPreparedStatement;
  protected CallableStatement updateHourlyStatement;
  protected ResultSet myResultSet;
  protected String databaseURL = "jdbc:odbc:edgewood";

  public void executeUpdateHourlyRate(String theRate, String theJobCategory)
  {

    try
    {
      Float rate = Float.valueOf(theRate);
      updateHourlyStatement.setInt(RATE_POSITION, rate.intValue());
      updateHourlyStatement.setString(JOBCATEGORY_POSITION, theJobCategory);

      int rowsAffected = updateHourlyStatement.executeUpdate();
      displayTextArea.setText("Updated " + rowsAffected + " rows.\n\n");
      displayTextArea.append("Updates Complete.");
      statusLabel.setText("Updates Complete. ");
    }

    catch (SQLException exception)
    {
      statusLabel.setText(exception.toString());
      exception.printStackTrace();
    }
    catch (NumberFormatException exception)
    {
      statusLabel.setText("ERROR:  Illegal Hourly Rate entered!");
    }

  }

  public void executeHourlyRateSearch(String theRate, String theJobCategory)
  {
    try
    {
      Float rate = Float.valueOf(theRate);
      searchPreparedStatement.setInt(RATE_POSITION, rate.intValue());
      searchPreparedStatement.setString(JOBCATEGORY_POSITION, theJobCategory);

      myResultSet = searchPreparedStatement.executeQuery();
      ResultSetMetaData myResultSetMetaData = myResultSet.getMetaData();

      displayTextArea.setText("");

      // display column names
      int columnCount = myResultSetMetaData.getColumnCount();
      int j;
      for (j=1; j < columnCount; j++)
      {
        displayTextArea.append(myResultSetMetaData.getColumnName(j) + "\t");     
      }
      displayTextArea.append("\t\t" + myResultSetMetaData.getColumnName(j));
      displayTextArea.append("\n\n");

      // display query results
      while (myResultSet.next())
      {
        for (int i=1; i <= columnCount; i++)
        {
          displayTextArea.append(myResultSet.getString(i) + "\t\t");     
        }
        displayTextArea.append("\n");
      }

      myResultSet.close();
      statusLabel.setText("Hourly Rate Search complete.");

      }
    catch (SQLException exception)
    {
      statusLabel.setText(exception.toString());
      exception.printStackTrace();
    }
    catch (NumberFormatException exception)
    {
      statusLabel.setText("ERROR:  Illegal Hourly Rate entered!");
    }
  }

  protected void buildGui()
  {
    setLayout(new BorderLayout());
    
    // create toolBarPanel and add three buttons
    toolBarPanel = new Panel();
    toolBarPanel.setLayout(new FlowLayout());
    toolBarPanel.add(searchButton = new Button("Hourly Rate Search"));
    toolBarPanel.add(updateButton = new Button("Update Hourly Rates"));
    toolBarPanel.add(exitButton = new Button("Exit"));

    //
    //  Comment out this line if your DB supports stored procedures  
    //
    updateButton.setEnabled(false);

    // add the toolBarPanel to current Frame
    add("North", toolBarPanel);

    // create editPanel and add a text area
    editPanel = new Panel();
    editPanel.add(displayTextArea = new TextArea(20, 80));    // rows x cols
    displayTextArea.setEditable(false);

    // add the editPanel to current Frame
    add("Center", editPanel);

    // create statusBarPanel and add a static label
    statusBarPanel = new Panel();
    statusBarPanel.add(statusLabel = new Label("Awaiting Command...                  "));

    // add the statusBarPanel to current Frame
    add("South", statusBarPanel);
    pack();
    setVisible(true);

  }

  protected void setupListeners()
  {
     searchButton.addActionListener(new SearchActionListener(this));
     updateButton.addActionListener(new UpdateActionListener(this));
     exitButton.addActionListener(this);
     this.addWindowListener(this);
  }


  protected void setupDatabaseConnection()
  {
    try
    {
      // load driver
      Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

      // get database connection
      statusLabel.setText("Getting Connection...");
      myConnection = DriverManager.getConnection(databaseURL);
      statusLabel.setText("Getting Connection...Success");

      searchPreparedStatement = myConnection.prepareStatement(
                                "SELECT LastName, FirstName, Email, HourlyRate " +
                                "from consultants " +
                                "WHERE HourlyRate <= ? and JobCategory = ?");

      // If you are using MS Access, commment out the
      // following code for the stored procedure
      //    updateHourlyStatement = myConnection.prepareCall(
      //              "{call sp_updateHourlyRate [(?, ?)]}");
      
    }
    catch (Exception exception)
    {
      statusLabel.setText(exception.toString());
      exception.printStackTrace();
    }

  }

  public JobSelect(String theTitle)
  {
    super(theTitle);

    buildGui();
    setupListeners();
    setupDatabaseConnection();
  }

  // main driver
  public static void main(String argv[])
  {
    JobSelect myApp = new JobSelect("Consultant Job Selector");
  }

  public void windowClosing(WindowEvent event)
  {
    this.setVisible(false);
    this.dispose();
    System.exit(1);

  }

  public void windowClosed(WindowEvent event) { }
  public void windowActivated(WindowEvent event) { }
  public void windowDeactivated(WindowEvent event) { }
  public void windowDeiconified(WindowEvent event) { }
  public void windowIconified(WindowEvent event) { }
  public void windowOpened(WindowEvent event) { }

  public void actionPerformed(ActionEvent event)
  {
    this.windowClosing(new WindowEvent(this, WindowEvent.WINDOW_CLOSING));
  }

}