View Code - 20260722

yarn_stock_coverage_report.php | 20260722_120325.php Initial Domain Snapshot

<?php
error_reporting(E_ALL);
ini_set('display_errors',1);

require_once __DIR__ . '/modules/auth/page_acl.php';
$ctx = page_require_access('yarn_entry');

$pdo        = $ctx['pdo'];
$company_id = (int)$ctx['company_id'];

require_once __DIR__ . '/partials/header.php';

/* =========================================================
   1. BUILD STOCK MAP (denier + color)
========================================================= */

function build_stock_map(PDO $pdo,$cid){

$stock=[];

$sql="
    SELECT denier,color,SUM(weight) wt
    FROM yarn_in
    WHERE company_id=:cid
    GROUP BY denier,color
    ";

$st=$pdo->prepare($sql);
    $st->execute([':cid'=>$cid]);

$in=$st->fetchAll(PDO::FETCH_ASSOC);

$sql="
    SELECT denier,color,SUM(weight) wt
    FROM yarn_out
    WHERE company_id=:cid
    GROUP BY denier,color
    ";

$st=$pdo->prepare($sql);
    $st->execute([':cid'=>$cid]);

$out=$st->fetchAll(PDO::FETCH_ASSOC);

foreach($in as $r){

$k=strtoupper(trim($r['denier'])).'|'.strtoupper(trim($r['color']));
        $stock[$k]=($stock[$k]??0)+(float)$r['wt'];

}

foreach($out as $r){

$k=strtoupper(trim($r['denier'])).'|'.strtoupper(trim($r['color']));
        $stock[$k]=($stock[$k]??0)-(float)$r['wt'];

}

return $stock;

}

$stock_map = build_stock_map($pdo,$company_id);

/* =========================================================
   2. LOAD yarn_master
========================================================= */

$yarn_master = [];

$sql="SELECT denier,color,technical_name
FROM yarn_master
WHERE company_id=:cid";

$st=$pdo->prepare($sql);
$st->execute([':cid'=>$company_id]);

while($r=$st->fetch(PDO::FETCH_ASSOC)){

$d=strtoupper(trim($r['denier']));
$c=strtoupper(trim($r['color']));
$t=strtoupper(trim($r['technical_name']));

$yarn_master[]=[
'denier'=>$d,
'color'=>$c,
'tech'=>$t
];

}

/* =========================================================
   3. PLANNER DATA
========================================================= */

$days=1;
$method='expected_meter';

$total_machines_expected = 116;

/* CURRENT MACHINE DIST */

$sql="
SELECT quality_id,COUNT(*) machines
FROM loom_quality_master
WHERE company_id=:cid
GROUP BY quality_id
";

$st=$pdo->prepare($sql);
$st->execute([':cid'=>$company_id]);

$current_dist=[];

while($r=$st->fetch(PDO::FETCH_ASSOC)){
$current_dist[$r['quality_id']]=$r['machines'];
}

/* QUALITY MASTER */

$sql="
SELECT id,quality_name,avg_per_day_pro
FROM qualities
WHERE company_id=:cid
";

$st=$pdo->prepare($sql);
$st->execute([':cid'=>$company_id]);

$qnames=[];
$qexpected=[];

while($q=$st->fetch(PDO::FETCH_ASSOC)){

$qnames[$q['id']]=$q['quality_name'];
$qexpected[$q['id']]=$q['avg_per_day_pro'];

}

/* MACHINE AVG */

$sql="
SELECT machine_id,AVG(daily_meter) avg_meter
FROM(
SELECT machine_id,entry_date,SUM(meter_total) daily_meter
FROM production_entry
WHERE company_id=:cid
GROUP BY machine_id,entry_date
)t
GROUP BY machine_id
";

$st=$pdo->prepare($sql);
$st->execute([':cid'=>$company_id]);

$machine_avg=[];

while($r=$st->fetch(PDO::FETCH_ASSOC)){
$machine_avg[$r['machine_id']]=$r['avg_meter'];
}

/* PRODUCTION CALC */

$quality_meter=[];

$sql="
SELECT machine_code,quality_id
FROM loom_quality_master
WHERE company_id=:cid
";

$st=$pdo->prepare($sql);
$st->execute([':cid'=>$company_id]);

while($m=$st->fetch(PDO::FETCH_ASSOC)){

$machine=(int)filter_var($m['machine_code'],FILTER_SANITIZE_NUMBER_INT);
$qid=(int)$m['quality_id'];

$expected=$qexpected[$qid]??0;

$eff=1;

if(isset($machine_avg[$machine]) && $expected>0){

$eff=$machine_avg[$machine]/$expected;

if($eff>1)$eff=1;
if($eff<0.5)$eff=0.5;

}

$avg=$expected*$eff;

$quality_meter[$qid]=($quality_meter[$qid]??0)+$avg;

}

/* =========================================================
   4. YARN REQUIREMENT
========================================================= */

$sql="
SELECT loom_quality_id,yarn_name,gram_per_meter
FROM loom_quality_yarn_data
WHERE cid=:cid
";

$st=$pdo->prepare($sql);
$st->execute([':cid'=>$company_id]);

$yarns=$st->fetchAll(PDO::FETCH_ASSOC);

$sql="SELECT id,short_name FROM loom_quality_data";
$loomq=$pdo->query($sql)->fetchAll(PDO::FETCH_ASSOC);

$loom_map=[];

foreach($loomq as $l){
$loom_map[$l['short_name']]=$l['id'];
}

/* =========================================================
   5. BUILD REQUIREMENT MAP
========================================================= */

$req=[];

foreach($quality_meter as $qid=>$meter){

$qname=$qnames[$qid]??'';

$loom_id=$loom_map[$qname]??null;

if(!$loom_id) continue;

foreach($yarns as $y){

if($y['loom_quality_id']!=$loom_id) continue;

$kg=($meter*$y['gram_per_meter'])/1000;

$yn=strtoupper(trim($y['yarn_name']));

$req[$yn]=($req[$yn]??0)+$kg;

}

/* =========================================================
   6. EXTRACT DENIER FROM YARN NAME
========================================================= */

function extract_denier($name){

$name=strtoupper($name);

if(preg_match('/\d+\/\d+/',$name,$m))
return $m[0];

if(preg_match('/\d+\s+[A-Z]+/',$name,$m))
return trim($m[0]);

if(preg_match('/\d+/',$name,$m))
return $m[0];

return '';

}

/* =========================================================
   7. CALCULATE STOCK PER YARN
========================================================= */

$report=[];

foreach($req as $yarn=>$kg){

$denier=extract_denier($yarn);

$stock=0;

foreach($yarn_master as $ym){

if(strtoupper($ym['denier'])!=$denier) continue;

if($ym['color']==''){

foreach($stock_map as $k=>$w){

if(strpos($k,$denier.'|')===0)
$stock+=$w;

}

}else{

$key=$denier.'|'.$ym['color'];

$stock+=($stock_map[$key]??0);

}

$report[]=[
'name'=>$yarn,
'req'=>$kg,
'stock'=>$stock
];

}

<div class="card-header bg-dark text-white">
Yarn Coverage Report
</div>

<tr>
<th>YARN NAME</th>
<th>DAILY REQUIREMENT</th>
<th>STOCK</th>
<th>COVERAGE</th>
<th>STATUS</th>
</tr>

</thead>

<tbody>

<?php foreach($report as $r):

$days = $r['stock']>0 ? $r['stock']/$r['req'] : 0;

$status='SAFE';
$cls='bg-success';

if($days<3){
$status='CRITICAL';
$cls='bg-danger';
}
elseif($days<7){
$status='LOW';
$cls='bg-warning';
}

<tr>

</tr>

<?php endforeach; ?>

</tbody>

</table>

</div>

<?php require_once __DIR__ . '/partials/footer.php'; ?>