View Code - 20260722

pasaria_entry_import.php | 20260722_120325.php Initial Domain Snapshot

<?php
/* =============================================================================
   File: /erp/pasaria_entry_import.php
   Purpose: Pasaria Entry • Import (CSV/XLSX preview + import OK rows)
   Scope : Page-local only. No global/base edits.
   ========================================================================== */

error_reporting(E_ALL);
ini_set('display_errors', 1);

/* ---------------- A) DB + Auth (page scoped) ---------------- */
if (!isset($pdo) || !($pdo instanceof PDO)) { require __DIR__ . '/core/db.php'; }
require __DIR__ . '/modules/auth/auth.php';
require_login();
$u          = auth_user();
$company_id = (int)($u['company_id'] ?? 0);
$user_id    = (int)($u['id'] ?? 0);
if ($company_id <= 0) { http_response_code(401); exit('Unauthorized'); }

/* ---------------- B) Helpers ---------------- */
function h($s){ return htmlspecialchars((string)$s, ENT_QUOTES, 'UTF-8'); }
function col_exists(PDO $pdo, $tbl, $col){
  $st=$pdo->prepare("SHOW COLUMNS FROM `$tbl` LIKE ?"); $st->execute([$col]);
  return (bool)$st->fetch();
}
function ensure_col(PDO $pdo, $tbl, $col, $ddl){
  if (!col_exists($pdo,$tbl,$col)) { $pdo->exec("ALTER TABLE `$tbl` ADD $ddl"); }
}
function pick_col(array $row, array $cands){
  $keys = array_map(fn($k)=>strtolower(trim($k)), array_keys($row));
  foreach ($cands as $cand) {
    $want = strtolower(trim((string)$cand));
    $i = array_search($want, $keys, true);
    if ($i !== false) return array_keys($row)[$i];
  }
  return null;
}
function parse_date_auto($v){
  if ($v === null || $v === '') return '';
  if (is_numeric($v)) {
    $base = new DateTime('1899-12-30'); // Excel base
    $base->modify('+' . intval($v) . ' days');
    return $base->format('Y-m-d');
  }
  $s = trim((string)$v);
  $s = str_replace(['.','/'], '-', $s);
  $ts = strtotime($s);
  return $ts ? date('Y-m-d', $ts) : '';
}
function int_or_null($v){
  if ($v === null || $v === '') return null;
  $n = (int)preg_replace('/\D+/', '', (string)$v);
  return $n > 0 ? $n : null;
}

/* ---------------- C) Ensure target table + self heal ---------------- */
$pdo->exec("
CREATE TABLE IF NOT EXISTS pasaria_entry (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  company_id BIGINT NOT NULL,
  entry_date DATE NOT NULL,
  quality_name VARCHAR(120) NULL,
  primary_beam VARCHAR(120) NULL,
  primary_beam_no VARCHAR(50) NULL,
  secondary_beam VARCHAR(120) NULL,
  secondary_beam_no VARCHAR(50) NULL,
  pasaria_type VARCHAR(120) NULL,
  machine_no INT NULL,
  machine_quality VARCHAR(50) NULL,
  employee_id BIGINT NULL,
  employee_name VARCHAR(120) NULL,
  remark VARCHAR(255) NULL,
  created_by BIGINT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_pe_company (company_id),
  INDEX idx_pe_date (entry_date),
  INDEX idx_pe_machine (machine_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
");
try {
  // Heal: if old column names exist, rename them
  if (col_exists($pdo,'pasaria_entry','quality') && !col_exists($pdo,'pasaria_entry','quality_name')) {
    $pdo->exec("ALTER TABLE pasaria_entry CHANGE COLUMN quality quality_name VARCHAR(120) NULL");
  }
  if (col_exists($pdo,'pasaria_entry','mc_no') && !col_exists($pdo,'pasaria_entry','machine_no')) {
    $pdo->exec("ALTER TABLE pasaria_entry CHANGE COLUMN mc_no machine_no INT NULL");
  }
  // Ensure required columns (idempotent)
  ensure_col($pdo,'pasaria_entry','quality_name',    "COLUMN `quality_name` VARCHAR(120) NULL AFTER `entry_date`");
  ensure_col($pdo,'pasaria_entry','machine_no',      "COLUMN `machine_no` INT NULL AFTER `pasaria_type`");
  ensure_col($pdo,'pasaria_entry','machine_quality', "COLUMN `machine_quality` VARCHAR(50) NULL AFTER `machine_no`");
  ensure_col($pdo,'pasaria_entry','employee_id',     "COLUMN `employee_id` BIGINT NULL AFTER `machine_quality`");
  ensure_col($pdo,'pasaria_entry','employee_name',   "COLUMN `employee_name` VARCHAR(120) NULL AFTER `employee_id`");
  ensure_col($pdo,'pasaria_entry','remark',          "COLUMN `remark` VARCHAR(255) NULL AFTER `employee_name`");
} catch (Throwable $e) { /* ignore */ }

/* ---------------- D) Dept 31 employees (Pasaria) ---------------- */
function load_pasaria_employees(PDO $pdo, int $company_id): array {
  // Prefer company_employee_master if present; else fallback to employees (dept_id=31 or dept name 'pasaria')
  try {
    $q = $pdo->prepare("SHOW TABLES LIKE 'company_employee_master'");
    $q->execute();
    if ($q->fetchColumn()) {
      $st = $pdo->prepare("SELECT id, name FROM company_employee_master
                           WHERE company_id=? AND department_id=31 AND is_active=1
                           ORDER BY name");
      $st->execute([$company_id]);
      $rows = $st->fetchAll(PDO::FETCH_ASSOC);
      if ($rows) return $rows;
    }
  } catch (Throwable $e) {}
  try {
    $st = $pdo->prepare("
      SELECT e.id, e.name
        FROM employees e
   LEFT JOIN departments d ON d.id=e.department_id
       WHERE e.company_id=? AND (
               e.department_id=31
            OR LOWER(d.name)='pasaria'
            OR LOWER(d.name)='pasariya'
       )
    ORDER BY e.name");
    $st->execute([$company_id]);
    return $st->fetchAll(PDO::FETCH_ASSOC);
  } catch (Throwable $e) { return []; }
}
$EMP = load_pasaria_employees($pdo,$company_id);

/* ---------------- E) Template download (handle BEFORE any output) --------- */
$want = $_GET['download'] ?? '';
if ($want === 'csv') {
  header('Content-Type: text/csv');
  header('Content-Disposition: attachment; filename="pasaria_import_template.csv"');
  $out = fopen('php://output', 'w');
  fputcsv($out, [
    'Date','Quality',
    'Primary Beam','Primary Beam No',
    'Secondary Beam','Secondary Beam No',
    'Pasaria Type','Machine No','Machine Quality',
    'Employee name','Employee id','Remark'
  ]);
  fputcsv($out, ['2025-07-01','GANGAPATTU','Red Patta','2130','Chiku','1077','Without Jog','13','GP','Prakash','34','']);
  fclose($out);
  exit;
}
if ($want === 'xlsx') {
  $hasSS = class_exists('\PhpOffice\PhpSpreadsheet\Spreadsheet');
  if ($hasSS) {
    $xl = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
    $sh = $xl->getActiveSheet();
    $sh->fromArray([[
      'Date','Quality',
      'Primary Beam','Primary Beam No',
      'Secondary Beam','Secondary Beam No',
      'Pasaria Type','Machine No','Machine Quality',
      'Employee name','Employee id','Remark'
    ]], NULL, 'A1');
    $sh->fromArray([[
      '2025-07-01','GANGAPATTU','Red Patta','2130','Chiku','1077','Without Jog','13','GP','Prakash','34',''
    ]], NULL, 'A2');
    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header('Content-Disposition: attachment; filename="pasaria_import_template.xlsx"');
    $w = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($xl, 'Xlsx');
    $w->save('php://output');
    exit;
  } else {
    header('Content-Type: text/csv');
    header('Content-Disposition: attachment; filename="pasaria_import_template.xlsx.csv"');
    $out = fopen('php://output','w');
    fputcsv($out, [
      'Date','Quality',
      'Primary Beam','Primary Beam No',
      'Secondary Beam','Secondary Beam No',
      'Pasaria Type','Machine No','Machine Quality',
      'Employee name','Employee id','Remark'
    ]);
    fputcsv($out, ['2025-07-01','GANGAPATTU','Red Patta','2130','Chiku','1077','Without Jog','13','GP','Prakash','34','']);
    fclose($out);
    exit;
  }
}

/* ---------------- F) File reader (CSV/XLSX) ---------------- */
function read_uploaded_rows(array $FILE): array {
  if (!isset($FILE['tmp_name']) || !is_uploaded_file($FILE['tmp_name'])) {
    throw new Exception('Could not read the file.');
  }
  $tmp = $FILE['tmp_name'];
  $name = $FILE['name'] ?? '';
  $ext = strtolower(pathinfo($name, PATHINFO_EXTENSION));

if (in_array($ext, ['xlsx','xls'], true)) {
    if (!class_exists('\PhpOffice\PhpSpreadsheet\IOFactory')) {
      throw new Exception('PhpSpreadsheet not installed; XLSX preview needs phpoffice/phpspreadsheet. कृपया CSV use करें या लाइब्रेरी install करें।');
    }
    $reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader('Xlsx');
    $reader->setReadDataOnly(true);
    $ss = $reader->load($tmp);
    $ws = $ss->getActiveSheet();
    $arr = $ws->toArray(null, true, true, true);
    if (!$arr) return [];
    $rows=[]; $headers=[];
    foreach ($arr as $ri=>$r){
      $vals = array_values($r);
      $isEmpty = true; foreach($vals as $v){ if($v!==null && $v!==''){ $isEmpty=false; break; } }
      if ($isEmpty) continue;
      if (!$headers){ $headers = array_map(fn($x)=>trim((string)$x), $vals); }
      else { $row=[]; foreach($vals as $i=>$v){ $row[$headers[$i] ?? 'col'.$i] = $v; } $rows[]=$row; }
    }
    return $rows;
  }

// CSV
  $fh = fopen($tmp,'r'); if (!$fh) throw new Exception('Could not read the file.');
  $rows=[]; $headers=[];
  while(($r=fgetcsv($fh))!==false){
    $blank=true; foreach($r as $c){ if($c!==null && $c!==''){ $blank=false; break; } }
    if ($blank) continue;
    if (!$headers){ $headers = array_map(fn($x)=>trim((string)$x), $r); continue; }
    $row=[]; foreach($r as $i=>$v){ $row[$headers[$i] ?? 'col'.$i] = $v; } $rows[]=$row;
  }
  fclose($fh);
  return $rows;
}

/* ---------------- G) Build preview ---------------- */
function normalize_pasaria_rows(array $raw, int $employee_override_id, string $employee_override_name): array {
  $out=[];
  $hdr = $raw[0] ?? [];
  $col_date      = pick_col($hdr, ['date','pissing date','entry date','entry_date']);
  $col_quality   = pick_col($hdr, ['quality','quality_name']);
  $col_pbeam     = pick_col($hdr, ['primary beam','primary_beam']);
  $col_pbeam_no  = pick_col($hdr, ['primary beam no','primary beam no.','primary_beam_no']);
  $col_sbeam     = pick_col($hdr, ['secondary beam','secondary_beam']);
  $col_sbeam_no  = pick_col($hdr, ['secondary beam no','secondary beam no.','secondary_beam_no']);
  $col_pasaria   = pick_col($hdr, ['pasaria type','pasaria_type','pasaria']);
  $col_mach_no   = pick_col($hdr, ['machine_no','machine no','mc no','mc_no','mc']);
  $col_mach_q    = pick_col($hdr, ['machine quality','machine_quality','quality (machine)','machine qual','mq']);
  $col_emp_name  = pick_col($hdr, ['employee name','employee_name']);
  $col_emp_id    = pick_col($hdr, ['employee id','employee_id']);
  $col_remark    = pick_col($hdr, ['remark','remarks','note','notes']);

foreach ($raw as $i=>$r){
    $entry_date     = parse_date_auto($r[$col_date] ?? '');
    $quality_name   = trim((string)($r[$col_quality] ?? ''));
    $pb             = trim((string)($r[$col_pbeam] ?? ''));
    $pbn            = trim((string)($r[$col_pbeam_no] ?? ''));
    $sb             = trim((string)($r[$col_sbeam] ?? ''));
    $sbn            = trim((string)($r[$col_sbeam_no] ?? ''));
    $ptype          = trim((string)($r[$col_pasaria] ?? ''));
    $mno            = int_or_null($r[$col_mach_no] ?? null);
    $mq             = trim((string)($r[$col_mach_q] ?? ''));
    $ename          = trim((string)($r[$col_emp_name] ?? ''));
    $eid            = (int)($r[$col_emp_id] ?? 0);
    $remark         = trim((string)($r[$col_remark] ?? ''));

if ($employee_override_id > 0) { $eid = $employee_override_id; $ename = $employee_override_name; }

$status = 'OK';
    if ($entry_date==='') $status='Invalid date';

$out[] = [
      'rownum'            => $i+2,
      'entry_date'        => $entry_date,
      'quality_name'      => $quality_name,
      'primary_beam'      => $pb,
      'primary_beam_no'   => $pbn,
      'secondary_beam'    => $sb,
      'secondary_beam_no' => $sbn,
      'pasaria_type'      => $ptype,
      'machine_no'        => $mno,
      'machine_quality'   => $mq,
      'employee_name'     => $ename,
      'employee_id'       => $eid ?: null,
      'remark'            => $remark ?: null,
      'status'            => $status,
      'ok'                => ($status==='OK')
    ];
  }
  return $out;
}

/* ---------------- H) Actions ---------------- */
$top_error = '';
$preview_rows = [];
$ok_count = 0; $err_count = 0;
$ok_payload_b64 = '';

$act = $_POST['_action'] ?? '';
$emp_for_all = (int)($_POST['emp_for_all'] ?? 0);
$emp_for_all_name = '';
if ($emp_for_all>0) {
  foreach ($EMP as $e){ if ((int)$e['id']===$emp_for_all){ $emp_for_all_name=$e['name']; break; } }
}

if ($act === 'preview') {
  try{
    $raw = read_uploaded_rows($_FILES['file'] ?? []);
    if (!$raw) throw new Exception('File has no data.');
    $preview_rows = normalize_pasaria_rows($raw, $emp_for_all, $emp_for_all_name);
    foreach ($preview_rows as $r){ if ($r['ok']) $ok_count++; else $err_count++; }
    $ok_rows = array_values(array_filter($preview_rows, fn($r)=>$r['ok']));
    $ok_payload_b64 = base64_encode(json_encode($ok_rows));
  }catch(Throwable $e){
    $top_error = $e->getMessage();
  }
}
if ($act === 'import_ok') {
  $payload = $_POST['ok_payload'] ?? '';
  $rows = $payload ? json_decode(base64_decode($payload), true) : [];
  if (!$rows || !is_array($rows)) {
    $top_error = 'Nothing to import.';
  } else {
    $insCols = ['company_id','entry_date','quality_name','primary_beam','primary_beam_no',
                'secondary_beam','secondary_beam_no','pasaria_type','machine_no',
                'machine_quality','employee_id','employee_name','remark','created_by'];
    $ph = rtrim(str_repeat('?,',count($insCols)),',');
    $sql = "INSERT INTO pasaria_entry (".implode(',',$insCols).") VALUES ($ph)";
    $st  = $pdo->prepare($sql);
    $imported = 0; $failed = 0;
    $pdo->beginTransaction();
    try{
      foreach ($rows as $r){
        $st->execute([
          $company_id,
          $r['entry_date'] ?: null,
          $r['quality_name'] ?: null,
          $r['primary_beam'] ?: null,
          $r['primary_beam_no'] ?: null,
          $r['secondary_beam'] ?: null,
          $r['secondary_beam_no'] ?: null,
          $r['pasaria_type'] ?: null,
          $r['machine_no'] ?? null,
          $r['machine_quality'] ?: null,
          $r['employee_id'] ?? null,
          $r['employee_name'] ?: null,
          $r['remark'] ?: null,
          $user_id
        ]);
        $imported++;
      }
      $pdo->commit();
      $top_error = "Imported: $imported • Failed: $failed";
    } catch (Throwable $e) {
      $pdo->rollBack();
      $top_error = "Imported: $imported • Failed: ".($failed+1)." (".$e->getMessage().")";
    }
  }
}

/* ---------------- I) Optional header include (guarded) ---------------- */
$__header = __DIR__ . '/partials/header.php';
if (is_file($__header)) { include_once $__header; }

/* ---------------- J) UI ---------------- */
$hasSpreadsheet = class_exists('\PhpOffice\PhpSpreadsheet\Spreadsheet');
?>
<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<title>Pasaria Entry Import • Mister Manager</title>
<meta name="viewport" content="width=device-width, initial-scale=1">
<style>
  :root{--primary:#34A853;--bg:#F5FFF7;--text:#202124;--muted:#5F6368;--card:#fff;--shadow:0 8px 20px rgba(0,0,0,.06);--radius:16px;--danger:#b00020;--ok:#1e8e3e}
  body{margin:0;font-family:system-ui,-apple-system,Segoe UI,Roboto,Arial;background:var(--bg);color:var(--text)}
  .wrap{max-width:1050px;margin:18px auto;padding:12px}
  .card{background:var(--card);border-radius:var(--radius);box-shadow:var(--shadow);padding:16px}
  h1{margin:0 0 10px}
  .toolbar{display:flex;gap:8px;flex-wrap:wrap;justify-content:flex-end;margin-bottom:10px}
  .btn{padding:10px 14px;border-radius:12px;border:1px solid #e5e7eb;background:#fff;cursor:pointer;text-decoration:none;color:#111}
  .btn.primary{background:var(--primary);color:#fff;border-color:transparent}
  .msg{margin:10px 0;padding:10px 12px;border-radius:10px}
  .err{background:#fdecea;border:1px solid #f5c2c7;color:#7f1d1d}
  .okmsg{background:#e6f4ea;border:1px solid #cce9d4;color:#065f46}
  .row{display:flex;gap:10px;flex-wrap:wrap;align-items:center}
  select,input[type=file]{padding:10px;border:1px solid #e5e7eb;border-radius:10px}
  table{width:100%;border-collapse:collapse;margin-top:12px;background:#fff;border-radius:12px;overflow:hidden}
  th,td{padding:10px;border-bottom:1px solid #f1f5f9;text-align:left;font-size:13px}
  th{background:#f8fafc;color:#334155}
  .status-ok{color:#16a34a;font-weight:600}
  .status-err{color:#b91c1c;font-weight:600}
</style>
</head>
<body>
<div class="wrap">
  <div class="card">
    <div class="toolbar">
      <a class="btn" href="?download=xlsx">Download Template (xlsx)</a>
      <a class="btn" href="?download=csv">Download Template (.csv)</a>
      <a class="btn" href="/erp/pasaria_entry.php">Go to Pasaria Entry</a>
    </div>

<h1>Pasaria Entry Import</h1>

<?php if(!$hasSpreadsheet): ?>
      <div class="msg err">PhpSpreadsheet not installed; XLSX preview needs <code>phpoffice/phpspreadsheet</code>. आप CSV यूज़ करें या लाइब्रेरी install कर दें।</div>
    <?php endif; ?>

<?php if($top_error): ?>
      <div class="msg err"><?= h($top_error) ?></div>
    <?php endif; ?>

<form method="post" enctype="multipart/form-data" class="row" style="margin-bottom:10px">
      <input type="hidden" name="_action" value="preview">
      <div class="pill" style="font-size:12px;padding:6px 10px;border-radius:999px;background:#eef2ff;color:#3730a3">Use one employee for all rows (Dept 31)</div>
      <select name="emp_for_all">
        <option value="0">— Select —</option>
        <?php foreach($EMP as $e): ?>
          <option value="<?= (int)$e['id'] ?>" <?= ($emp_for_all>0 && $emp_for_all==(int)$e['id']?'selected':'') ?>>
            <?= h($e['name']) ?> (ID <?= (int)$e['id'] ?>)
          </option>
        <?php endforeach; ?>
      </select>
      <input type="file" name="file" required>
      <button class="btn primary" type="submit">Preview</button>
    </form>

<?php if($preview_rows): ?>
      <div>
        Preview — <span class="status-ok">OK: <?= (int)$ok_count ?></span>, <span class="status-err">Errors: <?= (int)$err_count ?></span>
      </div>

<div style="overflow:auto; max-height:55vh">
      <table>
        <thead>
          <tr>
            <th>#(Row)</th>
            <th>entry_date</th>
            <th>quality_name</th>
            <th>primary_beam</th>
            <th>primary_beam_no</th>
            <th>secondary_beam</th>
            <th>secondary_beam_no</th>
            <th>pasaria_type</th>
            <th>machine_no</th>
            <th>machine_quality</th>
            <th>employee_name</th>
            <th>employee_id</th>
            <th>remark</th>
            <th>Status</th>
          </tr>
        </thead>
        <tbody>
          <?php foreach($preview_rows as $r): ?>
            <tr>
              <td><?= (int)$r['rownum'] ?></td>
              <td><?= h($r['entry_date']) ?></td>
              <td><?= h($r['quality_name']) ?></td>
              <td><?= h($r['primary_beam']) ?></td>
              <td><?= h($r['primary_beam_no']) ?></td>
              <td><?= h($r['secondary_beam']) ?></td>
              <td><?= h($r['secondary_beam_no']) ?></td>
              <td><?= h($r['pasaria_type']) ?></td>
              <td><?= h($r['machine_no']) ?></td>
              <td><?= h($r['machine_quality']) ?></td>
              <td><?= h($r['employee_name']) ?></td>
              <td><?= h($r['employee_id']) ?></td>
              <td><?= h($r['remark']) ?></td>
              <td><?= $r['ok'] ? '<span class="status-ok">OK</span>' : '<span class="status-err">'.h($r['status']).'</span>' ?></td>
            </tr>
          <?php endforeach; ?>
        </tbody>
      </table>
      </div>

<form method="post" style="margin-top:10px; display:flex; gap:8px; justify-content:flex-end">
        <input type="hidden" name="_action" value="import_ok">
        <input type="hidden" name="ok_payload" value="<?= h($ok_payload_b64) ?>">
        <button class="btn primary" type="submit">Import OK Rows</button>
      </form>
    <?php endif; ?>
  </div>
</div>
</body>
</html>