1 files changed, 660 insertions, 24 deletions
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<number> {
+  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<NewRegEvalCriteriaDetails, 'criteriaId'>[],
 ) {
   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<RegEvalCriteria>,
   detailList: Partial<RegEvalCriteriaDetails>[],
 ) {
   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<RegEvalCriteria>,
+) {
+  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<ArrayBuffer> {
+  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<string, unknown> = {};
+      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<ArrayBuffer> {
+  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<string, any[]> = 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