View Code - 20260721

beam_sent_report.php | 20260721_154032.php Initial Bulk Import

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

/* ================= AUTH ================= */
require_once __DIR__ . '/../modules/auth/page_acl.php';
$ctx = page_require_access('jobwork_send', []);

$pdo        = $ctx['pdo'];
$company_id = (int)$ctx['company_id'];
$u          = $ctx['user'] ?? null;

/* ================= HEADER INCLUDE ================= */
$page_title = "Beam Sent Report";
require_once __DIR__ . '/../partials/header.php';

/* ================= INPUT ================= */
$from = $_GET['from'] ?? date('Y-m-01');
$to   = $_GET['to']   ?? date('Y-m-d');

$from_disp = date('d-m-Y', strtotime($from));
$to_disp   = date('d-m-Y', strtotime($to));

/* ================= MAIN QUERY ================= */
// Select kiya bs.taka column taki dynamic taka count calculate ho sake
$sql = "
SELECT 
    b.id AS beam_sent_id,
    b.challan_no,
    b.challan_date,
    b.party_name,
    b.beam_id,
    COALESCE(bs.beam_no, b.beam_no) AS beam_no,
    COALESCE(bs.meter, b.meter) AS meter,
    COALESCE(bs.taka, 1) AS taka, 
    COALESCE(q.name, 'N/A') AS quality_name,
    q.tar AS quality_tar,
    COALESCE(q.warping_rate, 0.0) AS warping_rate,
    qy.stock_type,
    qy.yarn_type,
    qy.denier AS original_denier,
    COALESCE(ywd.new_denier, qy.denier) AS actual_denier,
    qy.color,
    qy.total_tar,
    qy.wastage
FROM beam_sent b
INNER JOIN company_beam_stock bs 
        ON bs.id = b.beam_id 
       AND bs.company_id = b.company_id
LEFT JOIN beam_qualities q 
       ON q.id = bs.quality_id 
      AND q.company_id = bs.company_id
LEFT JOIN beam_quality_yarns qy 
       ON qy.beam_quality_id = q.id
LEFT JOIN yarn_weight_data ywd
       ON TRIM(LOWER(ywd.stock_type)) COLLATE utf8mb4_general_ci = TRIM(LOWER(qy.stock_type)) COLLATE utf8mb4_general_ci
      AND TRIM(LOWER(ywd.yarn_type)) COLLATE utf8mb4_general_ci = TRIM(LOWER(qy.yarn_type)) COLLATE utf8mb4_general_ci
      AND TRIM(LOWER(ywd.denier)) COLLATE utf8mb4_general_ci = TRIM(LOWER(qy.denier)) COLLATE utf8mb4_general_ci
      AND ywd.company_id = b.company_id
WHERE b.company_id = ?
  AND DATE(b.sent_at) BETWEEN ? AND ?
ORDER BY b.id ASC, qy.seq_no ASC
";

$st = $pdo->prepare($sql);
$st->execute([$company_id, $from, $to]);
$rows = $st->fetchAll(PDO::FETCH_ASSOC);

/* ================= DATA PROCESSING ================= */
$report_data = [];
$yarn_summary = [];
$quality_summary = []; // Quality wise aggregates handle karne ke liye

foreach ($rows as $row) {
    $bs_id = $row['beam_sent_id'];
    $meter = (float)$row['meter'];
    $taka  = (int)$row['taka'];
    $w_rate = (float)$row['warping_rate'];
    
    // Warping Cost = Warping Rate (per taka) * Total Taka in that beam
    $w_cost = $w_rate * $taka; 
    
    if (!isset($report_data[$bs_id])) {
        $report_data[$bs_id] = [
            'challan_no'        => $row['challan_no'],
            'challan_date'      => $row['challan_date'],
            'party_name'        => $row['party_name'],
            'beam_no'           => $row['beam_no'],
            'meter'             => $meter,
            'taka'              => $taka,
            'quality_name'      => $row['quality_name'],
            'warping_rate'      => $w_rate,
            'warping_cost'      => $w_cost,
            'yarns'             => [],
            'beam_total_tar'    => 0.0,
            'beam_total_weight' => 0.0
        ];
        
        // Quality Wise Compilation
        $q_name = $row['quality_name'];
        if (!isset($quality_summary[$q_name])) {
            $quality_summary[$q_name] = [
                'taka'         => 0,
                'meter'        => 0.0,
                'warping_cost' => 0.0
            ];
        }
        $quality_summary[$q_name]['taka']         += $taka;
        $quality_summary[$q_name]['meter']        += $meter;
        $quality_summary[$q_name]['warping_cost'] += $w_cost;
    }
    
    if ($row['yarn_type'] !== null) {
        $denier_numeric = (float)$row['actual_denier'];
        $total_tar = (float)$row['total_tar'];
        
        if ($denier_numeric > 0) {
            $calculated_weight_kg = ($meter * $total_tar * $denier_numeric) / 9000000.0;
        } else {
            $calculated_weight_kg = 0.0;
        }

$report_data[$bs_id]['yarns'][] = [
            'stock_type' => $row['stock_type'],
            'yarn_type'  => $row['yarn_type'],
            'denier'     => $row['actual_denier'], 
            'color'      => $row['color'],
            'total_tar'  => $total_tar,
            'weight_kg'  => $calculated_weight_kg
        ];
        
        $report_data[$bs_id]['beam_total_tar']    += $total_tar;
        $report_data[$bs_id]['beam_total_weight'] += $calculated_weight_kg;
        
        $y_key = $row['stock_type'] . '|' . $row['yarn_type'] . '|' . $row['actual_denier'] . '|' . $row['color'];
        if (!isset($yarn_summary[$y_key])) {
            $yarn_summary[$y_key] = [
                'stock_type' => $row['stock_type'],
                'yarn_type'  => $row['yarn_type'],
                'denier'     => $row['actual_denier'],
                'color'      => $row['color'],
                'weight_kg'  => 0.0
            ];
        }
        $yarn_summary[$y_key]['weight_kg'] += $calculated_weight_kg;
    }
}
?>

<h4 class="fw-bold text-center mb-2">BEAM SENT REPORT</h4>
<div class="text-center fw-bold mb-3">
    Period: <?= htmlspecialchars($from_disp) ?> to <?= htmlspecialchars($to_disp) ?>
</div>

<div class="mb-4 d-print-none">
    <form method="get" class="row g-3 align-items-end">
        <div class="col-md-3">
            <label class="form-label fw-bold">From Date</label>
            <input type="date" name="from" value="<?= htmlspecialchars($from) ?>" class="form-control">
        </div>
        <div class="col-md-3">
            <label class="form-label fw-bold">To Date</label>
            <input type="date" name="to" value="<?= htmlspecialchars($to) ?>" class="form-control">
        </div>
        <div class="col-md-2">
            <button type="submit" class="btn btn-primary w-100">Filter</button>
        </div>
        <div class="col-md-2">
            <button type="button" onclick="window.print()" class="btn btn-secondary w-100">Print</button>
        </div>
    </form>
</div>

<div class="card mb-4">
    <div class="card-header fw-bold bg-light">1️⃣ Beam Sent Details & Warping Cost Metrics</div>
    <div class="card-body table-responsive">
        <table class="table table-bordered table-sm align-middle">
            <thead class="table-light text-center small">
                <tr>
                    <th>Challan No</th>
                    <th>Challan Date</th>
                    <th>Party Name</th>
                    <th>Beam No</th>
                    <th>Quality Name</th>
                    <th>Taka</th>
                    <th>Meter</th>
                    <th class="table-warning">Warping Rate (Per Taka)</th>
                    <th class="table-warning">Warping Charges</th>
                    <th class="table-info">Yarn Stock</th>
                    <th class="table-info">Yarn Type</th>
                    <th class="table-info">Actual Denier</th>
                    <th class="table-info">Color</th>
                    <th class="table-info">Total Tar</th>
                    <th class="table-info">Yarn Weight (KG)</th>
                </tr>
            </thead>
            <tbody>
                <?php 
                $total_beams_count = 0;
                $total_taka_sum = 0;
                $total_meters = 0.0;
                $grand_warping_cost = 0.0;
                $grand_all_yarn_weight = 0.0;
                
                if (empty($report_data)): 
                ?>
                    <tr>
                        <td colspan="15" class="text-center py-3 text-muted">No records found for the selected date range.</td>
                    </tr>
                <?php 
                else:
                    foreach ($report_data as $b_id => $b_row): 
                        $yarn_count = count($b_row['yarns']);
                        $rowspan = $yarn_count > 0 ? $yarn_count + 1 : 1;
                        
                        $total_beams_count++;
                        $total_taka_sum += $b_row['taka'];
                        $total_meters += $b_row['meter'];
                        $grand_warping_cost += $b_row['warping_cost'];
                        $grand_all_yarn_weight += $b_row['beam_total_weight'];
                ?>
                    <tr>
                        <td rowspan="<?= $rowspan ?>" class="text-center fw-bold"><?= htmlspecialchars($b_row['challan_no']) ?></td>
                        <td rowspan="<?= $rowspan ?>" class="text-center"><?= date('d-m-Y', strtotime($b_row['challan_date'])) ?></td>
                        <td rowspan="<?= $rowspan ?>"><?= htmlspecialchars($b_row['party_name']) ?></td>
                        <td rowspan="<?= $rowspan ?>" class="text-center"><?= htmlspecialchars($b_row['beam_no']) ?></td>
                        <td rowspan="<?= $rowspan ?>" class="fw-bold text-primary"><?= htmlspecialchars($b_row['quality_name']) ?></td>
                        <td rowspan="<?= $rowspan ?>" class="text-center fw-bold text-dark"><?= $b_row['taka'] ?></td>
                        <td rowspan="<?= $rowspan ?>" class="text-end fw-bold"><?= number_format($b_row['meter'], 2) ?></td>
                        <td rowspan="<?= $rowspan ?>" class="text-end">₹<?= number_format($b_row['warping_rate'], 2) ?></td>
                        <td rowspan="<?= $rowspan ?>" class="text-end fw-bold text-danger bg-light">₹<?= number_format($b_row['warping_cost'], 2) ?></td>
                        
                        <?php if ($yarn_count > 0): ?>
                            <td class="text-center"><?= htmlspecialchars($b_row['yarns'][0]['stock_type']) ?></td>
                            <td><?= htmlspecialchars($b_row['yarns'][0]['yarn_type']) ?></td>
                            <td class="text-center fw-bold text-success"><?= htmlspecialchars($b_row['yarns'][0]['denier']) ?></td>
                            <td class="text-center"><?= htmlspecialchars($b_row['yarns'][0]['color']) ?></td>
                            <td class="text-end"><?= number_format($b_row['yarns'][0]['total_tar'], 2) ?></td>
                            <td class="text-end fw-bold table-light"><?= number_format($b_row['yarns'][0]['weight_kg'], 3) ?> KG</td>
                        <?php else: ?>
                            <td colspan="6" class="text-center text-muted small">No structural yarn configured for this quality.</td>
                        <?php endif; ?>
                    </tr>
                    
                    <?php 
                    for ($i = 1; $i < $yarn_count; $i++): 
                    ?>
                        <tr>
                            <td class="text-center"><?= htmlspecialchars($b_row['yarns'][$i]['stock_type']) ?></td>
                            <td><?= htmlspecialchars($b_row['yarns'][$i]['yarn_type']) ?></td>
                            <td class="text-center fw-bold text-success"><?= htmlspecialchars($b_row['yarns'][$i]['denier']) ?></td>
                            <td class="text-center"><?= htmlspecialchars($b_row['yarns'][$i]['color']) ?></td>
                            <td class="text-end"><?= number_format($b_row['yarns'][$i]['total_tar'], 2) ?></td>
                            <td class="text-end fw-bold table-light"><?= number_format($b_row['yarns'][$i]['weight_kg'], 3) ?> KG</td>
                        </tr>
                    <?php endfor; ?>

<?php if ($yarn_count > 0): ?>
                        <tr class="table-light fw-bold text-end small border-bottom border-dark">
                            <td colspan="5" class="text-center text-muted text-uppercase small">Challan Yarn Subtotal</td>
                            <td class="text-dark"><?= number_format($b_row['beam_total_tar'], 2) ?></td>
                            <td class="text-primary bg-light"><?= number_format($b_row['beam_total_weight'], 3) ?> KG</td>
                        </tr>
                    <?php endif; ?>
                    
                <?php 
                    endforeach; 
                endif;
                ?>
                
                <tr class="table-secondary fw-bold text-end">
                    <td colspan="3" class="text-center">TOTAL SUMMARY</td>
                    <td class="text-center">Beams: <?= $total_beams_count ?></td>
                    <td></td>
                    <td class="text-center text-primary"><?= $total_taka_sum ?> Taka</td>
                    <td><?= number_format($total_meters, 2) ?> Mtr</td>
                    <td></td>
                    <td class="text-danger">₹<?= number_format($grand_warping_cost, 2) ?></td>
                    <td colspan="5" class="table-light"></td>
                    <td class="text-success"><?= number_format($grand_all_yarn_weight, 3) ?> KG</td>
                </tr>
            </tbody>
        </table>
    </div>
</div>

<div class="card mb-4">
    <div class="card-header fw-bold bg-light">2️⃣ Quality Wise Summary (Taka & Warping Charges)</div>
    <div class="card-body table-responsive">
        <table class="table table-bordered table-sm align-middle">
            <thead class="table-light text-center small">
                <tr>
                    <th>Quality Name</th>
                    <th>Total Taka</th>
                    <th>Total Meters</th>
                    <th>Total Warping Charges</th>
                </tr>
            </thead>
            <tbody>
                <?php if (empty($quality_summary)): ?>
                    <tr>
                        <td colspan="4" class="text-center text-muted py-2">No data available.</td>
                    </tr>
                <?php else: ?>
                    <?php foreach ($quality_summary as $q_name => $q_data): ?>
                        <tr>
                            <td class="fw-bold text-primary px-3"><?= htmlspecialchars($q_name) ?></td>
                            <td class="text-center fw-bold"><?= $q_data['taka'] ?></td>
                            <td class="text-end px-3"><?= number_format($q_data['meter'], 2) ?> Mtr</td>
                            <td class="text-end fw-bold text-danger px-3">₹<?= number_format($q_data['warping_cost'], 2) ?></td>
                        </tr>
                    <?php endforeach; ?>
                    <tr class="table-secondary fw-bold text-end">
                        <td class="text-center">GRAND TOTAL</td>
                        <td class="text-center text-dark"><?= $total_taka_sum ?> Taka</td>
                        <td class="px-3"><?= number_format($total_meters, 2) ?> Mtr</td>
                        <td class="text-danger px-3">₹<?= number_format($grand_warping_cost, 2) ?></td>
                    </tr>
                <?php endif; ?>
            </tbody>
        </table>
    </div>
</div>

<div class="card text-break">
    <div class="card-header fw-bold bg-light">3️⃣ Total Consolidated Yarn Dispatched</div>
    <div class="card-body table-responsive">
        <table class="table table-bordered table-sm align-middle">
            <thead class="table-light text-center small">
                <tr>
                    <th>Stock Type</th>
                    <th>Yarn Type</th>
                    <th>Actual Denier</th>
                    <th>Color</th>
                    <th>Total Dispatched Weight (KG)</th>
                </tr>
            </thead>
            <tbody>
                <?php 
                $grand_yarn_weight = 0.0;
                if (empty($yarn_summary)): 
                ?>
                    <tr>
                        <td colspan="5" class="text-center py-3 text-muted">No compiled yarn configuration details found.</td>
                    </tr>
                <?php 
                else:
                    foreach ($yarn_summary as $summary): 
                        $grand_yarn_weight += $summary['weight_kg'];
                ?>
                    <tr class="text-center">
                        <td><?= htmlspecialchars($summary['stock_type']) ?></td>
                        <td class="text-start"><?= htmlspecialchars($summary['yarn_type']) ?></td>
                        <td class="fw-bold text-success"><?= htmlspecialchars($summary['denier']) ?></td>
                        <td><?= htmlspecialchars($summary['color']) ?></td>
                        <td class="text-end fw-bold text-dark"><?= number_format($summary['weight_kg'], 3) ?> KG</td>
                    </tr>
                <?php 
                    endforeach; 
                endif;
                ?>
                <tr class="fw-bold table-secondary">
                    <td colspan="4" class="text-center">TOTAL CONSOLIDATED YARN WEIGHT</td>
                    <td class="text-end text-success"><?= number_format($grand_yarn_weight, 3) ?> KG</td>
                </tr>
            </tbody>
        </table>
    </div>
</div>

</div>

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