create or replace PROCEDURE HRIS_REATTENDANCE(
    P_FROM_ATTENDANCE_DT date,
    P_EMPLOYEE_ID int default null,
    P_TO_ATTENDANCE_DT DATE default NULL)
AS
  V_ATTENDANCE_DT DATE;
  V_DATE_DIFF        int;
  V_EMPLOYEE_ID int;
  V_IN_TIME timestamp DEFAULT NULL;
  V_OUT_TIME timestamp DEFAULT NULL;
  V_DETAIL_ID INT DEFAULT NULL;
  V_SHIFT_ID INT DEFAULT NULL;
  V_HOLIDAY_ID INT DEFAULT NULL;
  V_LEAVE_ID INT DEFAULT NULL;
  V_TRAVEL_ID INT DEFAULT NULL;
  V_WEEKDAY INT DEFAULT NULL;
  V_DAYOFF varchar(100) DEFAULT NULL;
  V_HALFDAY varchar(100) DEFAULT NULL;
  V_TOTAL_DAYS int;
  V_WEEKDAY_STATUS VARCHAR(1000) DEFAULT NULL;
  V_TRAINING_ID int DEFAULT NULL;
  V_TRAINING_TYPE varchar(100) DEFAULT NULL;
  V_OVERALL_STATUS VARCHAR(100) DEFAULT NULL;
  V_WORKING_HOUR INT;
  V_LATE_STATUS VARCHAR(100);
  V_TWO_DAY_SHIFT CHAR(1);
  v_dummy int default 0;
  I INT;
  V_TODAY_DATE DATE;
  V_TOTAL_HOUR INT;
  V_IN_REMARKS VARCHAR(2000);
  V_OUT_REMARKS VARCHAR(2000);
BEGIN

SELECT DAYS_BETWEEN(P_FROM_ATTENDANCE_DT, (CASE WHEN P_TO_ATTENDANCE_DT IS NULL THEN CURRENT_DATE 
ELSE P_TO_ATTENDANCE_DT END)) + 1 INTO V_TOTAL_DAYS FROM DUMMY;

I = 0;
--LOOP THROUGH START AND END DATE
WHILE I < V_TOTAL_DAYS DO
SELECT ADD_DAYS(P_FROM_ATTENDANCE_DT, I) INTO V_TODAY_DATE FROM DUMMY;
I = I + 1;

SELECT WEEKDAY(V_TODAY_DATE) INTO V_WEEKDAY FROM DUMMY;

IF V_WEEKDAY <= 5 THEN 
V_WEEKDAY = V_WEEKDAY + 2;
ELSE
V_WEEKDAY = V_WEEKDAY - 5;
END IF;

INSERT INTO DUMMY_TABLE_PRATIK VALUES('WEEKDAY', V_WEEKDAY);

BEGIN
	DECLARE CURSOR EMPLOYEE FOR (
	SELECT EMPLOYEE_ID FROM HRIS_EMPLOYEES WHERE STATUS = 'E' AND 
	(EMPLOYEE_ID =
	      CASE
	        WHEN P_EMPLOYEE_ID IS NOT NULL
	        THEN P_EMPLOYEE_ID
	      END
	    OR P_EMPLOYEE_ID IS NULL)
	);
	--LOOP THROUGH EMPLOYEES
		FOR E AS EMPLOYEE DO
		V_EMPLOYEE_ID := E.EMPLOYEE_ID;
--FETCH SHIFT
		SELECT SHIFT_ID INTO V_SHIFT_ID DEFAULT NULL FROM HRIS_SHIFTS WHERE DEFAULT_SHIFT = 'Y';
		
--GET IN TIME AND OUT TIME
		SELECT MIN(ATTENDANCE_TIME) INTO V_IN_TIME 
		default null FROM HRIS_ATTENDANCE
		WHERE EMPLOYEE_ID = V_EMPLOYEE_ID AND ATTENDANCE_DT = V_TODAY_DATE limit 1;
		
		SELECT MAX(ATTENDANCE_TIME) INTO V_OUT_TIME 
		default null FROM HRIS_ATTENDANCE
		WHERE EMPLOYEE_ID = V_EMPLOYEE_ID AND ATTENDANCE_DT = V_TODAY_DATE limit 1;
		
		SELECT REMARKS INTO V_IN_REMARKS DEFAULT NULL FROM HRIS_ATTENDANCE
		WHERE EMPLOYEE_ID = V_EMPLOYEE_ID AND ATTENDANCE_DT = V_TODAY_DATE 
		AND ATTENDANCE_TIME = (SELECT MIN(ATTENDANCE_TIME) FROM HRIS_ATTENDANCE
		WHERE EMPLOYEE_ID = V_EMPLOYEE_ID AND ATTENDANCE_DT = V_TODAY_DATE) LIMIT 1;
		
		SELECT REMARKS INTO V_OUT_REMARKS DEFAULT NULL FROM HRIS_ATTENDANCE
		WHERE EMPLOYEE_ID = V_EMPLOYEE_ID AND ATTENDANCE_DT = V_TODAY_DATE 
		AND ATTENDANCE_TIME = (SELECT MAX(ATTENDANCE_TIME) FROM HRIS_ATTENDANCE
		WHERE EMPLOYEE_ID = V_EMPLOYEE_ID AND ATTENDANCE_DT = V_TODAY_DATE) LIMIT 1;
		
		IF V_OUT_TIME = V_IN_TIME OR SECONDS_BETWEEN(V_IN_TIME, V_OUT_TIME) < (60*5) THEN 
			V_OUT_TIME := NULL;
		END IF;
		
		IF V_IN_TIME IS NOT NULL THEN
			IF V_OVERALL_STATUS = 'DO' THEN 
				V_OVERALL_STATUS = 'WD';
			ELSEIF
			V_OVERALL_STATUS = 'HD' THEN 
				V_OVERALL_STATUS = 'WH';
			ELSE
			V_OVERALL_STATUS = 'PR';
			END IF;
		ELSE
		V_OVERALL_STATUS = 'AB';
		END IF;

		
--FETCH DAYOFF
		IF V_WEEKDAY = 1 THEN 
		SELECT WEEKDAY1 INTO V_WEEKDAY_STATUS FROM HRIS_SHIFTS WHERE SHIFT_ID = V_SHIFT_ID
		limit 1;
		ELSEIF V_WEEKDAY = 2 THEN 
		SELECT WEEKDAY2 INTO V_WEEKDAY_STATUS FROM HRIS_SHIFTS WHERE SHIFT_ID = V_SHIFT_ID
		limit 1;
		ELSEIF V_WEEKDAY = 3 THEN 
		SELECT WEEKDAY3 INTO V_WEEKDAY_STATUS FROM HRIS_SHIFTS WHERE SHIFT_ID = V_SHIFT_ID
		limit 1;
		ELSEIF V_WEEKDAY = 4 THEN 
		SELECT WEEKDAY4 INTO V_WEEKDAY_STATUS FROM HRIS_SHIFTS WHERE SHIFT_ID = V_SHIFT_ID
		limit 1;
		ELSEIF V_WEEKDAY = 5 THEN 
		SELECT WEEKDAY5 INTO V_WEEKDAY_STATUS FROM HRIS_SHIFTS WHERE SHIFT_ID = V_SHIFT_ID
		limit 1;
		ELSEIF V_WEEKDAY = 6 THEN 
		SELECT WEEKDAY6 INTO V_WEEKDAY_STATUS FROM HRIS_SHIFTS WHERE SHIFT_ID = V_SHIFT_ID
		limit 1;
		ELSEIF V_WEEKDAY = 7 THEN 
		SELECT WEEKDAY7 INTO V_WEEKDAY_STATUS FROM HRIS_SHIFTS WHERE SHIFT_ID = V_SHIFT_ID
		limit 1;
		END IF;
		
		IF V_WEEKDAY_STATUS = 'DAY_OFF' THEN
			V_OVERALL_STATUS = 'DO';
		END IF;
		
INSERT INTO DUMMY_TABLE_PRATIK VALUES('WEEKDAY', V_WEEKDAY_STATUS);
		
--CHECK HOLIDAY
		SELECT HMS.HOLIDAY_ID INTO V_HOLIDAY_ID DEFAULT NULL 
		FROM HRIS_HOLIDAY_MASTER_SETUP HMS
		JOIN HRIS_EMPLOYEE_HOLIDAY HA ON (HA.HOLIDAY_ID = HMS.HOLIDAY_ID AND HA.EMPLOYEE_ID = V_EMPLOYEE_ID) 
		WHERE (V_TODAY_DATE BETWEEN START_DATE AND END_DATE) AND HMS.STATUS = 'E' limit 1;
		
		IF V_HOLIDAY_ID IS NOT NULL THEN
			V_OVERALL_STATUS = 'HD';
		END IF;
		
--CHECK LEAVE
		SELECT LMS.LEAVE_ID INTO V_LEAVE_ID 
		DEFAULT NULL FROM HRIS_LEAVE_MASTER_SETUP LMS JOIN HRIS_EMPLOYEE_LEAVE_REQUEST
		ELR ON (ELR.LEAVE_ID = LMS.LEAVE_ID AND ELR.EMPLOYEE_ID = V_EMPLOYEE_ID) WHERE ELR.STATUS = 'AP'
		AND (V_TODAY_DATE BETWEEN ELR.START_DATE AND ELR.END_DATE) AND LMS.STATUS = 'E' limit 1;
		
		IF V_LEAVE_ID IS NOT NULL THEN
			V_OVERALL_STATUS = 'LV';
		END IF;
		
--CHECK TRAVEL
		SELECT TRAVEL_ID INTO V_TRAVEL_ID 
		DEFAULT NULL FROM HRIS_EMPLOYEE_TRAVEL_REQUEST WHERE STATUS = 'AP'
		AND (V_TODAY_DATE BETWEEN FROM_DATE AND TO_DATE) AND EMPLOYEE_ID = V_EMPLOYEE_ID limit 1;
		
		IF V_TRAVEL_ID IS NOT NULL THEN
			V_OVERALL_STATUS = 'TV';
		END IF;
		
--CHECK TRAINING
		SELECT TRAINING_ID INTO V_TRAINING_ID 
		DEFAULT NULL FROM HRIS_EMPLOYEE_TRAINING_REQUEST WHERE STATUS = 'AP'
		AND (V_TODAY_DATE BETWEEN START_DATE AND END_DATE) AND EMPLOYEE_ID = V_EMPLOYEE_ID limit 1;
		
		IF V_TRAINING_ID IS NOT NULL THEN
			V_OVERALL_STATUS = 'TR';
		END IF;

--DELETE AND INSERT IN ATTENDANCE DETAIL
		
		DELETE FROM HRIS_ATTENDANCE_DETAIL WHERE EMPLOYEE_ID = V_EMPLOYEE_ID AND ATTENDANCE_DT = V_TODAY_DATE;

		INSERT
	      INTO HRIS_ATTENDANCE_DETAIL
	        (
	          ID,
	          EMPLOYEE_ID,
	          ATTENDANCE_DT,
	          IN_TIME,
	          OUT_TIME,
	          SHIFT_ID,
	          DAYOFF_FLAG,
	          HALFDAY_FLAG,
	          --HALFDAY_PERIOD,
	         -- GRACE_PERIOD,
	          HOLIDAY_ID,
	          LEAVE_ID,
	          TRAVEL_ID,
	          TRAINING_ID,
	          TRAINING_TYPE,
	          OVERALL_STATUS,
	          TWO_DAY_SHIFT,
	          TOTAL_HOUR,
	          OT_MINUTES,
	          IN_REMARKS,
	          OUT_REMARKS
	          --IGNORE_TIME
	        )
	        VALUES
	        (
	          (SELECT IFNULL(MAX(ID)+1, 1) FROM HRIS_ATTENDANCE_DETAIL),
	          V_EMPLOYEE_ID,
	          V_TODAY_DATE,
	          V_IN_TIME,
	          V_OUT_TIME,
	          V_SHIFT_ID,
	          V_DAYOFF,
	          V_HALFDAY,
	          --V_LEAVE_HALFDAY_PERIOD,
	          --V_LEAVE_GRACE_PERIOD,
	          V_HOLIDAY_ID,
	          V_LEAVE_ID,
	          V_TRAVEL_ID,
	          V_TRAINING_ID,
	          V_TRAINING_TYPE,
	          (
	          CASE
	            WHEN V_OVERALL_STATUS IS NULL
	            THEN 'AB'
	            ELSE V_OVERALL_STATUS
	          END),
	          (
	          CASE
	            WHEN V_TWO_DAY_SHIFT IS NULL
	            THEN 'D'
	            ELSE V_TWO_DAY_SHIFT
	          END),
	          SECONDS_BETWEEN(V_IN_TIME, V_OUT_TIME)/60,
	          SECONDS_BETWEEN(V_IN_TIME, V_OUT_TIME)/60 - V_WORKING_HOUR,
	          V_IN_REMARKS,
	          V_OUT_REMARKS
	          --V_IGNORE_TIME
	        );
		
		END FOR;
		--LOOP THROUGH EMPLOYEES
	END;
END WHILE;
--LOOP THROUGH START AND END DATE
END;
