diff options
Diffstat (limited to 'lib')
5 files changed, 755 insertions, 392 deletions
diff --git a/lib/evaluation-criteria/excel/reg-eval-criteria-excel-export.ts b/lib/evaluation-criteria/excel/reg-eval-criteria-excel-export.ts new file mode 100644 index 00000000..b4254b80 --- /dev/null +++ b/lib/evaluation-criteria/excel/reg-eval-criteria-excel-export.ts @@ -0,0 +1,303 @@ +import { type Table } from "@tanstack/react-table"; +import ExcelJS from "exceljs"; +import { type RegEvalCriteriaView } from "@/db/schema"; +import { + REG_EVAL_CRITERIA_CATEGORY, + REG_EVAL_CRITERIA_CATEGORY2, + REG_EVAL_CRITERIA_ITEM, +} from "@/db/schema"; + +/** + * 평가 기준 데이터를 Excel로 내보내기 + */ +export async function exportRegEvalCriteriaToExcel<TData extends RegEvalCriteriaView>( + table: Table<TData>, + { + filename = "Regular_Evaluation_Criteria", + excludeColumns = ["select", "actions"], + sheetName = "평가 기준", + }: { + filename?: string; + excludeColumns?: string[]; + sheetName?: string; + } = {} +): Promise<void> { + // 워크북 생성 + const workbook = new ExcelJS.Workbook(); + workbook.creator = "EVCP System"; + workbook.created = new Date(); + + // 메인 워크시트 생성 + const worksheet = workbook.addWorksheet(sheetName); + + // 한글 헤더 매핑 + 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: "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: "remarks", header: "비고", width: 20 }, + { key: "id", header: "ID", width: 10 }, + { key: "criteriaId", header: "기준 ID", width: 12 }, + { key: "orderIndex", header: "정렬 순서", width: 12 }, + ].filter(col => !excludeColumns.includes(col.key)); + + // 컬럼 설정 + 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" }; + + // 데이터 행 추가 + const rowModel = table.getRowModel(); + rowModel.rows.forEach((row) => { + const rowData: Record<string, any> = {}; + columnHeaders.forEach((col) => { + let value = row.original[col.key as keyof RegEvalCriteriaView]; + + // 특정 컬럼들에 대해 한글 라벨로 변환 + 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); + }); + + // 셀 스타일 적용 + worksheet.eachRow((row, rowNumber) => { + row.eachCell((cell) => { + cell.border = { + top: { style: "thin" }, + left: { style: "thin" }, + bottom: { style: "thin" }, + right: { style: "thin" }, + }; + + // 데이터 행 가운데 정렬 + if (rowNumber > 1) { + cell.alignment = { horizontal: "center", vertical: "middle" }; + } + }); + }); + + // 유효성 검사 시트 생성 (풀다운용) + 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); + + validationSheet.getColumn(1).values = ["평가부문", ...categoryOptions]; + validationSheet.getColumn(2).values = ["점수구분", ...category2Options]; + validationSheet.getColumn(3).values = ["항목", ...itemOptions]; + + // 메인 시트에 데이터 유효성 검사 적용 + 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; + + 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}`], + }); + } + + // 파일 다운로드 + const buffer = await workbook.xlsx.writeBuffer(); + const blob = new Blob([buffer], { + type: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", + }); + const url = URL.createObjectURL(blob); + const link = document.createElement("a"); + link.href = url; + link.download = `${filename}.xlsx`; + link.click(); + URL.revokeObjectURL(url); +} + +/** + * 평가 기준 템플릿 다운로드 + */ +export async function exportRegEvalCriteriaTemplate(): Promise<void> { + // 워크북 생성 + 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: "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: "remarks", header: "비고", width: 20 }, + { 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 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); + + validationSheet.getColumn(1).values = ["평가부문", ...categoryOptions]; + validationSheet.getColumn(2).values = ["점수구분", ...category2Options]; + validationSheet.getColumn(3).values = ["항목", ...itemOptions]; + + // 데이터 유효성 검사 적용 + (worksheet as any).dataValidations.add("A2:A1000", { + type: "list", + allowBlank: false, + formulae: [`ValidationData!$A$2:$A$${categoryOptions.length + 1}`], + }); + + (worksheet as any).dataValidations.add("B2:B1000", { + type: "list", + allowBlank: false, + formulae: [`ValidationData!$B$2:$B$${category2Options.length + 1}`], + }); + + (worksheet as any).dataValidations.add("C2:C1000", { + type: "list", + allowBlank: false, + formulae: [`ValidationData!$C$2:$C$${itemOptions.length + 1}`], + }); + + // 테두리 스타일 적용 + headerRow.eachCell((cell) => { + cell.border = { + top: { style: "thin" }, + left: { style: "thin" }, + bottom: { style: "thin" }, + right: { style: "thin" }, + }; + }); + + // 샘플 데이터 추가 (2-3줄) + worksheet.addRow([ + "품질", + "공정", + "품질", + "품질시스템", + "ISO 9001 인증", + "품질경영시스템 운영 현황", + "10", + "10", + "10", + "10", + "품질시스템 운영", + "1" + ]); + + worksheet.addRow([ + "관리자", + "가격", + "납기", + "납기준수", + "최근 3년", + "납기준수율 90% 이상", + "5", + "5", + "5", + "5", + "납기준수 실적", + "2" + ]); + + // 샘플 데이터 행에 테두리 적용 + worksheet.getRow(2).eachCell((cell) => { + cell.border = { + top: { style: "thin" }, + left: { style: "thin" }, + bottom: { style: "thin" }, + right: { style: "thin" }, + }; + cell.alignment = { horizontal: "center", vertical: "middle" }; + }); + + worksheet.getRow(3).eachCell((cell) => { + cell.border = { + top: { style: "thin" }, + left: { style: "thin" }, + bottom: { style: "thin" }, + right: { style: "thin" }, + }; + cell.alignment = { horizontal: "center", vertical: "middle" }; + }); + + // 파일 다운로드 + const buffer = await workbook.xlsx.writeBuffer(); + const blob = new Blob([buffer], { + type: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", + }); + const url = URL.createObjectURL(blob); + const link = document.createElement("a"); + link.href = url; + link.download = "평가기준_템플릿.xlsx"; + link.click(); + URL.revokeObjectURL(url); +}
\ No newline at end of file diff --git a/lib/evaluation-criteria/excel/reg-eval-criteria-excel-import.ts b/lib/evaluation-criteria/excel/reg-eval-criteria-excel-import.ts new file mode 100644 index 00000000..eb3d7020 --- /dev/null +++ b/lib/evaluation-criteria/excel/reg-eval-criteria-excel-import.ts @@ -0,0 +1,417 @@ +/* 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.scoreCategory !== 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 diff --git a/lib/evaluation-criteria/service.ts b/lib/evaluation-criteria/service.ts index 5d5e5b8f..8badc61f 100644 --- a/lib/evaluation-criteria/service.ts +++ b/lib/evaluation-criteria/service.ts @@ -22,11 +22,7 @@ import { updateRegEvalCriteriaDetails,
} from './repository';
import db from '@/db/db';
-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,
@@ -43,37 +39,6 @@ import { type GetRegEvalCriteriaSchema } from './validations'; // ----------------------------------------------------------------------------------------------------
/* 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 = 2;
-const DATA_START_ROW_INDEX = 3;
-const EXCEL_HEADERS = [
- 'Category',
- 'Score Category',
- 'Item',
- 'Classification',
- 'Range',
- 'Detail',
- 'Remarks',
- 'ID',
- 'Criteria ID',
- 'Order Index',
- 'Equipment-Shipbuilding Score',
- 'Equipment-Marine Engineering Score',
- 'Bulk-Shipbuilding Score',
- 'Bulk-Marine Engineering Score',
-];
// ----------------------------------------------------------------------------------------------------
@@ -249,340 +214,7 @@ async function removeRegEvalCriteriaDetails(id: number) { }
}
-// ----------------------------------------------------------------------------------------------------
-
-/* FUNCTION FOR IMPORTING EXCEL FILES */
-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 || headerRow.cellCount < EXCEL_HEADERS.length || !Array.isArray(headerRow.values)) {
- throw new Error('Excel 파일의 워크시트에서 유효한 헤더 행을 찾지 못했습니다.');
- }
- const headerValues = headerRow?.values?.slice(1);
- const isHeaderMatched = EXCEL_HEADERS.every((header, idx) => {
- const actualHeader = (headerValues[idx] ?? '').toString().trim();
- return actualHeader === header;
- });
- if (!isHeaderMatched) {
- throw new Error('Excel 파일의 워크시트에서 유효한 헤더 행을 찾지 못했습니다.');
- }
-
- const columnIndexMap = new Map<string, number>();
- headerRow.eachCell((cell, colIndex) => {
- if (typeof cell.value === 'string') {
- columnIndexMap.set(cell.value.trim(), colIndex);
- }
- });
-
- const columnToFieldMap = new Map<number, keyof RegEvalCriteriaView>();
- regEvalCriteriaColumnsConfig.forEach((cfg) => {
- if (!cfg.excelHeader) {
- return;
- }
- const colIndex = columnIndexMap.get(cfg.excelHeader.trim());
- if (colIndex !== undefined) {
- columnToFieldMap.set(colIndex, cfg.id);
- }
- });
- 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((fieldId, colIdx) => {
- const cellValue = row.getCell(colIdx).value;
- rowFields[fieldId] = 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,
- });
- });
- // console.log('원본 데이터: ');
- // console.dir(rowDataList, { depth: null });
-
- 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);
-
- // console.log('기존 데이터: ');
- // console.dir(existingData, { depth: null });
-
- 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})가 있습니다.`);
- }
- }
- console.log('생성: ');
- console.dir(createList, { depth: null });
- console.log('업뎃: ');
- console.dir(updateList, { depth: null });
- console.log('삭제: ', deleteIdList);
-
- 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,
- };
- }
-}
// ----------------------------------------------------------------------------------------------------
@@ -592,7 +224,6 @@ export { modifyRegEvalCriteriaWithDetails,
getRegEvalCriteria,
getRegEvalCriteriaWithDetails,
- importRegEvalCriteriaExcel,
removeRegEvalCriteria,
removeRegEvalCriteriaDetails,
};
\ No newline at end of file diff --git a/lib/evaluation-criteria/table/reg-eval-criteria-columns.tsx b/lib/evaluation-criteria/table/reg-eval-criteria-columns.tsx index 77e6118d..bdf583bc 100644 --- a/lib/evaluation-criteria/table/reg-eval-criteria-columns.tsx +++ b/lib/evaluation-criteria/table/reg-eval-criteria-columns.tsx @@ -12,7 +12,7 @@ import { DropdownMenuItem, DropdownMenuTrigger, } from '@/components/ui/dropdown-menu'; -import { PenToolIcon, TrashIcon } from 'lucide-react'; +import { Ellipsis } from 'lucide-react'; import { REG_EVAL_CRITERIA_CATEGORY, REG_EVAL_CRITERIA_CATEGORY2, @@ -361,28 +361,25 @@ function getColumns({ setRowAction }: GetColumnsProps): ColumnDef<RegEvalCriteri // [6] ACTIONS COLUMN - DROPDOWN MENU const actionsColumn: ColumnDef<RegEvalCriteriaView> = { id: 'actions', - header: '작업', enableHiding: false, cell: function Cell({ row }) { return ( <DropdownMenu> <DropdownMenuTrigger asChild> <Button variant="ghost" size="icon"> - <PenToolIcon className="h-4 w-4" /> + <Ellipsis className="size-4" aria-hidden="true" /> </Button> </DropdownMenuTrigger> <DropdownMenuContent align="end"> <DropdownMenuItem onClick={() => setRowAction({ row, type: 'update' })} > - <PenToolIcon className="mr-2 h-4 w-4" /> Modify Criteria </DropdownMenuItem> <DropdownMenuItem onClick={() => setRowAction({ row, type: 'delete' })} className="text-destructive" > - <TrashIcon className="mr-2 h-4 w-4" /> Delete Criteria </DropdownMenuItem> </DropdownMenuContent> @@ -396,20 +393,14 @@ function getColumns({ setRowAction }: GetColumnsProps): ColumnDef<RegEvalCriteri selectColumn, ...criteriaColumns, { - id: 'score', - header: '배점', - columns: [ - { - id: 'scoreEquip', - header: '기자재', - columns: scoreEquipColumns, - }, - { - id: 'scoreBulk', - header: '벌크', - columns: scoreBulkColumns, - }, - ], + id: 'scoreEquip', + header: '기자재', + columns: scoreEquipColumns, + }, + { + id: 'scoreBulk', + header: '벌크', + columns: scoreBulkColumns, }, ...hiddenColumns, remarksColumn, diff --git a/lib/evaluation-criteria/table/reg-eval-criteria-table-toolbar-actions.tsx b/lib/evaluation-criteria/table/reg-eval-criteria-table-toolbar-actions.tsx index b14cb22f..f066fa92 100644 --- a/lib/evaluation-criteria/table/reg-eval-criteria-table-toolbar-actions.tsx +++ b/lib/evaluation-criteria/table/reg-eval-criteria-table-toolbar-actions.tsx @@ -14,8 +14,9 @@ import { } from '@/components/ui/alert-dialog'; import { Button } from '@/components/ui/button'; import { Download, Plus, Trash2, Upload } from 'lucide-react'; -import { exportTableToExcel } from '@/lib/export'; -import { importRegEvalCriteriaExcel, removeRegEvalCriteria } from '../service'; +import { exportRegEvalCriteriaToExcel, exportRegEvalCriteriaTemplate } from '../excel/reg-eval-criteria-excel-export'; +import { importRegEvalCriteriaExcel } from '../excel/reg-eval-criteria-excel-import'; +import { removeRegEvalCriteria } from '../service'; import { toast } from 'sonner'; import { type RegEvalCriteriaView } from '@/db/schema'; import { type Table } from '@tanstack/react-table'; @@ -117,9 +118,9 @@ function RegEvalCriteriaTableToolbarActions(props: RegEvalCriteriaTableToolbarAc }; // Excel Export - const handleExport = () => { + const handleExport = async () => { try { - exportTableToExcel(table, { + await exportRegEvalCriteriaToExcel(table, { filename: 'Regular_Evaluation_Criteria', excludeColumns: ['select', 'actions'], }); @@ -130,6 +131,17 @@ function RegEvalCriteriaTableToolbarActions(props: RegEvalCriteriaTableToolbarAc } }; + // Excel Template Download + const handleTemplateDownload = async () => { + try { + await exportRegEvalCriteriaTemplate(); + toast.success('템플릿 파일이 다운로드되었습니다.'); + } catch (error) { + console.error('Error in Template Download: ', error); + toast.error('템플릿 다운로드 중 오류가 발생했습니다.'); + } + }; + return ( <div className="flex items-center gap-2"> <Button @@ -201,6 +213,15 @@ function RegEvalCriteriaTableToolbarActions(props: RegEvalCriteriaTableToolbarAc <Download className="size-4" aria-hidden="true" /> <span className="hidden sm:inline">Export</span> </Button> + <Button + variant="outline" + size="sm" + onClick={handleTemplateDownload} + className="gap-2" + > + <Download className="size-4" aria-hidden="true" /> + <span className="hidden sm:inline">Template</span> + </Button> </div> ); } |