From 14f61e24947fb92dd71ec0a7196a6e815f8e66da Mon Sep 17 00:00:00 2001 From: dujinkim Date: Mon, 21 Jul 2025 07:54:26 +0000 Subject: (최겸)기술영업 RFQ 담당자 초대, 요구사항 반영 MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit --- lib/techsales-rfq/service.ts | 7112 ++++++++++++++++++++++-------------------- 1 file changed, 3697 insertions(+), 3415 deletions(-) (limited to 'lib/techsales-rfq/service.ts') diff --git a/lib/techsales-rfq/service.ts b/lib/techsales-rfq/service.ts index c991aa42..fd50b7a6 100644 --- a/lib/techsales-rfq/service.ts +++ b/lib/techsales-rfq/service.ts @@ -1,3416 +1,3698 @@ -'use server' - -import { unstable_noStore, revalidateTag, revalidatePath } from "next/cache"; -import db from "@/db/db"; -import { - techSalesRfqs, - techSalesVendorQuotations, - techSalesVendorQuotationRevisions, - techSalesAttachments, - techSalesVendorQuotationAttachments, - users, - techSalesRfqComments, - techSalesRfqItems, - biddingProjects -} from "@/db/schema"; -import { and, desc, eq, ilike, or, sql, inArray, count, asc } from "drizzle-orm"; -import { unstable_cache } from "@/lib/unstable-cache"; -import { filterColumns } from "@/lib/filter-columns"; -import { getErrorMessage } from "@/lib/handle-error"; -import type { Filter } from "@/types/table"; -import { - selectTechSalesRfqsWithJoin, - countTechSalesRfqsWithJoin, - selectTechSalesVendorQuotationsWithJoin, - countTechSalesVendorQuotationsWithJoin, - selectTechSalesDashboardWithJoin, - selectSingleTechSalesVendorQuotationWithJoin -} from "./repository"; -import { GetTechSalesRfqsSchema } from "./validations"; -import { getServerSession } from "next-auth/next"; -import { authOptions } from "@/app/api/auth/[...nextauth]/route"; -import { sendEmail } from "../mail/sendEmail"; -import { formatDate } from "../utils"; -import { techVendors, techVendorPossibleItems } from "@/db/schema/techVendors"; -import { decryptWithServerAction } from "@/components/drm/drmUtils"; -import { deleteFile, saveDRMFile } from "../file-stroage"; - -// 정렬 타입 정의 -// 의도적으로 any 사용 - drizzle ORM의 orderBy 타입이 복잡함 -// eslint-disable-next-line @typescript-eslint/no-explicit-any -type OrderByType = any; - -export type Project = { - id: number; - projectCode: string; - projectName: string; - pjtType: "SHIP" | "TOP" | "HULL"; -} - -/** - * 연도별 순차 RFQ 코드 생성 함수 (다중 생성 지원) - * 형식: RFQ-YYYY-001, RFQ-YYYY-002, ... - */ -// eslint-disable-next-line @typescript-eslint/no-explicit-any -async function generateRfqCodes(tx: any, count: number, year?: number): Promise { - const currentYear = year || new Date().getFullYear(); - const yearPrefix = `RFQ-${currentYear}-`; - - // 해당 연도의 가장 최근 RFQ 코드 조회 - const latestRfq = await tx - .select({ rfqCode: techSalesRfqs.rfqCode }) - .from(techSalesRfqs) - .where(ilike(techSalesRfqs.rfqCode, `${yearPrefix}%`)) - .orderBy(desc(techSalesRfqs.rfqCode)) - .limit(1); - - let nextNumber = 1; - - if (latestRfq.length > 0) { - // 기존 코드에서 번호 추출 (RFQ-2024-001 -> 001) - const lastCode = latestRfq[0].rfqCode; - const numberPart = lastCode.split('-').pop(); - if (numberPart) { - const lastNumber = parseInt(numberPart, 10); - if (!isNaN(lastNumber)) { - nextNumber = lastNumber + 1; - } - } - } - - // 요청된 개수만큼 순차적으로 코드 생성 - const codes: string[] = []; - for (let i = 0; i < count; i++) { - const paddedNumber = (nextNumber + i).toString().padStart(3, '0'); - codes.push(`${yearPrefix}${paddedNumber}`); - } - - return codes; -} - - -/** - * 직접 조인을 사용하여 RFQ 데이터 조회하는 함수 - * 페이지네이션, 필터링, 정렬 등 지원 - */ -export async function getTechSalesRfqsWithJoin(input: GetTechSalesRfqsSchema & { rfqType?: "SHIP" | "TOP" | "HULL" }) { - return unstable_cache( - async () => { - try { - const offset = (input.page - 1) * input.perPage; - - // 기본 필터 처리 - RFQFilterBox에서 오는 필터 - const basicFilters = input.basicFilters || []; - const basicJoinOperator = input.basicJoinOperator || "and"; - - // 고급 필터 처리 - 테이블의 DataTableFilterList에서 오는 필터 - const advancedFilters = input.filters || []; - const advancedJoinOperator = input.joinOperator || "and"; - - // 기본 필터 조건 생성 - let basicWhere; - if (basicFilters.length > 0) { - basicWhere = filterColumns({ - table: techSalesRfqs, - filters: basicFilters, - joinOperator: basicJoinOperator, - }); - } - - // 고급 필터 조건 생성 - let advancedWhere; - if (advancedFilters.length > 0) { - advancedWhere = filterColumns({ - table: techSalesRfqs, - filters: advancedFilters, - joinOperator: advancedJoinOperator, - }); - } - - // 전역 검색 조건 - let globalWhere; - if (input.search) { - const s = `%${input.search}%`; - globalWhere = or( - ilike(techSalesRfqs.rfqCode, s), - ilike(techSalesRfqs.materialCode, s), - ilike(techSalesRfqs.description, s), - ilike(techSalesRfqs.remark, s) - ); - } - - // 모든 조건 결합 - const whereConditions = []; - if (basicWhere) whereConditions.push(basicWhere); - if (advancedWhere) whereConditions.push(advancedWhere); - if (globalWhere) whereConditions.push(globalWhere); - - // 조건이 있을 때만 and() 사용 - const finalWhere = whereConditions.length > 0 - ? and(...whereConditions) - : undefined; - - // 정렬 기준 설정 - let orderBy: OrderByType[] = [desc(techSalesRfqs.createdAt)]; // 기본 정렬 - - if (input.sort?.length) { - // 안전하게 접근하여 정렬 기준 설정 - orderBy = input.sort.map(item => { - // TypeScript 에러 방지를 위한 타입 단언 - const sortField = item.id as string; - - switch (sortField) { - case 'id': - return item.desc ? desc(techSalesRfqs.id) : techSalesRfqs.id; - case 'rfqCode': - return item.desc ? desc(techSalesRfqs.rfqCode) : techSalesRfqs.rfqCode; - case 'materialCode': - return item.desc ? desc(techSalesRfqs.materialCode) : techSalesRfqs.materialCode; - case 'description': - return item.desc ? desc(techSalesRfqs.description) : techSalesRfqs.description; - case 'status': - return item.desc ? desc(techSalesRfqs.status) : techSalesRfqs.status; - case 'dueDate': - return item.desc ? desc(techSalesRfqs.dueDate) : techSalesRfqs.dueDate; - case 'rfqSendDate': - return item.desc ? desc(techSalesRfqs.rfqSendDate) : techSalesRfqs.rfqSendDate; - case 'remark': - return item.desc ? desc(techSalesRfqs.remark) : techSalesRfqs.remark; - case 'createdAt': - return item.desc ? desc(techSalesRfqs.createdAt) : techSalesRfqs.createdAt; - case 'updatedAt': - return item.desc ? desc(techSalesRfqs.updatedAt) : techSalesRfqs.updatedAt; - default: - return item.desc ? desc(techSalesRfqs.createdAt) : techSalesRfqs.createdAt; - } - }); - } - - // Repository 함수 호출 - rfqType 매개변수 추가 - return await db.transaction(async (tx) => { - const [data, total] = await Promise.all([ - selectTechSalesRfqsWithJoin(tx, { - where: finalWhere, - orderBy, - offset, - limit: input.perPage, - rfqType: input.rfqType, - }), - countTechSalesRfqsWithJoin(tx, finalWhere, input.rfqType), - ]); - - const pageCount = Math.ceil(Number(total) / input.perPage); - return { data, pageCount, total: Number(total) }; - }); - } catch (err) { - console.error("Error fetching RFQs with join:", err); - return { data: [], pageCount: 0, total: 0 }; - } - }, - [JSON.stringify(input)], - { - revalidate: 60, - tags: ["techSalesRfqs"], - } - )(); -} - -/** - * 직접 조인을 사용하여 벤더 견적서 조회하는 함수 - */ -export async function getTechSalesVendorQuotationsWithJoin(input: { - rfqId?: number; - vendorId?: number; - search?: string; - filters?: Filter[]; - sort?: { id: string; desc: boolean }[]; - page: number; - perPage: number; - rfqType?: "SHIP" | "TOP" | "HULL"; // rfqType 매개변수 추가 -}) { - return unstable_cache( - async () => { - try { - const offset = (input.page - 1) * input.perPage; - - // 기본 필터 조건들 - const whereConditions = []; - - // RFQ ID 필터 - if (input.rfqId) { - whereConditions.push(eq(techSalesVendorQuotations.rfqId, input.rfqId)); - } - - // 벤더 ID 필터 - if (input.vendorId) { - whereConditions.push(eq(techSalesVendorQuotations.vendorId, input.vendorId)); - } - - // 검색 조건 - if (input.search) { - const s = `%${input.search}%`; - const searchCondition = or( - ilike(techSalesVendorQuotations.currency, s), - ilike(techSalesVendorQuotations.status, s) - ); - if (searchCondition) { - whereConditions.push(searchCondition); - } - } - - // 고급 필터 처리 - if (input.filters && input.filters.length > 0) { - const filterWhere = filterColumns({ - table: techSalesVendorQuotations, - filters: input.filters as Filter[], - joinOperator: "and", - }); - if (filterWhere) { - whereConditions.push(filterWhere); - } - } - - // 최종 WHERE 조건 - const finalWhere = whereConditions.length > 0 - ? and(...whereConditions) - : undefined; - - // 정렬 기준 설정 - let orderBy: OrderByType[] = [desc(techSalesVendorQuotations.createdAt)]; - - if (input.sort?.length) { - orderBy = input.sort.map(item => { - switch (item.id) { - case 'id': - return item.desc ? desc(techSalesVendorQuotations.id) : techSalesVendorQuotations.id; - case 'status': - return item.desc ? desc(techSalesVendorQuotations.status) : techSalesVendorQuotations.status; - case 'currency': - return item.desc ? desc(techSalesVendorQuotations.currency) : techSalesVendorQuotations.currency; - case 'totalPrice': - return item.desc ? desc(techSalesVendorQuotations.totalPrice) : techSalesVendorQuotations.totalPrice; - case 'createdAt': - return item.desc ? desc(techSalesVendorQuotations.createdAt) : techSalesVendorQuotations.createdAt; - case 'updatedAt': - return item.desc ? desc(techSalesVendorQuotations.updatedAt) : techSalesVendorQuotations.updatedAt; - default: - return item.desc ? desc(techSalesVendorQuotations.createdAt) : techSalesVendorQuotations.createdAt; - } - }); - } - - // 트랜잭션 내부에서 Repository 호출 - const { data, total } = await db.transaction(async (tx) => { - const data = await selectTechSalesVendorQuotationsWithJoin(tx, { - where: finalWhere, - orderBy, - offset, - limit: input.perPage, - }); - - // 각 견적서의 첨부파일 정보 조회 - const dataWithAttachments = await Promise.all( - data.map(async (quotation) => { - const attachments = await db.query.techSalesVendorQuotationAttachments.findMany({ - where: eq(techSalesVendorQuotationAttachments.quotationId, quotation.id), - orderBy: [desc(techSalesVendorQuotationAttachments.createdAt)], - }); - - return { - ...quotation, - quotationAttachments: attachments.map(att => ({ - id: att.id, - fileName: att.fileName, - fileSize: att.fileSize, - filePath: att.filePath, - description: att.description, - })) - }; - }) - ); - - const total = await countTechSalesVendorQuotationsWithJoin(tx, finalWhere); - return { data: dataWithAttachments, total }; - }); - - const pageCount = Math.ceil(total / input.perPage); - - return { data, pageCount, total }; - } catch (err) { - console.error("Error fetching vendor quotations with join:", err); - return { data: [], pageCount: 0, total: 0 }; - } - }, - [JSON.stringify(input)], - { - revalidate: 60, - tags: [ - "techSalesVendorQuotations", - ...(input.rfqId ? [`techSalesRfq-${input.rfqId}`] : []) - ], - } - )(); -} - -/** - * 직접 조인을 사용하여 RFQ 대시보드 데이터 조회하는 함수 - */ -export async function getTechSalesDashboardWithJoin(input: { - search?: string; - filters?: Filter[]; - sort?: { id: string; desc: boolean }[]; - page: number; - perPage: number; - rfqType?: "SHIP" | "TOP" | "HULL"; // rfqType 매개변수 추가 -}) { - unstable_noStore(); // 대시보드는 항상 최신 데이터를 보여주기 위해 캐시하지 않음 - - try { - const offset = (input.page - 1) * input.perPage; - - // Advanced filtering - const advancedWhere = input.filters ? filterColumns({ - table: techSalesRfqs, - filters: input.filters as Filter[], - joinOperator: 'and', - }) : undefined; - - // Global search - let globalWhere; - if (input.search) { - const s = `%${input.search}%`; - globalWhere = or( - ilike(techSalesRfqs.rfqCode, s), - ilike(techSalesRfqs.materialCode, s), - ilike(techSalesRfqs.description, s) - ); - } - - const finalWhere = and( - advancedWhere, - globalWhere - ); - - // 정렬 기준 설정 - let orderBy: OrderByType[] = [desc(techSalesRfqs.updatedAt)]; // 기본 정렬 - - if (input.sort?.length) { - // 안전하게 접근하여 정렬 기준 설정 - orderBy = input.sort.map(item => { - switch (item.id) { - case 'id': - return item.desc ? desc(techSalesRfqs.id) : techSalesRfqs.id; - case 'rfqCode': - return item.desc ? desc(techSalesRfqs.rfqCode) : techSalesRfqs.rfqCode; - case 'status': - return item.desc ? desc(techSalesRfqs.status) : techSalesRfqs.status; - case 'dueDate': - return item.desc ? desc(techSalesRfqs.dueDate) : techSalesRfqs.dueDate; - case 'createdAt': - return item.desc ? desc(techSalesRfqs.createdAt) : techSalesRfqs.createdAt; - case 'updatedAt': - return item.desc ? desc(techSalesRfqs.updatedAt) : techSalesRfqs.updatedAt; - default: - return item.desc ? desc(techSalesRfqs.updatedAt) : techSalesRfqs.updatedAt; - } - }); - } - - // 트랜잭션 내부에서 Repository 호출 - const data = await db.transaction(async (tx) => { - return await selectTechSalesDashboardWithJoin(tx, { - where: finalWhere, - orderBy, - offset, - limit: input.perPage, - rfqType: input.rfqType, // rfqType 매개변수 추가 - }); - }); - - return { data, success: true }; - } catch (err) { - console.error("Error fetching dashboard data with join:", err); - return { data: [], success: false, error: getErrorMessage(err) }; - } -} - -/** - * 특정 RFQ의 벤더 목록 조회 - */ -export async function getTechSalesRfqVendors(rfqId: number) { - unstable_noStore(); - try { - // Repository 함수를 사용하여 벤더 견적 목록 조회 - const result = await getTechSalesVendorQuotationsWithJoin({ - rfqId, - page: 1, - perPage: 1000, // 충분히 큰 수로 설정하여 모든 벤더 조회 - }); - - return { data: result.data, error: null }; - } catch (err) { - console.error("Error fetching RFQ vendors:", err); - return { data: [], error: getErrorMessage(err) }; - } -} - -/** - * 기술영업 RFQ 발송 (선택된 벤더들에게) - */ -export async function sendTechSalesRfqToVendors(input: { - rfqId: number; - vendorIds: number[]; -}) { - unstable_noStore(); - try { - // 인증 확인 - const session = await getServerSession(authOptions); - - if (!session?.user) { - return { - success: false, - message: "인증이 필요합니다", - }; - } - - // RFQ 정보 조회 - const rfq = await db.query.techSalesRfqs.findFirst({ - where: eq(techSalesRfqs.id, input.rfqId), - columns: { - id: true, - rfqCode: true, - status: true, - dueDate: true, - rfqSendDate: true, - remark: true, - materialCode: true, - description: true, - rfqType: true, - }, - with: { - biddingProject: true, - createdByUser: { - columns: { - id: true, - name: true, - email: true, - } - } - } - }); - - if (!rfq) { - return { - success: false, - message: "RFQ를 찾을 수 없습니다", - }; - } - - // 발송 가능한 상태인지 확인 - if (rfq.status !== "RFQ Vendor Assignned" && rfq.status !== "RFQ Sent") { - return { - success: false, - message: "벤더가 할당된 RFQ 또는 이미 전송된 RFQ만 다시 전송할 수 있습니다", - }; - } - - const isResend = rfq.status === "RFQ Sent"; - - // 현재 사용자 정보 조회 - const sender = await db.query.users.findFirst({ - where: eq(users.id, Number(session.user.id)), - columns: { - id: true, - email: true, - name: true, - } - }); - - if (!sender || !sender.email) { - return { - success: false, - message: "보내는 사람의 이메일 정보를 찾을 수 없습니다", - }; - } - - // 선택된 벤더들의 견적서 정보 조회 - const vendorQuotations = await db.query.techSalesVendorQuotations.findMany({ - where: and( - eq(techSalesVendorQuotations.rfqId, input.rfqId), - inArray(techSalesVendorQuotations.vendorId, input.vendorIds) - ), - columns: { - id: true, - vendorId: true, - status: true, - currency: true, - }, - with: { - vendor: { - columns: { - id: true, - vendorName: true, - vendorCode: true, - } - } - } - }); - - if (vendorQuotations.length === 0) { - return { - success: false, - message: "선택된 벤더가 이 RFQ에 할당되어 있지 않습니다", - }; - } - - // 트랜잭션 시작 - await db.transaction(async (tx) => { - // 1. RFQ 상태 업데이트 (최초 발송인 경우 rfqSendDate 설정) - const updateData: Partial = { - status: "RFQ Sent", - sentBy: Number(session.user.id), - updatedBy: Number(session.user.id), - updatedAt: new Date(), - }; - - // rfqSendDate가 null인 경우에만 최초 전송일 설정 - if (!rfq.rfqSendDate) { - updateData.rfqSendDate = new Date(); - } - - await tx.update(techSalesRfqs) - .set(updateData) - .where(eq(techSalesRfqs.id, input.rfqId)); - - // 2. 선택된 벤더들의 견적서 상태를 "Assigned"에서 "Draft"로 변경 - for (const quotation of vendorQuotations) { - if (quotation.status === "Assigned") { - await tx.update(techSalesVendorQuotations) - .set({ - status: "Draft", - updatedBy: Number(session.user.id), - updatedAt: new Date(), - }) - .where(eq(techSalesVendorQuotations.id, quotation.id)); - } - } - - // 2. 각 벤더에 대해 이메일 발송 처리 - for (const quotation of vendorQuotations) { - if (!quotation.vendorId || !quotation.vendor) continue; - - // 벤더에 속한 모든 사용자 조회 - const vendorUsers = await db.query.users.findMany({ - where: eq(users.companyId, quotation.vendor.id), - columns: { - id: true, - email: true, - name: true, - language: true - } - }); - - // 유효한 이메일 주소만 필터링 - const vendorEmailsString = vendorUsers - .filter(user => user.email) - .map(user => user.email) - .join(", "); - - if (vendorEmailsString) { - // 대표 언어 결정 (첫 번째 사용자의 언어 또는 기본값) - const language = vendorUsers[0]?.language || "ko"; - - // RFQ 아이템 목록 조회 - const rfqItemsResult = await getTechSalesRfqItems(rfq.id); - const rfqItems = rfqItemsResult.data || []; - - // 이메일 컨텍스트 구성 (시리즈 정보 제거, 프로젝트 정보 간소화) - const emailContext = { - language: language, - rfq: { - id: rfq.id, - code: rfq.rfqCode, - title: rfqItems.length > 0 ? rfqItems.map(item => item.itemList).join(', ') : '', - projectCode: rfq.biddingProject?.pspid || '', - projectName: rfq.biddingProject?.projNm || '', - description: rfq.remark || '', - dueDate: rfq.dueDate ? formatDate(rfq.dueDate, "KR") : 'N/A', - materialCode: rfq.materialCode || '', - type: rfq.rfqType || 'SHIP', - }, - items: rfqItems.map(item => ({ - itemCode: item.itemCode, - itemList: item.itemList, - workType: item.workType, - shipType: item.shipType, - subItemName: item.subItemName, - itemType: item.itemType, - })), - vendor: { - id: quotation.vendor.id, - code: quotation.vendor.vendorCode || '', - name: quotation.vendor.vendorName, - }, - sender: { - fullName: sender.name || '', - email: sender.email, - }, - project: { - // 기본 정보만 유지 - id: rfq.biddingProject?.pspid || '', - name: rfq.biddingProject?.projNm || '', - sector: rfq.biddingProject?.sector || '', - shipType: rfq.biddingProject?.ptypeNm || '', - shipCount: rfq.biddingProject?.projMsrm || 0, - ownerName: rfq.biddingProject?.kunnrNm || '', - className: rfq.biddingProject?.cls1Nm || '', - }, - details: { - currency: quotation.currency || 'USD', - }, - quotationCode: `${rfq.rfqCode}-${quotation.vendorId}`, - systemUrl: process.env.NEXT_PUBLIC_APP_URL || 'http://60.101.108.100/ko/partners', - isResend: isResend, - versionInfo: isResend ? '(재전송)' : '', - }; - - // 이메일 전송 - await sendEmail({ - to: vendorEmailsString, - subject: isResend - ? `[기술영업 RFQ 재전송] ${rfq.rfqCode} - ${rfqItems.length > 0 ? rfqItems.map(item => item.itemList).join(', ') : '견적 요청'} ${emailContext.versionInfo}` - : `[기술영업 RFQ] ${rfq.rfqCode} - ${rfqItems.length > 0 ? rfqItems.map(item => item.itemList).join(', ') : '견적 요청'}`, - template: 'tech-sales-rfq-invite-ko', // 기술영업용 템플릿 - context: emailContext, - cc: sender.email, // 발신자를 CC에 추가 - }); - } - } - }); - - // 캐시 무효화 - revalidateTag("techSalesRfqs"); - revalidateTag("techSalesVendorQuotations"); - revalidateTag(`techSalesRfq-${input.rfqId}`); - revalidatePath(getTechSalesRevalidationPath(rfq?.rfqType || "SHIP")); - - return { - success: true, - message: `${vendorQuotations.length}개 벤더에게 RFQ가 성공적으로 발송되었습니다`, - sentCount: vendorQuotations.length, - }; - } catch (err) { - console.error("기술영업 RFQ 발송 오류:", err); - return { - success: false, - message: "RFQ 발송 중 오류가 발생했습니다", - }; - } -} - -/** - * 벤더용 기술영업 RFQ 견적서 조회 (withJoin 사용) - */ -export async function getTechSalesVendorQuotation(quotationId: number) { - unstable_noStore(); - try { - const quotation = await db.transaction(async (tx) => { - return await selectSingleTechSalesVendorQuotationWithJoin(tx, quotationId); - }); - - if (!quotation) { - return { data: null, error: "견적서를 찾을 수 없습니다." }; - } - - // RFQ 아이템 정보도 함께 조회 - const itemsResult = await getTechSalesRfqItems(quotation.rfqId); - const items = itemsResult.data || []; - - // 견적서 첨부파일 조회 - const quotationAttachments = await db.query.techSalesVendorQuotationAttachments.findMany({ - where: eq(techSalesVendorQuotationAttachments.quotationId, quotationId), - orderBy: [desc(techSalesVendorQuotationAttachments.createdAt)], - }); - - // 기존 구조와 호환되도록 데이터 재구성 - const formattedQuotation = { - id: quotation.id, - rfqId: quotation.rfqId, - vendorId: quotation.vendorId, - quotationCode: quotation.quotationCode, - quotationVersion: quotation.quotationVersion, - totalPrice: quotation.totalPrice, - currency: quotation.currency, - validUntil: quotation.validUntil, - status: quotation.status, - remark: quotation.remark, - rejectionReason: quotation.rejectionReason, - submittedAt: quotation.submittedAt, - acceptedAt: quotation.acceptedAt, - createdAt: quotation.createdAt, - updatedAt: quotation.updatedAt, - createdBy: quotation.createdBy, - updatedBy: quotation.updatedBy, - - // RFQ 정보 - rfq: { - id: quotation.rfqId, - rfqCode: quotation.rfqCode, - rfqType: quotation.rfqType, - status: quotation.rfqStatus, - dueDate: quotation.dueDate, - rfqSendDate: quotation.rfqSendDate, - materialCode: quotation.materialCode, - description: quotation.description, - remark: quotation.rfqRemark, - picCode: quotation.picCode, - createdBy: quotation.rfqCreatedBy, - biddingProjectId: quotation.biddingProjectId, - - // 아이템 정보 추가 - items: items, - - // 생성자 정보 - createdByUser: { - id: quotation.rfqCreatedBy, - name: quotation.rfqCreatedByName, - email: quotation.rfqCreatedByEmail, - }, - - // 프로젝트 정보 - biddingProject: quotation.biddingProjectId ? { - id: quotation.biddingProjectId, - pspid: quotation.pspid, - projNm: quotation.projNm, - sector: quotation.sector, - projMsrm: quotation.projMsrm, - ptypeNm: quotation.ptypeNm, - } : null, - }, - - // 벤더 정보 - vendor: { - id: quotation.vendorId, - vendorName: quotation.vendorName, - vendorCode: quotation.vendorCode, - country: quotation.vendorCountry, - email: quotation.vendorEmail, - phone: quotation.vendorPhone, - }, - - // 첨부파일 정보 - quotationAttachments: quotationAttachments.map(attachment => ({ - id: attachment.id, - fileName: attachment.fileName, - fileSize: attachment.fileSize, - filePath: attachment.filePath, - description: attachment.description, - })) - }; - - return { data: formattedQuotation, error: null }; - } catch (err) { - console.error("Error fetching vendor quotation:", err); - return { data: null, error: getErrorMessage(err) }; - } -} - -/** - * 기술영업 벤더 견적서 업데이트 (임시저장), - * 현재는 submit으로 처리, revision 을 아래의 함수로 사용가능함. - */ -export async function updateTechSalesVendorQuotation(data: { - id: number - currency: string - totalPrice: string - validUntil: Date - remark?: string - updatedBy: number - changeReason?: string -}) { - try { - return await db.transaction(async (tx) => { - // 현재 견적서 전체 데이터 조회 (revision 저장용) - const currentQuotation = await tx.query.techSalesVendorQuotations.findFirst({ - where: eq(techSalesVendorQuotations.id, data.id), - }); - - if (!currentQuotation) { - return { data: null, error: "견적서를 찾을 수 없습니다." }; - } - - // Accepted나 Rejected 상태가 아니면 수정 가능 - if (["Rejected"].includes(currentQuotation.status)) { - return { data: null, error: "승인되거나 거절된 견적서는 수정할 수 없습니다." }; - } - - // 실제 변경사항이 있는지 확인 - const hasChanges = - currentQuotation.currency !== data.currency || - currentQuotation.totalPrice !== data.totalPrice || - currentQuotation.validUntil?.getTime() !== data.validUntil.getTime() || - currentQuotation.remark !== (data.remark || null); - - if (!hasChanges) { - return { data: currentQuotation, error: null }; - } - - // 현재 버전을 revision history에 저장 - await tx.insert(techSalesVendorQuotationRevisions).values({ - quotationId: data.id, - version: currentQuotation.quotationVersion || 1, - snapshot: { - currency: currentQuotation.currency, - totalPrice: currentQuotation.totalPrice, - validUntil: currentQuotation.validUntil, - remark: currentQuotation.remark, - status: currentQuotation.status, - quotationVersion: currentQuotation.quotationVersion, - submittedAt: currentQuotation.submittedAt, - acceptedAt: currentQuotation.acceptedAt, - updatedAt: currentQuotation.updatedAt, - }, - changeReason: data.changeReason || "견적서 수정", - revisedBy: data.updatedBy, - }); - - // 새로운 버전으로 업데이트 - const result = await tx - .update(techSalesVendorQuotations) - .set({ - currency: data.currency, - totalPrice: data.totalPrice, - validUntil: data.validUntil, - remark: data.remark || null, - quotationVersion: (currentQuotation.quotationVersion || 1) + 1, - status: "Revised", // 수정된 상태로 변경 - updatedAt: new Date(), - }) - .where(eq(techSalesVendorQuotations.id, data.id)) - .returning(); - - return { data: result[0], error: null }; - }); - } catch (error) { - console.error("Error updating tech sales vendor quotation:", error); - return { data: null, error: "견적서 업데이트 중 오류가 발생했습니다" }; - } finally { - // 캐시 무효화 - revalidateTag("techSalesVendorQuotations"); - revalidatePath(`/partners/techsales/rfq-ship/${data.id}`); - } -} - -/** - * 기술영업 벤더 견적서 제출 - */ -export async function submitTechSalesVendorQuotation(data: { - id: number - currency: string - totalPrice: string - validUntil: Date - remark?: string - attachments?: Array<{ - fileName: string - filePath: string - fileSize: number - }> - updatedBy: number -}) { - try { - return await db.transaction(async (tx) => { - // 현재 견적서 전체 데이터 조회 (revision 저장용) - const currentQuotation = await tx.query.techSalesVendorQuotations.findFirst({ - where: eq(techSalesVendorQuotations.id, data.id), - }); - - if (!currentQuotation) { - return { data: null, error: "견적서를 찾을 수 없습니다." }; - } - - // Rejected 상태에서는 제출 불가 - if (["Rejected"].includes(currentQuotation.status)) { - return { data: null, error: "거절된 견적서는 제출할 수 없습니다." }; - } - - // // 실제 변경사항이 있는지 확인 - // const hasChanges = - // currentQuotation.currency !== data.currency || - // currentQuotation.totalPrice !== data.totalPrice || - // currentQuotation.validUntil?.getTime() !== data.validUntil.getTime() || - // currentQuotation.remark !== (data.remark || null); - - // // 변경사항이 있거나 처음 제출하는 경우 revision 저장 - // if (hasChanges || currentQuotation.status === "Draft") { - // await tx.insert(techSalesVendorQuotationRevisions).values({ - // quotationId: data.id, - // version: currentQuotation.quotationVersion || 1, - // snapshot: { - // currency: currentQuotation.currency, - // totalPrice: currentQuotation.totalPrice, - // validUntil: currentQuotation.validUntil, - // remark: currentQuotation.remark, - // status: currentQuotation.status, - // quotationVersion: currentQuotation.quotationVersion, - // submittedAt: currentQuotation.submittedAt, - // acceptedAt: currentQuotation.acceptedAt, - // updatedAt: currentQuotation.updatedAt, - // }, - // changeReason: "견적서 제출", - // revisedBy: data.updatedBy, - // }); - // } - - // 첫 제출인지 확인 (quotationVersion이 null인 경우) - const isFirstSubmission = currentQuotation.quotationVersion === null; - - // 첫 제출이 아닌 경우에만 revision 저장 (변경사항 이력 관리) - if (!isFirstSubmission) { - await tx.insert(techSalesVendorQuotationRevisions).values({ - quotationId: data.id, - version: currentQuotation.quotationVersion || 1, - snapshot: { - currency: currentQuotation.currency, - totalPrice: currentQuotation.totalPrice, - validUntil: currentQuotation.validUntil, - remark: currentQuotation.remark, - status: currentQuotation.status, - quotationVersion: currentQuotation.quotationVersion, - submittedAt: currentQuotation.submittedAt, - acceptedAt: currentQuotation.acceptedAt, - updatedAt: currentQuotation.updatedAt, - }, - changeReason: "견적서 제출", - revisedBy: data.updatedBy, - }); - } - - // 새로운 버전 번호 계산 (첫 제출은 1, 재제출은 1 증가) - const newRevisionId = isFirstSubmission ? 1 : (currentQuotation.quotationVersion || 1) + 1; - - // 새로운 버전으로 업데이트 - const result = await tx - .update(techSalesVendorQuotations) - .set({ - currency: data.currency, - totalPrice: data.totalPrice, - validUntil: data.validUntil, - remark: data.remark || null, - quotationVersion: newRevisionId, - status: "Submitted", - submittedAt: new Date(), - updatedAt: new Date(), - }) - .where(eq(techSalesVendorQuotations.id, data.id)) - .returning(); - - // 첨부파일 처리 (새로운 revisionId 사용) - if (data.attachments && data.attachments.length > 0) { - for (const attachment of data.attachments) { - await tx.insert(techSalesVendorQuotationAttachments).values({ - quotationId: data.id, - revisionId: newRevisionId, // 새로운 리비전 ID 사용 - fileName: attachment.fileName, - originalFileName: attachment.fileName, - fileSize: attachment.fileSize, - filePath: attachment.filePath, - fileType: attachment.fileName.split('.').pop() || 'unknown', - uploadedBy: data.updatedBy, - isVendorUpload: true, - }); - } - } - - // 메일 발송 (백그라운드에서 실행) - if (result[0]) { - // 벤더에게 견적 제출 확인 메일 발송 - sendQuotationSubmittedNotificationToVendor(data.id).catch(error => { - console.error("벤더 견적 제출 확인 메일 발송 실패:", error); - }); - - // 담당자에게 견적 접수 알림 메일 발송 - sendQuotationSubmittedNotificationToManager(data.id).catch(error => { - console.error("담당자 견적 접수 알림 메일 발송 실패:", error); - }); - } - - return { data: result[0], error: null }; - }); - } catch (error) { - console.error("Error submitting tech sales vendor quotation:", error); - return { data: null, error: "견적서 제출 중 오류가 발생했습니다" }; - } finally { - // 캐시 무효화 - revalidateTag("techSalesVendorQuotations"); - revalidatePath(`/partners/techsales/rfq-ship`); - } -} - -/** - * 통화 목록 조회 - */ -export async function fetchCurrencies() { - try { - // 기본 통화 목록 (실제로는 DB에서 가져와야 함) - const currencies = [ - { code: "USD", name: "미국 달러" }, - { code: "KRW", name: "한국 원" }, - { code: "EUR", name: "유로" }, - { code: "JPY", name: "일본 엔" }, - { code: "CNY", name: "중국 위안" }, - ] - - return { data: currencies, error: null } - } catch (error) { - console.error("Error fetching currencies:", error) - return { data: null, error: "통화 목록 조회 중 오류가 발생했습니다" } - } -} - -/** - * 벤더용 기술영업 견적서 목록 조회 (페이지네이션 포함) - */ -export async function getVendorQuotations(input: { - flags?: string[]; - page: number; - perPage: number; - sort?: { id: string; desc: boolean }[]; - filters?: Filter[]; - joinOperator?: "and" | "or"; - basicFilters?: Filter[]; - basicJoinOperator?: "and" | "or"; - search?: string; - from?: string; - to?: string; - rfqType?: "SHIP" | "TOP" | "HULL"; -}, vendorId: string) { - return unstable_cache( - async () => { - try { - console.log('🔍 [getVendorQuotations] 호출됨:', { - vendorId, - vendorIdParsed: parseInt(vendorId), - rfqType: input.rfqType, - inputData: input - }); - - const { page, perPage, sort, filters = [], search = "", from = "", to = "" } = input; - const offset = (page - 1) * perPage; - const limit = perPage; - - // 기본 조건: 해당 벤더의 견적서만 조회 (Assigned 상태 제외) - const vendorIdNum = parseInt(vendorId); - if (isNaN(vendorIdNum)) { - console.error('❌ [getVendorQuotations] Invalid vendorId:', vendorId); - return { data: [], pageCount: 0, total: 0 }; - } - - const baseConditions = [ - eq(techSalesVendorQuotations.vendorId, vendorIdNum), - sql`${techSalesVendorQuotations.status} != 'Assigned'` // Assigned 상태 제외 - ]; - - // rfqType 필터링 추가 - if (input.rfqType) { - baseConditions.push(eq(techSalesRfqs.rfqType, input.rfqType)); - } - - // 검색 조건 추가 - if (search) { - const s = `%${search}%`; - const searchCondition = or( - ilike(techSalesVendorQuotations.currency, s), - ilike(techSalesVendorQuotations.status, s) - ); - if (searchCondition) { - baseConditions.push(searchCondition); - } - } - - // 날짜 범위 필터 - if (from) { - baseConditions.push(sql`${techSalesVendorQuotations.createdAt} >= ${from}`); - } - if (to) { - baseConditions.push(sql`${techSalesVendorQuotations.createdAt} <= ${to}`); - } - - // 고급 필터 처리 - if (filters.length > 0) { - const filterWhere = filterColumns({ - table: techSalesVendorQuotations, - filters: filters as Filter[], - joinOperator: input.joinOperator || "and", - }); - if (filterWhere) { - baseConditions.push(filterWhere); - } - } - - // 최종 WHERE 조건 - const finalWhere = baseConditions.length > 0 - ? and(...baseConditions) - : undefined; - - // 정렬 기준 설정 - let orderBy: OrderByType[] = [desc(techSalesVendorQuotations.updatedAt)]; - - if (sort?.length) { - orderBy = sort.map(item => { - switch (item.id) { - case 'id': - return item.desc ? desc(techSalesVendorQuotations.id) : techSalesVendorQuotations.id; - case 'status': - return item.desc ? desc(techSalesVendorQuotations.status) : techSalesVendorQuotations.status; - case 'currency': - return item.desc ? desc(techSalesVendorQuotations.currency) : techSalesVendorQuotations.currency; - case 'totalPrice': - return item.desc ? desc(techSalesVendorQuotations.totalPrice) : techSalesVendorQuotations.totalPrice; - case 'validUntil': - return item.desc ? desc(techSalesVendorQuotations.validUntil) : techSalesVendorQuotations.validUntil; - case 'submittedAt': - return item.desc ? desc(techSalesVendorQuotations.submittedAt) : techSalesVendorQuotations.submittedAt; - case 'createdAt': - return item.desc ? desc(techSalesVendorQuotations.createdAt) : techSalesVendorQuotations.createdAt; - case 'updatedAt': - return item.desc ? desc(techSalesVendorQuotations.updatedAt) : techSalesVendorQuotations.updatedAt; - case 'rfqCode': - return item.desc ? desc(techSalesRfqs.rfqCode) : techSalesRfqs.rfqCode; - case 'materialCode': - return item.desc ? desc(techSalesRfqs.materialCode) : techSalesRfqs.materialCode; - case 'dueDate': - return item.desc ? desc(techSalesRfqs.dueDate) : techSalesRfqs.dueDate; - case 'rfqStatus': - return item.desc ? desc(techSalesRfqs.status) : techSalesRfqs.status; - default: - return item.desc ? desc(techSalesVendorQuotations.updatedAt) : techSalesVendorQuotations.updatedAt; - } - }); - } - - // 조인을 포함한 데이터 조회 (중복 제거를 위해 techSalesAttachments JOIN 제거) - const data = await db - .select({ - id: techSalesVendorQuotations.id, - rfqId: techSalesVendorQuotations.rfqId, - vendorId: techSalesVendorQuotations.vendorId, - status: techSalesVendorQuotations.status, - currency: techSalesVendorQuotations.currency, - totalPrice: techSalesVendorQuotations.totalPrice, - validUntil: techSalesVendorQuotations.validUntil, - submittedAt: techSalesVendorQuotations.submittedAt, - remark: techSalesVendorQuotations.remark, - createdAt: techSalesVendorQuotations.createdAt, - updatedAt: techSalesVendorQuotations.updatedAt, - createdBy: techSalesVendorQuotations.createdBy, - updatedBy: techSalesVendorQuotations.updatedBy, - quotationCode: techSalesVendorQuotations.quotationCode, - quotationVersion: techSalesVendorQuotations.quotationVersion, - rejectionReason: techSalesVendorQuotations.rejectionReason, - acceptedAt: techSalesVendorQuotations.acceptedAt, - // RFQ 정보 - rfqCode: techSalesRfqs.rfqCode, - materialCode: techSalesRfqs.materialCode, - dueDate: techSalesRfqs.dueDate, - rfqStatus: techSalesRfqs.status, - description: techSalesRfqs.description, - // 프로젝트 정보 (직접 조인) - projNm: biddingProjects.projNm, - // 아이템 개수 - itemCount: sql`( - SELECT COUNT(*) - FROM tech_sales_rfq_items - WHERE tech_sales_rfq_items.rfq_id = ${techSalesRfqs.id} - )`, - // RFQ 첨부파일 개수 (RFQ_COMMON 타입만 카운트) - attachmentCount: sql`( - SELECT COUNT(*) - FROM tech_sales_attachments - WHERE tech_sales_attachments.tech_sales_rfq_id = ${techSalesRfqs.id} - AND tech_sales_attachments.attachment_type = 'RFQ_COMMON' - )`, - }) - .from(techSalesVendorQuotations) - .leftJoin(techSalesRfqs, eq(techSalesVendorQuotations.rfqId, techSalesRfqs.id)) - .leftJoin(biddingProjects, eq(techSalesRfqs.biddingProjectId, biddingProjects.id)) - .where(finalWhere) - .orderBy(...orderBy) - .limit(limit) - .offset(offset); - - // 총 개수 조회 - const totalResult = await db - .select({ count: sql`count(*)` }) - .from(techSalesVendorQuotations) - .leftJoin(techSalesRfqs, eq(techSalesVendorQuotations.rfqId, techSalesRfqs.id)) - .leftJoin(biddingProjects, eq(techSalesRfqs.biddingProjectId, biddingProjects.id)) - .where(finalWhere); - - const total = totalResult[0]?.count || 0; - const pageCount = Math.ceil(total / perPage); - - return { data, pageCount, total }; - } catch (err) { - console.error("Error fetching vendor quotations:", err); - return { data: [], pageCount: 0, total: 0 }; - } - }, - [JSON.stringify(input), vendorId], // 캐싱 키 - { - revalidate: 60, // 1분간 캐시 - tags: [ - "techSalesVendorQuotations", - `vendor-${vendorId}-quotations` - ], - } - )(); -} - -/** - * 기술영업 벤더 견적 승인 (벤더 선택) - */ -export async function acceptTechSalesVendorQuotation(quotationId: number) { - try { - const result = await db.transaction(async (tx) => { - // 1. 선택된 견적 정보 조회 - const selectedQuotation = await tx - .select() - .from(techSalesVendorQuotations) - .where(eq(techSalesVendorQuotations.id, quotationId)) - .limit(1) - - if (selectedQuotation.length === 0) { - throw new Error("견적을 찾을 수 없습니다") - } - - const quotation = selectedQuotation[0] - - // 2. 선택된 견적을 Accepted로 변경 - await tx - .update(techSalesVendorQuotations) - .set({ - status: "Accepted", - acceptedAt: new Date(), - updatedAt: new Date(), - }) - .where(eq(techSalesVendorQuotations.id, quotationId)) - - // 4. RFQ 상태를 Closed로 변경 - await tx - .update(techSalesRfqs) - .set({ - status: "Closed", - updatedAt: new Date(), - }) - .where(eq(techSalesRfqs.id, quotation.rfqId)) - - return quotation - }) - - // 메일 발송 (백그라운드에서 실행) - // 선택된 벤더에게 견적 선택 알림 메일 발송 - sendQuotationAcceptedNotification(quotationId).catch(error => { - console.error("벤더 견적 선택 알림 메일 발송 실패:", error); - }); - - // 캐시 무효화 - revalidateTag("techSalesVendorQuotations") - revalidateTag(`techSalesRfq-${result.rfqId}`) - revalidateTag("techSalesRfqs") - - // 해당 RFQ의 모든 벤더 캐시 무효화 (선택된 벤더와 거절된 벤더들) - const allVendorsInRfq = await db.query.techSalesVendorQuotations.findMany({ - where: eq(techSalesVendorQuotations.rfqId, result.rfqId), - columns: { vendorId: true } - }); - - for (const vendorQuotation of allVendorsInRfq) { - revalidateTag(`vendor-${vendorQuotation.vendorId}-quotations`); - } - revalidatePath("/evcp/budgetary-tech-sales-ship") - revalidatePath("/partners/techsales") - - - return { success: true, data: result } - } catch (error) { - console.error("벤더 견적 승인 오류:", error) - return { - success: false, - error: error instanceof Error ? error.message : "벤더 견적 승인에 실패했습니다" - } - } -} - -/** - * 기술영업 RFQ 첨부파일 생성 (파일 업로드), 사용x - */ -export async function createTechSalesRfqAttachments(params: { - techSalesRfqId: number - files: File[] - createdBy: number - attachmentType?: "RFQ_COMMON" | "VENDOR_SPECIFIC" - description?: string -}) { - unstable_noStore(); - try { - const { techSalesRfqId, files, createdBy, attachmentType = "RFQ_COMMON", description } = params; - - if (!files || files.length === 0) { - return { data: null, error: "업로드할 파일이 없습니다." }; - } - - // RFQ 존재 확인 - const rfq = await db.query.techSalesRfqs.findFirst({ - where: eq(techSalesRfqs.id, techSalesRfqId), - columns: { id: true, status: true } - }); - - if (!rfq) { - return { data: null, error: "RFQ를 찾을 수 없습니다." }; - } - - // 편집 가능한 상태 확인 - if (!["RFQ Created", "RFQ Vendor Assignned"].includes(rfq.status)) { - return { data: null, error: "현재 상태에서는 첨부파일을 추가할 수 없습니다." }; - } - - const results: typeof techSalesAttachments.$inferSelect[] = []; - - // 트랜잭션으로 처리 - await db.transaction(async (tx) => { - - for (const file of files) { - const saveResult = await saveDRMFile(file, decryptWithServerAction,`techsales-rfq/${techSalesRfqId}` ) - - // DB에 첨부파일 레코드 생성 - const [newAttachment] = await tx.insert(techSalesAttachments).values({ - techSalesRfqId, - attachmentType, - fileName: saveResult.fileName, - originalFileName: file.name, - filePath: saveResult.publicPath, - fileSize: file.size, - fileType: file.type || undefined, - description: description || undefined, - createdBy, - }).returning(); - - results.push(newAttachment); - } - }); - - // RFQ 타입 조회하여 캐시 무효화 - const rfqType = await db.query.techSalesRfqs.findFirst({ - where: eq(techSalesRfqs.id, techSalesRfqId), - columns: { rfqType: true } - }); - - revalidateTag("techSalesRfqs"); - revalidateTag(`techSalesRfq-${techSalesRfqId}`); - revalidatePath(getTechSalesRevalidationPath(rfqType?.rfqType || "SHIP")); - - return { data: results, error: null }; - } catch (err) { - console.error("기술영업 RFQ 첨부파일 생성 오류:", err); - return { data: null, error: getErrorMessage(err) }; - } -} - -/** - * 기술영업 RFQ 첨부파일 조회 - */ -export async function getTechSalesRfqAttachments(techSalesRfqId: number) { - unstable_noStore(); - try { - const attachments = await db.query.techSalesAttachments.findMany({ - where: eq(techSalesAttachments.techSalesRfqId, techSalesRfqId), - orderBy: [desc(techSalesAttachments.createdAt)], - with: { - createdByUser: { - columns: { - id: true, - name: true, - email: true, - } - } - } - }); - - return { data: attachments, error: null }; - } catch (err) { - console.error("기술영업 RFQ 첨부파일 조회 오류:", err); - return { data: [], error: getErrorMessage(err) }; - } -} - -/** - * RFQ 첨부파일 타입별 조회 - */ -export async function getTechSalesRfqAttachmentsByType( - techSalesRfqId: number, - attachmentType: "RFQ_COMMON" | "VENDOR_SPECIFIC" | "TBE_RESULT" | "CBE_RESULT" -) { - unstable_noStore(); - try { - const attachments = await db.query.techSalesAttachments.findMany({ - where: and( - eq(techSalesAttachments.techSalesRfqId, techSalesRfqId), - eq(techSalesAttachments.attachmentType, attachmentType) - ), - orderBy: [desc(techSalesAttachments.createdAt)], - with: { - createdByUser: { - columns: { - id: true, - name: true, - email: true, - } - } - } - }); - - return { data: attachments, error: null }; - } catch (err) { - console.error(`기술영업 RFQ ${attachmentType} 첨부파일 조회 오류:`, err); - return { data: [], error: getErrorMessage(err) }; - } -} - -/** - * 기술영업 RFQ 첨부파일 삭제 - */ -export async function deleteTechSalesRfqAttachment(attachmentId: number) { - unstable_noStore(); - try { - // 첨부파일 정보 조회 - const attachment = await db.query.techSalesAttachments.findFirst({ - where: eq(techSalesAttachments.id, attachmentId), - }); - - if (!attachment) { - return { data: null, error: "첨부파일을 찾을 수 없습니다." }; - } - - // RFQ 상태 확인 - const rfq = await db.query.techSalesRfqs.findFirst({ - where: eq(techSalesRfqs.id, attachment.techSalesRfqId!), // Non-null assertion since we know it exists - columns: { id: true, status: true } - }); - - if (!rfq) { - return { data: null, error: "RFQ를 찾을 수 없습니다." }; - } - - // 편집 가능한 상태 확인 - if (!["RFQ Created", "RFQ Vendor Assignned"].includes(rfq.status)) { - return { data: null, error: "현재 상태에서는 첨부파일을 삭제할 수 없습니다." }; - } - - // 트랜잭션으로 처리 - const result = await db.transaction(async (tx) => { - // DB에서 레코드 삭제 - const deletedAttachment = await tx.delete(techSalesAttachments) - .where(eq(techSalesAttachments.id, attachmentId)) - .returning(); - - // 파일 시스템에서 파일 삭제 - try { - await deleteFile(`${attachment.filePath}`) - - } catch (fileError) { - console.warn("파일 삭제 실패:", fileError); - // 파일 삭제 실패는 심각한 오류가 아니므로 계속 진행 - } - - return deletedAttachment[0]; - }); - - // RFQ 타입 조회하여 캐시 무효화 - const attachmentRfq = await db.query.techSalesRfqs.findFirst({ - where: eq(techSalesRfqs.id, attachment.techSalesRfqId!), - columns: { rfqType: true } - }); - - revalidateTag("techSalesRfqs"); - revalidateTag(`techSalesRfq-${attachment.techSalesRfqId}`); - revalidatePath(getTechSalesRevalidationPath(attachmentRfq?.rfqType || "SHIP")); - - return { data: result, error: null }; - } catch (err) { - console.error("기술영업 RFQ 첨부파일 삭제 오류:", err); - return { data: null, error: getErrorMessage(err) }; - } -} - -/** - * 기술영업 RFQ 첨부파일 일괄 처리 (업로드 + 삭제) - */ -export async function processTechSalesRfqAttachments(params: { - techSalesRfqId: number - newFiles: { file: File; attachmentType: "RFQ_COMMON" | "VENDOR_SPECIFIC" | "TBE_RESULT" | "CBE_RESULT"; description?: string }[] - deleteAttachmentIds: number[] - createdBy: number -}) { - unstable_noStore(); - try { - const { techSalesRfqId, newFiles, deleteAttachmentIds, createdBy } = params; - - // RFQ 존재 및 상태 확인 - const rfq = await db.query.techSalesRfqs.findFirst({ - where: eq(techSalesRfqs.id, techSalesRfqId), - columns: { id: true, status: true } - }); - - if (!rfq) { - return { data: null, error: "RFQ를 찾을 수 없습니다." }; - } - - if (!["RFQ Created", "RFQ Vendor Assignned"].includes(rfq.status)) { - return { data: null, error: "현재 상태에서는 첨부파일을 수정할 수 없습니다." }; - } - - const results = { - uploaded: [] as typeof techSalesAttachments.$inferSelect[], - deleted: [] as typeof techSalesAttachments.$inferSelect[], - }; - - await db.transaction(async (tx) => { - - // 1. 삭제할 첨부파일 처리 - if (deleteAttachmentIds.length > 0) { - const attachmentsToDelete = await tx.query.techSalesAttachments.findMany({ - where: sql`${techSalesAttachments.id} IN (${deleteAttachmentIds.join(',')})` - }); - - for (const attachment of attachmentsToDelete) { - // DB에서 레코드 삭제 - const [deletedAttachment] = await tx.delete(techSalesAttachments) - .where(eq(techSalesAttachments.id, attachment.id)) - .returning(); - - results.deleted.push(deletedAttachment); - await deleteFile(attachment.filePath) - - } - } - - // 2. 새 파일 업로드 처리 - if (newFiles.length > 0) { - for (const { file, attachmentType, description } of newFiles) { - const saveResult = await saveDRMFile(file, decryptWithServerAction,`techsales-rfq/${techSalesRfqId}` ) - - // DB에 첨부파일 레코드 생성 - const [newAttachment] = await tx.insert(techSalesAttachments).values({ - techSalesRfqId, - attachmentType, - fileName: saveResult.fileName, - originalFileName: file.name, - filePath: saveResult.publicPath, - fileSize: file.size, - fileType: file.type || undefined, - description: description || undefined, - createdBy, - }).returning(); - - results.uploaded.push(newAttachment); - } - } - }); - - // 캐시 무효화 - revalidateTag("techSalesRfqs"); - revalidateTag(`techSalesRfq-${techSalesRfqId}`); - revalidatePath("/evcp/budgetary-tech-sales-ship"); - - return { - data: results, - error: null, - message: `${results.uploaded.length}개 업로드, ${results.deleted.length}개 삭제 완료` - }; - } catch (err) { - console.error("기술영업 RFQ 첨부파일 일괄 처리 오류:", err); - return { data: null, error: getErrorMessage(err) }; - } -} - -// ======================================== -// 메일 발송 관련 함수들 -// ======================================== - -/** - * 벤더 견적 제출 확인 메일 발송 (벤더용) - */ -export async function sendQuotationSubmittedNotificationToVendor(quotationId: number) { - try { - // 견적서 정보 조회 (projectSeries 조인 추가) - const quotation = await db.query.techSalesVendorQuotations.findFirst({ - where: eq(techSalesVendorQuotations.id, quotationId), - with: { - rfq: { - with: { - biddingProject: true, - createdByUser: { - columns: { - id: true, - name: true, - email: true, - } - } - } - }, - vendor: { - columns: { - id: true, - vendorName: true, - vendorCode: true, - } - } - } - }); - - if (!quotation || !quotation.rfq || !quotation.vendor) { - console.error("견적서 또는 관련 정보를 찾을 수 없습니다"); - return { success: false, error: "견적서 정보를 찾을 수 없습니다" }; - } - - // 벤더 사용자들 조회 - const vendorUsers = await db.query.users.findMany({ - where: eq(users.companyId, quotation.vendor.id), - columns: { - id: true, - email: true, - name: true, - language: true - } - }); - - const vendorEmails = vendorUsers - .filter(user => user.email) - .map(user => user.email) - .join(", "); - - if (!vendorEmails) { - console.warn(`벤더 ID ${quotation.vendor.id}에 등록된 이메일 주소가 없습니다`); - return { success: false, error: "벤더 이메일 주소가 없습니다" }; - } - - // RFQ 아이템 정보 조회 - const rfqItemsResult = await getTechSalesRfqItems(quotation.rfq.id); - const rfqItems = rfqItemsResult.data || []; - - // 이메일 컨텍스트 구성 (시리즈 정보 제거, 프로젝트 정보 간소화) - const emailContext = { - language: vendorUsers[0]?.language || "ko", - quotation: { - id: quotation.id, - currency: quotation.currency, - totalPrice: quotation.totalPrice, - validUntil: quotation.validUntil, - submittedAt: quotation.submittedAt, - remark: quotation.remark, - }, - rfq: { - id: quotation.rfq.id, - code: quotation.rfq.rfqCode, - title: quotation.rfq.description || '', - projectCode: quotation.rfq.biddingProject?.pspid || '', - projectName: quotation.rfq.biddingProject?.projNm || '', - dueDate: quotation.rfq.dueDate, - materialCode: quotation.rfq.materialCode, - description: quotation.rfq.remark, - }, - items: rfqItems.map(item => ({ - itemCode: item.itemCode, - itemList: item.itemList, - workType: item.workType, - shipType: item.shipType, - subItemName: item.subItemName, - itemType: item.itemType, - })), - vendor: { - id: quotation.vendor.id, - code: quotation.vendor.vendorCode, - name: quotation.vendor.vendorName, - }, - project: { - name: quotation.rfq.biddingProject?.projNm || '', - sector: quotation.rfq.biddingProject?.sector || '', - shipCount: quotation.rfq.biddingProject?.projMsrm ? Number(quotation.rfq.biddingProject.projMsrm) : 0, - ownerName: quotation.rfq.biddingProject?.kunnrNm || '', - className: quotation.rfq.biddingProject?.cls1Nm || '', - }, - manager: { - name: quotation.rfq.createdByUser?.name || '', - email: quotation.rfq.createdByUser?.email || '', - }, - systemUrl: process.env.NEXT_PUBLIC_APP_URL || 'http://60.101.108.100/ko/partners', - companyName: 'Samsung Heavy Industries', - year: new Date().getFullYear(), - }; - - // 이메일 발송 - await sendEmail({ - to: vendorEmails, - subject: `[견적 제출 확인] ${quotation.rfq.rfqCode} - 견적 요청`, - template: 'tech-sales-quotation-submitted-vendor-ko', - context: emailContext, - }); - - console.log(`벤더 견적 제출 확인 메일 발송 완료: ${vendorEmails}`); - return { success: true }; - } catch (error) { - console.error("벤더 견적 제출 확인 메일 발송 오류:", error); - return { success: false, error: "메일 발송 중 오류가 발생했습니다" }; - } -} - -/** - * 벤더 견적 접수 알림 메일 발송 (담당자용) - */ -export async function sendQuotationSubmittedNotificationToManager(quotationId: number) { - try { - // 견적서 정보 조회 - const quotation = await db.query.techSalesVendorQuotations.findFirst({ - where: eq(techSalesVendorQuotations.id, quotationId), - with: { - rfq: { - with: { - biddingProject: true, - createdByUser: { - columns: { - id: true, - name: true, - email: true, - } - } - } - }, - vendor: { - columns: { - id: true, - vendorName: true, - vendorCode: true, - } - } - } - }); - - if (!quotation || !quotation.rfq || !quotation.vendor) { - console.error("견적서 또는 관련 정보를 찾을 수 없습니다"); - return { success: false, error: "견적서 정보를 찾을 수 없습니다" }; - } - - const manager = quotation.rfq.createdByUser; - if (!manager?.email) { - console.warn("담당자 이메일 주소가 없습니다"); - return { success: false, error: "담당자 이메일 주소가 없습니다" }; - } - - // RFQ 아이템 정보 조회 - const rfqItemsResult = await getTechSalesRfqItems(quotation.rfq.id); - const rfqItems = rfqItemsResult.data || []; - - // 이메일 컨텍스트 구성 (시리즈 정보 제거, 프로젝트 정보 간소화) - const emailContext = { - language: "ko", - quotation: { - id: quotation.id, - currency: quotation.currency, - totalPrice: quotation.totalPrice, - validUntil: quotation.validUntil, - submittedAt: quotation.submittedAt, - remark: quotation.remark, - }, - rfq: { - id: quotation.rfq.id, - code: quotation.rfq.rfqCode, - title: quotation.rfq.description || '', - projectCode: quotation.rfq.biddingProject?.pspid || '', - projectName: quotation.rfq.biddingProject?.projNm || '', - dueDate: quotation.rfq.dueDate, - materialCode: quotation.rfq.materialCode, - description: quotation.rfq.remark, - }, - items: rfqItems.map(item => ({ - itemCode: item.itemCode, - itemList: item.itemList, - workType: item.workType, - shipType: item.shipType, - subItemName: item.subItemName, - itemType: item.itemType, - })), - vendor: { - id: quotation.vendor.id, - code: quotation.vendor.vendorCode, - name: quotation.vendor.vendorName, - }, - project: { - name: quotation.rfq.biddingProject?.projNm || '', - sector: quotation.rfq.biddingProject?.sector || '', - shipCount: quotation.rfq.biddingProject?.projMsrm ? Number(quotation.rfq.biddingProject.projMsrm) : 0, - ownerName: quotation.rfq.biddingProject?.kunnrNm || '', - className: quotation.rfq.biddingProject?.cls1Nm || '', - }, - manager: { - name: manager.name || '', - email: manager.email, - }, - systemUrl: process.env.NEXT_PUBLIC_APP_URL || 'http://60.101.108.100/ko/evcp', - companyName: 'Samsung Heavy Industries', - year: new Date().getFullYear(), - }; - - // 이메일 발송 - await sendEmail({ - to: manager.email, - subject: `[견적 접수 알림] ${quotation.vendor.vendorName}에서 ${quotation.rfq.rfqCode} 견적서를 제출했습니다`, - template: 'tech-sales-quotation-submitted-manager-ko', - context: emailContext, - }); - - console.log(`담당자 견적 접수 알림 메일 발송 완료: ${manager.email}`); - return { success: true }; - } catch (error) { - console.error("담당자 견적 접수 알림 메일 발송 오류:", error); - return { success: false, error: "메일 발송 중 오류가 발생했습니다" }; - } -} - -/** - * 벤더 견적 선택 알림 메일 발송 - */ -export async function sendQuotationAcceptedNotification(quotationId: number) { - try { - // 견적서 정보 조회 - const quotation = await db.query.techSalesVendorQuotations.findFirst({ - where: eq(techSalesVendorQuotations.id, quotationId), - with: { - rfq: { - with: { - biddingProject: true, - createdByUser: { - columns: { - id: true, - name: true, - email: true, - } - } - } - }, - vendor: { - columns: { - id: true, - vendorName: true, - vendorCode: true, - } - } - } - }); - - if (!quotation || !quotation.rfq || !quotation.vendor) { - console.error("견적서 또는 관련 정보를 찾을 수 없습니다"); - return { success: false, error: "견적서 정보를 찾을 수 없습니다" }; - } - - // 벤더 사용자들 조회 - const vendorUsers = await db.query.users.findMany({ - where: eq(users.companyId, quotation.vendor.id), - columns: { - id: true, - email: true, - name: true, - language: true - } - }); - - const vendorEmails = vendorUsers - .filter(user => user.email) - .map(user => user.email) - .join(", "); - - if (!vendorEmails) { - console.warn(`벤더 ID ${quotation.vendor.id}에 등록된 이메일 주소가 없습니다`); - return { success: false, error: "벤더 이메일 주소가 없습니다" }; - } - - // RFQ 아이템 정보 조회 - const rfqItemsResult = await getTechSalesRfqItems(quotation.rfq.id); - const rfqItems = rfqItemsResult.data || []; - - // 이메일 컨텍스트 구성 (시리즈 정보 제거, 프로젝트 정보 간소화) - const emailContext = { - language: vendorUsers[0]?.language || "ko", - quotation: { - id: quotation.id, - currency: quotation.currency, - totalPrice: quotation.totalPrice, - validUntil: quotation.validUntil, - acceptedAt: quotation.acceptedAt, - remark: quotation.remark, - }, - rfq: { - id: quotation.rfq.id, - code: quotation.rfq.rfqCode, - title: quotation.rfq.description || '', - projectCode: quotation.rfq.biddingProject?.pspid || '', - projectName: quotation.rfq.biddingProject?.projNm || '', - dueDate: quotation.rfq.dueDate, - materialCode: quotation.rfq.materialCode, - description: quotation.rfq.remark, - }, - items: rfqItems.map(item => ({ - itemCode: item.itemCode, - itemList: item.itemList, - workType: item.workType, - shipType: item.shipType, - subItemName: item.subItemName, - itemType: item.itemType, - })), - vendor: { - id: quotation.vendor.id, - code: quotation.vendor.vendorCode, - name: quotation.vendor.vendorName, - }, - project: { - name: quotation.rfq.biddingProject?.projNm || '', - sector: quotation.rfq.biddingProject?.sector || '', - shipCount: quotation.rfq.biddingProject?.projMsrm ? Number(quotation.rfq.biddingProject.projMsrm) : 0, - ownerName: quotation.rfq.biddingProject?.kunnrNm || '', - className: quotation.rfq.biddingProject?.cls1Nm || '', - }, - manager: { - name: quotation.rfq.createdByUser?.name || '', - email: quotation.rfq.createdByUser?.email || '', - }, - systemUrl: process.env.NEXT_PUBLIC_APP_URL || 'http://60.101.108.100/ko/partners', - companyName: 'Samsung Heavy Industries', - year: new Date().getFullYear(), - }; - - // 이메일 발송 - await sendEmail({ - to: vendorEmails, - subject: `[견적 선택 알림] ${quotation.rfq.rfqCode} - 귀하의 견적이 선택되었습니다`, - template: 'tech-sales-quotation-accepted-ko', - context: emailContext, - }); - - console.log(`벤더 견적 선택 알림 메일 발송 완료: ${vendorEmails}`); - return { success: true }; - } catch (error) { - console.error("벤더 견적 선택 알림 메일 발송 오류:", error); - return { success: false, error: "메일 발송 중 오류가 발생했습니다" }; - } -} - -// ==================== Vendor Communication 관련 ==================== - -export interface TechSalesAttachment { - id: number - fileName: string - fileSize: number - fileType: string | null // <- null 허용 - filePath: string - uploadedAt: Date -} - -export interface TechSalesComment { - id: number - rfqId: number - vendorId: number | null // null 허용으로 변경 - userId?: number | null // null 허용으로 변경 - content: string - isVendorComment: boolean | null // null 허용으로 변경 - createdAt: Date - updatedAt: Date - userName?: string | null // null 허용으로 변경 - vendorName?: string | null // null 허용으로 변경 - attachments: TechSalesAttachment[] - isRead: boolean | null // null 허용으로 변경 -} - -/** - * 특정 RFQ의 벤더별 읽지 않은 메시지 개수를 조회하는 함수 - * - * @param rfqId RFQ ID - * @returns 벤더별 읽지 않은 메시지 개수 (vendorId: count) - */ -export async function getTechSalesUnreadMessageCounts(rfqId: number): Promise> { - try { - // 벤더가 보낸 읽지 않은 메시지를 벤더별로 카운트 - const unreadCounts = await db - .select({ - vendorId: techSalesRfqComments.vendorId, - count: sql`count(*)`, - }) - .from(techSalesRfqComments) - .where( - and( - eq(techSalesRfqComments.rfqId, rfqId), - eq(techSalesRfqComments.isVendorComment, true), // 벤더가 보낸 메시지 - eq(techSalesRfqComments.isRead, false), // 읽지 않은 메시지 - sql`${techSalesRfqComments.vendorId} IS NOT NULL` // vendorId가 null이 아닌 것 - ) - ) - .groupBy(techSalesRfqComments.vendorId); - - // Record 형태로 변환 - const result: Record = {}; - unreadCounts.forEach(item => { - if (item.vendorId) { - result[item.vendorId] = item.count; - } - }); - - return result; - } catch (error) { - console.error('techSales 읽지 않은 메시지 개수 조회 오류:', error); - return {}; - } -} - -/** - * 특정 RFQ와 벤더 간의 커뮤니케이션 메시지를 가져오는 서버 액션 - * - * @param rfqId RFQ ID - * @param vendorId 벤더 ID - * @returns 코멘트 목록 - */ -export async function fetchTechSalesVendorComments(rfqId: number, vendorId?: number): Promise { - if (!vendorId) { - return [] - } - - try { - // 인증 확인 - const session = await getServerSession(authOptions); - - if (!session?.user) { - throw new Error("인증이 필요합니다") - } - - // 코멘트 쿼리 - const comments = await db.query.techSalesRfqComments.findMany({ - where: and( - eq(techSalesRfqComments.rfqId, rfqId), - eq(techSalesRfqComments.vendorId, vendorId) - ), - orderBy: [techSalesRfqComments.createdAt], - with: { - user: { - columns: { - name: true - } - }, - vendor: { - columns: { - vendorName: true - } - }, - attachments: true, - } - }) - - // 결과 매핑 - return comments.map(comment => ({ - id: comment.id, - rfqId: comment.rfqId, - vendorId: comment.vendorId, - userId: comment.userId || undefined, - content: comment.content, - isVendorComment: comment.isVendorComment, - createdAt: comment.createdAt, - updatedAt: comment.updatedAt, - userName: comment.user?.name, - vendorName: comment.vendor?.vendorName, - isRead: comment.isRead, - attachments: comment.attachments.map(att => ({ - id: att.id, - fileName: att.fileName, - fileSize: att.fileSize, - fileType: att.fileType, - filePath: att.filePath, - uploadedAt: att.uploadedAt - })) - })) - } catch (error) { - console.error('techSales 벤더 코멘트 가져오기 오류:', error) - throw error - } -} - -/** - * 코멘트를 읽음 상태로 표시하는 서버 액션 - * - * @param rfqId RFQ ID - * @param vendorId 벤더 ID - */ -export async function markTechSalesMessagesAsRead(rfqId: number, vendorId?: number): Promise { - if (!vendorId) { - return - } - - try { - // 인증 확인 - const session = await getServerSession(authOptions); - - if (!session?.user) { - throw new Error("인증이 필요합니다") - } - - // 벤더가 작성한 읽지 않은 코멘트 업데이트 - await db.update(techSalesRfqComments) - .set({ isRead: true }) - .where( - and( - eq(techSalesRfqComments.rfqId, rfqId), - eq(techSalesRfqComments.vendorId, vendorId), - eq(techSalesRfqComments.isVendorComment, true), - eq(techSalesRfqComments.isRead, false) - ) - ) - - // 캐시 무효화 - revalidateTag(`tech-sales-rfq-${rfqId}-comments`) - } catch (error) { - console.error('techSales 메시지 읽음 표시 오류:', error) - throw error - } -} - -// ==================== RFQ 조선/해양 관련 ==================== - -/** - * 기술영업 조선 RFQ 생성 (1:N 관계) - */ -export async function createTechSalesShipRfq(input: { - biddingProjectId: number; - itemIds: number[]; // 조선 아이템 ID 배열 - dueDate: Date; - description?: string; - createdBy: number; -}) { - unstable_noStore(); - try { - return await db.transaction(async (tx) => { - // 프로젝트 정보 조회 (유효성 검증) - const biddingProject = await tx.query.biddingProjects.findFirst({ - where: (biddingProjects, { eq }) => eq(biddingProjects.id, input.biddingProjectId) - }); - - if (!biddingProject) { - throw new Error(`프로젝트 ID ${input.biddingProjectId}를 찾을 수 없습니다.`); - } - - // RFQ 코드 생성 (SHIP 타입) - const rfqCode = await generateRfqCodes(tx, 1); - - // RFQ 생성 - const [rfq] = await tx - .insert(techSalesRfqs) - .values({ - rfqCode: rfqCode[0], - biddingProjectId: input.biddingProjectId, - description: input.description, - dueDate: input.dueDate, - status: "RFQ Created", - rfqType: "SHIP", - createdBy: input.createdBy, - updatedBy: input.createdBy, - }) - .returning({ id: techSalesRfqs.id }); - - // 아이템들 추가 - for (const itemId of input.itemIds) { - await tx - .insert(techSalesRfqItems) - .values({ - rfqId: rfq.id, - itemShipbuildingId: itemId, - itemType: "SHIP", - }); - } - - // 캐시 무효화 - revalidateTag("techSalesRfqs"); - revalidatePath("/evcp/budgetary-tech-sales-ship"); - - return { data: rfq, error: null }; - }); - } catch (err) { - console.error("Error creating Ship RFQ:", err); - return { data: null, error: getErrorMessage(err) }; - } -} - -/** - * 기술영업 해양 Hull RFQ 생성 (1:N 관계) - */ -export async function createTechSalesHullRfq(input: { - biddingProjectId: number; - itemIds: number[]; // Hull 아이템 ID 배열 - dueDate: Date; - description?: string; - createdBy: number; -}) { - unstable_noStore(); - console.log('🔍 createTechSalesHullRfq 호출됨:', input); - - try { - return await db.transaction(async (tx) => { - // 프로젝트 정보 조회 (유효성 검증) - const biddingProject = await tx.query.biddingProjects.findFirst({ - where: (biddingProjects, { eq }) => eq(biddingProjects.id, input.biddingProjectId) - }); - - if (!biddingProject) { - throw new Error(`프로젝트 ID ${input.biddingProjectId}를 찾을 수 없습니다.`); - } - - // RFQ 코드 생성 (HULL 타입) - const hullRfqCode = await generateRfqCodes(tx, 1); - - // RFQ 생성 - const [rfq] = await tx - .insert(techSalesRfqs) - .values({ - rfqCode: hullRfqCode[0], - biddingProjectId: input.biddingProjectId, - description: input.description, - dueDate: input.dueDate, - status: "RFQ Created", - rfqType: "HULL", - createdBy: input.createdBy, - updatedBy: input.createdBy, - }) - .returning({ id: techSalesRfqs.id }); - - // 아이템들 추가 - for (const itemId of input.itemIds) { - await tx - .insert(techSalesRfqItems) - .values({ - rfqId: rfq.id, - itemOffshoreHullId: itemId, - itemType: "HULL", - }); - } - - // 캐시 무효화 - revalidateTag("techSalesRfqs"); - revalidatePath("/evcp/budgetary-tech-sales-hull"); - - return { data: rfq, error: null }; - }); - } catch (err) { - console.error("Error creating Hull RFQ:", err); - return { data: null, error: getErrorMessage(err) }; - } -} - -/** - * 기술영업 해양 TOP RFQ 생성 (1:N 관계) - */ -export async function createTechSalesTopRfq(input: { - biddingProjectId: number; - itemIds: number[]; // TOP 아이템 ID 배열 - dueDate: Date; - description?: string; - createdBy: number; -}) { - unstable_noStore(); - console.log('🔍 createTechSalesTopRfq 호출됨:', input); - - try { - return await db.transaction(async (tx) => { - // 프로젝트 정보 조회 (유효성 검증) - const biddingProject = await tx.query.biddingProjects.findFirst({ - where: (biddingProjects, { eq }) => eq(biddingProjects.id, input.biddingProjectId) - }); - - if (!biddingProject) { - throw new Error(`프로젝트 ID ${input.biddingProjectId}를 찾을 수 없습니다.`); - } - - // RFQ 코드 생성 (TOP 타입) - const topRfqCode = await generateRfqCodes(tx, 1); - - // RFQ 생성 - const [rfq] = await tx - .insert(techSalesRfqs) - .values({ - rfqCode: topRfqCode[0], - biddingProjectId: input.biddingProjectId, - description: input.description, - dueDate: input.dueDate, - status: "RFQ Created", - rfqType: "TOP", - createdBy: input.createdBy, - updatedBy: input.createdBy, - }) - .returning({ id: techSalesRfqs.id }); - - // 아이템들 추가 - for (const itemId of input.itemIds) { - await tx - .insert(techSalesRfqItems) - .values({ - rfqId: rfq.id, - itemOffshoreTopId: itemId, - itemType: "TOP", - }); - } - - // 캐시 무효화 - revalidateTag("techSalesRfqs"); - revalidatePath("/evcp/budgetary-tech-sales-top"); - - return { data: rfq, error: null }; - }); - } catch (err) { - console.error("Error creating TOP RFQ:", err); - return { data: null, error: getErrorMessage(err) }; - } -} - -/** - * 조선 RFQ 전용 조회 함수 - */ -export async function getTechSalesShipRfqsWithJoin(input: GetTechSalesRfqsSchema) { - return getTechSalesRfqsWithJoin({ ...input, rfqType: "SHIP" }); -} - -/** - * 해양 TOP RFQ 전용 조회 함수 - */ -export async function getTechSalesTopRfqsWithJoin(input: GetTechSalesRfqsSchema) { - return getTechSalesRfqsWithJoin({ ...input, rfqType: "TOP" }); -} - -/** - * 해양 HULL RFQ 전용 조회 함수 - */ -export async function getTechSalesHullRfqsWithJoin(input: GetTechSalesRfqsSchema) { - return getTechSalesRfqsWithJoin({ ...input, rfqType: "HULL" }); -} - -/** - * 조선 벤더 견적서 전용 조회 함수 - */ -export async function getTechSalesShipVendorQuotationsWithJoin(input: { - rfqId?: number; - vendorId?: number; - search?: string; - filters?: Filter[]; - sort?: { id: string; desc: boolean }[]; - page: number; - perPage: number; -}) { - return getTechSalesVendorQuotationsWithJoin({ ...input, rfqType: "SHIP" }); -} - -/** - * 해양 TOP 벤더 견적서 전용 조회 함수 - */ -export async function getTechSalesTopVendorQuotationsWithJoin(input: { - rfqId?: number; - vendorId?: number; - search?: string; - filters?: Filter[]; - sort?: { id: string; desc: boolean }[]; - page: number; - perPage: number; -}) { - return getTechSalesVendorQuotationsWithJoin({ ...input, rfqType: "TOP" }); -} - -/** - * 해양 HULL 벤더 견적서 전용 조회 함수 - */ -export async function getTechSalesHullVendorQuotationsWithJoin(input: { - rfqId?: number; - vendorId?: number; - search?: string; - filters?: Filter[]; - sort?: { id: string; desc: boolean }[]; - page: number; - perPage: number; -}) { - return getTechSalesVendorQuotationsWithJoin({ ...input, rfqType: "HULL" }); -} - -/** - * 기술영업 RFQ의 아이템 목록 조회 - */ -export async function getTechSalesRfqItems(rfqId: number) { - unstable_noStore(); - try { - const items = await db.query.techSalesRfqItems.findMany({ - where: eq(techSalesRfqItems.rfqId, rfqId), - with: { - itemShipbuilding: { - columns: { - id: true, - itemCode: true, - itemList: true, - workType: true, - shipTypes: true, - } - }, - itemOffshoreTop: { - columns: { - id: true, - itemCode: true, - itemList: true, - workType: true, - subItemList: true, - } - }, - itemOffshoreHull: { - columns: { - id: true, - itemCode: true, - itemList: true, - workType: true, - subItemList: true, - } - } - }, - orderBy: [techSalesRfqItems.id] - }); - - // 아이템 타입에 따라 정보 매핑 - const mappedItems = items.map(item => { - let itemInfo = null; - - switch (item.itemType) { - case 'SHIP': - itemInfo = item.itemShipbuilding; - break; - case 'TOP': - itemInfo = item.itemOffshoreTop; - break; - case 'HULL': - itemInfo = item.itemOffshoreHull; - break; - } - - return { - id: item.id, - rfqId: item.rfqId, - itemType: item.itemType, - itemCode: itemInfo?.itemCode || '', - itemList: itemInfo?.itemList || '', - workType: itemInfo?.workType || '', - // 조선이면 shipType, 해양이면 subItemList - shipType: item.itemType === 'SHIP' ? (itemInfo as { shipTypes?: string })?.shipTypes || '' : undefined, - subItemName: item.itemType !== 'SHIP' ? (itemInfo as { subItemList?: string })?.subItemList || '' : undefined, - }; - }); - - return { data: mappedItems, error: null }; - } catch (err) { - console.error("Error fetching RFQ items:", err); - return { data: [], error: getErrorMessage(err) }; - } -} - -/** - * RFQ 아이템들과 매칭되는 후보 벤더들을 찾는 함수 - */ -export async function getTechSalesRfqCandidateVendors(rfqId: number) { - unstable_noStore(); - - try { - return await db.transaction(async (tx) => { - // 1. RFQ 정보 조회 (타입 확인) - const rfq = await tx.query.techSalesRfqs.findFirst({ - where: eq(techSalesRfqs.id, rfqId), - columns: { - id: true, - rfqType: true - } - }); - - if (!rfq) { - return { data: [], error: "RFQ를 찾을 수 없습니다." }; - } - - // 2. RFQ 아이템들 조회 - const rfqItems = await tx.query.techSalesRfqItems.findMany({ - where: eq(techSalesRfqItems.rfqId, rfqId), - with: { - itemShipbuilding: true, - itemOffshoreTop: true, - itemOffshoreHull: true, - } - }); - - if (rfqItems.length === 0) { - return { data: [], error: null }; - } - - // 3. 아이템 코드들 추출 - const itemCodes: string[] = []; - rfqItems.forEach(item => { - if (item.itemType === "SHIP" && item.itemShipbuilding?.itemCode) { - itemCodes.push(item.itemShipbuilding.itemCode); - } else if (item.itemType === "TOP" && item.itemOffshoreTop?.itemCode) { - itemCodes.push(item.itemOffshoreTop.itemCode); - } else if (item.itemType === "HULL" && item.itemOffshoreHull?.itemCode) { - itemCodes.push(item.itemOffshoreHull.itemCode); - } - }); - - if (itemCodes.length === 0) { - return { data: [], error: null }; - } - - // 4. RFQ 타입에 따른 벤더 타입 매핑 - const vendorTypeFilter = rfq.rfqType === "SHIP" ? "SHIP" : - rfq.rfqType === "TOP" ? "OFFSHORE_TOP" : - rfq.rfqType === "HULL" ? "OFFSHORE_HULL" : null; - - if (!vendorTypeFilter) { - return { data: [], error: "지원되지 않는 RFQ 타입입니다." }; - } - - // 5. 매칭되는 벤더들 조회 (타입 필터링 포함) - const candidateVendors = await tx - .select({ - id: techVendors.id, // 벤더 ID를 id로 명명하여 key 문제 해결 - vendorId: techVendors.id, // 호환성을 위해 유지 - vendorName: techVendors.vendorName, - vendorCode: techVendors.vendorCode, - country: techVendors.country, - email: techVendors.email, - phone: techVendors.phone, - status: techVendors.status, - techVendorType: techVendors.techVendorType, - matchedItemCodes: sql` - array_agg(DISTINCT ${techVendorPossibleItems.itemCode}) - `, - matchedItemCount: sql` - count(DISTINCT ${techVendorPossibleItems.itemCode}) - `, - }) - .from(techVendorPossibleItems) - .innerJoin(techVendors, eq(techVendorPossibleItems.vendorId, techVendors.id)) - .where( - and( - inArray(techVendorPossibleItems.itemCode, itemCodes), - eq(techVendors.status, "ACTIVE") - // 벤더 타입 필터링 임시 제거 - 데이터 확인 후 다시 추가 - // eq(techVendors.techVendorType, vendorTypeFilter) - ) - ) - .groupBy( - techVendorPossibleItems.vendorId, - techVendors.id, - techVendors.vendorName, - techVendors.vendorCode, - techVendors.country, - techVendors.email, - techVendors.phone, - techVendors.status, - techVendors.techVendorType - ) - .orderBy(desc(sql`count(DISTINCT ${techVendorPossibleItems.itemCode})`)); - - return { data: candidateVendors, error: null }; - }); - } catch (err) { - console.error("Error fetching candidate vendors:", err); - return { data: [], error: getErrorMessage(err) }; - } -} - -/** - * RFQ 타입에 따른 캐시 무효화 경로 반환 - */ -function getTechSalesRevalidationPath(rfqType: "SHIP" | "TOP" | "HULL"): string { - switch (rfqType) { - case "SHIP": - return "/evcp/budgetary-tech-sales-ship"; - case "TOP": - return "/evcp/budgetary-tech-sales-top"; - case "HULL": - return "/evcp/budgetary-tech-sales-hull"; - default: - return "/evcp/budgetary-tech-sales-ship"; - } -} - -/** - * 기술영업 RFQ에 여러 벤더 추가 (techVendors 기반) - * 벤더 추가 시에는 견적서를 생성하지 않고, RFQ 전송 시에 견적서를 생성 - */ -export async function addTechVendorsToTechSalesRfq(input: { - rfqId: number; - vendorIds: number[]; - createdBy: number; -}) { - unstable_noStore(); - - try { - return await db.transaction(async (tx) => { - const results = []; - const errors: string[] = []; - - // 1. RFQ 상태 및 타입 확인 - const rfq = await tx.query.techSalesRfqs.findFirst({ - where: eq(techSalesRfqs.id, input.rfqId), - columns: { - id: true, - status: true, - rfqType: true, - } - }); - - if (!rfq) { - throw new Error("RFQ를 찾을 수 없습니다"); - } - - // 2. 각 벤더에 대해 처리 (이미 추가된 벤더는 견적서가 있는지 확인) - for (const vendorId of input.vendorIds) { - try { - // 이미 추가된 벤더인지 확인 (견적서 존재 여부로 확인) - const existingQuotation = await tx.query.techSalesVendorQuotations.findFirst({ - where: and( - eq(techSalesVendorQuotations.rfqId, input.rfqId), - eq(techSalesVendorQuotations.vendorId, vendorId) - ) - }); - - if (existingQuotation) { - errors.push(`벤더 ID ${vendorId}는 이미 추가되어 있습니다.`); - continue; - } - - // 벤더가 실제로 존재하는지 확인 - const vendor = await tx.query.techVendors.findFirst({ - where: eq(techVendors.id, vendorId), - columns: { id: true, vendorName: true } - }); - - if (!vendor) { - errors.push(`벤더 ID ${vendorId}를 찾을 수 없습니다.`); - continue; - } - - // 🔥 중요: 벤더 추가 시에는 견적서를 생성하지 않고, "Assigned" 상태로만 생성 - // quotation_version은 null로 설정하여 벤더가 실제 견적 제출 시에만 리비전 생성 - const [quotation] = await tx - .insert(techSalesVendorQuotations) - .values({ - rfqId: input.rfqId, - vendorId: vendorId, - status: "Assigned", // Draft가 아닌 Assigned 상태로 생성 - quotationVersion: null, // 리비전은 견적 제출 시에만 생성 - createdBy: input.createdBy, - updatedBy: input.createdBy, - }) - .returning({ id: techSalesVendorQuotations.id }); - - // 🆕 RFQ의 아이템 코드들을 tech_vendor_possible_items에 추가 - try { - // RFQ의 아이템들 조회 - const rfqItemsResult = await getTechSalesRfqItems(input.rfqId); - - if (rfqItemsResult.data && rfqItemsResult.data.length > 0) { - const itemCodes = rfqItemsResult.data - .map(item => item.itemCode) - .filter(code => code); // 빈 코드 제외 - - // 각 아이템 코드에 대해 tech_vendor_possible_items에 추가 (중복 체크) - for (const itemCode of itemCodes) { - // 이미 존재하는지 확인 - const existing = await tx.query.techVendorPossibleItems.findFirst({ - where: and( - eq(techVendorPossibleItems.vendorId, vendorId), - eq(techVendorPossibleItems.itemCode, itemCode) - ) - }); - - // 존재하지 않으면 추가 - if (!existing) { - await tx.insert(techVendorPossibleItems).values({ - vendorId: vendorId, - itemCode: itemCode, - }); - } - } - } - } catch (possibleItemError) { - // tech_vendor_possible_items 추가 실패는 전체 실패로 처리하지 않음 - console.warn(`벤더 ${vendorId}의 가능 아이템 추가 실패:`, possibleItemError); - } - - results.push({ id: quotation.id, vendorId, vendorName: vendor.vendorName }); - } catch (vendorError) { - console.error(`Error adding vendor ${vendorId}:`, vendorError); - errors.push(`벤더 ID ${vendorId} 추가 중 오류가 발생했습니다.`); - } - } - - // 3. RFQ 상태가 "RFQ Created"이고 성공적으로 추가된 벤더가 있는 경우 상태 업데이트 - if (rfq.status === "RFQ Created" && results.length > 0) { - await tx.update(techSalesRfqs) - .set({ - status: "RFQ Vendor Assignned", - updatedBy: input.createdBy, - updatedAt: new Date() - }) - .where(eq(techSalesRfqs.id, input.rfqId)); - } - - // 캐시 무효화 (RFQ 타입에 따른 동적 경로) - revalidateTag("techSalesRfqs"); - revalidateTag("techSalesVendorQuotations"); - revalidateTag(`techSalesRfq-${input.rfqId}`); - revalidatePath(getTechSalesRevalidationPath(rfq.rfqType || "SHIP")); - - return { - data: results, - error: errors.length > 0 ? errors.join(", ") : null, - successCount: results.length, - errorCount: errors.length - }; - }); - } catch (err) { - console.error("Error adding tech vendors to RFQ:", err); - return { data: [], error: getErrorMessage(err) }; - } -} - -/** - * 기술영업 RFQ의 벤더 목록 조회 (techVendors 기반) - */ -export async function getTechSalesRfqTechVendors(rfqId: number) { - unstable_noStore(); - - try { - return await db.transaction(async (tx) => { - const vendors = await tx - .select({ - id: techSalesVendorQuotations.id, - vendorId: techVendors.id, - vendorName: techVendors.vendorName, - vendorCode: techVendors.vendorCode, - country: techVendors.country, - email: techVendors.email, - phone: techVendors.phone, - status: techSalesVendorQuotations.status, - totalPrice: techSalesVendorQuotations.totalPrice, - currency: techSalesVendorQuotations.currency, - validUntil: techSalesVendorQuotations.validUntil, - submittedAt: techSalesVendorQuotations.submittedAt, - createdAt: techSalesVendorQuotations.createdAt, - }) - .from(techSalesVendorQuotations) - .innerJoin(techVendors, eq(techSalesVendorQuotations.vendorId, techVendors.id)) - .where(eq(techSalesVendorQuotations.rfqId, rfqId)) - .orderBy(desc(techSalesVendorQuotations.createdAt)); - - return { data: vendors, error: null }; - }); - } catch (err) { - console.error("Error fetching RFQ tech vendors:", err); - return { data: [], error: getErrorMessage(err) }; - } -} - -/** - * 기술영업 RFQ에서 기술영업 벤더 제거 (techVendors 기반) - */ -export async function removeTechVendorFromTechSalesRfq(input: { - rfqId: number; - vendorId: number; -}) { - unstable_noStore(); - - try { - return await db.transaction(async (tx) => { - // 해당 벤더의 견적서 상태 확인 - const existingQuotation = await tx.query.techSalesVendorQuotations.findFirst({ - where: and( - eq(techSalesVendorQuotations.rfqId, input.rfqId), - eq(techSalesVendorQuotations.vendorId, input.vendorId) - ) - }); - - if (!existingQuotation) { - return { data: null, error: "해당 벤더가 이 RFQ에 존재하지 않습니다." }; - } - - // Assigned 상태가 아닌 경우 삭제 불가 - if (existingQuotation.status !== "Assigned") { - return { data: null, error: "Assigned 상태의 벤더만 삭제할 수 있습니다." }; - } - - // 해당 벤더의 견적서 삭제 - const [deletedQuotation] = await tx - .delete(techSalesVendorQuotations) - .where( - and( - eq(techSalesVendorQuotations.rfqId, input.rfqId), - eq(techSalesVendorQuotations.vendorId, input.vendorId) - ) - ) - .returning({ id: techSalesVendorQuotations.id }); - - // 캐시 무효화 - revalidateTag("techSalesRfqs"); - revalidateTag("techSalesVendorQuotations"); - - return { data: deletedQuotation, error: null }; - }); - } catch (err) { - console.error("Error removing tech vendor from RFQ:", err); - return { data: null, error: getErrorMessage(err) }; - } -} - -/** - * 기술영업 RFQ에서 여러 기술영업 벤더 제거 (techVendors 기반) - */ -export async function removeTechVendorsFromTechSalesRfq(input: { - rfqId: number; - vendorIds: number[]; -}) { - unstable_noStore(); - - try { - return await db.transaction(async (tx) => { - const results = []; - const errors: string[] = []; - - for (const vendorId of input.vendorIds) { - // 해당 벤더의 견적서 상태 확인 - const existingQuotation = await tx.query.techSalesVendorQuotations.findFirst({ - where: and( - eq(techSalesVendorQuotations.rfqId, input.rfqId), - eq(techSalesVendorQuotations.vendorId, vendorId) - ) - }); - - if (!existingQuotation) { - errors.push(`벤더 ID ${vendorId}가 이 RFQ에 존재하지 않습니다.`); - continue; - } - - // Assigned 상태가 아닌 경우 삭제 불가 - if (existingQuotation.status !== "Assigned") { - errors.push(`벤더 ID ${vendorId}는 Assigned 상태가 아니므로 삭제할 수 없습니다.`); - continue; - } - - // 해당 벤더의 견적서 삭제 - const [deletedQuotation] = await tx - .delete(techSalesVendorQuotations) - .where( - and( - eq(techSalesVendorQuotations.rfqId, input.rfqId), - eq(techSalesVendorQuotations.vendorId, vendorId) - ) - ) - .returning({ id: techSalesVendorQuotations.id }); - - results.push(deletedQuotation); - } - - // 캐시 무효화 - revalidateTag("techSalesRfqs"); - revalidateTag("techSalesVendorQuotations"); - - return { - data: results, - error: errors.length > 0 ? errors.join(", ") : null, - successCount: results.length, - errorCount: errors.length - }; - }); - } catch (err) { - console.error("Error removing tech vendors from RFQ:", err); - return { data: [], error: getErrorMessage(err) }; - } -} - -/** - * 기술영업 벤더 검색 - */ -export async function searchTechVendors(searchTerm: string, limit = 100, rfqType?: "SHIP" | "TOP" | "HULL") { - unstable_noStore(); - - try { - // RFQ 타입에 따른 벤더 타입 매핑 - const vendorTypeFilter = rfqType === "SHIP" ? "조선" : - rfqType === "TOP" ? "해양TOP" : - rfqType === "HULL" ? "해양HULL" : null; - - const whereConditions = [ - eq(techVendors.status, "ACTIVE"), - or( - ilike(techVendors.vendorName, `%${searchTerm}%`), - ilike(techVendors.vendorCode, `%${searchTerm}%`) - ) - ]; - - // RFQ 타입이 지정된 경우 벤더 타입 필터링 추가 (컴마 구분 문자열에서 검색) - if (vendorTypeFilter) { - whereConditions.push(sql`${techVendors.techVendorType} LIKE ${'%' + vendorTypeFilter + '%'}`); - } - - const results = await db - .select({ - id: techVendors.id, - vendorName: techVendors.vendorName, - vendorCode: techVendors.vendorCode, - status: techVendors.status, - country: techVendors.country, - techVendorType: techVendors.techVendorType, - }) - .from(techVendors) - .where(and(...whereConditions)) - .limit(limit) - .orderBy(techVendors.vendorName); - - return results; - } catch (err) { - console.error("Error searching tech vendors:", err); - throw new Error(getErrorMessage(err)); - } -} - - -/** - * 벤더 견적서 거절 처리 (벤더가 직접 거절) - */ -export async function rejectTechSalesVendorQuotations(input: { - quotationIds: number[]; - rejectionReason?: string; -}) { - try { - const session = await getServerSession(authOptions); - if (!session?.user?.id) { - throw new Error("인증이 필요합니다."); - } - - const result = await db.transaction(async (tx) => { - // 견적서들이 존재하고 벤더가 권한이 있는지 확인 - const quotations = await tx - .select({ - id: techSalesVendorQuotations.id, - status: techSalesVendorQuotations.status, - vendorId: techSalesVendorQuotations.vendorId, - }) - .from(techSalesVendorQuotations) - .where(inArray(techSalesVendorQuotations.id, input.quotationIds)); - - if (quotations.length !== input.quotationIds.length) { - throw new Error("일부 견적서를 찾을 수 없습니다."); - } - - // 이미 거절된 견적서가 있는지 확인 - const alreadyRejected = quotations.filter(q => q.status === "Rejected"); - if (alreadyRejected.length > 0) { - throw new Error("이미 거절된 견적서가 포함되어 있습니다."); - } - - // 승인된 견적서가 있는지 확인 - const alreadyAccepted = quotations.filter(q => q.status === "Accepted"); - if (alreadyAccepted.length > 0) { - throw new Error("이미 승인된 견적서는 거절할 수 없습니다."); - } - - // 견적서 상태를 거절로 변경 - await tx - .update(techSalesVendorQuotations) - .set({ - status: "Rejected", - rejectionReason: input.rejectionReason || null, - updatedBy: parseInt(session.user.id), - updatedAt: new Date(), - }) - .where(inArray(techSalesVendorQuotations.id, input.quotationIds)); - - return { success: true, updatedCount: quotations.length }; - }); - revalidateTag("techSalesRfqs"); - revalidateTag("techSalesVendorQuotations"); - revalidatePath("/partners/techsales/rfq-ship", "page"); - return { - success: true, - message: `${result.updatedCount}개의 견적서가 거절되었습니다.`, - data: result - }; - } catch (error) { - console.error("견적서 거절 오류:", error); - return { - success: false, - error: getErrorMessage(error) - }; - } -} - -// ==================== Revision 관련 ==================== - -/** - * 견적서 revision 히스토리 조회 - */ -export async function getTechSalesVendorQuotationRevisions(quotationId: number) { - try { - const revisions = await db - .select({ - id: techSalesVendorQuotationRevisions.id, - version: techSalesVendorQuotationRevisions.version, - snapshot: techSalesVendorQuotationRevisions.snapshot, - changeReason: techSalesVendorQuotationRevisions.changeReason, - revisionNote: techSalesVendorQuotationRevisions.revisionNote, - revisedBy: techSalesVendorQuotationRevisions.revisedBy, - revisedAt: techSalesVendorQuotationRevisions.revisedAt, - // 수정자 정보 조인 - revisedByName: users.name, - }) - .from(techSalesVendorQuotationRevisions) - .leftJoin(users, eq(techSalesVendorQuotationRevisions.revisedBy, users.id)) - .where(eq(techSalesVendorQuotationRevisions.quotationId, quotationId)) - .orderBy(desc(techSalesVendorQuotationRevisions.version)); - - return { data: revisions, error: null }; - } catch (error) { - console.error("견적서 revision 히스토리 조회 오류:", error); - return { data: null, error: "견적서 히스토리를 조회하는 중 오류가 발생했습니다." }; - } -} - -/** - * 견적서의 현재 버전과 revision 히스토리를 함께 조회 (각 리비전의 첨부파일 포함) - */ -export async function getTechSalesVendorQuotationWithRevisions(quotationId: number) { - try { - // 먼저 현재 견적서 조회 - const currentQuotation = await db.query.techSalesVendorQuotations.findFirst({ - where: eq(techSalesVendorQuotations.id, quotationId), - with: { - // 벤더 정보와 RFQ 정보도 함께 조회 (필요한 경우) - } - }); - - if (!currentQuotation) { - return { data: null, error: "견적서를 찾을 수 없습니다." }; - } - - // 이제 현재 견적서의 정보를 알고 있으므로 병렬로 나머지 정보 조회 - const [revisionsResult, currentAttachments] = await Promise.all([ - getTechSalesVendorQuotationRevisions(quotationId), - getTechSalesVendorQuotationAttachmentsByRevision(quotationId, currentQuotation.quotationVersion || 0) - ]); - - // 현재 견적서에 첨부파일 정보 추가 - const currentWithAttachments = { - ...currentQuotation, - attachments: currentAttachments.data || [] - }; - - // 각 리비전의 첨부파일 정보 추가 - const revisionsWithAttachments = await Promise.all( - (revisionsResult.data || []).map(async (revision) => { - const attachmentsResult = await getTechSalesVendorQuotationAttachmentsByRevision(quotationId, revision.version); - return { - ...revision, - attachments: attachmentsResult.data || [] - }; - }) - ); - - return { - data: { - current: currentWithAttachments, - revisions: revisionsWithAttachments - }, - error: null - }; - } catch (error) { - console.error("견적서 전체 히스토리 조회 오류:", error); - return { data: null, error: "견적서 정보를 조회하는 중 오류가 발생했습니다." }; - } -} - -/** - * 견적서 첨부파일 조회 (리비전 ID 기준 오름차순 정렬) - */ -export async function getTechSalesVendorQuotationAttachments(quotationId: number) { - return unstable_cache( - async () => { - try { - const attachments = await db - .select({ - id: techSalesVendorQuotationAttachments.id, - quotationId: techSalesVendorQuotationAttachments.quotationId, - revisionId: techSalesVendorQuotationAttachments.revisionId, - fileName: techSalesVendorQuotationAttachments.fileName, - originalFileName: techSalesVendorQuotationAttachments.originalFileName, - fileSize: techSalesVendorQuotationAttachments.fileSize, - fileType: techSalesVendorQuotationAttachments.fileType, - filePath: techSalesVendorQuotationAttachments.filePath, - description: techSalesVendorQuotationAttachments.description, - uploadedBy: techSalesVendorQuotationAttachments.uploadedBy, - vendorId: techSalesVendorQuotationAttachments.vendorId, - isVendorUpload: techSalesVendorQuotationAttachments.isVendorUpload, - createdAt: techSalesVendorQuotationAttachments.createdAt, - updatedAt: techSalesVendorQuotationAttachments.updatedAt, - }) - .from(techSalesVendorQuotationAttachments) - .where(eq(techSalesVendorQuotationAttachments.quotationId, quotationId)) - .orderBy(desc(techSalesVendorQuotationAttachments.createdAt)); - - return { data: attachments }; - } catch (error) { - console.error("견적서 첨부파일 조회 오류:", error); - return { error: "견적서 첨부파일 조회 중 오류가 발생했습니다." }; - } - }, - [`quotation-attachments-${quotationId}`], - { - revalidate: 60, - tags: [`quotation-${quotationId}`, "quotation-attachments"], - } - )(); -} - -/** - * 특정 리비전의 견적서 첨부파일 조회 - */ -export async function getTechSalesVendorQuotationAttachmentsByRevision(quotationId: number, revisionId: number) { - try { - const attachments = await db - .select({ - id: techSalesVendorQuotationAttachments.id, - quotationId: techSalesVendorQuotationAttachments.quotationId, - revisionId: techSalesVendorQuotationAttachments.revisionId, - fileName: techSalesVendorQuotationAttachments.fileName, - originalFileName: techSalesVendorQuotationAttachments.originalFileName, - fileSize: techSalesVendorQuotationAttachments.fileSize, - fileType: techSalesVendorQuotationAttachments.fileType, - filePath: techSalesVendorQuotationAttachments.filePath, - description: techSalesVendorQuotationAttachments.description, - uploadedBy: techSalesVendorQuotationAttachments.uploadedBy, - vendorId: techSalesVendorQuotationAttachments.vendorId, - isVendorUpload: techSalesVendorQuotationAttachments.isVendorUpload, - createdAt: techSalesVendorQuotationAttachments.createdAt, - updatedAt: techSalesVendorQuotationAttachments.updatedAt, - }) - .from(techSalesVendorQuotationAttachments) - .where(and( - eq(techSalesVendorQuotationAttachments.quotationId, quotationId), - eq(techSalesVendorQuotationAttachments.revisionId, revisionId) - )) - .orderBy(desc(techSalesVendorQuotationAttachments.createdAt)); - - return { data: attachments }; - } catch (error) { - console.error("리비전별 견적서 첨부파일 조회 오류:", error); - return { error: "첨부파일 조회 중 오류가 발생했습니다." }; - } -} - - -// ==================== Project AVL 관련 ==================== - -/** - * Accepted 상태의 Tech Sales Vendor Quotations 조회 (RFQ, Vendor 정보 포함) - */ -export async function getAcceptedTechSalesVendorQuotations(input: { - search?: string; - filters?: Filter[]; - sort?: { id: string; desc: boolean }[]; - page: number; - perPage: number; - rfqType?: "SHIP" | "TOP" | "HULL"; -}) { - unstable_noStore(); - - try { - const offset = (input.page - 1) * input.perPage; - - // 기본 WHERE 조건: status = 'Accepted'만 조회, rfqType이 'SHIP'이 아닌 것만 - const baseConditions = [ - eq(techSalesVendorQuotations.status, 'Accepted'), - sql`${techSalesRfqs.rfqType} != 'SHIP'` // 조선 RFQ 타입 제외 - ]; - - // 검색 조건 추가 - const searchConditions = []; - if (input.search) { - searchConditions.push( - ilike(techSalesRfqs.rfqCode, `%${input.search}%`), - ilike(techSalesRfqs.description, `%${input.search}%`), - ilike(sql`vendors.vendor_name`, `%${input.search}%`), - ilike(sql`vendors.vendor_code`, `%${input.search}%`) - ); - } - - // 정렬 조건 변환 - const orderByConditions: OrderByType[] = []; - if (input.sort?.length) { - input.sort.forEach((sortItem) => { - switch (sortItem.id) { - case "rfqCode": - orderByConditions.push(sortItem.desc ? desc(techSalesRfqs.rfqCode) : asc(techSalesRfqs.rfqCode)); - break; - case "description": - orderByConditions.push(sortItem.desc ? desc(techSalesRfqs.description) : asc(techSalesRfqs.description)); - break; - case "vendorName": - orderByConditions.push(sortItem.desc ? desc(sql`vendors.vendor_name`) : asc(sql`vendors.vendor_name`)); - break; - case "vendorCode": - orderByConditions.push(sortItem.desc ? desc(sql`vendors.vendor_code`) : asc(sql`vendors.vendor_code`)); - break; - case "totalPrice": - orderByConditions.push(sortItem.desc ? desc(techSalesVendorQuotations.totalPrice) : asc(techSalesVendorQuotations.totalPrice)); - break; - case "acceptedAt": - orderByConditions.push(sortItem.desc ? desc(techSalesVendorQuotations.acceptedAt) : asc(techSalesVendorQuotations.acceptedAt)); - break; - default: - orderByConditions.push(desc(techSalesVendorQuotations.acceptedAt)); - } - }); - } else { - orderByConditions.push(desc(techSalesVendorQuotations.acceptedAt)); - } - - // 필터 조건 추가 - const filterConditions = []; - if (input.filters?.length) { - const filterWhere = filterColumns({ - table: techSalesVendorQuotations, - filters: input.filters, - joinOperator: "and", - }); - if (filterWhere) { - filterConditions.push(filterWhere); - } - } - - // RFQ 타입 필터 - if (input.rfqType) { - filterConditions.push(eq(techSalesRfqs.rfqType, input.rfqType)); - } - - // 모든 조건 결합 - const allConditions = [ - ...baseConditions, - ...filterConditions, - ...(searchConditions.length > 0 ? [or(...searchConditions)] : []) - ]; - - const whereCondition = allConditions.length > 1 - ? and(...allConditions) - : allConditions[0]; - - // 데이터 조회 - const data = await db - .select({ - // Quotation 정보 - id: techSalesVendorQuotations.id, - rfqId: techSalesVendorQuotations.rfqId, - vendorId: techSalesVendorQuotations.vendorId, - quotationCode: techSalesVendorQuotations.quotationCode, - quotationVersion: techSalesVendorQuotations.quotationVersion, - totalPrice: techSalesVendorQuotations.totalPrice, - currency: techSalesVendorQuotations.currency, - validUntil: techSalesVendorQuotations.validUntil, - status: techSalesVendorQuotations.status, - remark: techSalesVendorQuotations.remark, - submittedAt: techSalesVendorQuotations.submittedAt, - acceptedAt: techSalesVendorQuotations.acceptedAt, - createdAt: techSalesVendorQuotations.createdAt, - updatedAt: techSalesVendorQuotations.updatedAt, - - // RFQ 정보 - rfqCode: techSalesRfqs.rfqCode, - rfqType: techSalesRfqs.rfqType, - description: techSalesRfqs.description, - dueDate: techSalesRfqs.dueDate, - rfqStatus: techSalesRfqs.status, - materialCode: techSalesRfqs.materialCode, - - // Vendor 정보 - vendorName: sql`vendors.vendor_name`, - vendorCode: sql`vendors.vendor_code`, - vendorEmail: sql`vendors.email`, - vendorCountry: sql`vendors.country`, - - // Project 정보 - projNm: biddingProjects.projNm, - pspid: biddingProjects.pspid, - sector: biddingProjects.sector, - }) - .from(techSalesVendorQuotations) - .leftJoin(techSalesRfqs, eq(techSalesVendorQuotations.rfqId, techSalesRfqs.id)) - .leftJoin(sql`vendors`, eq(techSalesVendorQuotations.vendorId, sql`vendors.id`)) - .leftJoin(biddingProjects, eq(techSalesRfqs.biddingProjectId, biddingProjects.id)) - .where(whereCondition) - .orderBy(...orderByConditions) - .limit(input.perPage) - .offset(offset); - - // 총 개수 조회 - const totalCount = await db - .select({ count: count() }) - .from(techSalesVendorQuotations) - .leftJoin(techSalesRfqs, eq(techSalesVendorQuotations.rfqId, techSalesRfqs.id)) - .leftJoin(sql`vendors`, eq(techSalesVendorQuotations.vendorId, sql`vendors.id`)) - .leftJoin(biddingProjects, eq(techSalesRfqs.biddingProjectId, biddingProjects.id)) - .where(whereCondition); - - const total = totalCount[0]?.count ?? 0; - const pageCount = Math.ceil(total / input.perPage); - - return { - data, - pageCount, - total, - }; - - } catch (error) { - console.error("getAcceptedTechSalesVendorQuotations 오류:", error); - throw new Error(`Accepted quotations 조회 실패: ${getErrorMessage(error)}`); - } -} - -export async function getBidProjects(pjtType: 'SHIP' | 'TOP' | 'HULL'): Promise { - try { - // 트랜잭션을 사용하여 프로젝트 데이터 조회 - const projectList = await db.transaction(async (tx) => { - // 기본 쿼리 구성 - const query = tx - .select({ - id: biddingProjects.id, - projectCode: biddingProjects.pspid, - projectName: biddingProjects.projNm, - pjtType: biddingProjects.pjtType, - }) - .from(biddingProjects) - .where(eq(biddingProjects.pjtType, pjtType)); - - const results = await query.orderBy(biddingProjects.id); - return results; - }); - - // Handle null projectName values and ensure pjtType is not null - const validProjectList = projectList.map(project => ({ - ...project, - projectName: project.projectName || '', // Replace null with empty string - pjtType: project.pjtType as "SHIP" | "TOP" | "HULL" // Type assertion since WHERE filters ensure non-null - })); - - return validProjectList; - } catch (error) { - console.error("프로젝트 목록 가져오기 실패:", error); - return []; // 오류 발생 시 빈 배열 반환 - } +'use server' + +import { unstable_noStore, revalidateTag, revalidatePath } from "next/cache"; +import db from "@/db/db"; +import { + techSalesRfqs, + techSalesVendorQuotations, + techSalesVendorQuotationRevisions, + techSalesAttachments, + techSalesVendorQuotationAttachments, + techSalesVendorQuotationContacts, + techSalesContactPossibleItems, + users, + techSalesRfqComments, + techSalesRfqItems, + biddingProjects +} from "@/db/schema"; +import { and, desc, eq, ilike, or, sql, inArray, count, asc } from "drizzle-orm"; +import { unstable_cache } from "@/lib/unstable-cache"; +import { filterColumns } from "@/lib/filter-columns"; +import { getErrorMessage } from "@/lib/handle-error"; +import type { Filter } from "@/types/table"; +import { + selectTechSalesRfqsWithJoin, + countTechSalesRfqsWithJoin, + selectTechSalesVendorQuotationsWithJoin, + countTechSalesVendorQuotationsWithJoin, + selectTechSalesDashboardWithJoin, + selectSingleTechSalesVendorQuotationWithJoin +} from "./repository"; +import { GetTechSalesRfqsSchema } from "./validations"; +import { getServerSession } from "next-auth/next"; +import { authOptions } from "@/app/api/auth/[...nextauth]/route"; +import { sendEmail } from "../mail/sendEmail"; +import { formatDate } from "../utils"; +import { techVendors, techVendorPossibleItems, techVendorContacts } from "@/db/schema/techVendors"; +import { deleteFile, saveDRMFile, saveFile } from "@/lib/file-stroage"; +import { decryptWithServerAction } from "@/components/drm/drmUtils"; + +// 정렬 타입 정의 +// 의도적으로 any 사용 - drizzle ORM의 orderBy 타입이 복잡함 +// eslint-disable-next-line @typescript-eslint/no-explicit-any +type OrderByType = any; + +export type Project = { + id: number; + projectCode: string; + projectName: string; + pjtType: "SHIP" | "TOP" | "HULL"; +} + +/** + * 연도별 순차 RFQ 코드 생성 함수 (다중 생성 지원) + * 형식: RFQ-YYYY-001, RFQ-YYYY-002, ... + */ +// eslint-disable-next-line @typescript-eslint/no-explicit-any +async function generateRfqCodes(tx: any, count: number, year?: number): Promise { + const currentYear = year || new Date().getFullYear(); + const yearPrefix = `RFQ-${currentYear}-`; + + // 해당 연도의 가장 최근 RFQ 코드 조회 + const latestRfq = await tx + .select({ rfqCode: techSalesRfqs.rfqCode }) + .from(techSalesRfqs) + .where(ilike(techSalesRfqs.rfqCode, `${yearPrefix}%`)) + .orderBy(desc(techSalesRfqs.rfqCode)) + .limit(1); + + let nextNumber = 1; + + if (latestRfq.length > 0) { + // 기존 코드에서 번호 추출 (RFQ-2024-001 -> 001) + const lastCode = latestRfq[0].rfqCode; + const numberPart = lastCode.split('-').pop(); + if (numberPart) { + const lastNumber = parseInt(numberPart, 10); + if (!isNaN(lastNumber)) { + nextNumber = lastNumber + 1; + } + } + } + + // 요청된 개수만큼 순차적으로 코드 생성 + const codes: string[] = []; + for (let i = 0; i < count; i++) { + const paddedNumber = (nextNumber + i).toString().padStart(3, '0'); + codes.push(`${yearPrefix}${paddedNumber}`); + } + + return codes; +} + + +/** + * 직접 조인을 사용하여 RFQ 데이터 조회하는 함수 + * 페이지네이션, 필터링, 정렬 등 지원 + */ +export async function getTechSalesRfqsWithJoin(input: GetTechSalesRfqsSchema & { rfqType?: "SHIP" | "TOP" | "HULL" }) { + return unstable_cache( + async () => { + try { + const offset = (input.page - 1) * input.perPage; + + // 기본 필터 처리 - RFQFilterBox에서 오는 필터 + const basicFilters = input.basicFilters || []; + const basicJoinOperator = input.basicJoinOperator || "and"; + // 고급 필터 처리 - 테이블의 DataTableFilterList에서 오는 필터 + const advancedFilters = input.filters || []; + const advancedJoinOperator = input.joinOperator || "and"; + + // 기본 필터 조건 생성 + let basicWhere; + if (basicFilters.length > 0) { + basicWhere = filterColumns({ + table: techSalesRfqs, + filters: basicFilters, + joinOperator: basicJoinOperator, + }); + } + + // 고급 필터 조건 생성 + let advancedWhere; + if (advancedFilters.length > 0) { + advancedWhere = filterColumns({ + table: techSalesRfqs, + filters: advancedFilters, + joinOperator: advancedJoinOperator, + }); + } + + // 전역 검색 조건 + let globalWhere; + if (input.search) { + const s = `%${input.search}%`; + globalWhere = or( + ilike(techSalesRfqs.rfqCode, s), + ilike(techSalesRfqs.materialCode, s), + ilike(techSalesRfqs.description, s), + ilike(techSalesRfqs.remark, s) + ); + } + + // 모든 조건 결합 + const whereConditions = []; + if (basicWhere) whereConditions.push(basicWhere); + if (advancedWhere) whereConditions.push(advancedWhere); + if (globalWhere) whereConditions.push(globalWhere); + + // 조건이 있을 때만 and() 사용 + const finalWhere = whereConditions.length > 0 + ? and(...whereConditions) + : undefined; + + // 정렬 기준 설정 + let orderBy: OrderByType[] = [desc(techSalesRfqs.createdAt)]; // 기본 정렬 + + if (input.sort?.length) { + // 안전하게 접근하여 정렬 기준 설정 + orderBy = input.sort.map(item => { + // TypeScript 에러 방지를 위한 타입 단언 + const sortField = item.id as string; + + switch (sortField) { + case 'id': + return item.desc ? desc(techSalesRfqs.id) : techSalesRfqs.id; + case 'rfqCode': + return item.desc ? desc(techSalesRfqs.rfqCode) : techSalesRfqs.rfqCode; + case 'materialCode': + return item.desc ? desc(techSalesRfqs.materialCode) : techSalesRfqs.materialCode; + case 'description': + return item.desc ? desc(techSalesRfqs.description) : techSalesRfqs.description; + case 'status': + return item.desc ? desc(techSalesRfqs.status) : techSalesRfqs.status; + case 'dueDate': + return item.desc ? desc(techSalesRfqs.dueDate) : techSalesRfqs.dueDate; + case 'rfqSendDate': + return item.desc ? desc(techSalesRfqs.rfqSendDate) : techSalesRfqs.rfqSendDate; + case 'remark': + return item.desc ? desc(techSalesRfqs.remark) : techSalesRfqs.remark; + case 'createdAt': + return item.desc ? desc(techSalesRfqs.createdAt) : techSalesRfqs.createdAt; + case 'updatedAt': + return item.desc ? desc(techSalesRfqs.updatedAt) : techSalesRfqs.updatedAt; + default: + return item.desc ? desc(techSalesRfqs.createdAt) : techSalesRfqs.createdAt; + } + }); + } + + // Repository 함수 호출 - rfqType 매개변수 추가 + return await db.transaction(async (tx) => { + const [data, total] = await Promise.all([ + selectTechSalesRfqsWithJoin(tx, { + where: finalWhere, + orderBy, + offset, + limit: input.perPage, + rfqType: input.rfqType, + }), + countTechSalesRfqsWithJoin(tx, finalWhere, input.rfqType), + ]); + + const pageCount = Math.ceil(Number(total) / input.perPage); + return { data, pageCount, total: Number(total) }; + }); + } catch (err) { + console.error("Error fetching RFQs with join:", err); + return { data: [], pageCount: 0, total: 0 }; + } + }, + [JSON.stringify(input)], + { + revalidate: 60, + tags: ["techSalesRfqs"], + } + )(); +} + +/** + * 직접 조인을 사용하여 벤더 견적서 조회하는 함수 + */ +export async function getTechSalesVendorQuotationsWithJoin(input: { + rfqId?: number; + vendorId?: number; + search?: string; + filters?: Filter[]; + sort?: { id: string; desc: boolean }[]; + page: number; + perPage: number; + rfqType?: "SHIP" | "TOP" | "HULL"; // rfqType 매개변수 추가 +}) { + return unstable_cache( + async () => { + try { + const offset = (input.page - 1) * input.perPage; + + // 기본 필터 조건들 + const whereConditions = []; + + // RFQ ID 필터 + if (input.rfqId) { + whereConditions.push(eq(techSalesVendorQuotations.rfqId, input.rfqId)); + } + + // 벤더 ID 필터 + if (input.vendorId) { + whereConditions.push(eq(techSalesVendorQuotations.vendorId, input.vendorId)); + } + + // 검색 조건 + if (input.search) { + const s = `%${input.search}%`; + const searchCondition = or( + ilike(techSalesVendorQuotations.currency, s), + ilike(techSalesVendorQuotations.status, s) + ); + if (searchCondition) { + whereConditions.push(searchCondition); + } + } + + // 고급 필터 처리 + if (input.filters && input.filters.length > 0) { + const filterWhere = filterColumns({ + table: techSalesVendorQuotations, + filters: input.filters as Filter[], + joinOperator: "and", + }); + if (filterWhere) { + whereConditions.push(filterWhere); + } + } + + // 최종 WHERE 조건 + const finalWhere = whereConditions.length > 0 + ? and(...whereConditions) + : undefined; + + // 정렬 기준 설정 + let orderBy: OrderByType[] = [desc(techSalesVendorQuotations.createdAt)]; + + if (input.sort?.length) { + orderBy = input.sort.map(item => { + switch (item.id) { + case 'id': + return item.desc ? desc(techSalesVendorQuotations.id) : techSalesVendorQuotations.id; + case 'status': + return item.desc ? desc(techSalesVendorQuotations.status) : techSalesVendorQuotations.status; + case 'currency': + return item.desc ? desc(techSalesVendorQuotations.currency) : techSalesVendorQuotations.currency; + case 'totalPrice': + return item.desc ? desc(techSalesVendorQuotations.totalPrice) : techSalesVendorQuotations.totalPrice; + case 'createdAt': + return item.desc ? desc(techSalesVendorQuotations.createdAt) : techSalesVendorQuotations.createdAt; + case 'updatedAt': + return item.desc ? desc(techSalesVendorQuotations.updatedAt) : techSalesVendorQuotations.updatedAt; + default: + return item.desc ? desc(techSalesVendorQuotations.createdAt) : techSalesVendorQuotations.createdAt; + } + }); + } + + // 트랜잭션 내부에서 Repository 호출 + const { data, total } = await db.transaction(async (tx) => { + const data = await selectTechSalesVendorQuotationsWithJoin(tx, { + where: finalWhere, + orderBy, + offset, + limit: input.perPage, + }); + + // 각 견적서의 첨부파일 정보 조회 + const dataWithAttachments = await Promise.all( + data.map(async (quotation) => { + const attachments = await db.query.techSalesVendorQuotationAttachments.findMany({ + where: eq(techSalesVendorQuotationAttachments.quotationId, quotation.id), + orderBy: [desc(techSalesVendorQuotationAttachments.createdAt)], + }); + + return { + ...quotation, + quotationAttachments: attachments.map(att => ({ + id: att.id, + fileName: att.fileName, + fileSize: att.fileSize, + filePath: att.filePath, + description: att.description, + })) + }; + }) + ); + + const total = await countTechSalesVendorQuotationsWithJoin(tx, finalWhere); + return { data: dataWithAttachments, total }; + }); + + const pageCount = Math.ceil(total / input.perPage); + + return { data, pageCount, total }; + } catch (err) { + console.error("Error fetching vendor quotations with join:", err); + return { data: [], pageCount: 0, total: 0 }; + } + }, + [JSON.stringify(input)], + { + revalidate: 60, + tags: [ + "techSalesVendorQuotations", + ...(input.rfqId ? [`techSalesRfq-${input.rfqId}`] : []) + ], + } + )(); +} + +/** + * 직접 조인을 사용하여 RFQ 대시보드 데이터 조회하는 함수 + */ +export async function getTechSalesDashboardWithJoin(input: { + search?: string; + filters?: Filter[]; + sort?: { id: string; desc: boolean }[]; + page: number; + perPage: number; + rfqType?: "SHIP" | "TOP" | "HULL"; // rfqType 매개변수 추가 +}) { + unstable_noStore(); // 대시보드는 항상 최신 데이터를 보여주기 위해 캐시하지 않음 + + try { + const offset = (input.page - 1) * input.perPage; + + // Advanced filtering + const advancedWhere = input.filters ? filterColumns({ + table: techSalesRfqs, + filters: input.filters as Filter[], + joinOperator: 'and', + }) : undefined; + + // Global search + let globalWhere; + if (input.search) { + const s = `%${input.search}%`; + globalWhere = or( + ilike(techSalesRfqs.rfqCode, s), + ilike(techSalesRfqs.materialCode, s), + ilike(techSalesRfqs.description, s) + ); + } + + const finalWhere = and( + advancedWhere, + globalWhere + ); + + // 정렬 기준 설정 + let orderBy: OrderByType[] = [desc(techSalesRfqs.updatedAt)]; // 기본 정렬 + + if (input.sort?.length) { + // 안전하게 접근하여 정렬 기준 설정 + orderBy = input.sort.map(item => { + switch (item.id) { + case 'id': + return item.desc ? desc(techSalesRfqs.id) : techSalesRfqs.id; + case 'rfqCode': + return item.desc ? desc(techSalesRfqs.rfqCode) : techSalesRfqs.rfqCode; + case 'status': + return item.desc ? desc(techSalesRfqs.status) : techSalesRfqs.status; + case 'dueDate': + return item.desc ? desc(techSalesRfqs.dueDate) : techSalesRfqs.dueDate; + case 'createdAt': + return item.desc ? desc(techSalesRfqs.createdAt) : techSalesRfqs.createdAt; + case 'updatedAt': + return item.desc ? desc(techSalesRfqs.updatedAt) : techSalesRfqs.updatedAt; + default: + return item.desc ? desc(techSalesRfqs.updatedAt) : techSalesRfqs.updatedAt; + } + }); + } + + // 트랜잭션 내부에서 Repository 호출 + const data = await db.transaction(async (tx) => { + return await selectTechSalesDashboardWithJoin(tx, { + where: finalWhere, + orderBy, + offset, + limit: input.perPage, + rfqType: input.rfqType, // rfqType 매개변수 추가 + }); + }); + + return { data, success: true }; + } catch (err) { + console.error("Error fetching dashboard data with join:", err); + return { data: [], success: false, error: getErrorMessage(err) }; + } +} + +/** + * 특정 RFQ의 벤더 목록 조회 + */ +export async function getTechSalesRfqVendors(rfqId: number) { + unstable_noStore(); + try { + // Repository 함수를 사용하여 벤더 견적 목록 조회 + const result = await getTechSalesVendorQuotationsWithJoin({ + rfqId, + page: 1, + perPage: 1000, // 충분히 큰 수로 설정하여 모든 벤더 조회 + }); + + return { data: result.data, error: null }; + } catch (err) { + console.error("Error fetching RFQ vendors:", err); + return { data: [], error: getErrorMessage(err) }; + } +} + +/** + * 기술영업 RFQ 발송 (선택된 벤더들의 선택된 contact들에게) + */ +export async function sendTechSalesRfqToVendors(input: { + rfqId: number; + vendorIds: number[]; + selectedContacts?: Array<{ + vendorId: number; + contactId: number; + contactEmail: string; + contactName: string; + }>; +}) { + unstable_noStore(); + try { + // 인증 확인 + const session = await getServerSession(authOptions); + + if (!session?.user) { + return { + success: false, + message: "인증이 필요합니다", + }; + } + + // RFQ 정보 조회 + const rfq = await db.query.techSalesRfqs.findFirst({ + where: eq(techSalesRfqs.id, input.rfqId), + columns: { + id: true, + rfqCode: true, + status: true, + dueDate: true, + rfqSendDate: true, + remark: true, + materialCode: true, + description: true, + rfqType: true, + }, + with: { + biddingProject: true, + createdByUser: { + columns: { + id: true, + name: true, + email: true, + } + } + } + }); + + if (!rfq) { + return { + success: false, + message: "RFQ를 찾을 수 없습니다", + }; + } + + // 발송 가능한 상태인지 확인 + if (rfq.status !== "RFQ Vendor Assignned" && rfq.status !== "RFQ Sent") { + return { + success: false, + message: "벤더가 할당된 RFQ 또는 이미 전송된 RFQ만 다시 전송할 수 있습니다", + }; + } + + const isResend = rfq.status === "RFQ Sent"; + + // 현재 사용자 정보 조회 + const sender = await db.query.users.findFirst({ + where: eq(users.id, Number(session.user.id)), + columns: { + id: true, + email: true, + name: true, + } + }); + + if (!sender || !sender.email) { + return { + success: false, + message: "보내는 사람의 이메일 정보를 찾을 수 없습니다", + }; + } + + // 선택된 벤더들의 견적서 정보 조회 + const vendorQuotations = await db.query.techSalesVendorQuotations.findMany({ + where: and( + eq(techSalesVendorQuotations.rfqId, input.rfqId), + inArray(techSalesVendorQuotations.vendorId, input.vendorIds) + ), + columns: { + id: true, + vendorId: true, + status: true, + currency: true, + }, + with: { + vendor: { + columns: { + id: true, + vendorName: true, + vendorCode: true, + } + } + } + }); + + if (vendorQuotations.length === 0) { + return { + success: false, + message: "선택된 벤더가 이 RFQ에 할당되어 있지 않습니다", + }; + } + + // 트랜잭션 시작 + await db.transaction(async (tx) => { + // 1. RFQ 상태 업데이트 (최초 발송인 경우 rfqSendDate 설정) + const updateData: Partial = { + status: "RFQ Sent", + sentBy: Number(session.user.id), + updatedBy: Number(session.user.id), + updatedAt: new Date(), + }; + + // rfqSendDate가 null인 경우에만 최초 전송일 설정 + if (!rfq.rfqSendDate) { + updateData.rfqSendDate = new Date(); + } + + await tx.update(techSalesRfqs) + .set(updateData) + .where(eq(techSalesRfqs.id, input.rfqId)); + + // 2. 선택된 벤더들의 견적서 상태를 "Assigned"에서 "Draft"로 변경 + for (const quotation of vendorQuotations) { + if (quotation.status === "Assigned") { + await tx.update(techSalesVendorQuotations) + .set({ + status: "Draft", + updatedBy: Number(session.user.id), + updatedAt: new Date(), + }) + .where(eq(techSalesVendorQuotations.id, quotation.id)); + } + } + + // 3. 각 벤더에 대해 이메일 발송 처리 + for (const quotation of vendorQuotations) { + if (!quotation.vendorId || !quotation.vendor) continue; + + let vendorEmailsString = ""; + + // contact 기반 발송 또는 기존 방식 (모든 벤더 사용자) + if (input.selectedContacts && input.selectedContacts.length > 0) { + // 선택된 contact들에게만 발송 + const vendorContacts = input.selectedContacts.filter( + contact => contact.vendorId === quotation.vendor!.id + ); + + if (vendorContacts.length > 0) { + vendorEmailsString = vendorContacts + .map(contact => contact.contactEmail) + .join(", "); + } + } else { + // 기존 방식: 벤더에 속한 모든 사용자에게 발송 + const vendorUsers = await db.query.users.findMany({ + where: eq(users.companyId, quotation.vendor.id), + columns: { + id: true, + email: true, + name: true, + language: true + } + }); + + vendorEmailsString = vendorUsers + .filter(user => user.email) + .map(user => user.email) + .join(", "); + } + + if (vendorEmailsString) { + // 대표 언어 결정 (기본값 한국어) + const language = "ko"; + + // RFQ 아이템 목록 조회 + const rfqItemsResult = await getTechSalesRfqItems(rfq.id); + const rfqItems = rfqItemsResult.data || []; + + // 이메일 컨텍스트 구성 + const emailContext = { + language: language, + rfq: { + id: rfq.id, + code: rfq.rfqCode, + title: rfqItems.length > 0 ? rfqItems.map(item => item.itemList).join(', ') : '', + projectCode: rfq.biddingProject?.pspid || '', + projectName: rfq.biddingProject?.projNm || '', + description: rfq.remark || '', + dueDate: rfq.dueDate ? formatDate(rfq.dueDate, "KR") : 'N/A', + materialCode: rfq.materialCode || '', + type: rfq.rfqType || 'SHIP', + }, + items: rfqItems.map(item => ({ + itemCode: item.itemCode, + itemList: item.itemList, + workType: item.workType, + shipTypes: item.shipTypes, + subItemList: item.subItemList, + itemType: item.itemType, + })), + vendor: { + id: quotation.vendor.id, + code: quotation.vendor.vendorCode || '', + name: quotation.vendor.vendorName, + }, + sender: { + fullName: sender.name || '', + email: sender.email, + }, + project: { + id: rfq.biddingProject?.pspid || '', + name: rfq.biddingProject?.projNm || '', + sector: rfq.biddingProject?.sector || '', + shipType: rfq.biddingProject?.ptypeNm || '', + shipCount: rfq.biddingProject?.projMsrm || 0, + ownerName: rfq.biddingProject?.kunnrNm || '', + className: rfq.biddingProject?.cls1Nm || '', + }, + details: { + currency: quotation.currency || 'USD', + }, + quotationCode: `${rfq.rfqCode}-${quotation.vendorId}`, + systemUrl: process.env.NEXT_PUBLIC_APP_URL || 'http://60.101.108.100/ko/partners', + isResend: isResend, + versionInfo: isResend ? '(재전송)' : '', + } + + + + // 이메일 전송 + await sendEmail({ + to: vendorEmailsString, + subject: isResend + ? `[기술영업 RFQ 재전송] ${rfq.rfqCode} - ${rfqItems.length > 0 ? rfqItems.map(item => item.itemList).join(', ') : '견적 요청'} ${emailContext.versionInfo}` + : `[기술영업 RFQ] ${rfq.rfqCode} - ${rfqItems.length > 0 ? rfqItems.map(item => item.itemList).join(', ') : '견적 요청'}`, + template: 'tech-sales-rfq-invite-ko', // 기술영업용 템플릿 + context: emailContext, + cc: sender.email, // 발신자를 CC에 추가 + }); + + // 4. 선택된 담당자 정보를 quotation_contacts 테이블에 저장 + if (input.selectedContacts && input.selectedContacts.length > 0) { + const vendorContacts = input.selectedContacts.filter( + contact => contact.vendorId === quotation.vendor!.id + ); + + for (const contact of vendorContacts) { + // quotation_contacts 중복 체크 + const existingQuotationContact = await tx.query.techSalesVendorQuotationContacts.findFirst({ + where: and( + eq(techSalesVendorQuotationContacts.quotationId, quotation.id), + eq(techSalesVendorQuotationContacts.contactId, contact.contactId) + ) + }); + + if (!existingQuotationContact) { + await tx.insert(techSalesVendorQuotationContacts).values({ + quotationId: quotation.id, + contactId: contact.contactId, + createdAt: new Date(), + updatedAt: new Date(), + }); + } + + // 5. 담당자별 아이템 매핑 정보 저장 (중복 방지) + for (const item of rfqItems) { + // tech_vendor_possible_items에서 해당 벤더의 아이템 찾기 + const vendorPossibleItem = await tx.query.techVendorPossibleItems.findFirst({ + where: and( + eq(techVendorPossibleItems.vendorId, quotation.vendor!.id), + eq(techVendorPossibleItems.itemCode, item.itemCode || '') + ) + }); + + if (vendorPossibleItem) { + // contact_possible_items 중복 체크 + const existingContactPossibleItem = await tx.query.techSalesContactPossibleItems.findFirst({ + where: and( + eq(techSalesContactPossibleItems.contactId, contact.contactId), + eq(techSalesContactPossibleItems.vendorPossibleItemId, vendorPossibleItem.id) + ) + }); + + if (!existingContactPossibleItem) { + await tx.insert(techSalesContactPossibleItems).values({ + contactId: contact.contactId, + vendorPossibleItemId: vendorPossibleItem.id, + createdAt: new Date(), + updatedAt: new Date(), + }); + } + } + } + } + } + } + } + }); + + // 캐시 무효화 + revalidateTag("techSalesRfqs"); + revalidateTag("techSalesVendorQuotations"); + revalidateTag(`techSalesRfq-${input.rfqId}`); + revalidatePath(getTechSalesRevalidationPath(rfq?.rfqType || "SHIP")); + + const sentContactCount = input.selectedContacts?.length || vendorQuotations.length; + const messageDetail = input.selectedContacts && input.selectedContacts.length > 0 + ? `${sentContactCount}명의 연락처에게 RFQ가 성공적으로 발송되었습니다` + : `${vendorQuotations.length}개 벤더에게 RFQ가 성공적으로 발송되었습니다`; + + return { + success: true, + message: messageDetail, + sentCount: sentContactCount, + }; + } catch (err) { + console.error("기술영업 RFQ 발송 오류:", err); + return { + success: false, + message: "RFQ 발송 중 오류가 발생했습니다", + }; + } +} + +/** + * 벤더용 기술영업 RFQ 견적서 조회 (withJoin 사용) + */ +export async function getTechSalesVendorQuotation(quotationId: number) { + unstable_noStore(); + try { + const quotation = await db.transaction(async (tx) => { + return await selectSingleTechSalesVendorQuotationWithJoin(tx, quotationId); + }); + + if (!quotation) { + return { data: null, error: "견적서를 찾을 수 없습니다." }; + } + + // RFQ 아이템 정보도 함께 조회 + const itemsResult = await getTechSalesRfqItems(quotation.rfqId); + const items = itemsResult.data || []; + + // 견적서 첨부파일 조회 + const quotationAttachments = await db.query.techSalesVendorQuotationAttachments.findMany({ + where: eq(techSalesVendorQuotationAttachments.quotationId, quotationId), + orderBy: [desc(techSalesVendorQuotationAttachments.createdAt)], + }); + + // 기존 구조와 호환되도록 데이터 재구성 + const formattedQuotation = { + id: quotation.id, + rfqId: quotation.rfqId, + vendorId: quotation.vendorId, + quotationCode: quotation.quotationCode, + quotationVersion: quotation.quotationVersion, + totalPrice: quotation.totalPrice, + currency: quotation.currency, + validUntil: quotation.validUntil, + status: quotation.status, + remark: quotation.remark, + rejectionReason: quotation.rejectionReason, + submittedAt: quotation.submittedAt, + acceptedAt: quotation.acceptedAt, + createdAt: quotation.createdAt, + updatedAt: quotation.updatedAt, + createdBy: quotation.createdBy, + updatedBy: quotation.updatedBy, + + // RFQ 정보 + rfq: { + id: quotation.rfqId, + rfqCode: quotation.rfqCode, + rfqType: quotation.rfqType, + status: quotation.rfqStatus, + dueDate: quotation.dueDate, + rfqSendDate: quotation.rfqSendDate, + materialCode: quotation.materialCode, + description: quotation.description, + remark: quotation.rfqRemark, + picCode: quotation.picCode, + createdBy: quotation.rfqCreatedBy, + biddingProjectId: quotation.biddingProjectId, + + // 아이템 정보 추가 + items: items, + + // 생성자 정보 + createdByUser: { + id: quotation.rfqCreatedBy, + name: quotation.rfqCreatedByName, + email: quotation.rfqCreatedByEmail, + }, + + // 프로젝트 정보 + biddingProject: quotation.biddingProjectId ? { + id: quotation.biddingProjectId, + pspid: quotation.pspid, + projNm: quotation.projNm, + sector: quotation.sector, + projMsrm: quotation.projMsrm, + ptypeNm: quotation.ptypeNm, + } : null, + }, + + // 벤더 정보 + vendor: { + id: quotation.vendorId, + vendorName: quotation.vendorName, + vendorCode: quotation.vendorCode, + country: quotation.vendorCountry, + email: quotation.vendorEmail, + phone: quotation.vendorPhone, + }, + + // 첨부파일 정보 + quotationAttachments: quotationAttachments.map(attachment => ({ + id: attachment.id, + fileName: attachment.fileName, + fileSize: attachment.fileSize, + filePath: attachment.filePath, + description: attachment.description, + })) + }; + + return { data: formattedQuotation, error: null }; + } catch (err) { + console.error("Error fetching vendor quotation:", err); + return { data: null, error: getErrorMessage(err) }; + } +} + +/** + * 기술영업 벤더 견적서 업데이트 (임시저장), + * 현재는 submit으로 처리, revision 을 아래의 함수로 사용가능함. + */ +export async function updateTechSalesVendorQuotation(data: { + id: number + currency: string + totalPrice: string + validUntil: Date + remark?: string + updatedBy: number + changeReason?: string +}) { + try { + return await db.transaction(async (tx) => { + // 현재 견적서 전체 데이터 조회 (revision 저장용) + const currentQuotation = await tx.query.techSalesVendorQuotations.findFirst({ + where: eq(techSalesVendorQuotations.id, data.id), + }); + + if (!currentQuotation) { + return { data: null, error: "견적서를 찾을 수 없습니다." }; + } + + // Accepted나 Rejected 상태가 아니면 수정 가능 + if (["Rejected"].includes(currentQuotation.status)) { + return { data: null, error: "승인되거나 거절된 견적서는 수정할 수 없습니다." }; + } + + // 실제 변경사항이 있는지 확인 + const hasChanges = + currentQuotation.currency !== data.currency || + currentQuotation.totalPrice !== data.totalPrice || + currentQuotation.validUntil?.getTime() !== data.validUntil.getTime() || + currentQuotation.remark !== (data.remark || null); + + if (!hasChanges) { + return { data: currentQuotation, error: null }; + } + + // 현재 버전을 revision history에 저장 + await tx.insert(techSalesVendorQuotationRevisions).values({ + quotationId: data.id, + version: currentQuotation.quotationVersion || 1, + snapshot: { + currency: currentQuotation.currency, + totalPrice: currentQuotation.totalPrice, + validUntil: currentQuotation.validUntil, + remark: currentQuotation.remark, + status: currentQuotation.status, + quotationVersion: currentQuotation.quotationVersion, + submittedAt: currentQuotation.submittedAt, + acceptedAt: currentQuotation.acceptedAt, + updatedAt: currentQuotation.updatedAt, + }, + changeReason: data.changeReason || "견적서 수정", + revisedBy: data.updatedBy, + }); + + // 새로운 버전으로 업데이트 + const result = await tx + .update(techSalesVendorQuotations) + .set({ + currency: data.currency, + totalPrice: data.totalPrice, + validUntil: data.validUntil, + remark: data.remark || null, + quotationVersion: (currentQuotation.quotationVersion || 1) + 1, + status: "Revised", // 수정된 상태로 변경 + updatedAt: new Date(), + }) + .where(eq(techSalesVendorQuotations.id, data.id)) + .returning(); + + return { data: result[0], error: null }; + }); + } catch (error) { + console.error("Error updating tech sales vendor quotation:", error); + return { data: null, error: "견적서 업데이트 중 오류가 발생했습니다" }; + } finally { + // 캐시 무효화 + revalidateTag("techSalesVendorQuotations"); + revalidatePath(`/partners/techsales/rfq-ship/${data.id}`); + } +} + +/** + * 기술영업 벤더 견적서 제출 + */ +export async function submitTechSalesVendorQuotation(data: { + id: number + currency: string + totalPrice: string + validUntil: Date + remark?: string + attachments?: Array<{ + fileName: string + originalFileName: string + filePath: string + fileSize: number + }> + updatedBy: number +}) { + try { + return await db.transaction(async (tx) => { + // 현재 견적서 전체 데이터 조회 (revision 저장용) + const currentQuotation = await tx.query.techSalesVendorQuotations.findFirst({ + where: eq(techSalesVendorQuotations.id, data.id), + }); + + if (!currentQuotation) { + return { data: null, error: "견적서를 찾을 수 없습니다." }; + } + + // Rejected 상태에서는 제출 불가 + if (["Rejected"].includes(currentQuotation.status)) { + return { data: null, error: "거절된 견적서는 제출할 수 없습니다." }; + } + + // // 실제 변경사항이 있는지 확인 + // const hasChanges = + // currentQuotation.currency !== data.currency || + // currentQuotation.totalPrice !== data.totalPrice || + // currentQuotation.validUntil?.getTime() !== data.validUntil.getTime() || + // currentQuotation.remark !== (data.remark || null); + + // // 변경사항이 있거나 처음 제출하는 경우 revision 저장 + // if (hasChanges || currentQuotation.status === "Draft") { + // await tx.insert(techSalesVendorQuotationRevisions).values({ + // quotationId: data.id, + // version: currentQuotation.quotationVersion || 1, + // snapshot: { + // currency: currentQuotation.currency, + // totalPrice: currentQuotation.totalPrice, + // validUntil: currentQuotation.validUntil, + // remark: currentQuotation.remark, + // status: currentQuotation.status, + // quotationVersion: currentQuotation.quotationVersion, + // submittedAt: currentQuotation.submittedAt, + // acceptedAt: currentQuotation.acceptedAt, + // updatedAt: currentQuotation.updatedAt, + // }, + // changeReason: "견적서 제출", + // revisedBy: data.updatedBy, + // }); + // } + + // 첫 제출인지 확인 (quotationVersion이 null인 경우) + const isFirstSubmission = currentQuotation.quotationVersion === null; + + // 첫 제출이 아닌 경우에만 revision 저장 (변경사항 이력 관리) + if (!isFirstSubmission) { + await tx.insert(techSalesVendorQuotationRevisions).values({ + quotationId: data.id, + version: currentQuotation.quotationVersion || 1, + snapshot: { + currency: currentQuotation.currency, + totalPrice: currentQuotation.totalPrice, + validUntil: currentQuotation.validUntil, + remark: currentQuotation.remark, + status: currentQuotation.status, + quotationVersion: currentQuotation.quotationVersion, + submittedAt: currentQuotation.submittedAt, + acceptedAt: currentQuotation.acceptedAt, + updatedAt: currentQuotation.updatedAt, + }, + changeReason: "견적서 제출", + revisedBy: data.updatedBy, + }); + } + + // 새로운 버전 번호 계산 (첫 제출은 1, 재제출은 1 증가) + const newRevisionId = isFirstSubmission ? 1 : (currentQuotation.quotationVersion || 1) + 1; + + // 새로운 버전으로 업데이트 + const result = await tx + .update(techSalesVendorQuotations) + .set({ + currency: data.currency, + totalPrice: data.totalPrice, + validUntil: data.validUntil, + remark: data.remark || null, + quotationVersion: newRevisionId, + status: "Submitted", + submittedAt: new Date(), + updatedAt: new Date(), + }) + .where(eq(techSalesVendorQuotations.id, data.id)) + .returning(); + + // 첨부파일 처리 (새로운 revisionId 사용) + if (data.attachments && data.attachments.length > 0) { + for (const attachment of data.attachments) { + await tx.insert(techSalesVendorQuotationAttachments).values({ + quotationId: data.id, + revisionId: newRevisionId, // 새로운 리비전 ID 사용 + fileName: attachment.fileName, // 해시된 파일명 (저장용) + originalFileName: attachment.originalFileName, // 원본 파일명 (표시용) + fileSize: attachment.fileSize, + filePath: attachment.filePath, + fileType: attachment.originalFileName.split('.').pop() || 'unknown', + uploadedBy: data.updatedBy, + isVendorUpload: true, + }); + } + } + + // 메일 발송 (백그라운드에서 실행) + if (result[0]) { + // 벤더에게 견적 제출 확인 메일 발송 + sendQuotationSubmittedNotificationToVendor(data.id).catch(error => { + console.error("벤더 견적 제출 확인 메일 발송 실패:", error); + }); + + // 담당자에게 견적 접수 알림 메일 발송 + sendQuotationSubmittedNotificationToManager(data.id).catch(error => { + console.error("담당자 견적 접수 알림 메일 발송 실패:", error); + }); + } + + return { data: result[0], error: null }; + }); + } catch (error) { + console.error("Error submitting tech sales vendor quotation:", error); + return { data: null, error: "견적서 제출 중 오류가 발생했습니다" }; + } finally { + // 캐시 무효화 + revalidateTag("techSalesVendorQuotations"); + revalidatePath(`/partners/techsales/rfq-ship`); + } +} + +/** + * 통화 목록 조회 + */ +export async function fetchCurrencies() { + try { + // 기본 통화 목록 (실제로는 DB에서 가져와야 함) + const currencies = [ + { code: "USD", name: "미국 달러" }, + { code: "KRW", name: "한국 원" }, + { code: "EUR", name: "유로" }, + { code: "JPY", name: "일본 엔" }, + { code: "CNY", name: "중국 위안" }, + ] + + return { data: currencies, error: null } + } catch (error) { + console.error("Error fetching currencies:", error) + return { data: null, error: "통화 목록 조회 중 오류가 발생했습니다" } + } +} + +/** + * 벤더용 기술영업 견적서 목록 조회 (페이지네이션 포함) + */ +export async function getVendorQuotations(input: { + flags?: string[]; + page: number; + perPage: number; + sort?: { id: string; desc: boolean }[]; + filters?: Filter[]; + joinOperator?: "and" | "or"; + basicFilters?: Filter[]; + basicJoinOperator?: "and" | "or"; + search?: string; + from?: string; + to?: string; + rfqType?: "SHIP" | "TOP" | "HULL"; +}, vendorId: string) { + return unstable_cache( + async () => { + try { + + + const { page, perPage, sort, filters = [], search = "", from = "", to = "" } = input; + const offset = (page - 1) * perPage; + const limit = perPage; + + // 기본 조건: 해당 벤더의 견적서만 조회 (Assigned 상태 제외) + const vendorIdNum = parseInt(vendorId); + if (isNaN(vendorIdNum)) { + console.error('❌ [getVendorQuotations] Invalid vendorId:', vendorId); + return { data: [], pageCount: 0, total: 0 }; + } + + const baseConditions = [ + eq(techSalesVendorQuotations.vendorId, vendorIdNum), + sql`${techSalesVendorQuotations.status} != 'Assigned'` // Assigned 상태 제외 + ]; + + // rfqType 필터링 추가 + if (input.rfqType) { + baseConditions.push(eq(techSalesRfqs.rfqType, input.rfqType)); + } + + // 검색 조건 추가 + if (search) { + const s = `%${search}%`; + const searchCondition = or( + ilike(techSalesVendorQuotations.currency, s), + ilike(techSalesVendorQuotations.status, s) + ); + if (searchCondition) { + baseConditions.push(searchCondition); + } + } + + // 날짜 범위 필터 + if (from) { + baseConditions.push(sql`${techSalesVendorQuotations.createdAt} >= ${from}`); + } + if (to) { + baseConditions.push(sql`${techSalesVendorQuotations.createdAt} <= ${to}`); + } + + // 고급 필터 처리 + if (filters.length > 0) { + const filterWhere = filterColumns({ + table: techSalesVendorQuotations, + filters: filters as Filter[], + joinOperator: input.joinOperator || "and", + }); + if (filterWhere) { + baseConditions.push(filterWhere); + } + } + + // 최종 WHERE 조건 + const finalWhere = baseConditions.length > 0 + ? and(...baseConditions) + : undefined; + + // 정렬 기준 설정 + let orderBy: OrderByType[] = [desc(techSalesVendorQuotations.updatedAt)]; + + if (sort?.length) { + orderBy = sort.map(item => { + switch (item.id) { + case 'id': + return item.desc ? desc(techSalesVendorQuotations.id) : techSalesVendorQuotations.id; + case 'status': + return item.desc ? desc(techSalesVendorQuotations.status) : techSalesVendorQuotations.status; + case 'currency': + return item.desc ? desc(techSalesVendorQuotations.currency) : techSalesVendorQuotations.currency; + case 'totalPrice': + return item.desc ? desc(techSalesVendorQuotations.totalPrice) : techSalesVendorQuotations.totalPrice; + case 'validUntil': + return item.desc ? desc(techSalesVendorQuotations.validUntil) : techSalesVendorQuotations.validUntil; + case 'submittedAt': + return item.desc ? desc(techSalesVendorQuotations.submittedAt) : techSalesVendorQuotations.submittedAt; + case 'createdAt': + return item.desc ? desc(techSalesVendorQuotations.createdAt) : techSalesVendorQuotations.createdAt; + case 'updatedAt': + return item.desc ? desc(techSalesVendorQuotations.updatedAt) : techSalesVendorQuotations.updatedAt; + case 'rfqCode': + return item.desc ? desc(techSalesRfqs.rfqCode) : techSalesRfqs.rfqCode; + case 'materialCode': + return item.desc ? desc(techSalesRfqs.materialCode) : techSalesRfqs.materialCode; + case 'dueDate': + return item.desc ? desc(techSalesRfqs.dueDate) : techSalesRfqs.dueDate; + case 'rfqStatus': + return item.desc ? desc(techSalesRfqs.status) : techSalesRfqs.status; + default: + return item.desc ? desc(techSalesVendorQuotations.updatedAt) : techSalesVendorQuotations.updatedAt; + } + }); + } + + // 조인을 포함한 데이터 조회 (중복 제거를 위해 techSalesAttachments JOIN 제거) + const data = await db + .select({ + id: techSalesVendorQuotations.id, + rfqId: techSalesVendorQuotations.rfqId, + vendorId: techSalesVendorQuotations.vendorId, + status: techSalesVendorQuotations.status, + currency: techSalesVendorQuotations.currency, + totalPrice: techSalesVendorQuotations.totalPrice, + validUntil: techSalesVendorQuotations.validUntil, + submittedAt: techSalesVendorQuotations.submittedAt, + remark: techSalesVendorQuotations.remark, + createdAt: techSalesVendorQuotations.createdAt, + updatedAt: techSalesVendorQuotations.updatedAt, + createdBy: techSalesVendorQuotations.createdBy, + updatedBy: techSalesVendorQuotations.updatedBy, + quotationCode: techSalesVendorQuotations.quotationCode, + quotationVersion: techSalesVendorQuotations.quotationVersion, + rejectionReason: techSalesVendorQuotations.rejectionReason, + acceptedAt: techSalesVendorQuotations.acceptedAt, + // RFQ 정보 + rfqCode: techSalesRfqs.rfqCode, + materialCode: techSalesRfqs.materialCode, + dueDate: techSalesRfqs.dueDate, + rfqStatus: techSalesRfqs.status, + description: techSalesRfqs.description, + // 프로젝트 정보 (직접 조인) + projNm: biddingProjects.projNm, + // 아이템 개수 + itemCount: sql`( + SELECT COUNT(*) + FROM tech_sales_rfq_items + WHERE tech_sales_rfq_items.rfq_id = ${techSalesRfqs.id} + )`, + // RFQ 첨부파일 개수 (RFQ_COMMON 타입만 카운트) + attachmentCount: sql`( + SELECT COUNT(*) + FROM tech_sales_attachments + WHERE tech_sales_attachments.tech_sales_rfq_id = ${techSalesRfqs.id} + AND tech_sales_attachments.attachment_type = 'RFQ_COMMON' + )`, + }) + .from(techSalesVendorQuotations) + .leftJoin(techSalesRfqs, eq(techSalesVendorQuotations.rfqId, techSalesRfqs.id)) + .leftJoin(biddingProjects, eq(techSalesRfqs.biddingProjectId, biddingProjects.id)) + .where(finalWhere) + .orderBy(...orderBy) + .limit(limit) + .offset(offset); + + // 총 개수 조회 + const totalResult = await db + .select({ count: sql`count(*)` }) + .from(techSalesVendorQuotations) + .leftJoin(techSalesRfqs, eq(techSalesVendorQuotations.rfqId, techSalesRfqs.id)) + .leftJoin(biddingProjects, eq(techSalesRfqs.biddingProjectId, biddingProjects.id)) + .where(finalWhere); + + const total = totalResult[0]?.count || 0; + const pageCount = Math.ceil(total / perPage); + + return { data, pageCount, total }; + } catch (err) { + console.error("Error fetching vendor quotations:", err); + return { data: [], pageCount: 0, total: 0 }; + } + }, + [JSON.stringify(input), vendorId], // 캐싱 키 + { + revalidate: 60, // 1분간 캐시 + tags: [ + "techSalesVendorQuotations", + `vendor-${vendorId}-quotations` + ], + } + )(); +} + +/** + * 기술영업 벤더 견적 승인 (벤더 선택) + */ +export async function acceptTechSalesVendorQuotation(quotationId: number) { + try { + const result = await db.transaction(async (tx) => { + // 1. 선택된 견적 정보 조회 + const selectedQuotation = await tx + .select() + .from(techSalesVendorQuotations) + .where(eq(techSalesVendorQuotations.id, quotationId)) + .limit(1) + + if (selectedQuotation.length === 0) { + throw new Error("견적을 찾을 수 없습니다") + } + + const quotation = selectedQuotation[0] + + // 2. 선택된 견적을 Accepted로 변경 + await tx + .update(techSalesVendorQuotations) + .set({ + status: "Accepted", + acceptedAt: new Date(), + updatedAt: new Date(), + }) + .where(eq(techSalesVendorQuotations.id, quotationId)) + + // 4. RFQ 상태를 Closed로 변경 + await tx + .update(techSalesRfqs) + .set({ + status: "Closed", + updatedAt: new Date(), + }) + .where(eq(techSalesRfqs.id, quotation.rfqId)) + + return quotation + }) + + // 메일 발송 (백그라운드에서 실행) + // 선택된 벤더에게 견적 선택 알림 메일 발송 + sendQuotationAcceptedNotification(quotationId).catch(error => { + console.error("벤더 견적 선택 알림 메일 발송 실패:", error); + }); + + // 캐시 무효화 + revalidateTag("techSalesVendorQuotations") + revalidateTag(`techSalesRfq-${result.rfqId}`) + revalidateTag("techSalesRfqs") + + // 해당 RFQ의 모든 벤더 캐시 무효화 (선택된 벤더와 거절된 벤더들) + const allVendorsInRfq = await db.query.techSalesVendorQuotations.findMany({ + where: eq(techSalesVendorQuotations.rfqId, result.rfqId), + columns: { vendorId: true } + }); + + for (const vendorQuotation of allVendorsInRfq) { + revalidateTag(`vendor-${vendorQuotation.vendorId}-quotations`); + } + revalidatePath("/evcp/budgetary-tech-sales-ship") + revalidatePath("/partners/techsales") + + + return { success: true, data: result } + } catch (error) { + console.error("벤더 견적 승인 오류:", error) + return { + success: false, + error: error instanceof Error ? error.message : "벤더 견적 승인에 실패했습니다" + } + } +} + +/** + * 기술영업 RFQ 첨부파일 생성 + */ +export async function createTechSalesRfqAttachments(params: { + techSalesRfqId: number + files: File[] + createdBy: number + attachmentType?: "RFQ_COMMON" | "VENDOR_SPECIFIC" + description?: string +}) { + unstable_noStore(); + try { + const { techSalesRfqId, files, createdBy, attachmentType = "RFQ_COMMON", description } = params; + + + + if (!files || files.length === 0) { + return { data: null, error: "업로드할 파일이 없습니다." }; + } + + // RFQ 존재 확인 + const rfq = await db.query.techSalesRfqs.findFirst({ + where: eq(techSalesRfqs.id, techSalesRfqId), + columns: { id: true, status: true } + }); + + if (!rfq) { + return { data: null, error: "RFQ를 찾을 수 없습니다." }; + } + + // // 편집 가능한 상태 확인 + // if (!["RFQ Created", "RFQ Vendor Assignned"].includes(rfq.status)) { + // return { data: null, error: "현재 상태에서는 첨부파일을 추가할 수 없습니다." }; + // } + + const results: typeof techSalesAttachments.$inferSelect[] = []; + + // 트랜잭션으로 처리 + await db.transaction(async (tx) => { + + for (const file of files) { + + + const saveResult = await saveDRMFile( + file, + decryptWithServerAction, + `techsales-rfq/${techSalesRfqId}` + ); + + if (!saveResult.success) { + throw new Error(saveResult.error || "파일 저장에 실패했습니다."); + } + + // DB에 첨부파일 레코드 생성 + const [newAttachment] = await tx.insert(techSalesAttachments).values({ + techSalesRfqId, + attachmentType, + fileName: saveResult.fileName!, + originalFileName: file.name, + filePath: saveResult.publicPath!, + fileSize: file.size, + fileType: file.type || undefined, + description: description || undefined, + createdBy, + }).returning(); + + results.push(newAttachment); + } + }); + + + + // RFQ 타입 조회하여 캐시 무효화 + const rfqType = await db.query.techSalesRfqs.findFirst({ + where: eq(techSalesRfqs.id, techSalesRfqId), + columns: { rfqType: true } + }); + + revalidateTag("techSalesRfqs"); + revalidateTag(`techSalesRfq-${techSalesRfqId}`); + revalidatePath(getTechSalesRevalidationPath(rfqType?.rfqType || "SHIP")); + revalidatePath("/partners/techsales"); + return { data: results, error: null }; + } catch (err) { + console.error("기술영업 RFQ 첨부파일 생성 오류:", err); + return { data: null, error: getErrorMessage(err) }; + } +} + +/** + * 기술영업 RFQ 첨부파일 조회 + */ +export async function getTechSalesRfqAttachments(techSalesRfqId: number) { + unstable_noStore(); + try { + const attachments = await db.query.techSalesAttachments.findMany({ + where: eq(techSalesAttachments.techSalesRfqId, techSalesRfqId), + orderBy: [desc(techSalesAttachments.createdAt)], + with: { + createdByUser: { + columns: { + id: true, + name: true, + email: true, + } + } + } + }); + + return { data: attachments, error: null }; + } catch (err) { + console.error("기술영업 RFQ 첨부파일 조회 오류:", err); + return { data: [], error: getErrorMessage(err) }; + } +} + +/** + * RFQ 첨부파일 타입별 조회 + */ +export async function getTechSalesRfqAttachmentsByType( + techSalesRfqId: number, + attachmentType: "RFQ_COMMON" | "VENDOR_SPECIFIC" | "TBE_RESULT" | "CBE_RESULT" +) { + unstable_noStore(); + try { + const attachments = await db.query.techSalesAttachments.findMany({ + where: and( + eq(techSalesAttachments.techSalesRfqId, techSalesRfqId), + eq(techSalesAttachments.attachmentType, attachmentType) + ), + orderBy: [desc(techSalesAttachments.createdAt)], + with: { + createdByUser: { + columns: { + id: true, + name: true, + email: true, + } + } + } + }); + + return { data: attachments, error: null }; + } catch (err) { + console.error(`기술영업 RFQ ${attachmentType} 첨부파일 조회 오류:`, err); + return { data: [], error: getErrorMessage(err) }; + } +} + +/** + * 기술영업 RFQ 첨부파일 삭제 + */ +export async function deleteTechSalesRfqAttachment(attachmentId: number) { + unstable_noStore(); + try { + // 첨부파일 정보 조회 + const attachment = await db.query.techSalesAttachments.findFirst({ + where: eq(techSalesAttachments.id, attachmentId), + }); + + if (!attachment) { + return { data: null, error: "첨부파일을 찾을 수 없습니다." }; + } + + // RFQ 상태 확인 + const rfq = await db.query.techSalesRfqs.findFirst({ + where: eq(techSalesRfqs.id, attachment.techSalesRfqId!), // Non-null assertion since we know it exists + columns: { id: true, status: true } + }); + + if (!rfq) { + return { data: null, error: "RFQ를 찾을 수 없습니다." }; + } + + // // 편집 가능한 상태 확인 + // if (!["RFQ Created", "RFQ Vendor Assignned"].includes(rfq.status)) { + // return { data: null, error: "현재 상태에서는 첨부파일을 삭제할 수 없습니다." }; + // } + + // 트랜잭션으로 처리 + const result = await db.transaction(async (tx) => { + // DB에서 레코드 삭제 + const deletedAttachment = await tx.delete(techSalesAttachments) + .where(eq(techSalesAttachments.id, attachmentId)) + .returning(); + + // 파일 시스템에서 파일 삭제 + try { + deleteFile(attachment.filePath) + + } catch (fileError) { + console.warn("파일 삭제 실패:", fileError); + // 파일 삭제 실패는 심각한 오류가 아니므로 계속 진행 + } + + return deletedAttachment[0]; + }); + + // RFQ 타입 조회하여 캐시 무효화 + const attachmentRfq = await db.query.techSalesRfqs.findFirst({ + where: eq(techSalesRfqs.id, attachment.techSalesRfqId!), + columns: { rfqType: true } + }); + + revalidateTag("techSalesRfqs"); + revalidateTag(`techSalesRfq-${attachment.techSalesRfqId}`); + revalidatePath(getTechSalesRevalidationPath(attachmentRfq?.rfqType || "SHIP")); + + return { data: result, error: null }; + } catch (err) { + console.error("기술영업 RFQ 첨부파일 삭제 오류:", err); + return { data: null, error: getErrorMessage(err) }; + } +} + +/** + * 기술영업 RFQ 첨부파일 일괄 처리 (업로드 + 삭제) + */ +export async function processTechSalesRfqAttachments(params: { + techSalesRfqId: number + newFiles: { file: File; attachmentType: "RFQ_COMMON" | "VENDOR_SPECIFIC" | "TBE_RESULT" | "CBE_RESULT"; description?: string }[] + deleteAttachmentIds: number[] + createdBy: number +}) { + unstable_noStore(); + try { + const { techSalesRfqId, newFiles, deleteAttachmentIds, createdBy } = params; + + + + // RFQ 존재 및 상태 확인 + const rfq = await db.query.techSalesRfqs.findFirst({ + where: eq(techSalesRfqs.id, techSalesRfqId), + columns: { id: true, status: true } + }); + + if (!rfq) { + return { data: null, error: "RFQ를 찾을 수 없습니다." }; + } + // // 편집 가능한 상태 확인 + // if (!["RFQ Created", "RFQ Vendor Assignned"].includes(rfq.status)) { + // return { data: null, error: "현재 상태에서는 첨부파일을 수정할 수 없습니다." }; + // } + + const results = { + uploaded: [] as typeof techSalesAttachments.$inferSelect[], + deleted: [] as typeof techSalesAttachments.$inferSelect[], + }; + + await db.transaction(async (tx) => { + + // 1. 삭제할 첨부파일 처리 + if (deleteAttachmentIds.length > 0) { + const attachmentsToDelete = await tx.query.techSalesAttachments.findMany({ + where: sql`${techSalesAttachments.id} IN (${deleteAttachmentIds.join(',')})` + }); + + for (const attachment of attachmentsToDelete) { + // DB에서 레코드 삭제 + const [deletedAttachment] = await tx.delete(techSalesAttachments) + .where(eq(techSalesAttachments.id, attachment.id)) + .returning(); + + results.deleted.push(deletedAttachment); + await deleteFile(attachment.filePath); + } + } + + // 2. 새 파일 업로드 처리 + if (newFiles.length > 0) { + for (const { file, attachmentType, description } of newFiles) { + const saveResult = await saveDRMFile( + file, + decryptWithServerAction, + `techsales-rfq/${techSalesRfqId}` + ); + + if (!saveResult.success) { + throw new Error(saveResult.error || "파일 저장에 실패했습니다."); + } + + // DB에 첨부파일 레코드 생성 + const [newAttachment] = await tx.insert(techSalesAttachments).values({ + techSalesRfqId, + attachmentType, + fileName: saveResult.fileName!, + originalFileName: file.name, + filePath: saveResult.publicPath!, + fileSize: file.size, + fileType: file.type || undefined, + description: description || undefined, + createdBy, + }).returning(); + + results.uploaded.push(newAttachment); + } + } + }); + + + + // 캐시 무효화 + revalidateTag("techSalesRfqs"); + revalidateTag(`techSalesRfq-${techSalesRfqId}`); + revalidatePath("/evcp/budgetary-tech-sales-ship"); + + return { + data: results, + error: null, + message: `${results.uploaded.length}개 업로드, ${results.deleted.length}개 삭제 완료` + }; + } catch (err) { + console.error("기술영업 RFQ 첨부파일 일괄 처리 오류:", err); + return { data: null, error: getErrorMessage(err) }; + } +} + +// ======================================== +// 메일 발송 관련 함수들 +// ======================================== + +/** + * 벤더 견적 제출 확인 메일 발송 (벤더용) + */ +export async function sendQuotationSubmittedNotificationToVendor(quotationId: number) { + try { + // 견적서 정보 조회 (projectSeries 조인 추가) + const quotation = await db.query.techSalesVendorQuotations.findFirst({ + where: eq(techSalesVendorQuotations.id, quotationId), + with: { + rfq: { + with: { + biddingProject: true, + createdByUser: { + columns: { + id: true, + name: true, + email: true, + } + } + } + }, + vendor: { + columns: { + id: true, + vendorName: true, + vendorCode: true, + } + } + } + }); + + if (!quotation || !quotation.rfq || !quotation.vendor) { + console.error("견적서 또는 관련 정보를 찾을 수 없습니다"); + return { success: false, error: "견적서 정보를 찾을 수 없습니다" }; + } + + // 벤더 사용자들 조회 + const vendorUsers = await db.query.users.findMany({ + where: eq(users.companyId, quotation.vendor.id), + columns: { + id: true, + email: true, + name: true, + language: true + } + }); + + const vendorEmails = vendorUsers + .filter(user => user.email) + .map(user => user.email) + .join(", "); + + if (!vendorEmails) { + console.warn(`벤더 ID ${quotation.vendor.id}에 등록된 이메일 주소가 없습니다`); + return { success: false, error: "벤더 이메일 주소가 없습니다" }; + } + + // RFQ 아이템 정보 조회 + const rfqItemsResult = await getTechSalesRfqItems(quotation.rfq.id); + const rfqItems = rfqItemsResult.data || []; + + // 이메일 컨텍스트 구성 (시리즈 정보 제거, 프로젝트 정보 간소화) + const emailContext = { + language: vendorUsers[0]?.language || "ko", + quotation: { + id: quotation.id, + currency: quotation.currency, + totalPrice: quotation.totalPrice, + validUntil: quotation.validUntil, + submittedAt: quotation.submittedAt, + remark: quotation.remark, + }, + rfq: { + id: quotation.rfq.id, + code: quotation.rfq.rfqCode, + title: quotation.rfq.description || '', + projectCode: quotation.rfq.biddingProject?.pspid || '', + projectName: quotation.rfq.biddingProject?.projNm || '', + dueDate: quotation.rfq.dueDate, + materialCode: quotation.rfq.materialCode, + description: quotation.rfq.remark, + }, + items: rfqItems.map(item => ({ + itemCode: item.itemCode, + itemList: item.itemList, + workType: item.workType, + shipTypes: item.shipTypes, + subItemList: item.subItemList, + itemType: item.itemType, + })), + vendor: { + id: quotation.vendor.id, + code: quotation.vendor.vendorCode, + name: quotation.vendor.vendorName, + }, + project: { + name: quotation.rfq.biddingProject?.projNm || '', + sector: quotation.rfq.biddingProject?.sector || '', + shipCount: quotation.rfq.biddingProject?.projMsrm ? Number(quotation.rfq.biddingProject.projMsrm) : 0, + ownerName: quotation.rfq.biddingProject?.kunnrNm || '', + className: quotation.rfq.biddingProject?.cls1Nm || '', + }, + manager: { + name: quotation.rfq.createdByUser?.name || '', + email: quotation.rfq.createdByUser?.email || '', + }, + systemUrl: process.env.NEXT_PUBLIC_APP_URL || 'http://60.101.108.100/ko/partners', + companyName: 'Samsung Heavy Industries', + year: new Date().getFullYear(), + }; + + // 이메일 발송 + await sendEmail({ + to: vendorEmails, + subject: `[견적 제출 확인] ${quotation.rfq.rfqCode} - 견적 요청`, + template: 'tech-sales-quotation-submitted-vendor-ko', + context: emailContext, + }); + + console.log(`벤더 견적 제출 확인 메일 발송 완료: ${vendorEmails}`); + return { success: true }; + } catch (error) { + console.error("벤더 견적 제출 확인 메일 발송 오류:", error); + return { success: false, error: "메일 발송 중 오류가 발생했습니다" }; + } +} + +/** + * 벤더 견적 접수 알림 메일 발송 (담당자용) + */ +export async function sendQuotationSubmittedNotificationToManager(quotationId: number) { + try { + // 견적서 정보 조회 + const quotation = await db.query.techSalesVendorQuotations.findFirst({ + where: eq(techSalesVendorQuotations.id, quotationId), + with: { + rfq: { + with: { + biddingProject: true, + createdByUser: { + columns: { + id: true, + name: true, + email: true, + } + } + } + }, + vendor: { + columns: { + id: true, + vendorName: true, + vendorCode: true, + } + } + } + }); + + if (!quotation || !quotation.rfq || !quotation.vendor) { + console.error("견적서 또는 관련 정보를 찾을 수 없습니다"); + return { success: false, error: "견적서 정보를 찾을 수 없습니다" }; + } + + const manager = quotation.rfq.createdByUser; + if (!manager?.email) { + console.warn("담당자 이메일 주소가 없습니다"); + return { success: false, error: "담당자 이메일 주소가 없습니다" }; + } + + // RFQ 아이템 정보 조회 + const rfqItemsResult = await getTechSalesRfqItems(quotation.rfq.id); + const rfqItems = rfqItemsResult.data || []; + + // 이메일 컨텍스트 구성 (시리즈 정보 제거, 프로젝트 정보 간소화) + const emailContext = { + language: "ko", + quotation: { + id: quotation.id, + currency: quotation.currency, + totalPrice: quotation.totalPrice, + validUntil: quotation.validUntil, + submittedAt: quotation.submittedAt, + remark: quotation.remark, + }, + rfq: { + id: quotation.rfq.id, + code: quotation.rfq.rfqCode, + title: quotation.rfq.description || '', + projectCode: quotation.rfq.biddingProject?.pspid || '', + projectName: quotation.rfq.biddingProject?.projNm || '', + dueDate: quotation.rfq.dueDate, + materialCode: quotation.rfq.materialCode, + description: quotation.rfq.remark, + }, + items: rfqItems.map(item => ({ + itemCode: item.itemCode, + itemList: item.itemList, + workType: item.workType, + shipTypes: item.shipTypes, + subItemList: item.subItemList, + itemType: item.itemType, + })), + vendor: { + id: quotation.vendor.id, + code: quotation.vendor.vendorCode, + name: quotation.vendor.vendorName, + }, + project: { + name: quotation.rfq.biddingProject?.projNm || '', + sector: quotation.rfq.biddingProject?.sector || '', + shipCount: quotation.rfq.biddingProject?.projMsrm ? Number(quotation.rfq.biddingProject.projMsrm) : 0, + ownerName: quotation.rfq.biddingProject?.kunnrNm || '', + className: quotation.rfq.biddingProject?.cls1Nm || '', + }, + manager: { + name: manager.name || '', + email: manager.email, + }, + systemUrl: process.env.NEXT_PUBLIC_APP_URL || 'http://60.101.108.100/ko/evcp', + companyName: 'Samsung Heavy Industries', + year: new Date().getFullYear(), + }; + + // 이메일 발송 + await sendEmail({ + to: manager.email, + subject: `[견적 접수 알림] ${quotation.vendor.vendorName}에서 ${quotation.rfq.rfqCode} 견적서를 제출했습니다`, + template: 'tech-sales-quotation-submitted-manager-ko', + context: emailContext, + }); + + console.log(`담당자 견적 접수 알림 메일 발송 완료: ${manager.email}`); + return { success: true }; + } catch (error) { + console.error("담당자 견적 접수 알림 메일 발송 오류:", error); + return { success: false, error: "메일 발송 중 오류가 발생했습니다" }; + } +} + +/** + * 벤더 견적 선택 알림 메일 발송 + */ +export async function sendQuotationAcceptedNotification(quotationId: number) { + try { + // 견적서 정보 조회 + const quotation = await db.query.techSalesVendorQuotations.findFirst({ + where: eq(techSalesVendorQuotations.id, quotationId), + with: { + rfq: { + with: { + biddingProject: true, + createdByUser: { + columns: { + id: true, + name: true, + email: true, + } + } + } + }, + vendor: { + columns: { + id: true, + vendorName: true, + vendorCode: true, + } + } + } + }); + + if (!quotation || !quotation.rfq || !quotation.vendor) { + console.error("견적서 또는 관련 정보를 찾을 수 없습니다"); + return { success: false, error: "견적서 정보를 찾을 수 없습니다" }; + } + + // 벤더 사용자들 조회 + const vendorUsers = await db.query.users.findMany({ + where: eq(users.companyId, quotation.vendor.id), + columns: { + id: true, + email: true, + name: true, + language: true + } + }); + + const vendorEmails = vendorUsers + .filter(user => user.email) + .map(user => user.email) + .join(", "); + + if (!vendorEmails) { + console.warn(`벤더 ID ${quotation.vendor.id}에 등록된 이메일 주소가 없습니다`); + return { success: false, error: "벤더 이메일 주소가 없습니다" }; + } + + // RFQ 아이템 정보 조회 + const rfqItemsResult = await getTechSalesRfqItems(quotation.rfq.id); + const rfqItems = rfqItemsResult.data || []; + + // 이메일 컨텍스트 구성 (시리즈 정보 제거, 프로젝트 정보 간소화) + const emailContext = { + language: vendorUsers[0]?.language || "ko", + quotation: { + id: quotation.id, + currency: quotation.currency, + totalPrice: quotation.totalPrice, + validUntil: quotation.validUntil, + acceptedAt: quotation.acceptedAt, + remark: quotation.remark, + }, + rfq: { + id: quotation.rfq.id, + code: quotation.rfq.rfqCode, + title: quotation.rfq.description || '', + projectCode: quotation.rfq.biddingProject?.pspid || '', + projectName: quotation.rfq.biddingProject?.projNm || '', + dueDate: quotation.rfq.dueDate, + materialCode: quotation.rfq.materialCode, + description: quotation.rfq.remark, + }, + items: rfqItems.map(item => ({ + itemCode: item.itemCode, + itemList: item.itemList, + workType: item.workType, + shipTypes: item.shipTypes, + subItemList: item.subItemList, + itemType: item.itemType, + })), + vendor: { + id: quotation.vendor.id, + code: quotation.vendor.vendorCode, + name: quotation.vendor.vendorName, + }, + project: { + name: quotation.rfq.biddingProject?.projNm || '', + sector: quotation.rfq.biddingProject?.sector || '', + shipCount: quotation.rfq.biddingProject?.projMsrm ? Number(quotation.rfq.biddingProject.projMsrm) : 0, + ownerName: quotation.rfq.biddingProject?.kunnrNm || '', + className: quotation.rfq.biddingProject?.cls1Nm || '', + }, + manager: { + name: quotation.rfq.createdByUser?.name || '', + email: quotation.rfq.createdByUser?.email || '', + }, + systemUrl: process.env.NEXT_PUBLIC_APP_URL || 'http://60.101.108.100/ko/partners', + companyName: 'Samsung Heavy Industries', + year: new Date().getFullYear(), + }; + + // 이메일 발송 + await sendEmail({ + to: vendorEmails, + subject: `[견적 선택 알림] ${quotation.rfq.rfqCode} - 귀하의 견적이 선택되었습니다`, + template: 'tech-sales-quotation-accepted-ko', + context: emailContext, + }); + + console.log(`벤더 견적 선택 알림 메일 발송 완료: ${vendorEmails}`); + return { success: true }; + } catch (error) { + console.error("벤더 견적 선택 알림 메일 발송 오류:", error); + return { success: false, error: "메일 발송 중 오류가 발생했습니다" }; + } +} + +// ==================== Vendor Communication 관련 ==================== + +export interface TechSalesAttachment { + id: number + fileName: string + fileSize: number + fileType: string | null // <- null 허용 + filePath: string + uploadedAt: Date +} + +export interface TechSalesComment { + id: number + rfqId: number + vendorId: number | null // null 허용으로 변경 + userId?: number | null // null 허용으로 변경 + content: string + isVendorComment: boolean | null // null 허용으로 변경 + createdAt: Date + updatedAt: Date + userName?: string | null // null 허용으로 변경 + vendorName?: string | null // null 허용으로 변경 + attachments: TechSalesAttachment[] + isRead: boolean | null // null 허용으로 변경 +} + +/** + * 특정 RFQ의 벤더별 읽지 않은 메시지 개수를 조회하는 함수 + * + * @param rfqId RFQ ID + * @returns 벤더별 읽지 않은 메시지 개수 (vendorId: count) + */ +export async function getTechSalesUnreadMessageCounts(rfqId: number): Promise> { + try { + // 벤더가 보낸 읽지 않은 메시지를 벤더별로 카운트 + const unreadCounts = await db + .select({ + vendorId: techSalesRfqComments.vendorId, + count: sql`count(*)`, + }) + .from(techSalesRfqComments) + .where( + and( + eq(techSalesRfqComments.rfqId, rfqId), + eq(techSalesRfqComments.isVendorComment, true), // 벤더가 보낸 메시지 + eq(techSalesRfqComments.isRead, false), // 읽지 않은 메시지 + sql`${techSalesRfqComments.vendorId} IS NOT NULL` // vendorId가 null이 아닌 것 + ) + ) + .groupBy(techSalesRfqComments.vendorId); + + // Record 형태로 변환 + const result: Record = {}; + unreadCounts.forEach(item => { + if (item.vendorId) { + result[item.vendorId] = item.count; + } + }); + + return result; + } catch (error) { + console.error('techSales 읽지 않은 메시지 개수 조회 오류:', error); + return {}; + } +} + +/** + * 특정 RFQ와 벤더 간의 커뮤니케이션 메시지를 가져오는 서버 액션 + * + * @param rfqId RFQ ID + * @param vendorId 벤더 ID + * @returns 코멘트 목록 + */ +export async function fetchTechSalesVendorComments(rfqId: number, vendorId?: number): Promise { + if (!vendorId) { + return [] + } + + try { + // 인증 확인 + const session = await getServerSession(authOptions); + + if (!session?.user) { + throw new Error("인증이 필요합니다") + } + + // 코멘트 쿼리 + const comments = await db.query.techSalesRfqComments.findMany({ + where: and( + eq(techSalesRfqComments.rfqId, rfqId), + eq(techSalesRfqComments.vendorId, vendorId) + ), + orderBy: [techSalesRfqComments.createdAt], + with: { + user: { + columns: { + name: true + } + }, + vendor: { + columns: { + vendorName: true + } + }, + attachments: true, + } + }) + + // 결과 매핑 + return comments.map(comment => ({ + id: comment.id, + rfqId: comment.rfqId, + vendorId: comment.vendorId, + userId: comment.userId || undefined, + content: comment.content, + isVendorComment: comment.isVendorComment, + createdAt: comment.createdAt, + updatedAt: comment.updatedAt, + userName: comment.user?.name, + vendorName: comment.vendor?.vendorName, + isRead: comment.isRead, + attachments: comment.attachments.map(att => ({ + id: att.id, + fileName: att.fileName, + fileSize: att.fileSize, + fileType: att.fileType, + filePath: att.filePath, + originalFileName: att.originalFileName, + uploadedAt: att.uploadedAt + })) + })) + } catch (error) { + console.error('techSales 벤더 코멘트 가져오기 오류:', error) + throw error + } +} + +/** + * 코멘트를 읽음 상태로 표시하는 서버 액션 + * + * @param rfqId RFQ ID + * @param vendorId 벤더 ID + */ +export async function markTechSalesMessagesAsRead(rfqId: number, vendorId?: number): Promise { + if (!vendorId) { + return + } + + try { + // 인증 확인 + const session = await getServerSession(authOptions); + + if (!session?.user) { + throw new Error("인증이 필요합니다") + } + + // 벤더가 작성한 읽지 않은 코멘트 업데이트 + await db.update(techSalesRfqComments) + .set({ isRead: true }) + .where( + and( + eq(techSalesRfqComments.rfqId, rfqId), + eq(techSalesRfqComments.vendorId, vendorId), + eq(techSalesRfqComments.isVendorComment, true), + eq(techSalesRfqComments.isRead, false) + ) + ) + + // 캐시 무효화 + revalidateTag(`tech-sales-rfq-${rfqId}-comments`) + } catch (error) { + console.error('techSales 메시지 읽음 표시 오류:', error) + throw error + } +} + +// ==================== RFQ 조선/해양 관련 ==================== + +/** + * 기술영업 조선 RFQ 생성 (1:N 관계) + */ +export async function createTechSalesShipRfq(input: { + biddingProjectId: number; + itemIds: number[]; // 조선 아이템 ID 배열 + dueDate: Date; + description?: string; + createdBy: number; +}) { + unstable_noStore(); + try { + return await db.transaction(async (tx) => { + // 프로젝트 정보 조회 (유효성 검증) + const biddingProject = await tx.query.biddingProjects.findFirst({ + where: (biddingProjects, { eq }) => eq(biddingProjects.id, input.biddingProjectId) + }); + + if (!biddingProject) { + throw new Error(`프로젝트 ID ${input.biddingProjectId}를 찾을 수 없습니다.`); + } + + // RFQ 코드 생성 (SHIP 타입) + const rfqCode = await generateRfqCodes(tx, 1); + + // RFQ 생성 + const [rfq] = await tx + .insert(techSalesRfqs) + .values({ + rfqCode: rfqCode[0], + biddingProjectId: input.biddingProjectId, + description: input.description, + dueDate: input.dueDate, + status: "RFQ Created", + rfqType: "SHIP", + createdBy: input.createdBy, + updatedBy: input.createdBy, + }) + .returning({ id: techSalesRfqs.id }); + + // 아이템들 추가 + for (const itemId of input.itemIds) { + await tx + .insert(techSalesRfqItems) + .values({ + rfqId: rfq.id, + itemShipbuildingId: itemId, + itemType: "SHIP", + }); + } + + // 캐시 무효화 + revalidateTag("techSalesRfqs"); + revalidatePath("/evcp/budgetary-tech-sales-ship"); + + return { data: rfq, error: null }; + }); + } catch (err) { + console.error("Error creating Ship RFQ:", err); + return { data: null, error: getErrorMessage(err) }; + } +} + +/** + * 기술영업 해양 Hull RFQ 생성 (1:N 관계) + */ +export async function createTechSalesHullRfq(input: { + biddingProjectId: number; + itemIds: number[]; // Hull 아이템 ID 배열 + dueDate: Date; + description?: string; + createdBy: number; +}) { + unstable_noStore(); + console.log('🔍 createTechSalesHullRfq 호출됨:', input); + + try { + return await db.transaction(async (tx) => { + // 프로젝트 정보 조회 (유효성 검증) + const biddingProject = await tx.query.biddingProjects.findFirst({ + where: (biddingProjects, { eq }) => eq(biddingProjects.id, input.biddingProjectId) + }); + + if (!biddingProject) { + throw new Error(`프로젝트 ID ${input.biddingProjectId}를 찾을 수 없습니다.`); + } + + // RFQ 코드 생성 (HULL 타입) + const hullRfqCode = await generateRfqCodes(tx, 1); + + // RFQ 생성 + const [rfq] = await tx + .insert(techSalesRfqs) + .values({ + rfqCode: hullRfqCode[0], + biddingProjectId: input.biddingProjectId, + description: input.description, + dueDate: input.dueDate, + status: "RFQ Created", + rfqType: "HULL", + createdBy: input.createdBy, + updatedBy: input.createdBy, + }) + .returning({ id: techSalesRfqs.id }); + + // 아이템들 추가 + for (const itemId of input.itemIds) { + await tx + .insert(techSalesRfqItems) + .values({ + rfqId: rfq.id, + itemOffshoreHullId: itemId, + itemType: "HULL", + }); + } + + // 캐시 무효화 + revalidateTag("techSalesRfqs"); + revalidatePath("/evcp/budgetary-tech-sales-hull"); + + return { data: rfq, error: null }; + }); + } catch (err) { + console.error("Error creating Hull RFQ:", err); + return { data: null, error: getErrorMessage(err) }; + } +} + +/** + * 기술영업 해양 TOP RFQ 생성 (1:N 관계) + */ +export async function createTechSalesTopRfq(input: { + biddingProjectId: number; + itemIds: number[]; // TOP 아이템 ID 배열 + dueDate: Date; + description?: string; + createdBy: number; +}) { + unstable_noStore(); + console.log('🔍 createTechSalesTopRfq 호출됨:', input); + + try { + return await db.transaction(async (tx) => { + // 프로젝트 정보 조회 (유효성 검증) + const biddingProject = await tx.query.biddingProjects.findFirst({ + where: (biddingProjects, { eq }) => eq(biddingProjects.id, input.biddingProjectId) + }); + + if (!biddingProject) { + throw new Error(`프로젝트 ID ${input.biddingProjectId}를 찾을 수 없습니다.`); + } + + // RFQ 코드 생성 (TOP 타입) + const topRfqCode = await generateRfqCodes(tx, 1); + + // RFQ 생성 + const [rfq] = await tx + .insert(techSalesRfqs) + .values({ + rfqCode: topRfqCode[0], + biddingProjectId: input.biddingProjectId, + description: input.description, + dueDate: input.dueDate, + status: "RFQ Created", + rfqType: "TOP", + createdBy: input.createdBy, + updatedBy: input.createdBy, + }) + .returning({ id: techSalesRfqs.id }); + + // 아이템들 추가 + for (const itemId of input.itemIds) { + await tx + .insert(techSalesRfqItems) + .values({ + rfqId: rfq.id, + itemOffshoreTopId: itemId, + itemType: "TOP", + }); + } + + // 캐시 무효화 + revalidateTag("techSalesRfqs"); + revalidatePath("/evcp/budgetary-tech-sales-top"); + + return { data: rfq, error: null }; + }); + } catch (err) { + console.error("Error creating TOP RFQ:", err); + return { data: null, error: getErrorMessage(err) }; + } +} + +/** + * 조선 RFQ 전용 조회 함수 + */ +export async function getTechSalesShipRfqsWithJoin(input: GetTechSalesRfqsSchema) { + return getTechSalesRfqsWithJoin({ ...input, rfqType: "SHIP" }); +} + +/** + * 해양 TOP RFQ 전용 조회 함수 + */ +export async function getTechSalesTopRfqsWithJoin(input: GetTechSalesRfqsSchema) { + return getTechSalesRfqsWithJoin({ ...input, rfqType: "TOP" }); +} + +/** + * 해양 HULL RFQ 전용 조회 함수 + */ +export async function getTechSalesHullRfqsWithJoin(input: GetTechSalesRfqsSchema) { + return getTechSalesRfqsWithJoin({ ...input, rfqType: "HULL" }); +} + +/** + * 조선 벤더 견적서 전용 조회 함수 + */ +export async function getTechSalesShipVendorQuotationsWithJoin(input: { + rfqId?: number; + vendorId?: number; + search?: string; + filters?: Filter[]; + sort?: { id: string; desc: boolean }[]; + page: number; + perPage: number; +}) { + return getTechSalesVendorQuotationsWithJoin({ ...input, rfqType: "SHIP" }); +} + +/** + * 해양 TOP 벤더 견적서 전용 조회 함수 + */ +export async function getTechSalesTopVendorQuotationsWithJoin(input: { + rfqId?: number; + vendorId?: number; + search?: string; + filters?: Filter[]; + sort?: { id: string; desc: boolean }[]; + page: number; + perPage: number; +}) { + return getTechSalesVendorQuotationsWithJoin({ ...input, rfqType: "TOP" }); +} + +/** + * 해양 HULL 벤더 견적서 전용 조회 함수 + */ +export async function getTechSalesHullVendorQuotationsWithJoin(input: { + rfqId?: number; + vendorId?: number; + search?: string; + filters?: Filter[]; + sort?: { id: string; desc: boolean }[]; + page: number; + perPage: number; +}) { + return getTechSalesVendorQuotationsWithJoin({ ...input, rfqType: "HULL" }); +} + +/** + * 기술영업 RFQ의 아이템 목록 조회 + */ +export async function getTechSalesRfqItems(rfqId: number) { + unstable_noStore(); + try { + const items = await db.query.techSalesRfqItems.findMany({ + where: eq(techSalesRfqItems.rfqId, rfqId), + with: { + itemShipbuilding: { + columns: { + id: true, + itemCode: true, + itemList: true, + workType: true, + shipTypes: true, + } + }, + itemOffshoreTop: { + columns: { + id: true, + itemCode: true, + itemList: true, + workType: true, + subItemList: true, + } + }, + itemOffshoreHull: { + columns: { + id: true, + itemCode: true, + itemList: true, + workType: true, + subItemList: true, + } + } + }, + orderBy: [techSalesRfqItems.id] + }); + + // 아이템 타입에 따라 정보 매핑 + const mappedItems = items.map(item => { + let itemInfo = null; + + switch (item.itemType) { + case 'SHIP': + itemInfo = item.itemShipbuilding; + break; + case 'TOP': + itemInfo = item.itemOffshoreTop; + break; + case 'HULL': + itemInfo = item.itemOffshoreHull; + break; + } + + return { + id: item.id, + rfqId: item.rfqId, + itemType: item.itemType, + itemCode: itemInfo?.itemCode || '', + itemList: itemInfo?.itemList || '', + workType: itemInfo?.workType || '', + // 조선이면 shipType, 해양이면 subItemList + shipTypes: item.itemType === 'SHIP' ? (itemInfo as { shipTypes?: string })?.shipTypes || '' : undefined, + subItemList: item.itemType !== 'SHIP' ? (itemInfo as { subItemList?: string })?.subItemList || '' : undefined, + }; + }); + + return { data: mappedItems, error: null }; + } catch (err) { + console.error("Error fetching RFQ items:", err); + return { data: [], error: getErrorMessage(err) }; + } +} + +/** + * RFQ 아이템들과 매칭되는 후보 벤더들을 찾는 함수 + */ +export async function getTechSalesRfqCandidateVendors(rfqId: number) { + unstable_noStore(); + + try { + return await db.transaction(async (tx) => { + // 1. RFQ 정보 조회 (타입 확인) + const rfq = await tx.query.techSalesRfqs.findFirst({ + where: eq(techSalesRfqs.id, rfqId), + columns: { + id: true, + rfqType: true + } + }); + + if (!rfq) { + return { data: [], error: "RFQ를 찾을 수 없습니다." }; + } + + // 2. RFQ 아이템들 조회 + const rfqItems = await tx.query.techSalesRfqItems.findMany({ + where: eq(techSalesRfqItems.rfqId, rfqId), + with: { + itemShipbuilding: true, + itemOffshoreTop: true, + itemOffshoreHull: true, + } + }); + + if (rfqItems.length === 0) { + return { data: [], error: null }; + } + + // 3. 아이템 코드들 추출 + const itemCodes: string[] = []; + rfqItems.forEach(item => { + if (item.itemType === "SHIP" && item.itemShipbuilding?.itemCode) { + itemCodes.push(item.itemShipbuilding.itemCode); + } else if (item.itemType === "TOP" && item.itemOffshoreTop?.itemCode) { + itemCodes.push(item.itemOffshoreTop.itemCode); + } else if (item.itemType === "HULL" && item.itemOffshoreHull?.itemCode) { + itemCodes.push(item.itemOffshoreHull.itemCode); + } + }); + + if (itemCodes.length === 0) { + return { data: [], error: null }; + } + + // 4. RFQ 타입에 따른 벤더 타입 매핑 + const vendorTypeFilter = rfq.rfqType === "SHIP" ? "SHIP" : + rfq.rfqType === "TOP" ? "OFFSHORE_TOP" : + rfq.rfqType === "HULL" ? "OFFSHORE_HULL" : null; + + if (!vendorTypeFilter) { + return { data: [], error: "지원되지 않는 RFQ 타입입니다." }; + } + + // 5. 매칭되는 벤더들 조회 (타입 필터링 포함) + const candidateVendors = await tx + .select({ + id: techVendors.id, // 벤더 ID를 id로 명명하여 key 문제 해결 + vendorId: techVendors.id, // 호환성을 위해 유지 + vendorName: techVendors.vendorName, + vendorCode: techVendors.vendorCode, + country: techVendors.country, + email: techVendors.email, + phone: techVendors.phone, + status: techVendors.status, + techVendorType: techVendors.techVendorType, + matchedItemCodes: sql` + array_agg(DISTINCT ${techVendorPossibleItems.itemCode}) + `, + matchedItemCount: sql` + count(DISTINCT ${techVendorPossibleItems.itemCode}) + `, + }) + .from(techVendorPossibleItems) + .innerJoin(techVendors, eq(techVendorPossibleItems.vendorId, techVendors.id)) + .where( + and( + inArray(techVendorPossibleItems.itemCode, itemCodes), + or( + eq(techVendors.status, "ACTIVE"), + eq(techVendors.status, "QUOTE_COMPARISON") // 견적비교용 벤더도 RFQ 초대 가능 + ) + // 벤더 타입 필터링 임시 제거 - 데이터 확인 후 다시 추가 + // eq(techVendors.techVendorType, vendorTypeFilter) + ) + ) + .groupBy( + techVendorPossibleItems.vendorId, + techVendors.id, + techVendors.vendorName, + techVendors.vendorCode, + techVendors.country, + techVendors.email, + techVendors.phone, + techVendors.status, + techVendors.techVendorType + ) + .orderBy(desc(sql`count(DISTINCT ${techVendorPossibleItems.itemCode})`)); + + return { data: candidateVendors, error: null }; + }); + } catch (err) { + console.error("Error fetching candidate vendors:", err); + return { data: [], error: getErrorMessage(err) }; + } +} + +/** + * RFQ 타입에 따른 캐시 무효화 경로 반환 + */ +function getTechSalesRevalidationPath(rfqType: "SHIP" | "TOP" | "HULL"): string { + switch (rfqType) { + case "SHIP": + return "/evcp/budgetary-tech-sales-ship"; + case "TOP": + return "/evcp/budgetary-tech-sales-top"; + case "HULL": + return "/evcp/budgetary-tech-sales-hull"; + default: + return "/evcp/budgetary-tech-sales-ship"; + } +} + +/** + * 기술영업 RFQ에 여러 벤더 추가 (techVendors 기반) + * 벤더 추가 시에는 견적서를 생성하지 않고, RFQ 전송 시에 견적서를 생성 + */ +export async function addTechVendorsToTechSalesRfq(input: { + rfqId: number; + vendorIds: number[]; + createdBy: number; +}) { + unstable_noStore(); + + try { + return await db.transaction(async (tx) => { + const results = []; + const errors: string[] = []; + + // 1. RFQ 상태 및 타입 확인 + const rfq = await tx.query.techSalesRfqs.findFirst({ + where: eq(techSalesRfqs.id, input.rfqId), + columns: { + id: true, + status: true, + rfqType: true, + } + }); + + if (!rfq) { + throw new Error("RFQ를 찾을 수 없습니다"); + } + + // 2. 각 벤더에 대해 처리 (이미 추가된 벤더는 견적서가 있는지 확인) + for (const vendorId of input.vendorIds) { + try { + // 이미 추가된 벤더인지 확인 (견적서 존재 여부로 확인) + const existingQuotation = await tx.query.techSalesVendorQuotations.findFirst({ + where: and( + eq(techSalesVendorQuotations.rfqId, input.rfqId), + eq(techSalesVendorQuotations.vendorId, vendorId) + ) + }); + + if (existingQuotation) { + errors.push(`벤더 ID ${vendorId}는 이미 추가되어 있습니다.`); + continue; + } + + // 벤더가 실제로 존재하는지 확인 + const vendor = await tx.query.techVendors.findFirst({ + where: eq(techVendors.id, vendorId), + columns: { id: true, vendorName: true } + }); + + if (!vendor) { + errors.push(`벤더 ID ${vendorId}를 찾을 수 없습니다.`); + continue; + } + + // 🔥 중요: 벤더 추가 시에는 견적서를 생성하지 않고, "Assigned" 상태로만 생성 + // quotation_version은 null로 설정하여 벤더가 실제 견적 제출 시에만 리비전 생성 + const [quotation] = await tx + .insert(techSalesVendorQuotations) + .values({ + rfqId: input.rfqId, + vendorId: vendorId, + status: "Assigned", // Draft가 아닌 Assigned 상태로 생성 + quotationVersion: null, // 리비전은 견적 제출 시에만 생성 + createdBy: input.createdBy, + updatedBy: input.createdBy, + }) + .returning({ id: techSalesVendorQuotations.id }); + + // 🆕 RFQ의 아이템 코드들을 tech_vendor_possible_items에 추가 + try { + // RFQ의 아이템들 조회 + const rfqItemsResult = await getTechSalesRfqItems(input.rfqId); + + if (rfqItemsResult.data && rfqItemsResult.data.length > 0) { + for (const item of rfqItemsResult.data) { + const { + itemCode, + itemList, + workType, // 공종 + shipTypes, // 선종 (배열일 수 있음) + subItemList // 서브아이템리스트 (있을 수도 있음) + } = item; + + // 동적 where 조건 생성: 값이 있으면 비교, 없으면 비교하지 않음 + const whereConds = [ + eq(techVendorPossibleItems.vendorId, vendorId), + itemCode ? eq(techVendorPossibleItems.itemCode, itemCode) : undefined, + itemList ? eq(techVendorPossibleItems.itemList, itemList) : undefined, + workType ? eq(techVendorPossibleItems.workType, workType) : undefined, + shipTypes ? eq(techVendorPossibleItems.shipTypes, shipTypes) : undefined, + subItemList ? eq(techVendorPossibleItems.subItemList, subItemList) : undefined, + ].filter(Boolean); + + const existing = await tx.query.techVendorPossibleItems.findFirst({ + where: and(...whereConds) + }); + + if (!existing) { + await tx.insert(techVendorPossibleItems).values({ + vendorId : vendorId, + itemCode: itemCode ?? null, + itemList: itemList ?? null, + workType: workType ?? null, + shipTypes: shipTypes ?? null, + subItemList: subItemList ?? null, + }); + } + } + } + } catch (possibleItemError) { + // tech_vendor_possible_items 추가 실패는 전체 실패로 처리하지 않음 + console.warn(`벤더 ${vendorId}의 가능 아이템 추가 실패:`, possibleItemError); + } + + results.push({ id: quotation.id, vendorId, vendorName: vendor.vendorName }); + } catch (vendorError) { + console.error(`Error adding vendor ${vendorId}:`, vendorError); + errors.push(`벤더 ID ${vendorId} 추가 중 오류가 발생했습니다.`); + } + } + + // 3. RFQ 상태가 "RFQ Created"이고 성공적으로 추가된 벤더가 있는 경우 상태 업데이트 + if (rfq.status === "RFQ Created" && results.length > 0) { + await tx.update(techSalesRfqs) + .set({ + status: "RFQ Vendor Assignned", + updatedBy: input.createdBy, + updatedAt: new Date() + }) + .where(eq(techSalesRfqs.id, input.rfqId)); + } + + // 캐시 무효화 (RFQ 타입에 따른 동적 경로) + revalidateTag("techSalesRfqs"); + revalidateTag("techSalesVendorQuotations"); + revalidateTag(`techSalesRfq-${input.rfqId}`); + revalidatePath(getTechSalesRevalidationPath(rfq.rfqType || "SHIP")); + + return { + data: results, + error: errors.length > 0 ? errors.join(", ") : null, + successCount: results.length, + errorCount: errors.length + }; + }); + } catch (err) { + console.error("Error adding tech vendors to RFQ:", err); + return { data: [], error: getErrorMessage(err) }; + } +} + +/** + * 기술영업 RFQ의 벤더 목록 조회 (techVendors 기반) + */ +export async function getTechSalesRfqTechVendors(rfqId: number) { + unstable_noStore(); + + try { + return await db.transaction(async (tx) => { + const vendors = await tx + .select({ + id: techSalesVendorQuotations.id, + vendorId: techVendors.id, + vendorName: techVendors.vendorName, + vendorCode: techVendors.vendorCode, + country: techVendors.country, + email: techVendors.email, + phone: techVendors.phone, + status: techSalesVendorQuotations.status, + totalPrice: techSalesVendorQuotations.totalPrice, + currency: techSalesVendorQuotations.currency, + validUntil: techSalesVendorQuotations.validUntil, + submittedAt: techSalesVendorQuotations.submittedAt, + createdAt: techSalesVendorQuotations.createdAt, + }) + .from(techSalesVendorQuotations) + .innerJoin(techVendors, eq(techSalesVendorQuotations.vendorId, techVendors.id)) + .where(eq(techSalesVendorQuotations.rfqId, rfqId)) + .orderBy(desc(techSalesVendorQuotations.createdAt)); + + return { data: vendors, error: null }; + }); + } catch (err) { + console.error("Error fetching RFQ tech vendors:", err); + return { data: [], error: getErrorMessage(err) }; + } +} + +/** + * 기술영업 RFQ에서 기술영업 벤더 제거 (techVendors 기반) + */ +export async function removeTechVendorFromTechSalesRfq(input: { + rfqId: number; + vendorId: number; +}) { + unstable_noStore(); + + try { + return await db.transaction(async (tx) => { + // 해당 벤더의 견적서 상태 확인 + const existingQuotation = await tx.query.techSalesVendorQuotations.findFirst({ + where: and( + eq(techSalesVendorQuotations.rfqId, input.rfqId), + eq(techSalesVendorQuotations.vendorId, input.vendorId) + ) + }); + + if (!existingQuotation) { + return { data: null, error: "해당 벤더가 이 RFQ에 존재하지 않습니다." }; + } + + // Assigned 상태가 아닌 경우 삭제 불가 + if (existingQuotation.status !== "Assigned") { + return { data: null, error: "Assigned 상태의 벤더만 삭제할 수 있습니다." }; + } + + // 해당 벤더의 견적서 삭제 + const [deletedQuotation] = await tx + .delete(techSalesVendorQuotations) + .where( + and( + eq(techSalesVendorQuotations.rfqId, input.rfqId), + eq(techSalesVendorQuotations.vendorId, input.vendorId) + ) + ) + .returning({ id: techSalesVendorQuotations.id }); + + // 캐시 무효화 + revalidateTag("techSalesRfqs"); + revalidateTag("techSalesVendorQuotations"); + + return { data: deletedQuotation, error: null }; + }); + } catch (err) { + console.error("Error removing tech vendor from RFQ:", err); + return { data: null, error: getErrorMessage(err) }; + } +} + +/** + * 기술영업 RFQ에서 여러 기술영업 벤더 제거 (techVendors 기반) + */ +export async function removeTechVendorsFromTechSalesRfq(input: { + rfqId: number; + vendorIds: number[]; +}) { + unstable_noStore(); + + try { + return await db.transaction(async (tx) => { + const results = []; + const errors: string[] = []; + + for (const vendorId of input.vendorIds) { + // 해당 벤더의 견적서 상태 확인 + const existingQuotation = await tx.query.techSalesVendorQuotations.findFirst({ + where: and( + eq(techSalesVendorQuotations.rfqId, input.rfqId), + eq(techSalesVendorQuotations.vendorId, vendorId) + ) + }); + + if (!existingQuotation) { + errors.push(`벤더 ID ${vendorId}가 이 RFQ에 존재하지 않습니다.`); + continue; + } + + // Assigned 상태가 아닌 경우 삭제 불가 + if (existingQuotation.status !== "Assigned") { + errors.push(`벤더 ID ${vendorId}는 Assigned 상태가 아니므로 삭제할 수 없습니다.`); + continue; + } + + // 해당 벤더의 견적서 삭제 + const [deletedQuotation] = await tx + .delete(techSalesVendorQuotations) + .where( + and( + eq(techSalesVendorQuotations.rfqId, input.rfqId), + eq(techSalesVendorQuotations.vendorId, vendorId) + ) + ) + .returning({ id: techSalesVendorQuotations.id }); + + results.push(deletedQuotation); + } + + // 캐시 무효화 + revalidateTag("techSalesRfqs"); + revalidateTag("techSalesVendorQuotations"); + + return { + data: results, + error: errors.length > 0 ? errors.join(", ") : null, + successCount: results.length, + errorCount: errors.length + }; + }); + } catch (err) { + console.error("Error removing tech vendors from RFQ:", err); + return { data: [], error: getErrorMessage(err) }; + } +} + +/** + * 기술영업 벤더 검색 + */ +export async function searchTechVendors(searchTerm: string, limit = 100, rfqType?: "SHIP" | "TOP" | "HULL") { + unstable_noStore(); + + try { + // RFQ 타입에 따른 벤더 타입 매핑 + const vendorTypeFilter = rfqType === "SHIP" ? "조선" : + rfqType === "TOP" ? "해양TOP" : + rfqType === "HULL" ? "해양HULL" : null; + + const whereConditions = [ + or( + eq(techVendors.status, "ACTIVE"), + eq(techVendors.status, "QUOTE_COMPARISON") + ), + or( + ilike(techVendors.vendorName, `%${searchTerm}%`), + ilike(techVendors.vendorCode, `%${searchTerm}%`) + ) + ]; + + // RFQ 타입이 지정된 경우 벤더 타입 필터링 추가 (컴마 구분 문자열에서 검색) + if (vendorTypeFilter) { + whereConditions.push(sql`${techVendors.techVendorType} LIKE ${'%' + vendorTypeFilter + '%'}`); + } + + const results = await db + .select({ + id: techVendors.id, + vendorName: techVendors.vendorName, + vendorCode: techVendors.vendorCode, + status: techVendors.status, + country: techVendors.country, + techVendorType: techVendors.techVendorType, + }) + .from(techVendors) + .where(and(...whereConditions)) + .limit(limit) + .orderBy(techVendors.vendorName); + + return results; + } catch (err) { + console.error("Error searching tech vendors:", err); + throw new Error(getErrorMessage(err)); + } +} + + +/** + * 벤더 견적서 거절 처리 (벤더가 직접 거절) + */ +export async function rejectTechSalesVendorQuotations(input: { + quotationIds: number[]; + rejectionReason?: string; +}) { + try { + const session = await getServerSession(authOptions); + if (!session?.user?.id) { + throw new Error("인증이 필요합니다."); + } + + const result = await db.transaction(async (tx) => { + // 견적서들이 존재하고 벤더가 권한이 있는지 확인 + const quotations = await tx + .select({ + id: techSalesVendorQuotations.id, + status: techSalesVendorQuotations.status, + vendorId: techSalesVendorQuotations.vendorId, + }) + .from(techSalesVendorQuotations) + .where(inArray(techSalesVendorQuotations.id, input.quotationIds)); + + if (quotations.length !== input.quotationIds.length) { + throw new Error("일부 견적서를 찾을 수 없습니다."); + } + + // 이미 거절된 견적서가 있는지 확인 + const alreadyRejected = quotations.filter(q => q.status === "Rejected"); + if (alreadyRejected.length > 0) { + throw new Error("이미 거절된 견적서가 포함되어 있습니다."); + } + + // 승인된 견적서가 있는지 확인 + const alreadyAccepted = quotations.filter(q => q.status === "Accepted"); + if (alreadyAccepted.length > 0) { + throw new Error("이미 승인된 견적서는 거절할 수 없습니다."); + } + + // 견적서 상태를 거절로 변경 + await tx + .update(techSalesVendorQuotations) + .set({ + status: "Rejected", + rejectionReason: input.rejectionReason || null, + updatedBy: parseInt(session.user.id), + updatedAt: new Date(), + }) + .where(inArray(techSalesVendorQuotations.id, input.quotationIds)); + + return { success: true, updatedCount: quotations.length }; + }); + revalidateTag("techSalesRfqs"); + revalidateTag("techSalesVendorQuotations"); + revalidatePath("/partners/techsales/rfq-ship", "page"); + return { + success: true, + message: `${result.updatedCount}개의 견적서가 거절되었습니다.`, + data: result + }; + } catch (error) { + console.error("견적서 거절 오류:", error); + return { + success: false, + error: getErrorMessage(error) + }; + } +} + +// ==================== Revision 관련 ==================== + +/** + * 견적서 revision 히스토리 조회 + */ +export async function getTechSalesVendorQuotationRevisions(quotationId: number) { + try { + const revisions = await db + .select({ + id: techSalesVendorQuotationRevisions.id, + version: techSalesVendorQuotationRevisions.version, + snapshot: techSalesVendorQuotationRevisions.snapshot, + changeReason: techSalesVendorQuotationRevisions.changeReason, + revisionNote: techSalesVendorQuotationRevisions.revisionNote, + revisedBy: techSalesVendorQuotationRevisions.revisedBy, + revisedAt: techSalesVendorQuotationRevisions.revisedAt, + // 수정자 정보 조인 + revisedByName: users.name, + }) + .from(techSalesVendorQuotationRevisions) + .leftJoin(users, eq(techSalesVendorQuotationRevisions.revisedBy, users.id)) + .where(eq(techSalesVendorQuotationRevisions.quotationId, quotationId)) + .orderBy(desc(techSalesVendorQuotationRevisions.version)); + + return { data: revisions, error: null }; + } catch (error) { + console.error("견적서 revision 히스토리 조회 오류:", error); + return { data: null, error: "견적서 히스토리를 조회하는 중 오류가 발생했습니다." }; + } +} + +/** + * 견적서의 현재 버전과 revision 히스토리를 함께 조회 (각 리비전의 첨부파일 포함) + */ +export async function getTechSalesVendorQuotationWithRevisions(quotationId: number) { + try { + // 먼저 현재 견적서 조회 + const currentQuotation = await db.query.techSalesVendorQuotations.findFirst({ + where: eq(techSalesVendorQuotations.id, quotationId), + with: { + // 벤더 정보와 RFQ 정보도 함께 조회 (필요한 경우) + } + }); + + if (!currentQuotation) { + return { data: null, error: "견적서를 찾을 수 없습니다." }; + } + + // 이제 현재 견적서의 정보를 알고 있으므로 병렬로 나머지 정보 조회 + const [revisionsResult, currentAttachments] = await Promise.all([ + getTechSalesVendorQuotationRevisions(quotationId), + getTechSalesVendorQuotationAttachmentsByRevision(quotationId, currentQuotation.quotationVersion || 0) + ]); + + // 현재 견적서에 첨부파일 정보 추가 + const currentWithAttachments = { + ...currentQuotation, + attachments: currentAttachments.data || [] + }; + + // 각 리비전의 첨부파일 정보 추가 + const revisionsWithAttachments = await Promise.all( + (revisionsResult.data || []).map(async (revision) => { + const attachmentsResult = await getTechSalesVendorQuotationAttachmentsByRevision(quotationId, revision.version); + return { + ...revision, + attachments: attachmentsResult.data || [] + }; + }) + ); + + return { + data: { + current: currentWithAttachments, + revisions: revisionsWithAttachments + }, + error: null + }; + } catch (error) { + console.error("견적서 전체 히스토리 조회 오류:", error); + return { data: null, error: "견적서 정보를 조회하는 중 오류가 발생했습니다." }; + } +} + +/** + * 견적서 첨부파일 조회 (리비전 ID 기준 오름차순 정렬) + */ +export async function getTechSalesVendorQuotationAttachments(quotationId: number) { + return unstable_cache( + async () => { + try { + const attachments = await db + .select({ + id: techSalesVendorQuotationAttachments.id, + quotationId: techSalesVendorQuotationAttachments.quotationId, + revisionId: techSalesVendorQuotationAttachments.revisionId, + fileName: techSalesVendorQuotationAttachments.fileName, + originalFileName: techSalesVendorQuotationAttachments.originalFileName, + fileSize: techSalesVendorQuotationAttachments.fileSize, + fileType: techSalesVendorQuotationAttachments.fileType, + filePath: techSalesVendorQuotationAttachments.filePath, + description: techSalesVendorQuotationAttachments.description, + uploadedBy: techSalesVendorQuotationAttachments.uploadedBy, + vendorId: techSalesVendorQuotationAttachments.vendorId, + isVendorUpload: techSalesVendorQuotationAttachments.isVendorUpload, + createdAt: techSalesVendorQuotationAttachments.createdAt, + updatedAt: techSalesVendorQuotationAttachments.updatedAt, + }) + .from(techSalesVendorQuotationAttachments) + .where(eq(techSalesVendorQuotationAttachments.quotationId, quotationId)) + .orderBy(desc(techSalesVendorQuotationAttachments.createdAt)); + + return { data: attachments }; + } catch (error) { + console.error("견적서 첨부파일 조회 오류:", error); + return { error: "견적서 첨부파일 조회 중 오류가 발생했습니다." }; + } + }, + [`quotation-attachments-${quotationId}`], + { + revalidate: 60, + tags: [`quotation-${quotationId}`, "quotation-attachments"], + } + )(); +} + +/** + * 특정 리비전의 견적서 첨부파일 조회 + */ +export async function getTechSalesVendorQuotationAttachmentsByRevision(quotationId: number, revisionId: number) { + try { + const attachments = await db + .select({ + id: techSalesVendorQuotationAttachments.id, + quotationId: techSalesVendorQuotationAttachments.quotationId, + revisionId: techSalesVendorQuotationAttachments.revisionId, + fileName: techSalesVendorQuotationAttachments.fileName, + originalFileName: techSalesVendorQuotationAttachments.originalFileName, + fileSize: techSalesVendorQuotationAttachments.fileSize, + fileType: techSalesVendorQuotationAttachments.fileType, + filePath: techSalesVendorQuotationAttachments.filePath, + description: techSalesVendorQuotationAttachments.description, + uploadedBy: techSalesVendorQuotationAttachments.uploadedBy, + vendorId: techSalesVendorQuotationAttachments.vendorId, + isVendorUpload: techSalesVendorQuotationAttachments.isVendorUpload, + createdAt: techSalesVendorQuotationAttachments.createdAt, + updatedAt: techSalesVendorQuotationAttachments.updatedAt, + }) + .from(techSalesVendorQuotationAttachments) + .where(and( + eq(techSalesVendorQuotationAttachments.quotationId, quotationId), + eq(techSalesVendorQuotationAttachments.revisionId, revisionId) + )) + .orderBy(desc(techSalesVendorQuotationAttachments.createdAt)); + + return { data: attachments }; + } catch (error) { + console.error("리비전별 견적서 첨부파일 조회 오류:", error); + return { error: "첨부파일 조회 중 오류가 발생했습니다." }; + } +} + + +// ==================== Project AVL 관련 ==================== + +/** + * Accepted 상태의 Tech Sales Vendor Quotations 조회 (RFQ, Vendor 정보 포함) + */ +export async function getAcceptedTechSalesVendorQuotations(input: { + search?: string; + filters?: Filter[]; + sort?: { id: string; desc: boolean }[]; + page: number; + perPage: number; + rfqType?: "SHIP" | "TOP" | "HULL"; +}) { + unstable_noStore(); + + try { + const offset = (input.page - 1) * input.perPage; + + // 기본 WHERE 조건: status = 'Accepted'만 조회, rfqType이 'SHIP'이 아닌 것만 + const baseConditions = [ + eq(techSalesVendorQuotations.status, 'Accepted'), + sql`${techSalesRfqs.rfqType} != 'SHIP'` // 조선 RFQ 타입 제외 + ]; + + // 검색 조건 추가 + const searchConditions = []; + if (input.search) { + searchConditions.push( + ilike(techSalesRfqs.rfqCode, `%${input.search}%`), + ilike(techSalesRfqs.description, `%${input.search}%`), + ilike(sql`vendors.vendor_name`, `%${input.search}%`), + ilike(sql`vendors.vendor_code`, `%${input.search}%`) + ); + } + + // 정렬 조건 변환 + const orderByConditions: OrderByType[] = []; + if (input.sort?.length) { + input.sort.forEach((sortItem) => { + switch (sortItem.id) { + case "rfqCode": + orderByConditions.push(sortItem.desc ? desc(techSalesRfqs.rfqCode) : asc(techSalesRfqs.rfqCode)); + break; + case "description": + orderByConditions.push(sortItem.desc ? desc(techSalesRfqs.description) : asc(techSalesRfqs.description)); + break; + case "vendorName": + orderByConditions.push(sortItem.desc ? desc(sql`vendors.vendor_name`) : asc(sql`vendors.vendor_name`)); + break; + case "vendorCode": + orderByConditions.push(sortItem.desc ? desc(sql`vendors.vendor_code`) : asc(sql`vendors.vendor_code`)); + break; + case "totalPrice": + orderByConditions.push(sortItem.desc ? desc(techSalesVendorQuotations.totalPrice) : asc(techSalesVendorQuotations.totalPrice)); + break; + case "acceptedAt": + orderByConditions.push(sortItem.desc ? desc(techSalesVendorQuotations.acceptedAt) : asc(techSalesVendorQuotations.acceptedAt)); + break; + default: + orderByConditions.push(desc(techSalesVendorQuotations.acceptedAt)); + } + }); + } else { + orderByConditions.push(desc(techSalesVendorQuotations.acceptedAt)); + } + + // 필터 조건 추가 + const filterConditions = []; + if (input.filters?.length) { + const filterWhere = filterColumns({ + table: techSalesVendorQuotations, + filters: input.filters, + joinOperator: "and", + }); + if (filterWhere) { + filterConditions.push(filterWhere); + } + } + + // RFQ 타입 필터 + if (input.rfqType) { + filterConditions.push(eq(techSalesRfqs.rfqType, input.rfqType)); + } + + // 모든 조건 결합 + const allConditions = [ + ...baseConditions, + ...filterConditions, + ...(searchConditions.length > 0 ? [or(...searchConditions)] : []) + ]; + + const whereCondition = allConditions.length > 1 + ? and(...allConditions) + : allConditions[0]; + + // 데이터 조회 + const data = await db + .select({ + // Quotation 정보 + id: techSalesVendorQuotations.id, + rfqId: techSalesVendorQuotations.rfqId, + vendorId: techSalesVendorQuotations.vendorId, + quotationCode: techSalesVendorQuotations.quotationCode, + quotationVersion: techSalesVendorQuotations.quotationVersion, + totalPrice: techSalesVendorQuotations.totalPrice, + currency: techSalesVendorQuotations.currency, + validUntil: techSalesVendorQuotations.validUntil, + status: techSalesVendorQuotations.status, + remark: techSalesVendorQuotations.remark, + submittedAt: techSalesVendorQuotations.submittedAt, + acceptedAt: techSalesVendorQuotations.acceptedAt, + createdAt: techSalesVendorQuotations.createdAt, + updatedAt: techSalesVendorQuotations.updatedAt, + + // RFQ 정보 + rfqCode: techSalesRfqs.rfqCode, + rfqType: techSalesRfqs.rfqType, + description: techSalesRfqs.description, + dueDate: techSalesRfqs.dueDate, + rfqStatus: techSalesRfqs.status, + materialCode: techSalesRfqs.materialCode, + + // Vendor 정보 + vendorName: sql`vendors.vendor_name`, + vendorCode: sql`vendors.vendor_code`, + vendorEmail: sql`vendors.email`, + vendorCountry: sql`vendors.country`, + + // Project 정보 + projNm: biddingProjects.projNm, + pspid: biddingProjects.pspid, + sector: biddingProjects.sector, + }) + .from(techSalesVendorQuotations) + .leftJoin(techSalesRfqs, eq(techSalesVendorQuotations.rfqId, techSalesRfqs.id)) + .leftJoin(sql`vendors`, eq(techSalesVendorQuotations.vendorId, sql`vendors.id`)) + .leftJoin(biddingProjects, eq(techSalesRfqs.biddingProjectId, biddingProjects.id)) + .where(whereCondition) + .orderBy(...orderByConditions) + .limit(input.perPage) + .offset(offset); + + // 총 개수 조회 + const totalCount = await db + .select({ count: count() }) + .from(techSalesVendorQuotations) + .leftJoin(techSalesRfqs, eq(techSalesVendorQuotations.rfqId, techSalesRfqs.id)) + .leftJoin(sql`vendors`, eq(techSalesVendorQuotations.vendorId, sql`vendors.id`)) + .leftJoin(biddingProjects, eq(techSalesRfqs.biddingProjectId, biddingProjects.id)) + .where(whereCondition); + + const total = totalCount[0]?.count ?? 0; + const pageCount = Math.ceil(total / input.perPage); + + return { + data, + pageCount, + total, + }; + + } catch (error) { + console.error("getAcceptedTechSalesVendorQuotations 오류:", error); + throw new Error(`Accepted quotations 조회 실패: ${getErrorMessage(error)}`); + } +} + +export async function getBidProjects(pjtType: 'SHIP' | 'TOP' | 'HULL'): Promise { + try { + // 트랜잭션을 사용하여 프로젝트 데이터 조회 + const projectList = await db.transaction(async (tx) => { + // 기본 쿼리 구성 + const query = tx + .select({ + id: biddingProjects.id, + projectCode: biddingProjects.pspid, + projectName: biddingProjects.projNm, + pjtType: biddingProjects.pjtType, + }) + .from(biddingProjects) + .where(eq(biddingProjects.pjtType, pjtType)); + + const results = await query.orderBy(biddingProjects.id); + return results; + }); + + // Handle null projectName values and ensure pjtType is not null + const validProjectList = projectList.map(project => ({ + ...project, + projectName: project.projectName || '', // Replace null with empty string + pjtType: project.pjtType as "SHIP" | "TOP" | "HULL" // Type assertion since WHERE filters ensure non-null + })); + + return validProjectList; + } catch (error) { + console.error("프로젝트 목록 가져오기 실패:", error); + return []; // 오류 발생 시 빈 배열 반환 + } +} + +/** + * 여러 벤더의 contact 정보 조회 + */ +export async function getTechVendorsContacts(vendorIds: number[]) { + unstable_noStore(); + try { + // 직접 조인으로 벤더와 contact 정보 조회 + const contactsWithVendor = await db + .select({ + contactId: techVendorContacts.id, + contactName: techVendorContacts.contactName, + contactPosition: techVendorContacts.contactPosition, + contactEmail: techVendorContacts.contactEmail, + contactPhone: techVendorContacts.contactPhone, + isPrimary: techVendorContacts.isPrimary, + vendorId: techVendorContacts.vendorId, + vendorName: techVendors.vendorName, + vendorCode: techVendors.vendorCode + }) + .from(techVendorContacts) + .leftJoin(techVendors, eq(techVendorContacts.vendorId, techVendors.id)) + .where(inArray(techVendorContacts.vendorId, vendorIds)) + .orderBy( + asc(techVendorContacts.vendorId), + desc(techVendorContacts.isPrimary), + asc(techVendorContacts.contactName) + ); + + // 벤더별로 그룹화 + const contactsByVendor = contactsWithVendor.reduce((acc, row) => { + const vendorId = row.vendorId; + if (!acc[vendorId]) { + acc[vendorId] = { + vendor: { + id: vendorId, + vendorName: row.vendorName || '', + vendorCode: row.vendorCode || '' + }, + contacts: [] + }; + } + acc[vendorId].contacts.push({ + id: row.contactId, + contactName: row.contactName, + contactPosition: row.contactPosition, + contactEmail: row.contactEmail, + contactPhone: row.contactPhone, + isPrimary: row.isPrimary + }); + return acc; + }, {} as Record; + }>); + + return { data: contactsByVendor, error: null }; + } catch (err) { + console.error("벤더 contact 조회 오류:", err); + return { data: {}, error: getErrorMessage(err) }; + } +} + +/** + * quotation별 발송된 담당자 정보 조회 + */ +export async function getQuotationContacts(quotationId: number) { + unstable_noStore(); + try { + // quotation에 연결된 담당자들 조회 + const quotationContacts = await db + .select({ + id: techSalesVendorQuotationContacts.id, + contactId: techSalesVendorQuotationContacts.contactId, + contactName: techVendorContacts.contactName, + contactPosition: techVendorContacts.contactPosition, + contactEmail: techVendorContacts.contactEmail, + contactPhone: techVendorContacts.contactPhone, + contactCountry: techVendorContacts.contactCountry, + isPrimary: techVendorContacts.isPrimary, + createdAt: techSalesVendorQuotationContacts.createdAt, + }) + .from(techSalesVendorQuotationContacts) + .innerJoin( + techVendorContacts, + eq(techSalesVendorQuotationContacts.contactId, techVendorContacts.id) + ) + .where(eq(techSalesVendorQuotationContacts.quotationId, quotationId)) + .orderBy(techSalesVendorQuotationContacts.createdAt); + + return { + success: true, + data: quotationContacts, + error: null, + }; + } catch (error) { + console.error("Quotation contacts 조회 오류:", error); + return { + success: false, + data: [], + error: getErrorMessage(error), + }; + } +} + +/** + * 견적서 첨부파일 업로드 (클라이언트용) + */ +export async function uploadQuotationAttachments( + quotationId: number, + files: File[], + userId: number +): Promise<{ success: boolean; attachments?: Array<{ fileName: string; originalFileName: string; filePath: string; fileSize: number }>; error?: string }> { + try { + const uploadedAttachments = []; + + for (const file of files) { + const saveResult = await saveFile({ + file, + directory: `techsales-quotations/${quotationId}`, + userId: userId.toString(), + }); + + if (!saveResult.success) { + throw new Error(saveResult.error || '파일 저장에 실패했습니다.'); + } + + uploadedAttachments.push({ + fileName: saveResult.fileName!, // 해시된 파일명 (저장용) + originalFileName: saveResult.originalName!, // 원본 파일명 (표시용) + filePath: saveResult.publicPath!, + fileSize: file.size, + }); + } + + return { + success: true, + attachments: uploadedAttachments + }; + } catch (error) { + console.error('견적서 첨부파일 업로드 오류:', error); + return { + success: false, + error: error instanceof Error ? error.message : '파일 업로드 중 오류가 발생했습니다.' + }; + } } \ No newline at end of file -- cgit v1.2.3