View Code - 20260721

va_challan_report.php | 20260721_154033.php Initial Bulk Import

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

require_once __DIR__ . '/modules/auth/page_acl.php';
$ctx = page_require_access('va_pending_report'); 
$pdo = $ctx['pdo']; 
$company_id = (int)$ctx['company_id'];

// Previous Year DB Name
$prev_db = "shree_erp_2425";

$search_type = $_GET['type'] ?? 'challan'; 
$query_val = trim($_GET['q'] ?? '');
$use_prev = isset($_GET['prev_year']) && $_GET['prev_year'] == 1;
$is_print = isset($_GET['print']) && $_GET['print'] == '1';

$data_send = [];
$data_recv = [];
$grey_details = null;
$cutting_details = [];
$record_found = false;

function normalize_report_mgray_rows($raw) {
    if (!$raw) {
        return [];
    }

$decoded = json_decode((string)$raw, true);
    if (!is_array($decoded)) {
        return [];
    }

$rows = [];
    foreach ($decoded as $row) {
        $gray_id = trim((string)($row['gray_id'] ?? ''));
        $pcs = (float)($row['pcs'] ?? 0);

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

$rows[] = [
            'gray_id' => $gray_id,
            'pcs' => $pcs,
        ];
    }

return $rows;
}

function get_report_row_matched_pcs(array $row, string $queryGrayId): float {
    $queryGrayId = trim($queryGrayId);
    if ($queryGrayId === '') {
        return 0;
    }

$mgrayRows = normalize_report_mgray_rows($row['mgray_detail'] ?? '');
    if ($mgrayRows) {
        $matched = 0;
        foreach ($mgrayRows as $detail) {
            if ((string)$detail['gray_id'] === $queryGrayId) {
                $matched += (float)$detail['pcs'];
            }
        }
        return $matched;
    }

$greyIds = array_filter(array_map('trim', explode(',', (string)($row['grey_id'] ?? ''))), fn($value) => $value !== '');
    return in_array($queryGrayId, $greyIds, true) ? (float)($row['pcs'] ?? 0) : 0;
}

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

function get_report_gray_breakup(array $row): array {
    $mgrayRows = normalize_report_mgray_rows($row['mgray_detail'] ?? '');
    if ($mgrayRows) {
        return array_map(function ($detail) {
            return [
                'gray_id' => (string)$detail['gray_id'],
                'pcs' => (float)$detail['pcs'],
                'exact' => true,
            ];
        }, $mgrayRows);
    }

$greyIds = array_values(array_filter(array_map('trim', explode(',', (string)($row['grey_id'] ?? ''))), fn($value) => $value !== ''));
    if (!$greyIds) {
        return [];
    }

if (count($greyIds) === 1) {
        return [[
            'gray_id' => $greyIds[0],
            'pcs' => (float)($row['pcs'] ?? 0),
            'exact' => true,
        ]];
    }

return array_map(function ($grayId) {
        return [
            'gray_id' => $grayId,
            'pcs' => null,
            'exact' => false,
        ];
    }, $greyIds);
}

if ($query_val !== '') {
    $table_prefix = $use_prev ? "`$prev_db`." : "";

if ($search_type === 'challan') {
        // CHALLAN MODE
        $stmt = $pdo->prepare("SELECT v.*, fq.quality_name FROM {$table_prefix}va_send_entry v LEFT JOIN fabric_quality_master fq ON v.quality = fq.id WHERE v.challan_no = :q AND v.company_id = :cid");
        $stmt->execute([':q' => $query_val, ':cid' => $company_id]);
        $data_send = $stmt->fetchAll(PDO::FETCH_ASSOC);

$stmt = $pdo->prepare("SELECT * FROM {$table_prefix}va_receive_entry WHERE challan_no = :q AND company_id = :cid");
        $stmt->execute([':q' => $query_val, ':cid' => $company_id]);
        $data_recv = $stmt->fetchAll(PDO::FETCH_ASSOC);
        
        if(!empty($data_send) || !empty($data_recv)) $record_found = true;

} else {
        // GRAY ID MODE (Lot No = Gray ID)
        $stmt = $pdo->prepare("SELECT g.*, fq.quality_name FROM {$table_prefix}gray_receive g LEFT JOIN fabric_quality_master fq ON g.quality_id = fq.id WHERE g.gray_id = :q AND g.company_id = :cid");
        $stmt->execute([':q' => $query_val, ':cid' => $company_id]);
        $grey_details = $stmt->fetch(PDO::FETCH_ASSOC);

if($grey_details) {
            $record_found = true;
            
            // 2. Cutting History (Using gray_id as lot_no)
            $stmt = $pdo->prepare("SELECT gray_id as lot_no, rec_date, total_meter, fresh_saree, second_saree FROM {$table_prefix}cutting_entry WHERE gray_id = :q AND company_id = :cid ORDER BY rec_date ASC");
            $stmt->execute([':q' => $query_val, ':cid' => $company_id]);
            $cutting_details = $stmt->fetchAll(PDO::FETCH_ASSOC);

// 3. VA Send Details (Single + Multi Gray)
            $detail_like = '%"gray_id":"' . $query_val . '"%';
            $stmt = $pdo->prepare("SELECT v.*, fq.quality_name FROM {$table_prefix}va_send_entry v LEFT JOIN fabric_quality_master fq ON v.quality = fq.id WHERE (FIND_IN_SET(:q, REPLACE(IFNULL(v.grey_id, ''), ' ', '')) > 0 OR v.mgray_detail LIKE :detail_like) AND v.company_id = :cid ORDER BY v.send_date ASC, v.id ASC");
            $stmt->execute([':q' => $query_val, ':detail_like' => $detail_like, ':cid' => $company_id]);
            $raw_send_rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

$matched_send_by_challan = [];
            foreach ($raw_send_rows as $send_row) {
                $matched_pcs = get_report_row_matched_pcs($send_row, $query_val);
                if ($matched_pcs <= 0) {
                    continue;
                }

$send_row['matched_pcs'] = $matched_pcs;
                $data_send[] = $send_row;

$challan_no = (string)($send_row['challan_no'] ?? '');
                if ($challan_no !== '') {
                    $matched_send_by_challan[$challan_no] = ($matched_send_by_challan[$challan_no] ?? 0) + $matched_pcs;
                }
            }

// 4. VA Receive Details (Linked via Challans found in Send)
            if(!empty($data_send)) {
                $challans = array_unique(array_column($data_send, 'challan_no'));
                $inQuery = implode(',', array_fill(0, count($challans), '?'));

$stmt = $pdo->prepare("SELECT challan_no, SUM(pcs) AS total_sent_pcs FROM {$table_prefix}va_send_entry WHERE challan_no IN ($inQuery) AND company_id = ? GROUP BY challan_no");
                $stmt->execute([...$challans, $company_id]);
                $total_send_by_challan = [];
                foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $send_total_row) {
                    $total_send_by_challan[(string)$send_total_row['challan_no']] = (float)$send_total_row['total_sent_pcs'];
                }

$stmt = $pdo->prepare("SELECT * FROM {$table_prefix}va_receive_entry WHERE challan_no IN ($inQuery) AND company_id = ? ORDER BY receive_date ASC");
                $stmt->execute([...$challans, $company_id]);
                $raw_recv_rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

foreach ($raw_recv_rows as $recv_row) {
                    $challan_no = (string)($recv_row['challan_no'] ?? '');
                    $matched_sent = (float)($matched_send_by_challan[$challan_no] ?? 0);
                    $total_sent = (float)($total_send_by_challan[$challan_no] ?? 0);
                    $allocation_ratio = $total_sent > 0 ? ($matched_sent / $total_sent) : 0;
                    $recv_row['matched_pcs'] = (float)($recv_row['total_pcs'] ?? 0) * $allocation_ratio;
                    $data_recv[] = $recv_row;
                }
            }
        }
    }
}

if (!$is_print) {
    require_once __DIR__ . '/partials/header.php';
} else {
    ?>
    <!doctype html>
    <html lang="en">
    <head>
        <meta charset="utf-8">
        <meta name="viewport" content="width=device-width, initial-scale=1">
        <title>VA Challan Report</title>
        <style>
            body {
                font-family: Arial, sans-serif;
                margin: 18px;
                color: #111;
                background: #fff;
            }
            .container {
                width: 100%;
                max-width: 100%;
            }
            .card {
                border: 1px solid #ddd;
                padding: 18px;
            }
            table {
                width: 100%;
                border-collapse: collapse;
            }
            th,
            td {
                border: 1px solid #ccc;
                padding: 6px 8px;
            }
            th {
                background: #f3f3f3;
            }
            .text-end,
            .text-right {
                text-align: right;
            }
            .text-center {
                text-align: center;
            }
            .row {
                display: flex;
                flex-wrap: wrap;
                gap: 16px;
            }
            .col-md-6,
            .col-12 {
                box-sizing: border-box;
                width: 100%;
            }
            @media (min-width: 768px) {
                .col-md-6 {
                    width: calc(50% - 8px);
                }
            }
            .no-print {
                display: none !important;
            }
        </style>
    </head>
    <body>
    <?php
}

$print_url = '?' . http_build_query([
    'type' => $search_type,
    'q' => $query_val,
    'prev_year' => $use_prev ? 1 : 0,
    'print' => 1,
]);
?>

<div class="container mt-4">
    <div class="card shadow-sm border-0 p-4">
        <div class="d-flex justify-content-between align-items-center mb-4">
            <h4 class="text-center fw-bold mb-0">VA & Grey Tracking (Lot Wise)</h4>
            <?php if (!$is_print && $record_found): ?>
                <a href="<?= htmlspecialchars($print_url) ?>" target="_blank" class="btn btn-dark no-print">Print</a>
            <?php endif; ?>
        </div>

<div class="d-flex justify-content-center mb-4 no-print">
            <div class="btn-group border rounded-pill p-1 bg-light">
                <a href="?type=challan" class="btn <?= $search_type=='challan'?'btn-primary':'btn-outline-primary' ?> rounded-pill px-4">Challan Mode</a>
                <a href="?type=grey" class="btn <?= $search_type=='grey'?'btn-primary':'btn-outline-primary' ?> rounded-pill px-4">Gray ID Mode</a>
            </div>
        </div>

<form method="GET" class="row g-2 justify-content-center mb-4 no-print">
            <input type="hidden" name="type" value="<?= $search_type ?>">
            <div class="col-md-5">
                <div class="input-group">
                    <input type="text" name="q" class="form-control" placeholder="Enter <?= $search_type ?>..." value="<?= htmlspecialchars($query_val) ?>" required>
                    <button class="btn btn-dark px-4">Track</button>
                </div>
            </div>
        </form>

<?php if ($query_val && !$record_found && !$use_prev): ?>
            <div class="alert alert-warning text-center py-4 border-0 shadow-sm">
                <h5>Record Not Found in Current Year!</h5>
                <p>Kya aap pichhle year ke database mein check karna chahte hain?</p>
                <a href="?type=<?= $search_type ?>&q=<?= $query_val ?>&prev_year=1" class="btn btn-warning fw-bold">Check in Previous Year</a>
            </div>
        <?php endif; ?>

<?php if ($record_found): ?>
            
            <?php if ($search_type === 'challan'): ?>
                <div class="row">
                    <div class="col-md-6 border-end">
                        <h6 class="fw-bold text-primary text-uppercase">Sending History</h6>
                        <table class="table table-sm border small">
                            <thead class="table-light"><tr><th>Date</th><th>Quality</th><th>Gray ID Breakup</th><th class="text-end">Pcs</th></tr></thead>
                            <tbody><?php $ts=0; foreach($data_send as $s): $ts+=$s['pcs']; ?>
                                <?php $gray_breakup = get_report_gray_breakup($s); ?>
                                <tr>
                                    <td><?= date('d-m-Y', strtotime($s['send_date'])) ?></td>
                                    <td><?= $s['quality_name'] ?></td>
                                    <td>
                                        <?php if ($gray_breakup): ?>
                                            <?php foreach ($gray_breakup as $breakup): ?>
                                                <div>
                                                    Gray ID: <span class="fw-bold"><?= htmlspecialchars((string)$breakup['gray_id']) ?></span>
                                                    <?php if ($breakup['pcs'] !== null): ?>
                                                        | PCS: <span class="fw-bold text-primary"><?= format_report_number($breakup['pcs']) ?></span>
                                                    <?php else: ?>
                                                        | <span class="text-muted">PCS split not saved</span>
                                                    <?php endif; ?>
                                                </div>
                                            <?php endforeach; ?>
                                        <?php else: ?>
                                            <span class="text-muted">Gray detail not available</span>
                                        <?php endif; ?>
                                    </td>
                                    <td class="text-end"><?= format_report_number($s['pcs']) ?></td>
                                </tr>
                            <?php endforeach; ?></tbody>
                            <tfoot class="fw-bold bg-light"><tr><td colspan="3">Total Sent</td><td class="text-end"><?= format_report_number($ts) ?></td></tr></tfoot>
                        </table>
                    </div>
                    <div class="col-md-6">
                        <h6 class="fw-bold text-success text-uppercase">Receiving History</h6>
                        <table class="table table-sm border small">
                            <thead class="table-light"><tr><th>Date</th><th>Party</th><th class="text-end">Pcs</th></tr></thead>
                            <tbody><?php $tr=0; foreach($data_recv as $r): $tr+=$r['total_pcs']; ?>
                                <tr><td><?= date('d-m-Y', strtotime($r['receive_date'])) ?></td><td><?= $r['party_name'] ?></td><td class="text-end"><?= $r['total_pcs'] ?></td></tr>
                            <?php endforeach; ?></tbody>
                            <tfoot class="fw-bold bg-light"><tr><td colspan="2">Total Recv</td><td class="text-end"><?= $tr ?></td></tr></tfoot>
                        </table>
                    </div>
                </div>

<?php else: ?>
                <div class="row g-3">
                    <div class="col-12">
                        <div class="p-3 bg-light border rounded d-flex justify-content-between align-items-center">
                            <div>
                                <h5 class="mb-0 fw-bold"><?= $grey_details['quality_name'] ?> (Gray ID: #<?= $query_val ?>)</h5>
                                <small>Received: <?= date('d-m-Y', strtotime($grey_details['rec_date'])) ?></small>
                            </div>
                            <div>
                                <span class="badge bg-primary fs-6 px-3">Gray: <?= $grey_details['grey_mts'] ?></span>
                                <span class="badge bg-success fs-6 px-3">Finish: <?= $grey_details['finish_mts'] ?></span>
                            </div>
                        </div>
                    </div>

<div class="col-12 mt-4">
                        <h6 class="fw-bold text-secondary">CUTTING HISTORY</h6>
                        <table class="table table-bordered table-sm align-middle mb-0 small">
                            <thead class="table-dark text-center">
                                <tr><th>DATE</th><th>LOT NO (GRAY ID)</th><th>METERS USED</th><th>FRESH SAREE</th><th>SECOND</th></tr>
                            </thead>
                            <tbody class="text-center">
                                <?php $tc=0; foreach($cutting_details as $c): $tc+=$c['fresh_saree']; ?>
                                <tr>
                                    <td><?= date('d-m-Y', strtotime($c['rec_date'])) ?></td>
                                    <td class="fw-bold"><?= $c['lot_no'] ?></td>
                                    <td><?= $c['total_meter'] ?></td>
                                    <td class="text-primary fw-bold fs-6"><?= $c['fresh_saree'] ?></td>
                                    <td><?= $c['second_saree'] ?></td>
                                </tr>
                                <?php endforeach; ?>
                            </tbody>
                            <tfoot class="table-light fw-bold text-center">
                                <tr><td colspan="3">Total Fresh Available from Cutting</td><td class="text-primary"><?= $tc ?></td><td></td></tr>
                            </tfoot>
                        </table>
                    </div>

<div class="col-12 mt-4">
                        <h6 class="fw-bold text-info">VA PROCESS (SEND & RECEIVE)</h6>
                        <div class="row">
                            <div class="col-md-6">
                                <table class="table table-sm border table-hover small">
                                    <thead class="bg-primary text-white"><tr><th>Send Date</th><th>Challan</th><th class="text-end">Pcs</th></tr></thead>
                                    <tbody><?php $vas=0; foreach($data_send as $vs): $line_pcs = (float)($vs['matched_pcs'] ?? $vs['pcs'] ?? 0); $vas+=$line_pcs; ?>
                                        <tr><td><?= date('d-m-Y', strtotime($vs['send_date'])) ?></td><td class="fw-bold"><?= $vs['challan_no'] ?></td><td class="text-end"><?= format_report_number($line_pcs) ?></td></tr>
                                    <?php endforeach; ?></tbody>
                                    <tfoot class="fw-bold"><tr><td colspan="2">Total Sent to VA</td><td class="text-end"><?= format_report_number($vas) ?></td></tr></tfoot>
                                </table>
                            </div>
                            <div class="col-md-6">
                                <table class="table table-sm border table-hover small">
                                    <thead class="bg-success text-white"><tr><th>Recv Date</th><th>Challan</th><th class="text-end">Pcs</th></tr></thead>
                                    <tbody><?php $var=0; foreach($data_recv as $vr): $line_pcs = (float)($vr['matched_pcs'] ?? $vr['total_pcs'] ?? 0); $var+=$line_pcs; ?>
                                        <tr><td><?= date('d-m-Y', strtotime($vr['receive_date'])) ?></td><td class="fw-bold"><?= $vr['challan_no'] ?></td><td class="text-end"><?= format_report_number($line_pcs) ?></td></tr>
                                    <?php endforeach; ?></tbody>
                                    <tfoot class="fw-bold"><tr><td colspan="2">Total Recv from VA</td><td class="text-end"><?= format_report_number($var) ?></td></tr></tfoot>
                                </table>
                            </div>
                        </div>
                    </div>

<div class="col-12 mt-3">
                        <div class="card bg-light border-0 py-2 px-3">
                            <div class="d-flex justify-content-between align-items-center fw-bold">
                                <span>STOCK SUMMARY (#<?= $query_val ?>)</span>
                                <div>
                                    <span class="me-4 text-warning">Pending at VA: <?= format_report_number($vas - $var) ?> Pcs</span>
                                    <span class="<?= ($tc - $vas < 0) ? 'text-danger' : 'text-success' ?>">
                                        Not Sent: <?= format_report_number($tc - $vas) ?> Pcs
                                    </span>
                                </div>
                            </div>
                        </div>
                    </div>
                </div>
            <?php endif; ?>
        <?php endif; ?>
    </div>
</div>

<?php if (!$is_print): ?>
<?php require_once __DIR__ . '/partials/footer.php'; ?>
<?php else: ?>
<script>
window.addEventListener('load', function () {
    window.print();
});
</script>
</body>
</html>
<?php endif; ?>