SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#tmpErrors')) DROP TABLE #tmpErrors
GO
CREATE TABLE #tmpErrors (Error int)
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
GO
PRINT N'Altering [dbo].[ProcessDefinition_ProjectPlanTask]'
GO
ALTER TABLE [dbo].[ProcessDefinition_ProjectPlanTask] ADD
[StepUpgradeHistory] [nvarchar] (max) NULL
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[ProcessDefinition_UpdateProcessHistoryDescription]'
GO

 
 
CREATE PROCEDURE [dbo].[ProcessDefinition_UpdateProcessHistoryDescription]
(
     @ProcessHistoryId int,
      @ProcessDescription nvarchar(max)
)
WITH ENCRYPTION AS
BEGIN
 
               SET NOCOUNT ON;

	UPDATE    ProcessDefinition_ProcessHistory
	SET              ProcessDescription = @ProcessDescription
	WHERE     (ProcessHistoryId = @ProcessHistoryId)


END

GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[ProcessDefinition_ClearProcessHistoryLinks]'
GO
CREATE PROCEDURE [dbo].[ProcessDefinition_ClearProcessHistoryLinks] 
	@processHistoryId int
WITH ENCRYPTION AS
BEGIN
			SET NOCOUNT ON;

	DELETE FROM ProcessDefinition_ProcessLinkHistory
	WHERE     (ProcessHistoryId = @processHistoryId)

END
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[ProcessDefinition_AddProcessHistoryLink]'
GO
CREATE PROCEDURE [dbo].[ProcessDefinition_AddProcessHistoryLink]
	@processHistoryId int,
	@processId int,
	@sectionName nvarchar(256),
	@linkUri nvarchar(1024),
	@fullLinkUri nvarchar(1024),
	@linksToProcessId int,
	@linkText nvarchar(1024),
	@linkType int,
	@linkUid nvarchar(50),
	@sectionDirection nvarchar(10),
	@sectionMultiplicity nvarchar(10),
	@sectionType nvarchar(128),
	@MajorVersion int, 
	@MinorVersion int
WITH ENCRYPTION AS
BEGIN
			SET NOCOUNT ON;

			DECLARE @excludedProcessId int
	SET @excludedProcessId = NULL
	IF((@linkType & 1) > 0)
	BEGIN
		SET @excludedProcessId = @linksToProcessId
		SET @linksToProcessId = NULL		
	END

	INSERT INTO ProcessDefinition_ProcessLinkHistory
		(ProcessId,ProcessHistoryId, SectionName, LinkUri, LinksToProcessId, FullLinkUri, 
		LinkText, LinkType, LinkUid, SectionDirection, SectionMultiplicity, SectionType, ExcludedLinksToProcessId, MajorVersion, MinorVersion)
	VALUES     (@processId,@ProcessHistoryId,@sectionName,@linkUri,@linksToProcessId,@fullLinkUri,
		@linkText, @linkType, @linkUid, @sectionDirection, @sectionMultiplicity, @sectionType, @excludedProcessId, @MajorVersion, @MinorVersion)


END
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[Notes_RenameTag]'
GO
CREATE PROCEDURE [dbo].[Notes_RenameTag]
	@TagId int,
	@TagName nvarchar(100)
WITH ENCRYPTION AS
BEGIN
			SET NOCOUNT ON;

	UPDATE Notes_Tag SET TagName=@TagName
		WHERE     (TagId = @tagId)

END



GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[ProcessDefinition_SetTaskStepUpgrade]'
GO
CREATE PROCEDURE [dbo].[ProcessDefinition_SetTaskStepUpgrade]
	@taskId int,
	@upgradeText nvarchar(max)
WITH ENCRYPTION AS
BEGIN
			SET NOCOUNT ON;

	UPDATE ProcessDefinition_ProjectPlanTask
	SET    StepUpgradeHistory = ISNULL(StepUpgradeHistory,'')+@upgradeText
	WHERE (ProjectPlanTaskId = @taskId)

END
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[ProcessDefinition_GetTags]'
GO
ALTER PROCEDURE [dbo].[ProcessDefinition_GetTags] 
     @repositoryName nvarchar(256),
	 @minCount int
WITH ENCRYPTION AS
BEGIN
			SET NOCOUNT ON;

	DECLARE @RepositoryId int
	SET @RepositoryId=dbo.ProcessDefinition_GetRepositoryId(@repositoryName)

SELECT ReposTags.TagName, COUNT(ReposProcesses.ProcessId) AS ProcessCount, ReposTags.TagId
FROM  (SELECT ProcessId
         FROM  ProcessDefinition_Process AS ProcessDefinition_Process_1
         WHERE (ForeignId = @RepositoryId) AND (IsDeleted = 0)) AS ReposProcesses RIGHT OUTER JOIN
         Notes_AssignedTag ON ReposProcesses.ProcessId = Notes_AssignedTag.ForeignId RIGHT OUTER JOIN
             (SELECT Notes_Tag.TagId, Notes_Tag.TagName
            FROM  ProcessDefinition_Process INNER JOIN
                     Notes_Tag ON ProcessDefinition_Process.ProcessId = Notes_Tag.ForeignId
            WHERE (ProcessDefinition_Process.ForeignId = @repositoryId)) AS ReposTags ON Notes_AssignedTag.TagId = ReposTags.TagId
GROUP BY ReposTags.TagName, ReposTags.TagId
ORDER BY ReposTags.TagName




END
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[ProcessDefinition_RepositoryPin]'
GO
CREATE TABLE [dbo].[ProcessDefinition_RepositoryPin]
(
[RepositoryPinId] [int] NOT NULL IDENTITY(1, 1),
[UserId] [int] NOT NULL,
[RepositoryId] [int] NOT NULL,
[ProjectId] [int] NOT NULL
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating primary key [PK_ProcessDefinition_RepositoryPin] on [dbo].[ProcessDefinition_RepositoryPin]'
GO
ALTER TABLE [dbo].[ProcessDefinition_RepositoryPin] ADD CONSTRAINT [PK_ProcessDefinition_RepositoryPin] PRIMARY KEY CLUSTERED  ([RepositoryPinId])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating index [IX_ProcessDefinition_RepositoryPin_UserId] on [dbo].[ProcessDefinition_RepositoryPin]'
GO
CREATE NONCLUSTERED INDEX [IX_ProcessDefinition_RepositoryPin_UserId] ON [dbo].[ProcessDefinition_RepositoryPin] ([UserId])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[ProcessDefinition_GetAllUserProjects]'
GO
ALTER PROCEDURE [dbo].[ProcessDefinition_GetAllUserProjects] 
	@userId int
WITH ENCRYPTION AS
BEGIN
			SET NOCOUNT ON;

	SELECT UserRepos.ProjectId, UserRepos.ProjectName, UserRepos.RepositoryId, 
                      UserRepos.RepositoryName, UserRepos.MemberRole, UserRepos.ProjectMemberRole,
					CASE WHEN UserPins.RepositoryPinId IS NULL THEN 0 ELSE 1 END AS IsPinned
	FROM
	(

SELECT     ProcessDefinition_Project.ProjectId, ProcessDefinition_Project.ProjectName, ProcessDefinition_Repository.RepositoryId, 
                      ProcessDefinition_Repository.RepositoryName, ProcessDefinition_Member.MemberRole, 3 AS ProjectMemberRole
FROM         ProcessDefinition_Project INNER JOIN
                      ProcessDefinition_Member ON ProcessDefinition_Project.RepositoryId = ProcessDefinition_Member.RepositoryId INNER JOIN
                      ProcessDefinition_Repository ON ProcessDefinition_Project.RepositoryId = ProcessDefinition_Repository.RepositoryId
WHERE     (ProcessDefinition_Member.MemberRole >= 3) AND (ProcessDefinition_Member.UserId = @userId)
	UNION

	SELECT     ProcessDefinition_Project.ProjectId, ProcessDefinition_Project.ProjectName, ProcessDefinition_Repository.RepositoryId, 
						  ProcessDefinition_Repository.RepositoryName, ProcessDefinition_Member.MemberRole, ProcessDefinition_ProjectMember.ProjectMemberRole
	FROM         ProcessDefinition_Project INNER JOIN
						  ProcessDefinition_Member ON ProcessDefinition_Project.RepositoryId = ProcessDefinition_Member.RepositoryId INNER JOIN
						  ProcessDefinition_Repository ON ProcessDefinition_Project.RepositoryId = ProcessDefinition_Repository.RepositoryId INNER JOIN
						  ProcessDefinition_ProjectMember ON ProcessDefinition_Project.ProjectId = ProcessDefinition_ProjectMember.ProjectId
	WHERE     (ProcessDefinition_Member.MemberRole > 0) AND (ProcessDefinition_Member.MemberRole < 3) AND (ProcessDefinition_Member.UserId = @userId) AND 
						  (ProcessDefinition_ProjectMember.UserId = @userId)	UNION 
SELECT     NULL AS ProjectId,NULL AS ProjectName,ProcessDefinition_Repository.RepositoryId, ProcessDefinition_Repository.RepositoryName, ProcessDefinition_Member.MemberRole,
			NULL AS ProjectMemberRole
FROM         ProcessDefinition_Repository INNER JOIN
                      ProcessDefinition_Member ON ProcessDefinition_Repository.RepositoryId = ProcessDefinition_Member.RepositoryId
WHERE     (ProcessDefinition_Member.UserId = @userId) AND (ProcessDefinition_Member.MemberRole > 0)
) AS UserRepos LEFT OUTER JOIN
(
SELECT RepositoryPinId,RepositoryId,ProjectId FROM
         ProcessDefinition_RepositoryPin 
		 WHERE UserId=@userId
) AS UserPins ON ISNULL(UserRepos.ProjectId,0) = UserPins.ProjectId AND UserRepos.RepositoryId = UserPins.RepositoryId
	ORDER BY IsPinned DESC,RepositoryName,ProjectName



END
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[Notes_CreateAssignedTag]'
GO
ALTER PROCEDURE [dbo].[Notes_CreateAssignedTag]
		@ForeignId int,
	@ForeignType int,
	@AssignedByForeignId int,
	@AssignedByForeignType int,
	@TagName nvarchar(100),
	@AssignedTagId int OUTPUT
WITH ENCRYPTION AS
BEGIN
			SET NOCOUNT ON;

	DECLARE @thisReposId int
	SELECT @thisReposId=ForeignId
	FROM  ProcessDefinition_Process
	WHERE (ProcessId = @foreignId)

	DECLARE @TagId int
	SELECT @TagId=Notes_Tag.TagId
	FROM  Notes_Tag INNER JOIN
			 ProcessDefinition_Process ON Notes_Tag.ForeignId = ProcessDefinition_Process.ProcessId
	WHERE (ProcessDefinition_Process.ForeignId = @thisReposId) AND (Notes_Tag.TagName = @tagName)


	IF (@TagId IS NULL)
	BEGIN
				INSERT INTO Notes_Tag (TagName, ForeignId, ForeignType) VALUES (@TagName, @ForeignId, @ForeignType)
		SET @TagId = SCOPE_IDENTITY()
	END

	exec Notes_CreateAssignedTagWithTagId 	@ForeignId,	@ForeignType,@AssignedByForeignId,@AssignedByForeignType,@TagId,@AssignedTagId OUTPUT

END




GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[ProcessDefinition_UpdateDiscussion]'
GO
 
 
CREATE PROCEDURE [dbo].[ProcessDefinition_UpdateDiscussion]
(
     @DiscussionId int,
	 @DiscussionText nvarchar(max)
)
WITH ENCRYPTION AS
BEGIN
 
               SET NOCOUNT ON;
 
     UPDATE ProcessDefinition_Discussion SET DiscussionText=@DiscussionText
	  WHERE DiscussionId=@DiscussionId
END
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[ProcessDefinition_GetTaggedProcessListByReposName]'
GO
ALTER PROCEDURE [dbo].[ProcessDefinition_GetTaggedProcessListByReposName] 
	@repositoryName nvarchar(256),
	@useFrozen bit
WITH ENCRYPTION AS
BEGIN
			SET NOCOUNT ON;

	IF(@useFrozen = 0)
	BEGIN
		SELECT     ISNULL(Notes_Tag.TagName,'untagged') AS TagName, ISNULL(Notes_Tag.TagId,0) AS TagId, 
			ProcessDefinition_Process.ProcessId, ProcessDefinition_Process.ProcessTitle, ProcessDefinition_Process.PrettyPostTitle,ProcessDefinition_Process.ProcessTypeId
		FROM         ProcessDefinition_Repository INNER JOIN
							  ProcessDefinition_Process ON ProcessDefinition_Repository.RepositoryId = ProcessDefinition_Process.ForeignId LEFT OUTER JOIN
							  Notes_Tag INNER JOIN
							  Notes_AssignedTag ON Notes_Tag.TagId = Notes_AssignedTag.TagId ON ProcessDefinition_Process.ProcessId = Notes_AssignedTag.ForeignId
		WHERE     (ProcessDefinition_Repository.RepositoryName = @repositoryName) AND IsDeleted=0
		ORDER BY Notes_Tag.TagName, ProcessDefinition_Process.ProcessTitle
	END
	ELSE
	BEGIN
		SELECT     ISNULL(Notes_Tag.TagName, 'untagged') AS TagName, ISNULL(Notes_Tag.TagId, 0) AS TagId, FrozenProcesses.ProcessId, 
							  FrozenProcesses.ProcessTitle, FrozenProcesses.PrettyPostTitle,FrozenProcesses.ProcessTypeId
		FROM         ProcessDefinition_Repository INNER JOIN
							  (
					SELECT     ProcessId, ProcessTitle, PrettyPostTitle, ForeignId,ProcessTypeId
					FROM         ProcessDefinition_ProcessHistory
					where IsFrozenVersion = 1)  AS FrozenProcesses ON ProcessDefinition_Repository.RepositoryId = FrozenProcesses.ForeignId LEFT OUTER JOIN
							  Notes_Tag INNER JOIN
							  Notes_AssignedTag ON Notes_Tag.TagId = Notes_AssignedTag.TagId ON FrozenProcesses.ProcessId = Notes_AssignedTag.ForeignId
		WHERE     (ProcessDefinition_Repository.RepositoryName = @repositoryName)
		ORDER BY TagName, FrozenProcesses.ProcessTitle
	END
END
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[ProcessDefinition_GetAllProcessHistories]'
GO
 
 
CREATE PROCEDURE [dbo].[ProcessDefinition_GetAllProcessHistories]
(
     @repositoryName nvarchar(256)
)
WITH ENCRYPTION AS
BEGIN
 
               SET NOCOUNT ON;

	DECLARE @RepositoryId int
	SET @RepositoryId=dbo.ProcessDefinition_GetRepositoryId(@repositoryName) 

	SELECT ProcessHistoryId, ProcessDescription, ProcessId, IsLatestVersion, MajorVersion, MinorVersion
	FROM  ProcessDefinition_ProcessHistory
	WHERE (ForeignId = @RepositoryId)

END
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[ProcessDefinition_GetProcessDefinition]'
GO
ALTER PROCEDURE [dbo].[ProcessDefinition_GetProcessDefinition] 
	@repositoryId int,
	@domainRoot nvarchar(512)
WITH ENCRYPTION AS
BEGIN
			SET NOCOUNT ON;

	SELECT(
SELECT RepositoryName as [name],
	@domainRoot as [domain],

( SELECT TypeUId AS [id],
	ProcessTypeName AS [name],
	ProcessTypeTemplate AS [template],
	IsDeleted AS [deleted],


(SELECT UniqueId as [id],    
		ScreenLabel AS [label], 
		MetadataType AS [type],
		Position as [position],
		DefaultValue as [default],
		AllowableValues as [allowable],
		FormatInfo as [format],
		ControlData as [data]			
FROM         Metadata_MetadataField
WHERE     (ForeignId = ProcessTypeId AND ForeignType = 17)
FOR XML PATH('field'), ROOT('metadata'), ELEMENTS, type)

	FROM ProcessDefinition_ProcessType
	WHERE (RepositoryId=@repositoryId)
FOR XML PATH('type'), ROOT('types'), ELEMENTS , type),
 		
( SELECT dbo.ProcessDefinition_GetProcessXml(ProcessId)
	FROM ProcessDefinition_Process
	WHERE ForeignId=@repositoryId AND IsDeleted=0
FOR XML PATH(''), ROOT('processes'), ELEMENTS, type)

FROM ProcessDefinition_Repository
WHERE (RepositoryId=@repositoryId)
FOR XML PATH('library'), ELEMENTS
) AS LibraryXml

END
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO

PRINT N'Creating [dbo].[ProcessDefinition_SetRepositoryPin]'
GO
CREATE PROCEDURE [dbo].[ProcessDefinition_SetRepositoryPin] 
	@userId int,
	@repositoryName nvarchar(256),
	@projectId int,
	@pinSet bit
WITH ENCRYPTION AS
BEGIN
			SET NOCOUNT ON;

	DECLARE @RepositoryId int
	SET @RepositoryId=dbo.ProcessDefinition_GetRepositoryId(@repositoryName)



	DECLARE @curId int
	SELECT @curId=RepositoryPinId
	FROM  ProcessDefinition_RepositoryPin
	WHERE (RepositoryId = @repositoryId) AND (ProjectId = @projectId) AND (UserId = @userId)

	IF(@pinSet=1 AND @curId IS NULL)
	BEGIN 
		INSERT INTO ProcessDefinition_RepositoryPin
				 (UserId, RepositoryId, ProjectId)
		VALUES (@userId,@repositoryId,@projectId)			
	END
	ELSE
	IF(@pinSet=0 AND @curId IS NOT NULL)
	BEGIN 
		DELETE FROM ProcessDefinition_RepositoryPin
				WHERE RepositoryPinId=@curId	
	END

END
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
IF EXISTS (SELECT * FROM #tmpErrors) ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT>0 BEGIN
PRINT 'The database update succeeded'
COMMIT TRANSACTION
END
ELSE PRINT 'The database update failed'
GO
DROP TABLE #tmpErrors
GO

PRINT N'Adding full text indexing to tables'
GO
CREATE FULLTEXT INDEX ON [dbo].[ProcessDefinition_Step] KEY INDEX [PK_ProcessDefinition_Step] ON [ProcessDefinitionFtIndex]
GO
PRINT N'Adding full text indexing to columns'
GO
ALTER FULLTEXT INDEX ON [dbo].[ProcessDefinition_Step] ADD ([StepName] LANGUAGE 1033)
GO
ALTER FULLTEXT INDEX ON [dbo].[ProcessDefinition_Step] ADD ([StepDescription] LANGUAGE 1033)
GO
ALTER FULLTEXT INDEX ON [dbo].[ProcessDefinition_Step] ENABLE
GO
PRINT N'Altering [dbo].[ProcessDefinition_GetAutocompleteProcesses]'
GO
ALTER PROCEDURE [dbo].[ProcessDefinition_GetAutocompleteProcesses]
     @repositoryName nvarchar(256),
	@Name nvarchar(128),
	@UserId int
WITH ENCRYPTION AS
BEGIN
			SET NOCOUNT ON;

	DECLARE @RepositoryId int
	SET @RepositoryId=dbo.ProcessDefinition_GetRepositoryId(@repositoryName)

	DECLARE @rawSearch nvarchar(128)
	SET @rawSearch='"'+REPLACE(@Name,'%','')+'"'

	SELECT ProcessId, ProcessTitle,PrettyPostTitle,HasPostAccess FROM
	(
		SELECT     ProcessId, ProcessTitle,PrettyPostTitle, dbo.ProcessDefinition_HasPostAccess(PrettyPostTitle, @repositoryName, @UserId) AS HasPostAccess, 1 AS SortOrder
		FROM         ProcessDefinition_Process
		WHERE     (ProcessTitle LIKE @Name) AND (ForeignId = @repositoryId) AND IsDeleted=0
		UNION
		SELECT     ProcessId, ProcessTitle, PrettyPostTitle,dbo.ProcessDefinition_HasPostAccess(PrettyPostTitle, @repositoryName, @UserId) AS HasPostAccess, 2 AS SortOrder
		FROM         ProcessDefinition_Process
		WHERE CONTAINS(ProcessDefinition_Process.*,@rawSearch) AND (ForeignId = @repositoryId) AND IsDeleted=0
		UNION
		SELECT ProcessDefinition_Process.ProcessId, ProcessDefinition_Process.ProcessTitle, ProcessDefinition_Process.PrettyPostTitle,dbo.ProcessDefinition_HasPostAccess(ProcessDefinition_Process.PrettyPostTitle, @repositoryName, @UserId) AS HasPostAccess, 2 AS SortOrder
		FROM  ProcessDefinition_Step INNER JOIN
         ProcessDefinition_Process ON ProcessDefinition_Step.ParentProcessId = ProcessDefinition_Process.ProcessId
		 WHERE CONTAINS(ProcessDefinition_Step.*,@rawSearch) AND (ProcessDefinition_Process.ForeignId = @repositoryId) AND ProcessDefinition_Process.IsDeleted=0
	) AS X
	GROUP BY ProcessId, ProcessTitle,PrettyPostTitle,HasPostAccess
	ORDER BY MIN(SortOrder),CAST(ProcessTitle AS nvarchar(128))

END
GO

PRINT N'Altering [dbo].[ProcessDefinition_GetProjectPlanTask]'
GO
ALTER PROCEDURE [dbo].[ProcessDefinition_GetProjectPlanTask] 
	@userId int,
	@taskId int
WITH ENCRYPTION AS
BEGIN
			SET NOCOUNT ON;

	DECLARE @projectId int
	SELECT     @projectId=ProcessDefinition_ProjectPlan.ProjectId
	FROM         ProcessDefinition_ProjectPlanTask INNER JOIN
						  ProcessDefinition_ProjectPlan ON ProcessDefinition_ProjectPlanTask.ProjectPlanId = ProcessDefinition_ProjectPlan.ProjectPlanId
	WHERE     (ProcessDefinition_ProjectPlanTask.ProjectPlanTaskId = @taskId)

	IF(dbo.ProcessDefinition_HasProjectAccess(@projectId,@userId)=1)
	BEGIN
SELECT     ProcessDefinition_ProjectPlanTask.ProjectPlanTaskId, ProcessDefinition_ProjectPlanTask.TaskName, ProcessDefinition_ProjectPlanTask.ParentTaskId, 
                      ProcessDefinition_ProjectPlanTask.TaskGuid, ProcessDefinition_ProjectPlanTask.TaskProcessId, ProcessDefinition_ProjectPlanTask.TaskStepId, 
                      ProcessDefinition_ProjectPlanTask.TaskOptional, ProcessDefinition_ProjectPlanTask.TaskIterative, ProcessDefinition_ProjectPlanTask.TaskConcurrent, 
                      ProcessDefinition_ProjectPlanTask.TaskIsRemoved, ProcessDefinition_ProjectPlanTask.TaskBasedOnGuid, ProcessDefinition_ProjectPlanTask.ProjectPlanId, 
                      ProcessDefinition_ProcessHistory.PrettyPostTitle, ProcessDefinition_ProjectPlanTask.TaskNotes, ProcessDefinition_ProjectPlan.ProjectId, 
                      ProcessDefinition_ProjectPlan.ProjectPlanName, ProcessDefinition_ProjectPlanTask.EstimatedStartDate, ProcessDefinition_ProjectPlanTask.EstimatedEndDate, 
                      ProcessDefinition_ProjectPlanTask.ActualStartDate, ProcessDefinition_ProjectPlanTask.ActualEndDate, ProcessDefinition_ProjectPlanTask.CurrentStatus, 
                      ProcessDefinition_ProjectPlanTask.StatusNotes, ProcessDefinition_ProcessHistory.IsFrozenVersion, ProcessDefinition_ProjectPlanTask.ProcessHistoryId, 
                      ProcessDefinition_ProcessHistory.AltGroup, ProcessDefinition_ProjectPlanTask.TaskPosition, ProcessDefinition_ProjectPlanTask.TaskProgressPc,
					  ProcessDefinition_ProjectPlanTask.StepUpgradeHistory
FROM         ProcessDefinition_ProjectPlanTask INNER JOIN
                      ProcessDefinition_ProjectPlan ON ProcessDefinition_ProjectPlanTask.ProjectPlanId = ProcessDefinition_ProjectPlan.ProjectPlanId LEFT OUTER JOIN
                      ProcessDefinition_ProcessHistory ON ProcessDefinition_ProjectPlanTask.ProcessHistoryId = ProcessDefinition_ProcessHistory.ProcessHistoryId
WHERE     (ProcessDefinition_ProjectPlanTask.ProjectPlanTaskId = @taskId)

	END


END


--duplicate cross repos tags
INSERT INTO Notes_Tag (TagName,ForeignId,ForeignType)
SELECT TagName,FirstProcessId,144021 AS ForeignType
FROM
(
SELECT ProcessDefinition_Process.ForeignId as TagRepos, 
		ProcessDefinition_Process_1.ForeignId AS AssignedRepos, 
		MIN(ProcessDefinition_Process_1.ProcessId) AS FirstProcessId,
		Notes_Tag.TagName
FROM  Notes_Tag INNER JOIN
         Notes_AssignedTag ON Notes_Tag.TagId = Notes_AssignedTag.TagId INNER JOIN
         ProcessDefinition_Process ON Notes_Tag.ForeignId = ProcessDefinition_Process.ProcessId INNER JOIN
         ProcessDefinition_Process AS ProcessDefinition_Process_1 ON Notes_AssignedTag.ForeignId = ProcessDefinition_Process_1.ProcessId
		 WHERE ProcessDefinition_Process.ForeignId<>ProcessDefinition_Process_1.ForeignId
		 GROUP BY ProcessDefinition_Process.ForeignId,
		 ProcessDefinition_Process_1.ForeignId,
		 Notes_Tag.TagName
) AS X

--assign tags to the ones that are repos specific
update Notes_AssignedTag SET TagId=X.TagId FROM
(
SELECT ProcessDefinition_Process.ForeignId AS TagRepos, ProcessDefinition_Process_1.ForeignId AS AssignedRepos, ProcessDefinition_Process_1.ProcessId, Notes_AssignedTag.AssignedTagId, Notes_Tag.TagName, ReposTag.TagId
FROM  Notes_Tag INNER JOIN
         Notes_AssignedTag ON Notes_Tag.TagId = Notes_AssignedTag.TagId INNER JOIN
         ProcessDefinition_Process ON Notes_Tag.ForeignId = ProcessDefinition_Process.ProcessId INNER JOIN
         ProcessDefinition_Process AS ProcessDefinition_Process_1 ON Notes_AssignedTag.ForeignId = ProcessDefinition_Process_1.ProcessId AND ProcessDefinition_Process.ForeignId <> ProcessDefinition_Process_1.ForeignId INNER JOIN
             (SELECT ProcessDefinition_Process_2.ForeignId, Notes_Tag_1.TagName, Notes_Tag_1.TagId
            FROM  Notes_Tag AS Notes_Tag_1 INNER JOIN
                     ProcessDefinition_Process AS ProcessDefinition_Process_2 ON Notes_Tag_1.ForeignId = ProcessDefinition_Process_2.ProcessId) AS ReposTag ON ProcessDefinition_Process_1.ForeignId = ReposTag.ForeignId AND Notes_Tag.TagName = ReposTag.TagName
		) AS X 
WHERE X.AssignedTagId=Notes_AssignedTag.AssignedTagId

--remove assigned tags that are duplicated
DELETE FROM Notes_AssignedTag WHERE TagId IN
(
SELECT RemTags.TagId
FROM  (SELECT ProcessDefinition_Process.ForeignId, Notes_Tag.TagId, Notes_Tag.TagName
         FROM  Notes_Tag INNER JOIN
                  ProcessDefinition_Process ON Notes_Tag.ForeignId = ProcessDefinition_Process.ProcessId) AS RemTags INNER JOIN
             (SELECT ProcessDefinition_Process_1.ForeignId, MIN(Notes_Tag_1.TagId) AS KeepTagId, Notes_Tag_1.TagName
            FROM  Notes_Tag AS Notes_Tag_1 INNER JOIN
                     ProcessDefinition_Process AS ProcessDefinition_Process_1 ON Notes_Tag_1.ForeignId = ProcessDefinition_Process_1.ProcessId
            GROUP BY ProcessDefinition_Process_1.ForeignId, Notes_Tag_1.TagName
            HAVING (COUNT(Notes_Tag_1.TagId) > 1)) AS DupTags ON RemTags.ForeignId = DupTags.ForeignId AND RemTags.TagId <> DupTags.KeepTagId AND RemTags.TagName = DupTags.TagName
)

--remove duplicated tags 
DELETE FROM Notes_Tag WHERE TagId IN
(
SELECT RemTags.TagId
FROM  (SELECT ProcessDefinition_Process.ForeignId, Notes_Tag.TagId, Notes_Tag.TagName
         FROM  Notes_Tag INNER JOIN
                  ProcessDefinition_Process ON Notes_Tag.ForeignId = ProcessDefinition_Process.ProcessId) AS RemTags INNER JOIN
             (SELECT ProcessDefinition_Process_1.ForeignId, MIN(Notes_Tag_1.TagId) AS KeepTagId, Notes_Tag_1.TagName
            FROM  Notes_Tag AS Notes_Tag_1 INNER JOIN
                     ProcessDefinition_Process AS ProcessDefinition_Process_1 ON Notes_Tag_1.ForeignId = ProcessDefinition_Process_1.ProcessId
            GROUP BY ProcessDefinition_Process_1.ForeignId, Notes_Tag_1.TagName
            HAVING (COUNT(Notes_Tag_1.TagId) > 1)) AS DupTags ON RemTags.ForeignId = DupTags.ForeignId AND RemTags.TagId <> DupTags.KeepTagId AND RemTags.TagName = DupTags.TagName
)
