import javax.swing.*;
import java.awt.*;
import com.borland.jbcl.layout.*;
import java.awt.event.*;
import javax.swing.table.*;
import java.util.*;
import java.sql.*;

public class Query
    extends JFrame {

  DefaultTableModel model = new DefaultTableModel();
  //定義JTable為DefaultTableModel的實例model模式
  JTable tblResult = new JTable(model);
  //定義鏈表，用于建立組合框序號與表名、字段等的對應關系
  //數據庫操作變量
  DBManager db = new DBManager();
  //數據庫表鏈表
  LinkedList linkTable = new LinkedList();
  //字段鏈表
  LinkedList linkItem = new LinkedList();
  //操作符鏈表
  LinkedList linkOper = new LinkedList();
  //代碼值鏈表
  LinkedList linkCode = new LinkedList();
  //ID鏈表，供查看記錄信息時與序號建立對應關系
  LinkedList linkID = new LinkedList();
  //用于加載代碼表
  Vector vecCode = new Vector();
  //當前操作表
  String curTable = new String();
  //當前操作字段
  String curItem = new String();
  //判斷是否是初始化，以使組合框響應事件能夠正確運行
  boolean isInitTable;
  boolean isInitItem;
  //定義二維數組sTable用于存儲數據庫表信息
  private String[][] sTable = {
      {
      "BuildInfo", "房產信息"}
      , {
      "HouseInfo", "住房信息"}
      , {
      "OwnerInfo", "房主基本信息"}
      , {
      "FamilyInfo", "房主家屬信息"}
  };
  //定義二維數組sOper用于存儲數據庫表信息
  private String[][] sOper = {
      {
      "=", "等于"}
      , {
      ">", "大于"}
      , {
      "<", "小于"}
      , {
      "<>", "不等于"}
      , {
      "like", "類似"}
  };
  //定義二維數組sItem用于存儲數據庫表信息
  //意義依次為：表名；字段名；字段意義；代碼表名
  private String[][] sItems = {
      {
      "BuildInfo", "Location", "坐落", ""}
      , {
      "BuildInfo", "Time", "建成年代", ""}
      , {
      "BuildInfo", "Usage", "房屋用途", "HouseUsage"}
      , {
      "BuildInfo", "Number", "房屋套數", ""}
      , {
      "BuildInfo", "Type", "房屋類型", "HouseType"}
      , {
      "BuildInfo", "Area", "占地面積", ""}
      , {
      "BuildInfo", "UseArea", "使用面積", ""}
      , {
      "HouseInfo", "BuildID", "房屋坐落", ""}
      , {
      "HouseInfo", "Name", "房間號", ""}
      , {
      "HouseInfo", "Heading", "朝向", "Heading"}
      , {
      "HouseInfo", "Number", "間數", ""}
      , {
      "HouseInfo", "UseType", "使用方式", "UseType"}
      , {
      "HouseInfo", "BuyTime", "購房時間", ""}
      , {
      "HouseInfo", "BuyPrice", "購房價格", ""}
      , {
      "HouseInfo", "Area", "面積", ""}
      , {
      "HouseInfo", "YTArea", "陽臺面積", ""}
      , {
      "HouseInfo", "OwnerID", "住戶", ""}
      , {
      "OwnerInfo", "Name", "姓名", ""}
      , {
      "OwnerInfo", "Gender", "性別", "Gender"}
      , {
      "OwnerInfo", "Birthday", "出生年月", ""}
      , {
      "OwnerInfo", "WorkTime", "參加工作時間", ""}
      , {
      "OwnerInfo", "Org", "工作單位", ""}
      , {
      "FamilyInfo", "OwnerID", "房主姓名", ""}
      , {
      "FamilyInfo", "Name", "家屬姓名", ""}
      , {
      "FamilyInfo", "Gender", "性別", "Gender"}
      , {
      "FamilyInfo", "Relation", "與房主關系", "Relation"}
      , {
      "FamilyInfo", "Birthday", "出生年月", ""}
      , {
      "FamilyInfo", "WorkTime", "參加工作時間", ""}
  };

  JButton btnExit = new JButton();
  JLabel jLabel8 = new JLabel();
  JScrollPane jScrollPane1 = new JScrollPane();
  JLabel jLabel2 = new JLabel();
  JButton btnView = new JButton();
  JComboBox cboTable = new JComboBox();
  JComboBox cboOper = new JComboBox();
  JButton btnQuery = new JButton();
  JLabel jLabel11 = new JLabel();
  JPanel jPanel1 = new JPanel();
  JComboBox cboValue = new JComboBox();
  XYLayout xYLayout2 = new XYLayout();
  JLabel jLabel10 = new JLabel();
  JComboBox cboItem = new JComboBox();
  XYLayout xYLayout1 = new XYLayout();

  public Query() {
    try {
      jbInit();
      initForm();
    }
    catch (Exception ex) {
      ex.printStackTrace();
    }
  }

  public Query(String sql) {
    try {
      jbInit();
      initForm();
      curTable = "FamilyInfo";
      resultList(sql);
    }
    catch (Exception ex) {
      ex.printStackTrace();
    }
  }

  void jbInit() throws Exception {
    jLabel8.setText("信息集：");
    jLabel8.setRequestFocusEnabled(true);
    jLabel8.setFont(new java.awt.Font("宋體", 0, 12));
    btnExit.setText("退出");
    btnExit.addActionListener(new Query_btnExit_actionAdapter(this));
    btnExit.setFont(new java.awt.Font("宋體", 0, 12));
    this.getContentPane().setLayout(xYLayout1);
    jLabel2.setFont(new java.awt.Font("宋體", 0, 12));
    jLabel2.setText("操作符：");
    btnView.setText("查看");
    btnView.addActionListener(new Query_btnView_actionAdapter(this));
    btnView.setFont(new java.awt.Font("宋體", 0, 12));
    cboTable.setFont(new java.awt.Font("宋體", 0, 12));
    cboTable.setEditable(false);
    cboTable.addActionListener(new Query_cboTable_actionAdapter(this));
    cboOper.setFont(new java.awt.Font("宋體", 0, 12));
    cboOper.setEditable(false);
    btnQuery.setFont(new java.awt.Font("宋體", 0, 12));
    btnQuery.setText("查詢");
    btnQuery.addActionListener(new Query_btnQuery_actionAdapter(this));
    jLabel11.setFont(new java.awt.Font("宋體", 0, 12));
    jLabel11.setText("查詢值：");
    jPanel1.setBorder(BorderFactory.createEtchedBorder());
    jPanel1.setLayout(xYLayout2);
    cboValue.setFont(new java.awt.Font("宋體", 0, 12));
    cboValue.setEditable(true);
    jLabel10.setText("查詢項：");
    jLabel10.setFont(new java.awt.Font("宋體", 0, 12));
    cboItem.setFont(new java.awt.Font("宋體", 0, 12));
    cboItem.setEditable(false);
    cboItem.addActionListener(new Query_cboItem_actionAdapter(this));
    xYLayout1.setWidth(434);
    xYLayout1.setHeight(325);
    this.setState(Frame.NORMAL);
    this.setTitle("住戶信息查詢");
    tblResult.setFont(new java.awt.Font("宋體", 0, 12));
    tblResult.setVerifyInputWhenFocusTarget(false);
    jPanel1.add(jLabel8, new XYConstraints(12, 18, 68, -1));
    jPanel1.add(jLabel2, new XYConstraints(12, 57, 50, -1));
    jPanel1.add(jScrollPane1, new XYConstraints(13, 96, 366, 132));
    jScrollPane1.getViewport().add(tblResult, null);
    jPanel1.add(cboItem, new XYConstraints(235, 14, 136, -1));
    jPanel1.add(cboTable, new XYConstraints(62, 13, 112, -1));
    jPanel1.add(jLabel10, new XYConstraints(182, 18, 50, -1));
    jPanel1.add(cboOper, new XYConstraints(62, 52, 111, -1));
    jPanel1.add(jLabel11, new XYConstraints(182, 57, 50, -1));
    jPanel1.add(cboValue, new XYConstraints(234, 51, 138, -1));
    jPanel1.add(btnView, new XYConstraints(253, 243, 61, 27));
    jPanel1.add(btnExit, new XYConstraints(318, 243, 61, 27));
    jPanel1.add(btnQuery, new XYConstraints(14, 243, 61, 27));
    this.getContentPane().add(jPanel1, new XYConstraints(16, 16, 400, 289));
  }

//初始化窗體
  void initForm() {
    isInitTable = true;
    //初始化數據庫表信息
    for (int i = 0; i < sTable.length; i++) {
      cboTable.addItem(sTable[i][1]);
      linkTable.add(sTable[i][0]);
    }
    //初始化操作符
    for (int i = 0; i < sOper.length; i++) {
      cboOper.addItem(sOper[i][1]);
      linkOper.add(sOper[i][0]);
    }
    //設置選定項為空
    cboTable.setSelectedItem(null);
    cboOper.setSelectedIndex(0);
    isInitTable = false;
  }

//查詢按鈕響應事件
  void btnQuery_actionPerformed(ActionEvent e) {
    String sql = new String();
    //判斷查詢條件是否完整
    if (cboTable.getSelectedIndex() < 0 || cboItem.getSelectedIndex() < 0 ||
        (cboValue.getSelectedIndex() < 0 &&
         cboValue.getEditor().getItem().toString().equals(""))) {
      CommonDialog.showDialog(CommonDialog.OK, "房產管理系統", "請構建完整的查詢條件！");
      return;
    }
    //調用函數createSql構造查詢語句
    sql = createSql();
    //調用函數resultList，傳入查詢語句顯示結果
    resultList(sql);
  }

//構造查詢語句
  String createSql() {
    String sql = "";
    //用于存儲查詢值
    String value = "";
    String queryValue = "";
    //基本語句
    sql = "select * from " + curTable + " where " + curItem + " " +
        linkOper.get(cboOper.getSelectedIndex());
    //代碼值
    if (linkCode.size() > 0) {
      value = linkCode.get(cboValue.getSelectedIndex()).toString();
    } //對坐落和房主姓名，根據描述得到ID值
    else if (curItem.equals("BuildID")) {
      value = db.getByField("BuildInfo", "ID", "Location='" +cboValue.getEditor().getItem().toString() + "'");
    }
    else if (curItem.equals("OwnerID")) {
      value = db.getByField("OwnerInfo", "ID","Name='" + cboValue.getEditor().getItem().toString() + "'");
    }
    else {
      value = cboValue.getEditor().getItem().toString();
    }
    //對like操作做特殊處理
    if (linkOper.get(cboOper.getSelectedIndex()).equals("like")) {
      queryValue = " '%" + value + "%'";
    }
    else {
      queryValue = "'" + value + "'";
    }
    //生成最終SQL語句
    sql += queryValue;
    return sql;
  }
//顯示查詢結果
  void resultList(String sql) {
    Vector tempvector;
    //根據不同的查詢信息初始化不同的表頭信息