View Code - 20260721

mesr_jobwork_yarn_consume_report.php | 20260721_154033.php Initial Bulk Import

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

$u = $ctx['user'] ?? null;
$company_id = (int)($ctx['company_id'] ?? 0);
$pdo = $ctx['pdo'] ?? null;
$is_print = isset($_GET['print']) && $_GET['print'] === '1';

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>Jobwork Yarn Consumption Report</title>
        <style>
            body {
                font-family: Arial, sans-serif;
                margin: 18px;
                color: #000;
                background: #fff;
            }

.print-sheet {
                width: 100%;
            }

.print-header {
                display: flex;
                justify-content: space-between;
                align-items: flex-start;
                margin-bottom: 16px;
            }

.report-title {
                margin: 0 0 8px;
                font-size: 24px;
            }

.report-meta {
                margin: 0;
                font-size: 14px;
                line-height: 1.5;
            }

table {
                width: 100%;
                border-collapse: collapse;
                font-size: 12px;
            }

th,
            td {
                border: 1px solid #000;
                padding: 6px 8px;
            }

th {
                text-align: left;
                background: #f2f2f2;
            }

.text-end {
                text-align: right;
            }

.table-total td {
                font-weight: 700;
            }
        </style>
    </head>
    <body>
    <?php
}

// ================== STEP 1: PARTY ==================
function h($value)
{
    return htmlspecialchars((string)$value, ENT_QUOTES, 'UTF-8');
}

$input_month = trim($_GET['month'] ?? date('Y-m'));
if (!preg_match('/^\d{4}-\d{2}$/', $input_month)) {
    $input_month = date('Y-m');
}

$selected_month = date('Y-m-01', strtotime($input_month . '-01'));
$prev_month = date('Y-m-01', strtotime($selected_month . ' -1 month'));
$start_date = date('Y-m-01', strtotime($selected_month));
$end_date = date('Y-m-t', strtotime($selected_month));

$party_stmt = $pdo->prepare(" 
    SELECT id, party_name, linked_cid, status
    FROM job_work_party
    WHERE company_id = :cid
    ORDER BY party_name ASC
");
$party_stmt->execute([':cid' => $company_id]);
$parties = $party_stmt->fetchAll(PDO::FETCH_ASSOC);

$party_id = (int)($_GET['party_id'] ?? 0);
if ($party_id <= 0 && !empty($parties)) {
    foreach ($parties as $party_row) {
        if ((int)($party_row['linked_cid'] ?? 0) > 0) {
            $party_id = (int)$party_row['id'];
            break;
        }
    }

if ($party_id <= 0) {
        $party_id = (int)$parties[0]['id'];
    }
}

$selected_party = null;
foreach ($parties as $party_row) {
    if ((int)$party_row['id'] === $party_id) {
        $selected_party = $party_row;
        break;
    }
}

if ($selected_party === null && !empty($parties)) {
    $selected_party = $parties[0];
    $party_id = (int)$selected_party['id'];
}

$party_name = trim((string)($selected_party['party_name'] ?? ''));
$linked_cid = (int)($selected_party['linked_cid'] ?? 0);

$specific_map = [];
$generic_map = [];
$in_data = [];
$opening_data = [];
$closing_data = [];

if ($linked_cid > 0) {
    $master_stmt = $pdo->prepare(" 
        SELECT technical_name, denier, color
        FROM yarn_master
        WHERE company_id = :cid
          AND is_active = 1
          AND technical_name IS NOT NULL
          AND TRIM(technical_name) <> ''
    ");
    $master_stmt->execute([':cid' => $linked_cid]);
    $master_rows = $master_stmt->fetchAll(PDO::FETCH_ASSOC);

foreach ($master_rows as $master_row) {
        $denier = strtoupper(trim((string)($master_row['denier'] ?? '')));
        $color = strtoupper(trim((string)($master_row['color'] ?? '')));
        $technical_name = trim((string)($master_row['technical_name'] ?? ''));

if ($technical_name === '' || $denier === '') {
            continue;
        }

if ($color !== '') {
            $specific_map[$denier . '|' . $color] = $technical_name;
        }

if (!isset($generic_map[$denier])) {
            $generic_map[$denier] = $technical_name;
        }
    }

if ($party_name !== '') {
        $in_stmt = $pdo->prepare(" 
            SELECT yarn_type, denier, color, SUM(weight) AS total_in
            FROM jobwork_send
            WHERE company_id = :cid
                            AND TRIM(party_name) = TRIM(:party)
              AND entry_date BETWEEN :start_date AND :end_date
            GROUP BY yarn_type, denier, color
        ");
        $in_stmt->execute([
            ':cid' => $company_id,
            ':party' => $party_name,
            ':start_date' => $start_date,
            ':end_date' => $end_date,
        ]);

while ($row = $in_stmt->fetch(PDO::FETCH_ASSOC)) {
            $denier = strtoupper(trim((string)($row['denier'] ?? '')));
            $color = strtoupper(trim((string)($row['color'] ?? '')));
            $raw_denier = trim((string)($row['denier'] ?? ''));
            $raw_color = trim((string)($row['color'] ?? ''));
            $raw_type = trim((string)($row['yarn_type'] ?? ''));

$technical_name = $specific_map[$denier . '|' . $color]
                ?? $generic_map[$denier]
                ?? trim($raw_denier . ' ' . $raw_color);

if ($technical_name === '') {
                $technical_name = $raw_type !== '' ? $raw_type : 'Unknown';
            }

if (!isset($in_data[$technical_name])) {
                $in_data[$technical_name] = 0.0;
            }

$in_data[$technical_name] += (float)$row['total_in'];
        }
    }

$stock_stmt = $pdo->prepare(" 
        SELECT technical_name, SUM(total_weight) AS total_weight
        FROM yarn_monthly_closing
        WHERE company_id = :cid
          AND month = :month
        GROUP BY technical_name
    ");

$stock_stmt->execute([
        ':cid' => $linked_cid,
        ':month' => $prev_month,
    ]);
    while ($row = $stock_stmt->fetch(PDO::FETCH_ASSOC)) {
        $opening_data[trim((string)$row['technical_name'])] = (float)$row['total_weight'];
    }

$stock_stmt->execute([
        ':cid' => $linked_cid,
        ':month' => $selected_month,
    ]);
    while ($row = $stock_stmt->fetch(PDO::FETCH_ASSOC)) {
        $closing_data[trim((string)$row['technical_name'])] = (float)$row['total_weight'];
    }
}

$all_names = array_values(array_unique(array_filter(array_merge(
    array_keys($in_data),
    array_keys($opening_data),
    array_keys($closing_data)
))));
sort($all_names, SORT_NATURAL | SORT_FLAG_CASE);

$total_opening = 0.0;
$total_in = 0.0;
$total_closing = 0.0;
$total_consumption = 0.0;

$print_query = [
    'party_id' => $party_id,
    'month' => date('Y-m', strtotime($selected_month)),
    'print' => '1',
];
$print_url = '?' . http_build_query($print_query);
?>

<div class="<?= $is_print ? 'print-sheet' : 'container mt-4' ?>">
    <div class="<?= $is_print ? '' : 'card p-3' ?>">
        <div class="d-flex justify-content-between align-items-center mb-3 flex-wrap gap-2">
            <div>
                <h3 class="mb-1">Jobwork Yarn Consumption Report</h3>
                <div><b>Party:</b> <?= h($party_name !== '' ? $party_name : 'Not selected') ?></div>
                <div><b>Linked CID:</b> <?= $linked_cid > 0 ? (int)$linked_cid : 'Not linked' ?></div>
            </div>
            <div class="text-end">
                <div><b>Month:</b> <?= h(date('F Y', strtotime($selected_month))) ?></div>
                <?php if (!$is_print): ?>
                    <button type="button" class="btn btn-outline-secondary btn-sm mt-2 no-print" onclick="window.open('<?= h($print_url) ?>', '_blank')">Print</button>
                <?php endif; ?>
            </div>
        </div>

<?php if (!$is_print): ?>
        <form method="get" class="row g-2 mb-3 no-print">
            <div class="col-md-4">
                <label class="form-label">Party</label>
                <select name="party_id" class="form-select">
                    <option value="">Select Party</option>
                    <?php foreach ($parties as $party_row): ?>
                        <option value="<?= (int)$party_row['id'] ?>" <?= ((int)$party_row['id'] === $party_id) ? 'selected' : '' ?>>
                            <?= h($party_row['party_name']) ?>
                            <?= !empty($party_row['linked_cid']) ? ' (CID ' . (int)$party_row['linked_cid'] . ')' : '' ?>
                        </option>
                    <?php endforeach; ?>
                </select>
            </div>
            <div class="col-md-3">
                <label class="form-label">Month</label>
                <input type="month" name="month" value="<?= h(date('Y-m', strtotime($selected_month))) ?>" class="form-control">
            </div>
            <div class="col-md-2 d-flex align-items-end">
                <button class="btn btn-primary w-100">Filter</button>
            </div>
        </form>
        <?php endif; ?>

<?php if (empty($parties)): ?>
            <div class="alert alert-warning mb-0">No jobwork party found for this company.</div>
        <?php elseif ($linked_cid <= 0): ?>
            <div class="alert alert-warning mb-0">Selected party is not linked with any MESR company ID, so opening and closing stock cannot be loaded.</div>
        <?php else: ?>
            <div class="table-responsive">
                <table class="table table-bordered table-sm align-middle mb-0">
                    <thead>
                        <tr>
                            <th>Technical Name</th>
                            <th class="text-end">Opening</th>
                            <th class="text-end">In</th>
                            <th class="text-end">Closing</th>
                            <th class="text-end">Consumption</th>
                        </tr>
                    </thead>
                    <tbody>
                    <?php if (empty($all_names)): ?>
                        <tr>
                            <td colspan="5" class="text-center text-muted">No data found for the selected month and party.</td>
                        </tr>
                    <?php else: ?>
                        <?php foreach ($all_names as $name): ?>
                            <?php
                            $opening = (float)($opening_data[$name] ?? 0);
                            $in = (float)($in_data[$name] ?? 0);
                            $closing = (float)($closing_data[$name] ?? 0);
                            $consumption = $opening + $in - $closing;

$total_opening += $opening;
                            $total_in += $in;
                            $total_closing += $closing;
                            $total_consumption += $consumption;
                            ?>
                            <tr>
                                <td><?= h($name) ?></td>
                                <td class="text-end"><?= number_format($opening, 3) ?></td>
                                <td class="text-end"><?= number_format($in, 3) ?></td>
                                <td class="text-end"><?= number_format($closing, 3) ?></td>
                                <td class="text-end"><?= number_format($consumption, 3) ?></td>
                            </tr>
                        <?php endforeach; ?>
                        <tr class="<?= $is_print ? 'table-total' : 'table-light fw-bold' ?>">
                            <td>Total</td>
                            <td class="text-end"><?= number_format($total_opening, 3) ?></td>
                            <td class="text-end"><?= number_format($total_in, 3) ?></td>
                            <td class="text-end"><?= number_format($total_closing, 3) ?></td>
                            <td class="text-end"><?= number_format($total_consumption, 3) ?></td>
                        </tr>
                    <?php endif; ?>
                    </tbody>
                </table>
            </div>
        <?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; ?>