(대표님) 20250619 1844 KST 작업사항

1 files changed, 133 insertions, 0 deletions

diff --git a/lib/esg-check-list/repository.ts b/lib/esg-check-list/repository.ts
new file mode 100644
index 00000000..dfe04eb3
--- /dev/null
+++ b/lib/esg-check-list/repository.ts
@@ -0,0 +1,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()),
+    };
+}
\ No newline at end of file