📄 File: preview_import.php

Path: /home2/outerorb/emp.outerorbittech.in/scripts/preview_import.php

Size: 4.29 KB

Permissions: 0666

<?php
require __DIR__ . '/../includes/helpers.php';
require __DIR__ . '/../vendor/autoload.php';
$file = __DIR__ . '/../uploads/att/18-MAR-2026.xls';
$pdo = db();
try {
    $ss = \PhpOffice\PhpSpreadsheet\IOFactory::load($file);
    $sheet = $ss->getActiveSheet();
    $rows = array_values($sheet->toArray(null, true, true, true));

    $normalize = function($v){
        $s = trim((string)$v);
        $s = preg_replace('/[\x00-\x1F\x7F\xA0]/u', ' ', $s); // remove control/nbsp
        $k = strtolower(trim(preg_replace('/[^a-z0-9_ ]+/', '', $s)));
        return str_replace(' ', '_', $k);
    };

    $headerIndex = null; $map = [];
    $scan = min(80, count($rows));
    for ($i=0;$i<$scan;$i++){
        $r = array_values($rows[$i]);
        $norms = array_map($normalize, $r);
        if (in_array('card_no', $norms, true) || in_array('card', $norms, true) || in_array('cardnumber', $norms, true)) { $headerIndex = $i; $headerRow = $r; break; }
        if ((in_array('s_no',$norms,true) || in_array('sno',$norms,true)) && (in_array('emp_code',$norms,true) || in_array('emp_name',$norms,true) || in_array('empname',$norms,true))) { $headerIndex = $i; $headerRow = $r; break; }
    }
    // Fallback: look for literal 'card' text in any cell (handles odd chars/encodings)
    if ($headerIndex === null) {
        for ($i=0;$i<$scan;$i++){
            $r = array_values($rows[$i]);
            foreach ($r as $cell) {
                if ($cell !== null && mb_stripos((string)$cell, 'card') !== false) { $headerIndex = $i; $headerRow = $r; break 2; }
            }
        }
    }
    if ($headerIndex === null) { $headerIndex = 0; $headerRow = array_values($rows[0]); }
    foreach (array_values($headerRow) as $i=>$h) { $map[$normalize($h)] = $i; }

    // detect card/in/out/date columns by literal header text (robust to odd chars)
    $cardIdx = null; $inIdx = null; $outIdx = null; $dateIdx = null;
    foreach (array_values($headerRow) as $i=>$h) {
        $t = (string)$h;
        if ($t === null) continue;
        $tl = mb_strtolower($t);
        if ($cardIdx === null && mb_stripos($t, 'card') !== false) $cardIdx = $i;
        if ($inIdx === null && (mb_stripos($t, 'in') !== false || mb_stripos($t, 'in time') !== false || mb_stripos($t, 'in_time') !== false)) $inIdx = $i;
        if ($outIdx === null && (mb_stripos($t, 'out') !== false || mb_stripos($t, 'out time') !== false || mb_stripos($t, 'out_time') !== false)) $outIdx = $i;
        if ($dateIdx === null && mb_stripos($t, 'date') !== false && mb_stripos($t,'time') === false) $dateIdx = $i;
    }
    // fallback: if card not found, consider columns with 'emp' or 'code'
    if ($cardIdx === null) {
        foreach (array_values($headerRow) as $i=>$h) {
            if (mb_stripos((string)$h,'emp') !== false || mb_stripos((string)$h,'code') !== false) { $cardIdx = $i; break; }
        }
    }

    $preview = ['header_row'=> $headerIndex+1, 'map'=>array_keys($map), 'card_column_index'=>$cardIdx, 'in_index'=>$inIdx, 'out_index'=>$outIdx, 'date_index'=>$dateIdx, 'rows'=>[]];

    for ($r = $headerIndex+1; $r < count($rows) && count($preview['rows']) < 60; $r++){
        $vals = array_values($rows[$r]);
        $raw = $vals[$cardIdx] ?? null;
        $card = trim((string)$raw);
        $card = preg_replace('/[\x00-\x1F\x7F\xA0]/u','',$card);
        if ($card === '' && $raw !== null && is_numeric($raw)) $card = (string)$raw;
        if (strpos($card,'.')!==false) $card = preg_replace('/\.0+$/','',$card);
        $card = preg_replace('/[^0-9A-Za-z]/','',$card);
        $in = $inIdx !== null ? trim($vals[$inIdx] ?? '') : null;
        $out = $outIdx !== null ? trim($vals[$outIdx] ?? '') : null;
        $date = $dateIdx !== null ? trim($vals[$dateIdx] ?? '') : null;
        $emp = null;
        if ($card !== '') {
            $stmt = $pdo->prepare('SELECT id, first_name, last_name FROM employees WHERE card_number = ? LIMIT 1');
            $stmt->execute([$card]);
            $emp = $stmt->fetch();
        }
        $preview['rows'][] = ['row'=>$r+1,'raw_card'=>$raw,'card'=>$card,'in'=>$in,'out'=>$out,'date'=>$date,'matched_employee'=>$emp ?: null];
    }

    echo json_encode($preview, JSON_UNESCAPED_UNICODE|JSON_UNESCAPED_SLASHES|JSON_PRETTY_PRINT);
} catch (Throwable $e){ echo 'ERROR: '.$e->getMessage(); }

← Back to Directory Edit File 🔒 Chmod