View Code - 20260721

finish_beam_production.php | 20260721_154032.php Initial Bulk Import

<?php
// finish_beam_production.php
// Usage: drop in /erp/ and open in browser. Requires auth + PDO $pdo like other pages.

// --- Bootstrap & Auth (your project's pattern) ---
header('X-Frame-Options: SAMEORIGIN');
error_reporting(E_ALL); ini_set('display_errors',1);

require __DIR__.'/modules/auth/auth.php';
require_login();
$u = auth_user();
$company_id = (int) $u['company_id'];
$user_id    = (int) $u['id'];

$pdo = $GLOBALS['pdo'] ?? null;
if (!$pdo) { require __DIR__ . '/core/db.php'; }

// ------------------------- Helpers -------------------------
function h($s){ return htmlspecialchars((string)$s, ENT_QUOTES, 'UTF-8'); }
function table_exists(PDO $pdo, $t){ try{ $pdo->query("SELECT 1 FROM `$t` LIMIT 1"); return true; }catch(Throwable $e){ return false; } }
function col_exists(PDO $pdo, $t, $c){ try{ foreach($pdo->query("DESCRIBE `$t`") as $r){ if(($r['Field']??'')===$c) return true; } }catch(Throwable $e){} return false; }

// Ensure finish_beam has the columns we will write (safe, idempotent)
if (table_exists($pdo,'finish_beam')) {
    $alter = [];
    $need = [
      'meter_from_date' => "DATE NULL",
      'meter_to_date' => "DATE NULL",
      'production_meter_total' => "DECIMAL(12,3) NULL DEFAULT 0.000",
      'production_entries_count' => "INT NULL DEFAULT 0",
      'pending_meter' => "DECIMAL(12,3) NULL",
      'shortage_percent' => "DECIMAL(6,2) NULL",
      'warper_id' => "INT NULL",
      'finished' => "TINYINT(1) NULL DEFAULT 1" // optional flag
    ];
    foreach($need as $col=>$spec) if(!col_exists($pdo,'finish_beam',$col)) $alter[] = "ADD COLUMN `$col` $spec";
    if($alter){ try{ $pdo->exec("ALTER TABLE finish_beam ".implode(',', $alter)); }catch(Throwable $e){ /* ignore */ } }
} else {
    // If finish_beam missing, bail
    echo "<h2>Table finish_beam not found. Create it first.</h2>";
    exit;
}

// ------------------------- Core sync function -------------------------
/**
 * Sync calculations for one finish_beam row.
 * Returns array with computed values or false on error.
 */
function calc_finish_beam(PDO $pdo, array $fbRow, int $company_id) {
    // fbRow: a row from finish_beam
    $res = [
      'meter_from_date' => null,
      'meter_to_date' => null,
      'production_meter_total' => 0.0,
      'production_entries_count' => 0,
      'pending_meter' => null,
      'shortage_percent' => null,
      'warper_id' => null,
      'notes' => ''
    ];

$beam_no = trim((string)($fbRow['beam_no'] ?? ''));
    if ($beam_no === '') { $res['notes'] = 'blank beam_no'; return $res; }

// Find beam_entry for this beam_no & company (to get meter and installation_date & employee)
    try {
        $st = $pdo->prepare("SELECT id, entry_date AS created_date, installation_date, meter, taka_no, employee_id
                              FROM beam_entry WHERE company_id=:cid AND beam_no=:bn ORDER BY id DESC LIMIT 1");
        $st->execute([':cid'=>$company_id, ':bn'=>$beam_no]);
        $be = $st->fetch(PDO::FETCH_ASSOC);
    } catch(Throwable $e) { $res['notes'] = 'beam_entry lookup failed'; return $res; }

if (!$be) {
        $res['notes'] = 'beam_entry not found';
        return $res;
    }

// Determine date range: start = installation_date (if present) else created_date
    $start_date = null;
    if (!empty($be['installation_date']) && $be['installation_date']!=='0000-00-00') {
        $start_date = date('Y-m-d', strtotime($be['installation_date']));
    } else {
        $start_date = date('Y-m-d', strtotime($be['created_date'] ?? date('Y-m-d')));
    }
    $res['meter_from_date'] = $start_date;

// finish_date is in finish_beam
    $finish_dt_raw = $fbRow['finish_date'] ?? $fbRow['finished_at'] ?? $fbRow['finish_date'] ?? null;
    if (!$finish_dt_raw) {
        $res['notes'] = 'finish_date missing';
        return $res;
    }
    $finish_date = date('Y-m-d', strtotime($finish_dt_raw));
    $res['meter_to_date'] = $finish_date;

// If finish < start, nothing to sum
    if (strtotime($finish_date) < strtotime($start_date)) {
        $res['notes'] = 'finish < install';
        return $res;
    }

// Production selection: production_entry rows for this company and machine in date range
    // NOTE: production_entry.lines_json is JSON; we'll decode lines_json and sum meters that fall in range.
    try {
        $q = $pdo->prepare("SELECT id, entry_date, machine_id, lines_json
                              FROM production_entry
                             WHERE company_id=:cid
                               AND machine_id = :mid
                               AND entry_date BETWEEN :d1 AND :d2
                          ORDER BY entry_date ASC, id ASC");
        $q->execute([':cid'=>$company_id, ':mid'=>$fbRow['machine_no'], ':d1'=>$start_date, ':d2'=>$finish_date]);
        $rows = $q->fetchAll(PDO::FETCH_ASSOC);
    } catch(Throwable $e){
        $res['notes'] = 'production lookup failed';
        return $res;
    }

$total_meters = 0.0;
    $total_lines  = 0;
    foreach($rows as $pe) {
        $json = trim($pe['lines_json'] ?? '');
        if ($json === '') continue;
        // Some systems store JSON with single quotes or escaped; try decode safely
        $decoded = json_decode($json, true);
        if ($decoded === null && json_last_error() !== JSON_ERROR_NONE) {
            // try to fix: replace single quotes with double (best-effort)
            $try = str_replace("'", '"', $json);
            $decoded = json_decode($try, true);
            if ($decoded === null) continue;
        }
        if (!is_array($decoded)) continue;
        foreach($decoded as $line) {
            // expected keys: meter (number), date (YYYY-MM-DD)
            $m = isset($line['meter']) ? (float)$line['meter'] : 0.0;
            $d = isset($line['date']) ? date('Y-m-d', strtotime($line['date'])) : null;
            // ensure date in range (installation -> finish) inclusive
            if ($d === null) {
                // fallback: accept meter if production_entry.entry_date in range
                $entry_date = date('Y-m-d', strtotime($pe['entry_date']));
                if ($entry_date < $start_date || $entry_date > $finish_date) continue;
            } else {
                if ($d < $start_date || $d > $finish_date) continue;
            }
            $total_meters += $m;
            $total_lines++;
        }
    }

$res['production_meter_total'] = round($total_meters, 3);
    $res['production_entries_count'] = (int)$total_lines;

// beam original meter
    $beam_meter = isset($be['meter']) ? (float)$be['meter'] : 0.0;

// pending meter = original - produced
    $pending = $beam_meter - $res['production_meter_total'];
    $res['pending_meter'] = round($pending, 3);
    if ($beam_meter > 0) $res['shortage_percent'] = round(($pending / $beam_meter) * 100, 2);
    else $res['shortage_percent'] = null;

// warper_id from beam_entry.employee_id
    $res['warper_id'] = (!empty($be['employee_id']) ? (int)$be['employee_id'] : null);

$res['notes'] = 'ok';
    return $res;
}

// ------------------ Perform sync for rows (bulk or single) ------------------
function sync_all_finish_beams(PDO $pdo, int $company_id, int $user_id, $limit = 500) {
    // returns array of results
    $results = [];
    // fetch finish_beam rows for this company which have finish_date not null
    $st = $pdo->prepare("SELECT * FROM finish_beam WHERE company_id = :cid ORDER BY id ASC LIMIT :lim");
    $st->bindValue(':cid', $company_id, PDO::PARAM_INT);
    $st->bindValue(':lim', (int)$limit, PDO::PARAM_INT);
    $st->execute();
    $rows = $st->fetchAll(PDO::FETCH_ASSOC);
    foreach($rows as $r) {
        $calc = calc_finish_beam($pdo, $r, $company_id);
        if (!$calc) { $results[] = ['id'=>$r['id'],'status'=>'calc_failed']; continue; }

// update DB
        try {
            $pdo->beginTransaction();
            $upd = $pdo->prepare("UPDATE finish_beam SET
                meter_from_date = :mfrom,
                meter_to_date   = :mto,
                production_meter_total = :pmt,
                production_entries_count = :pec,
                pending_meter = :pm,
                shortage_percent = :sp,
                warper_id = :wid,
                updated_at = NOW()
              WHERE id = :id AND company_id = :cid");
            $upd->execute([
                ':mfrom' => $calc['meter_from_date'] ?: null,
                ':mto'   => $calc['meter_to_date'] ?: null,
                ':pmt'   => $calc['production_meter_total'],
                ':pec'   => $calc['production_entries_count'],
                ':pm'    => $calc['pending_meter'],
                ':sp'    => $calc['shortage_percent'],
                ':wid'   => $calc['warper_id'],
                ':id'    => (int)$r['id'],
                ':cid'   => $company_id
            ]);
            $pdo->commit();
            $results[] = ['id'=>$r['id'],'beam_no'=>$r['beam_no'],'status'=>'updated','calc'=>$calc];
        } catch(Throwable $e){
            if($pdo->inTransaction()) $pdo->rollBack();
            $results[] = ['id'=>$r['id'],'beam_no'=>$r['beam_no'],'status'=>'db_error','error'=>$e->getMessage()];
        }
    }
    return $results;
}

// Handle AJAX / manual run requests
if (isset($_GET['action']) && $_GET['action'] === 'sync') {
    header('Content-Type: application/json; charset=utf-8');
    $res = sync_all_finish_beams($pdo, $company_id, $user_id, 1000);
    echo json_encode(['ok'=>true,'rows'=>count($res),'detail'=>$res], JSON_UNESCAPED_UNICODE);
    exit;
}

// CSV export
if (isset($_GET['action']) && $_GET['action'] === 'export_csv') {
    $st = $pdo->prepare("SELECT * FROM finish_beam WHERE company_id=:cid ORDER BY id ASC");
    $st->execute([':cid'=>$company_id]);
    header('Content-Type: text/csv; charset=utf-8');
    header('Content-Disposition: attachment; filename="finish_beam_production_'.date('Ymd_His').'.csv"');
    $out = fopen('php://output','w');
    // header row
    $cols = ['id','beam_no','machine_no','meter_from_date','meter_to_date','production_meter_total','production_entries_count','pending_meter','shortage_percent','warper_id','finish_date'];
    fputcsv($out,$cols);
    while($row=$st->fetch(PDO::FETCH_ASSOC)){
        $r = [];
        foreach($cols as $c) $r[] = $row[$c] ?? '';
        fputcsv($out,$r);
    }
    fclose($out);
    exit;
}

// ----------------------------- UI -----------------------------
?><!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<title>Finish Beam Production — Sync & Report</title>
<meta name="viewport" content="width=device-width,initial-scale=1">
<style>
body{font-family:system-ui,-apple-system,Segoe UI,Roboto,Arial;margin:16px;color:#111}
.container{max-width:1100px;margin:0 auto}
.header{display:flex;align-items:center;justify-content:space-between;gap:12px}
.btn{display:inline-block;padding:8px 12px;border-radius:8px;background:#0b7; color:#fff;text-decoration:none}
.btn.alt{background:#08c}
.btn.warn{background:#f59}
table{width:100%;border-collapse:collapse;margin-top:12px}
th,td{padding:8px;border:1px solid #ddd;font-size:13px}
th{background:#f3f4f6;text-align:left}
.small{font-size:12px;color:#666}
.row-actions{display:flex;gap:8px}
.notice{background:#fff7cc;padding:10px;border-radius:8px;margin-top:12px}
</style>
</head>
<body>
<div class="container">
  <div class="header">
    <div>
      <h2>Finish Beam Production — Sync & Report</h2>
      <div class="small">Company: <?=h($company_id)?> — User: <?=h($u['name'] ?? $user_id)?></div>
    </div>
    <div class="row-actions">
      <a class="btn" href="?action=sync" id="runSyncBtn">Run sync now</a>
      <a class="btn alt" href="?action=export_csv">Export CSV</a>
      <a class="btn warn" href="#" id="printBtn">Print / Save as PDF</a>
    </div>
  </div>

<div class="notice">
    <strong>Auto-sync:</strong> page will poll every 30s and update calculations in background. You can still click <em>Run sync now</em>.
    <div class="small">Calculations use <code>beam_entry.installation_date</code> → <code>finish_beam.finish_date</code> inclusive; data aggregated from <code>production_entry.lines_json</code>.</div>
  </div>

<div id="tableWrap">
    <?php
      // show finish_beam list
      $st = $pdo->prepare("SELECT fb.*, be.meter AS original_meter, be.installation_date AS install_date
                             FROM finish_beam fb
                        LEFT JOIN beam_entry be ON be.company_id = fb.company_id AND be.beam_no = fb.beam_no
                            WHERE fb.company_id = :cid
                         ORDER BY fb.id DESC
                         LIMIT 500");
      $st->execute([':cid'=>$company_id]);
      $rows = $st->fetchAll(PDO::FETCH_ASSOC);
      if (!$rows) {
        echo "<div class='small'>No finish_beam rows for this company.</div>";
      } else {
        echo "<table><thead><tr>";
        $hcols = ['ID','Beam#','Machine','Install Date','Finish Date','Original Meter','Produced','Entries','Pending','Shortage%','Warper','Notes'];
        foreach($hcols as $hc) echo "<th>".h($hc)."</th>";
        echo "</tr></thead><tbody>";
        foreach($rows as $r) {
          $warper = '';
          if (!empty($r['warper_id'])) {
            $wq = $pdo->prepare("SELECT name FROM company_employee_master WHERE company_id=:cid AND id=:id LIMIT 1");
            $wq->execute([':cid'=>$company_id, ':id'=>$r['warper_id']]);
            $warper = $wq->fetchColumn() ?: '';
          }
          echo "<tr>";
          echo "<td>".h($r['id'])."</td>";
          echo "<td>".h($r['beam_no'])."</td>";
          echo "<td>".h($r['machine_no'])."</td>";
          echo "<td>".h($r['meter_from_date'] ?? $r['install_date'] ?? '')."</td>";
          echo "<td>".h($r['finish_date'] ?? '')."</td>";
          echo "<td>".h($r['original_meter'] ?? $r['meter_no'] ?? '')."</td>";
          echo "<td>".h($r['production_meter_total'] ?? '0')."</td>";
          echo "<td>".h($r['production_entries_count'] ?? '0')."</td>";
          echo "<td>".h($r['pending_meter'] ?? '')."</td>";
          echo "<td>".h($r['shortage_percent'] ?? '')."</td>";
          echo "<td>".h($warper)."</td>";
          echo "<td class='small'>".h($r['remark'] ?? '')."</td>";
          echo "</tr>";
        }
        echo "</tbody></table>";
      }
    ?>
  </div>
</div>

function setStatus(t, cls){
    status.innerText = t;
    status.style.color = cls || '#333';
  }

async function runSync() {
    setStatus('Syncing... (AJAX)', '#08c');
    try {
      const resp = await fetch('?action=sync');
      const data = await resp.json();
      if (data && data.ok) {
        setStatus('Sync complete. Updated rows: ' + (data.rows || 0), '#080');
        // reload table part
        location.reload();
      } else {
        setStatus('Sync failed', '#c00');
      }
    } catch(e) {
      setStatus('Sync error: ' + e.message, '#c00');
    }
  }

runBtn && runBtn.addEventListener('click', function(ev){
    ev.preventDefault();
    runSync();
  });

// Poll every 30s to run sync silently in background (only if page visible)
  let pollTimer = setInterval(()=> {
    if (document.hidden) return; // skip if tab hidden
    // call sync endpoint but do not block UI; we'll show small status
    fetch('?action=sync').then(r=>r.json()).then(j=>{
      // small console log
      console.log('Auto-sync', j);
    }).catch(e=>{ console.log('Auto-sync err', e); });
  }, 30000);

printBtn && printBtn.addEventListener('click', function(ev){
    ev.preventDefault();
    window.print();
  });

})();
</script>
</body>
</html>