1003 lines
40 KiB
Transact-SQL
1003 lines
40 KiB
Transact-SQL
|
|
/****** Object: StoredProcedure [getItemAndChildren] ******/
|
|
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getItemAndChildren]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
|
|
DROP PROCEDURE [getItemAndChildren];
|
|
GO
|
|
|
|
-- getItemAndChildren 111
|
|
CREATE PROCEDURE [dbo].[getItemAndChildren] (@ItemID int) WITH EXECUTE AS OWNER AS BEGIN
|
|
with Itemz([Level], [ParentID], [Ordinal], [ItemID], [PreviousID], [FromType], [ContentID], [DTS], [UserID], [LastChanged]) as (
|
|
Select 0 [Level], 0 [ParentID], 0 [Ordinal], [ItemID], [PreviousID],0 [FromType],[ContentID],[DTS],[UserID],[LastChanged]
|
|
FROM [Items]
|
|
where [ItemID]=@ItemID
|
|
Union All
|
|
-- Children
|
|
select [Level] + 1,Z.ItemID,0, I.[ItemID], I.[PreviousID], P.[FromType],I.[ContentID],I.[DTS],I.[UserID],I.[LastChanged]
|
|
from Itemz Z
|
|
join Parts P on P.ContentID = Z.ContentID
|
|
join Items I on I.ItemID = P.ItemID
|
|
-- Siblings
|
|
Union All
|
|
select [Level] ,Z.[ParentID],Z.[Ordinal] +1, I.[ItemID], I.[PreviousID], [FromType],I.[ContentID],I.[DTS],I.[UserID],I.[LastChanged]
|
|
from Itemz Z
|
|
join Items I on I.PreviousID = Z.ItemID
|
|
where Z.[Level] > 0
|
|
)
|
|
select I.[Level],I.[FromType],I.[Ordinal], I.[ParentID], I.[ItemID],I.[PreviousID],I.[ContentID],I.[DTS],I.[UserID],I.[LastChanged],
|
|
C.[Number],C.[Text],C.[Type],C.[FormatID],C.[Config],C.[DTS] As [cDTS],C.[UserID] As [cUserID],C.[LastChanged] As [cLastChanged] from ItemZ I
|
|
join Contents C on C.ContentID = I.ContentID
|
|
order by I.[Level] , I.[FromType], I.[Ordinal]
|
|
END
|
|
GO
|
|
-- Display the status of Proc creation
|
|
IF (@@Error = 0) PRINT 'Procedure Creation: getItemAndChildren Succeeded'
|
|
ELSE PRINT 'Procedure Creation: getItemAndChildren Error on Creation'
|
|
GO
|
|
|
|
|
|
/****** Object: StoredProcedure [getItemNextAndChildren] ******/
|
|
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getItemNextAndChildren]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
|
|
DROP PROCEDURE [getItemNextAndChildren];
|
|
GO
|
|
|
|
-- getItemNextAndChildren 111
|
|
CREATE PROCEDURE [dbo].[getItemNextAndChildren] (@ItemID int) WITH EXECUTE AS OWNER AS BEGIN
|
|
with Itemz([Level], [ParentID], [Ordinal], [ItemID], [PreviousID], [FromType], [ContentID], [DTS], [UserID], [LastChanged]) as (
|
|
Select 0 [Level], 0 [ParentID], 0 [Ordinal], [ItemID], [PreviousID],0 [FromType],[ContentID],[DTS],[UserID],[LastChanged]
|
|
FROM [Items]
|
|
where [ItemID]=@ItemID
|
|
Union All
|
|
-- Children
|
|
select [Level] + 1,Z.ItemID,0, I.[ItemID], I.[PreviousID], P.[FromType],I.[ContentID],I.[DTS],I.[UserID],I.[LastChanged]
|
|
from Itemz Z
|
|
join Parts P on P.ContentID = Z.ContentID
|
|
join Items I on I.ItemID = P.ItemID
|
|
-- Siblings
|
|
Union All
|
|
select [Level] ,Z.[ParentID],Z.[Ordinal] +1, I.[ItemID], I.[PreviousID], [FromType],I.[ContentID],I.[DTS],I.[UserID],I.[LastChanged]
|
|
from Itemz Z
|
|
join Items I on I.PreviousID = Z.ItemID
|
|
)
|
|
select I.[Level],I.[FromType],I.[Ordinal], I.[ParentID], I.[ItemID],I.[PreviousID],I.[ContentID],I.[DTS],I.[UserID],I.[LastChanged],
|
|
C.[Number],C.[Text],C.[Type],C.[FormatID],C.[Config],C.[DTS] As [cDTS],C.[UserID] As [cUserID],C.[LastChanged] As [cLastChanged] from ItemZ I
|
|
join Contents C on C.ContentID = I.ContentID
|
|
order by I.[Level] , I.[FromType], I.[Ordinal]
|
|
END
|
|
GO
|
|
-- Display the status of Proc creation
|
|
IF (@@Error = 0) PRINT 'Procedure Creation: getItemNextAndChildren Succeeded'
|
|
ELSE PRINT 'Procedure Creation: getItemNextAndChildren Error on Creation'
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [ve_GetFolderPath] ******/
|
|
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[ve_GetFolderPath]') AND OBJECTPROPERTY(id,N'IsScalarFunction') = 1)
|
|
DROP FUNCTION [ve_GetFolderPath];
|
|
GO
|
|
|
|
/*
|
|
|
|
Select VersionID,Name,dbo.ve_GetFolderPath(VersionID) from DocVersions order by VersionID
|
|
|
|
*/
|
|
CREATE FUNCTION [dbo].[ve_GetFolderPath] (@VersionID int) RETURNS varchar(MAX) WITH EXECUTE AS OWNER AS BEGIN
|
|
declare @Path varchar(MAX);
|
|
with Folderz(Level, FolderID, Path) as (
|
|
Select 1 Level, FolderID,CAST(Name as varchar(MAX))
|
|
from DocVersions I
|
|
where VersionID = @VersionID
|
|
Union All
|
|
--
|
|
Select FS.ParentID - (Select ParentID from Folders where FolderID = FS.ParentID), ParentID, CAST(Name + char(7) + Path as varchar(MAX))
|
|
from Folders FS
|
|
join Folderz FZ on FS.FolderID = FZ.FolderID
|
|
where FS.FolderID <> FS.ParentID
|
|
)
|
|
select @Path = path From Folderz where Level = 0
|
|
return @Path
|
|
END;
|
|
GO
|
|
-- Display the status of Proc creation
|
|
IF (@@Error = 0) PRINT 'ScalarFunction Creation: ve_GetFolderPath Succeeded'
|
|
ELSE PRINT 'ScalarFunction Creation: ve_GetFolderPath Error on Creation'
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [vefn_AllSections] ******/
|
|
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[vefn_AllSections]') AND OBJECTPROPERTY(id,N'IsScalarFunction') = 1)
|
|
DROP FUNCTION [vefn_AllSections];
|
|
GO
|
|
|
|
-- Select .dbo.vefn_AllSections(10001) MyTypeSection, .dbo.vlnfn_AllSections(20001) MyTypeStep, .dbo.vlnfn_AllSections(5) MyTypeProc
|
|
CREATE FUNCTION [dbo].[vefn_AllSections](@type int)
|
|
RETURNS int
|
|
AS
|
|
BEGIN
|
|
IF(@type < 20000 and @type >= 10000)
|
|
RETURN 10000
|
|
RETURN @type
|
|
END
|
|
GO
|
|
-- Display the status of Proc creation
|
|
IF (@@Error = 0) PRINT 'ScalarFunction Creation: vefn_AllSections Succeeded'
|
|
ELSE PRINT 'ScalarFunction Creation: vefn_AllSections Error on Creation'
|
|
GO
|
|
|
|
|
|
|
|
/****** Object: StoredProcedure [ve_GetPath2] ******/
|
|
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[ve_GetPath2]') AND OBJECTPROPERTY(id,N'IsScalarFunction') = 1)
|
|
DROP FUNCTION [ve_GetPath2];
|
|
GO
|
|
|
|
CREATE FUNCTION [dbo].[ve_GetPath2] (@ItemID int) RETURNS varchar(max) WITH EXECUTE AS OWNER AS BEGIN
|
|
-- declare @STructID int
|
|
--set @StructID=11
|
|
declare @Path varchar(max);
|
|
declare @Delim as varchar(1);
|
|
set @Delim = char(7);
|
|
with Itemz(ItemCount,ItemID,PreviousID,FromType,CType,Number,Text,PPath, Path) as (
|
|
Select 1,ItemID,PreviousID,0,C.Type/10000,C.Number,C.Text,
|
|
Cast(Case when C.Type < 20000 then @Delim + C.Number +@Delim + C.Text else '' end as nvarchar(max)) PPath,
|
|
Cast(Case when C.Type < 20000 then @Delim + C.Number +@Delim + C.Text else '1' end as nvarchar(max)) Path
|
|
from Items I
|
|
join Contents C on I.ContentID = C.ContentID
|
|
where ItemID=@ItemID
|
|
Union All
|
|
-- siblings
|
|
Select ItemCount+1,I.PreviousID,II.PreviousID,0,C.Type/10000,Z.Number,Z.Text,PPath,cast(ItemCount + 1 as nvarchar(3)) + PPath
|
|
from Items I
|
|
join Contents C on I.ContentID = C.ContentID
|
|
Join Items II on II.ItemID = I.PreviousID
|
|
Join Itemz Z on I.ItemID=Z.ItemID
|
|
where I.PreviousID != 0
|
|
Union All
|
|
-- children
|
|
select 1,I.ItemID,I.PreviousID,P.FromType,C.Type/10000,C.Number,C.Text,
|
|
case C.Type/10000
|
|
when 0 then @Delim + C.Number +@Delim + C.Text
|
|
when 1 then @Delim + C.Number +@Delim + C.Text +@Delim + cast(ItemCount + 0 as varchar(3))
|
|
else
|
|
case P.FromType
|
|
when 1 then 'PRC' + @Delim + cast(ItemCount + 0 as varchar(3))
|
|
when 2 then @Delim + 'SEC' + @Delim + cast(ItemCount + 0 as varchar(3))
|
|
when 3 then @Delim + 'Caution' + @Delim + cast(ItemCount + 0 as varchar(3))
|
|
when 4 then @Delim + 'Note' + @Delim + cast(ItemCount + 0 as varchar(3))
|
|
when 5 then @Delim + 'RNO' + @Delim
|
|
when 7 then @Delim + 'Table' + @Delim
|
|
else @Delim + cast(ItemCount + 0 as varchar(3))
|
|
end end + PPath PPath,
|
|
--'1' +
|
|
case C.Type/10000
|
|
when 0 then @Delim + C.Number +@Delim + C.Text
|
|
when 1 then @Delim + C.Number +@Delim + C.Text +@Delim + cast(ItemCount + 0 as varchar(3))
|
|
else
|
|
case P.FromType
|
|
when 1 then 'PRC' + @Delim + cast(ItemCount + 0 as varchar(3))
|
|
when 2 then @Delim + 'SEC' + @Delim + cast(ItemCount + 0 as varchar(3))
|
|
when 3 then @Delim + 'Caution' + @Delim + cast(ItemCount + 0 as varchar(3))
|
|
when 4 then @Delim + 'Note' + @Delim + cast(ItemCount + 0 as varchar(3))
|
|
when 5 then @Delim + 'RNO'
|
|
when 7 then @Delim + 'Table'
|
|
else @Delim + cast(ItemCount + 0 as varchar(3))
|
|
end end + PPath Path
|
|
|
|
from Parts P
|
|
join Items I on I.ContentID = P.ContentID
|
|
join Contents C on I.ContentID = C.ContentID
|
|
join Itemz Z on P.ItemID=Z.ItemID
|
|
)
|
|
select @Path = path From Itemz where ItemCount=1 and CType=0
|
|
OPTION (MAXRECURSION 10000)
|
|
return @Path
|
|
END;
|
|
GO
|
|
-- Display the status of Proc creation
|
|
IF (@@Error = 0) PRINT 'ScalarFunction Creation: ve_GetPath2 Succeeded'
|
|
ELSE PRINT 'ScalarFunction Creation: ve_GetPath2 Error on Creation'
|
|
GO
|
|
|
|
|
|
/****** Object: StoredProcedure [vesp_SearchAnnotationItemAndChildren] ******/
|
|
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[vesp_SearchAnnotationItemAndChildren]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
|
|
DROP PROCEDURE [vesp_SearchAnnotationItemAndChildren];
|
|
GO
|
|
|
|
/*
|
|
exec vesp_SearchAnnotationItemAndChildren Null,Null,'1',Null,0
|
|
exec vesp_SearchAnnotationItemAndChildren Null,Null,'2',Null,0
|
|
exec vesp_SearchAnnotationItemAndChildren Null,Null,'3',Null,0
|
|
exec vesp_SearchAnnotationItemAndChildren Null,Null,'4',Null,0
|
|
exec vesp_SearchAnnotationItemAndChildren Null,Null,'5',Null,0
|
|
exec vesp_SearchAnnotationItemAndChildren Null,Null,'',Null,0
|
|
*/
|
|
CREATE PROCEDURE [dbo].[vesp_SearchAnnotationItemAndChildren] (@DocVersionList varchar(MAX), @StepTypeList varchar(MAX), @AnnotationTypeList varchar(MAX), @SearchString varchar(MAX), @CaseSensitive as int) WITH EXECUTE AS OWNER
|
|
AS
|
|
BEGIN
|
|
Declare @Delim char(1)
|
|
Set @Delim=char(7)
|
|
Declare @DelimNumber char(1)
|
|
Set @DelimNumber=char(17)
|
|
Set @SearchString = .dbo.vefn_FixSearchString(@SearchString)
|
|
begin
|
|
with Itemz(DVPath, [Level], [ParentID], [Ordinal], [ItemID], [PreviousID], [FromType], [ContentID], [DTS], [UserID], [LastChanged],[pContentID],
|
|
[pDTS],[pUserID],[pLastChanged],PPath, Path, IsRNO, POrdinalPath, OrdinalPath) as (
|
|
Select DVPath, 0 [Level], 0 [ParentID], 0 [Ordinal], [I].[ItemID], [PreviousID],0 [FromType],[I].[ContentID],[I].[DTS],[I].[UserID],[I].[LastChanged]
|
|
,0 as [pContentID],[I].[DTS] As [pDTS], [I].[UserID] As [pUserID], [I].[LastChanged] As [pLastChanged],
|
|
Cast('' as nvarchar(max)) PPath,
|
|
Cast(Case when C.Type < 20000 then @Delim + C.Number + @DelimNumber + C.Text else '1' end as nvarchar(max)) Path,
|
|
0 IsRNO, Cast('' as nvarchar(max)) POrdinalPath, Cast('0001' as nvarchar(max)) OrdinalPath
|
|
FROM [Items] I
|
|
Join Contents C on C.ContentID=I.ContentID
|
|
JOIN vefn_DocVersionSplit(@DocVersionList) DV
|
|
ON I.[ItemID] = DV.[ItemID]
|
|
Union All
|
|
-- Children
|
|
select DVPath, [Level] + 1,Z.ItemID,0, I.[ItemID], I.[PreviousID], P.[FromType],I.[ContentID],I.[DTS],I.[UserID],I.[LastChanged]
|
|
,P.[ContentID] as [pContentID],P.[DTS] As [pDTS],P.[UserID] As [pUserID],P.[LastChanged] As [pLastChanged]
|
|
,PATH + --''
|
|
case C.Type/10000
|
|
when 2 then
|
|
case P.FromType
|
|
when 3 then @Delim + 'Caution'
|
|
when 4 then @Delim + 'Note'
|
|
else '' end
|
|
else '' end
|
|
PPath,
|
|
Path + @Delim + case C.Type/10000
|
|
when 0 then C.Number + @DelimNumber + C.Text
|
|
when 1 then C.Number + @DelimNumber + C.Text
|
|
else
|
|
case P.FromType
|
|
--when 1 then 'PRC' + @Delim + cast(1 as varchar(3))
|
|
--when 2 then 'SEC' + @Delim + cast(1 as varchar(3))
|
|
when 3 then 'Caution' + @Delim + cast(1 as varchar(3))
|
|
when 4 then 'Note' + @Delim + cast(1 as varchar(3))
|
|
when 5 then 'RNO' + @Delim
|
|
when 7 then 'Table' + @Delim
|
|
else cast(1 as varchar(3))
|
|
end end Path,
|
|
case when P.FromType = 5 then -1 else 0 end IsRNO,
|
|
OrdinalPath + '.' + Right('0000' + Cast(P.FromType as varchar(4)), 4) + '-' ,
|
|
OrdinalPath + '.' + Right('0000' + Cast(P.FromType as varchar(4)), 4) + '-0001'
|
|
from Itemz Z
|
|
join Parts P on P.ContentID = Z.ContentID
|
|
join Items I on I.ItemID = P.ItemID
|
|
join Contents C on C.ContentID = I.ContentID
|
|
Union All
|
|
-- Siblings
|
|
select DVPath, [Level] ,Z.[ParentID],Z.[Ordinal] +1, I.[ItemID], I.[PreviousID], [FromType],I.[ContentID],I.[DTS],I.[UserID],I.[LastChanged]
|
|
,null,null,null,null
|
|
,PPath,
|
|
--'1' +
|
|
PPath + @Delim + case C.Type/10000
|
|
when 0 then C.Number + @DelimNumber + C.Text
|
|
when 1 then C.Number + @DelimNumber + C.Text
|
|
else cast(Ordinal + 2 as varchar(3))
|
|
end Path, 0,
|
|
POrdinalPath,
|
|
POrdinalPath + right('0000' + cast(Z.[Ordinal] + 2 as varchar(4)), 4)
|
|
from Itemz Z
|
|
join Items I on I.PreviousID = Z.ItemID
|
|
join Contents C on C.ContentID = I.ContentID
|
|
--where Z.[Level] > 0
|
|
)
|
|
select DvPath, Path, I.[Level],I.[FromType],I.[Ordinal], I.[ParentID], I.[ItemID],I.[PreviousID],I.[ContentID],I.[DTS],I.[UserID],I.[LastChanged],
|
|
C.[Number],C.[Text],C.[Type],C.[FormatID],C.[Config],C.[DTS] As [cDTS],C.[UserID] As [cUserID],C.[LastChanged] As [cLastChanged],
|
|
[pContentID],[pDTS],[pUserID],[pLastChanged],AnnotationID SearchAnnotationID, SearchText, TT.Name AnnotationType,
|
|
(SELECT COUNT(*) FROM [Annotations] WHERE [Annotations].[ItemID]=[I].[ItemID]) [AnnotationCount],
|
|
(SELECT COUNT(*) FROM [DocVersions] WHERE [DocVersions].[ItemID]=[I].[ItemID]) [DocVersionCount],
|
|
(SELECT COUNT(*) FROM [Items] [Next] WHERE [Next].[PreviousID]=[I].[ItemID]) [NextCount],
|
|
(SELECT COUNT(*) FROM [Parts] WHERE [Parts].[ItemID]=[I].[ItemID]) [PartCount],
|
|
(SELECT COUNT(*) FROM [Transitions] WHERE [Transitions].[RangeID]=[I].[ItemID]) [Transition_RangeIDCount],
|
|
(SELECT COUNT(*) FROM [Transitions] WHERE [Transitions].[ToID]=[I].[ItemID]) [Transition_ToIDCount],
|
|
(SELECT COUNT(*) FROM [Details] WHERE [Details].[ContentID]=[C].[ContentID]) [DetailCount],
|
|
(SELECT COUNT(*) FROM [Entries] WHERE [Entries].[ContentID]=[C].[ContentID]) [EntryCount],
|
|
(SELECT COUNT(*) FROM [Items] WHERE [Items].[ContentID]=[C].[ContentID]) [ItemCount],
|
|
(SELECT COUNT(*) FROM [Parts] WHERE [Parts].[ContentID]=[C].[ContentID]) [cPartCount],
|
|
(SELECT COUNT(*) FROM [RoUsages] WHERE [RoUsages].[ContentID]=[C].[ContentID]) [RoUsageCount],
|
|
(SELECT COUNT(*) FROM [Transitions] WHERE [Transitions].[FromID]=[C].[ContentID]) [TransitionCount],
|
|
(SELECT COUNT(*) FROM [ZContents] WHERE [ZContents].[ContentID]=[C].[ContentID]) [ZContentCount]
|
|
from ItemZ I
|
|
join Contents C on C.ContentID = I.ContentID
|
|
left join Entries E on C.ContentID = E.ContentID
|
|
left join Documents D on E.DocID = D.DocID
|
|
join Annotations A on A.ItemID = I.ItemID
|
|
join vefn_AnnotationTypeSplit(@AnnotationTypeList) TT on TT.TypeID=A.TypeID
|
|
where
|
|
(isnull(@SearchString,'')=''
|
|
OR (@CaseSensitive = 1 AND A.SearchText like @SearchString Collate SQL_Latin1_General_CP1_CS_AS)
|
|
OR (@CaseSensitive = 0 AND A.SearchText like @SearchString Collate SQL_Latin1_General_CP1_CI_AS))
|
|
AND
|
|
((isnull(@StepTypeList,'') = '' and dbo.vefn_AllSections(C.Type)>=10000)
|
|
or
|
|
((dbo.vefn_AllSections(C.Type) in (Select ID from vefn_SplitInt(@StepTypeList,',')))
|
|
or
|
|
(@StepTypeList like '%20040%' AND IsRNO = -1)))
|
|
order by DvPath,OrdinalPath
|
|
OPTION (MAXRECURSION 10000)
|
|
END
|
|
END
|
|
GO
|
|
-- Display the status of Proc creation
|
|
IF (@@Error = 0) PRINT 'Procedure Creation: vesp_SearchAnnotationItemAndChildren Succeeded'
|
|
ELSE PRINT 'Procedure Creation: vesp_SearchAnnotationItemAndChildren Error on Creation'
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [vesp_SearchItemAndChildren] ******/
|
|
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[vesp_SearchItemAndChildren]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
|
|
DROP PROCEDURE [vesp_SearchItemAndChildren];
|
|
GO
|
|
|
|
/*
|
|
exec vesp_SearchItemAndChildren '1,2,4','20007','%#Link:Transition%',1,0,0,0
|
|
exec vesp_SearchItemAndChildren '1,2,4','20007','%#Link:Transition%',1,1,0,0
|
|
exec vesp_SearchItemAndChildren '1,2,4','20007','%#Link:Transition%',1,2,0,0
|
|
exec vesp_SearchItemAndChildren '4','','%#Link:Refer%',1,2,0,0
|
|
exec vesp_SearchItemAndChildren '4','','%200`F%',0,1,0,0
|
|
exec vesp_SearchItemAndChildren '4','10000','',0,0,0,0
|
|
exec vesp_SearchItemAndChildren '4','','%[0-9]`F%',0,1,0,0
|
|
exec vesp_SearchItemAndChildren '4','','%WATER%',0,0,0,0
|
|
exec vesp_SearchItemAndChildren '','','',0,0,0,0
|
|
exec vesp_SearchItemAndChildren '','20040','',0,0,0,0
|
|
exec vesp_SearchItemAndChildren '','10000','XYZZY',0,0,0,0
|
|
exec vesp_SearchItemAndChildren '','10000','XYZZY',1,0,0,0
|
|
exec vesp_SearchItemAndChildren '','','%IF%',0,0,0,0
|
|
exec vesp_SearchItemAndChildren '','','%IF%',1,0,0,0
|
|
exec vesp_SearchItemAndChildren '','','%47%',1,0,0,0
|
|
exec vesp_SearchItemAndChildren '','','%47%',1,0,0,1
|
|
*/
|
|
CREATE PROCEDURE [dbo].[vesp_SearchItemAndChildren] (@DocVersionList varchar(MAX), @StepTypeList varchar(MAX),
|
|
@SearchString varchar(MAX), @CaseSensitive as int, @IncludeLinks as int, @IncludeRtfFormatting as int, @IncludeSpecialCharacters as int) WITH EXECUTE AS OWNER
|
|
AS
|
|
BEGIN
|
|
Declare @Delim char(1)
|
|
Set @Delim=char(7)
|
|
Declare @DelimNumber char(1)
|
|
Set @DelimNumber=char(17)
|
|
Set @SearchString = .dbo.vefn_FixSearchString(@SearchString)
|
|
begin
|
|
with Itemz(DVPath, [Level], [ParentID], [Ordinal], [ItemID], [PreviousID], [FromType], [ContentID], [DTS], [UserID], [LastChanged],[pContentID],
|
|
[pDTS],[pUserID],[pLastChanged],PPath, Path, IsRNO, POrdinalPath, OrdinalPath) as (
|
|
Select DVPath, 0 [Level], 0 [ParentID], 0 [Ordinal], [I].[ItemID], [PreviousID],0 [FromType],[I].[ContentID],[I].[DTS],[I].[UserID],[I].[LastChanged]
|
|
,0 as [pContentID],[I].[DTS] As [pDTS], [I].[UserID] As [pUserID], [I].[LastChanged] As [pLastChanged],
|
|
Cast('' as nvarchar(max)) PPath,
|
|
Cast(Case when C.Type < 20000 then @Delim + C.Number + @DelimNumber + C.Text else '1' end as nvarchar(max)) Path,
|
|
0 IsRNO, Cast('' as nvarchar(max)) POrdinalPath, Cast('0001' as nvarchar(max)) OrdinalPath
|
|
FROM [Items] I
|
|
Join Contents C on C.ContentID=I.ContentID
|
|
JOIN vefn_DocVersionSplit(@DocVersionList) DV
|
|
ON I.[ItemID] = DV.[ItemID]
|
|
Union All
|
|
-- Children
|
|
select DVPath, [Level] + 1,Z.ItemID,0, I.[ItemID], I.[PreviousID], P.[FromType],I.[ContentID],I.[DTS],I.[UserID],I.[LastChanged]
|
|
,P.[ContentID] as [pContentID],P.[DTS] As [pDTS],P.[UserID] As [pUserID],P.[LastChanged] As [pLastChanged]
|
|
,PATH + --''
|
|
case C.Type/10000
|
|
when 2 then
|
|
case P.FromType
|
|
when 3 then @Delim + 'Caution'
|
|
when 4 then @Delim + 'Note'
|
|
else '' end
|
|
else '' end
|
|
PPath,
|
|
Path + @Delim + case C.Type/10000
|
|
when 0 then C.Number + @DelimNumber + C.Text
|
|
when 1 then C.Number + @DelimNumber + C.Text
|
|
else
|
|
case P.FromType
|
|
--when 1 then 'PRC' + @Delim + cast(1 as varchar(3))
|
|
--when 2 then 'SEC' + @Delim + cast(1 as varchar(3))
|
|
when 3 then 'Caution' + @Delim + cast(1 as varchar(3))
|
|
when 4 then 'Note' + @Delim + cast(1 as varchar(3))
|
|
when 5 then 'RNO' + @Delim
|
|
when 7 then 'Table' + @Delim
|
|
else cast(1 as varchar(3))
|
|
end end Path,
|
|
case when P.FromType = 5 then -1 else 0 end IsRNO,
|
|
OrdinalPath + '.' + Right('0000' + Cast(P.FromType as varchar(4)), 4) + '-' ,
|
|
OrdinalPath + '.' + Right('0000' + Cast(P.FromType as varchar(4)), 4) + '-0001'
|
|
from Itemz Z
|
|
join Parts P on P.ContentID = Z.ContentID
|
|
join Items I on I.ItemID = P.ItemID
|
|
join Contents C on C.ContentID = I.ContentID
|
|
Union All
|
|
-- Siblings
|
|
select DVPath, [Level] ,Z.[ParentID],Z.[Ordinal] +1, I.[ItemID], I.[PreviousID], [FromType],I.[ContentID],I.[DTS],I.[UserID],I.[LastChanged]
|
|
,null,null,null,null
|
|
,PPath,
|
|
--'1' +
|
|
PPath + @Delim + case C.Type/10000
|
|
when 0 then C.Number + @DelimNumber + C.Text
|
|
when 1 then C.Number + @DelimNumber + C.Text
|
|
else cast(Ordinal + 2 as varchar(3))
|
|
end Path, 0,
|
|
POrdinalPath,
|
|
POrdinalPath + right('0000' + cast(Z.[Ordinal] + 2 as varchar(4)), 4)
|
|
from Itemz Z
|
|
join Items I on I.PreviousID = Z.ItemID
|
|
join Contents C on C.ContentID = I.ContentID
|
|
--where Z.[Level] > 0
|
|
)
|
|
select DvPath, Path, I.[Level],I.[FromType],I.[Ordinal], I.[ParentID], I.[ItemID],I.[PreviousID],I.[ContentID],I.[DTS],I.[UserID],I.[LastChanged],
|
|
C.[Number],C.[Text],C.[Type],C.[FormatID],C.[Config],C.[DTS] As [cDTS],C.[UserID] As [cUserID],C.[LastChanged] As [cLastChanged],
|
|
[pContentID],[pDTS],[pUserID],[pLastChanged],
|
|
(SELECT COUNT(*) FROM [Annotations] WHERE [Annotations].[ItemID]=[I].[ItemID]) [AnnotationCount],
|
|
(SELECT COUNT(*) FROM [DocVersions] WHERE [DocVersions].[ItemID]=[I].[ItemID]) [DocVersionCount],
|
|
(SELECT COUNT(*) FROM [Items] [Next] WHERE [Next].[PreviousID]=[I].[ItemID]) [NextCount],
|
|
(SELECT COUNT(*) FROM [Parts] WHERE [Parts].[ItemID]=[I].[ItemID]) [PartCount],
|
|
(SELECT COUNT(*) FROM [Transitions] WHERE [Transitions].[RangeID]=[I].[ItemID]) [Transition_RangeIDCount],
|
|
(SELECT COUNT(*) FROM [Transitions] WHERE [Transitions].[ToID]=[I].[ItemID]) [Transition_ToIDCount],
|
|
(SELECT COUNT(*) FROM [Details] WHERE [Details].[ContentID]=[C].[ContentID]) [DetailCount],
|
|
(SELECT COUNT(*) FROM [Entries] WHERE [Entries].[ContentID]=[C].[ContentID]) [EntryCount],
|
|
(SELECT COUNT(*) FROM [Items] WHERE [Items].[ContentID]=[C].[ContentID]) [ItemCount],
|
|
(SELECT COUNT(*) FROM [Parts] WHERE [Parts].[ContentID]=[C].[ContentID]) [cPartCount],
|
|
(SELECT COUNT(*) FROM [RoUsages] WHERE [RoUsages].[ContentID]=[C].[ContentID]) [RoUsageCount],
|
|
(SELECT COUNT(*) FROM [Transitions] WHERE [Transitions].[FromID]=[C].[ContentID]) [TransitionCount],
|
|
(SELECT COUNT(*) FROM [ZContents] WHERE [ZContents].[ContentID]=[C].[ContentID]) [ZContentCount]
|
|
from ItemZ I
|
|
join Contents C on C.ContentID = I.ContentID
|
|
left join Entries E on C.ContentID = E.ContentID
|
|
left join Documents D on E.DocID = D.DocID
|
|
where
|
|
(isnull(@SearchString,'')=''
|
|
OR (@CaseSensitive = 1 AND .dbo.vefn_RemoveExtraText(C.Text,@IncludeLinks, @IncludeRtfFormatting, @IncludeSpecialCharacters) like @SearchString Collate SQL_Latin1_General_CP1_CS_AS)
|
|
OR (@CaseSensitive = 0 AND .dbo.vefn_RemoveExtraText(C.Text,@IncludeLinks, @IncludeRtfFormatting, @IncludeSpecialCharacters) like @SearchString Collate SQL_Latin1_General_CP1_CI_AS)
|
|
OR (@CaseSensitive = 1 AND D.DocAscii like @SearchString Collate SQL_Latin1_General_CP1_CS_AS)
|
|
OR (@CaseSensitive = 0 AND D.DocAscii like @SearchString Collate SQL_Latin1_General_CP1_CI_AS))
|
|
AND
|
|
((isnull(@StepTypeList,'') = '' and dbo.vefn_AllSections(C.Type)>=10000)
|
|
or
|
|
((dbo.vefn_AllSections(C.Type) in (Select ID from vefn_SplitInt(@StepTypeList,',')))
|
|
or
|
|
(@StepTypeList like '%20040%' AND IsRNO = -1)))
|
|
order by DvPath,OrdinalPath
|
|
OPTION (MAXRECURSION 10000)
|
|
END
|
|
END
|
|
GO
|
|
-- Display the status of Proc creation
|
|
IF (@@Error = 0) PRINT 'Procedure Creation: vesp_SearchItemAndChildren Succeeded'
|
|
ELSE PRINT 'Procedure Creation: vesp_SearchItemAndChildren Error on Creation'
|
|
GO
|
|
|
|
|
|
/****** Object: StoredProcedure [vesp_SearchROItemAndChildren] ******/
|
|
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[vesp_SearchROItemAndChildren]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
|
|
DROP PROCEDURE [vesp_SearchROItemAndChildren];
|
|
GO
|
|
|
|
/*
|
|
exec vesp_SearchROItemAndChildren '1,2,4','20007','1:00010000019C,0001000002AE'
|
|
exec vesp_SearchROItemAndChildren '1,2,4','','1:00010000019C,0001000002AE'
|
|
exec vesp_SearchROItemAndChildren '1,2,4','20007','1:0001'
|
|
exec vesp_SearchROItemAndChildren '1,2,4','20006','1'
|
|
exec vesp_SearchROItemAndChildren '1,2,4','20006',''
|
|
exec vesp_SearchROItemAndChildren '1,2,4','20006',Null
|
|
*/
|
|
CREATE PROCEDURE [dbo].[vesp_SearchROItemAndChildren] (@DocVersionList varchar(MAX), @StepTypeList varchar(MAX), @ROSearchString varchar(MAX)) WITH EXECUTE AS OWNER
|
|
AS
|
|
BEGIN
|
|
Declare @Delim char(1)
|
|
Set @Delim=char(7)
|
|
Declare @DelimNumber char(1)
|
|
Set @DelimNumber=char(17)
|
|
begin
|
|
with Itemz(DVPath, [Level], [ParentID], [Ordinal], [ItemID], [PreviousID], [FromType], [ContentID], [DTS], [UserID], [LastChanged],[pContentID],
|
|
[pDTS],[pUserID],[pLastChanged],PPath, Path, IsRNO, POrdinalPath, OrdinalPath) as (
|
|
Select DVPath, 0 [Level], 0 [ParentID], 0 [Ordinal], [I].[ItemID], [PreviousID],0 [FromType],[I].[ContentID],[I].[DTS],[I].[UserID],[I].[LastChanged]
|
|
,0 as [pContentID],[I].[DTS] As [pDTS], [I].[UserID] As [pUserID], [I].[LastChanged] As [pLastChanged],
|
|
Cast('' as nvarchar(max)) PPath,
|
|
Cast(Case when C.Type < 20000 then @Delim + C.Number + @DelimNumber + C.Text else '1' end as nvarchar(max)) Path,
|
|
0 IsRNO, Cast('' as nvarchar(max)) POrdinalPath, Cast('0001' as nvarchar(max)) OrdinalPath
|
|
|
|
FROM [Items] I
|
|
Join Contents C on C.ContentID=I.ContentID
|
|
JOIN vefn_DocVersionSplit(@DocVersionList) DV
|
|
ON I.[ItemID] = DV.[ItemID]
|
|
Union All
|
|
-- Children
|
|
select DVPath, [Level] + 1,Z.ItemID,0, I.[ItemID], I.[PreviousID], P.[FromType],I.[ContentID],I.[DTS],I.[UserID],I.[LastChanged]
|
|
,P.[ContentID] as [pContentID],P.[DTS] As [pDTS],P.[UserID] As [pUserID],P.[LastChanged] As [pLastChanged]
|
|
,PATH + --''
|
|
case C.Type/10000
|
|
when 2 then
|
|
case P.FromType
|
|
when 3 then @Delim + 'Caution'
|
|
when 4 then @Delim + 'Note'
|
|
else '' end
|
|
else '' end
|
|
PPath,
|
|
Path + @Delim + case C.Type/10000
|
|
when 0 then C.Number + @DelimNumber + C.Text
|
|
when 1 then C.Number + @DelimNumber + C.Text
|
|
else
|
|
case P.FromType
|
|
--when 1 then 'PRC' + @Delim + cast(1 as varchar(3))
|
|
--when 2 then 'SEC' + @Delim + cast(1 as varchar(3))
|
|
when 3 then 'Caution' + @Delim + cast(1 as varchar(3))
|
|
when 4 then 'Note' + @Delim + cast(1 as varchar(3))
|
|
when 5 then 'RNO' + @Delim
|
|
when 7 then 'Table' + @Delim
|
|
else cast(1 as varchar(3))
|
|
end end Path,
|
|
case when P.FromType = 5 then -1 else 0 end IsRNO,
|
|
OrdinalPath + '.' + Right('0000' + Cast(P.FromType as varchar(4)), 4) + '-' ,
|
|
OrdinalPath + '.' + Right('0000' + Cast(P.FromType as varchar(4)), 4) + '-0001'
|
|
from Itemz Z
|
|
join Parts P on P.ContentID = Z.ContentID
|
|
join Items I on I.ItemID = P.ItemID
|
|
join Contents C on C.ContentID = I.ContentID
|
|
Union All
|
|
-- Siblings
|
|
select DVPath, [Level] ,Z.[ParentID],Z.[Ordinal] +1, I.[ItemID], I.[PreviousID], [FromType],I.[ContentID],I.[DTS],I.[UserID],I.[LastChanged]
|
|
,null,null,null,null
|
|
,PPath,
|
|
--'1' +
|
|
PPath + @Delim + case C.Type/10000
|
|
when 0 then C.Number + @DelimNumber + C.Text
|
|
when 1 then C.Number + @DelimNumber + C.Text
|
|
else cast(Ordinal + 2 as varchar(3))
|
|
end Path, 0,
|
|
POrdinalPath,
|
|
POrdinalPath + right('0000' + cast(Z.[Ordinal] + 2 as varchar(4)), 4)
|
|
from Itemz Z
|
|
join Items I on I.PreviousID = Z.ItemID
|
|
join Contents C on C.ContentID = I.ContentID
|
|
--where Z.[Level] > 0
|
|
)
|
|
select DvPath, Path, I.[Level],I.[FromType],I.[Ordinal], I.[ParentID], I.[ItemID],I.[PreviousID],I.[ContentID],I.[DTS],I.[UserID],I.[LastChanged],
|
|
C.[Number],C.[Text],C.[Type],C.[FormatID],C.[Config],C.[DTS] As [cDTS],C.[UserID] As [cUserID],C.[LastChanged] As [cLastChanged],
|
|
[pContentID],[pDTS],[pUserID],[pLastChanged],
|
|
(SELECT COUNT(*) FROM [Annotations] WHERE [Annotations].[ItemID]=[I].[ItemID]) [AnnotationCount],
|
|
(SELECT COUNT(*) FROM [DocVersions] WHERE [DocVersions].[ItemID]=[I].[ItemID]) [DocVersionCount],
|
|
(SELECT COUNT(*) FROM [Items] [Next] WHERE [Next].[PreviousID]=[I].[ItemID]) [NextCount],
|
|
(SELECT COUNT(*) FROM [Parts] WHERE [Parts].[ItemID]=[I].[ItemID]) [PartCount],
|
|
(SELECT COUNT(*) FROM [Transitions] WHERE [Transitions].[RangeID]=[I].[ItemID]) [Transition_RangeIDCount],
|
|
(SELECT COUNT(*) FROM [Transitions] WHERE [Transitions].[ToID]=[I].[ItemID]) [Transition_ToIDCount],
|
|
(SELECT COUNT(*) FROM [Details] WHERE [Details].[ContentID]=[C].[ContentID]) [DetailCount],
|
|
(SELECT COUNT(*) FROM [Entries] WHERE [Entries].[ContentID]=[C].[ContentID]) [EntryCount],
|
|
(SELECT COUNT(*) FROM [Items] WHERE [Items].[ContentID]=[C].[ContentID]) [ItemCount],
|
|
(SELECT COUNT(*) FROM [Parts] WHERE [Parts].[ContentID]=[C].[ContentID]) [cPartCount],
|
|
(SELECT COUNT(*) FROM [RoUsages] WHERE [RoUsages].[ContentID]=[C].[ContentID]) [RoUsageCount],
|
|
(SELECT COUNT(*) FROM [Transitions] WHERE [Transitions].[FromID]=[C].[ContentID]) [TransitionCount],
|
|
(SELECT COUNT(*) FROM [ZContents] WHERE [ZContents].[ContentID]=[C].[ContentID]) [ZContentCount]
|
|
from ItemZ I
|
|
join Contents C on C.ContentID = I.ContentID
|
|
left join Entries E on C.ContentID = E.ContentID
|
|
left join Documents D on E.DocID = D.DocID
|
|
where
|
|
I.ContentID in(select Distinct ContentID from ROUsages RU
|
|
join vefn_SplitROSearch(@ROSearchString) RR
|
|
ON RR.RODBID = RU.RODBID and
|
|
RU.ROID like RR.ROID + '%' Collate SQL_Latin1_General_CP1_CI_AS)
|
|
AND
|
|
((isnull(@StepTypeList,'') = '' and dbo.vefn_AllSections(C.Type)>=10000)
|
|
or
|
|
((dbo.vefn_AllSections(C.Type) in (Select ID from vefn_SplitInt(@StepTypeList,',')))
|
|
or
|
|
(@StepTypeList like '%20040%' AND IsRNO = -1)))
|
|
order by DvPath,OrdinalPath
|
|
OPTION (MAXRECURSION 10000)
|
|
END
|
|
END
|
|
GO
|
|
-- Display the status of Proc creation
|
|
IF (@@Error = 0) PRINT 'Procedure Creation: vesp_SearchROItemAndChildren Succeeded'
|
|
ELSE PRINT 'Procedure Creation: vesp_SearchROItemAndChildren Error on Creation'
|
|
GO
|
|
|
|
|
|
|
|
/****** Object: StoredProcedure [vefn_FixSearchString] ******/
|
|
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[vefn_FixSearchString]') AND OBJECTPROPERTY(id,N'IsScalarFunction') = 1)
|
|
DROP FUNCTION [vefn_FixSearchString];
|
|
GO
|
|
|
|
/*
|
|
select ss,.dbo.vefn_FixSearchString(ss)
|
|
from (
|
|
select '%' ss union
|
|
select '50[%]' ss union
|
|
select 'IF%' ss union
|
|
select '%then:' ss union
|
|
select '530`F' ss union
|
|
select 'check' ss) tt
|
|
*/
|
|
CREATE FUNCTION [dbo].[vefn_FixSearchString](@SearchString varchar(MAX))
|
|
RETURNS varchar(MAX)
|
|
AS
|
|
BEGIN
|
|
IF(replace(@SearchString,'[%]','') like '%[%]%')
|
|
RETURN @SearchString
|
|
RETURN '%' + @SearchString + '%'
|
|
END
|
|
GO
|
|
-- Display the status of Proc creation
|
|
IF (@@Error = 0) PRINT 'ScalarFunction Creation: vefn_FixSearchString Succeeded'
|
|
ELSE PRINT 'ScalarFunction Creation: vefn_FixSearchString Error on Creation'
|
|
GO
|
|
|
|
|
|
/****** Object: StoredProcedure [vefn_FirstLink] ******/
|
|
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[vefn_FirstLink]') AND OBJECTPROPERTY(id,N'IsScalarFunction') = 1)
|
|
DROP FUNCTION [vefn_FirstLink];
|
|
GO
|
|
|
|
/*
|
|
select .dbo.vefn_FirstLink('asdasdadsasd' + char(21) + 'asdasdasd\vasdasdasd', 2)
|
|
select .dbo.vefn_FirstLink('asdasdadsasd' + char(21) + 'asdasdasd\vasdasdasd', 1)
|
|
select .dbo.vefn_FirstLink('asdasdadsasd' + char(21) + 'asdasdasd\vasdasdasd', 0)
|
|
*/
|
|
|
|
CREATE FUNCTION [dbo].[vefn_FirstLink](@text nvarchar(MAX),@includeLink int)
|
|
RETURNS int
|
|
AS
|
|
BEGIN
|
|
DECLARE @index int
|
|
SET @index = 0
|
|
if(@includeLink = 2)
|
|
RETURN 0
|
|
if(@includeLink = 1)
|
|
return CHARINDEX('\v' , @text)
|
|
DECLARE @index2 int
|
|
SET @index = PATINDEX('%[' + nchar(9574)+nchar(9516)+nchar(21) + ']%',@text)
|
|
return @index
|
|
END
|
|
GO
|
|
-- Display the status of Proc creation
|
|
IF (@@Error = 0) PRINT 'ScalarFunction Creation: vefn_FirstLink Succeeded'
|
|
ELSE PRINT 'ScalarFunction Creation: vefn_FirstLink Error on Creation'
|
|
GO
|
|
|
|
|
|
|
|
/****** Object: StoredProcedure [vefn_RemoveExtraText] ******/
|
|
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[vefn_RemoveExtraText]') AND OBJECTPROPERTY(id,N'IsScalarFunction') = 1)
|
|
DROP FUNCTION [vefn_RemoveExtraText];
|
|
GO
|
|
|
|
/*
|
|
select text, .dbo.vefn_RemoveExtraText(text,2,0,0) StrippedText from Contents where ContentID=373
|
|
select text, .dbo.vefn_RemoveExtraText(text,1,0,0) StrippedText from Contents where ContentID=373
|
|
select text, .dbo.vefn_RemoveExtraText(text,0,0,0) StrippedText ,.dbo.vefn_FirstLink(text,0), PATINDEX('%[' + nchar(9516) + nchar(9574) + char(21) + ']%' , text) from Contents where ContentID=373
|
|
select .dbo.vefn_RemoveExtraText('\b Bold\b0',0,0,0)
|
|
select .dbo.vefn_RemoveExtraText('\b Bold\b0',0,1,0)
|
|
select .dbo.vefn_RemoveExtraText('A\u1?B\u12?C\u123?D\u1234?E',0,0,0)
|
|
select .dbo.vefn_RemoveExtraText('A\u1?B\u12?C\u123?D\u1234?E',0,0,1)
|
|
*/
|
|
|
|
|
|
CREATE FUNCTION [dbo].[vefn_RemoveExtraText](@text nvarchar(MAX),@includeLink int, @includeRtfFormatting int , @includeSpecialCharacters int)
|
|
RETURNS varchar(MAX)
|
|
AS
|
|
BEGIN
|
|
DECLARE @index int
|
|
DECLARE @index2 int
|
|
SET @index = .dbo.vefn_FirstLink(@text,@includeLink)
|
|
while (@index != 0)
|
|
BEGIN
|
|
SET @index2 = CHARINDEX('\v0' , @text)
|
|
SET @text = substring(@text,1,@index-1) + substring(@text,@index2+3,len(@text))
|
|
SET @index = .dbo.vefn_FirstLink(@text,@includeLink)
|
|
END
|
|
if(@includeRtfFormatting=0)
|
|
-- Remove Rtf Formatting
|
|
BEGIN
|
|
SET @text = Replace(@text, '\b0', '');
|
|
SET @text = Replace(@text, '\b', '');
|
|
SET @text = Replace(@text, '\ul0', '');
|
|
SET @text = Replace(@text, '\ul', '');
|
|
SET @text = Replace(@text, '\i0', '');
|
|
SET @text = Replace(@text, '\i', '');
|
|
SET @text = Replace(@text, '\super', '');
|
|
SET @text = Replace(@text, '\sub', '');
|
|
SET @text = Replace(@text, '\nosupersub', '');
|
|
END
|
|
if(@includeSpecialCharacters=0)
|
|
-- Remove Special Characters
|
|
BEGIN
|
|
SET @index = PATINDEX('%\u[0-9]?%',@text)
|
|
while(@index != 0)
|
|
BEGIN
|
|
SET @text = substring(@text,1,@index-1) + substring(@text,@index+4,len(@text))
|
|
SET @index = PATINDEX('%\u[0-9]?%',@text)
|
|
END
|
|
SET @index = PATINDEX('%\u[0-9][0-9]?%',@text)
|
|
while(@index != 0)
|
|
BEGIN
|
|
SET @text = substring(@text,1,@index-1) + substring(@text,@index+5,len(@text))
|
|
SET @index = PATINDEX('%\u[0-9][0-9]?%',@text)
|
|
END
|
|
SET @index = PATINDEX('%\u[0-9][0-9][0-9]?%',@text)
|
|
while(@index != 0)
|
|
BEGIN
|
|
SET @text = substring(@text,1,@index-1) + substring(@text,@index+6,len(@text))
|
|
SET @index = PATINDEX('%\u[0-9][0-9][0-9]?%',@text)
|
|
END
|
|
SET @index = PATINDEX('%\u[0-9][0-9][0-9][0-9]?%',@text)
|
|
while(@index != 0)
|
|
BEGIN
|
|
SET @text = substring(@text,1,@index-1) + substring(@text,@index+7,len(@text))
|
|
SET @index = PATINDEX('%\u[0-9][0-9][0-9][0-9]?%',@text)
|
|
END
|
|
SET @index = PATINDEX('%\''[0-9A-Fa-f][0-9A-Fa-f]%',@text)
|
|
while(@index != 0)
|
|
BEGIN
|
|
SET @text = substring(@text,1,@index-1) + substring(@text,@index+4,len(@text))
|
|
SET @index = PATINDEX('%\''[0-9A-Fa-f][0-9A-Fa-f]%',@text)
|
|
END
|
|
|
|
END
|
|
RETURN @text
|
|
END
|
|
GO
|
|
-- Display the status of Proc creation
|
|
IF (@@Error = 0) PRINT 'ScalarFunction Creation: vefn_RemoveExtraText Succeeded'
|
|
ELSE PRINT 'ScalarFunction Creation: vefn_RemoveExtraText Error on Creation'
|
|
GO
|
|
|
|
|
|
|
|
|
|
|
|
/****** Object: StoredProcedure [vefn_DocVersionSplit] ******/
|
|
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[vefn_DocVersionSplit]') AND OBJECTPROPERTY(id,N'IsTableFunction') = 1)
|
|
DROP FUNCTION [vefn_DocVersionSplit];
|
|
GO
|
|
|
|
/*
|
|
select * from vefn_DocVersionSplit('1,4')
|
|
select * from vefn_DocVersionSplit(null)
|
|
select * from vefn_DocVersionSplit('')
|
|
*/
|
|
CREATE FUNCTION [dbo].[vefn_DocVersionSplit](@DocVersionList varchar(MAX))
|
|
RETURNS @IDs TABLE
|
|
(
|
|
VersionID int PRIMARY KEY,
|
|
ItemID int,
|
|
DVPath varchar(MAX)
|
|
)
|
|
AS
|
|
BEGIN
|
|
IF(isnull(@DocVersionList,'') = '')
|
|
Insert into @IDs
|
|
Select VersionID, ItemID, dbo.ve_GetFolderPath(VersionID)
|
|
from DocVersions where ItemID is not null
|
|
else
|
|
Insert into @IDs
|
|
Select ID, ItemID, dbo.ve_GetFolderPath(VersionID)
|
|
from vefn_SplitInt(@DocVersionList,',') as T
|
|
join DocVersions DV on VersionID = T.ID
|
|
RETURN
|
|
END
|
|
GO
|
|
-- Display the status of Proc creation
|
|
IF (@@Error = 0) PRINT 'TableFunction Creation: vefn_DocVersionSplit Succeeded'
|
|
ELSE PRINT 'TableFunction Creation: vefn_DocVersionSplit Error on Creation'
|
|
GO
|
|
|
|
|
|
|
|
/****** Object: StoredProcedure [vefn_SplitInt] ******/
|
|
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[vefn_SplitInt]') AND OBJECTPROPERTY(id,N'IsTableFunction') = 1)
|
|
DROP FUNCTION [vefn_SplitInt];
|
|
GO
|
|
|
|
--select * from DocVersions where VersionID in(select * from vefn_SplitInt('1,2,4',','))
|
|
--select Type, Count(*) from Contents where Type in(select * from vefn_SplitInt('20001,20002,20004',',')) group by Type
|
|
CREATE FUNCTION [dbo].[vefn_SplitInt](@text varchar(MAX), @delimiter varchar(20) = ',')
|
|
RETURNS @IDs TABLE
|
|
(
|
|
ID int PRIMARY KEY
|
|
)
|
|
AS
|
|
BEGIN
|
|
DECLARE @index int
|
|
SET @index = -1
|
|
WHILE (LEN(@text) > 0)
|
|
BEGIN
|
|
SET @index = CHARINDEX(@delimiter , @text)
|
|
IF (@index = 0) AND (LEN(@text) > 0)
|
|
BEGIN
|
|
INSERT INTO @IDs VALUES (CAST(@text AS INT ))
|
|
BREAK
|
|
END
|
|
IF (@index > 1)
|
|
BEGIN
|
|
INSERT INTO @IDs VALUES (CAST(LEFT(@text, @index - 1) AS INT))
|
|
SET @text = RIGHT(@text, (LEN(@text) - @index))
|
|
END
|
|
ELSE
|
|
SET @text = RIGHT(@text, (LEN(@text) - @index))
|
|
END
|
|
RETURN
|
|
END
|
|
GO
|
|
-- Display the status of Proc creation
|
|
IF (@@Error = 0) PRINT 'TableFunction Creation: vefn_SplitInt Succeeded'
|
|
ELSE PRINT 'TableFunction Creation: vefn_SplitInt Error on Creation'
|
|
GO
|
|
|
|
|
|
/****** Object: StoredProcedure [vefn_SplitROSearch] ******/
|
|
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[vefn_SplitROSearch]') AND OBJECTPROPERTY(id,N'IsTableFunction') = 1)
|
|
DROP FUNCTION [vefn_SplitROSearch];
|
|
GO
|
|
|
|
/*
|
|
select * from vefn_SplitROSearch(null)
|
|
select * from vefn_SplitROSearch('')
|
|
select * from vefn_SplitROSearch('1')
|
|
select * from vefn_SplitROSearch('1:0001')
|
|
select * from vefn_SplitROSearch('1:0001000019C')
|
|
select * from vefn_SplitROSearch('1:0001000019C,0001000019D')
|
|
*/
|
|
CREATE FUNCTION [dbo].[vefn_SplitROSearch](@text varchar(MAX)='')
|
|
RETURNS @ROIDSearch TABLE
|
|
(
|
|
RODBID int,
|
|
ROID varchar(16)
|
|
--,CONSTRAINT ROIDSearchPK PRIMARY KEY(RODBID, ROID)
|
|
)
|
|
AS
|
|
BEGIN
|
|
DECLARE @index int
|
|
IF(isnull(@text,'') = '')
|
|
BEGIN
|
|
Insert into @ROIDSearch
|
|
Select RODBID, ''
|
|
from RODbs
|
|
RETURN
|
|
END
|
|
SET @index = CHARINDEX(':' , @text)
|
|
DECLARE @RODBID int
|
|
IF (@index = 0) AND (LEN(@text) > 0)
|
|
BEGIN
|
|
SET @RODBID = CAST(@text AS INT)
|
|
INSERT INTO @ROIDSearch VALUES (@RODBID,'')
|
|
SET @Text=''
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
SET @RODBID = CAST(LEFT(@text, @index - 1) AS INT)
|
|
SET @text = RIGHT(@text, (LEN(@text) - @index))
|
|
SET @index = -1
|
|
END
|
|
SET @text = RIGHT(@text, (LEN(@text) - @index))
|
|
WHILE (LEN(@text) > 0)
|
|
BEGIN
|
|
SET @index = CHARINDEX(',' , @text)
|
|
IF (@index = 0) AND (LEN(@text) > 0)
|
|
BEGIN
|
|
INSERT INTO @ROIDSearch VALUES (@RODBID,@text)
|
|
BREAK
|
|
END
|
|
IF (@index > 1)
|
|
BEGIN
|
|
INSERT INTO @ROIDSearch VALUES (@RODBID,LEFT(@text, @index - 1))
|
|
SET @text = RIGHT(@text, (LEN(@text) - @index))
|
|
END
|
|
ELSE
|
|
SET @text = RIGHT(@text, (LEN(@text) - @index))
|
|
END
|
|
RETURN
|
|
END
|
|
GO
|
|
-- Display the status of Proc creation
|
|
IF (@@Error = 0) PRINT 'TableFunction Creation: vefn_SplitROSearch Succeeded'
|
|
ELSE PRINT 'TableFunction Creation: vefn_SplitROSearch Error on Creation'
|
|
GO
|
|
|
|
|
|
|
|
/****** Object: StoredProcedure [vefn_AnnotationTypeSplit] ******/
|
|
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[vefn_AnnotationTypeSplit]') AND OBJECTPROPERTY(id,N'IsTableFunction') = 1)
|
|
DROP FUNCTION [vefn_AnnotationTypeSplit];
|
|
GO
|
|
|
|
/*
|
|
select * from vefn_AnnotationTypeSplit('1,4')
|
|
select * from vefn_AnnotationTypeSplit(null)
|
|
select * from vefn_AnnotationTypeSplit('')
|
|
*/
|
|
CREATE FUNCTION [dbo].[vefn_AnnotationTypeSplit](@AnnotationTypeList varchar(MAX))
|
|
RETURNS @IDs TABLE
|
|
(
|
|
TypeID int PRIMARY KEY,
|
|
Name varchar(100)
|
|
)
|
|
AS
|
|
BEGIN
|
|
IF(isnull(@AnnotationTypeList,'') = '')
|
|
Insert into @IDs
|
|
Select TypeID, Name
|
|
from AnnotationTypes
|
|
else
|
|
Insert into @IDs
|
|
Select TypeID,NAME
|
|
from AnnotationTypes
|
|
where TypeID in(select ID from vefn_SplitInt(@AnnotationTypeList,','))
|
|
RETURN
|
|
END
|
|
GO
|
|
-- Display the status of Proc creation
|
|
IF (@@Error = 0) PRINT 'TableFunction Creation: vefn_AnnotationTypeSplit Succeeded'
|
|
ELSE PRINT 'TableFunction Creation: vefn_AnnotationTypeSplit Error on Creation'
|
|
GO
|
|
|
|
|
|
/****** Object: StoredProcedure [ve_GetPath] ******/
|
|
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[ve_GetPath]') AND OBJECTPROPERTY(id,N'IsScalarFunction') = 1)
|
|
DROP FUNCTION [ve_GetPath];
|
|
GO
|
|
|
|
/*
|
|
select ItemID, CC.Type, dbo.ve_GetPath(ItemID)
|
|
from Items II join Contents CC on II.ContentID = CC.ContentID
|
|
where ItemID in(111,265,266,267)
|
|
*/
|
|
-- drop function ve_GetPath
|
|
CREATE FUNCTION [dbo].[ve_GetPath] (@ItemID int) RETURNS varchar(max) WITH EXECUTE AS OWNER AS BEGIN
|
|
-- declare @STructID int
|
|
--set @StructID=11
|
|
declare @Path varchar(max);
|
|
with Itemz(ItemCount,ItemID,PreviousID,FromType,CType,Number,Text,PPath, Path) as (
|
|
Select 1,ItemID,PreviousID,0,C.Type/10000,C.Number,C.Text,
|
|
Cast(Case when C.Type < 20000 then '`' + C.Number +'`' + C.Text else '' end as nvarchar(max)) PPath,
|
|
Cast(Case when C.Type < 20000 then '`' + C.Number +'`' + C.Text else '1' end as nvarchar(max)) Path
|
|
from Items I
|
|
join Contents C on I.ContentID = C.ContentID
|
|
where ItemID=@ItemID
|
|
Union All
|
|
-- siblings
|
|
Select ItemCount+1,I.PreviousID,II.PreviousID,0,C.Type/10000,Z.Number,Z.Text,PPath,cast(ItemCount + 1 as nvarchar(3)) + PPath
|
|
from Items I
|
|
join Contents C on I.ContentID = C.ContentID
|
|
Join Items II on II.ItemID = I.PreviousID
|
|
Join Itemz Z on I.ItemID=Z.ItemID
|
|
where I.PreviousID != 0
|
|
Union All
|
|
-- children
|
|
select 1,I.ItemID,I.PreviousID,P.FromType,C.Type/10000,C.Number,C.Text,
|
|
case C.Type/10000
|
|
when 0 then '`' + C.Number +'`' + C.Text
|
|
when 1 then '`' + C.Number +'`' + C.Text +'`' + cast(ItemCount + 0 as varchar(3))
|
|
else
|
|
case P.FromType
|
|
when 1 then 'PRC`' + cast(ItemCount + 0 as varchar(3))
|
|
when 2 then '`SEC`' + cast(ItemCount + 0 as varchar(3))
|
|
when 3 then '`Caution`' + cast(ItemCount + 0 as varchar(3))
|
|
when 4 then '`Note`' + cast(ItemCount + 0 as varchar(3))
|
|
when 5 then '`RNO`'
|
|
when 7 then '`Table`'
|
|
else '`' + cast(ItemCount + 0 as varchar(3))
|
|
end end + PPath PPath,
|
|
--'1' +
|
|
case C.Type/10000
|
|
when 0 then '`' + C.Number +'`' + C.Text
|
|
when 1 then '`' + C.Number +'`' + C.Text +'`' + cast(ItemCount + 0 as varchar(3))
|
|
else
|
|
case P.FromType
|
|
when 1 then 'PRC`' + cast(ItemCount + 0 as varchar(3))
|
|
when 2 then '`SEC`' + cast(ItemCount + 0 as varchar(3))
|
|
when 3 then '`Caution`' + cast(ItemCount + 0 as varchar(3))
|
|
when 4 then '`Note`' + cast(ItemCount + 0 as varchar(3))
|
|
when 5 then '`RNO'
|
|
when 7 then '`Table'
|
|
else '`' + cast(ItemCount + 0 as varchar(3))
|
|
end end + PPath Path
|
|
|
|
from Parts P
|
|
join Items I on I.ContentID = P.ContentID
|
|
join Contents C on I.ContentID = C.ContentID
|
|
join Itemz Z on P.ItemID=Z.ItemID
|
|
)
|
|
select @Path = path From Itemz where ItemCount=1 and CType=0
|
|
OPTION (MAXRECURSION 10000)
|
|
return @Path
|
|
END;
|
|
GO
|
|
-- Display the status of Proc creation
|
|
IF (@@Error = 0) PRINT 'ScalarFunction Creation: ve_GetPath Succeeded'
|
|
ELSE PRINT 'ScalarFunction Creation: ve_GetPath Error on Creation'
|
|
GO
|