HomeHardware Independent Imaging SolutionAnalyzingEnabling or Changing Custom Name Parsing (for model names)

2.1. Enabling or Changing Custom Name Parsing (for model names)

The attached script will enable the disabled by default custom name parsing rule to remove 'service tag' items from the end of the model names in the Compatibility classes, and compress the existing compatibility classes to a single entry for the filter settings, and copy all previous associations to any of the classes to the 'target' class.

It also converts model associations to the custom name parsed entries.

For additional customization, change the Custom name parsing function at the top as needed.

use eXpress
GO
ALTER FUNCTION [dbo].[HIIS_CustomNameParsing]
(
	@fullprodname varchar(256)
)
RETURNS varchar(256)
AS
BEGIN
declare @retval as varchar(256)
set @retval = @fullprodname

select @retval = ltrim(rtrim(left(@fullprodname,
	case 
	when
	charindex('(',@fullprodname) = 0 then len(@fullprodname)
	else charindex('(',@fullprodname) -1 END
	)))

return @retval
END

GO
insert into HIIS_Association_Strings
(stringval,profid)
(
select stringval, profid from
(
select dbo.HIIS_CustomNameParsing(Model) as stringval, hrc.ProfID, ROW_NUMBER() over (partition by dbo.HIIS_CustomNameParsing(hrc.Model), hrc.profid order by ReferenceClassID) rn from HIIS_ReferenceClass hrc 
where Model <> dbo.HIIS_CustomNameParsing(Model)
) a where rn = 1
and not exists (
select * from HIIS_Association_Strings has where has.profid = a.ProfID and lower(has.stringval) = lower(a.stringval)
)
)

update hfa set AssociationId = mapping.target from
HIIS_Folders_Associations hfa join
(
select 
  hass.id as sourceid, hast.id as target
from 
	HIIS_ReferenceClass hrc 
	join HIIS_Association_Strings hass 
		on hrc.ProfID = hass.profid and 
		lower(hrc.Model) = lower(hass.stringval)
	join HIIS_Association_Strings hast
		on hrc.ProfID = hast.profid and
		lower(dbo.HIIS_CustomNameParsing(hrc.Model)) = lower(hast.stringval)
where hrc.Model <> dbo.HIIS_CustomNameParsing(hrc.Model)
) mapping on mapping.sourceid = hfa.AssociationId
where hfa.Associationtype = 500

update hfa set AssociationId = mapping.target from
HIIS_Dropins_Associations hfa join
(
select 
  hass.id as sourceid, hast.id as target
from 
	HIIS_ReferenceClass hrc 
	join HIIS_Association_Strings hass 
		on hrc.ProfID = hass.profid and 
		lower(hrc.Model) = lower(hass.stringval)
	join HIIS_Association_Strings hast
		on hrc.ProfID = hast.profid and
		lower(dbo.HIIS_CustomNameParsing(hrc.Model)) = lower(hast.stringval)
where hrc.Model <> dbo.HIIS_CustomNameParsing(hrc.Model)
) mapping on mapping.sourceid = hfa.AssociationId
where hfa.Associationtype = 500

update hfa set AssociationId = mapping.target from
HIIS_App_Associations hfa join
(
select 
  hass.id as sourceid, hast.id as target
from 
	HIIS_ReferenceClass hrc 
	join HIIS_Association_Strings hass 
		on hrc.ProfID = hass.profid and 
		lower(hrc.Model) = lower(hass.stringval)
	join HIIS_Association_Strings hast
		on hrc.ProfID = hast.profid and
		lower(dbo.HIIS_CustomNameParsing(hrc.Model)) = lower(hast.stringval)
where hrc.Model <> dbo.HIIS_CustomNameParsing(hrc.Model)
) mapping on mapping.sourceid = hfa.AssociationId
where hfa.Associationtype = 500
GO
CREATE TABLE #cte
(
[source] int,
[target] int
) 
insert into #cte
(source,target)
(
select distinct a.ID as source ,b.ID as target from 
(select hrc.NumbeofProcessors, model, dbo.HIIS_CustomNameParsing(model) parsedName, deviceLists.devices,hrc.ReferenceClassID,hrc.ProfID, ID from HIIS_ReferenceClass hrc join 
(select rc.ReferenceClassID,rc.ProfID, (select name + '|'+ manuf + '|'+ description +';' from dbo.HIIS_Get_filteredDefListDN(rc.ReferenceClassID, rc.ProfID) order by name,manuf,description for xml path('')) devices from HIIS_ReferenceClass rc) deviceLists
on deviceLists.ReferenceClassID = hrc.ReferenceClassID  and deviceLists.ProfID = hrc.ProfID
where dbo.HIIS_CustomNameParsing(ReferenceClassName) <> ReferenceClassName
) a
join 
(select hrc.NumbeofProcessors, model, deviceLists.devices,hrc.ReferenceClassID,hrc.ProfID, ID from HIIS_ReferenceClass hrc join 
(select rc.ReferenceClassID,rc.ProfID, (select name + '|'+ manuf + '|'+ description +';' from dbo.HIIS_Get_filteredDefListDN(rc.ReferenceClassID, rc.ProfID) order by name,manuf,description for xml path('')) devices from HIIS_ReferenceClass rc) deviceLists
on deviceLists.ReferenceClassID = hrc.ReferenceClassID  and deviceLists.ProfID = hrc.ProfID
) b on a.parsedName = b.Model and a.devices = b.devices and a.ProfID = b.ProfID and a.NumbeofProcessors = b.NumbeofProcessors and a.ID <> b.ID
)
--change source associations to target associations for collected drivers
Update hfa
set AssociationId = target
from
HIIS_Folders_Associations hfa join #cte on hfa.AssociationId = #cte.source and (hfa.AssociationId = 50 or hfa.AssociationId = 51 or hfa.AssociationId = 60 or hfa.AssociationId = 61)

--change source to target app associations
Update hfa
set AssociationId = target
from
HIIS_App_Associations hfa join #cte on hfa.AssociationId = #cte.source and hfa.AssociationId = 1


--change source to target dropin associations
Update hfa
set AssociationId = target
from
HIIS_Dropins_Associations hfa join #cte on hfa.AssociationId = #cte.source and hfa.AssociationId = 1


delete HIIS_ReferenceClass from HIIS_ReferenceClass join #cte on #cte.source = ID

Create table #cte2
(
sourceid int,
targetid int
)
insert into #cte2
(sourceid,targetid)
(
select distinct a.ID as sourceid, b.ID as targetid from
(
select hrc.NumbeofProcessors, model, dbo.HIIS_CustomNameParsing(model) parsedName, deviceLists.devices,hrc.ReferenceClassID,hrc.ProfID, ID from HIIS_ReferenceClass hrc join 
(select rc.ReferenceClassID,rc.ProfID, (select name + '|'+ manuf + '|'+ description +';' from dbo.HIIS_Get_filteredDefListDN(rc.ReferenceClassID, rc.ProfID) order by name,manuf,description for xml path('')) devices from HIIS_ReferenceClass rc) deviceLists
on deviceLists.ReferenceClassID = hrc.ReferenceClassID  and deviceLists.ProfID = hrc.ProfID
where dbo.HIIS_CustomNameParsing(ReferenceClassName) <> ReferenceClassName
) a join 
(
select * from 
(select *, ROW_NUMBER() over (partition by parsedName,devices,ProfID,NumbeofProcessors order by ID) rn from 
(select hrc.NumbeofProcessors, model, dbo.HIIS_CustomNameParsing(model) parsedName, deviceLists.devices,hrc.ReferenceClassID,hrc.ProfID, ID from HIIS_ReferenceClass hrc join 
(select rc.ReferenceClassID,rc.ProfID, (select name + '|'+ manuf + '|'+ description +';' from dbo.HIIS_Get_filteredDefListDN(rc.ReferenceClassID, rc.ProfID) order by name,manuf,description for xml path('')) devices from HIIS_ReferenceClass rc) deviceLists
on deviceLists.ReferenceClassID = hrc.ReferenceClassID  and deviceLists.ProfID = hrc.ProfID
where dbo.HIIS_CustomNameParsing(ReferenceClassName) <> ReferenceClassName) x) y where rn = 1
) b on a.parsedName = b.parsedName and a.devices = b.devices and a.ProfID = b.ProfID and a.NumbeofProcessors = b.NumbeofProcessors and a.ID <> b.ID
)

update hfa 
set AssociationId = targetid
from
HIIS_Folders_Associations hfa join #cte2 on hfa.AssociationId = #cte2.sourceid and (hfa.Associationtype = 50 or hfa.Associationtype = 51 or hfa.Associationtype = 60 or hfa.Associationtype = 61)
GO


update hfa 
set AssociationId = targetid
from
HIIS_App_Associations hfa join #cte2 on hfa.AssociationId = #cte2.sourceid and (hfa.Associationtype = 1)
GO


update hfa 
set AssociationId = targetid
from
HIIS_Dropins_Associations hfa join #cte2 on hfa.AssociationId = #cte2.sourceid and (hfa.Associationtype = 1)

delete HIIS_ReferenceClass from HIIS_ReferenceClass join #cte2 on #cte2.sourceid = ID


delete HIIS_Folders_Associations from HIIS_Folders_Associations hfa join
(
select * from
(
select *, ROW_NUMBER() over (partition by FolderId,AssociationId,Associationtype order by id) rn from HIIS_Folders_Associations 
) a  where a.rn >1
) b on b.Id = hfa.Id

delete HIIS_App_Associations from HIIS_App_Associations hfa join
(
select * from
(
select *, ROW_NUMBER() over (partition by AppId,AssociationId,Associationtype order by id) rn from HIIS_App_Associations 
) a  where a.rn >1
) b on b.Id = hfa.Id

delete HIIS_Dropins_Associations from HIIS_Dropins_Associations hfa join
(
select * from
(
select *, ROW_NUMBER() over (partition by dropinid,AssociationId,Associationtype order by id) rn from HIIS_Dropins_Associations 
) a  where a.rn >1
) b on b.Id = hfa.Id
delete HIIS_Folders_Associations from HIIS_Folders_Associations hfa join
(
select *, ROW_NUMBER() over(Partition by FolderId,AssociationId order by Associationtype) rn from HIIS_Folders_Associations where Associationtype = 50 or Associationtype = 51 or Associationtype = 60 or Associationtype = 61

) a on a.Id = hfa.Id
 where rn > 1
Go


Update HIIS_ReferenceClass
set 
ReferenceClassName = LEFT(ReferenceClassName,LEN(ReferenceClassName) - Len(Model)) + dbo.HIIS_CustomNameParsing(Model),
Model = dbo.HIIS_CustomNameParsing(Model)


delete [HIIS_ReferenceClassGrp]
FROM [HIIS_ReferenceClassGrp] hrcg left outer join HIIS_ReferenceClass hrc on hrc.ProfID = hrcg.ProfID and hrc.ReferenceClassID = hrcg.ReferenceClassUID
where hrc.ReferenceClassID is null

delete [HIIS_ReferenceClassDef]
FROM [HIIS_ReferenceClassDef] hrcd left outer join HIIS_ReferenceClass hrc on hrc.ProfID = hrcd.ProfID and hrc.ReferenceClassID = hrcd.RefID
where hrc.ReferenceClassID is null

GO
drop table #cte
drop table #cte2

Downloads

This page was: Helpful | Not Helpful