View Code - 20260722

salary_report.php | 20260722_120325.php Initial Domain Snapshot

<?php
/* =============================================================================
   File: /erp/salary_report.php
   Purpose: Combined Salary Report = Attendance Salary + Extra – Deduction – Advance(auto-suggest)
            with dynamic Month & Period filters + Payment Export handoff.
   Scope : Page-local only (NO global/base edits). Company-scoped.
   ============================================================================= */
header('X-Frame-Options: SAMEORIGIN');
error_reporting(E_ALL); ini_set('display_errors','1');

/* ------------ Auth + PDO ------------ */
require __DIR__ . '/modules/auth/auth.php';
require_login();
$u          = auth_user();
$COMPANY_ID = (int)$u['company_id'];
$USER_ID    = (int)$u['id'];

$pdo = $GLOBALS['pdo'] ?? null;
if (!$pdo) { require __DIR__ . '/core/db.php'; }

/* ------------ CSRF for POST actions (export) ------------ */
if (session_status() !== PHP_SESSION_ACTIVE) session_start();
if (empty($_SESSION['csrf'])) $_SESSION['csrf'] = bin2hex(random_bytes(16));
function csrf() { return $_SESSION['csrf'] ?? ''; }
function check_csrf(){
  if (($_POST['csrf'] ?? '') !== ($_SESSION['csrf'] ?? '')) { http_response_code(403); exit('Bad CSRF'); }
}

/* ------------ Helpers ------------ */
function ymd($d){ return date('Y-m-d', strtotime($d)); }
function first_day_of_month($ym){ return date('Y-m-01', strtotime($ym.'-01')); }
function last_day_of_month($ym){ return date('Y-m-t',  strtotime($ym.'-01')); }

/* ------------------------------------------------------------
   UI state: read filters
   month_mode: YYYY-MM (e.g. 2025-07)
   period: H1 (1-15) | H2 (16-end) | FULL (Monthly) | H2FULL (16-end + Monthly)
   ------------------------------------------------------------ */
$month_ym = preg_replace('/[^0-9\-]/','', $_GET['month'] ?? date('Y-m')); // YYYY-MM
$period   = $_GET['period'] ?? 'H1';
$PERIOD_OPTS = ['H1'=>'1–15','H2'=>'16–End','FULL'=>'Monthly','H2FULL'=>'16–End + Monthly'];

/* ------------------------------------------------------------
   Build date windows per period for Extra/Deduction/Advance:
   - H1    : 1..15 of selected month
   - H2    : 16..end of selected month
   - FULL  : 1..end of selected month
   - H2FULL: (attendance rows: H2 OR FULL). For Extra/Deduction/Advance we
             consider FULL (whole month) so monthly वालों का पूरा और semi-monthly
             वालों का H2 हिस्सा comfortable रहे.
   ------------------------------------------------------------ */
$MSTART = first_day_of_month($month_ym);
$MEND   = last_day_of_month($month_ym);

switch ($period) {
  case 'H1':    $DSTART=$MSTART;                 $DEND=date('Y-m-15', strtotime($MSTART)); break;
  case 'H2':    $DSTART=date('Y-m-16', strtotime($MSTART)); $DEND=$MEND;                    break;
  case 'FULL':  $DSTART=$MSTART;                 $DEND=$MEND;                              break;
  case 'H2FULL':$DSTART=$MSTART;                 $DEND=$MEND;                              break;
  default:      $period='H1'; $DSTART=$MSTART;    $DEND=date('Y-m-15', strtotime($MSTART));
}

/* ------------------------------------------------------------
   ATTENDANCE rows pick logic from manual_attendance_data:
   - We detect "Monthly" vs "Semi-monthly" using salary_period column text.
   - Month label: we match Month-Year from salary_period OR via period_end month.
   Strategy (defensive):
   1) Build month label 'Mon YYYY' to match left part of salary_period if present.
   2) Fallback additionally to period_end within [MSTART..MEND] OR period_start..period_end
      overlapping the chosen window.
   ------------------------------------------------------------ */
$monLabel = strtoupper(date('M Y', strtotime($MSTART))); // e.g., "JUL 2025"

/* Build filter fragments */
$condMonthly    = " (LOWER(m.salary_period) LIKE '%monthly%' AND (UPPER(m.salary_period) LIKE :monlabel OR DATE_FORMAT(m.period_end,'%Y-%m')=:ym)) ";
$condSemiMonthly= " (LOWER(m.salary_period) LIKE '%semi-monthly%' AND (UPPER(m.salary_period) LIKE :monlabel OR DATE_FORMAT(m.period_end,'%Y-%m')=:ym)) ";

/* Period sub-filter for semi-monthly rows by text cue '1-15' / '16-end' */
$condH1 = " (m.salary_period REGEXP '1[[:space:]]*[-–][[:space:]]*15') ";
$condH2 = " (m.salary_period REGEXP '16[[:space:]]*[-–][[:space:]]*(end|30|31)') ";

/* Final attendance WHERE for selected period */
if ($period==='H1') {
  $attCond = " ($condSemiMonthly AND $condH1) ";
} elseif ($period==='H2') {
  $attCond = " ($condSemiMonthly AND $condH2) ";
} elseif ($period==='FULL') {
  $attCond = " $condMonthly ";
} else { // H2FULL
  $attCond = " (($condSemiMonthly AND $condH2) OR $condMonthly) ";
}

/* ------------------------------------------------------------
   Query 1: Attendance totals by employee within chosen period selection
   ------------------------------------------------------------ */
$sqlAtt = "
  SELECT m.employee_id,
         MAX(m.employee_name) AS employee_name,
         MAX(m.department)    AS department,
         SUM(m.salary)        AS base_salary
  FROM manual_attendance_data m
  WHERE m.company_id = :cid
    AND $attCond
  GROUP BY m.employee_id
";
$st = $pdo->prepare($sqlAtt);
$st->execute([
  ':cid'      => $COMPANY_ID,
  ':ym'       => $month_ym,
  ':monlabel' => "%$monLabel%"
]);
$attRows = $st->fetchAll(PDO::FETCH_ASSOC);

/* Build map: emp_id → base row  */
$rows = [];
foreach ($attRows as $r){
  $eid = (int)$r['employee_id'];
  $rows[$eid] = [
    'employee_id'   => $eid,
    'employee_name' => $r['employee_name'] ?? '',
    'department'    => $r['department'] ?? '',
    'base_salary'   => (float)$r['base_salary'],
    'extra'         => 0.0,
    'deduction'     => 0.0,
    'adv_auto'      => 0.0,
  ];
}

/* If no attendance, still allow extras/deductions to appear (rare case).
   We'll collect all employee_ids that had extra/deduction within DSTART..DEND and merge. */

/* ------------------------------------------------------------
   Query 2: EXTRA within date window
   ------------------------------------------------------------ */
$sqlExtra = "
  SELECT e.employee_id, COALESCE(SUM(e.amount),0) AS extra_sum
  FROM employee_extra e
  WHERE e.company_id=:cid
    AND e.entry_date BETWEEN :ds AND :de
  GROUP BY e.employee_id
";
$st = $pdo->prepare($sqlExtra);
$st->execute([':cid'=>$COMPANY_ID, ':ds'=>$DSTART, ':de'=>$DEND]);
foreach ($st->fetchAll(PDO::FETCH_ASSOC) as $r){
  $eid = (int)$r['employee_id'];
  if (!isset($rows[$eid])) { $rows[$eid] = ['employee_id'=>$eid,'employee_name'=>'','department'=>'','base_salary'=>0,'extra'=>0,'deduction'=>0,'adv_auto'=>0]; }
  $rows[$eid]['extra'] = (float)$r['extra_sum'];
}

/* ------------------------------------------------------------
   Query 3: DEDUCTION within date window
   ------------------------------------------------------------ */
$sqlDed = "
  SELECT d.employee_id, COALESCE(SUM(d.amount),0) AS ded_sum
  FROM employee_deduction d
  WHERE d.company_id=:cid
    AND d.entry_date BETWEEN :ds AND :de
  GROUP BY d.employee_id
";
$st = $pdo->prepare($sqlDed);
$st->execute([':cid'=>$COMPANY_ID, ':ds'=>$DSTART, ':de'=>$DEND]);
foreach ($st->fetchAll(PDO::FETCH_ASSOC) as $r){
  $eid = (int)$r['employee_id'];
  if (!isset($rows[$eid])) { $rows[$eid] = ['employee_id'=>$eid,'employee_name'=>'','department'=>'','base_salary'=>0,'extra'=>0,'deduction'=>0,'adv_auto'=>0]; }
  $rows[$eid]['deduction'] = (float)$r['ded_sum'];
}

/* ------------------------------------------------------------
   Query 4: ADVANCE auto-suggest
   Policy:
   - employee_advance rows with status='OPEN' and date <= period end
   - If deduct_parts >= 1 → suggest equal part = remaining_amount / deduct_parts (ceil to integer paise? we’ll round to 0)
   - If deduct_parts is NULL/0 → suggest full remaining_amount
   NOTE: This page does NOT update the advance table. It only suggests a deduction figure.
   ------------------------------------------------------------ */
$sqlAdv = "
  SELECT a.employee_id, a.remaining_amount, a.deduct_parts
  FROM employee_advance a
  WHERE a.company_id=:cid
    AND a.status='OPEN'
    AND a.date <= :de
";
$st = $pdo->prepare($sqlAdv);
$st->execute([':cid'=>$COMPANY_ID, ':de'=>$DEND]);
$advTmp = [];
foreach ($st->fetchAll(PDO::FETCH_ASSOC) as $r){
  $eid  = (int)$r['employee_id'];
  $rem  = max(0, (float)$r['remaining_amount']);
  $parts= (int)($r['deduct_parts'] ?? 0);
  $suggest = $parts > 0 ? ($rem / $parts) : $rem; // equal part; not exceeding remaining
  if (!isset($advTmp[$eid])) $advTmp[$eid]=0.0;
  $advTmp[$eid] += $suggest;
}
foreach ($advTmp as $eid=>$amt){
  if (!isset($rows[$eid])) { $rows[$eid] = ['employee_id'=>$eid,'employee_name'=>'','department'=>'','base_salary'=>0,'extra'=>0,'deduction'=>0,'adv_auto'=>0]; }
  $rows[$eid]['adv_auto'] = (float)$amt;
}

/* ------------------------------------------------------------
   Enrich missing names/dept from latest attendance row (if any)
   ------------------------------------------------------------ */
if (!empty($rows)){
  $ids = implode(',', array_map('intval', array_keys($rows)));
  $q   = $pdo->query("SELECT employee_id, MAX(employee_name) AS employee_name, MAX(department) AS department
                      FROM manual_attendance_data
                      WHERE company_id={$COMPANY_ID} AND employee_id IN ($ids)
                      GROUP BY employee_id");
  foreach ($q->fetchAll(PDO::FETCH_ASSOC) as $r){
    $eid = (int)$r['employee_id'];
    if (isset($rows[$eid])) {
      if ($rows[$eid]['employee_name']==='') $rows[$eid]['employee_name']=$r['employee_name'] ?? '';
      if ($rows[$eid]['department']==='')    $rows[$eid]['department']=$r['department'] ?? '';
    }
  }
}

/* ------------------------------------------------------------
   Compute totals + net
   ------------------------------------------------------------ */
$grand = ['base'=>0,'extra'=>0,'ded'=>0,'adv'=>0,'net'=>0];
foreach ($rows as &$r){
  $net = (float)$r['base_salary'] + (float)$r['extra'] - (float)$r['deduction'] - (float)$r['adv_auto'];
  $r['net_pay'] = (float)round($net, 0);
  $grand['base'] += (float)$r['base_salary'];
  $grand['extra']+= (float)$r['extra'];
  $grand['ded']  += (float)$r['deduction'];
  $grand['adv']  += (float)$r['adv_auto'];
  $grand['net']  += (float)$r['net_pay'];
}
unset($r);

/* ------------------------------------------------------------
   Distinct month list for dropdown from attendance table
   ------------------------------------------------------------ */
$monList = [];
$qm = $pdo->prepare("
  SELECT DISTINCT DATE_FORMAT(period_end,'%Y-%m') AS ym
  FROM manual_attendance_data
  WHERE company_id=:cid
  ORDER BY ym DESC
  LIMIT 18
");
$qm->execute([':cid'=>$COMPANY_ID]);
$monList = array_column($qm->fetchAll(PDO::FETCH_ASSOC), 'ym');

/* ------------------------------------------------------------
   Handle payment export POST (selected rows)
   ------------------------------------------------------------ */
if (($_POST['action'] ?? '')==='export_payment') {
  check_csrf();
  $sel = $_POST['sel'] ?? []; // array of employee_id
  $payload = [];
  foreach ($sel as $eid){
    $eid = (int)$eid;
    if (!isset($rows[$eid])) continue;
    // Pack fields that payment_export.php expects; we’ll send minimal set safely:
    $payload[] = [
      'employee_id'   => $eid,
      'employee_name' => $rows[$eid]['employee_name'],
      'amount'        => (string)$rows[$eid]['net_pay'], // integers
      'remarks'       => $rows[$eid]['employee_name'],
      'department'    => $rows[$eid]['department'] ?? '',
    ];
  }
  // Redirect via POST to /erp/payment_export.php with JSON payload
  // (payment_export.php already company-scoped & trusted in your system)
  // We’ll render an auto-submit form:
  $period_label = $PERIOD_OPTS[$period] ?? $period;
  $narration    = $period_label . ' - ' . strtoupper(date('M-Y', strtotime($MSTART)));
  $json = htmlspecialchars(json_encode($payload), ENT_QUOTES, 'UTF-8');
  echo "<form id='x' method='post' action='/erp/payment_export.php'>
          <input type='hidden' name='csrf' value='".csrf()."'>
          <input type='hidden' name='data_json' value='{$json}'>
          <input type='hidden' name='period_label' value='".htmlspecialchars($period_label,ENT_QUOTES)."'>
          <input type='hidden' name='month_label'  value='".htmlspecialchars(strtoupper(date('M Y', strtotime($MSTART))),ENT_QUOTES)."'>
          <input type='hidden' name='narration'    value='".htmlspecialchars($narration,ENT_QUOTES)."'>
        </form>
        <script>document.getElementById('x').submit();</script>";
  exit;
}
?>
<!doctype html>
<html lang="en">
<head>
  <meta charset="utf-8">
  <title>Salary Report</title>
  <meta name="viewport" content="width=device-width,initial-scale=1">
  <style>
    body{font-family:system-ui,-apple-system,Segoe UI,Roboto,Ubuntu,'Helvetica Neue',Arial,sans-serif;background:#f5fff7;margin:0}
    header{background:#34A853;color:#fff;padding:10px 16px}
    .wrap{padding:16px}
    .card{background:#fff;border-radius:12px;box-shadow:0 2px 10px rgba(0,0,0,.06);padding:16px;margin-bottom:16px}
    .row{display:flex;gap:12px;flex-wrap:wrap;align-items:center}
    select,button,input[type=date]{padding:8px 10px;border:1px solid #cfd8dc;border-radius:8px}
    table{width:100%;border-collapse:collapse;font-size:14px}
    th,td{border-bottom:1px solid #eceff1;padding:8px 6px;text-align:right}
    th:first-child,td:first-child, td:nth-child(2), td:nth-child(3){text-align:left}
    tfoot td{font-weight:700}
    .actions{display:flex;gap:8px;align-items:center}
    .badge{display:inline-block;background:#A7D8DE;color:#333;padding:2px 8px;border-radius:999px;font-size:12px}
    .muted{color:#5F6368}
  </style>
</head>
<body>
<header>
  <strong>Salary Report</strong>
</header>
<div class="wrap">
  <form method="get" class="card">
    <div class="row">
      <div>
        <div class="muted">Month</div>
        <select name="month" required>
          <?php
            $cur = $month_ym;
            foreach ($monList as $ym){
              $sel = ($ym===$cur)?'selected':'';
              $label = strtoupper(date('M Y', strtotime($ym.'-01')));
              echo "<option value='{$ym}' {$sel}>{$label}</option>";
            }
            // add current if not present
            if (!in_array(date('Y-m'), $monList)){
              $ym  = date('Y-m');
              $sel = ($ym===$cur)?'selected':'';
              $label = strtoupper(date('M Y'));
              echo "<option value='{$ym}' {$sel}>{$label}</option>";
            }
          ?>
        </select>
      </div>
      <div>
        <div class="muted">Period</div>
        <select name="period">
          <?php foreach($PERIOD_OPTS as $k=>$v){ $sel=($k===$period)?'selected':''; echo "<option value='{$k}' {$sel}>{$v}</option>"; } ?>
        </select>
      </div>
      <div class="actions">
        <button type="submit">Apply</button>
        <span class="badge"><?php echo htmlspecialchars($PERIOD_OPTS[$period]??$period); ?> · <?php echo strtoupper(date('M Y', strtotime($MSTART))); ?></span>
        <span class="muted">Window: <?php echo $DSTART.' → '.$DEND; ?></span>
      </div>
    </div>
  </form>

<form method="post" class="card">
    <input type="hidden" name="csrf" value="<?php echo csrf(); ?>">
    <input type="hidden" name="action" value="export_payment">
    <div class="row" style="justify-content:space-between;margin-bottom:8px">
      <div><strong>Report</strong></div>
      <div class="actions">
        <button type="submit">Export to Payment (Excel)</button>
      </div>
    </div>
    <table>
      <thead>
        <tr>
          <th><input type="checkbox" onclick="document.querySelectorAll('.ck').forEach(c=>c.checked=this.checked)"></th>
          <th>Employee</th>
          <th>Department</th>
          <th>Base</th>
          <th>Extra</th>
          <th>Deduction</th>
          <th>Advance (auto)</th>
          <th>Net Pay</th>
        </tr>
      </thead>
      <tbody>
        <?php
        if (empty($rows)){
          echo "<tr><td colspan='8' style='text-align:center;color:#888'>No rows</td></tr>";
        } else {
          // sort by name
          usort($rows, fn($a,$b)=>strcmp($a['employee_name'],$b['employee_name']));
          foreach ($rows as $r){
            $eid = (int)$r['employee_id'];
            echo "<tr>";
            echo "<td><input class='ck' type='checkbox' name='sel[]' value='{$eid}' checked></td>";
            echo "<td>".htmlspecialchars($r['employee_name'])."</td>";
            echo "<td>".htmlspecialchars($r['department'])."</td>";
            echo "<td>".number_format((float)$r['base_salary'],0)."</td>";
            echo "<td>".number_format((float)$r['extra'],0)."</td>";
            echo "<td>".number_format((float)$r['deduction'],0)."</td>";
            echo "<td>".number_format((float)$r['adv_auto'],0)."</td>";
            echo "<td><strong>".number_format((float)$r['net_pay'],0)."</strong></td>";
            echo "</tr>";
          }
        }
        ?>
      </tbody>
      <tfoot>
        <tr>
          <td colspan="3">Totals</td>
          <td><?php echo number_format($grand['base'],0); ?></td>
          <td><?php echo number_format($grand['extra'],0); ?></td>
          <td><?php echo number_format($grand['ded'],0); ?></td>
          <td><?php echo number_format($grand['adv'],0); ?></td>
          <td><?php echo number_format($grand['net'],0); ?></td>
        </tr>
      </tfoot>
    </table>
  </form>

<div class="card muted" style="font-size:13px">
    <strong>Notes:</strong>
    <ul>
      <li><em>Advance (auto)</em> सिर्फ सुझाव है: <code>remaining_amount / deduct_parts</code> (या पूरा शेष अगर parts=0/NULL)। यह पेज advance टेबल को अपडेट नहीं करता।</li>
      <li>Period = <code>H1</code> (1–15), <code>H2</code> (16–End), <code>FULL</code> (Monthly), और <code>H2FULL</code> में Attendance rows (Semi-monthly H2 + Monthly FULL) दोनों उठते हैं; Extra/Deduction/Advance पूरी महीने की विंडो से लिए जाते हैं ताकि Monthly वालों का पूरा कवर हो जाए।</li>
      <li>Export पर चुनी हुई rows का JSON <code>/erp/payment_export.php</code> को POST होता है (साथ में <em>period_label</em>, <em>month_label</em>, <em>narration</em>) — वह आपकी existing फाइल Excel/CSV बना देगी।</li>
    </ul>
  </div>
</div>
</body>
</html>