/*
 * The SimpleCustFormatter Bean
 */
import java.sql.*;
import java.util.*;

public class SimpleCustFormatter {

  String classname;
  String cust_id = null;
  String min_qty = null;
  Connection conn;

  /* Constructor */
  public SimpleCustFormatter() throws Exception {
    /* Load a JDBC Driver and get a connection. */
    Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
    conn = 
      DriverManager.getConnection("jdbc:odbc:SAMPLE",null,null);
  }

  /* Setter method for the HTML element we create using
   * HtmlSelectListFactory (see getCustchooser()).
   */
  public void setCust_id(String cust_id) {
    this.cust_id = cust_id;
  }

  /* Setter method for the HTML element we create using
   * HtmlTextFieldFactory (see getQtyinputfield()).
   */
  public void setMin_qty(String min_qty) {
    this.min_qty = min_qty;
  }

  /* Getter method for the customer name SELECT element. */
  public String getCustchooser() throws Exception {
    SQLTable cust = new SQLTable("CUST", "CUST_ID");
    cust.addColumn("CUST_NAME", "Customer");
    cust.addColumn("CUST_ID", "Customer ID");
    return HtmlSelectListFactory.getSelectList(conn, cust, cust_id);
  }

  /* Getter method for the quantity input field. */
  public String getQtyinputfield() {
    return HtmlTextFieldFactory.getTextField("min_qty", min_qty);
  }

  /* Getter method for the results of the query. */
  public String getTable() throws Exception {

    SQLTable cust = getCust();
    if (cust_id != null) {
      cust.constrainByPrimaryKey(cust_id);
    }

    SQLTable inv = getInv();
    cust.addChildTable(inv, "INV_CUST_ID");

    SQLTable line = getLine();
    if (min_qty != null) {
      line.addConstraint("LINE_QTY", Constraint.GE, min_qty);
    }
    inv.addChildTable(line, "LINE_INV_ID");

    return HtmlTableFactory.getFormattedTable(conn, cust);
  }

  /* Create a representation of the customer table. */
  private SQLTable getCust() {
    SQLTable cust = new SQLTable("CUST", "CUST_ID");
    cust.addColumn("CUST_NAME", "Customer");
    cust.addColumn("CUST_ID", "Customer ID");
    return cust;
  }

  /* Create a representation of the invoice table. */
  private SQLTable getInv() {
    SQLTable inv  = new SQLTable("INV", "INV_ID");
    return inv;
  }

  /* Create a representation of the line item table. */
  private SQLTable getLine() {
    SQLTable line = new SQLTable("LINE", "LINE_ID");
    line.addColumn("LINE_DESC", "Item Description");
    line.addColumn("LINE_QTY", "Quantity");
    return line;
  }

}