R__functions.sql

END //

-- Получение внутреннего ID по коду справочника из 1С
DROP FUNCTION IF EXISTS Discipline_GetIDFromExternalID//
CREATE FUNCTION Discipline_GetIDFromExternalID (
    pDisciplineExternalID VARCHAR(9)
) RETURNS int(11)
READS SQL DATA
    BEGIN
        DECLARE pID INT DEFAULT -1;

        SELECT disciplines.ID INTO pID
        FROM disciplines
        WHERE disciplines.ExternalID = pDisciplineExternalID
        LIMIT 1;

        RETURN pID;
    END //

-- Обновление данных дисциплины при синхронизации
DROP FUNCTION IF EXISTS Discipline_ChangeInfo//
CREATE FUNCTION Discipline_ChangeInfo (
    pDisciplineID INT,
    pTeacherID INT,
    pGradeID INT,
    pSubjectID INT,
    pExamType enum('exam', 'credit', 'grading_credit'),
    pLectureCount INT,
    pPracticeCount INT,
    pLabCount INT,
    pFacultyID INT,
    pSemesterID INT,
    pSubtype enum('scientific_coursework', 'disciplinary_coursework')
) RETURNS int(11)
NO SQL
BEGIN
    DECLARE vDisciplineID INT DEFAULT -1;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;

    IF pSubtype IS NOT NULL THEN
        SET pSubjectID = 346;
    END IF;

    # create discipline
    UPDATE `disciplines`
        SET disciplines.AuthorID = COALESCE(pTeacherID, disciplines.AuthorID),
            disciplines.GradeID = COALESCE(pGradeID, disciplines.GradeID),
            disciplines.SubjectID = COALESCE(pSubjectID, disciplines.SubjectID),
            disciplines.ExamType = COALESCE(pExamType, disciplines.ExamType),
            disciplines.LectureCount = COALESCE(pLectureCount, disciplines.LectureCount),
            disciplines.PracticeCount = COALESCE(pPracticeCount, disciplines.PracticeCount),
            disciplines.LabCount = COALESCE(pLabCount, disciplines.LabCount),
            disciplines.SemesterID = COALESCE(pSemesterID, disciplines.SemesterID),
            disciplines.FacultyID = COALESCE(pFacultyID, disciplines.FacultyID),
            disciplines.Subtype = COALESCE(pSubtype, disciplines.Subtype)
    WHERE disciplines.ID = pDisciplineID;

    RETURN 0;
END //


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

    UPDATE disciplines
        SET disciplines.SubjectID = pSubjectID
        WHERE disciplines.ID = pDisciplineID
        LIMIT 1;
    RETURN ROW_COUNT()-1;
END //

DROP FUNCTION IF EXISTS ChangeDisciplineSubject//
CREATE FUNCTION ChangeDisciplineSubject (
    pTeacherID INT,
    pDisciplineID INT,
    pSubjectID INT
) RETURNS int(11)
NO SQL
BEGIN
    # TODO: move to php
    IF  NOT InternalIsTeacherAuthor(pTeacherID, pDisciplineID) OR
        InternalIsMapLocked(pDisciplineID)
    THEN
        RETURN -1;
    END IF;
    RETURN ChangeDisciplineSubjectUnsafe(pDisciplineID, pSubjectID);
END //

DROP FUNCTION IF EXISTS GetMilestone//
CREATE FUNCTION GetMilestone (
    pFacultyID INT,
    pSemesterID INT
) RETURNS int(11)
READS SQL DATA
BEGIN
    DECLARE vMilestone, vCounter INT DEFAULT 0;

    # get most frequent milestone
    SELECT COUNT(*) AS 'cnt', disciplines.Milestone INTO vCounter, vMilestone
        FROM disciplines
        WHERE disciplines.SemesterID = pSemesterID AND disciplines.FacultyID = pFacultyID
        GROUP BY disciplines.Milestone
        ORDER BY cnt DESC
        LIMIT 1;

    RETURN vMilestone;
END //

-- remove all students from discipline (separate bound & in general groups)
DROP FUNCTION IF EXISTS DetachAllStudents//
CREATE FUNCTION DetachAllStudents (pDisciplineID INT) RETURNS int(11)
NO SQL
BEGIN
    DELETE FROM disciplines_groups
        WHERE disciplines_groups.DisciplineID = pDisciplineID;
    DELETE FROM disciplines_students
        WHERE disciplines_students.DisciplineID = pDisciplineID;
    RETURN 0;
END //

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

    # set grade
    UPDATE disciplines
        SET disciplines.GradeID = pGradeID
        WHERE disciplines.ID = pDisciplineID
        LIMIT 1;
    RETURN ROW_COUNT()-1;
END //


-- todo: check
DROP FUNCTION IF EXISTS ChangeDisciplineControl//
CREATE FUNCTION ChangeDisciplineControl (
    pTeacherID INT,
    pDisciplineID INT,
    pExamType enum('exam', 'credit', 'grading_credit')
)   RETURNS int(11)
NO SQL
BEGIN
    DECLARE vChecker, vExtraMax, vExtraID INT DEFAULT -1;
    DECLARE vOldExamType enum('exam', 'credit', 'grading_credit');
    DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;

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

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

    # check type changing: exam <-> credit/grading_credit
    IF NOT (vOldExamType = 'exam' XOR pExamType != 'exam') THEN

        # TODO: extract method addExtraModule
        IF pExamType = 'exam' THEN # change to exam
            # count discipline's current max rate
            SET vChecker = GetDisciplineProperty(pDisciplineID, 'maxrate');
            IF vChecker >= 61 THEN # can't add exam module ( > 100 points)
                RETURN 1;
            END IF;
            SET vChecker = AddModuleExamUnsafe(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, RecordBookID, Type)
            VALUES (pDisciplineID, pTeacherID, EntityID, pAttach);
    END IF;
    RETURN ROW_COUNT()-1;
END //

DROP FUNCTION IF EXISTS RemoveStudentsAttach//
CREATE FUNCTION RemoveStudentsAttach (pDisciplineID INT, pGroupID INT) RETURNS INT
NO SQL
BEGIN
    DECLARE vSemesterID INT DEFAULT GetDisciplineProperty(pDisciplineID, 'semester');

    DELETE FROM disciplines_students
        WHERE   disciplines_students.DisciplineID = pDisciplineID AND
                disciplines_students.RecordBookID IN (
                    SELECT students_groups.RecordBookID FROM students_groups
                    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 (pTeacherID IS NOT NULL AND 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 (pTeacherID IS NOT NULL AND 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,
    pRecordBookID 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 pTeacherID IS NOT NULL AND NOT InternalIsTeacherBound(pTeacherID, pDisciplineID) THEN
        RETURN -1;
    END IF;

    SET vSemesterID = GetDisciplineProperty(pDisciplineID, 'semester');
    SET vStudentGroupID = GetStudentGroup(pRecordBookID, 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.RecordBookID = pRecordBookID
            LIMIT 1;
    ELSE # bind stand alone student ;(
        INSERT INTO disciplines_students (DisciplineID, RecordBookID, Type) VALUES (pDisciplineID, pRecordBookID, 'attach')
            ON DUPLICATE KEY UPDATE
                disciplines_students.Type = 'attach';
    END IF;

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

DROP FUNCTION IF EXISTS UnbindStudent//
CREATE FUNCTION UnbindStudent (
    pTeacherID INT,
    pDisciplineID INT,
    pRecordBookID 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 pTeacherID IS NOT NULL AND NOT InternalIsTeacherBound(pTeacherID, pDisciplineID) THEN
        RETURN -1;
    END IF;

    SET vSemesterID = GetDisciplineProperty(pDisciplineID, 'semester');
    SET vStudentGroupID = GetStudentGroup(pRecordBookID, 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, RecordBookID, Type)
            VALUES (pDisciplineID, pRecordBookID, 'detach')
            ON DUPLICATE KEY UPDATE disciplines_students.Type = 'detach';
    ELSE
        DELETE FROM disciplines_students
            WHERE   disciplines_students.DisciplineID = pDisciplineID AND
                    disciplines_students.RecordBookID = pRecordBookID
            LIMIT 1;
    END IF;
    RETURN LogBind(pDisciplineID, pTeacherID, pRecordBookID, '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;