achievement.0.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 student.name AS student_name, curriculum_schedule.* FROM student INNER JOIN curriculum_schedule ON student.stu_num = curriculum_schedule.stu_num WHERE (((curriculum_schedule.curriculum_id)=" & iCurriculumId & ")) ORDER BY curriculum_schedule.point DESC;")
    End Function

    ' 学生所有成绩的加权平均值
    Public Shared Function WeighingAverage(ByVal sStuNum As String) As Single
        WeighingAverage = DbBase.DataInterface.Execute4Value("SELECT AVG(point * credit_hour * coefficient) AS average FROM v_acheivenment WHERE stu_num = '" & sStuNum & "'")
    End Function

    ' 专业内所有学生所有成绩排名
    Public Shared Function StudentOrder(ByVal iProfessionId As Integer) As DataSet
        StudentOrder = DbBase.DataInterface.Execute4DS("SELECT stu_num, AVG(point * credit_hour * coefficient) AS average FROM v_acheivenment GROUP BY stu_num ORDER BY average")
    End Function

    ' 某个学生的所有成绩统计
    Public Shared Function AllAchievments(ByVal sStuNum As String) As DataSet
        AllAchievments = DbBase.DataInterface.Execute4DS("SELECT student.name AS student_name, course.name AS course_name, curriculum_schedule.* FROM course INNER JOIN (curriculum INNER JOIN (student INNER JOIN curriculum_schedule ON student.stu_num = curriculum_schedule.stu_num) ON curriculum.id = curriculum_schedule.curriculum_id) ON course.id = curriculum.course_id where curriculum_schedule.stu_num = '" & sStuNum & "'")
    End Function

End Class ' END CLASS DEFINITION Achievement