View Code - 20260920

monthly_salary_report.php | 20260920_164915.php Initial Domain Snapshot

<?php
/* =============================================================================
   File: /erp/monthly_salary_report.php
   Purpose: Monthly Salary Report (controls kept out of print; controls single-row)
   Changes:
     - Controls (filters + actions) moved above #reportContent and given .no-print
     - print window hides .no-print so marked area will NOT print
     - All previous features preserved (inactive filter, designation, gross/net/bank/cash)
     - Added Master Salary Snapshot Integration (Summary Cards included)
============================================================================= */
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'; }

/* ---- shared partial candidates ---- */
$header_candidates = [
  __DIR__ . '/partials/header.php',
  __DIR__ . '/erp/partials/header.php',
  __DIR__ . '/../erp/partials/header.php',
  __DIR__ . '/../../erp/partials/header.php'
];
$footer_candidates = [
  __DIR__ . '/partials/footer.php',
  __DIR__ . '/erp/partials/footer.php',
  __DIR__ . '/../erp/partials/footer.php',
  __DIR__ . '/../../erp/partials/footer.php'
];

/* ---- 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'); }}
function h($v){ return htmlspecialchars((string)$v, ENT_QUOTES, 'UTF-8'); }
function include_first_existing(array $candidates): bool {
  foreach ($candidates as $candidate) {
    if (file_exists($candidate)) {
      require_once $candidate;
      return true;
    }
  }
  return false;
}

/* ---- Ensure master_salary_snapshots table exists (with report_type) ---- */
$snapshot_ddl = <<<SQL
CREATE TABLE IF NOT EXISTS master_salary_snapshots (
    id INT AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    salary_year INT NOT NULL,
    salary_month INT NOT NULL,
    period VARCHAR(20) NOT NULL,
    report_type VARCHAR(50) NOT NULL,
    report_html LONGTEXT NOT NULL,
    created_by BIGINT UNSIGNED NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY unique_report (company_id, salary_year, salary_month, period, report_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
SQL;
$pdo->exec($snapshot_ddl);

/* ---- fetch company name for card (safe fallback) ---- */
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 monthly_salary_report (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 company_id BIGINT UNSIGNED NOT NULL,
 month_key DATE 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,
 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_emp (company_id, month_key, employee_id),
 KEY idx_company_month (company_id, month_key),
 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); }
function render_monthly_salary_pdf_html(string $company_name, string $month_key, array $byDept, float $grand_gross, float $grand_net, float $grand_bank, float $grand_cash): string {
  ob_start();
  ?>
<!doctype html>
<html>
<head>
<meta charset="utf-8">
<style>
  @page { size: A4 portrait; margin: 10mm; }
  body { font-family: DejaVu Sans, sans-serif; color: #111; font-size: 8.5px; margin: 0; }
  h1 { font-size: 16px; margin: 0 0 3px; }
  .subtitle { font-size: 9px; color: #555; margin-bottom: 10px; }
  .summary { width: 100%; border-collapse: separate; border-spacing: 4px; margin-bottom: 10px; }
  .summary td { width: 25%; border: 1px solid #d7dee3; border-radius: 4px; padding: 6px; vertical-align: top; }
  .summary-label { font-size: 8px; color: #666; margin-bottom: 4px; }
  .summary-value { font-size: 12px; font-weight: bold; }
  .dept-title { background: #f1f4f6; border: 1px solid #d7dee3; padding: 6px 7px; font-size: 9px; font-weight: bold; margin: 12px 0 6px; }
  table.report { width: 100%; border-collapse: collapse; margin-bottom: 8px; table-layout: fixed; }
  table.report th, table.report td { border: 1px solid #d7dee3; padding: 3px 4px; word-wrap: break-word; }
  table.report th { background: #eef2f5; font-size: 7px; text-transform: uppercase; }
  .text-right { text-align: right; }
  .text-center { text-align: center; }
  .employee-name { font-weight: bold; }
  .employee-designation { font-size: 7px; color: #666; }
  .tfoot td { background: #f8fafb; font-weight: bold; }
  .negative { color: #b42318; font-weight: bold; }
</style>
</head>
<body>
  <h1><?= h($company_name) ?></h1>
  <div class="subtitle">Monthly Salary Report - <?= h(date('M Y', strtotime($month_key))) ?></div>

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

<?php foreach ($byDept as $deptName => $list): ?>
    <?php
      $t = ['basic'=>0,'extra'=>0,'ded'=>0,'adv'=>0,'gross'=>0,'net'=>0,'bank'=>0,'cash'=>0];
      foreach ($list as $item) {
        $t['basic'] += $item['calc_basic'];
        $t['extra'] += $item['extra'];
        $t['ded'] += $item['deduction'];
        $t['adv'] += $item['advance'];
        $t['gross'] += $item['gross'];
        $t['net'] += $item['net'];
        if ($item['payment_type'] === 'BANK') { $t['bank'] += $item['net']; }
        if ($item['payment_type'] === 'CASH') { $t['cash'] += $item['net']; }
      }
    ?>
    <div class="dept-title">
      <?= h($deptName) ?>
      | Gross: Rs. <?= number_format($t['gross'], 2) ?>
      | Net: Rs. <?= h(money0($t['net'])) ?>
      | Bank: Rs. <?= h(money0($t['bank'])) ?>
      | Cash: Rs. <?= h(money0($t['cash'])) ?>
    </div>

<table class="report">
      <thead>
        <tr>
          <th>#</th>
          <th>Employee</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 Pay</th>
        </tr>
      </thead>
      <tbody>
        <?php $index = 1; foreach ($list as $item): ?>
          <tr>
            <td class="text-center"><?= $index++ ?></td>
            <td>
              <div class="employee-name"><?= h($item['employee_name']) ?></div>
              <div class="employee-designation"><?= h($item['designation_name'] ?? '') ?></div>
            </td>
            <td class="text-center"><?= h($item['payment_type']) ?></td>
            <td class="text-center"><?= h($item['salary_type']) ?></td>
            <td class="text-right"><?= number_format($item['per_day'], 2) ?></td>
            <td class="text-right"><?= number_format($item['attendance'], 2) ?></td>
            <td class="text-right"><?= number_format($item['calc_basic'], 2) ?></td>
            <td class="text-right"><?= number_format($item['extra'], 2) ?></td>
            <td class="text-right"><?= number_format($item['deduction'], 2) ?></td>
            <td class="text-right"><?= number_format($item['advance'], 2) ?></td>
            <td class="text-right"><?= number_format($item['gross'], 0) ?></td>
            <td class="text-right">
              <?php if (!empty($item['negative_flag'])): ?>
                <span class="negative">Advance Entry Required: <?= h(money0($item['negative_amount'] ?? 0)) ?></span>
              <?php else: ?>
                <?= h(money0($item['net'])) ?>
              <?php endif; ?>
            </td>
          </tr>
        <?php endforeach; ?>
      </tbody>
      <tfoot class="tfoot">
        <tr>
          <td colspan="6" class="text-center">Dept Total</td>
          <td class="text-right"><?= number_format($t['basic'], 2) ?></td>
          <td class="text-right"><?= number_format($t['extra'], 2) ?></td>
          <td class="text-right"><?= number_format($t['ded'], 2) ?></td>
          <td class="text-right"><?= number_format($t['adv'], 2) ?></td>
          <td class="text-right"><?= number_format($t['gross'], 2) ?></td>
          <td class="text-right"><?= h(money0($t['net'])) ?></td>
        </tr>
      </tfoot>
    </table>
  <?php endforeach; ?>
</body>
</html>
  <?php
  return (string)ob_get_clean();
}

/* ---- inputs ---- */
$year  = (int)($_GET['y'] ?? $_POST['y'] ?? date('Y'));
$month = (int)($_GET['m'] ?? $_POST['m'] ?? date('n'));
$dept  = (int)($_GET['dept'] ?? $_POST['dept'] ?? 0);
$export_pdf = (int)($_GET['export_pdf'] ?? 0) === 1;
$MONTH_KEY = first_day($year,$month);
$FROM = $MONTH_KEY;
$TO   = last_day($year,$month);
$total_days = days_in_month($year,$month);

/* ---- Handle Save Snapshot Action (with report_type = 'Monthly') ---- */
if (($_POST['action'] ?? '') === 'save_snapshot') {
    check_csrf();
    $report_html = $_POST['report_html'] ?? '';
    $snapshot_period = $_POST['period'] ?? 'H2';
    $report_type = 'Monthly';

if (!empty($report_html)) {
        try {
            $stmt = $pdo->prepare("
                INSERT INTO master_salary_snapshots 
                (company_id, salary_year, salary_month, period, report_type, report_html, created_by) 
                VALUES (?, ?, ?, ?, ?, ?, ?)
                ON DUPLICATE KEY UPDATE 
                report_html = VALUES(report_html), 
                created_by = VALUES(created_by), 
                created_at = NOW()
            ");
            $stmt->execute([$COMPANY_ID, $year, $month, $snapshot_period, $report_type, $report_html, $USER_ID]);
            header("Location: monthly_salary_report.php?y={$year}&m={$month}&dept={$dept}&snapshot_saved=1");
            exit;
        } catch (Throwable $e) {
            http_response_code(500);
            exit("Error saving snapshot: " . htmlspecialchars($e->getMessage()));
        }
    }
}

/* ---- 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);

/* ---- Clear month logic (same as before) ---- */
if (($_POST['action'] ?? '') === 'clear_month') {
  check_csrf();
  $del_dept = (int)($_POST['dept'] ?? 0);
  try {
    $pdo->beginTransaction();
    if ($del_dept > 0) {
      $stDel = $pdo->prepare("DELETE FROM monthly_salary_report WHERE company_id = ? AND month_key = ? AND department_id = ?");
      $stDel->execute([$COMPANY_ID, $MONTH_KEY, $del_dept]);
      $deleted = $stDel->rowCount();
    } else {
      $stDel = $pdo->prepare("DELETE FROM monthly_salary_report WHERE company_id = ? AND month_key = ?");
      $stDel->execute([$COMPANY_ID, $MONTH_KEY]);
      $deleted = $stDel->rowCount();
    }
    $pdo->commit();
    $qs = '?y=' . urlencode($year) . '&m=' . urlencode($month) . '&dept=' . urlencode($dept);
    $qs .= $del_dept ? '&cleared_dept=' . (int)$del_dept . '&deleted=' . (int)$deleted : '&cleared=1&deleted=' . (int)$deleted;
    header("Location: monthly_salary_report.php" . $qs);
    exit;
  } catch (Throwable $ex) {
    if ($pdo->inTransaction()) $pdo->rollBack();
    http_response_code(500);
    echo "Error clearing data: " . htmlspecialchars($ex->getMessage());
    exit;
  }
}

/* ---- employees query: exclude inactive (COALESCE(is_active,0)=1) and include designation_name ---- */
$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='Monthly' 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 / extras / deductions / advances prepared statements ---- */
$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>=?");
$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 ?");
$stAdv = $pdo->prepare("SELECT id, remaining_amount, `date` FROM employee_advance WHERE company_id=? AND employee_id=? AND status='OPEN' AND `date` BETWEEN ? AND ?");

$rows = [];
$grand_bank=0; $grand_cash=0; $grand_net=0; $grand_gross=0;

foreach ($emps as $e) {
  if (empty($e['id'])) continue;
  $emp_id  = (int)$e['id'];
  $dept_id = (int)($e['department_id'] ?? 0);
  $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);

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

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

if (strcasecmp($stype,'Attendance')===0) {
      $calc_basic = $per_day * $att_days;
  } elseif (strcasecmp($stype,'Fixed')===0) {
      $calc_basic = $basic;
  } elseif (strcasecmp($stype,'Fixed Attendance')===0) {
      $calc_basic = ($att_days > 0) ? ($per_day * $att_days) : 0;
  } else {
      $calc_basic = ($basic > 0) ? $basic : ($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_raw = round($gross - $ded - $adv_deduct, 0);

if ($net_raw < 0) {
    $negative_flag = true;
    $negative_amount = abs($net_raw);
    $net = 0;   // ✅ total safe
} else {
    $negative_flag = false;
    $negative_amount = 0;
    $net = $net_raw;
}

$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[] = [
	'negative_flag'=>$negative_flag,
	'negative_amount'=>$negative_amount,
    '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 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
      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),
      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, $r['employee_id'], $r['employee_name'],
      $r['department_id'], $r['department'],
      'Monthly', $r['salary_type'], $total_days,
      $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'],
      'Monthly - '.date('M-Y', strtotime($MONTH_KEY)), $USER_ID
    ]);
  }
  $pdo->commit();
  header("Location: monthly_salary_report.php?y={$year}&m={$month}&dept={$dept}&saved=1");
  exit;
}

/* ---- EXPORT (unchanged) ---- */
if (($_POST['action'] ?? '') === 'export') {
  // Activity log helper include
  $activity_helper = __DIR__ . '/helpers/activity_helper.php';
  if (file_exists($activity_helper)) require_once $activity_helper;
  check_csrf();
  $ins = $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
      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),
      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();
  $exported_employees = [];
  foreach ($rows as $r) {
    $ins->execute([
      $COMPANY_ID, $MONTH_KEY, $r['employee_id'], $r['employee_name'],
      $r['department_id'], $r['department'],
      'Monthly', $r['salary_type'], $total_days,
      $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'],
      'Monthly - '.date('M-Y', strtotime($MONTH_KEY)), $USER_ID
    ]);
    $exported_employees[] = $r['employee_name'] . ' (ID:' . $r['employee_id'] . ')';
  }
  $pdo->commit();
  // Log activity for export
  if (function_exists('log_activity')) {
    log_activity([
      'activity_type' => 'export',
      'module_name'   => 'monthly_salary_report',
      'action_name'   => 'salary_export',
      'activity_note' => 'Exported salary for employees: ' . implode(', ', $exported_employees),
      'reference_id'  => null,
    ]);
  }
  ?>
  <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($MONTH_KEY); ?>">
    <input type="hidden" name="dept" value="<?php echo htmlspecialchars($dept); ?>">
    <?php csrf_field(); ?>
  </form>
  <script>document.getElementById('handoff').submit();</script>
  <?php exit;
}

$byDept=[];
foreach($rows as $r){ $byDept[$r['department']][]=$r; }

if ($export_pdf) {
  $dompdf_autoload = __DIR__ . '/lib/dompdf/vendor/autoload.php';

if (!file_exists($dompdf_autoload)) {
    http_response_code(500);
    exit('PDF library not found.');
  }

require_once $dompdf_autoload;

$pdf_html = render_monthly_salary_pdf_html($company_name, $MONTH_KEY, $byDept, $grand_gross, $grand_net, $grand_bank, $grand_cash);
  $dompdf = new \Dompdf\Dompdf([
    'isRemoteEnabled' => false,
    'isHtml5ParserEnabled' => true,
    'defaultFont' => 'DejaVu Sans',
  ]);

$dompdf->loadHtml($pdf_html, 'UTF-8');
  $dompdf->setPaper('A4', 'portrait');
  $dompdf->render();
  $pdf_output = $dompdf->output();

$file_name = 'monthly_salary_report_' . date('M_Y', strtotime($MONTH_KEY)) . '.pdf';

header('Content-Type: application/pdf');
  header('Content-Disposition: attachment; filename="' . $file_name . '"');
  header('Content-Length: ' . strlen($pdf_output));
  echo $pdf_output;
  exit;
}

$header_included = include_first_existing($header_candidates);
$header_warning_html = '';
if (!$header_included) {
  error_log("monthly_salary_report: header.php not found. Tried: " . implode(', ', $header_candidates));
  $header_warning_html = '<div style="padding:8px;background:#f8d7da;color:#721c24;border-radius:6px;margin-bottom:10px;">System header missing</div>';
}

/* ---- UI ---- */
?>
<!doctype html>
<html>
<head>
<meta charset="utf-8">
<title>Monthly Salary Report</title>
</head>
<body>

<?= $header_warning_html ?>

<style>

body { 
    background-color: #f4f7f6; 
    color: #333; 
}

.erp-company-card { 
    background: #fff; 
    border-bottom: 3px solid #198754; 
}

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

.summary-card { 
    border: none; 
    border-radius: 10px; 
    transition: 0.3s; 
}

.summary-card:hover { 
    transform: translateY(-5px); 
    box-shadow: 0 10px 20px rgba(0,0,0,0.1); 
}

.table-salary thead th { 
    background-color: #f8f9fa; 
    font-size: 0.7rem; 
    text-transform: uppercase; 
    letter-spacing: 0.5px; 
}

.dept-header { 
    background: #e9ecef; 
    padding: 10px 15px; 
    border-radius: 8px; 
    font-weight: 700; 
    color: #495057; 
}

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

@media print {

@page {
    size: A4;
    margin: 6mm;
  }

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

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

/* Remove container extra width */
  .container-fluid,
  #reportContent {
    width: 100% !important;
    max-width: 100% !important;
    padding: 0 !important;
  }

/* Summary cards in single row */
  .row.g-3.mb-4 {
    display: flex !important;
    flex-wrap: nowrap !important;
  }

.row.g-3.mb-4 > div {
    width: 25% !important;
    max-width: 25% !important;
    flex: 0 0 25% !important;
  }

/* Reduce table density */
  .table {
    font-size: 10px !important;
  }

.table th,
  .table td {
    padding: 3px 6px !important;
  }

/* Avoid breaking sections */
  .summary-card,
  .dept-section {
    page-break-inside: avoid;
    break-inside: avoid;
  }

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

}
</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"><?= htmlspecialchars($company_name); ?></h2>
        </div>
        <div class="no-print">
            <button type="button" class="btn btn-outline-dark rounded-pill px-4" onclick="window.print()">
                <i class="bi bi-printer me-2"></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="h5 mb-0 fw-bold text-uppercase tracking-wider">Monthly Salary Report</div>
        <div class="badge bg-success fs-6 py-2 px-3">
            <i class="bi bi-calendar3 me-2"></i> <?= htmlspecialchars(date('M Y', strtotime($MONTH_KEY))); ?>
        </div>
    </div>
</div>

<div class="container-fluid">
    <?php if (isset($_GET['saved'])): ?>
        <div class="alert alert-success alert-dismissible fade show border-0 shadow-sm" role="alert">
            <i class="bi bi-check-circle-fill me-2"></i> Saved monthly report for <strong><?= htmlspecialchars(date('M Y', strtotime($MONTH_KEY))); ?></strong>.
            <button type="button" class="btn-close" data-bs-dismiss="alert"></button>
        </div>
    <?php endif; ?>

<?php if (isset($_GET['snapshot_saved'])): ?>
        <div class="alert alert-success alert-dismissible fade show border-0 shadow-sm" role="alert">
            <i class="bi bi-cloud-check-fill me-2"></i> Successfully saved to Master Salary Report!
            <button type="button" class="btn-close" data-bs-dismiss="alert"></button>
        </div>
    <?php endif; ?>

<?php if (isset($_GET['cleared'])): ?>
        <div class="alert alert-danger border-0 shadow-sm"><i class="bi bi-trash3-fill me-2"></i> Cleared month data. Rows deleted: <?= (int)$_GET['deleted'] ?? 0; ?></div>
    <?php endif; ?>

<div class="card shadow-sm border-0 mb-4 no-print">
        <div class="card-body">
            <div class="row g-3 align-items-center">
                <div class="col-lg-6">
                    <form method="get" class="d-flex flex-wrap gap-2">
                        <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="width:100px;">
                        <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 Report</button>
                    </form>
                </div>
                <div class="col-lg-6 text-lg-end">
                    <form method="post" id="mainActions" class="d-inline-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; ?>">
                        
                        <button name="action" value="save" class="btn btn-success btn-sm px-3">💾 Save</button>
                        <button name="action" value="export" class="btn btn-info btn-sm text-white px-3">🏦 Export</button>
                        <a href="?y=<?= urlencode((string)$year); ?>&m=<?= urlencode((string)$month); ?>&dept=<?= urlencode((string)$dept); ?>&export_pdf=1" class="btn btn-danger btn-sm px-3">📄 PDF Export</a>
                        <button name="action" value="clear_month" class="btn btn-outline-danger btn-sm" onclick="return confirm('Are you sure you want to clear this month?')">🧹 Clear</button>
                    </form>

<form method="post" class="d-inline ms-2" id="snapshotForm">
                        <?php csrf_field(); ?>
                        <input type="hidden" name="action" value="save_snapshot">
                        <input type="hidden" name="y" value="<?= $year; ?>">
                        <input type="hidden" name="m" value="<?= $month; ?>">
                        <input type="hidden" name="period" id="snapshotPeriodField" value="H2">
                        <select name="snapshot_period_ui" id="snapshotPeriodUi" class="form-select form-select-sm d-inline-block w-auto align-middle me-1">
                            <option value="H2" selected>H2 (16–End)</option>
                            <option value="H1">H1 (1–15)</option>
                            <option value="Monthly">Monthly</option>
                        </select>
                        <textarea name="report_html" id="reportHtmlField" style="display:none;"></textarea>
                        
                        <button type="button" onclick="saveReportSnapshot()" class="btn btn-dark btn-sm px-3 align-middle">
                            <i class="bi bi-cloud-upload me-1"></i> Save to Master Salary
                        </button>
                    </form>

<span class="ms-3 border-start ps-3 text-muted small">Employees: <b><?= count($rows); ?></b></span>
                </div>
            </div>
        </div>
    </div>

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

<?php foreach($byDept as $deptName=>$list):
            $t = ['basic'=>0,'extra'=>0,'ded'=>0,'adv'=>0,'gross'=>0,'net'=>0,'bank'=>0,'cash'=>0];
            foreach($list as $x){
                $t['basic']+=$x['calc_basic']; $t['extra']+=$x['extra']; $t['ded']+=$x['deduction'];
                $t['adv']+=$x['advance']; $t['gross']+=$x['gross']; $t['net']+=$x['net'];
                if($x['payment_type']==='BANK') $t['bank']+=$x['net'];
                if($x['payment_type']==='CASH') $t['cash']+=$x['net'];
            }
        ?>
        <div class="dept-section mb-5">
            <div class="dept-header d-flex justify-content-between align-items-center mb-3">
                <span class="fs-5"><?= htmlspecialchars($deptName); ?></span>
                <div class="small fw-normal">
                    Gross: <span class="fw-bold me-3">₹<?= number_format($t['gross'],2); ?></span>
                    Net: <span class="fw-bold text-success me-3"><?= money0($t['net']); ?></span>
                    Bank: <span class="text-primary me-2"><?= money0($t['bank']); ?></span>
                    Cash: <span class="text-warning-emphasis"><?= money0($t['cash']); ?></span>
                </div>
            </div>

<div class="table-responsive">
                <table class="table table-hover table-bordered table-sm align-middle table-salary">
                    <thead>
                        <tr class="text-center">
                            <th style="width:40px">#</th>
                            <th class="text-start">Employee</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 class="table-light">Net Pay</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">
                                <div class="fw-bold text-dark"><?= htmlspecialchars($r['employee_name']); ?></div>
                                <div style="font-size:10px; color:#6c757d"><?= htmlspecialchars($r['designation_name'] ?? ''); ?></div>
                            </td>
                            <td class="text-center"><span class="badge <?= ($r['payment_type']=='BANK'?'bg-primary-subtle text-primary':'bg-secondary-subtle text-dark') ?>"><?= $r['payment_type'] ?></span></td>
                            <td class="text-center small"><?= $r['salary_type'] ?></td>
                            <td><?= number_format($r['per_day'],2); ?></td>
                            <td class="fw-bold"><?= number_format($r['attendance'],2); ?></td>
                            <td><?= number_format($r['calc_basic'],2); ?></td>
                            <td class="text-success"><?= number_format($r['extra'],2); ?></td>
                            <td class="text-danger"><?= number_format($r['deduction'],2); ?></td>
                            <td class="text-danger"><?= number_format($r['advance'],2); ?></td>
                            <td><?= number_format($r['gross'],0); ?></td>
                           <td class="table-light fw-bold fs-6">
<?php if($r['negative_flag']): ?>
    <span class="text-danger">
        ⚠ Advance Entry Required: <?= money0($r['negative_amount']); ?>
    </span>
<?php else: ?>
    <?= money0($r['net']); ?>
<?php endif; ?>
</td>
                        </tr>
                        <?php endforeach; ?>
                    </tbody>
                    <tfoot class="table-light fw-bold text-end">
                        <tr>
                            <td colspan="6" class="text-center">DEPT TOTAL</td>
                            <td><?= number_format($t['basic'],2); ?></td>
                            <td><?= number_format($t['extra'],2); ?></td>
                            <td><?= number_format($t['ded'],2); ?></td>
                            <td><?= number_format($t['adv'],2); ?></td>
                            <td><?= number_format($t['gross'],2); ?></td>
                            <td class="bg-success text-white"><?= money0($t['net']); ?></td>
                        </tr>
                    </tfoot>
                </table>
            </div>
        </div>
        <?php endforeach; ?>
    </div>
</div>

document.getElementById('snapshotForm').submit();
}

function confirmClear(e, dept) {
  var deptMsg = dept && dept > 0 ? ' only for selected department' : ' for entire company';
  if (!confirm('Are you sure you want to CLEAR monthly report data' + deptMsg + ' for <?php echo htmlspecialchars(date('M Y', strtotime($MONTH_KEY))); ?> ? This CANNOT be undone.')) {
    e.preventDefault();
    return false;
  }
  return true;
}

/* Print: open minimal new window (no header/footer) containing only #reportContent
   Also ensure .no-print is 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 payrollPeriod = 'Monthly';

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+' — '+payrollPeriod+'</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
$footer_included = include_first_existing($footer_candidates);
if (!$footer_included) {
  error_log("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>