package xxglxt;

import java.awt.BorderLayout;

import javax.swing.JPanel;
import javax.swing.JList;
import java.awt.Rectangle;
import javax.swing.border.TitledBorder;
import java.awt.Color;
import javax.swing.JButton;
import javax.swing.JLabel;
import javax.swing.JComboBox;
import javax.swing.JTextArea;
import javax.swing.JTextField;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.DriverManager;
import java.sql.*;
import javax.swing.ListSelectionModel;
import javax.swing.JScrollPane;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import javax.swing.JDialog;
import javax.swing.JFrame;
import java.awt.Container;
import java.awt.FlowLayout;
import javax.swing.JOptionPane;
import javax.swing.DefaultListModel;

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

    private void jbInit() throws Exception {

        this.setLayout(null);
        jButton1.setBounds(new Rectangle(21, 251, 77, 30));
        jButton1.setText("增加");
        jButton1.addActionListener(new KcFrame_jButton1_actionAdapter(this));
        jButton2.setBounds(new Rectangle(110, 252, 77, 28));
        jButton2.setText("修改");
        jButton2.addActionListener(new KcFrame_jButton2_actionAdapter(this));
        jButton3.setBounds(new Rectangle(208, 251, 76, 27));
        jButton3.setText("刪除");
        jButton3.addActionListener(new KcFrame_jButton3_actionAdapter(this));
        jLabel1.setText("現有課程");
        jLabel1.setBounds(new Rectangle(20, 8, 135, 31));
        jLabel2.setText("應用年級：");
        jLabel2.setBounds(new Rectangle(325, 53, 91, 31));
        jLabel3.setText("使用書籍：");
        jLabel3.setBounds(new Rectangle(325, 88, 87, 31));
        jButton4.setBounds(new Rectangle(393, 137, 67, 25));
        jButton4.setText("確定");
        jButton4.addActionListener(new KcFrame_jButton4_actionAdapter(this));
        jComboBox1.setBounds(new Rectangle(408, 53, 105, 24));
        jTextField1.setBounds(new Rectangle(408, 92, 104, 24));
        jLabel4.setBorder(titledBorder1);
        jLabel4.setText("本頁是對課程的增加與修改");
        jLabel4.setBounds(new Rectangle(308, 188, 236, 91));
        jScrollPane1.setBounds(new Rectangle(12, 35, 293, 213));
        this.add(jButton1);
        this.add(jButton3);
        this.add(jLabel1);
        this.add(jLabel2);
        this.add(jLabel3);
        this.add(jComboBox1);
        this.add(jTextField1);
        this.add(jLabel4);
        this.add(jScrollPane1);
        this.add(jButton4);
        this.add(jButton2);
        csh();
        bjh();

    }
    private void bjh(){
        new sun.jdbc.odbc.JdbcOdbcDriver();
   try {
       con = DriverManager.getConnection("jdbc:odbc:glxt"); //數據橋連名子為ＯＤＢＣ名為glxt
       sql="select * from gradetable";
       pre = con.prepareStatement(sql);
       result = pre.executeQuery();
       while(result.next()){
           jComboBox1.addItem(result.getString(2));
       }
       result.close();
       pre.close();



       result.close();
       pre.close();

   } catch (SQLException ex) {
   }





    }

private void csh(){
//    System.out.println("csh");
    if( jList1 != null){
           jList1 = null;
    }
    new sun.jdbc.odbc.JdbcOdbcDriver();
    try {
        con = DriverManager.getConnection("jdbc:odbc:glxt"); //數據橋連名子為ＯＤＢＣ名為glxt
        sql="select count(*) from kctable";
        pre = con.prepareStatement(sql);
        result = pre.executeQuery();
        if(result.next()){
            inttemp=result.getInt(1);
        }
        result.close();
        pre.close();

        strtemp=new String[inttemp];
        kcname=new String[inttemp];
        njid=new int[inttemp];
        njname=new String[inttemp];
        bookname=new String[inttemp];
        kcid=new int[inttemp];
        gradename=new String[inttemp];

        inttemp=0;
        sql = "select kc_name,kctable.grade_id,kc_book,kc_id,grade_name from kctable left  outer join gradetable on kctable.grade_id=gradetable.Grade_id";
        pre = con.prepareStatement(sql);
        result = pre.executeQuery();

        while(result.next()){
            kcname[inttemp]=result.getString(1);
            njid[inttemp]=result.getInt(2);
            bookname[inttemp]=result.getString(3);
            kcid[inttemp]=result.getInt(4);
            gradename[inttemp]=result.getString(5);
            strtemp[inttemp]="課程名為"+kcname[inttemp].trim()+"，使用年級為"+gradename[inttemp]+"，使用書本為"+bookname[inttemp];
            inttemp++;
        }

        if( dlmtemp != null){
            dlmtemp.removeAllElements();
        }

        for( int i = 0; i < strtemp.length; i++){
            dlmtemp.addElement(strtemp[i]);
        }

        jList1 = new JList(dlmtemp);
        jList1.setBackground(Color.pink);
        jList1.setBorder(titledBorder1);
        jList1.setSelectionMode(ListSelectionModel.SINGLE_SELECTION);
        jScrollPane1.getViewport().add(jList1);

        result.close();
        pre.close();

    } catch (SQLException ex) {
    }




}

public void deleteList(){
    int index = jList1.getSelectedIndex();
    dlmtemp.removeElementAt(index);
}

public void  updateListKcName(String newName){
    int index = jList1.getSelectedIndex();
    kcname[index] = newName;
    strtemp[index] = "課程名為"+kcname[index].trim()+"，使用年級為"+gradename[index]+"，使用書本為"+bookname[index];
    dlmtemp.setElementAt(strtemp[index],index);
}

public void updateListGradename(){
    int index = jList1.getSelectedIndex();
    gradename[index] = (String) jComboBox1.getSelectedItem();
    bookname[index] = jTextField1.getText();
    strtemp[index] = "課程名為"+kcname[index].trim()+"，使用年級為"+gradename[index]+"，使用書本為"+bookname[index];
    dlmtemp.setElementAt(strtemp[index],index);
}

    JList jList1 ;
    String[] kcname;
    String[] njname;
    int[] njid;
    String[] bookname;
    int[] kcid;
    String[]gradename;
    DefaultListModel dlmtemp = new DefaultListModel();

    TitledBorder titledBorder1 = new TitledBorder("");
    JButton jButton1 = new JButton();
    JButton jButton2 = new JButton();
    JButton jButton3 = new JButton();
    JLabel jLabel1 = new JLabel();
    JLabel jLabel2 = new JLabel();
    JLabel jLabel3 = new JLabel();
    JButton jButton4 = new JButton();
    JComboBox jComboBox1 = new JComboBox();
    JTextField jTextField1 = new JTextField();
    JLabel jLabel4 = new JLabel();
    Connection con=null;
    PreparedStatement pre=null;
    ResultSet result=null;
    String sql="";
    String [] strtemp;
    int inttemp=0;
    JScrollPane jScrollPane1 = new JScrollPane();
    public void jButton1_actionPerformed(ActionEvent e) {

       String inputValue = JOptionPane.showInputDialog("輸入新課程名稱：");

        try {
            con = DriverManager.getConnection("jdbc:odbc:glxt"); //數據橋連名子為ＯＤＢＣ名為glxt

            sql = "insert into kctable(kc_name) values(?)";
            pre = con.prepareStatement(sql);
            pre.setString(1,inputValue);
            JOptionPane.showMessageDialog(null,"增加成功");
            pre.executeUpdate();
            pre.close();


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

        initInfo();
//        try {
//            jbInit();
//        } catch (Exception ex1) {
//        }




    }

    public void initInfo(){
        System.out.println("initInfo");
        csh();
    }

    public void jButton2_actionPerformed(ActionEvent e) {

        if(jList1.getSelectedIndex()==-1){
            JOptionPane.showMessageDialog(null,"請先選擇記錄");
        }else{
            inttemp=jList1.getSelectedIndex();
             String inputValue = JOptionPane.showInputDialog("更改後的課程名：");
             System.out.println(inttemp);
             System.out.println(inputValue);
             System.out.println(kcid[inttemp]);
            try {
                con = DriverManager.getConnection("jdbc:odbc:glxt"); //數據橋連名子為ＯＤＢＣ名為glxt
                sql = "update kctable set kc_name=? where kc_id=?";
                pre = con.prepareStatement(sql);
                pre.setString(1,inputValue);
                pre.setInt(2, kcid[inttemp]);
                pre.executeUpdate();
                JOptionPane.showMessageDialog(null,"課程名稱修改成功！");
                pre.close();
                con.close();

            } catch (SQLException ex) {
            }
            updateListKcName(inputValue);



        }


    }

    public void jButton3_actionPerformed(ActionEvent e) {
        if(jList1.getSelectedIndex()==-1){
                   JOptionPane.showMessageDialog(null,"請先選擇記錄");
               }else{
                   inttemp=jList1.getSelectedIndex();

                   try {
                       con = DriverManager.getConnection("jdbc:odbc:glxt"); //數據橋連名子為ＯＤＢＣ名為glxt
                       sql = "delete from kctable where kc_id=?";
                       pre = con.prepareStatement(sql);
                       pre.setInt(1, kcid[inttemp]);
                       pre.executeUpdate();
                       JOptionPane.showMessageDialog(null,"課程成功刪除！");
                       pre.close();
                       con.close();

                   } catch (SQLException ex) {
                   }

                   deleteList();


        }

    }



    public void jButton4_actionPerformed(ActionEvent e) {

        if (jList1.getSelectedIndex() == -1) {
            JOptionPane.showMessageDialog(null, "請先選擇記錄");
        }else{
            inttemp=jList1.getSelectedIndex();
            try {
                int inttemp2=0;
                con = DriverManager.getConnection("jdbc:odbc:glxt"); //數據橋連名子為ＯＤＢＣ名為glxt
                sql="update kctable set grade_id=?,kc_book=? where kc_id=?";
                String sql2="select grade_id from gradetable where Grade_name=?";

               pre = con.prepareStatement(sql2);
               pre.setString(1,String.valueOf(jComboBox1.getSelectedItem()));

               result=pre.executeQuery();
               if(result.next()){
                   inttemp2=result.getInt(1);
               }
               System.out.println(inttemp2);
               result.close();
               pre.close();



               pre = con.prepareStatement(sql);
               pre.setInt(1,inttemp2);
               pre.setString(2,jTextField1.getText());
               pre.setInt(3,kcid[inttemp]);




                pre.executeUpdate();

               JOptionPane.showMessageDialog(null, "修改成功");
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            updateListGradename();
        }





    }
}


class KcFrame_jButton4_actionAdapter implements ActionListener {
    private KcFrame adaptee;
    KcFrame_jButton4_actionAdapter(KcFrame adaptee) {
        this.adaptee = adaptee;
    }

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


class KcFrame_jButton3_actionAdapter implements ActionListener {
    private KcFrame adaptee;
    KcFrame_jButton3_actionAdapter(KcFrame adaptee) {
        this.adaptee = adaptee;
    }

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


class KcFrame_jButton2_actionAdapter implements ActionListener {
    private KcFrame adaptee;
    KcFrame_jButton2_actionAdapter(KcFrame adaptee) {
        this.adaptee = adaptee;
    }

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


class KcFrame_jButton1_actionAdapter implements ActionListener {
    private KcFrame adaptee;
    KcFrame_jButton1_actionAdapter(KcFrame adaptee) {
        this.adaptee = adaptee;
    }

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