(대표님, 최겸) 계약, 업로드 관련, 메뉴처리, 입찰, 프리쿼트, rfqLast관련, tbeLast관련

1 files changed, 521 insertions, 21 deletions

diff --git a/lib/rfq-last/compare-action.ts b/lib/rfq-last/compare-action.ts
index 5d210631..2be594e9 100644
--- a/lib/rfq-last/compare-action.ts
+++ b/lib/rfq-last/compare-action.ts
@@ -1,7 +1,7 @@
 "use server";
 
 import db from "@/db/db";
-import { eq, and, inArray } from "drizzle-orm";
+import { eq, and, inArray,ne } from "drizzle-orm";
 import {
   rfqsLast,
   rfqLastDetails,
@@ -10,8 +10,13 @@ import {
   rfqLastVendorQuotationItems,
   vendors,
   paymentTerms,
-  incoterms,
+  incoterms,vendorSelections
 } from "@/db/schema";
+import { revalidatePath } from "next/cache";
+import { getServerSession } from "next-auth/next"
+import { authOptions } from "@/app/api/auth/[...nextauth]/route"
+
+// ===== 타입 정의 =====
 
 export interface ComparisonData {
   rfqInfo: {
@@ -55,7 +60,7 @@ export interface VendorComparison {
   totalAmount: number;
   currency: string;
   rank?: number;
-  priceVariance?: number; // 평균 대비 차이 %
+  priceVariance?: number;
   
   // 구매자 제시 조건
   buyerConditions: {
@@ -69,8 +74,6 @@ export interface VendorComparison {
     taxCode?: string;
     placeOfShipping?: string;
     placeOfDestination?: string;
-    
-    // 추가 조건
     firstYn: boolean;
     firstDescription?: string;
     sparepartYn: boolean;
@@ -90,8 +93,6 @@ export interface VendorComparison {
     taxCode?: string;
     placeOfShipping?: string;
     placeOfDestination?: string;
-    
-    // 추가 조건 응답
     firstAcceptance?: "수용" | "부분수용" | "거부";
     firstDescription?: string;
     sparepartAcceptance?: "수용" | "부분수용" | "거부";
@@ -104,12 +105,28 @@ export interface VendorComparison {
   conditionDifferences: {
     hasDifferences: boolean;
     differences: string[];
-    criticalDifferences: string[]; // 중요한 차이점
+    criticalDifferences: string[];
   };
   
   // 비고
   generalRemark?: string;
   technicalProposal?: string;
+  
+  // 선정 관련 정보
+  isSelected?: boolean;
+  selectionDate?: Date | null;
+  selectionReason?: string;
+  selectedBy?: number;
+  selectedByName?: string;
+  selectionApprovalStatus?: "대기" | "승인" | "반려" | null;
+  selectionApprovedBy?: number;
+  selectionApprovedAt?: Date | null;
+  selectionApprovalComment?: string;
+  
+  // 계약 관련 정보 추가
+  contractStatus?: string;
+  contractNo?: string;
+  contractCreatedAt?: Date | null;
 }
 
 export interface PrItemComparison {
@@ -143,10 +160,12 @@ export interface PrItemComparison {
     lowestPrice: number;
     highestPrice: number;
     averagePrice: number;
-    priceVariance: number; // 표준편차
+    priceVariance: number;
   };
 }
 
+// ===== 메인 조회 함수 =====
+
 export async function getComparisonData(
   rfqId: number,
   vendorIds: number[]
@@ -159,8 +178,6 @@ export async function getComparisonData(
         rfqCode: rfqsLast.rfqCode,
         rfqTitle: rfqsLast.rfqTitle,
         rfqType: rfqsLast.rfqType,
-        // projectCode: rfqsLast.projectCode,
-        // projectName: rfqsLast.projectName,
         dueDate: rfqsLast.dueDate,
         packageNo: rfqsLast.packageNo,
         packageName: rfqsLast.packageName,
@@ -171,7 +188,7 @@ export async function getComparisonData(
 
     if (!rfqData[0]) return null;
 
-    // 2. 벤더별 정보 및 응답 조회
+    // 2. 벤더별 정보, 응답, 선정 정보 조회
     const vendorData = await db
       .select({
         // 벤더 정보
@@ -197,6 +214,21 @@ export async function getComparisonData(
         buyerSparepartDescription: rfqLastDetails.sparepartDescription,
         buyerMaterialPriceRelatedYn: rfqLastDetails.materialPriceRelatedYn,
         
+        // 선정 관련 정보
+        isSelected: rfqLastDetails.isSelected,
+        selectionDate: rfqLastDetails.selectionDate,
+        selectionReason: rfqLastDetails.selectionReason,
+        selectedBy: rfqLastDetails.selectedBy,
+        selectionApprovalStatus: rfqLastDetails.selectionApprovalStatus,
+        selectionApprovedBy: rfqLastDetails.selectionApprovedBy,
+        selectionApprovedAt: rfqLastDetails.selectionApprovedAt,
+        selectionApprovalComment: rfqLastDetails.selectionApprovalComment,
+        
+        // 계약 관련 정보
+        contractStatus: rfqLastDetails.contractStatus,
+        contractNo: rfqLastDetails.contractNo,
+        contractCreatedAt: rfqLastDetails.contractCreatedAt,
+        
         // 벤더 응답
         responseId: rfqLastVendorResponses.id,
         participationStatus: rfqLastVendorResponses.participationStatus,
@@ -247,7 +279,19 @@ export async function getComparisonData(
       )
       .where(inArray(vendors.id, vendorIds));
 
-    // 3. Payment Terms와 Incoterms 설명 조회
+    // 3. 선정자 이름 조회 (선정된 업체가 있는 경우)
+    const selectedVendor = vendorData.find(v => v.isSelected);
+    let selectedByName = "";
+    if (selectedVendor?.selectedBy) {
+      const [user] = await db
+        .select({ name: users.name })
+        .from(users)
+        .where(eq(users.id, selectedVendor.selectedBy))
+        .limit(1);
+      selectedByName = user?.name || "";
+    }
+
+    // 4. Payment Terms와 Incoterms 설명 조회
     const paymentTermsData = await db
       .select({
         code: paymentTerms.code,
@@ -269,7 +313,7 @@ export async function getComparisonData(
       incotermsData.map(ic => [ic.code, ic.description])
     );
 
-    // 4. PR Items 조회
+    // 5. PR Items 조회
     const prItems = await db
       .select({
         id: rfqPrItems.id,
@@ -284,7 +328,7 @@ export async function getComparisonData(
       .from(rfqPrItems)
       .where(eq(rfqPrItems.rfqsLastId, rfqId));
 
-    // 5. 벤더별 견적 아이템 조회
+    // 6. 벤더별 견적 아이템 조회
     const quotationItems = await db
       .select({
         vendorResponseId: rfqLastVendorQuotationItems.vendorResponseId,
@@ -309,7 +353,7 @@ export async function getComparisonData(
         )
       );
 
-    // 6. 데이터 가공 및 분석
+    // 7. 데이터 가공 및 분석
     const validAmounts = vendorData
       .map(v => v.totalAmount)
       .filter(a => a != null && a > 0);
@@ -318,7 +362,7 @@ export async function getComparisonData(
     const maxAmount = Math.max(...validAmounts);
     const avgAmount = validAmounts.reduce((a, b) => a + b, 0) / validAmounts.length;
 
-    // 벤더별 비교 데이터 구성
+    // 8. 벤더별 비교 데이터 구성
     const vendorComparisons: VendorComparison[] = vendorData.map((v, index) => {
       const differences: string[] = [];
       const criticalDifferences: string[] = [];
@@ -413,16 +457,32 @@ export async function getComparisonData(
         
         generalRemark: v.generalRemark,
         technicalProposal: v.technicalProposal,
+        
+        // 선정 관련 정보
+        isSelected: v.isSelected || false,
+        selectionDate: v.selectionDate,
+        selectionReason: v.selectionReason,
+        selectedBy: v.selectedBy,
+        selectedByName: v.isSelected ? selectedByName : undefined,
+        selectionApprovalStatus: v.selectionApprovalStatus,
+        selectionApprovedBy: v.selectionApprovedBy,
+        selectionApprovedAt: v.selectionApprovedAt,
+        selectionApprovalComment: v.selectionApprovalComment,
+        
+        // 계약 관련 정보
+        contractStatus: v.contractStatus,
+        contractNo: v.contractNo,
+        contractCreatedAt: v.contractCreatedAt,
       };
     });
 
-    // 가격 순위 계산
+    // 9. 가격 순위 계산
     vendorComparisons.sort((a, b) => a.totalAmount - b.totalAmount);
     vendorComparisons.forEach((v, index) => {
       v.rank = index + 1;
     });
 
-    // PR 아이템별 비교 데이터 구성
+    // 10. PR 아이템별 비교 데이터 구성
     const prItemComparisons: PrItemComparison[] = prItems.map(item => {
       const itemQuotes = quotationItems
         .filter(q => q.prItemId === item.id)
@@ -477,7 +537,23 @@ export async function getComparisonData(
       };
     });
 
-    // 최종 데이터 구성
+    console.log({
+      rfqInfo: rfqData[0],
+      vendors: vendorComparisons,
+      prItems: prItemComparisons,
+      summary: {
+        lowestBidder: vendorComparisons[0]?.vendorName || "",
+        highestBidder: vendorComparisons[vendorComparisons.length - 1]?.vendorName || "",
+        priceRange: {
+          min: minAmount,
+          max: maxAmount,
+          average: avgAmount,
+        },
+        currency: vendorComparisons[0]?.currency || "USD",
+      },
+    });
+
+    // 11. 최종 데이터 반환
     return {
       rfqInfo: rfqData[0],
       vendors: vendorComparisons,
@@ -497,4 +573,428 @@ export async function getComparisonData(
     console.error("견적 비교 데이터 조회 실패:", error);
     return null;
   }
-}
\ No newline at end of file
+}
+
+interface SelectVendorParams {
+  rfqId: number;
+  vendorId: number;
+  vendorName: string;
+  vendorCode: string;
+  totalAmount: number;
+  currency: string;
+  selectionReason: string;
+  priceRank: number;
+  hasConditionDifferences: boolean;
+  criticalDifferences: string[];
+  userId: number; // 현재 사용자 ID
+}
+
+export async function selectVendor(params: SelectVendorParams) {
+  try {
+    // 트랜잭션 시작
+    const result = await db.transaction(async (tx) => {
+      // 1. RFQ 상태 확인
+      const [rfq] = await tx
+        .select()
+        .from(rfqsLast)
+        .where(eq(rfqsLast.id, params.rfqId));
+
+      if (!rfq) {
+        throw new Error("RFQ를 찾을 수 없습니다.");
+      }
+
+      if (rfq.status === "최종업체선정") {
+        throw new Error("이미 업체가 선정된 RFQ입니다.");
+      }
+
+      // 2. 기존에 선정된 업체가 있다면 선정 해제
+      await tx
+        .update(rfqLastDetails)
+        .set({ 
+          isSelected: false,
+          updatedAt: new Date(),
+          updatedBy: params.userId
+        })
+        .where(
+          and(
+            eq(rfqLastDetails.rfqsLastId, params.rfqId),
+            eq(rfqLastDetails.isSelected, true)
+          )
+        );
+
+      // 3. 새로운 업체 선정
+      const [selection] = await tx
+        .update(rfqLastDetails)
+        .set({
+          isSelected: true,
+          selectionDate: new Date(),
+          selectionReason: params.selectionReason,
+          selectedBy: params.userId,
+          totalAmount: params.totalAmount.toString(),
+          priceRank: params.priceRank,
+          updatedAt: new Date(),
+          updatedBy: params.userId,
+        })
+        .where(
+          and(
+            eq(rfqLastDetails.rfqsLastId, params.rfqId),
+            eq(rfqLastDetails.vendorsId, params.vendorId),
+            eq(rfqLastDetails.isLatest, true)
+          )
+        )
+        .returning();
+
+      if (!selection) {
+        throw new Error("업체 견적 정보를 찾을 수 없습니다.");
+      }
+
+      // 4. RFQ 상태 업데이트
+      await tx
+        .update(rfqsLast)
+        .set({
+          status: "최종업체선정",
+          updatedAt: new Date(),
+        })
+        .where(eq(rfqsLast.id, params.rfqId));
+
+      // 5. 다른 업체들의 견적은 미선정 상태로 명시적 업데이트
+      await tx
+        .update(rfqLastDetails)
+        .set({
+          isSelected: false,
+          updatedAt: new Date(),
+        })
+        .where(
+          and(
+            eq(rfqLastDetails.rfqsLastId, params.rfqId),
+            eq(rfqLastDetails.isLatest, true),
+            // NOT equal to selected vendor
+            // Drizzle에서는 ne (not equal) 연산자를 사용
+            ne(rfqLastDetails.vendorsId, params.vendorId)
+          )
+        );
+
+      return selection;
+    });
+
+    // 캐시 무효화
+    revalidatePath(`/evcp/rfq-last/${params.rfqId}`);
+    revalidatePath("/evcp/rfq");
+    
+    return {
+      success: true,
+      data: result,
+      redirectUrl: `/evcp/rfq-last/${params.rfqId}/selection-complete`
+    };
+
+  } catch (error) {
+    console.error("업체 선정 오류:", error);
+    return {
+      success: false,
+      error: error instanceof Error ? error.message : "업체 선정 중 오류가 발생했습니다."
+    };
+  }
+}
+
+// 업체 선정 취소
+export async function cancelVendorSelection(rfqId: number, cancelReason: string) {
+  try {
+    const session = await getServerSession(authOptions)
+    if (!session?.user) {
+      throw new Error("인증이 필요합니다.")
+    }
+
+    const userId = Number(session.user.id)
+
+
+    await db.transaction(async (tx) => {
+      // 선정 정보 업데이트 (취소 사유 기록)
+      const [cancelled] = await tx
+        .update(rfqLastDetails)
+        .set({
+          isSelected: false,
+          selectionDate: null,
+          selectionReason: null,
+          selectedBy: null,
+          cancelReason: cancelReason,
+          selectionApprovalStatus: null,
+          selectionApprovedBy: null,
+          selectionApprovedAt: null,
+          selectionApprovalComment: null,
+          updatedAt: new Date(),
+          updatedBy: userId,
+        })
+        .where(
+          and(
+            eq(rfqLastDetails.rfqsLastId, rfqId),
+            eq(rfqLastDetails.isSelected, true)
+          )
+        )
+        .returning();
+
+      if (!cancelled) {
+        throw new Error("선정된 업체를 찾을 수 없습니다.");
+      }
+
+      // RFQ 상태 되돌리기
+      await tx
+        .update(rfqsLast)
+        .set({
+          status: "견적접수",
+          updatedAt: new Date(),
+        })
+        .where(eq(rfqsLast.id, rfqId));
+    });
+
+    revalidatePath(`/evcp/rfq-last/${rfqId}`);
+    revalidatePath("/evcp/rfq-last");
+
+    return { success: true };
+  } catch (error) {
+    console.error("업체 선정 취소 오류:", error);
+    return {
+      success: false,
+      error: error instanceof Error ? error.message : "업체 선정 취소 중 오류가 발생했습니다."
+    };
+  }
+}
+// 선정된 업체 정보 조회
+export async function getSelectedVendor(rfqId: number) {
+  try {
+    const [selected] = await db
+      .select({
+        detail: rfqLastDetails,
+        vendor: vendors,
+      })
+      .from(rfqLastDetails)
+      .leftJoin(vendors, eq(rfqLastDetails.vendorsId, vendors.id))
+      .where(
+        and(
+          eq(rfqLastDetails.rfqsLastId, rfqId),
+          eq(rfqLastDetails.isSelected, true),
+          eq(rfqLastDetails.isLatest, true)
+        )
+      )
+      .limit(1);
+
+    return {
+      success: true,
+      data: selected
+    };
+  } catch (error) {
+    console.error("선정 업체 조회 오류:", error);
+    return {
+      success: false,
+      error: error instanceof Error ? error.message : "선정 업체 조회 중 오류가 발생했습니다."
+    };
+  }
+}
+
+// 선정 승인 요청
+export async function requestSelectionApproval(
+  rfqId: number,
+  vendorId: number,
+  userId: number
+) {
+  try {
+    const [updated] = await db
+      .update(rfqLastDetails)
+      .set({
+        selectionApprovalStatus: "대기",
+        updatedAt: new Date(),
+        updatedBy: userId,
+      })
+      .where(
+        and(
+          eq(rfqLastDetails.rfqsLastId, rfqId),
+          eq(rfqLastDetails.vendorsId, vendorId),
+          eq(rfqLastDetails.isSelected, true),
+          eq(rfqLastDetails.isLatest, true)
+        )
+      )
+      .returning();
+
+    if (!updated) {
+      throw new Error("선정된 업체를 찾을 수 없습니다.");
+    }
+
+    revalidatePath(`/evcp/rfq-last/${rfqId}`);
+
+    return {
+      success: true,
+      data: updated
+    };
+  } catch (error) {
+    console.error("선정 승인 요청 오류:", error);
+    return {
+      success: false,
+      error: error instanceof Error ? error.message : "선정 승인 요청 중 오류가 발생했습니다."
+    };
+  }
+}
+
+// 선정 승인/반려 처리
+export async function processSelectionApproval(
+  rfqId: number,
+  vendorId: number,
+  action: "승인" | "반려",
+  comment: string,
+  approverId: number
+) {
+  try {
+    await db.transaction(async (tx) => {
+      // 선정 승인 정보 업데이트
+      const [updated] = await tx
+        .update(rfqLastDetails)
+        .set({
+          selectionApprovalStatus: action,
+          selectionApprovedBy: approverId,
+          selectionApprovedAt: new Date(),
+          selectionApprovalComment: comment,
+          updatedAt: new Date(),
+          updatedBy: approverId,
+        })
+        .where(
+          and(
+            eq(rfqLastDetails.rfqsLastId, rfqId),
+            eq(rfqLastDetails.vendorsId, vendorId),
+            eq(rfqLastDetails.isSelected, true),
+            eq(rfqLastDetails.isLatest, true)
+          )
+        )
+        .returning();
+
+      if (!updated) {
+        throw new Error("선정된 업체를 찾을 수 없습니다.");
+      }
+
+      // 승인된 경우 RFQ 상태 업데이트
+      if (action === "승인") {
+        await tx
+          .update(rfqsLast)
+          .set({
+            status: "계약 진행중",
+            updatedAt: new Date(),
+          })
+          .where(eq(rfqsLast.id, rfqId));
+      }
+    });
+
+    revalidatePath(`/evcp/rfq-last/${rfqId}`);
+    revalidatePath("/evcp/rfq");
+
+    return {
+      success: true,
+      message: action === "승인" 
+        ? "업체 선정이 승인되었습니다." 
+        : "업체 선정이 반려되었습니다."
+    };
+  } catch (error) {
+    console.error("선정 승인 처리 오류:", error);
+    return {
+      success: false,
+      error: error instanceof Error ? error.message : "선정 승인 처리 중 오류가 발생했습니다."
+    };
+  }
+}
+
+// 가격 순위 업데이트 (견적 제출 후 자동 실행)
+export async function updatePriceRanks(rfqId: number) {
+  try {
+    // 해당 RFQ의 모든 최신 견적 조회
+    const quotes = await db
+      .select({
+        id: rfqLastDetails.id,
+        vendorsId: rfqLastDetails.vendorsId,
+        totalAmount: rfqLastDetails.totalAmount,
+      })
+      .from(rfqLastDetails)
+      .where(
+        and(
+          eq(rfqLastDetails.rfqsLastId, rfqId),
+          eq(rfqLastDetails.isLatest, true)
+        )
+      )
+      .orderBy(asc(rfqLastDetails.totalAmount));
+
+    // 순위 업데이트
+    await db.transaction(async (tx) => {
+      for (let i = 0; i < quotes.length; i++) {
+        await tx
+          .update(rfqLastDetails)
+          .set({ 
+            priceRank: i + 1,
+            updatedAt: new Date()
+          })
+          .where(eq(rfqLastDetails.id, quotes[i].id));
+      }
+    });
+
+    return {
+      success: true,
+      message: "가격 순위가 업데이트되었습니다."
+    };
+  } catch (error) {
+    console.error("가격 순위 업데이트 오류:", error);
+    return {
+      success: false,
+      error: error instanceof Error ? error.message : "가격 순위 업데이트 중 오류가 발생했습니다."
+    };
+  }
+}
+
+// RFQ의 모든 견적 상태 조회
+export async function getRfqQuotationStatus(rfqId: number) {
+  try {
+    const quotations = await db
+      .select({
+        id: rfqLastDetails.id,
+        vendorId: rfqLastDetails.vendorsId,
+        vendorName: vendors.name,
+        vendorCode: vendors.code,
+        totalAmount: rfqLastDetails.totalAmount,
+        currency: rfqLastDetails.currency,
+        priceRank: rfqLastDetails.priceRank,
+        isSelected: rfqLastDetails.isSelected,
+        selectionDate: rfqLastDetails.selectionDate,
+        selectionReason: rfqLastDetails.selectionReason,
+        selectionApprovalStatus: rfqLastDetails.selectionApprovalStatus,
+        emailStatus: rfqLastDetails.emailStatus,
+        lastEmailSentAt: rfqLastDetails.lastEmailSentAt,
+      })
+      .from(rfqLastDetails)
+      .leftJoin(vendors, eq(rfqLastDetails.vendorsId, vendors.id))
+      .where(
+        and(
+          eq(rfqLastDetails.rfqsLastId, rfqId),
+          eq(rfqLastDetails.isLatest, true)
+        )
+      )
+      .orderBy(asc(rfqLastDetails.priceRank));
+
+    const selectedVendor = quotations.find(q => q.isSelected);
+    const totalQuotations = quotations.length;
+    const respondedQuotations = quotations.filter(q => q.totalAmount).length;
+
+    return {
+      success: true,
+      data: {
+        quotations,
+        summary: {
+          total: totalQuotations,
+          responded: respondedQuotations,
+          pending: totalQuotations - respondedQuotations,
+          selected: selectedVendor ? 1 : 0,
+          selectedVendor: selectedVendor || null,
+        }
+      }
+    };
+  } catch (error) {
+    console.error("RFQ 견적 상태 조회 오류:", error);
+    return {
+      success: false,
+      error: error instanceof Error ? error.message : "견적 상태 조회 중 오류가 발생했습니다."
+    };
+  }
+}
+