Mega Report

Supervision

Report for Supervisor

Supervision since 3 weeks ago

All Ears Room Booking Report.md

Must Do

Check List for each Session

• Appointment
• Invoice
• Check Payment
• Notes
• Obsidian
• Audio file
• Book room

Link to original

Client records to update

Client Hours - Unpopulated Current Issues

Client Hours - unpopulated current issues

TABLE
  date AS Date,
  clientref AS Client,
  medium AS Medium,
  hours AS Hours,
  row["where"] AS Where,
  file.link AS Note
FROM "100 CPD Record/Client Hours/Data"
WHERE string(row["current issues"]) = "<unpopulated>"
SORT date DESC, file.name DESC

Link to original

Client Hours since Sunday

Client Hours since Sunday

// Calculate the Sunday preceding today (even if today is Sunday)
let today = new Date();
let daysSinceSunday = today.getDay();
let offset = daysSinceSunday === 0 ? 7 : daysSinceSunday;
let startDate = new Date(today);
startDate.setDate(today.getDate() - offset - 0);
startDate.setHours(0, 0, 0, 0);
 
// Define endDate as today
let endDate = new Date();
endDate.setHours(23, 59, 59, 999);
 
// Helper to format dates as YYYY-MM-DD
function formatDate(date) {
    const dt = new Date(date);
    const year = dt.getFullYear();
    const month = String(dt.getMonth() + 1).padStart(2, '0');
    const day = String(dt.getDate()).padStart(2, '0');
    return `${year}-${month}-${day}`;
}
 
// Format hours with simulated decimal alignment using monospace font
function formatHours(hours) {
    return `\`${hours.toFixed(2).padStart(6, ' ')}\``;
}
 
// Helper to normalise medium display
function formatMedium(medium) {
    if (medium == null || medium === "") return "No Medium";
    if (Array.isArray(medium)) {
        const cleaned = medium
            .map(m => String(m).trim())
            .filter(Boolean);
        return cleaned.length ? cleaned.join(", ") : "No Medium";
    }
    return String(medium).trim() || "No Medium";
}
 
// If medium is an array matching date array length, use the corresponding item.
// Otherwise fall back to the whole medium field.
function getMediumForEntry(mediumField, index) {
    if (Array.isArray(mediumField)) {
        if (mediumField[index] != null && String(mediumField[index]).trim() !== "") {
            return String(mediumField[index]).trim();
        }
        const cleaned = mediumField
            .map(m => String(m).trim())
            .filter(Boolean);
        return cleaned.length ? cleaned.join(", ") : "No Medium";
    }
    return formatMedium(mediumField);
}
 
// Fetch pages and filter by date (supporting arrays)
let pages = dv.pages('"100 CPD Record" and #clienthours')
    .where(p => {
        const dates = Array.isArray(p.date) ? p.date : [p.date];
        return dates.some(d => {
            const dt = new Date(d);
            return dt >= startDate && dt <= endDate;
        });
    })
    .sort(p => {
        const d = Array.isArray(p.date) ? p.date[0] : p.date;
        return d;
    }, 'asc');
 
// Debug
dv.paragraph(`Start Date: ${startDate.toDateString()} | End Date: ${endDate.toDateString()}`);
dv.paragraph(`Total pages fetched: ${pages.length}`);
 
if (pages.length === 0) {
    dv.paragraph("No pages found for the specified query.");
} else {
    const groupedData = {};
    const agencyTotals = {};
    let grandTotal = 0;
 
    pages.forEach(p => {
        const agency = p.agency || "No Agency";
        const clientref = p.clientref || "No Clientref";
        const key = `${agency} - ${clientref}`;
        const hours = Number(p.hours) || 0;
        const dates = Array.isArray(p.date) ? p.date : [p.date];
        const mediumField = p.medium;
 
        if (!groupedData[key]) {
            groupedData[key] = {
                agency: agency,
                clientref: clientref,
                totalHours: 0,
                entries: []
            };
        }
 
        dates.forEach((d, i) => {
            const dt = new Date(d);
            if (dt >= startDate && dt <= endDate) {
                groupedData[key].entries.push({
                    file: p.file.link,
                    date: formatDate(dt),
                    medium: getMediumForEntry(mediumField, i),
                    hours: formatHours(hours)
                });
 
                groupedData[key].totalHours += hours;
 
                if (!agencyTotals[agency]) agencyTotals[agency] = 0;
                agencyTotals[agency] += hours;
                grandTotal += hours;
            }
        });
    });
 
    const tableData = [];
    const agencyGroups = {};
 
    for (const [key, group] of Object.entries(groupedData)) {
        group.entries.sort((a, b) => new Date(a.date) - new Date(b.date));
        const agency = group.agency;
 
        if (!agencyGroups[agency]) agencyGroups[agency] = [];
 
        group.entries.forEach(entry => {
            agencyGroups[agency].push([entry.file, entry.date, entry.medium, entry.hours]);
        });
 
        agencyGroups[agency].push([
            `**${group.agency} - ${group.clientref}**`,
            "",
            "",
            `\`${group.totalHours.toFixed(2).padStart(6, ' ')}\``
        ]);
    }
 
    for (const [agency, rows] of Object.entries(agencyGroups)) {
        tableData.push([`**${agency}**`, "", "", ""]);
        tableData.push(["----", "----", "----", "----"]);
        tableData.push(...rows);
        tableData.push(["----", "----", "----", "----"]);
        tableData.push([
            `**${agency} Subtotal**`,
            "",
            "",
            `**\`${agencyTotals[agency].toFixed(2).padStart(6, ' ')}\`**`
        ]);
        tableData.push(["====", "====", "====", "===="]);
    }
 
    tableData.push([
        "**Grand Total**",
        "",
        "",
        `**\`${grandTotal.toFixed(2).padStart(6, ' ')}\`**`
    ]);
 
    dv.table(["File", "Date", "Medium", "Hours"], tableData);
}

Link to original

Audio Processing Queue

Report on Audio Processing

const statusPath = "150 Audio Recordings/Processing Status/audio-processing-status.json";
 
const days = Number(dv.current()["Days"] ?? 14);
 
const now = new Date();
const cutoff = new Date(now);
cutoff.setDate(cutoff.getDate() - days);
 
let rows = [];
 
try {
  const raw = await app.vault.adapter.read(statusPath);
  rows = JSON.parse(raw);
} catch (e) {
  dv.paragraph("No processing status file found, or the JSON could not be read.");
}
 
function parseDate(value) {
  if (!value) return null;
  const normalised = String(value).replace(" ", "T");
  const date = new Date(normalised);
  return isNaN(date) ? null : date;
}
 
function badge(status) {
  if (status === "completed") return "✅ Completed";
  if (status === "failed") return "❌ Failed";
  if (status === "processing") return "🔄 Processing";
  if (status === "queued") return "⏳ Queued";
  return status ?? "Unknown";
}
 
const processed = rows
  .map(r => ({
    ...r,
    updatedDate: parseDate(r.updated),
    firstSeenDate: parseDate(r.first_seen)
  }))
  .filter(r => r.updatedDate && r.updatedDate >= cutoff)
  .sort((a, b) => b.updatedDate - a.updatedDate);
 
dv.paragraph(`Showing processing activity from the last ${days} days.`);
 
dv.table(
  ["File", "First Seen", "Status", "Stage", "Updated", "Message", "Error"],
  processed.map(r => [
    r.file,
    r.first_seen ?? "",
    badge(r.status),
    r.stage ?? "",
    r.updated ?? "",
    r.message ?? "",
    r.error ?? ""
  ])
);

const days = Number(dv.current()["Days"] ?? 14);
 
const transcriptFolder = "150 Audio Recordings"; // adjust if needed
 
const now = new Date();
const cutoff = new Date(now);
cutoff.setDate(cutoff.getDate() - days);
 
function fileDateInfo(p) {
  const d = p.date ?? p.file.ctime;
 
  if (typeof d?.toFormat === "function") {
    const key = d.toFormat("yyyy-MM-dd");
    return { date: new Date(`${key}T00:00:00`), key };
  }
 
  if (typeof d?.toISODate === "function") {
    const key = d.toISODate();
    return { date: new Date(`${key}T00:00:00`), key };
  }
 
  const match = String(d).match(/^(\d{4})-(\d{2})-(\d{2})/);
  if (match) {
    const key = `${match[1]}-${match[2]}-${match[3]}`;
    return { date: new Date(`${key}T00:00:00`), key };
  }
 
  const date = new Date(d);
  if (Number.isNaN(date.getTime())) return null;
  const key = `${date.getFullYear()}-${String(date.getMonth() + 1).padStart(2, "0")}-${String(date.getDate()).padStart(2, "0")}`;
  return { date, key };
}
 
let pages = dv.pages(`"${transcriptFolder}"`)
  .where(p => p.file.name !== dv.current().file.name)
  .where(p => p.file.ext === "md")
  .map(p => {
    const dateInfo = fileDateInfo(p);
    return {
      page: p,
      date: dateInfo?.date ?? null,
      dateKey: dateInfo?.key ?? "",
      name: p.file.name
    };
  })
  .where(x => x.date && !isNaN(x.date) && x.date >= cutoff)
  .array();
 
// Sort by filename (descending = newest first if names are ISO-like)
pages.sort((a, b) => b.name.localeCompare(a.name, undefined, { numeric: true }));
 
dv.paragraph(`Showing transcripts from the last ${days} days.`);
 
dv.table(
  ["Transcript", "Date", "Folder"],
  pages.map(x => [
    x.page.file.link,
    x.dateKey,
    x.page.file.folder
  ])
);

Link to original

Authoritative Hours and Ratios

Summary of Client and Supervision Hours

// ─── Configuration ───
// 0) Minimum session length (client hours) to count:
const minClientHours = 0.5;      // only count entries where p.hours > this
// 1) absolute-hour limit for non-UKCP supervision (BACP ceiling):
const maxNonUkcpHours   = 20;    // e.g. 20
// 2) percentage-limit (of total supervision) for non-UKCP:
const maxNonUkcpPercent = 100;   // e.g. 100% means no extra cap by percent
 
// ─── Date range ───
const startDate = new Date(`${dv.current()["Start Date"]}`);
const endDate   = new Date(`${dv.current()["End Date"]}`);
 
// ─── Safe number formatter ───
const fmt = n => {
  const num = Number(n);
  return isNaN(num) ? "N/A" : num.toFixed(2);
};
 
// ─── Helper to check array-or-single dates ───
function inRange(p) {
  const dates = Array.isArray(p.date) ? p.date : [p.date];
  return dates.some(d => {
    const dt = new Date(d);
    return dt >= startDate && dt <= endDate;
  });
}
 
// ─── 1) Sum client hours (only where hours > minClientHours) ───
 
let grandTotalClient = 0;
 
dv.pages('"100 CPD Record" and #clienthours')
 
  .where(inRange)
 
  .forEach(p => {
 
    const hrs = Number(p.hours || 0);
 
    if (!(hrs > minClientHours)) return; // strictly greater than threshold
 
  
 
    const dates = Array.isArray(p.date) ? p.date : [p.date];
 
    dates.forEach(d => {
 
      const dt = new Date(d);
 
      if (dt >= startDate && dt <= endDate) {
 
        grandTotalClient += hrs;
 
      }
 
    });
 
  });
 
// ─── 2) Sum supervision & UKCP hours (excluding peer supervision) ───
let grandTotalSup = 0, ukcpTotal = 0;
let formatTotals = { "1-to-1": 0, "group": 0, "peer": 0, "fishbowl": 0, "unspecified": 0 };
 
dv.pages('"100 CPD Record" and #supervision')
  .where(inRange)
  .forEach(p => {
    const dates = Array.isArray(p.date) ? p.date : [p.date];
    dates.forEach(d => {
      const dt = new Date(d);
      if (dt >= startDate && dt <= endDate) {
        const hrs = p.hours || 0;
        const rawFmt = (p.format || "unspecified").toString().toLowerCase();
        const allowed = ["1-to-1", "group", "peer", "fishbowl"];
        const normalised = allowed.includes(rawFmt) ? rawFmt : "unspecified";
 
        // Always record format hours
        formatTotals[normalised] = (formatTotals[normalised] || 0) + hrs;
 
        // Only include non-peer hours in totals
        if (normalised !== "peer") {
          grandTotalSup += hrs;
          if ((p.Body || "").toLowerCase() === "ukcp") {
            ukcpTotal += hrs;
          }
        }
      }
    });
  });
 
// ─── 3) Compute non-UKCP hours & percent ───
const nonUkcpTotal   = grandTotalSup - ukcpTotal;
const nonUkcpPercent = grandTotalSup > 0
  ? (nonUkcpTotal / grandTotalSup) * 100
  : NaN;
 
// ─── 4) Warnings ───
 
 
// ─── 5) Determine allowed non-UKCP “actual” threshold ───
const p = maxNonUkcpPercent / 100;
const percentThresholdHours = ((1 / (1 - p)) - 1) * ukcpTotal;
 
let nonUkcpThreshold;
if (nonUkcpTotal <= maxNonUkcpHours && nonUkcpPercent <= maxNonUkcpPercent) {
  nonUkcpThreshold = nonUkcpTotal;
} else {
  nonUkcpThreshold = Math.min(maxNonUkcpHours, percentThresholdHours);
}
 
// ─── 6) Compute Current Actual Ratio ───
const currentActualRatio = (ukcpTotal + nonUkcpThreshold) > 0
  ? fmt(grandTotalClient / (ukcpTotal + nonUkcpThreshold))
  : "N/A";
 
// ─── 7) Other ratios ───
const ratioAll  = grandTotalSup    > 0 ? fmt(grandTotalClient / grandTotalSup) : "N/A";
const ratioUKCP = ukcpTotal        > 0 ? fmt(grandTotalClient / ukcpTotal)   : "N/A";
 
// ─── 8) Render header & thresholds ───
dv.header(2, "Supervision Summary & Ratios");
 
// ─── 9) Render main table ───
const formatRows = Object.entries(formatTotals).map(
  ([f, hrs]) => [`Supervision (${f})`, fmt(hrs)]
);
 
dv.table(
  ["Calculation", "Result"],
  [
    ["**Grand Total Client Hours**", fmt(grandTotalClient)],
    ["**Total Allowable Supervision Hours (exc. peer)**", fmt(ukcpTotal + nonUkcpThreshold)],
    ["**Current Actual Ratio (Client ÷ (UKCP + allowed non-UKCP))**", currentActualRatio]
  ]
);
 
 

Link to original

Clients hours

Client Hours this Week

WriteUpp Appointment Pattern Check 2026-07-26

WriteUpp appointment pattern check

Report generated: 2026-07-26 06:30:01 BST Source: WriteUpp OpenAPI /v1/appointments from 2026-07-25 to 2026-10-24 Rows considered: from 2026-07-25 onwards.

Confidentiality note: this report is generated from appointment metadata only. It does not call the WriteUpp patients endpoint and does not write client names or contact details.

Timezone note: WriteUpp API appointment times are treated as UTC/GMT and shown here in Europe/London local time, including BST when applicable.

Status note: cancelled and non-attended appointments are included; no status filter is applied.

Location note: appointment locations are checked against the appointment type. Phone is flagged because the expected pattern only uses video and face-to-face sessions.

Expected pattern

• Monday: video
• Tuesday: face to face
• Wednesday: face to face
• Thursday: video

Summary

• Total appointment rows from yesterday onwards: 19
• Rows checked (Mon-Thu, video/face to face only): 8
• Appointment statuses included: all statuses returned by the date-filtered API request
• Mismatches: 0

Mismatches

None.

Checked appointments

Date	Weekday	Start Time	Type	Status	Location	Actual	Expected	Issue	Result
2026-07-27	Monday	2026-07-27 14:45	Video Consultation	Booked	Online	video	video		OK
2026-07-27	Monday	2026-07-27 17:15	Video Consultation	Booked	Online	video	video		OK
2026-07-27	Monday	2026-07-27 18:30	Video Consultation	Booked	Online	video	video		OK
2026-07-29	Wednesday	2026-07-29 13:00	Face to Face Session	Booked	All Ears Clinic	face_to_face	face_to_face		OK
2026-07-29	Wednesday	2026-07-29 16:45	Face to Face Session	Booked	All Ears Clinic	face_to_face	face_to_face		OK
2026-08-03	Monday	2026-08-03 16:00	Video Consultation	Booked	Online	video	video		OK
2026-08-05	Wednesday	2026-08-05 14:15	Face to Face Session	Booked	All Ears Clinic	face_to_face	face_to_face		OK
2026-08-05	Wednesday	2026-08-05 15:30	Face to Face Session	Booked	All Ears Clinic	face_to_face	face_to_face		OK

Daily counts

Date	Weekday	Session Type	Status	Count
2026-07-27	Monday	video	Booked	3
2026-07-29	Wednesday	face_to_face	Booked	2
2026-08-03	Monday	video	Booked	1
2026-08-05	Wednesday	face_to_face	Booked	2

Status counts

Status	Count
Booked	8

Link to original

Client Hours by Medium over All Agencies

All time - Percentage Client Hours by Medium - 3 cols

const startDate = new Date(`${dv.current()["Start Date"]}`);
const endDate   = new Date(`${dv.current()["End Date"]}`);
 
const MIN_SESSION_HOURS = 0.5; // 30 minutes
 
// Helper – render a number with at most 2 decimals
const fmt = n => parseFloat(n.toFixed(2)).toString();
 
// ──────────────────────────────────────────────────────────
// Fetch pages with the #clienthours tag in the right folder
let pages = dv
  .pages('"100 CPD Record" and #clienthours')
  .where(p => {
    const fileDate = new Date(p.date);
    const hrs = Number(p.hours) || 0;
    return fileDate >= startDate && fileDate <= endDate && hrs >= MIN_SESSION_HOURS;
  })
  .sort(p => p.date, "asc");
 
// Debug info
dv.paragraph(`Total records fetched: ${pages.length}`);
 
if (pages.length === 0) {
  dv.paragraph("No records found for the specified query.");
} else {
  // ────────────── Aggregate by “medium” ──────────────
  const groupedData   = {};
  let   grandTotalHrs = 0;
 
  pages.forEach(p => {
    const medium = p.medium || "No medium";
 
    if (!groupedData[medium]) {
      groupedData[medium] = { medium, totalHours: 0 };
    }
 
    const hrs = p.hours || 0;
    groupedData[medium].totalHours += hrs;
    grandTotalHrs += hrs;
  });
 
  // ─────────────── Build table rows ────────────────
  const tableData = Object.values(groupedData)
    .map(g => {
      const pct = fmt((g.totalHours / grandTotalHrs) * 100);    // % of total
      return [
        `**${g.medium} Total**`,
        `**${fmt(g.totalHours)}**`,
        `**${pct}%**`
      ];
    })
    .sort((a, b) => a[0].localeCompare(b[0]));   // alphabetical
 
  // Grand‑total row
  tableData.push([
    "**Grand Total**",
    `**${fmt(grandTotalHrs)}**`,
    "**100%**"
  ]);
 
  // Render
  dv.table(["Medium", "Total Hours", "Percentage of Total"], tableData);
}
 

Link to original

All time - Ratio Online to In Person

Report with Warnings

const startDate = new Date(`${dv.current()["Start Date"]}`);
const endDate   = new Date(`${dv.current()["End Date"]}`);
 
// ===================== CONFIG =====================
const REMOTE_MEDIUMS = ["phone", "teams", "writeupp-online"];
const IN_PERSON_KEYS = ["in-person"];     // add variants if needed: ["in-person","in person"]
const WARN_AMBER = 65;                    // % threshold for Amber
const WARN_RED   = 60;                    // % threshold for Red
const SHOW_OTHER_IF_NONZERO = true;       // show "Other Total" only if > 0
const MIN_SESSION_HOURS = 0.5;            // 30 minutes
// ==================================================
 
// Helpers
const fmt = n => isFinite(n) ? parseFloat(Number(n).toFixed(2)).toString() : "0";
const norm = s => String(s ?? "")
  .toLowerCase()
  .trim()
  .replace(/[_\s]+/g, "-"); // "In Person" => "in-person"
 
// ──────────────────────────────────────────────────────────
// Fetch pages with the #clienthours tag in the right folder
let pages = dv
  .pages('"100 CPD Record" and #clienthours')
  .where(p => {
    const fileDate = new Date(p.date);
    const hrs = Number(p.hours) || 0;
    return fileDate >= startDate && fileDate <= endDate && hrs >= MIN_SESSION_HOURS;
  })
  .sort(p => p.date, "asc");
 
// Debug info
dv.paragraph(`Total records fetched: ${pages.length}`);
 
if (pages.length === 0) {
  dv.paragraph("No records found for the specified query.");
} else {
  // ────────────── Aggregate by “medium” ──────────────
  const groupedData   = {}; // key: original medium label
  let   grandTotalHrs = 0;
 
  pages.forEach(p => {
    const mediumLabel = p.medium || "No medium";
    if (!groupedData[mediumLabel]) {
      groupedData[mediumLabel] = { medium: mediumLabel, totalHours: 0 };
    }
    const hrs = Number(p.hours) || 0;
    groupedData[mediumLabel].totalHours += hrs;
    grandTotalHrs += hrs;
  });
 
  if (grandTotalHrs === 0) {
    dv.paragraph("No hours recorded in the selected period.");
  } else {
    // ─────────────── Classify into In-Person / Remote / Other ───────────────
    const inPersonSet = new Set(IN_PERSON_KEYS.map(norm));
    const remoteSet   = new Set(REMOTE_MEDIUMS.map(norm));
 
    let inPersonHours = 0;
    let remoteHours   = 0;
    let otherHours    = 0;
 
    for (const g of Object.values(groupedData)) {
      const key = norm(g.medium);
      if (inPersonSet.has(key)) {
        inPersonHours += g.totalHours;
      } else if (remoteSet.has(key)) {
        remoteHours += g.totalHours;
      } else {
        otherHours += g.totalHours;
      }
    }
 
    const inPersonPct = (inPersonHours / grandTotalHrs) * 100;
    const remotePct   = (remoteHours   / grandTotalHrs) * 100;
    const otherPct    = (otherHours    / grandTotalHrs) * 100;
 
    // ─────────────── Build compact totals table ───────────────
    const rows = [
      ["**In-Person Total**", `**${fmt(inPersonHours)}**`, `**${fmt(inPersonPct)}%**`],
      ["**Remote Total (phone/Teams/writeupp-online)**", `**${fmt(remoteHours)}**`, `**${fmt(remotePct)}%**`],
    ];
 
    if (SHOW_OTHER_IF_NONZERO && otherHours > 0.0001) {
      rows.push(["**Other Total**", `**${fmt(otherHours)}**`, `**${fmt(otherPct)}%**`]);
    }
 
    rows.push(["**Grand Total**", `**${fmt(grandTotalHrs)}**`, "**100%**"]);
 
    dv.table(["Category", "Total Hours", "Percentage of Total"], rows);
 
    // ─────────────── In-person warning banner ───────────────
    let status = `✅ In-person percentage is healthy (${fmt(inPersonPct)}%).`;
    if (inPersonPct < WARN_AMBER && inPersonPct >= WARN_RED) {
      status = `🟠 Amber: In-person percentage is approaching a critically low level (${fmt(inPersonPct)}% < ${WARN_AMBER}%).`;
    } else if (inPersonPct < WARN_RED) {
      status = `🔴 Red: In-person percentage is critically low (${fmt(inPersonPct)}% < ${WARN_RED}%).`;
    }
 
    dv.paragraph(
      `**In-Person Check:** ${status}\n` +
      `• In-person hours: ${fmt(inPersonHours)} of ${fmt(grandTotalHrs)} total\n` +
      `• Thresholds — Amber: < ${WARN_AMBER}% | Red: < ${WARN_RED}%`
    );
  }
}
 

Link to original

---

All Clients with number of Hours

All time - CH by client- 3 cols by Hours

const startDate = new Date(dv.current()["Start Date"]);
const endDate = new Date(dv.current()["End Date"]);
 
// Fetch pages with the 'clienthours' tag
let pages = dv
  .pages('"100 CPD Record" and #clienthours')
  .where(p => {
    const fileDate = new Date(p.date);
    const hours = Number(p.hours ?? 0);
    return fileDate >= startDate && fileDate <= endDate && hours > 0.5;
  })
  .sort(p => p.date, "asc");
 
dv.paragraph(`Total records fetched: ${pages.length}`);
if (pages.length === 0) {
  dv.paragraph("No records found for the specified query.");
} else {
  const groupedData = {};
 
  pages.forEach(p => {
    const agency = p.agency || "No Agency";
    const clientref = p.clientref || "Not stated";
    const key = `${agency} - ${clientref}`;
    const hours = Number(p.hours ?? 0);
 
    // Handle medium safely
    const rawMedium = p.medium;
    const medium = Array.isArray(rawMedium)
      ? String(rawMedium[0]).toLowerCase()
      : String(rawMedium || "unspecified").toLowerCase();
 
    if (!groupedData[key]) {
      groupedData[key] = {
        agency,
        clientref,
        totalHours: 0,
        mediumHours: {}  // key = medium, value = total hours
      };
    }
 
    groupedData[key].totalHours += hours;
    groupedData[key].mediumHours[medium] = (groupedData[key].mediumHours[medium] || 0) + hours;
  });
 
  // Filter out zero-hour clients
  const filteredGroups = Object.values(groupedData).filter(
    group => group.totalHours > 0
  );
 
  // Calculate most used medium based on HOURS
  filteredGroups.forEach(group => {
    const { mediumHours, totalHours } = group;
    group.mostUsedMedium = "unclear";
 
    for (const [medium, hrs] of Object.entries(mediumHours)) {
      if (hrs > totalHours / 2) {
        group.mostUsedMedium = medium;
        break;
      }
    }
  });
 
  // Recalculate totals
  const clientsByAgency = {};
  let uniqueClients = new Set();
  let lineCount = 0;
  let grandTotalFiltered = 0;
  const agencyFilteredSubtotal = {};
 
  filteredGroups.forEach(group => {
    const { agency, clientref, totalHours } = group;
 
    uniqueClients.add(clientref);
 
    if (!clientsByAgency[agency]) {
      clientsByAgency[agency] = new Set();
    }
    clientsByAgency[agency].add(clientref);
 
    grandTotalFiltered += totalHours;
    agencyFilteredSubtotal[agency] = (agencyFilteredSubtotal[agency] || 0) + totalHours;
  });
 
  const singleSessionClients = filteredGroups.filter(
    group => group.totalHours <= 1
  ).length;
 
  const tableData = [];
 
  // Sort and build rows. Use Markdown bold, not HTML, so Pandoc/Zettlr preserves cells.
  filteredGroups
    .sort((a, b) => {
      const diff = b.totalHours - a.totalHours;
      if (diff !== 0) return diff;
      const agencyCmp = a.agency.localeCompare(b.agency);
      if (agencyCmp !== 0) return agencyCmp;
      return a.clientref.localeCompare(b.clientref);
    })
    .forEach(group => {
      tableData.push([
        `**${group.agency} - ${group.clientref} Total**`,
        `**${group.totalHours.toFixed(2)}**`,
        group.mostUsedMedium
      ]);
      lineCount++;
    });
 
  // Divider
  tableData.push(["---", "", ""]);
 
  // Agency subtotals
  Object.entries(agencyFilteredSubtotal)
    .sort((a, b) => a[0].localeCompare(b[0]))
    .forEach(([agency, subtotal]) => {
      tableData.push([
        `**${agency} Subtotal**`,
        `**${subtotal.toFixed(2)}**`,
        ""
      ]);
      lineCount++;
    });
 
  // Grand total
  tableData.push(["---", "", ""]);
  tableData.push([
    "**Grand Total**",
    `**${grandTotalFiltered.toFixed(2)}**`,
    ""
  ]);
 
  // Live Obsidian view. Dataview's dv.table does not expose Markdown alignment,
  // but the export block below emits a right-aligned Markdown separator for Pandoc.
  dv.table(
    ["Group", "Total Hours", "Most Used Medium"],
    tableData
  );
 
  // Client counts
  dv.paragraph("**Clients per agency:**");
  Object.entries(clientsByAgency)
    .sort((a, b) => a[0].localeCompare(b[0]))
    .forEach(([agency, set]) => {
      dv.paragraph(`- **${agency}**: ${set.size}`);
    });
 
  dv.paragraph(`Total unique clients (with >0 hours): **${uniqueClients.size}**`);
  dv.paragraph(`Clients with total hours ≤ 1 (single sessions): **${singleSessionClients}**`);
//  dv.paragraph(`Total lines in table (excluding grand total): **${lineCount}**`);
}

const startDate = new Date("2022-11-01");
const endDate = new Date("2031-04-30");
 
const escapeCell = (value) => String(value ?? "")
  .replace(/\|/g, "\\|")
  .replace(/\n/g, " ")
  .trim();
 
// Fetch pages with the 'clienthours' tag
let pages = dv
  .pages('"100 CPD Record" and #clienthours')
  .where(p => {
    const fileDate = new Date(p.date);
    const hours = Number(p.hours ?? 0);
    return fileDate >= startDate && fileDate <= endDate && hours > 0.5;
  })
  .sort(p => p.date, "asc");
 
const lines = [`Total records fetched: ${pages.length}`, ""];
 
if (pages.length === 0) {
  lines.push("No records found for the specified query.");
} else {
  const groupedData = {};
 
  pages.forEach(p => {
    const agency = p.agency || "No Agency";
    const clientref = p.clientref || "Not stated";
    const key = `${agency} - ${clientref}`;
    const hours = Number(p.hours ?? 0);
 
    const rawMedium = p.medium;
    const medium = Array.isArray(rawMedium)
      ? String(rawMedium[0]).toLowerCase()
      : String(rawMedium || "unspecified").toLowerCase();
 
    if (!groupedData[key]) {
      groupedData[key] = {
        agency,
        clientref,
        totalHours: 0,
        mediumHours: {}
      };
    }
 
    groupedData[key].totalHours += hours;
    groupedData[key].mediumHours[medium] = (groupedData[key].mediumHours[medium] || 0) + hours;
  });
 
  const filteredGroups = Object.values(groupedData).filter(
    group => group.totalHours > 0
  );
 
  filteredGroups.forEach(group => {
    const { mediumHours, totalHours } = group;
    group.mostUsedMedium = "unclear";
 
    for (const [medium, hrs] of Object.entries(mediumHours)) {
      if (hrs > totalHours / 2) {
        group.mostUsedMedium = medium;
        break;
      }
    }
  });
 
  const clientsByAgency = {};
  let uniqueClients = new Set();
  let grandTotalFiltered = 0;
  const agencyFilteredSubtotal = {};
 
  filteredGroups.forEach(group => {
    const { agency, clientref, totalHours } = group;
 
    uniqueClients.add(clientref);
 
    if (!clientsByAgency[agency]) {
      clientsByAgency[agency] = new Set();
    }
    clientsByAgency[agency].add(clientref);
 
    grandTotalFiltered += totalHours;
    agencyFilteredSubtotal[agency] = (agencyFilteredSubtotal[agency] || 0) + totalHours;
  });
 
  const singleSessionClients = filteredGroups.filter(
    group => group.totalHours <= 1
  ).length;
 
  lines.push("| Group | Total Hours | Most Used Medium |");
  lines.push("| --- | ---: | --- |");
 
  filteredGroups
    .sort((a, b) => {
      const diff = b.totalHours - a.totalHours;
      if (diff !== 0) return diff;
      const agencyCmp = a.agency.localeCompare(b.agency);
      if (agencyCmp !== 0) return agencyCmp;
      return a.clientref.localeCompare(b.clientref);
    })
    .forEach(group => {
      lines.push(`| **${escapeCell(group.agency)} - ${escapeCell(group.clientref)} Total** | **${group.totalHours.toFixed(2)}** | ${escapeCell(group.mostUsedMedium)} |`);
    });
 
  lines.push("| --- |  |  |");
 
  Object.entries(agencyFilteredSubtotal)
    .sort((a, b) => a[0].localeCompare(b[0]))
    .forEach(([agency, subtotal]) => {
      lines.push(`| **${escapeCell(agency)} Subtotal** | **${subtotal.toFixed(2)}** |  |`);
    });
 
  lines.push("| --- |  |  |");
  lines.push(`| **Grand Total** | **${grandTotalFiltered.toFixed(2)}** |  |`);
  lines.push("");
 
  lines.push("**Clients per agency:**", "");
  Object.entries(clientsByAgency)
    .sort((a, b) => a[0].localeCompare(b[0]))
    .forEach(([agency, set]) => {
      lines.push(`- **${escapeCell(agency)}**: ${set.size}`);
    });
 
  lines.push("", `Total unique clients (with >0 hours): **${uniqueClients.size}**`);
  lines.push(`Clients with total hours ≤ 1 (single sessions): **${singleSessionClients}**`);
}
 
return lines.join("\n");

Link to original

Clients for Case Study

Clients for Case Study

// Retrieve date range and minimum hours
const startDate = new Date(dv.current()["Start Date"]);
const endDate   = new Date(dv.current()["End Date"]);
const minHours  = Number(dv.current().hours) || 0;
 
// Query records for JD Psychotherapy
let pages = dv.pages('"100 CPD Record" and #clienthours')
  .where(p => {
    const d = new Date(p.date);
    return d >= startDate && d <= endDate && p.agency === "JD Psychotherapy";
  })
  .sort(p => p.date, "asc");
 
// Debug: total fetched
dv.paragraph(`Total records fetched (JD Psychotherapy): ${pages.length}`);
 
if (!pages.length) {
  dv.paragraph("No records found for JD Psychotherapy in the specified date range.");
} else {
  // Group by client
  const grouped = {};
  pages.forEach(p => {
    const client = p.clientref || "Not stated";
    if (!grouped[client]) {
      grouped[client] = { clientref: client, name: "", latestNameDate: new Date(0), total: 0, recCount: 0, inCount: 0 };
    }
    const g = grouped[client];
    const recordDate = new Date(p.date);
    // Match the supervisor reports: use the most recently recorded non-empty name.
    if (p.name && !isNaN(recordDate) && recordDate > g.latestNameDate) {
      g.name = String(p.name).trim();
      g.latestNameDate = recordDate;
    }
    const h = Number(p.hours) || 0;
    // Short contacts still contribute their metadata and medium to the report,
    // but only sessions of at least one hour contribute to case-study hours.
    if (h >= 1) g.total += h;
    g.recCount++;
 
    // Normalize medium to array
    const mediums = Array.isArray(p.medium) ? p.medium : p.medium ? [p.medium] : [];
    if (mediums.some(m => String(m).toLowerCase() === "in-person")) {
      g.inCount++;
    }
  });
 
  // Filter: ≥50% in-person and total hours ≥ minHours
  const filtered = Object.values(grouped).filter(g => 
    g.inCount / g.recCount >= 0.5 && g.total >= minHours
  );
 
  // Build table rows
  const rows = [];
  let agencySubtotal = 0;
  filtered.sort((a, b) => a.clientref.localeCompare(b.clientref)).forEach(g => {
    rows.push([
      `**JD Psychotherapy - ${g.clientref}**`,
      g.name,
      `<div style='text-align: right;'>${g.total.toFixed(2)}</div>`
    ]);
    agencySubtotal += g.total;
  });
 
  // Subtotal and Grand Total
  rows.push(["---", "", ""]);
  rows.push([
    `**JD Psychotherapy Subtotal**`,
    "",
    `<div style='text-align: right;'>${agencySubtotal.toFixed(2)}</div>`
  ]);
  rows.push(["---", "", ""]);
  rows.push([
    `**Grand Total**`,
    "",
    `<div style='text-align: right;'>${agencySubtotal.toFixed(2)}</div>`
  ]);
 
  // Render table
  dv.table(
    ["Group", "Name", `<div style='text-align: right;'>Total Hours</div>`],
    rows
  );
 
  // Summary
  dv.paragraph(`Total unique clients with ≥ ${minHours} hours and ≥ 50% in-person: **${filtered.length}**`);
}

Link to original

Error Checking

table medium
from "/"
where (!medium or medium = "") 
  and contains(tags, "clienthours")
  and !contains(file.path, "meta/")

table agency
from "/"
where (!agency or agency = "") and contains(tags, "clienthours") and !contains(file.path, "meta/")

Supervision

Supervision by Body

Supervision Hours by Body with Percentages

const startDate = new Date(`${dv.current()["Start Date"]}`);
const endDate = new Date(`${dv.current()["End Date"]}`);
 
const MIN_SESSION_HOURS = 0.5; // 30 minutes
 
// Fetch pages with the 'clienthours' tag and #supervision
let pages = dv.pages('"100 CPD Record" and #supervision')
  .where(p => {
    const fileDate = new Date(p.date);
    const hrs = Number(p.hours) || 0;
    return fileDate >= startDate && fileDate <= endDate && hrs >= MIN_SESSION_HOURS;
  })
  .sort(p => p.date, 'asc');
 
dv.paragraph(`Total records fetched: ${pages.length}`);
 
if (pages.length === 0) {
  dv.paragraph("No pages found for the specified query.");
} else {
  // Group by Body
  const bodyGroups = {};
  let grandTotalHours = 0;
 
  pages.forEach(p => {
    const body = p.Body || "No Body";
    const hours = p.hours || 0;
 
    if (!bodyGroups[body]) {
      bodyGroups[body] = 0;
    }
 
    bodyGroups[body] += hours;
    grandTotalHours += hours;
  });
 
  // Prepare the table
  const tableData = [];
 
  for (const [body, hours] of Object.entries(bodyGroups).sort()) {
    const percentOfTotal = ((hours / grandTotalHours) * 100).toFixed(2);
    tableData.push([
      `**${body}**`,
      `**${hours}**`,
      `**${percentOfTotal}%**`,
    ]);
  }
 
  // Grand total row
  tableData.push([
    "**Grand Total**",
    `**${grandTotalHours}**`,
    "**100%**",
  ]);
 
  dv.table(["Body", "Total Hours", "% of Total"], tableData);
}

Link to original

Supervision by Medium

Supervision Hours by Medium with Percentages

const startDate = new Date(`${dv.current()["Start Date"]}`);
const endDate = new Date(`${dv.current()["End Date"]}`);
 
const MIN_SESSION_HOURS = 0.5; // 30 minutes
 
// Fetch pages with the 'clienthours' tag and #supervision
let pages = dv.pages('"100 CPD Record" and #supervision')
  .where(p => {
    const fileDate = new Date(p.date);
    const hrs = Number(p.hours) || 0;
    return fileDate >= startDate && fileDate <= endDate && hrs >= MIN_SESSION_HOURS;
  })
  .sort(p => p.date, 'asc');
 
dv.paragraph(`Total records fetched: ${pages.length}`);
 
if (pages.length === 0) {
  dv.paragraph("No pages found for the specified query.");
} else {
  // Group by medium
  const mediumGroups = {};
  let grandTotalHours = 0;
 
  pages.forEach(p => {
    const medium = p.medium || "No medium";
    const hours = p.hours || 0;
 
    if (!mediumGroups[medium]) {
      mediumGroups[medium] = 0;
    }
 
    mediumGroups[medium] += hours;
    grandTotalHours += hours;
  });
 
  // Prepare the table
  const tableData = [];
 
  for (const [medium, hours] of Object.entries(mediumGroups).sort()) {
    const percentOfTotal = ((hours / grandTotalHours) * 100).toFixed(2);
    tableData.push([
      `**${medium}**`,
      `**${hours}**`,
      `**${percentOfTotal}%**`,
    ]);
  }
 
  // Grand total row
  tableData.push([
    "**Grand Total**",
    `**${grandTotalHours}**`,
    "**100%**",
  ]);
 
  dv.table(["Medium", "Total Hours", "% of Total"], tableData);
}

Link to original

Supervision by Supervisor

Supervision Hours by Supervisor with Percentages

const startDate = new Date(`${dv.current()["Start Date"]}`);
const endDate = new Date(`${dv.current()["End Date"]}`);
 
const MIN_SESSION_HOURS = 0.5; // 30 minutes
 
// Fetch pages with the 'clienthours' tag and #supervision
let pages = dv.pages('"100 CPD Record" and #supervision')
  .where(p => {
    const fileDate = new Date(p.date);
    const hrs = Number(p.hours) || 0;
    return fileDate >= startDate && fileDate <= endDate && hrs >= MIN_SESSION_HOURS;
  })
  .sort(p => p.date, 'asc');
 
dv.paragraph(`Total records fetched: ${pages.length}`);
 
if (pages.length === 0) {
  dv.paragraph("No pages found for the specified query.");
} else {
  // Group by 'who' first, then within each group by 'Body' and 'medium'
  const whoGroups = {};
  let grandTotalHours = 0;
 
  pages.forEach(p => {
    const who = p.who || "Not stated";
    const body = p.Body || "No Body";
    const medium = p.medium || "No medium";
    const hours = p.hours || 0;
 
    if (!whoGroups[who]) {
      whoGroups[who] = {
        combinations: {},
        totalHours: 0,
      };
    }
 
    const comboKey = `${body} - ${medium}`;
 
    if (!whoGroups[who].combinations[comboKey]) {
      whoGroups[who].combinations[comboKey] = 0;
    }
 
    whoGroups[who].combinations[comboKey] += hours;
    whoGroups[who].totalHours += hours;
    grandTotalHours += hours;
  });
 
  // Display table for each 'who'
  for (const [who, data] of Object.entries(whoGroups).sort()) {
    const whoPercentage = ((data.totalHours / grandTotalHours) * 100).toFixed(2);
    dv.header(3, `Supervision With: ${who} (${whoPercentage}%)`);
 
    const tableData = [];
 
    for (const [combo, hours] of Object.entries(data.combinations).sort()) {
      const percentOfWho = ((hours / data.totalHours) * 100).toFixed(2);
      const percentOfTotal = ((hours / grandTotalHours) * 100).toFixed(2);
      tableData.push([
        `**${combo}**`,
        `**${hours}**`,
        `**${percentOfWho}%**`,
        `**${percentOfTotal}%**`,
      ]);
    }
 
    // Subtotal for this 'who'
    tableData.push([
      "**Subtotal**",
      `**${data.totalHours}**`,
      "**100%**",
      `**${whoPercentage}%**`,
    ]);
 
    dv.table(
      ["Body - Medium", "Total Hours", "% of Who", "% of Total"],
      tableData
    );
  }
 
  // Grand total
  dv.paragraph(`**Grand Total Hours Across All Supervision: ${grandTotalHours}**`);
}

TABLE
  file.link AS "Record",
  choice(length(default(medium, "")) = 0, "⛔ missing", medium) AS "Medium",
  choice(length(default(who, "")) = 0, "⛔ missing", supervisor) AS "Supervisor",
  choice(length(default(body, "")) = 0, "⛔ missing", body) AS "Body"
FROM "100 CPD Record/Supervision"
WHERE
  contains(file.tags, "#supervision")
  AND (
    length(default(medium, "")) = 0
    OR length(default(who, "")) = 0
    OR length(default(body, "")) = 0
  )
SORT file.name ASC

Link to original

Supervision Since 3 Sundays ago

Supervision Hours since Sunday 3 weeks ago

// Calculate the Sunday preceding today (even if today is Sunday)
let today = new Date();
let daysSinceSunday = today.getDay();
let offset = daysSinceSunday === 0 ? 28 : daysSinceSunday + 21;
let startDate = new Date(today);
startDate.setDate(today.getDate() - offset);
startDate.setHours(0, 0, 0, 0);
 
// Define endDate as today
let endDate = new Date();
endDate.setHours(23, 59, 59, 999);
 
// Helper to format dates as YYYY-MM-DD
function formatDate(date) {
    const dt = new Date(date);
    const year = dt.getFullYear();
    const month = String(dt.getMonth() + 1).padStart(2, '0');
    const day = String(dt.getDate()).padStart(2, '0');
    return `${year}-${month}-${day}`;
}
 
// Format hours with simulated decimal alignment using monospace font
function formatHours(hours) {
    return `\`${(hours ?? 0).toFixed(2).padStart(6, ' ')}\``;
}
 
// Fetch pages and filter by date
let pages = dv.pages('"100 CPD Record" and #supervision')
    .where(p => {
        const dates = Array.isArray(p.date) ? p.date : [p.date];
        return dates.some(d => {
            const dt = new Date(d);
            return dt >= startDate && dt <= endDate;
        });
    })
    .sort(p => {
        const d = Array.isArray(p.date) ? p.date[0] : p.date;
        return d;
    }, 'asc');
 
// Debug
dv.paragraph(`Start Date: ${startDate.toDateString()} | End Date: ${endDate.toDateString()}`);
dv.paragraph(`Total pages fetched: ${pages.length}`);
 
if (pages.length === 0) {
    dv.paragraph("No supervision entries found for the specified query.");
} else {
    const groupedData = {};
    const bodyTotals = {};
    let grandTotal = 0;
 
    pages.forEach(p => {
        const body = p.body || "No Body";
        const who = p.who || "No Who";
        const key = `${body} - ${who}`;
        const hours = p.hours || 0;
        const dates = Array.isArray(p.date) ? p.date : [p.date];
        const medium = Array.isArray(p.medium)
            ? p.medium.join(", ")
            : (p.medium ?? "—");
 
        if (!groupedData[key]) {
            groupedData[key] = {
                body,
                who,
                totalHours: 0,
                entries: []
            };
        }
 
        dates.forEach(d => {
            const dt = new Date(d);
            if (dt >= startDate && dt <= endDate) {
                groupedData[key].entries.push({
                    file: p.file.link,
                    date: formatDate(dt),
                    medium,
                    hours: formatHours(hours)
                });
                groupedData[key].totalHours += hours;
 
                if (!bodyTotals[body]) bodyTotals[body] = 0;
                bodyTotals[body] += hours;
                grandTotal += hours;
            }
        });
    });
 
    const tableData = [];
    const bodyGroups = {};
 
    for (const [key, group] of Object.entries(groupedData)) {
        group.entries.sort((a, b) => new Date(a.date) - new Date(b.date));
        const body = group.body;
 
        if (!bodyGroups[body]) bodyGroups[body] = [];
 
        group.entries.forEach(entry => {
            bodyGroups[body].push([entry.file, entry.date, entry.medium, entry.hours]);
        });
 
        // summary row per who
        bodyGroups[body].push([
            `**${group.body} - ${group.who}**`,
            "",
            "",
            `\`${group.totalHours.toFixed(2).padStart(6, ' ')}\``
        ]);
    }
 
    for (const [body, rows] of Object.entries(bodyGroups)) {
        tableData.push([`**${body}**`, "", "", ""]);
        tableData.push(["----", "----", "----", "----"]);
        tableData.push(...rows);
        tableData.push(["----", "----", "----", "----"]);
        tableData.push([
            `**${body} Subtotal**`,
            "",
            "",
            `**\`${bodyTotals[body].toFixed(2).padStart(6, ' ')}\`**`
        ]);
        tableData.push(["====", "====", "====", "===="]);
    }
 
    // grand total
    tableData.push([
        "**Grand Total**",
        "",
        "",
        `**\`${grandTotal.toFixed(2).padStart(6, ' ')}\`**`
    ]);
 
    dv.table(["File", "Date", "Medium", "Hours"], tableData);
}
 

Link to original

Supervision Reports

Report for supervision - Julia

const currentMonthYear = window.moment().format("MMMM YYYY");
dv.header(1, currentMonthYear);

const lastSupervision = dv.current()?.["last supervision"];
 
if (lastSupervision) {
  const date = new Date(lastSupervision);
  const formatted = date.toLocaleDateString("en-GB", {
    day: "2-digit",
    month: "short",
    year: "numeric"
  });
  dv.paragraph("**Previous supervision:** " + formatted);
} else {
  dv.paragraph("⚠️ No 'last supervision' date found in this note.");
}

Report instructions

Adjust widths in supervision-report.css:

• For client report: edit col:nth-child(N)
• For issues report: edit col:nth-child(1)
• To find the location of the css file, go to Settings/Appearance/CSS Snippets
• File/Export to PDF… save in the Downloads folder

Current

// Supervision client report (scoped table class added at the end)
// Now includes "S" column for allocated-supervisor abbreviation.
// Shows Fq as "old -> new" if frequency has changed since last supervision.
// Shows Name as "old -> new" if name has changed since last supervision.
// Adds "I/O" column: 'I' if majority in-person; 'O' if majority online/remote.
// Columns: [S, Client Ref, Start Date, Name, Age, Gen, I/O, Fq, Total Hours, Issues]
 
// ── Inputs from this note ────────────────────────────────────────────────────
const supervisorRaw = dv.current()?.supervisor;
const lastSupervision = dv.current()?.["last supervision"];
 
if (!supervisorRaw || !lastSupervision) {
  dv.paragraph("⚠️ Missing 'supervisor' or 'last supervision' field in this note.");
} else {
  const supervisionDate = new Date(lastSupervision);
 
  // ── Helpers ───────────────────────────────────────────────────────────────
  const toArray = v => Array.isArray(v) ? v : (v == null ? [] : String(v).split(/[, \s]+/).filter(Boolean));
  const stripHash = t => String(t ?? "").replace(/^#/, "");
  const norm = t => stripHash(String(t ?? "")).trim().replace(/\s+/g, " ").toLowerCase();
 
  function tagSetOf(p) {
    const fm = toArray(p.tags ?? p.tag);
    const inline = (p.file?.tags ?? []).map(stripHash);
    return new Set([...fm, ...inline].map(norm));
  }
 
  const supervisorTag = norm(Array.isArray(supervisorRaw) ? supervisorRaw[0] : supervisorRaw);
 
  const getDates = raw => {
    const arr = Array.isArray(raw) ? raw : [raw];
    return arr
      .map(x => (x instanceof Date ? x : new Date(x)))
      .filter(d => d instanceof Date && !isNaN(d));
  };
 
  const minDate = raw => {
    const ds = getDates(raw);
    return ds.length ? new Date(Math.min(...ds.map(d => d.getTime()))) : null;
  };
 
  const maxDate = raw => {
    const ds = getDates(raw);
    return ds.length ? new Date(Math.max(...ds.map(d => d.getTime()))) : null;
  };
 
  const fmtDate = d =>
    (d instanceof Date && !isNaN(d))
      ? dv.luxon.DateTime.fromJSDate(d).toFormat("yyyy-LL-dd")
      : "";
 
  const coerceHours = h => {
    if (Array.isArray(h)) return Array.from(h).map(coerceHours).reduce((a,b)=>a+b, 0);
    if (typeof h === "number") return isFinite(h) ? h : 0;
    if (h == null) return 0;
    let txt = String(h).trim();
    txt = txt.replace("½","0.5").replace("¼","0.25").replace("¾","0.75");
    txt = txt.replace(/(\d+)\s*\/\s*(\d+)/, (_,a,b)=> (parseFloat(a)/parseFloat(b)).toString());
    const m = txt.match(/-?\d+(\.\d+)?/);
    const n = m ? parseFloat(m[0]) : NaN;
    return isNaN(n) ? 0 : n;
  };
 
  const isDuration = v => v && typeof v === "object" && dv?.luxon?.Duration?.isDuration?.(v);
 
  const formatDurationShort = dur => {
    const o = dur.shiftTo("weeks", "days", "hours", "minutes");
    const w = Math.floor(o.weeks || 0);
    const d = Math.floor(o.days || 0);
    const h = Math.floor(o.hours || 0);
    const m = Math.floor(o.minutes || 0);
    const parts = [];
    if (w) parts.push(`${w}w`);
    if (d) parts.push(`${d}d`);
    if (h) parts.push(`${h}h`);
    if (!parts.length && m) parts.push(`${m}m`);
    return parts.join(" ") || "0m";
  };
 
  const cleanFreqVal = v => {
    if (isDuration(v)) return formatDurationShort(v);
    if (typeof v === "string") {
      const isoW = v.match(/^P(\d+)W$/i);
      if (isoW) return `${isoW[1]}w`;
      return v;
    }
    if (typeof v === "number") return `${v}`;
    return String(v ?? "");
  };
 
  const coerceFrequency = f =>
    Array.isArray(f) ? f.map(cleanFreqVal).filter(Boolean).join(", ") : cleanFreqVal(f);
 
  const abbrevSupervisor = val => {
    const s = String(val ?? "").trim();
    if (!s) return "";
    const m = s.match(/^([A-Za-z]).*?-([A-Za-z])/);
    if (m) return (m[1] + m[2]).toUpperCase();
    return s[0]?.toUpperCase() ?? "";
  };
 
  const ONLINE_KEYS = [
    "online","writeupp-online","zoom","teams","microsoft teams","ms teams","google meet","meet",
    "skype","video","videocall","video call","telehealth","remote","virtual",
    "phone","telephone","call","whatsapp call","signal call","facetime"
  ];
 
  const INPERSON_KEYS = [
    "in person","in-person","in person.","f2f","face-to-face","face to face",
    "room","in the room","clinic","in clinic","office","practice"
  ];
 
  const hitsAny = (txt, keys) => keys.some(k => txt.includes(k));
 
  const classifyMedium = raw => {
    const tokens = toArray(raw).map(norm).filter(Boolean);
    if (!tokens.length) return null;
    const anyOnline = tokens.some(t => hitsAny(t, ONLINE_KEYS));
    const anyInPerson = tokens.some(t => hitsAny(t, INPERSON_KEYS));
    if (anyOnline && !anyInPerson) return "online";
    if (anyInPerson && !anyOnline) return "inperson";
    if (anyOnline && anyInPerson) return "online";
    return null;
  };
 
  const rows = Array.from(
    dv.pages('"100 CPD Record"')
      .where(p => p.clientref && tagSetOf(p).has("clienthours"))
      .groupBy(p => p.clientref),
    group => {
      const records = Array.from(group.rows ?? []);
 
      const validRecords = records
        .map(r => ({ r, firstDate: minDate(r.date), lastDate: maxDate(r.date) }))
        .filter(x => x.firstDate && x.lastDate);
 
      if (validRecords.length === 0) return null;
 
      const hasSupervisorTag = records.some(r => tagSetOf(r).has(supervisorTag));
      if (!hasSupervisorTag) return null;
 
      const isTerminated = records.some(r => r.terminate === true);
      const hasRecent = validRecords.some(x => x.lastDate > supervisionDate);
      if (isTerminated && !hasRecent) return null;
 
      const recentlyTerminated = isTerminated && hasRecent;
 
      const sorted = Array.from(validRecords).sort((a, b) => a.firstDate - b.firstDate);
 
      const first = sorted[0];
      const clientref = group.key + (recentlyTerminated ? " *" : "");
      const earliestDate = first.firstDate;
 
      let name = "", nameBefore = "", age = "", gender = "", issues = "";
      let frequency = "", frequencyBefore = "";
      let latestName = 0, latestNameBefore = 0, latestAge = 0, latestGender = 0, latestIssues = 0, latestFrequency = 0, latestBefore = 0;
      let allocSupRaw = "", latestAlloc = 0;
 
      let nOnline = 0, nInPerson = 0;
      let latestMediumClass = null, latestMediumDate = 0;
 
      for (let { r, lastDate } of validRecords) {
        const d = +lastDate;
 
        if (r.name) {
          const val = String(r.name).trim();
          if (d > latestName) {
            name = val;
            latestName = d;
          }
          if (d <= +supervisionDate && d > latestNameBefore) {
            nameBefore = val;
            latestNameBefore = d;
          }
        }
 
        if (r.age && d > latestAge) {
          age = r.age;
          latestAge = d;
        }
 
        if (r.gender && d > latestGender) {
          gender = r.gender;
          latestGender = d;
        }
 
        if (r.issues && d > latestIssues) {
          issues = Array.isArray(r.issues) ? r.issues.filter(Boolean).join(", ") : r.issues;
          latestIssues = d;
        }
 
        if (r.frequency != null) {
          const val = coerceFrequency(r.frequency);
          if (d > latestFrequency) {
            frequency = val ?? "";
            latestFrequency = d;
          }
          if (d <= +supervisionDate && d > latestBefore) {
            frequencyBefore = val ?? "";
            latestBefore = d;
          }
        }
 
        const asv = r["allocated-supervisor"];
        if (asv != null && d > latestAlloc) {
          allocSupRaw = Array.isArray(asv) ? (asv.length ? asv[asv.length - 1] : "") : asv;
          latestAlloc = d;
        }
 
        const cls = classifyMedium(r.medium);
        if (cls === "online") nOnline++;
        else if (cls === "inperson") nInPerson++;
 
        if (cls && d > latestMediumDate) {
          latestMediumClass = cls;
          latestMediumDate = d;
        }
      }
 
      const sAbbrev = abbrevSupervisor(allocSupRaw);
 
      const nameDisplay = (name && nameBefore && norm(name) !== norm(nameBefore))
        ? `${nameBefore} -> ${name}`
        : name || "";
 
      const fqDisplay = (frequency && frequencyBefore && norm(frequency) !== norm(frequencyBefore))
        ? `${frequencyBefore} -> ${frequency}`
        : frequency || "";
 
      const countedRecords = validRecords.filter(({ r }) => coerceHours(r.hours) > 0.5);
      
      const totalHours = Array.from(countedRecords, ({ r }) => coerceHours(r.hours))
  .reduce((a,b) => a + b, 0);
 
      let io = "";
      if (nInPerson > nOnline) io = "I";
      else if (nOnline > nInPerson) io = "O";
      else if (latestMediumClass) io = (latestMediumClass === "online" ? "O" : "I");
 
      return [
        sAbbrev,
        clientref,
        fmtDate(earliestDate),
        nameDisplay,
        age ?? "",
        gender ?? "",
        io,
        fqDisplay,
        +totalHours.toFixed(2),
        issues ?? ""
      ];
    }
  ).filter(Boolean)
    // Current report: supervisor abbreviation (S), then client name.
    .sort((a, b) => String(a[0] ?? "").localeCompare(String(b[0] ?? ""), undefined, { sensitivity: "base" })
      || String(a[3] ?? "").localeCompare(String(b[3] ?? ""), undefined, { sensitivity: "base" }));
 
  dv.table(
    ["S", "Client Ref", "Start Date", "Name", "Age", "Gen", "I/O", "Fq", "Total Hours", "Issues"],
    rows
  );
 
  const _t = dv.container.querySelectorAll("table");
  if (_t.length) _t[_t.length - 1].classList.add("supervision-client-report");
}

^*Finished

// Report: Finished clients seen since last supervision
// Columns: [Client Ref, Name, Last Seen]
// Tagged as "supervision-finished" for CSS styling
 
const lastSupervision = dv.current()?.["last supervision"];
const supervisorRaw   = dv.current()?.supervisor;
 
if (!lastSupervision || !supervisorRaw) {
  dv.paragraph("⚠️ Missing 'last supervision' or 'supervisor' field in this note.");
} else {
  const supervisionDate = new Date(lastSupervision);
 
  // --- Tag normalisation ---------------------------------------------------
  const toArray   = v => Array.isArray(v) ? v : (v == null ? [] : String(v).split(/[, \s]+/).filter(Boolean));
  const stripHash = t => String(t ?? "").replace(/^#/, "");
  const norm      = t => stripHash(String(t ?? "")).toLowerCase();
 
  function tagSetOf(p) {
    const fm     = toArray(p.tags ?? p.tag);
    const inline = (p.file?.tags ?? []).map(stripHash);
    return new Set([...fm, ...inline].map(norm));
  }
 
  const supervisorTag = norm(Array.isArray(supervisorRaw) ? supervisorRaw[0] : supervisorRaw);
 
  // --- Build results -------------------------------------------------------
  const results = Array.from(
    dv.pages('"100 CPD Record"')
      .where(p => p.clientref && tagSetOf(p).has("clienthours"))
      .groupBy(p => p.clientref),
    group => {
      const records = Array.from(group.rows ?? []).filter(p => p.date);
      if (records.length === 0) return null;
 
      // must include supervisor tag OR 'finished'
      const hasRequiredTag = records.some(r => {
        const tags = tagSetOf(r);
        return tags.has(supervisorTag) || tags.has("finished");
      });
      if (!hasRequiredTag) return null;
 
      // must have at least one terminated record
      const hasTerminated = records.some(r => r.terminate === true);
 
      // must have at least one record after supervision
      const hasRecent = records.some(r => {
        const dates = toArray(r.date).map(d => new Date(d)).filter(d => d instanceof Date && !isNaN(d));
        return dates.some(d => d > supervisionDate);
      });
 
      if (!(hasTerminated && hasRecent)) return null;
 
      // find most recent date overall
      let mostRecentDate = new Date(0);
      let lastSeenDate   = null;
      for (let r of records) {
        const dates = toArray(r.date).map(d => new Date(d)).filter(d => !isNaN(d));
        for (let d of dates) {
          if (d > mostRecentDate) {
            mostRecentDate = d;
            lastSeenDate   = d;
          }
        }
      }
 
      // most recent record with a name
      let mostRecentNamed = null;
      let latestNamedDate = new Date(0);
      for (let r of records) {
        if (r.name) {
          const dates = toArray(r.date).map(d => new Date(d)).filter(d => !isNaN(d));
          for (let d of dates) {
            if (d > latestNamedDate) {
              latestNamedDate = d;
              mostRecentNamed = r;
            }
          }
        }
      }
 
      const clientref = group.key;
      const name      = mostRecentNamed?.name ?? "";
      const lastSeen  = lastSeenDate ? dv.luxon.DateTime.fromJSDate(lastSeenDate).toFormat("yyyy-LL-dd") : "";
 
      return [clientref, name, lastSeen];
    }
  ).filter(Boolean)
    .sort((a, b) => String(a[1] ?? "").localeCompare(String(b[1] ?? ""), undefined, { sensitivity: "base" })
      || String(a[0] ?? "").localeCompare(String(b[0] ?? ""), undefined, { sensitivity: "base" }));
 
  // --- Render --------------------------------------------------------------
  if (results.length === 0) {
    dv.paragraph("none");
  } else {
    dv.table(["Client Ref", "Name", "Last Seen"], results);
 
    // Tag last table with class + colgroup
    const tables = dv.container.querySelectorAll("table");
    const t = tables[tables.length - 1];
    if (t) {
      t.classList.add("supervision-finished");
      if (!t.querySelector("colgroup")) {
        const cg = document.createElement("colgroup");
        for (let i = 0; i < 3; i++) cg.appendChild(document.createElement("col"));
        t.insertBefore(cg, t.firstChild);
      }
      t.style.tableLayout = "fixed";
      t.style.width = "100%";
    }
  }
}

New

// Report: New clients since last supervision
// Columns: [Client Ref, Name, Issues, Referred By]
// Tagged as "supervision-new" for CSS styling
 
const lastSupervision = dv.current()?.["last supervision"];
const supervisorRaw   = dv.current()?.supervisor;
 
if (!lastSupervision || !supervisorRaw) {
  dv.paragraph("⚠️ Missing 'last supervision' or 'supervisor' field in this note.");
} else {
  const supervisionDate = new Date(lastSupervision);
 
  // --- Tag normalisation ---------------------------------------------------
  const toArray   = v => Array.isArray(v) ? v : (v == null ? [] : String(v).split(/[, \s]+/).filter(Boolean));
  const stripHash = t => String(t ?? "").replace(/^#/, "");
  const norm      = t => stripHash(String(t ?? "")).toLowerCase();
 
  function tagSetOf(p) {
    const fm     = toArray(p.tags ?? p.tag);
    const inline = (p.file?.tags ?? []).map(stripHash);
    return new Set([...fm, ...inline].map(norm));
  }
 
  const supervisorTag = norm(Array.isArray(supervisorRaw) ? supervisorRaw[0] : supervisorRaw);
 
  // --- Build results -------------------------------------------------------
  const results = Array.from(
    dv.pages('"100 CPD Record"')
      .where(p => p.clientref && tagSetOf(p).has("clienthours"))
      .groupBy(p => p.clientref),
    group => {
      const records = Array.from(group.rows ?? []).filter(p => p.date);
      if (records.length === 0) return null;
 
      // must have supervisor tag OR 'new'
      const hasRequiredTag = records.some(r => {
        const tags = tagSetOf(r);
        return tags.has(supervisorTag) || tags.has("new");
      });
      if (!hasRequiredTag) return null;
 
      // earliest record date
      let earliestDate = new Date(8640000000000000);
      for (let r of records) {
        const dates = toArray(r.date).map(d => new Date(d)).filter(d => !isNaN(d));
        for (let d of dates) {
          if (d < earliestDate) earliestDate = d;
        }
      }
 
      // only include clients whose earliest record is on/after supervision date
      if (earliestDate < supervisionDate) return null;
 
      // most recent values for fields
      let name = "", issues = "", referred = "";
      let latestNameDate = new Date(0), latestIssuesDate = new Date(0), latestRefDate = new Date(0);
 
      for (let r of records) {
        const dates = toArray(r.date).map(d => new Date(d)).filter(d => !isNaN(d));
        const parsed = dates.length ? dates[0] : null;
        if (!parsed) continue;
 
        if (r.name && parsed > latestNameDate) {
          name = r.name;
          latestNameDate = parsed;
        }
        if (r.issues && parsed > latestIssuesDate) {
          issues = r.issues;
          latestIssuesDate = parsed;
        }
        if (r["referred by"] && parsed > latestRefDate) {
          referred = r["referred by"];
          latestRefDate = parsed;
        }
      }
 
      return [group.key, name, issues, referred];
    }
  ).filter(Boolean)
    .sort((a, b) => String(a[1] ?? "").localeCompare(String(b[1] ?? ""), undefined, { sensitivity: "base" })
      || String(a[0] ?? "").localeCompare(String(b[0] ?? ""), undefined, { sensitivity: "base" }));
 
  // --- Render --------------------------------------------------------------
  if (results.length === 0) {
    dv.paragraph("none");
  } else {
    dv.table(["Client Ref", "Name", "Issues", "Referred By"], results);
 
    // Tag last table with class + colgroup
    const tables = dv.container.querySelectorAll("table");
    const t = tables[tables.length - 1];
    if (t) {
      t.classList.add("supervision-new");
      if (!t.querySelector("colgroup")) {
        const cg = document.createElement("colgroup");
        for (let i = 0; i < 4; i++) cg.appendChild(document.createElement("col"));
        t.insertBefore(cg, t.firstChild);
      }
      t.style.tableLayout = "fixed";
      t.style.width = "100%";
    }
  }
}

Ongoing

// Report: Latest "Current Issues" per client (widths controlled via CSS)
// Columns: [Client Ref, Name, Current Issues]
 
const lastSupervision = dv.current()?.["last supervision"];
const supervisorRaw   = dv.current()?.supervisor; // can be "#name", "name", array, etc.
 
if (!lastSupervision || !supervisorRaw) {
  dv.paragraph("⚠️ Missing 'last supervision' or 'supervisor' field in this note.");
} else {
  const supervisionDate = new Date(lastSupervision);
 
  // --- Tag normalisation helpers -------------------------------------------
  const toArray   = v => Array.isArray(v) ? v : (v == null ? [] : String(v).split(/[, \s]+/).filter(Boolean));
  const stripHash = t => String(t ?? "").replace(/^#/, "");
  const norm      = t => stripHash(String(t ?? "")).toLowerCase();
 
  function tagSetOf(p) {
    const fm     = toArray(p.tags ?? p.tag);
    const inline = (p.file?.tags ?? []).map(stripHash);
    return Array.from(new Set([...fm, ...inline].map(norm)));
  }
 
  const supervisorTag = norm(Array.isArray(supervisorRaw) ? supervisorRaw[0] : supervisorRaw);
 
  // --- Date helpers ---------------------------------------------------------
  function getDates(raw) {
    const arr = Array.isArray(raw) ? raw : [raw];
    return arr.map(x => new Date(x)).filter(d => !isNaN(d));
  }
  function latestDate(raw) {
    const ds = getDates(raw);
    return ds.length ? new Date(Math.max(...ds.map(d => d.getTime()))) : null;
  }
 
  // --- Build table rows -----------------------------------------------------
  const rows = Array.from(
    dv.pages('"100 CPD Record"')
      .where(p => tagSetOf(p).includes("clienthours"))
      .groupBy(p => p.clientref),
    group => {
      const records = Array.from(group.rows ?? []).filter(p => p.date);
      if (records.length === 0) return null;
 
      const hasSupervisorTagged = records.some(r => tagSetOf(r).includes(supervisorTag));
      if (!hasSupervisorTagged) return null;
 
      const isTerminated = records.some(r => r.terminate === true);
      const latestOverallDate = records.reduce((latest, r) => {
        const d = latestDate(r.date);
        return d && d > latest ? d : latest;
      }, new Date(0));
      if (isTerminated && latestOverallDate < supervisionDate) return null;
 
      // Most recent name
      let name = "";
      let latestNameDate = new Date(0);
      for (let r of records) {
        if (r.name) {
          const d = latestDate(r.date);
          if (d && d > latestNameDate) {
            latestNameDate = d;
            name = r.name;
          }
        }
      }
 
      // Current issues: prefer latest after supervision; otherwise latest overall
      const issueRecords = records
        .filter(r => r["current issues"])
        .map(r => {
          const d = latestDate(r.date);
          const val = Array.isArray(r["current issues"])
            ? r["current issues"][r["current issues"].length - 1]
            : r["current issues"];
          return { date: d, value: val };
        })
        .filter(r => r.date);
 
      if (issueRecords.length === 0) return [group.key, name, ""];
 
      const postSupervision = issueRecords
        .filter(r => r.date > supervisionDate)
        .sort((a, b) => b.date - a.date);
 
      const currentIssues = postSupervision.length > 0
        ? postSupervision[0].value
        : issueRecords.sort((a, b) => b.date - a.date)[0].value;
 
      return [group.key, name, currentIssues];
    }
  ).filter(Boolean)
    .sort((a, b) => String(a[1] ?? "").localeCompare(String(b[1] ?? ""), undefined, { sensitivity: "base" })
      || String(a[0] ?? "").localeCompare(String(b[0] ?? ""), undefined, { sensitivity: "base" }));
 
  // --- Render table ---------------------------------------------------------
  dv.table(["Client Ref", "Name", "Current Issues"], rows);
 
  // Tag the just-rendered table and inject a <colgroup> with 3 <col>s (no widths)
  const tables = dv.container.querySelectorAll("table");
  const t = tables[tables.length - 1];
  if (t) {
    t.classList.add("supervision-issues-latest");  // unique class for CSS targeting
    if (!t.querySelector("colgroup")) {
      const cg = document.createElement("colgroup");
      for (let i = 0; i < 3; i++) cg.appendChild(document.createElement("col"));
      t.insertBefore(cg, t.firstChild);
    }
    // Optional, but helps PDF export respect widths
    t.style.tableLayout = "fixed";
    t.style.width = "100%";
  }
}

### Sessions since last supervision

// Report: All current issues since last supervision (per client)
// This version creates <col> elements but leaves widths to CSS only.
 
const lastSupervision = dv.current()?.["last supervision"];
const supervisorRaw   = dv.current()?.supervisor;
 
if (!lastSupervision || !supervisorRaw) {
  dv.paragraph("⚠️ Missing 'last supervision' or 'supervisor' field in this note.");
} else {
  const supervisionDate = new Date(lastSupervision);
 
  // ── Tag normalisation ──────────────────────────────────────────────────
  const toArray   = v => Array.isArray(v) ? v : (v == null ? [] : String(v).split(/[, \s]+/).filter(Boolean));
  const stripHash = t => String(t ?? "").replace(/^#/, "");
  const norm      = t => stripHash(String(t ?? "")).toLowerCase();
 
  function tagSetOf(p) {
    const fm     = toArray(p.tags ?? p.tag);
    const inline = (p.file?.tags ?? []).map(stripHash);
    return Array.from(new Set([...fm, ...inline].map(norm)));
  }
 
  const supervisorTag = norm(Array.isArray(supervisorRaw) ? supervisorRaw[0] : supervisorRaw);
 
  // ── Date helpers ───────────────────────────────────────────────────────
  function getDates(raw) {
    const arr = Array.isArray(raw) ? raw : [raw];
    return arr.map(x => new Date(x)).filter(d => d instanceof Date && !isNaN(d));
  }
 
  function latestDate(raw) {
    const ds = getDates(raw);
    return ds.length ? new Date(Math.max(...ds.map(d => d.getTime()))) : null;
  }
 
  const fmt = d => {
    if (!(d instanceof Date) || isNaN(+d)) return "";
    const y  = d.getFullYear();
    const m  = String(d.getMonth() + 1).padStart(2, "0");
    const da = String(d.getDate()).padStart(2, "0");
    return `${y}-${m}-${da}`;
  };
 
  // ── Build per-client groups ────────────────────────────────────────────
  const clientGroups = Array.from(
    dv.pages('"100 CPD Record"')
      .where(p => tagSetOf(p).includes("clienthours"))
      .groupBy(p => p.clientref),
    group => {
      const records = Array.from(group.rows ?? []).filter(p => p.date);
      if (records.length === 0) return null;
 
      // Supervisor filter
      const hasSupervisorTagged = records.some(r => tagSetOf(r).includes(supervisorTag));
      if (!hasSupervisorTagged) return null;
 
      // Termination logic
      const isTerminated = records.some(r => r.terminate === true);
      const latestOverallDate = records.reduce((latest, r) => {
        const d = latestDate(r.date);
        return d && d > latest ? d : latest;
      }, new Date(0));
      if (isTerminated && latestOverallDate < supervisionDate) return null;
 
      // Most recent name
      let name = "";
      let latestNameDate = new Date(0);
      for (let r of records) {
        if (r.name) {
          const d = latestDate(r.date);
          if (d && d > latestNameDate) {
            latestNameDate = d;
            name = r.name;
          }
        }
      }
 
      // Gather all issues since supervision
      const issueRows = [];
      for (let r of records) {
        if (!r["current issues"]) continue;
        const datesAfter = getDates(r.date).filter(d => d > supervisionDate);
        if (datesAfter.length === 0) continue;
 
        const issueDate = new Date(Math.max(...datesAfter.map(d => d.getTime())));
        const issuesArr = Array.isArray(r["current issues"]) ? r["current issues"] : [r["current issues"]];
        for (const issue of issuesArr) {
          issueRows.push([fmt(issueDate), issue]);
        }
      }
 
      if (issueRows.length === 0) return null;
 
      // Sort issues per client by date (ascending)
      issueRows.sort((a, b) => new Date(a[0]) - new Date(b[0]));
 
      return {
        clientref: group.key,
        name: name,
        issues: issueRows
      };
    }
  ).filter(Boolean)
    .sort((a, b) => String(a.name ?? "").localeCompare(String(b.name ?? ""), undefined, { sensitivity: "base" })
      || String(a.clientref ?? "").localeCompare(String(b.clientref ?? ""), undefined, { sensitivity: "base" }));
 
  // ── Render ─────────────────────────────────────────────────────────────
  dv.paragraph(`Listing all current issues since: **${fmt(supervisionDate)}**`);
 
  if (clientGroups.length === 0) {
    dv.paragraph("No current issues recorded since the last supervision.");
  } else {
    for (const g of clientGroups) {
      dv.header(3, `${g.clientref} — ${g.name}`);
      dv.table(["Issue Date", "Current Issues"], g.issues);
 
      // Tag the most recently rendered table and inject a <colgroup> with 2 <col>s.
      // No inline widths: CSS (supervision-report.css) controls sizing.
      const tables = dv.container.querySelectorAll("table");
      const t = tables[tables.length - 1];
      if (t) {
        t.classList.add("supervision-issues-table");
 
        if (!t.querySelector("colgroup")) {
          const cg = document.createElement("colgroup");
          // Create two columns: date + issues (widths via CSS)
          for (let i = 0; i < 2; i++) {
            cg.appendChild(document.createElement("col"));
          }
          t.insertBefore(cg, t.firstChild);
        }
      }
    }
  }
}

Circular transclusion detected: 100-CPD-Record/Supervision/Data/Ratio-of-Supervision-Hours---Abbrev

Link to original

Client Joining Month

// ─────────────────────────────────────────────────────────────
// First Episodes by Month — DVJS + SVG (namespace-safe)
// Filters: Agency, Year, Start Date, End Date
// Rule: first appointment must be ≥ MIN_FIRST_HOURS
// Chart: stacked bars by calendar year + legend (colour/hatch per year)
// ─────────────────────────────────────────────────────────────
 
const MIN_FIRST_HOURS = 0.5;
 
// Front-matter driven filters (use any of these keys; case-insensitive fallbacks)
const getFM = (k) => dv.current()?.[k];
const FILTER_AGENCY = (getFM("Agency Filter") ?? getFM("agency filter") ?? "").toString().trim();
const FILTER_YEAR   = (getFM("Year Filter")   ?? getFM("year filter")   ?? "").toString().trim();
 
const rawStart = getFM("Start Date") ?? getFM("start date") ?? "";
const rawEnd   = getFM("End Date")   ?? getFM("end date")   ?? "";
 
const monthOrder = ["Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"];
const SVGNS = "http://www.w3.org/2000/svg";
 
// Helpers
const toDate = (v) => {
  // Accepts scalar or array; returns { date, key } or null.
  if (Array.isArray(v)) v = v[0];
  if (!v) return null;
 
  // Preserve date-only YAML/Dataview values as local calendar dates.
  // Avoid Date -> toISOString() for display, because BST can shift midnight
  // back to the previous UTC day.
  if (typeof v?.toFormat === "function") {
    const key = v.toFormat("yyyy-MM-dd");
    return { date: new Date(`${key}T00:00:00`), key };
  }
 
  if (typeof v?.toISODate === "function") {
    const key = v.toISODate();
    return { date: new Date(`${key}T00:00:00`), key };
  }
 
  const match = String(v).match(/^(\d{4})-(\d{2})-(\d{2})/);
  if (match) {
    const key = `${match[1]}-${match[2]}-${match[3]}`;
    return { date: new Date(`${key}T00:00:00`), key };
  }
 
  const d = new Date(v);
  if (Number.isNaN(d.getTime())) return null;
  const yyyy = d.getFullYear();
  const mm = String(d.getMonth() + 1).padStart(2, "0");
  const dd = String(d.getDate()).padStart(2, "0");
  return { date: d, key: `${yyyy}-${mm}-${dd}` };
};
const toNumber = (v) => {
  if (Array.isArray(v)) v = v[0];
  if (v == null || v === "") return null;
  const n = parseFloat(String(v).trim());
  return Number.isNaN(n) ? null : n;
};
const svgEl = (name, attrs = {}) => {
  const el = document.createElementNS(SVGNS, name);
  for (const [k, v] of Object.entries(attrs)) el.setAttribute(k, String(v));
  return el;
};
 
// Parse date-window (inclusive). If only one bound provided, use that side only.
let START_INFO = toDate(rawStart);
let END_INFO   = toDate(rawEnd);
let START_DATE = START_INFO?.date ?? null;
let END_DATE   = END_INFO?.date ?? null;
 
// If start > end, swap (helps avoid accidental inversions)
if (START_DATE && END_DATE && START_DATE > END_DATE) {
  const tmpDate = START_DATE;
  START_DATE = END_DATE;
  END_DATE = tmpDate;
 
  const tmpInfo = START_INFO;
  START_INFO = END_INFO;
  END_INFO = tmpInfo;
}
 
// 1) Load + normalise records from tagged pages
const src = dv.pages("#clienthours").where(p => p.clientref && p.date);
const records = Array.from(src).map(p => {
  const dateInfo = toDate(p.date);
  return {
    clientref: p.clientref,
    agency: (p.agency ?? "").toString(),
    date: dateInfo?.date ?? null,
    dateKey: dateInfo?.key ?? "",
    hours: toNumber(p.hours) ?? 0
  };
}).filter(p => p.date !== null);
 
// 2) Group by client and pick earliest record with hours ≥ MIN_FIRST_HOURS
records.sort((a,b) => a.date - b.date);
const byClient = new Map();
for (const r of records) {
  if (!byClient.has(r.clientref)) byClient.set(r.clientref, []);
  byClient.get(r.clientref).push(r);
}
const firstEpisodes = [];
for (const [clientref, rows] of byClient.entries()) {
  rows.sort((a,b) => a.date - b.date);
  const firstQual = rows.find(rr => (rr.hours ?? 0) >= MIN_FIRST_HOURS);
  if (!firstQual) continue;
  firstEpisodes.push({ clientref, agency: firstQual.agency, date: firstQual.date });
}
 
// 3) Apply filters (agency/year + date window inclusive)
const filtered = firstEpisodes.filter(ep => {
  const agencyOk = !FILTER_AGENCY || ep.agency === FILTER_AGENCY;
  const yearOk   = !FILTER_YEAR   || ep.date.getFullYear().toString() === FILTER_YEAR;
 
  const startOk  = !START_DATE || ep.date >= START_DATE;  // inclusive
  const endOk    = !END_DATE   || ep.date <= END_DATE;    // inclusive
 
  return agencyOk && yearOk && startOk && endOk;
});
 
// 4) Count by month AND year
const yearSet = new Set();
for (const ep of filtered) yearSet.add(ep.date.getFullYear());
const years = Array.from(yearSet).sort((a,b) => a - b); // ascending
 
// countsByMonthYear[month][year] = count
const countsByMonthYear = Object.fromEntries(monthOrder.map(m => [m, Object.create(null)]));
for (const ep of filtered) {
  const m = ep.date.toLocaleString("en-GB", { month: "short" });
  const y = ep.date.getFullYear();
  if (!(m in countsByMonthYear)) continue;
  countsByMonthYear[m][y] = (countsByMonthYear[m][y] ?? 0) + 1;
}
 
// chartData: one entry per month with a year->count map and total
const chartData = monthOrder.map(m => {
  const byY = countsByMonthYear[m];
  const total = years.reduce((acc, y) => acc + (byY[y] ?? 0), 0);
  return { month: m, byYear: byY, total };
});
 
// 5) Summary + table (keep your original table: totals per month)
const bits = [];
if (FILTER_AGENCY) bits.push(`Agency: **${FILTER_AGENCY}**`);
if (FILTER_YEAR)   bits.push(`Year: **${FILTER_YEAR}**`);
bits.push(`Min First Hours: **${MIN_FIRST_HOURS}**`);
if (START_DATE || END_DATE) {
  const fmt = info => info?.key ?? "—";
  bits.push(`Window: **${fmt(START_INFO)} → ${fmt(END_INFO)}**`);
}
dv.paragraph(`First episodes by month ${bits.length ? "(" + bits.join(" · ") + ")" : ""}:`);
dv.table(["Month", "First Episodes"], chartData.map(d => [d.month, d.total]));
 
// 6) SVG bar chart (stacked by year + legend BELOW chart)
// NOTE: this block derives its own year list from chartData to avoid "years is not defined".
 
const container = this.container.createEl("div", { cls: "first-episodes-chart" });
container.style.width = "100%";
container.style.maxWidth = "900px";
 
// Derive years locally from chartData (safe even if you didn't paste earlier code)
const yearsLocal = Array.from(
  new Set(
    chartData.flatMap(d => Object.keys(d.byYear ?? {}).map(k => Number(k))).filter(n => Number.isFinite(n))
  )
).sort((a,b) => a - b);
 
const width = 900;
const legendRowH = 16;
const legendTitleH = 16;
const legendPadding = 10;
const legendH = yearsLocal.length ? (legendTitleH + yearsLocal.length * legendRowH + legendPadding) : 0;
 
// Give the chart more vertical room: base height + legend band
const baseHeight = 320;
const height = baseHeight + legendH;
 
// Margins for the plotting area (legend is outside this)
const margin = { top: 28, right: 20, bottom: 44, left: 44 };
const innerW = width - margin.left - margin.right;
const innerH = baseHeight - margin.top - margin.bottom;
 
const svg = svgEl("svg", { viewBox: `0 0 ${width} ${height}`, preserveAspectRatio: "xMidYMid meet" });
svg.style.width = "100%";
svg.style.height = "auto";
container.appendChild(svg);
 
// ----- Patterns / styles per year -----
const defs = svgEl("defs");
svg.appendChild(defs);
 
const palette = [
  "#4C78A8", "#F58518", "#54A24B", "#E45756", "#72B7B2",
  "#B279A2", "#FF9DA6", "#9D755D", "#BAB0AC"
];
 
const useColour = true;         // set false for hatch-only
const hatchOnlyStroke = "currentColor";
 
function addHatchPattern(id, stroke, angleDeg, spacing, strokeW) {
  const p = svgEl("pattern", {
    id,
    patternUnits: "userSpaceOnUse",
    width: spacing,
    height: spacing,
    patternTransform: `rotate(${angleDeg})`
  });
  p.appendChild(svgEl("line", {
    x1: 0, y1: 0,
    x2: 0, y2: spacing,
    stroke,
    "stroke-width": strokeW,
    "stroke-linecap": "square",
    "stroke-opacity": "0.9"
  }));
  defs.appendChild(p);
}
 
const yearStyle = new Map();
yearsLocal.forEach((yr, idx) => {
  const colour = palette[idx % palette.length];
  const angle = (idx % 4) * 45;
  const spacing = 6 + (idx % 3) * 2;
  const strokeW = 1 + (idx % 2) * 0.5;
  const patId = `fe-hatch-${yr}`;
 
  addHatchPattern(patId, useColour ? colour : hatchOnlyStroke, angle, spacing, strokeW);
 
  yearStyle.set(yr, {
    fill: useColour ? colour : "none",
    fillOpacity: useColour ? 0.35 : 0,
    patternUrl: `url(#${patId})`
  });
});
 
// Scales
const maxY = Math.max(1, ...chartData.map(d => d.total));
const xStep = innerW / chartData.length;
const barW = xStep * 0.7;
const x = i => margin.left + i * xStep + (xStep - barW) / 2;
const y = v => margin.top + innerH - (v / maxY) * innerH;
 
// Axes group
const axisG = svg.appendChild(svgEl("g"));
axisG.appendChild(svgEl("line", {
  x1: margin.left, y1: margin.top, x2: margin.left, y2: margin.top + innerH, stroke: "currentColor"
}));
axisG.appendChild(svgEl("line", {
  x1: margin.left, y1: margin.top + innerH, x2: margin.left + innerW, y2: margin.top + innerH, stroke: "currentColor"
}));
 
// Y ticks
const yTicks = Math.min(6, maxY);
for (let t = 0; t <= yTicks; t++) {
  const val = Math.round((t / yTicks) * maxY);
  const yy = y(val);
  axisG.appendChild(svgEl("line", {
    x1: margin.left, y1: yy, x2: margin.left + innerW, y2: yy,
    stroke: "currentColor", "stroke-opacity": "0.15"
  }));
  const lbl = svgEl("text", { x: margin.left - 8, y: yy + 4, "text-anchor": "end", fill: "currentColor" });
  lbl.style.fontSize = "12px";
  lbl.textContent = String(val);
  axisG.appendChild(lbl);
}
 
// Bars + X labels (STACKED by year)
const barsG = svg.appendChild(svgEl("g"));
 
chartData.forEach((d, i) => {
  const baseX = x(i);
  let cumulative = 0;
 
  yearsLocal.forEach(yr => {
    const c = (d.byYear?.[yr] ?? d.byYear?.[String(yr)] ?? 0);
    if (!c) return;
 
    const y0 = y(cumulative);
    const y1 = y(cumulative + c);
    const segH = Math.max(1, y0 - y1);
 
    const st = yearStyle.get(yr);
 
    // optional tinted base
    if (st.fill !== "none" && st.fillOpacity > 0) {
      barsG.appendChild(svgEl("rect", {
        x: baseX, y: y1, width: barW, height: segH,
        fill: st.fill, "fill-opacity": String(st.fillOpacity), stroke: "none"
      }));
    }
 
    // hatch overlay (always)
    barsG.appendChild(svgEl("rect", {
      x: baseX, y: y1, width: barW, height: segH,
      fill: st.patternUrl, "fill-opacity": "1",
      stroke: "currentColor", "stroke-opacity": "0.15"
    }));
 
    cumulative += c;
  });
 
  if (d.total === 0) {
    barsG.appendChild(svgEl("rect", {
      x: baseX,
      y: margin.top + innerH - 1,
      width: barW,
      height: 1,
      fill: "currentColor",
      "fill-opacity": "0.15"
    }));
  }
 
  const xl = svgEl("text", {
    x: margin.left + i * xStep + xStep / 2,
    y: margin.top + innerH + 18,
    "text-anchor": "middle",
    fill: "currentColor"
  });
  xl.style.fontSize = "12px";
  xl.textContent = d.month;
  barsG.appendChild(xl);
});
 
// Title
const title = svgEl("text", { x: margin.left, y: 18, fill: "currentColor" });
title.style.fontSize = "14px";
title.style.fontWeight = "600";
title.textContent = "First Episodes by Month (stacked by year)";
svg.appendChild(title);
 
// Legend BELOW chart (in reserved band)
if (yearsLocal.length) {
  const legendG = svg.appendChild(svgEl("g"));
  const boxW = 14, boxH = 10;
 
  const legendX = margin.left;
  const legendTop = baseHeight + 14;
 
  const lt = svgEl("text", { x: legendX, y: legendTop, fill: "currentColor" });
  lt.style.fontSize = "12px";
  lt.style.fontWeight = "600";
  lt.textContent = "Year key";
  legendG.appendChild(lt);
 
  yearsLocal.forEach((yr, idx) => {
    const yRow = legendTop + legendTitleH + idx * legendRowH;
    const st = yearStyle.get(yr);
 
    if (st.fill !== "none" && st.fillOpacity > 0) {
      legendG.appendChild(svgEl("rect", {
        x: legendX,
        y: yRow - boxH + 2,
        width: boxW,
        height: boxH,
        fill: st.fill,
        "fill-opacity": String(st.fillOpacity),
        stroke: "none"
      }));
    }
 
    legendG.appendChild(svgEl("rect", {
      x: legendX,
      y: yRow - boxH + 2,
      width: boxW,
      height: boxH,
      fill: st.patternUrl,
      stroke: "currentColor",
      "stroke-opacity": "0.35"
    }));
 
    const t = svgEl("text", { x: legendX + boxW + 8, y: yRow + 2, fill: "currentColor" });
    t.style.fontSize = "12px";
    t.textContent = String(yr);
    legendG.appendChild(t);
  });
}

Link to original

---

creation date:: 2025-04-16.md 13:49

Client Activity

Client Activity this 12 month and previous 12 month

// ─────────────────────────────────────────────────────────────
// Client Hours by Rolling 12-Month Periods — grouped + stacked
// Filters: Agency, Start Date, End Date
// Rules:
//   - include only sessions with hours > 0.5
// Graph:
//   - Period 1 = last 12 months to today
//   - Period 2 = 12 months immediately before that
//   - for each month: two bars (comparison vs current), each stacked by agency
// Tables:
//   - monthly totals by period
//   - period totals by agency
// ─────────────────────────────────────────────────────────────
 
const MIN_HOURS = 0.5;
 
const getFM = (k) => dv.current()?.[k];
 
const FILTER_AGENCY = (getFM("Agency Filter") ?? getFM("agency filter") ?? "").toString().trim();
const rawStart = getFM("Start Date") ?? getFM("start date") ?? "";
const rawEnd   = getFM("End Date")   ?? getFM("end date")   ?? "";
 
const SVGNS = "http://www.w3.org/2000/svg";
 
// Helpers
const firstOf = (v) => Array.isArray(v) ? v[0] : v;
 
const toDate = (v) => {
  v = firstOf(v);
  if (!v) return null;
  const d = new Date(v);
  return Number.isNaN(d.getTime()) ? null : d;
};
 
const toNumber = (v) => {
  v = firstOf(v);
  if (v == null || v === "") return 0;
  const n = parseFloat(String(v).trim());
  return Number.isNaN(n) ? 0 : n;
};
 
const svgEl = (name, attrs = {}) => {
  const el = document.createElementNS(SVGNS, name);
  for (const [k, v] of Object.entries(attrs)) el.setAttribute(k, String(v));
  return el;
};
 
const fmtHours = (n) => Number(n || 0).toFixed(2);
 
const fmtISODate = (d) => {
  if (!d) return "—";
  const y = d.getFullYear();
  const m = String(d.getMonth() + 1).padStart(2, "0");
  const day = String(d.getDate()).padStart(2, "0");
  return `${y}-${m}-${day}`;
};
 
const fmtMonthYear = (d) =>
  d.toLocaleString("en-GB", { month: "short", year: "numeric" });
 
const startOfDay = (d) => {
  const x = new Date(d);
  x.setHours(0, 0, 0, 0);
  return x;
};
 
const endOfDay = (d) => {
  const x = new Date(d);
  x.setHours(23, 59, 59, 999);
  return x;
};
 
const startOfMonth = (d) => new Date(d.getFullYear(), d.getMonth(), 1);
const endOfMonth = (d) => new Date(d.getFullYear(), d.getMonth() + 1, 0, 23, 59, 59, 999);
const addMonths = (d, n) => new Date(d.getFullYear(), d.getMonth() + n, d.getDate());
const monthKey = (d) => `${d.getFullYear()}-${String(d.getMonth() + 1).padStart(2, "0")}`;
 
// Optional outer filter window from frontmatter
let START_DATE = toDate(rawStart);
let END_DATE   = toDate(rawEnd);
 
if (START_DATE && END_DATE && START_DATE > END_DATE) {
  const tmp = START_DATE;
  START_DATE = END_DATE;
  END_DATE = tmp;
}
 
// ------------------------------------------------------------------
// Rolling 12-month periods
// ------------------------------------------------------------------
const today = new Date();
const todayStart = startOfDay(today);
const todayEnd = endOfDay(today);
 
const currentPeriodEnd = todayEnd;
const currentPeriodStart = startOfMonth(addMonths(today, -11));
 
const previousPeriodStart = startOfMonth(addMonths(currentPeriodStart, -12));
const previousPeriodEnd = endOfMonth(addMonths(currentPeriodStart, -1));
 
const currentMonths = Array.from({ length: 12 }, (_, i) => startOfMonth(addMonths(currentPeriodStart, i)));
const previousMonths = currentMonths.map(d => startOfMonth(addMonths(d, -12)));
 
// 1) Load + normalise records
const src = dv.pages("#clienthours").where(p => p.clientref && p.date);
 
const records = Array.from(src).map(p => ({
  clientref: firstOf(p.clientref),
  agency: (firstOf(p.agency) ?? "Unknown").toString().trim() || "Unknown",
  date: toDate(p.date),
  hours: toNumber(p.hours)
})).filter(r => r.date !== null);
 
// 2) Apply outer filters and minimum-hours rule
const filtered = records.filter(r => {
  const agencyOk = !FILTER_AGENCY || r.agency === FILTER_AGENCY;
  const startOk  = !START_DATE || r.date >= START_DATE;
  const endOk    = !END_DATE   || r.date <= END_DATE;
  const hoursOk  = r.hours > MIN_HOURS;
  return agencyOk && startOk && endOk && hoursOk;
});
 
// 3) Gather agencies
const agencies = Array.from(new Set(filtered.map(r => r.agency))).sort((a, b) => a.localeCompare(b));
 
// Ensure we still work if no data
if (agencies.length === 0) agencies.push("Unknown");
 
// 4) Aggregate by month + agency
// monthAgencyTotals["YYYY-MM"]["Agency"] = hours
const monthAgencyTotals = new Map();
 
for (const r of filtered) {
  const mk = monthKey(r.date);
  if (!monthAgencyTotals.has(mk)) monthAgencyTotals.set(mk, new Map());
  const byAgency = monthAgencyTotals.get(mk);
  byAgency.set(r.agency, (byAgency.get(r.agency) ?? 0) + r.hours);
}
 
// 5) Build comparison rows
const monthlyComparison = currentMonths.map((currMonthStart, i) => {
  const prevMonthStart = previousMonths[i];
  const currKey = monthKey(currMonthStart);
  const prevKey = monthKey(prevMonthStart);
 
  const currMap = monthAgencyTotals.get(currKey) ?? new Map();
  const prevMap = monthAgencyTotals.get(prevKey) ?? new Map();
 
  const currentByAgency = Object.fromEntries(
    agencies.map(a => [a, currMap.get(a) ?? 0])
  );
  const previousByAgency = Object.fromEntries(
    agencies.map(a => [a, prevMap.get(a) ?? 0])
  );
 
  const currentTotal = agencies.reduce((acc, a) => acc + (currentByAgency[a] ?? 0), 0);
  const previousTotal = agencies.reduce((acc, a) => acc + (previousByAgency[a] ?? 0), 0);
 
  return {
    monthLabel: currMonthStart.toLocaleString("en-GB", { month: "short" }),
    currentMonthLabel: fmtMonthYear(currMonthStart),
    previousMonthLabel: fmtMonthYear(prevMonthStart),
    currentByAgency,
    previousByAgency,
    currentTotal,
    previousTotal
  };
});
 
// 6) Period totals by agency
const currentTotalsByAgency = Object.fromEntries(agencies.map(a => [a, 0]));
const previousTotalsByAgency = Object.fromEntries(agencies.map(a => [a, 0]));
 
monthlyComparison.forEach(row => {
  agencies.forEach(a => {
    currentTotalsByAgency[a] += row.currentByAgency[a] ?? 0;
    previousTotalsByAgency[a] += row.previousByAgency[a] ?? 0;
  });
});
 
const currentTotal = agencies.reduce((acc, a) => acc + currentTotalsByAgency[a], 0);
const previousTotal = agencies.reduce((acc, a) => acc + previousTotalsByAgency[a], 0);
 
// 7) Summary text
const bits = [];
if (FILTER_AGENCY) bits.push(`Agency: **${FILTER_AGENCY}**`);
if (START_DATE || END_DATE) bits.push(`Outer Window: **${fmtISODate(START_DATE)} → ${fmtISODate(END_DATE)}**`);
bits.push(`Min Session Hours: **>${MIN_HOURS}**`);
bits.push(`Current Period: **${fmtISODate(currentPeriodStart)} → ${fmtISODate(currentPeriodEnd)}**`);
bits.push(`Comparison Period: **${fmtISODate(previousPeriodStart)} → ${fmtISODate(previousPeriodEnd)}**`);
 
dv.paragraph(`Client hours by rolling 12-month periods (${bits.join(" · ")}):`);
 
// 8) Monthly totals table
dv.header(3, "Monthly client hours comparison");
dv.table(
  ["Month", "Comparison Period", "Last 12 Months"],
  monthlyComparison.map(r => [
    `${r.monthLabel} (${r.previousMonthLabel} / ${r.currentMonthLabel})`,
    fmtHours(r.previousTotal),
    fmtHours(r.currentTotal)
  ])
);
 
// 9) Period totals by agency table
dv.header(3, "Period totals by agency");
dv.table(
  ["Agency", "Comparison Period", "Last 12 Months"],
  [
    ...agencies.map(a => [a, fmtHours(previousTotalsByAgency[a]), fmtHours(currentTotalsByAgency[a])]),
    ["Total", fmtHours(previousTotal), fmtHours(currentTotal)]
  ]
);
 
// 10) SVG chart (render only in live Obsidian preview context)
if (typeof document !== "undefined" && this?.container?.createEl) {
const container = this.container.createEl("div", { cls: "client-hours-chart" });
container.style.width = "100%";
container.style.maxWidth = "1100px";
 
const width = 1100;
const baseHeight = 360;
const legendRowH = 18;
const legendTitleH = 16;
const legendPadding = 10;
const legendH = (2 + agencies.length) * legendRowH + legendTitleH + legendPadding;
const height = baseHeight + legendH;
 
const margin = { top: 28, right: 20, bottom: 56, left: 56 };
const innerW = width - margin.left - margin.right;
const innerH = baseHeight - margin.top - margin.bottom;
 
const svg = svgEl("svg", {
  viewBox: `0 0 ${width} ${height}`,
  preserveAspectRatio: "xMidYMid meet"
});
svg.style.width = "100%";
svg.style.height = "auto";
container.appendChild(svg);
 
// Definitions / patterns
const defs = svgEl("defs");
svg.appendChild(defs);
 
const palette = [
  "#4C78A8", "#F58518", "#54A24B", "#E45756", "#72B7B2",
  "#B279A2", "#FF9DA6", "#9D755D", "#BAB0AC"
];
 
function addHatchPattern(id, stroke, angleDeg, spacing, strokeW) {
  const p = svgEl("pattern", {
    id,
    patternUnits: "userSpaceOnUse",
    width: spacing,
    height: spacing,
    patternTransform: `rotate(${angleDeg})`
  });
  p.appendChild(svgEl("line", {
    x1: 0, y1: 0,
    x2: 0, y2: spacing,
    stroke,
    "stroke-width": strokeW,
    "stroke-linecap": "square",
    "stroke-opacity": "0.9"
  }));
  defs.appendChild(p);
}
 
// One style per agency
const agencyStyle = new Map();
 
agencies.forEach((agency, idx) => {
  const colour = palette[idx % palette.length];
  const angle = (idx % 4) * 45;
  const spacing = 6 + (idx % 3) * 2;
  const strokeW = 1 + ((idx % 2) * 0.4);
  const patId = `agency-hatch-${idx}`;
 
  addHatchPattern(patId, colour, angle, spacing, strokeW);
 
  agencyStyle.set(agency, {
    fill: colour,
    fillOpacity: 0.35,
    patternUrl: `url(#${patId})`
  });
});
 
// Period outline style
const periodOutline = new Map([
  ["previous", { strokeDasharray: "4 2", strokeOpacity: 0.45 }],
  ["current",  { strokeDasharray: "",    strokeOpacity: 0.25 }]
]);
 
// Scales
const maxY = Math.max(
  1,
  ...monthlyComparison.flatMap(d => [d.previousTotal, d.currentTotal])
);
 
const xStep = innerW / monthlyComparison.length;
const groupW = xStep * 0.76;
const barGap = 8;
const barW = Math.max(8, (groupW - barGap) / 2);
 
const groupX = i => margin.left + i * xStep + (xStep - groupW) / 2;
const y = v => margin.top + innerH - (v / maxY) * innerH;
 
// Axes
const axisG = svg.appendChild(svgEl("g"));
 
axisG.appendChild(svgEl("line", {
  x1: margin.left, y1: margin.top,
  x2: margin.left, y2: margin.top + innerH,
  stroke: "currentColor"
}));
 
axisG.appendChild(svgEl("line", {
  x1: margin.left, y1: margin.top + innerH,
  x2: margin.left + innerW, y2: margin.top + innerH,
  stroke: "currentColor"
}));
 
const yTicks = Math.min(6, Math.max(1, Math.ceil(maxY)));
for (let t = 0; t <= yTicks; t++) {
  const val = (t / yTicks) * maxY;
  const yy = y(val);
 
  axisG.appendChild(svgEl("line", {
    x1: margin.left,
    y1: yy,
    x2: margin.left + innerW,
    y2: yy,
    stroke: "currentColor",
    "stroke-opacity": "0.15"
  }));
 
  const lbl = svgEl("text", {
    x: margin.left - 8,
    y: yy + 4,
    "text-anchor": "end",
    fill: "currentColor"
  });
  lbl.style.fontSize = "12px";
  lbl.textContent = fmtHours(val);
  axisG.appendChild(lbl);
}
 
// Bars
const barsG = svg.appendChild(svgEl("g"));
 
monthlyComparison.forEach((d, i) => {
  const gx = groupX(i);
 
  const seriesValues = [
    { key: "previous", byAgency: d.previousByAgency, total: d.previousTotal, x: gx },
    { key: "current",  byAgency: d.currentByAgency,  total: d.currentTotal,  x: gx + barW + barGap }
  ];
 
  seriesValues.forEach(({ key, byAgency, total, x }) => {
    let cumulative = 0;
 
    agencies.forEach(agency => {
      const value = byAgency[agency] ?? 0;
      if (!value) return;
 
      const y0 = y(cumulative);
      const y1 = y(cumulative + value);
      const h = Math.max(1, y0 - y1);
 
      const st = agencyStyle.get(agency);
 
      barsG.appendChild(svgEl("rect", {
        x, y: y1, width: barW, height: h,
        fill: st.fill,
        "fill-opacity": String(st.fillOpacity),
        stroke: "none"
      }));
 
      barsG.appendChild(svgEl("rect", {
        x, y: y1, width: barW, height: h,
        fill: st.patternUrl,
        "fill-opacity": "1",
        stroke: "currentColor",
        "stroke-opacity": "0.10"
      }));
 
      cumulative += value;
    });
 
    // Period outline around the whole bar
    if (total > 0) {
      const yy = y(total);
      const h = Math.max(1, margin.top + innerH - yy);
      const po = periodOutline.get(key);
 
      barsG.appendChild(svgEl("rect", {
        x, y: yy, width: barW, height: h,
        fill: "none",
        stroke: "currentColor",
        "stroke-opacity": String(po.strokeOpacity),
        "stroke-dasharray": po.strokeDasharray
      }));
    } else {
      barsG.appendChild(svgEl("rect", {
        x,
        y: margin.top + innerH - 1,
        width: barW,
        height: 1,
        fill: "currentColor",
        "fill-opacity": "0.15"
      }));
    }
  });
 
  const xl = svgEl("text", {
    x: margin.left + i * xStep + xStep / 2,
    y: margin.top + innerH + 18,
    "text-anchor": "middle",
    fill: "currentColor"
  });
  xl.style.fontSize = "12px";
  xl.textContent = d.monthLabel;
  barsG.appendChild(xl);
 
  const sub = svgEl("text", {
    x: margin.left + i * xStep + xStep / 2,
    y: margin.top + innerH + 32,
    "text-anchor": "middle",
    fill: "currentColor"
  });
  sub.style.fontSize = "10px";
  sub.style.opacity = "0.7";
  sub.textContent = d.currentMonthLabel.split(" ")[1];
  barsG.appendChild(sub);
});
 
// Title
const title = svgEl("text", {
  x: margin.left,
  y: 18,
  fill: "currentColor"
});
title.style.fontSize = "14px";
title.style.fontWeight = "600";
title.textContent = "Client Hours by Month (Last 12 Months vs Previous 12 Months; stacked by agency)";
svg.appendChild(title);
 
// Legend below chart
const legendG = svg.appendChild(svgEl("g"));
const legendX = margin.left;
const legendTop = baseHeight + 14;
const boxW = 14;
const boxH = 10;
 
// Legend title
const legendTitle = svgEl("text", {
  x: legendX,
  y: legendTop,
  fill: "currentColor"
});
legendTitle.style.fontSize = "12px";
legendTitle.style.fontWeight = "600";
legendTitle.textContent = "Key";
legendG.appendChild(legendTitle);
 
// Period styles
[
  ["Comparison Period", "previous"],
  ["Last 12 Months", "current"]
].forEach(([label, key], idx) => {
  const yRow = legendTop + legendTitleH + idx * legendRowH;
  const po = periodOutline.get(key);
 
  legendG.appendChild(svgEl("rect", {
    x: legendX,
    y: yRow - boxH + 2,
    width: boxW,
    height: boxH,
    fill: "none",
    stroke: "currentColor",
    "stroke-opacity": String(po.strokeOpacity),
    "stroke-dasharray": po.strokeDasharray
  }));
 
  const t = svgEl("text", {
    x: legendX + boxW + 8,
    y: yRow + 2,
    fill: "currentColor"
  });
  t.style.fontSize = "12px";
  t.textContent = label;
  legendG.appendChild(t);
});
 
// Agency styles
agencies.forEach((agency, idx) => {
  const st = agencyStyle.get(agency);
  const yRow = legendTop + legendTitleH + (2 + idx) * legendRowH;
 
  legendG.appendChild(svgEl("rect", {
    x: legendX,
    y: yRow - boxH + 2,
    width: boxW,
    height: boxH,
    fill: st.fill,
    "fill-opacity": String(st.fillOpacity),
    stroke: "none"
  }));
 
  legendG.appendChild(svgEl("rect", {
    x: legendX,
    y: yRow - boxH + 2,
    width: boxW,
    height: boxH,
    fill: st.patternUrl,
    stroke: "currentColor",
    "stroke-opacity": "0.35"
  }));
 
  const t = svgEl("text", {
    x: legendX + boxW + 8,
    y: yRow + 2,
    fill: "currentColor"
  });
  t.style.fontSize = "12px";
  t.textContent = agency;
  legendG.appendChild(t);
});
} else {
  dv.paragraph("_Chart omitted in markdown export context._");
}

Link to original

Client Activity Three Year Comparison

// ─────────────────────────────────────────────────────────────
// Client Hours by Month and Year — current year, last year, year before
// Filters: Agency, Start Date, End Date
// Rules:
//   - include only sessions with hours > 0.5
// Graph:
//   - for each month: three bars, one per year
//   - bars are stacked by agency, matching the existing client-activity chart style
// Tables:
//   - monthly totals by year
//   - yearly totals by agency
// ─────────────────────────────────────────────────────────────
 
const MIN_HOURS = 0.5;
const getFM = (k) => dv.current()?.[k];
 
const FILTER_AGENCY = (getFM("Agency Filter") ?? getFM("agency filter") ?? "").toString().trim();
const rawStart = getFM("Start Date") ?? getFM("start date") ?? "";
const rawEnd   = getFM("End Date")   ?? getFM("end date")   ?? "";
 
const SVGNS = "http://www.w3.org/2000/svg";
const monthOrder = ["Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"];
 
const firstOf = (v) => Array.isArray(v) ? v[0] : v;
 
const toDate = (v) => {
  v = firstOf(v);
  if (!v) return null;
  if (typeof v?.toFormat === "function") return new Date(`${v.toFormat("yyyy-MM-dd")}T00:00:00`);
  if (typeof v?.toISODate === "function") return new Date(`${v.toISODate()}T00:00:00`);
  const match = String(v).match(/^(\d{4})-(\d{2})-(\d{2})/);
  if (match) return new Date(`${match[1]}-${match[2]}-${match[3]}T00:00:00`);
  const d = new Date(v);
  return Number.isNaN(d.getTime()) ? null : d;
};
 
const toNumber = (v) => {
  v = firstOf(v);
  if (v == null || v === "") return 0;
  const n = parseFloat(String(v).trim());
  return Number.isNaN(n) ? 0 : n;
};
 
const fmtHours = (n) => Number(n || 0).toFixed(2);
const fmtISODate = (d) => {
  if (!d) return "—";
  const y = d.getFullYear();
  const m = String(d.getMonth() + 1).padStart(2, "0");
  const day = String(d.getDate()).padStart(2, "0");
  return `${y}-${m}-${day}`;
};
const svgEl = (name, attrs = {}) => {
  const el = document.createElementNS(SVGNS, name);
  for (const [k, v] of Object.entries(attrs)) el.setAttribute(k, String(v));
  return el;
};
 
let START_DATE = toDate(rawStart);
let END_DATE = toDate(rawEnd);
if (START_DATE && END_DATE && START_DATE > END_DATE) {
  const tmp = START_DATE;
  START_DATE = END_DATE;
  END_DATE = tmp;
}
 
const startOfMonth = (d) => new Date(d.getFullYear(), d.getMonth(), 1);
const endOfMonth = (d) => new Date(d.getFullYear(), d.getMonth() + 1, 0, 23, 59, 59, 999);
const addMonths = (d, n) => new Date(d.getFullYear(), d.getMonth() + n, 1);
const monthKey = (d) => `${d.getFullYear()}-${String(d.getMonth() + 1).padStart(2, "0")}`;
const fmtMonthYear = (d) => d.toLocaleString("en-GB", { month: "short", year: "numeric" });
 
const currentPeriodEndMonth = startOfMonth(new Date());
const currentMonths = Array.from({ length: 12 }, (_, i) => addMonths(currentPeriodEndMonth, i - 11));
const periods = [
  { key: "yearBefore", label: "Year before", offsetMonths: -24 },
  { key: "lastYear", label: "Last year", offsetMonths: -12 },
  { key: "thisYear", label: "This year", offsetMonths: 0 }
].map(period => ({
  ...period,
  months: currentMonths.map(d => addMonths(d, period.offsetMonths))
}));
const periodRangeLabel = (period) => `${fmtMonthYear(period.months[0])}–${fmtMonthYear(period.months[period.months.length - 1])}`;
 
// 1) Load + normalise records
const src = dv.pages("#clienthours").where(p => p.clientref && p.date);
const records = Array.from(src).map(p => ({
  agency: (firstOf(p.agency) ?? "Unknown").toString().trim() || "Unknown",
  date: toDate(p.date),
  hours: toNumber(p.hours)
})).filter(r => r.date !== null);
 
// 2) Apply filters
const filtered = records.filter(r => {
  const agencyOk = !FILTER_AGENCY || r.agency === FILTER_AGENCY;
  const startOk = !START_DATE || r.date >= START_DATE;
  const endOk = !END_DATE || r.date <= END_DATE;
  const hoursOk = r.hours > MIN_HOURS;
  return agencyOk && startOk && endOk && hoursOk;
});
 
// 3) Gather agencies
const agencies = Array.from(new Set(filtered.map(r => r.agency))).sort((a, b) => a.localeCompare(b));
if (agencies.length === 0) agencies.push("Unknown");
 
// 4) Aggregate by rolling comparison period, month, agency
const makeAgencyTotals = () => Object.fromEntries(agencies.map(a => [a, 0]));
const periodMonthAgencyTotals = new Map();
for (const period of periods) {
  periodMonthAgencyTotals.set(period.key, new Map(period.months.map(d => [monthKey(d), makeAgencyTotals()])));
}
 
for (const r of filtered) {
  const mk = monthKey(r.date);
  for (const period of periods) {
    const totals = periodMonthAgencyTotals.get(period.key).get(mk);
    if (!totals) continue;
    totals[r.agency] = (totals[r.agency] ?? 0) + r.hours;
  }
}
 
const monthRows = currentMonths.map((monthStart, monthIndex) => {
  const byPeriod = Object.fromEntries(periods.map(period => {
    const comparisonMonth = period.months[monthIndex];
    const byAgency = periodMonthAgencyTotals.get(period.key).get(monthKey(comparisonMonth));
    const total = agencies.reduce((acc, agency) => acc + (byAgency[agency] ?? 0), 0);
    return [period.key, { byAgency, total, month: comparisonMonth }];
  }));
  return { month: monthStart.toLocaleString("en-GB", { month: "short" }), monthIndex, currentMonth: monthStart, byPeriod };
});
 
const periodAgencyTotals = Object.fromEntries(periods.map(period => [period.key, makeAgencyTotals()]));
for (const row of monthRows) {
  for (const period of periods) {
    for (const agency of agencies) {
      periodAgencyTotals[period.key][agency] += row.byPeriod[period.key].byAgency[agency] ?? 0;
    }
  }
}
const periodTotals = Object.fromEntries(periods.map(period => [
  period.key,
  agencies.reduce((acc, agency) => acc + (periodAgencyTotals[period.key][agency] ?? 0), 0)
]));
 
// 5) Summary and tables
const bits = [];
if (FILTER_AGENCY) bits.push(`Agency: **${FILTER_AGENCY}**`);
if (START_DATE || END_DATE) bits.push(`Outer Window: **${fmtISODate(START_DATE)} → ${fmtISODate(END_DATE)}**`);
bits.push(`Min Session Hours: **>${MIN_HOURS}**`);
bits.push(`Current rolling window: **${fmtMonthYear(currentMonths[0])} → ${fmtMonthYear(currentMonths[currentMonths.length - 1])}**`);
dv.paragraph(`Client hours by month, comparing the current rolling 12 months with the same months in the previous two years (${bits.join(" · ")}):`);
 
dv.header(3, "Monthly client hours comparison");
dv.table(
  ["Month", ...periods.map(period => `${period.label} (${periodRangeLabel(period)})`)],
  monthRows.map(row => [row.month, ...periods.map(period => fmtHours(row.byPeriod[period.key].total))])
);
 
dv.header(3, "Rolling-period totals by agency");
dv.table(
  ["Agency", ...periods.map(period => period.label), "Total"],
  [
    ...agencies.map(agency => [
      agency,
      ...periods.map(period => fmtHours(periodAgencyTotals[period.key][agency])),
      fmtHours(periods.reduce((acc, period) => acc + periodAgencyTotals[period.key][agency], 0))
    ]),
    ["Total", ...periods.map(period => fmtHours(periodTotals[period.key])), fmtHours(periods.reduce((acc, period) => acc + periodTotals[period.key], 0))]
  ]
);
 
// 6) SVG chart (render only in live Obsidian preview context)
if (typeof document !== "undefined" && this?.container?.createEl) {
  const container = this.container.createEl("div", { cls: "client-hours-chart" });
  container.style.width = "100%";
  container.style.maxWidth = "1200px";
 
  const width = 1200;
  const baseHeight = 390;
  const legendRowH = 18;
  const legendTitleH = 16;
  const legendPadding = 10;
  const legendH = (3 + agencies.length) * legendRowH + legendTitleH + legendPadding;
  const height = baseHeight + legendH;
 
  const margin = { top: 30, right: 24, bottom: 58, left: 58 };
  const innerW = width - margin.left - margin.right;
  const innerH = baseHeight - margin.top - margin.bottom;
 
  const svg = svgEl("svg", { viewBox: `0 0 ${width} ${height}`, preserveAspectRatio: "xMidYMid meet" });
  svg.style.width = "100%";
  svg.style.height = "auto";
  container.appendChild(svg);
 
  const defs = svgEl("defs");
  svg.appendChild(defs);
 
  const palette = ["#4C78A8", "#F58518", "#54A24B", "#E45756", "#72B7B2", "#B279A2", "#FF9DA6", "#9D755D", "#BAB0AC"];
  function addHatchPattern(id, stroke, angleDeg, spacing, strokeW) {
    const p = svgEl("pattern", { id, patternUnits: "userSpaceOnUse", width: spacing, height: spacing, patternTransform: `rotate(${angleDeg})` });
    p.appendChild(svgEl("line", { x1: 0, y1: 0, x2: 0, y2: spacing, stroke, "stroke-width": strokeW, "stroke-linecap": "square", "stroke-opacity": "0.9" }));
    defs.appendChild(p);
  }
 
  const agencyStyle = new Map();
  agencies.forEach((agency, idx) => {
    const colour = palette[idx % palette.length];
    const patId = `agency-hatch-${idx}`;
    addHatchPattern(patId, colour, (idx % 4) * 45, 6 + (idx % 3) * 2, 1 + ((idx % 2) * 0.4));
    agencyStyle.set(agency, { fill: colour, fillOpacity: 0.35, patternUrl: `url(#${patId})` });
  });
 
  const periodOutline = new Map([
    [periods[0].key, { strokeDasharray: "2 2", strokeOpacity: 0.35 }],
    [periods[1].key, { strokeDasharray: "5 3", strokeOpacity: 0.50 }],
    [periods[2].key, { strokeDasharray: "", strokeOpacity: 0.28 }]
  ]);
 
  const maxY = Math.max(1, ...monthRows.flatMap(row => periods.map(period => row.byPeriod[period.key].total)));
  const xStep = innerW / monthRows.length;
  const groupW = xStep * 0.82;
  const barGap = 5;
  const barW = Math.max(6, (groupW - (barGap * 2)) / 3);
  const groupX = i => margin.left + i * xStep + (xStep - groupW) / 2;
  const yScale = v => margin.top + innerH - (v / maxY) * innerH;
 
  const axisG = svg.appendChild(svgEl("g"));
  axisG.appendChild(svgEl("line", { x1: margin.left, y1: margin.top, x2: margin.left, y2: margin.top + innerH, stroke: "currentColor" }));
  axisG.appendChild(svgEl("line", { x1: margin.left, y1: margin.top + innerH, x2: margin.left + innerW, y2: margin.top + innerH, stroke: "currentColor" }));
 
  const yTicks = Math.min(6, Math.max(1, Math.ceil(maxY)));
  for (let t = 0; t <= yTicks; t++) {
    const val = (t / yTicks) * maxY;
    const yy = yScale(val);
    axisG.appendChild(svgEl("line", { x1: margin.left, y1: yy, x2: margin.left + innerW, y2: yy, stroke: "currentColor", "stroke-opacity": "0.15" }));
    const lbl = svgEl("text", { x: margin.left - 8, y: yy + 4, "text-anchor": "end", fill: "currentColor" });
    lbl.style.fontSize = "12px";
    lbl.textContent = fmtHours(val);
    axisG.appendChild(lbl);
  }
 
  const barsG = svg.appendChild(svgEl("g"));
  monthRows.forEach((row, i) => {
    const gx = groupX(i);
    periods.forEach((period, periodIdx) => {
      const x = gx + periodIdx * (barW + barGap);
      const byAgency = row.byPeriod[period.key].byAgency;
      const total = row.byPeriod[period.key].total;
      let cumulative = 0;
 
      agencies.forEach(agency => {
        const value = byAgency[agency] ?? 0;
        if (!value) return;
        const y0 = yScale(cumulative);
        const y1 = yScale(cumulative + value);
        const h = Math.max(1, y0 - y1);
        const st = agencyStyle.get(agency);
        barsG.appendChild(svgEl("rect", { x, y: y1, width: barW, height: h, fill: st.fill, "fill-opacity": String(st.fillOpacity), stroke: "none" }));
        barsG.appendChild(svgEl("rect", { x, y: y1, width: barW, height: h, fill: st.patternUrl, "fill-opacity": "1", stroke: "currentColor", "stroke-opacity": "0.10" }));
        cumulative += value;
      });
 
      if (total > 0) {
        const yy = yScale(total);
        const h = Math.max(1, margin.top + innerH - yy);
        const yo = periodOutline.get(period.key);
        barsG.appendChild(svgEl("rect", { x, y: yy, width: barW, height: h, fill: "none", stroke: "currentColor", "stroke-opacity": String(yo.strokeOpacity), "stroke-dasharray": yo.strokeDasharray }));
      } else {
        barsG.appendChild(svgEl("rect", { x, y: margin.top + innerH - 1, width: barW, height: 1, fill: "currentColor", "fill-opacity": "0.15" }));
      }
    });
 
    const xl = svgEl("text", { x: margin.left + i * xStep + xStep / 2, y: margin.top + innerH + 18, "text-anchor": "middle", fill: "currentColor" });
    xl.style.fontSize = "12px";
    xl.textContent = row.month;
    barsG.appendChild(xl);
  });
 
  const title = svgEl("text", { x: margin.left, y: 18, fill: "currentColor" });
  title.style.fontSize = "14px";
  title.style.fontWeight = "600";
  title.textContent = `Client Hours by Month (${periods.map(period => periodRangeLabel(period)).join(" / ")}; stacked by agency)`;
  svg.appendChild(title);
 
  const legendG = svg.appendChild(svgEl("g"));
  const legendX = margin.left;
  const legendTop = baseHeight + 14;
  const boxW = 14;
  const boxH = 10;
 
  const legendTitle = svgEl("text", { x: legendX, y: legendTop, fill: "currentColor" });
  legendTitle.style.fontSize = "12px";
  legendTitle.style.fontWeight = "600";
  legendTitle.textContent = "Key";
  legendG.appendChild(legendTitle);
 
  periods.forEach((period, idx) => {
    const yRow = legendTop + legendTitleH + idx * legendRowH;
    const yo = periodOutline.get(period.key);
    legendG.appendChild(svgEl("rect", { x: legendX, y: yRow - boxH + 2, width: boxW, height: boxH, fill: "none", stroke: "currentColor", "stroke-opacity": String(yo.strokeOpacity), "stroke-dasharray": yo.strokeDasharray }));
    const t = svgEl("text", { x: legendX + boxW + 8, y: yRow + 2, fill: "currentColor" });
    t.style.fontSize = "12px";
    t.textContent = `${period.label}: ${periodRangeLabel(period)}`;
    legendG.appendChild(t);
  });
 
  agencies.forEach((agency, idx) => {
    const st = agencyStyle.get(agency);
    const yRow = legendTop + legendTitleH + (3 + idx) * legendRowH;
    legendG.appendChild(svgEl("rect", { x: legendX, y: yRow - boxH + 2, width: boxW, height: boxH, fill: st.fill, "fill-opacity": String(st.fillOpacity), stroke: "none" }));
    legendG.appendChild(svgEl("rect", { x: legendX, y: yRow - boxH + 2, width: boxW, height: boxH, fill: st.patternUrl, stroke: "currentColor", "stroke-opacity": "0.35" }));
    const t = svgEl("text", { x: legendX + boxW + 8, y: yRow + 2, fill: "currentColor" });
    t.style.fontSize = "12px";
    t.textContent = agency;
    legendG.appendChild(t);
  });
} else {
  dv.paragraph("_Chart omitted in markdown export context._");
}

// Exportable Markdown/SVG version for Quartz portfolio snapshots.
// This duplicates the live report calculations, but returns a Markdown string.
const MIN_HOURS = 0.5;
// Export blocks run through the Templater exporter, where dv.current() may not
// exist. Keep export settings explicit rather than depending on dv.current().
const FILTER_AGENCY = "";
const rawStart = "2022-01-01";
const rawEnd = "";
 
const firstOf = (v) => Array.isArray(v) ? v[0] : v;
const toDate = (v) => {
  v = firstOf(v);
  if (!v) return null;
  if (typeof v?.toFormat === "function") return new Date(`${v.toFormat("yyyy-MM-dd")}T00:00:00`);
  if (typeof v?.toISODate === "function") return new Date(`${v.toISODate()}T00:00:00`);
  const match = String(v).match(/^(\d{4})-(\d{2})-(\d{2})/);
  if (match) return new Date(`${match[1]}-${match[2]}-${match[3]}T00:00:00`);
  const d = new Date(v);
  return Number.isNaN(d.getTime()) ? null : d;
};
const toNumber = (v) => {
  v = firstOf(v);
  if (v == null || v === "") return 0;
  const n = parseFloat(String(v).trim());
  return Number.isNaN(n) ? 0 : n;
};
const fmtHours = (n) => Number(n || 0).toFixed(2);
const fmtISODate = (d) => {
  if (!d) return "—";
  const y = d.getFullYear();
  const m = String(d.getMonth() + 1).padStart(2, "0");
  const day = String(d.getDate()).padStart(2, "0");
  return `${y}-${m}-${day}`;
};
const esc = (s) => String(s ?? "")
  .replace(/&/g, "&")
  .replace(/</g, "&lt;")
  .replace(/>/g, "&gt;")
  .replace(/"/g, "&quot;");
const cell = (s) => String(s ?? "").replace(/\|/g, "\\|").replace(/\n/g, " ").trim();
 
let START_DATE = toDate(rawStart);
let END_DATE = toDate(rawEnd);
if (START_DATE && END_DATE && START_DATE > END_DATE) {
  const tmp = START_DATE;
  START_DATE = END_DATE;
  END_DATE = tmp;
}
 
const startOfMonth = (d) => new Date(d.getFullYear(), d.getMonth(), 1);
const endOfMonth = (d) => new Date(d.getFullYear(), d.getMonth() + 1, 0, 23, 59, 59, 999);
const addMonths = (d, n) => new Date(d.getFullYear(), d.getMonth() + n, 1);
const monthKey = (d) => `${d.getFullYear()}-${String(d.getMonth() + 1).padStart(2, "0")}`;
const fmtMonthYear = (d) => d.toLocaleString("en-GB", { month: "short", year: "numeric" });
 
const currentPeriodEndMonth = startOfMonth(new Date());
const currentMonths = Array.from({ length: 12 }, (_, i) => addMonths(currentPeriodEndMonth, i - 11));
const periods = [
  { key: "yearBefore", label: "Year before", offsetMonths: -24 },
  { key: "lastYear", label: "Last year", offsetMonths: -12 },
  { key: "thisYear", label: "This year", offsetMonths: 0 }
].map(period => ({
  ...period,
  months: currentMonths.map(d => addMonths(d, period.offsetMonths))
}));
const periodRangeLabel = (period) => `${fmtMonthYear(period.months[0])}–${fmtMonthYear(period.months[period.months.length - 1])}`;
 
const records = Array.from(dv.pages("#clienthours").where(p => p.clientref && p.date)).map(p => ({
  agency: (firstOf(p.agency) ?? "Unknown").toString().trim() || "Unknown",
  date: toDate(p.date),
  hours: toNumber(p.hours)
})).filter(r => r.date !== null);
 
const filtered = records.filter(r => {
  const agencyOk = !FILTER_AGENCY || r.agency === FILTER_AGENCY;
  const startOk = !START_DATE || r.date >= START_DATE;
  const endOk = !END_DATE || r.date <= END_DATE;
  const hoursOk = r.hours > MIN_HOURS;
  return agencyOk && startOk && endOk && hoursOk;
});
 
const agencies = Array.from(new Set(filtered.map(r => r.agency))).sort((a, b) => a.localeCompare(b));
if (agencies.length === 0) agencies.push("Unknown");
 
const makeAgencyTotals = () => Object.fromEntries(agencies.map(a => [a, 0]));
const periodMonthAgencyTotals = new Map();
for (const period of periods) {
  periodMonthAgencyTotals.set(period.key, new Map(period.months.map(d => [monthKey(d), makeAgencyTotals()])));
}
for (const r of filtered) {
  const mk = monthKey(r.date);
  for (const period of periods) {
    const totals = periodMonthAgencyTotals.get(period.key).get(mk);
    if (!totals) continue;
    totals[r.agency] = (totals[r.agency] ?? 0) + r.hours;
  }
}
 
const monthRows = currentMonths.map((monthStart, monthIndex) => {
  const byPeriod = Object.fromEntries(periods.map(period => {
    const comparisonMonth = period.months[monthIndex];
    const byAgency = periodMonthAgencyTotals.get(period.key).get(monthKey(comparisonMonth));
    const total = agencies.reduce((acc, agency) => acc + (byAgency[agency] ?? 0), 0);
    return [period.key, { byAgency, total, month: comparisonMonth }];
  }));
  return { month: monthStart.toLocaleString("en-GB", { month: "short" }), monthIndex, currentMonth: monthStart, byPeriod };
});
 
const periodAgencyTotals = Object.fromEntries(periods.map(period => [period.key, makeAgencyTotals()]));
for (const row of monthRows) {
  for (const period of periods) {
    for (const agency of agencies) periodAgencyTotals[period.key][agency] += row.byPeriod[period.key].byAgency[agency] ?? 0;
  }
}
const periodTotals = Object.fromEntries(periods.map(period => [period.key, agencies.reduce((acc, agency) => acc + (periodAgencyTotals[period.key][agency] ?? 0), 0)]));
 
const lines = [];
const bits = [];
if (FILTER_AGENCY) bits.push(`Agency: **${FILTER_AGENCY}**`);
if (START_DATE || END_DATE) bits.push(`Outer Window: **${fmtISODate(START_DATE)} → ${fmtISODate(END_DATE)}**`);
bits.push(`Min Session Hours: **>${MIN_HOURS}**`);
bits.push(`Current rolling window: **${fmtMonthYear(currentMonths[0])} → ${fmtMonthYear(currentMonths[currentMonths.length - 1])}**`);
lines.push(`Client hours by month, comparing the current rolling 12 months with the same months in the previous two years (${bits.join(" · ")}):`, "");
 
lines.push("### Monthly client hours comparison", "");
lines.push(`| Month | ${periods.map(period => `${period.label} (${periodRangeLabel(period)})`).join(" | ")} |`);
lines.push(`| --- | ${periods.map(() => "---:").join(" | ")} |`);
for (const row of monthRows) lines.push(`| ${row.month} | ${periods.map(period => fmtHours(row.byPeriod[period.key].total)).join(" | ")} |`);
lines.push("");
 
lines.push("### Rolling-period totals by agency", "");
lines.push(`| Agency | ${periods.map(period => period.label).join(" | ")} | Total |`);
lines.push(`| --- | ${periods.map(() => "---:").join(" | ")} | ---: |`);
for (const agency of agencies) {
  const total = periods.reduce((acc, period) => acc + periodAgencyTotals[period.key][agency], 0);
  lines.push(`| ${cell(agency)} | ${periods.map(period => fmtHours(periodAgencyTotals[period.key][agency])).join(" | ")} | ${fmtHours(total)} |`);
}
lines.push(`| **Total** | ${periods.map(period => `**${fmtHours(periodTotals[period.key])}**`).join(" | ")} | **${fmtHours(periods.reduce((acc, period) => acc + periodTotals[period.key], 0))}** |`, "");
 
// SVG chart as text
const width = 1200;
const baseHeight = 390;
const legendRowH = 18;
const legendTitleH = 16;
const legendPadding = 10;
const legendH = (3 + agencies.length) * legendRowH + legendTitleH + legendPadding;
const height = baseHeight + legendH;
const margin = { top: 30, right: 24, bottom: 58, left: 58 };
const innerW = width - margin.left - margin.right;
const innerH = baseHeight - margin.top - margin.bottom;
const palette = ["#4C78A8", "#F58518", "#54A24B", "#E45756", "#72B7B2", "#B279A2", "#FF9DA6", "#9D755D", "#BAB0AC"];
const agencyStyle = new Map(agencies.map((agency, idx) => {
  const colour = palette[idx % palette.length];
  return [agency, { fill: colour, fillOpacity: 0.35, patId: `export-agency-hatch-${idx}`, angle: (idx % 4) * 45, spacing: 6 + (idx % 3) * 2, strokeW: 1 + ((idx % 2) * 0.4) }];
}));
const periodOutline = new Map([
  [periods[0].key, { strokeDasharray: "2 2", strokeOpacity: 0.35 }],
  [periods[1].key, { strokeDasharray: "5 3", strokeOpacity: 0.50 }],
  [periods[2].key, { strokeDasharray: "", strokeOpacity: 0.28 }]
]);
const maxY = Math.max(1, ...monthRows.flatMap(row => periods.map(period => row.byPeriod[period.key].total)));
const xStep = innerW / monthRows.length;
const groupW = xStep * 0.82;
const barGap = 5;
const barW = Math.max(6, (groupW - (barGap * 2)) / 3);
const groupX = i => margin.left + i * xStep + (xStep - groupW) / 2;
const yScale = v => margin.top + innerH - (v / maxY) * innerH;
 
const svg = [];
svg.push(`<div class="portfolio-chart client-hours-chart">`);
svg.push(`<svg viewBox="0 0 ${width} ${height}" role="img" aria-label="Client hours by rolling comparison period" xmlns="http://www.w3.org/2000/svg">`);
svg.push(`<defs>`);
for (const [agency, st] of agencyStyle.entries()) {
  svg.push(`<pattern id="${st.patId}" patternUnits="userSpaceOnUse" width="${st.spacing}" height="${st.spacing}" patternTransform="rotate(${st.angle})"><line x1="0" y1="0" x2="0" y2="${st.spacing}" stroke="${st.fill}" stroke-width="${st.strokeW}" stroke-linecap="square" stroke-opacity="0.9" /></pattern>`);
}
svg.push(`</defs>`);
svg.push(`<text x="${margin.left}" y="18" fill="currentColor" font-size="14" font-weight="600">Client Hours by Month (${periods.map(period => periodRangeLabel(period)).join(" / ")}; stacked by agency)</text>`);
svg.push(`<line x1="${margin.left}" y1="${margin.top}" x2="${margin.left}" y2="${margin.top + innerH}" stroke="currentColor" />`);
svg.push(`<line x1="${margin.left}" y1="${margin.top + innerH}" x2="${margin.left + innerW}" y2="${margin.top + innerH}" stroke="currentColor" />`);
const yTicks = Math.min(6, Math.max(1, Math.ceil(maxY)));
for (let t = 0; t <= yTicks; t++) {
  const val = (t / yTicks) * maxY;
  const yy = yScale(val);
  svg.push(`<line x1="${margin.left}" y1="${yy.toFixed(1)}" x2="${margin.left + innerW}" y2="${yy.toFixed(1)}" stroke="currentColor" stroke-opacity="0.15" />`);
  svg.push(`<text x="${margin.left - 8}" y="${(yy + 4).toFixed(1)}" text-anchor="end" fill="currentColor" font-size="12">${fmtHours(val)}</text>`);
}
for (const [i, row] of monthRows.entries()) {
  const gx = groupX(i);
  for (const [periodIdx, period] of periods.entries()) {
    const x = gx + periodIdx * (barW + barGap);
    const byAgency = row.byPeriod[period.key].byAgency;
    const total = row.byPeriod[period.key].total;
    let cumulative = 0;
    for (const agency of agencies) {
      const value = byAgency[agency] ?? 0;
      if (!value) continue;
      const y0 = yScale(cumulative);
      const y1 = yScale(cumulative + value);
      const h = Math.max(1, y0 - y1);
      const st = agencyStyle.get(agency);
      svg.push(`<rect x="${x.toFixed(1)}" y="${y1.toFixed(1)}" width="${barW.toFixed(1)}" height="${h.toFixed(1)}" fill="${st.fill}" fill-opacity="${st.fillOpacity}" />`);
      svg.push(`<rect x="${x.toFixed(1)}" y="${y1.toFixed(1)}" width="${barW.toFixed(1)}" height="${h.toFixed(1)}" fill="url(#${st.patId})" stroke="currentColor" stroke-opacity="0.10" />`);
      cumulative += value;
    }
    if (total > 0) {
      const yy = yScale(total);
      const h = Math.max(1, margin.top + innerH - yy);
      const yo = periodOutline.get(period.key);
      svg.push(`<rect x="${x.toFixed(1)}" y="${yy.toFixed(1)}" width="${barW.toFixed(1)}" height="${h.toFixed(1)}" fill="none" stroke="currentColor" stroke-opacity="${yo.strokeOpacity}" stroke-dasharray="${yo.strokeDasharray}" />`);
    } else {
      svg.push(`<rect x="${x.toFixed(1)}" y="${margin.top + innerH - 1}" width="${barW.toFixed(1)}" height="1" fill="currentColor" fill-opacity="0.15" />`);
    }
  }
  svg.push(`<text x="${(margin.left + i * xStep + xStep / 2).toFixed(1)}" y="${margin.top + innerH + 18}" text-anchor="middle" fill="currentColor" font-size="12">${row.month}</text>`);
}
const legendX = margin.left;
const legendTop = baseHeight + 14;
const boxW = 14;
const boxH = 10;
svg.push(`<text x="${legendX}" y="${legendTop}" fill="currentColor" font-size="12" font-weight="600">Key</text>`);
periods.forEach((period, idx) => {
  const yRow = legendTop + legendTitleH + idx * legendRowH;
  const yo = periodOutline.get(period.key);
  svg.push(`<rect x="${legendX}" y="${yRow - boxH + 2}" width="${boxW}" height="${boxH}" fill="none" stroke="currentColor" stroke-opacity="${yo.strokeOpacity}" stroke-dasharray="${yo.strokeDasharray}" />`);
  svg.push(`<text x="${legendX + boxW + 8}" y="${yRow + 2}" fill="currentColor" font-size="12">${period.label}: ${periodRangeLabel(period)}</text>`);
});
agencies.forEach((agency, idx) => {
  const st = agencyStyle.get(agency);
  const yRow = legendTop + legendTitleH + (3 + idx) * legendRowH;
  svg.push(`<rect x="${legendX}" y="${yRow - boxH + 2}" width="${boxW}" height="${boxH}" fill="${st.fill}" fill-opacity="${st.fillOpacity}" />`);
  svg.push(`<rect x="${legendX}" y="${yRow - boxH + 2}" width="${boxW}" height="${boxH}" fill="url(#${st.patId})" stroke="currentColor" stroke-opacity="0.35" />`);
  svg.push(`<text x="${legendX + boxW + 8}" y="${yRow + 2}" fill="currentColor" font-size="12">${esc(agency)}</text>`);
});
svg.push(`</svg>`);
svg.push(`</div>`);
 
lines.push("### Client hours graph", "", svg.join("\n"), "");
return lines.join("\n");

Link to original