r2p-to-optima

Migrates payroll and HR data from Symfonia R2P to Comarch ERP Optima. Both run on SQL Server, neither vendor publishes any kind of bridge, so this fills the gap.

Polish accounting firms often keep dozens of client companies in Optima while the source data still lives in R2P. Doing that by hand across more than a couple of companies is hopeless. The Optima side of the schema mapping was figured out the hard way - by running SQL Profiler against a live Optima instance while clicking through the GUI - because Comarch doesn't document it.

The pipeline is:

1. r2p/r2p_export_automated.py pulls each company out of R2P into a flat multi-record CSV keyed by NIP.
2. modular_import.py reads those CSVs and emits one .sql file per company, full of IF NOT EXISTS / MERGE-style statements.
3. You run that file in SSMS or sqlcmd against the target Optima company database.

The generated SQL is re-runnable. Nothing is deleted, duplicates are skipped, and FK constraints are flipped off around the bulk inserts and turned back on with diagnostics afterwards so anything that would have silently violated a constraint is loud instead.

Layout

r2p/r2p_export_automated.py     R2P -> CSV exporter
r2p/exports/                    output CSVs, <Company>_<NIP>_complete.csv

modular_import.py               entry point - scans exports/, dispatches per record type
base_importer.py                CSV parsing + SQL/MERGE generation
safe_base_importer.py           runtime wrapper that adds FK toggling and TRY/CATCH
field_mappings.py               R2P column -> CDN column tables

employees/                      owners (PraId 1..N) and employees (N+1..)
payroll/                        ListyPlac, Wyplaty, WypElementy
contract/                       wage garnishments (ZajeciaWynagr)
reference/                      Stanowiska (job positions)
absence/                        absence types, sick leave
deductions/                     deductions, PIT
elements/                       payment-element breakdown
special/                        qualifications, calendars, work types

utils/schema_mapper.py          orders INSERT columns to match CDN.PracKod / PracEtaty etc.
utils/reference_data.py         default IDs (DZL_ID, DDF_ID, KAL_ID, ...) from a fresh Optima install
utils/owner_extractor.py        owner name parser for company strings

export/                         generated .sql files (gitignored)

Setup

Python 3.10+ and Microsoft ODBC Driver 17 for SQL Server. Then:

pip install -r requirements.txt
cp .env.example .env

Fill in .env:

DB_SERVER=localhost\SQLEXPRESS
DB_DATABASE=payroll_db
DB_USERNAME=sa
DB_PASSWORD=...

DB_SERVER_R2P=localhost\SYMFONIAR2P

Running

Export:

python r2p/r2p_export_automated.py

Writes r2p/exports/<CompanyName>_<NIP>_complete.csv for every company found.

Generate SQL:

python modular_import.py

Iterates over r2p/exports/, prints per-company counts, drops .sql files into export/. If a CSV has no owner rows the orchestrator pulls one out of the company name. For silent partners or owners missing from R2P, add them to EXTRA_OWNERS in modular_import.py:

EXTRA_OWNERS = {
    "0000000000": [{"Imie": "Jan", "Nazwisko": "Przykladowy", "PESEL": ""}],
}

Apply against the target Optima database:

sqlcmd -S localhost\SQLEXPRESS -d CDN_PrzykladowaFirma \
       -i "export\Przykladowa_Firma_Sp_z_o_o_20250315_143022.sql"

All company names and NIPs in this README are made up.

CSV format

One CSV per company. Each row has a TypRekordu discriminator that picks the target table:

TypRekordu	Target	Notes
OWNER	CDN.PracKod / CDN.PracEtaty	Business owners, PraId 1..N
PRACOWNIK	CDN.PracKod / CDN.PracEtaty	Employees, PraId N+1..
LISTAPLAC	CDN.ListyPlac	Payroll list header
WYPLATA_SZCZEGOLY	CDN.Wyplaty / CDN.WypElementy	Per-employee payment detail
UMPRACA	CDN.PracEtaty	UoP
UMZLEC	CDN.PracEtaty	UZ
UMDZIELO	CDN.PracEtaty	UD
PRACDANE	CDN.DaneKadMod	Personal / HR data

Gotchas

• Owners live in the same tables as employees (CDN.PracKod / CDN.PracEtaty) and Optima expects them at the lowest PRA_PraId positions, employees after. @basePraId is computed at runtime from the target DB, so the offset is whatever the existing data demands. After import, double-check owner positions if anything downstream (PracPit, accounting module) references owners by position - a wrong offset there shows up as wrong tax numbers, not as an error.
• Optima has circular FKs across the payroll tables. The generated SQL disables them around the inserts and re-enables them with WITH CHECK CHECK CONSTRAINT + diagnostics afterwards. If something would have failed silently, it raises.
• Reference IDs in utils/reference_data.py (DZL_ID, DDF_ID, KAL_ID, ...) are the defaults Optima creates on a clean install. If the target was customised, replace them.
• import_wypelementy_nocache.py exists because the cached version caches FK lookups, and on databases with heavy concurrent writes that cache can go stale and produce dangling references. Slower but correct - swap it in if you start seeing FK violations on CDN.WypElementy.
• SafeBaseImporter wraps any importer class at runtime by building a new type via type() and MRO, instead of subclassing. That way the FK toggling and TRY/CATCH wrapping are applied uniformly without touching the domain importers.
• The Optima schema drifts between versions. The mappings here track one specific build. If you're on something newer or older, sanity-check the affected tables before running anything destructive.

Tech

Python 3.10+, pyodbc, rich, python-dotenv, SQL Server on both ends.

License

MIT, see LICENSE.

Symfonia R2P is a trademark of Asseco Poland S.A. Comarch ERP Optima is a trademark of Comarch S.A. This project is independent and not affiliated with either.