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
|
import db from "@/db/db";
import { esgAnswerOptions, esgEvaluationItems, esgEvaluations, esgEvaluationsView, projects } from "@/db/schema";
import { Item, items } from "@/db/schema/items";
import { formListsView, tagTypeClassFormMappings } from "@/db/schema/vendorData";
import {
eq,
inArray,
not,
asc,
desc,
and,
ilike,
gte,
lte,
count,
gt,
} from "drizzle-orm";
import { PgTransaction } from "drizzle-orm/pg-core";
// import { DatabaseConnection } from '@/types/database';
export async function selectEsgEvaluations(
tx: PgTransaction<any, any, any>,
params: {
where?: any;
orderBy?: (ReturnType<typeof asc> | ReturnType<typeof desc>)[];
offset?: number;
limit?: number;
}
) {
const { where, orderBy, offset = 0, limit = 10 } = params;
return await tx
.select()
.from(esgEvaluationsView)
.where(where)
.orderBy(...(orderBy ?? [asc(esgEvaluationsView.createdAt)]))
.offset(offset ?? 0)
.limit(limit ?? 10);
}
export async function countEsgEvaluations(
tx: PgTransaction<any, any, any>,
where?: any
) {
const result = await tx
.select({ count: count() })
.from(esgEvaluationsView)
.where(where);
return result[0]?.count ?? 0;
}
// 상세 데이터 조회 (평가항목과 답변 옵션 포함)
export async function getEsgEvaluationWithDetails(
tx: PgTransaction<any, any, any>,
id: number
) {
// 메인 평가표 정보
const evaluation = await tx
.select()
.from(esgEvaluations)
.where(eq(esgEvaluations.id, id))
.limit(1);
if (!evaluation[0]) return null;
// 평가항목들과 답변 옵션들
const items = await tx
.select({
// 평가항목 필드들
itemId: esgEvaluationItems.id,
evaluationItem: esgEvaluationItems.evaluationItem,
evaluationItemDescription: esgEvaluationItems.evaluationItemDescription,
itemOrderIndex: esgEvaluationItems.orderIndex,
itemIsActive: esgEvaluationItems.isActive,
itemCreatedAt: esgEvaluationItems.createdAt,
itemUpdatedAt: esgEvaluationItems.updatedAt,
// 답변 옵션 필드들
optionId: esgAnswerOptions.id,
answerText: esgAnswerOptions.answerText,
score: esgAnswerOptions.score,
optionOrderIndex: esgAnswerOptions.orderIndex,
optionIsActive: esgAnswerOptions.isActive,
optionCreatedAt: esgAnswerOptions.createdAt,
optionUpdatedAt: esgAnswerOptions.updatedAt,
})
.from(esgEvaluationItems)
.leftJoin(esgAnswerOptions, eq(esgEvaluationItems.id, esgAnswerOptions.esgEvaluationItemId))
.where(eq(esgEvaluationItems.esgEvaluationId, id))
.orderBy(
asc(esgEvaluationItems.orderIndex),
asc(esgAnswerOptions.orderIndex)
);
// 데이터 구조화
const itemsMap = new Map();
items.forEach((row) => {
if (!itemsMap.has(row.itemId)) {
itemsMap.set(row.itemId, {
id: row.itemId,
evaluationItem: row.evaluationItem,
evaluationItemDescription: row.evaluationItemDescription,
orderIndex: row.itemOrderIndex,
isActive: row.itemIsActive,
createdAt: row.itemCreatedAt,
updatedAt: row.itemUpdatedAt,
answerOptions: [],
});
}
if (row.optionId) {
itemsMap.get(row.itemId).answerOptions.push({
id: row.optionId,
answerText: row.answerText,
score: row.score,
orderIndex: row.optionOrderIndex,
isActive: row.optionIsActive,
createdAt: row.optionCreatedAt,
updatedAt: row.optionUpdatedAt,
});
}
});
return {
...evaluation[0],
evaluationItems: Array.from(itemsMap.values()),
};
}
|
