SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[Notes_PublishedTag]'
GO
CREATE TABLE [dbo].[Notes_PublishedTag]
(
[PublishedTagId] [int] NOT NULL IDENTITY(1, 1),
[PublishId] [int] NOT NULL,
[TagId] [int] NOT NULL,
[TagName] [nvarchar] (100) NOT NULL,
[ForeignId] [int] NOT NULL CONSTRAINT [DF_Notes_PublishedTag_ForeignId] DEFAULT ((0)),
[ForeignType] [int] NOT NULL CONSTRAINT [DF_Notes_PublishedTag_ForeignType] DEFAULT ((0))
)
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating primary key [PK_Notes_PublishedTag] on [dbo].[Notes_PublishedTag]'
GO
ALTER TABLE [dbo].[Notes_PublishedTag] ADD CONSTRAINT [PK_Notes_PublishedTag] PRIMARY KEY CLUSTERED  ([PublishedTagId])
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating index [IX_Notes_PublishedTag_PublishId] on [dbo].[Notes_PublishedTag]'
GO
CREATE NONCLUSTERED INDEX [IX_Notes_PublishedTag_PublishId] ON [dbo].[Notes_PublishedTag] ([PublishId])
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating index [IX_Notes_PublishedTag_TagId] on [dbo].[Notes_PublishedTag]'
GO
CREATE NONCLUSTERED INDEX [IX_Notes_PublishedTag_TagId] ON [dbo].[Notes_PublishedTag] ([TagId])
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating index [IX_Notes_PublishedTag_ForeignId] on [dbo].[Notes_PublishedTag]'
GO
CREATE NONCLUSTERED INDEX [IX_Notes_PublishedTag_ForeignId] ON [dbo].[Notes_PublishedTag] ([ForeignId])
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating index [IX_Notes_PublishedTag_ForeignType] on [dbo].[Notes_PublishedTag]'
GO
CREATE NONCLUSTERED INDEX [IX_Notes_PublishedTag_ForeignType] ON [dbo].[Notes_PublishedTag] ([ForeignType])
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Altering [dbo].[ProcessDefinition_CreatePublish]'
GO
 
 
ALTER PROCEDURE [dbo].[ProcessDefinition_CreatePublish]
(
     @RepositoryId int,
      @Title nvarchar(128),
      @Description ntext,
      @Version nvarchar(128),
      
      @UserId int
)
WITH ENCRYPTION AS
BEGIN
 
               SET NOCOUNT ON;
 
     INSERT INTO ProcessDefinition_Publish
      (
            RepositoryId, 
            Title, 
            Description, 
            Version, 
            PublishDate, 
            UserId
      ) VALUES (
            @RepositoryId, 
            @Title, 
            @Description, 
            @Version, 
            GetUtcDate(), 
            @UserId
      )
 
     DECLARE @newId int
     SET @newId = Scope_Identity()

        INSERT INTO Notes_PublishedTag
		   (PublishId, TagId, TagName, ForeignId, ForeignType)
    SELECT @newId, Notes_Tag.TagId, Notes_Tag.TagName, Notes_Tag.ForeignId, Notes_Tag.ForeignType
    FROM  Notes_Tag INNER JOIN
		   ProcessDefinition_Process ON Notes_Tag.ForeignId = ProcessDefinition_Process.ProcessId
    WHERE (Notes_Tag.ForeignType = 144021) AND (ProcessDefinition_Process.ForeignId = @repositoryId) AND (ProcessDefinition_Process.ForeignType = 7)

     RETURN @newId
END
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[Notes_PublishedAssignedTag]'
GO
CREATE TABLE [dbo].[Notes_PublishedAssignedTag]
(
[PublishedAssignedTagId] [int] NOT NULL IDENTITY(1, 1),
[PublishId] [int] NOT NULL,
[AssignedTagId] [int] NOT NULL,
[TagId] [int] NOT NULL,
[ForeignId] [int] NOT NULL,
[ForeignType] [int] NOT NULL,
[AssignedByForeignId] [int] NOT NULL,
[AssignedByForeignType] [int] NOT NULL,
[AssignedDt] [datetime] NOT NULL
)
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating primary key [PK_Notes_PublishedAssigned] on [dbo].[Notes_PublishedAssignedTag]'
GO
ALTER TABLE [dbo].[Notes_PublishedAssignedTag] ADD CONSTRAINT [PK_Notes_PublishedAssigned] PRIMARY KEY CLUSTERED  ([PublishedAssignedTagId])
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating index [IX_Notes_PublishedAssignedTag_PublishId] on [dbo].[Notes_PublishedAssignedTag]'
GO
CREATE NONCLUSTERED INDEX [IX_Notes_PublishedAssignedTag_PublishId] ON [dbo].[Notes_PublishedAssignedTag] ([PublishId])
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating index [IX_Notes_PublishedAssignedTag_AssignedTagId] on [dbo].[Notes_PublishedAssignedTag]'
GO
CREATE NONCLUSTERED INDEX [IX_Notes_PublishedAssignedTag_AssignedTagId] ON [dbo].[Notes_PublishedAssignedTag] ([AssignedTagId])
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating index [IX_Notes_PublishedAssignedTag_TagId] on [dbo].[Notes_PublishedAssignedTag]'
GO
CREATE NONCLUSTERED INDEX [IX_Notes_PublishedAssignedTag_TagId] ON [dbo].[Notes_PublishedAssignedTag] ([TagId])
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating index [IX_Notes_PublishedAssignedTag_ForeignId] on [dbo].[Notes_PublishedAssignedTag]'
GO
CREATE NONCLUSTERED INDEX [IX_Notes_PublishedAssignedTag_ForeignId] ON [dbo].[Notes_PublishedAssignedTag] ([ForeignId])
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating index [IX_Notes_PublishedAssignedTag_ForeignType] on [dbo].[Notes_PublishedAssignedTag]'
GO
CREATE NONCLUSTERED INDEX [IX_Notes_PublishedAssignedTag_ForeignType] ON [dbo].[Notes_PublishedAssignedTag] ([ForeignType])
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating index [IX_Notes_PublishedAssignedTag_AssignedByForeignId] on [dbo].[Notes_PublishedAssignedTag]'
GO
CREATE NONCLUSTERED INDEX [IX_Notes_PublishedAssignedTag_AssignedByForeignId] ON [dbo].[Notes_PublishedAssignedTag] ([AssignedByForeignId])
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Altering [dbo].[ProcessDefinition_CreatePublishProcess]'
GO
ALTER PROCEDURE [dbo].[ProcessDefinition_CreatePublishProcess]
(
     @ProcessId int,
      @ProcessHistoryId int,
      @PublishId int
)
WITH ENCRYPTION AS
BEGIN
 
               SET NOCOUNT ON;
 
     INSERT INTO ProcessDefinition_PublishProcess
      (
            ProcessId, 
            ProcessHistoryId, 
            PublishId
      ) VALUES (
            @ProcessId, 
            @ProcessHistoryId, 
            @PublishId
      )
 
     DECLARE @newId int
     SET @newId = Scope_Identity()

    INSERT INTO Notes_PublishedAssignedTag
         (PublishId, AssignedTagId, TagId, ForeignId, ForeignType, AssignedByForeignId, AssignedByForeignType, AssignedDt)
    SELECT @PublishId,AssignedTagId, TagId, ForeignId, ForeignType, AssignedByForeignId, AssignedByForeignType, AssignedDt
    FROM  Notes_AssignedTag
    WHERE (ForeignType = 144021) AND (ForeignId = @ProcessId)

     RETURN @newId
END
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Altering [dbo].[ProcessDefinition_GetRepositoryGrowth]'
GO
ALTER PROCEDURE [dbo].[ProcessDefinition_GetRepositoryGrowth]
	@repositoryName nvarchar(256)
WITH ENCRYPTION AS
BEGIN
			SET NOCOUNT ON;

	DECLARE @dtNow DateTime
	SET @dtNow=GetUtcDate()
	SELECT     COUNT(ProcessDefinition_Process.ProcessId) AS ProcessCount, 
			CASE WHEN DATEDIFF(month, ProcessDefinition_Process.DateCreated, @dtNow) >= 12 THEN 13 ELSE DATEDIFF(month, ProcessDefinition_Process.DateCreated, @dtNow) END AS CreationMonth
	FROM         ProcessDefinition_Process INNER JOIN
						  ProcessDefinition_Repository ON ProcessDefinition_Process.ForeignId = ProcessDefinition_Repository.RepositoryId
	WHERE     (ProcessDefinition_Repository.RepositoryName = @repositoryName) AND IsDeleted = 0
	GROUP BY CASE WHEN DATEDIFF(month, ProcessDefinition_Process.DateCreated, @dtNow) >= 12 THEN 13 ELSE DATEDIFF(month, ProcessDefinition_Process.DateCreated, @dtNow) END
	ORDER BY CreationMonth DESC

END
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[ProcessDefinition_GetLinkedProcessesFromHistory]'
GO
CREATE PROCEDURE [dbo].[ProcessDefinition_GetLinkedProcessesFromHistory] 
	@processHistoryId int
WITH ENCRYPTION AS
BEGIN
			SET NOCOUNT ON;



SELECT     ProcessDefinition_ProcessLinkHistory.ProcessLinkHistoryId, ProcessDefinition_ProcessLinkHistory.ProcessId, ProcessDefinition_ProcessLinkHistory.SectionName, 
                      ProcessDefinition_ProcessLinkHistory.LinkUri, ProcessDefinition_ProcessLinkHistory.FullLinkUri, ProcessDefinition_ProcessLinkHistory.LinkText, 
                      ProcessDefinition_ProcessHistory.PrettyPostTitle, 
		ProcessDefinition_ProcessHistory.ProcessTitle, ProcessTypeId, LinksToProcessId, LinkType, SectionDirection
FROM         ProcessDefinition_ProcessLinkHistory INNER JOIN
                      ProcessDefinition_ProcessHistory ON ProcessDefinition_ProcessLinkHistory.LinksToProcessId = ProcessDefinition_ProcessHistory.ProcessId
WHERE     (ProcessDefinition_ProcessLinkHistory.ProcessHistoryId = @processHistoryId) AND IsDeleted = 0

END
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Altering [dbo].[ProcessDefinition_GetTagsByReposName]'
GO
ALTER PROCEDURE [dbo].[ProcessDefinition_GetTagsByReposName] 
	@repositoryName nvarchar(256),
	@lastPublished bit
WITH ENCRYPTION AS
BEGIN
			SET NOCOUNT ON;

	IF(@lastPublished=0)
	BEGIN
	    SELECT     ISNULL(Notes_Tag.TagName,'untagged') AS TagName, COUNT(ProcessDefinition_Process.ProcessId) AS ProcessCount, ISNULL(Notes_Tag.TagId ,0) AS TagId
	    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)
				AND Notes_Tag.ForeignType=144021
	    GROUP BY Notes_Tag.TagName, Notes_Tag.TagId
	    HAVING      (COUNT(ProcessDefinition_Process.ProcessId) > 0)
	    ORDER BY Notes_Tag.TagName
	END
    ELSE
    BEGIN 
	DECLARE @RepositoryId int
	SET @RepositoryId=dbo.ProcessDefinition_GetRepositoryId(@repositoryName)

    	   DECLARE @lastPublishedId int
	   SELECT @lastPublishedId=MAX(PublishId)
	   FROM  ProcessDefinition_Publish
	   WHERE (RepositoryId = @RepositoryId)

	    SELECT     ISNULL(Notes_PublishedTag.TagName,'untagged') AS TagName, COUNT(ProcessDefinition_Process.ProcessId) AS ProcessCount, ISNULL(Notes_PublishedTag.TagId ,0) AS TagId
	    FROM         ProcessDefinition_Repository INNER JOIN
							 ProcessDefinition_Process ON ProcessDefinition_Repository.RepositoryId = ProcessDefinition_Process.ForeignId LEFT OUTER JOIN
							 Notes_PublishedTag INNER JOIN
							 Notes_PublishedAssignedTag ON Notes_PublishedTag.TagId = Notes_PublishedAssignedTag.TagId ON ProcessDefinition_Process.ProcessId = Notes_PublishedAssignedTag.ForeignId
	    WHERE     (ProcessDefinition_Repository.RepositoryName = @repositoryName AND IsDeleted=0)
				AND Notes_PublishedTag.PublishId=@lastPublishedId AND Notes_PublishedAssignedTag.PublishId=@lastPublishedId
	    GROUP BY Notes_PublishedTag.TagName, Notes_PublishedTag.TagId
	    HAVING      (COUNT(ProcessDefinition_Process.ProcessId) > 0)
	    ORDER BY Notes_PublishedTag.TagName




    END
END
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[ProcessDefinition_GetProcessesLinkUsageFromHistory]'
GO
CREATE PROCEDURE [dbo].[ProcessDefinition_GetProcessesLinkUsageFromHistory]
      @processhistoryid INT, @publishid INT
WITH ENCRYPTION AS
     BEGIN
                           SET NOCOUNT ON;
         SELECT ProcessId,
                SectionName,
                LinkText,
                ProcessTitle,
                PrettyPostTitle,
                DescriptionSnippet,
                ProcessTypeId,
                LinkType,
                SectionDirection
         FROM
             (
             SELECT LinksFROM.ProcessId,
                    LinksFROM.SectionName,
                    LinksFROM.LinkText,
                    LinksFROM.ProcessTitle,
                    LinksFROM.PrettyPostTitle,
                    LinksFROM.DescriptionSnippet,
                    LinksFROM.ProcessTypeId,
                    LinksFROM.LinkType,
                    LinksFROM.SectionDirection
             FROM
                 (
                  SELECT ProcessDefinition_PublishProcess.ProcessHistoryId,
                         ProcessDefinition_ProcessLinkHistory.ProcessId,
                         ProcessDefinition_ProcessLinkHistory.SectionName,
                         ProcessDefinition_ProcessLinkHistory.LinkText,
                         ProcessDefinition_ProcessHistory.ProcessTitle,
                         ProcessDefinition_ProcessLinkHistory.LinksToProcessId,
                         ProcessDefinition_ProcessHistory.PrettyPostTitle,
                         ProcessDefinition_ProcessHistory.DescriptionSnippet,
                         ProcessDefinition_ProcessHistory.ProcessTypeId,
                         ProcessDefinition_ProcessLinkHistory.LinkType,
                         ProcessDefinition_ProcessLinkHistory.SectionDirection
                  FROM ProcessDefinition_PublishProcess
                       INNER JOIN ProcessDefinition_ProcessLinkHistory ON ProcessDefinition_PublishProcess.ProcessHistoryId = ProcessDefinition_ProcessLinkHistory.ProcessHistoryId
                  INNER JOIN ProcessDefinition_ProcessHistory ON ProcessDefinition_ProcessLinkHistory.ProcessHistoryId = ProcessDefinition_ProcessHistory.ProcessHistoryId
                  WHERE(ProcessDefinition_PublishProcess.PublishId = @publishid)
                       AND (ProcessDefinition_ProcessHistory.IsDeleted = 0)
                 ) AS LinksFROM
                 INNER JOIN
                           (
                            SELECT ProcessDefinition_PublishProcess_1.ProcessHistoryId,
                                   ProcessDefinition_PublishProcess_1.ProcessId
                            FROM ProcessDefinition_PublishProcess AS ProcessDefinition_PublishProcess_1
                                 INNER JOIN ProcessDefinition_ProcessLinkHistory AS ProcessDefinition_ProcessLinkHistory_1 ON ProcessDefinition_PublishProcess_1.ProcessHistoryId = ProcessDefinition_ProcessLinkHistory_1.ProcessHistoryId
                            WHERE(ProcessDefinition_PublishProcess_1.PublishId = @publishid)
                                 AND (ProcessDefinition_PublishProcess_1.ProcessHistoryId = @processhistoryid)
                           ) AS LinksTo ON LinksFROM.LinksToProcessId = LinksTo.ProcessId
             GROUP BY LinksFROM.ProcessId,
                      LinksFROM.SectionName,
                      LinksFROM.LinkText,
                      LinksFROM.ProcessTitle,
                      LinksFROM.LinksToProcessId,
                      LinksFROM.PrettyPostTitle,
                      LinksFROM.DescriptionSnippet,
                      LinksFROM.ProcessTypeId,
                      LinksFROM.LinkType,
                      LinksFROM.SectionDirection
             UNION
             SELECT FromProcess.ProcessId,
                    'Steps' AS SectionName,
                    FromProcess.StepName AS LinkText,
                    FromProcess.ProcessTitle,
                    FromProcess.PrettyPostTitle,
                    FromProcess.DescriptionSnippet,
                    FromProcess.ProcessTypeId,
                    CASE
                        WHEN((FromProcess.StepType&4) > 0)
                        THEN 4
                        ELSE 0
                    END AS LinkType,
                    '' AS SectionDirection
             FROM
                 (
                  SELECT ProcessDefinition_StepHistory.ChildProcessId,
                         ProcessDefinition_ProcessHistory.ProcessId,
                         ProcessDefinition_StepHistory.StepName,
                         ProcessDefinition_ProcessHistory.ProcessTitle,
                         ProcessDefinition_ProcessHistory.PrettyPostTitle,
                         ProcessDefinition_ProcessHistory.DescriptionSnippet,
                         ProcessDefinition_ProcessHistory.ProcessTypeId,
                         ProcessDefinition_StepHistory.StepType
                  FROM ProcessDefinition_StepHistory
                       INNER JOIN ProcessDefinition_PublishProcess ON ProcessDefinition_StepHistory.ProcessHistoryId = ProcessDefinition_PublishProcess.ProcessHistoryId
                  INNER JOIN ProcessDefinition_ProcessHistory ON ProcessDefinition_StepHistory.ProcessHistoryId = ProcessDefinition_ProcessHistory.ProcessHistoryId
                  WHERE(ProcessDefinition_PublishProcess.PublishId = @publishid)
                 ) AS FromProcess
                 INNER JOIN
                           (
SELECT ProcessHistoryId, ProcessId
FROM  ProcessDefinition_PublishProcess AS ProcessDefinition_PublishProcess_1 WHERE(ProcessDefinition_PublishProcess_1.PublishId = @publishid)
                                 AND (ProcessDefinition_PublishProcess_1.ProcessHistoryId = @processhistoryid)
                           ) AS LinksTo ON FromProcess.ChildProcessId = LinksTo.ProcessId
             ) AS Links
         ORDER BY Links.SectionName,
                  Links.ProcessTitle;
     END;
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Altering [dbo].[ProcessDefinition_GetProcessesByTag]'
GO
ALTER PROCEDURE [dbo].[ProcessDefinition_GetProcessesByTag]
	@repositoryName nvarchar(256),
	@tagName nvarchar(256),
	@userId int,
	@usePublished bit
WITH ENCRYPTION AS
BEGIN
 
               SET NOCOUNT ON;

	IF(@usePublished=0)
	BEGIN
	    SELECT     ProcessDefinition_Process.ProcessTitle, ProcessDefinition_Process.PrettyPostTitle, ProcessDefinition_Process.DescriptionSnippet,
		    dbo.ProcessDefinition_HasProcessAccess(ProcessId, @UserId) AS HasPostAccess
	    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 AND
	    (
		    (Notes_Tag.TagName = @tagName) 
			    OR 
		    ((Notes_Tag.TagName IS NULL) AND (@tagName='untagged'))
	    )
	    ORDER BY Notes_Tag.TagName, ProcessDefinition_Process.ProcessTitle
	END
	ELSE
	BEGIN

	   DECLARE @repositoryId int
	   SET @repositoryId=dbo.ProcessDefinition_GetRepositoryId(@RepositoryName)

	   DECLARE @lastPublishedId int

	   SELECT @lastPublishedId=MAX(PublishId)
	   FROM  ProcessDefinition_Publish
	   WHERE (RepositoryId = @repositoryId)

	    SELECT     ProcessDefinition_ProcessHistory.ProcessTitle, ProcessDefinition_ProcessHistory.PrettyPostTitle, ProcessDefinition_ProcessHistory.DescriptionSnippet,
		    dbo.ProcessDefinition_HasProcessAccess(ProcessDefinition_ProcessHistory.ProcessId, @UserId) AS HasPostAccess
FROM  ProcessDefinition_ProcessHistory INNER JOIN
         ProcessDefinition_PublishProcess ON ProcessDefinition_ProcessHistory.ProcessHistoryId = ProcessDefinition_PublishProcess.ProcessHistoryId INNER JOIN
         ProcessDefinition_Repository ON ProcessDefinition_ProcessHistory.ForeignId = ProcessDefinition_Repository.RepositoryId LEFT OUTER JOIN
         Notes_PublishedTag INNER JOIN
         Notes_PublishedAssignedTag ON Notes_PublishedTag.TagId = Notes_PublishedAssignedTag.TagId ON ProcessDefinition_PublishProcess.ProcessId = Notes_PublishedAssignedTag.ForeignId	    
	    WHERE     (ProcessDefinition_Repository.RepositoryName = @repositoryName) AND IsDeleted=0 AND
	    (
		    (Notes_PublishedTag.TagName = @tagName) 
			    OR 
		    ((Notes_PublishedTag.TagName IS NULL) AND (@tagName='untagged'))
	    )
	    AND
	    (
	    Notes_PublishedAssignedTag.PublishId=@lastPublishedId AND Notes_PublishedTag.PublishId=@lastPublishedId
	    )
	    ORDER BY Notes_PublishedTag.TagName, ProcessDefinition_ProcessHistory.ProcessTitle

	END
END
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Altering [dbo].[ProcessDefinition_GetHistorySteps]'
GO
ALTER PROCEDURE [dbo].[ProcessDefinition_GetHistorySteps] 
	@ProcessHistoryId int
WITH ENCRYPTION AS
BEGIN
			SET NOCOUNT ON;
	
	SELECT	ISNULL(ProcessDefinition_StepHistory.StepName, 
			FreezeCorrectProcesses.ProcessTitle) AS StepName, 
			ProcessDefinition_StepHistory.StepDescription, 
			ProcessDefinition_StepHistory.ChildProcessId, 
			ProcessDefinition_StepHistory.StepType, 
			ProcessDefinition_StepHistory.IterationDescription, 
			ProcessDefinition_StepHistory.OptionalDescription, 
			FreezeCorrectProcesses.PrettyPostTitle, 
			ProcessDefinition_StepHistory.ProcessStepId,
			AltGroup,
			FreezeCorrectProcesses.ProcessTypeId,
			ProcessDefinition_StepHistory.bpmnJson
	FROM ProcessDefinition_StepHistory LEFT OUTER JOIN
		(
SELECT ProcessDefinition_ProcessHistory.ProcessId, ProcessDefinition_ProcessHistory.ProcessTitle, ProcessDefinition_ProcessHistory.PrettyPostTitle, ProcessDefinition_ProcessHistory.AltGroup, ProcessDefinition_ProcessHistory.ProcessTypeId
FROM  ProcessDefinition_PublishProcess INNER JOIN
         ProcessDefinition_ProcessHistory ON ProcessDefinition_PublishProcess.ProcessHistoryId = ProcessDefinition_ProcessHistory.ProcessHistoryId INNER JOIN
             (SELECT MAX(PublishId) AS LastPublishId
            FROM  ProcessDefinition_PublishProcess AS ProcessDefinition_PublishProcess_1
            WHERE (ProcessHistoryId = @ProcessHistoryId)) AS LastPublish ON ProcessDefinition_PublishProcess.PublishId = LastPublish.LastPublishId

		) AS FreezeCorrectProcesses ON ProcessDefinition_StepHistory.ChildProcessId = FreezeCorrectProcesses.ProcessId 
	WHERE (ProcessDefinition_StepHistory.ProcessHistoryId = @ProcessHistoryId)
	ORDER BY ProcessDefinition_StepHistory.StepPosition


END
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Altering [dbo].[Notes_GetAssignedTags]'
GO
ALTER PROCEDURE [dbo].[Notes_GetAssignedTags]
		@ForeignId int,
	@ForeignType int
WITH ENCRYPTION AS
BEGIN
			SET NOCOUNT ON;

	IF(@ForeignType=22)
	BEGIN

	   DECLARE @lastPublishedId int
	   SELECT @lastPublishedId=MAX(ProcessDefinition_Publish.PublishId)
	   FROM  ProcessDefinition_Publish INNER JOIN
			  ProcessDefinition_Repository ON ProcessDefinition_Publish.RepositoryId = ProcessDefinition_Repository.RepositoryId INNER JOIN
			  ProcessDefinition_Process ON ProcessDefinition_Repository.RepositoryId = ProcessDefinition_Process.ForeignId
	   WHERE (ProcessDefinition_Process.ForeignType = 7) AND (ProcessDefinition_Process.ProcessId = @ForeignId)


	    SELECT     Notes_PublishedTag.TagName, Notes_PublishedAssignedTag.AssignedTagId, TagUsage.AssignedTagCount, Notes_PublishedAssignedTag.TagId
	    FROM         Notes_PublishedAssignedTag INNER JOIN
							 Notes_PublishedTag ON Notes_PublishedAssignedTag.TagId = Notes_PublishedTag.TagId INNER JOIN
								 (SELECT     TagId, COUNT(AssignedTagId) AS AssignedTagCount
								    FROM          Notes_PublishedAssignedTag AS Notes_AssignedTag_1
								    GROUP BY TagId) AS TagUsage ON Notes_PublishedAssignedTag.TagId = TagUsage.TagId
	    WHERE     (Notes_PublishedAssignedTag.ForeignId = @ForeignId) AND (Notes_PublishedAssignedTag.ForeignType = 144021)
			 AND (Notes_PublishedAssignedTag.PublishId=@lastPublishedId) AND (Notes_PublishedTag.PublishId=@lastPublishedId)
	    ORDER BY Notes_PublishedTag.TagName

	END
	ELSE
	BEGIN
	    SELECT     Notes_Tag.TagName, Notes_AssignedTag.AssignedTagId, TagUsage.AssignedTagCount, Notes_AssignedTag.TagId
	    FROM         Notes_AssignedTag INNER JOIN
							 Notes_Tag ON Notes_AssignedTag.TagId = Notes_Tag.TagId INNER JOIN
								 (SELECT     TagId, COUNT(AssignedTagId) AS AssignedTagCount
								    FROM          Notes_AssignedTag AS Notes_AssignedTag_1
								    GROUP BY TagId) AS TagUsage ON Notes_AssignedTag.TagId = TagUsage.TagId
	    WHERE     (Notes_AssignedTag.ForeignId = @ForeignId) AND (Notes_AssignedTag.ForeignType = @ForeignType)
	    ORDER BY Notes_Tag.TagName
	END
END

GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Altering [dbo].[ProcessDefinition_GetProcessesByTitle]'
GO
 

ALTER PROCEDURE [dbo].[ProcessDefinition_GetProcessesByTitle]
(
     @repositoryName nvarchar(256),
     @PostTitle nvarchar(256),
	@ProjectId int, 	@TaskId int ,
	@UsePublished bit
)
WITH ENCRYPTION AS
BEGIN
 
               SET NOCOUNT ON;

	DECLARE @RepositoryId int
	SET @RepositoryId=dbo.ProcessDefinition_GetRepositoryId(@repositoryName)
     IF(@projectId IS NULL AND @TaskId IS NULL AND @UsePublished=1)
	BEGIN

	   DECLARE @lastPublishedId int
	   SELECT @lastPublishedId=MAX(PublishId)
	   FROM  ProcessDefinition_Publish
	   WHERE (RepositoryId = @RepositoryId)

	   SELECT ProcessDefinition_ProcessHistory.ForeignType, ProcessDefinition_ProcessHistory.ForeignId, ProcessDefinition_ProcessHistory.DateCreated, ProcessDefinition_ProcessHistory.ProcessDescription, ProcessDefinition_ProcessHistory.ProcessTitle, ProcessDefinition_ProcessHistory.IsPublished, ProcessDefinition_ProcessHistory.ProcessId, 
			  ProcessDefinition_ProcessHistory.MajorVersion, ProcessDefinition_ProcessHistory.MinorVersion, ProcessDefinition_ProcessHistory.ProcessTypeId, ProcessDefinition_ProcessType.ProcessTypeName, ProcessDefinition_ProcessHistory.DateFrozen, ProcessDefinition_ProcessHistory.AltGroup,
			  ProcessDefinition_ProcessHistory.ProcessHistoryId
	   FROM  ProcessDefinition_ProcessHistory INNER JOIN
			  ProcessDefinition_ProcessType ON ProcessDefinition_ProcessHistory.ProcessTypeId = ProcessDefinition_ProcessType.ProcessTypeId INNER JOIN
			  ProcessDefinition_PublishProcess ON ProcessDefinition_ProcessHistory.ProcessHistoryId = ProcessDefinition_PublishProcess.ProcessHistoryId
	   WHERE (ProcessDefinition_PublishProcess.PublishId = @lastPublishedId)		
		  AND     (ProcessDefinition_ProcessHistory.PrettyPostTitle LIKE @PostTitle) 
		  AND (ProcessDefinition_ProcessHistory.ForeignId = @RepositoryId)
	END
	ELSE 
     IF(@projectId IS NULL AND @TaskId IS NULL)
	BEGIN
		SELECT     ProcessDefinition_Process.ForeignType, ProcessDefinition_Process.ForeignId, ProcessDefinition_Process.DateCreated, 
							  ProcessDefinition_Process.ProcessDescription, ProcessDefinition_Process.ProcessTitle, ProcessDefinition_Process.IsPublished, 
							  ProcessDefinition_Process.ProcessId, ProcessDefinition_Process.MajorVersion, ProcessDefinition_Process.MinorVersion, 
							  ProcessDefinition_Process.ProcessTypeId, ProcessDefinition_ProcessType.ProcessTypeName, DateFrozen, AltGroup,
							  NULL AS ProcessHistoryId
		FROM         ProcessDefinition_Process INNER JOIN
							  ProcessDefinition_ProcessType ON ProcessDefinition_Process.ProcessTypeId = ProcessDefinition_ProcessType.ProcessTypeId
		WHERE     (ProcessDefinition_Process.PrettyPostTitle LIKE @PostTitle) AND (ProcessDefinition_Process.ForeignId = @RepositoryId)

	END
	ELSE IF(@TaskId IS NULL)
	BEGIN

		SELECT     ProcessDefinition_ProcessHistory.ForeignType, ProcessDefinition_ProcessHistory.ForeignId, ProcessDefinition_ProcessHistory.DateCreated, 
							  ProcessDefinition_ProcessHistory.ProcessDescription, ProcessDefinition_ProcessHistory.ProcessTitle, ProcessDefinition_ProcessHistory.IsPublished, 
							  ProcessDefinition_ProcessHistory.ProcessId, ProcessDefinition_ProcessHistory.MajorVersion, ProcessDefinition_ProcessHistory.MinorVersion, 
							  ProcessDefinition_ProjectProcessType.ProcessTypeId, ProcessDefinition_ProjectProcessType.ProcessTypeName, DateFrozen, AltGroup,
							  NULL AS ProcessHistoryId
		FROM         ProcessDefinition_ProjectProcessType INNER JOIN
							  ProcessDefinition_ProjectProcessVersion INNER JOIN
							  ProcessDefinition_ProcessHistory ON ProcessDefinition_ProjectProcessVersion.ProcessHistoryId = ProcessDefinition_ProcessHistory.ProcessHistoryId ON 
							  ProcessDefinition_ProjectProcessType.ProcessTypeId = ProcessDefinition_ProcessHistory.ProcessTypeId
		WHERE     (ProcessDefinition_ProjectProcessVersion.ProjectPlanId = @projectId) AND (ProcessDefinition_ProcessHistory.PrettyPostTitle LIKE @PostTitle) AND 
							  (ProcessDefinition_ProjectProcessType.ProjectPlanId = @projectId)

	END
	ELSE
	BEGIN
		
	    SELECT     ProcessDefinition_ProcessHistory.ForeignType, ProcessDefinition_ProcessHistory.ForeignId, ProcessDefinition_ProcessHistory.DateCreated, 
							 ProcessDefinition_ProcessHistory.ProcessDescription, ProcessDefinition_ProcessHistory.ProcessTitle, ProcessDefinition_ProcessHistory.IsPublished, 
							 ProcessDefinition_ProcessHistory.ProcessId, ProcessDefinition_ProcessHistory.MajorVersion, ProcessDefinition_ProcessHistory.MinorVersion, 
							 ProcessDefinition_ProjectProcessType.ProcessTypeId, ProcessDefinition_ProjectProcessType.ProcessTypeName, 
							 ProcessDefinition_ProcessHistory.DateFrozen, AltGroup,ProcessDefinition_ProcessHistory.ProcessHistoryId
	    FROM         ProcessDefinition_ProjectProcessType INNER JOIN
							 ProcessDefinition_ProcessHistory ON ProcessDefinition_ProjectProcessType.ProcessTypeId = ProcessDefinition_ProcessHistory.ProcessTypeId INNER JOIN
							 ProcessDefinition_ProjectPlanTask ON ProcessDefinition_ProcessHistory.ProcessHistoryId = ProcessDefinition_ProjectPlanTask.ProcessHistoryId
	    WHERE     (ProcessDefinition_ProjectPlanTask.ProjectPlanTaskId = @TaskId)

	END
	
END
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[ProcessDefinition_GetLastPublishedId]'
GO
CREATE PROCEDURE [dbo].[ProcessDefinition_GetLastPublishedId]
    @RepositoryName nvarchar(256)
WITH ENCRYPTION AS
BEGIN
			SET NOCOUNT ON;

	DECLARE @repositoryId int
	SET @repositoryId=dbo.ProcessDefinition_GetRepositoryId(@RepositoryName)

    DECLARE @lastPublishedId int

    SELECT MAX(PublishId) AS LastPublishId
    FROM  ProcessDefinition_Publish
    WHERE (RepositoryId = @repositoryId)


END
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[ProcessDefinition_GetLastPublishedProcessHistory]'
GO
CREATE PROCEDURE [dbo].[ProcessDefinition_GetLastPublishedProcessHistory]
    @ProcessId int,
    @RepositoryName nvarchar(256)
WITH ENCRYPTION AS
BEGIN
			SET NOCOUNT ON;

	DECLARE @repositoryId int
	SET @repositoryId=dbo.ProcessDefinition_GetRepositoryId(@RepositoryName)

    DECLARE @lastPublishedId int

    SELECT @lastPublishedId=MAX(PublishId)
    FROM  ProcessDefinition_Publish
    WHERE (RepositoryId = @repositoryId)

    SELECT ProcessHistoryId
    FROM  ProcessDefinition_PublishProcess
    WHERE (PublishId = @lastPublishedId) AND (ProcessId = @ProcessId)

END
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Altering [dbo].[Search_RepositorySearch]'
GO
ALTER PROCEDURE [dbo].[Search_RepositorySearch] 
	@searchFor nvarchar(500),
	@repositoryName nvarchar(256),
	@UserId int,
	@usePublished bit
WITH ENCRYPTION AS
BEGIN
			SET NOCOUNT ON;
	DECLARE @StrippedTitle nvarchar(500)
	SET @StrippedTitle=REPLACE(@searchFor,'"','')

	IF(@usePublished=0)
	BEGIN
	    SELECT			ProcessDefinition_ProcessHistory_1.ProcessId,
					    ProcessDefinition_ProcessHistory_1.PrettyPostTitle,
					    ProcessDefinition_ProcessHistory_1.MinorVersion, 
					    ProcessDefinition_ProcessHistory_1.IsLatestVersion,
					    ProcessDefinition_ProcessHistory_1.ProcessTitle,
					    ProcessDefinition_ProcessHistory_1.DescriptionSnippet,
					    1 AS HasPostAccess
	    FROM         (
		    SELECT     TOP (100) PERCENT ProcessDefinition_ProcessHistory.ProcessId, MAX(ProcessDefinition_ProcessHistory.MinorVersion) AS LatestMinorVersion
			    FROM          ProcessDefinition_ProcessHistory INNER JOIN
				    ProcessDefinition_Repository ON ProcessDefinition_ProcessHistory.ForeignId = ProcessDefinition_Repository.RepositoryId
			    WHERE     (ProcessDefinition_Repository.RepositoryName = @repositoryName) AND
						    CONTAINS(ProcessDefinition_ProcessHistory.*, @searchFor)
							  GROUP BY ProcessDefinition_ProcessHistory.ProcessId
							  ) AS LatestFoundProcesses INNER JOIN
				    ProcessDefinition_ProcessHistory AS ProcessDefinition_ProcessHistory_1 ON 
							 LatestFoundProcesses.LatestMinorVersion = ProcessDefinition_ProcessHistory_1.MinorVersion AND 
							 LatestFoundProcesses.ProcessId = ProcessDefinition_ProcessHistory_1.ProcessId INNER JOIN
				    ProcessDefinition_Process ON LatestFoundProcesses.ProcessId = ProcessDefinition_Process.ProcessId
	    WHERE ProcessDefinition_Process.IsDeleted=0
	    ORDER BY CASE WHEN ProcessDefinition_ProcessHistory_1.ProcessTitle=@StrippedTitle THEN 1 ELSE 2 END,ProcessDefinition_ProcessHistory_1.ProcessTitle
    END
    ELSE
    BEGIN

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

	   DECLARE @lastPublishedId int

	   SELECT @lastPublishedId=MAX(PublishId)
	   FROM  ProcessDefinition_Publish
	   WHERE (RepositoryId = @repositoryId)

			 SELECT ProcessDefinition_ProcessHistory_1.ProcessId, ProcessDefinition_ProcessHistory_1.PrettyPostTitle, ProcessDefinition_ProcessHistory_1.MinorVersion, ProcessDefinition_ProcessHistory_1.IsLatestVersion, ProcessDefinition_ProcessHistory_1.ProcessTitle, ProcessDefinition_ProcessHistory_1.DescriptionSnippet, 1 AS HasPostAccess
	   FROM  (SELECT TOP (100) PERCENT ProcessDefinition_ProcessHistory.ProcessId, MAX(ProcessDefinition_ProcessHistory.MinorVersion) AS LatestMinorVersion
			  FROM  ProcessDefinition_ProcessHistory INNER JOIN
					 ProcessDefinition_Repository ON ProcessDefinition_ProcessHistory.ForeignId = ProcessDefinition_Repository.RepositoryId
			  WHERE (ProcessDefinition_Repository.RepositoryName = @repositoryName) AND
								  CONTAINS(ProcessDefinition_ProcessHistory.*, @searchFor)
			  GROUP BY ProcessDefinition_ProcessHistory.ProcessId) AS LatestFoundProcesses INNER JOIN
			  ProcessDefinition_ProcessHistory AS ProcessDefinition_ProcessHistory_1 ON LatestFoundProcesses.LatestMinorVersion = ProcessDefinition_ProcessHistory_1.MinorVersion AND LatestFoundProcesses.ProcessId = ProcessDefinition_ProcessHistory_1.ProcessId INNER JOIN
			  ProcessDefinition_Process ON LatestFoundProcesses.ProcessId = ProcessDefinition_Process.ProcessId INNER JOIN
			  ProcessDefinition_PublishProcess ON ProcessDefinition_ProcessHistory_1.ProcessHistoryId = ProcessDefinition_PublishProcess.ProcessHistoryId
	   WHERE (ProcessDefinition_Process.IsDeleted = 0) AND (ProcessDefinition_PublishProcess.PublishId = @lastPublishedId)
	   ORDER BY CASE WHEN ProcessDefinition_ProcessHistory_1.ProcessTitle = @StrippedTitle THEN 1 ELSE 2 END, ProcessDefinition_ProcessHistory_1.ProcessTitle

    END


END
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
COMMIT TRANSACTION
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Adding full text indexing to tables'
GO
CREATE FULLTEXT INDEX ON [dbo].[ProcessDefinition_StepHistory] KEY INDEX [PK_ProcessDefinition_StepHistory] ON [ProcessDefinitionFtIndex]
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Adding full text indexing to columns'
GO
ALTER FULLTEXT INDEX ON [dbo].[ProcessDefinition_StepHistory] ADD ([StepName] LANGUAGE 1033)
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
ALTER FULLTEXT INDEX ON [dbo].[ProcessDefinition_StepHistory] ADD ([StepDescription] LANGUAGE 1033)
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
ALTER FULLTEXT INDEX ON [dbo].[ProcessDefinition_StepHistory] ENABLE
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO

PRINT N'Altering [dbo].[ProcessDefinition_GetAutocompleteProcesses]'
GO
ALTER PROCEDURE [dbo].[ProcessDefinition_GetAutocompleteProcesses]
     @repositoryName nvarchar(256),
	@Name nvarchar(128),
	@UserId int,
	@usePublished bit
WITH ENCRYPTION AS
BEGIN
			SET NOCOUNT ON;

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

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

	if(@usePublished=0)
	BEGIN

	    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
    ELSE
    BEGIN
	   DECLARE @lastPublishedId int

	   SELECT @lastPublishedId=MAX(PublishId)
	   FROM  ProcessDefinition_Publish
	   WHERE (RepositoryId = @repositoryId)

	   SELECT ProcessId, ProcessTitle,PrettyPostTitle,HasPostAccess FROM
	    (
			 SELECT ProcessDefinition_ProcessHistory.ProcessId, ProcessDefinition_ProcessHistory.ProcessTitle, ProcessDefinition_ProcessHistory.PrettyPostTitle, dbo.ProcessDefinition_HasPostAccess(ProcessDefinition_ProcessHistory.PrettyPostTitle, @repositoryName, @UserId) AS HasPostAccess, 1 AS SortOrder
			 FROM  ProcessDefinition_ProcessHistory INNER JOIN
					ProcessDefinition_PublishProcess ON ProcessDefinition_ProcessHistory.ProcessHistoryId = ProcessDefinition_PublishProcess.ProcessHistoryId
			 WHERE (ProcessDefinition_PublishProcess.PublishId = @lastPublishedId)
    			 AND (ProcessTitle LIKE @Name) AND (ForeignId = @repositoryId) AND IsDeleted=0
		    UNION
			 SELECT ProcessDefinition_ProcessHistory.ProcessId, ProcessDefinition_ProcessHistory.ProcessTitle, ProcessDefinition_ProcessHistory.PrettyPostTitle, dbo.ProcessDefinition_HasPostAccess(ProcessDefinition_ProcessHistory.PrettyPostTitle, @repositoryName, @UserId) AS HasPostAccess, 2 AS SortOrder
			 FROM  ProcessDefinition_ProcessHistory INNER JOIN
					ProcessDefinition_PublishProcess ON ProcessDefinition_ProcessHistory.ProcessHistoryId = ProcessDefinition_PublishProcess.ProcessHistoryId
			 WHERE (ProcessDefinition_PublishProcess.PublishId = @lastPublishedId)		    
			 AND CONTAINS(ProcessDefinition_ProcessHistory.*,@rawSearch) AND (ForeignId = @repositoryId) AND IsDeleted=0
		    UNION
			 SELECT ProcessDefinition_ProcessHistory.ProcessId, ProcessDefinition_ProcessHistory.ProcessTitle, ProcessDefinition_ProcessHistory.PrettyPostTitle,dbo.ProcessDefinition_HasPostAccess(ProcessDefinition_ProcessHistory.PrettyPostTitle, @repositoryName, @UserId) AS HasPostAccess, 2 AS SortOrder
			 FROM  ProcessDefinition_ProcessHistory INNER JOIN
					ProcessDefinition_StepHistory ON ProcessDefinition_ProcessHistory.ProcessHistoryId = ProcessDefinition_StepHistory.ProcessHistoryId INNER JOIN
					ProcessDefinition_PublishProcess ON ProcessDefinition_ProcessHistory.ProcessHistoryId = ProcessDefinition_PublishProcess.ProcessHistoryId
			 WHERE (ProcessDefinition_PublishProcess.PublishId = @lastPublishedId)
						 AND CONTAINS(ProcessDefinition_StepHistory.*,@rawSearch) AND (ProcessDefinition_ProcessHistory.ForeignId = @repositoryId) AND ProcessDefinition_ProcessHistory.IsDeleted=0

	    ) AS X
	    GROUP BY ProcessId, ProcessTitle,PrettyPostTitle,HasPostAccess
	    ORDER BY MIN(SortOrder),CAST(ProcessTitle AS nvarchar(128))

    END
END
GO