SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF object_id(N'fn_Contract_SubordinateCounts', N'TF') IS NOT NULL
    DROP FUNCTION fn_Contract_SubordinateCounts
GO

CREATE FUNCTION [dbo].[fn_Contract_SubordinateCounts](@PIT datetime = NULL)
RETURNS 
@retVal TABLE 
(	SubContractID int
	, SubJobID int
	, SupManagerID int
	, DirectReportCount int
	, IndirectReportCount int )
AS
BEGIN

	SET @PIT = ISNULL(@PIT, GETDATE());

	DECLARE @Jobs TABLE(SubContractID int, SubJobID int, SupManagerID int, DirectReportCount int )

	INSERT INTO @Jobs
	SELECT c.Entity_Id 
			, CAST(Job as int)
			, fcm.Right_Join
			, NULL
	FROM DT_CONTRACT c
	LEFT OUTER JOIN fn_Contract_Manager(NULL, NULL) fcm 
	ON c.Entity_Id = fcm.Left_Join
	AND CONVERT(datetime, @PIT) BETWEEN fcm.Start_Date and ISNULL(fcm.End_Date, '31 dec 2499')
	WHERE CONVERT(datetime, @PIT) BETWEEN c.Entity_Start_Date and ISNULL(c.Entity_End_Date, '31 dec 2499')

	UPDATE JobList
	SET DirectReportCount = (SELECT COUNT(SubContractID) FROM @Jobs a WHERE a.SupManagerID = JobList.SubJobID)
	FROM @Jobs JobList

	;WITH ChildrenCTE AS (
	  SELECT  SubJobID AS RootID
			, SubJobID
	  FROM    @Jobs
	  UNION ALL
	  SELECT  cte.RootID, d.SubJobID
	  FROM    ChildrenCTE cte
			  INNER JOIN @Jobs d ON d.SupManagerID = cte.SubJobID
	)
	INSERT INTO @retVal
	SELECT  d.SubContractID, d.SubJobID, d.SupManagerID, d.DirectReportCount, cnt.IndirectReportCount
	FROM    @Jobs d
			INNER JOIN (
			  SELECT  RootID
					, COUNT(*) - 1 AS IndirectReportCount
			  FROM    ChildrenCTE
			  GROUP BY RootID
			) cnt ON cnt.RootID = d.SubJobID
		ORDER BY IndirectReportCount DESC	RETURN 
END
GO

UPDATE [dbo].[REF_SQLFIELDS] SET [Code]=N'sc.DirectReportCount{{J}}LEFT OUTER JOIN fn_Contract_SubordinateCounts([PIT]) sc
ON sc.SubContractID = RT.Entity_Id
' WHERE [Table_Name] = N'DT_CONTRACT' AND [Field_Name] = N'SubordinateCount' AND [CustomisationID] = 0
INSERT INTO [dbo].[REF_SQLFIELDS] ([Table_Name], [Field_Name], [CustomisationID], [Version], [Code], [OnByDefault], [InLine]) VALUES (N'DT_CONTRACT', N'IndirectSubordinateCount', 0, 1, N'sc.IndirectReportCount{{J}}[#J#SubordinateCount]', 0, 1)

UPDATE [dbo].[REF_CUSTOM_CONTROL] SET [Caption]=N'Direct Subordinate Count', [Enabled]=0, [ControlType]=4, [ToolTipText]=N'Count of employees directly subordinate to this person.' WHERE [TableName] = N'DT_CONTRACT' AND [FieldName] = N'SUBORDINATECOUNT' AND [CustomisationID] = 0
INSERT INTO [dbo].[REF_CUSTOM_CONTROL] ([TableName], [FieldName], [CustomisationID], [Caption], [Enabled], [ControlTop], [ControlLeft], [ControlWidth], [ControlHeight], [Mask], [DefaultValue], [ControlType], [Compulsory], [LookupID], [ToolTipText], [ControlBackColor], [FontColor], [FontStyle], [FontBold], [FontItalic], [FontUnderline], [FontSize], [Visible], [MaximumLength], [CharacterTable], [Characters], [ShowCodesInCombo], [Justification], [MinValue], [MaxValue], [IntDigits], [DecDigits], [ValidateMask], [ShowZero], [fmtThousands], [EntityUnique], [TreatZeroAsBlank], [ObjectChooserTable], [OverideComboTextField], [MiscSelectorTable], [WidthOffset], [DisplayInHeader], [KeyField], [LabelAbove], [HideLabelOnFrame], [ImageMode], [ImageFilePattern], [ScaleImage], [MaintainAspect], [MiscSelectorSortFields], [MailMergeReport], [NULLDisplayField], [DisplayTab], [SecurityIdentifier], [ExcelTextOutput], [Hidden], [Locked], [DataProtectionTrigger]) VALUES (N'DT_CONTRACT', N'INDIRECTSUBORDINATECOUNT', 0, N'Indirect Subordinate Count', 0, 0, 0, 4, 1, N'', NULL, 4, 0, NULL, N'Count of all employees subordinate to this person including those under managers lower down the hierarchy.', -2147483633, -2147483630, N'Calibri', 0, 0, 0, 9, 1, 0, N'', -1, 0, 0, NULL, NULL, NULL, NULL, 0, 1, N',', 0, 0, NULL, NULL, NULL, 0, 0, 0, 0, 0, 0, N'', 0, 0, NULL, NULL, NULL, NULL, NULL, 0, 0, 0, 0)