summaryrefslogtreecommitdiff
path: root/lib/rfqs/service.ts
diff options
context:
space:
mode:
authorjoonhoekim <26rote@gmail.com>2025-03-25 15:55:45 +0900
committerjoonhoekim <26rote@gmail.com>2025-03-25 15:55:45 +0900
commit1a2241c40e10193c5ff7008a7b7b36cc1d855d96 (patch)
tree8a5587f10ca55b162d7e3254cb088b323a34c41b /lib/rfqs/service.ts
initial commit
Diffstat (limited to 'lib/rfqs/service.ts')
-rw-r--r--lib/rfqs/service.ts2783
1 files changed, 2783 insertions, 0 deletions
diff --git a/lib/rfqs/service.ts b/lib/rfqs/service.ts
new file mode 100644
index 00000000..b1e02cd0
--- /dev/null
+++ b/lib/rfqs/service.ts
@@ -0,0 +1,2783 @@
+// src/lib/tasks/service.ts
+"use server"; // Next.js 서버 액션에서 직접 import하려면 (선택)
+
+import { revalidatePath, revalidateTag, unstable_noStore } from "next/cache";
+import db from "@/db/db";
+
+import { filterColumns } from "@/lib/filter-columns";
+import { unstable_cache } from "@/lib/unstable-cache";
+import { getErrorMessage } from "@/lib/handle-error";
+
+import { GetRfqsSchema, CreateRfqSchema, UpdateRfqSchema, CreateRfqItemSchema, GetMatchedVendorsSchema, GetRfqsForVendorsSchema, UpdateRfqVendorSchema, GetTBESchema, RfqType, GetCBESchema } from "./validations";
+import { asc, desc, ilike, inArray, and, gte, lte, not, or, sql, eq, isNull, ne, isNotNull, count } from "drizzle-orm";
+import path from "path";
+import fs from "fs/promises";
+import { randomUUID } from "crypto";
+import { writeFile, mkdir } from 'fs/promises'
+import { join } from 'path'
+
+import { vendorResponses, vendorResponsesView, Rfq, rfqs, rfqAttachments, rfqItems, RfqWithItems, rfqComments, rfqEvaluations, vendorRfqView, vendorTbeView, rfqsView, vendorResponseAttachments, vendorTechnicalResponses, vendorCbeView, cbeEvaluations, vendorCommercialResponses } from "@/db/schema/rfq";
+import { countRfqs, deleteRfqById, deleteRfqsByIds, getRfqById, groupByStatus, insertRfq, insertRfqItem, selectRfqs, updateRfq, updateRfqs, updateRfqVendor } from "./repository";
+import logger from '@/lib/logger';
+import { vendorPossibleItems, vendors } from "@/db/schema/vendors";
+import { sendEmail } from "../mail/sendEmail";
+import { projects } from "@/db/schema/projects";
+import { items } from "@/db/schema/items";
+import * as z from "zod"
+
+
+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,
+ 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,
+ 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<RfqWithItems | 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 }
+ }
+
+ console.log(vendorIdList,"vendorIdList")
+
+ // ─────────────────────────────────────────────────────
+ // 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),
+ 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 [{ count }] = await tx
+ .select({ count: sql<number>`count(*)`.as("count") })
+ .from(vendorRfqView)
+ .where(finalWhere)
+
+ return [data, Number(count)]
+ })
+
+
+ console.log(rows)
+ console.log(total)
+ // ─────────────────────────────────────────────────────
+ // 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) 코멘트 조회: 기존과 동일
+ // ─────────────────────────────────────────────────────
+ const commAll = await db
+ .select()
+ .from(rfqComments)
+ .where(
+ and(
+ inArray(rfqComments.vendorId, distinctVendorIds),
+ eq(rfqComments.rfqId, rfqId)
+ )
+ )
+
+ 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,
+ })
+ }
+
+ // ─────────────────────────────────────────────────────
+ // 6) rows에 comments 병합
+ // ─────────────────────────────────────────────────────
+ const final = updatedRows.map((row) => ({
+ ...row,
+ comments: commByVendorId.get(row.id) ?? [],
+ }))
+
+ // ─────────────────────────────────────────────────────
+ // 7) 반환
+ // ─────────────────────────────────────────────────────
+ const pageCount = Math.ceil(total / limit)
+ return { data: final, pageCount }
+ },
+ [JSON.stringify({ input, rfqId })],
+ { revalidate: 3600, tags: ["rfq-vendors"] }
+ )()
+}
+
+export async function inviteVendors(input: InviteVendorsInput) {
+ unstable_noStore() // 서버 액션 캐싱 방지
+ try {
+ const { rfqId, vendorIds } = input
+ if (!rfqId || !Array.isArray(vendorIds) || vendorIds.length === 0) {
+ throw new Error("Invalid input")
+ }
+
+ // DB 데이터 준비 및 첨부파일 처리를 위한 트랜잭션
+ const rfqData = await db.transaction(async (tx) => {
+ // 2-A) RFQ 기본 정보 조회
+ const [rfqRow] = await tx
+ .select({
+ rfqCode: rfqsView.rfqCode,
+ description: rfqsView.description,
+ projectCode: rfqsView.projectCode,
+ projectName: rfqsView.projectName,
+ dueDate: rfqsView.dueDate,
+ createdBy: rfqsView.createdBy,
+ })
+ .from(rfqsView)
+ .where(eq(rfqsView.id, rfqId))
+
+ if (!rfqRow) {
+ throw new Error(`RFQ #${rfqId} not found`)
+ }
+
+ // 2-B) 아이템 목록 조회
+ const items = await tx
+ .select({
+ itemCode: rfqItems.itemCode,
+ description: rfqItems.description,
+ quantity: rfqItems.quantity,
+ uom: rfqItems.uom,
+ })
+ .from(rfqItems)
+ .where(eq(rfqItems.rfqId, rfqId))
+
+ // 2-C) 첨부파일 목록 조회
+ const attachRows = await tx
+ .select({
+ id: rfqAttachments.id,
+ fileName: rfqAttachments.fileName,
+ filePath: rfqAttachments.filePath,
+ })
+ .from(rfqAttachments)
+ .where(
+ and(
+ eq(rfqAttachments.rfqId, rfqId),
+ isNull(rfqAttachments.vendorId),
+ isNull(rfqAttachments.evaluationId)
+ )
+ )
+
+ const vendorRows = await tx
+ .select({ id: vendors.id, email: vendors.email })
+ .from(vendors)
+ .where(inArray(vendors.id, vendorIds))
+
+ // NodeMailer attachments 형식 맞추기
+ const attachments = []
+ for (const att of attachRows) {
+ const absolutePath = path.join(process.cwd(), "public", att.filePath.replace(/^\/+/, ""))
+ attachments.push({
+ path: absolutePath,
+ filename: att.fileName,
+ })
+ }
+
+ return { rfqRow, items, vendorRows, attachments }
+ })
+
+ const { rfqRow, items, vendorRows, attachments } = rfqData
+ const baseUrl = process.env.NEXT_PUBLIC_BASE_URL || 'http://3.36.56.124:3000'
+ const loginUrl = `${baseUrl}/en/partners/rfq`
+
+ // 이메일 전송 오류를 기록할 배열
+ const emailErrors = []
+
+ // 각 벤더에 대해 처리
+ for (const v of vendorRows) {
+ if (!v.email) {
+ continue // 이메일 없는 벤더 무시
+ }
+
+ try {
+ // DB 업데이트: 각 벤더 상태 별도 트랜잭션
+ await db.transaction(async (tx) => {
+ // rfq_vendors upsert
+ const existing = await tx
+ .select()
+ .from(vendorResponses)
+ .where(and(eq(vendorResponses.rfqId, rfqId), eq(vendorResponses.vendorId, v.id)))
+
+ if (existing.length > 0) {
+ await tx
+ .update(vendorResponses)
+ .set({
+ responseStatus: "INVITED",
+ updatedAt: new Date(),
+ })
+ .where(eq(vendorResponses.id, existing[0].id))
+ } else {
+ await tx.insert(vendorResponses).values({
+ rfqId,
+ vendorId: v.id,
+ responseStatus: "INVITED",
+ })
+ }
+ })
+
+ // 이메일 발송 (트랜잭션 외부)
+ await sendEmail({
+ to: v.email,
+ subject: `[RFQ ${rfqRow.rfqCode}] You are invited from Samgsung Heavy Industries!`,
+ template: "rfq-invite",
+ context: {
+ language: "en",
+ rfqId,
+ vendorId: v.id,
+ rfqCode: rfqRow.rfqCode,
+ projectCode: rfqRow.projectCode,
+ projectName: rfqRow.projectName,
+ dueDate: rfqRow.dueDate,
+ description: rfqRow.description,
+ items: items.map((it) => ({
+ itemCode: it.itemCode,
+ description: it.description,
+ quantity: it.quantity,
+ uom: it.uom,
+ })),
+ loginUrl
+ },
+ attachments,
+ })
+ } catch (err) {
+ // 개별 벤더 처리 실패 로깅
+ console.error(`Failed to process vendor ${v.id}: ${getErrorMessage(err)}`)
+ emailErrors.push({ vendorId: v.id, error: getErrorMessage(err) })
+ // 계속 진행 (다른 벤더 처리)
+ }
+ }
+
+ // 최종적으로 RFQ 상태 업데이트 (별도 트랜잭션)
+ try {
+ await db.transaction(async (tx) => {
+ await tx
+ .update(rfqs)
+ .set({
+ status: "PUBLISHED",
+ updatedAt: new Date(),
+ })
+ .where(eq(rfqs.id, rfqId))
+
+ console.log(`Updated RFQ #${rfqId} status to PUBLISHED`)
+ })
+
+ // 캐시 무효화
+ revalidateTag("rfq-vendors")
+ revalidateTag("cbe-vendors")
+ revalidateTag("rfqs")
+ revalidateTag(`rfqs-${input.rfqType}`)
+ revalidateTag(`rfq-${rfqId}`)
+
+ // 이메일 오류가 있었는지 확인
+ if (emailErrors.length > 0) {
+ return {
+ error: `일부 벤더에게 이메일 발송 실패 (${emailErrors.length}/${vendorRows.length}), RFQ 상태는 업데이트됨`,
+ emailErrors
+ }
+ }
+
+ return { error: null }
+ } catch (err) {
+ return { error: `RFQ 상태 업데이트 실패: ${getErrorMessage(err)}` }
+ }
+ } catch (err) {
+ return { error: getErrorMessage(err) }
+ }
+}
+
+
+/**
+ * TBE용 평가 데이터 목록 조회
+ */
+export async function getTBE(input: GetTBESchema, rfqId: number) {
+ return unstable_cache(
+ async () => {
+ // 1) 페이징
+ const offset = ((input.page ?? 1) - 1) * (input.perPage ?? 10)
+ const limit = input.perPage ?? 10
+
+ // 2) 고급 필터
+ const advancedWhere = filterColumns({
+ table: vendorTbeView,
+ filters: input.filters ?? [],
+ joinOperator: input.joinOperator ?? "and",
+ })
+
+ // 3) 글로벌 검색
+ let globalWhere
+ if (input.search) {
+ const s = `%${input.search}%`
+ globalWhere = or(
+ sql`${vendorTbeView.vendorName} ILIKE ${s}`,
+ sql`${vendorTbeView.vendorCode} ILIKE ${s}`,
+ sql`${vendorTbeView.email} ILIKE ${s}`
+ )
+ }
+
+ // 4) REJECTED 아니거나 NULL
+ const notRejected = or(
+ ne(vendorTbeView.rfqVendorStatus, "REJECTED"),
+ isNull(vendorTbeView.rfqVendorStatus)
+ )
+
+ // 5) finalWhere
+ const finalWhere = and(
+ eq(vendorTbeView.rfqId, rfqId),
+ notRejected,
+ advancedWhere,
+ globalWhere
+ )
+
+ // 6) 정렬
+ const orderBy = input.sort?.length
+ ? input.sort.map((s) => {
+ const col = (vendorTbeView as any)[s.id]
+ return s.desc ? desc(col) : asc(col)
+ })
+ : [asc(vendorTbeView.vendorId)]
+
+ // 7) 메인 SELECT
+ const [rows, total] = await db.transaction(async (tx) => {
+ const data = await tx
+ .select({
+ // 원하는 컬럼들
+ id: vendorTbeView.vendorId,
+ tbeId: vendorTbeView.tbeId,
+ vendorId: vendorTbeView.vendorId,
+ vendorName: vendorTbeView.vendorName,
+ vendorCode: vendorTbeView.vendorCode,
+ address: vendorTbeView.address,
+ country: vendorTbeView.country,
+ email: vendorTbeView.email,
+ website: vendorTbeView.website,
+ vendorStatus: vendorTbeView.vendorStatus,
+
+ rfqId: vendorTbeView.rfqId,
+ rfqCode: vendorTbeView.rfqCode,
+ projectCode: vendorTbeView.projectCode,
+ projectName: vendorTbeView.projectName,
+ description: vendorTbeView.description,
+ dueDate: vendorTbeView.dueDate,
+
+ rfqVendorStatus: vendorTbeView.rfqVendorStatus,
+ rfqVendorUpdated: vendorTbeView.rfqVendorUpdated,
+
+ tbeResult: vendorTbeView.tbeResult,
+ tbeNote: vendorTbeView.tbeNote,
+ tbeUpdated: vendorTbeView.tbeUpdated,
+ })
+ .from(vendorTbeView)
+ .where(finalWhere)
+ .orderBy(...orderBy)
+ .offset(offset)
+ .limit(limit)
+
+ const [{ count }] = await tx
+ .select({ count: sql<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) {
+ return unstable_cache(
+ async () => {
+ // 1) 페이징
+ const offset = ((input.page ?? 1) - 1) * (input.perPage ?? 10)
+ const limit = input.perPage ?? 10
+
+ // 2) 고급 필터
+ const advancedWhere = filterColumns({
+ table: vendorTbeView,
+ filters: input.filters ?? [],
+ joinOperator: input.joinOperator ?? "and",
+ })
+
+ // 3) 글로벌 검색
+ let globalWhere
+ if (input.search) {
+ const s = `%${input.search}%`
+ globalWhere = or(
+ sql`${vendorTbeView.vendorName} ILIKE ${s}`,
+ sql`${vendorTbeView.vendorCode} ILIKE ${s}`,
+ sql`${vendorTbeView.email} ILIKE ${s}`
+ )
+ }
+
+ // 4) REJECTED 아니거나 NULL
+ const notRejected = or(
+ ne(vendorTbeView.rfqVendorStatus, "REJECTED"),
+ isNull(vendorTbeView.rfqVendorStatus)
+ )
+
+ // 5) finalWhere
+ const finalWhere = and(
+ isNotNull(vendorTbeView.tbeId),
+ eq(vendorTbeView.vendorId, vendorId),
+
+ notRejected,
+ advancedWhere,
+ globalWhere
+ )
+
+ // 6) 정렬
+ const orderBy = input.sort?.length
+ ? input.sort.map((s) => {
+ const col = (vendorTbeView as any)[s.id]
+ return s.desc ? desc(col) : asc(col)
+ })
+ : [asc(vendorTbeView.vendorId)]
+
+ // 7) 메인 SELECT
+ const [rows, total] = await db.transaction(async (tx) => {
+ const data = await tx
+ .select({
+ // 원하는 컬럼들
+ id: vendorTbeView.vendorId,
+ tbeId: vendorTbeView.tbeId,
+ vendorId: vendorTbeView.vendorId,
+ vendorName: vendorTbeView.vendorName,
+ vendorCode: vendorTbeView.vendorCode,
+ address: vendorTbeView.address,
+ country: vendorTbeView.country,
+ email: vendorTbeView.email,
+ website: vendorTbeView.website,
+ vendorStatus: vendorTbeView.vendorStatus,
+
+ rfqId: vendorTbeView.rfqId,
+ rfqCode: vendorTbeView.rfqCode,
+ projectCode: vendorTbeView.projectCode,
+ projectName: vendorTbeView.projectName,
+ description: vendorTbeView.description,
+ dueDate: vendorTbeView.dueDate,
+
+ vendorResponseId: vendorTbeView.vendorResponseId,
+ rfqVendorStatus: vendorTbeView.rfqVendorStatus,
+ rfqVendorUpdated: vendorTbeView.rfqVendorUpdated,
+
+ tbeResult: vendorTbeView.tbeResult,
+ tbeNote: vendorTbeView.tbeNote,
+ tbeUpdated: vendorTbeView.tbeUpdated,
+ })
+ .from(vendorTbeView)
+ .where(finalWhere)
+ .orderBy(...orderBy)
+ .offset(offset)
+ .limit(limit)
+
+ const [{ count }] = await tx
+ .select({ count: sql<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))
+
+
+ // DB 트랜잭션
+ await db.transaction(async (tx) => {
+ // (A) RFQ 기본 정보 조회
+ const [rfqRow] = await tx
+ .select({
+ rfqCode: vendorResponsesView.rfqCode,
+ description: vendorResponsesView.rfqDescription,
+ projectCode: vendorResponsesView.projectCode,
+ projectName: vendorResponsesView.projectName,
+ dueDate: vendorResponsesView.rfqDueDate,
+ createdBy: vendorResponsesView.rfqCreatedBy,
+ })
+ .from(vendorResponsesView)
+ .where(eq(vendorResponsesView.rfqId, rfqId))
+
+ if (!rfqRow) {
+ throw new Error(`RFQ #${rfqId} not found`)
+ }
+
+ // (B) RFQ 아이템 목록
+ const items = await tx
+ .select({
+ itemCode: rfqItems.itemCode,
+ description: rfqItems.description,
+ quantity: rfqItems.quantity,
+ uom: rfqItems.uom,
+ })
+ .from(rfqItems)
+ .where(eq(rfqItems.rfqId, rfqId))
+
+ // (C) 대상 벤더들
+ const vendorRows = await tx
+ .select({ id: vendors.id, email: vendors.email })
+ .from(vendors)
+ .where(sql`${vendors.id} in (${vendorIds})`)
+
+ // (D) 모든 TBE 파일 저장 & 이후 벤더 초대 처리
+ // 파일은 한 번만 저장해도 되지만, 각 벤더별로 따로 저장/첨부가 필요하다면 루프를 돌려도 됨.
+ // 여기서는 "모든 파일"을 RFQ-DIR에 저장 + "각 벤더"에는 동일 파일 목록을 첨부한다는 예시.
+ const savedFiles = []
+ for (const file of tbeFiles) {
+ const originalName = file.name || "tbe-sheet.xlsx"
+ const savePath = path.join(uploadDir, originalName)
+
+ // 파일 ArrayBuffer → Buffer 변환 후 저장
+ const arrayBuffer = await file.arrayBuffer()
+ fs.writeFile(savePath, Buffer.from(arrayBuffer))
+
+ // 저장 경로 & 파일명 기록
+ savedFiles.push({
+ fileName: originalName,
+ filePath: `/rfq/${rfqId}/${originalName}`, // public 이하 경로
+ absolutePath: savePath,
+ })
+ }
+
+ // (E) 각 벤더별로 TBE 평가 레코드, 초대 처리, 메일 발송
+ for (const v of vendorRows) {
+ if (!v.email) {
+ // 이메일 없는 경우 로직 (스킵 or throw)
+ continue
+ }
+
+ // 1) TBE 평가 레코드 생성
+ const [evalRow] = await tx
+ .insert(rfqEvaluations)
+ .values({
+ rfqId,
+ vendorId: v.id,
+ evalType: "TBE",
+ })
+ .returning({ id: rfqEvaluations.id })
+
+ // 2) rfqAttachments에 저장한 파일들을 기록
+ for (const sf of savedFiles) {
+ await tx.insert(rfqAttachments).values({
+ rfqId,
+ // vendorId: v.id,
+ evaluationId: evalRow.id,
+ fileName: sf.fileName,
+ filePath: sf.filePath,
+ })
+ }
+
+ // 4) 메일 발송
+ const baseUrl = process.env.NEXT_PUBLIC_BASE_URL || 'http://3.36.56.124:3000'
+ const loginUrl = `${baseUrl}/ko/partners/rfq`
+ await sendEmail({
+ to: v.email,
+ subject: `[RFQ ${rfqRow.rfqCode}] You are invited for TBE!`,
+ template: "rfq-invite",
+ context: {
+ language: "en",
+ rfqId,
+ vendorId: v.id,
+
+ rfqCode: rfqRow.rfqCode,
+ projectCode: rfqRow.projectCode,
+ projectName: rfqRow.projectName,
+ dueDate: rfqRow.dueDate,
+ description: rfqRow.description,
+
+ items: items.map((it) => ({
+ itemCode: it.itemCode,
+ description: it.description,
+ quantity: it.quantity,
+ uom: it.uom,
+ })),
+ loginUrl,
+ },
+ attachments: savedFiles.map((sf) => ({
+ path: sf.absolutePath,
+ filename: sf.fileName,
+ })),
+ })
+ }
+
+ // 5) 캐시 무효화
+ revalidateTag("tbe-vendors")
+ })
+
+ // 성공
+ return { error: null }
+ } catch (err) {
+ console.error("[inviteTbeVendorsAction] Error:", err)
+ return { error: getErrorMessage(err) }
+ }
+}
+////partners
+
+
+export async function modifyRfqVendor(input: UpdateRfqVendorSchema) {
+ unstable_noStore();
+ try {
+ const data = await db.transaction(async (tx) => {
+ const [res] = await updateRfqVendor(tx, input.id, {
+ responseStatus: input.status,
+ });
+ return res;
+ });
+
+ revalidateTag("rfqs-vendor");
+ revalidateTag("rfq-vendors");
+
+ return { data: null, error: null };
+ } catch (err) {
+ return { data: null, error: getErrorMessage(err) };
+ }
+}
+
+export async function createRfqCommentWithAttachments(params: {
+ rfqId: number
+ vendorId?: number | null
+ commentText: string
+ commentedBy: number
+ evaluationId?: number | null
+ files?: File[]
+}) {
+ const { rfqId, vendorId, commentText, commentedBy, evaluationId, files } = params
+
+
+ // 1) 새로운 코멘트 생성
+ const [insertedComment] = await db
+ .insert(rfqComments)
+ .values({
+ rfqId,
+ vendorId: vendorId || null,
+ commentText,
+ commentedBy,
+ evaluationId: evaluationId || null,
+ })
+ .returning({ id: rfqComments.id, createdAt: rfqComments.createdAt }) // id만 반환하도록
+
+ if (!insertedComment) {
+ throw new Error("Failed to create comment")
+ }
+
+ // 2) 첨부파일 처리 (S3 업로드 등은 프로젝트 상황에 따라)
+ 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,
+ 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 []; // 오류 발생 시 빈 배열 반환
+ }
+}
+
+
+// 반환 타입 명시적 정의 - rfqCode가 null일 수 있음을 반영
+export interface BudgetaryRfq {
+ id: number;
+ rfqCode: string | null; // null 허용으로 변경
+ description: string | null;
+ projectId: number | null;
+ projectCode: string | null;
+ projectName: string | null;
+}
+
+interface GetBudgetaryRfqsParams {
+ search?: string;
+ projectId?: number;
+ limit?: number;
+ offset?: number;
+}
+
+type GetBudgetaryRfqsResponse =
+ | { rfqs: BudgetaryRfq[]; totalCount: number; error?: never }
+ | { error: string; rfqs?: never; totalCount: number }
+/**
+ * Budgetary 타입의 RFQ 목록을 가져오는 서버 액션
+ * Purchase RFQ 생성 시 부모 RFQ로 선택할 수 있도록 함
+ * 페이징 및 필터링 기능 포함
+ */
+export async function getBudgetaryRfqs(params: GetBudgetaryRfqsParams = {}): Promise<GetBudgetaryRfqsResponse> {
+ const { search, projectId, limit = 50, offset = 0 } = params;
+ const cacheKey = `budgetary-rfqs-${JSON.stringify(params)}`;
+ return unstable_cache(
+ async () => {
+ try {
+
+ const baseCondition = eq(rfqs.rfqType, RfqType.BUDGETARY);
+
+ 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(where1, where2, baseCondition)
+
+ // 총 개수 조회
+ const [countResult] = await db
+ .select({ count: count() })
+ .from(rfqs)
+ .leftJoin(projects, eq(rfqs.projectId, projects.id))
+ .where(finalWhere);
+
+ // 실제 데이터 조회
+ const budgetaryRfqs = await db
+ .select({
+ id: rfqs.id,
+ rfqCode: rfqs.rfqCode,
+ description: rfqs.description,
+ 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: budgetaryRfqs as BudgetaryRfq[], // 타입 단언으로 호환성 보장
+ totalCount: Number(countResult?.count) || 0
+ };
+ } catch (error) {
+ console.error("Error fetching budgetary RFQs:", error);
+ return {
+ error: "Failed to fetch budgetary RFQs",
+ totalCount: 0
+ };
+ }
+ },
+ [cacheKey],
+ {
+ revalidate: 60, // 1분 캐시
+ tags: ["rfqs-budgetary"],
+ }
+ )();
+}
+
+export async function getAllVendors() {
+ // Adjust the query as needed (add WHERE, ORDER, etc.)
+ const allVendors = await db.select().from(vendors)
+ return allVendors
+}
+
+/**
+ * Server action to associate items from an RFQ with a vendor
+ *
+ * @param rfqId - The ID of the RFQ containing items to associate
+ * @param vendorId - The ID of the vendor to associate items with
+ * @returns Object indicating success or failure
+ */
+export async function addItemToVendors(rfqId: number, vendorIds: number[]) {
+ try {
+ // Input validation
+ if (!vendorIds.length) {
+ return {
+ success: false,
+ error: "No vendors selected"
+ };
+ }
+
+ // 1. Find all itemCodes associated with the given rfqId using select
+ const rfqItemResults = await db
+ .select({ itemCode: rfqItems.itemCode })
+ .from(rfqItems)
+ .where(eq(rfqItems.rfqId, rfqId));
+
+ // Extract itemCodes
+ const itemCodes = rfqItemResults.map(item => item.itemCode);
+
+ if (itemCodes.length === 0) {
+ return {
+ success: false,
+ error: "No items found for this RFQ"
+ };
+ }
+
+ // 2. Find existing vendor-item combinations to avoid duplicates
+ const existingCombinations = await db
+ .select({
+ vendorId: vendorPossibleItems.vendorId,
+ itemCode: vendorPossibleItems.itemCode
+ })
+ .from(vendorPossibleItems)
+ .where(
+ and(
+ inArray(vendorPossibleItems.vendorId, vendorIds),
+ inArray(vendorPossibleItems.itemCode, itemCodes)
+ )
+ );
+
+ // Create a Set of existing combinations for easy lookups
+ const existingSet = new Set();
+ existingCombinations.forEach(combo => {
+ existingSet.add(`${combo.vendorId}-${combo.itemCode}`);
+ });
+
+ // 3. Prepare records to insert (only non-existing combinations)
+ const recordsToInsert = [];
+
+ for (const vendorId of vendorIds) {
+ for (const itemCode of itemCodes) {
+ const key = `${vendorId}-${itemCode}`;
+ if (!existingSet.has(key)) {
+ recordsToInsert.push({
+ vendorId,
+ itemCode,
+ // createdAt and updatedAt will be set by defaultNow()
+ });
+ }
+ }
+ }
+
+ // 4. Bulk insert if there are records to insert
+ let insertedCount = 0;
+ if (recordsToInsert.length > 0) {
+ const result = await db.insert(vendorPossibleItems).values(recordsToInsert);
+ insertedCount = recordsToInsert.length;
+ }
+
+ // 5. Revalidate to refresh data
+ revalidateTag("rfq-vendors");
+
+ // 6. Return success with counts
+ return {
+ success: true,
+ insertedCount,
+ totalPossibleItems: vendorIds.length * itemCodes.length,
+ vendorCount: vendorIds.length,
+ itemCount: itemCodes.length
+ };
+ } catch (error) {
+ console.error("Error adding items to vendors:", error);
+ return {
+ success: false,
+ error: error instanceof Error ? error.message : "Unknown error"
+ };
+ }
+}
+
+/**
+ * 특정 평가에 대한 TBE 템플릿 파일 목록 조회
+ * evaluationId가 일치하고 vendorId가 null인 파일 목록
+ */
+export async function fetchTbeTemplateFiles(evaluationId: number) {
+
+ console.log(evaluationId, "evaluationId")
+ try {
+ const files = await db
+ .select({
+ id: rfqAttachments.id,
+ fileName: rfqAttachments.fileName,
+ filePath: rfqAttachments.filePath,
+ createdAt: rfqAttachments.createdAt,
+ })
+ .from(rfqAttachments)
+ .where(
+ and(
+ isNull(rfqAttachments.commentId),
+ isNull(rfqAttachments.vendorId),
+ eq(rfqAttachments.evaluationId, evaluationId),
+ // eq(rfqAttachments.vendorId, vendorId),
+
+ )
+ )
+
+ return { files, error: null }
+ } catch (error) {
+ console.error("Error fetching TBE template files:", error)
+ return {
+ files: [],
+ error: "템플릿 파일을 가져오는 중 오류가 발생했습니다."
+ }
+ }
+}
+
+/**
+ * 특정 TBE 템플릿 파일 다운로드를 위한 정보 조회
+ */
+export async function getTbeTemplateFileInfo(fileId: number) {
+ try {
+ const file = await db
+ .select({
+ fileName: rfqAttachments.fileName,
+ filePath: rfqAttachments.filePath,
+ })
+ .from(rfqAttachments)
+ .where(eq(rfqAttachments.id, fileId))
+ .limit(1)
+
+ if (!file.length) {
+ return { file: null, error: "파일을 찾을 수 없습니다." }
+ }
+
+ return { file: file[0], error: null }
+ } catch (error) {
+ console.error("Error getting TBE template file info:", error)
+ return {
+ file: null,
+ error: "파일 정보를 가져오는 중 오류가 발생했습니다."
+ }
+ }
+}
+
+/**
+ * TBE 응답 파일 업로드 처리
+ */
+export async function uploadTbeResponseFile(formData: FormData) {
+ try {
+ const file = formData.get("file") as File
+ const rfqId = parseInt(formData.get("rfqId") as string)
+ const vendorId = parseInt(formData.get("vendorId") as string)
+ const evaluationId = parseInt(formData.get("evaluationId") as string)
+ const vendorResponseId = parseInt(formData.get("vendorResponseId") as string)
+
+ if (!file || !rfqId || !vendorId || !evaluationId) {
+ return {
+ success: false,
+ error: "필수 필드가 누락되었습니다."
+ }
+ }
+
+ // 타임스탬프 기반 고유 파일명 생성
+ const timestamp = Date.now()
+ const originalName = file.name
+ const fileExtension = originalName.split(".").pop()
+ const fileName = `${originalName.split(".")[0]}-${timestamp}.${fileExtension}`
+
+ // 업로드 디렉토리 및 경로 정의
+ const uploadDir = join(process.cwd(), "rfq", "tbe-responses")
+
+ // 디렉토리가 없으면 생성
+ try {
+ await mkdir(uploadDir, { recursive: true })
+ } catch (error) {
+ // 이미 존재하면 무시
+ }
+
+ const filePath = join(uploadDir, fileName)
+
+ // 파일을 버퍼로 변환
+ const bytes = await file.arrayBuffer()
+ const buffer = Buffer.from(bytes)
+
+ // 파일을 서버에 저장
+ await writeFile(filePath, buffer)
+
+ // 먼저 vendorTechnicalResponses 테이블에 엔트리 생성
+ const technicalResponse = await db.insert(vendorTechnicalResponses)
+ .values({
+ responseId: vendorResponseId,
+ summary: "TBE 응답 파일 업로드", // 필요에 따라 수정
+ notes: `파일명: ${originalName}`,
+ })
+ .returning({ id: vendorTechnicalResponses.id });
+
+ // 생성된 기술 응답 ID 가져오기
+ const technicalResponseId = technicalResponse[0].id;
+
+ // 파일 정보를 데이터베이스에 저장
+ const dbFilePath = `/rfq/tbe-responses/${fileName}`
+
+ // vendorResponseAttachments 테이블 스키마에 맞게 데이터 삽입
+ await db.insert(vendorResponseAttachments)
+ .values({
+ // 오류 메시지를 기반으로 올바른 필드 이름 사용
+ // 테이블 스키마에 정의된 필드만 포함해야 함
+ responseId: vendorResponseId,
+ technicalResponseId: technicalResponseId,
+ // vendorId와 evaluationId 필드가 테이블에 있다면 포함, 없다면 제거
+ // vendorId: vendorId,
+ // evaluationId: evaluationId,
+ fileName: originalName,
+ filePath: dbFilePath,
+ uploadedAt: new Date(),
+ });
+
+ // 경로 재검증 (캐시된 데이터 새로고침)
+ revalidatePath(`/rfq/${rfqId}/tbe`)
+ revalidateTag(`tbe-vendors-${vendorId}`)
+
+ return {
+ success: true,
+ message: "파일이 성공적으로 업로드되었습니다."
+ }
+ } catch (error) {
+ console.error("Error uploading file:", error)
+ return {
+ success: false,
+ error: "파일 업로드에 실패했습니다."
+ }
+ }
+}
+
+export async function getTbeSubmittedFiles(responseId: number) {
+ try {
+ // First, get the technical response IDs where vendorResponseId matches responseId
+ const technicalResponses = await db
+ .select({
+ id: vendorTechnicalResponses.id,
+ })
+ .from(vendorTechnicalResponses)
+ .where(
+ eq(vendorTechnicalResponses.responseId, responseId)
+ )
+
+ if (technicalResponses.length === 0) {
+ return { files: [], error: null }
+ }
+
+ // Extract the IDs from the result
+ const technicalResponseIds = technicalResponses.map(tr => tr.id)
+
+ // Then get attachments where technicalResponseId matches any of the IDs we found
+ const files = await db
+ .select({
+ id: vendorResponseAttachments.id,
+ fileName: vendorResponseAttachments.fileName,
+ filePath: vendorResponseAttachments.filePath,
+ uploadedAt: vendorResponseAttachments.uploadedAt,
+ fileType: vendorResponseAttachments.fileType,
+ attachmentType: vendorResponseAttachments.attachmentType,
+ description: vendorResponseAttachments.description,
+ })
+ .from(vendorResponseAttachments)
+ .where(
+ inArray(vendorResponseAttachments.technicalResponseId, technicalResponseIds)
+ )
+ .orderBy(vendorResponseAttachments.uploadedAt)
+
+ return { files, error: null }
+ } catch (error) {
+ return { files: [], error: 'Failed to fetch TBE submitted files' }
+ }
+}
+
+
+
+export async function getTbeFilesForVendor(rfqId: number, vendorId: number) {
+ try {
+ // Step 1: Get responseId from vendor_responses table
+ const response = await db
+ .select({
+ id: vendorResponses.id,
+ })
+ .from(vendorResponses)
+ .where(
+ and(
+ eq(vendorResponses.rfqId, rfqId),
+ eq(vendorResponses.vendorId, vendorId)
+ )
+ )
+ .limit(1);
+
+ if (!response || response.length === 0) {
+ return { files: [], error: 'No vendor response found' };
+ }
+
+ const responseId = response[0].id;
+
+ // Step 2: Get the technical response IDs
+ const technicalResponses = await db
+ .select({
+ id: vendorTechnicalResponses.id,
+ })
+ .from(vendorTechnicalResponses)
+ .where(
+ eq(vendorTechnicalResponses.responseId, responseId)
+ );
+
+ if (technicalResponses.length === 0) {
+ return { files: [], error: null };
+ }
+
+ // Extract the IDs from the result
+ const technicalResponseIds = technicalResponses.map(tr => tr.id);
+
+ // Step 3: Get attachments where technicalResponseId matches any of the IDs
+ const files = await db
+ .select({
+ id: vendorResponseAttachments.id,
+ fileName: vendorResponseAttachments.fileName,
+ filePath: vendorResponseAttachments.filePath,
+ uploadedAt: vendorResponseAttachments.uploadedAt,
+ fileType: vendorResponseAttachments.fileType,
+ attachmentType: vendorResponseAttachments.attachmentType,
+ description: vendorResponseAttachments.description,
+ })
+ .from(vendorResponseAttachments)
+ .where(
+ inArray(vendorResponseAttachments.technicalResponseId, technicalResponseIds)
+ )
+ .orderBy(vendorResponseAttachments.uploadedAt);
+
+ return { files, error: null };
+ } catch (error) {
+ return { files: [], error: 'Failed to fetch vendor files' };
+ }
+}
+
+export async function getAllTBE(input: GetTBESchema) {
+ return unstable_cache(
+ async () => {
+ // 1) 페이징
+ const offset = ((input.page ?? 1) - 1) * (input.perPage ?? 10)
+ const limit = input.perPage ?? 10
+
+ // 2) 고급 필터
+ const advancedWhere = filterColumns({
+ table: vendorTbeView,
+ filters: input.filters ?? [],
+ joinOperator: input.joinOperator ?? "and",
+ })
+
+ // 3) 글로벌 검색
+ let globalWhere
+ if (input.search) {
+ const s = `%${input.search}%`
+ globalWhere = or(
+ sql`${vendorTbeView.vendorName} ILIKE ${s}`,
+ sql`${vendorTbeView.vendorCode} ILIKE ${s}`,
+ sql`${vendorTbeView.email} ILIKE ${s}`,
+ sql`${vendorTbeView.rfqCode} ILIKE ${s}`,
+ sql`${vendorTbeView.projectCode} ILIKE ${s}`,
+ sql`${vendorTbeView.projectName} ILIKE ${s}`
+ )
+ }
+
+ // 4) REJECTED 아니거나 NULL
+ const notRejected = or(
+ ne(vendorTbeView.rfqVendorStatus, "REJECTED"),
+ isNull(vendorTbeView.rfqVendorStatus)
+ )
+
+ // 5) rfqType 필터 추가
+ const rfqTypeFilter = input.rfqType ? eq(vendorTbeView.rfqType, input.rfqType) : undefined
+
+ // 6) finalWhere - rfqType 필터 추가
+ const finalWhere = and(
+ notRejected,
+ advancedWhere,
+ globalWhere,
+ rfqTypeFilter // 새로 추가된 rfqType 필터
+ )
+
+ // 6) 정렬
+ const orderBy = input.sort?.length
+ ? input.sort.map((s) => {
+ const col = (vendorTbeView as any)[s.id]
+ return s.desc ? desc(col) : asc(col)
+ })
+ : [desc(vendorTbeView.rfqId), asc(vendorTbeView.vendorId)] // Default sort by newest RFQ first
+
+ // 7) 메인 SELECT
+ const [rows, total] = await db.transaction(async (tx) => {
+ const data = await tx
+ .select({
+ // 원하는 컬럼들
+ id: vendorTbeView.vendorId,
+ tbeId: vendorTbeView.tbeId,
+ vendorId: vendorTbeView.vendorId,
+ vendorName: vendorTbeView.vendorName,
+ vendorCode: vendorTbeView.vendorCode,
+ address: vendorTbeView.address,
+ country: vendorTbeView.country,
+ email: vendorTbeView.email,
+ website: vendorTbeView.website,
+ vendorStatus: vendorTbeView.vendorStatus,
+
+ rfqId: vendorTbeView.rfqId,
+ rfqCode: vendorTbeView.rfqCode,
+ projectCode: vendorTbeView.projectCode,
+ projectName: vendorTbeView.projectName,
+ description: vendorTbeView.description,
+ dueDate: vendorTbeView.dueDate,
+
+ rfqVendorStatus: vendorTbeView.rfqVendorStatus,
+ rfqVendorUpdated: vendorTbeView.rfqVendorUpdated,
+
+ tbeResult: vendorTbeView.tbeResult,
+ tbeNote: vendorTbeView.tbeNote,
+ tbeUpdated: vendorTbeView.tbeUpdated,
+ })
+ .from(vendorTbeView)
+ .where(finalWhere)
+ .orderBy(...orderBy)
+ .offset(offset)
+ .limit(limit)
+
+ const [{ count }] = await tx
+ .select({ count: sql<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: vendorCbeView,
+ filters: input.filters ?? [],
+ joinOperator: input.joinOperator ?? "and",
+ });
+
+ // [3] 글로벌 검색
+ let globalWhere;
+ if (input.search) {
+ const s = `%${input.search}%`;
+ globalWhere = or(
+ sql`${vendorCbeView.vendorName} ILIKE ${s}`,
+ sql`${vendorCbeView.vendorCode} ILIKE ${s}`,
+ sql`${vendorCbeView.email} ILIKE ${s}`
+ );
+ }
+
+ // [4] REJECTED 아니거나 NULL
+ const notRejected = or(
+ ne(vendorCbeView.rfqVendorStatus, "REJECTED"),
+ isNull(vendorCbeView.rfqVendorStatus)
+ );
+
+ // [5] 최종 where
+ const finalWhere = and(
+ eq(vendorCbeView.rfqId, rfqId),
+ notRejected,
+ advancedWhere,
+ globalWhere
+ );
+
+ // [6] 정렬
+ const orderBy = input.sort?.length
+ ? input.sort.map((s) => {
+ // vendor_cbe_view 컬럼 중 정렬 대상이 되는 것만 매핑
+ const col = (vendorCbeView as any)[s.id];
+ return s.desc ? desc(col) : asc(col);
+ })
+ : [asc(vendorCbeView.vendorId)];
+
+ // [7] 메인 SELECT
+ const [rows, total] = await db.transaction(async (tx) => {
+ const data = await tx
+ .select({
+ // 필요한 컬럼만 추출
+ id: vendorCbeView.vendorId,
+ cbeId: vendorCbeView.cbeId,
+ vendorId: vendorCbeView.vendorId,
+ vendorName: vendorCbeView.vendorName,
+ vendorCode: vendorCbeView.vendorCode,
+ address: vendorCbeView.address,
+ country: vendorCbeView.country,
+ email: vendorCbeView.email,
+ website: vendorCbeView.website,
+ vendorStatus: vendorCbeView.vendorStatus,
+
+ rfqId: vendorCbeView.rfqId,
+ rfqCode: vendorCbeView.rfqCode,
+ projectCode: vendorCbeView.projectCode,
+ projectName: vendorCbeView.projectName,
+ description: vendorCbeView.description,
+ dueDate: vendorCbeView.dueDate,
+
+ rfqVendorStatus: vendorCbeView.rfqVendorStatus,
+ rfqVendorUpdated: vendorCbeView.rfqVendorUpdated,
+
+ cbeResult: vendorCbeView.cbeResult,
+ cbeNote: vendorCbeView.cbeNote,
+ cbeUpdated: vendorCbeView.cbeUpdated,
+
+ // 상업평가 정보
+ totalCost: vendorCbeView.totalCost,
+ currency: vendorCbeView.currency,
+ paymentTerms: vendorCbeView.paymentTerms,
+ incoterms: vendorCbeView.incoterms,
+ deliverySchedule: vendorCbeView.deliverySchedule,
+ })
+ .from(vendorCbeView)
+ .where(finalWhere)
+ .orderBy(...orderBy)
+ .offset(offset)
+ .limit(limit);
+
+ const [{ count }] = await tx
+ .select({ count: sql<number>`count(*)`.as("count") })
+ .from(vendorCbeView)
+ .where(finalWhere);
+
+ return [data, Number(count)];
+ });
+
+ if (!rows.length) {
+ return { data: [], pageCount: 0 };
+ }
+
+ // [8] Comments 조회
+ // TBE 에서는 rfqComments + rfqEvaluations(evalType="TBE") 를 조인했지만,
+ // CBE는 cbeEvaluations 또는 evalType="CBE"를 기준으로 바꾸면 됩니다.
+ // 만약 cbeEvaluations.id 를 evaluationId 로 참조한다면 아래와 같이 innerJoin:
+ const distinctVendorIds = [...new Set(rows.map((r) => r.vendorId))];
+
+ const commAll = await db
+ .select({
+ id: rfqComments.id,
+ commentText: rfqComments.commentText,
+ vendorId: rfqComments.vendorId,
+ evaluationId: rfqComments.evaluationId,
+ createdAt: rfqComments.createdAt,
+ commentedBy: rfqComments.commentedBy,
+ // cbeEvaluations에는 evalType 컬럼이 별도로 없을 수도 있음(프로젝트 구조에 맞게 수정)
+ // evalType: cbeEvaluations.evalType,
+ })
+ .from(rfqComments)
+ .innerJoin(
+ cbeEvaluations,
+ eq(cbeEvaluations.id, rfqComments.evaluationId)
+ )
+ .where(
+ and(
+ isNotNull(rfqComments.evaluationId),
+ eq(rfqComments.rfqId, rfqId),
+ inArray(rfqComments.vendorId, distinctVendorIds)
+ )
+ );
+
+ // vendorId -> comments grouping
+ const commByVendorId = new Map<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] CBE 파일 조회 (프로젝트에 따라 구조가 달라질 수 있음)
+ // - TBE는 vendorTechnicalResponses 기준
+ // - CBE는 vendorCommercialResponses(가정) 등이 있을 수 있음
+ // - 여기서는 예시로 "동일한 vendorResponses + vendorResponseAttachments" 라고 가정
+ // Step 1: vendorResponses 가져오기 (rfqId + vendorIds)
+ const responsesAll = await db
+ .select({
+ id: vendorResponses.id,
+ vendorId: vendorResponses.vendorId,
+ })
+ .from(vendorResponses)
+ .where(
+ and(
+ eq(vendorResponses.rfqId, rfqId),
+ inArray(vendorResponses.vendorId, distinctVendorIds)
+ )
+ );
+
+ // Group responses by vendorId
+ const responsesByVendorId = new Map<number, number[]>();
+ for (const resp of responsesAll) {
+ if (!responsesByVendorId.has(resp.vendorId)) {
+ responsesByVendorId.set(resp.vendorId, []);
+ }
+ responsesByVendorId.get(resp.vendorId)!.push(resp.id);
+ }
+
+ // Step 2: responseIds
+ const allResponseIds = responsesAll.map((r) => r.id);
+
+
+ const commercialResponsesAll = await db
+ .select({
+ id: vendorCommercialResponses.id,
+ responseId: vendorCommercialResponses.responseId,
+ })
+ .from(vendorCommercialResponses)
+ .where(inArray(vendorCommercialResponses.responseId, allResponseIds));
+
+ const commercialResponseIdsByResponseId = new Map<number, number[]>();
+ for (const cr of commercialResponsesAll) {
+ if (!commercialResponseIdsByResponseId.has(cr.responseId)) {
+ commercialResponseIdsByResponseId.set(cr.responseId, []);
+ }
+ commercialResponseIdsByResponseId.get(cr.responseId)!.push(cr.id);
+ }
+
+ const allCommercialResponseIds = commercialResponsesAll.map((cr) => cr.id);
+
+
+ // 여기서는 예시로 TBE와 마찬가지로 vendorResponseAttachments를
+ // 직접 responseId로 관리한다고 가정(혹은 commercialResponseId로 연결)
+ // Step 3: vendorResponseAttachments 조회
+ const filesAll = await db
+ .select({
+ id: vendorResponseAttachments.id,
+ fileName: vendorResponseAttachments.fileName,
+ filePath: vendorResponseAttachments.filePath,
+ responseId: vendorResponseAttachments.responseId,
+ fileType: vendorResponseAttachments.fileType,
+ attachmentType: vendorResponseAttachments.attachmentType,
+ description: vendorResponseAttachments.description,
+ uploadedAt: vendorResponseAttachments.uploadedAt,
+ uploadedBy: vendorResponseAttachments.uploadedBy,
+ })
+ .from(vendorResponseAttachments)
+ .where(
+ and(
+ inArray(vendorResponseAttachments.responseId, allCommercialResponseIds),
+ isNotNull(vendorResponseAttachments.responseId)
+ )
+ );
+
+ // Step 4: responseId -> files
+ const filesByResponseId = new Map<number, any[]>();
+ for (const file of filesAll) {
+ const rid = file.responseId!;
+ if (!filesByResponseId.has(rid)) {
+ filesByResponseId.set(rid, []);
+ }
+ filesByResponseId.get(rid)!.push({
+ id: file.id,
+ fileName: file.fileName,
+ filePath: file.filePath,
+ fileType: file.fileType,
+ attachmentType: file.attachmentType,
+ description: file.description,
+ uploadedAt: file.uploadedAt,
+ uploadedBy: file.uploadedBy,
+ });
+ }
+
+ // Step 5: vendorId -> files
+ const filesByVendorId = new Map<number, any[]>();
+ for (const [vendorId, responseIds] of responsesByVendorId.entries()) {
+ filesByVendorId.set(vendorId, []);
+ for (const responseId of responseIds) {
+ const files = filesByResponseId.get(responseId) || [];
+ filesByVendorId.get(vendorId)!.push(...files);
+ }
+ }
+
+ // [10] 최종 데이터 합치기
+ const final = rows.map((row) => ({
+ ...row,
+ dueDate: row.dueDate ? new Date(row.dueDate) : null,
+ comments: commByVendorId.get(row.vendorId) ?? [],
+ files: filesByVendorId.get(row.vendorId) ?? [],
+ }));
+
+ const pageCount = Math.ceil(total / limit);
+ return { data: final, pageCount };
+ },
+ // 캐싱 키 & 옵션
+ [JSON.stringify({ input, rfqId })],
+ {
+ revalidate: 3600,
+ tags: ["cbe-vendors"],
+ }
+ )();
+} \ No newline at end of file
generated by cgit v1.2.3 (git 2.39.1) at 2025-12-07 18:59:08 +0000