From 0793ef1e3aa2232ce72debd57ba449a699e0c734 Mon Sep 17 00:00:00 2001 From: dujinkim Date: Fri, 4 Jul 2025 04:32:04 +0000 Subject: (최겸) 0704 평가기준표 수정(create, detail, update 및 excel 기능) MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit --- lib/evaluation-criteria/service.ts | 684 +++++++++++++++++++++++++++++++++++-- 1 file changed, 660 insertions(+), 24 deletions(-) (limited to 'lib/evaluation-criteria/service.ts') diff --git a/lib/evaluation-criteria/service.ts b/lib/evaluation-criteria/service.ts index e204579f..19f2dd81 100644 --- a/lib/evaluation-criteria/service.ts +++ b/lib/evaluation-criteria/service.ts @@ -11,12 +11,10 @@ import { or,count, eq } from 'drizzle-orm'; import { - countRegEvalCriteria, deleteRegEvalCriteria, deleteRegEvalCriteriaDetails, insertRegEvalCriteria, insertRegEvalCriteriaDetails, - selectRegEvalCriteria, selectRegEvalCriteriaWithDetails, updateRegEvalCriteria, updateRegEvalCriteriaDetails, @@ -24,17 +22,20 @@ import { import db from '@/db/db'; import { filterColumns } from '@/lib/filter-columns'; import { - REG_EVAL_CRITERIA_CATEGORY2_ENUM, - REG_EVAL_CRITERIA_CATEGORY_ENUM, - REG_EVAL_CRITERIA_ITEM_ENUM, - regEvalCriteria, regEvalCriteriaDetails, // regEvalCriteriaView 대신 regEvalCriteria 사용 + regEvalCriteria, regEvalCriteriaDetails, type NewRegEvalCriteria, type NewRegEvalCriteriaDetails, type RegEvalCriteria, type RegEvalCriteriaDetails, - type RegEvalCriteriaView, } from '@/db/schema'; import { type GetRegEvalCriteriaSchema } from './validations'; +import { getServerSession } from 'next-auth'; +import { authOptions } from '@/app/api/auth/[...nextauth]/route'; + +/* EXCEL EXPORT SERVER ACTIONS */ +import ExcelJS from "exceljs"; +import { REG_EVAL_CRITERIA_CATEGORY, REG_EVAL_CRITERIA_CATEGORY2, REG_EVAL_CRITERIA_ITEM } from "@/db/schema"; + // ---------------------------------------------------------------------------------------------------- @@ -42,6 +43,19 @@ import { type GetRegEvalCriteriaSchema } from './validations'; // ---------------------------------------------------------------------------------------------------- +/* HELPER FUNCTION FOR GETTING CURRENT USER ID */ +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 getting current user ID:', error); + return 3; // 기본값 3 + } +} + +// ---------------------------------------------------------------------------------------------------- + /* FUNCTION FOR GETTING CRITERIA - 메인 기준 목록만 가져오기 */ async function getRegEvalCriteria(input: GetRegEvalCriteriaSchema) { try { @@ -116,8 +130,6 @@ async function getRegEvalCriteriaWithDetails(id: number) { /* FUNCTION FOR GETTING CRITERIA DETAILS ONLY - 특정 기준의 상세 항목들만 가져오기 */ async function getRegEvalCriteriaDetails(criteriaId: number) { try { - - console.log(criteriaId,"criteriaId") return await db.transaction(async (tx) => { const details = await tx .select() @@ -135,14 +147,16 @@ async function getRegEvalCriteriaDetails(criteriaId: number) { // ---------------------------------------------------------------------------------------------------- -/* FUNCTION FOR CREATING CRITERIA WITH DETAILS */ -async function createRegEvalCriteriaWithDetails( +/* FUNCTION FOR CREATING CRITERIA WITH FIXED SCORES */ +async function createRegEvalCriteriaFixed( criteriaData: NewRegEvalCriteria, detailList: Omit[], ) { try { return await db.transaction(async (tx) => { - const criteria = await insertRegEvalCriteria(tx, criteriaData); + const criteria = await insertRegEvalCriteria(tx, { + ...criteriaData, + }); const criteriaId = criteria.id; const newDetailList = detailList.map((detailItem, index) => ({ ...detailItem, @@ -158,25 +172,50 @@ async function createRegEvalCriteriaWithDetails( return { ...criteria, criteriaDetails }; }); } catch (error) { - console.error('Error in Creating New Regular Evaluation Criteria with Details: ', error); - throw new Error('Failed to Create New Regular Evaluation Criteria with Details'); + console.error('Error in Creating New Regular Evaluation Criteria (Fixed): ', error); + throw new Error('Failed to Create New Regular Evaluation Criteria (Fixed)'); + } +} + +/* FUNCTION FOR CREATING CRITERIA WITH VARIABLE SCORES */ +async function createRegEvalCriteriaVariable( + criteriaData: NewRegEvalCriteria, +) { + try { + return await db.transaction(async (tx) => { + const criteria = await insertRegEvalCriteria(tx, { + ...criteriaData, + }); + + return { ...criteria, criteriaDetails: [] }; + }); + } catch (error) { + console.error('Error in Creating New Regular Evaluation Criteria (Variable): ', error); + throw new Error('Failed to Create New Regular Evaluation Criteria (Variable)'); } } // ---------------------------------------------------------------------------------------------------- -/* FUNCTION FOR MODIFYING CRITERIA WITH DETAILS */ -async function modifyRegEvalCriteriaWithDetails( +/* FUNCTION FOR MODIFYING CRITERIA WITH FIXED SCORES */ +async function modifyRegEvalCriteriaFixed( id: number, criteriaData: Partial, detailList: Partial[], ) { try { + const currentUserId = await getCurrentUserId(); + return await db.transaction(async (tx) => { + // 고정 점수용 criteria 업데이트 (variableScore 필드는 null로 설정) + const modifiedCriteria = await updateRegEvalCriteria(tx, id, { + ...criteriaData, + variableScoreMin: null, + variableScoreMax: null, + variableScoreUnit: null, + updatedBy: currentUserId, + }); - console.log(id, criteriaData) - - const modifiedCriteria = await updateRegEvalCriteria(tx, id, criteriaData); const originCriteria = await getRegEvalCriteriaWithDetails(id); const originCriteriaDetails = originCriteria?.criteriaDetails || []; const detailIdList = detailList @@ -186,10 +225,12 @@ async function modifyRegEvalCriteriaWithDetails( (item) => !detailIdList.includes(item.id), ); + // 기존 세부사항 삭제 for (const item of toDeleteIdList) { await deleteRegEvalCriteriaDetails(tx, item.id); } + // 세부사항 업데이트/생성 const criteriaDetails = []; for (let idx = 0; idx < detailList.length; idx += 1) { const detailItem = detailList[idx]; @@ -214,8 +255,39 @@ async function modifyRegEvalCriteriaWithDetails( return { ...modifiedCriteria, criteriaDetails }; }); } catch (error) { - console.error('Error in Modifying Regular Evaluation Criteria with Details: ', error); - throw new Error('Failed to Modify Regular Evaluation Criteria with Details'); + console.error('Error in Modifying Regular Evaluation Criteria with Fixed Scores: ', error); + throw new Error('Failed to Modify Regular Evaluation Criteria with Fixed Scores'); + } +} + +/* FUNCTION FOR MODIFYING CRITERIA WITH VARIABLE SCORES */ +async function modifyRegEvalCriteriaVariable( + id: number, + criteriaData: Partial, +) { + try { + const currentUserId = await getCurrentUserId(); + + return await db.transaction(async (tx) => { + // 변동 점수용 criteria 업데이트 (기존 details 모두 삭제) + const modifiedCriteria = await updateRegEvalCriteria(tx, id, { + ...criteriaData, + updatedBy: currentUserId, + }); + + // 변동 점수에서는 기존 모든 세부사항 삭제 + const originCriteria = await getRegEvalCriteriaWithDetails(id); + const originCriteriaDetails = originCriteria?.criteriaDetails || []; + + for (const item of originCriteriaDetails) { + await deleteRegEvalCriteriaDetails(tx, item.id); + } + + return { ...modifiedCriteria, criteriaDetails: [] }; + }); + } catch (error) { + console.error('Error in Modifying Regular Evaluation Criteria with Variable Scores: ', error); + throw new Error('Failed to Modify Regular Evaluation Criteria with Variable Scores'); } } @@ -245,15 +317,579 @@ async function removeRegEvalCriteriaDetails(id: number) { } } +/* FUNCTION FOR GENERATING EXCEL FILE FROM DATABASE */ +async function generateRegEvalCriteriaExcel(): Promise { + try { + const workbook = new ExcelJS.Workbook(); + workbook.creator = "EVCP System"; + workbook.created = new Date(); + + const worksheet = workbook.addWorksheet("평가 기준"); + + const columnHeaders = [ + { key: "category", header: "평가부문", width: 15 }, + { key: "category2", header: "점수구분", width: 15 }, + { key: "item", header: "항목", width: 15 }, + { key: "classification", header: "구분", width: 20 }, + { key: "range", header: "범위", width: 20 }, + { key: "remarks", header: "비고", width: 20 }, + { key: "scoreType", header: "점수 유형", width: 15 }, + { key: "variableScoreMin", header: "최소 점수", width: 15 }, + { key: "variableScoreMax", header: "최대 점수", width: 15 }, + { key: "variableScoreUnit", header: "단위", width: 15 }, + { key: "detail", header: "평가내용", width: 30 }, + { key: "scoreEquipShip", header: "기자재-조선", width: 18 }, + { key: "scoreEquipMarine", header: "기자재-해양", width: 18 }, + { key: "scoreBulkShip", header: "벌크-조선", width: 18 }, + { key: "scoreBulkMarine", header: "벌크-해양", width: 18 }, + { key: "orderIndex", header: "정렬 순서", width: 12 }, + ]; + + worksheet.columns = columnHeaders; + + const headerRow = worksheet.getRow(1); + headerRow.font = { bold: true }; + headerRow.fill = { + type: "pattern", + pattern: "solid", + fgColor: { argb: "FFCCCCCC" }, + }; + headerRow.alignment = { horizontal: "center", vertical: "middle" }; + + // 실제 DB에서 1:n 관계 데이터 가져오기 + const allCriteria = await db.transaction(async (tx) => { + const criteria = await tx + .select() + .from(regEvalCriteria) + .orderBy(asc(regEvalCriteria.id)); + + const result: any[] = []; + for (const criterion of criteria) { + const details = await tx + .select() + .from(regEvalCriteriaDetails) + .where(eq(regEvalCriteriaDetails.criteriaId, criterion.id)) + .orderBy(asc(regEvalCriteriaDetails.orderIndex)); + + if (details.length === 0) { + result.push({ + category: criterion.category, + category2: criterion.category2, + item: criterion.item, + classification: criterion.classification, + range: criterion.range || '', + remarks: criterion.remarks || '', + scoreType: criterion.scoreType === 'fixed' ? '고정' : '변동', + variableScoreMin: criterion.variableScoreMin || '', + variableScoreMax: criterion.variableScoreMax || '', + variableScoreUnit: criterion.variableScoreUnit || '', + detail: '', + scoreEquipShip: '', + scoreEquipMarine: '', + scoreBulkShip: '', + scoreBulkMarine: '', + orderIndex: '', + }); + } else { + for (const detail of details) { + result.push({ + category: criterion.category, + category2: criterion.category2, + item: criterion.item, + classification: criterion.classification, + range: criterion.range || '', + remarks: criterion.remarks || '', + scoreType: criterion.scoreType === 'fixed' ? '고정' : '변동', + variableScoreMin: criterion.variableScoreMin || '', + variableScoreMax: criterion.variableScoreMax || '', + variableScoreUnit: criterion.variableScoreUnit || '', + detail: detail.detail, + scoreEquipShip: detail.scoreEquipShip || '', + scoreEquipMarine: detail.scoreEquipMarine || '', + scoreBulkShip: detail.scoreBulkShip || '', + scoreBulkMarine: detail.scoreBulkMarine || '', + orderIndex: detail.orderIndex?.toString() || '', + }); + } + } + } + return result; + }); + + allCriteria.forEach((row) => { + const rowData: Record = {}; + columnHeaders.forEach((col) => { + let value = row[col.key as keyof typeof row]; + + if (col.key === "category") { + value = REG_EVAL_CRITERIA_CATEGORY.find(item => item.value === value)?.label || value; + } else if (col.key === "category2") { + value = REG_EVAL_CRITERIA_CATEGORY2.find(item => item.value === value)?.label || value; + } else if (col.key === "item") { + value = REG_EVAL_CRITERIA_ITEM.find(item => item.value === value)?.label || value; + } + + rowData[col.key] = value || ""; + }); + worksheet.addRow(rowData); + }); + + // 데이터 검증을 위한 숨겨진 시트 생성 + const validationSheet = workbook.addWorksheet("ValidationData"); + validationSheet.state = "hidden"; + + const categoryOptions = REG_EVAL_CRITERIA_CATEGORY.map(item => item.label); + const category2Options = REG_EVAL_CRITERIA_CATEGORY2.map(item => item.label); + const itemOptions = REG_EVAL_CRITERIA_ITEM.map(item => item.label); + const scoreTypeOptions = ['고정', '변동']; + + validationSheet.getColumn(1).values = ["평가부문", ...categoryOptions]; + validationSheet.getColumn(2).values = ["점수구분", ...category2Options]; + validationSheet.getColumn(3).values = ["항목", ...itemOptions]; + validationSheet.getColumn(4).values = ["점수 유형", ...scoreTypeOptions]; + + // 드롭다운 설정 + const categoryColIndex = columnHeaders.findIndex(col => col.key === "category") + 1; + const category2ColIndex = columnHeaders.findIndex(col => col.key === "category2") + 1; + const itemColIndex = columnHeaders.findIndex(col => col.key === "item") + 1; + const scoreTypeColIndex = columnHeaders.findIndex(col => col.key === "scoreType") + 1; + + const dataRowCount = allCriteria.length + 1; // 헤더 포함 + const maxRows = Math.max(dataRowCount + 50, 1000); // 추가 행을 위한 여유 + + if (categoryColIndex > 0) { + (worksheet as any).dataValidations.add(`${worksheet.getColumn(categoryColIndex).letter}2:${worksheet.getColumn(categoryColIndex).letter}${maxRows}`, { + type: "list", + allowBlank: false, + formulae: [`ValidationData!$A$2:$A$${categoryOptions.length + 1}`], + }); + } + + if (category2ColIndex > 0) { + (worksheet as any).dataValidations.add(`${worksheet.getColumn(category2ColIndex).letter}2:${worksheet.getColumn(category2ColIndex).letter}${maxRows}`, { + type: "list", + allowBlank: false, + formulae: [`ValidationData!$B$2:$B$${category2Options.length + 1}`], + }); + } + + if (itemColIndex > 0) { + (worksheet as any).dataValidations.add(`${worksheet.getColumn(itemColIndex).letter}2:${worksheet.getColumn(itemColIndex).letter}${maxRows}`, { + type: "list", + allowBlank: false, + formulae: [`ValidationData!$C$2:$C$${itemOptions.length + 1}`], + }); + } + + if (scoreTypeColIndex > 0) { + (worksheet as any).dataValidations.add(`${worksheet.getColumn(scoreTypeColIndex).letter}2:${worksheet.getColumn(scoreTypeColIndex).letter}${maxRows}`, { + type: "list", + allowBlank: false, + formulae: [`ValidationData!$D$2:$D$${scoreTypeOptions.length + 1}`], + }); + } + + worksheet.eachRow((row) => { + row.eachCell((cell) => { + 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 file:', error); + throw new Error('Failed to generate Excel file'); + } +} + +/* FUNCTION FOR GENERATING EXCEL TEMPLATE */ +async function generateRegEvalCriteriaTemplate(): Promise { + try { + const workbook = new ExcelJS.Workbook(); + workbook.creator = "EVCP System"; + workbook.created = new Date(); + + const worksheet = workbook.addWorksheet("평가 기준 템플릿"); + + const templateHeaders = [ + { key: "category", header: "평가부문", width: 15 }, + { key: "category2", header: "점수구분", width: 15 }, + { key: "item", header: "항목", width: 15 }, + { key: "classification", header: "구분", width: 20 }, + { key: "range", header: "범위", width: 20 }, + { key: "remarks", header: "비고", width: 20 }, + { key: "scoreType", header: "점수 유형", width: 15 }, + { key: "variableScoreMin", header: "최소 점수", width: 15 }, + { key: "variableScoreMax", header: "최대 점수", width: 15 }, + { key: "variableScoreUnit", header: "단위", width: 15 }, + { key: "detail", header: "평가내용", width: 30 }, + { key: "scoreEquipShip", header: "기자재-조선", width: 18 }, + { key: "scoreEquipMarine", header: "기자재-해양", width: 18 }, + { key: "scoreBulkShip", header: "벌크-조선", width: 18 }, + { key: "scoreBulkMarine", header: "벌크-해양", width: 18 }, + { key: "orderIndex", header: "정렬 순서", width: 12 }, + ]; + + worksheet.columns = templateHeaders; + + const headerRow = worksheet.getRow(1); + headerRow.font = { bold: true }; + headerRow.fill = { + type: "pattern", + pattern: "solid", + fgColor: { argb: "FFCCCCCC" }, + }; + headerRow.alignment = { horizontal: "center", vertical: "middle" }; + + const exampleData = [ + { + category: "품질", + category2: "공정", + item: "품질", + classification: "품질관리시스템", + range: "ISO 9001 인증", + remarks: "국제 표준 품질관리시스템 인증", + scoreType: "고정", + variableScoreMin: "", + variableScoreMax: "", + variableScoreUnit: "", + detail: "우수 (ISO 9001 인증보유)", + scoreEquipShip: "10", + scoreEquipMarine: "10", + scoreBulkShip: "8", + scoreBulkMarine: "8", + orderIndex: "1", + }, + { + category: "품질", + category2: "공정", + item: "품질", + classification: "품질관리시스템", + range: "ISO 9001 인증", + remarks: "국제 표준 품질관리시스템 인증", + scoreType: "고정", + variableScoreMin: "", + variableScoreMax: "", + variableScoreUnit: "", + detail: "보통 (자체 품질관리시스템)", + scoreEquipShip: "6", + scoreEquipMarine: "6", + scoreBulkShip: "5", + scoreBulkMarine: "5", + orderIndex: "2", + }, + { + category: "품질", + category2: "공정", + item: "품질", + classification: "품질관리시스템", + range: "ISO 9001 인증", + remarks: "국제 표준 품질관리시스템 인증", + scoreType: "고정", + variableScoreMin: "", + variableScoreMax: "", + variableScoreUnit: "", + detail: "미흡 (품질관리시스템 미비)", + scoreEquipShip: "2", + scoreEquipMarine: "2", + scoreBulkShip: "1", + scoreBulkMarine: "1", + orderIndex: "3", + }, + { + category: "조달", + category2: "가격", + item: "납기", + classification: "납기준수율", + range: "최근 3년간 납기준수율", + remarks: "계약대비 실제 납기준수 비율", + scoreType: "변동", + variableScoreMin: "0", + variableScoreMax: "20", + variableScoreUnit: "점", + detail: "", + scoreEquipShip: "", + scoreEquipMarine: "", + scoreBulkShip: "", + scoreBulkMarine: "", + orderIndex: "", + }, + ]; + + exampleData.forEach((row) => { + worksheet.addRow(row); + }); + + + const validationSheet = workbook.addWorksheet("ValidationData"); + validationSheet.state = "hidden"; + + const categoryOptions = REG_EVAL_CRITERIA_CATEGORY.map(item => item.label); + const category2Options = REG_EVAL_CRITERIA_CATEGORY2.map(item => item.label); + const itemOptions = REG_EVAL_CRITERIA_ITEM.map(item => item.label); + const scoreTypeOptions = ['고정', '변동']; + + validationSheet.getColumn(1).values = ["평가부문", ...categoryOptions]; + validationSheet.getColumn(2).values = ["점수구분", ...category2Options]; + validationSheet.getColumn(3).values = ["항목", ...itemOptions]; + validationSheet.getColumn(4).values = ["점수 유형", ...scoreTypeOptions]; + + const categoryColIndex = templateHeaders.findIndex(col => col.key === "category") + 1; + const category2ColIndex = templateHeaders.findIndex(col => col.key === "category2") + 1; + const itemColIndex = templateHeaders.findIndex(col => col.key === "item") + 1; + const scoreTypeColIndex = templateHeaders.findIndex(col => col.key === "scoreType") + 1; + + if (categoryColIndex > 0) { + (worksheet as any).dataValidations.add(`${worksheet.getColumn(categoryColIndex).letter}2:${worksheet.getColumn(categoryColIndex).letter}1000`, { + type: "list", + allowBlank: false, + formulae: [`ValidationData!$A$2:$A$${categoryOptions.length + 1}`], + }); + } + + if (category2ColIndex > 0) { + (worksheet as any).dataValidations.add(`${worksheet.getColumn(category2ColIndex).letter}2:${worksheet.getColumn(category2ColIndex).letter}1000`, { + type: "list", + allowBlank: false, + formulae: [`ValidationData!$B$2:$B$${category2Options.length + 1}`], + }); + } + + if (itemColIndex > 0) { + (worksheet as any).dataValidations.add(`${worksheet.getColumn(itemColIndex).letter}2:${worksheet.getColumn(itemColIndex).letter}1000`, { + type: "list", + allowBlank: false, + formulae: [`ValidationData!$C$2:$C$${itemOptions.length + 1}`], + }); + } + + if (scoreTypeColIndex > 0) { + (worksheet as any).dataValidations.add(`${worksheet.getColumn(scoreTypeColIndex).letter}2:${worksheet.getColumn(scoreTypeColIndex).letter}1000`, { + type: "list", + allowBlank: false, + formulae: [`ValidationData!$D$2:$D$${scoreTypeOptions.length + 1}`], + }); + } + + worksheet.eachRow((row) => { + row.eachCell((cell) => { + 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'); + } +} + +/* EXCEL IMPORT CLIENT-FRIENDLY SERVER ACTION */ +async function importRegEvalCriteriaExcel(file: File): Promise<{ + errorFile?: Blob; + errorMessage?: string; + successMessage?: string; +}> { + try { + const arrayBuffer = await file.arrayBuffer(); + const workbook = new ExcelJS.Workbook(); + await workbook.xlsx.load(arrayBuffer); + + const worksheet = workbook.getWorksheet(1); + if (!worksheet) { + return { errorMessage: '워크시트를 찾을 수 없습니다.' }; + } + + const errors: string[] = []; + const groupedData: Map = new Map(); + + // 각 행 처리 + worksheet.eachRow((row, rowIndex) => { + if (rowIndex === 1) return; // 헤더 건너뛰기 + + try { + const rowData = { + category: row.getCell(1).value?.toString()?.trim() || '', + category2: row.getCell(2).value?.toString()?.trim() || '', + item: row.getCell(3).value?.toString()?.trim() || '', + classification: row.getCell(4).value?.toString()?.trim() || '', + range: row.getCell(5).value?.toString()?.trim() || '', + remarks: row.getCell(6).value?.toString()?.trim() || '', + scoreType: row.getCell(7).value?.toString()?.trim() || '', + variableScoreMin: row.getCell(8).value || '', + variableScoreMax: row.getCell(9).value || '', + variableScoreUnit: row.getCell(10).value?.toString()?.trim() || '', + detail: row.getCell(11).value?.toString()?.trim() || '', + scoreEquipShip: row.getCell(12).value || '', + scoreEquipMarine: row.getCell(13).value || '', + scoreBulkShip: row.getCell(14).value || '', + scoreBulkMarine: row.getCell(15).value || '', + orderIndex: row.getCell(16).value || '', + }; + + // 빈 행 건너뛰기 + if (!rowData.category && !rowData.category2 && !rowData.item && !rowData.classification) { + return; + } + + // 필수 필드 검증 + if (!rowData.category || !rowData.category2 || !rowData.item || !rowData.classification) { + errors.push(`행 ${rowIndex}: 필수 필드(평가부문, 점수구분, 항목, 구분)가 누락되었습니다.`); + return; + } + + // 점수 유형 검증 + if (rowData.scoreType !== '고정' && rowData.scoreType !== '변동') { + errors.push(`행 ${rowIndex}: 점수 유형은 '고정' 또는 '변동'이어야 합니다.`); + return; + } + + // 변동 점수 유형 검증 + if (rowData.scoreType === '변동') { + if (rowData.scoreEquipShip || rowData.scoreEquipMarine || + rowData.scoreBulkShip || rowData.scoreBulkMarine) { + errors.push(`행 ${rowIndex}: 변동 점수 유형에서는 개별 배점을 입력할 수 없습니다.`); + return; + } + if (rowData.detail) { + errors.push(`행 ${rowIndex}: 변동 점수 유형에서는 평가내용을 입력할 수 없습니다.`); + return; + } + } + + // 고정 점수 유형 검증 + if (rowData.scoreType === '고정') { + if (!rowData.detail) { + errors.push(`행 ${rowIndex}: 고정 점수 유형에서는 평가내용이 필수입니다.`); + return; + } + } + + // 한글 라벨을 영어 값으로 변환 + const categoryValue = REG_EVAL_CRITERIA_CATEGORY.find(item => item.label === rowData.category)?.value || rowData.category; + const category2Value = REG_EVAL_CRITERIA_CATEGORY2.find(item => item.label === rowData.category2)?.value || rowData.category2; + const itemValue = REG_EVAL_CRITERIA_ITEM.find(item => item.label === rowData.item)?.value || rowData.item; + + // 데이터 그룹화 + const groupKey = `${categoryValue}-${category2Value}-${itemValue}-${rowData.classification}`; + if (!groupedData.has(groupKey)) { + groupedData.set(groupKey, []); + } + groupedData.get(groupKey)!.push({ + ...rowData, + category: categoryValue, + category2: category2Value, + item: itemValue, + scoreType: rowData.scoreType === '고정' ? 'fixed' : 'variable' + }); + + } catch (error) { + errors.push(`행 ${rowIndex}: 데이터 처리 중 오류가 발생했습니다 - ${error}`); + } + }); + + // 에러가 있으면 에러 파일 생성 (Blob으로 반환) + 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}개의 오류가 발견되었습니다. 오류 파일을 확인하세요.` + }; + } + + // 성공한 데이터 처리 + let importedCount = 0; + const currentUserId = await getCurrentUserId(); + + for (const [, groupData] of groupedData) { + const firstRow = groupData[0]; + + const criteriaData = { + category: firstRow.category, + category2: firstRow.category2, + item: firstRow.item, + classification: firstRow.classification, + range: firstRow.range || null, + remarks: firstRow.remarks || null, + scoreType: firstRow.scoreType, + variableScoreMin: firstRow.variableScoreMin ? String(firstRow.variableScoreMin) : null, + variableScoreMax: firstRow.variableScoreMax ? String(firstRow.variableScoreMax) : null, + variableScoreUnit: firstRow.variableScoreUnit || null, + createdBy: currentUserId, + updatedBy: currentUserId, + }; + + if (firstRow.scoreType === 'fixed') { + const detailList = groupData.map((detailData, index) => ({ + detail: detailData.detail, + scoreEquipShip: detailData.scoreEquipShip ? String(detailData.scoreEquipShip) : null, + scoreEquipMarine: detailData.scoreEquipMarine ? String(detailData.scoreEquipMarine) : null, + scoreBulkShip: detailData.scoreBulkShip ? String(detailData.scoreBulkShip) : null, + scoreBulkMarine: detailData.scoreBulkMarine ? String(detailData.scoreBulkMarine) : null, + orderIndex: detailData.orderIndex ? parseInt(String(detailData.orderIndex)) : index, + })); + + await createRegEvalCriteriaFixed(criteriaData, detailList); + } else { + await createRegEvalCriteriaVariable(criteriaData); + } + + importedCount++; + } + + return { successMessage: `Excel 파일이 성공적으로 업로드되었습니다. ${importedCount}개의 평가 기준이 추가되었습니다.` }; + } catch (error) { + console.error('Error in Importing Regular Evaluation Criteria from Excel:', error); + return { errorMessage: 'Excel 파일 처리 중 오류가 발생했습니다.' }; + } +} + // ---------------------------------------------------------------------------------------------------- /* EXPORT */ export { - createRegEvalCriteriaWithDetails, - modifyRegEvalCriteriaWithDetails, + createRegEvalCriteriaFixed, + createRegEvalCriteriaVariable, + modifyRegEvalCriteriaFixed, + modifyRegEvalCriteriaVariable, getRegEvalCriteria, getRegEvalCriteriaWithDetails, - getRegEvalCriteriaDetails, // 새로 추가 + getRegEvalCriteriaDetails, removeRegEvalCriteria, removeRegEvalCriteriaDetails, + generateRegEvalCriteriaExcel, + generateRegEvalCriteriaTemplate, + importRegEvalCriteriaExcel, }; \ No newline at end of file -- cgit v1.2.3