1 files changed, 52 insertions, 0 deletions
diff --git a/uploader/publications/datatables.py b/uploader/publications/datatables.py
new file mode 100644
index 0000000..e07fafd
--- /dev/null
+++ b/uploader/publications/datatables.py
@@ -0,0 +1,52 @@
+"""Fetch data for datatables."""
+import logging
+from typing import Optional
+
+from MySQLdb.cursors import DictCursor
+
+from gn_libs.mysqldb import Connection, debug_query
+
+logger = logging.getLogger(__name__)
+
+def fetch_publications(
+        conn: Connection,
+        search: Optional[str] = None,
+        offset: int = 0,
+        limit: int = -1
+) -> tuple[dict, int, int, int]:
+    """Fetch publications from the database."""
+    _query = "SELECT * FROM Publication"
+    _count_query = "SELECT COUNT(*) FROM Publication"
+    _params = None
+    _where_clause = ""
+    _limit_clause = ""
+    if search is not None and bool(search):
+        _where_clause = ("WHERE PubMed_ID LIKE %s "
+                      "OR Authors LIKE %s "
+                      "OR Title LIKE %s")
+        _params = (f"%{search}%",) * 3
+
+    if limit > 0:
+        _limit_clause = f"LIMIT {limit} OFFSET {offset}"
+
+    with conn.cursor(cursorclass=DictCursor) as cursor:
+        cursor.execute("SELECT COUNT(*) FROM Publication")
+        _total_rows = int(cursor.fetchone()["COUNT(*)"])
+
+        cursor.execute(f"{_count_query} {_where_clause}", _params)
+        debug_query(cursor, logger)
+        _result = cursor.fetchone()
+        _total_filtered = int(_result["COUNT(*)"] if bool(_result) else 0)
+
+        cursor.execute(f"{_query} {_where_clause} {_limit_clause}", _params)
+        debug_query(cursor, logger)
+        _current_filtered = tuple(
+            {**dict(row), "index": idx}
+            for idx, row
+            in enumerate(cursor.fetchall(), start=offset+1))
+
+        return (
+            _current_filtered,
+            len(_current_filtered),
+            _total_filtered,
+            _total_rows)