diff options
Diffstat (limited to 'lib/pq/service.ts')
| -rw-r--r-- | lib/pq/service.ts | 1327 |
1 files changed, 1252 insertions, 75 deletions
diff --git a/lib/pq/service.ts b/lib/pq/service.ts index 6159a307..18d1a5d3 100644 --- a/lib/pq/service.ts +++ b/lib/pq/service.ts @@ -1,14 +1,14 @@ "use server" import db from "@/db/db" -import { GetPQSchema } from "./validations" +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, vendorPqCriteriaAnswers, vendorPqReviewLogs, vendorProjectPQs } from "@/db/schema/pq" +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"; @@ -18,8 +18,12 @@ import { randomUUID } from 'crypto'; import { writeFile, mkdir } from 'fs/promises'; import { GetVendorsSchema } from "../vendors/validations"; import { countVendors, selectVendors } from "../vendors/repository"; -import { projects } from "@/db/schema"; +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 목록 조회 @@ -374,19 +378,19 @@ export interface ProjectPQ { export async function getPQProjectsByVendorId(vendorId: number): Promise<ProjectPQ[]> { const result = await db .select({ - id: vendorProjectPQs.id, - projectId: vendorProjectPQs.projectId, - status: vendorProjectPQs.status, - submittedAt: vendorProjectPQs.submittedAt, + id: vendorPQSubmissions.id, + projectId: vendorPQSubmissions.projectId, + status: vendorPQSubmissions.status, + submittedAt: vendorPQSubmissions.submittedAt, projectCode: projects.code, projectName: projects.name, }) - .from(vendorProjectPQs) + .from(vendorPQSubmissions) .innerJoin( projects, - eq(vendorProjectPQs.projectId, projects.id) + eq(vendorPQSubmissions.projectId, projects.id) ) - .where(eq(vendorProjectPQs.vendorId, vendorId)) + .where(eq(vendorPQSubmissions.vendorId, vendorId)) .orderBy(projects.code); return result; @@ -659,10 +663,12 @@ export async function savePQAnswersAction(input: SavePQInput) { */ export async function submitPQAction({ vendorId, - projectId + projectId, + pqSubmissionId }: { vendorId: number; projectId?: number; + pqSubmissionId?: number; // 특정 PQ 제출 ID가 있는 경우 사용 }) { unstable_noStore(); @@ -671,21 +677,21 @@ export async function submitPQAction({ const host = headersList.get('host') || 'localhost:3000'; // 1. 모든 PQ 항목에 대한 응답이 있는지 검증 - const queryConditions = [ + const answerQueryConditions = [ eq(vendorPqCriteriaAnswers.vendorId, vendorId) ]; // Add projectId condition when it exists if (projectId !== undefined) { - queryConditions.push(eq(vendorPqCriteriaAnswers.projectId, projectId)); + answerQueryConditions.push(eq(vendorPqCriteriaAnswers.projectId, projectId)); } else { - queryConditions.push(isNull(vendorPqCriteriaAnswers.projectId)); + answerQueryConditions.push(isNull(vendorPqCriteriaAnswers.projectId)); } const pqCriteriaCount = await db .select({ count: count() }) .from(vendorPqCriteriaAnswers) - .where(and(...queryConditions)); + .where(and(...answerQueryConditions)); const totalPqCriteriaCount = pqCriteriaCount[0]?.count || 0; @@ -724,86 +730,116 @@ export async function submitPQAction({ projectName = projectData?.projectName || 'Unknown Project'; } - // 3. 상태 업데이트 + // 3. 현재 PQ 제출 상태 확인 및 업데이트 const currentDate = new Date(); + let existingSubmission; - if (projectId) { - // 프로젝트별 PQ인 경우 vendorProjectPQs 테이블 업데이트 - const existingProjectPQ = await db - .select({ id: vendorProjectPQs.id, status: vendorProjectPQs.status }) - .from(vendorProjectPQs) + // 특정 PQ Submission ID가 있는 경우 + if (pqSubmissionId) { + existingSubmission = await db + .select({ + id: vendorPQSubmissions.id, + status: vendorPQSubmissions.status, + type: vendorPQSubmissions.type + }) + .from(vendorPQSubmissions) .where( and( - eq(vendorProjectPQs.vendorId, vendorId), - eq(vendorProjectPQs.projectId, projectId) + eq(vendorPQSubmissions.id, pqSubmissionId), + eq(vendorPQSubmissions.vendorId, vendorId) ) ) .then(rows => rows[0]); - if (existingProjectPQ) { - // 프로젝트 PQ 상태가 제출 가능한 상태인지 확인 - const allowedStatuses = ["REQUESTED", "IN_PROGRESS", "REJECTED"]; - - if (!allowedStatuses.includes(existingProjectPQ.status)) { - return { - ok: false, - error: `Cannot submit Project PQ in current status: ${existingProjectPQ.status}` - }; - } - - // Update existing project PQ status - await db - .update(vendorProjectPQs) - .set({ - status: "SUBMITTED", - submittedAt: currentDate, - updatedAt: currentDate, - }) - .where(eq(vendorProjectPQs.id, existingProjectPQ.id)); + 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 { - // Project PQ entry doesn't exist, create one - await db - .insert(vendorProjectPQs) - .values({ - vendorId, - projectId, - status: "SUBMITTED", - submittedAt: currentDate, - createdAt: currentDate, - updatedAt: currentDate, - }); + submissionQueryConditions.push(isNull(vendorPQSubmissions.projectId)); } - } else { - // 일반 PQ인 경우 협력업체 상태 검증 및 업데이트 - const allowedStatuses = ["IN_PQ", "PENDING_REVIEW", "IN_REVIEW", "REJECTED", "PQ_FAILED"]; - if (!allowedStatuses.includes(vendor.status)) { + 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: ${vendor.status}` + error: `Cannot submit PQ in current status: ${existingSubmission.status}` }; } - // Update vendor status + // 기존 제출 상태 업데이트 await db - .update(vendors) + .update(vendorPQSubmissions) .set({ - status: "PQ_SUBMITTED", + status: "SUBMITTED", + submittedAt: currentDate, updatedAt: currentDate, }) - .where(eq(vendors.id, vendorId)); + .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"]; - // 4. 관리자에게 이메일 알림 발송 + 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] PQ Submitted: ${vendor.vendorName}`; + : `[eVCP] General PQ Submitted: ${vendor.vendorName}`; - const adminUrl = projectId - ? `http://${host}/evcp/pq/${vendorId}?projectId=${projectId}` - : `http://${host}/evcp/pq/${vendorId}`; + const adminUrl = `http://${host}/evcp/pq/${vendorId}/${existingSubmission?.id || ''}`; await sendEmail({ to: process.env.ADMIN_EMAIL, @@ -821,18 +857,17 @@ export async function submitPQAction({ }); } catch (emailError) { console.error("Failed to send admin notification:", emailError); - // 이메일 실패는 전체 프로세스를 중단하지 않음 } } - // 5. 벤더에게 확인 이메일 발송 + // 6. 벤더에게 확인 이메일 발송 if (vendor.email) { try { const emailSubject = projectId ? `[eVCP] Project PQ Submission Confirmation for ${projectName}` - : "[eVCP] PQ Submission Confirmation"; + : "[eVCP] General PQ Submission Confirmation"; - const portalUrl = `${host}/dashboard`; + const portalUrl = `${host}/partners/pq`; await sendEmail({ to: vendor.email, @@ -849,16 +884,16 @@ export async function submitPQAction({ }); } catch (emailError) { console.error("Failed to send vendor confirmation:", emailError); - // 이메일 실패는 전체 프로세스를 중단하지 않음 } } - // 6. 캐시 무효화 + // 7. 캐시 무효화 revalidateTag("vendors"); revalidateTag("vendor-status-counts"); + revalidateTag(`vendor-pq-submissions-${vendorId}`); if (projectId) { - revalidateTag(`vendor-project-pqs-${vendorId}`); + revalidateTag(`project-pq-submissions-${projectId}`); revalidateTag(`project-vendors-${projectId}`); revalidateTag(`project-pq-${projectId}`); } @@ -1702,4 +1737,1146 @@ export async function loadProjectPQAction(vendorId: number, projectId?: number): 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: "오류가 발생했습니다." }; + } }
\ No newline at end of file |