| Server IP : 72.60.21.38 / Your IP : 216.73.216.25 Web Server : LiteSpeed System : Linux uk-fast-web1372.main-hosting.eu 4.18.0-553.121.1.lve.el8.x86_64 #1 SMP Thu Apr 30 16:40:41 UTC 2026 x86_64 User : u390967363 ( 390967363) PHP Version : 8.2.30 Disable Function : system, exec, shell_exec, passthru, mysql_list_dbs, ini_alter, dl, symlink, link, chgrp, leak, popen, apache_child_terminate, virtual, mb_send_mail MySQL : OFF | cURL : ON | WGET : ON | Perl : OFF | Python : OFF | Sudo : OFF | Pkexec : OFF Directory : /home/u390967363/domains/aibenproperties.com/public_html/app/ |
Upload File : |
<?php
session_start();
require_once 'includes/db.php';
require_once 'includes/functions.php';
require_once 'includes/installments.php';
$role = $_SESSION['user_role'] ?? 'guest';
$role_norm = strtolower(preg_replace('/[^a-z0-9]+/','_', (string)$role));
$isChairmanQueueView = in_array($role_norm, ['chairman_ceo','super_admin'], true);
if (!isset($_SESSION['user_id'])) {
header("Location: login.php");
exit;
}
$allowed = isExecutive($role) || isAdminTier($role) || in_array($role_norm, ['chairman_ceo']);
if (!$allowed) {
include 'includes/header.php';
echo '<div class="container p-4"><div class="alert alert-danger">Access denied.</div></div>';
include 'includes/footer.php';
exit;
}
$isInsightsExecDashboard = isset($_GET['insights']) && (string)$_GET['insights'] === '1';
$isLegacyExecDashboard = isset($_GET['legacy']) && (string)$_GET['legacy'] === '1';
if ($isInsightsExecDashboard) {
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
header("Location: executive-dashboard.php?insights=1");
exit;
}
$companyId = function_exists('getCurrentCompanyId') ? getCurrentCompanyId() : null;
$now = new DateTimeImmutable('now');
$monthStart = (new DateTimeImmutable('first day of this month'))->setTime(0, 0, 0);
$nextMonthStart = (new DateTimeImmutable('first day of next month'))->setTime(0, 0, 0);
$prevMonthStart = (new DateTimeImmutable('first day of last month'))->setTime(0, 0, 0);
$prevMonthEnd = $monthStart->modify('-1 second');
$paymentDateCol = 'created_at';
try {
if (function_exists('kpiPaymentDateColumn')) {
$paymentDateCol = (string)kpiPaymentDateColumn('payments');
} elseif (function_exists('tableHasColumn') && tableHasColumn('payments', 'approval_date')) {
$paymentDateCol = 'approval_date';
} elseif (function_exists('tableHasColumn') && tableHasColumn('payments', 'payment_date')) {
$paymentDateCol = 'payment_date';
}
} catch (Throwable $e) {}
$paymentStatuses = ['completed', 'verified', 'paid'];
try {
if (function_exists('kpiPaymentFinalizedStatuses')) {
$paymentStatuses = (array)kpiPaymentFinalizedStatuses();
if (empty($paymentStatuses)) $paymentStatuses = ['completed', 'verified', 'paid'];
}
} catch (Throwable $e) {}
$sumPayments = function (DateTimeImmutable $from, DateTimeImmutable $to) use ($pdo, $companyId, $paymentStatuses, $paymentDateCol) {
$ph = implode(',', array_fill(0, count($paymentStatuses), '?'));
$sql = "SELECT COALESCE(SUM(amount),0) FROM payments WHERE status IN ($ph) AND $paymentDateCol >= ? AND $paymentDateCol < ?";
$params = array_merge($paymentStatuses, [$from->format('Y-m-d H:i:s'), $to->format('Y-m-d H:i:s')]);
if ($companyId && function_exists('tableHasColumn') && tableHasColumn('payments', 'company_id')) {
$sql .= " AND (company_id = ? OR company_id IS NULL)";
$params[] = $companyId;
}
$stmt = $pdo->prepare($sql);
$stmt->execute($params);
return (float)($stmt->fetchColumn() ?: 0);
};
$sumExpenses = function (DateTimeImmutable $from, DateTimeImmutable $to) use ($pdo) {
$filters = [
'start_date' => $from->format('Y-m-d'),
'end_date' => $to->modify('-1 second')->format('Y-m-d'),
'include_pending_manual' => false
];
$params = [];
$union = buildExpensesUnionSql($filters, $params, false);
$sql = "SELECT COALESCE(SUM(e.amount),0) FROM $union e";
$stmt = $pdo->prepare($sql);
$stmt->execute($params);
return (float)($stmt->fetchColumn() ?: 0);
};
$revThisMonth = 0.0;
$revPrevMonth = 0.0;
$expThisMonth = 0.0;
$expPrevMonth = 0.0;
try { $revThisMonth = $sumPayments($monthStart, $nextMonthStart); } catch (Throwable $e) {}
try { $revPrevMonth = $sumPayments($prevMonthStart, $monthStart); } catch (Throwable $e) {}
try { $expThisMonth = $sumExpenses($monthStart, $nextMonthStart); } catch (Throwable $e) {}
try { $expPrevMonth = $sumExpenses($prevMonthStart, $monthStart); } catch (Throwable $e) {}
$revAllTime = 0.0;
$expAllTime = 0.0;
try {
$ph = implode(',', array_fill(0, count($paymentStatuses), '?'));
$q = "SELECT COALESCE(SUM(amount),0) FROM payments WHERE status IN ($ph)";
$p = $paymentStatuses;
if ($companyId && function_exists('tableHasColumn') && tableHasColumn('payments', 'company_id')) { $q .= " AND (company_id = ? OR company_id IS NULL)"; $p[] = $companyId; }
$st = $pdo->prepare($q);
$st->execute($p);
$revAllTime = (float)($st->fetchColumn() ?: 0);
} catch (Throwable $e) {}
try {
$filters = ['include_pending_manual' => false];
$params = [];
$union = buildExpensesUnionSql($filters, $params, false);
$q = "SELECT COALESCE(SUM(e.amount),0) FROM $union e";
$st = $pdo->prepare($q);
$st->execute($params);
$expAllTime = (float)($st->fetchColumn() ?: 0);
} catch (Throwable $e) {}
$netThisMonth = $revThisMonth - $expThisMonth;
$netPrevMonth = $revPrevMonth - $expPrevMonth;
$netAllTime = $revAllTime - $expAllTime;
$pctChange = function (float $cur, float $prev) {
if (abs($prev) < 0.00001) {
if (abs($cur) < 0.00001) return 0.0;
return $cur > 0 ? 100.0 : -100.0;
}
return (($cur - $prev) / $prev) * 100.0;
};
$trendPill = function (float $cur, float $prev, bool $upIsGood = true) use ($pctChange) {
if (abs($cur) < 0.00001 && abs($prev) < 0.00001) {
return ['text' => '—', 'class' => 'neutral'];
}
$chg = $pctChange($cur, $prev);
if (abs($chg) < 3.0) {
return ['text' => 'Stable', 'class' => 'neutral'];
}
$isUp = $chg >= 0;
$good = $upIsGood ? $isUp : !$isUp;
return [
'text' => ($isUp ? '+' : '') . number_format($chg, 1) . '%',
'class' => $good ? 'up' : 'down'
];
};
$cashBalance = 0.0;
$bankPositions = [];
try {
$hasA = $pdo->query("SHOW TABLES LIKE 'finance_accounts'")->rowCount() > 0;
if ($hasA && function_exists('tableHasColumn') && tableHasColumn('finance_accounts', 'id') && tableHasColumn('finance_accounts', 'account_name')) {
$cols = ['id', 'account_name'];
if (tableHasColumn('finance_accounts', 'account_type')) $cols[] = 'account_type';
if (tableHasColumn('finance_accounts', 'balance')) $cols[] = 'balance';
$q = "SELECT " . implode(',', $cols) . " FROM finance_accounts";
$p = [];
if ($companyId && tableHasColumn('finance_accounts', 'company_id')) {
$q .= " WHERE (company_id = ? OR company_id IS NULL)";
$p[] = $companyId;
}
$q .= " ORDER BY account_name ASC";
$st = $pdo->prepare($q);
$st->execute($p);
$bankPositions = $st->fetchAll(PDO::FETCH_ASSOC) ?: [];
foreach ($bankPositions as $acc) {
$cashBalance += (float)($acc['balance'] ?? 0);
}
}
} catch (Throwable $e) {}
$months = [];
$inflow = [];
$outflow = [];
$netflow = [];
$cursor = $monthStart->modify('-5 months');
for ($i = 0; $i < 6; $i++) {
$from = (new DateTimeImmutable($cursor->format('Y-m-01')))->setTime(0, 0, 0);
$to = (new DateTimeImmutable($from->modify('first day of next month')->format('Y-m-01')))->setTime(0, 0, 0);
$months[] = $from->format('M Y');
$in = 0.0; $out = 0.0;
try { $in = $sumPayments($from, $to); } catch (Throwable $e) { $in = 0.0; }
try { $out = $sumExpenses($from, $to); } catch (Throwable $e) { $out = 0.0; }
$inflow[] = round($in, 2);
$outflow[] = round($out, 2);
$netflow[] = round($in - $out, 2);
$cursor = $cursor->modify('+1 month');
}
$pendingApprovals = 0;
try {
$hasEm = $pdo->query("SHOW TABLES LIKE 'expenses_manual'")->rowCount() > 0;
if ($hasEm && function_exists('tableHasColumn') && tableHasColumn('expenses_manual', 'status')) {
$q = "SELECT COUNT(*) FROM expenses_manual WHERE LOWER(TRIM(status)) = 'pending'";
$p = [];
if ($companyId && tableHasColumn('expenses_manual', 'company_id')) {
$q .= " AND company_id = ?";
$p[] = $companyId;
}
$st = $pdo->prepare($q);
$st->execute($p);
$pendingApprovals = (int)($st->fetchColumn() ?: 0);
}
} catch (Throwable $e) {}
$pendingPayments = 0;
try {
$hasPay = $pdo->query("SHOW TABLES LIKE 'payments'")->rowCount() > 0;
if ($hasPay && function_exists('tableHasColumn')) {
$stCol = tableHasColumn('payments', 'status') ? 'status' : (tableHasColumn('payments', 'payment_status') ? 'payment_status' : null);
if ($stCol) {
$pendingStatuses = function_exists('kpiPaymentPendingStatuses') ? (array)kpiPaymentPendingStatuses() : ['submitted', 'pending', 'pending_gateway', 'awaiting_verification', 'pending_verification', 'pending_confirmation'];
$ph = implode(',', array_fill(0, count($pendingStatuses), '?'));
$q = "SELECT COUNT(*) FROM payments WHERE LOWER(TRIM($stCol)) IN ($ph)";
$p = array_map(function ($v) { return strtolower(trim((string)$v)); }, $pendingStatuses);
if ($companyId && tableHasColumn('payments', 'company_id')) { $q .= " AND (company_id = ? OR company_id IS NULL)"; $p[] = $companyId; }
$st = $pdo->prepare($q);
$st->execute($p);
$pendingPayments = (int)($st->fetchColumn() ?: 0);
}
}
} catch (Throwable $e) {}
$overduePayments = 0;
try {
$hasIns = $pdo->query("SHOW TABLES LIKE 'installments'")->rowCount() > 0;
if ($hasIns && function_exists('tableHasColumn') && tableHasColumn('installments', 'due_date')) {
$statusCol = tableHasColumn('installments', 'status') ? 'status' : null;
$q = "SELECT COUNT(*) FROM installments WHERE due_date < CURRENT_DATE()";
$p = [];
if ($statusCol) {
$q .= " AND (LOWER(TRIM($statusCol)) IN ('overdue','pending') OR $statusCol IS NULL)";
}
if ($companyId && tableHasColumn('installments', 'company_id')) {
$q .= " AND company_id = ?";
$p[] = $companyId;
}
$st = $pdo->prepare($q);
$st->execute($p);
$overduePayments = (int)($st->fetchColumn() ?: 0);
}
} catch (Throwable $e) {}
$lowBalanceCount = 0;
$lowBalanceThreshold = 100000.0;
try {
if (!empty($bankPositions)) {
foreach ($bankPositions as $acc) {
$bal = (float)($acc['balance'] ?? 0);
if ($bal < $lowBalanceThreshold) $lowBalanceCount++;
}
}
} catch (Throwable $e) {}
$allocTotal = 0;
$allocPending = 0;
$allocApproved = 0;
$allocAllocated = 0;
$allocFinalized = 0;
try {
$hasAlloc = $pdo->query("SHOW TABLES LIKE 'allocations'")->rowCount() > 0;
if ($hasAlloc && function_exists('tableHasColumn') && tableHasColumn('allocations', 'status')) {
$q = "SELECT status, COUNT(*) AS c FROM allocations WHERE 1=1";
$p = [];
if ($companyId && tableHasColumn('allocations', 'company_id')) { $q .= " AND company_id = ?"; $p[] = $companyId; }
$q .= " GROUP BY status";
$st = $pdo->prepare($q);
$st->execute($p);
foreach ($st->fetchAll(PDO::FETCH_ASSOC) ?: [] as $row) {
$status = strtolower(trim((string)($row['status'] ?? '')));
$c = (int)($row['c'] ?? 0);
$allocTotal += $c;
if ($status === 'pending') $allocPending += $c;
if ($status === 'approved') $allocApproved += $c;
if ($status === 'allocated') $allocAllocated += $c;
if ($status === 'finalized') $allocFinalized += $c;
}
}
} catch (Throwable $e) {}
$tasksOpen = 0;
$tasksOverdue = 0;
try {
$hasTasks = $pdo->query("SHOW TABLES LIKE 'tasks'")->rowCount() > 0;
if ($hasTasks && function_exists('tableHasColumn') && tableHasColumn('tasks', 'status')) {
$q = "SELECT COUNT(*) FROM tasks WHERE LOWER(TRIM(status)) <> 'completed'";
$p = [];
if ($companyId && tableHasColumn('tasks', 'company_id')) { $q .= " AND company_id = ?"; $p[] = $companyId; }
$st = $pdo->prepare($q);
$st->execute($p);
$tasksOpen = (int)($st->fetchColumn() ?: 0);
}
if ($hasTasks && function_exists('tableHasColumn') && tableHasColumn('tasks', 'due_date')) {
$q = "SELECT COUNT(*) FROM tasks WHERE due_date < CURRENT_DATE()";
$p = [];
if (function_exists('tableHasColumn') && tableHasColumn('tasks', 'status')) {
$q .= " AND LOWER(TRIM(status)) <> 'completed'";
}
if ($companyId && function_exists('tableHasColumn') && tableHasColumn('tasks', 'company_id')) { $q .= " AND company_id = ?"; $p[] = $companyId; }
$st = $pdo->prepare($q);
$st->execute($p);
$tasksOverdue = (int)($st->fetchColumn() ?: 0);
}
} catch (Throwable $e) {}
$missingModules = [];
try {
foreach (['payments', 'finance_accounts', 'allocations', 'tasks', 'audit_logs'] as $tbl) {
$ok = false;
try { $ok = $pdo->query("SHOW TABLES LIKE " . $pdo->quote($tbl))->rowCount() > 0; } catch (Throwable $e) { $ok = false; }
if (!$ok) $missingModules[] = $tbl;
}
} catch (Throwable $e) {}
$estates = [];
try {
$hasE = $pdo->query("SHOW TABLES LIKE 'estates'")->rowCount() > 0;
if ($hasE && function_exists('tableHasColumn') && tableHasColumn('estates', 'id') && tableHasColumn('estates', 'name')) {
$q = "SELECT id, name FROM estates";
$p = [];
if ($companyId && tableHasColumn('estates', 'company_id')) { $q .= " WHERE company_id = ?"; $p[] = $companyId; }
$q .= " ORDER BY name ASC";
$st = $pdo->prepare($q);
$st->execute($p);
$estates = $st->fetchAll(PDO::FETCH_ASSOC) ?: [];
}
} catch (Throwable $e) {}
$revenueByEstate = [];
$expensesByEstate = [];
$profitByEstate = [];
try {
$hasJoinChain = function_exists('tableHasColumn')
&& $pdo->query("SHOW TABLES LIKE 'properties'")->rowCount() > 0
&& $pdo->query("SHOW TABLES LIKE 'allocations'")->rowCount() > 0
&& $pdo->query("SHOW TABLES LIKE 'payments'")->rowCount() > 0
&& tableHasColumn('properties', 'estate_id')
&& tableHasColumn('allocations', 'property_id')
&& tableHasColumn('allocations', 'id');
if ($hasJoinChain && function_exists('tableHasColumn')) {
$ph = implode(',', array_fill(0, count($paymentStatuses), '?'));
$dateFrom = $monthStart->format('Y-m-d H:i:s');
$dateTo = $nextMonthStart->format('Y-m-d H:i:s');
if (tableHasColumn('payments', 'allocation_id')) {
$q = "
SELECT pr.estate_id AS estate_id, COALESCE(SUM(pay.amount),0) AS revenue
FROM payments pay
JOIN allocations a ON a.id = pay.allocation_id
JOIN properties pr ON pr.id = a.property_id
WHERE pay.status IN ($ph) AND pay.$paymentDateCol >= ? AND pay.$paymentDateCol < ?
";
$p = array_merge($paymentStatuses, [$dateFrom, $dateTo]);
if ($companyId && tableHasColumn('payments', 'company_id')) { $q .= " AND (pay.company_id = ? OR pay.company_id IS NULL)"; $p[] = $companyId; }
$q .= " GROUP BY pr.estate_id";
$st = $pdo->prepare($q);
$st->execute($p);
foreach ($st->fetchAll(PDO::FETCH_ASSOC) ?: [] as $row) {
$eid = (int)($row['estate_id'] ?? 0);
if ($eid > 0) $revenueByEstate[$eid] = ($revenueByEstate[$eid] ?? 0) + (float)($row['revenue'] ?? 0);
}
}
if (tableHasColumn('payments', 'deal_id') && tableHasColumn('allocations', 'deal_id')) {
$q = "
SELECT pr.estate_id AS estate_id, COALESCE(SUM(pay.amount),0) AS revenue
FROM payments pay
JOIN (
SELECT deal_id, MIN(property_id) AS property_id
FROM allocations
WHERE deal_id IS NOT NULL
GROUP BY deal_id
) a ON a.deal_id = pay.deal_id
JOIN properties pr ON pr.id = a.property_id
WHERE pay.status IN ($ph) AND pay.$paymentDateCol >= ? AND pay.$paymentDateCol < ?
";
$p = array_merge($paymentStatuses, [$dateFrom, $dateTo]);
if ($companyId && tableHasColumn('payments', 'company_id')) { $q .= " AND (pay.company_id = ? OR pay.company_id IS NULL)"; $p[] = $companyId; }
$q .= " GROUP BY pr.estate_id";
$st = $pdo->prepare($q);
$st->execute($p);
foreach ($st->fetchAll(PDO::FETCH_ASSOC) ?: [] as $row) {
$eid = (int)($row['estate_id'] ?? 0);
if ($eid > 0) $revenueByEstate[$eid] = ($revenueByEstate[$eid] ?? 0) + (float)($row['revenue'] ?? 0);
}
}
if (tableHasColumn('payments', 'property_id')) {
$q = "
SELECT pr.estate_id AS estate_id, COALESCE(SUM(pay.amount),0) AS revenue
FROM payments pay
JOIN properties pr ON pr.id = pay.property_id
WHERE pay.status IN ($ph) AND pay.$paymentDateCol >= ? AND pay.$paymentDateCol < ?
";
$p = array_merge($paymentStatuses, [$dateFrom, $dateTo]);
if ($companyId && tableHasColumn('payments', 'company_id')) { $q .= " AND (pay.company_id = ? OR pay.company_id IS NULL)"; $p[] = $companyId; }
$q .= " GROUP BY pr.estate_id";
$st = $pdo->prepare($q);
$st->execute($p);
foreach ($st->fetchAll(PDO::FETCH_ASSOC) ?: [] as $row) {
$eid = (int)($row['estate_id'] ?? 0);
if ($eid > 0) $revenueByEstate[$eid] = ($revenueByEstate[$eid] ?? 0) + (float)($row['revenue'] ?? 0);
}
}
}
} catch (Throwable $e) {}
try {
$hasEm = $pdo->query("SHOW TABLES LIKE 'expenses_manual'")->rowCount() > 0;
if ($hasEm && function_exists('tableHasColumn') && tableHasColumn('expenses_manual', 'amount') && tableHasColumn('expenses_manual', 'status') && tableHasColumn('expenses_manual', 'expense_date') && tableHasColumn('expenses_manual', 'estate_id')) {
$q = "SELECT estate_id, COALESCE(SUM(amount),0) AS s FROM expenses_manual WHERE LOWER(TRIM(status)) = 'approved' AND expense_date >= ? AND expense_date < ?";
$p = [$monthStart->format('Y-m-d H:i:s'), $nextMonthStart->format('Y-m-d H:i:s')];
if ($companyId && tableHasColumn('expenses_manual', 'company_id')) { $q .= " AND company_id = ?"; $p[] = $companyId; }
$q .= " GROUP BY estate_id";
$st = $pdo->prepare($q);
$st->execute($p);
foreach ($st->fetchAll(PDO::FETCH_ASSOC) ?: [] as $row) {
$eid = (int)($row['estate_id'] ?? 0);
if ($eid > 0) $expensesByEstate[$eid] = ($expensesByEstate[$eid] ?? 0) + (float)($row['s'] ?? 0);
}
}
} catch (Throwable $e) {}
try {
$hasM = $pdo->query("SHOW TABLES LIKE 'maintenance_requests'")->rowCount() > 0;
$hasP = $pdo->query("SHOW TABLES LIKE 'properties'")->rowCount() > 0;
if ($hasM && $hasP && function_exists('tableHasColumn') && tableHasColumn('maintenance_requests', 'status') && tableHasColumn('maintenance_requests', 'updated_at') && tableHasColumn('maintenance_requests', 'property_id') && tableHasColumn('properties', 'estate_id')) {
$costCol = tableHasColumn('maintenance_requests', 'cost') ? 'cost' : (tableHasColumn('maintenance_requests', 'amount') ? 'amount' : null);
if ($costCol) {
$q = "
SELECT pr.estate_id AS estate_id, COALESCE(SUM(m.$costCol),0) AS s
FROM maintenance_requests m
JOIN properties pr ON pr.id = m.property_id
WHERE LOWER(TRIM(m.status)) = 'completed' AND m.updated_at >= ? AND m.updated_at < ?
";
$p = [$monthStart->format('Y-m-d H:i:s'), $nextMonthStart->format('Y-m-d H:i:s')];
if ($companyId && tableHasColumn('maintenance_requests', 'company_id')) { $q .= " AND m.company_id = ?"; $p[] = $companyId; }
$q .= " GROUP BY pr.estate_id";
$st = $pdo->prepare($q);
$st->execute($p);
foreach ($st->fetchAll(PDO::FETCH_ASSOC) ?: [] as $row) {
$eid = (int)($row['estate_id'] ?? 0);
if ($eid > 0) $expensesByEstate[$eid] = ($expensesByEstate[$eid] ?? 0) + (float)($row['s'] ?? 0);
}
}
}
} catch (Throwable $e) {}
try {
$hasC = $pdo->query("SHOW TABLES LIKE 'commissions'")->rowCount() > 0;
$hasA = $pdo->query("SHOW TABLES LIKE 'allocations'")->rowCount() > 0;
$hasP = $pdo->query("SHOW TABLES LIKE 'properties'")->rowCount() > 0;
if ($hasC && $hasA && $hasP && function_exists('tableHasColumn') && tableHasColumn('allocations', 'property_id') && tableHasColumn('properties', 'estate_id')) {
$statusCol = tableHasColumn('commissions', 'status') ? 'status' : (tableHasColumn('commissions', 'commission_status') ? 'commission_status' : null);
$amountCol = tableHasColumn('commissions', 'amount') ? 'amount' : (tableHasColumn('commissions', 'commission_amount') ? 'commission_amount' : null);
$dateCol = tableHasColumn('commissions', 'date_paid') ? 'date_paid' : (tableHasColumn('commissions', 'paid_at') ? 'paid_at' : (tableHasColumn('commissions', 'created_at') ? 'created_at' : null));
$joinCol = tableHasColumn('commissions', 'allocation_id') ? 'allocation_id' : (tableHasColumn('commissions', 'deal_id') ? 'deal_id' : null);
if ($statusCol && $amountCol && $dateCol && $joinCol) {
$q = "
SELECT pr.estate_id AS estate_id, COALESCE(SUM(c.$amountCol),0) AS s
FROM commissions c
JOIN allocations a ON a.id = c.$joinCol
JOIN properties pr ON pr.id = a.property_id
WHERE LOWER(TRIM(c.$statusCol)) = 'paid' AND c.$dateCol >= ? AND c.$dateCol < ?
";
$p = [$monthStart->format('Y-m-d H:i:s'), $nextMonthStart->format('Y-m-d H:i:s')];
if ($companyId && tableHasColumn('commissions', 'company_id')) { $q .= " AND c.company_id = ?"; $p[] = $companyId; }
$q .= " GROUP BY pr.estate_id";
$st = $pdo->prepare($q);
$st->execute($p);
foreach ($st->fetchAll(PDO::FETCH_ASSOC) ?: [] as $row) {
$eid = (int)($row['estate_id'] ?? 0);
if ($eid > 0) $expensesByEstate[$eid] = ($expensesByEstate[$eid] ?? 0) + (float)($row['s'] ?? 0);
}
}
}
} catch (Throwable $e) {}
foreach ($estates as $es) {
$eid = (int)($es['id'] ?? 0);
if ($eid <= 0) continue;
$rev = (float)($revenueByEstate[$eid] ?? 0);
$exp = (float)($expensesByEstate[$eid] ?? 0);
if ($rev <= 0 && $exp <= 0) continue;
$profitByEstate[] = [
'estate_id' => $eid,
'estate' => (string)($es['name'] ?? ('Estate #' . $eid)),
'revenue' => $rev,
'expenses' => $exp,
'profit' => $rev - $exp
];
}
usort($profitByEstate, function ($a, $b) { return ($b['profit'] <=> $a['profit']) ?: ($b['revenue'] <=> $a['revenue']); });
$profitTop = array_slice($profitByEstate, 0, 8);
$topVendor = ['name' => '—', 'amount' => 0.0];
try {
$hasEm = $pdo->query("SHOW TABLES LIKE 'expenses_manual'")->rowCount() > 0;
$hasV = $pdo->query("SHOW TABLES LIKE 'vendors'")->rowCount() > 0;
if ($hasEm && $hasV && function_exists('tableHasColumn') && tableHasColumn('expenses_manual', 'vendor_id') && tableHasColumn('vendors', 'id') && tableHasColumn('vendors', 'name') && tableHasColumn('expenses_manual', 'amount') && tableHasColumn('expenses_manual', 'status') && tableHasColumn('expenses_manual', 'expense_date')) {
$q = "
SELECT v.id AS vid, v.name AS nm, COALESCE(SUM(em.amount),0) AS s
FROM expenses_manual em
JOIN vendors v ON v.id = em.vendor_id
WHERE LOWER(TRIM(em.status)) = 'approved' AND em.expense_date >= ? AND em.expense_date < ?
";
$p = [$monthStart->format('Y-m-d H:i:s'), $nextMonthStart->format('Y-m-d H:i:s')];
if ($companyId && tableHasColumn('expenses_manual', 'company_id')) { $q .= " AND em.company_id = ?"; $p[] = $companyId; }
$q .= " GROUP BY v.id, v.name ORDER BY s DESC LIMIT 1";
$st = $pdo->prepare($q);
$st->execute($p);
$row = $st->fetch(PDO::FETCH_ASSOC);
if ($row) $topVendor = ['name' => (string)($row['nm'] ?? '—'), 'amount' => (float)($row['s'] ?? 0)];
}
} catch (Throwable $e) {}
$topMarketer = ['name' => '—', 'amount' => 0.0];
try {
$hasPay = $pdo->query("SHOW TABLES LIKE 'payments'")->rowCount() > 0;
$hasDS = $pdo->query("SHOW TABLES LIKE 'deals_submit'")->rowCount() > 0;
$hasAlloc = $pdo->query("SHOW TABLES LIKE 'allocations'")->rowCount() > 0;
if ($hasPay && $hasDS && function_exists('tableHasColumn') && tableHasColumn('deals_submit', 'marketer_name')) {
$ph = implode(',', array_fill(0, count($paymentStatuses), '?'));
$q = null;
$p = array_merge($paymentStatuses, [$monthStart->format('Y-m-d H:i:s'), $nextMonthStart->format('Y-m-d H:i:s')]);
if ($hasAlloc && tableHasColumn('payments', 'allocation_id') && tableHasColumn('allocations', 'id') && tableHasColumn('allocations', 'deal_id')) {
$q = "
SELECT d.marketer_name AS nm, COALESCE(SUM(pay.amount),0) AS s
FROM payments pay
JOIN allocations a ON a.id = pay.allocation_id
JOIN deals_submit d ON d.id = a.deal_id
WHERE pay.status IN ($ph) AND pay.$paymentDateCol >= ? AND pay.$paymentDateCol < ? AND TRIM(COALESCE(d.marketer_name,'')) <> ''
";
} elseif (tableHasColumn('payments', 'deal_id') && tableHasColumn('deals_submit', 'id')) {
$q = "
SELECT d.marketer_name AS nm, COALESCE(SUM(pay.amount),0) AS s
FROM payments pay
JOIN deals_submit d ON d.id = pay.deal_id
WHERE pay.status IN ($ph) AND pay.$paymentDateCol >= ? AND pay.$paymentDateCol < ? AND TRIM(COALESCE(d.marketer_name,'')) <> ''
";
}
if ($q) {
if ($companyId && tableHasColumn('payments', 'company_id')) { $q .= " AND (pay.company_id = ? OR pay.company_id IS NULL)"; $p[] = $companyId; }
$q .= " GROUP BY d.marketer_name ORDER BY s DESC LIMIT 1";
$st = $pdo->prepare($q);
$st->execute($p);
$row = $st->fetch(PDO::FETCH_ASSOC);
if ($row) $topMarketer = ['name' => (string)($row['nm'] ?? '—'), 'amount' => (float)($row['s'] ?? 0)];
}
}
} catch (Throwable $e) {}
$recentExpenses = [];
try {
$res = get_all_expenses(['page' => 1, 'per_page' => 5, 'status' => 'approved']);
$recentExpenses = $res['rows'] ?? [];
} catch (Throwable $e) {}
$recentPayments = [];
try {
$ph = implode(',', array_fill(0, count($paymentStatuses), '?'));
$q = "SELECT id, amount, $paymentDateCol AS d, user_id FROM payments WHERE status IN ($ph)";
$p = $paymentStatuses;
if ($companyId && function_exists('tableHasColumn') && tableHasColumn('payments', 'company_id')) { $q .= " AND (company_id = ? OR company_id IS NULL)"; $p[] = $companyId; }
$q .= " ORDER BY $paymentDateCol DESC LIMIT 5";
$st = $pdo->prepare($q);
$st->execute($p);
$recentPayments = $st->fetchAll(PDO::FETCH_ASSOC) ?: [];
} catch (Throwable $e) {}
$recentApprovals = [];
try {
$hasAudit = $pdo->query("SHOW TABLES LIKE 'audit_logs'")->rowCount() > 0;
if ($hasAudit && function_exists('tableHasColumn') && tableHasColumn('audit_logs', 'action') && tableHasColumn('audit_logs', 'module')) {
$dateCol = tableHasColumn('audit_logs', 'created_at') ? 'created_at' : (tableHasColumn('audit_logs', 'log_date') ? 'log_date' : null);
if ($dateCol) {
$q = "SELECT action, module, record_id, $dateCol AS d FROM audit_logs WHERE action LIKE '%APPROV%'";
$p = [];
if ($companyId && tableHasColumn('audit_logs', 'company_id')) { $q .= " AND company_id = ?"; $p[] = $companyId; }
$q .= " ORDER BY $dateCol DESC LIMIT 5";
$st = $pdo->prepare($q);
$st->execute($p);
$recentApprovals = $st->fetchAll(PDO::FETCH_ASSOC) ?: [];
}
}
} catch (Throwable $e) {}
$spikePct = $pctChange($expThisMonth, $expPrevMonth);
$spikeAlert = ($expThisMonth > 0 && $spikePct >= 30.0);
$spikeSeverity = $spikePct >= 75.0 ? 'danger' : 'warning';
$aiInsights = [];
$aiInsightsTop = [];
try {
if (function_exists('generate_financial_insights')) {
$aiInsights = (array)generate_financial_insights($pdo, [
'company_id' => $companyId,
'revenue' => (float)$revThisMonth,
'revenue_prev' => (float)$revPrevMonth,
'expenses' => (float)$expThisMonth,
'expenses_prev' => (float)$expPrevMonth,
'profit' => (float)$netThisMonth,
'pending_approvals' => (int)$pendingApprovals,
'pending_payments' => (int)$pendingPayments,
'bank_positions' => $bankPositions,
'low_balance_threshold' => (float)$lowBalanceThreshold,
'profit_by_estate' => $profitByEstate,
'period_start' => $monthStart->format('Y-m-d'),
'period_end' => $nextMonthStart->format('Y-m-d'),
]);
$aiInsightsTop = array_slice($aiInsights, 0, 5);
}
} catch (Throwable $e) {
$aiInsights = [];
$aiInsightsTop = [];
}
include 'includes/header.php';
?>
<div class="container-fluid px-4 py-4">
<div class="d-flex flex-column flex-md-row justify-content-between align-items-md-center gap-2 mb-4">
<div>
<h1 class="h3 fw-bold mb-1">Financial Insights</h1>
<div class="text-muted small">Read-only snapshot · <?= htmlspecialchars($now->format('M d, Y')) ?></div>
</div>
<div class="d-flex gap-2">
<a class="btn btn-sm btn-outline-secondary" href="executive-dashboard.php">Executive Dashboard</a>
</div>
</div>
<style>
.ceo-kpi { border:1px solid rgba(15,23,42,.08); border-radius:16px; padding:14px 14px; background:#fff; box-shadow:0 8px 24px rgba(2,6,23,.06); height:100%; }
.ceo-kpi .k { font-size:12px; font-weight:800; letter-spacing:.06em; text-transform:uppercase; color:#64748b; }
.ceo-kpi .v { font-size:22px; font-weight:900; color:#0f172a; margin-top:6px; }
.ceo-kpi .t { font-size:12px; color:#64748b; margin-top:6px; }
.ceo-pill { display:inline-flex; align-items:center; gap:6px; padding:4px 10px; border-radius:999px; border:1px solid rgba(15,23,42,.12); background:rgba(2,6,23,.03); font-size:12px; font-weight:800; }
.ceo-pill.up { color:#166534; background:rgba(34,197,94,.10); border-color:rgba(34,197,94,.22); }
.ceo-pill.down { color:#991b1b; background:rgba(239,68,68,.10); border-color:rgba(239,68,68,.22); }
.ceo-pill.neutral { color:#334155; background:rgba(148,163,184,.12); border-color:rgba(148,163,184,.22); }
.ceo-card { border:1px solid rgba(15,23,42,.08); border-radius:16px; background:#fff; box-shadow:0 8px 24px rgba(2,6,23,.06); }
.ceo-card .card-header { background:transparent; border-bottom:1px solid rgba(15,23,42,.08); padding:12px 14px; font-weight:900; }
.ceo-card .card-body { padding:14px; }
.ceo-bar { height:10px; border-radius:999px; background:rgba(2,6,23,.06); overflow:hidden; }
.ceo-bar > span { display:block; height:100%; border-radius:999px; }
.ceo-row { display:flex; align-items:center; justify-content:space-between; gap:10px; }
.ceo-muted { color:#64748b; }
.ceo-decision-grid{display:grid;grid-template-columns:repeat(3,1fr);gap:12px}
@media (max-width: 992px){.ceo-decision-grid{grid-template-columns:1fr}}
.ceo-decision{border:1px solid rgba(15,23,42,.08);border-radius:14px;padding:12px 12px;background:rgba(2,6,23,.02);text-decoration:none;color:#0f172a;display:flex;justify-content:space-between;gap:10px}
.ceo-decision .ttl{font-size:12px;font-weight:900;letter-spacing:.06em;text-transform:uppercase;color:#64748b}
.ceo-decision .val{font-size:18px;font-weight:900;margin-top:2px}
.ceo-insight{border:1px solid rgba(15,23,42,.08);border-radius:14px;padding:12px 12px;background:#fff}
.ceo-insight.risk{border-color:rgba(239,68,68,.35);background:rgba(239,68,68,.06)}
.ceo-insight.warning{border-color:rgba(245,158,11,.40);background:rgba(245,158,11,.08)}
.ceo-insight.info{border-color:rgba(34,197,94,.32);background:rgba(34,197,94,.07)}
</style>
<div class="ceo-card mb-4">
<div class="card-body">
<div class="d-flex flex-column flex-md-row align-items-md-center justify-content-between gap-2">
<div class="fw-bold" style="font-size:18px">
<?= htmlspecialchars($netThisMonth >= 0 ? 'Business is profitable this month' : 'Business is operating at a loss') ?>
</div>
<span class="ceo-pill <?= $netThisMonth >= 0 ? 'up' : 'down' ?>"><?= htmlspecialchars(formatCurrency($netThisMonth)) ?></span>
</div>
<div class="text-muted small mt-1">
Revenue <?= htmlspecialchars(formatCurrency($revThisMonth)) ?> · Expenses <?= htmlspecialchars(formatCurrency($expThisMonth)) ?>
</div>
</div>
</div>
<div class="row g-3 mb-4">
<div class="col-12 col-md-6 col-xl-3">
<div class="ceo-kpi">
<div class="d-flex justify-content-between align-items-start gap-2">
<div class="k">Total Revenue</div>
<?php $tr = $trendPill($revThisMonth, $revPrevMonth, true); ?>
<span class="ceo-pill <?= htmlspecialchars($tr['class']) ?>"><?= htmlspecialchars($tr['text']) ?></span>
</div>
<div class="v"><?= htmlspecialchars(formatCurrency($revThisMonth)) ?></div>
<div class="t">All-time <?= htmlspecialchars(formatCurrency($revAllTime)) ?></div>
</div>
</div>
<div class="col-12 col-md-6 col-xl-3">
<div class="ceo-kpi">
<div class="d-flex justify-content-between align-items-start gap-2">
<div class="k">Total Expenses</div>
<?php $tr = $trendPill($expThisMonth, $expPrevMonth, false); ?>
<span class="ceo-pill <?= htmlspecialchars($tr['class']) ?>"><?= htmlspecialchars($tr['text']) ?></span>
</div>
<div class="v"><?= htmlspecialchars(formatCurrency($expThisMonth)) ?></div>
<div class="t">All-time <?= htmlspecialchars(formatCurrency($expAllTime)) ?></div>
</div>
</div>
<div class="col-12 col-md-6 col-xl-3">
<div class="ceo-kpi">
<div class="d-flex justify-content-between align-items-start gap-2">
<div class="k">Net Profit</div>
<?php $tr = $trendPill($netThisMonth, $netPrevMonth, true); ?>
<span class="ceo-pill <?= htmlspecialchars($tr['class']) ?>"><?= htmlspecialchars($tr['text']) ?></span>
</div>
<div class="v" style="color:<?= $netThisMonth >= 0 ? '#166534' : '#991b1b' ?>"><?= htmlspecialchars(formatCurrency($netThisMonth)) ?></div>
<div class="t">All-time <?= htmlspecialchars(formatCurrency($netAllTime)) ?></div>
</div>
</div>
<div class="col-12 col-md-6 col-xl-3">
<div class="ceo-kpi">
<div class="d-flex justify-content-between align-items-start gap-2">
<div class="k">Cash Balance</div>
<span class="ceo-pill"><?= count($bankPositions) ?> acct</span>
</div>
<div class="v"><?= htmlspecialchars(formatCurrency($cashBalance)) ?></div>
<div class="t">Sum of finance accounts</div>
</div>
</div>
</div>
<div class="row g-3 mb-4">
<div class="col-12">
<div class="ceo-card">
<div class="card-header">AI Financial Insights</div>
<div class="card-body">
<?php if (empty($aiInsightsTop)): ?>
<div class="text-muted">No insights available for this period yet.</div>
<?php else: ?>
<div class="row g-2">
<?php foreach ($aiInsightsTop as $ins): ?>
<?php
$t = strtolower(trim((string)($ins['type'] ?? 'info')));
if (!in_array($t, ['risk','warning','info'], true)) $t = 'info';
?>
<div class="col-12 col-lg-6">
<div class="ceo-insight <?= htmlspecialchars($t) ?>">
<div class="d-flex justify-content-between gap-2">
<div class="fw-bold"><?= htmlspecialchars((string)($ins['title'] ?? 'Insight')) ?></div>
<span class="ceo-pill <?= $t === 'risk' ? 'down' : ($t === 'warning' ? 'neutral' : 'up') ?>"><?= htmlspecialchars(strtoupper($t)) ?></span>
</div>
<div class="text-muted small mt-1"><?= htmlspecialchars((string)($ins['description'] ?? '')) ?></div>
<div class="mt-2 fw-bold" style="font-size:12px;letter-spacing:.06em;text-transform:uppercase;color:#64748b">Recommendation</div>
<div class="small"><?= htmlspecialchars((string)($ins['recommendation'] ?? '')) ?></div>
</div>
</div>
<?php endforeach; ?>
</div>
<?php endif; ?>
</div>
</div>
</div>
</div>
<div class="row g-3 mb-4">
<div class="col-12 col-xl-8">
<div class="ceo-card h-100">
<div class="card-header">Profit by Estate (This Month)</div>
<div class="card-body">
<?php
$maxAbs = 0.0;
foreach ($profitTop as $r) { $maxAbs = max($maxAbs, abs((float)$r['profit'])); }
if ($maxAbs <= 0) $maxAbs = 1.0;
?>
<?php if (empty($profitTop)): ?>
<div class="text-muted">No estate-level financial activity detected in this period.</div>
<?php else: ?>
<?php foreach ($profitTop as $idx => $r): ?>
<?php
$p = (float)$r['profit'];
$w = min(100, max(3, (abs($p) / $maxAbs) * 100));
$color = $p >= 0 ? '#22c55e' : '#ef4444';
?>
<div class="mb-3">
<div class="ceo-row">
<div class="fw-bold d-flex align-items-center gap-2">
<span class="ceo-pill neutral">#<?= (int)$idx + 1 ?></span>
<span><?= htmlspecialchars((string)$r['estate']) ?></span>
<span class="ceo-pill <?= $p >= 0 ? 'up' : 'down' ?>"><?= htmlspecialchars($p >= 0 ? 'Profit' : 'Loss') ?></span>
</div>
<div class="fw-bold" style="color:<?= $p >= 0 ? '#166534' : '#991b1b' ?>"><?= htmlspecialchars(formatCurrency($p)) ?></div>
</div>
<div class="ceo-muted small d-flex justify-content-between">
<span>Revenue <?= htmlspecialchars(formatCurrency((float)$r['revenue'])) ?></span>
<span>Expenses <?= htmlspecialchars(formatCurrency((float)$r['expenses'])) ?></span>
</div>
<div class="ceo-bar mt-2"><span style="width:<?= htmlspecialchars(number_format($w, 2)) ?>%; background:<?= $color ?>"></span></div>
</div>
<?php endforeach; ?>
<?php endif; ?>
</div>
</div>
</div>
<div class="col-12 col-xl-4">
<div class="ceo-card h-100">
<div class="card-header">Top Contributors</div>
<div class="card-body">
<?php $topEstate = $profitTop[0] ?? null; ?>
<div class="mb-3">
<div class="text-muted small">Top estate (by profit)</div>
<div class="fw-bold"><?= htmlspecialchars($topEstate ? (string)$topEstate['estate'] : '—') ?></div>
<div class="ceo-muted small"><?= htmlspecialchars($topEstate ? formatCurrency((float)$topEstate['profit']) : '—') ?></div>
</div>
<div class="mb-3">
<div class="text-muted small">Top vendor (by expenses)</div>
<div class="fw-bold"><?= htmlspecialchars((string)$topVendor['name']) ?></div>
<div class="ceo-muted small"><?= htmlspecialchars(($topVendor['name'] ?? '—') !== '—' ? formatCurrency((float)$topVendor['amount']) : '—') ?></div>
</div>
<div>
<div class="text-muted small">Top marketer (by revenue)</div>
<div class="fw-bold"><?= htmlspecialchars((string)$topMarketer['name']) ?></div>
<div class="ceo-muted small"><?= htmlspecialchars(($topMarketer['name'] ?? '—') !== '—' ? formatCurrency((float)$topMarketer['amount']) : '—') ?></div>
</div>
</div>
</div>
</div>
</div>
<div class="row g-3 mb-4">
<div class="col-12 col-xl-8">
<div class="ceo-card h-100">
<div class="card-header">Cashflow Overview + Alerts (Last 6 Months)</div>
<div class="card-body">
<canvas id="cashflowChart" height="110"></canvas>
<div class="d-flex flex-wrap gap-2 mt-3 small">
<span class="ceo-pill">Inflow: <?= htmlspecialchars(formatCurrency(array_sum($inflow))) ?></span>
<span class="ceo-pill">Outflow: <?= htmlspecialchars(formatCurrency(array_sum($outflow))) ?></span>
<span class="ceo-pill">Net: <?= htmlspecialchars(formatCurrency(array_sum($netflow))) ?></span>
</div>
</div>
</div>
</div>
<div class="col-12 col-xl-4">
<div class="ceo-card h-100">
<div class="card-header">Alerts</div>
<div class="card-body">
<div class="mb-3">
<div class="text-muted small fw-bold">Financial Risks</div>
<div class="d-flex justify-content-between align-items-center py-2 border-bottom">
<div>High expense spike</div>
<?php if ($spikeAlert): ?>
<span class="ceo-pill <?= $spikeSeverity === 'danger' ? 'down' : 'neutral' ?>"><?= htmlspecialchars(number_format($spikePct, 1)) ?>%</span>
<?php else: ?>
<span class="ceo-pill neutral">Stable</span>
<?php endif; ?>
</div>
<div class="d-flex justify-content-between align-items-center py-2">
<div>Overdue payments</div>
<span class="ceo-pill <?= $overduePayments > 0 ? 'down' : 'neutral' ?>"><?= (int)$overduePayments ?></span>
</div>
</div>
<div class="mb-3">
<div class="text-muted small fw-bold">Pending Approvals</div>
<div class="d-flex justify-content-between align-items-center py-2">
<div>Expense approvals</div>
<span class="ceo-pill <?= $pendingApprovals > 0 ? 'down' : 'neutral' ?>"><?= (int)$pendingApprovals ?></span>
</div>
</div>
<div>
<div class="text-muted small fw-bold">Cash Warnings</div>
<div class="d-flex justify-content-between align-items-center py-2">
<div>Low balance accounts</div>
<span class="ceo-pill <?= $lowBalanceCount > 0 ? 'down' : 'neutral' ?>"><?= (int)$lowBalanceCount ?></span>
</div>
<div class="text-muted small">Threshold: <?= htmlspecialchars(formatCurrency($lowBalanceThreshold)) ?></div>
</div>
</div>
</div>
</div>
</div>
</div>
<script>
(function(){
var el = document.getElementById('cashflowChart');
if (!el || typeof Chart === 'undefined') return;
var ctx = el.getContext('2d');
var fmt = function(v){
var n = Number(v || 0);
try { return new Intl.NumberFormat(undefined, { style: 'currency', currency: 'NGN', maximumFractionDigits: 0 }).format(n); } catch (e) {}
return (n < 0 ? '-' : '') + '₦' + Math.abs(n).toLocaleString();
};
new Chart(ctx, {
type: 'bar',
data: {
labels: <?= json_encode($months) ?>,
datasets: [
{ label: 'Inflow', data: <?= json_encode($inflow) ?>, backgroundColor: 'rgba(34,197,94,.55)', borderColor: 'rgba(34,197,94,.9)', borderWidth: 1, borderRadius: 8 },
{ label: 'Outflow', data: <?= json_encode($outflow) ?>, backgroundColor: 'rgba(239,68,68,.45)', borderColor: 'rgba(239,68,68,.9)', borderWidth: 1, borderRadius: 8 },
{ type: 'line', label: 'Net', data: <?= json_encode($netflow) ?>, borderColor: 'rgba(37,99,235,.95)', backgroundColor: 'rgba(37,99,235,.10)', tension: .35, pointRadius: 3, pointHoverRadius: 6 }
]
},
options: {
responsive: true,
plugins: {
legend: { position: 'bottom' },
tooltip: {
callbacks: {
label: function(ctx){
var lbl = (ctx.dataset && ctx.dataset.label) ? (ctx.dataset.label + ': ') : '';
return lbl + fmt(ctx.parsed.y);
}
}
}
},
scales: {
y: {
beginAtZero: true,
ticks: { callback: function(value){ return fmt(value); } }
}
}
}
});
})();
</script>
<?php
include 'includes/footer.php';
exit;
}?><?php
// Handle Executive Refund Decisions (approve/reject) with reason capture
if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['exec_refund_action'], $_POST['refund_id'])) {
$action = $_POST['exec_refund_action'];
$rid = (int)$_POST['refund_id'];
$reason = trim($_POST['refund_reason'] ?? '');
$allowed_actions = ['approve','reject'];
if (!in_array($action, $allowed_actions)) {
header("Location: executive-dashboard.php?notice=" . urlencode('Invalid action') . "&type=danger");
exit;
}
if ($reason === '') {
header("Location: executive-dashboard.php?notice=" . urlencode('Reason is required') . "&type=danger");
exit;
}
try {
$status = $action === 'approve' ? 'approved' : 'rejected';
$sql = "UPDATE refunds SET status = ?" . (function_exists('tableHasColumn') && tableHasColumn('refunds','updated_at') ? ", updated_at = CURRENT_TIMESTAMP" : "") . " WHERE id = ?";
$stmt = $pdo->prepare($sql);
$stmt->execute([$status, $rid]);
logActivity($_SESSION['user_id'], 'Refund Decision', "Refund #$rid $status: $reason");
header("Location: executive-dashboard.php?notice=" . urlencode('Decision recorded') . "&type=success");
exit;
} catch (Exception $e) {
header("Location: executive-dashboard.php?notice=" . urlencode('Failed to record decision') . "&type=danger");
exit;
}
}
if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['exec_action']) && $_POST['exec_action'] === 'update_commission_settings') {
$allowEditPolicy = in_array(strtolower((string)($_SESSION['user_role'] ?? '')), ['super_admin','chairman_ceo']);
if (!$allowEditPolicy) {
header("Location: executive-dashboard.php?notice=" . urlencode('Unauthorized to update commission settings') . "&type=danger");
exit;
}
$uid = $_SESSION['user_id'] ?? null;
$globalPct = isset($_POST['global_commission_pct']) ? (float)$_POST['global_commission_pct'] : null;
try {
$old = getSetting('commission_global_pct', '');
if ($globalPct !== null) {
$stmt = $pdo->prepare("INSERT INTO system_settings (setting_key, setting_value) VALUES ('commission_global_pct', ?) ON DUPLICATE KEY UPDATE setting_value = VALUES(setting_value)");
$stmt->execute([strval($globalPct)]);
$detail = json_encode(['key'=>'commission_global_pct','old'=>$old,'new'=>strval($globalPct)]);
logActivity($uid, 'UPDATE_COMMISSION_GLOBAL', $detail);
}
} catch (Exception $e) {}
try {
$hasCfg = function_exists('tableHasColumn') ? tableHasColumn('estates','config_json') : false;
if (!$hasCfg) {
try { $pdo->exec("ALTER TABLE estates ADD COLUMN config_json TEXT NULL"); } catch (Exception $e) {}
}
$overrides = isset($_POST['override']) && is_array($_POST['override']) ? $_POST['override'] : [];
foreach ($overrides as $eid => $pct) {
$eid = (int)$eid;
if ($eid <= 0) continue;
$pctVal = ($pct === '' || $pct === null) ? null : (float)$pct;
$row = null;
try {
$st = $pdo->prepare("SELECT config_json FROM estates WHERE id = ? LIMIT 1");
$st->execute([$eid]);
$row = $st->fetch(PDO::FETCH_ASSOC);
} catch (Exception $e) {}
$cfg = [];
if ($row && isset($row['config_json']) && $row['config_json']) {
try { $cfg = json_decode($row['config_json'], true); if (!is_array($cfg)) $cfg = []; } catch (Exception $e) { $cfg = []; }
}
$old = $cfg['commission_override_pct'] ?? null;
if ($pctVal === null) {
unset($cfg['commission_override_pct']);
} else {
$cfg['commission_override_pct'] = $pctVal;
}
$json = json_encode($cfg);
try {
$up = $pdo->prepare("UPDATE estates SET config_json = ? WHERE id = ?");
$up->execute([$json, $eid]);
$detail = json_encode(['estate_id'=>$eid,'field'=>'commission_override_pct','old'=>$old,'new'=>$pctVal]);
logActivity($uid, 'UPDATE_COMMISSION_OVERRIDE', $detail);
} catch (Exception $e) {}
}
header("Location: executive-dashboard.php?notice=" . urlencode('Commission settings updated') . "&type=success");
exit;
} catch (Exception $e) {
header("Location: executive-dashboard.php?notice=" . urlencode('Failed to update commission settings') . "&type=danger");
exit;
}
}
if (!function_exists('safeDivide')) {
function safeDivide($a, $b) {
return ((float)$b === 0.0) ? 0.0 : ((float)$a / (float)$b);
}
}
if (!function_exists('executiveTableExists')) {
function executiveTableExists($pdo, $tableName) {
try {
$stmt = $pdo->prepare("SHOW TABLES LIKE ?");
$stmt->execute([$tableName]);
return $stmt->rowCount() > 0;
} catch (Throwable $e) {
return false;
}
}
}
if (!function_exists('getExecutiveAlerts')) {
function getExecutiveAlerts($pdo, $companyId = null) {
if (!executiveTableExists($pdo, 'deals_submit')) return [];
if (function_exists('tableHasColumn') && (!tableHasColumn('deals_submit', 'status') || !tableHasColumn('deals_submit', 'amount_paid_so_far'))) return [];
try {
$sql = "
SELECT COALESCE(SUM(amount_paid_so_far), 0) as total, COUNT(*) as count
FROM deals_submit
WHERE status IN ('pending', 'pending_verification')
";
$params = [];
if ($companyId && function_exists('tableHasColumn') && tableHasColumn('deals_submit', 'company_id')) {
$sql .= " AND company_id = ? ";
$params[] = $companyId;
}
$stmt = $pdo->prepare($sql);
$stmt->execute($params);
$row = $stmt->fetch(PDO::FETCH_ASSOC) ?: [];
$total = (float)($row['total'] ?? 0);
$count = (int)($row['count'] ?? 0);
if ($count <= 0) return [];
return [[
'type' => $total > 50000000 ? 'danger' : 'warning',
'count' => $count,
'total' => $total,
'message' => $count . ' payment(s) require approval (' . formatCurrency($total) . ' at risk)'
]];
} catch (Throwable $e) {
return [];
}
}
}
if (!function_exists('getDataWarnings')) {
function getDataWarnings($pdo, $companyId = null, array $dashboardCounters = []) {
$warnings = [];
try {
if (array_key_exists('pending_submissions', $dashboardCounters) && executiveTableExists($pdo, 'deals_submit') && function_exists('tableHasColumn') && tableHasColumn('deals_submit', 'status')) {
$sql = "SELECT COUNT(*) FROM deals_submit WHERE status IN ('pending', 'pending_verification')";
$params = [];
if ($companyId && tableHasColumn('deals_submit', 'company_id')) {
$sql .= " AND company_id = ?";
$params[] = $companyId;
}
$stmt = $pdo->prepare($sql);
$stmt->execute($params);
if ((int)$dashboardCounters['pending_submissions'] !== (int)$stmt->fetchColumn()) $warnings[] = 'Data syncing... please refresh.';
}
if (array_key_exists('overdue_installments', $dashboardCounters) && executiveTableExists($pdo, 'installments') && function_exists('tableHasColumn') && tableHasColumn('installments', 'due_date') && tableHasColumn('installments', 'status')) {
$sql = "SELECT COUNT(*) FROM installments WHERE due_date < CURRENT_DATE() AND status = 'overdue'";
$params = [];
if ($companyId && tableHasColumn('installments', 'company_id')) {
$sql .= " AND company_id = ?";
$params[] = $companyId;
}
$stmt = $pdo->prepare($sql);
$stmt->execute($params);
if ((int)$dashboardCounters['overdue_installments'] !== (int)$stmt->fetchColumn()) $warnings[] = 'Risk monitoring is refreshing with live records.';
}
} catch (Throwable $e) {
}
return array_values(array_unique($warnings));
}
}
if (!function_exists('getRevenueInsights')) {
function getRevenueInsights($pdo, $companyId = null) {
$empty = ['current' => 0.0, 'previous' => 0.0, 'change' => 0.0, 'text' => 'Revenue projection will appear as live submission values accumulate.'];
if (!executiveTableExists($pdo, 'deals_submit')) return $empty;
if (function_exists('tableHasColumn') && (!tableHasColumn('deals_submit', 'amount_paid_so_far') || !tableHasColumn('deals_submit', 'created_at'))) return $empty;
try {
$currentStart = (new DateTimeImmutable('first day of this month'))->format('Y-m-d 00:00:00');
$nextStart = (new DateTimeImmutable('first day of next month'))->format('Y-m-d 00:00:00');
$previousStart = (new DateTimeImmutable('first day of last month'))->format('Y-m-d 00:00:00');
$currentSql = "SELECT COALESCE(SUM(amount_paid_so_far), 0) FROM deals_submit WHERE created_at >= ? AND created_at < ?";
$currentParams = [$currentStart, $nextStart];
$previousSql = "SELECT COALESCE(SUM(amount_paid_so_far), 0) FROM deals_submit WHERE created_at >= ? AND created_at < ?";
$previousParams = [$previousStart, $currentStart];
if ($companyId && function_exists('tableHasColumn') && tableHasColumn('deals_submit', 'company_id')) {
$currentSql .= " AND (company_id = ? OR company_id IS NULL)";
$previousSql .= " AND (company_id = ? OR company_id IS NULL)";
$currentParams[] = $companyId;
$previousParams[] = $companyId;
}
$stmt = $pdo->prepare($currentSql);
$stmt->execute($currentParams);
$current = (float)($stmt->fetchColumn() ?: 0);
$stmt = $pdo->prepare($previousSql);
$stmt->execute($previousParams);
$previous = (float)($stmt->fetchColumn() ?: 0);
$change = safeDivide(($current - $previous), ($previous ?: 1)) * 100;
if ($current <= 0 && $previous <= 0) return $empty;
if ($previous < 10000) {
$text = $current >= $previous
? 'Revenue increased significantly this period.'
: 'Revenue declined significantly this period.';
} elseif (abs($change) > 200) {
$text = $change >= 0
? 'Revenue increased significantly this period.'
: 'Revenue declined significantly this period.';
} else {
$text = 'Revenue changed by ' . round($change, 1) . '% vs last period.';
}
return ['current' => $current, 'previous' => $previous, 'change' => $change, 'text' => $text];
} catch (Throwable $e) {
return $empty;
}
}
}
if (!function_exists('getRevenueChartData')) {
function getRevenueChartData($pdo, $startDate = null, $endDate = null, $companyId = null) {
$startDate = $startDate ?: date('Y-m-01');
$endDate = $endDate ?: date('Y-m-t');
$empty = ['labels' => [], 'values' => [], 'map' => []];
if (!executiveTableExists($pdo, 'deals_submit')) return $empty;
if (function_exists('tableHasColumn') && (!tableHasColumn('deals_submit', 'created_at') || !tableHasColumn('deals_submit', 'amount_paid_so_far'))) return $empty;
try {
$sql = "
SELECT DATE(created_at) as day, COALESCE(SUM(amount_paid_so_far), 0) as total
FROM deals_submit
WHERE created_at BETWEEN :start AND :end
";
if ($companyId && function_exists('tableHasColumn') && tableHasColumn('deals_submit', 'company_id')) {
$sql .= " AND (company_id = :company_id OR company_id IS NULL)";
}
$sql .= " GROUP BY DATE(created_at) ORDER BY day ASC";
$stmt = $pdo->prepare($sql);
$params = [
':start' => $startDate . ' 00:00:00',
':end' => $endDate . ' 23:59:59'
];
if ($companyId && function_exists('tableHasColumn') && tableHasColumn('deals_submit', 'company_id')) {
$params[':company_id'] = $companyId;
}
$stmt->execute($params);
$data = $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
if (empty($data)) {
$fallbackSql = "
SELECT DATE(created_at) as day, COALESCE(SUM(amount_paid_so_far), 0) as total
FROM deals_submit
WHERE created_at >= :fallback_start
";
$fallbackParams = [
':fallback_start' => date('Y-m-d 00:00:00', strtotime('-30 days'))
];
if ($companyId && function_exists('tableHasColumn') && tableHasColumn('deals_submit', 'company_id')) {
$fallbackSql .= " AND (company_id = :company_id OR company_id IS NULL)";
$fallbackParams[':company_id'] = $companyId;
}
$fallbackSql .= " GROUP BY DATE(created_at) ORDER BY day ASC";
$fallbackStmt = $pdo->prepare($fallbackSql);
$fallbackStmt->execute($fallbackParams);
$data = $fallbackStmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
}
$labels = [];
$values = [];
$map = [];
foreach ($data as $row) {
$day = (string)($row['day'] ?? '');
if ($day === '') continue;
$total = (float)($row['total'] ?? 0);
$labels[] = date('j M', strtotime($day));
$values[] = $total;
$map[$day] = $total;
}
return ['labels' => $labels, 'values' => $values, 'map' => $map];
} catch (Throwable $e) {
return $empty;
}
}
}
if (!function_exists('getRevenueComparisonData')) {
function getRevenueComparisonData($pdo, $companyId = null) {
$empty = ['thisMonth' => [], 'lastMonth' => []];
if (!executiveTableExists($pdo, 'deals_submit')) return $empty;
if (function_exists('tableHasColumn') && (!tableHasColumn('deals_submit', 'created_at') || !tableHasColumn('deals_submit', 'amount_paid_so_far'))) return $empty;
$fetchPeriod = function ($startDate, $endDate) use ($pdo, $companyId) {
try {
$sql = "
SELECT DATE(created_at) as day, COALESCE(SUM(amount_paid_so_far), 0) as total
FROM deals_submit
WHERE created_at BETWEEN :start AND :end
";
$params = [
':start' => $startDate . ' 00:00:00',
':end' => $endDate . ' 23:59:59'
];
if ($companyId && function_exists('tableHasColumn') && tableHasColumn('deals_submit', 'company_id')) {
$sql .= " AND (company_id = :company_id OR company_id IS NULL)";
$params[':company_id'] = $companyId;
}
$sql .= " GROUP BY DATE(created_at) ORDER BY day ASC";
$stmt = $pdo->prepare($sql);
$stmt->execute($params);
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
return array_map(function ($row) {
return [
'day' => (string)($row['day'] ?? ''),
'total' => (float)($row['total'] ?? 0)
];
}, $rows);
} catch (Throwable $e) {
return [];
}
};
$thisMonthStart = date('Y-m-01');
$thisMonthEnd = date('Y-m-t');
$lastMonthStart = date('Y-m-01', strtotime('first day of last month'));
$lastMonthEnd = date('Y-m-t', strtotime('last day of last month'));
return [
'thisMonth' => $fetchPeriod($thisMonthStart, $thisMonthEnd),
'lastMonth' => $fetchPeriod($lastMonthStart, $lastMonthEnd)
];
}
}
if (!function_exists('getProjectedRevenue')) {
function getProjectedRevenue($pdo, $companyId = null) {
$empty = ['current' => 0.0, 'projected' => 0.0, 'delta' => 0.0, 'daysElapsed' => 0, 'daysInMonth' => 0];
if (!executiveTableExists($pdo, 'deals_submit')) return $empty;
if (function_exists('tableHasColumn') && (!tableHasColumn('deals_submit', 'created_at') || !tableHasColumn('deals_submit', 'amount_paid_so_far'))) return $empty;
try {
$startDate = date('Y-m-01 00:00:00');
$nextMonth = date('Y-m-01 00:00:00', strtotime('first day of next month'));
$sql = "SELECT COALESCE(SUM(amount_paid_so_far), 0) FROM deals_submit WHERE created_at >= ? AND created_at < ?";
$params = [$startDate, $nextMonth];
if ($companyId && function_exists('tableHasColumn') && tableHasColumn('deals_submit', 'company_id')) {
$sql .= " AND (company_id = ? OR company_id IS NULL)";
$params[] = $companyId;
}
$stmt = $pdo->prepare($sql);
$stmt->execute($params);
$current = (float)($stmt->fetchColumn() ?: 0);
$daysElapsed = max(1, (int)date('j'));
$daysInMonth = (int)date('t');
$projected = ($current > 0 && $daysElapsed > 0) ? ($current / $daysElapsed) * $daysInMonth : 0.0;
$delta = $current > 0 ? $projected - $current : 0.0;
return [
'current' => $current,
'projected' => $projected,
'delta' => $delta,
'daysElapsed' => $daysElapsed,
'daysInMonth' => $daysInMonth
];
} catch (Throwable $e) {
return $empty;
}
}
}
if (!function_exists('explainRevenueSpike')) {
function explainRevenueSpike($amount) {
$amount = (float)$amount;
if ($amount > 10000000) {
return 'Spike driven by high-value bulk payments.';
}
if ($amount > 5000000) {
return 'Increase due to multiple mid-size transactions.';
}
return 'Normal transaction activity.';
}
}
if (!function_exists('getRevenueSpikes')) {
function getRevenueSpikes($pdo, $companyId = null) {
$spikes = [];
if (!executiveTableExists($pdo, 'deals_submit')) return $spikes;
if (function_exists('tableHasColumn') && (!tableHasColumn('deals_submit', 'created_at') || !tableHasColumn('deals_submit', 'amount_paid_so_far'))) return $spikes;
try {
$threshold = function_exists('getSetting') ? (float)(getSetting('exec_revenue_spike_threshold', 5000000) ?: 5000000) : 5000000;
$sql = "
SELECT DATE(created_at) as day, COALESCE(SUM(amount_paid_so_far), 0) as total
FROM deals_submit
GROUP BY DATE(created_at)
";
$params = [];
if ($companyId && function_exists('tableHasColumn') && tableHasColumn('deals_submit', 'company_id')) {
$sql = "
SELECT DATE(created_at) as day, COALESCE(SUM(amount_paid_so_far), 0) as total
FROM deals_submit
WHERE (company_id = ? OR company_id IS NULL)
GROUP BY DATE(created_at)
";
$params[] = $companyId;
}
$stmt = $pdo->prepare($sql);
$stmt->execute($params);
foreach (($stmt->fetchAll(PDO::FETCH_ASSOC) ?: []) as $row) {
$total = (float)($row['total'] ?? 0);
if ($total >= $threshold) {
$spikes[] = [
'date' => (string)($row['day'] ?? ''),
'amount' => $total,
'reason' => explainRevenueSpike($total)
];
}
}
return $spikes;
} catch (Throwable $e) {
return [];
}
}
}
if (!function_exists('getRiskSummary')) {
function getRiskSummary($pdo, $companyId = null) {
try {
if (executiveTableExists($pdo, 'installments') && function_exists('tableHasColumn') && tableHasColumn('installments', 'due_date') && tableHasColumn('installments', 'status')) {
$sql = "SELECT COUNT(*) FROM installments WHERE due_date < CURRENT_DATE() AND status = 'overdue'";
$params = [];
if ($companyId && tableHasColumn('installments', 'company_id')) {
$sql .= " AND company_id = ?";
$params[] = $companyId;
}
$stmt = $pdo->prepare($sql);
$stmt->execute($params);
$overdue = (int)($stmt->fetchColumn() ?: 0);
if ($overdue > 0) return ['status' => 'danger', 'message' => $overdue . ' overdue instalment(s) detected'];
} elseif (executiveTableExists($pdo, 'payments') && function_exists('tableHasColumn') && tableHasColumn('payments', 'due_date') && tableHasColumn('payments', 'status')) {
$sql = "SELECT COUNT(*) FROM payments WHERE due_date < CURRENT_DATE() AND status NOT IN ('paid','approved','verified','completed','success')";
$params = [];
if ($companyId && tableHasColumn('payments', 'company_id')) {
$sql .= " AND company_id = ?";
$params[] = $companyId;
}
$stmt = $pdo->prepare($sql);
$stmt->execute($params);
$overdue = (int)($stmt->fetchColumn() ?: 0);
if ($overdue > 0) return ['status' => 'danger', 'message' => $overdue . ' overdue instalment(s) detected'];
}
} catch (Throwable $e) {
}
return ['status' => 'safe', 'message' => 'No financial risks detected'];
}
}
if (!function_exists('getApprovalVelocity')) {
function getApprovalVelocity($pdo, $companyId = null) {
try {
if (executiveTableExists($pdo, 'deals_submit') && function_exists('tableHasColumn') && tableHasColumn('deals_submit', 'status') && tableHasColumn('deals_submit', 'created_at') && tableHasColumn('deals_submit', 'updated_at')) {
$sql = "SELECT AVG(TIMESTAMPDIFF(HOUR, created_at, updated_at)) FROM deals_submit WHERE status IN ('approved','completed')";
$params = [];
if ($companyId && tableHasColumn('deals_submit', 'company_id')) {
$sql .= " AND company_id = ?";
$params[] = $companyId;
}
$stmt = $pdo->prepare($sql);
$stmt->execute($params);
$avgHours = (float)($stmt->fetchColumn() ?: 0);
if ($avgHours > 0) return 'Average approval time: ' . round($avgHours, 1) . ' hrs';
}
} catch (Throwable $e) {
}
return 'Approval turnaround data not sufficient';
}
}
if (!function_exists('getTopProject')) {
function getTopProject($pdo, $companyId = null) {
if (!executiveTableExists($pdo, 'deals_submit')) return 'No dominant estate trend yet';
if (function_exists('tableHasColumn') && !tableHasColumn('deals_submit', 'project_name')) return 'No dominant estate trend yet';
try {
$sql = "
SELECT project_name, COUNT(*) as total
FROM deals_submit
WHERE project_name IS NOT NULL AND TRIM(project_name) <> ''
";
$params = [];
if ($companyId && function_exists('tableHasColumn') && tableHasColumn('deals_submit', 'company_id')) {
$sql .= " AND company_id = ?";
$params[] = $companyId;
}
$sql .= " GROUP BY project_name ORDER BY total DESC, project_name ASC LIMIT 1";
$stmt = $pdo->prepare($sql);
$stmt->execute($params);
$row = $stmt->fetch(PDO::FETCH_ASSOC);
return $row ? 'Most active estate: ' . $row['project_name'] : 'No dominant estate trend yet';
} catch (Throwable $e) {
return 'No dominant estate trend yet';
}
}
}
include 'includes/header.php';
$companyId = getCurrentCompanyId();
$isChairman = ($role_norm === 'chairman_ceo');
$approvalColClass = 'col-lg-8';
$sideColClass = 'col-lg-4';
$globalCommission = getSetting('commission_global_pct', '');
$estateOverrides = [];
try {
$q = "SELECT id, name" . (function_exists('tableHasColumn') && tableHasColumn('estates','config_json') ? ", config_json" : "") . " FROM estates";
$params = [];
if ($companyId && function_exists('tableHasColumn') && tableHasColumn('estates','company_id')) { $q .= " WHERE company_id = ?"; $params[] = $companyId; }
$st = $pdo->prepare($q);
$st->execute($params);
while ($r = $st->fetch(PDO::FETCH_ASSOC)) {
$ov = null;
if (isset($r['config_json']) && $r['config_json']) {
try { $cfg = json_decode($r['config_json'], true); if (is_array($cfg) && isset($cfg['commission_override_pct'])) { $ov = $cfg['commission_override_pct']; } } catch (Exception $e) {}
}
$estateOverrides[] = ['id'=>$r['id'],'name'=>$r['name'],'override'=>$ov];
}
} catch (Exception $e) {}
$startDate = isset($_GET['start_date']) && $_GET['start_date'] ? $_GET['start_date'] . ' 00:00:00' : date('Y-m-01 00:00:00');
$endDate = isset($_GET['end_date']) && $_GET['end_date'] ? $_GET['end_date'] . ' 23:59:59' : date('Y-m-t 23:59:59');
$kpi_revenue = 0;
$kpi_collections_rate = 0;
$kpi_exec_pending_alloc = 0;
$kpi_payments_checker = 0;
$kpi_payments_checker_amount = 0;
$kpi_overdue_inst = 0;
$kpi_overdue_amount = 0;
$kpi_discounts_total = 0;
$kpi_comm_liability = 0;
$kpi_comm_paid = 0;
$kpi_revoked_accounts = 0;
$kpi_admin_fees_retained = 0;
$kpi_outstanding_exposure = 0;
$kpi_discount_roles = [];
try {
$dateCol = function_exists('kpiPaymentDateColumn') ? kpiPaymentDateColumn('payments') : 'approval_date';
$kpi_revenue = function_exists('kpiSumPayments')
? kpiSumPayments($pdo, kpiPaymentFinalizedStatuses(), $startDate, $endDate, $companyId, $dateCol, true)
: 0.0;
} catch (Exception $e) {}
// Total Discounts (period)
try {
$tblExists = $pdo->query("SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'deals'")->fetchColumn();
if ((int)$tblExists > 0) {
$cols = $pdo->query("DESCRIBE deals")->fetchAll(PDO::FETCH_ASSOC);
$colNames = array_map(fn($c) => $c['Field'], $cols);
if (in_array('discount_amount', $colNames, true)) {
// choose date column
$dateCol = in_array('created_at',$colNames,true) ? 'created_at' : (in_array('date',$colNames,true) ? 'date' : null);
$q = "SELECT COALESCE(SUM(discount_amount),0) FROM deals";
$p = [];
if ($dateCol) {
$q .= " WHERE $dateCol BETWEEN ? AND ?";
$p = [$startDate, $endDate];
}
if ($companyId && in_array('company_id',$colNames,true)) {
$q .= $dateCol ? " AND company_id = ?" : " WHERE company_id = ?";
$p[] = $companyId;
}
$st = $pdo->prepare($q); $st->execute($p);
$kpi_discounts_total = (float)($st->fetchColumn() ?: 0);
if (in_array('discount_approved_by_role',$colNames,true)) {
$qq = "SELECT discount_approved_by_role AS r, COUNT(*) AS c FROM deals";
$pp = [];
$cond = [];
if ($dateCol) { $cond[] = "$dateCol BETWEEN ? AND ?"; $pp[] = $startDate; $pp[] = $endDate; }
if ($companyId && in_array('company_id',$colNames,true)) { $cond[] = "company_id = ?"; $pp[] = $companyId; }
if (!empty($cond)) { $qq .= " WHERE " . implode(" AND ", $cond); }
$qq .= " GROUP BY r";
$st2 = $pdo->prepare($qq); $st2->execute($pp);
$tmp = $st2->fetchAll(PDO::FETCH_ASSOC);
$map = [];
foreach ($tmp as $row) {
$role = strtolower(trim((string)($row['r'] ?? 'other')));
$role = str_replace([' ', '-'], '_', $role);
if ($role === '') $role = 'other';
if (!isset($map[$role])) $map[$role] = 0;
$map[$role] += (int)($row['c'] ?? 0);
}
$kpi_discount_roles = $map;
}
}
}
} catch (Exception $e) {}
// Commission liability and paid
try {
$hasComm = $pdo->query("SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'commissions'")->fetchColumn();
if ((int)$hasComm > 0) {
$cols = $pdo->query("DESCRIBE commissions")->fetchAll(PDO::FETCH_ASSOC);
$names = array_map(fn($c)=>$c['Field'],$cols);
$amountCol = in_array('amount',$names,true) ? 'amount' : (in_array('commission_amount',$names,true) ? 'commission_amount' : null);
$statusCol = in_array('status',$names,true) ? 'status' : (in_array('commission_status',$names,true) ? 'commission_status' : (in_array('state',$names,true) ? 'state' : null));
if ($amountCol && $statusCol) {
$companyFilter = ($companyId && in_array('company_id',$names,true)) ? " AND company_id = " . (int)$companyId : "";
$liabTokens = ['payable','pending','approved','pending_payment'];
$paidTokens = ['paid','completed'];
$inList = function(array $arr){ return "('" . implode("','", array_map('strval',$arr)) . "')"; };
$st1 = $pdo->query("SELECT COALESCE(SUM($amountCol),0) FROM commissions WHERE $statusCol IN " . $inList($liabTokens) . $companyFilter);
$st2 = $pdo->query("SELECT COALESCE(SUM($amountCol),0) FROM commissions WHERE $statusCol IN " . $inList($paidTokens) . $companyFilter);
$kpi_comm_liability = (float)($st1 ? $st1->fetchColumn() : 0);
$kpi_comm_paid = (float)($st2 ? $st2->fetchColumn() : 0);
}
}
} catch (Exception $e) {}
// Revoked accounts (allocations)
try {
if ($companyId) {
$s = $pdo->prepare("SELECT COUNT(*) FROM allocations WHERE status = 'revoked' AND company_id = ?");
$s->execute([$companyId]);
} else {
$s = $pdo->query("SELECT COUNT(*) FROM allocations WHERE status = 'revoked'");
}
$kpi_revoked_accounts = (int)($s->fetchColumn() ?: 0);
} catch (Exception $e) {}
try {
$ids = [];
if ($companyId) {
$st = $pdo->prepare("SELECT id FROM allocations WHERE status = 'revoked' AND company_id = ?");
$st->execute([$companyId]);
} else {
$st = $pdo->query("SELECT id FROM allocations WHERE status = 'revoked'");
}
while ($rid = $st->fetchColumn()) {
$ids[] = (int)$rid;
}
foreach ($ids as $aid) {
$sumPaid = function_exists('kpiSumPayments')
? kpiSumPayments($pdo, kpiPaymentFinalizedStatuses(), null, null, null, null, false, " AND allocation_id = ?", [$aid])
: 0.0;
$kpi_admin_fees_retained += round($sumPaid * 0.20, 2);
}
} catch (Exception $e) {}
try {
$rows = [];
if ($companyId) {
$st = $pdo->prepare("SELECT id, property_id FROM allocations WHERE status NOT IN ('completed','revoked','rejected') AND company_id = ?");
$st->execute([$companyId]);
} else {
$st = $pdo->query("SELECT id, property_id FROM allocations WHERE status NOT IN ('completed','revoked','rejected')");
}
$rows = $st->fetchAll(PDO::FETCH_ASSOC);
foreach ($rows as $r) {
$total = allocationTotalAmount($pdo, ['property_id' => (int)$r['property_id']]);
$paid = function_exists('kpiSumPayments')
? kpiSumPayments($pdo, kpiPaymentFinalizedStatuses(), null, null, null, null, false, " AND allocation_id = ?", [(int)$r['id']])
: 0.0;
$rem = max(0.0, $total - $paid);
$kpi_outstanding_exposure += $rem;
}
} catch (Exception $e) {}
// Inventory Intelligence per Estate (Sellable/Available/Committed/Allocated/Revoked)
$inventoryRows = [];
try {
$q = "SELECT id, name FROM estates";
$params = [];
if ($companyId && function_exists('tableHasColumn') && tableHasColumn('estates','company_id')) { $q .= " WHERE company_id = ?"; $params[] = $companyId; }
$st = $pdo->prepare($q);
$st->execute($params);
$estates = $st->fetchAll(PDO::FETCH_ASSOC) ?: [];
foreach ($estates as $es) {
$inv = getEstateInventory((int)$es['id']);
$soldPct = ($inv['total_sellable_sqm'] > 0) ? round((($inv['committed_sqm'] + $inv['allocated_sqm']) / $inv['total_sellable_sqm']) * 100) : 0;
$allocPct = ($inv['total_sellable_sqm'] > 0) ? round(($inv['allocated_sqm'] / $inv['total_sellable_sqm']) * 100) : 0;
$dist = [];
try {
ensurePlotsTable();
$ps = $pdo->prepare("SELECT size_sqm, COUNT(*) AS cnt FROM plots WHERE estate_id = ? AND status = 'available' GROUP BY size_sqm ORDER BY size_sqm");
$ps->execute([(int)$es['id']]);
$dist = $ps->fetchAll(PDO::FETCH_ASSOC) ?: [];
} catch (Throwable $e) {}
$low = [];
foreach ($dist as $d) {
if ((int)$d['cnt'] <= 2) { $low[] = (float)$d['size_sqm']; }
}
$inventoryRows[] = [
'id' => (int)$es['id'],
'name' => $es['name'],
'sellable' => $inv['total_sellable_sqm'],
'available' => $inv['available_sqm'],
'committed' => $inv['committed_sqm'],
'allocated' => $inv['allocated_sqm'],
'revoked' => $inv['revoked_sqm'],
'pct_sold' => $soldPct,
'pct_alloc' => $allocPct,
'low_sizes' => $low,
'dist' => $dist
];
}
} catch (Throwable $e) {}
try {
$sumPaid = function_exists('kpiSumPayments') ? kpiSumPayments($pdo, kpiPaymentFinalizedStatuses(), null, null, $companyId, null, true) : 0.0;
$sumPend = function_exists('kpiSumPayments') ? kpiSumPayments($pdo, kpiPaymentPendingStatuses(), null, null, $companyId, null, true) : 0.0;
$base = $sumPaid + $sumPend;
$kpi_collections_rate = $base > 0 ? round(($sumPaid / $base) * 100) : 0;
} catch (Exception $e) {}
$execDocClause = '';
try {
try {
$hasDocuments = $pdo->query("SHOW TABLES LIKE 'documents'")->rowCount() > 0;
if ($hasDocuments && function_exists('tableHasColumn') && tableHasColumn('documents','user_id') && tableHasColumn('documents','type') && tableHasColumn('documents','status') && tableHasColumn('documents','file_path')) {
$execDocClause = " OR EXISTS (SELECT 1 FROM documents d WHERE d.user_id = a.user_id AND d.type = 'allocation_letter' AND d.status = 'pending_executive_approval' AND d.file_path LIKE CONCAT('%Allocation_Letter_', a.id, '_%'))";
}
} catch (Exception $e) {}
if ($companyId) {
$s = $pdo->prepare("SELECT COUNT(*) FROM allocations a WHERE (a.status = 'pending_executive_approval'" . $execDocClause . ") AND a.company_id = ?");
$s->execute([$companyId]);
} else {
$s = $pdo->query("SELECT COUNT(*) FROM allocations a WHERE (a.status = 'pending_executive_approval'" . $execDocClause . ")");
}
$kpi_exec_pending_alloc = (int)$s->fetchColumn();
} catch (Exception $e) {}
// Completed and Rejected today
$kpi_completed_today = 0;
$kpi_rejected_today = 0;
try {
$dateCol = 'updated_at';
$cols = $pdo->query("DESCRIBE allocations")->fetchAll(PDO::FETCH_ASSOC);
$names = array_map(fn($c) => $c['Field'], $cols);
if (!in_array('updated_at', $names, true) && in_array('created_at', $names, true)) { $dateCol = 'created_at'; }
if ($companyId && in_array('company_id', $names, true)) {
$st1 = $pdo->prepare("SELECT COUNT(*) FROM allocations WHERE status = 'completed' AND DATE($dateCol) = CURDATE() AND company_id = ?");
$st1->execute([$companyId]);
$kpi_completed_today = (int)$st1->fetchColumn();
$st2 = $pdo->prepare("SELECT COUNT(*) FROM allocations WHERE status = 'rejected' AND DATE($dateCol) = CURDATE() AND company_id = ?");
$st2->execute([$companyId]);
$kpi_rejected_today = (int)$st2->fetchColumn();
} else {
$st1 = $pdo->query("SELECT COUNT(*) FROM allocations WHERE status = 'completed' AND DATE($dateCol) = CURDATE()");
$kpi_completed_today = (int)$st1->fetchColumn();
$st2 = $pdo->query("SELECT COUNT(*) FROM allocations WHERE status = 'rejected' AND DATE($dateCol) = CURDATE()");
$kpi_rejected_today = (int)$st2->fetchColumn();
}
} catch (Exception $e) {}
try {
$financePendingSummary = function_exists('kpiFinancePendingSummary') ? kpiFinancePendingSummary($pdo, $companyId) : ['count' => 0, 'amount' => 0.0];
$kpi_payments_checker = (int)($financePendingSummary['count'] ?? 0);
$kpi_payments_checker_amount = (float)($financePendingSummary['amount'] ?? 0);
} catch (Exception $e) {}
try {
$ov = function_exists('kpiOverdueInstallmentsSummary') ? kpiOverdueInstallmentsSummary($pdo, $companyId, true) : ['count' => 0, 'amount' => 0.0];
$kpi_overdue_inst = (int)($ov['count'] ?? 0);
$kpi_overdue_amount = (float)($ov['amount'] ?? 0);
} catch (Exception $e) {}
$chart_labels = "[]";
$chart_values = "[]";
$revenueTrendDelta = 0;
try {
$today = new DateTimeImmutable('today');
$chartStart = $today->modify('-89 days')->format('Y-m-d 00:00:00');
$chartEnd = $today->format('Y-m-d 23:59:59');
$chartMap = [];
$approvedStatuses = function_exists('kpiSqlList') ? kpiSqlList(kpiPaymentFinalizedStatuses()) : "('verified','approved','paid','completed','success')";
$dateCol = function_exists('kpiPaymentDateColumn') ? kpiPaymentDateColumn('payments') : 'approval_date';
if ($companyId) {
$stmt = $pdo->prepare("
SELECT DATE($dateCol) as d, COALESCE(SUM(amount),0) as amt
FROM payments
WHERE status IN $approvedStatuses AND $dateCol BETWEEN ? AND ? AND company_id = ?
GROUP BY DATE($dateCol)
ORDER BY DATE($dateCol) ASC
");
$stmt->execute([$chartStart, $chartEnd, $companyId]);
} else {
$stmt = $pdo->prepare("
SELECT DATE($dateCol) as d, COALESCE(SUM(amount),0) as amt
FROM payments
WHERE status IN $approvedStatuses AND $dateCol BETWEEN ? AND ?
GROUP BY DATE($dateCol)
ORDER BY DATE($dateCol) ASC
");
$stmt->execute([$chartStart, $chartEnd]);
}
foreach (($stmt->fetchAll(PDO::FETCH_ASSOC) ?: []) as $r) {
$chartMap[(string)$r['d']] = (float)$r['amt'];
}
$labels = [];
$vals = [];
for ($i = 89; $i >= 0; $i--) {
$date = $today->modify("-{$i} days");
$key = $date->format('Y-m-d');
$labels[] = $date->format('M j');
$vals[] = (float)($chartMap[$key] ?? 0);
}
$firstHalf = array_slice($vals, 0, 45);
$secondHalf = array_slice($vals, 45);
$firstHalfTotal = array_sum($firstHalf);
$secondHalfTotal = array_sum($secondHalf);
if ($firstHalfTotal > 0) {
$revenueTrendDelta = round((($secondHalfTotal - $firstHalfTotal) / $firstHalfTotal) * 100, 1);
} elseif ($secondHalfTotal > 0) {
$revenueTrendDelta = 100;
}
$chart_labels = json_encode($labels);
$chart_values = json_encode($vals);
} catch (Exception $e) {}
$type_labels = "[]";
$type_values = "[]";
try {
$approvedStatuses = function_exists('kpiSqlList') ? kpiSqlList(kpiPaymentFinalizedStatuses()) : "('verified','approved','paid','completed','success')";
if ($companyId) {
$stmt = $pdo->prepare("
SELECT COALESCE(pr.type,'Unknown') as ptype, COALESCE(SUM(pm.amount),0) as amt
FROM payments pm
JOIN allocations a ON pm.allocation_id = a.id
JOIN properties pr ON a.property_id = pr.id
WHERE pm.status IN $approvedStatuses AND a.company_id = ?
GROUP BY ptype
ORDER BY amt DESC
");
$stmt->execute([$companyId]);
} else {
$stmt = $pdo->query("
SELECT COALESCE(pr.type,'Unknown') as ptype, COALESCE(SUM(pm.amount),0) as amt
FROM payments pm
JOIN allocations a ON pm.allocation_id = a.id
JOIN properties pr ON a.property_id = pr.id
WHERE pm.status IN $approvedStatuses
GROUP BY ptype
ORDER BY amt DESC
");
}
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
$labels = [];
$vals = [];
foreach ($rows as $r) {
$labels[] = $r['ptype'] ?: 'Unknown';
$vals[] = (float)$r['amt'];
}
$type_labels = json_encode($labels);
$type_values = json_encode($vals);
} catch (Exception $e) {}
$top_properties = [];
try {
$hasPayments = $pdo->query("SHOW TABLES LIKE 'payments'")->rowCount() > 0;
$hasAlloc = $pdo->query("SHOW TABLES LIKE 'allocations'")->rowCount() > 0;
if ($hasPayments && $hasAlloc && function_exists('tableHasColumn') && tableHasColumn('payments','allocation_id')) {
$approvedStatusesSql = function_exists('kpiSqlList') ? kpiSqlList(kpiPaymentFinalizedStatuses()) : "('verified','approved','paid','completed','success')";
$landClause = (function_exists('tableHasColumn') && tableHasColumn('payments','payment_type')) ? " AND (pm.payment_type IS NULL OR TRIM(pm.payment_type) = '' OR LOWER(TRIM(pm.payment_type)) = 'land')" : "";
$allocUserCol = (function_exists('tableHasColumn') && tableHasColumn('allocations','user_id')) ? 'user_id' : ((function_exists('tableHasColumn') && tableHasColumn('allocations','client_id')) ? 'client_id' : 'user_id');
$sql = "
SELECT
p.id,
COALESCE(p.title, p.property_name) AS property_name,
COUNT(DISTINCT a.$allocUserCol) AS total_clients,
COALESCE(SUM(pm.amount),0) AS total_revenue
FROM properties p
LEFT JOIN allocations a ON a.property_id = p.id
LEFT JOIN payments pm ON pm.allocation_id = a.id AND pm.status IN $approvedStatusesSql$landClause
";
$params = [];
if ($companyId && function_exists('tableHasColumn') && tableHasColumn('properties','company_id')) { $sql .= " WHERE (p.company_id = ? OR p.company_id IS NULL)"; $params[] = $companyId; }
$sql .= " GROUP BY p.id ORDER BY total_revenue DESC, total_clients DESC, p.id DESC LIMIT 6";
$st = $pdo->prepare($sql);
$st->execute($params);
$top_properties = $st->fetchAll(PDO::FETCH_ASSOC) ?: [];
} else {
$sql = "
SELECT
p.id,
COALESCE(p.title, p.property_name) AS property_name,
COUNT(DISTINCT t.user_id) AS total_clients,
COALESCE(SUM(t.amount),0) AS total_revenue
FROM properties p
LEFT JOIN transactions t ON p.id = t.property_id AND t.status = 'approved'
";
$params = [];
if ($companyId && function_exists('tableHasColumn') && tableHasColumn('properties','company_id')) { $sql .= " WHERE (p.company_id = ? OR p.company_id IS NULL)"; $params[] = $companyId; }
$sql .= " GROUP BY p.id ORDER BY total_revenue DESC, total_clients DESC, p.id DESC LIMIT 6";
$st = $pdo->prepare($sql);
$st->execute($params);
$top_properties = $st->fetchAll(PDO::FETCH_ASSOC) ?: [];
}
} catch (Exception $e) {}
$allocations_exec = [];
try {
if ($companyId) {
$st = $pdo->prepare("
SELECT a.id, a.reference, a.status, a.created_at, u.name as client_name, p.title as property_title, p.price
FROM allocations a
LEFT JOIN users u ON a.user_id = u.id
LEFT JOIN properties p ON a.property_id = p.id
WHERE (a.status = 'pending_executive_approval'" . $execDocClause . ") AND a.company_id = ?
ORDER BY a.created_at DESC
LIMIT 8
");
$st->execute([$companyId]);
} else {
$st = $pdo->query("
SELECT a.id, a.reference, a.status, a.created_at, u.name as client_name, p.title as property_title, p.price
FROM allocations a
LEFT JOIN users u ON a.user_id = u.id
LEFT JOIN properties p ON a.property_id = p.id
WHERE (a.status = 'pending_executive_approval'" . $execDocClause . ")
ORDER BY a.created_at DESC
LIMIT 8
");
}
$allocations_exec = $st->fetchAll(PDO::FETCH_ASSOC);
} catch (Exception $e) {}
$payments_checker = [];
try {
$financeQueueStatuses = function_exists('kpiSqlList') ? kpiSqlList(kpiFinanceQueuePaymentStatuses()) : "('pending_verification','pending_confirmation')";
if ($companyId) {
$st = $pdo->prepare("
SELECT p.id, p.amount, p.method, p.status, p.date, u.name as payer
FROM payments p
LEFT JOIN invoices i ON p.invoice_id = i.id
LEFT JOIN users u ON i.tenant_id = u.id
WHERE p.status IN $financeQueueStatuses AND p.company_id = ?
ORDER BY p.date DESC
LIMIT 8
");
$st->execute([$companyId]);
} else {
$st = $pdo->query("
SELECT p.id, p.amount, p.method, p.status, p.date, u.name as payer
FROM payments p
LEFT JOIN invoices i ON p.invoice_id = i.id
LEFT JOIN users u ON i.tenant_id = u.id
WHERE p.status IN $financeQueueStatuses
ORDER BY p.date DESC
LIMIT 8
");
}
$payments_checker = $st->fetchAll(PDO::FETCH_ASSOC);
} catch (Exception $e) {}
$high_value = [];
try {
$threshold = (float)(getSetting('exec_large_payment_threshold', 3000000) ?: 3000000);
$approvedStatuses = function_exists('kpiSqlList') ? kpiSqlList(kpiPaymentFinalizedStatuses()) : "('verified','approved','paid','completed','success')";
if ($companyId) {
$st = $pdo->prepare("
SELECT p.id, p.amount, p.method, p.status, p.date, u.name as payer
FROM payments p
LEFT JOIN invoices i ON p.invoice_id = i.id
LEFT JOIN users u ON i.tenant_id = u.id
WHERE p.status IN $approvedStatuses AND p.amount >= ? AND p.company_id = ?
ORDER BY p.amount DESC
LIMIT 8
");
$st->execute([$threshold, $companyId]);
} else {
$st = $pdo->prepare("
SELECT p.id, p.amount, p.method, p.status, p.date, u.name as payer
FROM payments p
LEFT JOIN invoices i ON p.invoice_id = i.id
LEFT JOIN users u ON i.tenant_id = u.id
WHERE p.status IN $approvedStatuses AND p.amount >= ?
ORDER BY p.amount DESC
LIMIT 8
");
$st->execute([$threshold]);
}
$high_value = $st->fetchAll(PDO::FETCH_ASSOC);
} catch (Exception $e) {}
$risk_overdues = [];
try {
if ($companyId) {
$st = $pdo->prepare("
SELECT i.id, i.due_date, i.amount_due, i.paid_amount, a.id as allocation_id, u.name as client_name, p.title as property_title
FROM installments i
LEFT JOIN allocations a ON i.allocation_id = a.id
LEFT JOIN users u ON a.user_id = u.id
LEFT JOIN properties p ON a.property_id = p.id
WHERE i.status = 'overdue' AND a.company_id = ?
ORDER BY i.due_date ASC
LIMIT 10
");
$st->execute([$companyId]);
} else {
$st = $pdo->query("
SELECT i.id, i.due_date, i.amount_due, i.paid_amount, a.id as allocation_id, u.name as client_name, p.title as property_title
FROM installments i
LEFT JOIN allocations a ON i.allocation_id = a.id
LEFT JOIN users u ON a.user_id = u.id
LEFT JOIN properties p ON a.property_id = p.id
WHERE i.status = 'overdue'
ORDER BY i.due_date ASC
LIMIT 10
");
}
$risk_overdues = $st->fetchAll(PDO::FETCH_ASSOC);
} catch (Exception $e) {}
$verificationDeskUrl = 'allocation-letters.php?view=executive&status=pending';
$approvalsDeskUrl = $verificationDeskUrl;
$paymentsDeskUrl = 'finance-payments.php';
$riskDeskUrl = 'executive-audit.php';
$financialInsightsUrl = file_exists(__DIR__ . '/executive-finance.php') ? 'executive-finance.php' : 'reports-financial.php';
$reportsExportsUrl = file_exists(__DIR__ . '/reports-financial.php') ? 'reports-financial.php' : 'reports.php';
$verificationCountText = $kpi_exec_pending_alloc === 1 ? '1 allocation requires chairman verification' : number_format($kpi_exec_pending_alloc) . ' allocations require chairman verification';
if ($kpi_exec_pending_alloc === 0) { $verificationCountText = 'No pending verifications — system is up to date'; }
$paymentsInsightText = $kpi_payments_checker === 1 ? '1 transaction awaiting finance approval' : number_format($kpi_payments_checker) . ' transactions awaiting finance approval';
if ($kpi_payments_checker === 0) { $paymentsInsightText = 'No payment approvals are waiting in finance'; }
$overdueInsightText = $kpi_overdue_inst === 1 ? '1 overdue instalment needs attention' : number_format($kpi_overdue_inst) . ' overdue instalments need attention';
if ($kpi_overdue_inst === 0) { $overdueInsightText = 'No financial risks detected'; }
$completedInsightText = $kpi_completed_today === 1 ? '1 approval completed today' : number_format($kpi_completed_today) . ' approvals completed today';
if ($kpi_completed_today === 0) { $completedInsightText = 'No completed approvals today'; }
$revenueTrendText = $revenueTrendDelta > 0
? 'Revenue increased by ' . number_format($revenueTrendDelta, 1) . '% over the recent period'
: ($revenueTrendDelta < 0
? 'Revenue declined by ' . number_format(abs($revenueTrendDelta), 1) . '% over the recent period'
: 'Revenue is stable across the recent period');
$refunds_exec = [];
try {
if ($companyId) {
$s = $pdo->prepare("
SELECT r.id, r.user_id, r.amount_paid, r.refund_amount, r.reason, r.status, u.name as client_name, pr.title as property_title
FROM refunds r
LEFT JOIN users u ON r.user_id = u.id
LEFT JOIN allocations a ON r.allocation_id = a.id
LEFT JOIN properties pr ON a.property_id = pr.id
WHERE r.status = 'recommended_approval' " . (tableHasColumn('refunds','company_id') ? "AND r.company_id = ?" : "") . "
ORDER BY r.created_at DESC
LIMIT 8
");
$params = [];
if (tableHasColumn('refunds','company_id')) $params[] = $companyId;
$s->execute($params);
} else {
$s = $pdo->query("
SELECT r.id, r.user_id, r.amount_paid, r.refund_amount, r.reason, r.status, u.name as client_name, pr.title as property_title
FROM refunds r
LEFT JOIN users u ON r.user_id = u.id
LEFT JOIN allocations a ON r.allocation_id = a.id
LEFT JOIN properties pr ON a.property_id = pr.id
WHERE r.status = 'recommended_approval'
ORDER BY r.created_at DESC
LIMIT 8
");
}
$refunds_exec = $s->fetchAll(PDO::FETCH_ASSOC);
} catch (Exception $e) {}
$estateOptions = [];
$selectedEstateId = isset($_GET['estate_id']) ? (int)$_GET['estate_id'] : 0;
$phaseRevenue = [];
try {
if (function_exists('tableHasColumn') && tableHasColumn('properties','estate_id')) {
$approvedStatuses = function_exists('kpiSqlList') ? kpiSqlList(kpiPaymentFinalizedStatuses()) : "('verified','approved','paid','completed','success')";
$dateCol = function_exists('kpiPaymentDateColumn') ? kpiPaymentDateColumn('payments') : 'approval_date';
if ($companyId) {
$st = $pdo->prepare("
SELECT e.id, e.name, COALESCE(SUM(pm.amount),0) as amt
FROM estates e
LEFT JOIN properties pr ON pr.estate_id = e.id
LEFT JOIN allocations a ON a.property_id = pr.id
LEFT JOIN payments pm ON pm.allocation_id = a.id AND pm.status IN $approvedStatuses AND pm.$dateCol BETWEEN ? AND ?
WHERE e.company_id = ?
GROUP BY e.id, e.name
ORDER BY e.name ASC
");
$st->execute([$startDate, $endDate, $companyId]);
} else {
$st = $pdo->prepare("
SELECT e.id, e.name, COALESCE(SUM(pm.amount),0) as amt
FROM estates e
LEFT JOIN properties pr ON pr.estate_id = e.id
LEFT JOIN allocations a ON a.property_id = pr.id
LEFT JOIN payments pm ON pm.allocation_id = a.id AND pm.status IN $approvedStatuses AND pm.$dateCol BETWEEN ? AND ?
GROUP BY e.id, e.name
ORDER BY e.name ASC
");
$st->execute([$startDate, $endDate]);
}
$estateOptions = $st->fetchAll(PDO::FETCH_ASSOC);
if ($selectedEstateId > 0) {
if ($companyId) {
$ph = $pdo->prepare("
SELECT ph.name as phase_name, COALESCE(SUM(pm.amount),0) as amt
FROM phases ph
LEFT JOIN properties pr ON pr.phase_id = ph.id
LEFT JOIN allocations a ON a.property_id = pr.id
LEFT JOIN payments pm ON pm.allocation_id = a.id AND pm.status IN $approvedStatuses AND pm.$dateCol BETWEEN ? AND ? AND a.company_id = ?
WHERE ph.estate_id = ?
GROUP BY ph.id, ph.name
ORDER BY amt DESC
");
$ph->execute([$startDate, $endDate, $companyId, $selectedEstateId]);
} else {
$ph = $pdo->prepare("
SELECT ph.name as phase_name, COALESCE(SUM(pm.amount),0) as amt
FROM phases ph
LEFT JOIN properties pr ON pr.phase_id = ph.id
LEFT JOIN allocations a ON a.property_id = pr.id
LEFT JOIN payments pm ON pm.allocation_id = a.id AND pm.status IN $approvedStatuses AND pm.$dateCol BETWEEN ? AND ?
WHERE ph.estate_id = ?
GROUP BY ph.id, ph.name
ORDER BY amt DESC
");
$ph->execute([$startDate, $endDate, $selectedEstateId]);
}
$phaseRevenue = $ph->fetchAll(PDO::FETCH_ASSOC);
}
}
} catch (Exception $e) {}
// Allocation status mix for donut
$alloc_pending_count = $alloc_approved_count = $alloc_active_count = $alloc_completed_count = $alloc_rejected_count = 0;
try {
if ($companyId) {
$st = $pdo->prepare("SELECT status, COUNT(*) as c FROM allocations WHERE status IN ('pending','approved','active','completed','rejected') AND company_id = ? GROUP BY status");
$st->execute([$companyId]);
} else {
$st = $pdo->query("SELECT status, COUNT(*) as c FROM allocations WHERE status IN ('pending','approved','active','completed','rejected') GROUP BY status");
}
$rows = $st->fetchAll(PDO::FETCH_ASSOC);
foreach ($rows as $row) {
$s = strtolower($row['status'] ?? '');
$c = (int)($row['c'] ?? 0);
if ($s === 'pending') $alloc_pending_count = $c;
elseif ($s === 'approved') $alloc_approved_count = $c;
elseif ($s === 'active') $alloc_active_count = $c;
elseif ($s === 'completed') $alloc_completed_count = $c;
elseif ($s === 'rejected') $alloc_rejected_count = $c;
}
} catch (Exception $e) {}
$currentRangeStartObj = new DateTimeImmutable(substr($startDate, 0, 10));
$currentRangeEndObj = new DateTimeImmutable(substr($endDate, 0, 10));
$rangeDays = max(1, (int)$currentRangeStartObj->diff($currentRangeEndObj)->days + 1);
$previousRangeEndObj = $currentRangeStartObj->modify('-1 day');
$previousRangeStartObj = $previousRangeEndObj->modify('-' . ($rangeDays - 1) . ' days');
$previousStartDate = $previousRangeStartObj->format('Y-m-d 00:00:00');
$previousEndDate = $previousRangeEndObj->format('Y-m-d 23:59:59');
$trendWindowLabel = 'vs previous ' . number_format($rangeDays) . '-day window';
$buildTrendMeta = function ($current, $previous, $goodWhenDown = false) {
$delta = 0.0;
if ((float)$previous > 0) {
$delta = ((float)$current - (float)$previous) / (float)$previous * 100;
} elseif ((float)$current > 0) {
$delta = 100.0;
}
$direction = $delta >= 0 ? 'up' : 'down';
$tone = $goodWhenDown ? ($delta <= 0 ? 'success' : 'danger') : ($delta >= 0 ? 'success' : 'danger');
return [
'delta' => round(abs($delta), 1),
'direction' => $direction,
'tone' => $tone,
'sign' => $direction === 'up' ? '+' : '-'
];
};
$chartDateCol = function_exists('kpiPaymentDateColumn') ? kpiPaymentDateColumn('payments') : 'approval_date';
$approvedStatusesSql = function_exists('kpiSqlList') ? kpiSqlList(kpiPaymentFinalizedStatuses()) : "('verified','approved','paid','completed','success')";
$financeQueueStatuses = function_exists('kpiSqlList') ? kpiSqlList(kpiFinanceQueuePaymentStatuses()) : "('pending_verification','pending_confirmation')";
$metricPreviousRevenue = 0.0;
try {
$metricPreviousRevenue = function_exists('kpiSumPayments')
? (float)kpiSumPayments($pdo, kpiPaymentFinalizedStatuses(), $previousStartDate, $previousEndDate, $companyId, $chartDateCol, true)
: 0.0;
} catch (Exception $e) {}
$metricPreviousVerification = 0;
try {
if ($companyId) {
$stmt = $pdo->prepare("SELECT COUNT(*) FROM allocations a WHERE (a.status = 'pending_executive_approval'" . $execDocClause . ") AND DATE(a.created_at) BETWEEN ? AND ? AND a.company_id = ?");
$stmt->execute([$previousRangeStartObj->format('Y-m-d'), $previousRangeEndObj->format('Y-m-d'), $companyId]);
} else {
$stmt = $pdo->prepare("SELECT COUNT(*) FROM allocations a WHERE (a.status = 'pending_executive_approval'" . $execDocClause . ") AND DATE(a.created_at) BETWEEN ? AND ?");
$stmt->execute([$previousRangeStartObj->format('Y-m-d'), $previousRangeEndObj->format('Y-m-d')]);
}
$metricPreviousVerification = (int)$stmt->fetchColumn();
} catch (Exception $e) {}
$metricPreviousPayments = 0;
try {
if ($companyId) {
$stmt = $pdo->prepare("SELECT COUNT(*) FROM payments p WHERE p.status IN $financeQueueStatuses AND DATE(p.date) BETWEEN ? AND ? AND p.company_id = ?");
$stmt->execute([$previousRangeStartObj->format('Y-m-d'), $previousRangeEndObj->format('Y-m-d'), $companyId]);
} else {
$stmt = $pdo->prepare("SELECT COUNT(*) FROM payments p WHERE p.status IN $financeQueueStatuses AND DATE(p.date) BETWEEN ? AND ?");
$stmt->execute([$previousRangeStartObj->format('Y-m-d'), $previousRangeEndObj->format('Y-m-d')]);
}
$metricPreviousPayments = (int)$stmt->fetchColumn();
} catch (Exception $e) {}
$metricPreviousRisk = 0;
try {
if ($companyId) {
$stmt = $pdo->prepare("SELECT COUNT(*) FROM installments i LEFT JOIN allocations a ON i.allocation_id = a.id WHERE i.status = 'overdue' AND DATE(i.due_date) BETWEEN ? AND ? AND a.company_id = ?");
$stmt->execute([$previousRangeStartObj->format('Y-m-d'), $previousRangeEndObj->format('Y-m-d'), $companyId]);
} else {
$stmt = $pdo->prepare("SELECT COUNT(*) FROM installments i WHERE i.status = 'overdue' AND DATE(i.due_date) BETWEEN ? AND ?");
$stmt->execute([$previousRangeStartObj->format('Y-m-d'), $previousRangeEndObj->format('Y-m-d')]);
}
$metricPreviousRisk = (int)$stmt->fetchColumn();
} catch (Exception $e) {}
$decisionDateCol = 'updated_at';
try {
$cols = $pdo->query("DESCRIBE allocations")->fetchAll(PDO::FETCH_ASSOC);
$names = array_map(fn($c) => $c['Field'], $cols);
if (!in_array('updated_at', $names, true) && in_array('created_at', $names, true)) {
$decisionDateCol = 'created_at';
}
} catch (Exception $e) {}
$metricPreviousCompleted = 0;
try {
if ($companyId) {
$stmt = $pdo->prepare("SELECT COUNT(*) FROM allocations WHERE status = 'completed' AND DATE($decisionDateCol) BETWEEN ? AND ? AND company_id = ?");
$stmt->execute([$previousRangeStartObj->format('Y-m-d'), $previousRangeEndObj->format('Y-m-d'), $companyId]);
} else {
$stmt = $pdo->prepare("SELECT COUNT(*) FROM allocations WHERE status = 'completed' AND DATE($decisionDateCol) BETWEEN ? AND ?");
$stmt->execute([$previousRangeStartObj->format('Y-m-d'), $previousRangeEndObj->format('Y-m-d')]);
}
$metricPreviousCompleted = (int)$stmt->fetchColumn();
} catch (Exception $e) {}
$recentDayKeys = [];
for ($i = 13; $i >= 0; $i--) {
$recentDayKeys[] = (new DateTimeImmutable('today'))->modify("-{$i} days")->format('Y-m-d');
}
$seriesFromMap = function (array $map) use ($recentDayKeys) {
$series = [];
foreach ($recentDayKeys as $key) {
$series[] = (float)($map[$key] ?? 0);
}
return $series;
};
$verificationSparkline = array_fill(0, count($recentDayKeys), 0);
$paymentsSparkline = array_fill(0, count($recentDayKeys), 0);
$riskSparkline = array_fill(0, count($recentDayKeys), 0);
$completedSparkline = array_fill(0, count($recentDayKeys), 0);
try {
$map = [];
if ($companyId) {
$stmt = $pdo->prepare("SELECT DATE(a.created_at) as d, COUNT(*) as c FROM allocations a WHERE (a.status = 'pending_executive_approval'" . $execDocClause . ") AND DATE(a.created_at) BETWEEN ? AND ? AND a.company_id = ? GROUP BY DATE(a.created_at)");
$stmt->execute([$recentDayKeys[0], end($recentDayKeys), $companyId]);
} else {
$stmt = $pdo->prepare("SELECT DATE(a.created_at) as d, COUNT(*) as c FROM allocations a WHERE (a.status = 'pending_executive_approval'" . $execDocClause . ") AND DATE(a.created_at) BETWEEN ? AND ? GROUP BY DATE(a.created_at)");
$stmt->execute([$recentDayKeys[0], end($recentDayKeys)]);
}
foreach (($stmt->fetchAll(PDO::FETCH_ASSOC) ?: []) as $row) $map[(string)$row['d']] = (int)$row['c'];
$verificationSparkline = $seriesFromMap($map);
} catch (Exception $e) {}
try {
$map = [];
if ($companyId) {
$stmt = $pdo->prepare("SELECT DATE(p.date) as d, COUNT(*) as c FROM payments p WHERE p.status IN $financeQueueStatuses AND DATE(p.date) BETWEEN ? AND ? AND p.company_id = ? GROUP BY DATE(p.date)");
$stmt->execute([$recentDayKeys[0], end($recentDayKeys), $companyId]);
} else {
$stmt = $pdo->prepare("SELECT DATE(p.date) as d, COUNT(*) as c FROM payments p WHERE p.status IN $financeQueueStatuses AND DATE(p.date) BETWEEN ? AND ? GROUP BY DATE(p.date)");
$stmt->execute([$recentDayKeys[0], end($recentDayKeys)]);
}
foreach (($stmt->fetchAll(PDO::FETCH_ASSOC) ?: []) as $row) $map[(string)$row['d']] = (int)$row['c'];
$paymentsSparkline = $seriesFromMap($map);
} catch (Exception $e) {}
try {
$map = [];
if ($companyId) {
$stmt = $pdo->prepare("SELECT DATE(i.due_date) as d, COUNT(*) as c FROM installments i LEFT JOIN allocations a ON i.allocation_id = a.id WHERE i.status = 'overdue' AND DATE(i.due_date) BETWEEN ? AND ? AND a.company_id = ? GROUP BY DATE(i.due_date)");
$stmt->execute([$recentDayKeys[0], end($recentDayKeys), $companyId]);
} else {
$stmt = $pdo->prepare("SELECT DATE(i.due_date) as d, COUNT(*) as c FROM installments i WHERE i.status = 'overdue' AND DATE(i.due_date) BETWEEN ? AND ? GROUP BY DATE(i.due_date)");
$stmt->execute([$recentDayKeys[0], end($recentDayKeys)]);
}
foreach (($stmt->fetchAll(PDO::FETCH_ASSOC) ?: []) as $row) $map[(string)$row['d']] = (int)$row['c'];
$riskSparkline = $seriesFromMap($map);
} catch (Exception $e) {}
try {
$map = [];
if ($companyId) {
$stmt = $pdo->prepare("SELECT DATE($decisionDateCol) as d, COUNT(*) as c FROM allocations WHERE status = 'completed' AND DATE($decisionDateCol) BETWEEN ? AND ? AND company_id = ? GROUP BY DATE($decisionDateCol)");
$stmt->execute([$recentDayKeys[0], end($recentDayKeys), $companyId]);
} else {
$stmt = $pdo->prepare("SELECT DATE($decisionDateCol) as d, COUNT(*) as c FROM allocations WHERE status = 'completed' AND DATE($decisionDateCol) BETWEEN ? AND ? GROUP BY DATE($decisionDateCol)");
$stmt->execute([$recentDayKeys[0], end($recentDayKeys)]);
}
foreach (($stmt->fetchAll(PDO::FETCH_ASSOC) ?: []) as $row) $map[(string)$row['d']] = (int)$row['c'];
$completedSparkline = $seriesFromMap($map);
} catch (Exception $e) {}
$metricTrends = [
'verification' => $buildTrendMeta($kpi_exec_pending_alloc, $metricPreviousVerification, true),
'payments' => $buildTrendMeta($kpi_payments_checker, $metricPreviousPayments, true),
'risk' => $buildTrendMeta($kpi_overdue_inst, $metricPreviousRisk, true),
'completed' => $buildTrendMeta($kpi_completed_today, $metricPreviousCompleted, false),
];
$highValuePendingPayments = [];
$highValuePendingAmount = 0.0;
try {
$threshold = (float)(getSetting('exec_large_payment_threshold', 3000000) ?: 3000000);
if ($companyId) {
$stmt = $pdo->prepare("
SELECT p.id, p.amount, p.method, p.status, p.date, u.name as payer
FROM payments p
LEFT JOIN invoices i ON p.invoice_id = i.id
LEFT JOIN users u ON i.tenant_id = u.id
WHERE p.status IN $financeQueueStatuses AND p.amount >= ? AND p.company_id = ?
ORDER BY p.amount DESC
LIMIT 8
");
$stmt->execute([$threshold, $companyId]);
} else {
$stmt = $pdo->prepare("
SELECT p.id, p.amount, p.method, p.status, p.date, u.name as payer
FROM payments p
LEFT JOIN invoices i ON p.invoice_id = i.id
LEFT JOIN users u ON i.tenant_id = u.id
WHERE p.status IN $financeQueueStatuses AND p.amount >= ?
ORDER BY p.amount DESC
LIMIT 8
");
$stmt->execute([$threshold]);
}
$highValuePendingPayments = $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
foreach ($highValuePendingPayments as $paymentRow) $highValuePendingAmount += (float)($paymentRow['amount'] ?? 0);
} catch (Exception $e) {}
$approvalDelayAvgDays = 0.0;
$approvalDelayPreviousDays = 0.0;
try {
$delayStatuses = "('completed','approved','executive_approved','rejected')";
if ($companyId) {
$stmt = $pdo->prepare("SELECT AVG(TIMESTAMPDIFF(HOUR, created_at, $decisionDateCol))/24 FROM allocations WHERE status IN $delayStatuses AND DATE($decisionDateCol) BETWEEN ? AND ? AND company_id = ?");
$stmt->execute([$currentRangeStartObj->format('Y-m-d'), $currentRangeEndObj->format('Y-m-d'), $companyId]);
$approvalDelayAvgDays = (float)($stmt->fetchColumn() ?: 0);
$stmt = $pdo->prepare("SELECT AVG(TIMESTAMPDIFF(HOUR, created_at, $decisionDateCol))/24 FROM allocations WHERE status IN $delayStatuses AND DATE($decisionDateCol) BETWEEN ? AND ? AND company_id = ?");
$stmt->execute([$previousRangeStartObj->format('Y-m-d'), $previousRangeEndObj->format('Y-m-d'), $companyId]);
$approvalDelayPreviousDays = (float)($stmt->fetchColumn() ?: 0);
} else {
$stmt = $pdo->prepare("SELECT AVG(TIMESTAMPDIFF(HOUR, created_at, $decisionDateCol))/24 FROM allocations WHERE status IN $delayStatuses AND DATE($decisionDateCol) BETWEEN ? AND ?");
$stmt->execute([$currentRangeStartObj->format('Y-m-d'), $currentRangeEndObj->format('Y-m-d')]);
$approvalDelayAvgDays = (float)($stmt->fetchColumn() ?: 0);
$stmt = $pdo->prepare("SELECT AVG(TIMESTAMPDIFF(HOUR, created_at, $decisionDateCol))/24 FROM allocations WHERE status IN $delayStatuses AND DATE($decisionDateCol) BETWEEN ? AND ?");
$stmt->execute([$previousRangeStartObj->format('Y-m-d'), $previousRangeEndObj->format('Y-m-d')]);
$approvalDelayPreviousDays = (float)($stmt->fetchColumn() ?: 0);
}
} catch (Exception $e) {}
$approvalDelayText = 'Approval turnaround is stable with same-day executive flow.';
if ($approvalDelayAvgDays > 0) {
$delayDelta = $approvalDelayAvgDays - $approvalDelayPreviousDays;
if ($delayDelta > 0.15) $approvalDelayText = 'Approval delays are increasing — average delay is now ' . number_format($approvalDelayAvgDays, 1) . ' days.';
elseif ($delayDelta < -0.15) $approvalDelayText = 'Approval cycle is accelerating — average delay improved to ' . number_format($approvalDelayAvgDays, 1) . ' days.';
else $approvalDelayText = 'Approval delay is holding steady at ' . number_format($approvalDelayAvgDays, 1) . ' days.';
}
$estateRevenueLeaders = [];
try {
if (function_exists('tableHasColumn') && tableHasColumn('properties', 'estate_id')) {
if ($companyId) {
$stmt = $pdo->prepare("
SELECT e.id, e.name, COALESCE(SUM(pm.amount),0) as revenue, COUNT(DISTINCT a.id) as allocations_count
FROM estates e
LEFT JOIN properties pr ON pr.estate_id = e.id
LEFT JOIN allocations a ON a.property_id = pr.id
LEFT JOIN payments pm ON pm.allocation_id = a.id AND pm.status IN $approvedStatusesSql AND pm.$chartDateCol BETWEEN ? AND ?
WHERE e.company_id = ?
GROUP BY e.id, e.name
ORDER BY revenue DESC, allocations_count DESC, e.name ASC
LIMIT 5
");
$stmt->execute([$startDate, $endDate, $companyId]);
} else {
$stmt = $pdo->prepare("
SELECT e.id, e.name, COALESCE(SUM(pm.amount),0) as revenue, COUNT(DISTINCT a.id) as allocations_count
FROM estates e
LEFT JOIN properties pr ON pr.estate_id = e.id
LEFT JOIN allocations a ON a.property_id = pr.id
LEFT JOIN payments pm ON pm.allocation_id = a.id AND pm.status IN $approvedStatusesSql AND pm.$chartDateCol BETWEEN ? AND ?
GROUP BY e.id, e.name
ORDER BY revenue DESC, allocations_count DESC, e.name ASC
LIMIT 5
");
$stmt->execute([$startDate, $endDate]);
}
$estateRevenueLeaders = $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
}
} catch (Exception $e) {}
$mostActiveEstateName = $estateRevenueLeaders[0]['name'] ?? ($top_properties[0]['property_name'] ?? 'Portfolio Watchlist');
$mostActiveEstateRevenue = (float)($estateRevenueLeaders[0]['revenue'] ?? ($top_properties[0]['total_revenue'] ?? 0));
$mostActiveEstateInsight = $mostActiveEstateRevenue > 0
? 'Most active estate: ' . $mostActiveEstateName . ' with ' . formatCurrency($mostActiveEstateRevenue) . ' recognized in the active window.'
: 'No dominant estate trend yet — portfolio activity is evenly distributed.';
$monthSeriesFactory = function ($monthStartObj, $monthEndObj) use ($pdo, $companyId, $approvedStatusesSql, $chartDateCol) {
$map = [];
$submissionRevenue = getRevenueChartData($pdo, $monthStartObj->format('Y-m-d'), $monthEndObj->format('Y-m-d'), $companyId);
if (!empty($submissionRevenue['map'])) {
$map = $submissionRevenue['map'];
}
try {
if (empty($map)) {
if ($companyId) {
$stmt = $pdo->prepare("
SELECT DATE($chartDateCol) as d, COALESCE(SUM(amount),0) as amt
FROM payments
WHERE status IN $approvedStatusesSql AND $chartDateCol BETWEEN ? AND ? AND company_id = ?
GROUP BY DATE($chartDateCol)
ORDER BY DATE($chartDateCol) ASC
");
$stmt->execute([$monthStartObj->format('Y-m-d 00:00:00'), $monthEndObj->format('Y-m-d 23:59:59'), $companyId]);
} else {
$stmt = $pdo->prepare("
SELECT DATE($chartDateCol) as d, COALESCE(SUM(amount),0) as amt
FROM payments
WHERE status IN $approvedStatusesSql AND $chartDateCol BETWEEN ? AND ?
GROUP BY DATE($chartDateCol)
ORDER BY DATE($chartDateCol) ASC
");
$stmt->execute([$monthStartObj->format('Y-m-d 00:00:00'), $monthEndObj->format('Y-m-d 23:59:59')]);
}
foreach (($stmt->fetchAll(PDO::FETCH_ASSOC) ?: []) as $row) $map[(string)$row['d']] = (float)$row['amt'];
}
} catch (Exception $e) {}
$axisLabels = [];
$fullDates = [];
$values = [];
$chartValues = [];
$pointRadii = [];
$pointHoverRadii = [];
$daysInMonth = (int)$monthEndObj->format('j');
for ($day = 1; $day <= $daysInMonth; $day++) {
$date = $monthStartObj->setDate((int)$monthStartObj->format('Y'), (int)$monthStartObj->format('m'), $day);
$key = $date->format('Y-m-d');
$hasRecordedRevenue = array_key_exists($key, $map);
$amount = $hasRecordedRevenue ? (float)$map[$key] : 0.0;
$axisLabels[] = (string)$day;
$fullDates[] = $date->format('F j');
$values[] = $amount;
$chartValues[] = $hasRecordedRevenue ? $amount : null;
$pointRadii[] = $hasRecordedRevenue ? ($amount > 0 ? 4 : 3) : 0;
$pointHoverRadii[] = $hasRecordedRevenue ? 7 : 0;
}
$annotations = [];
$nonZero = [];
foreach ($values as $index => $value) if ($value > 0) $nonZero[$index] = $value;
arsort($nonZero);
$nonZeroAverage = !empty($nonZero) ? (array_sum($nonZero) / count($nonZero)) : 0;
foreach (array_slice(array_keys($nonZero), 0, 2) as $index) {
$value = (float)$values[$index];
$annotations[] = [
'index' => (int)$index,
'value' => $value,
'text' => $value >= ($nonZeroAverage * 1.35) ? 'Spike due to bulk payment approval' : 'High collection day'
];
}
if (!array_filter($chartValues, function ($value) { return $value !== null; })) {
$chartValues = $values;
$pointRadii = array_map(function ($value) { return $value > 0 ? 4 : 0; }, $values);
$pointHoverRadii = array_map(function ($value) { return $value > 0 ? 7 : 0; }, $values);
}
return [
'axisLabels' => $axisLabels,
'fullDates' => $fullDates,
'values' => $values,
'chartValues' => $chartValues,
'pointRadii' => $pointRadii,
'pointHoverRadii' => $pointHoverRadii,
'annotations' => $annotations,
'total' => array_sum($values),
'label' => $monthStartObj->format('F Y')
];
};
$thisMonthStartObj = (new DateTimeImmutable('today'))->modify('first day of this month');
$thisMonthEndObj = (new DateTimeImmutable('today'))->modify('last day of this month');
$lastMonthStartObj = $thisMonthStartObj->modify('first day of last month');
$lastMonthEndObj = $thisMonthStartObj->modify('last day of last month');
$thisMonthSeries = $monthSeriesFactory($thisMonthStartObj, $thisMonthEndObj);
$lastMonthSeries = $monthSeriesFactory($lastMonthStartObj, $lastMonthEndObj);
$daysElapsedThisMonth = max(1, (int)(new DateTimeImmutable('today'))->format('j'));
$projectedMonthRevenue = ($daysElapsedThisMonth > 0) ? ($thisMonthSeries['total'] / $daysElapsedThisMonth) * (int)$thisMonthEndObj->format('j') : 0;
$projectedRevenueDelta = 0.0;
if ((float)$lastMonthSeries['total'] > 0) $projectedRevenueDelta = (($projectedMonthRevenue - (float)$lastMonthSeries['total']) / (float)$lastMonthSeries['total']) * 100;
elseif ($projectedMonthRevenue > 0) $projectedRevenueDelta = 100.0;
$revenueProjectionText = $projectedMonthRevenue > 0
? 'Revenue is projected to ' . ($projectedRevenueDelta >= 0 ? 'increase' : 'decline') . ' by ' . number_format(abs($projectedRevenueDelta), 1) . '% if the current pace continues.'
: 'Revenue projection will appear as soon as approvals convert into collected cash.';
$thisMonthSeries['projectionText'] = 'Projected close: ' . formatCurrency($projectedMonthRevenue);
$thisMonthSeries['impactText'] = 'Current run rate compared with ' . $lastMonthSeries['label'];
$lastMonthSeries['projectionText'] = 'Closed at: ' . formatCurrency($lastMonthSeries['total']);
$lastMonthSeries['impactText'] = 'Reference baseline for executive comparison';
$revenueComparisonData = ['this_month' => $thisMonthSeries, 'last_month' => $lastMonthSeries];
$comparison = getRevenueComparisonData($pdo, $companyId);
$projection = getProjectedRevenue($pdo, $companyId);
$spikes = getRevenueSpikes($pdo, $companyId);
$revenueSpikeThreshold = function_exists('getSetting') ? (float)(getSetting('exec_revenue_spike_threshold', 5000000) ?: 5000000) : 5000000;
$initialRevenueSpikeAlerts = [];
foreach (($comparison['thisMonth'] ?? []) as $row) {
$total = (float)($row['total'] ?? 0);
if ($total >= $revenueSpikeThreshold) {
$initialRevenueSpikeAlerts[] = [
'date' => (string)($row['day'] ?? ''),
'amount' => $total,
'reason' => explainRevenueSpike($total)
];
}
}
$chartData = getRevenueChartData($pdo, null, null, $companyId);
if (empty($chartData['values'])) {
$chartData = [
'labels' => ['No Data'],
'values' => [0]
];
}
$executiveAlerts = getExecutiveAlerts($pdo, $companyId);
$executiveRevenueInsight = getRevenueInsights($pdo, $companyId);
$executiveRiskSummary = getRiskSummary($pdo, $companyId);
$executiveApprovalVelocity = getApprovalVelocity($pdo, $companyId);
$executiveTopProject = getTopProject($pdo, $companyId);
if (($executiveRevenueInsight['current'] ?? 0) > 0 || ($executiveRevenueInsight['previous'] ?? 0) > 0) {
$revenueProjectionText = $executiveRevenueInsight['text'];
}
if ($executiveApprovalVelocity !== 'Approval turnaround data not sufficient') {
$approvalDelayText = $executiveApprovalVelocity;
}
if ($executiveTopProject !== 'No dominant estate trend yet') {
$mostActiveEstateInsight = $executiveTopProject;
}
$recentExecutiveActivity = [];
try {
if ($pdo->query("SHOW TABLES LIKE 'audit_logs'")->rowCount() > 0) {
$query = "SELECT l.created_at, l.action, l.details, COALESCE(u.name,'System') as user_name FROM audit_logs l LEFT JOIN users u ON l.user_id = u.id";
$params = [];
if ($companyId && function_exists('tableHasColumn') && tableHasColumn('audit_logs', 'company_id')) {
$query .= " WHERE l.company_id = ? ";
$params[] = $companyId;
}
$query .= " ORDER BY l.created_at DESC LIMIT 8";
$stmt = $pdo->prepare($query);
$stmt->execute($params);
foreach (($stmt->fetchAll(PDO::FETCH_ASSOC) ?: []) as $row) {
$summary = trim((string)($row['details'] ?: $row['action']));
if ($summary === '') $summary = 'Executive activity recorded';
$recentExecutiveActivity[] = [
'title' => $summary,
'meta' => strtoupper(str_replace('_', ' ', (string)($row['action'] ?? 'Activity'))) . ' • ' . ($row['user_name'] ?? 'System'),
'time' => formatRelativeTime($row['created_at']),
'timestamp' => $row['created_at']
];
}
}
} catch (Exception $e) {}
if (empty($recentExecutiveActivity)) {
foreach (array_slice($payments_checker, 0, 3) as $paymentRow) {
$recentExecutiveActivity[] = [
'title' => formatCurrency((float)($paymentRow['amount'] ?? 0)) . ' payment submitted for approval',
'meta' => ($paymentRow['payer'] ?? 'Client') . ' • ' . strtoupper((string)($paymentRow['method'] ?? 'Payment')),
'time' => formatRelativeTime($paymentRow['date'] ?? null),
'timestamp' => $paymentRow['date'] ?? null
];
}
foreach (array_slice($allocations_exec, 0, 3) as $allocationRow) {
$recentExecutiveActivity[] = [
'title' => 'Allocation #' . (int)$allocationRow['id'] . ' submitted for review',
'meta' => ($allocationRow['client_name'] ?? 'Client') . ' • ' . ($allocationRow['property_title'] ?? 'Property'),
'time' => formatRelativeTime($allocationRow['created_at'] ?? null),
'timestamp' => $allocationRow['created_at'] ?? null
];
}
}
$recentCompletedAllocations = [];
try {
if ($companyId) {
$stmt = $pdo->prepare("
SELECT a.id, a.status, a.$decisionDateCol as decision_at, u.name as client_name, p.title as property_title, p.price
FROM allocations a
LEFT JOIN users u ON a.user_id = u.id
LEFT JOIN properties p ON a.property_id = p.id
WHERE a.status IN ('completed','approved','executive_approved') AND DATE(a.$decisionDateCol) = CURDATE() AND a.company_id = ?
ORDER BY a.$decisionDateCol DESC
LIMIT 8
");
$stmt->execute([$companyId]);
} else {
$stmt = $pdo->query("
SELECT a.id, a.status, a.$decisionDateCol as decision_at, u.name as client_name, p.title as property_title, p.price
FROM allocations a
LEFT JOIN users u ON a.user_id = u.id
LEFT JOIN properties p ON a.property_id = p.id
WHERE a.status IN ('completed','approved','executive_approved') AND DATE(a.$decisionDateCol) = CURDATE()
ORDER BY a.$decisionDateCol DESC
LIMIT 8
");
}
$recentCompletedAllocations = $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
} catch (Exception $e) {}
$syncWarnings = [];
if (($kpi_exec_pending_alloc > 0 && count($allocations_exec) === 0) || ($kpi_exec_pending_alloc === 0 && count($allocations_exec) > 0)) $syncWarnings[] = 'Verification queue and dashboard count are out of sync.';
if (($kpi_payments_checker > 0 && count($payments_checker) === 0) || ($kpi_payments_checker === 0 && count($payments_checker) > 0)) $syncWarnings[] = 'Payments approval feed is still syncing.';
if (($kpi_overdue_inst > 0 && count($risk_overdues) === 0) || ($kpi_overdue_inst === 0 && count($risk_overdues) > 0)) $syncWarnings[] = 'Risk monitoring records have not fully refreshed.';
$syncWarnings = array_values(array_unique(array_merge($syncWarnings, getDataWarnings($pdo, $companyId, [
'pending_submissions' => (int)($executiveAlerts[0]['count'] ?? 0),
'overdue_installments' => $kpi_overdue_inst
]))));
$hasDataSyncIssue = !empty($syncWarnings);
$syncWarningText = $hasDataSyncIssue ? 'Data syncing... please refresh. ' . implode(' ', $syncWarnings) : '';
$smartAlert = ['tone' => 'safe', 'icon' => 'fa-circle-check', 'title' => 'All systems normal', 'message' => 'No pending risks — executive operations are stable across approvals, payments, and monitoring.'];
if ($kpi_overdue_inst > 0) {
$smartAlert = ['tone' => 'critical', 'icon' => 'fa-triangle-exclamation', 'title' => 'Immediate executive attention required', 'message' => number_format($kpi_overdue_inst) . ' overdue instalments detected — ' . formatCurrency($kpi_overdue_amount) . ' is now at risk.'];
} elseif (count($highValuePendingPayments) > 0) {
$smartAlert = ['tone' => 'critical', 'icon' => 'fa-wallet', 'title' => 'High-value approvals waiting', 'message' => number_format(count($highValuePendingPayments)) . ' high-value payments require immediate approval (' . formatCurrency($highValuePendingAmount) . ' at risk).'];
} elseif ($kpi_payments_checker > 0 || $kpi_exec_pending_alloc > 0) {
$smartAlert = ['tone' => 'warning', 'icon' => 'fa-shield-check', 'title' => 'Executive queue requires review', 'message' => number_format($kpi_payments_checker) . ' payments and ' . number_format($kpi_exec_pending_alloc) . ' verification items are waiting for action.'];
}
if ($smartAlert['tone'] !== 'critical' && !empty($executiveAlerts)) {
$smartAlert = [
'tone' => ($executiveAlerts[0]['type'] ?? 'warning') === 'danger' ? 'critical' : 'warning',
'icon' => ($executiveAlerts[0]['type'] ?? 'warning') === 'danger' ? 'fa-wallet' : 'fa-shield-check',
'title' => ($executiveAlerts[0]['type'] ?? 'warning') === 'danger' ? 'High-value approvals waiting' : 'Approval queue requires review',
'message' => $executiveAlerts[0]['message']
];
}
if ($smartAlert['tone'] === 'safe' && ($executiveRiskSummary['status'] ?? 'safe') === 'danger') {
$smartAlert = ['tone' => 'critical', 'icon' => 'fa-triangle-exclamation', 'title' => 'Immediate executive attention required', 'message' => $executiveRiskSummary['message']];
}
$insightCards = [
['icon' => 'fa-arrow-trend-up', 'tone' => 'blue', 'title' => 'Revenue Projection', 'body' => $revenueProjectionText],
['icon' => 'fa-stopwatch', 'tone' => 'amber', 'title' => 'Approval Velocity', 'body' => $approvalDelayText],
['icon' => 'fa-building', 'tone' => 'green', 'title' => 'Estate Momentum', 'body' => $mostActiveEstateInsight]
];
$verificationDeskUrl = $verificationDeskUrl ?? 'allocation-letters.php?view=executive&status=pending';
$approvalsDeskUrl = $approvalsDeskUrl ?? $verificationDeskUrl;
$paymentsDeskUrl = $paymentsDeskUrl ?? 'finance-payments.php';
$riskDeskUrl = $riskDeskUrl ?? 'executive-audit.php';
$financialInsightsUrl = $financialInsightsUrl ?? (file_exists(__DIR__ . '/executive-finance.php') ? 'executive-finance.php' : 'reports-financial.php');
$reportsExportsUrl = $reportsExportsUrl ?? (file_exists(__DIR__ . '/reports-financial.php') ? 'reports-financial.php' : 'reports.php');
$smartActions = [
['label' => 'View Risk Alerts', 'description' => $kpi_overdue_inst > 0 ? number_format($kpi_overdue_inst) . ' overdue accounts are now escalated.' : 'Risk desk is clear but stays available for executive review.', 'href' => $riskDeskUrl, 'icon' => 'fa-triangle-exclamation', 'variant' => $kpi_overdue_inst > 0 ? 'critical' : 'neutral', 'priority' => $kpi_overdue_inst > 0 ? 400 + $kpi_overdue_inst : 40],
['label' => 'Approve Payments', 'description' => $kpi_payments_checker > 0 ? number_format($kpi_payments_checker) . ' transactions worth ' . formatCurrency($kpi_payments_checker_amount) . ' are waiting.' : 'No finance approvals are pending right now.', 'href' => $paymentsDeskUrl, 'icon' => 'fa-wallet', 'variant' => $kpi_payments_checker > 0 ? 'warning' : 'neutral', 'priority' => $kpi_payments_checker > 0 ? 360 + $kpi_payments_checker + count($highValuePendingPayments) : 50],
];
if (!$isChairmanQueueView) {
$smartActions[] = ['label' => 'Open Verification Desk', 'description' => $kpi_exec_pending_alloc > 0 ? number_format($kpi_exec_pending_alloc) . ' allocations are waiting for executive verification.' : 'Verification desk is currently clear.', 'href' => $verificationDeskUrl, 'icon' => 'fa-shield-check', 'variant' => $kpi_exec_pending_alloc > 0 ? 'info' : 'neutral', 'priority' => $kpi_exec_pending_alloc > 0 ? 320 + $kpi_exec_pending_alloc : 45];
}
$smartActions[] = ['label' => 'Open Approvals Queue', 'description' => number_format($kpi_completed_today) . ' decisions completed today — monitor throughput and exceptions.', 'href' => $approvalsDeskUrl, 'icon' => 'fa-list-check', 'variant' => 'neutral', 'priority' => 90 + $kpi_completed_today];
$smartActions[] = ['label' => 'Review Financial Insights', 'description' => htmlspecialchars_decode($revenueTrendText, ENT_QUOTES), 'href' => $financialInsightsUrl, 'icon' => 'fa-chart-column', 'variant' => $revenueTrendDelta < 0 ? 'warning' : 'success', 'priority' => 80 + max(0, (int)round(abs($revenueTrendDelta)))];
$smartActions[] = ['label' => 'Export Reports', 'description' => 'Open executive exports and enterprise reporting packs.', 'href' => $reportsExportsUrl, 'icon' => 'fa-file-export', 'variant' => 'neutral', 'priority' => 30];
usort($smartActions, function ($left, $right) { return ($right['priority'] ?? 0) <=> ($left['priority'] ?? 0); });
$drilldownPayload = [
'payments' => ['title' => 'Pending Payment Approvals', 'subtitle' => 'Finance items currently queued for executive sign-off.', 'buttonLabel' => 'Open Payments Queue', 'buttonHref' => $paymentsDeskUrl, 'items' => array_map(function ($row) { return ['title' => ($row['payer'] ?? 'Client') . ' • Payment #' . (int)$row['id'], 'meta' => strtoupper((string)($row['method'] ?? 'Payment')) . ' • ' . (!empty($row['date']) ? date('M j, Y', strtotime($row['date'])) : 'Pending'), 'value' => formatCurrency((float)($row['amount'] ?? 0)), 'href' => 'finance-payments.php']; }, $payments_checker)],
'risk' => ['title' => 'Overdue Instalment Exposure', 'subtitle' => 'Accounts that require intervention before exposure rises further.', 'buttonLabel' => 'Open Risk Monitoring', 'buttonHref' => $riskDeskUrl, 'items' => array_map(function ($row) use ($riskDeskUrl) { return ['title' => ($row['client_name'] ?? 'Client') . ' • Allocation #' . (int)($row['allocation_id'] ?? 0), 'meta' => ($row['property_title'] ?? 'Property') . ' • Due ' . (!empty($row['due_date']) ? date('M j, Y', strtotime($row['due_date'])) : 'N/A'), 'value' => formatCurrency(max(0, (float)($row['amount_due'] ?? 0) - (float)($row['paid_amount'] ?? 0))), 'href' => $riskDeskUrl]; }, $risk_overdues)],
'completed' => ['title' => 'Completed Decision Flow', 'subtitle' => 'Recent approvals and completions recorded in the executive window.', 'buttonLabel' => 'Open Approvals Queue', 'buttonHref' => $approvalsDeskUrl, 'items' => array_map(function ($row) use ($approvalsDeskUrl) { return ['title' => ($row['client_name'] ?? 'Client') . ' • Allocation #' . (int)$row['id'], 'meta' => ($row['property_title'] ?? 'Property') . ' • ' . (!empty($row['decision_at']) ? date('M j, g:i a', strtotime($row['decision_at'])) : 'Completed'), 'value' => isset($row['price']) ? formatCurrency((float)$row['price']) : ucfirst(str_replace('_', ' ', (string)($row['status'] ?? 'completed'))), 'href' => $approvalsDeskUrl]; }, $recentCompletedAllocations)]
];
if (!$isChairmanQueueView) {
$drilldownPayload['verification'] = ['title' => 'Pending Verification Breakdown', 'subtitle' => 'Direct access to allocations waiting for executive verification.', 'buttonLabel' => 'Open Verification Desk', 'buttonHref' => $verificationDeskUrl, 'items' => array_map(function ($row) { return ['title' => ($row['client_name'] ?? 'Client') . ' • Allocation #' . (int)$row['id'], 'meta' => ($row['property_title'] ?? 'Property') . ' • ' . (!empty($row['created_at']) ? date('M j, Y', strtotime($row['created_at'])) : 'Awaiting review'), 'value' => isset($row['price']) ? formatCurrency((float)$row['price']) : '-', 'href' => 'allocation-letters.php?view=executive&action=details&allocation_id=' . (int)$row['id']]; }, $allocations_exec)];
}
$voiceSummaryText = $smartAlert['message'] . ' ' . $revenueProjectionText . ' ' . $approvalDelayText . ' ' . $mostActiveEstateInsight;
?>
<style>
.dashboard-premium{
--bg-soft:#f3f6fb;
--panel:#ffffff;
--text-main:#132238;
--text-muted:#6b7280;
--border-soft:#e6ebf2;
--shadow-soft:0 10px 30px rgba(15, 23, 42, 0.08);
--shadow-hover:0 20px 40px rgba(15, 23, 42, 0.12);
--radius-lg:16px;
--radius-md:12px;
--blue:#2563eb;
--blue-soft:rgba(37, 99, 235, 0.12);
--green:#16a34a;
--green-soft:rgba(22, 163, 74, 0.12);
--red:#dc2626;
--red-soft:rgba(220, 38, 38, 0.12);
--amber:#d97706;
--amber-soft:rgba(217, 119, 6, 0.12);
font-family:"Inter","Poppins",-apple-system,BlinkMacSystemFont,"Segoe UI",Roboto,"Helvetica Neue",Arial,sans-serif;
background:var(--bg-soft);
color:var(--text-main);
}
.dashboard-premium .card{
border:1px solid var(--border-soft);
border-radius:var(--radius-lg);
box-shadow:var(--shadow-soft);
overflow:hidden;
}
.dashboard-premium .exec-hero{
padding:28px;
background:linear-gradient(135deg,#ffffff 0%,#eef4ff 54%,#f8fbff 100%);
}
.dashboard-premium .eyebrow{
display:inline-flex;
align-items:center;
gap:8px;
padding:6px 12px;
border-radius:999px;
background:#eaf1ff;
color:#1d4ed8;
font-size:.78rem;
font-weight:700;
letter-spacing:.04em;
text-transform:uppercase;
}
.dashboard-premium .exec-title{
font-size:2rem;
font-weight:800;
margin:14px 0 8px;
}
.dashboard-premium .exec-subtitle{
color:var(--text-muted);
font-size:1rem;
margin:0;
}
.dashboard-premium .toolbar-form{
display:flex;
flex-wrap:wrap;
gap:10px;
align-items:center;
}
.dashboard-premium .toolbar-form .form-control{
min-width:150px;
border-radius:12px;
border:1px solid var(--border-soft);
padding:.7rem .9rem;
}
.dashboard-premium .toolbar-form .btn,
.dashboard-premium .action-stack .btn{
border-radius:12px;
padding:.8rem 1rem;
font-weight:600;
}
.dashboard-premium .btn-primary-dark{
background:#111827;
border-color:#111827;
color:#fff;
}
.dashboard-premium .btn-primary-dark:hover{
background:#0f172a;
border-color:#0f172a;
color:#fff;
}
.dashboard-premium .executive-summary{
padding:24px;
background:linear-gradient(135deg,#132238 0%,#1e3a5f 100%);
color:#fff;
}
.dashboard-premium .executive-summary,
.dashboard-premium .executive-summary h5,
.dashboard-premium .executive-summary .summary-item,
.dashboard-premium .executive-summary .summary-item div,
.dashboard-premium .executive-summary .summary-chip,
.dashboard-premium .executive-summary .summary-chip strong{
color:#fff;
}
.dashboard-premium .executive-summary h5{
font-size:1.05rem;
font-weight:700;
margin-bottom:18px;
}
.dashboard-premium .summary-list{
display:grid;
gap:12px;
}
.dashboard-premium .summary-item{
display:flex;
align-items:flex-start;
gap:12px;
padding:12px 14px;
border-radius:14px;
background:rgba(255,255,255,0.08);
}
.dashboard-premium .summary-item i{
margin-top:2px;
font-size:1rem;
}
.dashboard-premium .summary-metrics{
display:flex;
flex-wrap:wrap;
gap:12px;
margin-top:18px;
}
.dashboard-premium .summary-chip{
min-width:150px;
padding:12px 14px;
border-radius:14px;
background:rgba(255,255,255,0.08);
}
.dashboard-premium .summary-chip span{
display:block;
font-size:.78rem;
color:rgba(255,255,255,0.86);
}
.dashboard-premium .summary-chip strong{
display:block;
margin-top:4px;
font-size:1.05rem;
}
.dashboard-premium .kpi-card{
padding:24px;
border-radius:20px;
border:1px solid rgba(226,232,240,0.8);
background:#ffffff;
transition:all 0.3s cubic-bezier(0.4, 0, 0.2, 1);
height:100%;
cursor:pointer;
position:relative;
overflow:hidden;
}
.dashboard-premium .kpi-card:hover{
transform:translateY(-4px);
box-shadow:0 12px 24px -8px rgba(2,8,23,0.12);
border-color:var(--blue-soft);
}
.dashboard-premium .kpi-card .metric-label{
color:var(--text-muted);
font-size:.7rem;
font-weight:700;
letter-spacing:.05em;
text-transform:uppercase;
}
.dashboard-premium .kpi-card .metric-value{
margin-top:8px;
font-size:clamp(1.1rem, 2vw, 1.7rem);
line-height:1.2;
font-weight:800;
color: var(--text-dark);
letter-spacing: -0.01em;
white-space: normal;
word-break: break-word;
}
.dashboard-premium .kpi-card .metric-insight{
margin-top:8px;
color:var(--text-muted);
font-size:.82rem;
line-height:1.4;
display: -webkit-box;
-webkit-line-clamp: 2;
-webkit-box-orient: vertical;
overflow: hidden;
}
.dashboard-premium .kpi-card .metric-foot{
margin-top:18px;
display:flex;
align-items:center;
justify-content:space-between;
color:#2563eb;
font-weight:700;
font-size:.88rem;
}
.dashboard-premium .metric-icon{
width:54px;
height:54px;
border-radius:14px;
display:flex;
align-items:center;
justify-content:center;
font-size:1.2rem;
}
.dashboard-premium .icon-blue{background:var(--blue-soft);color:var(--blue);}
.dashboard-premium .icon-amber{background:var(--amber-soft);color:var(--amber);}
.dashboard-premium .icon-red{background:var(--red-soft);color:var(--red);}
.dashboard-premium .icon-green{background:var(--green-soft);color:var(--green);}
.dashboard-premium .smart-alert{
display:flex;
align-items:flex-start;
justify-content:space-between;
gap:16px;
padding:18px 20px;
border-radius:18px;
border:1px solid transparent;
backdrop-filter:blur(12px);
}
.dashboard-premium .smart-alert.critical{
background:linear-gradient(135deg,rgba(254,226,226,0.9) 0%,rgba(255,245,245,0.96) 100%);
border-color:#fecaca;
color:#991b1b;
}
.dashboard-premium .smart-alert.warning{
background:linear-gradient(135deg,rgba(254,243,199,0.9) 0%,rgba(255,251,235,0.96) 100%);
border-color:#fde68a;
color:#92400e;
}
.dashboard-premium .smart-alert.safe{
background:linear-gradient(135deg,rgba(220,252,231,0.92) 0%,rgba(240,253,244,0.98) 100%);
border-color:#bbf7d0;
color:#166534;
}
.dashboard-premium .smart-alert-main{
display:flex;
gap:14px;
align-items:flex-start;
}
.dashboard-premium .smart-alert-icon{
width:48px;
height:48px;
border-radius:14px;
display:flex;
align-items:center;
justify-content:center;
font-size:1.15rem;
background:rgba(255,255,255,0.55);
}
.dashboard-premium .smart-alert-title{
font-weight:800;
font-size:1rem;
margin-bottom:4px;
}
.dashboard-premium .smart-alert-message{
font-size:.94rem;
line-height:1.5;
}
.dashboard-premium .smart-alert-close{
border:none;
background:transparent;
color:inherit;
width:38px;
height:38px;
border-radius:12px;
}
.dashboard-premium .smart-alert-close:hover{
background:rgba(255,255,255,0.4);
}
.dashboard-premium .sync-banner{
display:flex;
align-items:center;
gap:12px;
padding:14px 16px;
border-radius:16px;
background:rgba(255,255,255,0.9);
border:1px solid #fde68a;
color:#92400e;
}
.dashboard-premium .insight-grid{
display:grid;
grid-template-columns:repeat(3,minmax(0,1fr));
gap:16px;
}
.dashboard-premium .insight-card{
padding:20px;
border-radius:18px;
border:1px solid rgba(255,255,255,0.3);
color:#fff;
position:relative;
overflow:hidden;
}
.dashboard-premium .insight-card::after{
content:"";
position:absolute;
inset:auto -20px -30px auto;
width:120px;
height:120px;
border-radius:999px;
background:rgba(255,255,255,0.12);
filter:blur(10px);
}
.dashboard-premium .insight-card.blue{background:linear-gradient(135deg,#1d4ed8 0%,#60a5fa 100%);}
.dashboard-premium .insight-card.amber{background:linear-gradient(135deg,#b45309 0%,#f59e0b 100%);}
.dashboard-premium .insight-card.green{background:linear-gradient(135deg,#047857 0%,#34d399 100%);}
.dashboard-premium .insight-icon{
width:48px;
height:48px;
border-radius:14px;
background:rgba(255,255,255,0.18);
display:flex;
align-items:center;
justify-content:center;
font-size:1.1rem;
margin-bottom:16px;
}
.dashboard-premium .insight-title{
font-size:.88rem;
text-transform:uppercase;
letter-spacing:.04em;
font-weight:700;
opacity:.92;
margin-bottom:10px;
}
.dashboard-premium .insight-body{
font-size:.98rem;
line-height:1.55;
max-width:92%;
}
.dashboard-premium .metric-topline{
display:flex;
align-items:flex-start;
justify-content:space-between;
gap:10px;
}
.dashboard-premium .metric-topline > div:first-child {
flex: 1;
min-width: 0;
}
.dashboard-premium .metric-trend{
display:inline-flex;
align-items:center;
gap:4px;
padding:4px 8px;
border-radius:6px;
font-size:.7rem;
font-weight:700;
margin-top:10px;
white-space: nowrap;
}
.dashboard-premium .metric-trend.success{
background:rgba(16,185,129,0.1);
color:#059669;
}
.dashboard-premium .metric-trend.danger{
background:rgba(239,68,68,0.1);
color:#dc2626;
}
.dashboard-premium .metric-sparkline{
width:100%;
height:52px;
margin-top:12px;
}
.dashboard-premium .metric-caption{
margin-top:14px;
font-size:.75rem;
color:var(--text-muted);
line-height:1.5;
}
.dashboard-premium .priority-glow{
border-color:rgba(245,158,11,0.45);
box-shadow:0 18px 36px rgba(245,158,11,0.14);
}
.dashboard-premium .priority-glow::before{
content:"";
position:absolute;
inset:0;
border-radius:inherit;
padding:1px;
background:linear-gradient(135deg,rgba(245,158,11,0.7),rgba(59,130,246,0.18));
-webkit-mask:linear-gradient(#fff 0 0) content-box,linear-gradient(#fff 0 0);
mask:linear-gradient(#fff 0 0) content-box,linear-gradient(#fff 0 0);
-webkit-mask-composite:xor;
mask-composite:exclude;
pointer-events:none;
}
.dashboard-premium .section-card .card-header{
padding:18px 22px;
background:#fff;
border-bottom:1px solid var(--border-soft);
}
.dashboard-premium .section-card .card-body{
padding:22px;
}
.dashboard-premium .section-title{
font-size:1.05rem;
font-weight:700;
margin:0;
}
.dashboard-premium .section-subtitle{
color:var(--text-muted);
font-size:.88rem;
margin-top:4px;
}
.dashboard-premium .pill-badge{
display:inline-flex;
align-items:center;
gap:6px;
padding:8px 12px;
border-radius:999px;
background:#f4f7fb;
color:#334155;
font-size:.82rem;
font-weight:700;
}
.dashboard-premium .queue-table{
margin:0;
}
.dashboard-premium .queue-table th{
border-bottom:1px solid var(--border-soft);
color:#64748b;
font-size:.78rem;
font-weight:700;
letter-spacing:.04em;
padding:14px 22px;
text-transform:uppercase;
}
.dashboard-premium .queue-table td{
padding:16px 22px;
vertical-align:middle;
border-top:1px solid #eef2f7;
}
.dashboard-premium .queue-row{
cursor:pointer;
transition:background-color .2s ease;
}
.dashboard-premium .queue-row:hover{
background:#f8fbff;
}
.dashboard-premium .entity-name{
font-weight:700;
color:var(--text-main);
}
.dashboard-premium .entity-meta{
color:var(--text-muted);
font-size:.85rem;
margin-top:2px;
}
.dashboard-premium .table-actions{
display:flex;
justify-content:flex-end;
gap:8px;
flex-wrap:wrap;
}
.dashboard-premium .table-actions .btn{
border-radius:10px;
}
.dashboard-premium .empty-state{
padding:38px 22px;
text-align:center;
color:var(--text-muted);
}
.dashboard-premium .empty-state i{
display:inline-flex;
align-items:center;
justify-content:center;
width:58px;
height:58px;
border-radius:18px;
background:#eef4ff;
color:#2563eb;
font-size:1.35rem;
margin-bottom:14px;
}
.dashboard-premium .risk-list{
display:grid;
gap:14px;
}
.dashboard-premium .risk-item{
display:flex;
justify-content:space-between;
gap:14px;
padding:16px;
border-radius:14px;
border:1px solid #fee2e2;
background:#fff7f7;
}
.dashboard-premium .risk-item .meta{
color:var(--text-muted);
font-size:.85rem;
margin-top:2px;
}
.dashboard-premium .risk-item .amount{
color:var(--red);
font-weight:800;
text-align:right;
}
.dashboard-premium .action-stack{
display:grid;
gap:12px;
}
.dashboard-premium .action-stack .btn{
display:flex;
align-items:center;
justify-content:flex-start;
gap:12px;
width:100%;
text-align:left;
}
.dashboard-premium .action-stack .btn i{
width:18px;
}
.dashboard-premium .notice-card{
border-radius:14px;
}
.dashboard-premium .trend-note{
color:var(--text-muted);
font-size:.88rem;
}
.dashboard-premium .smart-actions-grid{
display:grid;
gap:12px;
}
.dashboard-premium .smart-action{
display:flex;
align-items:flex-start;
gap:14px;
width:100%;
padding:16px;
border-radius:16px;
border:1px solid var(--border-soft);
background:linear-gradient(180deg,#fff 0%,#f8fafc 100%);
text-decoration:none;
color:inherit;
transition:transform .2s ease, box-shadow .2s ease, border-color .2s ease;
}
.dashboard-premium .smart-action:hover{
transform:translateY(-3px);
box-shadow:var(--shadow-hover);
color:inherit;
}
.dashboard-premium .smart-action.critical{border-color:#fecaca;background:linear-gradient(180deg,#fff1f2 0%,#fff 100%);}
.dashboard-premium .smart-action.warning{border-color:#fde68a;background:linear-gradient(180deg,#fffbeb 0%,#fff 100%);}
.dashboard-premium .smart-action.info{border-color:#bfdbfe;background:linear-gradient(180deg,#eff6ff 0%,#fff 100%);}
.dashboard-premium .smart-action.success{border-color:#bbf7d0;background:linear-gradient(180deg,#f0fdf4 0%,#fff 100%);}
.dashboard-premium .smart-action-icon{
width:44px;
height:44px;
border-radius:14px;
display:flex;
align-items:center;
justify-content:center;
background:#0f172a;
color:#fff;
flex-shrink:0;
}
.dashboard-premium .smart-action-copy{
flex:1;
}
.dashboard-premium .smart-action-label{
font-weight:800;
display:flex;
align-items:center;
justify-content:space-between;
gap:12px;
}
.dashboard-premium .smart-action-desc{
color:var(--text-muted);
margin-top:4px;
font-size:.9rem;
line-height:1.45;
}
.dashboard-premium .timeline-list{
display:grid;
gap:16px;
max-height:420px;
overflow:auto;
padding-right:4px;
}
.dashboard-premium .timeline-item{
position:relative;
padding-left:26px;
}
.dashboard-premium .timeline-item::before{
content:"";
position:absolute;
left:6px;
top:4px;
width:10px;
height:10px;
border-radius:999px;
background:#2563eb;
box-shadow:0 0 0 6px rgba(37,99,235,0.12);
}
.dashboard-premium .timeline-item::after{
content:"";
position:absolute;
left:10px;
top:18px;
bottom:-18px;
width:2px;
background:#e2e8f0;
}
.dashboard-premium .timeline-item:last-child::after{
display:none;
}
.dashboard-premium .timeline-title{
font-weight:700;
line-height:1.45;
}
.dashboard-premium .timeline-meta,
.dashboard-premium .timeline-time{
color:var(--text-muted);
font-size:.84rem;
}
.dashboard-premium .chart-panel{
position:relative;
min-height:360px;
}
.dashboard-premium .chart-toolbar{
display:flex;
align-items:center;
justify-content:space-between;
gap:12px;
flex-wrap:wrap;
margin-bottom:18px;
}
.dashboard-premium .toggle-group{
display:inline-flex;
padding:4px;
border-radius:999px;
background:#f1f5f9;
}
.dashboard-premium .toggle-group button{
border:none;
background:transparent;
border-radius:999px;
padding:9px 14px;
font-weight:700;
color:#475569;
}
.dashboard-premium .toggle-group button.active{
background:#fff;
color:#0f172a;
box-shadow:0 6px 14px rgba(15,23,42,0.08);
}
.dashboard-premium .chart-meta{
display:grid;
grid-template-columns:repeat(2,minmax(0,1fr));
gap:12px;
margin-top:18px;
}
.dashboard-premium .chart-meta-card{
padding:14px 16px;
border-radius:14px;
background:#f8fafc;
border:1px solid #e2e8f0;
}
.dashboard-premium .chart-meta-label{
color:var(--text-muted);
font-size:.8rem;
text-transform:uppercase;
letter-spacing:.04em;
font-weight:700;
}
.dashboard-premium .chart-meta-value{
margin-top:6px;
font-size:1rem;
font-weight:800;
}
.dashboard-premium .lazy-shell{
position:absolute;
inset:0;
border-radius:18px;
display:flex;
align-items:center;
justify-content:center;
background:linear-gradient(135deg,rgba(248,250,252,0.9),rgba(241,245,249,0.95));
color:#64748b;
font-weight:700;
z-index:1;
}
.dashboard-premium .lazy-shell.hidden{
display:none;
}
.dashboard-premium .drilldown-list{
display:grid;
gap:12px;
}
.dashboard-premium .drilldown-item{
display:flex;
align-items:center;
justify-content:space-between;
gap:14px;
padding:14px 16px;
border:1px solid #e2e8f0;
border-radius:14px;
text-decoration:none;
color:inherit;
}
.dashboard-premium .drilldown-item:hover{
background:#f8fbff;
color:inherit;
}
.dashboard-premium .drilldown-value{
font-weight:800;
color:#0f172a;
}
.dashboard-premium .glass-panel{
background:rgba(255,255,255,0.78);
backdrop-filter:blur(16px);
}
.dashboard-premium .page-transition{
animation:fadeSlideIn .22s ease;
}
@keyframes fadeSlideIn{
from{opacity:0;transform:translateY(8px);}
to{opacity:1;transform:translateY(0);}
}
@media (max-width: 1199.98px){
.dashboard-premium .insight-grid{
grid-template-columns:1fr;
}
}
@media (max-width: 991.98px){
.dashboard-premium .exec-hero{
padding:22px;
}
.dashboard-premium .exec-title{
font-size:1.7rem;
}
.dashboard-premium .chart-meta{
grid-template-columns:1fr;
}
}
@media (max-width: 767.98px){
.dashboard-premium .toolbar-form .form-control,
.dashboard-premium .toolbar-form .btn{
width:100%;
}
.dashboard-premium .queue-table th,
.dashboard-premium .queue-table td{
padding:14px 16px;
}
.dashboard-premium .table-actions{
justify-content:flex-start;
}
.dashboard-premium .smart-alert{
flex-direction:column;
}
.dashboard-premium .smart-alert-close{
align-self:flex-end;
}
}
</style>
<div class="container-fluid px-4 dashboard-premium page-transition">
<div class="card exec-hero mt-4 mb-4">
<div class="row g-4 align-items-start">
<div class="col-xl-5">
<div class="eyebrow"><i class="fa-solid fa-chart-pie"></i> Executive Overview</div>
<h2 class="exec-title"><?= $isChairmanQueueView ? 'Chairman Decision System' : 'Executive Decision System' ?></h2>
<p class="exec-subtitle"><?= $isChairmanQueueView ? 'Monitor verification pressure, financial risk, and final approvals from one enterprise command view.' : 'Track approvals, revenue movement, and operational risk with executive-level clarity.' ?></p>
</div>
<div class="col-xl-7">
<div class="d-flex flex-column gap-3">
<form class="toolbar-form" method="GET" action="executive-dashboard.php">
<input id="filterStart" type="date" name="start_date" class="form-control" value="<?= isset($_GET['start_date']) ? htmlspecialchars($_GET['start_date']) : date('Y-m-01') ?>">
<input id="filterEnd" type="date" name="end_date" class="form-control" value="<?= isset($_GET['end_date']) ? htmlspecialchars($_GET['end_date']) : date('Y-m-t') ?>">
<button class="btn btn-primary" type="submit"><i class="fa-solid fa-filter me-2"></i>Apply Range</button>
</form>
<div class="toolbar-form">
<a href="<?= $verificationDeskUrl ?>" class="btn btn-primary-dark"><i class="fa-solid fa-shield-check me-2"></i>Open Verification Desk</a>
<button class="btn btn-outline-secondary" type="button" onclick="downloadKpiSnapshot()"><i class="fa-solid fa-file-arrow-down me-2"></i>Export KPIs</button>
<button class="btn btn-outline-dark" type="button" onclick="exportDashboardPdf()"><i class="fa-solid fa-file-pdf me-2"></i>Export PDF</button>
<button class="btn btn-outline-primary" type="button" onclick="readExecutiveSummary()"><i class="fa-solid fa-volume-high me-2"></i>Read Executive Summary</button>
<a href="allocation-letters.php?view=executive" class="btn btn-outline-secondary"><i class="fa-solid fa-file-lines me-2"></i>Allocation Letters</a>
<a href="<?= $riskDeskUrl ?>" class="btn btn-outline-danger"><i class="fa-solid fa-triangle-exclamation me-2"></i>Risk Alerts</a>
</div>
<div class="trend-note">Decision window: <?= date('M j, Y', strtotime($startDate)) ?> — <?= date('M j, Y', strtotime($endDate)) ?></div>
</div>
</div>
</div>
</div>
<?php if (!empty($_GET['notice'])): ?>
<div class="alert notice-card alert-<?= htmlspecialchars($_GET['type'] ?? 'info') ?> mb-4"><?= htmlspecialchars($_GET['notice']) ?></div>
<?php endif; ?>
<div class="smart-alert <?= htmlspecialchars($smartAlert['tone']) ?> mb-3" id="smartAlertBanner">
<div class="smart-alert-main">
<div class="smart-alert-icon"><i class="fa-solid <?= htmlspecialchars($smartAlert['icon']) ?>"></i></div>
<div>
<div class="smart-alert-title"><?= htmlspecialchars($smartAlert['title']) ?></div>
<div class="smart-alert-message"><?= htmlspecialchars($smartAlert['message']) ?></div>
</div>
</div>
<button type="button" class="smart-alert-close" aria-label="Dismiss alert" onclick="dismissSmartAlert()"><i class="fa-solid fa-xmark"></i></button>
</div>
<?php if ($hasDataSyncIssue): ?>
<div class="sync-banner mb-4">
<i class="fa-solid fa-rotate-right"></i>
<div><?= htmlspecialchars($syncWarningText) ?></div>
</div>
<?php endif; ?>
<div class="card executive-summary mb-4">
<div class="row g-4">
<div class="col-lg-8">
<h5>Executive Summary</h5>
<div class="summary-list">
<div class="summary-item">
<i class="fa-solid fa-circle-exclamation text-warning"></i>
<div><?= $kpi_payments_checker === 0 ? 'No payments require approval at the moment' : number_format($kpi_payments_checker) . ' payments require approval with ' . formatCurrency($kpi_payments_checker_amount) . ' waiting for sign-off' ?></div>
</div>
<div class="summary-item">
<i class="fa-solid fa-chart-line <?= $kpi_overdue_inst > 0 ? 'text-danger' : 'text-success' ?>"></i>
<div><?= $kpi_overdue_inst === 0 ? 'No overdue instalments detected across the monitored portfolio' : number_format($kpi_overdue_inst) . ' overdue instalments represent ' . formatCurrency($kpi_overdue_amount) . ' in exposed value' ?></div>
</div>
<div class="summary-item">
<i class="fa-solid fa-arrow-trend-up <?= $revenueTrendDelta < 0 ? 'text-danger' : 'text-success' ?>"></i>
<div><?= htmlspecialchars($revenueTrendText) ?></div>
</div>
</div>
</div>
<div class="col-lg-4">
<div class="summary-metrics">
<div class="summary-chip">
<span>Revenue In Period</span>
<strong id="kpi_revenue_total"><?= formatCurrency($kpi_revenue) ?></strong>
</div>
<div class="summary-chip">
<span>Collections Rate</span>
<strong id="kpi_collections_rate"><?= number_format($kpi_collections_rate, 1) ?>%</strong>
</div>
<div class="summary-chip">
<span>Decision Throughput</span>
<strong><?= number_format($kpi_completed_today) ?> today</strong>
</div>
<div class="summary-chip">
<span>Risk Exposure</span>
<strong id="kpi_overdue_value"><?= formatCurrency($kpi_overdue_amount) ?></strong>
</div>
</div>
</div>
</div>
</div>
<div class="card section-card glass-panel mb-4">
<div class="card-header d-flex flex-wrap justify-content-between align-items-center gap-3">
<div>
<h3 class="section-title">AI Executive Insights</h3>
<div class="section-subtitle">Predictive signals and operating intelligence generated from current portfolio activity.</div>
</div>
<div class="pill-badge"><i class="fa-solid fa-sparkles"></i>Live intelligence layer</div>
</div>
<div class="card-body">
<div class="insight-grid">
<?php foreach ($insightCards as $insight): ?>
<div class="insight-card <?= htmlspecialchars($insight['tone']) ?>">
<div class="insight-icon"><i class="fa-solid <?= htmlspecialchars($insight['icon']) ?>"></i></div>
<div class="insight-title"><?= htmlspecialchars($insight['title']) ?></div>
<div class="insight-body"><?= htmlspecialchars($insight['body']) ?></div>
</div>
<?php endforeach; ?>
</div>
</div>
</div>
<div class="row g-3 mb-4">
<?php if (!$isChairmanQueueView): ?>
<div class="col-xl-3 col-md-6">
<div class="card kpi-card js-card-link <?= $isChairman ? 'priority-glow' : '' ?>" data-href="<?= $verificationDeskUrl ?>" data-drilldown="verification">
<div class="metric-topline">
<div>
<div class="metric-label">Pending Verification</div>
<div class="metric-value" id="kpi_exec_pending"><?= number_format($kpi_exec_pending_alloc) ?></div>
<div class="metric-trend <?= htmlspecialchars($metricTrends['verification']['tone']) ?>">
<i class="fa-solid <?= $metricTrends['verification']['direction'] === 'up' ? 'fa-arrow-trend-up' : 'fa-arrow-trend-down' ?>"></i>
<span><?= $metricTrends['verification']['sign'] ?><?= number_format($metricTrends['verification']['delta'], 1) ?>% <?= htmlspecialchars($trendWindowLabel) ?></span>
</div>
<div class="metric-insight"><?= htmlspecialchars($verificationCountText) ?></div>
</div>
<div class="metric-icon icon-blue"><i class="fa-solid fa-user-check"></i></div>
</div>
<canvas class="metric-sparkline" data-series='<?= json_encode($verificationSparkline) ?>' data-color="#2563eb"></canvas>
<div class="metric-caption">Daily queue pressure across the last 14 days.</div>
<div class="metric-foot">
<span>Open breakdown</span>
<i class="fa-solid fa-arrow-right"></i>
</div>
</div>
</div>
<?php endif; ?>
<div class="col-xl-3 col-md-6">
<div class="card kpi-card js-card-link <?= $isChairman ? 'priority-glow' : '' ?>" data-href="<?= $paymentsDeskUrl ?>" data-drilldown="payments">
<div class="metric-topline">
<div>
<div class="metric-label">Payments to Approve</div>
<div class="metric-value" id="kpi_payments_to_approve"><?= formatCurrency($kpi_payments_checker_amount) ?></div>
<div class="metric-trend <?= htmlspecialchars($metricTrends['payments']['tone']) ?>">
<i class="fa-solid <?= $metricTrends['payments']['direction'] === 'up' ? 'fa-arrow-trend-up' : 'fa-arrow-trend-down' ?>"></i>
<span><?= $metricTrends['payments']['sign'] ?><?= number_format($metricTrends['payments']['delta'], 1) ?>% <?= htmlspecialchars($trendWindowLabel) ?></span>
</div>
<div class="metric-insight"><?= htmlspecialchars($paymentsInsightText) ?></div>
</div>
<div class="metric-icon icon-amber"><i class="fa-solid fa-wallet"></i></div>
</div>
<canvas class="metric-sparkline" data-series='<?= json_encode($paymentsSparkline) ?>' data-color="#d97706"></canvas>
<div class="metric-caption">Pending finance approvals and high-value exposure trend.</div>
<div class="metric-foot">
<span>Open breakdown</span>
<i class="fa-solid fa-arrow-right"></i>
</div>
</div>
</div>
<div class="col-xl-3 col-md-6">
<div class="card kpi-card js-card-link <?= $isChairman ? 'priority-glow' : '' ?>" data-href="<?= $riskDeskUrl ?>" data-drilldown="risk">
<div class="metric-topline">
<div>
<div class="metric-label">Overdue Instalments</div>
<div class="metric-value text-danger" id="kpi_overdue_inst"><?= number_format($kpi_overdue_inst) ?></div>
<div class="metric-trend <?= htmlspecialchars($metricTrends['risk']['tone']) ?>">
<i class="fa-solid <?= $metricTrends['risk']['direction'] === 'up' ? 'fa-arrow-trend-up' : 'fa-arrow-trend-down' ?>"></i>
<span><?= $metricTrends['risk']['sign'] ?><?= number_format($metricTrends['risk']['delta'], 1) ?>% <?= htmlspecialchars($trendWindowLabel) ?></span>
</div>
<div class="metric-insight"><?= htmlspecialchars($overdueInsightText) ?></div>
</div>
<div class="metric-icon icon-red"><i class="fa-solid fa-triangle-exclamation"></i></div>
</div>
<canvas class="metric-sparkline" data-series='<?= json_encode($riskSparkline) ?>' data-color="#dc2626"></canvas>
<div class="metric-caption">Escalated receivable risk movement over the last 14 days.</div>
<div class="metric-foot">
<span>Open breakdown</span>
<i class="fa-solid fa-arrow-right"></i>
</div>
</div>
</div>
<div class="col-xl-3 col-md-6">
<div class="card kpi-card js-card-link" data-href="<?= $approvalsDeskUrl ?>" data-drilldown="completed">
<div class="metric-topline">
<div>
<div class="metric-label">Completed Today</div>
<div class="metric-value text-success" id="kpi_completed_today"><?= number_format($kpi_completed_today) ?></div>
<div class="metric-trend <?= htmlspecialchars($metricTrends['completed']['tone']) ?>">
<i class="fa-solid <?= $metricTrends['completed']['direction'] === 'up' ? 'fa-arrow-trend-up' : 'fa-arrow-trend-down' ?>"></i>
<span><?= $metricTrends['completed']['sign'] ?><?= number_format($metricTrends['completed']['delta'], 1) ?>% <?= htmlspecialchars($trendWindowLabel) ?></span>
</div>
<div class="metric-insight"><?= htmlspecialchars($completedInsightText) ?></div>
</div>
<div class="metric-icon icon-green"><i class="fa-solid fa-circle-check"></i></div>
</div>
<canvas class="metric-sparkline" data-series='<?= json_encode($completedSparkline) ?>' data-color="#16a34a"></canvas>
<div class="metric-caption">Completed decisions recorded through the active executive window.</div>
<div class="metric-foot">
<span>Open breakdown</span>
<i class="fa-solid fa-arrow-right"></i>
</div>
</div>
</div>
</div>
<div class="row g-4">
<div class="col-xl-8">
<?php if (!$isChairmanQueueView): ?>
<div class="card section-card mb-4">
<div class="card-header d-flex flex-wrap justify-content-between align-items-center gap-3">
<div>
<h3 class="section-title"><?= $isChairmanQueueView ? 'Chairman Verification Queue' : 'Approvals & Decisions Queue' ?></h3>
<div class="section-subtitle"><?= $isChairmanQueueView ? 'High-priority allocations requiring final verification and signature' : 'Priority allocations awaiting executive decisioning' ?></div>
</div>
<div class="pill-badge"><i class="fa-solid fa-hourglass-half"></i><?= number_format((int)$kpi_exec_pending_alloc) ?> pending</div>
</div>
<div class="card-body p-0">
<?php if (count($allocations_exec) === 0): ?>
<div class="empty-state">
<i class="fa-solid fa-folder-open"></i>
<div class="fw-semibold mb-1"><?= $isChairmanQueueView ? 'No allocations waiting for chairman verification' : 'No allocations waiting for executive review' ?></div>
<div>New items appear here automatically when they enter the decision queue.</div>
</div>
<?php else: ?>
<div class="table-responsive">
<table class="table queue-table align-middle mb-0" id="executiveQueueTable">
<thead>
<tr>
<th>Client</th>
<th>Asset</th>
<th>Decision Status</th>
<th class="text-end">Value</th>
<th class="text-end">Actions</th>
</tr>
</thead>
<tbody>
<?php foreach ($allocations_exec as $a): ?>
<tr class="queue-row" data-href="<?= $isChairmanQueueView ? 'allocation-letters.php?view=executive&action=details&allocation_id=' . (int)$a['id'] : 'executive-allocations.php' ?>">
<td>
<div class="entity-name"><?= htmlspecialchars($a['client_name'] ?? 'Client') ?></div>
<div class="entity-meta">Allocation #<?= (int)$a['id'] ?></div>
</td>
<td>
<div class="entity-name"><?= htmlspecialchars($a['property_title'] ?? 'Property') ?></div>
<div class="entity-meta"><?= !empty($a['created_at']) ? date('M j, Y', strtotime($a['created_at'])) : 'Awaiting review' ?></div>
</td>
<td>
<span class="badge <?= $isChairmanQueueView ? 'bg-warning text-dark' : 'bg-info text-dark' ?>"><?= htmlspecialchars(ucwords(str_replace('_', ' ', $a['status'] ?? 'pending'))) ?></span>
</td>
<td class="text-end">
<div class="entity-name"><?= isset($a['price']) ? formatCurrency($a['price']) : '-' ?></div>
</td>
<td class="text-end">
<div class="table-actions">
<?php if ($isChairmanQueueView): ?>
<button type="button" class="btn btn-sm btn-dark" onclick="approveChairmanAllocation(<?= (int)$a['id'] ?>, event)"><i class="fa-solid fa-stamp me-1"></i>Approve</button>
<button type="button" class="btn btn-sm btn-outline-danger" onclick="openAllocDecision(<?= (int)$a['id'] ?>,'rejected', event)"><i class="fa-solid fa-xmark me-1"></i>Reject</button>
<a class="btn btn-sm btn-outline-primary" href="allocation-letters.php?view=executive&action=details&allocation_id=<?= (int)$a['id'] ?>"><i class="fa-solid fa-eye me-1"></i>View</a>
<?php else: ?>
<button type="button" class="btn btn-sm btn-success" onclick="openAllocDecision(<?= (int)$a['id'] ?>,'executive_approved', event)"><i class="fa-solid fa-check me-1"></i>Approve</button>
<button type="button" class="btn btn-sm btn-outline-danger" onclick="openAllocDecision(<?= (int)$a['id'] ?>,'rejected', event)"><i class="fa-solid fa-xmark me-1"></i>Reject</button>
<a class="btn btn-sm btn-outline-primary" href="allocation-letters.php?view=executive&action=preview&allocation_id=<?= (int)$a['id'] ?>" target="_blank"><i class="fa-solid fa-eye me-1"></i>View</a>
<?php endif; ?>
</div>
</td>
</tr>
<?php endforeach; ?>
</tbody>
</table>
</div>
<?php endif; ?>
</div>
</div>
<?php endif; ?>
<div class="card section-card">
<div class="card-header d-flex flex-wrap justify-content-between align-items-center gap-3">
<div>
<h3 class="section-title">Revenue Trend & Approval Impact</h3>
<div class="section-subtitle">Compare current and previous month collections with approval-driven spikes and projected close.</div>
</div>
<div class="pill-badge <?= $revenueTrendDelta < 0 ? 'text-danger' : 'text-success' ?>">
<i class="fa-solid <?= $revenueTrendDelta < 0 ? 'fa-arrow-trend-down' : 'fa-arrow-trend-up' ?>"></i>
<?= $revenueTrendDelta >= 0 ? '+' : '' ?><?= number_format($revenueTrendDelta, 1) ?>%
</div>
</div>
<div class="card-body">
<div class="chart-panel">
<div class="chart-toolbar">
<div class="toggle-group" id="revenueToggleGroup">
<button type="button" class="active" data-range="this_month">This Month</button>
<button type="button" data-range="last_month">Last Month</button>
</div>
<div class="pill-badge"><i class="fa-solid fa-wave-square"></i>Curved revenue signal</div>
</div>
<div class="lazy-shell" id="revenueChartShell">Loading revenue intelligence…</div>
<canvas id="revenueChart" height="120"></canvas>
<div class="chart-meta" id="revenueChartMeta">
<div class="chart-meta-card">
<div class="chart-meta-label">Projected Close</div>
<div class="chart-meta-value" id="chartProjectionValue">₦<?= number_format((float)($projection['projected'] ?? 0), 2) ?></div>
</div>
<div class="chart-meta-card">
<div class="chart-meta-label">Approval Impact</div>
<div class="chart-meta-value" id="chartImpactValue"><?= htmlspecialchars(!empty($initialRevenueSpikeAlerts) ? (count($initialRevenueSpikeAlerts) . ' revenue spike(s) detected') : 'No revenue spikes detected') ?></div>
</div>
</div>
<div id="revenueSpikeAlerts">
<?php foreach ($initialRevenueSpikeAlerts as $spike): ?>
<div class="alert alert-info mt-3 mb-0">
Spike detected on <?= htmlspecialchars(date('j M', strtotime((string)($spike['date'] ?? 'now')))) ?>
(₦<?= number_format((float)($spike['amount'] ?? 0), 2) ?>)
— <?= htmlspecialchars((string)($spike['reason'] ?? '')) ?>
</div>
<?php endforeach; ?>
</div>
</div>
</div>
</div>
</div>
<div class="col-xl-4">
<div class="card section-card mb-4">
<div class="card-header d-flex flex-wrap justify-content-between align-items-center gap-3">
<div>
<h3 class="section-title">Risk Monitoring</h3>
<div class="section-subtitle">Financial clarity on overdue instalments and exposed receivables.</div>
</div>
<div class="pill-badge text-danger"><i class="fa-solid fa-shield-halved"></i><?= number_format($kpi_overdue_inst) ?> alerts</div>
</div>
<div class="card-body">
<?php if (count($risk_overdues) === 0): ?>
<div class="empty-state p-0">
<i class="fa-solid fa-shield-check"></i>
<div class="fw-semibold mb-1">No financial risks detected</div>
<div>System operating normally with no overdue instalments in the monitored period.</div>
</div>
<?php else: ?>
<div class="risk-list">
<?php foreach ($risk_overdues as $r): ?>
<div class="risk-item">
<div>
<div class="entity-name"><?= htmlspecialchars($r['client_name'] ?? 'Client') ?></div>
<div class="meta"><?= htmlspecialchars($r['property_title'] ?? 'Property') ?></div>
<div class="meta">Due <?= !empty($r['due_date']) ? date('M j, Y', strtotime($r['due_date'])) : 'N/A' ?></div>
</div>
<div class="amount">
<div><?= isset($r['amount_due']) ? formatCurrency(max(0, (float)$r['amount_due'] - (float)($r['paid_amount'] ?? 0))) : '-' ?></div>
<div class="meta text-danger">Outstanding</div>
</div>
</div>
<?php endforeach; ?>
</div>
<?php endif; ?>
</div>
</div>
<div class="card section-card">
<div class="card-header">
<h3 class="section-title">Smart Actions</h3>
<div class="section-subtitle">Adaptive next steps reordered by urgency, value at risk, and decision pressure.</div>
</div>
<div class="card-body">
<div class="smart-actions-grid">
<?php foreach ($smartActions as $action): ?>
<a href="<?= htmlspecialchars($action['href']) ?>" class="smart-action <?= htmlspecialchars($action['variant']) ?>">
<div class="smart-action-icon"><i class="fa-solid <?= htmlspecialchars($action['icon']) ?>"></i></div>
<div class="smart-action-copy">
<div class="smart-action-label">
<span><?= htmlspecialchars($action['label']) ?></span>
<i class="fa-solid fa-arrow-right"></i>
</div>
<div class="smart-action-desc"><?= htmlspecialchars($action['description']) ?></div>
</div>
</a>
<?php endforeach; ?>
</div>
</div>
</div>
<div class="card section-card mt-4">
<div class="card-header d-flex flex-wrap justify-content-between align-items-center gap-3">
<div>
<h3 class="section-title">Recent Executive Activity</h3>
<div class="section-subtitle">Live timeline of approvals, queue movements, and notable operating actions.</div>
</div>
<div class="pill-badge"><i class="fa-solid fa-clock-rotate-left"></i><?= count($recentExecutiveActivity) ?> updates</div>
</div>
<div class="card-body">
<div class="timeline-list">
<?php foreach ($recentExecutiveActivity as $activity): ?>
<div class="timeline-item">
<div class="timeline-title"><?= htmlspecialchars($activity['title']) ?></div>
<div class="timeline-meta"><?= htmlspecialchars($activity['meta']) ?></div>
<div class="timeline-time mt-1"><?= htmlspecialchars($activity['time']) ?></div>
</div>
<?php endforeach; ?>
</div>
</div>
</div>
</div>
</div>
</div>
<div class="modal fade" id="allocDecisionModal" tabindex="-1" aria-hidden="true">
<div class="modal-dialog">
<div class="modal-content">
<div class="modal-header">
<h5 class="modal-title">Allocation Decision</h5>
<button type="button" class="btn-close" data-bs-dismiss="modal"></button>
</div>
<div class="modal-body">
<input type="hidden" id="allocDecisionId">
<input type="hidden" id="allocDecisionStatus">
<div class="mb-3">
<label class="form-label">Reason</label>
<textarea class="form-control" id="allocDecisionReason" required></textarea>
</div>
</div>
<div class="modal-footer">
<button type="button" class="btn btn-outline-secondary" data-bs-dismiss="modal">Cancel</button>
<button type="button" class="btn btn-primary" onclick="submitAllocDecision()">Submit</button>
</div>
</div>
</div>
</div>
<div class="modal fade" id="paymentRejectModal" tabindex="-1" aria-hidden="true">
<div class="modal-dialog">
<div class="modal-content">
<div class="modal-header">
<h5 class="modal-title">Reject Payment</h5>
<button type="button" class="btn-close" data-bs-dismiss="modal"></button>
</div>
<div class="modal-body">
<input type="hidden" id="paymentRejectId">
<div class="mb-3">
<label class="form-label">Reason</label>
<textarea class="form-control" id="paymentRejectReason" required></textarea>
</div>
</div>
<div class="modal-footer">
<button type="button" class="btn btn-outline-secondary" data-bs-dismiss="modal">Cancel</button>
<button type="button" class="btn btn-danger" onclick="submitPaymentReject()">Reject</button>
</div>
</div>
</div>
</div>
<div class="modal fade" id="kpiDrilldownModal" tabindex="-1" aria-hidden="true">
<div class="modal-dialog modal-lg modal-dialog-scrollable">
<div class="modal-content">
<div class="modal-header">
<div>
<h5 class="modal-title" id="kpiDrilldownTitle">Executive Breakdown</h5>
<div class="text-muted small" id="kpiDrilldownSubtitle">Detailed queue view</div>
</div>
<button type="button" class="btn-close" data-bs-dismiss="modal" aria-label="Close"></button>
</div>
<div class="modal-body">
<div class="drilldown-list" id="kpiDrilldownList"></div>
</div>
<div class="modal-footer">
<a href="#" class="btn btn-primary" id="kpiDrilldownCta">Open Queue</a>
</div>
</div>
</div>
</div>
<script>
function stopEventBubble(event){
if (event) {
event.preventDefault();
event.stopPropagation();
}
}
const revenueLabels = <?= json_encode($chartData['labels'] ?? [], JSON_UNESCAPED_SLASHES | JSON_UNESCAPED_UNICODE) ?>;
const revenueValues = <?= json_encode($chartData['values'] ?? [], JSON_UNESCAPED_SLASHES | JSON_UNESCAPED_UNICODE) ?>;
const thisMonthData = <?= json_encode($comparison['thisMonth'] ?? [], JSON_UNESCAPED_SLASHES | JSON_UNESCAPED_UNICODE) ?>;
const lastMonthData = <?= json_encode($comparison['lastMonth'] ?? [], JSON_UNESCAPED_SLASHES | JSON_UNESCAPED_UNICODE) ?>;
const revenueSpikeThreshold = <?= json_encode((float)(function_exists('getSetting') ? (getSetting('exec_revenue_spike_threshold', 5000000) ?: 5000000) : 5000000), JSON_UNESCAPED_SLASHES | JSON_UNESCAPED_UNICODE) ?>;
const projectedRevenue = <?= json_encode($projection ?? [], JSON_UNESCAPED_SLASHES | JSON_UNESCAPED_UNICODE) ?>;
const drilldownPayload = <?= json_encode($drilldownPayload, JSON_UNESCAPED_SLASHES | JSON_UNESCAPED_UNICODE) ?>;
const revenueComparisonData = <?= json_encode($revenueComparisonData, JSON_UNESCAPED_SLASHES | JSON_UNESCAPED_UNICODE) ?>;
const voiceSummaryText = <?= json_encode($voiceSummaryText, JSON_UNESCAPED_SLASHES | JSON_UNESCAPED_UNICODE) ?>;
function dismissSmartAlert(){
var banner = document.getElementById('smartAlertBanner');
if (banner) banner.style.display = 'none';
}
function renderSparkline(canvas){
if (!canvas) return;
var raw = canvas.getAttribute('data-series') || '[]';
var values = [];
try { values = JSON.parse(raw); } catch (e) { values = []; }
var ctx = canvas.getContext('2d');
if (!ctx) return;
var width = canvas.clientWidth || 260;
var height = canvas.clientHeight || 52;
var ratio = window.devicePixelRatio || 1;
canvas.width = width * ratio;
canvas.height = height * ratio;
ctx.scale(ratio, ratio);
ctx.clearRect(0, 0, width, height);
if (!values.length) return;
var max = Math.max.apply(null, values);
var min = Math.min.apply(null, values);
var range = max - min || 1;
var color = canvas.getAttribute('data-color') || '#2563eb';
var step = values.length > 1 ? width / (values.length - 1) : width;
ctx.beginPath();
values.forEach(function(value, index){
var x = step * index;
var y = height - (((value - min) / range) * (height - 10)) - 5;
if (index === 0) ctx.moveTo(x, y);
else ctx.lineTo(x, y);
});
ctx.strokeStyle = color;
ctx.lineWidth = 2;
ctx.lineJoin = 'round';
ctx.lineCap = 'round';
ctx.stroke();
}
function openKpiDrilldown(key){
var payload = drilldownPayload[key];
if (!payload) return;
document.getElementById('kpiDrilldownTitle').innerText = payload.title || 'Executive Breakdown';
document.getElementById('kpiDrilldownSubtitle').innerText = payload.subtitle || '';
document.getElementById('kpiDrilldownCta').href = payload.buttonHref || '#';
document.getElementById('kpiDrilldownCta').innerText = payload.buttonLabel || 'Open Queue';
var list = document.getElementById('kpiDrilldownList');
list.innerHTML = '';
var items = Array.isArray(payload.items) ? payload.items : [];
if (!items.length) {
list.innerHTML = '<div class="text-muted">No records available for this breakdown right now.</div>';
} else {
items.forEach(function(item){
var row = document.createElement(item.href ? 'a' : 'div');
row.className = 'drilldown-item';
if (item.href) row.href = item.href;
row.innerHTML = '<div><div class="fw-semibold">' + (item.title || 'Record') + '</div><div class="text-muted small mt-1">' + (item.meta || '') + '</div></div><div class="drilldown-value">' + (item.value || '-') + '</div>';
list.appendChild(row);
});
}
new bootstrap.Modal(document.getElementById('kpiDrilldownModal')).show();
}
function openAllocDecision(id, status, event){
stopEventBubble(event);
var m = new bootstrap.Modal(document.getElementById('allocDecisionModal'));
document.getElementById('allocDecisionId').value = id;
document.getElementById('allocDecisionStatus').value = status;
document.getElementById('allocDecisionReason').value = '';
m.show();
}
function submitAllocDecision(){
var id = document.getElementById('allocDecisionId').value;
var st = document.getElementById('allocDecisionStatus').value;
var r = document.getElementById('allocDecisionReason').value.trim();
if (!r) { alert('Reason is required'); return; }
fetch('ajax_update_allocation_status.php', {
method: 'POST',
headers: {'Content-Type':'application/json'},
body: JSON.stringify({id: id, status: st, reason: r})
}).then(r => r.json()).then(j => {
if (j && j.success) {
location.reload();
} else {
alert(j.message || 'Failed');
}
}).catch(() => alert('Request failed'));
}
function approveChairmanAllocation(id, event){
stopEventBubble(event);
if (!confirm('Approve this allocation and generate the final letter?')) return;
fetch('start-approval.php', {
method: 'POST',
headers: {'Content-Type':'application/x-www-form-urlencoded'},
body: 'allocation_id=' + encodeURIComponent(id)
}).then(r => r.json()).then(j => {
if (j && j.success) {
location.reload();
} else if (j && j.blockers && j.blockers.length) {
alert(j.blockers.join(', '));
} else {
alert((j && (j.error || j.message)) || 'Failed');
}
}).catch(() => alert('Request failed'));
}
function approvePayment(id){
fetch('ajax_update_payment_status.php', {
method: 'POST',
headers: {'Content-Type':'application/json'},
body: JSON.stringify({id: id, status: 'approved'})
}).then(r => r.json()).then(j => {
if (j && j.success) {
location.reload();
} else {
alert(j.message || 'Failed');
}
}).catch(() => alert('Request failed'));
}
function openPaymentReject(id){
var m = new bootstrap.Modal(document.getElementById('paymentRejectModal'));
document.getElementById('paymentRejectId').value = id;
document.getElementById('paymentRejectReason').value = '';
m.show();
}
function submitPaymentReject(){
var id = document.getElementById('paymentRejectId').value;
var r = document.getElementById('paymentRejectReason').value.trim();
if (!r) { alert('Reason is required'); return; }
fetch('ajax_update_payment_status.php', {
method: 'POST',
headers: {'Content-Type':'application/json'},
body: JSON.stringify({id: id, status: 'failed', reason: r})
}).then(r => r.json()).then(j => {
if (j && j.success) {
location.reload();
} else {
alert(j.message || 'Failed');
}
}).catch(() => alert('Request failed'));
}
document.querySelectorAll('.js-card-link').forEach(function(card){
card.addEventListener('click', function(){
var drilldownKey = card.getAttribute('data-drilldown');
if (drilldownKey) {
openKpiDrilldown(drilldownKey);
return;
}
var href = card.getAttribute('data-href');
if (href) window.location.href = href;
});
});
document.querySelectorAll('.queue-row').forEach(function(row){
row.addEventListener('click', function(event){
if (event.target.closest('button, a')) return;
var href = row.getAttribute('data-href');
if (href) window.location.href = href;
});
});
document.querySelectorAll('.metric-sparkline').forEach(renderSparkline);
window.addEventListener('resize', function(){
document.querySelectorAll('.metric-sparkline').forEach(renderSparkline);
});
function readExecutiveSummary(){
if (!('speechSynthesis' in window)) {
alert('Voice summary is not supported in this browser.');
return;
}
window.speechSynthesis.cancel();
var utterance = new SpeechSynthesisUtterance(voiceSummaryText);
utterance.rate = 0.95;
utterance.pitch = 1;
window.speechSynthesis.speak(utterance);
}
function exportDashboardPdf(){
window.print();
}
</script>
<script>
const nairaFormatter = new Intl.NumberFormat('en-NG', { style: 'currency', currency: 'NGN', maximumFractionDigits: 0 });
const revenueCanvas = document.getElementById('revenueChart');
let revenueChart = null;
function drawRoundedRect(ctx, x, y, width, height, radius){
if (typeof ctx.roundRect === 'function') {
ctx.beginPath();
ctx.roundRect(x, y, width, height, radius);
return;
}
var r = Math.min(radius, width / 2, height / 2);
ctx.beginPath();
ctx.moveTo(x + r, y);
ctx.arcTo(x + width, y, x + width, y + height, r);
ctx.arcTo(x + width, y + height, x, y + height, r);
ctx.arcTo(x, y + height, x, y, r);
ctx.arcTo(x, y, x + width, y, r);
ctx.closePath();
}
const revenueAnnotationPlugin = {
id: 'executiveAnnotations',
afterDatasetsDraw(chart){
const activeRange = chart?.config?.options?.plugins?.executiveAnnotations?.range;
const payload = revenueComparisonData[activeRange];
if (!payload || !Array.isArray(payload.annotations)) return;
const meta = chart.getDatasetMeta(0);
if (!meta || !meta.data) return;
const {ctx} = chart;
ctx.save();
ctx.font = '12px Inter, sans-serif';
payload.annotations.forEach(function(annotation){
var index = annotation.index;
var point = meta.data[index];
if (!point) return;
var xPos = point.x;
var yPos = point.y;
var label = annotation.text + ' • ' + nairaFormatter.format(annotation.value || 0);
var textWidth = ctx.measureText(label).width;
var bubbleWidth = Math.min(textWidth + 20, 220);
var bubbleX = Math.max(12, Math.min(xPos - (bubbleWidth / 2), chart.width - bubbleWidth - 12));
var bubbleY = Math.max(12, yPos - 46);
ctx.strokeStyle = 'rgba(37,99,235,0.24)';
ctx.lineWidth = 1.5;
ctx.beginPath();
ctx.moveTo(xPos, yPos - 8);
ctx.lineTo(xPos, bubbleY + 28);
ctx.stroke();
ctx.fillStyle = '#ffffff';
drawRoundedRect(ctx, bubbleX, bubbleY, bubbleWidth, 28, 10);
ctx.fill();
ctx.stroke();
ctx.fillStyle = '#1e293b';
ctx.fillText(label, bubbleX + 10, bubbleY + 18);
ctx.fillStyle = '#2563eb';
ctx.beginPath();
ctx.arc(xPos, yPos, 5, 0, Math.PI * 2);
ctx.fill();
ctx.fillStyle = '#ffffff';
ctx.beginPath();
ctx.arc(xPos, yPos, 2, 0, Math.PI * 2);
ctx.fill();
});
ctx.restore();
}
};
function buildRevenueGradient(context){
const chart = context.chart;
const chartArea = chart.chartArea;
if (!chartArea) return 'rgba(37, 99, 235, 0.18)';
const gradient = chart.ctx.createLinearGradient(0, chartArea.top, 0, chartArea.bottom);
gradient.addColorStop(0, 'rgba(37, 99, 235, 0.28)');
gradient.addColorStop(1, 'rgba(37, 99, 235, 0.02)');
return gradient;
}
function getRevenueSeriesPayload(source){
const rows = Array.isArray(source) ? source : [];
const labels = [];
const values = [];
const fullDates = [];
rows.forEach(function(item){
const rawDate = item.day || '';
const day = new Date(rawDate).getDate();
if (Number.isNaN(day)) return;
labels.push(day);
values.push(Number(item.total || 0));
fullDates.push(rawDate);
});
return {
labels: labels.length ? labels : ['No Data'],
values: values.length ? values : [0],
fullDates: fullDates.length ? fullDates : []
};
}
function explainRevenueSpikeClient(amount){
const total = Number(amount || 0);
if (total > 10000000) return 'Spike driven by high-value bulk payments.';
if (total > 5000000) return 'Increase due to multiple mid-size transactions.';
return 'Normal transaction activity.';
}
function getRevenueSpikeEntries(source){
const rows = Array.isArray(source) ? source : [];
return rows
.map(function(item){
return {
day: item.day || '',
total: Number(item.total || 0)
};
})
.filter(function(item){
return item.day && item.total >= Number(revenueSpikeThreshold || 0);
});
}
function renderRevenueSpikeAlerts(source){
const impactNode = document.getElementById('chartImpactValue');
const alertsNode = document.getElementById('revenueSpikeAlerts');
const spikes = getRevenueSpikeEntries(source);
if (impactNode) impactNode.innerText = spikes.length ? (spikes.length + ' revenue spike(s) detected') : 'No revenue spikes detected';
if (!alertsNode) return;
if (!spikes.length) {
alertsNode.innerHTML = '';
return;
}
alertsNode.innerHTML = spikes.map(function(spike){
const date = new Date(spike.day);
const label = Number.isNaN(date.getTime())
? spike.day
: date.toLocaleDateString('en-GB', { day: 'numeric', month: 'short' });
return '<div class="alert alert-info mt-3 mb-0">Spike detected on ' +
label +
' (₦' + spike.total.toLocaleString(undefined, { minimumFractionDigits: 2, maximumFractionDigits: 2 }) +
') — ' + explainRevenueSpikeClient(spike.total) +
'</div>';
}).join('');
}
function updateRevenueChart(rangeKey){
if (!revenueChart) return;
revenueChart.options.plugins.executiveAnnotations.range = rangeKey || 'this_month';
var projectionNode = document.getElementById('chartProjectionValue');
if (projectionNode) projectionNode.innerText = projectedRevenue?.projected ? ('Projected close: ₦' + Number(projectedRevenue.projected).toLocaleString()) : '';
switchDataset(rangeKey === 'last_month' ? 'last' : 'this');
}
function switchDataset(type){
if (!revenueChart) return;
var showThisMonth = type === 'this' || type === 'this_month';
var activeSource = showThisMonth ? thisMonthData : lastMonthData;
var activePayload = getRevenueSeriesPayload(showThisMonth ? thisMonthData : lastMonthData);
revenueChart.data.labels = activePayload.labels;
revenueChart.data.datasets[0].data = showThisMonth ? activePayload.values : [];
revenueChart.data.datasets[1].data = showThisMonth ? [] : activePayload.values;
revenueChart.$activeFullDates = activePayload.fullDates || [];
revenueChart.data.datasets[0].hidden = !showThisMonth;
revenueChart.data.datasets[1].hidden = showThisMonth;
renderRevenueSpikeAlerts(activeSource);
revenueChart.update();
}
function initRevenueChart(){
if (!revenueCanvas || revenueChart) return;
const ctx = revenueCanvas.getContext('2d');
const payload = getRevenueSeriesPayload(thisMonthData);
revenueChart = new Chart(ctx, {
type: 'line',
data: {
labels: payload.labels,
datasets: [{
label: 'This Month',
data: payload.values,
borderColor: '#2563eb',
backgroundColor: buildRevenueGradient,
pointRadius: 4,
pointHoverRadius: 7,
pointBackgroundColor: '#2563eb',
pointBorderColor: '#ffffff',
pointBorderWidth: 2,
pointHoverBackgroundColor: '#2563eb',
pointHoverBorderColor: '#ffffff',
pointHoverBorderWidth: 3,
borderWidth: 3,
tension: 0.45,
spanGaps: true,
fill: true,
hidden: false
}, {
label: 'Last Month',
data: [],
borderColor: '#94a3b8',
backgroundColor: 'transparent',
pointRadius: 3,
pointHoverRadius: 5,
pointBackgroundColor: '#94a3b8',
pointBorderColor: '#ffffff',
pointBorderWidth: 2,
borderWidth: 2,
borderDash: [5, 5],
tension: 0.45,
spanGaps: true,
fill: false,
hidden: true
}]
},
options: {
responsive: true,
maintainAspectRatio: true,
interaction: {
intersect: false,
mode: 'index'
},
onClick: function(evt, elements) {
if (!elements || !elements.length) return;
const index = elements[0].index;
const fullDates = this.$activeFullDates || [];
const selectedDate = fullDates[index] || this.data.labels[index];
if (!selectedDate || selectedDate === 'No Data') return;
window.location.href = 'transactions.php?date=' + encodeURIComponent(selectedDate);
},
plugins: {
legend: { display: true },
executiveAnnotations: { range: 'this_month' },
tooltip: {
backgroundColor: '#132238',
padding: 12,
callbacks: {
title: function(context){
var chart = context[0]?.chart;
var fullDates = chart?.$activeFullDates || [];
var index = context[0]?.dataIndex || 0;
return fullDates[index] || context[0]?.label || '';
},
label: function(context){
return (context.dataset.label || 'Revenue') + ': ₦' + Number(context.raw || 0).toLocaleString();
}
}
}
},
scales: {
y: {
beginAtZero: true,
ticks: {
callback: function(value){
return nairaFormatter.format(value || 0);
}
},
grid: {
color: 'rgba(148, 163, 184, 0.16)'
}
},
x: {
grid: { display: false }
}
}
},
plugins: [revenueAnnotationPlugin]
});
updateRevenueChart('this_month');
var shell = document.getElementById('revenueChartShell');
if (shell) shell.classList.add('hidden');
}
const chartObserver = ('IntersectionObserver' in window && revenueCanvas)
? new IntersectionObserver(function(entries, observer){
entries.forEach(function(entry){
if (entry.isIntersecting) {
initRevenueChart();
observer.disconnect();
}
});
}, {rootMargin: '120px'})
: null;
if (chartObserver && revenueCanvas) {
chartObserver.observe(revenueCanvas);
} else {
initRevenueChart();
}
document.querySelectorAll('#revenueToggleGroup button').forEach(function(button){
button.addEventListener('click', function(){
document.querySelectorAll('#revenueToggleGroup button').forEach(function(item){ item.classList.remove('active'); });
button.classList.add('active');
initRevenueChart();
updateRevenueChart(button.getAttribute('data-range') || 'this_month');
});
});
</script>
<script>
function downloadTableCsv(tableId, filename){
var t = document.getElementById(tableId);
if (!t) return;
var rows = Array.from(t.querySelectorAll('tr'));
var csv = rows.map(function(tr){
var cells = Array.from(tr.querySelectorAll('th,td')).map(function(td){
var text = td.innerText.replace(/"/g,'""');
return '"' + text + '"';
});
return cells.join(',');
}).join('\n');
var blob = new Blob([csv], {type: 'text/csv;charset=utf-8;'});
var link = document.createElement('a');
var url = URL.createObjectURL(blob);
link.setAttribute('href', url);
link.setAttribute('download', filename || 'export.csv');
document.body.appendChild(link);
link.click();
document.body.removeChild(link);
URL.revokeObjectURL(url);
}
</script>
<script>
function downloadKpiSnapshot(){
try {
var start = document.getElementById('filterStart')?.value || '';
var end = document.getElementById('filterEnd')?.value || '';
var now = new Date().toISOString();
var rows = [
['Metric','Value','Start','End','Generated'],
['Revenue In Period', (document.getElementById('kpi_revenue_total')?.innerText || '').trim(), start, end, now],
['Collections Rate', (document.getElementById('kpi_collections_rate')?.innerText || '').trim(), start, end, now],
['Pending Verification', (document.getElementById('kpi_exec_pending')?.innerText || '').trim(), start, end, now],
['Payments to Approve', (document.getElementById('kpi_payments_to_approve')?.innerText || '').trim(), start, end, now],
['Overdue Installments', (document.getElementById('kpi_overdue_inst')?.innerText || '').trim(), start, end, now],
['Risk Exposure', (document.getElementById('kpi_overdue_value')?.innerText || '').trim(), start, end, now],
['Completed Today', (document.getElementById('kpi_completed_today')?.innerText || '').trim(), start, end, now]
];
var csv = rows.map(function(r){ return r.map(function(c){ c = (c||'').toString().replace(/"/g,'""'); return '"'+c+'"'; }).join(','); }).join('\n');
var blob = new Blob([csv], {type:'text/csv;charset=utf-8;'});
var a = document.createElement('a');
a.href = URL.createObjectURL(blob);
a.download = 'executive-kpis.csv';
document.body.appendChild(a);
a.click();
document.body.removeChild(a);
setTimeout(function(){ URL.revokeObjectURL(a.href); }, 1000);
} catch (e) {}
}
</script>
<script>
function openRefundDecision(action, id){
const reason = prompt('Provide reason for this decision:');
if (!reason) return;
const form = document.createElement('form');
form.method = 'POST';
form.action = 'executive-dashboard.php';
const a = document.createElement('input'); a.type='hidden'; a.name='exec_refund_action'; a.value=action; form.appendChild(a);
const rid = document.createElement('input'); rid.type='hidden'; rid.name='refund_id'; rid.value=String(id); form.appendChild(rid);
const r = document.createElement('input'); r.type='hidden'; r.name='refund_reason'; r.value=reason; form.appendChild(r);
document.body.appendChild(form);
form.submit();
}
</script>
<?php include 'includes/footer.php'; ?>