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.
110 lines
2.5 KiB
110 lines
2.5 KiB
3 years ago
|
-- Die cif wird um ICE-Schlüssel erweitert
|
||
|
--freemarker template
|
||
|
--(c) 2010 Daniel Quathamer
|
||
|
|
||
|
--Liste der cif-Schlüssel des Moduls:
|
||
|
--Wird in cif mit key=1 eingefügt
|
||
|
<#assign dimension_levels_man = [
|
||
|
{"key":"9003","apnr":"1", "druck":"Männlich", "kurz":"M"},
|
||
|
{"key":"9003","apnr":"2", "druck":"Weiblich", "kurz":"W"},
|
||
|
{"key":"9003","apnr":"3", "druck":"Divers", "kurz":"D"},
|
||
|
{"key":"9003","apnr":"4", "druck":"Unbekannt", "kurz":"U"}
|
||
|
] />
|
||
|
<#assign cif_keys_list = [
|
||
|
{"name":"Geschlecht (Amtl.)", "key":"9003"}
|
||
|
] />
|
||
|
|
||
|
--Liste der cifx-Schlüssel des Moduls:
|
||
|
--Wird in cifx mit key=2 eingefügt
|
||
|
|
||
|
|
||
|
create temp table tmp_cif2
|
||
|
(
|
||
|
tid serial,
|
||
|
key smallint not null ,
|
||
|
hs integer not null,
|
||
|
apnr integer not null ,
|
||
|
kurz char(10),
|
||
|
druck varchar(100),
|
||
|
lang_1 char(50),
|
||
|
astat INTEGER ,
|
||
|
parent INTEGER ,
|
||
|
sortc1 CHAR(10) ,
|
||
|
bund_apnr CHAR(10) ,
|
||
|
sprache CHAR(3)
|
||
|
);
|
||
|
|
||
|
create temp table tmp_hilf(tid integer);
|
||
|
|
||
|
--zuerst Metadaten cif:
|
||
|
<#list cif_keys_list as cif_key>
|
||
|
select 'cif-Schlüssel ${cif_key.name} key= ${cif_key.key} einfügen '::varchar(255) from xdummy;
|
||
|
insert into tmp_cif2(key,hs,apnr,kurz,druck,lang_1
|
||
|
)
|
||
|
select 1,0,${cif_key.key},substring('${cif_key.name}' from 1 for 10),'${cif_key.name}','${cif_key.name}'
|
||
|
from xdummy
|
||
|
;
|
||
|
</#list>
|
||
|
|
||
|
|
||
|
<#foreach dimension_level in dimension_levels_man>
|
||
|
|
||
|
insert into tmp_cif2(key,hs,apnr,kurz,druck,lang_1
|
||
|
)
|
||
|
select ${dimension_level.key},
|
||
|
0,
|
||
|
${dimension_level.apnr},
|
||
|
'${dimension_level.kurz}',
|
||
|
'${dimension_level.druck}',
|
||
|
'${dimension_level.druck}'
|
||
|
from xdummy
|
||
|
;
|
||
|
|
||
|
</#foreach>
|
||
|
|
||
|
|
||
|
--Vorhandene Schlüssel werden ersetzt, nicht vorhandene Schlüssel bleiben:
|
||
|
delete from cif where '' || key || '_' || apnr in
|
||
|
(select '' || key || '_' || apnr from tmp_cif2 )
|
||
|
;
|
||
|
|
||
|
|
||
|
|
||
|
|
||
|
insert into tmp_hilf select max(tid) from cif;
|
||
|
update tmp_hilf set tid=1 where tid is null;
|
||
|
|
||
|
insert into cif(tid,key,hs,apnr,kurz,druck,lang_1
|
||
|
-- ,
|
||
|
-- parent,
|
||
|
-- astat,
|
||
|
-- bund_apnr,
|
||
|
-- sortc1,
|
||
|
-- sprache
|
||
|
)
|
||
|
select C.tid+H.tid,key,hs,apnr,kurz,druck,lang_1
|
||
|
-- ,
|
||
|
-- parent,
|
||
|
-- astat,
|
||
|
-- bund_apnr,
|
||
|
-- sortc1,
|
||
|
-- sprache
|
||
|
from tmp_cif2 C, tmp_hilf H
|
||
|
where apnr is not null;
|
||
|
|
||
|
drop table tmp_cif2;
|
||
|
drop table tmp_hilf;
|
||
|
|
||
|
|
||
|
|
||
|
update cif set kurz=substring(druck from 1 for 10)
|
||
|
where kurz is null and druck is not null;
|
||
|
|
||
|
|
||
|
--Damit die Hochschulnummern in der CIF und in der Hochschulinfo-Tabelle
|
||
|
-- uebereinstimmen, ist folgender UPDATE notwendig:
|
||
|
|
||
|
update cif
|
||
|
set hs = (select hs_nr from hochschulinfo)
|
||
|
where hs != 0;
|