(최겸) 구매 입찰 to contract 수정

1 files changed, 275 insertions, 97 deletions

diff --git a/lib/bidding/actions.ts b/lib/bidding/actions.ts
index 65ff3138..ae1fb54f 100644
--- a/lib/bidding/actions.ts
+++ b/lib/bidding/actions.ts
@@ -1,11 +1,12 @@
 "use server"

 

 import db from "@/db/db"

-import { eq, and, sql } from "drizzle-orm"

+import { eq, and } from "drizzle-orm"

 import {

   biddings,

   biddingCompanies,

   prItemsForBidding,

+  companyPrItemBids,

   vendors,

   generalContracts,

   generalContractItems,

@@ -38,51 +39,23 @@ export async function transmitToContract(biddingId: number, userId: number) {
 

     const biddingCondition = biddingConditionData.length > 0 ? biddingConditionData[0] : null

 

-    // 3. 낙찰된 업체들 조회 (별도 쿼리)

-    let winnerCompaniesData: { companyId: number; finalQuoteAmount: string | null; vendorCode: string | null; vendorName: string | null; }[] = []

-    try {

-      // 2.1 biddingCompanies만 먼저 조회 (join 제거)

-      const biddingCompaniesRaw = await db.select()

-        .from(biddingCompanies)

-        .where(

-          and(

-            eq(biddingCompanies.biddingId, biddingId),

-            eq(biddingCompanies.isWinner, true)

-          )

-        )

-

-

-      // 2.2 각 company에 대한 vendor 정보 개별 조회

-      for (const bc of biddingCompaniesRaw) {

-        try {

-          const vendorData = await db.select()

-            .from(vendors)

-            .where(eq(vendors.id, bc.companyId))

-            .limit(1)

-

-          const vendor = vendorData.length > 0 ? vendorData[0] : null

-          winnerCompaniesData.push({

-            companyId: bc.companyId,

-            finalQuoteAmount: bc.finalQuoteAmount,

-            vendorCode: vendor?.vendorCode || null,

-            vendorName: vendor?.vendorName || null as string | null,

-          })

-        } catch (vendorError) {

-          console.error('Vendor query error for', bc.companyId, ':', vendorError)

-          // vendor 정보 없이도 진행

-          winnerCompaniesData.push({

-            companyId: bc.companyId,

-            finalQuoteAmount: bc.finalQuoteAmount,

-            vendorCode: null as string | null,

-            vendorName: null as string | null,

-          })

-        }

-      }

-

-    } catch (queryError) {

-      console.error('Query error:', queryError)

-      throw new Error(`biddingCompanies 쿼리 실패: ${queryError}`)

-    }

+    // 3. 낙찰된 업체들 조회 (biddingCompanies.id 포함)

+    const winnerCompaniesData = await db.select({

+      id: biddingCompanies.id,

+      companyId: biddingCompanies.companyId,

+      finalQuoteAmount: biddingCompanies.finalQuoteAmount,

+      awardRatio: biddingCompanies.awardRatio,

+      vendorCode: vendors.vendorCode,

+      vendorName: vendors.vendorName

+    })

+    .from(biddingCompanies)

+    .leftJoin(vendors, eq(biddingCompanies.companyId, vendors.id))

+    .where(

+      and(

+        eq(biddingCompanies.biddingId, biddingId),

+        eq(biddingCompanies.isWinner, true)

+      )

+    )

 

     // 상태 검증

     if (biddingData.status !== 'vendor_selected') {

@@ -95,10 +68,47 @@ export async function transmitToContract(biddingId: number, userId: number) {
     }

 

     for (const winnerCompany of winnerCompaniesData) {

+      // winnerCompany에서 직접 정보 사용

+      const awardRatio = (Number(winnerCompany.awardRatio) || 100) / 100

+      const biddingCompanyId = winnerCompany.id

+

+      // 현재 winnerCompany의 입찰 데이터 조회

+      const companyBids = await db.select({

+        prItemId: companyPrItemBids.prItemId,

+        proposedDeliveryDate: companyPrItemBids.proposedDeliveryDate,

+        bidUnitPrice: companyPrItemBids.bidUnitPrice,

+        bidAmount: companyPrItemBids.bidAmount,

+        currency: companyPrItemBids.currency,

+        // PR 아이템 정보도 함께 조회

+        itemNumber: prItemsForBidding.itemNumber,

+        itemInfo: prItemsForBidding.itemInfo,

+        materialDescription: prItemsForBidding.materialDescription,

+        quantity: prItemsForBidding.quantity,

+        quantityUnit: prItemsForBidding.quantityUnit,

+        totalWeight: prItemsForBidding.totalWeight,

+        weightUnit: prItemsForBidding.weightUnit,

+      })

+      .from(companyPrItemBids)

+      .leftJoin(prItemsForBidding, eq(companyPrItemBids.prItemId, prItemsForBidding.id))

+      .where(eq(companyPrItemBids.biddingCompanyId, biddingCompanyId))

+

+      // 발주비율에 따른 최종 계약금액 계산

+      let totalContractAmount = 0

+      if (companyBids.length > 0) {

+        for (const bid of companyBids) {

+          const originalQuantity = Number(bid.quantity) || 0

+          const bidUnitPrice = Number(bid.bidUnitPrice) || 0

+          const finalQuantity = originalQuantity * awardRatio

+          const finalAmount = finalQuantity * bidUnitPrice

+          totalContractAmount += finalAmount

+        }

+      }

+

       // 계약 번호 자동 생성 (실제 규칙에 맞게)

       const contractNumber = await generateContractNumber(userId.toString(), biddingData.contractType)

       console.log('Generated contractNumber:', contractNumber)

-      // general-contract 생성

+

+      // general-contract 생성 (발주비율 계산된 최종 금액 사용)

       const contractResult = await db.insert(generalContracts).values({

         contractNumber,

         revision: 0,

@@ -108,9 +118,9 @@ export async function transmitToContract(biddingId: number, userId: number) {
         name: biddingData.title,

         vendorId: winnerCompany.companyId,

         linkedBidNumber: biddingData.biddingNumber,

-        contractAmount: winnerCompany.finalQuoteAmount || null,

-        contractStartDate: biddingData.contractStartDate || null,

-        contractEndDate: biddingData.contractEndDate || null,

+        contractAmount: totalContractAmount || null, // 발주비율 계산된 최종 금액 사용

+        startDate: biddingData.contractStartDate || null,

+        endDate: biddingData.contractEndDate || null,

         currency: biddingData.currency || 'KRW',

         // 계약 조건 정보 추가

         paymentTerm: biddingCondition?.paymentTerms || null,

@@ -124,33 +134,39 @@ export async function transmitToContract(biddingId: number, userId: number) {
       console.log('contractResult', contractResult)

       const contractId = contractResult[0].id

 

-      // 4. PR 아이템들로 general-contract-items 생성

-      const prItems = await db.select()

-        .from(prItemsForBidding)

-        .where(eq(prItemsForBidding.biddingId, biddingId))

+      // 현재 winnerCompany의 품목정보 생성 (발주비율 적용)

+      if (companyBids.length > 0) {

+        console.log(`Creating ${companyBids.length} contract items for winner company ${winnerCompany.companyId} with award ratio ${awardRatio}`)

+        for (const bid of companyBids) {

+          // 발주비율에 따른 최종 수량/중량 계산

+          const originalQuantity = Number(bid.quantity) || 0

+          const originalWeight = Number(bid.totalWeight) || 0

+          const bidUnitPrice = Number(bid.bidUnitPrice) || 0

+

+          const finalQuantity = originalQuantity * awardRatio

+          const finalWeight = originalWeight * awardRatio

+          const finalAmount = finalQuantity > 0

+            ? finalQuantity * bidUnitPrice

+            : finalWeight * bidUnitPrice

 

-      if (prItems.length > 0) {

-        console.log(`Creating ${prItems.length} contract items for contract ${contractId}`)

-        for (const prItem of prItems) {

           await db.insert(generalContractItems).values({

-            contractId,

-            project: prItem.projectInfo || '',

-            itemCode: prItem.itemNumber || '',

-            itemInfo: prItem.itemInfo || '',

-            specification: prItem.materialDescription || '',

-            quantity: prItem.quantity || null,

-            quantityUnit: prItem.quantityUnit || '',

-            contractUnitPrice: prItem.annualUnitPrice || null,

-            contractAmount: prItem.annualUnitPrice && prItem.quantity

-              ? (prItem.annualUnitPrice * prItem.quantity)

-              : null,

-            contractCurrency: biddingData.currency || 'KRW',

-            contractDeliveryDate: prItem.requestedDeliveryDate || null,

-          })

+            contractId: contractId,

+            itemCode: bid.itemNumber || '',

+            itemInfo: bid.itemInfo || '',

+            specification: bid.materialDescription || '',

+            quantity: finalQuantity || null,

+            quantityUnit: bid.quantityUnit || '',

+            totalWeight: finalWeight || null,

+            weightUnit: bid.weightUnit || '',

+            contractDeliveryDate: bid.proposedDeliveryDate || null,

+            contractUnitPrice: bid.bidUnitPrice || null,

+            contractAmount: finalAmount || null,

+            contractCurrency: bid.currency || biddingData.currency || 'KRW',

+          } as any)

         }

-        console.log(`Created ${prItems.length} contract items`)

+        console.log(`Created ${companyBids.length} contract items for winner company ${winnerCompany.companyId}`)

       } else {

-        console.log('No PR items found for this bidding')

+        console.log(`No bid data found for winner company ${winnerCompany.companyId}`)

       }

     }

 

@@ -189,34 +205,101 @@ export async function transmitToPO(biddingId: number) {
 

     const biddingCondition = biddingConditionData.length > 0 ? biddingConditionData[0] : null

 

-    // 3. 낙찰된 업체들 조회

+    // 3. 낙찰된 업체들 조회 (발주비율 포함)

     const winnerCompaniesRaw = await db.select({

+      id: biddingCompanies.id,

       companyId: biddingCompanies.companyId,

       finalQuoteAmount: biddingCompanies.finalQuoteAmount,

+      awardRatio: biddingCompanies.awardRatio,

       vendorCode: vendors.vendorCode,

       vendorName: vendors.vendorName

     })

-      .from(biddingCompanies)

-      .leftJoin(vendors, eq(biddingCompanies.companyId, vendors.id))

-      .where(

-        and(

-          eq(biddingCompanies.biddingId, biddingId),

-          eq(biddingCompanies.isWinner, true)

-        )

+    .from(biddingCompanies)

+    .leftJoin(vendors, eq(biddingCompanies.companyId, vendors.id))

+    .where(

+      and(

+        eq(biddingCompanies.biddingId, biddingId),

+        eq(biddingCompanies.isWinner, true)

       )

+    )

 

     if (winnerCompaniesRaw.length === 0) {

       throw new Error("낙찰된 업체가 없습니다.")

     }

 

-    // 4. PR 아이템 조회

-    const prItems = await db.select()

-      .from(prItemsForBidding)

-      .where(eq(prItemsForBidding.biddingId, biddingId))

+    // 4. 낙찰된 업체들의 입찰 데이터 조회 (발주비율 적용)

+    type POItem = {

+      prItemId: number

+      proposedDeliveryDate: string | null

+      bidUnitPrice: string | null

+      bidAmount: string | null

+      currency: string | null

+      itemNumber: string | null

+      itemInfo: string | null

+      materialDescription: string | null

+      quantity: string | null

+      quantityUnit: string | null

+      totalWeight: string | null

+      weightUnit: string | null

+      finalQuantity: number

+      finalWeight: number

+      finalAmount: number

+      awardRatio: number

+      vendorCode: string | null

+      vendorName: string | null

+      companyId: number

+    }

+    const poItems: POItem[] = []

+    for (const winner of winnerCompaniesRaw) {

+      const awardRatio = (Number(winner.awardRatio) || 100) / 100

+

+      const companyBids = await db.select({

+        prItemId: companyPrItemBids.prItemId,

+        proposedDeliveryDate: companyPrItemBids.proposedDeliveryDate,

+        bidUnitPrice: companyPrItemBids.bidUnitPrice,

+        bidAmount: companyPrItemBids.bidAmount,

+        currency: companyPrItemBids.currency,

+        // PR 아이템 정보

+        itemNumber: prItemsForBidding.itemNumber,

+        itemInfo: prItemsForBidding.itemInfo,

+        materialDescription: prItemsForBidding.materialDescription,

+        quantity: prItemsForBidding.quantity,

+        quantityUnit: prItemsForBidding.quantityUnit,

+        totalWeight: prItemsForBidding.totalWeight,

+        weightUnit: prItemsForBidding.weightUnit,

+      })

+      .from(companyPrItemBids)

+      .leftJoin(prItemsForBidding, eq(companyPrItemBids.prItemId, prItemsForBidding.id))

+      .where(eq(companyPrItemBids.biddingCompanyId, winner.id))

+

+      // 발주비율 적용하여 PO 아이템 생성

+      for (const bid of companyBids) {

+        const originalQuantity = Number(bid.quantity) || 0

+        const originalWeight = Number(bid.totalWeight) || 0

+        const bidUnitPrice = Number(bid.bidUnitPrice) || 0

+

+        const finalQuantity = originalQuantity * awardRatio

+        const finalWeight = originalWeight * awardRatio

+        const finalAmount = finalQuantity > 0

+          ? finalQuantity * bidUnitPrice

+          : finalWeight * bidUnitPrice

+

+        poItems.push({

+          ...bid,

+          finalQuantity,

+          finalWeight,

+          finalAmount,

+          awardRatio,

+          vendorCode: winner.vendorCode,

+          vendorName: winner.vendorName,

+          companyId: winner.companyId,

+        } as POItem)

+      }

+    }

 

-    // 5. PO 데이터 구성 (bidding condition 정보 사용)

+    // 5. PO 데이터 구성 (bidding condition 정보와 발주비율 적용된 데이터 사용)

     const poData = {

-      T_Bidding_HEADER: winnerCompaniesRaw.map((company, index) => ({

+      T_Bidding_HEADER: winnerCompaniesRaw.map((company) => ({

         ANFNR: bidding.biddingNumber,

         LIFNR: company.vendorCode || `VENDOR${company.companyId}`,

         ZPROC_IND: 'A', // 구매 처리 상태

@@ -232,20 +315,16 @@ export async function transmitToPO(biddingId: number) {
         IHRAN: getCurrentSAPDate(),

         TEXT: `PO from Bidding: ${bidding.title}`,

       })),

-      T_Bidding_ITEM: prItems.map((item, index) => ({

+      T_Bidding_ITEM: poItems.map((item, index) => ({

         ANFNR: bidding.biddingNumber,

         ANFPS: (index + 1).toString().padStart(5, '0'),

-        LIFNR: winnerCompaniesRaw[0]?.vendorCode || `VENDOR${winnerCompaniesRaw[0]?.companyId}`,

-        NETPR: item.annualUnitPrice?.toString() || '0',

+        LIFNR: item.vendorCode || `VENDOR${item.companyId}`,

+        NETPR: item.bidUnitPrice?.toString() || '0',

         PEINH: '1',

         BPRME: item.quantityUnit || 'EA',

-        NETWR: item.annualUnitPrice && item.quantity

-          ? (item.annualUnitPrice * item.quantity).toString()

-          : '0',

-        BRTWR: item.annualUnitPrice && item.quantity

-          ? ((item.annualUnitPrice * item.quantity) * 1.1).toString() // 10% 부가세 가정

-          : '0',

-        LFDAT: item.requestedDeliveryDate?.toISOString().split('T')[0] || getCurrentSAPDate(),

+        NETWR: item.finalAmount?.toString() || '0',

+        BRTWR: (Number(item.finalAmount || 0) * 1.1).toString(), // 10% 부가세 가정

+        LFDAT: item.proposedDeliveryDate ? new Date(item.proposedDeliveryDate).toISOString().split('T')[0] : getCurrentSAPDate(),

       })),

       T_PR_RETURN: [{

         ANFNR: bidding.biddingNumber,

@@ -272,3 +351,102 @@ export async function transmitToPO(biddingId: number) {
     throw new Error(error instanceof Error ? error.message : 'PO 전송에 실패했습니다.')

   }

 }

+

+// 낙찰된 업체들의 상세 정보 조회 (발주비율에 따른 계산 포함)

+export async function getWinnerDetails(biddingId: number) {

+  try {

+    // 1. 낙찰된 업체들 조회

+    const winnerCompanies = await db.select({

+      id: biddingCompanies.id,

+      companyId: biddingCompanies.companyId,

+      finalQuoteAmount: biddingCompanies.finalQuoteAmount,

+      awardRatio: biddingCompanies.awardRatio,

+      vendorName: vendors.vendorName,

+      vendorCode: vendors.vendorCode,

+    })

+    .from(biddingCompanies)

+    .leftJoin(vendors, eq(biddingCompanies.companyId, vendors.id))

+    .where(

+      and(

+        eq(biddingCompanies.biddingId, biddingId),

+        eq(biddingCompanies.isWinner, true)

+      )

+    )

+

+    if (winnerCompanies.length === 0) {

+      return { success: false, error: '낙찰된 업체가 없습니다.' }

+    }

+

+    // 2. 각 낙찰 업체의 입찰 품목 정보 조회

+    const winnerDetails = []

+

+    for (const winner of winnerCompanies) {

+      // 업체의 입찰 품목 정보 조회

+      const companyBids = await db.select({

+        prItemId: companyPrItemBids.prItemId,

+        proposedDeliveryDate: companyPrItemBids.proposedDeliveryDate,

+        bidUnitPrice: companyPrItemBids.bidUnitPrice,

+        bidAmount: companyPrItemBids.bidAmount,

+        currency: companyPrItemBids.currency,

+        // PR 아이템 정보

+        itemNumber: prItemsForBidding.itemNumber,

+        itemInfo: prItemsForBidding.itemInfo,

+        materialDescription: prItemsForBidding.materialDescription,

+        quantity: prItemsForBidding.quantity,

+        quantityUnit: prItemsForBidding.quantityUnit,

+        totalWeight: prItemsForBidding.totalWeight,

+        weightUnit: prItemsForBidding.weightUnit,

+      })

+      .from(companyPrItemBids)

+      .leftJoin(prItemsForBidding, eq(companyPrItemBids.prItemId, prItemsForBidding.id))

+      .where(eq(companyPrItemBids.biddingCompanyId, winner.id))

+

+      // 발주비율에 따른 계산 (백분율을 실제 비율로 변환)

+      const awardRatio = (Number(winner.awardRatio) || 100) / 100

+      const calculatedItems = companyBids.map(bid => {

+        const originalQuantity = Number(bid.quantity) || 0

+        const originalWeight = Number(bid.totalWeight) || 0

+        const bidUnitPrice = Number(bid.bidUnitPrice) || 0

+

+        // 발주비율에 따른 최종 수량/중량 계산

+        const finalQuantity = originalQuantity * awardRatio

+        const finalWeight = originalWeight * awardRatio

+

+        // 최종 견적가 계산 (수량 또는 중량에 따른)

+        const finalAmount = finalQuantity > 0

+          ? finalQuantity * bidUnitPrice

+          : finalWeight * bidUnitPrice

+

+        return {

+          ...bid,

+          finalQuantity,

+          finalWeight,

+          finalAmount,

+          awardRatio,

+        }

+      })

+

+      // 업체 총 견적가 계산

+      const totalFinalAmount = calculatedItems.reduce((sum, item) => sum + item.finalAmount, 0)

+

+      winnerDetails.push({

+        ...winner,

+        items: calculatedItems,

+        totalFinalAmount,

+        awardRatio: Number(winner.awardRatio) || 1,

+      })

+    }

+

+    return {

+      success: true,

+      data: winnerDetails

+    }

+

+  } catch (error) {

+    console.error('Winner details 조회 실패:', error)

+    return {

+      success: false,

+      error: '낙찰 업체 상세 정보 조회에 실패했습니다.'

+    }

+  }

+}