[Merge] AVL 및 Vendor-Pool 기능 1차 구현

1 files changed, 190 insertions, 0 deletions

diff --git a/lib/avl/snapshot-utils.ts b/lib/avl/snapshot-utils.ts
new file mode 100644
index 00000000..0f5d9240
--- /dev/null
+++ b/lib/avl/snapshot-utils.ts
@@ -0,0 +1,190 @@
+/**
+ * AVL Vendor Info 스냅샷 관련 유틸리티 함수들
+ */
+
+import db from "@/db/db"
+import { avlVendorInfo } from "@/db/schema/avl/avl"
+import { inArray } from "drizzle-orm"
+
+/**
+ * AVL Vendor Info 스냅샷 데이터 타입
+ */
+export interface VendorInfoSnapshot {
+  id: number
+  // 설계 정보
+  equipBulkDivision: string | null
+  disciplineCode: string | null
+  disciplineName: string | null
+  // 자재 정보
+  materialNameCustomerSide: string | null
+  packageCode: string | null
+  packageName: string | null
+  materialGroupCode: string | null
+  materialGroupName: string | null
+  // 협력업체 정보
+  vendorId: number | null
+  vendorName: string | null
+  vendorCode: string | null
+  avlVendorName: string | null
+  tier: string | null
+  // FA 정보
+  faTarget: boolean
+  faStatus: string | null
+  // Agent 정보
+  isAgent: boolean
+  // 계약 서명주체
+  contractSignerId: number | null
+  contractSignerName: string | null
+  contractSignerCode: string | null
+  // 위치 정보
+  headquarterLocation: string | null
+  manufacturingLocation: string | null
+  // SHI Qualification
+  hasAvl: boolean
+  isBlacklist: boolean
+  isBcc: boolean
+  // 기술영업 견적결과
+  techQuoteNumber: string | null
+  quoteCode: string | null
+  quoteVendorId: number | null
+  quoteVendorName: string | null
+  quoteVendorCode: string | null
+  quoteCountry: string | null
+  quoteTotalAmount: string | null // 숫자를 문자열로 변환
+  quoteReceivedDate: string | null
+  // 업체 실적 현황
+  recentQuoteDate: string | null
+  recentQuoteNumber: string | null
+  recentOrderDate: string | null
+  recentOrderNumber: string | null
+  // 기타
+  remark: string | null
+  // 타임스탬프
+  createdAt: string
+  updatedAt: string
+}
+
+/**
+ * AVL Vendor Info ID 목록으로부터 스냅샷 데이터를 생성합니다.
+ * 
+ * @param vendorInfoIds - 스냅샷을 생성할 AVL Vendor Info ID 목록
+ * @returns 스냅샷 데이터 배열
+ */
+export async function createVendorInfoSnapshot(vendorInfoIds: number[]): Promise<VendorInfoSnapshot[]> {
+  if (vendorInfoIds.length === 0) {
+    console.log('[SNAPSHOT] 빈 vendorInfoIds 배열, 빈 스냅샷 반환')
+    return []
+  }
+
+  try {
+    console.log('[SNAPSHOT] 스냅샷 생성 시작', { vendorInfoIds, count: vendorInfoIds.length })
+    
+    // AVL Vendor Info 데이터 조회
+    const vendorInfoList = await db
+      .select()
+      .from(avlVendorInfo)
+      .where(inArray(avlVendorInfo.id, vendorInfoIds))
+
+    console.log('[SNAPSHOT] DB 조회 완료', { 
+      requestedIds: vendorInfoIds,
+      foundCount: vendorInfoList.length,
+      foundIds: vendorInfoList.map(v => v.id)
+    })
+
+    // 스냅샷 데이터로 변환
+    const snapshot: VendorInfoSnapshot[] = vendorInfoList.map(info => ({
+      id: info.id,
+      // 설계 정보
+      equipBulkDivision: info.equipBulkDivision,
+      disciplineCode: info.disciplineCode,
+      disciplineName: info.disciplineName,
+      // 자재 정보
+      materialNameCustomerSide: info.materialNameCustomerSide,
+      packageCode: info.packageCode,
+      packageName: info.packageName,
+      materialGroupCode: info.materialGroupCode,
+      materialGroupName: info.materialGroupName,
+      // 협력업체 정보
+      vendorId: info.vendorId,
+      vendorName: info.vendorName,
+      vendorCode: info.vendorCode,
+      avlVendorName: info.avlVendorName,
+      tier: info.tier,
+      // FA 정보
+      faTarget: info.faTarget ?? false,
+      faStatus: info.faStatus,
+      // Agent 정보
+      isAgent: info.isAgent ?? false,
+      // 계약 서명주체
+      contractSignerId: info.contractSignerId,
+      contractSignerName: info.contractSignerName,
+      contractSignerCode: info.contractSignerCode,
+      // 위치 정보
+      headquarterLocation: info.headquarterLocation,
+      manufacturingLocation: info.manufacturingLocation,
+      // SHI Qualification
+      hasAvl: info.hasAvl ?? false,
+      isBlacklist: info.isBlacklist ?? false,
+      isBcc: info.isBcc ?? false,
+      // 기술영업 견적결과
+      techQuoteNumber: info.techQuoteNumber,
+      quoteCode: info.quoteCode,
+      quoteVendorId: info.quoteVendorId,
+      quoteVendorName: info.quoteVendorName,
+      quoteVendorCode: info.quoteVendorCode,
+      quoteCountry: info.quoteCountry,
+      quoteTotalAmount: info.quoteTotalAmount?.toString() || null,
+      quoteReceivedDate: info.quoteReceivedDate,
+      // 업체 실적 현황
+      recentQuoteDate: info.recentQuoteDate,
+      recentQuoteNumber: info.recentQuoteNumber,
+      recentOrderDate: info.recentOrderDate,
+      recentOrderNumber: info.recentOrderNumber,
+      // 기타
+      remark: info.remark,
+      // 타임스탬프 (ISO 문자열로 변환)
+      createdAt: info.createdAt?.toISOString() || new Date().toISOString(),
+      updatedAt: info.updatedAt?.toISOString() || new Date().toISOString(),
+    }))
+
+    console.log('[SNAPSHOT] 스냅샷 변환 완료', { 
+      snapshotCount: snapshot.length,
+      sampleData: snapshot.slice(0, 2) // 처음 2개 항목만 로깅
+    })
+
+    return snapshot
+
+  } catch (error) {
+    console.error('[SNAPSHOT] Vendor Info 스냅샷 생성 실패:', error)
+    throw new Error('Vendor Info 스냅샷 생성 중 오류가 발생했습니다.')
+  }
+}
+
+/**
+ * 스냅샷 데이터의 통계 정보를 생성합니다.
+ * 
+ * @param snapshot - 스냅샷 데이터 배열
+ * @returns 통계 정보 객체
+ */
+export function getSnapshotStatistics(snapshot: VendorInfoSnapshot[]) {
+  return {
+    totalCount: snapshot.length,
+    avlCount: snapshot.filter(item => item.hasAvl).length,
+    blacklistCount: snapshot.filter(item => item.isBlacklist).length,
+    bccCount: snapshot.filter(item => item.isBcc).length,
+    faTargetCount: snapshot.filter(item => item.faTarget).length,
+    agentCount: snapshot.filter(item => item.isAgent).length,
+    tierDistribution: {
+      'Tier 1': snapshot.filter(item => item.tier === 'Tier 1').length,
+      'Tier 2': snapshot.filter(item => item.tier === 'Tier 2').length,
+      'Tier 3': snapshot.filter(item => item.tier === 'Tier 3').length,
+      'Other': snapshot.filter(item => item.tier && !['Tier 1', 'Tier 2', 'Tier 3'].includes(item.tier)).length,
+      'Unspecified': snapshot.filter(item => !item.tier).length,
+    },
+    byDiscipline: snapshot.reduce((acc, item) => {
+      const discipline = item.disciplineName || 'Unknown'
+      acc[discipline] = (acc[discipline] || 0) + 1
+      return acc
+    }, {} as Record<string, number>),
+  }
+}