SPB Git

Repository Public

RulesContent.swift 15.8 KB 312 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/RulesContent.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: Hardcoded constants inside formulas9293    /// Numeric literals of a formula that look like embedded inputs (display form, e.g. "1.15", "5%").94    func hardcodedConstants(_ expr: Expr) -> [String] {95        var out = [String]()96        func note(_ value: Double, _ display: String) {97            if !options.allowedConstants.contains(value), !out.contains(display) { out.append(display) }98        }99        func visit(_ e: Expr, structural: Bool) {100            switch e {101            case .number(let d): if !structural { note(d, Expr.number(d).formulaText()) }102            case .percent(.number(let d)): if !structural { note(d / 100, Expr.number(d).formulaText() + "%") }103            case .unary(.neg, .number(let d)): if !structural { note(-d, "-" + Expr.number(d).formulaText()) }104            case .unary(_, let x), .percent(let x), .paren(let x), .spill(let x), .implicit(let x): visit(x, structural: structural)105            case .binary(.pow, let a, let b):106                visit(a, structural: structural)107                // exponents (x^2, x^(1/12)) are part of the formula's structure108                visit(b, structural: true)109            case .binary(_, let a, let b): visit(a, structural: structural); visit(b, structural: structural)110            case .call(let name, let args):111                let s = options.functionsWithStructuralConstants.contains(name)112                for a in args {113                    // only literals passed directly are structural; nested calculations are judged on their own114                    switch a {115                    case .number, .percent(.number), .unary(.neg, .number): visit(a, structural: s)116                    default: visit(a, structural: false)117                    }118                }119            case .invoke(let f, let args): visit(f, structural: false); for a in args { visit(a, structural: false) }120            default: break       // array constants, text, references121            }122        }123        visit(expr, structural: false)124        return out125    }126127    mutating func ruleHardcodedConstants() {128        for si in wb.sheets.indices {129            for group in groupsByShape(si, where: { !hardcodedConstants($0.expr).isEmpty }) {130                let first = group[0]131                let constants = hardcodedConstants(first.expr)132                add(.hardcodedConstant, .info, sheet: si, cells: group.map(\.ref),133                    evidence: "\(first.ref.a1): \(text(first)) contains the constant\(constants.count == 1 ? "" : "s") \(constants.joined(separator: ", "))"134                        + (group.count > 1 ? " (same formula in \(group.count) cells: \(rangesText(group.map(\.ref))))" : "") + ".",135                    explanation: "A number typed inside a formula is an assumption that cannot be seen, labelled or changed in one place.",136                    action: "Move the value to a labelled input cell (or a defined name) and refer to it, unless it is a true constant of the calculation.")137            }138        }139    }140141    // MARK: Volatile functions142143    func volatileFunctions(_ expr: Expr) -> [String] {144        var out = [String]()145        expr.walk { node in146            if case .call(let n, _) = node, engine.registry.lookup(n)?.isVolatile == true, !out.contains(n) { out.append(n) }147        }148        return out149    }150151    mutating func ruleVolatile() {152        for si in wb.sheets.indices {153            var byFunction = [String: [CellRef]]()154            var wide = [(ModelAuditor.FormulaCell, [String], [String])]()155            for f in formulas[si] {156                let names = volatileFunctions(f.expr)157                guard !names.isEmpty else { continue }158                for n in names { byFunction[n, default: []].append(f.ref) }159                var whole = [String]()160                f.expr.walk { node in161                    if case .range(let r) = node, r.kind != .cells { whole.append(node.formulaText()) }162                }163                if !whole.isEmpty { wide.append((f, names, whole)) }164            }165            if options.rules.contains(.volatileFunction) {166                for name in byFunction.keys.sorted() {167                    let cells = byFunction[name]!168                    add(.volatileFunction, .info, sheet: si, cells: cells, key: name,169                        evidence: "\(name) is used in \(cells.count) cell\(cells.count == 1 ? "" : "s"): \(rangesText(cells)).",170                        explanation: "\(name) is volatile: it is recalculated — together with everything that depends on it — on every change anywhere in the workbook.",171                        action: "Keep volatile functions to a few input cells; replace OFFSET/INDIRECT by INDEX or direct references where possible.")172                }173            }174            if options.rules.contains(.volatileWholeColumn) {175                for (f, names, whole) in wide {176                    add(.volatileWholeColumn, .warning, sheet: si, cells: [f.ref],177                        evidence: "\(f.ref.a1): \(text(f)) combines \(names.joined(separator: ", ")) with the whole-row/column reference\(whole.count == 1 ? "" : "s") \(whole.joined(separator: ", ")).",178                        explanation: "The formula is recalculated on every change (volatile) and scans an entire row or column each time.",179                        action: "Limit the reference to the data range (or a table column) or remove the volatile function.")180                }181            }182        }183    }184185    // MARK: Complexity186187    mutating func ruleComplexity() {188        for si in wb.sheets.indices {189            func exceeds(_ c: ModelAuditor.Complexity) -> Bool {190                c.length > options.maxFormulaLength || c.nestingDepth > options.maxNestingDepth || c.score > options.maxComplexityScore191            }192            for group in groupsByShape(si, where: { exceeds(ModelAuditor.complexity(of: $0.expr)) }) {193                let first = group[0]194                let c = ModelAuditor.complexity(of: first.expr)195                let severe = c.length >= 2 * options.maxFormulaLength || c.nestingDepth >= options.maxNestingDepth + 3196                var shown = text(first)197                if shown.count > 120 { shown = String(shown.prefix(117)) + "…" }198                add(.complexFormula, severe ? .warning : .info, sheet: si, cells: group.map(\.ref),199                    evidence: "\(first.ref.a1): \(shown) — \(c.length) characters, nesting depth \(c.nestingDepth), \(c.functionCount) function calls, \(c.referenceCount) references, complexity score \(Value.generalString((c.score * 10).rounded() / 10))"200                        + (group.count > 1 ? " (\(group.count) cells)" : "") + ".",201                    explanation: "Long or deeply nested formulas are hard to read, test and maintain, and errors in them go unnoticed.",202                    action: "Split the calculation into intermediate cells, or use LET to name the steps.")203            }204        }205    }206207    // MARK: External workbook references208209    static func externalWorkbook(_ sheet: String?) -> String? {210        guard let s = sheet, let open = s.firstIndex(of: "["), let close = s.firstIndex(of: "]"), open < close else { return nil }211        return String(s[s.index(after: open)..<close])212    }213214    func externalWorkbooks(_ expr: Expr) -> [String] {215        var out = [String]()216        expr.walk { node in217            var s: String?218            switch node {219            case .cell(let sh, _): s = sh220            case .range(let r): s = r.sheet221            case .name(_, let sh): s = sh222            default: break223            }224            if let book = ModelAuditor.Scan.externalWorkbook(s), !out.contains(book) { out.append(book) }225        }226        return out227    }228229    mutating func ruleExternal() {230        for si in wb.sheets.indices {231            var byBook = [String: [ModelAuditor.FormulaCell]]()232            for f in formulas[si] { for b in externalWorkbooks(f.expr) { byBook[b, default: []].append(f) } }233            for book in byBook.keys.sorted() {234                let cells = byBook[book]!235                add(.externalReference, .warning, sheet: si, cells: cells.map(\.ref), key: book,236                    evidence: "\(cells.count) formula\(cells.count == 1 ? "" : "s") read\(cells.count == 1 ? "s" : "") from the external workbook \"\(book)\", e.g. \(cells[0].ref.a1): \(text(cells[0])).",237                    explanation: "Links to other workbooks break when files are moved or renamed, and their values cannot be recalculated or audited from here.",238                    action: "Import the data into this workbook (or a data connection) and refer to the local copy.")239            }240        }241        for dn in wb.names.values.sorted(by: { $0.name < $1.name }) {242            guard let e = try? FormulaParser.parse(dn.formula) else { continue }243            let books = externalWorkbooks(e)244            guard !books.isEmpty else { continue }245            add(.externalReference, .warning, sheet: nil, cells: [], key: "name:" + dn.name,246                evidence: "The defined name \(dn.name) (=\(dn.formula)) refers to the external workbook \"\(books[0])\".",247                explanation: "Links to other workbooks break when files are moved or renamed, and their values cannot be recalculated or audited from here.",248                action: "Point the name to cells of this workbook.")249        }250    }251252    // MARK: References to blank cells253254    /// Blank cells and wholly blank ranges a formula reads outside blank-tolerant functions.255    func blankReferences(_ f: ModelAuditor.FormulaCell, sheet si: Int) -> [String] {256        var out = [String]()257        func check(_ node: Expr) {258            for r in FormulaIntel.directRefs(node, workbook: wb, sheet: si) {259                let s = wb.sheets[r.sheet]260                if r.range.isSingleCell {261                    if !(s.cell(r.range.r0, r.range.c0)?.hasContent ?? false), !out.contains(r.text) { out.append(r.text) }262                } else if !r.range.isWholeColumns, !r.range.isWholeRows {263                    var any = false264                    s.cells.forEach(in: r.range) { _, _, c in if c.hasContent { any = true } }265                    if !any, !out.contains(r.text) { out.append(r.text) }266                }267            }268        }269        func visit(_ e: Expr) {270            switch e {271            case .cell, .range: check(e)272            case .call(let name, let args):273                if options.blankTolerantFunctions.contains(name) {274                    // conditions and tests may look at blanks; still inspect nested calculations275                    for a in args { if case .call = a { visit(a) } else if case .binary(let op, _, _) = a, [.add, .sub, .mul, .div, .pow].contains(op) { visit(a) } }276                } else { for a in args { visit(a) } }277            case .binary(let op, let a, let b):278                if op == .eq || op == .ne, a == .text("") || b == .text("") { return }       // explicit blank test279                if op == .range { return }280                visit(a); visit(b)281            case .unary(_, let x), .percent(let x), .paren(let x), .implicit(let x): visit(x)282            case .invoke(_, let args): for a in args { visit(a) }283            default: break284            }285        }286        visit(f.expr)287        return out288    }289290    mutating func ruleBlankReferences() {291        for si in wb.sheets.indices {292            var order = [String]()293            var groups = [String: [(ModelAuditor.FormulaCell, [String])]]()294            for f in formulas[si] {295                let blanks = blankReferences(f, sheet: si)296                guard !blanks.isEmpty else { continue }297                if groups[f.shape] == nil { order.append(f.shape) }298                groups[f.shape, default: []].append((f, blanks))299            }300            for shape in order {301                let g = groups[shape]!302                let (first, blanks) = g[0]303                add(.blankReference, .warning, sheet: si, cells: g.map(\.0.ref),304                    evidence: "\(first.ref.a1): \(text(first)) reads the empty cell\(blanks.count == 1 ? "" : "s") \(blanks.joined(separator: ", "))"305                        + (g.count > 1 ? " (\(g.count) cells with the same formula read blanks)" : "") + ".",306                    explanation: "An empty cell counts as 0 (or as empty text) in a calculation, so a missing input silently produces a plausible-looking result.",307                    action: "Fill in the missing input, correct the reference, or make the formula handle the blank case explicitly.")308            }309        }310    }311}312
Files main
Menu

Theme