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/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