Berichte zur ZSL-Mittelverteilung in SuperX-KENN
http://www.superx-projekt.de/doku/kenn_modul/zsl/
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.
296 lines
10 KiB
296 lines
10 KiB
24570^ZSL-Mittelverteilung (Teiltabelle)^--(c) 2023 Jutta Otten jutta.otten@verwaltung.uni-stuttgart.de & D.Quathamer\ |
|
--Freemarker Template\ |
|
<#include "SQL_lingua_franca"/>\ |
|
<#include "SuperX_general"/>\ |
|
<sqlvars>\ |
|
<sqlvar name="jahr" type="hashsequence"><![CDATA[\ |
|
SELECT ord as key,name from aggregierung\ |
|
where kategorie='KENN-Jahr'\ |
|
/* and ord >= <<Jahr von>> */\ |
|
/* and ord <= <<Jahr bis>> */\ |
|
order by 1\ |
|
;\ |
|
]]>\ |
|
</sqlvar>\ |
|
<sqlvar name="hs_nr" type="hashsequence"><![CDATA[\ |
|
SELECT hs_nr as key,name from kenn_hochschulen\ |
|
where hs_nr::char(10) in <@printkeys .vars["Hochschule"].allNeededKeys />\ |
|
and traegerschaft='ST'\ |
|
order by 2\ |
|
;\ |
|
]]>\ |
|
</sqlvar>\ |
|
<sqlvar name="fgr" type="hashsequence"><![CDATA[\ |
|
SELECT apnr as key,drucktext as name from kenn_dim_fgr\ |
|
where fg_studierende is not null\ |
|
order by 2\ |
|
;\ |
|
]]>\ |
|
</sqlvar>\ |
|
<sqlvar name="abgrp" type="hashsequence"><![CDATA[\ |
|
SELECT 'BA'::char(2) as key,'Bachelor'::varchar(255) as name from xdummy\ |
|
union\ |
|
SELECT 'MA','Master' from xdummy\ |
|
union\ |
|
SELECT 'ST','Staatsexamen' from xdummy\ |
|
order by 1\ |
|
;\ |
|
]]>\ |
|
</sqlvar>\ |
|
</sqlvars>\ |
|
<#assign fact_table="kenn_stg_aggr_astat" />\ |
|
<#assign fact_filter="1=1" />\ |
|
<#assign semesterfeld="sem_rueck_beur_ein" />\ |
|
<#assign aggrfunction="sum(summe)" />\ |
|
<#assign aggrcoltype="integer" />\ |
|
\ |
|
<#assign graph_col_summe= "" />\ |
|
<#assign graph_col= "col" />\ |
|
/* <#assign graph_col=<<Spaltenvisualisierung>> /> */\ |
|
<#if graph_col=="_graph">\ |
|
<#assign graph_col_summe= "|" />\ |
|
</#if>\ |
|
\ |
|
<#if "<<Gewichtung>>"=="1">\ |
|
<#assign aggrcoltype="float" />\ |
|
<#assign aggrfunction="sum(summe::"+aggrcoltype+"*faktor)" />\ |
|
</#if>\ |
|
\ |
|
<#if "<<Ausgabetabelle>>" == "stud_hs1" >\ |
|
<#assign jahrfeld="akad_jahr_anf" />\ |
|
<#assign semesterfilter="substring('' || S.tid from 5 for 1) in ('1','2')" />\ |
|
<#assign fact_filter=fact_filter + " and kz_rueck_beur_ein < 4" /> -- ohne exmatr., ohne beurlaubten\ |
|
<#assign fact_filter=fact_filter + " and studiengang_nr = 1 and fach_nr = 1" /> -- Kopf mit 1. Studiengang, 1. Fach\ |
|
<#assign fact_filter=fact_filter + " and hssem = 1" /> -- 1. HS-Sem.\ |
|
<#elseif "<<Ausgabetabelle>>" == "stud_rsz2" >\ |
|
<#assign jahrfeld="val(substring('' || S.tid from 1 for 4))" />\ |
|
<#assign semesterfilter="substring('' || S.tid from 5 for 1)='2'" />\ |
|
<#assign fact_filter=fact_filter + " and kz_rueck_beur_ein < 4" /> -- ohne exmatr., ohne beurlaubten\ |
|
<#assign fact_filter=fact_filter + " and studiengang_nr = 1 and fach_nr = 1" /> -- Kopf mit 1. Studiengang, 1. Fach\ |
|
<#assign fact_filter=fact_filter + " and coalesce(fach_sem_zahl,0) < regel+3" /> -- in RSZ +2\ |
|
<#elseif "<<Ausgabetabelle>>" == "absolv" >\ |
|
<#assign fact_table="kenn_lab_aggr_astat" />\ |
|
<#assign jahrfeld="akad_jahr_absolv" />\ |
|
<#assign semesterfeld="sem_der_pruefung" />\ |
|
<#assign semesterfilter="substring('' || S.tid from 5 for 1) in ('1','2')" />\ |
|
<#assign fact_filter=fact_filter + " and studiengang_nr = 1 and fach_nr = 1" /> -- Kopf mit 1. Studiengang, 1. Fach?\ |
|
<#assign fact_filter=fact_filter + " and pruefung_ergebnis <> 2" /> -- nur erfolgreiche Prüfungen\ |
|
</#if>\ |
|
\ |
|
\ |
|
\ |
|
\ |
|
<#assign staat_filter="1=1" />\ |
|
<#if "<<Staatsangehörigkeit>>"="I">\ |
|
<#assign staat_filter="ca12_staat=0" />\ |
|
<#elseif "<<Staatsangehörigkeit>>"="A">\ |
|
<#assign staat_filter="ca12_staat!=0" />\ |
|
<#elseif "<<Staatsangehörigkeit>>"="EU">\ |
|
<#assign staat_filter="ca12_staat !=0 and 0 <(select count(*) from sos_staat_eu E where val(E.astat)=ca12_staat and "+semesterfeld+" between E.semester_von and E.semester_bis and EU='J') " />\ |
|
<#elseif "<<Staatsangehörigkeit>>"="NEU">\ |
|
<#assign staat_filter="0 <(select count(*) from sos_staat_eu E where val(E.astat)=ca12_staat and "+semesterfeld+" between E.semester_von and E.semester_bis and EU='N') " />\ |
|
</#if>\ |
|
\ |
|
<#assign fieldclause=jahrfeld+" as jahr,hs_nr,hzbart_astat,ca12_staat,fgr,abgrp,"+aggrfunction + " as summe" />\ |
|
<#assign fromclause="kenn_semester S, "+fact_table +" inner join kenn_zsl_ausgleich_kennungen Z on (concat (substring(stufrm from 1 for 1),abschluss)=Z.astat and kennung_<<Ausgabetabelle>>=1) " />\ |
|
\ |
|
<#assign whereclause="S.tid="+semesterfeld+" and " + semesterfilter+" and "+ fact_filter />\ |
|
<#assign whereclause=whereclause+ " and " + jahrfeld+ " >= <<Jahr von>>" />\ |
|
<#assign whereclause=whereclause+ " and " + jahrfeld+ " <= <<Jahr bis>>" />\ |
|
\ |
|
--Default: Schleife über Hochschule\ |
|
<#assign rowdim=<<Zeilen>> />\ |
|
<#assign rowfieldname="<<Zeilen>>" />\ |
|
<#assign coldim=<<Spalten>> />\ |
|
<#assign colfieldname="<<Spalten>>" />\ |
|
\ |
|
<#if rowfieldname!=colfieldname >\ |
|
\ |
|
--start Datentabelle\ |
|
<@selectintotmp \ |
|
select=fieldclause\ |
|
source=fromclause\ |
|
target="tmp_tabelle">\ |
|
-- nested in selectinto muss die where Bedingung u. ggfs. group by etc kommen\ |
|
where hs_nr::char(10) in <@printkeys .vars["Hochschule"].allNeededKeys />\ |
|
and ${whereclause}\ |
|
/* and abschluss in (<<Abschluss>>) */\ |
|
/* and hzbart=<<Hochschulzugangsber.>> */\ |
|
/* and geschlecht = <<Geschlecht>> */\ |
|
and ${staat_filter}\ |
|
group by 1,2,3,4,5,6\ |
|
</@selectintotmp>\ |
|
<@informixnolog/>;\ |
|
\ |
|
\ |
|
<#assign sortnr=1 />\ |
|
--Zuerst Summe:\ |
|
select 1::smallint as ebene ,\ |
|
${sortnr}::integer as sortnr,\ |
|
'Summe'::varchar(255) as levelname,\ |
|
<#if coldim?has_content>\ |
|
<#foreach col in coldim>\ |
|
(case when ${colfieldname}='${col.key}' then sum(summe) else 0 end)::${aggrcoltype} as spalte${col.key},\ |
|
</#foreach>\ |
|
</#if>\ |
|
(case when hzbart_astat in ('39','67','17','47','59','79','76','31','51','96','94','95') \ |
|
and ca12_staat <> 0 then sum(summe) else 0 end)::${aggrcoltype} as summe_ba,\ |
|
(sum(summe))::${aggrcoltype} as summe\ |
|
into temp tmp_tabelle2\ |
|
FROM tmp_tabelle\ |
|
group by 1,2,3,${colfieldname},hzbart_astat,ca12_staat\ |
|
;\ |
|
\ |
|
--dann Levels:\ |
|
<#if rowdim?has_content>\ |
|
<#foreach row in rowdim>\ |
|
<#assign sortnr=sortnr+1 />\ |
|
insert into tmp_tabelle2\ |
|
select 2::smallint as ebene,\ |
|
${sortnr},\ |
|
'${row.name}' as levelname,\ |
|
<#if coldim?has_content>\ |
|
<#foreach col in coldim>\ |
|
(case when ${colfieldname}='${col.key}' then sum(summe) else 0 end)::${aggrcoltype} as spalte${col.key},\ |
|
</#foreach>\ |
|
</#if>\ |
|
(case when hzbart_astat in ('39','67','17','47','59','79','76','31','51','96','94','95') \ |
|
and ca12_staat <> 0 then sum(summe) else 0 end)::${aggrcoltype} as summe_ba,\ |
|
sum(summe)\ |
|
FROM tmp_tabelle\ |
|
where ${rowfieldname}='${row.key}'\ |
|
group by 1,2,3,${colfieldname},hzbart_astat,ca12_staat\ |
|
;\ |
|
</#foreach>\ |
|
</#if>\ |
|
\ |
|
--Aufsummieren:\ |
|
select ebene ,\ |
|
sortnr,\ |
|
levelname,\ |
|
<#if coldim?has_content>\ |
|
<#foreach col in coldim>\ |
|
sum(spalte${col.key})::${aggrcoltype} as spalte${col.key},\ |
|
</#foreach>\ |
|
</#if>\ |
|
sum(summe_ba)::${aggrcoltype} as summe_ba,\ |
|
sum(summe)::${aggrcoltype} as summe\ |
|
into temp tmp_tabelle3\ |
|
FROM tmp_tabelle2\ |
|
group by 1,2,3\ |
|
;\ |
|
\ |
|
drop table tmp_tabelle;\ |
|
drop table tmp_tabelle2;\ |
|
\ |
|
\ |
|
select <#if graph_col=="col">\ |
|
ebene,\ |
|
<#else>\ |
|
ebene as level, --bei Grafiken keine Ebenenanzeige\ |
|
</#if>\ |
|
<#if graph_col?starts_with("_spark")>\ |
|
levelname as _categorySpark,\ |
|
<#else>\ |
|
levelname,\ |
|
</#if>\ |
|
<#if coldim?has_content>\ |
|
<#foreach col in coldim>\ |
|
<#if graph_col=="col">\ |
|
spalte${col.key},\ |
|
<#elseif graph_col?starts_with("_spark")>\ |
|
spalte${col.key} as ${graph_col}${col.key},\ |
|
<#else>\ |
|
'' || spalte${col.key} || '|' || summe as ${graph_col}${col.key},\ |
|
</#if>\ |
|
</#foreach>\ |
|
</#if>\ |
|
<#if graph_col=="col">\ |
|
summe,\ |
|
summe_ba\ |
|
<#elseif graph_col?starts_with("_spark")>\ |
|
summe as ${graph_col}summe,\ |
|
summe_ba as ${graph_col}summe_ba\ |
|
<#else>\ |
|
summe,\ |
|
'' || summe_ba || '|' || summe as ${graph_col}summe_ba\ |
|
</#if>\ |
|
\ |
|
FROM tmp_tabelle3\ |
|
ORDER BY sortnr,1,2,3;\ |
|
<#else>\ |
|
--Zeilen und Spalten gleich:\ |
|
select null::integer into temp tmp_tabelle3\ |
|
from xdummy;\ |
|
select * from tmp_tabelle3;\ |
|
</#if>^--Freemarker Template\ |
|
<sqlvars>\ |
|
<sqlvar name="jahr" type="hashsequence"><![CDATA[\ |
|
SELECT ord,name,'Jahr'::varchar(255) as dimcaption\ |
|
from aggregierung\ |
|
where kategorie='KENN-Jahr'\ |
|
/* and ord >= <<Jahr von>> */\ |
|
/* and ord <= <<Jahr bis>> */\ |
|
order by 1\ |
|
;\ |
|
]]>\ |
|
</sqlvar>\ |
|
<sqlvar name="hs_nr" type="hashsequence"><![CDATA[\ |
|
SELECT hs_nr as key,name,'Hochschule'::varchar(255) as dimcaption\ |
|
from kenn_hochschulen\ |
|
where hs_nr::char(10) in <@printkeys .vars["Hochschule"].allNeededKeys />\ |
|
and traegerschaft='ST'\ |
|
order by 2\ |
|
;\ |
|
]]>\ |
|
</sqlvar>\ |
|
<sqlvar name="fgr" type="hashsequence"><![CDATA[\ |
|
SELECT apnr as key,drucktext as name,'Fächergruppe'::varchar(255) as dimcaption\ |
|
from kenn_dim_fgr\ |
|
where fg_studierende is not null\ |
|
order by 2\ |
|
;\ |
|
]]>\ |
|
</sqlvar>\ |
|
<sqlvar name="abgrp" type="hashsequence"><![CDATA[\ |
|
SELECT 'BA'::char(1) as key,'Bachelor'::varchar(255) as name,\ |
|
'Abschlussart'::varchar(255) as dimcaption \ |
|
from xdummy\ |
|
union\ |
|
SELECT 'MA','Master',\ |
|
'Abschlussart'::varchar(255) as dimcaption from xdummy\ |
|
union\ |
|
SELECT 'ST','Staatsexamen',\ |
|
'Abschlussart'::varchar(255) as dimcaption from xdummy\ |
|
order by 1\ |
|
;\ |
|
]]>\ |
|
</sqlvar>\ |
|
</sqlvars>\ |
|
<#assign coldim=<<Spalten>> />\ |
|
<#assign colfieldname="<<Spalten>>" />\ |
|
XIL List\ |
|
drop_and_delete movable_columns sizable_columns horizontal_scrolling\ |
|
white_space_color=COLOR_WHITE fixed_columns=1\ |
|
min_heading_height=35\ |
|
Column CID=2 heading_text=" Ebene " center_heading\ |
|
row_selectable col_selectable heading_platform readonly\ |
|
width=5 text_size=30\ |
|
Column CID=2 heading_text=" Name " center_heading\ |
|
row_selectable col_selectable heading_platform readonly\ |
|
width=30 text_size=30\ |
|
<#if coldim?has_content>\ |
|
<#foreach col in coldim>\ |
|
Column CID=3 heading_text=" ${col.dimcaption}\\000${col.name}" center_heading\ |
|
row_selectable col_selectable heading_platform readonly\ |
|
width=10 text_size=30\ |
|
</#foreach>\ |
|
</#if>\ |
|
Column CID=3 heading_text="Gesamt" center_heading\ |
|
row_selectable col_selectable heading_platform readonly\ |
|
width=10 text_size=30\ |
|
Column CID=3 heading_text="Darunter\\n\ |
|
Bildungsausl." center_heading row_selectable col_selectable heading_platform readonly\ |
|
width=10 text_size=30\ |
|
@@@^Studienfach^Anzahl bzw. Anteil^Studierende oder Absolvent_innen nach Hochschule, Fächergruppe, Gewichtung^drop table tmp_tabelle3;^^1^850^600^^1^<<SQL>> select 'Bitte wählen Sie unterschiedliche Spalten/Zeilen' from xdummy where '<<Zeilen>>'='<<Spalten>>' union \ |
|
select 'Gewichtung mit folgenden Faktoren: Abschlüsse grundständiger Studiengänge ohne Staasexamen * 1 + Abschlüsse konsekutiver Master-Studiengänge * 0,5 + Abschlüsse Staatsexamen (inkl. Lehramt) * 1,5' from xdummy where '<<Gewichtung>>'='1'^
|
|
|