How to Dynamically Report on Extended Members from the Top Consolidation Cube?

Imports System
Imports System.Collections.Generic
Imports System.Data
Imports System.Data.Common
Imports System.Globalization
Imports System.IO
Imports System.Linq
Imports System.Windows.Forms
Imports Microsoft.VisualBasic
Imports OneStream.Finance.Database
Imports OneStream.Finance.Engine
Imports OneStream.Shared.Common
Imports OneStream.Shared.Database
Imports OneStream.Shared.Engine
Imports OneStream.Shared.Wcf
Imports OneStream.Stage.Database
Imports OneStream.Stage.Engine
Imports System.Diagnostics
Imports System.Text.RegularExpressions
Imports System.Threading
Imports System.Threading.Tasks
Imports System.Collections.Concurrent


Namespace OneStream.BusinessRule.Finance.XF_DynamicExtensibilityReporting
	Public Class MainClass
		
		#Region "Not in use"
		Public Function Main(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As FinanceRulesApi, ByVal args As FinanceRulesArgs) As Object
			Try
				
				Return Nothing
			Catch ex As Exception
				Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
			End Try
		End Function
		#End Region 
		
		Public Function DynamicExtensibilityReporting (ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As FinanceRulesApi) As Decimal
		
			Try

				If api.Entity.HasChildren AndAlso api.Pov.Cube.CubeReferences.Values.Count > 0 'Dynamic calc is run at Parent Entity Level for the TOP Consolidation Cube
					
					Dim ParentEntity As String = api.Pov.Entity.Name
					Dim PovTime As String = api.Pov.Time.Name
					
					'Retrieve Current UD8 Text Properties:
					Dim U8Text1 As String = api.UD8.Text(api.Pov.UD8.MemberId,1,api.Pov.ScenarioTypeId,api.Pov.Time.MemberId)
	
					Dim DimTag As String = ""
					Dim FixedSourcePOV As String = ""
					
					If U8Text1.StartsWith("DimTag=",stringcomparison.CurrentCultureIgnoreCase) Then 
						DimTag = StringHelper.SplitString(U8Text1,"=")(1)
						DimTag = StringHelper.RemoveEnclosingSquareBrackets(DimTag)
					End If
				
					Dim DynamicPOV As New Dictionary (Of String, String) From {{"A#",$"{api.pov.Account.Name}"}, {"F#",$"{api.pov.Flow.Name}"}, {"O#",$"{api.pov.Origin.Name}"}, {"I#",$"{api.pov.IC.Name}"}, {"U1#",$"{api.pov.UD1.Name}"}, {"U2#",$"{api.pov.UD2.Name}"}, {"U3#",$"{api.pov.UD3.Name}"}, {"U4#",$"{api.pov.UD4.Name}"}, {"U5#",$"{api.pov.UD5.Name}"}, {"U6#",$"{api.pov.UD6.Name}"}, {"U7#",$"{api.pov.UD7.Name}"}, {"U8#","None"}}

					'Gets the Source Data Buffer POV
					Dim DimSourcePov As String = Me.GetSourceDataBufferPOV(si,globals,api,DimTag,DynamicPOV) 
					
					'Creates data buffer where we store the Aggregated data for all base Entities from the given Parent Entity:
					Dim AggEntityData As New DataBUffer()

					'Data is pulled from all base Entities by Extended Dim as defined in U8 Text1 (DimTag=<>)
					If Globals.GetObject($"{ParentEntity}{PovTime}{DimSourcePov}") Is Nothing
						Dim BaseEntityList As List(Of Member) = api.Members.GetBaseMembers(api.Pov.EntityDim.DimPk,api.Pov.Entity.MemberId)
						AggEntityData = Me.AggregateEntityDataBuffer(si,api,BaseEntityList,DimSourcePov)
						Globals.SetObject($"{ParentEntity}{PovTime}{DimSourcePov}",AggEntityData)
					Else	
						AggEntityData = globals.GetObject($"{ParentEntity}{PovTime}{DimSourcePov}")
					End If

					'Defines Target CellPK to pull from the Aggregated Data Buffer - Pull cell by Extended Dim Member:
					Dim GetTargetCellPK As DataBufferCellPk = Me.GetTargetDataBufferCellPk(si,globals,api,DimTag)
					
					If Not AggEntityData.GetCell(si,GetTargetCellPK) Is Nothing	'If Target Cell Exists within AggDataBuffer we pick it and return the Cell Amount:					

						Return AggEntityData.GetCell(si,GetTargetCellPK).CellAmount
						
					Else 'If Cell Does not exist within the Data Buffer, aggregate values through Extended Dim:
						Dim DoesBufferExist As Boolean = False
						api.Data.FormulaVariables.GetDataBuffer($"{ParentEntity}{PovTime}AggEntityData",DoesBufferExist)
						If Not DoesBufferExist Then 
							api.Data.FormulaVariables.SetDataBufferVariable($"{ParentEntity}{PovTime}AggEntityData",AggEntityData,True)
						End If 	
						Dim FilteredAggEntityData As DataBuffer = api.Data.GetDataBufferUsingFormula($"FilterMembers(${ParentEntity}{PovTime}AggEntityData, [{DimTag}{DynamicPOV(DimTag)}.base])") 'ie: DataBufferFilter = [U1#api.pov.UD1.name.base]
						
						Dim TotalCellAmount As Decimal = Decimal.Zero
						TotalCellAmount += Aggregate DriverCell In FilteredAggEntityData.DataBufferCells.Values Into Sum(DriverCell.CellAmount)

						Return TotalCellAmount
					End If	


				Else 'If its base Entity, return base product cell: 
					
					Return api.Data.GetDataCell("U8#None").CellAmount	
					
				End If
												
				
				Return Nothing
			Catch ex As Exception
				Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
			End Try
			
		End Function
		
		Public Function AggregateEntityDataBuffer(ByVal si As SessionInfo, ByVal api As FinanceRulesApi, ByVal DataUnitList As List(Of Member), ByVal SourceFormulaString As String, ByVal Optional DataBufferFilters As String = "" ) As DataBUffer

		'  Aggregates All Data Units (Entities) based in a DataUnitList and provided Filters
		'  
		'  Version		Date		Author							Comment
		'  -------------------------------------------------------------------------------------------------------------
		'  V1.0			25-04-2024	A.Formenti 						Initial Version
		'  
		'  Changes:
		'  
		'______________________________________________________________________________________________________________

			Try
				
				Dim ResultBuffer As New DataBuffer()

				Dim TargetCurrency As Currency = api.Entity.GetLocalCurrency(api.Pov.Entity.MemberId)
				
				For Each CalcEntity As Member In DataUnitList

					'Get the FX Rate based on the Entity and Account POV:
					Dim SourceCurrency As Currency = api.Entity.GetLocalCurrency(CalcEntity.MemberId)
					
					
					Dim rateFx As Decimal = 1
					
					Dim rateType As New FxRateType
					Dim ruleType As New FxRuleType

					If sourceCurrency.Id <> targetCurrency.Id Then
						Dim accountType As String = api.Account.GetAccountType(api.Pov.Account.MemberId).Name
						If accountType.XFEqualsIgnoreCase("Asset") OrElse accountType.XFEqualsIgnoreCase("Liability")
							rateType = api.FxRates.GetFxRateTypeForAssetLiability(DimConstants.DefaultPOV, DimConstants.DefaultPOV)
							ruleType = api.FxRates.GetFxRuleTypeForAssetLiability(DimConstants.DefaultPOV, DimConstants.DefaultPOV)
						Else If accountType.XFEqualsIgnoreCase("Revenue") OrElse accountType.XFEqualsIgnoreCase("Expense")
							rateType = api.FxRates.GetFxRateTypeForRevenueExp(DimConstants.DefaultPOV, DimConstants.DefaultPOV)
							ruleType = api.FxRates.GetFxRuleTypeForRevenueExp(DimConstants.DefaultPOV, DimConstants.DefaultPOV)
						End If 
						rateFx = api.FxRates.GetCalculatedFxRate(rateType, DimConstants.DefaultPOV, DimConstants.DefaultPOV, sourceCurrency.Id, targetCurrency.Id)	
					End If
					api.Data.FormulaVariables.SetDecimalVariable("FXRate",rateFx)
					
					Dim SourceDataBuffer As New DataBuffer
					Dim AggDataBuffer As New DataBuffer

					 If api.Pov.View.Name.XFEqualsIgnoreCase("YTD") AndAlso ruleType.Name.XFEqualsIgnoreCase("Periodic") 'If View is YTD and Rule Type Periodic - Apply Periodic Translation
						
						If sourceCurrency.Id <> targetCurrency.Id Then 'If needs translation, translate on the fly with Periodic Method:
							Dim TimeList As List (Of MemberInfo) = api.Members.GetMembersUsingFilter(api.Pov.TimeDim.DimPk, $"T#{api.pov.time.name}.AllPriorInYearInclusive")
							For Each sTime As MemberInfo In TimeList
								rateFx = api.FxRates.GetCalculatedFxRate(rateType, DimConstants.DefaultPOV, sTime.Member.MemberId, sourceCurrency.Id, targetCurrency.Id)
								api.Data.FormulaVariables.SetDecimalVariable("FXRate",rateFx)
								Dim SourceDataBufferByTime As DataBuffer = api.Data.GetDataBufferUsingFormula($"FilterMembers(RemoveZeros(V#Periodic:T#{sTime.Member.Name}:E#{CalcEntity.Name}:P#[{api.Pov.Parent.name}]:C#Local:{SourceFormulaString}) * $FXRate, {DataBufferFilters})",,True)	
								'Converts Data Buffer to get Aggregated Extended Members:
								Dim AggDataBufferByTime As DataBuffer = api.Data.ConvertDataBufferExtendedMembers(api.Pov.Cube.name,CalcEntity.Name,api.Pov.Scenario.Name,SourceDataBufferByTime)
								
								'Accumulates both Base and Extended Members Data Buffers
								For Each AggCell As DataBufferCell In AggDataBufferByTime.DataBufferCells.Values
									 If Not SourceDataBufferByTime.DataBufferCells.ContainsKey(AggCell.DataBufferCellPk)
										 SourceDataBufferByTime.SetCell(si,AggCell)
									 End If 
								 Next AggCell
								
								'Loop through the Buffer and accumulates data by Time:
								For Each sCell As DataBufferCell In SourceDataBufferByTime.DataBufferCells.Values
									SourceDataBuffer.SetCell(si,sCell,True)
								Next sCell
								
							Next sTime
						End If 
						
					Else 'If View Is Periodic or Direct Translation:
						
						 SourceDataBuffer = api.Data.GetDataBufferUsingFormula($"FilterMembers(RemoveZeros(E#{CalcEntity.Name}:P#[{api.Pov.Parent.name}]:C#Local:{SourceFormulaString}) * $FXRate, {DataBufferFilters})",,True)					
						 'Converts Data Buffer to get Aggregated Extended Members:
						 AggDataBuffer = api.Data.ConvertDataBufferExtendedMembers(api.Pov.Cube.name,CalcEntity.Name,api.Pov.Scenario.Name,SourceDataBuffer)	
						 'Accumulates both Base and Extended Members Data Buffers
						 For Each AggCell As DataBufferCell In AggDataBuffer.DataBufferCells.Values
							 If Not SourceDataBuffer.DataBufferCells.ContainsKey(AggCell.DataBufferCellPk)
								 SourceDataBuffer.SetCell(si,AggCell)
							 End If 
						 Next AggCell 

					End If 	
					
					'Loop through the Buffer and accumulates data by Entity:
					For Each sCell As DataBufferCell In SourceDataBuffer.DataBufferCells.Values
						ResultBuffer.SetCell(si,sCell,True)
					Next sCell	
					
				Next CalcEntity

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

		Private Function GetSourceDataBufferPOV(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As FinanceRulesApi, ByVal DimTag As String, ByVal DynamicPOV As Dictionary (Of String, String)) As String  
				
			Try
				
				'  Gets the Source Data Buffer POV
				'  
				'  Version		Date		Author							Comment
				'  -------------------------------------------------------------------------------------------------------------
				'  V1.0			05-04-2024	A.Formenti 						Initial Version
				'  
				'  Changes:
				'  
				'______________________________________________________________________________________________________________

				Dim DimSourcePov As String = ""
			
				'Builds Up the Source POV:
				Dim sPovBuilder As New System.Text.StringBuilder
				
				For Each sDimTag As String In DynamicPOV.keys
					If  Not sDimTag.StartsWith(DimTag, StringComparison.CurrentCultureIgnoreCase) Then 'Add all Dynamic Dims POV except for Extended Dim
						sPovBuilder.Append($":{sDimTag}{DynamicPOV(sDimTag)}")
					End If 	
				Next sDimTag
								
				'Source POV:
				DimSourcePov = sPovBuilder.Remove(0,1).ToString
				globals.SetObject($"GetSourcePOV_{api.pov.UD8.Name}",DimSourcePov)

				Return DimSourcePov

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

		Private Function GetTargetDataBufferCellPk(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As FinanceRulesApi, ByVal DimTag As String) As DataBufferCellPk  
				
			Try
				
				'  Gets the Target DataBufferCellPK
				'  
				'  Version		Date		Author							Comment
				'  -------------------------------------------------------------------------------------------------------------
				'  V1.0			05-04-2024	A.Formenti 						Initial Version
				'  
				'  Changes:
				'  
				'______________________________________________________________________________________________________________
				
				
				Dim TargetBufferCellPk As New DataBufferCellPk
				
				
				'By Default all members pulled from XFCommon:
				TargetBufferCellPk.SetMembers(api,"XFCommon","XFCommon","XFCommon","XFCommon","XFCommon","XFCommon","XFCommon","XFCommon","XFCommon","XFCommon","XFCommon","XFCommon")
					
				'Extended dim to be pulled from the api.pov:
				If DimTag.XFEqualsIgnoreCase("A#") Then 
					TargetBufferCellPk.AccountId = api.Pov.Account.MemberId
				Else If DimTag.XFEqualsIgnoreCase("F#") Then
					TargetBufferCellPk.AccountId = api.Pov.Flow.MemberId
				Else If DimTag.XFEqualsIgnoreCase("O#") Then
					TargetBufferCellPk.AccountId = api.Pov.Origin.MemberId
				Else If DimTag.XFEqualsIgnoreCase("I#") Then
					TargetBufferCellPk.AccountId = api.Pov.IC.MemberId
				Else If DimTag.XFEqualsIgnoreCase("U1#") Then
					TargetBufferCellPk.AccountId = api.Pov.UD1.MemberId
				Else If DimTag.XFEqualsIgnoreCase("U2#") Then
					TargetBufferCellPk.AccountId = api.Pov.UD2.MemberId
				Else If DimTag.XFEqualsIgnoreCase("U3#") Then
					TargetBufferCellPk.AccountId = api.Pov.UD3.MemberId
				Else If DimTag.XFEqualsIgnoreCase("U4#") Then
					TargetBufferCellPk.AccountId = api.Pov.UD4.MemberId
				Else If DimTag.XFEqualsIgnoreCase("U5#") Then
					TargetBufferCellPk.AccountId = api.Pov.UD5.MemberId
				Else If DimTag.XFEqualsIgnoreCase("U6#") Then
					TargetBufferCellPk.AccountId = api.Pov.UD6.MemberId
				Else If DimTag.XFEqualsIgnoreCase("U7#") Then
					TargetBufferCellPk.AccountId = api.Pov.UD7.MemberId
				End If 
			
			

				Return TargetBufferCellPk

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

	End Class
End Namespace