1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
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 입력 현황 조회 중 오류가 발생했습니다.')
}
}
|
