View Code - 20260722

import_flat.php | 20260722_120325.php Initial Domain Snapshot

<?php
require __DIR__.'/../core/db.php';
require __DIR__.'/../modules/auth/auth.php';
require_login();
$u = auth_user();
$COMPANY_ID = (int)$u['company_id'];
$USER_ID    = (int)$u['id'];

/* Try PhpSpreadsheet (optional) */
$phpss_ok = false;
$autoload = dirname(__DIR__) . '/../vendor/autoload.php';
if (is_file($autoload)) {
  require_once $autoload;
  $phpss_ok = class_exists('PhpOffice\\PhpSpreadsheet\\IOFactory');
}
use PhpOffice\PhpSpreadsheet\IOFactory as SSIO;

/* Simple XLSX reader (fallback) */
function xlsx_simple_read($path){
  if(!class_exists('ZipArchive')) throw new RuntimeException('ZipArchive missing; install PHP zip or upload CSV.');
  $zip=new ZipArchive(); if($zip->open($path)!==true) throw new RuntimeException('Cannot open XLSX.');
  $shared=[]; $ssi=$zip->locateName('xl/sharedStrings.xml');
  if($ssi!==false){ $xml=simplexml_load_string($zip->getFromIndex($ssi));
    foreach($xml->si as $si){ if(isset($si->t)) $shared[]=(string)$si->t; else { $t=''; foreach($si->r as $r) $t.=(string)$r->t; $shared[]=$t; } }
  }
  $sheet=$zip->getFromName('xl/worksheets/sheet1.xml');
  if($sheet===false){ for($i=1;$i<=10;$i++){ $sheet=$zip->getFromName("xl/worksheets/sheet{$i}.xml"); if($sheet!==false) break; }
    if($sheet===false){ $zip->close(); throw new RuntimeException('Worksheet not found'); } }
  $sx=simplexml_load_string($sheet);
  $col=function($ref){ $L=preg_replace('/\d+/','',strtoupper((string)$ref)); $n=0; for($i=0;$i<strlen($L);$i++){ $n=$n*26+(ord($L[$i])-64);} return max(0,$n-1); };
  $rows=[];
  foreach($sx->sheetData->row as $row){ $r=[]; foreach($row->c as $c){ $ref=(string)$c['r']; $type=(string)$c['t']; $v=(string)$c->v; $val=($type==='s')?($shared[(int)$v]??''):$v; $r[$col($ref)]=$val; }
    if($r){ ksort($r); $line=array_values($r); for($i=count($line)-1;$i>=0;$i--){ if($line[$i]!==''&&$line[$i]!==null) break; unset($line[$i]); } $rows[]=array_values($line); }
  }
  $zip->close(); return $rows;
}

/* Read any sheet -> rows[][] */
function read_any($tmp,$name,$phpss_ok){
  $ext=strtolower(pathinfo($name,PATHINFO_EXTENSION));
  if($ext==='csv'){
    $rows=[]; if(($fh=fopen($tmp,'r'))){ while(($r=fgetcsv($fh))!==false){ $rows[]=$r; } fclose($fh); }
    return $rows;
  }
  if(in_array($ext,['xlsx','xlsm','xls'])){
    if($phpss_ok){ $reader=SSIO::createReaderForFile($tmp); $ss=$reader->load($tmp); $ws=$ss->getActiveSheet(); return $ws->toArray(null,true,true,false); }
    return xlsx_simple_read($tmp);
  }
  throw new RuntimeException("Unsupported file .$ext (use CSV/XLSX)");
}

/* Normalize date */
function d($v){
  if ($v===''||$v===null) return null;
  if (is_numeric($v) && (int)$v>10000){ $base=new DateTime('1899-12-30',new DateTimeZone('UTC')); $base->modify('+'.(int)$v.' days'); return $base->format('Y-m-d'); }
  $t=strtotime(str_replace('.','/',$v)); return $t? date('Y-m-d',$t):null;
}

$err=''; $preview=[]; $imported=0;

if (isset($_POST['step']) && $_POST['step']==='preview' && !empty($_FILES['file']['tmp_name'])) {
  try{
    $rows = read_any($_FILES['file']['tmp_name'], $_FILES['file']['name'], $phpss_ok);
    if(!$rows || count($rows)<2) throw new RuntimeException('Empty file or missing header.');

// Expect header: Date,Quality,Karigar,Meter,Taka No.,Mc No.,PBN,SBN,Weight,Total Meter
    $hdr = array_map(fn($x)=>strtolower(trim((string)$x)), $rows[0]);
    $need = ['date','quality','karigar','meter','taka no.','mc no.','pbn','sbn','weight','total meter'];
    foreach ($need as $n) { if (!in_array($n,$hdr,true)) throw new RuntimeException("Missing column: $n"); }
    $idx = array_flip($hdr);

for ($i=1;$i<count($rows);$i++){
      $r=$rows[$i]; if(!array_filter($r)) continue;
      $preview[]=[
        'date'=>d($r[$idx['date']]??null),
        'quality'=>$r[$idx['quality']]??'',
        'karigar'=>$r[$idx['karigar']]??'',
        'meter'=>$r[$idx['meter']]??'',
        'taka_no'=>$r[$idx['taka no.']]??'',
        'mc_no'=>$r[$idx['mc no.']]??'',
        'pbn'=>$r[$idx['pbn']]??'',
        'sbn'=>$r[$idx['sbn']]??'',
        'weight'=>$r[$idx['weight']]??'',
        'total_meter'=>$r[$idx['total meter']]??'',
      ];
    }
  }catch(Throwable $e){ $err=$e->getMessage(); }
}

if (isset($_POST['step']) && $_POST['step']==='import' && !empty($_POST['payload'])) {
  $data = json_decode($_POST['payload'], true);
  if (!is_array($data)) { $err='Bad payload'; }
  else {
    $ins = $pdo->prepare("
      INSERT INTO loom_production_flat
        (company_id, entry_date, quality_name, karigar_name, meter,
         taka_no, machine_no, pbn, sbn, weight, total_meter, created_by)
      VALUES (?,?,?,?,?,?,?,?,?,?,?,?)
    ");
    foreach ($data as $r){
      $ins->execute([
        $COMPANY_ID,
        $r['date'],
        $r['quality'],
        $r['karigar'],
        (float)$r['meter'],
        (int)$r['taka_no'],
        (int)$r['mc_no'],
        $r['pbn']?:null,
        $r['sbn']?:null,
        ($r['weight']===''? null : (float)$r['weight']),
        ($r['total_meter']===''? null : (float)$r['total_meter']),
        $USER_ID
      ]);
      $imported++;
    }
  }
}
?>
<!doctype html>
<html><head><meta charset="utf-8"><title>Import Flat (CSV/XLSX)</title>
<style>
 body{font-family:system-ui;background:#0b0f12;color:#e8e8e8;margin:0}
 .wrap{max-width:1000px;margin:24px auto;padding:0 16px}
 .card{background:#12171c;border:1px solid #2a3137;border-radius:12px;padding:16px;margin-top:16px}
 table{width:100%;border-collapse:collapse} th,td{border-bottom:1px solid #2a3137;padding:6px 8px;font-size:13px}
 .btn{padding:8px 12px;border-radius:8px;border:1px solid #2a3137;background:#1a2128;color:#e8e8e8}
 .ok{color:#8ef19a}.bad{color:#ffb4b4}
</style>
<script>
function doImport(){ const p=document.getElementById('payload'); document.getElementById('impForm').payload.value=p.textContent; document.getElementById('impForm').submit(); }
</script>
</head>
<body><div class="wrap">
  <div class="card">
    <h3 style="margin:0 0 8px">Import Flat Data (CSV / XLSX)</h3>
    <form method="post" enctype="multipart/form-data">
      <input type="hidden" name="step" value="preview">
      <input type="file" name="file" accept=".csv,.xlsx,.xlsm,.xls" required>
      <button class="btn">Preview</button>
    </form>
    <div style="font-size:12px;color:#9aa4af;margin-top:6px">
      Expected columns: Date, Quality, Karigar, Meter, Taka No., Mc No., PBN, SBN, Weight, Total Meter
      <br>PhpSpreadsheet: <?= $phpss_ok ? 'available' : 'not found (fallback reader active)' ?>
    </div>
  </div>

<?php if ($err): ?><div class="card bad"><?=htmlspecialchars($err)?></div><?php endif; ?>

<?php if ($preview): ?>
    <div class="card">
      <div style="display:flex;justify-content:space-between;align-items:center">
        <b>Preview (rows: <?=count($preview)?>)</b>
        <form id="impForm" method="post">
          <input type="hidden" name="step" value="import">
          <input type="hidden" name="payload" value="">
          <button type="button" class="btn" onclick="doImport()">Import</button>
        </form>
      </div>
      <div style="overflow:auto;max-height:60vh;margin-top:10px">
        <table>
          <thead><tr>
            <th>Date</th><th>Quality</th><th>Karigar</th><th>Meter</th><th>Taka No.</th><th>Mc No.</th><th>PBN</th><th>SBN</th><th>Weight</th><th>Total Meter</th>
          </tr></thead>
          <tbody>
            <?php foreach ($preview as $r): ?>
              <tr>
                <td><?=htmlspecialchars($r['date'])?></td>
                <td><?=htmlspecialchars($r['quality'])?></td>
                <td><?=htmlspecialchars($r['karigar'])?></td>
                <td><?=htmlspecialchars($r['meter'])?></td>
                <td><?=htmlspecialchars($r['taka_no'])?></td>
                <td><?=htmlspecialchars($r['mc_no'])?></td>
                <td><?=htmlspecialchars($r['pbn'])?></td>
                <td><?=htmlspecialchars($r['sbn'])?></td>
                <td><?=htmlspecialchars($r['weight'])?></td>
                <td><?=htmlspecialchars($r['total_meter'])?></td>
              </tr>
            <?php endforeach; ?>
          </tbody>
        </table>
      </div>
      <pre id="payload" style="display:none"><?=htmlspecialchars(json_encode($preview, JSON_UNESCAPED_UNICODE))?></pre>
    </div>
  <?php endif; ?>

<?php if ($imported): ?>
    <div class="card ok">Imported <?=$imported?> rows into <code>loom_production_flat</code>.</div>
  <?php endif; ?>
</div></body></html>