DELIMITER //

DROP FUNCTION IF EXISTS GetGradeID//
DROP FUNCTION IF EXISTS SetCurSemesterID//
DROP FUNCTION IF EXISTS SetHashKey//

drop function if exists InternalIsTeacherBinded//
DROP FUNCTION IF EXISTS GetSemesterID//

# -------------------------------------------------------------------------------------------
# Label: abbreviations
# -------------------------------------------------------------------------------------------

# abbreviation: abbr
# specialization: spec
# department: dep

# -------------------------------------------------------------------------------------------
# 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
    NO SQL
BEGIN
    DECLARE vChecker boolean DEFAULT -1;

    SELECT disciplines.isLocked INTO vChecker
        FROM `disciplines`
        WHERE pDisciplineID = disciplines.ID
    LIMIT 1;
    RETURN  ( vChecker > 0 );
END //


# check, that student really take this course
DROP FUNCTION IF EXISTS InternalIsStudentAttached//
CREATE FUNCTION `InternalIsStudentAttached`
    (`pStudentID` INT, `pDisciplineID` INT) RETURNS BOOLEAN
    NO SQL
BEGIN
    RETURN EXISTS(
        SELECT * FROM `view_disciplines_students`
        WHERE view_disciplines_students.StudentID = pStudentID AND
              view_disciplines_students.DisciplineID = pDisciplineID AND
              (view_disciplines_students.AttachType IS NULL OR
                  view_disciplines_students.AttachType = 'attach')
        );
END //


# check, that teacher teach this course
drop function if exists InternalIsTeacherBounded//
CREATE FUNCTION `InternalIsTeacherBounded`
    (   `pTeacherID` INT, `pDisciplineID` INT) RETURNS BOOLEAN
    NO SQL
BEGIN
    RETURN EXISTS (SELECT * FROM `disciplines_teachers`
                   WHERE disciplines_teachers.TeacherID = pTeacherID AND
                         disciplines_teachers.DisciplineID = pDisciplineID);
END //


DROP FUNCTION IF EXISTS InternalIsTeacherAuthor//
CREATE FUNCTION `InternalIsTeacherAuthor`
    (   `pTeacherID` INT, `pDisciplineID` INT) RETURNS BOOLEAN
    NO SQL
BEGIN
    RETURN EXISTS (SELECT * FROM `disciplines`
                    WHERE disciplines.ID = pDisciplineID AND disciplines.AuthorID = pTeacherID);
END //


DROP FUNCTION IF EXISTS GetDisciplineMaxRate//
CREATE FUNCTION `GetDisciplineMaxRate`
    (   `pDisciplineID` INT) RETURNS int(11)
    NO SQL
BEGIN
    DECLARE vMaxRate INT DEFAULT 0;

    # discipline map consist of submodules, we sum all their max rates
    SELECT SUM(submodules.MaxRate)
        INTO vMaxRate
        FROM `submodules`
        INNER JOIN `modules`
            ON  submodules.ModuleID = modules.ID AND (modules.Type = 'regular' OR modules.Type = 'exam')
        WHERE modules.DisciplineID = pDisciplineID;

    RETURN vMaxRate;
END //


DROP FUNCTION IF EXISTS GetRateForDisc//
CREATE FUNCTION `GetRateForDisc`
    (   `pStudentID` INT, `pDisciplineID` INT) RETURNS int(11)
    NO SQL
BEGIN
    DECLARE vRate INT DEFAULT -1;

    SELECT SUM(rating_table.Rate)
        INTO vRate
        FROM `rating_table`
        INNER JOIN `submodules` ON  rating_table.SubmoduleID = submodules.ID
        INNER JOIN `modules`    ON  submodules.ModuleID = modules.ID AND
                                    modules.DisciplineID = pDisciplineID
        WHERE   rating_table.StudentID = pStudentID AND
            (   modules.Type != 'exam' OR
                submodules.ID =
                    (   SELECT submodules.ID
                        FROM `submodules`
                        INNER JOIN `rating_table` ON rating_table.SubModuleID = submodules.ID
                        WHERE   submodules.ModuleID = modules.ID AND
                                rating_table.StudentID = pStudentID
                        ORDER BY submodules.OrderNum DESC
                        LIMIT 1
                    )
            )
        LIMIT 1;

    RETURN  vRate;
END //


DROP FUNCTION IF EXISTS GetRateForDiscSemester//
CREATE FUNCTION `GetRateForDiscSemester`
    (   `pStudentID` INT, `pDisciplineID` INT) RETURNS int(11)
    NO SQL
BEGIN
    DECLARE vRate INT DEFAULT -1;

    SELECT SUM(rating_table.Rate)
        INTO vRate
        FROM `rating_table`
        INNER JOIN `submodules` ON  rating_table.SubmoduleID = submodules.ID
        INNER JOIN `modules`    ON  submodules.ModuleID = modules.ID
        WHERE   rating_table.StudentID = pStudentID AND
                modules.DisciplineID = pDisciplineID AND
                modules.Type = 'regular'
        LIMIT 1;

    RETURN vRate;
END //


DROP FUNCTION IF EXISTS GetRateForDiscBonus//
CREATE FUNCTION `GetRateForDiscBonus`
    (   `pStudentID` INT, `pDisciplineID` INT) RETURNS int(11)
    NO SQL
BEGIN
    DECLARE vRate INT DEFAULT -1;

    SELECT SUM(rating_table.Rate)
        INTO vRate
        FROM `rating_table`
        INNER JOIN `submodules` ON  rating_table.SubmoduleID = submodules.ID
        INNER JOIN `modules`    ON  submodules.ModuleID = modules.ID
        WHERE   rating_table.StudentID = pStudentID AND
                modules.DisciplineID = pDisciplineID AND
                modules.Type = 'bonus'
        LIMIT 1;

    RETURN  vRate;
END //




DROP FUNCTION IF EXISTS GetRateForDiscExam//
CREATE FUNCTION `GetRateForDiscExam`
    (   `pStudentID` INT, `pDisciplineID` INT) RETURNS int(11)
    NO SQL
BEGIN
    DECLARE vRate INT DEFAULT -1;

    SELECT rating_table.Rate
        INTO vRate
        FROM `rating_table`
        INNER JOIN `submodules` ON  rating_table.SubmoduleID = submodules.ID
        INNER JOIN `modules`    ON  submodules.ModuleID = modules.ID
        WHERE   rating_table.StudentID = pStudentID AND
                modules.DisciplineID = pDisciplineID AND
                modules.Type = 'exam'
        ORDER BY submodules.OrderNum DESC
        LIMIT 1;

    RETURN  vRate;
END //


DROP FUNCTION IF EXISTS GetRateForDiscExamNum//
CREATE FUNCTION `GetRateForDiscExamNum`
    (   `pStudentID` INT, `pDisciplineID` INT, `pOrderNum` INT
    ) RETURNS int(11)
    NO SQL
BEGIN
    DECLARE vRate INT DEFAULT -1;

    SELECT rating_table.Rate
        INTO vRate
        FROM `rating_table`
        INNER JOIN `submodules` ON  rating_table.SubmoduleID = submodules.ID
        INNER JOIN `modules`    ON  submodules.ModuleID = modules.ID
        WHERE   rating_table.StudentID = pStudentID AND
                modules.DisciplineID = pDisciplineID AND
                modules.Type = 'exam' AND
                submodules.OrderNum = pOrderNum
        LIMIT 1;

    RETURN  vRate;
END //


DROP FUNCTION IF EXISTS GetRateForDiscExtra//
CREATE FUNCTION `GetRateForDiscExtra`
    (   `pStudentID` INT, `pDisciplineID` INT, `pOrderNum` INT
    ) RETURNS int(11)
    NO SQL
BEGIN
    DECLARE vRate INT DEFAULT -1;

    SELECT rating_table.Rate
        INTO vRate
        FROM `rating_table`
        INNER JOIN `submodules` ON  rating_table.SubmoduleID = submodules.ID
        INNER JOIN `modules`    ON  submodules.ModuleID = modules.ID
        WHERE   rating_table.StudentID = pStudentID AND
                modules.DisciplineID = pDisciplineID AND
                modules.Type = 'extra' AND
                submodules.OrderNum = pOrderNum
        LIMIT 1;

    RETURN  vRate;
END //


# check, if any module is created
DROP FUNCTION IF EXISTS InternalIsMapCreated//
CREATE FUNCTION `InternalIsMapCreated`
    (   `pDisciplineID` INT) RETURNS int(11)
    NO SQL
BEGIN
    RETURN EXISTS (
        SELECT * FROM `view_disciplines_results`
        WHERE view_disciplines_results.DisciplineID = pDisciplineID AND
              view_disciplines_results.DisciplineRateMax = 100
    );
END //



# set notification flag
DROP FUNCTION IF EXISTS InternalNotify//
CREATE FUNCTION `InternalNotify`        (   `AccountID` INT
                                        )   RETURNS int(11)
    NO SQL
BEGIN
    UPDATE `accounts`
        SET accounts.Notification = 1
        WHERE accounts.ID = AccountID
        LIMIT 1;
    RETURN ROW_COUNT()-1;
END //




# -------------------------------------------------------------------------------------------
# Label: preferences
# Label: magic
# -------------------------------------------------------------------------------------------

DROP FUNCTION IF EXISTS SetSettings//
CREATE FUNCTION `SetSettings`
    (`pKey` VARCHAR(50) CHARSET utf8, `pVal` INT, `pValS` VARCHAR(300) CHARSET utf8
    ) RETURNS int(11)
    NO SQL
BEGIN
    INSERT INTO `general_settings`
        (Val, ValS, Name) VALUES(pVal, pValS, pKey)
        ON DUPLICATE KEY UPDATE
            general_settings.Val = pVal,
            general_settings.ValS = pValS;
        RETURN 0;
END//



DROP FUNCTION IF EXISTS SetBitmaskByPagename//
CREATE FUNCTION `SetBitmaskByPagename`
    (`pPagename` TEXT CHARSET utf8, `pMask` INT) RETURNS int(11)
    NO SQL
BEGIN
    INSERT INTO `page_access`
        (Pagename, Bitmask) VALUES(pPagename, pMask)
        ON DUPLICATE KEY UPDATE
            page_access.Bitmask = pMask;
    RETURN 0;
END //



DROP FUNCTION IF EXISTS GetBitmaskByPagename//
CREATE FUNCTION `GetBitmaskByPagename` (`pPagename` TEXT CHARSET utf8) RETURNS int(11)
    NO SQL
BEGIN
    RETURN (SELECT page_access.Bitmask
                FROM `page_access`
                WHERE page_access.Pagename = pPagename
                LIMIT 1);
END //




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

DROP FUNCTION IF EXISTS SetSemesterID//
CREATE FUNCTION `SetSemesterID` (`pSemesterID` INT) RETURNS int(11)
    NO SQL
BEGIN
    SET @CurrentSemesterID = pSemesterID;
    RETURN 0;
END //




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

DROP FUNCTION IF EXISTS CreateStudyGroup//
CREATE FUNCTION `CreateStudyGroup`
    (   `pGradeID` INT, `pGroupNum` INT,
        `pSpecializationID` INT, `pGroupName` VARCHAR(50) CHARSET utf8
    )   RETURNS int(11)
    NO SQL
BEGIN
    # check GradeID, SpecID constraints and (GradeID, GroupNum, SpecID) - unique
    DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;

    # create discipline
    INSERT INTO `study_groups`
        (GradeID, GroupNum, SpecializationID, Name)
        VALUES (pGradeID, pGroupNum, pSpecializationID, pGroupName);
    RETURN 0;
END //


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

DROP FUNCTION IF EXISTS CreateSubject//
CREATE FUNCTION `CreateSubject`
    (   `pFacultyID` INT,
        `pSubjectName` VARCHAR(200) CHARSET utf8,
        `pSubjectAbbr` VARCHAR(20) CHARSET utf8
    )   RETURNS int(11)
    NO SQL
BEGIN
    DECLARE vSubjectID INT DEFAULT -1;

    # find same subject
    SELECT subjects.ID INTO vSubjectID
        FROM `subjects`
        WHERE subjects.Name = pSubjectName
        LIMIT 1;
    IF vSubjectID <= 0 THEN
        # create new subject
        INSERT INTO `subjects`
            (Name, Abbr) VALUES(pSubjectName, pSubjectAbbr);
        SET vSubjectID = LAST_INSERT_ID();
    END IF;

    BEGIN # handler block
        DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;
        # try to attach subject to faculty
        INSERT INTO `subjects_faculties`
            (SubjectID, FacultyID)
            VALUES (vSubjectID, pFacultyID)
            ON DUPLICATE KEY UPDATE # just stub
                subjects_faculties.FacultyID = subjects_faculties.FacultyID;
    END;
    RETURN 0;
END //



# -------------------------------------------------------------------------------------------
# Label: accounts
# -------------------------------------------------------------------------------------------

# TODO: rename
DROP FUNCTION IF EXISTS GetAccCountByCode//
CREATE FUNCTION `GetAccCountByCode` (`pCode` VARCHAR(40) CHARSET utf8) RETURNS int(11)
    NO SQL
BEGIN
    RETURN EXISTS(SELECT * FROM `accounts` WHERE accounts.ActivationCode = pCode);
END //


DROP FUNCTION IF EXISTS GetAccCountByMail //
CREATE FUNCTION `GetAccCountByMail` (`pEMail` VARCHAR(50) CHARSET utf8) RETURNS int(11)
    NO SQL
BEGIN
    RETURN EXISTS(SELECT * FROM `accounts` WHERE accounts.EMail = pEMail);
END //


DROP FUNCTION IF EXISTS GetAccCountByLogin//
CREATE FUNCTION `GetAccCountByLogin` (`pLogin` VARCHAR(50) CHARSET utf8) RETURNS int(11)
    NO SQL
BEGIN
    RETURN EXISTS(SELECT * FROM `accounts` WHERE accounts.Login = pLogin);
END //




DROP FUNCTION IF EXISTS ActivateAccount//
CREATE FUNCTION `ActivateAccount`
    (   `pCode` VARCHAR(40) CHARSET utf8,
        `pLogin` VARCHAR(50) CHARSET utf8,
        `pEMail` VARCHAR(50) CHARSET utf8,
        `pPassword` VARCHAR(255) CHARSET utf8
    )   RETURNS int(11)
    NO SQL
BEGIN
    # check for matching with existing accounts (note: Login & E-Mail are unique)
    DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;

    # activate account
    UPDATE `accounts`
        SET accounts.Login = pLogin,
            accounts.Password = pPassword,
            accounts.EMail = pEMail,
            accounts.ActivationCode = NULL
        WHERE accounts.ActivationCode = pCode AND
              (@vAccountID = accounts.ID) > 0 # save accountID
        LIMIT 1;

    IF (ROW_COUNT() = 0) THEN
        RETURN -2;
    END IF;
    RETURN @vAccountID;
END //



DROP FUNCTION IF EXISTS ChangePassword//
CREATE FUNCTION `ChangePassword`
    (   `pUserID` INT, `pPassword` VARCHAR(255) CHARSET utf8
    )   RETURNS int(11)
    NO SQL
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;

    # set new password
    UPDATE `accounts`
        SET accounts.Password = pPassword
        WHERE accounts.ID = pUserID
        LIMIT 1;
    RETURN ROW_COUNT()-1; # -1 if account doesn't exists, otherwise 0
END //



DROP FUNCTION IF EXISTS ChangeLogin//
CREATE FUNCTION `ChangeLogin`
    (   `pUserID` INT, `pLogin` VARCHAR(50) CHARSET utf8
    )   RETURNS int(11)
    NO SQL
BEGIN
    # check set login: login - unique
    DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;

    # set new login
    UPDATE `accounts`
        SET accounts.Login = pLogin
        WHERE accounts.ID = pUserID
        LIMIT 1;
    RETURN ROW_COUNT()-1; # -1 if account doesn't exists, otherwise 0
END //



DROP FUNCTION IF EXISTS ChangeMail//
CREATE FUNCTION `ChangeMail`
    (`pUserID` INT, `pEMail` VARCHAR(50) CHARSET utf8
    )   RETURNS int(11)
    NO SQL
BEGIN
    # check set login: login - unique
    DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;

    # set new e-mail
    UPDATE `accounts`
        SET accounts.EMail = pEMail
        WHERE accounts.ID = pUserID
        LIMIT 1;
    RETURN ROW_COUNT()-1; # -1 if account doesn't exists, otherwise 0
END //



DROP FUNCTION IF EXISTS SignIn//
CREATE FUNCTION `SignIn`
    (   `pLoginOrMail` VARCHAR(255) CHARSET utf8,
        `pPassword`  VARCHAR(64) CHARSET utf8
    )   RETURNS int(11)
    NO SQL
BEGIN
    DECLARE vAccountID INT DEFAULT -1;

    #check account existence
    SELECT accounts.ID
        INTO vAccountID
        FROM `accounts`
        WHERE   accounts.Password = pPassword AND
                (accounts.Login = pLoginOrMail OR accounts.EMail = pLoginOrMail)
        LIMIT 1;
    IF vAccountID <= 0 THEN
        RETURN -1;
    END IF;

    # logging
    INSERT INTO `logs_signin`
        (AccountID) VALUES (vAccountID);
    RETURN vAccountID;
END //




# -------------------------------------------------------------------------------------------
# Label: teachers
# -------------------------------------------------------------------------------------------

DROP FUNCTION IF EXISTS ChangeTeacherInfo//
CREATE FUNCTION `ChangeTeacherInfo`
    (   `pTeacherID` INT,
        `pLastName` VARCHAR(30) CHARSET utf8,
        `pFirstName` VARCHAR(30) CHARSET utf8,
        `pSecondName` VARCHAR(30) CHARSET utf8,
        `pJobPositionID` INT,
        `pDepartmentID` INT
    ) RETURNS int(11)
    NO SQL
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;

    # set new info
    UPDATE `teachers`
        SET teachers.LastName = pLastName,
            teachers.FirstName = pFirstName,
            teachers.SecondName = pSecondName,
            teachers.JobPositionID = pJobPositionID,
            teachers.DepartmentID = pDepartmentID
        WHERE teachers.ID = pTeacherID
        LIMIT 1;
    RETURN ROW_COUNT()-1; # -1 if teacher doesn't exists, otherwise 0
END //



DROP FUNCTION IF EXISTS CreateTeacher//
CREATE FUNCTION `CreateTeacher`
    (   `pLastName` VARCHAR(30) CHARSET utf8,
        `pFirstName` VARCHAR(30) CHARSET utf8,
        `pSecondName` VARCHAR(30) CHARSET utf8,
        `pJobPositionID` INT,
        `pDepartmentID`  INT,
        `pActivationCode` VARCHAR(40) CHARSET utf8
    ) RETURNS int(11)
    NO SQL
BEGIN
    DECLARE vAccountID INT DEFAULT -1;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;

# user role 2 - common teacher
# add new account
    INSERT INTO `accounts`
        (Login , Password , EMail, UserRoleID, ActivationCode )
        VALUES  ( NULL, NULL, NULL, 2, pActivationCode);
    SET vAccountID = LAST_INSERT_ID();

# add new teacher
    INSERT INTO `teachers`
        (AccountID, LastName, FirstName, SecondName, JobPositionID, DepartmentID)
        VALUES  (vAccountID, pLastName, pFirstName, pSecondName, pJobPositionID, pDepartmentID);
    RETURN ROW_COUNT()-1;
END //



DROP FUNCTION IF EXISTS CreateTeacherByDepName//
CREATE FUNCTION `CreateTeacherByDepName`
    (   `pLastName` VARCHAR(30) CHARSET utf8,
        `pFirstName` VARCHAR(30) CHARSET utf8,
        `pSecondName` VARCHAR(30) CHARSET utf8,
        `pDepartmentName` VARCHAR(200) CHARSET utf8,
        `pActivationCode` VARCHAR(40) CHARSET utf8
    )   RETURNS int(11)
    NO SQL
BEGIN
    DECLARE vAccountID, vChecker, vRoleID, vDepID INT DEFAULT -1;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;

    # try to find a department with pDepartmentName
    SELECT departments.ID
        INTO vDepID
        FROM `departments`
        WHERE   departments.Name = pDepartmentName OR
                (pDepartmentName = '' AND departments.Name IS NULL)
        # or stub department record
        LIMIT 1;
    IF vDepID <= 0 THEN
        RETURN -1;
    END IF;

    INSERT INTO `accounts`
        (Login , Password , EMail, UserRoleID, ActivationCode )
        VALUES ( NULL, NULL, NULL, 2, pActivationCode);
    SET vAccountID = LAST_INSERT_ID();

    INSERT INTO `teachers`
        (AccountID, LastName, FirstName, SecondName, JobPositionID, DepartmentID)
        VALUES (vAccountID, pLastName, pFirstName, pSecondName, 12, vDepID);
    RETURN 0;
END //



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

DROP FUNCTION IF EXISTS CreateStudent//
CREATE FUNCTION `CreateStudent`
    (   `pLastName` VARCHAR(30) CHARSET utf8,
        `pFirstName` VARCHAR(30) CHARSET utf8,
        `pSecondName` VARCHAR(30) CHARSET utf8,
        `pGradeID` INT, `pGroupNum` INT, `pFacultyID` INT,
        # TODO: delete pGradeID
        `pActivationCode` VARCHAR(40) CHARSET utf8
    )   RETURNS int(11)
    NO SQL
BEGIN
    DECLARE vAccountID, vGroupID INT DEFAULT -1;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;

    # find group
    SELECT view_groups.GroupID INTO vGroupID
        FROM `view_groups`
        WHERE   view_groups.FacultyID = pFacultyID AND
                view_groups.GradeID = pGradeID AND
                view_groups.GroupNum = pGroupNum
        LIMIT 1;
    IF vGroupID <= 0 THEN
        RETURN -1;
    END IF;

    # create new account
    INSERT INTO `accounts`
        (Login , Password , EMail, UserRoleID, ActivationCode )
        VALUES ( NULL, NULL, NULL, 1, pActivationCode);
    SET vAccountID = LAST_INSERT_ID();

    # create student
    INSERT INTO `students`
        (GroupID, AccountID, LastName, FirstName, SecondName)
        VALUES  (vGroupID, vAccountID, pLastName, pFirstName, pSecondName);
    RETURN 0;
END //


# TODO: ?#$@! magic
DROP FUNCTION IF EXISTS CreateStudentEx//
CREATE FUNCTION `CreateStudentEx`
    (   `pLastName` VARCHAR(30) CHARSET utf8,
        `pFirstName` VARCHAR(30) CHARSET utf8,
        `pSecondName` VARCHAR(30) CHARSET utf8,
        `pGradeNum` INT, `pGroupNum` INT,
        `pDegree` VARCHAR(20) CHARSET utf8,
        `pSpecName` VARCHAR(50) CHARSET utf8,
        `pFacultyID` INT,
        `pActivationCode` VARCHAR(40) CHARSET utf8
    )   RETURNS int(11)
    NO SQL
BEGIN
    DECLARE vAccountID, vGradeID, vSpecID, vGroupID INT DEFAULT -1;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;

    # try to find grade
    SELECT grades.ID INTO vGradeID
        FROM `grades`
        WHERE grades.Num = pGradeNum AND grades.Degree = pDegree
        LIMIT 1;
    # such grade doesn't exist
    IF vGradeID <= 0 THEN
        # insert new grade with pGradeNum and pDegree
        INSERT INTO `grades`
            (Num, Degree) VALUES (pGradeNum, pDegree);
        SET vGradeID = LAST_INSERT_ID();
    END IF;

    # try to find group
    SELECT view_groups.GroupID INTO vGroupID
        FROM `view_groups`
        WHERE view_groups.FacultyID = pFacultyID AND
            view_groups.GroupNum = pGroupNum AND
            view_groups.GradeID = vGradeID
        LIMIT 1;

    # group not found
    IF vGroupID <= 0 THEN
        # try to find specialization
        SELECT specializations.ID INTO vSpecID
            FROM `specializations`
            WHERE   (specializations.Name = pSpecName OR
                        (pSpecName = '' AND specializations.Name IS NULL)) AND
                    specializations.FacultyID = pFacultyID
            LIMIT 1;

        # specialization not found
        IF vSpecID <= 0 THEN
            # create new specialization
            INSERT INTO `specializations`
                (Name, Abbr, FacultyID)
                VALUES  (pSpecName, NULL, pFacultyID);
            SET vSpecID = LAST_INSERT_ID();
        END IF;

        # create new group
        INSERT INTO `study_groups`
            (GradeID, GroupNum, SpecializationID)
            VALUES (vGradeID, pGroupNum, vSpecID);
        SET vGroupID = LAST_INSERT_ID();
    END IF;

    # TODO: user roles
    # create account
    INSERT INTO `accounts`
        (Login, Password , EMail, UserRoleID, ActivationCode )
        VALUES  ( NULL, NULL, NULL, 1, pActivationCode);
    SET vAccountID = LAST_INSERT_ID();

    # create student
    INSERT INTO `students`
        (GroupID, AccountID, LastName, FirstName, SecondName)
        VALUES (vGroupID, vAccountID, pLastName, pFirstName, pSecondName);
    RETURN ROW_COUNT()-1;
END //




# -------------------------------------------------------------------------------------------
# Label: disciplines
# -------------------------------------------------------------------------------------------

DROP FUNCTION IF EXISTS OrderModuleTypesForSession//
CREATE FUNCTION `OrderModuleTypesForSession`
    (`pModuleType` INT ) RETURNS INT(3)
	NO SQL
BEGIN
	DECLARE vRes INT DEFAULT 0;

    CASE pModuleType
        WHEN 'extra' THEN SET vRes = 1;
        WHEN 'exam' THEN SET vRes = 2;
        WHEN 'bonus' THEN SET vRes = 3;
        ELSE SET vRes = 1;
    END CASE;

	RETURN vRes;
END //



DROP FUNCTION IF EXISTS AddDiscipline//
CREATE FUNCTION `AddDiscipline`
    (   `pTeacherID` INT, `pGradeID` INT, `pSubjectID` INT,
        `pExamType` VARCHAR(30) CHARSET utf8,
        `pLectureCount` INT, `pPracticeCount` INT, `pLabCount` INT,
        `pFacultyID` INT
    )   RETURNS int(11)
    NO SQL
BEGIN
    DECLARE vChecker, vDisciplineID INT DEFAULT -1;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;

    INSERT INTO `disciplines`
        (AuthorID, GradeID, SubjectID, ExamType, LectureCount, PracticeCount,LabCount, SemesterID,FacultyID)
        VALUES  (   pTeacherID, pGradeID, pSubjectID, pExamType, pLectureCount, pPracticeCount, pLabCount,
                    @CurrentSemesterID, pFacultyID);
    SET vDisciplineID = LAST_INSERT_ID();

    INSERT INTO `disciplines_teachers`
        (DisciplineID,TeacherID)
        VALUES (vDisciplineID, pTeacherID);

    IF pExamType = 'exam' THEN
        SET vChecker = AddModuleExam(pTeacherID, vDisciplineID);
    END IF;
    SET vChecker = AddModuleExtra(pTeacherID, vDisciplineID);
    RETURN vDisciplineID;
END //



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

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

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



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

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

    SELECT disciplines.GradeID INTO vChecker
        FROM `disciplines`
        WHERE disciplines.ID = pDisciplineID
        LIMIT 1;

    IF vChecker != pGradeID THEN
        DELETE FROM `disciplines_groups`
            WHERE disciplines_groups.DisciplineID = pDisciplineID;
        DELETE FROM `disciplines_students`
            WHERE disciplines_students.DisciplineID = pDisciplineID;
    END IF;

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


DROP FUNCTION IF EXISTS ChangeDisciplineControl//
CREATE FUNCTION `ChangeDisciplineControl`
    (   `pTeacherID` INT, `pDisciplineID` INT,
        `pExamType` VARCHAR(30) CHARSET utf8
    )   RETURNS int(11)
    NO SQL
BEGIN
    DECLARE vOldExamType, vChecker, vExtraMax, vExtraID INT DEFAULT -1;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;

    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 `disciplines`
        INNER JOIN `modules` ON modules.Type = 'extra' AND
                                modules.DisciplineID = pDisciplineID
        WHERE disciplines.ID = pDisciplineID
        LIMIT 1;
    IF vExtraID <= 0 THEN
        RETURN -1;
    END IF;
    # check changed change exam type
    IF  (vOldExamType = pExamType) THEN
        RETURN 0;
    END IF;


    IF pExamType = 'exam' THEN
        SET vExtraMax = 7;
        SET vChecker = GetDisciplineMaxRate(pDisciplineID);
        IF vChecker >= 61 THEN
            RETURN 1;
        END IF;
        SET vChecker = AddModuleExam(pTeacherID, pDisciplineID);

        # delete extra submodules
        DELETE FROM `submodules`
            WHERE submodules.OrderNum > 1 AND submodules.ModuleID = vExtraID;
    ELSE
        SET vExtraMax = 29;
        SET vChecker = DeleteModuleExam(pTeacherID, pDisciplineID);
        SET vChecker = AddSubmodule(pTeacherID, vExtraID, vExtraMax, '', NULL, 'LandmarkControl');
    END IF;

    UPDATE `disciplines`
        SET disciplines.ExamType = pExamType
        WHERE disciplines.ID = pDisciplineID
        LIMIT 1;
    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 BindGroup//
CREATE FUNCTION `BindGroup`
    (`pTeacherID` INT, `pDisciplineID` INT, `pGroupID` INT
    ) RETURNS int(11)
    NO SQL
BEGIN
    DECLARE vChecker INT DEFAULT -1;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;

# 1. check if AccessedTeacher is author
    IF  NOT InternalIsTeacherAuthor(pTeacherID, pDisciplineID) OR
        InternalIsMapLocked(pDisciplineID)
    THEN
        RETURN -1;
    END IF;

# 2. check if study group is bound to discipline
    # TODO: extract method
    SELECT disciplines_groups.ID INTO vChecker
        FROM `disciplines_groups`
        WHERE   disciplines_groups.GroupID = pGroupID AND
                disciplines_groups.DisciplineID = pDisciplineID
        LIMIT 1;
    IF vChecker > 0 THEN
        RETURN 1;
    END IF;

# 3. 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.ID
                    FROM `students`
                    WHERE students.GroupID = pGroupID
                );

# 4. bind whole group
    INSERT INTO `disciplines_groups`
        (DisciplineID, GroupID)
        VALUES (pDisciplineID, pGroupID );
    RETURN 0;
END //



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

# 1. check if AccessedTeacher is author
    IF NOT InternalIsTeacherAuthor(pTeacherID, pDisciplineID) THEN
        RETURN -1;
    END IF;

# 2. check if student's group is bound yet
    SELECT disciplines_groups.ID INTO vInGroup
        FROM `students`
            INNER JOIN `disciplines_groups` ON disciplines_groups.DisciplineID = pDisciplineID AND
                                               disciplines_groups.GroupID = students.GroupID
        WHERE students.ID = pStudentID
        LIMIT 1;

    # try to remove detached attribute
    IF vInGroup > 0 THEN
        DELETE FROM `disciplines_students`
            WHERE disciplines_students.DisciplineID = pDisciplineID AND
                disciplines_students.StudentID = pStudentID
            LIMIT 1;
        RETURN 0;
    END IF;


# 3. try bind student
    INSERT INTO `disciplines_students`
        (DisciplineID, StudentID, Type)
        VALUES (pDisciplineID, pStudentID, 'attach')
        # update stub/ already bounded
        ON DUPLICATE KEY UPDATE
            disciplines_students.StudentID = disciplines_students.StudentID;
    RETURN 0;

END //



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

    DELETE FROM `disciplines_groups`
        WHERE disciplines_groups.DisciplineID = pDisciplineID AND
            disciplines_groups.GroupID = pGroupID
    LIMIT 1;

    # 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.ID
                FROM `students`
                WHERE students.GroupID = pGroupID);
    RETURN 0;
END //



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

    SELECT disciplines_groups.ID INTO vInGroup
        FROM `students`
        INNER JOIN `disciplines_groups` ON disciplines_groups.DisciplineID = pDisciplineID AND
                                           disciplines_groups.GroupID = students.GroupID
        WHERE students.ID = pStudentID
        LIMIT 1;

    IF vInGroup > 0 THEN
        INSERT INTO `disciplines_students`
            (DisciplineID, StudentID, Type)
            VALUES (pDisciplineID, pStudentID, 'detach');
    ELSE
        DELETE FROM `disciplines_students`
        WHERE disciplines_students.DisciplineID = pDisciplineID AND
            disciplines_students.StudentID = pStudentID
        LIMIT 1;
    END IF;
    RETURN 0;
END //



DROP FUNCTION IF EXISTS BindTeacher//
CREATE FUNCTION `BindTeacher`
    (`pTeacherID` INT, `pBindingTeacherID` INT, `pDisciplineID` INT
    ) RETURNS int(11)
    NO SQL
BEGIN
    IF  NOT InternalIsTeacherAuthor(pTeacherID, pDisciplineID) THEN
        RETURN -1;
    END IF;

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



DROP FUNCTION IF EXISTS UnbindTeacher//
CREATE FUNCTION `UnbindTeacher`
    (   `pAuthorID` INT, `pBindingTeacher` INT, `pDisciplineID` INT
    ) RETURNS int(11)
    NO SQL
BEGIN
    IF pAuthorID = pBindingTeacher OR
       NOT InternalIsTeacherAuthor(pBindingTeacher, pDisciplineID)
    THEN
        RETURN -1;
    END IF;

    DELETE FROM `disciplines_teachers`
        WHERE   disciplines_teachers.DisciplineID = pDisciplineID AND
                disciplines_teachers.TeacherID = pBindingTeacher;
    RETURN 0;
END //



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

    IF  pAuthorID = pNewAuthorID OR
        NOT InternalIsTeacherAuthor(pAuthorID, pDisciplineID) OR
        NOT InternalIsTeacherBounded(pNewAuthorID, pDisciplineID)
    THEN
        RETURN -1;
    END IF;

    UPDATE `disciplines`
        SET disciplines.AuthorID = pNewAuthorID
        WHERE disciplines.ID = pDisciplineID
        LIMIT 1;
    RETURN ROW_COUNT()-1;
END //



DROP FUNCTION IF EXISTS ClearDiscipline//
CREATE FUNCTION `ClearDiscipline`
    (   `pAuthorID` INT, `pDisciplineID` INT
    )   RETURNS int(11)
    NO SQL
BEGIN
    IF NOT InternalIsTeacherAuthor(pAuthorID, pDisciplineID) THEN
        RETURN -1;
    END IF;

    DELETE FROM `logs_rating`
        WHERE logs_rating.SubModuleID IN
            (SELECT view_roadmap.SubmoduleID
                FROM `view_roadmap`
                WHERE view_roadmap.DisciplineID = pDisciplineID);

    DELETE FROM `rating_table`
        WHERE rating_table.SubModuleID IN
            (SELECT view_roadmap.SubmoduleID
             FROM `view_roadmap`
             WHERE view_roadmap.DisciplineID = pDisciplineID);

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

    RETURN ROW_COUNT()-1;
END //



DROP FUNCTION IF EXISTS DeleteDiscipline//
CREATE FUNCTION `DeleteDiscipline`
    (   `pAuthorID` INT, `pDisciplineID` INT
    )   RETURNS int(11)
    NO SQL
BEGIN
    DECLARE vTemp INT DEFAULT -1;
    IF NOT InternalIsTeacherAuthor(pAuthorID, pDisciplineID) THEN
        RETURN -1;
    END IF;

    SELECT disciplines.IsLocked INTO vTemp
        FROM `disciplines`
        WHERE disciplines.ID = pDisciplineID
        LIMIT 1;
    IF vTemp != 0 THEN
        RETURN -1;
    END IF;

    SET vTemp = CountRatings(pAuthorID, pDisciplineID);
    IF vTemp > 0 THEN
        RETURN -1;
    END IF;

    # TODO: ClearDiscipline if need


    DELETE FROM `submodules`
        WHERE submodules.ModuleID IN
            (SELECT modules.ID
                FROM `modules`
                WHERE modules.DisciplineID = pDisciplineID
            );

    DELETE FROM `modules`
        WHERE modules.DisciplineID = pDisciplineID;

    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 `disciplines`
        WHERE disciplines.ID = pDisciplineID
        LIMIT 1;

    RETURN 0;
END //



DROP FUNCTION IF EXISTS CountRatings//
CREATE FUNCTION `CountRatings`
    (   `pTeacherID` INT, `pDisciplineID` INT
    )   RETURNS int(11)
    NO SQL
BEGIN
    DECLARE vRes INT DEFAULT 0;

    IF  NOT InternalIsTeacherBounded(pTeacherID, pDisciplineID) THEN
        RETURN -1;
    END IF;

    SELECT COUNT(rating_table.StudentID)
        INTO vRes
        FROM `rating_table`
        INNER JOIN `submodules` ON rating_table.SubModuleID = submodules.ID
        INNER JOIN `modules` ON submodules.ModuleID = modules.ID
        WHERE modules.DisciplineID = pDisciplineID
        LIMIT 1;

    RETURN vRes;
END //



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


DROP FUNCTION IF EXISTS RestrictAfterMilestoneForCredits//
CREATE FUNCTION `RestrictAfterMilestoneForCredits`  (   `pTeacherID`     INT,
                                                        `pFacultyID`     INT
                                                    )   RETURNS int(11)
    NO SQL
BEGIN
    UPDATE `disciplines`
        SET disciplines.MilestoneDate = CURDATE(),
            disciplines.isMilestone = 1
        WHERE   disciplines.SemesterID = @CurrentSemesterID AND
                disciplines.ExamType = 'credit' AND
                disciplines.FacultyID= pFacultyID;
    RETURN 0;
END //

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



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 EXIT HANDLER FOR SQLEXCEPTION RETURN -1;

    SELECT disciplines.ID INTO vDisciplineID
        FROM `modules`
        INNER JOIN `disciplines` ON disciplines.ID = modules.DisciplineID AND
                                    disciplines.AuthorID = pTeacherID
        WHERE   modules.ID = pModuleID AND
                modules.Type = 'regular'
        LIMIT 1;
    IF vDisciplineID <= 0 OR
        InternalIsMapLocked(vDisciplineID)
    THEN
        RETURN -1;
    END IF;

    UPDATE `modules`
        SET modules.Name = pName
        WHERE modules.ID = pModuleID
        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 vChecker INT DEFAULT 0;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;

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

    SELECT MAX(modules.OrderNum)+1 INTO vChecker
        FROM `modules`
        WHERE   modules.DisciplineID = pDisciplineID AND modules.Type = 'regular'
        LIMIT 1;

    INSERT INTO `modules`
        (Name, OrderNum, DisciplineID )
        VALUES (pName, vChecker, 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 vChecker, vModule INT DEFAULT -1;
    IF  NOT InternalIsTeacherAuthor(pTeacherID, pDisciplineID) OR
        InternalIsMapLocked(pDisciplineID)
    THEN
        RETURN -1;
    END IF;

    SELECT modules.ID
        INTO vChecker
        FROM `modules`
        WHERE   modules.DisciplineID = pDisciplineID AND
                modules.Type = 'exam';
    IF vChecker > 0 THEN
        RETURN -2;
    END IF;

    INSERT INTO `modules`
        (Name, OrderNum, DisciplineID, Type)
        VALUES  ('Экзамен' , 3141692 , pDisciplineID, 'exam');

    SET vModule = LAST_INSERT_ID();
    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;
    IF  NOT InternalIsTeacherAuthor(pTeacherID, pDisciplineID)
    THEN
        RETURN -1;
    END IF;

    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;

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


    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;

    SET vGap = -1;
    IF vType = 1 THEN
    # exam
        SET vGap = 7;
    END IF;
    IF vType = 2 THEN
    # 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 vChecker INT DEFAULT -1;

    SELECT disciplines.ID
        INTO vChecker
        FROM `modules`
        INNER JOIN `disciplines`    ON  modules.DisciplineID = disciplines.ID AND
                                        disciplines.AuthorID = pTeacherID
        WHERE modules.ID = pModuleID
        LIMIT 1;
    IF  vChecker <= 0 THEN
        RETURN -1;
    END IF;

    IF  NOT InternalIsTeacherAuthor(pTeacherID, vChecker) OR
        InternalIsMapLocked(vChecker)
    THEN
        RETURN -1;
    END IF;

    DELETE FROM `submodules`
        WHERE submodules.ModuleID = pModuleID;

    DELETE FROM `modules`
        WHERE modules.ID = pModuleID;

    SET @counter = 0;
    UPDATE `modules`
        SET modules.OrderNum = (@counter := @counter + 1)
        WHERE   modules.DisciplineID = vChecker 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;

    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;

    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;

    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;
    IF  vDisciplineID1 != vDisciplineID2 OR vDisciplineID1 <= 0 OR
        InternalIsMapLocked(vDisciplineID1) THEN
        RETURN -1;
    END IF;

    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;

    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, 3 );

    SELECT  modules.ID
        INTO vModuleID
        FROM `modules`
        WHERE   modules.DisciplineID = pDisciplineID AND
                modules.Type = 'bonus'
        LIMIT 1;
    IF vModuleID <= 0 THEN
        RETURN -1;
    END IF;

    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;

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

DROP FUNCTION IF EXISTS ChangeSubmoduleMaxAndControl//
CREATE FUNCTION `ChangeSubmoduleMaxAndControl`  (   `TeacherID`     INT,
                                                    `SubmoduleID`   INT,
                                                    `MaxRate`       INT,
                                                    `ControlType`   VARCHAR(30) CHARSET utf8
                                            )   RETURNS int(11)
    NO SQL
BEGIN
    DECLARE vChecker, disciplineID INT;

    SET vChecker = -1;
    SET disciplineID = -1;
    SELECT  submodules.ID,
            disciplines.ID
    INTO vChecker, disciplineID
    FROM `submodules`
    INNER JOIN `modules`        ON  submodules.ModuleID = modules.ID
    INNER JOIN `disciplines`    ON  disciplines.ID = modules.DisciplineID
    WHERE   TeacherID = disciplines.AuthorID AND
            SubmoduleID = submodules.ID
    LIMIT 1;
    IF  vChecker <= 0 OR
        disciplineID <= 0 OR
        InternalIsMapLocked(disciplineID) THEN
        RETURN -1;
    END IF;


    UPDATE `submodules`
    SET     submodules.MaxRate = MaxRate,
            submodules.Type = ControlType
    WHERE submodules.ID = SubmoduleID
    LIMIT 1;
    RETURN 0;
END //



DROP FUNCTION IF EXISTS ChangeSubmoduleName//
CREATE FUNCTION `ChangeSubmoduleName`   (   `TeacherID`     INT,
                                            `SubmoduleID`   INT,
                                            `Name`          VARCHAR(200) CHARSET utf8
                                        )   RETURNS int(11)
    NO SQL
BEGIN
    DECLARE vChecker, disciplineID INT;

    SET disciplineID = -1;
    SET vChecker = -1;
    SELECT  submodules.ID,
            disciplines.ID
    INTO vChecker, disciplineID
    FROM `submodules`
    INNER JOIN `modules`        ON  submodules.ModuleID = modules.ID
    INNER JOIN `disciplines`    ON  disciplines.ID = modules.DisciplineID
    WHERE   TeacherID = disciplines.AuthorID AND
            SubmoduleID = submodules.ID
    LIMIT 1;
    IF  vChecker <= 0 OR
        disciplineID <= 0 OR
        InternalIsMapLocked(disciplineID) THEN
        RETURN -1;
    END IF;

    UPDATE `submodules`
    SET submodules.Name = Name
    WHERE submodules.ID = SubmoduleID
    LIMIT 1;
    RETURN 0;
END //



DROP FUNCTION IF EXISTS ChangeSubmoduleDescription//
CREATE FUNCTION `ChangeSubmoduleDescription`    (   `TeacherID`     INT,
                                                    `SubmoduleID`   INT,
                                                    `Description`   VARCHAR(200) CHARSET utf8
                                                )   RETURNS int(11)
    NO SQL
BEGIN
    DECLARE vChecker, disciplineID INT;

    SET vChecker = -1;
    SET disciplineID = -1;
    SELECT  submodules.ID,
            disciplines.ID
    INTO vChecker, disciplineID
    FROM `submodules`
    INNER JOIN `modules`        ON  submodules.ModuleID = modules.ID
    INNER JOIN `disciplines`    ON  disciplines.ID = modules.DisciplineID
    WHERE   TeacherID = disciplines.AuthorID AND
            SubmoduleID = submodules.ID
    LIMIT 1;
    IF  vChecker <= 0 OR
        disciplineID <= 0 OR
        InternalIsMapLocked(disciplineID) THEN
        RETURN -1;
    END IF;

    UPDATE `submodules`
    SET submodules.Description  = Description
    WHERE submodules.ID = SubmoduleID
    LIMIT 1;
    RETURN 0;
END //




DROP FUNCTION IF EXISTS DeleteSubmodule//
CREATE FUNCTION `DeleteSubmodule`
    (   `pTeacherID` INT, `pSubmoduleID` INT
    ) RETURNS int(11)
    NO SQL
BEGIN
    DECLARE vChecker, vModuleID, vDisciplineID INT DEFAULT -1;

    SELECT submodules.ID, modules.ID, disciplines.ID
        INTO vChecker, vModuleID, vDisciplineID
        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  vChecker <= 0 OR
        vDisciplineID <= 0 OR
        InternalIsMapLocked(vDisciplineID) THEN
        RETURN -1;
    END IF;

    SET vChecker = -1;
    SELECT rating_table.StudentID
        INTO vChecker
        FROM `rating_table`
        WHERE rating_table.SubmoduleID = pSubmoduleID
        LIMIT 1;
    IF vChecker > 0 THEN
        RETURN -2;
    END IF;

    DELETE FROM `submodules`
        WHERE submodules.ID = pSubmoduleID
        LIMIT 1;


    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 vChecker INT DEFAULT -1;

    SELECT disciplines.ID
        INTO vChecker
        FROM `modules`
        INNER JOIN `disciplines` ON disciplines.ID = modules.DisciplineID
        WHERE   disciplines.AuthorID = pTeacherID AND
                modules.ID = pModuleID
        LIMIT 1;
    IF  vChecker <= 0 OR
        InternalIsMapLocked(vChecker)
    THEN
        RETURN -1;
    END IF;


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

    IF pDescription = '' THEN
        INSERT INTO `submodules`
            (ModuleID, MaxRate, OrderNum, Name, Description, Type)
            VALUES  (pModuleID, pMaxRate, vChecker, pName, NULL, pControlType);
    ELSE
        INSERT INTO `submodules`
            (ModuleID, MaxRate, OrderNum, Name, Description, Type )
            VALUES (pModuleID, pMaxRate, vChecker, pName, pDescription, pControlType);
    END IF;

    RETURN  (SELECT submodules.ID
                FROM `submodules`
                WHERE   submodules.ModuleID = pModuleID AND
                        submodules.OrderNum = vChecker
                LIMIT 1
            );
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;

    IF  vModule1 <= 0 OR vModule1 != vModule2 OR
        InternalIsMapLocked(vDisciplineID)
    THEN
        RETURN -1;
    END IF;

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


DROP FUNCTION IF EXISTS GetMaxRateForDisc//
CREATE FUNCTION `GetMaxRateForDisc`
(   `pDisciplineID` INT  )   RETURNS int(11)
    NO SQL
BEGIN
    DECLARE vResult INT DEFAULT 0;

    SELECT SUM(submodules.MaxRate)
        INTO vResult
        FROM `modules`
        LEFT JOIN `submodules` ON   submodules.ModuleID = modules.ID
        WHERE   modules.DisciplineID = pDisciplineID AND
                submodules.IsUsed != 0 AND
                (modules.Type = 1 OR ( modules.Type = 2 AND submodules.OrderNum = 1))
        LIMIT 1;
    RETURN  (vResult);
END //


# Вычисление максимального балла для submodule
DROP FUNCTION IF EXISTS CalculateMaxRateForExtra//
CREATE FUNCTION `CalculateMaxRateForExtra`
( `pSubmoduleID` INT, `pStudentID` INT) RETURNS int(11)
    NO SQL
BEGIN
    DECLARE vExamType INT; # enum('exam', 'credit');# utf8;
    DECLARE vLim INT;
    DECLARE vResult INT DEFAULT -1;

    IF vExamType = 'exam' THEN
        SET vLim = 38;
    ELSE
        SET vLim = 60;
    END IF;

    SELECT vLim - GetRateForDiscSemester( pStudentID, DisciplineID)
        INTO vResult
        FROM `submodules`
        INNER JOIN `modules` ON submodules.ModuleID = modules.ID
        INNER JOIN `disciplines` ON modules.DisciplineID = disciplines.ID
        WHERE modules.type='extra' AND submodules.ID = pSubmoduleID;
    RETURN vResult;
END //


DROP FUNCTION IF EXISTS SetStudentRate//
CREATE FUNCTION `SetStudentRate`
(   `pTeacherID` INT, `pStudentID` INT,
    `pSubmoduleID` INT, `pRate` INT ) RETURNS int(11)
    NO SQL
BEGIN
    DECLARE vChecker, vDisciplineID, vGroupID, vRateID, vMaxRate, vMtype INT DEFAULT -1;
    DECLARE vIsOver, vIsLocked, vIsUsed tinyint DEFAULT 0;

    SELECT students.GroupID
        INTO vGroupID
        FROM `students`
        WHERE students.ID = pStudentID
        LIMIT 1;
    IF vGroupID <= 0 THEN
        RETURN -1;
    END IF;

    SET vMaxRate = CalculateMaxRateForExtra(pSubmoduleID, pStudentID);

    SET vIsOver = 1;
    SELECT  modules.DisciplineID,
            disciplines.IsLocked,
            disciplines.isMilestone,
            rating_table.StudentID,
            submodules.IsUsed,
            CASE
                WHEN modules.type='extra' THEN
                CalculateMaxRateForExtra(pSubmoduleID, pStudentID)
            ELSE
                submodules.maxRate
            END,
            modules.Type
    INTO vDisciplineID, vIsLocked, vIsOver, vRateID, vIsUsed, vMaxRate, vMtype
    FROM `submodules`
    INNER JOIN `modules`                ON  submodules.ModuleID = modules.ID
    INNER JOIN `disciplines`            ON  modules.DisciplineID = disciplines.ID
    INNER JOIN `disciplines_teachers`   ON  disciplines.ID = disciplines_teachers.DisciplineID AND
                                            pTeacherID = disciplines_teachers.TeacherID
    LEFT JOIN   `disciplines_groups`    ON  disciplines.ID = disciplines_groups.DisciplineID AND
                                            vGroupID = disciplines_groups.GroupID
    LEFT JOIN   `disciplines_students`  ON  disciplines.ID = disciplines_students.DisciplineID AND
                                            pStudentID = disciplines_students.StudentID  AND
                                            1 = disciplines_students.Type
    LEFT JOIN   `rating_table`          ON  pSubmoduleID = rating_table.SubmoduleID AND
                                            pStudentID = rating_table.StudentID
    WHERE   submodules.ID = pSubModuleID AND
            (   disciplines_students.ID IS NOT NULL OR
                disciplines_groups.ID IS NOT NULL
            )
    LIMIT 1;

    IF  vDisciplineID <= 0 OR
        pRate > vMaxRate OR
        (vIsOver > 0 AND (vMtype = 1 OR vMtype = 3))
    THEN
        RETURN -2;
    END IF;


    IF vRateID IS NOT NULL AND vRateID > 0 THEN
        INSERT INTO `logs_rating`
                (StudentID, SubmoduleID, TeacherID, Rate, Action )
        VALUES  (pStudentID, pSubModuleID, pTeacherID, pRate, 'change');

        UPDATE `rating_table`
        SET     rating_table.TeacherID  = pTeacherID,
                rating_table.Rate       = pRate,
                rating_table.Date       = CURDATE()
        WHERE   pSubmoduleID = rating_table.SubmoduleID AND
                pStudentID = rating_table.StudentID
        LIMIT 1;

    ELSE
        IF NOT vIsLocked THEN
            UPDATE `disciplines`
            SET     disciplines.IsLocked = 1
            WHERE   disciplines.ID = vDisciplineID
            LIMIT 1;
        END IF;

        INSERT INTO `logs_rating`
                (StudentID, SubmoduleID, TeacherID, Rate, Action )
        VALUES  (pStudentID, pSubModuleID, pTeacherID, pRate, 'add');

        INSERT INTO `rating_table`
            (   StudentID, TeacherID, SubmoduleID, Rate, Date)
            VALUES  ( pStudentID, pTeacherID, pSubmoduleID, pRate, CURDATE() );

        IF NOT vIsUsed THEN
            UPDATE `submodules`
            SET submodules.IsUsed = 1
            WHERE submodules.ID = pSubModuleID
            LIMIT 1;
        END IF;
    END IF;
    RETURN 0;
END //





# -------------------------------------------------------------------------------------------
# Label: requests
# -------------------------------------------------------------------------------------------



DROP FUNCTION IF EXISTS SetRequestStatus//
CREATE FUNCTION `SetRequestStatus`
(`pRequestID` INT, `pStatus` VARCHAR(20) CHARSET utf8) RETURNS int(11)
    NO SQL
BEGIN
    UPDATE `requests`
        SET requests.Status = pStatus
        WHERE requests.ID = pRequestID
        LIMIT 1;
    RETURN 0;
END//


DROP FUNCTION IF EXISTS CreateRequest//
CREATE FUNCTION `CreateRequest`
(   `pAccountID` INT, `pTitle` VARCHAR(50) CHARSET utf8,
    `pDescription` TEXT CHARSET utf8) RETURNS int(11)
    NO SQL
BEGIN
    INSERT INTO `requests`
        (AccountID, Title, Description, Status)
        VALUES  (pAccountID, pTitle, pDescription, 'opened');
    RETURN LAST_INSERT_ID();
END//





# DROP FUNCTION IF EXISTS SetRequestStatus//
# CREATE FUNCTION `SetRequestStatus`   (   `RequestID` INT,
#                                      `vStatus` VARCHAR(20) CHARSET utf8
#                                  )   RETURNS int(11)
#     NO SQL
# BEGIN
#  IF vStatus = "closed" THEN
#      INSERT INTO `requests_old`
#          (   requests.ID, requests.To, requests.From,
#              requests.Field1, requests.Field2, requests.Field3,
#              requests.Data, requests.DataExt,
#              requests.Date, requests.Type, requests.Status
#          )
#                  SELECT  requests.ID, requests.To, requests.From,
#                          requests.Field1, requests.Field2, requests.Field3,
#                          requests.Data, requests.DataExt,
#                          requests.Date, requests.Type, 'closed' AS 'Status'
#                  FROM `requests`
#                  WHERE requests.ID = RequestID
#      LIMIT 1;

#      DELETE FROM `requests`
#      WHERE requests.ID = RequestID
#      LIMIT 1;



#  ELSE
#      UPDATE  `requests`
#      SET requests.Status = vStatus
#      WHERE RequestID = requests.ID
#      LIMIT 1;
#  END IF;
#  RETURN 0;
# END//





# DROP FUNCTION IF EXISTS RequestReport//
# CREATE FUNCTION `RequestReport`      (   `AccountID` INT,
#                                      `vTitle` VARCHAR(50) CHARSET utf8,
#                                      `vDescription` TEXT CHARSET utf8
#                                  )   RETURNS int(11)
#     NO SQL
# BEGIN
#  INSERT INTO `requests`
#          (requests.To, requests.From, requests.Data, requests.DataExt, requests.Type)
#  VALUES  (0, AccountID, vDescription, vTitle, 4); # 4 - report
#  RETURN LAST_INSERT_ID();
# END//



# DROP FUNCTION IF EXISTS RequestDelegateDiscipline//
# CREATE FUNCTION `RequestDelegateDiscipline`  (   `AuthorID` INT,
#                                              `NewAuthorID` INT,
#                                              `DisciplineID` INT
#                                          )   RETURNS int(11)
#     NO SQL
# BEGIN
#  IF  AuthorID = NewAuthorID OR
#      NOT InternalIsTeacherAuthor(AuthorID, DisciplineID)
#  THEN
#      RETURN -1;
#  END IF;

#  INSERT INTO `requests`
#      (requests.To, requests.From, requests.Field1, requests.Type)
#  VALUES  (NewAuthorID, AuthorID, DisciplineID, 1);
#  RETURN 0;
# END //


# DROP FUNCTION IF EXISTS RequestDeleteDiscipline//
# CREATE FUNCTION `RequestDeleteDiscipline`    (   `AuthorID` INT,
#                                              `DisciplineID` INT
#                                          )   RETURNS int(11)
#     NO SQL
# BEGIN
#  IF  NOT InternalIsTeacherAuthor(AuthorID, DisciplineID)
#  THEN
#      RETURN -1;
#  END IF;

#  INSERT INTO `requests`
#      (requests.To, requests.From, requests.Field1, requests.Type)
#  VALUES  (0, AuthorID, DisciplineID, 2);
#  RETURN 0;
# END //

# DROP FUNCTION IF EXISTS RequestClearDiscipline//
# CREATE FUNCTION `RequestClearDiscipline` (   `AuthorID` INT,
#                                              `DisciplineID` INT
#                                          )   RETURNS int(11)
#     NO SQL
# BEGIN
#  IF  NOT InternalIsTeacherAuthor(AuthorID, DisciplineID)
#  THEN
#      RETURN -1;
#  END IF;

#  INSERT INTO `requests`
#      (requests.To, requests.From, requests.Field1, requests.Type)
#  VALUES  (0, AuthorID, DisciplineID, 3);
#  RETURN 0;
# END //




# -------------------------------------------------------------------------------------------
# Label: recovery
# -------------------------------------------------------------------------------------------



DROP FUNCTION IF EXISTS CreateRecoveryToken//
CREATE FUNCTION `CreateRecoveryToken`
(   `pAccountOrEMail` VARCHAR(255) CHARSET utf8,
    `pToken` VARCHAR(100) CHARSET utf8)   RETURNS int(11)
    NO SQL
BEGIN
    DECLARE vChecker INT DEFAULT 0;
    DECLARE vAccountID INT DEFAULT -1;

    SELECT accounts.ID
        INTO vAccountID
        FROM `accounts`
        WHERE accounts.EMail = pAccountOrEMail
        LIMIT 1;
    IF vAccountID <= 0 THEN
        RETURN -1;
    END IF;

    SELECT recovery_tokens.ID
        INTO vChecker
        FROM `recovery_tokens`
        WHERE recovery_tokens.Token = pToken
        LIMIT 1;
    IF vChecker > 0 THEN
        RETURN -1;
    END IF;

    INSERT INTO `recovery_tokens`
        (AccountID, Token )
        VALUES  (vAccountID, pToken);
    RETURN LAST_INSERT_ID();
END//



DROP FUNCTION IF EXISTS UseRecoveryToken//
CREATE FUNCTION `UseRecoveryToken`
(   `pToken` VARCHAR(100) CHARSET utf8) RETURNS int(11)
    NO SQL
BEGIN
    DECLARE vChecker INT DEFAULT -1;

    SELECT recovery_tokens.ID
        INTO vChecker
        FROM `recovery_tokens`
        WHERE recovery_tokens.Token = pToken
        LIMIT 1;
    IF vChecker <= 0 THEN
        RETURN -1;
    END IF;

    UPDATE `recovery_tokens`
        SET recovery_tokens.IsUsed = 1
        WHERE recovery_tokens.Token = pToken
        LIMIT 1;
    RETURN 0;
END//



DELIMITER ;