'use server'; /* IMPORT */ import { and, asc, count, desc, eq, gte, ilike, inArray, isNotNull, lte, or, ne, sql, type SQL, } from 'drizzle-orm'; import { authOptions } from '@/app/api/auth/[...nextauth]/route'; import { contracts, esgEvaluationItems, EVALUATION_DEPARTMENT_CODES, evaluationTargets, evaluationTargetReviewers, evaluationTargetReviews, evaluationTargetsWithDepartments, generalEvaluations, periodicEvaluations, projects, type Division, type EvaluationTargetWithDepartments, type MaterialType, users, vendors, } from '@/db/schema'; import db from '@/db/db'; import { decryptWithServerAction } from '@/components/drm/drmUtils'; import { DEPARTMENT_CODE_LABELS } from '@/types/evaluation'; import ExcelJS from 'exceljs'; import { filterColumns } from '@/lib/filter-columns'; import { getServerSession } from 'next-auth/next'; import { PgTransaction } from 'drizzle-orm/pg-core'; import { revalidatePath, unstable_noStore } from 'next/cache'; import { selectUsers } from '../admin-users/repository'; import { selectVendors } from '../vendors/repository'; import { sendEmail } from '../mail/sendEmail'; import { type GetEvaluationTargetsSchema } from './validation'; // ---------------------------------------------------------------------------------------------------- export async function selectEvaluationTargetsFromView( tx: PgTransaction, params: { where?: any; orderBy?: (ReturnType | ReturnType)[]; offset?: number; limit?: number; } ) { const { where, orderBy, offset = 0, limit = 10 } = params; return tx .select() .from(evaluationTargetsWithDepartments) .where(where) .orderBy(...(orderBy ?? [])) .offset(offset) .limit(limit); } /** 총 개수 count */ export async function countEvaluationTargetsFromView( tx: PgTransaction, where?: any ) { const res = await tx .select({ count: count() }) .from(evaluationTargetsWithDepartments) .where(where); return res[0]?.count ?? 0; } // ============= 메인 서버 액션도 함께 수정 ============= export async function getEvaluationTargets(input: GetEvaluationTargetsSchema) { unstable_noStore() try { const offset = (input.page - 1) * input.perPage; // ✅ 단순화된 필터 처리 let advancedWhere: SQL | undefined = undefined; if (input.filters && Array.isArray(input.filters) && input.filters.length > 0) { console.log("필터 적용:", input.filters.map(f => `${f.id} ${f.operator} ${f.value}`)); try { advancedWhere = filterColumns({ table: evaluationTargetsWithDepartments, filters: input.filters, joinOperator: input.joinOperator || 'and', }); console.log("필터 조건 생성 완료"); } catch (error) { console.error("필터 조건 생성 오류:", error); // 필터 오류 시에도 전체 데이터 반환 advancedWhere = undefined; } } // 2) 글로벌 검색 조건 let globalWhere: SQL | undefined = undefined; if (input.search) { const s = `%${input.search}%`; const searchConditions: SQL[] = [ ilike(evaluationTargetsWithDepartments.vendorCode, s), ilike(evaluationTargetsWithDepartments.vendorName, s), ilike(evaluationTargetsWithDepartments.adminComment, s), ilike(evaluationTargetsWithDepartments.consolidatedComment, s), ilike(evaluationTargetsWithDepartments.orderReviewerName, s), ilike(evaluationTargetsWithDepartments.procurementReviewerName, s), ilike(evaluationTargetsWithDepartments.qualityReviewerName, s), ilike(evaluationTargetsWithDepartments.designReviewerName, s), ilike(evaluationTargetsWithDepartments.csReviewerName, s), ].filter(Boolean); if (searchConditions.length > 0) { globalWhere = or(...searchConditions); } } // 3) 최종 WHERE 조건 결합 const whereConditions: SQL[] = []; if (advancedWhere) whereConditions.push(advancedWhere); if (globalWhere) whereConditions.push(globalWhere); const finalWhere = whereConditions.length > 0 ? and(...whereConditions) : undefined; // 4) 전체 데이터 수 조회 const totalResult = await db .select({ count: count() }) .from(evaluationTargetsWithDepartments) .where(finalWhere); const total = totalResult[0]?.count || 0; if (total === 0) { return { data: [], pageCount: 0, total: 0 }; } console.log("총 데이터 수:", total); // 5) 정렬 및 페이징 처리 const orderByColumns = input.sort.map((sort) => { const column = sort.id as keyof typeof evaluationTargetsWithDepartments.$inferSelect; return sort.desc ? desc(evaluationTargetsWithDepartments[column]) : asc(evaluationTargetsWithDepartments[column]); }); if (orderByColumns.length === 0) { orderByColumns.push(desc(evaluationTargetsWithDepartments.createdAt)); } const evaluationData = await db .select() .from(evaluationTargetsWithDepartments) .where(finalWhere) .orderBy(...orderByColumns) .limit(input.perPage) .offset(offset); const pageCount = Math.ceil(total / input.perPage); console.log("반환 데이터 수:", evaluationData.length); return { data: evaluationData, pageCount, total }; } catch (err) { console.error("getEvaluationTargets 오류:", err); return { data: [], pageCount: 0, total: 0 }; } } // ============= 개별 조회 함수도 업데이트 ============= export async function getEvaluationTargetById(id: number): Promise { try { const results = await db.transaction(async (tx) => { return await selectEvaluationTargetsFromView(tx, { where: eq(evaluationTargetsWithDepartments.id, id), limit: 1, }); }); return results[0] || null; } catch (err) { console.error("Error in getEvaluationTargetById:", err); return null; } } // 통계 조회도 View 기반으로 변경 export async function getEvaluationTargetsStats(evaluationYear: number) { try { const stats = await db.transaction(async (tx) => { const result = await tx .select({ total: count(), pending: sql`sum(case when status = 'PENDING' then 1 else 0 end)`, confirmed: sql`sum(case when status = 'CONFIRMED' then 1 else 0 end)`, excluded: sql`sum(case when status = 'EXCLUDED' then 1 else 0 end)`, consensusTrue: sql`sum(case when consensus_status = true then 1 else 0 end)`, consensusFalse: sql`sum(case when consensus_status = false then 1 else 0 end)`, consensusNull: sql`sum(case when consensus_status is null then 1 else 0 end)`, oceanDivision: sql`sum(case when division = 'PLANT' then 1 else 0 end)`, shipyardDivision: sql`sum(case when division = 'SHIP' then 1 else 0 end)`, }) .from(evaluationTargetsWithDepartments) .where(eq(evaluationTargetsWithDepartments.evaluationYear, evaluationYear)); return result[0]; }); return stats; } catch (err) { console.error("Error in getEvaluationTargetsStats:", err); return null; } } // ============= 수동 생성 관련 서버 액션 ============= // 평가 대상 수동 생성 인터페이스 export interface CreateEvaluationTargetInput { evaluationYear: number; division: Division; vendorId: number; materialType: MaterialType; adminComment?: string; // 각 부서별 담당자 지정 reviewers: { departmentCode: keyof typeof EVALUATION_DEPARTMENT_CODES; reviewerUserId: number; }[]; } // 평가 대상 수동 생성 // service.ts 파일의 CreateEvaluationTargetInput 타입 수정 export interface CreateEvaluationTargetInput { evaluationYear: number division: "PLANT" | "SHIP" vendorId: number materialType: "EQUIPMENT" | "BULK" | "EQUIPMENT_BULK" adminComment?: string // ✅ 추가된 L/D 클레임 필드들 ldClaimCount?: number ldClaimAmount?: number ldClaimCurrency?: "KRW" | "USD" | "EUR" | "JPY" reviewers: Array<{ departmentCode: string reviewerUserId: number }> } // createEvaluationTarget 함수 수정 // service.ts 수정 export async function createEvaluationTarget( input: CreateEvaluationTargetInput, createdBy: number ) { console.log(input, "input") try { const session = await getServerSession(authOptions) return await db.transaction(async (tx) => { // 벤더 정보 조회 const vendor = await tx .select({ id: vendors.id, vendorCode: vendors.vendorCode, vendorName: vendors.vendorName, country: vendors.country, }) .from(vendors) .where(eq(vendors.id, input.vendorId)) .limit(1); if (!vendor.length) { throw new Error("벤더를 찾을 수 없습니다."); } const vendorInfo = vendor[0]; // 중복 체크 const existing = await tx .select({ id: evaluationTargets.id }) .from(evaluationTargets) .where( and( eq(evaluationTargets.evaluationYear, input.evaluationYear), eq(evaluationTargets.vendorId, input.vendorId), eq(evaluationTargets.division, input.division) ) ) .limit(1); console.log(existing,input ) if (existing.length > 0) { throw new Error("이미 동일한 평가 대상이 존재합니다."); } // 🔧 수정: 타입 추론 문제 해결 const targetValues: typeof evaluationTargets.$inferInsert = { evaluationYear: input.evaluationYear, division: input.division, vendorId: input.vendorId, vendorCode: vendorInfo.vendorCode ?? '', vendorName: vendorInfo.vendorName, domesticForeign: vendorInfo.country === 'KR' ? 'DOMESTIC' : 'FOREIGN', materialType: input.materialType, status: 'PENDING', adminComment: input.adminComment, adminUserId: createdBy, ldClaimCount: input.ldClaimCount ?? 0, // 🔧 수정: decimal 타입은 숫자로 처리 ldClaimAmount: input.ldClaimAmount?.toString() ?? '0', ldClaimCurrency: input.ldClaimCurrency ?? 'KRW', } console.log(targetValues) // 평가 대상 생성 const newEvaluationTarget = await tx .insert(evaluationTargets) .values(targetValues) .returning({ id: evaluationTargets.id }); const evaluationTargetId = newEvaluationTarget[0].id; // 담당자들 지정 if (input.reviewers && input.reviewers.length > 0) { const reviewerIds = input.reviewers.map(r => r.reviewerUserId); // 🔧 수정: SQL 배열 처리 개선 const reviewerInfos = await tx .select({ id: users.id, }) .from(users) .where(inArray(users.id, reviewerIds)); // sql 대신 inArray 사용 const reviewerAssignments: typeof evaluationTargetReviewers.$inferInsert[] = [ ...input.reviewers.map(r => { const info = reviewerInfos.find(i => i.id === r.reviewerUserId); return { evaluationTargetId, departmentCode: r.departmentCode, departmentNameFrom: info?.departmentName ?? "TEST 부서", reviewerUserId: r.reviewerUserId, assignedBy: createdBy, }; }), // session user 추가 { evaluationTargetId, departmentCode: "admin", departmentNameFrom: "정기평가 관리자", reviewerUserId: Number(session.user.id), assignedBy: createdBy, } ]; await tx.insert(evaluationTargetReviewers).values(reviewerAssignments); } return { success: true, evaluationTargetId, message: "평가 대상이 성공적으로 생성되었습니다.", }; }); } catch (error) { console.error("Error creating evaluation target:", error); return { success: false, error: error instanceof Error ? error.message : "평가 대상 생성 중 오류가 발생했습니다.", }; } } //업데이트 입력 타입 정의 export interface UpdateEvaluationTargetInput { id: number adminComment?: string consolidatedComment?: string ldClaimCount?: number ldClaimAmount?: number ldClaimCurrency?: "KRW" | "USD" | "EUR" | "JPY" consensusStatus?: boolean | null orderIsApproved?: boolean | null procurementIsApproved?: boolean | null qualityIsApproved?: boolean | null designIsApproved?: boolean | null csIsApproved?: boolean | null // 담당자 이메일 변경 orderReviewerEmail?: string procurementReviewerEmail?: string qualityReviewerEmail?: string designReviewerEmail?: string csReviewerEmail?: string } export interface UpdateEvaluationTargetInput { id: number // 기본 정보 adminComment?: string consolidatedComment?: string ldClaimCount?: number ldClaimAmount?: number ldClaimCurrency?: "KRW" | "USD" | "EUR" | "JPY" consensusStatus?: boolean | null // 각 부서별 평가 결과 orderIsApproved?: boolean | null procurementIsApproved?: boolean | null qualityIsApproved?: boolean | null designIsApproved?: boolean | null csIsApproved?: boolean | null // 담당자 이메일 (사용자 ID로 변환됨) orderReviewerEmail?: string procurementReviewerEmail?: string qualityReviewerEmail?: string designReviewerEmail?: string csReviewerEmail?: string } export async function updateEvaluationTarget(input: UpdateEvaluationTargetInput) { try { const session = await getServerSession(authOptions) if (!session?.user) { throw new Error("인증이 필요합니다.") } console.log(input,"input") return await db.transaction(async (tx) => { // 평가 대상 존재 확인 const existing = await tx .select({ id: evaluationTargets.id }) .from(evaluationTargets) .where(eq(evaluationTargets.id, input.id)) .limit(1) if (!existing.length) { throw new Error("평가 대상을 찾을 수 없습니다.") } // 1. 기본 정보 업데이트 const updateFields: Partial = {} if (input.adminComment !== undefined) { updateFields.adminComment = input.adminComment } if (input.consolidatedComment !== undefined) { updateFields.consolidatedComment = input.consolidatedComment } if (input.ldClaimCount !== undefined) { updateFields.ldClaimCount = input.ldClaimCount } if (input.ldClaimAmount !== undefined) { updateFields.ldClaimAmount = input.ldClaimAmount.toString() } if (input.ldClaimCurrency !== undefined) { updateFields.ldClaimCurrency = input.ldClaimCurrency } if (input.consensusStatus !== undefined) { updateFields.consensusStatus = input.consensusStatus } // 기본 정보가 있으면 업데이트 if (Object.keys(updateFields).length > 0) { updateFields.updatedAt = new Date() await tx .update(evaluationTargets) .set(updateFields) .where(eq(evaluationTargets.id, input.id)) } // 2. 담당자 정보 업데이트 const reviewerUpdates = [ { departmentCode: EVALUATION_DEPARTMENT_CODES.ORDER_EVAL, email: input.orderReviewerEmail }, { departmentCode: EVALUATION_DEPARTMENT_CODES.PROCUREMENT_EVAL, email: input.procurementReviewerEmail }, { departmentCode: EVALUATION_DEPARTMENT_CODES.QUALITY_EVAL, email: input.qualityReviewerEmail }, { departmentCode: EVALUATION_DEPARTMENT_CODES.DESIGN_EVAL, email: input.designReviewerEmail }, { departmentCode: EVALUATION_DEPARTMENT_CODES.CS_EVAL, email: input.csReviewerEmail }, ] for (const update of reviewerUpdates) { if (update.email !== undefined) { // 기존 담당자 제거 await tx .delete(evaluationTargetReviewers) .where( and( eq(evaluationTargetReviewers.evaluationTargetId, input.id), eq(evaluationTargetReviewers.departmentCode, update.departmentCode) ) ) // 새 담당자 추가 (이메일이 있는 경우만) if (update.email) { // 이메일로 사용자 ID 조회 const user = await tx .select({ id: users.id }) .from(users) .where(eq(users.email, update.email)) .limit(1) if (user.length > 0) { await tx .insert(evaluationTargetReviewers) .values({ evaluationTargetId: input.id, departmentCode: update.departmentCode, reviewerUserId: Number(user[0].id), assignedBy: Number(session.user.id), }) } } } } // 3. 평가 결과 업데이트 const reviewUpdates = [ { departmentCode: EVALUATION_DEPARTMENT_CODES.ORDER_EVAL, isApproved: input.orderIsApproved }, { departmentCode: EVALUATION_DEPARTMENT_CODES.PROCUREMENT_EVAL, isApproved: input.procurementIsApproved }, { departmentCode: EVALUATION_DEPARTMENT_CODES.QUALITY_EVAL, isApproved: input.qualityIsApproved }, // { departmentCode: EVALUATION_DEPARTMENT_CODES.DESIGN_EVAL, isApproved: input.designIsApproved }, // { departmentCode: EVALUATION_DEPARTMENT_CODES.CS_EVAL, isApproved: input.csIsApproved }, ] for (const review of reviewUpdates) { if (review.isApproved !== undefined) { console.log(review.departmentCode,"review.departmentCode"); // 해당 부서의 담당자 조회 const reviewer = await tx .select({ reviewerUserId: evaluationTargetReviewers.reviewerUserId }) .from(evaluationTargetReviewers) .where( and( eq(evaluationTargetReviewers.evaluationTargetId, input.id), eq(evaluationTargetReviewers.departmentCode, review.departmentCode) ) ) .limit(1) console.log(reviewer,"reviewer") if (reviewer.length > 0) { // 기존 평가 결과 삭제 await tx .delete(evaluationTargetReviews) .where( and( eq(evaluationTargetReviews.evaluationTargetId, input.id), eq(evaluationTargetReviews.reviewerUserId, reviewer[0].reviewerUserId), eq(evaluationTargetReviews.departmentCode, review.departmentCode) // 추가 ) ) // 새 평가 결과 추가 (null이 아닌 경우만) if (review.isApproved !== null) { console.log("INSERT 시도:", review.departmentCode, review.isApproved); try { const insertResult = await tx .insert(evaluationTargetReviews) .values({ evaluationTargetId: input.id, reviewerUserId: reviewer[0].reviewerUserId, departmentCode: review.departmentCode, isApproved: review.isApproved, reviewedAt: new Date(), }) .returning({ id: evaluationTargetReviews.id }); // returning 추가 console.log("INSERT 성공:", insertResult); } catch (insertError) { console.error("INSERT 에러:", insertError); throw insertError; } } } } } const requiredDepartments = [ EVALUATION_DEPARTMENT_CODES.ORDER_EVAL, EVALUATION_DEPARTMENT_CODES.PROCUREMENT_EVAL, EVALUATION_DEPARTMENT_CODES.QUALITY_EVAL ] const currentReviews = await tx .select({ isApproved: evaluationTargetReviews.isApproved, departmentCode: evaluationTargetReviews.departmentCode, }) .from(evaluationTargetReviews) .where( and( eq(evaluationTargetReviews.evaluationTargetId, input.id), inArray(evaluationTargetReviews.departmentCode, requiredDepartments) ) ) // 3개 필수 부서의 리뷰가 모두 완료된 경우에만 의견 일치 상태 계산 const reviewedDepartments = currentReviews.map(r => r.departmentCode) const allRequiredDepartmentsReviewed = requiredDepartments.every(dept => reviewedDepartments.includes(dept) ) console.log(allRequiredDepartmentsReviewed,"allRequiredDepartmentsReviewed") if (allRequiredDepartmentsReviewed) { const approvals = currentReviews.map(r => r.isApproved) console.log(approvals,"approvals") const allApproved = approvals.every(approval => approval === true) const allRejected = approvals.every(approval => approval === false) const hasConsensus = allApproved || allRejected console.log("Auto-updating consensus status:", { hasConsensus, approvals, reviewedDepartments, allRequiredDepartmentsReviewed }) await tx .update(evaluationTargets) .set({ consensusStatus: hasConsensus, confirmedAt: hasConsensus ? new Date() : null, confirmedBy: hasConsensus ? Number(session.user.id) : null, updatedAt: new Date() }) .where(eq(evaluationTargets.id, input.id)) } revalidatePath('/evcp/evaluation-target-list') revalidatePath('/procurement/evaluation-target-list') return { success: true, message: "평가 대상이 성공적으로 수정되었습니다.", } }) } catch (error) { console.error("Error updating evaluation target:", error) return { success: false, error: error instanceof Error ? error.message : "평가 대상 수정 중 오류가 발생했습니다.", } } } // 담당자 목록 조회 시 부서 정보도 함께 반환 export async function getAvailableReviewers(departmentCode?: string) { try { const reviewers = await db .select({ id: users.id, name: users.name, email: users.email, // departmentName: "API로 추후", // ✅ 부서명도 반환 }) .from(users) .where(ne(users.domain, "partners")) .orderBy(users.name) // .limit(100); //partners가 아닌 domain에 따라서 필터링 return reviewers; } catch (error) { console.error("Error fetching available reviewers:", error); return []; } } // 사용 가능한 벤더 목록 조회 export async function getAvailableVendors(search?: string) { try { let query = db .select({ id: vendors.id, vendorCode: vendors.vendorCode, vendorName: vendors.vendorName, status: vendors.status, }) .from(vendors) .where( and( // 활성 상태인 벤더만 // eq(vendors.status, "ACTIVE"), // 검색어가 있으면 적용 search ? or( ilike(vendors.vendorCode, `%${search}%`), ilike(vendors.vendorName, `%${search}%`) ) : undefined ) ) .orderBy(vendors.vendorName) // .limit(100); return await query; } catch (error) { console.error("Error fetching available vendors:", error); return []; } } // 부서 정보 조회 (상수에서) export async function getDepartmentInfo() { return Object.entries(EVALUATION_DEPARTMENT_CODES).map(([key, value]) => { return { code: value, name: DEPARTMENT_CODE_LABELS[key as keyof typeof DEPARTMENT_CODE_LABELS], key, }; }); } export async function confirmEvaluationTargets( targetIds: number[], evaluationPeriod?: string // "상반기", "하반기", "연간" 등 ) { try { const session = await getServerSession(authOptions) if (!session?.user) { return { success: false, error: "인증이 필요합니다." } } if (targetIds.length === 0) { return { success: false, error: "선택된 평가 대상이 없습니다." } } // 평가 기간이 없으면 현재 날짜 기준으로 자동 결정 // const currentPeriod = evaluationPeriod || getCurrentEvaluationPeriod() const currentPeriod = "연간" // 트랜잭션으로 처리 const result = await db.transaction(async (tx) => { // 확정 가능한 대상들 확인 (PENDING 상태이면서 consensusStatus가 true인 것들) const eligibleTargets = await tx .select() .from(evaluationTargets) .where( and( inArray(evaluationTargets.id, targetIds), eq(evaluationTargets.status, "PENDING"), eq(evaluationTargets.consensusStatus, true) ) ) if (eligibleTargets.length === 0) { throw new Error("확정 가능한 평가 대상이 없습니다. (의견 일치 상태인 대기중 항목만 확정 가능)") } const confirmedTargetIds = eligibleTargets.map(target => target.id) // 1. 평가 대상 상태를 CONFIRMED로 변경 await tx .update(evaluationTargets) .set({ status: "CONFIRMED", confirmedAt: new Date(), confirmedBy: Number(session.user.id), updatedAt: new Date() }) .where(inArray(evaluationTargets.id, confirmedTargetIds)) // 2. 각 확정된 평가 대상에 대해 periodicEvaluations 레코드 생성 const periodicEvaluationsToCreate = [] for (const target of eligibleTargets) { // 이미 해당 기간에 평가가 존재하는지 확인 const existingEvaluation = await tx .select({ id: periodicEvaluations.id }) .from(periodicEvaluations) .where( and( eq(periodicEvaluations.evaluationTargetId, target.id), // eq(periodicEvaluations.evaluationPeriod, currentPeriod) ) ) .limit(1) // 없으면 생성 목록에 추가 if (existingEvaluation.length === 0) { periodicEvaluationsToCreate.push({ evaluationTargetId: target.id, evaluationPeriod: currentPeriod, // 평가년도에 따른 제출 마감일 설정 (예: 상반기는 7월 말, 하반기는 1월 말) submissionDeadline: getSubmissionDeadline(target.evaluationYear, currentPeriod), status: "PENDING" as const, createdAt: new Date(), updatedAt: new Date() }) } console.log("periodicEvaluationsToCreate", periodicEvaluationsToCreate) } // 3. periodicEvaluations 레코드들 일괄 생성 let createdEvaluationsCount = 0 if (periodicEvaluationsToCreate.length > 0) { const createdEvaluations = await tx .insert(periodicEvaluations) .values(periodicEvaluationsToCreate) .returning({ id: periodicEvaluations.id }) createdEvaluationsCount = createdEvaluations.length } console.log("createdEvaluationsCount", createdEvaluationsCount) // 4. 평가 항목 수 조회 (evaluationSubmissions 생성을 위해) const [generalItemsCount, esgItemsCount] = await Promise.all([ // 활성화된 일반평가 항목 수 tx.select({ count: count() }) .from(generalEvaluations) .where(eq(generalEvaluations.isActive, true)), // 활성화된 ESG 평가항목 수 tx.select({ count: count() }) .from(esgEvaluationItems) .where(eq(esgEvaluationItems.isActive, true)) ]) const totalGeneralItems = generalItemsCount[0]?.count || 0 const totalEsgItems = esgItemsCount[0]?.count || 0 // 5. 각 periodicEvaluation에 대해 담당자별 reviewerEvaluations도 생성 // if (periodicEvaluationsToCreate.length > 0) { // // 새로 생성된 periodicEvaluations 조회 // const newPeriodicEvaluations = await tx // .select({ // id: periodicEvaluations.id, // evaluationTargetId: periodicEvaluations.evaluationTargetId // }) // .from(periodicEvaluations) // .where( // and( // inArray(periodicEvaluations.evaluationTargetId, confirmedTargetIds), // eq(periodicEvaluations.evaluationPeriod, currentPeriod) // ) // ) // // 각 평가에 대해 담당자별 reviewerEvaluations 생성 // for (const periodicEval of newPeriodicEvaluations) { // // 해당 evaluationTarget의 담당자들 조회 // const reviewers = await tx // .select() // .from(evaluationTargetReviewers) // .where(eq(evaluationTargetReviewers.evaluationTargetId, periodicEval.evaluationTargetId)) // if (reviewers.length > 0) { // const reviewerEvaluationsToCreate = reviewers.map(reviewer => ({ // periodicEvaluationId: periodicEval.id, // evaluationTargetReviewerId: reviewer.id, // isCompleted: false, // createdAt: new Date(), // updatedAt: new Date() // })) // await tx // .insert(reviewerEvaluations) // .values(reviewerEvaluationsToCreate) // } // } // } // 6. 벤더별 evaluationSubmissions 레코드 생성 // const evaluationSubmissionsToCreate = [] // // 생성된 periodicEvaluations의 ID를 매핑하기 위한 맵 생성 // const periodicEvaluationIdMap = new Map() // if (createdEvaluationsCount > 0) { // const createdEvaluations = await tx // .select({ // id: periodicEvaluations.id, // evaluationTargetId: periodicEvaluations.evaluationTargetId // }) // .from(periodicEvaluations) // .where( // and( // inArray(periodicEvaluations.evaluationTargetId, confirmedTargetIds), // eq(periodicEvaluations.evaluationPeriod, currentPeriod) // ) // ) // // evaluationTargetId를 키로 하는 맵 생성 // createdEvaluations.forEach(periodicEval => { // periodicEvaluationIdMap.set(periodicEval.evaluationTargetId, periodicEval.id) // }) // } // console.log("periodicEvaluationIdMap", periodicEvaluationIdMap) // for (const target of eligibleTargets) { // // 이미 해당 년도/기간에 제출 레코드가 있는지 확인 // const existingSubmission = await tx // .select({ id: evaluationSubmissions.id }) // .from(evaluationSubmissions) // .where( // and( // eq(evaluationSubmissions.companyId, target.vendorId), // eq(evaluationSubmissions.evaluationYear, target.evaluationYear), // // eq(evaluationSubmissions.evaluationRound, currentPeriod) // ) // ) // .limit(1) // // 없으면 생성 목록에 추가 // if (existingSubmission.length === 0) { // const periodicEvaluationId = periodicEvaluationIdMap.get(target.id) // if (periodicEvaluationId) { // evaluationSubmissionsToCreate.push({ // companyId: target.vendorId, // periodicEvaluationId: periodicEvaluationId, // evaluationYear: target.evaluationYear, // evaluationRound: currentPeriod, // submissionStatus: "draft" as const, // totalGeneralItems: totalGeneralItems, // completedGeneralItems: 0, // totalEsgItems: totalEsgItems, // completedEsgItems: 0, // isActive: true, // createdAt: new Date(), // updatedAt: new Date() // }) // } // } // } // // 7. evaluationSubmissions 레코드들 일괄 생성 // let createdSubmissionsCount = 0 // if (evaluationSubmissionsToCreate.length > 0) { // const createdSubmissions = await tx // .insert(evaluationSubmissions) // .values(evaluationSubmissionsToCreate) // .returning({ id: evaluationSubmissions.id }) // createdSubmissionsCount = createdSubmissions.length // } return { confirmedTargetIds, createdEvaluationsCount, // createdSubmissionsCount, totalConfirmed: confirmedTargetIds.length } }) return { success: true, message: `${result.totalConfirmed}개 평가 대상이 확정되었습니다. ${result.createdEvaluationsCount}개의 정기평가가 생성되었습니다.`, confirmedCount: result.totalConfirmed, createdEvaluationsCount: result.createdEvaluationsCount, // createdSubmissionsCount: result.createdSubmissionsCount } } catch (error) { console.error("Error confirming evaluation targets:", error) return { success: false, error: error instanceof Error ? error.message : "확정 처리 중 오류가 발생했습니다." } } } // 현재 날짜 기준으로 평가 기간 결정하는 헬퍼 함수 function getCurrentEvaluationPeriod(): string { const now = new Date() const month = now.getMonth() + 1 // 0-based이므로 +1 // 1~6월: 상반기, 7~12월: 하반기 return month <= 6 ? "상반기" : "하반기" } // 평가년도와 기간에 따른 제출 마감일 설정하는 헬퍼 함수 function getSubmissionDeadline(evaluationYear: number, period: string): Date { const year = evaluationYear if (period === "상반기") { // 상반기 평가는 다음 해 6월 말까지 return new Date(year, 5, 31) // 7월은 6 (0-based) } else if (period === "하반기") { // 하반기 평가는 다음 올해 12월 말까지 return new Date(year, 11, 31) // 1월은 0 (0-based) } else { // 연간 평가는 올해 6월 말까지 return new Date(year, 5, 31) // 3월은 2 (0-based) } } export async function excludeEvaluationTargets(targetIds: number[]) { try { const session = await getServerSession(authOptions) if (!session?.user) { return { success: false, error: "인증이 필요합니다." } } if (targetIds.length === 0) { return { success: false, error: "선택된 평가 대상이 없습니다." } } // 트랜잭션으로 처리 await db.transaction(async (tx) => { // 제외 가능한 대상들 확인 (PENDING 상태인 것들) const eligibleTargets = await tx .select() .from(evaluationTargets) .where( and( inArray(evaluationTargets.id, targetIds), eq(evaluationTargets.status, "PENDING") ) ) if (eligibleTargets.length === 0) { throw new Error("제외 가능한 평가 대상이 없습니다. (대기중 상태인 항목만 제외 가능)") } // 상태를 EXCLUDED로 변경 const excludedTargetIds = eligibleTargets.map(target => target.id) await tx .update(evaluationTargets) .set({ status: "EXCLUDED", updatedAt: new Date() }) .where(inArray(evaluationTargets.id, excludedTargetIds)) return excludedTargetIds }) return { success: true, message: `${targetIds.length}개 평가 대상이 제외되었습니다.`, excludedCount: targetIds.length } } catch (error) { console.error("Error excluding evaluation targets:", error) return { success: false, error: error instanceof Error ? error.message : "제외 처리 중 오류가 발생했습니다." } } } export async function requestEvaluationReview(targetIds: number[], message?: string) { try { const session = await getServerSession(authOptions) if (!session?.user) { return { success: false, error: "인증이 필요합니다." } } if (targetIds.length === 0) { return { success: false, error: "선택된 평가 대상이 없습니다." } } // 선택된 평가 대상들과 담당자 정보 조회 const targetsWithReviewers = await db .select({ id: evaluationTargets.id, vendorCode: evaluationTargets.vendorCode, vendorName: evaluationTargets.vendorName, materialType: evaluationTargets.materialType, evaluationYear: evaluationTargets.evaluationYear, status: evaluationTargets.status, reviewerEmail: users.email, reviewerName: users.name, departmentCode: evaluationTargetReviewers.departmentCode, departmentName: evaluationTargetReviewers.departmentNameFrom, }) .from(evaluationTargets) .leftJoin( evaluationTargetReviewers, eq(evaluationTargets.id, evaluationTargetReviewers.evaluationTargetId) ) .leftJoin( users, eq(evaluationTargetReviewers.reviewerUserId, users.id) ) .where( and( inArray(evaluationTargets.id, targetIds), eq(evaluationTargets.status, "PENDING") ) ) if (targetsWithReviewers.length === 0) { return { success: false, error: "의견 요청 가능한 평가 대상이 없습니다." } } // 평가 대상별로 그룹화 const targetGroups = targetsWithReviewers.reduce((acc, item) => { if (!acc[item.id]) { acc[item.id] = { id: item.id, vendorCode: item.vendorCode, vendorName: item.vendorName, materialType: item.materialType, evaluationYear: item.evaluationYear, reviewers: [] } } if (item.reviewerEmail) { acc[item.id].reviewers.push({ email: item.reviewerEmail, name: item.reviewerName, departmentCode: item.departmentCode, departmentName: item.departmentName }) } return acc }, {} as Record) const targets = Object.values(targetGroups) // 모든 담당자 이메일 수집 (중복 제거) const reviewerEmails = new Set() const reviewerInfo = new Map() targets.forEach(target => { target.reviewers.forEach((reviewer: any) => { if (reviewer.email) { reviewerEmails.add(reviewer.email) if (!reviewerInfo.has(reviewer.email)) { reviewerInfo.set(reviewer.email, { name: reviewer.name || reviewer.email, departments: [] }) } const info = reviewerInfo.get(reviewer.email)! if (reviewer.departmentName && !info.departments.includes(reviewer.departmentName)) { info.departments.push(reviewer.departmentName) } } }) }) if (reviewerEmails.size === 0) { return { success: false, error: "담당자가 지정되지 않은 평가 대상입니다." } } // 각 담당자에게 이메일 발송 const emailPromises = Array.from(reviewerEmails).map(email => { const reviewer = reviewerInfo.get(email)! return sendEmail({ to: email, subject: `벤더 평가 의견 요청 - ${targets.length}건`, template: "evaluation-review-request", context: { requesterName: session.user.name || session.user.email, reviewerName: reviewer.name, targetCount: targets.length, targets: targets.map(target => ({ vendorCode: target.vendorCode, vendorName: target.vendorName, materialType: target.materialType, evaluationYear: target.evaluationYear })), message: message || "", reviewUrl: `${process.env.NEXTAUTH_URL}/evcp/evaluation-target-list`, requestDate: new Date().toLocaleString('ko-KR') } }) }) await Promise.all(emailPromises) return { success: true, message: `${reviewerEmails.size}명의 담당자에게 의견 요청 이메일이 발송되었습니다.`, emailCount: reviewerEmails.size } } catch (error) { console.error("Error requesting evaluation review:", error) return { success: false, error: error instanceof Error ? error.message : "의견 요청 중 오류가 발생했습니다." } } } interface AutoGenerateResult { success: boolean message: string error?: string generatedCount?: number skippedCount?: number details?: { shipTargets: number plantTargets: number duplicateSkipped: number } } /** * 자동으로 평가 대상을 생성하는 서버 액션 * 전년도 10월부터 현재년도 9월까지의 계약을 기준으로 평가 대상을 생성 */ export async function autoGenerateEvaluationTargets( evaluationYear: number, adminUserId: number ): Promise { try { // 평가 기간 계산 (전년도 10월 ~ 현재년도 9월) const startDate = `${evaluationYear - 1}-10-01` const endDate = `${evaluationYear}-09-30` console.log(`Generating evaluation targets for period: ${startDate} to ${endDate}`) // 1. 해당 기간의 계약들과 관련 정보를 조회 const contractsWithDetails = await db .select({ contractId: contracts.id, vendorId: contracts.vendorId, projectId: contracts.projectId, startDate: contracts.startDate, // vendor 정보 vendorCode: vendors.vendorCode, vendorName: vendors.vendorName, vendorType: vendors.country === "KR" ? "DOMESTIC" : "FOREIGN", // DOMESTIC | FOREIGN // project 정보 projectType: projects.type, // ship | plant }) .from(contracts) .innerJoin(vendors, eq(contracts.vendorId, vendors.id)) .innerJoin(projects, eq(contracts.projectId, projects.id)) .where( and( gte(contracts.startDate, startDate), lte(contracts.startDate, endDate) ) ) if (contractsWithDetails.length === 0) { return { success: true, message: "해당 기간에 생성할 평가 대상이 없습니다.", generatedCount: 0, skippedCount: 0 } } console.log(`Found ${contractsWithDetails.length} contracts in the period`) // 2. 벤더별, 구분별로 그룹화하여 중복 제거 const targetGroups = new Map() contractsWithDetails.forEach(contract => { const division = contract.projectType === "ship" ? "SHIP" : "PLANT" const key = `${contract.vendorId}-${division}` if (!targetGroups.has(key)) { targetGroups.set(key, { vendorId: contract.vendorId, vendorCode: contract.vendorCode, vendorName: contract.vendorName, domesticForeign: contract.vendorType === "DOMESTIC" ? "DOMESTIC" : "FOREIGN", division: division as "SHIP" | "PLANT", // 기본값으로 EQUIPMENT 설정 (추후 더 정교한 로직 필요시 수정) materialType: "EQUIPMENT" as const }) } }) console.log(`Created ${targetGroups.size} unique vendor-division combinations`) // 3. 이미 존재하는 평가 대상 확인 const existingTargetsKeys = new Set() if (targetGroups.size > 0) { const vendorIds = Array.from(targetGroups.values()).map(t => t.vendorId) const existingTargets = await db .select({ vendorId: evaluationTargets.vendorId, division: evaluationTargets.division }) .from(evaluationTargets) .where( and( eq(evaluationTargets.evaluationYear, evaluationYear), inArray(evaluationTargets.vendorId, vendorIds) ) ) existingTargets.forEach(target => { existingTargetsKeys.add(`${target.vendorId}-${target.division}`) }) } console.log(`Found ${existingTargetsKeys.size} existing targets`) // 4. 새로운 평가 대상만 필터링 const newTargets = Array.from(targetGroups.entries()) .filter(([key]) => !existingTargetsKeys.has(key)) .map(([_, target]) => target) if (newTargets.length === 0) { return { success: true, message: "이미 모든 평가 대상이 생성되어 있습니다.", generatedCount: 0, skippedCount: targetGroups.size } } // 5. 평가 대상 생성 const evaluationTargetsToInsert = newTargets.map(target => ({ evaluationYear, division: target.division, vendorId: target.vendorId, vendorCode: target.vendorCode, vendorName: target.vendorName, domesticForeign: target.domesticForeign, materialType: target.materialType, status: "PENDING" as const, adminUserId, ldClaimCount: 0, ldClaimAmount: "0", ldClaimCurrency: "KRW" as const })) // 배치로 삽입 await db.insert(evaluationTargets).values(evaluationTargetsToInsert) // 통계 계산 const shipTargets = newTargets.filter(t => t.division === "SHIP").length const plantTargets = newTargets.filter(t => t.division === "PLANT").length const duplicateSkipped = existingTargetsKeys.size console.log(`Successfully created ${newTargets.length} evaluation targets`) // 캐시 무효화 revalidatePath("/evcp/evaluation-target-list") revalidatePath("/procurement/evaluation-target-list") return { success: true, message: `${newTargets.length}개의 평가 대상이 성공적으로 생성되었습니다.`, generatedCount: newTargets.length, skippedCount: duplicateSkipped, details: { shipTargets, plantTargets, duplicateSkipped } } } catch (error) { console.error("Error auto generating evaluation targets:", error) return { success: false, error: error instanceof Error ? error.message : "알 수 없는 오류가 발생했습니다.", message: "평가 대상 자동 생성에 실패했습니다." } } } export async function deleteEvaluationTargets(targetIds: number[]) { console.log(targetIds, "targetIds to delete"); try { const session = await getServerSession(authOptions); if (!session?.user?.id) { throw new Error("로그인이 필요합니다."); } // 권한 체크 (필요한 경우) // const hasPermission = await checkUserPermission(session.user.id, 'manage_evaluations'); // if (!hasPermission) { // throw new Error("평가 관리 권한이 없습니다."); // } return await db.transaction(async (tx) => { // 1. 삭제하려는 타겟들이 존재하고 PENDING 상태인지 확인 const targetsToDelete = await tx .select({ id: evaluationTargets.id, status: evaluationTargets.status, vendorName: evaluationTargets.vendorName, evaluationYear: evaluationTargets.evaluationYear, division: evaluationTargets.division, }) .from(evaluationTargets) .where(inArray(evaluationTargets.id, targetIds)); if (targetsToDelete.length === 0) { throw new Error("삭제할 평가 대상을 찾을 수 없습니다."); } // PENDING 상태가 아닌 타겟들 확인 const nonPendingTargets = targetsToDelete.filter(target => target.status !== 'PENDING'); if (nonPendingTargets.length > 0) { const nonPendingNames = nonPendingTargets .map(t => `${t.vendorName} (${t.evaluationYear}년)`) .join(', '); throw new Error(`다음 평가 대상은 PENDING 상태가 아니어서 삭제할 수 없습니다: ${nonPendingNames}`); } // 실제로 삭제할 수 있는 타겟 ID들 const validTargetIds = targetsToDelete.map(t => t.id); console.log(`Deleting ${validTargetIds.length} evaluation targets:`, targetsToDelete.map(t => `${t.vendorName} (${t.evaluationYear}년, ${t.division})`)); // 2. 관련된 자식 테이블들 먼저 삭제 // evaluationTargetReviewers 테이블 삭제 const deletedReviewers = await tx .delete(evaluationTargetReviewers) .where(inArray(evaluationTargetReviewers.evaluationTargetId, validTargetIds)) .returning({ id: evaluationTargetReviewers.id }); console.log(`Deleted ${deletedReviewers.length} reviewer assignments`); // 3. 기타 관련 테이블들 삭제 (필요한 경우 추가) // 예: evaluationTargetDepartments, evaluationComments 등 // const deletedDepartments = await tx // .delete(evaluationTargetDepartments) // .where(inArray(evaluationTargetDepartments.evaluationTargetId, validTargetIds)) // .returning({ id: evaluationTargetDepartments.id }); // 4. 메인 테이블 삭제 const deletedTargets = await tx .delete(evaluationTargets) .where(inArray(evaluationTargets.id, validTargetIds)) .returning({ id: evaluationTargets.id, vendorName: evaluationTargets.vendorName, evaluationYear: evaluationTargets.evaluationYear }); console.log(`Successfully deleted ${deletedTargets.length} evaluation targets`); return { success: true, deletedCount: deletedTargets.length, deletedTargets: deletedTargets.map(t => ({ id: t.id, vendorName: t.vendorName, evaluationYear: t.evaluationYear })), message: `${deletedTargets.length}개의 평가 대상이 성공적으로 삭제되었습니다.`, }; }); } catch (error) { console.error("Error deleting evaluation targets:", error); return { success: false, error: error instanceof Error ? error.message : "평가 대상 삭제 중 오류가 발생했습니다.", }; } } /* HELPER FUNCTION FOR GETTING CURRENT USER ID */ export async function getCurrentUserId(): Promise { try { const session = await getServerSession(authOptions); return session?.user?.id ? Number(session.user.id) : 3; // 기본값 3, 실제 환경에서는 적절한 기본값 설정 } catch (error) { console.error('Error in Getting Current Session User ID:', error); return 3; // 기본값 3 } } /* FUNCTION FOR GENERATING EXCEL TEMPLATE */ export async function generateEvalTargetTemplate(): Promise { try { // [01] Create a new workbook and worksheet const workbook = new ExcelJS.Workbook(); const worksheet = workbook.addWorksheet('협력업체 평가 대상 관리 템플릿'); workbook.creator = 'eVCP System'; workbook.created = new Date(); // [02] Generate Header Rows worksheet.mergeCells(1, 1, 2, 6); worksheet.getCell(1, 1).value = '기본 정보'; worksheet.mergeCells(1, 7, 2, 9); worksheet.getCell(1, 7).value = 'L/D 클레임 정보'; worksheet.mergeCells(1, 10, 1, 19); worksheet.getCell(1, 10).value = '담당자 지정'; worksheet.mergeCells(2, 10, 2, 11); worksheet.getCell(2, 10).value = '발주 평가 담당'; worksheet.mergeCells(2, 12, 2, 13); worksheet.getCell(2, 12).value = '조달 평가 담당'; worksheet.mergeCells(2, 14, 2, 15); worksheet.getCell(2, 14).value = '품질 평가 담당'; worksheet.mergeCells(2, 16, 2, 17); worksheet.getCell(2, 16).value = '설계 평가 담당'; worksheet.mergeCells(2, 18, 2, 19); worksheet.getCell(2, 18).value = 'CS 평가 담당'; const templateHeaders = [ { key: 'evaluationYear', width: 15 }, { key: 'division', width: 15 }, { key: 'vendorCode', width: 20 }, { key: 'vendorName', width: 20 }, { key: 'materialType', width: 20 }, { key: 'adminComment', width: 30 }, { key: 'ldClaimCount', width: 15 }, { key: 'ldClaimAmount', width: 20 }, { key: 'ldClaimCurrency', width: 15 }, { key: 'orderEvalName', width: 20 }, { key: 'orderEvalEmail', width: 30 }, { key: 'procurementEvalName', width: 20 }, { key: 'procurementEvalEmail', width: 30 }, { key: 'qualityEvalName', width: 20 }, { key: 'qualityEvalEmail', width: 30 }, { key: 'designEvalName', width: 20 }, { key: 'designEvalEmail', width: 30 }, { key: 'csEvalName', width: 20 }, { key: 'csEvalEmail', width: 30 }, ]; worksheet.columns = templateHeaders; const headers = [ '평가년도', '구분', '협력업체 코드', '협력업체명', '자재구분', '관리자 의견', '클레임 건수', '클레임 금액', '통화단위', '이름', '이메일', '이름', '이메일', '이름', '이메일', '이름', '이메일', '이름', '이메일', ]; headers.forEach((header, idx) => { worksheet.getRow(3).getCell(idx + 1).value = header; }); // [03] Apply Header Styles function applyHeaderStyle(row: ExcelJS.Row) { row.eachCell(cell => { cell.fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: 'FFD9D9D9' }, }; cell.font = { bold: true }; cell.alignment = { horizontal: 'center', vertical: 'middle' }; cell.border = { top: { style: 'thin' }, left: { style: 'thin' }, bottom: { style: 'thin' }, right: { style: 'thin' }, }; }); } applyHeaderStyle(worksheet.getRow(1)); applyHeaderStyle(worksheet.getRow(2)); applyHeaderStyle(worksheet.getRow(3)); // [04] Generate Data Validation Sheet const validationSheet = workbook.addWorksheet('ValidationData'); validationSheet.state = 'hidden'; const DIVISION_LIST = ['해양', '조선']; const MATERIAL_TYPE_LIST = ['장비재', '벌크']; const CURRENCY_LIST = ['KRW (원)', 'USD (달러)', 'EUR (유로)', 'JPY (엔)']; validationSheet.getColumn(1).values = ['구분', ...DIVISION_LIST]; validationSheet.getColumn(2).values = ['자재구분', ...MATERIAL_TYPE_LIST]; validationSheet.getColumn(3).values = ['통화단위', ...CURRENCY_LIST]; const divisionCol = templateHeaders.findIndex(h => h.key === 'division') + 1; const materialCol = templateHeaders.findIndex(h => h.key === 'materialType') + 1; const currencyCol = templateHeaders.findIndex(h => h.key === 'ldClaimCurrency') + 1; if (divisionCol > 0) { (worksheet as any).dataValidations.add(`${worksheet.getColumn(divisionCol).letter}4:${worksheet.getColumn(divisionCol).letter}1000`, { type: 'list', allowBlank: false, formulae: [`ValidationData!$A$2:$A$${DIVISION_LIST.length + 1}`], }); } if (materialCol > 0) { (worksheet as any).dataValidations.add(`${worksheet.getColumn(materialCol).letter}4:${worksheet.getColumn(materialCol).letter}1000`, { type: 'list', allowBlank: false, formulae: [`ValidationData!$B$2:$B$${MATERIAL_TYPE_LIST.length + 1}`], }); } if (currencyCol > 0) { (worksheet as any).dataValidations.add(`${worksheet.getColumn(currencyCol).letter}4:${worksheet.getColumn(currencyCol).letter}1000`, { type: 'list', allowBlank: false, formulae: [`ValidationData!$C$2:$C$${CURRENCY_LIST.length + 1}`], }); } // [05] Add Sample Data Row worksheet.addRow({ evaluationYear: '2025', division: '해양', vendorCode: 'A00000000', vendorName: '(주)협력업체', materialType: '장비재', ldClaimCount: '0', ldClaimAmount: '0', ldClaimCurrency: 'KRW (원)', orderEvalName: '홍길동', orderEvalEmail: 'hong@example.com', }); // [06] Apply Border to Data Cells for (let rowIdx = 4; rowIdx <= 10; rowIdx++) { const row = worksheet.getRow(rowIdx); for (let colIdx = 1; colIdx <= templateHeaders.length; colIdx++) { const cell = row.getCell(colIdx); if (!cell.value) { cell.value = ''; } cell.border = { top: { style: 'thin' }, left: { style: 'thin' }, bottom: { style: 'thin' }, right: { style: 'thin' }, }; } } return await workbook.xlsx.writeBuffer() as ArrayBuffer; } catch (error) { console.error('Error in generating Excel template:', error); throw new Error('Failed to generate Excel template'); } } /* FUNCTION FOR IMPORTING EXCEL FILE */ export async function importEvalTargetExcel(file: File): Promise<{ errorFile?: Blob; errorMessage?: string; successMessage?: string; }> { const DIVISION_MAP: Record = { '해양': 'PLANT', '조선': 'SHIP', }; const MATERIAL_TYPE_MAP: Record = { '장비재': 'EQUIPMENT', '벌크': 'BULK', }; const CURRENCY_MAP: Record = { 'KRW (원)': 'KRW', 'USD (달러)': 'USD', 'EUR (유로)': 'EUR', 'JPY (엔)': 'JPY', }; function getCellText(cell: ExcelJS.Cell): string { const value = cell.value; if (!value) { return ''; } if (typeof value === 'string' || typeof value === 'number') { return value.toString().trim(); } if (typeof value === 'object' && 'text' in value) { return value.text?.toString().trim() || ''; } return ''; } try { const arrayBuffer = await decryptWithServerAction(file); const workbook = new ExcelJS.Workbook(); await workbook.xlsx.load(arrayBuffer); const worksheet = workbook.getWorksheet(1); if (!worksheet) { return { errorMessage: '워크시트를 찾을 수 없습니다.' }; } const errors: string[] = []; const importDataList: { evaluationYear: number; division: string; vendorId: number; vendorCode: string; vendorName: string; materialType: string; adminComment: string; ldClaimCount: number; ldClaimAmount: number; ldClaimCurrency: string; reviewers: { departmentCode: string; reviewerUserId: number; }[]; }[] = []; const rows = worksheet.getRows(4, worksheet.rowCount - 3); if (!rows) { return { errorMessage: '새로 추가할 평가 대상 데이터가 존재하지 않습니다.' }; } const duplicateCheckSet = new Set(); for (const [index, row] of rows.entries()) { const rowIndex = index + 4; try { const rawDivision = row.getCell(2).value?.toString().trim() || ''; const rawMaterialType = row.getCell(5).value?.toString().trim() || ''; const rawCurrency = row.getCell(9).value?.toString().trim() || 'KRW (원)'; const rowData = { evaluationYear: Number(row.getCell(1).value) || 0, division: DIVISION_MAP[rawDivision] || '', vendorId: 0, vendorCode: row.getCell(3).value?.toString().trim() || '', vendorName: row.getCell(4).value?.toString().trim() || '', materialType: MATERIAL_TYPE_MAP[rawMaterialType] || '', adminComment: row.getCell(6).value?.toString().trim() || '', ldClaimCount: Number(row.getCell(7).value) || 0, ldClaimAmount: Number(row.getCell(8).value) || 0, ldClaimCurrency: CURRENCY_MAP[rawCurrency] || 'KRW', reviewers: [] as { departmentCode: string; reviewerUserId: number; }[], }; if (!rowData.evaluationYear && !rowData.division && !rowData.vendorCode && !rowData.materialType) { continue; } if (!rowData.evaluationYear || !rowData.division || !rowData.vendorCode || !rowData.materialType) { errors.push(`행 ${rowIndex}: 필수 필드(평가년도, 구분, 협력업체 코드, 자재구분)가 누락되었습니다.`); continue; } if (!Object.values(DIVISION_MAP).includes(rowData.division)) { errors.push(`행 ${rowIndex}: 구분 값(${rawDivision})이 잘못되었습니다.`); continue; } if (!Object.values(MATERIAL_TYPE_MAP).includes(rowData.materialType)) { errors.push(`행 ${rowIndex}: 자재구분 값(${rawMaterialType})이 잘못되었습니다.`); continue; } if (!Object.values(CURRENCY_MAP).includes(rowData.ldClaimCurrency)) { errors.push(`행 ${rowIndex}: 통화 값(${rawCurrency})이 잘못되었습니다.`); continue; } const duplicateKey = `${rowData.evaluationYear}_${rowData.division}_${rowData.vendorCode}`; if (duplicateCheckSet.has(duplicateKey)) { errors.push(`행 ${rowIndex}: 시트 내에 중복된 평가 대상 항목이 있습니다. 평가 대상은 평가년도, 구분, 협력업체가 모두 같을 수 없습니다.`); continue; } duplicateCheckSet.add(duplicateKey); const existingTargetList = await db.transaction(async (tx) => { const selectRes = await tx .select({ id: evaluationTargets.id }) .from(evaluationTargets) .where( and( eq(evaluationTargets.evaluationYear, rowData.evaluationYear), eq(evaluationTargets.vendorCode, rowData.vendorCode), eq(evaluationTargets.division, rowData.division as 'PLANT' | 'SHIP'), ) ) .limit(1); return selectRes; }); if (existingTargetList.length > 0) { errors.push(`행 ${rowIndex}: 등록된 평가 대상 항목이 이미 존재합니다. 평가 대상은 평가년도, 구분, 협력업체가 모두 같을 수 없습니다.`); continue; } const { vendorList } = await db.transaction(async (tx) => { const selectRes = await selectVendors(tx, { where: eq(vendors.vendorCode, rowData.vendorCode), }); return { vendorList: selectRes }; }); if (vendorList.length === 0) { errors.push(`행 ${rowIndex}: 협력업체 코드(${rowData.vendorCode})가 존재하지 않습니다. 다시 한 번 확인해주십시오.`); continue; } rowData.vendorId = vendorList[0].id; const DEPARTMENT_MAP = [ { code: 'ORDER_EVAL', label: '발주 평가 담당', nameCol: 10, emailCol: 11 }, { code: 'PROCUREMENT_EVAL', label: '조달 평가 담당', nameCol: 12, emailCol: 13 }, { code: 'QUALITY_EVAL', label: '품질 평가 담당', nameCol: 14, emailCol: 15 }, { code: 'DESIGN_EVAL', label: '설계 평가 담당', nameCol: 16, emailCol: 17 }, { code: 'CS_EVAL', label: 'CS 평가 담당', nameCol: 18, emailCol: 19 }, ]; for (const department of DEPARTMENT_MAP) { const managerName = getCellText(row.getCell(department.nameCol)); const managerEmail = getCellText(row.getCell(department.emailCol)); if (managerEmail) { if (rowData.materialType === 'BULK' && ['DESIGN_EVAL', 'CS_EVAL'].includes(department.code)) { errors.push(`행 ${rowIndex}: 자재구분이 '벌크'인 경우, ${department.label}자를 지정할 수 없습니다.`); continue; } const { userList } = await db.transaction(async (tx) => { const selectRes = await selectUsers(tx, { where: and(eq(users.email, managerEmail), eq(users.isActive, true), ne(users.domain, "partners")), orderBy: [asc(users.name)], }); return { userList: selectRes }; }); if (userList.length === 0) { errors.push(`행 ${rowIndex}: 입력한 ${department.label}(${managerName}/${managerEmail})이 존재하지 않습니다. 다시 한 번 확인해주십시오.`); continue; } rowData.reviewers.push({ departmentCode: department.code, reviewerUserId: userList[0].id, }); } } if (rowData.reviewers.length === 0) { errors.push(`행 ${rowIndex}: 최소 한 명 이상의 담당자를 지정해주십시오.`); continue; } importDataList.push(rowData); } catch (error) { errors.push(`행 ${rowIndex}: 데이터 처리 중 오류가 발생했습니다 - ${error}`); } } if (errors.length > 0) { const errorWorkbook = new ExcelJS.Workbook(); const errorWorksheet = errorWorkbook.addWorksheet('Import Errors'); errorWorksheet.columns = [ { header: '오류 내용', key: 'error', width: 80 }, ]; errorWorksheet.getRow(1).font = { bold: true }; errorWorksheet.getRow(1).fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: 'FFFF0000' } }; errors.forEach(error => { errorWorksheet.addRow({ error }); }); const buffer = await errorWorkbook.xlsx.writeBuffer(); const errorFile = new Blob([buffer], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' }); return { errorFile, errorMessage: `${errors.length}개의 오류가 발견되었습니다. 오류 파일을 확인하십시오.` }; } if (importDataList.length === 0) { return { errorMessage: '새로 추가할 평가 대상 데이터가 존재하지 않습니다. 파일을 다시 한 번 확인해주십시오.' }; } const currentUserId = await getCurrentUserId(); for (const importData of importDataList) { const { evaluationYear, division, vendorId, materialType, adminComment, ldClaimCount, ldClaimAmount, ldClaimCurrency, reviewers, } = importData; await createEvaluationTarget( { evaluationYear, division: division as 'PLANT' | 'SHIP', vendorId, materialType: materialType as 'EQUIPMENT' | 'BULK', adminComment, ldClaimCount, ldClaimAmount, ldClaimCurrency: ldClaimCurrency as 'KRW' | 'USD' | 'EUR' | 'JPY', reviewers: reviewers as { departmentCode: 'ORDER_EVAL' | 'PROCUREMENT_EVAL' | 'QUALITY_EVAL' | 'DESIGN_EVAL' | 'CS_EVAL'; reviewerUserId: number; }[], }, currentUserId, ); } return { successMessage: `Excel 파일이 성공적으로 업로드되었습니다. ${importDataList.length}개의 평가 대상이 추가되었습니다.` }; } catch (error) { console.error('Error in Importing Evaluation Targets from Excel:', error); return { errorMessage: 'Excel 파일 처리 중 오류가 발생했습니다.' }; } }