diff options
Diffstat (limited to 'lib/evaluation-criteria/excel/reg-eval-criteria-excel-import.ts')
| -rw-r--r-- | lib/evaluation-criteria/excel/reg-eval-criteria-excel-import.ts | 417 |
1 files changed, 0 insertions, 417 deletions
diff --git a/lib/evaluation-criteria/excel/reg-eval-criteria-excel-import.ts b/lib/evaluation-criteria/excel/reg-eval-criteria-excel-import.ts deleted file mode 100644 index 92c7a25e..00000000 --- a/lib/evaluation-criteria/excel/reg-eval-criteria-excel-import.ts +++ /dev/null @@ -1,417 +0,0 @@ -/* eslint-disable @typescript-eslint/no-explicit-any */ - -'use server'; - -/* IMPORT */ -import * as ExcelJS from 'exceljs'; -import { filterColumns } from '@/lib/filter-columns'; -import { - regEvalCriteriaColumnsConfig, -} from '@/config/regEvalCriteriaColumnsConfig'; -import { - REG_EVAL_CRITERIA_CATEGORY2_ENUM, - REG_EVAL_CRITERIA_CATEGORY_ENUM, - REG_EVAL_CRITERIA_ITEM_ENUM, - REG_EVAL_CRITERIA_CATEGORY, - REG_EVAL_CRITERIA_CATEGORY2, - REG_EVAL_CRITERIA_ITEM, - type NewRegEvalCriteria, - type NewRegEvalCriteriaDetails, - type RegEvalCriteria, - type RegEvalCriteriaDetails, - type RegEvalCriteriaView, -} from '@/db/schema'; -import { - createRegEvalCriteriaWithDetails, - modifyRegEvalCriteriaWithDetails, - removeRegEvalCriteria, - getRegEvalCriteria, -} from '../service'; -import db from '@/db/db'; -import { selectRegEvalCriteria } from '../repository'; - -// ---------------------------------------------------------------------------------------------------- - -/* TYPES */ -interface ImportResult { - errorFile: File | null, - errorMessage: string | null, - successMessage?: string, -} -type ExcelRowData = { - criteriaData: NewRegEvalCriteria, - detailList: (NewRegEvalCriteriaDetails & { rowIndex: number, toDelete: boolean })[], -} - -// ---------------------------------------------------------------------------------------------------- - -/* CONSTANTS */ -const HEADER_ROW_INDEX = 1; -const DATA_START_ROW_INDEX = 2; - -// 영문과 한글 헤더 매핑 -const HEADER_MAPPING: Record<string, string> = { - // 영문 헤더 - 'Category': 'category', - 'Score Category': 'category2', - 'Item': 'item', - 'Classification': 'classification', - 'Range': 'range', - 'Detail': 'detail', - 'Remarks': 'remarks', - 'ID': 'id', - 'Criteria ID': 'criteriaId', - 'Order Index': 'orderIndex', - 'Equipment-Shipbuilding Score': 'scoreEquipShip', - 'Equipment-Marine Engineering Score': 'scoreEquipMarine', - 'Bulk-Shipbuilding Score': 'scoreBulkShip', - 'Bulk-Marine Engineering Score': 'scoreBulkMarine', - - // 한글 헤더 - '평가부문': 'category', - '점수구분': 'category2', - '항목': 'item', - '구분': 'classification', - '범위': 'range', - '평가내용': 'detail', - '비고': 'remarks', - '기준 ID': 'criteriaId', - '정렬 순서': 'orderIndex', - '장비-조선 점수': 'scoreEquipShip', - '장비-해양 점수': 'scoreEquipMarine', - '벌크-조선 점수': 'scoreBulkShip', - '벌크-해양 점수': 'scoreBulkMarine', -}; - -// ---------------------------------------------------------------------------------------------------- - -/* FUNCTION FOR IMPORTING EXCEL FILES */ -export async function importRegEvalCriteriaExcel(file: File): Promise<ImportResult> { - try { - const buffer = await file.arrayBuffer(); - const workbook = new ExcelJS.Workbook(); - try { - await workbook.xlsx.load(buffer); - } catch { - throw new Error('유효한 Excel 파일이 아닙니다. 파일을 다시 확인해주세요.'); - } - - const worksheet = workbook.worksheets[0]; - if (!worksheet) { - throw new Error('Excel 파일에 워크시트가 없습니다.'); - }; - if (worksheet.rowCount === 0) { - throw new Error('워크시트에 데이터가 없습니다.'); - } - - const headerRow = worksheet.getRow(HEADER_ROW_INDEX); - if (!headerRow || !Array.isArray(headerRow.values)) { - throw new Error('Excel 파일의 워크시트에서 유효한 헤더 행을 찾지 못했습니다.'); - } - - // 헤더 매핑 생성 - const columnToFieldMap = new Map<number, string>(); - headerRow.eachCell((cell, colIndex) => { - if (typeof cell.value === 'string') { - const headerValue = cell.value.trim(); - const fieldName = HEADER_MAPPING[headerValue]; - if (fieldName) { - columnToFieldMap.set(colIndex, fieldName); - } - } - }); - - // 필수 헤더 확인 - const requiredFields = ['category', 'category2', 'item', 'classification', 'detail']; - const foundFields = new Set(columnToFieldMap.values()); - const missingFields = requiredFields.filter(field => !foundFields.has(field)); - - if (missingFields.length > 0) { - throw new Error(`필수 헤더가 누락되었습니다: ${missingFields.join(', ')}`); - } - const errorRows: { rowIndex: number; message: string }[] = []; - const rowDataList: ExcelRowData[] = []; - const criteriaMap = new Map<string, { - criteria: NewRegEvalCriteria, - criteriaDetails: (NewRegEvalCriteriaDetails & { rowIndex: number, toDelete: boolean })[], - }>(); - - for (let r = DATA_START_ROW_INDEX; r <= worksheet.rowCount; r += 1) { - const row = worksheet.getRow(r); - if (!row) { - continue; - } - - const lastCellValue = row.getCell(row.cellCount).value; - const isDelete = typeof lastCellValue === 'string' && lastCellValue.toLowerCase() === 'd'; - - const rowFields = {} as Record<string, any>; - columnToFieldMap.forEach((fieldName, colIdx) => { - let cellValue = row.getCell(colIdx).value; - - // 한글 라벨을 영문 값으로 변환 - if (fieldName === 'category' && typeof cellValue === 'string') { - const found = REG_EVAL_CRITERIA_CATEGORY.find(item => item.label === cellValue?.toString().trim()); - cellValue = found ? found.value : cellValue; - } else if (fieldName === 'category2' && typeof cellValue === 'string') { - const found = REG_EVAL_CRITERIA_CATEGORY2.find(item => item.label === cellValue?.toString().trim()); - cellValue = found ? found.value : cellValue; - } else if (fieldName === 'item' && typeof cellValue === 'string') { - const found = REG_EVAL_CRITERIA_ITEM.find(item => item.label === cellValue?.toString().trim()); - cellValue = found ? found.value : cellValue; - } - - rowFields[fieldName] = cellValue ?? null; - }); - - const requiredFields = ['category', 'category2', 'item', 'classification', 'detail']; - for (const field of requiredFields) { - if (!rowFields[field]) { - errorRows.push({ rowIndex: r, message: `필수 필드 누락: ${field}` }); - } - } - - if (!REG_EVAL_CRITERIA_CATEGORY_ENUM.includes(rowFields.category)) { - errorRows.push({ rowIndex: r, message: `유효하지 않은 Category 값: ${rowFields.category}` }); - } - - if (!REG_EVAL_CRITERIA_CATEGORY2_ENUM.includes(rowFields.category2)) { - errorRows.push({ rowIndex: r, message: `유효하지 않은 Score Category 값: ${rowFields.category2}` }); - } - - if (!REG_EVAL_CRITERIA_ITEM_ENUM.includes(rowFields.item)) { - errorRows.push({ rowIndex: r, message: `유효하지 않은 Item 값: ${rowFields.item}` }); - } - - const criteriaKey = [ - rowFields.criteriaId ?? '', - rowFields.category, - rowFields.category2, - rowFields.item, - rowFields.classification, - rowFields.range ?? '', - ].join('|'); - - const criteriaDetail: NewRegEvalCriteriaDetails = { - id: rowFields.id, - criteriaId: rowFields.criteriaId, - detail: rowFields.detail, - orderIndex: rowFields.orderIndex, - scoreEquipShip: rowFields.scoreEquipShip, - scoreEquipMarine: rowFields.scoreEquipMarine, - scoreBulkShip: rowFields.scoreBulkShip, - scoreBulkMarine: rowFields.scoreBulkMarine, - }; - - if (!criteriaMap.has(criteriaKey)) { - const criteria: NewRegEvalCriteria = { - id: rowFields.criteriaId, - category: rowFields.category, - category2: rowFields.category2, - item: rowFields.item, - classification: rowFields.classification, - range: rowFields.range, - remarks: rowFields.remarks, - }; - - criteriaMap.set(criteriaKey, { - criteria, - criteriaDetails: [{ - ...criteriaDetail, - rowIndex: r, - toDelete: isDelete, - }], - }); - } else { - const existing = criteriaMap.get(criteriaKey)!; - existing.criteriaDetails.push({ - ...criteriaDetail, - rowIndex: r, - toDelete: isDelete, - }); - } - } - - criteriaMap.forEach(({ criteria, criteriaDetails }) => { - rowDataList.push({ - criteriaData: criteria, - detailList: criteriaDetails, - }); - }); - - if (errorRows.length > 0) { - const workbook = new ExcelJS.Workbook(); - const sheet = workbook.addWorksheet('Error List'); - sheet.columns = [ - { header: 'Row Index', key: 'rowIndex', width: 10 }, - { header: 'Error Message', key: 'message', width: 50 }, - ]; - errorRows.forEach((errorRow) => { - sheet.addRow({ - rowIndex: errorRow.rowIndex, - message: errorRow.message, - }); - }); - const buffer = await workbook.xlsx.writeBuffer(); - const errorFile = new File( - [buffer], - 'error_rows.xlsx', - { - type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet', - lastModified: Date.now(), - } - ); - - return { - errorFile, - errorMessage: '입력된 데이터 중에서 잘못된 데이터가 있어 오류 파일을 생성했습니다.', - }; - } - - const existingData = await db.transaction(async (tx) => { - return await selectRegEvalCriteria(tx, { limit: Number.MAX_SAFE_INTEGER }); - }); - const existingIds = existingData.map((row) => row.criteriaId!) - const existingIdSet = new Set<number>(existingIds); - - const createList: { - criteriaData: NewRegEvalCriteria, - detailList: Omit<NewRegEvalCriteriaDetails, 'criteriaId'>[], - }[] = []; - const updateList: { - id: number, - criteriaData: Partial<RegEvalCriteria>, - detailList: Partial<RegEvalCriteriaDetails>[], - }[] = []; - const deleteIdList: number[] = []; - - for (const { criteriaData, detailList } of rowDataList) { - const { id: criteriaId } = criteriaData; - const allMarkedForDelete = detailList.every(d => d.toDelete); - if (allMarkedForDelete) { - if (criteriaId && existingIdSet.has(criteriaId)) { - deleteIdList.push(criteriaId); - } - continue; - } - - if (!criteriaId) { - // eslint-disable-next-line @typescript-eslint/no-unused-vars - const { id, ...newCriteriaData } = criteriaData; - const newDetailList = detailList.map(d => { - if (d.id != null) { - throw new Error(`새로운 기준 항목에 ID가 존재합니다: ${d.rowIndex}행`); - } - // eslint-disable-next-line @typescript-eslint/no-unused-vars - const { rowIndex, toDelete, id, criteriaId, ...rest } = d; - return rest; - }); - - createList.push({ - criteriaData: newCriteriaData, - detailList: newDetailList, - }); - } else if (existingIdSet.has(criteriaId)) { - const matchedExistingDetails = existingData.filter(d => d.criteriaId === criteriaId); - const hasDeletedDetail = detailList.some(d => d.toDelete === true); - const hasNewDetail = detailList.some(d => d.id == null); - const matchedExistingCriteria = matchedExistingDetails[0]; - const criteriaChanged = ( - matchedExistingCriteria.category !== criteriaData.category || - matchedExistingCriteria.category2 !== criteriaData.category2 || - matchedExistingCriteria.item !== criteriaData.item || - matchedExistingCriteria.classification !== criteriaData.classification || - matchedExistingCriteria.range !== criteriaData.range || - matchedExistingCriteria.remarks !== criteriaData.remarks - ); - const detailChanged = detailList.some(d => { - if (!d.id) { - return false; - } - const matched = matchedExistingDetails.find(e => e.id === d.id); - if (!matched) { - throw Error(`존재하지 않는 잘못된 ID(${d.id})가 있습니다.`); - } - return ( - matched.detail !== d.detail || - matched.orderIndex !== d.orderIndex || - matched.scoreEquipShip !== d.scoreEquipShip || - matched.scoreEquipMarine !== d.scoreEquipMarine || - matched.scoreBulkShip !== d.scoreBulkShip || - matched.scoreBulkMarine !== d.scoreBulkMarine - ); - }); - - if (hasDeletedDetail || hasNewDetail || criteriaChanged || detailChanged) { - const updatedDetails = detailList - .filter(d => !d.toDelete) - .map(d => { - // eslint-disable-next-line @typescript-eslint/no-unused-vars - const { rowIndex, toDelete, ...rest } = d; - const cleaned = Object.fromEntries( - Object.entries(rest).map(([key, value]) => [ - key, - value === '' ? null : value, - ]) - ); - return cleaned; - }); - - updateList.push({ - id: criteriaId, - criteriaData, - detailList: updatedDetails, - }); - } - } else { - throw Error(`존재하지 않는 잘못된 Criteria ID(${criteriaId})가 있습니다.`); - } - } - - if (createList.length > 0) { - for (const { criteriaData, detailList } of createList) { - await createRegEvalCriteriaWithDetails(criteriaData, detailList); - } - } - if (updateList.length > 0) { - for (const { id, criteriaData, detailList } of updateList) { - await modifyRegEvalCriteriaWithDetails(id, criteriaData, detailList); - } - } - if (deleteIdList.length > 0) { - for (const id of deleteIdList) { - await removeRegEvalCriteria(id); - } - } - - const msg: string[] = []; - if (createList.length > 0) { - msg.push(`${createList.length}건 생성`); - } - if (updateList.length > 0) { - msg.push(`${updateList.length}건 수정`); - } - if (deleteIdList.length > 0) { - msg.push(`${deleteIdList.length}건 삭제`); - } - const successMessage = msg.length > 0 - ? '기준 항목이 정상적으로 ' + msg.join(', ') + '되었습니다.' - : '변경사항이 존재하지 않습니다.'; - - return { - errorFile: null, - errorMessage: null, - successMessage, - }; - } catch (error) { - let message = 'Excel 파일을 읽는 중 오류가 발생했습니다.'; - if (error instanceof Error) { - message = error.message; - } - - return { - errorFile: null, - errorMessage: message, - }; - } -}
\ No newline at end of file |