private void GenerateLoadFactInterface(StringBuilder sb, FactALInterface ifa)
{
sb.Append($"-- Load FactInterface {ifa.Name}\n");
sb.Append($"insert into {ifa.Name} (\n");
foreach (var attr in ifa.Attributes)
{
sb.Append($"{attr.Name}".Indent(1));
if (attr != ifa.Attributes.Last())
{
sb.Append(",");
}
sb.Append("\n");
}
sb.Append(") \n");
sb.Append("select \n");
foreach (var attr in ifa.Attributes)
{
sb.Append($"{attr.JoinAlias}.{GetBTName(attr.BTAttribute)} as {attr.Name}".Indent(1));
if (attr != ifa.Attributes.Last())
{
sb.Append(",");
}
sb.Append("\n");
}
sb.Append($"from {ifa.BTInterface.FullName} as t0\n");
// referenzierte BT-Fakttabellen joinen
foreach (var fref in ifa.FactInterfaceReferences)
{
sb.Append($"inner join {fref.BTInterface.FullName} as {fref.JoinAlias}\n".Indent(1));
sb.Append($"on t0.{fref.RefColumnName} = {fref.JoinAlias}.{fref.RefColumnName}".Indent(1));
}
sb.Append(";\n\n");
}
private void GenerateNowFactInterface(StringBuilder sb, FactALInterface ifa)
{
sb.Append($"-- Now-Table zu {ifa.Core.Name}\n");
sb.Append($"create table {ifa.Name}_NOW (\n");
foreach (var a in ifa.Attributes)
{
if (a.IsFact)
{
sb.Append($"{a.Name}_NOW {a.SqlType}".Indent(1));
}
else
{
sb.Append($"{a.Name} {a.SqlType}".Indent(1));
}
if (a == ifa.IdColumn)
{
sb.Append(" primary key not null");
}
if (a != ifa.Attributes.Last())
{
sb.Append(",");
}
sb.Append("\n");
}
sb.Append(");\n");
sb.Append("\n");
}
private void GenerateFactInterface(StringBuilder sb, FactALInterface ifa)
{
sb.Append($"truncate table {ifa.Model.Config.ALDatabase}.dbo.{ifa.Name} \n");
sb.Append($"insert into {ifa.Model.Config.ALDatabase}.dbo.{ifa.Name} (\n");
foreach (var a in ifa.Attributes)
{
sb.Append($"{a.Name}".Indent(1));
if (a != ifa.Attributes.Last())
{
sb.Append(",");
}
sb.Append("\n");
}
sb.Append(") select \n");
foreach (var a in ifa.Attributes)
{
sb.Append($"{a.Name}".Indent(1));
if (a != ifa.Attributes.Last())
{
sb.Append(",");
}
sb.Append("\n");
}
sb.Append($"from [{ifa.Model.Config.EtlDbServer}].{ifa.Model.Config.ALDatabase}.dbo.{ifa.Name};\n");
sb.Append("\n");
}
private void GenerateLoadNowTable(StringBuilder sb, FactALInterface ifa)
{
sb.Append($"-- Load NowTable for FactInteface {ifa.Core.Name}\n");
sb.Append($"truncate table {ifa.Name}_NOW;\n");
sb.Append($"insert into {ifa.Name}_NOW (\n");
foreach (var attr in ifa.Attributes)
{
if (attr.IsFact)
{
sb.Append($"{attr.Name}_NOW".Indent(1));
}
else
{
sb.Append($"{attr.Name}".Indent(1));
}
if (attr != ifa.Attributes.Last())
{
sb.Append(",");
}
sb.Append("\n");
}
sb.Append(") \n");
sb.Append("select\n");
foreach (var attr in ifa.Attributes)
{
sb.Append($"{attr.Name}".Indent(1));
if (attr != ifa.Attributes.Last())
{
sb.Append(",");
}
sb.Append("\n");
}
sb.Append($"from {ifa.Name} a\n");
if (ifa.Core.IsMandant)
{
sb.Append($"where a.{ifa.HistoryAttribute.Name} = (select max({ifa.HistoryAttribute.Name}) from {ifa.Name} as i where i.Mandant_ID = a.Mandant_ID)");
}
else
{
sb.Append($"where a.{ifa.HistoryAttribute.Name} = (select max({ifa.HistoryAttribute.Name}) from {ifa.Name})");
}
sb.Append(";\n\n");
}
private void GenerateTruncateFactInterface(StringBuilder sb, FactALInterface ifa)
{
sb.Append($"-- Inhalt von {ifa.Name} löschen\n");
sb.Append($"truncate table {ifa.Name};\n\n");
}
private void GenerateNowInterface(StringBuilder sb, FactALInterface ifa)
{
sb.Append($"drop table if exists {ifa.Name}_NOW;\n");
}
private void GenerateNowInterface(StringBuilder sb, FactALInterface ifa)
{
sb.Append($"IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[{ifa.Name}]') AND type in (N'U'))\n");
sb.Append($"drop table {ifa.Name}_NOW\n".Indent(1));
sb.Append("go\n\n");
}
