(대표님) 변경사항 20250707 10시 43분 - unstaged 변경사항 추가

1 files changed, 562 insertions, 0 deletions

diff --git a/lib/evaluation-submit/service.ts b/lib/evaluation-submit/service.ts
new file mode 100644
index 00000000..84d356e7
--- /dev/null
+++ b/lib/evaluation-submit/service.ts
@@ -0,0 +1,562 @@
+'use server'
+
+import db  from "@/db/db";
+import { 
+  reviewerEvaluations,
+  reviewerEvaluationsView,
+  reviewerEvaluationDetails,
+  regEvalCriteriaDetails,
+  regEvalCriteriaView,
+  NewReviewerEvaluationDetail,
+  ReviewerEvaluationDetail,
+  evaluationTargetReviewers,
+  evaluationTargets,
+  regEvalCriteria,
+  periodicEvaluations
+} from "@/db/schema";
+import { and, asc, desc, eq, ilike, or, SQL, count , sql, avg, isNotNull} from "drizzle-orm";
+import { filterColumns } from "@/lib/filter-columns";
+import { DEPARTMENT_CATEGORY_MAPPING, EvaluationFormData, EvaluationQuestionItem, GetSHIEvaluationsSubmitSchema, REVIEWER_TYPES, ReviewerType } from "./validation";
+
+
+// ===============================================================================
+// UTILITY FUNCTIONS
+// ===============================================================================
+
+/**
+ * division과 materialType을 기반으로 reviewerType을 계산합니다
+ */
+function calculateReviewerType(division: string, materialType: string): ReviewerType {
+  if (division === 'SHIP') {
+    if (materialType === 'EQUIPMENT' || materialType === 'EQUIPMENT_BULK') {
+      return REVIEWER_TYPES.EQUIPMENT_SHIP;
+    } else if (materialType === 'BULK') {
+      return REVIEWER_TYPES.BULK_SHIP;
+    }
+    return REVIEWER_TYPES.EQUIPMENT_SHIP; // 기본값
+  } else if (division === 'PLANT') {
+    if (materialType === 'EQUIPMENT' || materialType === 'EQUIPMENT_BULK') {
+      return REVIEWER_TYPES.EQUIPMENT_MARINE;
+    } else if (materialType === 'BULK') {
+      return REVIEWER_TYPES.BULK_MARINE;
+    }
+    return REVIEWER_TYPES.EQUIPMENT_MARINE; // 기본값
+  }
+  return REVIEWER_TYPES.EQUIPMENT_SHIP; // 기본값
+}
+
+/**
+ * reviewerType에 따라 해당하는 점수 필드를 가져옵니다
+ */
+function getScoreByReviewerType(
+  detailRecord: any,
+  reviewerType: ReviewerType
+): number | null {
+  let score: string | null = null;
+  
+  switch (reviewerType) {
+    case REVIEWER_TYPES.EQUIPMENT_SHIP:
+      score = detailRecord.scoreEquipShip;
+      break;
+    case REVIEWER_TYPES.EQUIPMENT_MARINE:
+      score = detailRecord.scoreEquipMarine;
+      break;
+    case REVIEWER_TYPES.BULK_SHIP:
+      score = detailRecord.scoreBulkShip;
+      break;
+    case REVIEWER_TYPES.BULK_MARINE:
+      score = detailRecord.scoreBulkMarine;
+      break;
+  }
+  
+  return score ? parseFloat(score) : null;
+}
+
+
+function getCategoryFilterByDepartment(departmentCode: string): SQL<unknown> {
+  const categoryMapping = DEPARTMENT_CATEGORY_MAPPING as Record<string, string>;
+  const category = categoryMapping[departmentCode] || 'administrator';
+  return eq(regEvalCriteria.category, category);
+}
+
+
+// ===============================================================================
+// MAIN FUNCTIONS
+// ===============================================================================
+
+
+
+/**
+ * 평가 폼 데이터를 조회하고, 응답 레코드가 없으면 생성합니다
+ */
+export async function getEvaluationFormData(reviewerEvaluationId: number): Promise<EvaluationFormData | null> {
+  try {
+    // 1. 리뷰어 평가 정보 조회 (부서 정보 + 평가 대상 정보 포함)
+    const reviewerEvaluationInfo = await db
+      .select({
+        id: reviewerEvaluations.id,
+        periodicEvaluationId: reviewerEvaluations.periodicEvaluationId,
+        evaluationTargetReviewerId: reviewerEvaluations.evaluationTargetReviewerId,
+        isCompleted: reviewerEvaluations.isCompleted,
+        // evaluationTargetReviewers 테이블에서 부서 정보
+        departmentCode: evaluationTargetReviewers.departmentCode,
+        // evaluationTargets 테이블에서 division과 materialType 정보
+        division: evaluationTargets.division,
+        materialType: evaluationTargets.materialType,
+        vendorName: evaluationTargets.vendorName,
+        vendorCode: evaluationTargets.vendorCode,
+      })
+      .from(reviewerEvaluations)
+      .leftJoin(
+        evaluationTargetReviewers,
+        eq(reviewerEvaluations.evaluationTargetReviewerId, evaluationTargetReviewers.id)
+      )
+      .leftJoin(
+        evaluationTargets,
+        eq(evaluationTargetReviewers.evaluationTargetId, evaluationTargets.id)
+      )
+      .where(eq(reviewerEvaluations.id, reviewerEvaluationId))
+      .limit(1);
+
+    if (reviewerEvaluationInfo.length === 0) {
+      throw new Error('Reviewer evaluation not found');
+    }
+
+    const evaluation = reviewerEvaluationInfo[0];
+
+    // 1-1. division과 materialType을 기반으로 reviewerType 계산
+    const reviewerType = calculateReviewerType(evaluation.division, evaluation.materialType);
+
+    // 2. 부서에 따른 카테고리 필터링 로직
+    // const categoryFilter = getCategoryFilterByDepartment("admin");
+    const categoryFilter = getCategoryFilterByDepartment(evaluation.departmentCode);
+
+    // 3. 해당 부서에 맞는 평가 기준들과 답변 옵션들 조회
+    const criteriaWithDetails = await db
+      .select({
+        // 질문 정보 (실제 스키마 기준)
+        criteriaId: regEvalCriteria.id,
+        category: regEvalCriteria.category,        // 평가부문
+        category2: regEvalCriteria.category2,      // 점수유형  
+        item: regEvalCriteria.item,                // 항목
+        classification: regEvalCriteria.classification, // 구분 (실제 질문)
+        range: regEvalCriteria.range,              // 범위 (실제로 평가명)
+        remarks: regEvalCriteria.remarks,
+        scoreType: regEvalCriteria.scoreType,      // ✅ fixed | variable
+        variableScoreMin: regEvalCriteria.variableScoreMin,
+        variableScoreMax: regEvalCriteria.variableScoreMax, 
+        variableScoreUnit: regEvalCriteria.variableScoreUnit, // ✅ 오타 있지만 실제 스키마 따름
+        
+        // 답변 옵션 정보
+        detailId: regEvalCriteriaDetails.id,
+        detail: regEvalCriteriaDetails.detail,
+        orderIndex: regEvalCriteriaDetails.orderIndex,
+        scoreEquipShip: regEvalCriteriaDetails.scoreEquipShip,
+        scoreEquipMarine: regEvalCriteriaDetails.scoreEquipMarine,
+        scoreBulkShip: regEvalCriteriaDetails.scoreBulkShip,
+        scoreBulkMarine: regEvalCriteriaDetails.scoreBulkMarine,
+      })
+      .from(regEvalCriteria)
+      .leftJoin(
+        regEvalCriteriaDetails,
+        eq(regEvalCriteria.id, regEvalCriteriaDetails.criteriaId)
+      )
+      .where(categoryFilter)
+      .orderBy(
+        regEvalCriteria.id,
+        regEvalCriteriaDetails.orderIndex
+      );
+
+    // 4. 기존 응답 데이터 조회 (실제 답변만)
+    const existingResponses = await db
+      .select({
+        id: reviewerEvaluationDetails.id,
+        reviewerEvaluationId: reviewerEvaluationDetails.reviewerEvaluationId,
+        regEvalCriteriaDetailsId: reviewerEvaluationDetails.regEvalCriteriaDetailsId,
+        score: reviewerEvaluationDetails.score,
+        comment: reviewerEvaluationDetails.comment,
+        createdAt: reviewerEvaluationDetails.createdAt,
+        updatedAt: reviewerEvaluationDetails.updatedAt,
+      })
+      .from(reviewerEvaluationDetails)
+      .where(
+        and(
+          eq(reviewerEvaluationDetails.reviewerEvaluationId, reviewerEvaluationId),
+          // ✅ null이 아닌 실제 응답만 조회
+          isNotNull(reviewerEvaluationDetails.regEvalCriteriaDetailsId)
+        )
+      );
+
+    // 5. 질문별로 그룹화하고 답변 옵션들 정리
+    const questionsMap = new Map<number, EvaluationQuestionItem>();
+
+    criteriaWithDetails.forEach(record => {
+      if (!record.detailId) return; // 답변 옵션이 없는 경우 스킵
+
+      const criteriaId = record.criteriaId;
+
+      // 해당 reviewerType에 맞는 점수 가져오기
+      const score = getScoreByReviewerType(record, reviewerType);
+      if (score === null) return; // 해당 리뷰어 타입에 점수가 없으면 스킵
+
+      // 질문이 이미 존재하는지 확인
+      if (!questionsMap.has(criteriaId)) {
+        questionsMap.set(criteriaId, {
+          criteriaId: record.criteriaId,
+          category: record.category,
+          category2: record.category2,
+          item: record.item,
+          classification: record.classification,
+          range: record.range,
+          scoreType: record.scoreType,
+          remarks: record.remarks,
+          availableOptions: [],
+          responseId: null,
+          selectedDetailId: null, // ✅ 초기값은 null (아직 선택하지 않음)
+          currentScore: null,
+          currentComment: null,
+        });
+      }
+
+      // 답변 옵션 추가
+      const question = questionsMap.get(criteriaId)!;
+      question.availableOptions.push({
+        detailId: record.detailId,
+        detail: record.detail,
+        score: score,
+        orderIndex: record.orderIndex,
+      });
+    });
+
+    // 6. ✅ 초기 응답 생성하지 않음 - 사용자가 실제로 답변할 때만 생성
+
+    // 7. 기존 응답 데이터를 질문에 매핑
+    const existingResponsesMap = new Map(
+      existingResponses.map(r => [r.regEvalCriteriaDetailsId, r])
+    );
+
+    // 8. 각 질문에 현재 응답 정보 매핑
+    const questions: EvaluationQuestionItem[] = [];
+    questionsMap.forEach(question => {
+      // 현재 선택된 답변 찾기 (실제 응답이 있는 경우에만)
+      let selectedResponse = null;
+      for (const option of question.availableOptions) {
+        const response = existingResponsesMap.get(option.detailId);
+        if (response) {
+          selectedResponse = response;
+          question.selectedDetailId = option.detailId;
+          break;
+        }
+      }
+
+      if (selectedResponse) {
+        question.responseId = selectedResponse.id;
+        question.currentScore = selectedResponse.score;
+        question.currentComment = selectedResponse.comment;
+      }
+      // ✅ else 케이스: 아직 답변하지 않은 상태 (모든 값이 null)
+
+      questions.push(question);
+    });
+
+    return {
+      evaluationInfo: { 
+        ...evaluation, 
+        reviewerType
+      },
+      questions,
+    };
+
+  } catch (err) {
+    console.error('Error in getEvaluationFormData:', err);
+    return null;
+  }
+}
+
+
+
+/**
+ * 평가 제출 목록을 조회합니다
+ */
+export async function getSHIEvaluationSubmissions(input: GetSHIEvaluationsSubmitSchema, userId: number) {
+  try {
+    const offset = (input.page - 1) * input.perPage;
+
+    // 고급 필터링
+    const advancedWhere = filterColumns({
+      table: reviewerEvaluationsView,
+      filters: input.filters,
+      joinOperator: input.joinOperator,
+    });
+
+    // 전역 검색
+    let globalWhere: SQL<unknown> | undefined;
+    if (input.search) {
+      const s = `%${input.search}%`;
+      globalWhere = or(
+        ilike(reviewerEvaluationsView.isCompleted, s),
+      );
+    }
+
+    const existingReviewer = await db.query.evaluationTargetReviewers.findFirst({
+      where: eq(evaluationTargetReviewers.reviewerUserId, userId),
+    });
+
+
+
+    const finalWhere = and(
+      advancedWhere,
+      globalWhere,
+      eq(reviewerEvaluationsView.evaluationTargetReviewerId, existingReviewer?.id),
+    );
+
+    // 정렬
+    const orderBy = input.sort.length > 0
+      ? input.sort.map((item) => {
+          return item.desc
+            ? desc(reviewerEvaluationsView[item.id])
+            : asc(reviewerEvaluationsView[item.id]);
+        })
+      : [desc(reviewerEvaluationsView.reviewerEvaluationCreatedAt)];
+
+    // 데이터 조회
+    const { data, total } = await db.transaction(async (tx) => {
+      // 메인 데이터 조회
+      const data = await tx
+        .select()
+        .from(reviewerEvaluationsView)
+        .where(finalWhere)
+        .orderBy(...orderBy)
+        .limit(input.perPage)
+        .offset(offset);
+
+      // 총 개수 조회
+      const totalResult = await tx
+        .select({ count: count() })
+        .from(reviewerEvaluationsView)
+        .where(finalWhere);
+
+      const total = totalResult[0]?.count || 0;
+
+      return { data, total };
+    });
+
+    const pageCount = Math.ceil(total / input.perPage);
+    return { data, pageCount };
+  } catch (err) {
+    console.log('Error in getEvaluationSubmissions:', err);
+    return { data: [], pageCount: 0 };
+  }
+}
+
+/**
+ * 특정 평가 제출의 상세 정보를 조회합니다
+ */
+export async function getSHIEvaluationSubmissionById(id: number) {
+  try {
+    const result = await db
+      .select()
+      .from(reviewerEvaluationsView)
+      .where(
+        and(
+          eq(reviewerEvaluationsView.evaluationTargetReviewerId, id),
+        )
+      )
+      .limit(1);
+
+    if (result.length === 0) {
+      return null;
+    }
+
+    const submission = result[0];
+
+    // 응답 데이터도 함께 조회
+    const [generalResponses] = await Promise.all([
+      db
+        .select()
+        .from(reviewerEvaluationDetails)
+        .where(
+          and(
+            eq(reviewerEvaluationDetails.reviewerEvaluationId, id),
+          )
+        ),
+    ]);
+
+    return {
+      ...submission,
+      generalResponses,
+    };
+  } catch (err) {
+    console.error('Error in getEvaluationSubmissionById:', err);
+    return null;
+  }
+}
+
+/**
+ * 평가 응답을 업데이트합니다
+ */
+export async function updateEvaluationResponse(
+  reviewerEvaluationId: number,
+  selectedDetailId: number,
+  comment?: string
+) {
+  try {
+    await db.transaction(async (tx) => {
+      // 1. 선택된 답변 옵션의 정보 조회
+      const selectedDetail = await tx
+        .select()
+        .from(regEvalCriteriaDetails)
+        .where(eq(regEvalCriteriaDetails.id, selectedDetailId))
+        .limit(1);
+
+      if (selectedDetail.length === 0) {
+        throw new Error('Selected detail not found');
+      }
+
+      // 2. reviewerEvaluation 정보 조회 (periodicEvaluationId 포함)
+      const reviewerEvaluationInfo = await tx
+        .select({
+          periodicEvaluationId: reviewerEvaluations.periodicEvaluationId,
+        })
+        .from(reviewerEvaluations)
+        .where(eq(reviewerEvaluations.id, reviewerEvaluationId))
+        .limit(1);
+
+      if (reviewerEvaluationInfo.length === 0) {
+        throw new Error('Reviewer evaluation not found');
+      }
+
+      const { periodicEvaluationId } = reviewerEvaluationInfo[0];
+
+      // 3. periodicEvaluation의 현재 상태 확인 및 업데이트
+      const currentStatus = await tx
+        .select({
+          status: periodicEvaluations.status,
+        })
+        .from(periodicEvaluations)
+        .where(eq(periodicEvaluations.id, periodicEvaluationId))
+        .limit(1);
+
+      if (currentStatus.length > 0 && currentStatus[0].status !== "IN_REVIEW") {
+        await tx
+          .update(periodicEvaluations)
+          .set({
+            status: "IN_REVIEW",
+            updatedAt: new Date(),
+          })
+          .where(eq(periodicEvaluations.id, periodicEvaluationId));
+      }
+
+      // 4. 리뷰어 타입 정보 조회
+      const evaluationInfo = await getEvaluationFormData(reviewerEvaluationId);
+      if (!evaluationInfo) {
+        throw new Error('Evaluation not found');
+      }
+
+      // 5. 해당 리뷰어 타입에 맞는 점수 가져오기
+      const score = getScoreByReviewerType(selectedDetail[0], evaluationInfo.evaluationInfo.reviewerType);
+      if (score === null) {
+        throw new Error('Score not found for this reviewer type');
+      }
+
+      // 6. 같은 질문에 대한 기존 응답들 삭제
+      const criteriaId = selectedDetail[0].criteriaId;
+      await tx
+        .delete(reviewerEvaluationDetails)
+        .where(
+          and(
+            eq(reviewerEvaluationDetails.reviewerEvaluationId, reviewerEvaluationId),
+            sql`${reviewerEvaluationDetails.regEvalCriteriaDetailsId} IN (
+              SELECT id FROM reg_eval_criteria_details WHERE criteria_id = ${criteriaId}
+            )`
+          )
+        );
+
+      // 7. 새로운 응답 생성
+      await tx
+        .insert(reviewerEvaluationDetails)
+        .values({
+          reviewerEvaluationId,
+          regEvalCriteriaDetailsId: selectedDetailId,
+          score: score.toString(),
+          comment,
+        });
+
+      // 8. 카테고리별 점수 계산 및 총점 업데이트
+      await recalculateEvaluationScores(tx, reviewerEvaluationId);
+    });
+
+    return { success: true };
+  } catch (err) {
+    console.error('Error in updateEvaluationResponse:', err);
+    throw err;
+  }
+}
+
+
+/**
+ * 평가 점수 재계산
+ */
+async function recalculateEvaluationScores(tx: any, reviewerEvaluationId: number) {
+  await tx
+    .update(reviewerEvaluations)
+    .set({
+      updatedAt: new Date(),
+    })
+    .where(eq(reviewerEvaluations.id, reviewerEvaluationId));
+}
+
+
+export async function completeEvaluation(
+  reviewerEvaluationId: number,
+  reviewerComment?: string
+) {
+  try {
+    await db.transaction(async (tx) => {
+      // 1. 먼저 해당 리뷰어 평가를 완료로 표시
+      const updatedEvaluation = await tx
+        .update(reviewerEvaluations)
+        .set({
+          isCompleted: true,
+          completedAt: new Date(),
+          reviewerComment,
+          updatedAt: new Date(),
+        })
+        .where(eq(reviewerEvaluations.id, reviewerEvaluationId))
+        .returning({ periodicEvaluationId: reviewerEvaluations.periodicEvaluationId });
+
+      if (updatedEvaluation.length === 0) {
+        throw new Error('Reviewer evaluation not found');
+      }
+
+      const { periodicEvaluationId } = updatedEvaluation[0];
+
+      // 2. 같은 periodicEvaluationId를 가진 모든 리뷰어 평가가 완료되었는지 확인
+      const allEvaluations = await tx
+        .select({
+          isCompleted: reviewerEvaluations.isCompleted,
+        })
+        .from(reviewerEvaluations)
+        .where(eq(reviewerEvaluations.periodicEvaluationId, periodicEvaluationId));
+
+      // 3. 모든 평가가 완료되었는지 확인
+      const allCompleted = allEvaluations.every(evaluation => evaluation.isCompleted);
+
+      // 4. 모든 평가가 완료되었다면 periodicEvaluations의 status 업데이트
+      if (allCompleted) {
+        await tx
+          .update(periodicEvaluations)
+          .set({
+            status: "REVIEW_COMPLETED",
+            updatedAt: new Date(),
+          })
+          .where(eq(periodicEvaluations.id, periodicEvaluationId));
+      }
+    });
+
+    return { success: true };
+  } catch (err) {
+    console.error('Error in completeEvaluation:', err);
+    throw err;
+  }
+}
\ No newline at end of file