StoredProcedures.sql

DELIMITER //

DROP PROCEDURE IF EXISTS GetCurSemesterInfo//
DROP PROCEDURE IF EXISTS GetCurSemesterID//
DROP PROCEDURE IF EXISTS GetHashKey//

DROP PROCEDURE IF EXISTS GetStudyGroupsForDisciplineFull//

DROP PROCEDURE IF EXISTS GetStudyGroupsForDiscipline//
DROP PROCEDURE IF EXISTS GetStudyGroups//

DROP PROCEDURE IF EXISTS GetDisciplineInfoByID//
DROP PROCEDURE IF EXISTS GetMapForDisciplineExam//
DROP PROCEDURE IF EXISTS GetMapForDiscipline//

DROP PROCEDURE IF EXISTS GetMapForStudent//
DROP PROCEDURE IF EXISTS GetMapForStudentExam//
DROP PROCEDURE IF EXISTS GetRatesForStudentsGroup//

DROP PROCEDURE IF EXISTS CreateFaculty //
DROP PROCEDURE IF EXISTS GetReports//
DROP PROCEDURE IF EXISTS GetSettings//


# -------------------------------------------------------------------------------------------
# Label: abbreviations
# -------------------------------------------------------------------------------------------

# abbreviation: abbr
# specialization: spec
# department: dep



# -------------------------------------------------------------------------------------------
# Label: preferences
# Label: magic
# -------------------------------------------------------------------------------------------





# -------------------------------------------------------------------------------------------
# Label: semesters
# -------------------------------------------------------------------------------------------

DROP PROCEDURE IF EXISTS GetSemesterInfo//
CREATE PROCEDURE `GetSemesterInfo` (
    IN `pSemesterID` INT
) NO SQL
BEGIN
    SELECT  semesters.ID    AS 'ID',
            semesters.Num   As 'Num',
            semesters.Year  As 'Year'
        FROM `semesters`
        WHERE semesters.ID = pSemesterID
        LIMIT 1;
END //

DROP PROCEDURE IF EXISTS GetSemesters//
CREATE PROCEDURE `GetSemesters` (
) NO SQL
BEGIN
    SELECT  semesters.ID,
            semesters.Year,
            semesters.Num
        FROM `semesters`
        ORDER BY semesters.ID DESC;
END //



# -------------------------------------------------------------------------------------------
# Label: faculties
# -------------------------------------------------------------------------------------------

DROP PROCEDURE IF EXISTS GetFaculties//
CREATE PROCEDURE `GetFaculties` (
) NO SQL
BEGIN
    SELECT  faculties.ID,
            faculties.Name,
            faculties.Abbr
        FROM `faculties`
        ORDER BY faculties.Name ASC;
END //

# -------------------------------------------------------------------------------------------
# Label: departments
# -------------------------------------------------------------------------------------------

DROP PROCEDURE IF EXISTS GetDepartments//
CREATE PROCEDURE `GetDepartments` (
    IN `pFacultyID` INT
) NO SQL
BEGIN
    SELECT  departments.ID,
            departments.Name
        FROM `departments`
        WHERE departments.FacultyID = pFacultyID
        ORDER BY departments.Name ASC;
END //


# -------------------------------------------------------------------------------------------
# Label: specializations
# -------------------------------------------------------------------------------------------

DROP PROCEDURE IF EXISTS GetSpecializations//
CREATE PROCEDURE `GetSpecializations` (
    IN `pFacultyID` INT
) NO SQL
BEGIN
    SELECT  specializations.ID,
            specializations.Name,
            specializations.Abbr
        FROM    `specializations`
        WHERE   specializations.FacultyID = pFacultyID
        ORDER BY subjects.Name ASC;
END //


# -------------------------------------------------------------------------------------------
# Label: job positions
# -------------------------------------------------------------------------------------------

DROP PROCEDURE IF EXISTS GetJobPositions//
CREATE PROCEDURE `GetJobPositions` (
) NO SQL
BEGIN
    SELECT  job_positions.ID,
            job_positions.Name
        FROM    `job_positions`
        ORDER BY job_positions.Name;
END //


# -------------------------------------------------------------------------------------------
# Label: grades
# -------------------------------------------------------------------------------------------

DROP PROCEDURE IF EXISTS GetGrades//
CREATE PROCEDURE `GetGrades` (
) NO SQL
BEGIN
    SELECT  grades.ID,
            grades.Num,
            grades.Degree
        FROM `grades`
        ORDER BY grades.ID;
END //


# -------------------------------------------------------------------------------------------
# Label: study groups
# -------------------------------------------------------------------------------------------

DROP PROCEDURE IF EXISTS GetGroups//
CREATE PROCEDURE `GetGroups` (
    IN `pGradeID` INT,
    IN `pFacultyID` INT
) NO SQL
BEGIN
    SELECT  view_groups.GroupID AS 'ID',
            view_groups.GroupNum,
            view_groups.SpecID,
            view_groups.SpecName,
            view_groups.SpecAbbr
        FROM `view_groups`
        WHERE   view_groups.GradeID = pGradeID AND
                view_groups.FacultyID = pFacultyID
        ORDER BY view_groups.GroupNum ASC;
END //


# get all general study groups, that takes this course
DROP PROCEDURE IF EXISTS GetGroupsForDiscipline//
CREATE PROCEDURE `GetGroupsForDiscipline` (
    IN `pDisciplineID` INT
) NO SQL
BEGIN
    SELECT  view_groups.GroupID AS 'ID',
            view_groups.GroupNum,
            view_groups.GradeID,
            view_groups.GradeNum,
            view_groups.Degree,
            view_groups.SpecID,
            view_groups.SpecName,
            view_groups.SpecAbbr
        FROM `disciplines_groups`
        INNER JOIN `view_groups` ON disciplines_groups.GroupID = view_groups.GroupID
        WHERE disciplines_groups.DisciplineID = pDisciplineID
        ORDER BY view_groups.GradeID ASC, view_groups.GroupID ASC;
END //



# -------------------------------------------------------------------------------------------
# Label: subjects
# -------------------------------------------------------------------------------------------

DROP PROCEDURE IF EXISTS GetSubjects//
CREATE PROCEDURE `GetSubjects` (
    IN `pFacultyID` INT
) NO SQL
BEGIN
    SELECT  subjects.ID,
            subjects.Name AS 'Title',
            subjects.Abbr
        FROM `subjects_faculties`
        INNER JOIN `subjects` ON subjects_faculties.SubjectID = subjects.ID
        WHERE subjects_faculties.FacultyID = pFacultyID
        ORDER BY subjects.Name ASC;
END //



# -------------------------------------------------------------------------------------------
# Label: accounts
# -------------------------------------------------------------------------------------------

# TODO: deprecated (see GetFullInfo)
DROP PROCEDURE IF EXISTS GetAccountInfo//
CREATE PROCEDURE `GetAccountInfo` ( IN `pUserID` INT )
    NO SQL
BEGIN
    SELECT  accounts.ID,
            accounts.Login,
            accounts.EMail,
            user_roles.Type,
            user_roles.RoleName AS 'Role',
            user_roles.Mark     AS 'RoleMark',
            accounts.IsEnabled,
            accounts.ActivationCode AS 'Code',
            accounts.UserAgent
        FROM `accounts`
        INNER JOIN `user_roles` ON accounts.UserRoleID = user_roles.ID
        WHERE accounts.ID = pUserID
        LIMIT 1;
END //

# TODO: deprecated (see GetFullInfo)
DROP PROCEDURE IF EXISTS GetPersonalInfo//
CREATE PROCEDURE `GetPersonalInfo` (
    IN `pUserID` INT,
    IN `pSemesterID` INT
) NO SQL
BEGIN
    DECLARE vAccountType INT DEFAULT -1;
    SELECT user_roles.Type INTO vAccountType
        FROM `accounts`
        INNER JOIN `user_roles` ON accounts.UserRoleID = user_roles.ID
        WHERE accounts.ID = pUserID
        LIMIT 1;

    # type 1: student
    #      2: teacher
    IF vAccountType = 1 THEN
        SELECT  view_students.LastName,
                view_students.FirstName,
                view_students.SecondName,
                view_students.StudentID,
                view_students.GradeID,
                view_students.GradeNum,
                view_students.GroupID,
                view_students.GroupNum,
                view_students.GroupName,
                view_students.Degree,
                view_students.SpecID,
                view_students.SpecName,
                view_students.SpecAbbr,
                view_students.FacultyID,
                view_students.FacultyName,
                view_students.FacultyAbbr
            FROM `view_students`
            WHERE view_students.AccountID = pUserID AND
                  view_students.SemesterID = pSemesterID
            LIMIT 1;
    ELSE
        SELECT  view_teachers.LastName,
                view_teachers.FirstName,
                view_teachers.SecondName,
                view_teachers.TeacherID,
                view_teachers.DepID,
                view_teachers.DepName,
                view_teachers.JobPositionName,
                view_teachers.FacultyID,
                view_teachers.FacultyName,
                view_teachers.FacultyAbbr
            FROM `view_teachers`
            WHERE view_teachers.AccountID = pUserID
            LIMIT 1;
    END IF;
END //

DROP PROCEDURE IF EXISTS GetFullInfo//
CREATE PROCEDURE `GetFullInfo` (
    IN `pUserID` INT,
    IN `pSemesterID` INT
) NO SQL
BEGIN
    DECLARE vAccountType enum('student', 'teacher');

    SELECT user_roles.Type INTO vAccountType
        FROM `accounts`
        INNER JOIN `user_roles` ON accounts.UserRoleID = user_roles.ID
        WHERE accounts.ID = pUserID
        LIMIT 1;

# type 1: student
#      2: teacher
    IF vAccountType = 'student' THEN

        SELECT  # student info
                view_students.LastName,
                view_students.FirstName,
                view_students.SecondName,
                view_students.StudentID,
                view_students.GradeID,
                view_students.GradeNum,
                view_students.GroupID,
                view_students.GroupNum,
                view_students.GroupName,
                view_students.Degree,
                view_students.SpecID,
                view_students.SpecName,
                view_students.SpecAbbr,
                view_students.FacultyID,
                view_students.FacultyName,
                view_students.FacultyAbbr,
                # account info
                accounts.ID,
                accounts.Login,
                accounts.EMail,
                user_roles.Type,
                user_roles.RoleName AS 'Role',
                user_roles.Mark     AS 'RoleMark',
                accounts.IsEnabled,
                accounts.ActivationCode AS 'Code',
                accounts.UserAgent
            FROM `view_students`
            INNER JOIN `accounts` ON accounts.ID = view_students.AccountID
            INNER JOIN `user_roles` ON user_roles.ID = accounts.UserRoleID
            WHERE view_students.AccountID = pUserID AND
                  view_students.SemesterID = pSemesterID
            LIMIT 1;
    ELSE
        SELECT  # teacher info
                view_teachers.LastName,
                view_teachers.FirstName,
                view_teachers.SecondName,
                view_teachers.TeacherID,
                view_teachers.DepID,
                view_teachers.DepName,
                view_teachers.JobPositionName,
                view_teachers.FacultyID,
                view_teachers.FacultyName,
                view_teachers.FacultyAbbr,
                # account info
                accounts.ID,
                accounts.Login,
                accounts.EMail,
                user_roles.Type,
                user_roles.RoleName AS 'Role',
                user_roles.Mark     AS 'RoleMark',
                accounts.IsEnabled,
                accounts.ActivationCode AS 'Code',
                accounts.UserAgent
            FROM `view_teachers`
            INNER JOIN `accounts` ON accounts.ID = view_teachers.AccountID
            INNER JOIN `user_roles` ON user_roles.ID = accounts.UserRoleID
            WHERE view_teachers.AccountID = pUserID
            LIMIT 1;
    END IF;
END //



# -------------------------------------------------------------------------------------------
# Label: teachers
# -------------------------------------------------------------------------------------------

# TODO: used in php
DROP PROCEDURE IF EXISTS GetTeachersByFaculty//
# CREATE PROCEDURE `GetTeachersByFaculty` (IN `pFacultyID` INT)
#     NO SQL
# BEGIN
#     SELECT  view_teachers.TeacherID       AS 'ID',
#             view_teachers.LastName      AS 'Last',
#             view_teachers.FirstName     AS 'First',
#             view_teachers.SecondName    AS 'Second',
#             view_teachers.AccountID,
#             view_teachers.JobPositionName,
#             view_teachers.DepID,
#             view_teachers.DepName
#         FROM `view_teachers`
#         WHERE view_teachers.FacultyID = pFacultyID
#         ORDER BY view_teachers.LastName ASC, view_teachers.FirstName ASC;
# END //



DROP PROCEDURE IF EXISTS GetTeachersByDepartment//
CREATE PROCEDURE `GetTeachersByDepartment` (IN `pDepartmentID` INT)
    NO SQL
BEGIN
    SELECT  view_teachers.TeacherID AS 'ID',
            view_teachers.LastName,
            view_teachers.FirstName,
            view_teachers.SecondName,
            view_teachers.AccountID,
            view_teachers.JobPositionName,
            view_teachers.DepID,
            view_teachers.DepName
        FROM `view_teachers`
        WHERE view_teachers.DepID = pDepartmentID
        ORDER BY view_teachers.LastName ASC, view_teachers.FirstName ASC;
END //


# get teachers, that teach course
DROP PROCEDURE IF EXISTS GetTeachersForDiscipline//
CREATE PROCEDURE `GetTeachersForDiscipline`(IN `pDisciplineID` INT)
    NO SQL
BEGIN
    DECLARE vAuthorID INT DEFAULT -1;
    SET vAuthorID = GetDisciplineProperty(pDisciplineID, 'author');

    SELECT  view_teachers.TeacherID AS 'ID',
            view_teachers.LastName,
            view_teachers.FirstName,
            view_teachers.SecondName,
            view_teachers.JobPositionID,
            view_teachers.JobPositionName,
            view_teachers.DepID,
            view_teachers.DepName,
            view_teachers.FacultyID,
            view_teachers.FacultyAbbr,
            ( view_teachers.TeacherID = vAuthorID ) AS 'IsAuthor'
        FROM `disciplines_teachers`
        INNER JOIN `view_teachers` ON view_teachers.TeacherID = disciplines_teachers.TeacherID
        WHERE disciplines_teachers.DisciplineID = pDisciplineID
        ORDER BY    view_teachers.TeacherID = vAuthorID DESC,
                    view_teachers.LastName ASC,
                    view_teachers.FirstName ASC;
END //



# get teachers, that don't teach course
DROP PROCEDURE IF EXISTS SearchTeachers//
CREATE PROCEDURE `SearchTeachers` (
    IN `pFacultyID` INT,
    IN `pDepartmentID` INT,
    IN `pFullName` VARCHAR(100) CHARSET utf8,
    # order: LastName + FirstName + SecondName
    IN `pDisciplineID` INT
) NO SQL
BEGIN
    DECLARE vAuthorID INT DEFAULT -1;
    DECLARE vFullNameReg VARCHAR(102) CHARSET utf8;
    SET vAuthorID = GetDisciplineProperty(pDisciplineID, 'author');
    SET vFullNameReg = CONCAT('%', pFullName, '%');


    CREATE TEMPORARY TABLE IF NOT EXISTS tDiscTeachers AS (
        SELECT disciplines_teachers.TeacherID
            FROM disciplines_teachers
            WHERE disciplines_teachers.DisciplineID = pDisciplineID
    );

    SELECT  view_teachers.TeacherID AS 'ID',
            view_teachers.LastName,
            view_teachers.FirstName,
            view_teachers.SecondName,
            view_teachers.JobPositionName,
            view_teachers.DepID,
            view_teachers.DepName,
            ( view_teachers.TeacherID = vAuthorID ) AS 'IsAuthor'
        FROM `view_teachers`
        WHERE   view_teachers.FacultyID = pFacultyID AND
                CASE WHEN pDepartmentID != 0 THEN view_teachers.DepID = pDepartmentID ELSE TRUE END AND
                NOT EXISTS (
                    SELECT * FROM tDiscTeachers
                    WHERE tDiscTeachers.TeacherID = view_teachers.TeacherID
                    LIMIT 1
                ) AND
                CASE
                    WHEN pFullName != '' THEN
                        CONCAT( view_teachers.LastName, ' ', view_teachers.FirstName, ' ', view_teachers.SecondName)
                            LIKE vFullNameReg
                    ELSE
                        TRUE
                END
        ORDER BY    view_teachers.FacultyID ASC,
                    view_teachers.DepName ASC,
                    view_teachers.LastName ASC,
                    view_teachers.FirstName ASC;
END //


# -------------------------------------------------------------------------------------------
# Label: students
# -------------------------------------------------------------------------------------------

DROP PROCEDURE IF EXISTS GetStudentsByStudyGroups//
DROP PROCEDURE IF EXISTS GetStudents//
CREATE PROCEDURE `GetStudents`  (
    IN `pGroupID` INT,
    IN `pSemesterID` INT
) NO SQL
BEGIN
    SELECT  view_students.StudentID AS 'ID',
            view_students.LastName,
            view_students.FirstName,
            view_students.SecondName,
            view_students.AccountID,
            view_students.GradeID,
            view_students.GradeNum,
            view_students.Degree,
            view_students.GroupID,
            view_students.GroupNum
        FROM `view_students`
        WHERE view_students.GroupID = pGroupID AND
              view_students.SemesterID = pSemesterID
        ORDER BY view_students.LastName ASC, view_students.FirstName ASC;
END //


DROP PROCEDURE IF EXISTS GetStudentsByFaculty//
CREATE PROCEDURE `GetStudentsByFaculty` (
    IN `pFacultyID` INT,
    IN `pGradeID` INT,
    IN `pGroupID` INT,
    IN `pSemesterID` INT
) NO SQL
BEGIN
    SELECT  view_students.StudentID AS 'ID',
            view_students.LastName,
            view_students.FirstName,
            view_students.SecondName,
            view_students.AccountID,
            view_students.GradeID,
            view_students.GradeNum,
            view_students.Degree,
            view_students.GroupID,
            view_students.GroupNum
    FROM `view_students`
    WHERE   view_students.FacultyID = pFacultyID AND
            view_students.SemesterID = pSemesterID AND
            CASE WHEN pGradeID != 0 THEN view_students.GradeID = pGradeID ELSE TRUE END AND
            CASE WHEN pGroupID != 0 THEN view_students.GroupID = pGroupID ELSE TRUE END
    ORDER BY view_students.LastName ASC, view_students.FirstName ASC;
END //


DROP PROCEDURE IF EXISTS SearchStudentsByName//
CREATE PROCEDURE `SearchStudentsByName`(
        IN `pFullName` VARCHAR(100) CHARSET utf8,
        IN `pFacultyID` INT,
        IN `pGradeID` INT,
        IN `pGroupID` INT,
        IN `pSemesterID` INT
    )
    NO SQL
BEGIN
    SELECT  view_students.StudentID AS 'ID',
            view_students.LastName,
            view_students.FirstName,
            view_students.SecondName,
            view_students.AccountID,
            view_students.GradeID,
            view_students.GradeNum,
            view_students.Degree,
            view_students.GroupID,
            view_students.GroupNum
    FROM `view_students`
    WHERE view_students.SemesterID = pSemesterID AND
          (pFacultyID = 0 OR view_students.FacultyID = pFacultyID) AND
          (pGradeID = 0 OR view_students.GradeID = pGradeID) AND
          (pGroupID = 0 OR view_students.GroupID = pGroupID) AND
          CONCAT(view_students.LastName, ' ', view_students.FirstName, ' ', view_students.SecondName)
              LIKE CONCAT('%', pFullName, '%')


    ORDER BY view_students.LastName ASC, view_students.FirstName ASC;
END //


# not in general groups, not attached
DROP PROCEDURE IF EXISTS SearchStudents//
CREATE PROCEDURE `SearchStudents` (
    IN `pGradeID` INT,
    IN `pGroupID` INT,
    IN `pFacultyID` INT,
    IN `pFullName` VARCHAR(100) CHARSET utf8,
    IN `pDisciplineID` INT
) NO SQL
BEGIN
    DECLARE vSemesterID INT DEFAULT -1;
    DECLARE vFullNameReg VARCHAR(102) CHARSET utf8;
    DECLARE vIsGroupAttached BOOLEAN DEFAULT FALSE;
    SET vSemesterID = GetDisciplineProperty(pDisciplineID, 'semester');
    SET vFullNameReg = CONCAT('%', pFullName, '%');

    CREATE TEMPORARY TABLE IF NOT EXISTS tDiscGroups AS (
        SELECT disciplines_groups.GroupID
        FROM `disciplines_groups`
        WHERE disciplines_groups.DisciplineID = pDisciplineID
    );
    CREATE TEMPORARY TABLE IF NOT EXISTS tAttStud AS (
        SELECT disciplines_students.StudentID
        FROM `disciplines_students`
        WHERE   disciplines_students.DisciplineID = pDisciplineID AND
                disciplines_students.Type = 'attach'
    );

    IF pGroupID != 0 THEN
        SET vIsGroupAttached = EXISTS(
            SELECT * FROM tDiscGroups WHERE tDiscGroups.GroupID = pGroupID LIMIT 1
        );
    END IF;

    SELECT  view_students.StudentID AS 'ID',
            view_students.LastName,
            view_students.FirstName,
            view_students.SecondName,
            view_students.GradeID,
            view_students.GradeNum,
            view_students.Degree,
            view_students.GroupID,
            view_students.GroupNum
        FROM `view_students`
        WHERE   view_students.SemesterID = vSemesterID AND
                view_students.FacultyID = pFacultyID AND
                view_students.GradeID = pGradeID AND

                CASE
                    WHEN pGroupID != 0 THEN
                        NOT vIsGroupAttached AND
                        view_students.GroupID = pGroupID
                    ELSE
                        NOT EXISTS( SELECT * FROM tDiscGroups WHERE tDiscGroups.GroupID = view_students.GroupID )
                END AND

                NOT EXISTS( SELECT * FROM tAttStud WHERE tAttStud.StudentID = view_students.StudentID LIMIT 1 ) AND

                CASE
                    WHEN pFullName != '' THEN
                        CONCAT(view_students.LastName, ' ', view_students.FirstName, ' ', view_students.SecondName) LIKE vFullNameReg
                    ELSE
                        TRUE
                END
        ORDER BY    view_students.GradeID ASC,
                    view_students.GroupID ASC;
END //



# in general groups, attached or detached
DROP PROCEDURE IF EXISTS GetStudentsForDiscipline//
CREATE PROCEDURE `GetStudentsForDiscipline` (
    IN `pDisciplineID` INT
) NO SQL
BEGIN
    DECLARE vSemesterID INT DEFAULT -1;
    SET vSemesterID = GetDisciplineProperty(pDisciplineID, 'semester');

    SELECT  view_students.StudentID AS 'ID',
            view_students.LastName,
            view_students.FirstName,
            view_students.SecondName,
            view_students.GradeID,
            view_students.GradeNum,
            view_students.Degree,
            view_students.GroupID,
            view_students.GroupNum,
            tDiscStudents.Type AS 'AttachType'
        FROM (
                SELECT  st.StudentID,
                        COALESCE(st.Type) AS 'Type'
                FROM (
                    SELECT  disciplines_students.StudentID,
                            disciplines_students.Type
                        FROM `disciplines_students`
                        WHERE disciplines_students.DisciplineID = pDisciplineID
                    UNION
                    SELECT  students_groups.StudentID,
                            NULL AS 'Type'
                        FROM `disciplines_groups`
                        LEFT JOIN `students_groups` ON students_groups.SemesterID = vSemesterID AND
                                                       students_groups.GroupID = disciplines_groups.GroupID
                        WHERE disciplines_groups.DisciplineID = pDisciplineID
                ) as st
                GROUP BY st.StudentID
        ) tDiscStudents
        INNER JOIN `view_students` ON   view_students.StudentID = tDiscStudents.StudentID AND
                                        view_students.SemesterID = vSemesterID
        ORDER BY    ( tDiscStudents.Type <=> 'attach' ) ASC,
                    view_students.GradeID ASC,
                    view_students.GroupNum ASC,
                    view_students.LastName ASC,
                    view_students.FirstName ASC;
END //



# in general groups + attached
DROP PROCEDURE IF EXISTS GetStudentsForRating//
CREATE PROCEDURE `GetStudentsForRating` (
    IN `pDisciplineID` INT
) NO SQL
BEGIN
    DECLARE vSemesterID INT DEFAULT -1;
    SET vSemesterID = GetDisciplineProperty(pDisciplineID, 'semester');


    SELECT  view_students.StudentID AS 'ID',
            view_students.LastName,
            view_students.FirstName,
            view_students.SecondName,
            view_students.GradeID,
            view_students.GradeNum,
            view_students.Degree,
            view_students.GroupID,
            view_students.GroupNum,
            ( tDiscStudents.Type IS NOT NULL ) AS 'IsAttached'
        FROM (
                SELECT  st.StudentID,
                        COALESCE(st.Type) AS 'Type'
                FROM (
                    SELECT  disciplines_students.StudentID,
                            disciplines_students.Type
                        FROM `disciplines_students`
                        WHERE disciplines_students.DisciplineID = pDisciplineID
                    UNION
                        SELECT  students_groups.StudentID,
                                NULL AS 'Type'
                        FROM `disciplines_groups`
                        LEFT JOIN `students_groups` ON  students_groups.SemesterID = vSemesterID AND
                                                        students_groups.GroupID = disciplines_groups.GroupID
                        WHERE disciplines_groups.DisciplineID = pDisciplineID
                ) as st
                GROUP BY st.StudentID
        ) tDiscStudents
        INNER JOIN `view_students` ON   view_students.StudentID = tDiscStudents.StudentID AND
                                        view_students.SemesterID = vSemesterID
        WHERE NOT tDiscStudents.Type <=> 'detach'
        ORDER BY    view_students.GradeID ASC,
                    view_students.GroupNum ASC,
                    view_students.LastName ASC,
                    view_students.FirstName ASC;
END //





# -------------------------------------------------------------------------------------------
# Label: disciplines
# -------------------------------------------------------------------------------------------


DROP PROCEDURE IF EXISTS GetDisciplineInfo//
DROP PROCEDURE IF EXISTS Discipline_GetInfo//
CREATE PROCEDURE `Discipline_GetInfo` (
    IN `pDisciplineID` INT
) NO SQL
BEGIN
    DECLARE vIsBonus BOOLEAN;
    SET vIsBonus = EXISTS(
        SELECT * FROM `modules`
            WHERE   modules.DisciplineID = pDisciplineID AND
                    modules.Type = 'bonus'
            LIMIT 1
    );

    SELECT
        view_disciplines.DisciplineID AS 'ID',
        view_disciplines.AuthorID,
        view_disciplines.GradeID,
        view_disciplines.GradeNum,
        view_disciplines.Degree,
        view_disciplines.ExamType       AS 'Type',
        view_disciplines.LectureCount   AS 'Lectures',
        view_disciplines.PracticeCount  AS 'Practice',
        view_disciplines.LabCount       AS 'Labs',
        view_disciplines.SemesterID,
        view_disciplines.SubjectID,
        view_disciplines.SubjectName,
        view_disciplines.SubjectAbbr,
        view_disciplines.FacultyID,
        view_disciplines.FacultyName,
        view_disciplines.IsLocked,
        view_disciplines.Milestone,
        view_disciplines.Subtype,
        vIsBonus AS 'IsBonus',
        semesters.Num AS 'semesterNum', # TODO: Camelize
        semesters.Year AS 'semesterYear'
    FROM `view_disciplines`
    INNER JOIN `semesters` ON semesters.ID = view_disciplines.SemesterID
    WHERE view_disciplines.DisciplineID = pDisciplineID
    LIMIT 1;
END //


# TODO: haven't reference on it
# all disciplines for faculty in current semester
DROP PROCEDURE IF EXISTS GetDisciplines//
CREATE PROCEDURE `GetDisciplines` (
    IN `pFacultyID` INT,
    IN `pSemesterID` INT
) NO SQL
BEGIN
    SELECT  view_disciplines.DisciplineID AS 'ID',
            view_disciplines.SubjectID,
            view_disciplines.SubjectName,
            view_disciplines.ExamType AS 'Type',
            (view_disciplines_results.DisciplineRateMax = 100) AS 'isMapCreated'
        FROM `view_disciplines`
        INNER JOIN `view_disciplines_results`
            ON view_disciplines_results.DisciplineID = view_disciplines.DisciplineID
        WHERE view_disciplines.SemesterID = pSemesterID AND
            view_disciplines.FacultyID = pFacultyID
        ORDER BY view_disciplines.SubjectName ASC;
END //



# processed format of output (after desequentialization)
# { discipline1 {group1, group2, ...}, discipline2 {groupN, ...}, ... }
DROP PROCEDURE IF EXISTS GetDisciplinesForTeacher//
CREATE PROCEDURE `GetDisciplinesForTeacher` (
        IN `pTeacherID` INT,
        IN `pSemesterID` INT
    )
    NO SQL
BEGIN
    SELECT DISTINCT view_disciplines.DisciplineID AS 'ID',
                    view_disciplines.ExamType AS 'Type',
                    view_disciplines.Subtype,
                    view_disciplines.GradeID,
                    view_disciplines.GradeNum,
                    view_disciplines.Degree,
                    view_groups.GroupID,
                    view_groups.GroupNum,
                    view_groups.GroupName,
                    view_disciplines.SubjectID,
                    view_disciplines.SubjectName,
                    view_disciplines.AuthorID,
                    view_disciplines.IsLocked AS 'IsLocked', # TODO: change column name
                    (view_disciplines_results.DisciplineRateMax = 100)    AS 'IsMapCreated'
    FROM `disciplines_teachers`
    LEFT JOIN `disciplines_groups` ON disciplines_groups.DisciplineID = disciplines_teachers.DisciplineID
        # left -> inner join with maybe NULL ?
    LEFT JOIN `view_groups` ON view_groups.GroupID = disciplines_groups.GroupID
    LEFT JOIN `view_disciplines` ON disciplines_teachers.DisciplineID = view_disciplines.DisciplineID
    LEFT JOIN `view_disciplines_results` ON view_disciplines_results.DisciplineID = view_disciplines.DisciplineID
    WHERE disciplines_teachers.TeacherID = pTeacherID AND
            view_disciplines.SemesterID = pSemesterID
    ORDER BY    view_disciplines.GradeID ASC,
                view_disciplines.SubjectName ASC,
                view_disciplines.DisciplineID ASC,
                view_groups.GroupNum ASC;
END //


# get all disciplines, that student take.
DROP PROCEDURE IF EXISTS GetDisciplinesForStudent//
CREATE PROCEDURE `GetDisciplinesForStudent` (
    IN `pStudentID` INT,
    IN `pSemesterID` INT
) NO SQL
BEGIN
    DECLARE vStudentGroup INT DEFAULT -1;
    SET vStudentGroup = GetStudentGroup(pStudentID, pSemesterID);

    # select all disciplines for user
    CREATE TEMPORARY TABLE IF NOT EXISTS tDisciplines AS (
        SELECT disc2.DisciplineID
            FROM (
                SELECT  disc1.DisciplineID,
                        COALESCE(disc1.Type) AS 'Type'
                FROM (
                        SELECT  disciplines_students.DisciplineID,
                                disciplines_students.Type
                            FROM `disciplines_students`
                            WHERE   disciplines_students.StudentID = pStudentID
                        UNION
                        SELECT  disciplines_groups.DisciplineID,
                                NULL AS 'Type'
                            FROM `disciplines_groups`
                            WHERE   disciplines_groups.GroupID = vStudentGroup
                    ) AS disc1
                INNER JOIN `disciplines` ON disciplines.ID = disc1.DisciplineID
                WHERE disciplines.SemesterID = pSemesterID
                GROUP BY disc1.DisciplineID
            ) AS disc2
            WHERE NOT disc2.Type <=> 'detach'
    );

    # only Cthulhu knows, what happened here
    SELECT  view_disciplines.DisciplineID AS 'ID',
            view_disciplines.SubjectID,
            view_disciplines.SubjectName,
            view_disciplines.ExamType AS 'Type',
            view_disciplines.Subtype,
            teachers.LastName,
            teachers.FirstName,
            teachers.SecondName,
            ( tDR.RateExam + tDR.RateMExam ) AS 'Rate',
            ( tDR.MaxRegularRate + tDR.MaxExamRate ) AS 'MaxCurrentRate'
        FROM (
            SELECT  tRating.DisciplineID,
                    SUM(
                        IF( tRating.SubmoduleIsUsed AND tRating.ModuleType <=> 'regular',
                            tRating.SubmoduleRate, 0)
                    ) AS 'MaxRegularRate',
                    MAX(
                        IF( tRating.SubmoduleIsUsed AND tRating.ModuleType <=> 'exam',
                            tRating.SubmoduleRate, 0)
                    ) AS 'MaxExamRate',
                    SUM(IF(NOT tRating.ModuleType <=> 'exam', tRating.Rate, 0)) AS 'RateMExam',
                    MAX(IF(tRating.ModuleType <=> 'exam' AND tRating.Rate IS NOT NULL, tRating.Rate, 0)) AS 'RateExam'
                FROM (
                    SELECT  tDisciplines.DisciplineID,
                            vr.SubmoduleRate,
                            vr.ModuleType,
                            rt.Rate,
                            vr.SubmoduleIsUsed
                        FROM `tDisciplines`
                        LEFT JOIN `view_roadmap` AS vr ON   vr.DisciplineID = tDisciplines.DisciplineID
                        LEFT JOIN `rating_table` AS rt  ON  rt.StudentID = pStudentID AND
                                                            rt.SubmoduleID = vr.SubmoduleID
                ) AS tRating
                GROUP BY tRating.DisciplineID
        ) AS tDR
        INNER JOIN `view_disciplines` ON view_disciplines.DisciplineID = tDR.DisciplineID
        INNER JOIN `teachers` ON teachers.ID = view_disciplines.AuthorID
        ORDER BY view_disciplines.ExamType ASC, view_disciplines.SubjectName ASC;
END //


# get all disciplines for group, including disciplines, where students have attached status
DROP PROCEDURE IF EXISTS GetDisciplinesForGroup//
CREATE PROCEDURE `GetDisciplinesForGroup` (
        IN `pGroupID` INT,
        IN `pSemesterID` INT
    )
    NO SQL
BEGIN
    (SELECT view_disciplines.DisciplineID  AS 'ID',
            view_disciplines.SubjectName,
            view_disciplines.Subtype,
            view_disciplines.ExamType  AS 'Type'
        FROM `disciplines_groups`
        INNER JOIN `view_disciplines` ON    view_disciplines.DisciplineID = disciplines_groups.DisciplineID AND
                                            view_disciplines.SemesterID = pSemesterID
        WHERE disciplines_groups.GroupID = pGroupID
    ) UNION DISTINCT
    (SELECT view_disciplines.DisciplineID  AS 'ID',
            view_disciplines.SubjectName,
            view_disciplines.Subtype,
            view_disciplines.ExamType AS 'Type'
        FROM `disciplines_students`
        INNER JOIN `students` ON disciplines_students.StudentID = students.ID
        INNER JOIN `view_disciplines` ON view_disciplines.DisciplineID = disciplines_students.DisciplineID AND
                                         view_disciplines.SemesterID = pSemesterID
        INNER JOIN `students_groups` ON students_groups.StudentID = students.ID AND
                                        students_groups.SemesterID = pSemesterID
        WHERE students_groups.GroupID = pGroupID
    );
END //



# -------------------------------------------------------------------------------------------
# Label: rating
# -------------------------------------------------------------------------------------------

DROP PROCEDURE IF EXISTS GetRatesForGroup//
CREATE PROCEDURE `GetRatesForGroup` (
    IN `pDisciplineID` INT,
    IN `pGroupID` INT)
    NO SQL
BEGIN
    DECLARE vChecker BOOLEAN DEFAULT FALSE;
    DECLARE vSemesterID INT DEFAULT -1;
    SET vSemesterID = GetDisciplineProperty(pDisciplineID, 'semester');

    SELECT disciplines_groups.ID IS NOT NULL INTO vChecker
        FROM `disciplines_groups`
        WHERE disciplines_groups.DisciplineID = pDisciplineID AND
              disciplines_groups.GroupID = pGroupID
        LIMIT 1;



    IF !vChecker THEN