| | |
|---|
| | | const mysql = require("mysql2/promise") |
|---|
| | | const dayjs = require("dayjs") |
|---|
| | | const _ = require("lodash") |
|---|
| | | |
|---|
| | | const log = require("../logger") |
|---|
| | | const searchLib = require("./search") |
|---|
| | | const {host, port, user, database, password} = require("./../settings").db |
|---|
| | | |
|---|
| | | ///////////////////////////////////////////////////////////////////////// |
|---|
| | | |
|---|
| | | let poolP = initPool() |
|---|
| | | |
|---|
| | | async function initPool () { |
|---|
| | | return mysql.createPool({ |
|---|
| | | host, |
|---|
| | | port, |
|---|
| | | database, |
|---|
| | | user, |
|---|
| | | password, |
|---|
| | | }) |
|---|
| | | async function initPool() { |
|---|
| | | return mysql.createPool({ |
|---|
| | | host, |
|---|
| | | port, |
|---|
| | | database, |
|---|
| | | user, |
|---|
| | | password, |
|---|
| | | }) |
|---|
| | | } |
|---|
| | | |
|---|
| | | ///////////////////////////////////////////////////////////////////////// |
|---|
| | | |
|---|
| | | module.exports = { |
|---|
| | | getUsers, |
|---|
| | | getUserCount, |
|---|
| | | getUserByLogin, |
|---|
| | | getUserByUserId, |
|---|
| | | getUserDefinedFields, |
|---|
| | | getUserDefinedField, |
|---|
| | | getUserTeilnahmen, |
|---|
| | | getUsers, |
|---|
| | | getUserCount, |
|---|
| | | getUserByLogin, |
|---|
| | | getUserByUserId, |
|---|
| | | getUserDefinedFields, |
|---|
| | | getUserDefinedField, |
|---|
| | | getUserTeilnahmen, |
|---|
| | | |
|---|
| | | getObjIdFromRefId, |
|---|
| | | getRefIdFromObjId, |
|---|
| | | getObjIdFromRefId, |
|---|
| | | getRefIdFromObjId, |
|---|
| | | |
|---|
| | | getKurse, |
|---|
| | | getKurs, |
|---|
| | | getKursItems, |
|---|
| | | // getKursByObjId, |
|---|
| | | // getKursByRefId, |
|---|
| | | getKursTeilnehmer, |
|---|
| | | getKursTeilnehmerCount, |
|---|
| | | getKurse, |
|---|
| | | getKurs, |
|---|
| | | getKursItems, |
|---|
| | | getKursItems2, |
|---|
| | | // getKursByObjId, |
|---|
| | | // getKursByRefId, |
|---|
| | | getKursTeilnehmer, |
|---|
| | | getSingleKursTeilnehmer, |
|---|
| | | getKursTeilnehmerCount, |
|---|
| | | |
|---|
| | | getKursLp, |
|---|
| | | getKursUnterobjektLp, |
|---|
| | | |
|---|
| | | getKursOffline, |
|---|
| | | setKursOffline, |
|---|
| | | |
|---|
| | | getUdf, |
|---|
| | | |
|---|
| | | getKursTeilnehmerRolle, |
|---|
| | | getKursTeilnehmerByRole, |
|---|
| | | getKursRoles, |
|---|
| | | |
|---|
| | | setStatus, |
|---|
| | | } |
|---|
| | | |
|---|
| | | ///////////////////////////////////////////////////////////////////////// |
|---|
| | | |
|---|
| | | async function getUsers (offset = 0, limit = 10) { |
|---|
| | | limit = Number(limit) || 10 |
|---|
| | | offset = Number(offset) || 0 |
|---|
| | | 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 ${database}.usr_data AS ud |
|---|
| | | WHERE login REGEXP '^[0-9]+$' |
|---|
| | | LIMIT ${limit} |
|---|
| | | OFFSET ${offset} |
|---|
| | | ` |
|---|
| | | ) |
|---|
| | | const count = await getUserCount() |
|---|
| | | return { |
|---|
| | | total: count, |
|---|
| | | offset, limit, |
|---|
| | | data: results, |
|---|
| | | } |
|---|
| | | 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} |
|---|
| | | FROM ${database}.usr_data AS ud |
|---|
| | | WHERE login REGEXP '^[0-9]+$' |
|---|
| | | LIMIT ${limit} |
|---|
| | | OFFSET ${offset} |
|---|
| | | ` |
|---|
| | | } else { |
|---|
| | | const ids = await searchLib.search(search) |
|---|
| | | if (!ids.length) { |
|---|
| | | throw "nothing found" |
|---|
| | | return await getUserSearchQuery(offset, limit) |
|---|
| | | } // nothing found |
|---|
| | | return `SELECT ${sel} |
|---|
| | | FROM ${database}.usr_data AS ud |
|---|
| | | WHERE login REGEXP '^[0-9]+$' |
|---|
| | | AND usr_id IN (${ids.join(",")}) |
|---|
| | | LIMIT ${limit} |
|---|
| | | OFFSET ${offset} |
|---|
| | | ` |
|---|
| | | } |
|---|
| | | } |
|---|
| | | |
|---|
| | | 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 getUsers(offset = 0, limit = 10, search = null) { |
|---|
| | | log.info("++++++++++ get users", offset, limit, search) |
|---|
| | | limit = Number(limit) || 10 |
|---|
| | | offset = Number(offset) || 0 |
|---|
| | | // TODO check args for SQL Injection |
|---|
| | | |
|---|
| | | const pool = await poolP |
|---|
| | | |
|---|
| | | try { |
|---|
| | | let userSearchQuery = await getUserSearchQuery(offset, limit, search) |
|---|
| | | // log.info(userSearchQuery) |
|---|
| | | const [results, fields] = await pool.query(userSearchQuery) |
|---|
| | | // console.log(results, fields) |
|---|
| | | const count = await getUserCount(offset, limit, search) |
|---|
| | | return { |
|---|
| | | total: count, |
|---|
| | | offset, limit, |
|---|
| | | data: results, |
|---|
| | | } |
|---|
| | | } catch (ex) { |
|---|
| | | return { |
|---|
| | | total: 0, |
|---|
| | | offset: 0, |
|---|
| | | limit: 0, |
|---|
| | | data: [], |
|---|
| | | } |
|---|
| | | } |
|---|
| | | } |
|---|
| | | |
|---|
| | | async function getUserByLogin (login) { |
|---|
| | | const sel = `usr_id, login, firstname, lastname, gender, email, institution, street, city, zipcode, country, department, active` |
|---|
| | | // TODO check args for SQL Injection |
|---|
| | | async function getUserCount(offset, limit, search) { |
|---|
| | | const pool = await poolP |
|---|
| | | // const q = getUserSearchQuery(offset, limit, search) |
|---|
| | | 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) |
|---|
| | | return results[0].count |
|---|
| | | |
|---|
| | | const pool = await poolP |
|---|
| | | const [results, fields] = await pool.query( |
|---|
| | | `SELECT ${sel} |
|---|
| | | FROM ${database}.usr_data AS ud |
|---|
| | | WHERE login = '${login}'` |
|---|
| | | ) |
|---|
| | | return joinUDF(results[0]) |
|---|
| | | // 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 getUserByUserId (userId) { |
|---|
| | | const sel = `usr_id, login, firstname, lastname, gender, email, institution, street, city, zipcode, country, department, active` |
|---|
| | | // TODO check args for SQL Injection |
|---|
| | | async function getUserByLogin(login) { |
|---|
| | | const sel = `usr_id, login, firstname, lastname, gender, email, institution, street, city, zipcode, country, department, active` |
|---|
| | | // TODO check args for SQL Injection |
|---|
| | | |
|---|
| | | const pool = await poolP |
|---|
| | | const [results, fields] = await pool.query( |
|---|
| | | `SELECT ${sel} |
|---|
| | | FROM ${database}.usr_data AS ud |
|---|
| | | WHERE usr_id = '${userId}'` |
|---|
| | | ) |
|---|
| | | return joinUDF(results[0]) |
|---|
| | | const pool = await poolP |
|---|
| | | const [results, fields] = await pool.query( |
|---|
| | | `SELECT ${sel} |
|---|
| | | FROM ${database}.usr_data AS ud |
|---|
| | | WHERE login = '${login}'` |
|---|
| | | ) |
|---|
| | | 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 getUserByUserId(userId) { |
|---|
| | | const sel = `usr_id, login, firstname, lastname, gender, email, institution, street, city, zipcode, country, department, active` |
|---|
| | | // TODO check args for SQL Injection |
|---|
| | | |
|---|
| | | const pool = await poolP |
|---|
| | | const [results, fields] = await pool.query( |
|---|
| | | `SELECT ${sel} |
|---|
| | | FROM ${database}.usr_data AS ud |
|---|
| | | WHERE usr_id = '${userId}'` |
|---|
| | | ) |
|---|
| | | return joinUDF(results[0]) |
|---|
| | | } |
|---|
| | | |
|---|
| | | 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 |
|---|
| | | 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 |
|---|
| | | /** |
|---|
| | | * @param refId |
|---|
| | | * @returns {Promise<{ref_id,obj_id}|undefined>} |
|---|
| | | */ |
|---|
| | | 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 |
|---|
| | | 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 |
|---|
| | | } |
|---|
| | | |
|---|
| | | ///////////////////////////////////////////////////////////////////////// |
|---|
| | | |
|---|
| | | async function promiseDelay (ms) { |
|---|
| | | return new Promise(resolve => setTimeout(resolve, ms)) |
|---|
| | | async function promiseDelay(ms) { |
|---|
| | | return new Promise(resolve => setTimeout(resolve, ms)) |
|---|
| | | } |
|---|
| | | |
|---|
| | | async function joinUDF (user) { |
|---|
| | | if (!user) return user |
|---|
| | | const fields = await getUserDefinedField(user.usr_id) |
|---|
| | | for (const field of fields) { |
|---|
| | | user[field.field_name] = field.value |
|---|
| | | } |
|---|
| | | return user |
|---|
| | | async function joinUDF(user) { |
|---|
| | | if (!user) return user |
|---|
| | | const fields = await getUserDefinedField(user.usr_id) |
|---|
| | | for (const field of fields) { |
|---|
| | | user[field.field_name] = field.value |
|---|
| | | } |
|---|
| | | return user |
|---|
| | | } |
|---|
| | | |
|---|
| | | /////// Kurs //////////////////////////////////////////////////////////////// |
|---|
| | | |
|---|
| | | async function getKurse () { |
|---|
| | | const pool = await poolP |
|---|
| | | const q = `SELECT or2.ref_id, or2.obj_id, od.title, od.description, od.type |
|---|
| | | FROM ${database}.object_reference or2 |
|---|
| | | INNER JOIN ${database}.object_data od ON od.obj_id = or2.obj_id |
|---|
| | | WHERE od.type = 'crs' |
|---|
| | | AND or2.deleted IS NULL |
|---|
| | | ` |
|---|
| | | const [results] = await pool.query(q) |
|---|
| | | return results |
|---|
| | | async function getKurse() { |
|---|
| | | const pool = await poolP |
|---|
| | | const q = `SELECT or2.ref_id, or2.obj_id, od.title, od.description, od.type, od.offline |
|---|
| | | FROM ${database}.object_reference or2 |
|---|
| | | INNER JOIN ${database}.object_data od ON od.obj_id = or2.obj_id |
|---|
| | | WHERE od.type = 'crs' |
|---|
| | | AND or2.deleted IS NULL |
|---|
| | | ` |
|---|
| | | const [results] = await pool.query(q) |
|---|
| | | return results |
|---|
| | | } |
|---|
| | | |
|---|
| | | // async function getKursByRefId (refId) { |
|---|
| | |
|---|
| | | // return getKursByObjId(obj_id) |
|---|
| | | // } |
|---|
| | | |
|---|
| | | async function getKurs (ref_id) { |
|---|
| | | const pool = await poolP |
|---|
| | | const q = `SELECT or2.ref_id, or2.obj_id, od.title, od.description, od.type, od.create_date |
|---|
| | | FROM ${database}.object_reference or2 |
|---|
| | | INNER JOIN ${database}.object_data od ON od.obj_id = or2.obj_id |
|---|
| | | WHERE or2.ref_id = '${ref_id}' |
|---|
| | | ` |
|---|
| | | let [results] = await pool.query(q) |
|---|
| | | results = results.length ? results[0] : undefined |
|---|
| | | return results |
|---|
| | | async function getKurs(ref_id) { |
|---|
| | | const pool = await poolP |
|---|
| | | const q = `SELECT or2.ref_id, or2.obj_id, od.title, od.description, od.type, od.create_date, od.offline |
|---|
| | | FROM ${database}.object_reference or2 |
|---|
| | | INNER JOIN ${database}.object_data od ON od.obj_id = or2.obj_id |
|---|
| | | WHERE or2.ref_id = '${ref_id}' |
|---|
| | | AND or2.deleted IS NULL |
|---|
| | | ` |
|---|
| | | let [results] = await pool.query(q) |
|---|
| | | results = results.length ? results[0] : undefined |
|---|
| | | return results |
|---|
| | | } |
|---|
| | | |
|---|
| | | async function getKursItems (ref_id) { |
|---|
| | | const pool = await poolP |
|---|
| | | // const q = `SELECT or2.ref_id, or2.obj_id, od.title, od.description, od.type |
|---|
| | | // FROM ${database}.object_reference or2 |
|---|
| | | // INNER JOIN ${database}.object_data od ON od.obj_id = or2.obj_id |
|---|
| | | // WHERE or2.obj_id = '${obj_id}' |
|---|
| | | // ` |
|---|
| | | const q = `SELECT ci.parent_id, or2.ref_id, or2.obj_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 or2.ref_id = ${ref_id} |
|---|
| | | OR parent_id = ${ref_id} |
|---|
| | | AND or2.deleted is NULL |
|---|
| | | ` |
|---|
| | | const [results] = await pool.query(q) |
|---|
| | | return results |
|---|
| | | async function getKursItems(ref_id) { |
|---|
| | | const pool = await poolP |
|---|
| | | // const q = `SELECT or2.ref_id, or2.obj_id, od.title, od.description, od.type |
|---|
| | | // FROM ${database}.object_reference or2 |
|---|
| | | // INNER JOIN ${database}.object_data od ON od.obj_id = or2.obj_id |
|---|
| | | // WHERE or2.obj_id = '${obj_id}' |
|---|
| | | // ` |
|---|
| | | const q = `SELECT ci.parent_id, or2.ref_id, or2.obj_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 or2.ref_id = ${ref_id} |
|---|
| | | OR parent_id = ${ref_id} |
|---|
| | | AND or2.deleted is NULL |
|---|
| | | ` |
|---|
| | | const [results] = await pool.query(q) |
|---|
| | | return results |
|---|
| | | } |
|---|
| | | |
|---|
| | | async function getKursTeilnehmer (ref_id) { |
|---|
| | | const pool = await poolP |
|---|
| | | const q = `SELECT ci.parent_id, |
|---|
| | | or2.ref_id, |
|---|
| | | or2.obj_id, |
|---|
| | | od.title, |
|---|
| | | od.type, |
|---|
| | | om.usr_id, |
|---|
| | | ud.login, |
|---|
| | | ud.firstname, |
|---|
| | | ud.lastname, |
|---|
| | | ud.active, |
|---|
| | | om.passed, |
|---|
| | | ulm.status, |
|---|
| | | ulm.status_changed |
|---|
| | | 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 |
|---|
| | | INNER JOIN ${database}.obj_members om ON om.obj_id = or2.obj_id AND om.member = 1 |
|---|
| | | INNER JOIN ${database}.usr_data ud ON ud.usr_id = om.usr_id |
|---|
| | | LEFT JOIN ${database}.ut_lp_marks ulm ON ulm.obj_id = or2.obj_id AND ud.usr_id = ulm.usr_id |
|---|
| | | WHERE (or2.ref_id = ${ref_id} OR parent_id = ${ref_id}) |
|---|
| | | ORDER BY usr_id |
|---|
| | | ` |
|---|
| | | const [results] = await pool.query(q) |
|---|
| | | return results |
|---|
| | | 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 |
|---|
| | | |
|---|
| | | 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 * |
|---|
| | | FROM tree |
|---|
| | | ORDER BY tree.ref_id |
|---|
| | | ` |
|---|
| | | const [results] = await pool.query(q) |
|---|
| | | return results |
|---|
| | | } |
|---|
| | | |
|---|
| | | async function getKursTeilnehmerCount (ref_id) { |
|---|
| | | const pool = await poolP |
|---|
| | | const q = `SELECT COUNT(*) as count |
|---|
| | | 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 |
|---|
| | | INNER JOIN ${database}.obj_members om ON om.obj_id = or2.obj_id AND om.member = 1 |
|---|
| | | INNER JOIN ${database}.usr_data ud ON ud.usr_id = om.usr_id |
|---|
| | | LEFT JOIN ${database}.ut_lp_marks ulm ON ulm.obj_id = or2.obj_id AND ud.usr_id = ulm.usr_id |
|---|
| | | WHERE (or2.ref_id = ${ref_id} |
|---|
| | | OR parent_id = ${ref_id}) |
|---|
| | | ORDER BY usr_id |
|---|
| | | ` |
|---|
| | | let [results] = await pool.query(q) |
|---|
| | | results = results.length ? results[0] : undefined |
|---|
| | | return {ref_id, count: results.count} |
|---|
| | | async function getKursTeilnehmer(ref_id) { |
|---|
| | | const pool = await poolP |
|---|
| | | const q = `SELECT ci.parent_id, |
|---|
| | | or2.ref_id, |
|---|
| | | or2.obj_id, |
|---|
| | | od.title, |
|---|
| | | od.type, |
|---|
| | | om.usr_id, |
|---|
| | | ud.login, |
|---|
| | | ud.firstname, |
|---|
| | | ud.lastname, |
|---|
| | | ud.active, |
|---|
| | | om.passed, |
|---|
| | | ulm.status, |
|---|
| | | ulm.status_changed |
|---|
| | | 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 |
|---|
| | | INNER JOIN ${database}.obj_members om ON om.obj_id = or2.obj_id AND om.member = 1 |
|---|
| | | INNER JOIN ${database}.usr_data ud ON ud.usr_id = om.usr_id |
|---|
| | | LEFT JOIN ${database}.ut_lp_marks ulm ON ulm.obj_id = or2.obj_id AND ud.usr_id = ulm.usr_id |
|---|
| | | WHERE (or2.ref_id = ${ref_id} OR parent_id = ${ref_id}) |
|---|
| | | AND or2.deleted IS NULL |
|---|
| | | ORDER BY usr_id |
|---|
| | | ` |
|---|
| | | // log.info(q) |
|---|
| | | const [results] = await pool.query(q) |
|---|
| | | return results |
|---|
| | | } |
|---|
| | | |
|---|
| | | async function getUserTeilnahmen (usr_id) { |
|---|
| | | const pool = await poolP |
|---|
| | | const q = `SELECT om.obj_id, or2.ref_id , om.usr_id, od.title , ulm.status, om.passed, ulm.status_changed |
|---|
| | | FROM ${database}.obj_members om |
|---|
| | | INNER JOIN ${database}.object_reference or2 ON or2.obj_id = om.obj_id |
|---|
| | | INNER JOIN ${database}.usr_data ud ON ud.usr_id = om.usr_id |
|---|
| | | INNER JOIN ${database}.object_data od ON od.obj_id = om.obj_id |
|---|
| | | LEFT JOIN ${database}.ut_lp_marks ulm |
|---|
| | | ON ulm.usr_id = om.usr_id AND ulm.obj_id = om.obj_id |
|---|
| | | WHERE om.usr_id = ${usr_id} |
|---|
| | | AND om.member = 1 |
|---|
| | | ` |
|---|
| | | console.log(q) |
|---|
| | | const [results] = await pool.query(q) |
|---|
| | | return results |
|---|
| | | async function getSingleKursTeilnehmer(ref_id, usr_id) { |
|---|
| | | const pool = await poolP |
|---|
| | | const q = `SELECT ci.parent_id, |
|---|
| | | or2.ref_id, |
|---|
| | | or2.obj_id, |
|---|
| | | od.title, |
|---|
| | | od.type, |
|---|
| | | om.usr_id, |
|---|
| | | ud.login, |
|---|
| | | ud.firstname, |
|---|
| | | ud.lastname, |
|---|
| | | ud.active, |
|---|
| | | om.passed, |
|---|
| | | ulm.status, |
|---|
| | | ulm.status_changed |
|---|
| | | 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 |
|---|
| | | INNER JOIN ${database}.obj_members om ON om.obj_id = or2.obj_id AND om.member = 1 |
|---|
| | | INNER JOIN ${database}.usr_data ud ON ud.usr_id = om.usr_id |
|---|
| | | LEFT JOIN ${database}.ut_lp_marks ulm ON ulm.obj_id = or2.obj_id AND ud.usr_id = ulm.usr_id |
|---|
| | | WHERE (or2.ref_id = ${ref_id} |
|---|
| | | OR parent_id = ${ref_id}) |
|---|
| | | AND om.usr_id = ${usr_id} |
|---|
| | | ORDER BY usr_id |
|---|
| | | ` |
|---|
| | | // log.info(q) |
|---|
| | | const [results] = await pool.query(q) |
|---|
| | | return results[0] |
|---|
| | | } |
|---|
| | | |
|---|
| | | async function getKursTeilnehmerCount(ref_id) { |
|---|
| | | const pool = await poolP |
|---|
| | | const q = `SELECT COUNT(*) as count |
|---|
| | | 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 |
|---|
| | | INNER JOIN ${database}.obj_members om ON om.obj_id = or2.obj_id AND om.member = 1 |
|---|
| | | INNER JOIN ${database}.usr_data ud ON ud.usr_id = om.usr_id |
|---|
| | | LEFT JOIN ${database}.ut_lp_marks ulm ON ulm.obj_id = or2.obj_id AND ud.usr_id = ulm.usr_id |
|---|
| | | WHERE (or2.ref_id = ${ref_id} |
|---|
| | | OR parent_id = ${ref_id}) |
|---|
| | | ORDER BY usr_id |
|---|
| | | ` |
|---|
| | | let [results] = await pool.query(q) |
|---|
| | | results = results.length ? results[0] : undefined |
|---|
| | | return {ref_id, count: results.count} |
|---|
| | | } |
|---|
| | | |
|---|
| | | async function getUserTeilnahmen(usr_id) { |
|---|
| | | const pool = await poolP |
|---|
| | | const q = `SELECT om.obj_id, or2.ref_id, om.usr_id, od.title, ulm.status, om.passed, ulm.status_changed |
|---|
| | | FROM ${database}.obj_members om |
|---|
| | | INNER JOIN ${database}.object_reference or2 ON or2.obj_id = om.obj_id |
|---|
| | | INNER JOIN ${database}.usr_data ud ON ud.usr_id = om.usr_id |
|---|
| | | INNER JOIN ${database}.object_data od ON od.obj_id = om.obj_id |
|---|
| | | LEFT JOIN ${database}.ut_lp_marks ulm |
|---|
| | | ON ulm.usr_id = om.usr_id AND ulm.obj_id = om.obj_id |
|---|
| | | WHERE om.usr_id = ${usr_id} |
|---|
| | | AND om.member = 1 |
|---|
| | | ` |
|---|
| | | // log.info(q).catch(console.error) |
|---|
| | | const [results] = await pool.query(q) |
|---|
| | | return results |
|---|
| | | } |
|---|
| | | |
|---|
| | | async function getKursLp(obj_id, raw = false) { |
|---|
| | | const {ref_id} = await getRefIdFromObjId(obj_id) |
|---|
| | | const tnUnter = await getKursUnterobjektLp(obj_id) |
|---|
| | | |
|---|
| | | if (raw) { |
|---|
| | | return tnUnter |
|---|
| | | } else { // LP aller Unterobjekte zusammenfassen |
|---|
| | | const libLp = require("../lib/libLp") |
|---|
| | | const data = libLp.alleAuswerten(tnUnter) |
|---|
| | | return data |
|---|
| | | } |
|---|
| | | |
|---|
| | | // const teilnehmer = await getKursTeilnehmer(ref_id) |
|---|
| | | // const tnUnter = await getKursUnterobjektLp(obj_id) |
|---|
| | | // console.table(teilnehmer) |
|---|
| | | // console.table(tnUnter) |
|---|
| | | // |
|---|
| | | // /** |
|---|
| | | // * Die beiden Datensätze mergen |
|---|
| | | // * NEIN - es reicht der |
|---|
| | | // * |
|---|
| | | // */ |
|---|
| | | // |
|---|
| | | // const idx = _.groupBy(tnUnter, "usr_id") |
|---|
| | | // for (const tn of teilnehmer) { |
|---|
| | | // delete tn.parent_id |
|---|
| | | // delete tn.type |
|---|
| | | // delete tn.active |
|---|
| | | // |
|---|
| | | // /** @type Array */ |
|---|
| | | // const unter = idx[tn.usr_id] |
|---|
| | | // if (!unter) continue |
|---|
| | | // |
|---|
| | | // /** |
|---|
| | | // * wenn unter.status_changed neuer, wird unter tn.status_changed vorgezogen |
|---|
| | | // * wenn unter.status neuer, überscheibt es tn.status |
|---|
| | | // * tn.passed muss zurückgesetzt werden wenn tn.status überschrieben wird |
|---|
| | | // */ |
|---|
| | | // tn.status_overwrite = false |
|---|
| | | // |
|---|
| | | // // max unter status_changed finden |
|---|
| | | // const unterStatusChanged = _.max(unter.map(u => u.status_changed)) |
|---|
| | | // |
|---|
| | | // // es muss nur überschrieben werden wenn das Unterdatum größer ist |
|---|
| | | // if (unterStatusChanged > tn.status_changed) { |
|---|
| | | // // unter Status auswerten |
|---|
| | | // // 0 = noch nicht bearbeitet |
|---|
| | | // // 1 = in Bearbeitung |
|---|
| | | // // 2 = bestanden |
|---|
| | | // // 3 = nicht bestanden |
|---|
| | | // |
|---|
| | | // /** @type Array */ |
|---|
| | | // const unterStatusse = unter.map(u => u.status) |
|---|
| | | // let newStatus = tn.status |
|---|
| | | // const allSame = function () { |
|---|
| | | // if (!unterStatusse.length) return false |
|---|
| | | // const first = unterStatusse[0] |
|---|
| | | // return unterStatusse.every(it => it === first) |
|---|
| | | // }() |
|---|
| | | // console.log({unterStatusse}) |
|---|
| | | // // Fall 1: keine Unterstatussse vorhanden -> status vom Kurs |
|---|
| | | // if (!unterStatusse.length) { |
|---|
| | | // newStatus = tn.status |
|---|
| | | // } |
|---|
| | | // // Fall 2: eines nicht bestanden -> nicht bestanden // 0,1,2,3 -> 3 |
|---|
| | | // if (unterStatusse.some(u => u === 3)) { |
|---|
| | | // newStatus = 3 |
|---|
| | | // } |
|---|
| | | // // Fall 3: alle statusse gleich -> status // 0,0,0 1,1,1 2,2,2 3,3,3 |
|---|
| | | // else if (allSame) { |
|---|
| | | // newStatus = unterStatusse[0] |
|---|
| | | // } |
|---|
| | | // // Fall 4: wenn eines in Bearbeitung -> in Bearbeitung // 0,1,0 2,1,2 |
|---|
| | | // else if (unterStatusse.some(u => u === 1)) { |
|---|
| | | // newStatus = 1 |
|---|
| | | // } |
|---|
| | | // // Fall 5: sonst in Bearbeitung |
|---|
| | | // else { |
|---|
| | | // // newStatus = Math.max.apply(this, unterStatusse) |
|---|
| | | // newStatus = 1 |
|---|
| | | // } |
|---|
| | | // tn.status_changed = unterStatusChanged |
|---|
| | | // tn.status = newStatus |
|---|
| | | // tn.status_overwrite = true |
|---|
| | | // } |
|---|
| | | // } |
|---|
| | | // return teilnehmer |
|---|
| | | } |
|---|
| | | |
|---|
| | | async function getKursUnterobjektLp(obj_id) { |
|---|
| | | const pool = await poolP |
|---|
| | | const q = `SELECT ud.usr_id, |
|---|
| | | ud.login, |
|---|
| | | ud.firstname, |
|---|
| | | ud.lastname, |
|---|
| | | ulc.obj_id, |
|---|
| | | ulc.item_id, |
|---|
| | | ulc.lpmode, |
|---|
| | | t.obj_id as item_obj_id, |
|---|
| | | od.type, |
|---|
| | | ulm.status, |
|---|
| | | ulm.status_changed, |
|---|
| | | ulm.percentage, |
|---|
| | | ulm.completed |
|---|
| | | FROM ${database}.ut_lp_collections ulc |
|---|
| | | INNER JOIN ${database}.object_reference t ON t.ref_id = ulc.item_id |
|---|
| | | INNER JOIN ${database}.ut_lp_marks ulm ON ulm.obj_id = t.obj_id |
|---|
| | | INNER JOIN ${database}.usr_data ud ON ud.usr_id = ulm.usr_id |
|---|
| | | INNER JOIN ${database}.object_data od ON od.obj_id = t.obj_id |
|---|
| | | WHERE ulc.obj_id = ${obj_id} # obj_id Kurs |
|---|
| | | AND ulc.active = 1 |
|---|
| | | AND ulc.lpmode = 5 # nur mode 5 |
|---|
| | | ORDER BY ud.usr_id, ulc.item_id |
|---|
| | | ` |
|---|
| | | const [results] = await pool.query(q) |
|---|
| | | return results |
|---|
| | | } |
|---|
| | | |
|---|
| | | |
|---|
| | | async function getKursOffline(obj_id) { |
|---|
| | | const pool = await poolP |
|---|
| | | const q = `SELECT offline |
|---|
| | | FROM ${database}.object_data |
|---|
| | | WHERE obj_id = ${obj_id} |
|---|
| | | ` |
|---|
| | | const [results] = await pool.query(q) |
|---|
| | | return results[0] |
|---|
| | | } |
|---|
| | | |
|---|
| | | async function setKursOffline(isOffline, obj_id) { |
|---|
| | | const pool = await poolP |
|---|
| | | const q = `UPDATE ${database}.object_data |
|---|
| | | SET offline = ${isOffline} |
|---|
| | | WHERE obj_id = ${obj_id} |
|---|
| | | ` |
|---|
| | | const [results] = await pool.query(q) |
|---|
| | | // return results |
|---|
| | | return {offline: isOffline} |
|---|
| | | } |
|---|
| | | |
|---|
| | | /////// 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 getKursTeilnehmerRolle(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 |
|---|
| | | } |
|---|
| | | } |
|---|
| | | |
|---|
| | | async function getKursTeilnehmerByRole(obj_id) { |
|---|
| | | const pool = await poolP |
|---|
| | | const q = ` |
|---|
| | | SELECT obj_id as role_id, ru.usr_id, ud.firstname, ud.lastname |
|---|
| | | FROM ${database}.object_data od |
|---|
| | | INNER JOIN ${database}.rbac_ua ru ON ru.rol_id = od.obj_id |
|---|
| | | INNER JOIN ${database}.usr_data ud ON ud.usr_id = ru.usr_id |
|---|
| | | WHERE type = "role" #AND title LIKE 'il_crs_member_157' |
|---|
| | | AND od.description LIKE 'Member%${obj_id}' |
|---|
| | | ` |
|---|
| | | const [results] = await pool.query(q) |
|---|
| | | return results |
|---|
| | | } |
|---|
| | | |
|---|
| | | async function getKursRoles(ref_id) { |
|---|
| | | const pool = await poolP |
|---|
| | | const q = ` |
|---|
| | | SELECT pa.rol_id, or2.ref_id, or2.obj_id, od2.type, od2.title, od2.description |
|---|
| | | FROM ${database}.rbac_pa pa |
|---|
| | | INNER JOIN ${database}.object_reference or2 ON or2.ref_id = pa.ref_id |
|---|
| | | INNER JOIN ${database}.object_data od ON od.obj_id = or2.obj_id |
|---|
| | | INNER JOIN ${database}.object_data od2 ON od2.obj_id = pa.rol_id |
|---|
| | | WHERE pa.ref_id = ${ref_id} |
|---|
| | | ` |
|---|
| | | const [results] = await pool.query(q) |
|---|
| | | return results |
|---|
| | | } |
|---|
| | | |
|---|
| | | |
|---|
| | | /////// STATUS //////////////////////////////////////////////////////////////// |
|---|
| | | |
|---|
| | | /** |
|---|
| | | * Zwei Möglichkeiten: |
|---|
| | | * 1. es gibt schon einen Eintrag in ut_lp_marks, dann muss man diesen updaten |
|---|
| | | * 2. es gibt noch keinen Eintrag in ut_lp_marks, dann muss erst einer erstellt werden (NEIN! Für neuen TN sind bereits beide Einträge vorhanden, kann also wegfallen) |
|---|
| | | * |
|---|
| | | * Weiterhin haben wir es mit zwei Tabellen zu tun: |
|---|
| | | * - obj_members (passed) |
|---|
| | | * - ut_lp_marks (status) |
|---|
| | | * |
|---|
| | | * UPDATE: Wir nehmen an, dass beide Einträge schon vorhanden sind! |
|---|
| | | * |
|---|
| | | * @param courseId |
|---|
| | | * @param userId |
|---|
| | | * @param {Number} passed |
|---|
| | | * @param {Number} status |
|---|
| | | * @returns {Promise<{status: string}>} |
|---|
| | | */ |
|---|
| | | async function setStatus(courseId, userId, passed = null, status = null) { |
|---|
| | | const pool = await poolP |
|---|
| | | log.info(`------------setStatus----- courseId=${courseId} userId=${userId} passed=${passed} status=${status}`) |
|---|
| | | passed = isNaN(Number(passed)) ? passed : Number(passed) |
|---|
| | | status = isNaN(Number(status)) ? status : Number(status) |
|---|
| | | |
|---|
| | | // ACHTUNG Transactions funktioneren so nicht, erst mal ohne machen... |
|---|
| | | const q = ` |
|---|
| | | START TRANSACTION; |
|---|
| | | |
|---|
| | | UPDATE ${database}.ut_lp_marks ulm |
|---|
| | | SET status = ${status} |
|---|
| | | WHERE ulm.usr_id = 31793 AND ulm.obj_id = 32212; |
|---|
| | | |
|---|
| | | UPDATE ${database}.obj_members om |
|---|
| | | SET passed = ${passed} |
|---|
| | | WHERE om.usr_id = ${userId} AND om.obj_id = ${courseId}; |
|---|
| | | |
|---|
| | | COMMIT; |
|---|
| | | ` |
|---|
| | | |
|---|
| | | let date = dayjs().format('YYYY-MM-DD HH:mm:ss') |
|---|
| | | const q1 = ` |
|---|
| | | UPDATE ${database}.ut_lp_marks ulm |
|---|
| | | SET status = ${status}, |
|---|
| | | status_changed = "${date}" |
|---|
| | | WHERE ulm.usr_id = ${userId} |
|---|
| | | AND ulm.obj_id = ${courseId}; |
|---|
| | | ` |
|---|
| | | const q2 = ` |
|---|
| | | UPDATE ${database}.obj_members om |
|---|
| | | SET passed = ${passed} |
|---|
| | | WHERE om.usr_id = ${userId} |
|---|
| | | AND om.obj_id = ${courseId}; |
|---|
| | | ` |
|---|
| | | |
|---|
| | | const [results1] = await pool.query(q1) |
|---|
| | | const [results2] = await pool.query(q2) |
|---|
| | | const {affectedRows: affectedRows1} = results1 |
|---|
| | | const {affectedRows: affectedRows2} = results2 |
|---|
| | | |
|---|
| | | if (affectedRows1 && affectedRows2) { |
|---|
| | | return {status: "ok"} |
|---|
| | | } else { |
|---|
| | | throw { |
|---|
| | | status: "error", |
|---|
| | | reason: {affectedRows1, affectedRows2} |
|---|
| | | } |
|---|
| | | } |
|---|
| | | } |
|---|
