View Code - 20260721

gray_lifecycle_problem_report.php | 20260721_154032.php Initial Bulk Import

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

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

$from  = $_GET['from'] ?? date('Y-m-01');
$to    = $_GET['to'] ?? date('Y-m-d');
$order = $_GET['order'] ?? 'DESC';

$orderSql = ($order === 'ASC') ? 'ASC' : 'DESC';

$sql = "
SELECT 
    g.gray_id, 
    g.rec_date,
    fq.quality_name,
    g.finish_mts,
    g.shortage,
    g.financial_year,

(SELECT COALESCE(SUM(fresh_saree), 0) FROM cutting_entry 
     WHERE gray_id = g.gray_id AND company_id = g.company_id) as fresh,

(SELECT COALESCE(SUM(second_saree), 0) FROM cutting_entry 
     WHERE gray_id = g.gray_id AND company_id = g.company_id) as second_cut,

(SELECT COALESCE(SUM(total_meter), 0) FROM cutting_entry 
     WHERE gray_id = g.gray_id AND company_id = g.company_id) as used_meter,

(SELECT COALESCE(SUM(pcs), 0) FROM va_send_entry 
     WHERE FIND_IN_SET(g.gray_id, grey_id) 
     AND company_id = g.company_id 
     AND financial_year = g.financial_year) as sent_pcs,

(SELECT COALESCE(SUM(vr.total_pcs), 0) 
     FROM va_receive_entry vr
     JOIN va_send_entry vs 
       ON vr.challan_no = vs.challan_no 
      AND vr.financial_year = vs.financial_year
     WHERE FIND_IN_SET(g.gray_id, vs.grey_id) 
       AND vr.company_id = g.company_id) as recv_pcs

FROM gray_receive g
LEFT JOIN fabric_quality_master fq ON fq.id = g.quality_id

WHERE g.company_id = :cid
AND g.rec_date BETWEEN :f AND :t

GROUP BY g.gray_id, g.financial_year
HAVING (fresh > 0 OR second_cut > 0)
ORDER BY g.rec_date $orderSql, g.gray_id $orderSql
";

$st = $pdo->prepare($sql);
$st->execute([
    ':cid' => $company_id,
    ':f'   => $from,
    ':t'   => $to
]);

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

function format_report_pcs($value) {
    $value = (float)$value;
    return fmod($value, 1.0) == 0.0
        ? (string)(int)$value
        : rtrim(rtrim(number_format($value, 2, '.', ''), '0'), '.');
}

$recvStmt = $pdo->prepare(" 
    SELECT challan_no, financial_year, COALESCE(SUM(total_pcs), 0) AS recv_pcs
    FROM va_receive_entry
    WHERE company_id = :cid
    GROUP BY challan_no, financial_year
");
$recvStmt->execute([':cid' => $company_id]);
$recvMap = [];
foreach ($recvStmt->fetchAll(PDO::FETCH_ASSOC) as $rv) {
    $recvMap[$rv['financial_year'] . '||' . $rv['challan_no']] = (float)$rv['recv_pcs'];
}

$sendStmt = $pdo->prepare(" 
    SELECT challan_no, financial_year, grey_id, mgray_detail, pcs
    FROM va_send_entry
    WHERE company_id = :cid
");
$sendStmt->execute([':cid' => $company_id]);

$lifecycleMap = [];
foreach ($sendStmt->fetchAll(PDO::FETCH_ASSOC) as $vs) {
    $fy = (string)($vs['financial_year'] ?? '');
    $challanNo = (string)($vs['challan_no'] ?? '');
    $recvKey = $fy . '||' . $challanNo;
    $challanRecv = (float)($recvMap[$recvKey] ?? 0);
    $greyId = trim((string)($vs['grey_id'] ?? ''));

if ($greyId !== '') {
        $grayIds = array_filter(array_map('trim', explode(',', $greyId)), fn($v) => $v !== '');

foreach ($grayIds as $singleGrayId) {
            $mapKey = $fy . '||' . $singleGrayId;
            if (!isset($lifecycleMap[$mapKey])) {
                $lifecycleMap[$mapKey] = ['sent_pcs' => 0, 'recv_pcs' => 0];
            }

$lifecycleMap[$mapKey]['sent_pcs'] += (float)$vs['pcs'];
            $lifecycleMap[$mapKey]['recv_pcs'] += $challanRecv;
        }

continue;
    }

$detailRows = json_decode((string)($vs['mgray_detail'] ?? ''), true);
    if (!is_array($detailRows) || empty($detailRows)) {
        continue;
    }

$cleanRows = [];
    $detailTotal = 0;
    foreach ($detailRows as $detail) {
        $singleGrayId = trim((string)($detail['gray_id'] ?? ''));
        $pcs = (float)($detail['pcs'] ?? 0);

if ($singleGrayId === '' || $pcs <= 0) {
            continue;
        }

$cleanRows[] = ['gray_id' => $singleGrayId, 'pcs' => $pcs];
        $detailTotal += $pcs;
    }

foreach ($cleanRows as $detail) {
        $mapKey = $fy . '||' . $detail['gray_id'];
        if (!isset($lifecycleMap[$mapKey])) {
            $lifecycleMap[$mapKey] = ['sent_pcs' => 0, 'recv_pcs' => 0];
        }

$allocRecv = $detailTotal > 0 ? ($challanRecv * $detail['pcs'] / $detailTotal) : 0;

$lifecycleMap[$mapKey]['sent_pcs'] += $detail['pcs'];
        $lifecycleMap[$mapKey]['recv_pcs'] += $allocRecv;
    }
}

$filteredRows = [];
foreach ($rows as $row) {
    $mapKey = (string)$row['financial_year'] . '||' . (string)$row['gray_id'];
    if (isset($lifecycleMap[$mapKey])) {
        $row['sent_pcs'] = $lifecycleMap[$mapKey]['sent_pcs'];
        $row['recv_pcs'] = $lifecycleMap[$mapKey]['recv_pcs'];
    } else {
        $row['sent_pcs'] = 0;
        $row['recv_pcs'] = 0;
    }

$not_sent = $row['fresh'] - $row['sent_pcs'];
    $total_cut = $row['fresh'] + $row['second_cut'];
    $second_pct = $total_cut > 0 ? round(($row['second_cut'] / $total_cut) * 100, 2) : 0;
    $shortage_pct = $row['finish_mts'] > 0 ? round(($row['shortage'] / $row['finish_mts']) * 100, 2) : 0;
    $cutting_pct = $row['finish_mts'] > 0 ? round((($row['finish_mts'] - $row['used_meter']) / $row['finish_mts']) * 100, 2) : 0;

if (
        ($row['sent_pcs'] > 0 && $not_sent > 0)
        || ($row['sent_pcs'] > $row['fresh'])
        || ($shortage_pct > 20)
        || ($cutting_pct > 9)
        || ($second_pct > 5)
    ) {
        $filteredRows[] = $row;
    }
}
$rows = $filteredRows;

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

body { zoom: 0.85; }

.no-print { display: none !important; }

.container-fluid {
        padding: 0 !important;
        margin: 0 !important;
        width: 100% !important;
    }

.card {
        border: none !important;
        box-shadow: none !important;
        margin: 0 !important;
    }

.card-body { padding: 0 !important; }

.table {
        width: 100% !important;
        font-size: 10px !important;
        border-collapse: collapse !important;
    }

.table th, .table td {
        padding: 3px !important;
        white-space: nowrap;
    }

tr, td, th {
        page-break-inside: avoid !important;
    }
}
</style>

<div class="d-none d-print-block text-center mb-2">
    <h5>Grey Lifecycle Problem Report</h5>
</div>

<div class="card shadow-sm mb-2 border-0 no-print">
    <div class="card-body d-flex justify-content-between">
        <h4>⚠ Grey Lifecycle Problem Report</h4>
        <div>
            <a href="?from=<?= $from ?>&to=<?= $to ?>&order=ASC" class="btn btn-sm btn-outline-primary">ASC</a>
            <a href="?from=<?= $from ?>&to=<?= $to ?>&order=DESC" class="btn btn-sm btn-outline-primary">DESC</a>
            <button onclick="window.print()" class="btn btn-dark btn-sm">Print</button>
        </div>
    </div>
</div>

<div class="card">
<div class="table-responsive">
<table class="table table-bordered table-hover align-middle">
<thead class="table-dark">
<tr>
    <th>Grey ID</th>
    <th>Date</th>
    <th>Quality</th>
    <th>Finish</th>
    <th>GR %</th>
    <th>Fresh</th>
    <th>Second</th>
    <th>Cut %</th>
    <th>Second %</th>
    <th>Sent</th>
    <th>Not Sent</th>
    <th>Received</th>
    <th style="min-width:280px;">Status</th>
</tr>
</thead>

<tbody>

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

$not_sent = $r['fresh'] - $r['sent_pcs'];
$total_cut = $r['fresh'] + $r['second_cut'];

$second_pct   = $total_cut > 0 ? round(($r['second_cut'] / $total_cut) * 100, 2) : 0;
$shortage_pct = $r['finish_mts'] > 0 ? round(($r['shortage'] / $r['finish_mts']) * 100, 2) : 0;
$cutting_pct  = $r['finish_mts'] > 0 ? round((($r['finish_mts'] - $r['used_meter']) / $r['finish_mts']) * 100, 2) : 0;

/* 🔥 MULTI STATUS */
$status = [];

if($r['sent_pcs'] > $r['fresh']){
    $status[] = 'Over Sent';
}

if($r['sent_pcs'] > 0 && $not_sent > 0){
    $status[] = 'VA Partial';
}

if($shortage_pct > 20){
    $status[] = 'High GR Shortage';
}

if($cutting_pct > 9){
    $status[] = 'High Cutting Shortage';
}

if($second_pct > 5){
    $status[] = 'High Second %';
}

$status_text = implode(' + ', $status);
?>

<?php endforeach; ?>

</tbody>
</table>
</div>
</div>

</div>

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