SourceCode/PROMS/SQL/new stuff

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


/*
getContentAuditsChronologyByItemID 146,146,0
getContentAuditsChronologyByItemID 146,146,1
*/	
ALTER PROCEDURE [dbo].[getContentAuditsChronologyByItemID]
(
	@ProcedureItemID int,
	@SelectedItemID int,
	@IncludeDeletedChildren int
)

WITH EXECUTE AS OWNER
AS
begin
	select [AuditID],[ContentID],[Number],[Text],[Type],[FormatID],[Config],[DTS],[UserID],[DeleteStatus],[DeleteDTS],[ActionWhat],[ActionWhen],[Path]
	from
	(
	select
	case
	when lastauditid is null then 'Added'
	when r.deletestatus > 0 then 'Deleted'
	when lastauditid = -1 then 'Changed'
	when DeletedAuditID is not null then 'Restored'
	else 'Changed'
	end actionwhat
	,case
	when lastauditid is null then dts
	when r.deletestatus > 0 then deletedts
	when lastauditid = -1 then dts
	when DeletedAuditID is not null then deletedts
	else dts
	end actionwhen
	,* 
	from vefn_tblchilditems (@ProcedureItemID,@SelectedItemID,@IncludeDeletedChildren) t
	inner join vefn_chronologyreport() r
	on t.icontentid = r.contentid
	where dts > procdts
	) ah
	order by contentid,actionwhen
	RETURN
end
go
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go



	
/*
select * from [vefn_tblChildItems] (146,146,0) order by ordinalpath
*/
ALTER  FUNCTION [dbo].[vefn_tblChildItems](@ProcItemID int, @ItemID int, @IncludeDeletedChildren int)
RETURNS @Children TABLE
(
  ItemID int PRIMARY KEY,
	IContentID int,
	IDeleteStatus int,
	ProcDTS datetime,
	Path nvarchar(max),
	OrdinalPath nvarchar(max)
)
WITH EXECUTE AS OWNER
AS
BEGIN
declare @path nvarchar(max)
declare @ppath nvarchar(max)
declare @ordinalpath nvarchar(max)
declare @pordinalpath nvarchar(max)
declare @ordinal int
declare @pitemid int
select @path = path,@ordinalpath = ordinalpath,@ordinal = ordinal,@pitemid = parentid from vefn_siblingandchildrenitems(null) where itemid = @ItemID
if @pitemid = 0 begin
	set @pordinalpath = ''
	set @ppath = ''
end	
else begin
	select @ppath = path,@pordinalpath = ordinalpath from vefn_siblingandchildrenitems(null) where itemid = @pitemID
end
declare @procdts datetime
select @procdts = dts from items where itemid = @ProcItemID
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([Level], [ItemID], [ContentID], DeleteStatus, ProcDTS, PPath, [Path], [POrdinalPath], [OrdinalPath], [FromType], [Ordinal]) as (
  Select 0 [Level], [ItemID], I.[ContentID], I.DeleteStatus, @procdts procdts,
		@ppath [PPath],
		@path [Path],
		@pordinalpath [POrdinalPath], 
		@ordinalpath [OrdinalPath],
0 [FromType],@ordinal [Ordinal]
	FROM [tblItems] I
--inner join tblContents C on C.ContentID=I.ContentID
    where I.[ItemID]=@ItemID
Union All
-- Children
  select [Level] + 1, I.[ItemID], I.[ContentID], I.DeleteStatus, @procdts procdts,
	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 + 
case when I.deletestatus != 0 then 'Old ' else '' end + cast(1 as varchar(3))
	end end Path,
	OrdinalPath + '.' + Right('000' + Cast(P.FromType as varchar(4)), 4) + '-' ,
	OrdinalPath + '.' + Right('000' + Cast(P.FromType as varchar(4)), 4) + '-00001',
P.[FromType],0
	from Itemz Z
	join tblParts P on P.ContentID = Z.ContentID
	join tblItems I on I.ItemID = P.ItemID
inner join tblContents C on C.ContentID=I.ContentID
	where @IncludeDeletedChildren = 1 or z.DeleteStatus = 0
-- Siblings
Union All
  select [Level] , I.[ItemID], I.[ContentID], I.DeleteStatus, @procdts procdts,
	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 + 
case when I.deletestatus != 0 then 'Old ' else '' end + cast(Ordinal + 2 as varchar(3))
	end Path,
	POrdinalPath,
	POrdinalPath + right('00000' + cast(Z.[Ordinal] + 2 as varchar(5)), 5),
FromType,Z.[Ordinal] +1
	from Itemz Z
	join tblItems I on I.PreviousID = Z.ItemID
inner join tblContents C on C.ContentID=I.ContentID
	where Z.[Level] > 0
)
insert into @Children select ItemID, ContentID, DeleteStatus, ProcDTS, Path, OrdinalPath from Itemz
RETURN
END
end


go

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


/*
getContentAuditsChronologyByItemID 146,146,0
getContentAuditsChronologyByItemID 146,146,1
*/	
ALTER PROCEDURE [dbo].[getContentAuditsChronologyByItemID]
(
	@ProcedureItemID int,
	@SelectedItemID int,
	@IncludeDeletedChildren int
)

WITH EXECUTE AS OWNER
AS
begin
	select [AuditID],[ContentID],[Number],[Text],[Type],[FormatID],[Config],[DTS],[UserID],[DeleteStatus],[DeleteDTS],[ActionWhat],[ActionWhen]
	from
	(
	select
	case
	when lastauditid is null then 'Added'
	when r.deletestatus > 0 then 'Deleted'
	when lastauditid = -1 then 'Changed'
	when DeletedAuditID is not null then 'Restored'
	else 'Changed'
	end actionwhat
	,case
	when lastauditid is null then dts
	when r.deletestatus > 0 then deletedts
	when lastauditid = -1 then dts
	when DeletedAuditID is not null then deletedts
	else dts
	end actionwhen
	,* 
	from vefn_tblchilditems (@ProcedureItemID,@SelectedItemID,@IncludeDeletedChildren) t
	inner join vefn_chronologyreport() r
	on t.icontentid = r.contentid
	where dts > procdts
	) ah
	order by contentid,actionwhen
	RETURN
end
go