COSTAGE/src-modules/module/costage/hilfstabellen/costage_st_pruef_aggr_fuellen.sql

--freemarker template
<sqlvars>
<sqlvar name="beginn_semester"><![CDATA[
select param_val from unload_params
where param_id='COSTAGE_start_leistungen_sem';
]]>
</sqlvar>
</sqlvars>


--Leistungen mit Veranstaltung -> Semester:
SELECT 
L.st_studium_nr,
S.st_studstamm_nr,
L.matrikelnummer::integer as matrikelnummer,
null::integer as studiengang_tid,
null::integer as st_studiengang_nr,
null::integer as st_studiengang_nr_tsg,
null::integer as studiengang_nr,
null::integer as fach_nr,
null::integer as st_studienstatus_nr,
null::integer as semester,
E.semester_tid as semester_veranst,
null::integer as fachsemester,
null::integer as fachsemester_tsg,
null::integer as st_studium_nr_tsg,
null::varchar(255) as unikey,
L.leistung_nr,
L.leistungstyp_nr,
L.leistungsdetailtyp_kb,
L.studienidentifikator,
L.stp_sp_nr,
L.veranstaltung_nummer,
L.veranstaltung_semester,
L.veranstaltung_titel,
L.veranstaltung_art,
L.semesterstunden,
L.unterrichtseinheiten,
L.thema_individuell,
L.pv_notenskala_nr,
L.note,
L.note_name,
L.note_zahl,
L.pv_pruef_zusatz_typen_nr,
L.note_zusatz,
L.note_zusatz_name,
L.note_positiv_flag,
L.note_negativ_flag,
L.pv_notenskala_typ_nr,
L.pv_notenskala_typ_kb,
L.pv_notenskala_typ_name,
L.credits,
date(L.leistungsdatum) as leistungsdatum,
L.anerkannt_flag,
L.zusatzleistung_flag,
L.leistungsstatus_nr,
L.leistungsstatus_map_kb,
null::char(10) as leistungsstatus_map_kb_msg,
null::float as note_msg,
2::integer as abschnitt, --provisorisch erstmal nur Hauptprüfungen
null::integer as pnr,
null::integer as leistung_nr_msg,
null::integer as st_studiengang_nr_msg,
null::varchar as studienidentifikator_esg_msg,
null::date as leistungsdatum_msg,
null::integer as semester_msg,
L.spov_abschluss as po_stp_stp_version_kb_esg_tsg_msg,
null::varchar as po_stp_stp_version_kb,
null::varchar as po_stp_stp_version_kb_tsg,
null::char(10) as studiengangs_typ_kb,
0::integer as abschlussarbeit,
null:: CHAR(10) as skz_key_abschlussarbeit,
null::VARCHAR(255) as po_stp_stp_version_kb_abschlussarbeit 
into temp tmp_pruef
FROM costage_st_semester E, costage_leistungen_typen T, costage_leistungen L, costage_st_studierendenstammdaten S
where E.semester_id=L.veranstaltung_semester
and T.leistungstyp_nr=L.leistungstyp_nr
and S.matrikelnummer=L.matrikelnummer
and S.matrikelnummer in (select ST.matrikelnummer
from costage_st_studierendenstammdaten ST
where ${COSTAGE_STUDENT_FILTER})
-- TSAB/AB:
and T.kurzbezeichnung in ('AP','AAP') --Abschlussprüfung/Anerkannte Abschlussprüfung
and L.leistungsstatus_nr in (4,2) --Letztgültig/Final/gültige Leistung
--px_leistungen_status_typen_v:
--4^F^Letztgültig^Final^
--1^P^in Bearbeitung^in process^
--2^V^gültige Leistung^valid^
--3^I^ungültige Leistung^invalid^
--5^D^gelöscht^deleted^
and E.semester_tid  >= ${beginn_semester}
 ;

--Leistungen ohne Veranstaltungssemester gehen über Prüfungsdatum:
insert into tmp_pruef
SELECT 
L.st_studium_nr,
S.st_studstamm_nr,
L.matrikelnummer::integer as matrikelnummer,
null::integer as studiengang_tid,
null::integer as st_studiengang_nr,
null::integer as st_studiengang_nr_tsg,
null::integer as studiengang_nr,
null::integer as fach_nr,
null::integer as st_studienstatus_nr,
E.semester_tid as semester,
null::integer as semester_veranst,
null::integer as fachsemester,
null::integer AS fachsemester_tsg,
null::integer as st_studium_nr_tsg,
null::varchar(255) as unikey,
L.leistung_nr,
L.leistungstyp_nr,
L.leistungsdetailtyp_kb,
L.studienidentifikator,
L.stp_sp_nr,
L.veranstaltung_nummer,
L.veranstaltung_semester,
L.veranstaltung_titel,
L.veranstaltung_art,
L.semesterstunden,
L.unterrichtseinheiten,
L.thema_individuell,
L.pv_notenskala_nr,
L.note,
L.note_name,
L.note_zahl,
L.pv_pruef_zusatz_typen_nr,
L.note_zusatz,
L.note_zusatz_name,
L.note_positiv_flag,
L.note_negativ_flag,
L.pv_notenskala_typ_nr,
L.pv_notenskala_typ_kb,
L.pv_notenskala_typ_name,
L.credits,
date(L.leistungsdatum) as leistungsdatum,
L.anerkannt_flag,
L.zusatzleistung_flag,
L.leistungsstatus_nr,
L.leistungsstatus_map_kb,
null::char(10) as leistungsstatus_map_kb_msg,
null::float as note_msg,
2::integer as abschnitt, --provisorisch erstmal nur Hauptprüfungen
null::integer as pnr,
null::integer as leistung_nr_msg,
null::integer as st_studiengang_nr_msg,
null::varchar as studienidentifikator_esg_msg,
null::date as leistungsdatum_msg,
null::integer as semester_msg,
L.spov_abschluss as po_stp_stp_version_kb_esg_tsg_msg,
null::Varchar as po_stp_stp_version_kb,
null::varchar as po_stp_stp_version_kb_tsg,
null::char(10) as studiengangs_typ_kb,
0::integer as abschlussarbeit,
null:: CHAR(10) as skz_key_abschlussarbeit,
null::VARCHAR(255) as po_stp_stp_version_kb_abschlussarbeit 
FROM costage_st_semester E, costage_leistungen_typen T, costage_leistungen L, costage_st_studierendenstammdaten S
where L.leistungsdatum between E.semester_anfang and  E.semester_ende
   and L.veranstaltung_semester is null
and T.leistungstyp_nr=L.leistungstyp_nr
and S.matrikelnummer=L.matrikelnummer
and S.matrikelnummer in (select ST.matrikelnummer
from costage_st_studierendenstammdaten ST
where ${COSTAGE_STUDENT_FILTER})
-- TSAB/AB:
and T.kurzbezeichnung in ('AP','AAP') --Abschlussprüfung/Anerkannte Abschlussprüfung
and L.leistungsstatus_nr in (4,2) --Letztgültig/Final/gültige Leistung
and E.semester_tid  >= ${beginn_semester}
 ;
 
update tmp_pruef set semester=E.semester_tid
 FROM costage_st_semester E
 where tmp_pruef.leistungsdatum between E.semester_anfang and  E.semester_ende
 and tmp_pruef.semester is null;
update tmp_pruef set semester_veranst=semester
 where semester_veranst is null;
 
update tmp_pruef set st_studiengang_nr=S.st_studiengang_nr
from costage_st_studien S
where S.st_studium_nr=tmp_pruef.st_studium_nr
and S.st_studstamm_nr=tmp_pruef.st_studstamm_nr
and tmp_pruef.leistungsdetailtyp_kb!='TSAB'
;
update tmp_pruef set st_studiengang_nr_tsg=S.st_studiengang_nr,
st_studium_nr_tsg=S.st_studium_nr
from costage_st_studien S
where S.st_studium_nr=tmp_pruef.st_studium_nr
and S.st_studstamm_nr=tmp_pruef.st_studstamm_nr
and tmp_pruef.leistungsdetailtyp_kb='TSAB'
;

--studiengangs_typ_kb ermitteln:
update tmp_pruef set studiengangs_typ_kb=G.studiengangs_typ_kb
from costage_st_studiengaenge G
where G.st_studiengang_nr=tmp_pruef.st_studiengang_nr;

update tmp_pruef set studiengangs_typ_kb=G.studiengangs_typ_kb
from costage_st_studiengaenge G
where G.st_studiengang_nr=tmp_pruef.st_studiengang_nr_tsg;

--bei TSG die Nr. des pers. Studiums zuweisen, in dem die Abschlussarbeit war:
update tmp_pruef set abschlussarbeit=1
from costage_leistungen L
where L.leistungsdetailtyp_kb='BAKARBN'
and tmp_pruef.st_studium_nr_tsg=L.abs_fach_st_studium_nr
and tmp_pruef.studiengangs_typ_kb='TSG'
and tmp_pruef.leistungsdetailtyp_kb='TSAB'
;

--Studiengangs-TID ermitteln:
update tmp_pruef set studiengang_tid=D.tid
from costage_dim_studiengang D
where D.st_studiengang_nr=tmp_pruef.st_studiengang_nr
and tmp_pruef.leistungsdetailtyp_kb!='TSAB';

update tmp_pruef set studiengang_tid=D.tid
from costage_dim_studiengang D
where D.st_studiengang_nr=tmp_pruef.st_studiengang_nr_tsg
and tmp_pruef.leistungsdetailtyp_kb='TSAB';


--Fachsemester aus Sem. des Prüfungsdatums holen:
update tmp_pruef set 
fachsemester = S.fachsemester
from  costage_st_studien_aggr S
where S.semester=tmp_pruef.semester
and S.st_studium_nr=tmp_pruef.st_studium_nr
and S.matrikelnummer=tmp_pruef.matrikelnummer
;
update tmp_pruef set 
fachsemester_tsg = S.fachsemester
from  costage_st_studien_aggr S
where S.semester=tmp_pruef.semester
and S.st_studium_nr_tsg=tmp_pruef.st_studium_nr
and S.matrikelnummer=tmp_pruef.matrikelnummer
and leistungsdetailtyp_kb ='TSAB'
;

--bei TSAB die Nr. des persönlichen Studiums aus dem Studienverlauf holen:
update tmp_pruef set st_studium_nr=S.st_studium_nr,
st_studiengang_nr=S.st_studiengang_nr,
studienidentifikator=S.studiengangs_identifikator
from  costage_st_studien_aggr S
where S.semester=tmp_pruef.semester
and S.st_studiengang_nr_tsg=tmp_pruef.st_studiengang_nr_tsg
and S.matrikelnummer=tmp_pruef.matrikelnummer
and leistungsdetailtyp_kb ='TSAB'
;

--Prüfung kann nach letztem eingeschr. Semester sein, daher zuerst letztes Sem. ermitteln:
select S.st_studium_nr,
S.matrikelnummer,
null::integer as st_studienstatus_nr,
null::integer as studiengang_nr,
null::integer as fach_nr,
null::char(10) as unikey,
null::integer as st_studiengang_nr_msg,
max(S.semester) as semester
into temp tmp_max_sem
from costage_st_studien_aggr S, tmp_pruef P
where S.st_studium_nr=P.st_studium_nr
and S.matrikelnummer=P.matrikelnummer
group by 1,2
;
--Dann Studium vom letzten Semester in den Prüfungs-Datensatz schreiben:
--TODO langsam:
update tmp_pruef set 
st_studienstatus_nr=S.st_studienstatus_nr,
--fachsemester = S.fachsemester, --wird oben ermittelt, ab 0.6 nicht mehr aus letztem Studiums-Semester
studiengang_nr=S.studiengang_nr,
st_studiengang_nr=S.st_studiengang_nr,
st_studiengang_nr_tsg=S.st_studiengang_nr_tsg,
fach_nr=S.fach_nr,
unikey=S.unikey
from  tmp_max_sem M, costage_st_studien_aggr S
where S.matrikelnummer=M.matrikelnummer
and S.st_studium_nr=M.st_studium_nr
and S.semester=M.semester
and S.st_studium_nr_tsg=tmp_pruef.st_studium_nr
and S.matrikelnummer=tmp_pruef.matrikelnummer
and leistungsdetailtyp_kb ='TSAB'
;
update tmp_pruef set 
st_studienstatus_nr=S.st_studienstatus_nr,
--fachsemester = S.fachsemester, --wird oben ermittelt, ab 0.6 nicht mehr aus letztem Studiums-Semester
studiengang_nr=S.studiengang_nr,
st_studiengang_nr=S.st_studiengang_nr,
fach_nr=S.fach_nr --,
--unikey=S.unikey
from  tmp_max_sem M, costage_st_studien_aggr S
where S.matrikelnummer=M.matrikelnummer
and S.st_studium_nr=M.st_studium_nr
and S.semester=M.semester
and S.st_studium_nr=tmp_pruef.st_studium_nr
and S.matrikelnummer=tmp_pruef.matrikelnummer
and leistungsdetailtyp_kb !='TSAB'
;
--TODO: bei MSG leer, bei EST und TSG gefüllt

drop table tmp_max_sem;

--MSG-Daten nachladen:
--zuerst Kopie nur mit MSG:
select * into temp tmp_pruef2
from tmp_pruef
where leistungsdetailtyp_kb !='TSAB'
; 

--Dann Nr., Datum, Note und Semester des MSG nachladen (TODO: ggf. noch Fachsemester?)
update tmp_pruef set leistung_nr_msg=T.leistung_nr,
leistungsdatum_msg=T.leistungsdatum,
semester_msg=T.semester,
leistungsstatus_map_kb_msg=T.leistungsstatus_map_kb,
note_msg=(case when T.pv_notenskala_typ_kb in ('DE1','DE2','DE3','DE4','DE5','DE6','DE7') or T.pv_notenskala_typ_kb is null  then T.note_zahl else null::float end),
studienidentifikator_esg_msg=T.studienidentifikator,
st_studium_nr=T.st_studium_nr,
po_stp_stp_version_kb=T.po_stp_stp_version_kb_esg_tsg_msg
from tmp_pruef2 T 
where tmp_pruef.st_studiengang_nr=T.st_studiengang_nr
and tmp_pruef.leistungsdetailtyp_kb ='TSAB'
and T.matrikelnummer=tmp_pruef.matrikelnummer

;

--Fallback wenn der MSG bei TSG nicht gefunden wird, weil der MSG gewechselt hat oder der TSG im jew. Semester der Prüfung geschlossen wurde:
--nach allen verfügbaren MSG gehen
update tmp_pruef set leistung_nr_msg=T.leistung_nr,
leistungsdatum_msg=T.leistungsdatum,
semester_msg=T.semester,
leistungsstatus_map_kb_msg=T.leistungsstatus_map_kb,
note_msg=(case when T.pv_notenskala_typ_kb in ('DE1','DE2','DE3','DE4','DE5','DE6','DE7') or T.pv_notenskala_typ_kb is null  then T.note_zahl else null::float end),
studienidentifikator_esg_msg=T.studienidentifikator,
st_studium_nr=T.st_studium_nr,
po_stp_stp_version_kb=T.po_stp_stp_version_kb_esg_tsg_msg
from tmp_pruef2 T, costage_st_studiengaenge_mtsg M 
where tmp_pruef.st_studiengang_nr_tsg=M.st_studiengang_nr_tsg
and M.st_studiengang_nr_msg=T.st_studiengang_nr
and tmp_pruef.leistungsdetailtyp_kb ='TSAB'
and tmp_pruef.leistung_nr_msg is null
and T.matrikelnummer=tmp_pruef.matrikelnummer
;

--Leistungsdatum determiniert Semester auch bei MSG:
-- update tmp_pruef set semester=E.semester_tid
 -- FROM costage_st_semester E
 -- where tmp_pruef.leistungsdatum_msg between E.semester_anfang and  E.semester_ende
 -- and tmp_pruef.leistungsdetailtyp_kb !='TSAB'
 -- and tmp_pruef.leistungsdatum_msg is not null;
 
update tmp_pruef set leistung_nr_msg=leistung_nr,
leistungsdatum_msg=leistungsdatum,
semester_msg=semester,
leistungsstatus_map_kb_msg=leistungsstatus_map_kb,
studienidentifikator_esg_msg=studienidentifikator
where tmp_pruef.leistungsdetailtyp_kb !='TSAB'
;
--Fachsemester bei MSG gibt es in CO nicht. Daher die Regel:
--die Fachsemesterzahl sollte die höchste FS-Zahl aus allen TSG enthalten: 
select matrikelnummer,st_studiengang_nr,max(semester) as semester_max,max(fachsemester_tsg) as fachsem_max
into temp tmp_fachsem_msg
from tmp_pruef
where leistungsdetailtyp_kb='TSAB'
and st_studiengang_nr is not null
group by 1,2;

update tmp_pruef set fachsemester=T.fachsem_max
from tmp_fachsem_msg T
where T.matrikelnummer=tmp_pruef.matrikelnummer
and T.st_studiengang_nr=tmp_pruef.st_studiengang_nr
and T.semester_max <=tmp_pruef.semester
and tmp_pruef.leistungsdetailtyp_kb='BAKN'
;

update tmp_pruef set fachsemester=T.fachsem_max
from tmp_fachsem_msg T
where T.matrikelnummer=tmp_pruef.matrikelnummer
and T.st_studiengang_nr=tmp_pruef.st_studiengang_nr
and T.semester_max >=tmp_pruef.semester
and tmp_pruef.leistungsdetailtyp_kb='TSAB'
;

drop table tmp_fachsem_msg;

--unikey setzen, wenn noch nicht vorhanden (z.B. bei MSG):
update tmp_pruef set unikey=K.unikey
from costage_st_studiengaenge G, costage_st_studienkennzahlen K
where G.st_studiengang_nr=tmp_pruef.st_studiengang_nr
and K.st_skz_nr=G.st_skz_nr
and tmp_pruef.unikey is null
;
--Nun bei TSG:
update tmp_pruef set unikey=K.unikey
from costage_st_studiengaenge G, costage_st_studienkennzahlen K
where G.st_studiengang_nr=tmp_pruef.st_studiengang_nr_tsg
and K.st_skz_nr=G.st_skz_nr
and tmp_pruef.unikey is null
;


--MSG Prüfungen können keine Fachnr. haben, daher entfernen:
update tmp_pruef set fach_nr=null
where tmp_pruef.leistungsdetailtyp_kb!='TSAB' 
and exists (select G.st_studiengang_nr
from costage_st_studiengaenge G
where G.st_studiengang_nr=tmp_pruef.st_studiengang_nr
and G.studiengangs_typ_kb='MSG')
;

--bei MSG das Fach und SPO-Version der abschlussarbeit ermitteln:

--zuerst Kopie nur mit TSG der Abschlussarbeit:
select * into temp tmp_pruef3
from tmp_pruef
where leistungsdetailtyp_kb ='TSAB'
and abschlussarbeit=1
; 

create index tmp_ix_123 on tmp_pruef3(studiengang_tid);
create index tmp_ix_1234 on tmp_pruef3(st_studium_nr);


update tmp_pruef set skz_key_abschlussarbeit=G.skz_key,
po_stp_stp_version_kb_abschlussarbeit=T.po_stp_stp_version_kb
from tmp_pruef3 T, costage_dim_studiengang G
where G.tid=T.studiengang_tid
and T.st_studium_nr=tmp_pruef.st_studium_nr
and tmp_pruef.studiengangs_typ_kb='MSG'
;


delete from costage_st_pruef_aggr
where semester >= ${beginn_semester}
or semester_tsg >= ${beginn_semester};

insert into costage_st_pruef_aggr(st_studium_nr,
st_studium_nr_tsg,
       st_studstamm_nr,
       matrikelnummer,
       semester,
       semester_tsg,
       semester_veranst,
       studiengang_tid,
       st_studiengang_nr,
       st_studiengang_nr_tsg,
       studiengang_nr,
       studiengangs_typ_kb,
       fach_nr,
       unikey,
       studienidentifikator,
       studienidentifikator_tsg,
       fachsemester,
       fachsemester_tsg,
       leistungstyp_nr,
       leistungsdetailtyp_kb,
       pnr,
       abschnitt,
       stp_sp_nr,
       po_stp_stp_version_kb,
       po_stp_stp_version_kb_tsg,
       note,
       note_tsg,
       credits,
       anerkannt_flag,
       leistungsstatus_nr,
       leistungsstatus_map_kb,
       leistungsstatus_map_kb_tsg,
       leistung_nr,
       leistung_nr_tsg,
       leistungsdatum,
       leistungsdatum_tsg,
       skz_key_abschlussarbeit,
       po_stp_stp_version_kb_abschlussarbeit,
       summe)
select 
       st_studium_nr,
       st_studium_nr_tsg,
       st_studstamm_nr,
       matrikelnummer,
       (case when leistungsdetailtyp_kb='TSAB' then semester_msg
       else semester end) as semester ,
       (case when leistungsdetailtyp_kb='TSAB' then semester
       else null::integer end) as semester_tsg ,
       semester_veranst,
       studiengang_tid,
       st_studiengang_nr,
       st_studiengang_nr_tsg,
       studiengang_nr,
       studiengangs_typ_kb,
       fach_nr,
       unikey,
       substring(studienidentifikator_esg_msg from 1 for 255) as studienidentifikator,
       (case when leistungsdetailtyp_kb='TSAB' then substring(studienidentifikator from 1 for 255)
       else null::varchar(255) end) as studienidentifikator_tsg,
       fachsemester,
       fachsemester_tsg,
       leistungstyp_nr,
       leistungsdetailtyp_kb,
       pnr,
       abschnitt,
       stp_sp_nr,
       (case when leistungsdetailtyp_kb='TSAB' then po_stp_stp_version_kb else po_stp_stp_version_kb_esg_tsg_msg end) as po_stp_stp_version_kb,
       (case when leistungsdetailtyp_kb='TSAB' then po_stp_stp_version_kb_esg_tsg_msg
       else null::varchar end) as po_stp_stp_version_kb_tsg ,
       (case when leistungsdetailtyp_kb='TSAB' then note_msg
       else (
       case when pv_notenskala_typ_kb in ('DE1','DE2','DE3','DE4','DE5','DE6','DE7') 
       or pv_notenskala_typ_kb is null  
       then note_zahl else null::float end) end) as note,
       (case when leistungsdetailtyp_kb='TSAB' and (pv_notenskala_typ_kb in ('DE1','DE2','DE3','DE4','DE5','DE6','DE7') 
       or pv_notenskala_typ_kb is null)  
       then note_zahl else null::float end) as note_tsg,
       credits,
       anerkannt_flag,
       leistungsstatus_nr,
       (case when leistungsdetailtyp_kb='TSAB' then leistungsstatus_map_kb_msg
       else leistungsstatus_map_kb end) as leistungsstatus_map_kb,
       (case when leistungsdetailtyp_kb='TSAB' then leistungsstatus_map_kb
       else  null::char(10) end) as leistungsstatus_map_kb_tsg,
       (case when leistungsdetailtyp_kb='TSAB' then leistung_nr_msg
       else leistung_nr end) as leistung_nr,
       (case when leistungsdetailtyp_kb='TSAB' then leistung_nr
       else null::integer end) as leistung_nr_tsg,
       (case when leistungsdetailtyp_kb='TSAB' then leistungsdatum_msg
       else leistungsdatum end) as leistungsdatum ,
      (case when leistungsdetailtyp_kb='TSAB' then leistungsdatum
       else null::date end) as leistungsdatum_tsg ,
       skz_key_abschlussarbeit,
       po_stp_stp_version_kb_abschlussarbeit,
       1 as summe
       from tmp_pruef
       --group by leistungsdatum,studiengangs_typ_kb,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
       ;

       
--TSG Daten nachtragen:
update costage_st_pruef_aggr set 
studiengang_nr=S.studiengang_nr,
st_studiengang_nr=S.st_studiengang_nr,
st_studiengang_nr_tsg=S.st_studiengang_nr_tsg,
--fach_nr=S.fach_nr,
unikey=S.unikey,
fachsemester_tsg=S.fachsemester
from  costage_st_studien_aggr S
where 1=1
and S.st_studium_nr_tsg=costage_st_pruef_aggr.st_studium_nr_tsg
and S.matrikelnummer=costage_st_pruef_aggr.matrikelnummer
and costage_st_pruef_aggr.leistungsdetailtyp_kb ='TSAB'
and costage_st_pruef_aggr.semester_tsg=S.semester
;
       
drop table tmp_pruef;
drop table tmp_pruef2;
drop table tmp_pruef3;