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