import { query, UUID2hex, HEX2uuid, pool } from '@commtool/sql-query';
import { renderObject } from '../../utils/renderTemplates.js';
import { Templates, configs } from '../../utils/compileTemplates.js';
import { getUID } from '../../utils/UUIDs.js';
import { queueAdd } from '../../tree/treeQueue/treeQueue.js';
import { isAdmin } from '../../utils/authChecks.js';
/**
* Add visibility filters for a location
* @param {Object} session - Request session
* @param {Buffer} locationUID - Location UID
* @param {Object} group - Group object with UID and Data
* @param {boolean} isPublic - Whether location is public
* @param {boolean} rebuild - Whether to rebuild filters
* @returns {Promise<void>}
*/
export const addVisibility = async (session, locationUID, group, isPublic, rebuild = true) => {
// is there a private filter?
let privFilter = await query(`SELECT Links.UID,Links.UIDTarget FROM Links WHERE Type='list' AND UID=? AND UIDTarget=?`, [locationUID, group.UID]);
let pubFilter = await query(`SELECT UID,UIDTarget FROM Links WHERE Type='list' AND UID=? AND UIDTarget=?`, [locationUID, UUID2hex(session.root)]);
if (rebuild) {
// delete all private filter for this location
for (const filter of privFilter) {
queueAdd(UUID2hex(session.root), UUID2hex(session.user), 'visible', filter.UID, UUID2hex(group.UID), locationUID, null);
}
// delete all public filter
for (const filter of pubFilter) {
queueAdd(UUID2hex(session.root), UUID2hex(session.user), 'visible', filter.UID, UUID2hex(session.root), locationUID, null);
}
privFilter = [];
pubFilter = [];
}
if (privFilter.length === 0) {
// create a visibility filter for all holding a job in the related group
const filter = {
"job": { groupHierarchieEQ: group.Data.hierarchie }, // (optional)filter rule for objects of type job
"admin": true
};
const [{ UID: filterUID }] = await query(`SELECT UIDV1() AS UID`,[]);
// we are creating the filter object and link it to the list
await query(`
INSERT INTO ObjectBase(UID,Type,UIDBelongsTo,Title,Display,SortName, FullTextIndex, dindex,Data)
VALUES (?,?,?,?,?,?,?,?,?) ON DUPLICATE KEY UPDATE Title=VALUE(Title), Display=VALUE(Display),
SortName=VALUE(SortName), Data=VALUE(Data)
`,
[filterUID, 'changeable', UUID2hex(group.UID), 'location changeability', '', '', '', 0, JSON.stringify(filter)]);
// now perform the linking and add the members to the list
queueAdd(UUID2hex(session.root), UUID2hex(session.user), 'changeable', filterUID, UUID2hex(group.UID), null, locationUID);
}
if (!isPublic) {
// is there a public filter?
// if yes, remove it
if (pubFilter.length > 0)
queueAdd(UUID2hex(session.root), UUID2hex(session.user), 'visible', pubFilter[0].UID, UUID2hex(session.root), locationUID, null);
return; // do not add public filter
}
if (pubFilter.length === 0) {
// if public, all with a job from the root organisation can view the location
const filter = {
"job": { all: null }, // (optional)filter rule for objects of type job
};
const [{ UID: filterUIDA }] = await query(`SELECT UIDV1() AS UID`,[]);
// we are creating the filter object and link it to the list
await query(`
INSERT INTO ObjectBase(UID,Type,UIDBelongsTo,Title,Display,SortName, FullTextIndex, dindex,Data)
VALUES (?,?,?,?,?,?,?,?,?) ON DUPLICATE KEY UPDATE Title=VALUE(Title), Display=VALUE(Display),
SortName=VALUE(SortName), Data=VALUE(Data)
`,
[filterUIDA, 'visible', UUID2hex(session.root), 'location visibility', '', '', '', 0, JSON.stringify(filter)]);
// now perform the linking and add the members to the list
queueAdd(UUID2hex(session.root), UUID2hex(session.user), 'visible', filterUIDA, UUID2hex(session.root), null, locationUID);
}
};
/**
* Create or update a location
* @param {Object} session - Request session
* @param {string} groupUIDString - Group UID as string
* @param {Object} body - Request body with location data
* @param {Object} queryParams - Query parameters (owner, copy)
* @returns {Promise<Object>} Result with success and location object
*/
export const createOrUpdateLocation = async (session, groupUIDString, body, queryParams) => {
const ownerUID = queryParams.owner ? UUID2hex(queryParams.owner) : UUID2hex(session.user);
const [owner] = await query(`SELECT Data FROM ObjectBase WHERE UID=? AND Type IN('person','extern')`, [ownerUID]);
if (!owner) {
return { success: false, message: 'invalid owner UID' };
}
const locationUID = await getUID(queryParams);
let belongsTo = locationUID;
// maybe original supplied, check if it is still a copy
const delta = configs.locations[session.root].mapGranularity;
const copies = await query(`SELECT Member.UID FROM Member
INNER JOIN ObjectBase ON (ObjectBase.UID=Member.UID)
WHERE Member.UID<>? AND ST_Distance_Sphere(Member.Geo, ST_GeomFromText(?))< ?
AND ObjectBase.UIDBelongsTo=Member.UID AND ObjectBase.dindex=1 AND ObjectBase.Type='location'
AND JSON_VALUE(Member.Data,'$.type')=?`,
[locationUID, `POINT(${body.geo.lat} ${body.geo.lng})`, delta, body.type]);
if (copies && copies.length > 0) {
belongsTo = copies[0].UID;
} else if (queryParams.copy) {
belongsTo = UUID2hex(queryParams.copy);
}
let groupUID = UUID2hex(groupUIDString);
if (!groupUID)
groupUID = UUID2hex(session.root);
const [group] = await query(`SELECT ObjectBase.UID,Member.Data FROM ObjectBase INNER JOIN Member ON (Member.UID=ObjectBase.UID)
WHERE ObjectBase.UID=? AND ObjectBase.Type='group'`, [groupUID], { cast: ['json'] });
if (!group) {
return { success: false, message: 'invalid group UID' };
}
const template = Templates[session.root].location;
const object = await renderObject(template, {
UID: locationUID,
group: group.Data,
...body
}, { session }, 'locations');
const dindex = body.public ? 1 : 0;
const locations = await query(`SELECT Data FROM ObjectBase WHERE UID=?`, [locationUID], { cast: ['json'] });
const location = locations[0];
if (!location) {
await query(`
INSERT INTO ObjectBase (UID,Type,UIDBelongsTo,Title,dindex)
VALUES (?,'location',?,?,?)
`,
[locationUID, belongsTo, object.Title, dindex]);
await query(`
INSERT INTO Member (UID,Display,SortName,FullTextIndex,Geo,Data)
VALUES (?,?,?,?,ST_GeomFromText(?),?)
`,
[locationUID, object.Display, object.SortIndex, object.FullTextIndex, `POINT(${body.geo.lat} ${body.geo.lng})`,
JSON.stringify({ ...body, UID: undefined })
]);
// now link the location to The group
await query(`INSERT IGNORE INTO Links (UID,Type,UIDTarget) VALUES(?,'memberA',?)`, [locationUID, groupUID]);
// now link the location to The personal owner
await query(`INSERT IGNORE INTO Links (UID,Type,UIDTarget) VALUES(?,'member',?)`, [locationUID, ownerUID]);
// and link it to the orga, so we have all locations of an organisation aligned
await query(`INSERT IGNORE INTO Links (UID,Type,UIDTarget) VALUES(?,'location',?)`, [locationUID, [UUID2hex(session.root)]]);
} else {
await query(`UPDATE ObjectBase SET
Title=?, UIDBelongsTo=?, dindex=?
WHERE UID=?`,
[object.Title, belongsTo, dindex, locationUID]);
await query(`UPDATE Member SET
Display=?, Geo=ST_GeomFromText(?), SortName=?,FullTextIndex=?, Data=?
WHERE UID=?`,
[object.Display, `POINT(${body.geo.lat} ${body.geo.lng})`, object.SortIndex, object.FullTextIndex,
JSON.stringify({ ...body, UID: undefined }),
locationUID]);
if (queryParams.owner) {
// change personal ownership
await query(`DELETE FROM Links WHERE Type='member' AND UID=?`, [locationUID]);
// now link the location to The personal owner
await query(`INSERT IGNORE INTO Links (UID,Type,UIDTarget) VALUES(?,'member',?)`, [locationUID, ownerUID]);
}
// change group ownership
await query(`DELETE FROM Links WHERE Type='memberA' AND UID=?`, [locationUID]);
// now link the location to The personal owner
await query(`INSERT IGNORE INTO Links (UID,Type,UIDTarget) VALUES(?,'memberA',?)`, [locationUID, groupUID]);
// and link it to the orga, so we have all locations of an organisation aligned
await query(`DELETE FROM Links WHERE Type='location' AND UID=?`, [locationUID]);
await query(`INSERT IGNORE INTO Links (UID,Type,UIDTarget) VALUES(?,'location',?)`, [locationUID, [UUID2hex(session.root)]]);
}
// check visibility filter and add them if they are not present
// change visibility
await addVisibility(session, locationUID, group, body.public);
return { success: true, result: { ...object, UID: HEX2uuid(object.UID) } };
};
/**
* Delete a location
* @param {Buffer} locationUID - Location UID
* @returns {Promise<Object>} Result with success status
*/
export const deleteLocation = async (locationUID) => {
// delete location And Link
await query(`DELETE ObjectBase,Links FROM ObjectBase LEFT JOIN Links ON (Links.UID=ObjectBase.UID )
WHERE ObjectBase.UID =? AND ObjectBase.Type='location'`, [locationUID]);
return { success: true };
};
/**
* Get a single location by UID
* @param {Buffer} locationUID - Location UID
* @returns {Promise<Object>} Result with location data or error
*/
export const getLocation = async (locationUID) => {
const result = await query(`SELECT ObjectBase.UID,ObjectBase.UIDBelongsTo,ObjectBase.Title,Member.Display,
Member.Data AS Data , ST_X(Member.Geo) AS lat, ST_Y(Member.Geo) AS lng,
pGroup.UID AS UIDgroup, pGroup.Title AS GroupTitle, gMember.Display AS GroupDisplay, gMember.Data AS GroupData,
owner.UID AS UIDowner,owner.Title AS OwnerTitle, oMember.Display AS OwnerDisplay, oMember.Data AS OwnerData
FROM ObjectBase
INNER JOIN Member ON (Member.UID=ObjectBase.UID)
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 gMember ON (pGroup.UID=gMember.UID)
INNER JOIN Links AS pLink ON (pLink.UID=ObjectBase.UID AND pLink.Type='member')
INNER JOIN ObjectBase AS owner ON (owner.UID=pLink.UIDTarget)
INNER JOIN Member AS oMember ON (oMember.UID=owner.UID)
WHERE ObjectBase.UID=? AND ObjectBase.Type='location'`,
[locationUID],
{ cast: ['UUID', 'json'], log: false }
);
if (result.length === 0) {
return { success: false, message: `location does not exist or is not accessible for this user` };
}
return { success: true, result: result[0] };
};
/**
* Get SQL for visible list filtering
* @param {Object} session - Request session
* @returns {Promise<string>} SQL fragment for visibility
*/
export const getListVisibleSql = async (session) => {
if (await isAdmin(session)) {
return ``;
}
return `INNER JOIN Visible AS Visible ON (ObjectBase.UID = Visible.UID AND Visible.UIDUser=U_UUID2BIN('${session.user}')) `;
};
/**
* Get listing of locations with optional filters
* @param {Object} session - Request session
* @param {Object} queryParams - Query parameters (types, Data, filter)
* @returns {Promise<Array>} Array of location objects
*/
export const getListing = async (session, queryParams) => {
let types = null;
if (queryParams.types) {
try {
types = queryParams.types ? JSON.parse(String(queryParams.types)) : types;
} catch (e) {
types = [queryParams.types];
}
}
let dataFields = '';
if (queryParams.Data) {
if (queryParams.Data !== 'all') {
let fields = [];
try {
fields = queryParams.Data ? JSON.parse(String(queryParams.Data)) : null;
} catch (e) {
fields[0] = [queryParams.Data];
}
for (const field of fields) {
if (field.query)
dataFields += `,JSON_QUERY(Member.Data,${pool.escape(field.path)}) AS ${pool.escape(field.alias)}`;
else
dataFields += `,JSON_VALUE(Member.Data,${pool.escape(field.path)}) AS ${pool.escape(field.alias)}`;
}
}
}
let filter = null;
if (queryParams.filter) {
try {
filter = JSON.parse(String(queryParams.filter));
} catch (e) {
}
}
const visSql = await getListVisibleSql(session);
const result = await query(`SELECT
ObjectBase.UID , ObjectBase.UIDBelongsTo, ObjectBase.Title ,Member.Display,
Member.SortName, Member.Data, ST_X(Member.Geo) AS lat, ST_Y(Member.Geo) AS lng,
pLink.UIDTarget AS UIDowner,
ogroup.UID AS UIDgroup, ogroup.Title AS GroupTitle, gMember.Display AS GroupDisplay
${dataFields}
FROM
ObjectBase AS ObjectBase
INNER JOIN Member ON (Member.UID=ObjectBase.UID)
${visSql}
INNER JOIN Links ON (Links.UID=ObjectBase.UID AND Links.Type='location')
INNER JOIN Links AS pLink ON (pLink.UID=ObjectBase.UID AND pLink.Type='member')
INNER JOIN Links AS gLink ON (gLink.UID=ObjectBase.UID AND gLink.Type='memberA')
INNER JOIN ObjectBase AS ogroup ON (ogroup.UID=gLink.UIDTarget)
INNER JOIN Member AS gMember ON (gMember.UID=ogroup.UID)
WHERE
ObjectBase.Type='location' AND Links.UIDTarget=?
GROUP BY ObjectBase.UID
ORDER BY
ObjectBase.dindex,ObjectBase.Display`, [UUID2hex(session.root)],
{
cast: ['json', 'UUID'],
filter: (object) => {
if (types && !types.includes(object.Data.type)) {
return false;
}
if (filter) {
for (const f of filter) {
const myValue = object.Data.criteria[f.key];
if (!myValue)
return false;
switch (filter.op) {
case '=':
myValue !== filter.value
return false;
break;
case '<':
myValue >= filter.value
return false;
break;
case '>':
myValue <= filter.value
return false;
break;
case '≤':
myValue > filter.value
return false;
break;
case '≥':
myValue < filter.value
return false;
break;
}
}
}
return true;
},
log: false,
group: (result, current) => {
// this will group the results during data streaming
// it is called for every streamed row
// result is the so far streamed, grouped as filtered results
// current is the row to be checked,
// do we have already a row with this UIDBelongsTo in the result set?
const index = result.findIndex(el => el.UIDBelongsTo === current.UIDBelongsTo && el.Data.type === current.Data.type);
const found = result[index];
if (index >= 0) {
// we are checking, if this is an original, not a copy, then it is replacing the entry in the result set
if (current.UID === current.UIDBelongsTo) {
// we will delete the found row in the result set and put the new row to the result
return [...result.filter((el, Index) => Index !== index), current];
// we will add the new row into the result set
}
// the row will not be addded to the result set
return result;
}
// the row will be addded to the result set
return [...result, current];
}
});
if (!queryParams.data || queryParams.Data !== 'all') {
return result.map(r => ({ ...r, Data: undefined }));
} else {
return result;
}
};