import { BadRequestException, ForbiddenException, Injectable, Logger, NotFoundException, Inject, forwardRef, } from '@nestjs/common'; import { InjectRepository, InjectDataSource } from '@nestjs/typeorm'; import { Repository, DataSource, In } from 'typeorm'; import { PreviousNcEntity } from '../entities/previous-nc.entity'; import { PreviousNcrEntryEntity } from '../entities/previous-ncr-entry.entity'; import { PreviousNcRemarkEntity } from '../entities/previous-nc-remark.entity'; import { PreviousNcFinalClosureEntity } from '../entities/previous-nc-final-closure.entity'; import { PreviousNcClientEntity } from '../entities/previous-nc-client.entity'; import { PreviousNcSurveEntity } from '../entities/previous-nc-surve.entity'; import { PreviousNcStandardEntity } from '../entities/previous-nc-standard.entity'; import { PreviousNcUserEntity } from '../entities/previous-nc-user.entity'; import { ListPreviousNcsDto } from '../dto/list-previous-ncs.dto'; import { UpdatePreviousNcDto } from '../dto/update-previous-nc.dto'; import { NcFileStorageService } from './nc-file-storage.service'; import { Readable } from 'stream'; import { NcEmailTemplateService } from './nc-email-template.service'; import { MailsService } from '../../mails/mails.service'; import { PreviousNcPdfService } from './previous-nc-pdf.service'; // πŸ†• const VIEW_ALL_ROLES = ['super-admin', 'coordinator', 'marketing']; // πŸ†• Static emails that ALWAYS receive the NC-raised copy (with PDFs) const NC_STATIC_ADMIN_EMAILS = [ 'audit5@qrs.ae', 'manzoorqrs@gmail.com', ]; export interface PreviousNcRowDto { id: number; source: 'QRS' | 'TQS' | 'NEW'; company_name: string | null; contact_primary: string | null; audit_type: string; nc_type: string; status: string; created_by_name: string | null; assigned_to_name: string | null; uploaded_doc: string | null; old_nc_document: string | null; created_at: Date; entries_count: number; } export interface PreviousNcDetailDto { nc: PreviousNcEntity & { source: 'QRS' | 'TQS' | 'NEW'; company_code: string | null; contact_person: string | null; client_email: string | null; mobile: string | null; location: string | null; company_name: string | null; auditee_name: string | null; audit_date: string | null; designation: string | null; standard_names: string[]; created_by_name: string | null; assigned_to_name: string | null; }; entries: PreviousNcrEntryEntity[]; remarks: PreviousNcRemarkEntity[]; final_closures: PreviousNcFinalClosureEntity[]; } export interface PreviousNcUserOptionDto { name: string; count: number; } @Injectable() export class PreviousNcService { private readonly logger = new Logger(PreviousNcService.name); constructor( // QRS connection @InjectRepository(PreviousNcEntity, 'qrs') private readonly qrsNcRepo: Repository, @InjectRepository(PreviousNcrEntryEntity, 'qrs') private readonly qrsEntryRepo: Repository, @InjectRepository(PreviousNcRemarkEntity, 'qrs') private readonly qrsRemarkRepo: Repository, @InjectRepository(PreviousNcFinalClosureEntity, 'qrs') private readonly qrsClosureRepo: Repository, @InjectRepository(PreviousNcClientEntity, 'qrs') private readonly qrsClientRepo: Repository, @InjectRepository(PreviousNcSurveEntity, 'qrs') private readonly qrsSurveRepo: Repository, @InjectRepository(PreviousNcStandardEntity, 'qrs') private readonly qrsStandardRepo: Repository, @InjectRepository(PreviousNcUserEntity, 'qrs') private readonly qrsUserRepo: Repository, // TQS connection @InjectRepository(PreviousNcEntity, 'tqs') private readonly tqsNcRepo: Repository, @InjectRepository(PreviousNcrEntryEntity, 'tqs') private readonly tqsEntryRepo: Repository, @InjectRepository(PreviousNcRemarkEntity, 'tqs') private readonly tqsRemarkRepo: Repository, @InjectRepository(PreviousNcFinalClosureEntity, 'tqs') private readonly tqsClosureRepo: Repository, @InjectRepository(PreviousNcClientEntity, 'tqs') private readonly tqsClientRepo: Repository, @InjectRepository(PreviousNcSurveEntity, 'tqs') private readonly tqsSurveRepo: Repository, @InjectRepository(PreviousNcStandardEntity, 'tqs') private readonly tqsStandardRepo: Repository, @InjectRepository(PreviousNcUserEntity, 'tqs') private readonly tqsUserRepo: Repository, @InjectDataSource('qrs') private readonly qrsDataSource: DataSource, @InjectDataSource('tqs') private readonly tqsDataSource: DataSource, @InjectDataSource('scheme_dbs') private readonly schemeDb: DataSource, // πŸ†• PHASE 1 private readonly fileStorage: NcFileStorageService, private readonly emailTpl: NcEmailTemplateService, private readonly mailsService: MailsService, @Inject(forwardRef(() => PreviousNcPdfService)) private readonly pdfService: PreviousNcPdfService, ) { } // πŸ†• EMAIL β€” notify client + auditor + admin when a NEW NC is raised private async notifyNcRaised( ncId: number, auditorUserId: number, ): Promise { // 1. Load the full NC detail (entries, company, auditor name) const detail = await this.findOne('NEW', ncId, auditorUserId); const nc: any = detail.nc; // 2. Resolve the auditor's email (the creator) let auditorEmail: string | null = null; { const userRows = await this.schemeDb.query( `SELECT email FROM users WHERE id = ? LIMIT 1`, [auditorUserId], ); auditorEmail = (userRows[0]?.email || '').trim().toLowerCase() || null; } // 3. Generate the two PDFs (best-effort each) let ncReportPdf: Buffer | null = null; let attendancePdf: Buffer | null = null; try { ncReportPdf = await this.pdfService.generateNcReportPdf( 'NEW', ncId, auditorUserId, ); } catch (e: any) { this.logger.warn(`[NC-MAIL] report PDF failed for ${ncId}: ${e.message}`); } try { attendancePdf = await this.pdfService.generateAttendancePdf( 'NEW', ncId, auditorUserId, ); } catch (e: any) { this.logger.warn( `[NC-MAIL] attendance PDF failed for ${ncId}: ${e.message}`, ); } const attachments = [ ncReportPdf && { filename: `NC-${ncId}-report.pdf`, content: ncReportPdf, contentType: 'application/pdf', }, attendancePdf && { filename: `NC-${ncId}-attendance.pdf`, content: attendancePdf, contentType: 'application/pdf', }, ].filter(Boolean) as any[]; const companyName = nc.company_name || 'N/A'; const auditType = nc.audit_type || 'N/A'; const auditDate = nc.audit_date ? new Date(nc.audit_date).toLocaleDateString('en-GB', { day: '2-digit', month: 'long', year: 'numeric', }) : 'N/A'; const auditorName = nc.created_by_name || 'N/A'; const entriesForTpl = detail.entries.map((e, i) => ({ index: i + 1, nc_type: e.nc_type || '', ncr_statement_html: this.formatNcrStatement(e.ncr_statement), criteria_clause_html: this.formatCriteriaClause(e.criteria_clause), corrective_action: e.corrective_action || '', })); // 4. AUDITOR confirmation copy β€” with both PDFs if (auditorEmail) { const html = this.emailTpl.renderNcRaisedAuditor({ nc_id: ncId, company_name: companyName, audit_type: auditType, audit_date: auditDate, auditor_name: auditorName, findings_count: detail.entries.length, entries: entriesForTpl, }); await this.mailsService .sendAsUser(auditorUserId, { to: auditorEmail, subject: `NC Submitted β€” ${companyName}`, html, attachments, }) .catch((e) => this.logger.warn(`[NC-MAIL] auditor send failed: ${e.message}`), ); } // 5. INTERNAL β€” coordinators + super-admins (by role) + static // admin emails. Everyone gets both PDFs. const roleRecipients = (await this.schemeDb.query( ` SELECT DISTINCT LOWER(TRIM(u.email)) AS email FROM users u INNER JOIN user_roles ur ON ur.user_id = u.id INNER JOIN roles r ON r.id = ur.role_id WHERE LOWER(r.name) IN ('super-admin', 'coordinator') AND u.email IS NOT NULL AND u.email <> '' `, )) as { email: string }[]; const internalEmails = new Set( roleRecipients.map((r) => r.email).filter(Boolean), ); for (const e of NC_STATIC_ADMIN_EMAILS) { const clean = (e || '').trim().toLowerCase(); if (clean) internalEmails.add(clean); } // Auditor already got their own copy β€” don't send twice if (auditorEmail) internalEmails.delete(auditorEmail); if (internalEmails.size > 0) { const html = this.emailTpl.renderNcRaisedAdmin({ nc_id: ncId, company_name: companyName, audit_type: auditType, audit_date: auditDate, auditor_name: auditorName, findings_count: detail.entries.length, raised_at: new Date().toLocaleString(), evidence_email: 'manzoorqrs@gmail.com', entries: entriesForTpl, }); for (const email of internalEmails) { await this.mailsService .sendAsUser(auditorUserId, { to: email, subject: `[Internal] New NC Raised β€” ${companyName} (NC #${ncId})`, html, attachments, }) .catch((err) => this.logger.warn( `[NC-MAIL] internal send failed (${email}): ${err.message}`, ), ); } } this.logger.log( `[NC-MAIL] NC ${ncId} notified β€” auditor=${!!auditorEmail} internal=${internalEmails.size} pdfs=${attachments.length}`, ); } private async userHasNcPermission(userId: number, action: string): Promise { if (!userId) return false; try { const rows = await this.schemeDb.query( `SELECT 1 FROM user_permissions up INNER JOIN permissions p ON p.id = up.permission_id WHERE up.user_id = ? AND p.action = ? LIMIT 1`, [userId, action], ); return (rows as any[]).length > 0; } catch (e: any) { this.logger.error(`[NC-PERM] lookup failed: ${e.message}`); return false; } } // ═══════════════════════════════════════════════════════════════ // πŸ†• NEW NCs (scheme_dbs ) β€” list, detail, create (raw SQL) // ═══════════════════════════════════════════════════════════════ private formatNcrStatement(raw: string | null): string { if (!raw) return ''; const escaped = this.escapeHtml(raw); const m = escaped.match(/^([^\n\r:]{3,}):\s*([\s\S]*)/); if (m) return `${m[1]}:
${m[2].replace(/\n/g, '
')}`; return escaped.replace(/\n/g, '
'); } private formatCriteriaClause(raw: string | null): string { if (!raw) return ''; return raw .split('\n') .map((line) => { const t = line.trim(); const m = t.match(/^(\d+(\.\d+)*)(.*)/); if (m) return `${m[1]}
${this.escapeHtml(m[3].trim())}`; return this.escapeHtml(line); }) .join('
'); } private escapeHtml(s: string): string { if (!s) return ''; return s .replace(/&/g, '&').replace(//g, '>') .replace(/"/g, '"').replace(/'/g, '''); } private async fetchNewNcs( q: ListPreviousNcsDto, canViewAll: boolean, currentUserId: number, ): Promise { const where: string[] = []; const params: any[] = []; if (!canViewAll) { where.push( '(nc.created_by = ? OR nc.followed_up_by = ? OR nc.closed_by = ?)', ); params.push(currentUserId, currentUserId, currentUserId); } if (q.status && q.status !== 'All') { where.push('nc.status = ?'); params.push(q.status); } if (q.nc_type && q.nc_type !== 'All') { where.push('nc.nc_type = ?'); params.push(q.nc_type); } if (q.audit_type && q.audit_type !== 'All') { where.push('nc.audit_type = ?'); params.push(q.audit_type); } if (q.date_from) { where.push('nc.created_at >= ?'); params.push(q.date_from); } if (q.date_to) { where.push('nc.created_at <= ?'); params.push(q.date_to); } const whereSql = where.length ? `WHERE ${where.join(' AND ')}` : ''; let rows: any[]; try { rows = await this.schemeDb.query( ` SELECT nc.id, nc.audit_type, nc.nc_type, nc.status, nc.created_at, c.name AS company_name, ar.audit_code AS audit_code, TRIM(CONCAT(COALESCE(fu.firstName,''),' ',COALESCE(fu.lastName,''))) AS created_by_name, TRIM(CONCAT(COALESCE(cb.firstName,''),' ',COALESCE(cb.lastName,''))) AS assigned_to_name, (SELECT COUNT(*) FROM ncr_entries e WHERE e.nc_id = nc.id) AS entries_count FROM nc__ncs nc LEFT JOIN audit_schedule_rows ar ON ar.id = nc.audit_id LEFT JOIN companies c ON c.id = nc.company_id LEFT JOIN users fu ON fu.id = nc.created_by LEFT JOIN users cb ON cb.id = nc.closed_by ${whereSql} ORDER BY nc.id DESC `, params, ); } catch (err: any) { this.logger.warn( `[PREV-NC-NEW] scheme_dbs s list query failed: ${err.message}`, ); return []; } const search = (q.search || '').trim().toLowerCase(); return rows .map( (r): PreviousNcRowDto => ({ id: Number(r.id), source: 'NEW', company_name: r.company_name ?? r.audit_code ?? null, contact_primary: null, audit_type: r.audit_type ?? '', nc_type: r.nc_type ?? '', status: r.status ?? 'open', created_by_name: (r.created_by_name || '').trim() || null, assigned_to_name: (r.assigned_to_name || '').trim() || null, uploaded_doc: null, old_nc_document: null, created_at: r.created_at, entries_count: Number(r.entries_count || 0), }), ) .filter((row) => !search ? true : (row.company_name || '').toLowerCase().includes(search), ); } private async findOneNew( id: number, currentUserId: number, ): Promise { const ncRows = await this.schemeDb.query( `SELECT * FROM nc__ncs WHERE id = ?`, [id], ); if (!ncRows.length) throw new NotFoundException(`NC ${id} not found in NEW`); const nc = ncRows[0]; const canViewAll = await this.userCanViewAll(currentUserId); // 🐞 DEBUG β€” see exactly what we're comparing and their types this.logger.log( `[DEBUG-FINDONENEW] id=${id} currentUserId=${currentUserId}(${typeof currentUserId}) ` + `created_by=${nc.created_by}(${typeof nc.created_by}) ` + `followed_up_by=${nc.followed_up_by}(${typeof nc.followed_up_by}) ` + `closed_by=${nc.closed_by}(${typeof nc.closed_by}) canViewAll=${canViewAll}`, ); const uid = Number(currentUserId); // πŸ†• normalize both sides to numbers const isOwner = Number(nc.created_by) === uid || Number(nc.followed_up_by) === uid || Number(nc.closed_by) === uid; if (!canViewAll && !isOwner) { this.logger.warn( `[DEBUG-FINDONENEW] DENY id=${id} user=${uid} not owner (created_by=${nc.created_by})`, ); throw new NotFoundException(`NC ${id} not found in NEW`); } const [ entries, remarks, finalClosures, auditRows, companyRows, auditStandardRows, companyStandardRows, ] = await Promise.all([ this.schemeDb.query( `SELECT * FROM ncr_entries WHERE nc_id = ? ORDER BY id ASC`, [id], ), this.schemeDb.query( `SELECT * FROM nc_remarks WHERE nc_id = ? ORDER BY created_at DESC`, [id], ), this.schemeDb.query( `SELECT * FROM nc_final_closures WHERE nc_id = ? ORDER BY id ASC`, [id], ), this.schemeDb.query( `SELECT ar.id, ar.audit_code, ar.audit_type, s.schedule_date FROM audit_schedule_rows ar LEFT JOIN audit_schedules s ON s.id = ar.schedule_id WHERE ar.id = ?`, [nc.audit_id], ), nc.company_id ? this.schemeDb.query( `SELECT id, name, company_code, contact_person, email, mobile, city FROM companies WHERE id = ?`, [nc.company_id], ) : Promise.resolve([]), // standards: audit-specific first (keyed by the schedule row_id), // company-wide as fallback this.schemeDb.query( `SELECT st.name FROM audit_schedule_row_standards asrs INNER JOIN standards st ON st.id = asrs.standard_id WHERE asrs.row_id = ?`, [nc.audit_id], ), nc.company_id ? this.schemeDb.query( `SELECT st.name FROM company_standards cs INNER JOIN standards st ON st.id = cs.standard_id WHERE cs.company_id = ?`, [nc.company_id], ) : Promise.resolve([]), ]); const audit = auditRows[0] || {}; const company = companyRows[0] || {}; const standardNames: string[] = ( auditStandardRows.length ? auditStandardRows : companyStandardRows ) .map((r: any) => (r.name || '').trim()) .filter(Boolean); const userIds = [nc.created_by, nc.closed_by] .filter(Boolean) .map((x) => Number(x)); let userMap = new Map(); if (userIds.length) { const us = await this.schemeDb.query( `SELECT id, firstName, lastName, email FROM users WHERE id IN (?)`, [userIds], ); userMap = new Map(us.map((u: any) => [Number(u.id), u])); } const nameOf = (uid: number | null) => { const u = uid != null ? userMap.get(Number(uid)) : null; if (!u) return null; const n = `${u.firstName ?? ''} ${u.lastName ?? ''}`.trim(); return n || u.email || null; }; return { nc: { ...nc, source: 'NEW', company_name: company.name ?? audit.audit_code ?? null, auditee_name: nc.auditee_name ?? null, auditees_json: nc.auditees ?? null, // πŸ†• raw JSON column audit_date: audit.schedule_date ?? null, designation: null, standard_names: standardNames, created_by_name: nameOf(nc.created_by), assigned_to_name: nameOf(nc.closed_by), company_code: company.company_code ?? null, contact_person: company.contact_person ?? null, client_email: company.email ?? null, mobile: company.mobile ?? null, location: company.city ?? null, }, entries, remarks, final_closures: finalClosures, }; } async createNew( dto: { audit_id: number; nc_type: string; status?: string; auditee_name?: string | null; auditees?: Array<{ name: string; designation?: string }>; // πŸ†• ADD THIS follow_up_date?: string | null; due_date?: string | null; follow_up_notes?: string | null; remark?: string | null; findings: Array<{ nc_type?: string; ncr_statement: string; criteria_clause?: string; corrective_action?: string; status?: string; }>; }, currentUserId: number, ): Promise<{ ok: true; nc_id: number; audit_id: number; findings_created: number; }> { const validFindings = (dto.findings || []).filter((f) => f.ncr_statement?.trim(), ); if (validFindings.length === 0) { throw new BadRequestException( 'At least one finding with a statement is required.', ); } if (!dto.nc_type?.trim()) { throw new BadRequestException('nc_type is required.'); } const auditRows = await this.schemeDb.query( `SELECT id, company_id, audit_type FROM audit_schedule_rows WHERE id = ? LIMIT 1`, [dto.audit_id], ); if (!auditRows.length) { throw new NotFoundException(`Audit ${dto.audit_id} not found`); } const audit = auditRows[0]; const companyId = audit.company_id ?? null; const auditType = audit.audit_type ?? null; const qr = this.schemeDb.createQueryRunner(); await qr.connect(); await qr.startTransaction(); let findingsCreated = 0; try { const ncResult = await qr.query( `INSERT INTO nc__ncs (audit_id, company_id, auditee_name, auditees, audit_type, nc_type, status, follow_up_date, due_date, follow_up_notes, remark, created_by, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW(), NOW())`, [ dto.audit_id, // audit_id companyId, // company_id dto.auditee_name ?? null, // auditee_name JSON.stringify(dto.auditees ?? []), // auditees ← now has its own column + ? auditType, // audit_type dto.nc_type, // nc_type dto.status ?? 'open', // status dto.follow_up_date || null, // follow_up_date dto.due_date || null, // due_date dto.follow_up_notes ?? null, // follow_up_notes dto.remark ?? null, // remark currentUserId, // created_by ], ); const ncId: number = ncResult.insertId; if (!ncId) throw new BadRequestException('Failed to create NC row.'); for (const f of validFindings) { await qr.query( `INSERT INTO ncr_entries (nc_id, nc_type, ncr_statement, criteria_clause, corrective_action, status, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, NOW(), NOW())`, [ ncId, f.nc_type ?? dto.nc_type, f.ncr_statement.trim(), f.criteria_clause ?? null, f.corrective_action ?? null, f.status ?? 'open', ], ); findingsCreated++; } if (dto.remark && dto.remark.trim()) { await qr.query( `INSERT INTO nc_remarks (nc_id, user_id, remark, created_at, updated_at) VALUES (?, ?, ?, NOW(), NOW())`, [ncId, currentUserId, dto.remark.trim()], ); } await qr.commitTransaction(); this.logger.log( `[PREV-NC-NEW] User ${currentUserId} raised NEW NC ${ncId} for audit ${dto.audit_id} with ${findingsCreated} finding(s)`, ); this.notifyNcRaised(ncId, currentUserId).catch((e) => this.logger.warn(`[NC-MAIL] notify failed for NC ${ncId}: ${e.message}`), ); return { ok: true, nc_id: ncId, audit_id: dto.audit_id, findings_created: findingsCreated, }; } catch (err) { await qr.rollbackTransaction(); this.logger.error( `[PREV-NC-NEW] create failed for audit ${dto.audit_id}: ${(err as Error).message}`, (err as Error).stack, ); throw err; } finally { await qr.release(); } } // ═══════════════════════════════════════════════════════════════ // πŸ†• EMAIL β€” send using recipients TYPED in the NC detail panel // ═══════════════════════════════════════════════════════════════ async sendNcNotificationManual( source: 'QRS' | 'TQS' | 'NEW', ncId: number, dto: { send_nc_to_client?: boolean; client_to?: string; // "client@example.com, b@x.com" client_cc?: string; client_bcc?: string; send_to_coord_auditor?: boolean; auditor_email?: string; coordinator_email?: string; }, currentUserId: number, ): Promise<{ ok: true; sent: string[] }> { const detail = await this.findOne(source, ncId, currentUserId); const nc: any = detail.nc; const sent: string[] = []; const clean = (s?: string) => (s || '') .split(',') .map((x) => x.trim()) .filter(Boolean) .join(', ') || null; const companyName = nc.company_name || 'N/A'; const auditType = nc.audit_type || 'N/A'; const auditDate = nc.audit_date ? new Date(nc.audit_date).toLocaleDateString('en-GB', { day: '2-digit', month: 'long', year: 'numeric', }) : 'N/A'; const auditorName = nc.created_by_name || 'N/A'; // Build PDFs ONLY if the client email is actually going out β€” and // generate both in parallel instead of one-after-another (β‰ˆhalf the time). const clientTo = clean(dto.client_to); const willSendClient = !!(dto.send_nc_to_client && clientTo); let ncReportPdf: Buffer | null = null; let attendancePdf: Buffer | null = null; if (willSendClient) { const [reportResult, attendanceResult] = await Promise.allSettled([ this.pdfService.generateNcReportPdf(source, ncId, currentUserId), this.pdfService.generateAttendancePdf(source, ncId, currentUserId), ]); if (reportResult.status === 'fulfilled') { ncReportPdf = reportResult.value; } else { this.logger.warn( `[NC-MAIL] report PDF failed for ${ncId}: ${reportResult.reason?.message}`, ); } if (attendanceResult.status === 'fulfilled') { attendancePdf = attendanceResult.value; } else { this.logger.warn( `[NC-MAIL] attendance PDF failed for ${ncId}: ${attendanceResult.reason?.message}`, ); } } const attachments = [ ncReportPdf && { filename: `NC-${ncId}-report.pdf`, content: ncReportPdf, contentType: 'application/pdf' }, attendancePdf && { filename: `NC-${ncId}-attendance.pdf`, content: attendancePdf, contentType: 'application/pdf' }, ].filter(Boolean) as any[]; // ── CLIENT (typed TO / CC / BCC) ── if (willSendClient) { const html = this.emailTpl.renderNcRaisedClient({ company_name: companyName, audit_type: auditType, audit_date: auditDate, auditor_name: auditorName, evidence_email: 'mkt1@qrs.ae', entries: detail.entries.map((e, i) => ({ index: i + 1, nc_type: e.nc_type || '', ncr_statement_html: this.formatNcrStatement(e.ncr_statement), criteria_clause_html: this.formatCriteriaClause(e.criteria_clause), corrective_action: e.corrective_action || '', })), }); await this.mailsService.sendAsUser(currentUserId, { to: clientTo, cc: clean(dto.client_cc) || undefined, bcc: clean(dto.client_bcc) || undefined, subject: `New NC Record Created β€” ${companyName}`, html, attachments, }); sent.push(`client:${clientTo}`); } // ── INTERNAL (typed auditor + coordinator) ── const internalTo = clean( [dto.auditor_email, dto.coordinator_email].filter(Boolean).join(', '), ); if (dto.send_to_coord_auditor && internalTo) { const html = this.emailTpl.renderNcRaisedAuditor({ nc_id: ncId, company_name: companyName, audit_type: auditType, audit_date: auditDate, auditor_name: auditorName, findings_count: detail.entries.length, }); await this.mailsService.sendAsUser(currentUserId, { to: internalTo, subject: `NC Submitted β€” ${companyName}`, html, }); sent.push(`internal:${internalTo}`); } this.logger.log(`[NC-MAIL] manual send NC ${ncId} β†’ ${sent.join(' | ') || 'nothing'}`); return { ok: true, sent }; } private sourceIdCache = new Map(); /** Translate a scheme_dbs s user id β†’ the legacy user id in QRS/TQS, by email. */ async resolveSourceUserId( schemeUserId: number, source: 'QRS' | 'TQS', ): Promise { const cacheKey = `${source}:${schemeUserId}`; if (this.sourceIdCache.has(cacheKey)) return this.sourceIdCache.get(cacheKey)!; // 1. scheme_dbs s email for this user const schemeRows = await this.schemeDb.query( `SELECT email FROM users WHERE id = ? LIMIT 1`, [schemeUserId], ); const email = (schemeRows[0]?.email || '').trim().toLowerCase(); if (!email) { this.sourceIdCache.set(cacheKey, null); return null; } // 2. matching legacy user id by email const userRepo = source === 'QRS' ? this.qrsUserRepo : this.tqsUserRepo; const match = await userRepo .createQueryBuilder('u') .select('u.id', 'id') .where('LOWER(u.email) = :email', { email }) .getRawOne<{ id: number }>(); const id = match ? Number(match.id) : null; this.sourceIdCache.set(cacheKey, id); this.logger.log( `[PREV-NC-IDMAP] scheme user ${schemeUserId} (${email}) β†’ ${source} id=${id ?? 'NONE'}`, ); return id; } // ═══════════════════════════════════════════════════════════════ // ROLE-BASED ACCESS CONTROL // ═══════════════════════════════════════════════════════════════ async userCanViewAll(userId: number): Promise { if (!userId) return false; const result = await this.schemeDb.query( ` SELECT LOWER(r.name) AS role_name FROM users u INNER JOIN user_roles ur ON ur.user_id = u.id INNER JOIN roles r ON r.id = ur.role_id WHERE u.id = ? `, [userId], ); const roleNames: string[] = (result as any[]).map((r) => r.role_name); const canViewAll = roleNames.some((rn) => VIEW_ALL_ROLES.includes(rn)); this.logger.log( `[PREV-NC-PERM] User ${userId} roles=[${roleNames.join(',')}] β†’ ${canViewAll ? 'VIEW ALL' : 'OWN ONLY' }`, ); return canViewAll; } // ═══════════════════════════════════════════════════════════════ // LIST USERS (for user-filter dropdown) // ═══════════════════════════════════════════════════════════════ async listUsers(): Promise { const sql = ` SELECT TRIM(CONCAT(COALESCE(u.first_name, ''), ' ', COALESCE(u.last_name, ''))) AS name, COUNT(DISTINCT nc.id) AS count FROM users u INNER JOIN nc__ncs nc ON (nc.followed_up_by = u.id OR nc.closed_by = u.id) WHERE (u.first_name IS NOT NULL OR u.last_name IS NOT NULL) GROUP BY name HAVING name != '' ORDER BY name `; const [qrsUsers, tqsUsers] = await Promise.all([ this.qrsDataSource.query(sql).catch((err) => { this.logger.warn(`[PREV-NC-USERS] QRS query failed: ${err.message}`); return []; }), this.tqsDataSource.query(sql).catch((err) => { this.logger.warn(`[PREV-NC-USERS] TQS query failed: ${err.message}`); return []; }), ]); const merged = new Map(); for (const u of [...qrsUsers, ...tqsUsers] as any[]) { const name = (u.name || '').trim(); if (!name) continue; merged.set(name, (merged.get(name) || 0) + Number(u.count || 0)); } const result = Array.from(merged.entries()) .map(([name, count]) => ({ name, count })) .sort((a, b) => a.name.localeCompare(b.name)); this.logger.log( `[PREV-NC-USERS] Returning ${result.length} distinct users`, ); return result; } async auditAssignReport(): Promise<{ rows: Array<{ user_id: number; auditor: string; source: 'QRS' | 'TQS'; total_assigned: number; stage1_uploaded: number; stage1_missing: number; stage2_uploaded: number; stage2_missing: number; initial: number; surveillance: number; recertification: number; months: Array<{ month: string; // 'YYYY-MM' total_assigned: number; stage1_uploaded: number; stage2_uploaded: number; initial: number; surveillance: number; recertification: number; }>; }>; totals: { total_assigned: number; stage1_uploaded: number; stage1_missing: number; stage2_uploaded: number; stage2_missing: number; initial: number; surveillance: number; recertification: number; }; }> { // The two tables we sum across in each DB. Both have identical columns. const TABLES = ['clients__clientdatas', 'newsurve__surves']; // A reusable "date is really set" test. NOTE: we must NOT compare against // '0000-00-00' directly β€” under MySQL strict mode (NO_ZERO_DATE) that throws // "#1525 Incorrect DATE value". YEAR() is safe for zero/NULL dates. const dateSet = (col: string) => `(${col} IS NOT NULL AND YEAR(${col}) > 0)`; // ── per-user totals (one query per table, UNION-ed) ── const totalsSql = TABLES.map( (t) => ` SELECT u.id AS user_id, TRIM(CONCAT(COALESCE(u.first_name,''),' ',COALESCE(u.last_name,''))) AS auditor, COUNT(*) AS total_assigned, SUM(CASE WHEN c.stg1_audit_report IS NOT NULL AND c.stg1_audit_report <> '' THEN 1 ELSE 0 END) AS stage1_uploaded, SUM(CASE WHEN c.stg1_audit_report IS NULL OR c.stg1_audit_report = '' THEN 1 ELSE 0 END) AS stage1_missing, SUM(CASE WHEN c.stg2_audit_report IS NOT NULL AND c.stg2_audit_report <> '' THEN 1 ELSE 0 END) AS stage2_uploaded, SUM(CASE WHEN c.stg2_audit_report IS NULL OR c.stg2_audit_report = '' THEN 1 ELSE 0 END) AS stage2_missing, SUM(CASE WHEN ${dateSet('c.auditdate')} THEN 1 ELSE 0 END) AS initial, SUM(CASE WHEN ${dateSet('c.serv_date')} THEN 1 ELSE 0 END) AS surveillance, SUM(CASE WHEN ${dateSet('c.recert_date')} THEN 1 ELSE 0 END) AS recertification FROM ${t} c INNER JOIN users u ON JSON_VALID(c.auditassign) AND JSON_CONTAINS(c.auditassign, JSON_QUOTE(CAST(u.id AS CHAR))) WHERE c.auditassign IS NOT NULL AND c.auditassign <> '' GROUP BY u.id, auditor `, ).join(' UNION ALL '); // Wrap the UNION so users that appear in BOTH tables get summed once. const totalsWrapped = ` SELECT user_id, MAX(auditor) AS auditor, SUM(total_assigned) AS total_assigned, SUM(stage1_uploaded) AS stage1_uploaded, SUM(stage1_missing) AS stage1_missing, SUM(stage2_uploaded) AS stage2_uploaded, SUM(stage2_missing) AS stage2_missing, SUM(initial) AS initial, SUM(surveillance) AS surveillance, SUM(recertification) AS recertification FROM ( ${totalsSql} ) AS u_all GROUP BY user_id ORDER BY total_assigned DESC `; // ── per-user per-month ── // Per-month: each audit type is counted by ITS OWN date column. // total_assigned / stage1 / stage2 / initial -> grouped by auditdate // surveillance -> grouped by serv_date // recertification -> grouped by recert_date // We emit one row per (user, month, source-date) and merge in JS by month. const monthsSql = TABLES.map( (t) => ` SELECT u.id AS user_id, DATE_FORMAT(c.auditdate, '%Y-%m') AS month, COUNT(*) AS total_assigned, SUM(CASE WHEN c.stg1_audit_report IS NOT NULL AND c.stg1_audit_report <> '' THEN 1 ELSE 0 END) AS stage1_uploaded, SUM(CASE WHEN c.stg2_audit_report IS NOT NULL AND c.stg2_audit_report <> '' THEN 1 ELSE 0 END) AS stage2_uploaded, COUNT(*) AS initial, 0 AS surveillance, 0 AS recertification FROM ${t} c INNER JOIN users u ON JSON_VALID(c.auditassign) AND JSON_CONTAINS(c.auditassign, JSON_QUOTE(CAST(u.id AS CHAR))) WHERE c.auditassign IS NOT NULL AND c.auditassign <> '' AND c.auditdate IS NOT NULL AND YEAR(c.auditdate) > 0 GROUP BY u.id, month UNION ALL SELECT u.id AS user_id, DATE_FORMAT(c.serv_date, '%Y-%m') AS month, 0 AS total_assigned, 0 AS stage1_uploaded, 0 AS stage2_uploaded, 0 AS initial, COUNT(*) AS surveillance, 0 AS recertification FROM ${t} c INNER JOIN users u ON JSON_VALID(c.auditassign) AND JSON_CONTAINS(c.auditassign, JSON_QUOTE(CAST(u.id AS CHAR))) WHERE c.auditassign IS NOT NULL AND c.auditassign <> '' AND c.serv_date IS NOT NULL AND YEAR(c.serv_date) > 0 GROUP BY u.id, month UNION ALL SELECT u.id AS user_id, DATE_FORMAT(c.recert_date, '%Y-%m') AS month, 0 AS total_assigned, 0 AS stage1_uploaded, 0 AS stage2_uploaded, 0 AS initial, 0 AS surveillance, COUNT(*) AS recertification FROM ${t} c INNER JOIN users u ON JSON_VALID(c.auditassign) AND JSON_CONTAINS(c.auditassign, JSON_QUOTE(CAST(u.id AS CHAR))) WHERE c.auditassign IS NOT NULL AND c.auditassign <> '' AND c.recert_date IS NOT NULL AND YEAR(c.recert_date) > 0 GROUP BY u.id, month `, ).join(' UNION ALL '); const monthsWrapped = ` SELECT user_id, month, SUM(total_assigned) AS total_assigned, SUM(stage1_uploaded) AS stage1_uploaded, SUM(stage2_uploaded) AS stage2_uploaded, SUM(initial) AS initial, SUM(surveillance) AS surveillance, SUM(recertification) AS recertification FROM ( ${monthsSql} ) AS m_all GROUP BY user_id, month ORDER BY month ASC `; // Run all four queries (2 per DB) in parallel, tolerating per-DB failure. const [qrsTotals, tqsTotals, qrsMonths, tqsMonths] = await Promise.all([ this.qrsDataSource.query(totalsWrapped).catch((e) => { this.logger.warn(`[AUDIT-ASSIGN] QRS totals failed: ${e.message}`); return []; }), this.tqsDataSource.query(totalsWrapped).catch((e) => { this.logger.warn(`[AUDIT-ASSIGN] TQS totals failed: ${e.message}`); return []; }), this.qrsDataSource.query(monthsWrapped).catch((e) => { this.logger.warn(`[AUDIT-ASSIGN] QRS months failed: ${e.message}`); return []; }), this.tqsDataSource.query(monthsWrapped).catch((e) => { this.logger.warn(`[AUDIT-ASSIGN] TQS months failed: ${e.message}`); return []; }), ]); // Index months by `${source}:${user_id}` so each auditor row gets its own. const monthIndex = new Map(); const indexMonths = (rows: any[], source: 'QRS' | 'TQS') => { for (const m of rows as any[]) { const key = `${source}:${Number(m.user_id)}`; if (!monthIndex.has(key)) monthIndex.set(key, []); monthIndex.get(key)!.push({ month: m.month, total_assigned: Number(m.total_assigned || 0), stage1_uploaded: Number(m.stage1_uploaded || 0), stage2_uploaded: Number(m.stage2_uploaded || 0), initial: Number(m.initial || 0), surveillance: Number(m.surveillance || 0), recertification: Number(m.recertification || 0), }); } }; indexMonths(qrsMonths, 'QRS'); indexMonths(tqsMonths, 'TQS'); const buildRows = (rows: any[], source: 'QRS' | 'TQS') => (rows as any[]).map((r) => ({ user_id: Number(r.user_id), auditor: (r.auditor || '').trim() || `User #${r.user_id}`, source, total_assigned: Number(r.total_assigned || 0), stage1_uploaded: Number(r.stage1_uploaded || 0), stage1_missing: Number(r.stage1_missing || 0), stage2_uploaded: Number(r.stage2_uploaded || 0), stage2_missing: Number(r.stage2_missing || 0), initial: Number(r.initial || 0), surveillance: Number(r.surveillance || 0), recertification: Number(r.recertification || 0), months: monthIndex.get(`${source}:${Number(r.user_id)}`) || [], })); const allRows = [ ...buildRows(qrsTotals, 'QRS'), ...buildRows(tqsTotals, 'TQS'), ].sort((a, b) => b.total_assigned - a.total_assigned); const totals = allRows.reduce( (acc, r) => { acc.total_assigned += r.total_assigned; acc.stage1_uploaded += r.stage1_uploaded; acc.stage1_missing += r.stage1_missing; acc.stage2_uploaded += r.stage2_uploaded; acc.stage2_missing += r.stage2_missing; acc.initial += r.initial; acc.surveillance += r.surveillance; acc.recertification += r.recertification; return acc; }, { total_assigned: 0, stage1_uploaded: 0, stage1_missing: 0, stage2_uploaded: 0, stage2_missing: 0, initial: 0, surveillance: 0, recertification: 0, }, ); this.logger.log( `[AUDIT-ASSIGN] report built β€” ${allRows.length} auditor rows ` + `(qrs=${qrsTotals.length} tqs=${tqsTotals.length}) total_assigned=${totals.total_assigned}`, ); return { rows: allRows, totals }; } // ═══════════════════════════════════════════════════════════════ // PAGED LIST WITH FILTERS // ═══════════════════════════════════════════════════════════════ async listPaged( q: ListPreviousNcsDto, currentUserId: number, ): Promise<{ rows: PreviousNcRowDto[]; total: number; page: number; limit: number; totalPages: number; }> { const page = q.page || 1; const limit = q.limit || 25; const canViewAll = await this.userCanViewAll(currentUserId); this.logger.log( `[PREV-NC-LIST] page=${page} limit=${limit} source=${q.source} status=${q.status} nc_type=${q.nc_type} audit_type=${q.audit_type} user_name="${q.user_name}" search="${q.search}" viewAll=${canViewAll}`, ); const src = q.source || 'All'; // ← treat undefined/null/'' as 'All' const wantQrs = src === 'All' || src === 'QRS'; const wantTqs = src === 'All' || src === 'TQS'; const wantNew = src === 'All' || src === 'NEW'; const [qrsRows, tqsRows, newRows] = await Promise.all([ wantQrs ? this.fetchFromSource('QRS', q, canViewAll, currentUserId) : Promise.resolve([]), wantTqs ? this.fetchFromSource('TQS', q, canViewAll, currentUserId) : Promise.resolve([]), wantNew ? this.fetchNewNcs(q, canViewAll, currentUserId) : Promise.resolve([]), ]); const ts = (d: any) => { const t = new Date(d).getTime(); return isNaN(t) ? 0 : t; }; const merged = [...qrsRows, ...tqsRows, ...newRows].sort( (a, b) => ts(b.created_at) - ts(a.created_at), ); const total = merged.length; const totalPages = Math.ceil(total / limit) || 1; const start = (page - 1) * limit; const pageRows = merged.slice(start, start + limit); return { rows: pageRows, total, page, limit, totalPages }; } private async fetchFromSource( source: 'QRS' | 'TQS' | 'NEW', q: ListPreviousNcsDto, canViewAll: boolean, currentUserId: number, ): Promise { const ncRepo = source === 'QRS' ? this.qrsNcRepo : this.tqsNcRepo; const clientRepo = source === 'QRS' ? this.qrsClientRepo : this.tqsClientRepo; const surveRepo = source === 'QRS' ? this.qrsSurveRepo : this.tqsSurveRepo; const userRepo = source === 'QRS' ? this.qrsUserRepo : this.tqsUserRepo; const entryRepo = source === 'QRS' ? this.qrsEntryRepo : this.tqsEntryRepo; let userFilterIds: number[] | null = null; if (q.user_name && q.user_name !== 'all') { const matchingUsers = await userRepo .createQueryBuilder('u') .where( "TRIM(CONCAT(COALESCE(u.first_name, ''), ' ', COALESCE(u.last_name, ''))) = :name", { name: q.user_name }, ) .getMany(); userFilterIds = matchingUsers.map((u) => u.id); this.logger.log( `[PREV-NC-LIST] user_name="${q.user_name}" β†’ ${source} ids=[${userFilterIds.join(',')}]`, ); if (userFilterIds.length === 0) return []; } const qb = ncRepo.createQueryBuilder('nc').orderBy('nc.id', 'DESC'); if (!canViewAll) { const sourceUid = await this.resolveSourceUserId( currentUserId, source as 'QRS' | 'TQS', ); if (sourceUid == null) return []; qb.andWhere('(nc.followed_up_by = :uid OR nc.closed_by = :uid)', { uid: sourceUid, }); } if (userFilterIds && userFilterIds.length > 0) { qb.andWhere( '(nc.followed_up_by IN (:...uFids) OR nc.closed_by IN (:...uFids))', { uFids: userFilterIds }, ); } if (q.status && q.status !== 'All') { qb.andWhere('nc.status = :status', { status: q.status }); } if (q.nc_type && q.nc_type !== 'All') { qb.andWhere('nc.nc_type = :ncType', { ncType: q.nc_type }); } if (q.audit_type && q.audit_type !== 'All') { qb.andWhere('nc.audit_type = :auditType', { auditType: q.audit_type }); } if (q.date_from) { qb.andWhere('nc.created_at >= :df', { df: q.date_from }); } if (q.date_to) { qb.andWhere('nc.created_at <= :dt', { dt: q.date_to }); } const ncs = await qb.getMany(); if (!ncs.length) return []; const clientIds = Array.from( new Set(ncs.map((n) => n.client_id).filter((x): x is number => !!x)), ); const surveIds = Array.from( new Set(ncs.map((n) => n.serve_id).filter((x): x is number => !!x)), ); const userIds = Array.from( new Set( ncs .flatMap((n) => [n.followed_up_by, n.closed_by]) .filter((x): x is number => !!x), ), ); const ncIds = ncs.map((n) => n.id); const [clients, surves, users, entryCounts] = await Promise.all([ clientIds.length ? clientRepo.find({ where: { id: In(clientIds) } }) : Promise.resolve([]), surveIds.length ? surveRepo.find({ where: { id: In(surveIds) } }) : Promise.resolve([]), userIds.length ? userRepo.find({ where: { id: In(userIds) } }) : Promise.resolve([]), ncIds.length ? this.countEntriesPerNc(entryRepo, ncIds) : Promise.resolve(new Map()), ]); const clientMap = new Map(clients.map((c) => [c.id, c])); const surveMap = new Map(surves.map((s) => [s.id, s])); const userMap = new Map(users.map((u) => [u.id, u])); const search = (q.search || '').trim().toLowerCase(); const rows = ncs .map((nc) => this.shapeRow(nc, source, clientMap, surveMap, userMap, entryCounts), ) .filter((row) => { if (!search) return true; const cn = (row.company_name || '').toLowerCase(); return cn.includes(search); }); return rows; } private async countEntriesPerNc( entryRepo: Repository, ncIds: number[], ): Promise> { const raw = await entryRepo .createQueryBuilder('e') .select('e.nc_id', 'nc_id') .addSelect('COUNT(e.id)', 'cnt') .where('e.nc_id IN (:...ids)', { ids: ncIds }) .groupBy('e.nc_id') .getRawMany<{ nc_id: number; cnt: string }>(); const map = new Map(); for (const r of raw) { map.set(Number(r.nc_id), Number(r.cnt)); } return map; } private shapeRow( nc: PreviousNcEntity, source: 'QRS' | 'TQS' | 'NEW', clientMap: Map, surveMap: Map, userMap: Map, entryCounts: Map, ): PreviousNcRowDto { const client = nc.client_id ? clientMap.get(nc.client_id) : null; const surve = nc.serve_id ? surveMap.get(nc.serve_id) : null; const followedUp = nc.followed_up_by ? userMap.get(nc.followed_up_by) : null; const closedBy = nc.closed_by ? userMap.get(nc.closed_by) : null; return { id: nc.id, source, company_name: client?.company_name ?? surve?.company_name ?? null, contact_primary: client?.contact_primary ?? surve?.contact_primary ?? null, audit_type: nc.audit_type, nc_type: nc.nc_type, status: nc.status, created_by_name: this.fullName(followedUp), assigned_to_name: this.fullName(closedBy), uploaded_doc: client?.nc_docs ?? surve?.nc_docs ?? null, old_nc_document: null, created_at: nc.created_at, entries_count: entryCounts.get(nc.id) ?? 0, }; } // ═══════════════════════════════════════════════════════════════ // SINGLE NC DETAIL // ═══════════════════════════════════════════════════════════════ async findOne( source: 'QRS' | 'TQS' | 'NEW', id: number, currentUserId: number, ): Promise { if (source === 'NEW') { return this.findOneNew(id, currentUserId); } const ncRepo = source === 'QRS' ? this.qrsNcRepo : this.tqsNcRepo; const entryRepo = source === 'QRS' ? this.qrsEntryRepo : this.tqsEntryRepo; const remarkRepo = source === 'QRS' ? this.qrsRemarkRepo : this.tqsRemarkRepo; const closureRepo = source === 'QRS' ? this.qrsClosureRepo : this.tqsClosureRepo; const clientRepo = source === 'QRS' ? this.qrsClientRepo : this.tqsClientRepo; const surveRepo = source === 'QRS' ? this.qrsSurveRepo : this.tqsSurveRepo; const userRepo = source === 'QRS' ? this.qrsUserRepo : this.tqsUserRepo; const standardRepo = source === 'QRS' ? this.qrsStandardRepo : this.tqsStandardRepo; const nc = await ncRepo.findOne({ where: { id } }); if (!nc) { throw new NotFoundException(`NC ${id} not found in ${source}`); } const canViewAll = await this.userCanViewAll(currentUserId); const sourceUid = await this.resolveSourceUserId( currentUserId, source as 'QRS' | 'TQS', ); if ( !canViewAll && nc.followed_up_by !== sourceUid && nc.closed_by !== sourceUid ) { throw new NotFoundException(`NC ${id} not found in ${source}`); } const [entries, remarks, finalClosures, client, surve] = await Promise.all([ entryRepo.find({ where: { nc_id: id }, order: { id: 'ASC' } }), remarkRepo.find({ where: { nc_id: id }, order: { created_at: 'DESC' } }), closureRepo.find({ where: { nc_id: id }, order: { id: 'ASC' } }), nc.client_id ? clientRepo.findOne({ where: { id: nc.client_id } }) : Promise.resolve(null), nc.serve_id ? surveRepo.findOne({ where: { id: nc.serve_id } }) : Promise.resolve(null), ]); const userIds = [nc.followed_up_by, nc.closed_by].filter( (x): x is number => !!x, ); const users = userIds.length ? await userRepo.find({ where: { id: In(userIds) } }) : []; const userMap = new Map(users.map((u) => [u.id, u])); const standardJson = client?.standard_name ?? surve?.standard_name ?? null; const standardNames = await this.resolveStandardNames( standardJson, standardRepo, ); return { nc: { ...nc, source, company_name: client?.company_name ?? surve?.company_name ?? null, auditee_name: nc.auditee_name, audit_date: client?.auditdate ?? surve?.auditdate ?? null, designation: client?.designationpr ?? surve?.designationpr ?? null, standard_names: standardNames, created_by_name: this.fullName( nc.followed_up_by ? userMap.get(nc.followed_up_by) : null, ), assigned_to_name: this.fullName( nc.closed_by ? userMap.get(nc.closed_by) : null, ), // πŸ†• client contact block (legacy QRS/TQS columns) company_code: null, // legacy tables have no company_code contact_person: client?.contact_primary ?? surve?.contact_primary ?? null, client_email: client?.email_id ?? surve?.email_id ?? null, mobile: client?.mobile_no ?? client?.telephone ?? surve?.mobile_no ?? surve?.telephone ?? null, location: null, // legacy tables have no city/location column }, entries, remarks, final_closures: finalClosures, }; } // ═══════════════════════════════════════════════════════════════ // UPDATE NC + ENTRIES + OPTIONAL NEW REMARK (transactional) // ═══════════════════════════════════════════════════════════════ async update( source: 'QRS' | 'TQS' | 'NEW', id: number, dto: UpdatePreviousNcDto, currentUserId: number, ): Promise<{ ok: true; nc_id: number; source: 'QRS' | 'TQS' | 'NEW'; updated_fields: string[]; updated_entries: number; new_remark_id: number | null; new_findings_created: number; }> { const canViewAll = await this.userCanViewAll(currentUserId); const ds = source === 'QRS' ? this.qrsDataSource : source === 'TQS' ? this.tqsDataSource : this.schemeDb; // 'NEW' const existing = await ds.query( `SELECT id, followed_up_by, closed_by, created_by FROM nc__ncs WHERE id = ?`, [id], ); if (!existing.length) { throw new NotFoundException(`NC ${id} not found in ${source}`); } const sourceUid = source === 'NEW' ? currentUserId : await this.resolveSourceUserId(currentUserId, source); if (!canViewAll) { const row = existing[0]; const uid = Number(sourceUid); const isAssociated = Number(row.followed_up_by) === uid || Number(row.closed_by) === uid || Number(row.created_by) === uid; if (!isAssociated) { throw new ForbiddenException( `You do not have permission to edit NC ${id}`, ); } } const qr = ds.createQueryRunner(); await qr.connect(); await qr.startTransaction(); const updatedFields: string[] = []; let updatedEntries = 0; let newRemarkId: number | null = null; let newFindingsCreated = 0; try { if (dto.nc) { const setClauses: string[] = []; const values: any[] = []; const map: Array< [keyof NonNullable, string] > = [ ['auditee_name', 'auditee_name'], ['audit_type', 'audit_type'], ['nc_type', 'nc_type'], ['status', 'status'], ['follow_up_date', 'follow_up_date'], ['follow_up_notes', 'follow_up_notes'], ['remark', 'remark'], ['due_date', 'due_date'], ]; for (const [key, column] of map) { if (dto.nc[key] !== undefined) { setClauses.push(`\`${column}\` = ?`); values.push(dto.nc[key]); updatedFields.push(column); } } if (dto.nc.status === 'closed') { // NEW NCs store scheme id; QRS/TQS store the resolved legacy id. setClauses.push('`closed_by` = ?', '`closed_at` = NOW()'); values.push(sourceUid); // sourceUid computed above updatedFields.push('closed_by', 'closed_at'); } if (setClauses.length > 0) { setClauses.push('`updated_at` = NOW()'); values.push(id); await qr.query( `UPDATE nc__ncs SET ${setClauses.join(', ')} WHERE id = ?`, values, ); } } if (Array.isArray(dto.entries) && dto.entries.length > 0) { for (const e of dto.entries) { const setClauses: string[] = []; const values: any[] = []; if (e.nc_type !== undefined) { setClauses.push('`nc_type` = ?'); values.push(e.nc_type); } if (e.ncr_statement !== undefined) { setClauses.push('`ncr_statement` = ?'); values.push(e.ncr_statement); } if (e.criteria_clause !== undefined) { setClauses.push('`criteria_clause` = ?'); values.push(e.criteria_clause); } if (e.corrective_action !== undefined) { setClauses.push('`corrective_action` = ?'); values.push(e.corrective_action); } if (e.status !== undefined) { setClauses.push('`status` = ?'); values.push(e.status); } if (setClauses.length > 0) { setClauses.push('`updated_at` = NOW()'); values.push(e.id, id); const result = await qr.query( `UPDATE ncr_entries SET ${setClauses.join( ', ', )} WHERE id = ? AND nc_id = ?`, values, ); if (result.affectedRows > 0) updatedEntries++; } } } // πŸ†• INSERT brand-new findings added during edit (drafts from the UI). // Works for QRS, TQS and NEW β€” uses the same `ds` chosen above, and // every database has the ncr_entries table keyed by nc_id. if (Array.isArray(dto.new_findings) && dto.new_findings.length > 0) { for (const f of dto.new_findings) { if (!f.ncr_statement || !String(f.ncr_statement).trim()) continue; await qr.query( `INSERT INTO ncr_entries (nc_id, nc_type, ncr_statement, criteria_clause, corrective_action, status, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, NOW(), NOW())`, [ id, f.nc_type ?? null, String(f.ncr_statement).trim(), f.criteria_clause ?? null, f.corrective_action ?? null, f.status ?? 'open', ], ); newFindingsCreated++; } } if (dto.new_remark && dto.new_remark.trim()) { const result = await qr.query( `INSERT INTO nc_remarks (nc_id, user_id, remark, created_at, updated_at) VALUES (?, ?, ?, NOW(), NOW())`, [id, currentUserId, dto.new_remark.trim()], ); newRemarkId = result.insertId ?? null; } await qr.commitTransaction(); this.logger.log( `[PREV-NC-UPDATE] User ${currentUserId} updated NC ${source}/${id} β€” ` + `fields=[${updatedFields.join(',')}] entries=${updatedEntries} ` + `new_findings=${newFindingsCreated} remark=${newRemarkId}`, ); return { ok: true, nc_id: id, source, updated_fields: updatedFields, updated_entries: updatedEntries, new_remark_id: newRemarkId, new_findings_created: newFindingsCreated, }; } catch (err) { await qr.rollbackTransaction(); this.logger.error( `[PREV-NC-UPDATE] Failed for ${source}/${id}: ${(err as Error).message}`, (err as Error).stack, ); throw err; } finally { await qr.release(); } } // ═══════════════════════════════════════════════════════════════ // πŸ†• PHASE 1 β€” EVIDENCE METHODS // ═══════════════════════════════════════════════════════════════ /** * Upload an evidence file for a specific finding (ncr_entry). * - Saves the file to NestJS's own storage * - Updates ncr_entries.document_path with "new:..." prefix * - Deletes the previous file IF it was a new-format file * (legacy files are never touched by NestJS) */ async uploadEntryEvidence( source: 'QRS' | 'TQS' | 'NEW', ncId: number, entryId: number, file: Express.Multer.File, currentUserId: number, ): Promise<{ ok: true; entry_id: number; document_path: string; filename: string; size: number; uploaded_at: string; }> { // 1. Permission check const canViewAll = await this.userCanViewAll(currentUserId); const ds = source === 'QRS' ? this.qrsDataSource : source === 'TQS' ? this.tqsDataSource : this.schemeDb; this.logger.log( `[DEBUG-EVIDENCE] source=${source}, ncId=${ncId}, entryId=${entryId}`, ); this.logger.log( `[DEBUG-EVIDENCE] using DB=${source === 'QRS' ? 'QRS' : source === 'TQS' ? 'TQS' : 'SCHEME' }`, ); const ncRow = await ds.query( `SELECT id, followed_up_by, closed_by FROM nc__ncs WHERE id = ?`, [ncId], ); if (!ncRow.length) { throw new NotFoundException(`NC ${ncId} not found in ${source}`); } if (!canViewAll) { const r = ncRow[0]; if (r.followed_up_by !== currentUserId && r.closed_by !== currentUserId) { throw new ForbiddenException( `You do not have permission to upload evidence for NC ${ncId}`, ); } } // 2. Verify entry exists and belongs to this NC const entryRow = await ds.query( `SELECT id, nc_id, document_path FROM ncr_entries WHERE id = ? AND nc_id = ?`, [entryId, ncId], ); if (!entryRow.length) { throw new NotFoundException( `Finding ${entryId} not found on NC ${ncId} in ${source}`, ); } const oldDocumentPath = entryRow[0].document_path; // 3. Save file to disk let saved; try { saved = await this.fileStorage.saveEntryEvidence(source, ncId, entryId, { originalname: file.originalname, mimetype: file.mimetype, buffer: file.buffer, size: file.size, }); } catch (err: any) { throw new BadRequestException(err.message); } // 4. Update DB await ds.query( `UPDATE ncr_entries SET document_path = ?, updated_at = NOW() WHERE id = ?`, [saved.dbPath, entryId], ); // 5. Delete the previous file IF it was a new-format file // Legacy files are NEVER deleted β€” they belong to Laravel. if (oldDocumentPath && this.fileStorage.isNewPath(oldDocumentPath)) { await this.fileStorage .deleteNewFile(oldDocumentPath, source) .catch((e) => { this.logger.warn( `Failed to delete previous file ${oldDocumentPath}: ${e.message}`, ); }); } this.logger.log( `[NC-EVIDENCE] User ${currentUserId} uploaded evidence for ${source}/NC${ncId}/entry${entryId}: ${saved.dbPath}`, ); return { ok: true, entry_id: entryId, document_path: saved.dbPath, filename: file.originalname, size: saved.size, uploaded_at: new Date().toISOString(), }; } /** * Open a finding's evidence file for streaming. * Works transparently for both NEW (NestJS storage) and LEGACY (Laravel) files. */ async openEntryFile( source: 'QRS' | 'TQS' | 'NEW', ncId: number, entryId: number, index: number = 0, // πŸ‘ˆ NEW: optional index for multi-file findings ): Promise<{ stream: Readable; size: number; mimeType: string; filename: string; }> { const ds = source === 'QRS' ? this.qrsDataSource : source === 'TQS' ? this.tqsDataSource : this.schemeDb; // 'NEW' const rows = await ds.query( `SELECT id, document_path FROM ncr_entries WHERE id = ? AND nc_id = ?`, [entryId, ncId], ); if (!rows.length) { throw new NotFoundException( `Finding ${entryId} not found on NC ${ncId} in ${source}`, ); } const documentPath = rows[0].document_path; if (!documentPath) { throw new NotFoundException(`No evidence file uploaded for this finding`); } // πŸ‘‡ NEW: unwrap the client-portal JSON-array format if present. // Client uploads via NcService.clientRespondToFinding save the path as // JSON like: [{"path":"new:123/xxx.pdf","name":"xxx.pdf"}, ...] // Legacy internal uploads still save a plain string β€” both must work. let resolvedPath: string = String(documentPath).trim(); if (resolvedPath.startsWith('[')) { try { const arr = JSON.parse(resolvedPath); if (!Array.isArray(arr) || arr.length === 0) { throw new NotFoundException(`No evidence file uploaded for this finding`); } const safeIdx = Math.min(Math.max(0, index || 0), arr.length - 1); const item = arr[safeIdx]; const pathValue = typeof item === 'string' ? item : item?.path; if (!pathValue) { throw new NotFoundException(`Evidence entry ${safeIdx} has no path`); } resolvedPath = String(pathValue).trim(); this.logger.log( `[openEntryFile] Client-portal JSON path detected. Using index=${safeIdx} of ${arr.length} β†’ ${resolvedPath}`, ); } catch (err: any) { if (err instanceof NotFoundException) throw err; this.logger.error(`[openEntryFile] Failed to parse JSON document_path: ${err.message}`); throw new NotFoundException(`Invalid evidence path format`); } } return this.fileStorage.openFile(resolvedPath, source); } /** * Delete a finding's evidence. * - NEW files: deleted from disk + DB cleared * - LEGACY files: only DB cleared (Laravel file untouched on disk) */ async deleteEntryEvidence( source: 'QRS' | 'TQS' | 'NEW', ncId: number, entryId: number, currentUserId: number, ): Promise<{ ok: true; entry_id: number; deleted_file: boolean; }> { const canViewAll = await this.userCanViewAll(currentUserId); const ds = source === 'QRS' ? this.qrsDataSource : source === 'TQS' ? this.tqsDataSource : this.schemeDb; // 'NEW' const ncRow = await ds.query( `SELECT id, followed_up_by, closed_by FROM nc__ncs WHERE id = ?`, [ncId], ); if (!ncRow.length) { throw new NotFoundException(`NC ${ncId} not found in ${source}`); } if (!canViewAll) { const r = ncRow[0]; if (r.followed_up_by !== currentUserId && r.closed_by !== currentUserId) { throw new ForbiddenException( `You do not have permission to delete evidence for NC ${ncId}`, ); } } const entryRow = await ds.query( `SELECT id, document_path FROM ncr_entries WHERE id = ? AND nc_id = ?`, [entryId, ncId], ); if (!entryRow.length) { throw new NotFoundException( `Finding ${entryId} not found on NC ${ncId} in ${source}`, ); } const currentPath = entryRow[0].document_path; let deletedFile = false; if (currentPath && this.fileStorage.isNewPath(currentPath)) { deletedFile = await this.fileStorage.deleteNewFile(currentPath, source); } // Clear DB column regardless of file type await ds.query( `UPDATE ncr_entries SET document_path = NULL, updated_at = NOW() WHERE id = ?`, [entryId], ); this.logger.log( `[NC-EVIDENCE] User ${currentUserId} deleted evidence for ${source}/NC${ncId}/entry${entryId} (was: ${currentPath || 'none'})`, ); return { ok: true, entry_id: entryId, deleted_file: deletedFile }; } async deleteNc( source: 'QRS' | 'TQS' | 'NEW', id: number, currentUserId: number, ): Promise<{ ok: true; deleted_id: number; deleted_entries: number }> { const ds = source === 'QRS' ? this.qrsDataSource : source === 'TQS' ? this.tqsDataSource : this.schemeDb; const existing = await ds.query( `SELECT id, followed_up_by, closed_by, created_by FROM nc__ncs WHERE id = ?`, [id], ); if (!existing.length) { throw new NotFoundException(`NC ${id} not found in ${source}`); } const canViewAll = await this.userCanViewAll(currentUserId); const hasDeletePerm = await this.userHasNcPermission(currentUserId, 'delete'); if (!canViewAll && !hasDeletePerm) { const sourceUid = source === 'NEW' ? currentUserId : await this.resolveSourceUserId(currentUserId, source); const r = existing[0]; const owns = r.followed_up_by === sourceUid || r.closed_by === sourceUid || r.created_by === sourceUid; if (!owns) { throw new ForbiddenException( `You do not have permission to delete NC ${id}`, ); } } const qr = ds.createQueryRunner(); await qr.connect(); await qr.startTransaction(); let deletedEntries = 0; try { await qr.query(`DELETE FROM nc_final_closures WHERE nc_id = ?`, [id]); await qr.query(`DELETE FROM nc_remarks WHERE nc_id = ?`, [id]); const entRes = await qr.query(`DELETE FROM ncr_entries WHERE nc_id = ?`, [id]); deletedEntries = entRes?.affectedRows ?? 0; await qr.query(`DELETE FROM nc__ncs WHERE id = ?`, [id]); await qr.commitTransaction(); } catch (err) { await qr.rollbackTransaction(); this.logger.error( `[PREV-NC-DELETE] Failed for ${source}/${id}: ${(err as Error).message}`, ); throw err; } finally { await qr.release(); } this.logger.log( `[PREV-NC-DELETE] User ${currentUserId} deleted ${source}/NC${id} (entries removed: ${deletedEntries})`, ); return { ok: true, deleted_id: id, deleted_entries: deletedEntries }; } async deleteEntry( source: 'QRS' | 'TQS' | 'NEW', ncId: number, entryId: number, currentUserId: number, ): Promise<{ ok: true; entry_id: number; deleted_file: boolean }> { const ds = source === 'QRS' ? this.qrsDataSource : source === 'TQS' ? this.tqsDataSource : this.schemeDb; const ncRow = await ds.query( `SELECT id, followed_up_by, closed_by, created_by FROM nc__ncs WHERE id = ?`, [ncId], ); if (!ncRow.length) { throw new NotFoundException(`NC ${ncId} not found in ${source}`); } // Permission: view-all role, OR delete permission, OR ownership. const canViewAll = await this.userCanViewAll(currentUserId); const hasDeletePerm = await this.userHasNcPermission(currentUserId, 'delete'); if (!canViewAll && !hasDeletePerm) { const sourceUid = source === 'NEW' ? currentUserId : await this.resolveSourceUserId(currentUserId, source); const r = ncRow[0]; const owns = r.followed_up_by === sourceUid || r.closed_by === sourceUid || r.created_by === sourceUid; if (!owns) { throw new ForbiddenException( `You do not have permission to delete findings on NC ${ncId}`, ); } } // Verify the finding exists and belongs to this NC. const entryRow = await ds.query( `SELECT id, document_path FROM ncr_entries WHERE id = ? AND nc_id = ?`, [entryId, ncId], ); if (!entryRow.length) { throw new NotFoundException( `Finding ${entryId} not found on NC ${ncId} in ${source}`, ); } const documentPath = entryRow[0].document_path; const qr = ds.createQueryRunner(); await qr.connect(); await qr.startTransaction(); try { // Remove closure rows that reference this finding first (FK). await qr.query( `DELETE FROM nc_final_closures WHERE nc_id = ? AND ncrentry_id = ?`, [ncId, entryId], ); await qr.query( `DELETE FROM ncr_entries WHERE id = ? AND nc_id = ?`, [entryId, ncId], ); await qr.commitTransaction(); } catch (err) { await qr.rollbackTransaction(); this.logger.error( `[PREV-NC-ENTRY-DELETE] Failed for ${source}/${ncId}/entry${entryId}: ${(err as Error).message}`, ); throw err; } finally { await qr.release(); } // Delete the evidence file ONLY if it's a NEW-format file (legacy untouched). let deletedFile = false; if (documentPath && this.fileStorage.isNewPath(documentPath)) { deletedFile = await this.fileStorage .deleteNewFile(documentPath, source) .catch(() => false); } this.logger.log( `[PREV-NC-ENTRY-DELETE] User ${currentUserId} deleted finding ${entryId} on ${source}/NC${ncId} (file removed: ${deletedFile})`, ); return { ok: true, entry_id: entryId, deleted_file: deletedFile }; } // ═══════════════════════════════════════════════════════════════ // PRIVATE HELPERS // ═══════════════════════════════════════════════════════════════ // ═══════════════════════════════════════════════════════════════ // πŸ†• AUDIT β†’ NC STATUS (which audits have an NC raised vs pending) // ═══════════════════════════════════════════════════════════════ async auditNcStatusReport( currentUserId: number, q?: { source?: 'QRS' | 'TQS' | 'All'; status?: 'Raised' | 'Pending' | 'All'; date_from?: string; date_to?: string; search?: string; auditor?: string; // πŸ†• year?: number | string; // πŸ†• month?: number | string; // πŸ†• }, ): Promise<{ rows: Array<{ source: 'QRS' | 'TQS'; audit_kind: 'client' | 'surveillance' | 'recertification'; audit_id: number; company_name: string | null; audit_date: string | null; auditor_names: string[]; nc_count: number; nc_status: 'NC Raised' | 'NC Pending'; }>; totals: { total: number; raised: number; pending: number }; can_view_all: boolean; }> { const canViewAll = (await this.userCanViewAll(currentUserId)) || (await this.userHasNcPermission(currentUserId, 'view-all')); // πŸ†• derive a date range from year/month so the existing SQL date filters apply if (q?.year) { const y = Number(q.year); if (q.month && q.month !== 'all' && Number(q.month) > 0) { const m = Number(q.month); const last = new Date(y, m, 0).getDate(); // last day of that month q = { ...q, date_from: `${y}-${String(m).padStart(2, '0')}-01`, date_to: `${y}-${String(m).padStart(2, '0')}-${String(last).padStart(2, '0')}`, }; } else { q = { ...q, date_from: `${y}-01-01`, date_to: `${y}-12-31` }; } } const wantQrs = !q?.source || q.source === 'All' || q.source === 'QRS'; const wantTqs = !q?.source || q.source === 'All' || q.source === 'TQS'; const runForSource = async (ds: DataSource, source: 'QRS' | 'TQS') => { let ownId: number | null = null; if (!canViewAll) { ownId = await this.resolveSourceUserId(currentUserId, source); if (ownId == null) return []; } const params: any[] = []; const pushDate = (col: string) => { let s = ''; if (q?.date_from) { s += ` AND ${col} >= ?`; params.push(q.date_from); } if (q?.date_to) { s += ` AND ${col} <= ?`; params.push(q.date_to); } return s; }; const pushOwn = (col: string) => { if (ownId == null) return ''; params.push(String(ownId)); return ` AND JSON_VALID(${col}) AND JSON_CONTAINS(${col}, JSON_QUOTE(?))`; }; // 1) client initial β€” auditdate const dCA = pushDate('c.auditdate'); const oCA = pushOwn('c.auditassign'); // 2) client surveillance β€” serv_date const dCS = pushDate('c.serv_date'); const oCS = pushOwn('c.auditassign'); // 3) client recertification β€” recert_date const dCR = pushDate('c.recert_date'); const oCR = pushOwn('c.auditassign'); // 4) newsurve surveillance β€” auditdate const dS = pushDate('s.auditdate'); const oS = pushOwn('s.auditassign'); const sql = ` SELECT 'client' AS audit_kind, c.id AS audit_id, c.company_name AS company_name, c.auditdate AS audit_date, c.auditassign AS auditor_ids, COUNT(nc.id) AS nc_count FROM clients__clientdatas c LEFT JOIN nc__ncs nc ON nc.client_id = c.id WHERE c.auditdate IS NOT NULL AND YEAR(c.auditdate) > 0 AND c.auditdate <= CURDATE() AND c.auditassign IS NOT NULL AND c.auditassign <> '' ${dCA} ${oCA} GROUP BY c.id, c.company_name, c.auditdate, c.auditassign UNION ALL SELECT 'surveillance' AS audit_kind, c.id AS audit_id, c.company_name AS company_name, c.serv_date AS audit_date, c.auditassign AS auditor_ids, COUNT(nc.id) AS nc_count FROM clients__clientdatas c LEFT JOIN nc__ncs nc ON nc.client_id = c.id WHERE c.serv_date IS NOT NULL AND YEAR(c.serv_date) > 0 AND c.serv_date <= CURDATE() AND c.auditassign IS NOT NULL AND c.auditassign <> '' ${dCS} ${oCS} GROUP BY c.id, c.company_name, c.serv_date, c.auditassign UNION ALL SELECT 'recertification' AS audit_kind, c.id AS audit_id, c.company_name AS company_name, c.recert_date AS audit_date, c.auditassign AS auditor_ids, COUNT(nc.id) AS nc_count FROM clients__clientdatas c LEFT JOIN nc__ncs nc ON nc.client_id = c.id WHERE c.recert_date IS NOT NULL AND YEAR(c.recert_date) > 0 AND c.recert_date <= CURDATE() AND c.auditassign IS NOT NULL AND c.auditassign <> '' ${dCR} ${oCR} GROUP BY c.id, c.company_name, c.recert_date, c.auditassign UNION ALL SELECT 'surveillance' AS audit_kind, s.id AS audit_id, s.company_name AS company_name, s.auditdate AS audit_date, s.auditassign AS auditor_ids, COUNT(nc.id) AS nc_count FROM newsurve__surves s LEFT JOIN nc__ncs nc ON nc.serve_id = s.id WHERE s.auditdate IS NOT NULL AND YEAR(s.auditdate) > 0 AND s.auditdate <= CURDATE() AND s.auditassign IS NOT NULL AND s.auditassign <> '' ${dS} ${oS} GROUP BY s.id, s.company_name, s.auditdate, s.auditassign ORDER BY audit_date DESC `; const raw: any[] = await ds.query(sql, params).catch((e) => { this.logger.warn(`[AUDIT-NC-STATUS] ${source} query failed: ${e.message}`); return []; }); if (!raw.length) return []; const idSet = new Set(); for (const r of raw) { try { const arr = JSON.parse(r.auditor_ids || '[]'); if (Array.isArray(arr)) arr.forEach((x) => { const n = Number(x); if (!isNaN(n)) idSet.add(n); }); } catch { /* ignore */ } } let nameMap = new Map(); if (idSet.size) { const ids = Array.from(idSet); const users: any[] = await ds.query( `SELECT id, TRIM(CONCAT(COALESCE(first_name,''),' ',COALESCE(last_name,''))) AS name FROM users WHERE id IN (?)`, [ids], ).catch(() => []); nameMap = new Map(users.map((u: any) => [Number(u.id), (u.name || '').trim()])); } return raw.map((r) => { let auditorNames: string[] = []; try { const arr = JSON.parse(r.auditor_ids || '[]'); if (Array.isArray(arr)) auditorNames = arr.map((x) => nameMap.get(Number(x)) || `User #${x}`).filter(Boolean); } catch { /* ignore */ } const ncCount = Number(r.nc_count || 0); return { source, audit_kind: r.audit_kind as 'client' | 'surveillance' | 'recertification', audit_id: Number(r.audit_id), company_name: (r.company_name || '').trim() || null, audit_date: r.audit_date ?? null, auditor_names: auditorNames, nc_count: ncCount, nc_status: (ncCount > 0 ? 'NC Raised' : 'NC Pending') as 'NC Raised' | 'NC Pending', }; }); }; const [qrsRows, tqsRows] = await Promise.all([ wantQrs ? runForSource(this.qrsDataSource, 'QRS') : Promise.resolve([]), wantTqs ? runForSource(this.tqsDataSource, 'TQS') : Promise.resolve([]), ]); let rows = [...qrsRows, ...tqsRows]; if (q?.status && q.status !== 'All') { const want = q.status === 'Raised' ? 'NC Raised' : 'NC Pending'; rows = rows.filter((r) => r.nc_status === want); } const search = (q?.search || '').trim().toLowerCase(); if (search) rows = rows.filter((r) => (r.company_name || '').toLowerCase().includes(search)); // πŸ†• auditor filter (case-insensitive, matches the frontend dropdown) const wantAuditor = (q?.auditor || '').trim().toLowerCase(); if (wantAuditor) { rows = rows.filter((r) => r.auditor_names.some((n) => n.trim().toLowerCase() === wantAuditor), ); } rows.sort((a, b) => new Date(b.audit_date ?? 0).getTime() - new Date(a.audit_date ?? 0).getTime()); const totals = rows.reduce( (acc, r) => { acc.total++; r.nc_status === 'NC Raised' ? acc.raised++ : acc.pending++; return acc; }, { total: 0, raised: 0, pending: 0 }, ); this.logger.log( `[AUDIT-NC-STATUS] user ${currentUserId} viewAll=${canViewAll} β†’ ${rows.length} conducted audits (raised=${totals.raised} pending=${totals.pending})`, ); return { rows, totals, can_view_all: canViewAll }; } private async resolveStandardNames( standardJson: string | null, standardRepo: Repository, ): Promise { if (!standardJson) return []; let ids: number[] = []; try { const parsed = JSON.parse(standardJson); if (Array.isArray(parsed)) { ids = parsed.map((x) => Number(x)).filter((x) => !isNaN(x)); } } catch { return []; } if (!ids.length) return []; const standards = await standardRepo.find({ where: { id: In(ids) } }); return standards.map((s) => s.name); } private fullName(u: PreviousNcUserEntity | null | undefined): string | null { if (!u) return null; const first = (u.first_name || '').trim(); const last = (u.last_name || '').trim(); const joined = `${first} ${last}`.trim(); return joined || u.email || null; } // previous-nc.service.ts β€” add this method async draftFinding(nc_type: string, note: string) { const url = process.env.NC_MODEL_URL || 'http://127.0.0.1:8000'; try { const res = await fetch(`${url}/draft`, { method: 'POST', headers: { 'Content-Type': 'application/json' }, body: JSON.stringify({ nc_type, note }), }); if (!res.ok) throw new Error(`Model service failed (${res.status})`); return res.json(); } catch (err: any) { this.logger.error(`[NC-AI] draft failed: ${err.message}`); // Fail soft β€” auditor can still type manually return { nc_type, statement: '', clause: '', corrective_action: '', error: true }; } } }