/****** Object:  StoredProcedure [dbo].[getContentAuditsSummaryByItemIDandUnitID]    Script Date: 10/10/2012 12:56:01 ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getContentAuditsSummaryByItemIDandUnitID]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
	DROP PROCEDURE [getContentAuditsSummaryByItemIDandUnitID];
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
	
/****** Object:  StoredProcedure [getContentAuditsSummaryByItemIDandUnitID] ******/
/*
getContentAuditsSummaryByItemIDandUnitID 146,146,0,1
getContentAuditsSummaryByItemIDandUnitID 42,42,0,1
getContentAuditsSummaryByItemIDandUnitID 1,1,0,1
getContentAuditsSummaryByItemIDandUnitID 146,146,1,1
*/	
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2012 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
create PROCEDURE [dbo].[getContentAuditsSummaryByItemIDandUnitID]
(
	@ProcedureItemID int,
	@SelectedItemID int,
	@IncludeDeletedChildren int,
	@UnitID int,
	@DTS datetime
)

WITH EXECUTE AS OWNER
AS
begin
DECLARE @ProcId Int
Set @ProcID = @ProcedureItemID
DECLARE @Apples TABLE
(
ProcID int,
ItemId int,
ContentID int Primary Key,
Apple nvarchar(255),
DerApple nvarchar(255)
)
--Build Applicabilty table for the specified Procedure ID
BEGIN
	with Itemz([Level],[Apple],[DerApple],[ParApple],[ItemID], [ContentID]) as 
	(Select  0
	,xMasterSlave.value('@Applicability','nvarchar(255)') Apple
	,IsNull(xMasterSlave.value('@Applicability','nvarchar(255)'),dbo.ve_getItemDerivedApplicability(@ProcID))  DerApple
	,dbo.ve_getItemDerivedApplicability(@ProcID)  ParApple
	,[II].[ItemID], [II].[ContentID]
		FROM [Items] II
		JOIN (Select ContentID, cast(config as XML) xConfig FROM Contents) CC ON II.ContentID = CC.ContentID
		outer apply xConfig.nodes('//MasterSlave') tMasterSlave(xMasterSlave)
		Where  II.[ItemID] = @ProcID
	Union All
	-- Children
		select ZZ.Level + 1 Level
		,xMasterSlave.value('@Applicability','nvarchar(255)') Apple
		,IsNull(xMasterSlave.value('@Applicability','nvarchar(255)'),ZZ.DerApple) DerApple
		,ZZ.DerApple ParApple
		, II.[ItemID], II.[ContentID]
		from Itemz ZZ
		join Parts PP on PP.ContentID = ZZ.ContentID
		join Items II on II.ItemID = PP.ItemID
		JOIN (Select ContentID, cast(config as XML) xConfig FROM Contents) CC ON II.ContentID = CC.ContentID
		outer apply xConfig.nodes('//MasterSlave') tMasterSlave(xMasterSlave)
	Union All
	-- Siblings
		select ZZ.Level
			,xMasterSlave.value('@Applicability','nvarchar(255)') Apple
		,IsNull(xMasterSlave.value('@Applicability','nvarchar(255)'),ZZ.ParApple) DerApple
		,ZZ.ParApple
		, II.[ItemID], II.[ContentID]
		from Itemz ZZ
		join Items II on II.PreviousID = ZZ.ItemID
		JOIN (Select ContentID, cast(config as XML) xConfig FROM Contents) CC ON II.ContentID = CC.ContentID
		outer apply xConfig.nodes('//MasterSlave') tMasterSlave(xMasterSlave)
		Where Level >= 1
	)
	insert into @Apples
	select @ProcID ProcID, ItemID, ContentID, Apple, DerApple--, [ItemID], [ContentID]
	 from ItemZ II
	OPTION (MAXRECURSION 10000)
END


DECLARE @Chrono TABLE
(
[AuditID] bigint,
[ContentID] int,
[Number] nvarchar(max),
[Text] nvarchar(max),
[Type] int,
[FormatID] int,
[Config] nvarchar(max),
[DTS] datetime,
[UserID] nvarchar(max),
[DeleteStatus] int,
[ActionDTS] datetime,
[ActionWhat] nvarchar(max),
[ActionWhen] datetime,
[Path] nvarchar(max),
ItemID int,
TypeName nvarchar(max),
ordinalpath nvarchar(max)
)

-- Use the Applicability Table to limit Items included in the list of changes
insert into @Chrono
select Distinct VC.* from vefn_GetMyChronology(@ProcedureItemID,@SelectedItemID,@IncludeDeletedChildren,@DTS) VC
Left JOIN @Apples AA ON AA.ContentID = VC.ContentID
Where  aa.ContentID is null or AA.DerApple = '-1' or ',' + AA.DerApple + ',' like '%,' + cast(@UnitID as varchar(10)) + ',%'

Declare @Audits TABLE
(
AuditID int primary key
)
-- Create a Unique list of Minimum and Maximum AuditIDs for Each ContentID
insert into @Audits
select min(auditID) from @Chrono group by contentid UNION select max(auditID) from @Chrono group by contentid
-- Return the List Of Changes with the first Audit Record and the Last Audit Record
select * from @Chrono
where AuditID is null OR AuditID in (select AuditID from @Audits)
order by OrdinalPath, contentid,auditid

	RETURN
end
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getContentAuditsSummaryByItemIDandUnitID Succeeded'
ELSE PRINT 'Procedure Creation: getContentAuditsSummaryByItemIDandUnitID Error on Creation'
GO