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.
246 lines
6.2 KiB
246 lines
6.2 KiB
--Freemarker Template |
|
<sqlvars> |
|
<sqlvar name="status_werte" type="hashsequence"> |
|
SELECT nr, |
|
bw_sb_stat_nr, |
|
wert, |
|
name |
|
FROM costage_bw_sb_status_werte |
|
; |
|
</sqlvar> |
|
<sqlvar name="beginn_semester"><![CDATA[ |
|
select param_val from unload_params |
|
where param_id='COSTAGE_start_bw_sem'; |
|
]]> |
|
</sqlvar> |
|
</sqlvars> |
|
|
|
|
|
|
|
|
|
<#assign bw_stati = [ |
|
{"kb":"vss", "nr":20,"name":"Formale Zulassungsvoraussetzungen"}, |
|
{"kb":"dok", "nr":30,"name":"Bewerbungsunterlagen"}, |
|
{"kb":"zul", "nr":40,"name":"Zulassung"}, |
|
{"kb":"stpl", "nr":50,"name":"Studienplatzangebot"}, |
|
{"kb":"qvss", "nr":60,"name":"Fachliche Zulassungsvoraussetzungen"}, |
|
{"kb":"abgabe", "nr":80,"name":"Bewerbungsabgabe"}, |
|
{"kb":"evs", "nr":90,"name":"Einschreibungsvoraussetzungen"} |
|
|
|
] /> |
|
|
|
SELECT B.nr, |
|
B.st_studstamm_nr, |
|
B.st_sem_nr, |
|
B.st_studiengang_nr, |
|
B.st_studfrm_nr, |
|
B.bw_sb_svor_nr, |
|
B.bw_zul_art_nr, |
|
B.bw_typ_kb, |
|
B.zurueckgezogen_am, |
|
B.fristbeginn, |
|
B.fristende, |
|
B.einstiegs_fachsemester, |
|
B.fachsemester_gewaehlt, |
|
B.abgeschickt_datum, |
|
E.semester_tid as semester, |
|
S.matrikelnummer, |
|
<#foreach bw_status in bw_stati> |
|
null::char(1) as status_${bw_status.kb} , |
|
null::char(1) as status_msg_${bw_status.kb} , |
|
</#foreach> |
|
null::char(10) as stufrm, |
|
null::char(10) as unikey, |
|
null::integer as studiengang_nr, |
|
null::integer as fach_nr, |
|
null::integer as st_studiengang_nr_msg, |
|
null::integer as bw_bewerbng_nr_msg, |
|
null::char(10) as bew_kz, |
|
null::char(10) as zul_kz, |
|
null::char(10) as annahme_kz, |
|
null::char(10) as einschreib_kz, |
|
null::CHAR(10) as hzb_art , |
|
null::DATE as hzb_datum , |
|
null::FLOAT as hzb_note , |
|
null::INTEGER as hzb_ort , |
|
null::INTEGER as hzb_staat , |
|
null::INTEGER as hzb_st_land_nr |
|
|
|
into temp tmp_bew |
|
FROM costage_st_semester E, costage_bw_bewerbungen B |
|
left outer join costage_st_studierendenstammdaten S |
|
on (B.st_studstamm_nr=S.st_studstamm_nr) |
|
where E.st_sem_nr=B.st_sem_nr |
|
--and B.bw_typ_kb not in ('MBW') --keine Mehrfachbewerbung |
|
--and B.st_studstamm_nr=1186303 |
|
and E.semester_tid >= ${beginn_semester} |
|
; |
|
--klappt nicht weil nicht eindeutig: |
|
-- update tmp_bew set bw_bewerbng_nr_msg=K.bw_bewrbng_nr |
|
-- from costage_bw_bewerbung_kombination K |
|
-- where K.bw_bewrbng_nr_teil=tmp_bew.nr |
|
-- and tmp_bew.bw_typ_kb='TBW' |
|
-- ; |
|
|
|
-- update tmp_bew set st_studiengang_nr_msg=B.st_studiengang_nr |
|
-- from costage_bw_bewerbungen B |
|
-- where B.nr=tmp_bew.bw_bewerbng_nr_msg |
|
-- and tmp_bew.bw_typ_kb='TBW' |
|
-- ; |
|
|
|
update tmp_bew set unikey=K.unikey |
|
from costage_st_studiengaenge G, costage_st_studienkennzahlen K |
|
where G.st_studiengang_nr=tmp_bew.st_studiengang_nr |
|
and K.st_skz_nr=G.st_skz_nr |
|
; |
|
|
|
update tmp_bew set stufrm=F.statistik_code |
|
from costage_st_studienformen F |
|
where F.nr=tmp_bew.st_studfrm_nr |
|
; |
|
|
|
<#foreach bw_status in bw_stati> |
|
<#foreach wert in status_werte> |
|
<#if bw_status.nr == wert.bw_sb_stat_nr> |
|
-- update tmp_bew set status_${bw_status.kb}=S.wert |
|
-- from costage_bw_bewerb_sb_status S |
|
-- where tmp_bew.nr=S.bw_bewerbng_nr |
|
-- and S.bw_sb_stat_nr =${bw_status.nr} |
|
-- ; |
|
|
|
update tmp_bew set status_${bw_status.kb}='${wert.wert}' |
|
where exists (select S.bw_bewerbng_nr |
|
from costage_bw_bewerb_sb_status S |
|
where tmp_bew.nr=S.bw_bewerbng_nr |
|
and S.bw_sb_stat_nr =${bw_status.nr} |
|
and S.wert='${wert.wert}') |
|
; |
|
|
|
|
|
-- update tmp_bew set status_msg_${bw_status.kb}=S.wert |
|
-- from costage_bw_bewerb_sb_status S |
|
-- where tmp_bew.bw_bewerbng_nr_msg=S.bw_bewerbng_nr |
|
-- and S.bw_sb_stat_nr =${bw_status.nr} |
|
-- ; |
|
|
|
--Zum Zeitpunkt der Einschreibung/Annahme muss der Status aus dem MSG kommen. |
|
--Die Zuordnung zum MSG ist aber nicht eindeutig. Beispiel Testfall hat in dem TSG Philosophie |
|
--zwei mögliche MBW-Kombinationen. Daher mit exists abfragen, ob mind. ein MBW den jew. Status=J hat: |
|
|
|
|
|
update tmp_bew set status_msg_${bw_status.kb}='${wert.wert}' |
|
where exists (select S.bw_bewerbng_nr |
|
from costage_bw_bewerb_sb_status S, costage_bw_bewerbung_kombination K |
|
where bw_bewrbng_nr_teil=tmp_bew.nr |
|
and K.bw_bewrbng_nr=S.bw_bewerbng_nr |
|
and S.bw_sb_stat_nr =${bw_status.nr} |
|
and S.wert='${wert.wert}') |
|
and bw_typ_kb='TBW' |
|
; |
|
-- update tmp_bew set status_msg_${bw_status.kb}='J' |
|
-- where exists (select S.bw_bewerbng_nr |
|
-- from costage_bw_bewerb_sb_status S |
|
-- where tmp_bew.bw_bewerbng_nr_msg=S.bw_bewerbng_nr |
|
-- and S.bw_sb_stat_nr =${bw_status.nr} |
|
-- and S.wert='J') |
|
-- ; |
|
</#if> |
|
</#foreach> |
|
</#foreach> |
|
|
|
|
|
update tmp_bew set bew_kz='J' where status_vss='J'; |
|
; |
|
update tmp_bew set zul_kz='J' where status_zul='J'; |
|
; |
|
--beim MSG annahme_kz/einschreib_kz noch runterschreiben auf TSG |
|
update tmp_bew set status_stpl='J' where status_msg_stpl='J'; |
|
update tmp_bew set status_evs='J' where status_msg_evs='J'; |
|
|
|
update tmp_bew set annahme_kz='J' where status_stpl='J' |
|
; |
|
update tmp_bew set einschreib_kz='J' where status_evs='J' |
|
; |
|
|
|
|
|
--TODO: |
|
update tmp_bew |
|
set hzb_art=H.hzb_art_stla_code, |
|
hzb_datum=H.zeugnisdatum, |
|
hzb_note=H.hzb_note, |
|
hzb_ort=H.land_sub_r_nr, |
|
hzb_st_land_nr=H.land_nr |
|
from costage_bw_bewerb_hzb H, costage_bw_bewerbungen BW |
|
where tmp_bew.st_studstamm_nr = BW.st_studstamm_nr |
|
and BW.nr=H.bw_bewrbng_nr |
|
; |
|
update tmp_bew set hzb_staat=val(L.statistik_code) |
|
from costage_st_laender L |
|
where L.st_land_nr=tmp_bew.hzb_st_land_nr |
|
; |
|
|
|
delete from costage_bw_antr_aggr |
|
where semester >= ${beginn_semester} |
|
; |
|
insert into costage_bw_antr_aggr( nr, |
|
st_studstamm_nr, |
|
matrikelnummer, |
|
semester, |
|
st_studiengang_nr, |
|
st_studiengang_nr_msg, |
|
bw_bewerbng_nr_msg, |
|
unikey, |
|
studiengang_nr, |
|
fach_nr, |
|
fachsemester, |
|
stufrm, |
|
bw_sb_svor_nr, |
|
bw_zul_art_nr, |
|
bw_typ_kb, |
|
<#foreach bw_status in bw_stati> |
|
status_${bw_status.kb}, |
|
</#foreach> |
|
bew_kz, |
|
zul_kz, |
|
annahme_kz, |
|
einschreib_kz, |
|
hzb_art, |
|
hzb_datum, |
|
hzb_note, |
|
hzb_ort, |
|
hzb_staat, |
|
summe |
|
) |
|
SELECT nr, |
|
st_studstamm_nr, |
|
val(matrikelnummer), |
|
semester, |
|
st_studiengang_nr, |
|
st_studiengang_nr_msg, |
|
bw_bewerbng_nr_msg, |
|
unikey, |
|
studiengang_nr, |
|
fach_nr, |
|
einstiegs_fachsemester as fachsemester, |
|
stufrm, |
|
bw_sb_svor_nr, |
|
bw_zul_art_nr, |
|
bw_typ_kb, |
|
<#foreach bw_status in bw_stati> |
|
status_${bw_status.kb}, |
|
</#foreach> |
|
bew_kz, |
|
zul_kz, |
|
annahme_kz, |
|
einschreib_kz, |
|
hzb_art, |
|
hzb_datum, |
|
hzb_note, |
|
hzb_ort, |
|
hzb_staat, |
|
count(*) |
|
from tmp_bew |
|
group by 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31; |
|
|
|
drop table tmp_bew; |
|
|
|
|