// @ts-check
/**
* @import {ExpressRequestAuthorized, ExpressResponse} from './../../types.js'
*/
import { query, pool, UUID2hex, HEX2uuid } from '@commtool/sql-query';
import { renderObject } from '../../utils/renderTemplates.js';
import { Templates } from '../../utils/compileTemplates.js';
import { getUID } from '../../utils/UUIDs.js';
import { queueAdd } from '../../tree/treeQueue/treeQueue.js';
import { updateTemplate } from '../../server.ws.js';
import { checkAdmin } from '../../utils/authChecks.js';
import { recreateJobsFunction } from '../job/utilities.js';
import { requestUpdateLogger, errorLoggerRead, errorLoggerUpdate } from '../../utils/requestLogger.js';
import mysqlTime from '../../utils/mysqlTime.js';
/**
* Extracts all achievement qualifiers (as strings) from a potentially nested filter object or array.
*
* The function traverses the filter structure recursively, collecting all string values found at any depth.
* The filter can be a string, an object with a single key whose value is another filter or array of filters, or an array of such filters.
*
* @param {Object|Array|string} filter - The filter structure to extract achievement qualifiers from.
* @returns {string[]} An array of extracted achievement qualifier strings.
*/
export function extractAchievements(filter) {
const qualies = [];
const logic = (filters) => {
if (!Array.isArray(filters)) {
match(filters);
}
for (const filter of filters) {
match(filter);
}
return true;
};
const match = (filter) => {
if (!filter) {
return;
}
if (typeof filter === 'string') {
qualies.push(filter);
return;
}
const [func] = Object.keys(filter);
logic(filter[func]);
};
if (filter) {
try {
match(filter);
} catch (e) {
console.log({ error: e, filter: filter });
}
}
return qualies;
}
/**
* Inserts or updates a function template in the database, manages related links and triggers updates.
*
* - Renders a function object from a template and request body.
* - Cleans up and compresses access filters to avoid unnecessary filter creation.
* - Inserts or updates the function in the ObjectBase table.
* - Handles achievement links and triggers requalification or visibility adjustments if needed.
* - If the function template changes, rebuilds all jobs based on this template and updates their data.
*
* @async
* @function insertFunction
* @param {import('express').Request} req - Express request object, expects session, body, and query parameters.
* @param {import('express').Response} res - Express response object, used to send JSON responses.
* @returns {Promise<void>}
*/
export const insertFunctionTemplate = async (req, res) => {
try {
const template = Templates[req.session.root].function;
const UIDfunction = await getUID(req);
const object = await renderObject(template, req.body, req);
object.Data = { ...req.body, UID: undefined };
// compress the access filter operations in order to avoid unneeded filter creation
const access = req.body.access;
if (access) {
for (const hier of Object.keys(access)) {
if (parseInt(hier) > Math.max(...req.body.hierarchies))
access[hier] = undefined;
else if (access[hier]) {
if (access[hier].person && access[hier].person.none !== undefined)
delete access[hier].person;
if (access[hier].guest && access[hier].guest.none !== undefined)
delete access[hier].guest;
if (access[hier].job && access[hier].job.none !== undefined)
delete access[hier].job;
if (access[hier].group && access[hier].group.none !== undefined)
delete access[hier].group;
if (access[hier].extern && access[hier].extern.none !== undefined)
delete access[hier].extern;
if (Object.keys(access[hier]).length === 0)
delete access[hier];
}
}
}
const functions = await query(`SELECT Data FROM ObjectBase WHERE UID=? AND Type='function'`, [UIDfunction]);
await query(`
INSERT INTO ObjectBase(UID,Type,UIDBelongsTo,Title,Display,SortName, FullTextIndex, dindex,Data)
VALUES (?,'function',
?,?,?,?,?,?,?)
ON DUPLICATE KEY UPDATE Title=VALUE(Title),Display=VALUE(Display),SortName=VALUE(SortName),FullTextIndex=VALUE(FullTextIndex),dindex=VALUE(dindex),Data=VALUE(Data)`,
[object.UID, UUID2hex(req.session.root), object.Title, object.Display, object.SortIndex, object.FullTextIndex, object.dindex, JSON.stringify(object.Data)]);
res.json({ success: true, result: { ...object, UID: HEX2uuid(object.UID) } });
updateTemplate(req.session.root);
if (functions.length) {
// modification of existing function
// delete old qualification links, will be re- added below
await query(`DELETE FROM Links WHERE Type='achievement' AND UID =? `, [UIDfunction]);
// create now the qualification links
const qualies = extractAchievements(req.body.qualification);
if (qualies.length > 0) {
query(`INSERT IGNORE INTO Links(UID,Type,UIDTarget) VALUES (?,'achievement',?)`,
qualies.map(q => ([UIDfunction, UUID2hex(q)])),
{ batch: true });
}
if (req.query.requalify == 1) {
// add requalification of all jobs based on this function into loop
queueAdd(UUID2hex(req.session.root), UUID2hex(req.session.user), 'function', object.UID, UUID2hex(req.session.root), null, UUID2hex(req.session.root));
}
// if the access parameter has changed, we have also tor re-adjust the visibility of all jobs belonging to this funtion
const oldData = JSON.parse(functions[0].Data);
if (JSON.stringify(req.body.access) !== JSON.stringify(oldData.access) || JSON.stringify(req.body.writeAccess) !== JSON.stringify(oldData.writeAccess)) {
// add visibility adjustments of all jobs based on this function into the loop
queueAdd(UUID2hex(req.session.root), UUID2hex(req.session.user), 'functionV', object.UID, UUID2hex(req.session.root), null, UUID2hex(req.session.root));
}
if (JSON.stringify(req.body) !== JSON.stringify(oldData)) {
//we have to rebuild all jobs based on this functionTemplate
await recreateJobsFunction(req, UIDfunction);
const jobs = await query(`SELECT
Job.UID , Job.UIDBelongsTo, Job.Data , gLink.UIDTarget AS UIDgroup
FROM
(ObjectBase AS Job
INNER JOIN Links AS jLink ON (Job.UID = jLink.UIDTarget AND jLink.Type ='function')
INNER JOIN Links AS gLink ON (Job.UID = gLink.UID AND gLink.Type ='memberA'))
WHERE
jLink.UID = ? AND Job.Type='job'`,
[UIDfunction]);
for (const job of jobs) {
const data = JSON.parse(job.Data);
data.function.categories = req.body.categories;
query(`UPDATE ObjectBase SET Data=? WHERE UID=?`, [JSON.stringify(data), job.UID]);
queueAdd(UUID2hex(req.session.root), UUID2hex(req.session.user), 'listMember', job.UID, job.UIDBelongsTo, null, job.UID);
}
}
}
} catch (e) {
errorLoggerUpdate(e);
}
};
/**
* Deletes a function template if there are no jobs linked to it.
*
* This function checks if any jobs are associated with the specified function template.
* If jobs are found, deletion is denied and a message is returned listing the members with jobs.
* If no jobs are linked, the function template and all related achievement links are deleted.
* The template update function is called after successful deletion.
*
* @async
* @function deleteFunctionTemplate
* @param {Object} req - Express request object, expects `params.UID` for the template UID and `session.root` for the root user UID.
* @param {Object} res - Express response object used to send JSON responses.
* @returns {Promise<void>} Sends a JSON response indicating success or failure.
*/
export const deleteFunctionTemplate = async (req, res) => {
try {
const UID = UUID2hex(req.params.UID);
// check if any jobs are lined to this template.
// in this case deny deletion
const exists = await query(`SELECT Member.Display FROM ObjectBase
INNER JOIN Member ON (Member.UID=ObjectBase.UIDBelongsTo)
INNER JOIN Links ON (Links.UIDTarget=ObjectBase.UID AND Links.Type='function')
WHERE Links.UID=? AND ObjectBase.Type='job'`,
[UID]);
if (exists.length > 0) {
res.json({ success: false, message: 'there are still jobs for this template for: ' + exists.map(e => e.Display).join(',') });
return;
}
await query(`DELETE FROM ObjectBase WHERE UID=? AND Type='function'
AND UIDBelongsTo=?`, [UID, UUID2hex(req.session.root)]);
// delete as well all existing quali links
await query(`DELETE FROM Links WHERE Type='achievement' AND UIDTarget=?`, [UID]);
updateTemplate(req.session.root);
res.json({ success: true });
} catch (e) {
errorLoggerUpdate(e);
}
};
/**
* Retrieves jobs associated with a specific function template.
*
* @async
* @function getJobsByFunctionTemplate
* @param {Object} req - Express request object, expects `params.UID` for the template UID and optional query parameters.
* @param {Object} res - Express response object used to send JSON responses.
* @returns {Promise<void>} Sends a JSON response with the jobs data.
*/
export const getJobsByFunctionTemplate = async (req, res) => {
try {
const UID = UUID2hex(req.params.UID);
const time = req.query.Timestamp ? `FOR SYSTEM_TIME AS OF TIMESTAMP'${mysqlTime(req.query.Timestamp)}'` : '';
let dataFields = '';
// you can specify as objetcs a json path of Data fields to be retreived
if (req.query.Data) {
let fields = [];
try {
fields = req.query.Data ? JSON.parse(String(req.query.Data)) : null;
} catch (e) {
fields[0] = [req.query.Data];
}
for (const field of fields) {
dataFields += `,JSON_VALUE(Member.Data,${pool.escape(field.path)}) AS ${pool.escape(field.alias)}`;
}
}
if (req.query.groupBanner) {
dataFields += `,JSON_VALUE(pgroup.Data,'$.banner') AS groupBanner`;
}
const result = await query(`
SELECT
Job.UID, Job.Type, Job.UIDBelongsTo, Job.Title , Member.Display , Member.SortName , pgroup.UID AS UIDgroup ,
CONCAT(pgroup.Title ,' ', pgroup.Display) AS pGroup, Job.hierarchie, Job.stage, Job.gender, Job.dindex
${dataFields}
FROM
ObjectBase ${time} AS Job
INNER JOIN Member ON (Member.UID=Job.UIDBelongsTo)
LEFT JOIN (ObjectBase AS pgroup
INNER JOIN Links AS GLink
ON (GLink.UIDTarget = pgroup.UID )
)
ON (GLink.UID =Job.UID AND GLink.Type ='memberA')
INNER JOIN Links ON ( Links.UIDTarget=Job.UID AND Links.Type ='function')
INNER JOIN Visible ON (Visible.UID=Job.UID)
WHERE
Links.UID = ? AND Job.Type ='job' AND Visible.UIDUser=?
GROUP BY
Job.UID
ORDER BY
Job.SortName,Member.SortName
`, [UID, UUID2hex(req.session.user)]);
res.json({ success: true, result: result });
} catch (e) {
errorLoggerRead(e);
}
};
/**
* Retrieves all function template names and UIDs for the organization.
*
* @async
* @function getFunctionTemplates
* @param {Object} req - Express request object, expects `session.root` for the root user UID.
* @param {Object} res - Express response object used to send JSON responses.
* @returns {Promise<void>} Sends a JSON response with the function templates list.
*/
export const getFunctionTemplates = async (req, res) => {
try {
// Conditionally include template data - can be large
let mainData = '';
if (req.query.includeData === 'true') {
mainData = ', Main.Data';
}
const result = await query(`
SELECT
Main.UID, Main.Display , Main.SortName ${mainData}
FROM
ObjectBase AS Main
WHERE
Main.UIDBelongsTo=? AND Main.Type='function'
GROUP BY
Main.UID
ORDER BY
Main.SortName
`, [UUID2hex(req.session.root)], { cast: ['json', 'UUID'] });
res.json({ success: true, result });
} catch (e) {
errorLoggerRead(e);
}
};
// Middleware exports
export { requestUpdateLogger, checkAdmin };