« Back to History
pasaria_salary_report.php
|
20260920_164915.php
Initial Domain Snapshot
Copy Code
<?php /* ============================================================================= File: /erp/pasaria_salary_report.php Title: Pasaria Salary Report — semi-monthly + EmpID auto-map + De-dup + Payments export (with bank fields) Scope: Page-local only (NO global/base edits). Multi-company aware. Changes: - "Total Amount" renamed/displayed as "Total NET_PAY" - salary_rows now include explicit 'net_pay' and 'amount' (amount = net_pay) - Export payloads unchanged except include net_pay field so other exporters can consume it - Filter card forced to single-row layout (no wrapping) with horizontal overflow ============================================================================= */ header('X-Frame-Options: SAMEORIGIN'); error_reporting(E_ALL); ini_set('display_errors', isset($_GET['debug']) && $_GET['debug']=='1' ? '1' : '0'); 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'; } if (!$pdo instanceof PDO) { die('DB not ready'); } /* ---- helpers ---- */ function h($s){ return htmlspecialchars((string)$s, ENT_QUOTES, 'UTF-8'); } function lastDay($y,$m){ return date('Y-m-t', strtotime(sprintf('%04d-%02d-01', $y, $m))); } /* ---- inputs ---- */ $year = isset($_GET['year']) ? (int)$_GET['year'] : (int)date('Y'); $month = isset($_GET['month']) ? (int)$_GET['month'] : (int)date('m'); $period = isset($_GET['period']) ? $_GET['period'] : '1-15'; // 1-15 | 16-end $empkey = isset($_GET['employee_key']) ? trim($_GET['employee_key']) : ''; // id:* | name:* $type = isset($_GET['pasaria_type']) ? trim($_GET['pasaria_type']) : ''; $export = isset($_GET['export']) ? $_GET['export'] : ''; $dedupe = isset($_GET['dedupe']) ? (int)$_GET['dedupe'] : 1; // default ON $start = $period==='16-end' ? sprintf('%04d-%02d-16 00:00:00', $year,$month) : sprintf('%04d-%02d-01 00:00:00', $year,$month); $end = $period==='16-end' ? sprintf('%s 23:59:59', lastDay($year,$month)) : sprintf('%04d-%02d-15 23:59:59', $year,$month); /* ---- Rate map (type -> rate) ---- */ $rateMap = []; $qr = $pdo->prepare("SELECT pasaria_type, MAX(rate) rate FROM pasaria_rates WHERE company_id=? GROUP BY pasaria_type"); $qr->execute([$company_id]); foreach($qr->fetchAll(PDO::FETCH_ASSOC) as $r){ $rateMap[trim($r['pasaria_type'])] = (float)$r['rate']; } /* ---- WHERE (date + type) ---- */ $where = " WHERE pe.company_id=:cid AND pe.entry_date BETWEEN :s AND :e "; $params = [':cid'=>$company_id, ':s'=>$start, ':e'=>$end]; if ($type!==''){ $where .= " AND TRIM(pe.pasaria_type)=:pt "; $params[':pt']=$type; } /* ---- employee filter ---- */ $having = ''; $empFilterText = ''; if ($empkey!==''){ if (strpos($empkey,'id:')===0){ $eid = (int)substr($empkey,3); $having = " HAVING emp_id_map = :eid "; $params[':eid'] = $eid; $empFilterText = "Employee ID #".$eid; } elseif (strpos($empkey,'name:')===0){ $nm = trim(substr($empkey,5)); // keep filtering by raw pe.employee_name for accuracy (existing behaviour) $where .= " AND TRIM(pe.employee_name)=:enm "; $params[':enm'] = $nm; $empFilterText = "Employee ".h($nm); } } /* ---- types for dropdown ---- */ $types = []; $stt = $pdo->prepare("SELECT DISTINCT TRIM(pe.pasaria_type) t FROM pasaria_entry pe $where ORDER BY t"); $stt->execute($params); foreach($stt->fetchAll(PDO::FETCH_COLUMN,0) as $t){ if($t!=='') $types[]=$t; } /* ---- DETAIL (with EmpID auto-map from company_employee_master) ---- - LEFT JOIN company_employee_master to pick mapped id/name. - employee_name prefers pe.employee_name, fallback to cem.name. - emp_id_map prefers pe.employee_id, otherwise cem.id. --------------------------------------------------------------------------- */ $sql = " SELECT pe.id, pe.entry_date, TRIM(pe.pasaria_type) AS pasaria_type, pe.machine_no, TRIM(COALESCE(NULLIF(pe.employee_name,''), cem.name, '')) AS employee_name, pe.primary_beam, pe.primary_beam_no, pe.secondary_beam, pe.secondary_beam_no, COALESCE(pe.employee_id, cem.id) AS emp_id_map FROM pasaria_entry pe LEFT JOIN company_employee_master cem ON cem.company_id = pe.company_id AND ( (pe.employee_id IS NOT NULL AND cem.id = pe.employee_id) OR (pe.employee_id IS NULL AND TRIM(cem.name) = TRIM(pe.employee_name)) ) $where $having ORDER BY pe.entry_date ASC, pe.id ASC "; $st = $pdo->prepare($sql); $st->execute($params); $rows = $st->fetchAll(PDO::FETCH_ASSOC); $originalRowCount = count($rows); /* ---- De-duplicate same jobs by a natural key (date D + machine + type + P/S beam no + emp) ---- */ $detail = []; $summary = []; $totalJobs = 0; $totalNet = 0.0; // total NET_PAY (was Total Amount) $seen = []; // job_key => ['count'=>N, 'first'=>array] foreach ($rows as $r){ $dateD = date('Y-m-d', strtotime($r['entry_date'])); // collapse by calendar date $eid = $r['emp_id_map'] ? (int)$r['emp_id_map'] : 0; $ename = $r['employee_name'] !== '' ? $r['employee_name'] : '—'; $typeKey = $r['pasaria_type'] ?? ''; $pbn = trim(($r['primary_beam']??'').' '.($r['primary_beam_no']??'')); $sbn = trim(($r['secondary_beam']??'').' '.($r['secondary_beam_no']??'')); $jobKey = implode('||', [$dateD,$r['machine_no'],$typeKey,$pbn,$sbn, $eid?('ID:'.$eid):('NM:'.$ename)]); $rate = $rateMap[$typeKey] ?? 0.0; $amount = $rate * 1.0; // 1 job per unique jobKey $net_pay = $amount; // for pasaria there are no extras/deductions here — treat amount as net_pay if ($dedupe){ if (!isset($seen[$jobKey])){ $seen[$jobKey] = ['count'=>1, 'first'=>[ 'date'=>$r['entry_date'],'employee_name'=>$ename,'emp_id'=>$eid?:null,'type'=>$typeKey, 'rate'=>$rate,'amount'=>$amount,'net_pay'=>$net_pay,'machine'=>$r['machine_no'],'pbn'=>$pbn,'sbn'=>$sbn,'dup'=>0 ]]; } else { $seen[$jobKey]['count']++; $seen[$jobKey]['first']['dup'] = $seen[$jobKey]['count'] - 1; } } else { $detail[] = [ 'date'=>$r['entry_date'],'employee_name'=>$ename,'emp_id'=>$eid?:null,'type'=>$typeKey, 'rate'=>$rate,'amount'=>$amount,'net_pay'=>$net_pay,'machine'=>$r['machine_no'],'pbn'=>$pbn,'sbn'=>$sbn,'dup'=>0 ]; $key = $eid ? ('id:'.$eid) : ('name:'.$ename); if (!isset($summary[$key])) $summary[$key]=['id'=>$eid?:null,'name'=>$ename,'jobs'=>0,'amount'=>0.0,'net'=>0.0,'counts'=>[]]; $summary[$key]['jobs'] += 1; $summary[$key]['amount'] += $amount; $summary[$key]['net'] += $net_pay; $summary[$key]['counts'][$typeKey] = ($summary[$key]['counts'][$typeKey] ?? 0) + 1; $totalJobs++; $totalNet += $net_pay; } } /* fold de-duplicated set into detail/summary */ if ($dedupe){ foreach($seen as $k=>$node){ $d = $node['first']; // single job $detail[] = $d; $key = $d['emp_id'] ? ('id:'.$d['emp_id']) : ('name:'.$d['employee_name']); if (!isset($summary[$key])) $summary[$key]=['id'=>$d['emp_id'],'name'=>$d['employee_name'],'jobs'=>0,'amount'=>0.0,'net'=>0.0,'counts'=>[]]; $summary[$key]['jobs'] += 1; $summary[$key]['amount'] += $d['amount']; $summary[$key]['net'] += $d['net_pay']; $summary[$key]['counts'][$d['type']] = ($summary[$key]['counts'][$d['type']] ?? 0) + 1; $totalJobs++; $totalNet += $d['net_pay']; } } /* ---- Employee dropdown (mapped IDs + names) ---- */ $empOptions=[]; $es = $pdo->prepare(" SELECT DISTINCT COALESCE(pe.employee_id, cem.id) AS eid, TRIM(COALESCE(NULLIF(pe.employee_name,''), cem.name, '')) AS en FROM pasaria_entry pe LEFT JOIN company_employee_master cem ON cem.company_id = pe.company_id AND ( (pe.employee_id IS NOT NULL AND cem.id = pe.employee_id) OR (pe.employee_id IS NULL AND TRIM(cem.name) = TRIM(pe.employee_name)) ) WHERE pe.company_id=? ORDER BY en "); $es->execute([$company_id]); foreach($es->fetchAll(PDO::FETCH_ASSOC) as $e){ $label = $e['eid'] ? ($e['en'].' (#'.$e['eid'].')') : ($e['en'] ?: '—'); $key = $e['eid'] ? ('id:'.$e['eid']) : ('name:'.$e['en']); if ($label==='') $label='—'; $empOptions[] = ['key'=>$key,'label'=>$label]; } /* ---- Prepare salary_rows for employee_salary_export.php INCLUDING BANK DETAILS ---- 1) If employee_id present -> fetch bank details by id 2) Else -> try matching by name Bank columns assumed: beneficiary_name, account_number, ifsc (adjust if your schema differs) ------------------------------------------------------------------------- */ $salary_rows = []; // prepared statements for bank lookup $bankByIdStmt = $pdo->prepare("SELECT TRIM(beneficiary_name) beneficiary_name, TRIM(account_number) account_number, TRIM(ifsc) ifsc FROM company_employee_master WHERE company_id = ? AND id = ? LIMIT 1"); $bankByNameStmt = $pdo->prepare("SELECT TRIM(beneficiary_name) beneficiary_name, TRIM(account_number) account_number, TRIM(ifsc) ifsc FROM company_employee_master WHERE company_id = ? AND TRIM(name) = TRIM(?) LIMIT 1"); foreach($summary as $s){ $emp_id = $s['id'] ?? null; $emp_name = $s['name'] ?? ''; $beneficiary_name = ''; $account_number = ''; $ifsc = ''; if ($emp_id){ $bankByIdStmt->execute([$company_id, (int)$emp_id]); $bd = $bankByIdStmt->fetch(PDO::FETCH_ASSOC); if ($bd){ $beneficiary_name = $bd['beneficiary_name'] ?? ''; $account_number = $bd['account_number'] ?? ''; $ifsc = $bd['ifsc'] ?? ''; } } // if still blank, try matching by name if ((!$beneficiary_name && !$account_number && !$ifsc) && trim($emp_name) !== ''){ $bankByNameStmt->execute([$company_id, $emp_name]); $bd2 = $bankByNameStmt->fetch(PDO::FETCH_ASSOC); if ($bd2){ $beneficiary_name = $bd2['beneficiary_name'] ?? ''; $account_number = $bd2['account_number'] ?? ''; $ifsc = $bd2['ifsc'] ?? ''; } } // For exporters we want 'amount' to be the NET_PAY value (so warper exporter can consume it as net_pay) $net_pay_val = round((float)$s['net'], 2); $amount_val = $net_pay_val; $salary_rows[] = [ 'employee_id' => $emp_id, 'employee_name' => $emp_name, 'jobs' => (int)$s['jobs'], // both amount and net_pay provided; amount kept for backward compatibility 'amount' => $amount_val, 'net_pay' => $net_pay_val, 'counts' => $s['counts'] ?? [], // bank fields expected by semi_exporter / employee_salary_export 'beneficiary_name' => $beneficiary_name, 'account_number' => $account_number, 'ifsc' => $ifsc ]; } $salary_rows_json = json_encode($salary_rows); $salary_rows_b64 = base64_encode($salary_rows_json); $salary_rows_semi_arr = []; foreach($salary_rows as $r){ $salary_rows_semi_arr[] = [ 'employee_id' => $r['employee_id'], 'employee_name' => $r['employee_name'], 'beneficiary_name'=> $r['beneficiary_name'] ?? '', 'account_number' => $r['account_number'] ?? '', 'ifsc' => isset($r['ifsc']) ? strtoupper($r['ifsc']) : '', // amount here is NET_PAY (for downstream exporters expecting 'amount') 'amount' => $r['amount'], 'net_pay' => $r['net_pay'], 'jobs' => $r['jobs'], 'source' => 'pasaria', 'year' => (int)$year, 'month' => (int)$month, 'period' => $period ]; } $salary_rows_semi_json = json_encode($salary_rows_semi_arr); /* ---- CSV export (unchanged content except clarity) ---- */ if ($export==='csv'){ header('Content-Type: text/csv; charset=utf-8'); header('Content-Disposition: attachment; filename=pasaria_salary_'.$year.'_'.$month.'_'.$period.($dedupe?'_dedup':'').'.csv'); $out = fopen('php://output','w'); fputcsv($out, ['Period',"$year-$month ($period)",'Company',$company_id,'Dedupe',$dedupe?'ON':'OFF']); fputcsv($out, ['Original rows',$originalRowCount,'Unique jobs',$totalJobs]); fputcsv($out,[]); $sumHead=['Employee','Emp ID','Total Jobs','Total NET_PAY']; foreach($types as $t){ $sumHead[]="Jobs [$t]"; } fputcsv($out,$sumHead); foreach($summary as $s){ $row=[$s['name'],$s['id'],$s['jobs'],number_format($s['net'],2,'.','')]; foreach($types as $t){ $row[]=(int)($s['counts'][$t]??0); } fputcsv($out,$row); } fputcsv($out,[]); fputcsv($out, ['Date','Employee','Emp ID','Type','Rate','Amount','NET_PAY','Machine','Primary Beam','Secondary Beam','Duplicates Collapsed']); foreach($detail as $d){ fputcsv($out, [$d['date'],$d['employee_name'],$d['emp_id'],$d['type'],$d['rate'], number_format($d['amount'],2,'.',''), number_format($d['net_pay'],2,'.',''), $d['machine'],$d['pbn'],$d['sbn'],$d['dup']]); } fclose($out); exit; } /* ---- styles + header include (filter card forced into single row) ---- */ $style = <<<CSS :root{--mm-green:#34A853;--mm-mint:#F5FFF7;--chip:#A7D8DE;} *{box-sizing:border-box} body{margin:0;font:14px/1.5 system-ui,-apple-system,Segoe UI,Roboto,Arial;background:#fafafa;color:#222;} .header{background:#fff;border-bottom:1px solid #eee;padding:10px 16px;position:sticky;top:0;z-index:9;} .container{max-width:1200px;margin:10px auto;padding:0 16px;} h1{font-size:20px;margin:6px 0 10px} .filters{ display:flex; gap:10px; align-items:flex-end; /* force single row: no wrap; allow horizontal scroll if too narrow */ flex-wrap:nowrap; overflow-x:auto; padding:6px; } .filters > div{ min-width:150px; display:flex; flex-direction:column; } .card{background:#fff;border:1px solid #eee;border-radius:12px;padding:12px;box-shadow:0 1px 2px rgba(0,0,0,.04);} .kpis{display:grid;grid-template-columns:repeat(3,1fr);gap:10px;margin:10px 0;} .kpi{padding:12px;border-radius:12px;background:var(--mm-mint);border:1px solid #e6f5ea;} .kpi b{display:block;font-size:12px;color:#444;margin-bottom:6px;} .kpi span{font-size:18px;color:#0b7a2a;font-weight:600;} .btn{display:inline-block;padding:8px 12px;border-radius:10px;border:1px solid #ddd;background:#fff;cursor:pointer;} .btn.primary{border-color:var(--mm-green);color:#fff;background:var(--mm-green);} .table{width:100%;border-collapse:collapse;font-size:13px;} .table th,.table td{border:1px solid #eee;padding:6px 8px;text-align:left;vertical-align:top;} .table th{background:#f8f9fa;font-weight:600;} .badge{display:inline-block;padding:2px 8px;border-radius:999px;background:var(--chip);font-size:12px;} @media print{.header,.filters,.actions{display:none!important;} body{background:#fff;} .card{box-shadow:none;border-color:#ccc;}} /* NOTE: Project rule prefers main.css; this page uses inline style only as page-local override */ CSS; ob_start(); if (is_file(__DIR__.'/partials/header.php')) include __DIR__.'/partials/header.php'; $headerHtml = ob_get_clean(); ?> <!doctype html> <html> <head> <meta charset="utf-8"> <title>Pasaria Salary Report</title> <style><?php echo $style; ?></style> </head> <body> <div class="header"> <?php echo $headerHtml; ?> <div class="container"> <h1>Pasaria Salary Report</h1> <div style="font-size:12px;color:#555;"> NET_PAY = Rate(pasaria_type) × Jobs (1 per <b>unique job</b>). Emp ID auto-mapped from <b>company_employee_master</b>. </div> </div> </div> <div class="container"> <form class="card filters" method="get"> <div><label>Month</label> <select name="month"><?php for($m=1;$m<=12;$m++): ?> <option value="<?php echo $m; ?>" <?php echo $m==$month?'selected':''; ?>> <?php echo date('M', mktime(0,0,0,$m,1,$year)); ?> </option><?php endfor; ?> </select> </div> <div><label>Year</label><input type="number" name="year" value="<?php echo h($year); ?>"></div> <div><label>Period</label> <select name="period"> <option value="1-15" <?php echo $period==='1-15'?'selected':''; ?>>1–15</option> <option value="16-end" <?php echo $period==='16-end'?'selected':''; ?>>16–End</option> </select> </div> <div><label>Employee</label> <select name="employee_key"> <option value="">All</option> <?php foreach($empOptions as $op): ?> <option value="<?php echo h($op['key']); ?>" <?php echo $empkey===$op['key']?'selected':''; ?>> <?php echo h($op['label']); ?> </option> <?php endforeach; ?> </select> </div> <div><label>Pasaria Type</label> <select name="pasaria_type"> <option value="">All</option> <?php foreach($types as $t): ?> <option value="<?php echo h($t); ?>" <?php echo $type===$t?'selected':''; ?>> <?php echo h($t); ?> </option> <?php endforeach; ?> </select> </div> <div> <label>De-duplicate same jobs</label> <label style="display:inline-flex;gap:6px;align-items:center;margin-top:6px;"> <input type="checkbox" name="dedupe" value="1" <?php echo $dedupe? 'checked':''; ?>> <span>ON</span> </label> </div> <div style="display:flex;align-items:flex-end;"> <button class="btn primary" type="submit">Apply</button> </div> </form> <div class="kpis"> <div class="kpi"><b>Original rows</b><span><?php echo (int)$originalRowCount; ?></span></div> <div class="kpi"><b>Unique jobs (after de-dup)</b><span><?php echo (int)$totalJobs; ?></span></div> <div class="kpi"><b>Total NET_PAY</b><span><?php echo number_format($totalNet,2); ?></span></div> </div> <div class="actions" style="display:flex;gap:8px;align-items:center;margin:8px 0 12px;"> <a class="btn" href="?<?php $q=$_GET; $q['export']='csv'; echo h(http_build_query($q)); ?>">Export CSV</a> <!-- Export Payments: POST to employee_salary_export.php with salary_rows (plain JSON) + fallbacks --> <?php $hasSalaryRows = (count($salary_rows) > 0); ?> <form method="post" action="/erp/employee_salary_export.php" target="_blank" style="display:inline-block;margin:0;"> <input type="hidden" name="company_id" value="<?php echo h($company_id); ?>"> <input type="hidden" name="year" value="<?php echo h($year); ?>"> <input type="hidden" name="month" value="<?php echo h($month); ?>"> <input type="hidden" name="period" value="<?php echo h($period); ?>"> <input type="hidden" name="dedupe" value="<?php echo h($dedupe); ?>"> <input type="hidden" name="source" value="pasaria"> <!-- PRIMARY: what employee_salary_export.php expects --> <input type="hidden" name="salary_rows" value="<?php echo h($salary_rows_json); ?>"> <!-- FALLBACKS --> <input type="hidden" name="salary_rows_b64" value="<?php echo h($salary_rows_b64); ?>"> <input type="hidden" name="salary_rows_semi" value="<?php echo h($salary_rows_semi_json); ?>"> <!-- helpful metadata in case exporter falls back to server-side source discovery --> <input type="hidden" name="source_table" value="pasaria_entry"> <input type="hidden" name="employee_col" value="employee_id"> <input type="hidden" name="amount_col" value="amount"> <?php if($hasSalaryRows): ?> <button class="btn" type="submit">Export Payments</button> <?php else: ?> <button class="btn" type="button" disabled title="No rows to export">Export Payments</button> <span style="font-size:12px;color:#a00;margin-left:8px;">No salary rows available for export.</span> <?php endif; ?> </form> <button class="btn" onclick="window.print()">Print</button> <span style="font-size:12px;color:#555;">Rates: <span class="badge">pasaria_rates</span></span> </div> <!-- SUMMARY --> <div class="card" style="margin-bottom:12px;"> <h3 style="margin:0 0 8px;">Summary (Employee-wise)</h3> <table class="table"> <thead> <tr> <th>Employee</th><th>Emp ID</th><th>Total Jobs</th> <?php foreach($types as $t): ?><th>Jobs (<?php echo h($t); ?>)</th><?php endforeach; ?> <th>Total NET_PAY</th> </tr> </thead> <tbody> <?php if(!$summary): ?> <tr><td colspan="<?php echo 5+count($types); ?>">No data</td></tr> <?php else: foreach($summary as $s): ?> <tr> <td><?php echo h($s['name']); ?></td> <td><?php echo h($s['id']); ?></td> <td><?php echo (int)$s['jobs']; ?></td> <?php foreach($types as $t): ?><td><?php echo (int)($s['counts'][$t]??0); ?></td><?php endforeach; ?> <td><?php echo number_format($s['net'],2); ?></td> </tr> <?php endforeach; endif; ?> </tbody> </table> </div> <!-- DETAIL --> <div class="card"> <h3 style="margin:0 0 8px;">Detail</h3> <table class="table"> <thead> <tr> <th>Date</th><th>Employee</th><th>Emp ID</th><th>Type</th> <th>Rate</th><th>Amount</th><th>NET_PAY</th><th>Machine</th><th>Primary Beam</th><th>Secondary Beam</th><th>Dup ×N</th> </tr> </thead> <tbody> <?php if(!$detail): ?> <tr><td colspan="11">No data</td></tr> <?php else: foreach($detail as $d): ?> <tr> <td><?php echo h($d['date']); ?></td> <td><?php echo h($d['employee_name']); ?></td> <td><?php echo h($d['emp_id']); ?></td> <td><?php echo h($d['type']); ?></td> <td><?php echo number_format($d['rate'],2); ?></td> <td><?php echo number_format($d['amount'],2); ?></td> <td><?php echo number_format($d['net_pay'],2); ?></td> <td><?php echo h($d['machine']); ?></td> <td><?php echo h($d['pbn']); ?></td> <td><?php echo h($d['sbn']); ?></td> <td><?php echo (int)$d['dup']; ?></td> </tr> <?php endforeach; endif; ?> </tbody> </table> </div> <div style="height:24px"></div> </div> </body> </html>