Unify tenant and admin loggers

1 files changed, 152 insertions, 0 deletions

diff --git a/jimbrella/logger.py b/jimbrella/logger.py
new file mode 100644
index 0000000..dc5f83c
--- /dev/null
+++ b/jimbrella/logger.py
@@ -0,0 +1,152 @@
+import sqlite3
+from datetime import datetime
+from typing import Union
+from .config import config
+
+
+class Logger:
+    def __init__(self, path: str):
+        """Read/write interface to non-volatile logger for admins to inspect.
+
+        This module does no authentication whatsoever. Authenticity must be ensured beforehand.
+        """
+        self.path = path
+
+    def read(self, maxlogs=20, serial=None) -> Union[dict, list]:
+        db = sqlite3.connect(self.path)
+        db.row_factory = sqlite3.Row
+        if serial is None:
+            data = db.execute("SELECT * FROM AdminLog ORDER BY serial DESC").fetchmany(
+                maxlogs
+            )
+        else:
+            data = db.execute(
+                "SELECT * FROM AdminLog WHERE serial = ?", (serial,)
+            ).fetchone()
+
+        db.close()
+        return data
+
+    def log_admin(
+        self, date: str, actor: str, umumbbid: int, umb_a: dict, umb_b: dict, note=""
+    ) -> None:
+        """Logs admin operations in a database so they can be recalled and possibly undone.
+
+        Every time an admin modifies the Umbrellas table via the web console, the information below are
+        kept for archival and/or rollback.
+
+        Once created, the log cannot be changed.
+
+        - serial    | incremental serial number for each modification
+        - date      | modification time, same format as Umbrellas table
+        - actor     | admin username
+        - id        | umbrella id
+        - (status|tenant_(name|id|phone|email)|lent_at)_a:
+            respective values in database pertaining to umbrella #<id> before modification
+        - (status|tenant_(name|id|phone|email)|lent_at)_b:
+            same as above but after modification
+        - note      | optional note describing the modification
+
+        Schema:
+        CREATE TABLE AdminLog(
+            serial INT PRIMARY KEY,
+            date TEXT,
+            actor TEXT,
+            id INT,
+            status_a TEXT,
+            status_b TEXT,
+            tenant_name_a TEXT,
+            tenant_name_b TEXT,
+            tenant_id_a TEXT,
+            tenant_id_b TEXT,
+            tenant_phone_a TEXT,
+            tenant_phone_b TEXT,
+            tenant_email_a TEXT,
+            tenant_email_b TEXT,
+            lent_at_a TEXT,
+            lent_at_b TEXT,
+            note TEXT
+        );
+        """
+        db = sqlite3.connect(self.path)
+
+        # get serial of most recent log, add one
+        latest = db.execute(
+            "SELECT serial FROM AdminLog ORDER BY serial DESC"
+        ).fetchone()
+        if latest is None:
+            serial = 1
+        else:
+            serial = latest[0] + 1
+
+        db.execute(
+            "INSERT INTO AdminLog VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
+            (
+                serial,
+                date,
+                actor,
+                umbid,
+                umb_a.get("status", ""),
+                umb_b.get("status", ""),
+                umb_a.get("tenant_name", ""),
+                umb_b.get("tenant_name", ""),
+                umb_a.get("tenant_id", ""),
+                umb_b.get("tenant_id", ""),
+                umb_a.get("tenant_phone", ""),
+                umb_b.get("tenant_phone", ""),
+                umb_a.get("tenant_email", ""),
+                umb_b.get("tenant_email", ""),
+                umb_a.get("lent_at", ""),
+                umb_b.get("lent_at", ""),
+                note,
+            ),
+        )
+        db.commit()
+        db.close()
+
+    def log_tenant(self, date: str, action: str, umbid: int, umb: dict) -> None:
+        """Keeps a log each time an umbrella is borrowed, returned, or marked overdue.
+
+        - serial, date  | same as AdminLog
+        - action        | one of "borrow", "return", and "overdue"
+        - id, tenant_*  | same as respective columns in table Umbrellas
+
+        Schema:
+        CREATE TABLE TenantLog(
+            serial INT PRIMARY KEY,
+            date TEXT,
+            action TEXT,
+            id INT,
+            tenant_name TEXT,
+            tenant_id TEXT,
+            tenant_phone TEXT,
+            tenant_email TEXT
+        );
+        """
+
+        db = sqlite3.connect(self.path)
+
+        latest = db.execute(
+            "SELECT serial FROM TenantLog ORDER BY serial DESC"
+        ).fetchone()
+
+        if latest is None:
+            serial = 1
+        else:
+            serial = latest[0] + 1
+
+        db.execute(
+            "INSERT INTO TenantLog VALUES (?, ?, ?, ?, ?, ?, ?, ?)",
+            (
+                serial,
+                date,
+                action,
+                umbid,
+                umb.get("tenant_name", ""),
+                umb.get("tenant_id", ""),
+                umb.get("tenant_phone", ""),
+                umb.get("tenant_email", ""),
+            ),
+        )
+        db.commit()
+        db.close()