From a3525f8bdfcf849cc1716fab81cb8facadbe9a8e Mon Sep 17 00:00:00 2001 From: dujinkim Date: Mon, 27 Oct 2025 10:03:06 +0000 Subject: (최겸) 구매 협력업체 관리(PQ/실사관리, 정기평가 협력업체 제출 상세 dialog 개발, MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit --- lib/pq/service.ts | 583 ++++++++++++++++++++++++++++++++---------------------- 1 file changed, 347 insertions(+), 236 deletions(-) (limited to 'lib/pq/service.ts') diff --git a/lib/pq/service.ts b/lib/pq/service.ts index 7296b836..54459a6c 100644 --- a/lib/pq/service.ts +++ b/lib/pq/service.ts @@ -5,7 +5,7 @@ import { CopyPqListInput, CreatePqListInput, UpdatePqValidToInput, copyPqListSch 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, ne, count,isNull,SQL, sql, lt, isNotNull} from "drizzle-orm"; +import { asc, desc, ilike, inArray, and, gte, lte, not, or, eq, ne, count,isNull,SQL, sql, lt, gt, isNotNull} from "drizzle-orm"; import { z } from "zod" import { revalidateTag, unstable_noStore, revalidatePath} from "next/cache"; import { format } from "date-fns" @@ -91,13 +91,25 @@ export async function getPQProjectsByVendorId(vendorId: number): Promise { try { // 파라미터 유효성 검증 if (isNaN(vendorId)) { throw new Error("Invalid vendorId parameter"); } + + // 타입 결정 로직 + let finalPqType: "GENERAL" | "PROJECT" | "NON_INSPECTION"; + if (pqType) { + finalPqType = pqType; + } else if (projectId) { + finalPqType = "PROJECT"; + } else { + finalPqType = "GENERAL"; + } + // 기본 쿼리 구성 const selectObj = { criteriaId: pqCriterias.id, @@ -127,65 +139,45 @@ export async function getPQDataByVendorId( 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); + // 타입별 쿼리 조건 구성 + const pqListConditions = [ + eq(pqCriterias.pqListId, pqLists.id), + eq(pqLists.type, finalPqType), + eq(pqLists.isDeleted, false) + ]; + + const answerConditions = [ + eq(pqCriterias.id, vendorPqCriteriaAnswers.criteriaId), + eq(vendorPqCriteriaAnswers.vendorId, vendorId) + ]; + + // 프로젝트별 조건 추가 + if (finalPqType === "PROJECT" && projectId) { + pqListConditions.push(eq(pqLists.projectId, projectId)); + answerConditions.push(eq(vendorPqCriteriaAnswers.projectId, projectId)); + } else if (finalPqType === "GENERAL" || finalPqType === "NON_INSPECTION") { + pqListConditions.push(isNull(pqLists.projectId)); + answerConditions.push(isNull(vendorPqCriteriaAnswers.projectId)); } + + const rows = await db + .select(selectObj) + .from(pqCriterias) + .innerJoin( + pqLists, + and(...pqListConditions) + ) + .leftJoin( + vendorPqCriteriaAnswers, + and(...answerConditions) + ) + .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 []; @@ -790,199 +782,199 @@ export async function uploadSHIMultipleFilesAction(files: File[], userId?: strin } } -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 | 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(); +// 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 | 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(); - // 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); // 각 벤더당 한 번만 카운트 +// // 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)); +// 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 - ) - ); +// // 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)); +// projectPqVendors.forEach(v => vendorIds.add(v.vendorId)); - // 중복 제거된 협력업체 ID 배열 - const uniqueVendorIds = Array.from(vendorIds); +// // 중복 제거된 협력업체 ID 배열 +// const uniqueVendorIds = Array.from(vendorIds); - // 총 개수 (중복 제거 후) - const total = uniqueVendorIds.length; +// // 총 개수 (중복 제거 후) +// const total = uniqueVendorIds.length; - if (total === 0) { - return { data: [], total: 0 }; - } +// if (total === 0) { +// return { data: [], total: 0 }; +// } - // 페이징 처리 (정렬 후 limit/offset 적용) - const paginatedIds = uniqueVendorIds.slice(offset, offset + input.perPage); +// // 페이징 처리 (정렬 후 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)], - }); +// // 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) 각 벤더별 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) - ) - ); +// // 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; +// 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 상태는 제외 - ) - ); +// // 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; +// 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 - }; +// // 프로젝트 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) - } - }; - }) - ); +// // 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 호출 시 무효화 - } - )(); -} +// 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( @@ -3105,6 +3097,66 @@ export async function togglePQListsAction(ids: number[], newIsDeleted: boolean) const session = await getServerSession(authOptions); const userId = session?.user?.id ? Number(session.user.id) : null; const now = new Date(); + + // 활성화하려는 경우 중복 활성화 체크 + if (!newIsDeleted) { + // 선택된 PQ 리스트들의 정보를 먼저 가져옴 + const selectedPqLists = await db + .select({ + id: pqLists.id, + name: pqLists.name, + type: pqLists.type, + projectId: pqLists.projectId, + }) + .from(pqLists) + .where(inArray(pqLists.id, ids)); + + // 현재 활성화된 PQ 리스트 확인 + const activePqLists = await db + .select({ + id: pqLists.id, + name: pqLists.name, + type: pqLists.type, + projectId: pqLists.projectId, + }) + .from(pqLists) + .where(and( + eq(pqLists.isDeleted, false), + not(inArray(pqLists.id, ids)) + )); + + // 각 선택된 PQ 리스트에 대해 중복 체크 + for (const selectedPq of selectedPqLists) { + // 일반 PQ 또는 미실사 PQ인 경우 + if (selectedPq.type === "GENERAL" || selectedPq.type === "NON_INSPECTION") { + const activeSameType = activePqLists.filter(pq => pq.type === selectedPq.type); + + if (activeSameType.length > 0) { + const activeNames = activeSameType.map(pq => pq.name).join(", "); + return { + success: false, + error: `${selectedPq.type === "GENERAL" ? "일반" : "미실사"} PQ는 하나만 활성화할 수 있습니다.먼저 활성화된 ${selectedPq.type === "GENERAL" ? "일반" : "미실사"} PQ를 비활성화한 후 활성화해주세요.` + }; + } + } + + // 프로젝트 PQ인 경우 + if (selectedPq.type === "PROJECT" && selectedPq.projectId) { + const activeSameProject = activePqLists.filter(pq => + pq.type === "PROJECT" && pq.projectId === selectedPq.projectId + ); + + if (activeSameProject.length > 0) { + const activeNames = activeSameProject.map(pq => pq.name).join(", "); + return { + success: false, + error: `프로젝트 PQ는 프로젝트별로 하나만 활성화할 수 있습니다. 먼저 활성화된 프로젝트 PQ를 비활성화한 후 활성화해주세요.` + }; + } + } + } + } + const updated = await db .update(pqLists) .set({ isDeleted: newIsDeleted, updatedAt: now, updatedBy: userId }) @@ -3726,6 +3778,65 @@ export async function deletePQSubmissionAction(pqSubmissionId: number) { } // PQ 목록별 항목 조회 (특정 pqListId에 속한 PQ 항목들) +// PQ 리스트 정보 조회 (상태 포함) +export async function getPQListInfo(pqListId: number) { + return unstable_cache( + async () => { + try { + const pqList = await db + .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, + }) + .from(pqLists) + .where(and( + eq(pqLists.id, pqListId), + eq(pqLists.isDeleted, false) + )) + .limit(1) + .then(rows => rows[0]); + + if (!pqList) { + return { + success: false, + error: "PQ 목록을 찾을 수 없습니다" + }; + } + + // 현재 시간과 비교하여 상태 결정 + const now = new Date(); + const isValid = !pqList.validTo || pqList.validTo > now; + const status = isValid ? "ACTIVE" : "INACTIVE"; + + return { + success: true, + data: { + ...pqList, + status + } + }; + } catch (error) { + console.error("Error in getPQListInfo:", error); + return { + success: false, + error: "PQ 목록 정보를 가져오는 중 오류가 발생했습니다" + }; + } + }, + [`pq-list-info-${pqListId}`], + { + tags: ["pq-lists"], + revalidate: 3600, // 1시간 + } + )(); +} + export async function getPQsByListId(pqListId: number, input: GetPQSchema) { return unstable_cache( async () => { -- cgit v1.2.3