SPB Git

Repository Public

ModelAuditor.swift 12.4 KB 260 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/ModelAuditor.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 SheetCore8990/// Static analysis of a workbook: errors, fragile constructions and performance traps.91///92/// The auditor only reads the workbook. It recalculates pending formulas first (so values are93/// current) and uses `CalcEngine.evaluate` for profiling and error tracing; it never edits cells94/// and leaves the undo history untouched.95public enum ModelAuditor {96    /// Runs the enabled rules and returns the findings sorted by severity, sheet, rule and position.97    /// `engine` must be the calculation engine of `workbook`.98    public static func audit(workbook: Workbook, engine: CalcEngine, options: AuditOptions = AuditOptions()) -> AuditReport {99        let start = DispatchTime.now().uptimeNanoseconds100        if engine.hasPendingWork { engine.recalculate() }101        var scan = Scan(workbook: workbook, engine: engine, options: options)102        let on = options.rules103        if on.contains(.errorValue) { scan.ruleErrorValues() }104        if on.contains(.brokenReference) { scan.ruleBrokenReferences() }105        if on.contains(.circularReference) { scan.ruleCircular() }106        if on.contains(.inconsistentFormula) { scan.ruleInconsistent() }107        if on.contains(.hardcodedOverwrite) { scan.ruleHardcodedOverwrite() }108        if on.contains(.duplicatedCalculation) { scan.ruleDuplicated() }109        if on.contains(.hardcodedConstant) { scan.ruleHardcodedConstants() }110        if on.contains(.volatileFunction) || on.contains(.volatileWholeColumn) { scan.ruleVolatile() }111        if on.contains(.complexFormula) { scan.ruleComplexity() }112        if on.contains(.externalReference) { scan.ruleExternal() }113        if on.contains(.blankReference) { scan.ruleBlankReferences() }114        if on.contains(.textNumberInArithmetic) || on.contains(.numberStoredAsText) { scan.ruleTextNumbers() }115        if on.contains(.mixedTypesInTableColumn) { scan.ruleMixedTypes() }116        if on.contains(.textResultInNumericColumn) { scan.ruleTextResults() }117        if on.contains(.mergedCellsInData) { scan.ruleMerged() }118        if on.contains(.hiddenContent) { scan.ruleHidden() }119        if on.contains(.unusedName) || on.contains(.brokenName) { scan.ruleNames() }120        if on.contains(.slowFormula) { scan.ruleSlow() }121122        var stats = AuditReport.Statistics()123        stats.sheets = workbook.sheets.count124        stats.definedNames = workbook.names.count125        for (si, sheet) in workbook.sheets.enumerated() {126            sheet.cells.forEachAll { _, _, c in if c.hasContent { stats.cells += 1 } }127            stats.formulas += scan.formulas[si].count128        }129        let ruleOrder = Dictionary(uniqueKeysWithValues: AuditRule.allCases.enumerated().map { ($1, $0) })130        var findings = scan.findings.filter { on.contains($0.rule) }131        findings.sort { a, b in132            if a.severity != b.severity { return a.severity > b.severity }133            let sa = a.sheetIndex ?? Int.max, sb = b.sheetIndex ?? Int.max134            if sa != sb { return sa < sb }135            if a.rule != b.rule { return ruleOrder[a.rule]! < ruleOrder[b.rule]! }136            let ca = a.cells.first ?? a.ranges.first?.topLeft ?? CellRef(0, 0), cb = b.cells.first ?? b.ranges.first?.topLeft ?? CellRef(0, 0)137            if ca != cb { return ca < cb }138            return a.id < b.id139        }140        stats.seconds = Double(DispatchTime.now().uptimeNanoseconds - start) / 1e9141        return AuditReport(findings: findings, statistics: stats, rules: AuditRule.allCases.filter { on.contains($0) })142    }143144    /// Complexity metrics of one formula.145    public struct Complexity: Hashable, Sendable, Codable {146        /// Characters of the formula text (without "=").147        public var length: Int148        /// Nodes of the syntax tree.149        public var nodeCount: Int150        /// Deepest nesting of function calls (a call with no call in its arguments has depth 1).151        public var nestingDepth: Int152        public var functionCount: Int153        public var referenceCount: Int154        /// nodeCount + 5 × max(0, nestingDepth − 1) + length / 10.155        public var score: Double156    }157158    public static func complexity(of expr: Expr) -> Complexity {159        var nodes = 0, calls = 0, refs = 0160        expr.walk { e in161            nodes += 1162            switch e {163            case .call: calls += 1164            case .cell, .range, .structured, .name: refs += 1165            default: break166            }167        }168        func depth(_ e: Expr) -> Int {169            switch e {170            case .call(_, let args): return 1 + (args.map(depth).max() ?? 0)171            case .invoke(let f, let args): return 1 + max(depth(f), args.map(depth).max() ?? 0)172            case .unary(_, let x), .percent(let x), .paren(let x), .spill(let x), .implicit(let x): return depth(x)173            case .binary(_, let a, let b): return max(depth(a), depth(b))174            default: return 0175            }176        }177        let length = expr.formulaText().count178        let nest = depth(expr)179        return Complexity(length: length, nodeCount: nodes, nestingDepth: nest, functionCount: calls, referenceCount: refs,180                          score: Double(nodes) + 5 * Double(max(0, nest - 1)) + Double(length) / 10)181    }182183    // MARK: Scan state shared by the rules184185    struct FormulaCell {186        var row: Int187        var col: Int188        var expr: Expr189        var value: Value190        var shape: String191        var ref: CellRef { CellRef(row, col) }192    }193194    struct Scan {195        let wb: Workbook196        let engine: CalcEngine197        let options: AuditOptions198        /// Formula cells per sheet, row-major.199        var formulas: [[FormulaCell]] = []200        /// Position of each formula cell in `formulas[sheet]`.201        var index: [[CellRef: Int]] = []202        var findings: [AuditFinding] = []203        private var usedIDs = Set<String>()204205        init(workbook: Workbook, engine: CalcEngine, options: AuditOptions) {206            wb = workbook; self.engine = engine; self.options = options207            for sheet in workbook.sheets {208                var list = [FormulaCell]()209                sheet.cells.forEachAll { r, c, cell in210                    guard let f = cell.formula else { return }211                    list.append(FormulaCell(row: r, col: c, expr: f.expr, value: f.value, shape: ""))212                }213                list.sort { $0.ref < $1.ref }214                for i in list.indices { list[i].shape = FormulaIntel.tokens(list[i].expr, style: .r1c1(list[i].ref)).joined() }215                formulas.append(list)216                index.append(Dictionary(uniqueKeysWithValues: list.enumerated().map { ($1.ref, $0) }))217            }218        }219220        func sheetName(_ si: Int) -> String { wb.sheets[si].name }221        func text(_ f: FormulaCell) -> String { "=" + f.expr.formulaText() }222        func formula(_ si: Int, _ c: CellRef) -> FormulaCell? { index[si][c].map { formulas[si][$0] } }223224        /// Formula cells of a sheet grouped by identical R1C1 shape, groups ordered by their first cell.225        func groupsByShape(_ si: Int, where include: (FormulaCell) -> Bool) -> [[FormulaCell]] {226            var order = [String]()227            var groups = [String: [FormulaCell]]()228            for f in formulas[si] where include(f) {229                if groups[f.shape] == nil { order.append(f.shape) }230                groups[f.shape, default: []].append(f)231            }232            return order.map { groups[$0]! }233        }234235        mutating func add(_ rule: AuditRule, _ severity: AuditSeverity, sheet si: Int?, cells: [CellRef], ranges extra: [RangeRef] = [],236                          key: String? = nil, evidence: String, explanation: String, action: String) {237            let sorted = Array(Set(cells)).sorted()238            let ranges = FormulaIntel.compress(sorted) + extra.filter { r in !sorted.contains { r == RangeRef($0) } }239            let name = si.map(sheetName)240            let anchor = key ?? ranges.first?.topLeft.a1 ?? "-"241            var id = rule.rawValue + ":" + (name.map { $0 + "!" } ?? "") + anchor242            var n = 2243            while !usedIDs.insert(id).inserted { id = rule.rawValue + ":" + (name.map { $0 + "!" } ?? "") + anchor + "#\(n)"; n += 1 }244            findings.append(AuditFinding(id: id, rule: rule, severity: severity, sheet: name, sheetIndex: si,245                                         cells: Array(sorted.prefix(options.maxCellsPerFinding)), ranges: ranges,246                                         evidence: evidence, explanation: explanation, recommendedAction: action))247        }248249        /// "D2, D3, D7" (at most `limit` addresses, then "…").250        func list(_ cells: [CellRef], limit: Int = 6) -> String {251            let s = cells.sorted()252            return s.prefix(limit).map(\.a1).joined(separator: ", ") + (s.count > limit ? ", … (\(s.count) cells)" : "")253        }254        func rangesText(_ cells: [CellRef], limit: Int = 6) -> String {255            let r = FormulaIntel.compress(cells)256            return r.prefix(limit).map(\.a1).joined(separator: ", ") + (r.count > limit ? ", …" : "")257        }258    }259}260
Files main
Menu

Theme