SPB Git

Repository Public

ScenarioStore.swift 10.0 KB 191 lines swift

1import Foundation2import SheetCore3import SheetUI4import Statistics5import WhatIf6import AnalysisKit78/// Scenario Manager operations on a live session. Scenarios are persisted by `ScenarioManager` in9/// `workbook.attachments["scenarios.json"]`.10///11/// Applying a scenario is one undoable step. Changes of the STORED set (create, edit, delete, result cells) are saved12/// immediately and mark the document edited, but SheetCore's undo does not track workbook attachments.13@MainActor enum ScenarioStore {14    static func load(_ session: WorkbookSession) -> ScenarioSet {15        (try? ScenarioManager.load(from: session.workbook)) ?? ScenarioSet()16    }1718    static func save(_ set: ScenarioSet, session: WorkbookSession) throws {19        try ScenarioManager.save(set, to: session.workbook)20        session.onChange?()21        session.invalidate()22    }2324    static func address(sheetID: Int, cell: CellRef, in wb: Workbook) -> CellAddress? {25        wb.sheetIndex(id: sheetID).map { CellAddress(sheet: $0, cell: cell) }26    }2728    /// Display label of a stored cell ("Unit price (Model!B4)"), or a note that its sheet was deleted.29    static func label(sheetID: Int, cell: CellRef, in wb: Workbook) -> String {30        address(sheetID: sheetID, cell: cell, in: wb).map { WhatIfSupport.nameWithAddress($0, in: wb) } ?? L("%@ (sheet deleted)", cell.a1)31    }3233    /// Captures the CURRENT content of the cells as a scenario and stores it (replacing a scenario of the same name).34    @discardableResult35    static func capture(name: String, comment: String, cells: [CellAddress], session: WorkbookSession) throws -> Scenario {36        let trimmed = name.trimmingCharacters(in: .whitespaces)37        guard !trimmed.isEmpty else { throw ToolError(L("Give the scenario a name.")) }38        guard !cells.isEmpty else { throw ToolError(L("Choose the input cells of the scenario.")) }39        guard cells.count <= 500 else { throw ToolError(L("A scenario can hold at most %d cells.", 500)) }40        let s = try ScenarioManager.capture(name: trimmed, comment: comment, cells: cells.map(\.modelCell), engine: session.engine)41        var set = load(session)42        set.upsert(s)43        try save(set, session: session)44        return s45    }4647    static func update(_ scenario: Scenario, session: WorkbookSession) throws {48        var set = load(session)49        guard let i = set.scenarios.firstIndex(where: { $0.id == scenario.id }) else { return }50        set.scenarios[i] = scenario51        try save(set, session: session)52    }5354    static func delete(_ id: UUID, session: WorkbookSession) throws {55        var set = load(session)56        set.scenarios.removeAll { $0.id == id }57        try save(set, session: session)58    }5960    static func setResultCells(_ cells: [CellAddress], session: WorkbookSession) throws {61        var set = load(session)62        set.resultCells = cells.map { ScenarioResultCell(sheetID: session.workbook.sheets[$0.sheet].id, cell: $0.cell) }63        try save(set, session: session)64    }6566    static func resultAddresses(_ set: ScenarioSet, in wb: Workbook) -> [CellAddress] {67        set.resultCells.compactMap { address(sheetID: $0.sheetID, cell: $0.cell, in: wb) }68    }6970    static func cellInput(_ value: ScenarioValue) throws -> CellInput {71        switch value {72        case .blank: return .blank73        case .number(let n): return .value(.number(n))74        case .text(let s): return .value(.text(s))75        case .bool(let b): return .value(.bool(b))76        case .formula(let t):77            guard let e = try? FormulaParser.parse(t) else { throw ToolError(L("The stored formula “=%@” is no longer valid.", t)) }78            return .formula(e)79        }80    }8182    /// Writes the scenario's values into the live workbook as ONE undoable step ("Apply Scenario").83    /// Nothing is modified when a cell of the scenario no longer exists.84    static func apply(_ scenario: Scenario, session: WorkbookSession) throws {85        let wb = session.workbook86        var edits = [(CellAddress, CellInput)]()87        for sc in scenario.cells {88            guard let a = address(sheetID: sc.sheetID, cell: sc.cell, in: wb) else {89                throw ToolError(L("The scenario “%@” refers to a sheet that no longer exists. Nothing was changed.", scenario.name))90            }91            edits.append((a, try cellInput(sc.value)))92        }93        session.perform(L("Apply Scenario")) { e in94            for (a, input) in edits { e.set(sheet: a.sheet, row: a.cell.row, col: a.cell.col, input) }95        }96    }9798    // MARK: Editing values as text99100    static func editText(_ v: ScenarioValue, locale: Locale) -> String {101        switch v {102        case .blank: return ""103        case .number(let n): return Value.generalString(n).replacingOccurrences(of: ".", with: locale.decimalSeparator ?? ".")104        case .text(let s): return s105        case .bool(let b): return b ? "TRUE" : "FALSE"106        case .formula(let t): return "=" + t107        }108    }109110    /// Parses what the user typed the way a cell entry would be parsed (numbers in the session locale, "=" formulas).111    static func parse(_ text: String, session: WorkbookSession) throws -> ScenarioValue {112        let t = text.trimmingCharacters(in: .whitespaces)113        if t.isEmpty { return .blank }114        guard let parsed = try? session.parser.parse(t) else { throw ToolError(L("“%@” is not a valid value or formula.", t)) }115        switch parsed.input {116        case .blank: return .blank117        case .value(let v): return ScenarioValue(v)118        case .formula(let e): return .formula(e.formulaText())119        }120    }121122    // MARK: Summary123124    /// Compares the result cells across the scenarios on an isolated copy of the model.125    nonisolated static func summary(_ set: ScenarioSet, resultCells: [CellAddress], model: CalcEngine) throws -> ScenarioSummary {126        try ScenarioManager.summary(scenarios: set.scenarios, resultCells: resultCells.map(\.modelCell), engine: model)127    }128129    /// The summary as a report: changing cells and result cells, one column per scenario (current values first).130    nonisolated static func report(_ summary: ScenarioSummary, set: ScenarioSet, resultCells: [CellAddress], workbook wb: Workbook) -> ToolResult {131        let header: [Value] = [.text(L("Cell")), .text(L("Address"))] + [.text(L("Current values"))] + set.scenarios.map { .text($0.name) }132        // changing cells in the order of the summary (union over scenarios, first appearance)133        var changing = [CellAddress]()134        for s in set.scenarios { for c in s.cells {135            if let si = wb.sheetIndex(id: c.sheetID) { let a = CellAddress(sheet: si, cell: c.cell); if !changing.contains(a) { changing.append(a) } }136        } }137        func table(_ title: String, _ cells: [CellAddress], _ labels: [String], _ values: [[Value]]) -> ReportTable {138            var t = ReportTable(title: title, rows: [header] + values.enumerated().map { i, row in139                [.text(i < cells.count ? WhatIfSupport.name(cells[i], in: wb) : labels[i]), .text(i < cells.count ? WhatIfSupport.label(cells[i], in: wb) : "")] + row140            })141            for i in values.indices where i < cells.count { t.rowFormats[i + 1] = WhatIfSupport.format(cells[i], in: wb) }142            return t143        }144        var blocks: [ResultBlock] = [145            .table(table(L("Changing cells"), changing, summary.changingCellLabels, summary.changingValues)),146        ]147        if summary.resultValues.isEmpty {148            blocks.append(.note([L("No result cells were chosen: the summary lists the changing cells only.")]))149        } else {150            blocks.append(.table(table(L("Result cells"), resultCells, summary.resultCellLabels, summary.resultValues)))151        }152        var notes = [L("“Current values” shows the workbook as it is now; each scenario column shows the result cells recalculated with that scenario's values.")]153        for s in set.scenarios where !s.comment.isEmpty { notes.append("\(s.name): \(s.comment)") }154        blocks.append(.note(notes))155        return ToolResult(title: L("Scenario Summary"), subtitle: L("%d scenarios, %d result cells", set.scenarios.count, resultCells.count),156                          blocks: blocks, sheetName: L("Scenario Summary"))157    }158159    /// Computes the summary now (synchronously) and writes it to a new sheet as one undoable step.160    @discardableResult161    static func writeSummary(session: WorkbookSession) throws -> ReportWriter.Outcome {162        let set = load(session)163        guard !set.scenarios.isEmpty else { throw ToolError(L("Create at least one scenario first.")) }164        let cells = resultAddresses(set, in: session.workbook)165        let model = WhatIfSupport.snapshot(session)166        let s = try summary(set, resultCells: cells, model: model)167        return ReportWriter.write(report(s, set: set, resultCells: cells, workbook: model.workbook), to: .newSheet, includeCharts: false, session: session)168    }169170    // MARK: Demo (preview)171172    /// Base / Optimistic / Pessimistic scenarios on the demo workbook's `Model` sheet.173    static func installDemo(_ session: WorkbookSession) {174        guard let si = session.workbook.sheetIndex(named: "Model") else { return }175        let id = session.workbook.sheets[si].id176        let cells = [CellRef(a1: "B4")!, CellRef(a1: "B5")!, CellRef(a1: "B6")!]177        func scenario(_ name: String, _ comment: String, _ v: [Double]) -> Scenario {178            Scenario(name: name, comment: comment, cells: zip(cells, v).map { ScenarioCell(sheetID: id, cell: $0, value: .number($1)) })179        }180        var set = ScenarioSet()181        set.scenarios = [182            scenario("Base", "Current plan", [48, 12_000, 29]),183            scenario("Optimistic", "Higher price and volume, lower unit cost", [52, 14_000, 27]),184            scenario("Pessimistic", "Price pressure and weaker demand", [44, 9_500, 31]),185        ]186        set.resultCells = ["B12", "B15", "B17"].map { ScenarioResultCell(sheetID: id, cell: CellRef(a1: $0)!) }187        try? save(set, session: session)188        if ProcessInfo.processInfo.environment["ORIX_WHATIF_DEMO_WRITE_SUMMARY"] != nil { _ = try? writeSummary(session: session) }189    }190}191
Files main
Menu

Theme