Report Books - If Statement

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
'************************************************************************************************************
'Reference Code:   XFR_MemberList_ByDataBuffer
'Description:      Member List Expansions that use a Data Buffer as a method to determine result lists
'Usage:            Cube View Reports / Report Books as a custom member list
'
'MemberListHasData Function
'    Descriptions:      Generates a list of members that ~have data~. Useful for limiting expansions in CV/QV ~AND~ BOOKS (key!)
'	 Works with:		Account, UD1, UD2, UD3, UD4, UD5, UD6, UD7, UD8
'	 Notes:				X#Member is used as the starting point that will be expanded based upon the provided Member Expansion
'						You only need to populate the parameter if you don't want to use the default value for that paremeter
'						Review the selectedMembers section for setting default values
'	 Report Book Notes: Recommend providing Cube, Entity, Cons, Scenario, Time as part of the Member List function to ensure consistent results
'
'    Parameter Example:
'		X#Member.CustomMemberList(BRName=XFR_MemberList_ByDataBuffer, MemberListName=MemberListHasData,
'		MemberExpansion=[Base|Children],
'		Sort=[True|False],
'		MaxNumOfMembers=[],
'		Cube=[],
'		Entity=[],
'		Cons=[],
'		Scenario=[],
'		Time=[],
'		Flow=[],
'		View=[],
'		Account=[],
'		UD1=[],
'		UD2=[],
'		UD3=[],
'		UD4=[],
'		UD5=[],
'		UD6=[],
'		UD7=[],
'		UD8=[])
'
'Created By:       db_pdx
'Date Created:     2023-11-09
'************************************************************************************************************
' CHANGELOG
' 2024-01-30 | Added UD1 and UD8 expansions to generalize for public consumption per OS-Community request.
'************************************************************************************************************
Namespace OneStream.BusinessRule.Finance.XFR_MemberList_ByDataBuffer
	Public Class MainClass
		Public Function Main(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As FinanceRulesApi, ByVal args As FinanceRulesArgs) As Object
			Try
				Select Case api.FunctionType
					
					Case Is = FinanceFunctionType.MemberListHeaders
						Dim objMemberListHeaders As New List(Of MemberListHeader)
						objMemberListHeaders.Add(New MemberListHeader("MemberListHasData"))
						Return objMemberListHeaders
						
					Case Is = FinanceFunctionType.MemberList
						
						#Region "MemberListHasData"
						If args.MemberListArgs.MemberListName.XFEqualsIgnoreCase("MemberListHasData") Then
							'Return object is: (MemberListHeader, List(Of MemberInfo))
							Dim objMemberListHeader As New MemberListHeader(args.MemberListArgs.MemberListName)
							
							'Note! DUCS members will be pulled from the UserPOV if they are not provided in this MemberList Function
							Dim maxNumberOfMembers As Integer =  Integer.Parse(args.MemberListArgs.NameValuePairs.XFGetValue("MaxNumOfMembers", "0")) 'default of 0 will not apply any member limiting
							Dim sortList         As Boolean = Boolean.Parse(args.MemberListArgs.NameValuePairs.XFGetValue("Sort", "True"))
							Dim memberExpansion  As String = args.MemberListArgs.NameValuePairs.XFGetValue("MemberExpansion", "Base")
							Dim selectedCube     As String = args.MemberListArgs.NameValuePairs.XFGetValue("Cube", api.Pov.Cube.Name)
							Dim selectedEntity   As String = args.MemberListArgs.NameValuePairs.XFGetValue("Entity", api.Pov.Entity.Name)
							Dim selectedCons     As String = args.MemberListArgs.NameValuePairs.XFGetValue("Cons", api.Pov.Cons.Name)
							Dim selectedScenario As String = args.MemberListArgs.NameValuePairs.XFGetValue("Scenario", api.Pov.Scenario.Name)
							Dim selectedTime     As String = args.MemberListArgs.NameValuePairs.XFGetValue("Time", api.Pov.Time.Name)
							Dim selectedFlow     As String = args.MemberListArgs.NameValuePairs.XFGetValue("Flow", "SetYourDefaultFlow")				'SET A DEFAULT HERE
							Dim selectedView     As String = args.MemberListArgs.NameValuePairs.XFGetValue("View", "SetYourDefaultView") 				'SET A DEFAULT HERE
							Dim selectedAccount  As String = args.MemberListArgs.NameValuePairs.XFGetValue("Account", "SetYourDefaultAccount")		'SET A DEFAULT HERE
							Dim selectedUD1      As String = args.MemberListArgs.NameValuePairs.XFGetValue("UD1", "SetYourDefaultUD1")		'SET A DEFAULT HERE
							Dim selectedUD2      As String = args.MemberListArgs.NameValuePairs.XFGetValue("UD2", "SetYourDefaultUD2")		'SET A DEFAULT HERE
							Dim selectedUD3      As String = args.MemberListArgs.NameValuePairs.XFGetValue("UD3", "SetYourDefaultUD3")		'SET A DEFAULT HERE
							Dim selectedUD4      As String = args.MemberListArgs.NameValuePairs.XFGetValue("UD4", "SetYourDefaultUD4")		'SET A DEFAULT HERE
							Dim selectedUD5      As String = args.MemberListArgs.NameValuePairs.XFGetValue("UD5", "SetYourDefaultUD5")			'SET A DEFAULT HERE
							Dim selectedUD6      As String = args.MemberListArgs.NameValuePairs.XFGetValue("UD6", "SetYourDefaultUD6")			'SET A DEFAULT HERE
							Dim selectedUD7      As String = args.MemberListArgs.NameValuePairs.XFGetValue("UD7", "SetYourDefaultUD7")		'SET A DEFAULT HERE
							Dim selectedUD8      As String = args.MemberListArgs.NameValuePairs.XFGetValue("UD8", "SetYourDefaultUD8")				'SET A DEFAULT HERE
							
							'Common parameters for the DataBuffer call
							Dim fixedFilter As String = String.Empty	'stores the fixed members used in the data buffer
							Dim fullFilter  As String = String.Empty	'stores the full filtermembers() used in GetDataBufferWithFormula
							
							'Determine which member we are going to expand upon.  It should be based on the MemberDim we are dealing with
							Select Case args.MemberListArgs.DimPk.DimTypeId
								Case DimType.Account.Id
									'Override whatever was provided in Account args with the initial ("top") member of the MemberList selection
									selectedAccount = args.MemberListArgs.TopMember.Name
									'Create our DataBuffer filter
									fixedFilter = $"Cb#{selectedCube}:E#{selectedEntity}:C#{selectedCons}:S#{selectedScenario}:T#{selectedTime}:V#{selectedView}:F#{selectedFlow}:O#Top:I#Top:U1#{selectedUD1}:U2#{selectedUD2}:U3#{selectedUD3}:U4#{selectedUD4}:U5#{selectedUD5}:U6#{selectedUD6}:U7#{selectedUD7}:U8#{selectedUD8}"
									fullFilter  = $"FilterMembers(RemoveZeros({fixedFilter}), [A#{selectedAccount}.{memberExpansion}])"
								Case DimType.UD1.Id
									'Override whatever was provided in UD1 args with the initial ("top") member of the MemberList selection
									selectedUD1 = args.MemberListArgs.TopMember.Name
									'Create our DataBuffer filter
									fixedFilter = $"Cb#{selectedCube}:E#{selectedEntity}:C#{selectedCons}:S#{selectedScenario}:T#{selectedTime}:V#{selectedView}:F#{selectedFlow}:O#Top:I#Top:A#{selectedAccount}:U2#{selectedUD2}:U3#{selectedUD3}:U4#{selectedUD4}:U5#{selectedUD5}:U6#{selectedUD6}:U7#{selectedUD7}:U8#{selectedUD8}"
									fullFilter  = $"FilterMembers(RemoveZeros({fixedFilter}), [U1#{selectedUD1}.{memberExpansion}])"
								Case DimType.UD2.Id
									'Override whatever was provided in UD2 args with the initial ("top") member of the MemberList selection
									selectedUD2 = args.MemberListArgs.TopMember.Name
									'Create our DataBuffer filter
									fixedFilter = $"Cb#{selectedCube}:E#{selectedEntity}:C#{selectedCons}:S#{selectedScenario}:T#{selectedTime}:V#{selectedView}:F#{selectedFlow}:O#Top:I#Top:A#{selectedAccount}:U1#{selectedUD1}:U3#{selectedUD3}:U4#{selectedUD4}:U5#{selectedUD5}:U6#{selectedUD6}:U7#{selectedUD7}:U8#{selectedUD8}"
									fullFilter  = $"FilterMembers(RemoveZeros({fixedFilter}), [U2#{selectedUD2}.{memberExpansion}])"
								Case DimType.UD3.Id
									'Override whatever was provided in UD3 args with the initial ("top") member of the MemberList selection
									selectedUD3 = args.MemberListArgs.TopMember.Name
									'Create our DataBuffer filter
									fixedFilter = $"Cb#{selectedCube}:E#{selectedEntity}:C#{selectedCons}:S#{selectedScenario}:T#{selectedTime}:V#{selectedView}:F#{selectedFlow}:O#Top:I#Top:A#{selectedAccount}:U1#{selectedUD1}:U2#{selectedUD2}:U4#{selectedUD4}:U5#{selectedUD5}:U6#{selectedUD6}:U7#{selectedUD7}:U8#{selectedUD8}"
									fullFilter  = $"FilterMembers(RemoveZeros({fixedFilter}), [U3#{selectedUD3}.{memberExpansion}])"
								Case DimType.UD4.Id
									'Override whatever was provided in UD4 args with the initial ("top") member of the MemberList selection
									selectedUD4 = args.MemberListArgs.TopMember.Name
									'Create our DataBuffer filter
									fixedFilter = $"Cb#{selectedCube}:E#{selectedEntity}:C#{selectedCons}:S#{selectedScenario}:T#{selectedTime}:V#{selectedView}:F#{selectedFlow}:O#Top:I#Top:A#{selectedAccount}:U1#{selectedUD1}:U2#{selectedUD2}:U3#{selectedUD3}:U5#{selectedUD5}:U6#{selectedUD6}:U7#{selectedUD7}:U8#{selectedUD8}"
									fullFilter  = $"FilterMembers(RemoveZeros({fixedFilter}), [U4#{selectedUD4}.{memberExpansion}])"
								Case DimType.UD5.Id
									'Override whatever was provided in UD5 args with the initial ("top") member of the MemberList selection
									selectedUD5 = args.MemberListArgs.TopMember.Name
									'Create our DataBuffer filter
									fixedFilter = $"Cb#{selectedCube}:E#{selectedEntity}:C#{selectedCons}:S#{selectedScenario}:T#{selectedTime}:V#{selectedView}:F#{selectedFlow}:O#Top:I#Top:A#{selectedAccount}:U1#{selectedUD1}:U2#{selectedUD2}:U3#{selectedUD3}:U4#{selectedUD4}:U6#{selectedUD6}:U7#{selectedUD7}:U8#{selectedUD8}"
									fullFilter  = $"FilterMembers(RemoveZeros({fixedFilter}), [U5#{selectedUD5}.{memberExpansion}])"
								Case DimType.UD6.Id
									'Override whatever was provided in UD6 args with the initial ("top") member of the MemberList selection
									selectedUD6 = args.MemberListArgs.TopMember.Name
									'Create our DataBuffer filter
									fixedFilter = $"Cb#{selectedCube}:E#{selectedEntity}:C#{selectedCons}:S#{selectedScenario}:T#{selectedTime}:V#{selectedView}:F#{selectedFlow}:O#Top:I#Top:A#{selectedAccount}:U1#{selectedUD1}:U2#{selectedUD2}:U3#{selectedUD3}:U4#{selectedUD4}:U5#{selectedUD5}:U7#{selectedUD7}:U8#{selectedUD8}"
									fullFilter  = $"FilterMembers(RemoveZeros({fixedFilter}), [U6#{selectedUD6}.{memberExpansion}])"
								Case DimType.UD7.Id
									'Override whatever was provided in UD7 args with the initial ("top") member of the MemberList selection
									selectedUD7 = args.MemberListArgs.TopMember.Name
									'Create our DataBuffer filter
									fixedFilter = $"Cb#{selectedCube}:E#{selectedEntity}:C#{selectedCons}:S#{selectedScenario}:T#{selectedTime}:V#{selectedView}:F#{selectedFlow}:O#Top:I#Top:A#{selectedAccount}:U1#{selectedUD1}:U2#{selectedUD2}:U3#{selectedUD3}:U4#{selectedUD4}:U5#{selectedUD5}:U6#{selectedUD6}:U8#{selectedUD8}"
									fullFilter  = $"FilterMembers(RemoveZeros({fixedFilter}), [U7#{selectedUD7}.{memberExpansion}])"
								Case DimType.UD8.Id
									'Override whatever was provided in UD8 args with the initial ("top") member of the MemberList selection
									selectedUD8 = args.MemberListArgs.TopMember.Name
									'Create our DataBuffer filter
									fixedFilter = $"Cb#{selectedCube}:E#{selectedEntity}:C#{selectedCons}:S#{selectedScenario}:T#{selectedTime}:V#{selectedView}:F#{selectedFlow}:O#Top:I#Top:A#{selectedAccount}:U1#{selectedUD1}:U2#{selectedUD2}:U3#{selectedUD3}:U4#{selectedUD4}:U5#{selectedUD5}:U6#{selectedUD6}:U7#{selectedUD7}"
									fullFilter  = $"FilterMembers(RemoveZeros({fixedFilter}), [U8#{selectedUD8}.{memberExpansion}])"
								Case Else
									Throw New Exception("The dimension used is NOT valid for this custom member list.")
							End Select
							
							'Get the Data Buffer
							Dim dataBuffer As DataBuffer = api.Data.GetDataBufferUsingFormula(fullFilter, DataApiScriptMethodType.Calculate, False)				
							
							'Create a dictionary so that we can sort the buffer
							'As well as limit the number of records returned
							'Finally return a List(Of Member) [or MemberInfo]
							Dim dataBufferDictionary As New Dictionary(Of MemberScriptBuilder, Decimal)
							Dim objMembers As New List(Of Member)
							
							If dataBuffer IsNot Nothing Then
								'Move to dictionary
								For Each dbCell As DataBufferCell In dataBuffer.DataBufferCells.Values
									dataBufferDictionary.Add(dbCell.DataBufferCellPk.CreateMemberScriptBuilder(api), dbCell.CellAmount)
								Next
								'Sort
								If sortList Then
									dataBufferDictionary = (From entry In dataBufferDictionary Order By entry.Value Descending).ToDictionary(Function(x) x.Key, Function(x) x.Value)
								End If
								'Limit
								If Not (maxNumberOfMembers = 0) Then
									dataBufferDictionary = dataBufferDictionary.Take(maxNumberOfMembers).ToDictionary(Function(x) x.Key, Function(x) x.Value)
								End If
								'Move to List of Members
								For Each key In dataBufferDictionary.Keys
									'Again, need to toggle based on what Dimension we are expecting to return
									Select Case args.MemberListArgs.DimPk.DimTypeId
										Case DimType.Account.Id
											objMembers.Add(api.Members.GetMember(args.MemberListArgs.DimPk.DimTypeId, key.Account))
										Case DimType.UD1.Id
											objMembers.Add(api.Members.GetMember(args.MemberListArgs.DimPk.DimTypeId, key.UD1))
										Case DimType.UD2.Id
											objMembers.Add(api.Members.GetMember(args.MemberListArgs.DimPk.DimTypeId, key.UD2))
										Case DimType.UD3.Id
											objMembers.Add(api.Members.GetMember(args.MemberListArgs.DimPk.DimTypeId, key.UD3))
										Case DimType.UD4.Id
											objMembers.Add(api.Members.GetMember(args.MemberListArgs.DimPk.DimTypeId, key.UD4))
										Case DimType.UD5.Id
											objMembers.Add(api.Members.GetMember(args.MemberListArgs.DimPk.DimTypeId, key.UD5))
										Case DimType.UD6.Id
											objMembers.Add(api.Members.GetMember(args.MemberListArgs.DimPk.DimTypeId, key.UD6))
										Case DimType.UD7.Id
											objMembers.Add(api.Members.GetMember(args.MemberListArgs.DimPk.DimTypeId, key.UD7))
										Case DimType.UD8.Id
											objMembers.Add(api.Members.GetMember(args.MemberListArgs.DimPk.DimTypeId, key.UD8))
									End Select 'DimType
								Next 'key
							Else
								Return Nothing
							End If 'dataBuffer is Nothing
							
							'Return the final object (MemberListHeader, List(Of Member/(Info)))
							Dim objMemberList As New MemberList(objMemberListHeader, objMembers)
							Return objMemberList
						#End Region
						End If
					
					#Region "Not used"
'					Case Is = FinanceFunctionType.DataCell
'					Case Is = FinanceFunctionType.FxRate
'					Case Is = FinanceFunctionType.Calculate
'					Case Is = FinanceFunctionType.ConditionalInput
'					Case Is = FinanceFunctionType.CustomCalculate
'					Case Is = FinanceFunctionType.ReadSourceDataRecords
					#End Region
				End Select

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