R__functions.sql

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

DELIMITER //
DROP FUNCTION IF EXISTS SaveSession //
DROP FUNCTION IF EXISTS DeattachAllStudents//
DROP FUNCTION IF EXISTS GetGroup//
DROP FUNCTION IF EXISTS CreateDepartment //

# -------------------------------------------------------------------------------------------
# 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
READS SQL DATA
RETURN (
    SELECT disciplines.IsLocked FROM disciplines
    WHERE disciplines.ID = pDisciplineID LIMIT 1
) <=> TRUE//

# check, that student really take this course
DROP FUNCTION IF EXISTS InternalIsStudentAttached//
CREATE FUNCTION InternalIsStudentAttached (
    pRecordBookID INT,
    pDisciplineID INT
) RETURNS BOOLEAN
READS SQL DATA
BEGIN
    DECLARE vAttachType enum('attach','detach') DEFAULT NULL;
    DECLARE vSemesterID, vGroupID INT DEFAULT 0;

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

    SELECT disciplines_students.Type INTO vAttachType
        FROM disciplines_students
        WHERE   disciplines_students.RecordBookID = pRecordBookID AND
                disciplines_students.DisciplineID = pDisciplineID
        LIMIT 1;
    IF vAttachType IS NOT NULL THEN # attached or detached
        RETURN (vAttachType <=> 'attach');
    END IF;

    SET vGroupID = GetStudentGroup(pRecordBookID, vSemesterID);
    RETURN EXISTS(SELECT * FROM disciplines_groups
        WHERE disciplines_groups.GroupID = vGroupID
    );
END //

# check, that teacher teach this course
DROP FUNCTION IF EXISTS InternalIsTeacherBound//
CREATE FUNCTION InternalIsTeacherBound (
    pTeacherID INT,
    pDisciplineID INT
) RETURNS BOOLEAN
READS SQL DATA
RETURN EXISTS (
    SELECT * FROM disciplines_teachers
    WHERE disciplines_teachers.TeacherID = pTeacherID AND disciplines_teachers.DisciplineID = pDisciplineID
    LIMIT 1
)//

DROP FUNCTION IF EXISTS InternalIsTeacherAuthor//
CREATE FUNCTION InternalIsTeacherAuthor (
    pTeacherID INT,
    pDisciplineID INT
) RETURNS BOOLEAN
READS SQL DATA
RETURN (
    SELECT disciplines.AuthorID FROM disciplines
    WHERE disciplines.ID = pDisciplineID LIMIT 1
) <=> pTeacherID //

DROP FUNCTION IF EXISTS SetExamPeriodOption//
DROP FUNCTION IF EXISTS Discipline_SetExamPeriodOption//
CREATE FUNCTION Discipline_SetExamPeriodOption (
    pRecordBookID INT,
    pSubmoduleID INT,
    pType enum('absence', 'pass', 'null')
) RETURNS int(11)
NO SQL
BEGIN
    DECLARE vType INT DEFAULT NULL;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;

    IF pType != 'null' THEN
        SET vType = pType;
    END IF;

    INSERT INTO exam_period_options (RecordBookID, SubmoduleID, Type) VALUES(pRecordBookID, pSubmoduleID, vType)
        ON DUPLICATE KEY UPDATE
            exam_period_options.Type = vType;

    RETURN 0;
END //

# check, regular + exam rate == 100
DROP FUNCTION IF EXISTS InternalIsMapCreated//
CREATE FUNCTION InternalIsMapCreated (pDisciplineID INT
) RETURNS int(11)
READS SQL DATA
RETURN (
    SELECT disciplines.MaxRate FROM disciplines
    WHERE disciplines.ID = pDisciplineID LIMIT 1
) <=> 100//

# ordering helper
DROP FUNCTION IF EXISTS InternalOrderModuleTypesForSession//
CREATE FUNCTION InternalOrderModuleTypesForSession (pModuleType INT) RETURNS INT(3)
DETERMINISTIC CONTAINS SQL
RETURN (CASE pModuleType
    WHEN 4 THEN 1 # extra
    WHEN 2 THEN 2 # exam
    WHEN 3 THEN 3 # bonus
    ELSE 4
END)//

# -------------------------------------------------------------------------------------------
# Label: user roles
# -------------------------------------------------------------------------------------------

-- todo: search by name
DROP FUNCTION IF EXISTS GetUserRole//
CREATE FUNCTION GetUserRole(
    pType enum('student', 'teacher', 'admin', 'dean')
) RETURNS INT(11)
DETERMINISTIC CONTAINS SQL
RETURN (CASE pType
    WHEN 'dean' THEN 4
    WHEN 'teacher' THEN 2
    WHEN 'admin' THEN 3
    ELSE 1
END)//

# -------------------------------------------------------------------------------------------
# Label: grades
# -------------------------------------------------------------------------------------------

DROP FUNCTION IF EXISTS CreateGrade//
CREATE FUNCTION CreateGrade (
    pGradeNum INT,
    pDegree enum('bachelor', 'master', 'specialist')
) RETURNS int(11) # groupID or -1 if failed
NO SQL
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;
    IF pDegree IS NULL OR NOT (pDegree BETWEEN 'bachelor' AND 'specialist') THEN
        RETURN -1;
    END IF;

    INSERT INTO grades (Num, Degree) VALUES (pGradeNum, pDegree)
        ON DUPLICATE KEY UPDATE
            grades.ID = LAST_INSERT_ID(grades.ID);
    RETURN LAST_INSERT_ID();
END //

# -------------------------------------------------------------------------------------------
# Label: 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,
    pYear INT
) RETURNS int(11) # group id
NO SQL
BEGIN
    DECLARE vGroupID, vFacultyID, vGroupYear, vIsSpecMatch INT DEFAULT -1;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;

    SET vFacultyID = (SELECT FacultyID FROM specializations WHERE specializations.ID = pSpecializationID LIMIT 1);

    # create discipline
    INSERT INTO study_groups (GradeID, GroupNum, FacultyID) VALUES (pGradeID, pGroupNum, vFacultyID)
        ON DUPLICATE KEY UPDATE
            study_groups.ID = LAST_INSERT_ID(study_groups.ID);
    SET vGroupID = LAST_INSERT_ID();

    SELECT groups_years.GroupID, (groups_years.SpecializationID = pSpecializationID AND
                                  (pGroupName IS NULL OR groups_years.Name <=> pGroupName))
        INTO vGroupYear, vIsSpecMatch
        FROM groups_years
        WHERE groups_years.GroupID = vGroupID AND groups_years.Year = pYear
        LIMIT 1;

    IF vGroupYear = -1 THEN
        INSERT INTO groups_years (GroupID, Year, SpecializationID, Name)
        VALUES (vGroupID, pYear, pSpecializationID, pGroupName);
    ELSEIF NOT vIsSpecMatch THEN
        RETURN -1;
    END IF;

    RETURN vGroupID;
END //

DROP FUNCTION IF EXISTS FindGroup//
CREATE FUNCTION FindGroup (
    pGradeID INT,
    pGroupNum INT,
    pFacultyID INT
) RETURNS int(11) # groupID or -1 if failed
READS SQL DATA
RETURN COALESCE((
    SELECT study_groups.ID FROM study_groups
    WHERE   study_groups.GradeID = pGradeID
            AND study_groups.GroupNum = pGroupNum
            AND study_groups.FacultyID = pFacultyID
    LIMIT 1
), -1)//

DROP FUNCTION IF EXISTS GetStudentGroup//
CREATE FUNCTION GetStudentGroup (
    pRecordBookID INT,
    pSemesterID INT
) RETURNS int(11) # groupID or -1 if failed
READS SQL DATA
RETURN COALESCE((
    SELECT students_groups.GroupID FROM students_groups
        JOIN record_books ON record_books.ID = students_groups.RecordBookID
        WHERE   record_books.ID = pRecordBookID
                AND students_groups.SemesterID = pSemesterID
                AND students_groups.State != 'expulsion'
        LIMIT 1
), -1)//

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

DROP FUNCTION IF EXISTS CreateSubject//
CREATE FUNCTION CreateSubject (
    pFacultyID INT,
    pSubjectName TEXT CHARSET utf8,
    pSubjectAbbr VARCHAR(20) CHARSET utf8
)   RETURNS int(11) -- subjectID or negative value
NO SQL
BEGIN
    DECLARE vSubjectID INT DEFAULT -1;
    IF pSubjectName = '' THEN RETURN -1; END IF;

    # create/get subject (subject name is unique key)
    INSERT INTO subjects (Name, Abbr) VALUES (pSubjectName, pSubjectAbbr)
        ON DUPLICATE KEY UPDATE
            subjects.ID = LAST_INSERT_ID(subjects.ID);
    SET vSubjectID = LAST_INSERT_ID();

    BEGIN # handler block
        DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;
        INSERT INTO subjects_faculties (SubjectID, FacultyID) VALUES (vSubjectID, pFacultyID)
            ON DUPLICATE KEY UPDATE # just stub
                subjects_faculties.ID = LAST_INSERT_ID(subjects_faculties.ID);
    END;
    RETURN vSubjectID;
END //

DROP FUNCTION IF EXISTS DeleteSubject //
CREATE FUNCTION DeleteSubject (pSubjectID INT) RETURNS INT(11)
NO SQL
BEGIN
    DECLARE vSubjectUsage BOOLEAN DEFAULT FALSE;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;

    SET vSubjectUsage = EXISTS(
        SELECT * FROM disciplines
        WHERE disciplines.SubjectID = pSubjectID LIMIT 1
    );
    IF vSubjectUsage 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: check php usage
DROP FUNCTION IF EXISTS CheckAccountExistence//
CREATE FUNCTION CheckAccountExistence (
    pData TEXT CHARSET utf8,
    pType enum('login','email', 'code')
) RETURNS BOOLEAN # TRUE - exist, FALSE - doesn't
READS SQL DATA
RETURN EXISTS(
    SELECT * FROM accounts
        WHERE CASE pType
            WHEN 'login' THEN pData = accounts.Login
            WHEN 'email' THEN pData = accounts.EMail
            WHEN 'code' THEN pData = accounts.ActivationCode
            ELSE FALSE
        END
    LIMIT 1
)//

DROP FUNCTION IF EXISTS CreateAccount//
CREATE FUNCTION CreateAccount (
    pLastName VARCHAR(30) CHARSET utf8,
    pFirstName VARCHAR(30) CHARSET utf8,
    pSecondName VARCHAR(30) CHARSET utf8,
    pCode varchar(40) CHARSET utf8,
    pUserRoleID int
) RETURNS int(11)
NO SQL
BEGIN
    INSERT INTO accounts (Login , Password , EMail, LastName, FirstName, SecondName, UserRoleID, ActivationCode)
        VALUES  ( NULL, NULL, NULL, pLastName, pFirstName, pSecondName, pUserRoleID, pCode);
    RETURN LAST_INSERT_ID();
END//

DROP FUNCTION IF EXISTS Account_CreateActivated//
CREATE FUNCTION Account_CreateActivated (
    pLastName VARCHAR(30) CHARSET utf8,
    pFirstName VARCHAR(30) CHARSET utf8,
    pSecondName VARCHAR(30) CHARSET utf8,
    pExternalID varchar(40) CHARSET utf8,
    pUserRoleID int
) RETURNS int(11)
NO SQL
    BEGIN
        INSERT INTO accounts (ExternalID, Login , Password , EMail, LastName, FirstName, SecondName, UserRoleID)
        VALUES  ( pExternalID, NULL, NULL, NULL, pLastName, pFirstName, pSecondName, pUserRoleID);
        RETURN LAST_INSERT_ID();
    END//

# return:
#   -1 - unknown error.
#   -2 - code doesn't exists.
#   -3 - email already registered.
#   -4 - login already registered.
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;

    IF CheckAccountExistence(pEMail, 'email') THEN
        RETURN -3;
    END IF;
    IF CheckAccountExistence(pLogin, 'login') THEN
        RETURN -4;
    END IF;

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

    RETURN IF(ROW_COUNT() = 0, -2, @vAccountID); # account with this Code not found
END //

DROP FUNCTION IF EXISTS ChangeAccountData//
CREATE FUNCTION ChangeAccountData (
    pUserID INT,
    pData TEXT CHARSET utf8,
    pType enum('login', 'email', 'password', 'LastName', 'FirstName', 'SecondName')
) RETURNS int(11) # 1 - success, 0 - failed
NO SQL
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN 0;

    CASE pType
        WHEN 'login' THEN
            UPDATE accounts
                SET accounts.Login = pData
                WHERE accounts.ID = pUserID
                LIMIT 1;
        WHEN 'email' THEN
            UPDATE accounts
                SET accounts.EMail = pData
                WHERE accounts.ID = pUserID
                LIMIT 1;
        WHEN 'password' THEN
            UPDATE accounts
                SET accounts.Password = SHA1(pData)
                WHERE accounts.ID = pUserID
                LIMIT 1;
        WHEN 'LastName' THEN
            UPDATE accounts
                SET accounts.LastName = pData
                WHERE accounts.ID = pUserID
                LIMIT 1;
        WHEN 'FirstName' THEN
            UPDATE accounts
                SET accounts.FirstName = pData
                WHERE accounts.ID = pUserID
                LIMIT 1;
        WHEN 'SecondName' THEN
            UPDATE accounts
                SET accounts.SecondName = pData
                WHERE accounts.ID = pUserID
                LIMIT 1;
    END CASE;

    RETURN 1;
END //

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

DROP FUNCTION IF EXISTS ChangeTeacherInfo//
CREATE FUNCTION ChangeTeacherInfo (
    pExternalID VARCHAR(40) CHARSET utf8,
    pTeacherID INT,
    pLastName VARCHAR(30) CHARSET utf8,
    pFirstName VARCHAR(30) CHARSET utf8,
    pSecondName VARCHAR(30) CHARSET utf8,
    pJobPositionID INT,
    pDepartmentID INT,
    pStatus BOOLEAN
) RETURNS int(11) # -1 if teacher doesn't exists, otherwise 0
NO SQL
BEGIN
    DECLARE vAccountID INT DEFAULT -1;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;

    SELECT accounts.ID
        INTO vAccountID
        FROM accounts
        INNER JOIN teachers ON accounts.ID = teachers.AccountID
        WHERE teachers.ID = pTeacherID;

    # set new info
    UPDATE accounts
    SET accounts.ExternalID = pExternalID,
        accounts.LastName = pLastName,
        accounts.FirstName = pFirstName,
        accounts.SecondName = pSecondName
    WHERE accounts.ID = vAccountID
    LIMIT 1;

    UPDATE teachers
        SET teachers.JobPositionID = pJobPositionID,
            teachers.DepartmentID = pDepartmentID,
            teachers.Status = pStatus
        WHERE teachers.ID = pTeacherID
        LIMIT 1;
    RETURN ROW_COUNT()-1;
END //

-- Создание неактивированного преподавателя без привязки к 1С
DROP FUNCTION IF EXISTS Teacher_Create//
CREATE FUNCTION Teacher_Create (
    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) # 0 >= success, <0 failed
NO SQL
BEGIN
    DECLARE vAccountID INT DEFAULT -1;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;

    SET vAccountID = CreateAccount(pLastName, pFirstName, pSecondName, pActivationCode, GetUserRole('teacher'));

    # add new teacher
    INSERT INTO teachers (AccountID, JobPositionID, DepartmentID) VALUES
        (vAccountID, pJobPositionID, pDepartmentID);
    RETURN LAST_INSERT_ID();
END //

-- Создание привязанного к 1С и уже активированного преподавателя
DROP FUNCTION IF EXISTS Teacher_CreateActivated//
CREATE FUNCTION Teacher_CreateActivated (
    pLastName VARCHAR(30) CHARSET utf8,
    pFirstName VARCHAR(30) CHARSET utf8,
    pSecondName VARCHAR(30) CHARSET utf8,
    pJobPositionID INT,
    pDepartmentID  INT,
    pExternalID VARCHAR(40) CHARSET utf8
) RETURNS int(11) # 0 >= success, <0 failed
NO SQL
    BEGIN
        DECLARE vAccountID INT DEFAULT -1;
        DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;

        SET vAccountID = Account_CreateActivated(pLastName, pFirstName, pSecondName, pExternalID, GetUserRole('teacher'));

        # add new teacher
        INSERT INTO teachers (AccountID, JobPositionID, DepartmentID) VALUES
            (vAccountID, pJobPositionID, pDepartmentID);
        RETURN LAST_INSERT_ID();
    END //

-- -1 - не сотрудник деканата и не преподаватель дисциплины
-- 0 - только чтение
-- 1 - редактирование
DROP FUNCTION IF EXISTS GetEditRightsForTeacher//
CREATE FUNCTION GetEditRightsForTeacher (
    pTeacherID INT,
    pDisciplineID INT
) RETURNS int(11)
READS SQL DATA
BEGIN
    DECLARE vUserRole INT DEFAULT -1;

    IF InternalIsTeacherBound(pTeacherID, pDisciplineID) > 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
        LIMIT 1;
    RETURN (vUserRole = GetUserRole('dean'), 0, -1);
END //

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

        SELECT teachers.ID INTO pID
        FROM teachers
            INNER JOIN accounts ON teachers.AccountID = accounts.ID
        WHERE accounts.ExternalID = pTeacherExternalID
        LIMIT 1;

        RETURN pID;
    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,
    pGroupID INT,
    pActivationCode VARCHAR(40) CHARSET utf8,
    pSemesterID INT
)   RETURNS int(11)
NO SQL
BEGIN
    DECLARE vAccountID, vStudentID INT DEFAULT -1;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;

    SET vAccountID = CreateAccount(pLastName, pFirstName, pSecondName, pActivationCode, GetUserRole('student'));

    # create student
    INSERT INTO students (AccountID)
        VALUES  (vAccountID);
    SET vStudentID = LAST_INSERT_ID();

    RETURN ControlStudentGroup(vStudentID, pGroupID, 'common', pSemesterID);
END //

DROP FUNCTION IF EXISTS CreateStudentGroupSearch//
CREATE FUNCTION CreateStudentGroupSearch (
    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,
    pSemesterID INT
)   RETURNS int(11)
NO SQL
BEGIN
    DECLARE vGroupID INT DEFAULT -1;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;

    SET vGroupID = FindGroup(pGradeID, pGroupNum, pFacultyID);
    IF vGroupID <= 0 THEN
        RETURN -1;
    END IF;
    RETURN CreateStudent(pLastName, pFirstName, pSecondName, vGroupID, pActivationCode, pSemesterID);
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 enum('bachelor', 'master', 'specialist'),
    pSpecName VARCHAR(200) CHARSET utf8,
    pFacultyID INT,
    pActivationCode VARCHAR(40) CHARSET utf8,
    pSemesterID INT
)   RETURNS int(11)
NO SQL
BEGIN
    DECLARE vAccountID, vGradeID, vSpecID, vGroupID, vYear INT DEFAULT -1;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;

    # get specialization id
    INSERT INTO specializations (Name, Abbr, FacultyID) VALUES  (pSpecName, NULL, pFacultyID)
        ON DUPLICATE KEY UPDATE
            specializations.ID = LAST_INSERT_ID(specializations.ID);
    SET vSpecID = LAST_INSERT_ID();

    SET vYear = COALESCE((SELECT Year FROM semesters WHERE semesters.ID = pSemesterID LIMIT 1), -1);
    SET vGradeID = CreateGrade(pGradeNum, pDegree);
    SET vGroupID = CreateGroup(vGradeID, pGroupNum, vSpecID, NULL, vYear);
    RETURN CreateStudent(pLastName, pFirstName, pSecondName, vGroupID, pActivationCode, pSemesterID);
END //

# Give a student an academic leave or attach him to group.
#   params:
#      StudentID (int)
#      GroupID (int)   : -1, to update all appropriate 'common' records
#      State (enum)
#      SemesterID (int)
DROP FUNCTION IF EXISTS ControlStudentGroup//
CREATE FUNCTION ControlStudentGroup (
    pStudentID INT,
    pGroupID INT,
    pState enum('common', 'outlet', 'expulsion', 'leave'),
    pSemesterID INT
) RETURNS int(11)
NO SQL
BEGIN
    DECLARE vChecker INT DEFAULT 0;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;

    INSERT INTO students_groups (StudentID, GroupID, SemesterID, State)
        VALUES (pStudentID, pGroupID, pSemesterID, pState)
        ON DUPLICATE KEY UPDATE
            students_groups.GroupID = pGroupID,
            students_groups.State = pState,
            students_groups.Date = CURDATE();

    RETURN ROW_COUNT()-1;
END //

DROP FUNCTION IF EXISTS RemoveFromGroupInSemester//
CREATE FUNCTION RemoveFromGroupInSemester (
    pStudentID INT,
    pGroupID INT,
    pSemesterID INT
) RETURNS int(11)
NO SQL
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;

    DELETE FROM students_groups
        WHERE   students_groups.GroupID = pGroupID
                and students_groups.StudentID = pStudentID
                and students_groups.SemesterID = pSemesterID
                and students_groups.State = 'common'
        LIMIT 1;
    RETURN ROW_COUNT()-1;
END //

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

DROP FUNCTION IF EXISTS GetDisciplineProperty//
CREATE FUNCTION GetDisciplineProperty (
    pDisciplineID INT,
    pType enum('grade', 'subject', 'author', 'semester', 'milestone', 'type', 'maxrate')
) RETURNS int(11)
READS SQL DATA
BEGIN
    DECLARE vRes INT DEFAULT -1;

    SELECT CASE pType
            WHEN 'semester' THEN disciplines.SemesterID
            WHEN 'author' THEN disciplines.AuthorID
            WHEN 'grade' THEN disciplines.GradeID
            WHEN 'subject' THEN disciplines.SubjectID
            WHEN 'milestone' THEN disciplines.Milestone
            WHEN 'type' THEN disciplines.ExamType + 0
            WHEN 'maxrate' THEN disciplines.MaxRate
        END INTO vRes
        FROM disciplines
        WHERE disciplines.ID = pDisciplineID
        LIMIT 1;

    RETURN vRes;
END //

DROP FUNCTION IF EXISTS Discipline_Create//
CREATE FUNCTION Discipline_Create (
    pTeacherID INT,
    pGradeID INT,
    pSubjectID INT,
    pExamType enum('exam', 'credit', 'grading_credit'),
    pLectureCount INT,
    pPracticeCount INT,
    pLabCount INT,
    pFacultyID INT,
    pSemesterID INT,
    pSubtype enum('scientific_coursework', 'disciplinary_coursework')
) RETURNS int(11)
NO SQL
BEGIN
    DECLARE vDisciplineID INT DEFAULT -1;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;

    # todo: make more flexible creation of coursework
    # I mean, while creating scientific coursework
    #    we don't have the SubjectID field, but we must.
    #    346 is used as a default id for scientific courseworks.
    #    This constant is duplicated in Model_Helper_CourseWorkBuilder

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

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

    SELECT Discipline_BindTeacher(vDisciplineID, pTeacherID) INTO @vTemp;

    # add exam and extra modules
    IF pExamType = 'exam' THEN
        SELECT AddModuleExamUnsafe(vDisciplineID) INTO @vTemp;
    END IF;
    SELECT AddModuleExtra(pTeacherID, vDisciplineID) INTO @vTemp;

    RETURN vDisciplineID;
END //

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

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

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

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

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

    RETURN vMilestone;
END //

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

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

    # TODO: move to php
    IF  InternalIsMapLocked(pDisciplineID) THEN
        RETURN -1;
    END IF;
    IF GetDisciplineProperty(pDisciplineID, 'grade') <=> pGradeID THEN
        RETURN 0;
    END IF;

    SELECT DetachAllStudents(pDisciplineID) INTO @vRes;
    # set grade
    UPDATE disciplines
        SET disciplines.GradeID = pGradeID
        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
    # TODO: move to php
    IF  NOT InternalIsTeacherAuthor(pTeacherID, pDisciplineID) THEN
        RETURN -1;
    END IF;
    RETURN ChangeDisciplineGradeUnsafe(pDisciplineID, pGradeID);
END //

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

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

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

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

        # TODO: extract method addExtraModule
        IF pExamType = 'exam' THEN # change to exam
            # count discipline's current max rate
            SET vChecker = GetDisciplineProperty(pDisciplineID, 'maxrate');
            IF vChecker >= 61 THEN # can't add exam module ( > 100 points)
                RETURN 1;
            END IF;
            SET vChecker = AddModuleExamUnsafe(pDisciplineID);

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

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

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

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

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

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

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

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

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

    DELETE FROM disciplines_students
        WHERE   disciplines_students.DisciplineID = pDisciplineID AND
                disciplines_students.StudentID IN (
                    SELECT students_groups.StudentID FROM students_groups