/****** 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 ID,ss,.dbo.vefn_FixSearchString(ss)
from (
select 1 ID,'*' ss union
select 2 ID,'50%' ss union
select 3 ID,'IF*' ss union
select 4 ID,'*then:' ss union
select 5 ID,'530`F' ss union
select 6 ID,'check' ss union
select 7 ID,'RCP*Cooling' ss union
select 8 ID,'14%[34%]' ss union
select 9 ID,'\*' ss union
select 10 ID,'\?' ss union
select 11 ID,'_' ss union
select 12 ID,'[' ss union
select 13 ID,']' ss union
select 14 ID,'%' ss union
select 15 ID,'_' ss union
select 16 ID,'-' ss
) tt order by ID
*/
CREATE FUNCTION [dbo].[vefn_FixSearchString](@SearchString nvarchar(MAX))
RETURNS nvarchar(MAX)
AS
BEGIN
  -- This code adds % at the beginning and end if the beginning and end
  -- of the search string if it does not have % at the beginning or end
  Set @SearchString = replace(@SearchString,'[','[[]')
  Set @SearchString = replace(@SearchString,'_','[_]')
  Set @SearchString = replace(@SearchString,'%','[%]')
  Set @SearchString = replace(@SearchString,'*','%')
  Set @SearchString = replace(@SearchString,'?','_')
  Set @SearchString = replace(@SearchString,'\%','*')
  Set @SearchString = replace(@SearchString,'\_','?')
  Set @SearchString = replace(@SearchString,'-','[-'+nchar(8209)+']')
  IF(@SearchString like '[%]%') RETURN @SearchString
  IF(@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 [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 nvarchar(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)
Declare @DelimStep char(1)
Set @DelimStep='.'
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 @DelimStep + 'Caution'
	when 4 then @DelimStep + 'Note'
  else '' end
	else '' end
	PPath,
	Path + case C.Type/10000
	when 0 then @Delim +C.Number + @DelimNumber + C.Text
	when 1 then @Delim +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 @DelimStep +'Caution' + @DelimStep + cast(1 as varchar(3))
	when 4 then @DelimStep +'Note' + @DelimStep + cast(1 as varchar(3))
	when 5 then @DelimStep +'RNO' + @DelimStep
	when 7 then @DelimStep +'Table' + @DelimStep
	else case when Z.FromType < 3 then @Delim else @DelimStep end + 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  + case C.Type/10000
	when 0 then @Delim + C.Number + @DelimNumber + C.Text
	when 1 then @Delim + C.Number + @DelimNumber + C.Text
	else case when Path like '%.%' then @DelimStep else @Delim end + 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 '','','RCP',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
exec vesp_SearchItemAndChildren '','','condenser not av',0,0,0,0
exec vesp_SearchItemAndChildren '','','(SG OR LHSI) AND DISPATCH',2,0,0,0
exec vesp_SearchItemAndChildren '','','DISPATCH NEAR SG',2,0,0,0
exec vesp_SearchItemAndChildren '','','PORV NEAR SG',2,0,0,0
exec vesp_SearchItemAndChildren '','','CHECK NORMAL',0,0,0,0
*/
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)
Declare @DelimStep char(1)
Set @DelimStep='.'
Declare @SearchString2 nvarchar(200)
Set @SearchString2 = case when @CaseSensitive = 2 then Cast(@SearchString as nvarchar(200)) else 'IGNORE_FULL_TEXT_SEARCH' end
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 @DelimStep + 'Caution'
	when 4 then @DelimStep + 'Note'
  else '' end
	else '' end
	PPath,
	Path + case C.Type/10000
	when 0 then @Delim +C.Number + @DelimNumber + C.Text
	when 1 then @Delim +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 @DelimStep +'Caution' + @DelimStep + cast(1 as varchar(3))
	when 4 then @DelimStep +'Note' + @DelimStep + cast(1 as varchar(3))
	when 5 then @DelimStep +'RNO' + @DelimStep
	when 7 then @DelimStep +'Table' + @DelimStep
	else case when Z.FromType < 3 then @Delim else @DelimStep end + 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  + case C.Type/10000
	when 0 then @Delim + C.Number + @DelimNumber + C.Text
	when 1 then @Delim + C.Number + @DelimNumber + C.Text
	else case when Path like '%.%' then @DelimStep else @Delim end + 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 = 2 AND CONTAINS(C.text, @SearchString2))
    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)
    OR (@CaseSensitive = 2 AND CONTAINS(D.DocAscii, @SearchString2))
	)
  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 [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_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 [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 [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 [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',Null,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)
Declare @DelimStep char(1)
Set @DelimStep='.'
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 @DelimStep + 'Caution'
	when 4 then @DelimStep + 'Note'
  else '' end
	else '' end
	PPath,
	--Path + case when Z.FromType < 3 then @Delim else @DelimStep end + case C.Type/10000
	Path + case C.Type/10000
	when 0 then @Delim +C.Number + @DelimNumber + C.Text
	when 1 then @Delim +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 @DelimStep +'Caution' + @DelimStep + cast(1 as varchar(3))
	when 4 then @DelimStep +'Note' + @DelimStep + cast(1 as varchar(3))
	when 5 then @DelimStep +'RNO' + @DelimStep
	when 7 then @DelimStep +'Table' + @DelimStep
	else case when Z.FromType < 3 then @Delim else @DelimStep end + 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  + case C.Type/10000
	when 0 then @Delim + C.Number + @DelimNumber + C.Text
	when 1 then @Delim + C.Number + @DelimNumber + C.Text
	else case when Path like '%.%' then @DelimStep else @Delim end + 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_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_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_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_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

/****** 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 [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 '','','RCP',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
exec vesp_SearchItemAndChildren '','','condenser not av',0,0,0,0
exec vesp_SearchItemAndChildren '','','(SG OR LHSI) AND DISPATCH',2,0,0,0
exec vesp_SearchItemAndChildren '','','DISPATCH NEAR SG',2,0,0,0
exec vesp_SearchItemAndChildren '','','PORV NEAR SG',2,0,0,0
exec vesp_SearchItemAndChildren '','','CHECK NORMAL',0,0,0,0
*/
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)
Declare @DelimStep char(1)
Set @DelimStep='.'
Declare @SearchString2 nvarchar(200)
Set @SearchString2 = case when @CaseSensitive = 2 then Cast(@SearchString as nvarchar(200)) else 'IGNORE_FULL_TEXT_SEARCH' end
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 @DelimStep + 'Caution'
	when 4 then @DelimStep + 'Note'
  else '' end
	else '' end
	PPath,
	Path + case C.Type/10000
	when 0 then @Delim +C.Number + @DelimNumber + C.Text
	when 1 then @Delim +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 @DelimStep +'Caution' + @DelimStep + cast(1 as varchar(3))
	when 4 then @DelimStep +'Note' + @DelimStep + cast(1 as varchar(3))
	when 5 then @DelimStep +'RNO' + @DelimStep
	when 7 then @DelimStep +'Table' + @DelimStep
	else case when Z.FromType < 3 then @Delim else @DelimStep end + 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  + case C.Type/10000
	when 0 then @Delim + C.Number + @DelimNumber + C.Text
	when 1 then @Delim + C.Number + @DelimNumber + C.Text
	else case when Path like '%.%' then @DelimStep else @Delim end + 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 = 2 AND CONTAINS(C.text, @SearchString2))
    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)
    OR (@CaseSensitive = 2 AND CONTAINS(D.DocAscii, @SearchString2))
	)
  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