SPB Git

Repository Public

DataTableScenarioTests.swift 10.9 KB 173 lines swift

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
Files main
Menu

Theme