From c5bc857132ea7310dd5897f3aa77f3469016a6e2 Mon Sep 17 00:00:00 2001
From: alex <alex@alexloehr.net>
Date: Fri, 16 May 2025 15:15:55 +0000
Subject: [PATCH] adding user-defined-fields

---
 lib/db.js |   37 ++++++++++++++++++
 tst.js    |   38 +++++++++++++-----
 2 files changed, 63 insertions(+), 12 deletions(-)

diff --git a/lib/db.js b/lib/db.js
index 61b297f..4813ba7 100644
--- a/lib/db.js
+++ b/lib/db.js
@@ -23,6 +23,8 @@
    getUserCount,
    getUserByLogin,
    getUserByUserId,
+   getUserDefinedFields,
+   getUserDefinedField,
 }
 
 /////////////////////////////////////////////////////////////////////////
@@ -30,6 +32,7 @@
 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(
@@ -61,6 +64,7 @@
 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(
@@ -69,12 +73,13 @@
        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(
@@ -83,6 +88,28 @@
        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
 }
 
@@ -91,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
+}
diff --git a/tst.js b/tst.js
index fc30d66..1353cde 100644
--- a/tst.js
+++ b/tst.js
@@ -13,20 +13,36 @@
 /////////////////////////////////////////////////////////////////////////
 
 async function run() {
-   const user = await db.getUserByLogin("242424")
-   console.log("user", user)
+   // const user = await db.getUserByLogin("242424")
+   // console.log("user", user)
+   //
+   // const user2 = await db.getUserByUserId(317)
+   // console.log("user2", user2)
+   //
+   // const start = new Date()
+   // const users = await db.getUsers(10,0)
+   // const users2 = users.map(({usr_id,login,firstname,lastname,email,institution}) => ({usr_id,login,firstname,lastname,institution}))
+   // console.table(users2)
+   // console.log(new Date() - start + "ms")
+   //
+   // const count = await db.getUserCount()
+   // console.log("count",count)
 
-   const user2 = await db.getUserByUserId(317)
-   console.log("user2", user2)
+   // console.time("udf")
+   // const udf = await db.getUserDefinedFields()
+   // console.log(udf)
+   // console.timeEnd("udf")
 
-   const start = new Date()
-   const users = await db.getUsers(10,0)
-   const users2 = users.map(({usr_id,login,firstname,lastname,email,institution}) => ({usr_id,login,firstname,lastname,institution}))
-   console.table(users2)
-   console.log(new Date() - start + "ms")
+   // const usr = await db.getUserByUserId(316)
+   const usr = await db.getUserByLogin("051134")
+   console.log(usr)
 
-   const count = await db.getUserCount()
-   console.log("count",count)
+   // console.time("udf1")
+   // const udf1 = await db.getUserDefinedField(316)
+   // console.log(udf1)
+   // console.timeEnd("udf1")
+
+
 }
 
 // async function run () {

--
Gitblit v1.8.0