View Code - 20260722

pissing_entry_import.php | 20260722_120325.php Initial Domain Snapshot

<?php
/* =========================================================================
   File: /erp/pissing_entry_import.php
   Purpose: Import pissing_entry (CSV/XLSX) — KHATA REMOVED
   Scope : Page-local only. No global/base edits.
   ========================================================================= */
error_reporting(E_ALL); ini_set('display_errors', 1);

/* ---------- Auth + DB (scoped) ---------- */
require __DIR__ . '/modules/auth/page_acl.php';
$ctx        = page_require_access('pissing_entry_import');
$u          = $ctx['user'];
$company_id = (int)$ctx['company_id'];
$user_id    = (int)($u['id'] ?? 0);
$pdo        = $ctx['pdo'] ?? null;
if (!$pdo) { require __DIR__ . '/core/db.php'; }

/* ---------- PhpSpreadsheet autoload ---------- */
$HAS_XLSX = false;
foreach ([__DIR__.'/vendor/autoload.php', __DIR__.'/../vendor/autoload.php', __DIR__.'/../../vendor/autoload.php'] as $a) {
  if (is_file($a)) { require_once $a; break; }
}
if (class_exists('\\PhpOffice\\PhpSpreadsheet\\IOFactory')) { $HAS_XLSX = true; }

/* ---------- Helpers ---------- */
function h($s){ return htmlspecialchars((string)$s, ENT_QUOTES, 'UTF-8'); }
function table_cols(PDO $pdo, string $t){
  try{ $r=$pdo->query("SHOW COLUMNS FROM `$t`")->fetchAll(PDO::FETCH_ASSOC);
       $m=[]; foreach($r as $c){ $m[strtolower($c['Field'])]=1; } return $m;
  }catch(Throwable $e){ return []; }
}
/* Excel serial → Y-m-d */
function excel_date_to_ymd($n){ $base=new DateTime('1899-12-30'); $base->modify('+'.((int)$n).' days'); return $base->format('Y-m-d'); }
function parse_date_auto($val){
  $val=trim((string)$val); if($val==='') return '';
  if (is_numeric($val) && (int)$val>25000) return excel_date_to_ymd((int)$val);
  $ts=strtotime($val); return $ts ? date('Y-m-d',$ts) : '';
}

/* ---------- EARLY: Template downloads (no output before) ---------- */
if (isset($_GET['download']) && $_GET['download']==='csv') {
  if (ob_get_level()) @ob_end_clean();
  header('Content-Type: text/csv; charset=utf-8');
  header('Content-Disposition: attachment; filename="pissing_template.csv"');
  $out=fopen('php://output','w');
  fprintf($out, chr(0xEF).chr(0xBB).chr(0xBF));
  // KHATA REMOVED
  fputcsv($out, ['pissing date','Machine','pissing type','beam no.','machine no.','Employee name','Employee id','remark']);
  fclose($out); exit;
}
if (isset($_GET['download']) && $_GET['download']==='xlsx') {
  if ($HAS_XLSX) {
    if (ob_get_level()) @ob_end_clean();
    $ss = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
    $sheet = $ss->getActiveSheet();
    $cols = ['pissing date','Machine','pissing type','beam no.','machine no.','Employee name','Employee id','remark']; // KHATA REMOVED
    $c=1; foreach($cols as $h){ $sheet->setCellValueByColumnAndRow($c++,1,$h); }
    $writer=\PhpOffice\PhpSpreadsheet\IOFactory::createWriter($ss,'Xlsx');
    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header('Content-Disposition: attachment; filename="pissing_template.xlsx"');
    $writer->save('php://output'); exit;
  } else {
    // CSV fallback
    if (ob_get_level()) @ob_end_clean();
    header('Content-Type: text/csv; charset=utf-8');
    header('Content-Disposition: attachment; filename="pissing_template.csv"');
    $out=fopen('php://output','w');
    fprintf($out, chr(0xEF).chr(0xBB).chr(0xBF));
    fputcsv($out, ['pissing date','Machine','pissing type','beam no.','machine no.','Employee name','Employee id','remark']);
    fclose($out); exit;
  }
}

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

/* ---------- Employees (Dept 31) ---------- */
$deptEmployees=[];
try{
  $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]); $deptEmployees=$st->fetchAll(PDO::FETCH_ASSOC);
  if (!$deptEmployees) {
    $st=$pdo->prepare("SELECT id,name FROM employees WHERE company_id=? AND department_id=31 ORDER BY name");
    $st->execute([$company_id]); $deptEmployees=$st->fetchAll(PDO::FETCH_ASSOC);
  }
}catch(Throwable $e){}

/* ---------- Detect pissing_entry columns ---------- */
$peCols = table_cols($pdo,'pissing_entry');
$has_employee_id   = isset($peCols['employee_id']);
$has_employee_name = isset($peCols['employee_name']);
$has_remark        = isset($peCols['remark']);
$has_machine       = isset($peCols['machine']);
$has_pissing_type  = isset($peCols['pissing_type']);
$has_beam_no       = isset($peCols['beam_no']);
$has_machine_no    = isset($peCols['machine_no']);
$entry_date_col    = isset($peCols['entry_date']) ? 'entry_date' : (isset($peCols['pissing_date']) ? 'pissing_date' : 'entry_date');

/* ---------- Header mapping (KHATA REMOVED) ---------- */
$aliases = [
  'pissing date'    => ['pissing date','date','entry_date','pissing_date'],
  'machine'         => ['machine','machine type'],
  'pissing type'    => ['pissing type','pissing_type','pissing-type','pissing quality','quality','type'],
  'beam no.'        => ['beam no.','beam','beam_no'],
  'machine no.'     => ['machine no.','machine_no','m/c no.'],
  'employee name'   => ['employee name','employee','emp name'],
  'employee id'     => ['employee id','emp id','employee_id'],
  'remark'          => ['remark','remarks','note']
];
function _canon($s){ return preg_replace('/[^a-z0-9]/','', strtolower(trim((string)$s))); }
function find_col($headers, $want, $aliases){
  $targets = array_map('_canon', $aliases[$want]);
  foreach ($headers as $i=>$h){ if (in_array(_canon($h), $targets, true)) return $i; }
  return -1;
}

/* ---------- Readers ---------- */
function read_csv_rows($tmp){
  $out=[]; $fh=fopen($tmp,'r'); if(!$fh) return [[],[]];
  $headers=[]; $i=0;
  while(($r=fgetcsv($fh, 0, ','))!==false){
    if($i++===0){ $headers=$r; continue; }
    if (!array_filter($r, fn($v)=>trim((string)$v)!=='')) continue;
    $out[]=['row'=>$i, 'data'=> array_combine($headers, array_map('strval',$r))];
  }
  fclose($fh); return [$headers,$out];
}
function read_xlsx_rows($tmp){
  $reader=\PhpOffice\PhpSpreadsheet\IOFactory::createReaderForFile($tmp);
  $reader->setReadDataOnly(true);
  $spread=$reader->load($tmp);
  $sheet=$spread->getActiveSheet();
  $rows=[]; $headers=[]; $i=0;
  foreach ($sheet->toArray(null,true,true,true) as $idx=>$r){
    $vals=array_values($r);
    if($i++===0){ $headers=$vals; continue; }
    if (!array_filter($vals, fn($v)=>trim((string)$v)!=='')) continue;
    $rows[]=['row'=>$idx, 'data'=> array_combine($headers, array_map('strval',$vals))];
  }
  return [$headers,$rows];
}

/* ---------- Preview / Import ---------- */
$rows=[]; $errorTop=''; $okCount=0; $errCount=0;
$doPreview = ($_SERVER['REQUEST_METHOD']==='POST' && ($_POST['_action']??'')==='preview');
$doImport  = ($_SERVER['REQUEST_METHOD']==='POST' && ($_POST['_action']??'')==='import_ok');

$chosenEmp = (int)($_POST['employee_id_fixed'] ?? 0);
$chosenEmpName = '';
foreach($deptEmployees as $e){ if ((int)$e['id']===$chosenEmp){ $chosenEmpName=(string)$e['name']; break; } }

if ($doPreview || $doImport){
  $file = $_FILES['file']['tmp_name'] ?? '';
  $name = $_FILES['file']['name'] ?? '';
  if (!$name || !is_uploaded_file($file)) {
    $errorTop = "Could not read the file.";
  } else {
    $ext = strtolower(pathinfo($name, PATHINFO_EXTENSION));
    try{
      if (in_array($ext,['xlsx','xls'])) {
        if (!$HAS_XLSX) throw new Exception("PhpSpreadsheet not installed; please upload CSV or install phpoffice/phpspreadsheet.");
        [$headers,$raw] = read_xlsx_rows($file);
      } else {
        [$headers,$raw] = read_csv_rows($file);
      }
    } catch(Throwable $e){ $errorTop=$e->getMessage(); $headers=[]; $raw=[]; }

if (!$errorTop && $raw){
      $hMap = [
        'date'    => find_col($headers,'pissing date',$aliases),
        'machine' => find_col($headers,'machine',$aliases),
        'ptype'   => find_col($headers,'pissing type',$aliases),
        'beam'    => find_col($headers,'beam no.',$aliases),
        'mcno'    => find_col($headers,'machine no.',$aliases),
        'empname' => find_col($headers,'employee name',$aliases),
        'empid'   => find_col($headers,'employee id',$aliases),
        'remark'  => find_col($headers,'remark',$aliases)
      ];

foreach ($raw as $row){
        $d = array_values($row['data']); $srcRow=(int)$row['row'];
        $pdate    = parse_date_auto( ($hMap['date']>=0) ? $d[$hMap['date']] : '' );
        $machine  = ($hMap['machine']>=0) ? trim($d[$hMap['machine']]) : '';
        $ptype    = ($hMap['ptype']>=0)   ? trim($d[$hMap['ptype']])   : '';
        $beam_no  = ($hMap['beam']>=0)    ? trim($d[$hMap['beam']])    : '';
        $mc_raw   = ($hMap['mcno']>=0)    ? trim($d[$hMap['mcno']])    : '';
        $mc_no    = ctype_digit((string)$mc_raw) ? (int)$mc_raw : (($mc_raw==='')? null : $mc_raw);
        $remark   = ($hMap['remark']>=0)  ? trim($d[$hMap['remark']])  : '';

$empNameIn= ($hMap['empname']>=0)? trim($d[$hMap['empname']]) : '';
        $empIdIn  = ($hMap['empid']>=0)  ? (int)$d[$hMap['empid']]    : 0;

$emp_id   = $chosenEmp ?: $empIdIn;
        $emp_name = $chosenEmp ? $chosenEmpName : $empNameIn;

$err = '';
        if ($pdate==='') $err='Invalid/empty date';

$rows[] = [
          'src_row'=>$srcRow,
          'pissing_date'=>$pdate,
          'machine'=>$machine,
          'pissing_type'=>$ptype,
          'beam_no'=>$beam_no,
          'machine_no'=>$mc_no,
          'remark'=>$remark,
          'employee_id'=>$emp_id,
          'employee_name'=>$emp_name,
          'status'=> $err ? $err : 'OK'
        ];
        if ($err) $errCount++; else $okCount++;
      }
    }
  }
}

/* ---------- Import OK rows ---------- */
$imported=0; $impErr=null;
if ($doImport && $rows){
  try{
    $pdo->beginTransaction();
    $insCols=['company_id',$entry_date_col]; $place=['?','?']; $extra=[];
    if ($has_machine)      { $insCols[]='machine';      $place[]='?'; $extra[]='machine'; }
    if ($has_pissing_type) { $insCols[]='pissing_type'; $place[]='?'; $extra[]='pissing_type'; }
    if ($has_beam_no)      { $insCols[]='beam_no';      $place[]='?'; $extra[]='beam_no'; }
    if ($has_machine_no)   { $insCols[]='machine_no';   $place[]='?'; $extra[]='machine_no'; }
    // KHATA REMOVED: no khata_id at all
    if ($has_remark)       { $insCols[]='remark';       $place[]='?'; $extra[]='remark'; }
    if ($has_employee_id)  { $insCols[]='employee_id';  $place[]='?'; $extra[]='employee_id'; }
    if ($has_employee_name){ $insCols[]='employee_name';$place[]='?'; $extra[]='employee_name'; }
    $insCols[]='created_by'; $place[]='?'; $extra[]='created_by';

$sql="INSERT INTO pissing_entry (".implode(',',$insCols).") VALUES (".implode(',',$place).")";
    $st=$pdo->prepare($sql);

foreach($rows as $r){
      if ($r['status']!=='OK') continue;
      $vals=[$company_id, $r['pissing_date']];
      foreach($extra as $k){
        if ($k==='created_by') $vals[]=$user_id;
        else {
          $v=$r[$k];
          $vals[] = ($v==='' || $v===null) ? null : $v;
        }
      }
      $st->execute($vals); $imported++;
    }
    $pdo->commit();
  }catch(Throwable $e){
    if ($pdo->inTransaction()) $pdo->rollBack();
    $impErr=$e->getMessage();
  }
}
?>
<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<title>Pissing Entry Import</title>
<meta name="viewport" content="width=device-width, initial-scale=1">
<style>
  :root{--pri:#34A853;--bg:#F5FFF7;--muted:#5F6368;--card:#fff;--line:#e5e7eb;--ok:#0a0;--bad:#b00020}
  body{margin:0;background:var(--bg);font:14px/1.45 system-ui,-apple-system,Segoe UI,Roboto,Arial;color:#111}
  .wrap{max-width:1100px;margin:18px auto;padding:0 16px 24px}
  .card{background:var(--card);border:1px solid var(--line);border-radius:14px;box-shadow:0 2px 8px rgba(0,0,0,.04);padding:16px;margin-top:16px}
  h1{margin:0 0 10px}
  .row{display:flex;gap:10px;flex-wrap:wrap;align-items:center}
  .btn{padding:8px 12px;border-radius:10px;border:1px solid var(--line);background:#fff;cursor:pointer}
  .btn.primary{background:var(--pri);color:#fff;border-color:transparent}
  input,select{padding:10px;border:1px solid #d1d5db;border-radius:10px}
  .msg{padding:10px 12px;border-radius:10px;margin:10px 0}
  .err{background:#fdecea;border:1px solid #f5c6cb;color:#7f1d1d}
  .okmsg{background:#e6f4ea;border:1px solid #cce9d4;color:#065f46}
  table{width:100%;border-collapse:collapse;margin-top:8px}
  th,td{padding:8px;border-bottom:1px solid #f1f5f9;text-align:left;font-size:13px}
  .status-ok{color:#059669;font-weight:600}
  .status-bad{color:#b00020;font-weight:600}
</style>
</head>
<body>
<div class="wrap">
  <div class="card">
    <div class="row" style="justify-content:space-between">
      <h1>Pissing Entry Import</h1>
      <div class="row">
        <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/pissing_entry.php">Go to Pissing Entry</a>
      </div>
    </div>

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

<?php if($errorTop): ?><div class="msg err"><?= h($errorTop) ?></div><?php endif; ?>
    <?php if($impErr): ?><div class="msg err">Import error: <?= h($impErr) ?></div>
    <?php elseif($doImport): ?><div class="msg okmsg">Imported: <b><?= (int)$imported ?></b> row(s).</div><?php endif; ?>

<form method="post" enctype="multipart/form-data" class="row">
      <div>
        <div style="font-size:12px;color:#666">Use one employee for all rows (Dept 31)</div>
        <select name="employee_id_fixed">
          <option value="0">-- Select employee (optional) --</option>
          <?php foreach($deptEmployees as $e): ?>
            <option value="<?= (int)$e['id'] ?>" <?= ((int)($_POST['employee_id_fixed'] ?? 0))==(int)$e['id']?'selected':'' ?>>
              <?= h($e['name']) ?> (ID <?= (int)$e['id'] ?>)
            </option>
          <?php endforeach; ?>
        </select>
      </div>

<input type="file" name="file" accept=".csv,.xlsx,.xls" required>
      <button class="btn" name="_action" value="preview">Preview</button>
      <?php if($rows && $okCount>0): ?>
        <button class="btn primary" name="_action" value="import_ok">Import OK Rows</button>
      <?php endif; ?>
    </form>
  </div>

<?php if($rows): ?>
    <div class="card">
      <div class="row">
        <div>Preview — <span style="color:#059669">OK: <?= (int)$okCount ?></span>,
          <span style="color:#b00020">Errors: <?= (int)$errCount ?></span></div>
      </div>
      <div style="overflow:auto">
        <table>
          <thead>
            <tr>
              <th>#(Row)</th>
              <th>pissing_date</th>
              <th>machine</th>
              <th>pissing_type</th>
              <th>beam_no</th>
              <th>machine_no</th>
              <?php if($has_employee_name): ?><th>employee_name</th><?php endif; ?>
              <?php if($has_employee_id):   ?><th>employee_id</th><?php endif; ?>
              <?php if($has_remark):        ?><th>remark</th><?php endif; ?>
              <th>Status</th>
            </tr>
          </thead>
          <tbody>
            <?php foreach($rows as $r): ?>
              <tr>
                <td><?= (int)$r['src_row'] ?></td>
                <td><?= h($r['pissing_date']) ?></td>
                <td><?= h($r['machine']) ?></td>
                <td><?= h($r['pissing_type']) ?></td>
                <td><?= h($r['beam_no']) ?></td>
                <td><?= h($r['machine_no']) ?></td>
                <?php if($has_employee_name): ?><td><?= h($r['employee_name']) ?></td><?php endif; ?>
                <?php if($has_employee_id):   ?><td><?= (int)$r['employee_id'] ?></td><?php endif; ?>
                <?php if($has_remark):        ?><td><?= h($r['remark']) ?></td><?php endif; ?>
                <td class="<?= $r['status']==='OK'?'status-ok':'status-bad' ?>"><?= h($r['status']) ?></td>
              </tr>
            <?php endforeach; ?>
          </tbody>
        </table>
      </div>
    </div>
  <?php endif; ?>
</div>
</body>
</html>