Aktionen

Script zur Umschlüsselung der Jura-Studierenden: Unterschied zwischen den Versionen

Aus ZEuS-Wiki

(Die Seite wurde neu angelegt: „ /* * 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 entsprechend…“)
 
Keine Bearbeitungszusammenfassung
 
Zeile 1: Zeile 1:
/*
/*
* 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).
* 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|
* Jura 20071:     08|135|-|-|H|20071|1|P|V|1|
*                 08|135|-|-|H|20032|1|P|V|2|
*                 08|135|-|-|H|20032|1|P|V|2|
* Jura 20032:     08|135|-|-|H|20032|1|P|V|1|
* 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|2|
*                 08|135|-|-|H|20071|1|P|V|8|
*                 08|135|-|-|H|20071|1|P|V|8|
* Jura 2017       08|135|-|-|H|2017|1|P|V|1|
* 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|2|
                    08|135|-|-|H|2017|1|P|V|8|  
                  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');
   
   
/*
/*
* 2. id für die Studiengänge ermitteln: select id from course_of_study where uniquename = 'ermittelter_uniquename';
* 6. 9000er aus der PO 20071 auf 20032 bzw. 2017 umschlüsseln
*/
*/
@set resultset name id der Studiengänge;
@set resultset name 9000 aus PO20071;
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 *
select id, uniquename from course_of_study where uniquename like '08|135|-|-|H|20%' order by course_of_study.uniquename;
from hisinone.examplan ep
join hisinone.examrelation er on er.id=ep.default_examrelation_id
where ep.unit_id=1989
@set resultset name Studierende dieser Studiengänge;
and er.k_workstatus_id=2
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
and ep.term_year=2018 and ep.term_type_id=1;
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)
update examplan set unit_id=1900 where id in (select ep.id
and p.term_year=2018 and p.term_type_id=1 --Aktuelles Semester eintragen
from hisinone.examplan ep
order by person.surname, person.firstname;
join hisinone.examrelation er on er.id=ep.default_examrelation_id
where ep.unit_id=1989
/*
and er.k_workstatus_id=2
* 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
and ep.term_year=2018 and ep.term_type_id=1
*/
);
@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');
 

Aktuelle Version vom 12. September 2018, 13:00 Uhr

/*

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

Abgerufen von „https://de.zeus-wiki.uni-konstanz.de/index.php?title=Script_zur_Umschlüsselung_der_Jura-Studierenden&oldid=11292“