IF  NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(N'[VCLib].[T_ENTITIES]') AND name = N'LeftIndex')
ALTER TABLE [VCLib].[T_ENTITIES] ADD [LeftIndex] int NULL;
IF  NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(N'[VCLib].[T_ENTITIES]') AND name = N'RightIndex')
ALTER TABLE [VCLib].[T_ENTITIES] ADD [RightIndex] int NULL;
IF  NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(N'[VCLib].[T_ENTITIES]') AND name = N'TreeLevel')
ALTER TABLE [VCLib].[T_ENTITIES] ADD [TreeLevel] int NULL;
GO
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[VCLib].[T_ENTITIES]') AND name = N'IX_T_ENTITIES_RightIndex')
CREATE NONCLUSTERED INDEX [IX_T_ENTITIES_RightIndex] ON [VCLib].[T_ENTITIES] ([RightIndex] ASC) INCLUDE([EntityID]);
GO
--EXEC [dbo].[RecalculateENTITIES]
--SELECT t0.EntityID, t0.EntName, t0.LeftIndex, t0.RightIndex, t0.TreeLevel FROM VCLib.T_ENTITIES t0 WHERE t0.EntName='DIAGNOSTICS_RESULTS';
--SELECT t0.EntityID, t0.EntName, t0.LeftIndex, t0.RightIndex, t0.TreeLevel FROM VCLib.T_ENTITIES t0 
--WHERE ( EXISTS (SELECT * FROM VCLib.T_ENTITIES t3  WHERE t3.EntName='DIAGNOSTICS_RESULTS' AND t0.LeftIndex>=t3.LeftIndex AND t0.RightIndex<=t3.RightIndex));

----Добавление записей:
--IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[VCLib].[ENTITIES_AfterInsert]'))
--DROP TRIGGER [VCLib].[ENTITIES_AfterInsert]
--GO

--CREATE TRIGGER ENTITIES_AfterInsert ON VCLib.T_ENTITIES
--FOR INSERT 
--AS
--BEGIN
--	DECLARE @left INT, @level INT, @parent int, @id int

--	DECLARE curs CURSOR LOCAL FORWARD_ONLY STATIC
--		FOR SELECT EntityID, BaseEntity FROM inserted
--	OPEN curs
--	FETCH NEXT FROM curs INTO @id, @parent
--	WHILE (@@FETCH_STATUS <> -1)
--	BEGIN
--		IF (@id IS NOT NULL)
--		BEGIN
--			IF (@parent IS NOT NULL)
--			BEGIN
--				SELECT @left = LeftIndex, @level = TreeLevel FROM VCLib.T_ENTITIES WHERE EntityID = @parent
--				UPDATE VCLib.T_ENTITIES SET LeftIndex = LeftIndex + 2 WHERE LeftIndex > @left
--				UPDATE VCLib.T_ENTITIES SET RightIndex = RightIndex + 2 WHERE RightIndex > @left
--				UPDATE VCLib.T_ENTITIES SET LeftIndex = @left + 1, RightIndex = @left + 2, TreeLevel = @level + 1
--					WHERE EntityID =@id
--			END
--			ELSE
--			BEGIN
--				UPDATE VCLib.T_ENTITIES SET LeftIndex = LeftIndex + 2, RightIndex = RightIndex + 2
--				UPDATE VCLib.T_ENTITIES SET LeftIndex = 1, RightIndex = 2, TreeLevel = 0 WHERE EntityID =@id
--			END
		
--			FETCH NEXT FROM curs INTO @id, @parent
--		END
--	END
--	CLOSE curs
--	DEALLOCATE curs
--END
--go

----Удаление записей:
--IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[VCLib].[ENTITIES_AfterDelete]'))
--DROP TRIGGER [VCLib].[ENTITIES_AfterDelete]
--GO

--CREATE TRIGGER ENTITIES_AfterDelete ON VCLib.T_ENTITIES 
--FOR DELETE 
--AS
--BEGIN
--	UPDATE t SET LeftIndex = t.LeftIndex -
--		2 * (SELECT COUNT(*) FROM deleted d WHERE t.LeftIndex > d.LeftIndex)
--		FROM VCLib.T_ENTITIES t WHERE t.LeftIndex > ANY(SELECT LeftIndex FROM deleted)
--	UPDATE t SET RightIndex = t.RightIndex -
--		2 * (SELECT COUNT(*) FROM deleted d WHERE t.RightIndex > d.RightIndex)
--		FROM VCLib.T_ENTITIES t WHERE t.RightIndex > ANY(SELECT LeftIndex FROM deleted)
--END
--go

----Изменение родителей записей:
--IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[VCLib].[ENTITIES_ChangeParent]'))
--DROP TRIGGER [VCLib].[ENTITIES_ChangeParent]
--GO

--CREATE TRIGGER ENTITIES_ChangeParent ON VCLib.T_ENTITIES 
--FOR UPDATE
--AS
--BEGIN
--	IF (UPDATE(BaseEntity))
--	BEGIN

--		DECLARE @left INT, @right INT, @level INT, @parent int, @id int, @spread INT
--		DECLARE @newparent int, @newleft INT, @newright INT, @newlevel INT
		
--		DECLARE curs CURSOR LOCAL FORWARD_ONLY STATIC
--			FOR SELECT d.EntityID, d.BaseEntity, d.LeftIndex, d.RightIndex, d.TreeLevel, i.BaseEntity,
--				t.LeftIndex, t.RightIndex, t.TreeLevel
--				FROM deleted d INNER JOIN inserted i ON i.EntityID =d.EntityID
--				LEFT JOIN VCLib.T_ENTITIES t ON t.EntityID=i.BaseEntity
--		OPEN curs
--		FETCH NEXT FROM curs INTO @id, @parent, @left, @right, @level,
--			@newparent, @newleft, @newright, @newlevel
--		WHILE (@@FETCH_STATUS <> -1)
--		BEGIN
		
--			IF (@id IS NOT NULL AND @left IS NOT NULL AND @right IS NOT NULL)
--			BEGIN
				
--				UPDATE VCLib.T_ENTITIES SET LeftIndex = -LeftIndex, RightIndex = -RightIndex
--					WHERE LeftIndex >= @left AND RightIndex <= @right
				
--				SET @spread = @right - @left + 1
				
--				IF (@newparent IS NULL AND @parent IS NOT NULL)
--				BEGIN
--					UPDATE VCLib.T_ENTITIES SET LeftIndex = LeftIndex + @spread
--						WHERE LeftIndex < @left AND LeftIndex > 0
--					UPDATE VCLib.T_ENTITIES SET RightIndex = RightIndex + @spread
--						WHERE RightIndex < @left AND RightIndex > 0
--					UPDATE VCLib.T_ENTITIES SET LeftIndex = 1 - (@left + LeftIndex),
--						RightIndex = 1 - (@left + RightIndex), TreeLevel = TreeLevel - @level
--						WHERE LeftIndex <= -@left AND RightIndex >= -@right
--				END
--				ELSE
--				BEGIN
--					IF (@left < @newleft)
--					BEGIN
--						UPDATE VCLib.T_ENTITIES SET LeftIndex = LeftIndex - @spread
--							WHERE LeftIndex > @left AND LeftIndex <= @newleft
--						UPDATE VCLib.T_ENTITIES SET RightIndex = RightIndex - @spread
--							WHERE RightIndex > @left AND RightIndex <= @newleft
--					END
--					ELSE
--					BEGIN
--						UPDATE VCLib.T_ENTITIES SET LeftIndex = LeftIndex + @spread
--							WHERE LeftIndex > @newleft AND LeftIndex < @left
--						UPDATE VCLib.T_ENTITIES SET RightIndex = RightIndex + @spread
--							WHERE RightIndex > @newleft AND RightIndex < @left
--					END
					
--					SELECT @newleft = LeftIndex, @newright = RightIndex
--						FROM VCLib.T_ENTITIES WHERE EntityID = @newparent
--					UPDATE VCLib.T_ENTITIES SET LeftIndex = @newleft - (@left + LeftIndex) + 1,
--						RightIndex = @newleft - (@left + RightIndex) + 1,
--						TreeLevel = @newlevel + (TreeLevel - @level) + 1
--						WHERE LeftIndex <= -@left AND RightIndex >= -@right
--				END
--			END

--			FETCH NEXT FROM curs INTO @id, @parent, @left, @right, @level,
--				@newparent, @newleft, @newright, @newlevel
--		END
--		CLOSE curs
--		DEALLOCATE curs

--	END
--END
--go

--- пересчет служебных полей
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[RecalculateEntitiesTreeParams]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[RecalculateEntitiesTreeParams]
GO

CREATE PROCEDURE [dbo].[RecalculateEntitiesTreeParams]
	@BaseEntity int = NULL,
	@Begin INT = 1,
	@End INT OUTPUT,
	@Level INT = 0
AS
BEGIN
	DECLARE @ChCount as int
	IF (@BaseEntity IS NULL) SELECT @ChCount = COUNT(*) FROM VCLib.T_ENTITIES WHERE BaseEntity IS NULL
	ELSE SELECT @ChCount = COUNT(*) FROM VCLib.T_ENTITIES WHERE BaseEntity=@BaseEntity
	
	IF (@ChCount = 0 or @ChCount is NULL)
	BEGIN
		SET @End = @Begin + 1
	END
	ELSE
	BEGIN
		DECLARE @ID as int, @NestedLevel INT
		SET @NestedLevel = @Level + 1
		
		IF (@BaseEntity IS NULL)
		BEGIN
			SET @Begin = 1
			DECLARE curs1 CURSOR LOCAL FORWARD_ONLY STATIC
				FOR SELECT EntityID FROM VCLib.T_ENTITIES WHERE BaseEntity IS NULL ORDER BY EntityID
			OPEN curs1
			FETCH NEXT FROM curs1 INTO @ID
			WHILE (@@FETCH_STATUS <> -1)
			BEGIN
				EXECUTE RecalculateEntitiesTreeParams @BaseEntity = @ID, @Begin = @Begin, @End = @End OUTPUT, @Level = @NestedLevel
				UPDATE VCLib.T_ENTITIES SET LeftIndex=@Begin, RightIndex=@End, TreeLevel=@Level WHERE EntityID=@ID AND (LeftIndex IS NULL OR LeftIndex!=@Begin OR RightIndex IS NULL OR RightIndex!=@End OR TreeLevel IS NULL OR TreeLevel!=@Level)
				SET @Begin = @End + 1
				FETCH NEXT FROM curs1 INTO @ID
			END
			CLOSE curs1
			DEALLOCATE curs1
		END
		ELSE
		BEGIN
			SET @Begin = @Begin + 1
			DECLARE curs1 CURSOR LOCAL FORWARD_ONLY STATIC
				FOR SELECT EntityID FROM VCLib.T_ENTITIES WHERE BaseEntity=@BaseEntity ORDER BY EntityID
			OPEN curs1
			FETCH NEXT FROM curs1 INTO @ID
			WHILE (@@FETCH_STATUS <> -1)
			BEGIN
				EXECUTE RecalculateEntitiesTreeParams @BaseEntity = @ID, @Begin = @Begin, @End = @End OUTPUT, @Level = @NestedLevel 
				UPDATE VCLib.T_ENTITIES SET LeftIndex=@Begin, RightIndex=@End, TreeLevel=@Level WHERE EntityID =@ID AND (LeftIndex IS NULL OR LeftIndex!=@Begin OR RightIndex IS NULL OR RightIndex!=@End OR TreeLevel IS NULL OR TreeLevel!=@Level)
				SET @Begin = @End + 1
				FETCH NEXT FROM curs1 INTO @ID
			END
			CLOSE curs1
			DEALLOCATE curs1
		END
		
		SET @End = @End + 1
	END
END
GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[RecalculateENTITIES]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[RecalculateENTITIES]
GO

CREATE PROCEDURE [dbo].[RecalculateENTITIES]
AS
BEGIN
	declare @end int
	--alter table VCLib.T_ENTITIES disable trigger all
	exec dbo.RecalculateEntitiesTreeParams null, 1, @end output, 0
	--alter table VCLib.T_ENTITIES enable trigger all
END
GO 	