(대표님/최겸) 20250624 작업사항 10시43분

1 files changed, 125 insertions, 0 deletions

diff --git a/lib/evaluation/service.ts b/lib/evaluation/service.ts
index e69de29b..3cc4ca7d 100644
--- a/lib/evaluation/service.ts
+++ b/lib/evaluation/service.ts
@@ -0,0 +1,125 @@
+import db from "@/db/db"
+import { 
+  periodicEvaluationsView, 
+  type PeriodicEvaluationView 
+} from "@/db/schema"
+import { 
+  and, 
+  asc, 
+  count, 
+  desc, 
+  ilike, 
+  or, 
+  type SQL 
+} from "drizzle-orm"
+import { filterColumns } from "@/lib/filter-columns"
+import { GetEvaluationTargetsSchema } from "../evaluation-target-list/validation";
+
+export async function getPeriodicEvaluations(input: GetEvaluationTargetsSchema) {
+    try {
+      const offset = (input.page - 1) * input.perPage;
+  
+      // ✅ getEvaluationTargets 방식과 동일한 필터링 처리
+      // 1) 고급 필터 조건
+      let advancedWhere: SQL<unknown> | undefined = undefined;
+      if (input.filters && input.filters.length > 0) {
+        advancedWhere = filterColumns({
+          table: periodicEvaluationsView,
+          filters: input.filters,
+          joinOperator: input.joinOperator || 'and',
+        });
+      }
+  
+      // 2) 기본 필터 조건
+      let basicWhere: SQL<unknown> | undefined = undefined;
+      if (input.basicFilters && input.basicFilters.length > 0) {
+        basicWhere = filterColumns({
+          table: periodicEvaluationsView,
+          filters: input.basicFilters,
+          joinOperator: input.basicJoinOperator || 'and',
+        });
+      }
+  
+      // 3) 글로벌 검색 조건
+      let globalWhere: SQL<unknown> | undefined = undefined;
+      if (input.search) {
+        const s = `%${input.search}%`;
+  
+        const validSearchConditions: SQL<unknown>[] = [];
+  
+        // 벤더 정보로 검색
+        const vendorCodeCondition = ilike(periodicEvaluationsView.vendorCode, s);
+        if (vendorCodeCondition) validSearchConditions.push(vendorCodeCondition);
+  
+        const vendorNameCondition = ilike(periodicEvaluationsView.vendorName, s);
+        if (vendorNameCondition) validSearchConditions.push(vendorNameCondition);
+  
+        // 평가 관련 코멘트로 검색
+        const evaluationNoteCondition = ilike(periodicEvaluationsView.evaluationNote, s);
+        if (evaluationNoteCondition) validSearchConditions.push(evaluationNoteCondition);
+  
+        const adminCommentCondition = ilike(periodicEvaluationsView.evaluationTargetAdminComment, s);
+        if (adminCommentCondition) validSearchConditions.push(adminCommentCondition);
+  
+        const consolidatedCommentCondition = ilike(periodicEvaluationsView.evaluationTargetConsolidatedComment, s);
+        if (consolidatedCommentCondition) validSearchConditions.push(consolidatedCommentCondition);
+  
+        // 최종 확정자 이름으로 검색
+        const finalizedByUserNameCondition = ilike(periodicEvaluationsView.finalizedByUserName, s);
+        if (finalizedByUserNameCondition) validSearchConditions.push(finalizedByUserNameCondition);
+  
+        if (validSearchConditions.length > 0) {
+          globalWhere = or(...validSearchConditions);
+        }
+      }
+  
+      // ✅ getEvaluationTargets 방식과 동일한 WHERE 조건 생성
+      const whereConditions: SQL<unknown>[] = [];
+  
+      if (advancedWhere) whereConditions.push(advancedWhere);
+      if (basicWhere) whereConditions.push(basicWhere);
+      if (globalWhere) whereConditions.push(globalWhere);
+  
+      const finalWhere = whereConditions.length > 0 ? and(...whereConditions) : undefined;
+  
+      // ✅ getEvaluationTargets 방식과 동일한 전체 데이터 수 조회
+      const totalResult = await db
+        .select({ count: count() })
+        .from(periodicEvaluationsView)
+        .where(finalWhere);
+  
+      const total = totalResult[0]?.count || 0;
+  
+      if (total === 0) {
+        return { data: [], pageCount: 0, total: 0 };
+      }
+  
+      console.log("Total periodic evaluations:", total);
+  
+      // ✅ getEvaluationTargets 방식과 동일한 정렬 및 페이징 처리된 데이터 조회
+      const orderByColumns = input.sort.map((sort) => {
+        const column = sort.id as keyof typeof periodicEvaluationsView.$inferSelect;
+        return sort.desc ? desc(periodicEvaluationsView[column]) : asc(periodicEvaluationsView[column]);
+      });
+  
+      if (orderByColumns.length === 0) {
+        orderByColumns.push(desc(periodicEvaluationsView.createdAt));
+      }
+  
+      const periodicEvaluationsData = await db
+        .select()
+        .from(periodicEvaluationsView)
+        .where(finalWhere)
+        .orderBy(...orderByColumns)
+        .limit(input.perPage)
+        .offset(offset);
+  
+      const pageCount = Math.ceil(total / input.perPage);
+  
+      return { data: periodicEvaluationsData, pageCount, total };
+    } catch (err) {
+      console.error("Error in getPeriodicEvaluations:", err);
+      // ✅ getEvaluationTargets 방식과 동일한 에러 반환 (total 포함)
+      return { data: [], pageCount: 0, total: 0 };
+    }
+  }
\ No newline at end of file