StoredFunctions.sql

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

DROP FUNCTION IF EXISTS GetRateForDiscExtra//
DROP FUNCTION IF EXISTS GetRateForDiscBonus//
DROP FUNCTION IF EXISTS GetRateForDiscSemester//
DROP FUNCTION IF EXISTS GetRateForDiscExamNum//
DROP FUNCTION IF EXISTS GetRateForDiscExam//
DROP FUNCTION IF EXISTS InternalNotify//
DROP FUNCTION IF EXISTS GetDisciplineMaxRate//
DROP FUNCTION IF EXISTS OrderModuleTypesForSession//

DROP FUNCTION IF EXISTS CreateStudyGroup//


# -------------------------------------------------------------------------------------------
# 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
    RETURN EXISTS(
        SELECT * FROM `disciplines`
        WHERE disciplines.ID = pDisciplineID AND disciplines.isLocked = 1
    );
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.SemesterID = @CurrentSemesterID AND
              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 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 SetSessionOption//
CREATE FUNCTION `SetSessionOption`
    (   `pStudentID` INT, `pSubmoduleID` INT,
        `pType` VARCHAR(30) CHARSET utf8 # enum('absence','pass')
    ) RETURNS int(11)
    NO SQL
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;

    INSERT INTO `session_options`
        (StudentID, SubmoduleID, Type) VALUES(pStudentID, pSubmoduleID, pType)
        ON DUPLICATE KEY UPDATE
        session_options.Type = pType;

    RETURN  0;
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 //

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

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

    RETURN vRes;
END //




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

# set values of record with key \pKey,
# if doesn't exist, then create.
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
# -------------------------------------------------------------------------------------------

# set current(for user) semester, life time - db session
DROP FUNCTION IF EXISTS SetSemesterID//
CREATE FUNCTION `SetSemesterID` (`pSemesterID` INT) RETURNS int(11)
    NO SQL
BEGIN
    SET @CurrentSemesterID := pSemesterID;
    RETURN 0;
END //



# -------------------------------------------------------------------------------------------
# Label: faculties
# -------------------------------------------------------------------------------------------

DROP FUNCTION IF EXISTS CreateFaculty //
CREATE FUNCTION CreateFaculty
    (   `pFacultyName` VARCHAR(100) CHARSET utf8,
        `pFacultyAbbr` VARCHAR(20) CHARSET utf8
    ) RETURNS INT(11)
    NO SQL
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;

    INSERT INTO faculties
    (Name, Abbr)
    VALUES(pFacultyName, pFacultyAbbr);
    RETURN 0;
END //

# -------------------------------------------------------------------------------------------
# Label: departments
# -------------------------------------------------------------------------------------------

DROP FUNCTION IF EXISTS CreateDepartment //
CREATE FUNCTION CreateDepartment
    (   `pName` VARCHAR(200) CHARSET utf8,
        `pFacultyID` INT(11)
    ) RETURNS INT(11)
NO SQL
    BEGIN
        DECLARE vChecker INT DEFAULT -1;
        DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;

        SELECT faculties.ID INTO vChecker
        FROM `faculties`
        WHERE faculties.ID = pFacultyID
        LIMIT 1;

        IF vChecker > 0 THEN
            INSERT INTO departments
            (Name, FacultyID)
            VALUES(pName, pFacultyID);
            RETURN 0;
        END IF;
        RETURN -1;
    END //

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

# negative int, if already exists
DROP FUNCTION IF EXISTS CreateGroup//
CREATE FUNCTION `CreateGroup`
    (   `pGradeID` INT, `pGroupNum` INT,
        `pSpecializationID` INT, `pGroupName` VARCHAR(50) CHARSET utf8
    )   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 //


DROP FUNCTION IF EXISTS DeleteSubject //
CREATE FUNCTION DeleteSubject
    (`pSubjectID` INT) RETURNS TEXT
    NO SQL
BEGIN
    DECLARE vChecker INT DEFAULT -1;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;

    SELECT disciplines.ID INTO vChecker
        FROM `disciplines`
        WHERE disciplines.SubjectID = pSubjectID
        LIMIT 1;
    IF vChecker > 0 THEN
        RETURN -1; # Удаляемый предмет используется в disciplines.
    END IF;

    DELETE FROM `subjects_faculties`
        WHERE subjects_faculties.SubjectID = pSubjectID;
        DELETE FROM `subjects`
        WHERE subjects.ID = pSubjectID
        LIMIT 1;

    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; # account with this Code not found
    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,
        `pFacultyID` INT,
        `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;

    IF pDepartmentName = '' THEN
        RETURN -1;
    END IF;

    # try to find a department with pDepartmentName
    SELECT departments.ID
        INTO vDepID
        FROM `departments`
        WHERE   (departments.Name = pDepartmentName AND departments.FacultyID = pFacultyID)
        LIMIT 1;

    IF vDepID <= 0 THEN
        # pDepartmentName is not empty now
        SET vChecker = CreateDepartment(pDepartmentName, pFacultyID);
        IF vChecker < 0 THEN
           RETURN -1;
        END IF;
        SET vDepID = LAST_INSERT_ID();
    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 //


-- -1 - не сотрудник деканата и не преподаватель дисциплины
-- 0 - только чтение
-- 1 - редактирование
DROP FUNCTION IF EXISTS GetEditRightsForTeacher//
CREATE FUNCTION `GetEditRightsForTeacher`   
    (   `pTeacherID` INT,
        `pDisciplineID` INT
    )   RETURNS int(11)
    NO SQL
BEGIN
    DECLARE vUserRole, vDiscTeacherID INT DEFAULT -1;
              
    SELECT disciplines_teachers.ID INTO vDiscTeacherID
        FROM `disciplines_teachers`
        WHERE disciplines_teachers.DisciplineID = pDisciplineID AND
              disciplines_teachers.TeacherID = pTeacherID;

    IF vDiscTeacherID > 0 THEN
        RETURN 1;
    END IF;

    SELECT accounts.UserRoleID INTO vUserRole
        FROM `teachers`
        INNER JOIN `accounts` ON teachers.AccountID=accounts.ID
        WHERE teachers.ID = pTeacherID;
    IF vUserRole = 4 THEN # 4 - сотрудник деканата
        RETURN 0;
    END IF;

    RETURN -1;
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,
        `pActivationCode` VARCHAR(40) CHARSET utf8
    )   RETURNS int(11)
    NO SQL
BEGIN
    DECLARE vAccountID, vGroupID, vStudentID, vSemesterID 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`
        (AccountID, LastName, FirstName, SecondName)
        VALUES  (vAccountID, pLastName, pFirstName, pSecondName);


    SELECT general_settings.Val INTO vSemesterID
        FROM `general_settings`
        WHERE general_settings.Name = 'SemesterID'
        LIMIT 1;

    # bind group in current semester
    INSERT INTO `students_groups`
        (StudentID, GroupID, SemesterID)
        VALUES (LAST_INSERT_ID(), vGroupID, vSemesterID);

    RETURN 0;
END //


# unlike fn CreateStudent, this can create all missing records (group, grade, specialization)
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;

    RETURN CreateStudent(pLastName, pFirstName, pSecondName, vGradeID, pGroupNum, pFacultyID, pActivationCode);
END //



# Give a student an academic leave or attach him to group.
#   params:
#      StudentID (int)
#      GroupID (int)   : is used, if OnLeave == false
#      OnLeave (bool)  : true - into study leave, false - attach to group
DROP FUNCTION IF EXISTS ControlStudentGroup//
CREATE FUNCTION `ControlStudentGroup` (
        `pStudentID` INT,
        `pGroupID` INT,
        `pState` BOOLEAN
    ) RETURNS int(11)
    NO SQL
BEGIN
    DECLARE vChecker INT DEFAULT 0;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;

    # out to study leave
    IF pState THEN
        UPDATE `students_groups`
            SET students_groups.IsStudyLeave = TRUE,
                students_groups.Date = CURDATE()
            WHERE students_groups.StudentID = pStudentID AND
                  students_groups.SemesterID = @CurrentSemesterID
            LIMIT 1;
    # attach to new group
    ELSE
        INSERT INTO `students_groups`
            (StudentID, GroupID, SemesterID)
            VALUES(pStudentID, pGroupID, @CurrentSemesterID);
    END IF;
    RETURN ROW_COUNT()-1;
END //


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



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,
        `pSubtype` VARCHAR(30) CHARSET utf8 # enum('scientific_coursework', 'discipline_coursework' )
    )   RETURNS int(11)
    NO SQL
BEGIN
    DECLARE vChecker, vDisciplineID INT DEFAULT -1;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;

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

    # bind teacher(author)
    INSERT INTO `disciplines_teachers`
        (DisciplineID,TeacherID)
        VALUES (vDisciplineID, pTeacherID);

    # add exam and extra modules
    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 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 GetMilestone//
CREATE FUNCTION `GetMilestone`
    (`pFacultyID` INT, `pSemesterID` INT
    ) RETURNS int(11)
    NO SQL
BEGIN
    DECLARE vMilestone, vCounter INT DEFAULT 0;

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



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

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

    # get current grade
    SELECT disciplines.GradeID INTO vCurGradeID
        FROM `disciplines`
        WHERE disciplines.ID = pDisciplineID
        LIMIT 1;
    IF vCurGradeID = pGradeID THEN
        RETURN 0;
    END IF;

    # delete all students
    DELETE FROM `disciplines_groups`
        WHERE disciplines_groups.DisciplineID = pDisciplineID;
    DELETE FROM `disciplines_students`
        WHERE disciplines_students.DisciplineID = pDisciplineID;

    # set grade
    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 that exam type really changed
    IF vOldExamType = 'exam' XOR pExamType != 'exam' THEN
        RETURN 0;
    END IF;


    IF pExamType = 'exam' THEN # change to exam
        SET vExtraMax = 7;
        # count discipline's current max rate
        SELECT view_disciplines_results.DisciplineRateMax INTO vChecker
            FROM `view_disciplines_results`
            WHERE view_disciplines_results.DisciplineID = pDisciplineID
            LIMIT 1;
        # can't add exam module
        IF vChecker >= 61 THEN
            RETURN 1;
        END IF;
        SET vChecker = AddModuleExam(pTeacherID, pDisciplineID);

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

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