View Code - 20260723

loom_patia.php | 20260723_000646.php Initial Domain Snapshot

<?php
/* =============================================================================
   File: /public_html/karigar/loom_patia.php
   View: Karigar ⇒ Machine matrix (Date rows × Machine columns)
   Rules:
     - No Machine dropdown. Columns = machines where the karigar has meters.
     - Karigar login: khata locked (read-only pill), only own data.
     - ERP login (owner/admin/folder or page_access): can pick a karigar (dropdown).
   ============================================================================= */
header('X-Frame-Options: SAMEORIGIN');
error_reporting(E_ALL); ini_set('display_errors',1);

/* ---------- Dual auth (Karigar OR ERP) ---------- */
$CTX = ['mode'=>'none','company_id'=>0,'user_id'=>0,'role'=>'','karigar_id'=>0,'name'=>''];
// Try karigar portal
$kar_auth = __DIR__.'/_auth.php';
if (is_file($kar_auth)) {
  require_once $kar_auth;
  if (function_exists('karigar_user')) {
    $ku = karigar_user(); // soft read
    if ($ku && !empty($ku['karigar_id'])) {
      $CTX = [
        'mode'=>'karigar',
        'company_id'=>(int)$ku['company_id'],
        'user_id'=>(int)($ku['user_id'] ?? 0),
        'role'=>'karigar',
        'karigar_id'=>(int)$ku['karigar_id'],
        'name'=>(string)($ku['name'] ?? 'Karigar'),
      ];
    }
  }
}
// Try ERP
if ($CTX['mode']==='none') {
  $erp_auth = __DIR__.'/../erp/modules/auth/auth.php';
  if (is_file($erp_auth)) {
    require_once $erp_auth;
    if (function_exists('auth_user')) {
      $eu = auth_user();
      if ($eu && !empty($eu['id'])) {
        $CTX = [
          'mode'=>'erp',
          'company_id'=>(int)$eu['company_id'],
          'user_id'=>(int)$eu['id'],
          'role'=>strtolower((string)$eu['role']),
          'karigar_id'=>0,
          'name'=>(string)($eu['name'] ?? 'User'),
        ];
      }
    }
  }
}
if ($CTX['mode']==='none') { http_response_code(403); echo "<h1>403</h1><p>Please login.</p>"; exit; }

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

/* ---------- Access (ERP) ---------- */
function _table_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 = ($CTX['mode']==='karigar') ? true : in_array($CTX['role'],['owner','admin','folder'],true);
if ($CTX['mode']==='erp' && !$allowed && _table_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([$CTX['company_id'],$PAGE_KEY,$CTX['user_id']]); $allowed=(bool)$q->fetchColumn();
}
if(!$allowed){ http_response_code(403); echo "<h1>403</h1><p>Access denied.</p>"; exit; }

/* ---------- 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 n2($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($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){ $o=[]; $d=new DateTime($a); $e=new DateTime($b); for(; $d<=$e; $d->modify('+1 day')) $o[]=$d->format('Y-m-d'); return $o; }
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;
}

/* ---------- Filters ---------- */
$company_id=(int)$CTX['company_id'];
$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);
if ($CTX['mode']==='karigar') $karigar_id = (int)$CTX['karigar_id'];

/* Karigar’s actual khata (lock for karigar login) */
$SELF_KHATA_ID=0;
if ($karigar_id>0 && _table_exists($pdo,'loom_karigar_master')){
  $st=$pdo->prepare("SELECT khata_id FROM loom_karigar_master WHERE company_id=? AND id=? LIMIT 1");
  $st->execute([$company_id,$karigar_id]); $SELF_KHATA_ID=(int)$st->fetchColumn();
  if ($CTX['mode']==='karigar' && $SELF_KHATA_ID>0){ $khata_id=$SELF_KHATA_ID; }
}

/* Khata list (labels / ERP dropdown) */
$KHATA_MAP=[]; $KHATAS=[];
if (_table_exists($pdo,'khatas')){
  $kq=$pdo->prepare("SELECT id, code, name FROM khatas WHERE company_id=? AND (is_active=1 OR is_active IS NULL) ORDER BY id");
  $kq->execute([$company_id]);
  foreach($kq as $r){ $KHATA_MAP[(int)$r['id']]=$r; $KHATAS[]=$r; }
}
$SELF_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');

/* Karigar dropdown list (ERP) */
$KLIST=[];
if ($CTX['mode']==='erp' && _table_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 as $r){ $nm=trim((string)$r['karigar_name']); if($nm==='') $nm='Karigar#'.$r['id']; $KLIST[]=['id'=>(int)$r['id'],'name'=>$nm]; }
}

/* ---------- Prepare grid containers ---------- */
$DATES = dates_between($date_from,$date_to);
$GRID=[]; $ROW_T=[]; $COL_T=[]; $MACHINE_SET=[]; $GRAND=0.0;
foreach($DATES as $d){ $GRID[$d]=[]; $ROW_T[$d]=0.0; }
$MACHINES = []; // filled later

/* ---------- Robust fetch: MySQL JSON → MariaDB CTE → PHP fallback ---------- */
if ($karigar_id>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');

$accum = function($d,$mc,$m) use (&$GRID,&$ROW_T,&$COL_T,&$MACHINE_SET,&$GRAND){
    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;
    $MACHINE_SET[$mc] = true;
    $GRAND += $m;
  };

$got=false;

// A) MySQL 8 — JSON_TABLE
  if (!_is_maria($pdo)) {
    try{
      $sql = "SELECT le.machine_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);
      $hit=0;
      foreach($st as $r){
        $d=(string)$r['dte']; $mc=(int)$r['machine_id']; $m=(float)$r['meter'];
        if($m<=0 || $d==='') continue;
        $accum($d,$mc,$m); $hit++;
      }
      if($hit>0) $got=true;
    }catch(Throwable $e){ /* go to B */ }
  }

// B) MariaDB — CTE / no-CTE
  if(!$got && _is_maria($pdo)){
    $p=[ ':cid'=>$company_id, ':kid'=>$karigar_id, ':d1'=>$date_from, ':d2'=>$date_to ];
    if($khata_id>0) $p[':khata']=$khata_id;

$cte = "
      WITH RECURSIVE seq(n) AS (SELECT 0 UNION ALL SELECT n+1 FROM seq WHERE n<999)
      SELECT le.machine_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";
    try{
      $st=$pdo->prepare($cte); $st->execute($p);
      $hit=0;
      foreach($st as $r){
        $d=(string)$r['dte']; $mc=(int)$r['machine_id']; $m=(float)$r['meter'];
        if($m<=0 || $d==='') continue; $accum($d,$mc,$m); $hit++;
      }
      if($hit>0) $got=true;
    }catch(Throwable $e){
      try{
        $nocte="
          SELECT le.machine_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($nocte); $st->execute($p);
        $hit=0;
        foreach($st as $r){
          $d=(string)$r['dte']; $mc=(int)$r['machine_id']; $m=(float)$r['meter'];
          if($m<=0 || $d==='') continue; $accum($d,$mc,$m); $hit++;
        }
        if($hit>0) $got=true;
      }catch(Throwable $e2){ /* fall to PHP */ }
    }
  }

// C) PHP fallback — header-window (-31 days)
  if(!$got){
    $hdr_from = date('Y-m-d', strtotime($date_from.' -31 days'));
    $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, lines_json
                         FROM loom_production_entry
                        WHERE $wh
                     ORDER BY entry_date, id");
    $st->execute($args);

foreach($st as $r){
      $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']));
      $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){
        $kid=(int)($ln['karigar_id']??0); if($kid!==$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; }
        $accum(date('Y-m-d',$ts),(int)$r['machine_id'],$m);
      }
    }
  }

$MACHINES = array_keys($MACHINE_SET); sort($MACHINES);
}

/* ---------- CSV export ---------- */
if ((int)gv('csv',0)===1){
  header('Content-Type:text/csv; charset=utf-8');
  header('Content-Disposition: attachment; filename=patia_k'.$karigar_id.'_'.$month_str.'_H'.$half.'.csv');
  $out=fopen('php://output','w');
  $head=array_merge(['Date'], array_map(fn($m)=>$m,$MACHINES), ['Day Total']); fputcsv($out,$head);
  foreach($DATES as $d){ $row=[$d]; foreach($MACHINES as $m){ $row[]=isset($GRID[$d][$m])?n2($GRID[$d][$m]):''; } $row[]=n2($ROW_T[$d]??0); fputcsv($out,$row); }
  $last=array_merge(['TOTAL'], array_map(fn($m)=>n2($COL_T[$m]??0),$MACHINES), [n2($GRAND)]); fputcsv($out,$last);
  fclose($out); exit;
}

/* ---------- 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</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">
      
      <?php if($CTX['mode']==='karigar'): ?>
        <input type="hidden" name="khata_id" value="<?=h($khata_id)?>">
        <div class="field">
          <label>Khata</label>
          <div class="pill"><?=h($SELF_KHATA_LABEL)?></div>
        </div>
      <?php else: ?>
        <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>
      <?php endif; ?>

<?php if($CTX['mode']==='erp'): ?>
      <div class="field">
        <label>Karigar</label>
        <select name="karigar_id">
          <option value="0">Select</option>
          <?php foreach($KLIST as $k): ?>
            <option value="<?=h($k['id'])?>" <?= $karigar_id===$k['id']?'selected':''?>><?=h($k['name'])?></option>
          <?php endforeach; ?>
        </select>
      </div>
      <?php else: ?>
        <input type="hidden" name="karigar_id" value="<?=h($karigar_id)?>">
      <?php endif; ?>

<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($CTX['mode']==='karigar'): ?><span class="pill">Your Data</span><?php endif; ?>
      </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></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.</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]) ? n2($GRID[$d][$mc]) : '' ?></td>
                <?php endforeach; ?>
                <td><strong><?= n2($ROW_T[$d]??0) ?></strong></td>
              </tr>
            <?php endforeach; ?>
          </tbody>
          <tfoot>
            <tr>
              <td><strong>TOTAL</strong></td>
              <?php foreach($MACHINES as $mc): ?><td><strong><?= n2($COL_T[$mc]??0) ?></strong></td><?php endforeach; ?>
              <td><strong><?= n2($GRAND) ?></strong></td>
            </tr>
          </tfoot>
        </table>
      </div>
    <?php endif; ?>
  </div>
</div>
</body>
</html>