diff options
Diffstat (limited to 'lib/rfqs/service.ts')
| -rw-r--r-- | lib/rfqs/service.ts | 1596 |
1 files changed, 1370 insertions, 226 deletions
diff --git a/lib/rfqs/service.ts b/lib/rfqs/service.ts index b56349e2..c7d1c3cd 100644 --- a/lib/rfqs/service.ts +++ b/lib/rfqs/service.ts @@ -8,7 +8,7 @@ import { filterColumns } from "@/lib/filter-columns"; import { unstable_cache } from "@/lib/unstable-cache"; import { getErrorMessage } from "@/lib/handle-error"; -import { GetRfqsSchema, CreateRfqSchema, UpdateRfqSchema, CreateRfqItemSchema, GetMatchedVendorsSchema, GetRfqsForVendorsSchema, UpdateRfqVendorSchema, GetTBESchema, RfqType, GetCBESchema } from "./validations"; +import { GetRfqsSchema, CreateRfqSchema, UpdateRfqSchema, CreateRfqItemSchema, GetMatchedVendorsSchema, GetRfqsForVendorsSchema, UpdateRfqVendorSchema, GetTBESchema, RfqType, GetCBESchema, createCbeEvaluationSchema } from "./validations"; import { asc, desc, ilike, inArray, and, gte, lte, not, or, sql, eq, isNull, ne, isNotNull, count } from "drizzle-orm"; import path from "path"; import fs from "fs/promises"; @@ -16,15 +16,16 @@ import { randomUUID } from "crypto"; import { writeFile, mkdir } from 'fs/promises' import { join } from 'path' -import { vendorResponses, vendorResponsesView, Rfq, rfqs, rfqAttachments, rfqItems, RfqWithItems, rfqComments, rfqEvaluations, vendorRfqView, vendorTbeView, rfqsView, vendorResponseAttachments, vendorTechnicalResponses, vendorCbeView, cbeEvaluations, vendorCommercialResponses } from "@/db/schema/rfq"; +import { vendorResponses, vendorResponsesView, Rfq, rfqs, rfqAttachments, rfqItems, RfqWithItems, rfqComments, rfqEvaluations, vendorRfqView, vendorTbeView, rfqsView, vendorResponseAttachments, vendorTechnicalResponses, vendorCbeView, cbeEvaluations, vendorCommercialResponses, vendorResponseCBEView, RfqViewWithItems } from "@/db/schema/rfq"; import { countRfqs, deleteRfqById, deleteRfqsByIds, getRfqById, groupByStatus, insertRfq, insertRfqItem, selectRfqs, updateRfq, updateRfqs, updateRfqVendor } from "./repository"; import logger from '@/lib/logger'; -import { vendorPossibleItems, vendors } from "@/db/schema/vendors"; +import { vendorContacts, vendorPossibleItems, vendors } from "@/db/schema/vendors"; import { sendEmail } from "../mail/sendEmail"; -import { projects } from "@/db/schema/projects"; +import { biddingProjects, projects } from "@/db/schema/projects"; import { items } from "@/db/schema/items"; import * as z from "zod" import { users } from "@/db/schema/users"; +import { headers } from 'next/headers'; interface InviteVendorsInput { @@ -176,6 +177,7 @@ export async function createRfq(input: CreateRfqSchema) { const [newTask] = await insertRfq(tx, { rfqCode: input.rfqCode, projectId: input.projectId || null, + bidProjectId: input.bidProjectId || null, description: input.description || null, dueDate: input.dueDate, status: input.status, @@ -547,7 +549,7 @@ export async function fetchRfqItems(rfqId: number) { })) } -export const findRfqById = async (id: number): Promise<RfqWithItems | null> => { +export const findRfqById = async (id: number): Promise<RfqViewWithItems | null> => { try { logger.info({ id }, 'Fetching user by ID'); const rfq = await getRfqById(id); @@ -726,13 +728,16 @@ export async function getMatchedVendors(input: GetMatchedVendorsSchema, rfqId: n // ───────────────────────────────────────────────────── // 5) 코멘트 조회: 기존과 동일 // ───────────────────────────────────────────────────── + console.log("distinctVendorIds", distinctVendorIds) const commAll = await db .select() .from(rfqComments) .where( and( inArray(rfqComments.vendorId, distinctVendorIds), - eq(rfqComments.rfqId, rfqId) + eq(rfqComments.rfqId, rfqId), + isNull(rfqComments.evaluationId), + isNull(rfqComments.cbeId) ) ) @@ -756,7 +761,7 @@ export async function getMatchedVendors(input: GetMatchedVendorsSchema, rfqId: n userMap.set(user.id, user); } - // 댓글 정보를 벤더 ID별로 그룹화하고, 사용자 이메일 추가 + // 댓글 정보를 협력업체 ID별로 그룹화하고, 사용자 이메일 추가 for (const c of commAll) { const vid = c.vendorId! if (!commByVendorId.has(vid)) { @@ -804,6 +809,9 @@ export async function inviteVendors(input: InviteVendorsInput) { throw new Error("Invalid input") } + const headersList = await headers(); + const host = headersList.get('host') || 'localhost:3000'; + // DB 데이터 준비 및 첨부파일 처리를 위한 트랜잭션 const rfqData = await db.transaction(async (tx) => { // 2-A) RFQ 기본 정보 조회 @@ -869,8 +877,7 @@ export async function inviteVendors(input: InviteVendorsInput) { }) const { rfqRow, items, vendorRows, attachments } = rfqData - const baseUrl = process.env.NEXT_PUBLIC_BASE_URL || 'http://3.36.56.124:3000' - const loginUrl = `${baseUrl}/en/partners/rfq` + const loginUrl = `http://${host}/en/partners/rfq` // 이메일 전송 오류를 기록할 배열 const emailErrors = [] @@ -878,11 +885,11 @@ export async function inviteVendors(input: InviteVendorsInput) { // 각 벤더에 대해 처리 for (const v of vendorRows) { if (!v.email) { - continue // 이메일 없는 벤더 무시 + continue // 이메일 없는 협력업체 무시 } try { - // DB 업데이트: 각 벤더 상태 별도 트랜잭션 + // DB 업데이트: 각 협력업체 상태 별도 트랜잭션 await db.transaction(async (tx) => { // rfq_vendors upsert const existing = await tx @@ -932,10 +939,10 @@ export async function inviteVendors(input: InviteVendorsInput) { attachments, }) } catch (err) { - // 개별 벤더 처리 실패 로깅 + // 개별 협력업체 처리 실패 로깅 console.error(`Failed to process vendor ${v.id}: ${getErrorMessage(err)}`) emailErrors.push({ vendorId: v.id, error: getErrorMessage(err) }) - // 계속 진행 (다른 벤더 처리) + // 계속 진행 (다른 협력업체 처리) } } @@ -1015,7 +1022,7 @@ export async function getTBE(input: GetTBESchema, rfqId: number) { // 5) finalWhere const finalWhere = and( eq(vendorTbeView.rfqId, rfqId), - notRejected, + // notRejected, advancedWhere, globalWhere ) @@ -1057,6 +1064,12 @@ export async function getTBE(input: GetTBESchema, rfqId: number) { tbeResult: vendorTbeView.tbeResult, tbeNote: vendorTbeView.tbeNote, tbeUpdated: vendorTbeView.tbeUpdated, + + technicalResponseId:vendorTbeView.technicalResponseId, + technicalResponseStatus:vendorTbeView.technicalResponseStatus, + technicalSummary:vendorTbeView.technicalSummary, + technicalNotes:vendorTbeView.technicalNotes, + technicalUpdated:vendorTbeView.technicalUpdated, }) .from(vendorTbeView) .where(finalWhere) @@ -1286,8 +1299,7 @@ export async function getTBEforVendor(input: GetTBESchema, vendorId: number) { const finalWhere = and( isNotNull(vendorTbeView.tbeId), eq(vendorTbeView.vendorId, vendorId), - - notRejected, + // notRejected, advancedWhere, globalWhere ) @@ -1318,6 +1330,12 @@ export async function getTBEforVendor(input: GetTBESchema, vendorId: number) { rfqId: vendorTbeView.rfqId, rfqCode: vendorTbeView.rfqCode, + rfqType:vendorTbeView.rfqType, + rfqStatus:vendorTbeView.rfqStatus, + rfqDescription: vendorTbeView.description, + rfqDueDate: vendorTbeView.dueDate, + + projectCode: vendorTbeView.projectCode, projectName: vendorTbeView.projectName, description: vendorTbeView.description, @@ -1491,7 +1509,6 @@ export async function inviteTbeVendorsAction(formData: FormData) { const vendorIdsRaw = formData.getAll("vendorIds[]") const vendorIds = vendorIdsRaw.map((id) => Number(id)) - // 2) FormData에서 파일들 추출 (multiple) const tbeFiles = formData.getAll("tbeFiles") as File[] if (!rfqId || !vendorIds.length || !tbeFiles.length) { @@ -1500,7 +1517,13 @@ export async function inviteTbeVendorsAction(formData: FormData) { // /public/rfq/[rfqId] 경로 const uploadDir = path.join(process.cwd(), "public", "rfq", String(rfqId)) - + + // 디렉토리가 없다면 생성 + try { + await fs.mkdir(uploadDir, { recursive: true }) + } catch (err) { + console.error("디렉토리 생성 실패:", err) + } // DB 트랜잭션 await db.transaction(async (tx) => { @@ -1532,94 +1555,150 @@ export async function inviteTbeVendorsAction(formData: FormData) { .from(rfqItems) .where(eq(rfqItems.rfqId, rfqId)) - // (C) 대상 벤더들 + // (C) 대상 벤더들 (이메일 정보 확장) const vendorRows = await tx - .select({ id: vendors.id, email: vendors.email }) + .select({ + id: vendors.id, + name: vendors.vendorName, + email: vendors.email, + representativeEmail: vendors.representativeEmail // 대표자 이메일 추가 + }) .from(vendors) .where(sql`${vendors.id} in (${vendorIds})`) - // (D) 모든 TBE 파일 저장 & 이후 벤더 초대 처리 + // (D) 모든 TBE 파일 저장 & 이후 협력업체 초대 처리 // 파일은 한 번만 저장해도 되지만, 각 벤더별로 따로 저장/첨부가 필요하다면 루프를 돌려도 됨. - // 여기서는 "모든 파일"을 RFQ-DIR에 저장 + "각 벤더"에는 동일 파일 목록을 첨부한다는 예시. + // 여기서는 "모든 파일"을 RFQ-DIR에 저장 + "각 협력업체"에는 동일 파일 목록을 첨부한다는 예시. const savedFiles = [] for (const file of tbeFiles) { const originalName = file.name || "tbe-sheet.xlsx" - const savePath = path.join(uploadDir, originalName) + // 파일명 충돌 방지를 위한 타임스탬프 추가 + const timestamp = new Date().getTime() + const fileName = `${timestamp}-${originalName}` + const savePath = path.join(uploadDir, fileName) // 파일 ArrayBuffer → Buffer 변환 후 저장 const arrayBuffer = await file.arrayBuffer() - fs.writeFile(savePath, Buffer.from(arrayBuffer)) + await fs.writeFile(savePath, Buffer.from(arrayBuffer)) // 저장 경로 & 파일명 기록 savedFiles.push({ - fileName: originalName, - filePath: `/rfq/${rfqId}/${originalName}`, // public 이하 경로 + fileName: originalName, // 원본 파일명으로 첨부 + filePath: `/rfq/${rfqId}/${fileName}`, // public 이하 경로 absolutePath: savePath, }) } // (E) 각 벤더별로 TBE 평가 레코드, 초대 처리, 메일 발송 - for (const v of vendorRows) { - if (!v.email) { - // 이메일 없는 경우 로직 (스킵 or throw) + for (const vendor of vendorRows) { + // 1) 협력업체 연락처 조회 - 추가 이메일 수집 + const contacts = await tx + .select({ + contactName: vendorContacts.contactName, + contactEmail: vendorContacts.contactEmail, + isPrimary: vendorContacts.isPrimary, + }) + .from(vendorContacts) + .where(eq(vendorContacts.vendorId, vendor.id)) + + // 2) 모든 이메일 주소 수집 및 중복 제거 + const allEmails = new Set<string>() + + // 협력업체 이메일 추가 (있는 경우에만) + if (vendor.email) { + allEmails.add(vendor.email.trim().toLowerCase()) + } + + // 협력업체 대표자 이메일 추가 (있는 경우에만) + if (vendor.representativeEmail) { + allEmails.add(vendor.representativeEmail.trim().toLowerCase()) + } + + // 연락처 이메일 추가 + contacts.forEach(contact => { + if (contact.contactEmail) { + allEmails.add(contact.contactEmail.trim().toLowerCase()) + } + }) + + // 중복이 제거된 이메일 주소 배열로 변환 + const uniqueEmails = Array.from(allEmails) + + if (uniqueEmails.length === 0) { + console.warn(`협력업체 ID ${vendor.id}에 등록된 이메일 주소가 없습니다. TBE 초대를 건너뜁니다.`) continue } - // 1) TBE 평가 레코드 생성 + // 3) TBE 평가 레코드 생성 const [evalRow] = await tx .insert(rfqEvaluations) .values({ rfqId, - vendorId: v.id, + vendorId: vendor.id, evalType: "TBE", }) .returning({ id: rfqEvaluations.id }) - // 2) rfqAttachments에 저장한 파일들을 기록 + // 4) rfqAttachments에 저장한 파일들을 기록 for (const sf of savedFiles) { await tx.insert(rfqAttachments).values({ rfqId, - // vendorId: v.id, + vendorId: vendor.id, evaluationId: evalRow.id, fileName: sf.fileName, filePath: sf.filePath, }) } - // 4) 메일 발송 + // 5) 각 고유 이메일 주소로 초대 메일 발송 const baseUrl = process.env.NEXT_PUBLIC_BASE_URL || 'http://3.36.56.124:3000' const loginUrl = `${baseUrl}/ko/partners/rfq` - await sendEmail({ - to: v.email, - subject: `[RFQ ${rfqRow.rfqCode}] You are invited for TBE!`, - template: "rfq-invite", - context: { - language: "en", - rfqId, - vendorId: v.id, - - rfqCode: rfqRow.rfqCode, - projectCode: rfqRow.projectCode, - projectName: rfqRow.projectName, - dueDate: rfqRow.dueDate, - description: rfqRow.description, - - items: items.map((it) => ({ - itemCode: it.itemCode, - description: it.description, - quantity: it.quantity, - uom: it.uom, - })), - loginUrl, - }, - attachments: savedFiles.map((sf) => ({ - path: sf.absolutePath, - filename: sf.fileName, - })), - }) + + console.log(`협력업체 ID ${vendor.id}(${vendor.name})에 대해 ${uniqueEmails.length}개의 고유 이메일로 TBE 초대 발송`) + + for (const email of uniqueEmails) { + try { + // 연락처 이름 찾기 (이메일과 일치하는 연락처가 있으면 사용, 없으면 '벤더명 담당자'로 대체) + const contact = contacts.find(c => + c.contactEmail && c.contactEmail.toLowerCase() === email.toLowerCase() + ) + const contactName = contact?.contactName || `${vendor.name} 담당자` + + await sendEmail({ + to: email, + subject: `[RFQ ${rfqRow.rfqCode}] You are invited for TBE!`, + template: "rfq-invite", + context: { + language: "en", + rfqId, + vendorId: vendor.id, + contactName, // 연락처 이름 추가 + rfqCode: rfqRow.rfqCode, + projectCode: rfqRow.projectCode, + projectName: rfqRow.projectName, + dueDate: rfqRow.dueDate, + description: rfqRow.description, + items: items.map((it) => ({ + itemCode: it.itemCode, + description: it.description, + quantity: it.quantity, + uom: it.uom, + })), + loginUrl, + }, + attachments: savedFiles.map((sf) => ({ + path: sf.absolutePath, + filename: sf.fileName, + })), + }) + console.log(`이메일 전송 성공: ${email} (${contactName})`) + } catch (emailErr) { + console.error(`이메일 전송 실패 (${email}):`, emailErr) + } + } } - // 5) 캐시 무효화 + // 6) 캐시 무효화 revalidateTag("tbe-vendors") }) @@ -1662,8 +1741,8 @@ export async function createRfqCommentWithAttachments(params: { files?: File[] }) { const { rfqId, vendorId, commentText, commentedBy, evaluationId,cbeId, files } = params - - + console.log("cbeId", cbeId) + console.log("evaluationId", evaluationId) // 1) 새로운 코멘트 생성 const [insertedComment] = await db .insert(rfqComments) @@ -1797,6 +1876,37 @@ export async function getProjects(): Promise<Project[]> { } +export async function getBidProjects(): Promise<Project[]> { + try { + // 트랜잭션을 사용하여 프로젝트 데이터 조회 + const projectList = await db.transaction(async (tx) => { + // 모든 프로젝트 조회 + const results = await tx + .select({ + id: biddingProjects.id, + projectCode: biddingProjects.pspid, + projectName: biddingProjects.projNm, + }) + .from(biddingProjects) + .orderBy(biddingProjects.id); + + return results; + }); + + // Handle null projectName values + const validProjectList = projectList.map(project => ({ + ...project, + projectName: project.projectName || '' // Replace null with empty string + })); + + return validProjectList; + } catch (error) { + console.error("프로젝트 목록 가져오기 실패:", error); + return []; // 오류 발생 시 빈 배열 반환 + } +} + + // 반환 타입 명시적 정의 - rfqCode가 null일 수 있음을 반영 export interface BudgetaryRfq { id: number; @@ -1919,6 +2029,19 @@ export async function getAllVendors() { return allVendors } + +export async function getVendorContactsByVendorId(vendorId: number) { + try { + const contacts = await db.query.vendorContacts.findMany({ + where: eq(vendorContacts.vendorId, vendorId), + }); + + return { success: true, data: contacts }; + } catch (error) { + console.error("Error fetching vendor contacts:", error); + return { success: false, error: "Failed to fetch vendor contacts" }; + } +} /** * Server action to associate items from an RFQ with a vendor * @@ -2020,8 +2143,6 @@ export async function addItemToVendors(rfqId: number, vendorIds: number[]) { * evaluationId가 일치하고 vendorId가 null인 파일 목록 */ export async function fetchTbeTemplateFiles(evaluationId: number) { - - console.log(evaluationId, "evaluationId") try { const files = await db .select({ @@ -2051,10 +2172,7 @@ export async function fetchTbeTemplateFiles(evaluationId: number) { } } -/** - * 특정 TBE 템플릿 파일 다운로드를 위한 정보 조회 - */ -export async function getTbeTemplateFileInfo(fileId: number) { +export async function getFileFromRfqAttachmentsbyid(fileId: number) { try { const file = await db .select({ @@ -2128,6 +2246,7 @@ export async function uploadTbeResponseFile(formData: FormData) { responseId: vendorResponseId, summary: "TBE 응답 파일 업로드", // 필요에 따라 수정 notes: `파일명: ${originalName}`, + responseStatus:"SUBMITTED" }) .returning({ id: vendorTechnicalResponses.id }); @@ -2354,7 +2473,9 @@ export async function getAllTBE(input: GetTBESchema) { rfqVendorStatus: vendorTbeView.rfqVendorStatus, rfqVendorUpdated: vendorTbeView.rfqVendorUpdated, + technicalResponseStatus:vendorTbeView.technicalResponseStatus, tbeResult: vendorTbeView.tbeResult, + tbeNote: vendorTbeView.tbeNote, tbeUpdated: vendorTbeView.tbeUpdated, }) @@ -2562,9 +2683,6 @@ export async function getAllTBE(input: GetTBESchema) { } - - - export async function getCBE(input: GetCBESchema, rfqId: number) { return unstable_cache( async () => { @@ -2574,7 +2692,7 @@ export async function getCBE(input: GetCBESchema, rfqId: number) { // [2] 고급 필터 const advancedWhere = filterColumns({ - table: vendorCbeView, + table: vendorResponseCBEView, filters: input.filters ?? [], joinOperator: input.joinOperator ?? "and", }); @@ -2584,73 +2702,83 @@ export async function getCBE(input: GetCBESchema, rfqId: number) { if (input.search) { const s = `%${input.search}%`; globalWhere = or( - sql`${vendorCbeView.vendorName} ILIKE ${s}`, - sql`${vendorCbeView.vendorCode} ILIKE ${s}`, - sql`${vendorCbeView.email} ILIKE ${s}` + sql`${vendorResponseCBEView.vendorName} ILIKE ${s}`, + sql`${vendorResponseCBEView.vendorCode} ILIKE ${s}`, + sql`${vendorResponseCBEView.rfqCode} ILIKE ${s}`, + sql`${vendorResponseCBEView.totalPrice}::text ILIKE ${s}` ); } - // [4] REJECTED 아니거나 NULL - const notRejected = or( - ne(vendorCbeView.rfqVendorStatus, "REJECTED"), - isNull(vendorCbeView.rfqVendorStatus) - ); + // [4] DECLINED 상태 제외 (거절된 업체는 표시하지 않음) + const notDeclined = ne(vendorResponseCBEView.responseStatus, "DECLINED"); - // [5] 최종 where + // [5] 최종 where 조건 const finalWhere = and( - eq(vendorCbeView.rfqId, rfqId), - notRejected, - advancedWhere, - globalWhere + eq(vendorResponseCBEView.rfqId, rfqId), + notDeclined, + advancedWhere ?? undefined, + globalWhere ?? undefined ); // [6] 정렬 const orderBy = input.sort?.length ? input.sort.map((s) => { - // vendor_cbe_view 컬럼 중 정렬 대상이 되는 것만 매핑 - const col = (vendorCbeView as any)[s.id]; - return s.desc ? desc(col) : asc(col); - }) - : [asc(vendorCbeView.vendorId)]; + // vendorResponseCBEView 컬럼 중 정렬 대상이 되는 것만 매핑 + const col = (vendorResponseCBEView as any)[s.id]; + return s.desc ? desc(col) : asc(col); + }) + : [asc(vendorResponseCBEView.vendorName)]; // 기본 정렬은 벤더명 // [7] 메인 SELECT const [rows, total] = await db.transaction(async (tx) => { const data = await tx .select({ - // 필요한 컬럼만 추출 - id: vendorCbeView.vendorId, - cbeId: vendorCbeView.cbeId, - vendorId: vendorCbeView.vendorId, - vendorName: vendorCbeView.vendorName, - vendorCode: vendorCbeView.vendorCode, - address: vendorCbeView.address, - country: vendorCbeView.country, - email: vendorCbeView.email, - website: vendorCbeView.website, - vendorStatus: vendorCbeView.vendorStatus, - - rfqId: vendorCbeView.rfqId, - rfqCode: vendorCbeView.rfqCode, - projectCode: vendorCbeView.projectCode, - projectName: vendorCbeView.projectName, - description: vendorCbeView.description, - dueDate: vendorCbeView.dueDate, - - rfqVendorStatus: vendorCbeView.rfqVendorStatus, - rfqVendorUpdated: vendorCbeView.rfqVendorUpdated, - - cbeResult: vendorCbeView.cbeResult, - cbeNote: vendorCbeView.cbeNote, - cbeUpdated: vendorCbeView.cbeUpdated, - - // 상업평가 정보 - totalCost: vendorCbeView.totalCost, - currency: vendorCbeView.currency, - paymentTerms: vendorCbeView.paymentTerms, - incoterms: vendorCbeView.incoterms, - deliverySchedule: vendorCbeView.deliverySchedule, + // 기본 식별 정보 + responseId: vendorResponseCBEView.responseId, + vendorId: vendorResponseCBEView.vendorId, + rfqId: vendorResponseCBEView.rfqId, + + // 협력업체 정보 + vendorName: vendorResponseCBEView.vendorName, + vendorCode: vendorResponseCBEView.vendorCode, + vendorStatus: vendorResponseCBEView.vendorStatus, + + // RFQ 정보 + rfqCode: vendorResponseCBEView.rfqCode, + rfqDescription: vendorResponseCBEView.rfqDescription, + rfqDueDate: vendorResponseCBEView.rfqDueDate, + rfqStatus: vendorResponseCBEView.rfqStatus, + rfqType: vendorResponseCBEView.rfqType, + + // 프로젝트 정보 + projectId: vendorResponseCBEView.projectId, + projectCode: vendorResponseCBEView.projectCode, + projectName: vendorResponseCBEView.projectName, + + // 응답 상태 정보 + responseStatus: vendorResponseCBEView.responseStatus, + responseNotes: vendorResponseCBEView.notes, + respondedAt: vendorResponseCBEView.respondedAt, + respondedBy: vendorResponseCBEView.respondedBy, + + // 상업 응답 정보 + commercialResponseId: vendorResponseCBEView.commercialResponseId, + commercialResponseStatus: vendorResponseCBEView.commercialResponseStatus, + totalPrice: vendorResponseCBEView.totalPrice, + currency: vendorResponseCBEView.currency, + paymentTerms: vendorResponseCBEView.paymentTerms, + incoterms: vendorResponseCBEView.incoterms, + deliveryPeriod: vendorResponseCBEView.deliveryPeriod, + warrantyPeriod: vendorResponseCBEView.warrantyPeriod, + validityPeriod: vendorResponseCBEView.validityPeriod, + commercialNotes: vendorResponseCBEView.commercialNotes, + + // 첨부파일 카운트 + attachmentCount: vendorResponseCBEView.attachmentCount, + commercialAttachmentCount: vendorResponseCBEView.commercialAttachmentCount, + technicalAttachmentCount: vendorResponseCBEView.technicalAttachmentCount, }) - .from(vendorCbeView) + .from(vendorResponseCBEView) .where(finalWhere) .orderBy(...orderBy) .offset(offset) @@ -2658,122 +2786,89 @@ export async function getCBE(input: GetCBESchema, rfqId: number) { const [{ count }] = await tx .select({ count: sql<number>`count(*)`.as("count") }) - .from(vendorCbeView) + .from(vendorResponseCBEView) .where(finalWhere); return [data, Number(count)]; }); if (!rows.length) { - return { data: [], pageCount: 0 }; + return { data: [], pageCount: 0, total: 0 }; } - // [8] Comments 조회 - // TBE 에서는 rfqComments + rfqEvaluations(evalType="TBE") 를 조인했지만, - // CBE는 cbeEvaluations 또는 evalType="CBE"를 기준으로 바꾸면 됩니다. - // 만약 cbeEvaluations.id 를 evaluationId 로 참조한다면 아래와 같이 innerJoin: + // [8] 협력업체 ID 목록 추출 const distinctVendorIds = [...new Set(rows.map((r) => r.vendorId))]; + const distinctResponseIds = [...new Set(rows.map((r) => r.responseId))]; + const distinctCommercialResponseIds = [...new Set(rows.filter(r => r.commercialResponseId).map((r) => r.commercialResponseId!))]; - const commAll = await db + // [9] CBE 평가 관련 코멘트 조회 + const commentsAll = await db .select({ id: rfqComments.id, commentText: rfqComments.commentText, vendorId: rfqComments.vendorId, - evaluationId: rfqComments.evaluationId, + cbeId: rfqComments.cbeId, createdAt: rfqComments.createdAt, commentedBy: rfqComments.commentedBy, - // cbeEvaluations에는 evalType 컬럼이 별도로 없을 수도 있음(프로젝트 구조에 맞게 수정) - // evalType: cbeEvaluations.evalType, }) .from(rfqComments) .innerJoin( - cbeEvaluations, - eq(cbeEvaluations.id, rfqComments.evaluationId) + vendorResponses, + eq(vendorResponses.id, rfqComments.cbeId) ) .where( and( - isNotNull(rfqComments.evaluationId), + isNotNull(rfqComments.cbeId), eq(rfqComments.rfqId, rfqId), inArray(rfqComments.vendorId, distinctVendorIds) ) ); - // vendorId -> comments grouping - const commByVendorId = new Map<number, any[]>(); - for (const c of commAll) { - const vid = c.vendorId!; - if (!commByVendorId.has(vid)) { - commByVendorId.set(vid, []); + // vendorId별 코멘트 그룹화 + const commentsByVendorId = new Map<number, any[]>(); + for (const comment of commentsAll) { + const vendorId = comment.vendorId!; + if (!commentsByVendorId.has(vendorId)) { + commentsByVendorId.set(vendorId, []); } - commByVendorId.get(vid)!.push({ - id: c.id, - commentText: c.commentText, - vendorId: c.vendorId, - evaluationId: c.evaluationId, - createdAt: c.createdAt, - commentedBy: c.commentedBy, + commentsByVendorId.get(vendorId)!.push({ + id: comment.id, + commentText: comment.commentText, + vendorId: comment.vendorId, + cbeId: comment.cbeId, + createdAt: comment.createdAt, + commentedBy: comment.commentedBy, }); } - // [9] CBE 파일 조회 (프로젝트에 따라 구조가 달라질 수 있음) - // - TBE는 vendorTechnicalResponses 기준 - // - CBE는 vendorCommercialResponses(가정) 등이 있을 수 있음 - // - 여기서는 예시로 "동일한 vendorResponses + vendorResponseAttachments" 라고 가정 - // Step 1: vendorResponses 가져오기 (rfqId + vendorIds) - const responsesAll = await db + // [10] 첨부 파일 조회 - 일반 응답 첨부파일 + const responseAttachments = await db .select({ - id: vendorResponses.id, - vendorId: vendorResponses.vendorId, + id: vendorResponseAttachments.id, + fileName: vendorResponseAttachments.fileName, + filePath: vendorResponseAttachments.filePath, + responseId: vendorResponseAttachments.responseId, + fileType: vendorResponseAttachments.fileType, + attachmentType: vendorResponseAttachments.attachmentType, + description: vendorResponseAttachments.description, + uploadedAt: vendorResponseAttachments.uploadedAt, + uploadedBy: vendorResponseAttachments.uploadedBy, }) - .from(vendorResponses) + .from(vendorResponseAttachments) .where( and( - eq(vendorResponses.rfqId, rfqId), - inArray(vendorResponses.vendorId, distinctVendorIds) + inArray(vendorResponseAttachments.responseId, distinctResponseIds), + isNotNull(vendorResponseAttachments.responseId) ) ); - // Group responses by vendorId - const responsesByVendorId = new Map<number, number[]>(); - for (const resp of responsesAll) { - if (!responsesByVendorId.has(resp.vendorId)) { - responsesByVendorId.set(resp.vendorId, []); - } - responsesByVendorId.get(resp.vendorId)!.push(resp.id); - } - - // Step 2: responseIds - const allResponseIds = responsesAll.map((r) => r.id); - - - const commercialResponsesAll = await db - .select({ - id: vendorCommercialResponses.id, - responseId: vendorCommercialResponses.responseId, - }) - .from(vendorCommercialResponses) - .where(inArray(vendorCommercialResponses.responseId, allResponseIds)); - - const commercialResponseIdsByResponseId = new Map<number, number[]>(); - for (const cr of commercialResponsesAll) { - if (!commercialResponseIdsByResponseId.has(cr.responseId)) { - commercialResponseIdsByResponseId.set(cr.responseId, []); - } - commercialResponseIdsByResponseId.get(cr.responseId)!.push(cr.id); - } - - const allCommercialResponseIds = commercialResponsesAll.map((cr) => cr.id); - - - // 여기서는 예시로 TBE와 마찬가지로 vendorResponseAttachments를 - // 직접 responseId로 관리한다고 가정(혹은 commercialResponseId로 연결) - // Step 3: vendorResponseAttachments 조회 - const filesAll = await db + // [11] 첨부 파일 조회 - 상업 응답 첨부파일 + const commercialResponseAttachments = await db .select({ id: vendorResponseAttachments.id, fileName: vendorResponseAttachments.fileName, filePath: vendorResponseAttachments.filePath, - responseId: vendorResponseAttachments.responseId, + commercialResponseId: vendorResponseAttachments.commercialResponseId, fileType: vendorResponseAttachments.fileType, attachmentType: vendorResponseAttachments.attachmentType, description: vendorResponseAttachments.description, @@ -2783,19 +2878,20 @@ export async function getCBE(input: GetCBESchema, rfqId: number) { .from(vendorResponseAttachments) .where( and( - inArray(vendorResponseAttachments.responseId, allCommercialResponseIds), - isNotNull(vendorResponseAttachments.responseId) + inArray(vendorResponseAttachments.commercialResponseId, distinctCommercialResponseIds), + isNotNull(vendorResponseAttachments.commercialResponseId) ) ); - // Step 4: responseId -> files + // [12] 첨부파일 그룹화 + // responseId별 첨부파일 맵 생성 const filesByResponseId = new Map<number, any[]>(); - for (const file of filesAll) { - const rid = file.responseId!; - if (!filesByResponseId.has(rid)) { - filesByResponseId.set(rid, []); + for (const file of responseAttachments) { + const responseId = file.responseId!; + if (!filesByResponseId.has(responseId)) { + filesByResponseId.set(responseId, []); } - filesByResponseId.get(rid)!.push({ + filesByResponseId.get(responseId)!.push({ id: file.id, fileName: file.fileName, filePath: file.filePath, @@ -2804,40 +2900,66 @@ export async function getCBE(input: GetCBESchema, rfqId: number) { description: file.description, uploadedAt: file.uploadedAt, uploadedBy: file.uploadedBy, + attachmentSource: 'response' }); } - // Step 5: vendorId -> files - const filesByVendorId = new Map<number, any[]>(); - for (const [vendorId, responseIds] of responsesByVendorId.entries()) { - filesByVendorId.set(vendorId, []); - for (const responseId of responseIds) { - const files = filesByResponseId.get(responseId) || []; - filesByVendorId.get(vendorId)!.push(...files); + // commercialResponseId별 첨부파일 맵 생성 + const filesByCommercialResponseId = new Map<number, any[]>(); + for (const file of commercialResponseAttachments) { + const commercialResponseId = file.commercialResponseId!; + if (!filesByCommercialResponseId.has(commercialResponseId)) { + filesByCommercialResponseId.set(commercialResponseId, []); } + filesByCommercialResponseId.get(commercialResponseId)!.push({ + id: file.id, + fileName: file.fileName, + filePath: file.filePath, + fileType: file.fileType, + attachmentType: file.attachmentType, + description: file.description, + uploadedAt: file.uploadedAt, + uploadedBy: file.uploadedBy, + attachmentSource: 'commercial' + }); } - // [10] 최종 데이터 합치기 - const final = rows.map((row) => ({ - ...row, - dueDate: row.dueDate ? new Date(row.dueDate) : null, - comments: commByVendorId.get(row.vendorId) ?? [], - files: filesByVendorId.get(row.vendorId) ?? [], - })); + // [13] 최종 데이터 병합 + const final = rows.map((row) => { + // 해당 응답의 모든 첨부파일 가져오기 + const responseFiles = filesByResponseId.get(row.responseId) || []; + const commercialFiles = row.commercialResponseId + ? filesByCommercialResponseId.get(row.commercialResponseId) || [] + : []; + + // 모든 첨부파일 병합 + const allFiles = [...responseFiles, ...commercialFiles]; + + return { + ...row, + rfqDueDate: row.rfqDueDate ? new Date(row.rfqDueDate) : null, + respondedAt: row.respondedAt ? new Date(row.respondedAt) : null, + comments: commentsByVendorId.get(row.vendorId) || [], + files: allFiles, + }; + }); const pageCount = Math.ceil(total / limit); - return { data: final, pageCount }; + return { + data: final, + pageCount, + total + }; }, // 캐싱 키 & 옵션 - [JSON.stringify({ input, rfqId })], + [`cbe-vendors-${rfqId}-${JSON.stringify(input)}`], { revalidate: 3600, - tags: ["cbe-vendors"], + tags: [`cbe-vendors-${rfqId}`], } )(); } - export async function generateNextRfqCode(rfqType: RfqType): Promise<{ code: string; error?: string }> { try { if (!rfqType) { @@ -2880,4 +3002,1026 @@ export async function generateNextRfqCode(rfqType: RfqType): Promise<{ code: str console.error('Error generating next RFQ code:', error); return { code: "", error: '코드 생성에 실패했습니다' }; } +} + +interface SaveTbeResultParams { + id: number // id from the rfq_evaluations table + vendorId: number // vendorId from the rfq_evaluations table + result: string // The selected evaluation result + notes: string // The evaluation notes +} + +export async function saveTbeResult({ + id, + vendorId, + result, + notes, +}: SaveTbeResultParams) { + try { + // Check if we have all required data + if (!id || !vendorId || !result) { + return { + success: false, + message: "Missing required data for evaluation update", + } + } + + // Update the record in the database + await db + .update(rfqEvaluations) + .set({ + result: result, + notes: notes, + updatedAt: new Date(), + }) + .where( + and( + eq(rfqEvaluations.id, id), + eq(rfqEvaluations.vendorId, vendorId), + eq(rfqEvaluations.evalType, "TBE") + ) + ) + + // Revalidate the tbe-vendors tag to refresh the data + revalidateTag("tbe-vendors") + revalidateTag("all-tbe-vendors") + + return { + success: true, + message: "TBE evaluation updated successfully", + } + } catch (error) { + console.error("Failed to update TBE evaluation:", error) + + return { + success: false, + message: error instanceof Error ? error.message : "An unknown error occurred", + } + } +} + + +export async function createCbeEvaluation(formData: FormData) { + try { + // 폼 데이터 추출 + const rfqId = Number(formData.get("rfqId")) + const vendorIds = formData.getAll("vendorIds[]").map(id => Number(id)) + const evaluatedBy = formData.get("evaluatedBy") ? Number(formData.get("evaluatedBy")) : null + + + const headersList = await headers(); + const host = headersList.get('host') || 'localhost:3000'; + + // 기본 CBE 데이터 추출 + const rawData = { + rfqId, + paymentTerms: formData.get("paymentTerms") as string, + incoterms: formData.get("incoterms") as string, + deliverySchedule: formData.get("deliverySchedule") as string, + notes: formData.get("notes") as string, + // 단일 협력업체 처리 시 사용할 vendorId (여러 협력업체 처리에선 사용하지 않음) + // vendorId: vendorIds[0] || 0, + } + + // zod 스키마 유효성 검사 (vendorId는 더미로 채워 검증하고 실제로는 배열로 처리) + const validationResult = createCbeEvaluationSchema.safeParse(rawData) + if (!validationResult.success) { + const errors = validationResult.error.format() + console.error("Validation errors:", errors) + return { error: "입력 데이터가 유효하지 않습니다." } + } + + const validData = validationResult.data + + // RFQ 정보 조회 + const [rfqInfo] = await db + .select({ + rfqCode: rfqsView.rfqCode, + projectCode: rfqsView.projectCode, + projectName: rfqsView.projectName, + dueDate: rfqsView.dueDate, + description: rfqsView.description, + }) + .from(rfqsView) + .where(eq(rfqsView.id, rfqId)) + + if (!rfqInfo) { + return { error: "RFQ 정보를 찾을 수 없습니다." } + } + + // 파일 처리 준비 + const files = formData.getAll("files") as File[] + const hasFiles = files && files.length > 0 && files[0].size > 0 + + // 파일 저장을 위한 디렉토리 생성 (파일이 있는 경우에만) + let uploadDir = "" + if (hasFiles) { + uploadDir = path.join(process.cwd(), "public", "rfq", String(rfqId)) + try { + await fs.mkdir(uploadDir, { recursive: true }) + } catch (err) { + console.error("디렉토리 생성 실패:", err) + return { error: "파일 업로드를 위한 디렉토리 생성에 실패했습니다." } + } + } + + // 첨부 파일 정보를 저장할 배열 + const attachments: { filename: string; path: string }[] = [] + + // 파일이 있는 경우, 파일을 저장하고 첨부 파일 정보 준비 + if (hasFiles) { + for (const file of files) { + if (file.size > 0) { + const originalFilename = file.name + const fileExtension = path.extname(originalFilename) + const timestamp = new Date().getTime() + const safeFilename = `cbe-${rfqId}-${timestamp}${fileExtension}` + const filePath = path.join("rfq", String(rfqId), safeFilename) + const fullPath = path.join(process.cwd(), "public", filePath) + + try { + // File을 ArrayBuffer로 변환하여 파일 시스템에 저장 + const arrayBuffer = await file.arrayBuffer() + const buffer = Buffer.from(arrayBuffer) + await fs.writeFile(fullPath, buffer) + + // 첨부 파일 정보 추가 + attachments.push({ + filename: originalFilename, + path: fullPath, // 이메일 첨부를 위한 전체 경로 + }) + } catch (err) { + console.error(`파일 저장 실패:`, err) + // 파일 저장 실패를 기록하지만 전체 프로세스는 계속 진행 + } + } + } + } + + // 각 벤더별로 CBE 평가 레코드 생성 및 알림 전송 + const createdCbeIds: number[] = [] + const failedVendors: { id: number, reason: string }[] = [] + + for (const vendorId of vendorIds) { + try { + // 협력업체 정보 조회 (이메일 포함) + const [vendorInfo] = await db + .select({ + id: vendors.id, + name: vendors.vendorName, + vendorCode: vendors.vendorCode, + email: vendors.email, // 협력업체 자체 이메일 추가 + representativeEmail: vendors.representativeEmail, // 협력업체 대표자 이메일 추가 + }) + .from(vendors) + .where(eq(vendors.id, vendorId)) + + if (!vendorInfo) { + failedVendors.push({ id: vendorId, reason: "협력업체 정보를 찾을 수 없습니다." }) + continue + } + + // 기존 협력업체 응답 레코드 찾기 + const existingResponse = await db + .select({ id: vendorResponses.id }) + .from(vendorResponses) + .where( + and( + eq(vendorResponses.rfqId, rfqId), + eq(vendorResponses.vendorId, vendorId) + ) + ) + .limit(1) + + if (existingResponse.length === 0) { + console.error(`협력업체 ID ${vendorId}에 대한 응답 레코드가 존재하지 않습니다.`) + failedVendors.push({ id: vendorId, reason: "협력업체 응답 레코드를 찾을 수 없습니다" }) + continue // 다음 벤더로 넘어감 + } + + // 1. CBE 평가 레코드 생성 + const [newCbeEvaluation] = await db + .insert(cbeEvaluations) + .values({ + rfqId, + vendorId, + evaluatedBy, + result: "PENDING", // 초기 상태는 PENDING으로 설정 + totalCost: 0, // 초기값은 0으로 설정 + currency: "USD", // 기본 통화 설정 + paymentTerms: validData.paymentTerms || null, + incoterms: validData.incoterms || null, + deliverySchedule: validData.deliverySchedule || null, + notes: validData.notes || null, + }) + .returning({ id: cbeEvaluations.id }) + + if (!newCbeEvaluation?.id) { + failedVendors.push({ id: vendorId, reason: "CBE 평가 생성 실패" }) + continue + } + + // 2. 상업 응답 레코드 생성 + const [newCbeResponse] = await db + .insert(vendorCommercialResponses) + .values({ + responseId: existingResponse[0].id, + responseStatus: "PENDING", + currency: "USD", + paymentTerms: validData.paymentTerms || null, + incoterms: validData.incoterms || null, + deliveryPeriod: validData.deliverySchedule || null, + }) + .returning({ id: vendorCommercialResponses.id }) + + if (!newCbeResponse?.id) { + failedVendors.push({ id: vendorId, reason: "상업 응답 생성 실패" }) + continue + } + + createdCbeIds.push(newCbeEvaluation.id) + + // 3. 첨부 파일이 있는 경우, 데이터베이스에 첨부 파일 레코드 생성 + if (hasFiles) { + for (let i = 0; i < attachments.length; i++) { + const attachment = attachments[i] + + await db.insert(rfqAttachments).values({ + rfqId, + vendorId, + fileName: attachment.filename, + filePath: `/${path.relative(path.join(process.cwd(), "public"), attachment.path)}`, // URL 경로를 위해 public 기준 상대 경로로 저장 + cbeId: newCbeEvaluation.id, + }) + } + } + + // 4. 협력업체 연락처 조회 + const contacts = await db + .select({ + contactName: vendorContacts.contactName, + contactEmail: vendorContacts.contactEmail, + isPrimary: vendorContacts.isPrimary, + }) + .from(vendorContacts) + .where(eq(vendorContacts.vendorId, vendorId)) + + // 5. 모든 이메일 주소 수집 및 중복 제거 + const allEmails = new Set<string>() + + // 연락처 이메일 추가 + contacts.forEach(contact => { + if (contact.contactEmail) { + allEmails.add(contact.contactEmail.trim().toLowerCase()) + } + }) + + // 협력업체 자체 이메일 추가 (있는 경우에만) + if (vendorInfo.email) { + allEmails.add(vendorInfo.email.trim().toLowerCase()) + } + + // 협력업체 대표자 이메일 추가 (있는 경우에만) + if (vendorInfo.representativeEmail) { + allEmails.add(vendorInfo.representativeEmail.trim().toLowerCase()) + } + + // 중복이 제거된 이메일 주소 배열로 변환 + const uniqueEmails = Array.from(allEmails) + + if (uniqueEmails.length === 0) { + console.warn(`협력업체 ID ${vendorId}에 등록된 이메일 주소가 없습니다.`) + } else { + console.log(`협력업체 ID ${vendorId}에 대해 ${uniqueEmails.length}개의 고유 이메일 주소로 알림을 전송합니다.`) + + // 이메일 발송에 필요한 공통 데이터 준비 + const emailData = { + rfqId, + cbeId: newCbeEvaluation.id, + vendorId, + rfqCode: rfqInfo.rfqCode, + projectCode: rfqInfo.projectCode, + projectName: rfqInfo.projectName, + dueDate: rfqInfo.dueDate, + description: rfqInfo.description, + vendorName: vendorInfo.name, + vendorCode: vendorInfo.vendorCode, + paymentTerms: validData.paymentTerms, + incoterms: validData.incoterms, + deliverySchedule: validData.deliverySchedule, + notes: validData.notes, + loginUrl: `http://${host}/en/partners/cbe` + } + + // 각 고유 이메일 주소로 이메일 발송 + for (const email of uniqueEmails) { + try { + // 연락처 이름 찾기 (이메일과 일치하는 연락처가 있으면 사용, 없으면 '벤더명 담당자'로 대체) + const contact = contacts.find(c => + c.contactEmail && c.contactEmail.toLowerCase() === email.toLowerCase() + ) + const contactName = contact?.contactName || `${vendorInfo.name} 담당자` + + await sendEmail({ + to: email, + subject: `[RFQ ${rfqInfo.rfqCode}] 상업 입찰 평가 (CBE) 알림`, + template: "cbe-invitation", + context: { + language: "ko", // 또는 다국어 처리를 위한 설정 + contactName, + ...emailData, + }, + attachments: attachments, + }) + console.log(`이메일 전송 성공: ${email}`) + } catch (emailErr) { + console.error(`이메일 전송 실패 (${email}):`, emailErr) + } + } + } + + } catch (err) { + console.error(`협력업체 ID ${vendorId}의 CBE 생성 실패:`, err) + failedVendors.push({ id: vendorId, reason: "예기치 않은 오류" }) + } + } + + // UI 업데이트를 위한 경로 재검증 + revalidatePath(`/rfq/${rfqId}`) + revalidateTag(`cbe-vendors-${rfqId}`) + + // 결과 반환 + if (createdCbeIds.length === 0) { + return { error: "어떤 벤더에 대해서도 CBE 평가를 생성하지 못했습니다." } + } + + return { + success: true, + cbeIds: createdCbeIds, + totalCreated: createdCbeIds.length, + totalFailed: failedVendors.length, + failedVendors: failedVendors.length > 0 ? failedVendors : undefined + } + + } catch (error) { + console.error("CBE 평가 생성 중 오류 발생:", error) + return { error: "예상치 못한 오류가 발생했습니다." } + } +} + +export async function getCBEbyVendorId(input: GetCBESchema, vendorId: number) { + return unstable_cache( + async () => { + // [1] 페이징 + const offset = ((input.page ?? 1) - 1) * (input.perPage ?? 10); + const limit = input.perPage ?? 10; + + // [2] 고급 필터 + const advancedWhere = filterColumns({ + table: vendorResponseCBEView, + filters: input.filters ?? [], + joinOperator: input.joinOperator ?? "and", + }); + + // [3] 글로벌 검색 + let globalWhere; + if (input.search) { + const s = `%${input.search}%`; + globalWhere = or( + sql`${vendorResponseCBEView.rfqCode} ILIKE ${s}`, + sql`${vendorResponseCBEView.projectCode} ILIKE ${s}`, + sql`${vendorResponseCBEView.projectName} ILIKE ${s}`, + sql`${vendorResponseCBEView.totalPrice}::text ILIKE ${s}` + ); + } + + // [4] DECLINED 상태 제외 (거절된 응답은 표시하지 않음) + // const notDeclined = ne(vendorResponseCBEView.responseStatus, "DECLINED"); + + // [5] 최종 where 조건 + const finalWhere = and( + eq(vendorResponseCBEView.vendorId, vendorId), // vendorId로 필터링 + isNotNull(vendorResponseCBEView.commercialCreatedAt), + // notDeclined, + advancedWhere ?? undefined, + globalWhere ?? undefined + ); + + // [6] 정렬 + const orderBy = input.sort?.length + ? input.sort.map((s) => { + // vendorResponseCBEView 컬럼 중 정렬 대상이 되는 것만 매핑 + const col = (vendorResponseCBEView as any)[s.id]; + return s.desc ? desc(col) : asc(col); + }) + : [desc(vendorResponseCBEView.rfqDueDate)]; // 기본 정렬은 RFQ 마감일 내림차순 + + // [7] 메인 SELECT + const [rows, total] = await db.transaction(async (tx) => { + const data = await tx + .select({ + // 기본 식별 정보 + responseId: vendorResponseCBEView.responseId, + vendorId: vendorResponseCBEView.vendorId, + rfqId: vendorResponseCBEView.rfqId, + + // 협력업체 정보 + vendorName: vendorResponseCBEView.vendorName, + vendorCode: vendorResponseCBEView.vendorCode, + vendorStatus: vendorResponseCBEView.vendorStatus, + + // RFQ 정보 + rfqCode: vendorResponseCBEView.rfqCode, + rfqDescription: vendorResponseCBEView.rfqDescription, + rfqDueDate: vendorResponseCBEView.rfqDueDate, + rfqStatus: vendorResponseCBEView.rfqStatus, + rfqType: vendorResponseCBEView.rfqType, + + // 프로젝트 정보 + projectId: vendorResponseCBEView.projectId, + projectCode: vendorResponseCBEView.projectCode, + projectName: vendorResponseCBEView.projectName, + + // 응답 상태 정보 + responseStatus: vendorResponseCBEView.responseStatus, + responseNotes: vendorResponseCBEView.notes, + respondedAt: vendorResponseCBEView.respondedAt, + respondedBy: vendorResponseCBEView.respondedBy, + + // 상업 응답 정보 + commercialResponseId: vendorResponseCBEView.commercialResponseId, + commercialResponseStatus: vendorResponseCBEView.commercialResponseStatus, + totalPrice: vendorResponseCBEView.totalPrice, + currency: vendorResponseCBEView.currency, + paymentTerms: vendorResponseCBEView.paymentTerms, + incoterms: vendorResponseCBEView.incoterms, + deliveryPeriod: vendorResponseCBEView.deliveryPeriod, + warrantyPeriod: vendorResponseCBEView.warrantyPeriod, + validityPeriod: vendorResponseCBEView.validityPeriod, + commercialNotes: vendorResponseCBEView.commercialNotes, + + // 첨부파일 카운트 + attachmentCount: vendorResponseCBEView.attachmentCount, + commercialAttachmentCount: vendorResponseCBEView.commercialAttachmentCount, + technicalAttachmentCount: vendorResponseCBEView.technicalAttachmentCount, + }) + .from(vendorResponseCBEView) + .where(finalWhere) + .orderBy(...orderBy) + .offset(offset) + .limit(limit); + + const [{ count }] = await tx + .select({ count: sql<number>`count(*)`.as("count") }) + .from(vendorResponseCBEView) + .where(finalWhere); + + return [data, Number(count)]; + }); + + if (!rows.length) { + return { data: [], pageCount: 0, total: 0 }; + } + + // [8] RFQ ID 목록 추출 + const distinctRfqIds = [...new Set(rows.map((r) => r.rfqId))]; + const distinctResponseIds = [...new Set(rows.map((r) => r.responseId))]; + const distinctCommercialResponseIds = [...new Set(rows.filter(r => r.commercialResponseId).map((r) => r.commercialResponseId!))]; + + // [9] CBE 평가 관련 코멘트 조회 + const commentsAll = await db + .select({ + id: rfqComments.id, + commentText: rfqComments.commentText, + rfqId: rfqComments.rfqId, + cbeId: rfqComments.cbeId, + createdAt: rfqComments.createdAt, + commentedBy: rfqComments.commentedBy, + }) + .from(rfqComments) + .innerJoin( + vendorResponses, + eq(vendorResponses.id, rfqComments.cbeId) + ) + .where( + and( + isNotNull(rfqComments.cbeId), + eq(rfqComments.vendorId, vendorId), + inArray(rfqComments.rfqId, distinctRfqIds) + ) + ); + + // rfqId별 코멘트 그룹화 + const commentsByRfqId = new Map<number, any[]>(); + for (const comment of commentsAll) { + const rfqId = comment.rfqId!; + if (!commentsByRfqId.has(rfqId)) { + commentsByRfqId.set(rfqId, []); + } + commentsByRfqId.get(rfqId)!.push({ + id: comment.id, + commentText: comment.commentText, + rfqId: comment.rfqId, + cbeId: comment.cbeId, + createdAt: comment.createdAt, + commentedBy: comment.commentedBy, + }); + } + + // [10] 첨부 파일 조회 - 일반 응답 첨부파일 + const responseAttachments = await db + .select({ + id: vendorResponseAttachments.id, + fileName: vendorResponseAttachments.fileName, + filePath: vendorResponseAttachments.filePath, + responseId: vendorResponseAttachments.responseId, + fileType: vendorResponseAttachments.fileType, + attachmentType: vendorResponseAttachments.attachmentType, + description: vendorResponseAttachments.description, + uploadedAt: vendorResponseAttachments.uploadedAt, + uploadedBy: vendorResponseAttachments.uploadedBy, + }) + .from(vendorResponseAttachments) + .where( + and( + inArray(vendorResponseAttachments.responseId, distinctResponseIds), + isNotNull(vendorResponseAttachments.responseId) + ) + ); + + // [11] 첨부 파일 조회 - 상업 응답 첨부파일 + const commercialResponseAttachments = await db + .select({ + id: vendorResponseAttachments.id, + fileName: vendorResponseAttachments.fileName, + filePath: vendorResponseAttachments.filePath, + commercialResponseId: vendorResponseAttachments.commercialResponseId, + fileType: vendorResponseAttachments.fileType, + attachmentType: vendorResponseAttachments.attachmentType, + description: vendorResponseAttachments.description, + uploadedAt: vendorResponseAttachments.uploadedAt, + uploadedBy: vendorResponseAttachments.uploadedBy, + }) + .from(vendorResponseAttachments) + .where( + and( + inArray(vendorResponseAttachments.commercialResponseId, distinctCommercialResponseIds), + isNotNull(vendorResponseAttachments.commercialResponseId) + ) + ); + + // [12] 첨부파일 그룹화 + // responseId별 첨부파일 맵 생성 + const filesByResponseId = new Map<number, any[]>(); + for (const file of responseAttachments) { + const responseId = file.responseId!; + if (!filesByResponseId.has(responseId)) { + filesByResponseId.set(responseId, []); + } + filesByResponseId.get(responseId)!.push({ + id: file.id, + fileName: file.fileName, + filePath: file.filePath, + fileType: file.fileType, + attachmentType: file.attachmentType, + description: file.description, + uploadedAt: file.uploadedAt, + uploadedBy: file.uploadedBy, + attachmentSource: 'response' + }); + } + + // commercialResponseId별 첨부파일 맵 생성 + const filesByCommercialResponseId = new Map<number, any[]>(); + for (const file of commercialResponseAttachments) { + const commercialResponseId = file.commercialResponseId!; + if (!filesByCommercialResponseId.has(commercialResponseId)) { + filesByCommercialResponseId.set(commercialResponseId, []); + } + filesByCommercialResponseId.get(commercialResponseId)!.push({ + id: file.id, + fileName: file.fileName, + filePath: file.filePath, + fileType: file.fileType, + attachmentType: file.attachmentType, + description: file.description, + uploadedAt: file.uploadedAt, + uploadedBy: file.uploadedBy, + attachmentSource: 'commercial' + }); + } + + // [13] 최종 데이터 병합 + const final = rows.map((row) => { + // 해당 응답의 모든 첨부파일 가져오기 + const responseFiles = filesByResponseId.get(row.responseId) || []; + const commercialFiles = row.commercialResponseId + ? filesByCommercialResponseId.get(row.commercialResponseId) || [] + : []; + + // 모든 첨부파일 병합 + const allFiles = [...responseFiles, ...commercialFiles]; + + return { + ...row, + rfqDueDate: row.rfqDueDate ? new Date(row.rfqDueDate) : null, + respondedAt: row.respondedAt ? new Date(row.respondedAt) : null, + comments: commentsByRfqId.get(row.rfqId) || [], + files: allFiles, + }; + }); + + const pageCount = Math.ceil(total / limit); + return { + data: final, + pageCount, + total + }; + }, + // 캐싱 키 & 옵션 + [`cbe-vendor-${vendorId}-${JSON.stringify(input)}`], + { + revalidate: 3600, + tags: [`cbe-vendor-${vendorId}`], + } + )(); +} + +export async function fetchCbeFiles(vendorId: number, rfqId: number) { + try { + // 1. 먼저 해당 RFQ와 벤더에 해당하는 CBE 평가 레코드를 찾습니다. + const cbeEval = await db + .select({ id: cbeEvaluations.id }) + .from(cbeEvaluations) + .where( + and( + eq(cbeEvaluations.rfqId, rfqId), + eq(cbeEvaluations.vendorId, vendorId) + ) + ) + .limit(1) + + if (!cbeEval.length) { + return { + files: [], + error: "해당 RFQ와 벤더에 대한 CBE 평가를 찾을 수 없습니다." + } + } + + const cbeId = cbeEval[0].id + + // 2. 관련 첨부 파일을 조회합니다. + // - commentId와 evaluationId는 null이어야 함 + // - rfqId와 vendorId가 일치해야 함 + // - cbeId가 위에서 찾은 CBE 평가 ID와 일치해야 함 + const files = await db + .select({ + id: rfqAttachments.id, + fileName: rfqAttachments.fileName, + filePath: rfqAttachments.filePath, + createdAt: rfqAttachments.createdAt + }) + .from(rfqAttachments) + .where( + and( + eq(rfqAttachments.rfqId, rfqId), + eq(rfqAttachments.vendorId, vendorId), + eq(rfqAttachments.cbeId, cbeId), + isNull(rfqAttachments.commentId), + isNull(rfqAttachments.evaluationId) + ) + ) + .orderBy(rfqAttachments.createdAt) + + return { + files, + cbeId + } + } catch (error) { + console.error("CBE 파일 조회 중 오류 발생:", error) + return { + files: [], + error: "CBE 파일을 가져오는 중 오류가 발생했습니다." + } + } +} + +export async function getAllCBE(input: GetCBESchema) { + return unstable_cache( + async () => { + // [1] 페이징 + const offset = ((input.page ?? 1) - 1) * (input.perPage ?? 10); + const limit = input.perPage ?? 10; + + // [2] 고급 필터 + const advancedWhere = filterColumns({ + table: vendorResponseCBEView, + filters: input.filters ?? [], + joinOperator: input.joinOperator ?? "and", + }); + + // [3] 글로벌 검색 + let globalWhere; + if (input.search) { + const s = `%${input.search}%`; + globalWhere = or( + sql`${vendorResponseCBEView.vendorName} ILIKE ${s}`, + sql`${vendorResponseCBEView.vendorCode} ILIKE ${s}`, + sql`${vendorResponseCBEView.rfqCode} ILIKE ${s}`, + sql`${vendorResponseCBEView.projectCode} ILIKE ${s}`, + sql`${vendorResponseCBEView.projectName} ILIKE ${s}`, + sql`${vendorResponseCBEView.totalPrice}::text ILIKE ${s}` + ); + } + + // [4] DECLINED 상태 제외 (거절된 업체는 표시하지 않음) + const notDeclined = ne(vendorResponseCBEView.responseStatus, "DECLINED"); + + // [5] rfqType 필터 추가 + const rfqTypeFilter = input.rfqType ? eq(vendorResponseCBEView.rfqType, input.rfqType) : undefined; + + // [6] 최종 where 조건 + const finalWhere = and( + notDeclined, + advancedWhere ?? undefined, + globalWhere ?? undefined, + rfqTypeFilter // 새로 추가된 rfqType 필터 + ); + + // [7] 정렬 + const orderBy = input.sort?.length + ? input.sort.map((s) => { + // vendorResponseCBEView 컬럼 중 정렬 대상이 되는 것만 매핑 + const col = (vendorResponseCBEView as any)[s.id]; + return s.desc ? desc(col) : asc(col); + }) + : [desc(vendorResponseCBEView.rfqId), asc(vendorResponseCBEView.vendorName)]; // 기본 정렬은 최신 RFQ 먼저, 그 다음 벤더명 + + // [8] 메인 SELECT + const [rows, total] = await db.transaction(async (tx) => { + const data = await tx + .select({ + // 기본 식별 정보 + responseId: vendorResponseCBEView.responseId, + vendorId: vendorResponseCBEView.vendorId, + rfqId: vendorResponseCBEView.rfqId, + + // 협력업체 정보 + vendorName: vendorResponseCBEView.vendorName, + vendorCode: vendorResponseCBEView.vendorCode, + vendorStatus: vendorResponseCBEView.vendorStatus, + + // RFQ 정보 + rfqCode: vendorResponseCBEView.rfqCode, + rfqDescription: vendorResponseCBEView.rfqDescription, + rfqDueDate: vendorResponseCBEView.rfqDueDate, + rfqStatus: vendorResponseCBEView.rfqStatus, + rfqType: vendorResponseCBEView.rfqType, + + // 프로젝트 정보 + projectId: vendorResponseCBEView.projectId, + projectCode: vendorResponseCBEView.projectCode, + projectName: vendorResponseCBEView.projectName, + + // 응답 상태 정보 + responseStatus: vendorResponseCBEView.responseStatus, + responseNotes: vendorResponseCBEView.notes, + respondedAt: vendorResponseCBEView.respondedAt, + respondedBy: vendorResponseCBEView.respondedBy, + + // 상업 응답 정보 + commercialResponseId: vendorResponseCBEView.commercialResponseId, + commercialResponseStatus: vendorResponseCBEView.commercialResponseStatus, + totalPrice: vendorResponseCBEView.totalPrice, + currency: vendorResponseCBEView.currency, + paymentTerms: vendorResponseCBEView.paymentTerms, + incoterms: vendorResponseCBEView.incoterms, + deliveryPeriod: vendorResponseCBEView.deliveryPeriod, + warrantyPeriod: vendorResponseCBEView.warrantyPeriod, + validityPeriod: vendorResponseCBEView.validityPeriod, + commercialNotes: vendorResponseCBEView.commercialNotes, + + // 첨부파일 카운트 + attachmentCount: vendorResponseCBEView.attachmentCount, + commercialAttachmentCount: vendorResponseCBEView.commercialAttachmentCount, + technicalAttachmentCount: vendorResponseCBEView.technicalAttachmentCount, + }) + .from(vendorResponseCBEView) + .where(finalWhere) + .orderBy(...orderBy) + .offset(offset) + .limit(limit); + + const [{ count }] = await tx + .select({ count: sql<number>`count(*)`.as("count") }) + .from(vendorResponseCBEView) + .where(finalWhere); + + return [data, Number(count)]; + }); + + if (!rows.length) { + return { data: [], pageCount: 0, total: 0 }; + } + + // [9] 고유한 rfqIds와 vendorIds 추출 - null 필터링 + const distinctVendorIds = [...new Set(rows.map((r) => r.vendorId).filter(Boolean))] as number[]; + const distinctRfqIds = [...new Set(rows.map((r) => r.rfqId).filter(Boolean))] as number[]; + const distinctResponseIds = [...new Set(rows.map((r) => r.responseId).filter(Boolean))] as number[]; + const distinctCommercialResponseIds = [...new Set(rows.filter(r => r.commercialResponseId).map((r) => r.commercialResponseId!))]; + + // [10] CBE 평가 관련 코멘트 조회 + const commentsConditions = [isNotNull(rfqComments.cbeId)]; + + // 배열이 비어있지 않을 때만 조건 추가 + if (distinctRfqIds.length > 0) { + commentsConditions.push(inArray(rfqComments.rfqId, distinctRfqIds)); + } + + if (distinctVendorIds.length > 0) { + commentsConditions.push(inArray(rfqComments.vendorId, distinctVendorIds)); + } + + const commentsAll = await db + .select({ + id: rfqComments.id, + commentText: rfqComments.commentText, + vendorId: rfqComments.vendorId, + rfqId: rfqComments.rfqId, + cbeId: rfqComments.cbeId, + createdAt: rfqComments.createdAt, + commentedBy: rfqComments.commentedBy, + }) + .from(rfqComments) + .innerJoin( + vendorResponses, + eq(vendorResponses.id, rfqComments.cbeId) + ) + .where(and(...commentsConditions)); + + // [11] 복합 키(rfqId-vendorId)별 코멘트 그룹화 + const commentsByCompositeKey = new Map<string, any[]>(); + for (const comment of commentsAll) { + if (!comment.rfqId || !comment.vendorId) continue; + + const compositeKey = `${comment.rfqId}-${comment.vendorId}`; + if (!commentsByCompositeKey.has(compositeKey)) { + commentsByCompositeKey.set(compositeKey, []); + } + commentsByCompositeKey.get(compositeKey)!.push({ + id: comment.id, + commentText: comment.commentText, + vendorId: comment.vendorId, + cbeId: comment.cbeId, + createdAt: comment.createdAt, + commentedBy: comment.commentedBy, + }); + } + + // [12] 첨부 파일 조회 - 일반 응답 첨부파일 + const responseAttachments = await db + .select({ + id: vendorResponseAttachments.id, + fileName: vendorResponseAttachments.fileName, + filePath: vendorResponseAttachments.filePath, + responseId: vendorResponseAttachments.responseId, + fileType: vendorResponseAttachments.fileType, + attachmentType: vendorResponseAttachments.attachmentType, + description: vendorResponseAttachments.description, + uploadedAt: vendorResponseAttachments.uploadedAt, + uploadedBy: vendorResponseAttachments.uploadedBy, + }) + .from(vendorResponseAttachments) + .where( + and( + inArray(vendorResponseAttachments.responseId, distinctResponseIds), + isNotNull(vendorResponseAttachments.responseId) + ) + ); + + // [13] 첨부 파일 조회 - 상업 응답 첨부파일 + const commercialResponseAttachments = await db + .select({ + id: vendorResponseAttachments.id, + fileName: vendorResponseAttachments.fileName, + filePath: vendorResponseAttachments.filePath, + commercialResponseId: vendorResponseAttachments.commercialResponseId, + fileType: vendorResponseAttachments.fileType, + attachmentType: vendorResponseAttachments.attachmentType, + description: vendorResponseAttachments.description, + uploadedAt: vendorResponseAttachments.uploadedAt, + uploadedBy: vendorResponseAttachments.uploadedBy, + }) + .from(vendorResponseAttachments) + .where( + and( + inArray(vendorResponseAttachments.commercialResponseId, distinctCommercialResponseIds), + isNotNull(vendorResponseAttachments.commercialResponseId) + ) + ); + + // [14] 첨부파일 그룹화 + // responseId별 첨부파일 맵 생성 + const filesByResponseId = new Map<number, any[]>(); + for (const file of responseAttachments) { + const responseId = file.responseId!; + if (!filesByResponseId.has(responseId)) { + filesByResponseId.set(responseId, []); + } + filesByResponseId.get(responseId)!.push({ + id: file.id, + fileName: file.fileName, + filePath: file.filePath, + fileType: file.fileType, + attachmentType: file.attachmentType, + description: file.description, + uploadedAt: file.uploadedAt, + uploadedBy: file.uploadedBy, + attachmentSource: 'response' + }); + } + + // commercialResponseId별 첨부파일 맵 생성 + const filesByCommercialResponseId = new Map<number, any[]>(); + for (const file of commercialResponseAttachments) { + const commercialResponseId = file.commercialResponseId!; + if (!filesByCommercialResponseId.has(commercialResponseId)) { + filesByCommercialResponseId.set(commercialResponseId, []); + } + filesByCommercialResponseId.get(commercialResponseId)!.push({ + id: file.id, + fileName: file.fileName, + filePath: file.filePath, + fileType: file.fileType, + attachmentType: file.attachmentType, + description: file.description, + uploadedAt: file.uploadedAt, + uploadedBy: file.uploadedBy, + attachmentSource: 'commercial' + }); + } + + // [15] 복합 키(rfqId-vendorId)별 첨부파일 맵 생성 + const filesByCompositeKey = new Map<string, any[]>(); + + // responseId -> rfqId-vendorId 매핑 생성 + const responseIdToCompositeKey = new Map<number, string>(); + for (const row of rows) { + if (row.responseId) { + responseIdToCompositeKey.set(row.responseId, `${row.rfqId}-${row.vendorId}`); + } + if (row.commercialResponseId) { + responseIdToCompositeKey.set(row.commercialResponseId, `${row.rfqId}-${row.vendorId}`); + } + } + + // responseId별 첨부파일을 복합 키별로 그룹화 + for (const [responseId, files] of filesByResponseId.entries()) { + const compositeKey = responseIdToCompositeKey.get(responseId); + if (compositeKey) { + if (!filesByCompositeKey.has(compositeKey)) { + filesByCompositeKey.set(compositeKey, []); + } + filesByCompositeKey.get(compositeKey)!.push(...files); + } + } + + // commercialResponseId별 첨부파일을 복합 키별로 그룹화 + for (const [commercialResponseId, files] of filesByCommercialResponseId.entries()) { + const compositeKey = responseIdToCompositeKey.get(commercialResponseId); + if (compositeKey) { + if (!filesByCompositeKey.has(compositeKey)) { + filesByCompositeKey.set(compositeKey, []); + } + filesByCompositeKey.get(compositeKey)!.push(...files); + } + } + + // [16] 최종 데이터 병합 + const final = rows.map((row) => { + const compositeKey = `${row.rfqId}-${row.vendorId}`; + + return { + ...row, + rfqDueDate: row.rfqDueDate ? new Date(row.rfqDueDate) : null, + respondedAt: row.respondedAt ? new Date(row.respondedAt) : null, + comments: commentsByCompositeKey.get(compositeKey) || [], + files: filesByCompositeKey.get(compositeKey) || [], + }; + }); + + const pageCount = Math.ceil(total / limit); + return { + data: final, + pageCount, + total + }; + }, + // 캐싱 키 & 옵션 + [`all-cbe-vendors-${JSON.stringify(input)}`], + { + revalidate: 3600, + tags: ["all-cbe-vendors"], + } + )(); }
\ No newline at end of file |