Repository Public
1// =============================================================================2// SP BOUCHER | SOFTWARE & RESEARCH3// ARTIFICIAL INTELLIGENCE • FINANCE • ENGINEERING4// =============================================================================5//6// PROJECT METADATA7// -----------------------------------------------------------------------------8// Project : ORIX Sheet9// File : Packages/SheetTemplates/Tests/SheetTemplatesTests/TestSupport.swift10// Module : SheetTemplatesTests11// 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 Testing89import SheetCore90import SheetEngine91import SheetIO92@testable import SheetTemplates9394let english = Locale(identifier: "en_CA")95let french = Locale(identifier: "fr_CA")9697/// One (template, locale) pair; the argument of the parameterised tests.98struct Case: Sendable, CustomTestStringConvertible {99 let id: String100 let locale: Locale101 var template: WorkbookTemplate { TemplateLibrary.template(id: id)! }102 var tag: String { "\(id)-\(locale.identifier.prefix(2))" }103 var testDescription: String { tag }104 static let all: [Case] = TemplateLibrary.all.flatMap { t in [Case(id: t.id, locale: english), Case(id: t.id, locale: french)] }105}106107/// Builds every template once per locale; tests that mutate a workbook ask for a fresh build instead.108final class BuildCache: @unchecked Sendable {109 static let shared = BuildCache()110 private let lock = NSLock()111 private var built: [String: (Workbook, [String])] = [:]112 func get(_ c: Case) -> (workbook: Workbook, problems: [String]) {113 lock.lock(); defer { lock.unlock() }114 if let b = built[c.tag] { return b }115 ORIXEngine.bootstrap()116 let b = c.template.buildChecked(locale: c.locale)117 built[c.tag] = b118 return b119 }120}121122func fresh(_ id: String, _ locale: Locale = english) -> (wb: Workbook, engine: CalcEngine) {123 ORIXEngine.bootstrap()124 let wb = TemplateLibrary.template(id: id)!.build(locale: locale)125 return (wb, CalcEngine(workbook: wb, trustCachedValues: true))126}127128extension Workbook {129 /// Value of a defined name (single cell) or of any formula text, read from the calculated workbook.130 func eval(_ text: String) -> Value { CalcEngine(workbook: self, trustCachedValues: true).evaluate(text, sheet: 0) }131 func number(_ name: String) -> Double {132 if case .number(let n) = eval(name).scalar { return n }133 return .nan134 }135 func string(_ name: String) -> String { (try? eval(name).scalar.str()) ?? "#ERR" }136 func cell(_ sheet: Int, _ a1: String) -> Value {137 let r = CellRef(a1: a1)!138 return sheets[sheet].value(r.row, r.col)139 }140 func num(_ sheet: Int, _ a1: String) -> Double { if case .number(let n) = cell(sheet, a1) { return n }; return .nan }141142 /// Address of the cell a single-cell defined name points at.143 func target(_ name: String) -> (sheet: Int, row: Int, col: Int) {144 let dn = definedName(name)!145 let parts = dn.formula.components(separatedBy: "!")146 let sheetName = parts[0].trimmingCharacters(in: CharacterSet(charactersIn: "'")).replacingOccurrences(of: "''", with: "'")147 let ref = CellRef(a1: parts[1].replacingOccurrences(of: "$", with: "").components(separatedBy: ":")[0])!148 return (sheetIndex(named: sheetName)!, ref.row, ref.col)149 }150}151152extension CalcEngine {153 /// Overwrites the input cell behind a defined name and recalculates.154 func setInput(_ name: String, _ value: Value) {155 let t = workbook.target(name)156 set(sheet: t.sheet, row: t.row, col: t.col, .value(value))157 recalculate()158 }159 func setCell(_ sheet: Int, _ a1: String, _ value: Value) {160 let r = CellRef(a1: a1)!161 set(sheet: sheet, row: r.row, col: r.col, .value(value))162 recalculate()163 }164}165166func close(_ a: Double, _ b: Double, rel: Double = 1e-9, abs: Double = 1e-9) -> Bool {167 a.isFinite && b.isFinite && Swift.abs(a - b) <= max(abs, rel * max(Swift.abs(a), Swift.abs(b)))168}169170/// Every error value of a workbook, as "Sheet!A1 = #DIV/0!".171func errorCells(_ wb: Workbook) -> [String] {172 var out: [String] = []173 for s in wb.sheets {174 s.cells.forEachAll { r, c, cell in175 let v = cell.computed176 if case .error(let e) = v { out.append("\(s.name)!\(CellRef(r, c).a1) = \(e.rawValue)") }177 if case .array(let a) = v, let e = a.items.compactMap(\.errorValue).first { out.append("\(s.name)!\(CellRef(r, c).a1) = array with \(e.rawValue)") }178 }179 }180 return out.sorted()181}182183/// Names of the functions called by an expression.184func functionNames(_ e: Expr) -> Set<String> {185 var out = Set<String>()186 e.walk { if case .call(let n, _) = $0 { out.insert(n) } }187 return out188}189190/// Expected values computed by Generators/gen_expected.py (numpy / scipy).191enum Expected {192 nonisolated(unsafe) static let all: [String: Any] = {193 guard let url = Bundle.module.url(forResource: "expected", withExtension: "json", subdirectory: "Fixtures"),194 let data = try? Data(contentsOf: url), let obj = try? JSONSerialization.jsonObject(with: data) as? [String: Any] else { return [:] }195 return obj196 }()197 static func section(_ id: String) -> [String: Any] { all[id] as? [String: Any] ?? [:] }198 static func number(_ id: String, _ key: String) -> Double { (section(id)[key] as? NSNumber)?.doubleValue ?? .nan }199 static func numbers(_ id: String, _ key: String) -> [Double] { (section(id)[key] as? [NSNumber])?.map(\.doubleValue) ?? [] }200 static func strings(_ id: String, _ key: String) -> [String] { section(id)[key] as? [String] ?? [] }201}202203/// Dot-path access into expected.json: `X.d("mortgage.canadian.payment")`.204enum X {205 static func any(_ path: String) -> Any? {206 var cur: Any? = Expected.all207 for key in path.split(separator: ".") { cur = (cur as? [String: Any])?[String(key)] }208 return cur209 }210 static func d(_ path: String) -> Double { (any(path) as? NSNumber)?.doubleValue ?? .nan }211 static func a(_ path: String) -> [Double] { (any(path) as? [NSNumber])?.map(\.doubleValue) ?? [] }212 static func m(_ path: String) -> [[Double]] { (any(path) as? [[NSNumber]])?.map { $0.map(\.doubleValue) } ?? [] }213 static func s(_ path: String) -> [String] { any(path) as? [String] ?? [] }214}215216extension Workbook {217 /// Numbers of a range of a sheet, row-major; non-numeric cells are NaN.218 func nums(_ sheet: Int, _ range: String) -> [Double] {219 let r = RangeRef(a1: range)!220 var out: [Double] = []221 for row in r.r0...r.r1 { for col in r.c0...r.c1 { if case .number(let n) = sheets[sheet].value(row, col) { out.append(n) } else { out.append(.nan) } } }222 return out223 }224 func texts(_ sheet: Int, _ range: String) -> [String] {225 let r = RangeRef(a1: range)!226 var out: [String] = []227 for row in r.r0...r.r1 { for col in r.c0...r.c1 { out.append(sheets[sheet].value(row, col).textValue ?? "") } }228 return out229 }230}231232/// Element-wise comparison with a relative tolerance.233func allClose(_ a: [Double], _ b: [Double], rel: Double = 1e-9, abs: Double = 1e-9) -> Bool {234 a.count == b.count && !a.isEmpty && zip(a, b).allSatisfy { close($0, $1, rel: rel, abs: abs) }235}236