DROP TRIGGER update_travel_attdendance_when_insert_attendance;
DELIMITER $$
CREATE TRIGGER update_travel_attdendance_when_insert_attendance
before insert ON `attendance` FOR EACH ROW
begin
       DECLARE id_exists Boolean;
       -- Check BookingRequest table
       set @day_scd= (select max(day_scd) from schedule WHERE serial_scd = new.serial_scd);
       set @serial_crs= (select max(serial_crs) from schedule WHERE serial_scd = new.serial_scd);
       set @serial_cdt_new= (select max(serial_cdt) from schedule WHERE serial_scd = new.serial_scd);
       set @serial_cdt= (select schedule.serial_cdt from schedule
       join credits_detail on schedule.serial_cdt=credits_detail.serial_cdt 
       WHERE serial_crs =  @serial_crs and day_scd= @day_scd
       order by end_at_cdt desc limit 1 );
       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_scd
                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.date_ace 
                AND travel_student.serial_std = new.serial_std );
      set @exit_type = (select exit_type from travel_attendance where serial_tat=@serial_tat);
      if ((new.type_ace='UNEXCUSED_ABSENCE' || new.type_ace='EXCUSED_ABSENCE') and new.status_ace='ACTIVE' and @exit_type is null) && @serial_cdt=@serial_cdt_new
      then
		update travel_attendance set exit_type='NO' WHERE serial_tat= @serial_tat;
        END if;
       -- COMMIT;
END;
$$
DELIMITER ;

DROP TRIGGER update_travel_attdendance_when_updte_attendance;
DELIMITER $$
CREATE TRIGGER update_travel_attdendance_when_updte_attendance
before UPDATE ON `attendance` FOR EACH ROW
begin
       DECLARE id_exists Boolean;
       -- Check BookingRequest table
       set @day_scd= (select max(day_scd) from schedule WHERE serial_scd = new.serial_scd);
       set @serial_cdt_new= (select max(serial_cdt) from schedule WHERE serial_scd = new.serial_scd);
       set @serial_crs= (select max(serial_crs) from schedule WHERE serial_scd = new.serial_scd);
       set @serial_cdt= (select schedule.serial_cdt from schedule
       join credits_detail on schedule.serial_cdt=credits_detail.serial_cdt 
       WHERE serial_crs =  @serial_crs and day_scd= @day_scd
       order by end_at_cdt desc limit 1 );
       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_scd
                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.date_ace 
                AND travel_student.serial_std = new.serial_std );
      set @exit_type = (select exit_type from travel_attendance where serial_tat=@serial_tat);
      if (((new.type_ace!='UNEXCUSED_ABSENCE' && new.type_ace!='EXCUSED_ABSENCE') and new.status_ace='ACTIVE' ) || new.status_ace='INACTIVE') and @exit_type ='NO' and @serial_cdt=@serial_cdt_new
      then
		update travel_attendance set exit_type= NULL WHERE serial_tat= @serial_tat;
        END if;
        
          if ((new.type_ace='UNEXCUSED_ABSENCE' || new.type_ace='EXCUSED_ABSENCE') and new.status_ace='ACTIVE' and @exit_type is null) && @serial_cdt=@serial_cdt_new
      then
		update travel_attendance set exit_type='NO' WHERE serial_tat= @serial_tat;
        END if;
       -- COMMIT;
END;
$$
DELIMITER ;