My time tracking and reporting script. Vanilla Python. Can generate CSV for GNU Cash invoices.

billit.py

from datetime import datetime, time
from zoneinfo import ZoneInfo
import sqlite3
import csv
import sys


class billit:
    def __init__(self, path):
        self.db = sqlite3.connect(path)
        self.db.execute(
            "CREATE TABLE IF NOT EXISTS entries(client, task, started_at, finished_at);")

    def clients(self):
        res = self.db.execute(
            "SELECT client FROM entries GROUP BY client ORDER BY MAX(started_at) DESC;")
        return [row[0] for row in res.fetchall()]

    def tasks(self, client):
        res = self.db.execute("""
            SELECT task FROM entries 
            WHERE client = ?
            GROUP BY task
            ORDER BY MAX(started_at) DESC;
            """, [client])
        return [row[0] for row in res.fetchall()]

    # Entries
    def punch(self, client, task, at):
        if client == '' or task == '':
            return

        top = self.db.execute("""
                              SELECT rowid
                              FROM entries
                              WHERE finished_at IS NULL
                              AND client = ?
                              AND task = ?
                              ORDER BY started_at DESC;
                              """, [client, task]).fetchone()
        if top is None:
            # Punch in
            self.db.execute("INSERT INTO entries VALUES (?, ?, ?, NULL);", [
                            client, task, at])
        else:
            # Punch out
            self.db.execute(
                """UPDATE entries SET finished_at = ?
                WHERE rowid = ?;""", [at, top[0]])
        self.db.commit()

    def wip(self):
        return self.db.execute("SELECT client, task, started_at FROM entries WHERE finished_at IS NULL;").fetchall()

    # Reporting
    def gnucash_invoice(self, client, rate, gnucash_owner, invoice_id, gnucash_account, tax_table, taxable=False):
        cols = ['id', 'date_opened', 'owner_id', 'billingid', 'notes', 'date',
                'desc', 'action', 'account', 'quantity', 'price', 'disc_type', 'disc_how',
                'discount', 'taxable', 'taxincluded', 'tax_table', 'date_posted',
                'due_date', 'account_posted', 'memo_posted', 'accu_splits']

        base_dict = {k: '' for k in cols} | {
            'id': invoice_id,
            'owner_id': gnucash_owner,
            'account': gnucash_account,
            'price': rate,
            'taxable': 'X' if taxable else '',
            'tax_table': tax_table,
        }

        res = self.db.execute("""SELECT
                            started_at as date,
                            task as desc,
                            'Heures' as action,
                            round((cast(finished_at as real)-cast(started_at as real)) / 3600, 3) as quantity
                         FROM entries 
                         WHERE finished_at IS NOT NULL
                         AND client = ?
                         ORDER BY date;""", [client])

        keys = [x[0] for x in res.description]
        rows = [base_dict | dict(zip(keys, row)) for row in res.fetchall()]

        for row in rows:
            # Set timezone and format
            row['date'] = datetime.fromtimestamp(
                row['date'], tz=ZoneInfo("America/Toronto")).strftime('%Y-%m-%d')

        return cols, rows

    def dump_gnucash_invoice(self, client_id):
        cols, entries = self.gnucash_invoice(client_id)
        w = csv.DictWriter(sys.stdout, cols, restval='',
                           extrasaction='raise', dialect='excel', delimiter=';')
        w.writerows(entries)


if __name__ == '__main__':

    if len(sys.argv) == 1:
        print("Please provide a path for the database.", file=sys.stderr)
        exit(1)

    a = billit(sys.argv[1])

    match sys.argv[2:]:
        case ['report', client]:
            a.dump_gnucash_invoice(client)

        case ['clients']:
            print('\n'.join(a.clients()))

        case ['tasks', client]:
            print('\n'.join(a.tasks(client)))

        case ['punch', client, task, at]:
            a.punch(client, task, int(at))
            print()

        case ['wip']:
            print(
                '\n'.join(['|'.join(map(str, x)) for x in a.wip()]))
        case _:
            print("Options not supported.", file=sys.stderr)
            exit(1)
    exit(0)