diff options
Diffstat (limited to 'lib/evaluation-submit/service.ts')
| -rw-r--r-- | lib/evaluation-submit/service.ts | 562 |
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 |