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

public class CookieDBmm extends Applet {
  boolean isStandalone = false;
  String bgColor;
  Label title = new Label("CS301 JAVA-JDBC-ODBC-MySQL Database Exercise using mmMySQL driver");
  Label label = new Label("SQL statement for test database at bilbo.uccs.edu");
  TextArea sqlStatement = new TextArea("select COOKIE_NAME, PRICE from COOKIES where PRICE < 1.50", 3, 60);
  Button submit = new Button("submit sql");
  TextArea sqlResult= new TextArea("sql results:", 10, 30);

  StringBuffer cookieName;
  StringBuffer supplierID;
  StringBuffer price;
  String url = "jdbc:mysql://blanca.uccs.edu:3306/cs301db?user=cs301;password=cs02web";

  Connection con;
  Statement stmt;

//Get a parameter value

  public String getParameter(String key, String def) {
    return isStandalone ? System.getProperty(key, def) :
      (getParameter(key) != null ? getParameter(key) : def);
  }

  //Construct the applet

  public CookieDBmm() {
  }
//Initialize the applet

  public void init() {
    try { bgColor = this.getParameter("bgColor", "green"); } catch (Exception e) { e.printStackTrace(); }
    try {
    jbInit();
    }
    catch (Exception e) {
    e.printStackTrace();
    }
  }


  private void jbInit() throws Exception {
    this.setSize(610,400);
    setBackground(Color.green);
    if (bgColor.equals("blue")) setBackground(Color.blue);
    if (bgColor.equals("red")) setBackground(Color.red);
    if (bgColor.equals("lightGray")) setBackground(Color.lightGray);
    if (bgColor.equals("yellow")) setBackground(Color.yellow);
    if (bgColor.equals("cyan")) setBackground(Color.cyan);
    if (bgColor.equals("pink")) setBackground(Color.pink);
    if (bgColor.equals("magenta")) setBackground(Color.magenta);
    setLayout(new GridBagLayout());
    GridBagConstraints gbc = new GridBagConstraints();
    gbc.anchor = GridBagConstraints.CENTER;
    gbc.gridwidth = GridBagConstraints.REMAINDER;
    gbc.insets = new Insets(0,0,10,10);
    Font hb18 = new Font("Helvetica",Font.BOLD,18);             // set font
    title.setFont(hb18);
    title.setForeground(Color.red);
    add(title, gbc);
    gbc.anchor = GridBagConstraints.CENTER;
    gbc.gridwidth = GridBagConstraints.REMAINDER;
    gbc.insets = new Insets(0,0,10,10);
    label.setFont(hb18);
    label.setForeground(Color.blue);
    add(label, gbc);
    gbc.anchor = GridBagConstraints.CENTER;
    gbc.gridwidth = GridBagConstraints.REMAINDER;
    gbc.insets = new Insets(0,0,10,10);
    sqlStatement.setFont(hb18);
    sqlStatement.setBackground(Color.lightGray);
    add(sqlStatement, gbc);
    gbc.anchor = GridBagConstraints.CENTER;
    gbc.gridwidth = GridBagConstraints.REMAINDER;
    gbc.insets = new Insets(0,0,10,10);
    submit.setFont(hb18);
    add(submit, gbc);
    gbc.anchor = GridBagConstraints.CENTER;
    gbc.gridwidth = GridBagConstraints.REMAINDER;
    gbc.insets = new Insets(0,0,10,10);
    Font cb18 = new Font("Courier",Font.BOLD,18);             // set font
    sqlResult.setFont(cb18);
    sqlResult.setBackground(Color.cyan);
    add(sqlResult, gbc);
    try {
			Class.forName("org.gjt.mm.mysql.Driver");

		} catch(java.lang.ClassNotFoundException e) {
			System.err.print("ClassNotFoundException: ");
			System.err.println(e.getMessage());
		}


		try {
			con = DriverManager.getConnection(url);


      stmt = con.createStatement();
      String sqlcmd = "drop table COOKIES";
      stmt.executeUpdate(sqlcmd);
      sqlcmd = "create table COOKIES(COOKIE_NAME, varchar(32) not null, SUP_ID int(6), PRICE float(8))";
      stmt.executeUpdate(sqlcmd);
      sqlcmd = "insert into COOKIES values ('CHOCOLATE', 101, 1.30);"+
               "insert into COOKIES values ('M&M', 49, 2.49);"+
               "insert into COOKIES values ('MINT', 150, 1.00)";
      stmt.executeUpdate(sqlcmd);

	   	String sqlselect = "select * from COOKIES";
      ResultSet rs = stmt.executeQuery(sqlselect);
      sqlResult.setText("     COOKIES TABLBE\nCOOKIE_NAME SUP_ID PRICE\n");
      sqlResult.append("========================\n");
      while (rs.next()) {
        cookieName = new StringBuffer(rs.getString(1));
        cookieName = center(cookieName, 10);
        supplierID = new StringBuffer(rs.getString(2));
        supplierID = right(supplierID, 6);
        price = new StringBuffer(rs.getString(3));
        sqlResult.append(cookieName+" "+supplierID+" "+price+"\n");
      }
			stmt.close();
			con.close();

		} catch(SQLException ex) {
			System.err.println("SQLException in creating table: " + ex.getMessage());
		}
  }
  public StringBuffer center(StringBuffer s, int n) {
    int length = s.length();
    for (int i = 0; i< n-length; i++) {
      if (i%2==0) s.insert(0, " ");
      else s.append(" ");
    }
    return s;
  }
  public StringBuffer right(StringBuffer  s, int n) {
    int length = s.length();
    for (int i = 0; i< n-length; i++) {
      s.insert(0, " ");
    }
    return s;
  }
  public boolean action(Event e, Object o) {

		try {
			Class.forName("org.gjt.mm.mysql.Driver");

		} catch(java.lang.ClassNotFoundException ex) {
			System.err.print("ClassNotFoundException: ");
			System.err.println(ex.getMessage());
		}
		try {
			con = DriverManager.getConnection("jdbc:mysql://crestone.uccs.edu:3306/test?user=cs522p1;password=cn98cs");

			stmt = con.createStatement();

	   	String sqlselect = sqlStatement.getText();
      ResultSet rs = stmt.executeQuery(sqlselect);
      sqlResult.setText("COOKIE_NAME PRICE\n");
      sqlResult.append("=================\n");
      while (rs.next()) {
        cookieName = new StringBuffer(rs.getString(1));
        cookieName = center(cookieName, 10);
        price = new StringBuffer(rs.getString(2));
        sqlResult.append(cookieName+" "+price+"\n");
      }

			stmt.close();
			con.close();

		} catch(SQLException ex) {
			System.err.println("SQLException in select: " + ex.getMessage());
		}
    return true;
  }
//Get Applet information

  public String getAppletInfo() {
    return "Applet Information";
  }
//Get parameter info
  
  public String[][] getParameterInfo() {
    String pinfo[][] = 
    {
      {"bgColor", "String", ""},
    };
    return pinfo;
  }
}