(최겸) 협력업체 평가기준표 excel 기능 수정

1 files changed, 0 insertions, 369 deletions

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