Add StageArchivesInformation's FileTimeStamp Field to Extracted Filename

sdayringer
New Contributor III

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:

sdayringer_1-1668091466997.png

STAGEARCHIVESINFORMATION TABLE:

sdayringer_2-1668091945230.png

 

 

5 REPLIES 5

JackLacava
Community Manager
Community Manager

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

 

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

sdayringer_0-1678133254189.png

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

DanielWillis
Contributor III

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))

 

 

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

sdayringer
New Contributor III

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

Please sign in! sdayringer