diff options
Diffstat (limited to 'lib/evaluation-criteria/stat.ts')
| -rw-r--r-- | lib/evaluation-criteria/stat.ts | 413 |
1 files changed, 413 insertions, 0 deletions
diff --git a/lib/evaluation-criteria/stat.ts b/lib/evaluation-criteria/stat.ts new file mode 100644 index 00000000..c39c8627 --- /dev/null +++ b/lib/evaluation-criteria/stat.ts @@ -0,0 +1,413 @@ +"use server" + +import db from "@/db/db" +import { vendors, contracts, contractItems, forms, formEntries, formMetas, tags, tagClasses, tagClassAttributes, projects } from "@/db/schema" +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 + vendorName: string + formCount: number // 벤더가 가진 form 개수 + tagCount: number // 벤더가 가진 tag 개수 + totalFields: number // 입력해야 하는 총 필드 개수 + completedFields: number // 입력 완료된 필드 개수 + 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 + .selectDistinct({ + id: projects.id, + projectCode: projects.code, + projectName: projects.name, + }) + .from(projects) + .innerJoin(contracts, eq(contracts.projectId, projects.id)) + .orderBy(projects.code) + + return projectList + } catch (error) { + console.error('Error getting projects with contracts:', error) + throw new Error('계약이 있는 프로젝트 조회 중 오류가 발생했습니다.') + } +} + + + +export async function getVendorFormStatus(projectId?: number): Promise<VendorFormStatus[]> { + try { + // 1. 벤더 조회 쿼리 수정 + const vendorList = projectId + ? await db + .selectDistinct({ + vendorId: vendors.id, + vendorName: vendors.vendorName, + }) + .from(vendors) + .innerJoin(contracts, eq(contracts.vendorId, vendors.id)) + .where(eq(contracts.projectId, projectId)) + : await db + .selectDistinct({ + vendorId: vendors.id, + vendorName: vendors.vendorName, + }) + .from(vendors) + .innerJoin(contracts, eq(contracts.vendorId, vendors.id)) + + + 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. 계약 조회 시 projectId 필터 추가 + const vendorContracts = projectId + ? await db + .select({ + id: contracts.id, + projectId: contracts.projectId + }) + .from(contracts) + .where( + and( + eq(contracts.vendorId, vendor.vendorId), + eq(contracts.projectId, projectId) + ) + ) + : await db + .select({ + id: contracts.id, + projectId: contracts.projectId + }) + .from(contracts) + .where(eq(contracts.vendorId, vendor.vendorId)) + + + 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.vendorId, + 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 입력 현황 조회 중 오류가 발생했습니다.') + } +} + + + +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.target_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 |