import java.awt.Frame;
import java.awt.Event;
import java.awt.Button;
import java.awt.Checkbox;
import java.awt.TextField;
import java.awt.GridLayout;
import java.awt.Label;
import java.awt.Panel;
import java.awt.TextArea;
import java.sql.*;
import java.sql.SQLException.*;

public class FYdbEntry extends Frame  {
  static TextField field1 = new TextField("", 5);  // client #
  static TextField field2 = new TextField("", 4);   // call #
  static TextField field3 = new TextField("", 8);  // amount paid
  static TextField field4 = new TextField("", 4);  // date - just MMdd
  static TextField field5 = new TextField("", 18);  // comment
  static Checkbox field6 = new Checkbox("no comm", true);   
  static TextField field7 = new TextField("", 6);  // travel expense
  static TextField field8 = new TextField("", 6);  // phone cost
  static TextField field9 = new TextField("", 6);  // commission
  static TextField field10 = new TextField("", 7);  // part cost
  static TextField field11 = new TextField("", 7);  // tool cost
  static TextField field12 = new TextField("", 7);  // other expense

  static GridLayout severalButtons = new GridLayout(10,3,15,8);
  static Panel bsPane = new Panel(severalButtons);
  static Panel taPane = new Panel();
  static Label blank1 = new Label(" ");
  static Label blank2 = new Label(" ");
  static Label blank3 = new Label(" ");
  static Label blank4 = new Label(" ");
  static Label lab1 = new Label(" Client ID");
  static Label lab2 = new Label(" Call #");
  static Label lab3 = new Label(" $ paid");
  static Label lab4 = new Label(" date");
  static Label lab5 = new Label(" comments");
  static Label lab6 = new Label(" ");
  static Label lab7 = new Label(" travel");
  static Label lab8 = new Label(" phone");
  static Label lab9 = new Label(" commission");
  static Label lab10 = new Label(" part(s)");
  static Label lab11 = new Label(" tool(s)");
  static Label lab12 = new Label(" other expense");
  static Button submitData = new Button("Submit");
  static Button viewDB = new Button("View Dbase");
  static Button otherFields = new Button("Expenses");
  static TextArea sqlanswers = new TextArea("", 12, 58, java.awt.TextArea.SCROLLBARS_VERTICAL_ONLY);
    String dbUrl = "jdbc:odbc:demonstration";
    String user = "";
    String password = "";
  String dbresultverify;

  FYdbEntry()  {
    new Frame();
  }

  public void dispose()  {  super.dispose();  }

  public boolean handleEvent(Event wiev)  {
    if(wiev.id == Event.WINDOW_DESTROY)  {
	dispose();
	System.exit(0);
    }
    else if (wiev.id == Event.ACTION_EVENT)  {
      if (wiev.target instanceof Button)  {

	String value1 = field1.getText();
	String value2 = field2.getText();
	String value3 = field3.getText();

	try  {
            Class.forName (
                "sun.jdbc.odbc.JdbcOdbcDriver");
	    Connection c = DriverManager.getConnection(
		dbUrl, user, password);
	    // SQL code:
	    Statement s = c.createStatement();
	    int nilval = s.executeUpdate("use FY1;");

	    if (wiev.arg == "Submit")  {
	      dbresultverify = "Written to database - Result:\n\n";
	      if (field6.getState())  {
	        int nilval2 = s.executeUpdate(
		  "INSERT INTO fydb (client_number, call_number, amt_paid, date, metro_x, phone_x, commission, part_cost, tool_purchase, other_expense, comment) " +
			"VALUES (\"" + value1 + "\", \"" + value2 + "\", \"" + value3 + "\", \"2001" + field4.getText() + "\", \"" + field7.getText() + "\", \"" + field8.getText() + "\", \"" + field9.getText() + "\", \"" + field10.getText() + "\", \"" + field11.getText() + "\", \"" + field12.getText() + "\", \"" + field5.getText() + 
                	"\");"   );
	      }
	      else  {
		int nilval2 = s.executeUpdate(
		  "INSERT INTO fydb (client_number, call_number, amt_paid, date, no_cmish, metro_x, phone_x, commission, part_cost, tool_purchase, other_expense, comment) " +
			"VALUES (\"" + value1 + "\", \"" + value2 + "\", \"" + value3 + "\", \"2001" + field4.getText() + "\", \"\", \"" + field7.getText() + "\", \"" + field8.getText() + "\", \"" + field9.getText() + "\", \"" + field10.getText() + "\", \"" + field11.getText() + "\", \"" + field12.getText() + "\", \"" + field5.getText() + 
			"\");"   );
	      }

	      ResultSet r = s.executeQuery(
		"SELECT * FROM FY1.fydb WHERE client_number='" + value1 + 
		  "' AND call_number='" + value2 + "';");
	      while (r.next())  {
		dbresultverify = dbresultverify + r.getString("client_number") + "-" + r.getString("call_number") + " " + r.getString("date") + "  $" + r.getString("amt_paid") + "  " + r.getString("comment"); 
		if (r.getString("no_cmish") != "null")  {
		  dbresultverify = dbresultverify + "*" + "\n";
		}
		else {  dbresultverify = dbresultverify + "\n";
		}
	      }
	    }
	    if (wiev.arg == "View Dbase")  {
	      dbresultverify = " - FY1.fydb database contains:\n--------------------------------\n";

	      ResultSet r = s.executeQuery(
		"SELECT * FROM FY1.fydb;"  );
	      while (r.next())  {
		dbresultverify = dbresultverify + r.getString("client_number") + "-" + r.getString("call_number") + " " + r.getString("date") + "  $" + r.getString("amt_paid") + "  " + r.getString("comment"); 
		if (r.getString("no_cmish") == "")  {
		  dbresultverify = dbresultverify + "*" + "\n";
		}
		else {  dbresultverify = dbresultverify + "\n";
		}
	      }
	    }
	    if (wiev.arg == "Expenses")  {
	      dbresultverify = " - FY1.fydb expense fields:\n--------------------------------\n";

	      ResultSet r = s.executeQuery(
		"SELECT * FROM FY1.fydb;"  );
	      while (r.next())  {
		dbresultverify = dbresultverify + r.getString("client_number") + "-" + r.getString("call_number") + " M$" + r.getString("metro_x") + " ph$" + r.getString("phone_x") + " P$" + r.getString("part_cost") + " t$" + r.getString("tool_purchase") + " O$" + r.getString("other_expense"); 
		if (r.getString("no_cmish") != "null")  {
		  dbresultverify = dbresultverify + "\n";
		}
		else {  dbresultverify = dbresultverify + "  J$" + r.getString("commission") + "\n";
		}
	      }
	    }
	    sqlanswers.setText(dbresultverify);

	    s.close(); 
	}
	catch (SQLException e)  {
		e.printStackTrace();
                System.out.println("\nSQL State code:\n" + e.getSQLState());
	}
	catch (Exception e)  {
		e.printStackTrace();
	}
	finally  {
	    field3.setText("");
	    field4.setText("");
	    field5.setText("");
	    field6.setEnabled(true);
	    field7.setText("");
	    field8.setText("");
	    field9.setText("");
	    field10.setText("");
	    field11.setText("");
	    field12.setText("");
	}

	return true;

      }
      return true;
    }
    else  {
      return super.handleEvent(wiev);
    }
    return true;
  }

    public static void main (String[] args)  {
	FYdbEntry FY1Frm = new FYdbEntry();
	bsPane.add(lab1);
	bsPane.add(lab2);
	bsPane.add(lab3);
	bsPane.add(field1);
	bsPane.add(field2);
	bsPane.add(field3);
	bsPane.add(lab4);
	bsPane.add(lab5);
	bsPane.add(lab6);
	bsPane.add(field4);
	bsPane.add(field5);
	bsPane.add(field6);
	bsPane.add(lab7);
	bsPane.add(lab8);
	bsPane.add(lab9);
	bsPane.add(field7);
	bsPane.add(field8);
	bsPane.add(field9);
	bsPane.add(lab10);
	bsPane.add(lab11);
	bsPane.add(lab12);
	bsPane.add(field10);
	bsPane.add(field11);
	bsPane.add(field12);
	bsPane.add(submitData);
	bsPane.add(viewDB);
	bsPane.add(otherFields);
	taPane.add(sqlanswers);
	FY1Frm.add(bsPane);
	FY1Frm.add("South", taPane);
	bsPane.show();
	FY1Frm.resize(450, 485);
	FY1Frm.setTitle("Enter data into FY1:fydb - JDBC by R. Hess");
	FY1Frm.show();
    }

}