(고건) 협력업체 평가 대상 관리 페이지 내 엑셀 템플릿 다운로드 및 엑셀 데이터 업로드 기능 추가

1 files changed, 506 insertions, 33 deletions

diff --git a/lib/evaluation-target-list/service.ts b/lib/evaluation-target-list/service.ts
index 6e2dbfe6..572a9006 100644
--- a/lib/evaluation-target-list/service.ts
+++ b/lib/evaluation-target-list/service.ts
@@ -1,41 +1,53 @@
-'use server'
+'use server';
 
-import { and, or, desc, asc, ilike, eq, isNull, sql, count, inArray, gte, lte, ne } from "drizzle-orm";
-
-import { getFiltersStateParser, getSortingStateParser } from "@/lib/parsers";
-import { filterColumns } from "@/lib/filter-columns";
-import db from "@/db/db";
+/* IMPORT */
+import {
+  and,
+  asc,
+  count,
+  desc,
+  eq,
+  gte,
+  ilike,
+  inArray,
+  isNotNull,
+  lte,
+  or,
+  sql,
+  type SQL,
+} from 'drizzle-orm';
+import { authOptions } from '@/app/api/auth/[...nextauth]/route';
 import {
+  contracts,
+  esgEvaluationItems,
+  EVALUATION_DEPARTMENT_CODES,
   evaluationTargets,
   evaluationTargetReviewers,
   evaluationTargetReviews,
-  users,
-  vendors,
-  type EvaluationTargetStatus,
-  type Division,
-  type MaterialType,
-  type DomesticForeign,
-  EVALUATION_DEPARTMENT_CODES,
-  EvaluationTargetWithDepartments,
   evaluationTargetsWithDepartments,
-  periodicEvaluations,
-  reviewerEvaluations,
-  evaluationSubmissions,
   generalEvaluations,
-  esgEvaluationItems,
-  contracts,
-  projects
-} from "@/db/schema";
-
-
-import { GetEvaluationTargetsSchema } from "./validation";
-import { PgTransaction } from "drizzle-orm/pg-core";
-import { getServerSession } from "next-auth/next"
-import { authOptions } from "@/app/api/auth/[...nextauth]/route"
-import { sendEmail } from "../mail/sendEmail";
-import type { SQL } from "drizzle-orm"
-import { DEPARTMENT_CODE_LABELS } from "@/types/evaluation";
-import { revalidatePath,unstable_noStore } from "next/cache";
+  periodicEvaluations,
+  projects,
+  type Division,
+  type EvaluationTargetWithDepartments,
+  type MaterialType,
+  users,
+  vendors,
+} from '@/db/schema';
+import db from '@/db/db';
+import { decryptWithServerAction } from '@/components/drm/drmUtils';
+import { DEPARTMENT_CODE_LABELS } from '@/types/evaluation';
+import ExcelJS from 'exceljs';
+import { filterColumns } from '@/lib/filter-columns';
+import { getServerSession } from 'next-auth/next';
+import { PgTransaction } from 'drizzle-orm/pg-core';
+import { revalidatePath, unstable_noStore } from 'next/cache';
+import { selectUsers } from '../admin-users/repository';
+import { selectVendors } from '../vendors/repository';
+import { sendEmail } from '../mail/sendEmail';
+import { type GetEvaluationTargetsSchema } from './validation';
+
+// ----------------------------------------------------------------------------------------------------
 
 export async function selectEvaluationTargetsFromView(
   tx: PgTransaction<any, any, any>,
@@ -674,10 +686,8 @@ export async function getAvailableReviewers(departmentCode?: string) {
         // departmentName: "API로 추후", // ✅ 부서명도 반환
       })
       .from(users)
-      .where(ne(users.domain, "partners"))
       .orderBy(users.name)
       // .limit(100);
-      //partners가 아닌 domain에 따라서 필터링
 
     return reviewers;
   } catch (error) {
@@ -1487,3 +1497,466 @@ export async function deleteEvaluationTargets(targetIds: number[]) {
   }
 }
 
+/* HELPER FUNCTION FOR GETTING CURRENT USER ID */
+export 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 in Getting Current Session User ID:', error);
+    return 3; // 기본값 3
+  }
+}
+
+/* FUNCTION FOR GENERATING EXCEL TEMPLATE */
+export async function generateEvalTargetTemplate(): Promise<ArrayBuffer> {
+  try {
+    // [01] Create a new workbook and worksheet
+    const workbook = new ExcelJS.Workbook();
+    const worksheet = workbook.addWorksheet('협력업체 평가 대상 관리 템플릿');
+    workbook.creator = 'eVCP System';
+    workbook.created = new Date();
+
+    // [02] Generate Header Rows
+    worksheet.mergeCells(1, 1, 2, 6);
+    worksheet.getCell(1, 1).value = '기본 정보';
+    worksheet.mergeCells(1, 7, 2, 9);
+    worksheet.getCell(1, 7).value = 'L/D 클레임 정보';
+    worksheet.mergeCells(1, 10, 1, 19);
+    worksheet.getCell(1, 10).value = '담당자 지정';
+    worksheet.mergeCells(2, 10, 2, 11);
+    worksheet.getCell(2, 10).value = '발주 평가 담당';
+    worksheet.mergeCells(2, 12, 2, 13);
+    worksheet.getCell(2, 12).value = '조달 평가 담당';
+    worksheet.mergeCells(2, 14, 2, 15);
+    worksheet.getCell(2, 14).value = '품질 평가 담당';
+    worksheet.mergeCells(2, 16, 2, 17);
+    worksheet.getCell(2, 16).value = '설계 평가 담당';
+    worksheet.mergeCells(2, 18, 2, 19);
+    worksheet.getCell(2, 18).value = 'CS 평가 담당';
+    const templateHeaders = [
+      { key: 'evaluationYear', width: 15 },
+      { key: 'division', width: 15 },
+      { key: 'vendorCode', width: 20 },
+      { key: 'vendorName', width: 20 },
+      { key: 'materialType', width: 20 },
+      { key: 'adminComment', width: 30 },
+      { key: 'ldClaimCount', width: 15 },
+      { key: 'ldClaimAmount', width: 20 },
+      { key: 'ldClaimCurrency', width: 15 },
+      { key: 'orderEvalName', width: 20 },
+      { key: 'orderEvalEmail', width: 30 },
+      { key: 'procurementEvalName', width: 20 },
+      { key: 'procurementEvalEmail', width: 30 },
+      { key: 'qualityEvalName', width: 20 },
+      { key: 'qualityEvalEmail', width: 30 },
+      { key: 'designEvalName', width: 20 },
+      { key: 'designEvalEmail', width: 30 },
+      { key: 'csEvalName', width: 20 },
+      { key: 'csEvalEmail', width: 30 },
+    ];
+    worksheet.columns = templateHeaders;
+    const headers = [
+      '평가년도',
+      '구분',
+      '협력업체 코드',
+      '협력업체명',
+      '자재구분',
+      '관리자 의견',
+      '클레임 건수',
+      '클레임 금액',
+      '통화단위',
+      '이름',
+      '이메일',
+      '이름',
+      '이메일',
+      '이름',
+      '이메일',
+      '이름',
+      '이메일',
+      '이름',
+      '이메일',
+    ];
+    headers.forEach((header, idx) => {
+      worksheet.getRow(3).getCell(idx + 1).value = header;
+    });
+
+    // [03] Apply Header Styles
+    function applyHeaderStyle(row: ExcelJS.Row) {
+      row.eachCell(cell => {
+        cell.fill = {
+          type: 'pattern',
+          pattern: 'solid',
+          fgColor: { argb: 'FFD9D9D9' },
+        };
+        cell.font = { bold: true };
+        cell.alignment = { horizontal: 'center', vertical: 'middle' };
+        cell.border = {
+          top: { style: 'thin' },
+          left: { style: 'thin' },
+          bottom: { style: 'thin' },
+          right: { style: 'thin' },
+        };
+      });
+    }
+    applyHeaderStyle(worksheet.getRow(1));
+    applyHeaderStyle(worksheet.getRow(2));
+    applyHeaderStyle(worksheet.getRow(3));
+
+    // [04] Generate Data Validation Sheet
+    const validationSheet = workbook.addWorksheet('ValidationData');
+    validationSheet.state = 'hidden';
+
+    const DIVISION_LIST = ['해양', '조선'];
+    const MATERIAL_TYPE_LIST = ['장비재', '벌크'];
+    const CURRENCY_LIST = ['KRW (원)', 'USD (달러)', 'EUR (유로)', 'JPY (엔)'];
+
+    validationSheet.getColumn(1).values = ['구분', ...DIVISION_LIST];
+    validationSheet.getColumn(2).values = ['자재구분', ...MATERIAL_TYPE_LIST];
+    validationSheet.getColumn(3).values = ['통화단위', ...CURRENCY_LIST];
+
+    const divisionCol = templateHeaders.findIndex(h => h.key === 'division') + 1;
+    const materialCol = templateHeaders.findIndex(h => h.key === 'materialType') + 1;
+    const currencyCol = templateHeaders.findIndex(h => h.key === 'ldClaimCurrency') + 1;
+
+    if (divisionCol > 0) {
+      (worksheet as any).dataValidations.add(`${worksheet.getColumn(divisionCol).letter}4:${worksheet.getColumn(divisionCol).letter}1000`, {
+        type: 'list',
+        allowBlank: false,
+        formulae: [`ValidationData!$A$2:$A$${DIVISION_LIST.length + 1}`],
+      });
+    }
+
+    if (materialCol > 0) {
+      (worksheet as any).dataValidations.add(`${worksheet.getColumn(materialCol).letter}4:${worksheet.getColumn(materialCol).letter}1000`, {
+        type: 'list',
+        allowBlank: false,
+        formulae: [`ValidationData!$B$2:$B$${MATERIAL_TYPE_LIST.length + 1}`],
+      });
+    }
+
+    if (currencyCol > 0) {
+      (worksheet as any).dataValidations.add(`${worksheet.getColumn(currencyCol).letter}4:${worksheet.getColumn(currencyCol).letter}1000`, {
+        type: 'list',
+        allowBlank: false,
+        formulae: [`ValidationData!$C$2:$C$${CURRENCY_LIST.length + 1}`],
+      });
+    }
+
+    // [05] Add Sample Data Row
+    worksheet.addRow({
+      evaluationYear: '2025',
+      division: '해양',
+      vendorCode: 'A00000000',
+      vendorName: '(주)협력업체',
+      materialType: '장비재',
+      ldClaimCount: '0',
+      ldClaimAmount: '0',
+      ldClaimCurrency: 'KRW (원)',
+      orderEvalName: '홍길동',
+      orderEvalEmail: 'hong@example.com',
+    });
+
+    // [06] Apply Border to Data Cells
+    for (let rowIdx = 4; rowIdx <= 10; rowIdx++) {
+      const row = worksheet.getRow(rowIdx);
+      for (let colIdx = 1; colIdx <= templateHeaders.length; colIdx++) {
+        const cell = row.getCell(colIdx);
+        if (!cell.value) {
+          cell.value = '';
+        }
+        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');
+  }
+}
+
+/* FUNCTION FOR IMPORTING EXCEL FILE */
+export async function importEvalTargetExcel(file: File): Promise<{
+  errorFile?: Blob;
+  errorMessage?: string;
+  successMessage?: string;
+}> {
+  const DIVISION_MAP: Record<string, string> = {
+    '해양': 'PLANT',
+    '조선': 'SHIP',
+  };
+  const MATERIAL_TYPE_MAP: Record<string, string> = {
+    '장비재': 'EQUIPMENT',
+    '벌크': 'BULK',
+  };
+  const CURRENCY_MAP: Record<string, string> = {
+    'KRW (원)': 'KRW',
+    'USD (달러)': 'USD',
+    'EUR (유로)': 'EUR',
+    'JPY (엔)': 'JPY',
+  };
+
+  function getCellText(cell: ExcelJS.Cell): string {
+    const value = cell.value;
+    if (!value) {
+      return '';
+    }
+
+    if (typeof value === 'string' || typeof value === 'number') {
+      return value.toString().trim();
+    }
+
+    if (typeof value === 'object' && 'text' in value) {
+      return value.text?.toString().trim() || '';
+    }
+
+    return '';
+  }
+
+  try {
+    const arrayBuffer = await decryptWithServerAction(file);
+    const workbook = new ExcelJS.Workbook();
+    await workbook.xlsx.load(arrayBuffer);
+    
+    const worksheet = workbook.getWorksheet(1);
+    if (!worksheet) {
+      return { errorMessage: '워크시트를 찾을 수 없습니다.' };
+    }
+    const errors: string[] = [];
+    const importDataList: {
+      evaluationYear: number;
+      division: string;
+      vendorId: number;
+      vendorCode: string;
+      vendorName: string;
+      materialType: string;
+      adminComment: string;
+      ldClaimCount: number;
+      ldClaimAmount: number;
+      ldClaimCurrency: string;
+      reviewers: { departmentCode: string; reviewerUserId: number; }[];
+    }[] = [];
+
+    const rows = worksheet.getRows(4, worksheet.rowCount - 3);
+
+    if (!rows) {
+      return { errorMessage: '새로 추가할 평가 대상 데이터가 존재하지 않습니다.' };
+    }
+
+    const duplicateCheckSet = new Set<string>();
+
+    for (const [index, row] of rows.entries()) {
+      const rowIndex = index + 4;
+      try {
+        const rawDivision = row.getCell(2).value?.toString().trim() || '';
+        const rawMaterialType = row.getCell(5).value?.toString().trim() || '';
+        const rawCurrency = row.getCell(9).value?.toString().trim() || 'KRW (원)';
+
+        const rowData = {
+          evaluationYear: Number(row.getCell(1).value) || 0,
+          division: DIVISION_MAP[rawDivision] || '',
+          vendorId: 0,
+          vendorCode: row.getCell(3).value?.toString().trim() || '',
+          vendorName: row.getCell(4).value?.toString().trim() || '',
+          materialType: MATERIAL_TYPE_MAP[rawMaterialType] || '',
+          adminComment: row.getCell(6).value?.toString().trim() || '',
+          ldClaimCount: Number(row.getCell(7).value) || 0,
+          ldClaimAmount: Number(row.getCell(8).value) || 0,
+          ldClaimCurrency: CURRENCY_MAP[rawCurrency] || 'KRW',
+          reviewers: [] as { departmentCode: string; reviewerUserId: number; }[],
+        };
+
+        if (!rowData.evaluationYear && !rowData.division && !rowData.vendorCode && !rowData.materialType) {
+          continue;
+        }
+
+        if (!rowData.evaluationYear || !rowData.division || !rowData.vendorCode || !rowData.materialType) {
+          errors.push(`행 ${rowIndex}: 필수 필드(평가년도, 구분, 협력업체 코드, 자재구분)가 누락되었습니다.`);
+          continue;
+        }
+
+        if (!Object.values(DIVISION_MAP).includes(rowData.division)) {
+          errors.push(`행 ${rowIndex}: 구분 값(${rawDivision})이 잘못되었습니다.`);
+          continue;
+        }
+
+        if (!Object.values(MATERIAL_TYPE_MAP).includes(rowData.materialType)) {
+          errors.push(`행 ${rowIndex}: 자재구분 값(${rawMaterialType})이 잘못되었습니다.`);
+          continue;
+        }
+
+        if (!Object.values(CURRENCY_MAP).includes(rowData.ldClaimCurrency)) {
+          errors.push(`행 ${rowIndex}: 통화 값(${rawCurrency})이 잘못되었습니다.`);
+          continue;
+        }
+
+        const duplicateKey = `${rowData.evaluationYear}_${rowData.division}_${rowData.vendorCode}`;
+        if (duplicateCheckSet.has(duplicateKey)) {
+          errors.push(`행 ${rowIndex}: 시트 내에 중복된 평가 대상 항목이 있습니다. 평가 대상은 평가년도, 구분, 협력업체가 모두 같을 수 없습니다.`);
+          continue;
+        }
+        duplicateCheckSet.add(duplicateKey);
+
+        const existingTargetList = await db.transaction(async (tx) => {
+          const selectRes = await tx
+            .select({ id: evaluationTargets.id })
+            .from(evaluationTargets)
+            .where(
+              and(
+                eq(evaluationTargets.evaluationYear, rowData.evaluationYear),
+                eq(evaluationTargets.vendorCode, rowData.vendorCode),
+                eq(evaluationTargets.division, rowData.division as 'PLANT' | 'SHIP'),
+              )
+            )
+            .limit(1);
+          return selectRes;
+        });
+
+        if (existingTargetList.length > 0) {
+          errors.push(`행 ${rowIndex}: 등록된 평가 대상 항목이 이미 존재합니다. 평가 대상은 평가년도, 구분, 협력업체가 모두 같을 수 없습니다.`);
+          continue;
+        }
+
+        const { vendorList } = await db.transaction(async (tx) => {
+          const selectRes = await selectVendors(tx, {
+            where: eq(vendors.vendorCode, rowData.vendorCode),
+          });
+
+          return { vendorList: selectRes };
+        });
+
+        if (vendorList.length === 0) {
+          errors.push(`행 ${rowIndex}: 협력업체 코드(${rowData.vendorCode})가 존재하지 않습니다. 다시 한 번 확인해주십시오.`);
+          continue;
+        }
+        rowData.vendorId = vendorList[0].id;
+
+        const DEPARTMENT_MAP = [
+          { code: 'ORDER_EVAL', label: '발주 평가 담당', nameCol: 10, emailCol: 11 },
+          { code: 'PROCUREMENT_EVAL', label: '조달 평가 담당', nameCol: 12, emailCol: 13 },
+          { code: 'QUALITY_EVAL', label: '품질 평가 담당', nameCol: 14, emailCol: 15 },
+          { code: 'DESIGN_EVAL', label: '설계 평가 담당', nameCol: 16, emailCol: 17 },
+          { code: 'CS_EVAL', label: 'CS 평가 담당', nameCol: 18, emailCol: 19 },
+        ];
+
+        for (const department of DEPARTMENT_MAP) {
+          const managerName = getCellText(row.getCell(department.nameCol));
+          const managerEmail = getCellText(row.getCell(department.emailCol));
+          if (managerEmail) {
+
+            if (rowData.materialType === 'BULK' && ['DESIGN_EVAL', 'CS_EVAL'].includes(department.code)) {
+              errors.push(`행 ${rowIndex}: 자재구분이 '벌크'인 경우, ${department.label}자를 지정할 수 없습니다.`);
+              continue;
+            }
+            
+            const { userList } = await db.transaction(async (tx) => {
+              const selectRes = await selectUsers(tx, {
+                where: and(eq(users.email, managerEmail), eq(users.isActive, true), isNotNull(users.userCode)),
+                orderBy: [asc(users.name)],
+              });
+
+              return { userList: selectRes };
+            });
+
+            if (userList.length === 0) {
+              errors.push(`행 ${rowIndex}: 입력한 ${department.label}(${managerName}/${managerEmail})이 존재하지 않습니다. 다시 한 번 확인해주십시오.`);
+              continue;
+            }
+            rowData.reviewers.push({
+              departmentCode: department.code,
+              reviewerUserId: userList[0].id,
+            });
+          }
+        }
+
+        if (rowData.reviewers.length === 0) {
+          errors.push(`행 ${rowIndex}: 최소 한 명 이상의 담당자를 지정해주십시오.`);
+          continue;
+        }
+
+        importDataList.push(rowData);
+
+      } catch (error) {
+        errors.push(`행 ${rowIndex}: 데이터 처리 중 오류가 발생했습니다 - ${error}`);
+      }
+    }
+
+    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}개의 오류가 발견되었습니다. 오류 파일을 확인하십시오.` 
+      };
+    }
+
+    if (importDataList.length === 0) {
+      return { errorMessage: '새로 추가할 평가 대상 데이터가 존재하지 않습니다. 파일을 다시 한 번 확인해주십시오.' };
+    }
+
+    const currentUserId = await getCurrentUserId();
+    
+    for (const importData of importDataList) {  
+      const {
+        evaluationYear,
+        division,
+        vendorId,
+        materialType,
+        adminComment,
+        ldClaimCount,
+        ldClaimAmount,
+        ldClaimCurrency,
+        reviewers,
+      } = importData;
+      await createEvaluationTarget(
+        {
+          evaluationYear,
+          division: division as 'PLANT' | 'SHIP',
+          vendorId,
+          materialType: materialType as 'EQUIPMENT' | 'BULK',
+          adminComment,
+          ldClaimCount,
+          ldClaimAmount,
+          ldClaimCurrency: ldClaimCurrency as 'KRW' | 'USD' | 'EUR' | 'JPY',
+          reviewers: reviewers as {
+            departmentCode: 'ORDER_EVAL' | 'PROCUREMENT_EVAL' | 'QUALITY_EVAL' | 'DESIGN_EVAL' | 'CS_EVAL';
+            reviewerUserId: number;
+          }[],
+        },
+        currentUserId,
+      );
+    }
+
+    return { successMessage: `Excel 파일이 성공적으로 업로드되었습니다. ${importDataList.length}개의 평가 대상이 추가되었습니다.` };
+  } catch (error) {
+    console.error('Error in Importing Evaluation Targets from Excel:', error);
+    return { errorMessage: 'Excel 파일 처리 중 오류가 발생했습니다.' };
+  }
+}