View Code - 20260721

karigar_advance_manage.php | 20260721_154032.php Initial Bulk Import

<?php
/* =============================================================================
   File: /erp/karigar_advance_manage.php
   Title: Karigar Advance — Add • Edit • Delete • Filter
   Scope: Page-local only (NO global/base edits). Multi-company aware.
   ============================================================================= */

header('X-Frame-Options: SAMEORIGIN');
error_reporting(E_ALL); ini_set('display_errors','1');

/* ---------- BOOTSTRAP + AUTH ---------- */
require __DIR__ . '/modules/auth/auth.php';
require_login();
require_once __DIR__ . '/modules/activity/activity_logger.php';
$u          = auth_user();
$COMPANY_ID = (int)($u['company_id'] ?? 0);
$USER_ID    = (int)($u['id'] ?? 0);
$pdo        = $GLOBALS['pdo'] ?? null;
if (!$pdo) { require __DIR__ . '/core/db.php'; }

/* ---------- Session + CSRF ---------- */
if (session_status() !== PHP_SESSION_ACTIVE) session_start();
if (empty($_SESSION['csrf'])) $_SESSION['csrf'] = bin2hex(random_bytes(16));
function csrf_field(){ echo '<input type="hidden" name="csrf" value="'.htmlspecialchars($_SESSION['csrf']).'">'; }
function check_csrf(){ if (($_POST['csrf'] ?? '') !== ($_SESSION['csrf'] ?? '')) { http_response_code(400); exit('Bad CSRF'); } }

/* ---------- Helpers ---------- */
function j($s){ return htmlspecialchars((string)$s, ENT_QUOTES, 'UTF-8'); }
function money0($n){ return number_format((float)$n, 2, '.', ''); }
function redirect_self($extra=''){
  $q = $_GET;
  if ($extra) parse_str($extra, $tmp);
  if (!empty($tmp)) $q = array_merge($q, $tmp);
  $url = strtok($_SERVER['REQUEST_URI'],'?');
  if (!empty($q)) $url .= '?'.http_build_query($q);
  header("Location: $url"); exit;
}

/* ---------- Idempotent schema (LOCAL only) ---------- */
$pdo->exec("
CREATE TABLE IF NOT EXISTS karigar_advance (
  id               BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  company_id       BIGINT UNSIGNED NOT NULL,
  karigar_id       BIGINT UNSIGNED NOT NULL,
  amount           DECIMAL(12,2)   NOT NULL DEFAULT 0,
  date             DATE            NOT NULL,
  remark           VARCHAR(255)    NULL,
  deduct_parts     INT             NOT NULL DEFAULT 1,
  remaining_amount DECIMAL(12,2)   NOT NULL DEFAULT 0,
  status           ENUM('OPEN','CLOSED') NOT NULL DEFAULT 'OPEN',
  created_by       BIGINT UNSIGNED NOT NULL,
  created_at       TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at       TIMESTAMP       NULL DEFAULT NULL,
  INDEX(company_id), INDEX(karigar_id), INDEX(status), INDEX(date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
");

/* ---------- Load karigars (dropdown) ---------- */
$karStmt = $pdo->prepare("
  SELECT id, COALESCE(karigar_name,'') AS name, COALESCE(khata_code,'') AS code, COALESCE(entry_name,'') AS extra
  FROM loom_karigar_master
  WHERE company_id = ? AND (is_active = 1 OR is_active IS NULL)
  ORDER BY name ASC
");
$karStmt->execute([$COMPANY_ID]);
$KARIGARS = $karStmt->fetchAll(PDO::FETCH_ASSOC);

/* ---------- POST handling (create/update/delete) BEFORE including header ---------- */
$flash = ''; $err = '';

if ($_SERVER['REQUEST_METHOD'] === 'POST') {
  $posted_f_kar = $_POST['f_kar'] ?? '';
}

if (($_POST['action'] ?? '') === 'create') {
  check_csrf();
  try{
    $karigar_id  = (int)($_POST['karigar_id'] ?? 0);
    $amount      = (float)($_POST['amount'] ?? 0);
    $date        = trim($_POST['date'] ?? '');
    $remark      = trim($_POST['remark'] ?? '');
    $deduct_parts= max(1, (int)($_POST['deduct_parts'] ?? 1));
    $status      = (($_POST['status'] ?? 'OPEN') === 'CLOSED') ? 'CLOSED' : 'OPEN';

if ($karigar_id <= 0) throw new Exception('Karigar required');
    if ($date === '') throw new Exception('Date required');
    if ($amount <= 0) throw new Exception('Amount must be > 0');

$remaining = $amount;
    $ins = $pdo->prepare("
      INSERT INTO karigar_advance
      (company_id, karigar_id, amount, date, remark, deduct_parts, remaining_amount, status, created_by, updated_at)
      VALUES (?,?,?,?,?,?,?,?,?, NOW())
    ");
    $ins->execute([$COMPANY_ID, $karigar_id, $amount, $date, $remark, $deduct_parts, $remaining, $status, $USER_ID]);

activity_log([
      'company_id'  => $COMPANY_ID,
      'user_id'     => $USER_ID,
      'module'      => 'karigar',
      'action_name' => 'create',
      'entity_type' => 'karigar_advance',
      'entity_id'   => (int)$pdo->lastInsertId(),
      'remarks'     => 'Created karigar advance'
    ]);

$_SESSION['flash_ok'] = 'Advance added.';
    $qs = $posted_f_kar ? 'f_kar='.urlencode($posted_f_kar) : '';
    redirect_self($qs);
  }catch(Exception $e){
    $_SESSION['flash_err'] = $e->getMessage();
    redirect_self();
  }
}

if (($_POST['action'] ?? '') === 'update') {
  check_csrf();
  try{
    $id               = (int)($_POST['id'] ?? 0);
    $karigar_id       = (int)($_POST['karigar_id'] ?? 0);
    $amount           = (float)($_POST['amount'] ?? 0);
    $date             = trim($_POST['date'] ?? '');
    $remark           = trim($_POST['remark'] ?? '');
    $deduct_parts     = max(1, (int)($_POST['deduct_parts'] ?? 1));
    $remaining_amount = max(0.0, (float)($_POST['remaining_amount'] ?? 0));
    $status           = (($_POST['status'] ?? 'OPEN') === 'CLOSED') ? 'CLOSED' : 'OPEN';

if ($id <= 0) throw new Exception('Invalid row');
    if ($karigar_id <= 0) throw new Exception('Karigar required');
    if ($date === '') throw new Exception('Date required');
    if ($amount <= 0) throw new Exception('Amount must be > 0');

$up = $pdo->prepare("
      UPDATE karigar_advance
      SET karigar_id=?, amount=?, date=?, remark=?, deduct_parts=?, remaining_amount=?, status=?, updated_at = NOW()
      WHERE id=? AND company_id=?
    ");
    $up->execute([$karigar_id, $amount, $date, $remark, $deduct_parts, $remaining_amount, $status, $id, $COMPANY_ID]);

activity_log([
      'company_id'  => $COMPANY_ID,
      'user_id'     => $USER_ID,
      'module'      => 'karigar',
      'action_name' => 'edit',
      'entity_type' => 'karigar_advance',
      'entity_id'   => $id,
      'remarks'     => 'Updated karigar advance'
    ]);

$_SESSION['flash_ok'] = 'Advance updated.';
    $qs = $posted_f_kar ? 'f_kar='.urlencode($posted_f_kar) : '';
    redirect_self($qs);
  }catch(Exception $e){
    $_SESSION['flash_err'] = $e->getMessage();
    redirect_self();
  }
}

if (($_POST['action'] ?? '') === 'delete') {
  check_csrf();
  try{
    $id = (int)($_POST['id'] ?? 0);
    if ($id <= 0) throw new Exception('Invalid row');
    $del = $pdo->prepare("DELETE FROM karigar_advance WHERE id=? AND company_id=?");
    $del->execute([$id, $COMPANY_ID]);

activity_log([
      'company_id'  => $COMPANY_ID,
      'user_id'     => $USER_ID,
      'module'      => 'karigar',
      'action_name' => 'delete',
      'entity_type' => 'karigar_advance',
      'entity_id'   => $id,
      'remarks'     => 'Deleted karigar advance'
    ]);

$_SESSION['flash_ok'] = 'Advance deleted.';
    $qs = $posted_f_kar ? 'f_kar='.urlencode($posted_f_kar) : '';
    redirect_self($qs);
  }catch(Exception $e){
    $_SESSION['flash_err'] = $e->getMessage();
    redirect_self();
  }
}

/* ---------- Filters ---------- */
$F_kar   = isset($_GET['f_kar']) ? (int)$_GET['f_kar'] : 0;
$F_stat  = isset($_GET['f_stat']) ? $_GET['f_stat'] : '';
$F_month = isset($_GET['f_month'])? $_GET['f_month'] : ''; // YYYY-MM

/* ---------- Fetch rows ---------- */
$where = ["ka.company_id = :cid"];
$params = [':cid' => $COMPANY_ID];
if ($F_kar > 0) { $where[] = "ka.karigar_id = :kar"; $params[':kar'] = $F_kar; }
if ($F_stat === 'OPEN' || $F_stat === 'CLOSED') { $where[] = "ka.status = :st"; $params[':st'] = $F_stat; }
if ($F_month) { $where[] = "DATE_FORMAT(ka.date,'%Y-%m') = :mon"; $params[':mon'] = $F_month; }

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

$sql = "
SELECT ka.*, lkm.karigar_name AS karigar_name, lkm.khata_code
FROM karigar_advance ka
LEFT JOIN loom_karigar_master lkm
  ON lkm.id = ka.karigar_id AND lkm.company_id = ka.company_id
WHERE $whereSql
ORDER BY ka.date DESC, ka.id DESC
LIMIT 500
";
$stmt = $pdo->prepare($sql);
$stmt->execute($params);
$ROWS = $stmt->fetchAll(PDO::FETCH_ASSOC);

/* ---------- Prepare editRow (ensure select presence) ---------- */
$editRow = null;
if (isset($_GET['edit'])) {
  $eid = (int)$_GET['edit'];
  $st = $pdo->prepare("SELECT * FROM karigar_advance WHERE id=? AND company_id=?");
  $st->execute([$eid, $COMPANY_ID]);
  $editRow = $st->fetch(PDO::FETCH_ASSOC) ?: null;
}
if ($editRow) {
  $editKarId = (int)$editRow['karigar_id'];
  $present = false;
  foreach ($KARIGARS as $k) { if ((int)$k['id'] === $editKarId) { $present = true; break; } }
  if (!$present && $editKarId > 0) {
    $q = $pdo->prepare("SELECT id, COALESCE(karigar_name,'') AS name, COALESCE(khata_code,'') AS code, COALESCE(entry_name,'') AS extra FROM loom_karigar_master WHERE id = ? AND company_id = ?");
    $q->execute([$editKarId, $COMPANY_ID]);
    if ($found = $q->fetch(PDO::FETCH_ASSOC)) {
      array_unshift($KARIGARS, $found);
    } else {
      array_unshift($KARIGARS, ['id'=>$editKarId,'name'=>$editRow['karigar_name'],'code'=>'','extra'=>'']);
    }
  }
}

/* ---------- Now include header/footer and render ---------- */
require_once __DIR__ . '/partials/header.php';
?>
<!doctype html>
<html lang="en">
<head>
  <meta charset="utf-8">
  <title>Karigar Advance</title>
  <meta name="viewport" content="width=device-width, initial-scale=1">
  <style>
    /* Minimal local styles — main.css will handle general look */
    .wrap{max-width:1100px;margin:18px auto;padding:0 12px;}
    .card{background:#fff;border:1px solid #e5e7eb;border-radius:12px;margin-bottom:16px;}
    .card h3{margin:0;padding:12px 16px;border-bottom:1px solid #eef2f7;}
    .pad{padding:12px 16px;}
    .row{display:flex;gap:12px;flex-wrap:wrap;}
    .row .col{flex:1 1 220px;}
    label{display:block;font-size:12px;color:#374151;margin-bottom:4px;}
    input[type="text"],input[type="number"],input[type="date"],select,textarea{width:100%;padding:8px 10px;border:1px solid #d1d5db;border-radius:8px;box-sizing:border-box;background:#fff;}
    .btn{display:inline-block;padding:8px 12px;border-radius:8px;background:#0f766e;color:#fff;border:1px solid #0f766e;text-decoration:none}
    .btn.sec{background:#fff;color:#0f766e}
    .btn.danger{background:#b91c1c;color:#fff;border-color:#b91c1c}
    .notice{margin:12px 16px;padding:10px;border-radius:6px;}
    .notice--success{background:#ecfdf5;border:1px solid #bbf7d0;color:#065f46}
    .notice--error{background:#fff1f2;border:1px solid #fecaca;color:#991b1b}
    .table{width:100%;border-collapse:collapse}
    .table th,.table td{padding:8px 10px;border-bottom:1px solid #f0f3f7;text-align:left}
  </style>
</head>
<body>
<div class="wrap">

<?php if (!empty($_SESSION['flash_ok'])): ?>
    <div class="notice notice--success"><?= j($_SESSION['flash_ok']) ?></div>
    <?php unset($_SESSION['flash_ok']); endif; ?>
  <?php if (!empty($_SESSION['flash_err'])): ?>
    <div class="notice notice--error"><?= j($_SESSION['flash_err']) ?></div>
    <?php unset($_SESSION['flash_err']); endif; ?>

<div class="card">
    <h3>Add / Edit</h3>
    <div class="pad">
      <form method="post" class="row" autocomplete="off">
        <?php csrf_field(); ?>
        <input type="hidden" name="action" value="<?= $editRow ? 'update' : 'create' ?>">
        <?php if ($editRow): ?><input type="hidden" name="id" value="<?= (int)$editRow['id'] ?>"><?php endif; ?>
        <input type="hidden" name="f_kar" value="<?= j($F_kar) ?>">

<div class="col">
          <label>Karigar</label>
          <select name="karigar_id" required>
            <option value="">Select…</option>
            <?php foreach($KARIGARS as $k):
              $sel = ($editRow && (int)$editRow['karigar_id'] === (int)$k['id']) ? 'selected' : '';
              $label = trim(($k['name']?:'').' '.($k['code']?('['.$k['code'].']'):'').' '.($k['extra']?('— '.$k['extra']):''));
            ?>
              <option value="<?= (int)$k['id'] ?>" <?= $sel ?>><?= j($label) ?></option>
            <?php endforeach; ?>
          </select>
        </div>

<div class="col">
          <label>Amount</label>
          <input type="number" step="0.01" min="0" name="amount" required value="<?= j($editRow['amount'] ?? '') ?>">
        </div>

<div class="col">
          <label>Deduct Parts</label>
          <input type="number" step="1" min="1" name="deduct_parts" value="<?= j($editRow['deduct_parts'] ?? 1) ?>">
        </div>

<div class="col">
          <label>Status</label>
          <select name="status">
            <?php $st = $editRow['status'] ?? 'OPEN'; ?>
            <option value="OPEN" <?= $st === 'OPEN' ? 'selected' : '' ?>>OPEN</option>
            <option value="CLOSED" <?= $st === 'CLOSED' ? 'selected' : '' ?>>CLOSED</option>
          </select>
        </div>

<div class="col" style="flex:1 1 100%;">
          <label>Remark</label>
          <input type="text" name="remark" value="<?= j($editRow['remark'] ?? '') ?>">
        </div>

<?php if ($editRow): ?>
          <div class="col">
            <label>Remaining Amount</label>
            <input type="number" step="0.01" min="0" name="remaining_amount" value="<?= j(money0($editRow['remaining_amount'])) ?>">
          </div>
        <?php endif; ?>

<div class="col" style="align-self:end">
          <button class="btn" type="submit"><?= $editRow ? 'Update' : 'Add' ?></button>
          <?php if ($editRow): ?><a class="btn sec" href="<?= strtok($_SERVER['REQUEST_URI'],'?') ?>">Cancel</a><?php endif; ?>
        </div>
      </form>
    </div>

<div class="pad" style="border-top:1px solid #f3f6f8">
      <form method="get" class="row" style="align-items:end;">
        <div class="col">
          <label>Filter — Karigar</label>
          <select name="f_kar">
            <option value="0">All</option>
            <?php foreach($KARIGARS as $k): $sel = ($F_kar > 0 && $F_kar === (int)$k['id']) ? 'selected' : ''; ?>
              <option value="<?= (int)$k['id'] ?>" <?= $sel ?>><?= j(trim($k['name'].' '.($k['code']?('['.$k['code'].']'):''))) ?></option>
            <?php endforeach; ?>
          </select>
        </div>

<div class="col">
          <label>Status</label>
          <select name="f_stat">
            <option value="">All</option>
            <option value="OPEN" <?= $F_stat === 'OPEN' ? 'selected' : '' ?>>OPEN</option>
            <option value="CLOSED" <?= $F_stat === 'CLOSED' ? 'selected' : '' ?>>CLOSED</option>
          </select>
        </div>

<div class="col">
          <label>Month (YYYY-MM)</label>
          <input type="month" name="f_month" value="<?= j($F_month) ?>">
        </div>

<div class="col">
          <button class="btn" type="submit">Apply</button>
          <a class="btn sec" href="<?= strtok($_SERVER['REQUEST_URI'],'?') ?>">Reset</a>
        </div>
      </form>
    </div>
  </div>

<div class="card">
    <h3>Advances (max 500)</h3>
    <div class="pad" style="overflow:auto">
      <table class="table">
        <thead>
          <tr>
            <th>#</th><th>Date</th><th>Karigar</th><th>Amount</th><th>Deduct Parts</th><th>Remaining</th><th>Status</th><th>Remark</th><th>Created By</th><th>Created At</th><th>Actions</th>
          </tr>
        </thead>
        <tbody>
        <?php if (!$ROWS): ?>
          <tr><td colspan="11" style="padding:12px">No rows.</td></tr>
        <?php else: foreach($ROWS as $r): ?>
          <tr>
            <td><?= (int)$r['id'] ?></td>
            <td><?= j($r['date']) ?></td>
            <td><?= j(($r['karigar_name']?:'').' '.($r['khata_code']?('['.$r['khata_code'].']'):'') ) ?></td>
            <td><?= j(money0($r['amount'])) ?></td>
            <td><?= (int)$r['deduct_parts'] ?></td>
            <td><?= j(money0($r['remaining_amount'])) ?></td>
            <td><?= j($r['status']) ?></td>
            <td><?= j($r['remark']) ?></td>
            <td><?= (int)$r['created_by'] ?></td>
            <td><?= j($r['created_at']) ?></td>
            <td>
              <a class="btn sec" href="?edit=<?= (int)$r['id'] ?>">Edit</a>
              <form method="post" onsubmit="return confirm('Delete this advance?');" style="display:inline">
                <?php csrf_field(); ?>
                <input type="hidden" name="action" value="delete">
                <input type="hidden" name="id" value="<?= (int)$r['id'] ?>">
                <button class="btn danger" type="submit">Delete</button>
              </form>
            </td>
          </tr>
        <?php endforeach; endif; ?>
        </tbody>
      </table>
    </div>
  </div>

<div style="text-align:center;color:#6b7280;font-size:12px;padding-bottom:18px">
    Company: <?= $COMPANY_ID ?> • User: <?= $USER_ID ?>
  </div>

</div>

<?php
require_once __DIR__ . '/partials/footer.php';
?>