<?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'];
$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'));
$e_date = date('Y-m-t', strtotime($year . '-' . $month . '-01')); // Last day of month

$search = "";
if (!empty($level)) {
    $search = " AND em.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 = ?
      $search
), 
punch_counts AS (
    SELECT 
        sp.punchingcode, 
        sp.shift_code, 
        sp.shift_date, 
        sp.emp_cd, 
        sp.unit_cd, 
        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.punchingcode, sp.shift_code, sp.shift_date
)
SELECT 
    pc.punchingcode, 
    pc.shift_code, 
    pc.emp_cd, 
    pc.unit_cd
FROM punch_counts pc 
LEFT JOIN shift_master sm ON pc.shift_code = sm.shift_code AND sm.unit_cd = pc.unit_cd 
LEFT JOIN emp_master em ON pc.emp_cd = em.EMP_NUMBER 
LEFT JOIN hrm_day_timing hdt1 ON 
    hdt1.shift = pc.shift_code 
    AND hdt1.unit_cd = pc.unit_cd 
    AND TIME(pc.in_datetime) BETWEEN hdt1.from_time AND hdt1.to_time 
    AND hdt1.from_time = (
        SELECT hdt2.from_time 
        FROM hrm_day_timing hdt2 
        WHERE hdt2.shift = pc.shift_code 
          AND hdt2.unit_cd = pc.unit_cd 
          AND TIME(pc.in_datetime) BETWEEN hdt2.from_time AND hdt2.to_time 
        ORDER BY ABS(TIMESTAMPDIFF(SECOND, pc.in_datetime, CONCAT(pc.shift_date, ' ', hdt2.from_time))) 
        ASC LIMIT 1
    ) 
LEFT JOIN hrm_day_timing hdt3 ON 
    hdt3.shift = pc.shift_code 
    AND hdt3.unit_cd = pc.unit_cd 
    AND TIME(pc.out_datetime) BETWEEN hdt3.from_time AND hdt3.to_time 
    AND hdt3.from_time = (
        SELECT hdt4.from_time 
        FROM hrm_day_timing hdt4 
        WHERE hdt4.shift = pc.shift_code 
          AND hdt4.unit_cd = pc.unit_cd 
          AND TIME(pc.out_datetime) BETWEEN hdt4.from_time AND hdt4.to_time 
        ORDER BY ABS(TIMESTAMPDIFF(SECOND, pc.out_datetime, CONCAT(pc.shift_date, ' ', hdt4.from_time))) 
        ASC LIMIT 1
    )
WHERE 1=1 and pc.emp_cd is not null
ORDER BY emp_cd asc;
";

$stmt = $conn->prepare($query);
if ($stmt === false) {
    die("MySQL prepare failed: " . $conn->error);
}

if (!empty($level)) {
    $stmt->bind_param("iiss", $month, $year, $unit_cd, $level);
} else {
    $stmt->bind_param("iii", $month, $year, $unit_cd);
}

$stmt->execute();
$result = $stmt->get_result();

if ($result->num_rows < 1) {
    // No data found, handle as needed
}

// Fetch all necessary data in one go
$attendanceData = [];
while ($row = $result->fetch_assoc()) {
    $attendanceData[$row['emp_cd']] = $row;
}

function holiday_return($conn, $date)
{
    $st = $conn->prepare("SELECT * FROM hr_holiday_mast WHERE holiday=?");
    $st->bind_param("s", $date);
    $st->execute();
    $res1 = $st->get_result();
    if ($res1->num_rows > 0) {
        $row = $res1->fetch_assoc();
        return $row['holiday_type'];
    } else {
        return '';
    }
}

function over_time($conn, $emp_cd, $date, $unit_cd)
{
    $st = $conn->prepare("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 1=1  AND t.unit_cd=? and em.EMP_NUMBER = ?
), punch_counts AS (
    SELECT 
        sp.punchingcode,
        sp.shift_code,
        sp.shift_date, 
        sp.emp_cd,
        sp.unit_cd,
        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 
    WHERE sp.shift_date = ?
    GROUP BY sp.punchingcode, sp.shift_code, sp.shift_date
)
SELECT 
    pc.punchingcode,
    pc.shift_code,
    sm.name as shift_nm,
    pc.shift_date, 
    pc.total_punches,
    pc.in_datetime,
    pc.out_datetime,
    pc.emp_cd,
    em.NAME as emp_nm,
    (IFNULL(TIMESTAMPDIFF(MINUTE, pc.in_datetime, pc.out_datetime), 0)/60) AS work_time,
    CASE 
        WHEN sm.type != 'O' THEN
            IF( TIME(sm.start_time) > TIME(sm.end_time), 
                (TIMESTAMPDIFF(MINUTE, CONCAT(DATE(pc.shift_date), ' ', sm.start_time), CONCAT(DATE(pc.shift_date) + INTERVAL 1 DAY, ' ', sm.end_time))/60),
                (TIMESTAMPDIFF(MINUTE, CONCAT(DATE(pc.shift_date), ' ', sm.start_time), CONCAT(DATE(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_time
FROM punch_counts pc
LEFT JOIN shift_master sm ON pc.shift_code = sm.shift_code AND sm.unit_cd = pc.unit_cd
LEFT JOIN emp_master em ON pc.emp_cd = em.EMP_NUMBER
LEFT JOIN hrm_day_timing hdt1 ON 
    hdt1.shift = pc.shift_code AND hdt1.unit_cd = pc.unit_cd
    AND TIME(pc.in_datetime) BETWEEN hdt1.from_time AND hdt1.to_time
    AND hdt1.from_time = (
        SELECT hdt2.from_time
        FROM hrm_day_timing hdt2
        WHERE hdt2.shift = pc.shift_code AND hdt2.unit_cd = pc.unit_cd
        AND TIME(pc.in_datetime) BETWEEN hdt2.from_time AND hdt2.to_time
        ORDER BY ABS(TIMESTAMPDIFF(SECOND, pc.in_datetime, CONCAT(pc.shift_date, ' ', hdt2.from_time))) ASC
        LIMIT 1
    )
LEFT JOIN hrm_day_timing hdt3 ON 
    hdt3.shift = pc.shift_code AND hdt3.unit_cd = pc.unit_cd
    AND TIME(pc.out_datetime) BETWEEN hdt3.from_time AND hdt3.to_time
    AND hdt3.from_time = (
        SELECT hdt4.from_time
        FROM hrm_day_timing hdt4
        WHERE hdt4.shift = pc.shift_code AND hdt4.unit_cd = pc.unit_cd
        AND TIME(pc.out_datetime) BETWEEN hdt4.from_time AND hdt4.to_time
        ORDER BY ABS(TIMESTAMPDIFF(SECOND, pc.out_datetime, CONCAT(pc.shift_date, ' ', hdt4.from_time))) ASC
        LIMIT 1
    )
WHERE 1=1");
    $st->bind_param("sss", $unit_cd, $emp_cd, $date);
    $st->execute();
    $res1 = $st->get_result();
    if ($res1->num_rows > 0) {
        $row = $res1->fetch_assoc();
        if (!empty($row['out_datetime'])) {
            $over_time = number_format($row['work_time'] - $row['shift_time'], 1);
            if ($over_time < 0) {
                $over_time = 0;
            }
        } else {
            $over_time = 0;
        }
        return $over_time;
    } else {
        return 0;
    }
}

function in_time($conn, $emp_cd, $date, $unit_cd)
{
    $st = $conn->prepare("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 1=1 AND t.unit_cd=? AND em.EMP_NUMBER = ?
    ),
    punch_counts AS (
    SELECT 
        sp.punchingcode,
        sp.shift_code,
        sp.shift_date, 
        sp.emp_cd,
        sp.unit_cd,
        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 
    WHERE sp.shift_date =?
    GROUP BY sp.punchingcode, sp.shift_code, sp.shift_date
    )
    SELECT 
    pc.punchingcode,
    pc.shift_code,
    sm.name AS shift_nm,
    pc.shift_date, 
    pc.total_punches,
    pc.in_datetime,
    pc.out_datetime,
    pc.emp_cd,
    em.NAME AS emp_nm
    FROM punch_counts pc
    LEFT JOIN shift_master sm ON pc.shift_code = sm.shift_code AND sm.unit_cd = pc.unit_cd
    LEFT JOIN emp_master em ON pc.emp_cd = em.EMP_NUMBER
    LEFT JOIN hrm_day_timing hdt1 ON
        hdt1.shift = pc.shift_code AND hdt1.unit_cd = pc.unit_cd
        AND TIME(pc.in_datetime) BETWEEN hdt1.from_time AND hdt1.to_time
        AND hdt1.from_time = (
            SELECT hdt2.from_time
            FROM hrm_day_timing hdt2
            WHERE hdt2.shift = pc.shift_code AND hdt2.unit_cd = pc.unit_cd
            AND TIME(pc.in_datetime) BETWEEN hdt2.from_time AND hdt2.to_time
            ORDER BY ABS(TIMESTAMPDIFF(SECOND, pc.in_datetime, CONCAT(pc.shift_date, ' ', hdt2.from_time))) ASC
            LIMIT 1
        )
    LEFT JOIN hrm_day_timing hdt3 ON
        hdt3.shift = pc.shift_code AND hdt3.unit_cd = pc.unit_cd
        AND TIME(pc.out_datetime) BETWEEN hdt3.from_time AND hdt3.to_time
        AND hdt3.from_time = (
            SELECT hdt4.from_time
            FROM hrm_day_timing hdt4
            WHERE hdt4.shift = pc.shift_code AND hdt4.unit_cd = pc.unit_cd
            AND TIME(pc.out_datetime) BETWEEN hdt4.from_time AND hdt4.to_time
            ORDER BY ABS(TIMESTAMPDIFF(SECOND, pc.out_datetime, CONCAT(pc.shift_date, ' ', hdt4.from_time))) ASC
            LIMIT 1
        )
    WHERE 1=1");
    $st->bind_param("sss", $unit_cd, $emp_cd, $date);
    $st->execute();
    $res1 = $st->get_result();
    if ($res1->num_rows > 0) {
        $row = $res1->fetch_assoc();
        return date('h:i A', strtotime($row['in_datetime']));
    } else {
        return '';
    }
}

function attendance($conn, $emp_cd, $date, $unit_cd)
{
    $st = $conn->prepare("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 1=1 AND t.unit_cd=? AND em.EMP_NUMBER = ?
    ),
    punch_counts AS (
    SELECT 
        sp.punchingcode,
        sp.shift_code,
        sp.shift_date, 
        sp.emp_cd,
        sp.unit_cd,
        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 
    WHERE sp.shift_date =?
    GROUP BY sp.punchingcode, sp.shift_code, sp.shift_date
    )
    SELECT 
    pc.punchingcode,
    pc.shift_code,
    sm.name AS shift_nm,
    pc.shift_date, 
    pc.total_punches,
    pc.in_datetime,
    pc.out_datetime,
    pc.emp_cd,
    em.NAME AS emp_nm
    FROM punch_counts pc
    LEFT JOIN shift_master sm ON pc.shift_code = sm.shift_code AND sm.unit_cd = pc.unit_cd
    LEFT JOIN emp_master em ON pc.emp_cd = em.EMP_NUMBER
    LEFT JOIN hrm_day_timing hdt1 ON
        hdt1.shift = pc.shift_code AND hdt1.unit_cd = pc.unit_cd
        AND TIME(pc.in_datetime) BETWEEN hdt1.from_time AND hdt1.to_time
        AND hdt1.from_time = (
            SELECT hdt2.from_time
            FROM hrm_day_timing hdt2
            WHERE hdt2.shift = pc.shift_code AND hdt2.unit_cd = pc.unit_cd
            AND TIME(pc.in_datetime) BETWEEN hdt2.from_time AND hdt2.to_time
            ORDER BY ABS(TIMESTAMPDIFF(SECOND, pc.in_datetime, CONCAT(pc.shift_date, ' ', hdt2.from_time))) ASC
            LIMIT 1
        )
    LEFT JOIN hrm_day_timing hdt3 ON
        hdt3.shift = pc.shift_code AND hdt3.unit_cd = pc.unit_cd
        AND TIME(pc.out_datetime) BETWEEN hdt3.from_time AND hdt3.to_time
        AND hdt3.from_time = (
            SELECT hdt4.from_time
            FROM hrm_day_timing hdt4
            WHERE hdt4.shift = pc.shift_code AND hdt4.unit_cd = pc.unit_cd
            AND TIME(pc.out_datetime) BETWEEN hdt4.from_time AND hdt4.to_time
            ORDER BY ABS(TIMESTAMPDIFF(SECOND, pc.out_datetime, CONCAT(pc.shift_date, ' ', hdt4.from_time))) ASC
            LIMIT 1
        )
    WHERE 1=1");
    $st->bind_param("sss", $unit_cd, $emp_cd, $date);
    $st->execute();
    $res1 = $st->get_result();
    if ($res1->num_rows > 0) {
        return 'P';
    } else {
        return 'A';
    }
}

function out_time($conn, $emp_cd, $date, $unit_cd)
{
    $st = $conn->prepare("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 1=1 AND t.unit_cd=? AND em.EMP_NUMBER = ?
    ),
    punch_counts AS (
    SELECT 
        sp.punchingcode,
        sp.shift_code,
        sp.shift_date, 
        sp.emp_cd,
        sp.unit_cd,
        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 
    WHERE sp.shift_date =?
    GROUP BY sp.punchingcode, sp.shift_code, sp.shift_date
    )
    SELECT 
    pc.punchingcode,
    pc.shift_code,
    sm.name AS shift_nm,
    pc.shift_date, 
    pc.total_punches,
    pc.in_datetime,
    pc.out_datetime,
    pc.emp_cd,
    em.NAME AS emp_nm
    FROM punch_counts pc
    LEFT JOIN shift_master sm ON pc.shift_code = sm.shift_code AND sm.unit_cd = pc.unit_cd
    LEFT JOIN emp_master em ON pc.emp_cd = em.EMP_NUMBER
    LEFT JOIN hrm_day_timing hdt1 ON
        hdt1.shift = pc.shift_code AND hdt1.unit_cd = pc.unit_cd
        AND TIME(pc.in_datetime) BETWEEN hdt1.from_time AND hdt1.to_time
        AND hdt1.from_time = (
            SELECT hdt2.from_time
            FROM hrm_day_timing hdt2
            WHERE hdt2.shift = pc.shift_code AND hdt2.unit_cd = pc.unit_cd
            AND TIME(pc.in_datetime) BETWEEN hdt2.from_time AND hdt2.to_time
            ORDER BY ABS(TIMESTAMPDIFF(SECOND, pc.in_datetime, CONCAT(pc.shift_date, ' ', hdt2.from_time))) ASC
            LIMIT 1
        )
    LEFT JOIN hrm_day_timing hdt3 ON
        hdt3.shift = pc.shift_code AND hdt3.unit_cd = pc.unit_cd
        AND TIME(pc.out_datetime) BETWEEN hdt3.from_time AND hdt3.to_time
        AND hdt3.from_time = (
            SELECT hdt4.from_time
            FROM hrm_day_timing hdt4
            WHERE hdt4.shift = pc.shift_code AND hdt4.unit_cd = pc.unit_cd
            AND TIME(pc.out_datetime) BETWEEN hdt4.from_time AND hdt4.to_time
            ORDER BY ABS(TIMESTAMPDIFF(SECOND, pc.out_datetime, CONCAT(pc.shift_date, ' ', hdt4.from_time))) ASC
            LIMIT 1
        )
    WHERE 1=1");
    $st->bind_param("sss", $unit_cd, $emp_cd, $date);
    $st->execute();
    $res1 = $st->get_result();
    if ($res1->num_rows > 0) {
        $row = $res1->fetch_assoc();
        if (!empty($row['out_datetime'])) {
            return date('h:i A', strtotime($row['out_datetime']));
        } else {
            return '';
        }
    } else {
        return '';
    }
}

$html .= '<table width="100%" border="1" nobr="true" 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>';

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

$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>';
$html .= '<td></td>';
$html .= '<td></td>';

$start_date = $s_date;
for ($i = 1; $i <= 31; $i++) {
    if ($i <= $days) {
        $week_day = date('D', strtotime($start_date));
        $html .= '<td style="font-size:10px; text-align:center" width="2.2%">' . $week_day . '</td>';
        $start_date = date("Y-m-d", strtotime("+1 day", strtotime($start_date)));
    } else {
        $html .= '<td style="font-size:10px; text-align:center" width="2.2%"></td>';
    }
}

$html .= '<td style="text-align:center;" width="2%"></td>';
$html .= '<td style="text-align:center;" width="2%"></td>';
$html .= '<td style="text-align:center;" width="2%"></td>';
$html .= '<td style="text-align:center;" width="2%"></td>';
$html .= '<td style="text-align:center;" width="2%"></td>';

$html .= '</tr>';

$sn = 1;

// Process data in PHP
foreach ($attendanceData as $emp_cd => $data) {
    $profile_photo = $master->employeeInfo($emp_cd, $conn)['PHOTO_FILE'];
    $html .= '<tr nobr="true">';
    $html .= '<td rowspan="4">' . $sn . '</td>';

    if (!empty($profile_photo)) {
        $html .= '<td rowspan="4"><img src="' . PROJECT_ROOT . '/hrm/img/profile/' . $profile_photo . '"  width="55px" height="120px"></td>';
    } else {
        $html .= '<td rowspan="4"></td>';
    }

    $html .= '<td rowspan="4">' . $emp_cd . ' - ' . $master->employeeInfo($emp_cd, $conn)['NAME'] . '</td>';

    $start_date = $s_date;
    $tot_pr = 0;
    $tot_sun = 0;
    $tot_ab = 0;
    $tot_cur_sun = 0;
    $attend = '';

    // First row: Attendance marks (P/A/S/H)
    for ($i = 1; $i <= 31; $i++) {
        if ($i <= $days) {
            $holiday = holiday_return($conn, $start_date);
            $day_of_week = date('w', strtotime($start_date));
            $date_attend = attendance($conn, $emp_cd, $start_date, $unit_cd);

            if (empty($holiday)) {
                if ($day_of_week == 0 && $date_attend == 'A') {
                    $attend = 'S';
                    $tot_sun++;
                } else {
                    $attend = $date_attend;
                }

                if ($start_date > date('Y-m-d')) {
                    $attend = '';
                }

                if (!empty($attend)) {
                    if ($day_of_week == 0) {
                        $style = 'background-color: rgb(255,255,102); text-align:center;';
                    } elseif ($attend == 'P') {
                        $style = 'background-color: rgb(148, 255, 102); text-align:center;';
                    } else {
                        $style = 'background-color: rgb(255, 112, 102); text-align:center;';
                    }
                } else {
                    $style = '';
                }

                $html .= '<td style="' . $style . '">' . $attend . '</td>';

                if ($attend == 'P') {
                    $tot_pr++;
                }
                if ($attend == 'S') {
                    $tot_cur_sun++;
                }
                if ($attend == 'A') {
                    $tot_ab++;
                }
            } else {
                $style = 'background-color: rgb(102, 245, 255); text-align:center;';
                $html .= '<td style="' . $style . '">' . substr($holiday, 0, 1) . '</td>';
            }
            $start_date = date("Y-m-d", strtotime("+1 day", strtotime($start_date)));
        } else {
            $html .= '<td></td>';
        }
    }

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

    // Second row: Overtime
    $html .= "<tr>";
    $start_date = $s_date;
    $tot_ot = 0;

    for ($i = 1; $i <= 31; $i++) {
        if ($i <= $days) {
            $over_time = over_time($conn, $emp_cd, $start_date, $unit_cd);
            $html .= '<td style="text-align:center;">' . $over_time . '</td>';
            $tot_ot += $over_time;
            $start_date = date("Y-m-d", strtotime("+1 day", strtotime($start_date)));
        } else {
            $html .= '<td></td>';
        }
    }

    $html .= '<td style="text-align:center">' . $tot_sun . '</td>';
    $html .= '<td style="text-align:center">' . $tot_ot . '</td>';
    $html .= '<td style="text-align:center">' . $tot_pr . '</td>';
    $html .= '<td style="text-align:center">' . ($tot_pr + $tot_cur_sun) . '</td>';
    $html .= '<td style="text-align:center">' . $tot_ab . '</td>';
    $html .= "</tr>";

    // Third row: In Time
    $html .= "<tr>";
    $start_date = $s_date;

    for ($i = 1; $i <= 31; $i++) {
        if ($i <= $days) {
            $in_time = in_time($conn, $emp_cd, $start_date, $unit_cd);
            $html .= '<td style="text-align:center;">' . $in_time . '</td>';
            $start_date = date("Y-m-d", strtotime("+1 day", strtotime($start_date)));
        } else {
            $html .= '<td></td>';
        }
    }

    $html .= '<td style="text-align:center"></td><td style="text-align:center"></td><td style="text-align:center"></td><td style="text-align:center"></td><td style="text-align:center"></td>';
    $html .= "</tr>";

    // Fourth row: Out Time
    $html .= "<tr>";
    $start_date = $s_date;

    for ($i = 1; $i <= 31; $i++) {
        if ($i <= $days) {
            $out_time = out_time($conn, $emp_cd, $start_date, $unit_cd);
            $html .= '<td style="text-align:center;">' . $out_time . '</td>';
            $start_date = date("Y-m-d", strtotime("+1 day", strtotime($start_date)));
        } else {
            $html .= '<td></td>';
        }
    }

    $html .= '<td style="text-align:center"></td><td style="text-align:center"></td><td style="text-align:center"></td><td style="text-align:center"></td><td style="text-align:center"></td>';
    $html .= "</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>';

$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");

?>


this is my code i want to optimize the query