USE [databasename - uri: garyandleo]
GO

/****** Object:  Trigger [dbo].[SCHS]    Script Date: 2/3/2023 11:40:59 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dbo].[SCHS]
    ON [dbo].[SCHEDULES]
    FOR INSERT
AS

DECLARE @fkey VARCHAR(30);

SELECT @fkey = filekey
FROM inserted
WHERE schtype = 2
	AND benefit IN (1, 3);

IF @fkey IS NOT NULL 
BEGIN
	CREATE TABLE #pp
	(
		filekey INT,
		start DATETIME,
		enddate DATETIME,
		pp INT
	);

	DECLARE @today DATETIME;
	SELECT @today = GETDATE();

	INSERT INTO #pp
	SELECT DISTINCT
		filekey,
		periodbegin,
		periodend,
		1
	FROM archives
	WHERE @today BETWEEN periodbegin AND periodend;

	INSERT INTO #pp
	SELECT DISTINCT
		archives.filekey,
		periodbegin,
		periodend,
		2
	FROM archives
		INNER JOIN #pp ON archives.filekey = #pp.filekey
			AND archives.periodend = #pp.start - 1;

	INSERT INTO #pp
	SELECT DISTINCT
		archives.filekey,
		periodbegin,
		periodend,
		3
	FROM archives
		INNER JOIN #pp ON archives.filekey = #pp.filekey
			AND archives.periodend = #pp.start - 1
	WHERE pp = 2;

	INSERT INTO #pp
	SELECT DISTINCT
		archives.filekey,
		periodbegin,
		periodend,
		4
	FROM archives
		INNER JOIN #pp ON archives.filekey = #pp.filekey
			AND archives.periodend = #pp.start - 1
	WHERE pp = 3;

	DECLARE employees_cursor CURSOR FOR
        SELECT
		filekey,
		schdate,
		benefit
	FROM schedules
	WHERE schtype = 2
		AND benefit IN (1, 3)
		AND schstyle <> 10
	ORDER BY filekey;

	SET NOCOUNT ON;

	OPEN employees_cursor;

	DECLARE 
        @filekey INT,
        @schdate DATETIME,
        @benefit INT,
        @hrs REAL,
        @shrs REAL,
        @chrs REAL,
        @pend DATETIME,
        @ppstart DATETIME,
        @spstart DATETIME,
        @pendpp INT;

	FETCH NEXT FROM employees_cursor INTO @filekey, @schdate, @benefit;

	WHILE (@@FETCH_STATUS = 0)
    BEGIN
		-- This is executed as long as the previous fetch succeeds

		-- Get Dates
		SELECT @spstart = (
            SELECT start
			FROM #pp
			WHERE filekey = @filekey
				AND @schdate BETWEEN start AND enddate
        );

		SELECT @pend = (
            SELECT enddate
			FROM #pp
			WHERE filekey = @filekey
				AND enddate = @spstart - 1
        );

		SELECT @pendpp = (
            SELECT pp
			FROM #pp
			WHERE filekey = @filekey
				AND enddate = @pend
        );

		SELECT @ppstart = (
            SELECT start
			FROM #pp
			WHERE filekey = @filekey
				AND pp = @pendpp + 1
        );

		-- Get Hours
		SELECT @hrs = ISNULL((
            SELECT SUM(hours)
			FROM paydeshist
			WHERE filekey = @filekey
				AND paydesnum IN (1, 2, 3, 9, 10, 11, 20, 21)
				AND eventdate >= @ppstart
				AND eventdate <= @pend
        ), 0);

		IF ROUND(((@hrs / 20) * 60), 0) > 480
            SELECT @shrs = 480;
        ELSE
            SELECT @shrs = ROUND(((@hrs / 20) * 60), 0);

		SELECT @chrs = @shrs - 60;

		IF @hrs > 0
        BEGIN
			IF @shrs > 0
            BEGIN
				UPDATE schedules 
                SET hours = @shrs, 
                    schstyle = 10 
                WHERE filekey = @filekey
					AND schtype = 2
					AND benefit = @benefit
					AND schdate = @schdate;

				IF @benefit = 1
                BEGIN
					IF NOT EXISTS (
                        SELECT *
					FROM supedits
					WHERE filekey = @filekey
						AND username = 'BENEFITS'
						AND effdate = @schdate
						AND prevpaydes = 1
                    )
                        INSERT INTO supedits
					VALUES
						(
							@filekey,
							301,
							@schdate,
							'1900-01-01 00:01:00.000',
							'BENEFITS',
							0, 0, 0, 0, 0, 0, 0,
							17,
							1,
							@chrs,
							0.0, 0.0,
							GETDATE(),
							0, 0, 0,
							'',
							GETDATE(),
							GETDATE(),
							GETDATE()
                        );
                    ELSE
                        UPDATE supedits 
                        SET hours = @chrs 
                        WHERE filekey = @filekey
						AND username = 'BENEFITS'
						AND effdate = @schdate
						AND prevpaydes = 1;
				END;

				IF @benefit = 3
                BEGIN
					IF NOT EXISTS (
                        SELECT *
					FROM supedits
					WHERE filekey = @filekey
						AND username = 'BENEFITS'
						AND effdate = @schdate
						AND prevpaydes = 3
                    )
                        INSERT INTO supedits
					VALUES
						(
							@filekey,
							301,
							@schdate,
							'1900-01-01 00:01:00.000',
							'BENEFITS',
							0, 0, 0, 0, 0, 0, 0,
							11,
							3,
							@chrs,
							0.0, 0.0,
							GETDATE(),
							0, 0, 0,
							'',
							GETDATE(),
							GETDATE(),
							GETDATE()
                        );
                    ELSE
                        UPDATE supedits 
                        SET hours = @chrs 
                        WHERE filekey = @filekey
						AND username = 'BENEFITS'
						AND effdate = @schdate
						AND prevpaydes = 3;
				END;
			END;
		END;

		FETCH NEXT FROM employees_cursor INTO @filekey, @schdate, @benefit;
	END;

	CLOSE employees_cursor;
	DEALLOCATE employees_cursor;
END;