From 018682c0d8799dad0890cae5877092a328a235c5 Mon Sep 17 00:00:00 2001
From: alex <alex@alexloehr.net>
Date: Wed, 04 Jun 2025 16:41:55 +0000
Subject: [PATCH] adding user teilnahmen
---
lib/db.js | 69 ++++++++++++++++++++++++++--------
app.js | 13 ++++++
package-lock.json | 7 +++
vue/src/pages/UserDetail.vue | 10 ++++-
package.json | 1
vue/src/pages/KursDetail.vue | 5 ++
vue/src/lib/api.js | 5 ++
7 files changed, 90 insertions(+), 20 deletions(-)
diff --git a/app.js b/app.js
index f512971..9110702 100644
--- a/app.js
+++ b/app.js
@@ -65,6 +65,19 @@
return res.code(404).send({status: "error", msg: "not found"})
}
})
+ .get("/api/user/teilnahmen/:userId", async function (req, res) {
+ const {userid} = req.params
+ if(!userid || isNaN(Number(userid))) {
+ return res.code(500).send({status: "error", msg: "userid error"})
+ }
+ const tn = await db.getUserTeilnahmen(userid)
+ if (tn) {
+ return res.send(tn)
+ }
+ else {
+ return res.code(404).send({status: "error", msg: "not found"})
+ }
+ })
/////// ref_id / obj_id ////////////////////////////////////////////////////////////////
diff --git a/lib/db.js b/lib/db.js
index 80577fa..a4708f5 100644
--- a/lib/db.js
+++ b/lib/db.js
@@ -25,6 +25,7 @@
getUserByUserId,
getUserDefinedFields,
getUserDefinedField,
+ getUserTeilnahmen,
getObjIdFromRefId,
getRefIdFromObjId,
@@ -36,6 +37,7 @@
// getKursByRefId,
getKursTeilnehmer,
getKursTeilnehmerCount,
+
}
/////////////////////////////////////////////////////////////////////////
@@ -157,7 +159,7 @@
}
async function joinUDF (user) {
- if(!user) return user
+ if (!user) return user
const fields = await getUserDefinedField(user.usr_id)
for (const field of fields) {
user[field.field_name] = field.value
@@ -171,8 +173,9 @@
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
+ 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
@@ -191,9 +194,10 @@
WHERE or2.ref_id = '${ref_id}'
`
let [results] = await pool.query(q)
- results = results.length? results[0] : undefined
+ 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
@@ -215,13 +219,25 @@
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
+ 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
- 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
+ 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
`
@@ -233,13 +249,32 @@
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
- INNER JOIN ${database}.usr_data ud ON ud.usr_id = om.usr_id
- WHERE (or2.ref_id = ${ref_id} OR parent_id = ${ref_id})
+ 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 }
+ 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, om.usr_id, od.title, ulm.status, om.passed, ulm.status_changed
+ FROM ilias_globusfachtest.obj_members om
+ INNER JOIN ilias_globusfachtest.usr_data ud ON ud.usr_id = om.usr_id
+ INNER JOIN ilias_globusfachtest.object_data od ON od.obj_id = om.obj_id
+ LEFT JOIN ilias_globusfachtest.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
+ `
+ const [results] = await pool.query(q)
+ return results
}
diff --git a/package-lock.json b/package-lock.json
index 5d5e05a..7861474 100644
--- a/package-lock.json
+++ b/package-lock.json
@@ -12,6 +12,7 @@
"@fastify/static": "^8.2.0",
"@vueuse/core": "^13.3.0",
"@vueuse/router": "^13.3.0",
+ "dayjs": "^1.11.13",
"dotenv": "^16.5.0",
"fastify": "^5.3.3",
"lodash": "^4.17.21",
@@ -2382,6 +2383,12 @@
"integrity": "sha512-M1uQkMl8rQK/szD0LNhtqxIPLpimGm8sOBwU7lLnCpSbTyY3yeU1Vc7l4KT5zT4s/yOxHH5O7tIuuLOCnLADRw==",
"license": "MIT"
},
+ "node_modules/dayjs": {
+ "version": "1.11.13",
+ "resolved": "https://registry.npmjs.org/dayjs/-/dayjs-1.11.13.tgz",
+ "integrity": "sha512-oaMBel6gjolK862uaPQOVTA7q3TZhuSvuMQAAglQDOWYO9A91IrAOUJEyKVlqJlHE0vq5p5UXxzdPfMH/x6xNg==",
+ "license": "MIT"
+ },
"node_modules/debug": {
"version": "4.4.1",
"resolved": "https://registry.npmjs.org/debug/-/debug-4.4.1.tgz",
diff --git a/package.json b/package.json
index b96c260..551c938 100644
--- a/package.json
+++ b/package.json
@@ -16,6 +16,7 @@
"@fastify/static": "^8.2.0",
"@vueuse/core": "^13.3.0",
"@vueuse/router": "^13.3.0",
+ "dayjs": "^1.11.13",
"dotenv": "^16.5.0",
"fastify": "^5.3.3",
"lodash": "^4.17.21",
diff --git a/vue/src/lib/api.js b/vue/src/lib/api.js
index fdc5fcc..321d1de 100644
--- a/vue/src/lib/api.js
+++ b/vue/src/lib/api.js
@@ -51,3 +51,8 @@
const res = await fetch(`${apiBase}/user/userid/${userId}?token=${apiToken.value}`)
return await res.json()
}
+
+export async function getUserTeilnahmen (userId) {
+ const res = await fetch(`${apiBase}/user/teilnahmen/${userId}?token=${apiToken.value}`)
+ return await res.json()
+}
diff --git a/vue/src/pages/KursDetail.vue b/vue/src/pages/KursDetail.vue
index cdbe513..48cca6f 100644
--- a/vue/src/pages/KursDetail.vue
+++ b/vue/src/pages/KursDetail.vue
@@ -4,6 +4,7 @@
import {onMounted, reactive, ref} from "vue"
import {getKurs, getKursItems, getKursTn, iliasBase, routerBase} from "../lib/api.js"
import LinkExtern from "../components/LinkExtern.vue"
+import dayjs from "dayjs"
const route = useRoute()
const kursId = route.params.kursId
@@ -101,7 +102,7 @@
KursTn
<small>({{ kursTn?.length }})</small>
</h2>
- <div :style="{columns: kursTn?.length > 16 ? 2 : 1}">
+ <div :style="{columns: kursTn?.length > 16 ? 1 : 1}">
<table>
<thead>
<tr>
@@ -111,6 +112,7 @@
<th>lastname</th>
<th>passed</th>
<th>status</th>
+ <th>status_changed</th>
</tr>
</thead>
<tbody>
@@ -123,6 +125,7 @@
<td>{{ tn.lastname }}</td>
<td>{{ tn.passed }}</td>
<td>{{ tn.status }}</td>
+ <td>{{ dayjs(tn.status_changed).format("DD.MM.YYYY HH:mm:ss") }}</td>
</tr>
</tbody>
</table>
diff --git a/vue/src/pages/UserDetail.vue b/vue/src/pages/UserDetail.vue
index a43944c..cc559f1 100644
--- a/vue/src/pages/UserDetail.vue
+++ b/vue/src/pages/UserDetail.vue
@@ -2,13 +2,15 @@
import {useRoute} from 'vue-router'
import {onMounted, reactive, ref} from "vue"
-import {getUser, iliasBase} from "../lib/api"
+import {getUser, getUserTeilnahmen, iliasBase} from "../lib/api"
import LinkExtern from "../components/LinkExtern.vue"
const route = useRoute()
const userId = route.params.userId
-const user = ref(null)
const error = ref(null)
+
+const user = ref(null)
+const teilnahmen = ref([])
onMounted(init)
@@ -18,6 +20,10 @@
const data = await getUser(userId)
console.log(data)
user.value = data
+
+ const tn = await getUserTeilnahmen(userId)
+ console.log(tn)
+ teilnahmen.value = tn
}
</script>
--
Gitblit v1.8.0