178 lines
5.8 KiB
Python
Executable File
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]
|