Leantime Timesheets Exporter & Mailer

Exports timesheets from Leantime via JSON-RPC (API key), converts timestamps UTC → local calendar dates, writes a CSV, and sends a summary email per user. Runs once or on a cron schedule in a tiny Alpine-based container.

Highlights

• 🕒 Correct UTC→local conversion (e.g., Europe/Zurich) with robust date parsing
• 📅 Period presets: last_day, last_week, last_month (for cron automation)
• 📤 CSV output + HTML email summary (optional attachment)
• 👤 Strict per-user filtering (no cross-user rows)
• 🧠 Project cache & smart Client // Project detection to fill the Client column
• 📧 Skips email if a user has no hours in the period
• 🔐 Auth via API key (x-api-key) to Leantime JSON-RPC

---

CSV Format

Header (semicolon-separated):

date;hours;client;projectId;projectName;ticketId;ticketTitle;type;description

Dates use CSV_DATE_STRFTIME (default "%d.%m.%Y" → e.g., 22.09.2025).

---

Quick Start

Docker Run

docker run --rm \
  -e LEANTIME_API_URL="https://planner.example.com/api/jsonrpc" \
  -e LEANTIME_API_KEY="YOUR_API_KEY" \
  -e LEANTIME_USERS="alice@example.com,bob@example.com" \
  -e LEANTIME_PERIOD="last_week" \
  -e LOCAL_TZ="Europe/Zurich" \
  -e SMTP_HOST="smtp.office365.com" \
  -e SMTP_PORT=587 \
  -e SMTP_STARTTLS=true \
  -e SMTP_USERNAME="support@example.com" \
  -e SMTP_PASSWORD="****" \
  -e SMTP_FROM="Timesheets Bot <support@example.com>" \
  -v $(pwd)/out:/app/out \
  onesystems/leantime-timesheets:latest

Docker Compose

services:
  leantime-timesheets:
    image: onesystems/leantime-timesheets:latest
    restart: unless-stopped
    env_file: .env          # optional, or use environment: below
    environment:
      LEANTIME_API_URL: https://planner.example.com/api/jsonrpc
      LEANTIME_API_KEY: ${LEANTIME_API_KEY}
      LEANTIME_USERS: alice@example.com, bob@example.com
      LEANTIME_PERIOD: last_week          # last_day | last_week | last_month
      LOCAL_TZ: Europe/Zurich
      CSV_DATE_STRFTIME: "%d.%m.%Y"
      SMTP_HOST: smtp.office365.com
      SMTP_PORT: 587
      SMTP_STARTTLS: "true"
      SMTP_SSL: "false"
      SMTP_USERNAME: support@example.com
      SMTP_PASSWORD: ${SMTP_PASSWORD}
      SMTP_FROM: "Timesheets Bot <support@example.com>"
      # Optional:
      # SMTP_EXTRA_TO: manager@example.com
      # ADMIN_CC:
      # TIMESHEETS_BCC_ALL:
      # STRICT_USER_FILTER: "true"
      # RESOLVE_TICKET_TITLES: "false"
      # DEBUG: "false"
      # CRON_SCHEDULE: "0 7 * * 1"  # run every Monday 07:00
    volumes:
      - ./out:/app/out

If CRON_SCHEDULE is not set, the container runs the job once and exits. If set (e.g., "0 7 * * 1"), cron runs the job periodically and the container stays up.

---

Environment Variables

Leantime API (required)

• LEANTIME_API_URL – e.g., https://planner.example.com/api/jsonrpc
• LEANTIME_API_KEY – API key for x-api-key header

Time range & users

• LEANTIME_PERIOD – last_day | last_week | last_month
• LEANTIME_USERS – comma/semicolon-separated list of emails
• LEANTIME_START, LEANTIME_END – YYYY-MM-DD (overrides LEANTIME_PERIOD if both set)

Timezones & formatting

• LOCAL_TZ – local zone for calendar dates (default Europe/Zurich)
• SERVER_TZ – ignored for epoch; kept for compatibility (default UTC)
• CSV_DATE_STRFTIME – default "%d.%m.%Y"

Email (optional but recommended)

• SMTP_HOST, SMTP_PORT
• SMTP_STARTTLS (true/false), SMTP_SSL (false/true)
• SMTP_USERNAME, SMTP_PASSWORD
• SMTP_FROM – e.g., Timesheets Bot <support@example.com>
• SMTP_EXTRA_TO – extra comma/semicolon-separated recipients
• ADMIN_CC, TIMESHEETS_BCC_ALL – optional lists

Behavior toggles

• STRICT_USER_FILTER – default true (keeps only rows belonging to target user)
• RESOLVE_TICKET_TITLES – default false (reserved for future enrichments)
• DEBUG – default false (prints RPC calls and caches)

Scheduling

• CRON_SCHEDULE – empty = run once; otherwise standard crontab (e.g., 0 7 * * 1)
• Container timezone is controlled by TZ (default Europe/Zurich in the image)

---

How it works

1. Determines the period (last_day, last_week, last_month) based on local time.

2. Queries Leantime via JSON-RPC with the API key.

3. Normalizes timestamps (epoch/ISO) as UTC, then converts to LOCAL_TZ calendar dates.

4. Builds a Client value from:

   • Client // Project title (robust split), or
   • project metadata fields (clientName, company, etc.), or
   • cached project map.

5. Writes a CSV to /app/out.

6. Sends one email per user (skips if total hours <= 0).

---

Cron examples

• Every Monday at 07:00 (previous week):

  CRON_SCHEDULE=0 7 * * 1
  LEANTIME_PERIOD=last_week
  

• First day of month at 08:30 (previous month):

  CRON_SCHEDULE=30 8 1 * *
  LEANTIME_PERIOD=last_month
  

---

Exit behavior

• Without CRON_SCHEDULE: runs once and exits (good for ad-hoc / CI).
• With CRON_SCHEDULE: keeps running; cron triggers the job and logs to stdout (/var/log/cron.log inside container).

---

Troubleshooting

• 401 Unauthorized: check LEANTIME_API_URL (must include https://…/api/jsonrpc) and LEANTIME_API_KEY.
• Empty Client column: ensure your Leantime project names follow Client // Project, or that project metadata includes a client/customer/company field. The tool caches both.
• Wrong dates: verify LOCAL_TZ, host timezone mounts, and CSV format CSV_DATE_STRFTIME.
• No emails: SMTP variables must be correct; email is skipped when total hours == 0.

---

Security notes

• Prefer passing secrets via env_file or Docker secrets.
• Limit API key scope and rotate regularly.
• Run with least privileges where possible.

---

Author

• Michael Kleger
  OneSystems GmbH
  mkleger@onesystems.ch

---

License

MIT License – free for commercial and private use.

---

Build

Multi Platform Builder

docker buildx create --use --platform=linux/arm64,linux/amd64 --name multi-platform-builder
docker buildx inspect --bootstrap

Build

docker buildx build --platform linux/amd64,linux/arm64 --push --tag onesystems/leantime-timesheets:v1.0.0 --tag onesystems/leantime-timesheets:latest --file Dockerfile .