prepare($insert_sql); $stmt->execute([ ':sapCode' => $_POST['sapCode'], ':fullNameEn' => $_POST['fullNameEn'], ':legalEntity' => $_POST['legalEntity'] ?? null, ':functionBusinessUnit' => $_POST['functionBusinessUnit'] ?? null, ':costCenterCode' => $_POST['costCenterCode'] ?? null, ':level' => $_POST['level'] ?? null, ':newAmendedSalary' => $newAmendedSalary, ':employerContributions' => $employerContributions, ':cars' => $cars, ':ticketRestaurant' => $ticketRestaurant, ':metlife' => $metlife, ':topusPerMonth' => $topusPerMonth, ':totalSalaryCostWithLabor' => $totalSalaryCostWithLabor, ':totalMonthlyCost' => $totalMonthlyCost, ':totalAnnualCost' => $totalAnnualCost, ':grossRevenue' => $grossRevenue ]); // To prevent form resubmission on refresh, redirect header("Location: " . $_SERVER['PHP_SELF']); exit(); } catch (PDOException $e) { // Check for duplicate entry if ($e->errorInfo[1] == 1062) { $form_error = "Error: A resource with this SAP Code already exists."; } else { $form_error = "Database error: " . $e->getMessage(); } } } } if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['action']) && $_POST['action'] === 'delete_roster') { try { require_once __DIR__ . '/db/config.php'; $pdo_delete = db(); $delete_sql = "DELETE FROM roster WHERE id = :id"; $stmt = $pdo_delete->prepare($delete_sql); $stmt->execute([':id' => $_POST['id']]); header("Location: " . $_SERVER['PHP_SELF']); exit(); } catch (PDOException $e) { $form_error = "Database error: " . $e->getMessage(); } } if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['action']) && $_POST['action'] === 'update_roster') { if (empty($_POST['id']) || empty($_POST['sapCode']) || empty($_POST['fullNameEn'])) { $form_error = "ID, SAP Code, and Full Name are required for an update."; } else { try { require_once __DIR__ . '/db/config.php'; $pdo_update = db(); // Prepare data from form $newAmendedSalary = (float)($_POST['newAmendedSalary'] ?? 0); $employerContributions = (float)($_POST['employerContributions'] ?? 0); $cars = (float)($_POST['cars'] ?? 0); $ticketRestaurant = (float)($_POST['ticketRestaurant'] ?? 0); $metlife = (float)($_POST['metlife'] ?? 0); $topusPerMonth = (float)($_POST['topusPerMonth'] ?? 0); $grossRevenue = (float)($_POST['grossRevenue'] ?? 0); // Auto-calculations $totalSalaryCostWithLabor = $newAmendedSalary + $employerContributions; $totalMonthlyCost = $totalSalaryCostWithLabor + $cars + $ticketRestaurant + $metlife + $topusPerMonth; $totalAnnualCost = $totalMonthlyCost * 14; $update_sql = "UPDATE roster SET sapCode = :sapCode, fullNameEn = :fullNameEn, legalEntity = :legalEntity, functionBusinessUnit = :functionBusinessUnit, costCenterCode = :costCenterCode, `level` = :level, newAmendedSalary = :newAmendedSalary, employerContributions = :employerContributions, cars = :cars, ticketRestaurant = :ticketRestaurant, metlife = :metlife, topusPerMonth = :topusPerMonth, totalSalaryCostWithLabor = :totalSalaryCostWithLabor, totalMonthlyCost = :totalMonthlyCost, totalAnnualCost = :totalAnnualCost, grossRevenue = :grossRevenue WHERE id = :id"; $stmt = $pdo_update->prepare($update_sql); $stmt->execute([ ':id' => $_POST['id'], ':sapCode' => $_POST['sapCode'], ':fullNameEn' => $_POST['fullNameEn'], ':legalEntity' => $_POST['legalEntity'] ?? null, ':functionBusinessUnit' => $_POST['functionBusinessUnit'] ?? null, ':costCenterCode' => $_POST['costCenterCode'] ?? null, ':level' => $_POST['level'] ?? null, ':newAmendedSalary' => $newAmendedSalary, ':employerContributions' => $employerContributions, ':cars' => $cars, ':ticketRestaurant' => $ticketRestaurant, ':metlife' => $metlife, ':topusPerMonth' => $topusPerMonth, ':totalSalaryCostWithLabor' => $totalSalaryCostWithLabor, ':totalMonthlyCost' => $totalMonthlyCost, ':totalAnnualCost' => $totalAnnualCost, ':grossRevenue' => $grossRevenue ]); header("Location: " . $_SERVER['PHP_SELF']); exit(); } catch (PDOException $e) { if ($e->errorInfo[1] == 1062) { $form_error = "Error: A resource with this SAP Code already exists."; } else { $form_error = "Database error: " . $e->getMessage(); } } } } // --- DATABASE INITIALIZATION --- require_once __DIR__ . '/db/config.php'; function execute_sql_from_file($pdo, $filepath) { try { $sql = file_get_contents($filepath); $pdo->exec($sql); return true; } catch (PDOException $e) { if (strpos($e->getMessage(), 'already exists') === false) { error_log("SQL Execution Error: " . $e->getMessage()); } return false; } } function seed_roster_data($pdo) { try { $stmt = $pdo->query("SELECT COUNT(*) FROM roster"); if ($stmt->fetchColumn() > 0) { return; // Data already exists } $seed_data = [ [ 'sapCode' => '1001', 'fullNameEn' => 'John Doe', 'legalEntity' => 'Entity A', 'functionBusinessUnit' => 'Finance', 'costCenterCode' => 'CC100', 'level' => 'Senior', 'newAmendedSalary' => 6000, 'employerContributions' => 1500, 'cars' => 500, 'ticketRestaurant' => 150, 'metlife' => 50, 'topusPerMonth' => 100 ], [ 'sapCode' => '1002', 'fullNameEn' => 'Jane Smith', 'legalEntity' => 'Entity B', 'functionBusinessUnit' => 'IT', 'costCenterCode' => 'CC200', 'level' => 'Manager', 'newAmendedSalary' => 8000, 'employerContributions' => 2000, 'cars' => 600, 'ticketRestaurant' => 150, 'metlife' => 60, 'topusPerMonth' => 120 ], ]; $insert_sql = "INSERT INTO roster (sapCode, fullNameEn, legalEntity, functionBusinessUnit, costCenterCode, `level`, newAmendedSalary, employerContributions, cars, ticketRestaurant, metlife, topusPerMonth, totalSalaryCostWithLabor, totalMonthlyCost, totalAnnualCost) VALUES (:sapCode, :fullNameEn, :legalEntity, :functionBusinessUnit, :costCenterCode, :level, :newAmendedSalary, :employerContributions, :cars, :ticketRestaurant, :metlife, :topusPerMonth, :totalSalaryCostWithLabor, :totalMonthlyCost, :totalAnnualCost)"; $stmt = $pdo->prepare($insert_sql); foreach ($seed_data as $row) { $totalSalaryCostWithLabor = $row['newAmendedSalary'] + $row['employerContributions']; $totalMonthlyCost = $totalSalaryCostWithLabor + $row['cars'] + $row['ticketRestaurant'] + $row['metlife'] + $row['topusPerMonth']; $totalAnnualCost = $totalMonthlyCost * 14; $stmt->execute([ ':sapCode' => $row['sapCode'], ':fullNameEn' => $row['fullNameEn'], ':legalEntity' => $row['legalEntity'], ':functionBusinessUnit' => $row['functionBusinessUnit'], ':costCenterCode' => $row['costCenterCode'], ':level' => $row['level'], ':newAmendedSalary' => $row['newAmendedSalary'], ':employerContributions' => $row['employerContributions'], ':cars' => $row['cars'], ':ticketRestaurant' => $row['ticketRestaurant'], ':metlife' => $row['metlife'], ':topusPerMonth' => $row['topusPerMonth'], ':totalSalaryCostWithLabor' => $totalSalaryCostWithLabor, ':totalMonthlyCost' => $totalMonthlyCost, ':totalAnnualCost' => $totalAnnualCost ]); } } catch (PDOException $e) { error_log("Seeding Error: " . $e->getMessage()); } } $roster_data = []; $search_term = $_GET['search'] ?? ''; try { $pdo = db(); // Apply all migrations $migration_files = glob(__DIR__ . '/db/migrations/*.sql'); sort($migration_files); foreach ($migration_files as $file) { execute_sql_from_file($pdo, $file); } seed_roster_data($pdo); $sql = "SELECT * FROM roster"; $params = []; if (!empty($search_term)) { $sql .= " WHERE fullNameEn LIKE :search OR sapCode LIKE :search"; $params[':search'] = '%' . $search_term . '%'; } $sql .= " ORDER BY fullNameEn"; $stmt = $pdo->prepare($sql); $stmt->execute($params); $roster_data = $stmt->fetchAll(PDO::FETCH_ASSOC); } catch (PDOException $e) { $db_error = "Database connection failed: " . $e->getMessage(); } // --- RENDER PAGE --- ?> Project Financials
Project Financials
{$message}
"; } ?>

Roster

Export Excel
Clear
SAP Code Full Name Level Legal Entity Function Cost Center Salary (€) Employer Contributions (€) Total Salary Cost (€) Cars (€) Metlife (€) Ticket Restaurant (€) Bonus/Month (€) Daily Cost (€) Total Monthly Cost (€) Total Annual Cost (€) Gross Revenue (€) Actions
No roster data found.
'> € € € € € € € € € € €