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 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);
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 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 //
-- Получение внутреннего 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
DECLARE pID INT DEFAULT -1;
SELECT disciplines.ID INTO pID
FROM disciplines
WHERE disciplines.ExternalID = pDisciplineExternalID
LIMIT 1;
RETURN pID;
END //
-- Обновление данных дисциплины при синхронизации
DROP FUNCTION IF EXISTS Discipline_ChangeInfo//
CREATE FUNCTION Discipline_ChangeInfo (
pDisciplineID INT,
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;
IF pSubtype IS NOT NULL THEN
SET pSubjectID = 346;
END IF;
# create discipline
UPDATE `disciplines`
SET disciplines.AuthorID = COALESCE(pTeacherID, disciplines.AuthorID),
disciplines.GradeID = COALESCE(pGradeID, disciplines.GradeID),
disciplines.SubjectID = COALESCE(pSubjectID, disciplines.SubjectID),
disciplines.ExamType = COALESCE(pExamType, disciplines.ExamType),
disciplines.LectureCount = COALESCE(pLectureCount, disciplines.LectureCount),
disciplines.PracticeCount = COALESCE(pPracticeCount, disciplines.PracticeCount),
disciplines.LabCount = COALESCE(pLabCount, disciplines.LabCount),
disciplines.SemesterID = COALESCE(pSemesterID, disciplines.SemesterID),
disciplines.FacultyID = COALESCE(pFacultyID, disciplines.FacultyID),
disciplines.Subtype = COALESCE(pSubtype, disciplines.Subtype)
WHERE disciplines.ID = pDisciplineID;
RETURN 0;
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 ChangeDisciplineGrade//
DROP FUNCTION IF EXISTS ChangeDisciplineGradeUnsafe//
DROP FUNCTION IF EXISTS Discipline_SetGradeUnsafe//
CREATE FUNCTION Discipline_SetGradeUnsafe (
pDisciplineID INT,
pGradeID INT
) RETURNS int(11)
NO SQL
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;
# set grade
UPDATE disciplines
SET disciplines.GradeID = pGradeID
WHERE disciplines.ID = pDisciplineID
LIMIT 1;
RETURN ROW_COUNT()-1;
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, RecordBookID, 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.RecordBookID IN (
SELECT students_groups.RecordBookID FROM students_groups
WHERE students_groups.GroupID = pGroupID AND students_groups.SemesterID = vSemesterID
);
RETURN 0;
END //
DROP FUNCTION IF EXISTS BindGroup//
CREATE FUNCTION BindGroup (
pTeacherID INT,
pDisciplineID INT,
pGroupID INT
) RETURNS int(11)
NO SQL
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -3;
# todo: move to php layer
IF (pTeacherID IS NOT NULL AND NOT InternalIsTeacherBound(pTeacherID, pDisciplineID))
OR InternalIsMapLocked(pDisciplineID) THEN
RETURN -1;
END IF;
-- already bound
IF EXISTS(
SELECT * FROM disciplines_groups
WHERE disciplines_groups.DisciplineID = pDisciplineID AND disciplines_groups.GroupID = pGroupID
) THEN
RETURN 1;
END IF;
# bind whole group
INSERT INTO disciplines_groups (DisciplineID, GroupID) VALUES ( pDisciplineID, pGroupID );
SELECT LogBind(pDisciplineID, pTeacherID, pGroupID, 'attach', 'group') INTO @vRes;
RETURN RemoveStudentsAttach(pDisciplineID, pGroupID); # delete students of this group which were bound to discipline before
END //
DROP FUNCTION IF EXISTS UnbindGroup//
CREATE FUNCTION UnbindGroup (
pTeacherID INT,
pDisciplineID INT,
pGroupID INT
) RETURNS int(11)
NO SQL
BEGIN
# todo: move to php layer
IF (pTeacherID IS NOT NULL AND NOT InternalIsTeacherBound(pTeacherID, pDisciplineID))
OR InternalIsMapLocked(pDisciplineID) THEN
RETURN -1;
END IF;
# detach group from the discipline
DELETE FROM disciplines_groups
WHERE disciplines_groups.DisciplineID = pDisciplineID AND disciplines_groups.GroupID = pGroupID
LIMIT 1;
SELECT LogBind(pDisciplineID, pTeacherID, pGroupID, 'detach', 'group') INTO @vRes;
RETURN RemoveStudentsAttach(pDisciplineID, pGroupID);
END //
DROP FUNCTION IF EXISTS BindStudent//
CREATE FUNCTION BindStudent (
pTeacherID INT,
pDisciplineID INT,
pRecordBookID INT
) RETURNS int(11)
NO SQL
BEGIN
DECLARE vStudentGroupID, vSemesterID, vInGroup INT DEFAULT -1;
DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;
# todo: move to php layer
IF pTeacherID IS NOT NULL AND NOT InternalIsTeacherBound(pTeacherID, pDisciplineID) THEN
RETURN -1;
END IF;
SET vSemesterID = GetDisciplineProperty(pDisciplineID, 'semester');
SET vStudentGroupID = GetStudentGroup(pRecordBookID, vSemesterID);
# check if student's group is bound yet
SET vInGroup = EXISTS(
SELECT * FROM disciplines_groups
WHERE disciplines_groups.DisciplineID = pDisciplineID AND disciplines_groups.GroupID = vStudentGroupID
LIMIT 1
);
# bind student
IF vInGroup THEN # student in group -> try to remove detached attribute
DELETE FROM disciplines_students
WHERE disciplines_students.DisciplineID = pDisciplineID AND disciplines_students.RecordBookID = pRecordBookID
LIMIT 1;
ELSE # bind stand alone student ;(
INSERT INTO disciplines_students (DisciplineID, RecordBookID, Type) VALUES (pDisciplineID, pRecordBookID, 'attach')
ON DUPLICATE KEY UPDATE
disciplines_students.Type = 'attach';
END IF;
RETURN LogBind(pDisciplineID, pTeacherID, pRecordBookID, 'attach', 'student');
END //
DROP FUNCTION IF EXISTS UnbindStudent//
CREATE FUNCTION UnbindStudent (
pTeacherID INT,
pDisciplineID INT,
pRecordBookID INT
) RETURNS int(11)
NO SQL
BEGIN
DECLARE vInGroup, vStudentGroupID, vSemesterID INT DEFAULT -1;
DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;
# todo: move to php layer
IF pTeacherID IS NOT NULL AND NOT InternalIsTeacherBound(pTeacherID, pDisciplineID) THEN
RETURN -1;
END IF;
SET vSemesterID = GetDisciplineProperty(pDisciplineID, 'semester');
SET vStudentGroupID = GetStudentGroup(pRecordBookID, vSemesterID);
# 2. check if student's group is bound yet
SET vInGroup = EXISTS(
SELECT * FROM disciplines_groups
WHERE disciplines_groups.DisciplineID = pDisciplineID AND
disciplines_groups.GroupID = vStudentGroupID
LIMIT 1
);
IF vInGroup > 0 THEN # student in general group
INSERT INTO disciplines_students (DisciplineID, RecordBookID, Type)
VALUES (pDisciplineID, pRecordBookID, 'detach')
ON DUPLICATE KEY UPDATE disciplines_students.Type = 'detach';
ELSE
DELETE FROM disciplines_students
WHERE disciplines_students.DisciplineID = pDisciplineID AND
disciplines_students.RecordBookID = pRecordBookID
LIMIT 1;
END IF;
RETURN LogBind(pDisciplineID, pTeacherID, pRecordBookID, 'detach', 'student');
END //
DROP FUNCTION IF EXISTS Discipline_BindTeacher//
CREATE FUNCTION Discipline_BindTeacher (
pDisciplineID INT,
pBindingTeacherID INT
) RETURNS int(11)
NO SQL
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;
# try to insert BindingTeacher in access list
INSERT INTO disciplines_teachers (DisciplineID, TeacherID) VALUES (pDisciplineID, pBindingTeacherID)
ON DUPLICATE KEY UPDATE # just stub
disciplines_teachers.ID = LAST_INSERT_ID(disciplines_teachers.ID);
RETURN 0;
END //
DROP FUNCTION IF EXISTS Discipline_UnbindTeacher//
CREATE FUNCTION Discipline_UnbindTeacher (
pDisciplineID INT,
pBindingTeacher INT
) RETURNS int(11)
NO SQL
BEGIN
DELETE FROM disciplines_teachers
WHERE disciplines_teachers.DisciplineID = pDisciplineID AND disciplines_teachers.TeacherID = pBindingTeacher
LIMIT 1;
RETURN ROW_COUNT()-1;
END //
# assign new author to discipline
DROP FUNCTION IF EXISTS Discipline_Delegate//
CREATE FUNCTION Discipline_Delegate (
pDisciplineID INT,
pNewAuthorID INT
) RETURNS int(11)
NO SQL
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;
SELECT Discipline_BindTeacher(pDisciplineID, pNewAuthorID) INTO @vTemp;
UPDATE disciplines
SET disciplines.AuthorID = pNewAuthorID
WHERE disciplines.ID = pDisciplineID
LIMIT 1;
RETURN ROW_COUNT()-1;
END //
# erase all discipline's rates(and logs), unlock discipline for editing
DROP FUNCTION IF EXISTS ClearDiscipline//
DROP FUNCTION IF EXISTS Discipline_Clear//
CREATE FUNCTION Discipline_Clear (
pAuthorTeacherID INT,
pDisciplineID INT
) RETURNS int(11)
NO SQL
BEGIN
IF NOT InternalIsTeacherAuthor(pAuthorTeacherID, pDisciplineID) THEN
RETURN -1;
END IF;
# clear logs
DELETE FROM logs_rating
WHERE logs_rating.SubmoduleID IN
(SELECT view_roadmap.SubmoduleID
FROM view_roadmap
WHERE view_roadmap.DisciplineID = pDisciplineID);
# clear rating
DELETE FROM rating_table
WHERE rating_table.SubmoduleID IN
(SELECT view_roadmap.SubmoduleID
FROM view_roadmap
WHERE view_roadmap.DisciplineID = pDisciplineID);
# clear exam_period_options
DELETE FROM exam_period_options
WHERE exam_period_options.SubmoduleID IN
(SELECT view_roadmap.SubmoduleID
FROM view_roadmap
WHERE view_roadmap.DisciplineID = pDisciplineID);
# unlock discipline
UPDATE disciplines
SET disciplines.IsLocked = 0
WHERE disciplines.ID = pDisciplineID
LIMIT 1;
RETURN ROW_COUNT()-1;
END //
DROP FUNCTION IF EXISTS Discipline_Delete//
CREATE FUNCTION Discipline_Delete (pDisciplineID INT) RETURNS int(11)
NO SQL
BEGIN
# delete roadmap
DELETE FROM submodules
WHERE submodules.moduleID in (
select modules.id FROM modules
WHERE modules.DisciplineID = pDisciplineID
);
DELETE FROM modules
WHERE modules.DisciplineID = pDisciplineID;
# detach all entities from discipline
DELETE FROM disciplines_teachers
WHERE disciplines_teachers.DisciplineID = pDisciplineID;
DELETE FROM disciplines_students
WHERE disciplines_students.DisciplineID = pDisciplineID;
DELETE FROM disciplines_groups
WHERE disciplines_groups.DisciplineID = pDisciplineID;
DELETE FROM logs_binds_groups
WHERE logs_binds_groups.DisciplineID = pDisciplineID;
DELETE FROM logs_binds_students
WHERE logs_binds_students.DisciplineID = pDisciplineID;
# delete discipline
DELETE FROM disciplines
WHERE disciplines.ID = pDisciplineID
LIMIT 1;
RETURN 0;
END //
# get count of related with discipline records in rating_table
DROP FUNCTION IF EXISTS Discipline_CountRatings//
CREATE FUNCTION Discipline_CountRatings (pDisciplineID INT) RETURNS int(11)
READS SQL DATA
RETURN (
SELECT COUNT(rating_table.StudentID) FROM view_roadmap
LEFT JOIN rating_table ON rating_table.SubmoduleID = view_roadmap.SubmoduleID
WHERE view_roadmap.DisciplineID = pDisciplineID
)//
DROP FUNCTION IF EXISTS RestrictAfterMilestone//
CREATE FUNCTION RestrictAfterMilestone (
pDisciplineID INT,
pMilestone INT
) RETURNS int(11)
NO SQL
BEGIN
UPDATE disciplines
SET disciplines.MilestoneDate = CURDATE(),
disciplines.Milestone = pMilestone
WHERE disciplines.ID = pDisciplineID
LIMIT 1;
RETURN 0;
END //
DROP FUNCTION IF EXISTS RestrictAfterMilestoneForCredits//
CREATE FUNCTION RestrictAfterMilestoneForCredits (
pFacultyID INT,
pMilestone INT,
pSemesterID INT
) RETURNS int(11)
NO SQL
BEGIN
UPDATE disciplines
SET disciplines.MilestoneDate = CURDATE(),
disciplines.Milestone = pMilestone
WHERE disciplines.FacultyID= pFacultyID AND
disciplines.SemesterID = pSemesterID AND
( disciplines.ExamType = 'credit' OR disciplines.ExamType = 'grading_credit');
RETURN 0;
END //
# -------------------------------------------------------------------------------------------
# Label: modules
# Label: roadmap
# -------------------------------------------------------------------------------------------
DROP FUNCTION IF EXISTS ChangeModuleName//
CREATE FUNCTION ChangeModuleName (
pTeacherID INT,
pModuleID INT,
pName VARCHAR(200) CHARSET utf8
) RETURNS int(11)
NO SQL
BEGIN
DECLARE vDisciplineID INT DEFAULT -1;
DECLARE vAlreadySameName BOOL DEFAULT FALSE;
SET vDisciplineID = (SELECT DisciplineID FROM modules WHERE ID = pModuleID LIMIT 1);
IF InternalIsMapLocked(vDisciplineID) THEN
RETURN -1;
END IF;
# Bodging, strange row_count() behaviour in some cases.
# i.e. update 1 module row. In case it's new and old names are same, row_count value may be 0.
SET vAlreadySameName = EXISTS(
SELECT * FROM modules
WHERE modules.ID = pModuleID AND modules.Type = 'regular' AND modules.Name = pName
LIMIT 1
);
IF vAlreadySameName THEN
return 0;
END IF;
UPDATE modules
SET modules.Name = pName
WHERE modules.ID = pModuleID AND modules.Type = 'regular'
LIMIT 1;
RETURN ROW_COUNT() - 1;
END //
DROP FUNCTION IF EXISTS AddModule//
CREATE FUNCTION AddModule (
pTeacherID INT,
pDisciplineID INT,
pName VARCHAR(200) CHARSET utf8
) RETURNS int(11)
NO SQL
BEGIN
DECLARE vOrderNum INT DEFAULT 0;
DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;
IF NOT InternalIsTeacherAuthor(pTeacherID, pDisciplineID) OR InternalIsMapLocked(pDisciplineID) THEN
RETURN -2;
END IF;
# get free orderNum
SET vOrderNum = COALESCE((
SELECT MAX(modules.OrderNum)+1 FROM modules
WHERE modules.DisciplineID = pDisciplineID AND modules.Type = 'regular' LIMIT 1
), 1);
INSERT INTO modules (Name, OrderNum, DisciplineID ) VALUES (pName, vOrderNum, pDisciplineID);
RETURN LAST_INSERT_ID();
END //
DROP FUNCTION IF EXISTS AddModuleExamUnsafe//
CREATE FUNCTION AddModuleExamUnsafe (pDisciplineID INT) RETURNS int(11)
NO SQL
BEGIN
DECLARE vModule, vChecker INT DEFAULT -1;
DECLARE vIsExamExists BOOLEAN;
IF InternalIsMapLocked(pDisciplineID) THEN
RETURN -1;
END IF;
SET vIsExamExists = EXISTS(
SELECT * FROM modules
WHERE modules.DisciplineID = pDisciplineID AND modules.Type = 'exam'
LIMIT 1
);
IF vIsExamExists THEN
RETURN -2;
END IF;
INSERT INTO modules (Name, OrderNum, DisciplineID, Type) VALUES ('Экзамен' , 3141592 , pDisciplineID, 'exam');
SET vModule = LAST_INSERT_ID();
# 3 attempt for pass exam
SELECT AddSubmoduleUnsafe(vModule, 40, '', NULL, 'LandmarkControl') INTO @vTemp;
SELECT AddSubmoduleUnsafe(vModule, 40, '', NULL, 'LandmarkControl') INTO @vTemp;
SELECT AddSubmoduleUnsafe(vModule, 40, '', NULL, 'LandmarkControl') INTO @vTemp;
RETURN vModule;
END //
DROP FUNCTION IF EXISTS AddModuleExam//
CREATE FUNCTION AddModuleExam (
pTeacherID INT,
pDisciplineID INT
) RETURNS int(11)
NO SQL
BEGIN
DECLARE vModule, vChecker INT DEFAULT -1;
DECLARE vIsExamExists BOOLEAN;
IF NOT InternalIsTeacherAuthor(pTeacherID, pDisciplineID) OR
InternalIsMapLocked(pDisciplineID)
THEN
RETURN -1;
END IF;
RETURN AddModuleExamUnsafe(pDisciplineID);
END //
DROP FUNCTION IF EXISTS AddModuleExtra//
CREATE FUNCTION AddModuleExtra (
pTeacherID INT,
pDisciplineID INT
) RETURNS int(11)
NO SQL
BEGIN
DECLARE vChecker, vModule, vType, vGap INT DEFAULT -1;
DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;
IF NOT InternalIsTeacherAuthor(pTeacherID, pDisciplineID)
THEN
RETURN -1;
END IF;
# try to find existing extra module
SELECT modules.ID INTO vChecker
FROM modules
WHERE modules.DisciplineID = pDisciplineID AND modules.Type = 'extra'
LIMIT 1;
IF vChecker > 0 THEN
RETURN -2;
END IF;
# add extra module
INSERT INTO modules
(Name, OrderNum, DisciplineID, Type)
VALUES ('Добор баллов' , 2900666 , pDisciplineID, 'extra');
# get discipline exam type
SELECT modules.ID, disciplines.ExamType INTO vModule, vType
FROM modules
INNER JOIN disciplines ON disciplines.ID = modules.DisciplineID
WHERE modules.DisciplineID = pDisciplineID AND
modules.Type = 'extra'
LIMIT 1;
IF vModule <= 0 THEN
RETURN -1;
END IF;
# 1 extra attempt for exam and 2 for credit
SET vGap = -1;
IF vType = 1 THEN # exam
SET vGap = 7;
END IF;
IF vType = 2 OR vType = 3 THEN # credit, grading_credit
SET vGap = 29;
SET vChecker = AddSubmodule(pTeacherID, vModule, vGap, '', NULL, 'LandmarkControl');
END IF;
SET vChecker = AddSubmodule(pTeacherID, vModule, vGap, '', NULL, 'LandmarkControl');
RETURN vModule;
END //
DROP FUNCTION IF EXISTS DeleteModule//
CREATE FUNCTION DeleteModule (
pTeacherID INT,
pModuleID INT
) RETURNS int(11)
NO SQL
BEGIN
DECLARE vDisciplineID INT DEFAULT -1;
# get discipline ID
SET vDisciplineID = (
SELECT disciplines.ID FROM modules
INNER JOIN disciplines ON modules.DisciplineID = disciplines.ID AND disciplines.AuthorID = pTeacherID
WHERE modules.ID = pModuleID LIMIT 1
);
# check rights
IF NOT InternalIsTeacherAuthor(pTeacherID, vDisciplineID) OR
InternalIsMapLocked(vDisciplineID)
THEN
RETURN -1;
END IF;
DELETE FROM submodules
WHERE submodules.ModuleID = pModuleID;
DELETE FROM modules
WHERE modules.ID = pModuleID;
# restore continuous ordering
SET @counter = 0;
UPDATE modules
SET modules.OrderNum = (@counter := @counter + 1)
WHERE modules.DisciplineID = vDisciplineID AND
modules.Type = 'regular'
ORDER BY modules.OrderNum ASC;
RETURN 0;
END //
DROP FUNCTION IF EXISTS DeleteModuleExam//
CREATE FUNCTION DeleteModuleExam (
pTeacherID INT,
pDisciplineID INT
) RETURNS int(11)
NO SQL
BEGIN
DECLARE vExamModuleID INT DEFAULT -1;
IF NOT InternalIsTeacherAuthor(pTeacherID, pDisciplineID) OR
InternalIsMapLocked(pDisciplineID)
THEN
RETURN -1;
END IF;
# get exam module ID
SELECT modules.ID INTO vExamModuleID
FROM modules
WHERE modules.Type = 'exam' AND
modules.DisciplineID = pDisciplineID
LIMIT 1;
IF vExamModuleID <= 0 THEN
RETURN -1;
END IF;
DELETE FROM submodules
WHERE vExamModuleID = submodules.ModuleID;
DELETE FROM modules
WHERE vExamModuleID = modules.ID
LIMIT 1;
RETURN 0;
END //
DROP FUNCTION IF EXISTS SwapModuleOrder//
CREATE FUNCTION SwapModuleOrder (
pTeacherID INT,
pModuleID1 INT,
pModuleID2 INT
) RETURNS int(11)
NO SQL
BEGIN
DECLARE vChecker, vOrder1, vOrder2,
vDisciplineID1, vDisciplineID2 INT DEFAULT -1;
# get disciplineID and orderNum for 1st module(pModuleID1)
SELECT modules.OrderNum,
modules.DisciplineID
INTO vOrder1, vDisciplineID1
FROM modules
INNER JOIN disciplines ON disciplines.ID = modules.DisciplineID
WHERE disciplines.AuthorID = pTeacherID AND
modules.ID = pModuleID1 AND
modules.Type = 'regular'
LIMIT 1;
# get disciplineID and orderNum for 2st module(pModuleID2)
SELECT modules.OrderNum,
modules.DisciplineID
INTO vOrder2, vDisciplineID2
FROM modules
INNER JOIN disciplines ON disciplines.ID = modules.DisciplineID
WHERE disciplines.AuthorID = pTeacherID AND
modules.ID = pModuleID2 AND
modules.Type = 'regular'
LIMIT 1;
# check that modules belong to one discipline, check rights
IF vDisciplineID1 != vDisciplineID2 OR vDisciplineID1 <= 0 OR
InternalIsMapLocked(vDisciplineID1) THEN
RETURN -1;
END IF;
# swap
UPDATE modules
SET modules.OrderNum = 271828
WHERE modules.ID = pModuleID1;
UPDATE modules
SET modules.OrderNum = vOrder1
WHERE modules.ID = pModuleID2
LIMIT 1;
UPDATE modules
SET modules.OrderNum = vOrder2
WHERE modules.ID = pModuleID1
LIMIT 1;
RETURN 0;
END //
DROP FUNCTION IF EXISTS AddModuleBonus//
CREATE FUNCTION AddModuleBonus (
pTeacherID INT,
pDisciplineID INT
) RETURNS INT(11)
NO SQL
BEGIN
DECLARE vChecker, vModuleID INT DEFAULT -1;
IF NOT InternalIsTeacherAuthor(pTeacherID, pDisciplineID) OR
InternalIsMapLocked(pDisciplineID)
THEN
RETURN -1;
END IF;
# check existing of bonus module
SELECT modules.ID INTO vChecker
FROM modules
WHERE modules.DisciplineID = pDisciplineID AND
modules.Type = 'bonus';
IF vChecker > 0 THEN
RETURN -2;
END IF;
INSERT INTO modules
(Name, OrderNum, DisciplineID, Type)
VALUES ('Бонусные баллы' , 2141692 , pDisciplineID, 'bonus');
SET vModuleID = LAST_INSERT_ID();
SET vChecker = AddSubmodule(pTeacherID, vModuleID, 10, '', NULL, 'LandmarkControl');
RETURN 0;
END //
DROP FUNCTION IF EXISTS GetBonusModule//
CREATE FUNCTION GetBonusModule (pDisciplineID INT) RETURNS int(11)
READS SQL DATA
RETURN (
SELECT modules.ID FROM modules
WHERE modules.DisciplineID = pDisciplineID AND modules.Type = 'bonus'
LIMIT 1
)//
DROP FUNCTION IF EXISTS DeleteModuleBonus//
CREATE FUNCTION DeleteModuleBonus (
pTeacherID INT,
pDisciplineID INT
) RETURNS int(11)
NO SQL
BEGIN
DECLARE vBonusModuleID INT DEFAULT -1;
IF NOT InternalIsTeacherAuthor(pTeacherID, pDisciplineID) OR
InternalIsMapLocked(pDisciplineID)
THEN
RETURN -1;
END IF;
IF GetBonusModule(pDisciplineID) <= 0 THEN
RETURN -1;
END IF;
DELETE FROM submodules
WHERE vBonusModuleID = submodules.ModuleID;
DELETE FROM modules
WHERE vBonusModuleID = modules.ID
LIMIT 1;
RETURN 0;
END //
# -------------------------------------------------------------------------------------------
# Label: submodules
# Label: roadmap
# -------------------------------------------------------------------------------------------
DROP FUNCTION IF EXISTS ChangeSubmoduleMaxAndControl//
CREATE FUNCTION ChangeSubmoduleMaxAndControl (
pTeacherID INT,
pSubmoduleID INT,
pMaxRate INT,
pControlType VARCHAR(30) CHARSET utf8
) RETURNS int(11)
NO SQL
BEGIN
DECLARE vChecker, vDisciplineID, vIsLocked, vNewDiscMaxRate, vCurRate INT DEFAULT -1;
# check that discipline and submodule exists and doesn't locked
SELECT disciplines.IsLocked,
disciplines.MaxRate - submodules.MaxRate + pMaxRate
INTO vIsLocked, vNewDiscMaxRate
FROM submodules
INNER JOIN modules ON submodules.ModuleID = modules.ID
INNER JOIN disciplines ON disciplines.ID = modules.DisciplineID
WHERE submodules.ID = pSubmoduleID AND
disciplines.AuthorID = pTeacherID
LIMIT 1;
IF vIsLocked != 0 OR
vNewDiscMaxRate > 100
THEN
RETURN -1;
END IF;
UPDATE submodules
SET submodules.MaxRate = pMaxRate,
submodules.Type = pControlType
WHERE submodules.ID = pSubmoduleID
LIMIT 1;
RETURN ROW_COUNT()-1;
END //
DROP FUNCTION IF EXISTS ChangeSubmoduleName//
CREATE FUNCTION ChangeSubmoduleName (
pTeacherID INT,
pSubmoduleID INT,
pName VARCHAR(200) CHARSET utf8
) RETURNS int(11)
NO SQL
BEGIN
DECLARE vIsLocked INT DEFAULT -1;
SELECT disciplines.IsLocked INTO vIsLocked
FROM view_roadmap
INNER JOIN disciplines ON disciplines.ID = view_roadmap.DisciplineID
WHERE view_roadmap.SubmoduleID = pSubmoduleID AND disciplines.AuthorID = pTeacherID
LIMIT 1;
IF vIsLocked != 0 THEN
RETURN -1;
END IF;
UPDATE submodules
SET submodules.Name = pName
WHERE submodules.ID = pSubmoduleID
LIMIT 1;
RETURN 0;
END //
DROP FUNCTION IF EXISTS ChangeSubmoduleDescription//
CREATE FUNCTION ChangeSubmoduleDescription (
pTeacherID INT,
pSubmoduleID INT,
pDescription VARCHAR(200) CHARSET utf8
) RETURNS int(11)
NO SQL
BEGIN
DECLARE vIsLocked INT DEFAULT -1;
SELECT disciplines.IsLocked INTO vIsLocked
FROM view_roadmap
INNER JOIN disciplines ON disciplines.ID = view_roadmap.DisciplineID
WHERE view_roadmap.SubmoduleID = pSubmoduleID AND disciplines.AuthorID = pTeacherID
LIMIT 1;
IF vIsLocked != 0 THEN
RETURN -1;
END IF;
UPDATE submodules
SET submodules.Description = pDescription
WHERE submodules.ID = pSubmoduleID
LIMIT 1;
RETURN 0;
END //
DROP FUNCTION IF EXISTS DeleteSubmodule//
CREATE FUNCTION DeleteSubmodule (
pTeacherID INT,
pSubmoduleID INT
) RETURNS int(11)
NO SQL
BEGIN
DECLARE vIsLocked, vModuleID INT DEFAULT -1;
DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;
SELECT modules.ID, disciplines.IsLocked
INTO vModuleID, vIsLocked
FROM submodules
INNER JOIN modules ON modules.ID = submodules.ModuleID
INNER JOIN disciplines ON modules.DisciplineID = disciplines.ID
WHERE disciplines.AuthorID = pTeacherID AND
submodules.ID = pSubmoduleID
LIMIT 1;
IF vIsLocked != 0 THEN
RETURN -1;
END IF;
# handler will catch constraint violation
DELETE FROM submodules
WHERE submodules.ID = pSubmoduleID
LIMIT 1;
# restore continuous ordering
SET @counter = 0;
UPDATE submodules
SET submodules.OrderNum = (@counter := @counter + 1)
WHERE submodules.ModuleID = vModuleID
ORDER BY submodules.OrderNum ASC;
RETURN 0;
END //
DROP FUNCTION IF EXISTS AddSubmoduleUnsafe//
CREATE FUNCTION AddSubmoduleUnsafe (
pModuleID INT,
pMaxRate INT,
pName VARCHAR(200) CHARSET utf8,
pDescription VARCHAR(200) CHARSET utf8,
pControlType VARCHAR(30) CHARSET utf8
) RETURNS int(11)
NO SQL
BEGIN
DECLARE vOrderNum, vIsLocked INT DEFAULT -1;
DECLARE vDescription VARCHAR(200) CHARSET utf8 DEFAULT NULL;
# DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;
# check discipline lock
SELECT disciplines.IsLocked INTO vIsLocked
FROM modules
INNER JOIN disciplines ON disciplines.ID = modules.DisciplineID
WHERE modules.ID = pModuleID
LIMIT 1;
IF vIsLocked != 0 THEN
RETURN -2;
END IF;
# get free order
SET vOrderNum = COALESCE((
SELECT MAX(submodules.OrderNum) FROM submodules
WHERE submodules.ModuleID = pModuleID LIMIT 1
), 0) + 1;
SET vDescription = IF(vDescription = '', NULL, pDescription);
INSERT INTO submodules (ModuleID, MaxRate, OrderNum, Name, Description, Type) VALUES
(pModuleID, pMaxRate, vOrderNum, pName, vDescription, pControlType);
RETURN LAST_INSERT_ID();
END //
DROP FUNCTION IF EXISTS AddSubmodule//
CREATE FUNCTION AddSubmodule (
pTeacherID INT,
pModuleID INT,
pMaxRate INT,
pName VARCHAR(200) CHARSET utf8,
pDescription VARCHAR(200) CHARSET utf8,
pControlType VARCHAR(30) CHARSET utf8
) RETURNS int(11)
NO SQL
BEGIN
DECLARE vIsLocked BOOLEAN DEFAULT TRUE;
# DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;
# check author and discipline lock
SELECT NOT disciplines.AuthorID <=> pTeacherID INTO vIsLocked
FROM modules
INNER JOIN disciplines ON disciplines.ID = modules.DisciplineID
WHERE modules.ID = pModuleID
LIMIT 1;
IF vIsLocked THEN
RETURN -2;
END IF;
RETURN AddSubmoduleUnsafe(pModuleID, pMaxRate, pName, pDescription, pControlType);
END //
DROP FUNCTION IF EXISTS SwapSubmoduleOrder//
CREATE FUNCTION SwapSubmoduleOrder (
pTeacherID INT,
pSubmoduleID1 INT,
pSubmoduleID2 INT
) RETURNS int(11)
NO SQL
BEGIN
DECLARE vDisciplineID, vOrder1, vOrder2,
vModule1, vModule2 INT DEFAULT -1;
SELECT submodules.OrderNum,
submodules.ModuleID,
disciplines.ID
INTO vOrder1, vModule1, vDisciplineID
FROM submodules
INNER JOIN modules ON submodules.ModuleID = modules.ID
INNER JOIN disciplines ON disciplines.ID = modules.DisciplineID
WHERE disciplines.AuthorID = pTeacherID AND
submodules.ID = pSubmoduleID1
LIMIT 1;
SELECT submodules.OrderNum,
submodules.ModuleID
INTO vOrder2, vModule2
FROM submodules
INNER JOIN modules ON submodules.ModuleID = modules.ID
INNER JOIN disciplines ON disciplines.ID = modules.DisciplineID
WHERE disciplines.AuthorID = pTeacherID AND
submodules.ID = pSubmoduleID2
LIMIT 1;
# check, that modules exists and belong to one discipline
IF vModule1 <= 0 OR vModule1 != vModule2 OR
InternalIsMapLocked(vDisciplineID)
THEN
RETURN -1;
END IF;
# swap
UPDATE submodules
SET submodules.OrderNum = 271828
WHERE submodules.ID = pSubmoduleID1
LIMIT 1;
UPDATE submodules
SET submodules.OrderNum = vOrder1
WHERE submodules.ID = pSubmoduleID2
LIMIT 1;
UPDATE submodules
SET submodules.OrderNum = vOrder2
WHERE submodules.ID = pSubmoduleID1
LIMIT 1;
RETURN 0;
END //
# -------------------------------------------------------------------------------------------
# Label: rating
# -------------------------------------------------------------------------------------------
-- get id of student's last exam with rate
-- first exam submodule, if don't rated yet
DROP FUNCTION IF EXISTS GetExamRateID//
CREATE FUNCTION GetExamRateID (
pRecordBook INT,
pDisciplineID INT
) RETURNS int(11)
READS SQL DATA
RETURN COALESCE((
SELECT view_roadmap.SubmoduleID FROM view_roadmap
LEFT JOIN rating_table AS rt on rt.SubmoduleID = view_roadmap.SubmoduleID AND rt.RecordBookID = pRecordBook
WHERE view_roadmap.DisciplineID = pDisciplineID AND view_roadmap.ModuleType = 'exam'
ORDER BY (rt.Rate IS NOT NULL) DESC, view_roadmap.SubmoduleOrderNum DESC
LIMIT 1
), -1)//
# Вычисление максимального балла для submodule
DROP FUNCTION IF EXISTS CalculateMaxRateForExtra//
CREATE FUNCTION CalculateMaxRateForExtra (
pDisciplineID INT,
pRecordBookID INT
) RETURNS int(11)
READS SQL DATA
BEGIN
DECLARE vExamType enum('exam', 'credit', 'grading_credit') DEFAULT NULL;
DECLARE vDiscID, vLim, vResult INT DEFAULT 0;
# get disc type
SET vExamType = GetDisciplineProperty(pDisciplineID, 'type');
# submodule doesn't exists
IF vExamType IS NULL OR vExamType <= 0 THEN
RETURN -1;
END IF;
SET vLim = IF(vExamType = 'exam', 38, 60);
SELECT SUM(IF(view_roadmap.ModuleType = 'regular', rating_table.Rate, 0)) INTO vResult
FROM view_roadmap
LEFT JOIN rating_table ON rating_table.RecordBookID = pRecordBookID AND
rating_table.SubmoduleID = view_roadmap.SubmoduleID
WHERE view_roadmap.DisciplineID = pDisciplineID
LIMIT 1;
RETURN vLim - vResult;
END //
DROP FUNCTION IF EXISTS SetStudentRate//
DROP FUNCTION IF EXISTS Discipline_SetRate//
CREATE FUNCTION Discipline_SetRate (
pTeacherID INT,
pRecordBookID INT,
pSubmoduleID INT,
pRate INT
) RETURNS int(11)
NO SQL
BEGIN
DECLARE vDisciplineID, vMaxRate, vModuleType INT DEFAULT -1;
DECLARE vIsOver, vIsLocked, vIsUsed BOOLEAN DEFAULT FALSE;
DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;
IF pRate < 0 THEN
INSERT INTO logs_rating (RecordBookID, SubmoduleID, TeacherID, Rate, Action ) VALUES
(pRecordBookID, pSubmoduleID, pTeacherID, pRate, 'delete');
# TODO: extract method log rate
DELETE FROM rating_table
WHERE rating_table.RecordBookID = pRecordBookID AND
rating_table.SubmoduleID = pSubmoduleID
LIMIT 1;
RETURN 0;
END IF;
SET vIsOver = TRUE;
SELECT disciplines.ID,
disciplines.IsLocked,
disciplines.Milestone,
submodules.IsUsed,
submodules.maxRate,
modules.Type
INTO vDisciplineID, vIsLocked, vIsOver, vIsUsed, vMaxRate, vModuleType
FROM submodules
INNER JOIN modules ON submodules.ModuleID = modules.ID
INNER JOIN disciplines ON modules.DisciplineID = disciplines.ID
WHERE submodules.ID = pSubmoduleID
LIMIT 1;
# correct max rate for extra module
IF vModuleType = 4 THEN # 4 - extra
SET vMaxRate = CalculateMaxRateForExtra(vDisciplineID, pRecordBookID);
END IF;
# 1) check rights
IF NOT InternalIsStudentAttached(pRecordBookID, vDisciplineID) OR
NOT InternalIsTeacherBound(pTeacherID, vDisciplineID) THEN
RETURN 1;
END IF;
# 2) check, you can't rate regular and bonus after milestone
IF (vIsOver AND (vModuleType = 1 OR vModuleType = 3)) THEN # 1 - regular, 3 - bonus
RETURN 2;
END IF;
# 3) check, max rate exceeding
IF pRate > vMaxRate THEN
RETURN 3;
END IF;
# add rate, or update old
SET @isUpdated = FALSE;
INSERT INTO rating_table (RecordBookID, TeacherID, SubmoduleID, Rate, Date)
VALUES (pRecordBookID, pTeacherID, pSubmoduleID, pRate, CURDATE() )
ON DUPLICATE KEY UPDATE
rating_table.TeacherID = (@isUpdated := pTeacherID),
rating_table.Rate = pRate,
rating_table.Date = CURDATE();
# log rate
INSERT INTO logs_rating (RecordBookID, SubmoduleID, TeacherID, Rate, Action )
VALUES (pRecordBookID, pSubmoduleID, pTeacherID, pRate, IF(@isUpdated, 'change', 'add') );
# lock discipline for structure editing
IF NOT vIsLocked THEN
UPDATE disciplines
SET disciplines.IsLocked = TRUE
WHERE disciplines.ID = vDisciplineID
LIMIT 1;
END IF;
# add submodule to max rate counting (see triggers)
IF NOT vIsUsed THEN
UPDATE submodules
SET submodules.IsUsed = TRUE
WHERE submodules.ID = pSubmoduleID
LIMIT 1;
END IF;
RETURN 0;
END //
# -------------------------------------------------------------------------------------------
# Label: requests
# -------------------------------------------------------------------------------------------
DROP FUNCTION IF EXISTS SetRequestStatus//
CREATE FUNCTION SetRequestStatus (
pRequestID INT,
pStatus enum('opened','processed','closed')
) RETURNS int(11)
NO SQL
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;
UPDATE requests
SET requests.Status = pStatus
WHERE requests.ID = pRequestID
LIMIT 1;
RETURN ROW_COUNT()-1;
END//
-- todo: does it work? HasImage field not found
DROP FUNCTION IF EXISTS CreateRequest//
CREATE FUNCTION CreateRequest (
pAccountID INT,
pTitle VARCHAR(50) CHARSET utf8,
pDescription TEXT CHARSET utf8,
pImage BOOLEAN
) RETURNS int(11)
NO SQL
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;
INSERT INTO requests (AccountID, Title, Description, Status, HasImage)
VALUES (pAccountID, pTitle, pDescription, 'opened', pImage);
RETURN LAST_INSERT_ID();
END//
DROP FUNCTION IF EXISTS UpdateRequest//
CREATE FUNCTION UpdateRequest (
pRequestID INT,
pTitle VARCHAR(50) CHARSET utf8,
pDescription TEXT CHARSET utf8
) RETURNS int(11)
NO SQL
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;
UPDATE requests
SET requests.Description = pDescription,
requests.Title = pTitle,
requests.Date = NOW()
WHERE requests.ID = pRequestID AND
requests.Description IS NULL AND
requests.Title IS NULL
LIMIT 1;
RETURN ROW_COUNT()-1;
END//
# return StudentID or TeacherID depending on UserRoleID
DROP FUNCTION IF EXISTS GetUserStudentOrTeacherID//
CREATE FUNCTION GetUserStudentOrTeacherID (
pAccountID INT(11),
pUserRoleID INT(11)
) RETURNS INT(11)
READS SQL DATA
BEGIN
DECLARE vID INT DEFAULT -1;
IF pUserRoleID = GetUserRole('student') THEN
SELECT students.ID
INTO vID
FROM students
WHERE students.AccountID = pAccountID
LIMIT 1;
ELSE
SELECT teachers.ID
INTO vID
FROM teachers
WHERE teachers.AccountID = pAccountID
LIMIT 1;
END IF;
RETURN vID;
END//
# -------------------------------------------------------------------------------------------
# Label: recovery
# -------------------------------------------------------------------------------------------
DROP FUNCTION IF EXISTS CreateRecoveryToken//
CREATE FUNCTION CreateRecoveryToken (
pAccountOrEMail VARCHAR(255) CHARSET utf8,
pToken VARCHAR(100) CHARSET utf8
) RETURNS VARCHAR(255) charset utf8
NO SQL
BEGIN
DECLARE vAccountID INT DEFAULT -1;
DECLARE vUserFullName TEXT charset utf8;
DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -2;
# get account ID
SELECT accounts.ID INTO vAccountID
FROM accounts
WHERE accounts.EMail = pAccountOrEMail OR
accounts.Login = pAccountOrEMail
LIMIT 1;
IF vAccountID <= 0 THEN
RETURN '';
END IF;
SET vUserFullName = GetUserFullNameByAccountID(vAccountID);
IF vUserFullName IS NULL OR vUserFullName = '' THEN
RETURN '';
END IF;
# transform all unused recovery tokens into used
UPDATE recovery_tokens
SET recovery_tokens.isUsed = 1
WHERE recovery_tokens.isUsed = 0 AND
recovery_tokens.AccountID = vAccountID;
# handle catch constraints violations
INSERT INTO recovery_tokens
( AccountID, Token )
VALUES (vAccountID, pToken);
RETURN vUserFullName;
END//
DROP FUNCTION IF EXISTS GetUserFullNameByAccountID//
CREATE FUNCTION GetUserFullNameByAccountID (
pAccountID INT(11)
) RETURNS VARCHAR(255) charset utf8
READS SQL DATA
BEGIN
DECLARE vUserFullName VARCHAR(255) charset utf8;
SELECT CONCAT(accounts.LastName,' ',accounts.FirstName,' ',accounts.SecondName)
INTO vUserFullName
FROM accounts
WHERE ID = pAccountID
LIMIT 1;
RETURN vUserFullName;
END//
DROP FUNCTION IF EXISTS UseRecoveryToken//
CREATE FUNCTION UseRecoveryToken (
pToken VARCHAR(100) CHARSET utf8
) RETURNS int(11)
NO SQL
BEGIN
DECLARE vChecker INT DEFAULT -1;
# set token used
UPDATE recovery_tokens
SET recovery_tokens.IsUsed = 1
WHERE recovery_tokens.Token = pToken
LIMIT 1;
RETURN ROW_COUNT()-1;
END//
# -------------------------------------------------------------------------------------------
# Label: authorization
# -------------------------------------------------------------------------------------------
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;
INSERT INTO logs_signin (AccountID) VALUES (vAccountID);
RETURN vAccountID;
END //
DROP FUNCTION IF EXISTS SignInByOpenID//
CREATE FUNCTION SignInByOpenID (
pGlobalKey VARCHAR(255) 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 concat('st-', accounts.ExternalID) = pGlobalKey
OR concat('ss-', accounts.INILA) = pGlobalKey
LIMIT 1;
IF vAccountID <= 0 THEN
RETURN -1;
END IF;
INSERT INTO logs_signin (AccountID) VALUES (vAccountID);
RETURN vAccountID;
END //
DROP FUNCTION IF EXISTS SignInTeacherByOpenID//
CREATE FUNCTION SignInTeacherByOpenID (
pGlobalKey VARCHAR(255) 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
JOIN teachers ON accounts.ID = teachers.AccountID
WHERE teachers.INILA = pGlobalKey
LIMIT 1;
IF vAccountID <= 0 THEN
RETURN -1;
END IF;
INSERT INTO logs_signin (AccountID) VALUES (vAccountID);
RETURN vAccountID;
END //
-- returns:
-- -1 : invalid token
-- >0 : accountID
DROP FUNCTION IF EXISTS SignInByToken//
CREATE FUNCTION SignInByToken (pToken char(40) charset ascii) RETURNS int(11) # account id
NO SQL
BEGIN
DECLARE vAccountID INT DEFAULT -1;
SELECT auth_tokens.AccountID INTO vAccountID
FROM auth_tokens
WHERE auth_tokens.Token = pToken
LIMIT 1;
IF vAccountID = -1 THEN
RETURN -1; -- token not found
END IF;
UPDATE auth_tokens
SET Accessed = CURRENT_TIMESTAMP
WHERE auth_tokens.Token = pToken
LIMIT 1;
INSERT INTO logs_signin(AccountID) VALUES (vAccountID);
RETURN vAccountID;
END //
DROP FUNCTION IF EXISTS DeleteAuthToken//
CREATE FUNCTION DeleteAuthToken(pToken char(40) CHARSET ascii) RETURNS int(11)
NO SQL
BEGIN
DELETE FROM auth_tokens
WHERE auth_tokens.Token = pToken;
RETURN ROW_COUNT()-1;
END//
-- returns NULL if failed, token otherwise
DROP FUNCTION IF EXISTS CreateAuthToken//
CREATE FUNCTION CreateAuthToken(
pAccountID int(11),
pDescription varchar(60) CHARACTER SET utf8,
pRightMask int(11)
) RETURNS char(40) charset ascii
NO SQL
BEGIN
DECLARE vTries int(11) DEFAULT 13; -- number of tries to generate unique token
DECLARE vCreated boolean DEFAULT FALSE;
DECLARE vSeed int(11) DEFAULT FLOOR(4294967296 * RAND(CURRENT_TIMESTAMP ^ LAST_INSERT_ID() ^ (pAccountID << 10)));
DECLARE vToken char(40) charset ascii DEFAULT SHA1(vSeed);
WHILE NOT vCreated AND vTries > 0 DO BEGIN
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET vCreated = FALSE;
SET vToken = SHA1(RAND());
SET vTries = vTries - 1;
SET vCreated = TRUE;
INSERT INTO auth_tokens(Token, AccountID, Description, Mask) VALUES (vToken, pAccountID, pDescription, pRightMask);
END; END WHILE;
RETURN IF(vCreated, vToken, NULL);
END//
# -------------------------------------------------------------------------------------------
# Label: roles
# -------------------------------------------------------------------------------------------
DROP FUNCTION IF EXISTS SetRole//
CREATE FUNCTION `SetRole` (
`pID` INT,
`pRole` INT
) RETURNS tinyint(1)
NO SQL
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN 0;
UPDATE `accounts`
SET accounts.UserRoleID = pRole
WHERE accounts.ID = pID;
RETURN 1;
END //
# -------------------------------------------------------------------------------------------
# Label: text marks
# -------------------------------------------------------------------------------------------
DROP FUNCTION IF EXISTS GetTextMark//
CREATE FUNCTION `GetTextMark`(isAfterExam char(1), examType varchar(20), rate INT) RETURNS varchar(100) CHARSET utf8
NO SQL
BEGIN
declare markTextId int;
declare markText varchar(100) charset utf8;
if examType = 'credit' then
if rate < 60 then
set markTextId = 6;#незачет
else
set markTextId = 7;#зачет
end if;
elseif examType = 'gradingcredit' then
if rate >= 85 then
set markTextId = 5;#неуд.
elseif rate >= 70 then
set markTextId = 4;#хорошо
elseif rate >= 60 then
set markTextId = 3;#удовл.
else
set markTextId = 2;#неуд.
end if;
elseif isAfterExam = '0' then
if rate < 38 then
set markTextId = 1;#недопуск
else
return "";
end if;
else
if rate >= 85 then
set markTextId = 5;#неуд.
elseif rate >= 70 then
set markTextId = 4;#хорошо
elseif rate >= 60 then
set markTextId = 3;#удовл.
else
set markTextId = 2;#неуд.
end if;
end if;
select name from text_mark where id = markTextId into markText;
return markText;
END//
-- Вспомогательные строковые функции
DROP FUNCTION IF EXISTS levenshtein//
CREATE FUNCTION levenshtein( s1 VARCHAR(255), s2 VARCHAR(255) )
RETURNS INT
DETERMINISTIC
BEGIN
DECLARE s1_len, s2_len, i, j, c, c_temp, cost INT;
DECLARE s1_char CHAR;
-- max strlen=255
DECLARE cv0, cv1 VARBINARY(256);
SET s1_len = CHAR_LENGTH(s1), s2_len = CHAR_LENGTH(s2), cv1 = 0x00, j = 1, i = 1, c = 0;
IF s1 = s2 THEN
RETURN 0;
ELSEIF s1_len = 0 THEN
RETURN s2_len;
ELSEIF s2_len = 0 THEN
RETURN s1_len;
ELSE
WHILE j <= s2_len DO
SET cv1 = CONCAT(cv1, UNHEX(HEX(j))), j = j + 1;
END WHILE;
WHILE i <= s1_len DO
SET s1_char = SUBSTRING(s1, i, 1), c = i, cv0 = UNHEX(HEX(i)), j = 1;
WHILE j <= s2_len DO
SET c = c + 1;
IF s1_char = SUBSTRING(s2, j, 1) THEN
SET cost = 0; ELSE SET cost = 1;
END IF;
SET c_temp = CONV(HEX(SUBSTRING(cv1, j, 1)), 16, 10) + cost;
IF c > c_temp THEN SET c = c_temp; END IF;
SET c_temp = CONV(HEX(SUBSTRING(cv1, j+1, 1)), 16, 10) + 1;
IF c > c_temp THEN
SET c = c_temp;
END IF;
SET cv0 = CONCAT(cv0, UNHEX(HEX(c))), j = j + 1;
END WHILE;
SET cv1 = cv0, i = i + 1;
END WHILE;
END IF;
RETURN c;
END//
DROP FUNCTION IF EXISTS levenshtein_ratio//
CREATE FUNCTION levenshtein_ratio( s1 VARCHAR(255), s2 VARCHAR(255) )
RETURNS INT
DETERMINISTIC
BEGIN
DECLARE s1_len, s2_len, max_len INT;
SET s1_len = LENGTH(s1), s2_len = LENGTH(s2);
IF s1_len > s2_len THEN
SET max_len = s1_len;
ELSE
SET max_len = s2_len;
END IF;
RETURN ROUND((1 - LEVENSHTEIN(s1, s2) / max_len) * 100);
END//
# -------------------------------------------------------------------------------------------
# Label: StudyPlans
# -------------------------------------------------------------------------------------------
DROP FUNCTION IF EXISTS Plan_BindDiscipline//
CREATE FUNCTION Plan_BindDiscipline(
pStudyPlanID INT,
pDisciplineID INT
)
RETURNS INT(11)
NO SQL
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;
INSERT INTO disciplines_study_plans (StudyPlanID, DisciplineID) VALUES (pStudyPlanID, pDisciplineID)
ON DUPLICATE KEY UPDATE # just stub
disciplines_study_plans.ID = LAST_INSERT_ID(disciplines_study_plans.ID);
RETURN 0;
END //
DELIMITER ;