win.vb

强大的教务考勤系统数据库

        If mycon.State = ConnectionState.Closed Then mycon.Open()
        Dim str As String

        Select Case operiden
            Case "admin"
                str = inquireAdmin()
            Case "teacher"
                str = inquireTea()
            Case "student"
                Select Case cbInquireContent.Text
                    Case "缺勤"
                        str = "select distinct course.cID as '课程ID',course.cName as '课程名',schedule.mNum as '缺勤数' from course,schedule where sID='" & operid & "' and schedule.cNum=course.cNum"
                    Case "迟到"
                        str = "select distinct course.cID as '课程ID',course.cName as '课程名',schedule.lNum as '迟到数' from course,schedule where sID='" & operid & "' and schedule.cNum=course.cNum"
                    Case "缺勤和迟到"
                        str = "select distinct course.cID as '课程ID',course.cName as '课程名',schedule.mNum as '缺勤数',schedule.lNum as '迟到数' from course,schedule where sID='" & operid & "' and schedule.cNum=course.cNum"
                    Case "缺勤或迟到"
                        str = "select distinct course.cID as '课程ID',course.cName as '课程名',schedule.mNum as '缺勤数',schedule.lNum as '迟到数' from course,schedule where sID='" & operid & "' and schedule.cNum=course.cNum"
                    Case Else
                        str = "select distinct course.cID as '课程ID',course.cName as '课程名',schedule.mNum as '缺勤数',schedule.lNum as '迟到数' from course,schedule where sID='" & operid & "' and schedule.cNum=course.cNum"
                End Select
                If cbInquireLesson.Text <> "" And cbInquireLesson.Text <> "全部" Then
                    str = str & " and schedule.cNum in(select cNum from course where cName='" & cbInquireLesson.Text & "')"
                End If
                If inquireContent() <> "" Then
                    str = str & " and" & inquireContent()
                End If
        End Select

        dbadapter = New SqlDataAdapter(str, mycon)
        Table.Clear()
        dbadapter.Fill(Table)

        buttonInquire.Text = "重新查询"
        gbInquire.Enabled = False

        If operiden = "student" Then
            Dim mycom As New SqlCommand(str, mycon)
            Dim myread As SqlDataReader
            myread = mycom.ExecuteReader

            Select Case cbInquireContent.Text
                Case "缺勤"
                    While myread.Read()
                        If myread(2) >= 4 Then
                            MsgBox("警告！" + vbCrLf + myread(1) + "的缺勤数超过4课时！", , "")
                        End If
                    End While
                Case "迟到"
                    While myread.Read()
                        If myread(2) >= 8 Then
                            MsgBox("警告！" + vbCrLf + myread(1) + "的迟到数超过8课时！", , "")
                        End If
                    End While
                Case "缺勤和迟到"
                    While myread.Read()
                        If myread(2) >= 4 Then
                            MsgBox("警告！" + vbCrLf + myread(1) + "的缺勤数超过4课时！", , "")
                        End If
                        If myread(3) >= 8 Then
                            MsgBox("警告！" + vbCrLf + myread(1) + "的迟到数超过8课时！", , "")
                        End If
                    End While
                Case "缺勤或迟到"
                    While myread.Read()
                        If myread(2) >= 4 Then
                            MsgBox("警告！" + vbCrLf + myread(1) + "的缺勤数超过4课时！", , "")
                        End If
                        If myread(3) >= 8 Then
                            MsgBox("警告！" + vbCrLf + myread(1) + "的迟到数超过8课时！", , "")
                        End If
                    End While
                Case Else
                    While myread.Read()
                        If myread(2) >= 4 Then
                            MsgBox("警告！" + vbCrLf + myread(1) + "的缺勤数超过4课时！", , "")
                        End If
                        If myread(3) >= 8 Then
                            MsgBox("警告！" + vbCrLf + myread(1) + "的迟到数超过8课时！", , "")
                        End If
                    End While
            End Select

            myread.Close()
        End If
    End Sub

    Private Function inquireAdmin() As String
        Dim str As String = ""
        Select Case Me.Text
            Case "查询教师出勤情况"
                Select Case cbInquireContent.Text
                    Case "缺勤"
                        str = "select distinct course.cID as '课程ID',course.cName as '课程名',teacher.tID as '教师ID',teacher.tName as '教师姓名',tSchedule.mNum as '缺勤数' from course,teacher,tSchedule where tSchedule.cNum=course.cNum and tSchedule.tID=teacher.tID" & str
                    Case "迟到"
                        str = "select distinct course.cID as '课程ID',course.cName as '课程名',teacher.tID as '教师ID',teacher.tName as '教师姓名',tSchedule.lNum as'迟到数' from course,teacher,tSchedule where tSchedule.cNum=course.cNum and tSchedule.tID=teacher.tID" & str
                    Case "缺勤和迟到"
                        str = "select distinct course.cID as '课程ID',course.cName as '课程名',teacher.tID as '教师ID',teacher.tName as '教师姓名',tSchedule.mNum as '缺勤数',tSchedule.lNum as'迟到数' from course,teacher,tSchedule where tSchedule.cNum=course.cNum and tSchedule.tID=teacher.tID" & str
                    Case "缺勤或迟到"
                        str = "select distinct course.cID as '课程ID',course.cName as '课程名',teacher.tID as '教师ID',teacher.tName as '教师姓名',tSchedule.mNum as '缺勤数',tSchedule.lNum as'迟到数' from course,teacher,tSchedule where tSchedule.cNum=course.cNum and tSchedule.tID=teacher.tID" & str
                    Case Else
                        str = "select distinct course.cID as '课程ID',course.cName as '课程名',teacher.tID as '教师ID',teacher.tName as '教师姓名',tSchedule.mNum as '缺勤数',tSchedule.lNum as'迟到数' from course,teacher,tSchedule where tSchedule.cNum=course.cNum and tSchedule.tID=teacher.tID" & str
                End Select
                If cbInquireLesson.Text <> "" And cbInquireLesson.Text <> "全部" Then
                    str = str & " and tSchedule.cNum in(select cNum from course where cName='" & cbInquireLesson.Text & "')"
                End If
                If inquireContent() <> "" Then
                    str = str & " and" & inquireContent()
                End If
            Case "查询学生出勤情况"
                Select Case cbInquireContent.Text
                    Case "缺勤"
                        str = "select distinct course.cID as '课程ID',course.cName as '课程名',student.sID as '学生ID',student.sName as '学生姓名',schedule.mNum as'缺勤数' from course,student,schedule where course.cNum=schedule.cNum and student.sID=schedule.sID" & str
                    Case "迟到"
                        str = "select distinct course.cID as '课程ID',course.cName as '课程名',student.sID as '学生ID',student.sName as '学生姓名',schedule.lNum as '迟到数' from course,student,schedule where course.cNum=schedule.cNum and student.sID=schedule.sID" & str
                    Case "缺勤和迟到"
                        str = "select distinct course.cID as '课程ID',course.cName as '课程名',student.sID as '学生ID',student.sName as '学生姓名',schedule.mNum as'缺勤数',schedule.lNum as '迟到数' from course,student,schedule where course.cNum=schedule.cNum and student.sID=schedule.sID" & str
                    Case "缺勤或迟到"
                        str = "select distinct course.cID as '课程ID',course.cName as '课程名',student.sID as '学生ID',student.sName as '学生姓名',schedule.mNum as'缺勤数',schedule.lNum as '迟到数' from course,student,schedule where course.cNum=schedule.cNum and student.sID=schedule.sID" & str
                    Case Else
                        str = "select distinct course.cID as '课程ID',course.cName as '课程名',student.sID as '学生ID',student.sName as '学生姓名',schedule.mNum as'缺勤数',schedule.lNum as '迟到数' from course,student,schedule where course.cNum=schedule.cNum and student.sID=schedule.sID" & str
                End Select
                If cbInquireLesson.Text <> "" And cbInquireLesson.Text <> "全部" Then
                    str = str & " and schedule.cNum in(select cNum from course where cName='" & cbInquireLesson.Text & "')"
                End If
                If inquireContent() <> "" Then
                    str = str & " and" & inquireContent()
                End If
            Case "查询课程出勤情况"
                Select Case cbInquireContent.Text
                    Case "缺勤"
                        str = "select distinct max(course.cID) as '课程ID',max(course.cName) as'课程名',sum(statistic.aNum) as '缺勤数' from course,statistic where course.cNum=statistic.cNum" & str
                    Case "迟到"
                        str = "select distinct max(course.cID) as '课程ID',max(course.cName) as'课程名',sum(statistic.lNum) as '迟到数' from course,statistic where course.cNum=statistic.cNum" & str
                    Case "缺勤和迟到"
                        str = "select distinct max(course.cID) as '课程ID',max(course.cName) as'课程名',sum(statistic.aNum) as '缺勤数',sum(statistic.lNum) as '迟到数' from course,statistic where course.cNum=statistic.cNum" & str
                    Case "缺勤或迟到"
                        str = "select distinct max(course.cID) as '课程ID',max(course.cName) as'课程名',sum(statistic.aNum) as '缺勤数',sum(statistic.lNum) as '迟到数' from course,statistic where course.cNum=statistic.cNum" & str
                    Case Else
                        str = "select distinct max(course.cID) as '课程ID',max(course.cName) as'课程名',sum(statistic.aNum) as '缺勤数',sum(statistic.lNum) as '迟到数' from course,statistic where course.cNum=statistic.cNum" & str
                End Select
                If cbInquireLesson.Text <> "" And cbInquireLesson.Text <> "全部" Then
                    str = str & " and statistic.cNum in(select cNum from course where cName='" & cbInquireLesson.Text & "')"
                End If
                If cbInquireTime.Text <> "" And cbInquireTime.Text <> "全部" Then
                    str = str & " and" & inquireTime()
                End If
                str = str & " group by statistic.cNum"
        End Select

        Return str
    End Function

    Private Function inquireTea() As String
        Dim str As String = ""
        Select Case Me.Text
            Case "查询教师出勤情况"
                Select Case cbInquireContent.Text
                    Case "缺勤"
                        str = "select distinct course.cID as'课程ID',course.cName as '课程名',tSchedule.mNum as '缺勤数' from course,tSchedule where course.cNum=tSchedule.cNum and tSchedule.tID='" & operid & "'"
                    Case "迟到"
                        str = "select distinct course.cID as'课程ID',course.cName as '课程名',tSchedule.lNum as'迟到数' from course,tSchedule where course.cNum=tSchedule.cNum and tSchedule.tID='" & operid & "'"
                    Case "缺勤和迟到"
                        str = "select distinct course.cID as'课程ID',course.cName as '课程名',tSchedule.mNum as '缺勤数',tSchedule.lNum as'迟到数' from course,tSchedule where course.cNum=tSchedule.cNum and tSchedule.tID='" & operid & "'"
                    Case "缺勤或迟到"
                        str = "select distinct course.cID as'课程ID',course.cName as '课程名',tSchedule.mNum as '缺勤数',tSchedule.lNum as'迟到数' from course,tSchedule where course.cNum=tSchedule.cNum and tSchedule.tID='" & operid & "'"
                    Case Else
                        str = "select distinct course.cID as'课程ID',course.cName as '课程名',tSchedule.mNum as '缺勤数',tSchedule.lNum as'迟到数' from course,tSchedule where course.cNum=tSchedule.cNum and tSchedule.tID='" & operid & "'"
                End Select
                If cbInquireLesson.Text <> "" And cbInquireLesson.Text <> "全部" Then
                    str = str & " and tSchedule.cNum in(select cNum from course where cName='" & cbInquireLesson.Text & "')"
                End If
                If inquireContent() <> "" Then
                    str = str & " and" & inquireContent()
                End If
            Case "查询学生出勤情况"
                Select Case cbInquireContent.Text
                    Case "缺勤"
                        str = "select distinct course.cID as'课程ID',course.cName as '课程名 ',student.sID as '学生ID',student.sName as '学生姓名',schedule.mNum as '缺勤数' from course,student,schedule where course.cNum=schedule.cNum and student.sID=schedule.sID and tID='" & operid & "'"
                    Case "迟到"
                        str = "select distinct course.cID as'课程ID',course.cName as '课程名 ',student.sID as '学生ID',student.sName as '学生姓名',schedule.lNum as '迟到数' from course,student,schedule where course.cNum=schedule.cNum and student.sID=schedule.sID and tID='" & operid & "'"
                    Case "缺勤和迟到"
                        str = "select distinct course.cID as'课程ID',course.cName as '课程名 ',student.sID as '学生ID',student.sName as '学生姓名',schedule.mNum as '缺勤数',schedule.lNum as '迟到数' from course,student,schedule where course.cNum=schedule.cNum and student.sID=schedule.sID and tID='" & operid & "'"
                    Case "缺勤或迟到"
                        str = "select distinct course.cID as'课程ID',course.cName as '课程名 ',student.sID as '学生ID',student.sName as '学生姓名',schedule.mNum as '缺勤数',schedule.lNum as '迟到数' from course,student,schedule where course.cNum=schedule.cNum and student.sID=schedule.sID and tID='" & operid & "'"
                    Case Else
                        str = "select distinct course.cID as'课程ID',course.cName as '课程名 ',student.sID as '学生ID',student.sName as '学生姓名',schedule.mNum as '缺勤数',schedule.lNum as '迟到数' from course,student,schedule where course.cNum=schedule.cNum and student.sID=schedule.sID and tID='" & operid & "'"
                End Select
                If cbInquireLesson.Text <> "" And cbInquireLesson.Text <> "全部" Then
                    str = str & " and schedule.cNum in(select cNum from course where cName='" & cbInquireLesson.Text & "')"
                End If
                If inquireContent() <> "" Then
                    str = str & " and" & inquireContent()
                End If
            Case "查询课程出勤情况"
                Select Case cbInquireContent.Text
                    Case "缺勤"
                        str = "select distinct max(course.cID) as '课程ID',max(course.cName) as '课程名',sum(statistic.aNum) as '缺勤数' from course,statistic where course.cNum=statistic.cNum and course.tID='" & operid & "'"
                    Case "迟到"
                        str = "select distinct max(course.cID) as '课程ID',max(course.cName) as '课程名',sum(statistic.lNum) as'迟到数' from course,statistic where course.cNum=statistic.cNum and course.tID='" & operid & "'"
                    Case "缺勤和迟到"
                        str = "select distinct max(course.cID) as '课程ID',max(course.cName) as '课程名',sum(statistic.aNum) as '缺勤数',sum(statistic.lNum) as'迟到数' from course,statistic where course.cNum=statistic.cNum and course.tID='" & operid & "'"
                    Case "缺勤或迟到"
                        str = "select distinct max(course.cID) as '课程ID',max(course.cName) as '课程名',sum(statistic.aNum) as '缺勤数',sum(statistic.lNum) as'迟到数' from course,statistic where course.cNum=statistic.cNum and course.tID='" & operid & "'"
                    Case Else
                        str = "select distinct max(course.cID) as '课程ID',max(course.cName) as '课程名',sum(statistic.aNum) as '缺勤数',sum(statistic.lNum) as'迟到数' from course,statistic where course.cNum=statistic.cNum and course.tID='" & operid & "'"
                End Select
                If cbInquireLesson.Text <> "" And cbInquireLesson.Text <> "全部" Then
                    str = str & " and statistic.cNum in(select cNum from course where cName='" & cbInquireLesson.Text & "')"
                End If
                If cbInquireTime.Text <> "" And cbInquireTime.Text <> "全部" Then
                    str = str & " and" & inquireTime()
                End If
                str = str & " group by statistic.cNum"
        End Select

        Return str
    End Function

    Private Function inquireContent() As String
        Dim str As String = ""
        Select Case cbInquireContent.Text
            Case "缺勤"
                Select Case cbInquireM.Text
                    Case "大于"
                        str = str & " mNum>" & tbInquireM.Text
                    Case "等于"
                        str = str & " mNum=" & tbInquireM.Text
                    Case "小于"
                        str = str & " mNum<" & tbInquireM.Text
                End Select
            Case "迟到"
                Select Case cbInquireL.Text
                    Case "大于"
                        str = str & " lNum>" & tbInquireL.Text
                    Case "等于"
                        str = str & " lNum=" & tbInquireL.Text
                    Case "小于"
                        str = str & " lNum<" & tbInquireL.Text
                End Select
            Case "缺勤和迟到"
                Select Case cbInquireM.Text
                    Case "大于"
                        str = str & " mNum>" & tbInquireM.Text
                    Case "等于"
                        str = str & " mNum=" & tbInquireM.Text
                    Case "小于"
                        str = str & " mNum<" & tbInquireM.Text
                End Select
                Select Case cbInquireL.Text
                    Case "大于"
                        str = str & " and lNum>" & tbInquireL.Text
                    Case "等于"
                        str = str & " and lNum=" & tbInquireL.Text
                    Case "小于"
                        str = str & " and lNum<" & tbInquireL.Text
                End Select
            Case "缺勤或迟到"
                Select Case cbInquireM.Text
                    Case "大于"
                        str = str & " mNum>" & tbInquireM.Text
                    Case "等于"
                        str = str & " mNum=" & tbInquireM.Text
                    Case "小于"
                        str = str & " mNum<" & tbInquireM.Text
                End Select
                Select Case cbInquireL.Text
                    Case "大于"
                        str = str & " or lNum>" & tbInquireL.Text
                    Case "等于"
                        str = str & " or lNum=" & tbInquireL.Text
                    Case "小于"
                        str = str & " or lNum<" & tbInquireL.Text
                End Select
        End Select