View Code - 20260721

employee_bonus_calculator.php | 20260721_154032.php Initial Bulk Import

<?php
/* ============================================================================
   File: /erp/employee_bonus_calculator.php
   Purpose: Employee Bonus Calculator
   - Bonus percent dropdown + custom
   - Attendance-based monthly = per_day * 30
   - Fixed monthly = basic_salary
   - Save -> employee_bonus_report
   - Export -> monthly_salary_report (gross/net = bonus_amount) then handoff
   - Print -> opens minimal print window with only report HTML
   Updated: 2025-10-21
   ============================================================================ */

header('X-Frame-Options: SAMEORIGIN');
error_reporting(E_ALL); ini_set('display_errors', 1);

/* ---- Auth + DB ---- */
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';

/* ---- include header if present (robust) ---- */
$header_candidates = [
  __DIR__ . '/partials/header.php',
  __DIR__ . '/erp/partials/header.php',
  __DIR__ . '/../erp/partials/header.php',
  __DIR__ . '/../../erp/partials/header.php'
];
foreach ($header_candidates as $hf) if (file_exists($hf)) { require_once $hf; break; }

/* ---- 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(403); exit('Bad CSRF'); }}

/* ---- helpers ---- */
function ymd($y,$m,$d=1){ return sprintf('%04d-%02d-%02d',$y,$m,$d); }
function parse_ym($ym){ $p=preg_split('/[^0-9]+/',$ym); return count($p)>=2?['y'=>(int)$p[0],'m'=>(int)$p[1]]:null; }
function days_in_month($y,$m){ return (int)date('t', strtotime(ymd($y,$m,1))); }

/* ---- ensure employee_bonus_report table ---- */
$pdo->exec("CREATE TABLE IF NOT EXISTS employee_bonus_report (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  company_id BIGINT UNSIGNED NOT NULL,
  bonus_month DATE NOT NULL,
  employee_id BIGINT UNSIGNED NOT NULL,
  employee_name VARCHAR(191) NULL,
  department_id BIGINT UNSIGNED NULL,
  department_name VARCHAR(100) NULL,
  designation_name VARCHAR(100) NULL,
  monthly_salary DECIMAL(12,2) DEFAULT 0,
  bonus_percent DECIMAL(6,2) DEFAULT 0,
  bonus_amount DECIMAL(12,2) DEFAULT 0,
  payment_type ENUM('BANK','CASH','UPI') NULL,
  beneficiary_name VARCHAR(191) NULL,
  account_number VARCHAR(64) NULL,
  ifsc VARCHAR(20) NULL,
  paid_date DATE NULL,
  created_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_company_month_emp (company_id, bonus_month, employee_id),
  KEY idx_company_month (company_id, bonus_month)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;");

/* ---- inputs ---- */
$bonus_ym = $_GET['month'] ?? $_POST['month'] ?? date('Y-m');
$percent_selected = (float)($_GET['percent'] ?? $_POST['percent'] ?? 10.0);
$custom_percent = isset($_GET['custom_percent']) ? (float)$_GET['custom_percent'] : (float)($_POST['custom_percent'] ?? 0);
$dept = (int)($_GET['dept'] ?? $_POST['dept'] ?? 0);
$paid_date_default = date('Y-m-d');

$pp = parse_ym($bonus_ym);
if (!$pp) { $bonus_ym = date('Y-m'); $pp = parse_ym($bonus_ym); }
$by = $pp['y']; $bm = $pp['m']; $BONUS_MONTH_KEY = ymd($by,$bm,1);

/* decide percent: if custom provided >0 use it else dropdown value */
$bonus_percent = ($custom_percent > 0) ? $custom_percent : $percent_selected;

/* ---- departments ---- */
$stDept = $pdo->prepare("SELECT id, name FROM company_departments WHERE company_id=? ORDER BY name");
$stDept->execute([$COMPANY_ID]);
$departments = $stDept->fetchAll(PDO::FETCH_KEY_PAIR);

/* ---- employees ----
   fetch salary fields: salary_type_name, per_day (used as per-attendance), basic_salary
*/
$sql = "SELECT id, name, department_id, department_name, designation_name, salary_type_name, per_day, basic_salary, payment_type, beneficiary_name, account_number, ifsc
        FROM company_employee_master
        WHERE company_id=? AND COALESCE(is_active,0)=1 " . ($dept ? "AND department_id=?" : "") . " ORDER BY department_id, name";
$stEmp = $pdo->prepare($sql);
$stEmp->execute($dept ? [$COMPANY_ID, $dept] : [$COMPANY_ID]);
$emps = $stEmp->fetchAll(PDO::FETCH_ASSOC);

/* ---- first attendance check for months of service (optional display) ---- */
$stFirst = $pdo->prepare("SELECT MIN(period_start) FROM manual_attendance_data WHERE company_id=? AND employee_id=?");

/* ---- build calculated rows ---- */
$rows = []; $grand_bonus = 0;
foreach ($emps as $e) {
  $emp_id = (int)$e['id'];
  // determine monthly salary
  $stype = strtolower((string)($e['salary_type_name'] ?? ''));
  $per_day = (float)($e['per_day'] ?? 0);
  $basic = (float)($e['basic_salary'] ?? 0);

if (strpos($stype, 'attendance') !== false || $per_day > 0 && !$basic) {
    // attendance-based -> monthly = per_day * 30
    $monthly_salary = round($per_day * 30, 2);
  } else {
    // fixed or fallback
    $monthly_salary = round(max($basic, $per_day * 30), 2);
  }

// bonus amount = monthly_salary * percent / 100
  $bonus_amount = round($monthly_salary * ($bonus_percent / 100.0), 2);

// first attendance (for information)
  $stFirst->execute([$COMPANY_ID, $emp_id]);
  $first_att = $stFirst->fetchColumn();

$rows[] = [
    'employee_id' => $emp_id,
    'employee_name' => $e['name'],
    'department_id' => (int)($e['department_id'] ?? 0),
    'department_name' => $e['department_name'] ?? ($departments[(int)($e['department_id'] ?? 0)] ?? ''),
    'designation_name' => $e['designation_name'] ?? '',
    'salary_type_name' => $e['salary_type_name'] ?? '',
    'per_day' => $per_day,
    'basic_salary' => $basic,
    'monthly_salary' => $monthly_salary,
    'bonus_percent' => $bonus_percent,
    'bonus_amount' => $bonus_amount,
    'payment_type' => $e['payment_type'] ?: 'BANK',
    'beneficiary_name' => $e['beneficiary_name'] ?: $e['name'],
    'account_number' => $e['account_number'] ?? '',
    'ifsc' => $e['ifsc'] ?? '',
    'first_attendance' => $first_att ? date('Y-m-01', strtotime($first_att)) : null
  ];

$grand_bonus += $bonus_amount;
}

/* ---- SAVE ---- */
if (($_POST['action'] ?? '') === 'save') {
  check_csrf();
  $ins = $pdo->prepare("INSERT INTO employee_bonus_report
    (company_id, bonus_month, employee_id, employee_name, department_id, department_name, designation_name,
     monthly_salary, bonus_percent, bonus_amount, payment_type, beneficiary_name, account_number, ifsc, created_by)
    VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
    ON DUPLICATE KEY UPDATE
      monthly_salary=VALUES(monthly_salary), bonus_percent=VALUES(bonus_percent), bonus_amount=VALUES(bonus_amount),
      payment_type=VALUES(payment_type), beneficiary_name=VALUES(beneficiary_name), account_number=VALUES(account_number), ifsc=VALUES(ifsc),
      created_at=VALUES(created_at)
  ");
  $pdo->beginTransaction();
  foreach ($rows as $r) {
    $ins->execute([
      $COMPANY_ID, $BONUS_MONTH_KEY, $r['employee_id'], $r['employee_name'],
      $r['department_id'], $r['department_name'], $r['designation_name'],
      $r['monthly_salary'], $r['bonus_percent'], $r['bonus_amount'],
      $r['payment_type'], $r['beneficiary_name'], $r['account_number'], $r['ifsc'], $USER_ID
    ]);
  }
  $pdo->commit();
  header("Location: employee_bonus_calculator.php?month=" . urlencode($bonus_ym) . "&percent=" . urlencode($percent_selected) . "&saved=1");
  exit;
}

/* ---- EXPORT ----
   Inserts rows into monthly_salary_report with gross_amount/net_pay = bonus_amount,
   then hands off to payment_export_monthly.php
*/
if (($_POST['action'] ?? '') === 'export') {
  check_csrf();
  $paid_date = $_POST['paid_date'] ?? date('Y-m-d');

// 1) Save into employee_bonus_report
  $insB = $pdo->prepare("INSERT INTO employee_bonus_report
    (company_id, bonus_month, employee_id, employee_name, department_id, department_name, designation_name,
     monthly_salary, bonus_percent, bonus_amount, payment_type, beneficiary_name, account_number, ifsc, paid_date, created_by)
    VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
    ON DUPLICATE KEY UPDATE bonus_amount=VALUES(bonus_amount), paid_date=VALUES(paid_date)
  ");
  $pdo->beginTransaction();
  foreach ($rows as $r) {
    $insB->execute([
      $COMPANY_ID, $BONUS_MONTH_KEY, $r['employee_id'], $r['employee_name'],
      $r['department_id'], $r['department_name'], $r['designation_name'],
      $r['monthly_salary'], $r['bonus_percent'], $r['bonus_amount'],
      $r['payment_type'], $r['beneficiary_name'], $r['account_number'], $r['ifsc'], $paid_date, $USER_ID
    ]);
  }
  $pdo->commit();

// 2) Insert into monthly_salary_report (gross/net = bonus_amount)
  $insMSR = $pdo->prepare("
    INSERT INTO monthly_salary_report
      (company_id, month_key, employee_id, employee_name, department_id, department_name,
       payroll_period_name, salary_type_name, total_days_in_month, attendance_days, per_day,
       basic_salary, calc_basic_amount, extra_amount, deduction_amount, advance_deduct,
       advance_ids, gross_amount, net_pay, payment_type, beneficiary_name, account_number, ifsc,
       narration, created_by)
    VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
    ON DUPLICATE KEY UPDATE net_pay=VALUES(net_pay), gross_amount=VALUES(gross_amount), updated_at=NOW()
  ");
  $pdo->beginTransaction();
  $days_in_month_val = days_in_month($by, $bm);
  foreach ($rows as $r) {
    $insMSR->execute([
      $COMPANY_ID, $BONUS_MONTH_KEY, $r['employee_id'], $r['employee_name'],
      $r['department_id'], $r['department_name'],
      'Bonus', 'Bonus', $days_in_month_val, 0, 0,
      0,0,0,0,0,
      json_encode([], JSON_UNESCAPED_UNICODE),
      $r['bonus_amount'], $r['bonus_amount'], $r['payment_type'], $r['beneficiary_name'],
      $r['account_number'], $r['ifsc'],
      'Employee Bonus - ' . date('M-Y', strtotime($BONUS_MONTH_KEY)), $USER_ID
    ]);
  }
  $pdo->commit();

// 3) handoff to payment export
  ?>
  <form id="handoff" method="post" action="payment_export_monthly.php">
    <input type="hidden" name="source" value="monthly_salary_report">
    <input type="hidden" name="company_id" value="<?php echo htmlspecialchars($COMPANY_ID); ?>">
    <input type="hidden" name="month_key" value="<?php echo htmlspecialchars($BONUS_MONTH_KEY); ?>">
    <input type="hidden" name="dept" value="<?php echo htmlspecialchars($dept); ?>">
    <?php csrf_field(); ?>
  </form>
  <script>document.getElementById('handoff').submit();</script>
  <?php
  exit;
}

/* ---- Print (client-side window) ----
   We provide a client print function that opens a minimal window with only #reportContent and triggers print.
*/

/* ---- UI ---- */
?>
<!doctype html>
<html>
<head>
<meta charset="utf-8">
<title>Employee Bonus Calculator</title>

<style>
@media print {
  header,.header,.site-header,.main-header,.topbar,.top-nav,.navbar,.erp-topbar,.erp-company-card,.appbar,
  .erp-report-controls,.no-print,.breadcrumb,.page-header,.toolbar{ display:none !important; visibility:hidden !important; height:0 !important; margin:0 !important; padding:0 !important; overflow:hidden !important; }
  #reportContent{ display:block !important; width:100% !important; margin:0 !important; padding:0 !important; }
  @page { margin:12mm; }
  body{ margin:0; padding:0; }
}
table{ border-collapse:collapse; }
th,td{ border:1px solid #ddd; padding:6px 8px; }
th{ background:#f6f6f6; font-weight:700; }
</style>
</head>
<body>

<div class="erp-company-card" role="banner" aria-label="Company details">
  <div class="company-name"><?php
    try { $s=$pdo->prepare("SELECT name FROM companies WHERE id=? LIMIT 1"); $s->execute([$COMPANY_ID]); echo htmlspecialchars($s->fetchColumn() ?: 'Company'); }
    catch(Throwable $e){ echo 'Company'; }
  ?></div>
  <div class="company-sub">Company ID: <?php echo (int)$COMPANY_ID; ?></div>
</div>

<h2>Employee Bonus Calculator</h2>

<div class="erp-report-controls no-print" style="display:flex;align-items:center;flex-wrap:wrap;gap:8px;padding:8px;background:#f8fff8;border:1px solid #d8eed8;border-radius:8px;margin-bottom:10px;">
  <form method="get" style="display:flex;gap:8px;align-items:center;margin:0;">
    <label>Month <input type="month" name="month" value="<?php echo htmlspecialchars($bonus_ym); ?>" style="padding:4px"></label>

<label>Percent
      <select name="percent" style="padding:4px">
        <option value="5" <?php if($percent_selected==5) echo 'selected'; ?>>5%</option>
        <option value="10" <?php if($percent_selected==10) echo 'selected'; ?>>10%</option>
        <option value="15" <?php if($percent_selected==15) echo 'selected'; ?>>15%</option>
        <option value="20" <?php if($percent_selected==20) echo 'selected'; ?>>20%</option>
        <option value="25" <?php if($percent_selected==25) echo 'selected'; ?>>25%</option>
        <option value="50" <?php if($percent_selected==50) echo 'selected'; ?>>50%</option>
      </select>
    </label>

<label>Custom % <input type="number" step="0.01" name="custom_percent" value="<?php echo htmlspecialchars($custom_percent); ?>" style="width:80px;padding:4px"></label>

<label>Dept
      <select name="dept" style="padding:4px">
        <option value="0">All</option>
        <?php foreach($departments as $id=>$nm) echo '<option value="'.(int)$id.'"'.($dept==$id?' selected':'').'>'.htmlspecialchars($nm).'</option>'; ?>
      </select>
    </label>

<label>Paid <input type="date" name="paid_date" value="<?php echo htmlspecialchars($paid_date_default); ?>" style="padding:4px"></label>

<button name="action" value="save" type="submit">💾 Save</button>
    <button name="action" value="export" type="submit">🏦 Export</button>

<button type="button" onclick="openReportPrintWindow()" style="padding:6px 10px;">🖨️ Print</button>
  </form>

<div style="margin-left:auto;font-size:13px;">
    Total Bonus: <b><?php echo number_format($grand_bonus,2); ?></b>
  </div>
</div>

<div id="reportContent">
  <table style="width:100%;font-size:13px;">
    <thead>
      <tr>
        <th>#</th><th>Employee</th><th>Designation</th><th>Department</th>
        <th>Salary Type</th><th>Monthly Salary</th><th>Percent</th><th>Bonus Amount</th>
      </tr>
    </thead>
    <tbody>
      <?php $i=1; foreach($rows as $r): if($dept && $r['department_id'] != $dept) continue; ?>
      <tr>
        <td style="text-align:center"><?php echo $i++; ?></td>
        <td><?php echo htmlspecialchars($r['employee_name']); ?></td>
        <td><?php echo htmlspecialchars($r['designation_name']); ?></td>
        <td><?php echo htmlspecialchars($r['department_name']); ?></td>
        <td><?php echo htmlspecialchars($r['salary_type_name']); ?></td>
        <td style="text-align:right"><?php echo number_format($r['monthly_salary'],2); ?></td>
        <td style="text-align:center"><?php echo number_format($r['bonus_percent'],2); ?>%</td>
        <td style="text-align:right"><?php echo number_format($r['bonus_amount'],2); ?></td>
      </tr>
      <?php endforeach; ?>
    </tbody>
  </table>
</div>

<script>
function escapeHtml(s){ return String(s||'').replace(/[&<>"']/g, function(ch){ return ({'&':'&amp;','<':'&lt;','>':'&gt;','"':'&quot;',"'":'&#39;'}[ch]); }); }

function openReportPrintWindow(){
  var content = document.getElementById('reportContent');
  if(!content){ alert('Report content missing'); return; }

var companyName = (document.querySelector('.company-name') || {}).textContent || '';
  var title = (companyName.trim()?companyName.trim() + ' - Employee Bonus' : 'Employee Bonus');
  var paidInput = document.querySelector('input[name="paid_date"]');
  var paidDate = paidInput ? paidInput.value : '';

var html = '<!doctype html><html><head><meta charset="utf-8"><title>' + escapeHtml(title) + '</title>' + css + '</head><body>';
  html += '<h2>' + escapeHtml(title) + ' (' + escapeHtml('<?php echo addslashes($bonus_ym); ?>') + ')</h2>';
  if(paidDate) html += '<div style="text-align:center;margin-bottom:8px">Paid Date: ' + escapeHtml(paidDate) + '</div>';
  html += '<div>' + content.innerHTML + '</div>';
  html += '</body></html>';

<?php
/* ---- footer include (robust) ---- */
$footer_candidates = [
  __DIR__ . '/partials/footer.php',
  __DIR__ . '/erp/partials/footer.php',
  __DIR__ . '/../erp/partials/footer.php',
  __DIR__ . '/../../erp/partials/footer.php'
];
foreach ($footer_candidates as $ff) if (file_exists($ff)) { require_once $ff; break; }
?>
</body>
</html>