diff options
Diffstat (limited to 'lib/b-rfq/service.ts')
| -rw-r--r-- | lib/b-rfq/service.ts | 2025 |
1 files changed, 1192 insertions, 833 deletions
diff --git a/lib/b-rfq/service.ts b/lib/b-rfq/service.ts index 5a65872b..4def634b 100644 --- a/lib/b-rfq/service.ts +++ b/lib/b-rfq/service.ts @@ -1,17 +1,21 @@ 'use server' -import { revalidateTag, unstable_cache ,unstable_noStore} from "next/cache" -import {count, desc, asc, and, or, gte, lte, ilike, eq, inArray, sql } from "drizzle-orm" +import { revalidatePath, revalidateTag, unstable_cache, unstable_noStore } from "next/cache" +import { count, desc, asc, and, or, gte, lte, ilike, eq, inArray, sql } from "drizzle-orm" import { filterColumns } from "@/lib/filter-columns" import db from "@/db/db" -import { vendorResponseDetailView, +import { + vendorResponseDetailView, attachmentRevisionHistoryView, rfqProgressSummaryView, - vendorResponseAttachmentsEnhanced ,Incoterm, RfqDashboardView, Vendor, VendorAttachmentResponse, bRfqAttachmentRevisions, bRfqs, bRfqsAttachments, incoterms, initialRfq, initialRfqDetailView, projects, users, vendorAttachmentResponses, vendors, - vendorResponseAttachmentsB} from "@/db/schema" // 실제 스키마 import 경로에 맞게 수정 + vendorResponseAttachmentsEnhanced, Incoterm, RfqDashboardView, Vendor, VendorAttachmentResponse, bRfqAttachmentRevisions, bRfqs, bRfqsAttachments, incoterms, initialRfq, initialRfqDetailView, projects, users, vendorAttachmentResponses, vendors, + vendorResponseAttachmentsB, + finalRfq, + finalRfqDetailView +} from "@/db/schema" // 실제 스키마 import 경로에 맞게 수정 import { rfqDashboardView } from "@/db/schema" // 뷰 import import type { SQL } from "drizzle-orm" -import { AttachmentRecord, BulkEmailInput, CreateRfqInput, DeleteAttachmentsInput, GetInitialRfqDetailSchema, GetRFQDashboardSchema, GetRfqAttachmentsSchema, GetVendorResponsesSchema, RemoveInitialRfqsSchema, RequestRevisionResult, ResponseStatus, UpdateInitialRfqSchema, VendorRfqResponseSummary, attachmentRecordSchema, bulkEmailSchema, createRfqServerSchema, deleteAttachmentsSchema, removeInitialRfqsSchema, requestRevisionSchema, updateInitialRfqSchema } from "./validations" +import { AttachmentRecord, BulkEmailInput, CreateRfqInput, DeleteAttachmentsInput, GetInitialRfqDetailSchema, GetRFQDashboardSchema, GetRfqAttachmentsSchema, GetVendorResponsesSchema, RemoveInitialRfqsSchema, RequestRevisionResult, ResponseStatus, ShortListConfirmInput, UpdateInitialRfqSchema, VendorRfqResponseSummary, attachmentRecordSchema, bulkEmailSchema, createRfqServerSchema, deleteAttachmentsSchema, removeInitialRfqsSchema, requestRevisionSchema, updateInitialRfqSchema, shortListConfirmSchema, GetFinalRfqDetailSchema } from "./validations" import { getServerSession } from "next-auth/next" import { authOptions } from "@/app/api/auth/[...nextauth]/route" import { unlink } from "fs/promises" @@ -21,7 +25,7 @@ import { sendEmail } from "../mail/sendEmail" import { RfqType } from "../rfqs/validations" const tag = { - initialRfqDetail:"initial-rfq", + initialRfqDetail: "initial-rfq", rfqDashboard: 'rfq-dashboard', rfq: (id: number) => `rfq-${id}`, rfqAttachments: (rfqId: number) => `rfq-attachments-${rfqId}`, @@ -34,122 +38,122 @@ const tag = { export async function getRFQDashboard(input: GetRFQDashboardSchema) { - try { - const offset = (input.page - 1) * input.perPage; - - const rfqFilterMapping = createRFQFilterMapping(); - const joinedTables = getRFQJoinedTables(); - - console.log(input, "견적 인풋") - - // 1) 고급 필터 조건 - let advancedWhere: SQL<unknown> | undefined = undefined; - if (input.filters && input.filters.length > 0) { - advancedWhere = filterColumns({ - table: rfqDashboardView, - filters: input.filters, - joinOperator: input.joinOperator || 'and', - joinedTables, - customColumnMapping: rfqFilterMapping, - }); - } + try { + const offset = (input.page - 1) * input.perPage; + + const rfqFilterMapping = createRFQFilterMapping(); + const joinedTables = getRFQJoinedTables(); + + console.log(input, "견적 인풋") + + // 1) 고급 필터 조건 + let advancedWhere: SQL<unknown> | undefined = undefined; + if (input.filters && input.filters.length > 0) { + advancedWhere = filterColumns({ + table: rfqDashboardView, + filters: input.filters, + joinOperator: input.joinOperator || 'and', + joinedTables, + customColumnMapping: rfqFilterMapping, + }); + } - // 2) 기본 필터 조건 - let basicWhere: SQL<unknown> | undefined = undefined; - if (input.basicFilters && input.basicFilters.length > 0) { - basicWhere = filterColumns({ - table: rfqDashboardView, - filters: input.basicFilters, - joinOperator: input.basicJoinOperator || 'and', - joinedTables, - customColumnMapping: rfqFilterMapping, - }); - } + // 2) 기본 필터 조건 + let basicWhere: SQL<unknown> | undefined = undefined; + if (input.basicFilters && input.basicFilters.length > 0) { + basicWhere = filterColumns({ + table: rfqDashboardView, + filters: input.basicFilters, + joinOperator: input.basicJoinOperator || 'and', + joinedTables, + customColumnMapping: rfqFilterMapping, + }); + } - // 3) 글로벌 검색 조건 - let globalWhere: SQL<unknown> | undefined = undefined; - if (input.search) { - const s = `%${input.search}%`; + // 3) 글로벌 검색 조건 + let globalWhere: SQL<unknown> | undefined = undefined; + if (input.search) { + const s = `%${input.search}%`; - const validSearchConditions: SQL<unknown>[] = []; + const validSearchConditions: SQL<unknown>[] = []; - const rfqCodeCondition = ilike(rfqDashboardView.rfqCode, s); - if (rfqCodeCondition) validSearchConditions.push(rfqCodeCondition); + const rfqCodeCondition = ilike(rfqDashboardView.rfqCode, s); + if (rfqCodeCondition) validSearchConditions.push(rfqCodeCondition); - const descriptionCondition = ilike(rfqDashboardView.description, s); - if (descriptionCondition) validSearchConditions.push(descriptionCondition); + const descriptionCondition = ilike(rfqDashboardView.description, s); + if (descriptionCondition) validSearchConditions.push(descriptionCondition); - const projectNameCondition = ilike(rfqDashboardView.projectName, s); - if (projectNameCondition) validSearchConditions.push(projectNameCondition); + const projectNameCondition = ilike(rfqDashboardView.projectName, s); + if (projectNameCondition) validSearchConditions.push(projectNameCondition); - const projectCodeCondition = ilike(rfqDashboardView.projectCode, s); - if (projectCodeCondition) validSearchConditions.push(projectCodeCondition); + const projectCodeCondition = ilike(rfqDashboardView.projectCode, s); + if (projectCodeCondition) validSearchConditions.push(projectCodeCondition); - const picNameCondition = ilike(rfqDashboardView.picName, s); - if (picNameCondition) validSearchConditions.push(picNameCondition); + const picNameCondition = ilike(rfqDashboardView.picName, s); + if (picNameCondition) validSearchConditions.push(picNameCondition); - const packageNoCondition = ilike(rfqDashboardView.packageNo, s); - if (packageNoCondition) validSearchConditions.push(packageNoCondition); + const packageNoCondition = ilike(rfqDashboardView.packageNo, s); + if (packageNoCondition) validSearchConditions.push(packageNoCondition); - const packageNameCondition = ilike(rfqDashboardView.packageName, s); - if (packageNameCondition) validSearchConditions.push(packageNameCondition); + const packageNameCondition = ilike(rfqDashboardView.packageName, s); + if (packageNameCondition) validSearchConditions.push(packageNameCondition); - if (validSearchConditions.length > 0) { - globalWhere = or(...validSearchConditions); - } - } + if (validSearchConditions.length > 0) { + globalWhere = or(...validSearchConditions); + } + } - // 6) 최종 WHERE 조건 생성 - const whereConditions: SQL<unknown>[] = []; + // 6) 최종 WHERE 조건 생성 + const whereConditions: SQL<unknown>[] = []; - if (advancedWhere) whereConditions.push(advancedWhere); - if (basicWhere) whereConditions.push(basicWhere); - if (globalWhere) whereConditions.push(globalWhere); + if (advancedWhere) whereConditions.push(advancedWhere); + if (basicWhere) whereConditions.push(basicWhere); + if (globalWhere) whereConditions.push(globalWhere); - const finalWhere = whereConditions.length > 0 ? and(...whereConditions) : undefined; + const finalWhere = whereConditions.length > 0 ? and(...whereConditions) : undefined; - // 7) 전체 데이터 수 조회 - const totalResult = await db - .select({ count: count() }) - .from(rfqDashboardView) - .where(finalWhere); + // 7) 전체 데이터 수 조회 + const totalResult = await db + .select({ count: count() }) + .from(rfqDashboardView) + .where(finalWhere); - const total = totalResult[0]?.count || 0; + const total = totalResult[0]?.count || 0; - if (total === 0) { - return { data: [], pageCount: 0, total: 0 }; - } + if (total === 0) { + return { data: [], pageCount: 0, total: 0 }; + } - console.log(total) + console.log(total) - // 8) 정렬 및 페이징 처리된 데이터 조회 - const orderByColumns = input.sort.map((sort) => { - const column = sort.id as keyof typeof rfqDashboardView.$inferSelect; - return sort.desc ? desc(rfqDashboardView[column]) : asc(rfqDashboardView[column]); - }); + // 8) 정렬 및 페이징 처리된 데이터 조회 + const orderByColumns = input.sort.map((sort) => { + const column = sort.id as keyof typeof rfqDashboardView.$inferSelect; + return sort.desc ? desc(rfqDashboardView[column]) : asc(rfqDashboardView[column]); + }); - if (orderByColumns.length === 0) { - orderByColumns.push(desc(rfqDashboardView.createdAt)); - } + if (orderByColumns.length === 0) { + orderByColumns.push(desc(rfqDashboardView.createdAt)); + } + + const rfqData = await db + .select() + .from(rfqDashboardView) + .where(finalWhere) + .orderBy(...orderByColumns) + .limit(input.perPage) + .offset(offset); + + const pageCount = Math.ceil(total / input.perPage); + + return { data: rfqData, pageCount, total }; + } catch (err) { + console.error("Error in getRFQDashboard:", err); + return { data: [], pageCount: 0, total: 0 }; + } - const rfqData = await db - .select() - .from(rfqDashboardView) - .where(finalWhere) - .orderBy(...orderByColumns) - .limit(input.perPage) - .offset(offset); - - const pageCount = Math.ceil(total / input.perPage); - - return { data: rfqData, pageCount, total }; - } catch (err) { - console.error("Error in getRFQDashboard:", err); - return { data: [], pageCount: 0, total: 0 }; - } - } // 헬퍼 함수들 @@ -311,142 +315,142 @@ export async function getRfqAttachments( input: GetRfqAttachmentsSchema, rfqId: number ) { - try { - const offset = (input.page - 1) * input.perPage + try { + const offset = (input.page - 1) * input.perPage - // Advanced Filter 처리 (메인 테이블 기준) - const advancedWhere = filterColumns({ - table: bRfqsAttachments, - filters: input.filters, - joinOperator: input.joinOperator, - }) + // Advanced Filter 처리 (메인 테이블 기준) + const advancedWhere = filterColumns({ + table: bRfqsAttachments, + filters: input.filters, + joinOperator: input.joinOperator, + }) - // 전역 검색 (첨부파일 + 리비전 파일명 검색) - let globalWhere - if (input.search) { - const s = `%${input.search}%` - globalWhere = or( - ilike(bRfqsAttachments.serialNo, s), - ilike(bRfqsAttachments.description, s), - ilike(bRfqsAttachments.currentRevision, s), - ilike(bRfqAttachmentRevisions.fileName, s), - ilike(bRfqAttachmentRevisions.originalFileName, s) - ) - } + // 전역 검색 (첨부파일 + 리비전 파일명 검색) + let globalWhere + if (input.search) { + const s = `%${input.search}%` + globalWhere = or( + ilike(bRfqsAttachments.serialNo, s), + ilike(bRfqsAttachments.description, s), + ilike(bRfqsAttachments.currentRevision, s), + ilike(bRfqAttachmentRevisions.fileName, s), + ilike(bRfqAttachmentRevisions.originalFileName, s) + ) + } - // 기본 필터 - let basicWhere - if (input.attachmentType.length > 0 || input.fileType.length > 0) { - basicWhere = and( - input.attachmentType.length > 0 - ? inArray(bRfqsAttachments.attachmentType, input.attachmentType) - : undefined, - input.fileType.length > 0 - ? inArray(bRfqAttachmentRevisions.fileType, input.fileType) - : undefined - ) - } + // 기본 필터 + let basicWhere + if (input.attachmentType.length > 0 || input.fileType.length > 0) { + basicWhere = and( + input.attachmentType.length > 0 + ? inArray(bRfqsAttachments.attachmentType, input.attachmentType) + : undefined, + input.fileType.length > 0 + ? inArray(bRfqAttachmentRevisions.fileType, input.fileType) + : undefined + ) + } - // 최종 WHERE 절 - const finalWhere = and( - eq(bRfqsAttachments.rfqId, rfqId), // RFQ ID 필수 조건 - advancedWhere, - globalWhere, - basicWhere - ) + // 최종 WHERE 절 + const finalWhere = and( + eq(bRfqsAttachments.rfqId, rfqId), // RFQ ID 필수 조건 + advancedWhere, + globalWhere, + basicWhere + ) + + // 정렬 (메인 테이블 기준) + const orderBy = input.sort.length > 0 + ? input.sort.map((item) => + item.desc ? desc(bRfqsAttachments[item.id as keyof typeof bRfqsAttachments]) : asc(bRfqsAttachments[item.id as keyof typeof bRfqsAttachments]) + ) + : [desc(bRfqsAttachments.createdAt)] - // 정렬 (메인 테이블 기준) - const orderBy = input.sort.length > 0 - ? input.sort.map((item) => - item.desc ? desc(bRfqsAttachments[item.id as keyof typeof bRfqsAttachments]) : asc(bRfqsAttachments[item.id as keyof typeof bRfqsAttachments]) + // 트랜잭션으로 데이터 조회 + const { data, total } = await db.transaction(async (tx) => { + // 메인 데이터 조회 (첨부파일 + 최신 리비전 조인) + const data = await tx + .select({ + // 첨부파일 메인 정보 + id: bRfqsAttachments.id, + attachmentType: bRfqsAttachments.attachmentType, + serialNo: bRfqsAttachments.serialNo, + rfqId: bRfqsAttachments.rfqId, + currentRevision: bRfqsAttachments.currentRevision, + latestRevisionId: bRfqsAttachments.latestRevisionId, + description: bRfqsAttachments.description, + createdBy: bRfqsAttachments.createdBy, + createdAt: bRfqsAttachments.createdAt, + updatedAt: bRfqsAttachments.updatedAt, + + // 최신 리비전 파일 정보 + fileName: bRfqAttachmentRevisions.fileName, + originalFileName: bRfqAttachmentRevisions.originalFileName, + filePath: bRfqAttachmentRevisions.filePath, + fileSize: bRfqAttachmentRevisions.fileSize, + fileType: bRfqAttachmentRevisions.fileType, + revisionComment: bRfqAttachmentRevisions.revisionComment, + + // 생성자 정보 + createdByName: users.name, + }) + .from(bRfqsAttachments) + .leftJoin( + bRfqAttachmentRevisions, + and( + eq(bRfqsAttachments.latestRevisionId, bRfqAttachmentRevisions.id), + eq(bRfqAttachmentRevisions.isLatest, true) ) - : [desc(bRfqsAttachments.createdAt)] - - // 트랜잭션으로 데이터 조회 - const { data, total } = await db.transaction(async (tx) => { - // 메인 데이터 조회 (첨부파일 + 최신 리비전 조인) - const data = await tx - .select({ - // 첨부파일 메인 정보 - id: bRfqsAttachments.id, - attachmentType: bRfqsAttachments.attachmentType, - serialNo: bRfqsAttachments.serialNo, - rfqId: bRfqsAttachments.rfqId, - currentRevision: bRfqsAttachments.currentRevision, - latestRevisionId: bRfqsAttachments.latestRevisionId, - description: bRfqsAttachments.description, - createdBy: bRfqsAttachments.createdBy, - createdAt: bRfqsAttachments.createdAt, - updatedAt: bRfqsAttachments.updatedAt, - - // 최신 리비전 파일 정보 - fileName: bRfqAttachmentRevisions.fileName, - originalFileName: bRfqAttachmentRevisions.originalFileName, - filePath: bRfqAttachmentRevisions.filePath, - fileSize: bRfqAttachmentRevisions.fileSize, - fileType: bRfqAttachmentRevisions.fileType, - revisionComment: bRfqAttachmentRevisions.revisionComment, - - // 생성자 정보 - createdByName: users.name, - }) - .from(bRfqsAttachments) - .leftJoin( - bRfqAttachmentRevisions, - and( - eq(bRfqsAttachments.latestRevisionId, bRfqAttachmentRevisions.id), - eq(bRfqAttachmentRevisions.isLatest, true) - ) - ) - .leftJoin(users, eq(bRfqsAttachments.createdBy, users.id)) - .where(finalWhere) - .orderBy(...orderBy) - .limit(input.perPage) - .offset(offset) - - // 전체 개수 조회 - const totalResult = await tx - .select({ count: count() }) - .from(bRfqsAttachments) - .leftJoin( - bRfqAttachmentRevisions, - eq(bRfqsAttachments.latestRevisionId, bRfqAttachmentRevisions.id) - ) - .where(finalWhere) - - const total = totalResult[0]?.count ?? 0 + ) + .leftJoin(users, eq(bRfqsAttachments.createdBy, users.id)) + .where(finalWhere) + .orderBy(...orderBy) + .limit(input.perPage) + .offset(offset) + + // 전체 개수 조회 + const totalResult = await tx + .select({ count: count() }) + .from(bRfqsAttachments) + .leftJoin( + bRfqAttachmentRevisions, + eq(bRfqsAttachments.latestRevisionId, bRfqAttachmentRevisions.id) + ) + .where(finalWhere) - return { data, total } - }) + const total = totalResult[0]?.count ?? 0 - const pageCount = Math.ceil(total / input.perPage) + return { data, total } + }) - // 각 첨부파일별 벤더 응답 통계 조회 - const attachmentIds = data.map(item => item.id) - let responseStatsMap: Record<number, any> = {} + const pageCount = Math.ceil(total / input.perPage) - if (attachmentIds.length > 0) { - responseStatsMap = await getAttachmentResponseStats(attachmentIds) - } + // 각 첨부파일별 벤더 응답 통계 조회 + const attachmentIds = data.map(item => item.id) + let responseStatsMap: Record<number, any> = {} - // 통계 데이터 병합 - const dataWithStats = data.map(attachment => ({ - ...attachment, - responseStats: responseStatsMap[attachment.id] || { - totalVendors: 0, - respondedCount: 0, - pendingCount: 0, - waivedCount: 0, - responseRate: 0 - } - })) + if (attachmentIds.length > 0) { + responseStatsMap = await getAttachmentResponseStats(attachmentIds) + } - return { data: dataWithStats, pageCount } - } catch (err) { - console.error("getRfqAttachments error:", err) - return { data: [], pageCount: 0 } + // 통계 데이터 병합 + const dataWithStats = data.map(attachment => ({ + ...attachment, + responseStats: responseStatsMap[attachment.id] || { + totalVendors: 0, + respondedCount: 0, + pendingCount: 0, + waivedCount: 0, + responseRate: 0 } - + })) + + return { data: dataWithStats, pageCount } + } catch (err) { + console.error("getRfqAttachments error:", err) + return { data: [], pageCount: 0 } + } + } // 첨부파일별 벤더 응답 통계 조회 @@ -529,7 +533,7 @@ export async function getVendorResponsesForAttachment( // 2. 각 응답에 대한 파일 정보 가져오기 const responseIds = responses.map(r => r.id); - + let responseFiles: any[] = []; if (responseIds.length > 0) { responseFiles = await db @@ -657,14 +661,14 @@ export async function requestTbe(rfqId: number, attachmentIds?: number[]) { await db.transaction(async (tx) => { const [updatedRfq] = await tx - .update(bRfqs) - .set({ - status: "TBE started", - updatedBy: Number(session.user.id), - updatedAt: new Date(), - }) - .where(eq(bRfqs.id, rfqId)) - .returning() + .update(bRfqs) + .set({ + status: "TBE started", + updatedBy: Number(session.user.id), + updatedAt: new Date(), + }) + .where(eq(bRfqs.id, rfqId)) + .returning() // 각 첨부파일에 대해 벤더 응답 레코드 생성 또는 업데이트 for (const attachment of targetAttachments) { @@ -673,7 +677,7 @@ export async function requestTbe(rfqId: number, attachmentIds?: number[]) { } }) - + const attachmentCount = targetAttachments.length const attachmentList = targetAttachments .map(a => `${a.serialNo} (${a.currentRevision})`) @@ -776,7 +780,7 @@ export async function addRfqAttachmentRecord(record: AttachmentRecord) { return { attachment, revision } }) - return { + return { success: true, message: `파일이 성공적으로 등록되었습니다. (시리얼: ${result.attachment.serialNo}, 리비전: Rev.0)`, attachment: result.attachment, @@ -884,7 +888,7 @@ export async function addRevisionToAttachment( return inserted; }); - + return { success: true, @@ -903,39 +907,39 @@ export async function addRevisionToAttachment( // 특정 첨부파일의 모든 리비전 조회 export async function getAttachmentRevisions(attachmentId: number) { - try { - const revisions = await db - .select({ - id: bRfqAttachmentRevisions.id, - revisionNo: bRfqAttachmentRevisions.revisionNo, - fileName: bRfqAttachmentRevisions.fileName, - originalFileName: bRfqAttachmentRevisions.originalFileName, - filePath: bRfqAttachmentRevisions.filePath, - fileSize: bRfqAttachmentRevisions.fileSize, - fileType: bRfqAttachmentRevisions.fileType, - revisionComment: bRfqAttachmentRevisions.revisionComment, - isLatest: bRfqAttachmentRevisions.isLatest, - createdBy: bRfqAttachmentRevisions.createdBy, - createdAt: bRfqAttachmentRevisions.createdAt, - createdByName: users.name, - }) - .from(bRfqAttachmentRevisions) - .leftJoin(users, eq(bRfqAttachmentRevisions.createdBy, users.id)) - .where(eq(bRfqAttachmentRevisions.attachmentId, attachmentId)) - .orderBy(desc(bRfqAttachmentRevisions.createdAt)) + try { + const revisions = await db + .select({ + id: bRfqAttachmentRevisions.id, + revisionNo: bRfqAttachmentRevisions.revisionNo, + fileName: bRfqAttachmentRevisions.fileName, + originalFileName: bRfqAttachmentRevisions.originalFileName, + filePath: bRfqAttachmentRevisions.filePath, + fileSize: bRfqAttachmentRevisions.fileSize, + fileType: bRfqAttachmentRevisions.fileType, + revisionComment: bRfqAttachmentRevisions.revisionComment, + isLatest: bRfqAttachmentRevisions.isLatest, + createdBy: bRfqAttachmentRevisions.createdBy, + createdAt: bRfqAttachmentRevisions.createdAt, + createdByName: users.name, + }) + .from(bRfqAttachmentRevisions) + .leftJoin(users, eq(bRfqAttachmentRevisions.createdBy, users.id)) + .where(eq(bRfqAttachmentRevisions.attachmentId, attachmentId)) + .orderBy(desc(bRfqAttachmentRevisions.createdAt)) - return { - success: true, - revisions, - } - } catch (error) { - console.error("getAttachmentRevisions error:", error) - return { - success: false, - message: "리비전 조회 중 오류가 발생했습니다.", - revisions: [], - } - } + return { + success: true, + revisions, + } + } catch (error) { + console.error("getAttachmentRevisions error:", error) + return { + success: false, + message: "리비전 조회 중 오류가 발생했습니다.", + revisions: [], + } + } } @@ -1003,7 +1007,7 @@ export async function deleteRfqAttachments(input: DeleteAttachmentsInput) { } }) - + return { success: true, message: `${result.deletedCount}개의 첨부파일이 삭제되었습니다.`, @@ -1012,7 +1016,7 @@ export async function deleteRfqAttachments(input: DeleteAttachmentsInput) { } catch (error) { console.error("deleteRfqAttachments error:", error) - + return { success: false, message: error instanceof Error ? error.message : "첨부파일 삭제 중 오류가 발생했습니다.", @@ -1026,119 +1030,119 @@ export async function deleteRfqAttachments(input: DeleteAttachmentsInput) { export async function getInitialRfqDetail(input: GetInitialRfqDetailSchema, rfqId?: number) { - try { - const offset = (input.page - 1) * input.perPage; - - // 1) 고급 필터 조건 - let advancedWhere: SQL<unknown> | undefined = undefined; - if (input.filters && input.filters.length > 0) { - advancedWhere = filterColumns({ - table: initialRfqDetailView, - filters: input.filters, - joinOperator: input.joinOperator || 'and', - }); - } + try { + const offset = (input.page - 1) * input.perPage; + + // 1) 고급 필터 조건 + let advancedWhere: SQL<unknown> | undefined = undefined; + if (input.filters && input.filters.length > 0) { + advancedWhere = filterColumns({ + table: initialRfqDetailView, + filters: input.filters, + joinOperator: input.joinOperator || 'and', + }); + } - // 2) 기본 필터 조건 - let basicWhere: SQL<unknown> | undefined = undefined; - if (input.basicFilters && input.basicFilters.length > 0) { - basicWhere = filterColumns({ - table: initialRfqDetailView, - filters: input.basicFilters, - joinOperator: input.basicJoinOperator || 'and', - }); - } + // 2) 기본 필터 조건 + let basicWhere: SQL<unknown> | undefined = undefined; + if (input.basicFilters && input.basicFilters.length > 0) { + basicWhere = filterColumns({ + table: initialRfqDetailView, + filters: input.basicFilters, + joinOperator: input.basicJoinOperator || 'and', + }); + } - let rfqIdWhere: SQL<unknown> | undefined = undefined; - if (rfqId) { - rfqIdWhere = eq(initialRfqDetailView.rfqId, rfqId); - } + let rfqIdWhere: SQL<unknown> | undefined = undefined; + if (rfqId) { + rfqIdWhere = eq(initialRfqDetailView.rfqId, rfqId); + } - // 3) 글로벌 검색 조건 - let globalWhere: SQL<unknown> | undefined = undefined; - if (input.search) { - const s = `%${input.search}%`; + // 3) 글로벌 검색 조건 + let globalWhere: SQL<unknown> | undefined = undefined; + if (input.search) { + const s = `%${input.search}%`; - const validSearchConditions: SQL<unknown>[] = []; + const validSearchConditions: SQL<unknown>[] = []; - const rfqCodeCondition = ilike(initialRfqDetailView.rfqCode, s); - if (rfqCodeCondition) validSearchConditions.push(rfqCodeCondition); + const rfqCodeCondition = ilike(initialRfqDetailView.rfqCode, s); + if (rfqCodeCondition) validSearchConditions.push(rfqCodeCondition); - const vendorNameCondition = ilike(initialRfqDetailView.vendorName, s); - if (vendorNameCondition) validSearchConditions.push(vendorNameCondition); + const vendorNameCondition = ilike(initialRfqDetailView.vendorName, s); + if (vendorNameCondition) validSearchConditions.push(vendorNameCondition); - const vendorCodeCondition = ilike(initialRfqDetailView.vendorCode, s); - if (vendorCodeCondition) validSearchConditions.push(vendorCodeCondition); + const vendorCodeCondition = ilike(initialRfqDetailView.vendorCode, s); + if (vendorCodeCondition) validSearchConditions.push(vendorCodeCondition); - const vendorCountryCondition = ilike(initialRfqDetailView.vendorCountry, s); - if (vendorCountryCondition) validSearchConditions.push(vendorCountryCondition); + const vendorCountryCondition = ilike(initialRfqDetailView.vendorCountry, s); + if (vendorCountryCondition) validSearchConditions.push(vendorCountryCondition); - const incotermsDescriptionCondition = ilike(initialRfqDetailView.incotermsDescription, s); - if (incotermsDescriptionCondition) validSearchConditions.push(incotermsDescriptionCondition); + const incotermsDescriptionCondition = ilike(initialRfqDetailView.incotermsDescription, s); + if (incotermsDescriptionCondition) validSearchConditions.push(incotermsDescriptionCondition); - const classificationCondition = ilike(initialRfqDetailView.classification, s); - if (classificationCondition) validSearchConditions.push(classificationCondition); + const classificationCondition = ilike(initialRfqDetailView.classification, s); + if (classificationCondition) validSearchConditions.push(classificationCondition); - const sparepartCondition = ilike(initialRfqDetailView.sparepart, s); - if (sparepartCondition) validSearchConditions.push(sparepartCondition); + const sparepartCondition = ilike(initialRfqDetailView.sparepart, s); + if (sparepartCondition) validSearchConditions.push(sparepartCondition); - if (validSearchConditions.length > 0) { - globalWhere = or(...validSearchConditions); - } - } + if (validSearchConditions.length > 0) { + globalWhere = or(...validSearchConditions); + } + } - // 5) 최종 WHERE 조건 생성 - const whereConditions: SQL<unknown>[] = []; + // 5) 최종 WHERE 조건 생성 + const whereConditions: SQL<unknown>[] = []; - if (advancedWhere) whereConditions.push(advancedWhere); - if (basicWhere) whereConditions.push(basicWhere); - if (globalWhere) whereConditions.push(globalWhere); - if (rfqIdWhere) whereConditions.push(rfqIdWhere); + if (advancedWhere) whereConditions.push(advancedWhere); + if (basicWhere) whereConditions.push(basicWhere); + if (globalWhere) whereConditions.push(globalWhere); + if (rfqIdWhere) whereConditions.push(rfqIdWhere); - const finalWhere = whereConditions.length > 0 ? and(...whereConditions) : undefined; + const finalWhere = whereConditions.length > 0 ? and(...whereConditions) : undefined; - // 6) 전체 데이터 수 조회 - const totalResult = await db - .select({ count: count() }) - .from(initialRfqDetailView) - .where(finalWhere); + // 6) 전체 데이터 수 조회 + const totalResult = await db + .select({ count: count() }) + .from(initialRfqDetailView) + .where(finalWhere); - const total = totalResult[0]?.count || 0; + const total = totalResult[0]?.count || 0; - if (total === 0) { - return { data: [], pageCount: 0, total: 0 }; - } + if (total === 0) { + return { data: [], pageCount: 0, total: 0 }; + } - console.log(totalResult); - console.log(total); + console.log(totalResult); + console.log(total); - // 7) 정렬 및 페이징 처리된 데이터 조회 - const orderByColumns = input.sort.map((sort) => { - const column = sort.id as keyof typeof initialRfqDetailView.$inferSelect; - return sort.desc ? desc(initialRfqDetailView[column]) : asc(initialRfqDetailView[column]); - }); + // 7) 정렬 및 페이징 처리된 데이터 조회 + const orderByColumns = input.sort.map((sort) => { + const column = sort.id as keyof typeof initialRfqDetailView.$inferSelect; + return sort.desc ? desc(initialRfqDetailView[column]) : asc(initialRfqDetailView[column]); + }); - if (orderByColumns.length === 0) { - orderByColumns.push(desc(initialRfqDetailView.createdAt)); - } + if (orderByColumns.length === 0) { + orderByColumns.push(desc(initialRfqDetailView.createdAt)); + } - const initialRfqData = await db - .select() - .from(initialRfqDetailView) - .where(finalWhere) - .orderBy(...orderByColumns) - .limit(input.perPage) - .offset(offset); - - const pageCount = Math.ceil(total / input.perPage); - - return { data: initialRfqData, pageCount, total }; - } catch (err) { - console.error("Error in getInitialRfqDetail:", err); - return { data: [], pageCount: 0, total: 0 }; - } + const initialRfqData = await db + .select() + .from(initialRfqDetailView) + .where(finalWhere) + .orderBy(...orderByColumns) + .limit(input.perPage) + .offset(offset); + + const pageCount = Math.ceil(total / input.perPage); + + return { data: initialRfqData, pageCount, total }; + } catch (err) { + console.error("Error in getInitialRfqDetail:", err); + return { data: [], pageCount: 0, total: 0 }; + } } export async function getVendorsForSelection() { @@ -1177,7 +1181,7 @@ export async function getVendorsForSelection() { export async function addInitialRfqRecord(data: AddInitialRfqFormData & { rfqId: number }) { try { console.log('Incoming data:', data); - + const [newRecord] = await db .insert(initialRfq) .values({ @@ -1233,7 +1237,7 @@ export async function getIncotermsForSelection() { } export async function removeInitialRfqs(input: RemoveInitialRfqsSchema) { - unstable_noStore () + unstable_noStore() try { const { ids } = removeInitialRfqsSchema.parse(input) @@ -1259,10 +1263,10 @@ interface ModifyInitialRfqInput extends UpdateInitialRfqSchema { } export async function modifyInitialRfq(input: ModifyInitialRfqInput) { - unstable_noStore () + unstable_noStore() try { const { id, ...updateData } = input - + // validation updateInitialRfqSchema.parse(updateData) @@ -1427,17 +1431,17 @@ export async function sendBulkInitialRfqEmails(input: BulkEmailInput) { // 각 벤더의 모든 유효한 이메일 주소를 정리하는 함수 function getAllVendorEmails(vendor: typeof vendorsWithAllEmails[0]): string[] { const emails: string[] = [] - + // 벤더 기본 이메일 if (vendor.email) { emails.push(vendor.email) } - + // 대표자 이메일 if (vendor.representativeEmail && vendor.representativeEmail !== vendor.email) { emails.push(vendor.representativeEmail) } - + // 연락처 이메일들 if (vendor.contactEmails && Array.isArray(vendor.contactEmails)) { vendor.contactEmails.forEach(contactEmail => { @@ -1446,7 +1450,7 @@ export async function sendBulkInitialRfqEmails(input: BulkEmailInput) { } }) } - + return emails.filter(email => email && email.trim() !== '') } @@ -1464,7 +1468,7 @@ export async function sendBulkInitialRfqEmails(input: BulkEmailInput) { // 해당 RFQ의 첨부파일들 const rfqAttachments = attachments.filter(att => att.rfqId === rfqDetail.rfqId) - + // 벤더 정보 const vendor = vendorsWithAllEmails.find(v => v.id === rfqDetail.vendorId) if (!vendor) { @@ -1474,7 +1478,7 @@ export async function sendBulkInitialRfqEmails(input: BulkEmailInput) { // 해당 벤더의 모든 이메일 주소 수집 const vendorEmails = getAllVendorEmails(vendor) - + if (vendorEmails.length === 0) { errors.push(`벤더 이메일 주소가 없습니다: ${vendor.vendorName}`) continue @@ -1486,7 +1490,7 @@ export async function sendBulkInitialRfqEmails(input: BulkEmailInput) { let revisionToUse = currentRfqRevision // 첫 번째 첨부파일을 기준으로 기존 응답 조회 (리비전 상태 확인용) - if (rfqAttachments.length > 0 && rfqDetail.initialRfqId) { + if (rfqAttachments.length > 0 && rfqDetail.initialRfqId) { const existingResponses = await db .select() .from(vendorAttachmentResponses) @@ -1501,7 +1505,7 @@ export async function sendBulkInitialRfqEmails(input: BulkEmailInput) { if (existingResponses.length > 0) { // 기존 응답이 있음 const existingRevision = parseInt(existingResponses[0].currentRevision?.replace("Rev.", "") || "0") - + if (currentRfqRevision > existingRevision) { // RFQ 리비전이 올라감 → 리비전 업데이트 emailType = "REVISION" @@ -1555,7 +1559,7 @@ export async function sendBulkInitialRfqEmails(input: BulkEmailInput) { }) .where(eq(vendorAttachmentResponses.id, existingResponse[0].id)) } - + } const formatDateSafely = (date: Date | string | null | undefined): string => { @@ -1565,11 +1569,11 @@ export async function sendBulkInitialRfqEmails(input: BulkEmailInput) { const dateObj = new Date(date) // 유효한 날짜인지 확인 if (isNaN(dateObj.getTime())) return "" - - return dateObj.toLocaleDateString('en-US', { - year: 'numeric', - month: '2-digit', - day: '2-digit' + + return dateObj.toLocaleDateString('en-US', { + year: 'numeric', + month: '2-digit', + day: '2-digit' }) } catch (error) { console.error("Date formatting error:", error) @@ -1579,7 +1583,7 @@ export async function sendBulkInitialRfqEmails(input: BulkEmailInput) { // 7. 이메일 발송 const emailData: EmailData = { - name:vendor.vendorName, + name: vendor.vendorName, rfqCode: rfqDetail.rfqCode || "", projectName: rfqDetail.rfqCode || "", // 실제 프로젝트명이 있다면 사용 projectCompany: rfqDetail.projectCompany || "", @@ -1589,7 +1593,7 @@ export async function sendBulkInitialRfqEmails(input: BulkEmailInput) { incotermsCode: rfqDetail.incotermsCode || "FOB", incotermsDescription: rfqDetail.incotermsDescription || "FOB Finland Port", dueDate: rfqDetail.dueDate ? formatDateSafely(rfqDetail.dueDate) : "", - validDate: rfqDetail.validDate ?formatDateSafely(rfqDetail.validDate) : "", + validDate: rfqDetail.validDate ? formatDateSafely(rfqDetail.validDate) : "", sparepart: rfqDetail.sparepart || "One(1) year operational spare parts", vendorName: vendor.vendorName, picName: session.user.name || rfqDetail.picName || "Procurement Manager", @@ -1603,7 +1607,7 @@ export async function sendBulkInitialRfqEmails(input: BulkEmailInput) { // 이메일 제목 생성 (타입에 따라 다르게) let emailSubject = "" const revisionText = revisionToUse > 0 ? ` Rev.${revisionToUse}` : "" - + switch (emailType) { case "NEW": emailSubject = `[SHI RFQ] ${rfqDetail.rfqCode}${revisionText} Invitation to Bidder for ${emailData.packageName} * ${vendor.vendorName} * RFQ No. ${rfqDetail.rfqCode}` @@ -1618,7 +1622,6 @@ export async function sendBulkInitialRfqEmails(input: BulkEmailInput) { // nodemailer로 모든 이메일 주소에 한번에 발송 await sendEmail({ - // from: session.user.email || undefined, to: vendorEmails.join(", "), // 콤마+공백으로 구분 subject: emailSubject, template: "initial-rfq-invitation", // hbs 템플릿 파일명 @@ -1629,7 +1632,7 @@ export async function sendBulkInitialRfqEmails(input: BulkEmailInput) { }) // 8. 초기 RFQ 상태 업데이트 (리비전은 변경하지 않음 - 이미 DB에 저장된 값 사용) - if(rfqDetail.initialRfqId && rfqDetail.rfqId){ + if (rfqDetail.initialRfqId && rfqDetail.rfqId) { // Promise.all로 두 테이블 동시 업데이트 await Promise.all([ // initialRfq 테이블 업데이트 @@ -1640,7 +1643,7 @@ export async function sendBulkInitialRfqEmails(input: BulkEmailInput) { updatedAt: new Date(), }) .where(eq(initialRfq.id, rfqDetail.initialRfqId)), - + // bRfqs 테이블 status도 함께 업데이트 db .update(bRfqs) @@ -1730,310 +1733,310 @@ export type VendorResponseDetail = VendorAttachmentResponse & { }; export async function getVendorRfqResponses(input: GetVendorResponsesSchema, vendorId?: string, rfqId?: string) { - try { - // 페이지네이션 설정 - const page = input.page || 1; - const perPage = input.perPage || 10; - const offset = (page - 1) * perPage; + try { + // 페이지네이션 설정 + const page = input.page || 1; + const perPage = input.perPage || 10; + const offset = (page - 1) * perPage; - // 기본 조건 - let whereConditions = []; + // 기본 조건 + let whereConditions = []; - // 벤더 ID 조건 - if (vendorId) { - whereConditions.push(eq(vendorAttachmentResponses.vendorId, Number(vendorId))); - } + // 벤더 ID 조건 + if (vendorId) { + whereConditions.push(eq(vendorAttachmentResponses.vendorId, Number(vendorId))); + } - // RFQ 타입 조건 - // if (input.rfqType !== "ALL") { - // whereConditions.push(eq(vendorAttachmentResponses.rfqType, input.rfqType as RfqType)); - // } + // RFQ 타입 조건 + // if (input.rfqType !== "ALL") { + // whereConditions.push(eq(vendorAttachmentResponses.rfqType, input.rfqType as RfqType)); + // } - // 날짜 범위 조건 - if (input.from && input.to) { - whereConditions.push( - and( - gte(vendorAttachmentResponses.requestedAt, new Date(input.from)), - lte(vendorAttachmentResponses.requestedAt, new Date(input.to)) - ) - ); - } + // 날짜 범위 조건 + if (input.from && input.to) { + whereConditions.push( + and( + gte(vendorAttachmentResponses.requestedAt, new Date(input.from)), + lte(vendorAttachmentResponses.requestedAt, new Date(input.to)) + ) + ); + } - const baseWhere = whereConditions.length > 0 ? and(...whereConditions) : undefined; - - // 그룹핑된 응답 요약 데이터 조회 - const groupedResponses = await db - .select({ - vendorId: vendorAttachmentResponses.vendorId, - rfqRecordId: vendorAttachmentResponses.rfqRecordId, - rfqType: vendorAttachmentResponses.rfqType, - - // 통계 계산 (조건부 COUNT 수정) - totalAttachments: count(), - respondedCount: sql<number>`SUM(CASE WHEN ${vendorAttachmentResponses.responseStatus} = 'RESPONDED' THEN 1 ELSE 0 END)`, - pendingCount: sql<number>`SUM(CASE WHEN ${vendorAttachmentResponses.responseStatus} = 'NOT_RESPONDED' THEN 1 ELSE 0 END)`, - revisionRequestedCount: sql<number>`SUM(CASE WHEN ${vendorAttachmentResponses.responseStatus} = 'REVISION_REQUESTED' THEN 1 ELSE 0 END)`, - waivedCount: sql<number>`SUM(CASE WHEN ${vendorAttachmentResponses.responseStatus} = 'WAIVED' THEN 1 ELSE 0 END)`, - - // 날짜 정보 - requestedAt: sql<Date>`MIN(${vendorAttachmentResponses.requestedAt})`, - lastRespondedAt: sql<Date | null>`MAX(${vendorAttachmentResponses.respondedAt})`, - - // 코멘트 여부 - hasComments: sql<boolean>`BOOL_OR(${vendorAttachmentResponses.responseComment} IS NOT NULL OR ${vendorAttachmentResponses.vendorComment} IS NOT NULL)`, - }) - .from(vendorAttachmentResponses) - .where(baseWhere) - .groupBy( - vendorAttachmentResponses.vendorId, - vendorAttachmentResponses.rfqRecordId, - vendorAttachmentResponses.rfqType - ) - .orderBy(desc(sql`MIN(${vendorAttachmentResponses.requestedAt})`)) - .offset(offset) - .limit(perPage); + const baseWhere = whereConditions.length > 0 ? and(...whereConditions) : undefined; - // 벤더 정보와 RFQ 정보를 별도로 조회 - const vendorIds = [...new Set(groupedResponses.map(r => r.vendorId))]; - const rfqRecordIds = [...new Set(groupedResponses.map(r => r.rfqRecordId))]; + // 그룹핑된 응답 요약 데이터 조회 + const groupedResponses = await db + .select({ + vendorId: vendorAttachmentResponses.vendorId, + rfqRecordId: vendorAttachmentResponses.rfqRecordId, + rfqType: vendorAttachmentResponses.rfqType, - // 벤더 정보 조회 - const vendorsData = await db.query.vendors.findMany({ - where: or(...vendorIds.map(id => eq(vendors.id, id))), - columns: { - id: true, - vendorCode: true, - vendorName: true, - country: true, - businessSize: true, - } - }); - - // RFQ 정보 조회 (초기 RFQ와 최종 RFQ 모두) - const [initialRfqs] = await Promise.all([ - db.query.initialRfq.findMany({ - where: or(...rfqRecordIds.map(id => eq(initialRfq.id, id))), - with: { - rfq: { - columns: { - id: true, - rfqCode: true, - description: true, - status: true, - dueDate: true, - } - } - } - }) + // 통계 계산 (조건부 COUNT 수정) + totalAttachments: count(), + respondedCount: sql<number>`SUM(CASE WHEN ${vendorAttachmentResponses.responseStatus} = 'RESPONDED' THEN 1 ELSE 0 END)`, + pendingCount: sql<number>`SUM(CASE WHEN ${vendorAttachmentResponses.responseStatus} = 'NOT_RESPONDED' THEN 1 ELSE 0 END)`, + revisionRequestedCount: sql<number>`SUM(CASE WHEN ${vendorAttachmentResponses.responseStatus} = 'REVISION_REQUESTED' THEN 1 ELSE 0 END)`, + waivedCount: sql<number>`SUM(CASE WHEN ${vendorAttachmentResponses.responseStatus} = 'WAIVED' THEN 1 ELSE 0 END)`, - ]); + // 날짜 정보 + requestedAt: sql<Date>`MIN(${vendorAttachmentResponses.requestedAt})`, + lastRespondedAt: sql<Date | null>`MAX(${vendorAttachmentResponses.respondedAt})`, - // 데이터 조합 및 변환 - const transformedResponses: VendorRfqResponseSummary[] = groupedResponses.map(response => { - const vendor = vendorsData.find(v => v.id === response.vendorId); - - let rfqInfo = null; - if (response.rfqType === "INITIAL") { - const initialRfq = initialRfqs.find(r => r.id === response.rfqRecordId); - rfqInfo = initialRfq?.rfq || null; - } + // 코멘트 여부 + hasComments: sql<boolean>`BOOL_OR(${vendorAttachmentResponses.responseComment} IS NOT NULL OR ${vendorAttachmentResponses.vendorComment} IS NOT NULL)`, + }) + .from(vendorAttachmentResponses) + .where(baseWhere) + .groupBy( + vendorAttachmentResponses.vendorId, + vendorAttachmentResponses.rfqRecordId, + vendorAttachmentResponses.rfqType + ) + .orderBy(desc(sql`MIN(${vendorAttachmentResponses.requestedAt})`)) + .offset(offset) + .limit(perPage); + + // 벤더 정보와 RFQ 정보를 별도로 조회 + const vendorIds = [...new Set(groupedResponses.map(r => r.vendorId))]; + const rfqRecordIds = [...new Set(groupedResponses.map(r => r.rfqRecordId))]; + + // 벤더 정보 조회 + const vendorsData = await db.query.vendors.findMany({ + where: or(...vendorIds.map(id => eq(vendors.id, id))), + columns: { + id: true, + vendorCode: true, + vendorName: true, + country: true, + businessSize: true, + } + }); - // 응답률 계산 - const responseRate = Number(response.totalAttachments) > 0 - ? Math.round((Number(response.respondedCount) / Number(response.totalAttachments)) * 100) - : 0; - - // 완료율 계산 (응답완료 + 포기) - const completionRate = Number(response.totalAttachments) > 0 - ? Math.round(((Number(response.respondedCount) + Number(response.waivedCount)) / Number(response.totalAttachments)) * 100) - : 0; - - // 전체 상태 결정 - let overallStatus: ResponseStatus = "NOT_RESPONDED"; - if (Number(response.revisionRequestedCount) > 0) { - overallStatus = "REVISION_REQUESTED"; - } else if (completionRate === 100) { - overallStatus = Number(response.waivedCount) === Number(response.totalAttachments) ? "WAIVED" : "RESPONDED"; - } else if (Number(response.respondedCount) > 0) { - overallStatus = "RESPONDED"; // 부분 응답 + // RFQ 정보 조회 (초기 RFQ와 최종 RFQ 모두) + const [initialRfqs] = await Promise.all([ + db.query.initialRfq.findMany({ + where: or(...rfqRecordIds.map(id => eq(initialRfq.id, id))), + with: { + rfq: { + columns: { + id: true, + rfqCode: true, + description: true, + status: true, + dueDate: true, + } } + } + }) - return { - id: `${response.vendorId}-${response.rfqRecordId}-${response.rfqType}`, - vendorId: response.vendorId, - rfqRecordId: response.rfqRecordId, - rfqType: response.rfqType, - rfq: rfqInfo, - vendor: vendor || null, - totalAttachments: Number(response.totalAttachments), - respondedCount: Number(response.respondedCount), - pendingCount: Number(response.pendingCount), - revisionRequestedCount: Number(response.revisionRequestedCount), - waivedCount: Number(response.waivedCount), - responseRate, - completionRate, - overallStatus, - requestedAt: response.requestedAt, - lastRespondedAt: response.lastRespondedAt, - hasComments: response.hasComments, - }; - }); - - // 전체 개수 조회 (그룹핑 기준) - PostgreSQL 호환 방식 - const totalCountResult = await db - .select({ - totalCount: sql<number>`COUNT(DISTINCT (${vendorAttachmentResponses.vendorId}, ${vendorAttachmentResponses.rfqRecordId}, ${vendorAttachmentResponses.rfqType}))` - }) - .from(vendorAttachmentResponses) - .where(baseWhere); + ]); - const totalCount = Number(totalCountResult[0].totalCount); - const pageCount = Math.ceil(totalCount / perPage); + // 데이터 조합 및 변환 + const transformedResponses: VendorRfqResponseSummary[] = groupedResponses.map(response => { + const vendor = vendorsData.find(v => v.id === response.vendorId); - return { - data: transformedResponses, - pageCount, - totalCount - }; + let rfqInfo = null; + if (response.rfqType === "INITIAL") { + const initialRfq = initialRfqs.find(r => r.id === response.rfqRecordId); + rfqInfo = initialRfq?.rfq || null; + } - } catch (err) { - console.error("getVendorRfqResponses 에러:", err); - return { data: [], pageCount: 0, totalCount: 0 }; + // 응답률 계산 + const responseRate = Number(response.totalAttachments) > 0 + ? Math.round((Number(response.respondedCount) / Number(response.totalAttachments)) * 100) + : 0; + + // 완료율 계산 (응답완료 + 포기) + const completionRate = Number(response.totalAttachments) > 0 + ? Math.round(((Number(response.respondedCount) + Number(response.waivedCount)) / Number(response.totalAttachments)) * 100) + : 0; + + // 전체 상태 결정 + let overallStatus: ResponseStatus = "NOT_RESPONDED"; + if (Number(response.revisionRequestedCount) > 0) { + overallStatus = "REVISION_REQUESTED"; + } else if (completionRate === 100) { + overallStatus = Number(response.waivedCount) === Number(response.totalAttachments) ? "WAIVED" : "RESPONDED"; + } else if (Number(response.respondedCount) > 0) { + overallStatus = "RESPONDED"; // 부분 응답 } + + return { + id: `${response.vendorId}-${response.rfqRecordId}-${response.rfqType}`, + vendorId: response.vendorId, + rfqRecordId: response.rfqRecordId, + rfqType: response.rfqType, + rfq: rfqInfo, + vendor: vendor || null, + totalAttachments: Number(response.totalAttachments), + respondedCount: Number(response.respondedCount), + pendingCount: Number(response.pendingCount), + revisionRequestedCount: Number(response.revisionRequestedCount), + waivedCount: Number(response.waivedCount), + responseRate, + completionRate, + overallStatus, + requestedAt: response.requestedAt, + lastRespondedAt: response.lastRespondedAt, + hasComments: response.hasComments, + }; + }); + + // 전체 개수 조회 (그룹핑 기준) - PostgreSQL 호환 방식 + const totalCountResult = await db + .select({ + totalCount: sql<number>`COUNT(DISTINCT (${vendorAttachmentResponses.vendorId}, ${vendorAttachmentResponses.rfqRecordId}, ${vendorAttachmentResponses.rfqType}))` + }) + .from(vendorAttachmentResponses) + .where(baseWhere); + + const totalCount = Number(totalCountResult[0].totalCount); + const pageCount = Math.ceil(totalCount / perPage); + + return { + data: transformedResponses, + pageCount, + totalCount + }; + + } catch (err) { + console.error("getVendorRfqResponses 에러:", err); + return { data: [], pageCount: 0, totalCount: 0 }; + } } /** * 특정 RFQ의 첨부파일별 응답 상세 조회 (상세 페이지용) */ export async function getRfqAttachmentResponses(vendorId: string, rfqRecordId: string) { - try { - // 해당 RFQ의 모든 첨부파일 응답 조회 - const responses = await db.query.vendorAttachmentResponses.findMany({ - where: and( - eq(vendorAttachmentResponses.vendorId, Number(vendorId)), - eq(vendorAttachmentResponses.rfqRecordId, Number(rfqRecordId)), - ), + try { + // 해당 RFQ의 모든 첨부파일 응답 조회 + const responses = await db.query.vendorAttachmentResponses.findMany({ + where: and( + eq(vendorAttachmentResponses.vendorId, Number(vendorId)), + eq(vendorAttachmentResponses.rfqRecordId, Number(rfqRecordId)), + ), + with: { + attachment: { with: { - attachment: { + rfq: { + columns: { + id: true, + rfqCode: true, + description: true, + status: true, + dueDate: true, + // 추가 정보 + picCode: true, + picName: true, + EngPicName: true, + packageNo: true, + packageName: true, + projectId: true, + projectCompany: true, + projectFlag: true, + projectSite: true, + remark: true, + }, with: { - rfq: { + project: { columns: { id: true, - rfqCode: true, - description: true, - status: true, - dueDate: true, - // 추가 정보 - picCode: true, - picName: true, - EngPicName: true, - packageNo: true, - packageName: true, - projectId: true, - projectCompany: true, - projectFlag: true, - projectSite: true, - remark: true, - }, - with: { - project: { - columns: { - id: true, - code: true, - name: true, - type: true, - } - } + code: true, + name: true, + type: true, } } } - }, - vendor: { - columns: { - id: true, - vendorCode: true, - vendorName: true, - country: true, - businessSize: true, - } - }, - responseAttachments: true, - }, - orderBy: [asc(vendorAttachmentResponses.attachmentId)] - }); + } + } + }, + vendor: { + columns: { + id: true, + vendorCode: true, + vendorName: true, + country: true, + businessSize: true, + } + }, + responseAttachments: true, + }, + orderBy: [asc(vendorAttachmentResponses.attachmentId)] + }); - return { - data: responses, - rfqInfo: responses[0]?.attachment?.rfq || null, - vendorInfo: responses[0]?.vendor || null, - }; + return { + data: responses, + rfqInfo: responses[0]?.attachment?.rfq || null, + vendorInfo: responses[0]?.vendor || null, + }; - } catch (err) { - console.error("getRfqAttachmentResponses 에러:", err); - return { data: [], rfqInfo: null, vendorInfo: null }; - } + } catch (err) { + console.error("getRfqAttachmentResponses 에러:", err); + return { data: [], rfqInfo: null, vendorInfo: null }; + } } export async function getVendorResponseStatusCounts(vendorId?: string, rfqId?: string, rfqType?: RfqType) { - try { - const initial: Record<ResponseStatus, number> = { - NOT_RESPONDED: 0, - RESPONDED: 0, - REVISION_REQUESTED: 0, - WAIVED: 0, - }; + try { + const initial: Record<ResponseStatus, number> = { + NOT_RESPONDED: 0, + RESPONDED: 0, + REVISION_REQUESTED: 0, + WAIVED: 0, + }; - // 조건 설정 - let whereConditions = []; + // 조건 설정 + let whereConditions = []; - // 벤더 ID 조건 - if (vendorId) { - whereConditions.push(eq(vendorAttachmentResponses.vendorId, Number(vendorId))); - } + // 벤더 ID 조건 + if (vendorId) { + whereConditions.push(eq(vendorAttachmentResponses.vendorId, Number(vendorId))); + } - // RFQ ID 조건 - if (rfqId) { - const attachmentIds = await db - .select({ id: bRfqsAttachments.id }) - .from(bRfqsAttachments) - .where(eq(bRfqsAttachments.rfqId, Number(rfqId))); - - if (attachmentIds.length > 0) { - whereConditions.push( - or(...attachmentIds.map(att => eq(vendorAttachmentResponses.attachmentId, att.id))) - ); - } - } + // RFQ ID 조건 + if (rfqId) { + const attachmentIds = await db + .select({ id: bRfqsAttachments.id }) + .from(bRfqsAttachments) + .where(eq(bRfqsAttachments.rfqId, Number(rfqId))); - // RFQ 타입 조건 - if (rfqType) { - whereConditions.push(eq(vendorAttachmentResponses.rfqType, rfqType)); - } + if (attachmentIds.length > 0) { + whereConditions.push( + or(...attachmentIds.map(att => eq(vendorAttachmentResponses.attachmentId, att.id))) + ); + } + } - const whereCondition = whereConditions.length > 0 ? and(...whereConditions) : undefined; + // RFQ 타입 조건 + if (rfqType) { + whereConditions.push(eq(vendorAttachmentResponses.rfqType, rfqType)); + } - // 상태별 그룹핑 쿼리 - const rows = await db - .select({ - status: vendorAttachmentResponses.responseStatus, - count: count(), - }) - .from(vendorAttachmentResponses) - .where(whereCondition) - .groupBy(vendorAttachmentResponses.responseStatus); - - // 결과 처리 - const result = rows.reduce<Record<ResponseStatus, number>>((acc, { status, count }) => { - if (status) { - acc[status as ResponseStatus] = Number(count); - } - return acc; - }, initial); + const whereCondition = whereConditions.length > 0 ? and(...whereConditions) : undefined; + + // 상태별 그룹핑 쿼리 + const rows = await db + .select({ + status: vendorAttachmentResponses.responseStatus, + count: count(), + }) + .from(vendorAttachmentResponses) + .where(whereCondition) + .groupBy(vendorAttachmentResponses.responseStatus); - return result; - } catch (err) { - console.error("getVendorResponseStatusCounts 에러:", err); - return {} as Record<ResponseStatus, number>; + // 결과 처리 + const result = rows.reduce<Record<ResponseStatus, number>>((acc, { status, count }) => { + if (status) { + acc[status as ResponseStatus] = Number(count); } + return acc; + }, initial); + + return result; + } catch (err) { + console.error("getVendorResponseStatusCounts 에러:", err); + return {} as Record<ResponseStatus, number>; + } } /** @@ -2041,101 +2044,101 @@ export async function getVendorResponseStatusCounts(vendorId?: string, rfqId?: s */ export async function getRfqResponseSummary(rfqId: string, rfqType?: RfqType) { - try { - // RFQ의 첨부파일 목록 조회 (relations 사용) - const attachments = await db.query.bRfqsAttachments.findMany({ - where: eq(bRfqsAttachments.rfqId, Number(rfqId)), - columns: { - id: true, - attachmentType: true, - serialNo: true, - description: true, - } - }); - - if (attachments.length === 0) { - return { - totalAttachments: 0, - totalVendors: 0, - responseRate: 0, - completionRate: 0, - statusCounts: {} as Record<ResponseStatus, number> - }; - } - - // 조건 설정 - let whereConditions = [ - or(...attachments.map(att => eq(vendorAttachmentResponses.attachmentId, att.id))) - ]; + try { + // RFQ의 첨부파일 목록 조회 (relations 사용) + const attachments = await db.query.bRfqsAttachments.findMany({ + where: eq(bRfqsAttachments.rfqId, Number(rfqId)), + columns: { + id: true, + attachmentType: true, + serialNo: true, + description: true, + } + }); - if (rfqType) { - whereConditions.push(eq(vendorAttachmentResponses.rfqType, rfqType)); - } + if (attachments.length === 0) { + return { + totalAttachments: 0, + totalVendors: 0, + responseRate: 0, + completionRate: 0, + statusCounts: {} as Record<ResponseStatus, number> + }; + } - const whereCondition = and(...whereConditions); + // 조건 설정 + let whereConditions = [ + or(...attachments.map(att => eq(vendorAttachmentResponses.attachmentId, att.id))) + ]; - // 벤더 수 및 응답 통계 조회 - const [vendorStats, statusCounts] = await Promise.all([ - // 전체 벤더 수 및 응답 벤더 수 (조건부 COUNT 수정) - db - .select({ - totalVendors: count(), - respondedVendors: sql<number>`SUM(CASE WHEN ${vendorAttachmentResponses.responseStatus} = 'RESPONDED' THEN 1 ELSE 0 END)`, - completedVendors: sql<number>`SUM(CASE WHEN ${vendorAttachmentResponses.responseStatus} = 'RESPONDED' OR ${vendorAttachmentResponses.responseStatus} = 'WAIVED' THEN 1 ELSE 0 END)`, - }) - .from(vendorAttachmentResponses) - .where(whereCondition), + if (rfqType) { + whereConditions.push(eq(vendorAttachmentResponses.rfqType, rfqType)); + } - // 상태별 개수 - db - .select({ - status: vendorAttachmentResponses.responseStatus, - count: count(), - }) - .from(vendorAttachmentResponses) - .where(whereCondition) - .groupBy(vendorAttachmentResponses.responseStatus) - ]); - - const stats = vendorStats[0]; - const statusCountsMap = statusCounts.reduce<Record<ResponseStatus, number>>((acc, { status, count }) => { - if (status) { - acc[status as ResponseStatus] = Number(count); - } - return acc; - }, { - NOT_RESPONDED: 0, - RESPONDED: 0, - REVISION_REQUESTED: 0, - WAIVED: 0, - }); - - const responseRate = stats.totalVendors > 0 - ? Math.round((Number(stats.respondedVendors) / Number(stats.totalVendors)) * 100) - : 0; - - const completionRate = stats.totalVendors > 0 - ? Math.round((Number(stats.completedVendors) / Number(stats.totalVendors)) * 100) - : 0; + const whereCondition = and(...whereConditions); - return { - totalAttachments: attachments.length, - totalVendors: Number(stats.totalVendors), - responseRate, - completionRate, - statusCounts: statusCountsMap - }; + // 벤더 수 및 응답 통계 조회 + const [vendorStats, statusCounts] = await Promise.all([ + // 전체 벤더 수 및 응답 벤더 수 (조건부 COUNT 수정) + db + .select({ + totalVendors: count(), + respondedVendors: sql<number>`SUM(CASE WHEN ${vendorAttachmentResponses.responseStatus} = 'RESPONDED' THEN 1 ELSE 0 END)`, + completedVendors: sql<number>`SUM(CASE WHEN ${vendorAttachmentResponses.responseStatus} = 'RESPONDED' OR ${vendorAttachmentResponses.responseStatus} = 'WAIVED' THEN 1 ELSE 0 END)`, + }) + .from(vendorAttachmentResponses) + .where(whereCondition), - } catch (err) { - console.error("getRfqResponseSummary 에러:", err); - return { - totalAttachments: 0, - totalVendors: 0, - responseRate: 0, - completionRate: 0, - statusCounts: {} as Record<ResponseStatus, number> - }; + // 상태별 개수 + db + .select({ + status: vendorAttachmentResponses.responseStatus, + count: count(), + }) + .from(vendorAttachmentResponses) + .where(whereCondition) + .groupBy(vendorAttachmentResponses.responseStatus) + ]); + + const stats = vendorStats[0]; + const statusCountsMap = statusCounts.reduce<Record<ResponseStatus, number>>((acc, { status, count }) => { + if (status) { + acc[status as ResponseStatus] = Number(count); } + return acc; + }, { + NOT_RESPONDED: 0, + RESPONDED: 0, + REVISION_REQUESTED: 0, + WAIVED: 0, + }); + + const responseRate = stats.totalVendors > 0 + ? Math.round((Number(stats.respondedVendors) / Number(stats.totalVendors)) * 100) + : 0; + + const completionRate = stats.totalVendors > 0 + ? Math.round((Number(stats.completedVendors) / Number(stats.totalVendors)) * 100) + : 0; + + return { + totalAttachments: attachments.length, + totalVendors: Number(stats.totalVendors), + responseRate, + completionRate, + statusCounts: statusCountsMap + }; + + } catch (err) { + console.error("getRfqResponseSummary 에러:", err); + return { + totalAttachments: 0, + totalVendors: 0, + responseRate: 0, + completionRate: 0, + statusCounts: {} as Record<ResponseStatus, number> + }; + } } /** @@ -2143,54 +2146,54 @@ export async function getRfqResponseSummary(rfqId: string, rfqType?: RfqType) { */ export async function getVendorResponseProgress(vendorId: string) { - try { - let whereConditions = [eq(vendorAttachmentResponses.vendorId, Number(vendorId))]; - - const whereCondition = and(...whereConditions); - - const progress = await db - .select({ - totalRequests: count(), - responded: sql<number>`SUM(CASE WHEN ${vendorAttachmentResponses.responseStatus} = 'RESPONDED' THEN 1 ELSE 0 END)`, - pending: sql<number>`SUM(CASE WHEN ${vendorAttachmentResponses.responseStatus} = 'NOT_RESPONDED' THEN 1 ELSE 0 END)`, - revisionRequested: sql<number>`SUM(CASE WHEN ${vendorAttachmentResponses.responseStatus} = 'REVISION_REQUESTED' THEN 1 ELSE 0 END)`, - waived: sql<number>`SUM(CASE WHEN ${vendorAttachmentResponses.responseStatus} = 'WAIVED' THEN 1 ELSE 0 END)`, - }) - .from(vendorAttachmentResponses) - .where(whereCondition); - console.log(progress,"progress") - - const stats = progress[0]; - const responseRate = Number(stats.totalRequests) > 0 - ? Math.round((Number(stats.responded) / Number(stats.totalRequests)) * 100) - : 0; - - const completionRate = Number(stats.totalRequests) > 0 - ? Math.round(((Number(stats.responded) + Number(stats.waived)) / Number(stats.totalRequests)) * 100) - : 0; + try { + let whereConditions = [eq(vendorAttachmentResponses.vendorId, Number(vendorId))]; - return { - totalRequests: Number(stats.totalRequests), - responded: Number(stats.responded), - pending: Number(stats.pending), - revisionRequested: Number(stats.revisionRequested), - waived: Number(stats.waived), - responseRate, - completionRate, - }; + const whereCondition = and(...whereConditions); - } catch (err) { - console.error("getVendorResponseProgress 에러:", err); - return { - totalRequests: 0, - responded: 0, - pending: 0, - revisionRequested: 0, - waived: 0, - responseRate: 0, - completionRate: 0, - }; - } + const progress = await db + .select({ + totalRequests: count(), + responded: sql<number>`SUM(CASE WHEN ${vendorAttachmentResponses.responseStatus} = 'RESPONDED' THEN 1 ELSE 0 END)`, + pending: sql<number>`SUM(CASE WHEN ${vendorAttachmentResponses.responseStatus} = 'NOT_RESPONDED' THEN 1 ELSE 0 END)`, + revisionRequested: sql<number>`SUM(CASE WHEN ${vendorAttachmentResponses.responseStatus} = 'REVISION_REQUESTED' THEN 1 ELSE 0 END)`, + waived: sql<number>`SUM(CASE WHEN ${vendorAttachmentResponses.responseStatus} = 'WAIVED' THEN 1 ELSE 0 END)`, + }) + .from(vendorAttachmentResponses) + .where(whereCondition); + console.log(progress, "progress") + + const stats = progress[0]; + const responseRate = Number(stats.totalRequests) > 0 + ? Math.round((Number(stats.responded) / Number(stats.totalRequests)) * 100) + : 0; + + const completionRate = Number(stats.totalRequests) > 0 + ? Math.round(((Number(stats.responded) + Number(stats.waived)) / Number(stats.totalRequests)) * 100) + : 0; + + return { + totalRequests: Number(stats.totalRequests), + responded: Number(stats.responded), + pending: Number(stats.pending), + revisionRequested: Number(stats.revisionRequested), + waived: Number(stats.waived), + responseRate, + completionRate, + }; + + } catch (err) { + console.error("getVendorResponseProgress 에러:", err); + return { + totalRequests: 0, + responded: 0, + pending: 0, + revisionRequested: 0, + waived: 0, + responseRate: 0, + completionRate: 0, + }; + } } @@ -2214,7 +2217,7 @@ export async function getRfqAttachmentResponsesWithRevisions(vendorId: string, r .from(rfqProgressSummaryView) .where(eq(rfqProgressSummaryView.rfqId, responses[0]?.rfqId || 0)) .limit(1); - + const progressSummary = progressSummaryResult[0] || null; // 3. 각 응답의 첨부파일 리비전 히스토리 조회 @@ -2225,7 +2228,7 @@ export async function getRfqAttachmentResponsesWithRevisions(vendorId: string, r .from(attachmentRevisionHistoryView) .where(eq(attachmentRevisionHistoryView.attachmentId, response.attachmentId)) .orderBy(desc(attachmentRevisionHistoryView.clientRevisionCreatedAt)); - + return { attachmentId: response.attachmentId, revisions: history @@ -2241,7 +2244,7 @@ export async function getRfqAttachmentResponsesWithRevisions(vendorId: string, r .from(vendorResponseAttachmentsEnhanced) .where(eq(vendorResponseAttachmentsEnhanced.vendorResponseId, response.responseId)) .orderBy(desc(vendorResponseAttachmentsEnhanced.uploadedAt)); - + return { responseId: response.responseId, files: files @@ -2253,7 +2256,7 @@ export async function getRfqAttachmentResponsesWithRevisions(vendorId: string, r const enhancedResponses = responses.map(response => { const attachmentHistory = attachmentHistories.find(h => h.attachmentId === response.attachmentId); const responseFileData = responseFiles.find(f => f.responseId === response.responseId); - + return { ...response, // 첨부파일 정보에 리비전 히스토리 추가 @@ -2293,7 +2296,7 @@ export async function getRfqAttachmentResponsesWithRevisions(vendorId: string, r versionLag: response.versionLag, needsUpdate: response.needsUpdate, hasMultipleRevisions: response.hasMultipleRevisions, - + // 새로 추가된 필드들 revisionRequestComment: response.revisionRequestComment, revisionRequestedAt: response.revisionRequestedAt?.toISOString() || null, @@ -2361,10 +2364,10 @@ export async function getRfqAttachmentResponsesWithRevisions(vendorId: string, r } catch (err) { console.error("getRfqAttachmentResponsesWithRevisions 에러:", err); - return { - data: [], - rfqInfo: null, - vendorInfo: null, + return { + data: [], + rfqInfo: null, + vendorInfo: null, statistics: { total: 0, responded: 0, @@ -2385,51 +2388,51 @@ export async function getRfqAttachmentResponsesWithRevisions(vendorId: string, r // 첨부파일 리비전 히스토리 조회 export async function getAttachmentRevisionHistory(attachmentId: number) { - try { - const history = await db - .select() - .from(attachmentRevisionHistoryView) - .where(eq(attachmentRevisionHistoryView.attachmentId, attachmentId)) - .orderBy(desc(attachmentRevisionHistoryView.clientRevisionCreatedAt)); + try { + const history = await db + .select() + .from(attachmentRevisionHistoryView) + .where(eq(attachmentRevisionHistoryView.attachmentId, attachmentId)) + .orderBy(desc(attachmentRevisionHistoryView.clientRevisionCreatedAt)); - return history; - } catch (err) { - console.error("getAttachmentRevisionHistory 에러:", err); - return []; - } + return history; + } catch (err) { + console.error("getAttachmentRevisionHistory 에러:", err); + return []; } +} // RFQ 전체 진행 현황 조회 export async function getRfqProgressSummary(rfqId: number) { - try { - const summaryResult = await db - .select() - .from(rfqProgressSummaryView) - .where(eq(rfqProgressSummaryView.rfqId, rfqId)) - .limit(1); - - return summaryResult[0] || null; - } catch (err) { - console.error("getRfqProgressSummary 에러:", err); - return null; - } + try { + const summaryResult = await db + .select() + .from(rfqProgressSummaryView) + .where(eq(rfqProgressSummaryView.rfqId, rfqId)) + .limit(1); + + return summaryResult[0] || null; + } catch (err) { + console.error("getRfqProgressSummary 에러:", err); + return null; + } } // 벤더 응답 파일 상세 조회 (향상된 정보 포함) export async function getVendorResponseFiles(vendorResponseId: number) { - try { - const files = await db - .select() - .from(vendorResponseAttachmentsEnhanced) - .where(eq(vendorResponseAttachmentsEnhanced.vendorResponseId, vendorResponseId)) - .orderBy(desc(vendorResponseAttachmentsEnhanced.uploadedAt)); + try { + const files = await db + .select() + .from(vendorResponseAttachmentsEnhanced) + .where(eq(vendorResponseAttachmentsEnhanced.vendorResponseId, vendorResponseId)) + .orderBy(desc(vendorResponseAttachmentsEnhanced.uploadedAt)); - return files; - } catch (err) { - console.error("getVendorResponseFiles 에러:", err); - return []; - } + return files; + } catch (err) { + console.error("getVendorResponseFiles 에러:", err); + return []; } +} // 타입 정의 확장 @@ -2440,53 +2443,53 @@ export type EnhancedVendorResponse = { rfqCode: string; rfqType: "INITIAL" | "FINAL"; rfqRecordId: number; - + // 첨부파일 정보 attachmentId: number; attachmentType: string; serialNo: string; attachmentDescription?: string; - + // 벤더 정보 vendorId: number; vendorCode: string; vendorName: string; vendorCountry: string; - + // 응답 상태 responseStatus: "NOT_RESPONDED" | "RESPONDED" | "REVISION_REQUESTED" | "WAIVED"; currentRevision: string; respondedRevision?: string; effectiveStatus: string; - + // 코멘트 관련 필드들 (새로 추가된 필드 포함) responseComment?: string; // 벤더가 응답할 때 작성하는 코멘트 vendorComment?: string; // 벤더 내부 메모 revisionRequestComment?: string; // 발주처가 수정 요청할 때 작성하는 사유 (새로 추가) - + // 날짜 관련 필드들 (새로 추가된 필드 포함) requestedAt: string; respondedAt?: string; revisionRequestedAt?: string; // 수정 요청 날짜 (새로 추가) - + // 발주처 최신 리비전 정보 latestClientRevisionNo?: string; latestClientFileName?: string; latestClientFileSize?: number; latestClientRevisionComment?: string; - + // 리비전 분석 isVersionMatched: boolean; versionLag?: number; needsUpdate: boolean; hasMultipleRevisions: boolean; - + // 응답 파일 통계 totalResponseFiles: number; latestResponseFileName?: string; latestResponseFileSize?: number; latestResponseUploadedAt?: string; - + // 첨부파일 정보 (리비전 히스토리 포함) attachment: { id: number; @@ -2506,7 +2509,7 @@ export type EnhancedVendorResponse = { isLatest: boolean; }>; }; - + // 벤더 응답 파일들 responseAttachments: Array<{ id: number; @@ -2592,10 +2595,366 @@ export async function requestRevision( } catch (error) { console.error("Request revision server action error:", error); - return { + return { success: false, message: "내부 서버 오류가 발생했습니다", error: "INTERNAL_ERROR", }; } +} + + + +export async function shortListConfirm(input: ShortListConfirmInput) { + try { + const validatedInput = shortListConfirmSchema.parse(input) + const { rfqId, selectedVendorIds, rejectedVendorIds } = validatedInput + + // 1. RFQ 정보 조회 + const rfqInfo = await db + .select() + .from(bRfqs) + .where(eq(bRfqs.id, rfqId)) + .limit(1) + + if (!rfqInfo.length) { + return { success: false, message: "RFQ를 찾을 수 없습니다." } + } + + const rfq = rfqInfo[0] + + // 2. 기존 initial_rfq에서 필요한 정보 조회 + const initialRfqData = await db + .select({ + id: initialRfq.id, + vendorId: initialRfq.vendorId, + dueDate: initialRfq.dueDate, + validDate: initialRfq.validDate, + incotermsCode: initialRfq.incotermsCode, + gtc: initialRfq.gtc, + gtcValidDate: initialRfq.gtcValidDate, + classification: initialRfq.classification, + sparepart: initialRfq.sparepart, + cpRequestYn: initialRfq.cpRequestYn, + prjectGtcYn: initialRfq.prjectGtcYn, + returnRevision: initialRfq.returnRevision, + }) + .from(initialRfq) + .where( + and( + eq(initialRfq.rfqId, rfqId), + inArray(initialRfq.vendorId, [...selectedVendorIds, ...rejectedVendorIds]) + ) + ) + + if (!initialRfqData.length) { + return { success: false, message: "해당 RFQ의 초기 RFQ 데이터를 찾을 수 없습니다." } + } + + // 3. 탈락된 벤더들의 이메일 정보 조회 + let rejectedVendorEmails: Array<{ + vendorId: number + vendorName: string + email: string + }> = [] + + if (rejectedVendorIds.length > 0) { + rejectedVendorEmails = await db + .select({ + vendorId: vendors.id, + vendorName: vendors.vendorName, + email: vendors.email, + }) + .from(vendors) + .where(inArray(vendors.id, rejectedVendorIds)) + } + + await db.transaction(async (tx) => { + // 4. 선택된 벤더들에 대해 final_rfq 테이블에 데이터 생성/업데이트 + for (const vendorId of selectedVendorIds) { + const initialData = initialRfqData.find(data => data.vendorId === vendorId) + + if (initialData) { + // 기존 final_rfq 레코드 확인 + const existingFinalRfq = await tx + .select() + .from(finalRfq) + .where( + and( + eq(finalRfq.rfqId, rfqId), + eq(finalRfq.vendorId, vendorId) + ) + ) + .limit(1) + + if (existingFinalRfq.length > 0) { + // 기존 레코드 업데이트 + await tx + .update(finalRfq) + .set({ + shortList: true, + finalRfqStatus: "DRAFT", + dueDate: initialData.dueDate, + validDate: initialData.validDate, + incotermsCode: initialData.incotermsCode, + gtc: initialData.gtc, + gtcValidDate: initialData.gtcValidDate, + classification: initialData.classification, + sparepart: initialData.sparepart, + cpRequestYn: initialData.cpRequestYn, + prjectGtcYn: initialData.prjectGtcYn, + updatedAt: new Date(), + }) + .where(eq(finalRfq.id, existingFinalRfq[0].id)) + } else { + // 새 레코드 생성 + await tx + .insert(finalRfq) + .values({ + rfqId, + vendorId, + finalRfqStatus: "DRAFT", + dueDate: initialData.dueDate, + validDate: initialData.validDate, + incotermsCode: initialData.incotermsCode, + gtc: initialData.gtc, + gtcValidDate: initialData.gtcValidDate, + classification: initialData.classification, + sparepart: initialData.sparepart, + shortList: true, + returnYn: false, + cpRequestYn: initialData.cpRequestYn, + prjectGtcYn: initialData.prjectGtcYn, + returnRevision: 0, + currency: "KRW", + taxCode: "VV", + deliveryDate: new Date(Date.now() + 30 * 24 * 60 * 60 * 1000), // 30일 후 + firsttimeYn: true, + materialPriceRelatedYn: false, + }) + } + } + } + + // 5. 탈락된 벤더들에 대해서는 shortList: false로 설정 (있다면) + if (rejectedVendorIds.length > 0) { + // 기존에 final_rfq에 있는 탈락 벤더들은 shortList를 false로 업데이트 + await tx + .update(finalRfq) + .set({ + shortList: false, + updatedAt: new Date(), + }) + .where( + and( + eq(finalRfq.rfqId, rfqId), + inArray(finalRfq.vendorId, rejectedVendorIds) + ) + ) + } + + // 6. initial_rfq의 shortList 필드도 업데이트 + if (selectedVendorIds.length > 0) { + await tx + .update(initialRfq) + .set({ + shortList: true, + updatedAt: new Date(), + }) + .where( + and( + eq(initialRfq.rfqId, rfqId), + inArray(initialRfq.vendorId, selectedVendorIds) + ) + ) + } + + if (rejectedVendorIds.length > 0) { + await tx + .update(initialRfq) + .set({ + shortList: false, + updatedAt: new Date(), + }) + .where( + and( + eq(initialRfq.rfqId, rfqId), + inArray(initialRfq.vendorId, rejectedVendorIds) + ) + ) + } + }) + + // 7. 탈락된 벤더들에게 Letter of Regret 이메일 발송 + const emailErrors: string[] = [] + + for (const rejectedVendor of rejectedVendorEmails) { + if (rejectedVendor.email) { + try { + await sendEmail({ + to: rejectedVendor.email, + subject: `Letter of Regret - RFQ ${rfq.rfqCode}`, + template: "letter-of-regret", + context: { + rfqCode: rfq.rfqCode, + vendorName: rejectedVendor.vendorName, + projectTitle: rfq.projectTitle || "Project", + dateTime: new Date().toLocaleDateString("ko-KR", { + year: "numeric", + month: "long", + day: "numeric", + }), + companyName: "Your Company Name", // 실제 회사명으로 변경 + language: "ko", + }, + }) + } catch (error) { + console.error(`Email sending failed for vendor ${rejectedVendor.vendorName}:`, error) + emailErrors.push(`${rejectedVendor.vendorName}에게 이메일 발송 실패`) + } + } + } + + // 8. 페이지 revalidation + revalidatePath(`/evcp/a-rfq/${rfqId}`) + revalidatePath(`/evcp/b-rfq/${rfqId}`) + + const successMessage = `Short List가 확정되었습니다. (선택: ${selectedVendorIds.length}개, 탈락: ${rejectedVendorIds.length}개)` + + return { + success: true, + message: successMessage, + errors: emailErrors.length > 0 ? emailErrors : undefined, + data: { + selectedCount: selectedVendorIds.length, + rejectedCount: rejectedVendorIds.length, + emailsSent: rejectedVendorEmails.length - emailErrors.length, + }, + } + + } catch (error) { + console.error("Short List confirm error:", error) + return { + success: false, + message: "Short List 확정 중 오류가 발생했습니다.", + } + } +} + +export async function getFinalRfqDetail(input: GetFinalRfqDetailSchema, rfqId?: number) { + + try { + const offset = (input.page - 1) * input.perPage; + + // 1) 고급 필터 조건 + let advancedWhere: SQL<unknown> | undefined = undefined; + if (input.filters && input.filters.length > 0) { + advancedWhere = filterColumns({ + table: finalRfqDetailView, + filters: input.filters, + joinOperator: input.joinOperator || 'and', + }); + } + + // 2) 기본 필터 조건 + let basicWhere: SQL<unknown> | undefined = undefined; + if (input.basicFilters && input.basicFilters.length > 0) { + basicWhere = filterColumns({ + table: finalRfqDetailView, + filters: input.basicFilters, + joinOperator: input.basicJoinOperator || 'and', + }); + } + + let rfqIdWhere: SQL<unknown> | undefined = undefined; + if (rfqId) { + rfqIdWhere = eq(finalRfqDetailView.rfqId, rfqId); + } + + // 3) 글로벌 검색 조건 + let globalWhere: SQL<unknown> | undefined = undefined; + if (input.search) { + const s = `%${input.search}%`; + + const validSearchConditions: SQL<unknown>[] = []; + + const rfqCodeCondition = ilike(finalRfqDetailView.rfqCode, s); + if (rfqCodeCondition) validSearchConditions.push(rfqCodeCondition); + + const vendorNameCondition = ilike(finalRfqDetailView.vendorName, s); + if (vendorNameCondition) validSearchConditions.push(vendorNameCondition); + + const vendorCodeCondition = ilike(finalRfqDetailView.vendorCode, s); + if (vendorCodeCondition) validSearchConditions.push(vendorCodeCondition); + + const vendorCountryCondition = ilike(finalRfqDetailView.vendorCountry, s); + if (vendorCountryCondition) validSearchConditions.push(vendorCountryCondition); + + const incotermsDescriptionCondition = ilike(finalRfqDetailView.incotermsDescription, s); + if (incotermsDescriptionCondition) validSearchConditions.push(incotermsDescriptionCondition); + + const paymentTermsDescriptionCondition = ilike(finalRfqDetailView.paymentTermsDescription, s); + if (paymentTermsDescriptionCondition) validSearchConditions.push(paymentTermsDescriptionCondition); + + const classificationCondition = ilike(finalRfqDetailView.classification, s); + if (classificationCondition) validSearchConditions.push(classificationCondition); + + const sparepartCondition = ilike(finalRfqDetailView.sparepart, s); + if (sparepartCondition) validSearchConditions.push(sparepartCondition); + + if (validSearchConditions.length > 0) { + globalWhere = or(...validSearchConditions); + } + } + + // 5) 최종 WHERE 조건 생성 + const whereConditions: SQL<unknown>[] = []; + + if (advancedWhere) whereConditions.push(advancedWhere); + if (basicWhere) whereConditions.push(basicWhere); + if (globalWhere) whereConditions.push(globalWhere); + if (rfqIdWhere) whereConditions.push(rfqIdWhere); + + const finalWhere = whereConditions.length > 0 ? and(...whereConditions) : undefined; + + // 6) 전체 데이터 수 조회 + const totalResult = await db + .select({ count: count() }) + .from(finalRfqDetailView) + .where(finalWhere); + + const total = totalResult[0]?.count || 0; + + if (total === 0) { + return { data: [], pageCount: 0, total: 0 }; + } + + console.log(totalResult); + console.log(total); + + // 7) 정렬 및 페이징 처리된 데이터 조회 + const orderByColumns = input.sort.map((sort) => { + const column = sort.id as keyof typeof finalRfqDetailView.$inferSelect; + return sort.desc ? desc(finalRfqDetailView[column]) : asc(finalRfqDetailView[column]); + }); + + if (orderByColumns.length === 0) { + orderByColumns.push(desc(finalRfqDetailView.createdAt)); + } + + const finalRfqData = await db + .select() + .from(finalRfqDetailView) + .where(finalWhere) + .orderBy(...orderByColumns) + .limit(input.perPage) + .offset(offset); + + const pageCount = Math.ceil(total / input.perPage); + + return { data: finalRfqData, pageCount, total }; + } catch (err) { + console.error("Error in getFinalRfqDetail:", err); + return { data: [], pageCount: 0, total: 0 }; + } }
\ No newline at end of file |