SPB Git

Repository Public

ScenarioAndSimulationTests.swift 16.6 KB 268 lines swift

1import Foundation2import Testing3import SheetCore4import SheetUI5import Statistics6import WhatIf7import AnalysisKit8@testable import WhatIfTools910@MainActor @Suite struct ScenarioTests {11    func cells(_ s: WorkbookSession, _ text: String) -> [CellAddress] { RangeReader.cells(text, workbook: s.workbook, sheet: 0) ?? [] }1213    @Test func capturePersistsInTheWorkbookAttachment() throws {14        let s = makeModel()15        let captured = try ScenarioStore.capture(name: " Base ", comment: "as planned", cells: cells(s, "B1:B2"), session: s)16        #expect(captured.name == "Base" && captured.cells.map(\.value) == [.number(50), .number(1000)])17        let data = try #require(s.workbook.attachments[ScenarioManager.attachmentKey])18        let decoded = try JSONDecoder().decode(ScenarioSet.self, from: data)19        #expect(decoded.scenarios == [captured])20        // a clone of the workbook (what a save / reopen carries) reads the same set21        #expect(try ScenarioManager.load(from: s.workbook.clone()).scenarios.first?.comment == "as planned")22        // same name replaces23        s.perform("Edit") { $0.set(sheet: 0, row: 0, col: 1, .value(.number(55))) }24        try ScenarioStore.capture(name: "base", comment: "", cells: cells(s, "B1:B2"), session: s)25        #expect(ScenarioStore.load(s).scenarios.count == 1 && ScenarioStore.load(s).scenarios[0].cells[0].value == .number(55))26        #expect(throws: ToolError.self) { try ScenarioStore.capture(name: "  ", comment: "", cells: cells(s, "B1"), session: s) }27        #expect(throws: ToolError.self) { try ScenarioStore.capture(name: "x", comment: "", cells: [], session: s) }28    }2930    @Test func applyIsOneUndoStepAndRestoresFormulas() throws {31        let s = makeModel()32        try ScenarioStore.capture(name: "Base", comment: "", cells: cells(s, "B1:B2, B7"), session: s)      // B7 holds a formula33        var high = try #require(ScenarioStore.load(s).scenario(named: "Base"))34        high.id = UUID(); high.name = "High"35        high.cells[0].value = .number(60); high.cells[1].value = .number(1500); high.cells[2].value = .number(9)36        var set = ScenarioStore.load(s); set.upsert(high); try ScenarioStore.save(set, session: s)37        let before = snapshot(s.workbook)38        #expect(!s.engine.canUndo)39        try ScenarioStore.apply(high, session: s)40        let sh = s.workbook.sheets[0]41        #expect(sh.value(0, 1) == .number(60) && sh.value(1, 1) == .number(1500) && sh.value(6, 1) == .number(9))42        #expect(sh.cell(6, 1)?.formula == nil)43        #expect(sh.value(4, 1) == .number(33_000))                 // (60 − 30)·1500 − 12000, recalculated44        #expect(s.engine.undoLabel == "Apply Scenario")45        s.undo()46        #expect(snapshot(s.workbook) == before && !s.engine.canUndo)47        #expect(s.workbook.sheets[0].cell(6, 1)?.formula != nil)48        // applying the captured base puts the formula back as a formula49        try ScenarioStore.apply(high, session: s)50        try ScenarioStore.apply(try #require(ScenarioStore.load(s).scenario(named: "Base")), session: s)51        #expect(s.workbook.sheets[0].cell(6, 1)?.formula?.expr.formulaText() == "2+3" && s.workbook.sheets[0].value(4, 1) == .number(8000))52    }5354    @Test func applyFailsCleanlyWhenASheetIsGone() throws {55        let s = makeModel()56        let other = Scenario(name: "Ghost", cells: [ScenarioCell(sheetID: 999, cell: CellRef(0, 0), value: .number(1))])57        let before = LiveState(s)58        #expect(throws: ToolError.self) { try ScenarioStore.apply(other, session: s) }59        #expect(LiveState(s) == before)60    }6162    @Test func summaryComparesResultCellsAndIsWrittenAsOneStep() throws {63        let s = makeModel()64        let id = s.workbook.sheets[0].id65        var set = ScenarioSet()66        set.scenarios = [67            Scenario(name: "Low", cells: [ScenarioCell(sheetID: id, cell: CellRef(1, 1), value: .number(500))]),68            Scenario(name: "High", comment: "boom", cells: [ScenarioCell(sheetID: id, cell: CellRef(1, 1), value: .number(2000)),69                                                              ScenarioCell(sheetID: id, cell: CellRef(0, 1), value: .number(60))]),70        ]71        try ScenarioStore.save(set, session: s)72        try ScenarioStore.setResultCells(cells(s, "B5"), session: s)73        set = ScenarioStore.load(s)74        #expect(set.resultCells == [ScenarioResultCell(sheetID: id, cell: CellRef(4, 1))])75        let before = LiveState(s)76        let result = cells(s, "B5")77        let summary = try ScenarioStore.summary(set, resultCells: result, model: WhatIfSupport.snapshot(s))78        #expect(summary.resultValues == [[.number(8000), .number(-2000), .number(48_000)]])       // current, Low, High79        let report = ScenarioStore.report(summary, set: set, resultCells: result, workbook: s.workbook)80        let table = try #require(report.table(titled: "Result cells"))81        #expect(table.rows[0] == [.text("Cell"), .text("Address"), .text("Current values"), .text("Low"), .text("High")])82        #expect(table.rows[1] == [.text("Profit"), .text("M!B5"), .number(8000), .number(-2000), .number(48_000)])83        let changing = try #require(report.table(titled: "Changing cells"))84        #expect(changing.rows[1] == [.text("Units"), .text("M!B2"), .number(1000), .number(500), .number(2000)])85        #expect(changing.rows[2] == [.text("Price"), .text("M!B1"), .number(50), .number(50), .number(60)])   // Low keeps the current price86        #expect(LiveState(s) == before)87        let o = try ScenarioStore.writeSummary(session: s)88        #expect(s.workbook.sheets.count == 3 && s.workbook.sheets[o.sheet].value(0, 0) == .text("Scenario Summary"))89        s.undo()90        #expect(LiveState(s).cells == before.cells && s.workbook.sheets.count == 2)91    }9293    @Test func editingValuesParsesLikeACellEntry() throws {94        let s = makeModel()95        #expect(try ScenarioStore.parse("1,250.5", session: s) == .number(1250.5))96        #expect(try ScenarioStore.parse("=B1*2", session: s) == .formula("B1*2"))97        #expect(try ScenarioStore.parse("  ", session: s) == .blank)98        #expect(try ScenarioStore.parse("hello", session: s) == .text("hello"))99        #expect(ScenarioStore.editText(.number(0.5), locale: Locale(identifier: "fr_CA")) == "0,5")100        #expect(ScenarioStore.editText(.formula("B1*2"), locale: s.locale) == "=B1*2")101        try ScenarioStore.capture(name: "A", comment: "", cells: cells(s, "B1"), session: s)102        var a = try #require(ScenarioStore.load(s).scenarios.first)103        a.cells[0].value = .number(77)104        try ScenarioStore.update(a, session: s)105        #expect(ScenarioStore.load(s).scenarios[0].cells[0].value == .number(77))106        try ScenarioStore.delete(a.id, session: s)107        #expect(ScenarioStore.load(s).scenarios.isEmpty)108    }109}110111@MainActor @Suite struct MonteCarloTests {112    func bindings() -> [MCBinding] {113        var price = MCBinding.preset(cell: "M!B1", name: "", value: 50); price.kind = .triangular; price.p1 = 45; price.p2 = 50; price.p3 = 58114        let units = MCBinding.preset(cell: "B2", name: "Volume", value: 1000)115        var cost = MCBinding.preset(cell: "B3", name: "", value: 30); cost.kind = .uniform; cost.p1 = 28; cost.p2 = 33116        return [price, units, cost]117    }118119    func input(_ s: WorkbookSession, seed: Int, trials: Int = 2000, _ more: (inout FormValues) -> Void = { _ in },120               isCancelled: @escaping @Sendable () -> Bool = { false }) throws -> ToolInput {121        let tool = MonteCarloTool.descriptor122        var v = FormValues(defaults: tool.fields)123        v.set("bindings", text: MCBinding.encode(bindings())); v.set("outputs", text: "B5"); v.set("trials", int: trials); v.set("seed", int: seed)124        more(&v)125        return try ToolInputResolver.resolve(tool, values: v, session: s, isCancelled: isCancelled)126    }127128    @Test func sameSeedReproducesExactlyAndTheWorkbookIsUntouched() throws {129        let s = makeModel()130        let before = LiveState(s)131        let (setup, a) = try MonteCarloTool.simulate(try input(s, seed: 7))132        let (_, b) = try MonteCarloTool.simulate(try input(s, seed: 7))133        let (_, c) = try MonteCarloTool.simulate(try input(s, seed: 8))134        #expect(a.outputSamples == b.outputSamples && a.inputSamples == b.inputSamples && a.outputs[0].mean == b.outputs[0].mean)135        #expect(a.outputSamples != c.outputSamples)136        #expect(a.trialsCompleted == 2000 && !a.cancelled)137        #expect(setup.inputs.map(\.name) == ["Price", "Volume", "Unit cost"])138        // every recorded output equals the model algebra on the sampled inputs139        for t in stride(from: 0, to: 2000, by: 97) {140            let (p, u, k) = (a.inputSamples[0][t], a.inputSamples[1][t], a.inputSamples[2][t])141            #expect(abs(a.outputSamples[0][t] - ((p - k) * u - 12_000)) < 1e-6)142        }143        #expect(LiveState(s) == before)144    }145146    @Test func resultCarriesStatisticsChartsAndThreshold() throws {147        let s = makeModel()148        let r = try MonteCarloTool.run(try input(s, seed: 3) { $0.set("threshold", text: "8000") })149        let (_, raw) = try MonteCarloTool.simulate(try input(s, seed: 3))150        #expect(r.metric("Mean") == raw.outputs[0].mean)151        let above = Double(raw.outputSamples[0].filter { $0 > 8000 }.count) / 2000152        #expect(abs(r.metric("P(Profit > 8,000)") - above) < 1e-12)153        #expect(r.charts.count == 3)154        #expect(r.table(titled: "Percentiles of Profit")?.rows.count == 10)155        #expect(r.table(titled: "Input assumptions")?.rows.count == 4)156        #expect(r.table(titled: "Sensitivity of Profit to the inputs")?.rows.count == 4)157        let o = ReportWriter.write(r, to: .newSheet, includeCharts: true, session: s)158        #expect(o.chartCount == 3 && s.engine.undoLabel == "Insert Report")159    }160161    @Test func cancellationReturnsPartialResultsAndLeavesTheWorkbookUntouched() throws {162        let s = makeModel()163        let before = LiveState(s)164        let polls = Counter()165        let r = try MonteCarloTool.run(try input(s, seed: 1, trials: 4000, isCancelled: { polls.next() > 6 }))166        #expect(r.warnings.first?.contains("cancelled") == true)167        let (_, full) = try MonteCarloTool.simulate(try input(s, seed: 1, trials: 4000))168        let polls2 = Counter()169        let (_, partial) = try MonteCarloTool.simulate(try input(s, seed: 1, trials: 4000, isCancelled: { polls2.next() > 6 }))170        #expect(partial.cancelled && partial.trialsCompleted > 0 && partial.trialsCompleted < 4000)171        #expect(Array(full.outputSamples[0].prefix(partial.trialsCompleted)) == Array(partial.outputSamples[0].prefix(partial.trialsCompleted)))172        #expect(throws: CancellationError.self) { try MonteCarloTool.simulate(try self.input(s, seed: 1, isCancelled: { true })) }173        #expect(LiveState(s) == before)174    }175176    @Test func bindingsRoundTripThroughJSON() throws {177        var list = bindings()178        var d = MCBinding(); d.cell = "B4"; d.kind = .discrete; d.valuesText = "10000; 12000; 15000"; d.probabilitiesText = "0.2; 0.5; 0.3"179        list.append(d)180        let decoded = MCBinding.decode(MCBinding.encode(list))181        #expect(decoded == list)182        #expect(MCBinding.decode("not json").isEmpty)183        let en = Locale(identifier: "en_US")184        #expect(try decoded[3].distribution(locale: en) == .discrete(values: [10000, 12000, 15000], probabilities: [0.2, 0.5, 0.3]))185        #expect(try decoded[0].distribution(locale: en) == .triangular(min: 45, mode: 50, max: 58))186        var b = decoded[1]187        b.convert(to: .uniform)188        #expect(b.kind == .uniform && b.p1 == 800 && b.p2 == 1200)          // mean ± 2 sd189        b.convert(to: .pert)190        #expect(try b.distribution(locale: en) == .pert(min: 800, mode: 1000, max: 1200, lambda: 4))191    }192193    @Test func invalidSetupsAreRejectedWithAMessage() throws {194        let s = makeModel()195        var bad = MCBinding.preset(cell: "B1", name: "", value: 50); bad.kind = .uniform; bad.p1 = 9; bad.p2 = 1196        #expect(throws: ToolError.self) { try bad.distribution(locale: s.locale) }197        var log = MCBinding.preset(cell: "B1", name: "", value: 50); log.kind = .lognormal; log.p1 = -1198        #expect(throws: ToolError.self) { try log.distribution(locale: s.locale) }199        var disc = MCBinding(); disc.kind = .discrete; disc.valuesText = "1; 2"; disc.probabilitiesText = "1"200        #expect(throws: ToolError.self) { try disc.distribution(locale: s.locale) }201        func attempt(_ list: [MCBinding], _ more: (inout FormValues) -> Void = { _ in }) throws {202            _ = try MonteCarloTool.simulate(try input(s, seed: 1) { v in v.set("bindings", text: MCBinding.encode(list)); more(&v) })203        }204        #expect(throws: ToolError.self) { try attempt([]) }                                                        // no input205        #expect(throws: ToolError.self) { try attempt([bad]) }                                                     // invalid distribution206        #expect(throws: ToolError.self) { try attempt([.preset(cell: "B5", name: "", value: 1)]) }                 // formula as input207        #expect(throws: ToolError.self) { try attempt([.preset(cell: "B1", name: "", value: 1), .preset(cell: "M!B1", name: "", value: 1)]) }   // bound twice208        #expect(throws: ToolError.self) { try attempt(self.bindings()) { $0.set("threshold", text: "abc") } }209        #expect(throws: ToolError.self) { try attempt(self.bindings()) { $0.set("correlation", choice: "matrix"); $0.set("matrix", text: "1 0.5; 0.5 1") } }   // 2×2 for 3 inputs210        #expect(!s.engine.canUndo)211    }212213    @Test func rankCorrelationIsApplied() throws {214        let s = makeModel()215        let (setup, r) = try MonteCarloTool.simulate(try input(s, seed: 5, trials: 5000) { $0.set("correlation", choice: "common"); $0.set("rho", number: 0.6) })216        #expect(setup.options.correlation?.matrix == [[1, 0.6, 0.6], [0.6, 1, 0.6], [0.6, 0.6, 1]])217        let achieved = try #require(r.achievedInputRankCorrelation)218        #expect(abs(achieved[0][1] - 0.6) < 0.05 && abs(achieved[1][2] - 0.6) < 0.05)219        let m = try MCCorrelation.parse("1 0.3 0; 0.3 1 -0.2; 0 -0.2 1", count: 3, locale: Locale(identifier: "en_US"))220        #expect(m[1] == [0.3, 1, -0.2])221        #expect(try MCCorrelation.parse("1 0,3; 0,3 1", count: 2, locale: Locale(identifier: "fr_CA"))[0][1] == 0.3)222        #expect(throws: ToolError.self) { try MCCorrelation.parse("1 0.3; 0.4 1", count: 2, locale: Locale(identifier: "en_US")) }223        #expect(throws: ToolError.self) { try MCCorrelation.parse("1 2; 2 1", count: 2, locale: Locale(identifier: "en_US")) }224    }225226    @Test func selectionPresetsNormalDistributions() {227        let s = makeModel()228        s.select(RangeRef(a1: "B1:B5")!)229        let list = MCBinding.fromSelection(s, existing: [MCBinding.preset(cell: "B2", name: "", value: 1)])230        #expect(list.map(\.cell) == ["M!B1", "M!B3", "M!B4"])                 // B2 already bound, B5 is a formula231        #expect(list[0].kind == .normal && list[0].p1 == 50 && list[0].p2 == 5 && list[0].name == "Price")232    }233}234235@MainActor @Suite struct WhatIfCatalogTests {236    @Test func toolsAreRegisteredWithDemosAndModelIsolation() {237        let tools = WhatIfToolList.all238        #expect(tools.map(\.id) == ["goalseek", "breakeven", "datatable1", "datatable2", "scenarios", "tornado", "montecarlo"])239        for t in tools {240            #expect(t.demo != nil && !t.info.isEmpty && !t.title.isEmpty)241            #expect(t.needsModel || t.customPanel != nil)242        }243    }244245    @Test func catalogHasCanadianFrench() throws {246        let fr = try #require(AnalysisLocalization.localized(whatIfBundle, "fr-CA"))247        #expect(fr.localizedString(forKey: "Goal Seek", value: nil, table: nil) == "Valeur cible")248        #expect(fr.localizedString(forKey: "Monte Carlo Simulation", value: nil, table: nil) == "Simulation de Monte-Carlo")249    }250251    @Test func helpersNameAndFormatCells() {252        let s = makeModel()253        let wb = s.workbook254        let b5 = CellAddress(sheet: 0, cell: CellRef(a1: "B5")!)255        #expect(WhatIfSupport.name(b5, in: wb) == "Profit" && WhatIfSupport.label(b5, in: wb) == "M!B5")256        #expect(WhatIfSupport.nameWithAddress(CellAddress(sheet: 0, cell: CellRef(a1: "D9")!), in: wb) == "M!D9")257        #expect(WhatIfSupport.formulaText(b5, in: wb) == "=B1*B2-B3*B2-B4")258        s.perform("Format") { e in259            e.setStyle(sheet: 0, range: RangeRef(a1: "B1")!) { $0.numberFormat = "#,##0.00" }260            e.setStyle(sheet: 0, range: RangeRef(a1: "B2")!) { $0.numberFormat = "0.0%" }261        }262        #expect(WhatIfSupport.format(CellAddress(sheet: 0, cell: CellRef(0, 1)), in: wb) == .fixed(2))263        #expect(WhatIfSupport.format(CellAddress(sheet: 0, cell: CellRef(1, 1)), in: wb) == .percent(1))264        #expect(WhatIfSupport.format(b5, in: wb) == .auto)265        #expect(WhatIfSupport.binLabels(edges: [0, 0.2, 0.4], locale: Locale(identifier: "en_US")) == ["0.10", "0.30"])266    }267}268
Files main
Menu

Theme