win.vb

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

        Return str
    End Function

    Private Function inquireTime() As String
        Dim str As String = ""
        Dim time As String

        Select Case cbInquireTime.Text
            Case "今天"
                time = Date.Now.ToShortDateString()
                str = " date='" & time & "'"
            Case "本周"
                Select Case Date.Now.DayOfWeek
                    Case DayOfWeek.Monday
                        time = Date.Now.AddDays(-1).ToShortDateString()
                    Case DayOfWeek.Tuesday
                        time = Date.Now.AddDays(-2).ToShortDateString()
                    Case DayOfWeek.Wednesday
                        time = Date.Now.AddDays(-3).ToShortDateString()
                    Case DayOfWeek.Thursday
                        time = Date.Now.AddDays(-4).ToShortDateString()
                    Case DayOfWeek.Friday
                        time = Date.Now.AddDays(-5).ToShortDateString()
                    Case DayOfWeek.Saturday
                        time = Date.Now.AddDays(-6).ToShortDateString()
                    Case DayOfWeek.Sunday
                        time = Date.Now.AddDays(-7).ToShortDateString()
                End Select
                str = " date>'" & time & "'"
            Case "本月"
                time = Date.Now.Year.ToString()
                str = " date>'" & time & "-"
                time = Date.Now.Month.ToString()
                str = str & time & "-1'"
            Case "本学期"
                If Date.Now.Month.CompareTo("1") > 0 And Date.Now.Month.CompareTo("1") < 8 Then
                    time = Date.Now.Year.ToString()
                    str = " date>'" & time & "-2-1"
                Else
                    If Date.Now.Month.Equals("1") Then
                        time = Date.Now.AddYears(-1).Year.ToString()
                    Else
                        time = Date.Now.Year.ToString()
                    End If
                    str = " date>'" & time & "-9-1"
                End If
            Case "本学年"
                If Date.Now.Month.CompareTo("1") < 8 Then
                    time = Date.Now.AddYears(-1).Year.ToString()
                Else
                    time = Date.Now.Year.ToString()
                End If
                str = " date>'" & time & "-9-1"
        End Select

        Select Case cbInquireContent.Text
            Case "缺勤"
                Select Case cbInquireM.Text
                    Case "大于"
                        str = str & " sum(statistic.aNum)>" & tbInquireM.Text
                    Case "等于"
                        str = str & " sum(statistic.aNum)=" & tbInquireM.Text
                    Case "小于"
                        str = str & " sum(statistic.aNum)<" & tbInquireM.Text
                End Select
            Case "迟到"
                Select Case cbInquireL.Text
                    Case "大于"
                        str = str & " sum(statistic.lNum)>" & tbInquireL.Text
                    Case "等于"
                        str = str & " sum(statistic.lNum)=" & tbInquireL.Text
                    Case "小于"
                        str = str & " sum(statistic.lNum)<" & tbInquireL.Text
                End Select
            Case "缺勤和迟到"
                Select Case cbInquireM.Text
                    Case "大于"
                        str = str & " sum(statistic.aNum)>" & tbInquireM.Text
                    Case "等于"
                        str = str & " sum(statistic.aNum)=" & tbInquireM.Text
                    Case "小于"
                        str = str & " sum(statistic.aNum)<" & tbInquireM.Text
                End Select
                Select Case cbInquireL.Text
                    Case "大于"
                        str = str & " and sum(statistic.lNum)>" & tbInquireL.Text
                    Case "等于"
                        str = str & " and sum(statistic.lNum)=" & tbInquireL.Text
                    Case "小于"
                        str = str & " and sum(statistic.lNum)<" & tbInquireL.Text
                End Select
            Case "缺勤或迟到"
                Select Case cbInquireM.Text
                    Case "大于"
                        str = str & " sum(statistic.aNum)>" & tbInquireM.Text
                    Case "等于"
                        str = str & " sum(statistic.aNum)=" & tbInquireM.Text
                    Case "小于"
                        str = str & " sum(statistic.aNum)<" & tbInquireM.Text
                End Select
                Select Case cbInquireL.Text
                    Case "大于"
                        str = str & " or sum(statistic.lNum)>" & tbInquireL.Text
                    Case "等于"
                        str = str & " or sum(statistic.lNum)=" & tbInquireL.Text
                    Case "小于"
                        str = str & " or sum(statistic.lNum)<" & tbInquireL.Text
                End Select
        End Select

        Return str
    End Function

    Private Sub Lesson()
        If buttonInquire.Text = "重新查询" Then
            gbLesson.Enabled = True
            tbLesID.Text = ""
            tbLesName.Text = ""
            tbLesTea.Text = ""
            tbLesCr.Text = ""
            tbLesStuNum.Text = ""
            cbLesWeek.Text = ""
            cbLesTime.Text = ""
            buttonInquire.Text = "查询"
            Table.Clear()
            If Me.Text = "删除课程" Then
                buttonDelete.Enabled = True
                buttonDelAll.Enabled = True
            Else
                buttonChange.Enabled = True
            End If
            Exit Sub
        End If

        If mycon.State = ConnectionState.Closed Then mycon.Open()
        Dim str As String
        Dim mycom As SqlCommand
        str = "select cTime from time where bTime='" & cbLesTime.Text & "' and weekday='" & cbLesWeek.Text & "'"
        mycom = New SqlCommand(str, mycon)
        Dim ctime As Long
        ctime = mycom.ExecuteScalar()

        str = ""
        If tbLesStuNum.Text <> "" Then
            str = str & (" and sNum=" & tbLesStuNum.Text)
        End If
        If ctime <> 0 Then
            str = str & " and time.cTime=" & ctime
        End If

        Dim str0 As String = "select distinct cNum,cID,cName,cRoom,course.tID,tName,sNum,weekday,bTime,eTime from course,time,teacher where course.tID=teacher.tID and time.cTime=course.cTime and cID like '%" & tbLesID.Text & "%' and cName like '%" & tbLesName.Text & "%' and cRoom like '%" & tbLesCr.Text & "%' and course.tID like '%" & tbLesTea.Text & "%'" & str
        dbadapter = New SqlDataAdapter(str0, mycon)
        Table0.Clear()
        dbadapter.Fill(Table0)

        str = "select distinct cNum as '流水号',cID as '课程ID',cName as '课程名',cRoom as '教室',course.tID as '教师ID',tName as '教师姓名',sNum as '学生数',weekday as '星期',bTime as '开始时间',eTime as '结束时间' from course,time,teacher where course.tID=teacher.tID and time.cTime=course.cTime and cID like '%" & tbLesID.Text & "%' and cName like '%" & tbLesName.Text & "%' and cRoom like '%" & tbLesCr.Text & "%' and course.tID like '%" & tbLesTea.Text & "%'" & str
        dbadapter = New SqlDataAdapter(str, mycon)
        Table.Clear()
        dbadapter.Fill(Table)

        buttonInquire.Text = "重新查询"
        gbLesson.Enabled = False
    End Sub

    Private Sub Teacher()
        If buttonInquire.Text = "重新查询" Then
            gbTeacher.Enabled = True
            tbTeaID.Text = ""
            tbTeaName.Text = ""
            rbTea1.Checked = True
            rbTea2.Checked = False
            buttonInquire.Text = "查询"
            Table.Clear()
            If Me.Text = "删除教师" Then
                buttonDelete.Enabled = True
                buttonDelAll.Enabled = True
            Else
                buttonChange.Enabled = True
            End If
            Exit Sub
        End If

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

        If rbTea1.Checked = False Then
            If tbTeaID.Text = "" Then
                If tbTeaName.Text = "" Then
                Else
                    str = str & " where tName='" & tbTeaName.Text & "'"
                End If
            Else
                If tbTeaName.Text = "" Then
                    str = str & " where tID='" & tbTeaID.Text & "'"
                Else
                    str = str & " where tID='" & tbTeaID.Text & "' and tName='" & tbTeaName.Text & "'"
                End If
            End If
        Else
            str = str & " where tID like '%" & tbTeaID.Text & "%' and tName like '%" & tbTeaName.Text & "%'"
        End If

        Dim str0 As String = "select tID,tName from teacher" & str
        dbadapter = New SqlDataAdapter(str0, mycon)
        Table0.Clear()
        dbadapter.Fill(Table0)

        str = "select tID as '教师ID',tName as '教师姓名' from teacher" & str
        dbadapter = New SqlDataAdapter(str, mycon)
        Table.Clear()
        dbadapter.Fill(Table)

        buttonInquire.Text = "重新查询"
        gbTeacher.Enabled = False
    End Sub

    Private Sub Student()
        If buttonInquire.Text = "重新查询" Then
            gbStu.Enabled = True
            tbStuID.Text = ""
            tbStuName.Text = ""
            rbStu1.Checked = True
            rbStu2.Checked = False
            rbStu3.Checked = False
            buttonInquire.Text = "查询"
            Table.Clear()
            If Me.Text = "删除学生" Then
                buttonDelete.Enabled = True
                buttonDelAll.Enabled = True
            Else
                buttonChange.Enabled = True
            End If
            Exit Sub
        End If

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

        If rbStu2.Checked = False And rbStu3.Checked = False Then
            If tbStuID.Text = "" Then
                If tbStuName.Text = "" Then
                Else
                    str = str & " where sName='" & tbStuName.Text & "'"
                End If
            Else
                If tbStuName.Text = "" Then
                    str = str & " where sID='" & tbStuID.Text & "'"
                Else
                    str = str & " where sID='" & tbStuID.Text & "' and sName='" & tbStuName.Text & "'"
                End If
            End If
        End If

        If rbStu2.Checked = True Then
            str = str & " where sID like '%" & tbStuID.Text & "%' and sName like '%" & tbStuName.Text & "%'"
        End If

        If rbStu3.Checked = True Then
            str = str & " where sID like '" & tbStuID.Text & "%' and sName like '" & tbStuName.Text & "%'"
        End If

        Dim str0 As String = "select sID,sName from student" & str
        dbadapter = New SqlDataAdapter(str0, mycon)
        Table0.Clear()
        dbadapter.Fill(Table0)

        str = "select sID as '学生ID',sName as '学生姓名' from student" & str
        dbadapter = New SqlDataAdapter(str, mycon)
        Table.Clear()
        dbadapter.Fill(Table)

        buttonInquire.Text = "重新查询"