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.
170 lines
7.5 KiB
170 lines
7.5 KiB
43040^Studierende Datenblatt (CO)^--Freemarker Template\ |
|
<#include "SQL_lingua_franca"/>\ |
|
<#include "SuperX_general"/>\ |
|
<#include "SQL_multitable_output"/>\ |
|
\ |
|
--ram excelexport \ |
|
<#include "SQL_lingua_franca"/>\ |
|
<#include "SuperX_general"/>\ |
|
<#include "SQL_multitable_output"/>\ |
|
\ |
|
<sqlvars>\ |
|
<sqlvar name="get_tables"><![CDATA[\ |
|
SELECT distinct name,name\ |
|
from sx_tables\ |
|
where name in ('costage_st_studien_aggr'\ |
|
${DatenblattTables(<<tablestylesheet>>,"<<Weitere Tabellen>>",.vars["Weitere TabellenObject"].allKeys)}\ |
|
\ |
|
)\ |
|
order by 2;]]>\ |
|
</sqlvar>\ |
|
<sqlvar name="get_table_joins"><![CDATA[\ |
|
select table_name || '.' || name || '=' || foreignkey_tab || '.' || foreignkey_col,table_name \ |
|
from sx_fields where table_name in ('costage_st_studien_aggr'\ |
|
${DatenblattTables(<<tablestylesheet>>,"<<Weitere Tabellen>>",.vars["Weitere TabellenObject"].allKeys)} )\ |
|
and foreignkey_tab in ('costage_st_studien_aggr'\ |
|
${DatenblattTables(<<tablestylesheet>>,"<<Weitere Tabellen>>",.vars["Weitere TabellenObject"].allKeys)})\ |
|
;]]>\ |
|
</sqlvar>\ |
|
<sqlvar name="get_felder"><![CDATA[\ |
|
SELECT name,\ |
|
caption,\ |
|
(trim(field_type) || '#' || is_primarykey || '#' || trim(string_not_null(foreignkey_tab)) || '#' || trim(string_not_null(foreignkey_col))\ |
|
|| '#' || trim(string_not_null(foreignkey_cap))\ |
|
|| '#' || trim(string_not_null(foreignkey_cond))\ |
|
|| '#' || trim(string_not_null(foreignkey_func))\ |
|
|| '#' || trim(string_not_null(table_name))\ |
|
|| '#' || trim(string_not_null(foreignkey_uniquename)))::char(255) as strukturStr\ |
|
from sx_fields\ |
|
where table_name in ('costage_st_studien_aggr'\ |
|
${DatenblattTables(<<tablestylesheet>>,"<<Weitere Tabellen>>",.vars["Weitere TabellenObject"].allKeys)}\ |
|
)\ |
|
and currentlyused=1\ |
|
${DatenblattFields(<<tablestylesheet>>,"<<Felder>>",FelderObject.allKeys)}\ |
|
order by 2,3;]]>\ |
|
</sqlvar>\ |
|
<sqlvar name="get_felder_fk"><![CDATA[\ |
|
SELECT name,\ |
|
caption,\ |
|
(trim(table_name)\ |
|
|| '#' || trim(string_not_null(foreignkey_tab))\ |
|
|| '#' || trim(string_not_null(foreignkey_uniquename)))::char(255) as strukturStr\ |
|
from sx_fields\ |
|
where table_name in ('costage_st_studien_aggr' ${DatenblattTables(<<tablestylesheet>>,"<<Weitere Tabellen>>",.vars["Weitere TabellenObject"].allKeys)}\ |
|
)\ |
|
and currentlyused=1\ |
|
${DatenblattFields(<<tablestylesheet>>,"<<Felder>>",FelderObject.allKeys)}\ |
|
order by 2,3;]]>\ |
|
</sqlvar>\ |
|
</sqlvars>\ |
|
\ |
|
<@generate_multitable_list />\ |
|
<@generate_field_list_multitable aggregationsfeld="summe" aggregatfunktion="sum(" />\ |
|
<@generate_foreign_fields_multitable />\ |
|
\ |
|
\ |
|
<#assign filter="\ |
|
/* and hrst in (<<Hörerstatus>>) */\ |
|
/* and semester >= <<Seit Semester>> */\ |
|
/* and semester <= <<Bis Semester>> */\ |
|
/* AND fachsemester <= <<bis Fachsemester>> */\ |
|
/* and statustyp in(<<Status>>) */\ |
|
/* and costage_st_studien_aggr.st_studiengang_nr in (select G.st_studiengang_nr from costage_st_studiengaenge G where G.abschluss in (<<Abschluss>>)) */\ |
|
/* and costage_st_studien_aggr.st_studiengang_nr in (select G.st_studiengang_nr from costage_st_studiengaenge G where G.skz_key in (<<Fach>>)) */\ |
|
/* and studiengang_nr =<<Studiengang-Nr.>> */\ |
|
/* and fach_nr =<<Fach-Nr.>> */\ |
|
/* and costage_st_studien_aggr.st_studstamm_nr in (select S.st_studstamm_nr from costage_st_studierendenstammdaten S where S.st_land1_nr in (<<Staatsangehörigkeit>>) ) */\ |
|
/* and costage_st_studien_aggr.st_studstamm_nr in (select S2.st_studstamm_nr from costage_st_studierendenstammdaten S2 where S2.geschlecht = <<Geschlecht>> ) */\ |
|
/* and costage_st_studien_aggr.unikey=<<Hochschule>> */\ |
|
" />\ |
|
\ |
|
\ |
|
<#if "<<Matrikel-Nr.>>"!="">\ |
|
<#assign filter=filter+" and costage_st_studien_aggr.matrikelnummer in (<<Matrikel-Nr.>>)"?replace("'","")/>\ |
|
</#if>\ |
|
\ |
|
\ |
|
/* <#assign filter = filter + " and 's_' || costage_st_studien_aggr.st_studiengang_nr in "+Studiengang.allNeededKeysList /> --<<Studiengang>> */ \ |
|
\ |
|
\ |
|
<@generate_resultset_multitable tabellen=table_list\ |
|
p_show_keys=<<Schlüssel anzeigen>>\ |
|
p_field_list_select=field_list_select\ |
|
p_foreign_fields=foreign_fields\ |
|
p_filter=filter\ |
|
p_field_list_groupby=field_list_groupby\ |
|
p_join_clause=join_clause\ |
|
/>^--Freemarker Template \ |
|
<sqlvars>\ |
|
<sqlvar name="basetable"><![CDATA[<#if "<<Köpfe oder Fälle ?>>"?index_of("S.gewichtung_id=")==-1>select 'costage_st_studien_aggr' from xdummy<#else>select 'sos_stud_gewichtung' from xdummy</#if>]]></sqlvar>\ |
|
<sqlvar name="restriction"><![CDATA[<#if basetable='costage_st_studien_aggr'>\ |
|
<#assign restrict=DatenblattTables(<<tablestylesheet>>,"<<Weitere Tabellen>>",.vars["Weitere TabellenObject"].allKeys)/>;\ |
|
<#else>\ |
|
<#assign restrict=DatenblattTables(<<tablestylesheet>>,"<<Weitere Tabellen>>",.vars["Weitere TabellenObject"].allKeys)?replace('costage_st_studien_aggr',basetable)?replace("'dim_studiengang'","'dim_studiengang_gew'")/>;\ |
|
</#if>\ |
|
select '${restrict?replace("\\x0027","\\x0027\\x0027")}' from xdummy;]]>\ |
|
</sqlvar>\ |
|
<sqlvar name="get_felder"><![CDATA[\ |
|
SELECT caption,\ |
|
foreignkey_tab,\ |
|
description as strukturStr\ |
|
from sx_fields\ |
|
where table_name in ('${basetable}'\ |
|
${restriction}\ |
|
)\ |
|
and currentlyused=1\ |
|
--Pseudonyme eingeschaltet:\ |
|
and (name != 'matrikel_nr' or 0=(select \ |
|
count(*) from konstanten where beschreibung='SOS_MTKNR_EXT'\ |
|
and apnr=1))\ |
|
<#if basetable='costage_st_studien_aggr'>\ |
|
${DatenblattFields(<<tablestylesheet>>,"<<Felder>>",FelderObject.allKeys)}\ |
|
<#else>\ |
|
${DatenblattFields(<<tablestylesheet>>,"<<Felder>>"?replace("costage_st_studien_aggr","sos_stud_gewichtung")?replace("dim_studiengang.","dim_studiengang_gew."),\ |
|
FelderObject.allKeys?replace("costage_st_studien_aggr","sos_stud_gewichtung")?replace("dim_studiengang.","dim_studiengang_gew."))}\ |
|
</#if>\ |
|
and table_name !='dim_person_attribute_aggr'\ |
|
--hier personattribute:\ |
|
union SELECT P.attributevalue , -- F.caption,\ |
|
F.foreignkey_tab,\ |
|
F.description as strukturStr\ |
|
from sx_fields F, personattributetype P\ |
|
where F.table_name in ('${basetable}'\ |
|
${restriction}\ |
|
)\ |
|
and currentlyused=1\ |
|
--Pseudonyme eingeschaltet:\ |
|
and (name != 'matrikel_nr' or 0=(select \ |
|
count(*) from konstanten where beschreibung='SOS_MTKNR_EXT'\ |
|
and apnr=1))\ |
|
<#if basetable='costage_st_studien_aggr'>\ |
|
${DatenblattFields(<<tablestylesheet>>,"<<Felder>>",FelderObject.allKeys)}\ |
|
<#else>\ |
|
${DatenblattFields(<<tablestylesheet>>,"<<Felder>>"?replace("costage_st_studien_aggr","sos_stud_gewichtung")?replace("dim_studiengang.","dim_studiengang_gew.")\ |
|
,FelderObject.allKeys?replace("costage_st_studien_aggr","sos_stud_gewichtung")?replace("dim_studiengang.","dim_studiengang_gew."))}\ |
|
</#if>\ |
|
and table_name ='dim_person_attribute_aggr'\ |
|
and F.name='attribute' || P.personattributetype_id\ |
|
order by 1;]]>\ |
|
</sqlvar>\ |
|
</sqlvars>\ |
|
XIL List\ |
|
sizable_columns horizontal_scrolling\ |
|
white_space_color=COLOR_WHITE fixed_columns=1\ |
|
min_heading_height=35\ |
|
<#assign i=1 />\ |
|
<#if get_felder?has_content >\ |
|
<#foreach myfield in get_felder>\ |
|
<#assign i=i+1 />\ |
|
Column CID=${i} heading_text="${myfield.key}" explanation="${myfield.strukturStr}" center_heading\ |
|
row_selectable col_selectable rightJust heading_platform readonly\ |
|
width=20\ |
|
<#if myfield.name != '' && <<Schlüssel anzeigen>>==1>\ |
|
<#assign i=i+1 />\ |
|
Column CID=${i} heading_text="${myfield.key}\\n(Schlüssel)" explanation="Schlüssel des Feldes ${myfield.key}" center_heading\ |
|
row_selectable col_selectable rightJust heading_platform readonly\ |
|
width=20\ |
|
</#if>\ |
|
</#foreach>\ |
|
</#if>\ |
|
@@@^Altersgruppe^Anzahl^Flexible Ausgabe der Studierendenstatistik, Studiengänge und Studierendenstammdaten^drop table tmp_tabelle;^^2^700^360^0^1^^
|
|
|