From 0316b8949b75f68a3d3f3e88f3b8512a913f9190 Mon Sep 17 00:00:00 2001
From: alex <alex@alexloehr.net>
Date: Mon, 02 Jun 2025 13:32:38 +0000
Subject: [PATCH] adding cli prog getRefId.js

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

diff --git a/lib/db.js b/lib/db.js
index b70af0e..e849ebd 100644
--- a/lib/db.js
+++ b/lib/db.js
@@ -20,51 +20,124 @@
 
 module.exports = {
    getUsers,
+   getUserCount,
    getUserByLogin,
    getUserByUserId,
+   getUserDefinedFields,
+   getUserDefinedField,
+
+   getObjIdFromRefId,
+   getRefIdFromObjId,
 }
 
 /////////////////////////////////////////////////////////////////////////
 
-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
+}
+
+/////// obj_id / ref_id ////////////////////////////////////////////////////////////////
+
+async function getObjIdFromRefId (refId) {
+   const pool = await poolP
+   const [results] = await pool.query(
+      `SELECT ref_id, obj_id
+       FROM ${database}.object_reference as obr
+       WHERE obr.ref_id = ${refId}
+      `
+   )
+   return results.length ? results[0] : undefined
+}
+
+async function getRefIdFromObjId (objId) {
+   const pool = await poolP
+   const [results] = await pool.query(
+      `SELECT ref_id, obj_id
+       FROM ${database}.object_reference as obr
+       WHERE obr.obj_id = ${objId}
+      `
+   )
+   return results.length ? results[0] : undefined
 }
 
 /////////////////////////////////////////////////////////////////////////
@@ -72,3 +145,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