// @ts-check
/**
* @import {ExpressRequestAuthorized} from './../../types.js'
*/
/**
* List Entries Service - Business Logic for Entry Management
*
* Provides all database operations and business logic for managing entries
* in static and dynamic lists, including adding, updating, deleting, and
* querying list entries.
*/
import { query, pool, UUID2hex, HEX2uuid, UUID2base64, transaction } from '@commtool/sql-query';
import { isValidUID } from '../../utils/UUIDs.js';
import { addUpdateList, addUpdateEntry } from '../../server.ws.js';
import mysqlTime from '../../utils/mysqlTime.js';
import { publishEvent } from '../../utils/events.js';
import { isObjectAdmin, isAdmin } from '../../utils/authChecks.js';
import { getTokenConnection } from '../../utils/transaction.js';
import { errorLoggerRead, errorLoggerUpdate } from '../../utils/requestLogger.js';
import { queueAddArray } from '../../tree/treeQueue/treeQueue.js';
import { keysCompare } from '../../utils/keyCompare.js';
import { addedDiff } from 'deep-object-diff';
/**
* Replace special characters in a string with underscores
* Used to create valid property names from parameter names
* @param {string} str
* @returns {string}
*/
const nameReplace = (str) => {
return str.replace(/[&\/\\#, +()$~%.'":*?<>{}]/g, '_');
};
/**
* Add one or more persons as entries to a static list
*
* @param {ExpressRequestAuthorized} req
* @returns {Promise<{success: boolean, added?: any[], type?: string, message?: string}>}
*/
export const putEntry = async (req) => {
try {
const entryType = req.query.guest ? 'guest' : 'entry';
const UIDlist = UUID2hex(req.params.UIDlist);
const result = await query(`SELECT
Member.Data,Member.UID,ObjectBase.Type
FROM ObjectBase
INNER JOIN Member ON (Member.UID=ObjectBase.UIDBelongsTo)
WHERE ObjectBase.UID=? AND Type IN ('list','gentry') `, [UIDlist]);
if (result.length === 0) {
return { success: false, message: `invalid list UID supplied` };
}
const list = { ...result[0], Data: JSON.parse(result[0].Data) };
let adds = [];
const extra = list.Data.extraParameter;
if (Array.isArray(req.body) && req.body.length > 0) {
adds = req.body.filter(el => el).map(el =>
(
el.UID ? { ...el, UID: UUID2hex(el.UID), Data: { ...el, UID: undefined } } : { UID: UUID2hex(el) }
)).filter(el => el.UID); // drop any entry where UUID2hex returned undefined
}
else if (req.body.UID) {
const uid = UUID2hex(req.body.UID);
if (uid) adds[0] = { UID: uid, Data: { ...req.body, UID: undefined } };
}
else {
const uid = UUID2hex(req.body);
if (uid) adds[0] = { UID: uid };
}
// Check if there are any items to add
if (!adds || adds.length === 0) {
console.warn('No valid items to add to list');
return { success: false, message: 'No valid items provided to add to list' };
}
// get the data for the persons
const toBeAdded = await query(`SELECT Main.UID AS UID,Main.Type AS MainType,ObjectBase.UID AS UIDBase,Main.Title,
Main.UIDBelongsTo, Member.Data,Member.Display,Main.SortName AS SortBase, Member.SortName,
IF(Entry.UID IS NULL,UIDV1(),Entry.UID) AS UIDentry, Entry.UID AS UIDalready, pgroup.UID AS UIDgroup ,
CONCAT(pgroup.Title ,' ', pmember.Display) AS pGroup,Main.hierarchie, Main.stage, Main.gender, Main.dindex
FROM ObjectBase AS Main
INNER JOIN Member ON (Main.UIDBelongsTo= Member.UID)
INNER JOIN ObjectBase ON (ObjectBase.UIDBelongsTo= Main.UID)
LEFT JOIN (
ObjectBase AS Entry
INNER JOIN Links ON (Entry.UID=Links.UID AND Links.Type IN ('member','memberA','member0')
AND Links.UIDTarget=?)
)
ON (Entry.UIDBelongsTo= Main.UID AND Entry.Type IN ('entry','guest'))
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 =ObjectBase.UIDBelongsTo AND GLink.Type ='memberA')
WHERE ObjectBase.UID IN(?) AND Main.Type IN ('person','extern') `,
[UIDlist, adds.map(el => el.UID)],
{ cast: ['json'] }
);
let added = [];
if (toBeAdded.length) {
const result = await query(`
INSERT IGNORE INTO ObjectBase(UID,Type,UIDBelongsTo,Title,Data)
VALUES (?,?,?,?,?)
`,
toBeAdded.map((a, Index) => {
const supplied = adds.find(el => (a.UIDBelongsTo.equals(el.UID) || a.UIDBase.equals(el.UID)));
const Data = supplied && supplied.Data ? supplied.Data : {};
return [a.UIDentry, entryType, a.UIDBelongsTo, a.Title, JSON.stringify(Data)];
}),
{ batch: true });
console.log(result);
const result2 = await query(`INSERT IGNORE INTO Links(UID, Type, UIDTarget) VALUES (?,'memberA',?) `,
toBeAdded.map(a => ([a.UIDentry, UIDlist])), { batch: true });
console.log(result2);
const personUIDs = toBeAdded.map(p => p.UIDBelongsTo);
if (!req.query.noUpdate) {
addUpdateList(UIDlist);
addUpdateList(personUIDs);
}
// publish the events
added = toBeAdded.filter(a => !a.UIDalready);
if (added.length) {
publishEvent(`/add/list/${entryType}/${HEX2uuid(UIDlist)}`, {
organization: req.session.root,
data: added.map(a => (HEX2uuid(a.UIDentry)))
});
if (entryType === 'entry') {
publishEvent(`/add/list/person/${HEX2uuid(UIDlist)}`, {
organization: req.session.root,
data: toBeAdded.filter(a => !a.UIDalready).map(a => HEX2uuid(a.UIDBelongsTo))
});
}
// add update requests for the potential filters of this list
queueAddArray(req, added.map(a => ({ UID: UUID2hex(a.UIDentry), UIDBelongsTo: UUID2hex(a.UIDBelongsTo), type: 'list', oldTarget: null, newTarget: UIDlist, timestamp: null })));
}
}
return { success: true, added: added.map(a => ({ UID: HEX2uuid(a.UIDentry), UIDBelongsTo: HEX2uuid(a.UIDBelongsTo), type: entryType }))};
}
catch (e) {
errorLoggerUpdate(e);
return { success: false, message: 'internal server error' };
}
};
/**
* Update extra parameters for a person's entry in a list
*
* @param {ExpressRequestAuthorized} req
* @param {'list'|'dlist'} type - List type
* @returns {Promise<{success: boolean, changed?: any, message?: string}>}
*/
export const postEntry = async (req, type) => {
try {
const UIDlist = UUID2hex(req.params.UIDlist);
const all = false;
if (!await isObjectAdmin(req, UIDlist) && !all) {
return { success: false, message: 'user has no change authority for this list/dlist/event' };
}
// get the list data
const result = await query(`SELECT
Member.Data,Member.UID,ObjectBase.Type
FROM ObjectBase
INNER JOIN Member ON (Member.UID=ObjectBase.UID)
WHERE ObjectBase.UID=? AND Type IN ('list','dlist','gentry') `, [UIDlist]);
if (result.length === 0) {
return { success: false, message: `invalid list UID supplied` };
}
const listData = JSON.parse(result[0].Data);
const UIDperson = UUID2hex(req.params.UIDperson);
if (!UIDperson) {
return { success: false, message: `invalid person UID format` };
}
const entries = await query(`SELECT Entry.UID AS UID,Entry.Data AS Data, Entry.UIDBelongsTo
FROM ObjectBase AS Main
INNER JOIN Member ON (Main.UIDBelongsTo= Member.UID)
INNER JOIN ObjectBase ON (ObjectBase.UIDBelongsTo= Main.UID)
INNER JOIN ObjectBase AS Entry ON (Entry.UIDBelongsTo= Main.UID AND Entry.Type IN('entry','guest'))
INNER JOIN Links ON (Entry.UID=Links.UID AND Links.Type IN ('member','memberA','member0') AND Links.UIDTarget=?)
WHERE ObjectBase.UID =? AND Main.Type IN ('person','extern') `, [UIDlist, UIDperson]);
if (entries.length === 0) {
return { success: false, message: `invalid person UID supplied` };
}
const entryData = entries.reduce((pVal, cVal) => ({ ...pVal, ...JSON.parse(cVal.Data) }), {});
const extra = [
...(listData.extraParameter ?? []),
...(listData.extraFields ?? []).filter(f => ['state','controlled'].includes(f.type))
];
if (extra) {
extra.forEach(param => {
const myName = nameReplace(param.name);
if (req.body[myName] === null || req.body[myName] === '')
delete entryData[myName];
else if (param.type === 'string' && req.body[myName] !== undefined)
entryData[myName] = req.body[myName];
else if (param.type === 'state' && req.body[myName] !== undefined)
entryData[myName] = req.body[myName];
else if (param.type === 'controlled' && req.body[myName] !== undefined)
entryData[myName] = req.body[myName];
else if (param.type === 'integer' && req.body[myName] !== undefined)
entryData[myName] = parseInt(req.body[myName]);
else if (param.type === 'bool' && req.body[myName] !== undefined)
entryData[myName] = req.body[myName] ? 1 : 0;
else if (param.type === 'money' && req.body[myName] !== undefined)
entryData[myName] = parseInt(req.body[myName]);
else if (param.type === 'date' && req.body[myName] !== undefined)
entryData[myName] = parseInt(req.body[myName]);
else if (param.type === 'select' && req.body[myName] !== undefined)
entryData[myName] = req.body[myName];
});
}
await query(`
INSERT INTO ObjectBase (Data,UID,Type) VALUES (?,?,'entry') ON DUPLICATE KEY UPDATE Data = VALUE(Data)`,
entries.map(entry => ([JSON.stringify(entryData), entry.UID])),
{ batch: true });
const entry = entries[0];
const diff = keysCompare(entries.length > 0 ? JSON.parse(entry.Data) : {}, entryData);
const myDiffLength = Object.keys(diff).length;
if (myDiffLength > 0) {
publishEvent(`/change/${type}/entry/${HEX2uuid(UIDlist)}`, {
organization: req.session.root,
data: {
UID: HEX2uuid(entry.UID),
diff: diff,
UIDperson: HEX2uuid(entries[0].UIDBelongsTo)
}
});
}
const modData = { ...diff };
Object.entries(modData).forEach(([key, entry]) => { modData[`__extra_${key}`] = entry; });
addUpdateEntry(UIDlist, { changed: { UIDperson: HEX2uuid(entries[0].UIDBelongsTo), Data: modData } });
return { success: true, changed: { UID: HEX2uuid(entries[0].UID), data: entryData } };
}
catch (e) {
errorLoggerUpdate(e);
return { success: false, message: 'internal server error' };
}
};
/**
* Remove one or more persons from a static list
*
* @param {ExpressRequestAuthorized} req
* @returns {Promise<{success: boolean, result?: any, message?: string, errors?: any[]}>}
*/
export const deleteEntry = async (req) => {
try {
const UIDlist = UUID2hex(req.params.UIDlist);
const result = await query(`SELECT
Member.Data,Member.UID,ObjectBase.Type
FROM ObjectBase
INNER JOIN Member ON (Member.UID=ObjectBase.UID)
WHERE ObjectBase.UID=? AND ObjectBase.Type ='list'`, [UIDlist]);
if (result.length === 0) {
return { success: false, message: `invalid list UID supplied` };
}
let deletes;
let bodyErrors = [];
const deleting = req.body;
if (Array.isArray(deleting)) {
deletes = deleting.map(el => {
try {
if (typeof (el) === 'string')
return UUID2hex(el);
else if (el.UID)
return UUID2hex(el.UID);
else
return null;
}
catch (e) { bodyErrors.push(e); }
});
}
else {
try {
if (typeof (deleting) === 'string')
deletes = [UUID2hex(deleting)];
else if (deleting.UID)
deletes = [UUID2hex(deleting.UID)];
}
catch (e) { bodyErrors.push(e); }
}
if (bodyErrors.length > 0) {
return { success: false, message: 'invalid body supplied', errors: bodyErrors };
}
const resPersons = await query(`SELECT Entry.UIDBelongsTo AS UID, Entry.UID AS UIDentry, Entry.Type AS EntryType
FROM ObjectBase AS Entry INNER JOIN Links ON (Entry.UID=Links.UID AND Links.Type IN ('member','memberA','member0') )
WHERE Links.UIDTarget=? AND (Entry.UIDBelongsTo IN (?) OR Entry.UID IN (?)) AND Entry.Type IN ('entry','guest')`,
[UIDlist, deletes, deletes], { log: false, cast: ['UUID'] });
if (resPersons && resPersons.length > 0) {
const personUIDs = resPersons.map(p => p.UID);
await query(`DELETE Entry,Links
FROM ObjectBase AS Entry INNER JOIN Links ON (Entry.UID=Links.UID AND Links.Type IN ('member','memberA','member0') )
WHERE Links.UIDTarget=? AND (Entry.UIDBelongsTo IN (?) OR Entry.UID IN (?)) AND Entry.Type IN ('entry','guest') `, [UIDlist, deletes, deletes]);
addUpdateList(UIDlist);
await addUpdateList(personUIDs);
queueAddArray(req, resPersons.map(a => ({ UID: UUID2hex(a.UIDentry), UIDBelongsTo: UUID2hex(a.UID), type: 'list', oldTarget: UIDlist, newTarget: null, timestamp: null })));
publishEvent(`/remove/list/entry/${HEX2uuid(UIDlist)}`, {
organization: req.session.root,
data: resPersons.map(entry => entry.UIDentry)
});
publishEvent(`/remove/list/person/${HEX2uuid(UIDlist)}`, {
organization: req.session.root,
data: resPersons.filter(entry => entry.EntryType === 'entry').map(entry => entry.UID)
});
}
return { success: true, result: { deleted: resPersons.map(entry => ({ ...entry, UID: HEX2uuid(entry.UIDentry), UIDBelongsTo: HEX2uuid(entry.UID) })) } };
}
catch (e) {
errorLoggerUpdate(e);
return { success: false, message: 'internal server error' };
}
};
/**
* Get all lists containing a specific person
*
* @param {Buffer} UIDperson - Binary person UID
* @param {string[]} type - List types to filter by
* @param {Buffer} userUID - Binary user UID
* @returns {Promise<{success: boolean, result?: any[]}>}
*/
export const getPersonLists = async (UIDperson, type, userUID) => {
try {
const result = await query(`SELECT ObjectBase.UID, ObjectBase.Title, Member.Display, ObjectBase.Type,
Visible.Type AS Visibility
FROM ObjectBase
INNER JOIN Member ON (Member.UID=ObjectBase.UID)
INNER JOIN Links ON (Links.UIDTarget=ObjectBase.UID AND Links.Type IN ('member','memberA'))
INNER JOIN ObjectBase AS Entry ON (Entry.UID=Links.UID)
INNER JOIN Visible ON (Visible.UID=ObjectBase.UID)
WHERE ObjectBase.Type IN (?) AND Entry.UIDBelongsTo=? AND Visible.UIDUser=?
GROUP BY ObjectBase.UID`, [type, UIDperson, userUID], { log: false, cast: ['UUID', 'json'] });
return { success: true, result };
}
catch (e) {
errorLoggerRead(e);
return { success: false, message: 'internal server error' };
}
};
/**
* Revert all entries in a list to their state at a given timestamp
*
* @param {string} UIDstr - UUID string of the list
* @param {number} timestamp - Unix timestamp in milliseconds
* @returns {Promise<{success: boolean, message?: string}>}
*/
export const revertList = async (UIDstr, timestamp) => {
try {
if (timestamp > Date.now()) {
return { success: false, message: 'timestamp can not be in the future' };
}
const asOf = `FOR SYSTEM_TIME AS OF TIMESTAMP FROM_UNIXTIME(${Math.max(timestamp / 1000, 1672527600)})`;
const listUID = UUID2hex(UIDstr);
const resList = await query(`SELECT ObjectBase.UID,Member.Data,Member.Display,ObjectBase.dindex
FROM ObjectBase
INNER JOIN Member ON (Member.UID=ObjectBase.UID)
WHERE ObjectBase.UID=? AND ObjectBase.Type='list'
`, [listUID], { cast: ['UUID', 'json'] });
if (!resList || resList.length === 0) {
return { success: false, message: 'list not found' };
}
// delete all entries for the given list
await query(`DELETE entry,link FROM ObjectBase AS entry
INNER JOIN Links AS link ON(link.UID=entry.UID AND link.Type='memberA')
WHERE link.UIDTarget=? `, [listUID], { backDate: Date.now() / 1000 - 10 });
await transaction(async (connection) => {
await query(`INSERT INTO ObjectBase (Data,UID,UIDBelongsTo,Type)
(SELECT entry.Data,entry.UID,entry.UIDBelongsTo,'entry' FROM ObjectBase ${asOf} AS entry
INNER JOIN Links ${asOf}
ON (Links.UID=entry.UID AND Links.Type='memberA')
WHERE Links.UIDTarget=?)
ON DUPLICATE KEY UPDATE ObjectBase.Type='entry'
`, [listUID], { log: false, connection });
await query(`INSERT IGNORE INTO Links (UID,Type,UIDTarget)
(SELECT UID,'memberA', UIDTarget FROM Links ${asOf}
WHERE Links.UIDTarget=? AND Links.Type='memberA')
`, [listUID], { log: false, connection });
}, { backDate: Date.now() / 1000 });
addUpdateList(listUID);
return { success: true };
}
catch (e) {
errorLoggerUpdate(e);
return { success: false, message: 'internal server error' };
}
};
/**
* Get entry data for a specific person in a list
*
* @param {Buffer} UIDlist - Binary list UID
* @param {Buffer} UIDperson - Binary person UID
* @returns {Promise<{success: boolean, result?: any[], message?: string}>}
*/
export const getListEntry = async (UIDlist, UIDperson) => {
try {
const result = await query(`
SELECT Main.Title,Member.Display,ObjectBase.Data
FROM ObjectBase
INNER JOIN ObjectBase AS Main ON (Main.UID=ObjectBase.UIDBelongsTo)
INNER JOIN Member ON (Member.UID=ObjectBase.UIDBelongsTo)
INNER JOIN Links ON (Links.UID=ObjectBase.UID AND Links.Type IN ('member','memberA'))
INNER JOIN ObjectBase AS list ON (list.UID=Links.UIDTarget AND list.Type IN ('list','dlist'))
WHERE Main.Type IN ('person','extern') AND ObjectBase.UID=? AND Links.UIDTarget=?
`, [UIDperson, UIDlist], { cast: ['json'] });
if (result.length > 0) {
return { success: true, result };
}
else {
return { success: false, message: 'person is not member of this list' };
}
}
catch (e) {
errorLoggerRead(e);
}
};
/**
* Get all persons in a list with optional field selection, filtering, and temporal queries
*
* @param {ExpressRequestAuthorized} req
* @param {boolean} [allowAll=false]
* @returns {Promise<any[]>}
*/
export const getListing = async (req, allowAll = false) => {
try {
const connection = await getTokenConnection(req);
const UID = UUID2hex(req.params.UID);
const UIDuser = UUID2hex(req.session.user);
let visibleFilter;
if (await isAdmin(req.session) || allowAll)
visibleFilter = '';
else {
const visibility = await query(`SELECT Type FROM Visible WHERE UID=? AND UIDUser=?`, [UID, UIDuser], { connection });
if (visibility.length === 0)
return ([]);
if (visibility[0].Type === 'changeable' || visibility[0].Type === 'admin')
visibleFilter = `INNER JOIN Visible ON (Visible.UID = MainBase.UID AND Visible.UIDUser=U_UUID2BIN('${HEX2uuid(UIDuser)}'))`;
else
visibleFilter = `INNER JOIN Visible ON (Visible.UID = MainBase.UID AND Visible.UIDUser=U_UUID2BIN('${HEX2uuid(UIDuser)}') 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];
}
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 && req.query.ExtraData !== 'null') {
if (req.query.ExtraData === 'all') {
dataFields = `,ObjectBase.Data AS ExtraData`;
}
else {
let fields = [];
try {
fields = req.query.ExtraData ? JSON.parse(String(req.query.ExtraData)) : null;
}
catch (e) {
fields[0] = [req.query.ExtraData];
}
for (const field of fields) {
if (!field.query)
dataFields += `,JSON_VALUE(ObjectBase.Data,${pool.escape(field.path)}) AS ${pool.escape(field.alias)}`;
else
dataFields += `,JSON_QUERY(ObjectBase.Data,${pool.escape(field.path)}) AS ${pool.escape(field.alias)}`;
}
}
}
const entryType = req.query.guest ? " IN ('entry','guest') " : " ='entry' ";
const asOf = req.query.timestamp ? `FOR SYSTEM_TIME AS OF FROM_UNIXTIME(${req.query.timestamp / 1000})` :
req.query.since ? `FOR SYSTEM_TIME BETWEEN '${mysqlTime(req.query.since)}' AND NOW();` : '';
const result = await query(`
SELECT
ObjectBase.UID, ObjectBase.Type AS entryType, MainBase.Type, ObjectBase.UIDBelongsTo, MainBase.Title , Member.Display , MainBase.SortName AS SortBase ,
Member.SortName, pgroup.UID AS UIDgroup , list.UID AS UIDlist,
CONCAT(pgroup.Title ,' ', pmember.Display) AS pGroup, CONCAT(list.Title ,' ', lmember.Display) AS list,
Main.hierarchie, Main.stage, Main.gender, Main.dindex, UNIX_TIMESTAMP(ObjectBase.validFrom) AS validFrom,
Links.Type AS linkType
${dataFields}
FROM
ObjectBase ${asOf}
INNER JOIN ObjectBase ${asOf} AS MainBase ON (MainBase.UIDBelongsTo=ObjectBase.UIDBelongsTo)
INNER JOIN ObjectBase ${asOf} AS Main ON (Main.UID=ObjectBase.UIDBelongsTo)
INNER JOIN Member ON (Member.UID=Main.UID)
LEFT JOIN (ObjectBase ${asOf} AS pgroup
INNER JOIN Links ${asOf} AS GLink ON (GLink.UIDTarget = pgroup.UID )
INNER JOIN Member AS pmember ON (pmember.UID=pgroup.UID)
)
ON (GLink.UID =ObjectBase.UIDBelongsTo AND GLink.Type ='memberA')
LEFT JOIN (ObjectBase ${asOf} AS list
INNER JOIN Links ${asOf} AS lLink ON (lLink.UIDTarget = list.UID )
INNER JOIN Member AS lmember ON (lmember.UID=list.UID)
)
ON (lLink.UID =ObjectBase.UID AND lLink.Type ='memberA')
INNER JOIN Links ${asOf} ON (Links.UID=ObjectBase.UID AND Links.Type IN ('member','memberA','member0'))
${visibleFilter}
WHERE Links.UIDTarget=? AND ObjectBase.Type ${entryType}
ORDER BY
Links.Type,Member.SortName
`,
[UID],
{
cast: ['UUID', 'json'],
log: false,
connection,
group: (reducedResult, cur) => {
if (cur.linkType === 'member0') {
return reducedResult.filter(el => el.UIDBelongsTo !== cur.UIDBelongsTo);
}
const exist = reducedResult.find(el => el.UIDBelongsTo === cur.UIDBelongsTo);
if (exist) {
if (req.query.ExtraData === 'all')
return reducedResult.map(el => el.UIDBelongsTo === exist.UIDBelongsTo ? { ...exist, ExtraData: { ...exist.ExtraData, ...el.ExtraData } } : el);
else
return reducedResult.map(el => el.UIDBelongsTo === exist.UIDBelongsTo ? { ...exist, ...el } : el);
}
else {
return [...reducedResult, cur];
}
}
});
return result;
}
catch (e) {
errorLoggerRead(e);
}
};
/**
* Get specific persons (by UID) from a list with optional field selection and temporal queries
*
* Behaves like {@link getListing} but additionally filters the result to only those
* entries whose person UID (`UIDBelongsTo`) or entry UID (`ObjectBase.UID`) appears
* in the array of UUIDs supplied in `req.body`.
*
* @param {ExpressRequestAuthorized} req - `req.params.UID` = list UID; `req.body` = UUID string[]
* @param {boolean} [allowAll=false]
* @returns {Promise<any[]>}
*/
export const getListingByUIDs = async (req, allowAll = false) => {
try {
const connection = await getTokenConnection(req);
const UID = UUID2hex(req.params.UID);
const UIDuser = UUID2hex(req.session.user);
const UIDs = Array.isArray(req.body)
? req.body.filter(uid => isValidUID(uid)).map(uid => UUID2hex(uid))
: [];
if (!UIDs.length) {
return [];
}
let visibleFilter;
if (await isAdmin(req.session) || allowAll)
visibleFilter = '';
else {
const visibility = await query(`SELECT Type FROM Visible WHERE UID=? AND UIDUser=?`, [UID, UIDuser], { connection });
if (visibility.length === 0)
return ([]);
if (visibility[0].Type === 'changeable' || visibility[0].Type === 'admin')
visibleFilter = `INNER JOIN Visible ON (Visible.UID = MainBase.UID AND Visible.UIDUser=U_UUID2BIN('${HEX2uuid(UIDuser)}'))`;
else
visibleFilter = `INNER JOIN Visible ON (Visible.UID = MainBase.UID AND Visible.UIDUser=U_UUID2BIN('${HEX2uuid(UIDuser)}') 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];
}
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 && req.query.ExtraData !== 'null') {
if (req.query.ExtraData === 'all') {
dataFields += `,ObjectBase.Data AS ExtraData`;
}
else {
let fields = [];
try {
fields = req.query.ExtraData ? JSON.parse(String(req.query.ExtraData)) : null;
}
catch (e) {
fields[0] = [req.query.ExtraData];
}
for (const field of fields) {
if (!field.query)
dataFields += `,JSON_VALUE(ObjectBase.Data,${pool.escape(field.path)}) AS ${pool.escape(field.alias)}`;
else
dataFields += `,JSON_QUERY(ObjectBase.Data,${pool.escape(field.path)}) AS ${pool.escape(field.alias)}`;
}
}
}
const entryType = req.query.guest ? " IN ('entry','guest') " : " ='entry' ";
const asOf = req.query.timestamp ? `FOR SYSTEM_TIME AS OF FROM_UNIXTIME(${req.query.timestamp / 1000})` :
req.query.since ? `FOR SYSTEM_TIME BETWEEN '${mysqlTime(req.query.since)}' AND NOW();` : '';
const result = await query(`
SELECT
ObjectBase.UID, ObjectBase.Type AS entryType, MainBase.Type, ObjectBase.UIDBelongsTo, MainBase.Title , Member.Display , MainBase.SortName AS SortBase ,
Member.SortName, pgroup.UID AS UIDgroup , list.UID AS UIDlist,
CONCAT(pgroup.Title ,' ', pmember.Display) AS pGroup, CONCAT(list.Title ,' ', lmember.Display) AS list,
Main.hierarchie, Main.stage, Main.gender, Main.dindex, UNIX_TIMESTAMP(ObjectBase.validFrom) AS validFrom,
Links.Type AS linkType
${dataFields}
FROM
ObjectBase ${asOf}
INNER JOIN ObjectBase ${asOf} AS MainBase ON (MainBase.UIDBelongsTo=ObjectBase.UIDBelongsTo)
INNER JOIN ObjectBase ${asOf} AS Main ON (Main.UID=ObjectBase.UIDBelongsTo)
INNER JOIN Member ON (Member.UID=Main.UID)
LEFT JOIN (ObjectBase ${asOf} AS pgroup
INNER JOIN Links ${asOf} AS GLink ON (GLink.UIDTarget = pgroup.UID )
INNER JOIN Member AS pmember ON (pmember.UID=pgroup.UID)
)
ON (GLink.UID =ObjectBase.UIDBelongsTo AND GLink.Type ='memberA')
LEFT JOIN (ObjectBase ${asOf} AS list
INNER JOIN Links ${asOf} AS lLink ON (lLink.UIDTarget = list.UID )
INNER JOIN Member AS lmember ON (lmember.UID=list.UID)
)
ON (lLink.UID =ObjectBase.UID AND lLink.Type ='memberA')
INNER JOIN Links ${asOf} ON (Links.UID=ObjectBase.UID AND Links.Type IN ('member','memberA','member0'))
${visibleFilter}
WHERE Links.UIDTarget=? AND ObjectBase.Type ${entryType}
AND (ObjectBase.UIDBelongsTo IN (?) OR ObjectBase.UID IN (?))
ORDER BY
Links.Type,Member.SortName
`,
[UID, UIDs, UIDs],
{
cast: ['UUID', 'json'],
log: false,
connection,
group: (reducedResult, cur) => {
if (cur.linkType === 'member0') {
return reducedResult.filter(el => el.UIDBelongsTo !== cur.UIDBelongsTo);
}
const exist = reducedResult.find(el => el.UIDBelongsTo === cur.UIDBelongsTo);
if (exist) {
if (req.query.ExtraData === 'all')
return reducedResult.map(el => el.UIDBelongsTo === exist.UIDBelongsTo ? { ...exist, ExtraData: { ...exist.ExtraData, ...el.ExtraData } } : el);
else
return reducedResult.map(el => el.UIDBelongsTo === exist.UIDBelongsTo ? { ...exist, ...el } : el);
}
else {
return [...reducedResult, cur];
}
}
});
return result;
}
catch (e) {
errorLoggerRead(e);
}
};