// @ts-check
/**
* @import {ExpressRequestAuthorized, ExpressResponse} from '../../types.js'
*/
import { query, pool, UUID2hex } from '@commtool/sql-query';
import { parseTimestampToSeconds } from '../../utils/parseTimestamp.js';
import errorHandler from '../../errorHander.js';
import { isValidUID } from '../../utils/UUIDs.js';
import { matchObject } from '@commtool/object-filter';
import mysqlTime from '../../utils/mysqlTime.js';
import { isAdmin } from '../../utils/authChecks.js';
import { errorLoggerRead, dbLogger } from '../../utils/requestLogger.js';
import { getSuperAdmin } from '../orga/service.js';
/**
* @typedef {'person'|'guest'|'extern'|'family'|'job'|'entry'|'eventJob'} PersonType
*/
/**
* @typedef {Object} DataFieldSpec
* @property {string} path - JSON path expression
* @property {string} alias - Column alias name
* @property {boolean} [query] - Use JSON_QUERY instead of JSON_VALUE
*/
/**
* @typedef {Object} PersonListItem
* @property {string} UID - Object UID (UUID string)
* @property {PersonType} Type - Object type
* @property {string} UIDBelongsTo - Parent person UID
* @property {string|null} Title - Title / role label
* @property {string} Display - Member display name
* @property {string} SortName - Sort key
* @property {string|null} UIDgroup - Primary group UID
* @property {string|null} pGroup - Primary group display label
* @property {number|null} hierarchie - Hierarchy level
* @property {number|null} stage - Stage / level
* @property {string|null} gender - Gender
* @property {number|null} dindex - Display index
* @property {'visible'|'changeable'} [visibility] - Visibility type
*/
// ---------------------------------------------------------------------------
// Helpers
// ---------------------------------------------------------------------------
/**
* Parse and validate the `type` query parameter.
* Falls back to `['person']` when the parameter is absent or invalid.
* Only allowed types are returned to prevent injection via illegal type values.
*
* @param {ExpressRequestAuthorized} req
* @returns {PersonType[]}
*/
export const getType = (req) => {
/** @type {string[]} */
let type = ['person'];
if (req.query.type) {
try {
const t = req.query.type;
if (Array.isArray(t)) {
type = t.map(v => String(v));
} else {
type = JSON.parse(String(t));
}
} catch (e) {
type = Array.isArray(req.query.type)
? req.query.type.map(v => String(v))
: [String(req.query.type)];
}
}
// Allowlist – prevents revealing data for unlisted object types
type = type.filter(el =>
(['person', 'guest', 'extern', 'family', 'job', 'entry', 'eventJob'].includes(el))
);
return /** @type {PersonType[]} */ (type);
};
/**
* Build the SQL snippet that restricts the `Visible` table join to the
* correct user / admin context.
*
* - Super-admins: restricted to the super-admin visible user
* - Org-admins: `changeable` visibility only for non-group targets
* - Normal users: full visibility of their own objects
*
* @param {Buffer|null} UID - Target object UID (hex Buffer) or null for bulk queries
* @param {ExpressRequestAuthorized} req
* @returns {Promise<string>}
*/
export const getListVisibleSql = async (UID, req) => {
try {
const authReq = /** @type {ExpressRequestAuthorized} */ (/** @type {unknown} */ (req));
if (await isAdmin(authReq.session)) {
const superadmin = await getSuperAdmin(authReq.session.root);
return ` AND Visible.UIDUser=U_UUID2BIN('${superadmin}') `;
}
const user = UUID2hex(authReq.session.user);
const targetType = await query(
`SELECT ObjectBase.Type, Visible.Type AS TypeVisible
FROM ObjectBase
INNER JOIN Visible ON (Visible.UID=ObjectBase.UID)
WHERE ObjectBase.UID=? AND Visible.UIDUser=?`,
[UID, user]
);
if (targetType.length === 0)
return ` AND Visible.UIDUser=U_UUID2BIN('${authReq.session.user}') AND Visible.Type='changeable' `;
if (targetType[0].Type !== 'group' && targetType[0].TypeVisible === 'visible') {
return ` AND Visible.UIDUser=U_UUID2BIN('${authReq.session.user}') AND Visible.Type='changeable' `;
}
return ` AND Visible.UIDUser=U_UUID2BIN('${authReq.session.user}') `;
} catch (e) {
errorLoggerRead(e);
return '';
}
};
/**
* Build extra SELECT column fragment from `Data` / `ExtraData` / `dataFilter` /
* `groupBanner` query parameters.
*
* @param {ExpressRequestAuthorized} req
* @param {string} [groupBannerTable='pmember'] - Table alias that holds banner data
* @returns {string}
*/
const buildDataFields = (req, groupBannerTable = 'pmember') => {
let dataFields = '';
const type = getType(req);
if (req.query.Data) {
if (req.query.Data === 'all') {
dataFields += `,Member.Data AS Data`;
} else {
/** @type {DataFieldSpec[]} */
let fields = [];
try {
fields = JSON.parse(String(req.query.Data));
} catch (e) {
fields = Array.isArray(req.query.Data)
? req.query.Data.map(v => ({ path: String(v), alias: String(v), query: false }))
: [{ path: String(req.query.Data), alias: String(req.query.Data), query: false }];
}
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 += `,ObjectBase.Data AS ExtraData`;
}
if (req.query.dataFilter) {
dataFields += `,Member.Data,Main.Data AS MainBaseData`;
if ((type.includes('job') || type.includes('entry')) && !req.query.ExtraData) {
dataFields += `,ObjectBase.Data AS ExtraData`;
}
}
if (req.query.groupBanner) {
dataFields += `,JSON_VALUE(${groupBannerTable}.Data,'$.banner') AS groupBanner`;
}
return dataFields;
};
// ---------------------------------------------------------------------------
// Core listing queries
// ---------------------------------------------------------------------------
/**
* Retrieve members/objects belonging to a group or object identified by `UID`.
* Supports siblings query, temporal snapshots (`timestamp`, `since`), data field
* projections, optional grouping and filtering.
*
* @param {ExpressRequestAuthorized} req - Express request (needs `req.params.UID`)
* @returns {Promise<PersonListItem[]>}
*/
export const getListing = async (req) => {
try {
const UID = UUID2hex(req.params.UID);
const authReq = /** @type {ExpressRequestAuthorized} */ (/** @type {unknown} */ (req));
let result;
let filter = '';
const type = getType(req);
const mainType = " IN('extern','person') ";
if (type.length === 0) return [];
if (req.query.hierarchie) filter += ` AND Main.hierarchie=${req.query.hierarchie}`;
if (req.query.stage) filter += ` AND Main.stage=${req.query.stage}`;
if (req.query.gender) filter += ` AND Main.gender='${req.query.gender}'`;
const dataFields = buildDataFields(req, 'pmember');
const timestamp = parseTimestampToSeconds(req.query.timestamp ? String(req.query.timestamp) : undefined);
let asOf = timestamp ? `FOR SYSTEM_TIME AS OF FROM_UNIXTIME(${timestamp})` : '';
let sinceWhere = '';
if (req.query.siblings) {
errorHandler({ obsolete: '/kpe20/persons/:UID with parameter siblings was called, which is now obsolete' });
result = await query(`
SELECT
Main.UID, Main.Type, Main.UIDBelongsTo, Main.Title , Member.Display , Member.SortName , pgroup.UID AS UIDgroup ,
CONCAT(pgroup.Title ,' ', pmember.Display) AS pGroup, Main.dindex
${dataFields}
FROM
ObjectBase ${asOf} AS Main
INNER JOIN Member ON (Member.UID=Main.UIDBelongsTo)
LEFT JOIN (ObjectBase ${asOf} AS pgroup
INNER JOIN Member AS pmember ON (pmember.UID=pgroup.UID)
INNER JOIN Links ${asOf} AS GLink ON (GLink.UIDTarget = pgroup.UID)
) ON (GLink.UID =Main.UID AND GLink.Type IN ('memberA','memberSys'))
INNER JOIN ObjectBase ${asOf} AS Sisters ON (Sisters.UIDBelongsTo=Main.UIDBelongsTo)
INNER JOIN Visible ON (Visible.UID=Main.UID)
WHERE
Sisters.UID=? ${filter} AND Main.Type IN (?) AND Visible.UIDUser=?
GROUP BY Main.UID
ORDER BY Main.SortName,Member.SortName
`, [UID, type, UUID2hex(authReq.session.user)],
{
cast: ['UUID', 'json'],
log: false,
filter: req.query.dataFilter
? (row) => matchObject(row, JSON.parse(String(req.query.dataFilter)), timestamp)
: null,
}
);
} else {
const vSql = await getListVisibleSql(UID, req);
if (req.query.since) {
const since = parseTimestampToSeconds(req.query.since ? String(req.query.since) : undefined);
sinceWhere = ` AND Links.validFrom > FROM_UNIXTIME(${since}) AND Links.validUntil > NOW()`;
asOf = `FOR SYSTEM_TIME BETWEEN FROM_UNIXTIME(${Math.max(since, 1672527600)}) AND NOW()`;
}
const hasEntry = type.includes('entry');
const hasOtherTypes = type.some(t => t !== 'entry');
let visibleCondition;
if (!hasEntry) {
visibleCondition = `ObjectBase.UID=Visible.UID`;
} else if (!hasOtherTypes) {
visibleCondition = `1=1`;
} else {
visibleCondition = `(ObjectBase.UID=Visible.UID OR ObjectBase.Type='entry')`;
}
result = await query(`
SELECT
ObjectBase.UID, ObjectBase.Type, ObjectBase.UIDBelongsTo, ObjectBase.Title ,
Member.Display , Member.SortName , pgroup.UID AS UIDgroup ,
CONCAT(pgroup.Title ,' ', pmember.Display) AS pGroup, Main.hierarchie, Main.stage, Main.gender, Main.dindex,
Visible.Type AS visibility
${dataFields}
FROM
ObjectBase ${asOf} AS ObjectBase
INNER JOIN ObjectBase ${asOf} AS Main ON (Main.UID=ObjectBase.UIDBelongsTo AND Main.Type ${mainType})
INNER JOIN Member ON (Member.UID=ObjectBase.UIDBelongsTo)
INNER JOIN ObjectBase AS VisibleObject ON (VisibleObject.UIDBelongsTo=Member.UID)
LEFT JOIN (ObjectBase ${asOf} AS pgroup
INNER JOIN Member AS pmember ON (pmember.UID=pgroup.UID)
INNER JOIN Links ${asOf} AS GLink ON (GLink.UIDTarget = pgroup.UID)
) ON (GLink.UID =ObjectBase.UID AND GLink.Type ='memberA')
INNER JOIN Links ${asOf} ON (Links.UID=ObjectBase.UID AND Links.Type IN('member','memberA'))
INNER JOIN Visible ON (Visible.UID=VisibleObject.UID AND ${visibleCondition} ${vSql})
WHERE
Links.UIDTarget = ? ${filter} AND ObjectBase.Type IN (?) ${sinceWhere}
GROUP BY ObjectBase.UID
ORDER BY ObjectBase.SortName,Member.SortName,ObjectBase.validFrom
`, [UID, type],
{
cast: ['UUID', 'json'],
log: false, //(sql)) => dbLogger(sql, '/persons/:UID'),
filter: req.query.dataFilter
? (row) => {
try {
return matchObject(
{ ...row, Type: row.Type === 'entry' ? 'person' : row.Type },
JSON.parse(String(req.query.dataFilter))
);
} catch (e) {
errorLoggerRead({ e });
}
}
: null,
group: req.query.grouped && req.query.grouped !== 'false' && req.query.grouped !== '0'
? (result, current) => {
if (!req.query.Data || req.query.Data !== 'all') delete current.Data;
if (!req.query.ExtraData) delete current.ExtraData;
delete current.MainBaseData;
const index = result.findIndex(el => el.UIDBelongsTo === current.UIDBelongsTo);
const found = result[index];
if (index >= 0) {
if (current.Type === 'person' || (current.Type === 'job' && found.Type === 'guest')) {
return [...result.filter((el, Index) => Index !== index), current];
}
return result;
}
return [...result, current];
}
: null,
}
);
}
return result;
} catch (e) {
errorLoggerRead(e);
return [];
}
};
/**
* Fetch multiple person objects by an array of UIDs supplied in `req.body`.
* Used when the caller already knows specific UIDs instead of querying by group.
*
* @param {ExpressRequestAuthorized} req - Express request; body must be a UUID string array
* @returns {Promise<{success: boolean, result: PersonListItem[], message?: string}>}
*/
export const getPersonsByUIDs = async (req) => {
try {
const visibleSql = await getListVisibleSql(null, req);
const authReq = /** @type {ExpressRequestAuthorized} */ (/** @type {unknown} */ (req));
let filter = '';
const type = getType(req);
const mainType = " IN('extern','person') ";
if (type.length === 0) return { success: false, result: [], message: 'no valid types' };
if (req.query.hierarchie) filter += ` AND Main.hierarchie=${req.query.hierarchie}`;
if (req.query.stage) filter += ` AND Main.stage=${req.query.stage}`;
if (req.query.gender) filter += ` AND Main.gender='${req.query.gender}'`;
const dataFields = buildDataFields(req, 'gMember');
const timestamp = parseTimestampToSeconds(req.query.timestamp ? String(req.query.timestamp) : undefined);
const since = parseTimestampToSeconds(req.query.since ? String(req.query.since) : undefined);
const asOf = timestamp
? `FOR SYSTEM_TIME AS OF FROM_UNIXTIME(${timestamp})`
: since ? `FOR SYSTEM_TIME BETWEEN FROM_UNIXTIME(${since}) AND NOW()` : '';
const UIDs = Array.isArray(req.body)
? req.body.filter(UID => isValidUID(UID)).map(UID => UUID2hex(UID))
: [];
if (!UIDs.length) {
return { success: false, result: [], message: 'no valid UID array supplied in body' };
}
const result = await query(`
SELECT
ObjectBase.UID, ObjectBase.Type, ObjectBase.UIDBelongsTo, ObjectBase.Title,
Member.Display, Member.SortName, pgroup.UID AS UIDgroup,
CONCAT(pgroup.Title ,' ', gMember.Display) AS pGroup,
CONCAT(pList.Title ,' ', lMember.Display) AS lList,
Main.hierarchie, Main.stage, Main.gender, Main.dindex
${dataFields}
FROM
ObjectBase ${asOf} AS ObjectBase
INNER JOIN ObjectBase ${asOf} AS Main ON (Main.UID=ObjectBase.UIDBelongsTo AND Main.Type ${mainType})
INNER JOIN Member ON (Member.UID=Main.UIDBelongsTo)
LEFT JOIN (ObjectBase ${asOf} AS pList
INNER JOIN Member AS lMember ON (lMember.UID=pList.UID)
INNER JOIN Links ${asOf} AS lLink ON (lLink.UIDTarget = pList.UID)
) ON (lLink.UID =ObjectBase.UID AND lLink.Type ='memberA')
LEFT JOIN (ObjectBase ${asOf} AS pgroup
INNER JOIN Member AS gMember ON (gMember.UID=pgroup.UID)
INNER JOIN Links ${asOf} AS GLink ON (GLink.UIDTarget = pgroup.UID)
) ON (GLink.UID =ObjectBase.UIDBelongsTo AND GLink.Type ='memberA')
INNER JOIN Visible ON (Visible.UID=Main.UID AND Visible.UIDUser=U_UUID2BIN('${authReq.session.user}'))
WHERE
ObjectBase.UID IN(?) ${filter} AND ObjectBase.Type IN (?)
GROUP BY ObjectBase.UID
ORDER BY ObjectBase.SortName,Member.SortName
`, [UIDs, type],
{
cast: ['UUID', 'json'],
log: false,
filter: req.query.dataFilter
? (row) => matchObject(row, JSON.parse(String(req.query.dataFilter)))
: null,
}
);
return { success: true, result };
} catch (e) {
errorLoggerRead(e);
return { success: false, result: [] };
}
};
/**
* Fetch persons belonging to one or more groups supplied in `req.body` as UUID array.
*
* @param {ExpressRequestAuthorized} req - Body must be an array of group UUID strings
* @returns {Promise<{success: boolean, result: PersonListItem[]}>}
*/
export const getPersonsByGroups = async (req) => {
const authReq = /** @type {ExpressRequestAuthorized} */ (/** @type {unknown} */ (req));
let filter = '';
const type = getType(req);
const mainType = " IN('extern','person') ";
if (type.length === 0) return { success: true, result: [] };
if (req.query.hierarchie) filter += ` AND Main.hierarchie=${req.query.hierarchie}`;
if (req.query.stage) filter += ` AND Main.stage=${req.query.stage}`;
if (req.query.gender) filter += ` AND Main.gender='${req.query.gender}'`;
const dataFields = buildDataFields(req, 'pgroup');
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
ObjectBase.UID, ObjectBase.Type, ObjectBase.UIDBelongsTo, ObjectBase.Title,
Member.Display, Member.SortName, pgroup.UID AS pUIDgroup, Links.UIDTarget AS UIDgroup,
CONCAT(pgroup.Title ,' ', gMember.Display) AS pGroup,
Main.hierarchie, Main.stage, Main.gender, Main.dindex
${dataFields}
FROM
ObjectBase ${asOf} AS ObjectBase
INNER JOIN ObjectBase ${asOf} AS Main ON (Main.UID=ObjectBase.UIDBelongsTo AND Main.Type ${mainType})
INNER JOIN Member ON (Member.UID=Main.UIDBelongsTo)
LEFT JOIN (ObjectBase ${asOf} AS pgroup
INNER JOIN Member AS gMember ON (gMember.UID=pgroup.UID)
INNER JOIN Links ${asOf} AS GLink ON (GLink.UIDTarget = pgroup.UID)
) ON (GLink.UID =ObjectBase.UID AND GLink.Type ='memberA')
INNER JOIN Links ${asOf} ON (Links.UID=ObjectBase.UID AND Links.Type IN('member','memberA') AND Links.UIDTarget IN (?))
INNER JOIN Visible ON (Visible.UID=Main.UID AND Visible.UIDUser=U_UUID2BIN('${authReq.session.user}'))
WHERE
ObjectBase.Type IN (?) ${filter}
GROUP BY ObjectBase.UID
ORDER BY ObjectBase.SortName,Member.SortName
`, [
req.body.filter(UID => isValidUID(UID)).map(UID => UUID2hex(UID)),
type,
],
{
cast: ['UUID', 'json'],
log: false,
filter: req.query.dataFilter
? (row) => matchObject(row, JSON.parse(String(req.query.dataFilter)))
: null,
}
);
return { success: true, result };
};
// ---------------------------------------------------------------------------
// Temporal / change queries
// ---------------------------------------------------------------------------
/**
* Return persons that were **added** to a group after `timestamp`.
*
* @param {ExpressRequestAuthorized} req - Needs `params.UID` and `params.timestamp`
* @returns {Promise<{success: boolean, result: PersonListItem[]}>}
*/
export const getAddedPersons = async (req) => {
const UID = UUID2hex(req.params.UID);
const vSql = await getListVisibleSql(UID, req);
const type = getType(req);
const result = await query(`
SELECT
ObjectBase.UID, ObjectBase.Type, ObjectBase.UIDBelongsTo, ObjectBase.Title,
Member.Display, Member.SortName, pgroup.UID AS UIDgroup,
CONCAT(pgroup.Title ,' ', pgroup.Display) AS pGroup, Member.Data AS Data
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 (ObjectBase AS pgroup
INNER JOIN Links AS GLink ON (GLink.UIDTarget = pgroup.UID)
) ON (GLink.UID =ObjectBase.UID AND GLink.Type ='memberA')
INNER JOIN Visible ON (Visible.UID=Main.UID ${vSql})
INNER JOIN Links ON (Links.UID=ObjectBase.UID AND Links.Type IN('member','memberA'))
LEFT JOIN Links FOR SYSTEM_TIME AS OF TIMESTAMP '${mysqlTime(req.params.timestamp)}' AS beforeLink
ON (beforeLink.UID=ObjectBase.UID AND beforeLink.Type IN('member','memberA') AND beforeLink.UIDTarget=?)
WHERE
Links.UIDTarget = ? AND ObjectBase.Type IN (?) AND beforeLink.UID IS NULL
GROUP BY ObjectBase.UID
ORDER BY ObjectBase.SortName,Member.SortName
`, [UID, UID, type], { cast: ['UUID', 'json'], log: false });
return { success: true, result };
};
/**
* Return persons that were **removed** from a group after `timestamp`.
*
* @param {ExpressRequestAuthorized} req - Needs `params.UID` and `params.timestamp`
* @returns {Promise<{success: boolean, result: PersonListItem[]}>}
*/
export const getRemovedPersons = async (req) => {
const UID = UUID2hex(req.params.UID);
const vSql = await getListVisibleSql(UID, req);
const type = getType(req);
const result = await query(`
SELECT
ObjectBase.UID, ObjectBase.Type, ObjectBase.UIDBelongsTo, ObjectBase.Title,
Member.Display, Member.SortName, pgroup.UID AS UIDgroup,
CONCAT(pgroup.Title ,' ', pgroup.Display) AS pGroup, Member.Data AS Data
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 (ObjectBase AS pgroup
INNER JOIN Links AS GLink ON (GLink.UIDTarget = pgroup.UID)
) ON (GLink.UID =ObjectBase.UID AND GLink.Type ='memberA')
INNER JOIN Visible ON (Visible.UID=Main.UID ${vSql})
INNER JOIN Links FOR SYSTEM_TIME AS OF TIMESTAMP '${mysqlTime(req.params.timestamp)}' AS beforeLink
ON (beforeLink.UID=ObjectBase.UID AND beforeLink.Type IN('member','memberA'))
LEFT JOIN Links ON (Links.UID=ObjectBase.UID AND Links.Type IN('member','memberA') AND Links.UIDTarget=?)
WHERE
beforeLink.UIDTarget = ? AND ObjectBase.Type IN (?) AND Links.UID IS NULL
GROUP BY ObjectBase.UID
ORDER BY ObjectBase.SortName,Member.SortName
`, [UID, UID, type], { cast: ['UUID', 'json'], log: false });
return { success: true, result };
};
/**
* Return persons who **entered** a group (became member from extern) after `timestamp`.
*
* @param {ExpressRequestAuthorized} req - Needs `params.UID` and `params.timestamp`
* @returns {Promise<{success: boolean, result: PersonListItem[]}>}
*/
export const getEnteredPersons = async (req) => {
const UID = UUID2hex(req.params.UID);
const vSql = await getListVisibleSql(UID, req);
const result = await query(`
SELECT
ObjectBase.UID, ObjectBase.Type, ObjectBase.UIDBelongsTo, ObjectBase.Title,
Member.Display, Member.SortName, pgroup.UID AS UIDgroup,
CONCAT(pgroup.Title ,' ', pgroup.Display) AS pGroup, Member.Data AS Data
FROM
ObjectBase AS ObjectBase
INNER JOIN Member ON (Member.UID=ObjectBase.UID)
LEFT JOIN (ObjectBase AS pgroup
INNER JOIN Links AS GLink ON (GLink.UIDTarget = pgroup.UID)
) ON (GLink.UID =ObjectBase.UID AND GLink.Type ='memberA')
INNER JOIN Links ON (Links.UID=ObjectBase.UID AND Links.Type IN('member','memberA'))
INNER JOIN Visible ON (Visible.UID=ObjectBase.UID)
LEFT JOIN Links FOR SYSTEM_TIME AS OF TIMESTAMP '${mysqlTime(req.params.timestamp)}' AS beforeLink
ON (beforeLink.UID=ObjectBase.UID AND beforeLink.Type IN('member','memberA') AND beforeLink.UIDTarget=?)
LEFT JOIN ObjectBase FOR SYSTEM_TIME AS OF TIMESTAMP '${mysqlTime(req.params.timestamp)}' AS oldBase
ON (oldBase.UID=ObjectBase.UID)
WHERE
Links.UIDTarget = ? AND ObjectBase.Type ='person'
AND ((beforeLink.UID IS NULL AND ObjectBase.UID IS NULL) OR oldBase.Type='extern')
GROUP BY ObjectBase.UID
ORDER BY ObjectBase.SortName,Member.SortName
`, [UID, UID], { cast: ['UUID', 'json'], log: false });
return { success: true, result };
};
/**
* Return persons who **exited** (changed from person to extern) after `timestamp`.
*
* @param {ExpressRequestAuthorized} req - Needs `params.UID` and `params.timestamp`
* @returns {Promise<{success: boolean, result: PersonListItem[]}>}
*/
export const getExitedPersons = async (req) => {
const UID = UUID2hex(req.params.UID);
const vSql = await getListVisibleSql(UID, req);
const result = await query(`
SELECT
ObjectBase.UID, ObjectBase.Type, ObjectBase.UIDBelongsTo, ObjectBase.Title,
Member.Display, Member.SortName
FROM
ObjectBase FOR SYSTEM_TIME AS OF TIMESTAMP '${mysqlTime(req.params.timestamp)}'
INNER JOIN Member ON (Member.UID=ObjectBase.UID)
INNER JOIN Visible ON (Visible.UID=ObjectBase.UID ${vSql})
INNER JOIN Links FOR SYSTEM_TIME AS OF TIMESTAMP '${mysqlTime(req.params.timestamp)}' AS beforeLink
ON (beforeLink.UID=ObjectBase.UID AND beforeLink.Type IN('member','memberA'))
LEFT JOIN ObjectBase AS NewBase ON (NewBase.UID=ObjectBase.UID)
WHERE
beforeLink.UIDTarget = ? AND ObjectBase.Type='person' AND NewBase.Type='extern'
GROUP BY ObjectBase.UID
ORDER BY ObjectBase.SortName,Member.SortName
`, [UID], { cast: ['UUID', 'json'], log: false });
return { success: true, result };
};
/**
* Check whether a specific person is a (current) member of a group.
*
* @param {string} personUID - UUID of the person object
* @param {string} groupUID - UUID of the group object
* @returns {Promise<{success: boolean, result: {UID: string, Type: PersonType}[]}>}
*/
export const checkIsMember = async (personUID, groupUID) => {
const result = await query(
`SELECT ObjectBase.UID, ObjectBase.Type
FROM ObjectBase
INNER JOIN ObjectBase AS Main ON (ObjectBase.UIDBelongsTo=Main.UIDBelongsTo)
INNER JOIN Links ON (Links.UID=ObjectBase.UID AND Links.Type IN ('member','memberA'))
WHERE Main.UID=? AND Links.UIDTarget=?`,
[UUID2hex(personUID), UUID2hex(groupUID)],
{ cast: ['UUID'], log: false }
);
return { success: true, result };
};