View Code - 20260920

loom_patia_karigar.php | 20260920_164915.php Initial Domain Snapshot

<?php
/* =============================================================================
   File: /erp/loom_patia_karigar.php
   View: Karigar ⇒ Machine matrix (Date rows × Machine cols)
   Access: Owner/Admin; others via page_access (page_key = 'loom_patia_karigar')
   Scope: Page-local only. No global/base changes.
   ============================================================================= */

header('X-Frame-Options: SAMEORIGIN');
error_reporting(E_ALL); ini_set('display_errors', 1);

/* ---------- [0] ERP Auth ---------- */
$ERP_ROOT = is_dir(__DIR__.'/modules') ? __DIR__
           : (is_dir(__DIR__.'/../modules') ? dirname(__DIR__)
           : (is_dir(__DIR__.'/../../modules') ? dirname(dirname(__DIR__)) : __DIR__));
require $ERP_ROOT . '/modules/auth/auth.php';
require_login();
$u          = auth_user();
$company_id = (int)$u['company_id'];
$user_id    = (int)$u['id'];
$role       = strtolower((string)($u['role'] ?? ''));

/* ---------- [0A] DB ---------- */
require $ERP_ROOT . '/core/db.php';
$pdo = $GLOBALS['pdo'] ?? $pdo;

/* ---------- [0B] Access Control ---------- */
function _tbl_exists(PDO $pdo, $t){ try{$pdo->query("SELECT 1 FROM `$t` LIMIT 1"); return true;} catch(Exception $e){ return false; } }

$PAGE_KEY = 'loom_patia_karigar';
$allowed_roles = ['owner','admin'];
$has_access = in_array($role, $allowed_roles, true);
if (!$has_access && _tbl_exists($pdo,'page_access')) {
  $q = $pdo->prepare("SELECT 1 FROM page_access
                       WHERE company_id=? AND page_key=? AND user_id=? AND (granted=1 OR granted IS NULL)
                       LIMIT 1");
  $q->execute([$company_id, $PAGE_KEY, $user_id]);
  $has_access = (bool)$q->fetchColumn();
}
if (!$has_access) { http_response_code(403);
  echo "<h1>403</h1><p>Access denied for <code>loom_patia_karigar</code>.</p>"; exit; }

/* ---------- [1] 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; }
function n0($v){ return number_format((float)$v, 0, '.', ''); }
function half_range($y,$m,$half){
  $start = sprintf('%04d-%02d-01',$y,$m);
  $end   = date('Y-m-t', strtotime($start));
  if ((int)$half === 2) $start = sprintf('%04d-%02d-16',$y,$m);
  else $end = sprintf('%04d-%02d-15',$y,$m);
  return [$start,$end];
}
function dates_between($a,$b){
  $out=[]; $d=new DateTime($a); $e=new DateTime($b);
  for(; $d <= $e; $d->modify('+1 day')) $out[]=$d->format('Y-m-d');
  return $out;
}
function _is_mariadb(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){ // '2025-08-04' or '04-08-2025' → '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;
}

/* ---------- [2] Filters ---------- */
$month_str = gv('month', date('Y-m'));
$half      = (int)gv('half', 1);
[$Y,$M]    = array_map('intval', explode('-', $month_str));
[$date_from,$date_to] = half_range($Y,$M,$half);
$khata_id   = (int)gv('khata_id', 0);
$karigar_id = (int)gv('karigar_id', 0);
$do_csv     = (int)gv('csv',0) === 1;

/* ---------- [3] Khatas ---------- */
$KHATAS = []; $KHATA_MAP = [];
if (_tbl_exists($pdo,'khatas')){
  $st=$pdo->prepare("SELECT id, code, name FROM khatas
                      WHERE company_id=? AND (is_active=1 OR is_active IS NULL)
                      ORDER BY id");
  $st->execute([$company_id]);
  foreach($st->fetchAll(PDO::FETCH_ASSOC) as $r){
    $KHATAS[] = $r;
    $KHATA_MAP[(int)$r['id']] = $r;
  }
}
$KHATA_LABEL = ($khata_id && isset($KHATA_MAP[$khata_id]))
  ? ($KHATA_MAP[$khata_id]['code'].' — '.$KHATA_MAP[$khata_id]['name'])
  : ($khata_id ? ('Khata '.$khata_id) : 'All Khata');

/* ---------- [4] Karigar dropdown ---------- */
$KLIST=[];
if (_tbl_exists($pdo,'loom_karigar_master')){
  $sql="SELECT id, karigar_name, khata_id FROM loom_karigar_master WHERE company_id=?";
  $args=[$company_id];
  if ($khata_id>0){ $sql.=" AND khata_id=?"; $args[]=$khata_id; }
  $sql.=" ORDER BY karigar_name";
  $q=$pdo->prepare($sql); $q->execute($args);
  foreach($q->fetchAll(PDO::FETCH_ASSOC) as $r){
    $nm = trim((string)$r['karigar_name']); if($nm==='') $nm = 'Karigar#'.$r['id'];
    $KLIST[] = ['id'=>(int)$r['id'],'name'=>$nm];
  }
}

/* ---------- [5] FETCH LINES BY LINE-DATE (CORE FIX + ROBUST FALLBACK) ---- */
$LINES = [];
if ($karigar_id>0) {

$got = false;

// Path A: MySQL 8 (JSON_TABLE)
  if (!_is_mariadb($pdo)) {
    try {
      $sql = "SELECT le.machine_id, le.quality_id, jt.dte, jt.meter
              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 = :cid
                ".($khata_id>0 ? "AND le.khata_id = :khata" : "")."
                AND jt.karigar_id = :kid
                AND jt.dte BETWEEN :d1 AND :d2
              ORDER BY jt.dte, le.machine_id, le.id";
      $p = [':cid'=>$company_id, ':kid'=>$karigar_id, ':d1'=>$date_from, ':d2'=>$date_to];
      if ($khata_id>0) $p[':khata']=$khata_id;
      $st = $pdo->prepare($sql); $st->execute($p);
      $LINES = $st->fetchAll(PDO::FETCH_ASSOC);
      if (!empty($LINES)) $got = true;
    } catch(Throwable $e) { /* fall through */ }
  }

// Path B: MariaDB (WITH RECURSIVE). If fails, try cross-join generator.
  if (!$got && _is_mariadb($pdo)) {
    $p = [':cid'=>$company_id, ':kid'=>$karigar_id, ':d1'=>$date_from, ':d2'=>$date_to];
    if ($khata_id>0) $p[':khata']=$khata_id;
    try {
      $sql_cte = "
        WITH RECURSIVE seq(n) AS (SELECT 0 UNION ALL SELECT n+1 FROM seq WHERE n < 999)
        SELECT le.machine_id, le.quality_id,
               DATE(JSON_UNQUOTE(JSON_EXTRACT(le.lines_json, CONCAT('[$',seq.n,'].date')))) AS dte,
               CAST(JSON_UNQUOTE(JSON_EXTRACT(le.lines_json, CONCAT('[$',seq.n,'].meter'))) AS DECIMAL(10,2)) AS meter
        FROM loom_production_entry le
        JOIN seq ON seq.n < JSON_LENGTH(le.lines_json)
        WHERE le.company_id = :cid
          ".($khata_id>0 ? "AND le.khata_id = :khata" : "")."
          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
        ORDER BY dte, le.machine_id";
      $st = $pdo->prepare($sql_cte); $st->execute($p);
      $LINES = $st->fetchAll(PDO::FETCH_ASSOC);
      if (!empty($LINES)) $got = true;
    } catch(Throwable $e) {
      try {
        $sql_nocte = "
          SELECT le.machine_id, le.quality_id,
                 DATE(JSON_UNQUOTE(JSON_EXTRACT(le.lines_json, CONCAT('[$',n.n,'].date')))) AS dte,
                 CAST(JSON_UNQUOTE(JSON_EXTRACT(le.lines_json, CONCAT('[$',n.n,'].meter'))) AS DECIMAL(10,2)) AS meter
          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
            ".($khata_id>0 ? "AND le.khata_id = :khata" : "")."
            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
          ORDER BY dte, le.machine_id";
        $st = $pdo->prepare($sql_nocte); $st->execute($p);
        $LINES = $st->fetchAll(PDO::FETCH_ASSOC);
        if (!empty($LINES)) $got = true;
      } catch(Throwable $e2) { /* continue to PHP fallback */ }
    }
  }

// Path C: PHP-only fallback (always works) — decode JSON in PHP and filter by line-date
  if (!$got) {
    $hdr_from = date('Y-m-d', strtotime($date_from.' -31 days')); // ताकि 31-Jul जैसे headers भी आ जाएँ
    $hdr_to   = $date_to;
    $args = [$company_id, $hdr_from, $hdr_to];
    $wh = "company_id=? AND entry_date BETWEEN ? AND ?";
    if ($khata_id>0){ $wh.=" AND khata_id=?"; $args[]=$khata_id; }
    $st = $pdo->prepare("SELECT entry_date, machine_id, quality_id, lines_json
                         FROM loom_production_entry
                         WHERE $wh
                         ORDER BY entry_date, id");
    $st->execute($args);
    foreach($st as $r){
      $arr = json_decode((string)$r['lines_json'], true);
      if(!$arr) continue;
      if (isset($arr['karigar_id']) || isset($arr['meter'])) $arr = [$arr];
      foreach($arr 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']??''); if(!$d) continue;
        if($d<$date_from || $d>$date_to) continue;
        $LINES[] = ['machine_id'=>(int)$r['machine_id'],
                    'quality_id'=>(int)($r['quality_id']??0),
                    'dte'=>$d,'meter'=>$m];
      }
    }
  }
}

/* ---------- [6] Build matrix ---------- */
$DATES = dates_between($date_from,$date_to);
$GRID=[]; $ROW_T=[]; $COL_T=[]; $MACHINES_SET=[]; $GRAND=0.0;
foreach($DATES as $d){ $GRID[$d]=[]; $ROW_T[$d]=0.0; }
$MTR_BY_Q=[]; $TOTAL_MTR=0.0;

if ($karigar_id>0){
  foreach($LINES as $r){
    $d  = substr((string)$r['dte'],0,10);
    if($d<$date_from || $d>$date_to) continue;
    $m  = (float)($r['meter'] ?? 0); if($m<=0) continue;
    $mc = (int)$r['machine_id'];
    if(!isset($GRID[$d][$mc])) $GRID[$d][$mc]=0.0;
    $GRID[$d][$mc] += $m;
    $ROW_T[$d]     += $m;
    $COL_T[$mc]    = ($COL_T[$mc]??0) + $m;
    $MACHINES_SET[$mc] = true;
    $GRAND += $m;

$qid = (int)($r['quality_id'] ?? 0);
    if ($qid>0) { $MTR_BY_Q[$qid] = ($MTR_BY_Q[$qid]??0) + $m; }
    $TOTAL_MTR += $m;
  }
}
$MACHINES = array_keys($MACHINES_SET); sort($MACHINES);

/* ---- Salary calc ---- */
$QR=[]; $QN=[];
try{
  $qq=$pdo->prepare("SELECT quality_id, quality_name, rate FROM quality_rates WHERE company_id=?");
  $qq->execute([$company_id]);
  foreach($qq->fetchAll(PDO::FETCH_ASSOC) as $r){
    $QR[(int)$r['quality_id']] = (float)$r['rate'];
    $QN[(int)$r['quality_id']] = (string)$r['quality_name'];
  }
}catch(Exception $e){}
$gross = 0.0; foreach($MTR_BY_Q as $qid=>$m){ $gross += $m * ($QR[$qid] ?? 0); }
if (!function_exists('__patia_sum_tbl')){
  function __patia_sum_tbl($pdo,$company_id,$kid,$from,$to,$tbl,$date_cols=['date','entry_date','created_at'],$amt_cols=['amount','amt','value']){
    try{
      foreach($date_cols as $dc){ foreach($amt_cols as $ac){
        $sql="SELECT SUM($ac) s FROM $tbl WHERE company_id=? AND karigar_id=? AND $dc BETWEEN ? AND ?";
        $st=$pdo->prepare($sql); $st->execute([$company_id,$kid,$from,$to]); $v=$st->fetchColumn();
        if($v!==false && $v!==null) return (float)$v;
      }}
    }catch(Exception $e){ }
    return 0.0;
  }
}
$extra = $karigar_id>0 ? __patia_sum_tbl($pdo,$company_id,$karigar_id,$date_from,$date_to,'extra_data') : 0.0;
$ded   = $karigar_id>0 ? __patia_sum_tbl($pdo,$company_id,$karigar_id,$date_from,$date_to,'deduction_data') : 0.0;
$adv   = $karigar_id>0 ? __patia_sum_tbl($pdo,$company_id,$karigar_id,$date_from,$date_to,'advance_data') : 0.0;
$net   = $gross + $extra - $ded - $adv;

/* ---------- [7] CSV ---------- */
if ($do_csv){
  header('Content-Type: text/csv; charset=utf-8');
  header('Content-Disposition: attachment; filename=patia_by_karigar_'.$karigar_id.'_'.$month_str.'_H'.$half.'.csv');
  $out=fopen('php://output','w');
  $head=array_merge(['Mc no.'], $MACHINES, ['Day Total']); fputcsv($out,$head);
  foreach($DATES as $d){
    $row=[date('n/j/Y', strtotime($d))];
    foreach($MACHINES as $m){ $row[] = isset($GRID[$d][$m]) ? n0($GRID[$d][$m]) : ''; }
    $row[] = n0($ROW_T[$d]??0);
    fputcsv($out,$row);
  }
  $last=array_merge(['TOTAL'], array_map(fn($m)=>n0($COL_T[$m]??0), $MACHINES), [n0($GRAND)]);
  fputcsv($out,$last);
  fclose($out); exit;
}

/* ---------- [8] UI ---------- */
$MM_GREEN='#34A853'; $MM_BG='#F5FFF7'; $MM_STONE='#5F6368';
$period_lbl = sprintf('%s (Half %d)  [%s → %s]', $month_str, $half, $date_from, $date_to);
?>
<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<title>Patia — Karigar ⇒ Machine (ERP)</title>
<meta name="viewport" content="width=device-width, initial-scale=1">
<style>
  body{font-family:system-ui,-apple-system,Segoe UI,Roboto,Ubuntu,Arial;margin:0;background:<?=h($MM_BG)?>;color:#222}
  .wrap{max-width:1180px;margin:20px auto;padding:0 16px}
  .card{background:#fff;border-radius:14px;box-shadow:0 6px 18px rgba(0,0,0,.06);padding:14px 16px;margin-bottom:14px}
  .row{display:flex;gap:12px;flex-wrap:wrap;align-items:flex-end}
  .field{display:flex;flex-direction:column;min-width:160px}
  label{font-size:12px;color:<?=$MM_STONE?>;margin-bottom:4px}
  select,input[type="month"]{padding:8px 10px;border:1px solid #ddd;border-radius:10px}
  .btn{background:<?=$MM_GREEN?>;color:#fff;border:none;border-radius:10px;padding:10px 14px;cursor:pointer}
  .btn-outline{background:#fff;color:<?=$MM_GREEN?>;border:1px solid <?=$MM_GREEN?>}
  .pill{display:inline-block;padding:6px 10px;background:#eaf7ee;border-radius:999px;color:#0b6e2b;font-size:12px;margin-left:6px}
  table{width:100%;border-collapse:separate;border-spacing:0;margin-top:10px}
  th,td{border-bottom:1px solid #eee;padding:8px 10px;white-space:nowrap;text-align:right}
  th:first-child,td:first-child{text-align:left;position:sticky;left:0;background:#fff}
  thead th{position:sticky;top:0;background:#fafafa}
  tfoot td{font-weight:600;background:#fafafa}
  .muted{color:<?=$MM_STONE?>}
  @media print{ .no-print{display:none!important} body{background:#fff} .wrap{max-width:100%;margin:0;padding:0} .card{box-shadow:none;border-radius:0;margin:0;padding:0} thead{display:table-header-group} tfoot{display:table-footer-group} }
</style>
</head>
<body>
<div class="wrap">
  <div class="card no-print">
    <form class="row" method="get">
      <div class="field">
        <label>Khata</label>
        <select name="khata_id">
          <option value="0">All Khata</option>
          <?php foreach($KHATAS as $k): ?>
            <option value="<?=h($k['id'])?>" <?= $khata_id===(int)$k['id']?'selected':''?>><?=h($k['code'])?> — <?=h($k['name'])?></option>
          <?php endforeach; ?>
        </select>
      </div>

<div class="field">
        <label>Karigar</label>
        <select name="karigar_id" required>
          <option value="0">Select Karigar</option>
          <?php foreach($KLIST as $k): ?>
            <option value="<?=h($k['id'])?>" <?= $karigar_id===$k['id']?'selected':''?>><?=h($k['name'])?></option>
          <?php endforeach; ?>
        </select>
      </div>

<div class="field">
        <label>Month</label>
        <input type="month" name="month" value="<?=h($month_str)?>">
      </div>
      <div class="field">
        <label>Half</label>
        <select name="half">
          <option value="1" <?= $half===1?'selected':''?>>1–15</option>
          <option value="2" <?= $half===2?'selected':''?>>16–End</option>
        </select>
      </div>

<div class="field"><button class="btn" type="submit">Apply</button></div>
      <div class="field">
        <a class="btn btn-outline" href="?<?=http_build_query([
          'khata_id'=>$khata_id,'karigar_id'=>$karigar_id,'month'=>$month_str,'half'=>$half,'csv'=>1
        ])?>">Export CSV</a>
      </div>
      <div class="field"><button type="button" class="btn" onclick="window.print()">Print</button></div>
      <div class="field"><button type="button" class="btn btn-outline" onclick="window.print()">PDF</button></div>

<div style="margin-left:auto" class="muted">
        Period: <span class="pill"><?=h($period_lbl)?></span>
        <?php if($khata_id>0): ?><span class="pill"><?=h($KHATA_LABEL)?></span><?php endif; ?>
        <span class="pill">Filtering by <strong>Production Date</strong></span>
      </div>
    </form>
  </div>

<div class="card">
    <div style="display:flex;justify-content:space-between;align-items:center;margin-bottom:8px">
      <div><strong>Patia — Karigar ⇒ Machine</strong>
        <?php if($karigar_id>0): ?><span class="pill">KID <?=h($karigar_id)?></span><?php endif; ?>
      </div>
      <div class="muted no-print">Machines: <?=count($MACHINES)?> | Days: <?=count($DATES)?></div>
    </div>

<?php if ($karigar_id<=0): ?>
      <div class="muted">Please select a Karigar and click Apply.</div>
    <?php else: ?>
      <div style="overflow:auto;max-height:70vh">
        <table>
          <thead>
            <tr>
              <th>Mc no.</th>
              <?php foreach($MACHINES as $mc): ?><th><?=h($mc)?></th><?php endforeach; ?>
              <th>Day Total</th>
            </tr>
          </thead>
          <tbody>
            <?php foreach($DATES as $d): ?>
              <tr>
                <td><?=h(date('n/j/Y', strtotime($d)))?></td>
                <?php foreach($MACHINES as $mc): ?>
                  <td><?= isset($GRID[$d][$mc]) ? n0($GRID[$d][$mc]) : '' ?></td>
                <?php endforeach; ?>
                <td><strong><?= n0($ROW_T[$d]??0) ?></strong></td>
              </tr>
            <?php endforeach; ?>
          </tbody>
          <tfoot>
            <tr>
              <td><strong>TOTAL</strong></td>
              <?php foreach($MACHINES as $mc): ?><td><strong><?= n0($COL_T[$mc]??0) ?></strong></td><?php endforeach; ?>
              <td><strong><?= n0($GRAND) ?></strong></td>
            </tr>
          </tfoot>
        </table>
      </div>
    <?php endif; ?>
  </div>

<?php if($karigar_id>0): ?>
  <div class="card">
    <div style="display:flex;justify-content:space-between;align-items:center;margin-bottom:8px">
      <div><strong>Salary</strong> <span class="pill"><?=h($period_lbl)?></span></div>
      <div class="muted">Total Meters: <?= n0($TOTAL_MTR) ?></div>
    </div>
    <?php if (empty($MTR_BY_Q)): ?>
      <div class="muted">No production for selected period.</div>
    <?php else: ?>
    <table>
      <thead>
        <tr><th>Quality</th><th>Meters</th><th>Rate</th><th>Amount</th></tr>
      </thead>
      <tbody>
        <?php foreach($MTR_BY_Q as $qid=>$m): $rate = $QR[$qid] ?? 0; $amt = $m * $rate; ?>
          <tr>
            <td><?= h($QN[$qid] ?? ('Q'.$qid)) ?></td>
            <td><?= number_format($m,2) ?></td>
            <td><?= number_format($rate,2) ?></td>
            <td><?= number_format($amt,2) ?></td>
          </tr>
        <?php endforeach; ?>
      </tbody>
      <tfoot>
        <tr><td colspan="3"><strong>Gross</strong></td><td><strong><?= number_format($gross,2) ?></strong></td></tr>
        <tr><td colspan="3">Extra</td><td><?= number_format($extra,2) ?></td></tr>
        <tr><td colspan="3">Deduction</td><td><?= number_format($ded,2) ?></td></tr>
        <tr><td colspan="3">Advance</td><td><?= number_format($adv,2) ?></td></tr>
        <tr><td colspan="3"><strong>Net</strong></td><td><strong><?= number_format($net,2) ?></strong></td></tr>
      </tfoot>
    </table>
    <?php endif; ?>
  </div>
  <?php endif; ?>

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