View Code - 20260721

new_semi_salary.php | 20260721_154033.php Initial Bulk Import

<?php
/* =============================================================================
   File: /erp/semi_monthly_salary_report.php
   Purpose: Semi-monthly salaries UI + SAVE + EXPORT
   CHANGE (ONLY):
     - Payment Export now POSTS salary_rows (UI data) directly to
       employee_salary_export.php (no DB re-read).
============================================================================= */
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'; }

/* ---- Header include (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');}}

/* ---- Company name ---- */
try {
  $stComp = $pdo->prepare("SELECT name FROM companies WHERE id = ? LIMIT 1");
  $stComp->execute([$COMPANY_ID]);
  $company_name = $stComp->fetchColumn() ?: 'Company';
} catch (Throwable $e) { $company_name = 'Company'; }

/* ---- Ensure table (unchanged) ---- */
$pdo->exec(<<<SQL
CREATE TABLE IF NOT EXISTS semi_monthly_salary_report (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  company_id BIGINT UNSIGNED NOT NULL,
  month_key DATE NOT NULL,
  period_label VARCHAR(8) NOT NULL,
  employee_id BIGINT UNSIGNED NOT NULL,
  employee_name VARCHAR(191) NULL,
  department_id BIGINT UNSIGNED NULL,
  department_name VARCHAR(100) NULL,
  payroll_period_name VARCHAR(50) NOT NULL,
  salary_type_name VARCHAR(50) NOT NULL,
  total_days_in_month INT UNSIGNED NOT NULL,
  days_in_half INT UNSIGNED NOT NULL,
  attendance_days DECIMAL(10,2) DEFAULT 0,
  per_day DECIMAL(12,2) DEFAULT 0,
  basic_salary DECIMAL(12,2) DEFAULT 0,
  calc_basic_amount DECIMAL(12,2) DEFAULT 0,
  extra_amount DECIMAL(12,2) DEFAULT 0,
  deduction_amount DECIMAL(12,2) DEFAULT 0,
  advance_deduct DECIMAL(12,2) DEFAULT 0,
  advance_ids JSON NULL,
  gross_amount DECIMAL(12,2) DEFAULT 0,
  net_pay 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,
  narration VARCHAR(191) NULL,
  remarks VARCHAR(255) NULL,
  locked TINYINT(1) NOT NULL DEFAULT 0,
  exported_at DATETIME NULL,
  payment_batch_id VARCHAR(64) NULL,
  created_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_company_month_half_emp (company_id, month_key, period_label, employee_id),
  KEY idx_company_month_half (company_id, month_key, period_label),
  KEY idx_company_dept (company_id, department_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
SQL);

/* ---- Helpers ---- */
function ymd($y,$m,$d=1){ return sprintf('%04d-%02d-%02d',$y,$m,$d); }
function first_day($y,$m){ return ymd($y,$m,1); }
function last_day($y,$m){ return date('Y-m-t', strtotime(ymd($y,$m,1))); }
function days_in_month($y,$m){ return (int)date('t', strtotime(ymd($y,$m,1))); }
function money0($n){ return (string)round((float)$n, 0); }

/* ---- Inputs ---- */
$year   = (int)($_GET['y'] ?? $_POST['y'] ?? date('Y'));
$month  = (int)($_GET['m'] ?? $_POST['m'] ?? date('n'));
$dept   = (int)($_GET['dept'] ?? $_POST['dept'] ?? 0);
$half   = ($_GET['half'] ?? $_POST['half'] ?? 'H1') === 'H2' ? 'H2' : 'H1';

$MONTH_KEY = first_day($year,$month);
$TOTAL_DAYS = days_in_month($year,$month);
if ($half==='H1'){ $FROM=ymd($year,$month,1); $TO=ymd($year,$month,15); $DAYS_IN_HALF=15; }
else { $FROM=ymd($year,$month,16); $TO=last_day($year,$month); $DAYS_IN_HALF=max(0,$TOTAL_DAYS-15); }

/* ---- 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 (unchanged rules) ---- */
$sqlEmp="SELECT id,name,department_id,department_name,designation_name,salary_type_name,payroll_period_name,
                per_day,basic_salary,payment_type,beneficiary_name,account_number,ifsc
         FROM company_employee_master
         WHERE company_id=? AND payroll_period_name='Semi-monthly'
           AND salary_type_name IN ('Attendance','Fixed','Fixed Attendance')
           AND COALESCE(is_active,0)=1 ".($dept?"AND department_id=?":"")."
         ORDER BY department_id,name";
$stEmp=$pdo->prepare($sqlEmp);
$stEmp->execute($dept?[$COMPANY_ID,$dept]:[$COMPANY_ID]);
$emps=$stEmp->fetchAll(PDO::FETCH_ASSOC);

/* ---- Attendance ---- */
$stAtt = $pdo->prepare("
  SELECT COALESCE(SUM(total_attendance), 0)
  FROM manual_attendance_data
  WHERE company_id = ?
    AND employee_id = ?
    AND period_start >= ?
    AND period_end <= ?
");

/* ---- Extras/Deductions ---- */
$stExtra=$pdo->prepare("SELECT COALESCE(SUM(amount),0) FROM employee_extra
                        WHERE company_id=? AND employee_id=? AND entry_date BETWEEN ? AND ?");
$stDed=$pdo->prepare("SELECT COALESCE(SUM(amount),0) FROM employee_deduction
                      WHERE company_id=? AND employee_id=? AND entry_date BETWEEN ? AND ?");

/* ---- Advances ---- */
$stAdv=$pdo->prepare("SELECT id, remaining_amount, `date`
                      FROM employee_advance
                      WHERE company_id=? AND employee_id=? AND status='OPEN'
                        AND `date` BETWEEN ? AND ?");

/* ---- Regenerate ---- */
if (($_POST['action'] ?? '') === 'regenerate') {
  check_csrf();
  header("Location: semi_monthly_salary_report.php?y={$year}&m={$month}&dept={$dept}&half={$half}&regen=1");
  exit;
}

/* ---- Clear ---- */
if (($_POST['action'] ?? '') === 'clear') {
  check_csrf();
  $sql = "DELETE FROM semi_monthly_salary_report
          WHERE company_id = ? AND month_key = ? AND period_label = ?";
  $params = [$COMPANY_ID, $MONTH_KEY, $half];
  if ($dept) { $sql .= " AND department_id = ?"; $params[] = $dept; }
  $st = $pdo->prepare($sql);
  $st->execute($params);
  header("Location: semi_monthly_salary_report.php?y={$year}&m={$month}&dept={$dept}&half={$half}&cleared=1");
  exit;
}

/* ---- Compute rows (UNCHANGED) ---- */
$rows=[]; $grand_bank=0; $grand_cash=0; $grand_net=0; $grand_gross=0;

foreach($emps as $e){
  $emp_id=(int)$e['id']; $dept_id=(int)$e['department_id'];
  $dept_nm=$e['department_name'] ?: ($departments[$dept_id] ?? '—');
  $stype=trim((string)$e['salary_type_name']);
  $per_day=(float)($e['per_day'] ?? 0);
  $basic=(float)($e['basic_salary'] ?? 0);

$stAtt->execute([$COMPANY_ID, $emp_id, $FROM, $TO]);
  $att_days=(float)$stAtt->fetchColumn();

if($per_day<=0 && $basic>0) $per_day=$basic/max($TOTAL_DAYS,1);

if      (strcasecmp($stype,'Attendance')===0)       $calc_basic=$per_day*$att_days;
  elseif  (strcasecmp($stype,'Fixed')===0)            $calc_basic=($basic>0)?($basic*($DAYS_IN_HALF/max($TOTAL_DAYS,1))):0;
  elseif  (strcasecmp($stype,'Fixed Attendance')===0) $calc_basic=($att_days>0?$per_day*$att_days:($basic*($DAYS_IN_HALF/max($TOTAL_DAYS,1))));
  else                                                $calc_basic=$basic>0?($basic*($DAYS_IN_HALF/max($TOTAL_DAYS,1))):($per_day*$att_days);

$stExtra->execute([$COMPANY_ID,$emp_id,$FROM,$TO]); $extra=(float)$stExtra->fetchColumn();
  $stDed->execute([$COMPANY_ID,$emp_id,$FROM,$TO]);   $ded=(float)$stDed->fetchColumn();

$stAdv->execute([$COMPANY_ID,$emp_id,$FROM,$TO]);
  $adv_ids=[]; $adv_deduct=0.0;
  foreach($stAdv->fetchAll(PDO::FETCH_ASSOC) as $a){
    $rem=(float)$a['remaining_amount']; if($rem<=0) continue;
    $adv_deduct += $rem; $adv_ids[]=(int)$a['id'];
  }

$gross=$calc_basic+$extra; $net=round($gross-$ded-$adv_deduct,0);

$pay_type=$e['payment_type'] ?: 'BANK';
  if($pay_type==='BANK') $grand_bank+=$net;
  if($pay_type==='CASH') $grand_cash+=$net;
  $grand_net+=$net;
  $grand_gross+=$gross;

$rows[]=[
    'employee_id'=>$emp_id,'employee_name'=>$e['name'],
    'designation_name'=>$e['designation_name'] ?? '',
    'department_id'=>$dept_id,'department'=>$dept_nm,'salary_type'=>$stype,
    'per_day'=>$per_day,'basic_salary'=>$basic,'attendance'=>$att_days,
    'calc_basic'=>$calc_basic,'extra'=>$extra,'deduction'=>$ded,
    'advance'=>$adv_deduct,'gross'=>$gross,'net'=>$net,
    'advance_ids'=>$adv_ids,'payment_type'=>$pay_type,
    'beneficiary_name'=>$e['beneficiary_name'] ?: $e['name'],
    'account_number'=>$e['account_number'] ?? '',
    'ifsc'=>$e['ifsc'] ?? ''
  ];
}

/* ---- SAVE (UNCHANGED) ---- */
if (($_POST['action'] ?? '') === 'save'){
  check_csrf();
  $ins=$pdo->prepare("
    INSERT INTO semi_monthly_salary_report
      (company_id, month_key, period_label, employee_id, employee_name, department_id, department_name,
       payroll_period_name, salary_type_name, total_days_in_month, days_in_half, 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
      employee_name=VALUES(employee_name), department_id=VALUES(department_id),
      department_name=VALUES(department_name), salary_type_name=VALUES(salary_type_name),
      total_days_in_month=VALUES(total_days_in_month), days_in_half=VALUES(days_in_half),
      attendance_days=VALUES(attendance_days), per_day=VALUES(per_day),
      basic_salary=VALUES(basic_salary), calc_basic_amount=VALUES(calc_basic_amount),
      extra_amount=VALUES(extra_amount), deduction_amount=VALUES(deduction_amount),
      advance_deduct=VALUES(advance_deduct), advance_ids=VALUES(advance_ids),
      gross_amount=VALUES(gross_amount), net_pay=VALUES(net_pay),
      payment_type=VALUES(payment_type), beneficiary_name=VALUES(beneficiary_name),
      account_number=VALUES(account_number), ifsc=VALUES(ifsc),
      narration=VALUES(narration), updated_at=NOW()
  ");
  $pdo->beginTransaction();
  foreach($rows as $r){
    $ins->execute([
      $COMPANY_ID,$MONTH_KEY,$half,$r['employee_id'],$r['employee_name'],
      $r['department_id'],$r['department'],'Semi-monthly',$r['salary_type'],
      $TOTAL_DAYS,$DAYS_IN_HALF,$r['attendance'],$r['per_day'],$r['basic_salary'],
      $r['calc_basic'],$r['extra'],$r['deduction'],$r['advance'],
      json_encode($r['advance_ids'], JSON_UNESCAPED_UNICODE),
      $r['gross'],$r['net'],$r['payment_type'],
      $r['beneficiary_name'],$r['account_number'],$r['ifsc'],
      'Semi-monthly '.$half.' - '.date('M-Y', strtotime($MONTH_KEY)),$USER_ID
    ]);
  }
  $pdo->commit();
  header("Location: semi_monthly_salary_report.php?y={$year}&m={$month}&dept={$dept}&half={$half}&saved=1");
  exit;
}

/* ---- EXPORT (CHANGED: UI -> salary_rows) ---- */
if (($_POST['action'] ?? '') === 'export'){
  check_csrf();

$salary_rows=[];
  foreach($rows as $r){
    if (($r['net'] ?? 0) > 0){
      $salary_rows[]=[
        'employee_id'=>(int)$r['employee_id'],
        'amount'=>(float)$r['net']
      ];
    }
  }

if (!$salary_rows){
    http_response_code(400);
    exit('No salary rows to export');
  }
  ?>
  <form id="handoff" method="post" action="employee_salary_export.php">
    <?php csrf_field(); ?>
    <input type="hidden" name="salary_rows"
      value='<?php echo htmlspecialchars(json_encode($salary_rows, JSON_UNESCAPED_UNICODE)); ?>'>
  </form>
  <script>document.getElementById('handoff').submit();</script>
  <?php
  exit;
}

/* ---- UI (UNCHANGED) ---- */
?>
<!doctype html>
<html>
<head>
<meta charset="utf-8"><title>Semi-monthly Salary Report</title>
</head>
<body>


<?php
/* ---- Footer include ---- */
$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>