Schnittstelle CampusOnline (TU Graz) zu SuperX-Modul COSTAGE
http://www.superx-projekt.de/doku/costage_modul/
You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
341 lines
10 KiB
341 lines
10 KiB
|
|
--freemarker template |
|
|
|
|
|
SELECT |
|
SM.semester_tid as semester, |
|
SG.st_absz_nr, |
|
SG.st_skz_nr, |
|
SG.studiengangs_identifikator, |
|
SG.studiengangs_bezeichnung, |
|
SM.st_sem_nr, |
|
ST.st_studium_nr, |
|
S.st_studstamm_nr, |
|
ST.st_studiengang_nr, |
|
SG.studiengangs_typ_kb, |
|
SA.studienstatustyp, |
|
SA.st_studienstatus_nr, |
|
SA.beginn_datum, |
|
SA.gueltig_ab, |
|
val(S.matrikelnummer) as matrikelnummer, |
|
S.geschlecht, |
|
S.ca12_staat, |
|
S.second_nationality, |
|
SG.fachkennzeichen, |
|
ST.studienform_statistik_code, |
|
null::integer as st_studiengang_nr_msg, |
|
null::integer as st_studiengang_nr_msg_primaerflag, |
|
null::integer as st_studiengang_nr_tsg, |
|
null::integer as studienfach_sortierung, |
|
null::char(10) as hrst, |
|
null::integer as fachsemester, |
|
null::integer as hssem, |
|
null::char(10) as statustyp, |
|
--0::smallint as primaerflag, |
|
null::integer as studiengang_nr, |
|
null::integer as fach_nr, |
|
null::char(10) as unikey, |
|
null::date as anfdat, |
|
null::date as ruebeudat, |
|
null::date as endedat, |
|
null::smallint as kz_rueck_beur_ein, |
|
rank () over (partition by SM.st_sem_nr, S.st_studstamm_nr, SG.studiengangs_identifikator order by SA.gueltig_ab desc) as status_historie |
|
into temp tmp_studien |
|
FROM costage_st_studierendenstammdaten S, |
|
costage_st_studien ST, |
|
costage_st_semester SM, |
|
costage_st_studiengaenge SG , |
|
costage_st_studienstatus SA |
|
|
|
where |
|
S.st_studstamm_nr = ST.st_studstamm_nr |
|
and ST.st_studiengang_nr=SG.st_studiengang_nr |
|
and S.matrikelnummer is not null |
|
--and SM.semester_tid >= 20171 |
|
-- and SM.semester_tid <= 20171 |
|
--and S.matrikelnummer in ('1415093') |
|
and SG.studiengangs_typ_kb in ('ESG','TSG') |
|
and SA.st_studium_nr=ST.st_studium_nr |
|
and SA.st_sem_nr=SM.st_sem_nr |
|
and SA.studienstatustyp not in ('a','o') |
|
and ${COSTAGE_STUDENT_FILTER} |
|
--group by 1,2,3,4,5,6,7,8,9,10,11,12,13 |
|
; |
|
-- select * from tmp_studien |
|
-- update tmp_studien |
|
-- set abschluss_str=A.name |
|
-- from costage_st_abschlussziele_lokal A |
|
-- where A.st_absz_nr=tmp_studien.abschluss |
|
-- ; |
|
|
|
update tmp_studien |
|
set hrst=H.hoererstatus_kb |
|
from costage_st_hoererstatus H |
|
where tmp_studien.st_studstamm_nr = H.st_studstamm_nr |
|
and tmp_studien.st_sem_nr = H.st_sem_nr |
|
; |
|
|
|
|
|
update tmp_studien |
|
set fachsemester=F.fachsemester |
|
from costage_st_fachsemester F |
|
where F.st_studium_nr=tmp_studien.st_studium_nr |
|
and F.st_sem_nr=tmp_studien.st_sem_nr |
|
and F.fachsemestertyp='FS' |
|
; |
|
|
|
|
|
select T.*,1::smallint as primaerflag |
|
into temp tmp_studien2 |
|
from tmp_studien T, costage_st_hauptstudien H |
|
where H.st_studstamm_nr=T.st_studstamm_nr |
|
and H.st_studium_nr=T.st_studium_nr |
|
and H.st_sem_nr=T.st_sem_nr |
|
and status_historie=1 |
|
; |
|
--Studiengänge ohne Primärflag: |
|
insert into tmp_studien2 |
|
select T.*,0::smallint as primaerflag |
|
from tmp_studien T left outer join costage_st_hauptstudien H |
|
on (H.st_studstamm_nr=T.st_studstamm_nr |
|
and H.st_studium_nr=T.st_studium_nr |
|
and H.st_sem_nr=T.st_sem_nr) |
|
where T.status_historie=1 |
|
and H.st_studstamm_nr is null |
|
; |
|
|
|
drop table tmp_studien; |
|
|
|
|
|
--Ermittlung msg: |
|
update tmp_studien2 set st_studiengang_nr_msg=M.st_studiengang_nr_msg |
|
from costage_st_studiengaenge_mtsg M, costage_st_studien ST |
|
where ST.st_studiengang_nr=M.st_studiengang_nr_msg |
|
and ST.st_studstamm_nr=tmp_studien2.st_studstamm_nr |
|
and M.st_studiengang_nr_tsg=tmp_studien2.st_studiengang_nr |
|
and tmp_studien2.studiengangs_typ_kb ='TSG' |
|
; |
|
|
|
--Ermittlung fach_nr: |
|
update tmp_studien2 set fach_nr=M.studienfach_sortierung |
|
from costage_st_studiengaenge_mtsg M |
|
where M.st_studiengang_nr_msg=tmp_studien2.st_studiengang_nr_msg |
|
and M.st_studiengang_nr_tsg=tmp_studien2.st_studiengang_nr |
|
and tmp_studien2.studiengangs_typ_kb ='TSG' |
|
; |
|
|
|
update tmp_studien2 set fach_nr=1 |
|
where fach_nr is null; |
|
|
|
--Ermittlung status bei TSG aus MSG: |
|
update tmp_studien2 set studienstatustyp=SA.studienstatustyp |
|
from costage_st_studienstatus SA, costage_st_studiengaenge SG ,costage_st_studien ST |
|
where SG.st_studiengang_nr=tmp_studien2.st_studiengang_nr_msg |
|
and ST.st_studium_nr=SA.st_studium_nr |
|
and ST.st_studstamm_nr=tmp_studien2.st_studstamm_nr |
|
and SA.st_sem_nr=tmp_studien2.st_sem_nr |
|
and SA.gueltig_ab=tmp_studien2.gueltig_ab |
|
and tmp_studien2.studiengangs_typ_kb ='TSG' |
|
; |
|
|
|
|
|
|
|
|
|
update tmp_studien2 set unikey=K.unikey |
|
from costage_st_studiengaenge G, costage_st_studienkennzahlen K |
|
where G.st_studiengang_nr=tmp_studien2.st_studiengang_nr |
|
and K.st_skz_nr=G.st_skz_nr |
|
; |
|
|
|
update tmp_studien2 set kz_rueck_beur_ein=(select val(astat) |
|
from cifx where key=400 |
|
and apnr=tmp_studien2.studienstatustyp |
|
); |
|
|
|
update tmp_studien2 |
|
set hssem=1 where kz_rueck_beur_ein=1; |
|
; |
|
|
|
update tmp_studien2 set studiengang_nr=1 where primaerflag=1; |
|
|
|
--Wenn Studium mit Primärflag ein TSG ist, wird die studiengang_nr auf alle anderen TSG des MSG ausgedehnt: |
|
select st_studstamm_nr,semester,st_studiengang_nr_msg |
|
into temp tmp_primaer_msg |
|
from tmp_studien2 |
|
where studiengangs_typ_kb ='TSG' |
|
and st_studiengang_nr_msg is not null |
|
and primaerflag=1; |
|
|
|
--select * from tmp_primaer_msg; |
|
|
|
update tmp_studien2 set studiengang_nr=1 where primaerflag=0 |
|
and studiengangs_typ_kb ='TSG' |
|
and exists (select st_studiengang_nr_msg |
|
from tmp_primaer_msg T |
|
where T.st_studstamm_nr=tmp_studien2.st_studstamm_nr |
|
and T.semester=tmp_studien2.semester |
|
and T.st_studiengang_nr_msg=tmp_studien2.st_studiengang_nr_msg |
|
) |
|
; |
|
|
|
select distinct st_studiengang_nr_msg, matrikelnummer, semester |
|
into temp temp_tmp_studien2 |
|
from tmp_studien2 |
|
where primaerflag =1 |
|
and studiengangs_typ_kb ='TSG'; |
|
|
|
|
|
delete from costage_st_studien_aggr |
|
--provisorisch in Testphase: |
|
where ${COSTAGE_STUDENT_FILTER}; |
|
|
|
create index ix_tmp_studien2 on tmp_studien2(kz_rueck_beur_ein) |
|
; |
|
insert into costage_st_studien_aggr( |
|
st_studium_nr, |
|
st_studstamm_nr, |
|
matrikelnummer, |
|
st_studiengang_nr, |
|
st_studienstatus_nr, |
|
statustyp, |
|
hrst, |
|
semester, |
|
fachsemester, |
|
hssem, |
|
primaerflag, |
|
studiengang_nr, |
|
fach_nr, |
|
unikey, |
|
anfdat, |
|
ruebeudat, |
|
endedat, |
|
stufrm, |
|
kz_rueck_beur_ein, |
|
summe |
|
) |
|
|
|
select st_studium_nr, |
|
st_studstamm_nr, |
|
matrikelnummer, |
|
st_studiengang_nr, |
|
st_studienstatus_nr, |
|
studienstatustyp, |
|
hrst, |
|
semester, |
|
fachsemester, |
|
hssem, |
|
primaerflag, |
|
--Nun ist studiengang_nr=1 definiert. Die restlichen werden mit RANK ermittelt: |
|
(case when studiengang_nr =1 then studiengang_nr |
|
else |
|
dense_rank () over (partition by semester, st_studstamm_nr,studiengang_nr order by |
|
(case when studiengangs_typ_kb='TSG' then st_studiengang_nr_msg else st_studium_nr end ))+1 |
|
end) |
|
as studiengang_nr, |
|
--AP Code: |
|
-- (case when studiengangs_typ_kb ='TSG' then |
|
-- (case when primaerflag =1 |
|
-- then 1 |
|
-- else |
|
-- (case when st_studiengang_nr_msg in (select distinct T2.st_studiengang_nr_msg from temp_tmp_studien2 T2 |
|
-- where tmp_studien2.matrikelnummer = T2.matrikelnummer and tmp_studien2.semester = T2.Semester) |
|
-- then 1 |
|
-- else |
|
-- dense_rank () over (partition by semester, st_studstamm_nr,studiengang_nr order by(case when studiengangs_typ_kb='TSG' then st_absz_nr else st_studium_nr end ))+1 |
|
-- end )end) |
|
-- else |
|
-- (case when primaerflag =1 |
|
-- then 1 |
|
-- else |
|
-- dense_rank () over (partition by semester, st_studstamm_nr,studiengang_nr order by( case when studiengangs_typ_kb='TSG' then st_absz_nr else st_studium_nr end ))+1 |
|
-- end) end ) |
|
--as studiengang_nr2, |
|
(case when primaerflag =1 then 1 |
|
else |
|
dense_rank () over (partition by semester, st_studstamm_nr,studiengang_nr |
|
order by primaerflag desc,fach_nr) |
|
end) as fach_nr, |
|
unikey, |
|
beginn_datum as anfdat, |
|
(case when studienstatustyp not in ('Z','X') then null::date |
|
else gueltig_ab end) as ruebeudat, |
|
(case when studienstatustyp in ('Z','X') then gueltig_ab |
|
else null::date end) as endedat, |
|
studienform_statistik_code as stufrm, |
|
kz_rueck_beur_ein, |
|
1 as summe |
|
from tmp_studien2 |
|
-- where semester = 20202 |
|
--and matrikelnummer = '5545242' |
|
where kz_rueck_beur_ein is not null |
|
; |
|
|
|
-- The below code appends the data for the records having kz_rueck_beur_ein as NULL, this data could be helpful for other diagonostic purpose; here this is segregated |
|
-- from above to accomodate ranking in studiengangNummer field |
|
insert into costage_st_studien_aggr( |
|
st_studium_nr, |
|
st_studstamm_nr, |
|
matrikelnummer, |
|
st_studiengang_nr, |
|
st_studienstatus_nr, |
|
statustyp, |
|
hrst, |
|
semester, |
|
fachsemester, |
|
hssem, |
|
primaerflag, |
|
studiengang_nr, |
|
fach_nr, |
|
unikey, |
|
anfdat, |
|
ruebeudat, |
|
endedat, |
|
stufrm, |
|
kz_rueck_beur_ein, |
|
summe |
|
) |
|
|
|
select st_studium_nr, |
|
st_studstamm_nr, |
|
matrikelnummer, |
|
st_studiengang_nr, |
|
st_studienstatus_nr, |
|
studienstatustyp, |
|
hrst, |
|
semester, |
|
fachsemester, |
|
hssem, |
|
primaerflag, |
|
--Nun ist studiengang_nr=1 definiert. Die restlichen werden mit RANK ermittelt: |
|
-- (case when studiengang_nr is not null then studiengang_nr |
|
-- else |
|
-- dense_rank () over (partition by semester, st_studstamm_nr,studiengang_nr order by |
|
-- (case when studiengangs_typ_kb='TSG' then st_absz_nr else st_studium_nr end ))+1 |
|
-- end) |
|
-- as studiengang_nr, |
|
cast (NULL as int) as studiengang_nr2, |
|
(case when primaerflag =1 then 1 |
|
else |
|
dense_rank () over (partition by semester, st_studstamm_nr,studiengang_nr |
|
order by primaerflag desc, fach_nr) |
|
end) as fach_nr, |
|
unikey, |
|
beginn_datum as anfdat, |
|
(case when studienstatustyp not in ('Z','X') then null::date |
|
else gueltig_ab end) as ruebeudat, |
|
(case when studienstatustyp in ('Z','X') then gueltig_ab |
|
else null::date end) as endedat, |
|
studienform_statistik_code as stufrm, |
|
kz_rueck_beur_ein, |
|
1 as summe |
|
from tmp_studien2 |
|
--where semester = 20202 |
|
-- and matrikelnummer = '5847370' |
|
where kz_rueck_beur_ein is null |
|
; |
|
|
|
drop table tmp_studien2; |
|
drop table tmp_primaer_msg; |
|
drop table temp_tmp_studien2; |
|
|
|
|
|
|