-- GET USER ASSIGNMENTS
INSERT INTO #Temp(SoftwareTitle, AssignmentId, LicenseId, License, Version, LicenseType, LicenseUnit, LicensedQty,
LicenseKeys, UserName, HardwareAsset, HardwareAssetId, UserAssigned, Installed)
(
SELECT A.DisplayName AS SoftwareTitle, E.SoftwareAssignmentDimKey, G.SoftwareLicenseDimKey, G.DisplayName AS License,
L.DisplayName AS Version, J.LicenseType, O.LicenseUnit,
A.TotalLicenseQuantity AS LicensedQuantity, '' AS LicenseKeys, P.DisplayName AS UserName,
R.DisplayName AS HardwareAsset, R.HardwareAssetDimKey, E.Quantity AS UserAssigned, W.InstalledCount
FROM SoftwareTitleDimvw A
INNER JOIN SoftwareTitleAssignmentSetFactvw B ON A.SoftwareTitleDimKey = B.SoftwareTitleDimKey
AND B.DeletedDate IS NULL
INNER JOIN SoftwareAssignmentSetDimvw C ON B.SoftwareTitleHasAssignmentSet_SoftwareAssignmentSetDimKey = C.SoftwareAssignmentSetDimKey
AND C.IsDeleted = 0
AND (C.ObjectStatus IS NULL OR C.ObjectStatus <> @ObjectStatusPendingDelete)
INNER JOIN SoftwareAssignmentSetAssignmentFactvw D ON C.SoftwareAssignmentSetDimKey = D.SoftwareAssignmentSetDimKey
AND D.DeletedDate IS NULL
INNER JOIN SoftwareAssignmentDimvw E ON D.AssignmentSetContainsAssignment_SoftwareAssignmentDimKey = E.SoftwareAssignmentDimKey
AND E.IsDeleted = 0
AND (E.ObjectStatus IS NULL OR E.ObjectStatus <> @ObjectStatusPendingDelete)
LEFT OUTER JOIN SoftwareAssignmentSoftwareLicenseFactvw F ON E.SoftwareAssignmentDimKey = F.SoftwareAssignmentDimKey
AND F.DeletedDate IS NULL
LEFT OUTER JOIN SoftwareLicenseDimvw G ON F.AssignmentHasSoftwareLicense_SoftwareLicenseDimKey = G.SoftwareLicenseDimKey
AND G.IsDeleted = 0
AND (G.ObjectStatus IS NULL OR G.ObjectStatus <> @ObjectStatusPendingDelete)
LEFT OUTER JOIN SoftwareLicenseSkuFactvw H ON G.SoftwareLicenseDimKey = H.SoftwareLicenseDimKey
AND H.DeletedDate IS NULL
LEFT OUTER JOIN SkuDimvw I ON H.SoftwareLicenseHasSku_SkuDimKey = I.SkuDimKey
AND I.IsDeleted = 0
AND (I.ObjectStatus IS NULL OR I.ObjectStatus <> @ObjectStatusPendingDelete)
LEFT OUTER JOIN (SELECT A.LicenseTypeId, ISNULL(B.DisplayName, A.LicenseTypeValue) AS LicenseType
FROM LicenseType A
LEFT OUTER JOIN DisplayStringDim B ON A.EnumTypeId = B.BaseManagedEntityId
AND B.LanguageCode = @UserLanguage
) J ON I.LicenseType_LicenseTypeId = J.LicenseTypeId
LEFT OUTER JOIN SkuSoftwareVersionFactvw K ON I.SkuDimKey = K.SkuDimKey
AND K.DeletedDate IS NULL
LEFT OUTER JOIN SoftwareVersionDimvw L ON K.SkuHasSoftwareVersion_SoftwareVersionDimKey = L.SoftwareVersionDimKey
AND L.IsDeleted = 0
AND (L.ObjectStatus IS NULL OR L.ObjectStatus <> @ObjectStatusPendingDelete)
INNER JOIN SoftwareAssignmentConfigItemTargetFactvw M ON E.SoftwareAssignmentDimKey = M.SoftwareAssignmentDimKey
AND M.DeletedDate IS NULL
INNER JOIN ConfigItemDimvw N ON M.AssignmentIsForTarget_ConfigItemDimKey = N.ConfigItemDimKey
AND N.IsDeleted = 0
AND (N.ObjectStatus IS NULL OR N.ObjectStatus <> @ObjectStatusPendingDelete)
LEFT OUTER JOIN (SELECT A.LicenseUnitId, ISNULL(B.DisplayName, A.LicenseUnitValue) AS LicenseUnit
FROM LicenseUnit A
LEFT OUTER JOIN DisplayStringDim B ON A.EnumTypeId = B.BaseManagedEntityId
AND B.LanguageCode = @UserLanguage
) O ON I.LicenseUnit_LicenseUnitId = O.LicenseUnitId
INNER JOIN UserDimvw P ON N.EntityDimKey = P.EntityDimKey
AND P.IsDeleted = 0
AND (P.ObjectStatus IS NULL OR P.ObjectStatus <> @ObjectStatusPendingDelete)
LEFT OUTER JOIN SystemUserDimvw Q ON P.EntityDimKey = Q.EntityDimKey
AND Q.IsDeleted = 0
AND (Q.ObjectStatus IS NULL OR Q.ObjectStatus <> @ObjectStatusPendingDelete)
LEFT OUTER JOIN (SELECT HAO.HardwareAssetOwnedBySystemUser_SystemUserDimKey AS SystemUserDimKey,
HAO.HardwareAssetDimKey, HA.DisplayName, HA.EntityDimKey
FROM HardwareAssetOwnerFactvw HAO
INNER JOIN HardwareAssetDimvw HA ON HAO.HardwareAssetDimKey = HA.HardwareAssetDimKey
AND HA.IsDeleted = 0
AND (HA.ObjectStatus IS NULL OR HA.ObjectStatus <> @ObjectStatusPendingDelete)
AND HA.LifecycleStatus NOT IN (@LifecycleDisposed, @LifecycleReturned)
WHERE HAO.DeletedDate IS NULL ) R ON Q.SystemUserDimKey = R.SystemUserDimKey
LEFT OUTER JOIN ConfigItemDimvw S ON R.EntityDimKey = S.EntityDimKey
AND S.IsDeleted = 0
AND (S.ObjectStatus IS NULL OR S.ObjectStatus <> @ObjectStatusPendingDelete)
LEFT OUTER JOIN (SELECT SAC.AssignmentIsForTarget_ConfigItemDimKey, SASA.SoftwareAssignmentSetDimKey,
SA.Quantity
FROM SoftwareAssignmentConfigItemTargetFactvw SAC
INNER JOIN SoftwareAssignmentDimvw SA ON SAC.SoftwareAssignmentDimKey = SA.SoftwareAssignmentDimKey
AND SA.IsDeleted = 0
AND (SA.ObjectStatus IS NULL OR SA.ObjectStatus <> @ObjectStatusPendingDelete)
INNER JOIN SoftwareAssignmentSetAssignmentFactvw SASA ON SA.SoftwareAssignmentDimKey = SASA.AssignmentSetContainsAssignment_SoftwareAssignmentDimKey
AND SASA.DeletedDate IS NULL
WHERE SAC.DeletedDate IS NULL ) T ON S.ConfigItemDimKey = T.AssignmentIsForTarget_ConfigItemDimKey
AND C.SoftwareAssignmentSetDimKey = T.SoftwareAssignmentSetDimKey
LEFT OUTER JOIN HardwareAssetComputerFactvw U ON R.HardwareAssetDimKey = U.HardwareAssetDimKey
AND U.DeletedDate IS NULL
LEFT OUTER JOIN ComputerDimvw V ON U.HardwareAssetReferencesConfigItem_ComputerDimKey = V.ComputerDimKey
AND V.IsDeleted = 0
AND (V.ObjectStatus IS NULL OR V.ObjectStatus <> @ObjectStatusPendingDelete)
LEFT OUTER JOIN (SELECT DISI.ComputerDimKey, SV.DisplayName, SV.SoftwareVersionDimKey, COUNT(SV.SoftwareVersionDimKey) AS InstalledCount
FROM SoftwareVersionDimvw SV
INNER JOIN VersionDiscoveredItemFactvw SVDI ON SV.SoftwareVersionDimKey = SVDI.SoftwareVersionDimKey
AND SVDI.DeletedDate IS NULL
INNER JOIN SoftwareItemDimvw SI ON SVDI.SoftwareVersionReferencesDiscoveredSoftwareItem_SoftwareItemDimKey = SI.SoftwareItemDimKey
AND SI.IsDeleted = 0
AND (SI.ObjectStatus IS NULL OR SI.ObjectStatus <> @ObjectStatusPendingDelete)
INNER JOIN DeviceInstalledSoftwareItemFactvw DISI ON SI.SoftwareItemDimKey = DISI.DeviceHasSoftwareItemInstalled_SoftwareItemDimKey
AND DISI.DeletedDate IS NULL
WHERE SV.IsDeleted = 0
AND (SV.ObjectStatus IS NULL OR SV.ObjectStatus <> @ObjectStatusPendingDelete)
GROUP BY DISI.ComputerDimKey, SV.DisplayName, SV.SoftwareVersionDimKey
) W ON L.SoftwareVersionDimKey = W.SoftwareVersionDimKey
AND V.ComputerDimKey = W.ComputerDimKey
WHERE A.IsDeleted = 0
AND (A.ObjectStatus IS NULL OR A.ObjectStatus <> @ObjectStatusPendingDelete)
AND A.SoftwareTitleDimKey = @SoftwareTitleId
)
-- GET HARDWARE ASSIGNMENTS
INSERT INTO #Temp(SoftwareTitle, AssignmentId, LicenseId, License, Version, LicenseType, LicenseUnit, LicensedQty,
LicenseKeys, HardwareAsset, HardwareAssetId, UserName, AssetAssigned, Installed)
(
SELECT A.DisplayName AS SoftwareTitle, E.SoftwareAssignmentDimKey, G.SoftwareLicenseDimKey, G.DisplayName AS License,
L.DisplayName AS Version, J.LicenseType, O.LicenseUnit,
A.TotalLicenseQuantity AS LicensedQuantity, '' AS LicenseKeys, P.DisplayName AS HardwareAsset,
P.HardwareAssetDimKey, Q.DisplayName AS UserName, E.Quantity, V.InstalledCount
FROM SoftwareTitleDimvw A
INNER JOIN SoftwareTitleAssignmentSetFactvw B ON A.SoftwareTitleDimKey = B.SoftwareTitleDimKey
AND B.DeletedDate IS NULL
INNER JOIN SoftwareAssignmentSetDimvw C ON B.SoftwareTitleHasAssignmentSet_SoftwareAssignmentSetDimKey = C.SoftwareAssignmentSetDimKey
AND C.IsDeleted = 0
AND (C.ObjectStatus IS NULL OR C.ObjectStatus <> @ObjectStatusPendingDelete)
INNER JOIN SoftwareAssignmentSetAssignmentFactvw D ON C.SoftwareAssignmentSetDimKey = D.SoftwareAssignmentSetDimKey
AND D.DeletedDate IS NULL
INNER JOIN SoftwareAssignmentDimvw E ON D.AssignmentSetContainsAssignment_SoftwareAssignmentDimKey = E.SoftwareAssignmentDimKey
AND E.IsDeleted = 0
AND (E.ObjectStatus IS NULL OR E.ObjectStatus <> @ObjectStatusPendingDelete)
LEFT OUTER JOIN SoftwareAssignmentSoftwareLicenseFactvw F ON E.SoftwareAssignmentDimKey = F.SoftwareAssignmentDimKey
AND F.DeletedDate IS NULL
LEFT OUTER JOIN SoftwareLicenseDimvw G ON F.AssignmentHasSoftwareLicense_SoftwareLicenseDimKey = G.SoftwareLicenseDimKey
AND G.IsDeleted = 0
AND (G.ObjectStatus IS NULL OR G.ObjectStatus <> @ObjectStatusPendingDelete)
LEFT OUTER JOIN SoftwareLicenseSkuFactvw H ON G.SoftwareLicenseDimKey = H.SoftwareLicenseDimKey
AND H.DeletedDate IS NULL
LEFT OUTER JOIN SkuDimvw I ON H.SoftwareLicenseHasSku_SkuDimKey = I.SkuDimKey
AND I.IsDeleted = 0
AND (I.ObjectStatus IS NULL OR I.ObjectStatus <> @ObjectStatusPendingDelete)
LEFT OUTER JOIN (SELECT A.LicenseTypeId, ISNULL(B.DisplayName, A.LicenseTypeValue) AS LicenseType
FROM LicenseType A
LEFT OUTER JOIN DisplayStringDim B ON A.EnumTypeId = B.BaseManagedEntityId
AND B.LanguageCode = @UserLanguage
) J ON I.LicenseType_LicenseTypeId = J.LicenseTypeId
LEFT OUTER JOIN SkuSoftwareVersionFactvw K ON I.SkuDimKey = K.SkuDimKey
AND K.DeletedDate IS NULL
LEFT OUTER JOIN SoftwareVersionDimvw L ON K.SkuHasSoftwareVersion_SoftwareVersionDimKey = L.SoftwareVersionDimKey
AND L.IsDeleted = 0
AND (L.ObjectStatus IS NULL OR L.ObjectStatus <> @ObjectStatusPendingDelete)
INNER JOIN SoftwareAssignmentConfigItemTargetFactvw M ON E.SoftwareAssignmentDimKey = M.SoftwareAssignmentDimKey
AND M.DeletedDate IS NULL
INNER JOIN ConfigItemDimvw N ON M.AssignmentIsForTarget_ConfigItemDimKey = N.ConfigItemDimKey
AND N.IsDeleted = 0
AND (N.ObjectStatus IS NULL OR N.ObjectStatus <> @ObjectStatusPendingDelete)
LEFT OUTER JOIN (SELECT A.LicenseUnitId, ISNULL(B.DisplayName, A.LicenseUnitValue) AS LicenseUnit
FROM LicenseUnit A
LEFT OUTER JOIN DisplayStringDim B ON A.EnumTypeId = B.BaseManagedEntityId
AND B.LanguageCode = @UserLanguage
) O ON I.LicenseUnit_LicenseUnitId = O.LicenseUnitId
INNER JOIN HardwareAssetDimvw P ON N.EntityDimKey = P.EntityDimKey
AND P.IsDeleted = 0
AND (P.ObjectStatus IS NULL OR P.ObjectStatus <> @ObjectStatusPendingDelete)
LEFT OUTER JOIN (SELECT HAO.HardwareAssetOwnedBySystemUser_SystemUserDimKey AS SystemUserDimKey,
HAO.HardwareAssetDimKey, SU.DisplayName, SU.EntityDimKey, U.UserDimKey AS UserId
FROM HardwareAssetOwnerFactvw HAO
INNER JOIN SystemUserDim SU ON HAO.HardwareAssetOwnedBySystemUser_SystemUserDimKey = SU.SystemUserDimKey
AND SU.IsDeleted=0
AND (SU.ObjectStatus IS NULL OR SU.ObjectStatus <> @ObjectStatusPendingDelete)
INNER JOIN UserDim U ON SU.EntityDimKey = U.EntityDimKey
WHERE HAO.DeletedDate IS NULL) Q ON P.HardwareAssetDimKey = Q.HardwareAssetDimKey
LEFT OUTER JOIN ConfigItemDimvw R ON Q.EntityDimKey = R.EntityDimKey
AND R.IsDeleted = 0
AND (R.ObjectStatus IS NULL OR R.ObjectStatus <> @ObjectStatusPendingDelete)
LEFT OUTER JOIN (SELECT SAC.AssignmentIsForTarget_ConfigItemDimKey, SASA.SoftwareAssignmentSetDimKey,
SA.Quantity
FROM SoftwareAssignmentConfigItemTargetFactvw SAC
INNER JOIN SoftwareAssignmentDimvw SA ON SAC.SoftwareAssignmentDimKey = SA.SoftwareAssignmentDimKey
AND SA.IsDeleted = 0
AND (SA.ObjectStatus IS NULL OR SA.ObjectStatus <> @ObjectStatusPendingDelete)
INNER JOIN SoftwareAssignmentSetAssignmentFactvw SASA ON SA.SoftwareAssignmentDimKey = SASA.AssignmentSetContainsAssignment_SoftwareAssignmentDimKey
AND SASA.DeletedDate IS NULL
WHERE SAC.DeletedDate IS NULL ) S ON R.ConfigItemDimKey = S.AssignmentIsForTarget_ConfigItemDimKey
AND C.SoftwareAssignmentSetDimKey = S.SoftwareAssignmentSetDimKey
LEFT OUTER JOIN HardwareAssetComputerFactvw T ON Q.HardwareAssetDimKey = T.HardwareAssetDimKey
AND T.DeletedDate IS NULL
LEFT OUTER JOIN ComputerDimvw U ON T.HardwareAssetReferencesConfigItem_ComputerDimKey = U.ComputerDimKey
AND U.IsDeleted = 0
AND (U.ObjectStatus IS NULL OR U.ObjectStatus <> @ObjectStatusPendingDelete)
LEFT OUTER JOIN (SELECT DISI.ComputerDimKey, SV.DisplayName, SV.SoftwareVersionDimKey, COUNT(SV.SoftwareVersionDimKey) AS InstalledCount
FROM SoftwareVersionDimvw SV
INNER JOIN VersionDiscoveredItemFactvw SVDI ON SV.SoftwareVersionDimKey = SVDI.SoftwareVersionDimKey
AND SVDI.DeletedDate IS NULL
INNER JOIN SoftwareItemDimvw SI ON SVDI.SoftwareVersionReferencesDiscoveredSoftwareItem_SoftwareItemDimKey = SI.SoftwareItemDimKey
AND SI.IsDeleted = 0
AND (SI.ObjectStatus IS NULL OR SI.ObjectStatus <> @ObjectStatusPendingDelete)
INNER JOIN DeviceInstalledSoftwareItemFactvw DISI ON SI.SoftwareItemDimKey = DISI.DeviceHasSoftwareItemInstalled_SoftwareItemDimKey
AND DISI.DeletedDate IS NULL
WHERE SV.IsDeleted = 0
AND (SV.ObjectStatus IS NULL OR SV.ObjectStatus <> @ObjectStatusPendingDelete)
GROUP BY DISI.ComputerDimKey, SV.DisplayName, SV.SoftwareVersionDimKey
) V ON L.SoftwareVersionDimKey = V.SoftwareVersionDimKey
AND U.ComputerDimKey = V.ComputerDimKey
WHERE A.IsDeleted = 0
AND (A.ObjectStatus IS NULL OR A.ObjectStatus <> @ObjectStatusPendingDelete)
AND A.SoftwareTitleDimKey = @SoftwareTitleId
)
INSERT INTO #Temp2
SELECT *
FROM #Temp
GROUP BY SoftwareTitle, AssignmentId, LicenseId, License, Version, LicenseType, LicenseUnit, LicensedQty, LicenseAssigned,
UserName, HardwareAsset, HardwareAssetId, UserAssigned, AssetAssigned, Installed, LicenseKeys
DROP TABLE #Temp
-- get total assigned for all assignments that are assigned to license level
UPDATE A
SET A.LicenseAssigned = B.TotalAssigned
FROM #Temp2 A
INNER JOIN (SELECT UserGroup.LicenseId, SUM(UserGroup.AssetAssigned) + SUM(UserGroup.UserAssigned) AS TotalAssigned
FROM
(SELECT A.LicenseId, SUM(ISNULL(A.AssetAssigned,0)) AS AssetAssigned,
MAX(ISNULL(A.UserAssigned,0)) AS UserAssigned
FROM #Temp2 A
GROUP BY A.LicenseId, A.UserName) UserGroup
GROUP BY UserGroup.LicenseId
) B ON A.LicenseId = B.LicenseId
-- get total assigned for all assignments that are not assigned to license level
UPDATE A
SET A.LicenseAssigned = (SELECT SUM(UserGroup.AssetAssigned) + SUM(UserGroup.UserAssigned) AS TotalAssigned
FROM (SELECT SUM(ISNULL(A.AssetAssigned,0)) AS AssetAssigned,
MAX(ISNULL(A.UserAssigned,0)) AS UserAssigned
FROM #Temp2 A
WHERE A.LicenseId IS NULL
GROUP BY A.LicenseId, A.UserName) UserGroup
)
FROM #Temp2 A
WHERE A.LicenseId IS NULL
-- get rid of hardware asset and installed values against user assignment if we also have an assignment against that asset
UPDATE A
SET A.HardwareAsset = NULL,
A.HardwareAssetId = NULL,
A.Installed = NULL
FROM #Temp2 A
INNER JOIN (SELECT LicenseId, UserName, HardwareAsset
FROM #Temp2
WHERE AssetAssigned IS NOT NULL
)B ON A.LicenseId = B.LicenseId
AND A.UserName = B.UserName
AND A.HardwareAsset = B.HardwareAsset
WHERE A.UserAssigned IS NOT NULL
DECLARE @AssignmentFetchStatus int
DECLARE crAssignments CURSOR FOR
SELECT AssignmentId
FROM #Temp2
OPEN crAssignments
FETCH NEXT FROM crAssignments INTO @assignment
DECLARE crKeys CURSOR FOR
SELECT B.[Key]
FROM SoftwareAssignmentLicenseKeyFactvw A
INNER JOIN SoftwareLicenseKeyDimvw B ON A.AssignmentHasSoftwareLicenseKeys_SoftwareLicenseKeyDimKey = B.SoftwareLicenseKeyDimKey
AND B.IsDeleted = 0
WHERE A.SoftwareAssignmentDimKey = @assignment
OPEN crKeys
FETCH NEXT FROM crKeys INTO @Key
SELECT @KeyFetchStatus = @@FETCH_STATUS
WHILE @KeyFetchStatus = 0
BEGIN
SELECT @KeyList = @KeyList + @Delimiter + @Key
SELECT @Delimiter = ', '
FETCH NEXT FROM crKeys INTO @Key
SELECT @KeyFetchStatus = @@FETCH_STATUS
END
CLOSE crKeys
DEALLOCATE crKeys
UPDATE #Temp2
SET LicenseKeys = @KeyList
WHERE AssignmentId = @assignment
FETCH NEXT FROM crAssignments INTO @assignment
SELECT @AssignmentFetchStatus = @@FETCH_STATUS
END
CLOSE crAssignments
DEALLOCATE crAssignments
SELECT *
FROM #Temp2
GROUP BY SoftwareTitle, AssignmentId, LicenseId, License, Version, LicenseType, LicenseUnit, LicensedQty, LicenseAssigned,
UserName, HardwareAsset, HardwareAssetId, UserAssigned, AssetAssigned, Installed, LicenseKeys
ORDER BY License
Const TimeZoneParameterName As String = "TimeZone"
Const SD_BaseTypeParameterName As String = "StartDate_BaseType"
Const SD_BaseValueParameterName As String = "StartDate_BaseValue"
Const SD_OffsetTypeParameterName As String = "StartDate_OffsetType"
Const SD_OffsetValueParameterName As String = "StartDate_OffsetValue"
Const ED_BaseTypeParameterName As String = "EndDate_BaseType"
Const ED_BaseValueParameterName As String = "EndDate_BaseValue"
Const ED_OffsetTypeParameterName As String = "EndDate_OffsetType"
Const ED_OffsetValueParameterName As String = "EndDate_OffsetValue"
Const IsRelativeTimeSupported As Boolean = False
Const TimeTypeParameterName As String = "TimeType"
Const TimeWeekMapParameterName As String = "TimeWeekMap"
Dim LocTables As System.Collections.Generic.Dictionary(Of String, Microsoft.EnterpriseManagement.Reporting.XmlStringTable)
Dim ReportTimeZone As Microsoft.EnterpriseManagement.Reporting.TimeZoneCoreInformation
Dim ReportStartDate As DateTime
Dim ReportEndDate As DateTime
Dim ReportTime As Microsoft.EnterpriseManagement.Reporting.ParameterProcessor.RelativeTime
Dim ReportCulture As System.Globalization.CultureInfo
Dim ParameterProcessor As Microsoft.EnterpriseManagement.Reporting.ParameterProcessor
Dim TargetList As String
Protected Overrides Sub OnInit()
LocTables = new System.Collections.Generic.Dictionary(Of String, Microsoft.EnterpriseManagement.Reporting.XmlStringTable)()
ReportTimeZone = Microsoft.EnterpriseManagement.Reporting.TimeZoneInformation.Current
ReportStartDate = DateTime.MinValue
ReportEndDate = DateTime.MinValue
ReportTime = Nothing
ReportCulture = System.Globalization.CultureInfo.GetCultureInfo(Report.User("Language"))
ParameterProcessor = New Microsoft.EnterpriseManagement.Reporting.ParameterProcessor(ReportCulture)
TargetList =Nothing
End Sub
'Public Function GetCallingManagementGroupId() As String
' Return 'Microsoft.EnterpriseManagement.Reporting.ReportingConfiguration.M'anagementGroupId
'End Function
Public Function GetReportLocLanguageCode() As String
Return ReportCulture.ThreeLetterWindowsLanguageName
End Function
Public Function GetReportLCID() As Integer
Return ReportCulture.LCID
End Function
Public Function GetLocTable(Name As String) As Microsoft.EnterpriseManagement.Reporting.XmlStringTable
Dim LocTable As Microsoft.EnterpriseManagement.Reporting.XmlStringTable
If Not LocTables.TryGetValue(Name, LocTable) Then
LocTable = New Microsoft.EnterpriseManagement.Reporting.XmlStringTable(LocTableStringQuery, Report.Parameters(Name).Value)
LocTables.Add(Name, LocTable)
End If
Return LocTable
End Function
Public Function GetReportTimeZone() As Microsoft.EnterpriseManagement.Reporting.TimeZoneCoreInformation
If IsNothing(ReportTimeZone) Then ReportTimeZone = Microsoft.EnterpriseManagement.Reporting.TimeZoneCoreInformation.FromValueString(Report.Parameters(TimeZoneParameterName).Value)
Return ReportTimeZone
End Function
Public Function ToDbDate(ByVal DateValue As DateTime) As DateTime
return GetReportTimeZone.ToUniversalTime(DateValue)
End Function
Public Function ToReportDate(ByVal DateValue As DateTime) As DateTime
return GetReportTimeZone.ToLocalTime(DateValue)
End Function
Public Function GetReportStartDate() As DateTime
If (ReportStartDate = DateTime.MinValue) Then
If (IsRelativeTimeSupported) Then
ReportStartDate = ParameterProcessor.GetDateTime(ToReportDate(DateTime.UtcNow), Report.Parameters(SD_BaseTypeParameterName).Value, Report.Parameters(SD_BaseValueParameterName).Value, Report.Parameters(SD_OffsetTypeParameterName).Value, Report.Parameters(SD_OffsetValueParameterName).Value, Report.Parameters(TimeTypeParameterName).Value)
Else
ReportStartDate = ParameterProcessor.GetDateTime(ToReportDate(DateTime.UtcNow), Report.Parameters(SD_BaseTypeParameterName).Value, Report.Parameters(SD_BaseValueParameterName).Value, Report.Parameters(SD_OffsetTypeParameterName).Value, Report.Parameters(SD_OffsetValueParameterName).Value)
End if
End If
return ReportStartDate
End Function
Public Function GetReportEndDate() As DateTime
If (ReportEndDate = DateTime.MinValue) Then
If (IsRelativeTimeSupported) Then
ReportEndDate = ParameterProcessor.GetDateTime(ToReportDate(DateTime.UtcNow), Report.Parameters(ED_BaseTypeParameterName).Value, Report.Parameters(ED_BaseValueParameterName).Value, Report.Parameters(ED_OffsetTypeParameterName).Value, Report.Parameters(ED_OffsetValueParameterName).Value, Report.Parameters(TimeTypeParameterName).Value)
If IsBusinessHours(GetReportTimeFilter()) Then ReportEndDate = ReportCulture.Calendar.AddDays(ReportEndDate, 1)
Else
ReportEndDate = ParameterProcessor.GetDateTime(ToReportDate(DateTime.UtcNow), Report.Parameters(ED_BaseTypeParameterName).Value, Report.Parameters(ED_BaseValueParameterName).Value, Report.Parameters(ED_OffsetTypeParameterName).Value, Report.Parameters(ED_OffsetValueParameterName).Value)
End if
End If
return ReportEndDate
End Function
Public Function GetReportTimeFilter() As Microsoft.EnterpriseManagement.Reporting.ParameterProcessor.RelativeTime
If IsNothing(ReportTime) Then ReportTime = New Microsoft.EnterpriseManagement.Reporting.ParameterProcessor.RelativeTime(Report.Parameters(TimeTypeParameterName).Value, Report.Parameters(SD_BaseValueParameterName).Value, Report.Parameters(ED_BaseValueParameterName).Value, CStr(Join(Report.Parameters(TimeWeekMapParameterName).Value, ",")))
return ReportTime
End Function
Public Function IsBusinessHours(Value As Microsoft.EnterpriseManagement.Reporting.ParameterProcessor.RelativeTime) As Boolean
return (Not IsNothing(Value)) And (Value.TimeType = Microsoft.EnterpriseManagement.Reporting.ParameterProcessor.RelativeTimeType.Business)
End Function
Public Function FormatDateTime(Format As String, Value As DateTime) As String
return Value.ToString(Format, ReportCulture)
End Function
Public Function FormatNumber(Format As String, Value As Decimal) As String
return Value.ToString(Format, ReportCulture)
End Function
Public Function FormatString(Format As String, ParamArray Values() as Object) As String
return String.Format(ReportCulture, Format, Values)
End Function
Public Function NullFormatString(Format As String, Value as String) As String
return IIF(String.IsNullOrEmpty(Value), String.Empty, String.Format(ReportCulture, Format, Value))
End Function
Public Function FormatBusinessHours(Format As String, Value As Microsoft.EnterpriseManagement.Reporting.ParameterProcessor.RelativeTime) As String
Dim result As String
If IsBusinessHours(Value) Then
Dim firstDay As DayOfWeek
Dim days As System.Collections.Generic.List(Of String)
firstDay = ReportCulture.DateTimeFormat.FirstDayOfWeek
days = new System.Collections.Generic.List(Of String)()
For loopDay As DayOfWeek = DayOfWeek.Sunday To DayOfWeek.Saturday
Dim day As DayOfWeek
day = CType((CInt(loopDay) + CInt(firstDay)) Mod 7, DayOfWeek)
If value.WeekMap.Contains(day) Then days.Add(ReportCulture.DateTimeFormat.GetAbbreviatedDayName(day))
Next loopDay
result = FormatString(Format, DateTime.Today.Add(Value.StartTime).ToString(ReportCulture.DateTimeFormat.ShortTimePattern), DateTime.Today.Add(Value.EndTime).ToString(ReportCulture.DateTimeFormat.ShortTimePattern), String.Join(",", days.ToArray()))
Else
result = String.Empty
End if
return result
End Function
Public Function BuildXmlValueList(ByVal ValueList() As Object) As String
Return Microsoft.EnterpriseManagement.Reporting.MultiValueParameter.ToXml("Data", "Value", ValueList)
End Function
REM -------------------------------------------------
Dim ReportFormatList As System.Collections.Generic.Dictionary(Of String, IDataFormatter)
Const FormatListParameterName As String = "DataFormat"
REM -------------------------------------------------
Public Interface IDataFormatter
Function FormatData(ByVal Value As String) As String
End Interface
Public Class RankDataFormatter
Implements IDataFormatter
Private Series As System.Collections.Generic.SortedDictionary(Of Decimal, String)
Private Culture As System.Globalization.CultureInfo
Public Sub New(ByVal Config As System.Xml.XmlNode, ByVal ReportCulture As System.Globalization.CultureInfo)
Culture = ReportCulture
Series = New System.Collections.Generic.SortedDictionary(Of Decimal, String)()
For Each SeriesXml As System.Xml.XmlNode In Config.ChildNodes
Series.Add(Decimal.Parse(SeriesXml.Attributes("Rank").Value), SeriesXml.Attributes("Format").Value)
Next
End Sub
Public Function FormatData(ByVal Value As String) As String Implements IDataFormatter.FormatData
Dim Result As String = CDec(Value).ToString("G", Culture)
For Each FormatItem As System.Collections.Generic.KeyValuePair(Of Decimal, String) In Series
Dim RunningValue As Decimal = Math.Round(Value / FormatItem.Key)
If RunningValue > 0 Then
Result = String.Format(Culture, FormatItem.Value, RunningValue.ToString("G", Culture))
Else
Exit For
End If
Next
Return Result
End Function
End Class
Public Class LookupDataFormatter
Implements IDataFormatter
Private MappingTable As System.Collections.Generic.IDictionary(Of String, String)
Public Sub New(ByVal Config As System.Xml.XmlNode)
MappingTable = New System.Collections.Generic.Dictionary(Of String, String)(StringComparer.OrdinalIgnoreCase)
For Each SeriesXml As System.Xml.XmlNode In Config.ChildNodes
MappingTable.Add(Decimal.Parse(SeriesXml.Attributes("Value").Value), SeriesXml.Attributes("Label").Value)
Next
End Sub
Public Function FormatData(ByVal Value As String) As String Implements IDataFormatter.FormatData
Dim Result As String
If MappingTable.ContainsKey(Value) Then
Result = MappingTable(Value)
Else
Result = Value
End If
Return Result
End Function
End Class
Public Function GetReportFormatList() As System.Collections.Generic.Dictionary(Of String, IDataFormatter)
If IsNothing(ReportFormatList) Then
Dim Xml As System.Xml.XmlDocument = New System.Xml.XmlDocument()
Xml.LoadXml(Report.Parameters(FormatListParameterName).Value)
ReportFormatList = New System.Collections.Generic.Dictionary(Of String, IDataFormatter)(StringComparer.OrdinalIgnoreCase)
For Each Node As System.Xml.XmlNode In Xml.DocumentElement.ChildNodes
Dim Formatter As IDataFormatter = Nothing
Select Case Node.Attributes("Type").Value.ToUpper()
Case "RANK"
Formatter = New RankDataFormatter(Node, ReportCulture)
Case "LOOKUP"
Formatter = New LookupDataFormatter(Node)
End Select
If Not IsNothing(Formatter) Then
ReportFormatList.Add(Node.Attributes("Name").Value, Formatter)
End If
Next
End If
Return ReportFormatList
End Function
Public Function FormatData(ByVal FormatName As String, ByVal DataType As String, ByVal Value As String)
Dim FormatList As System.Collections.Generic.Dictionary(Of String, IDataFormatter) = GetReportFormatList()
If Not String.IsNullOrEmpty(FormatName) And Not IsNothing(FormatList) Then
If FormatList.ContainsKey(FormatName) Then
Return FormatList(FormatName).FormatData(Value)
End If
End If
If Not String.IsNullOrEmpty(DataType) Then
If DataType = "DateTime" Then
Return FormatDateTime("g", CDate(Value))
ElseIf DataType.StartsWith("UInt") Then
Return FormatNumber("G", Value)
End If
End If
Return Value
End Function
REM ----------------------------------------
Public Dim CurrentObjectList As String
Public Function InitList(ByRef List As String) As String
List = String.Empty
Return List
End Function
Public Function AddListItem(ByRef List As String, Item as String) As String
List = List + Item
Return List
End Function
Public Function GetObjectList(ByVal OptionsXml As String) As String()
Dim Xml As System.Xml.XmlDocument
Xml = New System.Xml.XmlDocument()
Xml.LoadXml(OptionsXml)
Dim Result As System.Collections.Generic.List(Of String)
Result = New System.Collections.Generic.List(Of String)
For Each ObjectNode As System.Xml.XmlNode In Xml.SelectNodes("/Value/Object")
If Not Result.Contains(ObjectNode.InnerText) Then
Result.Add(ObjectNode.InnerText)
End If
Next
Home