diff options
Diffstat (limited to 'lib/b-rfq/service.ts')
| -rw-r--r-- | lib/b-rfq/service.ts | 2976 |
1 files changed, 0 insertions, 2976 deletions
diff --git a/lib/b-rfq/service.ts b/lib/b-rfq/service.ts deleted file mode 100644 index 896a082d..00000000 --- a/lib/b-rfq/service.ts +++ /dev/null @@ -1,2976 +0,0 @@ -'use server' - -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, - attachmentRevisionHistoryView, - rfqProgressSummaryView, - 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, 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" -import { getErrorMessage } from "../handle-error" -import { AddInitialRfqFormData } from "./initial/add-initial-rfq-dialog" -import { sendEmail } from "../mail/sendEmail" -import { RfqType } from "../rfqs/validations" - -const tag = { - initialRfqDetail: "initial-rfq", - rfqDashboard: 'rfq-dashboard', - rfq: (id: number) => `rfq-${id}`, - rfqAttachments: (rfqId: number) => `rfq-attachments-${rfqId}`, - attachmentRevisions: (attId: number) => `attachment-revisions-${attId}`, - vendorResponses: ( - attId: number, - type: 'INITIAL' | 'FINAL' = 'INITIAL', - ) => `vendor-responses-${attId}-${type}`, -} as const; - -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, - }); - } - - // 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}%`; - - const validSearchConditions: SQL<unknown>[] = []; - - const rfqCodeCondition = ilike(rfqDashboardView.rfqCode, s); - if (rfqCodeCondition) validSearchConditions.push(rfqCodeCondition); - - const descriptionCondition = ilike(rfqDashboardView.description, s); - if (descriptionCondition) validSearchConditions.push(descriptionCondition); - - const projectNameCondition = ilike(rfqDashboardView.projectName, s); - if (projectNameCondition) validSearchConditions.push(projectNameCondition); - - const projectCodeCondition = ilike(rfqDashboardView.projectCode, s); - if (projectCodeCondition) validSearchConditions.push(projectCodeCondition); - - const picNameCondition = ilike(rfqDashboardView.picName, s); - if (picNameCondition) validSearchConditions.push(picNameCondition); - - const packageNoCondition = ilike(rfqDashboardView.packageNo, s); - if (packageNoCondition) validSearchConditions.push(packageNoCondition); - - const packageNameCondition = ilike(rfqDashboardView.packageName, s); - if (packageNameCondition) validSearchConditions.push(packageNameCondition); - - if (validSearchConditions.length > 0) { - globalWhere = or(...validSearchConditions); - } - } - - - - // 6) 최종 WHERE 조건 생성 - const whereConditions: SQL<unknown>[] = []; - - if (advancedWhere) whereConditions.push(advancedWhere); - if (basicWhere) whereConditions.push(basicWhere); - if (globalWhere) whereConditions.push(globalWhere); - - const finalWhere = whereConditions.length > 0 ? and(...whereConditions) : undefined; - - // 7) 전체 데이터 수 조회 - const totalResult = await db - .select({ count: count() }) - .from(rfqDashboardView) - .where(finalWhere); - - const total = totalResult[0]?.count || 0; - - if (total === 0) { - return { data: [], pageCount: 0, total: 0 }; - } - - 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]); - }); - - 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 }; - } - -} - -// 헬퍼 함수들 -function createRFQFilterMapping() { - return { - // 뷰의 컬럼명과 실제 필터링할 컬럼 매핑 - rfqCode: rfqDashboardView.rfqCode, - description: rfqDashboardView.description, - status: rfqDashboardView.status, - projectName: rfqDashboardView.projectName, - projectCode: rfqDashboardView.projectCode, - picName: rfqDashboardView.picName, - packageNo: rfqDashboardView.packageNo, - packageName: rfqDashboardView.packageName, - dueDate: rfqDashboardView.dueDate, - overallProgress: rfqDashboardView.overallProgress, - createdAt: rfqDashboardView.createdAt, - }; -} - -function getRFQJoinedTables() { - return { - // 조인된 테이블 정보 (뷰이므로 실제로는 사용되지 않을 수 있음) - projects, - users, - }; -} - -// ================================================================ -// 3. RFQ Dashboard 타입 정의 -// ================================================================ - -async function generateNextSerial(picCode: string): Promise<string> { - try { - // 해당 picCode로 시작하는 RFQ 개수 조회 - const existingCount = await db - .select({ count: count() }) - .from(bRfqs) - .where(eq(bRfqs.picCode, picCode)) - - const nextSerial = (existingCount[0]?.count || 0) + 1 - return nextSerial.toString().padStart(5, '0') // 5자리로 패딩 - } catch (error) { - console.error("시리얼 번호 생성 오류:", error) - return "00001" // 기본값 - } -} - -export async function createRfqAction(input: CreateRfqInput) { - try { - // 입력 데이터 검증 - const validatedData = createRfqServerSchema.parse(input) - - // RFQ 코드 자동 생성: N + picCode + 시리얼5자리 - const serialNumber = await generateNextSerial(validatedData.picCode) - const rfqCode = `N${validatedData.picCode}${serialNumber}` - - // 데이터베이스에 삽입 - const result = await db.insert(bRfqs).values({ - rfqCode, - projectId: validatedData.projectId, - dueDate: validatedData.dueDate, - status: "DRAFT", - picCode: validatedData.picCode, - picName: validatedData.picName || null, - EngPicName: validatedData.engPicName || null, - packageNo: validatedData.packageNo || null, - packageName: validatedData.packageName || null, - remark: validatedData.remark || null, - projectCompany: validatedData.projectCompany || null, - projectFlag: validatedData.projectFlag || null, - projectSite: validatedData.projectSite || null, - createdBy: validatedData.createdBy, - updatedBy: validatedData.updatedBy, - }).returning({ - id: bRfqs.id, - rfqCode: bRfqs.rfqCode, - }) - - - - return { - success: true, - data: result[0], - message: "RFQ가 성공적으로 생성되었습니다", - } - - } catch (error) { - console.error("RFQ 생성 오류:", error) - - - return { - success: false, - error: "RFQ 생성에 실패했습니다", - } - } -} - -// RFQ 코드 중복 확인 액션 -export async function checkRfqCodeExists(rfqCode: string) { - try { - const existing = await db.select({ id: bRfqs.id }) - .from(bRfqs) - .where(eq(bRfqs.rfqCode, rfqCode)) - .limit(1) - - return existing.length > 0 - } catch (error) { - console.error("RFQ 코드 확인 오류:", error) - return false - } -} - -// picCode별 다음 예상 RFQ 코드 미리보기 -export async function previewNextRfqCode(picCode: string) { - try { - const serialNumber = await generateNextSerial(picCode) - return `N${picCode}${serialNumber}` - } catch (error) { - console.error("RFQ 코드 미리보기 오류:", error) - return `N${picCode}00001` - } -} - -const getBRfqById = async (id: number): Promise<RfqDashboardView | null> => { - // 1) RFQ 단건 조회 - const rfqsRes = await db - .select() - .from(rfqDashboardView) - .where(eq(rfqDashboardView.rfqId, id)) - .limit(1); - - if (rfqsRes.length === 0) return null; - const rfqRow = rfqsRes[0]; - - // 3) RfqWithItems 형태로 반환 - const result: RfqDashboardView = { - ...rfqRow, - - }; - - return result; -}; - - -export const findBRfqById = async (id: number): Promise<RfqDashboardView | null> => { - try { - - const rfq = await getBRfqById(id); - - return rfq; - } catch (error) { - throw new Error('Failed to fetch user'); - } -}; - - -export async function getRfqAttachments( - input: GetRfqAttachmentsSchema, - rfqId: number -) { - try { - const offset = (input.page - 1) * input.perPage - - // 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 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 - ) - - // 정렬 (메인 테이블 기준) - 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 { 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 - - return { data, total } - }) - - const pageCount = Math.ceil(total / input.perPage) - - // 각 첨부파일별 벤더 응답 통계 조회 - const attachmentIds = data.map(item => item.id) - let responseStatsMap: Record<number, any> = {} - - if (attachmentIds.length > 0) { - responseStatsMap = await getAttachmentResponseStats(attachmentIds) - } - - // 통계 데이터 병합 - 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 } - } - -} - -// 첨부파일별 벤더 응답 통계 조회 -async function getAttachmentResponseStats(attachmentIds: number[]) { - try { - const stats = await db - .select({ - attachmentId: vendorAttachmentResponses.attachmentId, - totalVendors: count(), - respondedCount: sql<number>`count(case when ${vendorAttachmentResponses.responseStatus} = 'RESPONDED' then 1 end)`, - pendingCount: sql<number>`count(case when ${vendorAttachmentResponses.responseStatus} = 'NOT_RESPONDED' then 1 end)`, - waivedCount: sql<number>`count(case when ${vendorAttachmentResponses.responseStatus} = 'WAIVED' then 1 end)`, - }) - .from(vendorAttachmentResponses) - .where(inArray(vendorAttachmentResponses.attachmentId, attachmentIds)) - .groupBy(vendorAttachmentResponses.attachmentId) - - // 응답률 계산해서 객체로 변환 - const statsMap: Record<number, any> = {} - stats.forEach(stat => { - const activeVendors = stat.totalVendors - stat.waivedCount - const responseRate = activeVendors > 0 - ? Math.round((stat.respondedCount / activeVendors) * 100) - : 0 - - statsMap[stat.attachmentId] = { - totalVendors: stat.totalVendors, - respondedCount: stat.respondedCount, - pendingCount: stat.pendingCount, - waivedCount: stat.waivedCount, - responseRate - } - }) - - return statsMap - } catch (error) { - console.error("getAttachmentResponseStats error:", error) - return {} - } -} - -// 특정 첨부파일에 대한 벤더 응답 현황 상세 조회 -export async function getVendorResponsesForAttachment( - attachmentId: number, - rfqType: 'INITIAL' | 'FINAL' = 'INITIAL' -) { - try { - // 1. 기본 벤더 응답 정보 가져오기 (첨부파일 정보와 조인) - const responses = await db - .select({ - id: vendorAttachmentResponses.id, - attachmentId: vendorAttachmentResponses.attachmentId, - vendorId: vendorAttachmentResponses.vendorId, - vendorCode: vendors.vendorCode, - vendorName: vendors.vendorName, - vendorCountry: vendors.country, - rfqType: vendorAttachmentResponses.rfqType, - rfqRecordId: vendorAttachmentResponses.rfqRecordId, - responseStatus: vendorAttachmentResponses.responseStatus, - - // 첨부파일의 현재 리비전 (가장 중요!) - currentRevision: bRfqsAttachments.currentRevision, - - // 벤더가 응답한 리비전 - respondedRevision: vendorAttachmentResponses.respondedRevision, - - responseComment: vendorAttachmentResponses.responseComment, - vendorComment: vendorAttachmentResponses.vendorComment, - - // 새로 추가된 필드들 - revisionRequestComment: vendorAttachmentResponses.revisionRequestComment, - revisionRequestedAt: vendorAttachmentResponses.revisionRequestedAt, - requestedAt: vendorAttachmentResponses.requestedAt, - respondedAt: vendorAttachmentResponses.respondedAt, - updatedAt: vendorAttachmentResponses.updatedAt, - }) - .from(vendorAttachmentResponses) - .leftJoin(vendors, eq(vendorAttachmentResponses.vendorId, vendors.id)) - .leftJoin(bRfqsAttachments, eq(vendorAttachmentResponses.attachmentId, bRfqsAttachments.id)) - .where( - and( - eq(vendorAttachmentResponses.attachmentId, attachmentId), - eq(vendorAttachmentResponses.rfqType, rfqType) - ) - ) - .orderBy(vendors.vendorName); - - // 2. 각 응답에 대한 파일 정보 가져오기 - const responseIds = responses.map(r => r.id); - - let responseFiles: any[] = []; - if (responseIds.length > 0) { - responseFiles = await db - .select({ - id: vendorResponseAttachmentsB.id, - vendorResponseId: vendorResponseAttachmentsB.vendorResponseId, - fileName: vendorResponseAttachmentsB.fileName, - originalFileName: vendorResponseAttachmentsB.originalFileName, - filePath: vendorResponseAttachmentsB.filePath, - fileSize: vendorResponseAttachmentsB.fileSize, - fileType: vendorResponseAttachmentsB.fileType, - description: vendorResponseAttachmentsB.description, - uploadedAt: vendorResponseAttachmentsB.uploadedAt, - }) - .from(vendorResponseAttachmentsB) - .where(inArray(vendorResponseAttachmentsB.vendorResponseId, responseIds)) - .orderBy(desc(vendorResponseAttachmentsB.uploadedAt)); - } - - // 3. 응답에 파일 정보 병합 및 리비전 상태 체크 - const enhancedResponses = responses.map(response => { - const files = responseFiles.filter(file => file.vendorResponseId === response.id); - const latestFile = files - .sort((a, b) => new Date(b.uploadedAt).getTime() - new Date(a.uploadedAt).getTime())[0] || null; - - // 벤더가 최신 리비전에 응답했는지 체크 - const isUpToDate = response.respondedRevision === response.currentRevision; - - return { - ...response, - files, - totalFiles: files.length, - latestFile, - isUpToDate, // 최신 리비전 응답 여부 - }; - }); - - return enhancedResponses; - } catch (err) { - console.error("getVendorResponsesForAttachment error:", err); - return []; - } -} - -export async function confirmDocuments(rfqId: number) { - try { - const session = await getServerSession(authOptions) - if (!session?.user?.id) { - throw new Error("인증이 필요합니다.") - } - - // TODO: RFQ 상태를 "Doc. Confirmed"로 업데이트 - await db - .update(bRfqs) - .set({ - status: "Doc. Confirmed", - updatedBy: Number(session.user.id), - updatedAt: new Date(), - }) - .where(eq(bRfqs.id, rfqId)) - - - return { - success: true, - message: "문서가 확정되었습니다.", - } - - } catch (error) { - console.error("confirmDocuments error:", error) - return { - success: false, - message: error instanceof Error ? error.message : "문서 확정 중 오류가 발생했습니다.", - } - } -} - -// TBE 요청 서버 액션 -export async function requestTbe(rfqId: number, attachmentIds?: number[]) { - try { - const session = await getServerSession(authOptions) - if (!session?.user?.id) { - throw new Error("인증이 필요합니다.") - } - - // attachmentIds가 제공된 경우 해당 첨부파일들만 처리 - let targetAttachments = [] - if (attachmentIds && attachmentIds.length > 0) { - // 선택된 첨부파일들 조회 - targetAttachments = await db - .select({ - id: bRfqsAttachments.id, - serialNo: bRfqsAttachments.serialNo, - attachmentType: bRfqsAttachments.attachmentType, - currentRevision: bRfqsAttachments.currentRevision, - }) - .from(bRfqsAttachments) - .where( - and( - eq(bRfqsAttachments.rfqId, rfqId), - inArray(bRfqsAttachments.id, attachmentIds) - ) - ) - - if (targetAttachments.length === 0) { - throw new Error("선택된 첨부파일을 찾을 수 없습니다.") - } - } else { - // 전체 RFQ의 모든 첨부파일 처리 - targetAttachments = await db - .select({ - id: bRfqsAttachments.id, - serialNo: bRfqsAttachments.serialNo, - attachmentType: bRfqsAttachments.attachmentType, - currentRevision: bRfqsAttachments.currentRevision, - }) - .from(bRfqsAttachments) - .where(eq(bRfqsAttachments.rfqId, rfqId)) - } - - if (targetAttachments.length === 0) { - throw new Error("TBE 요청할 첨부파일이 없습니다.") - } - - // TODO: TBE 요청 로직 구현 - // 1. RFQ 상태를 "TBE started"로 업데이트 (선택적) - // 2. 선택된 첨부파일들에 대해 벤더들에게 TBE 요청 이메일 발송 - // 3. vendorAttachmentResponses 테이블에 TBE 요청 레코드 생성 - // 4. TBE 관련 메타데이터 업데이트 - - - - // 예시: 선택된 첨부파일들에 대한 벤더 응답 레코드 생성 - 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() - - // 각 첨부파일에 대해 벤더 응답 레코드 생성 또는 업데이트 - for (const attachment of targetAttachments) { - // TODO: 해당 첨부파일과 연관된 벤더들에게 TBE 요청 처리 - console.log(`TBE 요청 처리: ${attachment.serialNo} (${attachment.currentRevision})`) - } - }) - - - const attachmentCount = targetAttachments.length - const attachmentList = targetAttachments - .map(a => `${a.serialNo} (${a.currentRevision})`) - .join(', ') - - return { - success: true, - message: `${attachmentCount}개 문서에 대한 TBE 요청이 전송되었습니다.\n대상: ${attachmentList}`, - targetAttachments, - } - - } catch (error) { - console.error("requestTbe error:", error) - return { - success: false, - message: error instanceof Error ? error.message : "TBE 요청 중 오류가 발생했습니다.", - } - } -} - -// 다음 시리얼 번호 생성 -async function getNextSerialNo(rfqId: number): Promise<string> { - try { - // 해당 RFQ의 기존 첨부파일 개수 조회 - const [result] = await db - .select({ count: count() }) - .from(bRfqsAttachments) - .where(eq(bRfqsAttachments.rfqId, rfqId)) - - const nextNumber = (result?.count || 0) + 1 - - // 001, 002, 003... 형태로 포맷팅 - return nextNumber.toString().padStart(3, '0') - - } catch (error) { - console.error("getNextSerialNo error:", error) - // 에러 발생 시 타임스탬프 기반으로 fallback - return Date.now().toString().slice(-3) - } -} - -export async function addRfqAttachmentRecord(record: AttachmentRecord) { - try { - const session = await getServerSession(authOptions) - if (!session?.user?.id) { - throw new Error("인증이 필요합니다.") - } - - const validatedRecord = attachmentRecordSchema.parse(record) - const userId = Number(session.user.id) - - const result = await db.transaction(async (tx) => { - // 1. 시리얼 번호 생성 - const [countResult] = await tx - .select({ count: count() }) - .from(bRfqsAttachments) - .where(eq(bRfqsAttachments.rfqId, validatedRecord.rfqId)) - - const serialNo = (countResult.count + 1).toString().padStart(3, '0') - - // 2. 메인 첨부파일 레코드 생성 - const [attachment] = await tx - .insert(bRfqsAttachments) - .values({ - rfqId: validatedRecord.rfqId, - attachmentType: validatedRecord.attachmentType, - serialNo: serialNo, - currentRevision: "Rev.0", - description: validatedRecord.description, - createdBy: userId, - }) - .returning() - - // 3. 초기 리비전 (Rev.0) 생성 - const [revision] = await tx - .insert(bRfqAttachmentRevisions) - .values({ - attachmentId: attachment.id, - revisionNo: "Rev.0", - fileName: validatedRecord.fileName, - originalFileName: validatedRecord.originalFileName, - filePath: validatedRecord.filePath, - fileSize: validatedRecord.fileSize, - fileType: validatedRecord.fileType, - revisionComment: validatedRecord.revisionComment, - isLatest: true, - createdBy: userId, - }) - .returning() - - // 4. 메인 테이블의 latest_revision_id 업데이트 - await tx - .update(bRfqsAttachments) - .set({ - latestRevisionId: revision.id, - updatedAt: new Date(), - }) - .where(eq(bRfqsAttachments.id, attachment.id)) - - return { attachment, revision } - }) - - return { - success: true, - message: `파일이 성공적으로 등록되었습니다. (시리얼: ${result.attachment.serialNo}, 리비전: Rev.0)`, - attachment: result.attachment, - revision: result.revision, - } - - } catch (error) { - console.error("addRfqAttachmentRecord error:", error) - return { - success: false, - message: error instanceof Error ? error.message : "첨부파일 등록 중 오류가 발생했습니다.", - } - } -} - -// 리비전 추가 (기존 첨부파일에 새 버전 추가) -export async function addRevisionToAttachment( - attachmentId: number, - revisionData: { - fileName: string; - originalFileName: string; - filePath: string; - fileSize: number; - fileType: string; - revisionComment?: string; - }, -) { - try { - const session = await getServerSession(authOptions); - if (!session?.user?.id) throw new Error('인증이 필요합니다.'); - - const userId = Number(session.user.id); - - // ──────────────────────────────────────────────────────────────────────────── - // 0. 첨부파일의 rfqId 사전 조회 (태그 무효화를 위해 필요) - // ──────────────────────────────────────────────────────────────────────────── - const [attInfo] = await db - .select({ rfqId: bRfqsAttachments.rfqId }) - .from(bRfqsAttachments) - .where(eq(bRfqsAttachments.id, attachmentId)) - .limit(1); - - if (!attInfo) throw new Error('첨부파일을 찾을 수 없습니다.'); - const rfqId = attInfo.rfqId; - - // ──────────────────────────────────────────────────────────────────────────── - // 1‑5. 리비전 트랜잭션 - // ──────────────────────────────────────────────────────────────────────────── - const newRevision = await db.transaction(async (tx) => { - // 1. 현재 최신 리비전 조회 - const [latestRevision] = await tx - .select({ revisionNo: bRfqAttachmentRevisions.revisionNo }) - .from(bRfqAttachmentRevisions) - .where( - and( - eq(bRfqAttachmentRevisions.attachmentId, attachmentId), - eq(bRfqAttachmentRevisions.isLatest, true), - ), - ); - - if (!latestRevision) throw new Error('기존 첨부파일을 찾을 수 없습니다.'); - - // 2. 새 리비전 번호 생성 - const currentNum = parseInt(latestRevision.revisionNo.replace('Rev.', '')); - const newRevisionNo = `Rev.${currentNum + 1}`; - - // 3. 기존 리비전 isLatest → false - await tx - .update(bRfqAttachmentRevisions) - .set({ isLatest: false }) - .where( - and( - eq(bRfqAttachmentRevisions.attachmentId, attachmentId), - eq(bRfqAttachmentRevisions.isLatest, true), - ), - ); - - // 4. 새 리비전 INSERT - const [inserted] = await tx - .insert(bRfqAttachmentRevisions) - .values({ - attachmentId, - revisionNo: newRevisionNo, - fileName: revisionData.fileName, - originalFileName: revisionData.originalFileName, - filePath: revisionData.filePath, - fileSize: revisionData.fileSize, - fileType: revisionData.fileType, - revisionComment: revisionData.revisionComment ?? `${newRevisionNo} 업데이트`, - isLatest: true, - createdBy: userId, - }) - .returning(); - - // 5. 메인 첨부파일 row 업데이트 - await tx - .update(bRfqsAttachments) - .set({ - currentRevision: newRevisionNo, - latestRevisionId: inserted.id, - updatedAt: new Date(), - }) - .where(eq(bRfqsAttachments.id, attachmentId)); - - return inserted; - }); - - - - return { - success: true, - message: `새 리비전(${newRevision.revisionNo})이 성공적으로 추가되었습니다.`, - revision: newRevision, - }; - } catch (error) { - console.error('addRevisionToAttachment error:', error); - return { - success: false, - message: error instanceof Error ? error.message : '리비전 추가 중 오류가 발생했습니다.', - }; - } -} - -// 특정 첨부파일의 모든 리비전 조회 -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)) - - return { - success: true, - revisions, - } - } catch (error) { - console.error("getAttachmentRevisions error:", error) - return { - success: false, - message: "리비전 조회 중 오류가 발생했습니다.", - revisions: [], - } - } -} - - -// 첨부파일 삭제 (리비전 포함) -export async function deleteRfqAttachments(input: DeleteAttachmentsInput) { - try { - const session = await getServerSession(authOptions) - if (!session?.user?.id) { - throw new Error("인증이 필요합니다.") - } - - const validatedInput = deleteAttachmentsSchema.parse(input) - - const result = await db.transaction(async (tx) => { - // 1. 삭제할 첨부파일들의 정보 조회 (파일 경로 포함) - const attachmentsToDelete = await tx - .select({ - id: bRfqsAttachments.id, - rfqId: bRfqsAttachments.rfqId, - serialNo: bRfqsAttachments.serialNo, - }) - .from(bRfqsAttachments) - .where(inArray(bRfqsAttachments.id, validatedInput.ids)) - - if (attachmentsToDelete.length === 0) { - throw new Error("삭제할 첨부파일을 찾을 수 없습니다.") - } - - // 2. 관련된 모든 리비전 파일 경로 조회 - const revisionFilePaths = await tx - .select({ filePath: bRfqAttachmentRevisions.filePath }) - .from(bRfqAttachmentRevisions) - .where(inArray(bRfqAttachmentRevisions.attachmentId, validatedInput.ids)) - - // 3. DB에서 리비전 삭제 (CASCADE로 자동 삭제되지만 명시적으로) - await tx - .delete(bRfqAttachmentRevisions) - .where(inArray(bRfqAttachmentRevisions.attachmentId, validatedInput.ids)) - - // 4. DB에서 첨부파일 삭제 - await tx - .delete(bRfqsAttachments) - .where(inArray(bRfqsAttachments.id, validatedInput.ids)) - - // 5. 실제 파일 삭제 (비동기로 처리) - Promise.all( - revisionFilePaths.map(async ({ filePath }) => { - try { - if (filePath) { - const fullPath = `${process.cwd()}/public${filePath}` - await unlink(fullPath) - } - } catch (fileError) { - console.warn(`Failed to delete file: ${filePath}`, fileError) - } - }) - ).catch(error => { - console.error("Some files failed to delete:", error) - }) - - return { - deletedCount: attachmentsToDelete.length, - rfqIds: [...new Set(attachmentsToDelete.map(a => a.rfqId))], - attachments: attachmentsToDelete, - } - }) - - - return { - success: true, - message: `${result.deletedCount}개의 첨부파일이 삭제되었습니다.`, - deletedAttachments: result.attachments, - } - - } catch (error) { - console.error("deleteRfqAttachments error:", error) - - return { - success: false, - message: error instanceof Error ? error.message : "첨부파일 삭제 중 오류가 발생했습니다.", - } - } -} - - - -//Initial RFQ - -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', - }); - } - - // 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); - } - - - // 3) 글로벌 검색 조건 - let globalWhere: SQL<unknown> | undefined = undefined; - if (input.search) { - const s = `%${input.search}%`; - - const validSearchConditions: SQL<unknown>[] = []; - - const rfqCodeCondition = ilike(initialRfqDetailView.rfqCode, s); - if (rfqCodeCondition) validSearchConditions.push(rfqCodeCondition); - - const vendorNameCondition = ilike(initialRfqDetailView.vendorName, s); - if (vendorNameCondition) validSearchConditions.push(vendorNameCondition); - - const vendorCodeCondition = ilike(initialRfqDetailView.vendorCode, s); - if (vendorCodeCondition) validSearchConditions.push(vendorCodeCondition); - - const vendorCountryCondition = ilike(initialRfqDetailView.vendorCountry, s); - if (vendorCountryCondition) validSearchConditions.push(vendorCountryCondition); - - const incotermsDescriptionCondition = ilike(initialRfqDetailView.incotermsDescription, s); - if (incotermsDescriptionCondition) validSearchConditions.push(incotermsDescriptionCondition); - - const classificationCondition = ilike(initialRfqDetailView.classification, s); - if (classificationCondition) validSearchConditions.push(classificationCondition); - - const sparepartCondition = ilike(initialRfqDetailView.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(initialRfqDetailView) - .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 initialRfqDetailView.$inferSelect; - return sort.desc ? desc(initialRfqDetailView[column]) : asc(initialRfqDetailView[column]); - }); - - 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 }; - } -} - -export async function getVendorsForSelection() { - try { - const vendorsData = await db - .select({ - id: vendors.id, - vendorName: vendors.vendorName, - vendorCode: vendors.vendorCode, - taxId: vendors.taxId, - country: vendors.country, - status: vendors.status, - }) - .from(vendors) - // .where( - // and( - // ne(vendors.status, "BLACKLISTED"), - // ne(vendors.status, "REJECTED") - // ) - // ) - .orderBy(vendors.vendorName) - - - return vendorsData.map(vendor => ({ - id: vendor.id, - vendorName: vendor.vendorName || "", - vendorCode: vendor.vendorCode || "", - country: vendor.country || "", - status: vendor.status, - })) - } catch (error) { - console.log("Error fetching vendors:", error) - throw new Error("Failed to fetch vendors") - } -} - -export async function addInitialRfqRecord(data: AddInitialRfqFormData & { rfqId: number }) { - try { - console.log('Incoming data:', data); - - const [newRecord] = await db - .insert(initialRfq) - .values({ - rfqId: data.rfqId, - vendorId: data.vendorId, - initialRfqStatus: data.initialRfqStatus, - dueDate: data.dueDate, - validDate: data.validDate, - incotermsCode: data.incotermsCode, - gtc: data.gtc, - gtcValidDate: data.gtcValidDate, - classification: data.classification, - sparepart: data.sparepart, - shortList: data.shortList, - returnYn: data.returnYn, - cpRequestYn: data.cpRequestYn, - prjectGtcYn: data.prjectGtcYn, - returnRevision: data.returnRevision, - }) - .returning() - - return { - success: true, - message: "초기 RFQ가 성공적으로 추가되었습니다.", - data: newRecord, - } - } catch (error) { - console.error("Error adding initial RFQ:", error) - return { - success: false, - message: "초기 RFQ 추가에 실패했습니다.", - error, - } - } -} - -export async function getIncotermsForSelection() { - try { - const incotermData = await db - .select({ - code: incoterms.code, - description: incoterms.description, - }) - .from(incoterms) - .orderBy(incoterms.code) - - return incotermData - - } catch (error) { - console.error("Error fetching incoterms:", error) - throw new Error("Failed to fetch incoterms") - } -} - -export async function removeInitialRfqs(input: RemoveInitialRfqsSchema) { - unstable_noStore() - try { - const { ids } = removeInitialRfqsSchema.parse(input) - - await db.transaction(async (tx) => { - await tx.delete(initialRfq).where(inArray(initialRfq.id, ids)) - }) - - - return { - data: null, - error: null, - } - } catch (err) { - return { - data: null, - error: getErrorMessage(err), - } - } -} - -interface ModifyInitialRfqInput extends UpdateInitialRfqSchema { - id: number -} - -export async function modifyInitialRfq(input: ModifyInitialRfqInput) { - unstable_noStore() - try { - const { id, ...updateData } = input - - // validation - updateInitialRfqSchema.parse(updateData) - - await db.transaction(async (tx) => { - const existingRfq = await tx - .select() - .from(initialRfq) - .where(eq(initialRfq.id, id)) - .limit(1) - - if (existingRfq.length === 0) { - throw new Error("초기 RFQ를 찾을 수 없습니다.") - } - - await tx - .update(initialRfq) - .set({ - ...updateData, - // Convert empty strings to null for optional fields - incotermsCode: updateData.incotermsCode || null, - gtc: updateData.gtc || null, - gtcValidDate: updateData.gtcValidDate || null, - classification: updateData.classification || null, - sparepart: updateData.sparepart || null, - validDate: updateData.validDate || null, - updatedAt: new Date(), - }) - .where(eq(initialRfq.id, id)) - }) - - - return { - data: null, - error: null, - } - } catch (err) { - return { - data: null, - error: getErrorMessage(err), - } - } -} - - - - -// 이메일 발송용 데이터 타입 -interface EmailData { - rfqCode: string - projectName: string - projectCompany: string - projectFlag: string - projectSite: string - classification: string - incotermsCode: string - incotermsDescription: string - dueDate: string - validDate: string - sparepart: string - vendorName: string - picName: string - picEmail: string - warrantyPeriod: string - packageName: string - rfqRevision: number - emailType: string -} - -export async function sendBulkInitialRfqEmails(input: BulkEmailInput) { - unstable_noStore() - try { - - const session = await getServerSession(authOptions) - if (!session?.user?.id) { - throw new Error("인증이 필요합니다.") - } - - const { initialRfqIds, language } = bulkEmailSchema.parse(input) - - // 1. 선택된 초기 RFQ들의 상세 정보 조회 - const initialRfqDetails = await db - .select({ - // initialRfqDetailView 필드들을 명시적으로 선택 - rfqId: initialRfqDetailView.rfqId, - rfqCode: initialRfqDetailView.rfqCode, - rfqStatus: initialRfqDetailView.rfqStatus, - initialRfqId: initialRfqDetailView.initialRfqId, - initialRfqStatus: initialRfqDetailView.initialRfqStatus, - vendorId: initialRfqDetailView.vendorId, - vendorCode: initialRfqDetailView.vendorCode, - vendorName: initialRfqDetailView.vendorName, - vendorCategory: initialRfqDetailView.vendorCategory, - vendorCountry: initialRfqDetailView.vendorCountry, - vendorBusinessSize: initialRfqDetailView.vendorBusinessSize, - dueDate: initialRfqDetailView.dueDate, - validDate: initialRfqDetailView.validDate, - incotermsCode: initialRfqDetailView.incotermsCode, - incotermsDescription: initialRfqDetailView.incotermsDescription, - shortList: initialRfqDetailView.shortList, - returnYn: initialRfqDetailView.returnYn, - cpRequestYn: initialRfqDetailView.cpRequestYn, - prjectGtcYn: initialRfqDetailView.prjectGtcYn, - returnRevision: initialRfqDetailView.returnRevision, - rfqRevision: initialRfqDetailView.rfqRevision, - gtc: initialRfqDetailView.gtc, - gtcValidDate: initialRfqDetailView.gtcValidDate, - classification: initialRfqDetailView.classification, - sparepart: initialRfqDetailView.sparepart, - createdAt: initialRfqDetailView.createdAt, - updatedAt: initialRfqDetailView.updatedAt, - // bRfqs에서 추가로 필요한 필드들 - picName: bRfqs.picName, - picCode: bRfqs.picCode, - packageName: bRfqs.packageName, - packageNo: bRfqs.packageNo, - projectCompany: bRfqs.projectCompany, - projectFlag: bRfqs.projectFlag, - projectSite: bRfqs.projectSite, - }) - .from(initialRfqDetailView) - .leftJoin(bRfqs, eq(initialRfqDetailView.rfqId, bRfqs.id)) - .where(inArray(initialRfqDetailView.initialRfqId, initialRfqIds)) - - if (initialRfqDetails.length === 0) { - return { - success: false, - message: "선택된 초기 RFQ를 찾을 수 없습니다.", - } - } - - // 2. 각 RFQ에 대한 첨부파일 조회 - const rfqIds = [...new Set(initialRfqDetails.map(rfq => rfq.rfqId))].filter((id): id is number => id !== null) - const attachments = await db - .select() - .from(bRfqsAttachments) - .where(inArray(bRfqsAttachments.rfqId, rfqIds)) - - // 3. 벤더 이메일 정보 조회 (모든 이메일 주소 포함) - const vendorIds = [...new Set(initialRfqDetails.map(rfq => rfq.vendorId))].filter((id): id is number => id !== null) - const vendorsWithAllEmails = await db - .select({ - id: vendors.id, - vendorName: vendors.vendorName, - email: vendors.email, - representativeEmail: vendors.representativeEmail, - // 연락처 이메일들을 JSON 배열로 집계 - contactEmails: sql<string[]>` - COALESCE( - (SELECT json_agg(contact_email) - FROM vendor_contacts - WHERE vendor_id = ${vendors.id} - AND contact_email IS NOT NULL - AND contact_email != '' - ), - '[]'::json - ) - `.as("contact_emails") - }) - .from(vendors) - .where(inArray(vendors.id, vendorIds)) - - // 각 벤더의 모든 유효한 이메일 주소를 정리하는 함수 - 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 => { - if (contactEmail && !emails.includes(contactEmail)) { - emails.push(contactEmail) - } - }) - } - - return emails.filter(email => email && email.trim() !== '') - } - - const results = [] - const errors = [] - - // 4. 각 초기 RFQ에 대해 처리 - for (const rfqDetail of initialRfqDetails) { - try { - // vendorId null 체크 - if (!rfqDetail.vendorId) { - errors.push(`벤더 ID가 없습니다: RFQ ID ${rfqDetail.initialRfqId}`) - continue - } - - // 해당 RFQ의 첨부파일들 - const rfqAttachments = attachments.filter(att => att.rfqId === rfqDetail.rfqId) - - // 벤더 정보 - const vendor = vendorsWithAllEmails.find(v => v.id === rfqDetail.vendorId) - if (!vendor) { - errors.push(`벤더 정보를 찾을 수 없습니다: RFQ ID ${rfqDetail.initialRfqId}`) - continue - } - - // 해당 벤더의 모든 이메일 주소 수집 - const vendorEmails = getAllVendorEmails(vendor) - - if (vendorEmails.length === 0) { - errors.push(`벤더 이메일 주소가 없습니다: ${vendor.vendorName}`) - continue - } - - // 5. 기존 vendorAttachmentResponses 조회하여 리비전 상태 확인 - const currentRfqRevision = rfqDetail.rfqRevision || 0 - let emailType: "NEW" | "RESEND" | "REVISION" = "NEW" - let revisionToUse = currentRfqRevision - - // 첫 번째 첨부파일을 기준으로 기존 응답 조회 (리비전 상태 확인용) - if (rfqAttachments.length > 0 && rfqDetail.initialRfqId) { - const existingResponses = await db - .select() - .from(vendorAttachmentResponses) - .where( - and( - eq(vendorAttachmentResponses.vendorId, rfqDetail.vendorId), - eq(vendorAttachmentResponses.rfqType, "INITIAL"), - eq(vendorAttachmentResponses.rfqRecordId, rfqDetail.initialRfqId) - ) - ) - - if (existingResponses.length > 0) { - // 기존 응답이 있음 - const existingRevision = parseInt(existingResponses[0].currentRevision?.replace("Rev.", "") || "0") - - if (currentRfqRevision > existingRevision) { - // RFQ 리비전이 올라감 → 리비전 업데이트 - emailType = "REVISION" - revisionToUse = currentRfqRevision - } else { - // 동일하거나 낮음 → 재전송 - emailType = "RESEND" - revisionToUse = existingRevision - } - } else { - // 기존 응답이 없음 → 신규 전송 - emailType = "NEW" - revisionToUse = currentRfqRevision - } - } - - // 6. vendorAttachmentResponses 레코드 생성/업데이트 - for (const attachment of rfqAttachments) { - const existingResponse = await db - .select() - .from(vendorAttachmentResponses) - .where( - and( - eq(vendorAttachmentResponses.attachmentId, attachment.id), - eq(vendorAttachmentResponses.vendorId, rfqDetail.vendorId), - eq(vendorAttachmentResponses.rfqType, "INITIAL") - ) - ) - .limit(1) - - if (existingResponse.length === 0) { - // 새 응답 레코드 생성 - await db.insert(vendorAttachmentResponses).values({ - attachmentId: attachment.id, - vendorId: rfqDetail.vendorId, - rfqType: "INITIAL", - rfqRecordId: rfqDetail.initialRfqId, - responseStatus: "NOT_RESPONDED", - currentRevision: `Rev.${revisionToUse}`, - requestedAt: new Date(), - }) - } else { - // 기존 레코드 업데이트 - await db - .update(vendorAttachmentResponses) - .set({ - currentRevision: `Rev.${revisionToUse}`, - requestedAt: new Date(), - // 리비전 업데이트인 경우 응답 상태 초기화 - responseStatus: emailType === "REVISION" ? "NOT_RESPONDED" : existingResponse[0].responseStatus, - }) - .where(eq(vendorAttachmentResponses.id, existingResponse[0].id)) - } - - } - - const formatDateSafely = (date: Date | string | null | undefined): string => { - if (!date) return "" - try { - // Date 객체로 변환하고 포맷팅 - const dateObj = new Date(date) - // 유효한 날짜인지 확인 - if (isNaN(dateObj.getTime())) return "" - - return dateObj.toLocaleDateString('en-US', { - year: 'numeric', - month: '2-digit', - day: '2-digit' - }) - } catch (error) { - console.error("Date formatting error:", error) - return "" - } - } - - // 7. 이메일 발송 - const emailData: EmailData = { - name: vendor.vendorName, - rfqCode: rfqDetail.rfqCode || "", - projectName: rfqDetail.rfqCode || "", // 실제 프로젝트명이 있다면 사용 - projectCompany: rfqDetail.projectCompany || "", - projectFlag: rfqDetail.projectFlag || "", - projectSite: rfqDetail.projectSite || "", - classification: rfqDetail.classification || "ABS", - incotermsCode: rfqDetail.incotermsCode || "FOB", - incotermsDescription: rfqDetail.incotermsDescription || "FOB Finland Port", - dueDate: rfqDetail.dueDate ? formatDateSafely(rfqDetail.dueDate) : "", - 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", - picEmail: session.user.email || "procurement@samsung.com", - warrantyPeriod: "Refer to commercial package attached", - packageName: rfqDetail.packageName || "", - rfqRevision: revisionToUse, // 리비전 정보 추가 - emailType: emailType, // 이메일 타입 추가 - } - - // 이메일 제목 생성 (타입에 따라 다르게) - 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}` - break - case "RESEND": - emailSubject = `[SHI RFQ - RESEND] ${rfqDetail.rfqCode}${revisionText} Invitation to Bidder for ${emailData.packageName} * ${vendor.vendorName} * RFQ No. ${rfqDetail.rfqCode}` - break - case "REVISION": - emailSubject = `[SHI RFQ - REVISED] ${rfqDetail.rfqCode}${revisionText} Invitation to Bidder for ${emailData.packageName} * ${vendor.vendorName} * RFQ No. ${rfqDetail.rfqCode}` - break - } - - // nodemailer로 모든 이메일 주소에 한번에 발송 - await sendEmail({ - to: vendorEmails.join(", "), // 콤마+공백으로 구분 - subject: emailSubject, - template: "initial-rfq-invitation", // hbs 템플릿 파일명 - context: { - ...emailData, - language, - } - }) - - // 8. 초기 RFQ 상태 업데이트 (리비전은 변경하지 않음 - 이미 DB에 저장된 값 사용) - if (rfqDetail.initialRfqId && rfqDetail.rfqId) { - // Promise.all로 두 테이블 동시 업데이트 - await Promise.all([ - // initialRfq 테이블 업데이트 - db - .update(initialRfq) - .set({ - initialRfqStatus: "Init. RFQ Sent", - updatedAt: new Date(), - }) - .where(eq(initialRfq.id, rfqDetail.initialRfqId)), - - // bRfqs 테이블 status도 함께 업데이트 - db - .update(bRfqs) - .set({ - status: "Init. RFQ Sent", - // updatedBy: session.user.id, - updatedAt: new Date(), - }) - .where(eq(bRfqs.id, rfqDetail.rfqId)) - ]); - } - - results.push({ - initialRfqId: rfqDetail.initialRfqId, - vendorName: vendor.vendorName, - vendorEmails: vendorEmails, // 발송된 모든 이메일 주소 기록 - emailCount: vendorEmails.length, - emailType: emailType, - rfqRevision: revisionToUse, - success: true, - }) - - } catch (error) { - console.error(`Error processing RFQ ${rfqDetail.initialRfqId}:`, error) - errors.push(`RFQ ${rfqDetail.initialRfqId} 처리 중 오류: ${getErrorMessage(error)}`) - } - } - - - - return { - success: true, - message: `${results.length}개의 RFQ 이메일이 발송되었습니다.`, - results, - errors: errors.length > 0 ? errors : undefined, - } - - } catch (err) { - console.error("Bulk email error:", err) - return { - success: false, - message: getErrorMessage(err), - } - } -} - -// 개별 RFQ 이메일 재발송 -export async function resendInitialRfqEmail(initialRfqId: number) { - unstable_noStore() - try { - const result = await sendBulkInitialRfqEmails({ - initialRfqIds: [initialRfqId], - language: "en", - }) - - return result - } catch (err) { - return { - success: false, - message: getErrorMessage(err), - } - } -} - -export type VendorResponseDetail = VendorAttachmentResponse & { - attachment: { - id: number; - attachmentType: string; - serialNo: string; - description: string | null; - currentRevision: string; - }; - vendor: { - id: number; - vendorCode: string; - vendorName: string; - country: string | null; - businessSize: string | null; - }; - rfq: { - id: number; - rfqCode: string | null; - description: string | null; - status: string; - dueDate: Date; - }; -}; - -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; - - // 기본 조건 - let whereConditions = []; - - // 벤더 ID 조건 - if (vendorId) { - whereConditions.push(eq(vendorAttachmentResponses.vendorId, Number(vendorId))); - } - - // 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)) - ) - ); - } - - 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); - - // 벤더 정보와 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, - } - }); - - // 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, - } - } - } - }) - - ]); - - // 데이터 조합 및 변환 - 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; - } - - // 응답률 계산 - 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)), - ), - with: { - attachment: { - with: { - 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: { - project: { - columns: { - id: 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)] - }); - - 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 }; - } -} - -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, - }; - - // 조건 설정 - let whereConditions = []; - - // 벤더 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 타입 조건 - if (rfqType) { - whereConditions.push(eq(vendorAttachmentResponses.rfqType, rfqType)); - } - - 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); - - // 결과 처리 - 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>; - } -} - -/** - * RFQ별 벤더 응답 요약 조회 - */ -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))) - ]; - - if (rfqType) { - whereConditions.push(eq(vendorAttachmentResponses.rfqType, rfqType)); - } - - const whereCondition = and(...whereConditions); - - // 벤더 수 및 응답 통계 조회 - 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), - - // 상태별 개수 - 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> - }; - } -} - -/** - * 벤더별 응답 진행률 조회 - */ -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; - - 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, - }; - } -} - - -export async function getRfqAttachmentResponsesWithRevisions(vendorId: string, rfqRecordId: string) { - try { - // 1. 벤더 응답 상세 정보 조회 (뷰 사용) - const responses = await db - .select() - .from(vendorResponseDetailView) - .where( - and( - eq(vendorResponseDetailView.vendorId, Number(vendorId)), - eq(vendorResponseDetailView.rfqRecordId, Number(rfqRecordId)) - ) - ) - .orderBy(asc(vendorResponseDetailView.attachmentId)); - - // 2. RFQ 진행 현황 요약 조회 - const progressSummaryResult = await db - .select() - .from(rfqProgressSummaryView) - .where(eq(rfqProgressSummaryView.rfqId, responses[0]?.rfqId || 0)) - .limit(1); - - const progressSummary = progressSummaryResult[0] || null; - - // 3. 각 응답의 첨부파일 리비전 히스토리 조회 - const attachmentHistories = await Promise.all( - responses.map(async (response) => { - const history = await db - .select() - .from(attachmentRevisionHistoryView) - .where(eq(attachmentRevisionHistoryView.attachmentId, response.attachmentId)) - .orderBy(desc(attachmentRevisionHistoryView.clientRevisionCreatedAt)); - - return { - attachmentId: response.attachmentId, - revisions: history - }; - }) - ); - - // 4. 벤더 응답 파일들 조회 (향상된 정보 포함) - const responseFiles = await Promise.all( - responses.map(async (response) => { - const files = await db - .select() - .from(vendorResponseAttachmentsEnhanced) - .where(eq(vendorResponseAttachmentsEnhanced.vendorResponseId, response.responseId)) - .orderBy(desc(vendorResponseAttachmentsEnhanced.uploadedAt)); - - return { - responseId: response.responseId, - files: files - }; - }) - ); - - // 5. 데이터 변환 및 통합 - 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, - // 첨부파일 정보에 리비전 히스토리 추가 - attachment: { - id: response.attachmentId, - attachmentType: response.attachmentType, - serialNo: response.serialNo, - description: response.attachmentDescription, - currentRevision: response.currentRevision, - // 모든 리비전 정보 - revisions: attachmentHistory?.revisions?.map(rev => ({ - id: rev.clientRevisionId, - revisionNo: rev.clientRevisionNo, - fileName: rev.clientFileName, - originalFileName: rev.clientFileName, - filePath: rev.clientFilePath, // 파일 경로 추가 - fileSize: rev.clientFileSize, - revisionComment: rev.clientRevisionComment, - createdAt: rev.clientRevisionCreatedAt?.toISOString() || new Date().toISOString(), - isLatest: rev.isLatestClientRevision - })) || [] - }, - // 벤더 응답 파일들 - responseAttachments: responseFileData?.files?.map(file => ({ - id: file.responseAttachmentId, - fileName: file.fileName, - originalFileName: file.originalFileName, - filePath: file.filePath, - fileSize: file.fileSize, - description: file.description, - uploadedAt: file.uploadedAt?.toISOString() || new Date().toISOString(), - isLatestResponseFile: file.isLatestResponseFile, - fileSequence: file.fileSequence - })) || [], - // 리비전 분석 정보 - isVersionMatched: response.isVersionMatched, - versionLag: response.versionLag, - needsUpdate: response.needsUpdate, - hasMultipleRevisions: response.hasMultipleRevisions, - - // 새로 추가된 필드들 - revisionRequestComment: response.revisionRequestComment, - revisionRequestedAt: response.revisionRequestedAt?.toISOString() || null, - }; - }); - - // RFQ 기본 정보 (첫 번째 응답에서 추출) - const rfqInfo = responses[0] ? { - id: responses[0].rfqId, - rfqCode: responses[0].rfqCode, - // 추가 정보는 기존 방식대로 별도 조회 필요 - description: "", - dueDate: progressSummary?.dueDate || new Date(), - status: progressSummary?.rfqStatus || "DRAFT", - // ... 기타 필요한 정보들 - } : null; - - // 벤더 정보 - const vendorInfo = responses[0] ? { - id: responses[0].vendorId, - vendorCode: responses[0].vendorCode, - vendorName: responses[0].vendorName, - country: responses[0].vendorCountry, - } : null; - - // 통계 정보 계산 - const calculateStats = (responses: typeof enhancedResponses) => { - const total = responses.length; - const responded = responses.filter(r => r.responseStatus === "RESPONDED").length; - const pending = responses.filter(r => r.responseStatus === "NOT_RESPONDED").length; - const revisionRequested = responses.filter(r => r.responseStatus === "REVISION_REQUESTED").length; - const waived = responses.filter(r => r.responseStatus === "WAIVED").length; - const versionMismatch = responses.filter(r => r.effectiveStatus === "VERSION_MISMATCH").length; - const upToDate = responses.filter(r => r.effectiveStatus === "UP_TO_DATE").length; - - return { - total, - responded, - pending, - revisionRequested, - waived, - versionMismatch, - upToDate, - responseRate: total > 0 ? Math.round((responded / total) * 100) : 0, - completionRate: total > 0 ? Math.round(((responded + waived) / total) * 100) : 0, - versionMatchRate: responded > 0 ? Math.round((upToDate / responded) * 100) : 100 - }; - }; - - const statistics = calculateStats(enhancedResponses); - - return { - data: enhancedResponses, - rfqInfo, - vendorInfo, - statistics, - progressSummary: progressSummary ? { - totalAttachments: progressSummary.totalAttachments, - attachmentsWithMultipleRevisions: progressSummary.attachmentsWithMultipleRevisions, - totalClientRevisions: progressSummary.totalClientRevisions, - totalResponseFiles: progressSummary.totalResponseFiles, - daysToDeadline: progressSummary.daysToDeadline - } : null - }; - - } catch (err) { - console.error("getRfqAttachmentResponsesWithRevisions 에러:", err); - return { - data: [], - rfqInfo: null, - vendorInfo: null, - statistics: { - total: 0, - responded: 0, - pending: 0, - revisionRequested: 0, - waived: 0, - versionMismatch: 0, - upToDate: 0, - responseRate: 0, - completionRate: 0, - versionMatchRate: 100 - }, - progressSummary: null - }; - } -} - -// 첨부파일 리비전 히스토리 조회 -export async function getAttachmentRevisionHistory(attachmentId: number) { - - 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 []; - } -} - -// 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; - } -} - -// 벤더 응답 파일 상세 조회 (향상된 정보 포함) -export async function getVendorResponseFiles(vendorResponseId: number) { - 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 []; - } -} - - -// 타입 정의 확장 -export type EnhancedVendorResponse = { - // 기본 응답 정보 - responseId: number; - rfqId: number; - 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; - attachmentType: string; - serialNo: string; - description?: string; - currentRevision: string; - revisions: Array<{ - id: number; - revisionNo: string; - fileName: string; - originalFileName: string; - filePath?: string; - fileSize?: number; - revisionComment?: string; - createdAt: string; - isLatest: boolean; - }>; - }; - - // 벤더 응답 파일들 - responseAttachments: Array<{ - id: number; - fileName: string; - originalFileName: string; - filePath: string; - fileSize?: number; - description?: string; - uploadedAt: string; - isLatestResponseFile: boolean; - fileSequence: number; - }>; -}; - - -export async function requestRevision( - responseId: number, - revisionReason: string -): Promise<RequestRevisionResult> { - try { - // 입력값 검증 - - const session = await getServerSession(authOptions) - if (!session?.user?.id) { - throw new Error("인증이 필요합니다.") - } - const validatedData = requestRevisionSchema.parse({ - responseId, - revisionReason, - }); - - // 현재 응답 정보 조회 - const existingResponse = await db - .select() - .from(vendorAttachmentResponses) - .where(eq(vendorAttachmentResponses.id, validatedData.responseId)) - .limit(1); - - if (existingResponse.length === 0) { - return { - success: false, - message: "해당 응답을 찾을 수 없습니다", - error: "NOT_FOUND", - }; - } - - const response = existingResponse[0]; - - // 응답 상태 확인 (이미 응답되었거나 포기된 상태에서만 수정 요청 가능) - if (response.responseStatus !== "RESPONDED") { - return { - success: false, - message: "응답된 상태의 항목에서만 수정을 요청할 수 있습니다", - error: "INVALID_STATUS", - }; - } - - // 응답 상태를 REVISION_REQUESTED로 업데이트 - const updateResult = await db - .update(vendorAttachmentResponses) - .set({ - responseStatus: "REVISION_REQUESTED", - revisionRequestComment: validatedData.revisionReason, // 새로운 필드에 저장 - revisionRequestedAt: new Date(), // 수정 요청 시간 저장 - updatedAt: new Date(), - updatedBy: Number(session.user.id), - }) - .where(eq(vendorAttachmentResponses.id, validatedData.responseId)) - .returning(); - - if (updateResult.length === 0) { - return { - success: false, - message: "수정 요청 업데이트에 실패했습니다", - error: "UPDATE_FAILED", - }; - } - - return { - success: true, - message: "수정 요청이 성공적으로 전송되었습니다", - }; - - } catch (error) { - console.error("Request revision server action error:", error); - 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 |