Repository Public
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