--freemarker template

<#assign xcubes =  [
 {"name":"Studierendenwürfel_SXC", 
 "uniquename":"xcube_sxc_stud_sem_da_geschl_hs",
 "sachgebiet":16,
 "table_name":"sxc_stud_sem_da_geschl_hs",
 "maskeninfo_id":47030 
},
{"name":"Makro_Studierendenwürfel_SXC", 
 "uniquename":"xcube_sxc_stud_sem_da_geschl_hs",
 "sachgebiet":16,
 "table_name":"sxc_stud_sem_da_geschl_hs",
 "maskeninfo_id":47030 
}
]  />

<#assign xcube_kennzahl = [
  { "maskeninfo_id":47030,
   "apnr":"sxc_stud",
   "name":"Studierende",
   "aggrfunction":"sum(bil002__studierende__anzahl)",
   "restriction":"bil002__studierende__q='e'",
   "datatype":"integer",
   "is_default":1,
   "sortnr":1},
   
  { "maskeninfo_id":47060,
   "apnr":"makro_sxc_stud",
   "name":"Makro_Studierende",
   "aggrfunction":"sum(bil002__studierende__anzahl)",
   "restriction":"bil002__studierende__q='e'",
   "datatype":"integer",
   "is_default":1,
   "sortnr":1}
] />


<#assign xcube_dimconfig = [
 <#-- SXC_Studierendenwürfel -->     
      {"maskeninfo_id":47030, 
    "name":"semester",
    "caption":"Semester",
    "is_coldefault":1,
    "foreignkey_tab":"kenn_semester",
    "foreignkey_col":"tid",
    "foreignkey_cap":"druck",
    "sortfield":"tid",
    "calc_gesamt":0},
       {"maskeninfo_id":47030, 
    "name":"hs_nr",
    "caption":"Hochschule",
    "is_rowdefault":1,
    "is_sicht":1},
{"maskeninfo_id":47030, 
    "name":"auspraegung_code_3",
    "caption":"Geschlecht",
    "foreignkey_tab":"xcube_dims",
    "foreignkey_col":"apnr",
    "foreignkey_cap":"name",
    "foreignkey_cond":"id='auspraegung_code_3'",
    "attrib_nachbearbeitung": "update <<tmp_table>> set <<col>>='UNB' where <<col>> is NULL",
    "sortfield":"sort1"},   
{"maskeninfo_id":47030, 
    "name":"auspraegung_code_2",
    "caption":"Nationalität",
    "foreignkey_tab":"xcube_dims",
    "foreignkey_col":"apnr",
    "foreignkey_cap":"name",
    "foreignkey_cond":"id='auspraegung_code_2'",
    
    "sortfield":"sort1"} ,
    <#-- Makro_SXC_Studierendenwürfel -->     
      {"maskeninfo_id":47060, 
    "name":"semester",
    "caption":"Semester",
    "is_coldefault":1,
    "foreignkey_tab":"kenn_semester",
    "foreignkey_col":"tid",
    "foreignkey_cap":"druck",
    "sortfield":"tid",
    "calc_gesamt":0},
       {"maskeninfo_id":47060, 
    "name":"hs_nr",
    "caption":"Hochschule",
    "is_rowdefault":1,
    "is_sicht":1},
{"maskeninfo_id":47060, 
    "name":"auspraegung_code_3",
    "caption":"Geschlecht",
    "foreignkey_tab":"xcube_dims",
    "foreignkey_col":"apnr",
    "foreignkey_cap":"name",
    "foreignkey_cond":"id='auspraegung_code_3'",
    "attrib_nachbearbeitung": "update <<tmp_table>> set <<col>>='UNB' where <<col>> is NULL",
    "sortfield":"sort1"},   
{"maskeninfo_id":47060, 
    "name":"auspraegung_code_2",
    "caption":"Nationalität",
    "foreignkey_tab":"xcube_dims",
    "foreignkey_col":"apnr",
    "foreignkey_cap":"name",
    "foreignkey_cond":"id='auspraegung_code_2'",
    
    "sortfield":"sort1"}
] />


--freemarker magic

 <#foreach cube in xcubes>
delete from xcube where maskeninfo_id=${cube.maskeninfo_id};
delete from xcube_dimconfig where maskeninfo_id=${cube.maskeninfo_id};
delete from xcube_kennzahl where maskeninfo_id=${cube.maskeninfo_id};
 insert into xcube (name,uniquename,table_name,maskeninfo_id,sachgebiet)
 values ('${cube.name}','${cube.uniquename}','${cube.table_name}',${cube.maskeninfo_id},${cube.sachgebiet});
</#foreach>

<#foreach k in xcube_kennzahl>
  insert into xcube_kennzahl (apnr,name,maskeninfo_id,aggrfunction,restriction,is_default,sortnr) values 
 ('${k.apnr}','${k.name}',${k.maskeninfo_id},'${k.aggrfunction?replace("\x0027","\x0027\x0027")}',
 '${k.restriction?replace("\x0027","\x0027\x0027")}',
 ${k.is_default},${k.sortnr});
<#if k?keys?seq_contains("datatype")> update xcube_kennzahl set datatype='${k.datatype}' where maskeninfo_id='${k.maskeninfo_id}' and apnr='${k.apnr}';</#if>
        </#foreach>


<#foreach conf in xcube_dimconfig>
-- einfache durch zwei einfache Zeichen ersetzten x0027
insert into xcube_dimconfig (maskeninfo_id,name,caption)
values (${conf.maskeninfo_id},'${conf.name}','${conf.caption?replace("\x0027","\x0027\x0027")}');


<#if conf?keys?seq_contains("foreignkey_tab")> update xcube_dimconfig set foreignkey_tab='${conf.foreignkey_tab}' where maskeninfo_id='${conf.maskeninfo_id}' and name='${conf.name}';</#if>
<#if conf?keys?seq_contains("foreignkey_cap")> update xcube_dimconfig set foreignkey_cap='${conf.foreignkey_cap}' where maskeninfo_id='${conf.maskeninfo_id}' and name='${conf.name}';</#if>
<#if conf?keys?seq_contains("foreignkey_col")> update xcube_dimconfig set foreignkey_col='${conf.foreignkey_col}' where maskeninfo_id='${conf.maskeninfo_id}' and name='${conf.name}';</#if>
<#if conf?keys?seq_contains("sortfield")> update xcube_dimconfig set sortfield='${conf.sortfield}' where maskeninfo_id='${conf.maskeninfo_id}' and name='${conf.name}';</#if>
<#if conf?keys?seq_contains("is_sicht")> update xcube_dimconfig set is_sicht=${conf.is_sicht} where maskeninfo_id='${conf.maskeninfo_id}' and name='${conf.name}';</#if>
<#if conf?keys?seq_contains("calc_gesamt")> update xcube_dimconfig set calc_gesamt=${conf.calc_gesamt} where maskeninfo_id='${conf.maskeninfo_id}' and name='${conf.name}';</#if>
<#if conf?keys?seq_contains("is_rowdim")> update xcube_dimconfig set is_rowdim=${conf.is_rowdim} where maskeninfo_id='${conf.maskeninfo_id}' and name='${conf.name}';</#if>
<#if conf?keys?seq_contains("is_secondrowdim")> update xcube_dimconfig set is_secondrowdim=${conf.is_secondrowdim} where maskeninfo_id='${conf.maskeninfo_id}' and name='${conf.name}';</#if>
<#if conf?keys?seq_contains("is_rowdefault")> update xcube_dimconfig set is_rowdefault=${conf.is_rowdefault} where maskeninfo_id='${conf.maskeninfo_id}' and name='${conf.name}';</#if>
<#if conf?keys?seq_contains("is_coldim")> update xcube_dimconfig set is_coldim=${conf.is_coldim} where maskeninfo_id='${conf.maskeninfo_id}' and name='${conf.name}';</#if>
<#if conf?keys?seq_contains("is_secondcoldim")> update xcube_dimconfig set is_secondcoldim=${conf.is_secondcoldim} where maskeninfo_id='${conf.maskeninfo_id}' and name='${conf.name}';</#if>
<#if conf?keys?seq_contains("is_coldefault")> update xcube_dimconfig set is_coldefault=${conf.is_coldefault} where maskeninfo_id='${conf.maskeninfo_id}' and name='${conf.name}';</#if>
<#if conf?keys?seq_contains("foreignkey_cond")> update xcube_dimconfig set foreignkey_cond='${conf.foreignkey_cond?replace("\x0027","\x0027\x0027")}'  where maskeninfo_id='${conf.maskeninfo_id}' and name='${conf.name}';</#if>
<#if conf?keys?seq_contains("is_virtual")> update xcube_dimconfig set is_virtual=${conf.is_virtual} where maskeninfo_id='${conf.maskeninfo_id}' and name='${conf.name}';</#if>
<#if conf?keys?seq_contains("vcsql")> update xcube_dimconfig set vcsql='${conf.vcsql?replace("\x0027","\x0027\x0027")}' where maskeninfo_id='${conf.maskeninfo_id}' and name='${conf.name}';</#if>
<#if conf?keys?seq_contains("attrib_nachbearbeitung")> update xcube_dimconfig set attrib_nachbearbeitung='${conf.attrib_nachbearbeitung?replace("\x0027","\x0027\x0027")}' where maskeninfo_id='${conf.maskeninfo_id}' and name='${conf.name}';</#if>
</#foreach>