View Code - 20260721

machines_edit.php | 20260721_154033.php Initial Bulk Import

<?php
/* =============================================================================
   File: /erp/company_edit.php
   Purpose: Company Edit + One-click seeding (Khatas + Machines)
   Scope:   This page only. No global/base setting changes.
   Access:  via page ACL (Owner bypass)
   ============================================================================= */
error_reporting(E_ALL); ini_set('display_errors',1);

/* ================================
   1) AUTH + ACL + DB CONTEXT
   ================================ */
require __DIR__ . '/modules/auth/page_acl.php';
$ctx        = page_require_access('company_edit'); // <-- ACL slug (give access to Owner/Admin)
$u          = $ctx['user'];
$company_id = (int)$ctx['company_id'];
$user_id    = (int)$u['id'];
$pdo        = (isset($ctx['pdo']) && $ctx['pdo'] instanceof PDO) ? $ctx['pdo'] : null;
if (!$pdo) { require __DIR__ . '/core/db.php'; }

/* ================================
   2) OPTIONAL, SCOPED MIGRATIONS
   - Create khatas table (if not exists)
   - Ensure machines.group_slug supports 'palti'
   - Add helpful unique indexes (company_id, code)
   ================================= */
try {
  // khatas table (minimal schema)
  $pdo->exec("
    CREATE TABLE IF NOT EXISTS khatas (
      id BIGINT PRIMARY KEY AUTO_INCREMENT,
      company_id BIGINT NOT NULL,
      code VARCHAR(50) NOT NULL,
      name VARCHAR(100) NULL,
      created_by BIGINT NULL,
      created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
      UNIQUE KEY uniq_company_code (company_id, code)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
  ");
} catch(Throwable $e){ /* ignore here; page continues */ }

try {
  // Add 'palti' to machines.group_slug enum (if enum is used)
  // NOTE: This MODIFY will succeed only if column exists and is ENUM.
  $pdo->exec("
    ALTER TABLE machines
    MODIFY COLUMN group_slug ENUM('loom','zari','rapier','tfo','palti','other')
    NOT NULL DEFAULT 'loom'
  ");
} catch(Throwable $e){ /* if already varchar/has palti/etc, safely ignore */ }

try {
  // Unique per company for machines.code (if not already)
  // Some MySQL/MariaDB versions don't support IF NOT EXISTS; keep try/catch.
  $pdo->exec("CREATE UNIQUE INDEX uniq_company_code_m ON machines(company_id, code)");
} catch(Throwable $e){ /* ignore */ }

/* ================================
   3) HELPERS
   ================================= */
function h($s){ return htmlspecialchars((string)$s, ENT_QUOTES, 'UTF-8'); }
function pv($k,$d=null){ return isset($_POST[$k]) ? trim((string)$_POST[$k]) : $d; }
function to_int_or_null($v){ $v = trim((string)$v); return ($v==='')? null : (int)$v; }
function pad_num($n, $digits){ return str_pad((string)$n, $digits, '0', STR_PAD_LEFT); }

/**
 * Helper: returns array of existing column names (intersection) from the provided $cols list
 * Safe: if INFORMATION_SCHEMA access is restricted, returns empty array (fail-safe).
 */
function table_has_columns(PDO $pdo, string $table, array $cols): array {
  if (count($cols) === 0) return [];

// build placeholders for the IN(...)
  $placeholders = implode(',', array_fill(0, count($cols), '?'));
  $sql = "
    SELECT COLUMN_NAME
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = ?
      AND COLUMN_NAME IN ($placeholders)
  ";
  // first param is table name, then the column names
  $params = array_merge([$table], $cols);
  try {
    $st = $pdo->prepare($sql);
    $st->execute($params);
    $found = $st->fetchAll(PDO::FETCH_COLUMN, 0);
    return is_array($found) ? $found : [];
  } catch(Throwable $e){
    // If INFORMATION_SCHEMA access fails (permissions, read-only user, etc), be conservative.
    // Returning empty => we'll do minimal INSERT that avoids optional columns.
    return [];
  }
}

/**
 * Ensure khatas mst-1..mst-$target exist (no delete).
 */
function ensure_khatas(PDO $pdo, int $company_id, ?int $target, string $prefix, ?int $user_id=null){
  if (!$target || $target < 1) return;
  $ins = $pdo->prepare("INSERT INTO khatas (company_id, code, name, created_by) VALUES (?,?,?,?)");
  $chk = $pdo->prepare("SELECT id FROM khatas WHERE company_id=? AND code=? LIMIT 1");
  for($i=1; $i<=$target; $i++){
    $code = $prefix.'-'.$i;    // e.g., mst-1
    $chk->execute([$company_id, $code]);
    if(!$chk->fetch(PDO::FETCH_NUM)){
      try {
        $ins->execute([$company_id, $code, $code, $user_id]);
      } catch(Throwable $e){
        // ignore individual failures; continue creating other khatas
      }
    }
  }
}

/**
 * Ensure machines <group> <NNN> up to $target exist (no delete).
 * Dynamically adapts to whether machines table has created_by/updated_by columns.
 */
function ensure_machines(PDO $pdo, int $company_id, string $group, ?int $target, int $digits, ?int $user_id=null){
  if (!$target || $target < 1) return;

$TYPE_OF_GROUP = [
    'loom'  => 'Power Loom',
    'zari'  => 'Zari',
    'rapier'=> 'Rapier',
    'tfo'   => 'TFO',
    'palti' => 'Shuttle Loom',
  ];
  $machine_type = $TYPE_OF_GROUP[$group] ?? 'Other';

// Name format: 'group 001' style (set '%s%s' for no-space format like 'tfo01')
  $NAME_FMT = '%s %s';

// Optional columns to probe
  $optional = ['created_by','updated_by'];
  $present_optional = table_has_columns($pdo, 'machines', $optional); // subset, maybe []

// Base columns we always expect
  $base_cols = ['company_id','code','group_slug','machine_type','status'];

// Final insert columns list (preserve order)
  $insert_cols = $base_cols;
  foreach($optional as $o){
    if (in_array($o, $present_optional, true)) $insert_cols[] = $o;
  }

// Build placeholders and quoted column list
  $placeholders = implode(',', array_fill(0, count($insert_cols), '?'));
  // Quote column names with backticks to avoid reserved-word issues
  $quoted_cols = implode(',', array_map(function($c){ return "`{$c}`"; }, $insert_cols));

// Prepare check and insert statements
  $chk = $pdo->prepare("SELECT id FROM machines WHERE company_id=? AND code=? LIMIT 1");
  $ins_sql = "INSERT INTO machines ({$quoted_cols}) VALUES ({$placeholders})";
  $ins = $pdo->prepare($ins_sql);

for($i=1; $i<=$target; $i++){
    $num  = str_pad((string)$i, $digits, '0', STR_PAD_LEFT);
    $code = sprintf($NAME_FMT, $group, $num);  // e.g., "loom 001" or "zari 01"

$chk->execute([$company_id, $code]);
    if(!$chk->fetch(PDO::FETCH_NUM)){
      // build values in same order as $insert_cols
      $vals = [];
      foreach($insert_cols as $c){
        switch($c){
          case 'company_id':   $vals[] = $company_id; break;
          case 'code':         $vals[] = $code; break;
          case 'group_slug':   $vals[] = $group; break;
          case 'machine_type': $vals[] = $machine_type; break;
          case 'status':       $vals[] = 'Active'; break;
          case 'created_by':
          case 'updated_by':
            $vals[] = ($user_id !== null) ? $user_id : null;
            break;
          default:
            $vals[] = null;
        }
      }

// execute insert; don't let a single-row failure stop the whole loop
      try {
        $ins->execute($vals);
      } catch(Throwable $e) {
        // Log silently (do not expose to users). Continue to next row.
        // error_log("ensure_machines insert failed for {$code}: ".$e->getMessage());
      }
    }
  }
}

/* ================================
   4) READ CURRENT COUNTS (for UI hints)
   ================================= */
function count_by_group(PDO $pdo, int $company_id, string $group){
  try {
    $st = $pdo->prepare("SELECT COUNT(*) FROM machines WHERE company_id=? AND group_slug=?");
    $st->execute([$company_id, $group]);
    return (int)$st->fetchColumn();
  } catch(Throwable $e){
    return 0;
  }
}
$current_counts = [
  'loom'  => count_by_group($pdo, $company_id, 'loom'),
  'zari'  => count_by_group($pdo, $company_id, 'zari'),
  'rapier'=> count_by_group($pdo, $company_id, 'rapier'),
  'tfo'   => count_by_group($pdo, $company_id, 'tfo'),
  'palti' => count_by_group($pdo, $company_id, 'palti'),
];
// khatas current count (by 'mst-' prefix)
$khata_prefix = pv('khata_prefix', 'mst');
$khata_current = 0;
try{
  $st = $pdo->prepare("SELECT COUNT(*) FROM khatas WHERE company_id=? AND code LIKE ?");
  $st->execute([$company_id, $khata_prefix.'-%']);
  $khata_current = (int)$st->fetchColumn();
}catch(Throwable $e){ /* ignore */ }

/* ================================
   5) HANDLE POST (SAVE + CREATE MISSING)
   ================================= */
$flash = null; $errors = [];
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
  $khata_prefix = pv('khata_prefix', 'mst');
  $khata_count  = to_int_or_null(pv('khata_count',''));
  $loom_count   = to_int_or_null(pv('loom_count',''));
  $zari_count   = to_int_or_null(pv('zari_count',''));
  $rapier_count = to_int_or_null(pv('rapier_count',''));
  $tfo_count    = to_int_or_null(pv('tfo_count',''));
  $palti_count  = to_int_or_null(pv('palti_count',''));

if ($khata_prefix==='') $errors[] = "Khata prefix is required (e.g., mst).";
  // (optional) numeric validations
  foreach([
    'khata_count'=>$khata_count,
    'loom_count'=>$loom_count,
    'zari_count'=>$zari_count,
    'rapier_count'=>$rapier_count,
    'tfo_count'=>$tfo_count,
    'palti_count'=>$palti_count,
  ] as $label=>$val){
    if ($val!==null && $val<0) $errors[] = "$label cannot be negative.";
  }

if (!$errors) {
    $pdo->beginTransaction();
    try {
      // Create missing khatas
      ensure_khatas($pdo, $company_id, $khata_count, $khata_prefix, $user_id);

// Create missing machines by group
      // Loom => 3 digits; others => 2 digits
      ensure_machines($pdo, $company_id, 'loom' , $loom_count , 3, $user_id);
      ensure_machines($pdo, $company_id, 'zari' , $zari_count , 2, $user_id);
      ensure_machines($pdo, $company_id, 'rapier', $rapier_count, 2, $user_id);
      ensure_machines($pdo, $company_id, 'tfo'  , $tfo_count  , 2, $user_id);
      ensure_machines($pdo, $company_id, 'palti', $palti_count, 2, $user_id);

$pdo->commit();
      $flash = "Saved. Missing items created where needed. (No deletions performed.)";

// Refresh current counters after creation
      $current_counts = [
        'loom'  => count_by_group($pdo, $company_id, 'loom'),
        'zari'  => count_by_group($pdo, $company_id, 'zari'),
        'rapier'=> count_by_group($pdo, $company_id, 'rapier'),
        'tfo'   => count_by_group($pdo, $company_id, 'tfo'),
        'palti' => count_by_group($pdo, $company_id, 'palti'),
      ];
      $st = $pdo->prepare("SELECT COUNT(*) FROM khatas WHERE company_id=? AND code LIKE ?");
      $st->execute([$company_id, $khata_prefix.'-%']);
      $khata_current = (int)$st->fetchColumn();

} catch(Throwable $e) {
      $pdo->rollBack();
      $errors[] = "DB error: ".$e->getMessage();
    }
  }
}

/* ================================
   6) RENDER
   ================================= */
$title = "Company Edit — Setup (Khatas & Machines)";
require __DIR__ . '/partials/header.php';
?>
<div class="wrap container-fluid py-4">
    <div class="d-flex justify-content-between align-items-center mb-4">
        <div>
            <h2 class="fw-bold m-0 text-dark">
                <i class="bi bi-magic text-primary me-2"></i> System Auto-Generator
            </h2>
            <p class="text-muted small mb-0">Bulk generate Khatas and Machine assets automatically</p>
        </div>
        <a href="/erp/public/index.php" class="btn btn-outline-secondary btn-sm px-3">
            <i class="bi bi-arrow-left me-1"></i> Back to Dashboard
        </a>
    </div>

<?php if($flash): ?>
        <div class="alert alert-success shadow-sm border-0 border-start border-4 border-success d-flex align-items-center mb-4">
            <i class="bi bi-check-circle-fill me-3 fs-4"></i>
            <div><?= h($flash) ?></div>
        </div>
    <?php endif; ?>

<?php if($errors): ?>
        <div class="alert alert-danger shadow-sm border-0 border-start border-4 border-danger mb-4">
            <div class="fw-bold mb-1"><i class="bi bi-exclamation-octagon-fill me-2"></i> Please fix these issues:</div>
            <ul class="mb-0 small ps-4">
                <?php foreach($errors as $e): ?> <li><?= h($e) ?></li> <?php endforeach; ?>
            </ul>
        </div>
    <?php endif; ?>

<form method="post" autocomplete="off">
        <div class="row g-4">
            
            <div class="col-lg-12">
                <div class="card shadow-sm border-0">
                    <div class="card-header bg-white py-3">
                        <h6 class="m-0 fw-bold text-primary small text-uppercase">
                            <i class="bi bi-journal-bookmark-fill me-2"></i> 1. Khatas Configuration
                        </h6>
                    </div>
                    <div class="card-body p-4">
                        <div class="row g-3 align-items-end">
                            <div class="col-md-4">
                                <label class="form-label small fw-bold">Khata Prefix</label>
                                <input type="text" name="khata_prefix" class="form-control shadow-none" 
                                       value="<?= h($khata_prefix) ?>" placeholder="e.g., mst">
                            </div>
                            <div class="col-md-4">
                                <label class="form-label small fw-bold">Target Count</label>
                                <input type="number" name="khata_count" class="form-control shadow-none" 
                                       placeholder="Enter total khatas needed">
                            </div>
                            <div class="col-md-4">
                                <div class="p-2 bg-light rounded border text-center">
                                    <small class="text-muted d-block">Current Khatas (<?= h($khata_prefix) ?>)</small>
                                    <span class="h5 fw-bold mb-0"><?= (int)$khata_current ?></span>
                                </div>
                            </div>
                        </div>
                    </div>
                </div>
            </div>

<div class="col-lg-12">
                <div class="card shadow-sm border-0">
                    <div class="card-header bg-white py-3">
                        <h6 class="m-0 fw-bold text-primary small text-uppercase">
                            <i class="bi bi-cpu-fill me-2"></i> 2. Machine Assets Generation
                        </h6>
                    </div>
                    <div class="card-body p-4">
                        <div class="row g-4">
                            <?php 
                            $machine_types = [
                                'loom'   => ['icon' => 'bi-layers', 'label' => 'Loom Machines', 'fmt' => 'loom 001'],
                                'zari'   => ['icon' => 'bi-gem', 'label' => 'Zari Machines', 'fmt' => 'zari 01'],
                                'rapier' => ['icon' => 'bi-gear-wide', 'label' => 'Rapier Machines', 'fmt' => 'rapier 01'],
                                'tfo'    => ['icon' => 'bi-wind', 'label' => 'TFO Machines', 'fmt' => 'tfo 01'],
                                'palti'  => ['icon' => 'bi-arrow-left-right', 'label' => 'Palti Machines', 'fmt' => 'palti 01']
                            ];
                            
                            foreach($machine_types as $key => $meta): ?>
                            <div class="col-md-6 col-xl-4">
                                <div class="p-3 border rounded-3 bg-white hover-shadow-sm transition">
                                    <div class="d-flex justify-content-between align-items-start mb-2">
                                        <div class="d-flex align-items-center">
                                            <div class="bg-primary bg-opacity-10 text-primary rounded p-2 me-2">
                                                <i class="bi <?= $meta['icon'] ?>"></i>
                                            </div>
                                            <label class="form-label mb-0 fw-bold"><?= $meta['label'] ?></label>
                                        </div>
                                        <span class="badge bg-light text-dark border">Existing: <?= (int)$current_counts[$key] ?></span>
                                    </div>
                                    <input type="number" name="<?= $key ?>_count" class="form-control form-control-sm" 
                                           placeholder="Target count (e.g., 50)">
                                    <div class="form-text x-small mt-1 text-muted">Format: <code><?= $meta['fmt'] ?></code></div>
                                </div>
                            </div>
                            <?php endforeach; ?>
                        </div>

<div class="mt-4 p-3 bg-light rounded-3 border border-dashed text-center">
                            <p class="mb-0 small text-muted italic">
                                <i class="bi bi-info-circle me-1"></i> 
                                No-space codes (e.g., <b>tfo01</b>) require updating <code>$NAME_FMT</code> to <code>'%s%s'</code> in the core logic.
                            </p>
                        </div>
                    </div>
                </div>
            </div>

<div class="col-12 mt-3 mb-5">
                <div class="card border-0 shadow-sm bg-dark">
                    <div class="card-body d-flex justify-content-between align-items-center py-3">
                        <span class="text-white-50 small pe-3">
                            <i class="bi bi-shield-lock me-1"></i> This action will only create missing records.
                        </span>
                        <button type="submit" class="btn btn-success px-5 fw-bold btn-lg shadow">
                            <i class="bi bi-lightning-charge-fill me-2"></i> Save & Generate Assets
                        </button>
                    </div>
                </div>
            </div>
        </div>
    </form>
</div>