Поиск и отображение связанных с рабочим планом данных

Рабочий план имеет одну специальность, форму обучения, один номер курса. Если на план записан один студент, то с большой вероятностью это индивидуальный план в 1С. Можно сделать представление для связанных с планом данных и показывать их вместе или вместо номера плана в интерфейсе

changed milestone to %окт 2021 2/2

added A-backend A-db A-teacher D-medium P-medium T-feature labels

assigned to @czen

create or replace view public.view_study_plans
as select distinct on(study_plans.id)
       study_plans.id as "StudyPlanID",
       study_plans.externalid as "StudyPlanExtID",
       learning_plans.id as "LearningPlanID",
       learning_plans.externalid as "LearningPlanExtID",
       study_plans.year as "PlanYear",
       study_plans.facultyid as "PlanFaculty",
       record_books.id as "SampleRecordBookID",
       study_groups.id as "GroupID",
       counts."BookCount" as "TotalRecordBooks",
       case when counts."BookCount"=1 then true else false end as "Individual",
       specializations."name" as "SpecName",
       specializations.code as "SpecCode",
       specializations.year as "SpecYear",
       grades.degree  as "Degree",
       grades.num  as "GradeNum",
       study_form.formname as "StudyForm",
       record_books.externalid  as "SampleRecordBook",
       concat(accounts.lastname, ' ', accounts.firstname, ' ',  accounts.secondname, ' ') as "SampleStudentName"
   from study_plans
            inner join lateral(
       select count(1) as "BookCount", sp.id
       from record_books
                join record_books_plans on record_books_plans.studyplanid = record_books.id
                join study_plans sp on sp.id = record_books_plans.studyplanid
       where sp.id = study_plans.id
       group by sp.id
       ) counts on 1=1 -- counts.id=study_plans.id
            left join learning_plans on learning_plans.id = study_plans.learningplan
            join record_books_plans on record_books_plans.studyplanid = study_plans.id
            join record_books on record_books.id = record_books_plans.recordbookid
            join students_groups on students_groups.recordbookid  = record_books.id
            join study_groups on students_groups.groupid  = study_groups.id
            join study_form on study_groups.formid = study_form.id
            join grades on grades.id = study_groups.gradeid
            join specializations on specializations.id = study_groups.specid
            join students on students.id = record_books.studentid
            join accounts on accounts.id = students.accountid;

added 1h of time spent

Пары дисциплин, в которых выставлены баллы везде: 88996 с одной УКД и 91829 с другой, но теми же баллами

89071 и 91846 с одной УКД, но чуть разными студентами

added 1h of time spent