SPB Git

Repository Public

TemplateMergeTests.swift 17.8 KB 309 lines swift

1// =============================================================================2//                  SP BOUCHER | SOFTWARE & RESEARCH3//             ARTIFICIAL INTELLIGENCE • FINANCE • ENGINEERING4// =============================================================================5//6// PROJECT METADATA7// -----------------------------------------------------------------------------8// Project          : ORIX Sheet9// File             : Packages/SheetAutomation/Tests/SheetAutomationTests/TemplateMergeTests.swift10// Module           : SheetAutomationTests11// Repository       : https://git.spboucher.ai/orix-sheet12// Created          : 2026-11-2113// Last Modified    : 2026-11-2114//15// =============================================================================16// AUTHOR & PROFESSIONAL AFFILIATION17// =============================================================================18//19// Author           : Simon-Pierre Boucher20// Position         : Professor of Finance21// Institution      : Université du Québec en Outaouais (UQO)22// Department       : Département des sciences administratives23// Organization     : SP Boucher24//25// Website          : https://spboucher.ai26// Git              : https://git.spboucher.ai27//28// =============================================================================29// OFFICIAL CONTACT DIRECTORY30// =============================================================================31//32// General          : info@spboucher.ai33// Professional     : contact@spboucher.ai34// Direct           : simon@spboucher.ai35// Research         : research@spboucher.ai36// Development      : dev@spboucher.ai37// Support          : support@spboucher.ai38// Security/Admin   : admin@spboucher.ai39// Notifications    : notifications@spboucher.ai40//41// Preferred Contact: support@spboucher.ai42//43// =============================================================================44// COPYRIGHT & INTELLECTUAL PROPERTY45// =============================================================================46//47// Copyright (c) 202648// Simon-Pierre Boucher49//50// All rights reserved where applicable.51//52// This file is governed by the applicable project license,53// copyright notices, institutional agreements and relevant law.54//55// Unless expressly authorized by the applicable license or56// rights holder, copying, redistribution, sublicensing,57// publication and commercial exploitation are not permitted.58//59// Existing third-party rights and notices remain unaffected.60//61// =============================================================================62// CONFIDENTIALITY & SECURITY63// =============================================================================64//65// Classification   : Public66// Distribution     : Public repository; use is governed by the project license67// Security Contact : admin@spboucher.ai68//69// Access to this source does not independently grant permission70// to redistribute or disclose confidential materials.71//72// Do not embed credentials, private keys, access tokens,73// certificates or confidential personal information.74//75// =============================================================================76// LICENSE & MAINTENANCE77// =============================================================================78//79// License          : None specified (no license file in this repository)80// Maintainer       : Simon-Pierre Boucher81// Technical Contact: dev@spboucher.ai82//83// =============================================================================84// END OF SP BOUCHER SOURCE HEADER85// =============================================================================8687import Foundation88import Testing89import PDFKit90import SheetCore91import SheetEngine92import SheetIO93import SheetCharts94import SheetPython95@testable import SheetAutomation9697@Suite struct TemplateTests {98    @Test func csvReportTemplate() async throws {99        let dir = TestEnv.tempDir()100        let csv = TestEnv.salesCSV(dir)101        let (run, wb) = try await WorkflowEngine(host: TestEnv.host(dir)).run(WorkflowTemplates.csvReport, parameters: ["source": csv.path])102        expectSucceeded(run)103        let w = try #require(wb)104        #expect(w.sheets.map(\.name) == ["Data", "Summary"])105        #expect(w.sheet(named: "Data")!.cells.usedRange() == RangeRef(a1: "A1:C25"))106        #expect((2...5).map { TestEnv.value(w, "Summary", "A\($0)") } == ["Food", "Housing", "Leisure", "Transport"].map { .text($0) })107        var total = 0.0108        for r in 2...5 { total += TestEnv.value(w, "Summary", "B\(r)").numberValue ?? .nan }109        #expect(abs(total - TestEnv.salesTotal) < 1e-9)110        #expect(w.sheet(named: "Summary")!.meta.objects.map(\.name) == ["Summary chart"])111        let pdf = try #require(PDFDocument(url: dir.appendingPathComponent("sales-report.pdf")))112        let text = pdf.string ?? ""113        #expect(pdf.pageCount == 1 && text.contains("Sum of amount") && text.contains("Leisure") && text.contains("amount by category"))114        #expect(run.outputFiles == [dir.appendingPathComponent("sales-report.pdf").path])115        // other column names through parameters116        try Data("region,units\nNorth,3\nSouth,4\nNorth,5\n".utf8).write(to: dir.appendingPathComponent("units.csv"))117        let (r2, w2) = try await WorkflowEngine(host: TestEnv.host(dir)).run(WorkflowTemplates.csvReport,118            parameters: ["source": "units.csv", "category": "region", "value": "units", "output": "out/units.pdf"])119        expectSucceeded(r2)120        #expect(TestEnv.value(try #require(w2), "Summary", "B2") == .number(8))121        #expect(TestEnv.exists(dir.appendingPathComponent("out/units.pdf")))122        // a wrong column name fails cleanly123        let (r3, _) = try await WorkflowEngine(host: TestEnv.host(dir)).run(WorkflowTemplates.csvReport, parameters: ["source": "units.csv", "value": "nope", "output": "bad.pdf"])124        #expect(r3.status == .failed && r3.failedStep?.kind == .createPivot && !TestEnv.exists(dir.appendingPathComponent("bad.pdf")))125    }126127    @Test func refreshWorkbookTemplate() async throws {128        let dir = TestEnv.tempDir()129        TestEnv.modelXLSX(dir)130        try Data("category,amount\nHousing,1000\nFood,500\nTransport,100\nLeisure,50\nSavings,350\n".utf8).write(to: dir.appendingPathComponent("fresh.csv"))131        let (run, _) = try await WorkflowEngine(host: TestEnv.host(dir)).run(WorkflowTemplates.refreshWorkbook, parameters: ["file": "model.xlsx", "data": "fresh.csv"])132        expectSucceeded(run)133        let out = dir.appendingPathComponent("model-refreshed.xlsx")134        let wb = try TestEnv.load(out)135        #expect(TestEnv.value(wb, "Summary", "B1") == .number(2000) && TestEnv.value(wb, "Summary", "B2") == .number(5))136        #expect(wb.sheet(named: "Summary")?.cell(0, 1)?.formula != nil)137        if let seen = TestEnv.openpyxl("wb = openpyxl.load_workbook(r'\(out.path)')\nprint(wb['Data'].max_row, wb['Data']['A6'].value, wb['Summary']['B1'].value)") {138            #expect(seen == "6 Savings =SUM(Data!B:B)")139        }140        if let cached = TestEnv.openpyxl("wb = openpyxl.load_workbook(r'\(out.path)', data_only=True)\nprint(wb['Summary']['B1'].value, wb['Summary']['B2'].value)") {141            #expect(cached == "2000 5")142        }143        // the original is untouched144        #expect(TestEnv.value(try TestEnv.load(dir.appendingPathComponent("model.xlsx")), "Summary", "B1") == .number(1740.15))145        // data that breaks the model stops the save146        try Data("category,amount\nHousing,=1/0\n".utf8).write(to: dir.appendingPathComponent("broken.csv"))147        let wb2 = TestEnv.modelWorkbook()148        let e = CalcEngine(workbook: wb2)149        e.setFormula(sheet: 1, row: 4, col: 0, "=Data!B2/Data!B3")150        try WorkbookWriter.save(wb2, to: dir.appendingPathComponent("ratio.xlsx"), format: .xlsx)151        try Data("category,amount\nHousing,10\nFood,0\n".utf8).write(to: dir.appendingPathComponent("zero.csv"))152        let (bad, _) = try await WorkflowEngine(host: TestEnv.host(dir)).run(WorkflowTemplates.refreshWorkbook, parameters: ["file": "ratio.xlsx", "data": "zero.csv"])153        #expect(bad.status == .failed && bad.failedStep?.kind == .validate)154        #expect(bad.failedStep?.issues.contains("Summary!A5 holds #DIV/0!") == true)155        #expect(!TestEnv.exists(dir.appendingPathComponent("ratio-refreshed.xlsx")))156    }157158    @Test func batchConvertTemplate() async throws {159        let dir = TestEnv.tempDir()160        for n in 1...4 { TestEnv.salesCSV(dir, name: "export-\(n).csv") }161        try Data([0xFF, 0xFE, 0x00, 0x00, 0x00]).write(to: dir.appendingPathComponent("export-9.csv"))     // may or may not load; must not stop the batch162        let (run, _) = try await WorkflowEngine(host: TestEnv.host(dir)).run(WorkflowTemplates.batchConvert, parameters: ["folder": dir.path])163        #expect(run.status == .succeeded || run.status == .completedWithErrors)164        let out = try FileManager.default.contentsOfDirectory(atPath: dir.appendingPathComponent("xlsx").path).sorted()165        #expect(Array(out.prefix(4)) == (1...4).map { "export-\($0).xlsx" })166        let wb = try TestEnv.load(dir.appendingPathComponent("xlsx/export-3.xlsx"))167        #expect(wb.sheets[0].cells.usedRange() == RangeRef(a1: "A1:C27") && wb.sheets[0].value(1, 2) == .number(100.5))168        if let seen = TestEnv.openpyxl("wb = openpyxl.load_workbook(r'\(dir.path)/xlsx/export-1.xlsx')\nws = wb.active\nprint(ws.max_row, ws['B2'].value.strip(), ws['C2'].value)") {169            #expect(seen == "27 Housing 100.5")170        }171    }172173    @Test func monthlyReportTemplate() async throws {174        let dir = TestEnv.tempDir()175        let csv = TestEnv.salesCSV(dir, name: "tx.csv")176        let (run, wb) = try await WorkflowEngine(host: TestEnv.host(dir)).run(WorkflowTemplates.monthlyReport, parameters: ["data": csv.path, "period": "2026-11"])177        expectSucceeded(run)178        let w = try #require(wb)179        #expect(TestEnv.value(w, "Report", "A1") == .text("Monthly report 2026-11"))180        let november = (12..<24).reduce(0.0) { $0 + 100 + Double($1) * 10 + 0.5 }181        #expect(TestEnv.value(w, "Report", "E3") == .number(november))182        #expect(w.sheet(named: "Report")!.cells.usedRange()?.r1 == 6)                 // title, blank, header, 4 categories183        #expect(TestEnv.value(w, "Report", "B4").numberValue! >= TestEnv.value(w, "Report", "B5").numberValue!)184        let pdf = try #require(PDFDocument(url: dir.appendingPathComponent("report-2026-11.pdf")))185        #expect(pdf.pageCount == 1 && (pdf.string ?? "").contains("Monthly report 2026-11") && (pdf.string ?? "").contains("Total by category"))186        let xlsx = try TestEnv.load(dir.appendingPathComponent("report-2026-11.xlsx"))187        #expect(TestEnv.value(xlsx, "Report", "E3") == .number(november))188        #expect(xlsx.sheet(named: "Report")?.cell(2, 4)?.formula != nil)189        #expect(run.stepResults.last?.issues.contains { $0.contains("[loss] Objects") } == true)     // the chart is not exported to XLSX, and says so190        // a month without data fails at validation and writes nothing191        let (emptyMonth, _) = try await WorkflowEngine(host: TestEnv.host(dir)).run(WorkflowTemplates.monthlyReport, parameters: ["data": csv.path, "period": "2031-01"])192        #expect(emptyMonth.status == .failed && emptyMonth.failedStep?.kind == .validate)193        #expect(!TestEnv.exists(dir.appendingPathComponent("report-2031-01.pdf")))194    }195196    @Test func approvedPythonScriptRunsRestricted() async throws {197        PythonPaths.supportDirectory = TestEnv.support198        guard await PythonDiscovery.discover().contains(where: { $0.isSupported }), await PythonSandbox.unavailabilityReason() == nil else { return }199        let dir = TestEnv.tempDir()200        let wb = TestEnv.modelWorkbook()201        let script = PythonScript(name: "Noop", code: "total = sum(range(10))\nprint(total)\n")202        PythonScripts.save(script, to: wb)203        PythonTrust.trust(codeHash: script.codeHash, workbookID: PythonScripts.workbookID(wb), restricted: true, scriptName: script.name)204        let (run, out) = try await TestEnv.run([.runPython(RunPythonSettings(script: "noop"))], workbook: wb.clone(), in: dir)205        expectSucceeded(run)206        #expect(out != nil && run.stepResults[0].message.contains("restricted mode"))207        // editing the script invalidates the approval208        var edited = script209        edited.code += "print('changed')\n"210        PythonScripts.save(edited, to: wb)211        let (refused, _) = try await TestEnv.run([.runPython(RunPythonSettings(script: "Noop"))], workbook: wb.clone(), in: dir)212        #expect(refused.status == .failed && refused.message.contains("Script not approved"))213    }214}215216@Suite struct MergeTests {217    /// A text picture of everything a merge must reproduce.218    func fingerprint(_ wb: Workbook) -> [String] {219        var out: [String] = ["names:" + wb.names.keys.sorted().joined(separator: ",")]220        for sheet in wb.sheets {221            var cells: [String] = []222            sheet.cells.forEachAll { r, c, cell in223                guard !cell.isSpill else { return }224                let content = cell.formula.map { "=" + $0.expr.formulaText() } ?? cell.value.description225                let style = wb.styles[cell.style]226                cells.append("\(CellRef(r, c).a1)|\(content)|\(style.bold)|\(style.numberFormat)|\(style.fill?.hexString ?? "-")")227            }228            out.append("\(sheet.name)#\(sheet.meta.colWidths.sorted { $0.key < $1.key })#\(sheet.meta.objects.map(\.name))#" + cells.sorted().joined(separator: ";"))229        }230        return out231    }232233    @Test func applyReproducesTheResultAsOneUndoStep() async throws {234        let dir = TestEnv.tempDir()235        TestEnv.salesCSV(dir)236        let live = TestEnv.modelWorkbook()237        let engine = CalcEngine(workbook: live)238        engine.recalculate()239        engine.clearUndoHistory()240        let before = fingerprint(live)241        let (run, result) = try await TestEnv.run([242            .setCell(SetCellSettings(sheet: "Data", cell: "B2", input: "5000")),243            .formatRange(FormatRangeSettings(range: "Data!A1:B1", bold: .on, fill: "FFEE00", columnWidth: 140)),244            .formatRange(FormatRangeSettings(range: "Data!B:B", numberFormat: "#,##0.00")),245            .importFile(ImportFileSettings(path: "sales.csv", sheet: "Imported")),246            .addSheet(SheetNameSettings(name: "Scratch")),247            .renameSheet(RenameSheetSettings(from: "Summary", to: "Overview")),248            .createPivot(CreatePivotSettings(range: "Imported!*", rows: ["category"], values: ["sum(amount)"], sheet: "Pivot")),249            .createChart(CreateChartSettings(range: "Data!A1:B4", name: "Chart A")),250            .setCell(SetCellSettings(sheet: "Overview", cell: "A2", input: "")),251            .setFormulaRange(SetFormulaRangeSettings(range: "Overview!D1:D3", formula: "=Data!B2*2")),252            .deleteSheet(SheetNameSettings(name: "Scratch")),253        ], workbook: live.clone(), in: dir)254        expectSucceeded(run)255        let target = try #require(result)256        #expect(fingerprint(live) == before)                                   // running on the clone changed nothing257258        let summary = WorkbookMerge.apply(target, to: engine, label: "Run Workflow “Test”")259        engine.recalculate()260        #expect(fingerprint(live) == fingerprint(target))261        #expect(live.sheets.map(\.name) == ["Data", "Overview", "Imported", "Pivot"])262        #expect(TestEnv.value(live, "Overview", "B1") == .number(5000 + 450.25 + 89.9))      // formulas are live after the merge263        #expect(TestEnv.value(live, "Overview", "D2") == .number(900.5))264        #expect(summary.sheetsAdded == 2 && summary.sheetsRenamed == 1 && summary.sheetsRemoved == 0 && summary.cellsChanged > 80)265        #expect(engine.undoLabel == "Run Workflow “Test”")266267        #expect(engine.undo() == "Run Workflow “Test”")268        engine.recalculate()269        #expect(fingerprint(live) == before)270        #expect(!engine.canUndo)                                               // it was a single step271        #expect(TestEnv.value(live, "Summary", "B1") == .number(1740.15))272        engine.redo()273        engine.recalculate()274        #expect(fingerprint(live) == fingerprint(target))275    }276277    @Test func applyHandlesRemovedSheetsNamesAndNoChange() async throws {278        let dir = TestEnv.tempDir()279        let live = TestEnv.modelWorkbook()280        let engine = CalcEngine(workbook: live)281        engine.setDefinedName(DefinedName(name: "Rate", formula: "0.05"))282        engine.setDefinedName(DefinedName(name: "Old", formula: "Data!$A$1"))283        engine.recalculate()284        engine.clearUndoHistory()285        // nothing changed → nothing recorded286        let same = WorkbookMerge.apply(live.clone(), to: engine)287        #expect(same.isEmpty && !engine.canUndo)288289        let (run, result) = try await TestEnv.run([.addSheet(SheetNameSettings(name: "Only")), .setCell(SetCellSettings(sheet: "Only", cell: "A1", input: "=1+1")),290                                                   .deleteSheet(SheetNameSettings(name: "Summary")), .deleteSheet(SheetNameSettings(name: "Data")),291                                                   .renameSheet(RenameSheetSettings(from: "Only", to: "Data"))], workbook: live.clone(), in: dir)292        expectSucceeded(run)293        let target = try #require(result)294        target.names["OLD"] = nil295        target.setDefinedName(DefinedName(name: "Fresh", formula: "1"))296        target.attachments["workflows/x.json"] = Data("{}".utf8)297        let summary = WorkbookMerge.apply(target, to: engine)298        engine.recalculate()299        #expect(live.sheets.map(\.name) == ["Data"] && TestEnv.value(live, "Data", "A1") == .number(2))300        #expect(summary.sheetsRemoved == 2 && summary.sheetsAdded == 1)301        #expect(live.names.keys.sorted() == ["FRESH", "RATE"])302        #expect(live.attachments["workflows/x.json"] != nil)303        engine.undo()304        engine.recalculate()305        #expect(live.sheets.map(\.name) == ["Data", "Summary"] && live.names.keys.sorted() == ["OLD", "RATE"])306        #expect(TestEnv.value(live, "Summary", "B1") == .number(1740.15))307    }308}309
Files main
Menu

Theme