View Code - 20260721

month_end_stock_report_json_edit.php | 20260721_154033.php Initial Bulk Import

<?php
/**
 * month_end_stock_report_json_edit.php
 * Visual JSON editor for month_end_stock_report (dynamic JSON column detection)
 *
 * - Detects which JSON columns exist and only SELECTs / allows updates for those
 * - Conditional session_start()
 * - Robust header/footer include resolution (tries ./partials and ./erp/partials)
 * - Enforces company_id filter and prepared statements
 * - Sets updated_by / updated_at on save
 *
 * Author: (your name)
 */

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

// Bootstrap auth & context
require_once __DIR__ . '/modules/auth/page_acl.php';
$ctx = page_require_access('month_end_stock_report'); // adjust ACL slug if needed

$u = $ctx['user'] ?? null;
$COMPANY_ID = (int)($ctx['company_id'] ?? 0);
$pdo = $ctx['pdo'] ?? null;

// Fallback DB include only if $pdo missing (per project rules)
if (!$pdo) {
    require_once __DIR__ . '/core/db.php';
    $pdo = $pdo ?? null;
}

if (!$u || !$pdo) {
    http_response_code(403);
    echo "Access denied or DB missing.";
    exit;
}

// Ensure CSRF token exists
if (empty($_SESSION['csrf_token'])) {
    $_SESSION['csrf_token'] = bin2hex(random_bytes(16));
}
$csrf = $_SESSION['csrf_token'];

/* ---------------------------
   Helper: resolve partials
   --------------------------- */
function resolve_partial($name) {
    $candidate1 = __DIR__ . '/partials/' . $name;
    $candidate2 = __DIR__ . '/erp/partials/' . $name;
    if (file_exists($candidate1)) return $candidate1;
    if (file_exists($candidate2)) return $candidate2;
    return $candidate1; // fallback (will show error so developer notices)
}

/* ---------------------------
   Helper: detect JSON columns present in table
   --------------------------- */
function get_table_columns(PDO $pdo, string $table) : array {
    $cols = [];
    try {
        $stmt = $pdo->query("SHOW COLUMNS FROM `{$table}`");
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
        foreach ($rows as $r) {
            $cols[] = $r['Field'];
        }
    } catch (Exception $e) {
        // leave empty array on error
    }
    return $cols;
}

$all_columns = get_table_columns($pdo, 'month_end_stock_report');

// Decide which JSON-ish fields to support by default (extendable)
$possible_json_fields = ['yarns_json', 'entries', 'json_data', 'items_json']; // add any candidates you sometimes use
$existing_json_fields = array_values(array_intersect($possible_json_fields, $all_columns));
// If none found, fall back to any column whose name ends with '_json'
if (empty($existing_json_fields)) {
    foreach ($all_columns as $c) {
        if (substr($c, -5) === '_json') $existing_json_fields[] = $c;
    }
}
// final allowed fields (non-empty)
$allowed_json_fields = array_values(array_unique($existing_json_fields));

/* ---------------------------
   AJAX: fetch_row, save_json
   --------------------------- */
if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['action'])) {
    header('Content-Type: application/json; charset=utf-8');

// CSRF check
    $sent = $_POST['csrf'] ?? '';
    if (!hash_equals($_SESSION['csrf_token'], (string)$sent)) {
        echo json_encode(['ok' => false, 'error' => 'Invalid CSRF token']);
        exit;
    }

$action = $_POST['action'];

if ($action === 'fetch_row') {
        $id = (int)($_POST['id'] ?? 0);
        // Build SELECT using safe base columns plus any existing json columns
        $baseCols = ['id','company_id','month','stock_type','beam_quality_name','total_meter'];
        $selectCols = array_intersect($baseCols, $all_columns);
        // include found json columns
        foreach ($allowed_json_fields as $jf) {
            if (in_array($jf, $all_columns, true) && !in_array($jf, $selectCols, true)) {
                $selectCols[] = $jf;
            }
        }
        if (empty($selectCols)) {
            echo json_encode(['ok' => false, 'error' => 'No selectable columns found in table']);
            exit;
        }
        $sql = 'SELECT ' . implode(',', array_map(function($c){ return "`{$c}`";}, $selectCols)) . ' FROM `month_end_stock_report` WHERE id = :id AND company_id = :cid LIMIT 1';
        $stmt = $pdo->prepare($sql);
        $stmt->execute([':id' => $id, ':cid' => $COMPANY_ID]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        if (!$row) {
            echo json_encode(['ok' => false, 'error' => 'Row not found or access denied']);
            exit;
        }
        echo json_encode(['ok' => true, 'row' => $row, 'available_json_fields' => $allowed_json_fields]);
        exit;
    }

if ($action === 'save_json') {
        $id = (int)($_POST['id'] ?? 0);
        $field = $_POST['field'] ?? '';
        // Validate field exists and is allowed
        if (!in_array($field, $allowed_json_fields, true) || !in_array($field, $all_columns, true)) {
            echo json_encode(['ok' => false, 'error' => 'Invalid or missing JSON field. Available: ' . implode(',', $allowed_json_fields)]);
            exit;
        }
        $json_raw = $_POST['json_raw'] ?? '';

// Validate JSON
        $decoded = json_decode($json_raw, true);
        if (json_last_error() !== JSON_ERROR_NONE) {
            echo json_encode(['ok' => false, 'error' => 'Invalid JSON: ' . json_last_error_msg()]);
            exit;
        }

// Update DB (prepared)
        $now = date('Y-m-d H:i:s');
        // Use explicit parameter placeholder for JSON column to avoid injection risk in dynamic SQL
        $sql = "UPDATE `month_end_stock_report` SET `{$field}` = :json, `updated_by` = :uid, `updated_at` = :uat WHERE `id` = :id AND `company_id` = :cid";
        $stmt = $pdo->prepare($sql);
        $ok = $stmt->execute([
            ':json' => json_encode($decoded, JSON_UNESCAPED_UNICODE),
            ':uid' => $u['id'],
            ':uat' => $now,
            ':id' => $id,
            ':cid' => $COMPANY_ID
        ]);

if (!$ok) {
            $err = $stmt->errorInfo();
            echo json_encode(['ok' => false, 'error' => 'DB update failed: ' . ($err[2] ?? '')]);
            exit;
        }

echo json_encode(['ok' => true, 'message' => 'Saved']);
        exit;
    }

echo json_encode(['ok' => false, 'error' => 'Unknown action']);
    exit;
}

/* ---------------------------
   Page UI (GET)
   --------------------------- */

// include header
$header_path = resolve_partial('header.php');
require_once $header_path;
?>

<div class="card">
  <div class="card-header">
    <h3>Month End Stock — JSON Editor</h3>
    <p class="muted">Company ID: <?php echo htmlspecialchars($COMPANY_ID, ENT_QUOTES); ?></p>
    <?php if (empty($allowed_json_fields)): ?>
      <div class="notice">No JSON-like columns found in <code>month_end_stock_report</code>. Add a column ending with <code>_json</code> or update <code>$possible_json_fields</code> in this file.</div>
    <?php else: ?>
      <div class="muted">Editable JSON columns: <?php echo htmlspecialchars(implode(', ', $allowed_json_fields), ENT_QUOTES); ?></div>
    <?php endif; ?>
  </div>

<div class="card-body">
    <table class="table table-striped">
      <thead>
        <tr>
          <th>#</th>
          <th>month</th>
          <th>stock_type</th>
          <th>beam_quality_name</th>
          <th>total_meter</th>
          <th>JSON field</th>
          <th>Actions</th>
        </tr>
      </thead>
      <tbody>
<?php
// Build SELECT for listing: base columns (if present) plus first available json field to label rows
$baseCols = ['id','month','stock_type','beam_quality_name','total_meter'];
$selectCols = array_values(array_intersect($baseCols, $all_columns));
// pick one json field (first allowed) for listing label if present
$label_json = $allowed_json_fields[0] ?? null;
if ($label_json && in_array($label_json, $all_columns, true)) {
    $selectCols[] = $label_json;
}
if (empty($selectCols)) {
    echo '<tr><td colspan="7">No columns available to display.</td></tr>';
} else {
    $sql = 'SELECT ' . implode(',', array_map(function($c){ return "`{$c}`";}, $selectCols)) . ' FROM `month_end_stock_report` WHERE company_id = :cid ORDER BY id DESC LIMIT 500';
    $stmt = $pdo->prepare($sql);
    $stmt->execute([':cid' => $COMPANY_ID]);
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
    foreach ($rows as $r) :
        $label = '—';
        if ($label_json && isset($r[$label_json]) && trim((string)$r[$label_json]) !== '') {
            $label = $label_json;
        } else {
            // fallback: find any allowed_json_fields that are present and non-empty for this row
            foreach ($allowed_json_fields as $af) {
                if (isset($r[$af]) && trim((string)$r[$af]) !== '') { $label = $af; break; }
            }
        }
?>
        <tr data-row-id="<?php echo (int)$r['id']; ?>">
          <td><?php echo (int)$r['id']; ?></td>
          <td><?php echo htmlspecialchars($r['month'] ?? '', ENT_QUOTES); ?></td>
          <td><?php echo htmlspecialchars($r['stock_type'] ?? '', ENT_QUOTES); ?></td>
          <td><?php echo htmlspecialchars($r['beam_quality_name'] ?? '', ENT_QUOTES); ?></td>
          <td><?php echo htmlspecialchars($r['total_meter'] ?? '', ENT_QUOTES); ?></td>
          <td><?php echo htmlspecialchars($label, ENT_QUOTES); ?></td>
          <td>
            <button class="btn btn-sm btn-outline" data-action="edit" data-id="<?php echo (int)$r['id']; ?>">Edit JSON</button>
          </td>
        </tr>
<?php
    endforeach;
}
?>
      </tbody>
    </table>
  </div>
</div>

<div id="jsonModal" class="modal" aria-hidden="true" style="display:none;">
  <div class="modal-content" style="max-width:900px;margin:2rem auto;">
    <div class="modal-header">
      <h4 id="modalTitle">Edit JSON</h4>
      <button id="modalClose" class="btn">Close</button>
    </div>
    <div class="modal-body">
      <p><strong>Row ID:</strong> <span id="modalRowId"></span> &nbsp; <strong>Field:</strong> <span id="modalField"></span></p>

<div style="margin-bottom:0.5rem;">
        <label><input type="radio" name="mode" value="raw" checked> Raw JSON</label>
        <label style="margin-left:1rem;"><input type="radio" name="mode" value="visual"> Visual editor</label>
      </div>

<div id="visualArea" style="display:none;">
        <div id="visualEditorNote" class="muted">Top-level object/array elements are converted to editable rows. For deeply nested edits, use Raw JSON.</div>
        <table id="visualTable" class="table table-condensed" style="width:100%;">
          <thead><tr><th>Path</th><th>Value (editable)</th></tr></thead>
          <tbody></tbody>
        </table>
      </div>
    </div>

<div class="modal-footer" style="text-align:right;">
      <button id="btnValidate" class="btn">Validate JSON</button>
      <button id="btnSave" class="btn btn-primary">Save</button>
    </div>
  </div>
</div>

function openModal() { modal.style.display = 'block'; modal.setAttribute('aria-hidden','false'); }
  function closeModal(){ modal.style.display = 'none'; modal.setAttribute('aria-hidden','true'); }

document.getElementById('modalClose').addEventListener('click', closeModal);

function addVisualRow(path, value) {
    const tr = document.createElement('tr');
    const tdPath = document.createElement('td');
    tdPath.textContent = path;
    const tdVal = document.createElement('td');
    const input = document.createElement('input');
    input.type = 'text';
    input.value = (value === null ? '' : String(value));
    input.dataset.path = path;
    input.className = 'form-control';
    tdVal.appendChild(input);
    tr.appendChild(tdPath);
    tr.appendChild(tdVal);
    visualTable.appendChild(tr);
  }

document.getElementById('btnSave').addEventListener('click', ()=>{
    const id = modalRowId.textContent;
    const field = modalField.textContent || '';
    if (!field) { alert('No JSON field selected'); return; }

let jsonToSave = jsonTextarea.value;

if (document.querySelector('input[name="mode"]:checked').value === 'visual') {
      let original;
      try {
        original = JSON.parse(jsonTextarea.value);
      } catch(e) {
        alert('Cannot save: original JSON invalid. Switch to Raw and fix it first.');
        return;
      }
      const inputs = document.querySelectorAll('#visualTable input[data-path]');
      inputs.forEach(inp=>{
        const path = inp.dataset.path;
        const newValRaw = inp.value;
        let newVal;
        if (newValRaw === '') {
          newVal = null;
        } else if (!isNaN(newValRaw) && newValRaw.trim() !== '') {
          newVal = Number(newValRaw);
        } else if (newValRaw.toLowerCase && (newValRaw.toLowerCase() === 'true' || newValRaw.toLowerCase() === 'false')) {
          newVal = (newValRaw.toLowerCase() === 'true');
        } else {
          newVal = newValRaw;
        }

if (path.startsWith('[')) {
          const m = path.match(/^\[(\d+)\](?:\.(.+))?$/);
          if (m) {
            const idx = parseInt(m[1],10);
            const subkey = m[2] || null;
            if (Array.isArray(original) && original[idx] !== undefined) {
              if (subkey) {
                original[idx][subkey] = newVal;
              } else {
                original[idx] = newVal;
              }
            }
          }
        } else if (path === '(value)') {
          original = newVal;
        } else {
          if (original && typeof original === 'object') {
            original[path] = newVal;
          }
        }
      });

jsonToSave = JSON.stringify(original, null, 2);
    }

try {
      JSON.parse(jsonToSave);
    } catch(e) {
      alert('JSON invalid: ' + e.message);
      return;
    }

fetch('', {
      method: 'POST',
      headers:{'Content-Type':'application/x-www-form-urlencoded'},
      body: new URLSearchParams({
        action: 'save_json',
        id: id,
        field: field,
        json_raw: jsonToSave,
        csrf: csrf
      })
    }).then(r=>r.json()).then(resp=>{
      if (!resp.ok) {
        alert('Save failed: ' + (resp.error || 'unknown'));
        return;
      }
      alert('Saved');
      closeModal();
      location.reload();
    }).catch(e=>{
      alert('Save error: '+e);
    });
  });

})();
</script>

<?php
// include footer
$footer_path = resolve_partial('footer.php');
require_once $footer_path;