1298 lines
34 KiB
PHP
1298 lines
34 KiB
PHP
<?php // phpcs:ignoreFile
|
|
require __DIR__ . '/../session.php';
|
|
require __DIR__ . '/../userAccess.php';
|
|
|
|
$pageTitle = 'Live Cohort Comparison';
|
|
$pageSubtitle = 'Compare the current operating window against historical cohorts.';
|
|
$pageDescription = 'Line up the latest operating window next to earlier cohorts to spotlight performance deltas.';
|
|
$assetBasePath = '../';
|
|
$layoutWithoutSidebar = true;
|
|
$layoutReturnUrl = '../overview.php';
|
|
$layoutReturnLabel = 'Back to overview';
|
|
|
|
$chartJsCdn = 'https://cdn.jsdelivr.net/npm/chart.js@4.4.0/dist/chart.umd.js';
|
|
|
|
$servername = '192.168.0.13\\SQLEXPRESS';
|
|
$username = 'opce';
|
|
$password = 'opcelasuca';
|
|
$dbname = 'history';
|
|
|
|
$appTimeZone = new DateTimeZone('America/Chicago');
|
|
|
|
$windowOptions = [24, 48, 72];
|
|
$defaultWindowHours = 24;
|
|
|
|
function getCurrentShiftWindow(DateTimeImmutable $now, DateTimeZone $tz, int $durationHours): array
|
|
{
|
|
$localized = $now->setTimezone($tz);
|
|
$todayShiftStart = $localized->setTime(5, 0, 0);
|
|
|
|
if ($localized < $todayShiftStart) {
|
|
$windowEnd = $todayShiftStart->modify('-1 day');
|
|
} else {
|
|
$windowEnd = $todayShiftStart;
|
|
}
|
|
|
|
$windowStart = $windowEnd->sub(new DateInterval('PT' . $durationHours . 'H'));
|
|
|
|
return [$windowStart, $windowEnd];
|
|
}
|
|
|
|
/**
|
|
* @param mixed $value Interval from user input.
|
|
*/
|
|
function sanitizeInterval($value): int
|
|
{
|
|
$interval = (int) ($value ?? 15);
|
|
$allowed = [5, 15, 30, 60];
|
|
|
|
if (!in_array($interval, $allowed, true)) {
|
|
return 15;
|
|
}
|
|
|
|
return $interval;
|
|
}
|
|
|
|
function sanitizeWindowHours($value, array $allowed, int $fallback): int
|
|
{
|
|
$candidate = (int) ($value ?? 0);
|
|
|
|
if (!in_array($candidate, $allowed, true)) {
|
|
return $fallback;
|
|
}
|
|
|
|
return $candidate;
|
|
}
|
|
|
|
function alignToInterval(DateTimeImmutable $dateTime, int $interval, DateTimeZone $tz): DateTimeImmutable
|
|
{
|
|
$localized = $dateTime->setTimezone($tz);
|
|
$minute = (int) $localized->format('i');
|
|
$second = (int) $localized->format('s');
|
|
|
|
$adjustMinutes = $minute % $interval;
|
|
|
|
if ($adjustMinutes > 0) {
|
|
$localized = $localized->modify(sprintf('-%d minutes', $adjustMinutes));
|
|
}
|
|
|
|
if ($second > 0) {
|
|
$localized = $localized->modify(sprintf('-%d seconds', $second));
|
|
}
|
|
|
|
return $localized;
|
|
}
|
|
|
|
function minutesToLabel(int $minutes): string
|
|
{
|
|
$hours = intdiv($minutes, 60);
|
|
$remaining = $minutes % 60;
|
|
|
|
return sprintf('%02d:%02d', $hours, $remaining);
|
|
}
|
|
|
|
function buildCohortTimeline(int $interval, int $durationHours): array
|
|
{
|
|
$timeline = [];
|
|
$maxMinutes = $durationHours * 60;
|
|
|
|
for ($offset = 0; $offset < $maxMinutes; $offset += $interval) {
|
|
$timeline[] = [
|
|
'offset' => $offset,
|
|
'label' => minutesToLabel($offset),
|
|
];
|
|
}
|
|
|
|
return $timeline;
|
|
}
|
|
|
|
function formatDateTime(DateTimeImmutable $dateTime): string
|
|
{
|
|
return $dateTime->format('Y-m-d H:i:s');
|
|
}
|
|
|
|
function fetchBestDayDefinition(PDO $pdo, string $tag, DateTimeZone $tz, int $durationHours): ?array
|
|
{
|
|
$durationDays = max(1, (int) round($durationHours / 24));
|
|
$lookbackDays = 60;
|
|
$minimumSamples = 12 * $durationDays;
|
|
|
|
$lookbackWindowDays = $lookbackDays + $durationDays;
|
|
|
|
$sql = "
|
|
WITH shift_windows AS (
|
|
SELECT
|
|
CAST(DATEADD(HOUR, -5, a.TimeStamp) AS date) AS shift_date,
|
|
SUM(CAST(a.Value AS float)) AS sum_value,
|
|
COUNT(*) AS sample_count
|
|
FROM dbo.archive AS a
|
|
INNER JOIN dbo.id_names AS n
|
|
ON a.ID = n.idnumber
|
|
WHERE n.name = ?
|
|
AND a.TimeStamp >= DATEADD(DAY, -CAST(? AS int), GETDATE())
|
|
GROUP BY CAST(DATEADD(HOUR, -5, a.TimeStamp) AS date)
|
|
),
|
|
ordered AS (
|
|
SELECT
|
|
shift_date,
|
|
sum_value,
|
|
sample_count,
|
|
ROW_NUMBER() OVER (ORDER BY shift_date) AS rn
|
|
FROM shift_windows
|
|
),
|
|
rolling AS (
|
|
SELECT
|
|
o.shift_date AS window_start_date,
|
|
SUM(w.sum_value) AS total_sum_value,
|
|
SUM(w.sample_count) AS total_sample_count,
|
|
COUNT(DISTINCT w.rn) AS shifts_in_window
|
|
FROM ordered AS o
|
|
JOIN ordered AS w
|
|
ON w.rn BETWEEN o.rn AND o.rn + (CAST(? AS int) - 1)
|
|
GROUP BY o.shift_date
|
|
)
|
|
SELECT TOP 1
|
|
window_start_date,
|
|
total_sum_value / NULLIF(total_sample_count, 0) AS avg_value,
|
|
total_sample_count
|
|
FROM rolling
|
|
WHERE shifts_in_window = CAST(? AS int)
|
|
AND total_sample_count >= ?
|
|
ORDER BY avg_value DESC;
|
|
";
|
|
|
|
$stmt = $pdo->prepare($sql);
|
|
$stmt->execute([
|
|
$tag,
|
|
$lookbackWindowDays,
|
|
$durationDays,
|
|
$durationDays,
|
|
$minimumSamples,
|
|
]);
|
|
$row = $stmt->fetch(PDO::FETCH_ASSOC);
|
|
|
|
if (!$row) {
|
|
return null;
|
|
}
|
|
|
|
$rawDate = $row['window_start_date'] ?? null;
|
|
|
|
if ($rawDate === null) {
|
|
return null;
|
|
}
|
|
|
|
if ($rawDate instanceof DateTimeInterface) {
|
|
$date = DateTimeImmutable::createFromInterface($rawDate);
|
|
} else {
|
|
$date = DateTimeImmutable::createFromFormat('Y-m-d', (string) $rawDate, $tz);
|
|
}
|
|
|
|
if (!$date instanceof DateTimeImmutable) {
|
|
return null;
|
|
}
|
|
|
|
$start = $date->setTime(5, 0, 0);
|
|
$end = $start->add(new DateInterval('PT' . $durationHours . 'H'));
|
|
|
|
return [
|
|
'label' => sprintf('Best %d-hour window (%s)', $durationHours, $start->format('M j, Y')),
|
|
'start' => $start,
|
|
'end' => $end,
|
|
'score' => (float) $row['avg_value'],
|
|
'sample_count' => (int) $row['total_sample_count'],
|
|
];
|
|
}
|
|
|
|
function fetchCohortSeries(
|
|
PDO $pdo,
|
|
string $tag,
|
|
DateTimeImmutable $start,
|
|
DateTimeImmutable $end,
|
|
int $interval,
|
|
DateTimeZone $tz
|
|
): array {
|
|
if ($end <= $start) {
|
|
return [
|
|
'points' => [],
|
|
'stats' => null,
|
|
'expectedBuckets' => 0,
|
|
];
|
|
}
|
|
|
|
$durationMinutes = max(0, (int) floor(($end->getTimestamp() - $start->getTimestamp()) / 60));
|
|
|
|
$bucketExpr = sprintf(
|
|
'DATEADD(MINUTE, DATEDIFF(MINUTE, 0, a.TimeStamp) / %d * %d, 0)',
|
|
$interval,
|
|
$interval
|
|
);
|
|
|
|
$sql = "
|
|
WITH bucketed AS (
|
|
SELECT
|
|
{$bucketExpr} AS bucket_start,
|
|
AVG(CAST(a.Value AS float)) AS avg_value,
|
|
COUNT(*) AS samples
|
|
FROM dbo.archive AS a
|
|
INNER JOIN dbo.id_names AS n
|
|
ON a.ID = n.idnumber
|
|
WHERE n.name = ?
|
|
AND a.TimeStamp >= ?
|
|
AND a.TimeStamp < ?
|
|
GROUP BY {$bucketExpr}
|
|
)
|
|
SELECT
|
|
CONVERT(varchar(19), bucket_start, 120) AS bucket_start_string,
|
|
avg_value,
|
|
samples
|
|
FROM bucketed
|
|
ORDER BY bucket_start ASC;
|
|
";
|
|
|
|
$stmt = $pdo->prepare($sql);
|
|
$stmt->execute([
|
|
$tag,
|
|
$start->format('Y-m-d H:i:s'),
|
|
$end->format('Y-m-d H:i:s'),
|
|
]);
|
|
|
|
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
|
|
$points = [];
|
|
$values = [];
|
|
$totalSamples = 0;
|
|
|
|
foreach ($rows as $row) {
|
|
$bucketStart = DateTimeImmutable::createFromFormat(
|
|
'Y-m-d H:i:s',
|
|
(string) $row['bucket_start_string'],
|
|
$tz
|
|
);
|
|
|
|
if (!$bucketStart instanceof DateTimeImmutable) {
|
|
continue;
|
|
}
|
|
|
|
$offsetMinutes = (int) floor(($bucketStart->getTimestamp() - $start->getTimestamp()) / 60);
|
|
|
|
if ($offsetMinutes < 0 || $offsetMinutes >= $durationMinutes) {
|
|
continue;
|
|
}
|
|
|
|
$value = $row['avg_value'] !== null ? (float) $row['avg_value'] : null;
|
|
$samples = (int) $row['samples'];
|
|
|
|
if ($value !== null) {
|
|
$values[] = $value;
|
|
}
|
|
|
|
$totalSamples += $samples;
|
|
|
|
$points[] = [
|
|
'offset' => $offsetMinutes,
|
|
'label' => minutesToLabel($offsetMinutes),
|
|
'value' => $value,
|
|
'samples' => $samples,
|
|
'bucket_start' => $bucketStart->format('Y-m-d H:i:s'),
|
|
];
|
|
}
|
|
|
|
$expectedBuckets = $interval > 0 ? (int) floor($durationMinutes / $interval) : 0;
|
|
$stats = calculateStats($values, $interval, $totalSamples, $expectedBuckets);
|
|
|
|
return [
|
|
'points' => $points,
|
|
'stats' => $stats,
|
|
'expectedBuckets' => $expectedBuckets,
|
|
];
|
|
}
|
|
|
|
function calculateStats(array $values, int $interval, int $totalSamples, int $expectedBuckets): ?array
|
|
{
|
|
$count = count($values);
|
|
|
|
if ($count === 0) {
|
|
return null;
|
|
}
|
|
|
|
$sum = array_sum($values);
|
|
$average = $sum / $count;
|
|
$minimum = min($values);
|
|
$maximum = max($values);
|
|
$last = end($values);
|
|
$variance = 0.0;
|
|
|
|
foreach ($values as $value) {
|
|
$variance += ($value - $average) * ($value - $average);
|
|
}
|
|
|
|
$stdDeviation = $count > 0 ? sqrt($variance / $count) : 0.0;
|
|
$integral = $sum * ($interval / 60);
|
|
$coverage = $expectedBuckets > 0 ? ($count / $expectedBuckets) * 100 : 0.0;
|
|
|
|
return [
|
|
'count' => $count,
|
|
'avg' => $average,
|
|
'min' => $minimum,
|
|
'max' => $maximum,
|
|
'last' => $last,
|
|
'stddev' => $stdDeviation,
|
|
'integral' => $integral,
|
|
'coverage' => $coverage,
|
|
'interval' => $interval,
|
|
'samples' => $totalSamples,
|
|
'expected_buckets' => $expectedBuckets,
|
|
];
|
|
}
|
|
|
|
try {
|
|
$pdo = new PDO(
|
|
"sqlsrv:Server=$servername;Database=$dbname",
|
|
$username,
|
|
$password,
|
|
[
|
|
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
|
|
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
|
|
]
|
|
);
|
|
} catch (PDOException $e) {
|
|
if (isset($_POST['action'])) {
|
|
header('Content-Type: application/json');
|
|
echo json_encode([
|
|
'success' => false,
|
|
'error' => 'Database connection failed: ' . $e->getMessage(),
|
|
]);
|
|
exit;
|
|
}
|
|
|
|
die('Connection failed: ' . $e->getMessage());
|
|
}
|
|
|
|
if (isset($_POST['action'])) {
|
|
ob_clean();
|
|
header('Content-Type: application/json');
|
|
|
|
try {
|
|
switch ($_POST['action']) {
|
|
case 'get_tags':
|
|
$stmt = $pdo->prepare(
|
|
"SELECT DISTINCT name
|
|
FROM dbo.id_names
|
|
WHERE name IS NOT NULL
|
|
AND name <> ''
|
|
ORDER BY name"
|
|
);
|
|
$stmt->execute();
|
|
$tags = $stmt->fetchAll(PDO::FETCH_COLUMN);
|
|
|
|
echo json_encode([
|
|
'success' => true,
|
|
'tags' => $tags,
|
|
]);
|
|
break;
|
|
|
|
case 'get_cohort_data':
|
|
$tag = trim((string) ($_POST['tag'] ?? ''));
|
|
|
|
if ($tag === '') {
|
|
throw new RuntimeException('Select at least one historian tag.');
|
|
}
|
|
|
|
$interval = sanitizeInterval($_POST['interval'] ?? 15);
|
|
$windowHours = sanitizeWindowHours(
|
|
$_POST['window_hours'] ?? $defaultWindowHours,
|
|
$windowOptions,
|
|
$defaultWindowHours
|
|
);
|
|
|
|
$cohortsRaw = $_POST['cohorts'] ?? '[]';
|
|
if (is_string($cohortsRaw)) {
|
|
$decoded = json_decode($cohortsRaw, true);
|
|
if (json_last_error() !== JSON_ERROR_NONE) {
|
|
throw new RuntimeException('Invalid cohort payload: ' . json_last_error_msg());
|
|
}
|
|
} else {
|
|
$decoded = $cohortsRaw;
|
|
}
|
|
|
|
$allowedCohorts = ['current', 'previous_week', 'best_day'];
|
|
$cohortKeys = array_values(
|
|
array_intersect(
|
|
$allowedCohorts,
|
|
is_array($decoded) ? $decoded : []
|
|
)
|
|
);
|
|
|
|
if (empty($cohortKeys)) {
|
|
$cohortKeys = $allowedCohorts;
|
|
}
|
|
|
|
if (!in_array('current', $cohortKeys, true)) {
|
|
$cohortKeys[] = 'current';
|
|
}
|
|
|
|
$now = new DateTimeImmutable('now', $appTimeZone);
|
|
[$shiftStart, $shiftEnd] = getCurrentShiftWindow($now, $appTimeZone, $windowHours);
|
|
$currentEnd = alignToInterval($shiftEnd, $interval, $appTimeZone);
|
|
$currentStart = $currentEnd->sub(new DateInterval('PT' . $windowHours . 'H'));
|
|
|
|
$definitions = [];
|
|
$infoMessages = [];
|
|
|
|
foreach ($cohortKeys as $key) {
|
|
switch ($key) {
|
|
case 'current':
|
|
$definitions['current'] = [
|
|
'label' => sprintf('Current %d hours', $windowHours),
|
|
'start' => $currentStart,
|
|
'end' => $currentEnd,
|
|
];
|
|
break;
|
|
|
|
case 'previous_week':
|
|
$definitions['previous_week'] = [
|
|
'label' => 'Same window last week',
|
|
'start' => $currentStart->sub(new DateInterval('P7D')),
|
|
'end' => $currentEnd->sub(new DateInterval('P7D')),
|
|
];
|
|
break;
|
|
|
|
case 'best_day':
|
|
$bestDefinition = fetchBestDayDefinition($pdo, $tag, $appTimeZone, $windowHours);
|
|
|
|
if ($bestDefinition !== null) {
|
|
$definitions['best_day'] = $bestDefinition;
|
|
if (!empty($bestDefinition['score'])) {
|
|
$infoMessages[] = sprintf(
|
|
'Best %d-hour window average was %.2f across %d samples.',
|
|
$windowHours,
|
|
$bestDefinition['score'],
|
|
$bestDefinition['sample_count']
|
|
);
|
|
}
|
|
} else {
|
|
$infoMessages[] = 'Best window cohort unavailable for the selected tag.';
|
|
}
|
|
break;
|
|
}
|
|
}
|
|
|
|
$timeline = buildCohortTimeline($interval, $windowHours);
|
|
$infoMessages[] = sprintf(
|
|
'Comparing a %d-hour window with %d-minute buckets.',
|
|
$windowHours,
|
|
$interval
|
|
);
|
|
$cohorts = [];
|
|
|
|
foreach ($definitions as $key => $definition) {
|
|
$series = fetchCohortSeries(
|
|
$pdo,
|
|
$tag,
|
|
$definition['start'],
|
|
$definition['end'],
|
|
$interval,
|
|
$appTimeZone
|
|
);
|
|
|
|
$warnings = [];
|
|
|
|
if ($series['stats'] === null) {
|
|
$warnings[] = 'No historian data found for this cohort window.';
|
|
} elseif ($series['stats']['coverage'] < 70) {
|
|
$warnings[] = sprintf(
|
|
'Only %.1f%% of expected buckets populated.',
|
|
$series['stats']['coverage']
|
|
);
|
|
}
|
|
|
|
$cohorts[] = [
|
|
'key' => $key,
|
|
'label' => $definition['label'],
|
|
'start' => formatDateTime($definition['start']),
|
|
'end' => formatDateTime($definition['end']),
|
|
'points' => $series['points'],
|
|
'stats' => $series['stats'],
|
|
'warnings' => $warnings,
|
|
];
|
|
}
|
|
|
|
if (empty($cohorts)) {
|
|
throw new RuntimeException('No cohorts could be generated for the requested tag.');
|
|
}
|
|
|
|
echo json_encode([
|
|
'success' => true,
|
|
'tag' => $tag,
|
|
'intervalMinutes' => $interval,
|
|
'windowHours' => $windowHours,
|
|
'timeline' => $timeline,
|
|
'cohorts' => $cohorts,
|
|
'messages' => $infoMessages,
|
|
'generatedAt' => formatDateTime($currentEnd),
|
|
]);
|
|
break;
|
|
|
|
default:
|
|
throw new RuntimeException('Unsupported action.');
|
|
}
|
|
} catch (Throwable $exception) {
|
|
echo json_encode([
|
|
'success' => false,
|
|
'error' => $exception->getMessage(),
|
|
]);
|
|
}
|
|
|
|
exit;
|
|
}
|
|
|
|
require __DIR__ . '/../includes/layout/header.php';
|
|
?>
|
|
<div
|
|
class="cohort-overlay"
|
|
id="cohortLoadingOverlay"
|
|
aria-hidden="true"
|
|
>
|
|
<div
|
|
class="cohort-overlay__spinner"
|
|
role="status"
|
|
aria-label="Loading cohort data"
|
|
></div>
|
|
</div>
|
|
|
|
<div class="app-content cohort-app">
|
|
<section class="data-panel cohort-config">
|
|
<div class="panel-intro">
|
|
<h2>Live cohort comparison</h2>
|
|
<p>
|
|
Resample the current operating window and stack it against historical
|
|
cohorts to spot changes in trajectory, stability, and peaks.
|
|
</p>
|
|
</div>
|
|
|
|
<div class="cohort-config__grid">
|
|
<div class="cohort-config__section" aria-labelledby="cohort-tag-title">
|
|
<h3 class="cohort-config__title" id="cohort-tag-title">Tag selection</h3>
|
|
<p class="cohort-config__hint">
|
|
Choose a historian tag to plot. The latest window will line up against
|
|
the cohorts you enable.
|
|
</p>
|
|
<div class="cohort-field">
|
|
<label class="cohort-field__label" for="cohortTag">Historian tag</label>
|
|
<select id="cohortTag" class="cohort-select">
|
|
<option value="">Loading tags…</option>
|
|
</select>
|
|
</div>
|
|
</div>
|
|
|
|
<div class="cohort-config__section" aria-labelledby="cohort-window-title">
|
|
<h3 class="cohort-config__title" id="cohort-window-title">Window duration</h3>
|
|
<p class="cohort-config__hint">
|
|
Pick how many hours to include in the active run and matched cohorts.
|
|
</p>
|
|
<div class="cohort-field">
|
|
<label class="cohort-field__label" for="cohortWindow">Window length</label>
|
|
<select id="cohortWindow" class="cohort-select">
|
|
<?php foreach ($windowOptions as $hours): ?>
|
|
<option value="<?php echo (int) $hours; ?>"<?php echo $hours === $defaultWindowHours ? ' selected' : ''; ?>>
|
|
<?php echo (int) $hours; ?> hours
|
|
</option>
|
|
<?php endforeach; ?>
|
|
</select>
|
|
</div>
|
|
</div>
|
|
|
|
<div class="cohort-config__section" aria-labelledby="cohort-set-title">
|
|
<h3 class="cohort-config__title" id="cohort-set-title">Cohorts</h3>
|
|
<p class="cohort-config__hint">
|
|
Compare the active run to one or more historical slices.
|
|
</p>
|
|
<div class="cohort-checkboxes">
|
|
<label class="cohort-checkbox">
|
|
<input type="checkbox" value="current" checked disabled>
|
|
<span>Current window</span>
|
|
</label>
|
|
<label class="cohort-checkbox">
|
|
<input type="checkbox" value="previous_week" checked>
|
|
<span>Same window last week</span>
|
|
</label>
|
|
<label class="cohort-checkbox">
|
|
<input type="checkbox" value="best_day" checked>
|
|
<span>Best window in the past 60 days</span>
|
|
</label>
|
|
</div>
|
|
</div>
|
|
|
|
<div class="cohort-config__section" aria-labelledby="cohort-interval-title">
|
|
<h3 class="cohort-config__title" id="cohort-interval-title">Bucket size</h3>
|
|
<p class="cohort-config__hint">
|
|
All cohorts are resampled to a common cadence so the comparison
|
|
stays apples-to-apples.
|
|
</p>
|
|
<div class="cohort-field">
|
|
<label class="cohort-field__label" for="cohortInterval">Aggregation step</label>
|
|
<select id="cohortInterval" class="cohort-select">
|
|
<option value="5">5 minutes</option>
|
|
<option value="15" selected>15 minutes</option>
|
|
<option value="30">30 minutes</option>
|
|
<option value="60">60 minutes</option>
|
|
</select>
|
|
</div>
|
|
</div>
|
|
</div>
|
|
|
|
<div class="cohort-actions">
|
|
<button type="button" class="button" id="cohortCompareBtn">Compare cohorts</button>
|
|
<button type="button" class="button button--ghost" id="cohortClearBtn">Clear results</button>
|
|
<button type="button" class="button button--ghost" id="cohortToggleDebugBtn">
|
|
Toggle debug panel
|
|
</button>
|
|
</div>
|
|
</section>
|
|
|
|
<div id="cohortStatus" class="cohort-status" aria-live="polite"></div>
|
|
|
|
<section class="cohort-results is-hidden" id="cohortResults">
|
|
<div class="cohort-results__grid">
|
|
<article class="data-panel cohort-results__panel">
|
|
<div class="cohort-results__header">
|
|
<div>
|
|
<h3>Cohort overlay</h3>
|
|
<p>
|
|
Relative time 0:00 represents the oldest point in the selected window.
|
|
Hover to see bucket values and sample counts.
|
|
</p>
|
|
</div>
|
|
<div class="cohort-results__controls">
|
|
<button type="button" class="button button--ghost" id="cohortToggleTableBtn">
|
|
Show raw data table
|
|
</button>
|
|
</div>
|
|
</div>
|
|
<div class="cohort-chart__wrapper">
|
|
<canvas id="cohortChart"></canvas>
|
|
</div>
|
|
|
|
<div class="cohort-table__wrapper is-hidden" id="cohortTableWrapper">
|
|
<table class="cohort-table">
|
|
<thead id="cohortTableHead"></thead>
|
|
<tbody id="cohortTableBody"></tbody>
|
|
</table>
|
|
</div>
|
|
</article>
|
|
|
|
<article class="data-panel cohort-results__panel">
|
|
<div class="cohort-results__header">
|
|
<div>
|
|
<h3>Snapshot summary</h3>
|
|
<p>Key metrics across each cohort using the shared cadence.</p>
|
|
</div>
|
|
</div>
|
|
<div class="cohort-summary" id="cohortSummary"></div>
|
|
<div class="cohort-messages" id="cohortMessages"></div>
|
|
</article>
|
|
</div>
|
|
</section>
|
|
|
|
<section class="data-panel cohort-debug is-hidden" id="cohortDebugPanel">
|
|
<div class="cohort-debug__header">
|
|
<h3>Debug details</h3>
|
|
<button type="button" class="button button--ghost" id="cohortHideDebugBtn">
|
|
Hide debug
|
|
</button>
|
|
</div>
|
|
<pre class="cohort-debug__content" id="cohortDebugContent"></pre>
|
|
</section>
|
|
</div>
|
|
|
|
<script src="<?php echo htmlspecialchars($chartJsCdn, ENT_QUOTES); ?>"></script>
|
|
|
|
<script>
|
|
const cohortState = {
|
|
chart: null,
|
|
elements: {},
|
|
timeline: [],
|
|
data: null,
|
|
windowHours: <?php echo (int) $defaultWindowHours; ?>,
|
|
};
|
|
|
|
const cohortDefaults = {
|
|
windowHours: cohortState.windowHours,
|
|
};
|
|
|
|
const cohortColorMap = {
|
|
current: '#38bdf8',
|
|
previous_week: '#f97316',
|
|
best_day: '#10b981',
|
|
};
|
|
|
|
document.addEventListener('DOMContentLoaded', () => {
|
|
cacheCohortElements();
|
|
bindCohortEvents();
|
|
loadCohortTags();
|
|
});
|
|
|
|
function cacheCohortElements() {
|
|
cohortState.elements = {
|
|
overlay: document.getElementById('cohortLoadingOverlay'),
|
|
tagSelect: document.getElementById('cohortTag'),
|
|
windowSelect: document.getElementById('cohortWindow'),
|
|
intervalSelect: document.getElementById('cohortInterval'),
|
|
compareBtn: document.getElementById('cohortCompareBtn'),
|
|
clearBtn: document.getElementById('cohortClearBtn'),
|
|
toggleTableBtn: document.getElementById('cohortToggleTableBtn'),
|
|
tableWrapper: document.getElementById('cohortTableWrapper'),
|
|
tableHead: document.getElementById('cohortTableHead'),
|
|
tableBody: document.getElementById('cohortTableBody'),
|
|
status: document.getElementById('cohortStatus'),
|
|
results: document.getElementById('cohortResults'),
|
|
summary: document.getElementById('cohortSummary'),
|
|
messages: document.getElementById('cohortMessages'),
|
|
chartCanvas: document.getElementById('cohortChart'),
|
|
debugPanel: document.getElementById('cohortDebugPanel'),
|
|
debugContent: document.getElementById('cohortDebugContent'),
|
|
toggleDebugBtn: document.getElementById('cohortToggleDebugBtn'),
|
|
hideDebugBtn: document.getElementById('cohortHideDebugBtn'),
|
|
};
|
|
}
|
|
|
|
function bindCohortEvents() {
|
|
cohortState.elements.compareBtn.addEventListener('click', generateCohortComparison);
|
|
cohortState.elements.clearBtn.addEventListener('click', clearCohortResults);
|
|
cohortState.elements.toggleTableBtn.addEventListener('click', toggleCohortTable);
|
|
cohortState.elements.toggleDebugBtn.addEventListener('click', () => toggleCohortDebug(true));
|
|
cohortState.elements.hideDebugBtn.addEventListener('click', () => toggleCohortDebug(false));
|
|
|
|
if (cohortState.elements.windowSelect) {
|
|
cohortState.elements.windowSelect.addEventListener('change', updateCurrentWindowCheckboxLabel);
|
|
}
|
|
|
|
updateCurrentWindowCheckboxLabel();
|
|
}
|
|
|
|
async function loadCohortTags() {
|
|
showCohortLoading(true);
|
|
|
|
try {
|
|
const formData = new FormData();
|
|
formData.append('action', 'get_tags');
|
|
|
|
const response = await fetch(window.location.href, {
|
|
method: 'POST',
|
|
body: formData,
|
|
});
|
|
|
|
const text = await response.text();
|
|
let payload;
|
|
|
|
try {
|
|
payload = JSON.parse(text);
|
|
} catch (error) {
|
|
console.error('Failed to parse tag payload', error, text);
|
|
throw new Error('Unexpected response when loading tags.');
|
|
}
|
|
|
|
if (!payload.success) {
|
|
throw new Error(payload.error || 'Historian tags could not be loaded.');
|
|
}
|
|
|
|
renderTagOptions(payload.tags || []);
|
|
} catch (error) {
|
|
console.error(error);
|
|
setCohortStatus(error.message, 'error');
|
|
renderTagOptions([]);
|
|
} finally {
|
|
showCohortLoading(false);
|
|
}
|
|
}
|
|
|
|
function renderTagOptions(tags) {
|
|
const select = cohortState.elements.tagSelect;
|
|
select.innerHTML = '';
|
|
|
|
if (!tags.length) {
|
|
const option = document.createElement('option');
|
|
option.value = '';
|
|
option.textContent = 'No historian tags found';
|
|
select.appendChild(option);
|
|
select.disabled = true;
|
|
return;
|
|
}
|
|
|
|
tags.forEach((tag) => {
|
|
const option = document.createElement('option');
|
|
option.value = tag;
|
|
option.textContent = tag;
|
|
select.appendChild(option);
|
|
});
|
|
|
|
select.disabled = false;
|
|
}
|
|
|
|
function getSelectedCohortKeys() {
|
|
const checkboxes = document.querySelectorAll('.cohort-checkbox input[type="checkbox"]');
|
|
const keys = [];
|
|
|
|
checkboxes.forEach((checkbox) => {
|
|
if (checkbox.checked || checkbox.disabled) {
|
|
keys.push(checkbox.value);
|
|
}
|
|
});
|
|
|
|
return keys;
|
|
}
|
|
|
|
async function generateCohortComparison() {
|
|
clearCohortStatus();
|
|
|
|
const tag = cohortState.elements.tagSelect.value;
|
|
if (!tag) {
|
|
setCohortStatus('Select a historian tag before running the comparison.', 'error');
|
|
return;
|
|
}
|
|
|
|
const cohorts = getSelectedCohortKeys();
|
|
const interval = cohortState.elements.intervalSelect.value;
|
|
const windowSelect = cohortState.elements.windowSelect;
|
|
const windowHours = windowSelect && windowSelect.value
|
|
? windowSelect.value
|
|
: cohortDefaults.windowHours;
|
|
|
|
showCohortLoading(true);
|
|
cohortState.elements.compareBtn.disabled = true;
|
|
|
|
try {
|
|
const formData = new FormData();
|
|
formData.append('action', 'get_cohort_data');
|
|
formData.append('tag', tag);
|
|
formData.append('interval', interval);
|
|
formData.append('cohorts', JSON.stringify(cohorts));
|
|
formData.append('window_hours', windowHours);
|
|
|
|
const response = await fetch(window.location.href, {
|
|
method: 'POST',
|
|
body: formData,
|
|
});
|
|
|
|
const text = await response.text();
|
|
let payload;
|
|
|
|
try {
|
|
payload = JSON.parse(text);
|
|
} catch (error) {
|
|
console.error('Failed to parse cohort payload', error, text);
|
|
throw new Error('Unexpected response when generating cohorts.');
|
|
}
|
|
|
|
if (!payload.success) {
|
|
throw new Error(payload.error || 'Unable to generate cohort comparison.');
|
|
}
|
|
|
|
cohortState.data = payload;
|
|
cohortState.timeline = payload.timeline || [];
|
|
cohortState.windowHours = Number(payload.windowHours || windowHours);
|
|
if (payload.windowHours && cohortState.elements.windowSelect) {
|
|
cohortState.elements.windowSelect.value = String(payload.windowHours);
|
|
}
|
|
updateCurrentWindowCheckboxLabel();
|
|
|
|
renderCohortChart(payload);
|
|
renderCohortSummary(payload);
|
|
renderCohortTable(payload);
|
|
renderCohortMessages(payload.messages || []);
|
|
renderCohortDebug(payload);
|
|
|
|
cohortState.elements.results.classList.remove('is-hidden');
|
|
const windowLabel = Number.isFinite(cohortState.windowHours)
|
|
? `${cohortState.windowHours}-hour window`
|
|
: 'selected window';
|
|
setCohortStatus(`Cohort comparison ready for the ${windowLabel}.`, 'success');
|
|
} catch (error) {
|
|
console.error(error);
|
|
setCohortStatus(error.message, 'error');
|
|
} finally {
|
|
showCohortLoading(false);
|
|
cohortState.elements.compareBtn.disabled = false;
|
|
}
|
|
}
|
|
|
|
function renderCohortChart(payload) {
|
|
const canvas = cohortState.elements.chartCanvas;
|
|
const ctx = canvas.getContext('2d');
|
|
const timeline = payload.timeline || [];
|
|
const cohorts = payload.cohorts || [];
|
|
|
|
if (cohortState.chart) {
|
|
cohortState.chart.destroy();
|
|
cohortState.chart = null;
|
|
}
|
|
|
|
const labels = timeline.map((slot) => slot.label);
|
|
const datasets = cohorts.map((cohort) => {
|
|
const color = cohortColorMap[cohort.key] || '#64748b';
|
|
const valueMap = {};
|
|
const sampleMap = {};
|
|
|
|
(cohort.points || []).forEach((point) => {
|
|
valueMap[point.offset] = point.value;
|
|
sampleMap[point.offset] = point.samples;
|
|
});
|
|
|
|
const data = timeline.map((slot) => {
|
|
const value = valueMap[slot.offset];
|
|
|
|
if (value === undefined || value === null) {
|
|
return null;
|
|
}
|
|
|
|
return Number.parseFloat(value.toFixed(4));
|
|
});
|
|
|
|
return {
|
|
label: cohort.label,
|
|
data,
|
|
borderColor: color,
|
|
backgroundColor: color + '33',
|
|
cubicInterpolationMode: 'monotone',
|
|
tension: 0.2,
|
|
borderWidth: 2,
|
|
pointRadius: 3,
|
|
pointHoverRadius: 5,
|
|
spanGaps: true,
|
|
cohortSamples: sampleMap,
|
|
};
|
|
});
|
|
|
|
cohortState.chart = new Chart(ctx, {
|
|
type: 'line',
|
|
data: {
|
|
labels,
|
|
datasets,
|
|
},
|
|
options: {
|
|
responsive: true,
|
|
maintainAspectRatio: false,
|
|
interaction: {
|
|
mode: 'index',
|
|
intersect: false,
|
|
},
|
|
scales: {
|
|
x: {
|
|
title: {
|
|
display: true,
|
|
text: 'Relative time (hours:minutes)',
|
|
},
|
|
},
|
|
y: {
|
|
title: {
|
|
display: true,
|
|
text: 'Value',
|
|
},
|
|
ticks: {
|
|
precision: 2,
|
|
},
|
|
},
|
|
},
|
|
plugins: {
|
|
tooltip: {
|
|
callbacks: {
|
|
label(context) {
|
|
const label = context.dataset.label || '';
|
|
const value = context.formattedValue;
|
|
const samples = context.dataset.cohortSamples?.[cohortState.timeline[context.dataIndex].offset];
|
|
const sampleText = samples !== undefined ? `, samples: ${samples}` : '';
|
|
return `${label}: ${value}${sampleText}`;
|
|
},
|
|
},
|
|
},
|
|
legend: {
|
|
position: 'top',
|
|
},
|
|
},
|
|
},
|
|
});
|
|
}
|
|
|
|
function renderCohortSummary(payload) {
|
|
const container = cohortState.elements.summary;
|
|
container.innerHTML = '';
|
|
|
|
(payload.cohorts || []).forEach((cohort) => {
|
|
const card = document.createElement('article');
|
|
card.className = 'cohort-summary__card';
|
|
|
|
const header = document.createElement('div');
|
|
header.className = 'cohort-summary__header';
|
|
header.innerHTML = `
|
|
<h4>${escapeHtml(cohort.label)}</h4>
|
|
<p>${escapeHtml(cohort.start)} → ${escapeHtml(cohort.end)}</p>
|
|
`;
|
|
card.appendChild(header);
|
|
|
|
const statsList = document.createElement('dl');
|
|
statsList.className = 'cohort-summary__metrics';
|
|
|
|
if (cohort.stats) {
|
|
appendMetric(statsList, 'Average', formatNumber(cohort.stats.avg));
|
|
appendMetric(statsList, 'Min', formatNumber(cohort.stats.min));
|
|
appendMetric(statsList, 'Max', formatNumber(cohort.stats.max));
|
|
appendMetric(statsList, 'Std dev', formatNumber(cohort.stats.stddev));
|
|
appendMetric(statsList, 'Last bucket', formatNumber(cohort.stats.last));
|
|
appendMetric(statsList, 'Coverage', formatNumber(cohort.stats.coverage, 1) + '%');
|
|
appendMetric(statsList, 'Time-weighted sum', formatNumber(cohort.stats.integral));
|
|
appendMetric(statsList, 'Buckets', `${cohort.stats.count}/${cohort.stats.expected_buckets}`);
|
|
appendMetric(statsList, 'Samples', cohort.stats.samples.toLocaleString());
|
|
} else {
|
|
const empty = document.createElement('p');
|
|
empty.className = 'cohort-summary__empty';
|
|
empty.textContent = 'No historian data returned for this cohort.';
|
|
card.appendChild(empty);
|
|
}
|
|
|
|
if (cohort.stats) {
|
|
card.appendChild(statsList);
|
|
}
|
|
|
|
if (Array.isArray(cohort.warnings) && cohort.warnings.length) {
|
|
const warningList = document.createElement('ul');
|
|
warningList.className = 'cohort-summary__warnings';
|
|
cohort.warnings.forEach((warning) => {
|
|
const item = document.createElement('li');
|
|
item.textContent = warning;
|
|
warningList.appendChild(item);
|
|
});
|
|
card.appendChild(warningList);
|
|
}
|
|
|
|
container.appendChild(card);
|
|
});
|
|
}
|
|
|
|
function appendMetric(list, label, value) {
|
|
const term = document.createElement('dt');
|
|
term.textContent = label;
|
|
|
|
const detail = document.createElement('dd');
|
|
detail.textContent = value;
|
|
|
|
list.appendChild(term);
|
|
list.appendChild(detail);
|
|
}
|
|
|
|
function renderCohortTable(payload) {
|
|
const timeline = payload.timeline || [];
|
|
const cohorts = payload.cohorts || [];
|
|
const head = cohortState.elements.tableHead;
|
|
const body = cohortState.elements.tableBody;
|
|
|
|
head.innerHTML = '';
|
|
body.innerHTML = '';
|
|
|
|
if (!timeline.length || !cohorts.length) {
|
|
const messageRow = document.createElement('tr');
|
|
const messageCell = document.createElement('td');
|
|
messageCell.colSpan = Math.max(cohorts.length + 1, 1);
|
|
messageCell.textContent = 'No cohort data to display.';
|
|
messageRow.appendChild(messageCell);
|
|
body.appendChild(messageRow);
|
|
return;
|
|
}
|
|
|
|
const headerRow = document.createElement('tr');
|
|
const leadingHeader = document.createElement('th');
|
|
leadingHeader.textContent = 'Relative time';
|
|
headerRow.appendChild(leadingHeader);
|
|
|
|
cohorts.forEach((cohort) => {
|
|
const th = document.createElement('th');
|
|
th.textContent = cohort.label;
|
|
headerRow.appendChild(th);
|
|
});
|
|
|
|
head.appendChild(headerRow);
|
|
|
|
const cohortMaps = cohorts.map((cohort) => {
|
|
const valueMap = {};
|
|
(cohort.points || []).forEach((point) => {
|
|
valueMap[point.offset] = point.value;
|
|
});
|
|
return valueMap;
|
|
});
|
|
|
|
timeline.forEach((slot) => {
|
|
const row = document.createElement('tr');
|
|
const timeCell = document.createElement('td');
|
|
timeCell.className = 'timestamp';
|
|
timeCell.textContent = slot.label;
|
|
row.appendChild(timeCell);
|
|
|
|
cohortMaps.forEach((valueMap) => {
|
|
const cell = document.createElement('td');
|
|
const value = valueMap[slot.offset];
|
|
|
|
if (value === undefined || value === null) {
|
|
cell.className = 'null-cell';
|
|
cell.textContent = '--';
|
|
} else {
|
|
cell.className = 'numeric-cell';
|
|
cell.textContent = formatNumber(value);
|
|
}
|
|
|
|
row.appendChild(cell);
|
|
});
|
|
|
|
body.appendChild(row);
|
|
});
|
|
}
|
|
|
|
function renderCohortMessages(messages) {
|
|
const container = cohortState.elements.messages;
|
|
container.innerHTML = '';
|
|
|
|
if (!messages.length) {
|
|
return;
|
|
}
|
|
|
|
const list = document.createElement('ul');
|
|
list.className = 'cohort-messages__list';
|
|
|
|
messages.forEach((message) => {
|
|
const item = document.createElement('li');
|
|
item.textContent = message;
|
|
list.appendChild(item);
|
|
});
|
|
|
|
container.appendChild(list);
|
|
}
|
|
|
|
function renderCohortDebug(payload) {
|
|
cohortState.elements.debugContent.textContent = JSON.stringify(
|
|
payload,
|
|
null,
|
|
2
|
|
);
|
|
}
|
|
|
|
function toggleCohortTable() {
|
|
const wrapper = cohortState.elements.tableWrapper;
|
|
const button = cohortState.elements.toggleTableBtn;
|
|
|
|
if (wrapper.classList.contains('is-hidden')) {
|
|
wrapper.classList.remove('is-hidden');
|
|
button.textContent = 'Hide raw data table';
|
|
} else {
|
|
wrapper.classList.add('is-hidden');
|
|
button.textContent = 'Show raw data table';
|
|
}
|
|
}
|
|
|
|
function toggleCohortDebug(forceOpen = null) {
|
|
const panel = cohortState.elements.debugPanel;
|
|
|
|
const shouldOpen = forceOpen === null
|
|
? panel.classList.contains('is-hidden')
|
|
: forceOpen;
|
|
|
|
if (shouldOpen) {
|
|
panel.classList.remove('is-hidden');
|
|
} else {
|
|
panel.classList.add('is-hidden');
|
|
}
|
|
}
|
|
|
|
function updateCurrentWindowCheckboxLabel() {
|
|
const select = cohortState.elements.windowSelect;
|
|
const selectedValue = select && select.value ? select.value : cohortDefaults.windowHours;
|
|
const selected = Number(selectedValue);
|
|
|
|
if (Number.isFinite(selected) && selected > 0) {
|
|
cohortState.windowHours = selected;
|
|
} else {
|
|
cohortState.windowHours = cohortDefaults.windowHours;
|
|
}
|
|
|
|
const currentLabel = document.querySelector('.cohort-checkbox input[value="current"] + span');
|
|
const bestLabel = document.querySelector('.cohort-checkbox input[value="best_day"] + span');
|
|
const hoursText = Number.isFinite(selected) && selected > 0 ? `${selected}` : '';
|
|
|
|
if (currentLabel) {
|
|
currentLabel.textContent = hoursText
|
|
? `Current ${hoursText}-hour window`
|
|
: 'Current window';
|
|
}
|
|
|
|
if (bestLabel) {
|
|
bestLabel.textContent = hoursText
|
|
? `Best ${hoursText}-hour window in the past 60 days`
|
|
: 'Best window in the past 60 days';
|
|
}
|
|
}
|
|
|
|
function clearCohortResults() {
|
|
clearCohortStatus();
|
|
cohortState.elements.results.classList.add('is-hidden');
|
|
cohortState.elements.tableWrapper.classList.add('is-hidden');
|
|
cohortState.elements.toggleTableBtn.textContent = 'Show raw data table';
|
|
cohortState.elements.summary.innerHTML = '';
|
|
cohortState.elements.messages.innerHTML = '';
|
|
cohortState.elements.tableHead.innerHTML = '';
|
|
cohortState.elements.tableBody.innerHTML = '';
|
|
cohortState.elements.debugContent.textContent = '';
|
|
cohortState.elements.debugPanel.classList.add('is-hidden');
|
|
cohortState.timeline = [];
|
|
cohortState.data = null;
|
|
const windowSelect = cohortState.elements.windowSelect;
|
|
cohortState.windowHours = Number(windowSelect && windowSelect.value
|
|
? windowSelect.value
|
|
: cohortDefaults.windowHours);
|
|
|
|
if (cohortState.chart) {
|
|
cohortState.chart.destroy();
|
|
cohortState.chart = null;
|
|
}
|
|
|
|
updateCurrentWindowCheckboxLabel();
|
|
}
|
|
|
|
function showCohortLoading(show) {
|
|
cohortState.elements.overlay.style.display = show ? 'flex' : 'none';
|
|
}
|
|
|
|
function setCohortStatus(message, type = 'info') {
|
|
const container = cohortState.elements.status;
|
|
container.innerHTML = '';
|
|
|
|
const div = document.createElement('div');
|
|
div.className = `status-message status-message--${type}`;
|
|
div.textContent = message;
|
|
|
|
container.appendChild(div);
|
|
}
|
|
|
|
function clearCohortStatus() {
|
|
cohortState.elements.status.innerHTML = '';
|
|
}
|
|
|
|
function formatNumber(value, fractionDigits = 2) {
|
|
if (value === null || value === undefined || Number.isNaN(value)) {
|
|
return '--';
|
|
}
|
|
|
|
return Number.parseFloat(value).toFixed(fractionDigits);
|
|
}
|
|
|
|
function escapeHtml(value) {
|
|
return value?.toString().replace(/[&<>"']/g, (char) => {
|
|
const entities = {
|
|
'&': '&',
|
|
'<': '<',
|
|
'>': '>',
|
|
'"': '"',
|
|
"'": ''',
|
|
};
|
|
return entities[char] || char;
|
|
});
|
|
}
|
|
</script>
|
|
|
|
<?php require __DIR__ . '/../includes/layout/footer.php'; ?>
|