View Code - 20260723

my_salary.php | 20260723_000646.php Initial Domain Snapshot

<?php
/* ============================================================================
   File: /karigar/my_salary.php
   Personal salary view for logged-in karigar (EXACT meters; optional rounding)
   - Robust line-date based meter aggregation (MySQL 8 / MariaDB / PHP fallback)
   - Shows Extra / Deduction / Advance DETAILS + totals
   - Adds "Full month" period option
   - Page-local only; no global/base changes
   ============================================================================ */
error_reporting(E_ALL); ini_set('display_errors',1);

require __DIR__ . '/_auth.php';
karigar_require_login();
$ku = karigar_user();

require __DIR__ . '/../erp/core/db.php';
$pdo = $GLOBALS['pdo'] ?? $pdo;

/* ---------- Helpers ---------- */
function h($s){ return htmlspecialchars((string)$s, ENT_QUOTES, 'UTF-8'); }
function gv($k,$d=null){ return isset($_GET[$k])?trim((string)$_GET[$k]):$d; }

// NOTE: half '1' => 1-15, '2' => 16-end, 'F' (or anything else) => full month
function yrmo_half_range(int $y,int $m,string $half): array {
  $start = sprintf('%04d-%02d-01',$y,$m);
  $end   = date('Y-m-t', strtotime($start));
  if($half==='2'){ $start = sprintf('%04d-%02d-16',$y,$m); }
  if($half==='1'){ $end   = sprintf('%04d-%02d-15',$y,$m); }
  return [$start,$end];
}
function safe_json($s){ $a=json_decode($s,true); return is_array($a)?$a:[]; }
function _is_maria(PDO $pdo): bool {
  try{ $v=(string)$pdo->query("SELECT @@version")->fetchColumn(); }catch(Throwable $e){ return false; }
  return stripos($v,'mariadb')!==false;
}
function _norm_date($s){ // '04-08-2025' / '04/08/2025' / '2025-08-04' → '2025-08-04'
  $s=trim((string)$s); if($s==='') return null;
  if (preg_match('/^\d{4}-\d{2}-\d{2}$/',$s)) return $s;
  if (preg_match('/^(\d{1,2})[\/\-](\d{1,2})[\/\-](\d{4})$/',$s,$m)) return sprintf('%04d-%02d-%02d',$m[3],$m[2],$m[1]);
  $ts=strtotime($s); return $ts?date('Y-m-d',$ts):null;
}
function _table_exists(PDO $pdo,$t){ try{$pdo->query("SELECT 1 FROM `$t` LIMIT 1");return true;}catch(Exception $e){return false;} }

/* ---------- Inputs ---------- */
$company_id = (int)$ku['company_id'];
$karigar_id = (int)$ku['karigar_id'];

$year  = (int)gv('year', date('Y'));
$month = (int)gv('month', date('n'));
$half  = gv('half','1');                 // keep default 1–15; now supports 'F' too
[$date_from,$date_to] = yrmo_half_range($year,$month,$half);

$ROUND_TO = max(1,(int)gv('round',1));   // 1 / 5 / 10

/* ---------- Masters: Quality rates/names ---------- */
$QR=[]; $QN=[];
$q=$pdo->prepare("SELECT quality_id, quality_name, rate FROM quality_rates WHERE company_id=?");
$q->execute([$company_id]);
foreach($q as $r){
  $QR[(int)$r['quality_id']] = (float)$r['rate'];
  $QN[(int)$r['quality_id']] = (string)$r['quality_name'];
}

/* ============================================================================
   METERS BY QUALITY (line-date aware, robust)
   ============================================================================ */
$MTR_BY_Q=[]; $TOTAL_MTR=0.0;
$_skipped_bad_date = 0; $_skipped_out_range = 0;
$from_ts = strtotime($date_from.' 00:00:00');
$to_ts   = strtotime($date_to.' 23:59:59');

$got=false;

/* A) MySQL 8 — JSON_TABLE (fast) */
if (!_is_maria($pdo)) {
  try{
    $sql = "SELECT le.quality_id AS qid, SUM(jt.meter) AS mtr
            FROM loom_production_entry le
            JOIN JSON_TABLE(le.lines_json, '$[*]'
              COLUMNS(
                karigar_id INT PATH '$.karigar_id',
                meter      DECIMAL(10,2) PATH '$.meter',
                dte        DATE PATH '$.date'
              )
            ) jt
            WHERE le.company_id=? 
              AND jt.karigar_id=? 
              AND jt.dte BETWEEN ? AND ?
            GROUP BY le.quality_id";
    $st=$pdo->prepare($sql);
    $st->execute([$company_id,$karigar_id,$date_from,$date_to]);
    $cnt=0;
    foreach($st as $r){
      $qid=(int)$r['qid']; $m=(float)$r['mtr'];
      if($qid<=0 || $m<=0) continue;
      $MTR_BY_Q[$qid]=($MTR_BY_Q[$qid]??0)+$m;
      $TOTAL_MTR += $m;
      $cnt++;
    }
    if($cnt>0) $got=true;
  }catch(Throwable $e){ /* fall through */ }
}

/* B) MariaDB — JSON_EXTRACT + index generator (CTE / no-CTE) */
if(!$got && _is_maria($pdo)){
  $cte = "
    WITH RECURSIVE seq(n) AS (SELECT 0 UNION ALL SELECT n+1 FROM seq WHERE n<999)
    SELECT le.quality_id AS qid,
           SUM(CAST(JSON_UNQUOTE(JSON_EXTRACT(le.lines_json, CONCAT('[$',seq.n,'].meter'))) AS DECIMAL(10,2))) AS mtr
    FROM loom_production_entry le
    JOIN seq ON seq.n < JSON_LENGTH(le.lines_json)
    WHERE le.company_id = :cid
      AND CAST(JSON_UNQUOTE(JSON_EXTRACT(le.lines_json, CONCAT('[$',seq.n,'].karigar_id'))) AS UNSIGNED) = :kid
      AND DATE(JSON_UNQUOTE(JSON_EXTRACT(le.lines_json, CONCAT('[$',seq.n,'].date')))) BETWEEN :d1 AND :d2
    GROUP BY le.quality_id";
  try{
    $st=$pdo->prepare($cte); $st->execute([':cid'=>$company_id,':kid'=>$karigar_id,':d1'=>$date_from,':d2'=>$date_to]);
    $cnt=0;
    foreach($st as $r){
      $qid=(int)$r['qid']; $m=(float)$r['mtr'];
      if($qid<=0 || $m<=0) continue;
      $MTR_BY_Q[$qid]=($MTR_BY_Q[$qid]??0)+$m;
      $TOTAL_MTR += $m;
      $cnt++;
    }
    if($cnt>0) $got=true;
  }catch(Throwable $e){
    try{
      $nocte = "
        SELECT le.quality_id AS qid,
               SUM(CAST(JSON_UNQUOTE(JSON_EXTRACT(le.lines_json, CONCAT('[$',n.n,'].meter'))) AS DECIMAL(10,2))) AS mtr
        FROM loom_production_entry le
        JOIN (
          SELECT a.i + 10*b.i + 100*c.i AS n
          FROM (SELECT 0 i UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
                UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a
          CROSS JOIN (SELECT 0 i UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
                      UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b
          CROSS JOIN (SELECT 0 i UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
                      UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) c
        ) n ON n.n < JSON_LENGTH(le.lines_json)
        WHERE le.company_id = :cid
          AND CAST(JSON_UNQUOTE(JSON_EXTRACT(le.lines_json, CONCAT('[$',n.n,'].karigar_id'))) AS UNSIGNED) = :kid
          AND DATE(JSON_UNQUOTE(JSON_EXTRACT(le.lines_json, CONCAT('[$',n.n,'].date')))) BETWEEN :d1 AND :d2
        GROUP BY le.quality_id";
      $st=$pdo->prepare($nocte); $st->execute([':cid'=>$company_id,':kid'=>$karigar_id,':d1'=>$date_from,':d2'=>$date_to]);
      $cnt=0;
      foreach($st as $r){
        $qid=(int)$r['qid']; $m=(float)$r['mtr'];
        if($qid<=0 || $m<=0) continue;
        $MTR_BY_Q[$qid]=($MTR_BY_Q[$qid]??0)+$m;
        $TOTAL_MTR += $m;
        $cnt++;
      }
      if($cnt>0) $got=true;
    }catch(Throwable $e2){ /* fall to PHP */ }
  }
}

/* C) PHP fallback — header-window (-31 days) + normalize */
if(!$got){
  $hdr_from = date('Y-m-d', strtotime($date_from.' -31 days'));
  $hdr_to   = $date_to;
  $rows=$pdo->prepare("SELECT entry_date, quality_id, lines_json
                         FROM loom_production_entry
                        WHERE company_id=? AND entry_date BETWEEN ? AND ?
                     ORDER BY entry_date,id");
  $rows->execute([$company_id,$hdr_from,$hdr_to]);
  foreach($rows as $r){
    $qid=(int)$r['quality_id']; if($qid<=0) continue;
    $row_entry = substr((string)$r['entry_date'],0,10);
    if(!preg_match('/^\d{4}-\d{2}-\d{2}$/',$row_entry)) $row_entry = date('Y-m-d', strtotime($r['entry_date']));
    foreach(safe_json($r['lines_json']) as $ln){
      if((int)($ln['karigar_id']??0)!==$karigar_id) continue;
      $m=(float)($ln['meter']??0); if($m<=0) continue;
      $d = _norm_date($ln['date'] ?? '') ?: $row_entry;
      $ts = strtotime($d);
      if(!$ts){ $_skipped_bad_date++; $ts=strtotime($row_entry); }
      if($ts < $from_ts || $ts > $to_ts){ $_skipped_out_range++; continue; }
      $MTR_BY_Q[$qid] = ($MTR_BY_Q[$qid]??0) + $m;
      $TOTAL_MTR += $m;
    }
  }
}

/* ---------- Money: Extra / Deduction / Advance (totals + lists) ---------- */
function sum_tbl($pdo,$company_id,$kid,$from,$to,$tbl,$date_cols=['date','entry_date','created_at'],$amt_cols=['amount','amt','value']){
  foreach($date_cols as $dc){ foreach($amt_cols as $ac){
    try{
      $st=$pdo->prepare("SELECT SUM($ac) s FROM $tbl WHERE company_id=? AND karigar_id=? AND $dc BETWEEN ? AND ?");
      $st->execute([$company_id,$kid,$from,$to]); $v=$st->fetchColumn();
      if($v!==false && $v!==null) return (float)$v;
    }catch(Exception $e){}
  }} return 0.0;
}
function list_tbl_rows($pdo,$company_id,$kid,$from,$to,$tbl){
  $rows=[];
  if(!_table_exists($pdo,$tbl)) return $rows;
  // prefer (date, amount, remark)
  $sqls=[
    "SELECT date AS d, amount AS a, remark AS r FROM $tbl WHERE company_id=? AND karigar_id=? AND date BETWEEN ? AND ? ORDER BY date DESC, id DESC",
    "SELECT entry_date AS d, amount AS a, remark AS r FROM $tbl WHERE company_id=? AND karigar_id=? AND entry_date BETWEEN ? AND ? ORDER BY entry_date DESC, id DESC",
    "SELECT DATE(created_at) AS d, amount AS a, remark AS r FROM $tbl WHERE company_id=? AND karigar_id=? AND created_at BETWEEN ? AND ? ORDER BY created_at DESC, id DESC",
  ];
  foreach($sqls as $sql){
    try{
      $st=$pdo->prepare($sql); $st->execute([$company_id,$kid,$from.' 00:00:00',$to.' 23:59:59']);
      $tmp=$st->fetchAll(PDO::FETCH_ASSOC);
      if($tmp){ foreach($tmp as $r){ $rows[]=['date'=>substr((string)$r['d'],0,10),'amount'=>(float)$r['a'],'remark'=>(string)($r['r']??'')]; }
        break;
      }
    }catch(Exception $e){}
  }
  return $rows;
}

$extra   = sum_tbl($pdo,$company_id,$karigar_id,$date_from,$date_to,'extra_data');
$ded     = sum_tbl($pdo,$company_id,$karigar_id,$date_from,$date_to,'deduction_data');
$adv     = sum_tbl($pdo,$company_id,$karigar_id,$date_from,$date_to,'advance_data');

$extra_rows = list_tbl_rows($pdo,$company_id,$karigar_id,$date_from,$date_to,'extra_data');
$ded_rows   = list_tbl_rows($pdo,$company_id,$karigar_id,$date_from,$date_to,'deduction_data');
$adv_rows   = list_tbl_rows($pdo,$company_id,$karigar_id,$date_from,$date_to,'advance_data');

/* ---------- Amount calc (exact net; optional rounding for payable) ---------- */
$gross=0.0; foreach($MTR_BY_Q as $qid=>$m){ $gross += $m * ($QR[$qid]??0); }
$net_raw = $gross + $extra - $ded - $adv;
$payable = round($net_raw/$ROUND_TO)*$ROUND_TO;

/* ---------- Recent 10 production lines (by line-date) ---------- */
$hist=[];
$hdr_from = date('Y-m-d', strtotime($date_from.' -31 days'));
$hdr_to   = $date_to;
$rows=$pdo->prepare("SELECT entry_date, quality_id, lines_json
                      FROM loom_production_entry
                     WHERE company_id=? AND entry_date BETWEEN ? AND ?
                  ORDER BY entry_date DESC, id DESC");
$rows->execute([$company_id,$hdr_from,$hdr_to]);
foreach($rows as $r){
  $qid=(int)$r['quality_id']; $row_entry = substr((string)$r['entry_date'],0,10);
  if(!preg_match('/^\d{4}-\d{2}-\d{2}$/',$row_entry)) $row_entry = date('Y-m-d', strtotime($r['entry_date']));
  foreach(safe_json($r['lines_json']) as $ln){
    if((int)($ln['karigar_id']??0)!==$karigar_id) continue;
    $m=(float)($ln['meter']??0); if($m<=0) continue;
    $d=_norm_date($ln['date']??'') ?: $row_entry;
    $ts=strtotime($d); if(!$ts) $ts=strtotime($row_entry);
    if($ts<$from_ts || $ts>$to_ts) continue;
    $hist[]=['date'=>date('Y-m-d',$ts),'quality'=>$QN[$qid]??("Q".$qid),'meter'=>$m];
  }
}
usort($hist, fn($a,$b)=>strcmp($b['date'],$a['date']));
$hist = array_slice($hist,0,10);

/* ---------- UI ---------- */
?>
<!doctype html>
<html>
<head>
<meta charset="utf-8">
<title>My Salary</title>
<meta name="viewport" content="width=device-width, initial-scale=1">
<style>
:root{ --green:#34A853; --mint:#F5FFF7; }
body{margin:0;background:var(--mint);font-family:system-ui,-apple-system,Segoe UI,Roboto,Ubuntu,Arial;color:#222}
.wrap{max-width:900px;margin:20px auto;padding:0 12px}
h2{color:#2c7d3f;margin:6px 0 12px}
.card{background:#fff;border:1px solid #e6f1e6;border-radius:12px;padding:12px;margin-bottom:14px}
label{font-size:12px;color:#333;display:block;margin-bottom:4px}
select{padding:8px;border:1px solid #cfe8d0;border-radius:10px}
.table{width:100%;border-collapse:collapse}
.table th,.table td{border-bottom:1px solid #eef5ef;padding:8px}
.table th{background:#d5efe0;text-align:left}
.small{font-size:12px;color:#555}
.badge{background:#d8f7e1;padding:6px 10px;border-radius:999px;color:#2b6b3c;display:inline-block;border:none;cursor:pointer}
.right{text-align:right}
.mono{font-variant-numeric: tabular-nums}
.section-title{font-weight:700;margin-bottom:6px;color:#175a35}
.empty{color:#777;font-size:12px}
.pill{background:#eaf7ee;border:1px solid #d5edd9;border-radius:999px;padding:4px 8px;font-size:12px;display:inline-block;color:#13572c}
.flex{display:flex;gap:10px;align-items:flex-end;flex-wrap:wrap}
</style>
</head>
<body>
<div class="wrap">
  <h2>My Salary</h2>

<form method="get" class="card flex">
    <div>
      <label>Month</label>
      <select name="month"><?php for($m=1;$m<=12;$m++): ?>
        <option value="<?= $m ?>" <?= $m===$month?'selected':'' ?>><?= date('M', mktime(0,0,0,$m,1,$year)) ?></option>
      <?php endfor; ?></select>
    </div>
    <div>
      <label>Period</label>
      <select name="half">
        <option value="1" <?= $half==='1'?'selected':'' ?>>1–15</option>
        <option value="2" <?= $half==='2'?'selected':'' ?>>16–End</option>
        <option value="F" <?= $half==='F'?'selected':'' ?>>Full month</option>
      </select>
    </div>
    <div>
      <label>Year</label>
      <select name="year"><?php for($y=date('Y')-1;$y<=date('Y')+1;$y++): ?>
        <option value="<?= $y ?>" <?= $y===$year?'selected':'' ?>><?= $y ?></option>
      <?php endfor; ?></select>
    </div>
    <div>
      <label>Round to</label>
      <select name="round">
        <?php foreach([1,5,10] as $r): ?>
          <option value="<?= $r ?>" <?= $ROUND_TO===$r?'selected':'' ?>>₹<?= $r ?></option>
        <?php endforeach; ?>
      </select>
    </div>
    <button class="badge" type="submit">Show</button>
    <span class="pill">Range: <?= h($date_from) ?> → <?= h($date_to) ?></span>
  </form>

<div class="card">
    <div class="section-title">Summary</div>
    <table class="table">
      <tr><th>Quality</th><th class="right">Meters</th><th class="right">Rate</th><th class="right">Amount</th></tr>
      <?php $gross_check=0.0; foreach($MTR_BY_Q as $qid=>$m): $rate=$QR[$qid]??0; $amt=$m*$rate; $gross_check+=$amt; ?>
        <tr>
          <td><?= h($QN[$qid]??("Q".$qid)) ?></td>
          <td class="right mono"><?= number_format($m,2) ?></td>
          <td class="right mono"><?= number_format($rate,2) ?></td>
          <td class="right mono"><?= number_format($amt,2) ?></td>
        </tr>
      <?php endforeach; ?>
      <tr><td colspan="4" class="small">Total Meters: <b><?= number_format($TOTAL_MTR,2) ?></b></td></tr>
      <tr><th colspan="3">Gross</th><th class="right mono"><?= number_format($gross,2) ?></th></tr>
      <tr><td colspan="3">Extra</td><td class="right mono"><?= number_format($extra,2) ?></td></tr>
      <tr><td colspan="3">Deduction</td><td class="right mono"><?= number_format($ded,2) ?></td></tr>
      <tr><td colspan="3">Advance</td><td class="right mono"><?= number_format($adv,2) ?></td></tr>
      <tr><th colspan="3">Net</th><th class="right mono"><?= number_format($net_raw,2) ?></th></tr>
      <tr><td colspan="3">Payable (rounded to ₹<?= (int)$ROUND_TO ?>)</td><td class="right mono"><?= number_format($payable,2) ?></td></tr>
    </table>
  </div>

<div class="card">
    <div class="section-title">Extra</div>
    <?php if($extra_rows): ?>
      <table class="table">
        <tr><th>Date</th><th>Remark</th><th class="right">Amount</th></tr>
        <?php foreach($extra_rows as $r): ?>
          <tr><td><?= h($r['date']) ?></td><td><?= h($r['remark']) ?></td><td class="right mono"><?= number_format($r['amount'],2) ?></td></tr>
        <?php endforeach; ?>
        <tr><th colspan="2">Total</th><th class="right mono"><?= number_format($extra,2) ?></th></tr>
      </table>
    <?php else: ?>
      <div class="empty">No extra entries in this period.</div>
    <?php endif; ?>
  </div>

<div class="card">
    <div class="section-title">Deduction</div>
    <?php if($ded_rows): ?>
      <table class="table">
        <tr><th>Date</th><th>Remark</th><th class="right">Amount</th></tr>
        <?php foreach($ded_rows as $r): ?>
          <tr><td><?= h($r['date']) ?></td><td><?= h($r['remark']) ?></td><td class="right mono"><?= number_format($r['amount'],2) ?></td></tr>
        <?php endforeach; ?>
        <tr><th colspan="2">Total</th><th class="right mono"><?= number_format($ded,2) ?></th></tr>
      </table>
    <?php else: ?>
      <div class="empty">No deduction entries in this period.</div>
    <?php endif; ?>
  </div>

<div class="card">
    <div class="section-title">Advance</div>
    <?php if($adv_rows): ?>
      <table class="table">
        <tr><th>Date</th><th>Remark</th><th class="right">Amount</th></tr>
        <?php foreach($adv_rows as $r): ?>
          <tr><td><?= h($r['date']) ?></td><td><?= h($r['remark']) ?></td><td class="right mono"><?= number_format($r['amount'],2) ?></td></tr>
        <?php endforeach; ?>
        <tr><th colspan="2">Total</th><th class="right mono"><?= number_format($adv,2) ?></th></tr>
      </table>
    <?php else: ?>
      <div class="empty">No advance entries in this period.</div>
    <?php endif; ?>
  </div>

<?php if($hist): ?>
  <div class="card">
    <div class="section-title">Recent production lines (last 10 in period)</div>
    <table class="table">
      <tr><th>Date</th><th>Quality</th><th class="right">Meter</th></tr>
      <?php foreach($hist as $h): ?>
        <tr><td><?= h($h['date']) ?></td><td><?= h($h['quality']) ?></td><td class="right mono"><?= number_format($h['meter'],2) ?></td></tr>
      <?php endforeach; ?>
    </table>
  </div>
  <?php endif; ?>

</div>
</body>
</html>