import java.awt.*;
import java.awt.event.*;
import javax.swing.*;
import java.util.*;
import java.sql.*;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import javax.swing.table.*;
import java.sql.ResultSet;
import java.util.Arrays;

class pingding extends JFrame implements ActionListener ,ItemListener{

	JLabel lb1=new JLabel("獎(jiǎng) 學(xué) 金 評(píng) 定");
   	JLabel lb3=new JLabel("專業(yè)班級(jí)：");
   	JComboBox setxtclass=new JComboBox();
   	JButton btn1=new JButton("查詢");
    JButton btn2=new JButton("評(píng)定");
   	JTable table;
	DefaultTableModel dtm;
	String columns[] = {"學(xué)號(hào)","姓名","專業(yè)班級(jí)","智育成績(jī)","綜合測(cè)評(píng)","獎(jiǎng)學(xué)金等級(jí)"};
	Object body[][]=new Object[50][6];
	
	Connection con;
    Statement stmt;
    ResultSet rs;
	public pingding(){
		setTitle("獎(jiǎng)學(xué)金評(píng)定");
		table = new JTable(body,columns);
        JScrollPane sl = new JScrollPane(table);
		
		getContentPane().setLayout(null);
		lb1.setBounds(340,10,300,30);
		lb1.setFont(new Font("宋體",Font.BOLD,30));
		getContentPane().add(lb1);
		

		Font f=new Font("宋體",Font.PLAIN,12);
	    setxtclass.addItem("計(jì)算機(jī)1班");
		setxtclass.addItem("計(jì)算機(jī)2班");
		setxtclass.addItem("計(jì)算機(jī)3班");
		setxtclass.addItem("計(jì)算機(jī)4班");
		setxtclass.addItemListener(this);

		setxtclass.setBounds(80,90,85,23);
		setxtclass.setFont(f);
		getContentPane().add(setxtclass);
		lb3.setBounds(10,90,80,25);
		lb3.setFont(f);
   	    getContentPane().add(lb3);
  	    
   	    btn1.setBounds(90,135,60,25);
   	    btn1.setFont(f);
   	    getContentPane().add(btn1);
   	    btn1.addActionListener(this);
   	    btn2.setBounds(90,170,60,25);
   	    btn2.setFont(f);
   	    getContentPane().add(btn2);
   	    btn2.addActionListener(this);

		sl.setBounds(180,60,600,350);
		getContentPane().add(sl);

//設(shè)置邊框

   	    setxtclass.setBorder(BorderFactory.createLineBorder(Color.black));
   	    btn1.setBorder(BorderFactory.createRaisedBevelBorder());
   	    btn2.setBorder(BorderFactory.createRaisedBevelBorder());
		sl.setBorder(BorderFactory.createLineBorder(Color.black));
		Dimension screenSize = Toolkit.getDefaultToolkit().getScreenSize();
        setBounds((screenSize.width-803)/2, (screenSize.height-629)/2, 800,480);
		//setSize(630,400);
		setVisible(true);
        //this.connection();
		}
public void itemStateChanged(ItemEvent e){
	setxtclass.getSelectedItem();
	
	}		
		

public void actionPerformed(ActionEvent evt){
	if(evt.getSource()==btn1){ 
     if(setxtclass.getSelectedItem().equals(""))
		    new JOptionPane().showMessageDialog(null,"無(wú)效查詢條件！");
	try{ 
        Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
        con=DriverManager.getConnection("jdbc:odbc:studentscore");
        stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);  
    	rs=stmt.executeQuery("select * from studentscore where  專業(yè)班級(jí) = '" + setxtclass.getSelectedItem() +"'ORDER BY 綜合測(cè)評(píng)成績(jī) DESC");}
    	
    catch(Exception e){System.out.println(e.getMessage());}
    
		 try{for(int x=0;x<body.length;x++){
            body[x][0]=null;
            body[x][1]=null;
            body[x][2]=null;
            body[x][3]=null;
            body[x][4]=null;
            body[x][5]=null;
            
            
            
            }
           int i=0;
	     while(rs.next()) 
  	        {   
	            
            body[i][0]=rs.getString(1);
            body[i][1]=rs.getString(2);
            body[i][2]=rs.getString(3);
            body[i][3]=rs.getString(12);
            body[i][4]=rs.getString(13);
            body[i][5]=rs.getString(13);
            body[i][5]=rs.getString(14);
   	        i++;
            
	        }
	        
	        table.validate(); 
            table.repaint();
	        con.close();
	        } 
	        catch(SQLException ex){System.out.println(ex.getMessage());}
	     }
	       
	if(evt.getSource()==btn2){

   try{ 
        Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
        con=DriverManager.getConnection("jdbc:odbc:studentscore");
        stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);  
    	rs=stmt.executeQuery("select * from studentscore where  專業(yè)班級(jí) = '" + setxtclass.getSelectedItem() +"'ORDER BY 綜合測(cè)評(píng)成績(jī) DESC");}
    	
    catch(Exception e){System.out.println(e.getMessage());}
    
		 try{for(int x=0;x<body.length;x++){
            body[x][0]=null;
            body[x][1]=null;
            body[x][2]=null;
            body[x][3]=null;
            body[x][4]=null;
            body[x][5]=null;
            }
           int i=0;
	     while(rs.next()) 
  	        {   
	            
            body[i][0]=rs.getString(1);
            body[i][1]=rs.getString(2);
            body[i][2]=rs.getString(3);
            body[i][3]=rs.getString(12);
            body[i][4]=rs.getString(13);
            //a[i]=body[i][4];
            if(i<=3)  body[i][5]="一等";
            if(i>3&&i<=6)  body[i][5]="二等";
            if(i>11&&i<=10)  body[i][5]="三等";
          
            String supdate="update studentscore set 獎(jiǎng)學(xué)金等級(jí)='"+ body[i][5]+"'where 學(xué)號(hào)='"+ body[i][0]+"'";
   		    stmt.executeUpdate(supdate);
   		    String sql="select * from studentscore where  專業(yè)班級(jí) = '" + setxtclass.getSelectedItem() +"'ORDER BY 綜合測(cè)評(píng)成績(jī) DESC";
   	        rs=stmt.executeQuery(sql);
   	        i++;
            for(int j=0;j<i;j++)
            {rs.next();}
            
	        }
	        
	        table.validate(); 
	        table.repaint();
            //setxtclass.setText("");
	        con.close();
	        } 
	        catch(SQLException ex){System.out.println(ex.getMessage());}
	     }
	        	    
	

}
		 
}
//-------------------------------------------------------------------

/*public class pingding1{
	public static void main(String args[]){
		new pingding();}
}*/