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//


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

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



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

DROP PROCEDURE IF EXISTS GetSettings//
CREATE PROCEDURE `GetSettings` (
    IN `pKey` VARCHAR(50) CHARSET utf8
) NO SQL
BEGIN
    SELECT general_settings.*
        FROM `general_settings`
        WHERE general_settings.Name = pKey
        LIMIT 1;
END //


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

DROP PROCEDURE IF EXISTS GetSemesterInfo//
CREATE PROCEDURE `GetSemesterInfo` (
    IN `pSemesterID` INT
) NO SQL
BEGIN
    SELECT  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
) NO SQL
BEGIN
    DECLARE vAccountType enum('student', 'teacher');
    DECLARE vSemesterID 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 = 'student' THEN

        SET vSemesterID = (
            SELECT general_settings.Val
                FROM `general_settings`
                WHERE general_settings.Name = 'SemesterID'
                LIMIT 1
        );

        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 = vSemesterID
            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         AS 'authorID',
        view_disciplines.GradeID          AS 'gradeID',
        view_disciplines.GradeNum         AS 'gradeNum',
        view_disciplines.Degree           AS 'degree',
        view_disciplines.ExamType         AS 'type',
        view_disciplines.LectureCount     AS 'lectures',
        view_disciplines.PracticeCount    AS 'practice',
        view_disciplines.LabCount         AS 'labs',
        view_disciplines.SemesterID       AS 'semesterID',
        view_disciplines.SubjectID        AS 'subjectID',
        view_disciplines.SubjectName      AS 'subjectName',
        view_disciplines.SubjectAbbr      AS 'subjectAbbr',
        view_disciplines.FacultyID        AS 'facultyID',
        view_disciplines.FacultyName      AS 'facultyName',
        view_disciplines.IsLocked         AS 'isLocked',
        view_disciplines.Milestone        AS 'milestone',
        view_disciplines.Subtype          AS 'subtype',
        vIsBonus                        AS 'isBonus'
    FROM `view_disciplines`
    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      AS 'subjectID',
            view_disciplines.SubjectName    AS '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      AS 'subtype',
                    view_disciplines.GradeID      AS 'gradeID',
                    view_disciplines.GradeNum     AS 'gradeNum',
                    view_disciplines.Degree       AS 'degree',
                    view_groups.GroupID           AS 'groupID',
                    view_groups.GroupNum          AS 'groupNum',
                    view_groups.GroupName         AS 'groupName',
                    view_disciplines.SubjectID    AS 'subjectID',
                    view_disciplines.SubjectName  AS 'subjectName',
                    view_disciplines.AuthorID     AS 'authorID',
                    view_disciplines.IsLocked     AS 'isLocked',
                    (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
    SELECT  view_disciplines.DisciplineID     AS 'ID',
            view_disciplines.SubjectID        AS 'subjectID',
            view_disciplines.SubjectName      AS 'subjectName',
            view_disciplines.ExamType         AS 'type',
            view_disciplines.Subtype          AS 'subtype',
            view_teachers.LastName            AS 'lastName',
            view_teachers.FirstName           AS 'firstName',
            view_teachers.SecondName          AS 'secondName',
            (view_rating_result.RateRegular + view_rating_result.RateExtra +
                view_rating_result.RateBonus + view_rating_result.RateExam) AS 'rate',
            view_disciplines_results.DisciplineRateCur AS 'maxCurrentRate'
            # --isMapCreated
        FROM `view_disciplines_students`
        INNER JOIN `view_disciplines` ON view_disciplines.DisciplineID = view_disciplines_students.DisciplineID
        INNER JOIN `view_disciplines_results` ON view_disciplines_results.DisciplineID = view_disciplines_students.DisciplineID
        LEFT JOIN `view_rating_result` ON  view_rating_result.StudentID = pStudentID AND
                                            view_rating_result.DisciplineID = view_disciplines_results.DisciplineID
        INNER JOIN `view_teachers` ON view_teachers.TeacherID = view_disciplines.AuthorID
        WHERE   view_disciplines_students.SemesterID = pSemesterID AND
                view_disciplines.SemesterID = pSemesterID AND
                view_disciplines_students.StudentID = pStudentID AND
                (view_disciplines_students.AttachType IS NULL OR view_disciplines_students.AttachType != 'detach')
        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   AS 'subjectName',
            view_disciplines.Subtype       AS '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   AS 'subjectName',
            view_disciplines.Subtype       AS '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
        SELECT  students.ID,
            students.LastName,
            students.FirstName,
            students.SecondName,
            view_rating_result.RateRegular AS 'intermediate',
            view_rating_result.RateBonus AS 'bonus',
            view_rating_result.RateExam AS 'exam'
        FROM `students`
            LEFT JOIN `view_rating_result` ON view_rating_result.DisciplineID = pDisciplineID AND
                                              view_rating_result.StudentID = students.ID
            INNER JOIN `students_groups` ON students_groups.StudentID = students.ID AND
                                            students_groups.SemesterID = vSemesterID
        WHERE students_groups.GroupID = pGroupID AND
              EXISTS(SELECT * FROM `disciplines_students`
              WHERE  disciplines_students.DisciplineID = pDisciplineID AND
                     disciplines_students.StudentID = students.ID)
        ORDER BY CONCAT(students.LastName, students.FirstName, students.SecondName) ASC,
                 students.ID ASC;
    ELSE
        SELECT  students.ID,
                students.LastName,
                students.FirstName,
                students.SecondName,
                view_rating_result.RateRegular AS 'intermediate',
                view_rating_result.RateBonus AS 'bonus',
                view_rating_result.RateExam AS 'exam'
        FROM `students`
            LEFT JOIN `view_rating_result` ON view_rating_result.DisciplineID = pDisciplineID AND
                                               view_rating_result.StudentID = students.ID
            INNER JOIN `students_groups` ON students_groups.StudentID = students.ID AND
                                            students_groups.SemesterID = vSemesterID
            WHERE students_groups.GroupID = pGroupID AND