-
-
Save georgeduckett/a852af8dcb9ca61ee2c0ca800464bd74 to your computer and use it in GitHub Desktop.
The key functions that generate files representing an Anaplan model based on an export of the module line items.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
private static async Task ExportModelDesign(SqlConnection sqlConn, AnaplanApiClient client) | |
{ | |
// We do this here as we're doing it for each model in each workspace | |
await foreach (var taskResult in client.ExportFileToSQL("ModuleLineItems.csv", (await client.GetExports()).Exports, (await client.GetFiles()).Files)) | |
{ | |
Console.Write($"\rExporting module line items to SQL. {taskResult.Task.Progress:P2} Step: {taskResult.Task.CurrentStep ?? "Exporting"}{new string(' ', Console.WindowWidth)}".Substring(0, Console.WindowWidth - 1)); | |
} | |
Console.WriteLine(); | |
// Update the various columns in the line items that can be inherited from the module. | |
await sqlConn.ExecuteAsync(@"UPDATE ModuleLineItems | |
SET[Applies To] = ModuleLineItems_1.[Applies To] | |
FROM AnaplanExport.dbo.ModuleLineItems INNER JOIN | |
AnaplanExport.dbo.ModuleLineItems AS ModuleLineItems_1 ON ModuleLineItems.[Module Name] = ModuleLineItems_1.[Line Item Name] AND ModuleLineItems_1.[Module Name] = '' | |
WHERE(ModuleLineItems.[Applies To] = '-') | |
UPDATE ModuleLineItems | |
SET[Read Access Driver] = ModuleLineItems_1.[Read Access Driver] | |
FROM AnaplanExport.dbo.ModuleLineItems INNER JOIN | |
AnaplanExport.dbo.ModuleLineItems AS ModuleLineItems_1 ON ModuleLineItems.[Module Name] = ModuleLineItems_1.[Line Item Name] AND ModuleLineItems_1.[Module Name] = '' | |
WHERE(ModuleLineItems.[Read Access Driver] = '-') | |
UPDATE ModuleLineItems | |
SET[Write Access Driver] = ModuleLineItems_1.[Write Access Driver] | |
FROM AnaplanExport.dbo.ModuleLineItems INNER JOIN | |
AnaplanExport.dbo.ModuleLineItems AS ModuleLineItems_1 ON ModuleLineItems.[Module Name] = ModuleLineItems_1.[Line Item Name] AND ModuleLineItems_1.[Module Name] = '' | |
WHERE(ModuleLineItems.[Write Access Driver] = '-') | |
UPDATE ModuleLineItems | |
SET[Data Tags] = ModuleLineItems_1.[Data Tags] | |
FROM AnaplanExport.dbo.ModuleLineItems INNER JOIN | |
AnaplanExport.dbo.ModuleLineItems AS ModuleLineItems_1 ON ModuleLineItems.[Module Name] = ModuleLineItems_1.[Line Item Name] AND ModuleLineItems_1.[Module Name] = '' | |
WHERE(ModuleLineItems.[Data Tags] = '-')"); | |
// Remove the line items that are just the modules themselves | |
await sqlConn.ExecuteAsync("DELETE FROM AnaplanExport.dbo.ModuleLineItems WHERE([Module Name] = '')"); | |
} | |
private static async Task GenerateFolderStructureFromModelDesign(SqlConnection sqlconn, string baseFolder) | |
{ | |
SqlMapper.SetTypeMap(typeof(ModuleLineItem), new CustomPropertyTypeMap(typeof(ModuleLineItem), (t, s) => { return t.GetProperty(s.Replace(" ", "")); })); | |
Console.Write("Getting line items from SQL"); | |
var lineItems = (await sqlconn.QueryAsync<ModuleLineItem>("SELECT * FROM AnaplanExport.dbo.[ModuleLineItems]")).ToArray(); | |
Console.Write("\rGenerating files for module line Items."); | |
var lineItemNumber = 1; | |
foreach (var lineItem in lineItems) | |
{ | |
Console.Write($"\r{new string(' ', Console.WindowWidth - 1)}"); | |
Console.Write($"\rProcessing {lineItemNumber++} of {lineItems.Length}: {lineItem.ModuleName}.{lineItem.LineItemName}"); | |
var moduleFolderName = Path.Combine(baseFolder, lineItem.ModuleName.Replace(Path.GetInvalidPathChars(), '_')); | |
if (!Directory.Exists(moduleFolderName)) { Directory.CreateDirectory(moduleFolderName); } | |
var lineItemObject = new ExpandoObject(); | |
foreach (var prop in typeof(ModuleLineItem).GetProperties()) | |
{ | |
var propValue = prop.GetValue(lineItem); | |
object parsedObject = null; | |
if (propValue is string propString) | |
{ | |
if (!string.IsNullOrWhiteSpace(propString) && propString.Contains('{')) | |
{ | |
try | |
{ | |
// TODO: Escape the string | |
parsedObject = JsonConvert.DeserializeObject(propString); | |
} | |
catch (Exception) { } | |
} | |
} | |
if (parsedObject == null) | |
{ | |
lineItemObject.TryAdd(prop.Name, propValue); | |
} | |
else | |
{ | |
lineItemObject.TryAdd(prop.Name, parsedObject); | |
} | |
} | |
// Don't need these as they're part of the file structure | |
lineItemObject.Remove(nameof(lineItem.LineItemName), out _); | |
lineItemObject.Remove(nameof(lineItem.ModuleName), out _); | |
// Don't include the cell count, as that'll mean changes most days | |
lineItemObject.Remove(nameof(lineItem.CellCount), out _); | |
// Deal with the lineItemObject not having a consistant order when generating the json | |
var serialisedLineItem = JsonUtility.ToNormalizedJsonString(lineItemObject, Formatting.Indented); | |
await File.WriteAllTextAsync(Path.Combine(moduleFolderName, lineItem.LineItemName.Replace(Path.GetInvalidFileNameChars(), '_') + ".json"), serialisedLineItem); | |
if (!string.IsNullOrEmpty(lineItem.Formula)) | |
{ | |
await File.WriteAllTextAsync(Path.Combine(moduleFolderName, lineItem.LineItemName.Replace(Path.GetInvalidFileNameChars(), '_') + " - formula.txt"), Anaplan.Formulas.FormulaPrettifier.Prettify(lineItem.Formula)); | |
} | |
} | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment