`; }function printDetailedReport() { if (!reportData) { alert("Load and calculate the sheet first."); return; }const html = buildDetailedReportHtml(); const win = window.open("", "_blank", "width=1200,height=900"); if (!win) { alert("Popup blocked. Please allow popups for this page."); return; }win.document.open(); win.document.write(html); win.document.close(); win.focus();setTimeout(() => { win.print(); }, 400); }// ----------------- Core Calculation (month = full sheet) ----------------- function calculateAll() { // 1) Extract evaluations and build transaction groups const evalsByCustomer = new Map(); // customer -> [{provider, evalDate, evalDateStr}] const txGroups = new Map(); // txKey -> {customer, date, checkoutBy, txId, amounts:[], rows:[]}for (const row of rawRows) { const customer = getCustomer(row); const svc = getService(row); const provider = getProvider(row); const checkoutBy = getCheckoutBy(row); const txId = getTransactionId(row); const dt = checkoutDate(row); const ap = amtPaid(row);// Evaluations if (EVALUATION_REGEX.test(svc)) { const ed = evaluationDate(row); if (customer && provider && ed) { const arr = evalsByCustomer.get(customer) || []; arr.push({ customer, provider, service: svc, evalDate: ed, evalDateStr: safeStr(getFirstCell(row, HEADER_ALIASES.appointmentDate)) || safeStr(getFirstCell(row, HEADER_ALIASES.checkoutDate)) || "" }); evalsByCustomer.set(customer, arr); } }// Transactions (for sales + for provider matching) // Only include rows that are sales (Amt paid > 0, not refund) if (!customer || !dt || !isSaleRow(row)) continue;// Use Transaction ID when present; otherwise fall back to customer+date+checkoutBy. const fallbackKey = `${customer}::${dt.toISOString()}::${checkoutBy || ""}`; const txKey = txId ? `TX::${txId}` : fallbackKey;const g = txGroups.get(txKey) || { txKey, txId, customer, date: dt, checkoutBy, amounts: [], rows: [] };g.amounts.push(ap); g.rows.push({ customer, service: svc, amtPaid: ap, checkoutBy, provider, checkoutDate: safeStr(getFirstCell(row, HEADER_ALIASES.checkoutDate)), appointmentDate: safeStr(getFirstCell(row, HEADER_ALIASES.appointmentDate)), transactionId: txId });txGroups.set(txKey, g); }// 2) Normalize transactions const transactions = Array.from(txGroups.values()).map(g => ({ ...g, total: computeTransactionTotal(g.amounts) })).filter(t => t.total > 0);transactions.sort((a, b) => a.date - b.date);// 3) Checkout summary const checkoutTotals = new Map(); for (const t of transactions) { const name = safeStr(t.checkoutBy) || "(blank)"; checkoutTotals.set(name, (checkoutTotals.get(name) || 0) + t.total); }const checkoutNames = Array.from(checkoutTotals.keys()).sort((a, b) => a.localeCompare(b)); renderRatesEditor(checkoutNames);const checkoutSummary = checkoutNames.map(name => { const total = checkoutTotals.get(name) || 0; const percent = Number(rates[name] ?? 0); const commission = total * (percent / 100); return { name, total, percent, commission }; }).sort((a,b) => b.commission - a.commission);const salesTotal = checkoutSummary.reduce((sum, x) => sum + x.total, 0); const totalCheckoutCommission = checkoutSummary.reduce((sum, x) => sum + x.commission, 0);// 4) Provider commission matching for (const arr of evalsByCustomer.values()) { arr.sort((a, b) => a.evalDate - b.evalDate); }const txByCustomer = new Map(); for (const t of transactions) { const arr = txByCustomer.get(t.customer) || []; arr.push(t); txByCustomer.set(t.customer, arr); } for (const arr of txByCustomer.values()) arr.sort((a, b) => a.date - b.date);const providerTotals = new Map(); // provider -> {count,totalPay} const matchedDetails = []; const unmatchedEvals = [];for (const [customer, evals] of evalsByCustomer.entries()) { const custTx = txByCustomer.get(customer) || []; if (!evals.length) continue;const evalSum = new Array(evals.length).fill(0); const evalTxDetails = new Array(evals.length).fill(null).map(() => []);for (const t of custTx) { let idx = -1; for (let i = evals.length - 1; i >= 0; i--) { if (evals[i].evalDate < t.date) { idx = i; break; } } if (idx === -1) continue; evalSum[idx] += t.total; evalTxDetails[idx].push({ txKey: t.txKey, txId: t.txId, date: t.date, dateStr: t.rows?.[0]?.checkoutDate || t.date.toISOString(), total: t.total, checkoutBy: t.checkoutBy, rowCount: t.rows.length, rows: t.rows }); }for (let i = 0; i < evals.length; i++) { const ev = evals[i]; const totalAfter = evalSum[i];if (!(totalAfter > 0)) { unmatchedEvals.push({ customer, provider: ev.provider, service: ev.service, evalDateStr: ev.evalDateStr || ev.evalDate.toISOString() }); continue; }const pay = (totalAfter < THRESHOLD_AMOUNT) ? PAY_UNDER : PAY_OVER_OR_EQUAL;const agg = providerTotals.get(ev.provider) || { count: 0, totalPay: 0 }; agg.count += 1; agg.totalPay += pay; providerTotals.set(ev.provider, agg);matchedDetails.push({ customer, provider: ev.provider, service: ev.service, evalDateStr: ev.evalDateStr || ev.evalDate.toISOString(), purchaseTotal: totalAfter, pay, thresholdApplied: totalAfter < THRESHOLD_AMOUNT ? `< ${THRESHOLD_AMOUNT}` : `>= ${THRESHOLD_AMOUNT}`, assignedTransactions: evalTxDetails[i] }); } }// 5) Render current screen renderCheckoutSummary(checkoutSummary); renderProviderSummary(providerTotals, matchedDetails, unmatchedEvals); renderAudit({ totalRows: rawRows.length, txCount: txGroups.size, salesTxCount: transactions.length, salesTotal, evalCount: Array.from(evalsByCustomer.values()).reduce((a, b) => a + b.length, 0), checkoutPeople: checkoutNames.length, headersCount: headers.length, headersPreview: headers.slice(0, 30), });// 6) Save full report data for printing reportData = { generatedAt: new Date(), headers: [...headers], rawRowCount: rawRows.length, checkoutSummary, providerSummary: Array.from(providerTotals.entries()) .map(([provider, v]) => ({ provider, ...v })) .sort((a, b) => b.totalPay - a.totalPay), matchedDetails, unmatchedEvals, transactions: transactions.map(t => ({ txKey: t.txKey, txId: t.txId, customer: t.customer, checkoutBy: t.checkoutBy, total: t.total, date: t.date.toISOString(), dateStr: t.rows?.[0]?.checkoutDate || t.date.toISOString(), rows: t.rows })), totals: { salesTotal, totalCheckoutCommission, totalProviderPay: Array.from(providerTotals.values()).reduce((sum, x) => sum + x.totalPay, 0) } }; }// ----------------- Actions ----------------- async function loadAndRun() { const link = sheetLinkEl.value.trim(); if (!link) { setStatus("Paste a Google Sheet link first.", "err"); return; }setStatus("Loading sheet…", ""); loadBtn.disabled = true;try { const csv = await fetchSheetCSV(link); const parsed = parseCSV(csv); const obj = toObjects(parsed);headers = obj.headers; rawRows = obj.objects;if (!headers.length || !rawRows.length) throw new Error("Sheet loaded but appears empty or not CSV.");buildHeaderMap(headers);resultsEl.style.display = "block";if (!hasAnyHeader(HEADER_ALIASES.amtPaid)) { setStatus(`Loaded ${rawRows.length} rows. Warning: no amount column detected in this sheet, so monetary totals may stay at $0.`, "ok"); } else { setStatus(`Done. Loaded ${rawRows.length} rows.`, "ok"); }calculateAll(); resultsEl.scrollIntoView({ behavior: "smooth", block: "start" }); } catch (e) { console.error(e); setStatus(e.message || String(e), "err"); resultsEl.style.display = "none"; reportData = null; } finally { loadBtn.disabled = false; } }loadBtn.addEventListener("click", loadAndRun);recalcBtn.addEventListener("click", () => { if (!rawRows.length) return; rates = loadRates(); calculateAll(); setStatus("Recalculated.", "ok"); resultsEl.scrollIntoView({ behavior: "smooth", block: "start" }); });resetBtn.addEventListener("click", () => { resetRates(); if (rawRows.length) calculateAll(); setStatus("Rates reset to defaults.", "ok"); });printBtn.addEventListener("click", printDetailedReport);// Remember last link const LAST_LINK_KEY = "last_sheet_link_month_v1"; try { const last = localStorage.getItem(LAST_LINK_KEY); if (last) sheetLinkEl.value = last; } catch {} sheetLinkEl.addEventListener("change", () => { try { localStorage.setItem(LAST_LINK_KEY, sheetLinkEl.value.trim()); } catch {} });})();