SPB Git

Repository Public

RulesConsistency.swift 14.0 KB 274 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/RulesConsistency.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: Formulas inconsistent with their neighbours9293    private struct LineVerdict {94        /// The cell follows the dominant formula of its contiguous block (a run of at least `runLength`).95        var consistent = false96        /// The cell breaks such a run; the index of a neighbour that follows the pattern.97        var breaksWith: Int?98    }99100    /// Scans one axis. `major` selects the line (column for a vertical scan), `minor` the position on it.101    private func verdicts(_ si: Int, vertical: Bool) -> [LineVerdict] {102        let list = formulas[si]103        var out = [LineVerdict](repeating: LineVerdict(), count: list.count)104        var lines = [Int: [Int]]()105        for (i, f) in list.enumerated() { lines[vertical ? f.col : f.row, default: []].append(i) }106        for (_, members) in lines {107            let sorted = members.sorted { (vertical ? list[$0].row : list[$0].col) < (vertical ? list[$1].row : list[$1].col) }108            var block = [Int]()109            func flush() {110                defer { block.removeAll() }111                guard block.count >= options.runLength else { return }112                var counts = [String: Int]()113                for i in block { counts[list[i].shape, default: 0] += 1 }114                let ranked = counts.sorted { ($0.value, $1.key) > ($1.value, $0.key) }115                guard let top = ranked.first, top.value >= options.runLength else { return }116                if ranked.count > 1, ranked[1].value * 2 >= top.value { 117                    // no clear pattern (two formulas share the block): cells of either are not judged118                    for i in block where list[i].shape == top.key { out[i].consistent = true }119                    return120                }121                let model = block.first { list[$0].shape == top.key }!122                let blockRange = RangeRef(list[block.first!].ref, list[block.last!].ref)123                for i in block {124                    if list[i].shape == top.key { out[i].consistent = true; continue }125                    // totals and other aggregates over the run itself are not breaks126                    var aggregates = false127                    list[i].expr.walk { node in128                        if case .range(let r) = node, r.sheet == nil || wb.sheetIndex(named: r.sheet!) == si, r.rangeRef.intersects(blockRange), !r.rangeRef.isSingleCell { aggregates = true }129                    }130                    // the first cell of a run that merely picks up a starting value (=B1) is an initialisation, not a break131                    var seed = list[i].expr132                    while case .paren(let x) = seed { seed = x }133                    if i == block.first, case .cell = seed { continue }134                    if !aggregates { out[i].breaksWith = model }135                }136            }137            var previous: Int?138            for i in sorted {139                let p = vertical ? list[i].row : list[i].col140                if let q = previous, p != q + 1 { flush() }141                block.append(i)142                previous = p143            }144            flush()145        }146        return out147    }148149    mutating func ruleInconsistent() {150        for si in wb.sheets.indices where !formulas[si].isEmpty {151            let list = formulas[si]152            let v = verdicts(si, vertical: true), h = verdicts(si, vertical: false)153            for i in list.indices {154                var model: Int?155                if let m = v[i].breaksWith, !h[i].consistent { model = m }156                else if let m = h[i].breaksWith, !v[i].consistent { model = m }157                guard let m = model else { continue }158                let f = list[i], g = list[m]159                // the totals row of a table summarises the column above: it is expected to differ160                if wb.sheets[si].meta.tables.contains(where: { $0.hasTotals && $0.range.r1 == f.ref.row && f.ref.col >= $0.range.c0 && f.ref.col <= $0.range.c1 }) { continue }161                let diff = FormulaDiff(old: FormulaIntel.tokens(g.expr, style: .r1c1(g.ref)), new: FormulaIntel.tokens(f.expr, style: .r1c1(f.ref)))162                add(.inconsistentFormula, .warning, sheet: si, cells: [f.ref],163                    evidence: "\(f.ref.a1) is \(text(f)) while its neighbours follow the pattern of \(g.ref.a1): \(text(g)). In relative (R1C1) form: \(f.shape) instead of \(g.shape); difference: \(diff.summary).",164                    explanation: "A formula that differs from an otherwise uniform row or column is a classic spreadsheet error: a copy that was edited, overwritten or not filled.",165                    action: "Check whether the difference is intended. If not, copy the neighbouring formula into \(f.ref.a1); if it is, make the exception visible (label or note).")166            }167        }168    }169170    // MARK: Hardcoded number in the middle of a formula run171172    mutating func ruleHardcodedOverwrite() {173        for (si, sheet) in wb.sheets.enumerated() where !formulas[si].isEmpty {174            let list = formulas[si]175            var found = [CellRef: (String, [CellRef])]()     // constant cell → (pattern text, pattern cells)176            func isNumberConstant(_ r: Int, _ c: Int) -> Bool {177                guard let cell = sheet.cell(r, c), cell.formula == nil, !cell.isSpill else { return false }178                return cell.value.isNumber179            }180            func shape(_ r: Int, _ c: Int) -> String? { index[si][CellRef(r, c)].map { list[$0].shape } }181            for f in list {182                for (dr, dc) in [(1, 0), (0, 1)] {183                    // 1 to 3 numeric constants directly after this formula, followed by the same formula again184                    var gap = [CellRef]()185                    var k = 1186                    while k <= 3, isNumberConstant(f.row + dr * k, f.col + dc * k) { gap.append(CellRef(f.row + dr * k, f.col + dc * k)); k += 1 }187                    guard !gap.isEmpty, shape(f.row + dr * k, f.col + dc * k) == f.shape else { continue }188                    // length of the surrounding run189                    var pattern = [f.ref]190                    var j = 1191                    while shape(f.row - dr * j, f.col - dc * j) == f.shape { pattern.append(CellRef(f.row - dr * j, f.col - dc * j)); j += 1 }192                    j = k193                    while shape(f.row + dr * j, f.col + dc * j) == f.shape { pattern.append(CellRef(f.row + dr * j, f.col + dc * j)); j += 1 }194                    guard pattern.count >= options.runLength else { continue }195                    for g in gap where found[g] == nil { found[g] = (text(f), pattern) }196                }197            }198            for (cell, info) in found.sorted(by: { $0.key < $1.key }) {199                let value = sheet.value(cell.row, cell.col)200                add(.hardcodedOverwrite, .warning, sheet: si, cells: [cell],201                    evidence: "\(cell.a1) contains the constant \(value.description) while the surrounding cells \(rangesText(info.1)) are formulas like \(info.0).",202                    explanation: "A typed-in number inside a run of formulas usually means a formula was overwritten: the cell no longer follows its inputs.",203                    action: "Restore the formula in \(cell.a1) (fill it from a neighbour), or move the override to an input cell and document it.")204            }205        }206    }207208    // MARK: Duplicated calculations209210    /// A call qualifies as a "calculation worth sharing" when it reads at least one multi-cell range and is not volatile.211    private func duplicateKey(_ e: Expr, sheet: String) -> String? {212        guard case .call(let name, _) = e, name != "LAMBDA" else { return nil }213        var hasRange = false, unstable = false214        e.walk { node in215            switch node {216            case .range(let r): if !r.rangeRef.isSingleCell { hasRange = true }217            case .call(let n, _): if engine.registry.lookup(n)?.isVolatile == true || n == "ROW" || n == "COLUMN" { unstable = true }218            case .structured(let sr): if sr.thisRow { unstable = true } else { hasRange = true }219            case .name, .invoke: unstable = true220            default: break221            }222        }223        guard hasRange, !unstable else { return nil }224        return FormulaIntel.tokens(e, style: .absolute(defaultSheet: sheet)).joined()225    }226227    mutating func ruleDuplicated() {228        // pass 1: how many cells compute each fixed-range call229        var counts = [String: Int]()230        var keysByCell = [[[(Expr, String)]]]()231        for si in wb.sheets.indices {232            let name = sheetName(si)233            var perCell = [[(Expr, String)]]()234            for f in formulas[si] {235                var here = [(Expr, String)]()236                var seen = Set<String>()237                f.expr.walk { node in238                    if let k = duplicateKey(node, sheet: name), seen.insert(k).inserted { here.append((node, k)) }239                }240                for (_, k) in here { counts[k, default: 0] += 1 }241                perCell.append(here)242            }243            keysByCell.append(perCell)244        }245        // pass 2: per cell, the outermost repeated call (walk order is parents first)246        var cellsByKey = [String: [(Int, CellRef)]]()247        var textByKey = [String: String]()248        var order = [String]()249        for si in wb.sheets.indices {250            for (i, f) in formulas[si].enumerated() {251                var covered = [Expr]()252                for (node, k) in keysByCell[si][i] where counts[k, default: 0] >= options.duplicateThreshold {253                    var inside = false254                    for outer in covered where !inside { outer.walk { if $0 == node { inside = true } } }255                    if inside { continue }256                    covered.append(node)257                    if cellsByKey[k] == nil { order.append(k); textByKey[k] = node.formulaText() }258                    cellsByKey[k, default: []].append((si, f.ref))259                }260            }261        }262        for k in order {263            let all = cellsByKey[k]!264            guard all.count >= options.duplicateThreshold else { continue }265            let si = all[0].0266            let cells = all.filter { $0.0 == si }.map(\.1)267            add(.duplicatedCalculation, .info, sheet: si, cells: cells,268                evidence: "\(textByKey[k]!) is recomputed in \(all.count) cells (\(rangesText(cells))).",269                explanation: "The same calculation over the same fixed range is repeated in every one of these formulas: it is evaluated again for each cell and must be changed in every copy.",270                action: "Compute it once in a helper cell (or a defined name) and refer to that cell from the formulas.")271        }272    }273}274
Files main
Menu

Theme