View Code - 20260721

loom_salary_send__to_paid.php | 20260721_154032.php Initial Bulk Import

<?php
/* ============================================================================
   File: /erp/loom_salary_report.php
   Title: Salary Report (Karigar × Quality) — RAW meters, No Rounding
   Highlights:
     • Report Period (Year/Month/Period) ≠ Apply Period (Year/Month/Period)
     • Separate selectors for Send-to-Deduction & Mark-as-Paid
     • Safe defaults (no warnings when data empty)
     • Deduction applied to chosen Apply Period end-date
     • Merges extra_data, deduction_data, advance_data by date range
     • PDF/Print/Excel & Save unique period report (apply queue)
   ============================================================================ */
header('X-Frame-Options: SAMEORIGIN');
error_reporting(E_ALL); ini_set('display_errors', 1);

/* [0] Auth + DB */
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'; }

/* [0a] Composer autoload (for Dompdf) — page local only */
$PDF_AVAILABLE = false;
$autoload = __DIR__ . '/vendor/autoload.php';
if (is_file($autoload)) {
  require_once $autoload;
  $PDF_AVAILABLE = class_exists(\Dompdf\Dompdf::class);
}

/* [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 pv($k,$d=null){ return isset($_POST[$k])?trim((string)$_POST[$k]):$d; }
function safe_json($s){ $a=json_decode($s,true); return is_array($a)?$a:[]; }
function norm_half($h){ $h=strtoupper((string)$h); return in_array($h,['1','2','F'],true)?$h:'F'; }
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 label_for_period(int $y,int $m,string $half,string $from,string $to): string{
  if ($half==='F') return 'FULL '.date('M', mktime(0,0,0,$m,1,$y)).' '.$y;
  if ($half==='1') return '1-15 '.date('M', mktime(0,0,0,$m,1,$y)).' '.$y;
  if ($half==='2') return '16-END '.date('M', mktime(0,0,0,$m,1,$y)).' '.$y;
  return $from.' to '.$to;
}

/* [2] Feature flag */
$FF_USE_QUEUE_ADJ = true;

/* [3] Report (Generate) Period — GET filters */
$year      = (int)gv('year', date('Y'));
$month     = (int)gv('month', date('n'));
$half      = norm_half(gv('half','F'));   // default Full
[$date_from,$date_to] = yrmo_half_range($year,$month,$half);
$khata_id  = (int)gv('khata_id', 0);
$machine_id= (int)gv('machine_id', 0);
$period_label = label_for_period($year,$month,$half,$date_from,$date_to);

/* [4] Company */
$company_name = "Company #$company_id";
try{
  $st=$pdo->prepare("SELECT name FROM companies WHERE id=?");
  $st->execute([$company_id]);
  if ($nm=$st->fetchColumn()) $company_name=$nm;
}catch(Exception $e){}

/* [5] Masters */
$QUALS=[]; $QUAL_RATE=[];
$q=$pdo->prepare("SELECT quality_id AS id, quality_name AS name, rate
                    FROM quality_rates WHERE company_id=? ORDER BY quality_name");
$q->execute([$company_id]);
foreach($q as $r){ $QUALS[(int)$r['id']]=$r['name']; $QUAL_RATE[(int)$r['id']]=(float)$r['rate']; }

$KAR_NAME=[]; $KAR_ENTRY=[]; $KAR_KHATA_CODE=[]; $KAR_KHATA_ID=[];
$st=$pdo->prepare("SELECT id,karigar_name,entry_name,khata_code,khata_id
                     FROM loom_karigar_master WHERE company_id=? ORDER BY karigar_name");
$st->execute([$company_id]);
foreach($st as $r){
  $id=(int)$r['id'];
  $KAR_NAME[$id]=$r['karigar_name']?:("Karigar #$id");
  $KAR_ENTRY[$id]=$r['entry_name']??'';
  $KAR_KHATA_CODE[$id]=$r['khata_code']??'';
  $KAR_KHATA_ID[$id]=(int)($r['khata_id']??0);
}

/* [6] Production rows for Report period */
$sql = "SELECT id, entry_date, machine_id, khata_id, quality_id, lines_json
          FROM loom_production_entry
         WHERE company_id=? AND entry_date BETWEEN ? AND ?";
$args=[$company_id,$date_from,$date_to];
if($khata_id>0){ $sql.=" AND khata_id=?"; $args[]=$khata_id; }
if($machine_id>0){ $sql.=" AND machine_id=?"; $args[]=$machine_id; }
$sql.=" ORDER BY entry_date,id";
$rows=$pdo->prepare($sql); $rows->execute($args);
$rows=$rows->fetchAll(PDO::FETCH_ASSOC);

/* [7] Aggregate (karigar × quality) */
$quality_set=[]; $quality_ids=[];         // SAFE defaults
$karigar_set=[]; $MTR=[]; $MTR_KAR_TOT=[];
$_skipped_bad_date=0; $_skipped_out_range=0;
$from_ts=strtotime($date_from.' 00:00:00'); $to_ts=strtotime($date_to.' 23:59:59');

foreach($rows as $row){
  $qid=(int)$row['quality_id']; if($qid<=0) continue;
  $quality_set[$qid]=true;
  $row_entry=substr((string)$row['entry_date'],0,10);
  if(!preg_match('/^\d{4}-\d{2}-\d{2}$/',$row_entry)){ $row_entry=date('Y-m-d', strtotime($row['entry_date'])); }
  $lines=safe_json($row['lines_json']);
  foreach($lines as $ln){
    $kid=(int)($ln['karigar_id']??0);
    $m  =(float)($ln['meter']??0);
    if($kid<=0 || $m<=0) continue;
    $dYmd=norm_line_date($ln['date']??'',$row_entry,$year,$month);
    $ts=strtotime($dYmd);
    if(!$ts){ $_skipped_bad_date++; $ts=strtotime($row_entry); }
    if($ts<$from_ts || $ts>$to_ts){ $_skipped_out_range++; continue; }
    $karigar_set[$kid]=true;
    $MTR[$kid][$qid]=($MTR[$kid][$qid]??0)+$m;
    $MTR_KAR_TOT[$kid]=($MTR_KAR_TOT[$kid]??0)+$m;
  }
}
$quality_ids=array_keys($quality_set);
usort($quality_ids, fn($a,$b)=>strcasecmp($QUALS[$a]??'', $QUALS[$b]??''));
$kar_list=array_keys($karigar_set);
usort($kar_list, fn($a,$b)=>strcasecmp($KAR_NAME[$a]??("K$a"), $KAR_NAME[$b]??("K$b")));

/* [8] Money merge (extra/deduction/advance) */
function sum_money_tbl($pdo,$company_id,$tbl,$kid,$from,$to,$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;
}
$EXTRA=[]; $DEDUCT=[]; $ADV=[];
if ($FF_USE_QUEUE_ADJ){
  try{
    $sq=$pdo->prepare("SELECT karigar_id, type, SUM(amount) amt
                         FROM salary_adjust_queue
                        WHERE company_id=? AND person_type='karigar'
                          AND apply_start=? AND apply_end=?
                        GROUP BY karigar_id,type");
    $sq->execute([$company_id,$date_from,$date_to]);
    foreach($sq as $r){
      $kid=(int)$r['karigar_id']; if($kid<=0) continue;
      $t=(string)$r['type']; $amt=(float)$r['amt'];
      if($t==='extra')     $EXTRA[$kid]=($EXTRA[$kid]??0)+$amt;
      if($t==='deduction') $DEDUCT[$kid]=($DEDUCT[$kid]??0)+$amt;
    }
  }catch(Exception $e){}
}
foreach($kar_list as $kid){
  $EXTRA[$kid]  = ($EXTRA[$kid]  ?? 0) + sum_money_tbl($pdo,$company_id,'extra_data',    $kid,$date_from,$date_to);
  $DEDUCT[$kid] = ($DEDUCT[$kid] ?? 0) + sum_money_tbl($pdo,$company_id,'deduction_data',$kid,$date_from,$date_to);
  $ADV[$kid]    =                      sum_money_tbl($pdo,$company_id,'advance_data',   $kid,$date_from,$date_to);
}

/* [9] Gross/Net */
$GROSS=[]; $NET=[]; $bank_total=0.0; $cash_total=0.0; $grand_net=0.0;
foreach($kar_list as $kid){
  $g=0.0; foreach($quality_ids as $qid){ $g+=($MTR[$kid][$qid]??0)*($QUAL_RATE[$qid]??0); }
  $GROSS[$kid]=$g;
  $n=$g+($EXTRA[$kid]??0)-($DEDUCT[$kid]??0)-($ADV[$kid]??0);
  $NET[$kid]=$n; $grand_net+=$n;
  if(!empty($KAR_KHATA_ID[$kid])) $bank_total+=$n; else $cash_total+=$n;
}

/* [10] Ensure tables (safe) */
$pdo->exec("CREATE TABLE IF NOT EXISTS deduction_data (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  company_id BIGINT NOT NULL,
  karigar_id BIGINT NOT NULL,
  khata_id BIGINT NULL,
  amount DECIMAL(12,2) NOT NULL DEFAULT 0,
  date DATE NOT NULL,
  remark VARCHAR(255) NULL,
  created_by BIGINT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX(company_id,karigar_id,date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

/* [11] Actions: Send to Deduction / Mark Paid — with independent Apply selectors */
$flash='';
if($_SERVER['REQUEST_METHOD']==='POST' && in_array(pv('action'),['send_to_deduction','mark_paid'],true)){
  $isPaid = (pv('action')==='mark_paid');
  $apply_year  = (int)pv('apply_year',  $year);
  $apply_month = (int)pv('apply_month', $month);
  $apply_half  = norm_half(pv('apply_half', $half));
  if($apply_month<1 || $apply_month>12) $apply_month=$month;
  if($apply_year<2000 || $apply_year>2100) $apply_year=$year;

[$apply_from,$apply_to]=yrmo_half_range($apply_year,$apply_month,$apply_half);
  $apply_label=label_for_period($apply_year,$apply_month,$apply_half,$apply_from,$apply_to);

$ins=$pdo->prepare("INSERT INTO deduction_data
    (company_id,karigar_id,khata_id,amount,date,remark,created_by)
     VALUES (?,?,?,?,?,?,?)");
  $chk=$pdo->prepare("SELECT 1 FROM deduction_data
                       WHERE company_id=? AND karigar_id=?
                         AND date BETWEEN ? AND ?
                         AND remark LIKE ? LIMIT 1");

$added=0; $tag=$isPaid?'PAID':'AUTO';
  foreach($kar_list as $kid){
    $amt=(float)($NET[$kid]??0); if($amt==0.0) continue;
    $like="%Salary ($apply_label)%";
    $chk->execute([$company_id,$kid,$apply_from,$apply_to,$like]);
    if($chk->fetchColumn()) continue;
    $remark="Salary ($apply_label) from loom_salary_report [$tag]";
    $ins->execute([$company_id,$kid,$KAR_KHATA_ID[$kid]??0,round($amt,2),$apply_to,$remark,$user_id]);
    $added++;
  }
  $flash = ($isPaid? 'Marked as PAID: ' : 'Sent to Deduction: ').$added." row(s) for $apply_label.";
}

/* [12] PDF export — unchanged from your last working version (omitted here for brevity) */
/* If you rely on it, keep your existing PDF block. */

/* ============================= HTML ============================== */
?>
<!doctype html>
<html>
<head>
<meta charset="utf-8">
<title>Salary Report (No Rounding)</title>
<meta name="viewport" content="width=device-width, initial-scale=1">
<style>
:root{ --green:#34A853; --mint:#F5FFF7; --stone:#5F6368; --sage:#D0E8D0; --warn:#ef4444; }
*{box-sizing:border-box} body{margin:0;background:var(--mint);color:#222;font-family:system-ui,-apple-system,Segoe UI,Roboto,Ubuntu,Arial}
.wrap{max-width:1200px;margin:20px auto;padding:0 12px}
.h1{color:#2c7d3f;margin:6px 0 12px}
.hbar{display:flex;gap:12px;align-items:flex-end;flex-wrap:wrap;background:#e9f7ee;border:1px solid #d6f0dd;padding:12px;border-radius:12px}
.hbar label{font-size:12px;display:block;margin-bottom:4px}
.hbar select{padding:8px 10px;border:1px solid #cfe8d0;border-radius:10px;background:#fff;min-width:120px}
.btn{background:var(--green);color:#fff;border:none;border-radius:12px;padding:9px 14px;font-weight:600;cursor:pointer;text-decoration:none;display:inline-block}
.btn.gray{background:#5F6368}
.btn.warn{background:var(--warn)}
.flash{background:#e7ffe9;border:1px solid #c7f0cb;color:#1e5a2c;padding:8px 12px;border-radius:10px;margin:10px 0;display:inline-block}
.card{background:#fff;border:1px solid #ecf2ec;border-radius:14px;overflow:hidden;margin-top:16px}
.table{width:100%;border-collapse:separate;border-spacing:0}
.table th,.table td{padding:10px 12px;border-bottom:1px solid #eef5ef}
.table thead th{background:#cfe9d7;color:#1b4d2a;text-align:right}
.table thead th:first-child{text-align:left}
.table tbody td{text-align:right}
.table tbody td:first-child{text-align:left}
.table tfoot td{background:#fff9e8;font-weight:700}
.totalcol{background:#f3faf4;font-weight:700}
.small{font-size:12px;color:#555}
.row{display:flex;gap:10px;flex-wrap:wrap;align-items:flex-end}
</style>
</head>
<body>
<div class="wrap">
  <h2 class="h1">Salary Report <span class="small">(No Rounding)</span></h2>
  <div class="small" style="margin-bottom:8px;"><b>Company:</b> <?= h($company_name) ?></div>
  <?php if($flash): ?><div class="flash"><?= h($flash) ?></div><?php endif; ?>

<form class="hbar" method="get" action="">
    <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</option>
      </select>
    </div>
    <div>
      <label>Year</label>
      <select name="year">
        <?php for($y=date('Y')-2;$y<=date('Y')+1;$y++): ?>
          <option value="<?= $y ?>" <?= $y===$year?'selected':''?>><?= $y ?></option>
        <?php endfor; ?>
      </select>
    </div>
    <button class="btn" type="submit">Show Report</button>
  </form>

<div class="small" style="margin:10px 0;">
    <b>Summary:</b> Bank / <?= number_format($bank_total,2) ?>, Cash / <?= number_format($cash_total,2) ?>,
    <b>Total</b> <?= number_format($grand_net,2) ?> (Exact net; merged extra/deduction/advance by date-range)
  </div>

<div class="card" style="padding:12px">
    <div class="row">
      
      <form id="sendDeductionForm" method="post" style="display:flex; gap:10px; align-items:flex-end; flex-wrap:wrap">
        <input type="hidden" name="action" value="send_to_deduction">
        <div><label class="small">Apply Year</label>
          <select name="apply_year">
            <?php for($y=date('Y')-2;$y<=date('Y')+1;$y++): ?>
              <option value="<?= $y ?>" <?= $y===$year?'selected':''?>><?= $y ?></option>
            <?php endfor; ?>
          </select>
        </div>
        <div><label class="small">Apply Month</label>
          <select name="apply_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 class="small">Apply Period</label>
          <select name="apply_half">
            <option value="1">1–15</option>
            <option value="2">16–End</option>
            <option value="F" selected>Full</option>
          </select>
        </div>
        <button class="btn gray" type="submit">Send to Deduction</button>
        <span class="small">Deduction will apply only to the selected period.</span>
      </form>

<form id="markPaidForm" method="post" style="display:flex; gap:10px; align-items:flex-end; flex-wrap:wrap; margin-top:12px">
        <input type="hidden" name="action" value="mark_paid">
        <div><label class="small">Apply Year</label>
          <select name="apply_year">
            <?php for($y=date('Y')-2;$y<=date('Y')+1;$y++): ?>
              <option value="<?= $y ?>" <?= $y===$year?'selected':''?>><?= $y ?></option>
            <?php endfor; ?>
          </select>
        </div>
        <div><label class="small">Apply Month</label>
          <select name="apply_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 class="small">Apply Period</label>
          <select name="apply_half">
            <option value="1">1–15</option>
            <option value="2">16–End</option>
            <option value="F" selected>Full</option>
          </select>
        </div>
        <button class="btn warn" type="submit">Mark as Paid</button>
        <span class="small">Same as above but remark includes [PAID] tag.</span>
      </form>
    </div>
  </div>

<div class="card" style="margin-top:12px">
    <table class="table">
      <thead>
        <tr>
          <th>Karigar</th>
          <?php if (!empty($quality_ids)): ?>
            <?php foreach($quality_ids as $qid): ?>
              <th><?= h($QUALS[$qid] ?? ("Q$qid")) ?></th>
            <?php endforeach; ?>
          <?php endif; ?>
          <th>Total Mtr</th><th>Gross</th><th>Extra</th><th>Deduction</th><th>Advance</th><th>Payable</th>
        </tr>
      </thead>
      <tbody>
        <?php if (!empty($kar_list)): ?>
          <?php foreach($kar_list as $kid): ?>
            <tr>
              <td><?= h($KAR_NAME[$kid]) ?><?php if(!empty($KAR_ENTRY[$kid])): ?><br><span class="small"><?= h($KAR_ENTRY[$kid]) ?></span><?php endif; ?></td>
              <?php if (!empty($quality_ids)): ?>
                <?php foreach($quality_ids as $qid): ?>
                  <td><?= number_format($MTR[$kid][$qid] ?? 0, 2) ?></td>
                <?php endforeach; ?>
              <?php endif; ?>
              <td class="totalcol"><?= number_format($MTR_KAR_TOT[$kid] ?? 0, 2) ?></td>
              <td><?= number_format($GROSS[$kid] ?? 0, 2) ?></td>
              <td><?= number_format($EXTRA[$kid] ?? 0, 2) ?></td>
              <td><?= number_format($DEDUCT[$kid] ?? 0, 2) ?></td>
              <td><?= number_format($ADV[$kid] ?? 0, 2) ?></td>
              <td class="totalcol"><?= number_format($NET[$kid] ?? 0, 2) ?></td>
            </tr>
          <?php endforeach; ?>
        <?php else: ?>
          <tr><td colspan="<?= 6 + max(1,count($quality_ids)) ?>" class="small">No production rows in this period.</td></tr>
        <?php endif; ?>
      </tbody>
      <tfoot>
        <tr>
          <td>TOTAL</td>
          <?php if (!empty($quality_ids)): ?>
            <?php
              $GRAND_GROSS=0.0;
              foreach($quality_ids as $qid){
                $s=0.0; foreach($kar_list as $kid){ $s += $MTR[$kid][$qid] ?? 0; }
                $GRAND_GROSS += $s * ($QUAL_RATE[$qid]??0.0);
                echo '<td>'.number_format($s,2).'</td>';
              }
            ?>
          <?php endif; ?>
          <?php
            $TOT_MTR = 0.0; foreach($kar_list as $kid){ $TOT_MTR += $MTR_KAR_TOT[$kid] ?? 0; }
            $TOT_EXTRA = 0.0; foreach($kar_list as $kid){ $TOT_EXTRA += $EXTRA[$kid] ?? 0; }
            $TOT_DED   = 0.0; foreach($kar_list as $kid){ $TOT_DED   += $DEDUCT[$kid] ?? 0; }
            $TOT_ADV   = 0.0; foreach($kar_list as $kid){ $TOT_ADV   += $ADV[$kid] ?? 0; }
          ?>
          <td class="totalcol"><?= number_format($TOT_MTR,2) ?></td>
          <td><?= number_format($GRAND_GROSS ?? 0,2) ?></td>
          <td><?= number_format($TOT_EXTRA,2) ?></td>
          <td><?= number_format($TOT_DED,2) ?></td>
          <td><?= number_format($TOT_ADV,2) ?></td>
          <td class="totalcol"><?= number_format($grand_net,2) ?></td>
        </tr>
        <tr>
          <td colspan="<?= 6 + max(1,count($quality_ids)) ?>" class="small" style="text-align:left;padding:12px">
            Amount (= Σ Meters × Rate). Rates from <code>quality_rates</code>. Lines from <code>loom_production_entry.lines_json</code>.
            <br>Skipped: Bad Date = <?= (int)$_skipped_bad_date ?>, Out of Range = <?= (int)$_skipped_out_range ?>.
          </td>
        </tr>
      </tfoot>
    </table>
  </div>
</div>
</body>
</html>