diff options
Diffstat (limited to 'lib/pq/service.ts')
| -rw-r--r-- | lib/pq/service.ts | 6559 |
1 files changed, 3678 insertions, 2881 deletions
diff --git a/lib/pq/service.ts b/lib/pq/service.ts index 18d1a5d3..ac1b9e87 100644 --- a/lib/pq/service.ts +++ b/lib/pq/service.ts @@ -1,2882 +1,3679 @@ -"use server" - -import db from "@/db/db" -import { GetPQSchema, GetPQSubmissionsSchema } from "./validations" -import { unstable_cache } from "@/lib/unstable-cache"; -import { filterColumns } from "@/lib/filter-columns"; -import { getErrorMessage } from "@/lib/handle-error"; -import { asc, desc, ilike, inArray, and, gte, lte, not, or, eq, count,isNull,SQL} from "drizzle-orm"; -import { z } from "zod" -import { revalidateTag, unstable_noStore, revalidatePath} from "next/cache"; -import { pqCriterias, pqCriteriasExtension, vendorCriteriaAttachments, vendorInvestigations, vendorPQSubmissions, vendorPqCriteriaAnswers, vendorPqReviewLogs, vendorProjectPQs } from "@/db/schema/pq" -import { countPqs, selectPqs } from "./repository"; -import { sendEmail } from "../mail/sendEmail"; -import { vendorAttachments, vendors } from "@/db/schema/vendors"; -import path from 'path'; -import fs from 'fs/promises'; -import { randomUUID } from 'crypto'; -import { writeFile, mkdir } from 'fs/promises'; -import { GetVendorsSchema } from "../vendors/validations"; -import { countVendors, selectVendors } from "../vendors/repository"; -import { projects, users } from "@/db/schema"; -import { headers } from 'next/headers'; -import { getServerSession } from "next-auth/next" -import { authOptions } from "@/app/api/auth/[...nextauth]/route" -import { alias } from 'drizzle-orm/pg-core'; -import { createPQFilterMapping, getPQJoinedTables } from "./helper"; - -/** - * PQ 목록 조회 - */ -export async function getPQs( - input: GetPQSchema, - projectId?: number | null, - onlyGeneral?: boolean -) { - return unstable_cache( - async () => { - try { - // Common query building logic extracted to a helper function - const buildBaseQuery = (queryBuilder: any) => { - let query = queryBuilder.from(pqCriterias); - - // Handle join conditions based on parameters - if (projectId) { - query = query - .innerJoin( - pqCriteriasExtension, - eq(pqCriterias.id, pqCriteriasExtension.pqCriteriaId) - ) - .where(eq(pqCriteriasExtension.projectId, projectId)); - } else if (onlyGeneral) { - query = query - .leftJoin( - pqCriteriasExtension, - eq(pqCriterias.id, pqCriteriasExtension.pqCriteriaId) - ) - .where(isNull(pqCriteriasExtension.id)); - } - - // Apply filters - const advancedWhere = filterColumns({ - table: pqCriterias, - filters: input.filters, - joinOperator: input.joinOperator, - }); - - // Handle search - let globalWhere; - if (input.search) { - const s = `%${input.search}%`; - globalWhere = or( - ilike(pqCriterias.code, s), - ilike(pqCriterias.groupName, s), - ilike(pqCriterias.remarks, s), - ilike(pqCriterias.checkPoint, s), - ilike(pqCriterias.description, s) - ); - } - - // Combine where clauses - const finalWhere = and(advancedWhere, globalWhere); - if (finalWhere) { - query = query.where(finalWhere); - } - - return { query, finalWhere }; - }; - - const offset = (input.page - 1) * input.perPage; - - // Build sort order configuration - const orderBy = input.sort?.length > 0 - ? input.sort.map((item) => - item.desc - ? desc(pqCriterias[item.id]) - : asc(pqCriterias[item.id]) - ) - : [asc(pqCriterias.createdAt)]; - - // Execute in a transaction - const { data, total } = await db.transaction(async (tx) => { - // 변경: 쿼리 결과 형태를 변경하여 데이터가 평탄화되도록 수정 - // Data query - const { query: baseQuery } = buildBaseQuery(tx.select({ - id: pqCriterias.id, - code: pqCriterias.code, - checkPoint: pqCriterias.checkPoint, - description: pqCriterias.description, - remarks: pqCriterias.remarks, - groupName: pqCriterias.groupName, - createdAt: pqCriterias.createdAt, - updatedAt: pqCriterias.updatedAt, - // 필요한 경우 pqCriteriasExtension의 필드도 여기에 추가 - })); - - const data = await baseQuery - .orderBy(...orderBy) - .offset(offset) - .limit(input.perPage); - - // Count query - reusing the same base query logic - const { query: countQuery } = buildBaseQuery(tx.select({ count: count() })); - const countRes = await countQuery; - const total = countRes[0]?.count ?? 0; - - return { data, total }; - }); - - // Calculate page count - const pageCount = Math.ceil(total / input.perPage); - - // 이미 평탄화된 객체 배열 형태로 반환됨 - return { data, pageCount }; - } catch (err) { - console.log('Error in getPQs:', err); - console.error('Error in getPQs:', err); - throw new Error('Failed to fetch PQ criteria'); - } - }, - [JSON.stringify(input), projectId?.toString() ?? 'undefined', onlyGeneral?.toString() ?? 'undefined'], - { - revalidate: 3600, - tags: ["pq"], - } - )(); -} - -// PQ 생성을 위한 입력 스키마 정의 -const createPqSchema = z.object({ - code: z.string().min(1, "Code is required"), - checkPoint: z.string().min(1, "Check point is required"), - description: z.string().optional().nullable(), - remarks: z.string().optional().nullable(), - groupName: z.string().optional() -}); - -export interface CreatePqInputType extends z.infer<typeof createPqSchema> { - projectId?: number | null; - contractInfo?: string | null; - additionalRequirement?: string | null; -} - -/** - * PQ 기준 생성 - */ -export async function createPq(input: CreatePqInputType) { - try { - // 기본 데이터 유효성 검증 - const validatedData = createPqSchema.parse(input); - - // 프로젝트 정보 및 확장 필드 확인 - const isProjectSpecific = !!input.projectId; - - // 트랜잭션 사용하여 PQ 기준 생성 - return await db.transaction(async (tx) => { - // 1. 기본 PQ 기준 생성 - const [newPqCriteria] = await tx - .insert(pqCriterias) - .values({ - code: validatedData.code, - checkPoint: validatedData.checkPoint, - description: validatedData.description || null, - remarks: validatedData.remarks || null, - groupName: validatedData.groupName || null, - }) - .returning({ id: pqCriterias.id }); - - // 2. 프로젝트별 PQ인 경우 확장 테이블에도 데이터 추가 - if (isProjectSpecific && input.projectId) { - await tx - .insert(pqCriteriasExtension) - .values({ - pqCriteriaId: newPqCriteria.id, - projectId: input.projectId, - contractInfo: input.contractInfo || null, - additionalRequirement: input.additionalRequirement || null, - }); - } - - // 성공 결과 반환 - return { - success: true, - pqId: newPqCriteria.id, - isProjectSpecific, - message: isProjectSpecific - ? "Project-specific PQ criteria created successfully" - : "General PQ criteria created successfully" - }; - }); - } catch (error) { - console.error("Error creating PQ criteria:", error); - - // Zod 유효성 검사 에러 처리 - if (error instanceof z.ZodError) { - return { - success: false, - message: "Validation failed", - errors: error.errors - }; - } - - // 기타 에러 처리 - return { - success: false, - message: "Failed to create PQ criteria" - }; - } -} -// PQ 캐시 무효화 함수 -export async function invalidatePqCache() { - revalidatePath(`/evcp/pq-criteria`); - revalidateTag(`pq`); -} - -// PQ 삭제를 위한 스키마 정의 -const removePqsSchema = z.object({ - ids: z.array(z.number()).min(1, "At least one PQ ID is required") -}); - -export type RemovePqsInputType = z.infer<typeof removePqsSchema>; - -/** - * PQ 기준 삭제 - */ -export async function removePqs(input: RemovePqsInputType) { - try { - // 입력 유효성 검증 - const validatedData = removePqsSchema.parse(input); - - // 트랜잭션 사용하여 PQ 기준 삭제 - await db.transaction(async (tx) => { - // PQ 기준 테이블에서 삭제 - await tx - .delete(pqCriterias) - .where(inArray(pqCriterias.id, validatedData.ids)); - }); - - // 캐시 무효화 - await invalidatePqCache(); - - return { success: true }; - } catch (error) { - console.error("Error removing PQ criteria:", error); - - // Zod 유효성 검사 에러 처리 - if (error instanceof z.ZodError) { - return { - success: false, - error: "Validation failed: " + error.errors.map(e => e.message).join(', ') - }; - } - - // 기타 에러 처리 - return { - success: false, - error: "Failed to remove PQ criteria" - }; - } -} - -// PQ 수정을 위한 스키마 정의 -const modifyPqSchema = z.object({ - id: z.number().positive("ID is required"), - code: z.string().min(1, "Code is required"), - checkPoint: z.string().min(1, "Check point is required"), - groupName: z.string().min(1, "Group is required"), - description: z.string().optional(), - remarks: z.string().optional() -}); - -export type ModifyPqInputType = z.infer<typeof modifyPqSchema>; - - -export async function modifyPq(input: ModifyPqInputType) { - try { - // 입력 유효성 검증 - const validatedData = modifyPqSchema.parse(input); - - // 트랜잭션 사용하여 PQ 기준 수정 - return await db.transaction(async (tx) => { - // PQ 기준 수정 - await tx - .update(pqCriterias) - .set({ - code: validatedData.code, - checkPoint: validatedData.checkPoint, - description: validatedData.description || null, - remarks: validatedData.remarks || null, - groupName: validatedData.groupName, - updatedAt: new Date(), - }) - .where(eq(pqCriterias.id, validatedData.id)); - - // 성공 결과 반환 - return { - success: true, - message: "PQ criteria updated successfully" - }; - }); - } catch (error) { - console.error("Error updating PQ criteria:", error); - - // Zod 유효성 검사 에러 처리 - if (error instanceof z.ZodError) { - return { - success: false, - error: "Validation failed: " + error.errors.map(e => e.message).join(', ') - }; - } - - // 기타 에러 처리 - return { - success: false, - error: "Failed to update PQ criteria" - }; - } finally { - // 캐시 무효화 - revalidatePath(`/partners/pq`); - revalidateTag(`pq`); - } -} - -export interface PQAttachment { - attachId: number - fileName: string - filePath: string - fileSize?: number -} - -export interface PQItem { - answerId: number | null - criteriaId: number - code: string - checkPoint: string - description: string | null - remarks?: string | null - // 프로젝트 PQ 전용 필드 - contractInfo?: string | null - additionalRequirement?: string | null - answer: string - attachments: PQAttachment[] -} - -export interface PQGroupData { - groupName: string - items: PQItem[] -} - -export interface ProjectPQ { - id: number; - projectId: number; - status: string; - submittedAt: Date | null; - projectCode: string; - projectName: string; -} - -export async function getPQProjectsByVendorId(vendorId: number): Promise<ProjectPQ[]> { - const result = await db - .select({ - id: vendorPQSubmissions.id, - projectId: vendorPQSubmissions.projectId, - status: vendorPQSubmissions.status, - submittedAt: vendorPQSubmissions.submittedAt, - projectCode: projects.code, - projectName: projects.name, - }) - .from(vendorPQSubmissions) - .innerJoin( - projects, - eq(vendorPQSubmissions.projectId, projects.id) - ) - .where(eq(vendorPQSubmissions.vendorId, vendorId)) - .orderBy(projects.code); - - return result; -} - -export async function getPQDataByVendorId( - vendorId: number, - projectId?: number -): Promise<PQGroupData[]> { - try { - // 기본 쿼리 구성 - const selectObj = { - criteriaId: pqCriterias.id, - groupName: pqCriterias.groupName, - code: pqCriterias.code, - checkPoint: pqCriterias.checkPoint, - description: pqCriterias.description, - remarks: pqCriterias.remarks, - - // 프로젝트 PQ 추가 필드 - contractInfo: pqCriteriasExtension.contractInfo, - additionalRequirement: pqCriteriasExtension.additionalRequirement, - - // 협력업체 응답 필드 - answer: vendorPqCriteriaAnswers.answer, - answerId: vendorPqCriteriaAnswers.id, - - // 첨부 파일 필드 - attachId: vendorCriteriaAttachments.id, - fileName: vendorCriteriaAttachments.fileName, - filePath: vendorCriteriaAttachments.filePath, - fileSize: vendorCriteriaAttachments.fileSize, - }; - - // Create separate queries for each case instead of modifying the same query variable - if (projectId) { - // 프로젝트별 PQ 쿼리 - const rows = await db - .select(selectObj) - .from(pqCriterias) - .innerJoin( - pqCriteriasExtension, - and( - eq(pqCriterias.id, pqCriteriasExtension.pqCriteriaId), - eq(pqCriteriasExtension.projectId, projectId) - ) - ) - .leftJoin( - vendorPqCriteriaAnswers, - and( - eq(pqCriterias.id, vendorPqCriteriaAnswers.criteriaId), - eq(vendorPqCriteriaAnswers.vendorId, vendorId), - eq(vendorPqCriteriaAnswers.projectId, projectId) - ) - ) - .leftJoin( - vendorCriteriaAttachments, - eq(vendorPqCriteriaAnswers.id, vendorCriteriaAttachments.vendorCriteriaAnswerId) - ) - .orderBy(pqCriterias.groupName, pqCriterias.code); - - return processQueryResults(rows); - } else { - // 일반 PQ 쿼리 - const rows = await db - .select(selectObj) - .from(pqCriterias) - .leftJoin( - pqCriteriasExtension, - eq(pqCriterias.id, pqCriteriasExtension.pqCriteriaId) - ) - .where(isNull(pqCriteriasExtension.id)) - .leftJoin( - vendorPqCriteriaAnswers, - and( - eq(pqCriterias.id, vendorPqCriteriaAnswers.criteriaId), - eq(vendorPqCriteriaAnswers.vendorId, vendorId), - isNull(vendorPqCriteriaAnswers.projectId) - ) - ) - .leftJoin( - vendorCriteriaAttachments, - eq(vendorPqCriteriaAnswers.id, vendorCriteriaAttachments.vendorCriteriaAnswerId) - ) - .orderBy(pqCriterias.groupName, pqCriterias.code); - - return processQueryResults(rows); - } - } catch (error) { - console.error("Error fetching PQ data:", error); - return []; - } - - // Helper function to process query results - function processQueryResults(rows: any[]) { - // 그룹별로 데이터 구성 - const groupMap = new Map<string, Record<number, PQItem>>(); - - for (const row of rows) { - const g = row.groupName || "Others"; - - // 그룹 확인 - if (!groupMap.has(g)) { - groupMap.set(g, {}); - } - - const groupItems = groupMap.get(g)!; - - // 아직 이 기준을 처리하지 않았으면 PQItem 생성 - if (!groupItems[row.criteriaId]) { - groupItems[row.criteriaId] = { - answerId: row.answerId, - criteriaId: row.criteriaId, - code: row.code, - checkPoint: row.checkPoint, - description: row.description, - remarks: row.remarks, - // 프로젝트 PQ 전용 필드 - contractInfo: row.contractInfo, - additionalRequirement: row.additionalRequirement, - answer: row.answer || "", - attachments: [], - }; - } - - // 첨부 파일이 있으면 추가 - if (row.attachId) { - groupItems[row.criteriaId].attachments.push({ - attachId: row.attachId, - fileName: row.fileName || "", - filePath: row.filePath || "", - fileSize: row.fileSize || undefined, - }); - } - } - - // 최종 데이터 구성 - const data: PQGroupData[] = []; - for (const [groupName, itemsMap] of groupMap.entries()) { - const items = Object.values(itemsMap); - data.push({ groupName, items }); - } - - return data; - } -} - - -interface PQAttachmentInput { - fileName: string // original user-friendly file name - url: string // the UUID-based path stored on server - size?: number // optional file size -} - -interface SavePQAnswer { - criteriaId: number - answer: string - attachments: PQAttachmentInput[] -} - -interface SavePQInput { - vendorId: number - projectId?: number - answers: SavePQAnswer[] -} - -/** - * 여러 항목을 한 번에 Upsert - */ -export async function savePQAnswersAction(input: SavePQInput) { - const { vendorId, projectId, answers } = input - - try { - for (const ans of answers) { - // 1) Check if a row already exists for (vendorId, criteriaId, projectId) - const queryConditions = [ - eq(vendorPqCriteriaAnswers.vendorId, vendorId), - eq(vendorPqCriteriaAnswers.criteriaId, ans.criteriaId) - ]; - - // Add projectId condition when it exists - if (projectId !== undefined) { - queryConditions.push(eq(vendorPqCriteriaAnswers.projectId, projectId)); - } else { - queryConditions.push(isNull(vendorPqCriteriaAnswers.projectId)); - } - - const existing = await db - .select() - .from(vendorPqCriteriaAnswers) - .where(and(...queryConditions)); - - let answerId: number - - // 2) If it exists, update the row; otherwise insert - if (existing.length === 0) { - // Insert new - const inserted = await db - .insert(vendorPqCriteriaAnswers) - .values({ - vendorId, - criteriaId: ans.criteriaId, - projectId: projectId || null, // Include projectId when provided - answer: ans.answer, - }) - .returning({ id: vendorPqCriteriaAnswers.id }) - - answerId = inserted[0].id - } else { - // Update existing - answerId = existing[0].id - - await db - .update(vendorPqCriteriaAnswers) - .set({ - answer: ans.answer, - updatedAt: new Date(), - }) - .where(eq(vendorPqCriteriaAnswers.id, answerId)) - } - - // 3) Now manage attachments in vendorCriteriaAttachments - // 3a) Load old attachments from DB - const oldAttachments = await db - .select({ - id: vendorCriteriaAttachments.id, - filePath: vendorCriteriaAttachments.filePath, - }) - .from(vendorCriteriaAttachments) - .where(eq(vendorCriteriaAttachments.vendorCriteriaAnswerId, answerId)) - - // 3b) Gather the new filePaths (urls) from the client - const newPaths = ans.attachments.map(a => a.url) - - // 3c) Find attachments to remove - const toRemove = oldAttachments.filter(old => !newPaths.includes(old.filePath)) - if (toRemove.length > 0) { - const removeIds = toRemove.map(r => r.id) - await db - .delete(vendorCriteriaAttachments) - .where(inArray(vendorCriteriaAttachments.id, removeIds)) - } - - // 3d) Insert new attachments that aren't in DB - const oldPaths = oldAttachments.map(o => o.filePath) - const toAdd = ans.attachments.filter(a => !oldPaths.includes(a.url)) - - for (const attach of toAdd) { - await db.insert(vendorCriteriaAttachments).values({ - vendorCriteriaAnswerId: answerId, - fileName: attach.fileName, - filePath: attach.url, - fileSize: attach.size ?? null, - }) - } - } - - return { ok: true } - } catch (error) { - console.error("savePQAnswersAction error:", error) - return { ok: false, error: String(error) } - } -} - - - -/** - * PQ 제출 서버 액션 - 협력업체 상태를 PQ_SUBMITTED로 업데이트 - * @param vendorId 협력업체 ID - */ -export async function submitPQAction({ - vendorId, - projectId, - pqSubmissionId -}: { - vendorId: number; - projectId?: number; - pqSubmissionId?: number; // 특정 PQ 제출 ID가 있는 경우 사용 -}) { - unstable_noStore(); - - try { - const headersList = await headers(); - const host = headersList.get('host') || 'localhost:3000'; - - // 1. 모든 PQ 항목에 대한 응답이 있는지 검증 - const answerQueryConditions = [ - eq(vendorPqCriteriaAnswers.vendorId, vendorId) - ]; - - // Add projectId condition when it exists - if (projectId !== undefined) { - answerQueryConditions.push(eq(vendorPqCriteriaAnswers.projectId, projectId)); - } else { - answerQueryConditions.push(isNull(vendorPqCriteriaAnswers.projectId)); - } - - const pqCriteriaCount = await db - .select({ count: count() }) - .from(vendorPqCriteriaAnswers) - .where(and(...answerQueryConditions)); - - const totalPqCriteriaCount = pqCriteriaCount[0]?.count || 0; - - // 응답 데이터 검증 - if (totalPqCriteriaCount === 0) { - return { ok: false, error: "No PQ answers found" }; - } - - // 2. 협력업체 정보 조회 - const vendor = await db - .select({ - id: vendors.id, - vendorName: vendors.vendorName, - email: vendors.email, - status: vendors.status, - }) - .from(vendors) - .where(eq(vendors.id, vendorId)) - .then(rows => rows[0]); - - if (!vendor) { - return { ok: false, error: "Vendor not found" }; - } - - // Project 정보 조회 (projectId가 있는 경우) - let projectName = ''; - if (projectId) { - const projectData = await db - .select({ - projectName: projects.name - }) - .from(projects) - .where(eq(projects.id, projectId)) - .then(rows => rows[0]); - - projectName = projectData?.projectName || 'Unknown Project'; - } - - // 3. 현재 PQ 제출 상태 확인 및 업데이트 - const currentDate = new Date(); - let existingSubmission; - - // 특정 PQ Submission ID가 있는 경우 - if (pqSubmissionId) { - existingSubmission = await db - .select({ - id: vendorPQSubmissions.id, - status: vendorPQSubmissions.status, - type: vendorPQSubmissions.type - }) - .from(vendorPQSubmissions) - .where( - and( - eq(vendorPQSubmissions.id, pqSubmissionId), - eq(vendorPQSubmissions.vendorId, vendorId) - ) - ) - .then(rows => rows[0]); - - if (!existingSubmission) { - return { ok: false, error: "PQ submission not found or access denied" }; - } - } - // ID가 없는 경우 vendorId와 projectId로 조회 - else { - const pqType = projectId ? "PROJECT" : "GENERAL"; - - const submissionQueryConditions = [ - eq(vendorPQSubmissions.vendorId, vendorId), - eq(vendorPQSubmissions.type, pqType) - ]; - - if (projectId) { - submissionQueryConditions.push(eq(vendorPQSubmissions.projectId, projectId)); - } else { - submissionQueryConditions.push(isNull(vendorPQSubmissions.projectId)); - } - - existingSubmission = await db - .select({ - id: vendorPQSubmissions.id, - status: vendorPQSubmissions.status, - type: vendorPQSubmissions.type - }) - .from(vendorPQSubmissions) - .where(and(...submissionQueryConditions)) - .then(rows => rows[0]); - } - - // 제출 가능한 상태 확인 - const allowedStatuses = ["REQUESTED", "IN_PROGRESS", "REJECTED"]; - - if (existingSubmission) { - if (!allowedStatuses.includes(existingSubmission.status)) { - return { - ok: false, - error: `Cannot submit PQ in current status: ${existingSubmission.status}` - }; - } - - // 기존 제출 상태 업데이트 - await db - .update(vendorPQSubmissions) - .set({ - status: "SUBMITTED", - submittedAt: currentDate, - updatedAt: currentDate, - }) - .where(eq(vendorPQSubmissions.id, existingSubmission.id)); - } else { - // PQ Submission ID가 없고 기존 submission도 없는 경우 새로운 제출 생성 - const pqType = projectId ? "PROJECT" : "GENERAL"; - await db - .insert(vendorPQSubmissions) - .values({ - vendorId, - projectId: projectId || null, - type: pqType, - status: "SUBMITTED", - submittedAt: currentDate, - createdAt: currentDate, - updatedAt: currentDate, - }); - } - - // 4. 일반 PQ인 경우 벤더 상태도 업데이트 - if (!projectId) { - const allowedVendorStatuses = ["IN_PQ", "PENDING_REVIEW", "IN_REVIEW", "REJECTED", "PQ_FAILED"]; - - if (allowedVendorStatuses.includes(vendor.status)) { - await db - .update(vendors) - .set({ - status: "PQ_SUBMITTED", - updatedAt: currentDate, - }) - .where(eq(vendors.id, vendorId)); - } - } - - // 5. 관리자에게 이메일 알림 발송 - if (process.env.ADMIN_EMAIL) { - try { - const emailSubject = projectId - ? `[eVCP] Project PQ Submitted: ${vendor.vendorName} for ${projectName}` - : `[eVCP] General PQ Submitted: ${vendor.vendorName}`; - - const adminUrl = `http://${host}/evcp/pq/${vendorId}/${existingSubmission?.id || ''}`; - - await sendEmail({ - to: process.env.ADMIN_EMAIL, - subject: emailSubject, - template: "pq-submitted-admin", - context: { - vendorName: vendor.vendorName, - vendorId: vendor.id, - projectId: projectId, - projectName: projectName, - isProjectPQ: !!projectId, - submittedDate: currentDate.toLocaleString(), - adminUrl, - } - }); - } catch (emailError) { - console.error("Failed to send admin notification:", emailError); - } - } - - // 6. 벤더에게 확인 이메일 발송 - if (vendor.email) { - try { - const emailSubject = projectId - ? `[eVCP] Project PQ Submission Confirmation for ${projectName}` - : "[eVCP] General PQ Submission Confirmation"; - - const portalUrl = `${host}/partners/pq`; - - await sendEmail({ - to: vendor.email, - subject: emailSubject, - template: "pq-submitted-vendor", - context: { - vendorName: vendor.vendorName, - projectId: projectId, - projectName: projectName, - isProjectPQ: !!projectId, - submittedDate: currentDate.toLocaleString(), - portalUrl, - } - }); - } catch (emailError) { - console.error("Failed to send vendor confirmation:", emailError); - } - } - - // 7. 캐시 무효화 - revalidateTag("vendors"); - revalidateTag("vendor-status-counts"); - revalidateTag(`vendor-pq-submissions-${vendorId}`); - - if (projectId) { - revalidateTag(`project-pq-submissions-${projectId}`); - revalidateTag(`project-vendors-${projectId}`); - revalidateTag(`project-pq-${projectId}`); - } - - return { ok: true }; - } catch (error) { - console.error("PQ submit error:", error); - return { ok: false, error: getErrorMessage(error) }; - } -} - -/** - * 향상된 파일 업로드 서버 액션 - * - 직접 파일 처리 (file 객체로 받음) - * - 디렉토리 자동 생성 - * - 중복 방지를 위한 UUID 적용 - */ -export async function uploadFileAction(file: File) { - unstable_noStore(); - - try { - // 파일 유효성 검사 - if (!file || file.size === 0) { - throw new Error("Invalid file"); - } - - const maxSize = 6e8; - if (file.size > maxSize) { - throw new Error(`File size exceeds limit (${Math.round(maxSize / 1024 / 1024)}MB)`); - } - - // 파일 확장자 가져오기 - const originalFilename = file.name; - const fileExt = path.extname(originalFilename); - const fileNameWithoutExt = path.basename(originalFilename, fileExt); - - // 저장 경로 설정 - const uploadDir = process.env.UPLOAD_DIR - ? process.env.UPLOAD_DIR - : path.join(process.cwd(), "public", "uploads") - const datePrefix = new Date().toISOString().slice(0, 10).replace(/-/g, ''); // YYYYMMDD - const targetDir = path.join(uploadDir, 'pq', datePrefix); - - // UUID로 고유 파일명 생성 - const uuid = randomUUID(); - const sanitizedFilename = fileNameWithoutExt - .replace(/[^a-zA-Z0-9-_]/g, '_') // 안전한 문자만 허용 - .slice(0, 50); // 이름 길이 제한 - - const filename = `${sanitizedFilename}-${uuid}${fileExt}`; - const filePath = path.join(targetDir, filename); - const relativeFilePath = path.join('pq', datePrefix, filename); - - // 디렉토리 생성 (없는 경우) - try { - await mkdir(targetDir, { recursive: true }); - } catch (err) { - console.error("Error creating directory:", err); - throw new Error("Failed to create upload directory"); - } - - // 파일 저장 - const buffer = await file.arrayBuffer(); - await writeFile(filePath, Buffer.from(buffer)); - - // 상대 경로를 반환 (DB에 저장하기 용이함) - const publicUrl = `/uploads/${relativeFilePath.replace(/\\/g, '/')}`; - - return { - fileName: originalFilename, - url: publicUrl, - size: file.size, - }; - } catch (error) { - console.error("File upload error:", error); - throw new Error(`Upload failed: ${getErrorMessage(error)}`); - } -} - -/** - * 여러 파일 일괄 업로드 - */ -export async function uploadMultipleFilesAction(files: File[]) { - unstable_noStore(); - - try { - const results = []; - - for (const file of files) { - try { - const result = await uploadFileAction(file); - results.push({ - success: true, - ...result - }); - } catch (error) { - results.push({ - success: false, - fileName: file.name, - error: getErrorMessage(error) - }); - } - } - - return { - ok: true, - results - }; - } catch (error) { - console.error("Batch upload error:", error); - return { - ok: false, - error: getErrorMessage(error) - }; - } -} - -export async function getVendorsInPQ(input: GetVendorsSchema) { - return unstable_cache( - async () => { - try { - const offset = (input.page - 1) * input.perPage; - - // 1) 고급 필터 - const advancedWhere = filterColumns({ - table: vendors, - filters: input.filters, - joinOperator: input.joinOperator, - }); - - // 2) 글로벌 검색 - let globalWhere: SQL<unknown> | undefined = undefined; - if (input.search) { - const s = `%${input.search}%`; - globalWhere = or( - ilike(vendors.vendorName, s), - ilike(vendors.vendorCode, s), - ilike(vendors.email, s), - ilike(vendors.status, s) - ); - } - - // 트랜잭션 내에서 데이터 조회 - const { data, total } = await db.transaction(async (tx) => { - // 협력업체 ID 모음 (중복 제거용) - const vendorIds = new Set<number>(); - - // 1-A) 일반 PQ 답변이 있는 협력업체 찾기 (status와 상관없이) - const generalPqVendors = await tx - .select({ - vendorId: vendorPqCriteriaAnswers.vendorId - }) - .from(vendorPqCriteriaAnswers) - .innerJoin( - vendors, - eq(vendorPqCriteriaAnswers.vendorId, vendors.id) - ) - .where( - and( - isNull(vendorPqCriteriaAnswers.projectId), // 일반 PQ만 (프로젝트 PQ 아님) - advancedWhere, - globalWhere - ) - ) - .groupBy(vendorPqCriteriaAnswers.vendorId); // 각 벤더당 한 번만 카운트 - - generalPqVendors.forEach(v => vendorIds.add(v.vendorId)); - - // 1-B) 프로젝트 PQ 답변이 있는 협력업체 ID 조회 (status와 상관없이) - const projectPqVendors = await tx - .select({ - vendorId: vendorProjectPQs.vendorId - }) - .from(vendorProjectPQs) - .innerJoin( - vendors, - eq(vendorProjectPQs.vendorId, vendors.id) - ) - .where( - and( - // 최소한 IN_PROGRESS부터는 작업이 시작된 상태이므로 포함 - not(eq(vendorProjectPQs.status, "REQUESTED")), // REQUESTED 상태는 제외 - advancedWhere, - globalWhere - ) - ); - - projectPqVendors.forEach(v => vendorIds.add(v.vendorId)); - - // 중복 제거된 협력업체 ID 배열 - const uniqueVendorIds = Array.from(vendorIds); - - // 총 개수 (중복 제거 후) - const total = uniqueVendorIds.length; - - if (total === 0) { - return { data: [], total: 0 }; - } - - // 페이징 처리 (정렬 후 limit/offset 적용) - const paginatedIds = uniqueVendorIds.slice(offset, offset + input.perPage); - - // 2) 페이징된 협력업체 상세 정보 조회 - const vendorsData = await selectVendors(tx, { - where: inArray(vendors.id, paginatedIds), - orderBy: input.sort.length > 0 - ? input.sort.map((item) => - item.desc ? desc(vendors.vendorName) : asc(vendors.vendorName) - ) - : [asc(vendors.createdAt)], - }); - - // 3) 각 벤더별 PQ 상태 정보 추가 - const vendorsWithPqInfo = await Promise.all( - vendorsData.map(async (vendor) => { - // 3-A) 첨부 파일 조회 - const attachments = await tx - .select({ - id: vendorAttachments.id, - fileName: vendorAttachments.fileName, - filePath: vendorAttachments.filePath, - }) - .from(vendorAttachments) - .where(eq(vendorAttachments.vendorId, vendor.id)); - - // 3-B) 일반 PQ 제출 여부 확인 (PQ 답변이 있는지) - const generalPqAnswers = await tx - .select({ count: count() }) - .from(vendorPqCriteriaAnswers) - .where( - and( - eq(vendorPqCriteriaAnswers.vendorId, vendor.id), - isNull(vendorPqCriteriaAnswers.projectId) - ) - ); - - const hasGeneralPq = generalPqAnswers[0]?.count > 0; - - // 3-C) 프로젝트 PQ 정보 조회 (모든 상태 포함) - const projectPqs = await tx - .select({ - projectId: vendorProjectPQs.projectId, - projectName: projects.name, - status: vendorProjectPQs.status, - submittedAt: vendorProjectPQs.submittedAt, - approvedAt: vendorProjectPQs.approvedAt, - rejectedAt: vendorProjectPQs.rejectedAt - }) - .from(vendorProjectPQs) - .innerJoin( - projects, - eq(vendorProjectPQs.projectId, projects.id) - ) - .where( - and( - eq(vendorProjectPQs.vendorId, vendor.id), - not(eq(vendorProjectPQs.status, "REQUESTED")) // REQUESTED 상태는 제외 - ) - ); - - const hasProjectPq = projectPqs.length > 0; - - // 프로젝트 PQ 상태별 카운트 - const projectPqStatusCounts = { - inProgress: projectPqs.filter(p => p.status === "IN_PROGRESS").length, - submitted: projectPqs.filter(p => p.status === "SUBMITTED").length, - approved: projectPqs.filter(p => p.status === "APPROVED").length, - rejected: projectPqs.filter(p => p.status === "REJECTED").length, - total: projectPqs.length - }; - - // 3-D) PQ 상태 정보 추가 - return { - ...vendor, - hasAttachments: attachments.length > 0, - attachmentsList: attachments, - pqInfo: { - hasGeneralPq, - hasProjectPq, - projectPqs, - projectPqStatusCounts, - // 현재 PQ 상태 (UI에 표시 용도) - pqStatus: getPqStatusDisplay(vendor.status, hasGeneralPq, hasProjectPq, projectPqStatusCounts) - } - }; - }) - ); - - return { data: vendorsWithPqInfo, total }; - }); - - // 페이지 수 - const pageCount = Math.ceil(total / input.perPage); - - return { data, pageCount }; - } catch (err) { - console.error("Error in getVendorsInPQ:", err); - // 에러 발생 시 - return { data: [], pageCount: 0 }; - } - }, - [JSON.stringify(input)], // 캐싱 키 - { - revalidate: 3600, - tags: ["vendors-in-pq", "project-pqs"], // revalidateTag 호출 시 무효화 - } - )(); -} - -// PQ 상태 표시 함수 -function getPqStatusDisplay( - vendorStatus: string, - hasGeneralPq: boolean, - hasProjectPq: boolean, - projectPqCounts: { inProgress: number, submitted: number, approved: number, rejected: number, total: number } -): string { - // 프로젝트 PQ 상태 문자열 생성 - let projectPqStatus = ""; - if (hasProjectPq) { - const parts = []; - if (projectPqCounts.inProgress > 0) { - parts.push(`진행중: ${projectPqCounts.inProgress}`); - } - if (projectPqCounts.submitted > 0) { - parts.push(`제출: ${projectPqCounts.submitted}`); - } - if (projectPqCounts.approved > 0) { - parts.push(`승인: ${projectPqCounts.approved}`); - } - if (projectPqCounts.rejected > 0) { - parts.push(`거부: ${projectPqCounts.rejected}`); - } - projectPqStatus = parts.join(", "); - } - - // 일반 PQ + 프로젝트 PQ 조합 상태 - if (hasGeneralPq && hasProjectPq) { - return `일반 PQ (${getPqVendorStatusText(vendorStatus)}) + 프로젝트 PQ (${projectPqStatus})`; - } else if (hasGeneralPq) { - return `일반 PQ (${getPqVendorStatusText(vendorStatus)})`; - } else if (hasProjectPq) { - return `프로젝트 PQ (${projectPqStatus})`; - } - - return "PQ 정보 없음"; -} - -// 협력업체 상태 텍스트 변환 -function getPqVendorStatusText(status: string): string { - switch (status) { - case "IN_PQ": return "진행중"; - case "PQ_SUBMITTED": return "제출됨"; - case "PQ_FAILED": return "실패"; - case "PQ_APPROVED": - case "APPROVED": return "승인됨"; - case "READY_TO_SEND": return "거래 준비"; - case "ACTIVE": return "활성"; - case "INACTIVE": return "비활성"; - case "BLACKLISTED": return "거래금지"; - default: return status; - } -} - - -export type VendorStatus = - | "PENDING_REVIEW" - | "IN_REVIEW" - | "REJECTED" - | "IN_PQ" - | "PQ_SUBMITTED" - | "PQ_FAILED" - | "APPROVED" - | "ACTIVE" - | "INACTIVE" - | "BLACKLISTED" - | "PQ_APPROVED" - - export async function updateVendorStatusAction( - vendorId: number, - newStatus: VendorStatus - ) { - try { - // 1) Update DB - await db.update(vendors) - .set({ status: newStatus }) - .where(eq(vendors.id, vendorId)) - - // 2) Load vendor’s email & name - const vendor = await db.select().from(vendors).where(eq(vendors.id, vendorId)).then(r => r[0]) - if (!vendor) { - return { ok: false, error: "Vendor not found" } - } - const headersList = await headers(); - const host = headersList.get('host') || 'localhost:3000'; - const loginUrl = `http://${host}/partners/pq` - - // 3) Send email - await sendEmail({ - to: vendor.email || "", - subject: `Your PQ Status is now ${newStatus}`, - template: "vendor-pq-status", // matches .hbs file - context: { - name: vendor.vendorName, - status: newStatus, - loginUrl: loginUrl, // etc. - }, - }) - revalidateTag("vendors") - revalidateTag("vendors-in-pq") - return { ok: true } - } catch (error) { - console.error("updateVendorStatusAction error:", error) - return { ok: false, error: String(error) } - } - } - - type ProjectPQStatus = "REQUESTED" | "IN_PROGRESS" | "SUBMITTED" | "APPROVED" | "REJECTED"; - -/** - * Update the status of a project-specific PQ for a vendor - */ -export async function updateProjectPQStatusAction({ - vendorId, - projectId, - status, - comment -}: { - vendorId: number; - projectId: number; - status: ProjectPQStatus; - comment?: string; -}) { - try { - const currentDate = new Date(); - - // 1) Prepare update data with appropriate timestamps - const updateData: any = { - status, - updatedAt: currentDate, - }; - - // Add status-specific fields - if (status === "APPROVED") { - updateData.approvedAt = currentDate; - } else if (status === "REJECTED") { - updateData.rejectedAt = currentDate; - updateData.rejectReason = comment || null; - } else if (status === "SUBMITTED") { - updateData.submittedAt = currentDate; - } - - // 2) Update the project PQ record - await db - .update(vendorProjectPQs) - .set(updateData) - .where( - and( - eq(vendorProjectPQs.vendorId, vendorId), - eq(vendorProjectPQs.projectId, projectId) - ) - ); - - // 3) Load vendor and project details for email - const vendor = await db - .select({ - id: vendors.id, - email: vendors.email, - vendorName: vendors.vendorName - }) - .from(vendors) - .where(eq(vendors.id, vendorId)) - .then(rows => rows[0]); - - if (!vendor) { - return { ok: false, error: "Vendor not found" }; - } - - const project = await db - .select({ - name: projects.name - }) - .from(projects) - .where(eq(projects.id, projectId)) - .then(rows => rows[0]); - - if (!project) { - return { ok: false, error: "Project not found" }; - } - - // 4) Send email notification - await sendEmail({ - to: vendor.email || "", - subject: `Your Project PQ for ${project.name} is now ${status}`, - template: "vendor-project-pq-status", // matches .hbs file (you might need to create this) - context: { - name: vendor.vendorName, - status, - projectName: project.name, - rejectionReason: status === "REJECTED" ? comment : undefined, - hasRejectionReason: status === "REJECTED" && !!comment, - loginUrl: `${process.env.NEXT_PUBLIC_URL}/partners/pq?projectId=${projectId}`, - approvalDate: status === "APPROVED" ? currentDate.toLocaleDateString() : undefined, - rejectionDate: status === "REJECTED" ? currentDate.toLocaleDateString() : undefined, - }, - }); - - // 5) Revalidate cache tags - revalidateTag("vendors"); - revalidateTag("vendors-in-pq"); - revalidateTag(`vendor-project-pqs-${vendorId}`); - revalidateTag(`project-pq-${projectId}`); - revalidateTag(`project-vendors-${projectId}`); - - return { ok: true }; - } catch (error) { - console.error("updateProjectPQStatusAction error:", error); - return { ok: false, error: String(error) }; - } -} - -// 코멘트 타입 정의 -interface ItemComment { - answerId: number; - checkPoint: string; // 체크포인트 정보 추가 - code: string; // 코드 정보 추가 - comment: string; -} - -/** - * PQ 변경 요청 처리 서버 액션 - * - * @param vendorId 협력업체 ID - * @param comment 항목별 코멘트 배열 (answerId, checkPoint, code, comment로 구성) - * @param generalComment 전체 PQ에 대한 일반 코멘트 (선택사항) - */ -export async function requestPqChangesAction({ - vendorId, - projectId, - comment, - generalComment, - reviewerName, - reviewerId -}: { - vendorId: number; - projectId?: number; // Optional project ID for project-specific PQs - comment: ItemComment[]; - generalComment?: string; - reviewerName?: string; - reviewerId?: string; -}) { - try { - // 1) 상태 업데이트 (PQ 타입에 따라 다르게 처리) - if (projectId) { - // 프로젝트 PQ인 경우 vendorProjectPQs 테이블 업데이트 - const projectPq = await db - .select() - .from(vendorProjectPQs) - .where( - and( - eq(vendorProjectPQs.vendorId, vendorId), - eq(vendorProjectPQs.projectId, projectId) - ) - ) - .then(rows => rows[0]); - - if (!projectPq) { - return { ok: false, error: "Project PQ record not found" }; - } - - await db - .update(vendorProjectPQs) - .set({ - status: "IN_PROGRESS", // 변경 요청 상태로 설정 - updatedAt: new Date(), - }) - .where( - and( - eq(vendorProjectPQs.vendorId, vendorId), - eq(vendorProjectPQs.projectId, projectId) - ) - ); - } else { - // 일반 PQ인 경우 vendors 테이블 업데이트 - await db - .update(vendors) - .set({ - status: "IN_PQ", // 변경 요청 상태로 설정 - updatedAt: new Date(), - }) - .where(eq(vendors.id, vendorId)); - } - - // 2) 협력업체 정보 가져오기 - const vendor = await db - .select() - .from(vendors) - .where(eq(vendors.id, vendorId)) - .then(r => r[0]); - - if (!vendor) { - return { ok: false, error: "Vendor not found" }; - } - - // 프로젝트 정보 가져오기 (프로젝트 PQ인 경우) - let projectName = ""; - if (projectId) { - const project = await db - .select({ - name: projects.name - }) - .from(projects) - .where(eq(projects.id, projectId)) - .then(rows => rows[0]); - - projectName = project?.name || "Unknown Project"; - } - - // 3) 각 항목별 코멘트 저장 - const currentDate = new Date(); - - - // 병렬로 모든 코멘트 저장 - if (comment && comment.length > 0) { - const insertPromises = comment.map(item => - db.insert(vendorPqReviewLogs) - .values({ - vendorPqCriteriaAnswerId: item.answerId, - // reviewerId: reviewerId, - reviewerName: reviewerName, - reviewerComment: item.comment, - createdAt: currentDate, - // 추가 메타데이터 필드가 있다면 저장 - // 이런 메타데이터는 DB 스키마에 해당 필드가 있어야 함 - // meta: JSON.stringify({ checkPoint: item.checkPoint, code: item.code }) - }) - ); - - // 모든 삽입 기다리기 - await Promise.all(insertPromises); - } - - // 4) 변경 요청 이메일 보내기 - // 코멘트 목록 준비 - const commentItems = comment.map(item => ({ - id: item.answerId, - code: item.code, - checkPoint: item.checkPoint, - text: item.comment - })); - - // PQ 유형에 따라 이메일 제목 및 내용 조정 - const emailSubject = projectId - ? `[IMPORTANT] Your Project PQ (${projectName}) requires changes` - : `[IMPORTANT] Your PQ submission requires changes`; - - // 로그인 URL - 프로젝트 PQ인 경우 다른 경로로 안내 - const loginUrl = projectId - ? `${process.env.NEXT_PUBLIC_URL}/partners/pq?projectId=${projectId}` - : `${process.env.NEXT_PUBLIC_URL}/partners/pq`; - - await sendEmail({ - to: vendor.email || "", - subject: emailSubject, - template: "vendor-pq-comment", // matches .hbs file - context: { - name: vendor.vendorName, - vendorCode: vendor.vendorCode, - loginUrl, - comments: commentItems, - generalComment: generalComment || "", - hasGeneralComment: !!generalComment, - commentCount: commentItems.length, - projectId, - projectName, - isProjPQ: !!projectId, - }, - }); - - // 5) 캐시 무효화 - PQ 유형에 따라 적절한 태그 무효화 - revalidateTag("vendors"); - revalidateTag("vendors-in-pq"); - - if (projectId) { - revalidateTag(`vendor-project-pqs-${vendorId}`); - revalidateTag(`project-pq-${projectId}`); - revalidateTag(`project-vendors-${projectId}`); - } - - return { ok: true }; - } catch (error) { - console.error("requestPqChangesAction error:", error); - return { ok: false, error: String(error) }; - } -} - -interface AddReviewCommentInput { - answerId: number // vendorPqCriteriaAnswers.id - comment: string - reviewerName?: string -} - -export async function addReviewCommentAction(input: AddReviewCommentInput) { - try { - // 1) Check that the answer row actually exists - const existing = await db - .select({ id: vendorPqCriteriaAnswers.id }) - .from(vendorPqCriteriaAnswers) - .where(eq(vendorPqCriteriaAnswers.id, input.answerId)) - - if (existing.length === 0) { - return { ok: false, error: "Item not found" } - } - - // 2) Insert the log - await db.insert(vendorPqReviewLogs).values({ - vendorPqCriteriaAnswerId: input.answerId, - reviewerComment: input.comment, - reviewerName: input.reviewerName ?? "AdminUser", - }) - - return { ok: true } - } catch (error) { - console.error("addReviewCommentAction error:", error) - return { ok: false, error: String(error) } - } -} - -interface GetItemReviewLogsInput { - answerId: number -} - -export async function getItemReviewLogsAction(input: GetItemReviewLogsInput) { - try { - - const logs = await db - .select() - .from(vendorPqReviewLogs) - .where(eq(vendorPqReviewLogs.vendorPqCriteriaAnswerId, input.answerId)) - .orderBy(desc(vendorPqReviewLogs.createdAt)); - - return { ok: true, data: logs }; - } catch (error) { - console.error("getItemReviewLogsAction error:", error); - return { ok: false, error: String(error) }; - } -} - -export interface VendorPQListItem { - projectId: number; - projectName: string; - status: string; - submittedAt?: Date | null; // Change to accept both undefined and null -} - -export interface VendorPQsList { - hasGeneralPq: boolean; - generalPqStatus?: string; // vendor.status for general PQ - projectPQs: VendorPQListItem[]; -} - -export async function getVendorPQsList(vendorId: number): Promise<VendorPQsList> { - try { - // 1. Check if vendor has general PQ answers - const generalPqAnswers = await db - .select({ count: count() }) - .from(vendorPqCriteriaAnswers) - .where( - and( - eq(vendorPqCriteriaAnswers.vendorId, vendorId), - isNull(vendorPqCriteriaAnswers.projectId) - ) - ); - - const hasGeneralPq = (generalPqAnswers[0]?.count || 0) > 0; - - // 2. Get vendor status for general PQ - let generalPqStatus; - if (hasGeneralPq) { - const vendor = await db - .select({ status: vendors.status }) - .from(vendors) - .where(eq(vendors.id, vendorId)) - .then(rows => rows[0]); - - generalPqStatus = vendor?.status; - } - - // 3. Get project PQs - const projectPQs = await db - .select({ - projectId: vendorProjectPQs.projectId, - projectName: projects.name, - status: vendorProjectPQs.status, - submittedAt: vendorProjectPQs.submittedAt - }) - .from(vendorProjectPQs) - .innerJoin( - projects, - eq(vendorProjectPQs.projectId, projects.id) - ) - .where( - and( - eq(vendorProjectPQs.vendorId, vendorId), - not(eq(vendorProjectPQs.status, "REQUESTED")) // Exclude requests that haven't been started - ) - ) - .orderBy(vendorProjectPQs.updatedAt); - - return { - hasGeneralPq, - generalPqStatus, - projectPQs: projectPQs - }; - - } catch (error) { - console.error("Error fetching vendor PQs list:", error); - return { - hasGeneralPq: false, - projectPQs: [] - }; - } -} - - -export async function loadGeneralPQData(vendorId: number) { - "use server"; - return getPQDataByVendorId(vendorId) -} - -export async function loadProjectPQData(vendorId: number, projectId: number) { - "use server"; - return getPQDataByVendorId(vendorId, projectId) -} - -export async function loadGeneralPQAction(vendorId: number) { - return getPQDataByVendorId(vendorId); -} - -export async function loadProjectPQAction(vendorId: number, projectId?: number): Promise<PQGroupData[]> { - if (!projectId) { - throw new Error("Project ID is required for loading project PQ data"); - } - return getPQDataByVendorId(vendorId, projectId); -} - - - -export async function getAllPQsByVendorId(vendorId: number) { - try { - const pqList = await db - .select({ - id: vendorPQSubmissions.id, - type: vendorPQSubmissions.type, - status: vendorPQSubmissions.status, - projectId: vendorPQSubmissions.projectId, - projectName: projects.name, - createdAt: vendorPQSubmissions.createdAt, - updatedAt: vendorPQSubmissions.updatedAt, - submittedAt: vendorPQSubmissions.submittedAt, - approvedAt: vendorPQSubmissions.approvedAt, - rejectedAt: vendorPQSubmissions.rejectedAt, - rejectReason: vendorPQSubmissions.rejectReason, - }) - .from(vendorPQSubmissions) - .leftJoin(projects, eq(vendorPQSubmissions.projectId, projects.id)) - .where(eq(vendorPQSubmissions.vendorId, vendorId)) - .orderBy(desc(vendorPQSubmissions.createdAt)); - - return pqList; - } catch (error) { - console.error("Error fetching PQ list:", error); - return []; - } -} - -// 특정 PQ의 상세 정보 조회 (개별 PQ 페이지용) -export async function getPQById(pqSubmissionId: number, vendorId: number) { - try { - const pq = await db - .select({ - id: vendorPQSubmissions.id, - vendorId: vendorPQSubmissions.vendorId, - projectId: vendorPQSubmissions.projectId, - type: vendorPQSubmissions.type, - status: vendorPQSubmissions.status, - createdAt: vendorPQSubmissions.createdAt, - submittedAt: vendorPQSubmissions.submittedAt, - approvedAt: vendorPQSubmissions.approvedAt, - rejectedAt: vendorPQSubmissions.rejectedAt, - rejectReason: vendorPQSubmissions.rejectReason, - - // 벤더 정보 (추가) - vendorName: vendors.vendorName, - vendorCode: vendors.vendorCode, - vendorStatus: vendors.status, - - // 프로젝트 정보 (조인) - projectName: projects.name, - projectCode: projects.code, - }) - .from(vendorPQSubmissions) - .leftJoin(vendors, eq(vendorPQSubmissions.vendorId, vendors.id)) - .leftJoin(projects, eq(vendorPQSubmissions.projectId, projects.id)) - .where( - and( - eq(vendorPQSubmissions.id, pqSubmissionId), - eq(vendorPQSubmissions.vendorId, vendorId) - ) - ) - .limit(1) - .then(rows => rows[0]); - - if (!pq) { - throw new Error("PQ not found or access denied"); - } - - return pq; - } catch (error) { - console.error("Error fetching PQ by ID:", error); - throw error; - } -} - -export async function getPQStatusCounts(vendorId: number) { - try { - // 모든 PQ 상태 조회 (일반 PQ + 프로젝트 PQ) - const pqStatuses = await db - .select({ - status: vendorPQSubmissions.status, - count: count(), - }) - .from(vendorPQSubmissions) - .where(eq(vendorPQSubmissions.vendorId, vendorId)) - .groupBy(vendorPQSubmissions.status); - - // 상태별 개수를 객체로 변환 - const statusCounts = { - REQUESTED: 0, - IN_PROGRESS: 0, - SUBMITTED: 0, - APPROVED: 0, - REJECTED: 0, - }; - - // 조회된 결과를 statusCounts 객체에 매핑 - pqStatuses.forEach((item) => { - if (item.status in statusCounts) { - statusCounts[item.status as keyof typeof statusCounts] = item.count; - } - }); - - return statusCounts; - } catch (error) { - console.error("Error fetching PQ status counts:", error); - return { - REQUESTED: 0, - IN_PROGRESS: 0, - SUBMITTED: 0, - APPROVED: 0, - REJECTED: 0, - }; - } -} - -// 상태 레이블 함수 -function getStatusLabel(status: string): string { - switch (status) { - case "REQUESTED": - return "요청됨"; - case "IN_PROGRESS": - return "진행 중"; - case "SUBMITTED": - return "제출됨"; - case "APPROVED": - return "승인됨"; - case "REJECTED": - return "거부됨"; - default: - return status; - } -} - -export async function getPQSubmissions(input: GetPQSubmissionsSchema) { - return unstable_cache( - async () => { - try { - const offset = (input.page - 1) * input.perPage; - - const pqFilterMapping = createPQFilterMapping(); - const joinedTables = getPQJoinedTables(); - - console.log(input, "input") - - // 1) 고급 필터 조건 (DataTableAdvancedToolbar에서) - let advancedWhere: SQL<unknown> | undefined = undefined; - if (input.filters && input.filters.length > 0) { - advancedWhere = filterColumns({ - table: vendorPQSubmissions, - filters: input.filters, - joinOperator: input.joinOperator || 'and', - joinedTables, - customColumnMapping: pqFilterMapping, - }); - console.log("advancedWhere:", advancedWhere); - } - - // 2) 기본 필터 조건 (PQFilterSheet에서) - let basicWhere: SQL<unknown> | undefined = undefined; - if (input.basicFilters && input.basicFilters.length > 0) { - basicWhere = filterColumns({ - table: vendorPQSubmissions, - filters: input.basicFilters, - joinOperator: input.basicJoinOperator || 'and', - joinedTables, - customColumnMapping: pqFilterMapping, - }); - console.log("basicWhere:", basicWhere); - } - - // 3) 글로벌 검색 조건 - let globalWhere: SQL<unknown> | undefined = undefined; - if (input.search) { - const s = `%${input.search}%`; - - const validSearchConditions: SQL<unknown>[] = []; - - // 기존 검색 조건들 - const nameCondition = ilike(vendors.vendorName, s); - if (nameCondition) validSearchConditions.push(nameCondition); - - const codeCondition = ilike(vendors.vendorCode, s); - if (codeCondition) validSearchConditions.push(codeCondition); - - const projectNameCondition = ilike(projects.name, s); - if (projectNameCondition) validSearchConditions.push(projectNameCondition); - - const projectCodeCondition = ilike(projects.code, s); - if (projectCodeCondition) validSearchConditions.push(projectCodeCondition); - - // 새로 추가된 검색 조건들 - const pqNumberCondition = ilike(vendorPQSubmissions.pqNumber, s); - if (pqNumberCondition) validSearchConditions.push(pqNumberCondition); - - const requesterCondition = ilike(users.name, s); - if (requesterCondition) validSearchConditions.push(requesterCondition); - - if (validSearchConditions.length > 0) { - globalWhere = or(...validSearchConditions); - } - } - - // 4) 날짜 조건 - let fromDateWhere: SQL<unknown> | undefined = undefined; - let toDateWhere: SQL<unknown> | undefined = undefined; - - if (input.submittedDateFrom) { - const fromDate = new Date(input.submittedDateFrom); - const condition = gte(vendorPQSubmissions.submittedAt, fromDate); - if (condition) fromDateWhere = condition; - } - - if (input.submittedDateTo) { - const toDate = new Date(input.submittedDateTo); - const condition = lte(vendorPQSubmissions.submittedAt, toDate); - if (condition) toDateWhere = condition; - } - - // 5) 최종 WHERE 조건 생성 - 각 그룹을 AND로 연결 - const whereConditions: SQL<unknown>[] = []; - - // 고급 필터 조건 추가 - if (advancedWhere) whereConditions.push(advancedWhere); - - // 기본 필터 조건 추가 - if (basicWhere) whereConditions.push(basicWhere); - - // 기타 조건들 추가 - if (globalWhere) whereConditions.push(globalWhere); - if (fromDateWhere) whereConditions.push(fromDateWhere); - if (toDateWhere) whereConditions.push(toDateWhere); - - // 모든 조건을 AND로 연결 - const finalWhere = whereConditions.length > 0 ? and(...whereConditions) : undefined; - - console.log("Final WHERE conditions:", { - advancedWhere: !!advancedWhere, - basicWhere: !!basicWhere, - globalWhere: !!globalWhere, - dateConditions: !!(fromDateWhere || toDateWhere), - totalConditions: whereConditions.length - }); - - // 6) 전체 데이터 수 조회 - const totalResult = await db - .select({ count: count() }) - .from(vendorPQSubmissions) - .leftJoin(vendors, eq(vendorPQSubmissions.vendorId, vendors.id)) - .leftJoin(projects, eq(vendorPQSubmissions.projectId, projects.id)) - .leftJoin(users, eq(vendorPQSubmissions.requesterId, users.id)) - .leftJoin(vendorInvestigations, eq(vendorInvestigations.pqSubmissionId, vendorPQSubmissions.id)) - .where(finalWhere); - - const total = totalResult[0]?.count || 0; - - if (total === 0) { - return { data: [], pageCount: 0 }; - } - - // 7) 정렬 및 페이징 처리된 데이터 조회 - const orderByColumns = input.sort.map((sort) => { - const column = sort.id as keyof typeof vendorPQSubmissions.$inferSelect; - return sort.desc ? desc(vendorPQSubmissions[column]) : asc(vendorPQSubmissions[column]); - }); - - if (orderByColumns.length === 0) { - orderByColumns.push(desc(vendorPQSubmissions.updatedAt)); - } - - const pqSubmissions = await db - .select({ - id: vendorPQSubmissions.id, - type: vendorPQSubmissions.type, - pqNumber: vendorPQSubmissions.pqNumber, - requesterId: vendorPQSubmissions.requesterId, - requesterName: users.name, - status: vendorPQSubmissions.status, - createdAt: vendorPQSubmissions.createdAt, - updatedAt: vendorPQSubmissions.updatedAt, - submittedAt: vendorPQSubmissions.submittedAt, - approvedAt: vendorPQSubmissions.approvedAt, - rejectedAt: vendorPQSubmissions.rejectedAt, - rejectReason: vendorPQSubmissions.rejectReason, - // Vendor 정보 - vendorId: vendors.id, - vendorName: vendors.vendorName, - vendorCode: vendors.vendorCode, - taxId: vendors.taxId, - vendorStatus: vendors.status, - // Project 정보 (프로젝트 PQ인 경우) - projectId: projects.id, - projectName: projects.name, - projectCode: projects.code, - }) - .from(vendorPQSubmissions) - .leftJoin(vendors, eq(vendorPQSubmissions.vendorId, vendors.id)) - .leftJoin(projects, eq(vendorPQSubmissions.projectId, projects.id)) - .leftJoin(users, eq(vendorPQSubmissions.requesterId, users.id)) - .where(finalWhere) - .orderBy(...orderByColumns) - .limit(input.perPage) - .offset(offset); - - // 8) 각 PQ 제출에 대한 추가 정보 조회 (기존과 동일) - const pqSubmissionsWithDetails = await Promise.all( - pqSubmissions.map(async (submission) => { - // 기본 반환 객체 - const baseResult = { - ...submission, - answerCount: 0, - attachmentCount: 0, - pqStatus: getStatusLabel(submission.status), - pqTypeLabel: submission.type === "GENERAL" ? "일반 PQ" : "프로젝트 PQ", - }; - - // vendorId가 null이면 기본 정보만 반환 - if (submission.vendorId === null) { - return baseResult; - } - - try { - // 답변 수 조회 - const vendorId = submission.vendorId; - - const answerWhereConditions: SQL<unknown>[] = []; - - const vendorCondition = eq(vendorPqCriteriaAnswers.vendorId, vendorId); - if (vendorCondition) answerWhereConditions.push(vendorCondition); - - let projectCondition: SQL<unknown> | undefined; - if (submission.projectId !== null) { - projectCondition = eq(vendorPqCriteriaAnswers.projectId, submission.projectId); - } else { - projectCondition = isNull(vendorPqCriteriaAnswers.projectId); - } - - if (projectCondition) answerWhereConditions.push(projectCondition); - - const answerWhere = and(...answerWhereConditions); - - const answersResult = await db - .select({ count: count() }) - .from(vendorPqCriteriaAnswers) - .where(answerWhere); - - const answerCount = answersResult[0]?.count || 0; - - // 첨부 파일 수 조회 - const attachmentsResult = await db - .select({ count: count() }) - .from(vendorPqCriteriaAnswers) - .leftJoin( - vendorCriteriaAttachments, - eq(vendorCriteriaAttachments.vendorCriteriaAnswerId, vendorPqCriteriaAnswers.id) - ) - .where(answerWhere); - - const attachmentCount = attachmentsResult[0]?.count || 0; - - const requesters = alias(users, 'requesters'); - const qmManagers = alias(users, 'qmManagers'); - - const investigationResult = await db - .select({ - id: vendorInvestigations.id, - investigationStatus: vendorInvestigations.investigationStatus, - evaluationType: vendorInvestigations.evaluationType, - investigationAddress: vendorInvestigations.investigationAddress, - investigationMethod: vendorInvestigations.investigationMethod, - scheduledStartAt: vendorInvestigations.scheduledStartAt, - scheduledEndAt: vendorInvestigations.scheduledEndAt, - requestedAt: vendorInvestigations.requestedAt, - confirmedAt: vendorInvestigations.confirmedAt, - completedAt: vendorInvestigations.completedAt, - forecastedAt: vendorInvestigations.forecastedAt, - evaluationScore: vendorInvestigations.evaluationScore, - evaluationResult: vendorInvestigations.evaluationResult, - investigationNotes: vendorInvestigations.investigationNotes, - requesterId: vendorInvestigations.requesterId, - requesterName: requesters.name, - qmManagerId: vendorInvestigations.qmManagerId, - qmManagerName: qmManagers.name, - qmManagerEmail: qmManagers.email, - }) - .from(vendorInvestigations) - .leftJoin(requesters, eq(vendorInvestigations.requesterId, requesters.id)) - .leftJoin(qmManagers, eq(vendorInvestigations.qmManagerId, qmManagers.id)) - .where(and( - eq(vendorInvestigations.vendorId, submission.vendorId), - eq(vendorInvestigations.pqSubmissionId, submission.id) - )) - .orderBy(desc(vendorInvestigations.createdAt)) - .limit(1); - - const investigation = investigationResult[0] || null; - - return { - ...baseResult, - answerCount, - attachmentCount, - investigation - }; - } catch (error) { - console.error("Error fetching PQ details:", error); - return baseResult; - } - }) - ); - - const pageCount = Math.ceil(total / input.perPage); - - return { data: pqSubmissionsWithDetails, pageCount }; - } catch (err) { - console.error("Error in getPQSubmissions:", err); - return { data: [], pageCount: 0 }; - } - }, - [JSON.stringify(input)], // 캐싱 키 - { - revalidate: 3600, - tags: ["pq-submissions"], // revalidateTag 호출 시 무효화 - } - )(); -} - -export async function getPQStatusCountsAll() { - try { - // 모든 PQ 상태별 개수 조회 (벤더 제한 없음) - const pqStatuses = await db - .select({ - status: vendorPQSubmissions.status, - count: count(), - }) - .from(vendorPQSubmissions) - .groupBy(vendorPQSubmissions.status); - - // 상태별 개수를 객체로 변환 - const statusCounts = { - REQUESTED: 0, - IN_PROGRESS: 0, - SUBMITTED: 0, - APPROVED: 0, - REJECTED: 0, - }; - - // 조회된 결과를 statusCounts 객체에 매핑 - pqStatuses.forEach((item) => { - if (item.status in statusCounts) { - statusCounts[item.status as keyof typeof statusCounts] = item.count; - } - }); - - return statusCounts; - } catch (error) { - console.error("Error fetching PQ status counts:", error); - return { - REQUESTED: 0, - IN_PROGRESS: 0, - SUBMITTED: 0, - APPROVED: 0, - REJECTED: 0, - }; - } -} - -// PQ 타입별, 상태별 개수 집계 함수 (추가 옵션) -export async function getPQDetailedStatusCounts() { - try { - // 타입별, 상태별 개수 조회 - const pqStatuses = await db - .select({ - type: vendorPQSubmissions.type, - status: vendorPQSubmissions.status, - count: count(), - }) - .from(vendorPQSubmissions) - .groupBy(vendorPQSubmissions.type, vendorPQSubmissions.status); - - // 결과를 저장할 객체 초기화 - const result = { - GENERAL: { - REQUESTED: 0, - IN_PROGRESS: 0, - SUBMITTED: 0, - APPROVED: 0, - REJECTED: 0, - total: 0 - }, - PROJECT: { - REQUESTED: 0, - IN_PROGRESS: 0, - SUBMITTED: 0, - APPROVED: 0, - REJECTED: 0, - total: 0 - }, - total: { - REQUESTED: 0, - IN_PROGRESS: 0, - SUBMITTED: 0, - APPROVED: 0, - REJECTED: 0, - total: 0 - } - }; - - // 결과 매핑 - pqStatuses.forEach((item) => { - if (item.type && item.status) { - const type = item.type as keyof typeof result; - const status = item.status as keyof typeof result.GENERAL; - - if (type in result && status in result[type]) { - // 타입별 상태 카운트 업데이트 - result[type][status] = item.count; - - // 타입별 합계 업데이트 - result[type].total += item.count; - - // 전체 상태별 카운트 업데이트 - result.total[status] += item.count; - - // 전체 합계 업데이트 - result.total.total += item.count; - } - } - }); - - return result; - } catch (error) { - console.error("Error fetching detailed PQ status counts:", error); - return { - GENERAL: { - REQUESTED: 0, - IN_PROGRESS: 0, - SUBMITTED: 0, - APPROVED: 0, - REJECTED: 0, - total: 0 - }, - PROJECT: { - REQUESTED: 0, - IN_PROGRESS: 0, - SUBMITTED: 0, - APPROVED: 0, - REJECTED: 0, - total: 0 - }, - total: { - REQUESTED: 0, - IN_PROGRESS: 0, - SUBMITTED: 0, - APPROVED: 0, - REJECTED: 0, - total: 0 - } - }; - } -} - -// PQ 승인 액션 -export async function approvePQAction({ - pqSubmissionId, - vendorId, -}: { - pqSubmissionId: number; - vendorId: number; -}) { - unstable_noStore(); - - try { - const headersList = await headers(); - const host = headersList.get('host') || 'localhost:3000'; - const currentDate = new Date(); - - // 1. PQ 제출 정보 조회 - const pqSubmission = await db - .select({ - id: vendorPQSubmissions.id, - vendorId: vendorPQSubmissions.vendorId, - projectId: vendorPQSubmissions.projectId, - type: vendorPQSubmissions.type, - status: vendorPQSubmissions.status, - }) - .from(vendorPQSubmissions) - .where( - and( - eq(vendorPQSubmissions.id, pqSubmissionId), - eq(vendorPQSubmissions.vendorId, vendorId) - ) - ) - .then(rows => rows[0]); - - if (!pqSubmission) { - return { ok: false, error: "PQ submission not found" }; - } - - // 2. 상태 확인 (SUBMITTED 상태만 승인 가능) - if (pqSubmission.status !== "SUBMITTED") { - return { - ok: false, - error: `Cannot approve PQ in current status: ${pqSubmission.status}` - }; - } - - // 3. 벤더 정보 조회 - const vendor = await db - .select({ - id: vendors.id, - vendorName: vendors.vendorName, - email: vendors.email, - status: vendors.status, - }) - .from(vendors) - .where(eq(vendors.id, vendorId)) - .then(rows => rows[0]); - - if (!vendor) { - return { ok: false, error: "Vendor not found" }; - } - - // 4. 프로젝트 정보 (프로젝트 PQ인 경우) - let projectName = ''; - if (pqSubmission.projectId) { - const projectData = await db - .select({ - id: projects.id, - name: projects.name, - }) - .from(projects) - .where(eq(projects.id, pqSubmission.projectId)) - .then(rows => rows[0]); - - projectName = projectData?.name || 'Unknown Project'; - } - - // 5. PQ 상태 업데이트 - await db - .update(vendorPQSubmissions) - .set({ - status: "APPROVED", - approvedAt: currentDate, - updatedAt: currentDate, - }) - .where(eq(vendorPQSubmissions.id, pqSubmissionId)); - - // 6. 일반 PQ인 경우 벤더 상태 업데이트 (선택사항) - if (pqSubmission.type === "GENERAL") { - await db - .update(vendors) - .set({ - status: "PQ_APPROVED", - updatedAt: currentDate, - }) - .where(eq(vendors.id, vendorId)); - } - - // 7. 벤더에게 이메일 알림 발송 - if (vendor.email) { - try { - const emailSubject = pqSubmission.projectId - ? `[eVCP] Project PQ Approved for ${projectName}` - : "[eVCP] General PQ Approved"; - - const portalUrl = `${host}/partners/pq`; - - await sendEmail({ - to: vendor.email, - subject: emailSubject, - template: "pq-approved-vendor", - context: { - vendorName: vendor.vendorName, - projectId: pqSubmission.projectId, - projectName: projectName, - isProjectPQ: !!pqSubmission.projectId, - approvedDate: currentDate.toLocaleString(), - portalUrl, - } - }); - } catch (emailError) { - console.error("Failed to send vendor notification:", emailError); - // 이메일 발송 실패가 전체 프로세스를 중단하지 않음 - } - } - - // 8. 캐시 무효화 - revalidateTag("vendors"); - revalidateTag("vendor-status-counts"); - revalidateTag("pq-submissions"); - revalidateTag(`vendor-pq-submissions-${vendorId}`); - - if (pqSubmission.projectId) { - revalidateTag(`project-pq-submissions-${pqSubmission.projectId}`); - revalidateTag(`project-vendors-${pqSubmission.projectId}`); - } - - return { ok: true }; - } catch (error) { - console.error("PQ approve error:", error); - return { ok: false, error: getErrorMessage(error) }; - } -} - -// PQ 거부 액션 -export async function rejectPQAction({ - pqSubmissionId, - vendorId, - rejectReason -}: { - pqSubmissionId: number; - vendorId: number; - rejectReason: string; -}) { - unstable_noStore(); - - try { - const headersList = await headers(); - const host = headersList.get('host') || 'localhost:3000'; - const currentDate = new Date(); - - // 1. PQ 제출 정보 조회 - const pqSubmission = await db - .select({ - id: vendorPQSubmissions.id, - vendorId: vendorPQSubmissions.vendorId, - projectId: vendorPQSubmissions.projectId, - type: vendorPQSubmissions.type, - status: vendorPQSubmissions.status, - }) - .from(vendorPQSubmissions) - .where( - and( - eq(vendorPQSubmissions.id, pqSubmissionId), - eq(vendorPQSubmissions.vendorId, vendorId) - ) - ) - .then(rows => rows[0]); - - if (!pqSubmission) { - return { ok: false, error: "PQ submission not found" }; - } - - // 2. 상태 확인 (SUBMITTED 상태만 거부 가능) - if (pqSubmission.status !== "SUBMITTED") { - return { - ok: false, - error: `Cannot reject PQ in current status: ${pqSubmission.status}` - }; - } - - // 3. 벤더 정보 조회 - const vendor = await db - .select({ - id: vendors.id, - vendorName: vendors.vendorName, - email: vendors.email, - status: vendors.status, - }) - .from(vendors) - .where(eq(vendors.id, vendorId)) - .then(rows => rows[0]); - - if (!vendor) { - return { ok: false, error: "Vendor not found" }; - } - - // 4. 프로젝트 정보 (프로젝트 PQ인 경우) - let projectName = ''; - if (pqSubmission.projectId) { - const projectData = await db - .select({ - id: projects.id, - name: projects.name, - }) - .from(projects) - .where(eq(projects.id, pqSubmission.projectId)) - .then(rows => rows[0]); - - projectName = projectData?.name || 'Unknown Project'; - } - - // 5. PQ 상태 업데이트 - await db - .update(vendorPQSubmissions) - .set({ - status: "REJECTED", - rejectedAt: currentDate, - rejectReason: rejectReason, - updatedAt: currentDate, - }) - .where(eq(vendorPQSubmissions.id, pqSubmissionId)); - - // 6. 일반 PQ인 경우 벤더 상태 업데이트 (선택사항) - if (pqSubmission.type === "GENERAL") { - await db - .update(vendors) - .set({ - status: "PQ_FAILED", - updatedAt: currentDate, - }) - .where(eq(vendors.id, vendorId)); - } - - // 7. 벤더에게 이메일 알림 발송 - if (vendor.email) { - try { - const emailSubject = pqSubmission.projectId - ? `[eVCP] Project PQ Rejected for ${projectName}` - : "[eVCP] General PQ Rejected"; - - const portalUrl = `${host}/partners/pq`; - - await sendEmail({ - to: vendor.email, - subject: emailSubject, - template: "pq-rejected-vendor", - context: { - vendorName: vendor.vendorName, - projectId: pqSubmission.projectId, - projectName: projectName, - isProjectPQ: !!pqSubmission.projectId, - rejectedDate: currentDate.toLocaleString(), - rejectReason: rejectReason, - portalUrl, - } - }); - } catch (emailError) { - console.error("Failed to send vendor notification:", emailError); - // 이메일 발송 실패가 전체 프로세스를 중단하지 않음 - } - } - - // 8. 캐시 무효화 - revalidateTag("vendors"); - revalidateTag("vendor-status-counts"); - revalidateTag("pq-submissions"); - revalidateTag(`vendor-pq-submissions-${vendorId}`); - - if (pqSubmission.projectId) { - revalidateTag(`project-pq-submissions-${pqSubmission.projectId}`); - revalidateTag(`project-vendors-${pqSubmission.projectId}`); - } - - return { ok: true }; - } catch (error) { - console.error("PQ reject error:", error); - return { ok: false, error: getErrorMessage(error) }; - } -} - - -// 실사 의뢰 생성 서버 액션 -export async function requestInvestigationAction( - pqSubmissionIds: number[], - data: { - evaluationType: "SITE_AUDIT" | "QM_SELF_AUDIT", - qmManagerId: number, - forecastedAt: Date, - investigationAddress: string, - investigationNotes?: string - } -) { - try { - // 세션에서 요청자 정보 가져오기 - const session = await getServerSession(authOptions); - const requesterId = session?.user?.id ? Number(session.user.id) : null; - - if (!requesterId) { - return { success: false, error: "인증된 사용자만 실사를 의뢰할 수 있습니다." }; - } - - const result = await db.transaction(async (tx) => { - // PQ 제출 정보 조회 - const pqSubmissions = await tx - .select({ - id: vendorPQSubmissions.id, - vendorId: vendorPQSubmissions.vendorId, - }) - .from(vendorPQSubmissions) - .where( - and( - inArray(vendorPQSubmissions.id, pqSubmissionIds), - eq(vendorPQSubmissions.status, "APPROVED") - ) - ); - - if (pqSubmissions.length === 0) { - throw new Error("승인된 PQ 제출 항목이 없습니다."); - } - - const now = new Date(); - - // 각 PQ에 대한 실사 요청 생성 - 타입이 정확히 맞는지 확인 - const investigations = pqSubmissions.map((pq) => { - return { - vendorId: pq.vendorId, - pqSubmissionId: pq.id, - investigationStatus: "PLANNED" as const, // enum 타입으로 명시적 지정 - evaluationType: data.evaluationType, - qmManagerId: data.qmManagerId, - forecastedAt: data.forecastedAt, - investigationAddress: data.investigationAddress, - investigationNotes: data.investigationNotes || null, - requesterId: requesterId, - requestedAt: now, - createdAt: now, - updatedAt: now, - }; - }); - - // 실사 요청 저장 - const created = await tx - .insert(vendorInvestigations) - .values(investigations) - .returning(); - - return created; - }); - - // 캐시 무효화 - revalidateTag("vendor-investigations"); - revalidateTag("pq-submissions"); - - return { - success: true, - count: result.length, - data: result - }; - } catch (err) { - console.error("실사 의뢰 중 오류 발생:", err); - return { - success: false, - error: err instanceof Error ? err.message : "알 수 없는 오류가 발생했습니다." - }; - } -} - -// 실사 의뢰 취소 서버 액션 -export async function cancelInvestigationAction(investigationIds: number[]) { - try { - const session = await getServerSession(authOptions) - const userId = session?.user?.id ? Number(session.user.id) : null - - if (!userId) { - return { success: false, error: "인증된 사용자만 실사를 취소할 수 있습니다." } - } - - const result = await db.transaction(async (tx) => { - // PLANNED 상태인 실사만 취소 가능 - const updatedInvestigations = await tx - .update(vendorInvestigations) - .set({ - investigationStatus: "CANCELED", - updatedAt: new Date(), - }) - .where( - and( - inArray(vendorInvestigations.id, investigationIds), - eq(vendorInvestigations.investigationStatus, "PLANNED") - ) - ) - .returning() - - return updatedInvestigations - }) - - // 캐시 무효화 - revalidateTag("vendor-investigations") - revalidateTag("pq-submissions") - - return { - success: true, - count: result.length, - data: result - } - } catch (err) { - console.error("실사 취소 중 오류 발생:", err) - return { - success: false, - error: err instanceof Error ? err.message : "알 수 없는 오류가 발생했습니다." - } - } -} - -// 실사 결과 발송 서버 액션 -export async function sendInvestigationResultsAction(investigationIds: number[]) { - try { - const session = await getServerSession(authOptions) - const userId = session?.user?.id ? Number(session.user.id) : null - - if (!userId) { - return { success: false, error: "인증된 사용자만 실사 결과를 발송할 수 있습니다." } - } - - // 여기서는 실사 상태를 업데이트하고, 필요하다면 이메일도 발송할 수 있습니다 - // 이메일 발송 로직은 서버 액션 내에서 구현할 수 있습니다 - const result = await db.transaction(async (tx) => { - // 완료된 실사만 결과 발송 가능 - const investigations = await tx - .select() - .from(vendorInvestigations) - .where( - and( - inArray(vendorInvestigations.id, investigationIds), - eq(vendorInvestigations.investigationStatus, "COMPLETED") - ) - ) - - if (investigations.length === 0) { - throw new Error("발송할 수 있는 완료된 실사가 없습니다.") - } - - // 여기에 이메일 발송 로직 추가 - // 예: await sendInvestigationResultEmails(investigations) - - // 필요하다면 상태 업데이트 (예: 결과 발송됨 상태 추가) - const updatedInvestigations = await tx - .update(vendorInvestigations) - .set({ - // 예시: 결과 발송 표시를 위한 필드 업데이트 - // resultSent: true, - // resultSentAt: new Date(), - updatedAt: new Date(), - }) - .where( - inArray(vendorInvestigations.id, investigationIds) - ) - .returning() - - return updatedInvestigations - }) - - // 캐시 무효화 - revalidateTag("vendor-investigations") - revalidateTag("pq-submissions") - - return { - success: true, - count: result.length, - data: result - } - } catch (err) { - console.error("실사 결과 발송 중 오류 발생:", err) - return { - success: false, - error: err instanceof Error ? err.message : "알 수 없는 오류가 발생했습니다." - } - } -} - - -export async function getQMManagers() { - try { - // QM 부서 사용자만 필터링 (department 필드가 있다고 가정) - // 또는 QM 역할을 가진 사용자만 필터링 (role 필드가 있다고 가정) - const qmUsers = await db - .select({ - id: users.id, - name: users.name, - email: users.email, - }) - .from(users) - // .where( - // // 필요에 따라 조건 조정 (예: QM 부서 또는 특정 역할만) - // // eq(users.department, "QM") 또는 - // // eq(users.role, "QM_MANAGER") - // // 테스트를 위해 모든 사용자 반환도 가능 - // eq(users.active, true) - // ) - .orderBy(users.name) - - return { - data: qmUsers, - success: true - } - } catch (error) { - console.error("QM 담당자 목록 조회 오류:", error) - return { - data: [], - success: false, - error: error instanceof Error ? error.message : "QM 담당자 목록을 가져오는 중 오류가 발생했습니다." - } - } -} - -export async function getFactoryLocationAnswer(vendorId: number, projectId: number | null = null) { - try { - // 1. "Location of Factory" 체크포인트를 가진 criteria 찾기 - const criteria = await db - .select({ - id: pqCriterias.id - }) - .from(pqCriterias) - .where(ilike(pqCriterias.checkPoint, "%Location of Factory%")) - .limit(1); - - if (!criteria.length) { - return { success: false, message: "Factory Location 질문을 찾을 수 없습니다." }; - } - - const criteriaId = criteria[0].id; - - // 2. 해당 criteria에 대한 벤더의 응답 조회 - const answerQuery = db - .select({ - answer: vendorPqCriteriaAnswers.answer - }) - .from(vendorPqCriteriaAnswers) - .where( - and( - eq(vendorPqCriteriaAnswers.vendorId, vendorId), - eq(vendorPqCriteriaAnswers.criteriaId, criteriaId) - ) - ); - - // 프로젝트 ID가 있으면 추가 조건 - if (projectId !== null) { - answerQuery.where(eq(vendorPqCriteriaAnswers.projectId, projectId)); - } else { - answerQuery.where(eq(vendorPqCriteriaAnswers.projectId, null)); - } - - const answers = await answerQuery.limit(1); - - if (!answers.length || !answers[0].answer) { - return { success: false, message: "공장 위치 정보를 찾을 수 없습니다." }; - } - - return { - success: true, - factoryLocation: answers[0].answer - }; - } catch (error) { - console.error("Factory location 조회 오류:", error); - return { success: false, message: "오류가 발생했습니다." }; - } +"use server"
+
+import db from "@/db/db"
+import { CopyPqListInput, CreatePqListInput, copyPqListSchema, createPqListSchema, GetPqListsSchema, GetPQSchema, GetPQSubmissionsSchema } from "./validations"
+import { unstable_cache } from "@/lib/unstable-cache";
+import { filterColumns } from "@/lib/filter-columns";
+import { getErrorMessage } from "@/lib/handle-error";
+import { asc, desc, ilike, inArray, and, gte, lte, not, or, eq, count,isNull,SQL, sql, lt, isNotNull} from "drizzle-orm";
+import { z } from "zod"
+import { revalidateTag, unstable_noStore, revalidatePath} from "next/cache";
+import { format } from "date-fns"
+import { pqCriterias, vendorCriteriaAttachments, vendorInvestigations, vendorPQSubmissions, vendorPqCriteriaAnswers, vendorPqReviewLogs, siteVisitRequests, vendorSiteVisitInfo, siteVisitRequestAttachments } from "@/db/schema/pq"
+import { sendEmail } from "../mail/sendEmail";
+import { decryptWithServerAction } from '@/components/drm/drmUtils'
+
+import { vendorAttachments, vendors } from "@/db/schema/vendors";
+import { saveFile, saveDRMFile } from "@/lib/file-stroage";
+import { GetVendorsSchema } from "../vendors/validations";
+import { selectVendors } from "../vendors/repository";
+import { projects, users } from "@/db/schema";
+import { headers } from 'next/headers';
+import { getServerSession } from "next-auth/next"
+import { authOptions } from "@/app/api/auth/[...nextauth]/route"
+import { alias } from 'drizzle-orm/pg-core';
+import { createPQFilterMapping, getPQJoinedTables } from "./helper";
+import { pqLists } from "@/db/schema/pq";
+
+export interface PQAttachment {
+ attachId: number
+ fileName: string
+ filePath: string
+ fileSize?: number
+}
+
+export interface PQItem {
+ answerId: number | null
+ criteriaId: number
+ code: string
+ checkPoint: string
+ description: string | null
+ remarks?: string | null
+ // 프로젝트 PQ 전용 필드
+ contractInfo?: string | null
+ additionalRequirement?: string | null
+ answer: string
+ shiComment: string
+ vendorReply: string
+ attachments: PQAttachment[]
+ subGroupName: string
+ inputFormat: string
+
+ createdAt: Date | null
+ updatedAt: Date | null
+}
+
+export interface PQGroupData {
+ groupName: string
+ items: PQItem[]
+}
+
+export interface ProjectPQ {
+ id: number;
+ projectId: number | null;
+ status: string;
+ submittedAt: Date | null;
+ projectCode: string;
+ projectName: string;
+}
+
+export async function getPQProjectsByVendorId(vendorId: number): Promise<ProjectPQ[]> {
+ const result = await db
+ .select({
+ id: vendorPQSubmissions.id,
+ projectId: vendorPQSubmissions.projectId,
+ status: vendorPQSubmissions.status,
+ submittedAt: vendorPQSubmissions.submittedAt,
+ projectCode: projects.code,
+ projectName: projects.name,
+ })
+ .from(vendorPQSubmissions)
+ .innerJoin(
+ projects,
+ eq(vendorPQSubmissions.projectId, projects.id)
+ )
+ .where(eq(vendorPQSubmissions.vendorId, vendorId))
+ .orderBy(projects.code);
+
+ return result;
+}
+
+export async function getPQDataByVendorId(
+ vendorId: number,
+ projectId?: number
+): Promise<PQGroupData[]> {
+ try {
+ // 기본 쿼리 구성
+ const selectObj = {
+ criteriaId: pqCriterias.id,
+ groupName: pqCriterias.groupName,
+ code: pqCriterias.code,
+ checkPoint: pqCriterias.checkPoint,
+ description: pqCriterias.description,
+ remarks: pqCriterias.remarks,
+
+ // 입력 형식 필드 추가
+ inputFormat: pqCriterias.inputFormat,
+
+ // 협력업체 응답 필드
+ answer: vendorPqCriteriaAnswers.answer,
+ answerId: vendorPqCriteriaAnswers.id,
+
+ // SHI 코멘트와 벤더 답변 필드 추가
+ shiComment: vendorPqCriteriaAnswers.shiComment,
+ vendorReply: vendorPqCriteriaAnswers.vendorReply,
+ createdAt: vendorPqCriteriaAnswers.createdAt,
+ updatedAt: vendorPqCriteriaAnswers.updatedAt,
+
+ // 첨부 파일 필드
+ attachId: vendorCriteriaAttachments.id,
+ fileName: vendorCriteriaAttachments.fileName,
+ filePath: vendorCriteriaAttachments.filePath,
+ fileSize: vendorCriteriaAttachments.fileSize,
+ };
+
+ // Create separate queries for each case instead of modifying the same query variable
+ if (projectId) {
+ // 프로젝트별 PQ 쿼리 - PQ 리스트 기반으로 변경
+ const rows = await db
+ .select(selectObj)
+ .from(pqCriterias)
+ .innerJoin(
+ pqLists,
+ and(
+ eq(pqCriterias.pqListId, pqLists.id),
+ eq(pqLists.projectId, projectId),
+ eq(pqLists.type, "PROJECT"),
+ eq(pqLists.isDeleted, false)
+ )
+ )
+ .leftJoin(
+ vendorPqCriteriaAnswers,
+ and(
+ eq(pqCriterias.id, vendorPqCriteriaAnswers.criteriaId),
+ eq(vendorPqCriteriaAnswers.vendorId, vendorId),
+ eq(vendorPqCriteriaAnswers.projectId, projectId)
+ )
+ )
+ .leftJoin(
+ vendorCriteriaAttachments,
+ eq(vendorPqCriteriaAnswers.id, vendorCriteriaAttachments.vendorCriteriaAnswerId)
+ )
+ .orderBy(pqCriterias.groupName, pqCriterias.code);
+
+ return processQueryResults(rows);
+ } else {
+ // 일반 PQ 쿼리 - PQ 리스트 기반으로 변경
+ const rows = await db
+ .select(selectObj)
+ .from(pqCriterias)
+ .innerJoin(
+ pqLists,
+ and(
+ eq(pqCriterias.pqListId, pqLists.id),
+ eq(pqLists.type, "GENERAL"),
+ eq(pqLists.isDeleted, false)
+ )
+ )
+ .leftJoin(
+ vendorPqCriteriaAnswers,
+ and(
+ eq(pqCriterias.id, vendorPqCriteriaAnswers.criteriaId),
+ eq(vendorPqCriteriaAnswers.vendorId, vendorId),
+ isNull(vendorPqCriteriaAnswers.projectId)
+ )
+ )
+ .leftJoin(
+ vendorCriteriaAttachments,
+ eq(vendorPqCriteriaAnswers.id, vendorCriteriaAttachments.vendorCriteriaAnswerId)
+ )
+ .orderBy(pqCriterias.groupName, pqCriterias.code);
+
+ return processQueryResults(rows);
+ }
+ } catch (error) {
+ console.error("Error fetching PQ data:", error);
+ return [];
+ }
+
+ // Helper function to process query results
+ function processQueryResults(rows: any[]) {
+ // 그룹별로 데이터 구성
+ const groupMap = new Map<string, Record<number, PQItem>>();
+
+ for (const row of rows) {
+ const g = row.groupName || "Others";
+
+ // 그룹 확인
+ if (!groupMap.has(g)) {
+ groupMap.set(g, {});
+ }
+
+ const groupItems = groupMap.get(g)!;
+
+ // 아직 이 기준을 처리하지 않았으면 PQItem 생성
+ if (!groupItems[row.criteriaId]) {
+ groupItems[row.criteriaId] = {
+ answerId: row.answerId,
+ criteriaId: row.criteriaId,
+ code: row.code,
+ checkPoint: row.checkPoint,
+ description: row.description,
+ remarks: row.remarks,
+ answer: row.answer || "",
+ shiComment: row.shiComment || "",
+ vendorReply: row.vendorReply || "",
+ attachments: [],
+ inputFormat: row.inputFormat || "",
+
+ subGroupName: row.subGroupName || "",
+ createdAt: row.createdAt,
+ updatedAt: row.updatedAt,
+ };
+ }
+
+ // 첨부 파일이 있으면 추가
+ if (row.attachId) {
+ groupItems[row.criteriaId].attachments.push({
+ attachId: row.attachId,
+ fileName: row.fileName || "",
+ filePath: row.filePath || "",
+ fileSize: row.fileSize || undefined,
+ });
+ }
+ }
+
+ // 최종 데이터 구성
+ const data: PQGroupData[] = [];
+ for (const [groupName, itemsMap] of groupMap.entries()) {
+ const items = Object.values(itemsMap);
+ data.push({ groupName, items });
+ }
+
+ return data;
+ }
+}
+
+
+interface PQAttachmentInput {
+ fileName: string // original user-friendly file name
+ url: string // the UUID-based path stored on server
+ size?: number // optional file size
+}
+
+interface SavePQAnswer {
+ criteriaId: number
+ answer: string
+ shiComment?: string
+ vendorReply?: string
+ attachments: PQAttachmentInput[]
+}
+
+interface SavePQInput {
+ vendorId: number
+ projectId?: number
+ answers: SavePQAnswer[]
+}
+
+/**
+ * 여러 항목을 한 번에 Upsert
+ */
+export async function savePQAnswersAction(input: SavePQInput) {
+ const { vendorId, projectId, answers } = input
+
+ try {
+ for (const ans of answers) {
+ // 1) Check if a row already exists for (vendorId, criteriaId, projectId)
+ const queryConditions = [
+ eq(vendorPqCriteriaAnswers.vendorId, vendorId),
+ eq(vendorPqCriteriaAnswers.criteriaId, ans.criteriaId)
+ ];
+
+ // Add projectId condition when it exists
+ if (projectId !== undefined) {
+ queryConditions.push(eq(vendorPqCriteriaAnswers.projectId, projectId));
+ } else {
+ queryConditions.push(isNull(vendorPqCriteriaAnswers.projectId));
+ }
+
+ const existing = await db
+ .select()
+ .from(vendorPqCriteriaAnswers)
+ .where(and(...queryConditions));
+
+ let answerId: number
+
+ // 2) If it exists, update the row; otherwise insert
+ if (existing.length === 0) {
+ // Insert new
+ const inserted = await db
+ .insert(vendorPqCriteriaAnswers)
+ .values({
+ vendorId,
+ criteriaId: ans.criteriaId,
+ projectId: projectId || null, // Include projectId when provided
+ answer: ans.answer,
+ shiComment: ans.shiComment || null,
+ vendorReply: ans.vendorReply || null,
+ createdAt: new Date(),
+ updatedAt: new Date(),
+ })
+ .returning({ id: vendorPqCriteriaAnswers.id })
+
+ answerId = inserted[0].id
+ } else {
+ // Update existing
+ answerId = existing[0].id
+
+ await db
+ .update(vendorPqCriteriaAnswers)
+ .set({
+ answer: ans.answer,
+ shiComment: ans.shiComment || null,
+ vendorReply: ans.vendorReply || null,
+ updatedAt: new Date(),
+ })
+ .where(eq(vendorPqCriteriaAnswers.id, answerId))
+ }
+
+ // 3) Now manage attachments in vendorCriteriaAttachments
+ // 3a) Load old attachments from DB
+ const oldAttachments = await db
+ .select({
+ id: vendorCriteriaAttachments.id,
+ filePath: vendorCriteriaAttachments.filePath,
+ })
+ .from(vendorCriteriaAttachments)
+ .where(eq(vendorCriteriaAttachments.vendorCriteriaAnswerId, answerId))
+
+ // 3b) Gather the new filePaths (urls) from the client
+ const newPaths = ans.attachments.map(a => a.url)
+
+ // 3c) Find attachments to remove
+ const toRemove = oldAttachments.filter(old => !newPaths.includes(old.filePath))
+ if (toRemove.length > 0) {
+ const removeIds = toRemove.map(r => r.id)
+ await db
+ .delete(vendorCriteriaAttachments)
+ .where(inArray(vendorCriteriaAttachments.id, removeIds))
+ }
+
+ // 3d) Insert new attachments that aren't in DB
+ const oldPaths = oldAttachments.map(o => o.filePath)
+ const toAdd = ans.attachments.filter(a => !oldPaths.includes(a.url))
+
+ for (const attach of toAdd) {
+ await db.insert(vendorCriteriaAttachments).values({
+ vendorCriteriaAnswerId: answerId,
+ fileName: attach.fileName,
+ filePath: attach.url,
+ fileSize: attach.size ?? null,
+ })
+ }
+ }
+
+ return { ok: true }
+ } catch (error) {
+ console.error("savePQAnswersAction error:", error)
+ return { ok: false, error: String(error) }
+ }
+}
+
+
+
+/**
+ * PQ 제출 서버 액션 - 협력업체 상태를 PQ_SUBMITTED로 업데이트
+ * @param vendorId 협력업체 ID
+ */
+export async function submitPQAction({
+ vendorId,
+ projectId,
+ pqSubmissionId
+}: {
+ vendorId: number;
+ projectId?: number;
+ pqSubmissionId?: number; // 특정 PQ 제출 ID가 있는 경우 사용
+}) {
+ unstable_noStore();
+
+ try {
+ const headersList = await headers();
+ const host = headersList.get('host') || 'localhost:3000';
+
+ // 1. 모든 PQ 항목에 대한 응답이 있는지 검증
+ const answerQueryConditions = [
+ eq(vendorPqCriteriaAnswers.vendorId, vendorId)
+ ];
+
+ // Add projectId condition when it exists
+ if (projectId !== undefined) {
+ answerQueryConditions.push(eq(vendorPqCriteriaAnswers.projectId, projectId));
+ } else {
+ answerQueryConditions.push(isNull(vendorPqCriteriaAnswers.projectId));
+ }
+
+ const pqCriteriaCount = await db
+ .select({ count: count() })
+ .from(vendorPqCriteriaAnswers)
+ .where(and(...answerQueryConditions));
+
+ const totalPqCriteriaCount = pqCriteriaCount[0]?.count || 0;
+
+ // 응답 데이터 검증
+ if (totalPqCriteriaCount === 0) {
+ return { ok: false, error: "No PQ answers found" };
+ }
+
+ // 2. 협력업체 정보 조회
+ const vendor = await db
+ .select({
+ id: vendors.id,
+ vendorName: vendors.vendorName,
+ email: vendors.email,
+ status: vendors.status,
+ })
+ .from(vendors)
+ .where(eq(vendors.id, vendorId))
+ .then(rows => rows[0]);
+
+ if (!vendor) {
+ return { ok: false, error: "Vendor not found" };
+ }
+
+ // Project 정보 조회 (projectId가 있는 경우)
+ let projectName = '';
+ if (projectId) {
+ const projectData = await db
+ .select({
+ projectName: projects.name
+ })
+ .from(projects)
+ .where(eq(projects.id, projectId))
+ .then(rows => rows[0]);
+
+ projectName = projectData?.projectName || 'Unknown Project';
+ }
+
+ // 3. 현재 PQ 제출 상태 확인 및 업데이트
+ const currentDate = new Date();
+ let existingSubmission;
+
+ // 특정 PQ Submission ID가 있는 경우
+ if (pqSubmissionId) {
+ existingSubmission = await db
+ .select({
+ id: vendorPQSubmissions.id,
+ status: vendorPQSubmissions.status,
+ type: vendorPQSubmissions.type
+ })
+ .from(vendorPQSubmissions)
+ .where(
+ and(
+ eq(vendorPQSubmissions.id, pqSubmissionId),
+ eq(vendorPQSubmissions.vendorId, vendorId)
+ )
+ )
+ .then(rows => rows[0]);
+
+ if (!existingSubmission) {
+ return { ok: false, error: "PQ submission not found or access denied" };
+ }
+ }
+ // ID가 없는 경우 vendorId와 projectId로 조회
+ else {
+ const pqType = projectId ? "PROJECT" : "GENERAL";
+
+ const submissionQueryConditions = [
+ eq(vendorPQSubmissions.vendorId, vendorId),
+ eq(vendorPQSubmissions.type, pqType)
+ ];
+
+ if (projectId) {
+ submissionQueryConditions.push(eq(vendorPQSubmissions.projectId, projectId));
+ } else {
+ submissionQueryConditions.push(isNull(vendorPQSubmissions.projectId));
+ }
+
+ existingSubmission = await db
+ .select({
+ id: vendorPQSubmissions.id,
+ status: vendorPQSubmissions.status,
+ type: vendorPQSubmissions.type
+ })
+ .from(vendorPQSubmissions)
+ .where(and(...submissionQueryConditions))
+ .then(rows => rows[0]);
+ }
+
+ // 제출 가능한 상태 확인
+ const allowedStatuses = ["REQUESTED", "IN_PROGRESS", "REJECTED"];
+
+ if (existingSubmission) {
+ if (!allowedStatuses.includes(existingSubmission.status)) {
+ return {
+ ok: false,
+ error: `Cannot submit PQ in current status: ${existingSubmission.status}`
+ };
+ }
+
+ // 기존 제출 상태 업데이트
+ await db
+ .update(vendorPQSubmissions)
+ .set({
+ status: "SUBMITTED",
+ submittedAt: currentDate,
+ updatedAt: currentDate,
+ })
+ .where(eq(vendorPQSubmissions.id, existingSubmission.id));
+ } else {
+ // PQ Submission ID가 없고 기존 submission도 없는 경우 새로운 제출 생성
+ const pqType = projectId ? "PROJECT" : "GENERAL";
+
+ // PQ 번호 생성 (예: PQ-2024-001)
+ const currentYear = new Date().getFullYear();
+ const pqNumber = `PQ-${currentYear}-${String(vendorId).padStart(3, '0')}`;
+
+ await db
+ .insert(vendorPQSubmissions)
+ .values({
+ pqNumber,
+ vendorId,
+ projectId: projectId || null,
+ type: pqType,
+ status: "SUBMITTED",
+ submittedAt: currentDate,
+ createdAt: currentDate,
+ updatedAt: currentDate,
+ });
+ }
+
+ // 4. 일반 PQ인 경우 벤더 상태도 업데이트
+ if (!projectId) {
+ const allowedVendorStatuses = ["IN_PQ", "PENDING_REVIEW", "IN_REVIEW", "REJECTED", "PQ_FAILED"];
+
+ if (allowedVendorStatuses.includes(vendor.status)) {
+ await db
+ .update(vendors)
+ .set({
+ status: "PQ_SUBMITTED",
+ updatedAt: currentDate,
+ })
+ .where(eq(vendors.id, vendorId));
+ }
+ }
+
+ // 5. 관리자에게 이메일 알림 발송
+ if (process.env.ADMIN_EMAIL) {
+ try {
+ const emailSubject = projectId
+ ? `[eVCP] Project PQ Submitted: ${vendor.vendorName} for ${projectName}`
+ : `[eVCP] General PQ Submitted: ${vendor.vendorName}`;
+
+ const adminUrl = `http://${host}/evcp/pq/${vendorId}/${existingSubmission?.id || ''}`;
+
+ await sendEmail({
+ to: process.env.ADMIN_EMAIL,
+ subject: emailSubject,
+ template: "pq-submitted-admin",
+ context: {
+ vendorName: vendor.vendorName,
+ vendorId: vendor.id,
+ projectId: projectId,
+ projectName: projectName,
+ isProjectPQ: !!projectId,
+ submittedDate: currentDate.toLocaleString(),
+ adminUrl,
+ }
+ });
+ } catch (emailError) {
+ console.error("Failed to send admin notification:", emailError);
+ }
+ }
+
+ // 6. 벤더에게 확인 이메일 발송
+ if (vendor.email) {
+ try {
+ const emailSubject = projectId
+ ? `[eVCP] Project PQ Submission Confirmation for ${projectName}`
+ : "[eVCP] General PQ Submission Confirmation";
+
+ const portalUrl = `${host}/partners/pq`;
+
+ await sendEmail({
+ to: vendor.email,
+ subject: emailSubject,
+ template: "pq-submitted-vendor",
+ context: {
+ vendorName: vendor.vendorName,
+ projectId: projectId,
+ projectName: projectName,
+ isProjectPQ: !!projectId,
+ submittedDate: currentDate.toLocaleString(),
+ portalUrl,
+ }
+ });
+ } catch (emailError) {
+ console.error("Failed to send vendor confirmation:", emailError);
+ }
+ }
+
+ // 7. 캐시 무효화
+ revalidateTag("vendors");
+ revalidateTag("vendor-status-counts");
+ revalidateTag(`vendor-pq-submissions-${vendorId}`);
+
+ if (projectId) {
+ revalidateTag(`project-pq-submissions-${projectId}`);
+ revalidateTag(`project-vendors-${projectId}`);
+ revalidateTag(`project-pq-${projectId}`);
+ }
+
+ return { ok: true };
+ } catch (error) {
+ console.error("PQ submit error:", error);
+ return { ok: false, error: getErrorMessage(error) };
+ }
+}
+
+/**
+ * 향상된 파일 업로드 서버 액션
+ * - 직접 파일 처리 (file 객체로 받음)
+ * - 디렉토리 자동 생성
+ * - 중복 방지를 위한 UUID 적용
+ */
+/**
+ * 벤더용 파일 업로드 액션 (saveFile 사용)
+ */
+export async function uploadVendorFileAction(file: File, userId?: string) {
+ unstable_noStore();
+
+ try {
+ const result = await saveFile({
+ file,
+ directory: 'pq/vendor',
+ originalName: file.name,
+ userId,
+ });
+
+ if (!result.success) {
+ throw new Error(result.error || "파일 업로드에 실패했습니다.");
+ }
+
+ return {
+ fileName: result.fileName!,
+ url: result.publicPath!,
+ size: result.fileSize!,
+ };
+ } catch (error) {
+ console.error("Vendor file upload error:", error);
+ throw new Error(`Upload failed: ${getErrorMessage(error)}`);
+ }
+}
+
+/**
+ * SHI용 파일 업로드 액션 (saveDRMFile 사용)
+ */
+export async function uploadSHIFileAction(file: File, userId?: string) {
+ unstable_noStore();
+
+ try {
+ const result = await saveDRMFile(
+ file,
+ decryptWithServerAction,
+ 'pq/shi',
+ userId
+ );
+
+ if (!result.success) {
+ throw new Error(result.error || "파일 업로드에 실패했습니다.");
+ }
+
+ return {
+ fileName: result.fileName!,
+ url: result.publicPath!,
+ size: result.fileSize!,
+ };
+ } catch (error) {
+ console.error("SHI file upload error:", error);
+ throw new Error(`Upload failed: ${getErrorMessage(error)}`);
+ }
+}
+
+/**
+ * 벤더용 여러 파일 일괄 업로드
+ */
+export async function uploadVendorMultipleFilesAction(files: File[], userId?: string) {
+ unstable_noStore();
+
+ try {
+ const results = [];
+
+ for (const file of files) {
+ try {
+ const result = await uploadVendorFileAction(file, userId);
+ results.push({
+ success: true,
+ ...result
+ });
+ } catch (error) {
+ results.push({
+ success: false,
+ fileName: file.name,
+ error: getErrorMessage(error)
+ });
+ }
+ }
+
+ return {
+ ok: true,
+ results
+ };
+ } catch (error) {
+ console.error("Vendor batch upload error:", error);
+ return {
+ ok: false,
+ error: getErrorMessage(error)
+ };
+ }
+}
+
+/**
+ * SHI용 여러 파일 일괄 업로드
+ */
+export async function uploadSHIMultipleFilesAction(files: File[], userId?: string) {
+ unstable_noStore();
+
+ try {
+ const results = [];
+
+ for (const file of files) {
+ try {
+ const result = await uploadSHIFileAction(file, userId);
+ results.push({
+ success: true,
+ ...result
+ });
+ } catch (error) {
+ results.push({
+ success: false,
+ fileName: file.name,
+ error: getErrorMessage(error)
+ });
+ }
+ }
+
+ return {
+ ok: true,
+ results
+ };
+ } catch (error) {
+ console.error("SHI batch upload error:", error);
+ return {
+ ok: false,
+ error: getErrorMessage(error)
+ };
+ }
+}
+
+export async function getVendorsInPQ(input: GetVendorsSchema) {
+ return unstable_cache(
+ async () => {
+ try {
+ const offset = (input.page - 1) * input.perPage;
+
+ // 1) 고급 필터
+ const advancedWhere = filterColumns({
+ table: vendors,
+ filters: input.filters,
+ joinOperator: input.joinOperator,
+ });
+
+ // 2) 글로벌 검색
+ let globalWhere: SQL<unknown> | undefined = undefined;
+ if (input.search) {
+ const s = `%${input.search}%`;
+ globalWhere = or(
+ ilike(vendors.vendorName, s),
+ ilike(vendors.vendorCode, s),
+ ilike(vendors.email, s),
+ ilike(vendors.status, s)
+ );
+ }
+
+ // 트랜잭션 내에서 데이터 조회
+ const { data, total } = await db.transaction(async (tx) => {
+ // 협력업체 ID 모음 (중복 제거용)
+ const vendorIds = new Set<number>();
+
+ // 1-A) 일반 PQ 답변이 있는 협력업체 찾기 (status와 상관없이)
+ const generalPqVendors = await tx
+ .select({
+ vendorId: vendorPqCriteriaAnswers.vendorId
+ })
+ .from(vendorPqCriteriaAnswers)
+ .innerJoin(
+ vendors,
+ eq(vendorPqCriteriaAnswers.vendorId, vendors.id)
+ )
+ .where(
+ and(
+ isNull(vendorPqCriteriaAnswers.projectId), // 일반 PQ만 (프로젝트 PQ 아님)
+ advancedWhere,
+ globalWhere
+ )
+ )
+ .groupBy(vendorPqCriteriaAnswers.vendorId); // 각 벤더당 한 번만 카운트
+
+ generalPqVendors.forEach(v => vendorIds.add(v.vendorId));
+
+ // 1-B) 프로젝트 PQ 답변이 있는 협력업체 ID 조회 (status와 상관없이)
+ const projectPqVendors = await tx
+ .select({
+ vendorId: vendorPQSubmissions.vendorId
+ })
+ .from(vendorPQSubmissions)
+ .innerJoin(
+ vendors,
+ eq(vendorPQSubmissions.vendorId, vendors.id)
+ )
+ .where(
+ and(
+ eq(vendorPQSubmissions.type, "PROJECT"),
+ // 최소한 IN_PROGRESS부터는 작업이 시작된 상태이므로 포함
+ not(eq(vendorPQSubmissions.status, "REQUESTED")), // REQUESTED 상태는 제외
+ advancedWhere,
+ globalWhere
+ )
+ );
+
+ projectPqVendors.forEach(v => vendorIds.add(v.vendorId));
+
+ // 중복 제거된 협력업체 ID 배열
+ const uniqueVendorIds = Array.from(vendorIds);
+
+ // 총 개수 (중복 제거 후)
+ const total = uniqueVendorIds.length;
+
+ if (total === 0) {
+ return { data: [], total: 0 };
+ }
+
+ // 페이징 처리 (정렬 후 limit/offset 적용)
+ const paginatedIds = uniqueVendorIds.slice(offset, offset + input.perPage);
+
+ // 2) 페이징된 협력업체 상세 정보 조회
+ const vendorsData = await selectVendors(tx, {
+ where: inArray(vendors.id, paginatedIds),
+ orderBy: input.sort.length > 0
+ ? input.sort.map((item) =>
+ item.desc ? desc(vendors.vendorName) : asc(vendors.vendorName)
+ )
+ : [asc(vendors.createdAt)],
+ });
+
+ // 3) 각 벤더별 PQ 상태 정보 추가
+ const vendorsWithPqInfo = await Promise.all(
+ vendorsData.map(async (vendor) => {
+ // 3-A) 첨부 파일 조회
+ const attachments = await tx
+ .select({
+ id: vendorAttachments.id,
+ fileName: vendorAttachments.fileName,
+ filePath: vendorAttachments.filePath,
+ })
+ .from(vendorAttachments)
+ .where(eq(vendorAttachments.vendorId, vendor.id));
+
+ // 3-B) 일반 PQ 제출 여부 확인 (PQ 답변이 있는지)
+ const generalPqAnswers = await tx
+ .select({ count: count() })
+ .from(vendorPqCriteriaAnswers)
+ .where(
+ and(
+ eq(vendorPqCriteriaAnswers.vendorId, vendor.id),
+ isNull(vendorPqCriteriaAnswers.projectId)
+ )
+ );
+
+ const hasGeneralPq = generalPqAnswers[0]?.count > 0;
+
+ // 3-C) 프로젝트 PQ 정보 조회 (모든 상태 포함)
+ const projectPqs = await tx
+ .select({
+ projectId: vendorPQSubmissions.projectId,
+ projectName: projects.name,
+ status: vendorPQSubmissions.status,
+ submittedAt: vendorPQSubmissions.submittedAt,
+ approvedAt: vendorPQSubmissions.approvedAt,
+ rejectedAt: vendorPQSubmissions.rejectedAt
+ })
+ .from(vendorPQSubmissions)
+ .innerJoin(
+ projects,
+ eq(vendorPQSubmissions.projectId, projects.id)
+ )
+ .where(
+ and(
+ eq(vendorPQSubmissions.vendorId, vendor.id),
+ eq(vendorPQSubmissions.type, "PROJECT"),
+ not(eq(vendorPQSubmissions.status, "REQUESTED")) // REQUESTED 상태는 제외
+ )
+ );
+
+ const hasProjectPq = projectPqs.length > 0;
+
+ // 프로젝트 PQ 상태별 카운트
+ const projectPqStatusCounts = {
+ inProgress: projectPqs.filter(p => p.status === "IN_PROGRESS").length,
+ submitted: projectPqs.filter(p => p.status === "SUBMITTED").length,
+ approved: projectPqs.filter(p => p.status === "APPROVED").length,
+ rejected: projectPqs.filter(p => p.status === "REJECTED").length,
+ total: projectPqs.length
+ };
+
+ // 3-D) PQ 상태 정보 추가
+ return {
+ ...vendor,
+ hasAttachments: attachments.length > 0,
+ attachmentsList: attachments,
+ pqInfo: {
+ hasGeneralPq,
+ hasProjectPq,
+ projectPqs,
+ projectPqStatusCounts,
+ // 현재 PQ 상태 (UI에 표시 용도)
+ pqStatus: getPqStatusDisplay(vendor.status, hasGeneralPq, hasProjectPq, projectPqStatusCounts)
+ }
+ };
+ })
+ );
+
+ return { data: vendorsWithPqInfo, total };
+ });
+
+ // 페이지 수
+ const pageCount = Math.ceil(total / input.perPage);
+
+ return { data, pageCount };
+ } catch (err) {
+ console.error("Error in getVendorsInPQ:", err);
+ // 에러 발생 시
+ return { data: [], pageCount: 0 };
+ }
+ },
+ [JSON.stringify(input)], // 캐싱 키
+ {
+ revalidate: 3600,
+ tags: ["vendors-in-pq", "project-pqs"], // revalidateTag 호출 시 무효화
+ }
+ )();
+}
+
+// PQ 상태 표시 함수
+function getPqStatusDisplay(
+ vendorStatus: string,
+ hasGeneralPq: boolean,
+ hasProjectPq: boolean,
+ projectPqCounts: { inProgress: number, submitted: number, approved: number, rejected: number, total: number }
+): string {
+ // 프로젝트 PQ 상태 문자열 생성
+ let projectPqStatus = "";
+ if (hasProjectPq) {
+ const parts = [];
+ if (projectPqCounts.inProgress > 0) {
+ parts.push(`진행중: ${projectPqCounts.inProgress}`);
+ }
+ if (projectPqCounts.submitted > 0) {
+ parts.push(`제출: ${projectPqCounts.submitted}`);
+ }
+ if (projectPqCounts.approved > 0) {
+ parts.push(`승인: ${projectPqCounts.approved}`);
+ }
+ if (projectPqCounts.rejected > 0) {
+ parts.push(`거부: ${projectPqCounts.rejected}`);
+ }
+ projectPqStatus = parts.join(", ");
+ }
+
+ // 일반 PQ + 프로젝트 PQ 조합 상태
+ if (hasGeneralPq && hasProjectPq) {
+ return `일반 PQ (${getPqVendorStatusText(vendorStatus)}) + 프로젝트 PQ (${projectPqStatus})`;
+ } else if (hasGeneralPq) {
+ return `일반 PQ (${getPqVendorStatusText(vendorStatus)})`;
+ } else if (hasProjectPq) {
+ return `프로젝트 PQ (${projectPqStatus})`;
+ }
+
+ return "PQ 정보 없음";
+}
+
+// 협력업체 상태 텍스트 변환
+function getPqVendorStatusText(status: string): string {
+ switch (status) {
+ case "IN_PQ": return "진행중";
+ case "PQ_SUBMITTED": return "제출됨";
+ case "PQ_FAILED": return "실패";
+ case "PQ_APPROVED":
+ case "APPROVED": return "승인됨";
+ case "READY_TO_SEND": return "거래 준비";
+ case "ACTIVE": return "활성";
+ case "INACTIVE": return "비활성";
+ case "BLACKLISTED": return "거래금지";
+ default: return status;
+ }
+}
+
+
+export type VendorStatus =
+ | "PENDING_REVIEW"
+ | "IN_REVIEW"
+ | "REJECTED"
+ | "IN_PQ"
+ | "PQ_SUBMITTED"
+ | "PQ_FAILED"
+ | "APPROVED"
+ | "ACTIVE"
+ | "INACTIVE"
+ | "BLACKLISTED"
+ | "PQ_APPROVED"
+
+ export async function updateVendorStatusAction(
+ vendorId: number,
+ newStatus: VendorStatus
+ ) {
+ try {
+ // 1) Update DB
+ await db.update(vendors)
+ .set({ status: newStatus })
+ .where(eq(vendors.id, vendorId))
+
+ // 2) Load vendor's email & name
+ const vendor = await db.select().from(vendors).where(eq(vendors.id, vendorId)).then(r => r[0])
+ if (!vendor) {
+ return { ok: false, error: "Vendor not found" }
+ }
+ const headersList = await headers();
+ const host = headersList.get('host') || 'localhost:3000';
+ const loginUrl = `http://${host}/partners/pq`
+
+ // 3) Send email
+ await sendEmail({
+ to: vendor.email || "",
+ subject: `Your PQ Status is now ${newStatus}`,
+ template: "vendor-pq-status", // matches .hbs file
+ context: {
+ name: vendor.vendorName,
+ status: newStatus,
+ loginUrl: loginUrl, // etc.
+ },
+ })
+ revalidateTag("vendors")
+ revalidateTag("vendors-in-pq")
+ return { ok: true }
+ } catch (error) {
+ console.error("updateVendorStatusAction error:", error)
+ return { ok: false, error: String(error) }
+ }
+ }
+
+ type ProjectPQStatus = "REQUESTED" | "IN_PROGRESS" | "SUBMITTED" | "APPROVED" | "REJECTED";
+
+/**
+ * Update the status of a project-specific PQ for a vendor
+ */
+export async function updateProjectPQStatusAction({
+ vendorId,
+ projectId,
+ status,
+ comment
+}: {
+ vendorId: number;
+ projectId: number;
+ status: ProjectPQStatus;
+ comment?: string;
+}) {
+ try {
+ const currentDate = new Date();
+
+ // 1) Prepare update data with appropriate timestamps
+ const updateData: any = {
+ status,
+ updatedAt: currentDate,
+ };
+
+ // Add status-specific fields
+ if (status === "APPROVED") {
+ updateData.approvedAt = currentDate;
+ } else if (status === "REJECTED") {
+ updateData.rejectedAt = currentDate;
+ updateData.rejectReason = comment || null;
+ } else if (status === "SUBMITTED") {
+ updateData.submittedAt = currentDate;
+ }
+
+ // 2) Update the project PQ record
+ await db
+ .update(vendorPQSubmissions)
+ .set(updateData)
+ .where(
+ and(
+ eq(vendorPQSubmissions.vendorId, vendorId),
+ eq(vendorPQSubmissions.projectId, projectId),
+ eq(vendorPQSubmissions.type, "PROJECT")
+ )
+ );
+
+ // 3) Load vendor and project details for email
+ const vendor = await db
+ .select({
+ id: vendors.id,
+ email: vendors.email,
+ vendorName: vendors.vendorName
+ })
+ .from(vendors)
+ .where(eq(vendors.id, vendorId))
+ .then(rows => rows[0]);
+
+ if (!vendor) {
+ return { ok: false, error: "Vendor not found" };
+ }
+
+ const project = await db
+ .select({
+ name: projects.name
+ })
+ .from(projects)
+ .where(eq(projects.id, projectId))
+ .then(rows => rows[0]);
+
+ if (!project) {
+ return { ok: false, error: "Project not found" };
+ }
+
+ // 4) Send email notification
+ await sendEmail({
+ to: vendor.email || "",
+ subject: `Your Project PQ for ${project.name} is now ${status}`,
+ template: "vendor-project-pq-status", // matches .hbs file (you might need to create this)
+ context: {
+ name: vendor.vendorName,
+ status,
+ projectName: project.name,
+ rejectionReason: status === "REJECTED" ? comment : undefined,
+ hasRejectionReason: status === "REJECTED" && !!comment,
+ loginUrl: `${process.env.NEXT_PUBLIC_URL}/partners/pq?projectId=${projectId}`,
+ approvalDate: status === "APPROVED" ? currentDate.toLocaleDateString() : undefined,
+ rejectionDate: status === "REJECTED" ? currentDate.toLocaleDateString() : undefined,
+ },
+ });
+
+ // 5) Revalidate cache tags
+ revalidateTag("vendors");
+ revalidateTag("vendors-in-pq");
+ revalidateTag(`vendor-project-pqs-${vendorId}`);
+ revalidateTag(`project-pq-${projectId}`);
+ revalidateTag(`project-vendors-${projectId}`);
+
+ return { ok: true };
+ } catch (error) {
+ console.error("updateProjectPQStatusAction error:", error);
+ return { ok: false, error: String(error) };
+ }
+}
+
+// 코멘트 타입 정의
+interface ItemComment {
+ answerId: number;
+ checkPoint: string; // 체크포인트 정보 추가
+ code: string; // 코드 정보 추가
+ comment: string;
+}
+
+/**
+ * PQ 변경 요청 처리 서버 액션
+ *
+ * @param vendorId 협력업체 ID
+ * @param comment 항목별 코멘트 배열 (answerId, checkPoint, code, comment로 구성)
+ * @param generalComment 전체 PQ에 대한 일반 코멘트 (선택사항)
+ */
+export async function requestPqChangesAction({
+ vendorId,
+ projectId,
+ comment,
+ generalComment,
+ reviewerName
+}: {
+ vendorId: number;
+ projectId?: number; // Optional project ID for project-specific PQs
+ comment: ItemComment[];
+ generalComment?: string;
+ reviewerName?: string;
+}) {
+ try {
+ // 1) 상태 업데이트 (PQ 타입에 따라 다르게 처리)
+ if (projectId) {
+ // 프로젝트 PQ인 경우 vendorPQSubmissions 테이블 업데이트
+ const projectPq = await db
+ .select()
+ .from(vendorPQSubmissions)
+ .where(
+ and(
+ eq(vendorPQSubmissions.vendorId, vendorId),
+ eq(vendorPQSubmissions.projectId, projectId),
+ eq(vendorPQSubmissions.type, "PROJECT")
+ )
+ )
+ .then(rows => rows[0]);
+
+ if (!projectPq) {
+ return { ok: false, error: "Project PQ record not found" };
+ }
+
+ await db
+ .update(vendorPQSubmissions)
+ .set({
+ status: "IN_PROGRESS", // 변경 요청 상태로 설정
+ updatedAt: new Date(),
+ })
+ .where(
+ and(
+ eq(vendorPQSubmissions.vendorId, vendorId),
+ eq(vendorPQSubmissions.projectId, projectId),
+ eq(vendorPQSubmissions.type, "PROJECT")
+ )
+ );
+ } else {
+ // 일반 PQ인 경우 vendors 테이블 업데이트
+ await db
+ .update(vendors)
+ .set({
+ status: "IN_PQ", // 변경 요청 상태로 설정
+ updatedAt: new Date(),
+ })
+ .where(eq(vendors.id, vendorId));
+ }
+
+ // 2) 협력업체 정보 가져오기
+ const vendor = await db
+ .select()
+ .from(vendors)
+ .where(eq(vendors.id, vendorId))
+ .then(r => r[0]);
+
+ if (!vendor) {
+ return { ok: false, error: "Vendor not found" };
+ }
+
+ // 프로젝트 정보 가져오기 (프로젝트 PQ인 경우)
+ let projectName = "";
+ if (projectId) {
+ const project = await db
+ .select({
+ name: projects.name
+ })
+ .from(projects)
+ .where(eq(projects.id, projectId))
+ .then(rows => rows[0]);
+
+ projectName = project?.name || "Unknown Project";
+ }
+
+ // 3) 각 항목별 코멘트 저장
+ const currentDate = new Date();
+
+
+ // 병렬로 모든 코멘트 저장
+ if (comment && comment.length > 0) {
+ const insertPromises = comment.map(item =>
+ db.insert(vendorPqReviewLogs)
+ .values({
+ vendorPqCriteriaAnswerId: item.answerId,
+ // reviewerId: reviewerId,
+ reviewerName: reviewerName,
+ reviewerComment: item.comment,
+ createdAt: currentDate,
+ // 추가 메타데이터 필드가 있다면 저장
+ // 이런 메타데이터는 DB 스키마에 해당 필드가 있어야 함
+ // meta: JSON.stringify({ checkPoint: item.checkPoint, code: item.code })
+ })
+ );
+
+ // 모든 삽입 기다리기
+ await Promise.all(insertPromises);
+ }
+
+ // 4) 변경 요청 이메일 보내기
+ // 코멘트 목록 준비
+ const commentItems = comment.map(item => ({
+ id: item.answerId,
+ code: item.code,
+ checkPoint: item.checkPoint,
+ text: item.comment
+ }));
+
+ // PQ 유형에 따라 이메일 제목 및 내용 조정
+ const emailSubject = projectId
+ ? `[IMPORTANT] Your Project PQ (${projectName}) requires changes`
+ : `[IMPORTANT] Your PQ submission requires changes`;
+
+ // 로그인 URL - 프로젝트 PQ인 경우 다른 경로로 안내
+ const loginUrl = projectId
+ ? `${process.env.NEXT_PUBLIC_URL}/partners/pq?projectId=${projectId}`
+ : `${process.env.NEXT_PUBLIC_URL}/partners/pq`;
+
+ await sendEmail({
+ to: vendor.email || "",
+ subject: emailSubject,
+ template: "vendor-pq-comment", // matches .hbs file
+ context: {
+ name: vendor.vendorName,
+ vendorCode: vendor.vendorCode,
+ loginUrl,
+ comments: commentItems,
+ generalComment: generalComment || "",
+ hasGeneralComment: !!generalComment,
+ commentCount: commentItems.length,
+ projectId,
+ projectName,
+ isProjPQ: !!projectId,
+ },
+ });
+
+ // 5) 캐시 무효화 - PQ 유형에 따라 적절한 태그 무효화
+ revalidateTag("vendors");
+ revalidateTag("vendors-in-pq");
+
+ if (projectId) {
+ revalidateTag(`vendor-project-pqs-${vendorId}`);
+ revalidateTag(`project-pq-${projectId}`);
+ revalidateTag(`project-vendors-${projectId}`);
+ }
+
+ return { ok: true };
+ } catch (error) {
+ console.error("requestPqChangesAction error:", error);
+ return { ok: false, error: String(error) };
+ }
+}
+
+interface AddReviewCommentInput {
+ answerId: number // vendorPqCriteriaAnswers.id
+ comment: string
+ reviewerName?: string
+}
+
+export async function addReviewCommentAction(input: AddReviewCommentInput) {
+ try {
+ // 1) Check that the answer row actually exists
+ const existing = await db
+ .select({ id: vendorPqCriteriaAnswers.id })
+ .from(vendorPqCriteriaAnswers)
+ .where(eq(vendorPqCriteriaAnswers.id, input.answerId))
+
+ if (existing.length === 0) {
+ return { ok: false, error: "Item not found" }
+ }
+
+ // 2) Insert the log
+ await db.insert(vendorPqReviewLogs).values({
+ vendorPqCriteriaAnswerId: input.answerId,
+ reviewerComment: input.comment,
+ reviewerName: input.reviewerName ?? "AdminUser",
+ })
+
+ return { ok: true }
+ } catch (error) {
+ console.error("addReviewCommentAction error:", error)
+ return { ok: false, error: String(error) }
+ }
+}
+
+interface GetItemReviewLogsInput {
+ answerId: number
+}
+
+export async function getItemReviewLogsAction(input: GetItemReviewLogsInput) {
+ try {
+
+ const logs = await db
+ .select()
+ .from(vendorPqReviewLogs)
+ .where(eq(vendorPqReviewLogs.vendorPqCriteriaAnswerId, input.answerId))
+ .orderBy(desc(vendorPqReviewLogs.createdAt));
+
+ return { ok: true, data: logs };
+ } catch (error) {
+ console.error("getItemReviewLogsAction error:", error);
+ return { ok: false, error: String(error) };
+ }
+}
+
+export interface VendorPQListItem {
+ projectId: number;
+ projectName: string;
+ status: string;
+ submittedAt?: Date | null; // Change to accept both undefined and null
+}
+
+export interface VendorPQsList {
+ hasGeneralPq: boolean;
+ generalPqStatus?: string; // vendor.status for general PQ
+ projectPQs: VendorPQListItem[];
+}
+
+export async function getVendorPQsList(vendorId: number): Promise<VendorPQsList> {
+ try {
+ // 1. Check if vendor has general PQ answers
+ const generalPqAnswers = await db
+ .select({ count: count() })
+ .from(vendorPqCriteriaAnswers)
+ .where(
+ and(
+ eq(vendorPqCriteriaAnswers.vendorId, vendorId),
+ isNull(vendorPqCriteriaAnswers.projectId)
+ )
+ );
+
+ const hasGeneralPq = (generalPqAnswers[0]?.count || 0) > 0;
+
+ // 2. Get vendor status for general PQ
+ let generalPqStatus;
+ if (hasGeneralPq) {
+ const vendor = await db
+ .select({ status: vendors.status })
+ .from(vendors)
+ .where(eq(vendors.id, vendorId))
+ .then(rows => rows[0]);
+
+ generalPqStatus = vendor?.status;
+ }
+
+ // 3. Get project PQs
+ const projectPQs = await db
+ .select({
+ projectId: vendorPQSubmissions.projectId,
+ projectName: projects.name,
+ status: vendorPQSubmissions.status,
+ submittedAt: vendorPQSubmissions.submittedAt
+ })
+ .from(vendorPQSubmissions)
+ .innerJoin(
+ projects,
+ eq(vendorPQSubmissions.projectId, projects.id)
+ )
+ .where(
+ and(
+ eq(vendorPQSubmissions.vendorId, vendorId),
+ eq(vendorPQSubmissions.type, "PROJECT"),
+ not(eq(vendorPQSubmissions.status, "REQUESTED")) // Exclude requests that haven't been started
+ )
+ )
+ .orderBy(vendorPQSubmissions.updatedAt);
+
+ return {
+ hasGeneralPq,
+ generalPqStatus,
+ projectPQs: projectPQs
+ };
+
+ } catch (error) {
+ console.error("Error fetching vendor PQs list:", error);
+ return {
+ hasGeneralPq: false,
+ projectPQs: []
+ };
+ }
+}
+
+
+export async function loadGeneralPQData(vendorId: number) {
+ "use server";
+ return getPQDataByVendorId(vendorId)
+}
+
+export async function loadProjectPQData(vendorId: number, projectId: number) {
+ "use server";
+ return getPQDataByVendorId(vendorId, projectId)
+}
+
+export async function loadGeneralPQAction(vendorId: number) {
+ return getPQDataByVendorId(vendorId);
+}
+
+export async function loadProjectPQAction(vendorId: number, projectId?: number): Promise<PQGroupData[]> {
+ if (!projectId) {
+ throw new Error("Project ID is required for loading project PQ data");
+ }
+ return getPQDataByVendorId(vendorId, projectId);
+}
+
+
+
+export async function getAllPQsByVendorId(vendorId: number) {
+ try {
+ const pqList = await db
+ .select({
+ id: vendorPQSubmissions.id,
+ type: vendorPQSubmissions.type,
+ status: vendorPQSubmissions.status,
+ projectId: vendorPQSubmissions.projectId,
+ projectName: projects.name,
+ createdAt: vendorPQSubmissions.createdAt,
+ updatedAt: vendorPQSubmissions.updatedAt,
+ submittedAt: vendorPQSubmissions.submittedAt,
+ approvedAt: vendorPQSubmissions.approvedAt,
+ rejectedAt: vendorPQSubmissions.rejectedAt,
+ rejectReason: vendorPQSubmissions.rejectReason,
+ })
+ .from(vendorPQSubmissions)
+ .leftJoin(projects, eq(vendorPQSubmissions.projectId, projects.id))
+ .where(eq(vendorPQSubmissions.vendorId, vendorId))
+ .orderBy(desc(vendorPQSubmissions.createdAt));
+
+ return pqList;
+ } catch (error) {
+ console.error("Error fetching PQ list:", error);
+ return [];
+ }
+}
+
+// 특정 PQ의 상세 정보 조회 (개별 PQ 페이지용)
+export async function getPQById(pqSubmissionId: number, vendorId: number) {
+ try {
+ const pq = await db
+ .select({
+ id: vendorPQSubmissions.id,
+ vendorId: vendorPQSubmissions.vendorId,
+ projectId: vendorPQSubmissions.projectId,
+ type: vendorPQSubmissions.type,
+ status: vendorPQSubmissions.status,
+ createdAt: vendorPQSubmissions.createdAt,
+ submittedAt: vendorPQSubmissions.submittedAt,
+ approvedAt: vendorPQSubmissions.approvedAt,
+ rejectedAt: vendorPQSubmissions.rejectedAt,
+ rejectReason: vendorPQSubmissions.rejectReason,
+
+ // 벤더 정보 (추가)
+ vendorName: vendors.vendorName,
+ vendorCode: vendors.vendorCode,
+ vendorStatus: vendors.status,
+
+ // 프로젝트 정보 (조인)
+ projectName: projects.name,
+ projectCode: projects.code,
+ })
+ .from(vendorPQSubmissions)
+ .leftJoin(vendors, eq(vendorPQSubmissions.vendorId, vendors.id))
+ .leftJoin(projects, eq(vendorPQSubmissions.projectId, projects.id))
+ .where(
+ and(
+ eq(vendorPQSubmissions.id, pqSubmissionId),
+ eq(vendorPQSubmissions.vendorId, vendorId)
+ )
+ )
+ .limit(1)
+ .then(rows => rows[0]);
+
+ if (!pq) {
+ throw new Error("PQ not found or access denied");
+ }
+
+ return pq;
+ } catch (error) {
+ console.error("Error fetching PQ by ID:", error);
+ throw error;
+ }
+}
+
+export async function getPQStatusCounts(vendorId: number) {
+ try {
+ // 모든 PQ 상태 조회 (일반 PQ + 프로젝트 PQ)
+ const pqStatuses = await db
+ .select({
+ status: vendorPQSubmissions.status,
+ count: count(),
+ })
+ .from(vendorPQSubmissions)
+ .where(eq(vendorPQSubmissions.vendorId, vendorId))
+ .groupBy(vendorPQSubmissions.status);
+
+ // 상태별 개수를 객체로 변환
+ const statusCounts = {
+ REQUESTED: 0,
+ IN_PROGRESS: 0,
+ SUBMITTED: 0,
+ APPROVED: 0,
+ REJECTED: 0,
+ };
+
+ // 조회된 결과를 statusCounts 객체에 매핑
+ pqStatuses.forEach((item) => {
+ if (item.status in statusCounts) {
+ statusCounts[item.status as keyof typeof statusCounts] = item.count;
+ }
+ });
+
+ return statusCounts;
+ } catch (error) {
+ console.error("Error fetching PQ status counts:", error);
+ return {
+ REQUESTED: 0,
+ IN_PROGRESS: 0,
+ SUBMITTED: 0,
+ APPROVED: 0,
+ REJECTED: 0,
+ };
+ }
+}
+
+// 상태 레이블 함수
+function getStatusLabel(status: string): string {
+ switch (status) {
+ case "REQUESTED":
+ return "요청됨";
+ case "IN_PROGRESS":
+ return "진행 중";
+ case "SUBMITTED":
+ return "제출됨";
+ case "APPROVED":
+ return "승인됨";
+ case "REJECTED":
+ return "거부됨";
+ default:
+ return status;
+ }
+}
+
+export async function getPQSubmissions(input: GetPQSubmissionsSchema) {
+ return unstable_cache(
+ async () => {
+ try {
+ const offset = (input.page - 1) * input.perPage;
+
+ const pqFilterMapping = createPQFilterMapping();
+ const joinedTables = getPQJoinedTables();
+
+ console.log(input, "input")
+
+ // 1) 고급 필터 조건 (DataTableAdvancedToolbar에서)
+ let advancedWhere: SQL<unknown> | undefined = undefined;
+ if (input.filters && input.filters.length > 0) {
+ advancedWhere = filterColumns({
+ table: vendorPQSubmissions,
+ filters: input.filters,
+ joinOperator: input.joinOperator || 'and',
+ joinedTables,
+ customColumnMapping: pqFilterMapping,
+ });
+ console.log("advancedWhere:", advancedWhere);
+ }
+
+ // 2) 기본 필터 조건 (PQFilterSheet에서)
+ let basicWhere: SQL<unknown> | undefined = undefined;
+ if (input.basicFilters && input.basicFilters.length > 0) {
+ basicWhere = filterColumns({
+ table: vendorPQSubmissions,
+ filters: input.basicFilters,
+ joinOperator: input.basicJoinOperator || 'and',
+ joinedTables,
+ customColumnMapping: pqFilterMapping,
+ });
+ console.log("basicWhere:", basicWhere);
+ }
+
+ // 3) 글로벌 검색 조건
+ let globalWhere: SQL<unknown> | undefined = undefined;
+ if (input.search) {
+ const s = `%${input.search}%`;
+
+ const validSearchConditions: SQL<unknown>[] = [];
+
+ // 기존 검색 조건들
+ const nameCondition = ilike(vendors.vendorName, s);
+ if (nameCondition) validSearchConditions.push(nameCondition);
+
+ const codeCondition = ilike(vendors.vendorCode, s);
+ if (codeCondition) validSearchConditions.push(codeCondition);
+
+ const projectNameCondition = ilike(projects.name, s);
+ if (projectNameCondition) validSearchConditions.push(projectNameCondition);
+
+ const projectCodeCondition = ilike(projects.code, s);
+ if (projectCodeCondition) validSearchConditions.push(projectCodeCondition);
+
+ // 새로 추가된 검색 조건들
+ const pqNumberCondition = ilike(vendorPQSubmissions.pqNumber, s);
+ if (pqNumberCondition) validSearchConditions.push(pqNumberCondition);
+
+ const requesterCondition = ilike(users.name, s);
+ if (requesterCondition) validSearchConditions.push(requesterCondition);
+
+ if (validSearchConditions.length > 0) {
+ globalWhere = or(...validSearchConditions);
+ }
+ }
+
+ // 4) 날짜 조건
+ let fromDateWhere: SQL<unknown> | undefined = undefined;
+ let toDateWhere: SQL<unknown> | undefined = undefined;
+
+ if (input.submittedDateFrom) {
+ const fromDate = new Date(input.submittedDateFrom);
+ const condition = gte(vendorPQSubmissions.submittedAt, fromDate);
+ if (condition) fromDateWhere = condition;
+ }
+
+ if (input.submittedDateTo) {
+ const toDate = new Date(input.submittedDateTo);
+ const condition = lte(vendorPQSubmissions.submittedAt, toDate);
+ if (condition) toDateWhere = condition;
+ }
+
+ // 5) 최종 WHERE 조건 생성 - 각 그룹을 AND로 연결
+ const whereConditions: SQL<unknown>[] = [];
+
+ // 고급 필터 조건 추가
+ if (advancedWhere) whereConditions.push(advancedWhere);
+
+ // 기본 필터 조건 추가
+ if (basicWhere) whereConditions.push(basicWhere);
+
+ // 기타 조건들 추가
+ if (globalWhere) whereConditions.push(globalWhere);
+ if (fromDateWhere) whereConditions.push(fromDateWhere);
+ if (toDateWhere) whereConditions.push(toDateWhere);
+
+ // 모든 조건을 AND로 연결
+ const finalWhere = whereConditions.length > 0 ? and(...whereConditions) : undefined;
+
+ console.log("Final WHERE conditions:", {
+ advancedWhere: !!advancedWhere,
+ basicWhere: !!basicWhere,
+ globalWhere: !!globalWhere,
+ dateConditions: !!(fromDateWhere || toDateWhere),
+ totalConditions: whereConditions.length
+ });
+
+ // 6) 전체 데이터 수 조회
+ const totalResult = await db
+ .select({ count: count() })
+ .from(vendorPQSubmissions)
+ .leftJoin(vendors, eq(vendorPQSubmissions.vendorId, vendors.id))
+ .leftJoin(projects, eq(vendorPQSubmissions.projectId, projects.id))
+ .leftJoin(users, eq(vendorPQSubmissions.requesterId, users.id))
+ .leftJoin(vendorInvestigations, eq(vendorInvestigations.pqSubmissionId, vendorPQSubmissions.id))
+ .where(finalWhere);
+
+ const total = totalResult[0]?.count || 0;
+
+ if (total === 0) {
+ return { data: [], pageCount: 0 };
+ }
+
+ // 7) 정렬 및 페이징 처리된 데이터 조회
+ const orderByColumns = input.sort.map((sort) => {
+ const column = sort.id as keyof typeof vendorPQSubmissions.$inferSelect;
+ return sort.desc ? desc(vendorPQSubmissions[column]) : asc(vendorPQSubmissions[column]);
+ });
+
+ if (orderByColumns.length === 0) {
+ orderByColumns.push(desc(vendorPQSubmissions.updatedAt));
+ }
+
+ const pqSubmissions = await db
+ .select({
+ id: vendorPQSubmissions.id,
+ type: vendorPQSubmissions.type,
+ pqNumber: vendorPQSubmissions.pqNumber,
+ requesterId: vendorPQSubmissions.requesterId,
+ requesterName: users.name,
+ status: vendorPQSubmissions.status,
+ createdAt: vendorPQSubmissions.createdAt,
+ updatedAt: vendorPQSubmissions.updatedAt,
+ submittedAt: vendorPQSubmissions.submittedAt,
+ approvedAt: vendorPQSubmissions.approvedAt,
+ rejectedAt: vendorPQSubmissions.rejectedAt,
+ rejectReason: vendorPQSubmissions.rejectReason,
+ pqItems: vendorPQSubmissions.pqItems,
+ // Vendor 정보
+ vendorId: vendors.id,
+ vendorName: vendors.vendorName,
+ vendorCode: vendors.vendorCode,
+ email: vendors.email,
+ taxId: vendors.taxId,
+ vendorStatus: vendors.status,
+ // Project 정보 (프로젝트 PQ인 경우)
+ projectId: projects.id,
+ projectName: projects.name,
+ projectCode: projects.code,
+ })
+ .from(vendorPQSubmissions)
+ .leftJoin(vendors, eq(vendorPQSubmissions.vendorId, vendors.id))
+ .leftJoin(projects, eq(vendorPQSubmissions.projectId, projects.id))
+ .leftJoin(users, eq(vendorPQSubmissions.requesterId, users.id))
+ .where(finalWhere)
+ .orderBy(...orderByColumns)
+ .limit(input.perPage)
+ .offset(offset);
+
+ // 8) 각 PQ 제출에 대한 추가 정보 조회 (기존과 동일)
+ const pqSubmissionsWithDetails = await Promise.all(
+ pqSubmissions.map(async (submission) => {
+ // 기본 반환 객체
+ const baseResult = {
+ ...submission,
+ answerCount: 0,
+ attachmentCount: 0,
+ pqStatus: getStatusLabel(submission.status),
+ pqTypeLabel: submission.type === "GENERAL" ? "일반 PQ" : "프로젝트 PQ",
+ };
+
+ // vendorId가 null이면 기본 정보만 반환
+ if (submission.vendorId === null) {
+ return baseResult;
+ }
+
+ try {
+ // 답변 수 조회
+ const vendorId = submission.vendorId;
+
+ const answerWhereConditions: SQL<unknown>[] = [];
+
+ const vendorCondition = eq(vendorPqCriteriaAnswers.vendorId, vendorId);
+ if (vendorCondition) answerWhereConditions.push(vendorCondition);
+
+ let projectCondition: SQL<unknown> | undefined;
+ if (submission.projectId !== null) {
+ projectCondition = eq(vendorPqCriteriaAnswers.projectId, submission.projectId);
+ } else {
+ projectCondition = isNull(vendorPqCriteriaAnswers.projectId);
+ }
+
+ if (projectCondition) answerWhereConditions.push(projectCondition);
+
+ const answerWhere = and(...answerWhereConditions);
+
+ const answersResult = await db
+ .select({ count: count() })
+ .from(vendorPqCriteriaAnswers)
+ .where(answerWhere);
+
+ const answerCount = answersResult[0]?.count || 0;
+
+ // 첨부 파일 수 조회
+ const attachmentsResult = await db
+ .select({ count: count() })
+ .from(vendorPqCriteriaAnswers)
+ .leftJoin(
+ vendorCriteriaAttachments,
+ eq(vendorCriteriaAttachments.vendorCriteriaAnswerId, vendorPqCriteriaAnswers.id)
+ )
+ .where(answerWhere);
+
+ const attachmentCount = attachmentsResult[0]?.count || 0;
+
+ const requesters = alias(users, 'requesters');
+ const qmManagers = alias(users, 'qmManagers');
+
+ const investigationResult = await db
+ .select({
+ id: vendorInvestigations.id,
+ investigationStatus: vendorInvestigations.investigationStatus,
+ evaluationType: vendorInvestigations.evaluationType,
+ investigationAddress: vendorInvestigations.investigationAddress,
+ investigationMethod: vendorInvestigations.investigationMethod,
+ scheduledStartAt: vendorInvestigations.scheduledStartAt,
+ scheduledEndAt: vendorInvestigations.scheduledEndAt,
+ requestedAt: vendorInvestigations.requestedAt,
+ confirmedAt: vendorInvestigations.confirmedAt,
+ completedAt: vendorInvestigations.completedAt,
+ forecastedAt: vendorInvestigations.forecastedAt,
+ evaluationScore: vendorInvestigations.evaluationScore,
+ evaluationResult: vendorInvestigations.evaluationResult,
+ investigationNotes: vendorInvestigations.investigationNotes,
+ requesterId: vendorInvestigations.requesterId,
+ requesterName: requesters.name,
+ qmManagerId: vendorInvestigations.qmManagerId,
+ qmManagerName: qmManagers.name,
+ qmManagerEmail: qmManagers.email,
+ })
+ .from(vendorInvestigations)
+ .leftJoin(requesters, eq(vendorInvestigations.requesterId, requesters.id))
+ .leftJoin(qmManagers, eq(vendorInvestigations.qmManagerId, qmManagers.id))
+ .where(and(
+ eq(vendorInvestigations.vendorId, submission.vendorId),
+ eq(vendorInvestigations.pqSubmissionId, submission.id)
+ ))
+ .orderBy(desc(vendorInvestigations.createdAt))
+ .limit(1);
+
+ const investigation = investigationResult[0] || null;
+
+ // investigation이 있으면 해당 investigation의 최신 siteVisitRequest 조회
+ let siteVisitRequestId: number | null = null;
+ if (investigation) {
+ const siteVisitRequestResult = await db
+ .select({ id: siteVisitRequests.id })
+ .from(siteVisitRequests)
+ .where(eq(siteVisitRequests.investigationId, investigation.id))
+ .orderBy(desc(siteVisitRequests.createdAt))
+ .limit(1);
+
+ siteVisitRequestId = siteVisitRequestResult[0]?.id || null;
+ }
+
+ return {
+ ...baseResult,
+ answerCount,
+ attachmentCount,
+ siteVisitRequestId,
+ investigation
+ };
+ } catch (error) {
+ console.error("Error fetching PQ details:", error);
+ return baseResult;
+ }
+ })
+ );
+
+ const pageCount = Math.ceil(total / input.perPage);
+
+ return { data: pqSubmissionsWithDetails, pageCount };
+ } catch (err) {
+ console.error("Error in getPQSubmissions:", err);
+ return { data: [], pageCount: 0 };
+ }
+ },
+ [JSON.stringify(input)], // 캐싱 키
+ {
+ revalidate: 3600,
+ tags: ["pq-submissions"], // revalidateTag 호출 시 무효화
+ }
+ )();
+}
+
+export async function getPQStatusCountsAll() {
+ try {
+ // 모든 PQ 상태별 개수 조회 (벤더 제한 없음)
+ const pqStatuses = await db
+ .select({
+ status: vendorPQSubmissions.status,
+ count: count(),
+ })
+ .from(vendorPQSubmissions)
+ .groupBy(vendorPQSubmissions.status);
+
+ // 상태별 개수를 객체로 변환
+ const statusCounts = {
+ REQUESTED: 0,
+ IN_PROGRESS: 0,
+ SUBMITTED: 0,
+ APPROVED: 0,
+ REJECTED: 0,
+ };
+
+ // 조회된 결과를 statusCounts 객체에 매핑
+ pqStatuses.forEach((item) => {
+ if (item.status in statusCounts) {
+ statusCounts[item.status as keyof typeof statusCounts] = item.count;
+ }
+ });
+
+ return statusCounts;
+ } catch (error) {
+ console.error("Error fetching PQ status counts:", error);
+ return {
+ REQUESTED: 0,
+ IN_PROGRESS: 0,
+ SUBMITTED: 0,
+ APPROVED: 0,
+ REJECTED: 0,
+ };
+ }
+}
+
+// PQ 타입별, 상태별 개수 집계 함수 (추가 옵션)
+export async function getPQDetailedStatusCounts() {
+ try {
+ // 타입별, 상태별 개수 조회
+ const pqStatuses = await db
+ .select({
+ type: vendorPQSubmissions.type,
+ status: vendorPQSubmissions.status,
+ count: count(),
+ })
+ .from(vendorPQSubmissions)
+ .groupBy(vendorPQSubmissions.type, vendorPQSubmissions.status);
+
+ // 결과를 저장할 객체 초기화
+ const result = {
+ GENERAL: {
+ REQUESTED: 0,
+ IN_PROGRESS: 0,
+ SUBMITTED: 0,
+ APPROVED: 0,
+ REJECTED: 0,
+ total: 0
+ },
+ PROJECT: {
+ REQUESTED: 0,
+ IN_PROGRESS: 0,
+ SUBMITTED: 0,
+ APPROVED: 0,
+ REJECTED: 0,
+ total: 0
+ },
+ total: {
+ REQUESTED: 0,
+ IN_PROGRESS: 0,
+ SUBMITTED: 0,
+ APPROVED: 0,
+ REJECTED: 0,
+ total: 0
+ }
+ };
+
+ // 결과 매핑
+ pqStatuses.forEach((item) => {
+ if (item.type && item.status) {
+ const type = item.type as keyof typeof result;
+ const status = item.status as keyof typeof result.GENERAL;
+
+ if (type in result && status in result[type]) {
+ // 타입별 상태 카운트 업데이트
+ result[type][status] = item.count;
+
+ // 타입별 합계 업데이트
+ result[type].total += item.count;
+
+ // 전체 상태별 카운트 업데이트
+ result.total[status] += item.count;
+
+ // 전체 합계 업데이트
+ result.total.total += item.count;
+ }
+ }
+ });
+
+ return result;
+ } catch (error) {
+ console.error("Error fetching detailed PQ status counts:", error);
+ return {
+ GENERAL: {
+ REQUESTED: 0,
+ IN_PROGRESS: 0,
+ SUBMITTED: 0,
+ APPROVED: 0,
+ REJECTED: 0,
+ total: 0
+ },
+ PROJECT: {
+ REQUESTED: 0,
+ IN_PROGRESS: 0,
+ SUBMITTED: 0,
+ APPROVED: 0,
+ REJECTED: 0,
+ total: 0
+ },
+ total: {
+ REQUESTED: 0,
+ IN_PROGRESS: 0,
+ SUBMITTED: 0,
+ APPROVED: 0,
+ REJECTED: 0,
+ total: 0
+ }
+ };
+ }
+}
+
+/**
+ * SHI 코멘트 업데이트 액션
+ */
+export async function updateSHICommentAction({
+ answerId,
+ shiComment,
+}: {
+ answerId: number;
+ shiComment: string;
+}) {
+ try {
+ await db
+ .update(vendorPqCriteriaAnswers)
+ .set({
+ shiComment,
+ updatedAt: new Date(),
+ })
+ .where(eq(vendorPqCriteriaAnswers.id, answerId));
+
+ return { ok: true };
+ } catch (error) {
+ console.error("updateSHICommentAction error:", error);
+ return { ok: false, error: String(error) };
+ }
+}
+
+// PQ 승인 액션
+export async function approvePQAction({
+ pqSubmissionId,
+ vendorId,
+}: {
+ pqSubmissionId: number;
+ vendorId: number;
+}) {
+ unstable_noStore();
+
+ try {
+ const headersList = await headers();
+ const host = headersList.get('host') || 'localhost:3000';
+ const currentDate = new Date();
+
+ // 1. PQ 제출 정보 조회
+ const pqSubmission = await db
+ .select({
+ id: vendorPQSubmissions.id,
+ vendorId: vendorPQSubmissions.vendorId,
+ projectId: vendorPQSubmissions.projectId,
+ type: vendorPQSubmissions.type,
+ status: vendorPQSubmissions.status,
+ })
+ .from(vendorPQSubmissions)
+ .where(
+ and(
+ eq(vendorPQSubmissions.id, pqSubmissionId),
+ eq(vendorPQSubmissions.vendorId, vendorId)
+ )
+ )
+ .then(rows => rows[0]);
+
+ if (!pqSubmission) {
+ return { ok: false, error: "PQ submission not found" };
+ }
+
+ // 2. 상태 확인 (SUBMITTED 상태만 승인 가능)
+ if (pqSubmission.status !== "SUBMITTED") {
+ return {
+ ok: false,
+ error: `Cannot approve PQ in current status: ${pqSubmission.status}`
+ };
+ }
+
+ // 3. 벤더 정보 조회
+ const vendor = await db
+ .select({
+ id: vendors.id,
+ vendorName: vendors.vendorName,
+ email: vendors.email,
+ status: vendors.status,
+ })
+ .from(vendors)
+ .where(eq(vendors.id, vendorId))
+ .then(rows => rows[0]);
+
+ if (!vendor) {
+ return { ok: false, error: "Vendor not found" };
+ }
+
+ // 4. 프로젝트 정보 (프로젝트 PQ인 경우)
+ let projectName = '';
+ if (pqSubmission.projectId) {
+ const projectData = await db
+ .select({
+ id: projects.id,
+ name: projects.name,
+ })
+ .from(projects)
+ .where(eq(projects.id, pqSubmission.projectId))
+ .then(rows => rows[0]);
+
+ projectName = projectData?.name || 'Unknown Project';
+ }
+
+ // 5. PQ 상태 업데이트
+ await db
+ .update(vendorPQSubmissions)
+ .set({
+ status: "APPROVED",
+ approvedAt: currentDate,
+ updatedAt: currentDate,
+ })
+ .where(eq(vendorPQSubmissions.id, pqSubmissionId));
+
+ // 6. 일반 PQ인 경우 벤더 상태 업데이트 (선택사항)
+ if (pqSubmission.type === "GENERAL") {
+ await db
+ .update(vendors)
+ .set({
+ status: "PQ_APPROVED",
+ updatedAt: currentDate,
+ })
+ .where(eq(vendors.id, vendorId));
+ }
+
+ // 7. 벤더에게 이메일 알림 발송
+ if (vendor.email) {
+ try {
+ const emailSubject = pqSubmission.projectId
+ ? `[eVCP] Project PQ Approved for ${projectName}`
+ : "[eVCP] General PQ Approved";
+
+ const portalUrl = `${host}/partners/pq`;
+
+ await sendEmail({
+ to: vendor.email,
+ subject: emailSubject,
+ template: "pq-approved-vendor",
+ context: {
+ vendorName: vendor.vendorName,
+ projectId: pqSubmission.projectId,
+ projectName: projectName,
+ isProjectPQ: !!pqSubmission.projectId,
+ approvedDate: currentDate.toLocaleString(),
+ portalUrl,
+ }
+ });
+ } catch (emailError) {
+ console.error("Failed to send vendor notification:", emailError);
+ // 이메일 발송 실패가 전체 프로세스를 중단하지 않음
+ }
+ }
+
+ // 8. 캐시 무효화
+ revalidateTag("vendors");
+ revalidateTag("vendor-status-counts");
+ revalidateTag("pq-submissions");
+ revalidateTag(`vendor-pq-submissions-${vendorId}`);
+
+ if (pqSubmission.projectId) {
+ revalidateTag(`project-pq-submissions-${pqSubmission.projectId}`);
+ revalidateTag(`project-vendors-${pqSubmission.projectId}`);
+ }
+
+ return { ok: true };
+ } catch (error) {
+ console.error("PQ approve error:", error);
+ return { ok: false, error: getErrorMessage(error) };
+ }
+}
+
+// PQ 거부 액션
+export async function rejectPQAction({
+ pqSubmissionId,
+ vendorId,
+ rejectReason
+}: {
+ pqSubmissionId: number;
+ vendorId: number;
+ rejectReason: string;
+}) {
+ unstable_noStore();
+
+ try {
+ const headersList = await headers();
+ const host = headersList.get('host') || 'localhost:3000';
+ const currentDate = new Date();
+
+ // 1. PQ 제출 정보 조회
+ const pqSubmission = await db
+ .select({
+ id: vendorPQSubmissions.id,
+ vendorId: vendorPQSubmissions.vendorId,
+ projectId: vendorPQSubmissions.projectId,
+ type: vendorPQSubmissions.type,
+ status: vendorPQSubmissions.status,
+ })
+ .from(vendorPQSubmissions)
+ .where(
+ and(
+ eq(vendorPQSubmissions.id, pqSubmissionId),
+ eq(vendorPQSubmissions.vendorId, vendorId)
+ )
+ )
+ .then(rows => rows[0]);
+
+ if (!pqSubmission) {
+ return { ok: false, error: "PQ submission not found" };
+ }
+
+ // 2. 상태 확인 (SUBMITTED 상태만 거부 가능)
+ if (pqSubmission.status !== "SUBMITTED") {
+ return {
+ ok: false,
+ error: `Cannot reject PQ in current status: ${pqSubmission.status}`
+ };
+ }
+
+ // 3. 벤더 정보 조회
+ const vendor = await db
+ .select({
+ id: vendors.id,
+ vendorName: vendors.vendorName,
+ email: vendors.email,
+ status: vendors.status,
+ })
+ .from(vendors)
+ .where(eq(vendors.id, vendorId))
+ .then(rows => rows[0]);
+
+ if (!vendor) {
+ return { ok: false, error: "Vendor not found" };
+ }
+
+ // 4. 프로젝트 정보 (프로젝트 PQ인 경우)
+ let projectName = '';
+ if (pqSubmission.projectId) {
+ const projectData = await db
+ .select({
+ id: projects.id,
+ name: projects.name,
+ })
+ .from(projects)
+ .where(eq(projects.id, pqSubmission.projectId))
+ .then(rows => rows[0]);
+
+ projectName = projectData?.name || 'Unknown Project';
+ }
+
+ // 5. PQ 상태 업데이트
+ await db
+ .update(vendorPQSubmissions)
+ .set({
+ status: "REJECTED",
+ rejectedAt: currentDate,
+ rejectReason: rejectReason,
+ updatedAt: currentDate,
+ })
+ .where(eq(vendorPQSubmissions.id, pqSubmissionId));
+
+ // 6. 일반 PQ인 경우 벤더 상태 업데이트 (선택사항)
+ if (pqSubmission.type === "GENERAL") {
+ await db
+ .update(vendors)
+ .set({
+ status: "PQ_FAILED",
+ updatedAt: currentDate,
+ })
+ .where(eq(vendors.id, vendorId));
+ }
+
+ // 7. 벤더에게 이메일 알림 발송
+ if (vendor.email) {
+ try {
+ const emailSubject = pqSubmission.projectId
+ ? `[eVCP] Project PQ Rejected for ${projectName}`
+ : "[eVCP] General PQ Rejected";
+
+ const portalUrl = `${host}/partners/pq`;
+
+ await sendEmail({
+ to: vendor.email,
+ subject: emailSubject,
+ template: "pq-rejected-vendor",
+ context: {
+ vendorName: vendor.vendorName,
+ projectId: pqSubmission.projectId,
+ projectName: projectName,
+ isProjectPQ: !!pqSubmission.projectId,
+ rejectedDate: currentDate.toLocaleString(),
+ rejectReason: rejectReason,
+ portalUrl,
+ }
+ });
+ } catch (emailError) {
+ console.error("Failed to send vendor notification:", emailError);
+ // 이메일 발송 실패가 전체 프로세스를 중단하지 않음
+ }
+ }
+
+ // 8. 캐시 무효화
+ revalidateTag("vendors");
+ revalidateTag("vendor-status-counts");
+ revalidateTag("pq-submissions");
+ revalidateTag(`vendor-pq-submissions-${vendorId}`);
+
+ if (pqSubmission.projectId) {
+ revalidateTag(`project-pq-submissions-${pqSubmission.projectId}`);
+ revalidateTag(`project-vendors-${pqSubmission.projectId}`);
+ }
+
+ return { ok: true };
+ } catch (error) {
+ console.error("PQ reject error:", error);
+ return { ok: false, error: getErrorMessage(error) };
+ }
+}
+
+
+// 실사 의뢰 생성 서버 액션
+export async function requestInvestigationAction(
+ pqSubmissionIds: number[],
+ data: {
+ evaluationType: "PURCHASE_SELF_EVAL" | "DOCUMENT_EVAL" | "PRODUCT_INSPECTION" | "SITE_VISIT_EVAL",
+ qmManagerId: number,
+ forecastedAt: Date,
+ investigationAddress: string,
+ investigationNotes?: string
+ }
+) {
+ try {
+ // 세션에서 요청자 정보 가져오기
+ const session = await getServerSession(authOptions);
+ const requesterId = session?.user?.id ? Number(session.user.id) : null;
+
+ if (!requesterId) {
+ return { success: false, error: "인증된 사용자만 실사를 의뢰할 수 있습니다." };
+ }
+
+ const result = await db.transaction(async (tx) => {
+ // PQ 제출 정보 조회
+ const pqSubmissions = await tx
+ .select({
+ id: vendorPQSubmissions.id,
+ vendorId: vendorPQSubmissions.vendorId,
+ })
+ .from(vendorPQSubmissions)
+ .where(
+ and(
+ inArray(vendorPQSubmissions.id, pqSubmissionIds),
+ eq(vendorPQSubmissions.status, "APPROVED")
+ )
+ );
+
+ if (pqSubmissions.length === 0) {
+ throw new Error("승인된 PQ 제출 항목이 없습니다.");
+ }
+
+ const now = new Date();
+
+ // 각 PQ에 대한 실사 요청 생성 - 타입이 정확히 맞는지 확인
+ const investigations = pqSubmissions.map((pq) => {
+ return {
+ vendorId: pq.vendorId,
+ pqSubmissionId: pq.id,
+ investigationStatus: "PLANNED" as const, // enum 타입으로 명시적 지정
+ evaluationType: data.evaluationType,
+ qmManagerId: data.qmManagerId,
+ forecastedAt: data.forecastedAt,
+ investigationAddress: data.investigationAddress,
+ investigationNotes: data.investigationNotes || null,
+ requesterId: requesterId,
+ requestedAt: now,
+ createdAt: now,
+ updatedAt: now,
+ };
+ });
+
+ // 실사 요청 저장
+ const created = await tx
+ .insert(vendorInvestigations)
+ .values(investigations)
+ .returning();
+
+ return created;
+ });
+
+ // 캐시 무효화
+ revalidateTag("vendor-investigations");
+ revalidateTag("pq-submissions");
+
+ return {
+ success: true,
+ count: result.length,
+ data: result
+ };
+ } catch (err) {
+ console.error("실사 의뢰 중 오류 발생:", err);
+ return {
+ success: false,
+ error: err instanceof Error ? err.message : "알 수 없는 오류가 발생했습니다."
+ };
+ }
+}
+
+// 실사 의뢰 취소 서버 액션
+export async function cancelInvestigationAction(investigationIds: number[]) {
+ try {
+ const session = await getServerSession(authOptions)
+ const userId = session?.user?.id ? Number(session.user.id) : null
+
+ if (!userId) {
+ return { success: false, error: "인증된 사용자만 실사를 취소할 수 있습니다." }
+ }
+
+ const result = await db.transaction(async (tx) => {
+ // PLANNED 상태인 실사만 취소 가능
+ const updatedInvestigations = await tx
+ .update(vendorInvestigations)
+ .set({
+ investigationStatus: "CANCELED",
+ updatedAt: new Date(),
+ })
+ .where(
+ and(
+ inArray(vendorInvestigations.id, investigationIds),
+ eq(vendorInvestigations.investigationStatus, "PLANNED")
+ )
+ )
+ .returning()
+
+ return updatedInvestigations
+ })
+
+ // 캐시 무효화
+ revalidateTag("vendor-investigations")
+ revalidateTag("pq-submissions")
+
+ return {
+ success: true,
+ count: result.length,
+ data: result
+ }
+ } catch (err) {
+ console.error("실사 취소 중 오류 발생:", err)
+ return {
+ success: false,
+ error: err instanceof Error ? err.message : "알 수 없는 오류가 발생했습니다."
+ }
+ }
+}
+
+// 실사 결과 발송 서버 액션
+export async function sendInvestigationResultsAction(input: {
+ investigationIds: number[];
+ purchaseComment?: string;
+}) {
+ try {
+ const session = await getServerSession(authOptions)
+ const userId = session?.user?.id ? Number(session.user.id) : null
+
+ if (!userId) {
+ return { success: false, error: "인증된 사용자만 실사 결과를 발송할 수 있습니다." }
+ }
+
+ const result = await db.transaction(async (tx) => {
+ // 완료된 실사만 결과 발송 가능
+ const investigations = await tx
+ .select({
+ id: vendorInvestigations.id,
+ vendorId: vendorInvestigations.vendorId,
+ pqSubmissionId: vendorInvestigations.pqSubmissionId,
+ evaluationResult: vendorInvestigations.evaluationResult,
+ investigationNotes: vendorInvestigations.investigationNotes,
+ investigationAddress: vendorInvestigations.investigationAddress,
+ investigationMethod: vendorInvestigations.investigationMethod,
+ confirmedAt: vendorInvestigations.confirmedAt,
+ // Vendor 정보
+ vendorCode: vendors.vendorCode,
+ vendorName: vendors.vendorName,
+ vendorEmail: vendors.email,
+ // PQ 정보
+ pqNumber: vendorPQSubmissions.pqNumber,
+ pqItems: vendorPQSubmissions.pqItems,
+ projectCode: projects.code,
+ projectName: projects.name,
+ // 발신자 정보
+ senderName: users.name,
+ senderEmail: users.email,
+ })
+ .from(vendorInvestigations)
+ .leftJoin(vendors, eq(vendorInvestigations.vendorId, vendors.id))
+ .leftJoin(vendorPQSubmissions, eq(vendorInvestigations.pqSubmissionId, vendorPQSubmissions.id))
+ .leftJoin(projects, eq(vendorPQSubmissions.projectId, projects.id))
+ .leftJoin(users, eq(vendorInvestigations.requesterId, users.id))
+ .where(
+ and(
+ inArray(vendorInvestigations.id, input.investigationIds),
+ eq(vendorInvestigations.investigationStatus, "COMPLETED")
+ )
+ )
+
+ if (investigations.length === 0) {
+ throw new Error("발송할 수 있는 완료된 실사가 없습니다.")
+ }
+
+ // 각 실사에 대해 이메일 발송
+ const emailResults = await Promise.all(
+ investigations.map(async (investigation) => {
+ try {
+ // 이메일 컨텍스트 구성
+ const emailContext = {
+ // 기본 정보
+ pqNumber: investigation.pqNumber || "N/A",
+ vendorCode: investigation.vendorCode || "N/A",
+ vendorName: investigation.vendorName || "N/A",
+
+ // 실사 정보
+ auditItem: investigation.pqItems || investigation.projectName || "N/A",
+ auditFactoryAddress: investigation.investigationAddress || "N/A",
+ auditMethod: getInvestigationMethodLabel(investigation.investigationMethod || ""),
+ auditResult: investigation.evaluationResult === "APPROVED" ? "Pass(승인)" :
+ investigation.evaluationResult === "SUPPLEMENT" ? "Pass(조건부승인)" :
+ investigation.evaluationResult === "REJECTED" ? "Fail(미승인)" : "N/A",
+ additionalNotes: input.purchaseComment || investigation.investigationNotes || "",
+
+ // 발신자 정보
+ senderName: investigation.senderName || "삼성중공업",
+ senderEmail: investigation.senderEmail || "procurement@samsung.com",
+
+ // 이메일 제목
+ subject: `[SHI Audit] 실사 결과 안내 _ ${investigation.vendorName} _ PQ No. ${investigation.pqNumber}`,
+ }
+
+ // 이메일 발송
+ await sendEmail({
+ to: investigation.vendorEmail,
+ subject: emailContext.subject,
+ template: "audit-result-notice",
+ context: emailContext,
+ })
+
+ return { success: true, investigationId: investigation.id }
+ } catch (error) {
+ console.error(`실사 ID ${investigation.id} 이메일 발송 실패:`, error)
+ return { success: false, investigationId: investigation.id, error: error instanceof Error ? error.message : "알 수 없는 오류" }
+ }
+ })
+ )
+
+ // 성공한 실사들의 상태를 RESULT_SENT로 업데이트
+ const successfulInvestigationIds = emailResults
+ .filter(result => result.success)
+ .map(result => result.investigationId)
+
+ if (successfulInvestigationIds.length > 0) {
+ await tx
+ .update(vendorInvestigations)
+ .set({
+ investigationStatus: "RESULT_SENT",
+ purchaseComment: input.purchaseComment,
+ updatedAt: new Date(),
+ })
+ .where(inArray(vendorInvestigations.id, successfulInvestigationIds))
+ }
+
+ return {
+ totalCount: investigations.length,
+ successCount: emailResults.filter(r => r.success).length,
+ failedCount: emailResults.filter(r => !r.success).length,
+ emailResults,
+ }
+ })
+
+ // 캐시 무효화
+ revalidateTag("vendor-investigations")
+ revalidateTag("pq-submissions")
+
+ return {
+ success: true,
+ data: result,
+ message: `${result.successCount}개 실사 결과가 성공적으로 발송되었습니다.`
+ }
+ } catch (err) {
+ console.error("실사 결과 발송 중 오류 발생:", err)
+ return {
+ success: false,
+ error: err instanceof Error ? err.message : "알 수 없는 오류가 발생했습니다."
+ }
+ }
+}
+
+// 실사 방법 라벨 변환 함수
+function getInvestigationMethodLabel(method: string): string {
+ switch (method) {
+ case "PURCHASE_SELF_EVAL":
+ return "구매자체평가"
+ case "DOCUMENT_EVAL":
+ return "서류평가"
+ case "PRODUCT_INSPECTION":
+ return "제품검사평가"
+ case "SITE_VISIT_EVAL":
+ return "방문실사평가"
+ default:
+ return method
+ }
+}
+
+export async function getQMManagers() {
+ try {
+ // QM 부서 사용자만 필터링 (department 필드가 있다고 가정)
+ // 또는 QM 역할을 가진 사용자만 필터링 (role 필드가 있다고 가정)
+ const qmUsers = await db
+ .select({
+ id: users.id,
+ name: users.name,
+ email: users.email,
+ })
+ .from(users)
+ // .where(
+ // // 필요에 따라 조건 조정 (예: QM 부서 또는 특정 역할만)
+ // // eq(users.department, "QM") 또는
+ // // eq(users.role, "QM_MANAGER")
+ // // 테스트를 위해 모든 사용자 반환도 가능
+ // eq(users.active, true)
+ // )
+ .orderBy(users.name)
+
+ return {
+ data: qmUsers,
+ success: true
+ }
+ } catch (error) {
+ console.error("QM 담당자 목록 조회 오류:", error)
+ return {
+ data: [],
+ success: false,
+ error: error instanceof Error ? error.message : "QM 담당자 목록을 가져오는 중 오류가 발생했습니다."
+ }
+ }
+}
+
+export async function getFactoryLocationAnswer(vendorId: number, projectId: number | null = null) {
+ try {
+ // 1. "Location of Factory" 체크포인트를 가진 criteria 찾기
+ const criteria = await db
+ .select({
+ id: pqCriterias.id
+ })
+ .from(pqCriterias)
+ .where(ilike(pqCriterias.checkPoint, "%Location of Factory%"))
+ .limit(1);
+
+ if (!criteria.length) {
+ return { success: false, message: "Factory Location 질문을 찾을 수 없습니다." };
+ }
+
+ const criteriaId = criteria[0].id;
+
+ // 2. 해당 criteria에 대한 벤더의 응답 조회
+ const answerQuery = db
+ .select({
+ answer: vendorPqCriteriaAnswers.answer
+ })
+ .from(vendorPqCriteriaAnswers)
+ .where(
+ and(
+ eq(vendorPqCriteriaAnswers.vendorId, vendorId),
+ eq(vendorPqCriteriaAnswers.criteriaId, criteriaId)
+ )
+ );
+
+ // 프로젝트 ID가 있으면 추가 조건
+ if (projectId !== null) {
+ answerQuery.where(eq(vendorPqCriteriaAnswers.projectId, projectId));
+ } else {
+ answerQuery.where(eq(vendorPqCriteriaAnswers.projectId, null));
+ }
+
+ const answers = await answerQuery.limit(1);
+
+ if (!answers.length || !answers[0].answer) {
+ return { success: false, message: "공장 위치 정보를 찾을 수 없습니다." };
+ }
+
+ return {
+ success: true,
+ factoryLocation: answers[0].answer
+ };
+ } catch (error) {
+ console.error("Factory location 조회 오류:", error);
+ return { success: false, message: "오류가 발생했습니다." };
+ }
+}
+
+// -----------------------------------------------------------------------------
+// PQ LISTS (GENERAL / PROJECT / NON_INSPECTION) CRUD + 조회
+
+// LOAD CRITERIAS BY LIST
+export async function getPqCriteriasByListId(listId: number) {
+ const criterias = await db
+ .select()
+ .from(pqCriterias)
+ .where(eq(pqCriterias.pqListId, listId))
+ .orderBy(pqCriterias.groupName, pqCriterias.code);
+ return criterias;
+}
+
+// -----------------------------------------------------------------------------
+// PQ LISTS CRUD 액션 - 개선된 버전
+// -----------------------------------------------------------------------------
+
+
+export async function getPQLists(input: GetPqListsSchema) {
+ return unstable_cache(
+ async () => {
+ try {
+ const offset = (input.page - 1) * input.perPage;
+
+ let globalWhere;
+ if (input.search) {
+ const s = `%${input.search}%`;
+ globalWhere = or(
+ ilike(pqLists.name, s),
+ ilike(pqLists.type, s)
+ );
+ }
+
+ const advancedWhere = input.filters
+ ? filterColumns({ table: pqLists, filters: input.filters, joinOperator: input.joinOperator })
+ : undefined;
+
+ const finalWhere = and(
+ // eq(pqLists.isDeleted, false),
+ advancedWhere,
+ globalWhere
+ );
+
+ const orderBy = input.sort.length
+ ? input.sort.map((s) => (s.desc ? desc(pqLists.createdAt) : asc(pqLists.createdAt)))
+ : [desc(pqLists.createdAt)];
+
+ const { data, total } = await db.transaction(async (tx) => {
+ // 만료된 PQ 리스트들을 자동으로 비활성화
+ const now = new Date();
+ await tx
+ .update(pqLists)
+ .set({
+ isDeleted: true,
+ updatedAt: now
+ })
+ .where(
+ and(
+ eq(pqLists.isDeleted, false),
+ lt(pqLists.validTo, now),
+ isNotNull(pqLists.validTo)
+ )
+ );
+
+ const data = await tx
+ .select({
+ id: pqLists.id,
+ name: pqLists.name,
+ type: pqLists.type,
+ projectId: pqLists.projectId,
+ validTo: pqLists.validTo,
+ isDeleted: pqLists.isDeleted,
+ createdAt: pqLists.createdAt,
+ updatedAt: pqLists.updatedAt,
+ createdBy: users.name,
+ projectCode: projects.code,
+ projectName: projects.name,
+ updatedBy: users.name,
+ })
+ .from(pqLists)
+ .leftJoin(projects, eq(pqLists.projectId, projects.id))
+ .leftJoin(users, eq(pqLists.createdBy, users.id))
+ .where(finalWhere)
+ .orderBy(...orderBy)
+ .offset(offset)
+ .limit(input.perPage);
+
+ const countRes = await tx
+ .select({ count: count() })
+ .from(pqLists)
+ .where(finalWhere);
+
+ // 각 PQ 리스트의 항목 수 조회
+ const dataWithCriteriaCount = await Promise.all(
+ data.map(async (item) => {
+ const criteriaCount = await getPqListCriteriaCount(item.id);
+ return {
+ ...item,
+ criteriaCount
+ };
+ })
+ );
+
+
+ return { data: dataWithCriteriaCount, total: countRes[0]?.count ?? 0 };
+ });
+
+ const pageCount = Math.ceil(total / input.perPage);
+ return { data, pageCount };
+ } catch (err) {
+ console.error("Error in getPQLists:", err);
+ return { data: [], pageCount: 0 };
+ }
+ },
+ [JSON.stringify(input)],
+ { revalidate: 3600, tags: ["pq-lists"] }
+ )();
+}
+
+export async function togglePQListsAction(ids: number[], newIsDeleted: boolean) {
+ try {
+ if (ids.length === 0) {
+ return { success: false, message: "선택한 항목이 없습니다" };
+ }
+ const session = await getServerSession(authOptions);
+ const userId = session?.user?.id ? Number(session.user.id) : null;
+ const now = new Date();
+ const updated = await db
+ .update(pqLists)
+ .set({ isDeleted: newIsDeleted, updatedAt: now, updatedBy: userId })
+ .where(inArray(pqLists.id, ids))
+ .returning();
+ revalidateTag("pq-lists");
+ return {
+ success: true,
+ data: updated,
+ message: `${updated.length}개의 PQ 목록이 ${newIsDeleted ? "비활성화" : "활성화"}되었습니다`
+ };
+ } catch (error) {
+ console.error("Error toggling PQ lists:", error);
+ return {
+ success: false,
+ message: "PQ 목록 상태 변경에 실패했습니다"
+ };
+ }
+}
+
+export async function createPQListAction(input: CreatePqListInput) {
+ try {
+ const validated = createPqListSchema.parse(input);
+ const session = await getServerSession(authOptions);
+ const userId = session?.user?.id;
+ // const userName = session?.user?.name || "Unknown";
+
+ // General PQ인 경우 중복 체크
+ if (validated.type === "GENERAL") {
+ const existingGeneralPQ = await db
+ .select()
+ .from(pqLists)
+ .where(
+ and(
+ eq(pqLists.type, "GENERAL"),
+ eq(pqLists.isDeleted, false)
+ )
+ )
+ .limit(1);
+
+ if (existingGeneralPQ.length > 0) {
+ return {
+ success: false,
+ error: "General PQ 목록은 하나만 생성할 수 있습니다"
+ };
+ }
+ }
+
+ // 프로젝트 PQ인 경우 중복 체크
+ if (validated.type === "PROJECT" && validated.projectId) {
+ const existingPQ = await db
+ .select()
+ .from(pqLists)
+ .where(
+ and(
+ eq(pqLists.projectId, validated.projectId),
+ eq(pqLists.type, "PROJECT"),
+ eq(pqLists.isDeleted, false)
+ )
+ )
+ .limit(1);
+
+ if (existingPQ.length > 0) {
+ return {
+ success: false,
+ error: "해당 프로젝트에 대한 PQ가 이미 존재합니다"
+ };
+ }
+ }
+ return await db.transaction(async (tx) => {
+ const now = new Date();
+ const [newPqList] = await tx
+ .insert(pqLists)
+ .values({
+ ...validated,
+ isDeleted: false,
+ createdAt: now,
+ updatedAt: now,
+ createdBy: userId,
+ updatedBy: userId,
+ })
+ .returning();
+
+ // 프로젝트 PQ인 경우 General PQ 항목들을 자동으로 복사
+ let copiedCriteriaCount = 0;
+ if (validated.type === "PROJECT") {
+ // General PQ 목록 찾기
+ const generalPqList = await tx
+ .select()
+ .from(pqLists)
+ .where(
+ and(
+ eq(pqLists.type, "GENERAL"),
+ eq(pqLists.isDeleted, false)
+ )
+ )
+ .limit(1)
+ .then(rows => rows[0]);
+
+ if (generalPqList) {
+ // General PQ의 항목들 조회
+ const generalCriterias = await tx
+ .select()
+ .from(pqCriterias)
+ .where(eq(pqCriterias.pqListId, generalPqList.id));
+
+ if (generalCriterias.length > 0) {
+ // 새로운 프로젝트 PQ에 항목들 복사
+ const newCriterias = generalCriterias.map(criteria => ({
+ code: criteria.code,
+ checkPoint: criteria.checkPoint,
+ description: criteria.description,
+ remarks: criteria.remarks,
+ groupName: criteria.groupName,
+ subGroupName: criteria.subGroupName,
+ pqListId: newPqList.id,
+ inputFormat: criteria.inputFormat,
+ createdAt: now,
+ updatedAt: now,
+ }));
+
+ await tx.insert(pqCriterias).values(newCriterias);
+ copiedCriteriaCount = newCriterias.length;
+ }
+ }
+ }
+
+ revalidateTag("pq-lists");
+ revalidateTag("pq-criterias");
+ return {
+ success: true,
+ data: newPqList,
+ copiedCriteriaCount
+ };
+ });
+ } catch (error) {
+ console.error("Error creating PQ list:", error);
+
+ if (error instanceof z.ZodError) {
+ return {
+ success: false,
+ error: "유효성 검사 실패",
+ details: error.errors
+ };
+ }
+
+ return {
+ success: false,
+ error: "PQ 목록 생성에 실패했습니다"
+ };
+ }
+}
+export async function deletePQListsAction(ids: number[]) {
+ try {
+ if (ids.length === 0) {
+ return {
+ success: false,
+ message: "삭제할 항목을 선택해주세요"
+ };
+ }
+ console.log("ids", ids)
+ console.log("pqLists", pqLists)
+ const now = new Date();
+ const updated = await db
+ .update(pqLists)
+ .set({ isDeleted: true, updatedAt: now })
+ .where(inArray(pqLists.id, ids))
+ .returning();
+
+ revalidateTag("pq-lists");
+ return {
+ success: true,
+ data: updated,
+ message: `${updated.length}개의 PQ 목록이 비활성화되었습니다`
+ };
+ } catch (error) {
+ console.error("Error deleting PQ lists:", error);
+ return {
+ success: false,
+ message: "PQ 목록 삭제에 실패했습니다"
+ };
+ }
+}
+
+export async function getPqListById(id: number) {
+ try {
+ const pqList = await db
+ .select()
+ .from(pqLists)
+ .where(and(
+ eq(pqLists.id, id),
+ eq(pqLists.isDeleted, false)
+ ))
+ .limit(1)
+ .then(rows => rows[0]);
+
+ return pqList || null;
+ } catch (error) {
+ console.error("Error fetching PQ list by ID:", error);
+ return null;
+ }
+}
+
+export async function getPqListCriteriaCount(listId: number) {
+ try {
+ const result = await db
+ .select({ count: count() })
+ .from(pqCriterias)
+ .where(eq(pqCriterias.pqListId, listId));
+
+ return result[0]?.count || 0;
+ } catch (error) {
+ console.error("Error getting PQ list criteria count:", error);
+ return 0;
+ }
+}
+
+
+
+export async function copyPQListAction(input: CopyPqListInput) {
+ try {
+ const validated = copyPqListSchema.parse(input);
+ const session = await getServerSession(authOptions);
+ const userId = session?.user?.id;
+ return await db.transaction(async (tx) => {
+ // 1. 원본 PQ 목록 조회
+ const sourcePqList = await tx
+ .select()
+ .from(pqLists)
+ .where(eq(pqLists.id, validated.sourcePqListId))
+ .limit(1)
+ .then(rows => rows[0]);
+
+ if (!sourcePqList) {
+ return {
+ success: false,
+ error: "복사할 PQ 목록을 찾을 수 없습니다"
+ };
+ }
+
+ // 2. 대상 프로젝트에 이미 PQ가 존재하는지 확인
+ const existingProjectPQ = await tx
+ .select()
+ .from(pqLists)
+ .where(
+ and(
+ eq(pqLists.projectId, validated.targetProjectId),
+ eq(pqLists.type, "PROJECT"),
+ eq(pqLists.isDeleted, false)
+ )
+ )
+ .limit(1);
+
+ if (existingProjectPQ.length > 0) {
+ return {
+ success: false,
+ error: "해당 프로젝트에 대한 PQ가 이미 존재합니다"
+ };
+ }
+
+ // 3. 새 PQ 목록 생성
+ const now = new Date();
+ const newName = validated.newName || `${sourcePqList.name} (복사본)`;
+
+ const [newPqList] = await tx
+ .insert(pqLists)
+ .values({
+ name: newName || sourcePqList.name,
+ type: sourcePqList.type,
+ projectId: validated.targetProjectId,
+ isDeleted: false,
+ createdAt: now,
+ updatedAt: now,
+ createdBy: userId,
+ updatedBy: userId,
+ validTo: validated.validTo,
+ })
+ .returning();
+
+ // 4. 원본 PQ 항목들 조회 및 복사
+ const sourceCriterias = await tx
+ .select()
+ .from(pqCriterias)
+ .where(eq(pqCriterias.pqListId, validated.sourcePqListId));
+
+ if (sourceCriterias.length > 0) {
+ const newCriterias = sourceCriterias.map(criteria => ({
+ code: criteria.code,
+ checkPoint: criteria.checkPoint,
+ description: criteria.description,
+ remarks: criteria.remarks,
+ groupName: criteria.groupName,
+ subGroupName: criteria.subGroupName,
+ pqListId: newPqList.id,
+ inputFormat: criteria.inputFormat,
+ createdAt: now,
+ updatedAt: now,
+ createdBy: userId,
+ updatedBy: userId,
+ }));
+
+ await tx.insert(pqCriterias).values(newCriterias);
+ }
+
+ revalidateTag("pq-lists");
+ return {
+ success: true,
+ data: newPqList,
+ copiedCriteriaCount: sourceCriterias.length
+ };
+ });
+ } catch (error) {
+ console.error("Error copying PQ list:", error);
+
+ if (error instanceof z.ZodError) {
+ return {
+ success: false,
+ error: "유효성 검사 실패",
+ details: error.errors
+ };
+ }
+
+ return {
+ success: false,
+ error: error instanceof Error ? error.message : "PQ 목록 복사에 실패했습니다"
+ };
+ }
+}
+export type Project = {
+ id: number;
+ projectCode: string;
+ projectName: string;
+ pjtType: string;
+}
+// -----------------------------------------------------------------------------
+export async function getProjects() {
+ try {
+ const projectList = await db.transaction(async (tx) => {
+ const results = await tx
+ .select({
+ id: projects.id,
+ code: projects.code,
+ name: projects.name,
+ type: projects.type,
+ createdAt: projects.createdAt,
+ updatedAt: projects.updatedAt,
+ })
+ .from(projects)
+ .orderBy(projects.code);
+
+ return results;
+ });
+
+ return projectList;
+ } catch (error) {
+ console.error("프로젝트 목록 가져오기 실패:", error);
+ return [];
+ }
+}
+
+// PQ 리스트에 등재된 프로젝트만 가져오는 함수
+export async function getProjectsWithPQList() {
+ try {
+ const projectList = await db.transaction(async (tx) => {
+ const results = await tx
+ .select({
+ id: projects.id,
+ projectCode: projects.code,
+ projectName: projects.name,
+ pjtType: projects.type,
+ type: projects.type,
+ createdAt: projects.createdAt,
+ updatedAt: projects.updatedAt,
+ })
+ .from(projects)
+ .innerJoin(pqLists, eq(projects.id, pqLists.projectId))
+ .where(
+ and(
+ eq(pqLists.type, "PROJECT"),
+ eq(pqLists.isDeleted, false)
+ )
+ )
+ .orderBy(projects.code);
+
+ return results;
+ });
+
+ return projectList;
+ } catch (error) {
+ console.error("PQ 리스트 등재 프로젝트 목록 가져오기 실패:", error);
+ return [];
+ }
+}
+// -----------------------------------------------------------------------------
+// PQ Criteria CRUD 액션 - 개선된 버전
+// -----------------------------------------------------------------------------
+
+// PQ 항목 생성 (특정 PQ 목록에 속함)
+export async function createPqCriteria(
+ pqListId: number,
+ input: {
+ code: string;
+ checkPoint: string;
+ groupName: string;
+ subGroupName?: string;
+ description?: string;
+ remarks?: string;
+ inputFormat?: string;
+ }
+) {
+ try {
+ const now = new Date();
+ const [newCriteria] = await db
+ .insert(pqCriterias)
+ .values({
+ code: input.code,
+ checkPoint: input.checkPoint,
+ description: input.description || null,
+ remarks: input.remarks || null,
+ groupName: input.groupName,
+ subGroupName: input.subGroupName || null,
+ pqListId: pqListId,
+ inputFormat: input.inputFormat || "TEXT",
+ createdAt: now,
+ updatedAt: now,
+ })
+ .returning();
+
+ revalidateTag("pq-criterias");
+ return {
+ success: true,
+ data: newCriteria,
+ message: "PQ 항목이 성공적으로 생성되었습니다"
+ };
+ } catch (error) {
+ console.error("Error creating PQ criteria:", error);
+ return {
+ success: false,
+ message: "PQ 항목 생성에 실패했습니다"
+ };
+ }
+}
+
+// PQ 항목 수정
+export async function updatePqCriteria(
+ id: number,
+ input: {
+ code: string;
+ checkPoint: string;
+ groupName: string;
+ subGroupName?: string;
+ description?: string;
+ remarks?: string;
+ inputFormat?: string;
+ }
+) {
+ try {
+ const now = new Date();
+ const [updatedCriteria] = await db
+ .update(pqCriterias)
+ .set({
+ code: input.code,
+ checkPoint: input.checkPoint,
+ description: input.description || null,
+ remarks: input.remarks || null,
+ groupName: input.groupName,
+ subGroupName: input.subGroupName || null,
+ inputFormat: input.inputFormat || "TEXT",
+ updatedAt: now,
+ })
+ .where(eq(pqCriterias.id, id))
+ .returning();
+
+ if (!updatedCriteria) {
+ return {
+ success: false,
+ message: "수정할 PQ 항목을 찾을 수 없습니다"
+ };
+ }
+
+ revalidateTag("pq-criterias");
+ return {
+ success: true,
+ data: updatedCriteria,
+ message: "PQ 항목이 성공적으로 수정되었습니다"
+ };
+ } catch (error) {
+ console.error("Error updating PQ criteria:", error);
+ return {
+ success: false,
+ message: "PQ 항목 수정에 실패했습니다"
+ };
+ }
+}
+
+// PQ 항목 삭제
+export async function deletePqCriterias(ids: number[]) {
+ try {
+ if (ids.length === 0) {
+ return {
+ success: false,
+ message: "삭제할 항목을 선택해주세요"
+ };
+ }
+
+ const deletedCriterias = await db
+ .delete(pqCriterias)
+ .where(inArray(pqCriterias.id, ids))
+ .returning();
+
+ revalidateTag("pq-criterias");
+ return {
+ success: true,
+ data: deletedCriterias,
+ message: `${deletedCriterias.length}개의 PQ 항목이 삭제되었습니다`
+ };
+ } catch (error) {
+ console.error("Error deleting PQ criterias:", error);
+ return {
+ success: false,
+ message: "PQ 항목 삭제에 실패했습니다"
+ };
+ }
+}
+
+/**
+ * PQ 제출 삭제 함수 (REQUESTED 상태일 때만 삭제 가능)
+ */
+export async function deletePQSubmissionAction(pqSubmissionId: number) {
+ try {
+ // PQ 제출 정보 조회
+ const submission = await db
+ .select()
+ .from(vendorPQSubmissions)
+ .where(eq(vendorPQSubmissions.id, pqSubmissionId))
+ .limit(1);
+
+ if (submission.length === 0) {
+ return { success: false, error: "PQ 제출을 찾을 수 없습니다." };
+ }
+
+ const pqSubmission = submission[0];
+
+ // REQUESTED 상태가 아니면 삭제 불가
+ if (pqSubmission.status !== "REQUESTED") {
+ return { success: false, error: "요청됨 상태가 아닌 PQ는 삭제할 수 없습니다." };
+ }
+
+ // 트랜잭션으로 관련 데이터 모두 삭제
+ await db.transaction(async (tx) => {
+ // 1. PQ 답변 삭제 (vendorId와 projectId로 식별)
+ await tx
+ .delete(vendorPqCriteriaAnswers)
+ .where(
+ and(
+ eq(vendorPqCriteriaAnswers.vendorId, pqSubmission.vendorId),
+ pqSubmission.projectId
+ ? eq(vendorPqCriteriaAnswers.projectId, pqSubmission.projectId)
+ : isNull(vendorPqCriteriaAnswers.projectId)
+ )
+ );
+
+ // 2. 첨부파일 삭제 (vendorCriteriaAnswerId로 연결)
+ const answerIds = await tx
+ .select({ id: vendorPqCriteriaAnswers.id })
+ .from(vendorPqCriteriaAnswers)
+ .where(
+ and(
+ eq(vendorPqCriteriaAnswers.vendorId, pqSubmission.vendorId),
+ pqSubmission.projectId
+ ? eq(vendorPqCriteriaAnswers.projectId, pqSubmission.projectId)
+ : isNull(vendorPqCriteriaAnswers.projectId)
+ )
+ );
+
+ if (answerIds.length > 0) {
+ await tx
+ .delete(vendorCriteriaAttachments)
+ .where(inArray(vendorCriteriaAttachments.vendorCriteriaAnswerId, answerIds.map(a => a.id)));
+ }
+
+ // 3. PQ 제출 삭제
+ await tx
+ .delete(vendorPQSubmissions)
+ .where(eq(vendorPQSubmissions.id, pqSubmissionId));
+ });
+
+ return { success: true };
+ } catch (error) {
+ console.error("deletePQSubmissionAction error:", error);
+ return { success: false, error: String(error) };
+ }
+}
+
+// PQ 목록별 항목 조회 (특정 pqListId에 속한 PQ 항목들)
+export async function getPQsByListId(pqListId: number, input: GetPQSchema) {
+ return unstable_cache(
+ async () => {
+ try {
+ const offset = (input.page - 1) * input.perPage;
+
+ // 검색 조건
+ let globalWhere;
+ if (input.search) {
+ const s = `%${input.search}%`;
+ globalWhere = or(
+ ilike(pqCriterias.code, s),
+ ilike(pqCriterias.groupName, s),
+ ilike(pqCriterias.subGroupName, s),
+ ilike(pqCriterias.remarks, s),
+ ilike(pqCriterias.checkPoint, s),
+ ilike(pqCriterias.description, s)
+ );
+ }
+
+ // 고급 필터
+ const advancedWhere = input.filters
+ ? filterColumns({ table: pqCriterias, filters: input.filters, joinOperator: input.joinOperator })
+ : undefined;
+
+ // 최종 WHERE 조건 (pqListId 조건 추가)
+ const finalWhere = and(
+ eq(pqCriterias.pqListId, pqListId), // 특정 PQ 목록에 속한 항목들만
+ advancedWhere,
+ globalWhere
+ );
+
+ // 정렬
+ const orderBy = input.sort.length
+ ? input.sort.map((s) => (s.desc ? desc(pqCriterias[s.id]) : asc(pqCriterias[s.id])))
+ : [asc(pqCriterias.createdAt)];
+
+ const { data, total } = await db.transaction(async (tx) => {
+ // 데이터 조회
+ const data = await tx
+ .select({
+ id: pqCriterias.id,
+ code: pqCriterias.code,
+ checkPoint: pqCriterias.checkPoint,
+ description: pqCriterias.description,
+ remarks: pqCriterias.remarks,
+ groupName: pqCriterias.groupName,
+ subGroupName: pqCriterias.subGroupName,
+ pqListId: pqCriterias.pqListId,
+ inputFormat: pqCriterias.inputFormat,
+
+ createdAt: pqCriterias.createdAt,
+ updatedAt: pqCriterias.updatedAt,
+ })
+ .from(pqCriterias)
+ .where(finalWhere)
+ .orderBy(...orderBy)
+ .offset(offset)
+ .limit(input.perPage);
+
+ // 카운트 조회
+ const countRes = await tx
+ .select({ count: count() })
+ .from(pqCriterias)
+ .where(finalWhere);
+
+ const total = countRes[0]?.count ?? 0;
+
+ return { data, total };
+ });
+
+ const pageCount = Math.ceil(total / input.perPage);
+ return { data, pageCount };
+ } catch (err) {
+ console.error("Error in getPQsByListId:", err);
+ return { data: [], pageCount: 0 };
+ }
+ },
+ [JSON.stringify(input), pqListId.toString()],
+ { revalidate: 3600, tags: ["pq-criterias"] }
+ )();
+}
+
+// 실사 정보 업데이트 액션 (구매자체평가용)
+export async function updateInvestigationDetailsAction(input: {
+ investigationId: number;
+ confirmedAt?: Date;
+ evaluationResult?: "APPROVED" | "SUPPLEMENT" | "REJECTED";
+ investigationNotes?: string;
+}) {
+ try {
+ const updateData: any = {
+ updatedAt: new Date(),
+ };
+
+ if (input.confirmedAt !== undefined) {
+ updateData.confirmedAt = input.confirmedAt;
+ }
+
+ if (input.evaluationResult !== undefined) {
+ updateData.evaluationResult = input.evaluationResult;
+ }
+
+ if (input.investigationNotes !== undefined) {
+ updateData.investigationNotes = input.investigationNotes;
+ }
+
+ await db
+ .update(vendorInvestigations)
+ .set(updateData)
+ .where(eq(vendorInvestigations.id, input.investigationId));
+
+ revalidateTag("pq-submissions");
+ revalidatePath("/evcp/pq_new");
+
+ return {
+ success: true,
+ message: "실사 정보가 성공적으로 업데이트되었습니다."
+ };
+
+ } catch (error) {
+ console.error("실사 정보 업데이트 오류:", error);
+ return {
+ success: false,
+ error: "실사 정보 업데이트 중 오류가 발생했습니다."
+ };
+ }
+}
+
+export async function autoDeactivateExpiredPQLists() {
+ try {
+ const now = new Date();
+
+ // 유효기간이 지난 PQ 리스트들을 비활성화
+ const expiredLists = await db
+ .update(pqLists)
+ .set({
+ isDeleted: true,
+ updatedAt: now
+ })
+ .where(
+ and(
+ eq(pqLists.isDeleted, false),
+ lt(pqLists.validTo, now),
+ isNotNull(pqLists.validTo)
+ )
+ )
+ .returning();
+
+ console.log(`[PQ Auto Deactivation] ${expiredLists.length}개의 만료된 PQ 리스트가 비활성화되었습니다.`);
+
+ if (expiredLists.length > 0) {
+ revalidateTag("pq-lists");
+ }
+
+ return {
+ success: true,
+ deactivatedCount: expiredLists.length,
+ message: `${expiredLists.length}개의 만료된 PQ 리스트가 비활성화되었습니다.`
+ };
+ } catch (error) {
+ console.error("Error auto-deactivating expired PQ lists:", error);
+ return {
+ success: false,
+ message: "만료된 PQ 리스트 자동 비활성화에 실패했습니다."
+ };
+ }
+}
+
+// SHI 참석자 총 인원수 계산 함수
+
+export async function getTotalShiAttendees(shiAttendees: Record<string, unknown> | null): Promise<number> {
+ if (!shiAttendees) return 0
+
+ let total = 0
+ Object.entries(shiAttendees).forEach(([key, value]) => {
+ if (value && typeof value === 'object' && 'checked' in value && 'count' in value) {
+ const attendee = value as { checked: boolean; count: number }
+ if (attendee.checked) {
+ total += attendee.count
+ }
+ }
+ })
+ return total
}
\ No newline at end of file |