📄 File: update_cards_from_biometric.php

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

Size: 7.14 KB

Permissions: 0666

<?php
// Usage: php scripts/update_cards_from_biometric.php [path/to/file.xls] [--apply]
require __DIR__ . '/../includes/helpers.php';
require __DIR__ . '/../vendor/autoload.php';
$pdo = db();
$argvCopy = $argv;
array_shift($argvCopy); // script name
$apply = false;
$path = null;
foreach ($argvCopy as $a) {
    if ($a === '--apply') { $apply = true; continue; }
    if (!$path) $path = $a;
}
if (!$path) {
    $default = __DIR__ . '/../uploads/att/biometric_export.xls';
    if (file_exists($default)) { $path = $default; }
    else {
        // try to find any xls/xlsx in uploads/att
        $files = glob(__DIR__ . '/../uploads/att/*.{xls,xlsx,csv}', GLOB_BRACE);
        if (!empty($files)) { $path = $files[0]; }
    }
}
if (!$path || !file_exists($path)) { echo "No file provided and no default biometric file found.\n"; exit(1); }
echo "Using file: $path\n";

// load spreadsheet (xls/xlsx or csv)
$ext = strtolower(pathinfo($path, PATHINFO_EXTENSION));
$rows = [];
if (in_array($ext, ['xls','xlsx'], true)) {
    $ss = \PhpOffice\PhpSpreadsheet\IOFactory::load($path);
    $sheet = $ss->getActiveSheet();
    $rows = array_values($sheet->toArray(null, true, true, true));
} else {
    if (($fh = fopen($path, 'r')) === false) { echo "Cannot open file\n"; exit(1); }
    while (($r = fgetcsv($fh)) !== false) { $rows[] = $r; }
    fclose($fh);
}
if (empty($rows)) { echo "No rows found in sheet\n"; exit(1); }

// helper normalizers
$normalizeHeader = function($v){ $k = strtolower(trim(preg_replace('/[^a-z0-9_ ]+/', '', (string)$v))); return str_replace(' ', '_', $k); };
$normalizeName = function($v){ $s = strtolower(trim((string)$v)); $s = preg_replace('/[^a-z0-9 ]+/', ' ', $s); $s = preg_replace('/\s+/', ' ', $s); return trim($s); };

// detect header row (similar to preview)
$rowsList = array_values($rows);
$scanLimit = min(120, count($rowsList));
$header = null; $headerRowIndex = null;
for ($i=0;$i<$scanLimit;$i++){
    $r = array_values($rowsList[$i]);
    $norms = array_map($normalizeHeader, $r);
    if (in_array('card_no', $norms, true) || in_array('card', $norms, true) || in_array('cardnumber', $norms, true) || in_array('card_no', $norms, true)) { $header = $r; $headerRowIndex = $i; break; }
    if (in_array('emp_name', $norms, true) || in_array('empname', $norms, true) || in_array('employee_name', $norms, true) || in_array('name', $norms, true)) { $header = $r; $headerRowIndex = $i; break; }
}
if ($header === null) {
    for ($i=0;$i<$scanLimit;$i++){
        $r = array_values($rowsList[$i]);
        foreach ($r as $cell) {
            if ($cell === null) continue;
            if (mb_stripos((string)$cell, 'card') !== false || mb_stripos((string)$cell, 'card no') !== false || mb_stripos((string)$cell, 'name') !== false) { $header = $r; $headerRowIndex = $i; break 2; }
        }
    }
}
if ($header === null) { $header = array_values($rowsList[0]); $headerRowIndex = 0; }

// build raw header array
$rawHeader = array_values($header);
$map = [];
foreach ($rawHeader as $i => $h) { $map[$normalizeHeader($h)] = $i; }

// detect columns
$cardIdx = null; $nameIdx = null;
// literal match first
foreach ($rawHeader as $i => $h) {
    if ($cardIdx === null && mb_stripos((string)$h,'card') !== false) $cardIdx = $i;
    if ($nameIdx === null && mb_stripos((string)$h,'name') !== false) $nameIdx = $i;
}
// normalized fallback
if ($cardIdx === null) {
    $cands = ['card_number','card_no','card','cardnumber','cardno','badge_number','badge_no','badge','card_id','uid'];
    foreach ($cands as $c) { if (isset($map[$c])) { $cardIdx = $map[$c]; break; } }
}
if ($nameIdx === null) {
    $ncs = ['employee_name','emp_name','name','empname','employee'];
    foreach ($ncs as $n) { if (isset($map[$n])) { $nameIdx = $map[$n]; break; } }
}
if ($cardIdx === null || $nameIdx === null) { echo "Could not detect card or name columns. Found headers: " . implode(', ', array_map('strval',$rawHeader)) . "\n"; exit(1); }

// load employees into map by normalized name
$stmt = $pdo->query('SELECT id, first_name, last_name, card_number FROM employees');
$employees = $stmt->fetchAll();
$empByName = [];
foreach ($employees as $e) {
    $full = trim(($e['first_name'] ?? '') . ' ' . ($e['last_name'] ?? ''));
    $n = $normalizeName($full);
    if ($n === '') continue;
    if (!isset($empByName[$n])) $empByName[$n] = [];
    $empByName[$n][] = $e;
}

$actions = [];
$skipped = 0; $matched = 0; $ambiguous = 0; $noMatch = 0;
for ($r = $headerRowIndex+1; $r < count($rowsList); $r++) {
    $vals = array_values($rowsList[$r]);
    $rawCard = $vals[$cardIdx] ?? '';
    $rawName = $vals[$nameIdx] ?? '';
    $card = trim((string)$rawCard);
    if ($card === '' && is_numeric($rawCard)) $card = (string)$rawCard;
    if (strpos($card, '.')!==false) $card = preg_replace('/\.0+$/','',$card);
    $card = preg_replace('/[^0-9A-Za-z]/','', $card);
    $name = $normalizeName($rawName);

    if ($name === '' || $card === '') { $skipped++; continue; }

    // exact match
    if (isset($empByName[$name])) {
        $matches = $empByName[$name];
        if (count($matches) === 1) {
            $e = $matches[0];
            $actions[] = ['row'=>$r+1,'employee_id'=>$e['id'],'employee_name'=>($e['first_name'].' '.$e['last_name']),'old_card'=>$e['card_number'],'new_card'=>$card];
            $matched++;
            continue;
        } else {
            // ambiguous multiple employees with same normalized name
            $ambiguous++; continue;
        }
    }

    // token partial match: split name tokens and try find employee containing all tokens
    $tokens = preg_split('/\s+/', $name);
    $found = null; $foundCount = 0;
    foreach ($empByName as $ename => $elist) {
        $ok = true; foreach ($tokens as $t) { if ($t === '') continue; if (mb_stripos($ename, $t) === false) { $ok = false; break; } }
        if ($ok) { $foundCount += count($elist); $found = $elist; }
        if ($foundCount > 1) break;
    }
    if ($foundCount === 1 && $found !== null) {
        $e = $found[0];
        $actions[] = ['row'=>$r+1,'employee_id'=>$e['id'],'employee_name'=>($e['first_name'].' '.$e['last_name']),'old_card'=>$e['card_number'],'new_card'=>$card];
        $matched++; continue;
    }
    $noMatch++;
}

// show summary and preview
echo "Rows scanned: " . (count($rowsList) - $headerRowIndex - 1) . "\n";
echo "Matched: $matched, Ambiguous: $ambiguous, No match: $noMatch, Skipped: $skipped\n\n";
if (empty($actions)) { echo "No updates to apply.\n"; exit(0); }

echo "Preview of updates: (first 200)\n";
$cnt = 0;
foreach ($actions as $a) {
    printf("Row %d -> employee_id=%s name=%s old_card=%s new_card=%s\n", $a['row'], $a['employee_id'], $a['employee_name'], $a['old_card'] ?? '', $a['new_card']);
    $cnt++; if ($cnt >= 200) break;
}

if (!$apply) { echo "\nDry-run mode. To apply updates run with --apply flag.\n"; exit(0); }

// apply updates
$updated = 0;
foreach ($actions as $a) {
    $stmt = $pdo->prepare('UPDATE employees SET card_number = ? WHERE id = ?');
    $stmt->execute([$a['new_card'], $a['employee_id']]);
    $updated += $stmt->rowCount();
}

echo "Applied updates: $updated\n";
exit(0);

← Back to Directory Edit File 🔒 Chmod