diff options
Diffstat (limited to 'lib/forms/stat.ts')
| -rw-r--r-- | lib/forms/stat.ts | 209 |
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 |