206 lines
8.5 KiB
PHP
206 lines
8.5 KiB
PHP
<?php
|
|
require_once __DIR__ . '/db/config.php';
|
|
|
|
// --- DATA FETCHING & CALCULATION ---
|
|
$project = null;
|
|
$project_id = $_GET['project_id'] ?? null;
|
|
$financial_data = [];
|
|
$months = [];
|
|
$metrics = ["Opening Balance", "Billings", "WIP", "Expenses", "Cost", "NSR", "Margin"];
|
|
|
|
if (!$project_id) {
|
|
header("HTTP/1.0 400 Bad Request");
|
|
echo "Project ID is required.";
|
|
exit;
|
|
}
|
|
|
|
try {
|
|
$pdo = db();
|
|
$stmt = $pdo->prepare("SELECT * FROM projects WHERE id = :id");
|
|
$stmt->execute([':id' => $project_id]);
|
|
$project = $stmt->fetch(PDO::FETCH_ASSOC);
|
|
|
|
if ($project) {
|
|
function get_month_range($start_date_str, $end_date_str) {
|
|
$months = [];
|
|
if (empty($start_date_str) || empty($end_date_str)) return $months;
|
|
$start = new DateTime($start_date_str);
|
|
$end = new DateTime($end_date_str);
|
|
$current = clone $start;
|
|
$current->modify('first day of this month');
|
|
while ($current <= $end) {
|
|
$months[] = $current->format('Y-m-01');
|
|
$current->modify('+1 month');
|
|
}
|
|
return $months;
|
|
}
|
|
$months = get_month_range($project['startDate'], $project['endDate']);
|
|
|
|
// --- Financial Calculations ---
|
|
|
|
// 1. Fetch base monthly data
|
|
$monthly_costs = [];
|
|
$monthly_wip = [];
|
|
|
|
$forecast_stmt = $pdo->prepare("SELECT id FROM forecasting WHERE projectId = :pid ORDER BY versionNumber DESC LIMIT 1");
|
|
$forecast_stmt->execute([':pid' => $project_id]);
|
|
$latest_forecast = $forecast_stmt->fetch(PDO::FETCH_ASSOC);
|
|
|
|
if ($latest_forecast) {
|
|
$fid = $latest_forecast['id'];
|
|
|
|
// Base Monthly Cost
|
|
$cost_sql = "SELECT fa.month, SUM(fa.allocatedDays * (r.totalMonthlyCost / 20)) FROM forecastAllocation fa JOIN roster r ON fa.rosterId = r.id WHERE fa.forecastingId = :fid GROUP BY fa.month";
|
|
$cost_stmt = $pdo->prepare($cost_sql);
|
|
$cost_stmt->execute([':fid' => $fid]);
|
|
$monthly_costs = $cost_stmt->fetchAll(PDO::FETCH_KEY_PAIR);
|
|
|
|
// Base Monthly WIP
|
|
$recoverability_decimal = $project['recoverability'] / 100;
|
|
$wip_sql = "SELECT fa.month, SUM(fa.allocatedDays * (r.grossRevenue * (1 - :recoverability))) as wip FROM forecastAllocation fa JOIN roster r ON fa.rosterId = r.id WHERE fa.forecastingId = :fid GROUP BY fa.month";
|
|
$wip_stmt = $pdo->prepare($wip_sql);
|
|
$wip_stmt->execute([':fid' => $fid, ':recoverability' => $recoverability_decimal]);
|
|
$monthly_wip = $wip_stmt->fetchAll(PDO::FETCH_KEY_PAIR);
|
|
}
|
|
|
|
// Base Monthly Billings
|
|
$billing_stmt = $pdo->prepare("SELECT month, amount FROM billingMonthly WHERE projectId = :pid");
|
|
$billing_stmt->execute([':pid' => $project_id]);
|
|
$monthly_billing = $billing_stmt->fetchAll(PDO::FETCH_KEY_PAIR);
|
|
|
|
// Base Monthly Expenses
|
|
$expenses_stmt = $pdo->prepare("SELECT month, amount FROM expensesMonthly WHERE projectId = :pid");
|
|
$expenses_stmt->execute([':pid' => $project_id]);
|
|
$monthly_expenses = $expenses_stmt->fetchAll(PDO::FETCH_KEY_PAIR);
|
|
|
|
// 2. Calculate and process values month by month, including overrides
|
|
$cumulative_billing = 0;
|
|
$cumulative_cost = 0;
|
|
$cumulative_expenses = 0;
|
|
$previous_month_opening_balance = 0; // Initialize Opening Balance for the first month.
|
|
|
|
// Fetch all overridden data at once to avoid querying in a loop
|
|
$override_stmt = $pdo->prepare("SELECT month, metricName, value FROM projectFinanceMonthly WHERE projectId = :pid AND is_overridden = 1");
|
|
$override_stmt->execute([':pid' => $project_id]);
|
|
$overridden_rows = $override_stmt->fetchAll(PDO::FETCH_ASSOC);
|
|
$overrides = [];
|
|
$metric_name_map = [
|
|
'wip' => 'WIP',
|
|
'opening_balance' => 'Opening Balance',
|
|
'billing' => 'Billings',
|
|
'expenses' => 'Expenses',
|
|
'cost' => 'Cost',
|
|
'nsr' => 'NSR',
|
|
'margin' => 'Margin'
|
|
];
|
|
foreach ($overridden_rows as $row) {
|
|
if (isset($metric_name_map[$row['metricName']])) {
|
|
$metric_display_name = $metric_name_map[$row['metricName']];
|
|
$overrides[$row['month']][$metric_display_name] = $row['value'];
|
|
}
|
|
}
|
|
|
|
foreach ($months as $month) {
|
|
// --- Opening Balance ---
|
|
// Opening Balance of the current month is the Opening Balance of the previous month.
|
|
$opening_balance = $previous_month_opening_balance;
|
|
$financial_data['Opening Balance'][$month] = $opening_balance;
|
|
if (isset($overrides[$month]['Opening Balance'])) {
|
|
$financial_data['Opening Balance'][$month] = $overrides[$month]['Opening Balance'];
|
|
}
|
|
|
|
// --- Base Data ---
|
|
$cost = $monthly_costs[$month] ?? 0;
|
|
$base_monthly_wip = $monthly_wip[$month] ?? 0;
|
|
$billing = $monthly_billing[$month] ?? 0;
|
|
$expenses = $monthly_expenses[$month] ?? 0;
|
|
|
|
// --- Cumulative Metrics ---
|
|
$cumulative_billing += $billing;
|
|
$financial_data['Billings'][$month] = $cumulative_billing;
|
|
if (isset($overrides[$month]['Billings'])) {
|
|
$financial_data['Billings'][$month] = $overrides[$month]['Billings'];
|
|
$cumulative_billing = $financial_data['Billings'][$month];
|
|
}
|
|
|
|
$cumulative_cost += $cost;
|
|
$financial_data['Cost'][$month] = $cumulative_cost;
|
|
if (isset($overrides[$month]['Cost'])) {
|
|
$financial_data['Cost'][$month] = $overrides[$month]['Cost'];
|
|
$cumulative_cost = $financial_data['Cost'][$month];
|
|
}
|
|
|
|
$cumulative_expenses += $expenses;
|
|
$financial_data['Expenses'][$month] = $cumulative_expenses;
|
|
if (isset($overrides[$month]['Expenses'])) {
|
|
$financial_data['Expenses'][$month] = $overrides[$month]['Expenses'];
|
|
$cumulative_expenses = $financial_data['Expenses'][$month];
|
|
}
|
|
|
|
// --- WIP (Closing Balance) ---
|
|
$final_opening_balance = $financial_data['Opening Balance'][$month];
|
|
$current_wip = $final_opening_balance + $expenses + $base_monthly_wip - $billing;
|
|
$financial_data['WIP'][$month] = $current_wip;
|
|
if (isset($overrides[$month]['WIP'])) {
|
|
$financial_data['WIP'][$month] = $overrides[$month]['WIP'];
|
|
}
|
|
|
|
// --- Calculated Metrics (NSR and Margin) ---
|
|
$nsr = $financial_data['WIP'][$month] + $financial_data['Billings'][$month] - $financial_data['Opening Balance'][$month] - $financial_data['Expenses'][$month];
|
|
$financial_data['NSR'][$month] = $nsr;
|
|
if (isset($overrides[$month]['NSR'])) {
|
|
$financial_data['NSR'][$month] = $overrides[$month]['NSR'];
|
|
}
|
|
|
|
$final_nsr = $financial_data['NSR'][$month];
|
|
$final_cost = $financial_data['Cost'][$month];
|
|
$margin = ($final_nsr != 0) ? (($final_nsr - $final_cost) / $final_nsr) : 0;
|
|
$financial_data['Margin'][$month] = $margin;
|
|
if (isset($overrides[$month]['Margin'])) {
|
|
$financial_data['Margin'][$month] = $overrides[$month]['Margin'];
|
|
}
|
|
|
|
// --- Carry-over for next iteration ---
|
|
// The next month's opening balance is this month's final opening balance (including override).
|
|
$previous_month_opening_balance = $financial_data['Opening Balance'][$month];
|
|
}
|
|
}
|
|
} catch (PDOException $e) {
|
|
header("HTTP/1.0 500 Internal Server Error");
|
|
echo "Database error: " . $e->getMessage();
|
|
exit;
|
|
}
|
|
|
|
if (!$project) {
|
|
header("HTTP/1.0 404 Not Found");
|
|
echo "Project not found.";
|
|
exit;
|
|
}
|
|
|
|
// --- CSV EXPORT ---
|
|
$filename = "project_finance_" . preg_replace('/[^a-zA-Z0-9_]/', '_', $project['name']) . ".csv";
|
|
|
|
header('Content-Type: text/csv');
|
|
header('Content-Disposition: attachment; filename="' . $filename . '"');
|
|
|
|
$output = fopen('php://output', 'w');
|
|
|
|
// Header row
|
|
$header = ['Metric'];
|
|
foreach ($months as $month) {
|
|
$header[] = date('M Y', strtotime($month));
|
|
}
|
|
fputcsv($output, $header);
|
|
|
|
// Data rows
|
|
foreach ($metrics as $metric) {
|
|
$row = [$metric];
|
|
foreach ($months as $month) {
|
|
$row[] = number_format($financial_data[$metric][$month] ?? 0, 2);
|
|
}
|
|
fputcsv($output, $row);
|
|
}
|
|
|
|
fclose($output);
|
|
exit;
|