All Ears Room Booking Report

//
// FIXED FOLDER WHERE CSV FILES ARE STORED (relative to vault root)
//
const targetFolder = "100 CPD Record/Room Bookings";
 
//
// OPTIONAL DATE RANGE + HOURLY RATE FROM YAML FRONTMATTER
//
const fm = dv.current().file.frontmatter || {};
const startStr = fm.report_start || null;    // e.g. 2025-11-01
const endStr   = fm.report_end   || null;    // e.g. 2025-12-31
 
// hourly_rate in YAML, default to 8 if missing or invalid
let hourlyRate = Number(fm.hourly_rate);
if (!hourlyRate || isNaN(hourlyRate)) {
    hourlyRate = 8;
}
 
function inRange(dateStr) {
    if (!dateStr) return false;
    if (startStr && dateStr < startStr) return false;
    if (endStr   && dateStr > endStr) return false;
    return true;
}
 
//
// FIND ALL CSV FILES IN THE FIXED FOLDER
//
const allFiles = app.vault.getFiles();
const csvFiles = allFiles.filter(f =>
    f.path.startsWith(targetFolder + "/") &&
    f.name.startsWith("officernd_bookings-") &&
    f.path.endsWith(".csv")
);
 
if (csvFiles.length === 0) {
    dv.paragraph("No officernd_bookings-*.csv files found in '" + targetFolder + "'");
    return;
}
 
//
// SIMPLE CSV PARSER
//
async function loadAndParseCsv(path) {
    const text = await dv.io.load(path);
    if (!text) return [];
 
    const lines = text.trim().split(/\r?\n/);
    if (lines.length <= 1) return [];
 
    const headers = lines[0].split(",").map(h => h.trim());
    const rows = [];
 
    for (let i = 1; i < lines.length; i++) {
        const line = lines[i].trim();
        if (!line) continue;
 
        const cols = line.split(",");
        const obj = {};
        headers.forEach((h, idx) => {
            obj[h] = (cols[idx] ?? "").trim();
        });
        rows.push(obj);
    }
 
    return rows;
}
 
//
// LOAD ALL CSVs AND DEDUPLICATE BY booking_id
//
const bookingsById = {};
 
for (let file of csvFiles) {
    const rows = await loadAndParseCsv(file.path);
    for (let row of rows) {
        if (!row.booking_id) continue;
        bookingsById[row.booking_id] = row;   // latest wins if duplicates
    }
}
 
const allRows = Object.values(bookingsById);
if (allRows.length === 0) {
    dv.paragraph("No bookings found in parsed files.");
    return;
}
 
//
// FILTER BY DATE RANGE + AGGREGATE BY MONTH (HOURS + COST)
//
const byMonth = {};  // month -> { hours, cost }
 
for (let row of allRows) {
    const date = row.date_local;
    if (!date || date.length < 10) continue;
    if (!inRange(date)) continue;
 
    const month = date.slice(0, 7); // YYYY-MM
    const hours = Number(row.duration_hours) || 0;
    const cost  = hours * hourlyRate;
 
    if (!byMonth[month]) {
        byMonth[month] = { hours: 0, cost: 0 };
    }
 
    byMonth[month].hours += hours;
    byMonth[month].cost  += cost;
}
 
//
// DISPLAY THE RESULTS
//
const tableRows = Object.entries(byMonth)
    .sort(([m1], [m2]) => m1.localeCompare(m2))
    .map(([m, v]) => [
        m,
        v.hours.toFixed(2),
        "£" + v.cost.toFixed(2)
    ]);
 
if (tableRows.length === 0) {
    dv.paragraph("No bookings within the selected date range.");
} else {
    dv.table(["Month", "Hours", `Cost (hourly £${hourlyRate})`], tableRows);
}
 

Downloading OfficeRND information about All Ears bookings for analysis.md

---

References

---