(최겸) 견적입찰 비교관련 수정

1 files changed, 159 insertions, 77 deletions

diff --git a/lib/rfq-last/compare-action.ts b/lib/rfq-last/compare-action.ts
index 1a50a373..57f8f00f 100644
--- a/lib/rfq-last/compare-action.ts
+++ b/lib/rfq-last/compare-action.ts
@@ -1,17 +1,17 @@
 "use server";
 
 import db from "@/db/db";
-import { eq, and, inArray, ne, asc, desc } from "drizzle-orm";
+import { eq, and, inArray, ne, asc, isNotNull } from "drizzle-orm";
 import {
   rfqsLast,
   rfqLastDetails,
   rfqPrItems,
   rfqLastVendorResponses,
   rfqLastVendorQuotationItems,
+  rfqLastVendorAttachments,
   vendors,
   paymentTerms,
   incoterms,
-  vendorSelections,
   users
 } from "@/db/schema";
 import { revalidatePath } from "next/cache";
@@ -62,22 +62,22 @@ export interface VendorResponseVersion {
   
   // 벤더 제안 조건
   vendorConditions: {
-    currency?: string;
-    paymentTermsCode?: string;
-    paymentTermsDesc?: string;
-    incotermsCode?: string;
-    incotermsDesc?: string;
+    currency?: string | null;
+    paymentTermsCode?: string | null;
+    paymentTermsDesc?: string | null;
+    incotermsCode?: string | null;
+    incotermsDesc?: string | null;
     deliveryDate?: Date | null;
-    contractDuration?: string;
-    taxCode?: string;
-    placeOfShipping?: string;
-    placeOfDestination?: string;
-    firstAcceptance?: "수용" | "부분수용" | "거부";
-    firstDescription?: string;
-    sparepartAcceptance?: "수용" | "부분수용" | "거부";
-    sparepartDescription?: string;
-    materialPriceRelatedYn?: boolean;
-    materialPriceRelatedReason?: string;
+    contractDuration?: string | null;
+    taxCode?: string | null;
+    placeOfShipping?: string | null;
+    placeOfDestination?: string | null;
+    firstAcceptance?: "수용" | "부분수용" | "거부" | null;
+    firstDescription?: string | null;
+    sparepartAcceptance?: "수용" | "부분수용" | "거부" | null;
+    sparepartDescription?: string | null;
+    materialPriceRelatedYn?: boolean | null;
+    materialPriceRelatedReason?: string | null;
   };
   
   // 조건 차이 분석
@@ -88,8 +88,8 @@ export interface VendorResponseVersion {
   };
   
   // 비고
-  generalRemark?: string;
-  technicalProposal?: string;
+  generalRemark?: string | null;
+  technicalProposal?: string | null;
   
   // 품목별 견적 아이템 정보 추가
   quotationItems?: {
@@ -98,11 +98,14 @@ export interface VendorResponseVersion {
     totalPrice: number;
     currency: string;
     quantity: number;
-    deliveryDate?: Date | null;
-    leadTime?: number;
-    manufacturer?: string;
-    modelNo?: string;
+    deliveryDate: Date | null | undefined;
+    leadTime: number | null | undefined;
+    manufacturer: string | null | undefined;
+    modelNo: string | null | undefined;
   }[];
+  
+  // 첨부파일 정보
+  attachments?: VendorAttachment[];
 }
 
 export interface VendorComparison {
@@ -119,14 +122,14 @@ export interface VendorComparison {
     incotermsCode: string;
     incotermsDesc?: string;
     deliveryDate: Date | null;
-    contractDuration?: string;
-    taxCode?: string;
-    placeOfShipping?: string;
-    placeOfDestination?: string;
+    contractDuration?: string | null;
+    taxCode?: string | null;
+    placeOfShipping?: string | null;
+    placeOfDestination?: string | null;
     firstYn: boolean;
-    firstDescription?: string;
+    firstDescription?: string | null;
     sparepartYn: boolean;
-    sparepartDescription?: string;
+    sparepartDescription?: string | null;
     materialPriceRelatedYn: boolean;
   };
   
@@ -150,22 +153,22 @@ export interface VendorComparison {
   
   // 레거시 호환: 최신 응답의 조건 정보
   vendorConditions: {
-    currency?: string;
-    paymentTermsCode?: string;
-    paymentTermsDesc?: string;
-    incotermsCode?: string;
-    incotermsDesc?: string;
+    currency?: string | null;
+    paymentTermsCode?: string | null;
+    paymentTermsDesc?: string | null;
+    incotermsCode?: string | null;
+    incotermsDesc?: string | null;
     deliveryDate?: Date | null;
-    contractDuration?: string;
-    taxCode?: string;
-    placeOfShipping?: string;
-    placeOfDestination?: string;
-    firstAcceptance?: "수용" | "부분수용" | "거부";
-    firstDescription?: string;
-    sparepartAcceptance?: "수용" | "부분수용" | "거부";
-    sparepartDescription?: string;
-    materialPriceRelatedYn?: boolean;
-    materialPriceRelatedReason?: string;
+    contractDuration?: string | null;
+    taxCode?: string | null;
+    placeOfShipping?: string | null;
+    placeOfDestination?: string | null;
+    firstAcceptance?: "수용" | "부분수용" | "거부" | null;
+    firstDescription?: string | null;
+    sparepartAcceptance?: "수용" | "부분수용" | "거부" | null;
+    sparepartDescription?: string | null;
+    materialPriceRelatedYn?: boolean | null;
+    materialPriceRelatedReason?: string | null;
   };
   
   // 레거시 호환: 최신 응답의 조건 차이 분석
@@ -176,23 +179,23 @@ export interface VendorComparison {
   };
   
   // 레거시 호환: 최신 응답의 비고
-  generalRemark?: string;
-  technicalProposal?: string;
+  generalRemark?: string | null;
+  technicalProposal?: string | null;
   
   // 선정 관련 정보
   isSelected?: boolean;
   selectionDate?: Date | null;
-  selectionReason?: string;
-  selectedBy?: number;
+  selectionReason?: string | null;
+  selectedBy?: number | null;
   selectedByName?: string;
   selectionApprovalStatus?: "대기" | "승인" | "반려" | null;
-  selectionApprovedBy?: number;
+  selectionApprovedBy?: number | null;
   selectionApprovedAt?: Date | null;
-  selectionApprovalComment?: string;
+  selectionApprovalComment?: string | null;
   
   // 계약 관련 정보 추가
-  contractStatus?: string;
-  contractNo?: string;
+  contractStatus?: string | null;
+  contractNo?: string | null;
   contractCreatedAt?: Date | null;
 }
 
@@ -202,9 +205,14 @@ export interface PrItemComparison {
   prItem: string;
   materialCode: string;
   materialDescription: string;
+  materialCategory?: string;
   requestedQuantity: number;
   uom: string;
+  size?: string;
+  grossWeight?: number;
+  gwUom?: string;
   requestedDeliveryDate: Date | null;
+  remark?: string;
   
   vendorQuotes: {
     vendorId: number;
@@ -231,6 +239,23 @@ export interface PrItemComparison {
   };
 }
 
+export interface VendorAttachment {
+  id: number;
+  attachmentType: string;
+  documentNo?: string;
+  fileName: string;
+  originalFileName: string;
+  filePath: string;
+  fileSize?: number;
+  fileType?: string;
+  description?: string;
+  validFrom?: Date | null;
+  validTo?: Date | null;
+  uploadedBy: number;
+  uploadedAt: Date;
+  uploaderName?: string;
+}
+
 // ===== 메인 조회 함수 =====
 
 export async function getComparisonData(
@@ -307,7 +332,7 @@ export async function getComparisonData(
       )
       .where(inArray(vendors.id, vendorIds));
 
-    // 2b. 모든 차수의 벤더 응답 조회 (isLatest 조건 제거)
+    // 2b. 벤더가 실제 제출한 응답만 조회 (submittedAt이 null이 아닌 것만)
     const allVendorResponses = await db
       .select({
         responseId: rfqLastVendorResponses.id,
@@ -347,7 +372,8 @@ export async function getComparisonData(
       .where(
         and(
           eq(rfqLastVendorResponses.rfqsLastId, rfqId),
-          inArray(rfqLastVendorResponses.vendorId, vendorIds)
+          inArray(rfqLastVendorResponses.vendorId, vendorIds),
+          isNotNull(rfqLastVendorResponses.submittedAt) // 벤더가 실제 제출한 것만
         )
       )
       .orderBy(asc(rfqLastVendorResponses.vendorId), asc(rfqLastVendorResponses.responseVersion));
@@ -386,7 +412,7 @@ export async function getComparisonData(
       incotermsData.map(ic => [ic.code, ic.description])
     );
 
-    // 5. PR Items 조회
+    // 5. PR Items 조회 (추가 필드 포함)
     const prItems = await db
       .select({
         id: rfqPrItems.id,
@@ -394,9 +420,14 @@ export async function getComparisonData(
         prItem: rfqPrItems.prItem,
         materialCode: rfqPrItems.materialCode,
         materialDescription: rfqPrItems.materialDescription,
+        materialCategory: rfqPrItems.materialCategory,
         quantity: rfqPrItems.quantity,
         uom: rfqPrItems.uom,
+        size: rfqPrItems.size,
+        grossWeight: rfqPrItems.grossWeight,
+        gwUom: rfqPrItems.gwUom,
         deliveryDate: rfqPrItems.deliveryDate,
+        remark: rfqPrItems.remark,
       })
       .from(rfqPrItems)
       .where(eq(rfqPrItems.rfqsLastId, rfqId));
@@ -424,6 +455,31 @@ export async function getComparisonData(
           .where(inArray(rfqLastVendorQuotationItems.vendorResponseId, allResponseIds))
       : [];
 
+    // 6b. 벤더 첨부파일 조회 (모든 응답 버전 포함)
+    const vendorAttachments = allResponseIds.length > 0
+      ? await db
+          .select({
+            id: rfqLastVendorAttachments.id,
+            vendorResponseId: rfqLastVendorAttachments.vendorResponseId,
+            attachmentType: rfqLastVendorAttachments.attachmentType,
+            documentNo: rfqLastVendorAttachments.documentNo,
+            fileName: rfqLastVendorAttachments.fileName,
+            originalFileName: rfqLastVendorAttachments.originalFileName,
+            filePath: rfqLastVendorAttachments.filePath,
+            fileSize: rfqLastVendorAttachments.fileSize,
+            fileType: rfqLastVendorAttachments.fileType,
+            description: rfqLastVendorAttachments.description,
+            validFrom: rfqLastVendorAttachments.validFrom,
+            validTo: rfqLastVendorAttachments.validTo,
+            uploadedBy: rfqLastVendorAttachments.uploadedBy,
+            uploadedAt: rfqLastVendorAttachments.uploadedAt,
+            uploaderName: users.name,
+          })
+          .from(rfqLastVendorAttachments)
+          .leftJoin(users, eq(rfqLastVendorAttachments.uploadedBy, users.id))
+          .where(inArray(rfqLastVendorAttachments.vendorResponseId, allResponseIds))
+      : [];
+
     // 7. 데이터 가공 및 분석 - 각 벤더별 최신 차수 기준으로 평균 계산
     // 각 벤더별로 가장 높은 responseVersion을 가진 응답 찾기
     const latestResponsesByVendor = new Map<number, typeof allVendorResponses[0]>();
@@ -454,7 +510,7 @@ export async function getComparisonData(
       : 0;
 
     // 8. 벤더별 비교 데이터 구성 (차수별 응답 포함)
-    const vendorComparisons: VendorComparison[] = vendorData.map((v) => {
+    const vendorComparisons = vendorData.map((v) => {
       // 이 벤더의 모든 응답 가져오기
       const vendorResponses = allVendorResponses.filter(r => r.vendorId === v.vendorId);
       
@@ -501,10 +557,30 @@ export async function getComparisonData(
             totalPrice: q.totalPrice || 0,
             currency: q.currency || "USD",
             quantity: q.quantity || 0,
-            deliveryDate: q.deliveryDate,
-            leadTime: q.leadTime,
-            manufacturer: q.manufacturer,
-            modelNo: q.modelNo,
+            deliveryDate: q.deliveryDate || undefined,
+            leadTime: q.leadTime || undefined,
+            manufacturer: q.manufacturer || undefined,
+            modelNo: q.modelNo || undefined,
+          }));
+
+        // 이 응답의 첨부파일 가져오기
+        const responseAttachments = vendorAttachments
+          .filter(a => a.vendorResponseId === resp.responseId)
+          .map(a => ({
+            id: a.id,
+            attachmentType: a.attachmentType,
+            documentNo: a.documentNo || undefined,
+            fileName: a.fileName,
+            originalFileName: a.originalFileName,
+            filePath: a.filePath,
+            fileSize: a.fileSize || undefined,
+            fileType: a.fileType || undefined,
+            description: a.description || undefined,
+            validFrom: a.validFrom || undefined,
+            validTo: a.validTo || undefined,
+            uploadedBy: a.uploadedBy,
+            uploadedAt: a.uploadedAt,
+            uploaderName: a.uploaderName || undefined,
           }));
 
         return {
@@ -544,9 +620,10 @@ export async function getComparisonData(
             criticalDifferences,
           },
           
-          generalRemark: resp.generalRemark,
-          technicalProposal: resp.technicalProposal,
+          generalRemark: resp.generalRemark || undefined,
+          technicalProposal: resp.technicalProposal || undefined,
           quotationItems: responseQuotationItems,
+          attachments: responseAttachments,
         };
       });
 
@@ -567,14 +644,14 @@ export async function getComparisonData(
           incotermsCode: v.buyerIncotermsCode || "",
           incotermsDesc: incotermsMap.get(v.buyerIncotermsCode || ""),
           deliveryDate: v.buyerDeliveryDate,
-          contractDuration: v.buyerContractDuration,
-          taxCode: v.buyerTaxCode,
-          placeOfShipping: v.buyerPlaceOfShipping,
-          placeOfDestination: v.buyerPlaceOfDestination,
+          contractDuration: v.buyerContractDuration || undefined,
+          taxCode: v.buyerTaxCode || undefined,
+          placeOfShipping: v.buyerPlaceOfShipping || undefined,
+          placeOfDestination: v.buyerPlaceOfDestination || undefined,
           firstYn: v.buyerFirstYn || false,
-          firstDescription: v.buyerFirstDescription,
+          firstDescription: v.buyerFirstDescription || undefined,
           sparepartYn: v.buyerSparepartYn || false,
-          sparepartDescription: v.buyerSparepartDescription,
+          sparepartDescription: v.buyerSparepartDescription || undefined,
           materialPriceRelatedYn: v.buyerMaterialPriceRelatedYn || false,
         },
         
@@ -618,23 +695,23 @@ export async function getComparisonData(
           criticalDifferences: [],
         },
         
-        generalRemark: latestResp?.generalRemark,
-        technicalProposal: latestResp?.technicalProposal,
+        generalRemark: latestResp?.generalRemark || undefined,
+        technicalProposal: latestResp?.technicalProposal || undefined,
         
         // 선정 관련 정보
         isSelected: v.isSelected || false,
         selectionDate: v.selectionDate,
-        selectionReason: v.selectionReason,
-        selectedBy: v.selectedBy,
+        selectionReason: v.selectionReason || undefined,
+        selectedBy: v.selectedBy || undefined,
         selectedByName: v.isSelected ? selectedByName : undefined,
-        selectionApprovalStatus: v.selectionApprovalStatus,
-        selectionApprovedBy: v.selectionApprovedBy,
+        selectionApprovalStatus: v.selectionApprovalStatus || undefined,
+        selectionApprovedBy: v.selectionApprovedBy || undefined,
         selectionApprovedAt: v.selectionApprovedAt,
-        selectionApprovalComment: v.selectionApprovalComment,
+        selectionApprovalComment: v.selectionApprovalComment || undefined,
         
         // 계약 관련 정보
-        contractStatus: v.contractStatus,
-        contractNo: v.contractNo,
+        contractStatus: v.contractStatus || undefined,
+        contractNo: v.contractNo || undefined,
         contractCreatedAt: v.contractCreatedAt,
       };
     });
@@ -690,9 +767,14 @@ export async function getComparisonData(
         prItem: item.prItem || "",
         materialCode: item.materialCode || "",
         materialDescription: item.materialDescription || "",
+        materialCategory: item.materialCategory || undefined,
         requestedQuantity: item.quantity || 0,
         uom: item.uom || "",
+        size: item.size || undefined,
+        grossWeight: item.grossWeight || undefined,
+        gwUom: item.gwUom || undefined,
         requestedDeliveryDate: item.deliveryDate,
+        remark: item.remark || undefined,
         vendorQuotes: itemQuotes,
         priceAnalysis: {
           lowestPrice: Math.min(...unitPrices) || 0,