R__functions.sql

                    WHERE   students_groups.GroupID = pGroupID AND students_groups.SemesterID = vSemesterID
                );
        RETURN 0;
END //

DROP FUNCTION IF EXISTS BindGroup//
CREATE FUNCTION BindGroup (
    pTeacherID INT,
    pDisciplineID INT,
    pGroupID INT
) RETURNS int(11)
NO SQL
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -3;

    # todo: move to php layer
    IF  NOT InternalIsTeacherBound(pTeacherID, pDisciplineID) OR InternalIsMapLocked(pDisciplineID) THEN
        RETURN -1;
    END IF;

    -- already bound
    IF EXISTS(
        SELECT * FROM disciplines_groups
        WHERE disciplines_groups.DisciplineID = pDisciplineID AND disciplines_groups.GroupID = pGroupID
    ) THEN
        RETURN 1;
    END IF;

    # bind whole group
    INSERT INTO disciplines_groups (DisciplineID, GroupID) VALUES ( pDisciplineID, pGroupID );
    SELECT LogBind(pDisciplineID, pTeacherID, pGroupID, 'attach', 'group') INTO @vRes;
    RETURN RemoveStudentsAttach(pDisciplineID, pGroupID); # delete students of this group which were bound to discipline before
END //

DROP FUNCTION IF EXISTS UnbindGroup//
CREATE FUNCTION UnbindGroup (
    pTeacherID INT,
    pDisciplineID INT,
    pGroupID INT
) RETURNS int(11)
NO SQL
BEGIN
    # todo: move to php layer
    IF NOT InternalIsTeacherBound(pTeacherID, pDisciplineID) OR InternalIsMapLocked(pDisciplineID) THEN
        RETURN -1;
    END IF;

    # detach group from the discipline
    DELETE FROM disciplines_groups
        WHERE   disciplines_groups.DisciplineID = pDisciplineID AND disciplines_groups.GroupID = pGroupID
        LIMIT 1;

    SELECT LogBind(pDisciplineID, pTeacherID, pGroupID, 'detach', 'group') INTO @vRes;
    RETURN RemoveStudentsAttach(pDisciplineID, pGroupID);
END //

DROP FUNCTION IF EXISTS BindStudent//
CREATE FUNCTION BindStudent (
    pTeacherID INT,
    pDisciplineID INT,
    pStudentID INT
)   RETURNS int(11)
NO SQL
BEGIN
    DECLARE vStudentGroupID, vSemesterID, vInGroup INT DEFAULT -1;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;

    # todo: move to php layer
    IF NOT InternalIsTeacherBound(pTeacherID, pDisciplineID) THEN
        RETURN -1;
    END IF;

    SET vSemesterID = GetDisciplineProperty(pDisciplineID, 'semester');
    SET vStudentGroupID = GetStudentGroup(pStudentID, vSemesterID);

    # check if student's group is bound yet
    SET vInGroup = EXISTS(
        SELECT * FROM disciplines_groups
            WHERE disciplines_groups.DisciplineID = pDisciplineID AND disciplines_groups.GroupID = vStudentGroupID
            LIMIT 1
    );

    # bind student
    IF vInGroup THEN # student in group -> try to remove detached attribute
        DELETE FROM disciplines_students
            WHERE   disciplines_students.DisciplineID = pDisciplineID AND disciplines_students.StudentID = pStudentID
            LIMIT 1;
    ELSE # bind stand alone student ;(
        INSERT INTO disciplines_students (DisciplineID, StudentID, Type) VALUES (pDisciplineID, pStudentID, 'attach')
            ON DUPLICATE KEY UPDATE
                disciplines_students.Type = 'attach';
    END IF;

    RETURN LogBind(pDisciplineID, pTeacherID, pStudentID, 'attach', 'student');
END //

DROP FUNCTION IF EXISTS UnbindStudent//
CREATE FUNCTION UnbindStudent (
    pTeacherID INT,
    pDisciplineID INT,
    pStudentID INT
) RETURNS int(11)
NO SQL
BEGIN
    DECLARE vInGroup, vStudentGroupID, vSemesterID INT DEFAULT -1;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;

    # todo: move to php layer
    IF  NOT InternalIsTeacherBound(pTeacherID, pDisciplineID) THEN
        RETURN -1;
    END IF;

    SET vSemesterID = GetDisciplineProperty(pDisciplineID, 'semester');
    SET vStudentGroupID = GetStudentGroup(pStudentID, vSemesterID);

    # 2. check if student's group is bound yet
    SET vInGroup = EXISTS(
        SELECT * FROM disciplines_groups
        WHERE disciplines_groups.DisciplineID = pDisciplineID AND
              disciplines_groups.GroupID = vStudentGroupID
        LIMIT 1
    );

    IF vInGroup > 0 THEN # student in general group
        INSERT INTO disciplines_students (DisciplineID, StudentID, Type) VALUES (pDisciplineID, pStudentID, 'detach')
            ON DUPLICATE KEY UPDATE disciplines_students.Type = 'detach';
    ELSE
        DELETE FROM disciplines_students
            WHERE   disciplines_students.DisciplineID = pDisciplineID AND disciplines_students.StudentID = pStudentID
            LIMIT 1;
    END IF;
    RETURN LogBind(pDisciplineID, pTeacherID, pStudentID, 'detach', 'student');
END //

DROP FUNCTION IF EXISTS Discipline_BindTeacher//
CREATE FUNCTION Discipline_BindTeacher (
    pDisciplineID INT,
    pBindingTeacherID INT
) RETURNS int(11)
NO SQL
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;

    # try to insert BindingTeacher in access list
    INSERT INTO disciplines_teachers (DisciplineID, TeacherID) VALUES (pDisciplineID, pBindingTeacherID)
        ON DUPLICATE KEY UPDATE # just stub
            disciplines_teachers.ID = LAST_INSERT_ID(disciplines_teachers.ID);
    RETURN 0;
END //

DROP FUNCTION IF EXISTS Discipline_UnbindTeacher//
CREATE FUNCTION Discipline_UnbindTeacher (
    pDisciplineID INT,
    pBindingTeacher INT
) RETURNS int(11)
NO SQL
BEGIN
    DELETE FROM disciplines_teachers
        WHERE   disciplines_teachers.DisciplineID = pDisciplineID AND disciplines_teachers.TeacherID = pBindingTeacher
        LIMIT 1;
    RETURN ROW_COUNT()-1;
END //

# assign new author to discipline
DROP FUNCTION IF EXISTS Discipline_Delegate//
CREATE FUNCTION Discipline_Delegate (
    pDisciplineID INT,
    pNewAuthorID INT
) RETURNS int(11)
NO SQL
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;

    SELECT Discipline_BindTeacher(pDisciplineID, pNewAuthorID) INTO @vTemp;
    UPDATE disciplines
        SET disciplines.AuthorID = pNewAuthorID
        WHERE disciplines.ID = pDisciplineID
        LIMIT 1;
    RETURN ROW_COUNT()-1;
END //

# erase all discipline's rates(and logs), unlock discipline for editing
DROP FUNCTION IF EXISTS ClearDiscipline//
DROP FUNCTION IF EXISTS Discipline_Clear//
CREATE FUNCTION Discipline_Clear (
        pAuthorTeacherID INT,
        pDisciplineID INT
    ) RETURNS int(11)
NO SQL
BEGIN
    IF NOT InternalIsTeacherAuthor(pAuthorTeacherID, pDisciplineID) THEN
        RETURN -1;
    END IF;

    # clear logs
    DELETE FROM logs_rating
        WHERE logs_rating.SubmoduleID IN
            (SELECT view_roadmap.SubmoduleID
                FROM view_roadmap
                WHERE view_roadmap.DisciplineID = pDisciplineID);

    # clear rating
    DELETE FROM rating_table
        WHERE rating_table.SubmoduleID IN
            (SELECT view_roadmap.SubmoduleID
             FROM view_roadmap
             WHERE view_roadmap.DisciplineID = pDisciplineID);
			 
	# clear exam_period_options
	DELETE FROM exam_period_options
        WHERE exam_period_options.SubmoduleID IN
            (SELECT view_roadmap.SubmoduleID
             FROM view_roadmap
             WHERE view_roadmap.DisciplineID = pDisciplineID);

    # unlock discipline
    UPDATE disciplines
        SET disciplines.IsLocked = 0
        WHERE disciplines.ID = pDisciplineID
        LIMIT 1;

    RETURN ROW_COUNT()-1;
END //

DROP FUNCTION IF EXISTS Discipline_Delete//
CREATE FUNCTION Discipline_Delete (pDisciplineID INT) RETURNS int(11)
NO SQL
BEGIN
    # delete roadmap
    DELETE FROM submodules
        WHERE submodules.moduleID in (
            select modules.id FROM modules
            WHERE modules.DisciplineID = pDisciplineID
        );

    DELETE FROM modules
        WHERE modules.DisciplineID = pDisciplineID;

    # detach all entities from discipline
    DELETE FROM disciplines_teachers
        WHERE disciplines_teachers.DisciplineID = pDisciplineID;
    DELETE FROM disciplines_students
        WHERE disciplines_students.DisciplineID = pDisciplineID;
    DELETE FROM disciplines_groups
        WHERE disciplines_groups.DisciplineID = pDisciplineID;
    DELETE FROM logs_binds_groups
        WHERE logs_binds_groups.DisciplineID = pDisciplineID;
    DELETE FROM logs_binds_students
        WHERE logs_binds_students.DisciplineID = pDisciplineID;

    # delete discipline
    DELETE FROM disciplines
        WHERE disciplines.ID = pDisciplineID
        LIMIT 1;

    RETURN 0;
END //

# get count of related with discipline records in rating_table
DROP FUNCTION IF EXISTS Discipline_CountRatings//
CREATE FUNCTION Discipline_CountRatings (pDisciplineID INT) RETURNS int(11)
READS SQL DATA
RETURN (
    SELECT COUNT(rating_table.StudentID) FROM view_roadmap
        LEFT JOIN rating_table ON rating_table.SubmoduleID = view_roadmap.SubmoduleID
        WHERE view_roadmap.DisciplineID = pDisciplineID
)//

DROP FUNCTION IF EXISTS RestrictAfterMilestone//
CREATE FUNCTION RestrictAfterMilestone (
    pDisciplineID INT,
    pMilestone INT
) RETURNS int(11)
NO SQL
BEGIN
    UPDATE disciplines
        SET disciplines.MilestoneDate = CURDATE(),
            disciplines.Milestone = pMilestone
        WHERE disciplines.ID = pDisciplineID
        LIMIT 1;
    RETURN 0;
END //

DROP FUNCTION IF EXISTS RestrictAfterMilestoneForCredits//
CREATE FUNCTION RestrictAfterMilestoneForCredits (
    pFacultyID INT,
    pMilestone INT,
    pSemesterID INT
) RETURNS int(11)
NO SQL
BEGIN
    UPDATE disciplines
        SET disciplines.MilestoneDate = CURDATE(),
            disciplines.Milestone = pMilestone
        WHERE   disciplines.FacultyID= pFacultyID AND
                disciplines.SemesterID = pSemesterID AND
                ( disciplines.ExamType = 'credit' OR disciplines.ExamType = 'grading_credit');
    RETURN 0;
END //

# -------------------------------------------------------------------------------------------
# Label: modules
# Label: roadmap
# -------------------------------------------------------------------------------------------

DROP FUNCTION IF EXISTS ChangeModuleName//
CREATE FUNCTION ChangeModuleName (
    pTeacherID INT,
    pModuleID INT,
    pName VARCHAR(200) CHARSET utf8
) RETURNS int(11)
NO SQL
BEGIN
    DECLARE vDisciplineID INT DEFAULT -1;
    DECLARE vAlreadySameName BOOL DEFAULT FALSE;

    SET vDisciplineID = (SELECT DisciplineID FROM modules WHERE ID = pModuleID LIMIT 1);
    IF InternalIsMapLocked(vDisciplineID) THEN
        RETURN -1;
    END IF;

    # Bodging, strange row_count() behaviour in some cases.
    # i.e. update 1 module row. In case it's new and old names are same, row_count value may be 0.
    SET vAlreadySameName = EXISTS(
        SELECT * FROM modules
        WHERE modules.ID = pModuleID AND modules.Type = 'regular' AND modules.Name = pName
        LIMIT 1
    );
    IF vAlreadySameName THEN
        return 0;
    END IF;


    UPDATE modules
        SET modules.Name = pName
        WHERE modules.ID = pModuleID AND modules.Type = 'regular'
        LIMIT 1;
    RETURN ROW_COUNT() - 1;
END //

DROP FUNCTION IF EXISTS AddModule//
CREATE FUNCTION AddModule (
    pTeacherID INT,
    pDisciplineID INT,
    pName VARCHAR(200) CHARSET utf8
) RETURNS int(11)
NO SQL
BEGIN
    DECLARE vOrderNum INT DEFAULT 0;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;

    IF  NOT InternalIsTeacherAuthor(pTeacherID, pDisciplineID) OR InternalIsMapLocked(pDisciplineID) THEN
        RETURN -2;
    END IF;

    # get free orderNum
    SET vOrderNum = COALESCE((
        SELECT MAX(modules.OrderNum)+1 FROM modules
        WHERE   modules.DisciplineID = pDisciplineID AND modules.Type = 'regular' LIMIT 1
    ), 1);

    INSERT INTO modules (Name, OrderNum, DisciplineID ) VALUES (pName, vOrderNum, pDisciplineID);
    RETURN LAST_INSERT_ID();
END //

DROP FUNCTION IF EXISTS AddModuleExamUnsafe//
CREATE FUNCTION AddModuleExamUnsafe (pDisciplineID INT) RETURNS int(11)
NO SQL
BEGIN
    DECLARE vModule, vChecker INT DEFAULT -1;
    DECLARE vIsExamExists BOOLEAN;

    IF InternalIsMapLocked(pDisciplineID) THEN
        RETURN -1;
    END IF;

    SET vIsExamExists = EXISTS(
        SELECT * FROM modules
        WHERE modules.DisciplineID = pDisciplineID AND modules.Type = 'exam'
        LIMIT 1
    );
    IF vIsExamExists THEN
        RETURN -2;
    END IF;

    INSERT INTO modules (Name, OrderNum, DisciplineID, Type) VALUES  ('Экзамен' , 3141592 , pDisciplineID, 'exam');
    SET vModule = LAST_INSERT_ID();
    # 3 attempt for pass exam
    SELECT AddSubmoduleUnsafe(vModule, 40, '', NULL, 'LandmarkControl') INTO @vTemp;
    SELECT AddSubmoduleUnsafe(vModule, 40, '', NULL, 'LandmarkControl') INTO @vTemp;
    SELECT AddSubmoduleUnsafe(vModule, 40, '', NULL, 'LandmarkControl') INTO @vTemp;
    RETURN vModule;
END //

DROP FUNCTION IF EXISTS AddModuleExam//
CREATE FUNCTION AddModuleExam (
    pTeacherID INT,
    pDisciplineID INT
) RETURNS int(11)
NO SQL
BEGIN
    DECLARE vModule, vChecker INT DEFAULT -1;
    DECLARE vIsExamExists BOOLEAN;
    IF  NOT InternalIsTeacherAuthor(pTeacherID, pDisciplineID) OR
        InternalIsMapLocked(pDisciplineID)
    THEN
        RETURN -1;
    END IF;

    RETURN AddModuleExamUnsafe(pDisciplineID);
END //

DROP FUNCTION IF EXISTS AddModuleExtra//
CREATE FUNCTION AddModuleExtra (
    pTeacherID INT,
    pDisciplineID INT
) RETURNS int(11)
NO SQL
BEGIN
    DECLARE vChecker, vModule, vType, vGap INT DEFAULT -1;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;
    IF  NOT InternalIsTeacherAuthor(pTeacherID, pDisciplineID)
    THEN
        RETURN -1;
    END IF;

    # try to find existing extra module
    SELECT modules.ID INTO vChecker
        FROM modules
        WHERE modules.DisciplineID = pDisciplineID AND modules.Type = 'extra'
        LIMIT 1;
    IF vChecker > 0 THEN
        RETURN -2;
    END IF;

    # add extra module
    INSERT INTO modules
        (Name, OrderNum, DisciplineID, Type)
        VALUES ('Добор баллов' , 2900666 , pDisciplineID, 'extra');


    # get discipline exam type
    SELECT  modules.ID, disciplines.ExamType INTO vModule, vType
        FROM modules
        INNER JOIN disciplines ON disciplines.ID = modules.DisciplineID
        WHERE   modules.DisciplineID = pDisciplineID AND
                modules.Type = 'extra'
        LIMIT 1;
    IF vModule <= 0 THEN
        RETURN -1;
    END IF;

    # 1 extra attempt for exam and 2 for credit
    SET vGap = -1;
    IF vType = 1 THEN # exam
        SET vGap = 7;
    END IF;
    IF vType = 2 OR vType = 3 THEN # credit, grading_credit
        SET vGap = 29;
        SET vChecker = AddSubmodule(pTeacherID, vModule, vGap, '', NULL, 'LandmarkControl');
    END IF;

    SET vChecker = AddSubmodule(pTeacherID, vModule, vGap, '', NULL, 'LandmarkControl');
    RETURN vModule;
END //

DROP FUNCTION IF EXISTS DeleteModule//
CREATE FUNCTION DeleteModule (
    pTeacherID INT,
    pModuleID INT
) RETURNS int(11)
NO SQL
BEGIN
    DECLARE vDisciplineID INT DEFAULT -1;

    # get discipline ID
    SET vDisciplineID = (
        SELECT disciplines.ID FROM modules
        INNER JOIN disciplines    ON  modules.DisciplineID = disciplines.ID AND disciplines.AuthorID = pTeacherID
        WHERE modules.ID = pModuleID LIMIT 1
    );

    # check rights
    IF  NOT InternalIsTeacherAuthor(pTeacherID, vDisciplineID) OR
        InternalIsMapLocked(vDisciplineID)
    THEN
        RETURN -1;
    END IF;

    DELETE FROM submodules
        WHERE submodules.ModuleID = pModuleID;
    DELETE FROM modules
        WHERE modules.ID = pModuleID;

    # restore continuous ordering
    SET @counter = 0;
    UPDATE modules
        SET modules.OrderNum = (@counter := @counter + 1)
        WHERE   modules.DisciplineID = vDisciplineID AND
                modules.Type = 'regular'
        ORDER BY modules.OrderNum ASC;

    RETURN 0;
END //

DROP FUNCTION IF EXISTS DeleteModuleExam//
CREATE FUNCTION DeleteModuleExam (
    pTeacherID INT,
    pDisciplineID INT
) RETURNS int(11)
NO SQL
BEGIN
    DECLARE vExamModuleID INT DEFAULT -1;
    IF  NOT InternalIsTeacherAuthor(pTeacherID, pDisciplineID) OR
        InternalIsMapLocked(pDisciplineID)
    THEN
        RETURN -1;
    END IF;

    # get exam module ID
    SELECT modules.ID INTO vExamModuleID
        FROM modules
        WHERE   modules.Type = 'exam' AND
                modules.DisciplineID = pDisciplineID
        LIMIT 1;
    IF vExamModuleID <= 0 THEN
        RETURN -1;
    END IF;

    DELETE FROM submodules
        WHERE vExamModuleID = submodules.ModuleID;
    DELETE FROM modules
        WHERE vExamModuleID = modules.ID
        LIMIT 1;

    RETURN 0;
END //

DROP FUNCTION IF EXISTS SwapModuleOrder//
CREATE FUNCTION SwapModuleOrder (
    pTeacherID INT,
    pModuleID1 INT,
    pModuleID2 INT
) RETURNS int(11)
NO SQL
BEGIN
    DECLARE vChecker, vOrder1, vOrder2,
            vDisciplineID1, vDisciplineID2 INT DEFAULT -1;

    # get disciplineID and orderNum for 1st module(pModuleID1)
    SELECT  modules.OrderNum,
            modules.DisciplineID
        INTO vOrder1, vDisciplineID1
        FROM modules
        INNER JOIN disciplines ON disciplines.ID = modules.DisciplineID
        WHERE   disciplines.AuthorID = pTeacherID AND
                modules.ID = pModuleID1 AND
                modules.Type = 'regular'
        LIMIT 1;

    # get disciplineID and orderNum for 2st module(pModuleID2)
    SELECT  modules.OrderNum,
            modules.DisciplineID
        INTO vOrder2, vDisciplineID2
        FROM modules
        INNER JOIN disciplines ON disciplines.ID = modules.DisciplineID
        WHERE   disciplines.AuthorID = pTeacherID AND
                modules.ID = pModuleID2 AND
                modules.Type = 'regular'
        LIMIT 1;

    # check that modules belong to one discipline, check rights
    IF  vDisciplineID1 != vDisciplineID2 OR vDisciplineID1 <= 0 OR
        InternalIsMapLocked(vDisciplineID1) THEN
        RETURN -1;
    END IF;

    # swap
    UPDATE modules
        SET modules.OrderNum = 271828
        WHERE modules.ID = pModuleID1;
    UPDATE modules
        SET modules.OrderNum = vOrder1
        WHERE modules.ID = pModuleID2
        LIMIT 1;
    UPDATE modules
        SET modules.OrderNum = vOrder2
        WHERE modules.ID = pModuleID1
        LIMIT 1;

    RETURN 0;
END //

DROP FUNCTION IF EXISTS AddModuleBonus//
CREATE FUNCTION AddModuleBonus (
    pTeacherID INT,
    pDisciplineID INT
)   RETURNS INT(11)
NO SQL
BEGIN
    DECLARE vChecker, vModuleID INT DEFAULT -1;
    IF  NOT InternalIsTeacherAuthor(pTeacherID, pDisciplineID) OR
        InternalIsMapLocked(pDisciplineID)
    THEN
        RETURN -1;
    END IF;

    # check existing of bonus module
    SELECT modules.ID INTO vChecker
        FROM modules
        WHERE   modules.DisciplineID = pDisciplineID AND
                modules.Type = 'bonus';
    IF vChecker > 0 THEN
        RETURN -2;
    END IF;

    INSERT INTO modules
        (Name, OrderNum, DisciplineID, Type)
        VALUES  ('Бонусные баллы' , 2141692 , pDisciplineID, 'bonus');

    SET vModuleID = LAST_INSERT_ID();
    SET vChecker = AddSubmodule(pTeacherID, vModuleID, 10, '', NULL, 'LandmarkControl');
    RETURN 0;
END //

DROP FUNCTION IF EXISTS GetBonusModule//
CREATE FUNCTION GetBonusModule (pDisciplineID INT) RETURNS int(11)
READS SQL DATA
RETURN (
    SELECT modules.ID FROM modules
        WHERE modules.DisciplineID = pDisciplineID AND modules.Type = 'bonus'
        LIMIT 1
)//

DROP FUNCTION IF EXISTS DeleteModuleBonus//
CREATE FUNCTION DeleteModuleBonus (
    pTeacherID INT,
    pDisciplineID INT
) RETURNS int(11)
NO SQL
BEGIN
    DECLARE vBonusModuleID INT DEFAULT -1;
    IF  NOT InternalIsTeacherAuthor(pTeacherID, pDisciplineID) OR
        InternalIsMapLocked(pDisciplineID)
    THEN
        RETURN -1;
    END IF;
    IF GetBonusModule(pDisciplineID) <= 0 THEN
        RETURN -1;
    END IF;

    DELETE FROM submodules
        WHERE vBonusModuleID = submodules.ModuleID;

    DELETE FROM modules
        WHERE vBonusModuleID = modules.ID
        LIMIT 1;

    RETURN 0;
END //

# -------------------------------------------------------------------------------------------
# Label: submodules
# Label: roadmap
# -------------------------------------------------------------------------------------------

DROP FUNCTION IF EXISTS ChangeSubmoduleMaxAndControl//
CREATE FUNCTION ChangeSubmoduleMaxAndControl (
    pTeacherID     INT,
    pSubmoduleID   INT,
    pMaxRate       INT,
    pControlType   VARCHAR(30) CHARSET utf8
) RETURNS int(11)
NO SQL
BEGIN
    DECLARE vChecker, vDisciplineID, vIsLocked, vNewDiscMaxRate, vCurRate INT DEFAULT -1;

    # check that discipline and submodule exists and doesn't locked
    SELECT  disciplines.IsLocked,
            disciplines.MaxRate - submodules.MaxRate + pMaxRate
            INTO vIsLocked, vNewDiscMaxRate
        FROM submodules
        INNER JOIN modules        ON  submodules.ModuleID = modules.ID
        INNER JOIN disciplines    ON  disciplines.ID = modules.DisciplineID
        WHERE submodules.ID = pSubmoduleID AND
              disciplines.AuthorID = pTeacherID
        LIMIT 1;
    IF  vIsLocked != 0 OR
        vNewDiscMaxRate > 100
    THEN
        RETURN -1;
    END IF;

    UPDATE submodules
        SET     submodules.MaxRate = pMaxRate,
                submodules.Type = pControlType
        WHERE submodules.ID = pSubmoduleID
        LIMIT 1;
    RETURN ROW_COUNT()-1;
END //

DROP FUNCTION IF EXISTS ChangeSubmoduleName//
CREATE FUNCTION ChangeSubmoduleName (
    pTeacherID INT,
    pSubmoduleID INT,
    pName VARCHAR(200) CHARSET utf8
) RETURNS int(11)
NO SQL
BEGIN
    DECLARE vIsLocked INT DEFAULT -1;

    SELECT disciplines.IsLocked INTO vIsLocked
        FROM view_roadmap
        INNER JOIN disciplines ON  disciplines.ID = view_roadmap.DisciplineID
        WHERE  view_roadmap.SubmoduleID = pSubmoduleID AND disciplines.AuthorID = pTeacherID
        LIMIT 1;
    IF  vIsLocked != 0 THEN
        RETURN -1;
    END IF;

    UPDATE submodules
        SET submodules.Name = pName
        WHERE submodules.ID = pSubmoduleID
        LIMIT 1;
    RETURN 0;
END //

DROP FUNCTION IF EXISTS ChangeSubmoduleDescription//
CREATE FUNCTION ChangeSubmoduleDescription (
    pTeacherID INT,
    pSubmoduleID INT,
    pDescription VARCHAR(200) CHARSET utf8
) RETURNS int(11)
NO SQL
BEGIN
    DECLARE vIsLocked INT DEFAULT -1;

    SELECT disciplines.IsLocked INTO vIsLocked
        FROM view_roadmap
        INNER JOIN disciplines ON disciplines.ID = view_roadmap.DisciplineID
        WHERE   view_roadmap.SubmoduleID = pSubmoduleID AND disciplines.AuthorID = pTeacherID
        LIMIT 1;
    IF  vIsLocked != 0 THEN
        RETURN -1;
    END IF;

    UPDATE submodules
        SET submodules.Description  = pDescription
        WHERE submodules.ID = pSubmoduleID
        LIMIT 1;
    RETURN 0;
END //

DROP FUNCTION IF EXISTS DeleteSubmodule//
CREATE FUNCTION DeleteSubmodule (
    pTeacherID INT,
    pSubmoduleID INT
) RETURNS int(11)
NO SQL
BEGIN
    DECLARE vIsLocked, vModuleID INT DEFAULT -1;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;

    SELECT modules.ID, disciplines.IsLocked
        INTO vModuleID, vIsLocked
        FROM submodules
        INNER JOIN modules ON modules.ID = submodules.ModuleID
        INNER JOIN disciplines ON modules.DisciplineID = disciplines.ID
        WHERE   disciplines.AuthorID = pTeacherID AND
                submodules.ID = pSubmoduleID
        LIMIT 1;
    IF  vIsLocked != 0 THEN
        RETURN -1;
    END IF;

    # handler will catch constraint violation
    DELETE FROM submodules
        WHERE submodules.ID = pSubmoduleID
        LIMIT 1;

    # restore continuous ordering
    SET @counter = 0;
    UPDATE submodules
        SET submodules.OrderNum = (@counter := @counter + 1)
        WHERE   submodules.ModuleID = vModuleID
        ORDER BY submodules.OrderNum ASC;

    RETURN 0;
END //

DROP FUNCTION IF EXISTS AddSubmoduleUnsafe//
CREATE FUNCTION AddSubmoduleUnsafe (
    pModuleID INT,
    pMaxRate INT,
    pName VARCHAR(200) CHARSET utf8,
    pDescription VARCHAR(200) CHARSET utf8,
    pControlType VARCHAR(30) CHARSET utf8
) RETURNS int(11)
NO SQL
BEGIN
    DECLARE vOrderNum, vIsLocked INT DEFAULT -1;
    DECLARE vDescription VARCHAR(200) CHARSET utf8 DEFAULT NULL;
    # DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;

    # check discipline lock
    SELECT disciplines.IsLocked INTO vIsLocked
        FROM modules
        INNER JOIN disciplines ON disciplines.ID = modules.DisciplineID
        WHERE modules.ID = pModuleID
        LIMIT 1;
    IF  vIsLocked != 0 THEN
        RETURN -2;
    END IF;

    # get free order
    SET vOrderNum = COALESCE((
        SELECT MAX(submodules.OrderNum) FROM submodules
        WHERE submodules.ModuleID = pModuleID LIMIT 1
    ), 0) + 1;

    SET vDescription = IF(vDescription = '', NULL, pDescription);
    INSERT INTO submodules (ModuleID, MaxRate, OrderNum, Name, Description, Type) VALUES
        (pModuleID, pMaxRate, vOrderNum, pName, vDescription, pControlType);
    RETURN  LAST_INSERT_ID();
END //

DROP FUNCTION IF EXISTS AddSubmodule//
CREATE FUNCTION AddSubmodule (
    pTeacherID INT,
    pModuleID INT,
    pMaxRate INT,
    pName VARCHAR(200) CHARSET utf8,
    pDescription VARCHAR(200) CHARSET utf8,
    pControlType VARCHAR(30) CHARSET utf8
) RETURNS int(11)
NO SQL
BEGIN
    DECLARE vIsLocked BOOLEAN DEFAULT TRUE;
    # DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;

    # check author and discipline lock
    SELECT NOT disciplines.AuthorID <=> pTeacherID INTO vIsLocked
        FROM modules
        INNER JOIN disciplines ON  disciplines.ID = modules.DisciplineID
        WHERE modules.ID = pModuleID
        LIMIT 1;
    IF vIsLocked THEN
        RETURN -2;
    END IF;

    RETURN AddSubmoduleUnsafe(pModuleID, pMaxRate, pName, pDescription, pControlType);
END //

DROP FUNCTION IF EXISTS SwapSubmoduleOrder//
CREATE FUNCTION SwapSubmoduleOrder (
    pTeacherID INT,
    pSubmoduleID1 INT,
    pSubmoduleID2 INT
) RETURNS int(11)
NO SQL
BEGIN
    DECLARE vDisciplineID, vOrder1, vOrder2,
            vModule1, vModule2 INT DEFAULT -1;

    SELECT  submodules.OrderNum,
            submodules.ModuleID,
            disciplines.ID
        INTO vOrder1, vModule1, vDisciplineID
        FROM submodules
        INNER JOIN modules        ON  submodules.ModuleID = modules.ID
        INNER JOIN disciplines    ON  disciplines.ID = modules.DisciplineID
        WHERE   disciplines.AuthorID = pTeacherID AND
                submodules.ID = pSubmoduleID1
        LIMIT 1;

    SELECT  submodules.OrderNum,
            submodules.ModuleID
        INTO vOrder2, vModule2
        FROM submodules
        INNER JOIN modules        ON  submodules.ModuleID = modules.ID
        INNER JOIN disciplines    ON  disciplines.ID = modules.DisciplineID
        WHERE   disciplines.AuthorID = pTeacherID AND
                submodules.ID = pSubmoduleID2
        LIMIT 1;

    # check, that modules exists and belong to one discipline
    IF  vModule1 <= 0 OR vModule1 != vModule2 OR
        InternalIsMapLocked(vDisciplineID)
    THEN
        RETURN -1;
    END IF;

    # swap
    UPDATE submodules
        SET submodules.OrderNum     = 271828
        WHERE submodules.ID = pSubmoduleID1
        LIMIT 1;
    UPDATE submodules
        SET submodules.OrderNum     = vOrder1
        WHERE submodules.ID = pSubmoduleID2
        LIMIT 1;
    UPDATE submodules
        SET submodules.OrderNum     = vOrder2
        WHERE submodules.ID = pSubmoduleID1
        LIMIT 1;
    RETURN 0;
END //

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

-- get id of student's last exam with rate
-- first exam submodule, if don't rated yet
DROP FUNCTION IF EXISTS GetExamRateID//
CREATE FUNCTION GetExamRateID (
    pRecordBook INT,
    pDisciplineID INT
) RETURNS int(11)
READS SQL DATA
RETURN COALESCE((
    SELECT view_roadmap.SubmoduleID FROM view_roadmap
    LEFT JOIN rating_table AS rt on rt.SubmoduleID = view_roadmap.SubmoduleID AND rt.RecordBookID = pRecordBook
        WHERE view_roadmap.DisciplineID = pDisciplineID AND view_roadmap.ModuleType = 'exam'
    ORDER BY (rt.Rate IS NOT NULL) DESC, view_roadmap.SubmoduleOrderNum DESC
    LIMIT 1
), -1)//

# Вычисление максимального балла для submodule
DROP FUNCTION IF EXISTS CalculateMaxRateForExtra//
CREATE FUNCTION CalculateMaxRateForExtra (
    pDisciplineID INT,
    pStudentID INT
) RETURNS int(11)
READS SQL DATA
BEGIN
    DECLARE vExamType enum('exam', 'credit', 'grading_credit') DEFAULT NULL;
    DECLARE vDiscID, vLim, vResult INT DEFAULT 0;

    # get disc type
    SET vExamType = GetDisciplineProperty(pDisciplineID, 'type');

    # submodule doesn't exists
    IF vExamType IS NULL OR vExamType <= 0 THEN
        RETURN -1;
    END IF;
    SET vLim = IF(vExamType = 'exam', 38, 60);

    SELECT SUM(IF(view_roadmap.ModuleType = 'regular', rating_table.Rate, 0)) INTO vResult
        FROM view_roadmap
        LEFT JOIN rating_table ON rating_table.StudentID = pStudentID AND
                                    rating_table.SubmoduleID = view_roadmap.SubmoduleID
        WHERE view_roadmap.DisciplineID = pDisciplineID
        LIMIT 1;
    RETURN vLim - vResult;
END //

DROP FUNCTION IF EXISTS SetStudentRate//
DROP FUNCTION IF EXISTS Discipline_SetRate//
CREATE FUNCTION Discipline_SetRate (
    pTeacherID INT,
    pStudentID INT,
    pSubmoduleID INT,
    pRate INT
) RETURNS int(11)
NO SQL
BEGIN
    DECLARE vDisciplineID, vMaxRate, vModuleType INT DEFAULT -1;
    DECLARE vIsOver, vIsLocked, vIsUsed BOOLEAN DEFAULT FALSE;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;

    IF pRate < 0 THEN
        INSERT INTO logs_rating (StudentID, SubmoduleID, TeacherID, Rate, Action ) VALUES
            (pStudentID, pSubmoduleID, pTeacherID, pRate, 'delete');

        # TODO: extract method log rate
        DELETE FROM rating_table
            WHERE   rating_table.StudentID = pStudentID AND
                    rating_table.SubmoduleID = pSubmoduleID
            LIMIT 1;

        RETURN 0;
    END IF;

    SET vIsOver = TRUE;
    SELECT  disciplines.ID,
            disciplines.IsLocked,
            disciplines.Milestone,
            submodules.IsUsed,
            submodules.maxRate,
            modules.Type
        INTO vDisciplineID, vIsLocked, vIsOver, vIsUsed, vMaxRate, vModuleType
        FROM submodules
        INNER JOIN modules                ON  submodules.ModuleID = modules.ID
        INNER JOIN disciplines            ON  modules.DisciplineID = disciplines.ID
        WHERE   submodules.ID = pSubmoduleID
        LIMIT 1;

    # correct max rate for extra module
    IF vModuleType = 4 THEN # 4 - extra