DELIMITER //
DROP FUNCTION IF EXISTS GetGradeID//
DROP FUNCTION IF EXISTS SetCurSemesterID//
DROP FUNCTION IF EXISTS SetHashKey//
drop function if exists InternalIsTeacherBinded//
DROP FUNCTION IF EXISTS GetSemesterID//
# -------------------------------------------------------------------------------------------
# Label: abbreviations
# -------------------------------------------------------------------------------------------
# abbreviation: abbr
# specialization: spec
# department: dep
# -------------------------------------------------------------------------------------------
# Label: internals
# -------------------------------------------------------------------------------------------
# actually check for first scoring, in this case you cannot yet edit discipline
# "SetRate" stored procedure can change isLocked flag
DROP FUNCTION IF EXISTS InternalIsMapLocked//
CREATE FUNCTION `InternalIsMapLocked`
(`pDisciplineID` INT) RETURNS BOOLEAN
NO SQL
BEGIN
RETURN EXISTS(
SELECT * FROM `disciplines`
WHERE disciplines.ID = pDisciplineID AND disciplines.isLocked = 1
);
END //
# check, that student really take this course
DROP FUNCTION IF EXISTS InternalIsStudentAttached//
CREATE FUNCTION `InternalIsStudentAttached`
(`pStudentID` INT, `pDisciplineID` INT) RETURNS BOOLEAN
NO SQL
BEGIN
RETURN EXISTS(
SELECT * FROM `view_disciplines_students`
WHERE view_disciplines_students.StudentID = pStudentID AND
view_disciplines_students.DisciplineID = pDisciplineID AND
(view_disciplines_students.AttachType IS NULL OR
view_disciplines_students.AttachType = 'attach')
);
END //
# check, that teacher teach this course
drop function if exists InternalIsTeacherBounded//
CREATE FUNCTION `InternalIsTeacherBounded`
( `pTeacherID` INT, `pDisciplineID` INT) RETURNS BOOLEAN
NO SQL
BEGIN
RETURN EXISTS (SELECT * FROM `disciplines_teachers`
WHERE disciplines_teachers.TeacherID = pTeacherID AND
disciplines_teachers.DisciplineID = pDisciplineID);
END //
DROP FUNCTION IF EXISTS InternalIsTeacherAuthor//
CREATE FUNCTION `InternalIsTeacherAuthor`
( `pTeacherID` INT, `pDisciplineID` INT) RETURNS BOOLEAN
NO SQL
BEGIN
RETURN EXISTS (SELECT * FROM `disciplines`
WHERE disciplines.ID = pDisciplineID AND disciplines.AuthorID = pTeacherID);
END //
DROP FUNCTION IF EXISTS GetDisciplineMaxRate//
CREATE FUNCTION `GetDisciplineMaxRate`
( `pDisciplineID` INT) RETURNS int(11)
NO SQL
BEGIN
DECLARE vMaxRate INT DEFAULT 0;
# discipline map consist of submodules, we sum all their max rates
SELECT SUM(submodules.MaxRate)
INTO vMaxRate
FROM `submodules`
INNER JOIN `modules`
ON submodules.ModuleID = modules.ID AND (modules.Type = 'regular' OR modules.Type = 'exam')
WHERE modules.DisciplineID = pDisciplineID;
RETURN vMaxRate;
END //
DROP FUNCTION IF EXISTS GetRateForDisc//
CREATE FUNCTION `GetRateForDisc`
( `pStudentID` INT, `pDisciplineID` INT) RETURNS int(11)
NO SQL
BEGIN
DECLARE vRate INT DEFAULT -1;
SELECT SUM(rating_table.Rate)
INTO vRate
FROM `rating_table`
INNER JOIN `submodules` ON rating_table.SubmoduleID = submodules.ID
INNER JOIN `modules` ON submodules.ModuleID = modules.ID AND
modules.DisciplineID = pDisciplineID
WHERE rating_table.StudentID = pStudentID AND
( modules.Type != 'exam' OR
submodules.ID =
( SELECT submodules.ID
FROM `submodules`
INNER JOIN `rating_table` ON rating_table.SubModuleID = submodules.ID
WHERE submodules.ModuleID = modules.ID AND
rating_table.StudentID = pStudentID
ORDER BY submodules.OrderNum DESC
LIMIT 1
)
)
LIMIT 1;
RETURN vRate;
END //
DROP FUNCTION IF EXISTS GetRateForDiscSemester//
CREATE FUNCTION `GetRateForDiscSemester`
( `pStudentID` INT, `pDisciplineID` INT) RETURNS int(11)
NO SQL
BEGIN
DECLARE vRate INT DEFAULT -1;
SELECT SUM(rating_table.Rate)
INTO vRate
FROM `rating_table`
INNER JOIN `submodules` ON rating_table.SubmoduleID = submodules.ID
INNER JOIN `modules` ON submodules.ModuleID = modules.ID
WHERE rating_table.StudentID = pStudentID AND
modules.DisciplineID = pDisciplineID AND
modules.Type = 'regular'
LIMIT 1;
RETURN vRate;
END //
DROP FUNCTION IF EXISTS GetRateForDiscBonus//
CREATE FUNCTION `GetRateForDiscBonus`
( `pStudentID` INT, `pDisciplineID` INT) RETURNS int(11)
NO SQL
BEGIN
DECLARE vRate INT DEFAULT -1;
SELECT SUM(rating_table.Rate)
INTO vRate
FROM `rating_table`
INNER JOIN `submodules` ON rating_table.SubmoduleID = submodules.ID
INNER JOIN `modules` ON submodules.ModuleID = modules.ID
WHERE rating_table.StudentID = pStudentID AND
modules.DisciplineID = pDisciplineID AND
modules.Type = 'bonus'
LIMIT 1;
RETURN vRate;
END //
DROP FUNCTION IF EXISTS GetRateForDiscExam//
CREATE FUNCTION `GetRateForDiscExam`
( `pStudentID` INT, `pDisciplineID` INT) RETURNS int(11)
NO SQL
BEGIN
DECLARE vRate INT DEFAULT -1;
SELECT rating_table.Rate
INTO vRate
FROM `rating_table`
INNER JOIN `submodules` ON rating_table.SubmoduleID = submodules.ID
INNER JOIN `modules` ON submodules.ModuleID = modules.ID
WHERE rating_table.StudentID = pStudentID AND
modules.DisciplineID = pDisciplineID AND
modules.Type = 'exam'
ORDER BY submodules.OrderNum DESC
LIMIT 1;
RETURN vRate;
END //
DROP FUNCTION IF EXISTS GetRateForDiscExamNum//
CREATE FUNCTION `GetRateForDiscExamNum`
( `pStudentID` INT, `pDisciplineID` INT, `pOrderNum` INT
) RETURNS int(11)
NO SQL
BEGIN
DECLARE vRate INT DEFAULT -1;
SELECT rating_table.Rate
INTO vRate
FROM `rating_table`
INNER JOIN `submodules` ON rating_table.SubmoduleID = submodules.ID
INNER JOIN `modules` ON submodules.ModuleID = modules.ID
WHERE rating_table.StudentID = pStudentID AND
modules.DisciplineID = pDisciplineID AND
modules.Type = 'exam' AND
submodules.OrderNum = pOrderNum
LIMIT 1;
RETURN vRate;
END //
DROP FUNCTION IF EXISTS GetRateForDiscExtra//
CREATE FUNCTION `GetRateForDiscExtra`
( `pStudentID` INT, `pDisciplineID` INT, `pOrderNum` INT
) RETURNS int(11)
NO SQL
BEGIN
DECLARE vRate INT DEFAULT -1;
SELECT rating_table.Rate
INTO vRate
FROM `rating_table`
INNER JOIN `submodules` ON rating_table.SubmoduleID = submodules.ID
INNER JOIN `modules` ON submodules.ModuleID = modules.ID
WHERE rating_table.StudentID = pStudentID AND
modules.DisciplineID = pDisciplineID AND
modules.Type = 'extra' AND
submodules.OrderNum = pOrderNum
LIMIT 1;
RETURN vRate;
END //
# check, if any module is created
DROP FUNCTION IF EXISTS InternalIsMapCreated//
CREATE FUNCTION `InternalIsMapCreated`
( `pDisciplineID` INT) RETURNS int(11)
NO SQL
BEGIN
RETURN EXISTS (
SELECT * FROM `view_disciplines_results`
WHERE view_disciplines_results.DisciplineID = pDisciplineID AND
view_disciplines_results.DisciplineRateMax = 100
);
END //
# set notification flag
DROP FUNCTION IF EXISTS InternalNotify//
CREATE FUNCTION `InternalNotify` ( `AccountID` INT
) RETURNS int(11)
NO SQL
BEGIN
UPDATE `accounts`
SET accounts.Notification = 1
WHERE accounts.ID = AccountID
LIMIT 1;
RETURN ROW_COUNT()-1;
END //
# -------------------------------------------------------------------------------------------
# Label: preferences
# Label: magic
# -------------------------------------------------------------------------------------------
DROP FUNCTION IF EXISTS SetSettings//
CREATE FUNCTION `SetSettings`
(`pKey` VARCHAR(50) CHARSET utf8, `pVal` INT, `pValS` VARCHAR(300) CHARSET utf8
) RETURNS int(11)
NO SQL
BEGIN
INSERT INTO `general_settings`
(Val, ValS, Name) VALUES(pVal, pValS, pKey)
ON DUPLICATE KEY UPDATE
general_settings.Val = pVal,
general_settings.ValS = pValS;
RETURN 0;
END//
DROP FUNCTION IF EXISTS SetBitmaskByPagename//
CREATE FUNCTION `SetBitmaskByPagename`
(`pPagename` TEXT CHARSET utf8, `pMask` INT) RETURNS int(11)
NO SQL
BEGIN
INSERT INTO `page_access`
(Pagename, Bitmask) VALUES(pPagename, pMask)
ON DUPLICATE KEY UPDATE
page_access.Bitmask = pMask;
RETURN 0;
END //
DROP FUNCTION IF EXISTS GetBitmaskByPagename//
CREATE FUNCTION `GetBitmaskByPagename` (`pPagename` TEXT CHARSET utf8) RETURNS int(11)
NO SQL
BEGIN
RETURN (SELECT page_access.Bitmask
FROM `page_access`
WHERE page_access.Pagename = pPagename
LIMIT 1);
END //
# -------------------------------------------------------------------------------------------
# Label: semesters
# -------------------------------------------------------------------------------------------
DROP FUNCTION IF EXISTS SetSemesterID//
CREATE FUNCTION `SetSemesterID` (`pSemesterID` INT) RETURNS int(11)
NO SQL
BEGIN
SET @CurrentSemesterID = pSemesterID;
RETURN 0;
END //
# -------------------------------------------------------------------------------------------
# Label: study groups
# -------------------------------------------------------------------------------------------
DROP FUNCTION IF EXISTS CreateStudyGroup//
CREATE FUNCTION `CreateStudyGroup`
( `pGradeID` INT, `pGroupNum` INT,
`pSpecializationID` INT, `pGroupName` VARCHAR(50) CHARSET utf8
) RETURNS int(11)
NO SQL
BEGIN
# check GradeID, SpecID constraints and (GradeID, GroupNum, SpecID) - unique
DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;
# create discipline
INSERT INTO `study_groups`
(GradeID, GroupNum, SpecializationID, Name)
VALUES (pGradeID, pGroupNum, pSpecializationID, pGroupName);
RETURN 0;
END //
# -------------------------------------------------------------------------------------------
# Label: subjects
# -------------------------------------------------------------------------------------------
DROP FUNCTION IF EXISTS CreateSubject//
CREATE FUNCTION `CreateSubject`
( `pFacultyID` INT,
`pSubjectName` VARCHAR(200) CHARSET utf8,
`pSubjectAbbr` VARCHAR(20) CHARSET utf8
) RETURNS int(11)
NO SQL
BEGIN
DECLARE vSubjectID INT DEFAULT -1;
# find same subject
SELECT subjects.ID INTO vSubjectID
FROM `subjects`
WHERE subjects.Name = pSubjectName
LIMIT 1;
IF vSubjectID <= 0 THEN
# create new subject
INSERT INTO `subjects`
(Name, Abbr) VALUES(pSubjectName, pSubjectAbbr);
SET vSubjectID = LAST_INSERT_ID();
END IF;
BEGIN # handler block
DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;
# try to attach subject to faculty
INSERT INTO `subjects_faculties`
(SubjectID, FacultyID)
VALUES (vSubjectID, pFacultyID)
ON DUPLICATE KEY UPDATE # just stub
subjects_faculties.FacultyID = subjects_faculties.FacultyID;
END;
RETURN 0;
END //
# -------------------------------------------------------------------------------------------
# Label: accounts
# -------------------------------------------------------------------------------------------
# TODO: rename
DROP FUNCTION IF EXISTS GetAccCountByCode//
CREATE FUNCTION `GetAccCountByCode` (`pCode` VARCHAR(40) CHARSET utf8) RETURNS int(11)
NO SQL
BEGIN
RETURN EXISTS(SELECT * FROM `accounts` WHERE accounts.ActivationCode = pCode);
END //
DROP FUNCTION IF EXISTS GetAccCountByMail //
CREATE FUNCTION `GetAccCountByMail` (`pEMail` VARCHAR(50) CHARSET utf8) RETURNS int(11)
NO SQL
BEGIN
RETURN EXISTS(SELECT * FROM `accounts` WHERE accounts.EMail = pEMail);
END //
DROP FUNCTION IF EXISTS GetAccCountByLogin//
CREATE FUNCTION `GetAccCountByLogin` (`pLogin` VARCHAR(50) CHARSET utf8) RETURNS int(11)
NO SQL
BEGIN
RETURN EXISTS(SELECT * FROM `accounts` WHERE accounts.Login = pLogin);
END //
DROP FUNCTION IF EXISTS ActivateAccount//
CREATE FUNCTION `ActivateAccount`
( `pCode` VARCHAR(40) CHARSET utf8,
`pLogin` VARCHAR(50) CHARSET utf8,
`pEMail` VARCHAR(50) CHARSET utf8,
`pPassword` VARCHAR(255) CHARSET utf8
) RETURNS int(11)
NO SQL
BEGIN
# check for matching with existing accounts (note: Login & E-Mail are unique)
DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;
# activate account
UPDATE `accounts`
SET accounts.Login = pLogin,
accounts.Password = pPassword,
accounts.EMail = pEMail,
accounts.ActivationCode = NULL
WHERE accounts.ActivationCode = pCode AND
(@vAccountID = accounts.ID) > 0 # save accountID
LIMIT 1;
IF (ROW_COUNT() = 0) THEN
RETURN -2; # account with this Code not found
END IF;
RETURN @vAccountID;
END //
DROP FUNCTION IF EXISTS ChangePassword//
CREATE FUNCTION `ChangePassword`
( `pUserID` INT, `pPassword` VARCHAR(255) CHARSET utf8
) RETURNS int(11)
NO SQL
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;
# set new password
UPDATE `accounts`
SET accounts.Password = pPassword
WHERE accounts.ID = pUserID
LIMIT 1;
RETURN ROW_COUNT()-1; # -1 if account doesn't exists, otherwise 0
END //
DROP FUNCTION IF EXISTS ChangeLogin//
CREATE FUNCTION `ChangeLogin`
( `pUserID` INT, `pLogin` VARCHAR(50) CHARSET utf8
) RETURNS int(11)
NO SQL
BEGIN
# check set login: login - unique
DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;
# set new login
UPDATE `accounts`
SET accounts.Login = pLogin
WHERE accounts.ID = pUserID
LIMIT 1;
RETURN ROW_COUNT()-1; # -1 if account doesn't exists, otherwise 0
END //
DROP FUNCTION IF EXISTS ChangeMail//
CREATE FUNCTION `ChangeMail`
(`pUserID` INT, `pEMail` VARCHAR(50) CHARSET utf8
) RETURNS int(11)
NO SQL
BEGIN
# check set login: login - unique
DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;
# set new e-mail
UPDATE `accounts`
SET accounts.EMail = pEMail
WHERE accounts.ID = pUserID
LIMIT 1;
RETURN ROW_COUNT()-1; # -1 if account doesn't exists, otherwise 0
END //
DROP FUNCTION IF EXISTS SignIn//
CREATE FUNCTION `SignIn`
( `pLoginOrMail` VARCHAR(255) CHARSET utf8,
`pPassword` VARCHAR(64) CHARSET utf8
) RETURNS int(11)
NO SQL
BEGIN
DECLARE vAccountID INT DEFAULT -1;
#check account existence
SELECT accounts.ID
INTO vAccountID
FROM `accounts`
WHERE accounts.Password = pPassword AND
(accounts.Login = pLoginOrMail OR accounts.EMail = pLoginOrMail)
LIMIT 1;
IF vAccountID <= 0 THEN
RETURN -1;
END IF;
# logging
INSERT INTO `logs_signin`
(AccountID) VALUES (vAccountID);
RETURN vAccountID;
END //
# -------------------------------------------------------------------------------------------
# Label: teachers
# -------------------------------------------------------------------------------------------
DROP FUNCTION IF EXISTS ChangeTeacherInfo//
CREATE FUNCTION `ChangeTeacherInfo`
( `pTeacherID` INT,
`pLastName` VARCHAR(30) CHARSET utf8,
`pFirstName` VARCHAR(30) CHARSET utf8,
`pSecondName` VARCHAR(30) CHARSET utf8,
`pJobPositionID` INT,
`pDepartmentID` INT
) RETURNS int(11)
NO SQL
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;
# set new info
UPDATE `teachers`
SET teachers.LastName = pLastName,
teachers.FirstName = pFirstName,
teachers.SecondName = pSecondName,
teachers.JobPositionID = pJobPositionID,
teachers.DepartmentID = pDepartmentID
WHERE teachers.ID = pTeacherID
LIMIT 1;
RETURN ROW_COUNT()-1; # -1 if teacher doesn't exists, otherwise 0
END //
DROP FUNCTION IF EXISTS CreateTeacher//
CREATE FUNCTION `CreateTeacher`
( `pLastName` VARCHAR(30) CHARSET utf8,
`pFirstName` VARCHAR(30) CHARSET utf8,
`pSecondName` VARCHAR(30) CHARSET utf8,
`pJobPositionID` INT,
`pDepartmentID` INT,
`pActivationCode` VARCHAR(40) CHARSET utf8
) RETURNS int(11)
NO SQL
BEGIN
DECLARE vAccountID INT DEFAULT -1;
DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;
# user role 2 - common teacher
# add new account
INSERT INTO `accounts`
(Login , Password , EMail, UserRoleID, ActivationCode )
VALUES ( NULL, NULL, NULL, 2, pActivationCode);
SET vAccountID = LAST_INSERT_ID();
# add new teacher
INSERT INTO `teachers`
(AccountID, LastName, FirstName, SecondName, JobPositionID, DepartmentID)
VALUES (vAccountID, pLastName, pFirstName, pSecondName, pJobPositionID, pDepartmentID);
RETURN ROW_COUNT()-1;
END //
DROP FUNCTION IF EXISTS CreateTeacherByDepName//
CREATE FUNCTION `CreateTeacherByDepName`
( `pLastName` VARCHAR(30) CHARSET utf8,
`pFirstName` VARCHAR(30) CHARSET utf8,
`pSecondName` VARCHAR(30) CHARSET utf8,
`pDepartmentName` VARCHAR(200) CHARSET utf8,
`pActivationCode` VARCHAR(40) CHARSET utf8
) RETURNS int(11)
NO SQL
BEGIN
DECLARE vAccountID, vChecker, vRoleID, vDepID INT DEFAULT -1;
DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;
# try to find a department with pDepartmentName
SELECT departments.ID
INTO vDepID
FROM `departments`
WHERE departments.Name = pDepartmentName OR
(pDepartmentName = '' AND departments.Name IS NULL)
# or stub department record
LIMIT 1;
IF vDepID <= 0 THEN
RETURN -1;
END IF;
INSERT INTO `accounts`
(Login , Password , EMail, UserRoleID, ActivationCode )
VALUES ( NULL, NULL, NULL, 2, pActivationCode);
SET vAccountID = LAST_INSERT_ID();
INSERT INTO `teachers`
(AccountID, LastName, FirstName, SecondName, JobPositionID, DepartmentID)
VALUES (vAccountID, pLastName, pFirstName, pSecondName, 12, vDepID);
RETURN 0;
END //
# -------------------------------------------------------------------------------------------
# Label: students
# -------------------------------------------------------------------------------------------
DROP FUNCTION IF EXISTS CreateStudent//
CREATE FUNCTION `CreateStudent`
( `pLastName` VARCHAR(30) CHARSET utf8,
`pFirstName` VARCHAR(30) CHARSET utf8,
`pSecondName` VARCHAR(30) CHARSET utf8,
`pGradeID` INT, `pGroupNum` INT, `pFacultyID` INT,
# TODO: delete pGradeID
`pActivationCode` VARCHAR(40) CHARSET utf8
) RETURNS int(11)
NO SQL
BEGIN
DECLARE vAccountID, vGroupID INT DEFAULT -1;
DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;
# find group
SELECT view_groups.GroupID INTO vGroupID
FROM `view_groups`
WHERE view_groups.FacultyID = pFacultyID AND
view_groups.GradeID = pGradeID AND
view_groups.GroupNum = pGroupNum
LIMIT 1;
IF vGroupID <= 0 THEN
RETURN -1;
END IF;
# create new account
INSERT INTO `accounts`
(Login , Password , EMail, UserRoleID, ActivationCode )
VALUES ( NULL, NULL, NULL, 1, pActivationCode);
SET vAccountID = LAST_INSERT_ID();
# create student
INSERT INTO `students`
(GroupID, AccountID, LastName, FirstName, SecondName)
VALUES (vGroupID, vAccountID, pLastName, pFirstName, pSecondName);
RETURN 0;
END //
DROP FUNCTION IF EXISTS CreateStudentEx//
CREATE FUNCTION `CreateStudentEx`
( `pLastName` VARCHAR(30) CHARSET utf8,
`pFirstName` VARCHAR(30) CHARSET utf8,
`pSecondName` VARCHAR(30) CHARSET utf8,
`pGradeNum` INT, `pGroupNum` INT,
`pDegree` VARCHAR(20) CHARSET utf8,
`pSpecName` VARCHAR(50) CHARSET utf8,
`pFacultyID` INT,
`pActivationCode` VARCHAR(40) CHARSET utf8
) RETURNS int(11)
NO SQL
BEGIN
DECLARE vAccountID, vGradeID, vSpecID, vGroupID INT DEFAULT -1;
DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;
# try to find grade
SELECT grades.ID INTO vGradeID
FROM `grades`
WHERE grades.Num = pGradeNum AND grades.Degree = pDegree
LIMIT 1;
# such grade doesn't exist
IF vGradeID <= 0 THEN
# insert new grade with pGradeNum and pDegree
INSERT INTO `grades`
(Num, Degree) VALUES (pGradeNum, pDegree);
SET vGradeID = LAST_INSERT_ID();
END IF;
# try to find group
SELECT view_groups.GroupID INTO vGroupID
FROM `view_groups`
WHERE view_groups.FacultyID = pFacultyID AND
view_groups.GroupNum = pGroupNum AND
view_groups.GradeID = vGradeID
LIMIT 1;
# group not found
IF vGroupID <= 0 THEN
# try to find specialization
SELECT specializations.ID INTO vSpecID
FROM `specializations`
WHERE (specializations.Name = pSpecName OR
(pSpecName = '' AND specializations.Name IS NULL)) AND
specializations.FacultyID = pFacultyID
LIMIT 1;
# specialization not found
IF vSpecID <= 0 THEN
# create new specialization
INSERT INTO `specializations`
(Name, Abbr, FacultyID)
VALUES (pSpecName, NULL, pFacultyID);
SET vSpecID = LAST_INSERT_ID();
END IF;
# create new group
INSERT INTO `study_groups`
(GradeID, GroupNum, SpecializationID)
VALUES (vGradeID, pGroupNum, vSpecID);
SET vGroupID = LAST_INSERT_ID();
END IF;
# TODO: user roles
# create account
INSERT INTO `accounts`
(Login, Password , EMail, UserRoleID, ActivationCode )
VALUES ( NULL, NULL, NULL, 1, pActivationCode);
SET vAccountID = LAST_INSERT_ID();
# create student
INSERT INTO `students`
(GroupID, AccountID, LastName, FirstName, SecondName)
VALUES (vGroupID, vAccountID, pLastName, pFirstName, pSecondName);
RETURN ROW_COUNT()-1;
END //
# -------------------------------------------------------------------------------------------
# Label: disciplines
# -------------------------------------------------------------------------------------------
DROP FUNCTION IF EXISTS OrderModuleTypesForSession//
CREATE FUNCTION `OrderModuleTypesForSession`
(`pModuleType` INT ) RETURNS INT(3)
NO SQL
BEGIN
DECLARE vRes INT DEFAULT 0;
CASE pModuleType
WHEN 'extra' THEN SET vRes = 1;
WHEN 'exam' THEN SET vRes = 2;
WHEN 'bonus' THEN SET vRes = 3;
ELSE SET vRes = 1;
END CASE;
RETURN vRes;
END //
DROP FUNCTION IF EXISTS AddDiscipline//
CREATE FUNCTION `AddDiscipline`
( `pTeacherID` INT, `pGradeID` INT, `pSubjectID` INT,
`pExamType` VARCHAR(30) CHARSET utf8,
`pLectureCount` INT, `pPracticeCount` INT, `pLabCount` INT,
`pFacultyID` INT
) RETURNS int(11)
NO SQL
BEGIN
DECLARE vChecker, vDisciplineID INT DEFAULT -1;
DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;
INSERT INTO `disciplines`
(AuthorID, GradeID, SubjectID, ExamType, LectureCount, PracticeCount,LabCount, SemesterID,FacultyID)
VALUES ( pTeacherID, pGradeID, pSubjectID, pExamType, pLectureCount, pPracticeCount, pLabCount,
@CurrentSemesterID, pFacultyID);
SET vDisciplineID = LAST_INSERT_ID();
INSERT INTO `disciplines_teachers`
(DisciplineID,TeacherID)
VALUES (vDisciplineID, pTeacherID);
IF pExamType = 'exam' THEN
SET vChecker = AddModuleExam(pTeacherID, vDisciplineID);
END IF;
SET vChecker = AddModuleExtra(pTeacherID, vDisciplineID);
RETURN vDisciplineID;
END //
DROP FUNCTION IF EXISTS ChangeDisciplineSubject//
CREATE FUNCTION `ChangeDisciplineSubject`
(`pTeacherID` INT, `pDisciplineID` INT, `pSubjectID` INT
) RETURNS int(11)
NO SQL
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;
IF NOT InternalIsTeacherAuthor(pTeacherID, pDisciplineID) OR
InternalIsMapLocked(pDisciplineID)
THEN
RETURN -1;
END IF;
UPDATE `disciplines`
SET disciplines.SubjectID = pSubjectID
WHERE disciplines.ID = pDisciplineID
LIMIT 1;
RETURN ROW_COUNT()-1;
END //
DROP FUNCTION IF EXISTS ChangeDisciplineGrade//
CREATE FUNCTION `ChangeDisciplineGrade`
(`pTeacherID` INT, `pDisciplineID` INT, `pGradeID` INT
) RETURNS int(11)
NO SQL
BEGIN
DECLARE vCurGradeID INT DEFAULT -1;
DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;
IF InternalIsMapLocked(pDisciplineID) OR
NOT InternalIsTeacherAuthor(pTeacherID, pDisciplineID)
THEN
RETURN -1;
END IF;
# get current grade
SELECT disciplines.GradeID INTO vCurGradeID
FROM `disciplines`
WHERE disciplines.ID = pDisciplineID
LIMIT 1;
IF vCurGradeID = pGradeID THEN
RETURN 0;
END IF;
# delete all students
DELETE FROM `disciplines_groups`
WHERE disciplines_groups.DisciplineID = pDisciplineID;
DELETE FROM `disciplines_students`
WHERE disciplines_students.DisciplineID = pDisciplineID;
# set grade
UPDATE `disciplines`
SET disciplines.GradeID = pGradeID
WHERE disciplines.ID = pDisciplineID
LIMIT 1;
RETURN ROW_COUNT()-1;
END //
DROP FUNCTION IF EXISTS ChangeDisciplineControl//
CREATE FUNCTION `ChangeDisciplineControl`
( `pTeacherID` INT, `pDisciplineID` INT,
`pExamType` VARCHAR(30) CHARSET utf8
) RETURNS int(11)
NO SQL
BEGIN
DECLARE vOldExamType, vChecker, vExtraMax, vExtraID INT DEFAULT -1;
DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;
IF InternalIsMapLocked(pDisciplineID) OR
NOT InternalIsTeacherAuthor(pTeacherID, pDisciplineID)
THEN
RETURN -1;
END IF;
# get exam type and extra module ID
SELECT disciplines.ExamType, modules.ID INTO vOldExamType, vExtraID
FROM `disciplines`
INNER JOIN `modules` ON modules.Type = 'extra' AND
modules.DisciplineID = pDisciplineID
WHERE disciplines.ID = pDisciplineID
LIMIT 1;
IF vExtraID <= 0 THEN
RETURN -1;
END IF;
# check changed change exam type
IF (vOldExamType = pExamType) THEN
RETURN 0;
END IF;
IF pExamType = 'exam' THEN
SET vExtraMax = 7;
SET vChecker = GetDisciplineMaxRate(pDisciplineID);
IF vChecker >= 61 THEN
RETURN 1;
END IF;
SET vChecker = AddModuleExam(pTeacherID, pDisciplineID);
# delete extra submodules
DELETE FROM `submodules`
WHERE submodules.OrderNum > 1 AND submodules.ModuleID = vExtraID;
ELSE
SET vExtraMax = 29;
SET vChecker = DeleteModuleExam(pTeacherID, pDisciplineID);
SET vChecker = AddSubmodule(pTeacherID, vExtraID, vExtraMax, '', NULL, 'LandmarkControl');
END IF;
UPDATE `disciplines`
SET disciplines.ExamType = pExamType
WHERE disciplines.ID = pDisciplineID
LIMIT 1;
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 BindGroup//
CREATE FUNCTION `BindGroup`
(`pTeacherID` INT, `pDisciplineID` INT, `pGroupID` INT
) RETURNS int(11)
NO SQL
BEGIN
DECLARE vChecker INT DEFAULT -1;
DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;
# 1. check if AccessedTeacher is author
IF NOT InternalIsTeacherAuthor(pTeacherID, pDisciplineID) OR
InternalIsMapLocked(pDisciplineID)
THEN
RETURN -1;
END IF;
# 2. check if study group is bound to discipline
# TODO: extract method
SELECT disciplines_groups.ID INTO vChecker
FROM `disciplines_groups`
WHERE disciplines_groups.GroupID = pGroupID AND
disciplines_groups.DisciplineID = pDisciplineID
LIMIT 1;
IF vChecker > 0 THEN
RETURN 1;
END IF;
# 3. delete students of this group which were bound to discipline before
DELETE FROM `disciplines_students`
WHERE disciplines_students.DisciplineID = pDisciplineID AND
disciplines_students.StudentID IN
(SELECT students.ID
FROM `students`
WHERE students.GroupID = pGroupID
);
# 4. bind whole group
INSERT INTO `disciplines_groups`
(DisciplineID, GroupID)
VALUES (pDisciplineID, pGroupID );
RETURN 0;
END //
DROP FUNCTION IF EXISTS BindStudent//
CREATE FUNCTION `BindStudent`
( `pTeacherID` INT, `pDisciplineID` INT, `pStudentID` INT
) RETURNS int(11)
NO SQL
BEGIN
DECLARE vInGroup, vChecker, vGroupID, vTemp INT DEFAULT -1;
DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;
# 1. check if AccessedTeacher is author
IF NOT InternalIsTeacherAuthor(pTeacherID, pDisciplineID) THEN
RETURN -1;
END IF;
# 2. check if student's group is bound yet
SELECT disciplines_groups.ID INTO vInGroup
FROM `students`
INNER JOIN `disciplines_groups` ON disciplines_groups.DisciplineID = pDisciplineID AND
disciplines_groups.GroupID = students.GroupID
WHERE students.ID = pStudentID
LIMIT 1;
# try to remove detached attribute
IF vInGroup > 0 THEN
DELETE FROM `disciplines_students`
WHERE disciplines_students.DisciplineID = pDisciplineID AND
disciplines_students.StudentID = pStudentID
LIMIT 1;
RETURN 0;
END IF;
# 3. try bind student
INSERT INTO `disciplines_students`
(DisciplineID, StudentID, Type)
VALUES (pDisciplineID, pStudentID, 'attach')
# update stub/ already bounded
ON DUPLICATE KEY UPDATE
disciplines_students.StudentID = disciplines_students.StudentID;
RETURN 0;
END //
DROP FUNCTION IF EXISTS UnbindGroup//
CREATE FUNCTION `UnbindGroup`
( `pTeacherID` INT, `pDisciplineID` INT, `pGroupID` INT
) RETURNS int(11)
NO SQL
BEGIN
IF NOT InternalIsTeacherAuthor(pTeacherID, pDisciplineID) OR InternalIsMapLocked(pDisciplineID)THEN
RETURN -1;
END IF;
DELETE FROM `disciplines_groups`
WHERE disciplines_groups.DisciplineID = pDisciplineID AND
disciplines_groups.GroupID = pGroupID
LIMIT 1;
# delete attached, and detached (doesn't take disc in any case)
DELETE FROM `disciplines_students`
WHERE disciplines_students.DisciplineID = pDisciplineID AND
disciplines_students.StudentID IN
(SELECT students.ID
FROM `students`
WHERE students.GroupID = pGroupID);
RETURN 0;
END //
DROP FUNCTION IF EXISTS UnbindStudent//
CREATE FUNCTION `UnbindStudent`
(`pTeacherID` INT, `pDisciplineID` INT, `pStudentID` INT
) RETURNS int(11)
NO SQL
BEGIN
DECLARE vInGroup INT DEFAULT -1;
DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;
IF NOT InternalIsTeacherAuthor(pTeacherID, pDisciplineID) THEN
RETURN -1;
END IF;
SELECT disciplines_groups.ID INTO vInGroup
FROM `students`
INNER JOIN `disciplines_groups` ON disciplines_groups.DisciplineID = pDisciplineID AND
disciplines_groups.GroupID = students.GroupID
WHERE students.ID = pStudentID
LIMIT 1;
IF vInGroup > 0 THEN
INSERT INTO `disciplines_students`
(DisciplineID, StudentID, Type)
VALUES (pDisciplineID, pStudentID, 'detach');
ELSE
DELETE FROM `disciplines_students`
WHERE disciplines_students.DisciplineID = pDisciplineID AND
disciplines_students.StudentID = pStudentID
LIMIT 1;
END IF;
RETURN 0;
END //
DROP FUNCTION IF EXISTS BindTeacher//
CREATE FUNCTION `BindTeacher`
(`pTeacherID` INT, `pBindingTeacherID` INT, `pDisciplineID` INT
) RETURNS int(11)
NO SQL
BEGIN
IF NOT InternalIsTeacherAuthor(pTeacherID, pDisciplineID) THEN
RETURN -1;
END IF;
# try to insert BindingTeacher in access list
INSERT INTO `disciplines_teachers`
(DisciplineID, TeacherID)
VALUES (pDisciplineID, pBindingTeacherID)
ON DUPLICATE KEY UPDATE # just stub
disciplines_teachers.TeacherID = disciplines_teachers.TeacherID;
RETURN 0;
END //
DROP FUNCTION IF EXISTS UnbindTeacher//
CREATE FUNCTION `UnbindTeacher`
( `pAuthorID` INT, `pBindingTeacher` INT, `pDisciplineID` INT
) RETURNS int(11)
NO SQL
BEGIN
IF pAuthorID = pBindingTeacher OR
NOT InternalIsTeacherAuthor(pBindingTeacher, pDisciplineID)
THEN
RETURN -1;
END IF;
DELETE FROM `disciplines_teachers`
WHERE disciplines_teachers.DisciplineID = pDisciplineID AND
disciplines_teachers.TeacherID = pBindingTeacher;
RETURN 0;
END //
DROP FUNCTION IF EXISTS DelegateDiscipline//
CREATE FUNCTION `DelegateDiscipline`
( `pAuthorID` INT, `pNewAuthorID` INT, `pDisciplineID` INT
) RETURNS int(11)
NO SQL
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;
IF pAuthorID = pNewAuthorID OR
NOT InternalIsTeacherAuthor(pAuthorID, pDisciplineID) OR
NOT InternalIsTeacherBounded(pNewAuthorID, pDisciplineID)
THEN
RETURN -1;
END IF;
UPDATE `disciplines`
SET disciplines.AuthorID = pNewAuthorID
WHERE disciplines.ID = pDisciplineID
LIMIT 1;
RETURN ROW_COUNT()-1;
END //
DROP FUNCTION IF EXISTS ClearDiscipline//
CREATE FUNCTION `ClearDiscipline`
( `pAuthorID` INT, `pDisciplineID` INT
) RETURNS int(11)
NO SQL
BEGIN
IF NOT InternalIsTeacherAuthor(pAuthorID, pDisciplineID) THEN
RETURN -1;
END IF;
DELETE FROM `logs_rating`
WHERE logs_rating.SubModuleID IN
(SELECT view_roadmap.SubmoduleID
FROM `view_roadmap`
WHERE view_roadmap.DisciplineID = pDisciplineID);
DELETE FROM `rating_table`
WHERE rating_table.SubModuleID IN
(SELECT view_roadmap.SubmoduleID
FROM `view_roadmap`
WHERE view_roadmap.DisciplineID = pDisciplineID);
UPDATE `disciplines`
SET disciplines.IsLocked = 0
WHERE disciplines.ID = pDisciplineID
LIMIT 1;
RETURN ROW_COUNT()-1;
END //
DROP FUNCTION IF EXISTS DeleteDiscipline//
CREATE FUNCTION `DeleteDiscipline`
( `pAuthorID` INT, `pDisciplineID` INT
) RETURNS int(11)
NO SQL
BEGIN
DECLARE vTemp INT DEFAULT -1;
IF NOT InternalIsTeacherAuthor(pAuthorID, pDisciplineID) THEN
RETURN -1;
END IF;
SELECT disciplines.IsLocked INTO vTemp
FROM `disciplines`
WHERE disciplines.ID = pDisciplineID
LIMIT 1;
IF vTemp != 0 THEN
RETURN -1;
END IF;
SET vTemp = CountRatings(pAuthorID, pDisciplineID);
IF vTemp > 0 THEN
RETURN -1;
END IF;
# TODO: ClearDiscipline if need
DELETE FROM `submodules`
WHERE submodules.ModuleID IN
(SELECT modules.ID
FROM `modules`
WHERE modules.DisciplineID = pDisciplineID
);
DELETE FROM `modules`
WHERE modules.DisciplineID = pDisciplineID;
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 `disciplines`
WHERE disciplines.ID = pDisciplineID
LIMIT 1;
RETURN 0;
END //
DROP FUNCTION IF EXISTS CountRatings//
CREATE FUNCTION `CountRatings`
( `pTeacherID` INT, `pDisciplineID` INT
) RETURNS int(11)
NO SQL
BEGIN
DECLARE vRes INT DEFAULT 0;
IF NOT InternalIsTeacherBounded(pTeacherID, pDisciplineID) THEN
RETURN -1;
END IF;
SELECT COUNT(rating_table.StudentID)
INTO vRes
FROM `rating_table`
INNER JOIN `submodules` ON rating_table.SubModuleID = submodules.ID
INNER JOIN `modules` ON submodules.ModuleID = modules.ID
WHERE modules.DisciplineID = pDisciplineID
LIMIT 1;
RETURN vRes;
END //
DROP FUNCTION IF EXISTS RestrictAfterMilestone//
CREATE FUNCTION `RestrictAfterMilestone`
( `pTeacherID` INT, `pDisciplineID` INT
) RETURNS int(11)
NO SQL
BEGIN
UPDATE `disciplines`
SET disciplines.MilestoneDate = CURDATE(),
disciplines.isMilestone = 1
WHERE disciplines.ID = pDisciplineID
LIMIT 1;
RETURN 0;
END //
DROP FUNCTION IF EXISTS RestrictAfterMilestoneForCredits//
CREATE FUNCTION `RestrictAfterMilestoneForCredits` ( `pTeacherID` INT,
`pFacultyID` INT
) RETURNS int(11)
NO SQL
BEGIN
UPDATE `disciplines`
SET disciplines.MilestoneDate = CURDATE(),
disciplines.isMilestone = 1
WHERE disciplines.SemesterID = @CurrentSemesterID AND
disciplines.ExamType = 'credit' AND
disciplines.FacultyID= pFacultyID;
RETURN 0;
END //
# -------------------------------------------------------------------------------------------
# Label: modules
# -------------------------------------------------------------------------------------------
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 EXIT HANDLER FOR SQLEXCEPTION RETURN -1;
SELECT disciplines.ID INTO vDisciplineID
FROM `modules`
INNER JOIN `disciplines` ON disciplines.ID = modules.DisciplineID AND
disciplines.AuthorID = pTeacherID
WHERE modules.ID = pModuleID AND
modules.Type = 'regular'
LIMIT 1;
IF vDisciplineID <= 0 OR
InternalIsMapLocked(vDisciplineID)
THEN
RETURN -1;
END IF;
UPDATE `modules`
SET modules.Name = pName
WHERE modules.ID = pModuleID
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 vChecker INT DEFAULT 0;
DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;
IF NOT InternalIsTeacherAuthor(pTeacherID, pDisciplineID) OR
InternalIsMapLocked(pDisciplineID)
THEN
RETURN -1;
END IF;
SELECT MAX(modules.OrderNum)+1 INTO vChecker
FROM `modules`
WHERE modules.DisciplineID = pDisciplineID AND modules.Type = 'regular'
LIMIT 1;
INSERT INTO `modules`
(Name, OrderNum, DisciplineID )
VALUES (pName, vChecker, pDisciplineID);
RETURN LAST_INSERT_ID();
END //
DROP FUNCTION IF EXISTS AddModuleExam//
CREATE FUNCTION `AddModuleExam`
(`pTeacherID` INT, `pDisciplineID` INT
) RETURNS int(11)
NO SQL
BEGIN
DECLARE vChecker, vModule INT DEFAULT -1;
IF NOT InternalIsTeacherAuthor(pTeacherID, pDisciplineID) OR
InternalIsMapLocked(pDisciplineID)
THEN
RETURN -1;
END IF;
SELECT modules.ID
INTO vChecker
FROM `modules`
WHERE modules.DisciplineID = pDisciplineID AND
modules.Type = 'exam';
IF vChecker > 0 THEN
RETURN -2;
END IF;
INSERT INTO `modules`
(Name, OrderNum, DisciplineID, Type)
VALUES ('Экзамен' , 3141692 , pDisciplineID, 'exam');
SET vModule = LAST_INSERT_ID();
SET vChecker = AddSubmodule(pTeacherID, vModule, 40, '', NULL, 'LandmarkControl');
SET vChecker = AddSubmodule(pTeacherID, vModule, 40, '', NULL, 'LandmarkControl');
SET vChecker = AddSubmodule(pTeacherID, vModule, 40, '', NULL, 'LandmarkControl');
RETURN vModule;
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;
IF NOT InternalIsTeacherAuthor(pTeacherID, pDisciplineID)
THEN
RETURN -1;
END IF;
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;
INSERT INTO `modules`
(Name, OrderNum, DisciplineID, Type)
VALUES ('Добор баллов' , 2900666 , pDisciplineID, 'extra');
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;
SET vGap = -1;
IF vType = 1 THEN
# exam
SET vGap = 7;
END IF;
IF vType = 2 THEN
# 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 vChecker INT DEFAULT -1;
SELECT disciplines.ID
INTO vChecker
FROM `modules`
INNER JOIN `disciplines` ON modules.DisciplineID = disciplines.ID AND
disciplines.AuthorID = pTeacherID
WHERE modules.ID = pModuleID
LIMIT 1;
IF vChecker <= 0 THEN
RETURN -1;
END IF;
IF NOT InternalIsTeacherAuthor(pTeacherID, vChecker) OR
InternalIsMapLocked(vChecker)
THEN
RETURN -1;
END IF;
DELETE FROM `submodules`
WHERE submodules.ModuleID = pModuleID;
DELETE FROM `modules`
WHERE modules.ID = pModuleID;
SET @counter = 0;
UPDATE `modules`
SET modules.OrderNum = (@counter := @counter + 1)
WHERE modules.DisciplineID = vChecker 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;
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;
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;
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;
IF vDisciplineID1 != vDisciplineID2 OR vDisciplineID1 <= 0 OR
InternalIsMapLocked(vDisciplineID1) THEN
RETURN -1;
END IF;
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;
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, 3 );
SELECT modules.ID
INTO vModuleID
FROM `modules`
WHERE modules.DisciplineID = pDisciplineID AND
modules.Type = 'bonus'
LIMIT 1;
IF vModuleID <= 0 THEN
RETURN -1;
END IF;
SET vChecker = AddSubmodule(pTeacherID, vModuleID, 10, '', NULL, 'LandmarkControl');
RETURN 0;
END //
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;
SELECT modules.ID
INTO vBonusModuleID
FROM `modules`
WHERE modules.Type = 'bonus' AND
modules.DisciplineID = pDisciplineID
LIMIT 1;
IF vBonusModuleID <= 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
# -------------------------------------------------------------------------------------------
DROP FUNCTION IF EXISTS ChangeSubmoduleMaxAndControl//
CREATE FUNCTION `ChangeSubmoduleMaxAndControl` ( `TeacherID` INT,
`SubmoduleID` INT,
`MaxRate` INT,
`ControlType` VARCHAR(30) CHARSET utf8
) RETURNS int(11)
NO SQL
BEGIN
DECLARE vChecker, disciplineID INT;
SET vChecker = -1;
SET disciplineID = -1;
SELECT submodules.ID,
disciplines.ID
INTO vChecker, disciplineID
FROM `submodules`
INNER JOIN `modules` ON submodules.ModuleID = modules.ID
INNER JOIN `disciplines` ON disciplines.ID = modules.DisciplineID
WHERE TeacherID = disciplines.AuthorID AND
SubmoduleID = submodules.ID
LIMIT 1;
IF vChecker <= 0 OR
disciplineID <= 0 OR
InternalIsMapLocked(disciplineID) THEN
RETURN -1;
END IF;
UPDATE `submodules`
SET submodules.MaxRate = MaxRate,
submodules.Type = ControlType
WHERE submodules.ID = SubmoduleID
LIMIT 1;
RETURN 0;
END //
DROP FUNCTION IF EXISTS ChangeSubmoduleName//
CREATE FUNCTION `ChangeSubmoduleName` ( `TeacherID` INT,
`SubmoduleID` INT,
`Name` VARCHAR(200) CHARSET utf8
) RETURNS int(11)
NO SQL
BEGIN
DECLARE vChecker, disciplineID INT;
SET disciplineID = -1;
SET vChecker = -1;
SELECT submodules.ID,
disciplines.ID
INTO vChecker, disciplineID
FROM `submodules`
INNER JOIN `modules` ON submodules.ModuleID = modules.ID
INNER JOIN `disciplines` ON disciplines.ID = modules.DisciplineID
WHERE TeacherID = disciplines.AuthorID AND
SubmoduleID = submodules.ID
LIMIT 1;
IF vChecker <= 0 OR
disciplineID <= 0 OR
InternalIsMapLocked(disciplineID) THEN
RETURN -1;
END IF;
UPDATE `submodules`
SET submodules.Name = Name
WHERE submodules.ID = SubmoduleID
LIMIT 1;
RETURN 0;
END //
DROP FUNCTION IF EXISTS ChangeSubmoduleDescription//
CREATE FUNCTION `ChangeSubmoduleDescription` ( `TeacherID` INT,
`SubmoduleID` INT,
`Description` VARCHAR(200) CHARSET utf8
) RETURNS int(11)
NO SQL
BEGIN
DECLARE vChecker, disciplineID INT;
SET vChecker = -1;
SET disciplineID = -1;
SELECT submodules.ID,
disciplines.ID
INTO vChecker, disciplineID
FROM `submodules`
INNER JOIN `modules` ON submodules.ModuleID = modules.ID
INNER JOIN `disciplines` ON disciplines.ID = modules.DisciplineID
WHERE TeacherID = disciplines.AuthorID AND
SubmoduleID = submodules.ID
LIMIT 1;
IF vChecker <= 0 OR
disciplineID <= 0 OR
InternalIsMapLocked(disciplineID) THEN
RETURN -1;
END IF;
UPDATE `submodules`
SET submodules.Description = Description
WHERE submodules.ID = SubmoduleID
LIMIT 1;
RETURN 0;
END //
DROP FUNCTION IF EXISTS DeleteSubmodule//
CREATE FUNCTION `DeleteSubmodule`
( `pTeacherID` INT, `pSubmoduleID` INT
) RETURNS int(11)
NO SQL
BEGIN
DECLARE vChecker, vModuleID, vDisciplineID INT DEFAULT -1;
SELECT submodules.ID, modules.ID, disciplines.ID
INTO vChecker, vModuleID, vDisciplineID
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 vChecker <= 0 OR
vDisciplineID <= 0 OR
InternalIsMapLocked(vDisciplineID) THEN
RETURN -1;
END IF;
SET vChecker = -1;
SELECT rating_table.StudentID
INTO vChecker
FROM `rating_table`
WHERE rating_table.SubmoduleID = pSubmoduleID
LIMIT 1;
IF vChecker > 0 THEN
RETURN -2;
END IF;
DELETE FROM `submodules`
WHERE submodules.ID = pSubmoduleID
LIMIT 1;
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 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 vChecker INT DEFAULT -1;
SELECT disciplines.ID
INTO vChecker
FROM `modules`
INNER JOIN `disciplines` ON disciplines.ID = modules.DisciplineID
WHERE disciplines.AuthorID = pTeacherID AND
modules.ID = pModuleID
LIMIT 1;
IF vChecker <= 0 OR
InternalIsMapLocked(vChecker)
THEN
RETURN -1;
END IF;
SET vChecker = 0;
SELECT MAX(submodules.OrderNum)
INTO vChecker
FROM `submodules`
WHERE submodules.ModuleID = pModuleID
LIMIT 1;
IF vChecker IS NULL THEN
SET vChecker = 0;
END IF;
SET vChecker = vChecker + 1;
IF pDescription = '' THEN
INSERT INTO `submodules`
(ModuleID, MaxRate, OrderNum, Name, Description, Type)
VALUES (pModuleID, pMaxRate, vChecker, pName, NULL, pControlType);
ELSE
INSERT INTO `submodules`
(ModuleID, MaxRate, OrderNum, Name, Description, Type )
VALUES (pModuleID, pMaxRate, vChecker, pName, pDescription, pControlType);
END IF;
RETURN (SELECT submodules.ID
FROM `submodules`
WHERE submodules.ModuleID = pModuleID AND
submodules.OrderNum = vChecker
LIMIT 1
);
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;
IF vModule1 <= 0 OR vModule1 != vModule2 OR
InternalIsMapLocked(vDisciplineID)
THEN
RETURN -1;
END IF;
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
# -------------------------------------------------------------------------------------------
DROP FUNCTION IF EXISTS GetMaxRateForDisc//
CREATE FUNCTION `GetMaxRateForDisc`
( `pDisciplineID` INT ) RETURNS int(11)
NO SQL
BEGIN
DECLARE vResult INT DEFAULT 0;
SELECT SUM(submodules.MaxRate)
INTO vResult
FROM `modules`
LEFT JOIN `submodules` ON submodules.ModuleID = modules.ID
WHERE modules.DisciplineID = pDisciplineID AND
submodules.IsUsed != 0 AND
(modules.Type = 1 OR ( modules.Type = 2 AND submodules.OrderNum = 1))
LIMIT 1;
RETURN (vResult);
END //
# Вычисление максимального балла для submodule
DROP FUNCTION IF EXISTS CalculateMaxRateForExtra//
CREATE FUNCTION `CalculateMaxRateForExtra`
( `pSubmoduleID` INT, `pStudentID` INT) RETURNS int(11)
NO SQL
BEGIN
DECLARE vExamType INT; # enum('exam', 'credit');# utf8;
DECLARE vLim INT;
DECLARE vResult INT DEFAULT -1;
IF vExamType = 'exam' THEN
SET vLim = 38;
ELSE
SET vLim = 60;
END IF;
SELECT vLim - GetRateForDiscSemester( pStudentID, DisciplineID)
INTO vResult
FROM `submodules`
INNER JOIN `modules` ON submodules.ModuleID = modules.ID
INNER JOIN `disciplines` ON modules.DisciplineID = disciplines.ID
WHERE modules.type='extra' AND submodules.ID = pSubmoduleID;
RETURN vResult;
END //
DROP FUNCTION IF EXISTS SetStudentRate//
CREATE FUNCTION `SetStudentRate`
( `pTeacherID` INT, `pStudentID` INT,
`pSubmoduleID` INT, `pRate` INT ) RETURNS int(11)
NO SQL
BEGIN
DECLARE vChecker, vDisciplineID, vGroupID, vRateID, vMaxRate, vMtype INT DEFAULT -1;
DECLARE vIsOver, vIsLocked, vIsUsed tinyint DEFAULT 0;
SELECT students.GroupID
INTO vGroupID
FROM `students`
WHERE students.ID = pStudentID
LIMIT 1;
IF vGroupID <= 0 THEN
RETURN -1;
END IF;
SET vMaxRate = CalculateMaxRateForExtra(pSubmoduleID, pStudentID);
SET vIsOver = 1;
SELECT modules.DisciplineID,
disciplines.IsLocked,
disciplines.isMilestone,
rating_table.StudentID,
submodules.IsUsed,
CASE
WHEN modules.type='extra' THEN
CalculateMaxRateForExtra(pSubmoduleID, pStudentID)
ELSE
submodules.maxRate
END,
modules.Type
INTO vDisciplineID, vIsLocked, vIsOver, vRateID, vIsUsed, vMaxRate, vMtype
FROM `submodules`
INNER JOIN `modules` ON submodules.ModuleID = modules.ID
INNER JOIN `disciplines` ON modules.DisciplineID = disciplines.ID
INNER JOIN `disciplines_teachers` ON disciplines.ID = disciplines_teachers.DisciplineID AND
pTeacherID = disciplines_teachers.TeacherID
LEFT JOIN `disciplines_groups` ON disciplines.ID = disciplines_groups.DisciplineID AND
vGroupID = disciplines_groups.GroupID
LEFT JOIN `disciplines_students` ON disciplines.ID = disciplines_students.DisciplineID AND
pStudentID = disciplines_students.StudentID AND
1 = disciplines_students.Type
LEFT JOIN `rating_table` ON pSubmoduleID = rating_table.SubmoduleID AND
pStudentID = rating_table.StudentID
WHERE submodules.ID = pSubModuleID AND
( disciplines_students.ID IS NOT NULL OR
disciplines_groups.ID IS NOT NULL
)
LIMIT 1;
IF vDisciplineID <= 0 OR
pRate > vMaxRate OR
(vIsOver > 0 AND (vMtype = 1 OR vMtype = 3))
THEN
RETURN -2;
END IF;
IF vRateID IS NOT NULL AND vRateID > 0 THEN
INSERT INTO `logs_rating`
(StudentID, SubmoduleID, TeacherID, Rate, Action )
VALUES (pStudentID, pSubModuleID, pTeacherID, pRate, 'change');
UPDATE `rating_table`
SET rating_table.TeacherID = pTeacherID,
rating_table.Rate = pRate,
rating_table.Date = CURDATE()
WHERE pSubmoduleID = rating_table.SubmoduleID AND
pStudentID = rating_table.StudentID
LIMIT 1;
ELSE
IF NOT vIsLocked THEN
UPDATE `disciplines`
SET disciplines.IsLocked = 1
WHERE disciplines.ID = vDisciplineID
LIMIT 1;
END IF;
INSERT INTO `logs_rating`
(StudentID, SubmoduleID, TeacherID, Rate, Action )
VALUES (pStudentID, pSubModuleID, pTeacherID, pRate, 'add');
INSERT INTO `rating_table`
( StudentID, TeacherID, SubmoduleID, Rate, Date)
VALUES ( pStudentID, pTeacherID, pSubmoduleID, pRate, CURDATE() );
IF NOT vIsUsed THEN
UPDATE `submodules`
SET submodules.IsUsed = 1
WHERE submodules.ID = pSubModuleID
LIMIT 1;
END IF;
END IF;
RETURN 0;
END //
# -------------------------------------------------------------------------------------------
# Label: requests
# -------------------------------------------------------------------------------------------
DROP FUNCTION IF EXISTS SetRequestStatus//
CREATE FUNCTION `SetRequestStatus`
(`pRequestID` INT, `pStatus` VARCHAR(20) CHARSET utf8) RETURNS int(11)
NO SQL
BEGIN
UPDATE `requests`
SET requests.Status = pStatus
WHERE requests.ID = pRequestID
LIMIT 1;
RETURN 0;
END//
DROP FUNCTION IF EXISTS CreateRequest//
CREATE FUNCTION `CreateRequest`
( `pAccountID` INT, `pTitle` VARCHAR(50) CHARSET utf8,
`pDescription` TEXT CHARSET utf8) RETURNS int(11)
NO SQL
BEGIN
INSERT INTO `requests`
(AccountID, Title, Description, Status)
VALUES (pAccountID, pTitle, pDescription, 'opened');
RETURN LAST_INSERT_ID();
END//
# DROP FUNCTION IF EXISTS SetRequestStatus//
# CREATE FUNCTION `SetRequestStatus` ( `RequestID` INT,
# `vStatus` VARCHAR(20) CHARSET utf8
# ) RETURNS int(11)
# NO SQL
# BEGIN
# IF vStatus = "closed" THEN
# INSERT INTO `requests_old`
# ( requests.ID, requests.To, requests.From,
# requests.Field1, requests.Field2, requests.Field3,
# requests.Data, requests.DataExt,
# requests.Date, requests.Type, requests.Status
# )
# SELECT requests.ID, requests.To, requests.From,
# requests.Field1, requests.Field2, requests.Field3,
# requests.Data, requests.DataExt,
# requests.Date, requests.Type, 'closed' AS 'Status'
# FROM `requests`
# WHERE requests.ID = RequestID
# LIMIT 1;
# DELETE FROM `requests`
# WHERE requests.ID = RequestID
# LIMIT 1;
# ELSE
# UPDATE `requests`
# SET requests.Status = vStatus
# WHERE RequestID = requests.ID
# LIMIT 1;
# END IF;
# RETURN 0;
# END//
# DROP FUNCTION IF EXISTS RequestReport//
# CREATE FUNCTION `RequestReport` ( `AccountID` INT,
# `vTitle` VARCHAR(50) CHARSET utf8,
# `vDescription` TEXT CHARSET utf8
# ) RETURNS int(11)
# NO SQL
# BEGIN
# INSERT INTO `requests`
# (requests.To, requests.From, requests.Data, requests.DataExt, requests.Type)
# VALUES (0, AccountID, vDescription, vTitle, 4); # 4 - report
# RETURN LAST_INSERT_ID();
# END//
# DROP FUNCTION IF EXISTS RequestDelegateDiscipline//
# CREATE FUNCTION `RequestDelegateDiscipline` ( `AuthorID` INT,
# `NewAuthorID` INT,
# `DisciplineID` INT
# ) RETURNS int(11)
# NO SQL
# BEGIN
# IF AuthorID = NewAuthorID OR
# NOT InternalIsTeacherAuthor(AuthorID, DisciplineID)
# THEN
# RETURN -1;
# END IF;
# INSERT INTO `requests`
# (requests.To, requests.From, requests.Field1, requests.Type)
# VALUES (NewAuthorID, AuthorID, DisciplineID, 1);
# RETURN 0;
# END //
# DROP FUNCTION IF EXISTS RequestDeleteDiscipline//
# CREATE FUNCTION `RequestDeleteDiscipline` ( `AuthorID` INT,
# `DisciplineID` INT
# ) RETURNS int(11)
# NO SQL
# BEGIN
# IF NOT InternalIsTeacherAuthor(AuthorID, DisciplineID)
# THEN
# RETURN -1;
# END IF;
# INSERT INTO `requests`
# (requests.To, requests.From, requests.Field1, requests.Type)
# VALUES (0, AuthorID, DisciplineID, 2);
# RETURN 0;
# END //
# DROP FUNCTION IF EXISTS RequestClearDiscipline//
# CREATE FUNCTION `RequestClearDiscipline` ( `AuthorID` INT,
# `DisciplineID` INT
# ) RETURNS int(11)
# NO SQL
# BEGIN
# IF NOT InternalIsTeacherAuthor(AuthorID, DisciplineID)
# THEN
# RETURN -1;
# END IF;
# INSERT INTO `requests`
# (requests.To, requests.From, requests.Field1, requests.Type)
# VALUES (0, AuthorID, DisciplineID, 3);
# RETURN 0;
# END //
# -------------------------------------------------------------------------------------------
# Label: recovery
# -------------------------------------------------------------------------------------------
DROP FUNCTION IF EXISTS CreateRecoveryToken//
CREATE FUNCTION `CreateRecoveryToken`
( `pAccountOrEMail` VARCHAR(255) CHARSET utf8,
`pToken` VARCHAR(100) CHARSET utf8) RETURNS int(11)
NO SQL
BEGIN
DECLARE vChecker INT DEFAULT 0;
DECLARE vAccountID INT DEFAULT -1;
SELECT accounts.ID
INTO vAccountID
FROM `accounts`
WHERE accounts.EMail = pAccountOrEMail
LIMIT 1;
IF vAccountID <= 0 THEN
RETURN -1;
END IF;
SELECT recovery_tokens.ID
INTO vChecker
FROM `recovery_tokens`
WHERE recovery_tokens.Token = pToken
LIMIT 1;
IF vChecker > 0 THEN
RETURN -1;
END IF;
INSERT INTO `recovery_tokens`
(AccountID, Token )
VALUES (vAccountID, pToken);
RETURN LAST_INSERT_ID();
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;
SELECT recovery_tokens.ID
INTO vChecker
FROM `recovery_tokens`
WHERE recovery_tokens.Token = pToken
LIMIT 1;
IF vChecker <= 0 THEN
RETURN -1;
END IF;
UPDATE `recovery_tokens`
SET recovery_tokens.IsUsed = 1
WHERE recovery_tokens.Token = pToken
LIMIT 1;
RETURN 0;
END//
DELIMITER ;