select @ComputerAssetLifeCycleDisposedId = ProvanceComputerAssetLifeCycleId from ProvanceComputerAssetLifeCycle where ID = 'Provance.Enumeration.ComputerAssetLifeCycle.Disposed'
select @ComputerAssetLifeCycleReturnedId = ProvanceComputerAssetLifeCycleId from ProvanceComputerAssetLifeCycle where ID = 'Provance.Enumeration.ComputerAssetLifeCycle.Returned'
select @GeneralAssetLifeCycleDisposedId = ProvanceGeneralAssetLifeCycleId from ProvanceGeneralAssetLifeCycle where ID = 'Provance.Enumeration.GeneralAssetLifeCycle.Disposed'
select @GeneralAssetLifeCycleReturnedId = ProvanceGeneralAssetLifeCycleId from ProvanceGeneralAssetLifeCycle where ID = 'Provance.Enumeration.GeneralAssetLifeCycle.Returned'
select @ProvanceHardwareAssetOwnershipTypeOwnedId = ProvanceHardwareAssetOwnershipTypeId from ProvanceHardwareAssetOwnershipTypevw where ID = 'Provance.Enumeration.HardwareAssetOwnershipType.Owned'
select @ProvanceHardwareAssetOwnershipTypeLeasedId = ProvanceHardwareAssetOwnershipTypeId from ProvanceHardwareAssetOwnershipTypevw where ID = 'Provance.Enumeration.HardwareAssetOwnershipType.Leased'
-- Set @AssetTag to null if spaces or empty string
SELECT @sAssetTag = nullif(ltrim(rtrim(@AssetTag)),'')
-- make sure @IncludeDisposed is not null
SELECT @bIncludeDisposed = isnull(@IncludeDisposed,0)
-- make sure @ExcludeLeased is not null
SELECT @bExcludeLeased = isnull(@ExcludeLeased,0)
-- make sure @ExcludeOwned is not null
SELECT @bExcludeOwned = isnull(@ExcludeOwned,0)
-- adjust @statusId to null value if not a specific id selected
SELECT @iStatusId = nullif(ltrim(rtrim(@StatusId)),-1)
-- TODO Need to check why @ReturnedId would be included. Sunday May 18, 2014 3pm
-- IF @StatusId IN (@DisposedId, @ReturnedId)
IF @StatusId IN (@ComputerAssetLifeCycleDisposedId, @ComputerAssetLifeCycleReturnedId,@GeneralAssetLifeCycleDisposedId,@GeneralAssetLifeCycleReturnedId)
BEGIN
SELECT @bIncludeDisposed = 1
END
-- adjust @TypeId to null if no specific type selected
SELECT @iTypeId = nullif(ltrim(rtrim(@TypeId)),-1)
-- set start/end date for Age of Asset date range
IF @AgeRange = @ageOptGreaterFive
BEGIN
SELECT @EndDate = DATEADD(YY,-5,GETDATE())
END
ELSE IF @AgeRange = @ageOptBetweenTwoFive
BEGIN
SELECT @StartDate = DATEADD(YY,-5, GETDATE()),
@EndDate = DATEADD(YY,-2,GETDATE())
END
ELSE IF @AgeRange = @ageOptLessTwo
BEGIN
SELECT @StartDate = DATEADD(YY,-2, GETDATE())
END
ELSE IF @AgeRange = @ageOptUnknown
BEGIN
SELECT @StartDate = DATEADD(DD,1,GETDATE()),
@EndDate = DATEADD(DD,1,GETDATE())
END
-- get list of organizations under passed parent organization
IF @OrganizationId IS NOT NULL
BEGIN
--prime the orgIDs table
INSERT INTO @orgIDs
VALUES(@OrganizationId)
SELECT @rowcount = @@ROWCOUNT
WHILE @rowcount > 0
BEGIN
INSERT INTO @orgIDs
SELECT A.ProvanceOrganizationDimKey
FROM ProvanceOrganizationIsContainedByOrganizationFactvw A
WHERE A.DeletedDate IS NULL
AND A.ProvanceOrganizationDimKey not IN (SELECT orgID FROM @orgIDs)
AND A.OrganizationIsContainedByOrganization_ProvanceOrganizationDimKey IN (SELECT orgID FROM @orgIDs)
SELECT @rowcount = @@ROWCOUNT
END
END
-- get list of cost centers under passed parent cost center
IF @CostCenterId IS NOT NULL
BEGIN
--prime the @costcenterIDs table
INSERT INTO @costCenterIDs
VALUES(@CostCenterId)
SELECT @rowcount = @@ROWCOUNT
WHILE @rowcount > 0
BEGIN
INSERT INTO @costCenterIDs
SELECT A.ProvanceCostCenterDimKey
FROM ProvanceCostCenterIsContainedByCostCenterFactvw A
WHERE A.DeletedDate IS NULL
AND A.ProvanceCostCenterDimKey not IN (SELECT ccId FROM @costCenterIDs)
AND A.CostCenterIsContainedByCostCenter_ProvanceCostCenterDimKey IN (SELECT ccId FROM @costCenterIDs)
SELECT @rowcount = @@ROWCOUNT
END
END
-- get list of locations under passed parent location
IF @LocationId IS NOT NULL
BEGIN
--prime the @locIDs table
INSERT INTO @locIDs
VALUES(@LocationId)
SELECT @rowcount = @@ROWCOUNT
WHILE @rowcount > 0
BEGIN
INSERT INTO @locIDs
SELECT A.ProvanceLocationDimKey
FROM ProvanceLocationIsContainedByLocationFactvw A
WHERE A.DeletedDate IS NULL
AND A.ProvanceLocationDimKey not IN (SELECT locID FROM @locIDs)
AND A.LocationIsContainedByLocation_ProvanceLocationDimKey IN (SELECT locID FROM @locIDs)
SELECT @rowcount = @@ROWCOUNT
END
END
-- retrieve data
SELECT isnull(Z.SerialNumber,'(' + cast(z.EntityDimKey as varchar(255))+ ')') SerialNumber, z.ProvanceHardwareAssetStandardDimKey, A.AssetTag, z.DisplayName, A.Lifecycle HardwareAssetLifecycleStatusValue,
D.DisplayName AS Location, F.DisplayName AS CostCenter, H.DisplayName AS Organization,
J.DisplayName AS Supplier, N.DisplayName AS Manufacturer, z.ReceivedDate, P.HardwareAssetTypeValue AS HardwareType
FROM (select a.AssetTag,a.EntityDimKey,a.ReceivedDate ,ISNULL(c.DisplayName, b.ProvanceComputerAssetLifeCycleValue) AS Lifecycle,a.BaseManagedEntityId
from ProvanceComputerAssetDimvw a
left join ProvanceComputerAssetLifeCycle b on A.LifeCycle_ProvanceComputerAssetLifeCycleId = B.ProvanceComputerAssetLifeCycleId
left join DisplayStringDim c ON b.EnumTypeId = c.BaseManagedEntityId and c.LanguageCode = @UserLanguage
WHERE (A.LifeCycle_ProvanceComputerAssetLifeCycleId NOT IN (@ComputerAssetLifeCycleDisposedId,@ComputerAssetLifeCycleReturnedId) OR @IncludeDisposed = 1)
AND (A.LifeCycle_ProvanceComputerAssetLifeCycleId = @iStatusId OR @iStatusId IS NULL)
union all
select a.AssetId,a.EntityDimKey,a.ReceivedDate,ISNULL(c.DisplayName, b.ProvanceGeneralAssetLifeCycleValue) AS Lifecycle,a.BaseManagedEntityId
from ProvanceGeneralAssetDim a
left join ProvanceGeneralAssetLifeCycle b on A.LifeCycle_ProvanceGeneralAssetLifeCycleId = B.ProvanceGeneralAssetLifeCycleId
left join DisplayStringDim c ON b.EnumTypeId = c.BaseManagedEntityId and c.LanguageCode = @UserLanguage
WHERE (A.LifeCycle_ProvanceGeneralAssetLifeCycleId NOT IN (@GeneralAssetLifeCycleDisposedId,@GeneralAssetLifeCycleReturnedId) OR @IncludeDisposed = 1)
AND (A.LifeCycle_ProvanceGeneralAssetLifeCycleId = @iStatusId OR @iStatusId IS NULL)) A
INNER JOIN ProvanceHardwareAssetStandardDimvw z on A.BaseManagedEntityId = Z.BaseManagedEntityId
LEFT JOIN ProvanceHardwareAssetStandardHasLocationFactvw C ON Z.ProvanceHardwareAssetStandardDimKey = C.ProvanceHardwareAssetStandardDimKey AND C.DeletedDate IS NULL
LEFT JOIN ProvanceLocationDimvw D ON c.HardwareAssetStandardHasLocation_ProvanceLocationDimKey = D.ProvanceLocationDimKey AND D.IsDeleted = 0 AND ISNULL(D.ObjectStatus,'') <> @ObjectStatusPendingDelete
LEFT JOIN ProvanceHardwareAssetStandardHasCostCenterFactvw E ON Z.ProvanceHardwareAssetStandardDimKey = E.ProvanceHardwareAssetStandardDimKey AND E.DeletedDate IS NULL
LEFT JOIN ProvanceCostCenterDimvw F ON e.HardwareAssetStandardHasCostCenter_ProvanceCostCenterDimKey = F.ProvanceCostCenterDimKey AND F.IsDeleted = 0 AND ISNULL(F.ObjectStatus,'') <> @ObjectStatusPendingDelete
LEFT JOIN ProvanceHardwareAssetStandardHasOrganizationFactvw G ON Z.ProvanceHardwareAssetStandardDimKey = G.ProvanceHardwareAssetStandardDimKey AND G.DeletedDate IS NULL
LEFT JOIN ProvanceOrganizationDimvw H ON G.HardwareAssetStandardHasOrganization_ProvanceOrganizationDimKey = H.ProvanceOrganizationDimKey AND H.IsDeleted = 0 AND ISNULL(H.ObjectStatus,'') <> @ObjectStatusPendingDelete
LEFT JOIN ProvanceHardwareAssetStandardHasSupplierFactvw I ON z.ProvanceHardwareAssetStandardDimKey = I.ProvanceHardwareAssetStandardDimKey AND I.DeletedDate IS NULL
LEFT JOIN ProvanceCompanyDimvw J ON I.HardwareAssetStandardHasSupplier_ProvanceCompanyDimKey = J.ProvanceCompanyDimKey AND J.IsDeleted = 0 AND ISNULL(J.ObjectStatus,'') <> @ObjectStatusPendingDelete
LEFT JOIN ProvanceHardwareAssetStandardIsHardwareCatalogSerializedFactvw K ON Z.ProvanceHardwareAssetStandardDimKey = K.ProvanceHardwareAssetStandardDimKey AND K.DeletedDate IS NULL
LEFT JOIN ProvanceHardwareCatalogSerializedDimvw L ON K.HardwareAssetStandardIsHardwareCatalogSerialized_ProvanceHardwareCatalogSerializedDimKey = L.ProvanceHardwareCatalogSerializedDimKey AND L.IsDeleted = 0 AND ISNULL(L.ObjectStatus,'') <> @ObjectStatusPendingDelete
LEFT JOIN ProvanceHardwareCatalogSerializedHasManufacturerFactVW M ON L.ProvanceHardwareCatalogSerializedDimKey = M.ProvanceHardwareCatalogSerializedDimKey AND M.DeletedDate IS NULL
LEFT JOIN ProvanceCompanyDimvw N ON M.HardwareCatalogSerializedHasManufacturer_ProvanceCompanyDimKey = N.ProvanceCompanyDimKey AND N.IsDeleted = 0 AND ISNULL(N.ObjectStatus,'') <> @ObjectStatusPendingDelete
LEFT JOIN (sELECT A.ProvanceHardwareAssetTypeId, A.EnumTypeId,
ISNULL(B.DisplayName, A.ProvanceHardwareAssetTypeValue) AS HardwareAssetTypeValue
FROM ProvanceHardwareAssetTypevw A
LEFT OUTER JOIN DisplayStringDim B ON A.EnumTypeId = B.BaseManagedEntityId
AND B.LanguageCode = @UserLanguage
) P ON z.Type_ProvanceHardwareAssetTypeId = P.ProvanceHardwareAssetTypeId
LEFT JOIN (sELECT A.ProvanceHardwareAssetOwnershipTypeId, A.EnumTypeId,
ISNULL(B.DisplayName, A.ProvanceHardwareAssetOwnershipTypeValue) AS HardwareAssetTypeValue
FROM ProvanceHardwareAssetOwnershipTypevw A
LEFT OUTER JOIN DisplayStringDim B ON A.EnumTypeId = B.BaseManagedEntityId
AND B.LanguageCode = @UserLanguage
) Q ON z.OwnershipType_ProvanceHardwareAssetOwnershipTypeId = Q.ProvanceHardwareAssetOwnershipTypeId
-- select * from ProvanceHardwareAssetOwnershipTypevw
WHERE z.IsDeleted = 0
AND ISNULL(z.ObjectStatus,'') <> @ObjectStatusPendingDelete
-- include/exclude owned hardware assets
AND (Q.ProvanceHardwareAssetOwnershipTypeId = @ProvanceHardwareAssetOwnershipTypeOwnedId OR @bExcludeOwned = 0)
-- include/exclude leased hardware assets
AND (q.ProvanceHardwareAssetOwnershipTypeId = @ProvanceHardwareAssetOwnershipTypeLeasedId OR @bExcludeLeased = 0)
-- filter by age of asset
AND (A.ReceivedDate BETWEEN @StartDate AND @EndDate OR
(@StartDate IS NULL AND A.ReceivedDate < @EndDate) OR
(@EndDate IS NULL AND A.ReceivedDate > @StartDate) OR
(@StartDate IS NULL AND @EndDate IS NULL) OR
(@AgeRange = @ageOptUnknown AND A.ReceivedDate IS NULL))
-- filter by hardware asset type
AND (z.Type_ProvanceHardwareAssetTypeId = @iTypeId OR @iTypeId IS NULL)
-- filter by manufacturer id
AND (N.ProvanceCompanyDimKey = @ManufacturerId OR @ManufacturerId IS NULL OR @ManufacturerId < 1)
-- filter by supplier id
AND (J.ProvanceCompanyDimKey = @SupplierId OR @SupplierId IS NULL OR @SupplierId < 1)
-- filter by top level organization id
AND (H.ProvanceOrganizationDimKey IN (SELECT orgID FROM @orgIDs) OR (@OrganizationId = -1 AND H.ProvanceOrganizationDimKey IS NULL) OR @OrganizationId IS NULL OR @OrganizationId =0)
-- filter by top level cost center id
AND (F.ProvanceCostCenterDimKey IN (SELECT ccID FROM @costCenterIDs) OR (@CostCenterId = -1 AND F.ProvanceCostCenterDimKey IS NULL) OR @CostCenterId IS NULL OR @CostCenterId =0)
-- filter by top level location id
AND (D.ProvanceLocationDimKey IN (SELECT locId FROM @locIDs) OR (@LocationId = -1 AND D.ProvanceLocationDimKey IS NULL) OR @LocationId IS NULL OR @LocationId = 0)
AND (@sAssetTag is null or A.AssetTag like @sAssetTag)</CommandText>
</Query>
<Fields>
<Field Name="SerialNumber">
<DataField>SerialNumber</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="HardwareAssetDimKey">
<DataField>HardwareAssetDimKey</DataField>
<rd:UserDefined>true</rd:UserDefined>
</Field>
<Field Name="ProvanceHardwareAssetStandardDimKey">
<DataField>ProvanceHardwareAssetStandardDimKey</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="AssetTag">
<DataField>AssetTag</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="DisplayName">
<DataField>DisplayName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="HardwareAssetLifecycleStatusValue">
<DataField>HardwareAssetLifecycleStatusValue</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Location">
<DataField>Location</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="CostCenter">
<DataField>CostCenter</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Organization">
<DataField>Organization</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Supplier">
<DataField>Supplier</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Manufacturer">
<DataField>Manufacturer</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="ReceivedDate">
<DataField>ReceivedDate</DataField>
<rd:TypeName>System.DateTime</rd:TypeName>
</Field>
<Field Name="HardwareType">
<DataField>HardwareType</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
</Fields>
</DataSet>
<DataSet Name="LT_Report">
<Query>
<DataSourceName>DWDataMart</DataSourceName>
<QueryParameters>
<QueryParameter Name="@ManagementPackSystemName">
<Value>Provance.Asset.Reports</Value>
</QueryParameter>
<QueryParameter Name="@ReportSystemName">
<Value>Provance.Report.SerializedHardwareAssets</Value>
</QueryParameter>
<QueryParameter Name="@LanguageCode">
<Value>=Code.GetReportLocLanguageCode()</Value>
</QueryParameter>
<QueryParameter Name="@Scope">
<Value>%</Value>
</QueryParameter>
</QueryParameters>
<CommandType>StoredProcedure</CommandType>
<CommandText>ReportDisplayStringGet</CommandText>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
</Query>
<Fields>
<Field Name="XML_F52E2B61_18A1_11d1_B105_00805F49916B">
<DataField>XML_F52E2B61-18A1-11d1-B105-00805F49916B</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
</Fields>
</DataSet>
<DataSet Name="LifecycleStatuses">
<Query>
<DataSourceName>DWDataMart</DataSourceName>
<QueryParameters>
<QueryParameter Name="@LanguageCode">
<Value>=Code.GetReportLocLanguageCode()</Value>
</QueryParameter>
<QueryParameter Name="@ReportSystemName">
<Value>Provance.Report.SerialzedHardwareAssets</Value>
</QueryParameter>
<QueryParameter Name="@SubElementName">
<Value>AllParameter</Value>
</QueryParameter>
<QueryParameter Name="@ManagementPackSystemName">
<Value>Provance.Asset.Reports</Value>
</QueryParameter>
<QueryParameter Name="@Scope">
<Value>%</Value>
</QueryParameter>
</QueryParameters>
<CommandText>--Query LifecycleStatuses SerializedHardwareAssets
CREATE TABLE #temp
(
HardwareAssetLifecycleStatusId int,
DisplayName varchar(256)
)
INSERT INTO #temp
SELECT ProvanceComputerAssetLifeCycleId, DisplayName
FROM
(SELECT A.ProvanceComputerAssetLifeCycleId, ISNULL(B.DisplayName, A.ProvanceComputerAssetLifeCycleValue) AS DisplayName
FROM ProvanceComputerAssetLifeCycle A
LEFT OUTER JOIN DisplayStringDim B ON A.EnumTypeId = B.BaseManagedEntityId
AND B.LanguageCode = @LanguageCode
-- WHERE A.EnumTypeId NOT IN ( @LifecycleStatusEnumName)
UNION ALL
SELECT -1 ,
ISNULL(rds.DisplayName,rds.SubElementName) AS DisplayName
FROM ManagementPackDim mp
Left JOIN DisplayStringDim rds ON (rds.ManagementPackId = mp.ManagementPackId)
And (rds.LanguageCode = @LanguageCode)
And (rds.ElementName = @ReportSystemName)
And (rds.SubElementName = @SubElementName)
WHERE (mp.MPName = @ManagementPackSystemName)
AND ((@Scope IS NULL) OR (rds.SubElementName like @Scope))
) OptionSet
ORDER BY OptionSet.DisplayName
-- If -1 was not inserted as a HardwareAssetLifecycleStatusId, we look for the english version
DECLARE @AllParameter varchar(256)
SELECT @AllParameter = DisplayName
FROM #temp
WHERE HardwareAssetLifeCycleStatusId = -1
IF @AllParameter IS NULL
BEGIN
INSERT INTO #temp
SELECT -1,
ISNULL(rds.DisplayName,rds.SubElementName) AS DisplayName
FROM ManagementPackDim mp
Left JOIN DisplayStringDim rds ON (rds.ManagementPackId = mp.ManagementPackId)
And (rds.LanguageCode = 'ENU')
And (rds.ElementName = @ReportSystemName)
And (rds.SubElementName = @SubElementName)
WHERE (mp.MPName = @ManagementPackSystemName)
AND ((@Scope IS NULL) OR (rds.SubElementName like @Scope))
END
-- If there is still no -1 we add an enntry manually
insert into #temp select -1,'[All]' where not exists (select -1 from #temp where HardwareAssetLifecycleStatusId = -1)
SELECT @sLocationDisplayName = substring(A.DisplayName,1,4000)
FROM ProvanceLocationDimvw A
WHERE A.ProvanceLocationDimKey = @LocationId
AND A.IsDeleted = 0
SELECT @sCostCenterDisplayName = substring(A.DisplayName,1,4000)
FROM ProvanceCostCenterDimvw A
WHERE A.ProvanceCostCenterDimKey = @CostCenterId
AND A.IsDeleted = 0
SELECT @sManufacturerDisplayName = substring(A.DisplayName,1,4000)
FROM ProvanceCompanyDimvw A
WHERE A.ProvanceCompanyDimKey = @ManufacturerId
AND A.IsDeleted = 0
SELECT @sOrganizationDisplayName = substring(A.DisplayName,1,4000)
FROM ProvanceOrganizationDimvw A
WHERE A.ProvanceOrganizationDimKey = @CostCenterId
AND A.IsDeleted = 0
SELECT @sStatusDisplayName = A.ProvanceComputerAssetLifeCycleValue
FROM ProvanceComputerAssetLifeCycle A
WHERE A.ProvanceComputerAssetLifeCycleId = @StatusId
SELECT @sSupplierDisplayName = substring(A.DisplayName,1,4000)
FROM ProvanceCompanyDimvw A
WHERE A.ProvanceCompanyDimKey = @SupplierId
AND A.IsDeleted = 0
SELECT @sStatusDisplayName = substring(A.DisplayName,1,4000)
FROM ProvanceHardwareCatalogNonSerializedDimvw A
WHERE A.ProvanceHardwareCatalogNonSerializedDimKey = @StatusId
AND A.IsDeleted = 0
INSERT INTO #temp
SELECT ProvanceHardwareAssetTypeId, DisplayName
FROM
(SELECT A.ProvanceHardwareAssetTypeId, ISNULL(B.DisplayName, A.ProvanceHardwareAssetTypeValue) AS DisplayName
FROM ProvanceHardwareAssetTypevw A
LEFT OUTER JOIN DisplayStringDim B ON A.EnumTypeId = B.BaseManagedEntityId
AND B.LanguageCode = @LanguageCode
WHERE A.EnumTypeId NOT IN (@AssetTypeEnumName)
UNION ALL
SELECT -1 ,
ISNULL(rds.DisplayName,rds.SubElementName) AS DisplayName
FROM ManagementPackDim mp
Left JOIN DisplayStringDim rds ON (rds.ManagementPackId = mp.ManagementPackId)
And (rds.LanguageCode = @LanguageCode)
And (rds.ElementName = @ReportSystemName)
And (rds.SubElementName = @SubElementName)
WHERE (mp.MPName = @ManagementPackSystemName)
AND ((@Scope IS NULL) OR (rds.SubElementName like @Scope))
) OptionSet
ORDER BY OptionSet.DisplayName
DECLARE @AllParameter varchar(256)
SELECT @AllParameter = DisplayName
FROM #temp
WHERE HardwareAssetTypeId = -1
IF @AllParameter IS NULL
BEGIN
INSERT INTO #temp
SELECT -1 ,
ISNULL(rds.DisplayName,rds.SubElementName) AS DisplayName
FROM ManagementPackDim mp
Left JOIN DisplayStringDim rds ON (rds.ManagementPackId = mp.ManagementPackId)
And (rds.LanguageCode = 'ENU')
And (rds.ElementName = @ReportSystemName)
And (rds.SubElementName = @SubElementName)
WHERE (mp.MPName = @ManagementPackSystemName)
AND ((@Scope IS NULL) OR (rds.SubElementName like @Scope))
END
-- If there is still no -1 we add an enntry manually
insert into #temp select -1,'[All]' where not exists (select -1 from #temp where HardwareAssetTypeId = -1)
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
Return Result.ToArray()
End Function
</Script></Code>
<Language>en-US</Language>
<CodeModules>
<CodeModule>System.Xml, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089</CodeModule>
<CodeModule>Microsoft.EnterpriseManagement.Reporting.Code, Version=7.0.5000.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35</CodeModule>
</CodeModules>
<ConsumeContainerWhitespace>true</ConsumeContainerWhitespace>
<rd:ReportUnitType>Inch</rd:ReportUnitType>
<rd:ReportID>8cfda3d8-0575-4fa2-9565-b97fa1b1e18b</rd:ReportID>
</Report>
Home