From 9bba16daf067302a75bc8d929861d9225794aea2 Mon Sep 17 00:00:00 2001
From: alex <alex@alexloehr.net>
Date: Mon, 26 May 2025 13:46:47 +0000
Subject: [PATCH] added readme

---
 lib/db.js |   56 ++++++++++++++++++++++++++++++++++++++++++++++++++------
 1 files changed, 50 insertions(+), 6 deletions(-)

diff --git a/lib/db.js b/lib/db.js
index 3ff3fc7..4813ba7 100644
--- a/lib/db.js
+++ b/lib/db.js
@@ -23,13 +23,16 @@
    getUserCount,
    getUserByLogin,
    getUserByUserId,
+   getUserDefinedFields,
+   getUserDefinedField,
 }
 
 /////////////////////////////////////////////////////////////////////////
 
-async function getUsers (limit = 10, offset = 0) {
+async function getUsers (offset = 0, limit = 10) {
    const sel = `usr_id, login, firstname, lastname, gender, email, institution, street, city, zipcode, country, department, active`
    // TODO user defined fields
+   // TODO check args for SQL Injection
 
    const pool = await poolP
    const [results, fields] = await pool.query(
@@ -40,13 +43,20 @@
        OFFSET ${offset}
       `
    )
-   return results
+   const count = await getUserCount()
+   return {
+      total: count,
+      offset, limit,
+      data: results,
+   }
 }
 
 async function getUserCount () {
    const pool = await poolP
    const [results, fields] = await pool.query(
-      `SELECT COUNT(*) FROM ilias.usr_data AS ud WHERE login REGEXP '^[0-9]+$'`
+      `SELECT COUNT(*)
+       FROM ilias.usr_data AS ud
+       WHERE login REGEXP '^[0-9]+$'`
    )
    return results[0]["COUNT(*)"]
 }
@@ -54,25 +64,51 @@
 async function getUserByLogin (login) {
    const sel = `usr_id, login, firstname, lastname, gender, email, institution, street, city, zipcode, country, department, active`
    // TODO user defined fields
+   // TODO check args for SQL Injection
 
    const pool = await poolP
    const [results, fields] = await pool.query(
       `SELECT ${sel}
        FROM ilias.usr_data AS ud
-       WHERE login = '${login}' AND login REGEXP '^[0-9]+$'`
+       WHERE login = '${login}'
+         AND login REGEXP '^[0-9]+$'`
    )
-   return results
+   return joinUDF(results[0])
 }
 
 async function getUserByUserId (userId) {
    const sel = `usr_id, login, firstname, lastname, gender, email, institution, street, city, zipcode, country, department, active`
    // TODO user defined fields
+   // TODO check args for SQL Injection
 
    const pool = await poolP
    const [results, fields] = await pool.query(
       `SELECT ${sel}
        FROM ilias.usr_data AS ud
-       WHERE usr_id = '${userId}' AND login REGEXP '^[0-9]+$'`
+       WHERE usr_id = '${userId}'
+         AND login REGEXP '^[0-9]+$'`
+   )
+   return joinUDF(results[0])
+}
+
+async function getUserDefinedFields () {
+   const pool = await poolP
+   const [results] = await pool.query(
+      `SELECT ut.usr_id, ud.field_name, ut.value
+        FROM ilias.udf_definition ud  
+        JOIN ilias.udf_text ut ON ut.field_id = ud.field_id`
+   )
+   return results
+}
+
+async function getUserDefinedField (usr_id) {
+   const pool = await poolP
+   const [results] = await pool.query(
+      `SELECT ut.usr_id, ud.field_name, ut.value
+        FROM ilias.udf_definition ud  
+        JOIN ilias.udf_text ut ON ut.field_id = ud.field_id
+        WHERE ut.usr_id = '${usr_id}'
+        `
    )
    return results
 }
@@ -82,3 +118,11 @@
 async function promiseDelay (ms) {
    return new Promise(resolve => setTimeout(resolve, ms))
 }
+
+async function joinUDF (user) {
+   const fields = await getUserDefinedField(user.usr_id)
+   for(const field of fields) {
+      user[field.field_name] = field.value
+   }
+   return user
+}

--
Gitblit v1.8.0