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 PROCEDURE IF EXISTS Departments_LoadAll//
DROP PROCEDURE IF EXISTS GetSession //
DROP PROCEDURE IF EXISTS GetRatesExam//
DROP FUNCTION IF EXISTS getStudentsForDisciplineT//
CREATE FUNCTION getStudentsForDisciplineT (pDisciplineID INT) RETURNS INT(11)
NO SQL
BEGIN
DECLARE vSemesterID INT DEFAULT -1;
SET vSemesterID = GetDisciplineProperty(pDisciplineID, 'semester');
CREATE TEMPORARY TABLE tDisciplineStudents AS (
SELECT st.StudentID,
COALESCE(st.Type) AS 'Type'
FROM (
SELECT disciplines_students.StudentID,
disciplines_students.Type
FROM disciplines_students
WHERE disciplines_students.DisciplineID = pDisciplineID
UNION
SELECT students_groups.StudentID,
NULL AS 'Type'
FROM disciplines_groups
LEFT JOIN students_groups ON students_groups.SemesterID = vSemesterID AND
students_groups.GroupID = disciplines_groups.GroupID AND
students_groups.State <= 'outlet'
WHERE disciplines_groups.DisciplineID = pDisciplineID
) as st
GROUP BY st.StudentID
);
RETURN 0;
END //
DROP FUNCTION IF EXISTS getDisciplinesForStudentT//
CREATE FUNCTION getDisciplinesForStudentT(pStudentID INT, pSemesterID INT) RETURNS INT(11)
NO SQL
BEGIN
DECLARE vStudentGroup INT DEFAULT -1;
SET vStudentGroup = GetStudentGroup(pStudentID, pSemesterID);
CREATE TEMPORARY TABLE IF NOT EXISTS tStudentDisciplines AS (
SELECT disc2.DisciplineID
FROM (
SELECT disc1.DisciplineID,
COALESCE(disc1.Type) AS 'Type'
FROM (
SELECT disciplines_students.DisciplineID,
disciplines_students.Type
FROM disciplines_students
WHERE disciplines_students.StudentID = pStudentID
UNION
SELECT disciplines_groups.DisciplineID,
NULL AS 'Type'
FROM disciplines_groups
WHERE disciplines_groups.GroupID = vStudentGroup
) AS disc1
INNER JOIN disciplines ON disciplines.ID = disc1.DisciplineID
WHERE disciplines.SemesterID = pSemesterID
GROUP BY disc1.DisciplineID
) AS disc2
WHERE NOT disc2.Type <=> 'detach'
);
RETURN 0;
END //
# -------------------------------------------------------------------------------------------
# Label: semesters
# -------------------------------------------------------------------------------------------
DROP PROCEDURE IF EXISTS GetSemestersInfo//
CREATE PROCEDURE GetSemestersInfo (IN pSemesterID INT)
READS SQL DATA
SELECT semesters.* -- ID, Year, Num
FROM semesters
WHERE IF(pSemesterID != 0, semesters.ID = pSemesterID, TRUE)
ORDER BY semesters.ID DESC //
# -------------------------------------------------------------------------------------------
# Label: faculties
# -------------------------------------------------------------------------------------------
DROP PROCEDURE IF EXISTS GetFaculties//
CREATE PROCEDURE GetFaculties ()
READS SQL DATA
SELECT faculties.* -- ID, Abbr, Name
FROM faculties
ORDER BY faculties.Name ASC //
# -------------------------------------------------------------------------------------------
# Label: departments
# -------------------------------------------------------------------------------------------
DROP PROCEDURE IF EXISTS GetDepartments//
CREATE PROCEDURE GetDepartments (IN pFacultyID INT)
READS SQL DATA
BEGIN
IF pFacultyID <=> 0 THEN
SELECT departments.* FROM departments
ORDER BY departments.Name ASC;
ELSE
SELECT departments.* -- ID, Name, FacultyID
FROM departments
WHERE departments.FacultyID = pFacultyID
ORDER BY departments.Name ASC;
END IF;
END //
# -------------------------------------------------------------------------------------------
# Label: specializations
# -------------------------------------------------------------------------------------------
DROP PROCEDURE IF EXISTS GetSpecializations//
CREATE PROCEDURE GetSpecializations (IN pFacultyID INT)
READS SQL DATA
SELECT specializations.* -- ID, Name, Abbr, FacultyID
FROM specializations
WHERE specializations.FacultyID = pFacultyID
ORDER BY subjects.Name ASC //
# -------------------------------------------------------------------------------------------
# Label: job positions
# -------------------------------------------------------------------------------------------
DROP PROCEDURE IF EXISTS GetJobPositions//
CREATE PROCEDURE GetJobPositions ()
READS SQL DATA
SELECT job_positions.* -- ID, Name
FROM job_positions
ORDER BY job_positions.Name //
# -------------------------------------------------------------------------------------------
# Label: grades
# -------------------------------------------------------------------------------------------
DROP PROCEDURE IF EXISTS GetGrades//
CREATE PROCEDURE GetGrades ()
READS SQL DATA
SELECT grades.* -- ID, Num, Degree
FROM grades
ORDER BY grades.ID //
# -------------------------------------------------------------------------------------------
# Label: study groups
# -------------------------------------------------------------------------------------------
DROP PROCEDURE IF EXISTS GetGroups//
CREATE PROCEDURE GetGroups (
IN pGradeID INT,
IN pFacultyID INT,
IN pSemesterID INT)
READS SQL DATA
BEGIN
SELECT view_groups.GroupID AS 'ID',
view_groups.GroupNum,
view_groups.SpecID,
view_groups.SpecName,
view_groups.SpecAbbr
FROM view_groups
INNER JOIN semesters ON semesters.ID = pSemesterID
WHERE view_groups.GradeID = pGradeID AND
view_groups.FacultyID = pFacultyID AND
view_groups.Year = semesters.Year
ORDER BY view_groups.GroupNum ASC;
END //
# get all groups, include attached student's groups
DROP PROCEDURE IF EXISTS GetGroupsForDisciplineAll//
CREATE PROCEDURE GetGroupsForDisciplineAll (IN pDisciplineID INT)
BEGIN
DECLARE vSemesterID, vYear INT DEFAULT -1;
SET vSemesterID = GetDisciplineProperty(pDisciplineID, 'semester');
SET vYear = (SELECT Year from semesters where semesters.ID = vSemesterID);
# general + attached
CREATE TEMPORARY TABLE IF NOT EXISTS tGroup AS (
SELECT tGroup1.GroupID
FROM (
SELECT students_groups.GroupID
FROM disciplines_students
INNER JOIN students_groups ON students_groups.StudentID = disciplines_students.StudentID AND
students_groups.SemesterID = vSemesterID AND
students_groups.State <= 'outlet'
WHERE disciplines_students.DisciplineID = pDisciplineID AND
disciplines_students.Type = 'attach'
UNION
SELECT disciplines_groups.GroupID
FROM disciplines_groups
WHERE disciplines_groups.DisciplineID = pDisciplineID
) tGroup1
);
SELECT view_groups.GroupID AS 'ID',
view_groups.GroupNum,
view_groups.GradeID,
view_groups.GradeNum,
view_groups.Degree,
view_groups.SpecID,
view_groups.SpecName,
view_groups.SpecAbbr
FROM tGroup
INNER JOIN view_groups ON tGroup.GroupID = view_groups.GroupID AND view_groups.Year = vYear
ORDER BY view_groups.GradeID ASC, view_groups.GroupID ASC;
END //
# get all general study groups, that takes this course
DROP PROCEDURE IF EXISTS GetGroupsForDiscipline//
CREATE PROCEDURE GetGroupsForDiscipline (IN pDisciplineID INT)
READS SQL DATA
BEGIN
SELECT view_groups.GroupID AS 'ID',
view_groups.GroupNum,
view_groups.GradeID,
view_groups.GradeNum,
view_groups.Degree,
view_groups.SpecID,
view_groups.SpecName,
view_groups.SpecAbbr
FROM disciplines_groups
INNER JOIN disciplines ON disciplines_groups.DisciplineID = disciplines.ID
INNER JOIN semesters ON disciplines.SemesterID = semesters.ID
INNER JOIN view_groups ON disciplines_groups.GroupID = view_groups.GroupID
WHERE disciplines_groups.DisciplineID = pDisciplineID
AND view_groups.Year = semesters.Year
ORDER BY view_groups.GradeID ASC, view_groups.GroupID ASC;
END //
# -------------------------------------------------------------------------------------------
# Label: subjects
# -------------------------------------------------------------------------------------------
DROP PROCEDURE IF EXISTS GetSubjects//
CREATE PROCEDURE GetSubjects (IN pFacultyID INT)
READS SQL DATA
BEGIN
SELECT subjects.ID,
subjects.Name AS 'Title',
subjects.Abbr
FROM subjects_faculties
INNER JOIN subjects ON subjects_faculties.SubjectID = subjects.ID
WHERE subjects_faculties.FacultyID = pFacultyID
ORDER BY subjects.Name ASC;
END //
# -------------------------------------------------------------------------------------------
# Label: accounts
# -------------------------------------------------------------------------------------------
DROP PROCEDURE IF EXISTS GetFullInfo//
CREATE PROCEDURE GetFullInfo (
IN pUserID INT,
IN pSemesterID INT)
READS SQL DATA
BEGIN
DECLARE vAccountType enum('student', 'teacher');
SELECT user_roles.Type INTO vAccountType
FROM accounts
INNER JOIN user_roles ON accounts.UserRoleID = user_roles.ID
WHERE accounts.ID = pUserID
LIMIT 1;
# type 1: student
# 2: teacher
IF vAccountType = 'student' THEN
SELECT
# personal info
students.LastName,
students.FirstName,
students.SecondName,
students.ID as 'StudentID',
# group info
-- GradeID, GradeNum, GroupID, GroupNum, GroupName, Degree,
-- SpecID, SpecName, SpecAbbr, FacultyID, FacultyName, FacultyAbbr,
view_groups_reduced.*,
students_groups.SemesterID,
# account info
accounts.ID,
accounts.Login,
accounts.EMail,
user_roles.Type,
user_roles.RoleName AS 'Role',
user_roles.Mark AS 'RoleMark',
accounts.IsEnabled,
accounts.ActivationCode AS 'Code',
accounts.UserAgent
FROM students
INNER JOIN accounts ON accounts.ID = students.AccountID
INNER JOIN user_roles ON user_roles.ID = accounts.UserRoleID
LEFT JOIN students_groups ON students.ID = students_groups.StudentID
LEFT JOIN view_groups_reduced ON view_groups_reduced.GroupID = students_groups.GroupID
WHERE students.AccountID = pUserID
ORDER BY students_groups.SemesterID <=> pSemesterID DESC
LIMIT 1;
ELSE
SELECT # teacher info
view_teachers.LastName,
view_teachers.FirstName,
view_teachers.SecondName,
view_teachers.TeacherID,
view_teachers.DepID,
view_teachers.DepName,
view_teachers.JobPositionName,
view_teachers.FacultyID,
view_teachers.FacultyName,
view_teachers.FacultyAbbr,
# account info
accounts.ID,
accounts.Login,
accounts.EMail,
user_roles.Type,
user_roles.RoleName AS 'Role',
user_roles.Mark AS 'RoleMark',
accounts.IsEnabled,
accounts.ActivationCode AS 'Code',
accounts.UserAgent
FROM view_teachers
INNER JOIN accounts ON accounts.ID = view_teachers.AccountID
INNER JOIN user_roles ON user_roles.ID = accounts.UserRoleID
WHERE view_teachers.AccountID = pUserID
LIMIT 1;
END IF;
END //
# -------------------------------------------------------------------------------------------
# Label: teachers
# -------------------------------------------------------------------------------------------
DROP PROCEDURE IF EXISTS GetTeachers//
CREATE PROCEDURE GetTeachers (
IN pFacultyID INT,
IN pDepartmentID INT)
READS SQL DATA
BEGIN
SELECT view_teachers.TeacherID AS 'ID',
view_teachers.LastName,
view_teachers.FirstName,
view_teachers.SecondName,
view_teachers.AccountID,
view_teachers.JobPositionName,
view_teachers.DepID,
view_teachers.DepName
FROM view_teachers
WHERE
CASE WHEN pFacultyID != 0 THEN view_teachers.FacultyID = pFacultyID ELSE TRUE END AND
CASE WHEN pDepartmentID != 0 THEN view_teachers.DepID = pDepartmentID ELSE TRUE END
ORDER BY view_teachers.LastName ASC,
view_teachers.FirstName ASC;
END //
# get teachers, that teach course
DROP PROCEDURE IF EXISTS GetTeachersForDiscipline//
CREATE PROCEDURE GetTeachersForDiscipline(IN pDisciplineID INT)
READS SQL DATA
BEGIN
DECLARE vAuthorID INT DEFAULT -1;
SET vAuthorID = GetDisciplineProperty(pDisciplineID, 'author');
SELECT view_teachers.TeacherID AS 'ID',
view_teachers.LastName,
view_teachers.FirstName,
view_teachers.SecondName,
view_teachers.JobPositionID,
view_teachers.JobPositionName,
view_teachers.DepID,
view_teachers.DepName,
view_teachers.FacultyID,
view_teachers.FacultyAbbr,
( view_teachers.TeacherID = vAuthorID ) AS 'IsAuthor'
FROM disciplines_teachers
INNER JOIN view_teachers ON view_teachers.TeacherID = disciplines_teachers.TeacherID
WHERE disciplines_teachers.DisciplineID = pDisciplineID
ORDER BY view_teachers.TeacherID = vAuthorID DESC,
view_teachers.LastName ASC,
view_teachers.FirstName ASC;
END //
DROP PROCEDURE IF EXISTS GetTeachersListForStudent//
CREATE PROCEDURE GetTeachersListForStudent (
IN pStudentID INT,
IN pSemesterID INT,
IN pLoadAll INT)
BEGIN
DECLARE vRes, vStudentGroup INT DEFAULT -1;
SET vStudentGroup = GetStudentGroup(pStudentID, pSemesterID);
DROP TABLE IF EXISTS tStudentDisciplines;
SET vRes = getDisciplinesForStudentT(pStudentID, pSemesterID);
SELECT tStudentDisciplines.DisciplineID,
teachers.ID AS 'TeacherID',
teachers.LastName,
teachers.FirstName,
teachers.SecondName
FROM tStudentDisciplines
INNER JOIN disciplines ON disciplines.ID = tStudentDisciplines.DisciplineID
LEFT JOIN disciplines_teachers ON disciplines_teachers.DisciplineID = tStudentDisciplines.DisciplineID
INNER JOIN teachers ON teachers.ID = disciplines_teachers.TeacherID
WHERE pLoadAll OR disciplines.Subtype IS NULL
ORDER BY tStudentDisciplines.DisciplineID ASC,
teachers.LastName ASC,
teachers.FirstName ASC;
END //
# get teachers, that don't teach course
DROP PROCEDURE IF EXISTS SearchTeachers//
CREATE PROCEDURE SearchTeachers (
IN pFacultyID INT,
IN pDepartmentID INT,
IN pWord1 VARCHAR(100) CHARSET utf8,
IN pWord2 VARCHAR(100) CHARSET utf8,
IN pWord3 VARCHAR(100) CHARSET utf8,
IN pDisciplineID INT)
BEGIN
DECLARE vWord1, vWord2, vWord3 VARCHAR(102) CHARSET utf8;
DECLARE vAuthorID INT DEFAULT -1;
SET vWord1 = CONCAT('%', pWord1, '%');
SET vWord2 = CONCAT('%', pWord2, '%');
SET vWord3 = CONCAT('%', pWord3, '%');
SET vAuthorID = GetDisciplineProperty(pDisciplineID, 'author');
DROP TABLE IF EXISTS tDiscTeachers;
CREATE TEMPORARY TABLE tDiscTeachers AS (
SELECT disciplines_teachers.TeacherID
FROM disciplines_teachers
WHERE disciplines_teachers.DisciplineID <=> pDisciplineID
);
SELECT tTeachers.TeacherID AS 'ID',
tTeachers.LastName,
tTeachers.FirstName,
tTeachers.SecondName,
tTeachers.JobPositionName,
tTeachers.DepID,
tTeachers.DepName,
( tTeachers.TeacherID = vAuthorID ) AS 'IsAuthor'
FROM view_teachers AS tTeachers
WHERE tTeachers.FacultyID = pFacultyID AND
CASE WHEN pDepartmentID != 0 THEN tTeachers.DepID = pDepartmentID ELSE TRUE END AND
NOT EXISTS (
SELECT * FROM tDiscTeachers
WHERE tDiscTeachers.TeacherID = tTeachers.TeacherID
LIMIT 1
) AND
CASE WHEN pWord1 != '' AND
(@full := CONCAT(tTeachers.LastName, ' ', tTeachers.FirstName, ' ', tTeachers.SecondName)) != '' THEN
@full LIKE vWord1 AND
@full LIKE vWord2 AND
@full LIKE vWord3
ELSE TRUE
END
ORDER BY tTeachers.FacultyID ASC,
tTeachers.DepName ASC,
tTeachers.LastName ASC,
tTeachers.FirstName ASC;
END //
# -------------------------------------------------------------------------------------------
# Label: students
# -------------------------------------------------------------------------------------------
DROP PROCEDURE IF EXISTS GetStudents//
CREATE PROCEDURE GetStudents (
IN pFacultyID INT,
IN pGradeID INT,
IN pGroupID INT,
IN pSemesterID INT,
IN pWord1 VARCHAR(100) CHARSET utf8,
IN pWord2 VARCHAR(100) CHARSET utf8,
IN pWord3 VARCHAR(100) CHARSET utf8)
BEGIN
DECLARE vWord1, vWord2, vWord3 VARCHAR(102) CHARSET utf8;
SET vWord1 = CONCAT('%', pWord1, '%');
SET vWord2 = CONCAT('%', pWord2, '%');
SET vWord3 = CONCAT('%', pWord3, '%');
SELECT view_students.StudentID AS 'ID',
view_students.LastName,
view_students.FirstName,
view_students.SecondName,
view_students.AccountID,
view_students.GradeID,
view_students.GradeNum,
view_students.Degree,
view_students.GroupID,
view_students.GroupNum
FROM view_students
WHERE view_students.SemesterID = pSemesterID AND
CASE WHEN pFacultyID != 0 THEN view_students.FacultyID = pFacultyID ELSE TRUE END AND
CASE WHEN pGradeID != 0 THEN view_students.GradeID = pGradeID ELSE TRUE END AND
CASE WHEN pGroupID != 0 THEN view_students.GroupID = pGroupID ELSE TRUE END AND
CASE WHEN pWord1 != '' AND
(@full := CONCAT(view_students.LastName, ' ', view_students.FirstName, ' ', view_students.SecondName)) != '' THEN
@full LIKE vWord1 AND
@full LIKE vWord2 AND
@full LIKE vWord3
ELSE TRUE
END
ORDER BY view_students.LastName ASC,
view_students.FirstName ASC;
END //
# not in general groups, not attached
DROP PROCEDURE IF EXISTS SearchStudents//
CREATE PROCEDURE SearchStudents (
IN pGradeID INT,
IN pGroupID INT,
IN pFacultyID INT,
IN pFullName VARCHAR(100) CHARSET utf8,
IN pDisciplineID INT)
BEGIN
DECLARE vSemesterID, vRes INT DEFAULT -1;
DECLARE vFullNameReg VARCHAR(102) CHARSET utf8;
SET vSemesterID = GetDisciplineProperty(pDisciplineID, 'semester');
SET vFullNameReg = CONCAT('%', pFullName, '%');
DROP TABLE IF EXISTS tDisciplineStudents;
SET vRes = getStudentsForDisciplineT(pDisciplineID);
SELECT view_students.StudentID AS 'ID',
view_students.LastName,
view_students.FirstName,
view_students.SecondName,
view_students.GradeID,
view_students.GradeNum,
view_students.Degree,
view_students.GroupID,
view_students.GroupNum
FROM view_students
LEFT JOIN tDisciplineStudents ON tDisciplineStudents.StudentID = view_students.StudentID
WHERE view_students.SemesterID = vSemesterID AND
view_students.FacultyID = pFacultyID AND
view_students.GradeID = pGradeID AND
(pGroupID = 0 OR view_students.GroupID = pGroupID) AND
tDisciplineStudents.StudentID IS NULL AND
CASE WHEN pFullName != '' THEN
CONCAT(view_students.LastName, ' ', view_students.FirstName, ' ', view_students.SecondName) LIKE vFullNameReg
ELSE TRUE END
ORDER BY view_students.GradeID ASC,
view_students.GroupID ASC,
view_students.LastName ASC,
view_students.FirstName ASC;
END //
# in general groups, attached or detached
DROP PROCEDURE IF EXISTS GetStudentsForDiscipline//
CREATE PROCEDURE GetStudentsForDiscipline (
IN pDisciplineID INT)
BEGIN
DECLARE vSemesterID, vRes INT DEFAULT -1;
SET vSemesterID = GetDisciplineProperty(pDisciplineID, 'semester');
DROP TABLE IF EXISTS tDisciplineStudents;
SET vRes = getStudentsForDisciplineT(pDisciplineID);
SELECT view_students.StudentID AS 'ID',
view_students.LastName,
view_students.FirstName,
view_students.SecondName,
view_students.GradeID,
view_students.GradeNum,
view_students.Degree,
view_students.GroupID,
view_students.GroupNum,
tDisciplineStudents.Type AS 'AttachType'
FROM tDisciplineStudents
INNER JOIN view_students ON view_students.StudentID = tDisciplineStudents.StudentID AND
view_students.SemesterID = vSemesterID
ORDER BY tDisciplineStudents.Type <=> 'attach' ASC,
view_students.GradeID ASC,
view_students.GroupNum ASC,
view_students.LastName ASC,
view_students.FirstName ASC;
END //
# in general groups + attached
DROP PROCEDURE IF EXISTS GetStudentsForRating//
CREATE PROCEDURE GetStudentsForRating (
IN pDisciplineID INT)
BEGIN
DECLARE vSemesterID, vRes INT DEFAULT -1;
SET vSemesterID = GetDisciplineProperty(pDisciplineID, 'semester');
DROP TABLE IF EXISTS tDisciplineStudents;
SET vRes = getStudentsForDisciplineT(pDisciplineID);
SELECT view_students.StudentID AS 'ID',
view_students.LastName,
view_students.FirstName,
view_students.SecondName,
view_students.GradeID,
view_students.GradeNum,
view_students.Degree,
view_students.GroupID,
view_students.GroupNum,
( tDisciplineStudents.Type IS NOT NULL ) AS 'IsAttached'
FROM tDisciplineStudents
INNER JOIN view_students ON view_students.StudentID = tDisciplineStudents.StudentID AND
view_students.SemesterID = vSemesterID
WHERE NOT tDisciplineStudents.Type <=> 'detach'
ORDER BY view_students.GradeID ASC,
view_students.GroupNum ASC,
view_students.LastName ASC,
view_students.FirstName ASC;
END //
# -------------------------------------------------------------------------------------------
# Label: disciplines
# -------------------------------------------------------------------------------------------
DROP PROCEDURE IF EXISTS GetDisciplineInfo//
DROP PROCEDURE IF EXISTS Discipline_GetInfo//
CREATE PROCEDURE Discipline_GetInfo (IN pDisciplineID INT)
READS SQL DATA
BEGIN
DECLARE vIsBonus BOOLEAN;
SET vIsBonus = EXISTS(
SELECT * FROM modules
WHERE modules.DisciplineID = pDisciplineID AND
modules.Type = 'bonus'
LIMIT 1
);
SELECT
view_disciplines.DisciplineID AS 'ID',
view_disciplines.AuthorID,
view_disciplines.GradeID,
view_disciplines.GradeNum,
view_disciplines.Degree,
view_disciplines.ExamType AS 'Type',
view_disciplines.LectureCount AS 'Lectures',
view_disciplines.PracticeCount AS 'Practice',
view_disciplines.LabCount AS 'Labs',
view_disciplines.SemesterID,
view_disciplines.SubjectID,
view_disciplines.SubjectName,
view_disciplines.SubjectAbbr,
view_disciplines.FacultyID,
view_disciplines.FacultyName,
view_disciplines.IsLocked,
view_disciplines.Milestone,
view_disciplines.Subtype,
view_disciplines.CompoundDiscID,
(view_disciplines.MaxRate = 100) AS 'IsMapCreated',
vIsBonus AS 'IsBonus',
semesters.Num AS 'semesterNum', # TODO: Camelize
semesters.Year AS 'semesterYear'
FROM view_disciplines
INNER JOIN semesters ON semesters.ID = view_disciplines.SemesterID
WHERE view_disciplines.DisciplineID = pDisciplineID
LIMIT 1;
END //
# это обертка процедуры Discipline_GetInfo для случая, когда передается SubmoduleID
DROP PROCEDURE IF EXISTS Discipline_GetInfoBySubmodule //
CREATE PROCEDURE Discipline_GetInfoBySubmodule (
pSubmoduleID INT
)
READS SQL DATA
BEGIN
DECLARE vDisciplineID INT DEFAULT -1;
SELECT modules.DisciplineID
INTO vDisciplineID
FROM submodules
INNER JOIN modules ON submodules.ModuleID = modules.ID
WHERE submodules.ID = pSubmoduleID
LIMIT 1;
CALL Discipline_GetInfo(vDisciplineID);
END //
# all disciplines for faculty in current semester
DROP PROCEDURE IF EXISTS GetDisciplines//
CREATE PROCEDURE GetDisciplines (
IN pFacultyID INT,
IN pSemesterID INT)
READS SQL DATA
BEGIN
SELECT view_disciplines.DisciplineID AS 'ID',
view_disciplines.SubjectID,
view_disciplines.SubjectName,
view_disciplines.ExamType AS 'Type',
(view_disciplines.MaxRate = 100) AS 'isMapCreated'
FROM view_disciplines
WHERE view_disciplines.SemesterID = pSemesterID AND
view_disciplines.FacultyID = pFacultyID
ORDER BY view_disciplines.SubjectName ASC;
END //
DROP PROCEDURE IF EXISTS GetCompoundDisciplinesForGrade//
CREATE PROCEDURE GetCompoundDisciplinesForGrade (IN pGradeID INT)
READS SQL DATA
BEGIN
SELECT compound_disciplines.ID,
compound_disciplines.Name
FROM compound_disciplines
WHERE compound_disciplines.GradeID = pGradeID;
END //
# processed format of output (after desequentialization)
# { discipline1 {group1, group2, ...}, discipline2 {groupN, ...}, ... }
DROP PROCEDURE IF EXISTS GetDisciplinesForTeacher//
CREATE PROCEDURE GetDisciplinesForTeacher (
IN pTeacherID INT,
IN pSemesterID INT)
READS SQL DATA
BEGIN
SELECT DISTINCT view_disciplines.DisciplineID AS 'ID',
view_disciplines.ExamType AS 'Type',
view_disciplines.Subtype,
view_disciplines.GradeID,
view_disciplines.GradeNum,
view_disciplines.Degree,
view_groups.GroupID,
view_groups.GroupNum,
view_groups.GroupName,
view_disciplines.SubjectID,
view_disciplines.SubjectName,
view_disciplines.AuthorID,
view_disciplines.IsLocked AS 'IsLocked', -- bodging, db schema remembered lowerCase
(view_disciplines.MaxRate = 100) AS 'IsMapCreated'
FROM disciplines_teachers
INNER JOIN view_disciplines ON disciplines_teachers.DisciplineID = view_disciplines.DisciplineID
INNER JOIN semesters ON semesters.ID = view_disciplines.SemesterID
LEFT JOIN disciplines_groups ON disciplines_groups.DisciplineID = disciplines_teachers.DisciplineID
LEFT JOIN view_groups ON view_groups.GroupID = disciplines_groups.GroupID AND view_groups.Year = semesters.Year
WHERE disciplines_teachers.TeacherID = pTeacherID AND view_disciplines.SemesterID = pSemesterID
ORDER BY view_disciplines.GradeID ASC,
view_disciplines.SubjectName ASC,
view_disciplines.DisciplineID ASC,
view_groups.GroupNum ASC;
END //
# get all disciplines, that student take.
DROP PROCEDURE IF EXISTS GetDisciplinesForStudent//
CREATE PROCEDURE GetDisciplinesForStudent (
IN pStudentID INT,
IN pSemesterID INT)
READS SQL DATA
BEGIN
DECLARE vRes INT DEFAULT -1;
DROP TABLE IF EXISTS tStudentDisciplines;
SET vRes = getDisciplinesForStudentT(pStudentID, pSemesterID);
# only Cthulhu knows, what happened here
SELECT view_disciplines.DisciplineID AS 'ID',
view_disciplines.SubjectID,
view_disciplines.SubjectName,
view_disciplines.ExamType AS 'Type',
view_disciplines.Subtype,
teachers.LastName,
teachers.FirstName,
teachers.SecondName,
( tDR.RateExam + tDR.RateMExam ) AS 'Rate',
( tDR.MaxRegularRate + tDR.MaxExamRate ) AS 'MaxCurrentRate'
FROM (
SELECT tRating.DisciplineID,
SUM(
IF( tRating.SubmoduleIsUsed AND tRating.ModuleType <=> 'regular',
tRating.SubmoduleRate, 0)
) AS 'MaxRegularRate',
MAX(
IF( tRating.SubmoduleIsUsed AND tRating.ModuleType <=> 'exam',
tRating.SubmoduleRate, 0)
) AS 'MaxExamRate',
SUM(IF(NOT tRating.ModuleType <=> 'exam', tRating.Rate, 0)) AS 'RateMExam',
MAX(IF(tRating.ModuleType <=> 'exam' AND tRating.Rate IS NOT NULL, tRating.Rate, 0)) AS 'RateExam'
FROM (
SELECT tStudentDisciplines.DisciplineID,
vr.SubmoduleRate,
vr.ModuleType,
rt.Rate,
vr.SubmoduleIsUsed
FROM tStudentDisciplines as tStudentDisciplines
LEFT JOIN view_roadmap AS vr ON vr.DisciplineID = tStudentDisciplines.DisciplineID
LEFT JOIN rating_table AS rt ON rt.StudentID = pStudentID AND
rt.SubmoduleID = vr.SubmoduleID
) AS tRating
GROUP BY tRating.DisciplineID
) AS tDR
INNER JOIN view_disciplines ON view_disciplines.DisciplineID = tDR.DisciplineID
INNER JOIN teachers ON teachers.ID = view_disciplines.AuthorID
ORDER BY view_disciplines.ExamType ASC, view_disciplines.SubjectName ASC;
END //
# get all disciplines for group, including disciplines, where students have attached status
DROP PROCEDURE IF EXISTS GetDisciplinesForGroup//
CREATE PROCEDURE GetDisciplinesForGroup (
IN pGroupID INT,
IN pSemesterID INT)
READS SQL DATA
BEGIN
# Get all attached groups for disc
DROP TABLE IF EXISTS tDisc;
CREATE TEMPORARY TABLE tDisc AS (
SELECT tTemp.DisciplineID
FROM (
SELECT disciplines_groups.DisciplineID
FROM disciplines_groups
WHERE disciplines_groups.GroupID = pGroupID
UNION
SELECT DISTINCT disciplines_students.DisciplineID
FROM students_groups
LEFT JOIN disciplines_students ON disciplines_students.StudentID = students_groups.StudentID
WHERE students_groups.GroupID = pGroupID AND
students_groups.SemesterID = pSemesterID AND
students_groups.State <= 'outlet' AND
disciplines_students.Type <=> 'attach'
) AS tTemp
);
SELECT tDisc.DisciplineID AS 'ID',
view_disciplines.SubjectName,
view_disciplines.Subtype,
view_disciplines.ExamType AS 'Type',
view_disciplines.CompoundDiscID,
view_disciplines.CompoundDiscName,
view_disciplines.CurRate,
view_disciplines.MaxRate
FROM tDisc
INNER JOIN view_disciplines ON tDisc.DisciplineID = view_disciplines.DisciplineID
WHERE view_disciplines.SemesterID = pSemesterID;
END //
# -------------------------------------------------------------------------------------------
# Label: rating
# -------------------------------------------------------------------------------------------
# TODO: merge with GetRatesForGroupByStage
DROP PROCEDURE IF EXISTS GetRatesForGroup//
CREATE PROCEDURE GetRatesForGroup (
IN pDisciplineID INT,
IN pGroupID INT)
BEGIN
DECLARE vInGeneralGroup BOOLEAN DEFAULT FALSE;
DECLARE vSemesterID INT DEFAULT -1;
SET vSemesterID = GetDisciplineProperty(pDisciplineID, 'semester');
SET vInGeneralGroup = EXISTS(
SELECT * FROM disciplines_groups
WHERE disciplines_groups.DisciplineID = pDisciplineID AND
disciplines_groups.GroupID = pGroupID
LIMIT 1
);
DROP TABLE IF EXISTS tStudents;
CREATE TEMPORARY TABLE tStudents AS (
SELECT students_groups.StudentID
FROM students_groups
LEFT JOIN disciplines_students ON disciplines_students.DisciplineID = pDisciplineID AND
disciplines_students.StudentID = students_groups.StudentID
WHERE students_groups.SemesterID = vSemesterID AND
students_groups.State <= 'outlet' AND # actual students
students_groups.GroupID = pGroupID AND
CASE WHEN vInGeneralGroup THEN
NOT disciplines_students.Type <=> 'detach' # not detached
ELSE
disciplines_students.Type <=> 'attach' # is attached
END
);
DROP TABLE IF EXISTS vRoadMap;
CREATE TEMPORARY TABLE vRoadMap AS (
SELECT view_roadmap.SubmoduleID,
view_roadmap.ModuleType AS 'Type'
FROM view_roadmap
WHERE view_roadmap.DisciplineID = pDisciplineID
);
SELECT students.ID,
students.LastName,
students.FirstName,
students.SecondName,
vRates.RateRegular AS 'intermediate',
vRates.RateBonus AS 'bonus',
vRates.RateExam AS 'exam'
FROM (
SELECT tStudents.StudentID,
SUM(IF(vRoadMap.Type = 'regular', rt.Rate, 0)) AS 'RateRegular',
SUM(IF(vRoadMap.Type = 'extra', rt.Rate, 0)) AS 'RateExtra',
SUM(IF(vRoadMap.Type = 'bonus', rt.Rate, 0)) AS 'RateBonus',
MAX(IF(vRoadMap.Type = 'exam', rt.Rate, 0)) AS 'RateExam'
FROM tStudents
CROSS JOIN vRoadMap
LEFT JOIN rating_table as rt ON rt.StudentID = tStudents.StudentID AND
rt.SubmoduleID = vRoadMap.SubmoduleID
GROUP BY tStudents.StudentID
) vRates
INNER JOIN students ON students.ID = vRates.StudentID
ORDER BY CONCAT(students.LastName, students.FirstName, students.SecondName) ASC, students.ID ASC;
END //
# TODO: merge with GetRatesForGroupByStage
DROP PROCEDURE IF EXISTS GetRatesForGroupAll//
CREATE PROCEDURE GetRatesForGroupAll (
IN pGroupID INT,
IN pSemesterID INT)
BEGIN
DROP TABLE IF EXISTS tDisc;
CREATE TEMPORARY TABLE tDisc AS (
SELECT tTemp.DisciplineID
FROM (
SELECT disciplines_groups.DisciplineID
FROM disciplines_groups
WHERE disciplines_groups.GroupID = pGroupID
UNION
SELECT DISTINCT disciplines_students.DisciplineID
FROM students_groups
LEFT JOIN disciplines_students ON disciplines_students.StudentID = students_groups.StudentID
WHERE students_groups.GroupID = pGroupID AND
students_groups.State <= 'outlet' AND
disciplines_students.Type <=> 'attach'
) AS tTemp
);
DROP TABLE IF EXISTS tStudents;
CREATE TEMPORARY TABLE tStudents AS (
SELECT students_groups.StudentID
FROM students_groups
WHERE students_groups.GroupID = pGroupID
and students_groups.SemesterID = pSemesterID
);
DROP TABLE IF EXISTS vRoadMap;
CREATE TEMPORARY TABLE vRoadMap AS (
SELECT tDisc.DisciplineID as 'DisciplineID',
view_roadmap.ModuleType as 'Type',
view_roadmap.SubmoduleID
FROM tDisc
LEFT JOIN view_roadmap ON view_roadmap.DisciplineID = tDisc.DisciplineID
);
SELECT vRates.StudentID as 'StudentID',
vRates.DisciplineID as 'DisciplineID',
disciplines.CompoundDiscID,
vRates.RateRegular AS 'intermediate',
vRates.RateBonus AS 'bonus',
vRates.RateExtra AS 'extra',
vRates.RateExam AS 'exam',
vRates.CntExam AS 'examCnt'
FROM (
SELECT tStudents.StudentID,
vRoadMap.DisciplineID,
SUM(IF(vRoadMap.Type = 'regular', rt.Rate, 0)) AS 'RateRegular',
SUM(IF(vRoadMap.Type = 'extra', rt.Rate, 0)) AS 'RateExtra',
SUM(IF(vRoadMap.Type = 'bonus', rt.Rate, 0)) AS 'RateBonus',
MAX(IF(vRoadMap.Type = 'exam', rt.Rate, 0)) AS 'RateExam',
SUM(IF(vRoadMap.Type = 'exam', 1, 0)) AS 'CntExam'
FROM tStudents
CROSS JOIN vRoadMap
LEFT JOIN rating_table as rt ON rt.StudentID = tStudents.StudentID AND
rt.SubmoduleID = vRoadMap.SubmoduleID
WHERE rt.Rate IS NOT NULL
GROUP BY tStudents.StudentID, vRoadMap.DisciplineID
) vRates
INNER JOIN students ON students.ID = vRates.StudentID
INNER JOIN disciplines ON disciplines.id = vRates.DisciplineID
ORDER BY CONCAT(students.LastName, students.FirstName, students.SecondName) ASC,
vRates.DisciplineID ASC;
END //
DROP PROCEDURE IF EXISTS GetRatesForGroupByStage//
CREATE PROCEDURE GetRatesForGroupByStage (
IN pDisciplineID INT,
IN pGroupID INT,
IN pMilestone INT)
BEGIN
DECLARE vSemesterID, vGroupID INT DEFAULT -1;
DECLARE vInGeneralGroup BOOL DEFAULT FALSE;
SET vSemesterID = GetDisciplineProperty(pDisciplineID, 'semester');
DROP TABLE IF EXISTS tStudents;
CREATE TEMPORARY TABLE tStudents (
StudentID INT NOT NULL
);
# check that group attached to discipline. Otherwise vGroupID = -1;
SET vInGeneralGroup = EXISTS(
SELECT * FROM disciplines_groups
WHERE disciplines_groups.DisciplineID = pDisciplineID AND
disciplines_groups.GroupID = pGroupID
LIMIT 1
);
DROP TABLE IF EXISTS tStudents;
CREATE TEMPORARY TABLE tStudents AS (
SELECT students_groups.StudentID
FROM students_groups
LEFT JOIN disciplines_students ON disciplines_students.DisciplineID = pDisciplineID AND
disciplines_students.StudentID = students_groups.StudentID
WHERE students_groups.SemesterID = vSemesterID AND
students_groups.State <= 'outlet' AND # actual students
students_groups.GroupID = pGroupID AND
CASE WHEN vInGeneralGroup THEN
NOT disciplines_students.Type <=> 'detach' # not detached
ELSE
disciplines_students.Type <=> 'attach' # is attached
END
);
SELECT tRes.*,
students.LastName,
students.FirstName,
students.SecondName
FROM (
SELECT tStudents.StudentID,
SUM(tRate.Rate*(tMap.ModuleType = 'regular')) AS 'Semester',
SUM(tRate.Rate*(tMap.ModuleType = 'bonus')) AS 'Bonus',
SUM(tRate.Rate*(tMap.ModuleType = 'extra')*(tMap.SubmoduleOrderNum < pMilestone)) AS 'Extra',
SUM(tRate.Rate*(tMap.ModuleType = 'extra')*(tMap.SubmoduleOrderNum < pMilestone - 1)) AS 'PreviousExtra',
SUM(tRate.Rate*(tMap.ModuleType = 'exam')*(tMap.SubmoduleOrderNum = pMilestone)) AS 'Exam',
MAX(tRate.Rate*(tMap.ModuleType = 'exam')*(tMap.SubmoduleOrderNum < pMilestone)) AS 'PreviousExam',
MAX(IF(tMap.SubmoduleOrderNum = pMilestone, exam_period_options.TYPE, NULL)) As 'Option',
MAX(IF(exam_period_options.TYPE = 'pass', 1, 0)) As 'AutoPassed'
FROM tStudents
LEFT JOIN view_roadmap AS tMap ON tMap.DisciplineID = pDisciplineID
LEFT JOIN rating_table AS tRate ON tRate.StudentID = tStudents.StudentID AND
tRate.SubmoduleID = tMap.SubmoduleID
LEFT JOIN exam_period_options ON exam_period_options.submoduleID = tMap.SubmoduleID AND
exam_period_options.StudentID = tStudents.StudentID
GROUP BY tStudents.StudentID
) tRes
INNER JOIN students ON students.ID = tRes.StudentID
ORDER BY students.LastName ASC,
students.FirstName ASC,
students.SecondName ASC;
END //
DROP PROCEDURE IF EXISTS GetRatesForDiscipline//
CREATE PROCEDURE GetRatesForDiscipline (
IN pDisciplineID INT)
READS SQL DATA
BEGIN
DECLARE vSemesterID INT DEFAULT -1;
SET vSemesterID = GetDisciplineProperty(pDisciplineID, 'semester');
SELECT view_roadmap.SubmoduleID,
rating_table.StudentID,
rating_table.Rate
FROM view_roadmap
LEFT JOIN rating_table ON rating_table.SubmoduleID = view_roadmap.SubmoduleID
INNER JOIN students_groups ON students_groups.SemesterID = vSemesterID AND
students_groups.StudentID = rating_table.StudentID
WHERE view_roadmap.DisciplineID = pDisciplineID
ORDER BY rating_table.StudentID;
END //
# get rates for student
# returns only last rated exam submodule, or first, if not rated
DROP PROCEDURE IF EXISTS GetRates//
CREATE PROCEDURE GetRates (
IN pStudentID INT,
IN pDisciplineID INT)
READS SQL DATA
BEGIN
DECLARE vExamSubmoduleID INT DEFAULT GetExamRateID(pStudentID, pDisciplineID);
SELECT vr.ModuleID,
vr.ModuleName,
vr.SubmoduleID,
vr.SubmoduleName,
vr.SubmoduleRate AS MaxRate,
vr.SubmoduleType AS SubmoduleControl,
vr.ModuleType,
rt.Rate,
rt.Date,
epo.Type AS ExamPeriodOption
FROM view_roadmap AS vr
LEFT JOIN rating_table AS rt ON vr.SubmoduleID = rt.SubmoduleID AND rt.StudentID = pStudentID
LEFT JOIN exam_period_options AS epo ON epo.SubmoduleID = rt.SubmoduleID AND epo.StudentID = pStudentID
WHERE vr.DisciplineID = pDisciplineID AND ( vr.ModuleType != 'exam' OR vr.SubmoduleID = vExamSubmoduleID )
ORDER BY vr.ModuleType ^ 1 ASC, -- 1, 3, 2, 4 ASC
vr.ModuleOrderNum ASC,
vr.SubmoduleOrderNum ASC;
END //
DROP PROCEDURE IF EXISTS GetRatesAll//
CREATE PROCEDURE GetRatesAll (
IN pStudentID INT,
IN pDisciplineID INT)
READS SQL DATA
BEGIN
SELECT view_roadmap.ModuleID,
view_roadmap.ModuleName,
view_roadmap.SubmoduleID,
view_roadmap.SubmoduleName,
view_roadmap.SubmoduleRate AS 'MaxRate',
view_roadmap.SubmoduleType,
view_roadmap.SubmoduleOrderNum,
rating_table.Rate,
rating_table.Date,
view_roadmap.ModuleType,
exam_period_options.Type As ExamPeriodOption
FROM view_roadmap
LEFT JOIN rating_table ON rating_table.SubmoduleID = view_roadmap.SubmoduleID AND
rating_table.StudentID = pStudentID
LEFT JOIN exam_period_options ON exam_period_options.StudentID = pStudentID AND
exam_period_options.SubmoduleID = view_roadmap.SubmoduleID
WHERE view_roadmap.DisciplineID = pDisciplineID
ORDER BY view_roadmap.ModuleOrderNum ASC,
view_roadmap.SubmoduleOrderNum ASC;
END //
DROP PROCEDURE IF EXISTS GetRatesHistory//
CREATE PROCEDURE GetRatesHistory (IN pDisciplineID INT)
READS SQL DATA
BEGIN
SELECT logs_rating.ID,
logs_rating.Date,
logs_rating.Rate,
students.LastName AS 'StudentLast',
students.FirstName AS 'StudentFirst',
students.SecondName AS 'StudentSecond',
teachers.LastName AS 'TeacherLast',
teachers.FirstName AS 'TeacherFirst',
teachers.SecondName AS 'TeacherSecond',
submodules.Name AS 'SubmoduleName',
modules.Name AS 'ModuleName'
FROM logs_rating
INNER JOIN students ON students.ID = logs_rating.StudentID
INNER JOIN teachers ON teachers.ID = logs_rating.TeacherID
INNER JOIN submodules ON submodules.ID = logs_rating.SubmoduleID
INNER JOIN modules ON modules.ID = submodules.ModuleID
WHERE modules.DisciplineID = pDisciplineID
ORDER BY logs_rating.Date DESC;
END //
DROP PROCEDURE IF EXISTS GetSignInHistory//
CREATE PROCEDURE `GetSignInHistory` (IN `pLimit` INT)
READS SQL DATA
BEGIN
SELECT logs_signin.ID,
logs_signin.Date,
logs_signin.AccountID,
teachers.LastName,
teachers.FirstName,
teachers.SecondName
FROM `logs_signin`
INNER JOIN `teachers` ON teachers.AccountID = logs_signin.AccountID
ORDER BY logs_signin.Date DESC LIMIT pLimit;
END //
DROP PROCEDURE IF EXISTS GetAttestationData//
CREATE PROCEDURE GetAttestationData (
IN pDisciplineID INT,
IN pGroupID INT
) READS SQL DATA
BEGIN
DECLARE vSemesterID INT DEFAULT -1;
SET vSemesterID = GetDisciplineProperty(pDisciplineID, 'semester');
SELECT students.ID AS 'StudentID',
rating_table.Rate As 'Rate',
rating_table.Date As 'Date',
submodules.OrderNum As 'OrderNum',
modules.Type As 'Type'
FROM students
INNER JOIN students_groups ON students_groups.StudentID = students.ID AND
students_groups.SemesterID = vSemesterID AND
students_groups.State <= 'outlet'
LEFT JOIN disciplines_groups ON disciplines_groups.DisciplineID = pDisciplineID AND
disciplines_groups.GroupID = students_groups.GroupID
LEFT JOIN disciplines_students ON disciplines_students.DisciplineID = pDisciplineID AND
disciplines_students.StudentID = students.ID
LEFT JOIN modules ON modules.DisciplineID = pDisciplineID AND
(modules.Type = 'exam' OR modules.Type = 'extra')
LEFT JOIN submodules ON submodules.ModuleID = modules.ID
LEFT JOIN rating_table ON rating_table.SubmoduleID = submodules.ID AND
rating_table.StudentID = students.ID
WHERE students_groups.GroupID = pGroupID AND rating_table.Rate IS NOT NULL AND
((disciplines_students.StudentID IS NOT NULL AND disciplines_students.Type = 'attach')
OR (disciplines_groups.DisciplineID IS NOT NULL AND disciplines_students.Type IS NULL)
)
ORDER BY CONCAT(students.LastName, ' ', students.FirstName, ' ', students.SecondName) ASC,
students.ID ASC,
modules.Type = 'exam' ASC,
submodules.OrderNum ASC;
END //
# -------------------------------------------------------------------------------------------
# Label: disciplines
# Label: roadmaps
# -------------------------------------------------------------------------------------------
# TODO: order hardcode
# TODO: deprecated
DROP PROCEDURE IF EXISTS GetRoadmap//
CREATE PROCEDURE GetRoadmap (
IN pDisciplineID INT,
IN pType enum('exam', 'rate', 'all')
) READS SQL DATA
BEGIN
SELECT view_roadmap.ModuleID,
view_roadmap.ModuleName,
view_roadmap.ModuleType,
view_roadmap.SubmoduleID,
view_roadmap.SubmoduleName,
view_roadmap.SubmoduleRate AS 'MaxRate',
view_roadmap.SubmoduleType
FROM view_roadmap
WHERE view_roadmap.DisciplineID = pDisciplineID AND
CASE pType
WHEN 'exam' THEN view_roadmap.ModuleType = 'exam' OR view_roadmap.ModuleType = 'extra'
WHEN 'rate' THEN view_roadmap.ModuleType != 'exam' OR view_roadmap.SubmoduleOrderNum = 1
ELSE TRUE
END
ORDER BY
CASE pType
WHEN 'exam' THEN InternalOrderModuleTypesForSession(view_roadmap.ModuleType)
WHEN 'rate' THEN view_roadmap.ModuleType ^ 1 # 1, 3, 2, 4 ASC
ELSE TRUE
END ASC,
view_roadmap.ModuleOrderNum ASC,
view_roadmap.SubmoduleOrderNum ASC;
END //
DROP PROCEDURE IF EXISTS Discipline_GetModules//
CREATE PROCEDURE Discipline_GetModules (
IN pDisciplineID INT,
IN pType enum('exam', 'rate', 'all')
) READS SQL DATA
BEGIN
SELECT modules.ID,
modules.Name,
modules.Type
FROM modules
WHERE modules.DisciplineID = pDisciplineID AND
CASE pType
WHEN 'exam' THEN modules.Type = 'exam' OR modules.Type = 'extra'
WHEN 'rate' THEN modules.Type != 'exam'
ELSE TRUE
END
ORDER BY
CASE pType
WHEN 'exam' THEN InternalOrderModuleTypesForSession(modules.Type)
WHEN 'rate' THEN modules.Type ^ 1 # 1, 3, 2, 4 ASC
ELSE TRUE
END ASC,
modules.OrderNum ASC;
END //
DROP PROCEDURE IF EXISTS Discipline_GetSubmodules//
CREATE PROCEDURE Discipline_GetSubmodules (
IN pDisciplineID INT,
IN pType enum('exam', 'rate', 'all')
) READS SQL DATA
BEGIN
SELECT view_roadmap.ModuleID,
view_roadmap.SubmoduleID AS 'ID',
view_roadmap.SubmoduleName AS 'Name',
view_roadmap.SubmoduleRate AS 'Rate',
view_roadmap.SubmoduleType AS 'Type'
FROM view_roadmap
WHERE view_roadmap.DisciplineID = pDisciplineID AND
CASE pType
WHEN 'exam' THEN view_roadmap.ModuleType = 'exam' OR view_roadmap.ModuleType = 'extra'
WHEN 'rate' THEN view_roadmap.ModuleType != 'exam' OR view_roadmap.SubmoduleOrderNum = 1
ELSE TRUE
END
ORDER BY
view_roadmap.ModuleOrderNum ASC,
view_roadmap.SubmoduleOrderNum ASC;
END //
# -------------------------------------------------------------------------------------------
# Label: roles
# -------------------------------------------------------------------------------------------
/*DROP PROCEDURE IF EXISTS GetAccountInfo//
CREATE PROCEDURE `GetAccountInfo`
(IN `pID` INT)
NO SQL
BEGIN
SELECT
view_teachers.LastName AS 'LastName',
view_teachers.FirstName AS 'FirstName',
view_teachers.SecondName AS 'SecondName',
view_teachers.FacultyID AS 'facultyID',
user_roles.ID AS 'roleID',
user_roles.Type AS 'roleType',
user_roles.RoleName AS 'roleName'
FROM `view_teachers`
INNER JOIN `accounts` ON accounts.ID = view_teachers.AccountID
INNER JOIN `user_roles` ON user_roles.ID = accounts.UserRoleID
WHERE view_teachers.AccountID = pID
LIMIT 1;
END //*/
DROP PROCEDURE IF EXISTS GetRoles//
CREATE PROCEDURE `GetRoles` ()
NO SQL
BEGIN
SELECT
user_roles.ID AS 'ID',
user_roles.Mark AS 'RoleMark',
user_roles.RoleName AS 'RoleName'
FROM `user_roles`
WHERE user_roles.Type='teacher';
END //
# -------------------------------------------------------------------------------------------
# Label: recovery
# -------------------------------------------------------------------------------------------
DROP PROCEDURE IF EXISTS GetRecoveryInfoByToken//
CREATE PROCEDURE GetRecoveryInfoByToken(IN pToken VARCHAR(100) CHARSET utf8)
READS SQL DATA
BEGIN
SELECT recovery_tokens.ID,
recovery_tokens.AccountID,
recovery_tokens.Date,
recovery_tokens.Token,
recovery_tokens.IsUsed
FROM recovery_tokens
WHERE recovery_tokens.Token = pToken
LIMIT 1;
END //
DROP PROCEDURE IF EXISTS GetRecoveryInfoByEMail//
CREATE PROCEDURE GetRecoveryInfoByEMail(IN pEMail VARCHAR(255) CHARSET utf8)
READS SQL DATA
BEGIN
SELECT recovery_tokens.ID,
recovery_tokens.AccountID,
recovery_tokens.Date,
recovery_tokens.Token,
recovery_tokens.IsUsed
FROM accounts
INNER JOIN recovery_tokens ON recovery_tokens.AccountID = accounts.ID
WHERE accounts.EMail = pEMail AND
recovery_tokens.IsUsed = 0
LIMIT 1;
END //
# -------------------------------------------------------------------------------------------
# Label: miscellaneous
# -------------------------------------------------------------------------------------------
DROP PROCEDURE IF EXISTS GetFinalFormInfo//
CREATE PROCEDURE GetFinalFormInfo (
IN pDisciplineID INT,
IN pGroupID INT
) READS SQL DATA
BEGIN
SELECT study_groups.GroupNum AS 'GroupNum',
groups_years.Name AS 'GroupName',
grades.ID AS 'GradeID',
grades.Num AS 'GradeNum',
grades.Degree AS 'Degree',
specializations.ID AS 'SpecID',
specializations.Name AS 'SpecName',
specializations.Abbr AS 'SpecAbbr',
specializations.Code AS 'SpecCode',
faculties.ID AS 'FacultyID',
faculties.Name AS 'FacultyName',
faculties.Abbr AS 'FacultyAbbr',
disciplines.ExamType AS 'ExamType',
subjects.ID AS 'SubjectID',
subjects.Name AS 'SubjectName',
subjects.Abbr AS 'SubjectAbbr',
teachers.ID AS 'AuthorID',
teachers.LastName AS 'LastName',
teachers.FirstName AS 'FirstName',
teachers.SecondName AS 'SecondName',
job_positions.Name AS 'JobPosition',
departments.ID AS 'DepID',
departments.Name AS 'DepName',
semesters.Year AS 'Year',
semesters.Num AS 'SemesterNum'
FROM study_groups
INNER JOIN grades ON study_groups.GradeID = grades.ID
INNER JOIN disciplines ON disciplines.ID = pDisciplineID
INNER JOIN subjects ON disciplines.SubjectID = subjects.ID
INNER JOIN teachers ON teachers.ID = disciplines.AuthorID
INNER JOIN departments ON departments.ID = teachers.DepartmentID
INNER JOIN job_positions ON job_positions.ID = teachers.JobPositionID
INNER JOIN semesters ON disciplines.SemesterID = semesters.ID
INNER JOIN groups_years ON groups_years.GroupID = study_groups.ID AND groups_years.Year = semesters.Year
INNER JOIN specializations ON groups_years.SpecializationID = specializations.ID
INNER JOIN faculties ON faculties.ID = specializations.FacultyID
WHERE study_groups.ID = pGroupID
LIMIT 1;
END //
DROP PROCEDURE IF EXISTS Student_GetInfo //
CREATE PROCEDURE Student_GetInfo(IN pID INT(11))
READS SQL DATA
BEGIN
SELECT
view_students.StudentID AS 'ID',
view_students.LastName,
view_students.FirstName,
view_students.SecondName,
view_students.AccountID,
view_students.SemesterID,
view_students.GroupID,
view_students.GroupNum,
view_students.GroupName,
view_students.GradeID,
view_students.GradeNum,
view_students.Degree,
view_students.SpecID,
view_students.SpecName,
view_students.SpecAbbr,
view_students.SpecCode,
view_students.FacultyID,
view_students.FacultyName,
view_students.FacultyAbbr
FROM view_students WHERE pID = view_students.StudentID
LIMIT 1;
END //
DROP PROCEDURE IF EXISTS Teacher_GetInfo //
CREATE PROCEDURE Teacher_GetInfo( IN pID INT(11) )
READS SQL DATA
SELECT view_teachers.TeacherID AS 'ID',
view_teachers.*
FROM view_teachers WHERE pID = view_teachers.TeacherID
LIMIT 1//
DROP PROCEDURE IF EXISTS GetRequests//
CREATE PROCEDURE GetRequests (
IN pOffset INT,
IN pCount INT,
IN pAccountID INT,
IN pFilter enum('opened','processed','closed','all')
) READS SQL DATA
BEGIN
SELECT requests.*, GetUserFullNameByAccountID(accounts.ID) as FullName, accounts.UserRoleID,
GetUserStudentOrTeacherID(AccountID, accounts.UserRoleID) as PersonalID
FROM requests join accounts on requests.AccountID = accounts.ID
WHERE IF(pFilter = 'all', TRUE, requests.Status = pFilter) AND
requests.Title != '' AND
requests.Description != ''
ORDER BY requests.Date DESC
LIMIT pCount OFFSET pOffset;
END//
DROP PROCEDURE IF EXISTS RequestsNum//
CREATE PROCEDURE RequestsNum (
IN pFilter enum('opened','processed','closed','all')
) READS SQL DATA
BEGIN
SELECT COUNT(*)
AS Num
FROM requests
WHERE IF(pFilter = 'all', TRUE, requests.Status = pFilter) AND
requests.Title != '' AND
requests.Description != '';
END//
# -------------------------------------------------------------------------------------------
# Label: authorization
# -------------------------------------------------------------------------------------------
-- pAccountID == 0 - get tokens of all users
DROP PROCEDURE IF EXISTS GetAuthTokens//
CREATE PROCEDURE GetAuthTokens(
IN pAccountID int(11))
BEGIN
-- accountID, Created, Accessed, Mask
IF pAccountID = 0 THEN
SELECT auth_tokens.*
FROM auth_tokens;
ELSE
SELECT auth_tokens.*
FROM auth_tokens
WHERE auth_tokens.AccountID = pAccountID;
END IF;
END//
DELIMITER ;