View Code - 20260721

roll_palti_report.php | 20260721_154033.php Initial Bulk Import

<?php
/* =========================================================
   File: /erp/roll_palti_report.php
   Purpose: Roll/Palti Date-wise Report (Print + PDF)
   Uses  : roll_palti_report + roll_palti_machine_data
   Scope : Page-local; multi-company aware
   ========================================================= */
error_reporting(E_ALL); ini_set('display_errors',1);

/* ---- Auth + PDO ---- */
require __DIR__ . '/modules/auth/auth.php';
require_login();
$u          = auth_user();
$company_id = (int)($u['company_id'] ?? 0);
$pdo        = $GLOBALS['pdo'] ?? null;
if (!$pdo) { require __DIR__ . '/core/db.php'; }

/* ---- Header friendly vars ---- */
if (!isset($company) || !is_array($company)) {
  $company = ['id'=>$company_id, 'name'=>($u['company_name'] ?? '')];
}

/* ---- Header include decision (PDF me skip) ---- */
$__header = __DIR__ . '/partials/header.php';
if (!isset($USE_HEADER)) {
  $IS_PDF = (isset($_GET['fmt']) && $_GET['fmt']==='pdf');
  $USE_HEADER = !$IS_PDF;
}
if (!isset($PAGE_ID) || $PAGE_ID==='') { $PAGE_ID = 'roll_palti_report'; }
$PAGE = $PAGE_ID;

/* ---- helpers ---- */
function table_exists(PDO $pdo, string $t): bool {
  $st = $pdo->prepare("SHOW TABLES LIKE ?"); $st->execute([$t]); return (bool)$st->fetchColumn();
}
function col_exists(PDO $pdo, string $t, string $c): bool {
  $st = $pdo->prepare("SHOW COLUMNS FROM `$t` LIKE ?"); $st->execute([$c]); return (bool)$st->fetchColumn();
}

/* ---- Ensure report table (and fix missing cols for older installs) ---- */
$pdo->exec("
CREATE TABLE IF NOT EXISTS roll_palti_report (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  company_id   BIGINT      NOT NULL,
  report_date  DATE        NOT NULL,
  machine_no   VARCHAR(50) NOT NULL,
  total_roll   INT         NOT NULL,
  employee_id  BIGINT      NULL,
  employee_name VARCHAR(120) NOT NULL,
  updated_by   BIGINT      NOT NULL,
  updated_at   TIMESTAMP   DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  created_at   TIMESTAMP   DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_company_date (company_id, report_date),
  INDEX idx_company_machine (company_id, machine_no),
  INDEX idx_company_emp (company_id, employee_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
if (!col_exists($pdo,'roll_palti_report','created_at')) {
  $pdo->exec("ALTER TABLE roll_palti_report ADD COLUMN created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP");
}
if (!col_exists($pdo,'roll_palti_report','updated_at')) {
  $pdo->exec("ALTER TABLE roll_palti_report ADD COLUMN updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP");
}

/* ---- Ensure machine master (for dropdown) ---- */
$pdo->exec("
CREATE TABLE IF NOT EXISTS roll_palti_machine_data (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  company_id   BIGINT       NOT NULL,
  machine_no   VARCHAR(50)  NOT NULL,
  khata        VARCHAR(100) NULL,
  employee     VARCHAR(120) NOT NULL,
  created_at   TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
  updated_at   TIMESTAMP    NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_company_machine (company_id, machine_no),
  INDEX idx_company (company_id),
  INDEX idx_emp (company_id, employee)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

/* ---- filters ---- */
$from    = $_GET['from']    ?? date('Y-m-01');
$to      = $_GET['to']      ?? date('Y-m-d');
$machine = trim($_GET['machine'] ?? '');
$employee= trim($_GET['employee'] ?? '');

/* ---- machine list for filter ---- */
$machines = [];
$st = $pdo->prepare("SELECT machine_no FROM roll_palti_machine_data WHERE company_id=? ORDER BY machine_no");
$st->execute([$company_id]);
$machines = array_map(fn($r)=>$r['machine_no'], $st->fetchAll(PDO::FETCH_ASSOC));

/* ---- build query ---- */
$where = ["r.company_id = :cid", "r.report_date BETWEEN :f AND :t"];
$param = [":cid"=>$company_id, ":f"=>$from, ":t"=>$to];
if ($machine !== '') { $where[] = "r.machine_no = :m"; $param[":m"] = $machine; }
if ($employee !== '') { $where[] = "r.employee_name LIKE :e"; $param[":e"] = '%'.$employee.'%'; }

/* optional join to users */
$hasUsers = table_exists($pdo,'users') && col_exists($pdo,'users','id') && col_exists($pdo,'users','name');
$sql = "SELECT r.*, ".($hasUsers?"u.name AS updated_by_name":"NULL AS updated_by_name")."
        FROM roll_palti_report r ".
       ($hasUsers?"LEFT JOIN users u ON u.id = r.updated_by ":"").
       "WHERE ".implode(' AND ',$where)."
        ORDER BY r.report_date DESC, r.machine_no ASC, r.id DESC";
$st = $pdo->prepare($sql);
foreach($param as $k=>$v){ $st->bindValue($k,$v); }
$st->execute();
$rows = $st->fetchAll(PDO::FETCH_ASSOC);

/* ---- totals ---- */
$grand_total = 0;
foreach ($rows as $r) { $grand_total += (int)$r['total_roll']; }

/* ---- PDF export ---- */
if (isset($_GET['fmt']) && $_GET['fmt']==='pdf') {
  ob_start(); ?>
  <style>
    body{font-family:DejaVu Sans, Arial, sans-serif;font-size:12px}
    h2{margin:0 0 6px 0}
    .meta{margin:0 0 10px 0}
    table{width:100%;border-collapse:collapse}
    th,td{border:1px solid #ccc;padding:6px;text-align:left}
    th{background:#f0f0f0}
    tfoot td{font-weight:bold}
  </style>
  <h2><?=htmlspecialchars($company['name'] ?? 'Company')?></h2>
  <div class="meta"><b>Roll/Palti Report</b> — <?=htmlspecialchars($from)?> to <?=htmlspecialchars($to)?>
    <?php if($machine!==''){ echo ' • Machine: '.htmlspecialchars($machine); } ?>
    <?php if($employee!==''){ echo ' • Employee: '.htmlspecialchars($employee); } ?>
  </div>
  <table>
    <thead>
      <tr>
        <th>Date</th><th>Machine</th><th>Total Roll</th><th>Employee</th><th>Updated By</th><th>Updated At</th>
      </tr>
    </thead>
    <tbody>
      <?php if(!$rows){ echo '<tr><td colspan="6">No data</td></tr>'; } 
      foreach($rows as $r){
        echo '<tr>'.
          '<td>'.htmlspecialchars($r['report_date']).'</td>'.
          '<td>'.htmlspecialchars($r['machine_no']).'</td>'.
          '<td>'.(int)$r['total_roll'].'</td>'.
          '<td>'.htmlspecialchars($r['employee_name']).'</td>'.
          '<td>'.htmlspecialchars($r['updated_by_name'] ?? $r['updated_by']).'</td>'.
          '<td>'.htmlspecialchars($r['updated_at'] ?? $r['created_at']).'</td>'.
        '</tr>';
      } ?>
    </tbody>
    <tfoot>
      <tr><td colspan="2">TOTAL</td><td><?= (int)$grand_total ?></td><td colspan="3"></td></tr>
    </tfoot>
  </table>
  <?php
  $html = ob_get_clean();

// dompdf try
  $autoloads = [
    __DIR__ . '/vendor/autoload.php',
    dirname(__DIR__) . '/vendor/autoload.php'
  ];
  foreach($autoloads as $a){ if (is_file($a)) { require_once $a; break; } }

if (class_exists('Dompdf\Dompdf')) {
    $dompdf = new Dompdf\Dompdf(['isRemoteEnabled'=>true]);
    $dompdf->loadHtml($html,'UTF-8');
    $dompdf->setPaper('A4','portrait');
    $dompdf->render();
    $dompdf->stream('roll_palti_report_'.date('Ymd_His').'.pdf', ['Attachment'=>true]);
    exit;
  } else {
    header('Content-Type: text/html; charset=utf-8');
    echo "<p style='color:red'>dompdf not found. Install via Composer in /erp/vendor.</p>";
    echo $html;
    exit;
  }
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<title>Roll/Palti Report • Mister Manager</title>
<meta name="viewport" content="width=device-width, initial-scale=1" />
<style>
  :root{ --primary:#34A853; --bg:#F5FFF7; --text:#202124; --radius:16px; --shadow:0 8px 20px rgba(0,0,0,.06) }
  *{box-sizing:border-box}
  body{margin:0;font-family:Inter,system-ui,Arial,sans-serif;background:var(--bg);color:var(--text)}
  .wrap{max-width:1200px;margin:24px auto;padding:12px}
  .card{background:#fff;border-radius:16px;padding:20px;box-shadow:var(--shadow)}
  .row{display:grid;grid-template-columns:1fr 1fr 1fr 1fr 1fr;gap:10px}
  input,select{width:100%;padding:10px;border:1px solid #ddd;border-radius:10px}
  .btn{border:0;border-radius:10px;padding:10px 14px;font-weight:700;cursor:pointer}
  .btn.primary{background:var(--primary);color:#fff}
  .btn.link{background:#fff;border:1px solid #ddd}
  table{width:100%;border-collapse:collapse;margin-top:12px}
  th,td{border-bottom:1px dashed #eee;padding:8px;text-align:left}
  tfoot td{font-weight:700;border-top:2px solid #ddd}
  .hdr{display:flex;justify-content:space-between;align-items:center;margin-bottom:10px}
  @media print{
    .no-print{display:none !important}
    body{background:#fff}
    .card{box-shadow:none}
  }
</style>
</head>
<body>
<?php if ($USE_HEADER && is_file($__header)) { include_once $__header; } ?>

<div class="wrap">
  <div class="hdr">
    <h2>Roll / Palti Report</h2>
    <div class="no-print">
      <a class="btn link" href="/erp/roll_palti_entry.php">Add Entry</a>
      <a class="btn link" href="/erp/roll_palti_machine_master.php">Machine Master</a>
    </div>
  </div>

<div class="card no-print">
    <form method="get" style="display:flex; gap:10px; flex-wrap:wrap; align-items:flex-end">
      <div>
        <label>From</label>
        <input type="date" name="from" value="<?=htmlspecialchars($from)?>">
      </div>
      <div>
        <label>To</label>
        <input type="date" name="to" value="<?=htmlspecialchars($to)?>">
      </div>
      <div>
        <label>Machine</label>
        <select name="machine">
          <option value="">— All —</option>
          <?php foreach($machines as $m){ $s = ($machine===$m)?'selected':''; ?>
            <option <?=$s?>><?=htmlspecialchars($m)?></option>
          <?php } ?>
        </select>
      </div>
      <div>
        <label>Employee</label>
        <input type="text" name="employee" placeholder="name contains…" value="<?=htmlspecialchars($employee)?>">
      </div>
      <div>
        <button class="btn primary" type="submit">Filter</button>
        <a class="btn link" href="?from=<?=date('Y-m-01')?>&to=<?=date('Y-m-d')?>">This Month</a>
        <button class="btn link" type="button" onclick="window.print()">Print</button>
        <a class="btn link" href="<?=htmlspecialchars($_SERVER['PHP_SELF'])?>?from=<?=urlencode($from)?>&to=<?=urlencode($to)?><?php
          if($machine!=='') echo '&machine='.urlencode($machine);
          if($employee!=='') echo '&employee='.urlencode($employee);
        ?>&fmt=pdf">PDF</a>
      </div>
    </form>
  </div>

<div class="card">
    <div style="font-weight:700;margin-bottom:8px">
      <?=htmlspecialchars($company['name'] ?? 'Company')?> — <?=htmlspecialchars($from)?> to <?=htmlspecialchars($to)?>
      <?php if($machine!==''){ echo ' • Machine: '.htmlspecialchars($machine); } ?>
      <?php if($employee!==''){ echo ' • Employee: '.htmlspecialchars($employee); } ?>
    </div>
    <table>
      <thead><tr>
        <th>Date</th><th>Machine</th><th>Total Roll</th><th>Employee</th><th>Updated By</th><th>Updated At</th>
      </tr></thead>
      <tbody>
        <?php
        if(!$rows){ echo '<tr><td colspan="6">No data</td></tr>'; }
        foreach($rows as $r){
          echo '<tr>'.
            '<td>'.htmlspecialchars($r['report_date']).'</td>'.
            '<td>'.htmlspecialchars($r['machine_no']).'</td>'.
            '<td>'.(int)$r['total_roll'].'</td>'.
            '<td>'.htmlspecialchars($r['employee_name']).'</td>'.
            '<td>'.htmlspecialchars($r['updated_by_name'] ?? $r['updated_by']).'</td>'.
            '<td>'.htmlspecialchars($r['updated_at'] ?? $r['created_at']).'</td>'.
          '</tr>';
        }
        ?>
      </tbody>
      <tfoot>
        <tr><td colspan="2">TOTAL</td><td><?= (int)$grand_total ?></td><td colspan="3"></td></tr>
      </tfoot>
    </table>
  </div>
</div>
</body>
</html>