package xxglxt;

import java.awt.BorderLayout;

import javax.swing.JPanel;
import javax.swing.JComboBox;
import java.awt.Rectangle;
import javax.swing.JList;
import javax.swing.BorderFactory;
import java.awt.Color;
import javax.swing.JEditorPane;
import javax.swing.JLabel;
import javax.swing.border.TitledBorder;
import javax.swing.JScrollPane;
import javax.swing.JTextField;
import javax.swing.JButton;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.*;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import sun.jdbc.odbc.OdbcDef;
import javax.swing.JOptionPane;

/**
 * <p>Title: </p>
 *
 * <p>Description: </p>
 *
 * <p>Copyright: Copyright (c) 2007</p>
 *
 * <p>Company: </p>
 *
 * @author not attributable
 * @version 1.0
 */
public class cjframe extends JPanel {
    public cjframe() {
        try {
            jbInit();
        } catch (Exception exception) {
            exception.printStackTrace();
        }
    }

    private void jbInit() throws Exception {
        this.setLayout(null);
        jLabel1.setBorder(titledBorder1);
        jLabel1.setBounds(new Rectangle(10, 14, 186, 121));
        jLabel2.setText("選擇年級：");
        jLabel2.setBounds(new Rectangle(16, 15, 73, 24));
        jLabel3.setText("選擇班級：");
        jLabel3.setBounds(new Rectangle(16, 47, 71, 19));
        jLabel4.setText("選擇學生：");
        jLabel4.setBounds(new Rectangle(16, 73, 68, 21));
        jComboBox1.setBounds(new Rectangle(79, 17, 96, 18));
        jComboBox1.addActionListener(new cjframe_jComboBox1_actionAdapter(this));
        jComboBox2.setBounds(new Rectangle(77, 47, 97, 16));
        jComboBox2.addActionListener(new cjframe_jComboBox2_actionAdapter(this));
        jComboBox3.setBounds(new Rectangle(79, 74, 95, 19));
        jComboBox3.addActionListener(new cjframe_jComboBox3_actionAdapter(this));
        jLabel5.setText("學號為：");
        jLabel5.setBounds(new Rectangle(21, 102, 154, 29));
        jScrollPane1.getViewport().setBackground(Color.white);
        jScrollPane1.setBorder(BorderFactory.createLoweredBevelBorder());
        jScrollPane1.setBounds(new Rectangle(209, 17, 334, 229));
        jPanel1.setLayout(null);
        jButton1.setBounds(new Rectangle(321, 251, 86, 30));
        jButton1.setText("確定");
        jButton1.addActionListener(new cjframe_jButton1_actionAdapter(this));

        this.add(jLabel2);
        this.add(jLabel3);
        this.add(jLabel4);
        this.add(jComboBox1);
        this.add(jComboBox2);
        this.add(jComboBox3);
        this.add(jLabel5);
        this.add(jLabel1);
        this.add(jScrollPane1);
        this.add(jButton1);
        jScrollPane1.getViewport().add(jPanel1);
        csh();



    }
    private void pcsh(){

        try {
           Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); //驅動加載
       } catch (ClassNotFoundException ex1) {
       }
       try {

           if( jlab != null){
               for(int i = 0; i < jlab.length; i++)
               {
                   jPanel1.remove(jlab[i]);
                   jPanel1.remove(jtext[i]);
                   jPanel1.setVisible(true);
                   jPanel1.repaint();
               }
               jlab = null;
               jtext=null;
           }

           i=0;
           Connection con3 = DriverManager.getConnection("jdbc:odbc:glxt"); //數據橋連名子為ＯＤＢＣ名為glxt;
           String sql3="select count(*) from kctable where grade_id=(select Grade_id from gradetable where Grade_name=?)";
           PreparedStatement pre3 = con3.prepareStatement(sql3);
           pre3.setString(1,String.valueOf(jComboBox1.getSelectedItem()));
           ResultSet result3 = pre3.executeQuery();
           if(result3.next()){
               i=result3.getInt(1);
           }
           jlab=new JLabel[i];
           jtext=new JTextField[i];

          for(int j=0;j<i;j++){
              jlab[j]=new JLabel();
              jtext[j]=new JTextField();
          }

          result3.close();
          pre3.close();
          System.out.println(i);

          String sql4 = "select kc_name from kctable where grade_id=(select Grade_id from gradetable where Grade_name=?)";
          PreparedStatement pre4 = con3.prepareStatement(sql4);
          pre4.setString(1, String.valueOf(jComboBox1.getSelectedItem()));
          ResultSet result4 = pre4.executeQuery();
           i=0;
            while (result4.next()) {
                String temp1=result4.getString(1);
                System.out.println("temp1-->"+temp1);
                jlab[i].setText(temp1);
                jlab[i].setBounds(10,(i+1)*20,100,20);
                jtext[i].setText("");
                jtext[i].setBounds(100,(i+1)*20,200,20);
                jPanel1.add(jlab[i]);
                jPanel1.add(jtext[i]);
                i++;
            }
            jPanel1.setVisible(true);
            jPanel1.repaint();
            result4.close();
            pre4.close();
            con3.close();
       } catch (SQLException ex) {
           ex.printStackTrace();
       }



    }
private void csh(){
try {
    Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); //驅動加載
} catch (ClassNotFoundException ex1) {
}
    try {
        con = DriverManager.getConnection("jdbc:odbc:glxt"); //數據橋連名子為ＯＤＢＣ名為glxt;
        sql = "select Grade_name from gradetable"; //查詢班級表中的班名
        pre = con.prepareStatement(sql);
        result = pre.executeQuery();
        jComboBox1.removeAllItems();
        jComboBox1.addItem("");

        while(result.next()){
            jComboBox1.addItem(result.getString(1));
        }
        result.close();
        pre.close();
        con.close();
    } catch (SQLException ex) {
        ex.printStackTrace();
    }

}
String sql="";
PreparedStatement pre=null;
ResultSet result=null;
Connection con=null;
int i;
int xsid=0;

    JLabel jLabel1 = new JLabel();
    TitledBorder titledBorder1 = new TitledBorder("");
    JLabel jLabel2 = new JLabel();
    JLabel jLabel3 = new JLabel();
    JLabel jLabel4 = new JLabel();
    JComboBox jComboBox1 = new JComboBox();
    JComboBox jComboBox2 = new JComboBox();
    JComboBox jComboBox3 = new JComboBox();
    JLabel jLabel5 = new JLabel();
    JScrollPane jScrollPane1 = new JScrollPane();
    JPanel jPanel1 = new JPanel();
    JLabel[] jlab;
    JTextField[] jtext;
    JButton jButton1 = new JButton();
    public void jComboBox1_actionPerformed(ActionEvent e) {

  try {
    try {
        Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); //驅動加載
    } catch (ClassNotFoundException ex1) {
    }
      String temp=String.valueOf(jComboBox1.getSelectedItem());
      Connection con1 = DriverManager.getConnection("jdbc:odbc:glxt"); //數據橋連名子為ＯＤＢＣ名為glxt;
      String sql1 = "select class_name from classtable where Grade_id=(select grade_id from gradetable where Grade_name=?)"; //查詢班級表中的班名
      PreparedStatement  pre1 = con1.prepareStatement(sql1);
      pre1.setString(1,temp);
      ResultSet result1 = pre1.executeQuery();
      jComboBox2.removeAllItems();
      while(result1.next()){
          jComboBox2.addItem(result1.getString(1));
      }
      result1.close();
      pre1.close();
      con1.close();
  } catch (SQLException ex) {
      ex.printStackTrace();
  }

pcsh();
    }

    public void jComboBox2_actionPerformed(ActionEvent e) {

        sql = "select stu_name from stutable where class_id=(select class_id from classtable where class_name=?)";
        try {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); //驅動加載
            Connection con2 = DriverManager.getConnection("jdbc:odbc:glxt"); //數據橋連名子為ＯＤＢＣ名為glxt;;
            PreparedStatement pre2=con2.prepareStatement(sql);
            pre2.setString(1,String.valueOf(jComboBox2.getSelectedItem()));
            ResultSet result2=pre2.executeQuery();
            jComboBox3.removeAllItems();
            jComboBox3.addItem("");
            while(result2.next()){
                jComboBox3.addItem(result2.getString(1));
            }
            result2.close();
            pre2.close();
            con2.close();





        } catch (ClassNotFoundException ex1) {
            ex1.printStackTrace();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }






    }

    public void jComboBox3_actionPerformed(ActionEvent e) {

        sql = "select stu_id,stu_xh from stutable where stu_name=?";
        try {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); //驅動加載
            Connection con3 = DriverManager.getConnection("jdbc:odbc:glxt"); //數據橋連名子為ＯＤＢＣ名為glxt;;
            PreparedStatement pre3 = con3.prepareStatement(sql);
            pre3.setString(1, String.valueOf(jComboBox3.getSelectedItem()));
            ResultSet result3 = pre3.executeQuery();
             while (result3.next()) {
                 xsid=result3.getInt(1);


                jLabel5.setText("學號為："+result3.getString(2));
            }
            result3.close();
            pre3.close();
            con3.close();



        } catch (ClassNotFoundException ex1) {
            ex1.printStackTrace();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }



    }

    public void jButton1_actionPerformed(ActionEvent e) {
        if(jComboBox1.getSelectedItem().equals("")){
            JOptionPane.showMessageDialog(null, "請先選擇班級！");

        }else if(jComboBox3.getSelectedItem().equals("")){
            JOptionPane.showMessageDialog(null, "選擇學生！");
        }else{
            for (int j = 0; j < i; j++) {
                if (jtext[j].getText().equals("")) {
                    JOptionPane.showMessageDialog(null, "成績有空值！");
                    return;
                } else if (Float.parseFloat(jtext[j].getText()) < 0 ||
                           Float.parseFloat(jtext[j].getText()) > 100) {
                    JOptionPane.showMessageDialog(null, "成績輸入錯誤！");
                    return;
                }
            }
            {
                try {
                    Connection con6 = DriverManager.getConnection(
                            "jdbc:odbc:glxt");
                    String sql6="select kc_id from kctable where grade_id=(select Grade_id from gradetable where Grade_name=?)";
                    PreparedStatement pre6=con6.prepareStatement(sql6);
                    pre6.setString(1,String.valueOf(jComboBox1.getSelectedItem()));
                    ResultSet result6=pre6.executeQuery();
                    while(result6.next()){
                        int cjid=result6.getInt(1);
                        Connection con7 = DriverManager.getConnection(
                            "jdbc:odbc:glxt");
                        String sql7 = "select cj_id from cjtable where stu_id=(select stu_id from stutable where stu_name=?) and kc_id=?";
                        PreparedStatement pre7 = con7.prepareStatement(sql7);
                       pre7.setString(1,String.valueOf(jComboBox3.getSelectedItem()));
                        pre7.setInt(2,cjid);
                        ResultSet result7 = pre7.executeQuery();
                        Connection con8=null;
                        PreparedStatement pre8=null;
                        int i3=0;
                        if(result7.next()){
                            sql="update cjtable set cj="+jtext[i3].getText()+" where cj_id= "+String.valueOf(result7.getInt(1));
                            con8 = DriverManager.getConnection("jdbc:odbc:glxt");
                            System.out.println(sql);
                            pre8 = con8.prepareStatement(sql);

                            pre8.executeUpdate();

                            pre8.close();
                            con8.close();
                            i3++;
                        }else{
                            sql="insert into cjtable(stu_id,kc_id,cj) values(?,?,?)";
                            con8 = DriverManager.getConnection("jdbc:odbc:glxt");
                            pre8=con8.prepareStatement(sql);
                            pre8.setInt(1,xsid);
                            pre8.setInt(2,cjid);
                            pre8.setFloat(3,Float.parseFloat(jtext[i3].getText()));
                            pre8.executeUpdate();
                            i3++;
                            pre8.close();
                            con8.close();
                        }


                        result7.close();
                        pre7.close();




                    }
                    JOptionPane.showMessageDialog(null, "成績修改成功！");
                    result6.close();
                    pre6.close();
                    con.close();



                } catch (SQLException ex) {
                    ex.printStackTrace();
                }




            }
        }


    }
}


class cjframe_jButton1_actionAdapter implements ActionListener {
    private cjframe adaptee;
    cjframe_jButton1_actionAdapter(cjframe adaptee) {
        this.adaptee = adaptee;
    }

    public void actionPerformed(ActionEvent e) {
        adaptee.jButton1_actionPerformed(e);
    }
}


class cjframe_jComboBox3_actionAdapter implements ActionListener {
    private cjframe adaptee;
    cjframe_jComboBox3_actionAdapter(cjframe adaptee) {
        this.adaptee = adaptee;
    }

    public void actionPerformed(ActionEvent e) {
        adaptee.jComboBox3_actionPerformed(e);
    }
}


class cjframe_jComboBox2_actionAdapter implements ActionListener {
    private cjframe adaptee;
    cjframe_jComboBox2_actionAdapter(cjframe adaptee) {
        this.adaptee = adaptee;
    }

    public void actionPerformed(ActionEvent e) {
        adaptee.jComboBox2_actionPerformed(e);
    }
}


class cjframe_jComboBox1_actionAdapter implements ActionListener {
    private cjframe adaptee;
    cjframe_jComboBox1_actionAdapter(cjframe adaptee) {
        this.adaptee = adaptee;
    }

    public void actionPerformed(ActionEvent e) {
        adaptee.jComboBox1_actionPerformed(e);
    }
}