https://de.zeus-wiki.uni-konstanz.de/index.php?title=Script_zur_Umschl%C3%BCsselung_der_Jura-Studierenden&feed=atom&action=historyScript zur Umschlüsselung der Jura-Studierenden - Versionsgeschichte2024-03-29T09:32:54ZVersionsgeschichte dieser Seite in ZEuS-WikiMediaWiki 1.39.6https://de.zeus-wiki.uni-konstanz.de/index.php?title=Script_zur_Umschl%C3%BCsselung_der_Jura-Studierenden&diff=11292&oldid=prevMatthias.moebius am 12. September 2018 um 11:00 Uhr2018-09-12T11:00:29Z<p></p>
<a href="https://de.zeus-wiki.uni-konstanz.de/index.php?title=Script_zur_Umschl%C3%BCsselung_der_Jura-Studierenden&diff=11292&oldid=11187">Änderungen zeigen</a>Matthias.moebiushttps://de.zeus-wiki.uni-konstanz.de/index.php?title=Script_zur_Umschl%C3%BCsselung_der_Jura-Studierenden&diff=11187&oldid=prevMatthias.moebius: 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…“2018-08-31T10:34:57Z<p>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…“</p>
<p><b>Neue Seite</b></p><div> /*<br />
* 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).<br />
* Jura 20071: 08|135|-|-|H|20071|1|P|V|1|<br />
* 08|135|-|-|H|20032|1|P|V|2|<br />
* Jura 20032: 08|135|-|-|H|20032|1|P|V|1|<br />
* 08|135|-|-|H|20071|1|P|V|2|<br />
* 08|135|-|-|H|20071|1|P|V|8|<br />
* Jura 2017 08|135|-|-|H|2017|1|P|V|1|<br />
08|135|-|-|H|2017|1|P|V|2| <br />
08|135|-|-|H|2017|1|P|V|8| <br />
*/<br />
<br />
/*<br />
* 2. id für die Studiengänge ermitteln: select id from course_of_study where uniquename = 'ermittelter_uniquename';<br />
*/<br />
@set resultset name id der Studiengänge;<br />
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;<br />
select id, uniquename from course_of_study where uniquename like '08|135|-|-|H|20%' order by course_of_study.uniquename;<br />
<br />
<br />
@set resultset name Studierende dieser Studiengänge;<br />
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<br />
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<br />
where course_of_study_id in (2409, 2411, 2410, 2654 , 3931, 3930, 2653, 3995)<br />
and p.term_year=2018 and p.term_type_id=1 --Aktuelles Semester eintragen<br />
order by person.surname, person.firstname;<br />
<br />
/*<br />
* 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<br />
*/<br />
<br />
@set resultset name Anzahl Betroffene;<br />
with mtknrs as (<br />
select s.registrationnumber as registrationnumber<br />
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<br />
where course_of_study_id in (2409, 2411, 2410, 2654 , 3931, 3930, 2653, 3995)<br />
and p.term_year=2018 and p.term_type_id=1 --Aktuelles Semester eintragen<br />
)<br />
, joined_tables as (<br />
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<br />
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<br />
where course_of_study_id in (2409, 2410, 2411, 2654 , 3931, 3930, 2653, 3995) <br />
and s.registrationnumber in (/* Liste der mtknrs */ select registrationnumber from mtknrs )<br />
--and p.term_year=2017 and p.term_type_id=2<br />
)<br />
, data_of_maxsem as (<br />
select registrationnumber, course_of_study_id, max(studysemester) as maxsem <br />
from joined_tables <br />
group by registrationnumber, course_of_study_id<br />
)<br />
select registrationnumber, degree_program_progress_id, course_of_study_id, studysemester, period from joined_tables jt<br />
where 1=1<br />
order by registrationnumber, period;<br />
<br />
<br />
/*<br />
* 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;<br />
* Durch die Aktualisierung von 'updated_at' wird sichergestellt, dass bei der nächsten Re-Migration die Änderung auch in der sospos-Datenbank eingetragen wird.<br />
*/<br />
/*Erststudium*/<br />
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<br />
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<br />
where course_of_study_id in (2409, 2411, 2410, 2654 , 3931, 3930, 2653, 3995)<br />
and p.term_year=2018 and p.term_type_id=1 --Aktuelles Semester eintragen<br />
) ,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) <br />
and s.registrationnumber in (/* Liste der mtknrs */ select registrationnumber from mtknrs )<br />
--and p.term_year=2017 and p.term_type_id=2<br />
), data_of_maxsem as (select registrationnumber, course_of_study_id, max(studysemester) as maxsem from joined_tables group by registrationnumber, course_of_study_id)<br />
select degree_program_progress_id from joined_tables jt<br />
where 1=1);<br />
<br />
/*Zweitstudium*/<br />
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<br />
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<br />
where course_of_study_id in (2409, 2411, 2410, 2654 , 3931, 3930, 2653, 3995)<br />
and p.term_year=2018 and p.term_type_id=1 --Aktuelles Semester eintragen<br />
) ,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) <br />
and s.registrationnumber in (/* Liste der mtknrs */ select registrationnumber from mtknrs )<br />
--and p.term_year=2017 and p.term_type_id=2<br />
), data_of_maxsem as (select registrationnumber, course_of_study_id, max(studysemester) as maxsem from joined_tables group by registrationnumber, course_of_study_id)<br />
select degree_program_progress_id from joined_tables jt<br />
where 1=1);<br />
<br />
/*Weiterstudium*/<br />
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<br />
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<br />
where course_of_study_id in (2409, 2411, 2410, 2654 , 3931, 3930, 2653, 3995)<br />
and p.term_year=2018 and p.term_type_id=1 --Aktuelles Semester eintragen<br />
) ,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) <br />
and s.registrationnumber in (/* Liste der mtknrs */ select registrationnumber from mtknrs )<br />
--and p.term_year=2017 and p.term_type_id=2<br />
), data_of_maxsem as (select registrationnumber, course_of_study_id, max(studysemester) as maxsem from joined_tables group by registrationnumber, course_of_study_id)<br />
select degree_program_progress_id from joined_tables jt<br />
where 1=1);<br />
<br />
/*<br />
* 5. Status der 5690 usw. von BE auf BEPV ändern (k_workstatus_id von 1 auf 23 ändern)<br />
*/<br />
@set resultset name BE zu BEPV;<br />
select examrelation.k_workstatus_id, * <br />
from hisinone.examrelation <br />
where id in (with mtknrs as (select s.registrationnumber as registrationnumber<br />
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<br />
where course_of_study_id in (2409, 2411, 2410, 2654 , 3931, 3930, 2653, 3995)<br />
and p.term_year=2018 and p.term_type_id=1) --Aktuelles Semester eintragen<br />
select examplan.default_examrelation_id <br />
from hisinone.examplan <br />
where person_id in (select person_id from student WHERE student.registrationnumber in (select registrationnumber from mtknrs )) <br />
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)'));<br />
<br />
update examrelation set k_workstatus_id=(select id from hisinone.k_workstatus where uniquename='BEPV')<br />
where id in (with mtknrs as (select s.registrationnumber as registrationnumber<br />
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<br />
where course_of_study_id in (2409, 2411, 2410, 2654 , 3931, 3930, 2653, 3995)<br />
and p.term_year=2018 and p.term_type_id=1) --Aktuelles Semester eintragen<br />
select examplan.default_examrelation_id <br />
from hisinone.examplan <br />
where person_id in (select person_id from student WHERE student.registrationnumber in (select registrationnumber from mtknrs )) <br />
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)'))<br />
and k_workstatus_id in (select id from hisinone.k_workstatus where uniquename='BE');<br />
</div>Matthias.moebius