diff options
Diffstat (limited to 'lib/evaluation/service.ts')
| -rw-r--r-- | lib/evaluation/service.ts | 1348 |
1 files changed, 1003 insertions, 345 deletions
diff --git a/lib/evaluation/service.ts b/lib/evaluation/service.ts index 19e41dff..67a692ab 100644 --- a/lib/evaluation/service.ts +++ b/lib/evaluation/service.ts @@ -1,389 +1,1047 @@ 'use server' import db from "@/db/db" -import { +import { evaluationSubmissions, - periodicEvaluationsView, - type PeriodicEvaluationView + evaluationTargetReviewers, + evaluationTargets, + periodicEvaluations, + periodicEvaluationsView, + regEvalCriteria, + regEvalCriteriaDetails, + reviewerEvaluationDetails, + reviewerEvaluations, + users, + type PeriodicEvaluationView } from "@/db/schema" -import { - and, - asc, - count, - desc, - ilike, - or, sql , eq, avg, - type SQL +import { + and, + asc, + count, + desc, + ilike, + or, sql, eq, avg, inArray, + type SQL } from "drizzle-orm" import { filterColumns } from "@/lib/filter-columns" import { GetEvaluationTargetsSchema } from "../evaluation-target-list/validation"; +import { sendEmail } from "../mail/sendEmail" +import { revalidatePath } from "next/cache" +import { DEPARTMENT_CODE_LABELS } from "@/types/evaluation" export async function getPeriodicEvaluations(input: GetEvaluationTargetsSchema) { - try { + 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]); + 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', }); - - if (orderByColumns.length === 0) { - orderByColumns.push(desc(periodicEvaluationsView.createdAt)); + } + + // 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); } - - const periodicEvaluationsData = await db - .select() - .from(periodicEvaluationsView) - .where(finalWhere) - .orderBy(...orderByColumns) - .limit(input.perPage) - .offset(offset); - - const pageCount = Math.ceil(total / input.perPage); + } - console.log(periodicEvaluationsData,"periodicEvaluationsData") - - return { data: periodicEvaluationsData, pageCount, total }; - } catch (err) { - console.error("Error in getPeriodicEvaluations:", err); - // ✅ getEvaluationTargets 방식과 동일한 에러 반환 (total 포함) + // ✅ 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 }; } - } - export interface PeriodicEvaluationsStats { - total: number - pendingSubmission: number - submitted: number - inReview: number - reviewCompleted: number - finalized: number - averageScore: number | null - completionRate: number - averageFinalScore: number | null - documentsSubmittedCount: number - documentsNotSubmittedCount: number - reviewProgress: { - totalReviewers: number - completedReviewers: number - pendingReviewers: number - reviewCompletionRate: number + 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); + + console.log(periodicEvaluationsData, "periodicEvaluationsData") + + return { data: periodicEvaluationsData, pageCount, total }; + } catch (err) { + console.error("Error in getPeriodicEvaluations:", err); + // ✅ getEvaluationTargets 방식과 동일한 에러 반환 (total 포함) + return { data: [], pageCount: 0, total: 0 }; } - - export async function getPeriodicEvaluationsStats(evaluationYear: number): Promise<PeriodicEvaluationsStats> { - try { - // 기본 WHERE 조건: 해당 연도의 평가만 - const baseWhere = eq(periodicEvaluationsView.evaluationYear, evaluationYear) - - // 1. 전체 통계 조회 - const totalStatsResult = await db - .select({ - total: count(), - averageScore: avg(periodicEvaluationsView.totalScore), - averageFinalScore: avg(periodicEvaluationsView.finalScore), - }) - .from(periodicEvaluationsView) - .where(baseWhere) - - const totalStats = totalStatsResult[0] || { - total: 0, - averageScore: null, - averageFinalScore: null +} + +export interface PeriodicEvaluationsStats { + total: number + pendingSubmission: number + submitted: number + inReview: number + reviewCompleted: number + finalized: number + averageScore: number | null + completionRate: number + averageFinalScore: number | null + documentsSubmittedCount: number + documentsNotSubmittedCount: number + reviewProgress: { + totalReviewers: number + completedReviewers: number + pendingReviewers: number + reviewCompletionRate: number + } +} + +export async function getPeriodicEvaluationsStats(evaluationYear: number): Promise<PeriodicEvaluationsStats> { + try { + // 기본 WHERE 조건: 해당 연도의 평가만 + const baseWhere = eq(periodicEvaluationsView.evaluationYear, evaluationYear) + + // 1. 전체 통계 조회 + const totalStatsResult = await db + .select({ + total: count(), + averageScore: avg(periodicEvaluationsView.totalScore), + averageFinalScore: avg(periodicEvaluationsView.finalScore), + }) + .from(periodicEvaluationsView) + .where(baseWhere) + + const totalStats = totalStatsResult[0] || { + total: 0, + averageScore: null, + averageFinalScore: null + } + + // 2. 상태별 카운트 조회 + const statusStatsResult = await db + .select({ + status: periodicEvaluationsView.status, + count: count(), + }) + .from(periodicEvaluationsView) + .where(baseWhere) + .groupBy(periodicEvaluationsView.status) + + // 상태별 카운트를 객체로 변환 + const statusCounts = statusStatsResult.reduce((acc, item) => { + acc[item.status] = item.count + return acc + }, {} as Record<string, number>) + + // 3. 문서 제출 상태 통계 + const documentStatsResult = await db + .select({ + documentsSubmitted: periodicEvaluationsView.documentsSubmitted, + count: count(), + }) + .from(periodicEvaluationsView) + .where(baseWhere) + .groupBy(periodicEvaluationsView.documentsSubmitted) + + const documentCounts = documentStatsResult.reduce((acc, item) => { + if (item.documentsSubmitted) { + acc.submitted = item.count + } else { + acc.notSubmitted = item.count } - - // 2. 상태별 카운트 조회 - const statusStatsResult = await db - .select({ - status: periodicEvaluationsView.status, - count: count(), - }) - .from(periodicEvaluationsView) - .where(baseWhere) - .groupBy(periodicEvaluationsView.status) - - // 상태별 카운트를 객체로 변환 - const statusCounts = statusStatsResult.reduce((acc, item) => { - acc[item.status] = item.count - return acc - }, {} as Record<string, number>) - - // 3. 문서 제출 상태 통계 - const documentStatsResult = await db - .select({ - documentsSubmitted: periodicEvaluationsView.documentsSubmitted, - count: count(), + return acc + }, { submitted: 0, notSubmitted: 0 }) + + // 4. 리뷰어 진행 상황 통계 + const reviewProgressResult = await db + .select({ + totalReviewers: sql<number>`SUM(${periodicEvaluationsView.totalReviewers})`.as('total_reviewers'), + completedReviewers: sql<number>`SUM(${periodicEvaluationsView.completedReviewers})`.as('completed_reviewers'), + pendingReviewers: sql<number>`SUM(${periodicEvaluationsView.pendingReviewers})`.as('pending_reviewers'), + }) + .from(periodicEvaluationsView) + .where(baseWhere) + + const reviewProgress = reviewProgressResult[0] || { + totalReviewers: 0, + completedReviewers: 0, + pendingReviewers: 0, + } + + // 5. 완료율 계산 + const finalizedCount = statusCounts['FINALIZED'] || 0 + const totalCount = totalStats.total + const completionRate = totalCount > 0 ? Math.round((finalizedCount / totalCount) * 100) : 0 + + // 6. 리뷰 완료율 계산 + const reviewCompletionRate = reviewProgress.totalReviewers > 0 + ? Math.round((reviewProgress.completedReviewers / reviewProgress.totalReviewers) * 100) + : 0 + + // 7. 평균 점수 포맷팅 (소수점 1자리) + const formatScore = (score: string | number | null): number | null => { + if (score === null || score === undefined) return null + return Math.round(Number(score) * 10) / 10 + } + + return { + total: totalCount, + pendingSubmission: statusCounts['PENDING_SUBMISSION'] || 0, + submitted: statusCounts['SUBMITTED'] || 0, + inReview: statusCounts['IN_REVIEW'] || 0, + reviewCompleted: statusCounts['REVIEW_COMPLETED'] || 0, + finalized: finalizedCount, + averageScore: formatScore(totalStats.averageScore), + averageFinalScore: formatScore(totalStats.averageFinalScore), + completionRate, + documentsSubmittedCount: documentCounts.submitted, + documentsNotSubmittedCount: documentCounts.notSubmitted, + reviewProgress: { + totalReviewers: reviewProgress.totalReviewers, + completedReviewers: reviewProgress.completedReviewers, + pendingReviewers: reviewProgress.pendingReviewers, + reviewCompletionRate, + }, + } + + } catch (error) { + console.error('Error in getPeriodicEvaluationsStats:', error) + // 에러 발생 시 기본값 반환 + return { + total: 0, + pendingSubmission: 0, + submitted: 0, + inReview: 0, + reviewCompleted: 0, + finalized: 0, + averageScore: null, + averageFinalScore: null, + completionRate: 0, + documentsSubmittedCount: 0, + documentsNotSubmittedCount: 0, + reviewProgress: { + totalReviewers: 0, + completedReviewers: 0, + pendingReviewers: 0, + reviewCompletionRate: 0, + }, + } + } +} + + + +interface RequestDocumentsData { + periodicEvaluationId: number + companyId: number + evaluationYear: number + evaluationRound: string + message: string +} + +export async function requestDocumentsFromVendors(data: RequestDocumentsData[]) { + try { + // 각 평가에 대해 evaluationSubmissions 레코드 생성 + const submissions = await Promise.all( + data.map(async (item) => { + // 이미 해당 periodicEvaluationId와 companyId로 생성된 submission이 있는지 확인 + const existingSubmission = await db.query.evaluationSubmissions.findFirst({ + where: and( + eq(evaluationSubmissions.periodicEvaluationId, item.periodicEvaluationId), + eq(evaluationSubmissions.companyId, item.companyId) + ) }) - .from(periodicEvaluationsView) - .where(baseWhere) - .groupBy(periodicEvaluationsView.documentsSubmitted) - - const documentCounts = documentStatsResult.reduce((acc, item) => { - if (item.documentsSubmitted) { - acc.submitted = item.count + + if (existingSubmission) { + // 이미 존재하면 reviewComments만 업데이트 + const [updated] = await db + .update(evaluationSubmissions) + .set({ + reviewComments: item.message, + updatedAt: new Date() + }) + .where(eq(evaluationSubmissions.id, existingSubmission.id)) + .returning() + + return updated } else { - acc.notSubmitted = item.count + // 새로 생성 + const [created] = await db + .insert(evaluationSubmissions) + .values({ + periodicEvaluationId: item.periodicEvaluationId, + companyId: item.companyId, + evaluationYear: item.evaluationYear, + evaluationRound: item.evaluationRound, + submissionStatus: 'draft', // 기본값 + reviewComments: item.message, + // 진행률 관련 필드들은 기본값 0으로 설정됨 + totalGeneralItems: 0, + completedGeneralItems: 0, + totalEsgItems: 0, + completedEsgItems: 0, + isActive: true + }) + .returning() + + return created } - return acc - }, { submitted: 0, notSubmitted: 0 }) - - // 4. 리뷰어 진행 상황 통계 - const reviewProgressResult = await db + }) + ) + + + return { + success: true, + message: `${submissions.length}개 업체에 자료 요청이 완료되었습니다.`, + submissions + } + + } catch (error) { + console.error("Error requesting documents from vendors:", error) + return { + success: false, + message: "자료 요청 중 오류가 발생했습니다.", + error: error instanceof Error ? error.message : "Unknown error" + } + } +} + +// 기존 요청 상태 확인 함수 추가 +export async function checkExistingSubmissions(periodicEvaluationIds: number[]) { + try { + const existingSubmissions = await db.query.evaluationSubmissions.findMany({ + where: (submissions) => { + // periodicEvaluationIds 배열에 포함된 ID들을 확인 + return periodicEvaluationIds.length === 1 + ? eq(submissions.periodicEvaluationId, periodicEvaluationIds[0]) + : periodicEvaluationIds.length > 1 + ? or(...periodicEvaluationIds.map(id => eq(submissions.periodicEvaluationId, id))) + : eq(submissions.id, -1) // 빈 배열인 경우 결과 없음 + }, + columns: { + id: true, + periodicEvaluationId: true, + companyId: true, + createdAt: true, + reviewComments: true + } + }) + + return existingSubmissions + } catch (error) { + console.error("Error checking existing submissions:", error) + return [] + } +} + + +// ================================================================ +// 타입 정의 +// ================================================================ +interface ReviewerInfo { + id: number + name: string + email: string + deptName: string | null + departmentCode: string + evaluationTargetId: number + evaluationTargetReviewerId: number +} + +interface ReviewerEvaluationRequestData { + periodicEvaluationId: number + evaluationTargetReviewerId: number + message: string +} + +// ================================================================ +// 1. 평가 대상별 리뷰어 정보 가져오기 +// ================================================================ +export async function getReviewersForEvaluations( + evaluationTargetIds: number[] +): Promise<ReviewerInfo[]> { + try { + if (evaluationTargetIds.length === 0) { + return [] + } + + // evaluation_target_reviewers와 users 테이블 조인 + const reviewers = await db + .select({ + id: users.id, + name: users.name, + email: users.email, + deptName: users.deptName, + departmentCode: evaluationTargetReviewers.departmentCode, + evaluationTargetId: evaluationTargetReviewers.evaluationTargetId, + evaluationTargetReviewerId: evaluationTargetReviewers.id, + }) + .from(evaluationTargetReviewers) + .innerJoin(users, eq(evaluationTargetReviewers.reviewerUserId, users.id)) + .where( + and( + inArray(evaluationTargetReviewers.evaluationTargetId, evaluationTargetIds), + eq(users.isActive, true) // 활성 사용자만 + ) + ) + .orderBy(evaluationTargetReviewers.evaluationTargetId, users.name) + + return reviewers + } catch (error) { + console.error('Error fetching reviewers for evaluations:', error) + throw new Error('평가자 정보를 가져오는데 실패했습니다.') + } +} +// ================================================================ +// 2. 리뷰어 평가 요청 생성 및 알림 발송 +// ================================================================ +export async function createReviewerEvaluationsRequest( + requestData: ReviewerEvaluationRequestData[] +): Promise<{ success: boolean; message: string }> { + try { + if (requestData.length === 0) { + return { + success: false, + message: "요청할 평가 데이터가 없습니다." + } + } + + console.log('평가 요청 데이터:', requestData) + + // 트랜잭션으로 처리 + await db.transaction(async (tx) => { + // 1. 기존 reviewerEvaluations 확인 (중복 방지) + const existingEvaluations = await tx .select({ - totalReviewers: sql<number>`SUM(${periodicEvaluationsView.totalReviewers})`.as('total_reviewers'), - completedReviewers: sql<number>`SUM(${periodicEvaluationsView.completedReviewers})`.as('completed_reviewers'), - pendingReviewers: sql<number>`SUM(${periodicEvaluationsView.pendingReviewers})`.as('pending_reviewers'), + periodicEvaluationId: reviewerEvaluations.periodicEvaluationId, + evaluationTargetReviewerId: reviewerEvaluations.evaluationTargetReviewerId, }) - .from(periodicEvaluationsView) - .where(baseWhere) - - const reviewProgress = reviewProgressResult[0] || { - totalReviewers: 0, - completedReviewers: 0, - pendingReviewers: 0, + .from(reviewerEvaluations) + .where( + and( + inArray( + reviewerEvaluations.periodicEvaluationId, + requestData.map(r => r.periodicEvaluationId) + ), + inArray( + reviewerEvaluations.evaluationTargetReviewerId, + requestData.map(r => r.evaluationTargetReviewerId) + ) + ) + ) + + // 2. 중복되지 않는 새로운 평가 요청만 필터링 + const newRequestData = requestData.filter(request => + !existingEvaluations.some(existing => + existing.periodicEvaluationId === request.periodicEvaluationId && + existing.evaluationTargetReviewerId === request.evaluationTargetReviewerId + ) + ) + + if (newRequestData.length === 0) { + throw new Error("모든 평가 요청이 이미 생성되어 있습니다.") } - - // 5. 완료율 계산 - const finalizedCount = statusCounts['FINALIZED'] || 0 - const totalCount = totalStats.total - const completionRate = totalCount > 0 ? Math.round((finalizedCount / totalCount) * 100) : 0 - - // 6. 리뷰 완료율 계산 - const reviewCompletionRate = reviewProgress.totalReviewers > 0 - ? Math.round((reviewProgress.completedReviewers / reviewProgress.totalReviewers) * 100) - : 0 - - // 7. 평균 점수 포맷팅 (소수점 1자리) - const formatScore = (score: string | number | null): number | null => { - if (score === null || score === undefined) return null - return Math.round(Number(score) * 10) / 10 + + console.log(`새로 생성할 평가 요청: ${newRequestData.length}개`) + + // 3. reviewerEvaluations 테이블에 레코드 생성 + const reviewerEvaluationInsertData = newRequestData.map(request => ({ + periodicEvaluationId: request.periodicEvaluationId, + evaluationTargetReviewerId: request.evaluationTargetReviewerId, + isCompleted: false, + // 기본값들 + processScore: null, + priceScore: null, + deliveryScore: null, + selfEvaluationScore: null, + participationBonus: "0", + qualityDeduction: "0", + totalScore: null, + grade: null, + completedAt: null, + reviewerComment: null, + })) + + const insertedEvaluations = await tx.insert(reviewerEvaluations).values(reviewerEvaluationInsertData).returning({ id: reviewerEvaluations.id }) + console.log(`reviewerEvaluations 레코드 생성 완료: ${insertedEvaluations.length}개`) + + // 4. 이메일 발송을 위한 상세 정보 수집 + try { + await sendEvaluationRequestEmails(tx, newRequestData, requestData[0]?.message || "") + } catch (emailError) { + console.error('이메일 발송 중 오류:', emailError) + // 이메일 발송 실패해도 전체 트랜잭션은 성공으로 처리 } - + }) + + const totalReviewers = [...new Set(requestData.map(r => r.evaluationTargetReviewerId))].length + const totalEvaluations = [...new Set(requestData.map(r => r.periodicEvaluationId))].length + + return { + success: true, + message: `${totalEvaluations}개 평가에 대해 ${totalReviewers}명의 평가자에게 요청이 발송되었습니다.` + } + + } catch (error) { + console.error('Error creating reviewer evaluation requests:', error) + return { + success: false, + message: error instanceof Error ? error.message : "평가 요청 생성 중 오류가 발생했습니다." + } + } +} + + +const getDepartmentLabel = (code: string): string => { + return DEPARTMENT_CODE_LABELS[code as keyof typeof DEPARTMENT_CODE_LABELS] || code +} + +// ================================================================ +// 이메일 발송 헬퍼 함수 (완전 새로 작성) +// ================================================================ +async function sendEvaluationRequestEmails( + tx: any, + requestData: ReviewerEvaluationRequestData[], + message: string +) { + try { + + // 1. 평가 정보 수집 (periodicEvaluations + evaluationTargets 조인) + const evaluationIds = [...new Set(requestData.map(r => r.periodicEvaluationId))] + + const evaluationDetails = await tx + .select({ + periodicEvaluationId: periodicEvaluations.id, + evaluationTargetId: periodicEvaluations.evaluationTargetId, + evaluationYear: evaluationTargets.evaluationYear, + evaluationPeriod: periodicEvaluations.evaluationPeriod, + vendorCode: evaluationTargets.vendorCode, + vendorName: evaluationTargets.vendorName, + division: evaluationTargets.division, + materialType: evaluationTargets.materialType, + domesticForeign: evaluationTargets.domesticForeign, + submissionDeadline: periodicEvaluations.submissionDeadline, + }) + .from(periodicEvaluations) + .innerJoin(evaluationTargets, eq(periodicEvaluations.evaluationTargetId, evaluationTargets.id)) + .where(inArray(periodicEvaluations.id, evaluationIds)) + + console.log('평가 상세 정보:', evaluationDetails) + + // 2. 리뷰어 정보 수집 + const reviewerIds = [...new Set(requestData.map(r => r.evaluationTargetReviewerId))] + console.log('리뷰어 ID들:', reviewerIds) + + const reviewerDetails = await tx + .select({ + evaluationTargetReviewerId: evaluationTargetReviewers.id, + evaluationTargetId: evaluationTargetReviewers.evaluationTargetId, + departmentCode: evaluationTargetReviewers.departmentCode, + reviewerUserId: evaluationTargetReviewers.reviewerUserId, + userName: users.name, + userEmail: users.email, + deptName: users.deptName, + }) + .from(evaluationTargetReviewers) + .innerJoin(users, eq(evaluationTargetReviewers.reviewerUserId, users.id)) + .where(inArray(evaluationTargetReviewers.id, reviewerIds)) + + console.log('리뷰어 상세 정보:', reviewerDetails) + + // 3. 평가별로 그룹핑 (각 평가에 대한 리뷰어들) + const evaluationGroups = evaluationDetails.map(evaluation => { + const relatedRequests = requestData.filter(req => req.periodicEvaluationId === evaluation.periodicEvaluationId) + const evaluationReviewers = relatedRequests.map(req => { + const reviewer = reviewerDetails.find(r => r.evaluationTargetReviewerId === req.evaluationTargetReviewerId) + return { + ...reviewer, + departmentLabel: getDepartmentLabel(reviewer?.departmentCode || ''), + } + }).filter(Boolean) + return { - total: totalCount, - pendingSubmission: statusCounts['PENDING_SUBMISSION'] || 0, - submitted: statusCounts['SUBMITTED'] || 0, - inReview: statusCounts['IN_REVIEW'] || 0, - reviewCompleted: statusCounts['REVIEW_COMPLETED'] || 0, - finalized: finalizedCount, - averageScore: formatScore(totalStats.averageScore), - averageFinalScore: formatScore(totalStats.averageFinalScore), - completionRate, - documentsSubmittedCount: documentCounts.submitted, - documentsNotSubmittedCount: documentCounts.notSubmitted, - reviewProgress: { - totalReviewers: reviewProgress.totalReviewers, - completedReviewers: reviewProgress.completedReviewers, - pendingReviewers: reviewProgress.pendingReviewers, - reviewCompletionRate, - }, + ...evaluation, + reviewers: evaluationReviewers, + relatedRequests } - - } catch (error) { - console.error('Error in getPeriodicEvaluationsStats:', error) - // 에러 발생 시 기본값 반환 - return { - total: 0, - pendingSubmission: 0, - submitted: 0, - inReview: 0, - reviewCompleted: 0, - finalized: 0, - averageScore: null, - averageFinalScore: null, - completionRate: 0, - documentsSubmittedCount: 0, - documentsNotSubmittedCount: 0, - reviewProgress: { - totalReviewers: 0, - completedReviewers: 0, - pendingReviewers: 0, - reviewCompletionRate: 0, - }, + }) + + console.log('평가 그룹:', evaluationGroups) + + // 4. 각 리뷰어에게 개별 이메일 발송 + const emailPromises = [] + + for (const group of evaluationGroups) { + for (const reviewer of group.reviewers) { + if (!reviewer?.userEmail) { + console.log(`이메일 주소가 없는 리뷰어 스킵: ${reviewer?.userName}`) + continue + } + + // 해당 리뷰어를 제외한 다른 리뷰어들 + const otherReviewers = group.reviewers.filter(r => r?.evaluationTargetReviewerId !== reviewer.evaluationTargetReviewerId) + + console.log(`${reviewer.userName}(${reviewer.userEmail})에게 이메일 발송 준비`) + + const emailPromise = sendEmail({ + to: reviewer.userEmail, + subject: `[평가 요청] ${group.vendorName} - ${group.evaluationYear}년 ${group.evaluationPeriod} 정기평가`, + template: "evaluation-request", + context: { + language: "ko", + reviewerName: reviewer.userName, + departmentLabel: reviewer.departmentLabel, + evaluation: { + vendorName: group.vendorName, + vendorCode: group.vendorCode, + evaluationYear: group.evaluationYear, + evaluationPeriod: group.evaluationPeriod, + division: group.division, + materialType: group.materialType, + domesticForeign: group.domesticForeign, + submissionDeadline: group.submissionDeadline ? new Date(group.submissionDeadline).toLocaleDateString('ko-KR') : null, + }, + otherReviewers: otherReviewers.map(r => ({ + name: r?.userName, + department: r?.departmentLabel, + email: r?.userEmail + })).filter(r => r.name), + message: message || "협력업체 정기평가를 진행해 주시기 바랍니다.", + evaluationUrl: `${process.env.NEXT_PUBLIC_APP_URL}/evaluations/${group.periodicEvaluationId}/review` + }, + }).catch(error => { + console.error(`${reviewer.userEmail}에게 이메일 발송 실패:`, error) + return null + }) + + emailPromises.push(emailPromise) } } + + // 5. 모든 이메일 발송 대기 + const emailResults = await Promise.allSettled(emailPromises) + const successCount = emailResults.filter(result => result.status === 'fulfilled').length + const failureCount = emailResults.filter(result => result.status === 'rejected').length + + console.log(`이메일 발송 완료: 성공 ${successCount}개, 실패 ${failureCount}개`) + + if (failureCount > 0) { + console.error('실패한 이메일들:', emailResults.filter(r => r.status === 'rejected').map(r => r.reason)) + } + + } catch (error) { + console.error('Error sending evaluation request emails:', error) + throw error // 이메일 발송 실패도 에러로 처리하려면 throw, 아니면 console.error만 } +} +// ================================================================ +// 3. 리뷰어별 평가 완료 상태 확인 (선택적 기능) +// ================================================================ +export async function getReviewerEvaluationStatus( + periodicEvaluationIds: number[] +): Promise<Array<{ + periodicEvaluationId: number + totalReviewers: number + completedReviewers: number + completionRate: number +}>> { + try { + if (periodicEvaluationIds.length === 0) { + return [] + } + const evaluationStatus = await db + .select({ + periodicEvaluationId: reviewerEvaluations.periodicEvaluationId, + totalReviewers: db.$count(reviewerEvaluations.id), + completedReviewers: db.$count( + reviewerEvaluations.id, + eq(reviewerEvaluations.isCompleted, true) + ), + }) + .from(reviewerEvaluations) + .where(inArray(reviewerEvaluations.periodicEvaluationId, periodicEvaluationIds)) + .groupBy(reviewerEvaluations.periodicEvaluationId) + return evaluationStatus.map(status => ({ + ...status, + completionRate: status.totalReviewers > 0 + ? Math.round((status.completedReviewers / status.totalReviewers) * 100) + : 0 + })) - interface RequestDocumentsData { - periodicEvaluationId: number - companyId: number - evaluationYear: number - evaluationRound: string - message: string + } catch (error) { + console.error('Error fetching reviewer evaluation status:', error) + throw new Error('평가 완료 상태를 가져오는데 실패했습니다.') } - - export async function requestDocumentsFromVendors(data: RequestDocumentsData[]) { - try { - // 각 평가에 대해 evaluationSubmissions 레코드 생성 - const submissions = await Promise.all( - data.map(async (item) => { - // 이미 해당 periodicEvaluationId와 companyId로 생성된 submission이 있는지 확인 - const existingSubmission = await db.query.evaluationSubmissions.findFirst({ - where: and( - eq(evaluationSubmissions.periodicEvaluationId, item.periodicEvaluationId), - eq(evaluationSubmissions.companyId, item.companyId) - ) +} + +// 평가 확정 데이터 타입 +interface FinalizeEvaluationData { + id: number + finalScore: number + finalGrade: "S" | "A" | "B" | "C" | "D" +} + +/** + * 평가를 최종 확정합니다 + */ +export async function finalizeEvaluations( + evaluationData: FinalizeEvaluationData[] +) { + try { + // 현재 사용자 정보 가져오기 + const currentUser = await getCurrentUser() + if (!currentUser) { + throw new Error("인증이 필요합니다") + } + + // 트랜잭션으로 여러 평가를 한번에 처리 + await db.transaction(async (tx) => { + const now = new Date() + + // 각 평가를 순차적으로 처리 + for (const evaluation of evaluationData) { + // 1. 평가 상태가 REVIEW_COMPLETED인지 확인 + const existingEvaluation = await tx + .select({ + id: periodicEvaluations.id, + status: periodicEvaluations.status, }) - - if (existingSubmission) { - // 이미 존재하면 reviewComments만 업데이트 - const [updated] = await db - .update(evaluationSubmissions) - .set({ - reviewComments: item.message, - updatedAt: new Date() - }) - .where(eq(evaluationSubmissions.id, existingSubmission.id)) - .returning() - - return updated - } else { - // 새로 생성 - const [created] = await db - .insert(evaluationSubmissions) - .values({ - periodicEvaluationId: item.periodicEvaluationId, - companyId: item.companyId, - evaluationYear: item.evaluationYear, - evaluationRound: item.evaluationRound, - submissionStatus: 'draft', // 기본값 - reviewComments: item.message, - // 진행률 관련 필드들은 기본값 0으로 설정됨 - totalGeneralItems: 0, - completedGeneralItems: 0, - totalEsgItems: 0, - completedEsgItems: 0, - isActive: true - }) - .returning() - - return created - } - }) - ) - - - return { - success: true, - message: `${submissions.length}개 업체에 자료 요청이 완료되었습니다.`, - submissions + .from(periodicEvaluations) + .where(eq(periodicEvaluations.id, evaluation.id)) + .limit(1) + + if (existingEvaluation.length === 0) { + throw new Error(`평가를 찾을 수 없습니다: ID ${evaluation.id}`) + } + + if (existingEvaluation[0].status !== "REVIEW_COMPLETED") { + throw new Error( + `평가 ${evaluation.id}는 검토 완료 상태가 아닙니다. 현재 상태: ${existingEvaluation[0].status}` + ) + } + + // 2. 평가를 최종 확정으로 업데이트 + await tx + .update(periodicEvaluations) + .set({ + finalScore: evaluation.finalScore.toString(), + finalGrade: evaluation.finalGrade, + status: "FINALIZED", + finalizedAt: now, + finalizedBy: currentUser.id, + updatedAt: now, + }) + .where(eq(periodicEvaluations.id, evaluation.id)) } - - } catch (error) { - console.error("Error requesting documents from vendors:", error) - return { - success: false, - message: "자료 요청 중 오류가 발생했습니다.", - error: error instanceof Error ? error.message : "Unknown error" + }) + + revalidatePath("/evcp/evaluation") + revalidatePath("/procurement/evaluation") + + return { + success: true, + message: `${evaluationData.length}건의 평가가 성공적으로 확정되었습니다`, + } + } catch (error) { + console.error("Error finalizing evaluations:", error) + throw new Error( + error instanceof Error + ? error.message + : "평가 확정 중 오류가 발생했습니다" + ) + } +} + +/** + * 평가 확정을 취소합니다 (필요시 추가) + */ +export async function unfinalizeEvaluations(evaluationIds: number[]) { + try { + const currentUser = await getCurrentUser() + if (!currentUser) { + throw new Error("인증이 필요합니다") + } + + await db.transaction(async (tx) => { + for (const evaluationId of evaluationIds) { + // 1. 평가 상태가 FINALIZED인지 확인 + const existingEvaluation = await tx + .select({ + id: periodicEvaluations.id, + status: periodicEvaluations.status, + }) + .from(periodicEvaluations) + .where(eq(periodicEvaluations.id, evaluationId)) + .limit(1) + + if (existingEvaluation.length === 0) { + throw new Error(`평가를 찾을 수 없습니다: ID ${evaluationId}`) + } + + if (existingEvaluation[0].status !== "FINALIZED") { + throw new Error( + `평가 ${evaluationId}는 확정 상태가 아닙니다. 현재 상태: ${existingEvaluation[0].status}` + ) + } + + // 2. 확정 해제 - 검토 완료 상태로 되돌림 + await tx + .update(periodicEvaluations) + .set({ + finalScore: null, + finalGrade: null, + status: "REVIEW_COMPLETED", + finalizedAt: null, + finalizedBy: null, + updatedAt: new Date(), + }) + .where(eq(periodicEvaluations.id, evaluationId)) } + }) + + revalidatePath("/evcp/evaluation") + revalidatePath("/procurement/evaluation") + + return { + success: true, + message: `${evaluationIds.length}건의 평가 확정이 취소되었습니다`, } + } catch (error) { + console.error("Error unfinalizing evaluations:", error) + throw new Error( + error instanceof Error + ? error.message + : "평가 확정 취소 중 오류가 발생했습니다" + ) } +} + + +// 평가 상세 정보 타입 +export interface EvaluationDetailData { + // 리뷰어 정보 + reviewerEvaluationId: number + reviewerName: string + reviewerEmail: string + departmentCode: string + departmentName: string + isCompleted: boolean + completedAt: Date | null + reviewerComment: string | null - // 기존 요청 상태 확인 함수 추가 - export async function checkExistingSubmissions(periodicEvaluationIds: number[]) { - try { - const existingSubmissions = await db.query.evaluationSubmissions.findMany({ - where: (submissions) => { - // periodicEvaluationIds 배열에 포함된 ID들을 확인 - return periodicEvaluationIds.length === 1 - ? eq(submissions.periodicEvaluationId, periodicEvaluationIds[0]) - : periodicEvaluationIds.length > 1 - ? or(...periodicEvaluationIds.map(id => eq(submissions.periodicEvaluationId, id))) - : eq(submissions.id, -1) // 빈 배열인 경우 결과 없음 - }, - columns: { - id: true, - periodicEvaluationId: true, - companyId: true, - createdAt: true, - reviewComments: true - } + // 평가 항목별 상세 + evaluationItems: { + // 평가 기준 정보 + criteriaId: number + category: string + category2: string + item: string + classification: string + range: string | null + remarks: string | null + scoreType: string + + // 선택된 옵션 정보 (fixed 타입인 경우) + selectedDetailId: number | null + selectedDetail: string | null + + // 점수 및 의견 + score: number | null + comment: string | null + }[] +} + + +/** + * 특정 정기평가의 상세 정보를 조회합니다 + */ +export async function getEvaluationDetails(periodicEvaluationId: number): Promise<{ + evaluationInfo: { + id: number + vendorName: string + vendorCode: string + evaluationYear: number + division: string + status: string + } + reviewerDetails: EvaluationDetailData[] +}> { + try { + // 1. 평가 기본 정보 조회 + const evaluationInfo = await db + .select({ + id: periodicEvaluations.id, + vendorName: evaluationTargets.vendorName, + vendorCode: evaluationTargets.vendorCode, + evaluationYear: evaluationTargets.evaluationYear, + division: evaluationTargets.division, + status: periodicEvaluations.status, }) - - return existingSubmissions - } catch (error) { - console.error("Error checking existing submissions:", error) - return [] + .from(periodicEvaluations) + .leftJoin(evaluationTargets, eq(periodicEvaluations.evaluationTargetId, evaluationTargets.id)) + .where(eq(periodicEvaluations.id, periodicEvaluationId)) + .limit(1) + + if (evaluationInfo.length === 0) { + throw new Error("평가를 찾을 수 없습니다") } - }
\ No newline at end of file + + // 2. 리뷰어별 평가 상세 정보 조회 + const reviewerDetailsRaw = await db + .select({ + // 리뷰어 평가 기본 정보 + reviewerEvaluationId: reviewerEvaluations.id, + reviewerName: users.name, + reviewerEmail: users.email, + departmentCode: evaluationTargetReviewers.departmentCode, + isCompleted: reviewerEvaluations.isCompleted, + completedAt: reviewerEvaluations.completedAt, + reviewerComment: reviewerEvaluations.reviewerComment, + + // 평가 항목 상세 + detailId: reviewerEvaluationDetails.id, + criteriaId: regEvalCriteria.id, + category: regEvalCriteria.category, + category2: regEvalCriteria.category2, + item: regEvalCriteria.item, + classification: regEvalCriteria.classification, + range: regEvalCriteria.range, + remarks: regEvalCriteria.remarks, + scoreType: regEvalCriteria.scoreType, + + // 선택된 옵션 정보 + selectedDetailId: reviewerEvaluationDetails.regEvalCriteriaDetailsId, + selectedDetail: regEvalCriteriaDetails.detail, + + // 점수 및 의견 + score: reviewerEvaluationDetails.score, + comment: reviewerEvaluationDetails.comment, + }) + .from(reviewerEvaluations) + .leftJoin(evaluationTargetReviewers, eq(reviewerEvaluations.evaluationTargetReviewerId, evaluationTargetReviewers.id)) + .leftJoin(users, eq(evaluationTargetReviewers.reviewerUserId, users.id)) + .leftJoin(reviewerEvaluationDetails, eq(reviewerEvaluations.id, reviewerEvaluationDetails.reviewerEvaluationId)) + .leftJoin(regEvalCriteriaDetails, eq(reviewerEvaluationDetails.regEvalCriteriaDetailsId, regEvalCriteriaDetails.id)) + .leftJoin(regEvalCriteria, eq(regEvalCriteriaDetails.criteriaId, regEvalCriteria.id)) + .where(eq(reviewerEvaluations.periodicEvaluationId, periodicEvaluationId)) + .orderBy(evaluationTargetReviewers.departmentCode, regEvalCriteria.category, regEvalCriteria.classification) + + // 3. 리뷰어별로 그룹화 + const reviewerDetailsMap = new Map<number, EvaluationDetailData>() + + reviewerDetailsRaw.forEach(row => { + if (!reviewerDetailsMap.has(row.reviewerEvaluationId)) { + reviewerDetailsMap.set(row.reviewerEvaluationId, { + reviewerEvaluationId: row.reviewerEvaluationId, + reviewerName: row.reviewerName || "", + reviewerEmail: row.reviewerEmail || "", + departmentCode: row.departmentCode || "", + departmentName: DEPARTMENT_CODE_LABELS[row.departmentCode as keyof typeof DEPARTMENT_CODE_LABELS] || row.departmentCode || "", + isCompleted: row.isCompleted || false, + completedAt: row.completedAt, + reviewerComment: row.reviewerComment, + evaluationItems: [] + }) + } + + // 평가 항목이 있는 경우에만 추가 + if (row.criteriaId && row.detailId) { + const reviewer = reviewerDetailsMap.get(row.reviewerEvaluationId)! + reviewer.evaluationItems.push({ + criteriaId: row.criteriaId, + category: row.category || "", + category2: row.category2 || "", + item: row.item || "", + classification: row.classification || "", + range: row.range, + remarks: row.remarks, + scoreType: row.scoreType || "fixed", + selectedDetailId: row.selectedDetailId, + selectedDetail: row.selectedDetail, + score: row.score ? Number(row.score) : null, + comment: row.comment + }) + } + }) + + return { + evaluationInfo: evaluationInfo[0], + reviewerDetails: Array.from(reviewerDetailsMap.values()) + } + + } catch (error) { + console.error("Error fetching evaluation details:", error) + throw new Error( + error instanceof Error + ? error.message + : "평가 상세 정보 조회 중 오류가 발생했습니다" + ) + } +}
\ No newline at end of file |