« Back to History
salary_report.php
|
20260921_175631.php
Initial Domain Snapshot
Copy Code
<?php /* ============================================================================= Salary Report (Karigar × Quality) — header = unique Qualities, with rates - Filters: Khata (MST1… + All), Machine (+ All), Month, Half, Year - Source: loom_production_entry.lines_json [{"karigar_id","meter","date"}] Row's quality_id is used for that line's quality. - Rates: quality_rates.quality_name (case-insensitive). If quality_id→name नहीं मिला, rate 0 माना जाएगा. - Footer: Row 1 = Total Mtr (per quality); Row 2 = Amount (= Total Mtr × rate) - Page-local only. No global/base changes. ============================================================================= */ error_reporting(E_ALL); ini_set('display_errors', 1); require __DIR__ . '/../modules/auth/auth.php'; require_login(); $u = auth_user(); $company_id = (int)$u['company_id']; $pdo = $GLOBALS['pdo'] ?? null; if (!$pdo) { require __DIR__ . '/../core/db.php'; } /* ---------- Helpers ---------- */ function h($s){ return htmlspecialchars((string)$s, ENT_QUOTES, 'UTF-8'); } function half_month_range(int $y,int $m,int $half){ $start = sprintf('%04d-%02d-%02d',$y,$m,$half===1?1:16); $end = sprintf('%04d-%02d-%02d',$y,$m,$half===1?15:(int)date('t',strtotime("$y-$m-01"))); return [$start,$end]; } function norm_line_date($v,int $Y,int $M,$row_entry){ $s=is_string($v)||is_int($v)?trim((string)$v):''; if($s==='') return $row_entry?:''; if(preg_match('/^\d{4}-\d{2}-\d{2}$/',$s)) return $s; if(preg_match('/^\d{1,2}$/',$s)){ $d=max(1,min(31,(int)$s)); $last=(int)date('t',strtotime("$Y-$M-01")); $d=min($d,$last); return sprintf('%04d-%02d-%02d',$Y,$M,$d); } $t=strtotime($s); return $t?date('Y-m-d',$t):($row_entry?:''); } function table_exists(PDO $pdo, string $name): bool { try{ $st=$pdo->prepare("SHOW TABLES LIKE ?"); $st->execute([$name]); return (bool)$st->fetchColumn(); }catch(Throwable $e){ return false; } } function cols_of(PDO $pdo, string $tbl): array { try{ $rs=$pdo->query("SHOW COLUMNS FROM `$tbl`")->fetchAll(PDO::FETCH_ASSOC); return array_column($rs,'Field'); }catch(Throwable $e){ return []; } } /* ---------- Load filter options ---------- */ function load_khatas(PDO $pdo, int $co): array { try{ $st=$pdo->prepare("SELECT DISTINCT khata_id AS id, khata_code FROM loom_karigar_master WHERE company_id=? AND khata_id IS NOT NULL AND khata_code<>'' ORDER BY khata_id"); $st->execute([$co]); $rows=$st->fetchAll(PDO::FETCH_ASSOC); if($rows){ foreach($rows as &$r){ $r['label']=$r['khata_code']; } return $rows; } }catch(Throwable $e){} return []; } function load_machines(PDO $pdo,int $co): array { foreach([ "SELECT id, machine_no AS label, khata_id FROM machines WHERE company_id=? ORDER BY machine_no", "SELECT id, code AS label, khata_id FROM machines WHERE company_id=? ORDER BY code", ] as $sql){ try{ $st=$pdo->prepare($sql); $st->execute([$co]); $a=$st->fetchAll(PDO::FETCH_ASSOC); if($a) return $a; }catch(Throwable $e){} } return []; } function load_karigar_names(PDO $pdo,int $co,array $ids): array { $ids=array_values(array_unique(array_map('intval',$ids))); if(!$ids) return []; $in=implode(',',array_fill(0,count($ids),'?')); try{ $st=$pdo->prepare("SELECT id, karigar_name FROM loom_karigar_master WHERE company_id=? AND id IN ($in)"); $st->execute(array_merge([$co],$ids)); $m=[]; foreach($st as $r){ $m[(int)$r['id']]=$r['karigar_name']; } if($m) return $m; }catch(Throwable $e){} try{ $st=$pdo->prepare("SELECT id, name FROM employees WHERE company_id=? AND id IN ($in)"); $st->execute(array_merge([$co],$ids)); $m=[]; foreach($st as $r){ $m[(int)$r['id']]=$r['name']; } if($m) return $m; }catch(Throwable $e){} return []; } /* Quality names (from likely masters) + Rates by quality_rates.quality_name */ function quality_name_map(PDO $pdo,int $co,array $qids): array { $qids=array_values(array_unique(array_map('intval',$qids))); if(!$qids) return []; $in = implode(',',array_fill(0,count($qids),'?')); $map=[]; foreach([ "SELECT id, name FROM qualities WHERE company_id=? AND id IN (%s)", "SELECT id, quality_name AS name FROM quality_master WHERE company_id=? AND id IN (%s)", "SELECT id, name FROM fabrics WHERE company_id=? AND id IN (%s)", ] as $tpl){ try{ $sql=sprintf($tpl,$in); $st=$pdo->prepare($sql); $st->execute(array_merge([$co],$qids)); foreach($st as $r){ $map[(int)$r['id']] = (string)$r['name']; } if(count($map)===count($qids)) break; }catch(Throwable $e){} } foreach($qids as $id){ if(!isset($map[$id])) $map[$id] = 'Q'.$id; } return $map; } function rates_by_name(PDO $pdo,int $co): array { $rates=[]; if(!table_exists($pdo,'quality_rates')) return $rates; try{ $st=$pdo->prepare("SELECT quality_name, rate FROM quality_rates WHERE company_id=?"); $st->execute([$co]); foreach($st as $r){ $nm=strtolower(trim((string)$r['quality_name'])); if($nm==='') continue; $rates[$nm]=(float)$r['rate']; } }catch(Throwable $e){} return $rates; } /* Sum helpers for extras/adv/ded tables (optional) */ function sum_adjustments(PDO $pdo,string $tbl,int $co,array $kar_ids,string $from,string $to,?int $khata): array { if(!table_exists($pdo,$tbl)) return array_fill_keys($kar_ids, 0.0); $cols = cols_of($pdo,$tbl); $dateCol = in_array('entry_date',$cols,true)?'entry_date':(in_array('date',$cols,true)?'date':null); if(!$dateCol || !in_array('karigar_id',$cols,true) || !in_array('amount',$cols,true)) { return array_fill_keys($kar_ids, 0.0); } $filterByKhata = $khata && in_array('khata_id',$cols,true); $in=implode(',',array_fill(0,count($kar_ids),'?')); $sql = "SELECT karigar_id, SUM(amount) AS s FROM `$tbl` WHERE company_id=? AND `$dateCol` BETWEEN ? AND ? AND karigar_id IN ($in)"; $params = array_merge([$co,$from,$to], $kar_ids); if($filterByKhata){ $sql.=" AND khata_id=?"; $params[]=$khata; } $sql .= " GROUP BY karigar_id"; $out = array_fill_keys($kar_ids, 0.0); try{ $st=$pdo->prepare($sql); $st->execute($params); while($r=$st->fetch(PDO::FETCH_ASSOC)){ $out[(int)$r['karigar_id']] = (float)$r['s']; } }catch(Throwable $e){} return $out; } /* ---------- Filters ---------- */ $khatas = load_khatas($pdo,$company_id); $machines = load_machines($pdo,$company_id); $sel_khata = isset($_GET['khata_id']) ? (int)$_GET['khata_id'] : 0; // 0=All $sel_machine = isset($_GET['machine_id']) ? (int)$_GET['machine_id'] : 0; // 0=All $sel_year = isset($_GET['year']) ? (int)$_GET['year'] : (int)date('Y'); $sel_month = isset($_GET['month']) ? (int)$_GET['month'] : (int)date('n'); $sel_half = isset($_GET['half']) ? (int)$_GET['half'] : 1; [$from_date,$to_date] = half_month_range($sel_year,$sel_month,$sel_half); $month_start = sprintf('%04d-%02d-01',$sel_year,$sel_month); $month_end = sprintf('%04d-%02d-%02d',$sel_year,$sel_month,(int)date('t',strtotime($month_start))); /* ---------- Read & Aggregate ---------- */ $meters = []; // [karigar_id][quality_id] = meters $kar_ids = []; // set $qids = []; // set try{ $sql = "SELECT entry_date, khata_id, machine_id, quality_id, lines_json FROM loom_production_entry WHERE company_id=? AND entry_date BETWEEN ? AND ?"; $params = [$company_id, $month_start, $month_end]; if ($sel_khata) { $sql .= " AND khata_id=?"; $params[] = $sel_khata; } if ($sel_machine) { $sql .= " AND machine_id=?"; $params[] = $sel_machine; } $sql .= " ORDER BY entry_date"; $st=$pdo->prepare($sql); $st->execute($params); while($r=$st->fetch(PDO::FETCH_ASSOC)){ $row_entry = $r['entry_date'] ?? ''; $q = (int)($r['quality_id'] ?? 0); $arr = json_decode((string)$r['lines_json'], true); if(!is_array($arr)) continue; foreach($arr as $ln){ $kid = (int)($ln['karigar_id'] ?? 0); $mtr = (float)($ln['meter'] ?? 0); $ld = norm_line_date($ln['date'] ?? '', $sel_year,$sel_month,$row_entry); if($kid<=0 || $mtr<=0 || !$ld) continue; if($ld < $from_date || $ld > $to_date) continue; $meters[$kid][$q] = ($meters[$kid][$q] ?? 0) + $mtr; $kar_ids[$kid] = true; $qids[$q] = true; } } }catch(Throwable $e){ /* ignore on UI */ } /* Build dimensions */ $kar_list = array_keys($kar_ids); sort($kar_list); $kar_name_map = load_karigar_names($pdo,$company_id,$kar_list); $qual_list = array_keys($qids); sort($qual_list); $qual_name = quality_name_map($pdo,$company_id,$qual_list); /* Rates strictly by name from quality_rates (case-insensitive) */ $rateByName = rates_by_name($pdo,$company_id); $qual_rate = []; foreach($qual_list as $qid){ $nm = strtolower($qual_name[$qid] ?? ('q'.$qid)); $qual_rate[$qid] = $rateByName[$nm] ?? 0.0; } /* Extras / Deduction / Advance */ $extra_map = sum_adjustments($pdo,'extra_data', $company_id,$kar_list,$from_date,$to_date,$sel_khata); $deduction_map = sum_adjustments($pdo,'deduction_data', $company_id,$kar_list,$from_date,$to_date,$sel_khata); $advance_map = sum_adjustments($pdo,'advance_data', $company_id,$kar_list,$from_date,$to_date,$sel_khata); /* Totals (for footers) */ $colMetersTotals = array_fill_keys($qual_list, 0.0); $colAmountTotals = array_fill_keys($qual_list, 0.0); $tot_total_mtr=0.0; $tot_gross=0.0; $tot_extra=0.0; $tot_deduct=0.0; $tot_adv=0.0; $tot_net=0.0; ?> <!doctype html> <html> <head> <meta charset="utf-8"> <title>Salary Report (Karigar × Quality)</title> <meta name="viewport" content="width=device-width, initial-scale=1"> <style> :root{ --mm-primary:#34A853; --mm-bg:#F5FFF7; --mm-text:#333; --mm-sub:#5F6368; --mm-accent:#A7D8DE; --mm-hi:#FFF9C4; --card:#fff; --bd:#e6e6e6; } *{box-sizing:border-box} body{font-family:system-ui,Segoe UI,Roboto,Arial,sans-serif;background:var(--mm-bg);color:var(--mm-text);margin:0;padding:16px;} .wrap{max-width:1200px;margin:auto;} h1{margin:0 0 12px;font-size:22px;color:var(--mm-primary)} .card{background:var(--card);border:1px solid var(--bd);border-radius:14px;box-shadow:0 2px 10px rgba(0,0,0,.04);padding:12px;margin-bottom:14px;} /* Filters */ .filters{display:grid;grid-template-columns:repeat(8,minmax(120px,1fr));gap:10px;align-items:end} label{font-size:12px;color:var(--mm-sub);display:block;margin-bottom:4px} select,button{width:100%;padding:10px 12px;border:1px solid var(--bd);border-radius:12px;font-size:14px;background:#fff} .btn{background:var(--mm-primary);color:#fff;border:none;border-radius:12px;cursor:pointer;position:relative;transition:transform .08s ease} .btn:active{transform:scale(0.98)} .btn.loading{pointer-events:none;opacity:.9} .btn .spinner{display:none;width:16px;height:16px;border:2px solid #fff;border-right-color:transparent;border-radius:50%;position:absolute;right:12px;top:50%;transform:translateY(-50%);animation:spin .8s linear infinite} .btn.loading .spinner{display:inline-block} @keyframes spin{to{transform:translateY(-50%) rotate(360deg)}} .pill{display:inline-block;padding:2px 8px;border-radius:999px;background:#e8f5ee;color:var(--mm-primary);font-size:12px;margin-left:6px} .muted{color:var(--mm-sub);font-size:12px} /* Table */ .tbl-wrap{overflow-x:auto;border:1px solid var(--bd);border-radius:12px} table{width:100%;border-collapse:collapse;font-size:14px;min-width:940px} th,td{border-bottom:1px solid var(--bd);padding:8px 10px;text-align:right} thead th{background:var(--mm-accent)} th.kar,td.kar{position:sticky;left:0;background:#fff;border-right:1px solid var(--bd);text-align:left;white-space:nowrap;z-index:2} tbody tr:nth-child(odd){background:#fafafa} .total{background:var(--mm-hi);font-weight:600} /* Mobile */ @media (max-width: 980px){ .filters{grid-template-columns:repeat(2,1fr)} table{font-size:13px;min-width:820px} } @media (max-width: 420px){ table{font-size:12px;min-width:720px} } </style> </head> <body> <div class="wrap"> <h1>Salary Report <small class="pill">Karigar × Quality</small></h1> <!-- Filters --> <div class="card"> <form method="get" class="filters" id="filtersForm"> <div> <label>Khata</label> <select name="khata_id"> <option value="0" <?= $sel_khata===0?'selected':'' ?>>All</option> <?php foreach($khatas as $k): ?> <option value="<?= (int)$k['id'] ?>" <?= $sel_khata==(int)$k['id']?'selected':'' ?>><?= h($k['label']) ?></option> <?php endforeach; ?> </select> </div> <div> <label>Machine</label> <select name="machine_id"> <option value="0" <?= $sel_machine===0?'selected':'' ?>>All Machines</option> <?php foreach($machines as $m): ?> <option value="<?= (int)$m['id'] ?>" <?= $sel_machine==(int)$m['id']?'selected':'' ?>><?= 'MC '.h($m['label']) ?></option> <?php endforeach; ?> </select> </div> <div> <label>Month</label> <select name="month"><?php $mn=[1=>"Jan",2=>"Feb",3=>"Mar",4=>"Apr",5=>"May",6=>"Jun",7=>"Jul",8=>"Aug",9=>"Sep",10=>"Oct",11=>"Nov",12=>"Dec"]; foreach($mn as $i=>$nm): ?> <option value="<?= $i ?>" <?= $sel_month==$i?'selected':'' ?>><?= $nm ?></option><?php endforeach; ?> </select> </div> <div> <label>Half</label> <select name="half"> <option value="1" <?= $sel_half==1?'selected':'' ?>>1–15</option> <option value="2" <?= $sel_half==2?'selected':'' ?>>16–EOM</option> </select> </div> <div> <label>Year</label> <select name="year"><?php foreach([date('Y')-1,date('Y'),date('Y')+1] as $yr): ?> <option value="<?= (int)$yr ?>" <?= $sel_year==$yr?'selected':'' ?>><?= (int)$yr ?></option><?php endforeach; ?> </select> </div> <div> <button class="btn" type="submit" id="showBtn">Show Report <span class="spinner"></span></button> <div class="muted">Range: <span class="pill"><?= h($from_date) ?> → <?= h($to_date) ?></span></div> </div> </form> </div> <!-- Report table --> <div class="card tbl-wrap"> <table> <thead> <tr> <th class="kar" rowspan="2">Karigar</th> <?php foreach($qual_list as $qid): ?> <th><?= h($qual_name[$qid] ?? ('Q'.$qid)) ?></th> <?php endforeach; ?> <th rowspan="2">Total Mtr</th> <th rowspan="2">Gross</th> <th rowspan="2">Extra</th> <th rowspan="2">Deduction</th> <th rowspan="2">Advance</th> <th rowspan="2">Net</th> </tr> <tr> <?php foreach($qual_list as $qid): ?> <th>@ <?= number_format($qual_rate[$qid] ?? 0, 2) ?></th> <?php endforeach; ?> </tr> </thead> <tbody> <?php foreach($kar_list as $kid): $kname = $kar_name_map[$kid] ?? ('Karigar '.$kid); $row_total_mtr=0.0; $row_gross=0.0; ?> <tr> <td class="kar"><?= h($kname) ?></td> <?php foreach($qual_list as $qid): $mtr = (float)($meters[$kid][$qid] ?? 0.0); $rt = (float)($qual_rate[$qid] ?? 0.0); $amt = $mtr * $rt; $row_total_mtr += $mtr; $row_gross += $amt; $colMetersTotals[$qid] += $mtr; $colAmountTotals[$qid] += $amt; ?> <td title="<?= 'Rate @ '.number_format($rt,2).' × '.number_format($mtr,2).' = '.number_format($amt,2) ?>"> <?= $mtr>0?number_format($mtr,2):'' ?> </td> <?php endforeach; ?> <?php $extra = (float)($extra_map[$kid] ?? 0.0); $ded = (float)($deduction_map[$kid] ?? 0.0); $adv = (float)($advance_map[$kid] ?? 0.0); $net = $row_gross - $adv - $ded + $extra; $tot_total_mtr += $row_total_mtr; $tot_gross += $row_gross; $tot_extra += $extra; $tot_deduct += $ded; $tot_adv += $adv; $tot_net += $net; ?> <td class="total"><?= $row_total_mtr>0?number_format($row_total_mtr,2):'' ?></td> <td class="total"><?= $row_gross>0?number_format($row_gross,2):'' ?></td> <td><?= $extra>0?number_format($extra,2):'' ?></td> <td><?= $ded>0?number_format($ded,2):'' ?></td> <td><?= $adv>0?number_format($adv,2):'' ?></td> <td class="total"><?= number_format($net,2) ?></td> </tr> <?php endforeach; ?> </tbody> <tfoot> <tr class="total"> <td class="kar">TOTAL Mtr</td> <?php foreach($qual_list as $qid): ?><td><?= $colMetersTotals[$qid]>0?number_format($colMetersTotals[$qid],2):'' ?></td><?php endforeach; ?> <td><?= number_format($tot_total_mtr,2) ?></td> <td><?= number_format($tot_gross,2) ?></td> <td><?= number_format($tot_extra,2) ?></td> <td><?= number_format($tot_deduct,2) ?></td> <td><?= number_format($tot_adv,2) ?></td> <td><?= number_format($tot_net,2) ?></td> </tr> <tr class="total"> <td class="kar">AMOUNT (= Mtr×Rate)</td> <?php foreach($qual_list as $qid): ?><td><?= $colAmountTotals[$qid]>0?number_format($colAmountTotals[$qid],2):'' ?></td><?php endforeach; ?> <td></td><td></td><td></td><td></td><td></td><td></td> </tr> </tfoot> </table> </div> </div> <script> (function(){ const form=document.getElementById('filtersForm'); const btn=document.getElementById('showBtn'); if(!form||!btn) return; form.addEventListener('submit', function(){ btn.classList.add('loading'); const els=form.querySelectorAll('select,button'); els.forEach(el=>{ if(el!==btn) el.disabled=true; }); }); })(); </script> </body> </html>