From bf27f46986dce46a6b1e30a314c45ddde9263d81 Mon Sep 17 00:00:00 2001
From: alex <alex@alexloehr.net>
Date: Mon, 26 May 2025 14:39:48 +0000
Subject: [PATCH] update doc

---
 lib/db.js |   70 +++++++++++++++++++++++++++++++----
 1 files changed, 62 insertions(+), 8 deletions(-)

diff --git a/lib/db.js b/lib/db.js
index b70af0e..a8cac67 100644
--- a/lib/db.js
+++ b/lib/db.js
@@ -20,49 +20,95 @@
 
 module.exports = {
    getUsers,
+   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(
       `SELECT ${sel}
-       FROM ilias.usr_data AS ud
+       FROM ${database}.usr_data AS ud
+       WHERE login REGEXP '^[0-9]+$'
        LIMIT ${limit}
        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 ${database}.usr_data AS ud
+       WHERE login REGEXP '^[0-9]+$'`
+   )
+   return results[0]["COUNT(*)"]
 }
 
 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}'`
+       FROM ${database}.usr_data AS ud
+       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}'`
+       FROM ${database}.usr_data AS ud
+       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 ${database}.udf_definition ud  
+        JOIN ${database}.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 ${database}.udf_definition ud  
+        JOIN ${database}.udf_text ut ON ut.field_id = ud.field_id
+        WHERE ut.usr_id = '${usr_id}'
+        `
    )
    return results
 }
@@ -72,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