fexammanage.java~77~

中小学校园的教师

package untitled2;

import javax.swing.*;
import java.awt.*;
import com.borland.jbcl.layout.*;
import java.awt.event.*;
import java.sql.SQLException;
import java.sql.DriverManager;
import java.sql.Connection;
import com.borland.dbswing.*;
import Digital_Campus_DataModule.*;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import javax.swing.border.*;
import java.util.Random;


/**
 * <p>Title: </p>
 * <p>Description: </p>
 * <p>Copyright: Copyright (c) 2006</p>
 * <p>Company: </p>
 * @author not attributable
 * @version 1.0
 */


public class FExamManage extends JFrame {
  XYLayout xYLayout1 = new XYLayout();
  JPanel jPExamRoom = new JPanel();
  JLabel jLTerm = new JLabel();
  JLabel jLExamType = new JLabel();
  TableScrollPane tSPExamRoom = new TableScrollPane();
  JdbTable jdbTExamRoom = new JdbTable();
  JButton jBCourseAdd = new JButton();
  JComboBox jCBTerm = new JComboBox();
  JPanel jPExamSite = new JPanel();
  TitledBorder titledBorder1;
  TitledBorder titledBorder2;
  TitledBorder titledBorder3;
  JComboBox jCBGrade = new JComboBox();
  JLabel jLGrade = new JLabel();
  JdbComboBox jdbExamType = new JdbComboBox();
  DB_DataModule dB_DataModule_Exam;
  JPanel jPExam = new JPanel();
  TitledBorder titledBorder4;
  JdbTable jdbTExam = new JdbTable();
  TableScrollPane tSPExam = new TableScrollPane();
  XYLayout xYLayout2 = new XYLayout();
  class Course{
      String Course_Name;
    }
    class Classes{
      int Class_Id;
      String Class_Name;
      int Class_Size;

    }


  public FExamManage() {
    try {
      jbInit();
    }
    catch(Exception ex) {
      ex.printStackTrace();
    }
  }

  void jbInit() throws Exception {
    titledBorder1 = new TitledBorder("");
    titledBorder2 = new TitledBorder("");
    titledBorder3 = new TitledBorder(BorderFactory.createEtchedBorder(Color.white,new Color(148, 145, 140)),"考场安排情况");
    dB_DataModule_Exam = Digital_Campus_DataModule.DB_DataModule.getDataModule();
    titledBorder4 = new TitledBorder(BorderFactory.createEtchedBorder(Color.white,new Color(148, 145, 140)),"考场信息");
    jPExamRoom.setLayout(null);
    jPExamRoom.setAutoscrolls(false);
    jPExamRoom.setBorder(titledBorder3);
    this.getContentPane().setLayout(xYLayout1);
    this.setResizable(false);
    this.setSize(new Dimension(800, 600));
    this.setTitle("考试管理");
    this.addWindowListener(new FExamManage_this_windowAdapter(this));
    jLTerm.setFont(new java.awt.Font("Dialog", 0, 14));
    jLTerm.setText("学期：");
    jLTerm.setBounds(new Rectangle(72, 25, 45, 20));
    jLExamType.setFont(new java.awt.Font("Dialog", 0, 14));
    jLExamType.setText("类型：");
    jLExamType.setBounds(new Rectangle(287, 26, 60, 18));
    tSPExamRoom.setBounds(new Rectangle(17, 26, 755, 150));
    jdbTExamRoom.setDataSet(dB_DataModule_Exam.getQDSCourse());
    jBCourseAdd.setBounds(new Rectangle(618, 25, 94, 21));
    jBCourseAdd.setText("生成考场");
    jBCourseAdd.addActionListener(new FExamManage_jBCourseAdd_actionAdapter(this));

    jCBTerm.setBounds(new Rectangle(108, 24, 143, 22));
    jPExamSite.setLayout(null);
    jPExamSite.setFont(new java.awt.Font("Dialog", 0, 13));
    jPExamSite.setBorder(titledBorder3);
    jPExamSite.setDebugGraphicsOptions(0);

    jCBGrade.setBounds(new Rectangle(497, 24, 82, 22));
    jLGrade.setBounds(new Rectangle(455, 26, 60, 18));
    jLGrade.setText("年级：");
    jLGrade.setFont(new java.awt.Font("Dialog", 0, 14));
    jdbExamType.setItems(new String[] {"期中考试 ", "期末考试", "小考", "中考", "高考"});
    jdbExamType.setBounds(new Rectangle(330, 24, 90, 22));
    jPExam.setFont(new java.awt.Font("Dialog", 0, 13));
    jPExam.setBorder(titledBorder4);
    jPExam.setLayout(xYLayout2);
    jdbTExam.setDataSet(dB_DataModule_Exam.getQDSCourse());
    this.getContentPane().add(jPExamSite,   new XYConstraints(6, 5, 783, 65));
    this.getContentPane().add(jPExamRoom,     new XYConstraints(8, 80, 785, 186));
    jPExamRoom.add(tSPExamRoom, null);
    tSPExamRoom.add(jdbTExamRoom, null);
    jPExamSite.add(jLExamType, null);
    jPExamSite.add(jBCourseAdd, null);
    jPExamSite.add(jCBGrade, null);
    jPExamSite.add(jLGrade, null);
    jPExamSite.add(jdbExamType, null);
    jPExamSite.add(jLTerm, null);
    jPExamSite.add(jCBTerm, null);
    this.getContentPane().add(jPExam,    new XYConstraints(10, 274, 779, 316));
    jPExam.add(tSPExam,           new XYConstraints(7, 0, -1, 284));
    tSPExam.add(jdbTExam, null);
  }

  void this_windowActivated(WindowEvent e) {
    String sql = "SELECT * FROM term ";

    Connection con = null;
    try{
      con = DBConnection.getConnection();
      PreparedStatement ps = con.prepareStatement(sql);
      ResultSet rs = ps.executeQuery();
      while(rs.next())
      {
        jCBTerm.addItem(rs.getString("term_id"));
      }
      sql = "SELECT * FROM grade";
      ps = con.prepareStatement(sql);
      rs = ps.executeQuery();
      while(rs.next())
      {
        jCBGrade.addItem(rs.getString("grade_name"));
      }

      DbUtil.close(rs);
      DbUtil.close(ps);
    }catch(Exception ee){
      ee.printStackTrace();
    }finally{
      DbUtil.close(con);
    }

  }

  void jBCourseAdd_actionPerformed(ActionEvent e) {
    int i,j,k,CoursesCount=0,ClasssesCount=0;
    Connection conCourses = null;
    Connection conCoursesCount = null;
    String Course_Name[]=null;
    String Classes_Info[][]=null;
    //读出所有课程
    String sqlCourses = "SELECT * "+
        "FROM course "+
        "WHERE (course_name like '%"+jCBGrade.getSelectedItem().toString()+"1%') "+
        " AND (course_term = '"+jCBTerm.getSelectedItem().toString()+"')";
    try{
      conCourses = DBConnection.getConnection();
      PreparedStatement psCourses = conCourses.prepareStatement(sqlCourses);
      System.out.print(sqlCourses) ;

      ResultSet rsCourses = psCourses.executeQuery();
      //课程总数
      String sqlCoursesCount = "SELECT COUNT(*) AS course_count "+
        "FROM course "+
        "WHERE (course_name like '%"+jCBGrade.getSelectedItem().toString()+"1%') "+
        " AND (course_term = '"+jCBTerm.getSelectedItem().toString()+"')";
    conCoursesCount = DBConnection.getConnection();

    PreparedStatement psCoursesCount = conCoursesCount.prepareStatement(sqlCoursesCount);
    System.out.print(sqlCoursesCount) ;
    ResultSet rsCoursesCount = psCoursesCount.executeQuery();
      if(rsCoursesCount.next()){
        CoursesCount=rsCoursesCount.getInt("course_count");
        System.out.print(CoursesCount+"\n") ;
        Course_Name = new  String[CoursesCount];
        for(i=0;i<CoursesCount;i++){
          if(rsCourses.next()){
            //Strcpy(Course_Info[i].Course_Name,String.valueOf(rsCourses.getString("course_name")));
            System.out.print(sqlCourses+"\n") ;
            //System.out.print(rsCourses.getString("course_name")) ;
            String Str_course_name=rsCourses.getString("course_name").toString() ;
            System.out.print(Str_course_name+"\n");
            Course_Name[i] = Str_course_name;

          }
        }
      }
      DbUtil.close(rsCourses);
      DbUtil.close(psCourses);
      DbUtil.close(rsCoursesCount);
      DbUtil.close(psCoursesCount);
    }catch(Exception ee){
      ee.printStackTrace();
    }finally{
      DbUtil.close(conCourses);
      DbUtil.close(conCoursesCount);
    }
  //-----------------------------------------------------------------------------//

    //读出所有班级
    Connection conClasses = null;
    Connection conClassesCount = null;
    Connection conClassesSize = null;

    String sqlClasses = "SELECT * "+
        "FROM class "+
        "WHERE (grade_name = '"+jCBGrade.getSelectedItem().toString()+"')";
    try{
      conClasses = DBConnection.getConnection();
      PreparedStatement psClasses = conClasses.prepareStatement(sqlClasses);
      System.out.print(sqlClasses+"\n") ;
      System.out.print(jCBGrade.getSelectedItem().toString()) ;

      ResultSet rsClasses = psClasses.executeQuery();
      //班级总数
      String sqlClassesCount = "SELECT COUNT(*) AS classes_count "+
        "FROM class "+
        "WHERE (grade_name = '"+jCBGrade.getSelectedItem().toString()+"')";
    conClassesCount = DBConnection.getConnection();
    PreparedStatement psClassesCount = conClassesCount.prepareStatement(sqlClassesCount);
    System.out.print(sqlClassesCount+"\n") ;
    System.out.print(jCBGrade.getSelectedItem().toString()+"\n") ;
    ResultSet rsClassesCount = psClassesCount.executeQuery();
      if(rsClassesCount.next()){
        ClasssesCount=rsClassesCount.getInt("classes_count");
        Classes_Info = new  String[ClasssesCount][3];
        for(i=0;i<ClasssesCount;i++){
          if(rsClasses.next()){
            //读出班级号
            Classes_Info[i][0] =rsClasses.getString("class_id");
            //读出班级名
            String Str_Class_Name;
            Str_Class_Name=rsClasses.getString("class_name");
            Classes_Info[i][1] =Str_Class_Name;
            //读出班级大小
            String sqlClassesSize = "SELECT COUNT(*) AS class_size "+
                "FROM student INNER JOIN "+
                "class ON student.class_id = class.class_id "+
                    "WHERE (class.class_name = '"+Str_Class_Name+"')";
                System.out.print(sqlClassesSize+"\n") ;
                conClassesSize = DBConnection.getConnection();
                PreparedStatement psClassesSize = conClassesSize.prepareStatement(sqlClassesSize);
                ResultSet rsClassesSize = psClassesSize.executeQuery();
                if(rsClassesSize.next() ){
                  Classes_Info[i][2]  =rsClassesSize.getString("class_size");
                }
                DbUtil.close(rsClassesSize);
                DbUtil.close(psClassesSize);

            //---------------------------
          }
        }
      }
      DbUtil.close(rsClasses);
      DbUtil.close(psClasses);
      DbUtil.close(rsClassesCount);
      DbUtil.close(psClassesCount);
    }catch(Exception ee){
      ee.printStackTrace();
    }finally{
      DbUtil.close(conClasses);
      DbUtil.close(conClassesCount);
      DbUtil.close(conClassesSize);

    }


    //////-------------------------------------------------------------
    //安排考场和考试
    for(i=0;i<CoursesCount;i++){
      for(j=0;j<ClasssesCount;j++){
        //添加考场
        Connection con = null;
        try{
          String sql = "INSERT INTO exam_room "+
              "(classroom_name, term_id, exam_name, exam_type) "+
              "VALUES ('"+Classes_Info[j][1]+"', '"+jCBTerm.getSelectedItem().toString() +"', '"+jCBTerm.getSelectedItem().toString()+Course_Name[i]+"', '"+jdbExamType.getSelectedItem().toString()+"')";
          con = DBConnection.getConnection();
          PreparedStatement ps = con.prepareStatement(sql);
          System.out.print(sql+"\n") ;
          ps.executeUpdate();
          DbUtil.close(ps);
        }catch(Exception ee){
          ee.printStackTrace();
        }finally{
          DbUtil.close(con);
        }
      }
      //安排考试
      //-------------------------------------
      //选出该年级的所有学生
      Connection conStudent = null;

      try{
        String sqlStudent = "SELECT * "+
            "FROM student INNER JOIN "+
            "class ON student.class_id = class.class_id INNER JOIN "+
            "grade ON class.grade_name = grade.grade_name "+
            "WHERE (grade.grade_name = '"+jCBGrade.getSelectedItem().toString()+"') ";
        conStudent = DBConnection.getConnection();
        PreparedStatement psStudent = conStudent.prepareStatement(sqlStudent);
        System.out.print(sqlStudent+"\n");
        ResultSet rsStudent = psStudent.executeQuery();
        while(rsStudent.next()){
          int Classno=0,Seatno=0;
          Random ClassnoRandom=new Random();
          Classno=ClassnoRandom.nextInt(ClasssesCount);
          Seatno=ClassnoRandom.nextInt(Integer.valueOf(Classes_Info[Classno][2]).intValue()+1);
          //System.out.print(ClasssesCount+"\n");
          System.out.print(Classno+"\n");
          //System.out.print(Integer.valueOf(Classes_Info[Classno][2]).intValue() +"\n");
          System.out.print(Seatno+"\n");

          //查询是否已有该座位

          Connection conSeatno = null;
          try{
            System.out.print(String.valueOf(i) +"\n");
            String sqlSeatno = "SELECT exam.* "+
                "FROM exam "+
                "WHERE (exam_class = '"+Classes_Info[Classno][1]+"') AND (exam_seat_no = '"+Seatno+"') AND (exam_name = '"+jCBTerm.getSelectedItem().toString()+Course_Name[i]+"')";
            conSeatno = DBConnection.getConnection();
            PreparedStatement psSeatno = conSeatno.prepareStatement(sqlSeatno);
            System.out.print(sqlSeatno+"\n");
            ResultSet rsSeatno = psSeatno.executeQuery();
            if(rsSeatno.next()){
              rsStudent.previous();
              DbUtil.close(conSeatno);
              continue;
            }
            DbUtil.close(rsSeatno);
            DbUtil.close(psSeatno);
          }catch(Exception ee){
           ee.printStackTrace();
          }finally{
            DbUtil.close(conSeatno);
          }

          //-----------------


          //可以写入考场信息
          Connection conExam = null;
          try{
            String sqlExam = "INSERT INTO exam "+
                "(card_id, exam_name, exam_class, exam_seat_no) "+
                "VALUES ('"+rsStudent.getString("card_id") +"', '"+jCBTerm.getSelectedItem().toString()+Course_Name[i]+"', '"+Classes_Info[Classno][1]+"', '"+String.valueOf(Seatno)+"')";
            conExam = DBConnection.getConnection();
            PreparedStatement psExam = conExam.prepareStatement(sqlExam);
            psExam.executeUpdate();
            DbUtil.close(psExam);
          }catch(Exception ee){
            ee.printStackTrace();
          }finally{
            DbUtil.close(conExam);
          }
        }
        DbUtil.close(rsStudent);
        DbUtil.close(psStudent);
      }catch(Exception ee){
        ee.printStackTrace();
      }finally{
        DbUtil.close(conStudent);
      }
    }


  }
}

class FExamManage_this_windowAdapter extends java.awt.event.WindowAdapter {
  FExamManage adaptee;

  FExamManage_this_windowAdapter(FExamManage adaptee) {
    this.adaptee = adaptee;
  }
  public void windowActivated(WindowEvent e) {
    adaptee.this_windowActivated(e);
  }
}

class FExamManage_jBCourseAdd_actionAdapter implements java.awt.event.ActionListener {
  FExamManage adaptee;

  FExamManage_jBCourseAdd_actionAdapter(FExamManage adaptee) {
    this.adaptee = adaptee;
  }
  public void actionPerformed(ActionEvent e) {
    adaptee.jBCourseAdd_actionPerformed(e);
  }
}