View Code - 20260721

karigar_efficiency_report.php | 20260721_154032.php Initial Bulk Import

<?php
// karigar_efficiency_report.php
// DEBUG-enabled full page with Performance & Total Meter sort option. Remove debug block after use.

// ------------------ TEMP DEBUG BLOCK (REMOVE AFTER USE) ------------------
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);
register_shutdown_function(function(){
    $err = error_get_last();
    if ($err) {
        http_response_code(500);
        echo "<pre style='font-family:monospace;white-space:pre-wrap'>";
        echo "FATAL SHUTDOWN ERROR:\n";
        print_r($err);
        echo "</pre>";
        @file_put_contents('/tmp/php_last_error.log', date('c')." SHUTDOWN: ".print_r($err,true)."\n", FILE_APPEND);
    }
});
set_exception_handler(function($e){
    http_response_code(500);
    echo "<pre style='font-family:monospace'>Uncaught Exception: ".$e->getMessage()."\n".$e->getTraceAsString()."</pre>";
    @file_put_contents('/tmp/php_last_error.log', date('c')." EX: ". $e->getMessage()."\n".$e->getTraceAsString()."\n", FILE_APPEND);
});
// -------------------------------------------------------------------------

// --- Auth & context (project pattern) ---
try {
    require_once __DIR__ . '/modules/auth/page_acl.php';
} catch (Throwable $e) {
    echo "<pre>Could not include page_acl.php: " . htmlspecialchars($e->getMessage(), ENT_QUOTES) . "\nCheck path: " . __DIR__ . "/modules/auth/page_acl.php</pre>";
    exit;
}

if (!function_exists('page_require_access')) {
    echo "<pre>Fatal: function page_require_access() not found. Ensure modules/auth/page_acl.php is correct and doesn't fatal. Path used: " . __DIR__ . "/modules/auth/page_acl.php</pre>";
    exit;
}

$ctx = null;
try {
    $ctx = page_require_access('karigar_efficiency_report');
} catch (Throwable $e) {
    echo "<pre>page_require_access threw: " . htmlspecialchars($e->getMessage(), ENT_QUOTES) . "\n" . htmlspecialchars($e->getTraceAsString(), ENT_QUOTES) . "</pre>";
    exit;
}

$pdo = $ctx['pdo'] ?? null;
$company_id = (int)($ctx['company_id'] ?? 0);
$company_name = $ctx['company_name'] ?? '';

if (!$pdo) {
    echo "<pre>Fatal: PDO missing in context. \$ctx dump:\n" . print_r($ctx, true) . "</pre>";
    exit;
}

if (!function_exists('h')) {
    function h($s){ return htmlspecialchars((string)$s, ENT_QUOTES, 'UTF-8'); }
}

/* Dompdf bootstrap */
$PDF_AVAILABLE = false;
$dompdf_autoloaders = [
    __DIR__ . '/lib/dompdf/autoload.inc.php',
    __DIR__ . '/vendor/autoload.php',
];
foreach ($dompdf_autoloaders as $autoload) {
    if (is_file($autoload)) {
        require_once $autoload;
        if (class_exists(\Dompdf\Dompdf::class)) {
            $PDF_AVAILABLE = true;
            break;
        }
    }
}

// --- Config / thresholds (easy to change) ---
$SINGLE_DAY_THRESHOLD = 50;    // meters threshold to consider single karigar ran full 24h (assumed)
$SHIFT_MINUTES = 720;          // 12 hours in minutes
$EXPORT = $_GET['export'] ?? '';
$PRINT_ONLY = (isset($_GET['print']) && $_GET['print'] == '1');

// --- Sorting param: eff_desc (default), eff_asc, meter_desc, meter_asc ---
$SORT = $_GET['sort'] ?? 'eff_desc';
$allowed_sorts = ['eff_desc','eff_asc','meter_desc','meter_asc'];
if (!in_array($SORT, $allowed_sorts)) $SORT = 'eff_desc';

// --- Input filters ---
$from = $_GET['from'] ?? date('Y-m-01');
$to   = $_GET['to']   ?? date('Y-m-d');
$karigar_filter = !empty($_GET['karigar']) ? (int)$_GET['karigar'] : 0;
$machine_filter = !empty($_GET['machine']) ? (int)$_GET['machine'] : 0;
$quality_filter = !empty($_GET['quality']) ? (int)$_GET['quality'] : 0;

// sanitize dates
$from_dt = date('Y-m-d', strtotime($from));
$to_dt   = date('Y-m-d', strtotime($to));

// --- Load karigar list (for filter) ---
$karigars = [];
try {
    $st = $pdo->prepare("SELECT id, karigar_name FROM loom_karigar_master WHERE company_id = :cid AND is_active = 1 ORDER BY karigar_name");
    $st->execute([':cid'=>$company_id]);
    while($r = $st->fetch(PDO::FETCH_ASSOC)) $karigars[] = $r;
} catch (Throwable $e) {
    // continue
}

// --- Load machines list (for filter) ---
$machines = [];
try {
    $st = $pdo->prepare("SELECT DISTINCT machine_from AS machine_no FROM loom_karigar_master WHERE company_id = :cid ORDER BY machine_from");
    $st->execute([':cid'=>$company_id]);
    while($r = $st->fetch(PDO::FETCH_ASSOC)) $machines[] = $r;
} catch (Throwable $e) {}

// --- Load qualities expected per day ---
$qualities = []; // id => ['name'=>..., 'avg'=>...]
try {
    $st = $pdo->prepare("SELECT id, quality_name, avg_per_day_pro FROM qualities WHERE company_id = :cid");
    $st->execute([':cid'=>$company_id]);
    while($r = $st->fetch(PDO::FETCH_ASSOC)) {
        $qualities[(int)$r['id']] = [
            'name' => $r['quality_name'],
            'avg'  => (float)$r['avg_per_day_pro']
        ];
    }
} catch (Throwable $e) {}

// --- Load production entries ---
$sql = "SELECT id, entry_date, machine_id, quality_id, meter_total, lines_json
        FROM production_entry
        WHERE company_id = :cid
          AND entry_date BETWEEN :from AND :to";

$params = [':cid'=>$company_id, ':from'=>$from_dt, ':to'=>$to_dt];

if ($machine_filter) {
    $sql .= " AND machine_id = :machine";
    $params[':machine'] = $machine_filter;
}
if ($quality_filter) {
    $sql .= " AND quality_id = :quality";
    $params[':quality'] = $quality_filter;
}

$production_rows = [];
try {
    $st = $pdo->prepare($sql);
    $st->execute($params);
    $production_rows = $st->fetchAll(PDO::FETCH_ASSOC);
} catch (Throwable $e) {
    echo "<pre>Error loading production entries: " . h($e->getMessage()) . "</pre>";
    exit;
}

// Build data: per karigar_id => dates => machine => meters
$dates_set = [];
$karigar_map = []; // id => name
$data_by_karigar = []; // [karigar_id][date][machine_no] = meter
$machine_list_used = [];

foreach($production_rows as $row) {
    $entry_date = date('Y-m-d', strtotime($row['entry_date']));
    $dates_set[$entry_date] = true;
    $machine_no = (int)$row['machine_id'];
    $machine_list_used[$machine_no] = true;
    $lj = $row['lines_json'];
    $lines = [];
    if (!empty($lj)) {
        $decoded = json_decode($lj, true);
        if (json_last_error() === JSON_ERROR_NONE && is_array($decoded)) {
            $lines = $decoded;
        } else {
            @file_put_contents('/tmp/php_last_error.log', date('c')." Invalid JSON in production_entry id {$row['id']}: ".json_last_error_msg()."\n", FILE_APPEND);
            $lines = [];
        }
    }
    if (empty($lines)) {
        continue;
    }
    foreach($lines as $ln) {
        $kid = (int)($ln['karigar_id'] ?? 0);
        $meter = (float)($ln['meter'] ?? 0);
        if ($kid <= 0) continue;
        $data_by_karigar[$kid][$entry_date][$machine_no] = ($data_by_karigar[$kid][$entry_date][$machine_no] ?? 0) + $meter;
    }
}

// load karigar names for used karigars
if (!empty($data_by_karigar)) {
    $k_ids = array_keys($data_by_karigar);
    $placeholders = implode(',', array_fill(0, count($k_ids), '?'));
    try {
        $q = $pdo->prepare("SELECT id, karigar_name FROM loom_karigar_master WHERE company_id = ? AND id IN ($placeholders)");
        $params = array_merge([$company_id], $k_ids);
        $q->execute($params);
        while($r = $q->fetch(PDO::FETCH_ASSOC)) {
            $karigar_map[(int)$r['id']] = $r['karigar_name'];
        }
    } catch (Throwable $e) {}
}

// --- Query downtime per machine+date ---
$downtime_map = [];
if (!empty($machine_list_used)) {
    $machine_ids = array_keys($machine_list_used);
    $placeholders = implode(',', array_fill(0, count($machine_ids), '?'));
    $sql = "SELECT machine_no, downtime_date, SUM(duration_minutes) as total_minutes
            FROM loom_downtime
            WHERE company_id = ?
              AND status = 'closed'
              AND downtime_date BETWEEN ? AND ?
              AND machine_no IN ($placeholders)
            GROUP BY machine_no, downtime_date";
    $params = array_merge([$company_id, $from_dt, $to_dt], $machine_ids);
    try {
        $q = $pdo->prepare($sql);
        $q->execute($params);
        while($r = $q->fetch(PDO::FETCH_ASSOC)) {
            $m = (int)$r['machine_no'];
            $d = date('Y-m-d', strtotime($r['downtime_date']));
            $downtime_map[$m][$d] = (float)$r['total_minutes'];
        }
    } catch (Throwable $e) {}
}

// --- Build report rows per karigar ---
$all_dates = array_keys($dates_set);
sort($all_dates);

$report_rows = [];
foreach($data_by_karigar as $kid => $date_map) {
    if ($karigar_filter && $kid !== $karigar_filter) continue;
    $row = [
        'karigar_id' => $kid,
        'karigar_name' => $karigar_map[$kid] ?? ('#'.$kid),
        'per_date' => [],
        'machines' => [],
        'total_meter' => 0.0,
        'expected_total' => 0.0,
        'downtime_adj_expected_total' => 0.0,
        'work_days' => 0
    ];
    foreach($all_dates as $date) {
        $day_total_for_karigar = 0.0;
        $machines_in_date = [];
        if (!empty($date_map[$date])) {
            foreach($date_map[$date] as $machine_no => $meter) {
                $day_total_for_karigar += $meter;
                $machines_in_date[] = $machine_no;
                $row['machines'][$machine_no] = true;
            }
        }
        $date_entries = [];
        foreach($data_by_karigar as $other_kid => $om) {
            if (empty($om[$date])) continue;
            foreach($om[$date] as $mno => $m_meter) {
                $date_entries[$mno][$other_kid] = ($date_entries[$mno][$other_kid] ?? 0) + $m_meter;
            }
        }

$date_expected = 0.0;
        $date_meter = 0.0;
        $date_downtime_total_minutes = 0.0;

if (!empty($machines_in_date)) {
            foreach($machines_in_date as $machine_no) {
                $karigar_meter_on_machine = (float)($date_map[$date][$machine_no] ?? 0);
                $machine_total_that_day = 0.0;
                if (!empty($date_entries[$machine_no])) {
                    foreach($date_entries[$machine_no] as $mk => $mv) $machine_total_that_day += $mv;
                }

$quality_id = 0;
                try {
                    $q = $pdo->prepare("SELECT quality_id FROM production_entry WHERE company_id = :cid AND entry_date = :d AND machine_id = :m LIMIT 1");
                    $q->execute([':cid'=>$company_id, ':d'=>$date, ':m'=>$machine_no]);
                    $qual_row = $q->fetch(PDO::FETCH_ASSOC);
                    $quality_id = $qual_row ? (int)$qual_row['quality_id'] : ($quality_filter ?: 0);
                } catch (Throwable $e) {
                    $quality_id = $quality_filter ?: 0;
                }
                $quality_avg = ($qualities[$quality_id]['avg'] ?? 0.0);

$downtime_minutes = (float)($downtime_map[$machine_no][$date] ?? 0.0);

if ($downtime_minutes >= $SHIFT_MINUTES || $machine_total_that_day <= 0) {
                    continue;
                }

$others_total = $machine_total_that_day - $karigar_meter_on_machine;
                $is_single_full_day = ($karigar_meter_on_machine >= $SINGLE_DAY_THRESHOLD && $others_total <= 0.0001);

if ($is_single_full_day) {
                    $base_expected = $quality_avg;
                } else {
                    $base_expected = $quality_avg / 2.0;
                }

$working_minutes = max(0, $SHIFT_MINUTES - $downtime_minutes);
                if ($working_minutes <= 0) continue;

$adjusted_expected = $base_expected * ($working_minutes / $SHIFT_MINUTES);

$date_expected += $adjusted_expected;
                $date_meter += $karigar_meter_on_machine;
                $date_downtime_total_minutes += $downtime_minutes;
            }
        }

if ($date_meter <= 0) {
            $row['per_date'][$date] = ['meter'=>0.0, 'expected'=>0.0, 'downtime'=>0.0, 'skip'=>true];
            continue;
        }

if ($date_expected <= 0) {
            $row['per_date'][$date] = ['meter'=>$date_meter, 'expected'=>0.0, 'downtime'=>$date_downtime_total_minutes, 'skip'=>true];
            continue;
        }

$row['work_days'] += 1;
        $row['total_meter'] += $date_meter;
        $row['expected_total'] += $date_expected;
        $row['downtime_adj_expected_total'] += $date_expected;
        $row['per_date'][$date] = ['meter'=>$date_meter, 'expected'=>$date_expected, 'downtime'=>$date_downtime_total_minutes, 'skip'=>false];
    }
    if ($row['work_days'] > 0) {
        $row['efficiency'] = $row['expected_total'] > 0 ? ($row['total_meter'] / $row['expected_total']) * 100.0 : 0.0;
        $report_rows[] = $row;
    }
}

// --- Sorting by chosen sort (efficiency or total_meter) ---
if (!empty($report_rows)) {
    usort($report_rows, function($a, $b) use ($SORT) {
        if (strpos($SORT, 'meter_') === 0) {
            $ma = $a['total_meter'] ?? 0;
            $mb = $b['total_meter'] ?? 0;
            if ($ma == $mb) return 0;
            if ($SORT === 'meter_desc') {
                return ($ma < $mb) ? 1 : -1; // high to low
            } else {
                return ($ma > $mb) ? 1 : -1; // low to high
            }
        } else {
            $ea = $a['efficiency'] ?? 0;
            $eb = $b['efficiency'] ?? 0;
            if ($ea == $eb) return 0;
            if ($SORT === 'eff_desc') {
                return ($ea < $eb) ? 1 : -1; // high to low
            } else {
                return ($ea > $eb) ? 1 : -1; // low to high
            }
        }
    });
}

// --- Handle CSV export ---
if ($EXPORT === 'csv') {
    header('Content-Type: text/csv; charset=utf-8');
    header('Content-Disposition: attachment; filename=karigar_efficiency_' . date('Ymd_His') . '.csv');
    $out = fopen('php://output', 'w');

$date_headers = $all_dates;
    $cols = array_merge(['Karigar', 'Machines', 'Work Days', 'Total Meter', 'Expected Total', 'Performance %'], $date_headers);
    fputcsv($out, $cols);

foreach($report_rows as $r) {
        $machines_list = implode(',', array_keys($r['machines']));
        $base = [
            $r['karigar_name'],
            $machines_list,
            $r['work_days'],
            round($r['total_meter'], 2),
            round($r['expected_total'], 2),
            round($r['efficiency'], 2),
        ];
        foreach($date_headers as $d) {
            $cell = $r['per_date'][$d] ?? null;
            if (!$cell || $cell['skip']) $base[] = '';
            else $base[] = round($cell['meter'],2);
        }
        fputcsv($out, $base);
    }
    fclose($out);
    exit;
}

// --- PDF export ---
if ($EXPORT === 'pdf') {
        if (!$PDF_AVAILABLE) {
                http_response_code(503);
                header('Content-Type: text/html; charset=UTF-8');
                echo '<h3>PDF export unavailable</h3><p>Dompdf is not installed on this server.</p>';
                exit;
        }

ob_start();
        ?>
        <!doctype html>
        <html>
        <head>
            <meta charset="utf-8">
            <title>Karigar Performance Report PDF</title>
            <style>
                @page { margin: 10px 10px; }
                body { margin: 0; font-family: DejaVu Sans, sans-serif; font-size: 7px; color: #111; }
                h1 { margin: 0 0 4px; font-size: 11px; }
                .subhead { margin: 0 0 8px; color: #555; font-size: 8px; }
                table { width: 100%; border-collapse: collapse; table-layout: fixed; margin-bottom: 12px; }
                th, td { border: 1px solid #d9d9d9; padding: 3px 2px; vertical-align: middle; word-break: break-word; }
                th { background: #f3f4f6; font-size: 6px; }
                td { font-size: 7px; }
            </style>
        </head>
        <body>
            <h1>Karigar Performance Report</h1>
            <div class="subhead">Company: <?= h($company_name) ?> | Period: <?= h($from_dt) ?> - <?= h($to_dt) ?></div>

<?php if (!empty($report_rows)): ?>
                <table>
                    <thead>
                        <tr>
                            <th>Karigar</th>
                            <th>Machines</th>
                            <th>Work Days</th>
                            <th>Total Meter</th>
                            <th>Expected Total</th>
                            <th>Performance %</th>
                            <?php foreach($all_dates as $date): ?>
                                <th><?= h($date) ?></th>
                            <?php endforeach; ?>
                        </tr>
                    </thead>
                    <tbody>
                        <?php foreach($report_rows as $r): ?>
                            <tr>
                                <td><?= h($r['karigar_name']) ?></td>
                                <td><?= h(implode(',', array_keys($r['machines']))) ?></td>
                                <td><?= h($r['work_days']) ?></td>
                                <td><?= h(round($r['total_meter'], 2)) ?></td>
                                <td><?= h(round($r['expected_total'], 2)) ?></td>
                                <td><?= h(round($r['efficiency'], 2)) ?></td>
                                <?php foreach($all_dates as $date):
                                        $cell = $r['per_date'][$date] ?? null;
                                ?>
                                    <td><?= (!$cell || $cell['skip']) ? '' : h(round($cell['meter'], 2)) ?></td>
                                <?php endforeach; ?>
                            </tr>
                        <?php endforeach; ?>
                    </tbody>
                </table>
            <?php else: ?>
                <p>No entries for selected dates.</p>
            <?php endif; ?>
        </body>
        </html>
        <?php
        $pdfHtml = ob_get_clean();

$dompdf = new \Dompdf\Dompdf([
                'isRemoteEnabled' => false,
                'defaultFont' => 'DejaVu Sans',
        ]);
        $dompdf->loadHtml($pdfHtml, 'UTF-8');
        $dompdf->setPaper('A4', 'portrait');
        $dompdf->render();
        $filename = 'karigar_efficiency_' . preg_replace('/[^0-9-]/', '', $from_dt) . '_to_' . preg_replace('/[^0-9-]/', '', $to_dt) . '.pdf';
        $dompdf->stream($filename, ['Attachment' => true]);
    exit;
}

// --- If not print-only, include header.php (project standard) ---
if (!$PRINT_ONLY) {
        $header_path = __DIR__ . '/partials/header.php';
    if (file_exists($header_path)) require_once $header_path;
    else {
        echo "<pre>Warning: header.php not found at $header_path — continuing without header.</pre>";
    }
}

// --- Render HTML ---
?>
<div class="card">
  <div class="card-header">
    <h3>Karigar Performance Report</h3>
    <div class="card-sub">Company: <?= h($company_name) ?> | Period: <?= h($from_dt) ?> — <?= h($to_dt) ?></div>
  </div>
  <div class="card-body">
    <form method="get" class="form-inline" style="margin-bottom:10px;">
      <input type="hidden" name="page" value="karigar_efficiency_report" />
      From: <input type="date" name="from" value="<?= h($from_dt) ?>" class="form-control" />
      To: <input type="date" name="to" value="<?= h($to_dt) ?>" class="form-control" />
      Karigar:
      <select name="karigar" class="form-control">
        <option value="">--All--</option>
        <?php foreach($karigars as $k): ?>
          <option value="<?= (int)$k['id'] ?>" <?= $karigar_filter== (int)$k['id'] ? 'selected':'' ?>><?= h($k['karigar_name']) ?></option>
        <?php endforeach; ?>
      </select>
      Machine:
      <select name="machine" class="form-control">
        <option value="">--All--</option>
        <?php foreach($machines as $m): ?>
          <option value="<?= (int)$m['machine_no'] ?>" <?= $machine_filter == (int)$m['machine_no'] ? 'selected':'' ?>><?= h($m['machine_no']) ?></option>
        <?php endforeach; ?>
      </select>
      Quality:
      <select name="quality" class="form-control">
        <option value="">--All--</option>
        <?php foreach($qualities as $qid=>$q): ?>
          <option value="<?= (int)$qid ?>" <?= $quality_filter==(int)$qid ? 'selected':'' ?>><?= h($q['name']) ?></option>
        <?php endforeach; ?>
      </select>

Sort by:
      <select name="sort" class="form-control">
        <option value="eff_desc" <?= $SORT==='eff_desc' ? 'selected':'' ?>>Performance: High → Low</option>
        <option value="eff_asc"  <?= $SORT==='eff_asc'  ? 'selected':'' ?>>Performance: Low → High</option>
        <option value="meter_desc"  <?= $SORT==='meter_desc'  ? 'selected':'' ?>>Total Meter: High → Low</option>
        <option value="meter_asc"   <?= $SORT==='meter_asc'   ? 'selected':'' ?>>Total Meter: Low → High</option>
      </select>

<button class="btn" type="submit">Filter</button>
      <a class="btn" href="?<?= http_build_query(array_merge($_GET, ['export'=>'csv'])) ?>">Export CSV</a>
      <a class="btn" href="?<?= http_build_query(array_merge($_GET, ['export'=>'pdf'])) ?>">Export PDF</a>
      <a class="btn" target="_blank" href="?<?= http_build_query(array_merge($_GET, ['print'=>'1'])) ?>">Print</a>
    </form>

<div class="table-responsive">
      <table class="table table-sm table-striped table-bordered table-hover">
        <thead class="table-light">
          <tr>
            <th>Karigar</th>
            <th>Machines</th>
            <th>Work Days</th>
            <th>Total Meter</th>
            <th>Expected</th>
            <th>Performance %</th>
            <?php foreach($all_dates as $d): ?><th><?= h($d) ?></th><?php endforeach; ?>
          </tr>
        </thead>
        <tbody>
          <?php if (empty($report_rows)): ?>
            <tr><td colspan="<?= 6 + count($all_dates) ?>">No data found for selected filters.</td></tr>
          <?php else: ?>
            <?php foreach($report_rows as $r): ?>
              <tr>
                <td><?= h($r['karigar_name']) ?></td>
                <td><?= h(implode(', ', array_keys($r['machines']))) ?></td>
                <td><?= (int)$r['work_days'] ?></td>
                <td><?= round($r['total_meter'],2) ?></td>
                <td><?= round($r['expected_total'],2) ?></td>
                <td><?= round($r['efficiency'],2) ?>%</td>
                <?php foreach($all_dates as $d):
                    $cell = $r['per_date'][$d] ?? null;
                    if (!$cell || $cell['skip']): ?>
                      <td class="text-muted">-</td>
                    <?php else: ?>
                      <td><?= round($cell['meter'],2) ?></td>
                    <?php endif;
                endforeach; ?>
              </tr>
            <?php endforeach; ?>
          <?php endif; ?>
        </tbody>
      </table>
    </div>
  </div>
</div>

<?php
if ($PRINT_ONLY) {
    echo '<script>window.print(); setTimeout(()=>window.close(),1000);</script>';
    exit;
}

$footer_path = __DIR__ . '/../partials/footer.php';
if (file_exists($footer_path)) require_once $footer_path;
else {
    echo "";
}