Files

178 lines
5.8 KiB
Python
Executable File

# -*- coding: utf-8 -*-
"""Рабочий секретарь: хранение (задачи + напоминания)."""
import json
import os
import sqlite3
import time
DB = os.environ.get("WORK_DB", "/opt/work-hub/work.db")
def now():
return int(time.time())
def db():
conn = sqlite3.connect(DB, timeout=15)
conn.row_factory = sqlite3.Row
return conn
def init():
with db() as c:
c.execute("""create table if not exists messages(
id integer primary key autoincrement,
ts integer, room text, sender text, body text)""")
c.execute("""create table if not exists items(
id integer primary key autoincrement,
ts integer, text text, type text, partner text,
due text, status text default 'inbox',
raw_json text, done_ts integer)""")
c.execute("""create table if not exists reminders(
id integer primary key autoincrement,
item_id integer, fire_at integer, text text,
sent integer default 0, created integer)""")
c.execute("create index if not exists idx_items_status on items(status)")
c.execute("create index if not exists idx_items_due on items(due)")
c.execute("create index if not exists idx_rem_fire on reminders(sent, fire_at)")
# ---------- сообщения ----------
def log_message(room, sender, body):
with db() as c:
c.execute("insert into messages(ts,room,sender,body) values(?,?,?,?)",
(now(), room, sender, body))
def recent_messages(limit=8):
with db() as c:
rows = c.execute("select ts,sender,body from messages order by id desc limit ?",
(limit,)).fetchall()
return [dict(r) for r in reversed(rows)]
# ---------- задачи ----------
def add_item(text, parsed):
due = parsed.get("due")
if hasattr(due, "isoformat"):
due = due.isoformat()
with db() as c:
cur = c.execute(
"insert into items(ts,text,type,partner,due,status,raw_json) values(?,?,?,?,?,?,?)",
(now(), text, parsed.get("type"), parsed.get("partner"), due, "inbox",
json.dumps(parsed, ensure_ascii=False, default=str)))
return cur.lastrowid
def update_item(item_id, fields):
allowed = {k: v for k, v in (fields or {}).items()
if k in ("text", "type", "partner", "due", "status")}
if not allowed:
return False
sets = ", ".join("%s=?" % k for k in allowed)
with db() as c:
c.execute("update items set %s where id=?" % sets,
tuple(allowed.values()) + (item_id,))
return True
def delete_item(item_id):
with db() as c:
c.execute("delete from items where id=?", (item_id,))
c.execute("delete from reminders where item_id=?", (item_id,))
def set_status(item_id, status):
with db() as c:
c.execute("update items set status=?, done_ts=? where id=?",
(status, now() if status == "done" else None, item_id))
if status == "done":
c.execute("update reminders set sent=1 where item_id=? and sent=0", (item_id,))
def set_due(item_id, due):
with db() as c:
c.execute("update items set due=? where id=?", (due, item_id))
def get(item_id):
with db() as c:
r = c.execute("select * from items where id=?", (item_id,)).fetchone()
return dict(r) if r else None
def open_items(limit=30):
with db() as c:
rows = c.execute(
"select * from items where status!='done' order by "
"case when due is null then 1 else 0 end, due, id desc limit ?", (limit,)).fetchall()
return [dict(r) for r in rows]
def due_between(start_iso, end_iso):
with db() as c:
rows = c.execute(
"select * from items where status!='done' and due is not null "
"and due>=? and due<=? order by due", (start_iso, end_iso)).fetchall()
return [dict(r) for r in rows]
def overdue(today_iso):
with db() as c:
rows = c.execute(
"select * from items where status!='done' and due is not null and due<? "
"order by due limit 20", (today_iso,)).fetchall()
return [dict(r) for r in rows]
def stuck(days=3, limit=10):
border = now() - days * 86400
with db() as c:
rows = c.execute(
"select * from items where status='inbox' and due is null and ts<? "
"order by ts limit ?", (border, limit)).fetchall()
return [dict(r) for r in rows]
def stats():
with db() as c:
total = c.execute("select count(*) from items").fetchone()[0]
done = c.execute("select count(*) from items where status='done'").fetchone()[0]
open_ = c.execute("select count(*) from items where status!='done'").fetchone()[0]
msgs = c.execute("select count(*) from messages").fetchone()[0]
return {"total": total, "done": done, "open": open_, "messages": msgs}
# ---------- напоминания ----------
def add_reminder(fire_at, text, item_id=None):
with db() as c:
cur = c.execute(
"insert into reminders(item_id,fire_at,text,sent,created) values(?,?,?,0,?)",
(item_id, int(fire_at), text, now()))
return cur.lastrowid
def due_reminders():
with db() as c:
rows = c.execute(
"select * from reminders where sent=0 and fire_at<=? order by fire_at limit 20",
(now(),)).fetchall()
return [dict(r) for r in rows]
def mark_reminder_sent(rid):
with db() as c:
c.execute("update reminders set sent=1 where id=?", (rid,))
def pending_reminders(limit=10):
with db() as c:
rows = c.execute(
"select r.*, i.text as item_text from reminders r "
"left join items i on i.id=r.item_id "
"where r.sent=0 order by r.fire_at limit ?", (limit,)).fetchall()
return [dict(r) for r in rows]