SPB Git

Repository Public

RulesErrors.swift 13.2 KB 275 lines swift

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
Files main
Menu

Theme