DROP TRIGGER update_travel_attdendance_when_insert_extracurricular_attendance;
DELIMITER $$
CREATE TRIGGER update_travel_attdendance_when_insert_extracurricular_attendance
before insert ON `extracurricular_activities_attendance` FOR EACH ROW
begin
       DECLARE id_exists Boolean;
       -- Check BookingRequest table
       set @day_ddc= (select day_ddc from date_day_configuration where date_ddc = new.attendance_date);
       set @serial_std= (select serial_std from extracurricular_activities_registration WHERE id = new.registered_id);
      
       set @serial_tat =(SELECT max(serial_tat) from travel_attendance 
                JOIN travel_student ON travel_attendance.serial_trs = travel_student.serial_trs AND travel_student.day != '' 
                JOIN travel_student_day tsd ON travel_student.serial_trs = tsd.serial_trs AND tsd.selected=1 and tsd.day = @day_ddc
                JOIN travel ON travel_student.serial_trv = travel.serial_trv AND travel.status = 'ACTIVE' 
                JOIN school_year ON travel.serial_year = school_year.serial_scy AND school_year.status_scy = 'ACTIVE,CURRENT' 
                JOIN shift ON travel.serial_sft = shift.serial_sft AND shift.type = 'EXIT' AND shift.status_sft = 'ACTIVE' 
                WHERE travel_attendance.date = new.attendance_date 
                AND travel_student.serial_std =  @serial_std);
      set @exit_type = (select exit_type from travel_attendance where serial_tat=@serial_tat);
      if ((new.attendance_type='UNEXCUSED_ABSENCE' || new.attendance_type='EXCUSED_ABSENCE') and @exit_type is null)
      then
		update travel_attendance set exit_type='NO' WHERE serial_tat= @serial_tat;
        END if;
       -- COMMIT;
END;
$$
DELIMITER ;

DROP TRIGGER update_travel_attdendance_when_update_extracurricular_attendance;
DELIMITER $$
CREATE TRIGGER update_travel_attdendance_when_update_extracurricular_attendance
before update ON `extracurricular_activities_attendance` FOR EACH ROW
begin
       DECLARE id_exists Boolean;
       -- Check BookingRequest table
       set @day_ddc= (select day_ddc from date_day_configuration where date_ddc = new.attendance_date);
       set @serial_std= (select serial_std from extracurricular_activities_registration WHERE id = new.registered_id);
      
       set @serial_tat =(SELECT max(serial_tat) from travel_attendance 
                JOIN travel_student ON travel_attendance.serial_trs = travel_student.serial_trs AND travel_student.day != '' 
                JOIN travel_student_day tsd ON travel_student.serial_trs = tsd.serial_trs AND tsd.selected=1 and tsd.day = @day_ddc
                JOIN travel ON travel_student.serial_trv = travel.serial_trv AND travel.status = 'ACTIVE' 
                JOIN school_year ON travel.serial_year = school_year.serial_scy AND school_year.status_scy = 'ACTIVE,CURRENT' 
                JOIN shift ON travel.serial_sft = shift.serial_sft AND shift.type = 'EXIT' AND shift.status_sft = 'ACTIVE' 
                WHERE travel_attendance.date = new.attendance_date 
                AND travel_student.serial_std =  @serial_std);
      set @exit_type = (select exit_type from travel_attendance where serial_tat=@serial_tat);
      if ((new.attendance_type='UNEXCUSED_ABSENCE' || new.attendance_type='EXCUSED_ABSENCE') and @exit_type is null)
      then
		update travel_attendance set exit_type='NO' WHERE serial_tat= @serial_tat;
        END if;
       -- COMMIT;
END;
$$
DELIMITER ;



DROP TRIGGER update_travel_attdendance_when_delete_extracurricular_attendance;
DELIMITER $$
CREATE TRIGGER update_travel_attdendance_when_delete_extracurricular_attendance
before delete ON `extracurricular_activities_attendance` FOR EACH ROW
begin
       DECLARE id_exists Boolean;
       -- Check BookingRequest table
       set @day_ddc= (select day_ddc from date_day_configuration where date_ddc = old.attendance_date);
       set @serial_std= (select serial_std from extracurricular_activities_registration WHERE id = old.registered_id);
      
       set @serial_tat =(SELECT max(serial_tat) from travel_attendance 
                JOIN travel_student ON travel_attendance.serial_trs = travel_student.serial_trs AND travel_student.day != '' 
                JOIN travel_student_day tsd ON travel_student.serial_trs = tsd.serial_trs AND tsd.selected=1 and tsd.day = @day_ddc
                JOIN travel ON travel_student.serial_trv = travel.serial_trv AND travel.status = 'ACTIVE' 
                JOIN school_year ON travel.serial_year = school_year.serial_scy AND school_year.status_scy = 'ACTIVE,CURRENT' 
                JOIN shift ON travel.serial_sft = shift.serial_sft AND shift.type = 'EXIT' AND shift.status_sft = 'ACTIVE' 
                WHERE travel_attendance.date = old.attendance_date 
                AND travel_student.serial_std =  @serial_std);
      set @exit_type = (select exit_type from travel_attendance where serial_tat=@serial_tat);
      if (@exit_type ='NO')
       then
		update travel_attendance set exit_type=null WHERE serial_tat= @serial_tat;
	END if;
        -- COMMIT;
END;
$$
DELIMITER ;