Repository Public
1import Foundation2import Testing3import SheetCore4import AnalyticsTestSupport5@testable import WhatIf67@Suite struct DataTableTests {8 @Test func oneVariableAgainstTheModelAlgebra() throws {9 for isolation in WhatIfIsolation.allCases {10 let m = Model()11 m.seedHistory()12 let volumes: [Double] = [0, 500, 800, 1000, 2500]13 let t = try expectUntouched(m, isolation, "one-variable") {14 try DataTable.oneVariable(engine: m.engine, input: m.cell("B2", sheet: 1), values: volumes,15 outputs: [m.cell("B5", sheet: 1), m.cell("B6", sheet: 1), m.cell("B7", sheet: 1), m.cell("B6", sheet: 2)], isolation: isolation)16 }17 #expect(t.values.count == 5 && t.outputLabels == ["Profit!B5", "Profit!B6", "Profit!B7", "DCF!B6"] && t.inputLabel == "Profit!B2")18 for (i, v) in volumes.enumerated() {19 #expect(t.values[i] == [.number(25 * v), .number(15 * v + 8000), .number(10 * v - 8000), .number(2 * (10 * v - 8000))])20 }21 let grid = t.table()22 #expect(grid.count == 6 && grid[0][0] == .text("Profit!B2") && grid[3] == [.number(800), .number(20000), .number(20000), .number(0), .number(0)])23 }24 }2526 @Test func twoVariableLoanGrid() throws {27 for isolation in WhatIfIsolation.allCases {28 let m = Model()29 m.seedHistory()30 let rates = [0.03, 0.045, 0.06, 0.075], terms: [Double] = [180, 240, 300, 360]31 let t = try expectUntouched(m, isolation, "two-variable") {32 try DataTable.twoVariable(engine: m.engine, rowInput: m.cell("B1"), rowValues: rates, columnInput: m.cell("B2"), columnValues: terms,33 output: m.cell("B4"), isolation: isolation)34 }35 #expect(t.values.count == 4 && t.values.allSatisfy { $0.count == 4 })36 for (i, n) in terms.enumerated() {37 for (j, r) in rates.enumerated() {38 #expect(isClose(t.values[i][j].numberValue ?? .nan, annuity(300_000, r, n), rel: 1e-12), "n=\(n) r=\(r)")39 }40 }41 let grid = t.table()42 #expect(grid[0] == [.text("Loan!B4")] + rates.map { Value.number($0) })43 #expect(grid[4][0] == .number(360) && isClose(grid[4][3].numberValue!, annuity(300_000, 0.06, 360), rel: 1e-12))44 }45 }4647 @Test func textInputsErrorsAndValidation() throws {48 let m = Model()49 m.set("H1", "std"); m.set("H2", "=IF(H1=\"vip\",0.2,IF(H1=\"std\",0.05,1/0))")50 let t = try expectUntouched(m, .clone, "text inputs") {51 try DataTable.oneVariable(engine: m.engine, input: m.cell("H1"), values: [.text("vip"), .text("std"), .text("other"), .blank], outputs: [m.cell("H2")])52 }53 #expect(t.values == [[.number(0.2)], [.number(0.05)], [.error(.div0)], [.error(.div0)]])54 #expect(try DataTable.oneVariable(engine: m.engine, input: m.cell("H1"), values: [Double](), outputs: [m.cell("H2")]).values.isEmpty)55 #expect(throws: WhatIfError.self) {56 try DataTable.twoVariable(engine: m.engine, rowInput: m.cell("B1"), rowValues: [1.0], columnInput: m.cell("B1"), columnValues: [1.0], output: m.cell("B4"))57 }58 #expect(throws: WhatIfError.self) { try DataTable.oneVariable(engine: m.engine, input: ModelCell(sheet: 3, row: 0, col: 0), values: [1.0], outputs: [m.cell("B4")]) }59 // overwriting a formula input temporarily is allowed and restored60 let f = try expectUntouched(m, .inPlace, "formula input") {61 try DataTable.oneVariable(engine: m.engine, input: m.cell("B5", sheet: 1), values: [100.0, 9000], outputs: [m.cell("B7", sheet: 1)], isolation: .inPlace)62 }63 let wantF: [[Value]] = [[.number(-22900)], [.number(-14000)]]64 #expect(f.values == wantF)65 }66}6768@Suite struct ScenarioTests {69 func makeSet(_ m: Model) throws -> ScenarioSet {70 var set = ScenarioSet()71 set.upsert(try Scenario(name: "Best", comment: "strong demand", workbook: m.wb,72 values: [(m.cell("B1", sheet: 1), .number(28)), (m.cell("B2", sheet: 1), .number(1500)), (m.cell("B3", sheet: 1), .number(14))]))73 set.upsert(try Scenario(name: "Worst", workbook: m.wb,74 values: [(m.cell("B1", sheet: 1), .number(22)), (m.cell("B2", sheet: 1), .number(600)), (m.cell("B4", sheet: 1), .number(9000))]))75 set.resultCells = [ScenarioResultCell(sheetID: m.wb.sheets[1].id, cell: CellRef(a1: "B7")!)]76 return set77 }7879 @Test func persistenceInWorkbookAttachments() throws {80 let m = Model()81 #expect(try ScenarioManager.load(from: m.wb) == ScenarioSet())82 let set = try makeSet(m)83 try ScenarioManager.save(set, to: m.wb)84 #expect(m.wb.attachments[ScenarioManager.attachmentKey] != nil && ScenarioManager.attachmentKey == "scenarios.json")85 let loaded = try ScenarioManager.load(from: m.wb)86 #expect(loaded == set && loaded.scenarios.map(\.name) == ["Best", "Worst"] && loaded.scenario(named: "best")?.comment == "strong demand")87 // survives a workbook clone, upsert replaces by name, remove deletes88 #expect(try ScenarioManager.load(from: WorkbookCloner.clone(m.wb)) == set)89 var edited = loaded90 edited.upsert(Scenario(name: "BEST", cells: []))91 #expect(edited.scenarios.count == 2 && edited.scenario(named: "Best")?.cells.isEmpty == true)92 edited.remove(named: "worst")93 #expect(edited.scenarios.map(\.name) == ["BEST"])94 m.wb.attachments[ScenarioManager.attachmentKey] = Data("not json".utf8)95 #expect(throws: (any Error).self) { try ScenarioManager.load(from: m.wb) }96 }9798 @Test func applyRestoreAndUndo() throws {99 let m = Model()100 m.seedHistory()101 let set = try makeSet(m)102 let before = snapshot(m.engine)103 let restore = try ScenarioManager.apply(set.scenario(named: "Best")!, engine: m.engine)104 #expect(m.num("B7", sheet: 1) == (28 - 14) * 1500 - 8000 && m.num("B6", sheet: 2) == 2 * 13000)105 #expect(m.engine.undoLabel == "Apply Scenario" && restore.name == "Before Best")106 #expect(restore.cells.map(\.value) == [.number(25), .number(1000), .number(15)])107 m.engine.undo() // one undo reverts the whole scenario108 #expect(snapshot(m.engine) == before)109 try ScenarioManager.apply(set.scenario(named: "Worst")!, engine: m.engine)110 #expect(m.num("B7", sheet: 1) == (22 - 15) * 600 - 9000)111 // a scenario that overwrites a formula: the restore point brings the formula back112 let override = try Scenario(name: "Override", workbook: m.wb, values: [(m.cell("B5", sheet: 1), .number(50_000)), (m.cell("C9", sheet: 1), .text("note")), (m.cell("C10", sheet: 1), .bool(true))])113 let mid = snapshot(m.engine)114 let back = try ScenarioManager.apply(override, engine: m.engine)115 #expect(m.num("B7", sheet: 1) == 50_000 - (15 * 600 + 9000))116 #expect(back.cells.map(\.value) == [.formula("B1*B2"), .blank, .blank])117 try ScenarioManager.apply(back, engine: m.engine)118 #expect(snapshot(m.engine) == mid && m.engine.inputText(sheet: 1, row: 4, col: 1) == "=B1*B2")119 // capture records the current content120 let cap = try ScenarioManager.capture(name: "Now", cells: [m.cell("B1", sheet: 1), m.cell("B5", sheet: 1), m.cell("Z9", sheet: 1)], engine: m.engine)121 #expect(cap.cells.map(\.value) == [.number(22), .formula("B1*B2"), .blank])122 }123124 @Test func scenariosFollowSheetsByIdentity() throws {125 let m = Model()126 let set = try makeSet(m)127 _ = m.engine.renameSheet(at: 1, to: "Budget")128 m.engine.moveSheet(from: 1, to: 0)129 m.engine.recalculate()130 #expect(m.wb.sheets[0].name == "Budget")131 try ScenarioManager.apply(set.scenario(named: "Best")!, engine: m.engine)132 #expect(m.wb.sheets[0].value(6, 1) == .number((28 - 14) * 1500 - 8000))133 // a scenario whose sheet is gone, or with a broken formula, applies nothing134 let before = snapshot(m.engine), count = m.engine.changeCount135 let ghost = Scenario(name: "Ghost", cells: [ScenarioCell(sheetID: m.wb.sheets[0].id, cell: CellRef(0, 1), value: .number(1)),136 ScenarioCell(sheetID: 999, cell: CellRef(0, 0), value: .number(1))])137 #expect(throws: WhatIfError.self) { try ScenarioManager.apply(ghost, engine: m.engine) }138 let broken = Scenario(name: "Broken", cells: [ScenarioCell(sheetID: m.wb.sheets[0].id, cell: CellRef(0, 1), value: .number(1)),139 ScenarioCell(sheetID: m.wb.sheets[0].id, cell: CellRef(1, 1), value: .formula("SUM(1,"))])140 #expect(throws: WhatIfError.self) { try ScenarioManager.apply(broken, engine: m.engine) }141 #expect(snapshot(m.engine) == before && m.engine.changeCount == count)142 #expect(throws: WhatIfError.self) { try ScenarioManager.summary(scenarios: [ghost], resultCells: [], engine: m.engine) }143 }144145 @Test func summaryComparesScenariosWithoutTouchingTheWorkbook() throws {146 for isolation in WhatIfIsolation.allCases {147 let m = Model()148 m.seedHistory()149 let set = try makeSet(m)150 let s = try expectUntouched(m, isolation, "summary") {151 try ScenarioManager.summary(scenarios: set.scenarios, resultCells: [m.cell("B7", sheet: 1), m.cell("B5", sheet: 1), m.cell("B6", sheet: 2)], engine: m.engine, isolation: isolation)152 }153 #expect(s.columns == ["Current Values", "Best", "Worst"])154 #expect(s.changingCellLabels == ["Profit!B1", "Profit!B2", "Profit!B3", "Profit!B4"])155 // a cell a scenario does not set keeps its current value (scenarios do not leak into each other)156 #expect(s.changingValues == [[.number(25), .number(28), .number(22)], [.number(1000), .number(1500), .number(600)],157 [.number(15), .number(14), .number(15)], [.number(8000), .number(8000), .number(9000)]])158 #expect(s.resultValues[0] == [.number(2000), .number(13000), .number(-4800)])159 #expect(s.resultValues[1] == [.number(25000), .number(42000), .number(13200)])160 #expect(s.resultValues[2] == [.number(4000), .number(26000), .number(-9600)])161 let t = s.table()162 #expect(t.count == 10 && t[0][2] == .text("Best") && t[1][0] == .text("Changing Cells") && t[6][0] == .text("Result Cells"))163 #expect(t[7] == [.text("Profit!B7"), .number(2000), .number(13000), .number(-4800)])164 #expect(t.allSatisfy { $0.count == 4 })165 }166 let m = Model()167 let empty = try expectUntouched(m, .inPlace, edits: false, "empty summary") {168 try ScenarioManager.summary(scenarios: [], resultCells: [m.cell("B4")], engine: m.engine, isolation: .inPlace)169 }170 #expect(empty.columns == ["Current Values"] && empty.resultValues.count == 1)171 }172}173