View Code - 20260721

taka_delete.php | 20260721_154033.php Initial Bulk Import

<?php
/* =============================================================================
   File: taka_delete.php
   Purpose: Bulk select + delete (archive) for loom_production_entry
   Notes:
     - Page-local only; reuses auth + db from the project
     - Bulk delete archives rows into loom_production_entry_deleted (same schema as other page)
     - CSRF protected, company-scoped, and uses DB transaction for atomicity
     - UI: checkbox per row, select-all, delete selected with optional reason
     - ADDED: full set of filters (id, machine_id, khata_id, quality_id, taka_no, q, date_from, date_to)
     - ADDED: "Delete All Filtered" feature which archives all records matching the current filters.
   ============================================================================= */

error_reporting(E_ALL); ini_set('display_errors', 1);

require __DIR__ . '/modules/auth/auth.php';
require_login();
$u          = auth_user();
$company_id = (int)$u['company_id'];
$user_id    = (int)$u['id'];

$pdo = $GLOBALS['pdo'] ?? null;
if (!$pdo) { require __DIR__ . '/core/db.php'; }

if (session_status() !== PHP_SESSION_ACTIVE) session_start();
if (empty($_SESSION['csrf'])) { $_SESSION['csrf'] = bin2hex(random_bytes(16)); }
$CSRF = $_SESSION['csrf'];

$alerts = [];
$errors = [];

// helper: check table/col existence
function _table_exists(PDO $pdo, $name){ try{ $st=$pdo->prepare("SHOW TABLES LIKE ?"); $st->execute([$name]); return (bool)$st->fetchColumn(); }catch(Throwable $e){ return false; } }
function _col_exists(PDO $pdo,$tbl,$col){ try{ $st=$pdo->prepare("SHOW COLUMNS FROM `$tbl` LIKE ?"); $st->execute([$col]); return (bool)$st->fetch(); }catch(Throwable $e){ return false; } }

// Ensure archive table exists (same DDL as in other page) - tolerant
try {
  $pdo->exec("CREATE TABLE IF NOT EXISTS `loom_production_entry_deleted` (
      `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
      `company_id` BIGINT NOT NULL,
      `original_id` BIGINT NOT NULL,
      `entry_date` date DEFAULT NULL,
      `machine_id` bigint DEFAULT NULL,
      `khata_id` bigint DEFAULT NULL,
      `quality_id` bigint DEFAULT NULL,
      `taka_no` int DEFAULT 0,
      `pbn` int DEFAULT 0,
      `sbn` int DEFAULT 0,
      `extra_mtr` decimal(10,2) DEFAULT 0,
      `lines_json` longtext,
      `meter_total` decimal(10,2) DEFAULT 0,
      `weight` decimal(10,2) DEFAULT 0,
      `remark` varchar(255) DEFAULT NULL,
      `created_by` bigint DEFAULT NULL,
      `created_at` datetime DEFAULT NULL,
      `updated_at` datetime DEFAULT NULL,
      `deleted_by` bigint NOT NULL,
      `deleted_at` datetime NOT NULL,
      `delete_reason` varchar(255) DEFAULT NULL,
      PRIMARY KEY (`id`),
      KEY `idx_company_original` (`company_id`,`original_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;");
} catch(Throwable $e){ $errors[] = "Archive ensure failed: ".$e->getMessage(); }

// Build a WHERE clause from incoming filter params (used for listing and delete_filtered)
function build_filters_from_input(array $src, array &$params){
  $where = ['company_id = :cid'];
  $params[':cid'] = $src['company_id'];

if (!empty($src['id'])) { $where[] = 'id = :id'; $params[':id'] = (int)$src['id']; }
  if (isset($src['machine_id']) && $src['machine_id'] !== '') { $where[] = 'machine_id = :machine_id'; $params[':machine_id'] = (int)$src['machine_id']; }
  if (isset($src['khata_id']) && $src['khata_id'] !== '')   { $where[] = 'khata_id = :khata_id'; $params[':khata_id'] = (int)$src['khata_id']; }
  if (!empty($src['quality_id'])) { $where[] = 'quality_id = :quality_id'; $params[':quality_id'] = (int)$src['quality_id']; }
  if (isset($src['taka_no']) && $src['taka_no'] !== '') { $where[] = 'taka_no = :taka_no'; $params[':taka_no'] = (int)$src['taka_no']; }

if (!empty($src['date_from'])) {
    $ts = strtotime(str_replace('/','-',$src['date_from'])); if ($ts) { $where[] = 'entry_date >= :df'; $params[':df'] = date('Y-m-d',$ts); }
  }
  if (!empty($src['date_to'])) {
    $ts = strtotime(str_replace('/','-',$src['date_to'])); if ($ts) { $where[] = 'entry_date <= :dt'; $params[':dt'] = date('Y-m-d',$ts); }
  }

if (!empty($src['q'])) {
    $where[] = '(remark LIKE :q OR CAST(id AS CHAR) LIKE :q)'; $params[':q'] = '%'.$src['q'].'%';
  }

return implode(' AND ', $where);
}

// Handle POST bulk delete
if ($_SERVER['REQUEST_METHOD'] === 'POST'){
  try {
    if (pv('csrf') !== $CSRF) throw new Exception('Invalid CSRF token');
    $action = pv('action','');

if ($action === 'bulk_delete'){
      $delete_filtered = pv('delete_filtered','0') === '1';
      $reason = trim((string)pv('delete_reason','')) ?: null;

// Determine IDs to delete
      $ids = [];
      if ($delete_filtered) {
        // Build filters from POST (we copy filter inputs into hidden fields before submit)
        $src = [
          'company_id' => $company_id,
          'id' => pv('filter_id',''),
          'machine_id' => pv('filter_machine_id',''),
          'khata_id' => pv('filter_khata_id',''),
          'quality_id' => pv('filter_quality_id',''),
          'taka_no' => pv('filter_taka_no',''),
          'date_from' => pv('filter_date_from',''),
          'date_to' => pv('filter_date_to',''),
          'q' => pv('filter_q','')
        ];
        $params = [];
        $W = build_filters_from_input($src, $params);
        $sql = "SELECT id FROM loom_production_entry WHERE $W";
        $st = $pdo->prepare($sql);
        $st->execute($params);
        $ids = array_map('intval', $st->fetchAll(PDO::FETCH_COLUMN));
        if (empty($ids)) throw new Exception('No records match the current filters.');

} else {
        $ids_raw = $_POST['ids'] ?? [];
        if (!is_array($ids_raw) || !count($ids_raw)) throw new Exception('No records selected');
        $ids = array_values(array_filter(array_map(function($v){ return intval($v); }, $ids_raw), function($v){ return $v>0; }));
      }

if (empty($ids)) throw new Exception('No valid ids to delete');

// Prepare statements (outside loop)
      $sel = $pdo->prepare("SELECT * FROM loom_production_entry WHERE company_id=? AND id=? LIMIT 1");

$ins = $pdo->prepare("INSERT INTO loom_production_entry_deleted
        (company_id, original_id, entry_date, machine_id, khata_id, quality_id,
         taka_no, pbn, sbn, extra_mtr, lines_json, meter_total, weight, remark,
         created_by, created_at, updated_at, deleted_by, deleted_at, delete_reason)
        VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");

$del = $pdo->prepare("DELETE FROM loom_production_entry WHERE company_id=? AND id=? LIMIT 1");

$pdo->beginTransaction();
      $archived = 0;
      foreach ($ids as $id){
        $sel->execute([$company_id, $id]);
        $row = $sel->fetch(PDO::FETCH_ASSOC);
        if (!$row) continue; // skip missing or not-owned

$src_pbn = array_key_exists('pbn', $row) ? $row['pbn'] : 0;
        $src_sbn = array_key_exists('sbn', $row) ? $row['sbn'] : 0;
        $src_lines = (isset($row['lines_json']) && $row['lines_json'] !== null && $row['lines_json'] !== '') ? $row['lines_json'] : '[]';

$ok1 = $ins->execute([
          (int)$row['company_id'], (int)$row['id'], $row['entry_date'] ?? null,
          $row['machine_id'] ?? null, $row['khata_id'] ?? null, $row['quality_id'] ?? null,
          $row['taka_no'] ?? 0, $src_pbn, $src_sbn, $row['extra_mtr'] ?? 0, $src_lines,
          $row['meter_total'] ?? 0, $row['weight'] ?? 0, $row['remark'] ?? null,
          $row['created_by'] ?? null, $row['created_at'] ?? null, $row['updated_at'] ?? null,
          $user_id, date('Y-m-d H:i:s'), $reason
        ]);
        if (!$ok1) { throw new Exception('Archive insert failed for ID '. $id); }

$del->execute([$company_id, $id]);
        if ($del->rowCount() === 1) $archived++;
      }
      $pdo->commit();
      $alerts[] = "$archived record(s) archived and deleted.";
    }

} catch(Throwable $e){
    if ($pdo && $pdo->inTransaction()) $pdo->rollBack();
    $errors[] = $e->getMessage();
    $alerts[] = 'ERROR: '.$e->getMessage();
  }
}

// Fetch rows (simple list with checkboxes) — respect filters from GET
$filters = [
  'company_id' => $company_id,
  'id' => gv('id',''),
  'machine_id' => gv('machine_id',''),
  'khata_id' => gv('khata_id',''),
  'quality_id' => gv('quality_id',''),
  'taka_no' => gv('taka_no',''),
  'date_from' => gv('date_from',''),
  'date_to' => gv('date_to',''),
  'q' => gv('q','')
];
$params = [];
$W = build_filters_from_input($filters, $params);
$rows = [];
try{
  $sql = "SELECT id, entry_date, machine_id, khata_id, quality_id, taka_no, meter_total, remark FROM loom_production_entry WHERE $W ORDER BY entry_date DESC, id DESC LIMIT 500";
  $st = $pdo->prepare($sql);
  $st->execute($params);
  $rows = $st->fetchAll(PDO::FETCH_ASSOC);
} catch(Throwable $e){ $errors[] = $e->getMessage(); }

// Small lookup maps for nicer UI (reuse if available)
function _fetch_map(PDO $pdo, $sql, $params=[]){ $m=[]; try{ $st=$pdo->prepare($sql); $st->execute($params); foreach($st as $r){ $m[(int)$r['id']] = (string)($r['name'] ?? $r['id']); } }catch(Throwable$e){} return $m; }
$MACHINES=[]; if (_table_exists($pdo,'machines')){
  $col = _col_exists($pdo,'machines','machine_no')? 'machine_no' : (_col_exists($pdo,'machines','mc_no')?'mc_no':null);
  if ($col) $MACHINES = _fetch_map($pdo, "SELECT id, $col AS name FROM machines WHERE company_id=?", [$company_id]);
}
$KHATAS=[]; foreach(['khata_master','loom_khata_master','khatas'] as $kt){ if(_table_exists($pdo,$kt) && _col_exists($pdo,$kt,'id') && _col_exists($pdo,$kt,'name')){ $KHATAS = _fetch_map($pdo, "SELECT id, name FROM `$kt` WHERE company_id=? ORDER BY name", [$company_id]); if($KHATAS) break; } }
$QUALS=[]; if(_table_exists($pdo,'quality_rates')){ $QUALS = _fetch_map($pdo, "SELECT COALESCE(NULLIF(quality_id,0), id) AS id, quality_name AS name FROM quality_rates WHERE company_id=? ORDER BY quality_name", [$company_id]); }

?>
<!doctype html>
<html lang="en"><head>
<meta charset="utf-8"><meta name="viewport" content="width=device-width,initial-scale=1">
<title>Taka Delete — Bulk Archive</title>
<style>
  body{font-family:system-ui,Segoe UI,Arial;margin:0;background:#f7f7f7;color:#222}
  .wrap{padding:16px;}
  .card{background:#fff;border-radius:10px;padding:12px;border:1px solid #e7e7e7}
  table{width:100%;border-collapse:collapse}
  th,td{padding:8px;border-bottom:1px solid #eee;text-align:left}
  .actions{display:flex;gap:8px;align-items:center;justify-content:flex-end;margin-top:8px}
  .btn{padding:8px 12px;border-radius:8px;border:none;background:#34A853;color:#fff;cursor:pointer}
  .btn.warn{background:#b00020}
  .muted{background:#fff;color:#222;border:1px solid #ccc}
  .filters{display:flex;flex-wrap:wrap;gap:8px;align-items:center}
  .filters label{display:block}
</style>
</head><body>
<div class="wrap">
  <h2>Taka Delete (bulk) — taka_delete</h2>

<?php foreach($alerts as $a): ?><div class="card" style="margin-bottom:8px; background:#e9f7ef;border:1px solid #c6efce"><?php echo h($a); ?></div><?php endforeach; ?>
  <?php if($errors): ?><div class="card" style="margin-bottom:8px;background:#fdecea;border:1px solid #f5c6cb"><pre><?php echo h(implode("
", $errors)); ?></pre></div><?php endif; ?>

<form method="get" class="card" style="margin-bottom:10px;">
    <div class="filters">
      <label>ID <input type="number" name="id" value="<?php echo h(gv('id','')); ?>"></label>
      <label>Machine ID <input type="number" name="machine_id" value="<?php echo h(gv('machine_id','')); ?>"></label>
      <label>Khata ID <input type="number" name="khata_id" value="<?php echo h(gv('khata_id','')); ?>"></label>
      <label>Quality ID <input type="number" name="quality_id" value="<?php echo h(gv('quality_id','')); ?>"></label>
      <label>Taka No <input type="number" name="taka_no" value="<?php echo h(gv('taka_no','')); ?>"></label>
      <label>Date From <input type="date" name="date_from" value="<?php echo h(gv('date_from','')); ?>"></label>
      <label>Date To <input type="date" name="date_to" value="<?php echo h(gv('date_to','')); ?>"></label>
      <label>Search (ID/Remark) <input type="text" name="q" value="<?php echo h(gv('q','')); ?>"></label>
      <div style="align-self:end;">
        <button class="btn muted" type="submit">Apply</button>
        <a class="btn muted" href="?">Reset</a>
      </div>
    </div>
    <div class="small" style="margin-top:6px; color:#666;">Showing up to 500 rows matching filters.</div>
  </form>

<table>
      <thead>
        <tr>
          <th style="width:40px"><input type="checkbox" id="chk_all"></th>
          <th>ID</th>
          <th>Date</th>
          <th>Machine</th>
          <th>Khata</th>
          <th>Quality</th>
          <th>Taka#</th>
          <th>Meter Total</th>
          <th>Remark</th>
        </tr>
      </thead>
      <tbody>
        <?php if(!$rows): ?>
          <tr><td colspan="9">No records found.</td></tr>
        <?php else: foreach($rows as $r): ?>
          <tr>
            <td><input type="checkbox" name="ids[]" value="<?php echo (int)$r['id']; ?>"></td>
            <td><?php echo h($r['id']); ?></td>
            <td><?php echo h($r['entry_date']); ?></td>
            <td><?php echo h($MACHINES[(int)$r['machine_id']] ?? $r['machine_id']); ?></td>
            <td><?php echo h($KHATAS[(int)$r['khata_id']] ?? $r['khata_id']); ?></td>
            <td><?php echo h($QUALS[(int)$r['quality_id']] ?? $r['quality_id']); ?></td>
            <td><?php echo h($r['taka_no']); ?></td>
            <td><?php echo h($r['meter_total']); ?></td>
            <td><?php echo h($r['remark']); ?></td>
          </tr>
        <?php endforeach; endif; ?>
      </tbody>
    </table>

<div style="margin-top:8px; display:flex; gap:8px; align-items:center; justify-content:space-between;">
      <div>
        <label>Delete reason (optional): <input type="text" name="delete_reason" style="min-width:320px;"></label>
      </div>
      <div class="actions">
        <button type="button" class="btn" id="btn_bulk_delete">Delete Selected</button>
        <button type="button" class="btn warn" id="btn_delete_filtered">Delete All Filtered</button>
      </div>
    </div>
  </form>

<div style="margin-top:10px; color:#666; font-size:13px;">Note: Selected takas will be archived into <code>loom_production_entry_deleted</code> and removed from <code>loom_production_entry</code>. Only records belonging to your company are affected. "Delete All Filtered" will archive ALL records that match the current filters (as shown above).</div>
</div>

// Delete selected
  document.getElementById('btn_bulk_delete').addEventListener('click', function(){
    const selected = Array.from(document.querySelectorAll('input[name="ids[]"]:checked')).map(i=>i.value);
    if (!selected.length){ alert('Select at least one record to delete'); return; }
    if (!confirm('Delete '+selected.length+' selected taka(s)? This will archive them.')) return;
    document.getElementById('delete_filtered').value = '0';
    document.getElementById('bulkForm').submit();
  });

// Delete all filtered
  document.getElementById('btn_delete_filtered').addEventListener('click', function(){
    // confirm with user and show how many are visible in the table
    const visibleCount = document.querySelectorAll('tbody tr').length;
    if (!confirm('Archive ALL takas matching current filters? (This will attempt to archive every matching record.)

Currently showing up to 500 rows in the UI.')) return;
    // set flag and submit; server will re-run the filters to determine exact set
    document.getElementById('delete_filtered').value = '1';
    document.getElementById('bulkForm').submit();
  });
</script>
</body></html>