View Code - 20260721

pasaria_rates.php | 20260721_154033.php Initial Bulk Import

<?php
/* =========================================================================
   File: /erp/pasaria_rates.php
   Purpose: Set Rates for Pasaria Types (pulled from pasaria_types)
   Scope : Page-only (no global/base edits)
   ========================================================================= */
error_reporting(E_ALL); ini_set('display_errors', 1);

/* --- DB + Auth (page-local) --- */
if (!isset($pdo) || !($pdo instanceof PDO)) { 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'];

/* --- Helpers --- */
function h($s){ return htmlspecialchars((string)$s, ENT_QUOTES, 'UTF-8'); }
function idx_exists(PDO $pdo,string $t,string $idx):bool{
  try{
    $db=$pdo->query("SELECT DATABASE()")->fetchColumn();
    $st=$pdo->prepare("SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA=? AND TABLE_NAME=? AND INDEX_NAME=? LIMIT 1");
    $st->execute([$db,$t,$idx]); return (bool)$st->fetchColumn();
  }catch(Throwable $e){ return false; }
}

/* --- Ensure RATES table (matches your screenshot) ------------------------- */
$pdo->exec("CREATE TABLE IF NOT EXISTS pasaria_rates (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  company_id   BIGINT NOT NULL,
  pasaria_type VARCHAR(120) NOT NULL,
  rate         DECIMAL(12,2) NOT NULL DEFAULT 0,
  created_by   BIGINT NULL,
  created_at   TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_pr_company (company_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

if (!idx_exists($pdo,'pasaria_rates','uniq_company_type')) {
  try { $pdo->exec("CREATE UNIQUE INDEX uniq_company_type ON pasaria_rates(company_id, pasaria_type)"); } catch(Throwable $e){}
}

/* --- Handle Bulk Save ---------------------------------------------------- */
$msg=''; $err='';
if ($_SERVER['REQUEST_METHOD']==='POST' && ($_POST['_action'] ?? '')==='save_all') {
  $rates = $_POST['rate'] ?? [];  // array: [pasaria_type => 'number or empty']
  if (!is_array($rates)) $rates = [];

try{
    $pdo->beginTransaction();

// Existing rows (for quick upsert/delete)
    $st=$pdo->prepare("SELECT pasaria_type, id FROM pasaria_rates WHERE company_id=?");
    $st->execute([$company_id]);
    $existing = [];
    foreach($st->fetchAll(PDO::FETCH_ASSOC) as $r){ $existing[$r['pasaria_type']] = (int)$r['id']; }

foreach ($rates as $type => $val) {
      $type = trim((string)$type);
      if ($type==='') continue;

$val = trim((string)$val);
      if ($val==='') {
        // Delete if exists
        if (isset($existing[$type])) {
          $del=$pdo->prepare("DELETE FROM pasaria_rates WHERE id=? AND company_id=?");
          $del->execute([$existing[$type], $company_id]);
        }
        continue;
      }
      if (!is_numeric($val) || (float)$val < 0) { $err = "Invalid rate for type: ".h($type); break; }

$val = number_format((float)$val, 2, '.', '');

if (isset($existing[$type])) {
        $up=$pdo->prepare("UPDATE pasaria_rates SET rate=?, created_by=? WHERE id=? AND company_id=?");
        $up->execute([$val, $user_id, $existing[$type], $company_id]);
      } else {
        $ins=$pdo->prepare("INSERT INTO pasaria_rates (company_id,pasaria_type,rate,created_by) VALUES (?,?,?,?)");
        $ins->execute([$company_id, $type, $val, $user_id]);
      }
    }

if (!$err) { $pdo->commit(); $msg='Rates saved.'; }
    else { $pdo->rollBack(); }
  }catch(Throwable $e){
    if ($pdo->inTransaction()) $pdo->rollBack();
    if (strpos($e->getMessage(),'uniq_company_type')!==false) {
      $err='Duplicate type found while saving. Refresh and try again.';
    } else {
      $err='Save failed: '.$e->getMessage();
    }
  }
}

/* --- Load Types (master) + join current rates ---------------------------- */
/* NOTE: Force collation in JOIN so even if columns differ (general_ci vs unicode_ci)
         the comparison still works. */
$rows=[];
try{
  $sql = "
    SELECT
      t.pasaria_type,
      t.description,
      t.is_active,
      t.sort_order,
      r.id   AS rate_id,
      r.rate AS current_rate,
      r.created_at
    FROM pasaria_types t
    LEFT JOIN pasaria_rates r
      ON r.company_id = t.company_id
     AND r.pasaria_type COLLATE utf8mb4_unicode_ci
         = t.pasaria_type COLLATE utf8mb4_unicode_ci
   WHERE t.company_id = ?
   ORDER BY t.is_active DESC, t.sort_order ASC, t.pasaria_type ASC";
  $st=$pdo->prepare($sql); $st->execute([$company_id]);
  $rows=$st->fetchAll(PDO::FETCH_ASSOC);
}catch(Throwable $e){ $err = 'Load failed: '.$e->getMessage(); }

/* --- Optional header include -------------------------------------------- */
$__header = __DIR__ . '/partials/header.php';
if (is_file($__header)) { $PAGE='pasaria_rates'; include_once $__header; }
?>
<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8"><meta name="viewport" content="width=device-width, initial-scale=1">
<title>Pasaria Rates</title>
<style>
:root{
  --primary:#34A853; --bg:#F5FFF7; --text:#202124; --muted:#5F6368;
  --card:#fff; --border:#e5e7eb; --shadow:0 8px 20px rgba(0,0,0,.06);
}
*{box-sizing:border-box}
body{margin:0;background:var(--bg);color:var(--text);font:14px system-ui,-apple-system,Segoe UI,Roboto,Arial}
.wrap{max-width:1000px;margin:18px auto;padding:12px}
h1{margin:0 0 10px;font-size:20px}
.card{background:var(--card);border:1px solid var(--border);border-radius:14px;box-shadow:var(--shadow);padding:16px}
.nav{display:flex;gap:8px;flex-wrap:wrap;margin-bottom:12px}
.chip{display:inline-flex;align-items:center;gap:8px;padding:6px 10px;border:1px solid var(--border);border-radius:999px;background:#fff}
.chip a{text-decoration:none;color:var(--text)}
.btn{display:inline-block;padding:8px 12px;border-radius:10px;background:var(--primary);color:#fff;text-decoration:none;font-weight:700;border:0;cursor:pointer}
.btn-ghost{background:#f1f3f4;color:#111}
form.inline{display:flex;gap:8px;flex-wrap:wrap}
input,select{padding:8px 10px;border:1px solid #d0d7de;border-radius:10px;background:#fff}
table{width:100%;border-collapse:collapse;margin-top:12px;background:#fff;border-radius:12px;overflow:hidden}
th,td{padding:10px;border-bottom:1px solid #eef2f7;text-align:left;font-size:13px;vertical-align:middle}
th{background:#f8fafc;color:#334155}
.badge{font-size:11px;padding:.15rem .45rem;border-radius:999px;border:1px solid #d0d7de}
.badge-on{background:#e6fcf5;color:#087f5b}
.badge-off{background:#fff5f5;color:#c92a2a}
.msg{margin:10px 0;padding:10px;border-radius:10px}
.ok{background:#e6f4ea;border:1px solid #cde7d7;color:#146c2e}
.err{background:#fdecea;border:1px solid #f5c6cb;color:#7f1d1d}
.small{color:#64748b;font-size:12px}
@media(max-width:720px){ .row{display:block} }
</style>
</head>
<body>
<div class="wrap">
  <div class="nav">
    <div class="chip"><a href="/erp/pasaria_entry.php">← Go to Pasaria Entry</a></div>
    <div class="chip"><a href="/erp/pasaria_types.php">Pasaria Types</a></div>
    <div class="chip"><a href="/erp/pasaria_rates.php"><b>Pasaria Rates</b></a></div>
  </div>

<div class="card">
    <h1>Pasaria Rates</h1>
    <?php if($msg): ?><div class="msg ok"><?=h($msg)?></div><?php endif; ?>
    <?php if($err): ?><div class="msg err"><?=h($err)?></div><?php endif; ?>

<table>
        <thead>
          <tr>
            <th style="width:54px">#</th>
            <th>Pasaria Type</th>
            <th>Description</th>
            <th style="width:120px">Active</th>
            <th style="width:160px">Rate (₹)</th>
            <th class="small" style="width:160px">Existing Row</th>
          </tr>
        </thead>
        <tbody>
          <?php if(!$rows): ?>
            <tr><td colspan="6" class="small" style="text-align:center">No types found. Add from <a href="/erp/pasaria_types.php">Pasaria Types</a>.</td></tr>
          <?php else: foreach($rows as $i=>$r): 
            $type = (string)$r['pasaria_type'];
            $rateVal = isset($_POST['rate'][$type]) ? (string)$_POST['rate'][$type]
                       : ( ($r['current_rate']!==null) ? number_format((float)$r['current_rate'],2,'.','') : '' );
          ?>
            <tr>
              <td><?= $i+1 ?></td>
              <td><b><?= h($type) ?></b></td>
              <td><?= h($r['description'] ?: '') ?></td>
              <td>
                <?php if ((int)$r['is_active']===1): ?>
                  <span class="badge badge-on">Active</span>
                <?php else: ?>
                  <span class="badge badge-off">Inactive</span>
                <?php endif; ?>
              </td>
              <td>
                <input type="text" name="rate[<?= h($type) ?>]" value="<?= h($rateVal) ?>" placeholder="e.g. 12.50">
                <div class="small">खाली छोड़ो ⇒ delete</div>
              </td>
              <td class="small">
                <?= $r['rate_id'] ? ('#'.(int)$r['rate_id'].' @ '.h($r['created_at'])) : '-' ?>
              </td>
            </tr>
          <?php endforeach; endif; ?>
        </tbody>
      </table>

<div style="margin-top:12px;display:flex;gap:10px;flex-wrap:wrap">
        <button class="btn" type="submit">Save Rates</button>
        <a class="btn-ghost" style="padding:8px 12px;border-radius:10px;border:1px solid #d0d7de;text-decoration:none" href="/erp/pasaria_types.php">Manage Types</a>
      </div>
      <div class="small" style="margin-top:6px">
        Note: यह पेज master <b>pasaria_types</b> से list दिखाता है। Rate खाली ⇒ उस type का row <b>pasaria_rates</b> से delete होगा।
      </div>
    </form>
  </div>
</div>
</body>
</html>