<?php
// DATABASE CONNECTION (use your config or adjust as needed)
$db = new PDO('mysql:host=localhost;dbname=school_checkin;charset=utf8', 'root', '');

// --- GET GRADE LEVELS, MONTHS, SCHOOL YEARS ---
$grades = ['Nursery','Kindergarten','Grade 1','Grade 2','Grade 3','Grade 4','Grade 5','Grade 6'];
$months = [
    1 => 'January', 2 => 'February', 3 => 'March', 4 => 'April',
    5 => 'May', 6 => 'June', 7 => 'July', 8 => 'August',
    9 => 'September', 10 => 'October', 11 => 'November', 12 => 'December'
];
$school_years = [];
$current_year = date('Y');
for ($y = $current_year-2; $y <= $current_year+1; $y++) {
    $school_years[] = $y . '-' . ($y+1);
}

// --- Get selected grade, school year, month, section ---
$selected_grade = isset($_POST['grade_level']) ? $_POST['grade_level'] : (isset($_GET['grade_level']) ? $_GET['grade_level'] : $grades[0]);
$selected_school_year = isset($_POST['school_year']) ? $_POST['school_year'] : (isset($_GET['school_year']) ? $_GET['school_year'] : $school_years[0]);
$selected_month = isset($_POST['month']) ? intval($_POST['month']) : (isset($_GET['month']) ? intval($_GET['month']) : 7);
$selected_section = isset($_POST['section']) ? $_POST['section'] : (isset($_GET['section']) ? $_GET['section'] : '');

// --- For Name of School field (default value) ---
$default_school_name = "SIOCON DISTRICT ADVENTIST ELEMENTARY SCHOOL INC";
$school_name = isset($_POST['school_name']) ? $_POST['school_name'] : $default_school_name;

//get adviser
$stmt = $db->prepare("SELECT name FROM advisers WHERE grade = ?");
$stmt->execute([$selected_grade]);
$adviser = $stmt->fetchAll(PDO::FETCH_ASSOC);
$adviser_name = isset($adviser[0]['name']) ? $adviser[0]['name'] : '';

// For school year, extract start and end
list($sy_start, $sy_end) = explode('-', $selected_school_year);
$sy_start = intval($sy_start);
$sy_end = intval($sy_end);

// --- Year logic: June-Dec = sy_start, Jan-May = sy_end (DepEd style) ---
if ($selected_month >= 6) {
    $year = $sy_start;
} else {
    $year = $sy_end;
}

// --- FETCH STUDENTS BASED ON SELECTED GRADE ---
$stmt = $db->prepare("SELECT lrn, last_name, first_name, middle_name, sex FROM students WHERE grade = ?");
$stmt->execute([$selected_grade]);
$students = $stmt->fetchAll(PDO::FETCH_ASSOC);

// --- MALE/FEMALE COUNT FOR SUMMARY TABLE ---
$male = 0;
$female = 0;
foreach ($students as $student) {
    $sx = strtolower(trim($student['sex']));
    if ($sx === 'm' || $sx === 'male') $male++;
    if ($sx === 'f' || $sx === 'female') $female++;
}
$grand = $male + $female;

// --- Generate dates for the selected month ---
$days_in_month = cal_days_in_month(CAL_GREGORIAN, $selected_month, $year);

$dates = [];
for ($d = 1; $d <= $days_in_month; $d++) {
    $dates[] = sprintf("%04d-%02d-%02d", $year, $selected_month, $d);
}

// --- SCHOOL CALENDAR FUNCTION ---
function isSchoolDay($date) {
    $dayOfWeek = date('N', strtotime($date)); // 1=Monday, 7=Sunday
    if ($dayOfWeek >= 6) return false; // Saturday or Sunday
    
    $holidays = [ /* Add holidays if needed */ ];
    if (in_array($date, $holidays)) return false;
    
    return true;
}

// --- Check if there is ANY attendance data for the selected month/grade ---
$data_check_stmt = $db->prepare(
    "SELECT 1 FROM sf2 JOIN students ON sf2.lrn = students.lrn WHERE students.grade = ? AND MONTH(sf2.date) = ? AND YEAR(sf2.date) = ? LIMIT 1"
);
$data_check_stmt->execute([$selected_grade, $selected_month, $year]);
$month_has_data = $data_check_stmt->fetchColumn();


// --- EXPORT TO EXCEL FEATURE ---
if (isset($_POST['export_excel'])) {
    $filename = "SF2_" . $selected_grade . "_" . str_replace(' ', '_', $months[$selected_month]) . "_" . $year . ".xls";
    header("Content-Type: application/vnd.ms-excel; charset=utf-8");
    header("Content-Disposition: attachment; filename=\"" . $filename . "\"");
    ob_start();
    ?>
    <html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns="http://www.w3.org/TR/REC-html40">
    <head><meta http-equiv="Content-Type" content="text/html; charset=utf-8"><!--[if gte mso 9]><xml><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet><x:Name>SF2</x:Name><x:WorksheetOptions><x:DisplayGridlines/></x:WorksheetOptions></x:ExcelWorksheet></x:ExcelWorksheets></x:ExcelWorkbook></xml><![endif]-->
        <style>body{font-family:Calibri,sans-serif}table{border-collapse:collapse}th,td{border:1px solid black;text-align:center;vertical-align:middle}.left{text-align:left;padding-left:5px}.no-border{border:none}.bold{font-weight:bold}</style>
    </head>
    <body>
    <div style="text-align:center;font-weight:bold;">School Form 2 (SF2) Daily Attendance Report of Learners</div><br>
    <table class="no-border">
        <tr><td class="no-border">School ID:</td><td class="no-border bold">404833</td><td class="no-border"></td><td class="no-border">School Year:</td><td class="no-border bold"><?= $selected_school_year ?></td><td class="no-border"></td><td class="no-border">Month:</td><td class="no-border bold"><?= $months[$selected_month] ?></td></tr>
        <tr><td class="no-border" colspan="2">Name of School:</td><td class="no-border bold" colspan="6"><?= htmlspecialchars($school_name) ?></td></tr>
    </table><br>
    <table>
        <thead>
        <tr><th rowspan="2">No</th><th rowspan="2" style="width:200px;">NAME<br>(Last Name, First Name, Middle Name)</th><th colspan="<?= count($dates) ?>">Date</th><th rowspan="2">ABSENT</th><th rowspan="2">PRESENT</th><th rowspan="2">LATE</th><th rowspan="2" style="width:150px;">REMARKS</th></tr>
        <tr><?php foreach ($dates as $d): echo '<th>' . date('j', strtotime($d)) . '</th>'; endforeach; ?></tr>
        </thead>
        <tbody>
        <?php
        date_default_timezone_set('Asia/Manila');
        $cdate = date('Y-m-d');
        foreach ($students as $i => $student):
            $student_lrn = $student['lrn'];
            $absent = 0; $present = 0; $late = 0;
            ?>
            <tr>
                <td><?= $i + 1 ?></td><td class="left"><?= htmlspecialchars($student['last_name'] . ', ' . $student['first_name'] . ' ' . $student['middle_name']) ?></td>
                <?php
                foreach ($dates as $d) {
                    $cell = "";
                    if (isSchoolDay($d)) {
                        if ($month_has_data && $d <= $cdate) {
                            $att_stmt = $db->prepare("SELECT am_absent, pm_absent FROM sf2 WHERE lrn=? AND date=? LIMIT 1");
                            $att_stmt->execute([$student_lrn, $d]);
                            $att = $att_stmt->fetch(PDO::FETCH_ASSOC);
                            if ($att) {
                                if ($att['am_absent'] == 1 || $att['pm_absent'] == 1) { $cell = "❌"; $absent++; }
                                else if ($att['am_absent'] == 2 || $att['pm_absent'] == 2) { $cell = "L"; $late++; }
                                else { $present++; }
                            } else { $present++; }
                        }
                    } else { $cell = "-"; }
                    echo "<td>$cell</td>";
                }
                ?>
                <td><?= $absent > 0 ? $absent : '' ?></td><td><?= $present > 0 ? $present : '' ?></td><td><?= $late > 0 ? $late : '' ?></td><td></td>
            </tr>
        <?php endforeach; ?>
        </tbody>
    </table><br>
    <?php
    $school_days = 0;
    if ($month_has_data) { foreach ($dates as $d) { if (isSchoolDay($d) && $d <= $cdate) $school_days++; } }
    ?>
    <table style="width: 50%; float: right;">
        <tr><th colspan="4">Month : <?= $months[$selected_month] ?></th></tr>
        <tr><th>No. of Days of Classes :</th><th>M</th><th>F</th><th>TOTAL</th></tr>
        <tr><td>Enrolment as of (1st Friday of June)</td><td><?= $male ?></td><td><?= $female ?></td><td><?= $grand ?></td></tr>
        <tr><td>Late enrollees during the month</td><td></td><td></td><td></td></tr>
        <tr><td>Registered and present</td><td></td><td></td><td></td></tr>
        <tr><td>Absent and all dropped</td><td></td><td></td><td></td></tr>
        <tr><td>Percentage of Enrolment and end of month</td><td colspan="3"></td></tr>
        <tr><td>Average Daily Attendance</td><td colspan="3"></td></tr>
        <tr><td>Percentage of Attendance for the month</td><td colspan="3"></td></tr>
        <tr><td>Number of school days in reporting month</td><td colspan="3"><?= $school_days > 0 ? $school_days : '' ?></td></tr>
        <tr><td>Dropped out</td><td colspan="3"></td></tr>
        <tr><td>Transferred out</td><td colspan="3"></td></tr>
        <tr><td>Transferred in</td><td colspan="3"></td></tr>
    </table>
    </body></html>
    <?php
    echo ob_get_clean();
    exit;
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8"><title>School Form 2 (SF2) Daily Attendance Report of Learners</title>
    <style id="theme-style">
        body{font-family:Arial,sans-serif;font-size:13px;margin:20px;background:#f9f9f9;color:#222}h2{text-align:center}table{border-collapse:collapse;width:100%}th,td{border:1px solid #333;padding:3px 5px;text-align:center}th{background:#f0f0f0}.header-table td{border:none}.left{text-align:left}.remarks{font-size:11px}.attendance-table{margin-bottom:0}.excel-footer-flex{display:flex;flex-direction:row;gap:0;align-items:flex-start;margin-top:22px}.excel-footer-flex>div{background:#fff}.excel-guidelines{flex:1.2;min-width:380px;border:1px solid #333;border-right:none;padding:9px 10px 12px 12px}.excel-codes{flex:1.1;min-width:270px;border-top:1px solid #333;border-bottom:1px solid #333;border-right:1px solid #333;padding:8px 10px 12px 10px}.excel-summary{flex:1.2;min-width:320px;border-top:1px solid #333;border-bottom:1px solid #333;border-right:1px solid #333;padding:8px 10px 4px 10px}.excel-guidelines ol,.excel-guidelines table{font-size:13px}@media (max-width:1000px){.excel-footer-flex{flex-direction:column}.excel-guidelines,.excel-codes,.excel-summary{border-right:1px solid #333;margin-bottom:8px}}input[type="text"]{font-size:13px}.absent{color:red;font-weight:bold}.late{color:orange}.weekend{background:#f0f0f0;color:#888}.holiday{background:#ffe6e6;color:#666}.school-name-row{text-align:center}.school-name-input{width:420px;min-width:250px;max-width:98%;text-align:center;margin:0 auto;display:inline-block}.btn{border:none;padding:7px 18px;border-radius:7px;font-size:15px;cursor:pointer;margin-bottom:10px;margin-right:8px;text-decoration:none;display:inline-block;transition:background .2s}.btn-back{background:#bb2d3b!important;color:#fff!important}.btn-back:hover{background:#a71d2a!important}.btn-print{background:#0d6efd!important;color:#fff!important}.btn-print:hover{background:#0b5ed7!important}body.dark-mode{background:#181818!important;color:#e2e2e2!important}body.dark-mode h2{color:#fff}body.dark-mode th,body.dark-mode td{border-color:#444!important}body.dark-mode th{background:#333!important;color:#edf3ff!important}body.dark-mode .header-table td{border:none}body.dark-mode .excel-footer-flex>div{background:#232323!important;color:#edecec!important}body.dark-mode .excel-guidelines,body.dark-mode .excel-codes,body.dark-mode .excel-summary{border-color:#444!important}body.dark-mode .school-name-input{background:#252525!important;color:#eee;border-color:#444}body.dark-mode .absent{color:#ff6565!important}body.dark-mode .late{color:#ffc400!important}body.dark-mode .remarks{color:#ccc}body.dark-mode input,body.dark-mode select{background:#222;color:#eee}body.dark-mode .excel-guidelines ol,body.dark-mode .excel-guidelines table{color:#dedede}body.dark-mode .btn-back{background:#a71d2a!important;color:#fff!important}body.dark-mode .btn-back:hover{background:#bb2d3b!important}body.dark-mode .weekend{background:#333!important;color:#999!important}body.dark-mode .holiday{background:#4a2c2c!important;color:#aaa!important}
    </style>
    <style media="print">
        @page { size: landscape; margin: 0.5in; } body { font-size: 10pt; } .no-print { display: none !important; } .print-only { display: block !important; } body, body.dark-mode { background: #fff !important; color: #000 !important; } h2 { color: #000 !important; } th, td, body.dark-mode th, body.dark-mode td { border: 1px solid #000 !important; color: #000 !important; } th, body.dark-mode th { background-color: #f0f0f0 !important; } .absent { color: #000 !important; font-weight: bold; } .weekend { background-color: #ccc !important; }
    </style>
</head>
<body>
<div class="no-print" style="text-align:right;margin-bottom:7px;">
    <a href="dashboard.php" class="btn btn-back">← Back to Dashboard</a>
    <button type="button" onclick="toggleMode()" style="padding:7px 18px;border-radius:7px;background:#007bff;color:white;border:none;cursor:pointer;">🌙/☀️ Toggle Light/Dark Mode</button>
</div>
<div class="print-only" style="display:none;">
    <h2 style="text-align:center;">School Form 2 (SF2) Daily Attendance Report of Learners</h2>
    <table style="border:none; width:100%;"><tr style="border:none;"><td style="border:none;">School ID: 404833</td><td style="border:none;">School Year: <?= $selected_school_year ?></td><td style="border:none;">Month: <?= $months[$selected_month] ?></td></tr><tr style="border:none;"><td style="border:none;" colspan="3">Name of School: <?= htmlspecialchars($school_name) ?></td></tr></table><br>
</div>
<div class="no-print"><h2>School Form 2 (SF2) Daily Attendance Report of Learners</h2></div>
<form method="post" action="">
    <div class="no-print">
        <table class="header-table" style="width: 100%;">
            <tr>
                <td>School ID: <input type="text" name="school_id" style="width:80px;" value="404833"></td>
                <td>School Year: <select name="school_year"><?php foreach ($school_years as $sy): echo "<option value='$sy' " . ($selected_school_year == $sy ? 'selected' : '') . ">$sy</option>"; endforeach; ?></select></td>
                <td>Month: <select name="month"><?php foreach ($months as $num => $name): echo "<option value='$num' " . ($selected_month == $num ? 'selected' : '') . ">$name</option>"; endforeach; ?></select></td>
                <td>Grade Level: <select name="grade_level"><?php foreach ($grades as $grade): echo "<option value='" . htmlspecialchars($grade) . "' " . ($selected_grade == $grade ? 'selected' : '') . ">" . htmlspecialchars($grade) . "</option>"; endforeach; ?></select></td>
                <td>Section: <input type="text" name="section" style="width:80px;" value="<?= htmlspecialchars($selected_section) ?>"></td>
                <td><button type="submit">Go</button></td>
            </tr>
            <tr class="school-name-row"><td colspan="6">Name of School: <input type="text" name="school_name" class="school-name-input" value="<?= htmlspecialchars($school_name) ?>" placeholder="Enter school name"></td></tr>
        </table><br>
    </div>
    <table class="attendance-table">
        <thead>
        <tr><th rowspan="2">No</th><th rowspan="2" style="min-width:170px;">NAME<br>(Last Name, First Name, Middle Name)</th><th colspan="<?= count($dates) ?>">Date (list row for date)</th><th rowspan="2">ABSENT</th><th rowspan="2">PRESENT</th><th rowspan="2">LATE</th><th rowspan="2" style="min-width:120px;">REMARKS<br><span class="remarks">(IF DROPPED OUT, state reason. If transferred in/out, write the name of school.)</span></th></tr>
        <tr><?php foreach ($dates as $d): $dayClass = !isSchoolDay($d) ? 'weekend' : ''; echo "<th class='$dayClass'>" . date('D', strtotime($d)) . "<br>" . date('d', strtotime($d)) . "<br>" . date('M', strtotime($d)) . "</th>"; endforeach; ?></tr>
        </thead>
        <tbody>
        <?php 
        date_default_timezone_set('Asia/Manila');
        $cdate = date('Y-m-d');
        foreach ($students as $i => $student):
            $student_lrn = $student['lrn'];
            $absent = 0; $present = 0; $late = 0;
            ?>
            <tr>
                <td><?= $i+1 ?></td><td class="left"><?= htmlspecialchars($student['last_name'] . ', ' . $student['first_name'] . ' ' . $student['middle_name']) ?></td>
                <?php
                foreach ($dates as $d) {
                    $cell = "";
                    if (isSchoolDay($d)) {
                        if ($month_has_data && $d <= $cdate) {
                            $att_stmt = $db->prepare("SELECT am_absent, pm_absent FROM sf2 WHERE lrn=? AND date=? LIMIT 1");
                            $att_stmt->execute([$student_lrn, $d]);
                            $att = $att_stmt->fetch(PDO::FETCH_ASSOC);
                            if ($att) {
                                if ($att['am_absent'] == 1 || $att['pm_absent'] == 1) { $cell .= "<span class='absent'>❌</span>"; $absent++; }
                                else if ($att['am_absent'] == 2 || $att['pm_absent'] == 2) { $cell .= "<span class='late'>L</span>"; $late++; }
                                else { $present++; }
                            } else { $present++; }
                        }
                    } else { $cell = "<span class='weekend'>-</span>"; }
                    echo "<td>$cell</td>";
                }
                ?>
                <td><?= $absent > 0 ? $absent : '' ?></td><td><?= $present > 0 ? $present : '' ?></td><td><?= $late > 0 ? $late : '' ?></td>
                <td><input type="text" name="student[<?= $student_lrn ?>][remarks]" style="width:120px;" class="no-print"></td>
            </tr>
        <?php endforeach; ?>
        </tbody>
    </table><br>
    <div class="no-print">
        <button type="submit">Submit</button>
        <button type="button" onclick="window.print()" class="btn btn-print">Print</button>
        <button type="submit" name="export_excel" value="1">Download to Excel</button>
    </div>
    <div class="excel-footer-flex">
        <div class="excel-guidelines"><b>GUIDELINES:</b><ol><li>The attendance shall be accomplished daily. Refer to the codes for checking learners' attendance.</li><li>Dates shall be written in the columns after Learner's Name.</li><li>To compute the following:</li></ol><table style="font-size:12px; margin-left:12px; margin-bottom:8px; border:none;"><tr><td style="border:none;">a. Percentage of Enrolment =</td><td style="border:none;">Registered Learners as of end of the month</td><td style="border:none;">x 100</td><td style="border:none;">Enrolment as of 1st Friday of the school year*</td></tr><tr><td style="border:none;">b. Average Daily Attendance =</td><td style="border:none;">Total Daily Attendance</td><td style="border:none;"></td><td style="border:none;">Number of School Days in reporting month</td></tr><tr><td style="border:none;">c. Percentage of Attendance for the month =</td><td style="border:none;">Registered Learners as of end of the month</td><td style="border:none;">x 100</td><td style="border:none;">Average daily attendance</td></tr></table><ol start="4"><li>Every end of the month, the class adviser will submit this form to the office of the principal for recording of summary table into School Form 4. Once signed by the principal, this form should be returned to the adviser.</li><li>The adviser will provide necessary interventions including but not limited to home visitation to learner/s who were absent for 5 consecutive days and/or those at risk of dropping out.</li><li>Attendance performance of learners will be reflected in Form 137 and Form 138 every grading period.</li></ol><span style="font-size:11px; margin-left:10px;">*Beginning of School Year cut-off report is every 1st Friday of the School Year</span></div>
        <div class="excel-codes"><b>1. CODES FOR CHECKING ATTENDANCE</b><div><strong>(Blank) = Present; ❌ = Absent; L = Late; - = Weekend/Holiday</strong></div><div>Upper for Late Comer, Lower for Outside (Leave)</div><b>2. REASONS/CAUSES FOR MLLS</b><ul style="margin:3px 0 0 15px;"><li><b>Domestic-Related Factors</b><ul><li>Had to take care of siblings</li><li>Early marriage/pregnancy</li><li>Family attitude towards schooling</li><li>Hunger/Malnutrition</li></ul></li><li><b>Individual-Related Factors</b><ul><li>Illness</li><li>Overage</li><li>Death</li><li>Poor academic performance</li><li>Lack of interest/Distraction</li></ul></li><li><b>School-Related Factor</b><ul><li>Teacher factor</li><li>Physical condition of classroom</li><li>Peer influence</li></ul></li><li><b>Geographic/Environmental</b><ul><li>Distance between home and school</li><li>Armed conflict</li><li>Calamities/Disaster</li></ul></li><li><b>Financial-Related</b><ul><li>Child labor, work</li></ul></li><li>Others (Specify)</li></ul></div>
        <div class="excel-summary">
            <table style="font-size:13px; width:100%;">
                <tr><th colspan="4">Month : <?= $months[$selected_month] ?></th></tr>
                <tr><th>No. of Days of Classes :</th><th>M</th><th>F</th><th>TOTAL</th></tr>
                <tr><td>Enrolment as of (1st Friday of June)</td><td><?= $male ?></td><td><?= $female ?></td><td><?= $grand ?></td></tr>
                <tr><td>Late enrollees during the month</td><td>-</td><td>-</td><td>-</td></tr>
                <tr><td>Registered and present</td><td>-</td><td>-</td><td>-</td></tr>
                <tr><td>Absent and all dropped</td><td>-</td><td>-</td><td>-</td></tr>
                <tr><td>Percentage of Enrolment and end of month</td><td colspan="3">-</td></tr>
                <tr><td>Average Daily Attendance</td><td colspan="3">-</td></tr>
                <tr><td>Percentage of Attendance for the month</td><td colspan="3">-</td></tr>
                <tr><td>Number of school days in reporting month</td><td colspan="3"><?php $school_days = 0; if ($month_has_data) { foreach ($dates as $d) { if (isSchoolDay($d) && $d <= $cdate) $school_days++; } } echo $school_days > 0 ? $school_days : ''; ?></td></tr>
                <tr><td>Dropped out</td><td colspan="3">-</td></tr>
                <tr><td>Transferred out</td><td colspan="3">-</td></tr>
                <tr><td>Transferred in</td><td colspan="3">-</td></tr>
            </table>
            <div style="margin-top:8px; margin-bottom:9px; color:#2685c7; font-style:italic;">I certify that this is a true and correct report.</div>
            <div style="margin-bottom:9px;"><span style="border-bottom:1px solid #000; width:260px; display:inline-block;text-align:center"><?= $adviser_name; ?></span><br><span style="font-size:13px;">(Signature of Adviser over Printed Name)</span></div>
            <div style="margin-bottom:8px; color:#2685c7;">Attested by:</div>
            <div><span style="border-bottom:1px solid #000; width:260px; display:inline-block;text-align:center;font-weight:bold">WINDEL BALANSAG DUMAYO</span><br><span style="font-size:13px;">(Signature of School Head over Printed Name)</span></div>
        </div>
    </div>
    <div class="no-print" style="font-size:11px; margin-top:2px; text-align:right;">Generated thru LIS</div><br>
</form>
<script>
    function toggleMode() { document.body.classList.toggle('dark-mode'); localStorage.setItem('sf2_mode', document.body.classList.contains('dark-mode') ? 'dark' : 'light'); }
    window.onload = function() { if(localStorage.getItem('sf2_mode') === 'dark') { document.body.classList.add('dark-mode'); } }
</script>
</body>
</html>