/****** Object:  UserDefinedFunction [dbo].[fn_GetAccruedEntitlement]    Script Date: 20/06/2018 10:46:27 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[fn_GetAccruedEntitlement]
(
	-- Add the parameters for the function here
	@ContractStartDate datetime,
	@AccrualDate datetime,
	@SpecifyPlanNdx int,
	@WorkingPatternAtContractStartDate nvarchar(50),
	@WorkingPatternAtAccrualDate nvarchar(50),
	@IncludeAdjustments bit
)
RETURNS numeric(18,4)
AS
BEGIN
	-- Declare the return variable here
	DECLARE @Retval numeric(18,4)

	IF ((SELECT Plan_Type FROM DB_OSP_PLANS	WHERE Ndx = @SpecifyPlanNdx) <> 'Holiday')
	BEGIN
		SET @Retval = 0
		RETURN @Retval
	END

	SET @Retval = (SELECT 

	(SELECT ISNULL(
				CASE (SELECT Leaver_Rounding FROM REF_OSP_PLAN_TEMPLATES WHERE Plan_Name = PLANS.Plan_Name)
				WHEN 0 THEN CEILING(IQ.Ent * 2) / 2 --CalcRoundUpToHalfDay 
				WHEN 1 THEN ROUND(2 * IQ.Ent, 0) / 2 --CalcRoundToNearestHalfDay 
				WHEN 2 THEN FLOOR(IQ.Ent) --CalcRoundDownToWholeDay 
				WHEN 3 THEN CEILING(IQ.Ent) --CalcRoundUpToWholeDay 
				WHEN 4 THEN ROUND(IQ.Ent, 2) --CalcRoundTo2DP
				WHEN 5 THEN ROUND(IQ.Ent, 0) --CalcRoundToNearestWholeDay 
				ELSE CEILING(IQ.Ent * 2) / 2 --CalcRoundToNearestWholeUnlessPoint5
				END
			, 0)
	FROM (SELECT CAST([dbo].[ZeroCheckDivide](YBD.Full_Entitlement ,

		CASE Pro_Rata_Weeks_Of_Service 
		WHEN 1 THEN
			CASE WHEN DATEADD(yyyy, -1, YBD.End_Date) > @ContractStartDate
				THEN DATEDIFF(day, YBD.Start_Date, YBD.End_Date)
				ELSE DATEDIFF(day, @ContractStartDate, YBD.End_Date)
			END
		ELSE
			--Calc of Y 
			CASE WHEN DATEADD(yyyy, -1, YBD.End_Date) > @ContractStartDate 
				THEN 12
				ELSE DATEDIFF(Month,  --A to C
				(CASE 
					(CASE WHEN DAY(@ContractStartDate) <= 
						CASE (SELECT Pro_Rata_Rounding_Type FROM REF_OSP_PLAN_TEMPLATES WHERE Plan_Name = PLANS.Plan_Name)
						WHEN 1 THEN DAY((SELECT TOP 1 WhichDate FROM REF_SHIFT_DAYS RSD
										WHERE RSD.Plan_Name = @WorkingPatternAtContractStartDate
										AND YEAR(WhichDate) = YEAR(@ContractStartDate)
										AND MONTH(WhichDate) = MONTH(@ContractStartDate)
										ORDER BY WhichDate ASC))
						WHEN 2 THEN DAY(dbo.fn_OSP_FirstMondayOfMonth(@ContractStartDate))
						WHEN 3 THEN DAY((SELECT TOP 1 WhichDate FROM REF_SHIFT_DAYS RSD
										WHERE RSD.Plan_Name = @WorkingPatternAtContractStartDate
										AND YEAR(WhichDate) = YEAR(@ContractStartDate)
										AND MONTH(WhichDate) = MONTH(@ContractStartDate)
										AND WhichDate > dbo.fn_OSP_FirstMondayOfMonth(@ContractStartDate)
										ORDER BY WhichDate ASC))
						ELSE CAST((SELECT Pro_Rata_Rounding_Type - 100 FROM REF_OSP_PLAN_TEMPLATES WHERE Plan_Name = PLANS.Plan_Name) as int)
						END 
					THEN 1 ELSE 0 
					END)
				WHEN 1 THEN CAST(CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@ContractStartDate)-1), @ContractStartDate), 101) as datetime)
				ELSE CAST(CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1, @ContractStartDate))-1),DATEADD(mm,1, @ContractStartDate)),101) as datetime) 
				END)
				,
			DATEADD(dd,1, YBD.End_Date)
				)
			END
		END)
		* 
		--Calc of Z
		(SELECT MAX(Diff) FROM (VALUES (
			CASE Pro_Rata_Weeks_Of_Service 
			WHEN 1 THEN
				CASE WHEN DATEADD(yyyy, -1, YBD.End_Date) > @ContractStartDate
					THEN DATEDIFF(day, YBD.Start_Date, @AccrualDate)
					ELSE DATEDIFF(day, @ContractStartDate, @AccrualDate)
				END
			ELSE		
				(CASE WHEN DATEADD(yyyy, -1, YBD.End_Date) > @ContractStartDate 
				THEN DATEDIFF(Month,  --D to B
					YBD.Start_Date,
					(CASE 
						(CASE WHEN DAY(@AccrualDate) < 
							CASE (SELECT Pro_Rata_Rounding_Type_Leaver FROM REF_OSP_PLAN_TEMPLATES WHERE Plan_Name = PLANS.Plan_Name)
							WHEN 1 THEN DAY((SELECT TOP 1 WhichDate FROM REF_SHIFT_DAYS RSD
											WHERE RSD.Plan_Name = @WorkingPatternAtAccrualDate
											AND YEAR(WhichDate) = YEAR(@AccrualDate)
											AND MONTH(WhichDate) = MONTH(@AccrualDate)
											ORDER BY WhichDate DESC))
							WHEN 2 THEN DAY(dbo.fn_OSP_LastFridayOfMonth(@AccrualDate))
							WHEN 3 THEN DAY((SELECT TOP 1 WhichDate FROM REF_SHIFT_DAYS RSD
											WHERE RSD.Plan_Name = @WorkingPatternAtAccrualDate
											AND YEAR(WhichDate) = YEAR(@AccrualDate)
											AND MONTH(WhichDate) = MONTH(@AccrualDate)
											AND WhichDate < dbo.fn_OSP_LastFridayOfMonth(@AccrualDate)
											ORDER BY WhichDate DESC))
							ELSE CAST((SELECT Pro_Rata_Rounding_Type - 100 FROM REF_OSP_PLAN_TEMPLATES WHERE Plan_Name = PLANS.Plan_Name) as int)
							END 
						THEN 0 ELSE 1 
						END)
					WHEN 1 THEN CAST(CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1, @AccrualDate))-1),DATEADD(mm,1, @AccrualDate)),101) as datetime) 
					ELSE CAST(CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@AccrualDate)-1), @AccrualDate), 101) as datetime)
					END)
					)
				ELSE DATEDIFF(Month,  --A to B
					(CASE 
						(CASE WHEN DAY(@ContractStartDate) <= 
							CASE (SELECT Pro_Rata_Rounding_Type FROM REF_OSP_PLAN_TEMPLATES WHERE Plan_Name = PLANS.Plan_Name)
							WHEN 1 THEN DAY((SELECT TOP 1 WhichDate FROM REF_SHIFT_DAYS RSD
											WHERE RSD.Plan_Name = @WorkingPatternAtContractStartDate
											AND YEAR(WhichDate) = YEAR(@ContractStartDate)
											AND MONTH(WhichDate) = MONTH(@ContractStartDate)
											ORDER BY WhichDate ASC))
							WHEN 2 THEN DAY(dbo.fn_OSP_FirstMondayOfMonth(@ContractStartDate))
							WHEN 3 THEN DAY((SELECT TOP 1 WhichDate FROM REF_SHIFT_DAYS RSD
											WHERE RSD.Plan_Name = @WorkingPatternAtContractStartDate
											AND YEAR(WhichDate) = YEAR(@ContractStartDate)
											AND MONTH(WhichDate) = MONTH(@ContractStartDate)
											AND WhichDate > dbo.fn_OSP_FirstMondayOfMonth(@ContractStartDate)
											ORDER BY WhichDate ASC))
							ELSE CAST((SELECT Pro_Rata_Rounding_Type - 100 FROM REF_OSP_PLAN_TEMPLATES WHERE Plan_Name = PLANS.Plan_Name) as int)
							END 
						THEN 1 ELSE 0 
						END)
					WHEN 1 THEN CAST(CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@ContractStartDate)-1), @ContractStartDate), 101) as datetime)
					ELSE CAST(CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1, @ContractStartDate))-1),DATEADD(mm,1, @ContractStartDate)),101) as datetime) 
					END)
					,(CASE 
						(CASE WHEN DAY(@AccrualDate) <
							CASE (SELECT Pro_Rata_Rounding_Type_Leaver FROM REF_OSP_PLAN_TEMPLATES WHERE Plan_Name = PLANS.Plan_Name)
							WHEN 1 THEN DAY((SELECT TOP 1 WhichDate FROM REF_SHIFT_DAYS RSD
											WHERE RSD.Plan_Name = @WorkingPatternAtAccrualDate
											AND YEAR(WhichDate) = YEAR(@AccrualDate)
											AND MONTH(WhichDate) = MONTH(@AccrualDate)
											ORDER BY WhichDate DESC))
							WHEN 2 THEN DAY(dbo.fn_OSP_LastFridayOfMonth(@AccrualDate))
							WHEN 3 THEN DAY((SELECT TOP 1 WhichDate FROM REF_SHIFT_DAYS RSD
											WHERE RSD.Plan_Name = @WorkingPatternAtAccrualDate
											AND YEAR(WhichDate) = YEAR(@AccrualDate)
											AND MONTH(WhichDate) = MONTH(@AccrualDate)
											AND WhichDate < dbo.fn_OSP_LastFridayOfMonth(@AccrualDate)
											ORDER BY WhichDate DESC))
							ELSE CAST((SELECT Pro_Rata_Rounding_Type - 100 FROM REF_OSP_PLAN_TEMPLATES WHERE Plan_Name = PLANS.Plan_Name) as int)
							END 
						THEN 0 ELSE 1 
						END)
					WHEN 1 THEN CAST(CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1, @AccrualDate))-1),DATEADD(mm,1, @AccrualDate)),101) as datetime) 
					ELSE CAST(CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@AccrualDate)-1), @AccrualDate), 101) as datetime)
					END)
					)
				END)
			END
		), (0)) AS AllVals(Diff))
		
		+ CASE ISNULL(@IncludeAdjustments,0) WHEN 0 THEN 0 ELSE ISNULL(YBD.Full_Adjusted,0) + ISNULL(YBD.Brought_Forward_Amount,0) END

		AS NUMERIC(18,4))

 AS Ent
) AS IQ)

	FROM [VW_OSP_PLANS] PLANS

	INNER JOIN DB_OSP_YEARLY_BREAKDOWN YBD
	ON YBD.Parent_Id = PLANS.Ndx
	AND @AccrualDate BETWEEN YBD.Start_Date AND ISNULL(YBD.End_Date, '31 dec 2499')

	WHERE PLANS.Ndx = @SpecifyPlanNdx
)
	-- Return the result of the function
	RETURN @Retval

END








GO