Forum Discussion

sdayringer's avatar
sdayringer
New Contributor III
3 years ago

Add StageArchivesInformation's FileTimeStamp Field to Extracted Filename

I'm writing a business rule that will extract the original imported TB files loaded at month end for each import workflow (which may have multiple files per period), rename the file with the time, wfprofile, and load time, move all load files for all workflows to one period folder, and delete the individual wfprofile folders autocreated.

I've been successful with all of the asks above, except I'm having trouble extracting the filetimestamp to insert into the new filename. I've found the desired FileTimeStamp field within the stagearchivesinformation table where the sourcefilename matches the filename extracted, but not sure how to reference that field... 

Any suggestions?

 

EXTENSIBILITY RULE:

Namespace OneStream.BusinessRule.Extender._BR_ExportTBLoadFiles_Period_TimeandWF
Public Class MainClass
Public Function Main(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As Object, ByVal args As ExtenderArgs) As Object
Try

'DEFINE THE WORKFLOW CLUSTER USED AS THE STARTING POINT TO EXTRACT FROM
Dim wfClusterPk As New WorkflowUnitClusterPk
Dim wfProfileName As String = String.Empty
Dim scenarioName As String = String.Empty
Dim timeName As String = String.Empty

'BUSINESS RULE PARAMETERS
Select Case args.FunctionType
Case Is = ExtenderFunctionType.Unknown
wfProfileName = "Entity Level"
scenarioName = "Actual"
timeName = "2022M10"
wfClusterPk = BRAPi.Workflow.General.GetWorkflowUnitClusterPk(si, wfProfileName, scenarioName, timeName)

'DATA MANAGEMENT PARAMETERS
Case Is = ExtenderFunctionType.ExecuteDataMgmtBusinessRuleStep
wfProfileName = args.NameValuePairs("WfProfileName")
scenarioName = args.NameValuePairs("ScenarioName")
timeName = args.NameValuePairs("TimeName")
wfClusterPk = BRAPi.Workflow.General.GetWorkflowUnitClusterPk(si, wfProfileName, scenarioName, timeName)
End Select

'PREPARE THE STAGE DATA EXTRACT FILE PATH
Dim yearName As String = timeName.Split("M")(0)
Dim yearInt As Integer = timeName.Split("M")(0).XFConvertToInt
Dim monthName As String = timeName.Split("M")(1)
Dim monthInt As Integer = timeName.Split("M")(1).XFConvertToInt
If monthName.Length < 2 Then
monthName = "0" & monthName
End If
Dim periodName As String = $"{monthName}.{yearInt.ToString().Substring(2, 2)}"
Dim configSettings As AppServerConfigSettings = AppServerConfig.GetSettings(si)
Dim folderPath As String = FileShareFolderHelper.GetContentsFolderForSystem(si,True,configSettings.FileShareRootFolder)& "\TB Load Files\" & scenarioName & "\Period\" & yearName & "\" & periodName
If Not Directory.Exists(folderPath) Then Directory.CreateDirectory(folderPath)


'EXPORT DATA FOR EACH DESCENDANT WORKFLOW PROFILE
Dim profileInfos As List(Of WorkflowProfileInfo) = BRAPi.Workflow.Metadata.GetRelatives(si, wfClusterPk, WorkflowProfileRelativeTypes.Descendants, WorkflowProfileTypes.InputImportChild)
If Not profileInfos Is Nothing Then
For Each profileInfo As WorkflowProfileInfo In profileInfos
Dim wfClusterPkChild As New WorkflowUnitClusterPk(profileInfo.ProfileKey, wfClusterPk.ScenarioKey, wfClusterPk.TimeKey)
BRAPi.Import.Data.ExportStageArchives(si, wfClusterPkChild, folderPath, False)

'RENAME EXPORT FILES
For Each file As String In Directory.GetFiles(folderPath & "\" & profileInfo.Name)
Dim sourcePath As String = system.IO.Path.GetDirectoryName(file)
Dim sourcefilename As String = system.IO.Path.GetFileName(file)
Dim NewFolder As String = folderPath
Dim NewFileName As String = timename &"_"& profileInfo.Name & "_"& sourcefilename &".csv"
Dim destinationFilePath As String = System.IO.Path.Combine(NewFolder, NewFileName)
System.IO.File.Move(file, destinationFilePath)
Next

'DELETE EMPTY FOLDERS
Dim profilePath As String = folderPath & "\" & profileInfo.Name
If Not Directory.EnumerateFileSystemEntries(profilePath).Any Then
Directory.Delete(profilePath)
Else
End If
Next
End If

Return Nothing
Catch ex As Exception
Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
End Try
End Function
End Class
End Namespace

OUTPUT:

STAGEARCHIVESINFORMATION TABLE:

 

 

Extenders
  • DanielWillis's avatar
    DanielWillis
    Valued Contributor
    2 years ago

    The error actually describes the issue exactly here. You're:

    1. trying to add something to a list passing 2 arguments but only one is allowed.

    2. trying to add a string to a list of DBParamInfo

    paramList.Add("@myFileName", myFileName)

    I think perhaps what you want to do is something like (also untested!)

     

    paramList.Add(new DBParamInfo("@myFileName", myFileName))

     

     

    • JackLacava's avatar
      JackLacava
      Honored Contributor
      2 years ago

      Good spot Daniel, that's exactly it, I forgot the wrapping. Will correct.

  • JackLacava's avatar
    JackLacava
    Honored Contributor
    3 years ago

    You'll have to fish that out of the database yourself. Here's some untested code that should give you an idea.

     

    ' have a utility function to perform the query
Public Function getTimeStamp(ByVal si As SessionInfo, ByRef dbConn As DbConnInfo, ByVal myFileName As String)
	Dim sql = "SELECT FileTimeStamp FROM StageArchivesInformation WHERE SourceFileName = @myFileName"
	Dim paramList As New List(Of DbParamInfo)
	paramList.Add(new DbParamInfo("@myFileName", myFileName))
	
	Using resultTable As DataTable = brapi.Database.ExecuteSql(dbConn, sql, paramList, False)
		If resultTable.Rows.Count = 1 Then
			Return resultTable.rows(0).Item(0).ToString("yyyyMMdd")
		Else
			Return Nothing ' or you might want to throw an exception
		End If
	End Using
End Function

' and then somewhere in your Main flow...
Using dbConn As DbConnInfo = BRApi.Database.CreateApplicationDbConnInfo(si)
	For Each fileName In myListOfFileNames
		Dim timestamp As String = Me.getTimeStamp(si, dbConn, fileName)
		If Not (timestamp Is Nothing) Then
			' ... do your renaming
		End If
	Next
End Using

     

    • sdayringer's avatar
      sdayringer
      New Contributor III
      2 years ago

      Hi there - hoping I'm just missing something simple, but I get this error when compiling the rule:

      Namespace OneStream.BusinessRule.Extender._BR_ExportTBLoadFiles_Period_TimeandWFandLoadTime
      Public Class MainClass

      Public Function getTimeStamp(ByVal si As SessionInfo, ByRef dbConn As DbConnInfo, ByVal myFileName As String)
      Dim sql = "SELECT FileTimeStamp FROM StageArchivesInformation WHERE SourceFileName = @myFileName"
      Dim paramList As New List(Of DbParamInfo)
      paramList.Add("@myFileName", myFileName)

      Using resultTable As DataTable = brapi.Database.ExecuteSql(dbConn, sql, paramList, False)
      If resultTable.Rows.Count = 1 Then
      Return resultTable.rows(0).Item(0).ToString("yyyyMMdd")
      Else
      Return Nothing ' or you might want to throw an exception
      End If
      End Using
      End Function

      Public Function Main(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As Object, ByVal args As ExtenderArgs) As Object
      Try

      'DEFINE THE WORKFLOW CLUSTER USED AS THE STARTING POINT TO EXTRACT FROM
      Dim wfClusterPk As New WorkflowUnitClusterPk
      Dim wfProfileName As String = String.Empty
      Dim scenarioName As String = String.Empty
      Dim timeName As String = String.Empty

      'BUSINESS RULE PARAMETERS
      Select Case args.FunctionType
      Case Is = ExtenderFunctionType.Unknown
      wfProfileName = "Entity Level"
      scenarioName = "Actual"
      timeName = "2020M12"
      wfClusterPk = BRAPi.Workflow.General.GetWorkflowUnitClusterPk(si, wfProfileName, scenarioName, timeName)

      'DATA MANAGEMENT PARAMETERS
      Case Is = ExtenderFunctionType.ExecuteDataMgmtBusinessRuleStep
      wfProfileName = args.NameValuePairs("WfProfileName")
      scenarioName = args.NameValuePairs("ScenarioName")
      timeName = args.NameValuePairs("TimeName")
      wfClusterPk = BRAPi.Workflow.General.GetWorkflowUnitClusterPk(si, wfProfileName, scenarioName, timeName)
      End Select

      'PREPARE THE STAGE DATA EXTRACT FILE PATH
      Dim yearName As String = timeName.Split("M")(0)
      Dim yearInt As Integer = timeName.Split("M")(0).XFConvertToInt
      Dim monthName As String = timeName.Split("M")(1)
      Dim monthInt As Integer = timeName.Split("M")(1).XFConvertToInt
      If monthName.Length < 2 Then
      monthName = "0" & monthName
      End If
      Dim periodName As String = $"{monthName}.{yearInt.ToString().Substring(2, 2)}"
      Dim configSettings As AppServerConfigSettings = AppServerConfig.GetSettings(si)
      Dim folderPath As String = FileShareFolderHelper.GetContentsFolderForSystem(si,True,configSettings.FileShareRootFolder)& "\TB Load Files\" & scenarioName & "\Period\" & yearName & "\" & periodName
      If Not Directory.Exists(folderPath) Then Directory.CreateDirectory(folderPath)


      'EXPORT DATA FOR EACH DESCENDANT WORKFLOW PROFILE
      Dim profileInfos As List(Of WorkflowProfileInfo) = BRAPi.Workflow.Metadata.GetRelatives(si, wfClusterPk, WorkflowProfileRelativeTypes.Descendants, WorkflowProfileTypes.InputImportChild)
      If Not profileInfos Is Nothing Then
      For Each profileInfo As WorkflowProfileInfo In profileInfos
      Dim wfClusterPkChild As New WorkflowUnitClusterPk(profileInfo.ProfileKey, wfClusterPk.ScenarioKey, wfClusterPk.TimeKey)
      BRAPi.Import.Data.ExportStageArchives(si, wfClusterPkChild, folderPath, False)


      'RENAME EXPORT FILES
      Using dbConn As DbConnInfo = BRApi.Database.CreateApplicationDbConnInfo(si)
      For Each file As String In Directory.GetFiles(folderPath & "\" & profileInfo.Name)
      Dim timestamp As String = Me.getTimeStamp(si,dbconn,file)
      If Not (timestamp Is Nothing) Then
      Dim sourcePath As String = system.IO.Path.GetDirectoryName(file)
      Dim sourcefilename As String = system.IO.Path.GetFileName(file)
      Dim NewFolder As String = folderPath
      Dim NewFileName As String = timename &"_"& profileInfo.Name & "_"& sourcefilename &".csv"
      Dim destinationFilePath As String = System.IO.Path.Combine(NewFolder, NewFileName)
      System.IO.File.Move(file, destinationFilePath)
      End If
      Next
      End Using

      'DELETE EMPTY FOLDERS
      Dim profilePath As String = folderPath & "\" & profileInfo.Name
      If Not Directory.EnumerateFileSystemEntries(profilePath).Any Then
      Directory.Delete(profilePath)
      Else
      End If
      Next
      End If

      Return Nothing
      Catch ex As Exception
      Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
      End Try
      End Function
      End Class
      End Namespace

  • sdayringer's avatar
    sdayringer
    New Contributor III
    2 years ago

    Thanks, the rule now compiles, but it doesn't seem to be pulling the timestamp...