achievement.vb

学生信息管理系统

Imports CommonDB
Imports System.Text

Public Class Achievement

    Public m_fPoint As Single
    Private m_iId As Integer
    Private m_sStuNum As String
    Private m_iCurriculumId As Integer

    Public ReadOnly Property Id() As Integer
        Get
            Id = m_iId
        End Get
    End Property

    '学生信息
    Public Property Student() As Student
        Get
            Student = New Student(m_sStuNum)
        End Get

        Set(ByVal Value As Student)
            m_sStuNum = Value.StudentNumber
        End Set
    End Property

    '课程信息
    Public Property Curriculum() As Curriculum
        Get
            Curriculum = New Curriculum(m_iCurriculumId)
        End Get

        Set(ByVal Value As Curriculum)
            m_iCurriculumId = Value.Id
        End Set
    End Property

    Public Sub New()
        m_iId = 0
    End Sub

	'构造函数
    Public Sub New(ByVal iId As Integer)
        Try
            Dim drAchievement As DataRow = DbBase.DataInterface.Execute4DS("select * from curriculum_schedule where id =" & iId).Tables(0).Rows(0)
            Me.m_iId = drAchievement("id")
            Me.m_sStuNum = drAchievement("stu_num")
            Me.m_iCurriculumId = drAchievement("curriculum_id")
            Me.m_fPoint = drAchievement("point")
        Catch ex As Exception
            Throw New Exception("Exception occuers when new Achievement!")
        End Try
    End Sub

    '更新成绩：增加成绩、修改成绩
    Public Function Update() As Integer
        Update = DbBase.DataInterface.ExecuteSql("update curriculum_schedule set point = " & Me.m_fPoint & " where id = " & Me.m_iId)
    End Function

    '删除成绩
    Public Function Delete() As Integer
        Delete = DbBase.DataInterface.ExecuteSql("update curriculum_schedule set point = 0 where id = " & Me.m_iId)
    End Function

    '某一门课的所有学生成绩排名
    Public Shared Function CurriculumOrder(ByVal iCurriculumId As Integer) As DataSet
        CurriculumOrder = DbBase.DataInterface.Execute4DS("SELECT stu_num,student_name,point FROM v_achievement WHERE curriculum_id = " & iCurriculumId & " ORDER BY point DESC")
    End Function

    ' 学生所有成绩的加权平均值
    Public Shared Function WeighingAverage(ByVal sStuNum As String) As Single
        WeighingAverage = DbBase.DataInterface.Execute4Value("SELECT average_point FROM v_student_average_achievement WHERE stu_num = '" & sStuNum & "'")
    End Function

    ' 专业内所有学生所有成绩排名
    Public Shared Function StudentOrder(ByVal iProfessionId As Integer) As DataSet
        StudentOrder = DbBase.DataInterface.Execute4DS("SELECT stu_num,student_name,average_point FROM v_profession_student_order where profession_id = " & iProfessionId)
    End Function

    ' 某个学生的所有成绩统计
    Public Shared Function AllAchievments(ByVal sStuNum As String) As DataSet
        AllAchievments = DbBase.DataInterface.Execute4DS("SELECT course_name,point,credit_hour FROM v_achievement where stu_num = '" & sStuNum & "'")
    End Function

    '成绩批量录入
    '参数dtAchievement中必需数据列为：id,point，其它数据列可自定义
    Public Shared Function LogginAchievement(ByVal dtAchievement As DataTable) As Integer
        'Dim sbSql As StringBuilder = New StringBuilder
        Dim iCount As Integer = dtAchievement.Rows.Count - 1
        Dim aSqls(iCount) As String
        For i As Integer = 0 To iCount
            'sbSql.Append()
            'DbBase.DataInterface.ExecuteSql("update curriculum_schedule set point = " & dtAchievement.Rows(i)("point") & "where id = " & dtAchievement.Rows(i)("id"))
            '定义String数组，使用DbBase.DataInterface.ExecuteSqls批量更新数据
            aSqls(i) = "update curriculum_schedule set point = " & dtAchievement.Rows(i)("point") & " where id = " & dtAchievement.Rows(i)("id")
        Next
        DbBase.DataInterface.ExecuteSqls(aSqls)
    End Function
End Class