Repository Public
1// =============================================================================2// SP BOUCHER | SOFTWARE & RESEARCH3// ARTIFICIAL INTELLIGENCE • FINANCE • ENGINEERING4// =============================================================================5//6// PROJECT METADATA7// -----------------------------------------------------------------------------8// Project : ORIX Sheet9// File : Packages/SheetAnalytics/Tests/AuditTests/RulesValidatorTests.swift10// Module : AuditTests11// 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 SheetCore90@testable import Audit9192@Suite struct ValidatorTests {93 func book(_ rules: [DataValidation]) -> (RulesTestBook, Validator) {94 let b = RulesTestBook()95 b.engine.editMeta(sheet: 0) { $0.validations = rules }96 return (b, Validator(engine: b.engine))97 }98 func dv(_ range: String, _ kind: DataValidation.Kind, _ op: String = "between", _ f1: String? = nil, _ f2: String? = nil) -> DataValidation {99 DataValidation(ranges: [RangeRef(a1: range)!], kind: kind, op: op, formula1: f1, formula2: f2)100 }101 func ok(_ v: Validator, _ value: Value, _ a1: String = "A1") -> Bool {102 let r = CellRef(a1: a1)!103 return v.validate(value, sheet: 0, row: r.row, col: r.col).isValid104 }105106 @Test func wholeNumberBetween() {107 let (_, v) = book([dv("A1:A5", .whole, "between", "1", "10")])108 for good in [1.0, 5, 10] { #expect(ok(v, .number(good))) }109 for bad: Value in [.number(0), .number(11), .number(5.5), .text("abc"), .text("5"), .bool(true), .error(.na)] { #expect(!ok(v, bad), "\(bad)") }110 let o = v.validate(.number(11), sheet: 0, row: 0, col: 0)111 #expect(o.style == .stop && !o.canOverride)112 #expect(o.reason == "Enter a whole number between 1 and 10.")113 #expect(o.message == o.reason)114 #expect(o.title == "Invalid entry")115 #expect(v.validate(.number(2.5), sheet: 0, row: 0, col: 0).reason == "Enter a whole number.")116 #expect(v.validate(.error(.div0), sheet: 0, row: 0, col: 0).reason == "The value is an error (#DIV/0!).")117 // outside the rule's range, or on another sheet index, anything goes118 #expect(ok(v, .text("anything"), "B1"))119 #expect(ok(v, .text("anything"), "A6"))120 #expect(v.validate(.text("x"), sheet: 9, row: 0, col: 0).isValid)121 #expect(v.rule(sheet: 0, row: 0, col: 0)?.kind == .whole)122 #expect(v.rule(sheet: 0, row: 9, col: 0) == nil)123 }124125 @Test func everyOperatorOnDecimals() {126 // (op, formula1, formula2, accepted, rejected)127 let cases: [(String, String, String?, [Double], [Double])] = [128 ("between", "2", "5", [2, 3.5, 5], [1.99, 5.01]),129 ("between", "5", "2", [2, 3.5, 5], [1.99, 5.01]),130 ("notBetween", "2", "5", [1.99, 5.01, -3], [2, 3.5, 5]),131 ("equal", "5", nil, [5], [4.999, 6]),132 ("notEqual", "5", nil, [4.999, 6], [5]),133 ("greaterThan", "5", nil, [5.001, 100], [5, 0]),134 ("lessThan", "5", nil, [4.999, -1], [5, 9]),135 ("greaterThanOrEqual", "5", nil, [5, 7], [4.999]),136 ("lessThanOrEqual", "5", nil, [5, -7], [5.001]),137 ]138 for (op, f1, f2, good, bad) in cases {139 let (_, v) = book([dv("A1", .decimal, op, f1, f2)])140 for x in good { #expect(ok(v, .number(x)), "\(op) should accept \(x)") }141 for x in bad { #expect(!ok(v, .number(x)), "\(op) should reject \(x)") }142 }143 let (_, v) = book([dv("A1", .decimal, "greaterThanOrEqual", "0.5")])144 #expect(v.validate(.number(0.25), sheet: 0, row: 0, col: 0).reason == "Enter a number greater than or equal to 0.5.")145 #expect(v.validate(.text("x"), sheet: 0, row: 0, col: 0).reason == "Enter a number.")146 let (_, bad) = book([dv("A1", .decimal, "sameAs", "1")])147 #expect(!ok(bad, .number(1)))148 #expect(bad.validate(.number(1), sheet: 0, row: 0, col: 0).reason == "The validation criteria of this cell could not be evaluated.")149 let (_, missing) = book([dv("A1", .decimal, "between", "1", nil)])150 #expect(!ok(missing, .number(1)))151 let (_, garbage) = book([dv("A1", .decimal, "equal", "\"abc\"")])152 #expect(!ok(garbage, .number(1)))153 }154155 @Test func datesAndTimes() {156 let start = DateSerial.serial(year: 2026, month: 1, day: 1), end = DateSerial.serial(year: 2026, month: 12, day: 31)157 let (_, v) = book([dv("A1", .date, "between", String(Int(start)), String(Int(end))),158 dv("B1", .time, "between", "0.375", "0.75"),159 dv("C1", .date, "greaterThan", "DATE(2026,6,30)")]) // DATE is not registered in the core registry160 #expect(ok(v, .number(DateSerial.serial(year: 2026, month: 7, day: 14))))161 #expect(ok(v, .number(start)) && ok(v, .number(end)))162 #expect(!ok(v, .number(DateSerial.serial(year: 2025, month: 12, day: 31))))163 #expect(!ok(v, .number(DateSerial.serial(year: 2027, month: 1, day: 1))))164 #expect(!ok(v, .text("2026-07-14")))165 #expect(v.validate(.text("soon"), sheet: 0, row: 0, col: 0).reason == "Enter a date.")166 #expect(ok(v, .number(0.5), "B1")) // 12:00167 #expect(ok(v, .number(0.375), "B1")) // 09:00168 #expect(!ok(v, .number(0.8), "B1")) // 19:12169 #expect(!ok(v, .text("noon"), "B1"))170 #expect(v.validate(.text("noon"), sheet: 0, row: 0, col: 1).reason == "Enter a time.")171 // a bound that cannot be evaluated rejects the entry with an explicit reason172 #expect(v.validate(.number(end), sheet: 0, row: 0, col: 2).reason == "The validation criteria of this cell could not be evaluated.")173 }174175 @Test func textLength() {176 let (_, v) = book([dv("A1", .textLength, "lessThanOrEqual", "5"), dv("B1", .textLength, "between", "2", "3"), dv("C1", .textLength, "equal", "4")])177 #expect(ok(v, .text("hello")))178 #expect(!ok(v, .text("hello!")))179 #expect(ok(v, .number(12345)))180 #expect(!ok(v, .number(123456))) // six characters as displayed181 #expect(ok(v, .text("héé"))) // characters, not bytes182 #expect(!ok(v, .text("a"), "B1"))183 #expect(ok(v, .text("ab"), "B1") && ok(v, .text("abc"), "B1"))184 #expect(!ok(v, .text("abcd"), "B1"))185 #expect(ok(v, .bool(true), "C1")) // "TRUE"186 #expect(v.validate(.text("abcdef"), sheet: 0, row: 0, col: 0).reason == "Enter text whose length is less than or equal to 5.")187 }188189 @Test func literalLists() {190 let (_, v) = book([dv("A1", .list, "between", "\"Red,Green, Blue\""), dv("B1", .list, "between", "\"1,2,3\""), dv("C1", .list, "between", "Yes,No"),191 dv("D1", .list, "between", "\"He said \"\"hi\"\",Bye\"")])192 #expect(ok(v, .text("Red")) && ok(v, .text("Green")) && ok(v, .text("Blue"))) // items are trimmed193 #expect(!ok(v, .text("green"))) // literal lists are case-sensitive, as in Excel194 #expect(!ok(v, .text("Purple")))195 #expect(!ok(v, .number(1)))196 #expect(v.validate(.text("Purple"), sheet: 0, row: 0, col: 0).reason == "Choose a value from the list.")197 #expect(v.dropdownItems(sheet: 0, row: 0, col: 0) == [.text("Red"), .text("Green"), .text("Blue")])198 #expect(ok(v, .number(2), "B1") && ok(v, .text("3"), "B1")) // numeric items match numbers199 #expect(!ok(v, .number(4), "B1"))200 #expect(ok(v, .text("No"), "C1")) // unquoted comma list is tolerated201 #expect(v.dropdownItems(sheet: 0, row: 0, col: 2) == [.text("Yes"), .text("No")])202 #expect(v.dropdownItems(sheet: 0, row: 0, col: 3) == [.text("He said \"hi\""), .text("Bye")])203 #expect(v.dropdownItems(sheet: 0, row: 5, col: 5) == nil)204 }205206 @Test func listsFromRangesAndNames() {207 var named = dv("B1:B3", .list, "between", "Fruits")208 named.showDropdown = false209 let (b, v) = book([dv("A1:A3", .list, "between", "$E$1:$E$4"), named, dv("C1:C3", .list, "between", "=F1:F2"), dv("D1", .list, "between", "$G$1")])210 b.column("E1", ["Apple", "Pear", "", "7"])211 b.column("F1", ["x", "y", "z", "w"])212 b.set("G1", "only")213 b.engine.setDefinedName(DefinedName(name: "Fruits", formula: "Sheet1!$E$1:$E$2"))214 #expect(v.dropdownItems(sheet: 0, row: 0, col: 0) == [.text("Apple"), .text("Pear"), .number(7)]) // blanks are skipped215 #expect(ok(v, .text("apple")) && ok(v, .text("PEAR"))) // lists read from cells are case-insensitive216 #expect(ok(v, .number(7)) && ok(v, .text("7")))217 #expect(!ok(v, .text("Plum")) && !ok(v, .number(8)))218 #expect(v.dropdownItems(sheet: 0, row: 1, col: 1) == [.text("Apple"), .text("Pear")])219 #expect(ok(v, .text("Pear"), "B2") && !ok(v, .number(7), "B2"))220 #expect(v.rule(sheet: 0, row: 1, col: 1)?.showDropdown == false)221 // a relative source moves with the cell: C1 → F1:F2, C3 → F3:F4222 #expect(v.dropdownItems(sheet: 0, row: 0, col: 2) == [.text("x"), .text("y")])223 #expect(v.dropdownItems(sheet: 0, row: 2, col: 2) == [.text("z"), .text("w")])224 #expect(ok(v, .text("w"), "C3") && !ok(v, .text("w"), "C1"))225 #expect(v.dropdownItems(sheet: 0, row: 0, col: 3) == [.text("only")])226 // the list follows the cells227 b.set("E3", "Fig")228 #expect(ok(v, .text("fig")))229 #expect(v.dropdownItems(sheet: 0, row: 0, col: 0)?.count == 4)230 }231232 @Test func blanksAndAllowBlank() {233 var strict = dv("B1", .whole, "between", "1", "10")234 strict.allowBlank = false235 var strictList = dv("C1", .list, "between", "\"a,b\"")236 strictList.allowBlank = false237 var strictLength = dv("D1", .textLength, "lessThan", "3")238 strictLength.allowBlank = false239 var strictBound = dv("F1", .decimal, "greaterThan", "$Z$1")240 strictBound.allowBlank = false241 let (_, v) = book([dv("A1", .whole, "between", "1", "10"), strict, strictList, strictLength, dv("E1", .decimal, "greaterThan", "$Z$1"), strictBound])242 #expect(ok(v, .blank, "A1"))243 #expect(!ok(v, .blank, "B1"))244 #expect(!ok(v, .blank, "C1"))245 #expect(ok(v, .blank, "D1")) // length 0 < 3246 // a bound that refers to an empty cell: ignored with allowBlank, read as 0 without247 #expect(ok(v, .number(-5), "E1"))248 #expect(!ok(v, .number(-5), "F1"))249 #expect(ok(v, .number(5), "F1"))250 }251252 @Test func boundsReadFromCellsShiftWithTheCell() {253 let (b, v) = book([dv("B1:B3", .decimal, "lessThanOrEqual", "A1"), dv("C1:C3", .whole, "between", "$A$1", "$A$3")])254 b.column("A1", ["10", "20", "30"])255 #expect(ok(v, .number(10), "B1") && !ok(v, .number(11), "B1"))256 #expect(ok(v, .number(20), "B2") && !ok(v, .number(21), "B2"))257 #expect(ok(v, .number(30), "B3") && !ok(v, .number(31), "B3"))258 #expect(ok(v, .number(30), "C3") && !ok(v, .number(31), "C3") && !ok(v, .number(9), "C2"))259 #expect(v.validate(.number(21), sheet: 0, row: 1, col: 1).reason == "Enter a number less than or equal to 20.")260 b.set("A2", "25")261 #expect(ok(v, .number(21), "B2"))262 }263264 @Test func customFormulaSeesTheProposedValueWithoutEditingTheWorkbook() {265 let (b, v) = book([dv("A1:A5", .custom, "between", "=A1>$C$1"), dv("B1:B5", .custom, "between", "=IF(B1>0,B1<A1,FALSE)"),266 dv("D1", .custom, "between", "=D1<>\"bad\""), dv("E1", .custom, "between", "=SUM(E1,"), dv("F1", .custom, "between", "=Sheet1!F1*2=8")])267 b.set("C1", "10")268 b.column("A1", ["50", "60", "70"])269 let before = (b.engine.changeCount, b.engine.undoLabel, b.engine.canRedo)270 #expect(ok(v, .number(11), "A1") && !ok(v, .number(9), "A1"))271 #expect(ok(v, .number(11), "A3") && !ok(v, .number(10), "A3")) // the reference shifted to A3 and was substituted272 #expect(ok(v, .text("abc"), "A4")) // text sorts above numbers: "abc" > 10273 #expect(ok(v, .number(40), "B1") && !ok(v, .number(50), "B1") && !ok(v, .number(-1), "B1"))274 #expect(ok(v, .number(65), "B3") && !ok(v, .number(65), "B2"))275 #expect(ok(v, .text("good"), "D1") && !ok(v, .text("BAD"), "D1"))276 #expect(!ok(v, .number(1), "E1"))277 #expect(v.validate(.number(1), sheet: 0, row: 0, col: 4).reason == "The validation formula of this cell could not be parsed.")278 #expect(ok(v, .number(4), "F1") && !ok(v, .number(5), "F1")) // sheet-qualified self reference279 #expect(v.validate(.number(9), sheet: 0, row: 0, col: 0).reason == "The value does not satisfy the validation formula.")280 // nothing was written: values, undo history and change counter are untouched281 #expect(b.sheet.value(0, 0) == .number(50) && b.sheet.value(2, 0) == .number(70))282 #expect(b.sheet.cell(0, 1) == nil && b.sheet.cell(0, 3) == nil)283 #expect((b.engine.changeCount, b.engine.undoLabel, b.engine.canRedo) == before)284 }285286 @Test func alertStylesAndCustomMessages() {287 var warn = dv("A1", .whole, "greaterThan", "0")288 warn.errorStyle = "warning"289 warn.errorTitle = "Careful"290 warn.errorMessage = "Quantities are normally positive."291 var info = dv("A2", .whole, "greaterThan", "0")292 info.errorStyle = "information"293 var odd = dv("A3", .whole, "greaterThan", "0")294 odd.errorStyle = "somethingElse"295 odd.errorTitle = ""296 let (_, v) = book([warn, info, odd, dv("A4", .any)])297 let w = v.validate(.number(-1), sheet: 0, row: 0, col: 0)298 #expect(!w.isValid && w.style == .warning && w.canOverride)299 #expect(w.title == "Careful" && w.message == "Quantities are normally positive.")300 #expect(w.reason == "Enter a whole number greater than 0.")301 let i = v.validate(.number(-1), sheet: 0, row: 1, col: 0)302 #expect(i.style == .information && i.canOverride && i.message == i.reason)303 let o = v.validate(.number(-1), sheet: 0, row: 2, col: 0)304 #expect(o.style == .stop && o.title == "Invalid entry")305 #expect(v.validate(.error(.na), sheet: 0, row: 3, col: 0) == .valid) // kind `any` accepts everything306 #expect(v.validate(.number(3), sheet: 0, row: 0, col: 0) == .valid)307 #expect(ValidationOutcome.valid.canOverride)308 }309310 @Test func typedInputIsParsedFirst() {311 let (b, v) = book([dv("A1", .whole, "between", "1", "10"), dv("B1", .decimal, "lessThan", "1"), dv("C1", .date, "greaterThan", "40000")])312 b.set("D1", "3")313 func typed(_ text: String, _ a1: String) -> ValidationOutcome {314 let r = CellRef(a1: a1)!315 return v.validate(input: text, sheet: 0, row: r.row, col: r.col, parser: b.parser)316 }317 #expect(typed("7", "A1").isValid)318 #expect(!typed("70", "A1").isValid)319 #expect(!typed("seven", "A1").isValid)320 #expect(typed("", "A1").isValid)321 #expect(typed("=D1+4", "A1").isValid) // the formula's result (7) is validated322 #expect(!typed("=D1*4", "A1").isValid)323 #expect(typed("=D1+", "A1").title == "Invalid formula")324 #expect(typed("50%", "B1").isValid)325 #expect(!typed("150%", "B1").isValid)326 #expect(typed("2026-03-01", "C1").isValid) // parsed to a date serial327 #expect(!typed("1999-03-01", "C1").isValid)328 #expect(b.sheet.cell(0, 0) == nil)329 }330331 @Test func invalidCellsOfExistingData() {332 var noBlank = dv("C1:C3", .list, "between", "\"a,b\"")333 noBlank.allowBlank = false334 let (b, v) = book([dv("A1:A6", .whole, "between", "1", "10"), dv("B1:B6", .textLength, "lessThan", "4"), noBlank, dv("D1:D6", .any)])335 b.column("A1", ["5", "50", "2.5", "x", "", "=3*3"])336 b.column("B1", ["abc", "abcd", "12", "12345"])337 b.column("C1", ["a", "z"])338 b.column("D1", ["anything"])339 let bad = v.invalidCells(sheet: 0, in: RangeRef(a1: "A1:D6")!)340 #expect(bad.keys.sorted().map(\.a1) == ["A2", "B2", "C2", "A3", "A4", "B4"])341 #expect(bad[CellRef(a1: "A2")!]?.reason == "Enter a whole number between 1 and 10.")342 #expect(bad[CellRef(a1: "A3")!]?.reason == "Enter a whole number.")343 #expect(bad[CellRef(a1: "C2")!]?.reason == "Choose a value from the list.")344 #expect(v.invalidCells(sheet: 0, in: RangeRef(a1: "A5:A6")!).isEmpty) // blank allowed, 9 valid345 #expect(v.invalidCells(sheet: 3, in: RangeRef(a1: "A1:D6")!).isEmpty)346 }347348 @Test func firstMatchingRuleWins() {349 let (_, v) = book([dv("A1:A3", .whole, "lessThan", "5"), dv("A1:B9", .whole, "greaterThan", "100")])350 #expect(ok(v, .number(1), "A2") && !ok(v, .number(200), "A2"))351 #expect(ok(v, .number(200), "A5") && !ok(v, .number(1), "A5"))352 #expect(ok(v, .number(200), "B1"))353 }354}355