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

import javax.swing.*;
import java.sql.*;

import javax.swing.table.*;
import java.util.Vector;

public class StuGradeQuery extends JFrame implements ActionListener {
	DataBaseManager db = new DataBaseManager();
	ResultSet rs;
	Container c;
	JPanel panel1, panel2;
	JLabel StuNuLabel, StuBnNuLabel;
	JTextField StuNuTextField, StuBnNuTextField;
	JButton SearchBtn, ExitBtn;
	JTable table = null;
	DefaultTableModel defaultModel = null;

	public StuGradeQuery() {
		super("查詢成績");
		this.setResizable(false);
		c = getContentPane();
		c.setLayout(new BorderLayout());
		StuNuLabel = new JLabel("學(xué)號   ", JLabel.CENTER);
		StuBnNuLabel=new JLabel("課程編號   ", JLabel.CENTER);
		StuNuTextField = new JTextField(15);
		StuBnNuTextField = new JTextField(15);
		SearchBtn = new JButton("查詢");
		ExitBtn = new JButton("退出");
		SearchBtn.addActionListener(this);
		ExitBtn.addActionListener(this);
		Box box1 = Box.createHorizontalBox();
		box1.add(StuNuLabel);
		box1.add(StuNuTextField);
		box1.add(SearchBtn);
		Box box2 = Box.createHorizontalBox();
		box2.add(StuBnNuLabel);
		box2.add(StuBnNuTextField);
		box2.add(ExitBtn);
		Box boxH = Box.createVerticalBox();
		boxH.add(box1);
		boxH.add(box2);
		boxH.add(Box.createVerticalGlue());
		panel1 = new JPanel();
		panel1.add(boxH);
		panel2 = new JPanel();
		String[] name = { "學(xué)號","課程編號" , "學(xué)期名稱","科目名稱","成績","等級"};
		String[][] data = new String[0][0];
		defaultModel = new DefaultTableModel(data, name);
		table = new JTable(defaultModel);
		table.setPreferredScrollableViewportSize(new Dimension(500, 280));
		JScrollPane s = new JScrollPane(table);
		panel2.add(s);
		c.add(panel1, BorderLayout.NORTH);
		c.add(panel2, BorderLayout.SOUTH);
	}

	   public void actionPerformed(ActionEvent e) {
      		if (e.getSource() == ExitBtn) {
      			this.dispose();
      		} else if (e.getSource() == SearchBtn) {
      			String strSQL = "select 學(xué)號,課程編號,學(xué)期名稱,科目名稱,成績,等級 from cj ";		 
      			String strSql =null;
      			if (StuNuTextField.getText().trim().equals("")
      					&& StuBnNuTextField.getText().trim().equals("")) {
      				strSql = strSQL;
      			} else if (StuNuTextField.getText().trim().equals("")) {
      				strSql = strSQL + " where 課程編號 like '%" 
      						+ StuBnNuTextField.getText().trim() + "%'";
      			} else if (StuBnNuTextField.getText().trim().equals("")) {
      				strSql = strSQL + " where 學(xué)號 like  '%"
      						+ StuNuTextField.getText().trim() + "%'";
      			} else {
      				strSql = strSQL + " where 學(xué)號 like '%"
      						+ StuNuTextField.getText().trim()
      						+ "%' and 課程編號 like '%"
      						+ StuBnNuTextField.getText().trim() + "%'";
      			}
      			try {
      				// 首先要刪除table中的數(shù)據(jù)：
      				int rowCount = defaultModel.getRowCount() - 1; // 取得table中的數(shù)據(jù)行；
      				int j = rowCount;
      				for (int i = 0; i <= rowCount; i++) {
      					defaultModel.removeRow(j);// 刪除rowCount行的數(shù)據(jù)；
      					defaultModel.setRowCount(j);// 重新設(shè)置行數(shù)；
      					j = j - 1;
      				}
      				rs = db.getResult(strSql);
      				while (rs.next()) {
      					Vector data=new Vector();
      					data.addElement(rs.getString(1));
      					data.addElement(rs.getString(2));
      					data.addElement(rs.getString(3));
      					data.addElement(rs.getString(4));
      					data.addElement(rs.getString(5));
      					defaultModel.addRow(data);
      				}
      				table.revalidate();
      			} catch (SQLException sqle) {
      				System.out.println(sqle.toString());
      			} catch (Exception ex) {
      				System.out.println(ex.toString());
      			}
      		}
      	}
}