// Reports — CRM-standard reports with marketing focus (sources, geography, conversion) const REPORT_DEFS = [ { key: 'marketing', label: 'Marketing', desc: 'Lead sources, channels & geography' }, { key: 'conversion', label: 'Conversion', desc: 'Funnel & stage progression' }, { key: 'sales', label: 'Sales performance', desc: 'Designer leaderboard & win rates' }, { key: 'showrooms', label: 'Showrooms', desc: 'Side-by-side location comparison' }, { key: 'activity', label: 'Activity', desc: 'Calls, emails, meetings per designer' }, { key: 'lost', label: 'Lost jobs', desc: 'Loss rate & top reasons for lost jobs' }, ]; // Shared scope computation — used by the page AND the export dialog so they stay in sync. // Returns role-restricted + showroom/designer/date-filtered data, plus the reps/showrooms // that actually have data in scope (so empty rows are hidden once a filter is applied). function computeReportScope({ contacts, deals, reps, showrooms, currentUser, showroom, rep, period, customFrom, customTo }) { const isSales = currentUser.role === 'sales_designer'; let aC = contacts, aD = deals; // Role access if (isSales) { const myName = reps.find(r => r.id === currentUser.repId)?.name || currentUser.name; aC = aC.filter(c => currentUser.works.includes(c.homeShowroom) && c.owner === myName); aD = aD.filter(d => currentUser.works.includes(d.showroom) && d.rep === currentUser.repId); } // Showroom if (showroom && showroom !== 'all') { aC = aC.filter(c => c.homeShowroom === showroom); aD = aD.filter(d => d.showroom === showroom); } // Designer options depend on showroom const repOptions = (showroom && showroom !== 'all' ? reps.filter(r => (r.works || []).includes(showroom)) : reps) .filter(r => !isSales || r.id === currentUser.repId); // Designer const effRep = isSales ? currentUser.repId : (rep || 'all'); if (effRep && effRep !== 'all') { const rName = reps.find(r => r.id === effRep)?.name; aC = aC.filter(c => c.owner === rName); aD = aD.filter(d => d.rep === effRep); } // Date range const today = new Date(); const inPeriod = (dateStr) => { if (period === 'custom') { if (!dateStr) return false; if (customFrom && dateStr < customFrom) return false; if (customTo && dateStr > customTo) return false; return true; } if (period === 'all') return true; if (!dateStr) return false; const d = new Date(dateStr); if (isNaN(d)) return false; const mb = period === '1mo' ? 1 : period === '3mo' ? 3 : period === '12mo' ? 12 : 60; const cutoff = new Date(today); cutoff.setMonth(cutoff.getMonth() - mb); return d >= cutoff; }; // Contacts may lack a firstContact date (e.g. imported from Supabase without that field). // Fall back to lastContact, then include undated contacts in every period so they're never invisible. const periodContacts = aC.filter(c => { const d = c.firstContact || c.lastContact; if (!d) return true; return inPeriod(d); }); const periodDeals = aD.filter(d => inPeriod(d.created)); // Which reps / showrooms actually belong in scope (so we don't show empty rows once filtered) const scopeReps = (effRep && effRep !== 'all') ? reps.filter(r => r.id === effRep) : (repOptions.length ? repOptions : reps); const activeIds = new Set(); periodContacts.forEach(c => activeIds.add(c.homeShowroom)); periodDeals.forEach(d => activeIds.add(d.showroom)); let scopeShowrooms = showrooms; if (showroom && showroom !== 'all') scopeShowrooms = showrooms.filter(s => s.id === showroom); else if (effRep && effRep !== 'all') scopeShowrooms = showrooms.filter(s => activeIds.has(s.id)); return { accessContacts: aC, accessDeals: aD, periodContacts, periodDeals, repOptions, scopeReps, scopeShowrooms, effRep, isSales }; } function buildReportMeta({ reps, showrooms, currentUser, showroom, effRep, period, customFrom, customTo, periodContacts, periodDeals }) { const T = window.T || ((k) => k); const isSales = currentUser.role === 'sales_designer'; const PERIOD_LABELS = { '1mo': 'Last 30 days', '3mo': 'Last 3 months', '12mo': 'Last 12 months', '5y': 'Last 5 years', all: 'All time' }; const showroomLabel = (!showroom || showroom === 'all') ? (isSales ? 'My ' + T('locs_lc') : 'All ' + T('locs_lc')) : (showrooms.find(s => s.id === showroom)?.name || showroom); const repLabel = (effRep && effRep !== 'all') ? (reps.find(r => r.id === effRep)?.name || effRep) : 'All ' + T('reps_lc'); const periodLabel = period === 'custom' ? `${customFrom || '…'} → ${customTo || '…'}` : PERIOD_LABELS[period]; return { showroomLabel, repLabel, periodLabel, generatedStr: new Date().toLocaleString('en-AU', { day: 'numeric', month: 'short', year: 'numeric', hour: '2-digit', minute: '2-digit' }), contacts: periodContacts.length, deals: periodDeals.length, }; } function Reports({ contacts, deals, reps, showrooms, currentUser, showroomScope, openDeal }) { const T = window.T || ((k) => k); const [tab, setTab] = React.useState('marketing'); const [period, setPeriod] = React.useState('12mo'); const [customFrom, setCustomFrom] = React.useState(''); const [customTo, setCustomTo] = React.useState(''); const [filterShowroom, setFilterShowroom] = React.useState(showroomScope || 'all'); const [filterRep, setFilterRep] = React.useState('all'); const [exportFmt, setExportFmt] = React.useState(null); // null = closed, otherwise initial format React.useEffect(() => { setFilterShowroom(showroomScope || 'all'); }, [showroomScope]); const isSales = currentUser.role === 'sales_designer'; const data = { contacts, deals, reps, showrooms }; const scope = computeReportScope({ ...data, currentUser, showroom: filterShowroom, rep: filterRep, period, customFrom, customTo }); const meta = buildReportMeta({ ...data, currentUser, showroom: filterShowroom, effRep: scope.effRep, period, customFrom, customTo, periodContacts: scope.periodContacts, periodDeals: scope.periodDeals }); const showroomOptions = isSales ? showrooms.filter(s => currentUser.works.includes(s.id)) : showrooms; return (
{/* Tabs + actions */}
{REPORT_DEFS.map(d => ( ))}
{/* Parameters bar */}
Parameters {/* Showroom */} {/* Designer */} {!isSales && ( )} {/* Date range */} {period === 'custom' && ( setCustomFrom(e.target.value)} style={{ fontSize: 12 }} title="From"/> to setCustomTo(e.target.value)} style={{ fontSize: 12 }} title="To"/> )} {(filterShowroom !== 'all' || filterRep !== 'all' || period !== '12mo') && !isSales && ( )} {meta.contacts} contacts ·{' '} {meta.deals} deals in range
{tab === 'marketing' && } {tab === 'conversion' && } {tab === 'sales' && } {tab === 'showrooms' && } {tab === 'activity' && } {tab === 'lost' && } {exportFmt && ( setExportFmt(null)} /> )}
); } // === Marketing: where do customers come from, where do they live === function MarketingReport({ contacts, deals, showrooms }) { // Source breakdown const sourceAgg = {}; for (const c of contacts) { const s = c.primarySource || '— Not recorded —'; if (!sourceAgg[s]) sourceAgg[s] = { total: 0, qualified: 0, sold: 0, value: 0 }; sourceAgg[s].total++; if (['qualified','in_pipeline','customer_active','past_customer','lost_quote'].includes(c.lifecycle)) sourceAgg[s].qualified++; if (['past_customer','customer_active','dormant_past_customer'].includes(c.lifecycle)) { sourceAgg[s].sold++; sourceAgg[s].value += c.ltv || 0; } } const sourceRows = Object.entries(sourceAgg).sort((a, b) => b[1].total - a[1].total); const maxSource = sourceRows[0]?.[1].total || 1; // Means breakdown const meansAgg = {}; for (const c of contacts) { const m = c.primaryMeans || '— Not recorded —'; if (!meansAgg[m]) meansAgg[m] = { total: 0, sold: 0 }; meansAgg[m].total++; if (['past_customer','customer_active','dormant_past_customer'].includes(c.lifecycle)) meansAgg[m].sold++; } const meansRows = Object.entries(meansAgg).sort((a, b) => b[1].total - a[1].total); const maxMeans = meansRows[0]?.[1].total || 1; // Geography — suburb breakdown const suburbAgg = {}; for (const c of contacts) { const su = (c.suburb || '— Not recorded —').trim(); if (!suburbAgg[su]) suburbAgg[su] = { total: 0, sold: 0, value: 0, showrooms: {} }; suburbAgg[su].total++; if (['past_customer','customer_active','dormant_past_customer'].includes(c.lifecycle)) { suburbAgg[su].sold++; suburbAgg[su].value += c.ltv || 0; } suburbAgg[su].showrooms[c.homeShowroom] = (suburbAgg[su].showrooms[c.homeShowroom] || 0) + 1; } const suburbRows = Object.entries(suburbAgg) .filter(([s]) => s !== '— Not recorded —') .sort((a, b) => b[1].total - a[1].total) .slice(0, 20); const maxSuburb = suburbRows[0]?.[1].total || 1; return (
['past_customer','customer_active','dormant_past_customer'].includes(c.lifecycle)).length} became customers`} deltaPct={0.058}/>
Lead source performance
Where contacts heard about KUB · ranked by volume
{sourceRows.map(([src, v]) => { const conv = v.total ? v.sold / v.total : 0; return ( ); })}
Source Contacts Sold Conv. rate Revenue
{src}
 {v.total} 0 ? 500 : 400 }}>{v.sold} 0.2 ? 'var(--won)' : conv > 0.1 ? 'var(--ink-2)' : 'var(--ink-3)' }}> {fmt.pct(conv, 0)} {v.value > 0 ? fmt.money(v.value, true) : '—'}
First-contact channel
How customers reached out
{meansRows.map(([m, v]) => { const conv = v.total ? v.sold / v.total : 0; return (
{m}
{v.total / maxMeans > 0.2 && {v.total}}
{fmt.pct(conv, 0)} conv
); })}
Top suburbs by contact volume
Where your customers live · target marketing efforts here
{suburbRows.map(([suburb, v], i) => { const conv = v.total ? v.sold / v.total : 0; const closestId = Object.entries(v.showrooms).sort((a, b) => b[1] - a[1])[0]?.[0]; const closest = showrooms.find(s => s.id === closestId); return ( ); })}
# Suburb Closest showroom Contacts Sold Conv. rate Total revenue Volume
{String(i + 1).padStart(2, '0')} {suburb} {closest && ( {closest.name} )} {v.total} 0 ? 500 : 400 }}>{v.sold} {fmt.pct(conv, 0)} {v.value > 0 ? fmt.money(v.value, true) : '—'}
{suburbRows.length === 20 && (
Showing top 20 suburbs · use Export for the full list
)}
); } // === Sales: designer performance, conversion funnel === function SalesReport({ contacts, deals, reps, showrooms, showroomScope }) { const scopedReps = showroomScope === 'all' ? reps : reps.filter(r => r.works.includes(showroomScope)); const agg = scopedReps.map(r => { const repDeals = deals.filter(d => d.rep === r.id); const repContacts = contacts.filter(c => c.owner === r.name); const won = repDeals.filter(d => d.stage === 'sold'); const lost = repDeals.filter(d => d.stage === 'lost'); const active = repDeals.filter(d => d.stage !== 'sold' && d.stage !== 'lost'); const wonVal = won.reduce((s, d) => s + d.value, 0); const closed = won.length + lost.length; return { ...r, contactCount: repContacts.length, wonVal, wonCount: won.length, lostCount: lost.length, activeCount: active.length, activeVal: active.reduce((s, d) => s + d.value, 0), winRate: closed ? won.length / closed : 0, avgDeal: won.length ? wonVal / won.length : 0, }; }).sort((a, b) => b.wonVal - a.wonVal); // Funnel const totalContacts = contacts.length; const qualified = contacts.filter(c => ['qualified','in_pipeline','customer_active','past_customer','lost_quote'].includes(c.lifecycle)).length; const quoted = contacts.filter(c => (c.dealCount || 0) > 0 || ['in_pipeline','customer_active','past_customer','dormant_customer_active','dormant_past_customer'].includes(c.lifecycle)).length; const sold = contacts.filter(c => ['past_customer','customer_active','dormant_past_customer'].includes(c.lifecycle)).length; const funnel = [ { id: 'contacts', label: 'Total Contacts', count: totalContacts, prevCount: null }, { id: 'qualified', label: 'Qualified', count: qualified, prevCount: totalContacts }, { id: 'quoted', label: 'Quoted', count: quoted, prevCount: qualified }, { id: 'sold', label: 'Sold', count: sold, prevCount: quoted }, ]; return (
Conversion funnel
From first contact to closed sale
{funnel.map((f, i) => { const w = (f.count / Math.max(totalContacts, 1)) * 100; const conv = i === 0 ? null : f.count / Math.max(f.prevCount, 1); return (
{f.label}
{f.count} {conv !== null && {fmt.pct(conv, 0)}}
{i === 0 ? 'Start' : `${fmt.pct(f.count / Math.max(totalContacts, 1), 0)} of all`}
); })}
Designer leaderboard
Closed-won value, period to date
{agg.slice(0, 10).map((r, i) => { const max = Math.max(...agg.map(x => x.wonVal), 1); return (
#{i + 1}
{r.name}
{r.wonVal / max > 0.2 && {fmt.money(r.wonVal, true)}}
{r.wonCount} wins
); })}
Designer performance detail
Per-rep funnel · selected period
{agg.map(r => ( ))}
Designer Contacts owned Active quotes Active $ Wins Closed-won $ Lost Win rate Avg deal
{r.name}
{r.contactCount} {r.activeCount} {fmt.money(r.activeVal, true)} {r.wonCount} {fmt.money(r.wonVal, true)} {r.lostCount} {fmt.pct(r.winRate, 0)} {r.wonCount > 0 ? fmt.money(r.avgDeal, true) : '—'}
); } // === Showrooms === function ShowroomReport({ contacts, deals, showrooms }) { const agg = showrooms.map(s => { const sContacts = contacts.filter(c => c.homeShowroom === s.id); const sDeals = deals.filter(d => d.showroom === s.id); const sold = sDeals.filter(d => d.stage === 'sold'); const wonVal = sold.reduce((sm, d) => sm + d.value, 0); return { ...s, contactCount: sContacts.length, newLeads: sContacts.filter(c => c.lifecycle === 'new_lead').length, qualified: sContacts.filter(c => c.lifecycle === 'qualified').length, inPipeline: sContacts.filter(c => c.lifecycle === 'in_pipeline').length, customers: sContacts.filter(c => ['past_customer','customer_active','dormant_past_customer'].includes(c.lifecycle)).length, dormant: sContacts.filter(c => c.lifecycle.startsWith('dormant_')).length, dealCount: sDeals.length, wonVal, wonCount: sold.length, }; }); const maxContacts = Math.max(...agg.map(s => s.contactCount), 1); const maxWon = Math.max(...agg.map(s => s.wonVal), 1); return (
Contacts by showroom
Total leads + active customers
{agg.map(s => (
{s.name}
{s.contactCount / maxContacts > 0.2 && {s.contactCount}}
{s.customers} customers
))}
Revenue by showroom
Closed-won value · period
{agg.map(s => (
{s.name}
{s.wonVal / maxWon > 0.2 && {fmt.money(s.wonVal, true)}}
{s.wonCount} wins
))}
Showroom comparison
All metrics side-by-side
{agg.map(s => ( ))}
Showroom Contacts New leads Qualified In pipeline Customers Dormant Deals Won $
{s.name} {s.state} {s.contactCount} {s.newLeads} {s.qualified} {s.inPipeline} {s.customers} {s.dormant} {s.dealCount} {fmt.money(s.wonVal, true)}
); } // === Activity report === function ActivityReport({ contacts, deals, reps }) { // Count activities by type let calls = 0, emails = 0, meetings = 0, notes = 0; for (const d of deals) { for (const a of (d.activities || [])) { if (a.type === 'call') calls++; else if (a.type === 'email') emails++; else if (a.type === 'meeting') meetings++; else notes++; } } const total = calls + emails + meetings + notes; // Per rep const perRep = reps.map(r => { const rDeals = deals.filter(d => d.rep === r.id); let c = 0, e = 0, m = 0, n = 0; for (const d of rDeals) { for (const a of (d.activities || [])) { if (a.type === 'call') c++; else if (a.type === 'email') e++; else if (a.type === 'meeting') m++; else n++; } } return { ...r, calls: c, emails: e, meetings: m, notes: n, total: c + e + m + n }; }).sort((a, b) => b.total - a.total); if (total === 0) { return (
📋
No activity data recorded yet
Activities (calls, emails, meetings, notes) are logged when your team updates deals. Once logged, they'll appear here broken down by designer and type.
); } return (
Activity by designer
Volume of customer touches per rep
{perRep.map(r => { const max = Math.max(...perRep.map(x => x.total), 1); return ( ); })}
Designer Total Calls Emails Meetings Notes Distribution
{r.name}
{r.total} {r.calls} {r.emails} {r.meetings} {r.notes}
Calls Emails Meetings Notes
); } function sourceColor(src) { const map = { 'Referral': 'oklch(0.5 0.12 145)', 'Website': 'oklch(0.5 0.1 240)', 'Google': 'oklch(0.55 0.13 30)', 'Facebook': 'oklch(0.45 0.12 260)', 'Driving by': 'oklch(0.55 0.1 80)', 'BayIsland': 'oklch(0.55 0.12 200)', 'Newsletter': 'oklch(0.5 0.05 90)', 'Other': 'oklch(0.55 0.02 90)', }; return map[src] || 'oklch(0.55 0.05 200)'; } // ============================================================================ // Export engine — report data models + CSV / Excel / PDF builders // ============================================================================ const _CUSTOMER_LC = ['past_customer', 'customer_active', 'dormant_past_customer']; const _QUALIFIED_LC = ['qualified', 'in_pipeline', 'customer_active', 'past_customer', 'lost_quote']; function buildReportSections(key, ctx) { switch (key) { case 'marketing': return _mktSections(ctx); case 'conversion': return _convSections(ctx); case 'sales': return _salesSections(ctx); case 'showrooms': return _showroomSections(ctx); case 'activity': return _activitySections(ctx); case 'lost': return _lostSections(ctx); default: return []; } } function _mktSections({ contacts, showrooms }) { const srcAgg = {}; contacts.forEach(c => { const s = c.primarySource || 'Not recorded'; if (!srcAgg[s]) srcAgg[s] = { contacts: 0, sold: 0, value: 0 }; srcAgg[s].contacts++; if (_CUSTOMER_LC.includes(c.lifecycle)) { srcAgg[s].sold++; srcAgg[s].value += c.ltv || 0; } }); const sourceRows = Object.entries(srcAgg).sort((a, b) => b[1].contacts - a[1].contacts) .map(([source, v]) => ({ source, contacts: v.contacts, sold: v.sold, conv: v.contacts ? v.sold / v.contacts : 0, revenue: v.value })); const meansAgg = {}; contacts.forEach(c => { const m = c.primaryMeans || 'Not recorded'; if (!meansAgg[m]) meansAgg[m] = { contacts: 0, sold: 0 }; meansAgg[m].contacts++; if (_CUSTOMER_LC.includes(c.lifecycle)) meansAgg[m].sold++; }); const meansRows = Object.entries(meansAgg).sort((a, b) => b[1].contacts - a[1].contacts) .map(([means, v]) => ({ means, contacts: v.contacts, conv: v.contacts ? v.sold / v.contacts : 0 })); const subAgg = {}; contacts.forEach(c => { const su = (c.suburb || 'Not recorded').trim(); if (!subAgg[su]) subAgg[su] = { contacts: 0, sold: 0, value: 0, sh: {} }; subAgg[su].contacts++; if (_CUSTOMER_LC.includes(c.lifecycle)) { subAgg[su].sold++; subAgg[su].value += c.ltv || 0; } subAgg[su].sh[c.homeShowroom] = (subAgg[su].sh[c.homeShowroom] || 0) + 1; }); const suburbRows = Object.entries(subAgg).filter(([s]) => s !== 'Not recorded') .sort((a, b) => b[1].contacts - a[1].contacts) .map(([suburb, v], i) => { const closestId = Object.entries(v.sh).sort((a, b) => b[1] - a[1])[0]?.[0]; return { rank: i + 1, suburb, showroom: showrooms.find(s => s.id === closestId)?.name || '', contacts: v.contacts, sold: v.sold, conv: v.contacts ? v.sold / v.contacts : 0, revenue: v.value }; }); return [ { title: 'Lead source performance', columns: [ { key: 'source', label: 'Source', type: 'text' }, { key: 'contacts', label: 'Contacts', type: 'num' }, { key: 'sold', label: 'Sold', type: 'num' }, { key: 'conv', label: 'Conversion rate', type: 'pct' }, { key: 'revenue', label: 'Revenue (AUD)', type: 'money' }, ], rows: sourceRows }, { title: 'First-contact channel', columns: [ { key: 'means', label: 'Channel', type: 'text' }, { key: 'contacts', label: 'Contacts', type: 'num' }, { key: 'conv', label: 'Conversion rate', type: 'pct' }, ], rows: meansRows }, { title: 'Suburbs by contact volume', columns: [ { key: 'rank', label: '#', type: 'num' }, { key: 'suburb', label: 'Suburb', type: 'text' }, { key: 'showroom', label: 'Closest showroom', type: 'text' }, { key: 'contacts', label: 'Contacts', type: 'num' }, { key: 'sold', label: 'Sold', type: 'num' }, { key: 'conv', label: 'Conversion rate', type: 'pct' }, { key: 'revenue', label: 'Revenue (AUD)', type: 'money' }, ], rows: suburbRows }, ]; } function _convSections({ contacts, deals }) { const total = contacts.length; const qualified = contacts.filter(c => _QUALIFIED_LC.includes(c.lifecycle)).length; const quoted = contacts.filter(c => (c.dealCount || 0) > 0 || ['in_pipeline','customer_active','past_customer','dormant_customer_active','dormant_past_customer'].includes(c.lifecycle)).length; const sold = contacts.filter(c => _CUSTOMER_LC.includes(c.lifecycle)).length; const funnel = [ { stage: 'Total contacts', count: total, prev: null }, { stage: 'Qualified', count: qualified, prev: total }, { stage: 'Quoted', count: quoted, prev: qualified }, { stage: 'Sold', count: sold, prev: quoted }, ].map(f => ({ stage: f.stage, count: f.count, ofTotal: total ? f.count / total : 0, stepConv: f.prev == null ? null : (f.prev ? f.count / f.prev : 0) })); const stageMap = { possible: 'Possible', in_progress: 'In progress', on_hold: 'On hold', sold: 'Sold', lost: 'Lost' }; const dist = {}; deals.forEach(d => { if (!dist[d.stage]) dist[d.stage] = { count: 0, value: 0 }; dist[d.stage].count++; dist[d.stage].value += d.value || 0; }); const distRows = Object.keys(stageMap).filter(k => dist[k]).map(k => ({ stage: stageMap[k], count: dist[k].count, value: dist[k].value })); return [ { title: 'Conversion funnel', columns: [ { key: 'stage', label: 'Stage', type: 'text' }, { key: 'count', label: 'Count', type: 'num' }, { key: 'ofTotal', label: '% of contacts', type: 'pct' }, { key: 'stepConv', label: 'Step conversion', type: 'pct' }, ], rows: funnel }, { title: 'Deal stage distribution', columns: [ { key: 'stage', label: 'Stage', type: 'text' }, { key: 'count', label: 'Deals', type: 'num' }, { key: 'value', label: 'Value (AUD)', type: 'money' }, ], rows: distRows }, ]; } function _salesSections({ contacts, deals, reps }) { const agg = reps.map(r => { const rd = deals.filter(d => d.rep === r.id); const rc = contacts.filter(c => c.owner === r.name); const won = rd.filter(d => d.stage === 'sold'); const lost = rd.filter(d => d.stage === 'lost'); const active = rd.filter(d => d.stage !== 'sold' && d.stage !== 'lost'); const wonVal = won.reduce((s, d) => s + d.value, 0); const closed = won.length + lost.length; return { name: r.name, contacts: rc.length, active: active.length, activeVal: active.reduce((s, d) => s + d.value, 0), wins: won.length, wonVal, lost: lost.length, winRate: closed ? won.length / closed : 0, avgDeal: won.length ? wonVal / won.length : 0, }; }).sort((a, b) => b.wonVal - a.wonVal); return [{ title: 'Designer performance', columns: [ { key: 'name', label: 'Designer', type: 'text' }, { key: 'contacts', label: 'Contacts owned', type: 'num' }, { key: 'active', label: 'Active quotes', type: 'num' }, { key: 'activeVal', label: 'Active value (AUD)', type: 'money' }, { key: 'wins', label: 'Wins', type: 'num' }, { key: 'wonVal', label: 'Closed-won (AUD)', type: 'money' }, { key: 'lost', label: 'Lost', type: 'num' }, { key: 'winRate', label: 'Win rate', type: 'pct' }, { key: 'avgDeal', label: 'Avg deal (AUD)', type: 'money' }, ], rows: agg }]; } function _showroomSections({ contacts, deals, showrooms }) { const rows = showrooms.map(s => { const sc = contacts.filter(c => c.homeShowroom === s.id); const sd = deals.filter(d => d.showroom === s.id); const won = sd.filter(d => d.stage === 'sold'); return { showroom: s.name, state: s.state, contacts: sc.length, newLeads: sc.filter(c => c.lifecycle === 'new_lead').length, qualified: sc.filter(c => c.lifecycle === 'qualified').length, inPipeline: sc.filter(c => c.lifecycle === 'in_pipeline').length, customers: sc.filter(c => _CUSTOMER_LC.includes(c.lifecycle)).length, dormant: sc.filter(c => (c.lifecycle || '').startsWith('dormant_')).length, deals: sd.length, wonVal: won.reduce((m, d) => m + d.value, 0), }; }); return [{ title: 'Showroom comparison', columns: [ { key: 'showroom', label: 'Showroom', type: 'text' }, { key: 'state', label: 'State', type: 'text' }, { key: 'contacts', label: 'Contacts', type: 'num' }, { key: 'newLeads', label: 'New leads', type: 'num' }, { key: 'qualified', label: 'Qualified', type: 'num' }, { key: 'inPipeline', label: 'In pipeline', type: 'num' }, { key: 'customers', label: 'Customers', type: 'num' }, { key: 'dormant', label: 'Dormant', type: 'num' }, { key: 'deals', label: 'Deals', type: 'num' }, { key: 'wonVal', label: 'Won (AUD)', type: 'money' }, ], rows }]; } function _activitySections({ deals, reps }) { let calls = 0, emails = 0, meetings = 0, notes = 0; deals.forEach(d => (d.activities || []).forEach(a => { if (a.type === 'call') calls++; else if (a.type === 'email') emails++; else if (a.type === 'meeting') meetings++; else notes++; })); const total = calls + emails + meetings + notes; const perRep = reps.map(r => { const rd = deals.filter(d => d.rep === r.id); let c = 0, e = 0, m = 0, n = 0; rd.forEach(d => (d.activities || []).forEach(a => { if (a.type === 'call') c++; else if (a.type === 'email') e++; else if (a.type === 'meeting') m++; else n++; })); return { name: r.name, total: c + e + m + n, calls: c, emails: e, meetings: m, notes: n }; }).sort((a, b) => b.total - a.total); const summary = [ { type: 'Phone calls', count: calls, share: total ? calls / total : 0 }, { type: 'Emails', count: emails, share: total ? emails / total : 0 }, { type: 'Meetings & visits', count: meetings, share: total ? meetings / total : 0 }, { type: 'Notes', count: notes, share: total ? notes / total : 0 }, ]; return [ { title: 'Activity by designer', columns: [ { key: 'name', label: 'Designer', type: 'text' }, { key: 'total', label: 'Total', type: 'num' }, { key: 'calls', label: 'Calls', type: 'num' }, { key: 'emails', label: 'Emails', type: 'num' }, { key: 'meetings', label: 'Meetings', type: 'num' }, { key: 'notes', label: 'Notes', type: 'num' }, ], rows: perRep }, { title: 'Activity summary', columns: [ { key: 'type', label: 'Activity type', type: 'text' }, { key: 'count', label: 'Count', type: 'num' }, { key: 'share', label: 'Share of total', type: 'pct' }, ], rows: summary }, ]; } // --- Cell formatting per output target --- function _cell(val, type, target) { if (val == null || val === '') return target === 'pdf' ? '—' : ''; if (type === 'money') return target === 'pdf' ? fmt.money(val) : (typeof val === 'number' ? Math.round(val) : val); if (type === 'pct') return (val * 100).toFixed(0) + '%'; return val; } function _downloadFile(filename, content, mime) { const blob = new Blob([content], { type: mime }); const url = URL.createObjectURL(blob); const a = document.createElement('a'); a.href = url; a.download = filename; document.body.appendChild(a); a.click(); setTimeout(() => { document.body.removeChild(a); URL.revokeObjectURL(url); }, 100); } // --- CSV --- function _csvEsc(v) { if (v == null) return ''; const s = String(v); return /[",\n]/.test(s) ? '"' + s.replace(/"/g, '""') + '"' : s; } function reportsToCSV(reports, meta) { const lines = []; lines.push(_csvEsc('Pearler report export')); lines.push(_csvEsc(`Scope: ${meta.showroomLabel} | ${meta.repLabel} | ${meta.periodLabel}`)); lines.push(_csvEsc('Generated: ' + meta.generatedStr)); reports.forEach(rep => rep.sections.forEach(sec => { lines.push(''); lines.push(_csvEsc(rep.label + ' — ' + sec.title)); lines.push(sec.columns.map(c => _csvEsc(c.label)).join(',')); sec.rows.forEach(row => lines.push(sec.columns.map(c => _csvEsc(_cell(row[c.key], c.type, 'csv'))).join(','))); })); return '\uFEFF' + lines.join('\r\n'); } // --- Excel (.xls — styled HTML workbook Excel opens cleanly) --- function _xesc(s) { return String(s == null ? '' : s).replace(/&/g, '&').replace(//g, '>'); } function reportsToExcel(reports, meta) { let body = ''; body += `

Pearler report export

`; body += `
Scope: ${_xesc(meta.showroomLabel)}  |  ${_xesc(meta.repLabel)}  |  ${_xesc(meta.periodLabel)}
Generated ${_xesc(meta.generatedStr)}
`; reports.forEach(rep => rep.sections.forEach(sec => { body += `
${_xesc(rep.label)} — ${_xesc(sec.title)}
`; body += ''; body += '' + sec.columns.map(c => ``).join('') + ''; sec.rows.forEach(row => { body += '' + sec.columns.map(c => { const v = _cell(row[c.key], c.type, 'excel'); return ``; }).join('') + ''; }); body += '
${_xesc(c.label)}
${_xesc(v)}
'; })); return `${body}`; } // --- PDF / Print (clean, print-ready document) --- function reportsToPrintDoc(reports, meta) { let body = ''; reports.forEach((rep, ri) => { body += `
0 ? 'style="page-break-before:always"' : ''}>`; body += `

${_xesc(rep.label)} report

`; rep.sections.forEach(sec => { body += `

${_xesc(sec.title)}

`; body += '' + sec.columns.map(c => ``).join('') + ''; if (!sec.rows.length) body += ``; sec.rows.forEach(row => { body += '' + sec.columns.map(c => ``).join('') + ''; }); body += '
${_xesc(c.label)}
No data in this range
${_xesc(_cell(row[c.key], c.type, 'pdf'))}
'; }); body += '
'; }); return `Pearler reports
Pearler Reports
${_xesc(meta.showroomLabel)}  ·  ${_xesc(meta.repLabel)}  ·  ${_xesc(meta.periodLabel)}  ·  Generated ${_xesc(meta.generatedStr)}
${body}
Pearler CRM · Confidential · ${_xesc(meta.generatedStr)}
`; } function openPrintDoc(html) { const w = window.open('', '_blank'); if (!w) { alert('Please allow pop-ups to open the print / PDF view.'); return; } w.document.open(); w.document.write(html); w.document.close(); w.focus(); const go = () => { try { w.print(); } catch (e) {} }; if (w.document.readyState === 'complete') setTimeout(go, 350); else w.onload = () => setTimeout(go, 350); } // ============================================================================ // Export dialog — set parameters (showroom / designer / date) + pick reports + format // ============================================================================ function ReportExportModal({ data, currentUser, defaultTab, initFormat, init, onClose }) { const T = window.T || ((k) => k); const isSales = currentUser.role === 'sales_designer'; const [showroom, setShowroom] = React.useState(init.showroom || 'all'); const [rep, setRep] = React.useState(init.rep || 'all'); const [period, setPeriod] = React.useState(init.period || '12mo'); const [customFrom, setCustomFrom] = React.useState(init.customFrom || ''); const [customTo, setCustomTo] = React.useState(init.customTo || ''); const [keys, setKeys] = React.useState(() => new Set([defaultTab])); const [format, setFormat] = React.useState(initFormat || 'excel'); React.useEffect(() => { const onKey = (e) => { if (e.key === 'Escape') onClose(); }; window.addEventListener('keydown', onKey); return () => window.removeEventListener('keydown', onKey); }, [onClose]); // Recompute scope live from the dialog's own parameters const scope = computeReportScope({ ...data, currentUser, showroom, rep, period, customFrom, customTo }); const meta = buildReportMeta({ ...data, currentUser, showroom, effRep: scope.effRep, period, customFrom, customTo, periodContacts: scope.periodContacts, periodDeals: scope.periodDeals }); const showroomOptions = isSales ? data.showrooms.filter(s => currentUser.works.includes(s.id)) : data.showrooms; const toggle = (k) => setKeys(prev => { const n = new Set(prev); n.has(k) ? n.delete(k) : n.add(k); return n; }); const allOn = REPORT_DEFS.every(d => keys.has(d.key)); const toggleAll = () => setKeys(allOn ? new Set() : new Set(REPORT_DEFS.map(d => d.key))); const formats = [ { id: 'excel', label: 'Excel', sub: '.xls · formatted tables', icon: 'table' }, { id: 'csv', label: 'CSV', sub: '.csv · raw data', icon: 'download' }, { id: 'pdf', label: 'PDF / Print', sub: 'print-ready document', icon: 'book' }, ]; const doExport = () => { const reportKeys = REPORT_DEFS.filter(d => keys.has(d.key)).map(d => d.key); if (!reportKeys.length) return; const exportCtx = { contacts: scope.periodContacts, deals: scope.periodDeals, reps: scope.scopeReps, showrooms: scope.scopeShowrooms }; const reports = reportKeys.map(k => ({ key: k, label: (REPORT_DEFS.find(d => d.key === k) || {}).label || k, sections: buildReportSections(k, exportCtx) })); const stamp = new Date().toISOString().slice(0, 10); const base = (reportKeys.length === 1 ? reportKeys[0] : 'pearler-reports') + '-' + stamp; if (format === 'csv') _downloadFile(base + '.csv', reportsToCSV(reports, meta), 'text/csv;charset=utf-8;'); else if (format === 'excel') _downloadFile(base + '.xls', reportsToExcel(reports, meta), 'application/vnd.ms-excel'); else openPrintDoc(reportsToPrintDoc(reports, meta)); onClose(); }; const ctlLabel = { fontSize: 11, color: 'var(--ink-3)', display: 'block', marginBottom: 4 }; return (
{format === 'pdf' ? 'Print / export reports' : 'Export reports'}
Set parameters, choose reports and a format
{/* Parameters */}
Parameters
{isSales && showroomOptions.length <= 1 ? ( {showroomOptions[0]?.name || '—'} ) : ( )}
{!isSales && (
)}
{period === 'custom' && (
setCustomFrom(e.target.value)}/>
setCustomTo(e.target.value)}/>
)}
In scope: {meta.contacts} contacts ·{' '} {meta.deals} deals
{/* Reports */}
Reports to include
{REPORT_DEFS.map(d => { const on = keys.has(d.key); return ( ); })}
{/* Format */}
Format
{formats.map(f => { const on = format === f.id; return ( ); })}
{keys.size} report{keys.size !== 1 ? 's' : ''} · {format === 'pdf' ? 'PDF' : format.toUpperCase()}
); } // === Lost jobs: loss rate + top reasons (respects the report parameters) === // Most historical lost deals carry no structured reason, so derive one from notes/activity text. window.deriveLossReason = function (d) { if (d.lossReason) return d.lossReason; const txt = ((d.notes || '') + ' ' + (d.activities || []).map(a => a.label || '').join(' ')).toLowerCase(); if (!txt.trim()) return 'Not recorded'; const has = (...ws) => ws.some(w => txt.includes(w)); if (has('cheaper', 'competitor', 'another company', 'local cabinet', 'went with', 'better price', 'quote elsewhere', 'someone else')) return 'Went with a competitor'; if (has('too expensive', 'expensive', 'price', 'cost too', 'over budget', 'out of budget', 'pricey', 'cheaper option')) return 'Too expensive / price'; if (has('cancel', 'not proceeding', 'not going ahead', 'no longer', 'pulled out', 'sold the house', 'sold their house', 'not moving forward')) return 'Project cancelled'; if (has('no response', 'no reply', 'went cold', 'not responding', 'no pick up', 'unable to contact', 'ghosted', 'no answer', 'stopped responding', 'no contact', 'unreachable')) return 'Not responding / went cold'; if (has('budget', 'funds', 'finance', 'afford', 'loan', 'money')) return 'Budget fell through'; if (has('changed their mind', 'changed mind', 'decided not', 'changed her mind', 'changed his mind', 'reconsider')) return 'Changed their mind'; if (has('design', 'not happy', "didn't like", 'did not like', 'layout', 'unhappy')) return 'Not happy with the design'; if (has('timeframe', 'time frame', 'delay', 'next year', 'on hold', 'waiting', 'timing', 'postpone', 'builder', 'not ready')) return 'Timeframe didn’t suit'; return 'Other'; }; function _lostReasonRows(deals) { const lost = deals.filter(d => d.stage === 'lost'); const agg = {}; lost.forEach(d => { const r = window.deriveLossReason(d); if (!agg[r]) agg[r] = { count: 0, value: 0 }; agg[r].count++; agg[r].value += d.value || 0; }); const rows = Object.entries(agg) .map(([reason, v]) => ({ reason, count: v.count, value: v.value, share: lost.length ? v.count / lost.length : 0, avg: v.count ? v.value / v.count : 0 })) .sort((a, b) => b.count - a.count); return { lost, rows }; } function _lostSections({ deals }) { const { rows } = _lostReasonRows(deals); return [{ title: 'Reasons for lost jobs', columns: [ { key: 'reason', label: 'Reason', type: 'text' }, { key: 'count', label: 'Lost jobs', type: 'num' }, { key: 'share', label: 'Share of losses', type: 'pct' }, { key: 'value', label: 'Value lost (AUD)', type: 'money' }, ], rows, }]; } function LostReport({ deals, reps, showrooms, openDeal }) { const [drill, setDrill] = React.useState(null); // {type:'reason'|'rep', value, label} const lost = deals.filter(d => d.stage === 'lost'); const sold = deals.filter(d => d.stage === 'sold'); const closed = lost.length + sold.length; const lostRate = closed ? lost.length / closed : 0; const lostValue = lost.reduce((s, d) => s + (d.value || 0), 0); const storeName = (id) => (showrooms || []).find(s => s.id === id)?.name || id; // Cross-filter: each chart reflects the OTHER active selection so reason & designer move together const reasonBase = (drill && drill.type === 'rep') ? lost.filter(d => d.rep === drill.value) : lost; const repBase = (drill && drill.type === 'reason') ? lost.filter(d => window.deriveLossReason(d) === drill.value) : lost; const reasonRowsOf = (arr) => { const agg = {}; arr.forEach(d => { const r = window.deriveLossReason(d); if (!agg[r]) agg[r] = { count: 0, value: 0 }; agg[r].count++; agg[r].value += d.value || 0; }); return Object.entries(agg).map(([reason, v]) => ({ reason, count: v.count, value: v.value, share: arr.length ? v.count / arr.length : 0 })).sort((a, b) => b.count - a.count); }; const rows = reasonRowsOf(reasonBase); const top5 = rows.slice(0, 5); const maxCount = top5[0]?.count || 1; // Lost jobs by designer (reflects a selected reason) const byRep = (reps || []).map(r => ({ id: r.id, name: r.name, count: repBase.filter(d => d.rep === r.id).length })) .filter(r => r.count > 0).sort((a, b) => b.count - a.count).slice(0, 8); const maxRep = byRep[0]?.count || 1; // Time from lead created → loss (compute from the SAME dates shown in the row) const daysToLoss = (d) => { if (d.created && d.lastActivity) { const diff = Math.round((new Date(d.lastActivity) - new Date(d.created)) / 86400000); if (!isNaN(diff)) return Math.max(0, diff); } if (d.createdDaysAgo != null && d.lastActivityDaysAgo != null) return Math.max(0, d.createdDaysAgo - d.lastActivityDaysAgo); return null; }; const lostDate = (d) => d.lastActivity || d.expectedClose || ''; const fullDate = (s) => { if (!s) return '—'; const dt = new Date(s); return isNaN(dt) ? s : dt.toLocaleDateString('en-AU', { day: 'numeric', month: 'short', year: 'numeric' }); }; if (lost.length === 0) { return
No lost jobs in this scope. Adjust the parameters above.
; } // Jobs filtered by the active drill selection let jobs = lost; if (drill && drill.type === 'reason') jobs = lost.filter(d => window.deriveLossReason(d) === drill.value); else if (drill && drill.type === 'rep') jobs = lost.filter(d => d.rep === drill.value); jobs = jobs.slice().sort((a, b) => (daysToLoss(b) ?? 0) - (daysToLoss(a) ?? 0)); const maxDays = Math.max(...jobs.map(d => daysToLoss(d) || 0), 1); const avgDays = jobs.length ? Math.round(jobs.reduce((s, d) => s + (daysToLoss(d) || 0), 0) / jobs.length) : 0; const isReasonSel = (r) => drill && drill.type === 'reason' && drill.value === r; const pickReason = (r) => setDrill(isReasonSel(r) ? null : { type: 'reason', value: r, label: r }); const isRepSel = (id) => drill && drill.type === 'rep' && drill.value === id; const pickRep = (r) => setDrill(isRepSel(r.id) ? null : { type: 'rep', value: r.id, label: r.name }); return (
0.5}/>
Top 5 reasons for lost jobs
Click a reason to see the jobs & how long each took to lose
{top5.map((r, i) => ( ))}
All loss reasons
Click a row to drill in · {lost.length} lost jobs
{rows.map(r => ( pickReason(r.reason)} className={isReasonSel(r.reason) ? 'selected' : ''} style={{ cursor: 'pointer' }}> ))}
Reason Jobs % of losses Value lost
{r.reason}
{r.count} {fmt.pct(r.share, 0)} {r.value > 0 ? fmt.money(r.value, true) : '—'}
Lost jobs by designer
Click a designer to drill in
{byRep.length === 0 ?
No data.
: byRep.map(r => ( ))}
{/* Drill-down: the actual lost jobs + how long each took to lose */}
Lost jobs{drill ? ' · ' + drill.label : ''} ({jobs.length})
Time from lead created to loss{jobs.length ? ` · avg ${avgDays} days to loss` : ''}
{drill && }
{jobs.slice(0, 150).map(d => { const days = daysToLoss(d); const barColor = days == null ? 'var(--ink-4)' : days > 120 ? 'var(--lost)' : days > 45 ? 'var(--warn)' : 'var(--won)'; return ( openDeal && openDeal(d)} style={{ cursor: openDeal ? 'pointer' : 'default' }}> ); })}
Customer Designer {window.T ? window.T('location') : 'Showroom'} Reason Lead created Lost on Time to loss Value
{d.customer}
{d.quoteNumber || d.id}
 {d.repName} {storeName(d.showroom)} {window.deriveLossReason(d)} {fullDate(d.created)} {fullDate(lostDate(d))}
{days != null ? days + 'd' : '—'}
{d.value > 0 ? fmt.money(d.value, true) : '—'}
{jobs.length > 150 &&
Showing first 150 of {jobs.length} — narrow the parameters or pick a reason.
}
); } window.Reports = Reports;