 <?php
// Database connection file (config.php)
require 'config.php';



// Step 1: Truncate the target table
$sql1 = "TRUNCATE TABLE plots_ic_attendance.woff";
if ($conn->query($sql1) === TRUE) {
    echo "Successfully truncated plots_ic_attendance.woff--";
} else {
    echo "Error: " . $sql1 . "<br>" . $conn->error;
}


// Step 3: Call week‑off procedures and insert week‑off dates,
// excluding dates that are already inserted for the same employee
// and also excluding dates that fall between apply_from and apply_to for that empid.



// Procedure: All Sunday
$sql2 = "CALL GetAllSundays(2025)";
if ($conn->query($sql24) === TRUE) {
    echo "Successfully called GetAllSundays--";
} else {
    echo "Error: " . $sql24 . "<br>" . $conn->error;
}

$sql25 = "INSERT INTO plots_ic_attendance.woff (empid, DateWoff, status)
SELECT e.empid, t.DateWoff, 'WOFF'
FROM plots_attendance.emp_weekoff e
JOIN TempWeekOffDates t
WHERE e.WeekOff = 'All Sunday'
  AND NOT EXISTS (SELECT 1 FROM plots_ic_attendance.woff w WHERE w.empid = e.empid AND w.DateWoff = t.DateWoff)
  
      WHERE r.empid = e.empid
        AND t.DateWoff BETWEEN r.apply_from AND r.apply_to
  )";
if ($conn->query($sql2) === TRUE) {
    echo "Successfully inserted week-off for All Sunday--";
} else {
    echo "Error: " . $sql2 . "<br>" . $conn->error;
}

$conn->close();
?>
