Repository Public
1// =============================================================================2// SP BOUCHER | SOFTWARE & RESEARCH3// ARTIFICIAL INTELLIGENCE • FINANCE • ENGINEERING4// =============================================================================5//6// PROJECT METADATA7// -----------------------------------------------------------------------------8// Project : ORIX Sheet9// File : Packages/SheetAnalytics/Sources/Audit/Auditor/RulesErrors.swift10// Module : Audit11// Repository : https://git.spboucher.ai/orix-sheet12// Created : 2026-11-2113// Last Modified : 2026-11-2114//15// =============================================================================16// AUTHOR & PROFESSIONAL AFFILIATION17// =============================================================================18//19// Author : Simon-Pierre Boucher20// Position : Professor of Finance21// Institution : Université du Québec en Outaouais (UQO)22// Department : Département des sciences administratives23// Organization : SP Boucher24//25// Website : https://spboucher.ai26// Git : https://git.spboucher.ai27//28// =============================================================================29// OFFICIAL CONTACT DIRECTORY30// =============================================================================31//32// General : info@spboucher.ai33// Professional : contact@spboucher.ai34// Direct : simon@spboucher.ai35// Research : research@spboucher.ai36// Development : dev@spboucher.ai37// Support : support@spboucher.ai38// Security/Admin : admin@spboucher.ai39// Notifications : notifications@spboucher.ai40//41// Preferred Contact: support@spboucher.ai42//43// =============================================================================44// COPYRIGHT & INTELLECTUAL PROPERTY45// =============================================================================46//47// Copyright (c) 202648// Simon-Pierre Boucher49//50// All rights reserved where applicable.51//52// This file is governed by the applicable project license,53// copyright notices, institutional agreements and relevant law.54//55// Unless expressly authorized by the applicable license or56// rights holder, copying, redistribution, sublicensing,57// publication and commercial exploitation are not permitted.58//59// Existing third-party rights and notices remain unaffected.60//61// =============================================================================62// CONFIDENTIALITY & SECURITY63// =============================================================================64//65// Classification : Public66// Distribution : Public repository; use is governed by the project license67// Security Contact : admin@spboucher.ai68//69// Access to this source does not independently grant permission70// to redistribute or disclose confidential materials.71//72// Do not embed credentials, private keys, access tokens,73// certificates or confidential personal information.74//75// =============================================================================76// LICENSE & MAINTENANCE77// =============================================================================78//79// License : None specified (no license file in this repository)80// Maintainer : Simon-Pierre Boucher81// Technical Contact: dev@spboucher.ai82//83// =============================================================================84// END OF SP BOUCHER SOURCE HEADER85// =============================================================================8687import Foundation88import SheetCore8990extension ModelAuditor.Scan {91 // MARK: Error values and their origin9293 /// One finding per cell where an error is born (no precedent holds the same error), with the number94 /// of cells it propagates to. #CIRC! is left to the circular-reference rule.95 mutating func ruleErrorValues() {96 let explainer = ErrorExplainer(engine: engine)97 for (si, sheet) in wb.sheets.enumerated() {98 var errors = [(CellRef, CellError, Bool)]()99 sheet.cells.forEachAll { r, c, cell in100 guard !cell.isSpill, let e = cell.computed.errorValue, e != .circular else { return }101 errors.append((CellRef(r, c), e, cell.formula != nil))102 }103 for (ref, err, isFormula) in errors.sorted(by: { $0.0 < $1.0 }) {104 if isFormula {105 var inherited = false106 for a in engine.precedents(sheet: si, row: ref.row, col: ref.col) where !inherited && a.sheet < wb.sheets.count {107 wb.sheets[a.sheet].cells.forEach(in: a.range) { r, c, cell in108 if cell.computed.errorValue == err, !(a.sheet == si && r == ref.row && c == ref.col) { inherited = true }109 }110 }111 if inherited { continue }112 }113 // downstream cells showing the same error114 var affected = [(Int, CellRef)]()115 var seen: Set<UInt64> = [key(si, ref)]116 var queue = [(si, ref)]117 var head = 0118 while head < queue.count && affected.count < 5000 {119 let (s, c) = queue[head]; head += 1120 for d in engine.dependents(sheet: s, row: c.row, col: c.col) where seen.insert(key(d.sheet, d.cell)).inserted {121 guard wb.sheets[d.sheet].value(d.cell.row, d.cell.col).errorValue == err else { continue }122 affected.append((d.sheet, d.cell)); queue.append((d.sheet, d.cell))123 }124 }125 let ex = explainer.explain(sheet: si, cell: ref)126 var evidence = isFormula ? "=" + (sheet.cell(ref.row, ref.col)?.formula?.expr.formulaText() ?? "") + " → " + err.rawValue127 : "The cell contains the constant " + err.rawValue128 if !affected.isEmpty {129 let shown = affected.prefix(5).map { FormulaIntel.address(sheetName($0.0), $0.1) }.joined(separator: ", ")130 evidence += ". Propagates to \(affected.count) cell\(affected.count == 1 ? "" : "s"): " + shown + (affected.count > 5 ? ", …" : "")131 }132 var explanation = ErrorExplainer.meaning(err)133 if let cause = ex?.causes.first { explanation += " " + cause.detail }134 add(.errorValue, .error, sheet: si, cells: [ref], evidence: evidence, explanation: explanation, action: ErrorExplainer.suggestion(err))135 }136 }137 }138139 private func key(_ si: Int, _ c: CellRef) -> UInt64 { UInt64(si) << 34 | UInt64(c.row) << 14 | UInt64(c.col) }140141 // MARK: Broken references142143 /// What is broken in an expression: #REF! literals, missing sheets, tables or table columns.144 func brokenParts(_ e: Expr) -> [String] {145 var out = [String]()146 func note(_ s: String) { if !out.contains(s) { out.append(s) } }147 e.walk { node in148 switch node {149 case .error(.ref): note("#REF! literal")150 case .cell(let s?, _), .name(_, let s?):151 if !s.contains("["), wb.sheetIndex(named: s) == nil { note("missing sheet \"\(s)\"") }152 case .range(let r):153 if let s = r.sheet, !s.contains("["), wb.sheetIndex(named: s) == nil { note("missing sheet \"\(s)\"") }154 case .structured(let sr):155 guard let name = sr.table else { break }156 guard let (_, t) = wb.table(named: name) else { note("missing table \"\(name)\""); break }157 for col in [sr.colStart, sr.colEnd].compactMap({ $0 }) where t.columnIndex(col) == nil { note("missing column \"\(col)\" in table \(t.name)") }158 default: break159 }160 }161 return out162 }163164 mutating func ruleBrokenReferences() {165 for si in wb.sheets.indices {166 for group in groupsByShape(si, where: { !brokenParts($0.expr).isEmpty }) {167 let first = group[0]168 let parts = brokenParts(first.expr)169 add(.brokenReference, .error, sheet: si, cells: group.map(\.ref),170 evidence: "\(first.ref.a1): \(text(first)) — " + parts.joined(separator: ", ") + (group.count > 1 ? " (\(group.count) cells with the same formula)" : ""),171 explanation: "The formula refers to something that no longer exists, usually because a row, column, sheet or table was deleted after the formula was written.",172 action: "Re-point the reference to the intended cells, or remove the broken part of the formula.")173 }174 }175 }176177 // MARK: Circular references178179 mutating func ruleCircular() {180 // nodes: every formula cell (small models) or only the cells showing #CIRC!181 var total = 0182 for list in formulas { total += list.count }183 let full = total <= options.cycleScanFormulaLimit184 var nodes = [(Int, CellRef)]()185 var id = [UInt64: Int]()186 for (si, list) in formulas.enumerated() {187 for f in list where full || f.value.errorValue == .circular {188 id[key(si, f.ref)] = nodes.count189 nodes.append((si, f.ref))190 }191 }192 guard !nodes.isEmpty else { return }193 // edges: v → w when formula v reads formula w194 var adj = [[Int]](repeating: [], count: nodes.count)195 var budget = 4_000_000196 for (v, (si, ref)) in nodes.enumerated() {197 for a in engine.precedents(sheet: si, row: ref.row, col: ref.col) where a.sheet < wb.sheets.count {198 if a.range.isSingleCell {199 if let w = id[key(a.sheet, a.range.topLeft)] { adj[v].append(w) }200 } else {201 wb.sheets[a.sheet].cells.forEach(in: a.range) { r, c, cell in202 if cell.formula != nil, let w = id[key(a.sheet, CellRef(r, c))] { adj[v].append(w) }203 }204 }205 budget -= 1 + adj[v].count / 64206 }207 if budget < 0 && full { break }208 }209 // iterative Tarjan210 var indexOf = [Int](repeating: -1, count: nodes.count), low = [Int](repeating: 0, count: nodes.count)211 var onStack = [Bool](repeating: false, count: nodes.count)212 var stack = [Int](), counter = 0213 var components = [[Int]]()214 for s in 0..<nodes.count where indexOf[s] < 0 {215 var work = [(s, 0)]216 indexOf[s] = counter; low[s] = counter; counter += 1217 stack.append(s); onStack[s] = true218 while let (v, k) = work.last {219 if k < adj[v].count {220 work[work.count - 1].1 = k + 1221 let w = adj[v][k]222 if indexOf[w] < 0 {223 indexOf[w] = counter; low[w] = counter; counter += 1224 stack.append(w); onStack[w] = true225 work.append((w, 0))226 } else if onStack[w] {227 low[v] = min(low[v], indexOf[w])228 }229 } else {230 work.removeLast()231 if let (p, _) = work.last { low[p] = min(low[p], low[v]) }232 if low[v] == indexOf[v] {233 var comp = [Int]()234 while let w = stack.popLast() {235 onStack[w] = false236 comp.append(w)237 if w == v { break }238 }239 if comp.count > 1 || adj[v].contains(v) { components.append(comp) }240 }241 }242 }243 }244 for comp in components {245 let members = Set(comp)246 let start = comp.min { (nodes[$0].0, nodes[$0].1) < (nodes[$1].0, nodes[$1].1) }!247 // shortest cycle through `start` inside the component (BFS)248 var parent = [Int: Int]()249 var queue = [start]250 var head = 0251 var closing: Int?252 search: while head < queue.count {253 let v = queue[head]; head += 1254 for w in adj[v] where members.contains(w) {255 if w == start { closing = v; break search }256 if parent[w] == nil { parent[w] = v; queue.append(w) }257 }258 }259 var path = [start]260 if var v = closing {261 var back = [Int]()262 while v != start { back.append(v); v = parent[v] ?? start }263 path += back.reversed()264 }265 let si = nodes[start].0266 let names = (path + [start]).map { FormulaIntel.address(sheetName(nodes[$0].0), nodes[$0].1) }267 let all = comp.filter { nodes[$0].0 == si }.map { nodes[$0].1 }268 add(.circularReference, .error, sheet: si, cells: all,269 evidence: "Cycle: " + names.joined(separator: " → ") + (comp.count > path.count ? " (\(comp.count) cells are involved in total)" : ""),270 explanation: "Each of these formulas depends, directly or through the others, on its own result, so none of them can be calculated (they show #CIRC!).",271 action: "Break the loop: replace one link of the cycle by an input value or restructure the calculation so that data flows one way.")272 }273 }274}275