View Code - 20260722

attendance_install.php | 20260722_120325.php Initial Domain Snapshot

<?php
/* ============================================================================
   Module: Attendance (Install/Setup)
   File:   /erp/modules/attendance/attendance_install.php
   Scope:  Module-only (NO global/base setting changes)
   ============================================================================
*/
error_reporting(E_ALL); ini_set('display_errors',1);

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

function q(PDO $pdo, $sql){ $pdo->exec($sql); }

/* --- 1) Company locations (geo-fence) --- */
q($pdo, "CREATE TABLE IF NOT EXISTS company_locations (
  id           BIGINT PRIMARY KEY AUTO_INCREMENT,
  company_id   BIGINT NOT NULL,
  name         VARCHAR(100) NOT NULL,
  latitude     DECIMAL(10,7) NOT NULL,
  longitude    DECIMAL(10,7) NOT NULL,
  radius_m     INT NOT NULL DEFAULT 150,    -- meters
  is_active    TINYINT(1) NOT NULL DEFAULT 1,
  created_by   BIGINT NULL,
  created_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  KEY (company_id, is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

/* --- 2) Attendance punches (requested table name: attendace_data) --- */
q($pdo, "CREATE TABLE IF NOT EXISTS attendace_data (
  id               BIGINT PRIMARY KEY AUTO_INCREMENT,
  company_id       BIGINT NOT NULL,
  user_id          BIGINT NOT NULL,          -- from users.id
  employee_id      BIGINT NULL,              -- if you map users->employees
  name_cache       VARCHAR(120) NULL,        -- denormalized name at time of punch
  work_date        DATE NOT NULL,
  in_time          DATETIME NULL,
  in_lat           DECIMAL(10,7) NULL,
  in_lng           DECIMAL(10,7) NULL,
  in_accuracy_m    DECIMAL(10,2) NULL,
  in_distance_m    DECIMAL(10,2) NULL,       -- distance to matched geofence
  in_location_id   BIGINT NULL,
  in_photo_path    VARCHAR(255) NULL,
  in_verified      TINYINT(1) NOT NULL DEFAULT 0,  -- selfie/supervisor verified
  out_time         DATETIME NULL,
  out_lat          DECIMAL(10,7) NULL,
  out_lng          DECIMAL(10,7) NULL,
  out_accuracy_m   DECIMAL(10,2) NULL,
  out_distance_m   DECIMAL(10,2) NULL,
  out_location_id  BIGINT NULL,
  out_photo_path   VARCHAR(255) NULL,
  out_verified     TINYINT(1) NOT NULL DEFAULT 0,
  supervisor_ok    TINYINT(1) NOT NULL DEFAULT 0,  -- PIN/QR verified
  device_info      VARCHAR(255) NULL,
  ip_address       VARCHAR(45) NULL,
  notes            VARCHAR(255) NULL,
  created_at       TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at       TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_user_date (company_id, user_id, work_date),
  KEY (company_id, work_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

/* --- 3) Optional supervisor PINs (guard/manager) --- */
q($pdo, "CREATE TABLE IF NOT EXISTS supervisor_pins (
  id           BIGINT PRIMARY KEY AUTO_INCREMENT,
  company_id   BIGINT NOT NULL,
  user_id      BIGINT NOT NULL,       -- supervisor/guard's user id
  pin_code     VARCHAR(12) NOT NULL,  -- hashed or plain if you prefer (recommend hash)
  is_active    TINYINT(1) NOT NULL DEFAULT 1,
  created_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_pin (company_id, user_id, pin_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

/* --- Optional: compatibility VIEW with correct spelling --- */
try {
  q($pdo, "CREATE OR REPLACE VIEW attendance_data AS SELECT * FROM attendace_data");
} catch(Throwable $e){ /* ignore if insufficient privilege */ }

/* --- Handle add-location form --- */
$msg = '';
if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['add_location'])) {
  $name   = trim($_POST['name'] ?? 'HQ');
  $lat    = (float)($_POST['lat'] ?? 0);
  $lng    = (float)($_POST['lng'] ?? 0);
  $radius = (int)($_POST['radius'] ?? 150);

$stmt = $pdo->prepare("INSERT INTO company_locations 
    (company_id, name, latitude, longitude, radius_m, is_active, created_by)
    VALUES (?,?,?,?,?,1,?)");
  $stmt->execute([$company_id, $name, $lat, $lng, $radius, (int)$u['id']]);
  $msg = 'Location added.';
}

/* fetch locations */
$locs = $pdo->prepare("SELECT * FROM company_locations WHERE company_id=? AND is_active=1 ORDER BY id DESC");
$locs->execute([$company_id]);
$list = $locs->fetchAll(PDO::FETCH_ASSOC);
?>
<!doctype html>
<html>
<head>
  <meta charset="utf-8"/>
  <title>Attendance Install</title>
  <style>
    body{font-family:system-ui,-apple-system,Segoe UI,Roboto,Arial,sans-serif;padding:20px;}
    .card{border:1px solid #ddd;border-radius:12px;padding:16px;margin-bottom:16px;}
    label{display:block;margin:6px 0 2px;}
    input{padding:8px;width:100%;box-sizing:border-box;border-radius:8px;border:1px solid #ccc;}
    button{padding:10px 14px;border-radius:10px;border:0;cursor:pointer;}
    .primary{background:#344F73;color:#fff;} /* Mr. Manager-ish tone */
    table{width:100%;border-collapse:collapse;margin-top:10px}
    th,td{border:1px solid #eee;padding:8px;text-align:left}
  </style>
</head>
<body>
  <div class="card">
    <h2>Attendance module installed</h2>
    <p><?= htmlspecialchars($msg) ?></p>
    <form method="post">
      <h3>Add Company Location (Geo-fence)</h3>
      <label>Name</label><input name="name" placeholder="HQ / Unit A" required>
      <label>Latitude</label><input name="lat" placeholder="e.g. 23.1234567" required>
      <label>Longitude</label><input name="lng" placeholder="e.g. 72.1234567" required>
      <label>Radius (meters)</label><input name="radius" type="number" value="150" required>
      <p><button class="primary" name="add_location" value="1">Add Location</button></p>
    </form>
  </div>

<div class="card">
    <h3>Active Locations</h3>
    <table>
      <thead><tr><th>ID</th><th>Name</th><th>Lat</th><th>Lng</th><th>Radius(m)</th></tr></thead>
      <tbody>
      <?php foreach($list as $r): ?>
        <tr>
          <td><?= (int)$r['id'] ?></td>
          <td><?= htmlspecialchars($r['name']) ?></td>
          <td><?= htmlspecialchars($r['latitude']) ?></td>
          <td><?= htmlspecialchars($r['longitude']) ?></td>
          <td><?= (int)$r['radius_m'] ?></td>
        </tr>
      <?php endforeach; ?>
      </tbody>
    </table>
  </div>
</body>
</html>