/**
* Event Job Service Layer
*
* This service manages job assignments in events. Jobs represent roles and
* responsibilities assigned to persons within events, with qualification
* tracking and template-based structure.
*
* Key concepts:
* - Event Jobs: Role assignments linking persons to events
* - Job Templates: Define job structure, qualifications, and permissions
* - Qualifications: Track if person meets job requirements
* - Responsibilities: Jobs can be responsible (admin), visible, or role-based
*
* Database structure:
* - ObjectBase: Stores eventJob objects with Type='eventJob'
* - Links: Junction table for job-event and job-template relationships
* - Visible: Access control based on job assignments
*
* @module EventJobService
*/
// @ts-check
/**
* @import {ExpressRequestAuthorized} from './../../types.js'
*/
import { query, UUID2hex, HEX2uuid, pool } from '@commtool/sql-query';
import { Templates } from '../../utils/compileTemplates.js';
import { renderObject } from '../../utils/renderTemplates.js';
import { getUID, isValidUID } from '../../utils/UUIDs.js';
import { addUpdateEntry, addUpdateList } from '../../server.ws.js';
import { isObjectAdmin } from '../../utils/authChecks.js';
import { publishEvent } from '../../utils/events.js';
/**
* Check if a person is qualified for a job
*
* Verifies that the person has all required qualifications/achievements
* for the job based on the qualification requirements.
*
* @param {Buffer} UIDperson - Person UID in hex format
* @param {Array<string>} qualifications - Array of required qualification UIDs
* @returns {Promise<number>} 1 if qualified, 0 if not
*/
export const jobQualified = async (UIDperson, qualifications) => {
const fullfilled = await query(
`SELECT ObjectBase.UIDBelongsTo, Links.UIDTarget
FROM ObjectBase
INNER JOIN Links ON (Links.UID=ObjectBase.UID)
WHERE ObjectBase.UIDBelongsTo=? AND Links.Type IN ('member','memberA','achievement')
AND ObjectBase.Type='achievement'
GROUP BY Links.UIDTarget`,
[UIDperson]
);
if (fullfilled) {
const qualies = fullfilled.map(el => HEX2uuid(el.UIDTarget));
if (qualifications && qualifications.every(q => qualies.includes(q))) {
return 1;
}
return 0;
}
return 0;
};
/**
* Recreate job objects from templates
*
* Re-renders job objects based on their templates, updating titles,
* sorting, and qualification status.
*
* @param {Buffer} root - Organization root UID
* @param {Array} jobs - Array of job objects to recreate
* @param {boolean} requalify - Whether to recheck qualifications
* @returns {Promise<void>}
*/
const recreateJobs = async (root, jobs, requalify = false) => {
const template = Templates[HEX2uuid(root)].job;
for (const job of jobs) {
let qualified = job.qualified ? "1" : "0";
if (requalify) {
const functionData = JSON.parse(job.FunctionData);
const qualifiedNum = await jobQualified(
job.UIDperson,
functionData.qualification ? functionData.qualification : {}
);
qualified = String(qualifiedNum);
}
let jobData = {};
try {
jobData = JSON.parse(job.JobData);
} catch (e) {
console.error('json parse error recreateJobs in jobs.js', e, job.JobData);
}
const Data = {
...jobData,
UID: HEX2uuid(job.UID),
qualified: qualified,
function: {
...JSON.parse(job.FunctionData),
functionUID: HEX2uuid(job.UIDfunction)
}
};
const object = await renderObject(
template,
{ ...Data, member: JSON.parse(job.MemberData) },
{ session: { root: HEX2uuid(root) } },
'events'
);
await query(
`UPDATE ObjectBase SET Title=?, dindex=?, hierarchie=?, stage=?, gender=?, Data=?
WHERE UID=?`,
[
object.Title, qualified, object.hierarchie, object.stage,
object.gender, JSON.stringify(Data), job.UID
]
);
}
};
/**
* Recreate all jobs for a group
*
* @param {ExpressRequestAuthorized} req - Request object
* @param {Buffer} group - Group UID
* @returns {Promise<Object>} Result with success status
*/
export const recreateJobsGroup = async (req, group) => {
try {
const jobs = await query(
`SELECT ObjectBase.UID, ObjectBase.Data AS JobData, ObjectBase.dindex as qualified,
PGMember.Data AS GroupData, PGroup.UID AS UIDGroup,
Person.UID AS UIDperson, Person.dindex AS dindexPerson,
FunctionT.Data AS FunctionData, FunctionT.UID AS UIDfunction,
Member.Data AS MemberData
FROM ObjectBase
INNER JOIN Links ON (Links.UID=ObjectBase.UID AND Links.Type IN ('member','memberA'))
INNER JOIN Links AS GLink ON (GLink.UID=ObjectBase.UID AND GLink.Type='memberA')
INNER JOIN ObjectBase AS PGroup ON (PGroup.UID=GLink.UIDTarget)
INNER JOIN Member AS PGMember ON (PGroup.UID=PGMember.UID)
INNER JOIN ObjectBase AS Person ON (ObjectBase.UIDBelongsTo=Person.UID AND Person.Type='person')
INNER JOIN Links AS FLink ON (FLink.UIDTarget=ObjectBase.UID AND FLink.Type='function')
INNER JOIN ObjectBase AS FunctionT ON (FunctionT.UID=FLink.UID)
INNER JOIN Member ON (Member.UID=ObjectBase.UIDBelongsTo)
WHERE ObjectBase.Type='job' AND Links.UIDTarget=?`,
[group]
);
await recreateJobs(UUID2hex(req.session.root), jobs, true);
return { success: true };
} catch (e) {
return { success: false, message: e };
}
};
/**
* Recreate all jobs for a function template
*
* @param {ExpressRequestAuthorized} req - Request object
* @param {Buffer} functionT - Function template UID
* @returns {Promise<Object>} Result with success status
*/
export const recreateJobsFunction = async (req, functionT) => {
try {
const jobs = await query(
`SELECT ObjectBase.UID, ObjectBase.Data AS JobData, ObjectBase.dindex as qualified,
PGMember.Data AS GroupData, PGroup.UID AS UIDGroup,
Person.UID AS UIDperson, Person.dindex AS dindexPerson,
FunctionT.Data AS FunctionData, FunctionT.UID AS UIDfunction,
Member.Data AS MemberData
FROM ObjectBase
INNER JOIN Links ON (Links.UID=ObjectBase.UID AND Links.Type IN ('member','memberA'))
INNER JOIN Links AS GLink ON (GLink.UID=ObjectBase.UID AND GLink.Type='memberA')
INNER JOIN ObjectBase AS PGroup ON (PGroup.UID=GLink.UIDTarget)
INNER JOIN Member AS PGMember ON (PGMember.UID=PGroup.UID)
INNER JOIN ObjectBase AS Person ON (ObjectBase.UIDBelongsTo=Person.UID AND Person.Type='person')
INNER JOIN Links AS FLink ON (FLink.UIDTarget=ObjectBase.UID AND FLink.Type='function')
INNER JOIN ObjectBase AS FunctionT ON (FunctionT.UID=FLink.UID)
INNER JOIN Member ON (Member.UID=ObjectBase.UIDBelongsTo)
WHERE ObjectBase.Type='job' AND FunctionT.UID=?`,
[functionT]
);
await recreateJobs(UUID2hex(req.session.root), jobs, true);
return { success: true };
} catch (e) {
return { success: false, message: e };
}
};
/**
* Requalify all jobs for a function template
*
* @param {ExpressRequestAuthorized} req - Request object
* @param {Buffer} functionT - Function template UID
* @returns {Promise<Object>} Result with success status
*/
export const requalify = async (req, functionT) => {
try {
const jobs = await query(
`SELECT ObjectBase.UID, ObjectBase.UIDBelongsTo AS UIDperson,
FunctionT.Data AS FunctionData, FunctionT.UID AS UIDfunction,
ObjectBase.Data AS JobData, Member.Data AS MemberData
FROM ObjectBase
INNER JOIN Links AS FLink ON (FLink.UIDTarget=ObjectBase.UID AND FLink.Type='function')
INNER JOIN ObjectBase AS FunctionT ON (FunctionT.UID=FLink.UID)
INNER JOIN Member ON (Member.UID=ObjectBase.UIDBelongsTo)
WHERE ObjectBase.Type='job' AND FunctionT.UID=?`,
[functionT]
);
for (const job of jobs) {
const functionData = JSON.parse(job.FunctionData);
if (functionData.qualification) {
const qualified = await jobQualified(job.UIDperson, functionData.qualification);
const jobData = JSON.parse(job.JobData);
jobData.qualified = qualified;
await query(
`UPDATE ObjectBase SET Data=?, dindex=? WHERE UID=?`,
[JSON.stringify(jobData), qualified, job.UID]
);
}
}
await recreateJobs(UUID2hex(req.session.root), jobs, true);
return { success: true };
} catch (e) {
return { success: false, message: e };
}
};
/**
* Add a job to an event
*
* Creates or updates a job assignment linking a person to an event
* with a specific function template.
*
* @param {ExpressRequestAuthorized} req - Request object
* @param {Buffer} functionUID - Function template UID
* @param {Buffer} memberUID - Member/person UID
* @param {Buffer} eventUID - Event UID
* @param {Buffer} UIDjob - Job UID to create/update
* @param {boolean} update - Whether this is an update operation
* @returns {Promise<Object>} Result with created job object
* @throws {Error} When validation fails
*/
const addJob = async (req, functionUID, memberUID, eventUID, UIDjob, update = false) => {
const functionTemplates = await query(
`SELECT Data FROM ObjectBase WHERE UID=? AND Type='eventJobT'`,
[functionUID]
);
if (!functionTemplates.length) {
throw new Error('missing or unknown UIDfunction');
}
const functionTemplate = functionTemplates[0];
const functionData = JSON.parse(functionTemplate.Data);
const members = await query(
`SELECT Member.Data, Member.Display, Member.SortName,
pgroup.UID AS UIDgroup,
CONCAT(pgroup.Title,' ', pMember.Display) AS pGroup,
JSON_VALUE(pMember.Data,'$.banner') AS groupBanner
FROM Member
LEFT JOIN (ObjectBase AS pgroup
INNER JOIN Links AS GLink ON (GLink.UIDTarget = pgroup.UID)
INNER JOIN Member AS pMember ON (pMember.UID=pgroup.UID)
)
ON (GLink.UID=Member.UID AND GLink.Type='memberA')
WHERE Member.UID=?`,
[memberUID]
);
if (!members.length) {
throw new Error('invalid member UID');
}
const member = members[0];
const events = await query(
`SELECT Member.Data, Member.Display
FROM ObjectBase
INNER JOIN Member ON (Member.UID=ObjectBase.UID)
WHERE ObjectBase.UID=? AND ObjectBase.Type='event'`,
[eventUID]
);
if (!events.length) {
throw new Error('invalid event UID');
}
const event = events[0];
const qualified = await jobQualified(memberUID, functionData.qualification);
const template = Templates[req.session.root].eventJob;
const objectData = {
function: { ...functionData, functionUID: HEX2uuid(functionUID) },
member: JSON.parse(member.Data),
qualified: qualified
};
const object = await renderObject(template, objectData, req, 'events');
await query(
`INSERT INTO ObjectBase (UID,Type,UIDBelongsTo,Title,Display,SortName,dindex,gender,stage,hierarchie,Data)
VALUES (?,'eventJob',?,?,?,?,?,?,?,?,?) ON DUPLICATE KEY UPDATE
Type='eventJob',Title=VALUE(Title),Display=VALUE(Display), UIDBelongsTo=VALUE(UIDBelongsTo),
SortName=VALUE(SortName), dindex=VALUE(dindex),gender=VALUE(gender),stage=VALUE(stage),
hierarchie=VALUE(hierarchie),Data=VALUE(Data)`,
[
UIDjob, memberUID, object.Title, object.Display, object.SortIndex, qualified,
object.gender, object.stage, object.hierarchie,
JSON.stringify({ ...functionData, qualified: qualified })
]
);
// Link job to event
await query(
`INSERT IGNORE INTO Links(UID, Type, UIDTarget) VALUES (?,?,?)`,
[UIDjob, functionData.responsible ? 'memberA' : 'member', eventUID]
);
// Link job to function template
await query(
`INSERT IGNORE INTO Links(UID, Type, UIDTarget) VALUES (?,'function',?)`,
[functionUID, UIDjob]
);
// Publish events
publishEvent(
'/add/event/job/' + HEX2uuid(eventUID),
{ organization: req.session.root, data: [HEX2uuid(UIDjob)] }
);
if (functionData.role) {
publishEvent(
'/add/event/changeable/' + HEX2uuid(eventUID),
{ organization: req.session.root, data: [HEX2uuid(memberUID)] }
);
publishEvent(
'/add/event/leader/' + HEX2uuid(eventUID),
{ organization: req.session.root, data: [HEX2uuid(memberUID)] }
);
}
const typeVisible = functionData.responsible ? 'admin' : functionData.visible;
if (typeVisible && !update) {
await query(
`INSERT INTO Visible (UID,Type,UIDUser) VALUES(?,?,?) ON DUPLICATE KEY UPDATE Type=VALUE(Type)`,
[eventUID, typeVisible, memberUID]
);
}
addUpdateEntry(eventUID, {
addJob: {
job: {
UID: HEX2uuid(UIDjob),
UIDBelongsTo: HEX2uuid(memberUID),
Title: object.Title,
Display: member.Display,
UIDgroup: HEX2uuid(member.UIDgroup),
pGroup: member.pGroup,
groupBanner: member.groupBanner,
UIDTemplate: HEX2uuid(functionUID),
SortName: object.SortBase + member.SortName,
isRole: functionData.role,
isResponsible: functionData.responsible,
isMandatory: functionData.mandatory
}
}
});
return { success: true, result: { ...object, UID: UIDjob } };
};
/**
* Create or update job with template
*
* @param {string} member - Member UID as string
* @param {string} event - Event UID as string
* @param {string} functionTemplate - Function template UID as string
* @param {string} jobUIDStr - Job UID as string
* @param {ExpressRequestAuthorized} req - Request object
* @returns {Promise<Object>} Result with created/updated job
* @throws {Error} When validation fails or user not authorized
*/
export const putMemberFunctionTemplate = async (member, event, functionTemplate, jobUIDStr, req) => {
const eventUID = UUID2hex(event);
const memberUID = UUID2hex(member);
const functionUID = UUID2hex(functionTemplate);
if (!isObjectAdmin(req, eventUID)) {
throw new Error('user not authorized for this event');
}
let jobUID = UUID2hex(jobUIDStr);
// Check for duplicates
const exists = await query(
`SELECT ObjectBase.UID
FROM ObjectBase
INNER JOIN Links AS GLink ON (GLink.UID=ObjectBase.UID AND GLink.Type IN ('member','memberA'))
INNER JOIN Links AS FLink ON (FLink.UIDTarget=ObjectBase.UID AND FLink.Type='function')
WHERE GLink.UIDTarget=? AND FLink.UID=? AND ObjectBase.UIDBelongsTo=? AND ObjectBase.Type='eventJob'`,
[eventUID, functionUID, memberUID]
);
let update = false;
if (exists.length) {
jobUID = exists[0].UID;
update = true;
}
if (!isValidUID(jobUIDStr)) {
throw new Error('invalid UID format in body.UID');
}
return await addJob(req, functionUID, memberUID, eventUID, jobUID, update);
};
/**
* Create free job without template
*
* @param {string} member - Member UID as string
* @param {string} event - Event UID as string
* @param {string} jobName - Job name
* @param {string} jobUIDStr - Job UID as string
* @param {ExpressRequestAuthorized} req - Request object
* @returns {Promise<Object>} Result with created job
* @throws {Error} When validation fails
*/
export const createFreeJob = async (member, event, jobName, jobUIDStr, req) => {
const eventUID = UUID2hex(event);
const memberUID = UUID2hex(member);
if (!isObjectAdmin(req, eventUID)) {
throw new Error('user not authorized for this event');
}
if (!jobName || jobName === '') {
throw new Error('job name is required');
}
// Check for duplicates
const exists = await query(
`SELECT ObjectBase.UID
FROM ObjectBase
INNER JOIN Links AS GLink ON (GLink.UID=ObjectBase.UID AND GLink.Type IN ('member','memberA'))
WHERE GLink.UIDTarget=? AND ObjectBase.Title=? AND ObjectBase.UIDBelongsTo=? AND ObjectBase.Type='eventJob'`,
[eventUID, jobName, memberUID]
);
if (exists.length) {
throw new Error('this job is already assigned to this person');
}
const members = await query(
`SELECT Member.Data, Member.Display, Member.SortName,
pgroup.UID AS UIDgroup,
CONCAT(pgroup.Title,' ', pMember.Display) AS pGroup,
JSON_VALUE(pMember.Data,'$.banner') AS groupBanner
FROM Member
LEFT JOIN (ObjectBase AS pgroup
INNER JOIN Member AS pMember ON (pMember.UID=pgroup.UID)
INNER JOIN Links AS GLink ON (GLink.UIDTarget = pgroup.UID)
)
ON (GLink.UID=Member.UID AND GLink.Type='memberA')
WHERE Member.UID=?`,
[memberUID]
);
if (!members.length) {
throw new Error('invalid member UID');
}
const memberData = members[0];
const [eventData] = await query(
`SELECT Member.Data, Member.Display, ObjectBase.stage, ObjectBase.hierarchie, ObjectBase.gender
FROM ObjectBase
INNER JOIN Member ON (Member.UID=ObjectBase.UID)
WHERE ObjectBase.UID=? AND ObjectBase.Type='event'`,
[eventUID]
);
if (!eventData) {
throw new Error('invalid event UID');
}
const functionData = {
name: jobName,
functionUID: null,
stage: eventData.stage,
hierarchie: eventData.hierarchie,
gender: eventData.gender
};
const template = Templates[req.session.root].eventJob;
const objectData = {
function: functionData,
member: JSON.parse(memberData.Data),
qualified: true
};
const object = await renderObject(template, objectData, req, 'events');
if (!isValidUID(jobUIDStr)) {
throw new Error('invalid UID format in body.UID');
}
const jobUID = UUID2hex(jobUIDStr);
await query(
`INSERT INTO ObjectBase (UID,Type,UIDBelongsTo,Title,Display,SortName,dindex,gender,stage,hierarchie,Data)
VALUES (?,'eventJob',?,?,?,?,?,?,?,?,?) ON DUPLICATE KEY UPDATE
Type='eventJob',Title=VALUE(Title),Display=VALUE(Display), UIDBelongsTo=VALUE(UIDBelongsTo),
SortName=VALUE(SortName), dindex=VALUE(dindex),gender=VALUE(gender),stage=VALUE(stage),
hierarchie=VALUE(hierarchie),Data=VALUE(Data)`,
[
jobUID, memberUID, object.Title, object.Display, object.SortIndex, true,
object.gender, object.stage, object.hierarchie,
JSON.stringify({ ...functionData, qualified: true })
]
);
// Link job to event
await query(
`INSERT IGNORE INTO Links(UID, Type, UIDTarget) VALUES (?,?,?)`,
[jobUID, 'member', eventUID]
);
addUpdateEntry(eventUID, {
addJob: {
job: {
UID: HEX2uuid(jobUID),
UIDBelongsTo: HEX2uuid(memberUID),
Title: object.Title,
Display: memberData.Display,
UIDgroup: HEX2uuid(memberData.UIDgroup),
pGroup: memberData.pGroup,
groupBanner: memberData.groupBanner,
UIDTemplate: null,
SortName: object.SortBase + memberData.SortName,
isRole: false,
isResponsible: false,
isMandatory: false
}
}
});
return { success: true, result: { ...object, UID: HEX2uuid(jobUID) } };
};
/**
* Update job template assignment
*
* @param {string} jobUID - Job UID as string
* @param {string} templateUID - New template UID as string
* @param {ExpressRequestAuthorized} req - Request object
* @returns {Promise<boolean>} Success status
* @throws {Error} When validation fails
*/
export const updateJobTemplate = async (jobUID, templateUID, req) => {
const UIDjob = UUID2hex(jobUID);
const job = await query(
`SELECT Member.UID, ObjectBase.UIDBelongsTo, Member.Data, Member.Display, Member.SortName,
pgroup.UID AS UIDgroup, CONCAT(pgroup.Title,' ', pMember.Display) AS pGroup,
JSON_VALUE(pMember.Data,'$.banner') AS groupBanner, event.UID AS UIDevent,
fLink.UID AS UIDtemplate
FROM ObjectBase
INNER JOIN Member ON (Member.UID=ObjectBase.UIDBelongsTo)
INNER JOIN Links AS ELink ON (ELink.UID=ObjectBase.UID AND ELink.Type IN('memberA','member'))
INNER JOIN ObjectBase AS event ON (event.UID=ELink.UIDTarget AND event.Type='event')
LEFT JOIN Links AS fLink ON(fLink.UIDTarget=ObjectBase.UID AND fLink.Type='function')
LEFT JOIN (ObjectBase AS pgroup
INNER JOIN Links AS GLink ON (GLink.UIDTarget = pgroup.UID)
INNER JOIN Member AS pMember ON (pMember.UID=pgroup.UID)
)
ON (GLink.UID=Member.UID AND GLink.Type='memberA')
INNER JOIN Links ON (Links.UID=ObjectBase.UID AND Links.Type IN('member','memberA'))
WHERE ObjectBase.UID=? AND ObjectBase.Type='eventJob'`,
[UIDjob]
);
if (!job || job.length === 0) {
throw new Error('invalid job UID');
}
const memberData = JSON.parse(job[0].Data);
const UIDmember = job[0].UIDBelongsTo;
const UIDevent = job[0].UIDevent;
if (!isObjectAdmin(req, UIDevent)) {
throw new Error('you are not authorized to change this event');
}
const UIDTemplate = UUID2hex(templateUID);
const functionT = await query(
`SELECT Data, UIDBelongsTo FROM ObjectBase WHERE UID=? AND Type='eventJobT'`,
[UIDTemplate]
);
if (!functionT || functionT.length === 0) {
throw new Error('invalid UIDTemplate parameter in the body');
}
const functionData = JSON.parse(functionT[0].Data);
// Delete old link
await query(
`DELETE FROM Links WHERE Type='function' AND UIDTarget=?`,
[UIDjob]
);
// Update job
const qualified = await jobQualified(UIDmember, functionData.qualification);
const template = Templates[req.session.root].eventJob;
const objectData = {
function: { ...functionData, functionUID: templateUID },
member: memberData,
qualified: qualified
};
const object = await renderObject(template, objectData, req, 'events');
addUpdateEntry(UIDevent, {
updateJob: {
job: {
UID: jobUID,
UIDBelongsTo: HEX2uuid(job[0].UID),
Title: object.Title,
Display: job[0].Display,
UIDgroup: HEX2uuid(job[0].UIDgroup),
pGroup: job[0].pGroup,
groupBanner: job[0].groupBanner,
UIDTemplate: templateUID,
SortName: object.SortIndex + job[0].SortName,
isRole: functionData.role,
isResponsible: functionData.responsible,
isMandatory: functionData.mandatory
}
}
});
await query(
`UPDATE ObjectBase SET Title=?,Display=?,SortName=?,dindex=?,Data=? WHERE UID=?`,
[
object.Title, object.Display, object.SortIndex, qualified,
JSON.stringify({ ...functionData, qualified: qualified }), UIDjob
]
);
// Update membership link
await query(
`UPDATE Links SET Type=? WHERE UID=? AND UIDTarget=? AND Type IN ('member','memberA')`,
[functionData.responsible ? 'memberA' : 'member', UIDjob, UIDevent]
);
if ((job[0].UIDtemplate && !job[0].UIDtemplate.equals(UIDTemplate)) || !job[0].UIDtemplate) {
// Delete old template link
if (job[0].UIDtemplate) {
await query(
`DELETE FROM Links WHERE UID=? AND UIDTarget=? AND Type='function'`,
[job[0].UIDtemplate, UIDjob]
);
}
// Link to new template
await query(
`INSERT IGNORE INTO Links(UID, Type, UIDTarget) VALUES (?,'function',?)`,
[UIDTemplate, UIDjob]
);
}
return true;
};
/**
* Set responsible person for event
*
* @param {string} event - Event UID as string
* @param {string} personUID - Person UID as string
* @param {ExpressRequestAuthorized} req - Request object
* @returns {Promise<Object>} Result with created job
* @throws {Error} When validation fails
*/
export const setResponsible = async (event, personUID, req) => {
const UIDevent = UUID2hex(event);
if (!isObjectAdmin(req, UIDevent)) {
throw new Error('you are not authorized to change this event');
}
// Delete old responsible job
await query(
`DELETE Links, ObjectBase FROM ObjectBase
INNER JOIN Links ON (Links.UID=ObjectBase.UID)
WHERE ObjectBase.Type='eventJob' AND Links.Type='memberA' AND Links.UIDTarget=?`,
[UIDevent]
);
// Get leader template
const resultT = await query(
`SELECT Main.UID, Main.Display, Main.SortName, Main.Data
FROM ObjectBase AS Main
INNER JOIN Links AS TLink ON (TLink.Type='event' AND Main.UIDBelongsTo=TLink.UIDTarget)
WHERE TLink.UID=? AND Main.Type='eventJobT' AND JSON_VALUE(Main.Data,'$.responsible')`,
[UIDevent]
);
// Add new leader with leader template
if (resultT.length > 0) {
const [{ UIDjob }] = await query(`SELECT UIDV1() AS UIDjob`, []);
return await addJob(req, resultT[0].UID, UUID2hex(personUID), UIDevent, UIDjob);
}
return { success: false, message: 'No leader template found' };
};
/**
* Delete a job
*
* @param {string} jobUID - Job UID as string
* @param {ExpressRequestAuthorized} req - Request object
* @returns {Promise<boolean>} Success status
* @throws {Error} When job not found or user not authorized
*/
export const deleteJob = async (jobUID, req) => {
const UID = UUID2hex(jobUID);
const [job] = await query(
`SELECT ObjectBase.UID, ObjectBase.UIDBelongsTo AS user, Links.UIDTarget
FROM ObjectBase
INNER JOIN Links ON (Links.UID=ObjectBase.UID AND Links.Type IN ('memberA','member'))
WHERE ObjectBase.UID=? AND ObjectBase.Type='eventJob'`,
[UID]
);
if (!job) {
throw new Error('job not found');
}
if (!isObjectAdmin(req, job.UIDTarget)) {
throw new Error('you are not authorized to change this event');
}
// Delete job
await query(`DELETE FROM ObjectBase WHERE UID=? AND Type='eventJob'`, [UID]);
// Delete links
await query(`DELETE FROM Links WHERE UID=?`, [UID]);
addUpdateEntry(job.UIDTarget, { deleteJob: { UID: jobUID } });
publishEvent(
'/remove/event/job/' + HEX2uuid(job.UIDTarget),
{ organization: req.session.root, data: [HEX2uuid(job.UID)] }
);
return true;
};
/**
* Delete all jobs for a person in an event
*
* @param {string} event - Event UID as string
* @param {string} person - Person UID as string
* @param {ExpressRequestAuthorized} req - Request object
* @returns {Promise<boolean>} Success status
* @throws {Error} When validation fails
*/
export const deleteLeaderJobs = async (event, person, req) => {
const UIDevent = UUID2hex(event);
const UIDperson = UUID2hex(person);
if (!UIDevent || !UIDperson) {
throw new Error('invalid parameters');
}
if (!isObjectAdmin(req, UIDevent)) {
throw new Error('you are not authorized to change this event');
}
const jobs = await query(
`SELECT ObjectBase.UID, ObjectBase.UIDBelongsTo AS user, Links.UIDTarget
FROM ObjectBase
INNER JOIN Links ON (Links.UID=ObjectBase.UID AND Links.Type IN ('memberA','member'))
INNER JOIN ObjectBase AS event ON (event.UID=Links.UIDTarget)
WHERE ObjectBase.UIDBelongsTo=? AND ObjectBase.Type='eventJob' AND Links.UIDTarget=?`,
[UIDperson, UIDevent]
);
if (jobs.length > 0) {
// Delete jobs and links
await query(
`DELETE Links, ObjectBase FROM ObjectBase
INNER JOIN Links ON(Links.UID=ObjectBase.UID)
WHERE Links.UIDTarget=? AND ObjectBase.UID IN (?) AND ObjectBase.Type='eventJob'`,
[UIDevent, jobs.map(el => el.UID)]
);
addUpdateEntry(UIDevent, { deleteJobs: jobs.map(j => HEX2uuid(j.UID)) });
publishEvent(
'/remove/event/job/' + HEX2uuid(UIDevent),
{ organization: req.session.root, data: jobs.map(el => HEX2uuid(el.UID)) }
);
publishEvent(
'/remove/event/leader/' + HEX2uuid(UIDevent),
{ organization: req.session.root, data: [HEX2uuid(UIDperson)] }
);
}
return true;
};
/**
* Get single job details
*
* @param {string} jobUID - Job UID as string
* @returns {Promise<Object>} Job object with details
* @throws {Error} When job not found
*/
export const getJob = async (jobUID) => {
const UIDjob = UUID2hex(jobUID);
const result = await query(
`SELECT
ObjectBase.UID, ObjectBase.UIDBelongsTo, ObjectBase.Title, ObjectBase.dindex AS qualified,
Member.Display, Member.Data AS personData, jobT.Data AS templateData,
event.Data AS eventData, event.UID AS UIDevent
FROM ObjectBase AS ObjectBase
INNER JOIN ObjectBase AS Main ON (Main.UID=ObjectBase.UIDBelongsTo AND Main.Type IN('extern','person'))
INNER JOIN Member ON (Member.UID=Main.UIDBelongsTo)
LEFT JOIN (Links AS TLink INNER JOIN ObjectBase AS jobT
ON (jobT.UID=TLink.UID AND jobT.Type='eventJobT'))
ON (ObjectBase.UID=TLink.UIDTarget AND TLink.Type='function')
INNER JOIN Links ON (Links.UID=ObjectBase.UID AND Links.Type IN('member','memberA'))
INNER JOIN Member AS event ON (Links.UIDTarget=event.UID)
WHERE ObjectBase.UID=?`,
[UIDjob],
{ cast: ['UUID', 'json'] }
);
if (!result || result.length === 0) {
throw new Error('job not found');
}
return result[0];
};
/**
* Get list of jobs for an event
*
* @param {ExpressRequestAuthorized} req - Request object with query parameters
* @returns {Promise<Array>} Array of job objects
*/
export const getJobsListing = async (req) => {
let dataFields = '';
if (req.query.Data) {
if (req.query.Data === 'all') {
dataFields = ',Member.Data AS Data';
} else {
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)}`;
}
}
}
const result = await query(
`SELECT
ObjectBase.UID, ObjectBase.UIDBelongsTo, ObjectBase.Title, ObjectBase.dindex AS qualified,
Member.Display, JSON_VALUE(Member.Data,'$.avatar') AS avatar,
pgroup.UID AS UIDgroup, JSON_VALUE(Member.Data,'$.gender') AS gender,
CONCAT(pgroup.Title,' ', pMember.Display) AS pGroup,
JSON_VALUE(pgroup.Data,'$.banner') AS groupBanner,
TLink.UID AS UIDTemplate, CONCAT(ObjectBase.SortName,Member.SortName) AS SortName,
JSON_EXTRACT(jobT.Data,'$.role') AS isRole,
JSON_EXTRACT(jobT.Data,'$.responsible') AS isResponsible,
JSON_EXTRACT(jobT.Data,'$.mandatory') AS isMandatory,
JSON_VALUE(Member.Data,'$.email[0].email') AS email
${dataFields}
FROM ObjectBase AS ObjectBase
INNER JOIN ObjectBase AS Main ON (Main.UID=ObjectBase.UIDBelongsTo AND Main.Type IN('extern','person'))
INNER JOIN Member ON (Member.UID=Main.UIDBelongsTo)
LEFT JOIN (Links AS TLink INNER JOIN ObjectBase AS jobT
ON (jobT.UID=TLink.UID AND jobT.Type='eventJobT'))
ON (ObjectBase.UID=TLink.UIDTarget AND TLink.Type='function')
LEFT JOIN (ObjectBase AS pgroup
INNER JOIN Links AS GLink ON (GLink.UIDTarget = pgroup.UID)
INNER JOIN Member AS pMember ON (pMember.UID=pgroup.UID)
)
ON (GLink.UID=Main.UID AND GLink.Type='memberA')
INNER JOIN Links ON (Links.UID=ObjectBase.UID AND Links.Type IN('member','memberA'))
WHERE Links.UIDTarget=? AND ObjectBase.Type='eventJob'
GROUP BY ObjectBase.UID
ORDER BY ObjectBase.SortName, Member.SortName`,
[UUID2hex(req.params.UID)],
{
log: false,
cast: ['UUID', 'json']
}
);
return result;
};