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