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

DROP FUNCTION IF EXISTS GetAccCountByCode//
DROP FUNCTION IF EXISTS GetAccCountByMail//
DROP FUNCTION IF EXISTS GetAccCountByLogin//

DROP FUNCTION IF EXISTS ChangePassword//
DROP FUNCTION IF EXISTS ChangeLogin//
DROP FUNCTION IF EXISTS ChangeMail//

DROP FUNCTION IF EXISTS GetRateForDisc//

drop function if exists InternalIsTeacherBounded//
DROP FUNCTION IF EXISTS SetSemesterID//
DROP FUNCTION IF EXISTS AddDiscipline//

DROP FUNCTION IF EXISTS GetMaxRateForDisc//

DROP FUNCTION IF EXISTS BindTeacher//

DROP FUNCTION IF EXISTS GetDisciplineSemesterID//

DROP FUNCTION IF EXISTS SetBitmaskByPagename//
DROP FUNCTION IF EXISTS GetBitmaskByPagename//
DROP FUNCTION IF EXISTS SetSettings//

DROP FUNCTION IF EXISTS CountRatings//


DROP FUNCTION IF EXISTS UpdateRequest//


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

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

    RETURN vChecker;
END //


# check, that student really take this course
DROP FUNCTION IF EXISTS InternalIsStudentAttached//
CREATE FUNCTION `InternalIsStudentAttached` (
    `pStudentID` INT,
    `pDisciplineID` INT,
    `pSemesterID` INT
) RETURNS BOOLEAN
    NO SQL
BEGIN
    DECLARE vAttachType enum('attach','detach') DEFAULT NULL;

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

    RETURN EXISTS(
        SELECT * FROM `students_groups`
        INNER JOIN `disciplines_groups` ON  disciplines_groups.DisciplineID = pDisciplineID AND
                                            disciplines_groups.GroupID = students_groups.GroupID
        WHERE   students_groups.StudentID = pStudentID AND
                students_groups.SemesterID = pSemesterID AND
                students_groups.State != 'expulsion'
        LIMIT 1
    );
END //



# check, that teacher teach this course
DROP FUNCTION IF EXISTS InternalIsTeacherBound//
CREATE FUNCTION InternalIsTeacherBound (
    `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
            LIMIT 1
    );
END //


DROP FUNCTION IF EXISTS InternalIsTeacherAuthor//
CREATE FUNCTION `InternalIsTeacherAuthor` (
    `pTeacherID` INT,
    `pDisciplineID` INT
) RETURNS BOOLEAN
NO SQL
BEGIN
    DECLARE vAuthorID INT DEFAULT -1;

    SELECT disciplines.AuthorID INTO vAuthorID
        FROM `disciplines`
        WHERE disciplines.ID = pDisciplineID
        LIMIT 1;

    RETURN ( vAuthorID = pTeacherID );
END //



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

    INSERT INTO `exam_period_options`
        (StudentID, SubmoduleID, Type) VALUES(pStudentID, pSubmoduleID, pType)
        ON DUPLICATE KEY UPDATE
            exam_period_options.Type = pType;
    RETURN  0;
END //


# check, regular + exam rate == 100
DROP FUNCTION IF EXISTS InternalIsMapCreated//
CREATE FUNCTION `InternalIsMapCreated` (
    `pDisciplineID` INT
) RETURNS int(11)
NO SQL
BEGIN
    DECLARE vMaxRate INT DEFAULT -1;

    SELECT disciplines.MaxRate INTO vMaxRate
        FROM disciplines
        WHERE disciplines.ID = pDisciplineID
        LIMIT 1;
    RETURN ( vMaxRate = 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
# -------------------------------------------------------------------------------------------

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



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

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

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

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

# create department or return existing
DROP FUNCTION IF EXISTS CreateDepartment //
DROP FUNCTION IF EXISTS Department_Create //
CREATE FUNCTION Department_Create (
    `pName` VARCHAR(200) CHARSET utf8,
    `pFacultyID` INT(11)
) RETURNS INT(11) # department id or -1 if failed
NO SQL
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;

    INSERT INTO departments
        (Name, FacultyID) VALUES (pName, pFacultyID)
        ON DUPLICATE KEY UPDATE
            departments.ID = LAST_INSERT_ID(departments.ID);
    RETURN LAST_INSERT_ID();
END //

DROP FUNCTION IF EXISTS Department_Search //
CREATE FUNCTION Department_Search (
    `pName` VARCHAR(200) CHARSET utf8,
    `pFacultyID` INT(11)
) RETURNS INT(11) # department id of -1 if not found
NO SQL
BEGIN
    DECLARE vID INT DEFAULT -1;

    SELECT departments.ID INTO vID
        FROM departments
        WHERE   departments.Name = pName AND
                departments.FacultyID = pFacultyID
        LIMIT 1;

    RETURN vID;
END //

# -------------------------------------------------------------------------------------------
# Label: specializations
# -------------------------------------------------------------------------------------------

# -------------------------------------------------------------------------------------------
# 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 vGradeID INT DEFAULT -1;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;

    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
) RETURNS int(11) # group id
    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)
        ON DUPLICATE KEY UPDATE
            study_groups.ID = LAST_INSERT_ID(study_groups.ID);
    RETURN LAST_INSERT_ID();
END //


DROP FUNCTION IF EXISTS GetGroup//
CREATE FUNCTION `GetGroup` (
    `pGradeID` INT,
    `pGroupNum` INT,
    `pFacultyID` INT
) RETURNS int(11) # groupID or -1 if failed
NO SQL
BEGIN
    DECLARE vGroupID INT DEFAULT -1;

    SELECT study_groups.ID INTO vGroupID
        FROM `study_groups`
        INNER JOIN `specializations` ON specializations.ID = study_groups.SpecializationID
        WHERE   study_groups.GradeID = pGradeID AND
                study_groups.GroupNum = pGroupNum AND
                specializations.FacultyID = pFacultyID
        LIMIT 1;

    RETURN vGroupID;
END //


DROP FUNCTION IF EXISTS GetStudentGroup//
CREATE FUNCTION `GetStudentGroup` (
    `pStudentID` INT,
    `pSemesterID` INT
) RETURNS int(11) # groupID or -1 if failed
    NO SQL
BEGIN
    DECLARE vGroupID INT DEFAULT -1;

    SELECT students_groups.GroupID INTO vGroupID
        FROM `students_groups`
        WHERE   students_groups.StudentID = pStudentID AND
                students_groups.SemesterID = pSemesterID AND
                students_groups.State != 'expulsion'
        LIMIT 1;

    RETURN vGroupID;
END //




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

DROP FUNCTION IF EXISTS CreateSubject//
CREATE FUNCTION `CreateSubject` (
    `pFacultyID` INT,
    `pSubjectName` TEXT CHARSET utf8,
    `pSubjectAbbr` VARCHAR(20) CHARSET utf8
)   RETURNS int(11)
NO SQL
BEGIN
    DECLARE vSubjectID INT DEFAULT -1;
    SET @vChecker := -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 = ( @vChecker := LAST_INSERT_ID(subjects.ID));
    SET vSubjectID = LAST_INSERT_ID();

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

    RETURN ( @vChecker > 0 ) + 0;
END //


DROP FUNCTION IF EXISTS DeleteSubject //
CREATE FUNCTION DeleteSubject (
    `pSubjectID` INT
) RETURNS INT(11) # 0 - success
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
# -------------------------------------------------------------------------------------------




DROP FUNCTION IF EXISTS CheckAccountExistence//
CREATE FUNCTION `CheckAccountExistence` (
    `pData` TEXT CHARSET utf8,
    `pType` enum('login','email', 'code')
) RETURNS BOOLEAN # TRUE - exist, FALSE - doesn't
NO SQL
BEGIN
    DECLARE vRes BOOLEAN DEFAULT FALSE;

    SET vRes = 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
    );

    RETURN vRes;
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
    DECLARE vExistEmail, vExistLogin BOOLEAN DEFAULT FALSE;
    # check for matching with existing accounts (note: Login & E-Mail are unique)
    DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;

    SET vExistEmail = CheckAccountExistence(pEMail, 'email');
    IF vExistEmail THEN
        RETURN -3;
    END IF;
    SET vExistLogin = CheckAccountExistence(pLogin, 'login');
    IF vExistLogin 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); # 0 - 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')
) 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;
    END CASE;

    RETURN 1;
END //



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

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

    # TODO: extract method - log sign in
    INSERT INTO `logs_signin` # logging
        (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) # -1 if teacher doesn't exists, otherwise 0
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;
END //



DROP FUNCTION IF EXISTS CreateTeacher//
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;

    # TODO: kill magic constants (user role 2 - common teacher)
    # create 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 LAST_INSERT_ID();
END //

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

    SET vIsBound = InternalIsTeacherBound(pTeacherID, pDisciplineID);
    IF vIsBound > 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;

    # TODO: magic constants (4 - сотрудник деканата)
    RETURN (vUserRole = 4, 0, -1);
END //



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

# TODO: magic constants (UserRoleID)
# TODO: group id instead num and grade
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,
    `pSemesterID` INT
)   RETURNS int(11)
NO SQL
BEGIN
    DECLARE vAccountID, vGroupID, vStudentID, vSemesterID INT DEFAULT -1;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;

    SET vGroupID = GetGroup(pGradeID, pGroupNum, pFacultyID);
    IF vGroupID <= 0 THEN
        RETURN -1;
    END IF;

    # create new account, UserRoleID: 1 = student
    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);
    SET vStudentID = LAST_INSERT_ID();

    RETURN ControlStudentGroup(vStudentID, vGroupID, 'common', 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(50) CHARSET utf8,
    `pFacultyID` INT,
    `pActivationCode` VARCHAR(40) CHARSET utf8,
    `pSemesterID` INT
)   RETURNS int(11)
NO SQL
BEGIN
    DECLARE vAccountID, vGradeID, vSpecID, vGroupID 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 vGradeID = CreateGrade(pGradeNum, pDegree);
    SET vGroupID = CreateGroup(vGradeID, pGroupNum, vSpecID, NULL);
    RETURN CreateStudent(pLastName, pFirstName, pSecondName, vGradeID, pGroupNum, pFacultyID, 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 vChecker INT DEFAULT 0;
    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')
) RETURNS int(11)
    NO SQL
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
        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 vChecker, 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();


    SET vChecker = Discipline_BindTeacher(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;

    # TODO: move to php
    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;

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



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;

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

    SET vCurGradeID = GetDisciplineProperty(pDisciplineID, 'grade');
    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` 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;

    # 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