procedures.sql

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

DELIMITER //

DROP PROCEDURE IF EXISTS Departments_LoadAll//
DROP PROCEDURE IF EXISTS GetSession //
DROP PROCEDURE IF EXISTS GetRatesExam//




DROP FUNCTION IF EXISTS getStudentsForDisciplineT//
CREATE FUNCTION getStudentsForDisciplineT (pDisciplineID INT) RETURNS INT(11)
NO SQL
BEGIN
    DECLARE vSemesterID INT DEFAULT -1;
    SET vSemesterID = GetDisciplineProperty(pDisciplineID, 'semester');

    CREATE TEMPORARY TABLE tDisciplineStudents AS (
        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 AND
                                          students_groups.State <= 'outlet'
                WHERE disciplines_groups.DisciplineID = pDisciplineID
            ) as st
        GROUP BY st.StudentID
    );
    RETURN 0;
END //



DROP FUNCTION IF EXISTS getDisciplinesForStudentT//
CREATE FUNCTION getDisciplinesForStudentT(pStudentID INT, pSemesterID INT) RETURNS INT(11)
NO SQL
BEGIN
    DECLARE vStudentGroup INT DEFAULT -1;
    SET vStudentGroup = GetStudentGroup(pStudentID, pSemesterID);

    CREATE TEMPORARY TABLE IF NOT EXISTS tStudentDisciplines 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'
    );
    RETURN 0;
END //

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

DROP PROCEDURE IF EXISTS GetSemestersInfo//
CREATE PROCEDURE GetSemestersInfo (IN pSemesterID INT)
READS SQL DATA
SELECT  semesters.* -- ID, Year, Num
    FROM semesters
    WHERE IF(pSemesterID != 0, semesters.ID = pSemesterID, TRUE)
    ORDER BY semesters.ID DESC //



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

DROP PROCEDURE IF EXISTS GetFaculties//
CREATE PROCEDURE GetFaculties ()
READS SQL DATA
SELECT  faculties.* -- ID, Abbr, Name
    FROM faculties
    ORDER BY faculties.Name ASC //


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

DROP PROCEDURE IF EXISTS GetDepartments//
CREATE PROCEDURE GetDepartments (IN pFacultyID INT)
READS SQL DATA
BEGIN
    IF pFacultyID <=> 0 THEN
        SELECT departments.* FROM departments
            ORDER BY departments.Name ASC;
    ELSE
        SELECT  departments.* -- ID, Name, FacultyID
            FROM departments
            WHERE departments.FacultyID = pFacultyID
            ORDER BY departments.Name ASC;
    END IF;
END //



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

DROP PROCEDURE IF EXISTS GetSpecializations//
CREATE PROCEDURE GetSpecializations (IN pFacultyID INT)
READS SQL DATA
SELECT  specializations.* -- ID, Name, Abbr, FacultyID
    FROM specializations
    WHERE specializations.FacultyID = pFacultyID
    ORDER BY subjects.Name ASC //


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

DROP PROCEDURE IF EXISTS GetJobPositions//
CREATE PROCEDURE GetJobPositions ()
READS SQL DATA
SELECT  job_positions.* -- ID, Name
    FROM    job_positions
    ORDER BY job_positions.Name //


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

DROP PROCEDURE IF EXISTS GetGrades//
CREATE PROCEDURE GetGrades ()
READS SQL DATA
SELECT  grades.* -- ID, Num, Degree
    FROM grades
    ORDER BY grades.ID //



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

DROP PROCEDURE IF EXISTS GetGroups//
CREATE PROCEDURE GetGroups (
    IN pGradeID INT,
    IN pFacultyID INT,
    IN pSemesterID INT)
READS SQL DATA
BEGIN
    SELECT  view_groups.GroupID AS 'ID',
            view_groups.GroupNum,
            view_groups.SpecID,
            view_groups.SpecName,
            view_groups.SpecAbbr
        FROM view_groups
        INNER JOIN semesters ON semesters.ID = pSemesterID
        WHERE   view_groups.GradeID = pGradeID AND
                view_groups.FacultyID = pFacultyID AND
                view_groups.Year = semesters.Year
        ORDER BY view_groups.GroupNum ASC;
END //


# get all groups, include attached student's groups
DROP PROCEDURE IF EXISTS GetGroupsForDisciplineAll//
CREATE PROCEDURE GetGroupsForDisciplineAll (IN pDisciplineID INT)
BEGIN
    DECLARE vSemesterID, vYear INT DEFAULT -1;
    SET vSemesterID = GetDisciplineProperty(pDisciplineID, 'semester');
    SET vYear = (SELECT Year from semesters where semesters.ID = vSemesterID);

    # general + attached
    CREATE TEMPORARY TABLE IF NOT EXISTS tGroup AS (
        SELECT tGroup1.GroupID
            FROM (
                SELECT  students_groups.GroupID
                    FROM disciplines_students
                    INNER JOIN students_groups ON students_groups.StudentID = disciplines_students.StudentID AND
                                                    students_groups.SemesterID = vSemesterID AND
                                                    students_groups.State <= 'outlet'
                    WHERE   disciplines_students.DisciplineID = pDisciplineID AND
                            disciplines_students.Type = 'attach'
                UNION
                SELECT  disciplines_groups.GroupID
                    FROM disciplines_groups
                    WHERE disciplines_groups.DisciplineID = pDisciplineID
            ) tGroup1
    );

    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 tGroup
        INNER JOIN view_groups ON tGroup.GroupID = view_groups.GroupID AND view_groups.Year = vYear
        ORDER BY view_groups.GradeID ASC, view_groups.GroupID ASC;
END //


# get all general study groups, that takes this course
DROP PROCEDURE IF EXISTS GetGroupsForDiscipline//
CREATE PROCEDURE GetGroupsForDiscipline (IN pDisciplineID INT)
READS SQL DATA
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 disciplines ON disciplines_groups.DisciplineID = disciplines.ID
        INNER JOIN semesters ON disciplines.SemesterID = semesters.ID
        INNER JOIN view_groups ON disciplines_groups.GroupID = view_groups.GroupID
        WHERE   disciplines_groups.DisciplineID = pDisciplineID
                AND view_groups.Year = semesters.Year
        ORDER BY view_groups.GradeID ASC, view_groups.GroupID ASC;
END //



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

DROP PROCEDURE IF EXISTS GetSubjects//
CREATE PROCEDURE GetSubjects (IN pFacultyID INT)
READS SQL DATA
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
# -------------------------------------------------------------------------------------------

DROP PROCEDURE IF EXISTS GetFullInfo//
CREATE PROCEDURE GetFullInfo (
    IN pUserID INT,
    IN pSemesterID INT)
READS SQL DATA
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
                # personal info
                students.LastName,
                students.FirstName,
                students.SecondName,
                students.ID as 'StudentID',

                # group info
                -- GradeID, GradeNum, GroupID, GroupNum, GroupName, Degree,
                -- SpecID, SpecName, SpecAbbr, FacultyID, FacultyName, FacultyAbbr,
                view_groups_reduced.*,
                students_groups.SemesterID,

                # 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 students
            INNER JOIN accounts ON accounts.ID = students.AccountID
            INNER JOIN user_roles ON user_roles.ID = accounts.UserRoleID
            LEFT JOIN students_groups ON students.ID = students_groups.StudentID
            LEFT JOIN view_groups_reduced ON view_groups_reduced.GroupID = students_groups.GroupID
            WHERE   students.AccountID = pUserID
            ORDER BY students_groups.SemesterID <=> pSemesterID DESC
            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
# -------------------------------------------------------------------------------------------

DROP PROCEDURE IF EXISTS GetTeachers//
CREATE PROCEDURE GetTeachers (
    IN pFacultyID INT,
    IN pDepartmentID INT)
READS SQL DATA
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
            CASE WHEN pFacultyID != 0 THEN view_teachers.FacultyID = pFacultyID ELSE TRUE END AND
            CASE WHEN pDepartmentID != 0 THEN view_teachers.DepID = pDepartmentID ELSE TRUE END
        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)
READS SQL DATA
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 //

DROP PROCEDURE IF EXISTS GetTeachersListForStudent//
CREATE PROCEDURE GetTeachersListForStudent (
    IN pStudentID INT,
    IN pSemesterID INT,
    IN pLoadAll INT)
BEGIN
    DECLARE vRes, vStudentGroup INT DEFAULT -1;
    SET vStudentGroup = GetStudentGroup(pStudentID, pSemesterID);

    DROP TABLE IF EXISTS tStudentDisciplines;
    SET vRes = getDisciplinesForStudentT(pStudentID, pSemesterID);

    SELECT  tStudentDisciplines.DisciplineID,
            teachers.ID AS 'TeacherID',
            teachers.LastName,
            teachers.FirstName,
            teachers.SecondName
        FROM tStudentDisciplines
        INNER JOIN disciplines ON disciplines.ID = tStudentDisciplines.DisciplineID
        LEFT JOIN disciplines_teachers ON disciplines_teachers.DisciplineID = tStudentDisciplines.DisciplineID
        INNER JOIN teachers ON teachers.ID = disciplines_teachers.TeacherID
        WHERE pLoadAll OR disciplines.Subtype IS NULL
        ORDER BY    tStudentDisciplines.DisciplineID ASC,
                    teachers.LastName ASC,
                    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 pWord1 VARCHAR(100) CHARSET utf8,
    IN pWord2 VARCHAR(100) CHARSET utf8,
    IN pWord3 VARCHAR(100) CHARSET utf8,
    IN pDisciplineID INT)
BEGIN
    DECLARE vWord1, vWord2, vWord3 VARCHAR(102) CHARSET utf8;
    DECLARE vAuthorID INT DEFAULT -1;

    SET vWord1 = CONCAT('%', pWord1, '%');
    SET vWord2 = CONCAT('%', pWord2, '%');
    SET vWord3 = CONCAT('%', pWord3, '%');

    SET vAuthorID = GetDisciplineProperty(pDisciplineID, 'author');


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

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


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

DROP PROCEDURE IF EXISTS GetStudents//
CREATE PROCEDURE GetStudents (
    IN pFacultyID INT,
    IN pGradeID INT,
    IN pGroupID INT,
    IN pSemesterID INT,
    IN pWord1 VARCHAR(100) CHARSET utf8,
    IN pWord2 VARCHAR(100) CHARSET utf8,
    IN pWord3 VARCHAR(100) CHARSET utf8)
BEGIN
    DECLARE vWord1, vWord2, vWord3 VARCHAR(102) CHARSET utf8;
    SET vWord1 = CONCAT('%', pWord1, '%');
    SET vWord2 = CONCAT('%', pWord2, '%');
    SET vWord3 = CONCAT('%', pWord3, '%');


    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
            CASE WHEN pFacultyID != 0 THEN view_students.FacultyID = pFacultyID ELSE TRUE END 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 AND
            CASE WHEN pWord1 != '' AND
                    (@full := CONCAT(view_students.LastName, ' ', view_students.FirstName, ' ', view_students.SecondName)) != '' THEN
                         @full LIKE vWord1 AND
                         @full LIKE vWord2 AND
                         @full LIKE vWord3
                    ELSE TRUE
            END
    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)
BEGIN
    DECLARE vSemesterID, vRes INT DEFAULT -1;
    DECLARE vFullNameReg VARCHAR(102) CHARSET utf8;
    SET vSemesterID = GetDisciplineProperty(pDisciplineID, 'semester');
    SET vFullNameReg = CONCAT('%', pFullName, '%');

    DROP TABLE IF EXISTS tDisciplineStudents;
    SET vRes = getStudentsForDisciplineT(pDisciplineID);

     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
        LEFT JOIN tDisciplineStudents ON tDisciplineStudents.StudentID = view_students.StudentID
        WHERE   view_students.SemesterID = vSemesterID AND
                view_students.FacultyID = pFacultyID AND
                view_students.GradeID = pGradeID AND
                (pGroupID = 0 OR view_students.GroupID = pGroupID) AND
                tDisciplineStudents.StudentID IS NULL 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,
                    view_students.LastName ASC,
                    view_students.FirstName ASC;
END //



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

    DROP TABLE IF EXISTS tDisciplineStudents;
    SET vRes = getStudentsForDisciplineT(pDisciplineID);

    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,
            tDisciplineStudents.Type AS 'AttachType'
        FROM tDisciplineStudents
        INNER JOIN view_students ON   view_students.StudentID = tDisciplineStudents.StudentID AND
                                        view_students.SemesterID = vSemesterID
        ORDER BY    tDisciplineStudents.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)
BEGIN
    DECLARE vSemesterID, vRes INT DEFAULT -1;
    SET vSemesterID = GetDisciplineProperty(pDisciplineID, 'semester');

    DROP TABLE IF EXISTS tDisciplineStudents;
    SET vRes = getStudentsForDisciplineT(pDisciplineID);

    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,
            ( tDisciplineStudents.Type IS NOT NULL ) AS 'IsAttached'
        FROM tDisciplineStudents
        INNER JOIN view_students ON   view_students.StudentID = tDisciplineStudents.StudentID AND
                                        view_students.SemesterID = vSemesterID
        WHERE NOT tDisciplineStudents.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)
READS SQL DATA
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,
        view_disciplines.CompoundDiscID,
        (view_disciplines.MaxRate = 100) AS 'IsMapCreated',
        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 //

# это обертка процедуры Discipline_GetInfo для случая, когда передается SubmoduleID
DROP PROCEDURE IF EXISTS Discipline_GetInfoBySubmodule //
CREATE PROCEDURE Discipline_GetInfoBySubmodule  (
  pSubmoduleID INT
)
READS SQL DATA
BEGIN
    DECLARE vDisciplineID INT DEFAULT -1;
    SELECT  modules.DisciplineID
        INTO vDisciplineID
        FROM submodules
        INNER JOIN modules ON  submodules.ModuleID = modules.ID
        WHERE submodules.ID = pSubmoduleID
        LIMIT 1;

    CALL Discipline_GetInfo(vDisciplineID);
END //


# all disciplines for faculty in current semester
DROP PROCEDURE IF EXISTS GetDisciplines//
CREATE PROCEDURE GetDisciplines (
    IN pFacultyID INT,
    IN pSemesterID INT)
READS SQL DATA
BEGIN
    SELECT  view_disciplines.DisciplineID AS 'ID',
            view_disciplines.SubjectID,
            view_disciplines.SubjectName,
            view_disciplines.ExamType AS 'Type',
            (view_disciplines.MaxRate = 100) AS 'isMapCreated'
        FROM view_disciplines
        WHERE   view_disciplines.SemesterID = pSemesterID AND
                view_disciplines.FacultyID = pFacultyID
        ORDER BY view_disciplines.SubjectName ASC;
END //

DROP PROCEDURE IF EXISTS GetCompoundDisciplinesForGrade//
CREATE PROCEDURE GetCompoundDisciplinesForGrade (IN pGradeID INT)
READS SQL DATA
BEGIN
    SELECT  compound_disciplines.ID,
            compound_disciplines.Name
        FROM compound_disciplines
        WHERE compound_disciplines.GradeID = pGradeID;
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)
READS SQL DATA
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', -- bodging, db schema remembered lowerCase
                (view_disciplines.MaxRate = 100)    AS 'IsMapCreated'
    FROM disciplines_teachers
    INNER JOIN view_disciplines ON disciplines_teachers.DisciplineID = view_disciplines.DisciplineID
    INNER JOIN semesters ON semesters.ID = view_disciplines.SemesterID
    LEFT JOIN disciplines_groups ON disciplines_groups.DisciplineID = disciplines_teachers.DisciplineID
    LEFT JOIN view_groups ON view_groups.GroupID = disciplines_groups.GroupID AND view_groups.Year = semesters.Year
    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)
READS SQL DATA
BEGIN
    DECLARE vRes INT DEFAULT -1;
    DROP TABLE IF EXISTS tStudentDisciplines;
    SET vRes = getDisciplinesForStudentT(pStudentID, pSemesterID);

    # 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  tStudentDisciplines.DisciplineID,
                            vr.SubmoduleRate,
                            vr.ModuleType,
                            rt.Rate,
                            vr.SubmoduleIsUsed
                        FROM tStudentDisciplines as tStudentDisciplines
                        LEFT JOIN view_roadmap AS vr ON   vr.DisciplineID = tStudentDisciplines.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)
READS SQL DATA
BEGIN

    # Get all attached groups for disc
    DROP TABLE IF EXISTS tDisc;
    CREATE TEMPORARY TABLE tDisc AS (
        SELECT tTemp.DisciplineID
        FROM (
             SELECT disciplines_groups.DisciplineID
                 FROM disciplines_groups
                 WHERE disciplines_groups.GroupID = pGroupID
             UNION
             SELECT DISTINCT disciplines_students.DisciplineID
                FROM students_groups
                LEFT JOIN disciplines_students ON disciplines_students.StudentID = students_groups.StudentID
                WHERE   students_groups.GroupID = pGroupID AND
                        students_groups.SemesterID = pSemesterID AND
                        students_groups.State <= 'outlet' AND
                        disciplines_students.Type <=> 'attach'
        ) AS tTemp
    );

    SELECT  tDisc.DisciplineID  AS 'ID',
            view_disciplines.SubjectName,
            view_disciplines.Subtype,
            view_disciplines.ExamType  AS 'Type',
            view_disciplines.CompoundDiscID,
            view_disciplines.CompoundDiscName,
            view_disciplines.CurRate,
            view_disciplines.MaxRate
        FROM tDisc
        INNER JOIN view_disciplines ON tDisc.DisciplineID = view_disciplines.DisciplineID
        WHERE view_disciplines.SemesterID = pSemesterID;
END //


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

# TODO: merge with GetRatesForGroupByStage
DROP PROCEDURE IF EXISTS GetRatesForGroup//
CREATE PROCEDURE GetRatesForGroup (
    IN pDisciplineID INT,
    IN pGroupID INT)
BEGIN
    DECLARE vInGeneralGroup BOOLEAN DEFAULT FALSE;
    DECLARE vSemesterID INT DEFAULT -1;
    SET vSemesterID = GetDisciplineProperty(pDisciplineID, 'semester');

    SET vInGeneralGroup = EXISTS(
        SELECT * FROM disciplines_groups
        WHERE disciplines_groups.DisciplineID = pDisciplineID AND
              disciplines_groups.GroupID = pGroupID
        LIMIT 1
    );


    DROP TABLE IF EXISTS tStudents;
    CREATE TEMPORARY TABLE tStudents AS (
        SELECT students_groups.StudentID
        FROM students_groups
        LEFT JOIN disciplines_students ON disciplines_students.DisciplineID = pDisciplineID AND
                                            disciplines_students.StudentID = students_groups.StudentID
        WHERE   students_groups.SemesterID = vSemesterID AND
                students_groups.State <= 'outlet' AND # actual students
                students_groups.GroupID = pGroupID AND
                CASE WHEN vInGeneralGroup THEN
                    NOT disciplines_students.Type <=> 'detach' # not detached
                ELSE
                    disciplines_students.Type <=> 'attach' # is attached
                END
    );

    DROP TABLE IF EXISTS vRoadMap;
    CREATE TEMPORARY TABLE vRoadMap AS (
        SELECT  view_roadmap.SubmoduleID,
                view_roadmap.ModuleType AS 'Type'
            FROM view_roadmap
            WHERE view_roadmap.DisciplineID = pDisciplineID
    );

    SELECT  students.ID,
            students.LastName,
            students.FirstName,
            students.SecondName,
            vRates.RateRegular AS 'intermediate',
            vRates.RateBonus AS 'bonus',
            vRates.RateExam AS 'exam'
        FROM (
            SELECT tStudents.StudentID,
                   SUM(IF(vRoadMap.Type = 'regular', rt.Rate, 0)) AS 'RateRegular',
                   SUM(IF(vRoadMap.Type = 'extra', rt.Rate, 0)) AS 'RateExtra',
                   SUM(IF(vRoadMap.Type = 'bonus', rt.Rate, 0)) AS 'RateBonus',
                   MAX(IF(vRoadMap.Type = 'exam', rt.Rate, 0)) AS 'RateExam'
                FROM tStudents
                CROSS JOIN vRoadMap
                LEFT JOIN rating_table as rt  ON  rt.StudentID = tStudents.StudentID AND
                                                    rt.SubmoduleID = vRoadMap.SubmoduleID
                GROUP BY tStudents.StudentID
        ) vRates
        INNER JOIN students ON students.ID = vRates.StudentID
        ORDER BY CONCAT(students.LastName, students.FirstName, students.SecondName) ASC, students.ID ASC;
END //



# TODO: merge with GetRatesForGroupByStage
DROP PROCEDURE IF EXISTS GetRatesForGroupAll//
CREATE PROCEDURE GetRatesForGroupAll (
    IN pGroupID INT,
    IN pSemesterID INT)
BEGIN
    DROP TABLE IF EXISTS tDisc;
    CREATE TEMPORARY TABLE tDisc AS (
        SELECT tTemp.DisciplineID
        FROM (
            SELECT disciplines_groups.DisciplineID
                FROM disciplines_groups
                WHERE disciplines_groups.GroupID = pGroupID
            UNION
            SELECT DISTINCT disciplines_students.DisciplineID
                FROM students_groups
                LEFT JOIN disciplines_students ON disciplines_students.StudentID = students_groups.StudentID
                WHERE   students_groups.GroupID = pGroupID AND
                        students_groups.State <= 'outlet' AND
                        disciplines_students.Type <=> 'attach'
             ) AS tTemp
    );

    DROP TABLE IF EXISTS tStudents;
    CREATE TEMPORARY TABLE tStudents AS (
        SELECT students_groups.StudentID
            FROM students_groups
            WHERE   students_groups.GroupID = pGroupID
                    and students_groups.SemesterID = pSemesterID
    );

    DROP TABLE IF EXISTS vRoadMap;
    CREATE TEMPORARY TABLE vRoadMap AS (
        SELECT  tDisc.DisciplineID as 'DisciplineID',
                view_roadmap.ModuleType as 'Type',
                view_roadmap.SubmoduleID
        FROM tDisc
        LEFT JOIN view_roadmap ON view_roadmap.DisciplineID = tDisc.DisciplineID
    );

    SELECT  vRates.StudentID as 'StudentID',
            vRates.DisciplineID as 'DisciplineID',
            disciplines.CompoundDiscID,
            vRates.RateRegular AS 'intermediate',
            vRates.RateBonus AS 'bonus',
            vRates.RateExtra AS 'extra',
            vRates.RateExam AS 'exam',
            vRates.CntExam AS 'examCnt'
    FROM (