src/services/issues.service.tsmodifiedimport {query} from "../db/db";
import {LoggerFactory} from "../util/logger";
import {BadRequestError, DatabaseError, DB_ConstraintViolationError, NotFoundError} from "../types/errortypes";
import {
Issue,
IssueActivityCategory,
IssueDetailed,
IssueFileReference,
IssueGlobalParameter,
PagingQueryParam
} from "../models/ingress";
import {getLoggedInUsername} from "../util/username";
import {toArray} from "../util/array.util";
import {getMappedProjectByProjectId, getProjectLocations} from "./projects.service";
import {generateTokenisedBlobDownloadUrl} from "../util/azure.util";
import {getFromCacheOrIam} from "../clients/iam.client.helper";
const logger = LoggerFactory("IssuesService")
const INSERT_ISSUE_QUERY = `SELECT *
FROM xyz."fn_InsertIssue"($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15,
$16, $17, $18, $19, $20, $21, $22, $23, $24, $25, $26, $27, $28,
$29)`;
const GET_ISSUE_QUERY = `SELECT *
FROM xyz."fn_GetIssue"($1, $2)`;
const GET_ISSUE_LIST_QUERY = `SELECT *
FROM xyz."fn_GetIssueList"($1, $2, $3, $4, $5)`;
const GET_DETAILED_ISSUE_LIST_QUERY = `SELECT *
FROM xyz."fn_GetIssueListDetailed"($1, $2, $3, $4, $5)`;
const UPDATE_ISSUE_QUERY = `SELECT *
FROM xyz."fn_UpdateIssue"($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15,
$16, $17, $18, $19, $20, $21, $22, $23, $24, $25, $26, $27, $28,
$29, $30)`;
const SOFT_DELETE_ISSUE_QUERY = `CALL xyz."usp_SoftDeleteIssue"($1, $2)`;
const GET_ISSUE_TYPES_QUERY = `SELECT *
FROM xyz."fn_GetIssueTypes"($1)`;
const UPDATE_ISSUE_TYPE_QUERY = `CALL xyz."usp_UpdateIssueType"($1, $2, $3)`;
const GET_ISSUE_STATUSES_QUERY = `SELECT *
FROM xyz."fn_GetIssueStatuses"($1)`;
const GET_ISSUE_CUSTOM_ATTRIBUTES_QUERY = `SELECT *
FROM xyz."fn_GetIssueCustomAttributes"($1)`;
const GET_ISSUE_ACTIVITY_CATEGORIES_QUERY = `SELECT *
FROM xyz."fn_GetIssueActivityCategories"($1, $2)`;
const GET_GLOBAL_ISSUE_PARAMETERS_QUERY = `SELECT *
FROM xyz."fn_GetGlobalIssueParametersList"()`;
export async function createIssue(projectId: string, issue: Issue): Promise<Issue> {
try {
const platform = issue.platform || "XYZ"; // Assume created from XYZ if not provided
const projectDetails = await getMappedProjectByProjectId(projectId);
const result = await query(INSERT_ISSUE_QUERY, [
projectId,
issue.title,
toArray(issue.modelId),
issue.modelElementId,
issue.modelRoomId,
issue.reporterEmail,
issue.assigneeEmail,
issue.assigneeType,
issue.issueTypeId,
issue.issueStatusId,
issue.description,
issue.issueLocationId,
issue.dueDate,
issue.xMeters,
issue.yMeters,
issue.zMeters,
issue.hardHatPosition,
issue.issueSeverityCategoryId,
false,
platform,
JSON.stringify(issue.customAttributes),
issue.reporterEmail,
issue.locationDetails,
issue.issueStageId,
issue.issueOutcomeId,
issue.company,
issue.observedDiscrepancy,
issue.closureReason,
issue.cost
]);
logger.info(`Created new issue with issueId: '${result.rows[0].IssueId}'.`);
return mapRowToIssue(projectDetails.mongoTenantId, result.rows[0]);
} catch (err) {
if (err instanceof Error && err.message.includes("No project found for ProjectId")) {
throw new NotFoundError(`Project with ProjectId: ${projectId} not found.`);
}
else if (err instanceof DB_ConstraintViolationError) {
logger.error(`Constraint violation error while creating Issue: ${err.message}`);
throw new BadRequestError(err.message);
} else {
logger.error("Error while creating Issue", err);
throw err;
}
}
}
export async function getIssue(projectId: string, issueId: string, simple: boolean): Promise<IssueDetailed> {
try {
const projectDetails = await getMappedProjectByProjectId(projectId);
const result = await query(GET_ISSUE_QUERY, [projectId, issueId]);
if (result.rows.length === 0) {
throw new NotFoundError(`Issue with IssueId: ${issueId} and ProjectId: ${projectId} not found.`);
}
if (simple) {
return await mapRowToIssue(projectDetails.mongoTenantId, result.rows[0])
} else
return {
...await mapRowToIssue(projectDetails.mongoTenantId, result.rows[0]),
fileReferences: await Promise.all(result.rows[0].FileReferences.map((fileReference: any) => mapRowToIssueFileReference(fileReference))),
}
} catch (err) {
if (err instanceof Error && err.message.includes("No project found for ProjectId")) {
throw new NotFoundError(`Project with ProjectId: ${projectId} not found.`);
} else {
logger.error("Error while retrieving Issue", err);
throw err;
}
}
}
export async function getIssueList(projectId: string, paging: PagingQueryParam, includeDeleted: boolean, lastSyncDateTime?: string, typeNames?: string[]): Promise<Issue[]> {
try {
const projectDetails = await getMappedProjectByProjectId(projectId);
const params: IssueListQueryParam[] = [projectId, paging.lastFetchedIndexId, paging.size, lastSyncDateTime ?? null, includeDeleted];
const listQuery = applyTypeNameFilter(GET_ISSUE_LIST_QUERY, params, typeNames);
const {rows} = await query(listQuery, params);
return await Promise.all(rows.map(async (row: any) => ({
...await mapRowToIssue(projectDetails.mongoTenantId, row),
indexId: row.Id,
...(lastSyncDateTime ? { isDeleted: row.IsDeleted } : {})
})));
} catch (err) {
if (err instanceof Error && err.message.includes("No project found for ProjectId")) {
throw new NotFoundError(`Project with ProjectId: ${projectId} not found.`);
} else {
logger.error(`Error fetching Issue list for ProjectId: ${projectId}`, err);
throw new DatabaseError(`Error fetching Issue list for ProjectId: ${projectId}`);
}
}
}
export async function getDetailedIssueList(projectId: string, paging: PagingQueryParam, includeDeleted: boolean, lastSyncDateTime?: string, typeNames?: string[]): Promise<IssueDetailed[]> {
try {
const projectDetails = await getMappedProjectByProjectId(projectId);
const params: IssueListQueryParam[] = [projectId, paging.lastFetchedIndexId, paging.size, lastSyncDateTime ?? null, includeDeleted];
const listQuery = applyTypeNameFilter(GET_DETAILED_ISSUE_LIST_QUERY, params, typeNames);
const {rows} = await query(listQuery, params);
return await Promise.all(rows.map(async (row:any) => ({
...await mapRowToDetailedIssue(projectDetails.mongoTenantId, row),
indexId: row.Id,
...(lastSyncDateTime ? { isDeleted: row.IsDeleted } : {})
})));
} catch (err) {
if (err instanceof Error && err.message.includes("No project found for ProjectId")) {
throw new NotFoundError(`Project with ProjectId: ${projectId} not found.`);
} else {
logger.error(`Error fetching Detailed Issue list for ProjectId: ${projectId}`, err);
throw new DatabaseError(`Error fetching Detailed Issue list for ProjectId: ${projectId}`);
}
}
}
export async function updateIssue(issueId: string, projectId: string, issue: Partial<Issue>): Promise<Issue> {
try {
const updatedBy = getLoggedInUsername()
const projectDetails = await getMappedProjectByProjectId(projectId);
const result = await query(UPDATE_ISSUE_QUERY, [
issueId,
projectId,
issue.title,
toArray(issue.modelId),
issue.modelElementId,
issue.modelRoomId,
issue.issueRaisedOn,
issue.reporterEmail,
issue.assigneeEmail,
issue.assigneeType,
issue.issueTypeId,
issue.issueStatusId,
issue.description,
issue.issueLocationId,
issue.locationDetails,
issue.dueDate,
issue.xMeters,
issue.yMeters,
issue.zMeters,
issue.issueSeverityCategoryId,
issue.hardHatPosition,
JSON.stringify(issue.customAttributes),
updatedBy,
issue.platform,
issue.issueStageId,
issue.issueOutcomeId,
issue.company,
issue.observedDiscrepancy,
issue.closureReason,
issue.cost
]);
if (result.rows.length === 0) {
throw new NotFoundError(`Issue with IssueId: ${issueId} and ProjectId: ${projectId} not found.`);
}
return await mapRowToIssue(projectDetails.mongoTenantId, result.rows[0]);
} catch (err) {
if (err instanceof Error && err.message.includes("No project found for ProjectId")) {
throw new NotFoundError(`Project with ProjectId: ${projectId} not found.`);
} else {
logger.error("Error while updating Issue", err);
throw err;
}
}
}
export async function deleteIssue(projectId: string, issueId: string) {
try {
await getIssue(projectId, issueId, true);
await query(SOFT_DELETE_ISSUE_QUERY, [projectId, issueId]);
} catch (err) {
if (err instanceof DB_ConstraintViolationError) {
logger.error("Constraint violation error while deleting Issue:", err.message);
throw new BadRequestError(err.message);
}
logger.error("Error while deleting Issue", err);
throw err;
}
}
export async function getIssueTypes(projectId: string): Promise<any[]> {
try {
const {rows} = await query(GET_ISSUE_TYPES_QUERY, [projectId]);
return rows;
} catch (err) {
if (err instanceof Error && err.message.includes("No project found for ProjectId")) {
throw new NotFoundError(`Project with ProjectId: ${projectId} not found.`);
} else {
logger.error(`Error fetching issue types for ProjectId: ${projectId}`, err);
throw new DatabaseError(`Error fetching issue types for ProjectId: ${projectId}`);
}
}
}
export async function updateIssueTypes(projectId: string, issueTypesNames: object) {
try {
const updatedBy = getLoggedInUsername();
await query(UPDATE_ISSUE_TYPE_QUERY, [projectId, JSON.stringify(issueTypesNames), updatedBy]);
} catch (err) {
if (err instanceof Error) {
if (err.message.includes("No project found for ProjectId")) {
throw new NotFoundError(`Project with ProjectId: ${projectId} not found.`);
} else if (err.message.includes("No matching issue types found for ProjectId")) {
throw new NotFoundError(`Project with ProjectId: ${projectId} doesn't have types to update.`);
}
}
logger.error("Error while updating issue type names", err);
throw err;
}
}
export async function getIssueStatuses(projectId: string): Promise<string[]> {
try {
const {rows} = await query(GET_ISSUE_STATUSES_QUERY, [projectId]);
return rows;
} catch (err) {
if (err instanceof Error && err.message.includes("No project found for ProjectId")) {
throw new NotFoundError(`Project with ProjectId: ${projectId} not found.`);
} else {
logger.error(`Error fetching issue status values for ProjectId: ${projectId}`, err);
throw new DatabaseError(`Error fetching issue status values for ProjectId: ${projectId}`);
}
}
}
async function getIssueCustomAttributes(projectId: string): Promise<string[]> {
try {
const {rows} = await query(GET_ISSUE_CUSTOM_ATTRIBUTES_QUERY, [projectId]);
return rows;
} catch (err) {
if (err instanceof Error && err.message.includes("No project found for ProjectId")) {
throw new NotFoundError(`Project with ProjectId: ${projectId} not found.`);
} else {
logger.error(`Error fetching issue custom attributes for ProjectId: ${projectId}`, err);
throw new DatabaseError(`Error fetching issue custom attributes for ProjectId: ${projectId}`);
}
}
}
export async function getIssueActivityCategories(projectId: string, issueId: string): Promise<IssueActivityCategory[]> {
try {
const {rows} = await query(GET_ISSUE_ACTIVITY_CATEGORIES_QUERY, [projectId, issueId]);
return rows.map(mapRowToIssueActivityCategory);
} catch (err) {
if (err instanceof Error && err.message.includes("No project found for ProjectId")) {
throw new NotFoundError(`Project with ProjectId: ${projectId} not found.`);
}
logger.error(`Error getting issue activity categories for projectId: ${projectId}, issueId: ${issueId}`, err);
throw err;
}
}
export async function getGlobalIssueParameters(): Promise<IssueGlobalParameter> {
// These are global for the platform, i.e. not project specific.
try {
const {rows} = await query(GET_GLOBAL_ISSUE_PARAMETERS_QUERY, []);
if (rows.length === 0) {
return {
issueSeverityCategories: [],
issueStages: [],
issueOutcomes: []
};
}
return mapGlobalIssueParametersRows(rows);
} catch (err) {
logger.error("Error fetching global issue parameters.", err);
throw new DatabaseError("Error fetching global issue parameters.");
}
}
function mapGlobalIssueParametersRows(rows: any[]): IssueGlobalParameter {
const result: IssueGlobalParameter = {
issueSeverityCategories: [],
issueStages: [],
issueOutcomes: []
};
rows.forEach(row => {
switch (row.ParameterTableName) {
case 'IssueSeverityCategory':
result.issueSeverityCategories.push({
issueSeverityCategoryId: row.ParameterId,
categoryName: row.ParameterValue
});
break;
case 'IssueStage':
result.issueStages.push({
issueStageId: row.ParameterId,
stage: row.ParameterValue
});
break;
case 'IssueOutcome':
result.issueOutcomes.push({
issueOutcomeId: row.ParameterId,
outcome: row.ParameterValue
});
break;
}
});
return result;
}
export async function getIssueParameters(projectId: string): Promise<any> {
try {
const issueTypes = getIssueTypes(projectId);
const issueStatuses = getIssueStatuses(projectId);
const issueCustomAttributes = getIssueCustomAttributes(projectId);
const issueGlobalParameters = getGlobalIssueParameters();
const issueLocations = getProjectLocations(projectId);
const parameters = {
issueTypes: (await issueTypes).map(mapIssueTypesToParameters),
issueStatuses: (await issueStatuses).map(mapIssueStatusesToParameters),
...await issueGlobalParameters,
issueLocations: (await issueLocations).map(location => ({
issueLocationId: location.issueLocationId,
location: location.location
})
),
issueCustomAttributes: (await issueCustomAttributes).map(mapIssueCustomAttributesToParameters)
};
return parameters;
} catch (err) {
if (err instanceof NotFoundError) {
throw err;
} else if (err instanceof Error && err.message.includes("No project found for ProjectId")) {
throw new NotFoundError(`Project with ProjectId: ${projectId} not found.`);
}
logger.error(`Error getting issue parameters for ${projectId}.`, err);
throw err;
}
}
type IssueListQueryParam = string | number | boolean | string[] | null | undefined;
// Appends a case-insensitive IssueType.TypeName filter (OR semantics) to an issue list
// query so the database performs the filtering. The supplied params array is mutated to
// bind the lowercased type names. Returns the query unchanged when no typeNames are
// supplied, preserving existing behaviour.
function applyTypeNameFilter(baseQuery: string, params: IssueListQueryParam[], typeNames?: string[]): string {
if (!typeNames || typeNames.length === 0) {
return baseQuery;
}
params.push(typeNames.map(name => name.trim().toLowerCase()));
return `${baseQuery} WHERE LOWER("TypeName") = ANY($${params.length}::text[])`;
}
async function mapRowToDetailedIssue(tenantId: any, row: any): Promise<IssueDetailed> {
return {
...await mapRowToIssue(tenantId, row),
fileReferences: await Promise.all(row.FileReferences.map((fileReference: any) => mapRowToIssueFileReference(fileReference))),
activityCategories: row.ActivityCategories.map(mapRowToIssueActivityCategory)
} as IssueDetailed;
}
async function mapRowToIssueFileReference(row: any): Promise<IssueFileReference> {
return {
indexId: row.Id,
fileReferenceId: row.FileReferenceId,
fileName: row.FileName,
fileExtension: row.FileExtension,
xyzDisplayName: row.XyzDisplayName,
description: row.Description,
fileHash: row.FileHash,
fileSizeBytes: Number(row.FileSizeBytes),
fullDownloadUrl: await generateTokenisedBlobDownloadUrl(row.CloudStoragePath),
smallImageDownloadUrl: await generateTokenisedBlobDownloadUrl(row.SmallImageCloudStoragePath),
insertedOn: row.InsertedOn,
createdBy: row.CreatedBy,
lastModifiedBy: row.LastModifiedBy,
lastModifiedOn: row.LastModifiedOn,
type: row.Type
};
}
async function mapRowToIssue(tenantId: any, row: any): Promise<Issue> {
return {
issueId: row.IssueId,
projectId: row.ProjectId,
modelId: row.ModelId,
modelElementId: row.ModelElementId,
modelRoomId: row.ModelRoomId,
title: row.Title,
description: row.Description,
issueNumber: row.IssueNumber,
issueStatusId: row.IssueStatusId,
issueStatus: row.IssueStatus,
issueStatusCode: row.IssueStatusCode,
issueStageId: row.IssueStageId,
issueStage: row.IssueStage,
issueOutcomeId: row.IssueOutcomeId,
issueOutcome: row.IssueOutcome,
issueRaisedOn: row.IssueRaisedOn,
reporterEmail: row.ReporterEmail,
reporterName: await getNameFromCacheOrIam(tenantId, row.ReporterEmail),
assigneeEmail: row.AssigneeEmail,
assigneeType: row.AssigneeType,
assigneeName: await getNameFromCacheOrIam(tenantId, row.AssigneeEmail),
issueTypeId: row.IssueTypeId,
typeName: row.TypeName,
issueLocationId: row.IssueLocationId,
location: row.Location,
dueDate: row.DueDate,
xMeters: row.XMeters,
yMeters: row.YMeters,
zMeters: row.ZMeters,
locationDetails: row.LocationDetails,
issueSeverityCategoryId: row.IssueSeverityCategoryId,
hardHatPosition: row.HardHatPosition,
company: row.Company,
observedDiscrepancy: row.ObservedDiscrepancy,
closureReason: row.ClosureReason,
cost: row.Cost,
lastModifiedBy: row.LastModifiedBy,
lastModifiedOn: row.LastModifiedOn,
issueSeverityCategoryName: row.IssueSeverityCategoryName,
customAttributes: JSON.parse(row.CustomAttributes ?? "[]"),
resolutionDate: row.IssueClosedOn
} as Issue;
};
function mapIssueTypesToParameters(row: any): any {
return {
issueTypeId: row.IssueTypeId,
displayName: row.DisplayName,
type: row.TypeName,
category: row.TypeCategory,
validForIssueCreate: row.ValidForIssueCreate,
validForIssueUpdate: row.ValidForIssueUpdate,
issueCustomAttributeIds: row.IssueCustomAttributeIds
}
};
function mapIssueStatusesToParameters(row: any): any {
return {
issueStatusId: row.IssueStatusId,
displayName: row.DisplayName,
status: row.Status,
validForIssueCreate: row.ValidForIssueCreate,
validForIssueUpdate: row.ValidForIssueUpdate
}
}
function mapIssueCustomAttributesToParameters(row: any): any {
return {
issueCustomAttributeId: row.IssueCustomAttributeId,
title: row.Title,
dataType: row.DataType,
visualType: row.VisualType,
isRequired: row.IsRequired,
fieldOptions: JSON.parse(row.FieldOptions)
}
}
function mapRowToIssueActivityCategory(row: any): IssueActivityCategory {
return {
activityCategoryId: row.ActivityCategoryId,
categoryTypeId: row.CategoryTypeId,
categoryName: row.CategoryName,
typeName: row.TypeName
};
}
export async function getNameFromCacheOrIam(tenant: string, email: string) {
if (email.toLowerCase() === 'system@xyzreality.com') {
return "System";
} else {
const contactInfo = await getFromCacheOrIam(tenant, email)
if (contactInfo) {
return `${contactInfo.firstName} ${contactInfo.lastName}`
}
}
return "";
}