test/e2e/util/db-helper.tsmodifiedimport {Pool} from "pg";
import {v4 as uuidv4} from "uuid";
import {query} from "../../../src/db/db";
import {CoordinateDTO, Device, Issue, RoomCapturePoint} from "../../../src/models/ingress";
import {CategoryTypeHierarchyMapping} from "../../../src/types/categoryTypeHierarchyMapping";
import {encryptCdeToken} from "../../../src/util/cde.token.util";
import {createAuthToken} from "./util";
import {linkFileReferences} from "../../../src/services/issues.fileReference.service";
import {CdeTypes} from "../../../src/types/project.attachment.types";
let pool: Pool | null = null;
function getDatabaseConfig() {
return {
host: "localhost",
user: "citus",
password: process.env.AZURE_POSTGRESQL_PASSWORD,
database: "xyz-platform-database-v2",
ssl: false,
port: process.env.PG_PORT ? parseInt(process.env.PG_PORT, 10) : 5432
};
}
const getDbPool = async () => {
const pool = new Pool(getDatabaseConfig());
return pool;
};
// eslint-disable-next-line @typescript-eslint/no-explicit-any
async function executeQuery(text: string, params: any[]) {
if (!pool) {
pool = await getDbPool();
}
return pool.query(text, params);
};
async function executeSimpleQuery(text: string) {
if (!pool) {
pool = await getDbPool();
}
return pool.query(text);
};
export async function setupAutodeskCloudHub(autodeskCloudHubId: string) {
const callProcedureQuery = `CALL xyz."usp_InsertAutodeskCloudHub"($1, $2, $3)`;
await executeQuery(callProcedureQuery, [autodeskCloudHubId, "", false]);
const selectQuery = 'SELECT * FROM xyz."AutodeskCloudHub" WHERE "AccHubId" = $1';
return executeQuery(selectQuery, [autodeskCloudHubId]);
}
export async function setupProjectAndGetDetails(mongoProjectId: string) {
const autodeskCloudHubId = uuidv4();
const accHubIdResult = await setupAutodeskCloudHub(autodeskCloudHubId)
const callProcedureQuery = `CALL xyz."usp_InsertProject"
($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11::xyz."BuildType", $12, $13, $14, $15, $16, $17, $18, $19, $20, $21, $22, $23, $24)`;
await query(callProcedureQuery, [
mongoProjectId,
accHubIdResult.rows[0].AutodeskCloudHubId,
"accProjectId",
"targetAccFolderUrn",
"E2E-TEST-PROJECT-NAME",
"E2E-TEST-PROJECT-DESCRIPTION",
"GB",
"tenantName",
"timezone",
"mongoTenantId",
"LEASE",
"NONE", // progressWeightingMethod
"METRIC", // measurementType
null, // currencyId
"1 Test Street", // addressLine1
null, // addressLine2
"London", // city
"SW1A 1AA", // postcode
null, // contractor
false, // isPortfolioEnabled
null, // (unused/null param)
51.5074, // latitude
-0.1278, // longitude
"EMEA" // region
]);
const selectQuery = 'SELECT * FROM xyz."Project" WHERE "MongoProjectId" = $1';
const projectResult = await executeQuery(selectQuery, [mongoProjectId]);
return projectResult.rows[0];
}
async function cleanupProjectPhotos(projectId: string) {
const projectShardId = await getProjectShardId(projectId);
// Delete 360Captures first (they reference FileReference)
const delete360CapturesQuery = 'DELETE FROM xyz."360Capture" WHERE "ProjectShardId" = $1';
await executeQuery(delete360CapturesQuery, [projectShardId]);
// Delete photos (they reference FileReference)
const deletePhotosQuery = 'DELETE FROM xyz."Photo" WHERE "ProjectShardId" = $1';
await executeQuery(deletePhotosQuery, [projectShardId]);
// Delete file references (they reference Project)
const deleteFileRefsQuery = 'DELETE FROM xyz."FileReference" WHERE "ProjectShardId" = $1';
await executeQuery(deleteFileRefsQuery, [projectShardId]);
}
export async function deleteProject(projectId: string) {
await cleanupProjectActivityCategories(projectId);
await cleanupProjectCategoryType(projectId);
await cleanupIssueType(projectId);
await cleanupProjectIssueStatus(projectId);
await cleanupProjectModelFolder(projectId);
await cleanupProjectPhotos(projectId);
await cleanupProjectSystemTypes(projectId);
await cleanupProjectCommissioningWorkflows(projectId);
const deleteQuery = 'DELETE FROM xyz."Project" WHERE "ProjectId" = $1';
await executeQuery(deleteQuery, [projectId]);
}
export async function cleanupProjectSystemTypes(projectId: string) {
const projectShardId = await getProjectShardId(projectId);
await executeQuery('DELETE FROM xyz."SystemType" WHERE "ProjectShardId" = $1', [projectShardId]);
}
export async function cleanupProjectCommissioningWorkflows(projectId: string) {
const projectShardId = await getProjectShardId(projectId);
await executeQuery('DELETE FROM xyz."CommissioningWorkflow" WHERE "ProjectShardId" = $1', [projectShardId]);
}
export async function cleanupProjectCategoryType(projectId: string) {
const projectShardId = await getProjectShardId(projectId)
const deleteQuery = 'DELETE FROM xyz."CategoryType" WHERE "ProjectShardId" = $1';
await executeQuery(deleteQuery, [projectShardId]);
}
export async function cleanupProjectActivityCategories(projectId: string) {
const projectShardId = await getProjectShardId(projectId)
const deleteQuery = 'DELETE FROM xyz."ActivityCategory" WHERE "ProjectShardId" = $1';
await executeQuery(deleteQuery, [projectShardId]);
}
export async function cleanupProjectIssueStatus(projectId: string) {
const projectShardId = await getProjectShardId(projectId)
const deleteQuery = 'DELETE FROM xyz."IssueStatus" WHERE "ProjectShardId" = $1';
await executeQuery(deleteQuery, [projectShardId]);
}
export async function cleanupProjectModelFolder(projectId: string) {
console.log(`Deleting project ModelFolder ${projectId}`);
const projectShardId = await getProjectShardId(projectId)
const deleteQuery = 'DELETE FROM xyz."ModelFolder" WHERE "ProjectShardId" = $1';
await executeQuery(deleteQuery, [projectShardId]);
}
export async function setupIssue(
issueId: string,
issueTypeId: string,
projectId: string,
title: string,
description: string,
xMeters: number,
yMeters: number,
zMeters: number,
createdBy: string,
issueSeverityCategoryId: string,
issueLocationId: string,
issueStatusId: string,
modelId: string,
modelElementId: string,
reporterEmail: string,
assigneeEmail: string
): Promise<Issue> {
const projectShardId = await getProjectShardId(projectId);
const insertQuery = `
INSERT INTO xyz."Issue" ("IssueId", "IssueTypeId", "ProjectShardId", "Title", "Description", "XMeters",
"YMeters",
"ZMeters", "CreatedBy", "IssueSeverityCategoryId", "IssueLocationId", "IssueStatusId",
"ModelElementId", "DueDate", "ReporterEmail", "AssigneeEmail", "ImportedFromCde",
"IssueRaisedOn", "IsDeleted", "LastModifiedOn", "LastModifiedBy")
VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19,
NOW() AT TIME ZONE \'UTC\', 'e2e-test') RETURNING *;`;
const issueModelMapInsertQuery = `INSERT INTO xyz."IssueModelMapping" ("IssueId", "ProjectShardId", "ModelId", "CreatedBy")
VALUES ($1, $2, $3, $4) ON CONFLICT DO NOTHING;`;
await executeQuery(insertQuery, [
issueId, issueTypeId, projectShardId, title, description, xMeters, yMeters, zMeters, createdBy,
issueSeverityCategoryId, issueLocationId, issueStatusId, modelElementId, 'NOW()',
reporterEmail, assigneeEmail, 'FALSE', 'NOW()', 'FALSE'
]);
await executeQuery(issueModelMapInsertQuery, [
issueId, projectShardId, modelId, createdBy
]);
const rowResult = await executeQuery(`SELECT *
FROM xyz."fn_GetIssue"($1, $2)`, [projectId, issueId]);
const row = rowResult.rows[0];
return {
indexId: row.Id,
issueId: row.IssueId,
projectId: projectId,
title: row.Title,
issueLocationId: row.LocationDescription,
issueTypeId: row.IssueTypeId,
issueStatusId: row.IssueStatusId,
description: row.Description,
xMeters: row.XMeters,
yMeters: row.YMeters,
zMeters: row.ZMeters,
modelId: row.ModelId,
modelElementId: row.ModelElementId
};
}
/** Soft-delete issues (sets IsDeleted) — used by E2E to simulate tombstones while keeping rows. */
export async function markIssuesAsDeleted(projectId: string, issueIds: string[]): Promise<void> {
if (issueIds.length === 0) {
return;
}
const projectShardId = await getProjectShardId(projectId);
await executeQuery(
`UPDATE xyz."Issue" SET "IsDeleted" = TRUE WHERE "ProjectShardId" = $1 AND "IssueId" = ANY($2::uuid[])`,
[projectShardId, issueIds]
);
}
/** Hard-delete issues created in E2E (no comments/file refs expected). */
export async function deleteIssuesForE2ECleanup(projectId: string, issueIds: string[]): Promise<void> {
if (issueIds.length === 0) {
return;
}
const projectShardId = await getProjectShardId(projectId);
await executeQuery(
`DELETE FROM xyz."IssueModelMapping" WHERE "ProjectShardId" = $1 AND "IssueId" = ANY($2::uuid[])`,
[projectShardId, issueIds]
);
await executeQuery(
`DELETE FROM xyz."Issue" WHERE "ProjectShardId" = $1 AND "IssueId" = ANY($2::uuid[])`,
[projectShardId, issueIds]
);
}
export async function getIssueTypesSimplified(
projectId: string) {
const projectShardId = await getProjectShardId(projectId);
const getQuery = `SELECT *
FROM xyz."IssueType"
WHERE "ProjectShardId" = '${projectShardId}'`;
const queryResult = await executeQuery(getQuery, []);
return queryResult.rows.map(
row => ({
typeName: row.TypeName,
displayName: row.DisplayName,
})
);
}
export async function cleanupIssueType(
projectId: string) {
const projectShardId = await getProjectShardId(projectId);
const getQuery = `SELECT *
FROM xyz."IssueType"
WHERE "ProjectShardId" = '${projectShardId}'`;
const queryResult = await executeQuery(getQuery, []);
for (const row of queryResult.rows) {
const deleteQuery = `DELETE
FROM xyz."IssueType"
WHERE "ProjectShardId" = '${projectShardId}'
AND "IssueTypeId" = '${row.IssueTypeId}'`;
try {
await executeQuery(deleteQuery, []);
} catch (e) {
// console.error(e);
}
}
return queryResult.rows.map(
row => ({
typeName: row.TypeName,
displayName: row.DisplayName,
})
);
}
export async function setupIssueType(
projectId: string,
type: string
) {
const projectShardId = await getProjectShardId(projectId);
const insertQuery = `INSERT INTO xyz."IssueType" ("IssueTypeId", "ProjectShardId", "TypeName", "DisplayName",
"Description", "CreatedBy", "IsActiveInUI", "ImportedFromCde",
"ValidForIssueCreate", "ValidForIssueUpdate")
VALUES ($1, $2, $3, $4, $5, $6, $7, $8, FALSE,
FALSE) RETURNING "IssueTypeId", "DisplayName", "Description","TypeName";`
const queryResult = await executeQuery(insertQuery, [
uuidv4(), projectShardId, type, `displayName-${type}`, `description-${type}`,
'e2e-test', true, false
]);
const row = queryResult.rows[0];
return {
issueTypeId: row.IssueTypeId,
displayName: row.DisplayName,
description: row.Description,
typeName: row.TypeName
};
}
export async function setupTestCoordinate(projectId: string): Promise<CoordinateDTO> {
const pointId = uuidv4().substring(20);
const xCoordinate = Math.random() * 1000;
const yCoordinate = Math.random() * 1000;
const zCoordinate = Math.random() * 1000;
const createdBy = "E2E test";
return await setupCoordinate(
projectId,
pointId,
xCoordinate,
yCoordinate,
zCoordinate,
createdBy,
);
}
export async function setupCoordinate(
projectId: string,
pointId: string,
xCoordinate: number,
yCoordinate: number,
zCoordinate: number,
createdBy: string
): Promise<CoordinateDTO> {
const callProcedureQuery = `
SELECT *
FROM xyz."fn_InsertCoordinate"(
$1::UUID,
$2::VARCHAR,
$3::FLOAT,
$4::FLOAT,
$5::FLOAT,
$6::VARCHAR
)`;
await query(callProcedureQuery, [projectId, pointId, xCoordinate, yCoordinate, zCoordinate, createdBy]);
const projectShardId = await getProjectShardId(projectId);
const selectQuery = `SELECT *
FROM xyz."Coordinate"
WHERE "ProjectShardId" = $1
AND "PointId" = $2`;
const result = await executeQuery(selectQuery, [projectShardId, pointId]);
const createdCoordinate = result.rows[0];
return {
indexId: createdCoordinate.Id,
coordinateId: createdCoordinate.CoordinateId,
projectId: projectId,
pointId: createdCoordinate.PointId,
xCoordinate: createdCoordinate.XCoordinate,
yCoordinate: createdCoordinate.YCoordinate,
zCoordinate: createdCoordinate.ZCoordinate,
};
}
export async function setupConflictCoordinate(
projectId: string,
conflictType: string,
coordinateId: string,
pointId: string,
xCoordinate: number,
yCoordinate: number,
zCoordinate: number,
createdBy: string,
): Promise<{
coordinateConflictId: string;
projectId: string;
coordinateId: string;
conflictType: string;
pointId: string;
xCoordinate: number;
yCoordinate: number;
zCoordinate: number;
}> {
const insertQuery = `
INSERT INTO xyz."CoordinateConflict" ("CoordinateConflictId",
"ConflictType",
"ProjectShardId",
"CoordinateId",
"PointId",
"XCoordinate",
"YCoordinate",
"ZCoordinate",
"CreatedBy",
"CreatedDate")
VALUES ($1::UUID,
$2::VARCHAR,
$3::INT,
$4::UUID,
$5::VARCHAR,
$6::FLOAT,
$7::FLOAT,
$8::FLOAT,
$9::VARCHAR,
NOW()::TIMESTAMP WITH TIME ZONE) RETURNING
"CoordinateConflictId",
"ProjectShardId",
"CoordinateId",
"ConflictType",
"PointId",
"XCoordinate",
"YCoordinate",
"ZCoordinate";
`;
const projectShardId = await getProjectShardId(projectId);
const result = await query(insertQuery, [
uuidv4(),
conflictType,
projectShardId,
coordinateId,
pointId,
xCoordinate,
yCoordinate,
zCoordinate,
createdBy
]);
const createdCoordinateConflict = result.rows[0];
return {
coordinateConflictId: createdCoordinateConflict.CoordinateConflictId,
projectId: projectId,
coordinateId: createdCoordinateConflict.CoordinateId,
conflictType: createdCoordinateConflict.ConflictType,
pointId: createdCoordinateConflict.PointId,
xCoordinate: createdCoordinateConflict.XCoordinate,
yCoordinate: createdCoordinateConflict.YCoordinate,
zCoordinate: createdCoordinateConflict.ZCoordinate,
};
}
export async function getCoordinates(projectId: string) {
const projectShardId = await getProjectShardId(projectId);
const selectQuery = 'SELECT * FROM xyz."Coordinate" where "ProjectShardId" = $1';
const projectResult = await executeQuery(selectQuery, [projectShardId]);
return projectResult.rows;
}
export async function getElementInstallationStatus(projectId: string, elementId: string) {
const projectShardId = await getProjectShardId(projectId);
const selectQuery = 'SELECT * FROM xyz."ElementInstallationStatus" where "ProjectShardId" = $1 and "ModelElementId" = $2';
const result = await executeQuery(selectQuery, [projectShardId, elementId]);
return result.rows;
}
export async function getCoordinateConflicts(projectId: string) {
const projectShardId = await getProjectShardId(projectId);
const selectQuery = 'SELECT * FROM xyz."CoordinateConflict" where "ProjectShardId" = $1';
const projectResult = await executeQuery(selectQuery, [projectShardId]);
return projectResult.rows;
}
export async function getIssues(issueId: string, projectId: string) {
const selectQuery = 'SELECT * FROM xyz."fn_GetIssue"($1, $2)';
const projectResult = await executeQuery(selectQuery, [projectId, issueId]);
return projectResult.rows;
}
export async function getAnyIssueSeverityCategoryId() {
const selectQuery = 'SELECT * FROM xyz."IssueSeverityCategory" limit 1';
const projectResult = await executeQuery(selectQuery, []);
return projectResult.rows[0].IssueSeverityCategoryId;
}
export async function getIssueTypeId(projectId: string) {
const projectShardId = await getProjectShardId(projectId);
const selectQuery = 'SELECT * FROM xyz."IssueType" where "ProjectShardId" = $1 and "IsActiveInUI" = true limit 1';
const projectResult = await executeQuery(selectQuery, [projectShardId]);
return projectResult.rows[0].IssueTypeId;
}
export async function getIssueStatusId(projectId: string, status: string ) {
const projectShardId = await getProjectShardId(projectId);
const selectQuery = 'SELECT * FROM xyz."IssueStatus" where "ProjectShardId" = $1 and "Status" = $2';
const projectResult = await executeQuery(selectQuery, [projectShardId, status]);
return projectResult.rows[0].IssueStatusId;
}
export async function getIssueById(issueId: string) {
const selectQuery = 'SELECT * FROM xyz."Issue" where "IssueId" = $1';
const projectResult = await executeQuery(selectQuery, [issueId]);
return projectResult.rows;
}
export async function getFileReference(fileReferenceId: string) {
const selectQuery = 'SELECT * FROM xyz."FileReference" where "FileReferenceId" = $1';
const projectResult = await executeQuery(selectQuery, [fileReferenceId]);
return projectResult.rows;
}
export async function getBatchFile(fileId: string) {
const selectQuery = 'SELECT * FROM xyz."BatchFile" where "BatchFileId" = $1';
const projectResult = await executeQuery(selectQuery, [fileId]);
return projectResult.rows;
}
export async function getVideo(fileReferenceId: string) {
const selectQuery = 'SELECT * FROM xyz."Video" where "FileReferenceId" = $1';
const projectResult = await executeQuery(selectQuery, [fileReferenceId]);
return projectResult.rows;
}
export async function getAllVideos(projectId: string) {
const projectShardId = await getProjectShardId(projectId);
const selectQuery = 'SELECT * FROM xyz."Videos" where "ProjectShardId" = $1';
return await executeQuery(selectQuery, [projectShardId]);
}
export async function getPhoto(fileReferenceId: string) {
const selectQuery = 'SELECT * FROM xyz."Photo" where "FileReferenceId" = $1';
const projectResult = await executeQuery(selectQuery, [fileReferenceId]);
return projectResult.rows;
}
export async function getAllPhotos(projectId: string) {
const projectShardId = await getProjectShardId(projectId);
const selectQuery = 'SELECT * FROM xyz."Photo" where "ProjectShardId" = $1';
return await executeQuery(selectQuery, [projectShardId]);
}
export async function get360Capture(fileReferenceId: string) {
const selectQuery = 'SELECT * FROM xyz."360Capture" where "FileReferenceId" = $1';
const projectResult = await executeQuery(selectQuery, [fileReferenceId]);
return projectResult.rows;
}
export async function getAll360Captures(projectId: string) {
const projectShardId = await getProjectShardId(projectId);
const selectQuery = 'SELECT * FROM xyz."360Capture" where "ProjectShardId" = $1';
return await executeQuery(selectQuery, [projectShardId]);
}
export async function linkFileToIssue(projectId: string, isuseId: string, fileReferenceId: string) {
const projectShardId = await getProjectShardId(projectId);
const insertQuery = 'INSERT INTO xyz."IssueFileReferenceMapping" ("IssueId", "ProjectShardId", "FileReferenceId") VALUES ($1, $2, $3)';
await executeQuery(insertQuery, [isuseId, projectShardId, fileReferenceId]);
}
export async function unlinkFileFromIssue(projectId: string, issueId: string, fileReferenceId: string) {
const projectShardId = await getProjectShardId(projectId);
const deleteQuery = 'DELETE FROM xyz."IssueFileReferenceMapping" WHERE "IssueId" = $1 AND "ProjectShardId" = $2 AND "FileReferenceId" = $3';
await executeQuery(deleteQuery, [issueId, projectShardId, fileReferenceId]);
}
export async function getIssueHistory(projectId: string, issueId: string) {
const projectShardId = await getProjectShardId(projectId);
const selectQuery = 'SELECT * FROM xyz."IssueHistory" WHERE "ProjectShardId" = $1 AND "IssueId" = $2';
const queryResult = await executeQuery(selectQuery, [projectShardId, issueId]);
return queryResult.rows;
}
export async function getIssueFileReferenceMappings(projectId: string, issueId: string) {
const projectShardId = await getProjectShardId(projectId);
const selectQuery = 'SELECT * FROM xyz."IssueFileReferenceMapping" WHERE "ProjectShardId" = $1 AND "IssueId" = $2';
const queryResult = await executeQuery(selectQuery, [projectShardId, issueId]);
return queryResult.rows;
}
/** Same row shape as the API uses (joins FileReference + Video). */
export async function getProjectVideoRow(projectId: string, fileReferenceId: string) {
const result = await executeQuery(`SELECT * FROM xyz."fn_GetProjectVideo_V2"($1, $2)`, [projectId, fileReferenceId]);
return result.rows[0];
}
/** E2E-only updates on `xyz."Video"` for asserting API `videoTakenOn` / `thumbnailDownloadUrl` against DB state. */
export async function setVideoE2eMetadata(
fileReferenceId: string,
opts: { videoTakenOnIso?: string | null; thumbnailCloudStoragePath?: string | null }
) {
if (opts.videoTakenOnIso !== undefined) {
await executeQuery(
`UPDATE xyz."Video" SET "VideoTakenOn" = $1::timestamptz WHERE "FileReferenceId" = $2::uuid`,
[opts.videoTakenOnIso || null, fileReferenceId]
);
}
if (opts.thumbnailCloudStoragePath !== undefined) {
await executeQuery(
`UPDATE xyz."Video" SET "ThumbnailCloudStoragePath" = $1 WHERE "FileReferenceId" = $2::uuid`,
[opts.thumbnailCloudStoragePath, fileReferenceId]
);
}
}
export async function createVideoRefInDB(basePhotoBlobPath: string, projectId: string, fileReferenceId: string, mongoDeviceId: string, status: string) {
const projectShardId = await getProjectShardId(projectId);
const signedUrl = `${basePhotoBlobPath}/ProjectId=${projectId}/${fileReferenceId}`;
try {
let insertQuery = 'INSERT INTO xyz."FileReference" ' +
'("FileReferenceId", "ProjectShardId", "FileName", "FileExtension", "FileSizeBytes", "CloudStoragePath", "CreatedBy", "LastModifiedOn", "LastModifiedBy") ' +
'VALUES ($1, $2, $3, $4, $5, $6, $7, NOW() AT TIME ZONE \'UTC\', $8) on conflict do nothing';
await executeQuery(insertQuery, [fileReferenceId, projectShardId, "e2e-video-file-name", "e2e-file-extension", 1238, signedUrl, "e2e-test", "e2e-test"]);
insertQuery = 'INSERT INTO xyz."Video" ' +
'("FileReferenceId", "ProjectShardId","MongoDeviceId","Status")' +
'VALUES ($1, $2, $3, $4) on conflict do nothing';
await executeQuery(insertQuery, [fileReferenceId, projectShardId, mongoDeviceId, status]);
} catch (err) {
console.error(err)
}
}
export async function create360CaptureRefInDB(basePhotoBlobPath: string, projectId: string, fileReferenceId: string, mongoDeviceId: string) {
const projectShardId = await getProjectShardId(projectId);
const signedUrl = `${basePhotoBlobPath}/ProjectId=${projectId}/${fileReferenceId}`;
const signedSmallUrl = `${basePhotoBlobPath}/ProjectId=${projectId}/${fileReferenceId}_small`;
try {
let insertQuery = 'INSERT INTO xyz."FileReference" ' +
'("FileReferenceId", "ProjectShardId", "FileName", "FileExtension", "FileSizeBytes", "CloudStoragePath", "CreatedBy", "LastModifiedOn", "LastModifiedBy") ' +
'VALUES ($1, $2, $3, $4, $5, $6, $7, NOW() AT TIME ZONE \'UTC\', $8)';
await executeQuery(insertQuery, [fileReferenceId, projectShardId, "e2e-360-capture-file-name", "e2e-file-extension", 1238, signedUrl, "e2e-test", "e2e-test"]);
insertQuery = 'INSERT INTO xyz."360Capture" ' +
'("FileReferenceId", "ProjectShardId","SmallImageCloudStoragePath", "ActualXMeters", "ActualYMeters", "ActualZMeters", "MongoDeviceId") ' +
'VALUES ($1, $2, $3, $4, $5, $6, $7)';
await executeQuery(insertQuery, [fileReferenceId, projectShardId, signedSmallUrl, 123.4, 45.6, 789.0, mongoDeviceId]);
} catch (err) {
console.error(err)
}
}
export async function createPhotoRefInDB(basePhotoBlobPath: string, projectId: string, fileReferenceId: string, mongoDeviceId?: string) {
const projectShardId = await getProjectShardId(projectId);
const signedUrl = `${basePhotoBlobPath}/ProjectId=${projectId}/${fileReferenceId}`;
const signedSmallUrl = `${basePhotoBlobPath}/ProjectId=${projectId}/${fileReferenceId}_small`;
try {
let insertQuery = 'INSERT INTO xyz."FileReference" ' +
'("FileReferenceId", "ProjectShardId", "FileName", "FileExtension", "FileSizeBytes", "CloudStoragePath", "CreatedBy", "LastModifiedOn", "LastModifiedBy") ' +
'VALUES ($1, $2, $3, $4, $5, $6, $7, NOW() AT TIME ZONE \'UTC\', $8)';
await executeQuery(insertQuery, [fileReferenceId, projectShardId, "e2e-fileName.jpg", "jpg", 1238, signedUrl, "e2e-test", "e2e-test"]);
insertQuery = 'INSERT INTO xyz."Photo" ' +
'("FileReferenceId", "ProjectShardId","SmallImageCloudStoragePath", "MongoDeviceId") ' +
'VALUES ($1, $2, $3, $4) on conflict do nothing';
await executeQuery(insertQuery, [fileReferenceId, projectShardId, signedSmallUrl, mongoDeviceId]);
} catch (err) {
console.error(err)
}
}
export async function createIssueFileReferenceMapping(projectId: string, issueId: string, fileReferenceId: string) {
await linkFileReferences(projectId, issueId, [{fileReferenceId: fileReferenceId, type: "PHOTO"}], "E2E Test", "XYZ");
}
export async function createDBIssueFileReferenceMapping(projectId: string, issueId: string, fileReferenceId: string) {
const porjectShardId = await getProjectShardId(projectId);
const insertQuery = `INSERT INTO xyz."IssueFileReferenceMapping" ("IssueId", "FileReferenceId",
"ProjectShardId")
VALUES ($1, $2, $3) ON CONFLICT DO NOTHING;`;
await executeQuery(insertQuery, [issueId, fileReferenceId, porjectShardId]);
}
/**
* Seed a representative set of IssueMappingHistory rows (model / photo / activity-category)
* for the issue-history e2e fixture. IssueMappingHistory is blanket-TRUNCATEd by other specs'
* cleanup, so the activity-history spec seeds these in its own before() rather than relying on
* the data dump surviving. MappedItemIds reference the dump-seeded lookups for the project.
*/
export async function seedIssueMappingHistoryFixture(projectId: string, issueId: string) {
const projectShardId = await getProjectShardId(projectId);
const first = async (sql: string) => (await executeQuery(sql, [projectShardId])).rows;
const [model] = await first('SELECT "ModelId" FROM xyz."Model" WHERE "ProjectShardId" = $1 ORDER BY "ModelName" LIMIT 1');
const [file] = await first('SELECT "FileReferenceId" FROM xyz."FileReference" WHERE "ProjectShardId" = $1 ORDER BY "FileName" LIMIT 1');
const cats = await first('SELECT "ActivityCategoryId" FROM xyz."ActivityCategory" WHERE "ProjectShardId" = $1 ORDER BY "CategoryName" LIMIT 2');
const insert = `INSERT INTO xyz."IssueMappingHistory"
("TransactionId", "IssueId", "ProjectShardId", "MappingTableName", "MappedItemId", "MappedItemType", "OperationType", "Platform", "LastModifiedBy", "ValidFrom")
VALUES (gen_random_uuid(), $1, $2, $3, $4, $5, 'UPDATED', 'XYZ', 'user_e2e@example.com', $6) ON CONFLICT DO NOTHING;`;
await executeQuery(insert, [issueId, projectShardId, 'IssueModelMapping', model.ModelId, 'MODEL', '2025-11-26T15:27:43Z']);
await executeQuery(insert, [issueId, projectShardId, 'IssueFileReferenceMapping', file.FileReferenceId, 'PHOTO', '2025-11-26T15:27:47Z']);
for (const c of cats) {
await executeQuery(insert, [issueId, projectShardId, 'IssueActivityCategoryMapping', c.ActivityCategoryId, 'ACTIVITY_CATEGORY', '2026-03-09T10:46:34Z']);
}
}
export async function createModelElement(projectId: string, modelElementId: string, originalSourceModelElementId: string, latestSourceModeElementId: string, latestSourceElementId: string) {
const projectShardId = await getProjectShardId(projectId);
await createSourceModelElement(projectShardId, originalSourceModelElementId)
const insertQuery = `INSERT INTO xyz."ModelElement" ("ModelElementId", "ProjectShardId",
"OriginalSourceModelElementId", "LatestSourceModelElementId",
"LatestSourceElementId", "CreatedBy")
VALUES ($1, $2, $3, $4, $5, $6) ON CONFLICT DO NOTHING;`;
await executeQuery(insertQuery, [modelElementId, projectShardId, originalSourceModelElementId, latestSourceModeElementId, latestSourceElementId, 'E2E-Tester'])
}
async function createSourceModelElement(projectShardId: string, elementId: string) {
const insertQuery = `INSERT INTO xyz."SourceModelElement"
("SourceModelElementId", "ProjectShardId", "SourceElementId", "OriginalSourceModelElementId")
VALUES ($2, $1, $3, $4) ON CONFLICT DO NOTHING;`;
await executeQuery(insertQuery, [projectShardId, elementId, elementId, elementId]);
}
export async function createSiteMarkerWithMapping(projectId: string, udi: string) {
const projectShardId = await getProjectShardId(projectId);
const sitemarkerId = uuidv4();
try {
const insertQuery = `INSERT INTO xyz."SiteMarker"("SiteMarkerId", "SiteMarkerBatchId", "Udi", "Code",
"MongoSiteMarkerId", "MongoSiteMarkerBatchId", "Status",
"CreatedBy", "InsertedOn")
VALUES ($1, '8ae9cb25-beb5-4d0f-a4f0-05f98bd2cf85', $2, 'code-1', 'mong-marker-id-1',
'BatchId01', 'TRANSFORM_ATTACHED', 'E2E Test', NOW()::TIMESTAMP WITH TIME ZONE)`
await executeQuery(insertQuery, [sitemarkerId, udi])
} catch (err) {
console.error("error while creating marker", err)
}
const insertProjectMappingQuery = `INSERT INTO xyz."ProjectSiteMarkerMapping"("SiteMarkerId",
"ProjectShardId",
"CreatedBy",
"InsertedOn")
VALUES ($1,
$2,
'e2e test',
NOW()::TIMESTAMP WITH TIME ZONE) `;
await executeQuery(insertProjectMappingQuery, [sitemarkerId, projectShardId])
}
export async function getCategoryTypeByTypeName(projectId: string, typeName: string) {
const projectShardId = await getProjectShardId(projectId);
const selectQuery = 'SELECT * FROM xyz."CategoryType" WHERE "ProjectShardId" = $1 AND "TypeName" = $2 order by "InsertedOn" desc';
const projectResult = await executeQuery(selectQuery, [projectShardId, typeName]);
return projectResult.rows;
}
export async function getDeviceToken(deviceId: string) {
const selectQuery = `SELECT *
FROM xyz."DeviceToken"
WHERE "DeviceTokenId" = $1`
const result = await executeQuery(selectQuery, [deviceId]);
return result.rows;
}
export async function getDefaultDisciplineByName(typeName: string) {
const selectQuery = 'SELECT * FROM xyz."DefaultDiscipline" WHERE "DisciplineName" = $1';
const projectResult = await executeQuery(selectQuery, [typeName]);
return projectResult.rows;
}
export async function getDefaultDisciplineById(id: string) {
const selectQuery = 'SELECT * FROM xyz."DefaultDiscipline" WHERE "DisciplineId" = $1';
const projectResult = await executeQuery(selectQuery, [id]);
return projectResult.rows;
}
export async function resetModelFolder(modelFolderId: string) {
const updateQuery = `UPDATE xyz."ModelFolder"
SET "LastModifiedBy" = null,
"LastModifiedOn" = null
WHERE "ModelFolderId" = $1`;
await executeQuery(updateQuery, [modelFolderId]);
}
export async function resetModel(modelId: string) {
const updateQuery = `UPDATE xyz."Model"
SET "IsDeleted" = false
WHERE "ModelId" = $1`;
await executeQuery(updateQuery, [modelId]);
}
export async function getModelsForProject(projectId: string) {
const projectShardId = await getProjectShardId(projectId);
const selectQuery = 'SELECT * FROM xyz."Model" WHERE "ProjectShardId" = $1 ORDER BY "Id" DESC';
const queryResult = await executeQuery(selectQuery, [projectShardId]);
return queryResult.rows;
}
export async function deleteCdeUserMapping(cdeProjectId: string) {
const deleteQuery = 'DELETE from xyz."CdeProjectCdeUserMapping" WHERE "CdeProjectId" =$1';
await executeQuery(deleteQuery, [cdeProjectId]);
}
export async function getProjectLinkToCdeProjectForUser(cdeProjectId: string) {
const selectQuery = `SELECT *
FROM xyz."CdeProjectCdeUserMapping"
WHERE "CdeProjectId" = $1`
const {rows} = await query(selectQuery, [cdeProjectId]);
return rows.length === 1;
}
export async function createCategoryType(projectId: string, typeName: string, importedFromSch: boolean) {
const projectShardId = await getProjectShardId(projectId);
const categoryTypeUUID = uuidv4();
const level = (CategoryTypeHierarchyMapping[typeName as keyof typeof CategoryTypeHierarchyMapping]);
const insertQuery = 'INSERT INTO xyz."CategoryType" ("CategoryTypeId", "ProjectShardId", "TypeName", "HierarchyLevel", "ImportedFromSchedule") VALUES ($1, $2, $3, $4, $5) ON Conflict ("ProjectShardId", "TypeName", "ActvCodeTypeScope", "ImportedFromSchedule") do nothing';
try {
await executeQuery(insertQuery, [categoryTypeUUID, projectShardId, typeName, level, importedFromSch]);
return categoryTypeUUID;
} catch (err) {
console.error(err)
throw err;
}
}
export async function createActivityCategory(projectId: string, parentCategoryId: string | null, categoryName: string, categoryTypeId: string) {
const projectShardId = await getProjectShardId(projectId);
const activityCategoryUUID = uuidv4();
const insertQuery = 'INSERT INTO xyz."ActivityCategory" ("ActivityCategoryId", "ParentActivityCategoryId", "CategoryName", "ProjectShardId", "CategoryTypeId", "CategoryIdentifier") VALUES ($1, $2, $3,$4, $5, $6)';
await executeQuery(insertQuery, [activityCategoryUUID, parentCategoryId, categoryName, projectShardId, categoryTypeId, categoryName]);
return activityCategoryUUID;
}
export async function createActivityForAProject(projectId: string, userActivityId: string) {
const projectShardId = await getProjectShardId(projectId);
const insertQuery = `INSERT INTO xyz."Activity" ("ActivityId", "ProjectShardId", "UserActivityId", "SystemIdentifier")
VALUES ($1, $2, $3, $4)`;
const activityUUID = uuidv4();
await executeQuery(insertQuery, [activityUUID, projectShardId, userActivityId, userActivityId]);
return activityUUID;
}
export async function updateElementActivityMappingToActive(modelActivityMappingId: string) {
const updateQuery = `UPDATE xyz."ModelElementActivityMapping"
SET "IsActive" = TRUE
WHERE "ModelElementActivityMappingId" = $1`;
await executeQuery(updateQuery, [modelActivityMappingId]);
}
export async function updateScheduleRevisionBaseLine(scheduleRevisionId: string, isBaseLine: boolean) {
const updateQuery = `UPDATE xyz."ScheduleRevision"
SET "IsBaseline" = $1
WHERE "ScheduleRevisionId" = $2`
await executeQuery(updateQuery, [isBaseLine, scheduleRevisionId]);
}
export async function updateScheduleRevisionCurrent(scheduleRevisionId: string, isCurrent: boolean) {
const updateQuery = `UPDATE xyz."ScheduleRevision"
SET "IsCurrent" = $1
WHERE "ScheduleRevisionId" = $2`
await executeQuery(updateQuery, [isCurrent, scheduleRevisionId]);
}
export async function updateAllScheduleRevisionCurrentForProject(projectId: string, isCurrent: boolean) {
const projectShardId = await getProjectShardId(projectId);
const updateQuery = `UPDATE xyz."ScheduleRevision"
SET "IsCurrent" = $1
WHERE "ProjectShardId" = $2`
await executeQuery(updateQuery, [isCurrent, projectShardId]);
}
export async function getScheduleRevision(scheduleRevisionId: string) {
const selectQuery = `SELECT *
FROM xyz."ScheduleRevision"
WHERE "ScheduleRevisionId" = $1`
const queryResult = await executeQuery(selectQuery, [scheduleRevisionId]);
return queryResult.rows;
}
export async function getScheduleRevisionByUserFileId(userFileId: string) {
console.log(`fetching schedule revision by userFileId ${userFileId}`);
const selectQuery = `SELECT *
FROM xyz."ScheduleRevision"
WHERE "UserFileId" = $1`
const queryResult = await executeQuery(selectQuery, [userFileId]);
return queryResult.rows;
}
export async function getActivityCategoryMapping(projectId: string, activityId: string, activityCategoryId: string) {
const projectShardId = await getProjectShardId(projectId);
const selectQuery = 'SELECT * FROM xyz."ActivityCategoryMapping" WHERE "ProjectShardId" = $1 AND "ActivityId"=$2 AND "ActivityCategoryId"=$3';
const queryResult = await executeQuery(selectQuery, [projectShardId, activityId, activityCategoryId]);
return queryResult.rows;
}
export async function createActivityCategoryMapping(projectId: string, activityCategoryId: string, activityId: string, categoryTypeId: string) {
const projectShardId = await getProjectShardId(projectId);
const activityCategoryMappingId = uuidv4();
const insertQuery = 'INSERT INTO xyz."ActivityCategoryMapping" ("ActivityCategoryMappingId", "ProjectShardId", "ActivityId", "ActivityCategoryId", "CategoryTypeId") VALUES ($1, $2, $3, $4, $5)';
await executeQuery(insertQuery, [activityCategoryMappingId, projectShardId, activityId, activityCategoryId, categoryTypeId]);
return activityCategoryMappingId;
}
export async function getElementActivityMapping(projectId: string, activityId: string, modelElementId: string) {
const projectShardId = await getProjectShardId(projectId);
const selectQuery = 'SELECT * FROM xyz."ModelElementActivityMapping" WHERE "ProjectShardId" = $1 AND "ActivityId"=$2 AND "ModelElementId"=$3 ';
const queryResult = await executeQuery(selectQuery, [projectShardId, activityId, modelElementId]);
return queryResult.rows;
}
async function getProjectShardId(projectId: string) {
const selectQuery = 'SELECT * FROM xyz."fn_GetProjectShardId" ($1)';
const queryResult = await executeQuery(selectQuery, [projectId]);
return queryResult.rows[0].fn_GetProjectShardId;
}
export async function getActivityCategories(projectId: string) {
const projectShardId = await getProjectShardId(projectId);
const selectQuery = 'SELECT * FROM xyz."ActivityCategory" WHERE "ProjectShardId" = $1';
const queryResult = await executeQuery(selectQuery, [projectShardId]);
return queryResult.rows;
}
export async function getProject(projectId: string) {
const selectQuery = 'SELECT * FROM xyz."Project" where "ProjectId"= ($1)';
const queryResult = await executeQuery(selectQuery, [projectId]);
return queryResult.rows[0];
}
export async function getProjectByMongoId(mongoProjectId: string) {
const selectQuery = 'SELECT * FROM xyz."Project" WHERE "MongoProjectId" = $1';
const {rows} = await query(selectQuery, [mongoProjectId]);
return rows;
}
export async function getModelFolderByProject(projectId: string, groupName: string) {
const projectShardId = await getProjectShardId(projectId);
const selectQuery = 'SELECT * FROM xyz."ModelFolder" WHERE "ProjectShardId" = $1 AND "FolderName" = $2';
const queryResult = await executeQuery(selectQuery, [projectShardId, groupName]);
return queryResult.rows;
}
export async function getModelFolderById(modelFolderId: string) {
const selectQuery = 'SELECT * FROM xyz."ModelFolder" WHERE "ModelFolderId" = $1';
const queryResult = await executeQuery(selectQuery, [modelFolderId]);
return queryResult.rows;
}
export async function getLatestModelVersion(projectId: string, modelId: string) {
const projectShardId = await getProjectShardId(projectId);
const selectQuery = 'SELECT * FROM xyz."ModelVersion" WHERE "ProjectShardId" = $1 AND "ModelId" = $2 ORDER BY "VersionNumber" DESC LIMIT 1';
const queryResult = await executeQuery(selectQuery, [projectShardId, modelId]);
return queryResult.rows[0];
}
export async function getModelVersions(projectId: string, modelId: string) {
const projectShardId = await getProjectShardId(projectId);
const selectQuery = 'SELECT * FROM xyz."ModelVersion" WHERE "ProjectShardId" = $1 AND "ModelId" = $2';
const queryResult = await executeQuery(selectQuery, [projectShardId, modelId]);
return queryResult.rows;
}
export async function getAllElementActivityMapping(projectId: string) {
const projectShardId = await getProjectShardId(projectId);
const selectQuery = 'SELECT * FROM xyz."ModelElementActivityMapping" WHERE "ProjectShardId" = $1';
const queryResult = await executeQuery(selectQuery, [projectShardId]);
return queryResult.rows;
}
export async function getActivityCategoryById(projectId: string, activityCategoryId: string) {
const projectShardId = await getProjectShardId(projectId);
const selectQuery = 'SELECT * FROM xyz."ActivityCategory" WHERE "ProjectShardId" = $1 AND "ActivityCategoryId" = $2';
const queryResult = await executeQuery(selectQuery, [projectShardId, activityCategoryId]);
return queryResult.rows;
}
export async function getActivityCategoryByName(projectId: string, categoryName: string) {
const projectShardId = await getProjectShardId(projectId);
const selectQuery = 'SELECT * FROM xyz."ActivityCategory" WHERE "ProjectShardId" = $1 AND "CategoryName"=$2 order by "InsertedOn" desc';
const queryResult = await executeQuery(selectQuery, [projectShardId, categoryName]);
return queryResult.rows;
}
export async function getScheduleActivityCategoryMapping(projectId: string, scheduleActivityCategoryMappingId: string) {
const projectShardId = await getProjectShardId(projectId);
const selectQuery = 'SELECT * FROM xyz."ScheduleActivityCategoryMapping" WHERE "ProjectShardId" = $1 AND "ScheduleActivityCategoryMappingId"=$2';
const queryResult = await executeQuery(selectQuery, [projectShardId, scheduleActivityCategoryMappingId]);
return queryResult.rows;
}
export async function createScheduleActivityCategoryMapping(projectId: string, activityCategoryId: string, activityId: string, categoryTypeId: string, scheduleRevisionId: string) {
const projectShardId = await getProjectShardId(projectId);
const scheduleActivityCateogryMappingUUID = uuidv4();
const insertQuery = 'INSERT INTO xyz."ScheduleActivityCategoryMapping" ("ScheduleActivityCategoryMappingId", "ProjectShardId", "ScheduleRevisionId", "ActivityId", "CategoryTypeId", "ActivityCategoryId") VALUES ($1, $2, $3, $4, $5, $6)';
await executeQuery(insertQuery, [scheduleActivityCateogryMappingUUID, projectShardId, scheduleRevisionId, activityId, categoryTypeId, activityCategoryId]);
return scheduleActivityCateogryMappingUUID;
}
export async function createIssueActivityCategoryMapping(projectId: string, issueId: string, activityCategoryId: string, categoryTypeId: string) {
const projectShardId = await getProjectShardId(projectId);
const insertQuery = 'INSERT INTO xyz."IssueActivityCategoryMapping" ("ProjectShardId", "IssueId", "ActivityCategoryId", "CategoryTypeId", "CreatedBy") VALUES ($1, $2, $3, $4, $5);';
await executeQuery(insertQuery, [projectShardId, issueId, activityCategoryId, categoryTypeId, 'E2E Test']);
}
export async function createRoomCapturePoint(projectId: string, modelRoomId: string, modelLevelId: string, userCapturePointId: string, createdFrom: string, xMeters: number, yMeters: number, zMeters: number, createdBy: string) {
const roomCapturePointId = uuidv4();
const projectShardId = await getProjectShardId(projectId);
const insertQuery = 'INSERT INTO xyz."RoomCapturePoint" ("RoomCapturePointId", "ProjectShardId", "ModelRoomId", "ModelLevelId", "UserCapturePointId", "CreatedFrom", "XMeters", "YMeters", "ZMeters", "CreatedBy") VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)';
try {
await executeQuery(insertQuery, [roomCapturePointId, projectShardId, modelRoomId, modelLevelId, userCapturePointId, createdFrom, xMeters, yMeters, zMeters, createdBy]);
} catch (e) {
console.error(e);
}
return roomCapturePointId;
}
export async function createUserFile(projectId: string) {
const projectShardId = await getProjectShardId(projectId);
const userFileIdUUID = uuidv4();
const fileType = 'fileType';
const fileName = 'Model-1-root-v2'
const fileExtension = 'xyz'
const cloudStoragePath = 'xyz'
const uploadedOn = '2024-07-03 08:03:38.602167+00'
const uploadedBy = 'manual'
const insertQuery = 'INSERT INTO xyz."UserFile" ("UserFileId","ProjectShardId","FileType","FileName","FileExtension","CloudStoragePath","UploadedOn","UploadedBy") VALUES ($1, $2, $3, $4, $5, $6, $7, $8)';
await executeQuery(insertQuery, [userFileIdUUID, projectShardId, fileType, fileName, fileExtension, cloudStoragePath, uploadedOn, uploadedBy]);
return userFileIdUUID;
}
/**
* Creates a UserFile in Queued, cancellable state for E2E tests that need to assert
* successful cancel (200). The row has IngestStatus='Queued', IsCancellable=TRUE, CancelRequested=FALSE.
*/
export async function createCancellableUserFile(projectId: string): Promise<string> {
const projectShardId = await getProjectShardId(projectId);
const userFileIdUUID = uuidv4();
const fileType = 'Model';
const fileName = 'e2e-cancellable-model';
const fileExtension = 'xyz';
const cloudStoragePath = 'e2e/cancellable';
const uploadedOn = '2024-07-03 08:03:38.602167+00';
const uploadedBy = 'e2e-test';
const insertQuery = `INSERT INTO xyz."UserFile" (
"UserFileId", "ProjectShardId", "FileType", "FileName", "FileExtension",
"CloudStoragePath", "UploadedOn", "UploadedBy", "IngestStatus", "IsCancellable", "CancelRequested"
) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, 'Queued', TRUE, FALSE)`;
await executeQuery(insertQuery, [
userFileIdUUID, projectShardId, fileType, fileName, fileExtension,
cloudStoragePath, uploadedOn, uploadedBy
]);
return userFileIdUUID;
}
export async function getUserFile(projectId: string, userFileId: string) {
const projectShardId = await getProjectShardId(projectId);
const selectQuery = 'SELECT * FROM xyz."UserFile" WHERE "ProjectShardId" = $1 AND "UserFileId" = $2';
const queryResult = await executeQuery(selectQuery, [projectShardId, userFileId]);
return queryResult.rows;
}
export async function createScheduleRevision(projectId: string, userFileId: string, isBaseLine: boolean, isCurrent: boolean, isHistoricalBaseline: boolean) {
const projectShardId = await getProjectShardId(projectId);
const scheduleRevisionIdUUID = uuidv4();
const scheduleName = "test schedule"
const insertQuery = `INSERT INTO xyz."ScheduleRevision" ("ScheduleRevisionId","ProjectShardId","UserFileId","ScheduleName","IsBaseline","IsCurrent","IsHistoricalBaseline") VALUES ($1, $2, $3, $4, $5, $6, $7)`;
await executeQuery(insertQuery, [scheduleRevisionIdUUID, projectShardId, userFileId, scheduleName, isBaseLine, isCurrent, isHistoricalBaseline]);
return scheduleRevisionIdUUID;
}
export async function getCoordinateConflict(projectId: string): Promise<any> {
const projectShardId = await getProjectShardId(projectId);
const selectQuery = 'SELECT * FROM xyz."CoordinateConflict"';
const queryResult = await executeQuery(selectQuery, [projectShardId]);
return queryResult.rows;
}
export async function createScheduleCalendar(projectId: string, scheduleRevisionId: string) {
const projectShardId = await getProjectShardId(projectId);
const scheduleCalendarId = uuidv4();
const scheduleProjectId = null
const calendarName = 'DefaultCalendar';
const calendarType = 'Standard';
const dailyHours = 8.0;
const workingDayRules = 'Monday-Friday: 8 hours';
const clndrId = 1;
const insertQuery = `
INSERT INTO xyz."ScheduleCalendar" ("ScheduleCalendarId", "ScheduleRevisionId", "ProjectShardId",
"ScheduleProjectId", "CalendarName", "CalendarType",
"DailyHours", "WeeklyHours", "MonthlyHours",
"YearlyHours", "IsPrivate", "WorkingDayRules",
"ClndrId", "ProjId", "BaseClndrId")
VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15)
`;
await executeQuery(insertQuery, [
scheduleCalendarId, scheduleRevisionId, projectShardId, scheduleProjectId, calendarName, calendarType, dailyHours, null, null, null, null, workingDayRules, clndrId, null, null]);
return scheduleCalendarId;
}
export async function createWbs(projectId: string, fullWbsCode: string, systemIdentifier: string) {
const wbsId = uuidv4();
const projectShardId = await getProjectShardId(projectId);
const insertQuery = `INSERT INTO xyz."Wbs" ("WbsId", "ProjectShardId", "FullWbsCode", "SystemIdentifier")
VALUES ($1, $2, $3, $4)`;
await executeQuery(insertQuery, [
wbsId,
projectShardId,
fullWbsCode,
systemIdentifier
]);
return wbsId;
}
export async function createScheduleWbs(projectId: string, scheduleRevisionId: string) {
const projectShardId = await getProjectShardId(projectId);
const fullWbsCode = 'WBS-001';
const systemIdentifier = 'WBS-SAMPLE-1'
const wbsId = await createWbs(projectId, fullWbsCode, systemIdentifier)
const parentWbsId = uuidv4();
const wbsName = 'test'
const startDate = '2024-09-01 08:00:00';
const finishDate = '2024-09-30 17:00:00';
const actualStartDate = '2024-09-01 08:00:00';
const actualFinishDate = '2024-09-30 17:00:00';
const isRoot = true;
const wbsCode = 'WBS-002';
const sourceFileWbsId = "12345"
const insertQuery = 'INSERT INTO xyz."ScheduleWbs" ("WbsId","ScheduleRevisionId","ProjectShardId","ParentWbsId","WbsName","FullWbsCode","StartDate","FinishDate", "ActualStartDate", "ActualFinishDate", "IsRootWbs", "WbsCode","SystemIdentifier", "SourceFileWbsId") VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14)';
await executeQuery(insertQuery, [wbsId, scheduleRevisionId, projectShardId, parentWbsId, wbsName, fullWbsCode, startDate, finishDate, actualStartDate, actualFinishDate, isRoot, wbsCode, systemIdentifier, sourceFileWbsId]);
return wbsId;
}
export async function createScheduleActivity(projectId: string, scheduleRevisionId: string, parentWbsId: any, scheduleCalendarId: string, activityId: string) {
const projectShardId = await getProjectShardId(projectId);
const activityIdUUID = uuidv4();
const activityType = 'Task';
const activityStatus = 'In Progress';
const userActivityId = 'ACT-001';
const activityName = 'Activity 1';
const onDrivingPath = true;
const startDate = '2024-09-01 08:00:00';
const finishDate = '2024-09-10 17:00:00';
const actualStartDate = '2024-09-01 08:00:00';
const actualFinishDate = null;
const systemIdentifier = 'SAMPLE-SYS-IDENTIFIER';
const taskId = 1;
const projId = 1;
const sourceFileWbsId = 1;
const clndrId = 1;
const validForProgressCalculations = true
const insertQuery = `INSERT INTO xyz."ScheduleActivity" ("ActivityId", "ScheduleRevisionId", "ProjectShardId",
"ParentWbsId",
"ScheduleCalendarId", "ActivityType", "ActivityStatus",
"UserActivityId", "ActivityName", "OnDrivingPath",
"StartDate",
"FinishDate", "ActualStartDate", "ActualFinishDate",
"SystemIdentifier",
"TaskId", "ProjId", "SourceFileWbsId", "ClndrId",
"ValidForProgressCalculations")
VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19,
$20)`;
await executeQuery(insertQuery, [
activityId, scheduleRevisionId, projectShardId, parentWbsId, scheduleCalendarId, activityType,
activityStatus, userActivityId, activityName, onDrivingPath, startDate, finishDate,
actualStartDate, actualFinishDate, systemIdentifier, taskId, projId, sourceFileWbsId, clndrId, validForProgressCalculations]);
return activityIdUUID;
}
export async function createScheduleDependency(
projectId: string, scheduleRevisionId: string, activityId: string, predecessorActivityId: string,
taskPredId: number, taskId: number, predTaskId: number
) {
const projectShardId = await getProjectShardId(projectId);
const scheduleDependencyId = uuidv4();
const dependency = "dependency"
const insertQuery = `
INSERT INTO xyz."ScheduleDependency" ("ScheduleDependencyId", "ScheduleRevisionId", "ProjectShardId",
"ActivityId", "PredecessorActivityId", "Dependency",
"TaskPredId", "TaskId", "PredTaskId")
VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)
`;
await executeQuery(insertQuery, [
scheduleDependencyId,
scheduleRevisionId,
projectShardId,
activityId,
predecessorActivityId,
dependency,
taskPredId,
taskId,
predTaskId
]);
return scheduleDependencyId;
}
export async function revertProjectSoftDelete(projectId: string, projectShardId: string) {
console.log("DB_HELPER||Reverting project soft delete for projectId: ", projectId);
const updateQuery = `UPDATE xyz."Project"
SET "IsDeleted" = false
WHERE "ProjectId" = $1`;
const res = await executeQuery(updateQuery, [projectId]);
// Create ProjectMapping
const createProjectMappingQuery = `INSERT INTO xyz."ProjectIdMapping" ("ProjectShardId", "ProjectId")
VALUES ($1, $2)`;
await executeQuery(createProjectMappingQuery, [projectShardId, projectId]);
}
export async function getScheduleActivity(ScheduleRevisionId: string) {
const selectQuery = 'SELECT * FROM xyz."ScheduleActivity" WHERE "ScheduleRevisionId" = $1';
const queryResult = await executeQuery(selectQuery, [ScheduleRevisionId]);
return queryResult.rows[0]
}
export async function getCdeAccountByThirdPartyAccountId(thirdPartyAccountId: string) {
const selectQuery = 'SELECT * FROM xyz."CdeAccount" WHERE "ThirdPartyCdeAccountId" = $1';
const queryResult = await executeQuery(selectQuery, [thirdPartyAccountId]);
return queryResult.rows[0]
}
export async function cleanupCDEProjectDetails(thirdPartyAccountId: string) {
const selectCdeAccountQuery = 'SELECT * FROM xyz."CdeProject" WHERE "ThirdPartyCdeProjectId" = $1 ORDER BY "InsertedOn" DESC';
const result = await executeQuery(selectCdeAccountQuery, [thirdPartyAccountId]);
if (result.rows.length > 0) {
const deleteSyncConfig = 'DELETE FROM xyz."CdeSyncConfig" WHERE "CdeProjectId" = $1';
await executeQuery(deleteSyncConfig, [result.rows[0].CdeProjectId]);
await cleanupUserByEmail('user@xyz.com')
const deleteCdeRole = 'DELETE FROM xyz."CdeRole" WHERE "CdeProjectId" = $1';
await executeQuery(deleteCdeRole, [result.rows[0].CdeProjectId]);
}
const deleteCdeAccount = 'DELETE FROM xyz."CdeProject" WHERE "ThirdPartyCdeProjectId" = $1';
await executeQuery(deleteCdeAccount, [thirdPartyAccountId]);
}
export async function cleanupUserByEmail(email: string) {
const selectCdeUser = 'SELECT * FROM xyz."CdeUser" WHERE "XyzUserEmail" = $1';
const result = await executeQuery(selectCdeUser, [email]);
if (result.rows.length > 0) {
try {
console.log("deleting user mapping for " + result.rows[0].CdeUserId);
const deleteCdeProjectCdeUserMapping = 'DELETE FROM xyz."CdeProjectCdeUserMapping" where "CdeUserId"=$1';
await executeQuery(deleteCdeProjectCdeUserMapping, [result.rows[0].CdeUserId]);
} catch (err) {
console.error(err);
}
try {
const deleteCdeUser = 'DELETE FROM xyz."CdeUser" WHERE "CdeUserId" = $1';
await executeQuery(deleteCdeUser, [result.rows[0].CdeUserId]);
} catch (err) {
console.error(err);
}
}
}
export async function cleanUpIssueMappingHistory() {
const clearMappingHistory = 'TRUNCATE TABLE xyz."IssueMappingHistory"';
await executeSimpleQuery(clearMappingHistory);
}
export async function getCdeProjectByThirdPartyProjectId(thirdPartyProjectId: string) {
const selectQuery = 'SELECT * FROM xyz."CdeProject" WHERE "ThirdPartyCdeProjectId" = $1';
const queryResult = await executeQuery(selectQuery, [thirdPartyProjectId]);
return queryResult.rows[0]
}
export async function getCdeSyncConfigByCdeProjectId(cdeProjectId: string) {
const selectQuery = 'SELECT * FROM xyz."CdeSyncConfig" WHERE "CdeProjectId" = $1';
const queryResult = await executeQuery(selectQuery, [cdeProjectId]);
return queryResult.rows[0];
}
export async function getCdeProjectToXyzUserLink(cdeProjectId: string) {
const selectQuery = 'SELECT * FROM xyz."CdeProjectCdeUserMapping" WHERE "CdeProjectId" = $1';
const queryResult = await executeQuery(selectQuery, [cdeProjectId]);
return queryResult.rows[0];
}
export async function getProjectLocations(projectId: string) {
const projectShardId = await getProjectShardId(projectId);
const selectQuery = 'SELECT * FROM xyz."IssueLocation" WHERE "ProjectShardId" = $1';
const queryResult = await executeQuery(selectQuery, [projectShardId]);
return queryResult.rows;
}
export async function cleanupIssueLocation(projectId: string) {
const projectShardId = await getProjectShardId(projectId);
const deleteQuery = 'DELETE FROM xyz."IssueLocation" WHERE "ProjectShardId" = $1 AND "ThirdPartyLocationId" is not null';
const queryResult = await executeQuery(deleteQuery, [projectShardId]);
return queryResult.rows;
}
export async function createScheduleProject(projectId: string, scheduleRevisionId: string, projShortName: string, projId: number) {
const projectShardId = await getProjectShardId(projectId);
const scheduleProjectId = uuidv4();
const insertQuery = `
INSERT INTO xyz."ScheduleProject" ("ScheduleProjectId", "ScheduleRevisionId", "ProjectShardId",
"ProjShortName", "ProjId")
VALUES ($1, $2, $3, $4, $5)
`;
await executeQuery(insertQuery, [
scheduleProjectId,
scheduleRevisionId,
projectShardId,
projShortName,
projId
]);
return scheduleProjectId;
}
export async function createScheduleResource(projectId: string, scheduleRevisionId: string, resourceName: string, userResourceId: string, resourceType: string, rsrcId: number) {
const projectShardId = await getProjectShardId(projectId);
const scheduleResourceId = uuidv4();
const insertQuery = `
INSERT INTO xyz."ScheduleResource" ("ScheduleResourceId", "ScheduleRevisionId", "ProjectShardId",
"ResourceName", "UserResourceId", "ResourceType", "RsrcId")
VALUES ($1, $2, $3, $4, $5, $6, $7)
`;
await executeQuery(insertQuery, [
scheduleResourceId,
scheduleRevisionId,
projectShardId,
resourceName,
userResourceId,
resourceType,
rsrcId
]);
return scheduleResourceId;
}
export async function createScheduleResourceRate(projectId: string, scheduleRevisionId: string, scheduleResourceId: string, rsrcRateId: number, rsrcId: number) {
const projectShardId = await getProjectShardId(projectId);
const scheduleResourceRateId = uuidv4();
const insertQuery = `
INSERT INTO xyz."ScheduleResourceRate" ("ScheduleResourceRateId", "ScheduleRevisionId", "ProjectShardId",
"ScheduleResourceId", "RsrcRateId", "RsrcId")
VALUES ($1, $2, $3, $4, $5, $6)
`;
await executeQuery(insertQuery, [
scheduleResourceRateId,
scheduleRevisionId,
projectShardId,
scheduleResourceId,
rsrcRateId,
rsrcId
]);
return scheduleResourceRateId;
}
export async function updateStatusReport(projectId: string, scheduleRevisionId: string, blobUrl: string) {
const projectShardId = await getProjectShardId(projectId);
const updateQuery = `update reporting."ProgressOutput"
set "CloudStoragePath" = $1
Where "ProjectShardId" = $2
AND "ScheduleRevisionId" = $3`
try {
console.log("updaditng reporting.\"ProgressOutput\"")
await executeQuery(updateQuery, [
blobUrl,
projectShardId,
scheduleRevisionId,
]);
} catch (e) {
console.error(e);
}
}
/**
* Creates a ProgressOutput record in the reporting schema for schedule parquet files.
*
* @param projectId - The project ID
* @param scheduleRevisionId - The schedule revision ID
* @param cloudStoragePath - The cloud storage path to the parquet file
* @param outputType - The output type (default: 'planned')
* @param outputHierarchyLevel - The output hierarchy level (default: 'activity')
* @returns The created ProgressOutput ID
*/
export async function createProgressOutput(
projectId: string,
scheduleRevisionId: string,
cloudStoragePath: string,
outputType: string = 'planned',
outputHierarchyLevel: string = 'activity'
): Promise<string> {
const projectShardId = await getProjectShardId(projectId);
const progressOutputId = uuidv4();
// Get project name
const projectQuery = `SELECT "Name"
FROM xyz."Project"
WHERE "ProjectId" = $1`;
const projectResult = await executeQuery(projectQuery, [projectId]);
const projectName = projectResult.rows.length > 0 ? projectResult.rows[0].Name : 'Test Project';
const insertQuery = `INSERT INTO reporting."ProgressOutput"("ProgressOutputId",
"ProjectShardId",
"ProjectId",
"ProjectName",
"ScheduleRevisionId",
"CloudStoragePath",
"OutputType",
"OutputHierarchyLevel",
"CalculatedOn",
"IsClientFacing")
VALUES ($1, $2, $3, $4, $5, $6, $7, $8, NOW(), $9)`;
await executeQuery(insertQuery, [
progressOutputId,
projectShardId,
projectId,
projectName,
scheduleRevisionId,
cloudStoragePath,
outputType,
outputHierarchyLevel,
true
]);
return progressOutputId;
}
/** Stable id so e2e can replace the same mapping artefact across runs. */
const E2E_PROJECT_ROOM_LEVEL_MAPPING_ARTEFACT_ID = "f111e2e0-e2e0-42e0-8e2e-000000000001";
const E2E_PROJECT_ROOM_LEVEL_MODEL_VERSION_ID = "8d4ed424-05c4-4a17-a42d-94f1a6973dc8";
/**
* Registers (or replaces) a ModelArtefact row so `getMappedArtefactByProjectId` returns
* project-room-level-mapping parquet at the given blob path (full `container/path` as returned by uploadCloudFile).
*/
export async function ensureProjectRoomLevelMappingArtefactForE2e(
projectId: string,
fullCloudStoragePath: string,
): Promise<void> {
const projectShardId = await getProjectShardId(projectId);
const userFileId = "3efb7809-dd1b-4344-aa11-0b549579bb60";
await executeQuery(
`DELETE FROM xyz."ModelVersionArtefactMapping" WHERE "ModelArtefactId" = $1 AND "ProjectShardId" = $2`,
[E2E_PROJECT_ROOM_LEVEL_MAPPING_ARTEFACT_ID, projectShardId],
);
await executeQuery(
`DELETE FROM xyz."ModelArtefact" WHERE "ModelArtefactId" = $1 AND "ProjectShardId" = $2`,
[E2E_PROJECT_ROOM_LEVEL_MAPPING_ARTEFACT_ID, projectShardId],
);
const insertArtefact = `
INSERT INTO xyz."ModelArtefact" (
"ModelArtefactId",
"ProjectShardId",
"UserFileId",
"OutputFormat",
"FileName",
"FileExtension",
"CloudStoragePath",
"ArtefactHash",
"IsClientFacing",
"ConverterImageName",
"CreatedBy",
"FileSizeBytes",
"OutputContent",
"IsProjectScoped"
) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14)
`;
await executeQuery(insertArtefact, [
E2E_PROJECT_ROOM_LEVEL_MAPPING_ARTEFACT_ID,
projectShardId,
userFileId,
"parquet",
"project-room-level-mapping",
"parquet",
fullCloudStoragePath,
"e2e-room-level-mapping-hash",
true,
"e2e-converter",
"E2E-Test",
1024,
"project-room-level-mapping",
false,
]);
const insertMapping = `
INSERT INTO xyz."ModelVersionArtefactMapping" ("InsertedOn", "ProjectShardId", "ModelArtefactId", "ModelVersionId")
VALUES (NOW(), $1, $2, $3)
`;
await executeQuery(insertMapping, [
projectShardId,
E2E_PROJECT_ROOM_LEVEL_MAPPING_ARTEFACT_ID,
E2E_PROJECT_ROOM_LEVEL_MODEL_VERSION_ID,
]);
}
export const E2E_CLIENT_ELEMENT_METAS_ARTEFACT_ID = "c111e2e0-e2e0-42e0-8e2e-000000000002";
/**
* Registers (or replaces) a ModelArtefact row for `client-element-metas` parquet so
* `getElementIdsForModel` in models.service can read element IDs at delete time.
*/
export async function ensureClientElementMetasArtefactForE2e(
projectId: string,
modelVersionId: string,
fullCloudStoragePath: string,
): Promise<void> {
const projectShardId = await getProjectShardId(projectId);
const userFileId = "3efb7809-dd1b-4344-aa11-0b549579bb60";
await executeQuery(
`DELETE FROM xyz."ModelVersionArtefactMapping" WHERE "ModelArtefactId" = $1 AND "ProjectShardId" = $2`,
[E2E_CLIENT_ELEMENT_METAS_ARTEFACT_ID, projectShardId],
);
await executeQuery(
`DELETE FROM xyz."ModelArtefact" WHERE "ModelArtefactId" = $1 AND "ProjectShardId" = $2`,
[E2E_CLIENT_ELEMENT_METAS_ARTEFACT_ID, projectShardId],
);
await executeQuery(
`INSERT INTO xyz."ModelArtefact" (
"ModelArtefactId", "ProjectShardId", "UserFileId", "OutputFormat",
"FileName", "FileExtension", "CloudStoragePath", "ArtefactHash",
"IsClientFacing", "ConverterImageName", "CreatedBy", "FileSizeBytes",
"OutputContent", "IsProjectScoped"
) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14)`,
[
E2E_CLIENT_ELEMENT_METAS_ARTEFACT_ID,
projectShardId,
userFileId,
"parquet",
"client-element-metas",
"parquet",
fullCloudStoragePath,
"e2e-element-metas-hash",
true,
"e2e-converter",
"E2E-Test",
1024,
"client-element-metas",
false,
],
);
await executeQuery(
`INSERT INTO xyz."ModelVersionArtefactMapping" ("InsertedOn", "ProjectShardId", "ModelArtefactId", "ModelVersionId")
VALUES (NOW(), $1, $2, $3)`,
[projectShardId, E2E_CLIENT_ELEMENT_METAS_ARTEFACT_ID, modelVersionId],
);
}
export async function cleanupClientElementMetasArtefactForE2e(projectId: string): Promise<void> {
const projectShardId = await getProjectShardId(projectId);
await executeQuery(
`DELETE FROM xyz."ModelVersionArtefactMapping" WHERE "ModelArtefactId" = $1 AND "ProjectShardId" = $2`,
[E2E_CLIENT_ELEMENT_METAS_ARTEFACT_ID, projectShardId],
);
await executeQuery(
`DELETE FROM xyz."ModelArtefact" WHERE "ModelArtefactId" = $1 AND "ProjectShardId" = $2`,
[E2E_CLIENT_ELEMENT_METAS_ARTEFACT_ID, projectShardId],
);
}
export async function createScheduleActivityResourceMapping(
projectId: string,
scheduleRevisionId: string,
activityId: string,
taskRsrcId: number,
taskId: number,
resourceType: string
) {
const projectShardId = await getProjectShardId(projectId);
const scheduleActivityResourceMappingId = uuidv4();
const insertQuery = `
INSERT INTO xyz."ScheduleActivityResourceMapping" ("ScheduleActivityResourceMappingId", "ScheduleRevisionId",
"ProjectShardId",
"ActivityId", "TaskRsrcId", "TaskId", "ResourceType")
VALUES ($1, $2, $3, $4, $5, $6, $7)
`;
await executeQuery(insertQuery, [
scheduleActivityResourceMappingId,
scheduleRevisionId,
projectShardId,
activityId,
taskRsrcId,
taskId,
resourceType
]);
return scheduleActivityResourceMappingId;
}
export async function createScheduleNonWorkingDay(
projectId: string,
scheduleRevisionId: string,
scheduleCalendarId: string,
nonWorkingDate: string,
isoDayIndex: number,
isPublicHoliday: boolean
) {
const projectShardId = await getProjectShardId(projectId);
const scheduleNonWorkingDayId = uuidv4();
const insertQuery = `
INSERT INTO xyz."ScheduleNonWorkingDay" ("ScheduleNonWorkingDayId", "ProjectShardId", "ScheduleRevisionId",
"ScheduleCalendarId", "NonWorkingDate", "IsoDayIndex",
"IsPublicHoliday")
VALUES ($1, $2, $3, $4, $5, $6, $7)
`;
await executeQuery(insertQuery, [
scheduleNonWorkingDayId,
projectShardId,
scheduleRevisionId,
scheduleCalendarId,
nonWorkingDate,
isoDayIndex,
isPublicHoliday
]);
return scheduleNonWorkingDayId;
}
export async function setupRoomCapturePoints(projectId: string, upsertedBy: string, roomCapturePoints: RoomCapturePoint[]) {
const mappedRoomCapturePoints = roomCapturePoints.map(point => ({
RoomCapturePointId: point.roomCapturePointId,
ModelRoomId: point.modelRoomId,
ModelLevelId: point.modelLevelId,
UserCapturePointId: point.userCapturePointId,
XMeters: point.xMeters,
YMeters: point.yMeters,
ZMeters: point.zMeters,
CreatedFrom: point.createdFrom,
CreatedBy: upsertedBy
}));
const roomCapturePointsJson = JSON.stringify(mappedRoomCapturePoints);
const callProcedureQuery = `CALL xyz."usp_UpsertRoomCapturePoints"($1, $2, $3)`;
try {
await executeQuery(callProcedureQuery, [projectId, upsertedBy, roomCapturePointsJson]);
} catch (error) {
console.log(error);
}
}
export async function setupTestConflictCoordinate(projectId: string, coordinate: string): Promise<{
coordinateConflictId: string;
projectId: string;
coordinateId: string;
conflictType: string;
pointId: string;
xCoordinate: number;
yCoordinate: number;
zCoordinate: number;
}> {
const conflictType = "Point_Id";
const coordinateId = coordinate;
const pointId = "point";
const xCoordinate = Math.random() * 1000;
const yCoordinate = Math.random() * 1000;
const zCoordinate = Math.random() * 1000;
return await setupConflictCoordinate(projectId, conflictType, coordinateId, pointId, xCoordinate, yCoordinate, zCoordinate, "unknown");
}
export async function getRoomCapturePointByRoomCapturePointId(roomCapturePointId: string) {
const selectQuery = 'SELECT * FROM xyz."RoomCapturePoint" WHERE "RoomCapturePointId" = $1';
const queryResult = await executeQuery(selectQuery, [roomCapturePointId]);
return queryResult.rows;
}
export async function deleteRoomCapturePointByUserCapturePointId(userCapturePointId: string) {
const callProcedureQuery = `DELETE
FROM xyz."RoomCapturePoint"
WHERE "UserCapturePointId" = $1`;
await executeQuery(callProcedureQuery, [userCapturePointId]);
}
export async function getMongoProjectIdByProjectId(projectId: string) {
const selectQuery = 'SELECT * FROM xyz."Project" WHERE "ProjectId" = $1';
const queryResult = await executeQuery(selectQuery, [projectId]);
return queryResult.rows[0].MongoProjectId;
}
export async function getCdeUserByCdeEmailId(cdeEmailId: string) {
const selectQuery = 'SELECT * FROM xyz."CdeUser" WHERE "CdeUserEmail" = $1';
const queryResult = await executeQuery(selectQuery, [cdeEmailId]);
return queryResult.rows[0];
}
export async function getCdeUserByXyzEmailId(xyzEmailId: string) {
const selectQuery = 'SELECT * FROM xyz."CdeUser" WHERE "XyzUserEmail" = $1';
const queryResult = await executeQuery(selectQuery, [xyzEmailId]);
return queryResult.rows[0];
}
export async function insertProjectSyncConfig(cdeProjectId: string, projectId: string, syncConfig: any) {
const projectShardId = await getProjectShardId(projectId);
const insertQuery = 'INSERT INTO xyz."CdeSyncConfig" ' +
'("ProjectShardId","SyncConfig", "CdeProjectId","CreatedBy","SyncActive","CdeSyncConfigId")' +
' VALUES ($1,$2,$3,$4,$5, $6) ON CONFLICT ("CdeProjectId", "ProjectShardId") DO UPDATE ' +
' SET ' +
'"SyncConfig" = COALESCE(EXCLUDED."SyncConfig", xyz."CdeSyncConfig"."SyncConfig") ;';
await executeQuery(insertQuery, [projectShardId, syncConfig, cdeProjectId, "E2E-Test", "TRUE", cdeProjectId]);
}
export async function createCdeProject(cdeProjectId: string, cdeAccountId: string, thirdPartyCdeProjectId: string,
cdeProjectName: string) {
try {
const selectQuery = 'SELECT * FROM xyz."CdeProject" where "ThirdPartyCdeProjectId" = $1';
const queryResult = await executeQuery(selectQuery, [thirdPartyCdeProjectId]);
if (queryResult.rows.length > 0) {
console.log("createCdeProject: createCdeProject already exists");
} else {
const insertQuery = `INSERT INTO xyz."CdeProject" ("CdeProjectId", "CdeAccountId", "ThirdPartyCdeProjectId",
"CdeProjectName", "CdeType", "CreatedBy")
VALUES ($1, $2, $3, $4, $5, $6) ON CONFLICT
ON CONSTRAINT "CdeProject_pkey" DO
UPDATE SET
"ThirdPartyCdeProjectId" = EXCLUDED."ThirdPartyCdeProjectId";`
await executeQuery(insertQuery, [cdeProjectId, cdeAccountId, thirdPartyCdeProjectId,
cdeProjectName, CdeTypes.ACC, "Autodesk-e2e-test"]);
}
} catch (err) {
console.error("Error while createCdeProject ", err);
throw err;
}
}
export async function createOrUpdateCdeUser(XyzUserEmail: string, cdePlatformName: string, futureExpiryForToken: boolean) {
const autodeskToken = await createAuthToken("autodesk-user", [], futureExpiryForToken)
const accessToken = encryptCdeToken(autodeskToken.token)
const refreshToken = encryptCdeToken("dummy refresh Token");
const thirdPartyCdeUserId = "third_party_cde_user_id"
const cdeEmailId = "cde_email_id@cde.com"
let queryResult: any;
try {
const insertQuery = `
INSERT INTO xyz."CdeUser" ("ThirdPartyCdeUserId", "CdeUserEmail", "XyzUserEmail", "AccessToken",
"RefreshToken",
"AuthGranted", "CreatedBy", "CdePlatformName")
VALUES ($1, $2, $3, $4, $5, $6, $7, $8) ON CONFLICT ("CdeUserEmail", "CdePlatformName") DO
UPDATE
SET
"AccessToken" = EXCLUDED."AccessToken", "RefreshToken" = EXCLUDED."RefreshToken" RETURNING *`;
queryResult = await executeQuery(insertQuery, [thirdPartyCdeUserId, cdeEmailId, XyzUserEmail, accessToken, refreshToken, true, XyzUserEmail, cdePlatformName]);
console.log("createOrUpdateCdeUser called: " + XyzUserEmail, cdePlatformName);
} catch (err) {
console.error("Error while creating OrUpdateCdeUser", err);
}
return queryResult.rows[0];
}
export async function setupDevice(deviceId: string, deviceName: string, deviceCode: string, isActivated: boolean, status: string, deviceUdi: string, createdBy: string) {
const insertQuery = `INSERT INTO xyz."Device" ("DeviceId", "DeviceName", "DeviceCode", "IsActivated", "Status",
"Udi", "CreatedBy")
VALUES ($1, $2, $3, $4, $5, $6, $7) ON CONFLICT ("DeviceId") DO NOTHING;`;
await executeQuery(insertQuery, [deviceId, deviceName, deviceCode, isActivated, status, deviceUdi, createdBy]);
}
export async function updateDeviceTenant(deviceId: string, tenantId: string) {
const updateQuery = `UPDATE xyz."Device"
SET "TenantId" = $1
WHERE "DeviceId" = $2`;
await executeQuery(updateQuery, [tenantId, deviceId]);
}
export async function createDeviceProjectMapping(deviceId: string, projectId: string) {
const shardId = await getProjectShardId(projectId)
const insertQuery = `INSERT INTO xyz."DeviceProjectMapping" ("DeviceId", "ProjectShardId", "CreatedBy")
VALUES ($1, $2, 'e2e-Test') ON CONFLICT ("ProjectShardId", "DeviceId") DO NOTHING;`;
await executeQuery(insertQuery, [deviceId, shardId]);
}
export async function getDeviceById(deviceId: string) {
const selectQuery = ` SELECT *
from xyz."Device"
WHERE "DeviceId" = $1`;
const queryResult = await executeQuery(selectQuery, [deviceId]);
return queryResult.rows[0];
}
export async function getDeviceMappingsById(deviceId: string) {
const selectQuery = ` SELECT *
from xyz."DeviceProjectMapping"
WHERE "DeviceId" = $1`;
const queryResult = await executeQuery(selectQuery, [deviceId]);
return queryResult.rows;
}
export async function attachDeviceUdiToPhoto(deviceUdi: string, fileReferenceId: string) {
const updateQuery = `UPDATE xyz."Photo"
SET "DeviceUdi" = $1
WHERE "FileReferenceId" = $2`;
await executeQuery(updateQuery, [deviceUdi, fileReferenceId]);
}
export async function attachDeviceUdiToVideo(deviceUdi: string, fileReferenceId: string) {
const updateQuery = `UPDATE xyz."Video"
SET "DeviceUdi" = $1
WHERE "FileReferenceId" = $2`;
await executeQuery(updateQuery, [deviceUdi, fileReferenceId]);
}
export async function setupDevices(createdBy: string, devices: Device[]) {
const mappedDevices = devices.map(device => ({
DeviceId: device.deviceId,
DeviceName: device.deviceName,
DeviceCode: device.deviceCode,
IsActivated: device.isActivated,
Status: device.status,
Udi: device.deviceUdi,
CreatedBy: createdBy
}));
const insertQuery = `
INSERT INTO xyz."Device" ("DeviceId", "DeviceName", "DeviceCode", "IsActivated", "Status", "Udi", "CreatedBy")
VALUES ($1, $2, $3, $4, $5, $6, $7) ON CONFLICT ("DeviceId") DO NOTHING;
`;
try {
for (const device of mappedDevices) {
await executeQuery(insertQuery, [
device.DeviceId,
device.DeviceName,
device.DeviceCode,
device.IsActivated,
device.Status,
device.Udi,
device.CreatedBy
]);
}
} catch (error) {
console.error(error);
}
}
export async function setupTimezones() {
const insertQuery = `
INSERT INTO xyz."Timezone" ("TimezoneId", "Timezone")
VALUES ($1, $2) ON CONFLICT ("TimezoneId") DO NOTHING;
`;
const mockTimezones = [
{
timezoneId: "aff125a9-4df0-483b-b839-3c437f225cdc",
timezone: "Europe/London"
},
{
timezoneId: "bff125a9-4df0-483b-b839-3c437f225cdc",
timezone: "Europe/Paris"
}
];
try {
// Postgres repo inserts defaults, removing so the test is easier to maintain.
await executeQuery('DELETE FROM xyz."Timezone";', []);
for (const tz of mockTimezones) {
await executeQuery(insertQuery, [
tz.timezoneId,
tz.timezone
]);
}
} catch (error) {
console.error(error);
}
}
export async function setupCurrencies() {
const insertQuery = `
INSERT INTO xyz."Currency" ("CurrencyId", "Name", "Code")
VALUES ($1, $2, $3) ON CONFLICT ("CurrencyId") DO NOTHING;
`;
const mockCurrencies = [
{
currencyId: "123e4567-e89b-12d3-a456-426614174000",
currencyName: "United States Dollar",
currencyCode: "USD"
},
{
currencyId: "223e4567-e89b-12d3-a456-426614174001",
currencyName: "Euro",
currencyCode: "EUR"
}
];
try {
// Postgres repo inserts defaults, removing so the test is easier to maintain.
await executeQuery('DELETE FROM xyz."Currency";', []);
for (const currency of mockCurrencies) {
await executeQuery(insertQuery, [
currency.currencyId,
currency.currencyName,
currency.currencyCode
]);
}
} catch (error) {
//console.error(error);
}
}
export async function getCurrencyByCurrencyCode(currencyCode: string) {
const selectQuery = ` SELECT *
from xyz."Currency"
WHERE "Code" = $1`;
const queryResult = await executeQuery(selectQuery, [currencyCode]);
return queryResult.rows[0];
}
export async function setupPortfolios(tenantId: string): Promise<{ defaultPortfolioId: string; otherPortfolioId: string }> {
const getDefaultQuery = `SELECT "PortfolioId" FROM xyz."Portfolio" WHERE "TenantId" = $1 AND "IsDefaultForTenant" = TRUE AND "IsDeleted" = FALSE LIMIT 1`;
let defaultPortfolioId: string;
try {
const defaultResult = await executeQuery(getDefaultQuery, [tenantId]);
if (defaultResult.rows.length > 0) {
defaultPortfolioId = defaultResult.rows[0].PortfolioId;
} else {
defaultPortfolioId = uuidv4();
const insertDefault = `INSERT INTO xyz."Portfolio" ("PortfolioId", "Name", "Description", "TenantId", "CurrencyId", "IsDefaultForTenant", "CreatedBy")
VALUES ($1, $2, $3, $4, $5, $6, $7) ON CONFLICT ("PortfolioId") DO NOTHING;`;
await executeQuery(insertDefault, [defaultPortfolioId, "E2E Default Portfolio", "Default for tenant", tenantId, null, true, "e2e-test"]);
}
const otherPortfolioId = uuidv4();
const insertOther = `INSERT INTO xyz."Portfolio" ("PortfolioId", "Name", "Description", "TenantId", "CurrencyId", "IsDefaultForTenant", "CreatedBy")
VALUES ($1, $2, $3, $4, $5, $6, $7) ON CONFLICT ("PortfolioId") DO NOTHING;`;
await executeQuery(insertOther, [otherPortfolioId, "E2E Other Portfolio", "Non-default", tenantId, null, false, "e2e-test"]);
return { defaultPortfolioId, otherPortfolioId };
} catch (err) {
console.error("setupPortfolios error", err);
throw err;
}
}
export async function setProjectIconUrl(projectId: string, iconUrl: string) {
const updateQuery = `UPDATE xyz."Project" SET "IconUrl" = $1 WHERE "ProjectId" = $2`;
await executeQuery(updateQuery, [iconUrl, projectId]);
}
export async function setProjectPortfolio(projectId: string, portfolioId: string) {
const updateQuery = `UPDATE xyz."Project" SET "PortfolioId" = $1, "IsPortfolioEnabled" = TRUE WHERE "ProjectId" = $2`;
await executeQuery(updateQuery, [portfolioId, projectId]);
}
export async function setProjectProgress(projectId: string) {
const updateProjectQuery = `UPDATE xyz."Project" SET "ProgressWeightingId" = '2d3d42f1-d112-42cb-8885-0bc544a4ea2a' WHERE "ProjectId" = $1`;
await executeQuery(updateProjectQuery, [projectId]);
// Insert progress for current date and multiple past months so fn_GetPortfolioProjectData can
// populate both latest progress and intervalProgress (e.g. monthly SPI aggregation).
const insertReportQuery = `INSERT INTO reporting."ProjectProgress"("ProjectShardId", "CalendarDate",
"TotalPlannedLaborUnits", "TotalLinkedElements",
"LaborWeightedPlannedProgress",
"LaborWeightedActualProgress",
"ElementWeightedPlannedProgress",
"ElementWeightedActualProgress",
"LaborWeightedSpi", "ElementWeightedSpi",
"ProgressOutputId", "DagsterRunId")
SELECT p."ProjectShardId",
d."CalendarDate",
1000.00,
50,
0.25,
0.20,
0.30,
0.22,
0.80,
0.73,
'e2e00000-0000-0000-0000-000000000001'::UUID,
'e2e-dashboard-test-run'
FROM xyz."Project" p
CROSS JOIN (
SELECT CURRENT_DATE AS "CalendarDate"
UNION ALL SELECT (date_trunc('month', CURRENT_DATE) - INTERVAL '1 month')::date
UNION ALL SELECT (date_trunc('month', CURRENT_DATE) - INTERVAL '2 months')::date
UNION ALL SELECT (date_trunc('month', CURRENT_DATE) - INTERVAL '3 months')::date
) d
WHERE p."ProjectId" = $1
ON CONFLICT ("ProjectShardId", "CalendarDate") DO NOTHING`;
await executeQuery(insertReportQuery, [projectId]);
}
export async function getProjectIdByTenantId(tenantId: string): Promise<string | null> {
const selectQuery = `SELECT "ProjectId" FROM xyz."Project" WHERE "TenantId" = $1 AND "IsDeleted" = FALSE LIMIT 1`;
const result = await executeQuery(selectQuery, [tenantId]);
return result.rows.length > 0 ? result.rows[0].ProjectId : null;
}
export async function linkProjectToProgressWeighting(projectId: string, progressWeightingId: string) {
const updateQuery = `UPDATE xyz."Project"
SET "ProgressWeightingId" = $1
WHERE "ProjectId" = $2`;
await executeQuery(updateQuery, [progressWeightingId, projectId]);
console.log(`Linked project ${projectId} to progress weighting ${progressWeightingId}`);
}
export async function getActivitiesProgress(projectId: string, activityId: string, calendarDate: string) {
const projectShardId = await getProjectShardId(projectId);
const selectQuery = `SELECT *
FROM xyz."ActivityProgress"
WHERE "ProjectShardId" = $1
AND "ActivityId" = $2
AND DATE ("CalendarDate") = DATE ($3)`;
const queryResult = await executeQuery(selectQuery, [projectShardId, activityId, calendarDate]);
return queryResult.rows;
}
export async function getActivitiesProgressHistory(projectId: string, activityId: string, calendarDate: string) {
const projectShardId = await getProjectShardId(projectId);
const selectQuery = `SELECT *
FROM xyz."ActivityProgressHistory"
WHERE "ProjectShardId" = $1
AND "ActivityId" = $2
AND DATE ("CalendarDate") = DATE ($3)
ORDER BY "InsertedOn" DESC`;
const queryResult = await executeQuery(selectQuery, [projectShardId, activityId, calendarDate]);
return queryResult.rows;
}
export async function getAllActivitiesProgressForDate(projectId: string, calendarDate: string) {
const projectShardId = await getProjectShardId(projectId);
const selectQuery = `SELECT *
FROM xyz."ActivityProgress"
WHERE "ProjectShardId" = $1
AND DATE ("CalendarDate") = DATE ($2)`;
const queryResult = await executeQuery(selectQuery, [projectShardId, calendarDate]);
return queryResult.rows;
}
export async function createModelWithVersion(
projectId: string,
modelId: string,
parentModelFolderId: string | null,
modelName: string,
isCurrentVersion: boolean
): Promise<{ modelId: string; modelVersionId: string }> {
const projectShardId = await getProjectShardId(projectId);
const modelVersionId = uuidv4();
const userFileId = await createUserFile(projectId);
const insertModelQuery = `
INSERT INTO xyz."Model" ("InsertedOn", "ModelId", "ProjectShardId", "ParentModelFolderId",
"ModelName", "Discipline", "IsDeleted")
VALUES (NOW(), $1, $2, $3, $4, 'E2E-Test-Discipline', FALSE)
ON CONFLICT ("ProjectShardId", "ModelId") DO NOTHING
`;
await executeQuery(insertModelQuery, [
modelId,
projectShardId,
parentModelFolderId,
modelName
]);
const insertVersionQuery = `
INSERT INTO xyz."ModelVersion" ("ModelVersionId", "ProjectShardId", "ModelId", "UserFileId",
"VersionName", "VersionNumber", "IsCurrentVersion", "InsertedOn")
VALUES ($1, $2, $3, $4, $5, $6, $7, NOW())
ON CONFLICT ("ProjectShardId", "ModelVersionId") DO NOTHING
`;
await executeQuery(insertVersionQuery, [
modelVersionId,
projectShardId,
modelId,
userFileId,
isCurrentVersion ? "Current Version" : "Old Version",
isCurrentVersion ? 2 : 1,
isCurrentVersion
]);
return { modelId, modelVersionId };
}
export async function deleteModelAndVersions(projectId: string, modelId: string): Promise<void> {
const projectShardId = await getProjectShardId(projectId);
// Collect artefact IDs linked to this model's versions before removing the mappings.
const artefactResult = await executeQuery(
`SELECT DISTINCT "ModelArtefactId" FROM xyz."ModelVersionArtefactMapping"
WHERE "ProjectShardId" = $1
AND "ModelVersionId" IN (
SELECT "ModelVersionId" FROM xyz."ModelVersion"
WHERE "ProjectShardId" = $1 AND "ModelId" = $2
)`,
[projectShardId, modelId],
);
const artefactIds: string[] = artefactResult.rows.map((r: any) => r.ModelArtefactId);
await executeQuery(
`DELETE FROM xyz."ModelVersionArtefactMapping"
WHERE "ProjectShardId" = $1
AND "ModelVersionId" IN (
SELECT "ModelVersionId" FROM xyz."ModelVersion"
WHERE "ProjectShardId" = $1 AND "ModelId" = $2
)`,
[projectShardId, modelId],
);
if (artefactIds.length > 0) {
await executeQuery(
`DELETE FROM xyz."ModelArtefact"
WHERE "ProjectShardId" = $1
AND "ModelArtefactId" = ANY($2::uuid[])`,
[projectShardId, artefactIds],
);
}
await executeQuery(
`DELETE FROM xyz."ModelVersion" WHERE "ProjectShardId" = $1 AND "ModelId" = $2`,
[projectShardId, modelId],
);
await executeQuery(
`DELETE FROM xyz."Model" WHERE "ProjectShardId" = $1 AND "ModelId" = $2`,
[projectShardId, modelId],
);
}
export async function getModelById(projectId: string, modelId: string) {
const projectShardId = await getProjectShardId(projectId);
const selectQuery = `SELECT * FROM xyz."Model" WHERE "ProjectShardId" = $1 AND "ModelId" = $2`;
const queryResult = await executeQuery(selectQuery, [projectShardId, modelId]);
return queryResult.rows[0];
}
export async function getCommissioningWorkflowById(projectId: string, commissioningWorkflowId: string) {
const projectShardId = await getProjectShardId(projectId);
const selectQuery = `SELECT * FROM xyz."CommissioningWorkflow" WHERE "ProjectShardId" = $1 AND "CommissioningWorkflowId" = $2`;
const queryResult = await executeQuery(selectQuery, [projectShardId, commissioningWorkflowId]);
return queryResult.rows[0];
}
export async function getCommissioningWorkflowsByProject(projectId: string) {
const projectShardId = await getProjectShardId(projectId);
const selectQuery = `SELECT * FROM xyz."CommissioningWorkflow" WHERE "ProjectShardId" = $1 ORDER BY "InsertedOn" ASC`;
const queryResult = await executeQuery(selectQuery, [projectShardId]);
return queryResult.rows;
}
export async function getSystemTypeById(projectId: string, systemTypeId: string) {
const projectShardId = await getProjectShardId(projectId);
const selectQuery = `SELECT * FROM xyz."SystemType" WHERE "ProjectShardId" = $1 AND "SystemTypeId" = $2`;
const queryResult = await executeQuery(selectQuery, [projectShardId, systemTypeId]);
return queryResult.rows[0];
}
export async function getSystemTypesByProject(projectId: string) {
const projectShardId = await getProjectShardId(projectId);
const selectQuery = `SELECT * FROM xyz."SystemType" WHERE "ProjectShardId" = $1 ORDER BY "InsertedOn" ASC`;
const queryResult = await executeQuery(selectQuery, [projectShardId]);
return queryResult.rows;
}
export async function getAssetTypeById(projectId: string, assetTypeId: string) {
const projectShardId = await getProjectShardId(projectId);
const selectQuery = `SELECT * FROM xyz."AssetType" WHERE "ProjectShardId" = $1 AND "AssetTypeId" = $2`;
const queryResult = await executeQuery(selectQuery, [projectShardId, assetTypeId]);
return queryResult.rows[0];
}
export async function getAssetTypesByProject(projectId: string) {
const projectShardId = await getProjectShardId(projectId);
const selectQuery = `SELECT * FROM xyz."AssetType" WHERE "ProjectShardId" = $1 ORDER BY "InsertedOn" ASC`;
const queryResult = await executeQuery(selectQuery, [projectShardId]);
return queryResult.rows;
}
export async function cleanupProjectAssetTypes(projectId: string) {
const projectShardId = await getProjectShardId(projectId);
await executeQuery('DELETE FROM xyz."AssetType" WHERE "ProjectShardId" = $1', [projectShardId]);
}