<?php
use MYPDF as GlobalMYPDF;

ini_set('memory_limit', '2024M');
// error_reporting(0);

include '../class/database.php';
include '../class/config.php';
include '../class/userAuth.php';
include '../class/masterInfo.php';

$config = new config();
$db = new database();
$conn = $db->opendb();

$auth = new userAuth();
$master = new masterInfo();

$auth->sec_session_start();

if ($auth->login_check($conn) == FALSE) {
    header("location: ../login");
    exit();
}

if (!filter_has_var(INPUT_GET, 'month') || filter_input(INPUT_GET, "month", FILTER_VALIDATE_INT) === false) {
    header("Location: ../index.php");
    exit();
}

$user_data = $_SESSION['user_data'];
$unit_cd = $user_data['unit_code'];
//$emp_cd = $user_data['emp_cd']; // not needed for report generation
//$sess_pohash = $_SESSION['pohash'];

require_once '../class/pdf/tcpdf_include.php';

class MYPDF extends TCPDF {}

$pdf = new MYPDF("L", PDF_UNIT, array(400, 400), true, 'UTF-8', false);

$pdf->SetCreator(PDF_CREATOR);
$pdf->SetAuthor('Attendance Register');
$pdf->SetTitle('Attendance Register');
$pdf->SetSubject('Attendance Register');
$pdf->SetKeywords('TAX, INVOICE, Attendance Register');

$pdf->setPrintHeader(true);
$pdf->setPrintFooter(true);

$pdf->SetDefaultMonospacedFont(PDF_FONT_MONOSPACED);
$pdf->SetMargins(5, 5, 5);
$pdf->SetAutoPageBreak(TRUE, PDF_MARGIN_BOTTOM);
$pdf->setImageScale(PDF_IMAGE_SCALE_RATIO);

if (@file_exists(dirname(__FILE__) . '/lang/eng.php')) {
    require_once dirname(__FILE__) . '/lang/eng.php';
    $pdf->setLanguageArray($l);
}

$pdf->SetFont('helvetica', '', 9);
$pdf->AddPage('L', 'A4');

$html = "";

$month = filter_input(INPUT_GET, "month", FILTER_VALIDATE_INT);
$year = filter_input(INPUT_GET, "year", FILTER_VALIDATE_INT);
$level = filter_input(INPUT_GET, "level", FILTER_SANITIZE_STRING);

if ($month < 10) {
    $month = '0' . $month;
}

$days = cal_days_in_month(CAL_GREGORIAN, $month, $year);
$s_date = date('Y-m-d', strtotime($year . '-' . $month . '-01'));

// --- Fetch employee info with optional level filter ---
$sqlEmp = "SELECT EMP_NUMBER as emp_cd, NAME, PHOTO_FILE FROM emp_master WHERE UNIT = ? AND ACTIVE_STATUS = 'Y'";
$params = [$unit_cd];
$types = "i";

if (!empty($level)) {
    $sqlEmp .= " AND LEVEL = ?";
    $params[] = $level;
    $types .= "s";
}

$stmtEmp = $conn->prepare($sqlEmp);
if ($stmtEmp === false) {
    die("Prepare failed for employees: " . $conn->error);
}
$stmtEmp->bind_param($types, ...$params);
$stmtEmp->execute();
$resEmp = $stmtEmp->get_result();

$employees = [];
while ($row = $resEmp->fetch_assoc()) {
    $employees[$row['emp_cd']] = $row;
}

if (empty($employees)) {
    die("No employees found for the given criteria.");
}

// --- Fetch holidays for the month ---
$stmtHol = $conn->prepare("SELECT holiday, holiday_type FROM hr_holiday_mast WHERE YEAR(holiday) = ? AND MONTH(holiday) = ?");
if ($stmtHol === false) {
    die("Prepare failed for holidays: " . $conn->error);
}
$stmtHol->bind_param("ii", $year, $month);
$stmtHol->execute();
$resHol = $stmtHol->get_result();

$holidays = [];
while ($row = $resHol->fetch_assoc()) {
    $holidays[$row['holiday']] = $row['holiday_type'];
}

// --- Fetch attendance punches for all employees in month in one go ---

$unit_cd = (string) $unit_cd; // Assuming unit_cd is a string (if integer, change type accordingly)
$level = !empty($level) ? (string) $level : null;

// Prepare the query with condition for level
$query = "
WITH shift_punches AS (
    SELECT 
        COALESCE(sp.shift, em.SHIFT) AS shift_code,
        t.punchingcode,
        CASE 
            WHEN t.datetime < CONCAT(DATE(t.datetime), ' ', s.punch_start_time)
            THEN DATE_SUB(DATE(t.datetime), INTERVAL 1 DAY)
            ELSE DATE(t.datetime)
        END AS shift_date,
        t.datetime,
        em.EMP_NUMBER AS emp_cd,
        t.unit_cd
    FROM tblt_timesheet t 
    LEFT JOIN emp_master em ON t.punchingcode = em.CARD_NO  
    LEFT JOIN shift_plan sp ON sp.emp_cd = em.EMP_NUMBER AND t.datetime BETWEEN sp.from_dt AND sp.to_dt AND sp.unit_cd = t.unit_cd 
    LEFT JOIN shift_master s ON s.shift_code = COALESCE(sp.shift, em.SHIFT) AND s.unit_cd = t.unit_cd 
    WHERE MONTH(t.date) = ? 
      AND YEAR(t.date) = ? 
      AND t.unit_cd = ?
      " . (!empty($level) ? "AND em.LEVEL = ?" : "") . "
), 
punch_counts AS (
    SELECT 
        sp.emp_cd,
        sp.shift_code,
        sp.shift_date,
        COUNT(sp.punchingcode) AS total_punches,
        MIN(sp.datetime) AS in_datetime,
        CASE WHEN COUNT(sp.punchingcode) % 2 = 0 THEN MAX(sp.datetime) ELSE NULL END AS out_datetime
    FROM shift_punches sp
    GROUP BY sp.emp_cd, sp.shift_code, sp.shift_date
)
SELECT 
    pc.emp_cd,
    pc.shift_date,
    pc.in_datetime,
    pc.out_datetime,
    pc.total_punches,
    sm.start_time,
    sm.end_time,
    sm.type,
    IFNULL(TIMESTAMPDIFF(MINUTE, pc.in_datetime, pc.out_datetime), 0)/60 AS work_hours,
    CASE 
        WHEN sm.type != 'O' THEN
            IF( TIME(sm.start_time) > TIME(sm.end_time), 
                (TIMESTAMPDIFF(MINUTE, CONCAT(pc.shift_date, ' ', sm.start_time), CONCAT(DATE_ADD(pc.shift_date, INTERVAL 1 DAY), ' ', sm.end_time))/60),
                (TIMESTAMPDIFF(MINUTE, CONCAT(pc.shift_date, ' ', sm.start_time), CONCAT(pc.shift_date, ' ', sm.end_time))/60)
            )
        ELSE
            (SELECT ((TIME_TO_SEC(time)/60)/60) FROM open_shift_break os WHERE os.shift = sm.shift_code AND os.unit_cd = sm.unit_cd AND os.pay_per = 1 LIMIT 1)
    END AS shift_hours
FROM punch_counts pc
LEFT JOIN shift_master sm ON pc.shift_code = sm.shift_code AND sm.unit_cd = ?
ORDER BY pc.emp_cd, pc.shift_date
";

$stmtPunches = $conn->prepare($query);
if ($stmtPunches === false) {
    die("Prepare failed for punches: " . $conn->error);
}

// Bind params based on whether $level is set
if (!empty($level)) {
    // Types: i = month, i = year, s = unit_cd, s = level, s = unit_cd again (for LEFT JOIN)
    $stmtPunches->bind_param("issis", $month, $year, $unit_cd, $level, $unit_cd);
} else {
    // Types: i = month, i = year, s = unit_cd, s = unit_cd again
    $stmtPunches->bind_param("iiss", $month, $year, $unit_cd, $unit_cd);
}

$stmtPunches->execute();
$resPunches = $stmtPunches->get_result();

$punches = [];
while ($row = $resPunches->fetch_assoc()) {
    $emp_cd = $row['emp_cd'];
    $dt = $row['shift_date'];
    $punches[$emp_cd][$dt] = $row;
}

// --- Prepare day iteration ---
$daysInMonth = cal_days_in_month(CAL_GREGORIAN, $month, $year);
$firstDayObj = new DateTime("$year-$month-01");

$finalData = [];
$sn = 1;

foreach ($employees as $emp_cd => $empInfo) {
    $finalData[$emp_cd] = [
        'info' => $empInfo,
        'days' => [],
        'summary' => [
            'P' => 0,
            'A' => 0,
            'S' => 0,
            'H' => 0,
            'OT' => 0,
        ],
    ];

    $currentDateObj = clone $firstDayObj;

    for ($d = 1; $d <= $daysInMonth; $d++) {
        $dateStr = $currentDateObj->format('Y-m-d');
        $dayOfWeek = (int) $currentDateObj->format('w');  // 0=Sun,...6=Sat

        $holidayType = $holidays[$dateStr] ?? '';

        if ($holidayType) {
            $status = substr($holidayType, 0, 1); // E.g. H or F
            $finalData[$emp_cd]['summary']['H']++;

            $in_time = '';
            $out_time = '';
            $ot = 0;
        } else {
            $punch = $punches[$emp_cd][$dateStr] ?? null;

            if ($punch) {
                $status = 'P';
                $finalData[$emp_cd]['summary']['P']++;

                $in_time = date('h:i A', strtotime($punch['in_datetime']));
                $out_time = !empty($punch['out_datetime']) ? date('h:i A', strtotime($punch['out_datetime'])) : '';

                $otCalc = $punch['work_hours'] - $punch['shift_hours'];
                $ot = $otCalc > 0 ? round($otCalc, 1) : 0;
                $finalData[$emp_cd]['summary']['OT'] += $ot;

            } else {
                if ($dayOfWeek === 0) {  // Sunday
                    $status = 'S';
                    $finalData[$emp_cd]['summary']['S']++;

                    // Count Sunday also as Present if your logic requires
                    // $finalData[$emp_cd]['summary']['P']++;
                    $in_time = '';
                    $out_time = '';
                    $ot = 0;
                } else {
                    $status = 'A';
                    $finalData[$emp_cd]['summary']['A']++;

                    $in_time = '';
                    $out_time = '';
                    $ot = 0;
                }
            }
        }

        $finalData[$emp_cd]['days'][$dateStr] = compact('status', 'in_time', 'out_time', 'ot', 'holidayType');

        $currentDateObj->modify('+1 day');
    }
}

// --- Generate HTML for report ---

$html .= '<table width="100%" border="1" cellpadding="2" nobr="true">';
$html .= '<tr>';
$html .= '<td style="text-align:center;" colspan="100%"><b>Attendance Register For ' . date('M', strtotime($year . '-' . $month . '-01')) . ' - ' . $year . '</b></td>';
$html .= '</tr>';

$html .= '<tr style="text-align:center;">';
$html .= '<td width="3%"><b>SrNo</b></td>';
$html .= '<td width="6%"><b>Pic.</b></td>';
$html .= '<td width="12.8%"><b>Employee Info</b></td>';

for ($i = 1; $i <= 31; $i++) {
    $html .= '<td style="text-align:center;" width="2.2%">' . $i . '</td>';
}

$html .= '<td style="text-align:center;" width="2%">S</td>';
$html .= '<td style="text-align:center;" width="2%">OT</td>';
$html .= '<td style="text-align:center;" width="2%">P</td>';
$html .= '<td style="text-align:center;" width="2%">TOTAL</td>';
$html .= '<td style="text-align:center;" width="2%">A</td>';
$html .= "</tr>";

$html .= '<tr>';
$html .= '<td></td><td></td><td></td>';
$currentDateObj = new DateTime("$year-$month-01");
for ($i = 1; $i <= 31; $i++) {
    if ($i <= $daysInMonth) {
        $weekDayShort = $currentDateObj->format('D');
        $html .= '<td style="font-size:10px; text-align:center" width="2.2%">' . $weekDayShort . '</td>';
        $currentDateObj->modify('+1 day');
    } else {
        $html .= '<td style="font-size:10px; text-align:center" width="2.2%"></td>';
    }
}

$html .= '<td></td><td></td><td></td><td></td><td></td>';
$html .= '</tr>';

// Rows per employee, 4 rows per employee

foreach ($finalData as $emp_cd => $empData) {
    $info = $empData['info'];
    $days = $empData['days'];
    $summary = $empData['summary'];

    $html .= '<tr nobr="true">';
    $html .= '<td rowspan="4">' . $sn . '</td>';

    if (!empty($info['PHOTO_FILE']) && file_exists('../hrm/img/profile/' . $info['PHOTO_FILE'])) {
        $html .= '<td rowspan="4"><img src="' . PROJECT_ROOT . '/hrm/img/profile/' . $info['PHOTO_FILE'] . '" width="55" height="120"></td>';
    } else {
        $html .= '<td rowspan="4"></td>';
    }

    $html .= '<td rowspan="4">' . htmlspecialchars($emp_cd) . ' - ' . htmlspecialchars($info['NAME']) . '</td>';

    // Attendance symbols row
    $countDays = 1;
    $currentDateObj = new DateTime("$year-$month-01");
    while ($countDays <= 31) {
        if ($countDays <= $daysInMonth) {
            $dateStr = $currentDateObj->format('Y-m-d');
            $dayData = $days[$dateStr] ?? ['status' => '', 'holidayType' => ''];

            $status = $dayData['status'];
            $style = '';
            if ($status === 'P') {
                $style = 'background-color: rgb(148, 255, 102); text-align:center;';
            } elseif ($status === 'A') {
                $style = 'background-color: rgb(255, 112, 102); text-align:center;';
            } elseif ($status === 'S') {
                $style = 'background-color: rgb(255,255,102); text-align:center;';
            } elseif (!empty($dayData['holidayType'])) {
                $style = 'background-color: rgb(102, 245, 255); text-align:center;';
            }
            $html .= '<td style="' . $style . '">' . htmlspecialchars($status) . '</td>';
            $currentDateObj->modify('+1 day');
        } else {
            $html .= '<td></td>';
        }
        $countDays++;
    }

    $html .= '<td></td><td></td><td></td><td></td><td></td></tr>';

    // Overtime row
    $countDays = 1;
    $html .= '<tr>';
    $currentDateObj = new DateTime("$year-$month-01");
    while ($countDays <= 31) {
        if ($countDays <= $daysInMonth) {
            $dateStr = $currentDateObj->format('Y-m-d');
            $dayData = $days[$dateStr] ?? ['ot' => 0];
            $html .= '<td style="text-align:center;">' . ($dayData['ot'] ? $dayData['ot'] : '') . '</td>';
            $currentDateObj->modify('+1 day');
        } else {
            $html .= '<td></td>';
        }
        $countDays++;
    }

    $html .= '<td style="text-align:center">' . $summary['S'] . '</td>';
    $html .= '<td style="text-align:center">' . number_format($summary['OT'], 1) . '</td>';
    $html .= '<td style="text-align:center">' . $summary['P'] . '</td>';
    $html .= '<td style="text-align:center">' . ($summary['P'] + $summary['S']) . '</td>';
    $html .= '<td style="text-align:center">' . $summary['A'] . '</td>';
    $html .= '</tr>';

    // In time row
    $countDays = 1;
    $html .= '<tr>';
    $currentDateObj = new DateTime("$year-$month-01");
    while ($countDays <= 31) {
        if ($countDays <= $daysInMonth) {
            $dateStr = $currentDateObj->format('Y-m-d');
            $dayData = $days[$dateStr] ?? ['in_time' => ''];
            $html .= '<td style="text-align:center;">' . htmlspecialchars($dayData['in_time']) . '</td>';
            $currentDateObj->modify('+1 day');
        } else {
            $html .= '<td></td>';
        }
        $countDays++;
    }
    $html .= '<td></td><td></td><td></td><td></td><td></td></tr>';

    // Out time row
    $countDays = 1;
    $html .= '<tr>';
    $currentDateObj = new DateTime("$year-$month-01");
    while ($countDays <= 31) {
        if ($countDays <= $daysInMonth) {
            $dateStr = $currentDateObj->format('Y-m-d');
            $dayData = $days[$dateStr] ?? ['out_time' => ''];
            $html .= '<td style="text-align:center;">' . htmlspecialchars($dayData['out_time']) . '</td>';
            $currentDateObj->modify('+1 day');
        } else {
            $html .= '<td></td>';
        }
        $countDays++;
    }
    $html .= '<td></td><td></td><td></td><td></td><td></td></tr>';

    $sn++;
}

$html .= '<tr>';
$html .= '<td colspan="2" style="text-align:center">&nbsp;Note:-</td>';
$html .= '<td colspan="37" style="text-align:center"><b> A-Absent | P-Present | S-Sunday | H-Holiday | F-Festival</b></td>';
$html .= '</tr>';
$html .= '</table>';

// --- Output PDF ---
$pdf->writeHTML($html, true, false, true, false, '');
ob_end_clean();
$pdf->SetDisplayMode('real', 'default');
$pdf->Output('Attendance Register - ' . date("Y-m-d h:i:s") . '.pdf', "I");

?>

convert this code into php spreadsheet code