2023-2024 Client Hours Report with Grand Total - 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
 
// Fetch pages with the 'clienthours' tag
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: Check the pages fetched
dv.paragraph(`Total records fetched: ${pages.length}`);
if (pages.length === 0) {
  dv.paragraph("No records found for the specified query.");
} else {
  // Group by 'clientref' and 'medium'
  const groupedData = {};
  let grandTotalHours = 0;
 
  pages.forEach((p) => {
    const agency = p.agency || "No Agency";
    const medium = p.medium || "No medium";
    const clientref = p.clientref || "Not stated";
    const key = `${agency} - ${clientref} - ${medium}`;
 
    if (!groupedData[key]) {
      groupedData[key] = {
        agency: agency,
        clientref: clientref,
        medium: medium,
        totalHours: 0,
        entries: [],
      };
    }
 
    const hours = p.hours || 0;
    groupedData[key].totalHours += hours;
    grandTotalHours += hours;
    groupedData[key].entries.push({
      file: p.file.link,
      date: p.date,
      hours: hours,
      agency: p.agency,
    });
  });
 
  // Prepare table data
  const tableData = [];
  for (const [key, group] of Object.entries(groupedData)) {
    // Add entries for each group
    group.entries.forEach((entry) => {
      tableData.push([entry.file, entry.date, entry.hours]);
    });
    // Add total hours row for each group
    tableData.push(["-----------", "----", "----"]);
    tableData.push([
      `**${group.agency} - ${group.clientref} - ${group.medium} Total**`,
      "",
      `**${group.totalHours}**`,
      "",
    ]);
    tableData.push(["-----------", "----", "----"]);
  }
 
  // Add grand total hours row
  tableData.push(["**Grand Total**", "", `**${grandTotalHours}**`, ""]);
  tableData.push(["----", "----", "----"]);
 
  // Debug: Check the format of tableData
  /*
    console.log("Table Data:");
    tableData.forEach((row, index) => {
        console.log(`Row ${index}:`, row);
    });*/
 
  // Ensure that tableData is in the correct format for dv.table
  /*   const isValidFormat = Array.isArray(tableData) && 
        tableData.length > 0 &&
        tableData.every(row => Array.isArray(row) && row.length === 4);
 
    if (isValidFormat) {
        dv.table(["File", "Date", "Hours", "Who"], tableData);
    } else {
        dv.paragraph("Table Data is not in the expected format. Check console for details.");
    } */
  dv.table(["File", "Date", "Hours"], tableData);
}