(대표님, 임수민) 벤더별 EDP 데이터 입력 진행률 페이지 개선

1 files changed, 162 insertions, 0 deletions

diff --git a/lib/forms/stat.ts b/lib/forms/stat.ts
new file mode 100644
index 00000000..fbcc6f46
--- /dev/null
+++ b/lib/forms/stat.ts
@@ -0,0 +1,162 @@
+"use server"
+
+import db from "@/db/db"
+import { vendors, contracts, contractItems, forms, formEntries, formMetas, tags, tagClasses, tagClassAttributes } from "@/db/schema"
+import { eq, and } from "drizzle-orm"
+import { getEditableFieldsByTag } from "./services"
+
+interface VendorFormStatus {
+  vendorId: number
+  vendorName: string
+  formCount: number // 벤더가 가진 form 개수
+  tagCount: number // 벤더가 가진 tag 개수
+  totalFields: number // 입력해야 하는 총 필드 개수
+  completedFields: number // 입력 완료된 필드 개수
+  completionRate: number // 완료율 (%)
+}
+
+
+export async function getVendorFormStatus(): Promise<VendorFormStatus[]> {
+  try {
+    // 1. 모든 벤더 조회
+    const vendorList = await db
+      .select({
+        id: vendors.id,
+        vendorName: vendors.vendorName
+      })
+      .from(vendors)
+
+    const vendorStatusList: VendorFormStatus[] = []
+
+    for (const vendor of vendorList) {
+      let vendorFormCount = 0
+      let vendorTagCount = 0
+      let vendorTotalFields = 0
+      let vendorCompletedFields = 0
+      const uniqueTags = new Set<string>()
+
+      // 2. 벤더별 계약 조회
+      const vendorContracts = await db
+      .selectDistinct({
+        vendorId: vendors.id,
+        vendorName: vendors.vendorName,
+      })
+      .from(vendors)
+      .innerJoin(contracts, eq(contracts.vendorId, vendors.id))
+
+      for (const contract of vendorContracts) {
+        // 3. 계약별 contractItems 조회
+        const contractItemsList = await db
+          .select({
+            id: contractItems.id
+          })
+          .from(contractItems)
+          .where(eq(contractItems.contractId, contract.id))
+
+        for (const contractItem of contractItemsList) {
+          // 4. contractItem별 forms 조회
+          const formsList = await db
+            .select({
+              id: forms.id,
+              formCode: forms.formCode,
+              contractItemId: forms.contractItemId
+            })
+            .from(forms)
+            .where(eq(forms.contractItemId, contractItem.id))
+
+          vendorFormCount += formsList.length
+
+          // 5. formEntries 조회
+          const entriesList = await db
+            .select({
+              id: formEntries.id,
+              formCode: formEntries.formCode,
+              data: formEntries.data
+            })
+            .from(formEntries)
+            .where(eq(formEntries.contractItemId, contractItem.id))
+
+          // 6. TAG별 편집 가능 필드 조회
+          const editableFieldsByTag = await getEditableFieldsByTag(contractItem.id, contract.projectId)
+
+          for (const entry of entriesList) {
+            // formMetas에서 해당 formCode의 columns 조회
+            const metaResult = await db
+              .select({
+                columns: formMetas.columns
+              })
+              .from(formMetas)
+              .where(
+                and(
+                  eq(formMetas.formCode, entry.formCode),
+                  eq(formMetas.projectId, contract.projectId)
+                )
+              )
+              .limit(1)
+
+            if (metaResult.length === 0) continue
+
+            const metaColumns = metaResult[0].columns as any[]
+            
+            // shi가 'IN' 또는 'BOTH'인 필드 찾기
+            const inputRequiredFields = metaColumns
+              .filter(col => col.shi === 'IN' || col.shi === 'BOTH')
+              .map(col => col.key)
+
+            // entry.data 분석 (배열로 가정)
+            const dataArray = Array.isArray(entry.data) ? entry.data : []
+            
+            for (const dataItem of dataArray) {
+              if (typeof dataItem !== 'object' || !dataItem) continue
+              
+              const tagNo = dataItem.TAG_NO
+              if (tagNo) {
+                uniqueTags.add(tagNo)
+                
+                // TAG별 편집 가능 필드 가져오기
+                const tagEditableFields = editableFieldsByTag.get(tagNo) || []
+                
+                // 최종 입력 필요 필드 = shi 기반 필드 + TAG 기반 편집 가능 필드
+                const allRequiredFields = inputRequiredFields.filter(field => 
+                    tagEditableFields.includes(field)
+                  )
+                
+                // 각 필드별 입력 상태 체크
+                for (const fieldKey of allRequiredFields) {
+                  vendorTotalFields++
+                  
+                  const fieldValue = dataItem[fieldKey]
+                  // 값이 있고, 빈 문자열이 아니고, null이 아니면 입력 완료
+                  if (fieldValue !== undefined && fieldValue !== null && fieldValue !== '') {
+                    vendorCompletedFields++
+                  }
+                }
+              }
+            }
+          }
+        }
+      }
+
+      // 완료율 계산
+      const completionRate = vendorTotalFields > 0 
+        ? Math.round((vendorCompletedFields / vendorTotalFields) * 100 * 10) / 10 
+        : 0
+
+      vendorStatusList.push({
+        vendorId: vendor.id,
+        vendorName: vendor.vendorName || '이름 없음',
+        formCount: vendorFormCount,
+        tagCount: uniqueTags.size,
+        totalFields: vendorTotalFields,
+        completedFields: vendorCompletedFields,
+        completionRate
+      })
+    }
+
+    return vendorStatusList
+
+  } catch (error) {
+    console.error('Error getting vendor form status:', error)
+    throw new Error('벤더별 Form 입력 현황 조회 중 오류가 발생했습니다.')
+  }
+}