View Code - 20260721

karigar_extra_manage.php | 20260721_154032.php Initial Bulk Import

<?php
/* =============================================================================
   File: /erp/karigar_extra_manage.php
   Title: Karigar Extra (+) — Add • Edit • Delete • Filter
   Scope: Page-local only (NO global/base edits). Multi-company aware.
   Notes: Karigars loaded from loom_karigar_master (active only).
   ============================================================================= */

/* ---------- 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'; }

/* ---------- dev errors ---------- */
error_reporting(E_ALL); ini_set('display_errors','1');

/* ---------- 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'); } }

/* ---------- Idempotent schema (LOCAL) - includes updated_at ---------- */
$pdo->exec("
CREATE TABLE IF NOT EXISTS karigar_extra (
  id             BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  company_id     BIGINT UNSIGNED NOT NULL,
  karigar_id     BIGINT UNSIGNED NOT NULL,
  karigar_name   VARCHAR(191)    NOT NULL,
  entry_date     DATE            NOT NULL,
  amount         DECIMAL(12,2)   NOT NULL DEFAULT 0,
  notes          VARCHAR(255)    NULL,
  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(entry_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
");

/* ---------- 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;
}

/* ---------- Load karigars for selects ---------- */
$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);
$KAR_BY_ID = [];
foreach($KARIGARS as $k){ $KAR_BY_ID[(int)$k['id']] = $k; }

/* ---------- POST handling BEFORE header (preserve any posted filter state) ---------- */
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
  $posted_f_kar = $_POST['f_kar'] ?? '';
}

$flash = ''; $err = '';

if (($_POST['action'] ?? '') === 'create') {
  check_csrf();
  try {
    $karigar_id = (int)($_POST['karigar_id'] ?? 0);
    $entry_date = trim($_POST['entry_date'] ?? '');
    $amount     = (float)($_POST['amount'] ?? 0);
    $notes      = trim($_POST['notes'] ?? '');

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

// Prefer master name
    $st = $pdo->prepare("SELECT COALESCE(karigar_name,'') AS name FROM loom_karigar_master WHERE id = ? AND company_id = ? LIMIT 1");
    $st->execute([$karigar_id, $COMPANY_ID]);
    $row = $st->fetch(PDO::FETCH_ASSOC);
    $kar_name = ($row && $row['name'] !== '') ? $row['name'] : ($KAR_BY_ID[$karigar_id]['name'] ?? 'Karigar#'.$karigar_id);

$ins = $pdo->prepare("
      INSERT INTO karigar_extra
      (company_id, karigar_id, karigar_name, entry_date, amount, notes, created_by, updated_at)
      VALUES (?,?,?,?,?,?,?, NOW())
    ");
    $ins->execute([$COMPANY_ID, $karigar_id, $kar_name, $entry_date, $amount, $notes, $USER_ID]);

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

$_SESSION['flash_ok'] = 'Extra 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);
    $entry_date = trim($_POST['entry_date'] ?? '');
    $amount     = (float)($_POST['amount'] ?? 0);
    $notes      = trim($_POST['notes'] ?? '');

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

$st = $pdo->prepare("SELECT COALESCE(karigar_name,'') AS name FROM loom_karigar_master WHERE id = ? AND company_id = ? LIMIT 1");
    $st->execute([$karigar_id, $COMPANY_ID]);
    $row = $st->fetch(PDO::FETCH_ASSOC);
    $kar_name = ($row && $row['name'] !== '') ? $row['name'] : ($KAR_BY_ID[$karigar_id]['name'] ?? 'Karigar#'.$karigar_id);

$up = $pdo->prepare("
      UPDATE karigar_extra
      SET karigar_id=?, karigar_name=?, entry_date=?, amount=?, notes=?, updated_at = NOW()
      WHERE id=? AND company_id=?
    ");
    $up->execute([$karigar_id, $kar_name, $entry_date, $amount, $notes, $id, $COMPANY_ID]);

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

$_SESSION['flash_ok'] = 'Extra 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_extra 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_extra',
            'entity_id'   => $id,
            'remarks'     => 'Deleted karigar extra'
        ]);

$_SESSION['flash_ok'] = 'Extra 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_month = isset($_GET['f_month']) ? $_GET['f_month'] : '';

/* ---------- Fetch rows with filters ---------- */
$where = ["ex.company_id = :cid"];
$params = [':cid' => $COMPANY_ID];
if ($F_kar > 0) { $where[] = "ex.karigar_id = :kar"; $params[':kar'] = $F_kar; }
if ($F_month)   { $where[] = "DATE_FORMAT(ex.entry_date,'%Y-%m') = :mon"; $params[':mon'] = $F_month; }
$whereSql = implode(' AND ', $where);

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

/* ---------- Prepare editRow so select includes current karigar ---------- */
$editRow = null;
if (isset($_GET['edit'])) {
  $eid = (int)$_GET['edit'];
  $st = $pdo->prepare("SELECT * FROM karigar_extra WHERE id=? AND company_id=?");
  $st->execute([$eid, $COMPANY_ID]);
  $editRow = $st->fetch(PDO::FETCH_ASSOC) ?: null;
}
if ($editRow) {
  $editKarId = (int)$editRow['karigar_id'];
  if ($editKarId > 0 && !isset($KAR_BY_ID[$editKarId])) {
    $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]);
    $found = $q->fetch(PDO::FETCH_ASSOC);
    if ($found) {
      array_unshift($KARIGARS, $found);
      $KAR_BY_ID[(int)$found['id']] = $found;
    } else {
      array_unshift($KARIGARS, ['id'=>$editKarId,'name'=>$editRow['karigar_name'],'code'=>'','extra'=>'']);
      $KAR_BY_ID[$editKarId] = ['id'=>$editKarId,'name'=>$editRow['karigar_name'],'code'=>'','extra'=>''];
    }
  }
}

/* ---------- Now include header (safe) ---------- */
require_once __DIR__ . '/partials/header.php';
?>
<!doctype html>
<html lang="en">
<head>
  <meta charset="utf-8">
  <title>Karigar Extra (+)</title>
  <meta name="viewport" content="width=device-width, initial-scale=1">
 
</head>
<body>
<div class="container-fluid py-4">

<?php if (!empty($_SESSION['flash_ok'])): ?>
        <div class="alert alert-success alert-dismissible fade show" role="alert">
            <?= j($_SESSION['flash_ok']) ?>
            <button type="button" class="btn-close" data-bs-dismiss="alert" aria-label="Close"></button>
        </div>
        <?php unset($_SESSION['flash_ok']); ?>
    <?php endif; ?>

<?php if (!empty($_SESSION['flash_err'])): ?>
        <div class="alert alert-danger alert-dismissible fade show" role="alert">
            <?= j($_SESSION['flash_err']) ?>
            <button type="button" class="btn-close" data-bs-dismiss="alert" aria-label="Close"></button>
        </div>
        <?php unset($_SESSION['flash_err']); ?>
    <?php endif; ?>

<div class="col-md-4">
                    <label class="form-label">Karigar</label>
                    <select name="karigar_id" class="form-select" 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-md-4">
                    <label class="form-label">Entry Date</label>
                    <input type="date" name="entry_date" class="form-control" value="<?= j($editRow['entry_date'] ?? date('Y-m-d')) ?>" required>
                </div>

<div class="col-md-4">
                    <label class="form-label">Amount</label>
                    <div class="input-group">
                        <span class="input-group-text">₹</span>
                        <input type="number" step="0.01" min="0" name="amount" class="form-control" value="<?= j($editRow['amount'] ?? '') ?>" required>
                    </div>
                </div>

<div class="col-12">
                    <label class="form-label">Notes</label>
                    <input type="text" name="notes" class="form-control" placeholder="Optional remarks..." value="<?= j($editRow['notes'] ?? '') ?>">
                </div>

<div class="col-12 mt-4">
                    <button class="btn btn-primary" type="submit text-uppercase">
                        <i class="bi bi-check-lg"></i> <?= $editRow ? 'Update Entry' : 'Add Entry' ?>
                    </button>
                    <?php if ($editRow): ?>
                        <a class="btn btn-outline-secondary ms-2" href="<?= strtok($_SERVER['REQUEST_URI'],'?') ?>">Cancel</a>
                    <?php endif; ?>
                </div>
            </form>
        </div>

<div class="card-footer bg-light border-top">
            <form method="get" class="row g-2 align-items-end">
                <div class="col-md-4 col-lg-3">
                    <label class="form-label small fw-bold text-muted">Filter Karigar</label>
                    <select name="f_kar" class="form-select form-select-sm">
                        <option value="0">All Karigars</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-md-3 col-lg-2">
                    <label class="form-label small fw-bold text-muted">Month</label>
                    <input type="month" name="f_month" value="<?= j($F_month) ?>" class="form-select form-select-sm">
                </div>

<div class="col-md-4">
                    <button class="btn btn-sm btn-dark" type="submit">Apply Filter</button>
                    <a class="btn btn-sm btn-link text-decoration-none" href="<?= strtok($_SERVER['REQUEST_URI'],'?') ?>">Reset</a>
                </div>
            </form>
        </div>
    </div>

<div class="card shadow-sm">
        <div class="card-header bg-white d-flex justify-content-between align-items-center">
            <h5 class="mb-0">Extra Entries <small class="text-muted fw-normal">(Max 500)</small></h5>
        </div>
        <div class="table-responsive">
            <table class="table table-hover align-middle mb-0">
                <thead class="table-light">
                    <tr>
                        <th class="ps-3">#</th>
                        <th>Date</th>
                        <th>Karigar</th>
                        <th class="text-end">Amount</th>
                        <th>Notes</th>
                        <th>Info</th>
                        <th class="text-center">Actions</th>
                    </tr>
                </thead>
                <tbody>
                <?php if (!$ROWS): ?>
                    <tr><td colspan="7" class="text-center py-4 text-muted">No entries found for the selected filters.</td></tr>
                <?php else: foreach ($ROWS as $r): ?>
                    <tr>
                        <td class="ps-3 text-muted"><?= (int)$r['id'] ?></td>
                        <td class="fw-medium"><?= j(date('d-M-Y', strtotime($r['entry_date']))) ?></td>
                        <td>
                            <span class="d-block fw-bold"><?= j($r['karigar_name']) ?></span>
                            <small class="text-muted"><?= j($r['khata_code'] ? '['.$r['khata_code'].']' : '') ?></small>
                        </td>
                        <td class="text-end fw-bold text-dark"><?= j(money0($r['amount'])) ?></td>
                        <td class="text-wrap" style="max-width: 200px;"><?= j($r['notes']) ?></td>
                        <td>
                            <small class="d-block text-muted" style="font-size: 0.75rem;">
                                By: User ID <?= (int)$r['created_by'] ?><br>
                                On: <?= j($r['created_at']) ?>
                            </small>
                        </td>
                        <td class="text-center">
                            <div class="btn-group btn-group-sm">
                                <a class="btn btn-outline-primary" href="?edit=<?= (int)$r['id'] ?>&<?= ($F_kar?('f_kar='.urlencode($F_kar).'&'):'') ?><?= ($F_month?('f_month='.urlencode($F_month).'&'):'') ?>">
                                    Edit
                                </a>
                                <form method="post" onsubmit="return confirm('Permanently delete this entry?');" style="display:inline">
                                    <?php csrf_field(); ?>
                                    <input type="hidden" name="action" value="delete">
                                    <input type="hidden" name="id" value="<?= (int)$r['id'] ?>">
                                    <input type="hidden" name="f_kar" value="<?= j($F_kar) ?>">
                                    <button class="btn btn-outline-danger" type="submit">Delete</button>
                                </form>
                            </div>
                        </td>
                    </tr>
                <?php endforeach; endif; ?>
                </tbody>
            </table>
        </div>
    </div>

<div class="mt-4 text-center text-muted small">
        <span class="badge bg-light text-dark border">Company ID: <?= $COMPANY_ID ?></span>
        <span class="badge bg-light text-dark border">User ID: <?= $USER_ID ?></span>
    </div>

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