SET NOCOUNT ON
-------------------------------------------------------------------------------
-- Table Modifications
-------------------------------------------------------------------------------
-- PayPer - you MUST manually set the PayPerID column to non-identity!!!
UPDATE PayPer
SET PayPerID = [Period]
WHERE PayPerID <> [Period]

IF NOT EXISTS(Select PayPerID FROM PayPer WHERE [Period] = 13)
BEGIN
	INSERT INTO PayPer(PayPerID,[Period])
	VALUES (13,13)
END

IF NOT EXISTS(Select PayPerID FROM PayPer WHERE [Period] = 14)
BEGIN
	INSERT INTO PayPer(PayPerID,[Period])
	VALUES (14,14)
END

IF NOT EXISTS(Select PayPerID FROM PayPer WHERE [Period] = 15)
BEGIN
	INSERT INTO PayPer(PayPerID,[Period])
	VALUES (15,15)
END

IF NOT EXISTS(Select PayPerID FROM PayPer WHERE [Period] = 16)
BEGIN
	INSERT INTO PayPer(PayPerID,[Period])
	VALUES (16,16)
END

IF NOT EXISTS(Select PayPerID FROM PayPer WHERE [Period] = 17)
BEGIN
	INSERT INTO PayPer(PayPerID,[Period])
	VALUES (17,17)
END

IF NOT EXISTS(Select PayPerID FROM PayPer WHERE [Period] = 18)
BEGIN
	INSERT INTO PayPer(PayPerID,[Period])
	VALUES (18,18)
END

IF NOT EXISTS(Select PayPerID FROM PayPer WHERE [Period] = 19)
BEGIN
	INSERT INTO PayPer(PayPerID,[Period])
	VALUES (19,19)
END

IF NOT EXISTS(Select PayPerID FROM PayPer WHERE [Period] = 20)
BEGIN
	INSERT INTO PayPer(PayPerID,[Period])
	VALUES (20,20)
END

IF NOT EXISTS(Select PayPerID FROM PayPer WHERE [Period] = 21)
BEGIN
	INSERT INTO PayPer(PayPerID,[Period])
	VALUES (21,21)
END

IF NOT EXISTS(Select PayPerID FROM PayPer WHERE [Period] = 22)
BEGIN
	INSERT INTO PayPer(PayPerID,[Period])
	VALUES (22,22)
END

IF NOT EXISTS(Select PayPerID FROM PayPer WHERE [Period] = 23)
BEGIN
	INSERT INTO PayPer(PayPerID,[Period])
	VALUES (23,23)
END

IF NOT EXISTS(Select PayPerID FROM PayPer WHERE [Period] = 24)
BEGIN
	INSERT INTO PayPer(PayPerID,[Period])
	VALUES (24,24)
END

IF NOT EXISTS(Select PayPerID FROM PayPer WHERE [Period] = 25)
BEGIN
	INSERT INTO PayPer(PayPerID,[Period])
	VALUES (25,25)
END

IF NOT EXISTS(Select PayPerID FROM PayPer WHERE [Period] = 26)
BEGIN
	INSERT INTO PayPer(PayPerID,[Period])
	VALUES (26,26)
END

SELECT * FROM PayPer

-------------------------------------------------------------------------------
-- Functions
-------------------------------------------------------------------------------
-- GetPayPeriodDays (Drop only)
IF object_id(N'GetPayPeriodDays', N'FN') IS NOT NULL
    DROP FUNCTION GetPayPeriodDays
GO

-- GetEmployeePayPeriodDays
IF object_id(N'GetEmployeePayPeriodDays', N'FN') IS NOT NULL
    DROP FUNCTION GetEmployeePayPeriodDays
GO

CREATE FUNCTION [dbo].[GetEmployeePayPeriodDays](@TchPRID int, @PayPeriod tinyint)
RETURNS decimal(4,2)
--RETURNS varchar(1024)
AS
BEGIN
	DECLARE @EmTypeID int
	DECLARE @RateCode varchar(1)
	DECLARE @Hours decimal(4,2)
	DECLARE @DaysPerWeek decimal(3,2)

	DECLARE @YearStart datetime
	DECLARE @YearEnd datetime
	DECLARE @PeriodStart datetime
	DECLARE @PeriodEnd datetime

	DECLARE @StartDate datetime
	DECLARE @EndDate datetime
	DECLARE @Workdays decimal(4,2)
	DECLARE @Holidays decimal(4,2)

	SELECT @EmTypeID = (SELECT EmTypeID FROM TchPR WHERE TchPRID = @TchPRID)

	SELECT @RateCode = (SELECT RateCode FROM TchPR WHERE TchPRID = @TchPRID)
	IF @RateCode IS NULL SELECT @RateCode = (
		SELECT ET.RateCode
		FROM TchPR TPR 
		LEFT OUTER JOIN EmType ET ON ET.EmTypeID = TPR.EmTypeID
		WHERE TPR.TchPRID = @TchPRID
	)

	SELECT @Hours = (SELECT [Hours] FROM TchPR WHERE TchPRID = @TchPRID)
	IF @Hours IS NULL SELECT @Hours = (
		SELECT ET.[Hours]
		FROM TchPR TPR 
		LEFT OUTER JOIN EmType ET ON ET.EmTypeID = TPR.EmTypeID
		WHERE TPR.TchPRID = @TchPRID
	)

	SELECT @DaysPerWeek = (SELECT TCDaysPerWeek FROM TchPR WHERE TchPRID = @TchPRID)
	IF @DaysPerWeek IS NULL SELECT @DaysPerWeek = (
		SELECT ET.DaysPerWeek
		FROM TchPR TPR 
		LEFT OUTER JOIN EmType ET ON ET.EmTypeID = TPR.EmTypeID
		WHERE TPR.TchPRID = @TchPRID
	)

	IF (@RateCode <> 'D') 
	AND (@Hours IS NULL OR @Hours = 0)
	AND (@DaysPerWeek IS NULL OR @DaysPerWeek = 0)
	RETURN 0.00

	SELECT @YearStart = (SELECT YearStart FROM TchPR WHERE TchPRID = @TchPRID)
	IF @YearStart IS NULL SELECT @YearStart = (
		SELECT ET.YearStart 
		FROM TchPR TPR 
		LEFT OUTER JOIN EmType ET ON ET.EmTypeID = TPR.EmTypeID
		WHERE TPR.TchPRID = @TchPRID
	)
	IF @YearStart IS NULL SELECT @YearStart = (
		(DATEADD(dd,CAST((SELECT Value FROM SysFi WHERE ID = 'YearStart') as int),'1800-12-28'))
	)

	SELECT @YearEnd = (SELECT YearEnd FROM TchPR WHERE TchPRID = @TchPRID)
	IF @YearEnd IS NULL SELECT @YearEnd = (
		SELECT ET.YearEnd 
		FROM TchPR TPR 
		LEFT OUTER JOIN EmType ET ON ET.EmTypeID = TPR.EmTypeID
		WHERE TPR.TchPRID = @TchPRID
	)
	IF @YearEnd IS NULL SELECT @YearEnd = (
		(DATEADD(dd,CAST((SELECT Value FROM SysFi WHERE ID = 'YearEnd') as int),'1800-12-28'))
	)

	SELECT @PeriodStart = (SELECT StartDate FROM PayPer WHERE [Period] = @PayPeriod)

	SELECT @PeriodEnd = (SELECT EndDate FROM PayPer WHERE [Period] = @PayPeriod)

	IF @YearStart > @PeriodStart AND @YearStart < @PeriodEnd
		SELECT @StartDate = @YearStart
	ELSE
		SELECT @StartDate = @PeriodStart

	IF @YearEnd < @PeriodEnd AND @YearEnd > @PeriodStart
		SELECT @EndDate = @YearEnd
	ELSE
		SELECT @EndDate = @PeriodEnd

	IF @StartDate < @EndDate AND @YearStart < @EndDate AND @YearEnd > @StartDate
	BEGIN
		SELECT @Workdays = 
			(DATEDIFF(dd, @StartDate, @EndDate) + 1)
			- (DATEDIFF(wk, @StartDate, @EndDate) * 2)
			- (CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END)
			- (CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END)

		SELECT @Holidays = SUM(HSC.Fraction) 
		FROM HolSch HSC
		LEFT OUTER JOIN EmTypeHolSch ETHS ON (ETHS.HolSchID = HSC.HolSchID) 
		WHERE (HSC.[Date] BETWEEN @StartDate AND @EndDate)
		AND (HSC.AllEmployees=1 OR ETHS.EmTypeID=@EmTypeID)
		AND (DATENAME(dw, HSC.[Date]) <> 'Saturday' AND DATENAME(dw, HSC.[Date]) <> 'Sunday')

		IF @Holidays IS NOT NULL SELECT @Workdays = @Workdays - @Holidays

		IF @Workdays < 0.00 SELECT @Workdays = 0.00
	END
	ELSE
		SELECT @Workdays = 0.00

	RETURN @WorkDays
END
GO

-- GetEmployeeTypePayPeriodDays
IF object_id(N'GetEmployeeTypePayPeriodDays', N'FN') IS NOT NULL
    DROP FUNCTION GetEmployeeTypePayPeriodDays
GO

CREATE FUNCTION [dbo].[GetEmployeeTypePayPeriodDays](@EmTypeID int, @PayPeriod tinyint)
RETURNS decimal(4,2)
--RETURNS varchar(1024)
AS
BEGIN
	DECLARE @RateCode varchar(1)
	DECLARE @Hours decimal(4,2)
	DECLARE @DaysPerWeek decimal(3,2)

	DECLARE @YearStart datetime
	DECLARE @YearEnd datetime
	DECLARE @PeriodStart datetime
	DECLARE @PeriodEnd datetime

	DECLARE @StartDate datetime
	DECLARE @EndDate datetime
	DECLARE @Workdays decimal(4,2)
	DECLARE @Holidays decimal(4,2)

	SELECT @RateCode = (
		SELECT ET.RateCode
		FROM EmType ET
		WHERE ET.EmTypeID = @EmTypeID
	)
	SELECT @Hours = (
		SELECT ET.[Hours]
		FROM EmType ET
		WHERE ET.EmTypeID = @EmTypeID
	)

	SELECT @DaysPerWeek = (
		SELECT ET.DaysPerWeek
		FROM EmType ET
		WHERE ET.EmTypeID = @EmTypeID
	)

	IF (@RateCode <> 'D') 
	AND (@Hours IS NULL OR @Hours = 0)
	AND (@DaysPerWeek IS NULL OR @DaysPerWeek = 0)
	RETURN 0.00

	SELECT @YearStart = (
		SELECT ET.YearStart 
		FROM EmType ET
		WHERE ET.EmTypeID = @EmTypeID
	)
	IF @YearStart IS NULL SELECT @YearStart = (
		(DATEADD(dd,CAST((SELECT Value FROM SysFi WHERE ID = 'YearStart') as int),'1800-12-28'))
	)

	SELECT @YearEnd = (
		SELECT ET.YearEnd 
		FROM EmType ET
		WHERE ET.EmTypeID = @EmTypeID
	)
	IF @YearEnd IS NULL SELECT @YearEnd = (
		(DATEADD(dd,CAST((SELECT Value FROM SysFi WHERE ID = 'YearEnd') as int),'1800-12-28'))
	)

	SELECT @PeriodStart = (SELECT StartDate FROM PayPer WHERE [Period] = @PayPeriod)

	SELECT @PeriodEnd = (SELECT EndDate FROM PayPer WHERE [Period] = @PayPeriod)

	IF @YearStart > @PeriodStart AND @YearStart < @PeriodEnd
		SELECT @StartDate = @YearStart
	ELSE
		SELECT @StartDate = @PeriodStart

	IF @YearEnd < @PeriodEnd AND @YearEnd > @PeriodStart
		SELECT @EndDate = @YearEnd
	ELSE
		SELECT @EndDate = @PeriodEnd

	IF @StartDate < @EndDate AND @YearStart < @EndDate AND @YearEnd > @StartDate
	BEGIN
		SELECT @Workdays = 
			(DATEDIFF(dd, @StartDate, @EndDate) + 1)
			- (DATEDIFF(wk, @StartDate, @EndDate) * 2)
			- (CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END)
			- (CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END)

		SELECT @Holidays = SUM(HSC.Fraction) 
		FROM HolSch HSC
		LEFT OUTER JOIN EmTypeHolSch ETHS ON (ETHS.HolSchID = HSC.HolSchID) 
		WHERE (HSC.[Date] BETWEEN @StartDate AND @EndDate)
		AND (HSC.AllEmployees=1 OR ETHS.EmTypeID=@EmTypeID)
		AND (DATENAME(dw, HSC.[Date]) <> 'Saturday' AND DATENAME(dw, HSC.[Date]) <> 'Sunday')

		IF @Holidays IS NOT NULL SELECT @Workdays = @Workdays - @Holidays

		IF @Workdays < 0.00 SELECT @Workdays = 0.00
	END
	ELSE
		SELECT @Workdays = 0.00

	--RETURN	'YearStart:' + CONVERT(varchar,@YearStart,1)
	--		+ '|YearEnd:' + CONVERT(varchar,@YearEnd,1)
	--		+ '|PeriodStart:' + CONVERT(varchar,@PeriodStart,1)
	--		+ '|PeriodEnd:' + CONVERT(varchar,@PeriodEnd,1)
	--		+ '|StartDate:' + CONVERT(varchar,@StartDate,1)
	--		+ '|EndDate:' + CONVERT(varchar,@EndDate,1)

	RETURN @WorkDays
END
GO

-------------------------------------------------------------------------------
-- View Modifications
-------------------------------------------------------------------------------

-- PayPeriodDaysView (Drop)
IF EXISTS(select * FROM sys.views where name = 'PayPeriodDaysView')
DROP VIEW PayPeriodDaysView
GO

-- EmployeePayPeriodDaysView (Add)
IF EXISTS(select * FROM sys.views where name = 'EmployeePayPeriodDaysView')
DROP VIEW EmployeePayPeriodDaysView
GO

CREATE VIEW [dbo].[EmployeePayPeriodDaysView] AS 
SELECT	TPR.[TchPRID],
		TPR.[Name],
		ET.[EmTypeID], 
		ET.[Description] AS [Type],
		CAST(CASE 
			WHEN (TPR.[YearStart] IS NOT NULL AND TPR.[YearStart] > 0) THEN TPR.[YearStart] 
			WHEN (ET.[YearStart] IS NOT NULL AND ET.[YearStart] > 0) THEN ET.[YearStart] 
			ELSE (DATEADD(dd,CAST((SELECT Value FROM SysFi WHERE ID = 'YearStart') as int),'1800-12-28'))
		END AS date) AS [YearStart],
		CAST(CASE 
			WHEN (TPR.[YearEnd] IS NOT NULL AND TPR.[YearEnd] > 0) THEN TPR.[YearEnd] 
			WHEN (ET.[YearEnd] IS NOT NULL AND ET.[YearEnd] > 0) THEN ET.[YearEnd] 
			ELSE (DATEADD(dd,CAST((SELECT Value FROM SysFi WHERE ID = 'YearEnd') as int),'1800-12-28'))
		END AS date) AS [YearEnd],
		CAST(PP1.StartDate AS date) AS PP1StartDate,
		CAST(PP1.EndDate AS date) AS PP1EndDate,
		[dbo].[GetEmployeePayPeriodDays](TPR.TchPRID,1) AS PP1Days,
		CAST(PP2.StartDate AS date) AS PP2StartDate,
		CAST(PP2.EndDate AS date) AS PP2EndDate,
		[dbo].[GetEmployeePayPeriodDays](TPR.TchPRID,2) AS PP2Days,
		CAST(PP3.StartDate AS date) AS PP3StartDate,
		CAST(PP3.EndDate AS date) AS PP3EndDate,
		[dbo].[GetEmployeePayPeriodDays](TPR.TchPRID,3) AS PP3Days,
		CAST(PP4.StartDate AS date) AS PP4StartDate,
		CAST(PP4.EndDate AS date) AS PP4EndDate,
		[dbo].[GetEmployeePayPeriodDays](TPR.TchPRID,4) AS PP4Days,
		CAST(PP5.StartDate AS date) AS PP5StartDate,
		CAST(PP5.EndDate AS date) AS PP5EndDate,
		[dbo].[GetEmployeePayPeriodDays](TPR.TchPRID,5) AS PP5Days,
		CAST(PP6.StartDate AS date) AS PP6StartDate,
		CAST(PP6.EndDate AS date) AS PP6EndDate,
		[dbo].[GetEmployeePayPeriodDays](TPR.TchPRID,6) AS PP6Days,
		CAST(PP7.StartDate AS date) AS PP7StartDate,
		CAST(PP7.EndDate AS date) AS PP7EndDate,
		[dbo].[GetEmployeePayPeriodDays](TPR.TchPRID,7) AS PP7Days,
		CAST(PP8.StartDate AS date) AS PP8StartDate,
		CAST(PP8.EndDate AS date) AS PP8EndDate,
		[dbo].[GetEmployeePayPeriodDays](TPR.TchPRID,8) AS PP8Days,
		CAST(PP9.StartDate AS date) AS PP9StartDate,
		CAST(PP9.EndDate AS date) AS PP9EndDate,
		[dbo].[GetEmployeePayPeriodDays](TPR.TchPRID,9) AS PP9Days,
		CAST(PP10.StartDate AS date) AS PP10StartDate,
		CAST(PP10.EndDate AS date) AS PP10EndDate,
		[dbo].[GetEmployeePayPeriodDays](TPR.TchPRID,10) AS PP10Days,
		CAST(PP11.StartDate AS date) AS PP11StartDate,
		CAST(PP11.EndDate AS date) AS PP11EndDate,
		[dbo].[GetEmployeePayPeriodDays](TPR.TchPRID,11) AS PP11Days,
		CAST(PP12.StartDate AS date) AS PP12StartDate,
		CAST(PP12.EndDate AS date) AS PP12EndDate,
		[dbo].[GetEmployeePayPeriodDays](TPR.TchPRID,12) AS PP12Days,
		CAST(PP13.StartDate AS date) AS PP13StartDate,
		CAST(PP13.EndDate AS date) AS PP13EndDate,
		[dbo].[GetEmployeePayPeriodDays](TPR.TchPRID,13) AS PP13Days,
		CAST(PP14.StartDate AS date) AS PP14StartDate,
		CAST(PP14.EndDate AS date) AS PP14EndDate,
		[dbo].[GetEmployeePayPeriodDays](TPR.TchPRID,14) AS PP14Days,
		CAST(PP15.StartDate AS date) AS PP15StartDate,
		CAST(PP15.EndDate AS date) AS PP15EndDate,
		[dbo].[GetEmployeePayPeriodDays](TPR.TchPRID,15) AS PP15Days,
		CAST(PP16.StartDate AS date) AS PP16StartDate,
		CAST(PP16.EndDate AS date) AS PP16EndDate,
		[dbo].[GetEmployeePayPeriodDays](TPR.TchPRID,16) AS PP16Days,
		CAST(PP17.StartDate AS date) AS PP17StartDate,
		CAST(PP17.EndDate AS date) AS PP17EndDate,
		[dbo].[GetEmployeePayPeriodDays](TPR.TchPRID,17) AS PP17Days,
		CAST(PP18.StartDate AS date) AS PP18StartDate,
		CAST(PP18.EndDate AS date) AS PP18EndDate,
		[dbo].[GetEmployeePayPeriodDays](TPR.TchPRID,18) AS PP18Days,
		CAST(PP19.StartDate AS date) AS PP19StartDate,
		CAST(PP19.EndDate AS date) AS PP19EndDate,
		[dbo].[GetEmployeePayPeriodDays](TPR.TchPRID,19) AS PP19Days,
		CAST(PP20.StartDate AS date) AS PP20StartDate,
		CAST(PP20.EndDate AS date) AS PP20EndDate,
		[dbo].[GetEmployeePayPeriodDays](TPR.TchPRID,20) AS PP20Days,
		CAST(PP21.StartDate AS date) AS PP21StartDate,
		CAST(PP21.EndDate AS date) AS PP21EndDate,
		[dbo].[GetEmployeePayPeriodDays](TPR.TchPRID,21) AS PP21Days,
		CAST(PP22.StartDate AS date) AS PP22StartDate,
		CAST(PP22.EndDate AS date) AS PP22EndDate,
		[dbo].[GetEmployeePayPeriodDays](TPR.TchPRID,22) AS PP22Days,
		CAST(PP23.StartDate AS date) AS PP23StartDate,
		CAST(PP23.EndDate AS date) AS PP23EndDate,
		[dbo].[GetEmployeePayPeriodDays](TPR.TchPRID,23) AS PP23Days,
		CAST(PP24.StartDate AS date) AS PP24StartDate,
		CAST(PP24.EndDate AS date) AS PP24EndDate,
		[dbo].[GetEmployeePayPeriodDays](TPR.TchPRID,24) AS PP24Days,
		CAST(PP25.StartDate AS date) AS PP25StartDate,
		CAST(PP25.EndDate AS date) AS PP25EndDate,
		[dbo].[GetEmployeePayPeriodDays](TPR.TchPRID,25) AS PP25Days,
		CAST(PP26.StartDate AS date) AS PP26StartDate,
		CAST(PP26.EndDate AS date) AS PP26EndDate,
		[dbo].[GetEmployeePayPeriodDays](TPR.TchPRID,26) AS PP26Days
FROM	TchPR TPR
LEFT OUTER JOIN GrpFi GRP ON GRP.GrpFiID = TPR.GrpFiID
LEFT OUTER JOIN EmType ET ON ET.EmTypeID = TPR.EmTypeID
LEFT OUTER JOIN PayPer PP1 ON PP1.[Period] = 1
LEFT OUTER JOIN PayPer PP2 ON PP2.[Period] = 2
LEFT OUTER JOIN PayPer PP3 ON PP3.[Period] = 3
LEFT OUTER JOIN PayPer PP4 ON PP4.[Period] = 4
LEFT OUTER JOIN PayPer PP5 ON PP5.[Period] = 5
LEFT OUTER JOIN PayPer PP6 ON PP6.[Period] = 6
LEFT OUTER JOIN PayPer PP7 ON PP7.[Period] = 7
LEFT OUTER JOIN PayPer PP8 ON PP8.[Period] = 8
LEFT OUTER JOIN PayPer PP9 ON PP9.[Period] = 9
LEFT OUTER JOIN PayPer PP10 ON PP10.[Period] = 10
LEFT OUTER JOIN PayPer PP11 ON PP11.[Period] = 11
LEFT OUTER JOIN PayPer PP12 ON PP12.[Period] = 12
LEFT OUTER JOIN PayPer PP13 ON PP13.[Period] = 13
LEFT OUTER JOIN PayPer PP14 ON PP14.[Period] = 14
LEFT OUTER JOIN PayPer PP15 ON PP15.[Period] = 15
LEFT OUTER JOIN PayPer PP16 ON PP16.[Period] = 16
LEFT OUTER JOIN PayPer PP17 ON PP17.[Period] = 17
LEFT OUTER JOIN PayPer PP18 ON PP18.[Period] = 18
LEFT OUTER JOIN PayPer PP19 ON PP19.[Period] = 19
LEFT OUTER JOIN PayPer PP20 ON PP20.[Period] = 20
LEFT OUTER JOIN PayPer PP21 ON PP21.[Period] = 21
LEFT OUTER JOIN PayPer PP22 ON PP22.[Period] = 22
LEFT OUTER JOIN PayPer PP23 ON PP23.[Period] = 23
LEFT OUTER JOIN PayPer PP24 ON PP24.[Period] = 24
LEFT OUTER JOIN PayPer PP25 ON PP25.[Period] = 25
LEFT OUTER JOIN PayPer PP26 ON PP26.[Period] = 26

WHERE	(TPR.Name NOT LIKE '!%') 
AND		(TPR.Name NOT LIKE '~%') 
AND		(TPR.Inactive IS NULL OR TPR.Inactive <> 1) 
AND		(GRP.Grp IS NULL OR GRP.Grp <> 'DELETED')

GO

-- EmployeeTypePayPeriodDaysView
IF EXISTS(select * FROM sys.views where name = 'EmployeeTypePayPeriodDaysView')
DROP VIEW EmployeeTypePayPeriodDaysView
GO

CREATE VIEW [dbo].[EmployeeTypePayPeriodDaysView] AS 
SELECT	
		ET.[EmTypeID], 
		ET.[Description], 
		CAST(CASE 
			WHEN (ET.[YearStart] IS NOT NULL AND ET.[YearStart] > 0) THEN ET.[YearStart] 
			ELSE (DATEADD(dd,CAST((SELECT Value FROM SysFi WHERE ID = 'YearStart') as int),'1800-12-28'))
		END AS date) AS [YearStart],
		CAST(CASE 
			WHEN (ET.[YearEnd] IS NOT NULL AND ET.[YearEnd] > 0) THEN ET.[YearEnd] 
			ELSE (DATEADD(dd,CAST((SELECT Value FROM SysFi WHERE ID = 'YearEnd') as int),'1800-12-28'))
		END AS date) AS [YearEnd],
		CAST(PP1.StartDate AS date) AS PP1StartDate,
		CAST(PP1.EndDate AS date) AS PP1EndDate,
		[dbo].[GetEmployeeTypePayPeriodDays](ET.EmTypeID,1) AS PP1Days,
		CAST(PP2.StartDate AS date) AS PP2StartDate,
		CAST(PP2.EndDate AS date) AS PP2EndDate,
		[dbo].[GetEmployeeTypePayPeriodDays](ET.EmTypeID,2) AS PP2Days,
		CAST(PP3.StartDate AS date) AS PP3StartDate,
		CAST(PP3.EndDate AS date) AS PP3EndDate,
		[dbo].[GetEmployeeTypePayPeriodDays](ET.EmTypeID,3) AS PP3Days,
		CAST(PP4.StartDate AS date) AS PP4StartDate,
		CAST(PP4.EndDate AS date) AS PP4EndDate,
		[dbo].[GetEmployeeTypePayPeriodDays](ET.EmTypeID,4) AS PP4Days,
		CAST(PP5.StartDate AS date) AS PP5StartDate,
		CAST(PP5.EndDate AS date) AS PP5EndDate,
		[dbo].[GetEmployeeTypePayPeriodDays](ET.EmTypeID,5) AS PP5Days,
		CAST(PP6.StartDate AS date) AS PP6StartDate,
		CAST(PP6.EndDate AS date) AS PP6EndDate,
		[dbo].[GetEmployeeTypePayPeriodDays](ET.EmTypeID,6) AS PP6Days,
		CAST(PP7.StartDate AS date) AS PP7StartDate,
		CAST(PP7.EndDate AS date) AS PP7EndDate,
		[dbo].[GetEmployeeTypePayPeriodDays](ET.EmTypeID,7) AS PP7Days,
		CAST(PP8.StartDate AS date) AS PP8StartDate,
		CAST(PP8.EndDate AS date) AS PP8EndDate,
		[dbo].[GetEmployeeTypePayPeriodDays](ET.EmTypeID,8) AS PP8Days,
		CAST(PP9.StartDate AS date) AS PP9StartDate,
		CAST(PP9.EndDate AS date) AS PP9EndDate,
		[dbo].[GetEmployeeTypePayPeriodDays](ET.EmTypeID,9) AS PP9Days,
		CAST(PP10.StartDate AS date) AS PP10StartDate,
		CAST(PP10.EndDate AS date) AS PP10EndDate,
		[dbo].[GetEmployeeTypePayPeriodDays](ET.EmTypeID,10) AS PP10Days,
		CAST(PP11.StartDate AS date) AS PP11StartDate,
		CAST(PP11.EndDate AS date) AS PP11EndDate,
		[dbo].[GetEmployeeTypePayPeriodDays](ET.EmTypeID,11) AS PP11Days,
		CAST(PP12.StartDate AS date) AS PP12StartDate,
		CAST(PP12.EndDate AS date) AS PP12EndDate,
		[dbo].[GetEmployeeTypePayPeriodDays](ET.EmTypeID,12) AS PP12Days,
		CAST(PP13.StartDate AS date) AS PP13StartDate,
		CAST(PP13.EndDate AS date) AS PP13EndDate,
		[dbo].[GetEmployeeTypePayPeriodDays](ET.EmTypeID,13) AS PP13Days,
		CAST(PP14.StartDate AS date) AS PP14StartDate,
		CAST(PP14.EndDate AS date) AS PP14EndDate,
		[dbo].[GetEmployeeTypePayPeriodDays](ET.EmTypeID,14) AS PP14Days,
		CAST(PP15.StartDate AS date) AS PP15StartDate,
		CAST(PP15.EndDate AS date) AS PP15EndDate,
		[dbo].[GetEmployeeTypePayPeriodDays](ET.EmTypeID,15) AS PP15Days,
		CAST(PP16.StartDate AS date) AS PP16StartDate,
		CAST(PP16.EndDate AS date) AS PP16EndDate,
		[dbo].[GetEmployeeTypePayPeriodDays](ET.EmTypeID,16) AS PP16Days,
		CAST(PP17.StartDate AS date) AS PP17StartDate,
		CAST(PP17.EndDate AS date) AS PP17EndDate,
		[dbo].[GetEmployeeTypePayPeriodDays](ET.EmTypeID,17) AS PP17Days,
		CAST(PP18.StartDate AS date) AS PP18StartDate,
		CAST(PP18.EndDate AS date) AS PP18EndDate,
		[dbo].[GetEmployeeTypePayPeriodDays](ET.EmTypeID,18) AS PP18Days,
		CAST(PP19.StartDate AS date) AS PP19StartDate,
		CAST(PP19.EndDate AS date) AS PP19EndDate,
		[dbo].[GetEmployeeTypePayPeriodDays](ET.EmTypeID,19) AS PP19Days,
		CAST(PP20.StartDate AS date) AS PP20StartDate,
		CAST(PP20.EndDate AS date) AS PP20EndDate,
		[dbo].[GetEmployeeTypePayPeriodDays](ET.EmTypeID,20) AS PP20Days,
		CAST(PP21.StartDate AS date) AS PP21StartDate,
		CAST(PP21.EndDate AS date) AS PP21EndDate,
		[dbo].[GetEmployeeTypePayPeriodDays](ET.EmTypeID,21) AS PP21Days,
		CAST(PP22.StartDate AS date) AS PP22StartDate,
		CAST(PP22.EndDate AS date) AS PP22EndDate,
		[dbo].[GetEmployeeTypePayPeriodDays](ET.EmTypeID,22) AS PP22Days,
		CAST(PP23.StartDate AS date) AS PP23StartDate,
		CAST(PP23.EndDate AS date) AS PP23EndDate,
		[dbo].[GetEmployeeTypePayPeriodDays](ET.EmTypeID,23) AS PP23Days,
		CAST(PP24.StartDate AS date) AS PP24StartDate,
		CAST(PP24.EndDate AS date) AS PP24EndDate,
		[dbo].[GetEmployeeTypePayPeriodDays](ET.EmTypeID,24) AS PP24Days,
		CAST(PP25.StartDate AS date) AS PP25StartDate,
		CAST(PP25.EndDate AS date) AS PP25EndDate,
		[dbo].[GetEmployeeTypePayPeriodDays](ET.EmTypeID,25) AS PP25Days,
		CAST(PP26.StartDate AS date) AS PP26StartDate,
		CAST(PP26.EndDate AS date) AS PP26EndDate,
		[dbo].[GetEmployeeTypePayPeriodDays](ET.EmTypeID,26) AS PP26Days
FROM EmType ET
LEFT OUTER JOIN PayPer PP1 ON PP1.[Period] = 1
LEFT OUTER JOIN PayPer PP2 ON PP2.[Period] = 2
LEFT OUTER JOIN PayPer PP3 ON PP3.[Period] = 3
LEFT OUTER JOIN PayPer PP4 ON PP4.[Period] = 4
LEFT OUTER JOIN PayPer PP5 ON PP5.[Period] = 5
LEFT OUTER JOIN PayPer PP6 ON PP6.[Period] = 6
LEFT OUTER JOIN PayPer PP7 ON PP7.[Period] = 7
LEFT OUTER JOIN PayPer PP8 ON PP8.[Period] = 8
LEFT OUTER JOIN PayPer PP9 ON PP9.[Period] = 9
LEFT OUTER JOIN PayPer PP10 ON PP10.[Period] = 10
LEFT OUTER JOIN PayPer PP11 ON PP11.[Period] = 11
LEFT OUTER JOIN PayPer PP12 ON PP12.[Period] = 12
LEFT OUTER JOIN PayPer PP13 ON PP13.[Period] = 13
LEFT OUTER JOIN PayPer PP14 ON PP14.[Period] = 14
LEFT OUTER JOIN PayPer PP15 ON PP15.[Period] = 15
LEFT OUTER JOIN PayPer PP16 ON PP16.[Period] = 16
LEFT OUTER JOIN PayPer PP17 ON PP17.[Period] = 17
LEFT OUTER JOIN PayPer PP18 ON PP18.[Period] = 18
LEFT OUTER JOIN PayPer PP19 ON PP19.[Period] = 19
LEFT OUTER JOIN PayPer PP20 ON PP20.[Period] = 20
LEFT OUTER JOIN PayPer PP21 ON PP21.[Period] = 21
LEFT OUTER JOIN PayPer PP22 ON PP22.[Period] = 22
LEFT OUTER JOIN PayPer PP23 ON PP23.[Period] = 23
LEFT OUTER JOIN PayPer PP24 ON PP24.[Period] = 24
LEFT OUTER JOIN PayPer PP25 ON PP25.[Period] = 25
LEFT OUTER JOIN PayPer PP26 ON PP26.[Period] = 26

GO

-- PayPerEmTypeDaysView
IF EXISTS(select * FROM sys.views where name = 'PayPerEmTypeDaysView')
DROP VIEW PayPerEmTypeDaysView
GO

CREATE VIEW [dbo].[PayPerEmTypeDaysView] AS 
SELECT	ET.EmTypeId
		,ET.[Description] AS EmployeeType
		,PP.[PayPerID]
		,CAST(PP.StartDate AS Date) AS StartDate
		,CAST(PP.EndDate AS Date) AS EndDate
		,[dbo].[GetEmployeeTypePayPeriodDays](ET.EmTypeID,PP.PayPerID) AS [Days]
FROM EmType ET
JOIN PayPer PP ON ((PP.StartDate IS NOT NULL) AND (PP.EndDate IS NOT NULL) AND (PP.StartDate < PP.EndDate))
GO

-------------------------------------------------------------------------------
-- Fixes
-------------------------------------------------------------------------------

-- populate missing SubLog.Tch_ID from existing TchPRID
UPDATE SL
SET SL.Tch_ID = TPR.[ID]
FROM SubLog SL
LEFT OUTER JOIN TchPR TPR ON TPR.TchPRID = SL.TchPRID
WHERE (SL.TchPRID IS NOT NULL)
AND (SL.Tch_ID IS NULL)

-- set global no teacher id
IF NOT ((SELECT [Value] FROM SysFi WHERE [ID] = 'NoTch_Id') > 0)
AND EXISTS(SELECT * FROM TchPR WHERE (UPPER([NAME]) LIKE '!NOTEACHER%' OR UPPER([NAME]) LIKE '!NO TEACHER%') AND (GrpFiID IS NULL))
BEGIN
	UPDATE SysFi
	SET [Value] = (SELECT TOP 1 [ID] FROM TchPR WHERE (UPPER([NAME]) LIKE '!NOTEACHER%' OR UPPER([NAME]) LIKE '!NO TEACHER%') AND (GrpFiID IS NULL) ORDER BY [ID] DESC)
	WHERE UPPER([ID]) = 'NOTCH_ID'
END

-- Update SubLog records to use global !NoTeacher
IF ((SELECT [Value] FROM SysFi WHERE [ID] = 'NoTch_Id') > 0)
AND EXISTS (SELECT TchPRID FROM TchPR WHERE (UPPER([NAME]) LIKE '!NOTEACHER%' OR UPPER([NAME]) LIKE '!NO TEACHER%') AND (GrpFiID IS NOT NULL))
BEGIN
	UPDATE SubLog 
	SET TchPRID = (SELECT TchPRID FROM TchPR WHERE ID = (SELECT [VALUE] FROM SysFi WHERE [ID] = 'NoTch_Id'))
	, Tch_ID = (SELECT [VALUE] FROM SysFi WHERE [ID] = 'NoTch_Id')
	WHERE TchPRID IN 
	(SELECT TchPRID FROM TchPR WHERE (UPPER([NAME]) LIKE '!NOTEACHER%' OR UPPER([NAME]) LIKE '!NO TEACHER%') AND (GrpFiID IS NOT NULL))
END

-- Update AbsTrn records to use global !NoTeacher
IF ((SELECT [Value] FROM SysFi WHERE [ID] = 'NoTch_Id') > 0)
AND EXISTS (SELECT TchPRID FROM TchPR WHERE (UPPER([NAME]) LIKE '!NOTEACHER%' OR UPPER([NAME]) LIKE '!NO TEACHER%') AND (GrpFiID IS NOT NULL))
BEGIN
	UPDATE AbsTrn 
	SET TchPRID = (SELECT TchPRID FROM TchPR WHERE ID = (SELECT [VALUE] FROM SysFi WHERE [ID] = 'NoTch_Id'))
	, Tch_ID = (SELECT [VALUE] FROM SysFi WHERE [ID] = 'NoTch_Id')
	WHERE TchPRID IN 
	(SELECT TchPRID FROM TchPR WHERE (UPPER([NAME]) LIKE '!NOTEACHER%' OR UPPER([NAME]) LIKE '!NO TEACHER%') AND (GrpFiID IS NOT NULL))
END

-- delete group-specific !NoTeacher(s)
IF EXISTS (SELECT TchPRID FROM TchPR WHERE (UPPER([NAME]) LIKE '!NOTEACHER%' OR UPPER([NAME]) LIKE '!NO TEACHER%') AND (GrpFiID IS NOT NULL))
BEGIN
	DELETE
	FROM TchPR
	WHERE TchPRID IN 
	(SELECT TchPRID FROM TchPR WHERE (UPPER([NAME]) LIKE '!NOTEACHER%' OR UPPER([NAME]) LIKE '!NO TEACHER%') AND (GrpFiID IS NOT NULL))
END


-------------------------------------------------------------------------------
-- Update version number
-------------------------------------------------------------------------------

UPDATE SysFi
SET Value = '10.00.030'
WHERE ID = 'Ver'
