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}%`
    );
  }
}