265 lines
12 KiB
PHP
265 lines
12 KiB
PHP
<?php
|
|
require_once __DIR__ . '/db/config.php';
|
|
|
|
$projectId = $_GET['projectId'] ?? null;
|
|
$versionNumber = $_GET['version'] ?? null;
|
|
|
|
if (!$projectId) {
|
|
die("Project ID is required.");
|
|
}
|
|
|
|
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;
|
|
}
|
|
}
|
|
|
|
$project = null;
|
|
$versions = [];
|
|
$allocations = [];
|
|
$roster_for_search = [];
|
|
$db_error = null;
|
|
|
|
function get_months($startDate, $endDate) {
|
|
$start = new DateTime($startDate);
|
|
$end = new DateTime($endDate);
|
|
$interval = new DateInterval('P1M');
|
|
$period = new DatePeriod($start, $interval, $end->modify('+1 month'));
|
|
$months = [];
|
|
foreach ($period as $dt) {
|
|
$months[] = $dt->format('Y-m-01');
|
|
}
|
|
return $months;
|
|
}
|
|
|
|
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);
|
|
}
|
|
|
|
// Fetch project details
|
|
$stmt = $pdo->prepare("SELECT * FROM projects WHERE id = :projectId");
|
|
$stmt->execute([':projectId' => $projectId]);
|
|
$project = $stmt->fetch(PDO::FETCH_ASSOC);
|
|
|
|
if (!$project) {
|
|
die("Project not found.");
|
|
}
|
|
|
|
// Fetch forecasting versions
|
|
$stmt = $pdo->prepare("SELECT * FROM forecasting WHERE projectId = :projectId ORDER BY versionNumber DESC");
|
|
$stmt->execute([':projectId' => $projectId]);
|
|
$versions = $stmt->fetchAll(PDO::FETCH_ASSOC);
|
|
|
|
if (empty($versionNumber) && !empty($versions)) {
|
|
$versionNumber = $versions[0]['versionNumber'];
|
|
}
|
|
|
|
$selected_version = null;
|
|
if ($versionNumber) {
|
|
foreach($versions as $v) {
|
|
if ($v['versionNumber'] == $versionNumber) {
|
|
$selected_version = $v;
|
|
break;
|
|
}
|
|
}
|
|
}
|
|
|
|
// Fetch allocations for the selected version
|
|
if ($selected_version) {
|
|
$months = get_months($project['startDate'], $project['endDate']);
|
|
$sql = "SELECT
|
|
fa.rosterId,
|
|
fa.resourceName,
|
|
fa.level,
|
|
GROUP_CONCAT(fa.month, '|', fa.allocatedDays ORDER BY fa.month) as monthly_allocations
|
|
FROM forecastAllocation fa
|
|
WHERE fa.forecastingId = :forecastingId
|
|
GROUP BY fa.rosterId, fa.resourceName, fa.level
|
|
ORDER BY fa.resourceName";
|
|
$stmt = $pdo->prepare($sql);
|
|
$stmt->execute([':forecastingId' => $selected_version['id']]);
|
|
$raw_allocations = $stmt->fetchAll(PDO::FETCH_ASSOC);
|
|
|
|
// Process allocations into a structured array
|
|
foreach ($raw_allocations as $raw_row) {
|
|
$alloc_row = [
|
|
'rosterId' => $raw_row['rosterId'],
|
|
'resourceName' => $raw_row['resourceName'],
|
|
'level' => $raw_row['level'],
|
|
'months' => []
|
|
];
|
|
foreach($months as $month) {
|
|
$alloc_row['months'][$month] = 0; // Default
|
|
}
|
|
$monthly_pairs = explode(',', $raw_row['monthly_allocations']);
|
|
foreach ($monthly_pairs as $pair) {
|
|
list($month, $days) = explode('|', $pair);
|
|
$alloc_row['months'][$month] = $days;
|
|
}
|
|
$allocations[] = $alloc_row;
|
|
}
|
|
}
|
|
|
|
// Fetch roster for search
|
|
$stmt = $pdo->query("SELECT id, sapCode, fullNameEn, `level` FROM roster ORDER BY fullNameEn");
|
|
$roster_for_search = $stmt->fetchAll(PDO::FETCH_ASSOC);
|
|
|
|
} catch (PDOException $e) {
|
|
$db_error = "Database error: " . $e->getMessage();
|
|
}
|
|
|
|
$months_headers = $project ? get_months($project['startDate'], $project['endDate']) : [];
|
|
|
|
?>
|
|
<!DOCTYPE html>
|
|
<html lang="en" data-bs-theme="dark">
|
|
<head>
|
|
<meta charset="UTF-8">
|
|
<meta name="viewport" content="width=device-width, initial-scale=1.0">
|
|
<title>Forecasting - <?php echo htmlspecialchars($project['name'] ?? ''); ?></title>
|
|
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.2/dist/css/bootstrap.min.css" rel="stylesheet">
|
|
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap-icons@1.11.3/font/bootstrap-icons.min.css">
|
|
<link href="https://cdn.jsdelivr.net/npm/select2@4.1.0-rc.0/dist/css/select2.min.css" rel="stylesheet" />
|
|
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/select2-bootstrap-5-theme@1.3.0/dist/select2-bootstrap-5-theme.min.css" />
|
|
<link rel="stylesheet" href="assets/css/custom.css?v=<?php echo time(); ?>">
|
|
</head>
|
|
<body>
|
|
<div class="top-navbar">
|
|
Project Financials
|
|
</div>
|
|
<div class="main-wrapper">
|
|
<nav class="sidebar">
|
|
<ul class="nav flex-column">
|
|
<li class="nav-item"><a class="nav-link" href="roster.php"><i class="bi bi-people-fill me-2"></i>Roster</a></li>
|
|
<li class="nav-item"><a class="nav-link active" href="projects.php"><i class="bi bi-briefcase-fill me-2"></i>Projects</a></li>
|
|
</ul>
|
|
</nav>
|
|
|
|
<main class="content-wrapper">
|
|
<?php if ($db_error): ?>
|
|
<div class="alert alert-danger"><?php echo htmlspecialchars($db_error); ?></div>
|
|
<?php endif; ?>
|
|
|
|
<div class="page-header">
|
|
<h1 class="h2">Forecasting: <?php echo htmlspecialchars($project['name'] ?? 'N/A'); ?></h1>
|
|
<div class="header-actions">
|
|
<a href="project_details.php?id=<?php echo $projectId; ?>" class="btn btn-secondary"><i class="bi bi-arrow-left-circle me-2"></i>Back to Project</a>
|
|
<form action="forecasting_actions.php" method="POST" class="d-inline">
|
|
<input type="hidden" name="action" value="new_version">
|
|
<input type="hidden" name="projectId" value="<?php echo $projectId; ?>">
|
|
<button type="submit" class="btn btn-primary"><i class="bi bi-plus-circle-fill me-2"></i>New Version</button>
|
|
</form>
|
|
</div>
|
|
</div>
|
|
|
|
<div class="card mb-4">
|
|
<div class="card-body">
|
|
<div class="row align-items-center">
|
|
<div class="col-md-4">
|
|
<label for="versionSelector" class="form-label">Select Version</label>
|
|
<select class="form-select" id="versionSelector">
|
|
<?php foreach ($versions as $v): ?>
|
|
<option value="<?php echo $v['versionNumber']; ?>" <?php echo ($v['versionNumber'] == $versionNumber) ? 'selected' : ''; ?>>
|
|
Version <?php echo $v['versionNumber']; ?> (<?php echo date("d M Y, H:i", strtotime($v['createdAt'])); ?>)
|
|
</option>
|
|
<?php endforeach; ?>
|
|
</select>
|
|
</div>
|
|
</div>
|
|
</div>
|
|
</div>
|
|
|
|
<div class="card">
|
|
<div class="card-header">
|
|
<h5 class="card-title">Resource Allocations</h5>
|
|
</div>
|
|
<div class="card-body">
|
|
<form action="forecasting_actions.php" method="POST" class="row g-3 align-items-end mb-4">
|
|
<input type="hidden" name="action" value="add_resource">
|
|
<input type="hidden" name="projectId" value="<?php echo $projectId; ?>">
|
|
<input type="hidden" name="forecastingId" value="<?php echo $selected_version['id'] ?? ''; ?>">
|
|
<div class="col-md-6">
|
|
<label for="rosterSearch" class="form-label">Add Resource</label>
|
|
<select class="form-control" id="rosterSearch" name="rosterId" required>
|
|
<option></option> <!-- Placeholder for Select2 -->
|
|
<?php foreach ($roster_for_search as $resource): ?>
|
|
<option value="<?php echo $resource['id']; ?>" data-level="<?php echo htmlspecialchars($resource['level']); ?>">
|
|
<?php echo htmlspecialchars($resource['fullNameEn'] . ' (' . $resource['sapCode'] . ')'); ?>
|
|
</option>
|
|
<?php endforeach; ?>
|
|
</select>
|
|
</div>
|
|
<div class="col-auto">
|
|
<button type="submit" class="btn btn-success">Add to Forecast</button>
|
|
</div>
|
|
</form>
|
|
</div>
|
|
<div class="table-responsive">
|
|
<table class="table table-bordered table-hover">
|
|
<thead>
|
|
<tr>
|
|
<th>Resource Name</th>
|
|
<th>Level</th>
|
|
<?php foreach ($months_headers as $month): ?>
|
|
<th><?php echo date("M Y", strtotime($month)); ?></th>
|
|
<?php endforeach; ?>
|
|
<th>Actions</th>
|
|
</tr>
|
|
</thead>
|
|
<tbody>
|
|
<?php if (empty($allocations)): ?>
|
|
<tr>
|
|
<td colspan="<?php echo count($months_headers) + 3; ?>" class="text-center text-secondary">No resources allocated yet.</td>
|
|
</tr>
|
|
<?php else: ?>
|
|
<?php foreach ($allocations as $alloc_row): ?>
|
|
<tr>
|
|
<td><?php echo htmlspecialchars($alloc_row['resourceName']); ?></td>
|
|
<td><?php echo htmlspecialchars($alloc_row['level']); ?></td>
|
|
<?php foreach ($alloc_row['months'] as $month => $days): ?>
|
|
<td class="editable"
|
|
data-month="<?php echo $month; ?>"
|
|
data-roster-id="<?php echo $alloc_row['rosterId']; ?>"
|
|
data-forecasting-id="<?php echo $selected_version['id']; ?>">
|
|
<?php echo htmlspecialchars($days); ?>
|
|
</td>
|
|
<?php endforeach; ?>
|
|
<td>
|
|
<form action="forecasting_actions.php" method="POST" onsubmit="return confirm('Remove this resource from the forecast?');">
|
|
<input type="hidden" name="action" value="remove_resource">
|
|
<input type="hidden" name="projectId" value="<?php echo $projectId; ?>">
|
|
<input type="hidden" name="forecastingId" value="<?php echo $selected_version['id']; ?>">
|
|
<input type="hidden" name="rosterId" value="<?php echo $alloc_row['rosterId']; ?>">
|
|
<button type="submit" class="btn btn-sm btn-outline-danger"><i class="bi bi-trash"></i></button>
|
|
</form>
|
|
</td>
|
|
</tr>
|
|
<?php endforeach; ?>
|
|
<?php endif; ?>
|
|
</tbody>
|
|
</table>
|
|
</div>
|
|
</div>
|
|
</main>
|
|
</div>
|
|
|
|
<script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
|
|
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.2/dist/js/bootstrap.bundle.min.js"></script>
|
|
<script src="https://cdn.jsdelivr.net/npm/select2@4.1.0-rc.0/dist/js/select2.min.js"></script>
|
|
<script src="assets/js/forecasting.js?v=<?php echo time(); ?>"></script>
|
|
</body>
|
|
</html>
|