ALTER TABLE T_Edit ADD ZoneType nvarchar(1) NULL
GO

UPDATE T_Edit set ZoneType='E' where ZoneType is null

Alter TABLE T_Edit add UpdateDate datetime
GO

UPDATE T_Edit set UpdateDate = getdate()

ALTER TABLE T_Participant ADD ReviewZoneRole NVARCHAR(128) null
GO

CREATE TABLE T_EditRole(
	ObjectId int NOT NULL,
	EditId int NOT NULL,
	SubjectId int NOT NULL,
	ReviewZoneRole nvarchar(128) NULL
) 
CREATE UNIQUE CLUSTERED INDEX IX_EditRole ON T_EditRole (ObjectId);
CREATE INDEX IX_EditRole2 ON T_EditRole (EditId);
CREATE INDEX IX_EditRole3 ON T_EditRole (SubjectId);

INSERT INTO T_SystemPolicy (ObjectId,Principal,Permission,PolicyAction, PolicyObject)
values ('59','SYSTEM','ALLOWINLINEEDITING','PERMIT','SYSTEM');

INSERT INTO T_SystemPolicy (ObjectId,Principal,Permission,PolicyAction, PolicyObject)
VALUES ('60','SYSTEM','REVIEWFAILEDDOCUMENT','DENY','SYSTEM');

INSERT INTO T_PermissionSet (ObjectId, PermissionType, Title, PermissionFlags)
values ('28', 'ReviewZone', 'Hidden', ';');

INSERT INTO T_PermissionSet (ObjectId, PermissionType, Title, PermissionFlags)
values ('29', 'ReviewZone', 'Viewer', ';DOREVIEW;');

INSERT INTO T_PermissionSet (ObjectId, PermissionType, Title, PermissionFlags)
values ('30', 'ReviewZone', 'Reviewer', ';DOREVIEW;COMMENT;');

INSERT INTO T_PermissionSet (ObjectId, PermissionType, Title, PermissionFlags)
values ('31', 'ReviewZone', 'Author', ';DOREVIEW;COMMENT;AUTHOR;');

GO

/*
PR-1407 - DocumentSequence Task
*/

ALTER TABLE T_Document ADD DocSequence INT NOT NULL DEFAULT 0
GO

------------------------------------------------------------------------
--BATCH UPDATE SCRIPT
------------------------------------------------------------------------
BEGIN TRANSACTION

DECLARE @TotalReviewIdAffectedRow INT
DECLARE @CurrentReviewIdRow INT
DECLARE @ReviewId INT

IF OBJECT_ID('tempdb..#AffectedReviewId') IS NOT NULL BEGIN DROP TABLE #AffectedReviewId END
CREATE TABLE #AffectedReviewId
(
  IdentityId INT NOT NULL PRIMARY KEY IDENTITY(1,1),
  ReviewId INT
)

INSERT INTO #AffectedReviewId(ReviewId)
SELECT DISTINCT(ReviewId)
FROM T_Document
WHERE DocSequence = 0
ORDER BY ReviewId ASC

SET @TotalReviewIdAffectedRow = @@ROWCOUNT

SET @CurrentReviewIdRow = 1
WHILE @CurrentReviewIdRow <= @TotalReviewIdAffectedRow
BEGIN
	-- Get each ReviewId, and then process them
	SELECT @ReviewId = ReviewId FROM #AffectedReviewId WHERE IdentityId = @CurrentReviewIdRow
	
	--//Start of Inner Loop
	DECLARE @CurrentObjectIdRow INT
	DECLARE @TotalObjectIdAffectedRow INT
	DECLARE @ObjectId INT
	
	IF OBJECT_ID('tempdb..#AffectedObjectId') IS NOT NULL BEGIN DROP TABLE #AffectedObjectId END
	CREATE TABLE #AffectedObjectId 
	(
	  IdentityId INT NOT NULL PRIMARY KEY IDENTITY(1,1),
	  ObjectId INT
	)
	
	TRUNCATE TABLE #AffectedObjectId
	INSERT INTO #AffectedObjectId(ObjectId)
	SELECT ObjectId FROM T_Document WHERE ReviewId = @ReviewId
	
	SET @TotalObjectIdAffectedRow = @@ROWCOUNT

	SET @CurrentObjectIdRow = 1
	WHILE @CurrentObjectIdRow <= @TotalObjectIdAffectedRow
	BEGIN
		SELECT @ObjectId = ObjectId FROM #AffectedObjectId WHERE IdentityId = @CurrentObjectIdRow
		UPDATE T_Document SET DocSequence = @CurrentObjectIdRow WHERE ObjectId = @ObjectId
		SET @CurrentObjectIdRow = @CurrentObjectIdRow + 1
	END
	--//End of Inner Loop
	
	SET @CurrentReviewIdRow = @CurrentReviewIdRow + 1
END

COMMIT TRANSACTION

/*
End of PR-1407 - DocumentSequence Task
*/

/*
SuperContributor
*/


INSERT INTO T_PermissionSet (ObjectId,	PermissionType, Title, 		PermissionFlags )
VALUES (32,'Review','Super-Contributor',
';DOREVIEW;COMMENT;AUTHOR;CONTRIBUTE;DEFINEEDITZONE;');

UPDATE T_PermissionSet set PermissionFlags=';DOREVIEW;COMMENT;AUTHOR;CREATEREVIEW;CONTRIBUTE;DEFINEEDITZONE;'
WHERE PermissionType='Review' and Title='Owner-Contributor'and ObjectId=7;

UPDATE T_PermissionSet set PermissionFlags=';DOREVIEW;COMMENT;AUTHOR;CREATEREVIEW;CONTRIBUTE;DEFINEEDITZONE;'
WHERE PermissionType='Workgroup' and Title='Author-Contributor'and ObjectId=11;

UPDATE T_PermissionSet set PermissionFlags=';WORKGROUPADMIN;DOREVIEW;COMMENT;AUTHOR;CREATEREVIEW;CONTRIBUTE;DEFINEEDITZONE;'
WHERE PermissionType='Workgroup' and Title='Admin-Author-Contributor'and ObjectId=16;

UPDATE T_PermissionSet set PermissionFlags=';DOREVIEW;COMMENT;AUTHOR;CREATEREVIEW;CONTRIBUTE;MONITOR;DEFINEEDITZONE;'
WHERE PermissionType='Workgroup' and Title='Author-Contributor-Monitor'and ObjectId=21;

UPDATE T_PermissionSet set PermissionFlags=';WORKGROUPADMIN;DOREVIEW;COMMENT;AUTHOR;CREATEREVIEW;CONTRIBUTE;MONITOR;DEFINEEDITZONE;'
WHERE PermissionType='Workgroup' and Title='Admin-Author-Contributor-Monitor' and ObjectId=26;

GO