sxc/schluesseltabellen/macro_masken_bez_fuellen.sql

--freemarker template

<#assign makros = [
{"tid":"47060"}
] />


<#assign masken = [
{"makro":"47060", "tid":"47030", "sortnr":"10"},
{"makro":"47060", "tid":"47030", "sortnr":"20"},
{"makro":"47060", "tid":"47030", "sortnr":"30"},
{"makro":"47060", "tid":"47030", "sortnr":"40"}

] />

<#assign felder = [
{"makro":"47060", "sortnr":"10", "feldname":"Ansicht in Ergebniszeilen", "value":"semester"},
{"makro":"47060", "sortnr":"10", "feldname":"Ansicht in Ergebnisspalten", "value":"auspraegung_code_3"},
{"makro":"47060", "sortnr":"10", "feldname":"Kennzahl", "value":"<<SQL>> select tid,name from xcube_kennzahl where maskeninfo_id=47030 order by sortnr,name;"},
{"makro":"47060", "sortnr":"20", "feldname":"Ansicht in Ergebniszeilen", "value":"semester"},
{"makro":"47060", "sortnr":"20", "feldname":"Ansicht in Ergebnisspalten", "value":"auspraegung_code_2"},
{"makro":"47060", "sortnr":"20", "feldname":"Kennzahl", "value":"<<SQL>> select tid,name from xcube_kennzahl where maskeninfo_id=47030 order by sortnr,name;"},
{"makro":"47060", "sortnr":"30", "feldname":"Ansicht in Ergebniszeilen", "value":"hs_nr"},
{"makro":"47060", "sortnr":"30", "feldname":"Ansicht in Ergebnisspalten", "value":"auspraegung_code_3"},
{"makro":"47060", "sortnr":"30", "feldname":"Kennzahl", "value":"<<SQL>> select tid,name from xcube_kennzahl where maskeninfo_id=47030 order by sortnr,name;"},
{"makro":"47060", "sortnr":"40", "feldname":"Ansicht in Ergebniszeilen", "value":"hs_nr"},
{"makro":"47060", "sortnr":"40", "feldname":"Ansicht in Ergebnisspalten", "value":"auspraegung_code_2"},
{"makro":"47060", "sortnr":"40", "feldname":"Kennzahl", "value":"<<SQL>> select tid,name from xcube_kennzahl where maskeninfo_id=47030 order by sortnr,name;"}


] />


-- ab hier nicht mehr ändern:

CREATE TEMP TABLE tmp_macro_masken_bez (
    maskeninfo_id1 integer NOT NULL,
    maskeninfo_id2 integer NOT NULL,
    active integer DEFAULT 1 NOT NULL,
    sortnr smallint NOT NULL,
    schleifenrelation character(255),
    schleifenfeldname character(255),
    alias character(255),
    schleifenfstand character(255),
    schleifenfsicht character(255),
    aktion character(255)
);
CREATE TEMP TABLE tmp_macro_feld_wert (
    macro integer NOT NULL,
    sortnr integer NOT NULL,
    feldname character(255) NOT NULL,
    alias character(255) NOT NULL,
    value character(255),
    value_caption character(255),
    feldstand character(255),
    feldsicht character(255),
    active integer DEFAULT 1 NOT NULL
);


<#foreach makro in makros>
<#foreach maske in masken>
<#if maske.makro==makro.tid>

insert into tmp_macro_masken_bez(maskeninfo_id1,
       maskeninfo_id2,
       active,
       sortnr)
select ${makro.tid},--maskeninfo_id1,
       ${maske.tid},--maskeninfo_id2,
       1,--active,
       ${maske.sortnr} --sortnr
       from xdummy;
       
<#foreach feld in felder>
<#if feld.makro==makro.tid && feld.sortnr==maske.sortnr>

--Tab.12: alle ohne Exmatr
insert into tmp_macro_feld_wert
( macro,
       sortnr,
       feldname,
       alias,
       value,
       value_caption,
       feldstand,
       feldsicht,
       active)
 SELECT ${makro.tid}, --macro
       ${maske.sortnr},--sortnr
       '${feld.feldname}',--feldname,
       '',--alias,
       '${feld.value}', --value, 
       '',--value_caption,
       '', --feldstand,
       '',--feldsicht,
       1 --active
FROM xdummy;

</#if>
</#foreach>


</#if>
</#foreach>
</#foreach>

      
delete from macro_masken_bez where maskeninfo_id1 in (select maskeninfo_id1 from tmp_macro_masken_bez)
;


insert into macro_masken_bez
(maskeninfo_id1,
       maskeninfo_id2,
       active,
       sortnr,
       schleifenrelation,
       schleifenfeldname,
       alias,
       schleifenfstand,
       schleifenfsicht,
       aktion)
select maskeninfo_id1,
       maskeninfo_id2,
       active,
       sortnr,
       schleifenrelation,
       schleifenfeldname,
       alias,
       schleifenfstand,
       schleifenfsicht,
       aktion
FROM tmp_macro_masken_bez
;

delete from macro_feld_wert where macro in (select macro from tmp_macro_feld_wert); 

insert into macro_feld_wert
( macro,
       sortnr,
       feldname,
       alias,
       value,
       value_caption,
       feldstand,
       feldsicht,
       active)
 SELECT macro,
       sortnr,
       feldname,
       alias,
       value,
       value_caption,
       feldstand,
       feldsicht,
       active
FROM tmp_macro_feld_wert;


drop table tmp_macro_masken_bez;
drop table tmp_macro_feld_wert;