frmclassschedule.vb

教务管理系统：学生成绩管理

        Me.CmbTimePeriod.Items.Add("14:00-16:00")
        Me.CmbTimePeriod.Items.Add("16:00-18:00")
        Me.CmbTimePeriod.Items.Add("18:00-20:00")
        Me.CmbTimePeriod.Items.Add("20:00-22:00")

    End Sub

    Private Sub addCmbCourse()
        Try
            Dim sql As String = "SELECT COURSENAME  FROM COURSEINFO"
            Dim dt As DataTable = GetDataTable(sql)
            For i As Integer = 0 To dt.Rows.Count - 1
                Me.CmbCourse.Items.Add(dt.Rows(i)("coursename"))
            Next
        Catch ex As Exception
            WriteErr(ex)
        End Try
    End Sub

    Private Sub Button_Enable(ByVal ff As Boolean)
        BtnAdd.Enabled = ff
        BtnEdit.Enabled = ff
        BtnOutput.Enabled = ff
        BtnDel.Enabled = ff
    End Sub


    Private Sub BindTree()


        Dim node As TreeNode
        TreeView1.Nodes.Clear()

        node = TreeView1.Nodes.Add(CmbClass.Text.Trim)
        node = node.Nodes.Add(year)
        node = node.Nodes.Add(term)

        CreateTreeFirst(node)
        CreateTreeSecond(node)

        TreeView1.ExpandAll()
    End Sub

    Private Sub CreateTreeFirst(ByVal node As TreeNode)
        Try

            Dim sql As String = "SELECT DISTINCT DAYOFWEEK FROM SCHEDULE WHERE CLASSID=" + classID + " AND YEAR=" + year + " AND TERM='" + term + "' "
            Dim dt As DataTable = GetDataTable(sql)

            For i As Integer = 0 To dt.Rows.Count - 1
                node.Nodes.Add(CmbDayOfWeek.Items.Item(dt.Rows(i)("dayofweek") - 1))
            Next

        Catch ex As Exception
            WriteErr(ex)
        End Try
    End Sub

    Private Sub CreateTreeSecond(ByVal node As TreeNode)

        Try
            Dim IDItem(2) As String
            IDItem = CmbClass.Text.Split("|"c)
            Dim sql As String = "SELECT A.MYID, A.DAYOFWEEK,A.TIMEPERIOD,B.COURSENAME FROM SCHEDULE A,COURSEINFO B WHERE A.COURSEID=B.COURSEID AND A.CLASSID=" + classID + " AND YEAR=" + year + " AND TERM='" + term + "'"
            Dim dt As DataTable = GetDataTable(sql)

            Dim node1 As TreeNode

            For Each node1 In node.Nodes
                For i As Integer = 0 To dt.Rows.Count - 1
                    If CmbDayOfWeek.Items.Item(dt.Rows(i)("DAYOFWEEK") - 1) = node1.Text Then
                        Dim str1 As String = dt.Rows(i)("TIMEPERIOD")
                        Dim str2 As String = dt.Rows(i)("COURSENAME")
                        Dim str3 As String = dt.Rows(i)("MYID")
                        node1.Nodes.Add(str3 + "|" + str1 + "|" + str2)
                    End If
                Next
            Next

        Catch ex As Exception
            WriteErr(ex)
        End Try
    End Sub


    Private Sub BtnOK_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnOK.Click
        If Me.CmbClass.Text.Trim() = String.Empty Then
            MessageBox.Show("班级不能为空")
            Me.CmbClass.Focus()
        ElseIf Me.CmbYear.Text.Trim() = String.Empty Then
            MessageBox.Show("年份不能为空")
            Me.CmbYear.Focus()
        ElseIf Me.CmbTerm.Text.Trim() = String.Empty Then
            MessageBox.Show("学期不能为空")
            Me.CmbTerm.Focus()
        Else
            Dim IDItem(2) As String
            IDItem = CmbClass.Text.Split("|"c)
            classID = IDItem(0)
            classname = IDItem(1)
            term = CmbTerm.Text.Trim
            year = CmbYear.Text.Trim
            BindTree()
            Button_Enable(True)
            BtnOK.Enabled = False
        End If
    End Sub

    Private Sub BtnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnAdd.Click
        If Me.TxtID.Text.Trim() = String.Empty Then
            MessageBox.Show("记录编号不能为空")
            Me.TxtID.Focus()
        ElseIf Not JudgeInt(TxtID.Text.Trim) Then
            MessageBox.Show("记录编号必须为整数")
            Me.TxtID.Focus()
        ElseIf JudgeKeyExist("Schedule", "myid", TxtID.Text.Trim) Then
            MessageBox.Show("记录编号已经存在")
            Me.TxtID.Text = ""
            Me.TxtID.Focus()
        ElseIf Me.CmbDayOfWeek.Text.Trim() = String.Empty Then
            MessageBox.Show("星期几不能为空")
            Me.CmbDayOfWeek.Focus()
        ElseIf Me.CmbTimePeriod.Text.Trim() = String.Empty Then
            MessageBox.Show("时段不能为空")
            Me.CmbTimePeriod.Focus()
        ElseIf Me.CmbCourse.Text.Trim() = String.Empty Then
            MessageBox.Show("课程不能为空")
            Me.CmbCourse.Focus()
        Else
            InsertData()
        End If
    End Sub

    Private Sub BtnEdit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnEdit.Click
        If Me.TxtID.Text.Trim() = String.Empty Then
            MessageBox.Show("记录编号不能为空")
            Me.TxtID.Focus()
        ElseIf Not JudgeInt(TxtID.Text.Trim) Then
            MessageBox.Show("记录编号必须为整数")
            Me.TxtID.Focus()
        ElseIf Not JudgeKeyExist("Schedule", "myid", TxtID.Text.Trim) Then
            MessageBox.Show("要删除的记录集不存在")
            Me.TxtID.Text = ""
            Me.TxtID.Focus()
        ElseIf Me.CmbDayOfWeek.Text.Trim() = String.Empty Then
            MessageBox.Show("星期几不能为空")
            Me.CmbDayOfWeek.Focus()
        ElseIf Me.CmbTimePeriod.Text.Trim() = String.Empty Then
            MessageBox.Show("时段不能为空")
            Me.CmbTimePeriod.Focus()
        ElseIf Me.CmbCourse.Text.Trim() = String.Empty Then
            MessageBox.Show("课程不能为空")
            Me.CmbCourse.Focus()
        Else
            Me.UpdateData()
        End If
    End Sub

    Private Sub BtnDel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnDel.Click
        If Me.TxtID.Text.Trim() = String.Empty Then
            MessageBox.Show("记录编号不能为空")
            Me.TxtID.Focus()
        ElseIf Not JudgeInt(TxtID.Text.Trim) Then
            MessageBox.Show("记录编号必须为整数")
            Me.TxtID.Focus()
        ElseIf Not JudgeKeyExist("Schedule", "myid", TxtID.Text.Trim) Then
            MessageBox.Show("要删除的记录集不存在")
            Me.TxtID.Focus()
        Else
            If MessageBox.Show("确定要删除？", "删除确认", MessageBoxButtons.OKCancel, MessageBoxIcon.Warning, MessageBoxDefaultButton.Button2) = DialogResult.OK Then
                Me.DeleteData()
            End If
        End If
    End Sub

    Private Sub BtnExit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnExit.Click
        Me.Close()
    End Sub
    Private Sub BtnOutput_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnOutput.Click

        Dim excelApp As New Excel.Application
        Dim excelBook As Excel.Workbook
        Dim excelWorksheet As Excel.Worksheet

        Try

            excelBook = excelApp.Workbooks.Open(Application.StartupPath & "\1.xls")
            excelWorksheet = excelBook.Sheets.Item(1)
            excelApp.Visible = True
            excelWorksheet.Activate()



            With excelWorksheet
                .Range(.Cells(1, 1), .Cells(1, 8)).Merge()
                .Range(.Cells(2, 1), .Cells(2, 8)).Merge()
                '.Cells.HorizontalAlignment = Right
                .Cells(1, 1).Value = classname & year & term & "课表"
                .Cells(1, 1).Font.Size = 20
                .Cells(1, 1).Font.bold = True
                .Cells(2, 1).Value = Today.ToString

            End With

            With excelWorksheet
                For i As Integer = 0 To 6
                    .Cells(3, i + 2).value = CmbDayOfWeek.Items.Item(i)
                Next
                For i As Integer = 0 To 7
                    .Cells(i + 4, 1).value = CmbTimePeriod.Items.Item(i)
                Next

            End With

            Dim node As TreeNode
            Dim node1 As TreeNode
            Dim node2 As TreeNode
            Dim node3 As TreeNode
            Dim node4 As TreeNode
            For Each node In TreeView1.Nodes()
                For Each node1 In node.Nodes()
                    For Each node2 In node1.Nodes()
                        For Each node3 In node2.Nodes()
                            For i As Integer = 0 To 6
                                If node3.Text = excelWorksheet.Cells(3, i + 2).value Then
                                    For Each node4 In node3.Nodes()
                                        Dim IDItem(3) As String
                                        IDItem = node4.Text.Split("|"c)
                                        For j As Integer = 0 To 7
                                            If IDItem(1) = excelWorksheet.Cells(j + 4, 1).value Then
                                                excelWorksheet.Cells(j + 4, i + 2).value() = IDItem(2)
                                            End If
                                        Next
                                    Next


                                End If
                            Next
                        Next
                    Next
                Next
            Next
            'For i As Integer = 0 To 18
            '    excelBook.ActiveSheet.cells(4 + i, 4) = allNum(i)
            'Next

        Catch ex As Exception
            WriteErr(ex)
        Finally
            excelBook = Nothing
            excelWorksheet = Nothing
            excelApp = Nothing
        End Try

    End Sub

    Private Sub BtnRechoose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnRechoose.Click
        Button_Enable(False)
        BtnOK.Enabled = True
        CmbClass.Text = ""
        CmbTerm.Text = ""
        CmbYear.Text = ""
        TreeView1.Nodes.Clear()
    End Sub

    Private Sub InsertData()

        Dim myID As Int16 = Int16.Parse(TxtID.Text.Trim)
        Dim DayOfWeek As String = (CmbDayOfWeek.SelectedIndex + 1).ToString
        Dim TimePeriod As String = CmbTimePeriod.Text.Trim
        Dim CourseId As String = CourseNameToID(CmbCourse.Text.Trim)

        Dim value As String = myID.ToString + "," + classID + "," + CourseId + "," + year + ",'" + term + "','" + DayOfWeek + "','" + TimePeriod + "'"

        Dim sql As String = "INSERT INTO SCHEDULE (MYID,CLASSID,COURSEID,YEAR,TERM,DAYOFWEEK,TIMEPERIOD) VALUES(" + value + ")"

        Dim sqlCmd As New SqlCommand
        sqlCmd.Connection = SqlCon
        sqlCmd.CommandText = sql

        Try
            SqlCon.Open()
            sqlCmd.ExecuteNonQuery()
            MessageBox.Show("添加记录成功")
        Catch ex As Exception
            MessageBox.Show("添加记录失败")
            WriteErr(ex)
        Finally
            SqlCon.Close()
        End Try
        BindTree()
    End Sub

    Private Sub UpdateData()

        Dim myID As Int16 = Int16.Parse(TxtID.Text.Trim)
        Dim DayOfWeek As String = (CmbDayOfWeek.SelectedIndex + 1).ToString
        Dim TimePeriod As String = CmbTimePeriod.Text.Trim
        Dim CourseId As String = CourseNameToID(CmbCourse.Text.Trim)

        Dim sql As String = "UPDATE SCHEDULE SET CLASSID=" + classID + ", YEAR=" + year + ",TERM='" + term + "',DAYOFWEEK='" + DayOfWeek + "',TIMEPERIOD='" + TimePeriod + "',COURSEID=" + CourseId + "  WHERE MYID= " + myID.ToString

        Dim sqlCmd As New SqlCommand
        sqlCmd.Connection = SqlCon
        sqlCmd.CommandText = sql

        Try
            SqlCon.Open()
            sqlCmd.ExecuteNonQuery()
            MessageBox.Show("修改记录成功")
        Catch ex As Exception
            MessageBox.Show("修改记录失败")
            WriteErr(ex)
        Finally
            SqlCon.Close()
        End Try
        BindTree()
    End Sub

    Private Sub DeleteData()


        Dim myID As Int16 = Int16.Parse(TxtID.Text.Trim)


        Dim sql As String = "DELETE FROM SCHEDULE WHERE MYID= " + myID.ToString

        Dim sqlCmd As New SqlCommand
        sqlCmd.Connection = SqlCon
        sqlCmd.CommandText = sql

        Try
            SqlCon.Open()
            sqlCmd.ExecuteNonQuery()
            MessageBox.Show("删除记录成功")
        Catch ex As Exception
            MessageBox.Show("删除记录失败")
            WriteErr(ex)
        Finally
            SqlCon.Close()
        End Try
        BindTree()
    End Sub

    Private Function CourseNameToID(ByVal name As String)
        Try
            Dim sql As String = "SELECT  COURSEID FROM COURSEINFO WHERE COURSENAME='" + name + "'"
            Dim dt As DataTable = GetDataTable(sql)
            If dt.Rows.Count <> 0 Then
                Return dt.Rows(0)("COURSEID")
            End If

        Catch ex As Exception
            WriteErr(ex)
        End Try
    End Function

    Private Sub TreeView1_AfterSelect(ByVal sender As Object, ByVal e As System.Windows.Forms.TreeViewEventArgs) Handles TreeView1.AfterSelect
        Select Case TreeNodeLevel(e.Node)
            Case 1, 2, 3
            Case 4
                CmbDayOfWeek.Text = e.Node.Text
                TxtID.Text = ""
                CmbTimePeriod.Text = ""
                CmbCourse.Text = ""
            Case 5
                Dim IDItem(3) As String
                IDItem = e.Node.Text.Split("|"c)
                TxtID.Text = IDItem(0)
                CmbDayOfWeek.Text = e.Node.Parent.Text
                CmbTimePeriod.Text = IDItem(1)
                CmbCourse.Text = IDItem(2)
        End Select
    End Sub



End Class