View Code - 20260722

rapier_maintenance.php | 20260722_120325.php Initial Domain Snapshot

<?php
declare(strict_types=1);

ini_set('display_errors', '1');
ini_set('display_startup_errors', '1');
error_reporting(E_ALL);

// CSV EXPORT: Handles execution right at the top before any HTML output
if (isset($_GET['export']) && $_GET['export'] === 'csv') {
    require_once __DIR__ . '/modules/auth/page_acl.php';
    $ctx = page_require_access('rapier_maintenance');
    $company_id = (int)($ctx['company_id'] ?? 0);
    $pdo = $ctx['pdo'] ?? null;

if (!$pdo) {
        require_once __DIR__ . '/core/db.php';
    }

$selected_month = $_GET['month'] ?? date('Y-m');
    $period_from = date('Y-m-01', strtotime($selected_month));
    $period_to = date('Y-m-t', strtotime($selected_month));

$rows = [];

// Fetch Rapier department IDs
    $deptStmt = $pdo->prepare("
        SELECT id FROM company_departments
        WHERE company_id = :cid
          AND (
                LOWER(name) = 'rapier'
                OR UPPER(code) = 'RAPI'
              )
    ");
    $deptStmt->execute([':cid' => $company_id]);
    $department_ids = $deptStmt->fetchAll(PDO::FETCH_COLUMN);

if (!empty($department_ids)) {
        $placeholders = implode(',', array_fill(0, count($department_ids), '?'));
        
        $sql = "
            SELECT
                e.employee_code,
                e.name AS employee_name,
                d.name AS department_name,
                COALESCE(a.total_attendance, 0) AS attendance_days
            FROM company_employee_master e
            INNER JOIN company_departments d
                ON d.id = e.department_id
               AND d.company_id = e.company_id
            LEFT JOIN manual_attendance_data a
                ON a.employee_id = e.id
               AND a.company_id = e.company_id
               AND a.period_start <= ?
               AND a.period_end >= ?
            WHERE e.company_id = ?
              AND e.department_id IN ($placeholders)
            ORDER BY e.name ASC
        ";

$params = [$period_to, $period_from, $company_id];
        foreach ($department_ids as $dept_id) {
            $params[] = $dept_id;
        }

$stmt = $pdo->prepare($sql);
        $stmt->execute($params);
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

header('Content-Type: text/csv');
    header('Content-Disposition: attachment; filename=rapier_maintenance_payment_' . $selected_month . '.csv');
    
    $output = fopen('php://output', 'w');
    fputcsv($output, [
        'Employee Code',
        'Employee Name',
        'Department',
        'Pay Type',
        'Rate',
        'Days',
        'Basic',
        'Net Pay'
    ]);

foreach ($rows as $row) {
        $pay_type = 'BANK';
        $rate = 100;
        $days = (float)$row['attendance_days'];
        $basic = $rate * $days;
        $net_pay = $basic;
        
        fputcsv($output, [
            $row['employee_code'],
            $row['employee_name'],
            $row['department_name'],
            $pay_type,
            $rate,
            $days,
            $basic,
            $net_pay
        ]);
    }
    fclose($output);
    exit;
}

// --- NORMAL PAGE RENDER LOGIC ---

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

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

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

if (!$pdo) {
    require_once __DIR__ . '/core/db.php';
}

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

$selected_month = $_GET['month'] ?? date('Y-m');
$rows = [];
$total_amount = 0;

try {
    $deptStmt = $pdo->prepare("
        SELECT id
        FROM company_departments
        WHERE company_id = :cid
          AND (
                LOWER(name) = 'rapier'
                OR UPPER(code) = 'RAPI'
              )
    ");
    $deptStmt->execute([':cid' => $company_id]);
    $department_ids = $deptStmt->fetchAll(PDO::FETCH_COLUMN);

if (!empty($department_ids)) {
        $placeholders = implode(',', array_fill(0, count($department_ids), '?'));

$sql = "
            SELECT
                e.id,
                e.employee_code,
                e.name AS employee_name,
                d.name AS department_name,
                COALESCE(a.total_attendance, 0) AS attendance_days,
                (COALESCE(a.total_attendance, 0) * 100) AS maintenance_amount
            FROM company_employee_master e
            INNER JOIN company_departments d
                ON d.id = e.department_id
               AND d.company_id = e.company_id
            LEFT JOIN manual_attendance_data a
                ON a.employee_id = e.id
               AND a.company_id = e.company_id
               AND a.period_start <= ?
               AND a.period_end >= ?
            WHERE e.company_id = ?
              AND e.department_id IN ($placeholders)
            ORDER BY e.name ASC
        ";

$period_from = date('Y-m-01', strtotime($selected_month));
        $period_to = date('Y-m-t', strtotime($selected_month));
        
        $params = [$period_to, $period_from, $company_id];
        foreach ($department_ids as $dept_id) {
            $params[] = $dept_id;
        }

$stmt = $pdo->prepare($sql);
        $stmt->execute($params);
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

foreach ($rows as $row) {
            $total_amount += (float)$row['maintenance_amount'];
        }
    }
} catch (Throwable $e) {
    echo '<div class="container-fluid py-3">';
    echo '<div class="alert alert-danger">' . h($e->getMessage()) . '</div>';
    echo '</div>';
}
?>

<div class="container-fluid py-3">
    <div class="card shadow-sm border-0 mb-3">
        <div class="card-body">
            <div class="d-flex flex-wrap justify-content-between align-items-center gap-3">
                <div>
                    <h3 class="mb-1">Rapier Maintenance</h3>
                    <div class="text-muted">Formula = Attendance × 100</div>
                </div>

<div class="d-flex flex-wrap align-items-center gap-2">
                    
                    <form method="get" class="d-flex align-items-center gap-2 m-0">
                        <label class="fw-semibold mb-0">Month</label>
                        <input type="month" name="month" value="<?= h($selected_month) ?>" class="form-control">
                        <button type="submit" class="btn btn-primary">Load</button>
                    </form>

<a href="?export=csv&month=<?= h($selected_month) ?>" class="btn btn-success">Export CSV</a>

<form method="post" action="payment_export_rapier_maintenance.php" class="m-0">
                        <input type="hidden" name="month_key" value="<?= h($selected_month) ?>">
                        <button type="submit" class="btn btn-info text-white">Payment Export</button>
                    </form>
                </div>
            </div>
        </div>
    </div>

<div class="card shadow-sm border-0">
        <div class="card-body">
            <div class="d-flex flex-wrap justify-content-between align-items-center mb-3">
                <div class="fw-bold">Employees: <?= count($rows) ?></div>
                <div class="fw-bold text-primary">Total Maintenance: ₹ <?= number_format($total_amount, 2) ?></div>
            </div>

<div class="table-responsive">
                <table class="table table-bordered table-hover align-middle">
                    <thead class="table-dark">
                        <tr>
                            <th width="50">
                                <input type="checkbox" id="select_all" class="form-check-input">
                            </th>
                            <th>#</th>
                            <th>Employee Code</th>
                            <th>Employee Name</th>
                            <th>Department</th>
                            <th class="text-center">Attendance</th>
                            <th class="text-end">Maintenance</th>
                        </tr>
                    </thead>
                    <tbody>
                    <?php if (!empty($rows)): ?>
                        <?php foreach ($rows as $index => $row): ?>
                            <tr>
                                <td>
                                    <input type="checkbox" class="form-check-input row-check" checked>
                                </td>
                                <td><?= $index + 1 ?></td>
                                <td><?= h($row['employee_code']) ?></td>
                                <td class="fw-semibold"><?= h($row['employee_name']) ?></td>
                                <td><?= h($row['department_name']) ?></td>
                                <td class="text-center"><?= (float)$row['attendance_days'] ?></td>
                                <td class="text-end fw-bold text-success">₹ <?= number_format((float)$row['maintenance_amount'], 2) ?></td>
                            </tr>
                        <?php endforeach; ?>
                    <?php else: ?>
                        <tr>
                            <td colspan="7" class="text-center text-muted py-5">No data found.</td>
                        </tr>
                    <?php endif; ?>
                    </tbody>
                    <tfoot>
                        <tr class="table-secondary fw-bold">
                            <td colspan="6" class="text-end">Grand Total</td>
                            <td class="text-end text-primary">₹ <?= number_format($total_amount, 2) ?></td>
                        </tr>
                    </tfoot>
                </table>
            </div>
        </div>
    </div>
</div>

if (selectAll) {
        selectAll.addEventListener('change', function () {
            rowChecks.forEach(cb => cb.checked = this.checked);
        });
    }
});
</script>

<?php
require_once __DIR__ . '/helpers/activity_helper.php';

if (!empty($rows)) {
    // Activity tracker
    activity_create('rapier_maintenance', 'view', 0, 'Rapier maintenance report viewed for ' . $selected_month);
}