View Code - 20260721

semi_monthly_salary_report.php | 20260721_154033.php Initial Bulk Import

<?php
/* =============================================================================
   File: /erp/semi_monthly_salary_report.php
   Purpose: Semi-monthly salaries into semi_monthly_salary_report
   UI tweaks: controls single-row .no-print, print opens new window (no header/footer),
            totals order Gross->Net->Bank->Cash, designation next to employee name,
            exclude inactive employees (COALESCE(is_active,0)=1).
   Core business logic unchanged.
============================================================================= */
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'; }

/* ---- Dompdf bootstrap (safe) ---- */
$PDF_AVAILABLE = false;
$dompdf_autoloaders = [
    __DIR__ . '/lib/dompdf/autoload.inc.php',
    __DIR__ . '/vendor/autoload.php',
];
foreach ($dompdf_autoloaders as $autoload) {
    if (is_file($autoload)) {
        require_once $autoload;
        if (class_exists(\Dompdf\Dompdf::class)) {
            $PDF_AVAILABLE = true;
            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');}}

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

/* ---- Ensure table ---- */
$ddl = <<<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;
$pdo->exec($ddl);

/* ---- 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';
$half   = ($half==='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 (Semi-monthly) + bank + payment_type;
       exclude Work Basis and exclude inactive employees; include designation_name */
$sqlEmp="SELECT id,name,department_id,department_name,designation_name,
                salary_type_name,payroll_period_name,
                per_day,basic_salary,
                h1_fixed_amount,h2_fixed_amount,
                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 for half (rows fully inside half counted fully) ---- */
$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 for half ---- */
$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 FULL in selected half ---- */
$stAdv=$pdo->prepare("SELECT id, remaining_amount, `date`
                      FROM employee_advance
                      WHERE company_id=? AND employee_id=? AND status='OPEN'
                        AND `date` BETWEEN ? AND ?");

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

/* ---- ACTION: CLEAR SAVED (module-local) ---- */
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 && (int)$dept > 0) { $sql .= " AND department_id = ?"; $params[] = (int)$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 from live data (always) ---- */
$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) {

// If custom semi split exists
    if (
        strcasecmp($e['payroll_period_name'],'Semi-monthly')===0
        && (
            (float)$e['h1_fixed_amount'] > 0
            || (float)$e['h2_fixed_amount'] > 0
        )
    ) {

if ($half === 'H1') {
            $calc_basic = (float)$e['h1_fixed_amount'];
        } else {
            $calc_basic = (float)$e['h2_fixed_amount'];
        }

} else {
        // fallback old proportional logic
        $calc_basic = ($basic>0)
            ? ($basic * ($DAYS_IN_HALF / max($TOTAL_DAYS,1)))
            : 0;
    }
}
 elseif (strcasecmp($stype,'Fixed Attendance')===0) {

// monthly / total_days * attendance_days
    $per_day_calc = ($basic > 0)
        ? ($basic / max($TOTAL_DAYS, 1))
        : $per_day;

$calc_basic = $per_day_calc * $att_days;
}
  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();

// advances in half
  $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'] ?? ''
  ];
}

/* ---- PDF EXPORT ---- */
if (($_GET['export'] ?? '') === 'pdf') {
    if (!$PDF_AVAILABLE) {
        http_response_code(503);
        header('Content-Type: text/html; charset=UTF-8');
        echo '<h3>PDF export unavailable</h3><p>Dompdf is not installed on this server.</p>';
        exit;
    }

$pdfByDept = [];
    foreach ($rows as $row) {
        $pdfByDept[$row['department']][] = $row;
    }

ob_start();
    ?>
    <!doctype html>
    <html>
    <head>
    <meta charset="utf-8">
    <title>Semi-monthly Salary Report PDF</title>
    <style>
        @page { margin: 14px 16px; }
        body { margin: 0; font-family: DejaVu Sans, sans-serif; font-size: 8.2px; color: #111; }
        h1 { margin: 0 0 4px; font-size: 13px; }
        .subhead { margin: 0 0 10px; font-size: 9px; color: #333; }
        .summary { width: 100%; border-collapse: collapse; margin-bottom: 10px; }
        .summary td { border: 1px solid #d7d7d7; padding: 5px 6px; width: 25%; }
        .summary .label { font-size: 7px; color: #666; text-transform: uppercase; }
        .summary .value { font-size: 11px; font-weight: 700; margin-top: 2px; }
        .dept { margin-top: 18px; }
        .dept-head { margin-bottom: 8px; padding-bottom: 4px; border-bottom: 1px solid #ddd; }
        .dept-title { font-size: 11px; font-weight: 700; }
        .dept-meta { font-size: 8px; color: #444; margin-top: 2px; }
        table.report { width: 100%; border-collapse: collapse; table-layout: fixed; margin-bottom: 12px; }
        table.report th, table.report td { border: 1px solid #dadada; padding: 3px 2px; vertical-align: middle; }
        table.report th { background: #f3f3f3; font-size: 7.4px; word-break: break-word; }
        table.report td { font-size: 7.6px; }
        .text-right { text-align: right; }
        .text-center { text-align: center; }
        .text-left { text-align: left; }
        .emp-name { font-weight: 700; }
        .designation { display: block; margin-top: 2px; font-size: 6.8px; color: #666; }
        .net { font-weight: 700; background: #faf6dd; }
        .totals td { background: #f8f8f8; font-weight: 700; }
        .col-sr { width: 4.5%; }
        .col-name { width: 24%; }
        .col-pay { width: 6.5%; }
        .col-type { width: 8%; }
        .col-num { width: 7.125%; }
    </style>
    </head>
    <body>
        <h1><?= htmlspecialchars($company_name, ENT_QUOTES, 'UTF-8') ?></h1>
        <div class="subhead">Semi-monthly Salary Report | <?= htmlspecialchars(($half==='H1'?'1-15':'16-End') . ' ' . date('M Y', strtotime($MONTH_KEY)), ENT_QUOTES, 'UTF-8') ?></div>

<table class="summary">
            <tr>
                <td><div class="label">Gross Payable</div><div class="value">Rs. <?= number_format($grand_gross, 0) ?></div></td>
                <td><div class="label">Net Disbursement</div><div class="value">Rs. <?= money0($grand_net) ?></div></td>
                <td><div class="label">Bank Transfer</div><div class="value">Rs. <?= money0($grand_bank) ?></div></td>
                <td><div class="label">Cash Payment</div><div class="value">Rs. <?= money0($grand_cash) ?></div></td>
            </tr>
        </table>

<?php foreach ($pdfByDept as $deptName => $list): ?>
            <?php
            $totals = ['basic'=>0, 'extra'=>0, 'ded'=>0, 'adv'=>0, 'gross'=>0, 'net'=>0, 'bank'=>0, 'cash'=>0];
            foreach ($list as $item) {
                $totals['basic'] += $item['calc_basic'];
                $totals['extra'] += $item['extra'];
                $totals['ded'] += $item['deduction'];
                $totals['adv'] += $item['advance'];
                $totals['gross'] += $item['gross'];
                $totals['net'] += $item['net'];
                if ($item['payment_type'] === 'BANK') $totals['bank'] += $item['net'];
                if ($item['payment_type'] === 'CASH') $totals['cash'] += $item['net'];
            }
            ?>
            <div class="dept">
                <div class="dept-head">
                    <div class="dept-title"><?= htmlspecialchars($deptName, ENT_QUOTES, 'UTF-8') ?></div>
                    <div class="dept-meta">Employees: <?= count($list) ?> | Gross: <?= number_format($totals['gross'], 0) ?> | Bank: <?= money0($totals['bank']) ?> | Cash: <?= money0($totals['cash']) ?></div>
                </div>

<table class="report">
                    <colgroup>
                        <col class="col-sr">
                        <col class="col-name">
                        <col class="col-pay">
                        <col class="col-type">
                        <col class="col-num"><col class="col-num"><col class="col-num"><col class="col-num"><col class="col-num"><col class="col-num"><col class="col-num"><col class="col-num">
                    </colgroup>
                    <thead>
                        <tr>
                            <th>#</th>
                            <th class="text-left">Employee Name</th>
                            <th>Pay</th>
                            <th>Type</th>
                            <th>Rate</th>
                            <th>Days</th>
                            <th>Basic</th>
                            <th>Extra</th>
                            <th>Ded.</th>
                            <th>Adv.</th>
                            <th>Gross</th>
                            <th>Net</th>
                        </tr>
                    </thead>
                    <tbody>
                        <?php $index = 1; foreach ($list as $item): ?>
                            <tr>
                                <td class="text-center"><?= $index++ ?></td>
                                <td class="text-left">
                                    <span class="emp-name"><?= htmlspecialchars($item['employee_name'], ENT_QUOTES, 'UTF-8') ?></span>
                                    <?php if (!empty($item['designation_name'])): ?>
                                        <span class="designation"><?= htmlspecialchars($item['designation_name'], ENT_QUOTES, 'UTF-8') ?></span>
                                    <?php endif; ?>
                                </td>
                                <td class="text-center"><?= htmlspecialchars($item['payment_type'], ENT_QUOTES, 'UTF-8') ?></td>
                                <td class="text-center"><?= htmlspecialchars((strcasecmp($item['salary_type'], 'Attendance')===0 ? 'Att.' : $item['salary_type']), ENT_QUOTES, 'UTF-8') ?></td>
                                <td class="text-right"><?= number_format($item['per_day'], 0) ?></td>
                                <td class="text-right"><?= number_format($item['attendance'], 2) ?></td>
                                <td class="text-right"><?= number_format($item['calc_basic'], 0) ?></td>
                                <td class="text-right"><?= number_format($item['extra'], 0) ?></td>
                                <td class="text-right"><?= number_format($item['deduction'], 0) ?></td>
                                <td class="text-right"><?= number_format($item['advance'], 0) ?></td>
                                <td class="text-right"><?= number_format($item['gross'], 0) ?></td>
                                <td class="text-right net"><?= money0($item['net']) ?></td>
                            </tr>
                        <?php endforeach; ?>
                    </tbody>
                    <tfoot>
                        <tr class="totals">
                            <td colspan="6" class="text-center">Total for <?= htmlspecialchars($deptName, ENT_QUOTES, 'UTF-8') ?></td>
                            <td class="text-right"><?= number_format($totals['basic'], 2) ?></td>
                            <td class="text-right"><?= number_format($totals['extra'], 2) ?></td>
                            <td class="text-right"><?= number_format($totals['ded'], 2) ?></td>
                            <td class="text-right"><?= number_format($totals['adv'], 2) ?></td>
                            <td class="text-right"><?= number_format($totals['gross'], 2) ?></td>
                            <td class="text-right net"><?= money0($totals['net']) ?></td>
                        </tr>
                    </tfoot>
                </table>
            </div>
        <?php endforeach; ?>
    </body>
    </html>
    <?php
    $pdfHtml = ob_get_clean();

$dompdf = new \Dompdf\Dompdf([
        'isRemoteEnabled' => false,
        'defaultFont' => 'DejaVu Sans',
    ]);
    $dompdf->loadHtml($pdfHtml, 'UTF-8');
    $dompdf->setPaper('A4', 'portrait');
    $dompdf->render();
    $filename = 'semi_monthly_salary_' . $year . '_' . str_pad((string)$month, 2, '0', STR_PAD_LEFT) . '_' . strtolower($half) . '.pdf';
    $dompdf->stream($filename, ['Attachment' => true]);
    exit;
}

/* ---- SAVE ---- */
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 (auto-save first) ---- */
if (($_POST['action'] ?? '') === 'export'){
  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();
  ?>
  <form id="handoff" method="post" action="employee_salary_export.php">
    <input type="hidden" name="source" value="semi_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($MONTH_KEY); ?>">
    <input type="hidden" name="period_label" value="<?php echo htmlspecialchars($half); ?>">
    <input type="hidden" name="dept" value="<?php echo htmlspecialchars($dept); ?>">
    <?php csrf_field(); ?>
  </form>
  <script>document.getElementById('handoff').submit();</script>
  <?php exit;
}

/* ---- UI ---- */

/* ---- robust include for header (try multiple candidate paths) ---- */
$header_candidates = [
    __DIR__ . '/partials/header.php',
    __DIR__ . '/erp/partials/header.php',
    __DIR__ . '/../erp/partials/header.php',
    __DIR__ . '/../../erp/partials/header.php'
];
$header_included = false;
foreach ($header_candidates as $hf) {
    if (file_exists($hf)) { require_once $hf; $header_included = true; break; }
}
if (!$header_included) {
    error_log("semi_monthly_salary_report: header.php not found. Tried: " . implode(', ', $header_candidates));
    echo "<div style=\"padding:8px;background:#f8d7da;color:#721c24;border-radius:6px;margin-bottom:10px;\">System header missing</div>";
}

?>
<!doctype html>
<html>
<head>
<meta charset="utf-8"><title>Semi-monthly Salary Report</title>
</head>
<body>
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css" rel="stylesheet">
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap-icons@1.10.0/font/bootstrap-icons.css">

.erp-company-card { 
        background: white; 
        border-bottom: 2px solid #34A853; 
    }

.appbar { 
        background: #212529; 
        color: white; 
        padding: 12px 0; 
    }

.summary-card { 
        border: none; 
        border-radius: 12px; 
        transition: transform 0.2s; 
    }

.summary-card:hover { 
        transform: translateY(-3px); 
    }

.table-salary thead th { 
        background-color: #f1f3f5; 
        font-size: 0.75rem; 
        color: #666; 
        font-weight: 700; 
    }

/* ================= PRINT STYLING ================= */

@media print {

@page {
        margin: 5mm;
    }

body {
        font-size: 11px;
        background: #fff !important;
    }

.no-print {
        display: none !important;
    }

.container-fluid {
        padding: 0 !important;
        margin: 0 !important;
    }

#reportContent {
        padding: 5px !important;
    }

table {
        width: 100% !important;
        table-layout: fixed;
        font-size: 10.5px;
    }

th, td {
        padding: 4px 6px !important;
    }

.dept-section {
        page-break-inside: avoid;
        break-inside: avoid;
    }

tr {
        page-break-inside: avoid;
        break-inside: avoid;
    }

}
  .col-md-3 {
        flex: 0 0 25% !important;
        max-width: 25% !important;
    }
</style>

<div class="erp-company-card p-4 mb-0">
    <div class="container-fluid d-flex justify-content-between align-items-center">
        <div>
            <h2 class="fw-bold mb-0 text-dark"><?= htmlspecialchars($company_name); ?></h2>
        </div>
        <div class="text-end no-print">
            <button class="btn btn-outline-dark btn-sm rounded-pill px-3" onclick="window.print()">
                <i class="bi bi-printer me-1"></i> Print / PDF
            </button>
        </div>
    </div>
</div>

<div class="appbar shadow-sm mb-4">
    <div class="container-fluid d-flex justify-content-between align-items-center">
        <div class="fw-bold fs-5">Semi-monthly Salary Report</div>
        <div class="badge bg-success fs-6">
            <i class="bi bi-calendar3 me-2"></i>
            <?= ($half==='H1'?'1–15':'16–End').' • '.date('M Y', strtotime($MONTH_KEY)); ?>
        </div>
    </div>
</div>

<div class="container-fluid">
    <?php if(!empty($_GET['cleared'])): ?>
        <div class="alert alert-warning border-0 shadow-sm"><i class="bi bi-trash me-2"></i> Saved rows deleted for selected scope.</div>
    <?php endif; ?>
    <?php if(!empty($_GET['regen'])): ?>
        <div class="alert alert-info border-0 shadow-sm"><i class="bi bi-arrow-repeat me-2"></i> Report regenerated from live data.</div>
    <?php endif; ?>
    <?php if(!empty($_GET['saved'])): ?>
        <div class="alert alert-success border-0 shadow-sm"><i class="bi bi-check-circle me-2"></i> Generated rows saved to database.</div>
    <?php endif; ?>

<div class="card shadow-sm mb-4 no-print border-0">
        <div class="card-body p-3">
            <div class="row align-items-center g-3">
                <div class="col-xl-6 col-lg-7">
                    <form method="get" class="d-flex flex-wrap gap-2 align-items-center">
                        <select name="m" class="form-select form-select-sm w-auto">
                            <?php for($i=1;$i<=12;$i++): ?>
                                <option value="<?= $i; ?>" <?= ($i==$month?'selected':''); ?>><?= date('M', mktime(0,0,0,$i,1,$year)); ?></option>
                            <?php endfor; ?>
                        </select>
                        <input type="number" name="y" value="<?= $year; ?>" class="form-control form-control-sm w-auto" style="max-width:85px;">
                        
                        <div class="btn-group btn-group-sm mx-1">
                            <input type="radio" class="btn-check" name="half" id="h1" value="H1" <?= ($half==='H1'?'checked':''); ?>>
                            <label class="btn btn-outline-secondary" for="h1">1–15</label>
                            <input type="radio" class="btn-check" name="half" id="h2" value="H2" <?= ($half==='H2'?'checked':''); ?>>
                            <label class="btn btn-outline-secondary" for="h2">16–End</label>
                        </div>

<select name="dept" class="form-select form-select-sm w-auto">
                            <option value="0">All Departments</option>
                            <?php foreach($departments as $id=>$nm): ?>
                                <option value="<?= (int)$id; ?>" <?= ($dept==$id?'selected':''); ?>><?= htmlspecialchars($nm); ?></option>
                            <?php endforeach; ?>
                        </select>
                        <button type="submit" class="btn btn-primary btn-sm px-3">Show</button>
                    </form>
                </div>

<div class="col-xl-6 col-lg-5 d-flex justify-content-lg-end gap-2 flex-wrap">
                    <?php $pdf_qs = http_build_query(['y' => $year, 'm' => $month, 'dept' => $dept, 'half' => $half, 'export' => 'pdf']); ?>
                    <form method="post" id="mainActions" class="d-flex gap-2">
                        <?php csrf_field(); ?>
                        <input type="hidden" name="y" value="<?= $year; ?>">
                        <input type="hidden" name="m" value="<?= $month; ?>">
                        <input type="hidden" name="dept" value="<?= $dept; ?>">
                        <input type="hidden" name="half" value="<?= htmlspecialchars($half); ?>">

<button name="action" value="save" class="btn btn-success btn-sm"><i class="bi bi-cloud-arrow-up"></i> Save</button>
                        <button name="action" value="export" class="btn btn-info btn-sm text-white"><i class="bi bi-bank"></i> Export</button>
                        <button name="action" value="regenerate" class="btn btn-secondary btn-sm"><i class="bi bi-arrow-clockwise"></i> Regen</button>
                        <button name="action" value="clear" class="btn btn-outline-danger btn-sm" onclick="return confirm('Clear saved data?')"><i class="bi bi-eraser"></i> Clear</button>
                    </form>
                    <a href="?<?= htmlspecialchars($pdf_qs, ENT_QUOTES, 'UTF-8'); ?>" class="btn btn-danger btn-sm"><i class="bi bi-file-earmark-pdf"></i> Export PDF</a>
                    <div class="vr mx-2 d-none d-md-block"></div>
                    <div class="small text-muted align-self-center">Count: <b><?= count($rows); ?></b></div>
                </div>
            </div>
        </div>
    </div>

<div class="row g-3 mb-4">
        <div class="col-md-3">
            <div class="card summary-card shadow-sm border-start border-4 border-secondary">
                <div class="card-body">
                    <div class="text-muted small fw-bold">GROSS PAYABLE</div>
                    <div class="fs-4 fw-bold">₹<?= number_format($grand_gross,0); ?></div>
                </div>
            </div>
        </div>
        <div class="col-md-3">
            <div class="card summary-card shadow-sm border-start border-4 border-success">
                <div class="card-body">
                    <div class="text-muted small fw-bold">NET DISBURSEMENT</div>
                    <div class="fs-4 fw-bold text-success"><?= money0($grand_net); ?></div>
                </div>
            </div>
        </div>
        <div class="col-md-3">
            <div class="card summary-card shadow-sm border-start border-4 border-primary">
                <div class="card-body">
                    <div class="text-muted small fw-bold">BANK TRANSFER</div>
                    <div class="fs-4 fw-bold text-primary"><?= money0($grand_bank); ?></div>
                </div>
            </div>
        </div>
        <div class="col-md-3">
            <div class="card summary-card shadow-sm border-start border-4 border-warning">
                <div class="card-body">
                    <div class="text-muted small fw-bold">CASH PAYMENT</div>
                    <div class="fs-4 fw-bold text-dark"><?= money0($grand_cash); ?></div>
                </div>
            </div>
        </div>
    </div>

<?php
// Build department wise totals (NET based)
$deptTotals = [];

foreach ($rows as $r) {
    $dname = $r['department'];
    if (!isset($deptTotals[$dname])) {
        $deptTotals[$dname] = 0;
    }
    $deptTotals[$dname] += $r['net'];  // You can change to gross if needed
}
?>

<?php foreach ($deptTotals as $dname => $amt): ?>
                <div>
                    <?= htmlspecialchars($dname) ?>:
                    <span class="text-dark">
                        <?= number_format($amt, 0); ?>
                    </span>
                </div>
            <?php endforeach; ?>

</div>
    </div>
</div>

<div id="reportContent" class="bg-white p-4 shadow-sm rounded-4 mb-5">
        <?php
        $byDept=[];
        foreach($rows as $r){ $byDept[$r['department']][]=$r; }

foreach($byDept as $deptName=>$list):
            $totals = ['basic'=>0, 'extra'=>0, 'ded'=>0, 'adv'=>0, 'gross'=>0, 'net'=>0, 'bank'=>0, 'cash'=>0];
            foreach($list as $x){
                $totals['basic']+=$x['calc_basic']; $totals['extra']+=$x['extra']; $totals['ded']+=$x['deduction'];
                $totals['adv']+=$x['advance']; $totals['gross']+=$x['gross']; $totals['net']+=$x['net'];
                if($x['payment_type']==='BANK') $totals['bank']+=$x['net'];
                if($x['payment_type']==='CASH') $totals['cash']+=$x['net'];
            }
        ?>
            <div class="dept-section mb-5">
                <div class="d-flex justify-content-between align-items-end border-bottom pb-2 mb-3">
                    <div>
                        <h4 class="fw-bold text-dark mb-0"><?= htmlspecialchars($deptName); ?></h4>
                        <span class="badge bg-light text-muted border"><?= count($list); ?> Employees</span>
                    </div>
                    <div class="text-end small">
                        <span class="me-3">Gross: <b><?= number_format($totals['gross'],2); ?></b></span>
                        <span class="me-3">Bank: <b class="text-primary"><?= money0($totals['bank']); ?></b></span>
                        <span>Cash: <b class="text-success"><?= money0($totals['cash']); ?></b></span>
                    </div>
                </div>

<div class="table-responsive">
                    <table class="table table-bordered table-sm align-middle table-salary">
                        <thead>
                            <tr class="text-center">
                                <th>#</th>
                                <th class="text-start">Employee Name</th>
                                <th>Pay Mode</th>
                                <th>Type</th>
                                <th>Rate</th>
                                <th>Days</th>
                                <th>Basic</th>
                                <th>Extra</th>
                                <th>Ded.</th>
                                <th>Adv.</th>
                                <th>Gross</th>
                                <th class="bg-light">Net Payable</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php $i=1; foreach($list as $r): ?>
                            <tr class="text-end">
                                <td class="text-center text-muted small"><?= $i++; ?></td>
                                <td class="text-start fw-semibold">
                                    <?= htmlspecialchars($r['employee_name']); ?>
                                    <div class="x-small text-muted fw-normal"><?= htmlspecialchars($r['designation_name'] ?? ''); ?></div>
                                </td>
                                <td class="text-center small"><?= htmlspecialchars($r['payment_type']); ?></td>
                                <td class="text-center small">
                                <?= (strcasecmp($r['salary_type'],'Attendance')===0 ? 'Att.' : htmlspecialchars($r['salary_type'])); ?>
                                </td>

<td><?= number_format($r['per_day'],0); ?></td>
                                <td class="fw-bold"><?= number_format($r['attendance'],2); ?></td>
                                <td><?= number_format($r['calc_basic'],0); ?></td>
                                <td><?= number_format($r['extra'],0); ?></td>
                                <td class="text-danger"><?= number_format($r['deduction'],0); ?></td>
                                <td class="text-danger"><?= number_format($r['advance'],0); ?></td>
                                <td><?= number_format($r['gross'],0); ?></td>
                                <td class="bg-light fw-bold text-dark fs-6"><?= money0($r['net']); ?></td>
                            </tr>
                            <?php endforeach; ?>
                        </tbody>
                        <tfoot class="table-light fw-bold">
                            <tr class="text-end">
                                <td colspan="6" class="text-center text-uppercase">Total for <?= $deptName ?></td>
                                <td><?= number_format($totals['basic'],2); ?></td>
                                <td><?= number_format($totals['extra'],2); ?></td>
                                <td><?= number_format($totals['ded'],2); ?></td>
                                <td><?= number_format($totals['adv'],2); ?></td>
                                <td><?= number_format($totals['gross'],2); ?></td>
                                <td class="bg-warning-subtle text-dark"><?= money0($totals['net']); ?></td>
                            </tr>
                        </tfoot>
                    </table>
                </div>
            </div>
        <?php endforeach; ?>
    </div>
</div>

/* Print: open minimal new window (no header/footer) containing only #reportContent.
   .no-print will be hidden in print window */
function openPrintWindow() {
  var content = document.getElementById('reportContent');
  if (!content) { alert('Report content not found'); return; }

var companyName = (document.querySelector('.erp-company-card .company-name') || {textContent: '<?php echo addslashes($company_name); ?>'}).textContent;
  var monthLabel = '<?php echo addslashes(date("M Y", strtotime($MONTH_KEY))); ?>';
  var halfLabel = '<?php echo addslashes(($half==='H1'?'1–15':'16–End')); ?>';

var win = window.open('', '_blank', 'width=1200,height=800,scrollbars=yes');
  var doc = win.document;
  doc.open();
  doc.write('<!doctype html><html><head><meta charset="utf-8"><title>Print - '+companyName+'</title>' + printCSS + '</head><body>');
  doc.write('<div class="print-header"><div class="print-company">'+companyName+'</div><div class="print-sub">'+monthLabel+' — '+halfLabel+'</div></div>');
  doc.write('<div id="printReport">');
  doc.write(content.innerHTML);
  doc.write('</div>');
  doc.write('</body></html>');
  doc.close();

setTimeout(function(){
    win.focus();
    win.print();
  }, 600);
}
</script>

<?php
/* ---- include shared footer with robust candidates ---- */
$footer_candidates = [
  __DIR__ . '/partials/footer.php',
  __DIR__ . '/erp/partials/footer.php',
  __DIR__ . '/../erp/partials/footer.php',
  __DIR__ . '/../../erp/partials/footer.php'
];
$footer_included = false;
foreach ($footer_candidates as $ff) {
  if (file_exists($ff)) { require_once $ff; $footer_included = true; break; }
}
if (!$footer_included) {
  error_log("semi_monthly_salary_report: footer.php not found. Tried: " . implode(', ', $footer_candidates));
  echo "<div style=\"padding:8px;margin-top:12px;color:#666;font-size:13px;\">Footer missing</div>";
}
?>
</body>
</html>