Skip to content

About

Weekly timetable optimizer for an education centre: OR-Tools CP-SAT solver, Excel exchange and a Flask browser editor.

Topics

Resources

Stars

0 stars

Watchers

1 watching

Forks

Latest commit

 

History

39 Commits

Folders and files

NameName
Last commit message
Last commit date
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

Repository files navigation

ScheduleGen

Weekly timetable planning for a children's education centre. A constraint solver (Google OR-Tools CP-SAT) places group lessons so that teachers, groups and rooms never clash. Staff then maintain the week together in a browser editor, add individual, tutoring and trial lessons or room rentals, and publish the timetable.

I built it for Kinder- und Elternzentrum Kolibri e.V. in Dresden.

Browser editor showing a generated week

The browser editor with a fictional week of 24 lessons. The solver placed them in under a second; the input comes from examples/create_demo_week.py.

What it does

  • Reads lessons from an Excel planning workbook: teacher, group, main and alternative rooms, building, duration, a fixed start or a time window, linked lessons and pauses.
  • Finds conflict-free start times and rooms with CP-SAT and writes the timetable back to Excel.
  • Keeps the week in one SQLite database and serves a browser editor (Flask) where several people work at the same time, each within their role: every change is saved at once, other pages pick it up within seconds, and a change based on an outdated version is refused instead of overwriting someone else's work. The server checks every change for teacher, group and room clashes and highlights them for everyone.
  • Publishes the timetable as PDF and HTML straight from the database and refuses while lessons clash. Per-teacher and per-group timetables can still be printed from a solver result workbook.
  • Exports the database to Excel and closes the loop of preferences: export → planning workbook → solver → result loaded back into the database.
  • Runs on a Windows office PC with a small desktop control panel, daily database backups and restore from the browser.

How it works

flowchart LR
    A[Planning workbook<br/>Excel] --> B[Optimizer<br/>OR-Tools CP-SAT]
    B --> C[Schedule workbook]
    C -- load --> D[(Schedule database<br/>SQLite)]
    E[Browser editor<br/>Flask + JS] <--> D
    D --> F[Published timetable<br/>PDF / HTML]
    D -- export --> G[Excel export]
    G -- macro --> A
Loading

Try it

You need Windows and Python 3.13 or newer with Tcl/Tk. In PowerShell, from the repository folder:

py -3.13 -m venv .venv
.\.venv\Scripts\python.exe -m pip install -r requirements.txt
.\.venv\Scripts\python.exe examples/create_demo_week.py
.\.venv\Scripts\python.exe main_sch.py examples/generated/demo_week.xlsx --output examples/generated/demo_week_schedule.xlsx --time-limit 10 --time-interval 5

The first script writes a planning workbook with invented groups and teachers; the second solves it and saves the timetable as examples/generated/demo_week_schedule.xlsx. examples/create_demo_workbook.py is a two-lesson version that documents the input format cell by cell.

Plain .xlsx files are read and written with openpyxl, so the optimizer runs without Excel. The Excel automation used by the desktop workflow needs desktop Excel and pywin32. Dependency versions are not pinned. Optional raster, calendar and legacy HTML-to-PDF exports are listed in requirements-export.txt; they also need Poppler or wkhtmltopdf, which pip does not install.

Running the editor locally

The editor is meant for a local network, not for the open internet. To run your own instance:

  1. Copy config.example.json to config.json if you use the desktop app. Automatic copying of generated files is off until you set a destination.
  2. Create gear_xls/config/ and copy server.example.json to gear_xls/config/server.json (it binds to 127.0.0.1:5055) and users.example.json to gear_xls/config/users.json.
  3. The example user has an empty password hash, so nobody can log in yet. Set a password from the repository root with the virtual environment's Python; it is read without echo and never appears in a command line:
from getpass import getpass
import json
from pathlib import Path
import bcrypt

path = Path("gear_xls/config/users.json")
data = json.loads(path.read_text(encoding="utf-8"))
data["users"][0]["password_hash"] = bcrypt.hashpw(
    getpass("Local demo password: ").encode(), bcrypt.gensalt()
).decode()
path.write_text(json.dumps(data, indent=2) + "\n", encoding="utf-8")
  1. The editor keeps everything in gear_xls/schedule_state/schedgen.sqlite3. Create an empty database once; the same script also moves the files of the former JSON-based editor into it if they are there:
New-Item -ItemType Directory -Force gear_xls/schedule_state | Out-Null
.\.venv\Scripts\python.exe gear_xls/scripts/migrate_to_sqlite.py apply --source-root . --db gear_xls/schedule_state/schedgen.sqlite3 --report gear_xls/schedule_state/migration_report.json

Then start gear_xls/server_routes.py and load a solver result into the database with button 3 of the desktop app (gui.py); button 7 publishes the timetable from the database. The data contracts and the whole cycle are described in PROJECT_MAP.md, the editor in gear_xls/ARCHITECTURE.md. The server writes its database, backups, logs and a generated session key next to the code, so use a separate copy for experiments.

Working on the code

  • Existing config.json, gear_xls/config/, planning workbooks and generated state are local files. Keep them when you pull new code; the examples are templates, not replacements.
  • Ignore rules keep local data and outputs out of Git, but they can't spot a private value pasted into a tracked file. Look at git diff --cached before pushing.
  • JSON and text files are ignored by default. If a new source file or test fixture needs one of these formats, add a narrow exception after checking what's inside.

Tests run with pytest (install requirements-dev.txt) and Node. The editor tests build their database in temporary folders, but not every older script is isolated, so run them in a separate copy:

python -m pytest -q tests/test_sqlite_storage.py tests/test_sqlite_migration.py tests/test_schedule_api.py tests/test_schedule_api_rules.py tests/test_schedule_page.py tests/test_backups.py tests/test_publication.py tests/test_excel_db.py
node tests/lesson_edit_smoke.js

Where things are

Area Entry points
Optimizer and input main_sch.py, reader.py, scheduler_base.py, constraint modules
Desktop app gui.py, gui_services/, server_tray.py
Schedule database gear_xls/storage/, gear_xls/scripts/migrate_to_sqlite.py, gear_xls/backup_manager.py
Browser editor gear_xls/server_routes.py, gear_xls/api_routes.py, gear_xls/editor_page.py, gear_xls/static/, gear_xls/js_modules/
Excel exchange gear_xls/excel_db_export.py, gear_xls/excel_db_import.py, gear_xls/schedule_exchange.py, gear_xls/excel_parser.py, gear_xls/excel_exporter.py
Published timetables gear_xls/publication.py, visualiser/, visualiserTV/
Excel macros (VBA) xlsx_initial/PlanningMacros.bas, gear_xls/Modul1.bas

Parts of the interface and older module notes are in Russian or German, and the Excel layout follows one centre's planning sheet.

How it's built

I develop ScheduleGen with AI coding agents. I set the requirements, decide how the model and the data flow work, split changes into small steps with acceptance criteria, and test each step before it goes in. The agents (Claude Code and OpenAI Codex) write most of the code, and many commits name them in a Co-authored-by line.

License

The code is available under the MIT License. The two VBA modules are the exception: they belong to Kinder- und Elternzentrum Kolibri e.V. and may only be reused with its permission, see NOTICE.

About

Weekly timetable optimizer for an education centre: OR-Tools CP-SAT solver, Excel exchange and a Flask browser editor.

Topics

Resources

Stars

0 stars

Watchers

1 watching

Forks

Releases

Packages

Used by

Contributors

Languages