From 3a59b36e843084b6b8879e2fd34694c2052aaef9 Mon Sep 17 00:00:00 2001
From: alex <alex@alexloehr.net>
Date: Mon, 02 Jun 2025 16:54:21 +0000
Subject: [PATCH] adding route kurs/count
---
lib/db.js | 34 ++++++++++++-----
app.js | 46 +++++++++++++++++++++++
bin/getKursTeilnehmer.js | 19 +++++++++
tst.js | 2
README.md | 6 +++
5 files changed, 96 insertions(+), 11 deletions(-)
diff --git a/README.md b/README.md
index b87c7a9..65a9d8c 100644
--- a/README.md
+++ b/README.md
@@ -86,4 +86,10 @@
GET /user/login/:login
GET /user/userId/:userId
+ GET /kurs
+ GET /kurs/items/:ref_id
+ GET /kurs/teilnehmer/:ref_id
+ GET /kurs/teilnehmer/:ref_id/count
+
+
diff --git a/app.js b/app.js
index 7b58610..1b402ee 100644
--- a/app.js
+++ b/app.js
@@ -21,6 +21,7 @@
})
fastify
+ /////// USER ////////////////////////////////////////////////////////////////
.get('/users', async function (req, res) {
const {offset, limit} = req.query
const users = await db.getUsers(offset, limit)
@@ -50,6 +51,9 @@
return res.code(404).send({status: "error", msg: "not found"})
}
})
+
+ /////// ref_id / obj_id ////////////////////////////////////////////////////////////////
+
.get("/ref_id/:ref_id", async function (req, res) {
const {ref_id} = req.params
const data = await db.getObjIdFromRefId(ref_id)
@@ -71,6 +75,48 @@
}
})
+ /////// Kurs ////////////////////////////////////////////////////////////////
+ .get("/kurs", async function (req, res) {
+ let data = await db.getKurse()
+ if (data) {
+ return res.send(data)
+ }
+ else {
+ return res.code(404).send({status: "error", msg: "not found"})
+ }
+ })
+ .get("/kurs/items/:refId", async function (req, res) {
+ const {refId} = req.params
+ let data = await db.getKursItems(refId)
+ if (data) {
+ return res.send(data)
+ }
+ else {
+ return res.code(404).send({status: "error", msg: "not found"})
+ }
+ })
+ .get("/kurs/teilnehmer/:refId", async function (req, res) {
+ const {refId} = req.params
+ let data = await db.getKursTeilnehmer(refId)
+ if (data) {
+ return res.send(data)
+ }
+ else {
+ return res.code(404).send({status: "error", msg: "not found"})
+ }
+ })
+ .get("/kurs/teilnehmer/:refId/count", async function (req, res) {
+ const {refId} = req.params
+ let data = await db.getKursTeilnehmerCount(refId)
+ if (data) {
+ return res.send(data)
+ }
+ else {
+ return res.code(404).send({status: "error", msg: "not found"})
+ }
+ })
+
+
/////////////////////////////////////////////////////////////////////////
diff --git a/bin/getKursTeilnehmer.js b/bin/getKursTeilnehmer.js
new file mode 100644
index 0000000..94eaa8d
--- /dev/null
+++ b/bin/getKursTeilnehmer.js
@@ -0,0 +1,19 @@
+const db = require("../lib/db")
+const yargs = require("yargs")
+
+/////////////////////////////////////////////////////////////////////////
+
+const argv = yargs
+ .usage("$0 <ref_id>", "get kurs TN using <ref_id>",)
+ .strict()
+ .parse()
+
+run(argv)
+ .then(console.log)
+ .catch(console.error)
+ .finally(process.exit)
+
+async function run({ref_id}) {
+ return await db.getKursTeilnehmer(ref_id)
+}
+
diff --git a/lib/db.js b/lib/db.js
index 8f8c3f5..6aa9575 100644
--- a/lib/db.js
+++ b/lib/db.js
@@ -33,7 +33,8 @@
getKursItems,
// getKursByObjId,
// getKursByRefId,
- getTeilnehmer,
+ getKursTeilnehmer,
+ getKursTeilnehmerCount,
}
/////////////////////////////////////////////////////////////////////////
@@ -196,18 +197,31 @@
// return results.length ? results[0] : undefined
}
-async function getTeilnehmer (obj_id) {
+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, 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#, ulm.status
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}.ut_lp_marks ulm
- ON ulm.obj_id = or2.obj_id AND ulm.usr_id = om.usr_id
- WHERE (or2.ref_id = ${obj_id}
- OR parent_id = ${obj_id}) # Fliesenratgeber #AND om.usr_id = 6573 # Holger Börner / nicht-bestanden -
+ 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})
`
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
+ INNER JOIN ${database}.usr_data ud ON ud.usr_id = om.usr_id
+ WHERE (or2.ref_id = ${ref_id} OR parent_id = ${ref_id})
+ `
+ let [results] = await pool.query(q)
+ results = results.length? results[0] : undefined
+ return { ref_id, count: results.count }
+}
diff --git a/tst.js b/tst.js
index edf2cc0..786ddba 100644
--- a/tst.js
+++ b/tst.js
@@ -21,7 +21,7 @@
// console.log(await db.getKurs(9911))
// const k = await db.getKurse()
- const k = await db.getKurs(9911)
+ const k = await db.getKursTeilnehmerCount(150)
console.log(k)
console.log(k.length)
--
Gitblit v1.8.0