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 --- .../excel/reg-eval-criteria-excel-export.ts | 303 ------ .../excel/reg-eval-criteria-excel-import.ts | 417 ------- lib/evaluation-criteria/service.ts | 684 +++++++++++- .../table/reg-eval-criteria-columns.tsx | 5 + .../table/reg-eval-criteria-create-dialog.tsx | 680 +++++++----- .../table/reg-eval-criteria-details-sheet.tsx | 188 ++-- .../table/reg-eval-criteria-form-sheet.tsx | 1138 ++++++++++---------- .../reg-eval-criteria-table-toolbar-actions.tsx | 37 +- .../table/reg-eval-criteria-update-sheet.tsx | 233 ++-- 9 files changed, 1943 insertions(+), 1742 deletions(-) delete mode 100644 lib/evaluation-criteria/excel/reg-eval-criteria-excel-export.ts delete mode 100644 lib/evaluation-criteria/excel/reg-eval-criteria-excel-import.ts (limited to 'lib') diff --git a/lib/evaluation-criteria/excel/reg-eval-criteria-excel-export.ts b/lib/evaluation-criteria/excel/reg-eval-criteria-excel-export.ts deleted file mode 100644 index b4254b80..00000000 --- a/lib/evaluation-criteria/excel/reg-eval-criteria-excel-export.ts +++ /dev/null @@ -1,303 +0,0 @@ -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( - table: Table, - { - filename = "Regular_Evaluation_Criteria", - excludeColumns = ["select", "actions"], - sheetName = "평가 기준", - }: { - filename?: string; - excludeColumns?: string[]; - sheetName?: string; - } = {} -): Promise { - // 워크북 생성 - 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 = {}; - 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 { - // 워크북 생성 - 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 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 = { - // 영문 헤더 - '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 { - 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(); - 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(); - - 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; - 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(existingIds); - - const createList: { - criteriaData: NewRegEvalCriteria, - detailList: Omit[], - }[] = []; - const updateList: { - id: number, - criteriaData: Partial, - detailList: Partial[], - }[] = []; - 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 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 diff --git a/lib/evaluation-criteria/table/reg-eval-criteria-columns.tsx b/lib/evaluation-criteria/table/reg-eval-criteria-columns.tsx index 88c8107b..d48e097b 100644 --- a/lib/evaluation-criteria/table/reg-eval-criteria-columns.tsx +++ b/lib/evaluation-criteria/table/reg-eval-criteria-columns.tsx @@ -85,6 +85,7 @@ function getColumns({ setRowAction }: GetColumnsProps): ColumnDef