GS-2169

alex

2025-06-25 7a62193da9c9bb8f66c1e26cd1cecfc7925d6de0

 lib/db.js

@@ -40,11 +40,14 @@
   getKursTeilnehmer,
   getKursTeilnehmerCount,

   getUdf,

   getMemberRoleForCourse,
}

/////////////////////////////////////////////////////////////////////////

function getUserSearchQuery (offset, limit, search) {
async function getUserSearchQuery (offset, limit, search) {
   const sel = `usr_id, login, firstname, lastname, gender, email, institution, street, city, zipcode, country, department, active`
   if (!search || search === '') {
      return `SELECT ${sel}
@@ -55,10 +58,10 @@
      `
   }
   else {
      const ids = searchLib.search(search)
      const ids = await searchLib.search(search)
      if (!ids.length) {
         throw "nothing found"
         return getUserSearchQuery(offset, limit)
         return await getUserSearchQuery(offset, limit)
      } // nothing found
      return `SELECT ${sel}
              FROM ${database}.usr_data AS ud
@@ -79,7 +82,7 @@
   const pool = await poolP

   try {
      let userSearchQuery = getUserSearchQuery(offset, limit, search)
      let userSearchQuery = await getUserSearchQuery(offset, limit, search)
      // console.log(userSearchQuery)
      const [results, fields] = await pool.query(userSearchQuery)
      const count = await getUserCount(offset, limit, search)
@@ -101,7 +104,7 @@
async function getUserCount (offset, limit, search) {
   const pool = await poolP
   // const q = getUserSearchQuery(offset, limit, search)
   const q = getUserSearchQuery(0, 1000000, search) // hier darf kein Limit sein, offset=0
   const q = await getUserSearchQuery(0, 1000000, search) // hier darf kein Limit sein, offset=0
   const q2 = `SELECT COUNT(*) AS count
               FROM (${q}) AS X`
   const [results, fields] = await pool.query(q2)
@@ -255,29 +258,30 @@
async function getKursItems2 (ref_id) {
   const pool = await poolP
   const q = `
       WITH RECURSIVE tree (parent_id, obj_id, ref_id, title, type) AS (SELECT ci.parent_id,
                                                                               or2.obj_id,
                                                                               ci.obj_id as ref_id,
                                                                               od.title,
                                                                               od.type
                                                                        FROM ${database}.crs_items ci
                                                                                 INNER JOIN ${database}.object_reference or2 ON or2.ref_id = ci.obj_id
                                                                                 INNER JOIN ${database}.object_data od ON od.obj_id = or2.obj_id
                                                                        WHERE ci.obj_id = ${ref_id} -- Kurs ref_id
                                                                          AND or2.deleted is NULL
       WITH RECURSIVE tree (parent_id, obj_id, ref_id, title, type) AS
                          (SELECT ci.parent_id,
                                  or2.obj_id,
                                  ci.obj_id as ref_id,
                                  od.title,
                                  od.type
                           FROM ${database}.crs_items ci
                                    INNER JOIN ${database}.object_reference or2 ON or2.ref_id = ci.obj_id
                                    INNER JOIN ${database}.object_data od ON od.obj_id = or2.obj_id
                           WHERE ci.obj_id = ${ref_id} -- Kurs ref_id
                             AND or2.deleted is NULL

                                                                        UNION ALL
                           UNION ALL

                                                                        SELECT child.parent_id,
                                                                               or2.obj_id,
                                                                               child.obj_id as ref_id,
                                                                               od.title,
                                                                               od.type
                                                                        FROM ${database}.crs_items child
                                                                                 INNER JOIN ${database}.object_reference or2 ON or2.ref_id = child.obj_id
                                                                                 INNER JOIN ${database}.object_data od ON od.obj_id = or2.obj_id
                                                                                 JOIN tree ON child.parent_id = tree.ref_id
                                                                        WHERE or2.deleted is NULL)
                           SELECT child.parent_id,
                                  or2.obj_id,
                                  child.obj_id as ref_id,
                                  od.title,
                                  od.type
                           FROM ${database}.crs_items child
                                    INNER JOIN ${database}.object_reference or2 ON or2.ref_id = child.obj_id
                                    INNER JOIN ${database}.object_data od ON od.obj_id = or2.obj_id
                                    JOIN tree ON child.parent_id = tree.ref_id
                           WHERE or2.deleted is NULL)
       SELECT *
       FROM tree
       ORDER BY tree.ref_id
@@ -310,6 +314,7 @@
              WHERE (or2.ref_id = ${ref_id} OR parent_id = ${ref_id})
              ORDER BY usr_id
   `
   console.log(q)
   const [results] = await pool.query(q)
   return results
}
@@ -349,3 +354,33 @@
   const [results] = await pool.query(q)
   return results
}

/////// UDF ////////////////////////////////////////////////////////////////

async function getUdf () {
   const pool = await poolP
   const q = `SELECT field_id, field_name, field_type
              from ${database}.udf_definition;`
   const [results] = await pool.query(q)
   return results
}

/////// ROLLEN ////////////////////////////////////////////////////////////////

async function getMemberRoleForCourse (obj_id) {
   const pool = await poolP
   const q = `SELECT obj_id, title, description
              from object_data od
              WHERE type = "role" #AND title LIKE 'il_crs_member_157'
                AND od.description LIKE 'Member%${obj_id}'
   `
   const [results] = await pool.query(q)
   // darf nur einen Treffer ergeben
   if (results.length) {
      const {obj_id, title, description} = results[0]
      return obj_id
   }
   else {
      return null
   }
}