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 'detach';

    SELECT view_disciplines_recordbooks.Type INTO vAttachType
    FROM view_disciplines_recordbooks
    WHERE view_disciplines_recordbooks.RecordBookID = pRecordBookID AND
          view_disciplines_recordbooks.DisciplineID = pDisciplineID
    LIMIT 1;

    RETURN 'attach' = COALESCE(vAttachType, 'attach');
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,
    pSpecName VARCHAR(200) CHARSET utf8,
    pFacultyID INT,
    pYear INT
) RETURNS int(11) # group id
NO SQL
BEGIN
    DECLARE vGroupID, vSpecId, vGroupYear, vIsSpecMatch INT DEFAULT -1;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;

    # create specialization
    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();

    # create group
    INSERT INTO study_groups (GradeID, GroupNum, FacultyID) VALUES (pGradeID, pGroupNum, pFacultyID)
        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 = vSpecId
        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)
        VALUES (vGroupID, pYear, vSpecId);
    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 Subject_Create;//
CREATE FUNCTION Subject_Create (
    pFacultyID INT,
    pSubjectName TEXT CHARSET utf8,
    pSubjectAbbr VARCHAR(20) CHARSET utf8,
    pExternalID VARCHAR(9) 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, ExternalID) VALUES (pSubjectName, pSubjectAbbr, pExternalID)
        ON DUPLICATE KEY UPDATE
            subjects.ID = LAST_INSERT_ID(subjects.ID),
            subjects.Name = pSubjectName,
            subjects.Abbr = COALESCE(pSubjectAbbr, subjects.Abbr),
            subjects.ExternalID = COALESCE(pExternalID, subjects.ExternalID);
    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 //

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

    SELECT subjects.ID INTO vID
    FROM subjects
    WHERE subjects.ExternalID = pSubjectExternalID
    LIMIT 1;

    RETURN vID;
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,
    pINILA VARCHAR(40) CHARSET utf8,
    pUserRoleID int
) RETURNS int(11)
NO SQL
    BEGIN
        INSERT INTO accounts (ExternalID, INILA, Login , Password , EMail, LastName, FirstName, SecondName, UserRoleID)
        VALUES  ( pExternalID, pINILA, 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 //

-- Получение внутреннего ID по хешу СНИЛС из 1С
DROP FUNCTION IF EXISTS Account_GetIDFromINILA//
CREATE FUNCTION Account_GetIDFromINILA (
    pAccountINILA VARCHAR(9)
) RETURNS int(11)
READS SQL DATA
    BEGIN
        DECLARE pID INT DEFAULT -1;

        SELECT accounts.ID INTO pID
        FROM accounts
        WHERE accounts.INILA = pAccountINILA
        LIMIT 1;

        RETURN pID;
    END //

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

        SELECT accounts.ID INTO pID
        FROM accounts
        WHERE accounts.ExternalID = pAccountExternalID
        LIMIT 1;

        RETURN pID;
    END //

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

DROP FUNCTION IF EXISTS ChangeTeacherInfo//
CREATE FUNCTION ChangeTeacherInfo (
    pExternalID VARCHAR(40) CHARSET utf8,
    pINILA 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.INILA = pINILA,
        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,
    pFacultyID  INT,
    pDepartmentID  INT,
    pExternalID VARCHAR(40) CHARSET utf8,
    pINILA  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, pINILA, GetUserRole('teacher'));

        # add new teacher
        INSERT INTO teachers (AccountID, JobPositionID, DepartmentID, FacultyID) VALUES
            (vAccountID, pJobPositionID, pDepartmentID, pFacultyID);
        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 VARCHAR(9)
) 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 //

-- Получение внутреннего ID по хешу СНИЛС из 1С
DROP FUNCTION IF EXISTS Teacher_GetIDFromINILA//
CREATE FUNCTION Teacher_GetIDFromINILA (
    pTeacherINILA VARCHAR(40)
) 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.INILA = pTeacherINILA
        LIMIT 1;

        RETURN pID;
    END //

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

DROP FUNCTION IF EXISTS CreateOrAddStudent//
CREATE FUNCTION CreateOrAddStudent (
    pExternalID VARCHAR(9) CHARSET utf8,
    pLastName VARCHAR(30) CHARSET utf8,
    pFirstName VARCHAR(30) CHARSET utf8,
    pSecondName VARCHAR(30) CHARSET utf8
)   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, null, GetUserRole('student'));

        IF pExternalID is not null THEN
            UPDATE accounts
                SET ExternalID = pExternalID
                WHERE ID = vAccountID;
        END IF;

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

        RETURN vStudentID;
    END //

DROP FUNCTION IF EXISTS Student_ChangeInfo//
CREATE FUNCTION Student_ChangeInfo (
    pID INT(11),
    pExternalID VARCHAR(9) CHARSET utf8,
    pLastName VARCHAR(30) CHARSET utf8,
    pFirstName VARCHAR(30) CHARSET utf8,
    pSecondName VARCHAR(30) CHARSET utf8
)   RETURNS int(11)
NO SQL
    BEGIN
        DECLARE vAccountID, vStudentID INT DEFAULT -1;
        DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;

        SELECT students.AccountID INTO vAccountID
            FROM `students`
            WHERE students.ID = pID;

        UPDATE `accounts`
            SET accounts.ExternalID = COALESCE(pExternalID, accounts.ExternalID),
                accounts.LastName = COALESCE(pLastName, accounts.LastName),
                accounts.FirstName = COALESCE(pFirstName, accounts.FirstName),
                accounts.SecondName = COALESCE(pSecondName, accounts.SecondName)
            WHERE accounts.ID = vAccountID;

        RETURN 0;
    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 vGradeID, vGroupID, vYear INT DEFAULT -1;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;

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

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

        SELECT students.ID INTO pID
        FROM accounts
        JOIN students ON accounts.ID = students.AccountID
        WHERE accounts.ExternalID = pAccountExternalID
        LIMIT 1;

        RETURN pID;
    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 (
    pRecordBookID 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 (RecordBookID, GroupID, SemesterID, State)
        VALUES (pRecordBookID, 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 (
    pRecordBook 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.RecordBookID = pRecordBook
                and students_groups.SemesterID = pSemesterID
                and students_groups.State = 'common'
        LIMIT 1;
    RETURN ROW_COUNT()-1;
END //

# -------------------------------------------------------------------------------------------
# Label: record books
# -------------------------------------------------------------------------------------------

DROP FUNCTION IF EXISTS RecordBook_Create//
DROP FUNCTION IF EXISTS RecordBook_Bind//
CREATE FUNCTION RecordBook_Bind (
    pExternalID VARCHAR(30) CHARSET utf8,
    pStudentID  int(11),
    pPlanID     int(11)
)   RETURNS int(11)
NO SQL
    BEGIN
        DECLARE vRecordBookID INT DEFAULT -1;
        DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;

        INSERT INTO record_books (StudentID, ExternalID)
            VALUES  (pStudentID, pExternalID)
            ON DUPLICATE KEY UPDATE StudentID = pStudentID,
                                    record_books.ID = LAST_INSERT_ID(record_books.ID);
        SET vRecordBookID = LAST_INSERT_ID();

        INSERT INTO record_books_plans (RecordBookID, StudyPlanID)
            VALUES (vRecordBookID, pPlanID)
            ON DUPLICATE KEY
                UPDATE record_books_plans.ID = LAST_INSERT_ID(record_books_plans.ID);

        RETURN vRecordBookID;
    END //

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

        SELECT record_books.ID INTO pID
        FROM record_books
        WHERE record_books.ExternalID = pRecordBookExternalID
        LIMIT 1;

        RETURN pID;
    END //

DROP FUNCTION IF EXISTS RecordBook_ChangeInfo//
CREATE FUNCTION RecordBook_ChangeInfo (
    pID INT(11),
    pExternalID VARCHAR(30) CHARSET utf8,
    pStudentID  int(11),
    pPlanID     int(11)
)   RETURNS int(11)
READS SQL DATA
    BEGIN
        DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;

        UPDATE record_books
            SET record_books.ExternalID = COALESCE(pExternalID, record_books.ExternalID)
            WHERE record_books.ID = pID;
        SELECT RecordBook_Bind(pExternalID, pStudentID, pPlanID) INTO @vRes;
        RETURN 0;
    END //

# -------------------------------------------------------------------------------------------
# Label: plans
# -------------------------------------------------------------------------------------------

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

        SELECT study_plans.ID INTO vID
        FROM study_plans
        WHERE study_plans.ExternalID = pPlanExternalID
        LIMIT 1;

        RETURN vID;
    END //

DROP FUNCTION IF EXISTS Plan_Create//
CREATE FUNCTION Plan_Create (
    pPlanExternalID VARCHAR(9),
    pYear INT,
    pFacultyID INT
) RETURNS int(11)
READS SQL DATA
    BEGIN
        DECLARE vID INT DEFAULT -1;

        INSERT INTO study_plans (ExternalID, Year, FacultyID) VALUES (pPlanExternalID, pYear, pFacultyID)
            ON DUPLICATE KEY UPDATE study_plans.Year = pYear, study_plans.FacultyID = pFacultyID;
        SET vID = LAST_INSERT_ID();

        RETURN vID;
    END //

DROP FUNCTION IF EXISTS Plan_ChangeInfo //
CREATE FUNCTION Plan_ChangeInfo(
    pID INT,
    pYear INT,
    pFacultyID INT
) RETURNS INT(11)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;
    UPDATE study_plans
        SET study_plans.Year = COALESCE(pYear, study_plans.Year),
            study_plans.FacultyID = COALESCE(pFacultyID, study_plans.FacultyID)
        WHERE study_plans.ID = pID;
    RETURN 0;
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 //

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