View Code - 20260721

employee_register_1_.php | 20260721_154032.php Initial Bulk Import

<?php
/* =============================================================================
   File: /erp/employee_register.php
   Purpose: Employee Registration → company_employee_master (+ salary history)
   Masters: company_departments, company_designation, company_salary_types, company_payroll_periods
   Notes  : Schema-aware master loaders + schema-aware users insert (Create Login)
   ============================================================================= */
error_reporting(E_ALL); ini_set('display_errors', 1);

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

/* ---------- DDL (targets) ---------- */
$pdo->exec("
CREATE TABLE IF NOT EXISTS company_employee_master (
  id                   BIGINT PRIMARY KEY AUTO_INCREMENT,
  company_id           BIGINT NOT NULL,
  employee_code        VARCHAR(30) NULL,
  name                 VARCHAR(120) NOT NULL,
  mobile               VARCHAR(20)  NOT NULL,
  email                VARCHAR(191) NULL,
  department_id        BIGINT NOT NULL,
  department_name      VARCHAR(120) NOT NULL,
  designation_id       BIGINT NULL,
  designation_name     VARCHAR(120) NULL,
  payroll_period_id    BIGINT NULL,
  payroll_period_name  VARCHAR(120) NULL,
  salary_type_id       BIGINT NOT NULL,
  salary_type_name     VARCHAR(60)  NOT NULL,
  per_day              DECIMAL(10,2) NULL,
  basic_salary         DECIMAL(12,2) NULL,
  is_active            TINYINT(1) NOT NULL DEFAULT 1,
  notes                VARCHAR(255) NULL,
  created_by           BIGINT NULL,
  created_at           TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at           TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_company_mobile (company_id, mobile)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
");

$pdo->exec("
CREATE TABLE IF NOT EXISTS employee_salary (
  id             BIGINT PRIMARY KEY AUTO_INCREMENT,
  company_id     BIGINT NOT NULL,
  employee_id    BIGINT NOT NULL,        -- FK to company_employee_master.id
  salary_type_id BIGINT NULL,
  salary_type    VARCHAR(60) NOT NULL,
  per_day        DECIMAL(10,2) NULL,
  basic_salary   DECIMAL(12,2) NULL,
  effective_from DATE NULL,
  effective_to   DATE NULL,
  active         TINYINT(1) NOT NULL DEFAULT 1,
  notes          VARCHAR(255) NULL,
  created_by     BIGINT NULL,
  created_at     TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at     TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_company_emp (company_id, employee_id),
  INDEX idx_company_active (company_id, active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
");

/* ---------- Helpers ---------- */
function h($s){ return htmlspecialchars((string)$s, ENT_QUOTES, 'UTF-8'); }
function col_exists(PDO $pdo, $table, $col){
  $st = $pdo->prepare("SHOW COLUMNS FROM `$table` LIKE ?");
  $st->execute([$col]);
  return (bool)$st->fetch(PDO::FETCH_ASSOC);
}
function any_present(PDO $pdo, $table, array $cands){
  foreach ($cands as $c) { if (col_exists($pdo,$table,$c)) return $c; }
  return null;
}
/** Schema-aware master fetcher → rows: [id, name, code] */
function fetch_master(PDO $pdo, string $table, int $company_id, array $nameCands, array $codeCands){
  $hasCompany = col_exists($pdo,$table,'company_id');
  $activeCol  = any_present($pdo,$table,['is_active','active','enabled']);
  $nameCol    = any_present($pdo,$table,$nameCands);
  $codeCol    = any_present($pdo,$table,$codeCands);

if (!$nameCol) { // fallback: first non-id/company_id column
    $cols = $pdo->query("SHOW COLUMNS FROM `$table`")->fetchAll(PDO::FETCH_COLUMN);
    foreach ($cols as $c) { if ($c!=='id' && $c!=='company_id') { $nameCol=$c; break; } }
  }

// try current company first
  $sql1 = "SELECT id, `$nameCol` AS name, ".($codeCol?"`$codeCol`":"NULL")." AS code FROM `$table`";
  $w=[]; $p=[];
  if ($hasCompany){ $w[]="company_id=?"; $p[]=$company_id; }
  if ($activeCol){ $w[]="`$activeCol`=1"; }
  if ($w) $sql1 .= " WHERE ".implode(" AND ",$w);
  $sql1 .= " ORDER BY ".($nameCol?"`$nameCol`":"id");
  $st=$pdo->prepare($sql1); $st->execute($p);
  $rows=$st->fetchAll(PDO::FETCH_ASSOC);
  if ($rows) return $rows;

// fallback: show all active (any company), prioritize current company
  $sql2 = "SELECT id, `$nameCol` AS name, ".($codeCol?"`$codeCol`":"NULL")." AS code, ".($hasCompany?"company_id":"0")." AS cmp FROM `$table`";
  $w=[]; $p=[];
  if ($activeCol){ $w[]="`$activeCol`=1"; }
  if ($w) $sql2 .= " WHERE ".implode(" AND ",$w);
  $sql2 .= " ORDER BY CASE WHEN ".($hasCompany?"company_id":"0")."=? THEN 0 ELSE 1 END, ".($nameCol?"`$nameCol`":"id");
  $st=$pdo->prepare($sql2); $st->execute([$company_id]);
  return $st->fetchAll(PDO::FETCH_ASSOC);
}
function normalize_user_role($role_text){
  $r=strtolower(trim($role_text));
  return in_array($r,['owner','admin','manager','developer'],true)?ucfirst($r):'Employee';
}
function is_loom_karigar($dept_name,$role_name){
  return (strcasecmp($dept_name,'loom')===0 && strcasecmp($role_name,'karigar')===0);
}

/* ---------- Load Masters (safe) ---------- */
$DEPTS = fetch_master($pdo, 'company_departments',  $company_id,
  ['name','title','label'], ['code','short_code','slug']);

$ROLES = fetch_master($pdo, 'company_designation',  $company_id,
  ['name','title','label','designation','role'], ['code','short_code','slug']);

$STYPES= fetch_master($pdo, 'company_salary_types', $company_id,
  ['name','title','label'], ['code','short_code','slug']);

/* payroll_type is the name column in your table */
$PPERS = fetch_master($pdo, 'company_payroll_periods', $company_id,
  ['payroll_type','name','title','label','period_name','period_label'], ['code','short_code','slug']);

/* ---------- Handle POST ---------- */
$flash=''; $err=''; $new_emp_id=null;

if($_SERVER['REQUEST_METHOD']==='POST'){
  $emp_code = trim($_POST['employee_code']??'');
  $name     = trim($_POST['name']??'');
  $deptId   = (int)($_POST['department_id']??0);
  $roleId   = (int)($_POST['designation_id']??0);
  $mobile   = preg_replace('/\s+/', '', (string)($_POST['mobile']??''));
  $email    = trim($_POST['email']??'');
  $stypeId  = (int)($_POST['salary_type_id']??0);
  $ppId     = (int)($_POST['payroll_period_id']??0);
  $perDay   = isset($_POST['per_day']) ? (float)$_POST['per_day'] : null;
  $basic    = isset($_POST['basic_salary']) ? (float)$_POST['basic_salary'] : null;
  $notes    = trim($_POST['notes']??'');
  $mk_user  = isset($_POST['make_login']) ? 1 : 0;

try{
    if($name===''||$deptId<=0||$stypeId<=0||$mobile==='') throw new Exception("Name, Department, Salary Type, Mobile आवश्यक हैं।");

// Map IDs → snapshots
    $dept = array_values(array_filter($DEPTS, fn($d)=>(int)$d['id']===$deptId))[0] ?? null;
    $role = $roleId>0 ? (array_values(array_filter($ROLES, fn($r)=>(int)$r['id']===$roleId))[0] ?? null) : null;
    $stype= array_values(array_filter($STYPES, fn($s)=>(int)$s['id']===$stypeId))[0] ?? null;
    $pper = $ppId>0 ? (array_values(array_filter($PPERS, fn($p)=>(int)$p['id']===$ppId))[0] ?? null) : null;

if(!$dept)  throw new Exception("Invalid Department.");
    if(!$stype) throw new Exception("Invalid Salary Type.");

$stypeName = strtolower(trim($stype['name']??''));
    if($stypeName==='attendance' && !($perDay>0)) throw new Exception("Attendance salary के लिए Per Day आवश्यक है।");
    if(($stypeName==='fixed' || $stypeName==='fixed attendance') && !($basic>0))
      throw new Exception("Fixed/Fixed Attendance के लिए Basic Salary आवश्यक है।");

// Duplicate mobile guard
    $chk=$pdo->prepare("SELECT id FROM company_employee_master WHERE company_id=? AND mobile=? LIMIT 1");
    $chk->execute([$company_id,$mobile]);
    if($chk->fetch()) throw new Exception("इस mobile पर employee पहले से मौजूद है।");

$pdo->beginTransaction();

// Insert master record
    $ins=$pdo->prepare("INSERT INTO company_employee_master
      (company_id,employee_code,name,mobile,email,
       department_id,department_name,designation_id,designation_name,
       payroll_period_id,payroll_period_name,
       salary_type_id,salary_type_name,per_day,basic_salary,
       is_active,notes,created_by)
      VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
    $ins->execute([
      $company_id, ($emp_code!==''?$emp_code:null), $name, $mobile, ($email!==''?$email:null),
      $dept['id'], $dept['name'],
      ($role? $role['id']:null), ($role? $role['name']:null),
      ($pper? $pper['id']:null), ($pper? $pper['name']:null),
      $stype['id'], $stype['name'],
      ($stypeName==='attendance'? $perDay : null),
      (($stypeName==='fixed'||$stypeName==='fixed attendance')? $basic : null),
      1, ($notes!==''?$notes:null), $user_id
    ]);
    $new_emp_id = (int)$pdo->lastInsertId();

// Salary history (deactivate old → insert new active)
    $pdo->prepare("UPDATE employee_salary SET active=0, effective_to=CURDATE()
                   WHERE company_id=? AND employee_id=? AND active=1")
        ->execute([$company_id,$new_emp_id]);

$insS=$pdo->prepare("INSERT INTO employee_salary
      (company_id,employee_id,salary_type_id,salary_type,per_day,basic_salary,effective_from,active,notes,created_by)
      VALUES (?,?,?,?,?,?,?,?,?,?)");
    $insS->execute([
      $company_id,$new_emp_id,$stype['id'],$stype['name'],
      ($stypeName==='attendance'? $perDay : null),
      (($stypeName==='fixed'||$stypeName==='fixed attendance')? $basic : null),
      date('Y-m-d'),1, ($notes!==''?$notes:null), $user_id
    ]);

// Optional Login (schema-aware; handles UNIQUE on username/email)
    if ($mk_user) {
      try {
        $has_username = col_exists($pdo,'users','username');
        $has_email    = col_exists($pdo,'users','email');
        $has_company  = col_exists($pdo,'users','company_id');
        $has_role     = col_exists($pdo,'users','role');
        $has_name     = col_exists($pdo,'users','name');

// Dup check by username/email = mobile (company-scoped if column exists)
        $dup = false;
        $or  = []; $params = [];
        if ($has_username){ $or[]='username=?'; $params[]=$mobile; }
        if ($has_email){    $or[]='email=?';    $params[]=$mobile; }
        if ($or){
          $sqlDup = "SELECT id FROM users WHERE ".
                    ($has_company ? "company_id=? AND " : "").
                    "(".implode(' OR ', $or).") LIMIT 1";
          $dupStmt = $pdo->prepare($sqlDup);
          $dupStmt->execute(array_merge($has_company?[$company_id]:[], $params));
          $dup = (bool)$dupStmt->fetch();
        }

if (!$dup) {
          $cols = []; $ph = []; $vals = [];
          if ($has_company){ $cols[]='company_id'; $ph[]='?'; $vals[]=$company_id; }
          if ($has_name){    $cols[]='name';       $ph[]='?'; $vals[]=$name; }
          if ($has_username){$cols[]='username';   $ph[]='?'; $vals[]=$mobile; } // important: avoid '' duplicate
          if ($has_email){   $cols[]='email';      $ph[]='?'; $vals[]=$mobile; } // login id as mobile (if email used)
          $cols[]='password_hash'; $ph[]='?'; $vals[] = password_hash('12345678', PASSWORD_DEFAULT);
          if ($has_role){    $cols[]='role';       $ph[]='?'; $vals[] = normalize_user_role($role['name'] ?? ''); }

$sqlIns = "INSERT INTO users (".implode(',', $cols).") VALUES (".implode(',', $ph).")";
          $pdo->prepare($sqlIns)->execute($vals);
        }
      } catch (Throwable $ex) {
        // Do not block employee creation
        $flash .= " (Login skipped: ".$ex->getMessage().")";
      }
    }

$pdo->commit();

if(is_loom_karigar($dept['name'], $role['name'] ?? '')){
      header("Location: /erp/loom_employee_register.php?employee_id=".$new_emp_id);
      exit;
    }

$flash = "Employee saved (ID: {$new_emp_id}).";
  }catch(Throwable $e){
    if($pdo->inTransaction()) $pdo->rollBack();
    $err = $e->getMessage();
  }
}

/* ---------- UI ---------- */
?><!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<title>Employee Register</title>
<meta name="viewport" content="width=device-width, initial-scale=1">
<style>
  :root{ --mm-green:#34A853; --mm-bg:#F5FFF7; --mm-gray:#5F6368; --mm-sky:#A7D8DE; }
  *{box-sizing:border-box}
  body{font-family:system-ui,Segoe UI,Roboto,Arial;background:var(--mm-bg);margin:0;color:#222}
  .wrap{max-width:920px;margin:24px auto;padding:16px}
  h1{margin:0 0 12px}
  .card{background:#fff;border:1px solid #eee;border-radius:14px;box-shadow:0 2px 8px rgba(0,0,0,.04);padding:16px;margin-bottom:12px}
  .grid{display:grid;grid-template-columns:1fr 1fr;gap:12px}
  @media (max-width:720px){ .grid{grid-template-columns:1fr} }
  label{display:flex;flex-direction:column;gap:6px}
  input,select,textarea,button{padding:10px;border:1px solid #ccc;border-radius:10px;font-size:14px}
  button.primary{background:var(--mm-green);color:#fff;border:none;cursor:pointer}
  .muted{color:#666;font-size:12px}
  .hide{display:none}
  .ok{color:#0a0}.err{color:#b00}
</style>
</head>
<body>
<div class="wrap">
  <h1>Employee Registration</h1>

<?php if($flash): ?><div class="card ok"><?= h($flash) ?></div><?php endif; ?>
  <?php if($err):   ?><div class="card err"><?= h($err)   ?></div><?php endif; ?>

<form method="post" class="card" id="regForm" autocomplete="off">
    <div class="grid">
      <label>Employee Code
        <input name="employee_code" placeholder="Optional">
      </label>

<label>Department
        <select name="department_id" id="department_id" required>
          <option value="">Select Department</option>
          <?php foreach($DEPTS as $d): ?>
            <option value="<?= (int)$d['id'] ?>" data-name="<?= h($d['name']) ?>">
              <?= h($d['name']) ?><?= $d['code']? " (".h($d['code']).")":"" ?>
            </option>
          <?php endforeach; ?>
        </select>
      </label>

<label>Designation (Role)
        <select name="designation_id" id="designation_id">
          <option value="">Select Designation</option>
          <?php foreach($ROLES as $r): ?>
            <option value="<?= (int)$r['id'] ?>" data-name="<?= h($r['name']) ?>">
              <?= h($r['name']) ?><?= $r['code']? " (".h($r['code']).")":"" ?>
            </option>
          <?php endforeach; ?>
        </select>
      </label>

<label>Payroll Period
        <select name="payroll_period_id" id="payroll_period_id">
          <option value="">Select Payroll Period</option>
          <?php foreach($PPERS as $p): ?>
            <option value="<?= (int)$p['id'] ?>" data-name="<?= h($p['name']) ?>">
              <?= h($p['name']) ?><?= $p['code']? " (".h($p['code']).")":"" ?>
            </option>
          <?php endforeach; ?>
        </select>
      </label>

<label>Mobile
        <input name="mobile" required pattern="[0-9+\-\s]{6,}">
      </label>

<label>Email
        <input name="email" type="email" placeholder="Optional">
      </label>

<label>Salary Type
        <select name="salary_type_id" id="salary_type_id" required>
          <option value="">Select Salary Type</option>
          <?php foreach($STYPES as $s): ?>
            <option value="<?= (int)$s['id'] ?>" data-name="<?= h(strtolower($s['name'])) ?>">
              <?= h($s['name']) ?><?= $s['code']? " (".h($s['code']).")":"" ?>
            </option>
          <?php endforeach; ?>
        </select>
      </label>

<label id="basicWrap" class="hide">Basic Salary (₹)
        <input name="basic_salary" type="number" step="0.01" placeholder="e.g., 15000">
      </label>

<label style="grid-column:1/-1">Notes
        <textarea name="notes" placeholder="Optional"></textarea>
      </label>
    </div>

<div style="display:flex;gap:10px;align-items:center;margin-top:10px">
      <label style="display:flex;gap:8px;align-items:center">
        <input type="checkbox" name="make_login" id="make_login">
        Create Login (ID = Mobile, Password = 12345678)
      </label>
    </div>

<div style="display:flex;justify-content:flex-end;margin-top:12px">
      <button class="primary">Register</button>
    </div>
  </form>
</div>