Skip to content

Instantly share code, notes, and snippets.

@georgeduckett
Created September 17, 2021 07:32
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save georgeduckett/a852af8dcb9ca61ee2c0ca800464bd74 to your computer and use it in GitHub Desktop.
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.
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