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

import javax.swing.*;

import java.sql.*;

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

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

	public StuGradeStat() {
		super("成績統(tǒng)計");
		c = getContentPane();
		c.setLayout(new BorderLayout());
		StuNuLabel = new JLabel("班級編號   ", JLabel.CENTER);
		StuNuTextField = new JTextField(15);
		SearchBtn = new JButton("統(tǒng)計");
		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(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 = { "最高分","最低分" ,"平均分"};
		String[][] data = new String[0][0];
		defaultModel = new DefaultTableModel(data, name);
		table = new JTable(defaultModel);
		table.setPreferredScrollableViewportSize(new Dimension(400, 180));
		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 最高分,最低分,平均分 from xs inner join (select 學號,max(成績) as 最高分,min(成績) as 最低分,avg(成績)as 平均分 from cj group by 學號) as cj2 on xs.學號=cj2.學號 ";		 
      		 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ù)；
      					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));
      					defaultModel.addRow(data);
      				}
      				table.revalidate();
      			} catch (SQLException sqle) {
      				System.out.println(sqle.toString());
      			} catch (Exception ex) {
      				System.out.println(ex.toString());
      			}
      		}
      	}
}