/*

Pentana Audit - EntityProcess.ChildCount Reset.sql

Written by Ideagen (email: support-pentana-audit@ideagen.com)

Purpose of script:
	The field EntityProcess.ChildCount is incremented by 1 each time a matching scope is added to an Entity Objective, Incident, Key Issue, Audit, Problem, Finding or Action.
    Similarly, if a scope is removed from a record, or that record is set to Closed (for Entity Objective, Incident, Key Issue, Audit) or Approved (for Problem, Finding, Action), then 1 is deducted from the count.

    Sometimes the ChildCount value can get out of alignment with the actual count, (normally identified where it is not possible to unmap an EntityProcess even where there are no dependent records).

    This script recalculates EntityProcess.ChildCount so that everything lines back up correctly.    
	
Changes:

To run:

- stop the Retain Server service (where present)
- stop the Pentana Audit robot service
- stop the Pentana application pools
- back up the Pentana database and verify
- back up the Retain database and verify (where present)
- run this script - it should run without error
- restart the Retain service (where present)
- restart the Pentana app pools
- restart the Pentana Audit robot service
- verify effect of change
 
*/

-- Make a backup of the EntityProcess values we are changing, in case of troubleshooting later
	DECLARE @TableNameEP AS VARCHAR(50) = 'zEntityProcess_' + (SELECT format(getdate(),'yyyyMMddHHmmss'))
	DECLARE @sqlEP AS VARCHAR(MAX)

	SET @sqlEP = 'SELECT ID, ChildCount, Name INTO ' + @TableNameEP + ' FROM EntityProcess;';
	EXEC(@sqlEP);


-- Go over each type of child record and find the count of non-closed/approved records, then update EntityProcess.ChildCount
    DECLARE @id AS int
    DECLARE @count1 AS int
    DECLARE @count2 AS int

    -- for each entity/process
    DECLARE EntityProcesses CURSOR FOR 
        SELECT [ID], [ChildCount] FROM [EntityProcess] -- no where clause

    OPEN EntityProcesses
    FETCH EntityProcesses INTO @id, @count1
    WHILE @@FETCH_STATUS = 0
        
        BEGIN
                
            SET @count2 = 0

            -- add each (active) child count
            SET @count2 = @count2 + (SELECT COUNT(*) FROM [EntityObjective] WHERE [EntityProcessID] = @id AND [ActiveStateID] <> 3) -- exclude closed
            SET @count2 = @count2 + (SELECT COUNT(*) FROM [IncidentScope] LEFT JOIN [Incident] ON [SourceID] = [Incident].[ID] WHERE [TargetID] = @id AND [AuditStateID] <> 5) -- exclude closed
            SET @count2 = @count2 + (SELECT COUNT(*) FROM [KeyIssueScope] LEFT JOIN [KeyIssue] ON [SourceID] = [KeyIssue].[ID] WHERE [TargetID] = @id AND [AuditStateID] <> 5) -- exclude closed
            SET @count2 = @count2 + (SELECT COUNT(*) FROM [AuditScope] LEFT JOIN [Audit] ON [SourceID] = [Audit].[ID] WHERE [TargetID] = @id AND [AuditStateID] <> 5) -- exclude closed
            SET @count2 = @count2 + (SELECT COUNT(*) FROM [ProblemScope] LEFT JOIN [Problem] ON [SourceID] = [Problem].[ID] WHERE [TargetID] = @id AND [SignOffStateID] <> 3) -- exclude approved
            SET @count2 = @count2 + (SELECT COUNT(*) FROM [Finding] WHERE [EntityProcessID] = @id AND [SignOffStateID] <> 3) -- exclude approved
            SET @count2 = @count2 + (SELECT COUNT(*) FROM [Action] WHERE [EntityProcessID] = @id AND [SignOffStateID] <> 3) -- exclude approved 

            IF @count1 <> @count2
                
                BEGIN
                
                    -- update count
                    UPDATE [EntityProcess] SET [ChildCount] = @count2 WHERE [ID] = @id

                    -- count is incorrect
                    PRINT CONVERT(varchar, @id) + ' changed from ' + CONVERT(varchar, @count1) + ' to ' + CONVERT(varchar, @count2)

                END
                
            -- next entity/process
            FETCH EntityProcesses INTO @id, @count1

        END

    DEALLOCATE EntityProcesses

-- Register this in the Installlog
	-- Find whether the last entry was an install or upgrade and manage the FromVersion and ToVersion appropiately
	DECLARE @FromVersion NVARCHAR(25) = (SELECT TOP 1 FromVersion FROM InstallLog ORDER BY ID DESC);
	DECLARE @ToVersion NVARCHAR(25) = (SELECT TOP 1 ToVersion FROM InstallLog ORDER BY ID DESC);

	IF @ToVersion IS NULL
		INSERT INTO InstallLog (GUID, Name, Type, Installed, FromVersion, ToVersion, [User]) VALUES (NewID(), 'Pentana Audit - EntityProcess.ChildCount Reset.sql', 'Script', GETDATE(), @FromVersion, NULL, SYSTEM_USER);
	ELSE
		INSERT INTO InstallLog (GUID, Name, Type, Installed, FromVersion, ToVersion, [User]) VALUES (NewID(), 'Pentana Audit - EntityProcess.ChildCount Reset.sql', 'Script', GETDATE(), @ToVersion, NULL, SYSTEM_USER);
	GO


/*  

	-- If you want to compare the counts before and after for those that changed (note the table name needs correcting)

		SELECT E.ID AS ID, E.Name, Z.ChildCount AS BeforeScript, E.ChildCount AS AfterScript FROM zEntityProcess_xxxxxxxxxxxxxx AS Z
			INNER JOIN EntityProcess AS E
			ON Z.ID = E.ID
			WHERE Z.ChildCount <> E.ChildCount;

	-- If you want to see EntityProcesses that were previously not unmappable but which now are (note the table name needs correcting):

		SELECT E.ID AS ID, E.Name, Z.ChildCount AS BeforeScript, E.ChildCount AS AfterScript FROM zEntityProcess_xxxxxxxxxxxxxx AS Z
		INNER JOIN EntityProcess AS E
		ON Z.ID = E.ID
		WHERE Z.ChildCount <> E.ChildCount
		AND E.ChildCount = 0;

*/