View Code - 20260722

mesr_yarn_in_running_beam.php | 20260722_120325.php Initial Domain Snapshot

<?php
/* /erp/mesr/mesr_beam_snapshot_processor.php */
require_once __DIR__ . '/../modules/auth/page_acl.php';
$ctx = page_require_access('beam_to_yarn');

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

// Constants as per your requirement
$default_location_id = 14;
$default_machine_id  = 9;

$message = "";
$results = [];
$summary = [];
$yarn_req = [];
$selected_month = $_POST['process_month'] ?? date('Y-m');

/* --- Helper to handle nulls in htmlspecialchars --- */
function h($s) {
    return htmlspecialchars((string)($s ?? ''), ENT_QUOTES, 'UTF-8');
}

/* --- LOGIC: Calculation Function --- */
function getProcessedData($pdo, $company_id) {
    // 1. Get Snapshots from mesr_running_beam_snapshot
    $sql = "SELECT machine_no, primary_running_beam, secondary_running_beam FROM mesr_running_beam_snapshot WHERE company_id = ?";
    $stmt = $pdo->prepare($sql);
    $stmt->execute([$company_id]);
    $snapshots = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    $beam_details = [];
    $quality_summary = [];

foreach ($snapshots as $snap) {
        $beams = array_filter([$snap['primary_running_beam'], $snap['secondary_running_beam']]);
        foreach ($beams as $b_no) {
            $st = $pdo->prepare("SELECT b.beam_no, b.meter, bq.name as quality_name, b.beam_quality 
                                 FROM company_beam_stock b 
                                 LEFT JOIN beam_qualities bq ON b.beam_quality = bq.id 
                                 WHERE b.company_id=? AND b.beam_no=? LIMIT 1");
            $st->execute([$company_id, $b_no]);
            $b = $st->fetch(PDO::FETCH_ASSOC);
            if (!$b) continue;

// Production Calc from Life Cycle
            $lc_stmt = $pdo->prepare("SELECT life_events FROM beam_life_cycle WHERE company_id=? AND beam_no=?");
            $lc_stmt->execute([$company_id, $b_no]);
            $events = json_decode($lc_stmt->fetchColumn() ?: '[]', true);
            
            $produced = 0;
            foreach ($events as $idx => $ev) {
                $to = isset($events[$idx + 1]) ? $events[$idx + 1]['load_date'] : date('Y-m-d');
                $prod_st = $pdo->prepare("SELECT SUM(meter_total) FROM production_entry WHERE company_id=? AND machine_id=? AND entry_date BETWEEN ? AND ?");
                $prod_st->execute([$company_id, $ev['machine_no'], $ev['load_date'], $to]);
                $produced += (float)$prod_st->fetchColumn();
            }
            
            $pending = max(0, (float)$b['meter'] - $produced);
            
            $beam_details[] = [
                'machine' => $snap['machine_no'],
                'beam_no' => $b['beam_no'],
                'quality' => $b['quality_name'],
                'quality_id' => $b['beam_quality'],
                'total' => (float)$b['meter'],
                'produced' => $produced,
                'pending' => $pending
            ];

// Summary Grouping by Quality
            $qid = $b['beam_quality'];
            if (!isset($quality_summary[$qid])) {
                $quality_summary[$qid] = ['name' => $b['quality_name'], 'pending_sum' => 0];
            }
            $quality_summary[$qid]['pending_sum'] += $pending;
        }
    }

// 2. Yarn Requirement Calculation based on Quality-wise Sum
    $yarn_summary = [];
    $st_weight = $pdo->query("SELECT stock_type, yarn_type, denier, new_denier FROM yarn_weight_data")->fetchAll(PDO::FETCH_ASSOC);

foreach ($quality_summary as $qid => $qdata) {
        $yarns = $pdo->prepare("SELECT stock_type, yarn_type, denier, color, total_tar FROM beam_quality_yarns WHERE beam_quality_id = ?");
        $yarns->execute([$qid]);
        
        foreach ($yarns->fetchAll(PDO::FETCH_ASSOC) as $y) {
            $new_denier = (float)$y['denier'];
            foreach($st_weight as $sw) {
                if(strtolower($sw['stock_type']) == strtolower($y['stock_type'] ?? '') && (float)$sw['denier'] == (float)$y['denier']) {
                    $new_denier = (float)$sw['new_denier'];
                    break;
                }
            }
            // Formula: (Pending Meter Sum * Tar * Denier) / 9,000,000
            $weight = (($qdata['pending_sum'] * (float)$y['total_tar']) * $new_denier) / 9000000.0;
            
            $key = $y['denier'] . '|' . $y['color'];
            if (!isset($yarn_summary[$key])) {
                $yarn_summary[$key] = ['denier' => $y['denier'], 'color' => $y['color'], 'weight' => 0];
            }
            $yarn_summary[$key]['weight'] += $weight;
        }
    }

return ['details' => $beam_details, 'summary' => $quality_summary, 'yarn' => $yarn_summary];
}

/* --- ACTIONS --- */
if (isset($_POST['btn_calculate'])) {
    $data = getProcessedData($pdo, $company_id);
    $results = $data['details'];
    $summary = $data['summary'];
    $yarn_req = $data['yarn'];
}

if (isset($_POST['btn_save'])) {
    $target_month = $_POST['process_month'] . '-01';
    $data = getProcessedData($pdo, $company_id);
    
    try {
        $pdo->beginTransaction();
        
        // Delete previous entries for this month (Delete-then-Insert)
        $pdo->prepare("DELETE FROM mesr_yarn_in_running_beam WHERE company_id = ? AND month = ?")
            ->execute([$company_id, $target_month]);

foreach ($data['yarn'] as $y) {
            // SQL with auto-filled location_id (14) and machine_id (9)
            $ins = $pdo->prepare("INSERT INTO mesr_yarn_in_running_beam 
                (company_id, month, location_id, machine_id, denier, color, net_weight, total_cons, created_at) 
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, NOW())");
            
            $ins->execute([
                $company_id, 
                $target_month, 
                $default_location_id, // 14
                $default_machine_id,  // 9
                $y['denier'], 
                $y['color'], 
                round($y['weight'], 4), 
                round($y['weight'], 4)
            ]);
        }
        $pdo->commit();
        $message = "Success: Data saved for " . h($target_month) . " with Location 14 and Machine 9.";
    } catch(Exception $e) {
        $pdo->rollBack();
        $message = "Error: " . $e->getMessage();
    }
}

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

<div class="container py-4">
    <div class="card mb-4 shadow-sm">
        <div class="card-body bg-light">
            <form method="POST" class="row g-3 align-items-end">
                <div class="col-md-3">
                    <label class="form-label fw-bold">Processing Month</label>
                    <input type="month" name="process_month" class="form-control" value="<?= h($selected_month) ?>">
                </div>
                <div class="col-md-3">
                    <button type="submit" name="btn_calculate" class="btn btn-primary w-100">Calculate & Preview</button>
                </div>
            </form>
        </div>
    </div>

<?php if ($message): ?>
        <div class="alert alert-success border-0 shadow-sm"><?= h($message) ?></div>
    <?php endif; ?>

<?php if (!empty($results)): ?>
    <div class="card shadow-sm mb-4">
        <div class="card-header bg-dark text-white">Detailed Running Beams (Snapshot)</div>
        <div class="table-responsive">
            <table class="table table-sm table-bordered mb-0">
                <thead class="table-secondary">
                    <tr><th>M/c</th><th>Beam No</th><th>Quality</th><th class="text-end">Total</th><th class="text-end">Produced</th><th class="text-end">Pending</th></tr>
                </thead>
                <tbody>
                    <?php foreach($results as $r): ?>
                    <tr>
                        <td><?= h($r['machine']) ?></td>
                        <td class="fw-bold"><?= h($r['beam_no']) ?></td>
                        <td><?= h($r['quality']) ?></td>
                        <td class="text-end"><?= number_format($r['total'], 2) ?></td>
                        <td class="text-end"><?= number_format($r['produced'], 2) ?></td>
                        <td class="text-end text-danger fw-bold"><?= number_format($r['pending'], 2) ?></td>
                    </tr>
                    <?php endforeach; ?>
                </tbody>
            </table>
        </div>
    </div>

<div class="row">
        <div class="col-md-5">
            <div class="card shadow-sm mb-4">
                <div class="card-header bg-info text-white">Quality-wise Aggregation</div>
                <table class="table table-bordered mb-0">
                    <thead><tr><th>Quality</th><th class="text-end">Pending Sum</th></tr></thead>
                    <tbody>
                        <?php foreach($summary as $s): ?>
                        <tr><td><?= h($s['name']) ?></td><td class="text-end fw-bold"><?= number_format($s['pending_sum'], 2) ?></td></tr>
                        <?php endforeach; ?>
                    </tbody>
                </table>
            </div>
        </div>

<div class="col-md-7">
            <div class="card shadow-sm mb-4 border-success">
                <div class="card-header bg-success text-white">Yarn to be Inserted (Loc: 14, M/c: 9)</div>
                <table class="table table-bordered mb-0">
                    <thead><tr><th>Denier</th><th>Color</th><th class="text-end">Net Weight (kg)</th></tr></thead>
                    <tbody>
                        <?php foreach($yarn_req as $y): ?>
                        <tr><td><?= h($y['denier']) ?></td><td><?= h($y['color']) ?></td><td class="text-end fw-bold text-success"><?= number_format($y['weight'], 4) ?></td></tr>
                        <?php endforeach; ?>
                    </tbody>
                </table>
                <div class="card-footer text-end">
                    <form method="POST">
                        <input type="hidden" name="process_month" value="<?= h($selected_month) ?>">
                        <button type="submit" name="btn_save" class="btn btn-success px-5 fw-bold">Confirm & Save to Database</button>
                    </form>
                </div>
            </div>
        </div>
    </div>
    <?php endif; ?>
</div>

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