(대표님) 폼리스트, spreadjs 관련 변경사항, 벤더문서 뷰 쿼리 수정, 이메일 템플릿 추가 등

1 files changed, 208 insertions, 1 deletions

diff --git a/lib/forms/stat.ts b/lib/forms/stat.ts
index 80193c48..054f2462 100644
--- a/lib/forms/stat.ts
+++ b/lib/forms/stat.ts
@@ -2,8 +2,10 @@
 
 import db from "@/db/db"
 import { vendors, contracts, contractItems, forms, formEntries, formMetas, tags, tagClasses, tagClassAttributes, projects } from "@/db/schema"
-import { eq, and } from "drizzle-orm"
+import { eq, and, inArray } from "drizzle-orm"
 import { getEditableFieldsByTag } from "./services"
+import { getServerSession } from "next-auth/next"
+import { authOptions } from "@/app/api/auth/[...nextauth]/route"
 
 interface VendorFormStatus {
   vendorId: number
@@ -15,6 +17,15 @@ interface VendorFormStatus {
   completionRate: number // 완료율 (%)
 }
 
+export interface FormStatusByVendor {
+  tagCount: number;
+  totalFields: number;
+  completedFields: number;
+  completionRate: number;
+  upcomingCount: number;  // 7일 이내 임박한  개수
+  overdueCount: number;   // 지연된 개수
+}
+
 export async function getProjectsWithContracts() {
   try {
     const projectList = await db
@@ -204,3 +215,199 @@ export async function getVendorFormStatus(projectId?: number): Promise<VendorFor
     throw new Error('벤더별 Form 입력 현황 조회 중 오류가 발생했습니다.')
   }
 }
+
+
+
+export async function getFormStatusByVendor(projectId: number, formCode: string): Promise<FormStatusByVendor[]> {
+  try {
+    const session = await getServerSession(authOptions)
+    if (!session?.user?.id) {
+      throw new Error("인증이 필요합니다.")
+    }
+
+    const vendorStatusList: FormStatusByVendor[] = []
+    const vendorId = Number(session.user.companyId)
+
+    const vendorContracts = await db
+      .select({
+        id: contracts.id,
+        projectId: contracts.projectId
+      })
+      .from(contracts)
+      .where(
+        and(
+          eq(contracts.vendorId, vendorId),
+          eq(contracts.projectId, projectId)
+        )
+      )
+
+    const contractIds = vendorContracts.map(v => v.id)
+
+    const contractItemsList = await db
+      .select({
+        id: contractItems.id
+      })
+      .from(contractItems)
+      .where(inArray(contractItems.contractId, contractIds))
+
+    const contractItemIds = contractItemsList.map(v => v.id)
+
+    let vendorFormCount = 0
+    let vendorTagCount = 0
+    let vendorTotalFields = 0
+    let vendorCompletedFields = 0
+    let vendorUpcomingCount = 0  // 7일 이내 임박한 개수
+    let vendorOverdueCount = 0    // 지연된 개수
+    const uniqueTags = new Set<string>()
+    const processedTags = new Set<string>()  // 중복 처리 방지용
+
+    // 현재 날짜와 7일 후 날짜 계산
+    const today = new Date()
+    today.setHours(0, 0, 0, 0)  // 시간 부분 제거
+    const sevenDaysLater = new Date(today)
+    sevenDaysLater.setDate(sevenDaysLater.getDate() + 7)
+
+    // 4. contractItem별 forms 조회
+    const formsList = await db
+      .select({
+        id: forms.id,
+        formCode: forms.formCode,
+        contractItemId: forms.contractItemId
+      })
+      .from(forms)
+      .where(
+        and(
+          inArray(forms.contractItemId, contractItemIds),
+          eq(forms.formCode, formCode)
+        )
+      )
+
+    vendorFormCount += formsList.length
+
+    // 5. formEntries 조회
+    const entriesList = await db
+      .select({
+        id: formEntries.id,
+        formCode: formEntries.formCode,
+        data: formEntries.data
+      })
+      .from(formEntries)
+      .where(
+        and(
+          inArray(formEntries.contractItemId, contractItemIds),
+          eq(formEntries.formCode, formCode)
+        )
+      )
+
+    // 6. TAG별 편집 가능 필드 조회
+    const editableFieldsByTag = new Map<string, string[]>()
+
+    for (const contractItemId of contractItemIds) {
+      const tagFields = await getEditableFieldsByTag(contractItemId, projectId)
+      
+      tagFields.forEach((fields, tagNo) => {
+        if (!editableFieldsByTag.has(tagNo)) {
+          editableFieldsByTag.set(tagNo, fields)
+        } else {
+          const existingFields = editableFieldsByTag.get(tagNo) || []
+          const mergedFields = [...new Set([...existingFields, ...fields])]
+          editableFieldsByTag.set(tagNo, mergedFields)
+        }
+      })
+    }
+
+    for (const entry of entriesList) {
+      const metaResult = await db
+        .select({
+          columns: formMetas.columns
+        })
+        .from(formMetas)
+        .where(
+          and(
+            eq(formMetas.formCode, entry.formCode),
+            eq(formMetas.projectId, projectId)
+          )
+        )
+        .limit(1)
+
+      if (metaResult.length === 0) continue
+
+      const metaColumns = metaResult[0].columns as any[]
+      
+      const inputRequiredFields = metaColumns
+        .filter(col => col.shi === 'IN' || col.shi === 'BOTH')
+        .map(col => col.key)
+
+      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) || []
+          
+          const allRequiredFields = inputRequiredFields.filter(field => 
+            tagEditableFields.includes(field)
+          )
+          
+          // 해당 TAG의 필드 완료 상태 체크
+          let tagHasIncompleteFields = false
+          
+          for (const fieldKey of allRequiredFields) {
+            vendorTotalFields++
+            
+            const fieldValue = dataItem[fieldKey]
+            if (fieldValue !== undefined && fieldValue !== null && fieldValue !== '') {
+              vendorCompletedFields++
+            } else {
+              tagHasIncompleteFields = true
+            }
+          }
+          
+          // 미완료 TAG에 대해서만 날짜 체크 (TAG당 한 번만 처리)
+          if (!processedTags.has(tagNo) && tagHasIncompleteFields) {
+            processedTags.add(tagNo)
+            
+            const targetDate = dataItem.DUE_DATE
+            if (targetDate) {
+              const target = new Date(targetDate)
+              target.setHours(0, 0, 0, 0)  // 시간 부분 제거
+              
+              if (target < today) {
+                // 미완료이면서 지연된 경우 (오늘보다 이전)
+                vendorOverdueCount++
+              } else if (target >= today && target <= sevenDaysLater) {
+                // 미완료이면서 7일 이내 임박한 경우
+                vendorUpcomingCount++
+              }
+            }
+          }
+        }
+      }
+    }
+
+    // 완료율 계산
+    const completionRate = vendorTotalFields > 0 
+      ? Math.round((vendorCompletedFields / vendorTotalFields) * 100 * 10) / 10 
+      : 0
+
+    vendorStatusList.push({
+      tagCount: uniqueTags.size,
+      totalFields: vendorTotalFields,
+      completedFields: vendorCompletedFields,
+      completionRate,
+      upcomingCount: vendorUpcomingCount,
+      overdueCount: vendorOverdueCount
+    })
+
+    return vendorStatusList
+
+  } catch (error) {
+    console.error('Error getting vendor form status:', error)
+    throw new Error('벤더별 Form 입력 현황 조회 중 오류가 발생했습니다.')
+  }
+}
\ No newline at end of file