SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
DELIMITER //
DROP FUNCTION IF EXISTS GetGradeID//
DROP FUNCTION IF EXISTS SetCurSemesterID//
DROP FUNCTION IF EXISTS SetHashKey//
drop function if exists InternalIsTeacherBinded//
DROP FUNCTION IF EXISTS GetSemesterID//
DROP FUNCTION IF EXISTS GetRateForDiscExtra//
DROP FUNCTION IF EXISTS GetRateForDiscBonus//
DROP FUNCTION IF EXISTS GetRateForDiscSemester//
DROP FUNCTION IF EXISTS GetRateForDiscExamNum//
DROP FUNCTION IF EXISTS GetRateForDiscExam//
DROP FUNCTION IF EXISTS InternalNotify//
DROP FUNCTION IF EXISTS GetDisciplineMaxRate//
DROP FUNCTION IF EXISTS OrderModuleTypesForSession//
DROP FUNCTION IF EXISTS CreateStudyGroup//
DROP FUNCTION IF EXISTS GetAccCountByCode//
DROP FUNCTION IF EXISTS GetAccCountByMail//
DROP FUNCTION IF EXISTS GetAccCountByLogin//
DROP FUNCTION IF EXISTS ChangePassword//
DROP FUNCTION IF EXISTS ChangeLogin//
DROP FUNCTION IF EXISTS ChangeMail//
DROP FUNCTION IF EXISTS GetRateForDisc//
drop function if exists InternalIsTeacherBounded//
DROP FUNCTION IF EXISTS SetSemesterID//
DROP FUNCTION IF EXISTS AddDiscipline//
DROP FUNCTION IF EXISTS GetMaxRateForDisc//
DROP FUNCTION IF EXISTS BindTeacher//
DROP FUNCTION IF EXISTS GetDisciplineSemesterID//
DROP FUNCTION IF EXISTS SetBitmaskByPagename//
DROP FUNCTION IF EXISTS GetBitmaskByPagename//
DROP FUNCTION IF EXISTS SetSettings//
DROP FUNCTION IF EXISTS CountRatings//
DROP FUNCTION IF EXISTS UpdateRequest//
# -------------------------------------------------------------------------------------------
# Label: abbreviations
# -------------------------------------------------------------------------------------------
# abbreviation: abbr
# specialization: spec
# department: dep
# -------------------------------------------------------------------------------------------
# Label: internals
# -------------------------------------------------------------------------------------------
# actually check for first scoring, in this case you cannot yet edit discipline
# "SetRate" stored procedure can change isLocked flag
DROP FUNCTION IF EXISTS InternalIsMapLocked//
CREATE FUNCTION `InternalIsMapLocked` (
`pDisciplineID` INT
) RETURNS BOOLEAN
NO SQL
BEGIN
DECLARE vChecker BOOLEAN DEFAULT FALSE;
SELECT disciplines.IsLocked INTO vChecker
FROM `disciplines`
WHERE disciplines.ID = pDisciplineID
LIMIT 1;
RETURN vChecker;
END //
# check, that student really take this course
DROP FUNCTION IF EXISTS InternalIsStudentAttached//
CREATE FUNCTION `InternalIsStudentAttached` (
`pStudentID` INT,
`pDisciplineID` INT,
`pSemesterID` INT
) RETURNS BOOLEAN
NO SQL
BEGIN
DECLARE vAttachType enum('attach','detach') DEFAULT NULL;
SELECT disciplines_students.Type INTO vAttachType
FROM `disciplines_students`
WHERE disciplines_students.StudentID = pStudentID AND
disciplines_students.DisciplineID = pDisciplineID
LIMIT 1;
IF vAttachType IS NOT NULL THEN # attached or detached
RETURN (vAttachType = 'attach');
END IF;
RETURN EXISTS(
SELECT * FROM `students_groups`
INNER JOIN `disciplines_groups` ON disciplines_groups.DisciplineID = pDisciplineID AND
disciplines_groups.GroupID = students_groups.GroupID
WHERE students_groups.StudentID = pStudentID AND
students_groups.SemesterID = pSemesterID AND
students_groups.State != 'expulsion'
LIMIT 1
);
END //
# check, that teacher teach this course
DROP FUNCTION IF EXISTS InternalIsTeacherBound//
CREATE FUNCTION InternalIsTeacherBound (
`pTeacherID` INT,
`pDisciplineID` INT
) RETURNS BOOLEAN
NO SQL
BEGIN
RETURN EXISTS (
SELECT * FROM `disciplines_teachers`
WHERE disciplines_teachers.TeacherID = pTeacherID AND
disciplines_teachers.DisciplineID = pDisciplineID
LIMIT 1
);
END //
DROP FUNCTION IF EXISTS InternalIsTeacherAuthor//
CREATE FUNCTION `InternalIsTeacherAuthor` (
`pTeacherID` INT,
`pDisciplineID` INT
) RETURNS BOOLEAN
NO SQL
BEGIN
DECLARE vAuthorID INT DEFAULT -1;
SELECT disciplines.AuthorID INTO vAuthorID
FROM `disciplines`
WHERE disciplines.ID = pDisciplineID
LIMIT 1;
RETURN ( vAuthorID = pTeacherID );
END //
DROP FUNCTION IF EXISTS SetExamPeriodOption//
CREATE FUNCTION `SetExamPeriodOption` (
`pStudentID` INT,
`pSubmoduleID` INT,
`pType` enum('absence','pass')
) RETURNS int(11)
NO SQL
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;
INSERT INTO `exam_period_options`
(StudentID, SubmoduleID, Type) VALUES(pStudentID, pSubmoduleID, pType)
ON DUPLICATE KEY UPDATE
exam_period_options.Type = pType;
RETURN 0;
END //
# check, regular + exam rate == 100
DROP FUNCTION IF EXISTS InternalIsMapCreated//
CREATE FUNCTION `InternalIsMapCreated` (
`pDisciplineID` INT
) RETURNS int(11)
NO SQL
BEGIN
DECLARE vMaxRate INT DEFAULT -1;
SELECT disciplines.MaxRate INTO vMaxRate
FROM disciplines
WHERE disciplines.ID = pDisciplineID
LIMIT 1;
RETURN ( vMaxRate = 100 );
END //
# ordering helper
DROP FUNCTION IF EXISTS InternalOrderModuleTypesForSession//
CREATE FUNCTION `InternalOrderModuleTypesForSession` (
`pModuleType` INT
) RETURNS INT(3)
NO SQL
BEGIN
DECLARE vRes INT DEFAULT 0;
CASE pModuleType
WHEN 4 THEN SET vRes = 1; # extra
WHEN 2 THEN SET vRes = 2; # exam
WHEN 3 THEN SET vRes = 3; # bonus
ELSE SET vRes = 4;
END CASE;
RETURN vRes;
END //
# -------------------------------------------------------------------------------------------
# Label: preferences
# Label: magic
# -------------------------------------------------------------------------------------------
# -------------------------------------------------------------------------------------------
# Label: semesters
# -------------------------------------------------------------------------------------------
# -------------------------------------------------------------------------------------------
# Label: faculties
# -------------------------------------------------------------------------------------------
DROP FUNCTION IF EXISTS CreateFaculty //
CREATE FUNCTION CreateFaculty (
`pFacultyName` TEXT CHARSET utf8,
`pFacultyAbbr` TEXT CHARSET utf8
) RETURNS INT(11) # -1 or id
NO SQL
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;
INSERT INTO faculties
(Name, Abbr) VALUES(pFacultyName, pFacultyAbbr);
RETURN LAST_INSERT_ID();
END//
# -------------------------------------------------------------------------------------------
# Label: departments
# -------------------------------------------------------------------------------------------
# create department or return existing
DROP FUNCTION IF EXISTS CreateDepartment //
DROP FUNCTION IF EXISTS Department_Create //
CREATE FUNCTION Department_Create (
`pName` VARCHAR(200) CHARSET utf8,
`pFacultyID` INT(11)
) RETURNS INT(11) # department id or -1 if failed
NO SQL
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;
INSERT INTO departments
(Name, FacultyID) VALUES (pName, pFacultyID)
ON DUPLICATE KEY UPDATE
departments.ID = LAST_INSERT_ID(departments.ID);
RETURN LAST_INSERT_ID();
END //
DROP FUNCTION IF EXISTS Department_Search //
CREATE FUNCTION Department_Search (
`pName` VARCHAR(200) CHARSET utf8,
`pFacultyID` INT(11)
) RETURNS INT(11) # department id of -1 if not found
NO SQL
BEGIN
DECLARE vID INT DEFAULT -1;
SELECT departments.ID INTO vID
FROM departments
WHERE departments.Name = pName AND
departments.FacultyID = pFacultyID
LIMIT 1;
RETURN vID;
END //
# -------------------------------------------------------------------------------------------
# Label: specializations
# -------------------------------------------------------------------------------------------
# -------------------------------------------------------------------------------------------
# Label: grades
# -------------------------------------------------------------------------------------------
DROP FUNCTION IF EXISTS CreateGrade//
CREATE FUNCTION `CreateGrade` (
`pGradeNum` INT,
`pDegree` enum('bachelor', 'master', 'specialist')
) RETURNS int(11) # groupID or -1 if failed
NO SQL
BEGIN
DECLARE vGradeID INT DEFAULT -1;
DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;
INSERT INTO `grades`
(Num, Degree) VALUES (pGradeNum, pDegree)
ON DUPLICATE KEY UPDATE
grades.ID = LAST_INSERT_ID(grades.ID);
RETURN LAST_INSERT_ID();
END //
# -------------------------------------------------------------------------------------------
# Label: groups
# -------------------------------------------------------------------------------------------
# negative int, if already exists
DROP FUNCTION IF EXISTS CreateGroup//
CREATE FUNCTION `CreateGroup` (
`pGradeID` INT,
`pGroupNum` INT,
`pSpecializationID` INT,
`pGroupName` VARCHAR(50) CHARSET utf8
) RETURNS int(11) # group id
NO SQL
BEGIN
# check GradeID, SpecID constraints and (GradeID, GroupNum, SpecID) - unique
DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;
# create discipline
INSERT INTO `study_groups`
(GradeID, GroupNum, SpecializationID, Name)
VALUES (pGradeID, pGroupNum, pSpecializationID, pGroupName)
ON DUPLICATE KEY UPDATE
study_groups.ID = LAST_INSERT_ID(study_groups.ID);
RETURN LAST_INSERT_ID();
END //
DROP FUNCTION IF EXISTS GetGroup//
CREATE FUNCTION `GetGroup` (
`pGradeID` INT,
`pGroupNum` INT,
`pFacultyID` INT
) RETURNS int(11) # groupID or -1 if failed
NO SQL
BEGIN
DECLARE vGroupID INT DEFAULT -1;
SELECT study_groups.ID INTO vGroupID
FROM `study_groups`
INNER JOIN `specializations` ON specializations.ID = study_groups.SpecializationID
WHERE study_groups.GradeID = pGradeID AND
study_groups.GroupNum = pGroupNum AND
specializations.FacultyID = pFacultyID
LIMIT 1;
RETURN vGroupID;
END //
DROP FUNCTION IF EXISTS GetStudentGroup//
CREATE FUNCTION `GetStudentGroup` (
`pStudentID` INT,
`pSemesterID` INT
) RETURNS int(11) # groupID or -1 if failed
NO SQL
BEGIN
DECLARE vGroupID INT DEFAULT -1;
SELECT students_groups.GroupID INTO vGroupID
FROM `students_groups`
WHERE students_groups.StudentID = pStudentID AND
students_groups.SemesterID = pSemesterID AND
students_groups.State != 'expulsion'
LIMIT 1;
RETURN vGroupID;
END //
# -------------------------------------------------------------------------------------------
# Label: subjects
# -------------------------------------------------------------------------------------------
DROP FUNCTION IF EXISTS CreateSubject//
CREATE FUNCTION `CreateSubject` (
`pFacultyID` INT,
`pSubjectName` TEXT CHARSET utf8,
`pSubjectAbbr` VARCHAR(20) CHARSET utf8
) RETURNS int(11)
NO SQL
BEGIN
DECLARE vSubjectID INT DEFAULT -1;
SET @vChecker := -1;
IF pSubjectName = '' THEN RETURN -1; END IF;
# create/get subject (subject name is unique key)
INSERT INTO `subjects`
(Name, Abbr) VALUES (pSubjectName, pSubjectAbbr)
ON DUPLICATE KEY UPDATE
subjects.ID = ( @vChecker := LAST_INSERT_ID(subjects.ID));
SET vSubjectID = LAST_INSERT_ID();
BEGIN # handler block
DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;
INSERT INTO `subjects_faculties` # try to attach subject to faculty
(SubjectID, FacultyID) VALUES (vSubjectID, pFacultyID)
ON DUPLICATE KEY UPDATE # just stub
subjects_faculties.ID = LAST_INSERT_ID(subjects_faculties.ID);
END;
RETURN ( @vChecker > 0 ) + 0;
END //
DROP FUNCTION IF EXISTS DeleteSubject //
CREATE FUNCTION DeleteSubject (
`pSubjectID` INT
) RETURNS INT(11) # 0 - success
NO SQL
BEGIN
DECLARE vSubjectUsage BOOLEAN DEFAULT FALSE;
DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;
SET vSubjectUsage = EXISTS(
SELECT * FROM `disciplines`
WHERE disciplines.SubjectID = pSubjectID
LIMIT 1
);
IF vSubjectUsage THEN
RETURN -1; # Удаляемый предмет используется в disciplines.
END IF;
DELETE FROM `subjects_faculties`
WHERE subjects_faculties.SubjectID = pSubjectID;
DELETE FROM `subjects`
WHERE subjects.ID = pSubjectID
LIMIT 1;
RETURN 0; # Успешно удалено;
END //
# -------------------------------------------------------------------------------------------
# Label: accounts
# -------------------------------------------------------------------------------------------
DROP FUNCTION IF EXISTS CheckAccountExistence//
CREATE FUNCTION `CheckAccountExistence` (
`pData` TEXT CHARSET utf8,
`pType` enum('login','email', 'code')
) RETURNS BOOLEAN # TRUE - exist, FALSE - doesn't
NO SQL
BEGIN
DECLARE vRes BOOLEAN DEFAULT FALSE;
SET vRes = EXISTS(
SELECT * FROM `accounts`
WHERE CASE pType
WHEN 'login' THEN pData = accounts.Login
WHEN 'email' THEN pData = accounts.EMail
WHEN 'code' THEN pData = accounts.ActivationCode
ELSE FALSE
END
LIMIT 1
);
RETURN vRes;
END //
# return:
# -1 - unknown error.
# -2 - code doesn't exists.
# -3 - email already registered.
# -4 - login already registered.
DROP FUNCTION IF EXISTS ActivateAccount//
CREATE FUNCTION `ActivateAccount` (
`pCode` VARCHAR(40) CHARSET utf8,
`pLogin` VARCHAR(50) CHARSET utf8,
`pEMail` VARCHAR(50) CHARSET utf8,
`pPassword` VARCHAR(255) CHARSET utf8
) RETURNS int(11)
NO SQL
BEGIN
DECLARE vExistEmail, vExistLogin BOOLEAN DEFAULT FALSE;
# check for matching with existing accounts (note: Login & E-Mail are unique)
DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;
SET vExistEmail = CheckAccountExistence(pEMail, 'email');
IF vExistEmail THEN
RETURN -3;
END IF;
SET vExistLogin = CheckAccountExistence(pLogin, 'login');
IF vExistLogin THEN
RETURN -4;
END IF;
# activate account
UPDATE `accounts`
SET accounts.Login = pLogin,
accounts.Password = SHA1(pPassword),
accounts.EMail = pEMail,
accounts.ActivationCode = NULL
WHERE accounts.ActivationCode = pCode AND
( @vAccountID := accounts.ID ) # save accountID
LIMIT 1;
RETURN IF(ROW_COUNT() = 0, -2, @vAccountID); # 0 - account with this Code not found
END //
DROP FUNCTION IF EXISTS ChangeAccountData//
CREATE FUNCTION `ChangeAccountData` (
`pUserID` INT,
`pData` TEXT CHARSET utf8,
`pType` enum('login', 'email', 'password')
) RETURNS int(11) # 1 - success, 0 - failed
NO SQL
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN 0;
CASE pType
WHEN 'login' THEN
UPDATE `accounts`
SET accounts.Login = pData
WHERE accounts.ID = pUserID
LIMIT 1;
WHEN 'email' THEN
UPDATE `accounts`
SET accounts.EMail = pData
WHERE accounts.ID = pUserID
LIMIT 1;
WHEN 'password' THEN
UPDATE `accounts`
SET accounts.Password = SHA1(pData)
WHERE accounts.ID = pUserID
LIMIT 1;
END CASE;
RETURN 1;
END //
DROP FUNCTION IF EXISTS SignIn//
CREATE FUNCTION `SignIn` (
`pLoginOrMail` VARCHAR(255) CHARSET utf8,
`pPassword` VARCHAR(64) CHARSET utf8
) RETURNS int(11) # account id
NO SQL
BEGIN
DECLARE vAccountID INT DEFAULT -1;
#check account existence
SELECT accounts.ID INTO vAccountID
FROM `accounts`
WHERE accounts.Password = SHA1(pPassword) AND
(accounts.Login = pLoginOrMail OR accounts.EMail = pLoginOrMail)
LIMIT 1;
IF vAccountID <= 0 THEN
RETURN -1;
END IF;
# TODO: extract method - log sign in
INSERT INTO `logs_signin` # logging
(AccountID) VALUES (vAccountID);
RETURN vAccountID;
END //
# -------------------------------------------------------------------------------------------
# Label: teachers
# -------------------------------------------------------------------------------------------
DROP FUNCTION IF EXISTS ChangeTeacherInfo//
CREATE FUNCTION `ChangeTeacherInfo` (
`pTeacherID` INT,
`pLastName` VARCHAR(30) CHARSET utf8,
`pFirstName` VARCHAR(30) CHARSET utf8,
`pSecondName` VARCHAR(30) CHARSET utf8,
`pJobPositionID` INT,
`pDepartmentID` INT
) RETURNS int(11) # -1 if teacher doesn't exists, otherwise 0
NO SQL
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;
# set new info
UPDATE `teachers`
SET teachers.LastName = pLastName,
teachers.FirstName = pFirstName,
teachers.SecondName = pSecondName,
teachers.JobPositionID = pJobPositionID,
teachers.DepartmentID = pDepartmentID
WHERE teachers.ID = pTeacherID
LIMIT 1;
RETURN ROW_COUNT()-1;
END //
DROP FUNCTION IF EXISTS CreateTeacher//
DROP FUNCTION IF EXISTS Teacher_Create//
CREATE FUNCTION `Teacher_Create` (
`pLastName` VARCHAR(30) CHARSET utf8,
`pFirstName` VARCHAR(30) CHARSET utf8,
`pSecondName` VARCHAR(30) CHARSET utf8,
`pJobPositionID` INT,
`pDepartmentID` INT,
`pActivationCode` VARCHAR(40) CHARSET utf8
) RETURNS int(11) # 0 - success, <0 failed
NO SQL
BEGIN
DECLARE vAccountID INT DEFAULT -1;
DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;
# TODO: kill magic constants (user role 2 - common teacher)
# create account
INSERT INTO `accounts`
(Login , Password , EMail, UserRoleID, ActivationCode )
VALUES ( NULL, NULL, NULL, 2, pActivationCode);
SET vAccountID = LAST_INSERT_ID();
# add new teacher
INSERT INTO `teachers`
(AccountID, LastName, FirstName, SecondName, JobPositionID, DepartmentID)
VALUES (vAccountID, pLastName, pFirstName, pSecondName, pJobPositionID, pDepartmentID);
RETURN LAST_INSERT_ID();
END //
-- -1 - не сотрудник деканата и не преподаватель дисциплины
-- 0 - только чтение
-- 1 - редактирование
DROP FUNCTION IF EXISTS GetEditRightsForTeacher//
CREATE FUNCTION `GetEditRightsForTeacher` (
`pTeacherID` INT,
`pDisciplineID` INT
) RETURNS int(11)
NO SQL
BEGIN
DECLARE vUserRole INT DEFAULT -1;
DECLARE vIsBound BOOLEAN;
SET vIsBound = InternalIsTeacherBound(pTeacherID, pDisciplineID);
IF vIsBound > 0 THEN
RETURN 1;
END IF;
SELECT accounts.UserRoleID INTO vUserRole
FROM `teachers`
INNER JOIN `accounts` ON teachers.AccountID=accounts.ID
WHERE teachers.ID = pTeacherID
LIMIT 1;
# TODO: magic constants (4 - сотрудник деканата)
RETURN (vUserRole = 4, 0, -1);
END //
# -------------------------------------------------------------------------------------------
# Label: students
# -------------------------------------------------------------------------------------------
# TODO: magic constants (UserRoleID)
# TODO: group id instead num and grade
DROP FUNCTION IF EXISTS CreateStudent//
CREATE FUNCTION `CreateStudent` (
`pLastName` VARCHAR(30) CHARSET utf8,
`pFirstName` VARCHAR(30) CHARSET utf8,
`pSecondName` VARCHAR(30) CHARSET utf8,
`pGradeID` INT,
`pGroupNum` INT,
`pFacultyID` INT,
`pActivationCode` VARCHAR(40) CHARSET utf8,
`pSemesterID` INT
) RETURNS int(11)
NO SQL
BEGIN
DECLARE vAccountID, vGroupID, vStudentID, vSemesterID INT DEFAULT -1;
DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;
SET vGroupID = GetGroup(pGradeID, pGroupNum, pFacultyID);
IF vGroupID <= 0 THEN
RETURN -1;
END IF;
# create new account, UserRoleID: 1 = student
INSERT INTO `accounts`
(Login , Password , EMail, UserRoleID, ActivationCode )
VALUES ( NULL, NULL, NULL, 1, pActivationCode);
SET vAccountID = LAST_INSERT_ID();
# create student
INSERT INTO `students`
(AccountID, LastName, FirstName, SecondName)
VALUES (vAccountID, pLastName, pFirstName, pSecondName);
SET vStudentID = LAST_INSERT_ID();
RETURN ControlStudentGroup(vStudentID, vGroupID, 'common', pSemesterID);
END //
# unlike fn CreateStudent, this can create all missing records (group, grade, specialization)
DROP FUNCTION IF EXISTS CreateStudentEx//
CREATE FUNCTION `CreateStudentEx` (
`pLastName` VARCHAR(30) CHARSET utf8,
`pFirstName` VARCHAR(30) CHARSET utf8,
`pSecondName` VARCHAR(30) CHARSET utf8,
`pGradeNum` INT,
`pGroupNum` INT,
`pDegree` enum('bachelor', 'master', 'specialist'),
`pSpecName` VARCHAR(50) CHARSET utf8,
`pFacultyID` INT,
`pActivationCode` VARCHAR(40) CHARSET utf8,
`pSemesterID` INT
) RETURNS int(11)
NO SQL
BEGIN
DECLARE vAccountID, vGradeID, vSpecID, vGroupID INT DEFAULT -1;
DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;
# get specialization id
INSERT INTO `specializations`
(Name, Abbr, FacultyID) VALUES (pSpecName, NULL, pFacultyID)
ON DUPLICATE KEY UPDATE
specializations.ID = LAST_INSERT_ID(specializations.ID);
SET vSpecID = LAST_INSERT_ID();
SET vGradeID = CreateGrade(pGradeNum, pDegree);
SET vGroupID = CreateGroup(vGradeID, pGroupNum, vSpecID, NULL);
RETURN CreateStudent(pLastName, pFirstName, pSecondName, vGradeID, pGroupNum, pFacultyID, pActivationCode, pSemesterID);
END //
# Give a student an academic leave or attach him to group.
# params:
# StudentID (int)
# GroupID (int) : -1, to update all appropriate 'common' records
# State (enum)
# SemesterID (int)
DROP FUNCTION IF EXISTS ControlStudentGroup//
CREATE FUNCTION `ControlStudentGroup` (
`pStudentID` INT,
`pGroupID` INT,
`pState` enum('common', 'outlet', 'expulsion', 'leave'),
`pSemesterID` INT
) RETURNS int(11)
NO SQL
BEGIN
DECLARE vChecker INT DEFAULT 0;
DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;
INSERT INTO `students_groups` (StudentID, GroupID, SemesterID, State)
VALUES (pStudentID, pGroupID, pSemesterID, pState)
ON DUPLICATE KEY UPDATE
students_groups.GroupID = pGroupID,
students_groups.State = pState,
students_groups.Date = CURDATE();
RETURN ROW_COUNT()-1;
END //
DROP FUNCTION IF EXISTS RemoveFromGroupInSemester//
CREATE FUNCTION `RemoveFromGroupInSemester` (
`pStudentID` INT,
`pGroupID` INT,
`pSemesterID` INT
) RETURNS int(11)
NO SQL
BEGIN
DECLARE vChecker INT DEFAULT 0;
DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;
DELETE FROM `students_groups`
WHERE students_groups.GroupID = pGroupID
and students_groups.StudentID = pStudentID
and students_groups.SemesterID = pSemesterID
and students_groups.State = 'common'
LIMIT 1;
RETURN ROW_COUNT()-1;
END //
# -------------------------------------------------------------------------------------------
# Label: disciplines
# -------------------------------------------------------------------------------------------
DROP FUNCTION IF EXISTS GetDisciplineProperty//
CREATE FUNCTION `GetDisciplineProperty` (
`pDisciplineID` INT,
`pType` enum('grade', 'subject', 'author', 'semester', 'milestone', 'type')
) RETURNS int(11)
NO SQL
BEGIN
DECLARE vRes INT DEFAULT -1;
SELECT CASE pType
WHEN 'semester' THEN disciplines.SemesterID
WHEN 'author' THEN disciplines.AuthorID
WHEN 'grade' THEN disciplines.GradeID
WHEN 'subject' THEN disciplines.SubjectID
WHEN 'milestone' THEN disciplines.Milestone
WHEN 'type' THEN disciplines.ExamType + 0
END INTO vRes
FROM `disciplines`
WHERE disciplines.ID = pDisciplineID
LIMIT 1;
RETURN vRes;
END //
DROP FUNCTION IF EXISTS Discipline_Create//
CREATE FUNCTION `Discipline_Create` (
`pTeacherID` INT,
`pGradeID` INT,
`pSubjectID` INT,
`pExamType` enum('exam', 'credit', 'grading_credit'),
`pLectureCount` INT,
`pPracticeCount` INT,
`pLabCount` INT,
`pFacultyID` INT,
`pSemesterID` INT,
`pSubtype` enum('scientific_coursework', 'disciplinary_coursework')
) RETURNS int(11)
NO SQL
BEGIN
DECLARE vChecker, vDisciplineID INT DEFAULT -1;
DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;
# todo: make more flexible creation of coursework
# I mean, while creating scientific coursework
# we don't have the SubjectID field, but we must.
# 346 is used as a default id for scientific courseworks.
# This constant is duplicated in Model_Helper_CourseWorkBuilder
IF pSubtype IS NOT NULL THEN
SET pSubjectID = 346;
END IF;
# create discipline
INSERT INTO `disciplines`
(AuthorID, GradeID, SubjectID, ExamType,
LectureCount, PracticeCount,LabCount,
SemesterID, FacultyID, Subtype)
VALUES (
pTeacherID, pGradeID, pSubjectID, pExamType,
pLectureCount, pPracticeCount, pLabCount,
pSemesterID, pFacultyID, pSubtype
);
SET vDisciplineID = LAST_INSERT_ID();
SET vChecker = Discipline_BindTeacher(vDisciplineID, pTeacherID);
# add exam and extra modules
IF pExamType = 'exam' THEN
SET vChecker = AddModuleExam(pTeacherID, vDisciplineID);
END IF;
SET vChecker = AddModuleExtra(pTeacherID, vDisciplineID);
RETURN vDisciplineID;
END //
DROP FUNCTION IF EXISTS ChangeDisciplineSubject//
CREATE FUNCTION `ChangeDisciplineSubject` (
`pTeacherID` INT,
`pDisciplineID` INT,
`pSubjectID` INT
) RETURNS int(11)
NO SQL
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;
# TODO: move to php
IF NOT InternalIsTeacherAuthor(pTeacherID, pDisciplineID) OR
InternalIsMapLocked(pDisciplineID)
THEN
RETURN -1;
END IF;
UPDATE `disciplines`
SET disciplines.SubjectID = pSubjectID
WHERE disciplines.ID = pDisciplineID
LIMIT 1;
RETURN ROW_COUNT()-1;
END //
DROP FUNCTION IF EXISTS GetMilestone//
CREATE FUNCTION `GetMilestone` (
`pFacultyID` INT,
`pSemesterID` INT
) RETURNS int(11)
NO SQL
BEGIN
DECLARE vMilestone, vCounter INT DEFAULT 0;
# get most frequent milestone
SELECT COUNT(*) AS 'cnt', disciplines.Milestone
INTO vCounter, vMilestone
FROM `disciplines`
WHERE disciplines.SemesterID = pSemesterID AND
disciplines.FacultyID = pFacultyID
GROUP BY disciplines.Milestone
ORDER BY cnt DESC
LIMIT 1;
RETURN vMilestone;
END //
DROP FUNCTION IF EXISTS ChangeDisciplineGrade//
CREATE FUNCTION `ChangeDisciplineGrade` (
`pTeacherID` INT,
`pDisciplineID` INT,
`pGradeID` INT
) RETURNS int(11)
NO SQL
BEGIN
DECLARE vCurGradeID INT DEFAULT -1;
DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;
# TODO: move to php
IF InternalIsMapLocked(pDisciplineID) OR
NOT InternalIsTeacherAuthor(pTeacherID, pDisciplineID)
THEN
RETURN -1;
END IF;
SET vCurGradeID = GetDisciplineProperty(pDisciplineID, 'grade');
IF vCurGradeID = pGradeID THEN
RETURN 0;
END IF;
# delete all students
DELETE FROM `disciplines_groups`
WHERE disciplines_groups.DisciplineID = pDisciplineID;
DELETE FROM `disciplines_students`
WHERE disciplines_students.DisciplineID = pDisciplineID;
# set grade
UPDATE `disciplines`
SET disciplines.GradeID = pGradeID
WHERE disciplines.ID = pDisciplineID
LIMIT 1;
RETURN ROW_COUNT()-1;
END //
DROP FUNCTION IF EXISTS ChangeDisciplineControl//
CREATE FUNCTION `ChangeDisciplineControl` (
`pTeacherID` INT,
`pDisciplineID` INT,
`pExamType` enum('exam', 'credit', 'grading_credit')
) RETURNS int(11)
NO SQL
BEGIN
DECLARE vChecker, vExtraMax, vExtraID INT DEFAULT -1;
DECLARE vOldExamType enum('exam', 'credit', 'grading_credit');
DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;
# move to php
IF InternalIsMapLocked(pDisciplineID) OR
NOT InternalIsTeacherAuthor(pTeacherID, pDisciplineID)
THEN
RETURN -1;
END IF;
# get exam type and extra module ID
SELECT disciplines.ExamType, modules.ID INTO vOldExamType, vExtraID
FROM `modules`
INNER JOIN `disciplines` ON disciplines.ID = modules.DisciplineID
WHERE modules.DisciplineID = pDisciplineID AND
modules.Type = 'extra'
LIMIT 1;
IF vExtraID <= 0 THEN
RETURN -1;
END IF;
# check type changing: exam <-> credit/grading_credit
IF NOT (vOldExamType = 'exam' XOR pExamType != 'exam') THEN
# TODO: extract method addExtraModule
IF pExamType = 'exam' THEN # change to exam
# count discipline's current max rate
SET vChecker = InternalIsMapCreated(pDisciplineID);
IF vChecker >= 61 THEN # can't add exam module ( > 100 points)
RETURN 1;
END IF;
SET vChecker = AddModuleExam(pTeacherID, pDisciplineID);
# delete extra submodules(only 1 extra for exam)
DELETE FROM `submodules`
WHERE submodules.OrderNum > 1 AND submodules.ModuleID = vExtraID;
SET vExtraMax = 7;
ELSE # change to credit
SET vExtraMax = 29;
SET vChecker = DeleteModuleExam(pTeacherID, pDisciplineID);
# 2 extra submodules (1 already created for exam)
SET vChecker = AddSubmodule(pTeacherID, vExtraID, vExtraMax, '', NULL, 'LandmarkControl');
END IF;
END IF;
# set new exam type
UPDATE `disciplines`
SET disciplines.ExamType = pExamType
WHERE disciplines.ID = pDisciplineID
LIMIT 1;
# set max rate for extra
UPDATE `submodules`
SET submodules.MaxRate = vExtraMax
WHERE submodules.ModuleID = vExtraID;
RETURN 0;
END //
DROP FUNCTION IF EXISTS ChangeDisciplineHours//
CREATE FUNCTION `ChangeDisciplineHours` (
`pTeacherID` INT,
`pDisciplineID` INT,
`pHours` INT,
`pType` INT
# Type: 0 - Practice Hours, 1 - Lecture Hours, 2 - Lab Hours
) RETURNS int(11)
NO SQL
BEGIN
IF NOT InternalIsTeacherAuthor(pTeacherID, pDisciplineID) THEN
RETURN -1;
END IF;
CASE pType
WHEN 0 THEN
UPDATE `disciplines`
SET disciplines.PracticeCount = pHours
WHERE disciplines.ID = pDisciplineID
LIMIT 1;
WHEN 1 THEN
UPDATE `disciplines`
SET disciplines.LectureCount = pHours
WHERE disciplines.ID = pDisciplineID
LIMIT 1;
WHEN 2 THEN
UPDATE `disciplines`
SET disciplines.LabCount = pHours
WHERE disciplines.ID = pDisciplineID
LIMIT 1;
END CASE;
RETURN ROW_COUNT()-1;
END //
DROP FUNCTION IF EXISTS LogBind//
CREATE FUNCTION `LogBind` (
`pDisciplineID` INT,
`pTeacherID` INT,
`EntityID` INT,
`pAttach` enum('attach', 'detach'),
`pType` enum('group', 'student')
) RETURNS int(11) # 0 - success, < 0 - failed
NO SQL
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;
IF pType = 'group' THEN
INSERT INTO `logs_binds_groups`
(`DisciplineID`, `TeacherID`, `GroupID`, `Type`)
VALUES (pDisciplineID, pTeacherID, EntityID, pAttach);
ELSEIF pType = 'student' THEN
INSERT INTO `logs_binds_students`
(`DisciplineID`, `TeacherID`, `StudentID`, `Type`)
VALUES (pDisciplineID, pTeacherID, EntityID, pAttach);
END IF;
RETURN ROW_COUNT()-1;
END //
DROP FUNCTION IF EXISTS BindGroup//
CREATE FUNCTION `BindGroup` (
`pTeacherID` INT,
`pDisciplineID` INT,
`pGroupID` INT
) RETURNS int(11)
NO SQL
BEGIN
DECLARE vSemesterID INT DEFAULT -1;
DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -3;
SET @isAttached := -1;
# todo: move to php layer
IF NOT InternalIsTeacherBound(pTeacherID, pDisciplineID) OR
InternalIsMapLocked(pDisciplineID)
THEN
RETURN -1;
END IF;
# bind whole group
INSERT INTO `disciplines_groups`
(DisciplineID, GroupID)
VALUES ( pDisciplineID, pGroupID )
ON DUPLICATE KEY UPDATE
disciplines_groups.ID = ( @isAttached := LAST_INSERT_ID(disciplines_groups.ID) );
IF @isAttached > 0 THEN # group was attached
SET vSemesterID = GetDisciplineProperty(pDisciplineID, 'semester');
# 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_groups.StudentID
FROM `students_groups`
WHERE students_groups.GroupID = pGroupID AND
students_groups.SemesterID = vSemesterID
);
RETURN 1;
END IF;
RETURN LogBind(pDisciplineID, pTeacherID, pGroupID, 'attach', 'group');
END //
DROP FUNCTION IF EXISTS BindStudent//
CREATE FUNCTION `BindStudent` (
`pTeacherID` INT,
`pDisciplineID` INT,
`pStudentID` INT
) RETURNS int(11)
NO SQL
BEGIN
DECLARE vChecker, vStudentGroupID, vTemp, vSemesterID INT DEFAULT -1;
DECLARE vInGroup BOOLEAN DEFAULT FALSE;
DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;
# todo: move to php layer
IF NOT InternalIsTeacherBound(pTeacherID, pDisciplineID) THEN
RETURN -1;
END IF;
SET vSemesterID = GetDisciplineProperty(pDisciplineID, 'semester');
SET vStudentGroupID = GetStudentGroup(pStudentID, 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.StudentID = pStudentID
LIMIT 1;
ELSE # bind stand alone student ;(
INSERT INTO `disciplines_students`
(DisciplineID, StudentID, Type)
VALUES (pDisciplineID, pStudentID, 'attach')
ON DUPLICATE KEY UPDATE
disciplines_students.Type = 'attach';
END IF;
RETURN LogBind(pDisciplineID, pTeacherID, pStudentID, 'attach', 'student');
END //
DROP FUNCTION IF EXISTS UnbindGroup//
CREATE FUNCTION `UnbindGroup` (
`pTeacherID` INT,
`pDisciplineID` INT,
`pGroupID` INT
) RETURNS int(11)
NO SQL
BEGIN
DECLARE vSemesterID INT DEFAULT -1;
# todo: move to php layer
IF 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;
SET vSemesterID = GetDisciplineProperty(pDisciplineID, 'semester');
# 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_groups.StudentID
FROM `students_groups`
WHERE students_groups.GroupID = pGroupID AND
students_groups.SemesterID = vSemesterID
);
RETURN LogBind(pDisciplineID, pTeacherID, pGroupID, 'detach', 'group');
END //
DROP FUNCTION IF EXISTS UnbindStudent//
CREATE FUNCTION `UnbindStudent` (
`pTeacherID` INT,
`pDisciplineID` INT,
`pStudentID` 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 NOT InternalIsTeacherBound(pTeacherID, pDisciplineID) THEN
RETURN -1;
END IF;
SET vSemesterID = GetDisciplineProperty(pDisciplineID, 'semester');
SET vStudentGroupID = GetStudentGroup(pStudentID, 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, StudentID, Type)
VALUES (pDisciplineID, pStudentID, 'detach')
ON DUPLICATE KEY UPDATE disciplines_students.Type='detach';
ELSE
DELETE FROM `disciplines_students`
WHERE disciplines_students.DisciplineID = pDisciplineID AND
disciplines_students.StudentID = pStudentID
LIMIT 1;
END IF;
IF NOT InternalIsTeacherAuthor(pTeacherID, pDisciplineID) OR
InternalIsMapLocked(pDisciplineID)
THEN
RETURN -1;
END IF;
RETURN LogBind(pDisciplineID, pTeacherID, pStudentID, '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 UnbindTeacher//
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 DelegateDiscipline//
DROP FUNCTION IF EXISTS Discipline_Delegate//
CREATE FUNCTION `Discipline_Delegate` (
`pDisciplineID` INT,
`pNewAuthorID` INT
) RETURNS int(11)
NO SQL
BEGIN
DECLARE vTemp INT DEFAULT 0;
DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;
SET vTemp = Discipline_BindTeacher(pDisciplineID, pNewAuthorID);
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//
CREATE FUNCTION `ClearDiscipline` (
`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);
# unlock discipline
UPDATE `disciplines`
SET disciplines.IsLocked = 0
WHERE disciplines.ID = pDisciplineID
LIMIT 1;
RETURN ROW_COUNT()-1;
END //
DROP FUNCTION IF EXISTS DeleteDiscipline//
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)
NO SQL
BEGIN
DECLARE vRes INT DEFAULT 0;
SELECT COUNT(rating_table.StudentID) INTO vRes
FROM `view_roadmap`
LEFT JOIN `rating_table` ON rating_table.SubmoduleID = view_roadmap.SubmoduleID
WHERE view_roadmap.DisciplineID = pDisciplineID;
RETURN vRes;
END //
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
UPDATE `modules`
SET modules.Name = pName
WHERE modules.ID = pModuleID AND
modules.Type = 'regular' AND
NOT InternalIsMapLocked(modules.DisciplineID)
LIMIT 1;
RETURN ROW_COUNT();
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
SELECT MAX(modules.OrderNum)+1 INTO vOrderNum
FROM `modules`
WHERE modules.DisciplineID = pDisciplineID AND modules.Type = 'regular'
LIMIT 1;
IF vOrderNum IS NULL THEN # TODO: check unreachable code
SET vOrderNum = 1;
END IF;
INSERT INTO `modules`
(Name, OrderNum, DisciplineID )
VALUES (pName, vOrderNum, 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 vModule, vChecker INT DEFAULT -1;
DECLARE vIsExamExists BOOLEAN;
IF NOT InternalIsTeacherAuthor(pTeacherID, pDisciplineID) OR
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
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;
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
SELECT disciplines.ID INTO vDisciplineID
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 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;
# get bonus module ID
-- todo: extract method
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
# 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 `submodules`
INNER JOIN `modules` ON submodules.ModuleID = modules.ID
INNER JOIN `disciplines` ON disciplines.ID = modules.DisciplineID
WHERE disciplines.AuthorID = pTeacherID AND
submodules.ID = pSubmoduleID
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 `submodules`
INNER JOIN `modules` ON submodules.ModuleID = modules.ID
INNER JOIN `disciplines` ON disciplines.ID = modules.DisciplineID
WHERE disciplines.AuthorID = pTeacherID AND
submodules.ID = pSubmoduleID
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 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 vOrderNum, vIsLocked INT DEFAULT -1;
DECLARE vDescription VARCHAR(200) CHARSET utf8 DEFAULT pDescription;
# DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;
# check author and discipline lock
SELECT disciplines.IsLocked INTO vIsLocked
FROM `modules`
INNER JOIN `disciplines` ON disciplines.ID = modules.DisciplineID
WHERE disciplines.AuthorID = pTeacherID AND
modules.ID = pModuleID
LIMIT 1;
IF vIsLocked != 0 THEN
RETURN -2;
END IF;
# get free order
SET vOrderNum = 0;
SELECT MAX(submodules.OrderNum) INTO vOrderNum
FROM `submodules`
WHERE submodules.ModuleID = pModuleID
LIMIT 1;
IF vOrderNum IS NULL THEN
SET vOrderNum = 0;
END IF;
SET vOrderNum = vOrderNum + 1;
# insert submodule
IF vDescription = '' THEN
SET vDescription = NULL;
END IF;
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 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
# -------------------------------------------------------------------------------------------
# Вычисление максимального балла для submodule
DROP FUNCTION IF EXISTS CalculateMaxRateForExtra//
CREATE FUNCTION `CalculateMaxRateForExtra` (
`pDisciplineID` INT,
`pStudentID` INT
) RETURNS int(11)
NO SQL
BEGIN
DECLARE vExamType enum('exam', '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.StudentID = pStudentID AND
rating_table.SubmoduleID = view_roadmap.SubmoduleID
WHERE view_roadmap.DisciplineID = pDisciplineID
LIMIT 1;
RETURN vLim - 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 vDisciplineID, vMaxRate, vModuleType, vSemesterID 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`
(StudentID, SubmoduleID, TeacherID, Rate, Action )
VALUES (pStudentID, pSubmoduleID, pTeacherID, pRate, 'delete');
# TODO: extract method log rate
DELETE FROM `rating_table`
WHERE rating_table.StudentID = pStudentID AND
rating_table.SubmoduleID = pSubmoduleID
LIMIT 1;
RETURN ROW_COUNT()-1;
END IF;
SET vIsOver = TRUE;
SELECT disciplines.ID,
disciplines.SemesterID,
disciplines.IsLocked,
disciplines.Milestone,
submodules.IsUsed,
submodules.maxRate,
modules.Type
INTO vDisciplineID, vSemesterID, 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, pStudentID);
END IF;
# 1) check rights
# 2) check, you can't rate regular and bonus after milestone
# 3) check, max rate exceeding
IF NOT InternalIsStudentAttached(pStudentID, vDisciplineID, vSemesterID) OR
NOT InternalIsTeacherBound(pTeacherID, vDisciplineID) OR
pRate > vMaxRate OR
(vIsOver AND (vModuleType = 1 OR vModuleType = 3)) # 1 - regular, 3 - bonus
THEN
RETURN -2;
END IF;
# add rate, or update old
SET @tmp = 0;
INSERT INTO `rating_table`
(StudentID, TeacherID, SubmoduleID, Rate, Date)
VALUES ( pStudentID, pTeacherID, pSubmoduleID, pRate, CURDATE())
ON DUPLICATE KEY UPDATE
rating_table.TeacherID = (@tmp := pTeacherID),
rating_table.Rate = pRate,
rating_table.Date = CURDATE();
# log rate
INSERT INTO `logs_rating`
(StudentID, SubmoduleID, TeacherID, Rate, Action )
VALUES (pStudentID, pSubmoduleID, pTeacherID, pRate,
CASE WHEN @tmp > 0 THEN 'add' ELSE 'change' END);
# 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
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//
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//
# -------------------------------------------------------------------------------------------
# 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
NO SQL
BEGIN
DECLARE vUserFullName VARCHAR(255) charset utf8;
DECLARE vChecker INT DEFAULT -1;
# try to find student with that account id
SELECT students.ID, CONCAT(students.LastName,' ',students.FirstName,' ',students.SecondName)
INTO vChecker, vUserFullName
FROM `students`
WHERE students.AccountID = pAccountID
LIMIT 1;
IF vChecker <= 0 THEN # try to find teacher with that account id
SELECT teachers.ID, CONCAT(teachers.LastName,' ',teachers.FirstName,' ',teachers.SecondName)
INTO vChecker, vUserFullName
FROM `teachers`
WHERE teachers.AccountID = pAccountID
LIMIT 1;
IF vChecker <= 0 THEN
RETURN '';
END IF;
END IF;
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//
DROP FUNCTION IF EXISTS SaveSession //
CREATE FUNCTION SaveSession
( pSessionID VARCHAR(40),
pUserLogin VARCHAR(50) CHARSET utf8,
pUserPassword VARCHAR(64)
) RETURNS INT(11)
NO SQL
BEGIN
INSERT INTO sessions (SessionID, Login, Password)
VALUES(pSessionID, pUserLogin, pUserPassword);
RETURN LAST_INSERT_ID();
END //
DELIMITER ;