View Code - 20260721

page_usage_report.php | 20260721_154033.php Initial Bulk Import

<?php
/* =========================================================================
   File: /erp/modules/dev/page_usage_report.php
   Purpose: View & export page_usage_log (company-scoped)
   Scope : Page-local only; no global/base edits.
   ========================================================================= */
error_reporting(E_ALL); ini_set('display_errors', 1);

/* ---------------------- A) Auth + Company Scope ----------------------- */
$company_id = 0; $u = null; $pdo = $pdo ?? null;
$ROLE_OK = ['Owner','Admin','Manager','Developer']; // who can view report

$used_acl = false;
$try_page_acl = __DIR__ . '/../auth/page_acl.php';
if (is_file($try_page_acl)) {
  require_once $try_page_acl;
  $ctx        = page_require_access('dev_tools'); // map this slug in your ACL
  $company_id = (int)($ctx['company_id'] ?? 0);
  $u          = $ctx['user'] ?? null;
  $pdo        = $ctx['pdo'] ?? null;
  $used_acl   = true;
}
if (!$used_acl) {
  // fallback to basic session auth
  $try_auth = __DIR__ . '/../auth/auth.php';
  if (is_file($try_auth)) {
    require_once $try_auth;
    require_login();
    $u = auth_user();
    $company_id = (int)($u['company_id'] ?? 0);
  }
  if (!$pdo instanceof PDO) {
    require __DIR__ . '/../../core/db.php';
  }
}

if (!$u || !in_array($u['role'] ?? '', $ROLE_OK, true)) {
  http_response_code(403);
  exit('Forbidden: role not allowed');
}

/* ---------------------- B) Helpers ------------------------------------ */
function h($s){ return htmlspecialchars((string)$s, ENT_QUOTES, 'UTF-8'); }
function qs_without($keys=[]){
  $q = $_GET; foreach((array)$keys as $k){ unset($q[$k]); }
  $s = http_build_query($q);
  return $s ? ('?'.$s) : '';
}
function input($key,$def=''){ return isset($_GET[$key]) ? trim((string)$_GET[$key]) : $def; }

/* ---------------------- C) Filters ------------------------------------ */
$df        = input('date_from',''); // YYYY-MM-DD
$dt        = input('date_to','');   // YYYY-MM-DD
$page_like = input('page','');      // partial match for page_name
$method    = strtoupper(input('method','')); // GET/POST (optional)
$user_id   = (int)input('user_id','0');
$table_has = input('table_contains',''); // substring inside used_tables JSON
$uri_like  = input('uri','');       // substring in URI
$per_page  = max(10, min(200, (int)input('per', 50)));
$pg        = max(1, (int)input('pg', 1));
$export    = (input('export','') === 'csv');

$where = ["company_id = :cid"];
$args  = [':cid' => $company_id];

if ($df !== '') { $where[] = "request_ts >= :df"; $args[':df'] = $df.' 00:00:00'; }
if ($dt !== '') { $where[] = "request_ts <= :dt"; $args[':dt'] = $dt.' 23:59:59'; }
if ($page_like !== '') { $where[] = "page_name LIKE :pgname"; $args[':pgname'] = '%'.$page_like.'%'; }
if ($method !== '' && in_array($method, ['GET','POST','PUT','DELETE','PATCH','HEAD','OPTIONS'], true)) {
  $where[] = "method = :m"; $args[':m'] = $method;
}
if ($user_id > 0) { $where[] = "user_id = :uid"; $args[':uid'] = $user_id; }
if ($uri_like !== '') { $where[] = "uri LIKE :uri"; $args[':uri'] = '%'.$uri_like.'%'; }
if ($table_has !== '') {
  // JSON stored as text -> substring search is fine for quick filter
  $where[] = "used_tables LIKE :tblhas"; $args[':tblhas'] = '%'.$table_has.'%';
}

$WHERE_SQL = implode(' AND ', $where);

/* ---------------------- D) Counts + Data ------------------------------- */
$total = 0;
try {
  $st = $pdo->prepare("SELECT COUNT(*) FROM page_usage_log WHERE $WHERE_SQL");
  $st->execute($args);
  $total = (int)$st->fetchColumn();
} catch(Throwable $e){ $total = 0; }

$offset = ($pg - 1) * $per_page;

$sql_base = "SELECT id, request_ts, method, page_name, uri,
                    user_id, script, included_files, declared_functions, used_tables, ip, ua
             FROM page_usage_log
             WHERE $WHERE_SQL";

if ($export) {
  // CSV export (no pagination)
  $sql = $sql_base . " ORDER BY id DESC LIMIT 50000";
  $st  = $pdo->prepare($sql);
  $st->execute($args);
  header('Content-Type: text/csv; charset=utf-8');
  header('Content-Disposition: attachment; filename="page_usage_log.csv"');
  $out = fopen('php://output', 'w');
  fputcsv($out, ['ID','Timestamp','Method','Page','URI','UserID','Script','Tables','FunctionsCount','IncludesCount','IP','UserAgent']);
  while ($r = $st->fetch(PDO::FETCH_ASSOC)) {
    $tables = '';
    if (!empty($r['used_tables'])) {
      $tjson = json_decode($r['used_tables'], true);
      if (is_array($tjson)) $tables = implode('|', $tjson);
      else $tables = (string)$r['used_tables'];
    }
    $funcCount = 0; $incCount = 0;
    $dj = json_decode($r['declared_functions'], true); if (is_array($dj)) $funcCount = count($dj);
    $ij = json_decode($r['included_files'], true);     if (is_array($ij)) $incCount  = count($ij);

fputcsv($out, [
      $r['id'], $r['request_ts'], $r['method'], $r['page_name'], $r['uri'],
      $r['user_id'], $r['script'], $tables, $funcCount, $incCount, $r['ip'], $r['ua']
    ]);
  }
  fclose($out);
  exit;
}

// paginated view
$sql = $sql_base . " ORDER BY id DESC LIMIT :lim OFFSET :off";
$st  = $pdo->prepare($sql);
foreach ($args as $k=>$v) { $st->bindValue($k, $v); }
$st->bindValue(':lim', $per_page, PDO::PARAM_INT);
$st->bindValue(':off', $offset, PDO::PARAM_INT);
$st->execute();
$rows = $st->fetchAll(PDO::FETCH_ASSOC);

/* ---------------------- E) Minimal UI (no global CSS deps) ------------- */
?>
<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<title>Page Usage Report</title>
<meta name="viewport" content="width=device-width, initial-scale=1">
<style>
  body{font-family:system-ui,-apple-system,Segoe UI,Roboto,Ubuntu; margin:16px; background:#f8fafb; color:#222}
  .card{background:#fff; border:1px solid #e5e7eb; border-radius:14px; padding:14px; box-shadow:0 1px 2px rgba(0,0,0,.04); margin-bottom:16px;}
  .grid{display:grid; grid-template-columns: repeat(6, 1fr); gap:8px;}
  .grid .col-2{grid-column: span 2;}
  .grid .col-3{grid-column: span 3;}
  .grid .col-6{grid-column: span 6;}
  input,select{width:100%; padding:8px 10px; border:1px solid #d1d5db; border-radius:10px; background:#fff}
  label{font-size:12px; color:#555;}
  .actions{display:flex; gap:8px; flex-wrap:wrap; margin-top:8px}
  .btn{padding:8px 12px; border-radius:10px; border:1px solid #cbd5e1; background:#fff; cursor:pointer}
  .btn.primary{background:#34A853; color:#fff; border-color:#34A853}
  .btn.link{background:transparent; border:none; color:#2563eb; text-decoration:underline; cursor:pointer}
  table{width:100%; border-collapse:collapse; background:#fff; overflow:hidden; border-radius:14px}
  th,td{padding:10px 12px; border-bottom:1px solid #eee; vertical-align:top; font-size:13px}
  th{background:#f3f4f6; text-align:left; font-weight:600;}
  .muted{color:#6b7280; font-size:12px}
  .badge{display:inline-block; padding:2px 8px; border-radius:9999px; border:1px solid #cbd5e1; font-size:11px; margin-right:4px}
  .pill{display:inline-block; background:#eef2ff; color:#3730a3; padding:2px 8px; border-radius:9999px; font-size:11px; margin:2px 4px 0 0}
  .pagination{display:flex; gap:6px; align-items:center; margin-top:10px}
  .pagination a{padding:6px 10px; border:1px solid #d1d5db; border-radius:8px; text-decoration:none; color:#111}
  .pagination .current{background:#111; color:#fff; border-color:#111}
  .nowrap{white-space:nowrap}
  .break{word-break:break-all}
</style>
</head>
<body>

<div class="card">
  <h2 style="margin:6px 0 12px;">Page Usage Report</h2>
  <form method="get" class="grid">
    <div>
      <label>Date From</label>
      <input type="date" name="date_from" value="<?=h($df)?>">
    </div>
    <div>
      <label>Date To</label>
      <input type="date" name="date_to" value="<?=h($dt)?>">
    </div>
    <div>
      <label>Method</label>
      <select name="method">
        <?php
          $methods = ['','GET','POST','PUT','DELETE','PATCH','HEAD','OPTIONS'];
          foreach ($methods as $m) {
            $sel = ($m === $method) ? 'selected' : '';
            echo '<option '.$sel.'>'.h($m).'</option>';
          }
        ?>
      </select>
    </div>
    <div>
      <label>User ID</label>
      <input type="number" name="user_id" value="<?= $user_id ?: '' ?>">
    </div>
    <div>
      <label>Page (contains)</label>
      <input type="text" name="page" placeholder="/erp/..." value="<?=h($page_like)?>">
    </div>
    <div>
      <label>URI (contains)</label>
      <input type="text" name="uri" placeholder="?param=..." value="<?=h($uri_like)?>">
    </div>

<div class="col-2">
      <label>Table Contains</label>
      <input type="text" name="table_contains" placeholder="beam_qualities" value="<?=h($table_has)?>">
    </div>
    <div>
      <label>Per Page</label>
      <input type="number" name="per" value="<?=h($per_page)?>" min="10" max="200">
    </div>
    <div>
      <label>Page #</label>
      <input type="number" name="pg" value="<?=h($pg)?>" min="1">
    </div>
    <div class="col-3 actions">
      <button class="btn">Apply Filters</button>
      <a class="btn" href="<?=h(basename(__FILE__))?>">Reset</a>
      <a class="btn primary" href="<?=h(basename(__FILE__).'?'.http_build_query(array_merge($_GET,['export'=>'csv'])))?>">Export CSV</a>
    </div>
  </form>
  <div class="muted" style="margin-top:6px;">
    Company: <b><?=h($company_id)?></b> • Total: <b><?=number_format($total)?></b>
  </div>
</div>

<div class="card">
  <div style="overflow:auto;">
    <table>
      <thead>
        <tr>
          <th class="nowrap">ID</th>
          <th class="nowrap">Timestamp</th>
          <th>Method</th>
          <th>Page</th>
          <th>URI</th>
          <th>User</th>
          <th>Tables</th>
          <th>Counts</th>
          <th>IP</th>
          <th>User Agent</th>
        </tr>
      </thead>
      <tbody>
      <?php if (!$rows): ?>
        <tr><td colspan="10" class="muted">No data found for selected filters.</td></tr>
      <?php else: foreach ($rows as $r):
        $tables = [];
        if (!empty($r['used_tables'])) {
          $tj = json_decode($r['used_tables'], true);
          if (is_array($tj)) $tables = $tj;
        }
        $funcCount = 0; $incCount = 0;
        $dj = json_decode($r['declared_functions'], true); if (is_array($dj)) $funcCount = count($dj);
        $ij = json_decode($r['included_files'], true);     if (is_array($ij)) $incCount  = count($ij);
      ?>
        <tr>
          <td class="nowrap"><?= (int)$r['id'] ?></td>
          <td class="nowrap"><?= h($r['request_ts']) ?></td>
          <td><span class="badge"><?= h($r['method']) ?></span></td>
          <td class="break"><?= h($r['page_name']) ?></td>
          <td class="break"><?= h($r['uri']) ?></td>
          <td class="nowrap"><?= (int)$r['user_id'] ?></td>
          <td>
            <?php
              if ($tables) {
                foreach ($tables as $t) echo '<span class="pill">'.h($t).'</span>';
              } else {
                echo '<span class="muted">—</span>';
              }
            ?>
          </td>
          <td class="nowrap">
            <span class="badge">func: <?= (int)$funcCount ?></span>
            <span class="badge">inc: <?= (int)$incCount ?></span>
          </td>
          <td class="nowrap"><?= h($r['ip']) ?></td>
          <td class="break muted"><?= h($r['ua']) ?></td>
        </tr>
      <?php endforeach; endif; ?>
      </tbody>
    </table>
  </div>

<?php
    // pagination
    $pages = max(1, (int)ceil($total / $per_page));
    if ($pages > 1):
      $baseQS = $_GET; unset($baseQS['pg']); $base = basename(__FILE__).'?'.http_build_query($baseQS);
  ?>
  <div class="pagination">
    <?php
      $makeLink = function($p,$label=null,$cls='') use($base){
        $href = $base . '&pg=' . $p;
        $lab  = $label ?? $p;
        echo '<a class="'.h($cls).'" href="'.h($href).'">'.h($lab).'</a>';
      };
      $makeLink(max(1,$pg-1), '« Prev');
      // show window
      $win = 2;
      for ($i=max(1,$pg-$win); $i<=min($pages,$pg+$win); $i++){
        $cls = ($i==$pg)?'current':'';
        $makeLink($i, (string)$i, $cls);
      }
      $makeLink(min($pages,$pg+1), 'Next »');
    ?>
  </div>
  <?php endif; ?>
</div>

</body>
</html>