/**
* Person Controller
*
* Contains all the business logic for person operations:
* - Creating and updating persons
* - Managing group memberships
* - Retrieving person data and history
* - Checking admin rights and visibility
* - Finding duplicates
* - Managing jobs and job groups
* - Age updates
*
* All functions handle various person types (person, job, guest, extern).
*/
// @ts-check
/**
* @import {ExpressRequestAuthorized, ExpressResponse} from './../../types.js'
*/
import { query, pool, UUID2hex, HEX2uuid } from '@commtool/sql-query';
import { parseTimestampToSeconds } from '../../utils/parseTimestamp.js';
import { renderObject } from '../../utils/renderTemplates.js';
import { Templates, BannerRules, getConfig } from '../../utils/compileTemplates.js';
import { getUID, isValidUID } from '../../utils/UUIDs.js';
import { addUpdateList, addUpdateEntry } from '../../server.ws.js';
import { isAdmin, isChangeable, isObjectAdmin, isObjectVisible } from '../../utils/authChecks.js';
import { matchObject } from '@commtool/object-filter';
import { doubleMetaphone } from 'double-metaphone';
import { errorLoggerRead, errorLoggerUpdate } from '../../utils/requestLogger.js';
import { decryptIbans, encryptIbans } from '../../utils/crypto.js';
import {
updatePersonPartial,
updateGroupMembershipShared,
} from './personHelpers.js';
import * as personService from './service.js';
// *************** Exported Controller Functions ***************
/**
* Updates the age (`dindex` field) of persons associated with a given root UID
* @param {Object} req - Express request object
* @param {Object} res - Express response object
*/
export const ageUpdate = async (req, res) => {
try {
const count = await personService.ageUpdatePersons(
UUID2hex(req.session.root),
req.session.user,
req.session.root
);
res.json({ success: true, result: count });
} catch (e) {
errorLoggerUpdate(e);
res.status(500).json({ success: false, message: 'Internal server error' });
}
};
/**
* Updates group membership for a person
* @param {ExpressRequestAuthorized} req - Express request object
* @param {ExpressResponse} res - Express response object
*/
export const updateGroupMembership = async ( req, res) => {
return updateGroupMembershipShared(req, res, 'person', isObjectAdmin);
};
/**
* Updates a person's data in the database (partial update endpoint)
*
* This endpoint (POST /:UID) performs partial data updates by merging the request body
* with existing data. It only updates the Member table, not ObjectBase fields.
* Used by both person and extern endpoints via delegation.
*
* @param {ExpressRequestAuthorized} req - Express request object
* @param {ExpressResponse} res - Express response object
*/
export const updatePerson = async (req, res) => {
try {
if (!isObjectAdmin(req, req.params.UID)) {
res.status(403).json({ success: false, message: 'user not authorized for this person' });
return;
}
const UID = UUID2hex(req.params.UID);
const result = await updatePersonPartial(UID, req.body, req);
res.json({ success: true, result });
} catch (e) {
errorLoggerUpdate(e);
res.status(500).json({ success: false, message: 'Internal server error' });
}
};
/**
* Creates or updates a person entry in the database
* @param {ExpressRequestAuthorized} req - Express request object
* @param {ExpressResponse} res - Express response object
*/
export const createOrUpdatePerson = async (req, res) => {
try {
const timestamp = parseTimestampToSeconds(req.query.timestamp ? String(req.query.timestamp) : undefined);
const asOf = timestamp ? `FOR SYSTEM_TIME AS OF FROM_UNIXTIME(${timestamp})` : '';
const config = await getConfig('db', req);
const UIDgroup = UUID2hex(req.params.group);
const UIDperson = await getUID(req);
if (!UIDperson) {
res.status(400).json({ success: false, message: 'invalid UID' });
return;
}
const group = await personService.fetchGroup(UIDgroup);
if (!group) {
res.status(404).json({ success: false, message: 'group not found' });
return;
}
const groupData = group.Data;
if (group.gender === 'C') {
res.status(400).json({ success: false, message: 'persons cannot be added to a combined-gender group (gender C)' });
return;
}
req.body.hierarchie = groupData.hierarchie;
req.body.stage = groupData.stage === 0 ? (req.body.stage || config.AdultStage) : groupData.stage;
const belongsTo = UUID2hex(req.body.UID);
const template = Templates[req.session.root]?.person;
const object = await renderObject(template, { ...req.body, group: groupData }, req);
object.Data = { ...req.body, UID: undefined };
encryptIbans(config, object.Data);
if (BannerRules[req.session.root].member[0] === 'inherit' && groupData.banner) {
object.Data.banner = groupData.banner;
}
const persons = await personService.fetchPersonExists(UIDperson, asOf);
// Safeguard: when changing an existing extern → person with a backdate,
// the timestamp must not land before the latest ObjectBase history row —
// that would create an impossible temporal ordering in the versioned table.
if (persons.length > 0 && timestamp && persons[0].Type !== 'person') {
const latestValidFrom = await personService.fetchLatestObjectValidFrom(UIDperson);
if (latestValidFrom !== null && timestamp < latestValidFrom) {
res.status(300).json({
success: false,
message: `Cannot backdate to ${timestamp}: type change (extern → person) must not precede the latest existing history row (${latestValidFrom}).`,
});
return;
}
}
if (persons.length === 0) {
await personService.createNewPerson(object, belongsTo, UIDgroup, req.session.user, req.session.root, timestamp);
} else {
await personService.updateExistingPerson(persons[0], object, UIDperson, UIDgroup, req, timestamp);
}
object.admin = await isObjectAdmin(req, UIDperson);
addUpdateEntry(UIDperson, { person: { Type: 'person', ...object, Data: { ...req.body, UID: undefined }, UID: HEX2uuid(object.UID) }, parent: group });
addUpdateList([UIDgroup, UIDperson]);
res.json({ success: true, result: { ...object, UID: HEX2uuid(object.UID) } });
} catch (e) {
errorLoggerUpdate(e);
res.status(500).json({ success: false, message: 'Internal server error' });
}
};
/**
* Retrieves detailed information about a person, job, guest, or extern object
* @param {ExpressRequestAuthorized} req - Express request object
* @param {ExpressResponse} res - Express response object
*/
export const getPerson = async (req, res) => {
try {
let visible = `INNER JOIN Visible ON (Visible.UID=ObjectBase.UID AND Visible.UIDUser=U_UUID2BIN('${req.session.user}'))`;
if (await isAdmin(req.session)) {
visible = '';
}
const UID = UUID2hex(req.params.UID);
const result2 = await query(`SELECT ObjectBase.UID,ObjectBase.UIDBelongsTo,ObjectBase.Type,ObjectBase.Title,Member.Display AS Display,
dindex,ObjectBase.gender, ObjectBase.hierarchie, ObjectBase.stage,Family.Data AS FamilyData,
Member.Data AS Data,ObjectBase.Data AS BaseData ,UNIX_TIMESTAMP(ObjectBase.validFrom) AS validFrom
FROM ObjectBase INNER JOIN Member ON (Member.UID=ObjectBase.UIDBelongsTo)
LEFT JOIN (Links INNER JOIN Member AS Family ON (Family.UID=Links.UIDTarget AND Links.Type IN ('member','memberA')))
ON (Links.UID=ObjectBase.UIDBelongsTo)
${visible}
WHERE ObjectBase.Type IN ('person','job','guest','extern') AND (ObjectBase.UIDBelongsTo=? OR ObjectBase.UID=?)`,
[UID, UID],
{
cast: ['UUID', 'json'],
log: false,
group: (result, current) => {
if (result.length === 1 && ['extern', 'person'].includes(result[0].Type))
return result;
if (['extern', 'person'].includes(current.Type))
return [current];
else if (current.Type === 'job') {
if (result.find(r => r.Type === 'job' && r.UID === current.UID))
return result;
return [...result, current];
} else {
return [...result, current];
}
}
}
);
const resa = result2;
if (resa.length === 0) {
res.json({ success: false, message: `object ${req.params.UID} does not exist or user has no proper access rights` });
return;
}
const result = resa[0];
result.admin = await isObjectAdmin(req, UID);
if (result.admin && ['person', 'extern'].includes(result.Type)) {
decryptIbans(result.Data);
}
// Handle optional data retrieval
if (req.query.parent) {
const p = await query(`SELECT ObjectBase.UID,ObjectBase.Title,Member.Display,ObjectBase.Type,
ObjectBase.dindex,Member.Data, ObjectBase.gender, ObjectBase.hierarchie, ObjectBase.stage, ObjectBase.validFrom,
ObjectBase.UIDBelongsTo,
UNIX_TIMESTAMP(ObjectBase.validFrom) AS validFrom, UNIX_TIMESTAMP(Links.validFrom) AS LvalidFrom
FROM ObjectBase
INNER JOIN Member ON (ObjectBase.UID=Member.UID)
INNER JOIN Links ON (Links.UIDTarget=ObjectBase.UID AND Links.Type='memberA')
WHERE Links.UID=?`, [UID], { cast: ['UUID', 'json'] });
if (p.length > 0) {
const ps = await query(`SELECT Siblings.UID,Siblings.Title,Member.Display,
Siblings.dindex,Siblings.Data,Siblings.gender, Siblings.hierarchie, Siblings.stage, UNIX_TIMESTAMP(Siblings.validFrom) AS validFrom
FROM ObjectBase
INNER JOIN ObjectBase AS Siblings ON (ObjectBase.UIDBelongsTo=Siblings.UIDBelongsTo AND Siblings.Type='group')
INNER JOIN Member ON (Member.UID=Siblings.UID)
INNER JOIN Links ON (Links.UIDTarget=Siblings.UID AND Links.Type='member')
WHERE Links.UID=? AND ObjectBase.UID =? AND Siblings.UID<>?
GROUP BY ObjectBase.UID`,
[UID, UUID2hex(p[0].UIDBelongsTo), UUID2hex(p[0].UID)],
{ cast: ['UUID', 'json'] });
result.parent = p[0];
if (ps.length > 0) {
result.parentS = ps;
}
}
}
if (req.query.siblings) {
const s = await query(`SELECT ObjectBase.UID,ObjectBase.Type,ObjectBase.Title,IF(Member.UID IS NULL,ObjectBase.Display,Member.Display) AS Display,
ObjectBase.dindex, IF(Member.UID IS NULL,ObjectBase.Data,Member.Data) AS Data, ObjectBase.hierarchie, ObjectBase.gender, ObjectBase.stage ,UNIX_TIMESTAMP(ObjectBase.validFrom) AS validFrom
FROM ObjectBase LEFT JOIN Member ON (Member.UID=ObjectBase.UIDBelongsTo)
WHERE UIDBelongsTo=? AND ObjectBase.UID <> ? AND ObjectBase.Type IN ('job','extern','guest','achievement')`,
[UID, UID], { cast: ['UUID', 'json'] });
if (s.length > 0) {
result.siblings = s;
}
}
if (req.query.main) {
const m = await query(`SELECT ObjectBase.UID,ObjectBase.Title,ObjectBase.Display,
ObjectBase.dindex,ObjectBase.Data ObjectBase.hierarchie, ObjectBase.gender, ObjectBase.stage,UNIX_TIMESTAMP(ObjectBase.validFrom) AS validFrom
FROM ObjectBase WHERE UID<>? AND UID = ?`, [UID, UUID2hex(result.UIDBelongsTo)], { cast: ['UUID', 'json'] });
if (m.length > 0) {
result.main = m[0];
}
}
const config = await getConfig('db', req);
res.json({ success: true, result: result });
} catch (e) {
errorLoggerRead(e);
res.status(500).json({ success: false, message: 'Internal server error' });
}
};
/**
* Retrieves the historical group membership information for a person
* @param {ExpressRequestAuthorized} req - Express request object
* @param {ExpressResponse} res - Express response object
*/
export const personHistorie = async (req, res) => {
try {
const result = await query(`
SELECT
MyGroup.Title AS GroupTitle,mgroup.Display,MyGroup.UID AS UIDGroup,
UNIX_TIMESTAMP(Links.validFrom) AS ValidFrom
FROM Links FOR SYSTEM_TIME ALL
INNER JOIN ObjectBase ON (Links.UID=ObjectBase.UID AND Links.Type='memberA')
INNER JOIN ObjectBase FOR SYSTEM_TIME ALL AS MyGroup ON (MyGroup.UID=Links.UIDTarget AND MyGroup.Type='group'
AND MyGroup.validFrom <= Links.validFrom
AND (MyGroup.validUntil > Links.validFrom OR MyGroup.validUntil = TIMESTAMP'2038-01-19 03:14:07'))
INNER JOIN Member AS mgroup ON (mgroup.UID=MyGroup.UID)
WHERE Links.UID=?
GROUP BY Links.validFrom, MyGroup.UID
ORDER BY Links.validUntil DESC`,
[UUID2hex(req.params.UID)], { log: true, cast: ['UUID', 'json'] });
res.json({ success: true, result: result });
} catch (e) {
errorLoggerRead(e);
res.status(500).json({ success: false, message: 'Internal server error' });
}
};
/**
* Checks if the logged-in user has admin rights for a given person UID
* @param {ExpressRequestAuthorized} req - Express request object
* @param {ExpressResponse} res - Express response object
*/
export const personAdmin = async (req, res) => {
try {
if (!req.query.user && !req.query.loginUser) {
const admin = await isObjectAdmin(req, req.params.UID);
res.json({ success: true, result: admin });
} else {
let user = UUID2hex(req.query.user);
if (req.query.loginUser) {
let users = await query(`SELECT Links.UIDTarget AS UID FROM Links
INNER JOIN Links AS MLink ON (Links.UIDTarget=MLink.UID AND MLink.Type IN ('member','memberA','memberSys'))
INNER JOIN Member AS MUser ON (MLink.UID=MUser.UID)
WHERE Links.UID=? AND Links.Type='identifyer' AND MLink.UIDTarget=?`,
[UUID2hex(req.query.loginUser), UUID2hex(req.session.root)]);
user = users[0].UID;
}
if (!user) {
res.json({ success: false, message: 'invalid user requested' });
return;
}
const visible = await isObjectVisible(req, user);
if (visible) {
const admin = await isChangeable(UUID2hex(req.params.UID), user);
res.json({ success: true, result: admin });
} else {
res.json({ success: false, message: 'user not authorized to this user' });
}
}
} catch (e) {
errorLoggerRead(e);
res.status(500).json({ success: false, message: 'Internal server error' });
}
};
/**
* Checks the visibility of a person and retrieves related job and member data
* @param {ExpressRequestAuthorized} req - Express request object
* @param {ExpressResponse} res - Express response object
*/
export const doCheckVisible = async (req, res) => {
try {
const timestamp = parseTimestampToSeconds(req.query.timestamp ? String(req.query.timestamp) : undefined);
const asOf = timestamp ? `FOR SYSTEM_TIME AS OF FROM_UNIXTIME(${timestamp})` : '';
const result = await query(`SELECT job.UID, Member.Data,job.Data AS ExtraData, job.Title, job.Display, job.UIDBelongsTo, Member.Display, job.Type
FROM ObjectBase ${asOf} AS job
INNER JOIN Member ON (Member.UID=job.UIDBelongsTo)
INNER JOIN Links ${asOf} AS gLink ON (job.UID=gLink.UID AND gLink.Type ='memberA')
INNER JOIN Links ${asOf} AS pLink ON (pLink.UIDTarget=gLink.UIDTarget AND pLink.Type IN ('member','memberA'))
INNER JOIN ObjectBase ${asOf} AS person ON (person.UID=pLink.UID )
WHERE person.UID=? AND job.Type='job'
GROUP BY job.UID`,
[UUID2hex(req.params.UID)],
{
log: false,
cast: ['UUID', 'json'],
filter: req.query.dataFilter ? (row) => matchObject(row, JSON.parse(String(req.query.dataFilter))) : null,
group: (result, current) => {
const index = result.findIndex(el => el.UIDBelongsTo === current.UIDBelongsTo);
if (index >= 0) {
return result;
}
return [...result, current];
}
});
res.json({ success: true, result });
} catch (e) {
errorLoggerRead(e);
res.status(500).json({ success: false, message: 'Internal server error' });
}
};
/**
* Finds potential duplicate person records based on phonetic similarity
* @param {ExpressRequestAuthorized} req - Express request object
* @param {ExpressResponse} res - Express response object
*/
export const personDuplicates = async (req, res) => {
try {
const [firstFirst] = req.params.firstName.split(/\s/);
const [firstLast] = req.params.lastName.split(/\s/);
const [phonFirst0, phonFirst1] = doubleMetaphone(firstFirst.replace('ß', 'ss'));
const [phonLast0, phonLast1] = doubleMetaphone(firstLast.replace('ß', 'ss'));
const duplications = await query(`SELECT Member.UID,Member.Display,ObjectBase.Title FROM Member
INNER JOIN ObjectBase ON (ObjectBase.UID=Member.UID)
INNER JOIN Links ON (Links.UID=Member.UID AND Links.Type IN ('member','memberA','memberSys'))
WHERE (PhonetikIndex REGEXP ? OR PhonetikIndex REGEXP ?) AND (PhonetikIndex REGEXP ? OR PhonetikIndex REGEXP ?)
AND Links.UIDTarget=?
`,
[`\\b${phonFirst0}\\b`, `\\b${phonFirst1}\\b`, `\\b${phonLast0}\\b`, `\\b${phonLast1}\\b`,UUID2hex(req.session.root)],
{ cast: ['UUID', 'json'] });
res.json({ success: true, result: duplications });
} catch (e) {
errorLoggerRead(e);
res.status(500).json({ success: false, message: 'Internal server error' });
}
};
/**
* GET /minTimestamp/:group/:UID — return the minimum allowed backdate timestamp (ms)
* for adding a person/extern to a specific target group.
* @param {ExpressRequestAuthorized} req
* @param {ExpressResponse} res
*/
export const minTimestamp = async (req, res) => {
try {
const personUID = UUID2hex(req.params.UID);
const groupUID = UUID2hex(req.params.group);
const minSeconds = await personService.getPersonMinBackdate(personUID, groupUID);
res.json({ success: true, result: minSeconds * 1000 });
} catch (e) {
errorLoggerRead(e);
res.status(500).json({ success: false, message: 'Internal server error' });
}
};
/**
* POST /rebuildAccess/:UID — Rebuild visibility and list access for a person.
* @param {ExpressRequestAuthorized} req
* @param {ExpressResponse} res
*/
export const rebuildPersonAccess = async (req, res) => {
try {
const UID = UUID2hex(req.params.UID);
await personService.rebuildPersonAccess(UID, req.session.root);
res.json({ success: true });
} catch (e) {
errorLoggerUpdate(e);
res.status(500).json({ success: false, error: e.message });
}
};
export const jobgroups = (visibility, grouped = true) => async (req, res) => {
try {
const visibilityCondition = visibility === 'changeable' ? ` AND Visible.Type='changeable' ` : '';
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];
}
if (!Array.isArray(fields)) {
fields = [fields];
}
for (const field of fields) {
if (!field.query)
dataFields += `,JSON_VALUE(Member.Data,${pool.escape(field.path)}) AS ${pool.escape(field.alias)}`;
else
dataFields += `,JSON_QUERY(Member.Data,${pool.escape(field.path)}) AS ${pool.escape(field.alias)}`;
}
}
}
if (req.query.ExtraData) {
dataFields += `,job.Data AS ExtraData`;
}
if (req.query.groupBanner) {
dataFields += `,JSON_VALUE(GMember.Data,'$.banner') AS groupBanner`;
}
const timestamp = parseTimestampToSeconds(req.query.timestamp ? String(req.query.timestamp) : undefined);
let asOf = timestamp ? `FOR SYSTEM_TIME AS OF FROM_UNIXTIME(${timestamp})` : '';
const groupBy = grouped ? 'GROUP BY MainGroup.UID' : '';
const result = await query(`
SELECT MainGroup.UID, MainGroup.Title AS TitleGroup, GMember.Display, job.UID AS UIDjob, job.Title AS TitleJob, CONCAT(MainGroup.Title ,' ', GMember.Display) AS pGroup,
UNIX_TIMESTAMP(gLink.validFrom) AS validFrom
${dataFields}
FROM ObjectBase AS job
INNER JOIN Links ${asOf} AS gLink ON (job.UID=gLink.UID AND gLink.Type IN ('memberA','memberSys'))
LEFT JOIN Links ${asOf} AS sLink ON (sLink.UID=gLink.UIDTarget AND sLink.Type='memberS')
INNER JOIN ObjectBase ${asOf} AS MainGroup ON (MainGroup.UID=gLink.UIDTarget OR MainGroup.UID=sLink.UIDTarget)
INNER JOIN Member AS GMember ON (MainGroup.UID=GMember.UID)
INNER JOIN Member ON (job.UIDBelongsTo=Member.UID)
INNER JOIN Visible ON (Visible.UID=MainGroup.UID ${visibilityCondition} AND Visible.UIDUser=?)
WHERE job.UIDBelongsTo=? AND job.Type='job'
${groupBy}
ORDER BY MainGroup.SortName,Member.SortName`,
[UUID2hex(req.session.user), UUID2hex(req.params.UID)],
{ log: false, cast: ['UUID', 'json'] });
res.json({ success: true, result });
} catch (e) {
errorLoggerRead(e);
res.status(500).json({ success: false, message: 'Internal server error' });
}
};