CREATE or replace PROCEDURE HRIS_RECALC_HOUSE_LEAVES(P_YES number default null)
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER
READS SQL DATA AS 
BEGIN
    -- THIS IS FOR COUNTING HOUSE LEAVE ON A DAILY BASIS  LOGIC FOR EVERY 12 DAYS ATTENDANCE WITH OTHER LEAVES EXCEPT HOUSE AND EXTRAORDINARY LEAVE, ADD 1 HOUSE LEAVE
    
    DECLARE v_i INT; 
    declare v_total_days int;
    DECLARE v_last_update_date DATE;
    DECLARE v_join_date_or_last_calc_date DATE;
    DECLARE v_count_attendance_days INT;
    DECLARE remaining_attendance_days INT;
    DECLARE cursor all_employees FOR 
        SELECT employee_id, join_date 
        FROM hris_employees 
        WHERE status = 'E' 
        AND resigned_flag = 'N' 
        AND retired_flag = 'N' 
        AND employee_type = 'R';
  
    FOR employee AS all_employees DO    
        
          
        DECLARE CURSOR attendance_dets FOR 
            SELECT hlmc.month_no, had. 
            FROM hris_attendance_detail had 
            LEFT JOIN hris_leave_month_code hlmc ON had.attendance_dt BETWEEN hlmc.from_date AND hlmc.to_date
            WHERE had.employee_id = employee.employee_id 
            AND had.attendance_dt  (SELECT MAX(modified_dt) 
        FROM hris_employee_leave_assign 
        WHERE employee_id = employee.employee_id 
        AND leave_id = 12) 
            AND had.attendance_dt = CURRENT_DATE
            ORDER BY had.attendance_dt ASC;
            
            -- Last modified
            SELECT MAX(modified_dt) into v_last_update_date
        FROM hris_employee_leave_assign 
        WHERE employee_id = employee.employee_id 
        AND leave_id = 12;
        
        -- This is because we called a procedure at this date we need to calculate after this for remaining_attendance_days
        IF employee.join_date  '2023-07-17' THEN
            v_join_date_or_last_calc_date = employee.join_date;
        END IF;
        
        v_join_date_or_last_calc_date = '2023-07-17';
        
      
        
-- We floor it because we have already calculated until the last update date
        SELECT MOD(COUNT(), 12),TO_INTEGER(count()12) INTO remaining_attendance_days, v_total_days
        FROM hris_attendance_detail 
        WHERE employee_id = employee.employee_id 
        AND attendance_dt BETWEEN v_join_date_or_last_calc_date AND v_last_update_date
        AND (leave_id IS NULL OR leave_id  12);    
        v_i = 0;
        v_count_attendance_days = remaining_attendance_days;
        
        
        FOR attendance_det AS attendance_dets DO
            -- 12 = HOUSE_LEAVE and 18 = EXTRAORDINARY_LEAVE
            IF attendance_det.leave_id = 12 OR attendance_det.leave_id = 18 THEN
                -- Do nothing
            ELSE
                v_count_attendance_days = v_count_attendance_days + 1;
            END IF;
            
            IF v_count_attendance_days = 12 THEN
                v_i = v_i + 1;
                v_count_attendance_days = 0;
                
                
                UPDATE hris_employee_leave_assign 
                SET total_days = v_total_days+v_i, modified_dt = CURRENT_DATE
                WHERE employee_id = employee.employee_id 
                AND leave_id = 12 
                AND fiscal_year_month_no = attendance_det.month_no; 
            END IF;    	
        END FOR;
    END FOR;
        
END;