diff options
Diffstat (limited to 'lib/rfqs-ship/service.ts')
| -rw-r--r-- | lib/rfqs-ship/service.ts | 4027 |
1 files changed, 4027 insertions, 0 deletions
diff --git a/lib/rfqs-ship/service.ts b/lib/rfqs-ship/service.ts new file mode 100644 index 00000000..c7d1c3cd --- /dev/null +++ b/lib/rfqs-ship/service.ts @@ -0,0 +1,4027 @@ +// 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, 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"; +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, 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 { vendorContacts, vendorPossibleItems, vendors } from "@/db/schema/vendors"; +import { sendEmail } from "../mail/sendEmail"; +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 { + 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<Rfq["status"], number> = { + 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<Record<Rfq["status"], number>>((acc, { status, count }) => { + acc[status] = count; + return acc; + }, initial); + }); + + return result; + } catch (err) { + return {} as Record<Rfq["status"], number>; + } + }, + [`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, + bidProjectId: input.bidProjectId || 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<typeof deleteRfqItemSchema>; + +/** + * 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<number>`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<RfqViewWithItems | null> => { + 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<number>`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<number, { status: string, updatedAt: Date }>() + 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) 코멘트 조회: 기존과 동일 + // ───────────────────────────────────────────────────── + console.log("distinctVendorIds", distinctVendorIds) + const commAll = await db + .select() + .from(rfqComments) + .where( + and( + inArray(rfqComments.vendorId, distinctVendorIds), + eq(rfqComments.rfqId, rfqId), + isNull(rfqComments.evaluationId), + isNull(rfqComments.cbeId) + ) + ) + + const commByVendorId = new Map<number, any[]>() + // 먼저 모든 사용자 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") + } + + const headersList = await headers(); + const host = headersList.get('host') || 'localhost:3000'; + + // 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 loginUrl = `http://${host}/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, + + technicalResponseId:vendorTbeView.technicalResponseId, + technicalResponseStatus:vendorTbeView.technicalResponseStatus, + technicalSummary:vendorTbeView.technicalSummary, + technicalNotes:vendorTbeView.technicalNotes, + technicalUpdated:vendorTbeView.technicalUpdated, + }) + .from(vendorTbeView) + .where(finalWhere) + .orderBy(...orderBy) + .offset(offset) + .limit(limit) + + const [{ count }] = await tx + .select({ count: sql<number>`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<number, any[]>() + 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<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: 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<number, number[]>(); + 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<number, any[]>(); + 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<number, any[]>(); + 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, + rfqType:vendorTbeView.rfqType, + rfqStatus:vendorTbeView.rfqStatus, + rfqDescription: vendorTbeView.description, + rfqDueDate: vendorTbeView.dueDate, + + + 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<number>`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<number, any[]>() + 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<number>`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<number, number>() + 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<number>`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<string, number>() + 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)) + + // 디렉토리가 없다면 생성 + try { + await fs.mkdir(uploadDir, { recursive: true }) + } catch (err) { + console.error("디렉토리 생성 실패:", err) + } + + // 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, + name: vendors.vendorName, + email: vendors.email, + representativeEmail: vendors.representativeEmail // 대표자 이메일 추가 + }) + .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 timestamp = new Date().getTime() + const fileName = `${timestamp}-${originalName}` + const savePath = path.join(uploadDir, fileName) + + // 파일 ArrayBuffer → Buffer 변환 후 저장 + const arrayBuffer = await file.arrayBuffer() + await fs.writeFile(savePath, Buffer.from(arrayBuffer)) + + // 저장 경로 & 파일명 기록 + savedFiles.push({ + fileName: originalName, // 원본 파일명으로 첨부 + filePath: `/rfq/${rfqId}/${fileName}`, // public 이하 경로 + absolutePath: savePath, + }) + } + + // (E) 각 벤더별로 TBE 평가 레코드, 초대 처리, 메일 발송 + 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 + } + + // 3) TBE 평가 레코드 생성 + const [evalRow] = await tx + .insert(rfqEvaluations) + .values({ + rfqId, + vendorId: vendor.id, + evalType: "TBE", + }) + .returning({ id: rfqEvaluations.id }) + + // 4) rfqAttachments에 저장한 파일들을 기록 + for (const sf of savedFiles) { + await tx.insert(rfqAttachments).values({ + rfqId, + vendorId: vendor.id, + evaluationId: evalRow.id, + fileName: sf.fileName, + filePath: sf.filePath, + }) + } + + // 5) 각 고유 이메일 주소로 초대 메일 발송 + const baseUrl = process.env.NEXT_PUBLIC_BASE_URL || 'http://3.36.56.124:3000' + const loginUrl = `${baseUrl}/ko/partners/rfq` + + 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) + } + } + } + + // 6) 캐시 무효화 + 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 + console.log("cbeId", cbeId) + console.log("evaluationId", evaluationId) + // 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<Project[]> { + 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 []; // 오류 발생 시 빈 배열 반환 + } +} + + +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; + 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<GetBudgetaryRfqsResponse> { + 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 +} + + +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 + * + * @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) { + 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: "템플릿 파일을 가져오는 중 오류가 발생했습니다." + } + } +} + +export async function getFileFromRfqAttachmentsbyid(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}`, + responseStatus:"SUBMITTED" + }) + .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, + + technicalResponseStatus:vendorTbeView.technicalResponseStatus, + 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<number>`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<string, any[]>() + 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<string, number[]>(); + 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<number, number[]>(); + 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<number, any[]>(); + 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<string, any[]>(); + + 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: 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.totalPrice}::text ILIKE ${s}` + ); + } + + // [4] DECLINED 상태 제외 (거절된 업체는 표시하지 않음) + const notDeclined = ne(vendorResponseCBEView.responseStatus, "DECLINED"); + + // [5] 최종 where 조건 + const finalWhere = and( + eq(vendorResponseCBEView.rfqId, rfqId), + 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); + }) + : [asc(vendorResponseCBEView.vendorName)]; // 기본 정렬은 벤더명 + + // [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] 협력업체 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!))]; + + // [9] CBE 평가 관련 코멘트 조회 + const commentsAll = await db + .select({ + id: rfqComments.id, + commentText: rfqComments.commentText, + vendorId: rfqComments.vendorId, + 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.rfqId, rfqId), + inArray(rfqComments.vendorId, distinctVendorIds) + ) + ); + + // vendorId별 코멘트 그룹화 + const commentsByVendorId = new Map<number, any[]>(); + for (const comment of commentsAll) { + const vendorId = comment.vendorId!; + if (!commentsByVendorId.has(vendorId)) { + commentsByVendorId.set(vendorId, []); + } + commentsByVendorId.get(vendorId)!.push({ + id: comment.id, + commentText: comment.commentText, + vendorId: comment.vendorId, + 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: commentsByVendorId.get(row.vendorId) || [], + files: allFiles, + }; + }); + + const pageCount = Math.ceil(total / limit); + return { + data: final, + pageCount, + total + }; + }, + // 캐싱 키 & 옵션 + [`cbe-vendors-${rfqId}-${JSON.stringify(input)}`], + { + revalidate: 3600, + tags: [`cbe-vendors-${rfqId}`], + } + )(); +} + +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: '코드 생성에 실패했습니다' }; + } +} + +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 |