control.vb

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

Friend NotInheritable Class Control
    Public Shared constr As String = "uid=sa;pwd=;database=systemdb;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=systemdb;Data Source=(local) "
    Public Shared mycon As New SqlConnection(constr)
    Public Shared choice, input, cTime, bNum1, bNum2, cNum, cName, welcome, name, situation, tSituation As String
    Public Shared weekday, thour, tminute, tstring, nhour, nminute, nstring, bhour, bminute, bstring, ehour, eminute, estring, jhour, jminute, jstring As String
    Public Shared dc As SqlDataAdapter
    Public Shared dt As New DataTable("课程信息表")
    Public Shared dtm As DateTime
    Public Shared rCount As Integer

    Public Shared Function GetTime()
        dtm = DateTime.Now
        weekday = dtm.DayOfWeek.ToString()
        nhour = dtm.Hour.ToString()
        nminute = dtm.Minute.ToString()
        thour = dtm.AddMinutes(-20).Hour.ToString()
        tminute = dtm.AddMinutes(-20).Minute.ToString()
        bhour = dtm.AddMinutes(-10).Hour.ToString()
        bminute = dtm.AddMinutes(-10).Minute.ToString()
        ehour = dtm.AddMinutes(10).Hour.ToString()
        eminute = dtm.AddMinutes(10).Minute.ToString()
        jhour = dtm.AddMinutes(45).Hour.ToString()
        jminute = dtm.AddMinutes(45).Minute.ToString()
    End Function
    Public Shared Function DaySet()
        If (tminute.Length < 2) Then
            tminute = "0" & tminute
        End If
        If (bminute.Length < 2) Then
            bminute = "0" & bminute
        End If
        If (eminute.Length < 2) Then
            eminute = "0" & eminute
        End If
        If (nminute.Length < 2) Then
            nminute = "0" & nminute
        End If
        If (jminute.Length < 2) Then
            jminute = "0" & jminute
        End If
        tstring = thour & ":" & tminute
        bstring = bhour & ":" & bminute
        estring = ehour & ":" & eminute
        nstring = nhour & ":" & nminute
        jstring = jhour & ":" & jminute
    End Function

    Public Shared Function Initiate()
        Dim isql As String
        Dim icom As SqlCommand
        isql = "SELECT COUNT (*) FROM time,course WHERE btime BETWEEN '" & nstring & "' AND '" & _
                estring & "'and time.cTime=course.cTime and course.cRoom='" & choice & "'AND time.weekday='" & _
                weekday & "'"
        icom = New SqlCommand(isql, mycon)
        If (icom.ExecuteScalar() > 0) Then
            isql = "SELECT time.cTime FROM time,course WHERE btime BETWEEN '" & nstring & "' AND '" & _
                    estring & "'and time.cTime=course.cTime and course.cRoom='" & choice & "'AND time.weekday='" & _
                    weekday & "'"
            icom = New SqlCommand(isql, mycon)
            cTime = icom.ExecuteScalar()

            isql = "SELECT cNum FROM course WHERE cTime='" & cTime & "' AND cRoom='" & choice & "'"
            icom = New SqlCommand(isql, mycon)
            cNum = icom.ExecuteScalar()

            isql = "SELECT sNum FROM course WHERE cTime='" & cTime & "' AND cRoom='" & choice & "'"
            icom = New SqlCommand(isql, mycon)
            rCount = icom.ExecuteScalar()
        End If
    End Function

    Public Shared Function iWelcome() As Integer
        If (dtm.Hour >= 6 And dtm.Hour < 12) Then
            welcome = "早上好！"
        ElseIf (dtm.Hour >= 12 And dtm.Hour < 18) Then
            welcome = "下午好！"
        Else
            welcome = "晚上好！"
        End If

        Dim isql1, isql2 As String
        Dim icom As SqlCommand

        isql1 = "SELECT COUNT (*) FROM student WHERE '" & input & "'=sID"
        isql2 = "SELECT COUNT (*) FROM teacher WHERE '" & input & "'=tID"
        icom = New SqlCommand(isql1, mycon)

        If (icom.ExecuteScalar() > 0) Then
            icom = New SqlCommand("SELECT sName FROM student WHERE '" & input & "'=student.sID", mycon)
            name = icom.ExecuteScalar()
            situation = "s"
            welcome = name & "同学" & welcome & vbCrLf & "现在是" & dtm.ToString()
            Return 1
        Else
            icom = New SqlCommand(isql2, mycon)
            If (icom.ExecuteScalar() > 0) Then
                icom = New SqlCommand("SELECT tName FROM teacher WHERE '" & input & "'=teacher.tID", mycon)
                name = icom.ExecuteScalar()
                situation = "t"
                welcome = name & "老师" & welcome & vbCrLf & "现在是" & dtm.ToString()
                Return 2
            Else
                welcome = "对不起，您非本校师生！"
                Return 0
            End If
        End If
    End Function

    Public Shared Function Information() As Boolean
        Dim isql3, isql4, isql5 As String
        Dim icom As SqlCommand
        isql3 = "SELECT COUNT(*) FROM schedule WHERE sID='" & input & "' AND cNum IN(SELECT cNum FROM course WHERE cTime='" & _
                cTime & "'AND cRoom='" & choice & "')"
        isql4 = "SELECT COUNT(*) FROM course WHERE tID='" & input & "' AND cTime='" & cTime & "' AND cRoom='" & choice & "'"
        If (iWelcome() = 1) Then
            icom = New SqlCommand(isql3, mycon)
            If (icom.ExecuteScalar() > 0) Then
                icom = New SqlCommand("SELECT cName FROM course WHERE cRoom='" & choice & "'AND cTime='" & cTime & "'", mycon)
                cName = icom.ExecuteScalar()
                welcome = welcome & vbCrLf & "请进入教室上" & cName & "课"
                Return True
            Else
                Return False
            End If
        ElseIf (iWelcome() = 2) Then
            icom = New SqlCommand(isql4, mycon)
            If (icom.ExecuteScalar() > 0) Then
                icom = New SqlCommand("SELECT cName FROM course WHERE cRoom='" & choice & "'AND cTime='" & cTime & "'", mycon)
                cName = icom.ExecuteScalar()
                welcome = welcome & vbCrLf & "请进入教室上" & cName & "课"
                Return True
            Else
                Return False
            End If
        End If
    End Function


    Public Shared Function TimeJudge()
        Dim ssql, ssql1, ssql2, ssql3 As String
        Dim tcom As SqlCommand
        DaySet()
        If (iWelcome() = 0) Then
            MsgBox(welcome, , "信息")
            Exit Function
        ElseIf (iWelcome() <> 0 And dt.Rows.Count = 0) Then
            welcome = welcome & vbCrLf & "非正常刷卡时间，请稍后再刷！"
            MsgBox(welcome, , "信息")
        Else
            ssql = "SELECT COUNT (*) FROM time,course WHERE btime BETWEEN '" & bstring & "' AND '" & _
                estring & "'and time.cTime=course.cTime and course.cRoom='" & choice & "'AND time.weekday='" & _
                weekday & "'"
            ssql1 = "SELECT COUNT (*) FROM time,course WHERE btime BETWEEN '" & nstring & "' AND '" & _
                    estring & "'and time.cTime=course.cTime and course.cRoom='" & choice & "'AND time.weekday='" & _
                    weekday & "'"
            ssql2 = "SELECT COUNT (*) FROM time,course WHERE btime BETWEEN '" & bstring & "' AND '" & _
                    nstring & "'and time.cTime=course.cTime and course.cRoom='" & choice & "'AND time.weekday='" & _
                    weekday & "'"
            ssql3 = "SELECT time.cTime FROM time,course WHERE btime BETWEEN '" & bstring & "' AND '" & _
                    estring & "'and time.cTime=course.cTime and course.cRoom='" & choice & "'AND time.weekday='" & _
                    weekday & "'"

            tcom = New SqlCommand(ssql, mycon)
            If (tcom.ExecuteScalar > 0) Then
                tcom = New SqlCommand(ssql3, mycon)
                cTime = tcom.ExecuteScalar()
                If (Information()) Then
                    tcom = New SqlCommand(ssql1, mycon)
                    If (tcom.ExecuteScalar() > 0) Then
                        welcome = welcome & vbCrLf & "马上就要上课了！"
                        If (situation = "s") Then
                            situation = "Normal"
                            EditDataTable()
                        ElseIf (situation = "t") Then
                            If (tSituation = "") Then
                                tSituation = "Normal"
                            Else
                                welcome = "请勿重复刷卡！"
                            End If
                        End If
                    Else
                        tcom = New SqlCommand(ssql2, mycon)
                        If (tcom.ExecuteScalar() > 0) Then
                            welcome = welcome & vbCrLf & "您已经迟到了！"
                            If (situation = "s") Then
                                situation = "Late"
                                EditDataTable()
                            ElseIf (situation = "t") Then
                                If (tSituation = "") Then
                                    tSituation = "Late"
                                Else
                                    welcome = "请勿重复刷卡！"
                                End If
                            End If
                        End If
                    End If
                Else
                    welcome = welcome & vbCrLf & "您是不是走错教室了？"
                End If
                MsgBox(welcome, , "信息")
            Else
                MsgBox(welcome & vbCrLf & "非正常刷卡", , "信息")
            End If
        End If
    End Function


    Public Shared Function TimeToCreate() As Boolean
        GetTime()
        DaySet()
        Dim tsql As String
        Dim tcom As SqlCommand
        tsql = "SELECT COUNT(*) FROM time WHERE bTime BETWEEN '" & nstring & "' AND '" & estring & "'"
        tcom = New SqlCommand(tsql, mycon)
        If (nstring >= "7:40" And nstring <= "7:50") Then
            Return True
        ElseIf (nstring >= "13:10" And nstring <= "13:20") Then
            Return True
        ElseIf (tcom.ExecuteScalar() > 0) Then
            Return True
        Else
            Return False
        End If
    End Function

    Public Shared Function TimeToReturn() As Boolean
        GetTime()
        DaySet()
        Dim tsql As String
        Dim tcom As SqlCommand
        tsql = "SELECT COUNT(*) FROM time WHERE bTime BETWEEN '" & tstring & "' AND '" & bstring & "'"
        tcom = New SqlCommand(tsql, mycon)
        If (tcom.ExecuteScalar() > 0) Then
            Return True
        Else
            Return False
        End If
    End Function


    Public Shared Function CreateDataTable()
        Dim csql As String
        Dim ccom As SqlCommand
        If (dt.Rows.Count() = 0) Then
            csql = "SELECT COUNT (*) FROM time WHERE btime<='" & jstring & "'AND etime>='" & jstring & "'AND weekday='" & weekday & "'"
            ccom = New SqlCommand(csql, mycon)
            If (ccom.ExecuteScalar() > 0) Then
                csql = "SELECT sID FROM schedule WHERE cNum IN (SELECT cNum FROM course WHERE cTime IN (SELECT cTime FROM time WHERE btime<='" & _
                        jstring & "'AND etime>='" & jstring & "'AND weekday ='" & weekday & "') AND cRoom='" & choice & "')"
                dc = New SqlDataAdapter(csql, mycon)
                dc.Fill(dt)
                Dim myDataColumn = New DataColumn
                myDataColumn.DataType = System.Type.GetType("System.Int16")
                myDataColumn.ColumnName = "situation"
                myDataColumn.ReadOnly = False
                myDataColumn.Unique = False
                dt.Columns.Add(myDataColumn)
            End If
            InitDataTable()
        End If
        bNum1 = cNum
    End Function

    Public Shared Function InitDataTable()
        Dim irow As DataRow
        For Each irow In dt.Rows
            irow(1) = 0
        Next
    End Function

    Public Shared Function EditDataTable()
        Dim erow As DataRow
        Dim tmpID As String
        For Each erow In dt.Rows
            tmpID = erow(0).ToString.Trim()
            If (situation = "Normal" And tmpID = input) Then
                If (erow(1) = 1 Or erow(1) = 2) Then
                    welcome = "请勿重复刷卡！"
                Else
                    erow(1) = 2
                    dt.AcceptChanges()
                End If
            ElseIf (situation = "Late" And tmpID = input) Then
                If (erow(1) = 1 Or erow(1) = 2) Then
                    welcome = "请勿重复刷卡！"
                Else
                    erow(1) = 1
                    dt.AcceptChanges()
                End If
            End If
        Next
    End Function

    Public Shared Function ReturnToDatabase()
        Dim rrow As DataRow
        Dim rsql As String
        Dim rcom As SqlCommand
        Dim lCount, mCount As Integer
        lCount = 0
        mCount = 0
        If (dt.Rows.Count() > 0) Then
            For Each rrow In dt.Rows
                If (rrow(1) = 1) Then
                    rsql = "EXECUTE update_stu_lNum'" & rrow(0) & "','" & cNum & "'"
                    rcom = New SqlCommand(rsql, mycon)
                    rcom.ExecuteNonQuery()
                    lCount = lCount + 1
                ElseIf (rrow(1) = 0) Then
                    rsql = "EXECUTE update_stu_mNum'" & rrow(0) & "','" & cNum & "'"
                    rcom = New SqlCommand(rsql, mycon)
                    rcom.ExecuteNonQuery()
                    mCount = mCount + 1
                End If
            Next
        End If
        If (cNum <> "") Then
            rsql = "INSERT INTO statistic(date,cNum,rNum,aNum,lNum) Values('" & _
                dtm.ToString() & "','" & cNum & "','" & (rCount - lCount - mCount).ToString() & "','" & _
                mCount.ToString() & "','" & lCount.ToString() & "')"
            rcom = New SqlCommand(rsql, mycon)
            rcom.ExecuteNonQuery()
        End If
        If (tSituation = "Late") Then
            rsql = "EXECUTE update_tea_lNum '" & cTime & "','" & choice & "','" & cNum & "'"
            rcom = New SqlCommand(rsql, mycon)
            rcom.ExecuteNonQuery()
            tSituation = ""
        ElseIf (tSituation = "") Then
            rsql = "EXECUTE update_tea_mNum '" & cTime & "','" & choice & "','" & cNum & "'"
            rcom = New SqlCommand(rsql, mycon)
            rcom.ExecuteNonQuery()
        End If
        dt.Clear()
        bNum2 = cNum
    End Function
End Class