USE [Magna]
GO

/****** Object:  Trigger [dbo].[TRGCALC]    Script Date: 1/9/2020 11:48:21 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

  
--DROP TRIGGER TRGCALC
CREATE TRIGGER [dbo].[TRGCALC] ON [dbo].[INCIDENTS]    
FOR INSERT, UPDATE, DELETE  
AS    
SET NOCOUNT ON  
  
DECLARE @FKEYS TABLE(FILEKEY INT)  
DECLARE @ACTIONS TABLE(FILEKEY INT, EVENTDATE datetime, TRIGGERID int, OCCURRED datetime, TRIGGERTYPE int, DETAILS varchar(80), DATA int, DATAACTION varchar(1), DATAID int)  
DECLARE @TRIGGERACTIONS TABLE(UNIQUEID int, FILEKEY int, EVENTDATE datetime, TRIGGERID int)  
DECLARE @EVENTS TABLE (UNIQUEID int IDENTITY(1, 1) PRIMARY KEY NOT NULL, FILEKEY int, EVENTDATE datetime, POINTS real, RUNNINGPOINTS real)  
  
--Build list of employees being calculated now AND COLLECT CURRENT EMPLOYEE TRIGGERACTIONS   
INSERT INTO @FKEYS SELECT FILEKEY FROM INSERTED UNION SELECT FILEKEY FROM DELETED    
INSERT INTO @TRIGGERACTIONS SELECT UNIQUEID, FILEKEY, EVENTDATE, TRIGGERID FROM TRIGGERACTIONS WHERE FILEKEY IN (SELECT FILEKEY FROM @FKEYS)  
INSERT INTO @EVENTS SELECT FILEKEY, EVENTDATE, POINTS, RUNNINGPOINTS FROM INCIDENTS WHERE POINTS > 0 AND RUNNINGPOINTS >= 4 AND FILEKEY IN (SELECT FILEKEY FROM @FKEYS)  
  
INSERT INTO @ACTIONS     
SELECT FILEKEY, EVENTDATE, 1 AS TRIGGERID, EVENTDATE AS OCCURRED, 3 AS TRIGGERTYPE, 'Verbal Warning' as DETAILS, 0 AS DATA, '?', 0    
FROM @EVENTS WHERE RUNNINGPOINTS - POINTS < 4  
 
--Added with Revision D all points past 6 are termination 
INSERT INTO @ACTIONS     
SELECT FILEKEY, EVENTDATE, 7 AS TRIGGERID, EVENTDATE AS OCCURRED, 3 AS TRIGGERTYPE, 'Termination' as DETAILS, 0 AS DATA, '?', 0    
FROM @EVENTS WHERE RUNNINGPOINTS >= 6  
  
INSERT INTO @ACTIONS     
SELECT FILEKEY, EVENTDATE, 0 AS TRIGGERID, EVENTDATE AS OCCURRED, 3 AS TRIGGERTYPE, 'Written Warning (1st)' as DETAILS, 0 AS DATA, '?', 0    
FROM @EVENTS E WHERE   
(  
(RUNNINGPOINTS - POINTS < 5 AND RUNNINGPOINTS >= 5)  
OR  
(RUNNINGPOINTS - POINTS < 6 AND RUNNINGPOINTS >= 6)  
OR  
(RUNNINGPOINTS - POINTS < 7 AND RUNNINGPOINTS >= 7)  
OR  
(RUNNINGPOINTS - POINTS < 8 AND RUNNINGPOINTS >= 8)  
OR  
(RUNNINGPOINTS - POINTS < 9 AND RUNNINGPOINTS >= 9)  
)  
  
UPDATE A SET TRIGGERID = (SELECT COUNT(*)+1 FROM @ACTIONS WHERE EVENTDATE BETWEEN DATEADD(m, -12, A.EVENTDATE) AND A.EVENTDATE AND TRIGGERID = 0) FROM @ACTIONS A WHERE TRIGGERID = 0  
  
UPDATE @ACTIONS SET DETAILS = 'Verbal Warning' WHERE TRIGGERID = 1  
UPDATE @ACTIONS SET DETAILS = 'Written Warning (1st)' WHERE TRIGGERID = 2  
UPDATE @ACTIONS SET DETAILS = 'Written Warning (2nd)' WHERE TRIGGERID = 3  
UPDATE @ACTIONS SET DETAILS = 'Written Warning (3rd)' WHERE TRIGGERID = 4
UPDATE @ACTIONS SET DETAILS = 'Beyond 3rd Writtn Warning!!' WHERE TRIGGERID in (5,6)  
UPDATE @ACTIONS SET DETAILS = 'Termination Pts>6' WHERE TRIGGERID = 7 
  
--LOOK FOR @ACTIONS WITH NO CORRESPONDING @TRIGGERACTIONS RECORD, INDICATING AN @ACTION FOR INSERTION  
UPDATE A SET DATAACTION = 'I'  
FROM @ACTIONS A  
WHERE ((SELECT COUNT(*) FROM @TRIGGERACTIONS WHERE FILEKEY = A.FILEKEY AND EVENTDATE = A.EVENTDATE AND TRIGGERID = A.TRIGGERID) = 0)  
  
--LOOK FOR @TRIGGERACTIONS WITH NO CORRESPONDING @ACTIONS RECORD, INDICATING A @TRIGGERACTION FOR DELETION  
INSERT INTO @ACTIONS  
(DATAACTION, DATAID)  
SELECT 'D', T.UNIQUEID  
FROM @TRIGGERACTIONS T  
WHERE ((SELECT COUNT(*) FROM @ACTIONS WHERE FILEKEY = T.FILEKEY AND EVENTDATE = T.EVENTDATE AND TRIGGERID = T.TRIGGERID) = 0)  
  
--CLEAR NON-ACTION ITEMS FROM @ACTIONS TABLE  
DELETE FROM @ACTIONS WHERE DATAACTION = '?'  
  
--DELETE MARKED TRIGGERACTIONS THAT ARE NO LONGER APPLICABLE  
DELETE T FROM TRIGGERACTIONS T, @ACTIONS A  
WHERE A.DATAID = T.UNIQUEID  
AND A.DATAACTION = 'D'  
  
--INSERT NEW TRIGGERACTIONS FROM @ACTIONS TABLE  
INSERT INTO TRIGGERACTIONS  
SELECT FILEKEY, EVENTDATE, TRIGGERID, OCCURRED, TRIGGERTYPE, DETAILS, DATA  
FROM @ACTIONS  
WHERE DATAACTION = 'I'  
  
SET NOCOUNT OFF  
GO