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"); }