INSERT INTO #temp(AssetAssigned,AssignmentSet,HardwareAsset,HardwareAssetId,UserAssigned,UserName,UserId,
VersionInstalledCount,SoftwareTitle,LicensedQuantity,SoftwareTitleId,AllocationQuantity,PublisherId)
SELECT DISTINCT U.AssetAssigned, U.AssignmentSet, U.HardwareAsset, U.HardwareAssetDimKey, U.UserAssigned,
U.UserName, U.UserId, U.VersionInstalledCount, U.SoftwareTitle, U.LicensedQuantity,
U.SoftwareTitleDimKey, X.AllocationQuantity, U.PublisherId
FROM (
SELECT A.SoftwareAssignmentSetDimKey AS AssignmentSet, B.Quantity AS UserAssigned, E.DisplayName AS UserName, E.UserDimKey AS UserId,
F.DisplayName AS HardwareAsset, G.Quantity AS AssetAssigned, F.HardwareAssetDimKey,
SUM(K.InstalledCount) AS VersionInstalledCount, AC.DisplayName AS SoftwareTitle, AC.TotalLicenseQuantity as LicensedQuantity, AC.SoftwareTitleDimKey,
L.CompanyDimKey AS PublisherId
FROM SoftwareAssignmentSetAssignmentFactvw A
INNER JOIN SoftwareAssignmentSetDimvw AA ON A.SoftwareAssignmentSetDimKey = AA.SoftwareAssignmentSetDimKey
AND AA.IsDeleted = 0
AND (AA.ObjectStatus IS NULL OR AA.ObjectStatus <> @ObjectStatusPendingDelete)
INNER JOIN SoftwareTitleAssignmentSetFactvw AB ON AA.SoftwareAssignmentSetDimKey = AB.SoftwareTitleHasAssignmentSet_SoftwareAssignmentSetDimKey
AND AB.DeletedDate IS NULL
INNER JOIN SoftwareTitleDimvw AC ON AB.SoftwareTitleDimKey = AC.SoftwareTitleDimKey
AND AC.IsDeleted = 0
AND (AC.ObjectStatus IS NULL OR AC.ObjectStatus <> @ObjectStatusPendingDelete)
INNER JOIN SoftwareAssignmentDimvw B ON A.AssignmentSetContainsAssignment_SoftwareAssignmentDimKey = B.SoftwareAssignmentDimKey
AND B.IsDeleted = 0
AND (B.ObjectStatus IS NULL OR B.ObjectStatus <> @ObjectStatusPendingDelete)
INNER JOIN SoftwareAssignmentConfigItemTargetFactvw C ON B.SoftwareAssignmentDimKey = C.SoftwareAssignmentDimKey
AND C.DeletedDate IS NULL
INNER JOIN ConfigItemDimvw D ON C.AssignmentIsForTarget_ConfigItemDimKey = D.ConfigItemDimKey
AND D.IsDeleted = 0
AND (D.ObjectStatus IS NULL OR D.ObjectStatus <> @ObjectStatusPendingDelete)
INNER JOIN UserDimvw E ON D.EntityDimKey = E.EntityDimKey
AND D.IsDeleted = 0
AND (D.ObjectStatus IS NULL OR D.ObjectStatus <> @ObjectStatusPendingDelete)
LEFT OUTER JOIN SystemUserDimvw EA ON E.EntityDimKey = EA.EntityDimKey
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 ) F ON EA.SystemUserDimKey = F.SystemUserDimKey
LEFT OUTER JOIN ConfigItemDimvw FA ON F.EntityDimKey = FA.EntityDimKey
AND FA.IsDeleted = 0
AND (FA.ObjectStatus IS NULL OR FA.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 ) G ON FA.ConfigItemDimKey = G.AssignmentIsForTarget_ConfigItemDimKey
AND A.SoftwareAssignmentSetDimKey = G.SoftwareAssignmentSetDimKey
LEFT OUTER JOIN HardwareAssetComputerFactvw I ON F.HardwareAssetDimKey = I.HardwareAssetDimKey
AND I.DeletedDate IS NULL
LEFT OUTER JOIN ComputerDimvw J ON I.HardwareAssetReferencesConfigItem_ComputerDimKey = J.ComputerDimKey
AND J.IsDeleted = 0
AND (J.ObjectStatus IS NULL OR J.ObjectStatus <> @ObjectStatusPendingDelete)
LEFT OUTER JOIN (SELECT DISI.ComputerDimKey, SV.DisplayName, STV.SoftwareTitleDimKey, COUNT(SV.SoftwareVersionDimKey) AS InstalledCount
FROM SoftwareTitleVersionFactvw STV
INNER JOIN SoftwareVersionDimvw SV ON STV.SoftwareTitleHasSoftwareVersion_SoftwareVersionDimKey = SV.SoftwareVersionDimKey
AND SV.IsDeleted = 0
AND (SV.ObjectStatus IS NULL OR SV.ObjectStatus <> @ObjectStatusPendingDelete)
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 STV.DeletedDate IS NULL
GROUP BY DISI.ComputerDimKey, SV.DisplayName, STV.SoftwareTitleDimKey
) K ON AB.SoftwareTitleDimKey = K.SoftwareTitleDimKey
AND J.ComputerDimKey = K.ComputerDimKey
LEFT OUTER JOIN PublisherSoftwareTitleFactvw L ON AC.SoftwareTitleDimKey = L.PublisherHasSoftwareTitle_SoftwareTitleDimKey
AND L.DeletedDate IS NULL
GROUP BY A.SoftwareAssignmentSetDimKey, B.Quantity, E.DisplayName, E.UserDimKey,
F.DisplayName, G.Quantity, F.HardwareAssetDimKey,
AC.DisplayName, AC.AvailableQuantity, AC.SoftwareTitleDimKey,
L.CompanyDimKey, AC.TotalLicenseQuantity
UNION
SELECT A.SoftwareAssignmentSetDimKey AS AssignmentSet, G.Quantity AS UserAssigned,F.DisplayName AS UserName,F.UserId AS UserId,
E.DisplayName AS HardwareAsset, B.Quantity AS AssetAssigned, E.HardwareAssetDimKey,
SUM(K.InstalledCount) AS VersionInstalledCount, AC.DisplayName AS SoftwareTitle, AC.TotalLicenseQuantity as LicensedQuantity, AC.SoftwareTitleDimKey,
L.CompanyDimKey AS PublisherId
FROM SoftwareAssignmentSetAssignmentFactvw A
INNER JOIN SoftwareAssignmentSetDimvw AA ON A.SoftwareAssignmentSetDimKey = AA.SoftwareAssignmentSetDimKey
AND AA.IsDeleted = 0
AND (AA.ObjectStatus IS NULL OR AA.ObjectStatus <> @ObjectStatusPendingDelete)
INNER JOIN SoftwareTitleAssignmentSetFactvw AB ON AA.SoftwareAssignmentSetDimKey = AB.SoftwareTitleHasAssignmentSet_SoftwareAssignmentSetDimKey
AND AB.DeletedDate IS NULL
INNER JOIN SoftwareTitleDimvw AC ON AB.SoftwareTitleDimKey = AC.SoftwareTitleDimKey
AND AC.IsDeleted = 0
AND (AC.ObjectStatus IS NULL OR AC.ObjectStatus <> @ObjectStatusPendingDelete)
INNER JOIN SoftwareAssignmentDimvw B ON A.AssignmentSetContainsAssignment_SoftwareAssignmentDimKey = B.SoftwareAssignmentDimKey
AND B.IsDeleted = 0
AND (B.ObjectStatus IS NULL OR B.ObjectStatus <> @ObjectStatusPendingDelete)
INNER JOIN SoftwareAssignmentConfigItemTargetFactvw C ON B.SoftwareAssignmentDimKey = C.SoftwareAssignmentDimKey
AND C.DeletedDate IS NULL
INNER JOIN ConfigItemDimvw D ON C.AssignmentIsForTarget_ConfigItemDimKey = D.ConfigItemDimKey
INNER JOIN HardwareAssetDimvw E ON D.EntityDimKey = E.EntityDimKey
AND E.IsDeleted = 0
AND (E.ObjectStatus IS NULL OR E.ObjectStatus <> @ObjectStatusPendingDelete)
AND E.LifecycleStatus NOT IN (@LifecycleDisposed, @LifecycleReturned)
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) F ON E.HardwareAssetDimKey = F.HardwareAssetDimKey
LEFT OUTER JOIN ConfigItemDimvw FA ON F.EntityDimKey = FA.EntityDimKey
AND FA.IsDeleted = 0
AND (FA.ObjectStatus IS NULL OR FA.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 ) G ON FA.ConfigItemDimKey = G.AssignmentIsForTarget_ConfigItemDimKey
AND A.SoftwareAssignmentSetDimKey = G.SoftwareAssignmentSetDimKey
LEFT OUTER JOIN HardwareAssetComputerFactvw I ON E.HardwareAssetDimKey = I.HardwareAssetDimKey
AND I.DeletedDate IS NULL
LEFT OUTER JOIN ComputerDimvw J ON I.HardwareAssetReferencesConfigItem_ComputerDimKey = J.ComputerDimKey
AND J.IsDeleted = 0
AND (J.ObjectStatus IS NULL OR J.ObjectStatus <> @ObjectStatusPendingDelete)
LEFT OUTER JOIN (SELECT DISI.ComputerDimKey, SV.DisplayName, STV.SoftwareTitleDimKey, COUNT(SV.SoftwareVersionDimKey) AS InstalledCount
FROM SoftwareTitleVersionFactvw STV
INNER JOIN SoftwareVersionDimvw SV ON STV.SoftwareTitleHasSoftwareVersion_SoftwareVersionDimKey = SV.SoftwareVersionDimKey
AND SV.IsDeleted = 0
AND (SV.ObjectStatus IS NULL OR SV.ObjectStatus <> @ObjectStatusPendingDelete)
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 STV.DeletedDate IS NULL
GROUP BY DISI.ComputerDimKey, SV.DisplayName, STV.SoftwareTitleDimKey
) K ON AB.SoftwareTitleDimKey = K.SoftwareTitleDimKey
AND J.ComputerDimKey = K.ComputerDimKey
LEFT OUTER JOIN PublisherSoftwareTitleFactvw L ON AC.SoftwareTitleDimKey = L.PublisherHasSoftwareTitle_SoftwareTitleDimKey
AND L.DeletedDate IS NULL
GROUP BY A.SoftwareAssignmentSetDimKey, G.Quantity,F.DisplayName,F.UserId,
E.DisplayName, B.Quantity, E.HardwareAssetDimKey,
AC.DisplayName, AC.TotalLicenseQuantity, AC.SoftwareTitleDimKey,
L.CompanyDimKey
) U
LEFT OUTER JOIN SoftwareTitleAllocationSetFactvw Z ON U.SoftwareTitleDimKey = Z.SoftwareTitleDimKey
AND Z.DeletedDate IS NULL
LEFT OUTER JOIN SoftwareAllocationSetDimvw Y ON Z.SoftwareTitleHasAllocationSet_SoftwareAllocationSetDimKey = Y.SoftwareAllocationSetDimKey
AND Y.IsDeleted = 0
AND (Y.ObjectStatus IS NULL OR Y.ObjectStatus <> @ObjectStatusPendingDelete)
LEFT OUTER JOIN (SELECT SASA.SoftwareAllocationSetDimKey, SUM(SAD.Quantity) AS AllocationQuantity
FROM SoftwareAllocationSetAllocationFactvw SASA
INNER JOIN SoftwareAllocationDimvw SAD ON SASA.AllocationSetContainsAllocation_SoftwareAllocationDimKey = SAD.SoftwareAllocationDimKey
AND SAD.IsDeleted = 0
AND (SAD.ObjectStatus IS NULL OR SAD.ObjectStatus <> @ObjectStatusPendingDelete)
WHERE SASA.DeletedDate IS NULL
GROUP BY SASA.SoftwareAllocationSetDimKey) X ON Y.SoftwareAllocationSetDimKey = X.SoftwareAllocationSetDimKey
WHERE (U.AssetAssigned IS NOT NULL OR U.HardwareAsset IS NULL)
update A
SET A.TotalAssigned = B.Assigned,
A.TotalInstalled = B.Installed
FROM #temp A
INNER JOIN (SELECT UserGroup.SoftwareTitleId, SUM(UserGroup.Installed) AS Installed,
SUM(UserGroup.AssetAssigned) + SUM(UserGroup.UserAssigned) AS Assigned
FROM (
SELECT A.SoftwareTitleId, SUM(IsNull(A.VersionInstalledCount,0)) AS Installed,
SUM(IsNull(A.AssetAssigned,0)) AS AssetAssigned, A.UserName, Max(IsNull(A.UserAssigned,0)) AS UserAssigned
FROM #temp A
GROUP BY A.SoftwareTitleId, A.UserName ) As UserGroup
GROUP BY UserGroup.SoftwareTitleId
) B ON A.SoftwareTitleId = B.SoftwareTitleId
UPDATE A
SET A.MaxPercentage = B.MaxPercentage,
A.MinPercentage = B.MinPercentage
FROM #temp A
INNER JOIN (
SELECT MAX(Percentage) AS MaxPercentage, MIN(Percentage) AS MinPercentage, SoftwareTitleId
FROM (
SELECT CASE
WHEN Installed <> 0 AND Assigned <> 0 THEN
(Installed*100)/(Assigned)
WHEN Assigned = 0 AND Installed <> 0 THEN
100
WHEN Installed = 0 AND Assigned <> 0 THEN
0
ELSE
0
END AS Percentage,
SoftwareTitleId, UserName
FROM (
SELECT A.SoftwareTitleId, SUM(IsNull(A.VersionInstalledCount,0)) AS Installed,
SUM(IsNull(A.AssetAssigned,0)) + Max(IsNull(A.UserAssigned,0)) AS Assigned, A.UserName
FROM #temp A
GROUP BY A.SoftwareTitleId, A.UserName ) AS UserGroup
) AS UserPercentages
Group BY UserPercentages.SoftwareTitleId ) B ON A.SoftwareTitleId = B.SoftwareTitleId
select * from #temp
WHERE (@SoftwareId IS NULL OR @SoftwareId < 1 OR #temp.SoftwareTitleId = @SoftwareId)
AND (@HardwareId IS NULL OR @HardwareId < 1 OR #temp.HardwareAssetId = @HardwareId)
AND (@UserId IS NULL OR @UserId < 1 OR #temp.UserId = @UserId)
AND (@PublisherId IS NULL OR @PublisherId < 1 OR #temp.PublisherId = @PublisherId)
order by SoftwareTitle, UserName, HardwareAsset
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
Dim ExceededTitles As System.Collections.ArrayList
Dim CloseTitles As System.Collections.ArrayList
Dim GoodTitles As System.Collections.ArrayList
Public Function CalculateRollupAllocationStatus(MinPercentage As Integer, MaxPercentage As Integer, ExceededThreshold As Integer, CloseThreshold As Integer, UnderThreshold As Integer) As String
Dim StatusName As String = "Blank.png"
If MaxPercentage >= ExceededThreshold Then
StatusName = "Exceeded.png"
Else If MaxPercentage >= CloseThreshold OR MinPercentage <= UnderThreshold OR MinPercentage = 0
StatusName = "Close.png"
Else
StatusName = "Good.png"
End If
Return StatusName
End Function
Public Function CalculateAssignmentStatus(Assigned As Integer, Installed As Integer, ExceededThreshold As Integer, CloseThreshold As Integer, UnderThreshold As Integer) As String
Dim StatusName As String = "Blank.png"
Dim InstalledPercentage AS Integer
If Not(Installed = 0) AND Not(Assigned = 0) Then
InstalledPercentage = Round(Installed * 100 / Assigned )
Else If Assigned = 0 AND Not(Installed = 0) Then
InstalledPercentage = 100
Else
InstalledPercentage = 0
End If
If InstalledPercentage >= ExceededThreshold Then
StatusName = "Exceeded.png"
Else If InstalledPercentage >= CloseThreshold OR InstalledPercentage <= UnderThreshold OR (Assigned = 0 AND Installed = 0)
StatusName = "Close.png"
Else
StatusName = "Good.png"
End If
Return StatusName
End Function
Public Function IsNewExceededTitle(Title As String) As Boolean
Dim result As Boolean = true
If ExceededTitles Is Nothing Then
ExceededTitles = New System.Collections.ArrayList()
End If
If ExceededTitles.Contains(Title) Then
result = false
Else
ExceededTitles.Add(Title)
End If
return result
End Function
Public Function IsNewCloseTitle(Title As String) As Boolean
Dim result As Boolean = true
If CloseTitles Is Nothing Then
CloseTitles = New System.Collections.ArrayList()
End If
If CloseTitles.Contains(Title) Then
result = false
Else
CloseTitles.Add(Title)
End If
return result
End Function
Public Function IsNewGoodTitle(Title As String) As Boolean
Dim result As Boolean = true
If GoodTitles Is Nothing Then
GoodTitles = New System.Collections.ArrayList()
End If
If GoodTitles.Contains(Title) Then
result = false
Else
GoodTitles.Add(Title)
End If
return result
End Function
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