View Code - 20260722

monthly_yarn_consumption_report.php | 20260722_120325.php Initial Domain Snapshot

<?php
/* =============================================================================
   File: /erp/jobwork/monthly_yarn_consumption_report.php
   Purpose: Monthly Yarn Consumption Report (Assign Wise Grouping)
   Pattern: Strict Production $ctx page_require_access Pattern
============================================================================= */
header('X-Frame-Options: SAMEORIGIN');
error_reporting(E_ALL); ini_set('display_errors', 1);

// Standard ERP Authorization & Database Access Connection Context
require_once __DIR__ . '/../modules/auth/page_acl.php';
$ctx = page_require_access('va_send_entry'); // Sahi dynamic credentials access ke liye

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

// Load Core System Header
require_once __DIR__ . '/../partials/header.php';

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

/* ===== DATE FILTER INPUTS ===== */
$year  = (int)($_GET['y'] ?? date('Y'));
$month = (int)($_GET['m'] ?? date('n'));

// Pad month matching with SQL schema expectations
$padded_month = sprintf('%02d', $month);

/* ===== SQL QUERY: GROUP BY ASSIGN_TO WITH RETURNS RESOLVED ===== */
try {
    $query = "SELECT 
                IFNULL(NULLIF(TRIM(assign_to), ''), 'Unassigned') AS assign_to_name,
                SUM(CASE WHEN is_return = 0 THEN boxes ELSE -boxes END) AS total_boxes,
                SUM(CASE WHEN is_return = 0 THEN weight ELSE -weight END) AS total_weight
              FROM yarn_out 
              WHERE company_id = :company_id 
                AND MONTH(txn_date) = :month 
                AND YEAR(txn_date) = :year
              GROUP BY assign_to_name
              ORDER BY total_weight DESC";

$stmt = $pdo->prepare($query);
    $stmt->execute([
        ':company_id' => $company_id,
        ':month'      => $padded_month,
        ':year'       => $year
    ]);
    $report_data = $stmt->fetchAll(PDO::FETCH_ASSOC);
} catch (PDOException $e) {
    die("<div class='alert alert-danger m-3'>Query Execution Failed: " . h($e->getMessage()) . "</div>");
}

$months = [
    1 => 'January', 2 => 'February', 3 => 'March', 4 => 'April',
    5 => 'May', 6 => 'June', 7 => 'July', 8 => 'August',
    9 => 'September', 10 => 'October', 11 => 'November', 12 => 'December'
];
?>

/* ================= PRINT OPTIMIZATION (A4 LANDSCAPE) ================= */
    @media print {
        @page {
            size: A4 landscape;
            margin: 8mm;
        }
        body {
            background: #fff !important;
            font-size: 11px;
        }
        .no-print {
            display: none !important;
        }
        .container-fluid, #reportContent {
            width: 100% !important;
            max-width: 100% !important;
            padding: 0 !important;
        }
        .table {
            font-size: 11px !important;
        }
        .table th, .table td {
            padding: 4px 8px !important;
        }
        tr {
            page-break-inside: avoid;
            break-inside: avoid;
        }
    }
</style>

<div class="appbar shadow-sm mb-4 mt-2">
    <div class="container-fluid d-flex justify-content-between align-items-center">
        <div class="h5 mb-0 fw-bold text-uppercase tracking-wider">
            <i class="bi bi-journal-text me-2"></i> Monthly Yarn Consumption Report (Assign Wise)
        </div>
        <div class="d-flex align-items-center gap-3">
            <div class="badge bg-success fs-6 py-2 px-3">
                <i class="bi bi-calendar3 me-2"></i> <?= h($months[$month] . ' ' . $year); ?>
            </div>
            <button type="button" class="btn btn-sm btn-light no-print rounded-pill px-3" onclick="window.print()">
                <i class="bi bi-printer me-1"></i> Print / PDF
            </button>
        </div>
    </div>
</div>

<div class="container-fluid">
    <div class="card shadow-sm border-0 mb-4 no-print">
        <div class="card-body py-2">
            <form method="get" class="d-flex flex-wrap gap-2 align-items-center">
                <div class="w-auto">
                    <select name="m" class="form-select form-select-sm">
                        <?php foreach ($months as $num => $name): ?>
                            <option value="<?= $num; ?>" <?= ($num == $month ? 'selected' : ''); ?>><?= $name; ?></option>
                        <?php endforeach; ?>
                    </select>
                </div>
                <div class="w-auto">
                    <input type="number" name="y" value="<?= $year; ?>" class="form-control form-control-sm" style="width:110px;">
                </div>
                <div class="w-auto">
                    <button type="submit" class="btn btn-primary btn-sm px-4">Show Report</button>
                </div>
            </form>
        </div>
    </div>

<div id="reportContent" class="bg-white p-4 shadow-sm rounded-3 mb-5">
        <div class="table-responsive">
            <table class="table table-hover table-bordered align-middle table-salary mb-0">
                <thead class="table-light text-center">
                    <tr>
                        <th style="width:70px;">#</th>
                        <th class="text-start">Assign To (Machine / Person / Department)</th>
                        <th class="text-end" style="width:250px;">Total Boxes Out</th>
                        <th class="text-end" style="width:250px;">Total Weight Out (Kg)</th>
                    </tr>
                </thead>
                <tbody>
                    <?php 
                    $grand_total_boxes = 0;
                    $grand_total_weight = 0;
                    $i = 1;

if (!empty($report_data)): 
                        foreach ($report_data as $row): 
                            $grand_total_boxes += $row['total_boxes'];
                            $grand_total_weight += $row['total_weight'];
                    ?>
                        <tr>
                            <td class="text-center text-muted small"><?= $i++; ?></td>
                            <td class="fw-bold text-dark text-start"><?= h($row['assign_to_name']); ?></td>
                            <td class="text-end fw-bold"><?= number_format($row['total_boxes'], 3); ?></td>
                            <td class="text-end fw-bold text-primary"><?= number_format($row['total_weight'], 3); ?></td>
                        </tr>
                    <?php 
                        endforeach; 
                    else: 
                    ?>
                        <tr>
                            <td colspan="4" class="text-center py-5 text-muted fs-5">
                                <i class="bi bi-inbox me-2"></i> No active yarn consumption entries linked to this company for this month.
                            </td>
                        </tr>
                    <?php endif; ?>
                </tbody>
                <?php if (!empty($report_data)): ?>
                    <tfoot class="table-dark fw-bold text-end">
                        <tr>
                            <td colspan="2" class="text-center text-uppercase tracking-wider">Grand Total</td>
                            <td><?= number_format($grand_total_boxes, 3); ?></td>
                            <td class="text-warning"><?= number_format($grand_total_weight, 3); ?></td>
                        </tr>
                    </footer>
                <?php endif; ?>
            </table>
        </div>
    </div>
</div>