310 lines
14 KiB
TypeScript
310 lines
14 KiB
TypeScript
|
import { pool, loadConfig } from './db';
|
|||
|
|
|
|||
|
|
// The analytics engine. Fetches the ticket rows once and computes the Overall-tab
|
|||
|
|
// aggregations and the PM×size SLA heatmaps in memory (966 rows — trivial). Mirrors
|
|||
|
|
// the initial app's `analytics_data` derivations. All money is converted to the
|
|||
|
|
// display currency (GBP) via the fixed FX rates (units per 1 GBP).
|
|||
|
|
|
|||
|
|
interface Row {
|
|||
|
|
number: string; status: string; state: string; assigned_to: string | null;
|
|||
|
|
brand: string | null; market: string | null; business_unit: string | null;
|
|||
|
|
requested_for: string | null; opened_by: string | null;
|
|||
|
|
opened_date: Date | string | null; closed_date: Date | string | null;
|
|||
|
|
opened_at: Date | string | null;
|
|||
|
|
final_cost: string | number | null; currency_code: string | null; size: string | null;
|
|||
|
|
ttfr_minutes: number | null; client_resp_minutes: number | null;
|
|||
|
|
fulfillment_date: Date | string | null; first_assigned_date: Date | string | null;
|
|||
|
|
to_do_at: Date | string | null; in_uat_at: Date | string | null;
|
|||
|
|
ticket_year: number | null;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
const SIZES = ['XS', 'S', 'M', 'L', 'XL', 'XXL'];
|
|||
|
|
|
|||
|
|
function ymd(v: Date | string | null): string | null {
|
|||
|
|
if (v == null) return null;
|
|||
|
|
if (v instanceof Date) {
|
|||
|
|
return `${v.getFullYear()}-${String(v.getMonth() + 1).padStart(2, '0')}-${String(v.getDate()).padStart(2, '0')}`;
|
|||
|
|
}
|
|||
|
|
const s = String(v);
|
|||
|
|
return s.length >= 10 ? s.slice(0, 10) : null;
|
|||
|
|
}
|
|||
|
|
function month(v: Date | string | null): string | null {
|
|||
|
|
const d = ymd(v);
|
|||
|
|
return d ? d.slice(0, 7) : null;
|
|||
|
|
}
|
|||
|
|
function toDate(v: Date | string | null): Date | null {
|
|||
|
|
if (v == null) return null;
|
|||
|
|
if (v instanceof Date) return v;
|
|||
|
|
const s = String(v).includes('T') ? String(v) : String(v).replace(' ', 'T');
|
|||
|
|
const d = new Date(s);
|
|||
|
|
return Number.isNaN(d.getTime()) ? null : d;
|
|||
|
|
}
|
|||
|
|
function daysBetween(a: Date | string | null, b: Date | string | null): number | null {
|
|||
|
|
const da = toDate(a), db = toDate(b);
|
|||
|
|
if (!da || !db) return null;
|
|||
|
|
return (db.getTime() - da.getTime()) / 86_400_000;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
async function fetchRows(): Promise<Row[]> {
|
|||
|
|
const { rows } = await pool.query<Row>(`
|
|||
|
|
SELECT number, status, state, assigned_to, brand, market, business_unit,
|
|||
|
|
requested_for, opened_by, opened_date, closed_date, opened_at,
|
|||
|
|
final_cost, currency_code, size, ttfr_minutes, client_resp_minutes,
|
|||
|
|
fulfillment_date, first_assigned_date, to_do_at, in_uat_at, ticket_year
|
|||
|
|
FROM tickets
|
|||
|
|
`);
|
|||
|
|
return rows;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
function toGBP(cost: number | null, ccy: string | null, fx: Record<string, number>): number | null {
|
|||
|
|
if (cost == null || !(cost > 0)) return null;
|
|||
|
|
const rate = fx[(ccy || 'GBP').toUpperCase()] ?? 1;
|
|||
|
|
return cost / rate;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
function countBy<T>(items: T[], key: (t: T) => string | null): { key: string; label: string; count: number }[] {
|
|||
|
|
const m = new Map<string, number>();
|
|||
|
|
for (const it of items) {
|
|||
|
|
const k = key(it);
|
|||
|
|
if (!k) continue;
|
|||
|
|
m.set(k, (m.get(k) ?? 0) + 1);
|
|||
|
|
}
|
|||
|
|
return [...m.entries()].map(([k, count]) => ({ key: k, label: k, count })).sort((a, b) => b.count - a.count);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
export interface OverviewResponse {
|
|||
|
|
totals: { total: number; active: number; closed: number };
|
|||
|
|
openedByMonth: { month: string; count: number }[];
|
|||
|
|
closedByMonth: { month: string; count: number }[];
|
|||
|
|
revenueByMonth: { month: string; count: number }[]; // count = GBP revenue
|
|||
|
|
byState: { key: string; label: string; count: number }[];
|
|||
|
|
byBrand: NestedBucket[];
|
|||
|
|
byMarket: { key: string; label: string; count: number }[];
|
|||
|
|
byBusinessUnit: { key: string; label: string; count: number }[];
|
|||
|
|
byRequester: NestedBucket[];
|
|||
|
|
byRequesterShare: { key: string; label: string; count: number }[]; // full distribution for the donut
|
|||
|
|
lifetime: { buckets: { key: string; label: string; count: number }[]; medianDays: number | null; avgDays: number | null; closed: number };
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
interface NestedBucket { key: string; label: string; count: number; children: { key: string; label: string; count: number }[]; }
|
|||
|
|
|
|||
|
|
// Two-level breakdown: parent dimension → child dimension counts.
|
|||
|
|
function nestedCountBy(items: Row[], parent: (r: Row) => string | null, child: (r: Row) => string | null, limit = 20): NestedBucket[] {
|
|||
|
|
const m = new Map<string, { count: number; kids: Map<string, number> }>();
|
|||
|
|
for (const r of items) {
|
|||
|
|
const p = parent(r);
|
|||
|
|
if (!p) continue;
|
|||
|
|
const entry = m.get(p) ?? { count: 0, kids: new Map() };
|
|||
|
|
entry.count++;
|
|||
|
|
const c = child(r);
|
|||
|
|
if (c) entry.kids.set(c, (entry.kids.get(c) ?? 0) + 1);
|
|||
|
|
m.set(p, entry);
|
|||
|
|
}
|
|||
|
|
return [...m.entries()]
|
|||
|
|
.sort((a, b) => b[1].count - a[1].count)
|
|||
|
|
.slice(0, limit)
|
|||
|
|
.map(([key, v]) => ({
|
|||
|
|
key, label: key, count: v.count,
|
|||
|
|
children: [...v.kids.entries()].sort((a, b) => b[1] - a[1]).map(([k, count]) => ({ key: k, label: k, count })),
|
|||
|
|
}));
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
function bySeriesMonth(items: Row[], dateOf: (r: Row) => Date | string | null): { month: string; count: number }[] {
|
|||
|
|
const m = new Map<string, number>();
|
|||
|
|
for (const r of items) {
|
|||
|
|
const mo = month(dateOf(r));
|
|||
|
|
if (mo) m.set(mo, (m.get(mo) ?? 0) + 1);
|
|||
|
|
}
|
|||
|
|
return [...m.entries()].sort((a, b) => a[0].localeCompare(b[0])).map(([month, count]) => ({ month, count }));
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
export async function getOverview(): Promise<OverviewResponse> {
|
|||
|
|
const rows = await fetchRows();
|
|||
|
|
const cfg = loadConfig();
|
|||
|
|
const fx = cfg.fxRates;
|
|||
|
|
const closed = rows.filter(r => r.status === 'closed');
|
|||
|
|
const active = rows.filter(r => r.status === 'active');
|
|||
|
|
|
|||
|
|
// Revenue by close-month (display currency)
|
|||
|
|
const revMap = new Map<string, number>();
|
|||
|
|
for (const r of closed) {
|
|||
|
|
const mo = month(r.closed_date);
|
|||
|
|
const gbp = toGBP(r.final_cost != null ? Number(r.final_cost) : null, r.currency_code, fx);
|
|||
|
|
if (mo && gbp) revMap.set(mo, (revMap.get(mo) ?? 0) + gbp);
|
|||
|
|
}
|
|||
|
|
const revenueByMonth = [...revMap.entries()].sort((a, b) => a[0].localeCompare(b[0]))
|
|||
|
|
.map(([month, v]) => ({ month, count: Math.round(v) }));
|
|||
|
|
|
|||
|
|
// Lifetime at close (days) → 6 buckets + median/avg
|
|||
|
|
const lifetimes: number[] = [];
|
|||
|
|
for (const r of closed) {
|
|||
|
|
const d = daysBetween(r.opened_date ?? r.opened_at, r.closed_date);
|
|||
|
|
if (d != null && d >= 0) lifetimes.push(d);
|
|||
|
|
}
|
|||
|
|
const LB = [
|
|||
|
|
{ key: '<7d', label: '< 7 days', hi: 7 },
|
|||
|
|
{ key: '7-14d', label: '7–14 days', hi: 14 },
|
|||
|
|
{ key: '14-31d', label: '14–31 days', hi: 31 },
|
|||
|
|
{ key: '1-3m', label: '1–3 months', hi: 93 },
|
|||
|
|
{ key: '3-6m', label: '3–6 months', hi: 186 },
|
|||
|
|
{ key: '>6m', label: '> 6 months', hi: Infinity },
|
|||
|
|
];
|
|||
|
|
const buckets = LB.map(b => ({ key: b.key, label: b.label, count: 0 }));
|
|||
|
|
for (const d of lifetimes) {
|
|||
|
|
const i = LB.findIndex(b => d < b.hi);
|
|||
|
|
buckets[i === -1 ? LB.length - 1 : i].count++;
|
|||
|
|
}
|
|||
|
|
const sorted = [...lifetimes].sort((a, b) => a - b);
|
|||
|
|
const medianDays = sorted.length ? Math.round(sorted[Math.floor(sorted.length / 2)]) : null;
|
|||
|
|
const avgDays = sorted.length ? Math.round(sorted.reduce((a, b) => a + b, 0) / sorted.length) : null;
|
|||
|
|
|
|||
|
|
return {
|
|||
|
|
totals: { total: rows.length, active: active.length, closed: closed.length },
|
|||
|
|
openedByMonth: bySeriesMonth(rows, r => r.opened_date ?? r.opened_at),
|
|||
|
|
closedByMonth: bySeriesMonth(closed, r => r.closed_date),
|
|||
|
|
revenueByMonth,
|
|||
|
|
byState: countBy(active, r => r.state || '—'),
|
|||
|
|
byBrand: nestedCountBy(rows, r => r.brand, r => r.market),
|
|||
|
|
byMarket: countBy(rows, r => r.market).slice(0, 20),
|
|||
|
|
byBusinessUnit: countBy(rows, r => normalizeBU(r.business_unit)),
|
|||
|
|
byRequester: nestedCountBy(rows, r => r.requested_for ?? r.opened_by, r => r.brand),
|
|||
|
|
byRequesterShare: countBy(rows, r => r.requested_for ?? r.opened_by),
|
|||
|
|
lifetime: { buckets, medianDays, avgDays, closed: lifetimes.length },
|
|||
|
|
};
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
function normalizeBU(bu: string | null): string | null {
|
|||
|
|
if (!bu) return null;
|
|||
|
|
const t = bu.trim();
|
|||
|
|
if (!t) return null;
|
|||
|
|
// Fix the casing dupes flagged in the data (e.g. "hygiene" → "Hygiene").
|
|||
|
|
return t.charAt(0).toUpperCase() + t.slice(1);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
// --- SLA heatmaps (PM × size) ----------------------------------------------
|
|||
|
|
export interface SlaCell { avgDays: number | null; count: number; onTime: number; onTimePct: number | null; }
|
|||
|
|
export interface SlaMetric {
|
|||
|
|
key: string; title: string; unit: 'days' | 'hours';
|
|||
|
|
norms: Record<string, number>; // per size, in the metric's unit
|
|||
|
|
pms: string[]; // row order
|
|||
|
|
sizes: string[]; // col order (XS..XXL)
|
|||
|
|
grid: Record<string, Record<string, SlaCell>>; // grid[pm][size]
|
|||
|
|
totals: Record<string, SlaCell>; // per-PM total across sizes
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
type MetricDef = {
|
|||
|
|
key: string; title: string; unit: 'days' | 'hours'; normKey: string;
|
|||
|
|
value: (r: Row) => number | null; // in DAYS
|
|||
|
|
scope: (r: Row) => boolean;
|
|||
|
|
};
|
|||
|
|
|
|||
|
|
const METRICS: MetricDef[] = [
|
|||
|
|
{ key: 'ttfr', title: 'Average Time to First Reply', unit: 'hours', normKey: 'ttfr',
|
|||
|
|
value: r => r.ttfr_minutes != null ? r.ttfr_minutes / 1440 : null, scope: r => r.ttfr_minutes != null },
|
|||
|
|
{ key: 'cresp', title: 'Average PM Response Time', unit: 'hours', normKey: 'cresp',
|
|||
|
|
value: r => r.client_resp_minutes != null ? r.client_resp_minutes / 1440 : null, scope: r => r.client_resp_minutes != null },
|
|||
|
|
{ key: 'avgclose', title: 'Average Time to Close a Project', unit: 'days', normKey: 'avgdays',
|
|||
|
|
value: r => daysBetween(r.opened_date ?? r.opened_at, r.closed_date), scope: r => r.status === 'closed' },
|
|||
|
|
{ key: 'otd', title: 'Projects Delivered on Time', unit: 'days', normKey: 'otd',
|
|||
|
|
value: r => daysBetween(r.opened_date ?? r.opened_at, r.closed_date), scope: r => r.status === 'closed' },
|
|||
|
|
{ key: 'assign', title: 'Time to Assign a PM', unit: 'days', normKey: 'asla',
|
|||
|
|
value: r => { const d = daysBetween(r.fulfillment_date, r.first_assigned_date); return d == null ? null : Math.max(0, d); }, scope: () => true },
|
|||
|
|
{ key: 'preview', title: 'Time to Send Preview Link', unit: 'days', normKey: 'psla',
|
|||
|
|
value: r => { const d = daysBetween(r.to_do_at, r.in_uat_at); return d != null && d >= 0 ? d : null; }, scope: () => true },
|
|||
|
|
];
|
|||
|
|
|
|||
|
|
function normDays(norm: number, unit: 'days' | 'hours'): number {
|
|||
|
|
return unit === 'hours' ? norm / 24 : norm;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
export async function getSlaHeatmaps(): Promise<SlaMetric[]> {
|
|||
|
|
const rows = await fetchRows();
|
|||
|
|
const cfg = loadConfig();
|
|||
|
|
const hidden = new Set<string>(); // future: pm_kpi_settings.hidden
|
|||
|
|
const pms = [...new Set(rows.map(r => r.assigned_to).filter((p): p is string => !!p && !hidden.has(p)))].sort();
|
|||
|
|
|
|||
|
|
return METRICS.map(def => {
|
|||
|
|
const norms = (cfg.norms?.[def.normKey] ?? {}) as Record<string, number>;
|
|||
|
|
const grid: Record<string, Record<string, SlaCell>> = {};
|
|||
|
|
const totals: Record<string, SlaCell> = {};
|
|||
|
|
|
|||
|
|
for (const pm of pms) {
|
|||
|
|
grid[pm] = {};
|
|||
|
|
const pmRows = rows.filter(r => r.assigned_to === pm && def.scope(r));
|
|||
|
|
let tSum = 0, tN = 0, tOnTime = 0, tScored = 0;
|
|||
|
|
for (const size of SIZES) {
|
|||
|
|
const cellRows = pmRows.filter(r => r.size === size);
|
|||
|
|
const vals = cellRows.map(def.value).filter((v): v is number => v != null && v >= 0);
|
|||
|
|
const cell = cellFor(vals, norms[size], def.unit);
|
|||
|
|
grid[pm][size] = cell;
|
|||
|
|
tSum += vals.reduce((a, b) => a + b, 0); tN += vals.length;
|
|||
|
|
if (norms[size] != null) { tOnTime += cell.onTime; tScored += vals.length; }
|
|||
|
|
}
|
|||
|
|
totals[pm] = {
|
|||
|
|
avgDays: tN ? round2(tSum / tN) : null, count: tN,
|
|||
|
|
onTime: tOnTime, onTimePct: tScored ? Math.round((tOnTime * 100) / tScored) : null,
|
|||
|
|
};
|
|||
|
|
}
|
|||
|
|
return { key: def.key, title: def.title, unit: def.unit, norms, pms, sizes: SIZES, grid, totals };
|
|||
|
|
});
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
function cellFor(valsDays: number[], normUnit: number | undefined, unit: 'days' | 'hours'): SlaCell {
|
|||
|
|
if (!valsDays.length) return { avgDays: null, count: 0, onTime: 0, onTimePct: null };
|
|||
|
|
const avg = valsDays.reduce((a, b) => a + b, 0) / valsDays.length;
|
|||
|
|
let onTime = 0, pct: number | null = null;
|
|||
|
|
if (normUnit != null) {
|
|||
|
|
const nd = normDays(normUnit, unit);
|
|||
|
|
onTime = valsDays.filter(v => v <= nd).length;
|
|||
|
|
pct = Math.round((onTime * 100) / valsDays.length);
|
|||
|
|
}
|
|||
|
|
return { avgDays: round2(avg), count: valsDays.length, onTime, onTimePct: pct };
|
|||
|
|
}
|
|||
|
|
function round2(n: number): number { return Math.round(n * 100) / 100; }
|
|||
|
|
|
|||
|
|
// Chart #17 — average time each Jira status is held, across all tickets that
|
|||
|
|
// carry per-status durations (ms), rendered in the board's workflow column order.
|
|||
|
|
// Statuses with < 2 tickets are dropped (matches the initial app).
|
|||
|
|
export interface JiraDuration { status: string; avgDays: number; avgHours: number; count: number; }
|
|||
|
|
export async function getJiraDurations(): Promise<{ order: string[]; rows: JiraDuration[] }> {
|
|||
|
|
const cfg = loadConfig();
|
|||
|
|
const order = cfg.jiraColumns ?? [];
|
|||
|
|
const { rows } = await pool.query<{ jira: { statusDurations?: Record<string, number> } | null }>(
|
|||
|
|
`SELECT jira FROM tickets WHERE jira ? 'statusDurations'`,
|
|||
|
|
);
|
|||
|
|
const agg = new Map<string, { sum: number; n: number }>();
|
|||
|
|
for (const r of rows) {
|
|||
|
|
const sd = r.jira?.statusDurations;
|
|||
|
|
if (!sd) continue;
|
|||
|
|
for (const [status, ms] of Object.entries(sd)) {
|
|||
|
|
const v = Number(ms);
|
|||
|
|
if (!Number.isFinite(v) || v <= 0) continue;
|
|||
|
|
const a = agg.get(status) ?? { sum: 0, n: 0 };
|
|||
|
|
a.sum += v; a.n += 1; agg.set(status, a);
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
const result: JiraDuration[] = [...agg.entries()]
|
|||
|
|
.filter(([, a]) => a.n >= 2)
|
|||
|
|
.map(([status, a]) => ({
|
|||
|
|
status,
|
|||
|
|
avgDays: round2(a.sum / a.n / 86_400_000),
|
|||
|
|
avgHours: round2(a.sum / a.n / 3_600_000),
|
|||
|
|
count: a.n,
|
|||
|
|
}))
|
|||
|
|
.sort((x, y) => {
|
|||
|
|
const ix = order.indexOf(x.status), iy = order.indexOf(y.status);
|
|||
|
|
if (ix !== -1 && iy !== -1) return ix - iy;
|
|||
|
|
if (ix !== -1) return -1;
|
|||
|
|
if (iy !== -1) return 1;
|
|||
|
|
return y.avgDays - x.avgDays;
|
|||
|
|
});
|
|||
|
|
return { order, rows: result };
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
export async function getConfig(): Promise<Record<string, unknown>> {
|
|||
|
|
const { rows } = await pool.query<{ key: string; value: unknown }>('SELECT key, value FROM app_config');
|
|||
|
|
const out: Record<string, unknown> = {};
|
|||
|
|
for (const r of rows) out[r.key] = r.value;
|
|||
|
|
return out;
|
|||
|
|
}
|