functions.sql

            SET vChecker = InternalIsMapCreated(pDisciplineID);
            IF vChecker >= 61 THEN # can't add exam module ( > 100 points)
                RETURN 1;
            END IF;
            SET vChecker = AddModuleExam(pTeacherID, pDisciplineID);

            # delete extra submodules(only 1 extra for exam)
            DELETE FROM `submodules`
                WHERE submodules.OrderNum > 1 AND submodules.ModuleID = vExtraID;

            SET vExtraMax = 7;
        ELSE # change to credit
            SET vExtraMax = 29;
            SET vChecker = DeleteModuleExam(pTeacherID, pDisciplineID);
            # 2 extra submodules (1 already created for exam)
            SET vChecker = AddSubmodule(pTeacherID, vExtraID, vExtraMax, '', NULL, 'LandmarkControl');
        END IF;
    END IF;

    # set new exam type
    UPDATE `disciplines`
        SET disciplines.ExamType = pExamType
        WHERE disciplines.ID = pDisciplineID
        LIMIT 1;

    # set max rate for extra
    UPDATE `submodules`
        SET submodules.MaxRate = vExtraMax
        WHERE submodules.ModuleID = vExtraID;
    RETURN 0;
END //

DROP FUNCTION IF EXISTS ChangeDisciplineHours//
CREATE FUNCTION `ChangeDisciplineHours` (
    `pTeacherID` INT,
    `pDisciplineID` INT,
    `pHours` INT,
    `pType` INT
    # Type: 0 - Practice Hours, 1 - Lecture Hours, 2 - Lab Hours
)   RETURNS int(11)
    NO SQL
BEGIN
    IF NOT InternalIsTeacherAuthor(pTeacherID, pDisciplineID) THEN
        RETURN -1;
    END IF;

    CASE pType
    WHEN 0 THEN
        UPDATE `disciplines`
            SET disciplines.PracticeCount = pHours
            WHERE disciplines.ID = pDisciplineID
            LIMIT 1;
    WHEN 1 THEN
        UPDATE `disciplines`
            SET disciplines.LectureCount = pHours
            WHERE disciplines.ID = pDisciplineID
            LIMIT 1;
    WHEN 2 THEN
        UPDATE `disciplines`
            SET disciplines.LabCount = pHours
            WHERE disciplines.ID = pDisciplineID
            LIMIT 1;
    END CASE;
    RETURN ROW_COUNT()-1;
END //

DROP FUNCTION IF EXISTS LogBind//
CREATE FUNCTION `LogBind` (
    `pDisciplineID` INT,
    `pTeacherID` INT,
    `EntityID` INT,
    `pAttach` enum('attach', 'detach'),
    `pType` enum('group', 'student')
) RETURNS int(11) # 0 - success, < 0 - failed
NO SQL
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;

    IF pType = 'group' THEN
        INSERT INTO `logs_binds_groups`
            (`DisciplineID`, `TeacherID`, `GroupID`, `Type`)
            VALUES (pDisciplineID, pTeacherID, EntityID, pAttach);
    ELSEIF pType = 'student' THEN
        INSERT INTO `logs_binds_students`
            (`DisciplineID`, `TeacherID`, `StudentID`, `Type`)
            VALUES (pDisciplineID, pTeacherID, EntityID, pAttach);
    END IF;
    RETURN ROW_COUNT()-1;
END //

DROP FUNCTION IF EXISTS BindGroup//
CREATE FUNCTION `BindGroup` (
        `pTeacherID` INT,
        `pDisciplineID` INT,
        `pGroupID` INT
    ) RETURNS int(11)
    NO SQL
BEGIN
    DECLARE vSemesterID INT DEFAULT -1;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -3;
    SET @isAttached := -1;


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

    # bind whole group
    INSERT INTO `disciplines_groups`
        (DisciplineID, GroupID)
        VALUES ( pDisciplineID, pGroupID )
        ON DUPLICATE KEY UPDATE
            disciplines_groups.ID = ( @isAttached := LAST_INSERT_ID(disciplines_groups.ID) );

    IF @isAttached > 0 THEN # group was attached
        SET vSemesterID = GetDisciplineProperty(pDisciplineID, 'semester');

        # delete students of this group which were bound to discipline before
        DELETE FROM `disciplines_students`
        WHERE   disciplines_students.DisciplineID = pDisciplineID AND
                disciplines_students.StudentID IN (
                    SELECT students_groups.StudentID
                    FROM `students_groups`
                    WHERE   students_groups.GroupID = pGroupID AND
                            students_groups.SemesterID = vSemesterID
                );
        RETURN 1;
    END IF;

    RETURN LogBind(pDisciplineID, pTeacherID, pGroupID, 'attach', 'group');
END //



DROP FUNCTION IF EXISTS BindStudent//
CREATE FUNCTION `BindStudent` (
        `pTeacherID` INT,
        `pDisciplineID` INT,
        `pStudentID` INT
    )   RETURNS int(11)
    NO SQL
BEGIN
    DECLARE vChecker, vStudentGroupID, vTemp, vSemesterID INT DEFAULT -1;
    DECLARE vInGroup BOOLEAN DEFAULT FALSE;
    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 UnbindGroup//
CREATE FUNCTION `UnbindGroup` (
        `pTeacherID` INT,
        `pDisciplineID` INT,
        `pGroupID` INT
    ) RETURNS int(11)
    NO SQL
BEGIN
    DECLARE vSemesterID INT DEFAULT -1;

    # 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;

    SET vSemesterID = GetDisciplineProperty(pDisciplineID, 'semester');

    # delete attached, and detached (doesn't take disc in any case)
    DELETE FROM `disciplines_students`
    WHERE disciplines_students.DisciplineID = pDisciplineID AND
          disciplines_students.StudentID IN (
              SELECT students_groups.StudentID
                 FROM `students_groups`
                 WHERE  students_groups.GroupID = pGroupID AND
                        students_groups.SemesterID = vSemesterID
          );

    RETURN LogBind(pDisciplineID, pTeacherID, pGroupID, 'detach', 'group');
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;


    IF  NOT InternalIsTeacherAuthor(pTeacherID, pDisciplineID) OR
        InternalIsMapLocked(pDisciplineID)
    THEN
        RETURN -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 UnbindTeacher//
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 DelegateDiscipline//
DROP FUNCTION IF EXISTS Discipline_Delegate//
CREATE FUNCTION `Discipline_Delegate` (
    `pDisciplineID` INT,
    `pNewAuthorID` INT
) RETURNS int(11)
    NO SQL
BEGIN
    DECLARE vTemp INT DEFAULT 0;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;

    SET vTemp = Discipline_BindTeacher(pDisciplineID, pNewAuthorID);

    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//
CREATE FUNCTION `ClearDiscipline` (
        `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);

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

    RETURN ROW_COUNT()-1;
END //



DROP FUNCTION IF EXISTS DeleteDiscipline//
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)
    NO SQL
BEGIN
    DECLARE vRes INT DEFAULT 0;

    SELECT COUNT(rating_table.StudentID) INTO vRes
        FROM `view_roadmap`
        LEFT JOIN `rating_table` ON rating_table.SubmoduleID = view_roadmap.SubmoduleID
        WHERE view_roadmap.DisciplineID = pDisciplineID;

    RETURN vRes;
END //



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
    UPDATE `modules`
        SET modules.Name = pName
        WHERE modules.ID = pModuleID AND
              modules.Type = 'regular' AND
              NOT InternalIsMapLocked(modules.DisciplineID)
        LIMIT 1;
    RETURN ROW_COUNT();
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
    SELECT MAX(modules.OrderNum)+1 INTO vOrderNum
        FROM `modules`
        WHERE   modules.DisciplineID = pDisciplineID AND modules.Type = 'regular'
        LIMIT 1;
    IF vOrderNum IS NULL THEN # TODO: check unreachable code
        SET vOrderNum = 1;
    END IF;

    INSERT INTO `modules`
        (Name, OrderNum, DisciplineID )
        VALUES (pName, vOrderNum, pDisciplineID);
    RETURN LAST_INSERT_ID();
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;

    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
    SET vChecker = AddSubmodule(pTeacherID, vModule, 40, '', NULL, 'LandmarkControl');
    SET vChecker = AddSubmodule(pTeacherID, vModule, 40, '', NULL, 'LandmarkControl');
    SET vChecker = AddSubmodule(pTeacherID, vModule, 40, '', NULL, 'LandmarkControl');
    RETURN vModule;
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
    SELECT disciplines.ID INTO vDisciplineID
        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 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;

    # get bonus module ID
    -- todo: extract method
    SELECT modules.ID INTO vBonusModuleID
        FROM `modules`
        WHERE   modules.Type = 'bonus' AND
                modules.DisciplineID = pDisciplineID
        LIMIT 1;
    IF vBonusModuleID <= 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 `submodules`
        INNER JOIN `modules`        ON  submodules.ModuleID = modules.ID
        INNER JOIN `disciplines`    ON  disciplines.ID = modules.DisciplineID
        WHERE   disciplines.AuthorID = pTeacherID AND
                submodules.ID = pSubmoduleID
        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 `submodules`
        INNER JOIN `modules`        ON  submodules.ModuleID = modules.ID
        INNER JOIN `disciplines`    ON  disciplines.ID = modules.DisciplineID
        WHERE   disciplines.AuthorID = pTeacherID AND
                submodules.ID = pSubmoduleID
        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 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 vOrderNum, vIsLocked INT DEFAULT -1;
    DECLARE vDescription VARCHAR(200) CHARSET utf8 DEFAULT pDescription;
    # DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;

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

    # get free order
    SET vOrderNum = 0;
    SELECT MAX(submodules.OrderNum) INTO vOrderNum
        FROM `submodules`
        WHERE submodules.ModuleID = pModuleID
        LIMIT 1;
    IF vOrderNum IS NULL THEN
        SET vOrderNum = 0;
    END IF;
    SET vOrderNum = vOrderNum + 1;

    # insert submodule

    IF vDescription = '' THEN
        SET vDescription = NULL;
    END IF;