DELIMITER //
DROP PROCEDURE IF EXISTS GetCurSemesterInfo//
DROP PROCEDURE IF EXISTS GetCurSemesterID//
DROP PROCEDURE IF EXISTS GetHashKey//
DROP PROCEDURE IF EXISTS GetStudyGroupsForDisciplineFull//
DROP PROCEDURE IF EXISTS GetStudyGroupsForDiscipline//
DROP PROCEDURE IF EXISTS GetStudyGroups//
DROP PROCEDURE IF EXISTS GetDisciplineInfoByID//
DROP PROCEDURE IF EXISTS GetMapForDisciplineExam//
DROP PROCEDURE IF EXISTS GetMapForDiscipline//
DROP PROCEDURE IF EXISTS GetMapForStudent//
DROP PROCEDURE IF EXISTS GetMapForStudentExam//
DROP PROCEDURE IF EXISTS GetRatesForStudentsGroup//
DROP PROCEDURE IF EXISTS CreateFaculty //
DROP PROCEDURE IF EXISTS GetReports//
DROP PROCEDURE IF EXISTS GetSettings//
# -------------------------------------------------------------------------------------------
# Label: abbreviations
# -------------------------------------------------------------------------------------------
# abbreviation: abbr
# specialization: spec
# department: dep
# -------------------------------------------------------------------------------------------
# Label: preferences
# Label: magic
# -------------------------------------------------------------------------------------------
# -------------------------------------------------------------------------------------------
# Label: semesters
# -------------------------------------------------------------------------------------------
DROP PROCEDURE IF EXISTS GetSemesterInfo//
CREATE PROCEDURE `GetSemesterInfo` (
IN `pSemesterID` INT
) NO SQL
BEGIN
SELECT semesters.ID AS 'ID',
semesters.Num As 'Num',
semesters.Year As 'Year'
FROM `semesters`
WHERE semesters.ID = pSemesterID
LIMIT 1;
END //
DROP PROCEDURE IF EXISTS GetSemesters//
CREATE PROCEDURE `GetSemesters` (
) NO SQL
BEGIN
SELECT semesters.ID,
semesters.Year,
semesters.Num
FROM `semesters`
ORDER BY semesters.ID DESC;
END //
# -------------------------------------------------------------------------------------------
# Label: faculties
# -------------------------------------------------------------------------------------------
DROP PROCEDURE IF EXISTS GetFaculties//
CREATE PROCEDURE `GetFaculties` (
) NO SQL
BEGIN
SELECT faculties.ID,
faculties.Name,
faculties.Abbr
FROM `faculties`
ORDER BY faculties.Name ASC;
END //
# -------------------------------------------------------------------------------------------
# Label: departments
# -------------------------------------------------------------------------------------------
DROP PROCEDURE IF EXISTS GetDepartments//
CREATE PROCEDURE `GetDepartments` (
IN `pFacultyID` INT
) NO SQL
BEGIN
SELECT departments.ID,
departments.Name
FROM `departments`
WHERE departments.FacultyID = pFacultyID
ORDER BY departments.Name ASC;
END //
# -------------------------------------------------------------------------------------------
# Label: specializations
# -------------------------------------------------------------------------------------------
DROP PROCEDURE IF EXISTS GetSpecializations//
CREATE PROCEDURE `GetSpecializations` (
IN `pFacultyID` INT
) NO SQL
BEGIN
SELECT specializations.ID,
specializations.Name,
specializations.Abbr
FROM `specializations`
WHERE specializations.FacultyID = pFacultyID
ORDER BY subjects.Name ASC;
END //
# -------------------------------------------------------------------------------------------
# Label: job positions
# -------------------------------------------------------------------------------------------
DROP PROCEDURE IF EXISTS GetJobPositions//
CREATE PROCEDURE `GetJobPositions` (
) NO SQL
BEGIN
SELECT job_positions.ID,
job_positions.Name
FROM `job_positions`
ORDER BY job_positions.Name;
END //
# -------------------------------------------------------------------------------------------
# Label: grades
# -------------------------------------------------------------------------------------------
DROP PROCEDURE IF EXISTS GetGrades//
CREATE PROCEDURE `GetGrades` (
) NO SQL
BEGIN
SELECT grades.ID,
grades.Num,
grades.Degree
FROM `grades`
ORDER BY grades.ID;
END //
# -------------------------------------------------------------------------------------------
# Label: study groups
# -------------------------------------------------------------------------------------------
DROP PROCEDURE IF EXISTS GetGroups//
CREATE PROCEDURE `GetGroups` (
IN `pGradeID` INT,
IN `pFacultyID` INT
) NO SQL
BEGIN
SELECT view_groups.GroupID AS 'ID',
view_groups.GroupNum,
view_groups.SpecID,
view_groups.SpecName,
view_groups.SpecAbbr
FROM `view_groups`
WHERE view_groups.GradeID = pGradeID AND
view_groups.FacultyID = pFacultyID
ORDER BY view_groups.GroupNum ASC;
END //
# get all general study groups, that takes this course
DROP PROCEDURE IF EXISTS GetGroupsForDiscipline//
CREATE PROCEDURE `GetGroupsForDiscipline` (
IN `pDisciplineID` INT
) NO SQL
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 `view_groups` ON disciplines_groups.GroupID = view_groups.GroupID
WHERE disciplines_groups.DisciplineID = pDisciplineID
ORDER BY view_groups.GradeID ASC, view_groups.GroupID ASC;
END //
# -------------------------------------------------------------------------------------------
# Label: subjects
# -------------------------------------------------------------------------------------------
DROP PROCEDURE IF EXISTS GetSubjects//
CREATE PROCEDURE `GetSubjects` (
IN `pFacultyID` INT
) NO SQL
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
# -------------------------------------------------------------------------------------------
# TODO: deprecated (see GetFullInfo)
DROP PROCEDURE IF EXISTS GetAccountInfo//
CREATE PROCEDURE `GetAccountInfo` ( IN `pUserID` INT )
NO SQL
BEGIN
SELECT 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 `accounts`
INNER JOIN `user_roles` ON accounts.UserRoleID = user_roles.ID
WHERE accounts.ID = pUserID
LIMIT 1;
END //
# TODO: deprecated (see GetFullInfo)
DROP PROCEDURE IF EXISTS GetPersonalInfo//
CREATE PROCEDURE `GetPersonalInfo` (
IN `pUserID` INT,
IN `pSemesterID` INT
) NO SQL
BEGIN
DECLARE vAccountType INT DEFAULT -1;
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 = 1 THEN
SELECT view_students.LastName,
view_students.FirstName,
view_students.SecondName,
view_students.StudentID,
view_students.GradeID,
view_students.GradeNum,
view_students.GroupID,
view_students.GroupNum,
view_students.GroupName,
view_students.Degree,
view_students.SpecID,
view_students.SpecName,
view_students.SpecAbbr,
view_students.FacultyID,
view_students.FacultyName,
view_students.FacultyAbbr
FROM `view_students`
WHERE view_students.AccountID = pUserID AND
view_students.SemesterID = pSemesterID
LIMIT 1;
ELSE
SELECT 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
FROM `view_teachers`
WHERE view_teachers.AccountID = pUserID
LIMIT 1;
END IF;
END //
DROP PROCEDURE IF EXISTS GetFullInfo//
CREATE PROCEDURE `GetFullInfo` (
IN `pUserID` INT,
IN `pSemesterID` INT
) NO SQL
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 # student info
view_students.LastName,
view_students.FirstName,
view_students.SecondName,
view_students.StudentID,
view_students.GradeID,
view_students.GradeNum,
view_students.GroupID,
view_students.GroupNum,
view_students.GroupName,
view_students.Degree,
view_students.SpecID,
view_students.SpecName,
view_students.SpecAbbr,
view_students.FacultyID,
view_students.FacultyName,
view_students.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_students`
INNER JOIN `accounts` ON accounts.ID = view_students.AccountID
INNER JOIN `user_roles` ON user_roles.ID = accounts.UserRoleID
WHERE view_students.AccountID = pUserID AND
view_students.SemesterID = pSemesterID
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
# -------------------------------------------------------------------------------------------
# TODO: used in php
DROP PROCEDURE IF EXISTS GetTeachersByFaculty//
# CREATE PROCEDURE `GetTeachersByFaculty` (IN `pFacultyID` INT)
# NO SQL
# BEGIN
# SELECT view_teachers.TeacherID AS 'ID',
# view_teachers.LastName AS 'Last',
# view_teachers.FirstName AS 'First',
# view_teachers.SecondName AS 'Second',
# view_teachers.AccountID,
# view_teachers.JobPositionName,
# view_teachers.DepID,
# view_teachers.DepName
# FROM `view_teachers`
# WHERE view_teachers.FacultyID = pFacultyID
# ORDER BY view_teachers.LastName ASC, view_teachers.FirstName ASC;
# END //
DROP PROCEDURE IF EXISTS GetTeachersByDepartment//
CREATE PROCEDURE `GetTeachersByDepartment` (IN `pDepartmentID` INT)
NO SQL
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 view_teachers.DepID = pDepartmentID
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)
NO SQL
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
# todo: new parameter, pLoadAll. See Model_Student::getTeachers().
) NO SQL
BEGIN
DECLARE vStudentGroup INT DEFAULT -1;
SET vStudentGroup = GetStudentGroup(pStudentID, pSemesterID);
# select all disciplines for user
CREATE TEMPORARY TABLE IF NOT EXISTS tDisciplines 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'
);
SELECT tDisciplines.DisciplineID,
GROUP_CONCAT(
CONCAT(teachers.LastName, ' ', teachers.FirstName, ' ', teachers.SecondName)
ORDER BY teachers.ID = disciplines.AuthorID DESC,
teachers.LastName ASC,
teachers.FirstName ASC
SEPARATOR ';'
) AS 'FullNameList'
FROM tDisciplines
INNER JOIN `disciplines` ON disciplines.ID = tDisciplines.DisciplineID
LEFT JOIN `disciplines_teachers` ON disciplines_teachers.DisciplineID = tDisciplines.DisciplineID
INNER JOIN `teachers` ON teachers.ID = disciplines_teachers.TeacherID
GROUP BY tDisciplines.DisciplineID
ORDER BY tDisciplines.DisciplineID;
# todo: aggregation
# todo: return teachers' ID
END //
# get teachers, that don't teach course
DROP PROCEDURE IF EXISTS SearchTeachers//
CREATE PROCEDURE `SearchTeachers` (
IN `pFacultyID` INT,
IN `pDepartmentID` INT,
IN `pFullName` VARCHAR(100) CHARSET utf8,
# order: LastName + FirstName + SecondName
IN `pDisciplineID` INT
) NO SQL
BEGIN
DECLARE vAuthorID INT DEFAULT -1;
DECLARE vFullNameReg VARCHAR(102) CHARSET utf8;
SET vAuthorID = GetDisciplineProperty(pDisciplineID, 'author');
SET vFullNameReg = CONCAT('%', pFullName, '%');
CREATE TEMPORARY TABLE IF NOT EXISTS tDiscTeachers AS (
SELECT disciplines_teachers.TeacherID
FROM disciplines_teachers
WHERE disciplines_teachers.DisciplineID = pDisciplineID
);
SELECT view_teachers.TeacherID AS 'ID',
view_teachers.LastName,
view_teachers.FirstName,
view_teachers.SecondName,
view_teachers.JobPositionName,
view_teachers.DepID,
view_teachers.DepName,
( view_teachers.TeacherID = vAuthorID ) AS 'IsAuthor'
FROM `view_teachers`
WHERE view_teachers.FacultyID = pFacultyID AND
CASE WHEN pDepartmentID != 0 THEN view_teachers.DepID = pDepartmentID ELSE TRUE END AND
NOT EXISTS (
SELECT * FROM tDiscTeachers
WHERE tDiscTeachers.TeacherID = view_teachers.TeacherID
LIMIT 1
) AND
CASE
WHEN pFullName != '' THEN
CONCAT( view_teachers.LastName, ' ', view_teachers.FirstName, ' ', view_teachers.SecondName)
LIKE vFullNameReg
ELSE
TRUE
END
ORDER BY view_teachers.FacultyID ASC,
view_teachers.DepName ASC,
view_teachers.LastName ASC,
view_teachers.FirstName ASC;
END //
# -------------------------------------------------------------------------------------------
# Label: students
# -------------------------------------------------------------------------------------------
DROP PROCEDURE IF EXISTS GetStudentsByStudyGroups//
DROP PROCEDURE IF EXISTS GetStudents//
CREATE PROCEDURE `GetStudents` (
IN `pGroupID` INT,
IN `pSemesterID` INT
) NO SQL
BEGIN
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.GroupID = pGroupID AND
view_students.SemesterID = pSemesterID
ORDER BY view_students.LastName ASC, view_students.FirstName ASC;
END //
DROP PROCEDURE IF EXISTS GetStudentsByFaculty//
CREATE PROCEDURE `GetStudentsByFaculty` (
IN `pFacultyID` INT,
IN `pGradeID` INT,
IN `pGroupID` INT,
IN `pSemesterID` INT
) NO SQL
BEGIN
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.FacultyID = pFacultyID AND
view_students.SemesterID = pSemesterID 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
ORDER BY view_students.LastName ASC, view_students.FirstName ASC;
END //
DROP PROCEDURE IF EXISTS SearchStudentsByName//
CREATE PROCEDURE `SearchStudentsByName`(
IN `pFullName` VARCHAR(100) CHARSET utf8,
IN `pFacultyID` INT,
IN `pGradeID` INT,
IN `pGroupID` INT,
IN `pSemesterID` INT
)
NO SQL
BEGIN
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
(pFacultyID = 0 OR view_students.FacultyID = pFacultyID) AND
(pGradeID = 0 OR view_students.GradeID = pGradeID) AND
(pGroupID = 0 OR view_students.GroupID = pGroupID) AND
CONCAT(view_students.LastName, ' ', view_students.FirstName, ' ', view_students.SecondName)
LIKE CONCAT('%', pFullName, '%')
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
) NO SQL
BEGIN
DECLARE vSemesterID INT DEFAULT -1;
DECLARE vFullNameReg VARCHAR(102) CHARSET utf8;
DECLARE vIsGroupAttached BOOLEAN DEFAULT FALSE;
SET vSemesterID = GetDisciplineProperty(pDisciplineID, 'semester');
SET vFullNameReg = CONCAT('%', pFullName, '%');
CREATE TEMPORARY TABLE IF NOT EXISTS tDiscGroups AS (
SELECT disciplines_groups.GroupID
FROM `disciplines_groups`
WHERE disciplines_groups.DisciplineID = pDisciplineID
);
CREATE TEMPORARY TABLE IF NOT EXISTS tAttStud AS (
SELECT disciplines_students.StudentID
FROM `disciplines_students`
WHERE disciplines_students.DisciplineID = pDisciplineID AND
disciplines_students.Type = 'attach'
);
IF pGroupID != 0 THEN
SET vIsGroupAttached = EXISTS(
SELECT * FROM tDiscGroups WHERE tDiscGroups.GroupID = pGroupID LIMIT 1
);
END IF;
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`
WHERE view_students.SemesterID = vSemesterID AND
view_students.FacultyID = pFacultyID AND
view_students.GradeID = pGradeID AND
CASE
WHEN pGroupID != 0 THEN
NOT vIsGroupAttached AND
view_students.GroupID = pGroupID
ELSE
NOT EXISTS( SELECT * FROM tDiscGroups WHERE tDiscGroups.GroupID = view_students.GroupID )
END AND
NOT EXISTS( SELECT * FROM tAttStud WHERE tAttStud.StudentID = view_students.StudentID LIMIT 1 ) 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;
END //
# in general groups, attached or detached
DROP PROCEDURE IF EXISTS GetStudentsForDiscipline//
CREATE PROCEDURE `GetStudentsForDiscipline` (
IN `pDisciplineID` INT
) NO SQL
BEGIN
DECLARE vSemesterID INT DEFAULT -1;
SET vSemesterID = GetDisciplineProperty(pDisciplineID, 'semester');
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,
tDiscStudents.Type AS 'AttachType'
FROM (
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
WHERE disciplines_groups.DisciplineID = pDisciplineID
) as st
GROUP BY st.StudentID
) tDiscStudents
INNER JOIN `view_students` ON view_students.StudentID = tDiscStudents.StudentID AND
view_students.SemesterID = vSemesterID
ORDER BY ( tDiscStudents.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
) NO SQL
BEGIN
DECLARE vSemesterID INT DEFAULT -1;
SET vSemesterID = GetDisciplineProperty(pDisciplineID, 'semester');
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,
( tDiscStudents.Type IS NOT NULL ) AS 'IsAttached'
FROM (
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
WHERE disciplines_groups.DisciplineID = pDisciplineID
) as st
GROUP BY st.StudentID
) tDiscStudents
INNER JOIN `view_students` ON view_students.StudentID = tDiscStudents.StudentID AND
view_students.SemesterID = vSemesterID
WHERE NOT tDiscStudents.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
) NO SQL
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,
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 //
# TODO: haven't reference on it
# all disciplines for faculty in current semester
DROP PROCEDURE IF EXISTS GetDisciplines//
CREATE PROCEDURE `GetDisciplines` (
IN `pFacultyID` INT,
IN `pSemesterID` INT
) NO SQL
BEGIN
SELECT view_disciplines.DisciplineID AS 'ID',
view_disciplines.SubjectID,
view_disciplines.SubjectName,
view_disciplines.ExamType AS 'Type',
(view_disciplines_results.DisciplineRateMax = 100) AS 'isMapCreated'
FROM `view_disciplines`
INNER JOIN `view_disciplines_results`
ON view_disciplines_results.DisciplineID = view_disciplines.DisciplineID
WHERE view_disciplines.SemesterID = pSemesterID AND
view_disciplines.FacultyID = pFacultyID
ORDER BY view_disciplines.SubjectName ASC;
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
)
NO SQL
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', # TODO: change column name
(view_disciplines_results.DisciplineRateMax = 100) AS 'IsMapCreated'
FROM `disciplines_teachers`
LEFT JOIN `disciplines_groups` ON disciplines_groups.DisciplineID = disciplines_teachers.DisciplineID
# left -> inner join with maybe NULL ?
LEFT JOIN `view_groups` ON view_groups.GroupID = disciplines_groups.GroupID
LEFT JOIN `view_disciplines` ON disciplines_teachers.DisciplineID = view_disciplines.DisciplineID
LEFT JOIN `view_disciplines_results` ON view_disciplines_results.DisciplineID = view_disciplines.DisciplineID
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
) NO SQL
BEGIN
DECLARE vStudentGroup INT DEFAULT -1;
SET vStudentGroup = GetStudentGroup(pStudentID, pSemesterID);
# select all disciplines for user
CREATE TEMPORARY TABLE IF NOT EXISTS tDisciplines 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'
);
# 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 tDisciplines.DisciplineID,
vr.SubmoduleRate,
vr.ModuleType,
rt.Rate,
vr.SubmoduleIsUsed
FROM `tDisciplines`
LEFT JOIN `view_roadmap` AS vr ON vr.DisciplineID = tDisciplines.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
)
NO SQL
BEGIN
(SELECT view_disciplines.DisciplineID AS 'ID',
view_disciplines.SubjectName,
view_disciplines.Subtype,
view_disciplines.ExamType AS 'Type'
FROM `disciplines_groups`
INNER JOIN `view_disciplines` ON view_disciplines.DisciplineID = disciplines_groups.DisciplineID AND
view_disciplines.SemesterID = pSemesterID
WHERE disciplines_groups.GroupID = pGroupID
) UNION DISTINCT
(SELECT view_disciplines.DisciplineID AS 'ID',
view_disciplines.SubjectName,
view_disciplines.Subtype,
view_disciplines.ExamType AS 'Type'
FROM `disciplines_students`
INNER JOIN `students` ON disciplines_students.StudentID = students.ID
INNER JOIN `view_disciplines` ON view_disciplines.DisciplineID = disciplines_students.DisciplineID AND
view_disciplines.SemesterID = pSemesterID
INNER JOIN `students_groups` ON students_groups.StudentID = students.ID AND
students_groups.SemesterID = pSemesterID
WHERE students_groups.GroupID = pGroupID
);
END //
# -------------------------------------------------------------------------------------------
# Label: rating
# -------------------------------------------------------------------------------------------
DROP PROCEDURE IF EXISTS GetRatesForGroup//
CREATE PROCEDURE `GetRatesForGroup` (
IN `pDisciplineID` INT,
IN `pGroupID` INT)
NO SQL
BEGIN
DECLARE vChecker BOOLEAN DEFAULT FALSE;
DECLARE vSemesterID INT DEFAULT -1;
SET vSemesterID = GetDisciplineProperty(pDisciplineID, 'semester');
SELECT disciplines_groups.ID IS NOT NULL INTO vChecker
FROM `disciplines_groups`
WHERE disciplines_groups.DisciplineID = pDisciplineID AND
disciplines_groups.GroupID = pGroupID
LIMIT 1;
IF !vChecker THEN
SELECT students.ID,
students.LastName,
students.FirstName,
students.SecondName,
view_rating_result.RateRegular AS 'intermediate',
view_rating_result.RateBonus AS 'bonus',
view_rating_result.RateExam AS 'exam'
FROM `students`
LEFT JOIN `view_rating_result` ON view_rating_result.DisciplineID = pDisciplineID AND
view_rating_result.StudentID = students.ID
INNER JOIN `students_groups` ON students_groups.StudentID = students.ID AND
students_groups.SemesterID = vSemesterID
WHERE students_groups.GroupID = pGroupID AND
EXISTS(SELECT * FROM `disciplines_students`
WHERE disciplines_students.DisciplineID = pDisciplineID AND
disciplines_students.StudentID = students.ID)
ORDER BY CONCAT(students.LastName, students.FirstName, students.SecondName) ASC,
students.ID ASC;
ELSE
SELECT students.ID,
students.LastName,
students.FirstName,
students.SecondName,
view_rating_result.RateRegular AS 'intermediate',
view_rating_result.RateBonus AS 'bonus',
view_rating_result.RateExam AS 'exam'
FROM `students`
LEFT JOIN `view_rating_result` ON view_rating_result.DisciplineID = pDisciplineID AND
view_rating_result.StudentID = students.ID
INNER JOIN `students_groups` ON students_groups.StudentID = students.ID AND
students_groups.SemesterID = vSemesterID
WHERE students_groups.GroupID = pGroupID AND
NOT EXISTS(SELECT * FROM `disciplines_students`
WHERE disciplines_students.DisciplineID = pDisciplineID AND
disciplines_students.StudentID = students.ID)
ORDER BY CONCAT(students.LastName, students.FirstName, students.SecondName) ASC,
students.ID ASC;
END IF;
END //
DROP PROCEDURE IF EXISTS GetRatesForGroupByStage//
CREATE PROCEDURE `GetRatesForGroupByStage`
( IN `pDisciplineID` INT, IN `pGroupID` INT, IN `pMilestone` INT)
NO SQL
BEGIN
SELECT view_disciplines_students.StudentID,
view_disciplines_students.LastName As 'LastName',
view_disciplines_students.FirstName As 'FirstName',
view_disciplines_students.SecondName As 'SecondName',
SUM(rating_table.Rate*(view_roadmap.ModuleType = 'regular')) AS 'Semester',
SUM(rating_table.Rate*(view_roadmap.ModuleType = 'bonus')) AS 'Bonus',
SUM(rating_table.Rate*(view_roadmap.ModuleType = 'extra')) AS 'Extra',
SUM(rating_table.Rate*(view_roadmap.ModuleType = 'exam')*(view_roadmap.SubmoduleOrderNum = pMilestone)) AS 'Exam',
MAX(rating_table.Rate*(view_roadmap.ModuleType = 'exam')*(view_roadmap.SubmoduleOrderNum < pMilestone)) AS 'PreviousExam',
MAX(IF(view_roadmap.SubmoduleOrderNum = pMilestone, exam_period_options.TYPE, NULL)) As 'Option',
MAX(IF(exam_period_options.TYPE = 'pass', 1, 0)) As 'AutoPassed'
FROM `view_disciplines_students`
LEFT JOIN `view_roadmap` ON view_roadmap.DisciplineID = view_disciplines_students.DisciplineID
LEFT JOIN `rating_table` ON rating_table.StudentID = view_disciplines_students.StudentID AND
rating_table.SubmoduleID = view_roadmap.SubmoduleID
LEFT JOIN `exam_period_options` ON exam_period_options.submoduleID = view_roadmap.SubmoduleID AND
exam_period_options.StudentID = view_disciplines_students.StudentID
WHERE view_disciplines_students.DisciplineID = pDisciplineID AND
view_disciplines_students.GroupID = pGroupID AND
NOT view_disciplines_students.AttachType <=> 'detach' AND
( view_roadmap.ModuleType <> 'extra' OR
(view_roadmap.ModuleType = 'extra' AND view_roadmap.SubmoduleOrderNum < pMilestone )
)
GROUP BY view_disciplines_students.StudentID
ORDER BY LastName ASC, FirstName ASC, SecondName ASC;
END //
DROP PROCEDURE IF EXISTS GetRates//
CREATE PROCEDURE `GetRates`
( IN `pStudentID` INT, IN `pDisciplineID` INT)
NO SQL
BEGIN
SELECT view_roadmap.ModuleID,
view_roadmap.ModuleName,
view_roadmap.SubmoduleID,
view_roadmap.SubmoduleName,
view_roadmap.SubmoduleRate AS 'MaxRate',
view_roadmap.SubmoduleType AS 'SubmoduleControl',
view_roadmap.ModuleType,
rating_table.Rate,
rating_table.Date,
exam_period_options.Type As ExamPeriodOption
FROM `view_roadmap`
LEFT JOIN `rating_table` ON view_roadmap.SubmoduleID = rating_table.SubmoduleID AND
rating_table.StudentID = pStudentID
LEFT JOIN `exam_period_options` ON exam_period_options.StudentID = pStudentID AND
exam_period_options.SubmoduleID = rating_table.SubmoduleID
WHERE view_roadmap.DisciplineID = pDisciplineID AND
(
view_roadmap.ModuleType != 'exam' OR
view_roadmap.SubmoduleID = (@tmp =
(SELECT rating_table.SubmoduleID FROM `submodules`
INNER JOIN `rating_table` ON rating_table.SubModuleID = submodules.ID
WHERE submodules.ModuleID = view_roadmap.ModuleID AND rating_table.StudentID = pStudentID
ORDER BY submodules.OrderNum DESC
LIMIT 1)
) OR
( @tmp IS NULL AND view_roadmap.SubmoduleOrderNum = 1)
)
ORDER BY view_roadmap.ModuleType ^ 1 ASC,
-- 1, 3, 2, 4 ASC
view_roadmap.ModuleOrderNum ASC,
view_roadmap.SubmoduleOrderNum ASC;
END //
# TODO: rename ~ GetRatesForStudent, Rating
DROP PROCEDURE IF EXISTS GetRatesExam//
CREATE PROCEDURE `GetRatesExam`
( IN `pStudentID` INT, IN `pDisciplineID` INT)
NO SQL
BEGIN
SELECT view_roadmap.ModuleID,
view_roadmap.ModuleName,
view_roadmap.SubmoduleID,
view_roadmap.SubmoduleName,
view_roadmap.SubmoduleRate AS 'MaxRate',
view_roadmap.SubmoduleType,
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 GetAttestationData//
CREATE PROCEDURE `GetAttestationData` (
IN `pDisciplineID` INT,
IN `pGroupID` INT
)
NO SQL
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
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: merge with GetRoadmapExam
# get roadmap of discipline
DROP PROCEDURE IF EXISTS GetRoadmap//
CREATE PROCEDURE `GetRoadmap`
( IN `pDisciplineID` INT)
NO SQL
BEGIN
SELECT view_disciplines.SubjectID,
view_disciplines.SubjectName,
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`
INNER JOIN `view_disciplines` ON view_disciplines.DisciplineID = pDisciplineID
WHERE view_roadmap.DisciplineID = pDisciplineID AND
(view_roadmap.ModuleType != 'exam' OR view_roadmap.SubmoduleOrderNum = 1)
ORDER BY view_roadmap.ModuleType ^ 1 ASC,
# 1, 3, 2, 4 ASC
view_roadmap.ModuleOrderNum ASC,
view_roadmap.SubmoduleOrderNum ASC;
END //
# get roadmap of discipline exam
DROP PROCEDURE IF EXISTS GetRoadmapExam//
CREATE PROCEDURE `GetRoadmapExam`
( IN `pDisciplineID` INT)
NO SQL
BEGIN
SELECT view_disciplines.SubjectID,
view_disciplines.SubjectName,
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`
INNER JOIN `view_disciplines` ON view_disciplines.DisciplineID = pDisciplineID
WHERE view_roadmap.DisciplineID = pDisciplineID AND
(view_roadmap.ModuleType = 'exam' OR view_roadmap.ModuleType = 'extra')
ORDER BY InternalOrderModuleTypesForSession(view_roadmap.ModuleType) ASC,
view_roadmap.ModuleOrderNum ASC,
view_roadmap.SubmoduleOrderNum ASC;
END //
# -------------------------------------------------------------------------------------------
# Label: recovery
# -------------------------------------------------------------------------------------------
DROP PROCEDURE IF EXISTS GetRecoveryInfoByToken//
CREATE PROCEDURE `GetRecoveryInfoByToken`
(IN `pToken` VARCHAR(100) CHARSET utf8)
NO SQL
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)
NO SQL
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
)
NO SQL
BEGIN
SELECT study_groups.GroupNum AS 'GroupNum',
study_groups.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 `specializations` ON study_groups.SpecializationID = specializations.ID
INNER JOIN `grades` ON study_groups.GradeID = grades.ID
INNER JOIN `faculties` ON faculties.ID = specializations.FacultyID
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
WHERE study_groups.ID = pGroupID
LIMIT 1;
END //
DROP PROCEDURE IF EXISTS GetSession //
CREATE PROCEDURE GetSession (IN pID INT(11), OUT pSessionID VARCHAR(40), OUT pUserLogin VARCHAR(50) CHARSET utf8, OUT pUserPassword VARCHAR(64))
NO SQL
BEGIN
SELECT SessionID, Login, Password
INTO pSessionID, pUserLogin, pUserPassword
FROM sessions
WHERE sessions.ID=pID;
END //
DROP PROCEDURE IF EXISTS Student_GetInfo //
CREATE PROCEDURE Student_GetInfo(
IN pID INT(11)
) NO SQL
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 //
DELIMITER ;