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");