« Back to History
pasaria_salary_report.php
|
20261119_140743.php
Initial Domain Snapshot
Copy Code
<?php /* ============================================================================= File: /erp/pasaria_salary_report.php Title: Pasaria Salary Report — semi-monthly + Advance Deduction + EmpID auto-map + De-dup Scope: Page-local only (NO global/base edits). Multi-company aware. ============================================================================= */ 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); $startDateShort = date('Y-m-d', strtotime($start)); $endDateShort = date('Y-m-d', strtotime($end)); /* ---- 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)); $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 EMP MAP ---- */ $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); /* ---- FETCH ADVANCES FOR EMPLOYEES IN PERIOD ---- */ $advByEmp = []; $stAdv = $pdo->prepare(" SELECT employee_id, COALESCE(SUM(amount),0) AS total_adv FROM employee_advance WHERE company_id = ? AND date BETWEEN ? AND ? GROUP BY employee_id "); $stAdv->execute([$company_id, $startDateShort, $endDateShort]); foreach ($stAdv->fetchAll(PDO::FETCH_ASSOC) as $ar) { $advByEmp[(int)$ar['employee_id']] = (float)$ar['total_adv']; } /* ---- Process Jobs & Deduplication ---- */ $detail = []; $summary = []; $totalJobs = 0; $totalAmount = 0.0; $totalAdvance = 0.0; $totalNet = 0.0; $seen = []; foreach ($rows as $r){ $dateD = date('Y-m-d', strtotime($r['entry_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; 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,'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,'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,'counts'=>[]]; $summary[$key]['jobs'] += 1; $summary[$key]['amount'] += $amount; $summary[$key]['counts'][$typeKey] = ($summary[$key]['counts'][$typeKey] ?? 0) + 1; $totalJobs++; $totalAmount += $amount; } } if ($dedupe){ foreach($seen as $k=>$node){ $d = $node['first']; $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,'counts'=>[]]; $summary[$key]['jobs'] += 1; $summary[$key]['amount'] += $d['amount']; $summary[$key]['counts'][$d['type']] = ($summary[$key]['counts'][$d['type']] ?? 0) + 1; $totalJobs++; $totalAmount += $d['amount']; } } /* Calculate Advances & Net Pay on Summary level */ foreach ($summary as $key => &$s) { $emp_id = $s['id'] ? (int)$s['id'] : 0; $adv = $emp_id > 0 ? ($advByEmp[$emp_id] ?? 0.0) : 0.0; $s['advance'] = $adv; $s['net'] = max(0, $s['amount'] - $adv); $totalAdvance += $adv; $totalNet += $s['net']; } unset($s); /* ---- Employee dropdown options ---- */ $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 Export Arrays ---- */ $salary_rows = []; $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 ((!$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'] ?? ''; } } $gross_val = round((float)$s['amount'], 2); $adv_val = round((float)$s['advance'], 2); $net_val = round((float)$s['net'], 2); $salary_rows[] = [ 'employee_id' => $emp_id, 'employee_name' => $emp_name, 'jobs' => (int)$s['jobs'], 'amount' => $net_val, 'gross_amount' => $gross_val, 'advance' => $adv_val, 'net_pay' => $net_val, 'counts' => $s['counts'] ?? [], '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); /* ---- CSV export ---- */ 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','Gross Amount','Advance (-)','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['amount'],2,'.',''),number_format($s['advance'],2,'.',''),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','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,'.',''), $d['machine'],$d['pbn'],$d['sbn'],$d['dup']]); } fclose($out); exit; } /* ---- styles ---- */ $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;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(4,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;}} 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 = Gross Salary - Advance Taken. 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>Unique jobs</b><span><?php echo (int)$totalJobs; ?></span></div> <div class="kpi"><b>Gross Salary</b><span>₹<?php echo number_format($totalAmount,2); ?></span></div> <div class="kpi"><b>Total Advance (-)</b><span style="color:#c92a2a;">₹<?php echo number_format($totalAdvance,2); ?></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> <?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"> <input type="hidden" name="salary_rows" value="<?php echo h($salary_rows_json); ?>"> <input type="hidden" name="salary_rows_b64" value="<?php echo h($salary_rows_b64); ?>"> <?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>Gross Amount</th> <th style="color:#c92a2a;">Advance (-)</th> <th>Total NET_PAY</th> </tr> </thead> <tbody> <?php if(!$summary): ?> <tr><td colspan="<?php echo 7+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['amount'],2); ?></td> <td style="color:#c92a2a;">₹<?php echo number_format($s['advance'],2); ?></td> <td><b>₹<?php echo number_format($s['net'],2); ?></b></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>Machine</th><th>Primary Beam</th><th>Secondary Beam</th><th>Dup ×N</th> </tr> </thead> <tbody> <?php if(!$detail): ?> <tr><td colspan="10">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 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>