// src/lib/tasks/service.ts "use server"; // Next.js 서버 액션에서 직접 import하려면 (선택) import { revalidatePath, revalidateTag, unstable_noStore } from "next/cache"; import db from "@/db/db"; 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 { 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"; 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 { 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 { sendEmail } from "../mail/sendEmail"; import { projects } from "@/db/schema/projects"; import { items } from "@/db/schema/items"; import * as z from "zod" import { users } from "@/db/schema/users"; interface InviteVendorsInput { rfqId: number vendorIds: number[] rfqType: RfqType } /* ----------------------------------------------------- 1) 조회 관련 ----------------------------------------------------- */ /** * 복잡한 조건으로 Rfq 목록을 조회 (+ pagination) 하고, * 총 개수에 따라 pageCount를 계산해서 리턴. * Next.js의 unstable_cache를 사용해 일정 시간 캐시. */ export async function getRfqs(input: GetRfqsSchema) { return unstable_cache( async () => { try { const offset = (input.page - 1) * input.perPage; // const advancedTable = input.flags.includes("advancedTable"); const advancedTable = true; // advancedTable 모드면 filterColumns()로 where 절 구성 const advancedWhere = filterColumns({ table: rfqsView, filters: input.filters, joinOperator: input.joinOperator, }); let globalWhere if (input.search) { const s = `%${input.search}%` globalWhere = or(ilike(rfqsView.rfqCode, s), ilike(rfqsView.projectCode, s) , ilike(rfqsView.projectName, s), ilike(rfqsView.dueDate, s), ilike(rfqsView.status, s) ) // 필요시 여러 칼럼 OR조건 (status, priority, etc) } let rfqTypeWhere; if (input.rfqType) { rfqTypeWhere = eq(rfqsView.rfqType, input.rfqType); } let whereConditions = []; if (advancedWhere) whereConditions.push(advancedWhere); if (globalWhere) whereConditions.push(globalWhere); if (rfqTypeWhere) whereConditions.push(rfqTypeWhere); // 조건이 있을 때만 and() 사용 const finalWhere = whereConditions.length > 0 ? and(...whereConditions) : undefined; const orderBy = input.sort.length > 0 ? input.sort.map((item) => item.desc ? desc(rfqsView[item.id]) : asc(rfqsView[item.id]) ) : [asc(rfqsView.createdAt)]; // 트랜잭션 내부에서 Repository 호출 const { data, total } = await db.transaction(async (tx) => { const data = await selectRfqs(tx, { where: finalWhere, orderBy, offset, limit: input.perPage, }); const total = await countRfqs(tx, finalWhere); return { data, total }; }); const pageCount = Math.ceil(total / input.perPage); return { data, pageCount }; } catch (err) { console.error("getRfqs 에러:", err); // 자세한 에러 로깅 // 에러 발생 시 디폴트 return { data: [], pageCount: 0 }; } }, [JSON.stringify(input)], { revalidate: 3600, tags: [`rfqs-${input.rfqType}`], } )(); } /** Status별 개수 */ export async function getRfqStatusCounts(rfqType: RfqType = RfqType.PURCHASE) { return unstable_cache( async () => { try { const initial: Record = { DRAFT: 0, PUBLISHED: 0, EVALUATION: 0, AWARDED: 0, }; const result = await db.transaction(async (tx) => { // rfqType을 기준으로 필터링 추가 const rows = await groupByStatus(tx, rfqType); return rows.reduce>((acc, { status, count }) => { acc[status] = count; return acc; }, initial); }); return result; } catch (err) { return {} as Record; } }, [`rfq-status-counts-${rfqType}`], // 캐싱 키에 rfqType 추가 { revalidate: 3600, } )(); } /* ----------------------------------------------------- 2) 생성(Create) ----------------------------------------------------- */ /** * Rfq 생성 후, (가장 오래된 Rfq 1개) 삭제로 * 전체 Rfq 개수를 고정 */ export async function createRfq(input: CreateRfqSchema) { console.log(input.createdBy, "input.createdBy") unstable_noStore(); // Next.js 서버 액션 캐싱 방지 try { await db.transaction(async (tx) => { // 새 Rfq 생성 const [newTask] = await insertRfq(tx, { rfqCode: input.rfqCode, projectId: input.projectId || null, description: input.description || null, dueDate: input.dueDate, status: input.status, rfqType: input.rfqType, // rfqType 추가 createdBy: input.createdBy, }); return newTask; }); // 캐시 무효화 revalidateTag(`rfqs-${input.rfqType}`); revalidateTag(`rfq-status-counts-${input.rfqType}`); return { data: null, error: null }; } catch (err) { return { data: null, error: getErrorMessage(err) }; } } /* ----------------------------------------------------- 3) 업데이트 ----------------------------------------------------- */ /** 단건 업데이트 */ export async function modifyRfq(input: UpdateRfqSchema & { id: number }) { unstable_noStore(); try { const data = await db.transaction(async (tx) => { const [res] = await updateRfq(tx, input.id, { rfqCode: input.rfqCode, projectId: input.projectId || null, dueDate: input.dueDate, rfqType: input.rfqType, status: input.status as "DRAFT" | "PUBLISHED" | "EVALUATION" | "AWARDED", createdBy: input.createdBy, }); return res; }); revalidateTag("rfqs"); if (data.status === input.status) { revalidateTag("rfqs-status-counts"); } return { data: null, error: null }; } catch (err) { return { data: null, error: getErrorMessage(err) }; } } export async function modifyRfqs(input: { ids: number[]; status?: Rfq["status"]; dueDate?: Date }) { unstable_noStore(); try { const data = await db.transaction(async (tx) => { const [res] = await updateRfqs(tx, input.ids, { status: input.status, dueDate: input.dueDate, }); return res; }); revalidateTag("rfqs"); if (data.status === input.status) { revalidateTag("rfq-status-counts"); } return { data: null, error: null }; } catch (err) { return { data: null, error: getErrorMessage(err) }; } } /* ----------------------------------------------------- 4) 삭제 ----------------------------------------------------- */ /** 단건 삭제 */ export async function removeRfq(input: { id: number }) { unstable_noStore(); try { await db.transaction(async (tx) => { // 삭제 await deleteRfqById(tx, input.id); // 바로 새 Rfq 생성 }); revalidateTag("rfqs"); revalidateTag("rfq-status-counts"); return { data: null, error: null }; } catch (err) { return { data: null, error: getErrorMessage(err) }; } } /** 복수 삭제 */ export async function removeRfqs(input: { ids: number[] }) { unstable_noStore(); try { await db.transaction(async (tx) => { // 삭제 await deleteRfqsByIds(tx, input.ids); }); revalidateTag("rfqs"); revalidateTag("rfq-status-counts"); return { data: null, error: null }; } catch (err) { return { data: null, error: getErrorMessage(err) }; } } // 삭제를 위한 입력 스키마 const deleteRfqItemSchema = z.object({ id: z.number().int(), rfqId: z.number().int(), rfqType: z.nativeEnum(RfqType).default(RfqType.PURCHASE), }); type DeleteRfqItemSchema = z.infer; /** * RFQ 아이템 삭제 함수 */ export async function deleteRfqItem(input: DeleteRfqItemSchema) { unstable_noStore(); // Next.js 서버 액션 캐싱 방지 try { // 삭제 작업 수행 await db .delete(rfqItems) .where( and( eq(rfqItems.id, input.id), eq(rfqItems.rfqId, input.rfqId) ) ); console.log(`Deleted RFQ item: ${input.id} for RFQ ${input.rfqId}`); // 캐시 무효화 revalidateTag("rfq-items"); revalidateTag(`rfqs-${input.rfqType}`); revalidateTag(`rfq-${input.rfqId}`); return { data: null, error: null }; } catch (err) { console.error("Error in deleteRfqItem:", err); return { data: null, error: getErrorMessage(err) }; } } // createRfqItem 함수 수정 (id 파라미터 추가) export async function createRfqItem(input: CreateRfqItemSchema & { id?: number }) { unstable_noStore(); try { // DB 트랜잭션 await db.transaction(async (tx) => { // id가 전달되었으면 해당 id로 업데이트, 그렇지 않으면 기존 로직대로 진행 if (input.id) { // 기존 아이템 업데이트 await tx .update(rfqItems) .set({ description: input.description ?? null, quantity: input.quantity ?? 1, uom: input.uom ?? "", updatedAt: new Date(), }) .where(eq(rfqItems.id, input.id)); console.log(`Updated RFQ item with id: ${input.id}`); } else { // 기존 로직: 같은 itemCode로 이미 존재하는지 확인 후 업데이트/생성 const existingItems = await tx .select() .from(rfqItems) .where( and( eq(rfqItems.rfqId, input.rfqId), eq(rfqItems.itemCode, input.itemCode) ) ); if (existingItems.length > 0) { // 이미 존재하는 경우 업데이트 const existingItem = existingItems[0]; await tx .update(rfqItems) .set({ description: input.description ?? null, quantity: input.quantity ?? 1, uom: input.uom ?? "", updatedAt: new Date(), }) .where(eq(rfqItems.id, existingItem.id)); console.log(`Updated existing RFQ item: ${existingItem.id} for RFQ ${input.rfqId}, Item ${input.itemCode}`); } else { // 존재하지 않는 경우 새로 생성 const [newItem] = await insertRfqItem(tx, { rfqId: input.rfqId, itemCode: input.itemCode, description: input.description ?? null, quantity: input.quantity ?? 1, uom: input.uom ?? "", }); console.log(`Created new RFQ item for RFQ ${input.rfqId}, Item ${input.itemCode}`); } } }); // 캐시 무효화 revalidateTag("rfq-items"); revalidateTag(`rfqs-${input.rfqType}`); revalidateTag(`rfq-${input.rfqId}`); return { data: null, error: null }; } catch (err) { console.error("Error in createRfqItem:", err); return { data: null, error: getErrorMessage(err) }; } } /** * 서버 액션: 파일 첨부/삭제 처리 * @param rfqId RFQ ID * @param removedExistingIds 기존 첨부 중 삭제된 record ID 배열 * @param newFiles 새로 업로드된 파일 (File[]) - Next.js server action에서 * @param vendorId (optional) 업로더가 vendor인지 구분 */ export async function processRfqAttachments(args: { rfqId: number; removedExistingIds?: number[]; newFiles?: File[]; vendorId?: number | null; rfqType?: RfqType | null; }) { const { rfqId, removedExistingIds = [], newFiles = [], vendorId = null } = args; try { // 1) 삭제된 기존 첨부: DB + 파일시스템에서 제거 if (removedExistingIds.length > 0) { // 1-1) DB에서 filePath 조회 const rows = await db .select({ id: rfqAttachments.id, filePath: rfqAttachments.filePath }) .from(rfqAttachments) .where(inArray(rfqAttachments.id, removedExistingIds)); // 1-2) DB 삭제 await db .delete(rfqAttachments) .where(inArray(rfqAttachments.id, removedExistingIds)); // 1-3) 파일 삭제 for (const row of rows) { // filePath: 예) "/rfq/123/...xyz" const absolutePath = path.join( process.cwd(), "public", row.filePath.replace(/^\/+/, "") // 슬래시 제거 ); try { await fs.unlink(absolutePath); } catch (err) { console.error("File remove error:", err); } } } // 2) 새 파일 업로드 if (newFiles.length > 0) { const rfqDir = path.join("public", "rfq", String(rfqId)); // 폴더 없으면 생성 await fs.mkdir(rfqDir, { recursive: true }); for (const file of newFiles) { // 2-1) File -> Buffer const ab = await file.arrayBuffer(); const buffer = Buffer.from(ab); // 2-2) 고유 파일명 const uniqueName = `${randomUUID()}-${file.name}`; // 예) "rfq/123/xxx" const relativePath = path.join("rfq", String(rfqId), uniqueName); const absolutePath = path.join("public", relativePath); // 2-3) 파일 저장 await fs.writeFile(absolutePath, buffer); // 2-4) DB Insert await db.insert(rfqAttachments).values({ rfqId, vendorId, fileName: file.name, filePath: "/" + relativePath.replace(/\\/g, "/"), // (Windows 경로 대비) }); } } const [countRow] = await db .select({ cnt: sql`count(*)`.as("cnt") }) .from(rfqAttachments) .where(eq(rfqAttachments.rfqId, rfqId)); const newCount = countRow?.cnt ?? 0; // 3) revalidateTag 등 캐시 무효화 revalidateTag("rfq-attachments"); revalidateTag(`rfqs-${args.rfqType}`) return { ok: true, updatedItemCount: newCount }; } catch (error) { console.error("processRfqAttachments error:", error); return { ok: false, error: String(error) }; } } export async function fetchRfqAttachments(rfqId: number) { // DB select const rows = await db .select() .from(rfqAttachments) .where(eq(rfqAttachments.rfqId, rfqId)) // rows: { id, fileName, filePath, createdAt, vendorId, ... } // 필요 없는 필드는 omit하거나 transform 가능 return rows.map((row) => ({ id: row.id, fileName: row.fileName, filePath: row.filePath, createdAt: row.createdAt, // or string vendorId: row.vendorId, size: undefined, // size를 DB에 저장하지 않았다면 })) } export async function fetchRfqItems(rfqId: number) { // DB select const rows = await db .select() .from(rfqItems) .where(eq(rfqItems.rfqId, rfqId)) // rows: { id, fileName, filePath, createdAt, vendorId, ... } // 필요 없는 필드는 omit하거나 transform 가능 return rows.map((row) => ({ // id: row.id, itemCode: row.itemCode, description: row.description, quantity: row.quantity, uom: row.uom, })) } export const findRfqById = async (id: number): Promise => { try { logger.info({ id }, 'Fetching user by ID'); const rfq = await getRfqById(id); if (!rfq) { logger.warn({ id }, 'User not found'); } else { logger.debug({ rfq }, 'User fetched successfully'); } return rfq; } catch (error) { logger.error({ error }, 'Error fetching user by ID'); throw new Error('Failed to fetch user'); } }; export async function getMatchedVendors(input: GetMatchedVendorsSchema, rfqId: number) { return unstable_cache( async () => { // ───────────────────────────────────────────────────── // 1) rfq_items에서 distinct itemCode // ───────────────────────────────────────────────────── const itemRows = await db .select({ code: rfqItems.itemCode }) .from(rfqItems) .where(eq(rfqItems.rfqId, rfqId)) .groupBy(rfqItems.itemCode) const itemCodes = itemRows.map((r) => r.code) const itemCount = itemCodes.length if (itemCount === 0) { return { data: [], pageCount: 0 } } // ───────────────────────────────────────────────────── // 2) vendorPossibleItems에서 모든 itemCodes를 보유한 vendor // ───────────────────────────────────────────────────── const inList = itemCodes.map((c) => `'${c}'`).join(",") const sqlVendorIds = await db.execute( sql` SELECT vpi.vendor_id AS "vendorId" FROM ${vendorPossibleItems} vpi WHERE vpi.item_code IN (${sql.raw(inList)}) GROUP BY vpi.vendor_id HAVING COUNT(DISTINCT vpi.item_code) = ${itemCount} ` ) const vendorIdList = sqlVendorIds.rows.map((row: any) => +row.vendorId) if (vendorIdList.length === 0) { return { data: [], pageCount: 0 } } // ───────────────────────────────────────────────────── // 3) 필터/검색/정렬 // ───────────────────────────────────────────────────── const offset = ((input.page ?? 1) - 1) * (input.perPage ?? 10) const limit = input.perPage ?? 10 // (가) 커스텀 필터 // 여기서는 "뷰(vendorRfqView)"의 컬럼들에 대해 필터합니다. const advancedWhere = filterColumns({ // 테이블이 아니라 "뷰"를 넘길 수도 있고, // 혹은 columns 객체(연결된 모든 컬럼)로 넘겨도 됩니다. table: vendorRfqView, filters: input.filters ?? [], joinOperator: input.joinOperator ?? "and", }) // (나) 글로벌 검색 let globalWhere if (input.search) { const s = `%${input.search}%` globalWhere = or( sql`${vendorRfqView.vendorName} ILIKE ${s}`, sql`${vendorRfqView.vendorCode} ILIKE ${s}`, sql`${vendorRfqView.email} ILIKE ${s}` ) } // (다) 최종 where // vendorId가 vendorIdList 내에 있어야 하고, // 특정 rfqId(뷰에 담긴 값)도 일치해야 함. const finalWhere = and( inArray(vendorRfqView.vendorId, vendorIdList), // 아래 라인은 rfq에 초대된 벤더만 필터링하는 조건으로 추정되지만 // rfq 를 진행하기 전에도 벤더를 보여줘야 하므로 주석처리하겠습니다 // eq(vendorRfqView.rfqId, rfqId), advancedWhere, globalWhere ) // (라) 정렬 const orderBy = input.sort?.length ? input.sort.map((s) => { // "column id" -> vendorRfqView.* 중 하나 const col = (vendorRfqView as any)[s.id] return s.desc ? desc(col) : asc(col) }) : [asc(vendorRfqView.vendorId)] // ───────────────────────────────────────────────────── // 4) View에서 데이터 SELECT // ───────────────────────────────────────────────────── const [rows, total] = await db.transaction(async (tx) => { const data = await tx .select({ id: vendorRfqView.vendorId, vendorID: vendorRfqView.vendorId, vendorName: vendorRfqView.vendorName, vendorCode: vendorRfqView.vendorCode, address: vendorRfqView.address, country: vendorRfqView.country, email: vendorRfqView.email, website: vendorRfqView.website, vendorStatus: vendorRfqView.vendorStatus, // rfqVendorStatus와 rfqVendorUpdated는 나중에 정확한 데이터로 교체할 예정 rfqVendorStatus: vendorRfqView.rfqVendorStatus, rfqVendorUpdated: vendorRfqView.rfqVendorUpdated, }) .from(vendorRfqView) .where(finalWhere) .orderBy(...orderBy) .offset(offset) .limit(limit) // 중복 제거된 데이터 생성 const distinctData = Array.from( new Map(data.map(row => [row.id, row])).values() ) // 중복 제거된 총 개수 계산 const [{ count }] = await tx .select({ count: sql`count(DISTINCT ${vendorRfqView.vendorId})`.as("count") }) .from(vendorRfqView) .where(finalWhere) return [distinctData, Number(count)] }) // ───────────────────────────────────────────────────── // 4-1) 정확한 rfqVendorStatus와 rfqVendorUpdated 조회 // ───────────────────────────────────────────────────── const distinctVendorIds = [...new Set(rows.map((r) => r.id))] // vendorResponses 테이블에서 정확한 상태와 업데이트 시간 조회 const vendorStatuses = await db .select({ vendorId: vendorResponses.vendorId, status: vendorResponses.responseStatus, updatedAt: vendorResponses.updatedAt }) .from(vendorResponses) .where( and( inArray(vendorResponses.vendorId, distinctVendorIds), eq(vendorResponses.rfqId, rfqId) ) ) // vendorId별 상태정보 맵 생성 const statusMap = new Map() for (const vs of vendorStatuses) { statusMap.set(vs.vendorId, { status: vs.status, updatedAt: vs.updatedAt }) } // 정확한 상태 정보로 업데이트된 rows 생성 const updatedRows = rows.map(row => ({ ...row, rfqVendorStatus: statusMap.get(row.id)?.status || null, rfqVendorUpdated: statusMap.get(row.id)?.updatedAt || null })) // ───────────────────────────────────────────────────── // 5) 코멘트 조회: 기존과 동일 // ───────────────────────────────────────────────────── const commAll = await db .select() .from(rfqComments) .where( and( inArray(rfqComments.vendorId, distinctVendorIds), eq(rfqComments.rfqId, rfqId) ) ) const commByVendorId = new Map() // 먼저 모든 사용자 ID를 수집 const userIds = new Set(commAll.map(c => c.commentedBy)); const userIdsArray = Array.from(userIds); // Drizzle의 select 메서드를 사용하여 사용자 정보를 가져옴 const usersData = await db .select({ id: users.id, email: users.email, }) .from(users) .where(inArray(users.id, userIdsArray)); // 사용자 ID를 키로 하는 맵 생성 const userMap = new Map(); for (const user of usersData) { userMap.set(user.id, user); } // 댓글 정보를 벤더 ID별로 그룹화하고, 사용자 이메일 추가 for (const c of commAll) { const vid = c.vendorId! if (!commByVendorId.has(vid)) { commByVendorId.set(vid, []) } // 사용자 정보 가져오기 const user = userMap.get(c.commentedBy); const userEmail = user ? user.email : 'unknown@example.com'; // 사용자를 찾지 못한 경우 기본값 설정 commByVendorId.get(vid)!.push({ id: c.id, commentText: c.commentText, vendorId: c.vendorId, evaluationId: c.evaluationId, createdAt: c.createdAt, commentedBy: c.commentedBy, commentedByEmail: userEmail, // 이메일 추가 }) } // ───────────────────────────────────────────────────── // 6) rows에 comments 병합 // ───────────────────────────────────────────────────── const final = updatedRows.map((row) => ({ ...row, comments: commByVendorId.get(row.id) ?? [], })) // ───────────────────────────────────────────────────── // 7) 반환 // ───────────────────────────────────────────────────── const pageCount = Math.ceil(total / limit) return { data: final, pageCount } }, [JSON.stringify({ input, rfqId })], { revalidate: 3600, tags: ["rfq-vendors"] } )() } export async function inviteVendors(input: InviteVendorsInput) { unstable_noStore() // 서버 액션 캐싱 방지 try { const { rfqId, vendorIds } = input if (!rfqId || !Array.isArray(vendorIds) || vendorIds.length === 0) { throw new Error("Invalid input") } // DB 데이터 준비 및 첨부파일 처리를 위한 트랜잭션 const rfqData = await db.transaction(async (tx) => { // 2-A) RFQ 기본 정보 조회 const [rfqRow] = await tx .select({ rfqCode: rfqsView.rfqCode, description: rfqsView.description, projectCode: rfqsView.projectCode, projectName: rfqsView.projectName, dueDate: rfqsView.dueDate, createdBy: rfqsView.createdBy, }) .from(rfqsView) .where(eq(rfqsView.id, rfqId)) if (!rfqRow) { throw new Error(`RFQ #${rfqId} not found`) } // 2-B) 아이템 목록 조회 const items = await tx .select({ itemCode: rfqItems.itemCode, description: rfqItems.description, quantity: rfqItems.quantity, uom: rfqItems.uom, }) .from(rfqItems) .where(eq(rfqItems.rfqId, rfqId)) // 2-C) 첨부파일 목록 조회 const attachRows = await tx .select({ id: rfqAttachments.id, fileName: rfqAttachments.fileName, filePath: rfqAttachments.filePath, }) .from(rfqAttachments) .where( and( eq(rfqAttachments.rfqId, rfqId), isNull(rfqAttachments.vendorId), isNull(rfqAttachments.evaluationId) ) ) const vendorRows = await tx .select({ id: vendors.id, email: vendors.email }) .from(vendors) .where(inArray(vendors.id, vendorIds)) // NodeMailer attachments 형식 맞추기 const attachments = [] for (const att of attachRows) { const absolutePath = path.join(process.cwd(), "public", att.filePath.replace(/^\/+/, "")) attachments.push({ path: absolutePath, filename: att.fileName, }) } return { rfqRow, items, vendorRows, attachments } }) 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 emailErrors = [] // 각 벤더에 대해 처리 for (const v of vendorRows) { if (!v.email) { continue // 이메일 없는 벤더 무시 } try { // DB 업데이트: 각 벤더 상태 별도 트랜잭션 await db.transaction(async (tx) => { // rfq_vendors upsert const existing = await tx .select() .from(vendorResponses) .where(and(eq(vendorResponses.rfqId, rfqId), eq(vendorResponses.vendorId, v.id))) if (existing.length > 0) { await tx .update(vendorResponses) .set({ responseStatus: "INVITED", updatedAt: new Date(), }) .where(eq(vendorResponses.id, existing[0].id)) } else { await tx.insert(vendorResponses).values({ rfqId, vendorId: v.id, responseStatus: "INVITED", }) } }) // 이메일 발송 (트랜잭션 외부) await sendEmail({ to: v.email, subject: `[RFQ ${rfqRow.rfqCode}] You are invited from Samgsung Heavy Industries!`, 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, }) } catch (err) { // 개별 벤더 처리 실패 로깅 console.error(`Failed to process vendor ${v.id}: ${getErrorMessage(err)}`) emailErrors.push({ vendorId: v.id, error: getErrorMessage(err) }) // 계속 진행 (다른 벤더 처리) } } // 최종적으로 RFQ 상태 업데이트 (별도 트랜잭션) try { await db.transaction(async (tx) => { await tx .update(rfqs) .set({ status: "PUBLISHED", updatedAt: new Date(), }) .where(eq(rfqs.id, rfqId)) console.log(`Updated RFQ #${rfqId} status to PUBLISHED`) }) // 캐시 무효화 revalidateTag("rfq-vendors") revalidateTag("cbe-vendors") revalidateTag("rfqs") revalidateTag(`rfqs-${input.rfqType}`) revalidateTag(`rfq-${rfqId}`) // 이메일 오류가 있었는지 확인 if (emailErrors.length > 0) { return { error: `일부 벤더에게 이메일 발송 실패 (${emailErrors.length}/${vendorRows.length}), RFQ 상태는 업데이트됨`, emailErrors } } return { error: null } } catch (err) { return { error: `RFQ 상태 업데이트 실패: ${getErrorMessage(err)}` } } } catch (err) { return { error: getErrorMessage(err) } } } /** * TBE용 평가 데이터 목록 조회 */ export async function getTBE(input: GetTBESchema, rfqId: 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: vendorTbeView, filters: input.filters ?? [], joinOperator: input.joinOperator ?? "and", }) // 3) 글로벌 검색 let globalWhere if (input.search) { const s = `%${input.search}%` globalWhere = or( sql`${vendorTbeView.vendorName} ILIKE ${s}`, sql`${vendorTbeView.vendorCode} ILIKE ${s}`, sql`${vendorTbeView.email} ILIKE ${s}` ) } // 4) REJECTED 아니거나 NULL const notRejected = or( ne(vendorTbeView.rfqVendorStatus, "REJECTED"), isNull(vendorTbeView.rfqVendorStatus) ) // 5) finalWhere const finalWhere = and( eq(vendorTbeView.rfqId, rfqId), notRejected, advancedWhere, globalWhere ) // 6) 정렬 const orderBy = input.sort?.length ? input.sort.map((s) => { const col = (vendorTbeView as any)[s.id] return s.desc ? desc(col) : asc(col) }) : [asc(vendorTbeView.vendorId)] // 7) 메인 SELECT const [rows, total] = await db.transaction(async (tx) => { const data = await tx .select({ // 원하는 컬럼들 id: vendorTbeView.vendorId, tbeId: vendorTbeView.tbeId, vendorId: vendorTbeView.vendorId, vendorName: vendorTbeView.vendorName, vendorCode: vendorTbeView.vendorCode, address: vendorTbeView.address, country: vendorTbeView.country, email: vendorTbeView.email, website: vendorTbeView.website, vendorStatus: vendorTbeView.vendorStatus, rfqId: vendorTbeView.rfqId, rfqCode: vendorTbeView.rfqCode, projectCode: vendorTbeView.projectCode, projectName: vendorTbeView.projectName, description: vendorTbeView.description, dueDate: vendorTbeView.dueDate, rfqVendorStatus: vendorTbeView.rfqVendorStatus, rfqVendorUpdated: vendorTbeView.rfqVendorUpdated, tbeResult: vendorTbeView.tbeResult, tbeNote: vendorTbeView.tbeNote, tbeUpdated: vendorTbeView.tbeUpdated, }) .from(vendorTbeView) .where(finalWhere) .orderBy(...orderBy) .offset(offset) .limit(limit) const [{ count }] = await tx .select({ count: sql`count(*)`.as("count") }) .from(vendorTbeView) .where(finalWhere) return [data, Number(count)] }) if (!rows.length) { return { data: [], pageCount: 0 } } // 8) Comments 조회 const distinctVendorIds = [...new Set(rows.map((r) => r.vendorId))] const commAll = await db .select({ id: rfqComments.id, commentText: rfqComments.commentText, vendorId: rfqComments.vendorId, evaluationId: rfqComments.evaluationId, createdAt: rfqComments.createdAt, commentedBy: rfqComments.commentedBy, evalType: rfqEvaluations.evalType, }) .from(rfqComments) .innerJoin( rfqEvaluations, and( eq(rfqEvaluations.id, rfqComments.evaluationId), eq(rfqEvaluations.evalType, "TBE") ) ) .where( and( isNotNull(rfqComments.evaluationId), eq(rfqComments.rfqId, rfqId), inArray(rfqComments.vendorId, distinctVendorIds) ) ) // 8-A) vendorId -> comments grouping const commByVendorId = new Map() for (const c of commAll) { const vid = c.vendorId! if (!commByVendorId.has(vid)) { commByVendorId.set(vid, []) } commByVendorId.get(vid)!.push({ id: c.id, commentText: c.commentText, vendorId: c.vendorId, evaluationId: c.evaluationId, createdAt: c.createdAt, commentedBy: c.commentedBy, }) } // 9) TBE 파일 조회 - vendorResponseAttachments로 대체 // Step 1: Get vendorResponses for the rfqId and vendorIds const responsesAll = await db .select({ id: vendorResponses.id, vendorId: vendorResponses.vendorId }) .from(vendorResponses) .where( and( eq(vendorResponses.rfqId, rfqId), inArray(vendorResponses.vendorId, distinctVendorIds) ) ); // Group responses by vendorId for later lookup const responsesByVendorId = new Map(); for (const resp of responsesAll) { if (!responsesByVendorId.has(resp.vendorId)) { responsesByVendorId.set(resp.vendorId, []); } responsesByVendorId.get(resp.vendorId)!.push(resp.id); } // Step 2: Get all responseIds const allResponseIds = responsesAll.map(r => r.id); // Step 3: Get technicalResponses for these responseIds const technicalResponsesAll = await db .select({ id: vendorTechnicalResponses.id, responseId: vendorTechnicalResponses.responseId }) .from(vendorTechnicalResponses) .where(inArray(vendorTechnicalResponses.responseId, allResponseIds)); // Create mapping from responseId to technicalResponseIds const technicalResponseIdsByResponseId = new Map(); for (const tr of technicalResponsesAll) { if (!technicalResponseIdsByResponseId.has(tr.responseId)) { technicalResponseIdsByResponseId.set(tr.responseId, []); } technicalResponseIdsByResponseId.get(tr.responseId)!.push(tr.id); } // Step 4: Get all technicalResponseIds const allTechnicalResponseIds = technicalResponsesAll.map(tr => tr.id); // Step 5: Get attachments for these technicalResponseIds const filesAll = await db .select({ id: vendorResponseAttachments.id, fileName: vendorResponseAttachments.fileName, filePath: vendorResponseAttachments.filePath, technicalResponseId: vendorResponseAttachments.technicalResponseId, fileType: vendorResponseAttachments.fileType, attachmentType: vendorResponseAttachments.attachmentType, description: vendorResponseAttachments.description, uploadedAt: vendorResponseAttachments.uploadedAt, uploadedBy: vendorResponseAttachments.uploadedBy }) .from(vendorResponseAttachments) .where( and( inArray(vendorResponseAttachments.technicalResponseId, allTechnicalResponseIds), isNotNull(vendorResponseAttachments.technicalResponseId) ) ); // Step 6: Create mapping from technicalResponseId to attachments const filesByTechnicalResponseId = new Map(); for (const file of filesAll) { // Skip if technicalResponseId is null (should never happen due to our filter above) if (file.technicalResponseId === null) continue; if (!filesByTechnicalResponseId.has(file.technicalResponseId)) { filesByTechnicalResponseId.set(file.technicalResponseId, []); } filesByTechnicalResponseId.get(file.technicalResponseId)!.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 }); } // Step 7: Create the final filesByVendorId map const filesByVendorId = new Map(); for (const [vendorId, responseIds] of responsesByVendorId.entries()) { filesByVendorId.set(vendorId, []); for (const responseId of responseIds) { const technicalResponseIds = technicalResponseIdsByResponseId.get(responseId) || []; for (const technicalResponseId of technicalResponseIds) { const files = filesByTechnicalResponseId.get(technicalResponseId) || []; filesByVendorId.get(vendorId)!.push(...files); } } } // 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) ?? [], })) const pageCount = Math.ceil(total / limit) return { data: final, pageCount } }, [JSON.stringify({ input, rfqId })], { revalidate: 3600, tags: ["tbe-vendors"], } )() } export async function getTBEforVendor(input: GetTBESchema, vendorId: number) { if (isNaN(vendorId) || vendorId === null || vendorId === undefined) { throw new Error("유효하지 않은 vendorId: 숫자 값이 필요합니다"); } return unstable_cache( async () => { // 1) 페이징 const offset = ((input.page ?? 1) - 1) * (input.perPage ?? 10) const limit = input.perPage ?? 10 // 2) 고급 필터 const advancedWhere = filterColumns({ table: vendorTbeView, filters: input.filters ?? [], joinOperator: input.joinOperator ?? "and", }) // 3) 글로벌 검색 let globalWhere if (input.search) { const s = `%${input.search}%` globalWhere = or( sql`${vendorTbeView.vendorName} ILIKE ${s}`, sql`${vendorTbeView.vendorCode} ILIKE ${s}`, sql`${vendorTbeView.email} ILIKE ${s}` ) } // 4) REJECTED 아니거나 NULL const notRejected = or( ne(vendorTbeView.rfqVendorStatus, "REJECTED"), isNull(vendorTbeView.rfqVendorStatus) ) // 5) finalWhere const finalWhere = and( isNotNull(vendorTbeView.tbeId), eq(vendorTbeView.vendorId, vendorId), notRejected, advancedWhere, globalWhere ) // 6) 정렬 const orderBy = input.sort?.length ? input.sort.map((s) => { const col = (vendorTbeView as any)[s.id] return s.desc ? desc(col) : asc(col) }) : [asc(vendorTbeView.vendorId)] // 7) 메인 SELECT const [rows, total] = await db.transaction(async (tx) => { const data = await tx .select({ // 원하는 컬럼들 id: vendorTbeView.vendorId, tbeId: vendorTbeView.tbeId, vendorId: vendorTbeView.vendorId, vendorName: vendorTbeView.vendorName, vendorCode: vendorTbeView.vendorCode, address: vendorTbeView.address, country: vendorTbeView.country, email: vendorTbeView.email, website: vendorTbeView.website, vendorStatus: vendorTbeView.vendorStatus, rfqId: vendorTbeView.rfqId, rfqCode: vendorTbeView.rfqCode, projectCode: vendorTbeView.projectCode, projectName: vendorTbeView.projectName, description: vendorTbeView.description, dueDate: vendorTbeView.dueDate, vendorResponseId: vendorTbeView.vendorResponseId, rfqVendorStatus: vendorTbeView.rfqVendorStatus, rfqVendorUpdated: vendorTbeView.rfqVendorUpdated, tbeResult: vendorTbeView.tbeResult, tbeNote: vendorTbeView.tbeNote, tbeUpdated: vendorTbeView.tbeUpdated, }) .from(vendorTbeView) .where(finalWhere) .orderBy(...orderBy) .offset(offset) .limit(limit) const [{ count }] = await tx .select({ count: sql`count(*)`.as("count") }) .from(vendorTbeView) .where(finalWhere) return [data, Number(count)] }) if (!rows.length) { return { data: [], pageCount: 0 } } // 8) Comments 조회 // - evaluationId != null && evalType = "TBE" // - => leftJoin(rfqEvaluations) or innerJoin const distinctVendorIds = [...new Set(rows.map((r) => r.vendorId))] const distinctTbeIds = [...new Set(rows.map((r) => r.tbeId).filter(Boolean))] // (A) 조인 방식 const commAll = await db .select({ id: rfqComments.id, commentText: rfqComments.commentText, vendorId: rfqComments.vendorId, evaluationId: rfqComments.evaluationId, createdAt: rfqComments.createdAt, commentedBy: rfqComments.commentedBy, evalType: rfqEvaluations.evalType, // (optional) }) .from(rfqComments) // evalType = 'TBE' .innerJoin( rfqEvaluations, and( eq(rfqEvaluations.id, rfqComments.evaluationId), eq(rfqEvaluations.evalType, "TBE") // ★ TBE만 ) ) .where( and( isNotNull(rfqComments.evaluationId), inArray(rfqComments.vendorId, distinctVendorIds) ) ) // 8-A) vendorId -> comments grouping const commByVendorId = new Map() for (const c of commAll) { const vid = c.vendorId! if (!commByVendorId.has(vid)) { commByVendorId.set(vid, []) } commByVendorId.get(vid)!.push({ id: c.id, commentText: c.commentText, vendorId: c.vendorId, evaluationId: c.evaluationId, createdAt: c.createdAt, commentedBy: c.commentedBy, }) } // 9) TBE 템플릿 파일 수 조회 const templateFiles = await db .select({ tbeId: rfqAttachments.evaluationId, fileCount: sql`count(*)`.as("file_count"), }) .from(rfqAttachments) .where( and( inArray(rfqAttachments.evaluationId, distinctTbeIds), isNull(rfqAttachments.vendorId), isNull(rfqAttachments.commentId) ) ) .groupBy(rfqAttachments.evaluationId) // tbeId -> fileCount 매핑 - null 체크 추가 const templateFileCountMap = new Map() for (const tf of templateFiles) { if (tf.tbeId !== null) { templateFileCountMap.set(tf.tbeId, Number(tf.fileCount)) } } // 10) TBE 응답 파일 확인 (각 tbeId + vendorId 조합에 대해) const tbeResponseFiles = await db .select({ tbeId: rfqAttachments.evaluationId, vendorId: rfqAttachments.vendorId, responseFileCount: sql`count(*)`.as("response_file_count"), }) .from(rfqAttachments) .where( and( inArray(rfqAttachments.evaluationId, distinctTbeIds), inArray(rfqAttachments.vendorId, distinctVendorIds), isNull(rfqAttachments.commentId) ) ) .groupBy(rfqAttachments.evaluationId, rfqAttachments.vendorId) // tbeId_vendorId -> hasResponse 매핑 - null 체크 추가 const tbeResponseMap = new Map() for (const rf of tbeResponseFiles) { if (rf.tbeId !== null && rf.vendorId !== null) { const key = `${rf.tbeId}_${rf.vendorId}` tbeResponseMap.set(key, Number(rf.responseFileCount)) } } // 11) 최종 합치기 const final = rows.map((row) => { const tbeId = row.tbeId const vendorId = row.vendorId // 템플릿 파일 수 const templateFileCount = tbeId !== null ? templateFileCountMap.get(tbeId) || 0 : 0 // 응답 파일 여부 const responseKey = tbeId !== null ? `${tbeId}_${vendorId}` : "" const responseFileCount = responseKey ? tbeResponseMap.get(responseKey) || 0 : 0 return { ...row, dueDate: row.dueDate ? new Date(row.dueDate) : null, comments: commByVendorId.get(row.vendorId) ?? [], templateFileCount, // 추가: 템플릿 파일 수 hasResponse: responseFileCount > 0, // 추가: 응답 파일 제출 여부 } }) const pageCount = Math.ceil(total / limit) return { data: final, pageCount } }, [JSON.stringify(input), String(vendorId)], // 캐싱 키에 packagesId 추가 { revalidate: 3600, tags: [`tbe-vendors-${vendorId}`], } )() } export async function inviteTbeVendorsAction(formData: FormData) { // 캐싱 방지 unstable_noStore() try { // 1) FormData에서 기본 필드 추출 const rfqId = Number(formData.get("rfqId")) 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) { throw new Error("Invalid input or no files attached.") } // /public/rfq/[rfqId] 경로 const uploadDir = path.join(process.cwd(), "public", "rfq", String(rfqId)) // DB 트랜잭션 await db.transaction(async (tx) => { // (A) RFQ 기본 정보 조회 const [rfqRow] = await tx .select({ rfqCode: vendorResponsesView.rfqCode, description: vendorResponsesView.rfqDescription, projectCode: vendorResponsesView.projectCode, projectName: vendorResponsesView.projectName, dueDate: vendorResponsesView.rfqDueDate, createdBy: vendorResponsesView.rfqCreatedBy, }) .from(vendorResponsesView) .where(eq(vendorResponsesView.rfqId, rfqId)) if (!rfqRow) { throw new Error(`RFQ #${rfqId} not found`) } // (B) RFQ 아이템 목록 const items = await tx .select({ itemCode: rfqItems.itemCode, description: rfqItems.description, quantity: rfqItems.quantity, uom: rfqItems.uom, }) .from(rfqItems) .where(eq(rfqItems.rfqId, rfqId)) // (C) 대상 벤더들 const vendorRows = await tx .select({ id: vendors.id, email: vendors.email }) .from(vendors) .where(sql`${vendors.id} in (${vendorIds})`) // (D) 모든 TBE 파일 저장 & 이후 벤더 초대 처리 // 파일은 한 번만 저장해도 되지만, 각 벤더별로 따로 저장/첨부가 필요하다면 루프를 돌려도 됨. // 여기서는 "모든 파일"을 RFQ-DIR에 저장 + "각 벤더"에는 동일 파일 목록을 첨부한다는 예시. const savedFiles = [] for (const file of tbeFiles) { const originalName = file.name || "tbe-sheet.xlsx" const savePath = path.join(uploadDir, originalName) // 파일 ArrayBuffer → Buffer 변환 후 저장 const arrayBuffer = await file.arrayBuffer() fs.writeFile(savePath, Buffer.from(arrayBuffer)) // 저장 경로 & 파일명 기록 savedFiles.push({ fileName: originalName, filePath: `/rfq/${rfqId}/${originalName}`, // public 이하 경로 absolutePath: savePath, }) } // (E) 각 벤더별로 TBE 평가 레코드, 초대 처리, 메일 발송 for (const v of vendorRows) { if (!v.email) { // 이메일 없는 경우 로직 (스킵 or throw) continue } // 1) TBE 평가 레코드 생성 const [evalRow] = await tx .insert(rfqEvaluations) .values({ rfqId, vendorId: v.id, evalType: "TBE", }) .returning({ id: rfqEvaluations.id }) // 2) rfqAttachments에 저장한 파일들을 기록 for (const sf of savedFiles) { await tx.insert(rfqAttachments).values({ rfqId, // vendorId: v.id, evaluationId: evalRow.id, fileName: sf.fileName, filePath: sf.filePath, }) } // 4) 메일 발송 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, })), }) } // 5) 캐시 무효화 revalidateTag("tbe-vendors") }) // 성공 return { error: null } } catch (err) { console.error("[inviteTbeVendorsAction] Error:", err) return { error: getErrorMessage(err) } } } ////partners export async function modifyRfqVendor(input: UpdateRfqVendorSchema) { unstable_noStore(); try { const data = await db.transaction(async (tx) => { const [res] = await updateRfqVendor(tx, input.id, { responseStatus: input.status, }); return res; }); revalidateTag("rfqs-vendor"); revalidateTag("rfq-vendors"); return { data: null, error: null }; } catch (err) { return { data: null, error: getErrorMessage(err) }; } } export async function createRfqCommentWithAttachments(params: { rfqId: number vendorId?: number | null commentText: string commentedBy: number evaluationId?: number | null cbeId?: number | null files?: File[] }) { const { rfqId, vendorId, commentText, commentedBy, evaluationId,cbeId, files } = params // 1) 새로운 코멘트 생성 const [insertedComment] = await db .insert(rfqComments) .values({ rfqId, vendorId: vendorId || null, commentText, commentedBy, evaluationId: evaluationId || null, cbeId: cbeId || null, }) .returning({ id: rfqComments.id, createdAt: rfqComments.createdAt }) // id만 반환하도록 if (!insertedComment) { throw new Error("Failed to create comment") } // 2) 첨부파일 처리 if (files && files.length > 0) { const rfqDir = path.join(process.cwd(), "public", "rfq", String(rfqId)); // 폴더 없으면 생성 await fs.mkdir(rfqDir, { recursive: true }); for (const file of files) { const ab = await file.arrayBuffer(); const buffer = Buffer.from(ab); // 2-2) 고유 파일명 const uniqueName = `${randomUUID()}-${file.name}`; // 예) "rfq/123/xxx" const relativePath = path.join("rfq", String(rfqId), uniqueName); const absolutePath = path.join(process.cwd(), "public", relativePath); // 2-3) 파일 저장 await fs.writeFile(absolutePath, buffer); // DB에 첨부파일 row 생성 await db.insert(rfqAttachments).values({ rfqId, vendorId: vendorId || null, evaluationId: evaluationId || null, cbeId: cbeId || null, commentId: insertedComment.id, // 새 코멘트와 연결 fileName: file.name, filePath: "/" + relativePath.replace(/\\/g, "/"), }) } } revalidateTag("rfq-vendors"); return { ok: true, commentId: insertedComment.id, createdAt: insertedComment.createdAt } } export async function fetchRfqAttachmentsbyCommentId(commentId: number) { // DB select const rows = await db .select() .from(rfqAttachments) .where(eq(rfqAttachments.commentId, commentId)) // rows: { id, fileName, filePath, createdAt, vendorId, ... } // 필요 없는 필드는 omit하거나 transform 가능 return rows.map((row) => ({ id: row.id, fileName: row.fileName, filePath: row.filePath, createdAt: row.createdAt, // or string vendorId: row.vendorId, evaluationId: row.evaluationId, size: undefined, // size를 DB에 저장하지 않았다면 })) } export async function updateRfqComment(params: { commentId: number commentText: string }) { const { commentId, commentText } = params // 예: 간단한 길이 체크 등 유효성 검사 if (!commentText || commentText.trim().length === 0) { throw new Error("Comment text must not be empty.") } // DB 업데이트 const updatedRows = await db .update(rfqComments) .set({ commentText }) // 필요한 컬럼만 set .where(eq(rfqComments.id, commentId)) .returning({ id: rfqComments.id }) // 혹은 returning 전체(row)를 받아서 확인할 수도 있음 if (updatedRows.length === 0) { // 해당 id가 없으면 예외 throw new Error("Comment not found or already deleted.") } revalidateTag("rfq-vendors"); return { ok: true } } export type Project = { id: number; projectCode: string; projectName: string; } export async function getProjects(): Promise { try { // 트랜잭션을 사용하여 프로젝트 데이터 조회 const projectList = await db.transaction(async (tx) => { // 모든 프로젝트 조회 const results = await tx .select({ id: projects.id, projectCode: projects.code, // 테이블의 실제 컬럼명에 맞게 조정 projectName: projects.name, // 테이블의 실제 컬럼명에 맞게 조정 }) .from(projects) .orderBy(projects.code); return results; }); return projectList; } catch (error) { console.error("프로젝트 목록 가져오기 실패:", error); return []; // 오류 발생 시 빈 배열 반환 } } // 반환 타입 명시적 정의 - rfqCode가 null일 수 있음을 반영 export interface BudgetaryRfq { id: number; rfqCode: string | null; // null 허용으로 변경 description: string | null; projectId: number | null; projectCode: string | null; projectName: string | null; } type GetBudgetaryRfqsResponse = | { rfqs: BudgetaryRfq[]; totalCount: number; error?: never } | { error: string; rfqs?: never; totalCount: number } /** * Budgetary 타입의 RFQ 목록을 가져오는 서버 액션 * Purchase RFQ 생성 시 부모 RFQ로 선택할 수 있도록 함 * 페이징 및 필터링 기능 포함 */ export interface GetBudgetaryRfqsParams { search?: string; projectId?: number; rfqId?: number; // 특정 ID로 단일 RFQ 검색 rfqTypes?: RfqType[]; // 특정 RFQ 타입들로 필터링 limit?: number; offset?: number; } export async function getBudgetaryRfqs(params: GetBudgetaryRfqsParams = {}): Promise { const { search, projectId, rfqId, rfqTypes, limit = 50, offset = 0 } = params; const cacheKey = `rfqs-query-${JSON.stringify(params)}`; return unstable_cache( async () => { try { // 기본 검색 조건 구성 let baseCondition; // 특정 RFQ 타입들로 필터링 (rfqTypes 배열이 주어진 경우) if (rfqTypes && rfqTypes.length > 0) { // 여러 타입으로 필터링 (OR 조건) baseCondition = inArray(rfqs.rfqType, rfqTypes); } else { // 기본적으로 BUDGETARY 타입만 검색 (이전 동작 유지) baseCondition = eq(rfqs.rfqType, RfqType.BUDGETARY); } // 특정 ID로 검색하는 경우 if (rfqId) { baseCondition = and(baseCondition, eq(rfqs.id, rfqId)); } let where1; // 검색어 조건 추가 (있을 경우) if (search && search.trim()) { const searchTerm = `%${search.trim()}%`; const searchCondition = or( ilike(rfqs.rfqCode, searchTerm), ilike(rfqs.description, searchTerm), ilike(projects.code, searchTerm), ilike(projects.name, searchTerm) ); where1 = searchCondition; } let where2; // 프로젝트 ID 조건 추가 (있을 경우) if (projectId) { where2 = eq(rfqs.projectId, projectId); } const finalWhere = and(baseCondition, where1, where2); // 총 개수 조회 const [countResult] = await db .select({ count: count() }) .from(rfqs) .leftJoin(projects, eq(rfqs.projectId, projects.id)) .where(finalWhere); // 실제 데이터 조회 const resultRfqs = await db .select({ id: rfqs.id, rfqCode: rfqs.rfqCode, description: rfqs.description, rfqType: rfqs.rfqType, // RFQ 타입 필드 추가 projectId: rfqs.projectId, projectCode: projects.code, projectName: projects.name, }) .from(rfqs) .leftJoin(projects, eq(rfqs.projectId, projects.id)) .where(finalWhere) .orderBy(desc(rfqs.createdAt)) .limit(limit) .offset(offset); return { rfqs: resultRfqs, totalCount: Number(countResult?.count) || 0 }; } catch (error) { console.error("Error fetching RFQs:", error); return { error: "Failed to fetch RFQs", totalCount: 0 }; } }, [cacheKey], { revalidate: 60, // 1분 캐시 tags: ["rfqs-query"], } )(); } export async function getAllVendors() { // Adjust the query as needed (add WHERE, ORDER, etc.) const allVendors = await db.select().from(vendors) return allVendors } /** * Server action to associate items from an RFQ with a vendor * * @param rfqId - The ID of the RFQ containing items to associate * @param vendorId - The ID of the vendor to associate items with * @returns Object indicating success or failure */ export async function addItemToVendors(rfqId: number, vendorIds: number[]) { try { // Input validation if (!vendorIds.length) { return { success: false, error: "No vendors selected" }; } // 1. Find all itemCodes associated with the given rfqId using select const rfqItemResults = await db .select({ itemCode: rfqItems.itemCode }) .from(rfqItems) .where(eq(rfqItems.rfqId, rfqId)); // Extract itemCodes const itemCodes = rfqItemResults.map(item => item.itemCode); if (itemCodes.length === 0) { return { success: false, error: "No items found for this RFQ" }; } // 2. Find existing vendor-item combinations to avoid duplicates const existingCombinations = await db .select({ vendorId: vendorPossibleItems.vendorId, itemCode: vendorPossibleItems.itemCode }) .from(vendorPossibleItems) .where( and( inArray(vendorPossibleItems.vendorId, vendorIds), inArray(vendorPossibleItems.itemCode, itemCodes) ) ); // Create a Set of existing combinations for easy lookups const existingSet = new Set(); existingCombinations.forEach(combo => { existingSet.add(`${combo.vendorId}-${combo.itemCode}`); }); // 3. Prepare records to insert (only non-existing combinations) const recordsToInsert = []; for (const vendorId of vendorIds) { for (const itemCode of itemCodes) { const key = `${vendorId}-${itemCode}`; if (!existingSet.has(key)) { recordsToInsert.push({ vendorId, itemCode, // createdAt and updatedAt will be set by defaultNow() }); } } } // 4. Bulk insert if there are records to insert let insertedCount = 0; if (recordsToInsert.length > 0) { const result = await db.insert(vendorPossibleItems).values(recordsToInsert); insertedCount = recordsToInsert.length; } // 5. Revalidate to refresh data revalidateTag("rfq-vendors"); // 6. Return success with counts return { success: true, insertedCount, totalPossibleItems: vendorIds.length * itemCodes.length, vendorCount: vendorIds.length, itemCount: itemCodes.length }; } catch (error) { console.error("Error adding items to vendors:", error); return { success: false, error: error instanceof Error ? error.message : "Unknown error" }; } } /** * 특정 평가에 대한 TBE 템플릿 파일 목록 조회 * evaluationId가 일치하고 vendorId가 null인 파일 목록 */ export async function fetchTbeTemplateFiles(evaluationId: number) { console.log(evaluationId, "evaluationId") try { const files = await db .select({ id: rfqAttachments.id, fileName: rfqAttachments.fileName, filePath: rfqAttachments.filePath, createdAt: rfqAttachments.createdAt, }) .from(rfqAttachments) .where( and( isNull(rfqAttachments.commentId), isNull(rfqAttachments.vendorId), eq(rfqAttachments.evaluationId, evaluationId), // eq(rfqAttachments.vendorId, vendorId), ) ) return { files, error: null } } catch (error) { console.error("Error fetching TBE template files:", error) return { files: [], error: "템플릿 파일을 가져오는 중 오류가 발생했습니다." } } } /** * 특정 TBE 템플릿 파일 다운로드를 위한 정보 조회 */ export async function getTbeTemplateFileInfo(fileId: number) { try { const file = await db .select({ fileName: rfqAttachments.fileName, filePath: rfqAttachments.filePath, }) .from(rfqAttachments) .where(eq(rfqAttachments.id, fileId)) .limit(1) if (!file.length) { return { file: null, error: "파일을 찾을 수 없습니다." } } return { file: file[0], error: null } } catch (error) { console.error("Error getting TBE template file info:", error) return { file: null, error: "파일 정보를 가져오는 중 오류가 발생했습니다." } } } /** * TBE 응답 파일 업로드 처리 */ export async function uploadTbeResponseFile(formData: FormData) { try { const file = formData.get("file") as File const rfqId = parseInt(formData.get("rfqId") as string) const vendorId = parseInt(formData.get("vendorId") as string) const evaluationId = parseInt(formData.get("evaluationId") as string) const vendorResponseId = parseInt(formData.get("vendorResponseId") as string) if (!file || !rfqId || !vendorId || !evaluationId) { return { success: false, error: "필수 필드가 누락되었습니다." } } // 타임스탬프 기반 고유 파일명 생성 const timestamp = Date.now() const originalName = file.name const fileExtension = originalName.split(".").pop() const fileName = `${originalName.split(".")[0]}-${timestamp}.${fileExtension}` // 업로드 디렉토리 및 경로 정의 const uploadDir = join(process.cwd(), "rfq", "tbe-responses") // 디렉토리가 없으면 생성 try { await mkdir(uploadDir, { recursive: true }) } catch (error) { // 이미 존재하면 무시 } const filePath = join(uploadDir, fileName) // 파일을 버퍼로 변환 const bytes = await file.arrayBuffer() const buffer = Buffer.from(bytes) // 파일을 서버에 저장 await writeFile(filePath, buffer) // 먼저 vendorTechnicalResponses 테이블에 엔트리 생성 const technicalResponse = await db.insert(vendorTechnicalResponses) .values({ responseId: vendorResponseId, summary: "TBE 응답 파일 업로드", // 필요에 따라 수정 notes: `파일명: ${originalName}`, }) .returning({ id: vendorTechnicalResponses.id }); // 생성된 기술 응답 ID 가져오기 const technicalResponseId = technicalResponse[0].id; // 파일 정보를 데이터베이스에 저장 const dbFilePath = `/rfq/tbe-responses/${fileName}` // vendorResponseAttachments 테이블 스키마에 맞게 데이터 삽입 await db.insert(vendorResponseAttachments) .values({ // 오류 메시지를 기반으로 올바른 필드 이름 사용 // 테이블 스키마에 정의된 필드만 포함해야 함 responseId: vendorResponseId, technicalResponseId: technicalResponseId, // vendorId와 evaluationId 필드가 테이블에 있다면 포함, 없다면 제거 // vendorId: vendorId, // evaluationId: evaluationId, fileName: originalName, filePath: dbFilePath, uploadedAt: new Date(), }); // 경로 재검증 (캐시된 데이터 새로고침) revalidatePath(`/rfq/${rfqId}/tbe`) revalidateTag(`tbe-vendors-${vendorId}`) return { success: true, message: "파일이 성공적으로 업로드되었습니다." } } catch (error) { console.error("Error uploading file:", error) return { success: false, error: "파일 업로드에 실패했습니다." } } } export async function getTbeSubmittedFiles(responseId: number) { try { // First, get the technical response IDs where vendorResponseId matches responseId const technicalResponses = await db .select({ id: vendorTechnicalResponses.id, }) .from(vendorTechnicalResponses) .where( eq(vendorTechnicalResponses.responseId, responseId) ) if (technicalResponses.length === 0) { return { files: [], error: null } } // Extract the IDs from the result const technicalResponseIds = technicalResponses.map(tr => tr.id) // Then get attachments where technicalResponseId matches any of the IDs we found const files = await db .select({ id: vendorResponseAttachments.id, fileName: vendorResponseAttachments.fileName, filePath: vendorResponseAttachments.filePath, uploadedAt: vendorResponseAttachments.uploadedAt, fileType: vendorResponseAttachments.fileType, attachmentType: vendorResponseAttachments.attachmentType, description: vendorResponseAttachments.description, }) .from(vendorResponseAttachments) .where( inArray(vendorResponseAttachments.technicalResponseId, technicalResponseIds) ) .orderBy(vendorResponseAttachments.uploadedAt) return { files, error: null } } catch (error) { return { files: [], error: 'Failed to fetch TBE submitted files' } } } export async function getTbeFilesForVendor(rfqId: number, vendorId: number) { try { // Step 1: Get responseId from vendor_responses table const response = await db .select({ id: vendorResponses.id, }) .from(vendorResponses) .where( and( eq(vendorResponses.rfqId, rfqId), eq(vendorResponses.vendorId, vendorId) ) ) .limit(1); if (!response || response.length === 0) { return { files: [], error: 'No vendor response found' }; } const responseId = response[0].id; // Step 2: Get the technical response IDs const technicalResponses = await db .select({ id: vendorTechnicalResponses.id, }) .from(vendorTechnicalResponses) .where( eq(vendorTechnicalResponses.responseId, responseId) ); if (technicalResponses.length === 0) { return { files: [], error: null }; } // Extract the IDs from the result const technicalResponseIds = technicalResponses.map(tr => tr.id); // Step 3: Get attachments where technicalResponseId matches any of the IDs const files = await db .select({ id: vendorResponseAttachments.id, fileName: vendorResponseAttachments.fileName, filePath: vendorResponseAttachments.filePath, uploadedAt: vendorResponseAttachments.uploadedAt, fileType: vendorResponseAttachments.fileType, attachmentType: vendorResponseAttachments.attachmentType, description: vendorResponseAttachments.description, }) .from(vendorResponseAttachments) .where( inArray(vendorResponseAttachments.technicalResponseId, technicalResponseIds) ) .orderBy(vendorResponseAttachments.uploadedAt); return { files, error: null }; } catch (error) { return { files: [], error: 'Failed to fetch vendor files' }; } } export async function getAllTBE(input: GetTBESchema) { return unstable_cache( async () => { // 1) 페이징 const offset = ((input.page ?? 1) - 1) * (input.perPage ?? 10) const limit = input.perPage ?? 10 // 2) 고급 필터 const advancedWhere = filterColumns({ table: vendorTbeView, filters: input.filters ?? [], joinOperator: input.joinOperator ?? "and", }) // 3) 글로벌 검색 let globalWhere if (input.search) { const s = `%${input.search}%` globalWhere = or( sql`${vendorTbeView.vendorName} ILIKE ${s}`, sql`${vendorTbeView.vendorCode} ILIKE ${s}`, sql`${vendorTbeView.email} ILIKE ${s}`, sql`${vendorTbeView.rfqCode} ILIKE ${s}`, sql`${vendorTbeView.projectCode} ILIKE ${s}`, sql`${vendorTbeView.projectName} ILIKE ${s}` ) } // 4) REJECTED 아니거나 NULL const notRejected = or( ne(vendorTbeView.rfqVendorStatus, "REJECTED"), isNull(vendorTbeView.rfqVendorStatus) ) // 5) rfqType 필터 추가 const rfqTypeFilter = input.rfqType ? eq(vendorTbeView.rfqType, input.rfqType) : undefined // 6) finalWhere - rfqType 필터 추가 const finalWhere = and( notRejected, advancedWhere, globalWhere, rfqTypeFilter // 새로 추가된 rfqType 필터 ) // 6) 정렬 const orderBy = input.sort?.length ? input.sort.map((s) => { const col = (vendorTbeView as any)[s.id] return s.desc ? desc(col) : asc(col) }) : [desc(vendorTbeView.rfqId), asc(vendorTbeView.vendorId)] // Default sort by newest RFQ first // 7) 메인 SELECT const [rows, total] = await db.transaction(async (tx) => { const data = await tx .select({ // 원하는 컬럼들 id: vendorTbeView.vendorId, tbeId: vendorTbeView.tbeId, vendorId: vendorTbeView.vendorId, vendorName: vendorTbeView.vendorName, vendorCode: vendorTbeView.vendorCode, address: vendorTbeView.address, country: vendorTbeView.country, email: vendorTbeView.email, website: vendorTbeView.website, vendorStatus: vendorTbeView.vendorStatus, rfqId: vendorTbeView.rfqId, rfqCode: vendorTbeView.rfqCode, projectCode: vendorTbeView.projectCode, projectName: vendorTbeView.projectName, description: vendorTbeView.description, dueDate: vendorTbeView.dueDate, rfqVendorStatus: vendorTbeView.rfqVendorStatus, rfqVendorUpdated: vendorTbeView.rfqVendorUpdated, tbeResult: vendorTbeView.tbeResult, tbeNote: vendorTbeView.tbeNote, tbeUpdated: vendorTbeView.tbeUpdated, }) .from(vendorTbeView) .where(finalWhere) .orderBy(...orderBy) .offset(offset) .limit(limit) const [{ count }] = await tx .select({ count: sql`count(*)`.as("count") }) .from(vendorTbeView) .where(finalWhere) return [data, Number(count)] }) if (!rows.length) { return { data: [], pageCount: 0 } } // 8) Get distinct rfqIds and vendorIds - filter out nulls 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[]; // 9) Comments 조회 const commentsConditions = [isNotNull(rfqComments.evaluationId)]; // 배열이 비어있지 않을 때만 조건 추가 if (distinctRfqIds.length > 0) { commentsConditions.push(inArray(rfqComments.rfqId, distinctRfqIds)); } if (distinctVendorIds.length > 0) { commentsConditions.push(inArray(rfqComments.vendorId, distinctVendorIds)); } const commAll = await db .select({ id: rfqComments.id, commentText: rfqComments.commentText, vendorId: rfqComments.vendorId, rfqId: rfqComments.rfqId, evaluationId: rfqComments.evaluationId, createdAt: rfqComments.createdAt, commentedBy: rfqComments.commentedBy, evalType: rfqEvaluations.evalType, }) .from(rfqComments) .innerJoin( rfqEvaluations, and( eq(rfqEvaluations.id, rfqComments.evaluationId), eq(rfqEvaluations.evalType, "TBE") ) ) .where(and(...commentsConditions)); // 9-A) Create a composite key (rfqId-vendorId) -> comments mapping const commByCompositeKey = new Map() for (const c of commAll) { if (!c.rfqId || !c.vendorId) continue; const compositeKey = `${c.rfqId}-${c.vendorId}`; if (!commByCompositeKey.has(compositeKey)) { commByCompositeKey.set(compositeKey, []) } commByCompositeKey.get(compositeKey)!.push({ id: c.id, commentText: c.commentText, vendorId: c.vendorId, evaluationId: c.evaluationId, createdAt: c.createdAt, commentedBy: c.commentedBy, }) } // 10) Responses 조회 const responsesAll = await db .select({ id: vendorResponses.id, rfqId: vendorResponses.rfqId, vendorId: vendorResponses.vendorId }) .from(vendorResponses) .where( and( inArray(vendorResponses.rfqId, distinctRfqIds), inArray(vendorResponses.vendorId, distinctVendorIds) ) ); // Group responses by rfqId-vendorId composite key const responsesByCompositeKey = new Map(); for (const resp of responsesAll) { const compositeKey = `${resp.rfqId}-${resp.vendorId}`; if (!responsesByCompositeKey.has(compositeKey)) { responsesByCompositeKey.set(compositeKey, []); } responsesByCompositeKey.get(compositeKey)!.push(resp.id); } // Get all responseIds const allResponseIds = responsesAll.map(r => r.id); // 11) Get technicalResponses for these responseIds const technicalResponsesAll = await db .select({ id: vendorTechnicalResponses.id, responseId: vendorTechnicalResponses.responseId }) .from(vendorTechnicalResponses) .where(inArray(vendorTechnicalResponses.responseId, allResponseIds)); // Create mapping from responseId to technicalResponseIds const technicalResponseIdsByResponseId = new Map(); for (const tr of technicalResponsesAll) { if (!technicalResponseIdsByResponseId.has(tr.responseId)) { technicalResponseIdsByResponseId.set(tr.responseId, []); } technicalResponseIdsByResponseId.get(tr.responseId)!.push(tr.id); } // Get all technicalResponseIds const allTechnicalResponseIds = technicalResponsesAll.map(tr => tr.id); // 12) Get attachments for these technicalResponseIds const filesAll = await db .select({ id: vendorResponseAttachments.id, fileName: vendorResponseAttachments.fileName, filePath: vendorResponseAttachments.filePath, technicalResponseId: vendorResponseAttachments.technicalResponseId, fileType: vendorResponseAttachments.fileType, attachmentType: vendorResponseAttachments.attachmentType, description: vendorResponseAttachments.description, uploadedAt: vendorResponseAttachments.uploadedAt, uploadedBy: vendorResponseAttachments.uploadedBy }) .from(vendorResponseAttachments) .where( and( inArray(vendorResponseAttachments.technicalResponseId, allTechnicalResponseIds), isNotNull(vendorResponseAttachments.technicalResponseId) ) ); // Create mapping from technicalResponseId to attachments const filesByTechnicalResponseId = new Map(); for (const file of filesAll) { if (file.technicalResponseId === null) continue; if (!filesByTechnicalResponseId.has(file.technicalResponseId)) { filesByTechnicalResponseId.set(file.technicalResponseId, []); } filesByTechnicalResponseId.get(file.technicalResponseId)!.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 }); } // 13) Create the final filesByCompositeKey map const filesByCompositeKey = new Map(); for (const [compositeKey, responseIds] of responsesByCompositeKey.entries()) { filesByCompositeKey.set(compositeKey, []); for (const responseId of responseIds) { const technicalResponseIds = technicalResponseIdsByResponseId.get(responseId) || []; for (const technicalResponseId of technicalResponseIds) { const files = filesByTechnicalResponseId.get(technicalResponseId) || []; filesByCompositeKey.get(compositeKey)!.push(...files); } } } // 14) 최종 합치기 const final = rows.map((row) => { const compositeKey = `${row.rfqId}-${row.vendorId}`; return { ...row, dueDate: row.dueDate ? new Date(row.dueDate) : null, comments: commByCompositeKey.get(compositeKey) ?? [], files: filesByCompositeKey.get(compositeKey) ?? [], }; }) const pageCount = Math.ceil(total / limit) return { data: final, pageCount } }, [JSON.stringify(input)], { revalidate: 3600, tags: ["all-tbe-vendors"], } )() } export async function getCBE(input: GetCBESchema, rfqId: 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: vendorCbeView, filters: input.filters ?? [], joinOperator: input.joinOperator ?? "and", }); // [3] 글로벌 검색 let globalWhere; if (input.search) { const s = `%${input.search}%`; globalWhere = or( sql`${vendorCbeView.vendorName} ILIKE ${s}`, sql`${vendorCbeView.vendorCode} ILIKE ${s}`, sql`${vendorCbeView.email} ILIKE ${s}` ); } // [4] REJECTED 아니거나 NULL const notRejected = or( ne(vendorCbeView.rfqVendorStatus, "REJECTED"), isNull(vendorCbeView.rfqVendorStatus) ); // [5] 최종 where const finalWhere = and( eq(vendorCbeView.rfqId, rfqId), notRejected, advancedWhere, globalWhere ); // [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)]; // [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, }) .from(vendorCbeView) .where(finalWhere) .orderBy(...orderBy) .offset(offset) .limit(limit); const [{ count }] = await tx .select({ count: sql`count(*)`.as("count") }) .from(vendorCbeView) .where(finalWhere); return [data, Number(count)]; }); if (!rows.length) { return { data: [], pageCount: 0 }; } // [8] Comments 조회 // TBE 에서는 rfqComments + rfqEvaluations(evalType="TBE") 를 조인했지만, // CBE는 cbeEvaluations 또는 evalType="CBE"를 기준으로 바꾸면 됩니다. // 만약 cbeEvaluations.id 를 evaluationId 로 참조한다면 아래와 같이 innerJoin: const distinctVendorIds = [...new Set(rows.map((r) => r.vendorId))]; const commAll = await db .select({ id: rfqComments.id, commentText: rfqComments.commentText, vendorId: rfqComments.vendorId, evaluationId: rfqComments.evaluationId, createdAt: rfqComments.createdAt, commentedBy: rfqComments.commentedBy, // cbeEvaluations에는 evalType 컬럼이 별도로 없을 수도 있음(프로젝트 구조에 맞게 수정) // evalType: cbeEvaluations.evalType, }) .from(rfqComments) .innerJoin( cbeEvaluations, eq(cbeEvaluations.id, rfqComments.evaluationId) ) .where( and( isNotNull(rfqComments.evaluationId), eq(rfqComments.rfqId, rfqId), inArray(rfqComments.vendorId, distinctVendorIds) ) ); // vendorId -> comments grouping const commByVendorId = new Map(); for (const c of commAll) { const vid = c.vendorId!; if (!commByVendorId.has(vid)) { commByVendorId.set(vid, []); } commByVendorId.get(vid)!.push({ id: c.id, commentText: c.commentText, vendorId: c.vendorId, evaluationId: c.evaluationId, createdAt: c.createdAt, commentedBy: c.commentedBy, }); } // [9] CBE 파일 조회 (프로젝트에 따라 구조가 달라질 수 있음) // - TBE는 vendorTechnicalResponses 기준 // - CBE는 vendorCommercialResponses(가정) 등이 있을 수 있음 // - 여기서는 예시로 "동일한 vendorResponses + vendorResponseAttachments" 라고 가정 // Step 1: vendorResponses 가져오기 (rfqId + vendorIds) const responsesAll = await db .select({ id: vendorResponses.id, vendorId: vendorResponses.vendorId, }) .from(vendorResponses) .where( and( eq(vendorResponses.rfqId, rfqId), inArray(vendorResponses.vendorId, distinctVendorIds) ) ); // Group responses by vendorId const responsesByVendorId = new Map(); 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(); 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 .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, allCommercialResponseIds), isNotNull(vendorResponseAttachments.responseId) ) ); // Step 4: responseId -> files const filesByResponseId = new Map(); for (const file of filesAll) { const rid = file.responseId!; if (!filesByResponseId.has(rid)) { filesByResponseId.set(rid, []); } filesByResponseId.get(rid)!.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, }); } // Step 5: vendorId -> files const filesByVendorId = new Map(); 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); } } // [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) ?? [], })); const pageCount = Math.ceil(total / limit); return { data: final, pageCount }; }, // 캐싱 키 & 옵션 [JSON.stringify({ input, rfqId })], { revalidate: 3600, tags: ["cbe-vendors"], } )(); } export async function generateNextRfqCode(rfqType: RfqType): Promise<{ code: string; error?: string }> { try { if (!rfqType) { return { code: "", error: 'RFQ 타입이 필요합니다' }; } // 현재 연도 가져오기 const currentYear = new Date().getFullYear(); // 현재 연도와 타입에 맞는 최신 RFQ 코드 찾기 const latestRfqs = await db.select({ rfqCode: rfqs.rfqCode }) .from(rfqs) .where(and( sql`SUBSTRING(${rfqs.rfqCode}, 5, 4) = ${currentYear.toString()}`, eq(rfqs.rfqType, rfqType) )) .orderBy(desc(rfqs.rfqCode)) .limit(1); let sequenceNumber = 1; if (latestRfqs.length > 0 && latestRfqs[0].rfqCode) { // null 체크 추가 - TypeScript 오류 해결 const latestCode = latestRfqs[0].rfqCode; const matches = latestCode.match(/[A-Z]+-\d{4}-(\d{3})/); if (matches && matches[1]) { sequenceNumber = parseInt(matches[1], 10) + 1; } } // 새로운 RFQ 코드 포맷팅 const typePrefix = rfqType === RfqType.BUDGETARY ? 'BUD' : rfqType === RfqType.PURCHASE_BUDGETARY ? 'PBU' : 'RFQ'; const newCode = `${typePrefix}-${currentYear}-${String(sequenceNumber).padStart(3, '0')}`; return { code: newCode }; } catch (error) { console.error('Error generating next RFQ code:', error); return { code: "", error: '코드 생성에 실패했습니다' }; } }