Script zur Umschlüsselung der Jura-Studierenden

/*
* 1. Ermitteln des uniquename (z.B. '96|179|-|-|H|44|1|P|V|1|') für die fraglichen Studiengänge über die Benutzeroberfläche (also einfach entsprechenden Studiengang suchen und uniquename 'rauskopieren).
* Jura 20071:     08|135|-|-|H|20071|1|P|V|1|
*                 08|135|-|-|H|20032|1|P|V|2|
* Jura 20032:     08|135|-|-|H|20032|1|P|V|1|
*                 08|135|-|-|H|20071|1|P|V|2|
*                 08|135|-|-|H|20071|1|P|V|8|
* Jura 2017       08|135|-|-|H|2017|1|P|V|1|
                  08|135|-|-|H|2017|1|P|V|2|  
                  08|135|-|-|H|2017|1|P|V|8|  
*/

/*
* 2. id für die Studiengänge ermitteln: select id from course_of_study where uniquename = 'ermittelter_uniquename';
*/
@set resultset name id der Studiengänge;
select id, uniquename from course_of_study where uniquename in ('08|135|-|-|H|2017|1|P|V|1|', '08|135|-|-|H|2017|1|P|V|2|', '08|135|-|-|H|2017|1|P|V|8|', '08|135|-|-|H|20071|1|P|V|1|', '08|135|-|-|H|20032|1|P|V|2|', '08|135|-|-|H|20032|1|P|V|1|', '08|135|-|-|H|20032|1|P|V|8|', '08|135|-|-|H|20071|1|P|V|2|') order by course_of_study.uniquename;
select id, uniquename from course_of_study where uniquename like '08|135|-|-|H|20%' order by course_of_study.uniquename;


@set resultset name Studierende dieser Studiengänge;
select s.registrationnumber as registrationnumber, person.surname, person.firstname, dpp.studysemester as studysemester, dpp.id as degree_program_progress_id, dpp.course_of_study_id, p.term_year*10+p.term_type_id as period
from degree_program_progress dpp join hisinone.degree_program dp on dp.id=dpp.degree_program_id join student s on s.id=dp.student_id join period p on p.id=dpp.period_id join person on person.id=s.person_id
where course_of_study_id in (2409, 2411, 2410, 2654 , 3931, 3930, 2653, 3995)
and p.term_year=2018 and p.term_type_id=1 --Aktuelles Semester eintragen
order by person.surname, person.firstname;

/*
* 3. Prüfen, wieviele Fälle betroffen sind (dies vorallem um festzustellen, ob weitere Einschränkungen nötig sind): select count(id) from degree_program_progress where course_of_study_id = id_des_alten_studiengangs
*/

@set resultset name Anzahl Betroffene;
with mtknrs as (
    select s.registrationnumber as registrationnumber
    from degree_program_progress dpp join hisinone.degree_program dp on dp.id=dpp.degree_program_id join student s on s.id=dp.student_id join period p on p.id=dpp.period_id join person on person.id=s.person_id
    where course_of_study_id in (2409, 2411, 2410, 2654 , 3931, 3930, 2653, 3995)
    and p.term_year=2018 and p.term_type_id=1 --Aktuelles Semester eintragen
)
, joined_tables as (
    select s.registrationnumber as registrationnumber, dpp.studysemester as studysemester, dpp.id as degree_program_progress_id, dpp.course_of_study_id, p.term_year*10+p.term_type_id as period
    from degree_program_progress dpp join hisinone.degree_program dp on dp.id=dpp.degree_program_id join student s on s.id=dp.student_id join period p on p.id=dpp.period_id
    where course_of_study_id in (2409, 2410, 2411, 2654 , 3931, 3930, 2653, 3995) 
    and s.registrationnumber in (/* Liste der mtknrs */ select registrationnumber from mtknrs )
        --and p.term_year=2017 and p.term_type_id=2
)
, data_of_maxsem as (
    select registrationnumber, course_of_study_id, max(studysemester) as maxsem 
    from joined_tables 
    group by registrationnumber, course_of_study_id
)
select registrationnumber, degree_program_progress_id, course_of_study_id, studysemester, period from joined_tables jt
where  1=1
order by registrationnumber, period;


/*
* 4. Studiengang umschlüsseln: UPDATE degree_program_progress SET course_of_study_id = id_des_neuen_studiengangs, updated_at = now() WHERE course_of_study_id =  id_des_alten_studiengangs;
*    Durch die Aktualisierung von 'updated_at' wird sichergestellt, dass bei der nächsten Re-Migration die Änderung auch in der sospos-Datenbank eingetragen wird.
*/
/*Erststudium*/
update degree_program_progress set course_of_study_id = 3931, updated_at = now() WHERE course_of_study_id in (2411, 2409) and id in (with mtknrs as (select s.registrationnumber as registrationnumber
from degree_program_progress dpp join hisinone.degree_program dp on dp.id=dpp.degree_program_id join student s on s.id=dp.student_id join period p on p.id=dpp.period_id join person on person.id=s.person_id
where course_of_study_id in (2409, 2411, 2410, 2654 , 3931, 3930, 2653, 3995)
and p.term_year=2018 and p.term_type_id=1 --Aktuelles Semester eintragen
) ,joined_tables as (select s.registrationnumber as registrationnumber, dpp.studysemester as studysemester, dpp.id as degree_program_progress_id, dpp.course_of_study_id, p.term_year*10+p.term_type_id as period from degree_program_progress dpp join hisinone.degree_program dp on dp.id=dpp.degree_program_id join student s on s.id=dp.student_id join period p on p.id=dpp.period_id where course_of_study_id in (2409, 2410, 2411, 2654 , 3931, 3930, 2653, 3995) 
        and s.registrationnumber in (/* Liste der mtknrs */ select registrationnumber from mtknrs )
        --and p.term_year=2017 and p.term_type_id=2
), data_of_maxsem as (select registrationnumber, course_of_study_id, max(studysemester) as maxsem from joined_tables group by registrationnumber, course_of_study_id)
select degree_program_progress_id from joined_tables jt
where  1=1);

/*Zweitstudium*/
update degree_program_progress set course_of_study_id = 3930, updated_at = now() WHERE course_of_study_id in (2654, 2410) and id in (with mtknrs as (select s.registrationnumber as registrationnumber
from degree_program_progress dpp join hisinone.degree_program dp on dp.id=dpp.degree_program_id join student s on s.id=dp.student_id join period p on p.id=dpp.period_id join person on person.id=s.person_id
where course_of_study_id in (2409, 2411, 2410, 2654 , 3931, 3930, 2653, 3995)
and p.term_year=2018 and p.term_type_id=1 --Aktuelles Semester eintragen
) ,joined_tables as (select s.registrationnumber as registrationnumber, dpp.studysemester as studysemester, dpp.id as degree_program_progress_id, dpp.course_of_study_id, p.term_year*10+p.term_type_id as period from degree_program_progress dpp join hisinone.degree_program dp on dp.id=dpp.degree_program_id join student s on s.id=dp.student_id join period p on p.id=dpp.period_id where course_of_study_id in (2409, 2410, 2411, 2654 , 3931, 3930, 2653, 3995) 
        and s.registrationnumber in (/* Liste der mtknrs */ select registrationnumber from mtknrs )
        --and p.term_year=2017 and p.term_type_id=2
), data_of_maxsem as (select registrationnumber, course_of_study_id, max(studysemester) as maxsem from joined_tables group by registrationnumber, course_of_study_id)
select degree_program_progress_id from joined_tables jt
where  1=1);

/*Weiterstudium*/
update degree_program_progress set course_of_study_id = 3995, updated_at = now() WHERE course_of_study_id in (2653) and id in (with mtknrs as (select s.registrationnumber as registrationnumber
from degree_program_progress dpp join hisinone.degree_program dp on dp.id=dpp.degree_program_id join student s on s.id=dp.student_id join period p on p.id=dpp.period_id join person on person.id=s.person_id
where course_of_study_id in (2409, 2411, 2410, 2654 , 3931, 3930, 2653, 3995)
and p.term_year=2018 and p.term_type_id=1 --Aktuelles Semester eintragen
) ,joined_tables as (select s.registrationnumber as registrationnumber, dpp.studysemester as studysemester, dpp.id as degree_program_progress_id, dpp.course_of_study_id, p.term_year*10+p.term_type_id as period from degree_program_progress dpp join hisinone.degree_program dp on dp.id=dpp.degree_program_id join student s on s.id=dp.student_id join period p on p.id=dpp.period_id where course_of_study_id in (2409, 2410, 2411, 2654 , 3931, 3930, 2653, 3995) 
        and s.registrationnumber in (/* Liste der mtknrs */ select registrationnumber from mtknrs )
        --and p.term_year=2017 and p.term_type_id=2
), data_of_maxsem as (select registrationnumber, course_of_study_id, max(studysemester) as maxsem from joined_tables group by registrationnumber, course_of_study_id)
select degree_program_progress_id from joined_tables jt
where  1=1);

/*
* 5. Status der 5690 usw. von BE auf BEPV ändern (k_workstatus_id von 1 auf 23 ändern)
*/
@set resultset name BE zu BEPV;
select examrelation.k_workstatus_id, * 
from hisinone.examrelation 
where id in (with mtknrs as (select s.registrationnumber as registrationnumber
                from degree_program_progress dpp join hisinone.degree_program dp on dp.id=dpp.degree_program_id join student s on s.id=dp.student_id join period p on p.id=dpp.period_id join person on person.id=s.person_id
                where course_of_study_id in (2409, 2411, 2410, 2654 , 3931, 3930, 2653, 3995)
                and p.term_year=2018 and p.term_type_id=1) --Aktuelles Semester eintragen
             select examplan.default_examrelation_id 
             from hisinone.examplan 
             where person_id in (select person_id from student WHERE student.registrationnumber in (select registrationnumber from mtknrs )) 
             and unit_id in (select id from unit where uniquename~'08\\|135\\|H\\|20032\\|(1001)|(1900)|(2000)|(3000)|(4000)|(5000)|(5090)|(5190)|(5290)|(5690)|(5790)|(6190)|(6290)|(8930)|(8999)'));

update examrelation set k_workstatus_id=(select id from hisinone.k_workstatus where uniquename='BEPV')
where id in (with mtknrs as (select s.registrationnumber as registrationnumber
                from degree_program_progress dpp join hisinone.degree_program dp on dp.id=dpp.degree_program_id join student s on s.id=dp.student_id join period p on p.id=dpp.period_id join person on person.id=s.person_id
                where course_of_study_id in (2409, 2411, 2410, 2654 , 3931, 3930, 2653, 3995)
                and p.term_year=2018 and p.term_type_id=1) --Aktuelles Semester eintragen
             select examplan.default_examrelation_id 
             from hisinone.examplan 
             where person_id in (select person_id from student WHERE student.registrationnumber in (select registrationnumber from mtknrs )) 
             and unit_id in (select id from unit where uniquename~'08\\|135\\|H\\|20032\\|(1001)|(1900)|(2000)|(3000)|(4000)|(5000)|(5090)|(5190)|(5290)|(5690)|(5790)|(6190)|(6290)|(8930)|(8999)'))
             and k_workstatus_id in (select id from hisinone.k_workstatus where uniquename='BE');