View Code - 20260721

gray_life_cycle_report_sort.php | 20260721_154032.php Initial Bulk Import

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

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

function h($value)
{
    return htmlspecialchars((string)$value, ENT_QUOTES, 'UTF-8');
}

$from = isset($_GET['from']) ? trim((string)$_GET['from']) : date('Y-m-d', strtotime('-30 days'));
$to = isset($_GET['to']) ? trim((string)$_GET['to']) : date('Y-m-d');
$sort = isset($_GET['sort']) ? trim((string)$_GET['sort']) : '';

$grey_shortage_expr = "CASE WHEN gr.finish_mts > 0 THEN (COALESCE(gr.shortage, 0) / gr.finish_mts) * 100 ELSE 0 END";
$cutting_shortage_expr = "CASE WHEN gr.finish_mts > 0 THEN ((gr.finish_mts - COALESCE(ca.used_meter, 0)) / gr.finish_mts) * 100 ELSE 0 END";
$second_pct_expr = "CASE WHEN (COALESCE(ca.fresh, 0) + COALESCE(ca.second_cut, 0)) > 0 THEN (COALESCE(ca.second_cut, 0) / (COALESCE(ca.fresh, 0) + COALESCE(ca.second_cut, 0))) * 100 ELSE 0 END";

$order_by = 'gr.rec_date DESC, gr.gray_id DESC';
switch ($sort) {
    case 'grey_high':
        $order_by = $grey_shortage_expr . ' DESC, gr.rec_date DESC, gr.gray_id DESC';
        break;
    case 'grey_low':
        $order_by = $grey_shortage_expr . ' ASC, gr.rec_date DESC, gr.gray_id DESC';
        break;
    case 'cut_high':
        $order_by = $cutting_shortage_expr . ' DESC, gr.rec_date DESC, gr.gray_id DESC';
        break;
    case 'cut_low':
        $order_by = $cutting_shortage_expr . ' ASC, gr.rec_date DESC, gr.gray_id DESC';
        break;
    case 'sec_high':
        $order_by = $second_pct_expr . ' DESC, gr.rec_date DESC, gr.gray_id DESC';
        break;
    case 'sec_low':
        $order_by = $second_pct_expr . ' ASC, gr.rec_date DESC, gr.gray_id DESC';
        break;
}

$params = [':cid' => $company_id];
$where = 'WHERE gr.company_id = :cid';

if ($from !== '') {
    $where .= ' AND gr.rec_date >= :from';
    $params[':from'] = $from;
}

if ($to !== '') {
    $where .= ' AND gr.rec_date <= :to';
    $params[':to'] = $to;
}

$sql = "
SELECT
    gr.gray_id,
    gr.gpno,
    gr.rec_date,
    gr.grey_mts,
    gr.finish_mts,
    COALESCE(gr.shortage, 0) AS shortage,
    fq.quality_name,
    m.mill_name,
    COALESCE(ca.fresh, 0) AS fresh,
    COALESCE(ca.second_cut, 0) AS second_cut,
    COALESCE(ca.used_meter, 0) AS used_meter
FROM gray_receive gr
LEFT JOIN fabric_quality_master fq ON fq.id = gr.quality_id
LEFT JOIN mill_master m ON m.id = gr.mill_id AND m.company_id = gr.company_id
LEFT JOIN (
    SELECT
        gray_id,
        company_id,
        COALESCE(SUM(fresh_saree), 0) AS fresh,
        COALESCE(SUM(second_saree), 0) AS second_cut,
        COALESCE(SUM(total_meter), 0) AS used_meter
    FROM cutting_entry
    GROUP BY gray_id, company_id
) ca ON ca.gray_id = gr.gray_id AND ca.company_id = gr.company_id
$where
ORDER BY $order_by
";

$stmt = $pdo->prepare($sql);
$stmt->execute($params);
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

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

<div class="container-fluid mt-3">
    <div class="card shadow-sm border-0 mb-3 no-print">
        <div class="card-body p-3">
            <form method="GET" class="row g-3 align-items-end">
                <div class="col-md-2">
                    <label class="small fw-bold">From Date</label>
                    <input type="date" name="from" class="form-control form-control-sm" value="<?= h($from) ?>">
                </div>
                <div class="col-md-2">
                    <label class="small fw-bold">To Date</label>
                    <input type="date" name="to" class="form-control form-control-sm" value="<?= h($to) ?>">
                </div>
                <div class="col-md-4">
                    <label class="small fw-bold">Sort By</label>
                    <select name="sort" class="form-select form-select-sm">
                        <option value="" <?= $sort === '' ? 'selected' : '' ?>>Default (Date Newest)</option>
                        <optgroup label="GR Shortage %">
                            <option value="grey_high" <?= $sort === 'grey_high' ? 'selected' : '' ?>>GR Shortage High to Low</option>
                            <option value="grey_low" <?= $sort === 'grey_low' ? 'selected' : '' ?>>GR Shortage Low to High</option>
                        </optgroup>
                        <optgroup label="Cutting Shortage %">
                            <option value="cut_high" <?= $sort === 'cut_high' ? 'selected' : '' ?>>Cutting Shortage High to Low</option>
                            <option value="cut_low" <?= $sort === 'cut_low' ? 'selected' : '' ?>>Cutting Shortage Low to High</option>
                        </optgroup>
                        <optgroup label="Second %">
                            <option value="sec_high" <?= $sort === 'sec_high' ? 'selected' : '' ?>>Second % High to Low</option>
                            <option value="sec_low" <?= $sort === 'sec_low' ? 'selected' : '' ?>>Second % Low to High</option>
                        </optgroup>
                    </select>
                </div>
                <div class="col-md-4">
                    <button type="submit" class="btn btn-sm btn-primary px-4 shadow-sm">Filter Data</button>
                    <a href="?from=<?= h($from) ?>&to=<?= h($to) ?>" class="btn btn-sm btn-outline-secondary ms-1 shadow-sm">Reset Sorting</a>
                    <button type="button" onclick="window.print()" class="btn btn-sm btn-dark ms-1 shadow-sm">Print Report</button>
                </div>
            </form>
        </div>
    </div>

<div class="card shadow-sm border-0">
        <div class="card-header bg-white py-2 d-flex justify-content-between align-items-center">
            <h6 class="mb-0 fw-bold text-primary">Grey Lifecycle Sort Report</h6>
            <span class="badge bg-light text-dark border no-print"><?= count($rows) ?> Lots Found</span>
        </div>
        <div class="card-body p-0">
            <div class="table-responsive">
                <table class="table table-bordered report-table mb-0">
                    <thead>
                        <tr>
                            <th>Rec. Date</th>
                            <th>Gray ID / GP / Mill</th>
                            <th>Quality</th>
                            <th class="text-end">Finish Mtr</th>
                            <th class="text-end">GR Srtg %</th>
                            <th class="text-end">Fresh</th>
                            <th class="text-end">Second</th>
                            <th class="text-end">Cut Srtg %</th>
                            <th class="text-end">Second %</th>
                        </tr>
                    </thead>
                    <tbody>
                        <?php foreach ($rows as $r): ?>
                            <?php
                            $finish_mts = (float)$r['finish_mts'];
                            $fresh = (float)$r['fresh'];
                            $second_cut = (float)$r['second_cut'];
                            $used_meter = (float)$r['used_meter'];
                            $total_cut = $fresh + $second_cut;
                            $gr_shortage_pct = $finish_mts > 0 ? (((float)$r['shortage'] / $finish_mts) * 100) : 0;
                            $cutting_shortage_pct = $finish_mts > 0 ? ((($finish_mts - $used_meter) / $finish_mts) * 100) : 0;
                            $second_pct = $total_cut > 0 ? (($second_cut / $total_cut) * 100) : 0;
                            ?>
                            <tr>
                                <td class="text-center"><?= date('d-m-Y', strtotime($r['rec_date'])) ?></td>
                                <td>
                                    <span class="badge-g">ID: <?= h($r['gray_id']) ?></span>
                                    <span class="gp-text">GP: <?= h($r['gpno']) ?></span>
                                    <span class="mill-text"><?= h($r['mill_name']) ?></span>
                                </td>
                                <td><?= h($r['quality_name']) ?></td>
                                <td class="text-end fw-bold"><?= number_format($finish_mts, 2) ?></td>
                                <td class="text-end text-danger fw-bold"><?= number_format($gr_shortage_pct, 2) ?>%</td>
                                <td class="text-end text-success fw-bold"><?= number_format($fresh, 2) ?></td>
                                <td class="text-end text-danger"><?= number_format($second_cut, 2) ?></td>
                                <td class="text-end text-warning fw-bold"><?= number_format($cutting_shortage_pct, 2) ?>%</td>
                                <td class="text-end text-info fw-bold"><?= number_format($second_pct, 2) ?>%</td>
                            </tr>
                        <?php endforeach; ?>
                        <?php if (empty($rows)): ?>
                            <tr>
                                <td colspan="9" class="text-center py-4 text-muted">No records found for the selected dates.</td>
                            </tr>
                        <?php endif; ?>
                    </tbody>
                </table>
            </div>
        </div>
    </div>
</div>

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