frmtotalclassroom.frm

学校排课管理系统 科目的查询

VERSION 5.00
Begin VB.Form frmtotalclassroom 
   Caption         =   "合班课的排法"
   ClientHeight    =   3195
   ClientLeft      =   60
   ClientTop       =   345
   ClientWidth     =   4680
   LinkTopic       =   "Form2"
   ScaleHeight     =   3195
   ScaleWidth      =   4680
   StartUpPosition =   3  '窗口缺省
End
Attribute VB_Name = "frmtotalclassroom"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Option Explicit

Dim db As DAO.Database '用来描述所有表的库
Dim rstmajor As DAO.Recordset '基本课表记录集
Dim rstplan As DAO.Recordset    '基本计划记录集
Dim rstmajorcourse As DAO.Recordset '班级课程记录集
Dim rstclassroom As DAO.Recordset '教室基本记录集
Dim rstclassroomcourse As DAO.Recordset '教室课程表记录集
Dim rstteacher As DAO.Recordset '教师基本表
Dim rstteachercourse As DAO.Recordset '教师课程表记录集
Dim rstcourse As DAO.Recordset '课程情况表
Dim rstxbh As DAO.Recordset '系情况表
Dim rstxbhcourse As DAO.Recordset
Dim rsttemp As DAO.Recordset
Option Explicit

Private Sub Form_Load()
Dim sign As String
Dim coursedaytime As Integer
 Dim h As Integer, k As Integer
 Dim daycourse As Integer
 Dim i As Integer, j As Integer '时间变量
 Dim weektotal As Integer '每天平均学时
 Set db = DBEngine.Workspaces(0).OpenDatabase("d:\课表编排系统\basic.mdb") '打开数据库
 Set rstxbh = db.OpenRecordset("select * from xbh") 'rstxbh用来记录系情况表
 rstxbh.MoveFirst
 Do Until rstxbh.EOF
  Set rstplan = db.OpenRecordset("select * from teachplan where coursetype='1'") '选择教学计划中的体育课
  rstplan.Filter = "xbh='" & rstxbh.Fields("xbh") & "'"
  Set rstplan = rstplan.OpenRecordset()
  If rstplan.RecordCount() = 0 Then
   Exit Do
  End If
  Do Until rstplan.EOF
    For i = 1 To 5
      For j = 2 To 4
       Set rstxbhcourse = db.OpenRecordset("select * from coursexbh ") '打开系课程表
       rstxbhcourse.Filter = "xbh='" & rstxbh.Fields("xbh") & "'"
       Set rstxbhcourse = rstxbhcourse.OpenRecordset()
       rstxbhcourse.Filter = "gradeid='" & rstplan.Fields("gradeid") & "'" '选择所要的系和年级的课程表
       Set rstxbhcourse = rstxbhcourse.OpenRecordset()
       If rstxbhcourse.Fields(i & j) = "1" Then '判断系课程表的ij字段是否为1
        Set rstteachercourse = db.OpenRecordset("select * from courseteacher")
         rstteachercourse.Filter = "teacherid='" & rstplan.Fields("teacherid") & "'"
         Set rstteachercourse = rstteachercourse.OpenRecordset() '选择本门课程的教师
        If rstteachercourse.Fields(i & j) = "1" Then
         Set rstclassroomcourse = db.OpenRecordset("select * from courseclassroom where type='t'") '选择所有的大教室
         Do Until rstclassroomcourse.Fields(i & j) = "1"
           rstclassroomcourse.MoveNext
         Loop '寻找一个ij时间空闲的体育声场
         Set rstmajor = db.OpenRecordset("select * from major")
         rstmajor.Filter = "xbh='" & rstxbh.Fields("xbh") & "'"
         Set rstmajor = rstmajor.OpenRecordset()
         rstmajor.Filter = "gradeid='" & rstxbhcourse.Fields("gradeid") & "'"
         Set rstmajor = rstmajor.OpenRecordset() '选择本系、本年级所要包含的所有专业
         
         rstmajor.MoveFirst
         Set rstcourse = db.OpenRecordset("select * from course") '选择所有的课程
         rstcourse.Filter = "courseid='" & rstplan.Fields("courseid") & "'"
         Set rstcourse = rstcourse.OpenRecordset() '选择本记录所对应的课程
         Set rstteacher = db.OpenRecordset("select * from teacher")
         rstteacher.Filter = "teacherid='" & rstplan.Fields("teacherid") & "'"
         Set rstteacher = rstteacher.OpenRecordset() '选择本记录所对应的教师姓名
         Set rstclassroom = db.OpenRecordset("select * from classroom")
         rstclassroom.Filter = "classroomid='" & rstclassroomcourse.Fields("classroomid") & "'"
         Set rstclassroom = rstclassroom.OpenRecordset()
         If Right(rstclassroomcourse.Fields("classroomid"), 1) = "1" Then
            sign = "单周"
         Else
            sign = "双周"
         End If
            
         rstxbhcourse.Edit
         rstxbhcourse.Fields(i & j) = rstcourse.Fields("coursename") & Space(2) & rstteacher.Fields("teachername") & Space(2) & rstclassroom.Fields("classroomname") & Space(2) & sign
         rstxbhcourse.Update
         rstteachercourse.Edit
         rstteachercourse.Fields(i & j) = rstcourse.Fields("coursename") & Space(2) & rstxbh.Fields("xname") & rstplan.Fields("gradeid") & "级" & Space(2) & rstclassroom.Fields("classroomname") & Space(2) & sign
         rstteachercourse.Update
         rstclassroomcourse.Edit
         rstclassroomcourse.Fields(i & j) = rstcourse.Fields("coursename") & Space(2) & rstxbh.Fields("xname") & rstplan.Fields("gradeid") & "级" & Space(2) & rstteacher.Fields("teachername") & Space(2) & sign
         rstclassroomcourse.Update
         Do Until rstmajor.EOF
          Set rstmajorcourse = db.OpenRecordset("select * from coursemajor")
          rstmajorcourse.Filter = "majorid='" & rstmajor.Fields("majorid") & "'"
          Set rstmajorcourse = rstmajorcourse.OpenRecordset()
          rstmajorcourse.Edit
          rstmajorcourse.Fields(i & j) = rstcourse.Fields("coursename") & Space(2) & rstteacher.Fields("teachername") & Space(2) & rstclassroom.Fields("classroomname") & Space(2) & sign
          rstmajorcourse.Update
          rstmajor.MoveNext
         Loop
          rstplan.Delete
             If rstplan.RecordCount() = 0 Then
                Exit Do
             End If
             rstplan.MoveNext
             If rstplan.EOF Then
                rstplan.MoveFirst
             End If
            j = 4
            k = 5
               
          Else
            rstplan.MoveNext
            If rstplan.EOF Then
              rstplan.MoveFirst
            End If
          End If
        Else
          rstplan.MoveNext
          If rstplan.EOF Then
            rstplan.MoveFirst
          End If
        End If
       Next j
       Next i
  Loop
   rstxbh.MoveNext
Loop
   
  Label1.Caption = "数据处理完成了！"
    
End Sub