163 AND ((p.created_at_iso >= ? AND p.created_at_iso < ?) 164 OR (COALESCE(p.created_at_iso, '') = '' AND julianday(p.created_at) >= julianday(?) AND julianday(p.created_at) < julianday(?))) 165 ) SELECT COUNT(*) AS received, 166 SUM(status = 'nouveau') AS fresh, 167 SUM(status IN ('nouveau','en_cours','nrp','rappel')) AS active, 168 SUM(status NOT IN ('','nouveau')) AS handled, 169 SUM(status IN ('qualifie','qualifié','converti','converted','vente')) AS qualified, 170 SUM(status = 'nrp') AS nrp, 171 SUM(callback IS NOT NULL) AS callbacks, 172 SUM(callback <= julianday(?)) AS overdue_callbacks FROM cohort`, 173 params: [...scope.params, ...campaignScope.params, from, to, from, to, new Date().toISOString()], 174 }); 175 const row = rows(result)[0] || {}; 176 const received = number(row.received), qualified = number(row.qualified); 177 return { received, qualified, fresh: number(row.fresh), active: number(row.active), handled: number(row.handled), 178 nrp: number(row.nrp), callbacks: number(row.callbacks), overdueCallbacks: number(row.overdue_callbacks), 179 conversionRate: received ? qualified / received * 100 : 0 }; 180} 181 182async function labelledRead(label, read) { 183 try { 184 return await read(); 185 } catch (error) { 186 throw new Error(`dashboard_${label}: ${clean(error?.message || error)}`); 187 } 188} 189 190export function confirmedBusinessAnswerSql(alias = '') { 191 const column = (name) => alias ? `${alias}.${name}` : name; 192 return `( 193 lower(COALESCE(CAST(${column('handled_by')} AS TEXT), '')) != 'ivr' 194 AND 195 (COALESCE(CAST(${column('answered_at')} AS TEXT), '') != '' 196 OR COALESCE(CAST(${column('talk_duration_seconds')} AS REAL), 0) > 0) 197 AND ( 198 COALESCE(CAST(${column('commercial_id')} AS TEXT), '') != '' 199 OR lower(COALESCE(CAST(${column('handled_by')} AS TEXT), '')) IN ('ai', 'external') 200 ) 201 )`; 202} 203 204export function missedCallSql(alias = '') { 205 const col = name => alias ? `${alias}.${name}` : name; 206 return ['status', 'issue', 'motif'].map(name => 207 `lower(COALESCE(${col(name)}, '')) IN ('appel_manque', 'missed', 'manque', 'no_answer', 'no-answer')`).join(' OR '); 208} 209 210async function queryDashboardCallAndEnergyAggregates(context, from, to, dateFrom, dateTo, centreIds) { 211 const callScope = centreScope('a.centre_appel_id', centreIds); 212 const talkScope = centreScope('a.centre_appel_id', centreIds); 213 const saleScope = centreScope('v.centre_appel_id', centreIds); 214 const digits = (col) => `replace(replace(replace(replace(replace(replace(COALESCE(${col}, ''), '+', ''), ' ', ''), '-', ''), '.', ''), '(', ''), ')', '')`; 215 // Un appel sans campagne n'est pas forcément hors énergie : l'attribution 216 // peut manquer à l'ingestion (ligne IA Twilio, strays Ringover). On retombe 217 // alors sur la campagne du prospect, puis sur le numéro appelé rapproché des 218 // numéros des campagnes énergie. Ce qui reste inattribuable est compté à 219 // part (unattributed_answered) au lieu de dégonfler silencieusement le KPI. 220 // Les replis passent par des ensembles précalculés et bornés à la cohorte 221 // d'appels déjà filtrée. Scanner tous les prospects historiques pour un 222 // mois de Dashboard faisait dépasser le budget de lecture des gros projets. 223 const isEnergySql = `CASE ··· 449async function queryEnergyAggregatesFast(context, from, to, dateFrom, dateTo, centreIds) { 450 const digits = (col) => `replace(replace(replace(replace(replace(replace(COALESCE(${col}, ''), '+', ''), ' ', ''), '-', ''), '.', ''), '(', ''), ')', '')`; 451 const references = await tables(context, 'tables_query', { 452 sql: `WITH energy_campaigns AS ( 453 SELECT DISTINCT CAST(cp.id AS TEXT) AS id, CAST(cp.local_id AS TEXT) AS local_id, 454 cp.fallback_called_numbers, CAST(cp.forward_number AS TEXT) AS forward_number 455 FROM {campaigns} cp 456 LEFT JOIN json_each(CASE WHEN json_valid(cp.offres_proposees) THEN cp.offres_proposees ELSE '[]' END) proposed 457 LEFT JOIN {offres} o ON CAST(o.id AS TEXT) = CAST(proposed.value AS TEXT) OR CAST(o.local_id AS TEXT) = CAST(proposed.value AS TEXT) 458 WHERE lower(COALESCE(cp.nom, '') || ' ' || COALESCE(o.type_offre, '') || ' ' || COALESCE(o.nom, '') || ' ' || COALESCE(o.fournisseur, '') || ' ' || COALESCE(o.famille, '')) LIKE '%energie%' 459 OR lower(COALESCE(cp.nom, '') || ' ' || COALESCE(o.type_offre, '') || ' ' || COALESCE(o.nom, '') || ' ' || COALESCE(o.fournisseur, '') || ' ' || COALESCE(o.famille, '')) LIKE '%énergie%' 460 OR lower(COALESCE(cp.nom, '') || ' ' || COALESCE(o.type_offre, '') || ' ' || COALESCE(o.nom, '') || ' ' || COALESCE(o.fournisseur, '') || ' ' || COALESCE(o.famille, '')) LIKE '%elec%' 461 OR lower(COALESCE(cp.nom, '') || ' ' || COALESCE(o.type_offre, '') || ' ' || COALESCE(o.nom, '') || ' ' || COALESCE(o.fournisseur, '') || ' ' || COALESCE(o.famille, '')) LIKE '%gaz%' 462 ), campaign_refs AS ( 463 SELECT id AS value FROM energy_campaigns WHERE COALESCE(id, '') != '' 464 UNION SELECT local_id FROM energy_campaigns WHERE COALESCE(local_id, '') != '' 465 ), called_numbers AS ( 466 SELECT CAST(fallback.value AS TEXT) AS value 467 FROM energy_campaigns ec 468 JOIN json_each(CASE 469 WHEN json_valid(ec.fallback_called_numbers) AND json_type(ec.fallback_called_numbers) = 'array' THEN ec.fallback_called_numbers 470 WHEN json_valid(ec.fallback_called_numbers) AND json_type(ec.fallback_called_numbers) = 'text' 471 AND json_valid(json_extract(ec.fallback_called_numbers, '$')) 472 AND json_type(json_extract(ec.fallback_called_numbers, '$')) = 'array' THEN json_extract(ec.fallback_called_numbers, '$') 473 ELSE '[]' END) fallback 474 UNION SELECT forward_number FROM energy_campaigns WHERE COALESCE(forward_number, '') != '' 475 ) 476 SELECT 'campaign' AS kind, value FROM campaign_refs 477 UNION ALL 478 SELECT 'number', substr(${digits('value')}, -9) FROM called_numbers WHERE length(${digits('value')}) >= 8`, 479 params: [], 480 }); 481 const campaignRefs = [...new Set(rows(references).filter((row) => row.kind === 'campaign').map((row) => clean(row.value)).filter(Boolean))]; 482 const numberTails = [...new Set(rows(references).filter((row) => row.kind === 'number').map((row) => clean(row.value)).filter(Boolean))]; 483 const energyPredicate = `( 484 CAST(a.campaign_id AS TEXT) IN (SELECT CAST(value AS TEXT) FROM json_each(?)) 485 OR (COALESCE(CAST(a.campaign_id AS TEXT), '') = '' AND CAST(p.campaign_id AS TEXT) IN (SELECT CAST(value AS TEXT) FROM json_each(?))) 486 OR (COALESCE(CAST(a.campaign_id AS TEXT), '') = '' AND substr(${digits('a.to_number')}, -9) IN (SELECT CAST(value AS TEXT) FROM json_each(?))) 487 )`; 488 // Tables limits a query to 1,000 bound values. Staging has hundreds of 489 // historical campaign identifiers, so pass each lookup set as one JSON 490 // parameter and expand it inside SQLite instead of binding every value. 491 const predicateParams = [JSON.stringify(campaignRefs), JSON.stringify(campaignRefs), JSON.stringify(numberTails)]; 492 const callScope = centreScope('a.centre_appel_id', centreIds); 493 const saleScope = centreScope('v.centre_appel_id', centreIds); 494 const result = await tables(context, 'tables_query', { 495 // Both CTEs are reused below. Prevent SQLite from inlining the date scan 496 // into every correlated duplicate check and aggregate branch. 497 sql: `WITH classified_raw AS MATERIALIZED ( 498 SELECT a.id AS root_id, a.started_at AS event_at, 499 lower(COALESCE(a.provider, '')) AS provider, 500 lower(COALESCE(a.handled_by, '')) AS handled_by, 501 substr(${digits('a.from_number')}, -9) AS caller_tail, 502 substr(${digits('a.to_number')}, -9) AS destination_tail, 503 COALESCE(CAST(a.campaign_id AS TEXT), '') AS campaign_key, 504 COALESCE(CAST(a.commercial_id AS TEXT), '') AS commercial_id, 505 COALESCE(NULLIF(CAST(a.opportunity_key AS TEXT), ''), NULLIF(CAST(a.interaction_id AS TEXT), ''), 506 NULLIF(CAST(a.parent_appel_id AS TEXT), ''), NULLIF(CAST(a.raw_call_id AS TEXT), ''), 507 NULLIF(CAST(a.local_id AS TEXT), ''), CAST(a.id AS TEXT)) AS opportunity_key, 508 CAST(a.prospect_id AS TEXT) AS prospect_id, 509 CASE WHEN ${energyPredicate} THEN 1 ELSE 0 END AS is_energy, 510 CASE WHEN COALESCE(CAST(a.campaign_id AS TEXT), '') = '' THEN 1 ELSE 0 END AS no_campaign 511 FROM {appels} a LEFT JOIN {prospects} p 512 ON p.id = CAST(a.prospect_id AS INTEGER) AND CAST(p.id AS TEXT) = CAST(a.prospect_id AS TEXT) 513 WHERE a.started_at >= ? AND a.started_at < ? AND ${callScope.sql} 514 AND lower(COALESCE(a.direction, 'entrant')) IN ('entrant', 'inbound') 515 AND COALESCE(CAST(a.parent_appel_id AS TEXT), '') = '' 516 AND ${confirmedBusinessAnswerSql('a')} 517 UNION ALL 518 SELECT a.id AS root_id, a.created_at AS event_at, 519 lower(COALESCE(a.provider, '')) AS provider, 520 lower(COALESCE(a.handled_by, '')) AS handled_by, 521 substr(${digits('a.from_number')}, -9) AS caller_tail, 522 substr(${digits('a.to_number')}, -9) AS destination_tail, 523 COALESCE(CAST(a.campaign_id AS TEXT), '') AS campaign_key,