R__functions.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 FUNCTION IF EXISTS SaveSession //
DROP FUNCTION IF EXISTS DeattachAllStudents//
DROP FUNCTION IF EXISTS GetGroup//
DROP FUNCTION IF EXISTS CreateDepartment //

# -------------------------------------------------------------------------------------------
# Label: internals
# -------------------------------------------------------------------------------------------

# actually check for first scoring, in this case you cannot yet edit discipline
# "SetRate" stored procedure can change isLocked flag
DROP FUNCTION IF EXISTS InternalIsMapLocked//
CREATE FUNCTION InternalIsMapLocked (pDisciplineID INT) RETURNS BOOLEAN
READS SQL DATA
RETURN (
    SELECT disciplines.IsLocked FROM disciplines
    WHERE disciplines.ID = pDisciplineID LIMIT 1
) <=> TRUE//

# check, that student really take this course
DROP FUNCTION IF EXISTS InternalIsStudentAttached//
CREATE FUNCTION InternalIsStudentAttached (
    pRecordBookID INT,
    pDisciplineID INT
) RETURNS BOOLEAN
READS SQL DATA
BEGIN
    DECLARE vAttachType enum('attach','detach') DEFAULT 'detach';

    SELECT view_disciplines_recordbooks.Type INTO vAttachType
    FROM view_disciplines_recordbooks
    WHERE view_disciplines_recordbooks.RecordBookID = pRecordBookID AND
          view_disciplines_recordbooks.DisciplineID = pDisciplineID
    LIMIT 1;

    RETURN 'attach' = COALESCE(vAttachType, 'attach');
END //

# check, that teacher teach this course
DROP FUNCTION IF EXISTS InternalIsTeacherBound//
CREATE FUNCTION InternalIsTeacherBound (
    pTeacherID INT,
    pDisciplineID INT
) RETURNS BOOLEAN
READS SQL DATA
RETURN EXISTS (
    SELECT * FROM disciplines_teachers
    WHERE disciplines_teachers.TeacherID = pTeacherID AND disciplines_teachers.DisciplineID = pDisciplineID
    LIMIT 1
)//

DROP FUNCTION IF EXISTS InternalIsTeacherAuthor//
CREATE FUNCTION InternalIsTeacherAuthor (
    pTeacherID INT,
    pDisciplineID INT
) RETURNS BOOLEAN
READS SQL DATA
RETURN (
    SELECT disciplines.AuthorID FROM disciplines
    WHERE disciplines.ID = pDisciplineID LIMIT 1
) <=> pTeacherID //

DROP FUNCTION IF EXISTS SetExamPeriodOption//
DROP FUNCTION IF EXISTS Discipline_SetExamPeriodOption//
CREATE FUNCTION Discipline_SetExamPeriodOption (
    pRecordBookID INT,
    pSubmoduleID INT,
    pType enum('absence', 'pass', 'null')
) RETURNS int(11)
NO SQL
BEGIN
    DECLARE vType INT DEFAULT NULL;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;

    IF pType != 'null' THEN
        SET vType = pType;
    END IF;

    INSERT INTO exam_period_options (RecordBookID, SubmoduleID, Type) VALUES(pRecordBookID, pSubmoduleID, vType)
        ON DUPLICATE KEY UPDATE
            exam_period_options.Type = vType;

    RETURN 0;
END //

# check, regular + exam rate == 100
DROP FUNCTION IF EXISTS InternalIsMapCreated//
CREATE FUNCTION InternalIsMapCreated (pDisciplineID INT
) RETURNS int(11)
READS SQL DATA
RETURN (
    SELECT disciplines.MaxRate FROM disciplines
    WHERE disciplines.ID = pDisciplineID LIMIT 1
) <=> 100//

# ordering helper
DROP FUNCTION IF EXISTS InternalOrderModuleTypesForSession//
CREATE FUNCTION InternalOrderModuleTypesForSession (pModuleType INT) RETURNS INT(3)
DETERMINISTIC CONTAINS SQL
RETURN (CASE pModuleType
    WHEN 4 THEN 1 # extra
    WHEN 2 THEN 2 # exam
    WHEN 3 THEN 3 # bonus
    ELSE 4
END)//

# -------------------------------------------------------------------------------------------
# Label: user roles
# -------------------------------------------------------------------------------------------

-- todo: search by name
DROP FUNCTION IF EXISTS GetUserRole//
CREATE FUNCTION GetUserRole(
    pType enum('student', 'teacher', 'admin', 'dean')
) RETURNS INT(11)
DETERMINISTIC CONTAINS SQL
RETURN (CASE pType
    WHEN 'dean' THEN 4
    WHEN 'teacher' THEN 2
    WHEN 'admin' THEN 3
    ELSE 1
END)//

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

DROP FUNCTION IF EXISTS CreateGrade//
CREATE FUNCTION CreateGrade (
    pGradeNum INT,
    pDegree enum('bachelor', 'master', 'specialist')
) RETURNS int(11) # groupID or -1 if failed
NO SQL
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;
    IF pDegree IS NULL OR NOT (pDegree BETWEEN 'bachelor' AND 'specialist') THEN
        RETURN -1;
    END IF;

    INSERT INTO grades (Num, Degree) VALUES (pGradeNum, pDegree)
        ON DUPLICATE KEY UPDATE
            grades.ID = LAST_INSERT_ID(grades.ID);
    RETURN LAST_INSERT_ID();
END //

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

# negative int, if already exists
DROP FUNCTION IF EXISTS CreateGroup//
CREATE FUNCTION CreateGroup (
    pGradeID INT,
    pGroupNum INT,
    pSpecializationID INT,
    pGroupName VARCHAR(50) CHARSET utf8,
    pYear INT
) RETURNS int(11) # group id
NO SQL
BEGIN
    DECLARE vGroupID, vFacultyID, vGroupYear, vIsSpecMatch INT DEFAULT -1;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;

    SET vFacultyID = (SELECT FacultyID FROM specializations WHERE specializations.ID = pSpecializationID LIMIT 1);

    # create discipline
    INSERT INTO study_groups (GradeID, GroupNum, FacultyID) VALUES (pGradeID, pGroupNum, vFacultyID)
        ON DUPLICATE KEY UPDATE
            study_groups.ID = LAST_INSERT_ID(study_groups.ID);
    SET vGroupID = LAST_INSERT_ID();

    SELECT groups_years.GroupID, (groups_years.SpecializationID = pSpecializationID AND
                                  (pGroupName IS NULL OR groups_years.Name <=> pGroupName))
        INTO vGroupYear, vIsSpecMatch
        FROM groups_years
        WHERE groups_years.GroupID = vGroupID AND groups_years.Year = pYear
        LIMIT 1;

    IF vGroupYear = -1 THEN
        INSERT INTO groups_years (GroupID, Year, SpecializationID, Name)
        VALUES (vGroupID, pYear, pSpecializationID, pGroupName);
    ELSEIF NOT vIsSpecMatch THEN
        RETURN -1;
    END IF;

    RETURN vGroupID;
END //

DROP FUNCTION IF EXISTS FindGroup//
CREATE FUNCTION FindGroup (