/*

Pentana Audit - EntityProcess.ChildCount Check.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 checks EntityProcess.ChildCount, so that you can see whether it is necessary to reset the counts (using Pentana Audit - EntityProcess.ChildCount Reset.sql)    
	
Changes:

To run:

    This script does not make changes to data.
 
*/

-- Go over each type of child record and find the count of non-closed/approved records, then list
    DECLARE @id AS int
    DECLARE @count1 AS int
    DECLARE @count2 AS int
	DECLARE @name AS NVARCHAR(255)

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

    OPEN EntityProcesses
    FETCH EntityProcesses INTO @id, @count1, @name
    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
                
                    -- count is incorrect
                    PRINT 'Entity ID ' + CONVERT(varchar, @id) + ' (' + @name + '): recorded value = ' + CONVERT(varchar, @count1) + ', actual count = ' + CONVERT(varchar, @count2)

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

        END

    DEALLOCATE EntityProcesses