自动生成课程表.frm

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

VERSION 5.00
Begin VB.Form Frmmakecourse 
   BackColor       =   &H00FFC0C0&
   Caption         =   "自动生成课程表界面"
   ClientHeight    =   3195
   ClientLeft      =   60
   ClientTop       =   345
   ClientWidth     =   4680
   BeginProperty Font 
      Name            =   "隶书"
      Size            =   42
      Charset         =   134
      Weight          =   700
      Underline       =   0   'False
      Italic          =   0   'False
      Strikethrough   =   0   'False
   EndProperty
   ForeColor       =   &H00800000&
   LinkTopic       =   "Form2"
   ScaleHeight     =   3195
   ScaleWidth      =   4680
   StartUpPosition =   3  'Windows Default
   WindowState     =   2  'Maximized
   Begin VB.CommandButton Command2 
      Caption         =   "自动生成课表"
      BeginProperty Font 
         Name            =   "隶书"
         Size            =   36
         Charset         =   134
         Weight          =   700
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      Height          =   855
      Left            =   840
      TabIndex        =   2
      Top             =   6600
      Width           =   5055
   End
   Begin VB.CommandButton Command1 
      BackColor       =   &H00FFC0C0&
      Caption         =   "退出本界面"
      BeginProperty Font 
         Name            =   "隶书"
         Size            =   36
         Charset         =   134
         Weight          =   700
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      Height          =   855
      Left            =   9000
      TabIndex        =   1
      Top             =   6600
      Width           =   4335
   End
   Begin VB.Label Label2 
      Alignment       =   2  'Center
      BackColor       =   &H00FFC0C0&
      BackStyle       =   0  'Transparent
      BeginProperty Font 
         Name            =   "隶书"
         Size            =   26.25
         Charset         =   134
         Weight          =   700
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      ForeColor       =   &H00800000&
      Height          =   1455
      Left            =   3000
      TabIndex        =   3
      Top             =   2520
      Width           =   7575
   End
   Begin VB.Label Label1 
      Alignment       =   2  'Center
      BackStyle       =   0  'Transparent
      BeginProperty Font 
         Name            =   "隶书"
         Size            =   26.25
         Charset         =   134
         Weight          =   700
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      ForeColor       =   &H00800000&
      Height          =   735
      Left            =   3000
      TabIndex        =   0
      Top             =   840
      Width           =   7215
   End
End
Attribute VB_Name = "Frmmakecourse"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
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 rstgradecourse As DAO.Recordset
Dim rsttemp As DAO.Recordset
Dim rstclass As DAO.Recordset
Dim rstclasscourse As DAO.Recordset
Dim dbcourse As DAO.Database
Option Explicit

Private Sub Command1_Click()

Unload Me
frmmain.Show vbModal
End Sub

Private Sub Command2_Click()
Dim sign As String
 Dim h As Integer, k As Integer
 Dim i As Integer, j As Integer
 Set db = DBEngine.Workspaces(0).OpenDatabase("d:\basic.mdb")
 Set dbcourse = DBEngine.Workspaces(0).OpenDatabase("d:\coursetable.mdb")
 Label2.Caption = "正在处理数据，请稍候！"
 Set rstplan = dbcourse.OpenRecordset("select * from teachplan where coursetype='2' and weekhour='3' ")
  Do Until rstplan.EOF
      i = 1
      For j = 1 To 4
       Set rstgradecourse = dbcourse.OpenRecordset("select * from coursegrade ")
       rstgradecourse.Filter = "gradeid='" & rstplan.Fields("gradeid") & "'"
       Set rstgradecourse = rstgradecourse.OpenRecordset()
       If rstgradecourse.Fields(i & j) = "1" Then
        Set rstteachercourse = dbcourse.OpenRecordset("select * from courseteacher")
         rstteachercourse.Filter = "teacherid='" & rstplan.Fields("teacherid") & "'"
         Set rstteachercourse = rstteachercourse.OpenRecordset()
        If rstteachercourse.Fields(i & j) = "1" Then
         Set rstclassroomcourse = dbcourse.OpenRecordset("select * from courseclassroom where type='1'")
         Do Until rstclassroomcourse.Fields(i & j) = "1"
           rstclassroomcourse.MoveNext
         Loop
         Set rstmajor = db.OpenRecordset("select * from major")
         rstmajor.Filter = "gradeid='" & rstgradecourse.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()
         rstgradecourse.edit
         rstgradecourse.Fields(i & j) = rstcourse.Fields("coursename") & vbNewLine & rstplan.Fields("begintime") & "-" & rstplan.Fields("endtime") & "周" & vbNewLine & rstclassroom.Fields("classroomname") & vbNewLine & rstteacher.Fields("teachername")
         rstgradecourse.Update
         rstteachercourse.edit
         rstteachercourse.Fields(i & j) = rstcourse.Fields("coursename") & vbNewLine & rstplan.Fields("begintime") & "-" & rstplan.Fields("endtime") & "周" & vbNewLine & rstplan.Fields("gradeid") & "级" & vbNewLine & rstclassroom.Fields("classroomname")
         rstteachercourse.Update
         rstclassroomcourse.edit
         rstclassroomcourse.Fields(i & j) = rstcourse.Fields("coursename") & vbNewLine & rstplan.Fields("begintime") & "-" & rstplan.Fields("endtime") & "周" & vbNewLine & rstplan.Fields("gradeid") & "级" & vbNewLine & rstteacher.Fields("teachername")
         rstclassroomcourse.Update
         Do Until rstmajor.EOF
          Set rstmajorcourse = dbcourse.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) & rstplan.Fields("begintime") & "-" & rstplan.Fields("endtime") & "周" & vbNewLine & rstclassroom.Fields("classroomname") & vbNewLine & rstteacher.Fields("teachername")
          rstmajorcourse.Update
          Set rstclass = db.OpenRecordset("select * from class")
          rstclass.Filter = "majorid='" & rstmajor.Fields("majorid") & "'"
          Set rstclass = rstclass.OpenRecordset()
          If rstclass.RecordCount() <> 0 Then
            rstclass.MoveFirst
            Do Until rstclass.EOF
            Set rstclasscourse = dbcourse.OpenRecordset("select * from courseclass")
            rstclasscourse.Filter = "classid='" & rstclass.Fields("classid") & "'"
            Set rstclasscourse = rstclasscourse.OpenRecordset()
            rstclasscourse.edit
            rstclasscourse.Fields(i & j) = "a"
            rstclasscourse.Update
            rstclass.MoveNext
            Loop
           End If
          rstmajor.MoveNext
         Loop
        For h = i + 2 To 5 Step 2
          For k = 1 To 4
            If rstgradecourse.Fields(h & k) = "1" Then
              If rstteachercourse.Fields(h & k) = "1" Then
                If rstclassroomcourse.Fields(h & k) = "1" Then
                   rstgradecourse.edit
                   rstgradecourse.Fields(h & k) = rstcourse.Fields("coursename") & Space(2) & rstplan.Fields("begintime") & "-" & rstplan.Fields("endtime") & "周" & vbNewLine & rstclassroom.Fields("classroomname") & vbNewLine & rstteacher.Fields("teachername")
                   rstgradecourse.Update
                   rstteachercourse.edit
                   rstteachercourse.Fields(h & k) = rstcourse.Fields("coursename") & Space(2) & rstplan.Fields("begintime") & "-" & rstplan.Fields("endtime") & "周" & vbNewLine & rstplan.Fields("gradeid") & "级" & vbNewLine & rstclassroom.Fields("classroomname")
                   rstteachercourse.Update
                   rstclassroomcourse.edit
                   rstclassroomcourse.Fields(h & k) = rstcourse.Fields("coursename") & Space(2) & rstplan.Fields("begintime") & "-" & rstplan.Fields("endtime") & "周" & vbNewLine & rstplan.Fields("gradeid") & "级" & vbNewLine & rstteacher.Fields("teachername")
                   rstclassroomcourse.Update
                   rstmajor.MoveFirst
                  Do Until rstmajor.EOF
                  Set rstmajorcourse = dbcourse.OpenRecordset("select * from coursemajor")
                  rstmajorcourse.Filter = "majorid='" & rstmajor.Fields("majorid") & "'"
                  Set rstmajorcourse = rstmajorcourse.OpenRecordset()
                  rstmajorcourse.edit
                  rstmajorcourse.Fields(h & k) = rstcourse.Fields("coursename") & Space(2) & rstplan.Fields("begintime") & "-" & rstplan.Fields("endtime") & "周" & vbNewLine & rstclassroom.Fields("classroomname") & vbNewLine & rstteacher.Fields("teachername")
                  rstmajorcourse.Update
                 Set rstclass = db.OpenRecordset("select * from class")
                 rstclass.Filter = "majorid='" & rstmajor.Fields("majorid") & "'"
                 Set rstclass = rstclass.OpenRecordset()
                 If rstclass.RecordCount() <> 0 Then
                 rstclass.MoveFirst
                 Do Until rstclass.EOF
                  Set rstclasscourse = dbcourse.OpenRecordset("select * from courseclass")
                  rstclasscourse.Filter = "classid='" & rstclass.Fields("classid") & "'"
                 Set rstclasscourse = rstclasscourse.OpenRecordset()
                 rstclasscourse.edit
                 rstclasscourse.Fields(h & k) = "a"
                 rstclasscourse.Update
                 rstclass.MoveNext
                Loop
                End If
                  rstmajor.MoveNext
                  Loop
                   Exit For
                   
                End If
               End If
               End If
             Next k
                 
          Next h
         
         rstplan.Delete
          rstplan.MoveNext
          If rstplan.EOF Then
             rstplan.MoveFirst
          End If
          If rstplan.RecordCount() = 0 Then
           Exit Do
           End If
     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
 Loop
 

  Set rstplan = dbcourse.OpenRecordset("select * from teachplan where coursetype='2'and weekhour='2'  ")
  
  Do Until rstplan.EOF
    For i = 1 To 3
      For j = 1 To 4
       Set rstgradecourse = dbcourse.OpenRecordset("select * from coursegrade ")
       rstgradecourse.Filter = "gradeid='" & rstplan.Fields("gradeid") & "'"
       Set rstgradecourse = rstgradecourse.OpenRecordset()
       If rstgradecourse.Fields(i & j) = "1" Then
        Set rstteachercourse = dbcourse.OpenRecordset("select * from courseteacher")
         rstteachercourse.Filter = "teacherid='" & rstplan.Fields("teacherid") & "'"
         Set rstteachercourse = rstteachercourse.OpenRecordset()
        If rstteachercourse.Fields(i & j) = "1" Then
         Set rstclassroomcourse = dbcourse.OpenRecordset("select * from courseclassroom where type='1'")
         rstclassroomcourse.MoveFirst
         Do While rstclassroomcourse.Fields(i & j) <> "1"
               rstclassroomcourse.MoveNext
               If rstclassroomcourse.EOF = True And j = 4 Then
                  Exit For
                End If
        Loop '寻找一个ij时间空闲的大教室
         Set rstmajor = db.OpenRecordset("select * from major")
         rstmajor.Filter = "gradeid='" & rstgradecourse.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")