views.sql

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 */;

DROP VIEW IF EXISTS view_disciplines_teachers;
DROP VIEW IF EXISTS `view_disciplines_students`;
DROP VIEW IF EXISTS `view_rating_result`;
DROP VIEW IF EXISTS view_disciplines_results;


-- todo: remove max year crutch - add return (year, groupID, ...), use view_groups
CREATE OR REPLACE VIEW `view_groups_reduced` AS
    SELECT  study_groups.ID         AS 'GroupID',
            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'
        FROM groups_years
        LEFT JOIN groups_years as t ON groups_years.GroupID = t.GroupID AND groups_years.Year < t.Year
        INNER JOIN study_groups ON groups_years.GroupID = study_groups.ID
        INNER JOIN `specializations` ON groups_years.SpecializationID = specializations.ID
        INNER JOIN `grades` ON study_groups.GradeID = grades.ID
        INNER JOIN `faculties` ON faculties.ID = specializations.FacultyID
        WHERE t.GroupID IS NULL;

CREATE OR REPLACE VIEW `view_groups` AS
    SELECT  study_groups.ID         AS 'GroupID',
            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',
            groups_years.Year
        FROM groups_years
        INNER JOIN study_groups ON groups_years.GroupID = study_groups.ID
        INNER JOIN `specializations` ON groups_years.SpecializationID = specializations.ID
        INNER JOIN `grades` ON study_groups.GradeID = grades.ID
        INNER JOIN `faculties` ON faculties.ID = specializations.FacultyID;


CREATE OR REPLACE VIEW `view_teachers` AS
    SELECT  teachers.ID         AS 'TeacherID',
            teachers.LastName,
            teachers.FirstName,
            teachers.SecondName,
            teachers.AccountID,
            job_positions.ID    AS 'JobPositionID',
            job_positions.Name  AS 'JobPositionName',
            departments.ID      AS 'DepID',
            departments.Name    AS 'DepName',
            faculties.ID        AS 'FacultyID',
            faculties.Name      AS 'FacultyName',
            faculties.Abbr      AS 'FacultyAbbr'
        FROM `teachers`
        INNER JOIN `departments`    ON  departments.ID = teachers.DepartmentID
        INNER JOIN `faculties`      ON  departments.FacultyID = faculties.ID
        INNER JOIN `job_positions`  ON  job_positions.ID = teachers.JobPositionID;


CREATE OR REPLACE VIEW `view_students` AS
    SELECT  students.ID         AS 'StudentID',
            students.LastName,
            students.FirstName,
            students.SecondName,
            students.AccountID,
            students_groups.SemesterID,
            view_groups_reduced.*
        FROM `students`
        LEFT JOIN `students_groups` ON students_groups.StudentID = students.ID
        LEFT JOIN `view_groups_reduced` ON view_groups_reduced.GroupID = students_groups.GroupID;


CREATE OR REPLACE VIEW `view_disciplines` AS
    SELECT  disciplines.ID          AS 'DisciplineID',
            disciplines.AuthorID,
            disciplines.ExamType,
            disciplines.LectureCount,
            disciplines.PracticeCount,
            disciplines.LabCount,
            disciplines.SemesterID,
            disciplines.IsLocked,
            disciplines.Milestone,
            disciplines.Subtype,
            disciplines.CompoundDiscID,
            disciplines.MaxRate,
            disciplines.CurRate,
            grades.ID               AS 'GradeID',
            grades.Num              AS 'GradeNum',
            grades.Degree,
            subjects.ID             AS 'SubjectID',
            subjects.Name           AS 'SubjectName',
            subjects.Abbr           AS 'SubjectAbbr',
            faculties.ID            AS 'FacultyID',
            faculties.Name          AS 'FacultyName',
            faculties.Abbr          AS 'FacultyAbbr',
            compound_disciplines.Name AS 'CompoundDiscName'
        FROM `disciplines`
        INNER JOIN `subjects` ON subjects.ID = disciplines.SubjectID
        INNER JOIN `faculties` ON faculties.ID = disciplines.FacultyID
        INNER JOIN `grades` ON grades.ID = disciplines.GradeID
        LEFT JOIN `compound_disciplines` ON compound_disciplines.ID = disciplines.CompoundDiscID;




CREATE OR REPLACE VIEW `view_roadmap` AS
    SELECT  modules.DisciplineID,
            modules.ID AS 'ModuleID',
            modules.Name AS 'ModuleName',
            modules.OrderNum AS 'ModuleOrderNum',
            modules.Type AS 'ModuleType', # enum('regular','exam', 'bonus', 'extra')
            submodules.ID AS 'SubmoduleID',
            submodules.Name AS 'SubmoduleName',
            submodules.OrderNum AS 'SubmoduleOrderNum',
            submodules.MaxRate AS 'SubmoduleRate',
            submodules.Type AS 'SubmoduleType', # enum('CurrentControl','LandmarkControl')
            submodules.IsUsed AS 'SubmoduleIsUsed'
        FROM `modules`
        LEFT JOIN `submodules` ON submodules.ModuleID = modules.ID;