(대표님) 구매 RFQ AVL dialog 추가

1 files changed, 267 insertions, 2 deletions

diff --git a/lib/rfq-last/service.ts b/lib/rfq-last/service.ts
index 43943c71..98d0e750 100644
--- a/lib/rfq-last/service.ts
+++ b/lib/rfq-last/service.ts
@@ -3,7 +3,7 @@
 
 import { revalidatePath, unstable_cache, unstable_noStore } from "next/cache";
 import db from "@/db/db";
-import { paymentTerms, incoterms, rfqLastVendorQuotationItems, rfqLastVendorAttachments, rfqLastVendorResponses, RfqsLastView, rfqLastAttachmentRevisions, rfqLastAttachments, rfqsLast, rfqsLastView, users, rfqPrItems, prItemsLastView, vendors, rfqLastDetails, rfqLastVendorResponseHistory, rfqLastDetailsView, vendorContacts, projects, basicContract, basicContractTemplates, rfqLastTbeSessions, rfqLastTbeDocumentReviews } from "@/db/schema";
+import { avlVendorInfo, paymentTerms, incoterms, rfqLastVendorQuotationItems, rfqLastVendorAttachments, rfqLastVendorResponses, RfqsLastView, rfqLastAttachmentRevisions, rfqLastAttachments, rfqsLast, rfqsLastView, users, rfqPrItems, prItemsLastView, vendors, rfqLastDetails, rfqLastVendorResponseHistory, rfqLastDetailsView, vendorContacts, projects, basicContract, basicContractTemplates, rfqLastTbeSessions, rfqLastTbeDocumentReviews } from "@/db/schema";
 import { sql, and, desc, asc, like, ilike, or, eq, SQL, count, gte, lte, isNotNull, ne, inArray } from "drizzle-orm";
 import { filterColumns } from "@/lib/filter-columns";
 import { GetRfqLastAttachmentsSchema, GetRfqsSchema } from "./validations";
@@ -4429,4 +4429,269 @@ export async function assignPicToRfqs({ rfqIds, picUserId }: AssignPicParams) {
       message: error instanceof Error ? error.message : "담당자 지정 중 오류가 발생했습니다." 
     };
   }
-}
\ No newline at end of file
+}
+
+
+// AVL 벤더 정보 가져오기
+export async function getAvlVendorsForRfq(rfqId: number) {
+  try {
+    const session = await getServerSession(authOptions);
+    if (!session?.user) {
+      throw new Error("인증이 필요합니다.");
+    }
+
+    // 1. RFQ 정보 조회하여 프로젝트 코드 가져오기
+    const rfqData = await db.select({
+      projectId: rfqsLast.projectId,
+      projectCode: projects.projectCode,
+    })
+    .from(rfqsLast)
+    .leftJoin(projects, eq(rfqsLast.projectId, projects.id))
+    .where(eq(rfqsLast.id, rfqId))
+    .limit(1);
+
+    if (!rfqData[0]?.projectCode) {
+      return { 
+        success: false, 
+        error: "RFQ에 연결된 프로젝트 코드를 찾을 수 없습니다." 
+      };
+    }
+
+    const projectCode = rfqData[0].projectCode;
+
+    // 2. RFQ PR Items에서 major인 자재그룹 코드 가져오기
+    const majorMaterials = await db.select({
+      materialCategory: rfqPrItems.materialCategory,
+    })
+    .from(rfqPrItems)
+    .where(
+      and(
+        eq(rfqPrItems.rfqsLastId, rfqId),
+        isTrue(rfqPrItems.majorYn)
+      )
+    )
+    .groupBy(rfqPrItems.materialCategory);
+
+    if (majorMaterials.length === 0) {
+      return { 
+        success: false, 
+        error: "Major 자재그룹을 찾을 수 없습니다." 
+      };
+    }
+
+    const materialGroupCodes = majorMaterials
+      .map(m => m.materialCategory)
+      .filter(Boolean);
+
+    // 3. AVL 벤더 정보 조회
+    const avlVendors = await db.select({
+      id: avlVendorInfo.id,
+      vendorId: avlVendorInfo.vendorId,
+      vendorName: avlVendorInfo.vendorName,
+      vendorCode: avlVendorInfo.vendorCode,
+      avlVendorName: avlVendorInfo.avlVendorName,
+      tier: avlVendorInfo.tier,
+      headquarterLocation: avlVendorInfo.headquarterLocation,
+      manufacturingLocation: avlVendorInfo.manufacturingLocation,
+      materialGroupCode: avlVendorInfo.materialGroupCode,
+      materialGroupName: avlVendorInfo.materialGroupName,
+      packageName: avlVendorInfo.packageName,
+      isAgent: avlVendorInfo.isAgent,
+      hasAvl: avlVendorInfo.hasAvl,
+      isBlacklist: avlVendorInfo.isBlacklist,
+      isBcc: avlVendorInfo.isBcc,
+      remark: avlVendorInfo.remark,
+    })
+    .from(avlVendorInfo)
+    .where(
+      and(
+        eq(avlVendorInfo.projectCode, projectCode),
+        // materialGroupCode가 materialGroupCodes 중 하나와 일치
+        ...(materialGroupCodes.length > 0 
+          ? [sql`${avlVendorInfo.materialGroupCode} = ANY(${materialGroupCodes})`]
+          : []
+        )
+      )
+    );
+
+    // 4. 이미 RFQ에 추가된 벤더 ID 조회
+    const existingVendors = await db.select({
+      vendorId: rfqLastDetails.vendorsId,
+    })
+    .from(rfqLastDetails)
+    .where(
+      and(
+        eq(rfqLastDetails.rfqsLastId, rfqId),
+        isTrue(rfqLastDetails.isLatest)
+      )
+    );
+
+    const existingVendorIds = existingVendors
+      .map(v => v.vendorId)
+      .filter(Boolean);
+
+    // 5. 벤더 정보가 없는 AVL 레코드에 대해 실제 벤더 정보 조회 및 매칭
+    const vendorsWithoutId = avlVendors.filter(v => !v.vendorId);
+    if (vendorsWithoutId.length > 0) {
+      // 벤더 이름과 코드로 실제 벤더 찾기
+      const vendorNames = vendorsWithoutId.map(v => v.vendorName).filter(Boolean);
+      const vendorCodes = vendorsWithoutId.map(v => v.vendorCode).filter(Boolean);
+      
+      const actualVendors = await db.select({
+        id: vendors.id,
+        vendorName: vendors.vendorName,
+        vendorCode: vendors.vendorCode,
+      })
+      .from(vendors)
+      .where(
+        or(
+          ...(vendorNames.length > 0 ? [sql`${vendors.vendorName} = ANY(${vendorNames})`] : []),
+          ...(vendorCodes.length > 0 ? [sql`${vendors.vendorCode} = ANY(${vendorCodes})`] : [])
+        )
+      );
+
+      // AVL 레코드에 실제 벤더 ID 매칭
+      avlVendors.forEach(avlVendor => {
+        if (!avlVendor.vendorId) {
+          const matchedVendor = actualVendors.find(v => 
+            v.vendorName === avlVendor.vendorName || 
+            v.vendorCode === avlVendor.vendorCode
+          );
+          if (matchedVendor) {
+            avlVendor.vendorId = matchedVendor.id;
+          }
+        }
+      });
+    }
+
+    return {
+      success: true,
+      vendors: avlVendors,
+      existingVendorIds,
+      projectCode,
+      materialGroupCodes,
+    };
+
+  } catch (error) {
+    console.error("AVL 벤더 조회 오류:", error);
+    return { 
+      success: false, 
+      error: "AVL 벤더 정보를 가져오는 중 오류가 발생했습니다." 
+    };
+  }
+}
+
+// AVL 벤더를 RFQ에 추가
+export async function addAvlVendorsToRfq({
+  rfqId,
+  vendors,
+}: {
+  rfqId: number;
+  vendors: Array<{
+    vendorId: number;
+    vendorName: string;
+    vendorCode: string | null;
+    contractRequirements: {
+      agreementYn: boolean;
+      ndaYn: boolean;
+      gtcType: "general" | "project" | "none";
+    };
+  }>;
+}) {
+  try {
+    const session = await getServerSession(authOptions);
+    if (!session?.user) {
+      throw new Error("인증이 필요합니다.");
+    }
+    const userId = Number(session.user.id);
+
+    // 이미 추가된 벤더 확인
+    const existingDetails = await db.select({
+      vendorId: rfqLastDetails.vendorsId,
+    })
+    .from(rfqLastDetails)
+    .where(
+      and(
+        eq(rfqLastDetails.rfqsLastId, rfqId),
+        isTrue(rfqLastDetails.isLatest)
+      )
+    );
+
+    const existingVendorIds = new Set(
+      existingDetails.map(d => d.vendorId).filter(Boolean)
+    );
+
+    // 추가할 벤더 필터링
+    const vendorsToAdd = vendors.filter(v => !existingVendorIds.has(v.vendorId));
+    
+    if (vendorsToAdd.length === 0) {
+      return {
+        success: true,
+        addedCount: 0,
+        skippedCount: vendors.length,
+        message: "모든 벤더가 이미 추가되어 있습니다."
+      };
+    }
+
+    // 벤더 추가
+    const newDetails = await Promise.all(
+      vendorsToAdd.map(async (vendor) => {
+        const { contractRequirements } = vendor;
+        
+        // 벤더 정보 조회하여 위치 확인
+        const vendorInfo = await db.select({
+          country: vendors.country,
+        })
+        .from(vendors)
+        .where(eq(vendors.id, vendor.vendorId))
+        .limit(1);
+
+        const isInternational = vendorInfo[0]?.country && 
+          vendorInfo[0].country !== "KR" && 
+          vendorInfo[0].country !== "한국";
+
+        return db.insert(rfqLastDetails).values({
+          rfqsLastId: rfqId,
+          vendorsId: vendor.vendorId,
+          
+          // 기본계약 설정
+          agreementYn: contractRequirements.agreementYn,
+          ndaYn: contractRequirements.ndaYn,
+          generalGtcYn: isInternational && contractRequirements.gtcType === "general",
+          projectGtcYn: isInternational && contractRequirements.gtcType === "project",
+          gtcType: isInternational ? contractRequirements.gtcType : "none",
+          
+          // 기본값 설정
+          currency: "USD",
+          shortList: false,
+          returnYn: false,
+          materialPriceRelatedYn: false,
+          sparepartYn: false,
+          firstYn: false,
+          sendVersion: 0,
+          isLatest: true,
+          
+          createdAt: new Date(),
+          createdBy: userId,
+          updatedAt: new Date(),
+          updatedBy: userId,
+        }).returning();
+      })
+    );
+
+    return {
+      success: true,
+      addedCount: newDetails.length,
+      skippedCount: vendors.length - newDetails.length,
+      message: `${newDetails.length}개의 AVL 벤더가 추가되었습니다.`,
+      addedVendors: newDetails,
+    };
+
+  } catch (error) {
+    console.error("AVL 벤더 추가 오류:", error);
+    return {
+      success: false,
+      error: "AVL 벤더 추가 중 오류가 발생했습니다."
+    };
+  }
+}