/****** Object:  StoredProcedure [dbo].[getTransitionsFromProc]    Script Date: 10/21/2011 15:19:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

/*
getTransitionsFromProc 10
*/
create PROCEDURE [dbo].[getTransitionsFromProc]
(
	@ItemID int
)

WITH EXECUTE AS OWNER
AS
SELECT tt.[TransitionID]
      ,[FromID]
      ,[ToID]
      ,[RangeID]
      ,[IsRange]
      ,[TranType]
      ,tt.[Config]
      ,tt.[DTS]
      ,tt.[UserID]
      ,tt.[LastChanged]
	  ,(SELECT COUNT(*) FROM [ZTransitions] WHERE [ZTransitions].[TransitionID]=[tt].[TransitionID]) [ZTransitionCount]
,cc.text ContentText
	FROM [Transitions] tt
inner join contents cc on tt.fromid = cc.contentid
	WHERE transitionid in (select transitionid from vefn_FindExternalFromTransitions(@ItemID))
	RETURN

/****** Object:  StoredProcedure [dbo].[getTransitionsToProc]    Script Date: 10/21/2011 15:24:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

/*
getTransitionsToProc 10
*/
CREATE PROCEDURE [dbo].[getTransitionsToProc]
(
	@ItemID int
)

WITH EXECUTE AS OWNER
AS
SELECT tt.[TransitionID]
      ,[FromID]
      ,[ToID]
      ,[RangeID]
      ,[IsRange]
      ,[TranType]
      ,tt.[Config]
      ,tt.[DTS]
      ,tt.[UserID]
      ,tt.[LastChanged]
	  ,(SELECT COUNT(*) FROM [ZTransitions] WHERE [ZTransitions].[TransitionID]=[tt].[TransitionID]) [ZTransitionCount]
,cc.text ContentText
	FROM [Transitions] tt
inner join contents cc on tt.fromid = cc.contentid
	WHERE transitionid in (select transitionid from vefn_FindExternalTransitions(@ItemID))
	RETURN

/****** Object:  UserDefinedFunction [dbo].[vefn_FindExternalFromTransitions]    Script Date: 10/21/2011 15:27:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
	
/*
Select * from vefn_FindExternalFromTransitions(185)
*/

create  FUNCTION [dbo].[vefn_FindExternalFromTransitions](@ItemID int)
RETURNS @Children TABLE
(
  [FromItemID] int,
	[TransitionID] [int] NOT NULL,
	[FromID] [int] NOT NULL,
	[ToID] [int] NOT NULL,
	[RangeID] [int] NOT NULL,
	[Config] [nvarchar](max) NULL
)
WITH EXECUTE AS OWNER
AS
BEGIN
with Itemz([Level], [Ordinal], [ItemID], [PreviousID], [FromType], [ContentID], [DTS], [UserID], [LastChanged],[pContentID], [pDTS],[pUserID],[pLastChanged]) as (
  Select 0 [Level], 0 [Ordinal], [ItemID], [PreviousID],0 [FromType],[ContentID],[DTS],[UserID],[LastChanged]
	,0 [pContentID],[DTS] [pDTS], [UserID] [pUserID], [LastChanged] [pLastChanged]
	FROM [Items]
    where [ItemID]=@ItemID
Union All
-- Children
  select [Level] + 1,0, I.[ItemID], I.[PreviousID], P.[FromType],I.[ContentID],I.[DTS],I.[UserID],I.[LastChanged]
	,P.[ContentID] [pContentID],P.[DTS] [pDTS],P.[UserID] [pUserID],P.[LastChanged] [pLastChanged]
	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.[Ordinal] +1, I.[ItemID], I.[PreviousID], [FromType],I.[ContentID],I.[DTS],I.[UserID],I.[LastChanged]
	,null,null,null,null
	from Itemz Z
	join Items I on I.PreviousID = Z.ItemID
	where Z.[Level] > 0
)
insert into @Children
select ItemID [FromItemID], TT.[TransitionID],	TT.[FromID],	TT.[ToID],	TT.[RangeID],	TT.[Config]
 from Transitions TT
 join Items II on II.ContentID=TT.FromID
where (ToID not in(select ItemID from Itemz) OR RangeID not in(select ItemID from Itemz))
AND FromID in(Select ContentID from ItemZ)
RETURN
END
GO

/****** Object:  StoredProcedure [dbo].[getLibDocsForProc]    Script Date: 10/21/2011 15:28:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

/*
getLibDocsForProc 10
*/
CREATE PROCEDURE [dbo].[getLibDocsForProc]
(
	@ItemID int
)

WITH EXECUTE AS OWNER
AS
SELECT dd.[DocID]
      ,[LibTitle]
      ,[DocContent]
      ,[DocAscii]
      ,[Config]
      ,dd.[DTS]
      ,dd.[UserID]
      ,dd.[LastChanged]
      ,[FileExtension],
		(SELECT COUNT(*) FROM [DROUsages] WHERE [DROUsages].[DocID]=[dd].[DocID]) [DROUsageCount],
		(SELECT COUNT(*) FROM [Entries] WHERE [Entries].[DocID]=[dd].[DocID]) [EntryCount],
		(SELECT COUNT(*) FROM [Pdfs] WHERE [Pdfs].[DocID]=[dd].[DocID]) [PdfCount]

	FROM [Documents] dd
	INNER JOIN [Entries] ee on dd.docid = ee.docid
	INNER JOIN vefn_ChildItems(@ItemID) cc on cc.contentid = ee.contentid
	RETURN

/****** Object:  StoredProcedure [dbo].[getRoUsagesForProc]    Script Date: 10/21/2011 15:31:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
	
create PROCEDURE [dbo].[getRoUsagesForProc]
(
	@ItemID int
)

WITH EXECUTE AS OWNER
AS
	SELECT
		[ROUsageID],
		rr.[ContentID],
		[ROID],
		[Config],
		[DTS],
		[UserID],
		[LastChanged],
		[RODbID]
	FROM [RoUsages] rr
	INNER JOIN vefn_ChildItems(@ItemID) cc on cc.contentid = rr.contentid
	RETURN

/****** Object:  UserDefinedFunction [dbo].[vefn_CheckXML]    Script Date: 10/25/2011 18:36:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
declare @NewXML xml
select @NewXML = consistencychecks from checks where checkid = 48
set @NewXML = cast(replace(cast(@NewXML as varchar(max)),'2011-08-24T11:20:57.027','2011-12-25T00:00:00.000') as xml)
set @NewXML = cast(replace(cast(@NewXML as varchar(max)),'88%','88.12345%') as xml)
set @NewXML = cast(replace(cast(@NewXML as varchar(max)),'="6%','="6.12345%') as xml)
set @NewXML = cast(replace(cast(@NewXML as varchar(max)),'23%','23.12345%') as xml)
set @NewXML = cast(replace(cast(@NewXML as varchar(max)),'90%','90.12345%') as xml)
set @NewXML = cast(replace(cast(@NewXML as varchar(max)),', Step 13',', Step 14') as xml)r
declare @OldXML xml
select @OldXML = consistencychecks from checks where checkid = 1
--select .dbo.vefn_CheckXML(@NewXML, @OldXML)
select ii.*,ss.*,.dbo.vefn_CheckXML(@NewXML, consistencychecks) chkxml from checks cc 
inner join revisions rr on cc.revisionid = rr.revisionid 
inner join items ii on rr.itemid = ii.itemid
inner join stages ss on cc.stageid = ss.stageid
*/
CREATE function [dbo].[vefn_CheckXML](@NewXML xml, @OldXML xml)
returns xml
begin
declare @ChkXML xml
set @ChkXML = 
(
select
(
select * from
(
	select ah.ROID,OldROValue,ROValue from
	(
		select r1.value('@ROID','varchar(20)') roid,r1.value('@ROValue','varchar(max)') oldrovalue
		from @OldXML.nodes('//ROCheck') as t1(r1)
	) ah
	inner join
	(
		select distinct r2.value('@ROID','varchar(20)') roid,r2.value('@ROValue','varchar(max)') rovalue
		from @NewXML.nodes('//ROCheck') as t2(r2)
	) ds
	on ah.roid = ds.roid
	and oldrovalue != rovalue
) ROCheck
for xml auto,root('ROChecks'),type
)
	,
	(
select * from
(
		select ah.TransitionID,OldTransitionValue,TransitionValue from
		(
			select r1.value('@TransitionID','int') TransitionID,r1.value('@TransitionValue','varchar(max)') OldTransitionValue
			from @OldXML.nodes('//TransitionCheck') as t1(r1)
		) ah
		inner join
		(
			select distinct r2.value('@TransitionID','int') TransitionID,r2.value('@TransitionValue','varchar(max)') TransitionValue
			from @NewXML.nodes('//TransitionCheck') as t2(r2)
		) ds
		on ah.TransitionID = ds.TransitionID
		and OldTransitionValue != TransitionValue
) TransitionCheck
	  for xml auto,root('TransitionChecks'),type
)
	,
	(
select * from
(
		select ah.DocID,OldDocDate,DocDate from
		(
			select r1.value('@DocID','int') docid,r1.value('@DocDate','datetime') olddocdate
			from @OldXML.nodes('//LibDocCheck') as t1(r1)
		) ah
		inner join
		(
			select distinct r2.value('@DocID','int') docid,r2.value('@DocDate','datetime') docdate
			from @NewXML.nodes('//LibDocCheck') as t2(r2)
		) ds
		on ah.docid = ds.docid
		and olddocdate != docdate
) LibDocCheck
	  for xml auto,root('LibDocChecks'),type
)
for xml path(''),ROOT ('ConsistencyChecks'),type
)
return @ChkXML
end
go

/****** Object:  UserDefinedFunction [dbo].[vefn_CheckAllXML]    Script Date: 10/21/2011 15:00:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
select [dbo].[vefn_CheckAllXML](1)
*/
CREATE function [dbo].[vefn_CheckAllXML](@DocVersionID int)
returns xml
begin
	declare @ChkXML xml
	set @ChkXML = 
	(
		select
		(--ro inconsistencies accross set
			select ItemID,ROID,ROValue from
			(
				select rr.itemid,r1.value('@ROID','varchar(max)') roid,r1.value('@ROValue','varchar(max)') rovalue
				from checks cc
				inner join revisions rr on cc.revisionid = rr.revisionid
				cross apply cc.consistencychecks.nodes('//ROCheck') t1(r1)
				where cc.checkid in
				--latest checkid with approved stage for each itemid
				(
					select max(cc.checkid) checkid 
					from checks cc 
					inner join stages ss on cc.stageid = ss.stageid 
					inner join revisions rr on cc.revisionid = rr.revisionid 
					where ss.isapproved = 1 
					group by rr.itemid
				)
				and rr.itemid in (select itemid from vefn_siblingitems((select itemid from docversions where versionid = @docversionid),0))
			) ROCheck
			where roid in
			(
				--get roids that has more than 1 rovalue from distinct roid and rovalue from checks for latest checkid with approved stage for each itemid
				select roid from
				(
					--distinct roid and rovalue from checks for latest checkid with approved stage for each itemid
					select distinct r1.value('@ROID','varchar(max)') roid,r1.value('@ROValue','varchar(max)') rovalue
					from checks cc
					cross apply cc.consistencychecks.nodes('//ROCheck') t1(r1)
					where cc.checkid in
					--latest checkid with approved stage for each itemid
					(
						select max(cc.checkid) checkid 
						from checks cc 
						inner join stages ss on cc.stageid = ss.stageid 
						inner join revisions rr on cc.revisionid = rr.revisionid 
						where ss.isapproved = 1 
						group by rr.itemid
					)
				) t1
				group by roid having count(*) > 1
			) 
			order by itemid
			for xml auto,root('ROChecks'),type
		),
--jcb
		(--transition inconsistencies accross set
			select ItemID,TransitionID,TransitionValue from
			(
				select rr.itemid,r1.value('@TransitionID','int') transitionid,r1.value('@TransitionValue','varchar(max)') transitionvalue
				from checks cc
				inner join revisions rr on cc.revisionid = rr.revisionid
				cross apply cc.consistencychecks.nodes('//TransitionCheck') t1(r1)
				where cc.checkid in
				--latest checkid with approved stage for each itemid
				(
					select max(cc.checkid) checkid 
					from checks cc 
					inner join stages ss on cc.stageid = ss.stageid 
					inner join revisions rr on cc.revisionid = rr.revisionid 
					where ss.isapproved = 1 
					group by rr.itemid
				)
				and rr.itemid in (select itemid from vefn_siblingitems((select itemid from docversions where versionid = @docversionid),0))
			) TransitionCheck
			where transitionid in
			(
				--get transitionids that has more than 1 transitionvalue from distinct transitionid and transitionvalue from checks for latest checkid with approved stage for each itemid
				select transitionid from
				(
					--distinct docid and docdate from checks for latest checkid with approved stage for each itemid
					select distinct r1.value('@TransitionID','int') transitionid,r1.value('@TransitionValue','varchar(max)') transitionvalue
					from checks cc
					cross apply cc.consistencychecks.nodes('//TransitionCheck') t1(r1)
					where cc.checkid in
					--latest checkid with approved stage for each itemid
					(
						select max(cc.checkid) checkid 
						from checks cc 
						inner join stages ss on cc.stageid = ss.stageid 
						inner join revisions rr on cc.revisionid = rr.revisionid 
						where ss.isapproved = 1 
						group by rr.itemid
					)
				) t1
				group by transitionid having count(*) > 1
			)
			order by itemid
			for xml auto,root('TransitionChecks'),type
		),
--end jcb
		(--libdoc inconsistencies accross set
			select ItemID,DocID,DocDate from
			(
				select rr.itemid,r1.value('@DocID','int') docid,r1.value('@DocDate','datetime') docdate
				from checks cc
				inner join revisions rr on cc.revisionid = rr.revisionid
				cross apply cc.consistencychecks.nodes('//LibDocCheck') t1(r1)
				where cc.checkid in
				--latest checkid with approved stage for each itemid
				(
					select max(cc.checkid) checkid 
					from checks cc 
					inner join stages ss on cc.stageid = ss.stageid 
					inner join revisions rr on cc.revisionid = rr.revisionid 
					where ss.isapproved = 1 
					group by rr.itemid
				)
				and rr.itemid in (select itemid from vefn_siblingitems((select itemid from docversions where versionid = @docversionid),0))
			) LibDocCheck
			where docid in
			(
				--get docids that has more than 1 docdate from distinct docid and docdate from checks for latest checkid with approved stage for each itemid
				select docid from
				(
					--distinct docid and docdate from checks for latest checkid with approved stage for each itemid
					select distinct r1.value('@DocID','int') docid,r1.value('@DocDate','datetime') docdate
					from checks cc
					cross apply cc.consistencychecks.nodes('//LibDocCheck') t1(r1)
					where cc.checkid in
					--latest checkid with approved stage for each itemid
					(
						select max(cc.checkid) checkid 
						from checks cc 
						inner join stages ss on cc.stageid = ss.stageid 
						inner join revisions rr on cc.revisionid = rr.revisionid 
						where ss.isapproved = 1 
						group by rr.itemid
					)
				) t1
				group by docid having count(*) > 1
			)
			order by itemid
			for xml auto,root('LibDocChecks'),type
		)
		for xml path(''),ROOT ('ConsistencyChecks'),type
	)
	return @ChkXML
end
go

/****** Object:  UserDefinedFunction [dbo].[vefn_CheckAllXMLByItemID]    Script Date: 10/21/2011 15:01:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
declare @myxml xml
set @myxml = (select dbo.vefn_checkallxml(1))
select dbo.vefn_checkallxmlbyitemid(1,@myxml)
*/
CREATE function [dbo].[vefn_CheckAllXMLByItemID](@ItemID int,@MyXml xml)
returns xml
begin
	declare @ChkXML xml
	declare @ROCheck table
	(
		ItemID int,
		ROID varchar(max),
		ROValue varchar(max)
	)
	insert into @ROCheck
	select r2.value('@ItemID','int'),r2.value('@ROID','varchar(max)'),r2.value('@ROValue','varchar(max)')
	from @MyXml.nodes('//ROCheck') t2(r2)
--jcb
	declare @TransitionCheck table
	(
		ItemID int,
		TransitionID int,
		TransitionValue varchar(max)
	)
	insert into @TransitionCheck
	select r2.value('@ItemID','int'),r2.value('@TransitionID','int'),r2.value('@TransitionValue','varchar(max)')
	from @MyXml.nodes('//TransitionCheck') t2(r2)
--end jcb
	declare @LibDocCheck table
	(
		ItemID int,
		DocID int,
		DocDate datetime
	)
	insert into @LibDocCheck
	select r2.value('@ItemID','int'),r2.value('@DocID','int'),r2.value('@DocDate','datetime')
	from @MyXml.nodes('//LibDocCheck') t2(r2)
	set @ChkXML = 
	(
	select
	(select * from @ROCheck ROCheck where itemid = @ItemID for xml auto, root('ROChecks'),type),
	(select * from @TransitionCheck TransitionCheck where itemid = @ItemID for xml auto, root('TransitionChecks'),type),
	(select * from @LibDocCheck LibDocCheck where itemid = @ItemID for xml auto, root('LibDocChecks'),type)
	for xml path(''),ROOT ('ConsistencyChecks'),type
	)
	return @ChkXML
end
go

/****** Object:  StoredProcedure [dbo].[vesp_GetConsistencyCheckProcedures]    Script Date: 10/25/2011 18:38:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
declare @myxml xml
select @myxml = consistencychecks from checks where checkid = 2
declare @docversionid int
set @docversionid = 1
exec vesp_GetConsistencyCheckProcedures @docversionid, @myxml
*/
CREATE PROCEDURE [dbo].[vesp_GetConsistencyCheckProcedures]
(
	@DocVersionID int,
	@MyXml xml
)
AS BEGIN
--to be removed
--	set @myxml = cast(replace(cast(@myxml as varchar(max)),'2011-08-24T11:20:57.027','2011-12-25T00:00:00.000') as xml)
--	set @myxml = cast(replace(cast(@myxml as varchar(max)),'88%','88.12345%') as xml)
--	set @myxml = cast(replace(cast(@myxml as varchar(max)),'="6%','="6.12345%') as xml)
--	set @myxml = cast(replace(cast(@myxml as varchar(max)),'23%','23.12345%') as xml)
--	set @myxml = cast(replace(cast(@myxml as varchar(max)),'90%','90.12345%') as xml)
--	set @myxml = cast(replace(cast(@myxml as varchar(max)),', Step 13',', Step 14') as xml)
--end to be removed
	DECLARE @Items TABLE
	(
		ID int identity(1,1),
		ItemID int
	)
	INSERT INTO @Items (itemid)
	select roc.value('@ItemID','int') itemid
	from @myxml.nodes('//ConsistencyChecks') t1(roc)
	declare @LatestApproved TABLE
	(
		ItemID int PRIMARY KEY,
		CheckID int
	)
	insert into @LatestApproved
	select rr.itemid,max(checkid) checkid
	from revisions rr
	inner join checks cc on rr.revisionid = cc.revisionid
	inner join stages ss on cc.stageid = ss.stageid
	where ss.isapproved = 1
	and rr.itemid in
	(select itemid from vefn_siblingitems((select itemid from docversions where versionid = @docversionid),0))
	group by rr.itemid

	SELECT
	ii.[ItemID]
	,ii.[PreviousID]
	,ii.[ContentID]
	,ii.[DTS]
	,ii.[UserID]
	,ii.[LastChanged]
	,CC.[Number]
	,CC.[Text]
	,CC.[Type]
	,CC.[FormatID]
	,CC.[Config]
	,CC.[DTS] [cDTS]
	,CC.[UserID] [cUserID]
	,CC.[LastChanged] [cLastChanged]
	,(SELECT COUNT(*) FROM [Annotations] WHERE [Annotations].[ItemID]=ii.[ItemID]) [AnnotationCount]
	,(SELECT COUNT(*) FROM [DocVersions] WHERE [DocVersions].[ItemID]=ii.[ItemID]) [DocVersionCount]
	,(SELECT COUNT(*) FROM [Items] [Next] WHERE [Next].[PreviousID]=ii.[ItemID]) [NextCount]
	,(SELECT COUNT(*) FROM [Parts] WHERE [Parts].[ItemID]=ii.[ItemID]) [PartCount]
	,(SELECT COUNT(*) FROM [Transitions] WHERE [Transitions].[RangeID]=ii.[ItemID]) [Transition_RangeIDCount]
	,(SELECT COUNT(*) FROM [Transitions] WHERE [Transitions].[ToID]=ii.[ItemID]) [Transition_ToIDCount]
	,(SELECT COUNT(*) FROM [Details] WHERE [Details].[ContentID]=CC.[ContentID]) [DetailCount]
	,(SELECT COUNT(*) FROM [Entries] WHERE [Entries].[ContentID]=CC.[ContentID]) [EntryCount]
	,(SELECT COUNT(*) FROM [Grids] WHERE [Grids].[ContentID]=CC.[ContentID]) [GridCount]
	,(SELECT COUNT(*) FROM [Images] WHERE [Images].[ContentID]=CC.[ContentID]) [ImageCount]
	,(SELECT COUNT(*) FROM [Items] WHERE [Items].[ContentID]=CC.[ContentID]) [ItemCount]
	,(SELECT COUNT(*) FROM [Parts] WHERE [Parts].[ContentID]=CC.[ContentID]) [cPartCount]
	,(SELECT COUNT(*) FROM [RoUsages] WHERE [RoUsages].[ContentID]=CC.[ContentID]) [RoUsageCount]
	,(SELECT COUNT(*) FROM [Transitions] WHERE [Transitions].[FromID]=CC.[ContentID]) [TransitionCount]
	,(SELECT COUNT(*) FROM [ZContents] WHERE [ZContents].[ContentID]=CC.[ContentID]) [ZContentCount]
	,ribeye.ChkXml 
	FROM [Items] ii
	INNER JOIN [Contents] cc ON ii.[ContentID] = cc.[ContentID]
	inner join
	(
	select la.itemid,.dbo.vefn_checkxml(@myxml,cc.consistencychecks) chkxml
	from checks cc
	inner join @LatestApproved la on cc.checkid = la.checkid
	) ribeye on ii.itemid = ribeye.itemid
left join @items iii on ii.itemid = iii.itemid
	where chkxml.exist('//ROChecks') | chkxml.exist('//TransitionChecks') | chkxml.exist('//LibDocChecks') > 0 or
	ii.ItemID in (select ItemID from @Items)
	order by isnull(iii.id,999999)
END
GO

/****** Object:  StoredProcedure [dbo].[vesp_GetAllConsistencyIssues]    Script Date: 10/21/2011 15:03:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
[dbo].[vesp_GetAllConsistencyIssues] 1

*/
CREATE PROCEDURE [dbo].[vesp_GetAllConsistencyIssues]
(
	@DocVersionID int
)
AS BEGIN
	declare @myxml xml
	set @myxml = (select dbo.vefn_checkallxml(@DocVersionID))
	DECLARE @Items TABLE
	(
		ItemID int PRIMARY KEY
	)
	INSERT INTO @Items
	select r1.value('@ItemID','int') itemid
	from @myxml.nodes('//ROCheck') t1(r1)
	union
	select r1.value('@ItemID','int') itemid
	from @myxml.nodes('//TransitionCheck') t1(r1)
	union
	select r1.value('@ItemID','int') itemid
	from @myxml.nodes('//LibDocCheck') t1(r1)
	SELECT
	ii.[ItemID]
	,ii.[PreviousID]
	,ii.[ContentID]
	,ii.[DTS]
	,ii.[UserID]
	,ii.[LastChanged]
	,CC.[Number]
	,CC.[Text]
	,CC.[Type]
	,CC.[FormatID]
	,CC.[Config]
	,CC.[DTS] [cDTS]
	,CC.[UserID] [cUserID]
	,CC.[LastChanged] [cLastChanged]
	,(SELECT COUNT(*) FROM [Annotations] WHERE [Annotations].[ItemID]=ii.[ItemID]) [AnnotationCount]
	,(SELECT COUNT(*) FROM [DocVersions] WHERE [DocVersions].[ItemID]=ii.[ItemID]) [DocVersionCount]
	,(SELECT COUNT(*) FROM [Items] [Next] WHERE [Next].[PreviousID]=ii.[ItemID]) [NextCount]
	,(SELECT COUNT(*) FROM [Parts] WHERE [Parts].[ItemID]=ii.[ItemID]) [PartCount]
	,(SELECT COUNT(*) FROM [Transitions] WHERE [Transitions].[RangeID]=ii.[ItemID]) [Transition_RangeIDCount]
	,(SELECT COUNT(*) FROM [Transitions] WHERE [Transitions].[ToID]=ii.[ItemID]) [Transition_ToIDCount]
	,(SELECT COUNT(*) FROM [Details] WHERE [Details].[ContentID]=CC.[ContentID]) [DetailCount]
	,(SELECT COUNT(*) FROM [Entries] WHERE [Entries].[ContentID]=CC.[ContentID]) [EntryCount]
	,(SELECT COUNT(*) FROM [Grids] WHERE [Grids].[ContentID]=CC.[ContentID]) [GridCount]
	,(SELECT COUNT(*) FROM [Images] WHERE [Images].[ContentID]=CC.[ContentID]) [ImageCount]
	,(SELECT COUNT(*) FROM [Items] WHERE [Items].[ContentID]=CC.[ContentID]) [ItemCount]
	,(SELECT COUNT(*) FROM [Parts] WHERE [Parts].[ContentID]=CC.[ContentID]) [cPartCount]
	,(SELECT COUNT(*) FROM [RoUsages] WHERE [RoUsages].[ContentID]=CC.[ContentID]) [RoUsageCount]
	,(SELECT COUNT(*) FROM [Transitions] WHERE [Transitions].[FromID]=CC.[ContentID]) [TransitionCount]
	,(SELECT COUNT(*) FROM [ZContents] WHERE [ZContents].[ContentID]=CC.[ContentID]) [ZContentCount]
	,(select dbo.vefn_CheckAllXMLByItemID(ii.itemid,@myxml)) ChkXml 
	FROM [Items] ii
	INNER JOIN [Contents] cc ON ii.[ContentID] = cc.[ContentID]
	where ii.ItemID in (select ItemID from @Items)
--	order by ii.itemid
END
go

/****** Object:  StoredProcedure [dbo].[getRevisionByItemIDandRevisionNumber]    Script Date: 11/23/2011 15:54:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
	
CREATE PROCEDURE [dbo].[getRevisionByItemIDandRevisionNumber]

(
	@ItemID int,
	@RevisionNumber nvarchar(50)
)
WITH EXECUTE AS OWNER
AS
declare @RevisionID int
set @RevisionID = (select revisionid from revisions where itemid = @itemid and revisionnumber = @RevisionNumber)
	SELECT
		[RevisionID],
		[ItemID],
		[TypeID],
		[RevisionNumber],
		[RevisionDate],
		[Notes],
		[Config],
		[DTS],
		[UserID],
		[LastChanged],
		(SELECT COUNT(*) FROM [Checks] WHERE [Checks].[RevisionID]=[Revisions].[RevisionID]) [CheckCount],
		(SELECT COUNT(*) FROM [Versions] WHERE [Versions].[RevisionID]=[Revisions].[RevisionID]) [VersionCount]
	FROM [Revisions]
	WHERE [RevisionID]=@RevisionID

	SELECT
		[Checks].[CheckID],
		[Checks].[RevisionID],
		[Checks].[StageID],
		[Checks].[ConsistencyChecks],
		[Checks].[DTS],
		[Checks].[UserID],
		[Checks].[LastChanged],
		[Stages].[Name] [Stage_Name],
		[Stages].[Description] [Stage_Description],
		[Stages].[IsApproved] [Stage_IsApproved],
		[Stages].[DTS] [Stage_DTS],
		[Stages].[UserID] [Stage_UserID]
	FROM [Checks]
		JOIN [Stages]  ON
			[Stages].[StageID]=[Checks].[StageID]
	WHERE
		[Checks].[RevisionID]=@RevisionID


	SELECT
		[Versions].[VersionID],
		[Versions].[RevisionID],
		[Versions].[StageID],
		[Versions].[DTS],
		[Versions].[UserID],
		[Versions].[PDF],
		[Versions].[SummaryPDF],
		[Stages].[Name] [Stage_Name],
		[Stages].[Description] [Stage_Description],
		[Stages].[IsApproved] [Stage_IsApproved],
		[Stages].[DTS] [Stage_DTS],
		[Stages].[UserID] [Stage_UserID]
	FROM [Versions]
		JOIN [Stages]  ON
			[Stages].[StageID]=[Versions].[StageID]
	WHERE
		[Versions].[RevisionID]=@RevisionID

	RETURN