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 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');

/*

• 6. 9000er aus der PO 20071 auf 20032 bzw. 2017 umschlüsseln
• /

@set resultset name 9000 aus PO20071; select * from hisinone.examplan ep join hisinone.examrelation er on er.id=ep.default_examrelation_id where ep.unit_id=1989 and er.k_workstatus_id=2 and ep.term_year=2018 and ep.term_type_id=1;

update examplan set unit_id=1900 where id in (select ep.id from hisinone.examplan ep join hisinone.examrelation er on er.id=ep.default_examrelation_id where ep.unit_id=1989 and er.k_workstatus_id=2 and ep.term_year=2018 and ep.term_type_id=1 );