diff options
Diffstat (limited to 'lib/vendor-document-list')
5 files changed, 5 insertions, 1813 deletions
diff --git a/lib/vendor-document-list/plant/document-stage-dialogs.tsx b/lib/vendor-document-list/plant/document-stage-dialogs.tsx index 0c972658..d4e0ff33 100644 --- a/lib/vendor-document-list/plant/document-stage-dialogs.tsx +++ b/lib/vendor-document-list/plant/document-stage-dialogs.tsx @@ -466,7 +466,7 @@ export function AddDocumentDialog({ } else if (config.documentClass) { return ( <div className="p-2 bg-gray-100 dark:bg-gray-800 rounded text-sm"> - {config.documentClass.code} - {config.documentClass.description} + {config.documentClass.value} - {config.documentClass.description} </div> ) } else { @@ -630,7 +630,7 @@ export function AddDocumentDialog({ Options from the selected class will be automatically created as stages. {(() => { const selected = documentClasses.find(cls => String(cls.id) === documentClassId) - return selected ? ` (Selected: ${selected.code})` : '' + return selected ? ` (Selected: ${selected.value})` : '' })()} </p> )} diff --git a/lib/vendor-document-list/plant/document-stages-service.ts b/lib/vendor-document-list/plant/document-stages-service.ts index 1a7b1ab6..7740a385 100644 --- a/lib/vendor-document-list/plant/document-stages-service.ts +++ b/lib/vendor-document-list/plant/document-stages-service.ts @@ -803,7 +803,6 @@ export async function getDocumentClassOptionsByContract(contractId: number) { const classes = await db .select({ id: documentClasses.id, - code: documentClasses.code, value: documentClasses.value, description: documentClasses.description, }) diff --git a/lib/vendor-document-list/plant/excel-import-stage copy 2.tsx b/lib/vendor-document-list/plant/excel-import-stage copy 2.tsx deleted file mode 100644 index 8dc85c51..00000000 --- a/lib/vendor-document-list/plant/excel-import-stage copy 2.tsx +++ /dev/null @@ -1,899 +0,0 @@ -"use client" - -import React from "react" -import { Dialog, DialogContent, DialogDescription, DialogFooter, DialogHeader, DialogTitle } from "@/components/ui/dialog" -import { Button } from "@/components/ui/button" -import { Input } from "@/components/ui/input" -import { Label } from "@/components/ui/label" -import { Progress } from "@/components/ui/progress" -import { Alert, AlertDescription } from "@/components/ui/alert" -import { Upload, FileSpreadsheet, Loader2, Download, CheckCircle, AlertCircle } from "lucide-react" -import { toast } from "sonner" -import { useRouter } from "next/navigation" -import ExcelJS from "exceljs" -import { - getDocumentClassOptionsByContract, - uploadImportData, -} from "./document-stages-service" - -// ============================================================================= -// Type Definitions -// ============================================================================= -interface ExcelImportDialogProps { - open: boolean - onOpenChange: (open: boolean) => void - contractId: number - projectType: "ship" | "plant" -} - -interface ImportResult { - documents: any[] - stages: any[] - errors: string[] - warnings: string[] -} - -interface ParsedDocument { - docNumber: string - title: string - documentClass: string - vendorDocNumber?: string - notes?: string - stages?: { stageName: string; planDate: string }[] -} - -// ============================================================================= -// Main Component -// ============================================================================= -export function ExcelImportDialog({ - open, - onOpenChange, - contractId, - projectType -}: ExcelImportDialogProps) { - const [file, setFile] = React.useState<File | null>(null) - const [isProcessing, setIsProcessing] = React.useState(false) - const [isDownloadingTemplate, setIsDownloadingTemplate] = React.useState(false) - const [importResult, setImportResult] = React.useState<ImportResult | null>(null) - const [processStep, setProcessStep] = React.useState<string>("") - const [progress, setProgress] = React.useState(0) - const router = useRouter() - - const handleFileChange = (e: React.ChangeEvent<HTMLInputElement>) => { - const selectedFile = e.target.files?.[0] - if (selectedFile) { - if (!validateFileExtension(selectedFile)) { - toast.error("Excel 파일(.xlsx, .xls)만 업로드 가능합니다.") - return - } - if (!validateFileSize(selectedFile, 10)) { - toast.error("파일 크기는 10MB 이하여야 합니다.") - return - } - setFile(selectedFile) - setImportResult(null) - } - } - - const handleDownloadTemplate = async () => { - setIsDownloadingTemplate(true) - try { - const workbook = await createImportTemplate(projectType, contractId) - const buffer = await workbook.xlsx.writeBuffer() - const blob = new Blob([buffer], { type: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" }) - const url = window.URL.createObjectURL(blob) - const link = document.createElement("a") - link.href = url - link.download = `문서_임포트_템플릿_${projectType}_${new Date().toISOString().split("T")[0]}.xlsx` - link.click() - window.URL.revokeObjectURL(url) - toast.success("템플릿 파일이 다운로드되었습니다.") - } catch (error) { - toast.error("템플릿 다운로드 중 오류가 발생했습니다: " + (error instanceof Error ? error.message : "알 수 없는 오류")) - } finally { - setIsDownloadingTemplate(false) - } - } - - const handleImport = async () => { - if (!file) { - toast.error("파일을 선택해주세요.") - return - } - setIsProcessing(true) - setProgress(0) - try { - setProcessStep("파일 읽는 중...") - setProgress(20) - const workbook = new ExcelJS.Workbook() - const buffer = await file.arrayBuffer() - await workbook.xlsx.load(buffer) - - setProcessStep("데이터 검증 중...") - setProgress(40) - const worksheet = workbook.getWorksheet("Documents") || workbook.getWorksheet(1) - if (!worksheet) throw new Error("Documents 시트를 찾을 수 없습니다.") - - setProcessStep("문서 및 스테이지 데이터 파싱 중...") - setProgress(60) - const parseResult = await parseDocumentsWithStages(worksheet, projectType, contractId) - - setProcessStep("서버에 업로드 중...") - setProgress(90) - const allStages: any[] = [] - parseResult.validData.forEach((doc) => { - if (doc.stages) { - doc.stages.forEach((stage) => { - allStages.push({ - docNumber: doc.docNumber, - stageName: stage.stageName, - planDate: stage.planDate, - }) - }) - } - }) - - const result = await uploadImportData({ - contractId, - documents: parseResult.validData, - stages: allStages, - projectType, - }) - - if (result.success) { - setImportResult({ - documents: parseResult.validData, - stages: allStages, - errors: parseResult.errors, - warnings: result.warnings || [], - }) - setProgress(100) - toast.success(`${parseResult.validData.length}개 문서가 성공적으로 임포트되었습니다.`) - } else { - throw new Error(result.error || "임포트에 실패했습니다.") - } - } catch (error) { - toast.error(error instanceof Error ? error.message : "임포트 중 오류가 발생했습니다.") - setImportResult({ - documents: [], - stages: [], - errors: [error instanceof Error ? error.message : "알 수 없는 오류"], - warnings: [], - }) - } finally { - setIsProcessing(false) - setProcessStep("") - setProgress(0) - } - } - - const handleClose = () => { - setFile(null) - setImportResult(null) - setProgress(0) - setProcessStep("") - onOpenChange(false) - } - - const handleConfirmImport = () => { - router.refresh() - handleClose() - } - - return ( - <Dialog open={open} onOpenChange={onOpenChange}> - <DialogContent className="sm:max-w-[700px] max-h-[90vh] flex flex-col"> - <DialogHeader className="flex-shrink-0"> - <DialogTitle> - <FileSpreadsheet className="inline w-5 h-5 mr-2" /> - Excel 파일 임포트 - </DialogTitle> - <DialogDescription> - Excel 파일을 사용하여 문서와 스테이지 계획을 일괄 등록합니다. - </DialogDescription> - </DialogHeader> - - <div className="flex-1 overflow-y-auto pr-2"> - <div className="grid gap-4 py-4"> - {/* 템플릿 다운로드 섹션 */} - <div className="border rounded-lg p-4 bg-blue-50/30 dark:bg-blue-950/30"> - <h4 className="font-medium text-blue-800 dark:text-blue-200 mb-2">1. 템플릿 다운로드</h4> - <p className="text-sm text-blue-700 dark:text-blue-300 mb-3"> - 올바른 형식과 스마트 검증이 적용된 템플릿을 다운로드하세요. - </p> - <Button variant="outline" size="sm" onClick={handleDownloadTemplate} disabled={isDownloadingTemplate}> - {isDownloadingTemplate ? <Loader2 className="h-4 w-4 animate-spin mr-2" /> : <Download className="h-4 w-4 mr-2" />} - 템플릿 다운로드 - </Button> - </div> - - {/* 파일 업로드 섹션 */} - <div className="border rounded-lg p-4"> - <h4 className="font-medium mb-2">2. 파일 업로드</h4> - <div className="grid gap-2"> - <Label htmlFor="excel-file">Excel 파일 선택</Label> - <Input - id="excel-file" - type="file" - accept=".xlsx,.xls" - onChange={handleFileChange} - disabled={isProcessing} - className="file:mr-4 file:py-2 file:px-4 file:rounded-full file:border-0 file:text-sm file:font-semibold file:bg-blue-50 file:text-blue-700 hover:file:bg-blue-100" - /> - {file && ( - <p className="text-sm text-gray-600 dark:text-gray-400 mt-1"> - 선택된 파일: {file.name} ({(file.size / 1024 / 1024).toFixed(2)} MB) - </p> - )} - </div> - </div> - - {/* 진행 상태 */} - {isProcessing && ( - <div className="border rounded-lg p-4 bg-yellow-50/30 dark:bg-yellow-950/30"> - <div className="flex items-center gap-2 mb-2"> - <Loader2 className="h-4 w-4 animate-spin text-yellow-600" /> - <span className="text-sm font-medium text-yellow-800 dark:text-yellow-200">처리 중...</span> - </div> - <p className="text-sm text-yellow-700 dark:text-yellow-300 mb-2">{processStep}</p> - <Progress value={progress} className="h-2" /> - </div> - )} - - {/* 임포트 결과 */} - {importResult && <ImportResultDisplay importResult={importResult} />} - - {/* 파일 형식 가이드 */} - <FileFormatGuide projectType={projectType} /> - </div> - </div> - - <DialogFooter className="flex-shrink-0"> - <Button variant="outline" onClick={handleClose}> - {importResult ? "닫기" : "취소"} - </Button> - {!importResult ? ( - <Button onClick={handleImport} disabled={!file || isProcessing}> - {isProcessing ? <Loader2 className="h-4 w-4 animate-spin mr-2" /> : <Upload className="h-4 w-4 mr-2" />} - {isProcessing ? "처리 중..." : "임포트 시작"} - </Button> - ) : importResult.documents.length > 0 ? ( - <Button onClick={handleConfirmImport}>완료 및 새로고침</Button> - ) : null} - </DialogFooter> - </DialogContent> - </Dialog> - ) -} - -// ============================================================================= -// Sub Components -// ============================================================================= -function ImportResultDisplay({ importResult }: { importResult: ImportResult }) { - return ( - <div className="space-y-3"> - {importResult.documents.length > 0 && ( - <Alert> - <CheckCircle className="h-4 w-4" /> - <AlertDescription> - <strong>{importResult.documents.length}개</strong> 문서가 성공적으로 임포트되었습니다. - {importResult.stages.length > 0 && <> ({importResult.stages.length}개 스테이지 계획날짜 포함)</>} - </AlertDescription> - </Alert> - )} - - {importResult.warnings.length > 0 && ( - <Alert> - <AlertCircle className="h-4 w-4" /> - <AlertDescription> - <strong>경고:</strong> - <ul className="mt-1 list-disc list-inside"> - {importResult.warnings.map((warning, index) => ( - <li key={index} className="text-sm"> - {warning} - </li> - ))} - </ul> - </AlertDescription> - </Alert> - )} - - {importResult.errors.length > 0 && ( - <Alert variant="destructive"> - <AlertCircle className="h-4 w-4" /> - <AlertDescription> - <strong>오류:</strong> - <ul className="mt-1 list-disc list-inside"> - {importResult.errors.map((error, index) => ( - <li key={index} className="text-sm"> - {error} - </li> - ))} - </ul> - </AlertDescription> - </Alert> - )} - </div> - ) -} - -function FileFormatGuide({ projectType }: { projectType: "ship" | "plant" }) { - return ( - <div className="bg-gray-50 dark:bg-gray-900 border border-gray-200 dark:border-gray-700 rounded-lg p-4"> - <h4 className="font-medium text-gray-800 dark:text-gray-200 mb-2">파일 형식 가이드</h4> - <div className="text-sm text-gray-700 dark:text-gray-300 space-y-1"> - <p> - <strong>통합 Documents 시트:</strong> - </p> - <ul className="ml-4 list-disc"> - <li>Document Number* (문서번호)</li> - <li>Document Name* (문서명)</li> - <li>Document Class* (문서클래스 - 드롭다운 선택)</li> - <li>Project Doc No.* (프로젝트 문서번호)</li> - <li>각 Stage Name 컬럼 (계획날짜 입력: YYYY-MM-DD)</li> - </ul> - <p className="mt-2 text-green-600 dark:text-green-400"> - <strong>스마트 검증 기능:</strong> - </p> - <ul className="ml-4 list-disc text-green-600 dark:text-green-400"> - <li>Document Class 드롭다운으로 정확한 값 선택</li> - <li>선택한 Class에 맞지 않는 Stage는 자동으로 회색 처리</li> - <li>잘못된 Stage에 날짜 입력시 빨간색으로 경고</li> - <li>날짜 형식 자동 검증</li> - </ul> - <p className="mt-2 text-yellow-600 dark:text-yellow-400"> - <strong>색상 가이드:</strong> - </p> - <ul className="ml-4 list-disc text-yellow-600 dark:text-yellow-400"> - <li>🟦 파란색 헤더: 필수 입력 항목</li> - <li>🟩 초록색 헤더: 해당 Class의 유효한 Stage</li> - <li>⬜ 회색 셀: 해당 Class에서 사용 불가능한 Stage</li> - <li>🟥 빨간색 셀: 잘못된 입력 (검증 실패)</li> - </ul> - <p className="mt-2 text-red-600 dark:text-red-400">* 필수 항목</p> - </div> - </div> - ) -} - -// ============================================================================= -// Helper Functions -// ============================================================================= -function validateFileExtension(file: File): boolean { - const allowedExtensions = [".xlsx", ".xls"] - const fileName = file.name.toLowerCase() - return allowedExtensions.some((ext) => fileName.endsWith(ext)) -} - -function validateFileSize(file: File, maxSizeMB: number): boolean { - const maxSizeBytes = maxSizeMB * 1024 * 1024 - return file.size <= maxSizeBytes -} - -function getExcelColumnName(index: number): string { - let result = "" - while (index > 0) { - index-- - result = String.fromCharCode(65 + (index % 26)) + result - index = Math.floor(index / 26) - } - return result -} - -function styleHeaderRow( - headerRow: ExcelJS.Row, - bgColor: string = "FF4472C4", - startCol?: number, - endCol?: number -) { - const start = startCol || 1 - const end = endCol || headerRow.cellCount - - for (let i = start; i <= end; i++) { - const cell = headerRow.getCell(i) - cell.fill = { - type: "pattern", - pattern: "solid", - fgColor: { argb: bgColor }, - } - cell.font = { - color: { argb: "FFFFFFFF" }, - bold: true, - } - cell.alignment = { - horizontal: "center", - vertical: "middle", - } - cell.border = { - top: { style: "thin" }, - left: { style: "thin" }, - bottom: { style: "thin" }, - right: { style: "thin" }, - } - } - headerRow.height = 20 -} - -// ============================================================================= -// Template Creation - 통합 시트 + 조건부서식/검증 -// ============================================================================= -async function createImportTemplate(projectType: "ship" | "plant", contractId: number) { - const res = await getDocumentClassOptionsByContract(contractId) - if (!res.success) throw new Error(res.error || "데이터 로딩 실패") - - const documentClasses = res.data.classes as Array<{ id: number; code: string; description: string }> - const options = res.data.options as Array<{ documentClassId: number; optionValue: string }> - - // 클래스별 옵션 맵 - const optionsByClassId = new Map<number, string[]>() - for (const c of documentClasses) optionsByClassId.set(c.id, []) - for (const o of options) optionsByClassId.get(o.documentClassId)!.push(o.optionValue) - - // 유니크 Stage - const allStageNames = Array.from(new Set(options.map((o) => o.optionValue))) - - const workbook = new ExcelJS.Workbook() - // 파일 열 때 강제 전체 계산 - workbook.calcProperties.fullCalcOnLoad = true - - // ================= Documents 시트 ================= - const worksheet = workbook.addWorksheet("Documents") - - const headers = [ - "Document Number*", - "Document Name*", - "Document Class*", - ...(projectType === "plant" ? ["Project Doc No.*"] : []), - ...allStageNames, - ] - const headerRow = worksheet.addRow(headers) - - // 필수 헤더 (파랑) - const requiredCols = projectType === "plant" ? 4 : 3 - styleHeaderRow(headerRow, "FF4472C4", 1, requiredCols) - // Stage 헤더 (초록) - styleHeaderRow(headerRow, "FF27AE60", requiredCols + 1, headers.length) - - // 샘플 데이터 - const firstClass = documentClasses[0] - const firstClassStages = firstClass ? optionsByClassId.get(firstClass.id) ?? [] : [] - const sampleRow = [ - projectType === "ship" ? "SH-2024-001" : "PL-2024-001", - "샘플 문서명", - firstClass ? firstClass.description : "", - ...(projectType === "plant" ? ["V-001"] : []), - ...allStageNames.map((s) => (firstClassStages.includes(s) ? "2024-03-01" : "")), - ] - worksheet.addRow(sampleRow) - - const docNumberColIndex = 1; // A: Document Number* - const docNameColIndex = 2; // B: Document Name* - const docNumberColLetter = getExcelColumnName(docNumberColIndex); - const docNameColLetter = getExcelColumnName(docNameColIndex); - - worksheet.dataValidations.add(`${docNumberColLetter}2:${docNumberColLetter}1000`, { - type: "textLength", - operator: "greaterThan", - formulae: [0], - allowBlank: false, - showErrorMessage: true, - errorTitle: "필수 입력", - error: "Document Number는 필수 항목입니다.", - }); - - // 1) 빈값 금지 (길이 > 0) - worksheet.dataValidations.add(`${docNumberColLetter}2:${docNumberColLetter}1000`, { - type: "textLength", - operator: "greaterThan", - formulae: [0], - allowBlank: false, - showErrorMessage: true, - errorTitle: "필수 입력", - error: "Document Number는 필수 항목입니다.", - }); - - - // 드롭다운: Document Class - const docClassColIndex = 3 // "Document Class*"는 항상 3열 - const docClassColLetter = getExcelColumnName(docClassColIndex) - worksheet.dataValidations.add(`${docClassColLetter}2:${docClassColLetter}1000`, { - type: "list", - allowBlank: false, - formulae: [`ReferenceData!$A$2:$A${documentClasses.length + 1}`], - showErrorMessage: true, - errorTitle: "잘못된 입력", - error: "드롭다운 목록에서 Document Class를 선택하세요.", - }) - - // 2) 중복 금지 (COUNTIF로 현재 값이 범위에서 1회만 등장해야 함) - // - Validation은 한 셀에 1개만 가능하므로, 중복 검증은 "Custom" 하나로 통합하는 방법도 있음. - // - 여기서는 '중복 금지'를 추가적으로 **Guidance용**으로 Conditional Formatting(빨간색)으로 가시화합니다. - worksheet.addConditionalFormatting({ - ref: `${docNumberColLetter}2:${docNumberColLetter}1000`, - rules: [ - // 빈값 빨간 - { - type: "expression", - formulae: [`LEN(${docNumberColLetter}2)=0`], - style: { - fill: { type: "pattern", pattern: "solid", bgColor: { argb: "FFFFC7CE" } }, - }, - }, - // 중복 빨간: COUNTIF($A$2:$A$1000, A2) > 1 - { - type: "expression", - formulae: [`COUNTIF($${docNumberColLetter}$2:$${docNumberColLetter}$1000,${docNumberColLetter}2)>1`], - style: { - fill: { type: "pattern", pattern: "solid", bgColor: { argb: "FFFFC7CE" } }, - }, - }, - ], - }); - - - // ===== Document Name* (B열): 빈값 금지 + 빈칸 빨간 ===== -worksheet.dataValidations.add(`${docNameColLetter}2:${docNameColLetter}1000`, { - type: "textLength", - operator: "greaterThan", - formulae: [0], - allowBlank: false, - showErrorMessage: true, - errorTitle: "필수 입력", - error: "Document Name은 필수 항목입니다.", -}); - -worksheet.addConditionalFormatting({ - ref: `${docNameColLetter}2:${docNameColLetter}1000`, - rules: [ - { - type: "expression", - formulae: [`LEN(${docNameColLetter}2)=0`], - style: { - fill: { type: "pattern", pattern: "solid", bgColor: { argb: "FFFFC7CE" } }, - }, - }, - ], -}); - -// ===== Document Class* (C열): 드롭다운 + allowBlank:false로 차단은 되어 있음 → 빈칸 빨간만 추가 ===== -worksheet.addConditionalFormatting({ - ref: `${docClassColLetter}2:${docClassColLetter}1000`, - rules: [ - { - type: "expression", - formulae: [`LEN(${docClassColLetter}2)=0`], - style: { - fill: { type: "pattern", pattern: "solid", bgColor: { argb: "FFFFC7CE" } }, - }, - }, - ], -}); - -// ===== Project Doc No.* (Plant 전용): (이미 작성하신 코드 유지) ===== -if (projectType === "plant") { - const vendorDocColIndex = 4; // D - const vendorDocColLetter = getExcelColumnName(vendorDocColIndex); - - worksheet.dataValidations.add(`${vendorDocColLetter}2:${vendorDocColLetter}1000`, { - type: "textLength", - operator: "greaterThan", - formulae: [0], - allowBlank: false, - showErrorMessage: true, - errorTitle: "필수 입력", - error: "Project Doc No.는 필수 항목입니다.", - }); - - worksheet.addConditionalFormatting({ - ref: `${vendorDocColLetter}2:${vendorDocColLetter}1000`, - rules: [ - { - type: "expression", - formulae: [`LEN(${vendorDocColLetter}2)=0`], - style: { - fill: { type: "pattern", pattern: "solid", bgColor: { argb: "FFFFC7CE" } }, - }, - }, - // 중복 빨간: COUNTIF($A$2:$A$1000, A2) > 1 - { - type: "expression", - formulae: [`COUNTIF($${vendorDocColLetter}$2:$${vendorDocColLetter}$1000,${vendorDocColLetter}2)>1`], - style: { - fill: { type: "pattern", pattern: "solid", bgColor: { argb: "FFFFC7CE" } }, - }, - }, - ], - }); - -} - - if (projectType === "plant") { - const vendorDocColIndex = 4; // Document Number, Name, Class 다음이 Project Doc No.* - const vendorDocColLetter = getExcelColumnName(vendorDocColIndex); - - // 공백 불가: 글자수 > 0 - worksheet.dataValidations.add(`${vendorDocColLetter}2:${vendorDocColLetter}1000`, { - type: "textLength", - operator: "greaterThan", - formulae: [0], - allowBlank: false, - showErrorMessage: true, - errorTitle: "필수 입력", - error: "Project Doc No.는 필수 항목입니다.", - }); - - // UX: 비어있으면 빨간 배경으로 표시 (조건부 서식) - worksheet.addConditionalFormatting({ - ref: `${vendorDocColLetter}2:${vendorDocColLetter}1000`, - rules: [ - { - type: "expression", - formulae: [`LEN(${vendorDocColLetter}2)=0`], - style: { - fill: { type: "pattern", pattern: "solid", bgColor: { argb: "FFFFC7CE" } }, // 연한 빨강 - }, - }, - ], - }); - } - - // 날짜 셀 형식 + 검증/조건부서식 - const stageStartCol = requiredCols + 1 - const stageEndCol = stageStartCol + allStageNames.length - 1 - - // ================= 매트릭스 시트 (Class-Stage Matrix) ================= - const matrixSheet = workbook.addWorksheet("Class-Stage Matrix") - const matrixHeaders = ["Document Class", ...allStageNames] - const matrixHeaderRow = matrixSheet.addRow(matrixHeaders) - styleHeaderRow(matrixHeaderRow, "FF34495E") - for (const docClass of documentClasses) { - const validStages = new Set(optionsByClassId.get(docClass.id) ?? []) - const row = [docClass.description, ...allStageNames.map((stage) => (validStages.has(stage) ? "✓" : ""))] - const dataRow = matrixSheet.addRow(row) - allStageNames.forEach((stage, idx) => { - const cell = dataRow.getCell(idx + 2) - if (validStages.has(stage)) { - cell.fill = { type: "pattern", pattern: "solid", fgColor: { argb: "FFD4EDDA" } } - cell.font = { color: { argb: "FF28A745" } } - } - }) - } - matrixSheet.columns = [{ width: 30 }, ...allStageNames.map(() => ({ width: 15 }))] - - // 매트릭스 범위 계산 (B ~ 마지막 Stage 열) - const matrixStageFirstColLetter = "B" - const matrixStageLastColLetter = getExcelColumnName(1 + allStageNames.length) // 1:A, 2:B ... (A는 Class, B부터 Stage) - const matrixClassCol = "$A:$A" - const matrixHeaderRowRange = "$1:$1" - const matrixBodyRange = `$${matrixStageFirstColLetter}:$${matrixStageLastColLetter}` - - // ================= 가이드 시트 ================= - const guideSheet = workbook.addWorksheet("사용 가이드") - const guideContent: string[][] = [ - ["📋 통합 문서 임포트 가이드"], - [""], - ["1. 하나의 시트에서 모든 정보 관리"], - [" • Document Number*: 고유한 문서 번호"], - [" • Document Name*: 문서명"], - [" • Document Class*: 드롭다운에서 선택"], - ...(projectType === "plant" ? [[" • Project Doc No.: 벤더 문서 번호"]] : []), - [" • Stage 컬럼들: 각 스테이지의 계획 날짜 (YYYY-MM-DD)"], - [""], - ["2. 스마트 검증 기능"], - [" • Document Class를 선택하면 해당하지 않는 Stage는 자동으로 비활성화(회색)"], - [" • 비유효 Stage에 날짜 입력 시 입력 자체가 막히고 경고 표시"], - [" • 날짜 형식 자동 검증"], - [""], - ["3. Class-Stage Matrix 시트 활용"], - [" • 각 Document Class별로 사용 가능한 Stage 확인"], - [" • ✓ 표시가 있는 Stage만 해당 Class에서 사용 가능"], - [""], - ["4. 작성 순서"], - [" ① Document Number, Name 입력"], - [" ② Document Class 드롭다운에서 선택"], - [" ③ Class-Stage Matrix 확인하여 유효한 Stage 파악"], - [" ④ 해당 Stage 컬럼에만 날짜 입력"], - [""], - ["5. 주의사항"], - [" • * 표시는 필수 항목"], - [" • Document Number는 중복 불가"], - [" • 해당 Class에 맞지 않는 Stage에 날짜 입력 시 무시/차단"], - [" • 날짜는 YYYY-MM-DD 형식 준수"], - ] - guideContent.forEach((row, i) => { - const r = guideSheet.addRow(row) - if (i === 0) r.getCell(1).font = { bold: true, size: 14 } - else if (row[0] && !row[0].startsWith(" ")) r.getCell(1).font = { bold: true } - }) - guideSheet.getColumn(1).width = 70 - - // ================= ReferenceData (숨김) ================= - const referenceSheet = workbook.addWorksheet("ReferenceData", { state: "hidden" }) - referenceSheet.getCell("A1").value = "DocumentClasses" - documentClasses.forEach((dc, idx) => { - referenceSheet.getCell(`A${idx + 2}`).value = dc.description - }) - - // ================= Stage 열별 서식/검증 ================= - // 문서 시트 컬럼 너비 - worksheet.columns = [ - { width: 18 }, // Doc Number - { width: 30 }, // Doc Name - { width: 30 }, // Doc Class - ...(projectType === "plant" ? [{ width: 18 }] : []), - ...allStageNames.map(() => ({ width: 12 })), - ] - - // 각 Stage 열 처리 - for (let stageIdx = 0; stageIdx < allStageNames.length; stageIdx++) { - const colIndex = stageStartCol + stageIdx - const colLetter = getExcelColumnName(colIndex) - - // 날짜 표시 형식 - for (let row = 2; row <= 1000; row++) { - worksheet.getCell(`${colLetter}${row}`).numFmt = "yyyy-mm-dd" - } - - // ---- 커스텀 데이터 검증 (빈칸 OR (해당 Class에 유효한 Stage AND 숫자(=날짜))) ---- - // INDEX('Class-Stage Matrix'!$B:$ZZ, MATCH($C2,'Class-Stage Matrix'!$A:$A,0), MATCH(H$1,'Class-Stage Matrix'!$1:$1,0)) - const validationFormula = - `=OR(` + - `LEN(${colLetter}2)=0,` + - `AND(` + - `INDEX('Class-Stage Matrix'!$${matrixStageFirstColLetter}:$${matrixStageLastColLetter},` + - `MATCH($${docClassColLetter}2,'Class-Stage Matrix'!${matrixClassCol},0),` + - `MATCH(${colLetter}$1,'Class-Stage Matrix'!${matrixHeaderRowRange},0)` + - `)<>\"\",` + - `ISNUMBER(${colLetter}2)` + - `)` + - `)` - worksheet.dataValidations.add(`${colLetter}2:${colLetter}1000`, { - type: "custom", - allowBlank: true, - formulae: [validationFormula], - showErrorMessage: true, - errorTitle: "허용되지 않은 입력", - error: "이 Stage는 선택한 Document Class에서 사용할 수 없거나 날짜 형식이 아닙니다.", - }) - - // ---- 조건부 서식 (유효하지 않은 Stage → 회색 배경) ---- - // TRUE이면 서식 적용: INDEX(...)="" -> 유효하지 않음 - const cfFormula = - `IFERROR(` + - `INDEX('Class-Stage Matrix'!$${matrixStageFirstColLetter}:$${matrixStageLastColLetter},` + - `MATCH($${docClassColLetter}2,'Class-Stage Matrix'!${matrixClassCol},0),` + - `MATCH(${colLetter}$1,'Class-Stage Matrix'!${matrixHeaderRowRange},0)` + - `)=\"\",` + - `TRUE` + // 매치 실패 등 오류 시에도 회색 처리 - `)` - worksheet.addConditionalFormatting({ - ref: `${colLetter}2:${colLetter}1000`, - rules: [ - { - type: "expression", - formulae: [cfFormula], - style: { - fill: { type: "pattern", pattern: "solid", bgColor: { argb: "FFEFEFEF" } }, // 연회색 - }, - }, - ], - }) - } - - return workbook -} - -// ============================================================================= -// Parse Documents with Stages - 통합 파싱 -// ============================================================================= -async function parseDocumentsWithStages( - worksheet: ExcelJS.Worksheet, - projectType: "ship" | "plant", - contractId: number -): Promise<{ validData: ParsedDocument[]; errors: string[] }> { - const documents: ParsedDocument[] = [] - const errors: string[] = [] - const seenDocNumbers = new Set<string>() - - const res = await getDocumentClassOptionsByContract(contractId) - if (!res.success) { - errors.push("Document Class 정보를 불러올 수 없습니다") - return { validData: [], errors } - } - const documentClasses = res.data.classes as Array<{ id: number; description: string }> - const options = res.data.options as Array<{ documentClassId: number; optionValue: string }> - - // 클래스별 유효한 스테이지 맵 - const validStagesByClass = new Map<string, Set<string>>() - for (const c of documentClasses) { - const stages = options.filter((o) => o.documentClassId === c.id).map((o) => o.optionValue) - validStagesByClass.set(c.description, new Set(stages)) - } - - // 헤더 파싱 - const headerRow = worksheet.getRow(1) - const headers: string[] = [] - headerRow.eachCell((cell, colNumber) => { - headers[colNumber - 1] = String(cell.value || "").trim() - }) - - const docNumberIdx = headers.findIndex((h) => h.includes("Document Number")) - const docNameIdx = headers.findIndex((h) => h.includes("Document Name")) - const docClassIdx = headers.findIndex((h) => h.includes("Document Class")) - const vendorDocNoIdx = projectType === "plant" ? headers.findIndex((h) => h.includes("Project Doc No")) : -1 - - if (docNumberIdx === -1 || docNameIdx === -1 || docClassIdx === -1) { - errors.push("필수 헤더가 누락되었습니다") - return { validData: [], errors } - } - - const stageStartIdx = projectType === "plant" ? 4 : 3 // headers slice 기준(0-index) - const stageHeaders = headers.slice(stageStartIdx) - - // 데이터 행 파싱 - worksheet.eachRow((row, rowNumber) => { - if (rowNumber === 1) return - const docNumber = String(row.getCell(docNumberIdx + 1).value || "").trim() - const docName = String(row.getCell(docNameIdx + 1).value || "").trim() - const docClass = String(row.getCell(docClassIdx + 1).value || "").trim() - const vendorDocNo = vendorDocNoIdx >= 0 ? String(row.getCell(vendorDocNoIdx + 1).value || "").trim() : undefined - - if (!docNumber && !docName) return - if (!docNumber) { - errors.push(`행 ${rowNumber}: Document Number가 없습니다`) - return - } - if (!docName) { - errors.push(`행 ${rowNumber}: Document Name이 없습니다`) - return - } - if (!docClass) { - errors.push(`행 ${rowNumber}: Document Class가 없습니다`) - return - } - if (projectType === "plant" && !vendorDocNo) { - errors.push(`행 ${rowNumber}: Project Doc No.가 없습니다`) - return - } - if (seenDocNumbers.has(docNumber)) { - errors.push(`행 ${rowNumber}: 중복된 Document Number: ${docNumber}`) - return - } - seenDocNumbers.add(docNumber) - - const validStages = validStagesByClass.get(docClass) - if (!validStages) { - errors.push(`행 ${rowNumber}: 유효하지 않은 Document Class: ${docClass}`) - return - } - - - - const stages: { stageName: string; planDate: string }[] = [] - stageHeaders.forEach((stageName, idx) => { - if (validStages.has(stageName)) { - const cell = row.getCell(stageStartIdx + idx + 1) - let planDate = "" - if (cell.value) { - if (cell.value instanceof Date) { - planDate = cell.value.toISOString().split("T")[0] - } else { - const dateStr = String(cell.value).trim() - if (/^\d{4}-\d{2}-\d{2}$/.test(dateStr)) planDate = dateStr - } - if (planDate) stages.push({ stageName, planDate }) - } - } - }) - - documents.push({ - docNumber, - title: docName, - documentClass: docClass, - vendorDocNumber: vendorDocNo, - stages, - }) - }) - - return { validData: documents, errors } -} diff --git a/lib/vendor-document-list/plant/excel-import-stage copy.tsx b/lib/vendor-document-list/plant/excel-import-stage copy.tsx deleted file mode 100644 index 068383af..00000000 --- a/lib/vendor-document-list/plant/excel-import-stage copy.tsx +++ /dev/null @@ -1,908 +0,0 @@ - - -"use client" - -import React from "react" -import { Dialog, DialogContent, DialogDescription, DialogFooter, DialogHeader, DialogTitle } from "@/components/ui/dialog" -import { Button } from "@/components/ui/button" -import { Input } from "@/components/ui/input" -import { Label } from "@/components/ui/label" -import { Progress } from "@/components/ui/progress" -import { Alert, AlertDescription } from "@/components/ui/alert" -import { Upload, FileSpreadsheet, Loader2, Download, CheckCircle, AlertCircle } from "lucide-react" -import { toast } from "sonner" -import { useRouter } from "next/navigation" -import ExcelJS from 'exceljs' -import { - getDocumentClassOptionsByContract, - // These functions need to be implemented in document-stages-service - uploadImportData, -} from "./document-stages-service" - -// ============================================================================= -// Type Definitions -// ============================================================================= -interface ExcelImportDialogProps { - open: boolean - onOpenChange: (open: boolean) => void - contractId: number - projectType: "ship" | "plant" -} - -interface ImportResult { - documents: any[] - stages: any[] - errors: string[] - warnings: string[] -} - -// ============================================================================= -// Main Component -// ============================================================================= -export function ExcelImportDialog({ - open, - onOpenChange, - contractId, - projectType -}: ExcelImportDialogProps) { - const [file, setFile] = React.useState<File | null>(null) - const [isProcessing, setIsProcessing] = React.useState(false) - const [isDownloadingTemplate, setIsDownloadingTemplate] = React.useState(false) - const [importResult, setImportResult] = React.useState<ImportResult | null>(null) - const [processStep, setProcessStep] = React.useState<string>("") - const [progress, setProgress] = React.useState(0) - const router = useRouter() - - const handleFileChange = (e: React.ChangeEvent<HTMLInputElement>) => { - const selectedFile = e.target.files?.[0] - if (selectedFile) { - // 파일 유효성 검사 - if (!validateFileExtension(selectedFile)) { - toast.error("Excel 파일(.xlsx, .xls)만 업로드 가능합니다.") - return - } - - if (!validateFileSize(selectedFile, 10)) { - toast.error("파일 크기는 10MB 이하여야 합니다.") - return - } - - setFile(selectedFile) - setImportResult(null) - } - } - - const handleDownloadTemplate = async () => { - setIsDownloadingTemplate(true) - try { - const workbook = await createImportTemplate(projectType, contractId) - const buffer = await workbook.xlsx.writeBuffer() - - const blob = new Blob([buffer], { - type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' - }) - - const url = window.URL.createObjectURL(blob) - const link = document.createElement('a') - link.href = url - link.download = `문서_임포트_템플릿_${projectType}_${new Date().toISOString().split('T')[0]}.xlsx` - link.click() - - window.URL.revokeObjectURL(url) - toast.success("템플릿 파일이 다운로드되었습니다.") - } catch (error) { - toast.error("템플릿 다운로드 중 오류가 발생했습니다: " + (error instanceof Error ? error.message : "알 수 없는 오류")) - } finally { - setIsDownloadingTemplate(false) - } - } - - const handleImport = async () => { - if (!file) { - toast.error("파일을 선택해주세요.") - return - } - - setIsProcessing(true) - setProgress(0) - - try { - setProcessStep("파일 읽는 중...") - setProgress(20) - - const workbook = new ExcelJS.Workbook() - const buffer = await file.arrayBuffer() - await workbook.xlsx.load(buffer) - - setProcessStep("데이터 검증 중...") - setProgress(40) - - // 워크시트 확인 - const documentsSheet = workbook.getWorksheet('Documents') || workbook.getWorksheet(1) - const stagesSheet = workbook.getWorksheet('Stage Plan Dates') || workbook.getWorksheet(2) - - if (!documentsSheet) { - throw new Error("Documents 시트를 찾을 수 없습니다.") - } - - setProcessStep("문서 데이터 파싱 중...") - setProgress(60) - - // 문서 데이터 파싱 - const documentData = await parseDocumentsSheet(documentsSheet, projectType) - - setProcessStep("스테이지 데이터 파싱 중...") - setProgress(80) - - // 스테이지 데이터 파싱 (선택사항) - let stageData: any[] = [] - if (stagesSheet) { - stageData = await parseStagesSheet(stagesSheet) - } - - setProcessStep("서버에 업로드 중...") - setProgress(90) - - // 서버로 데이터 전송 - const result = await uploadImportData({ - contractId, - documents: documentData.validData, - stages: stageData, - projectType - }) - - if (result.success) { - setImportResult({ - documents: documentData.validData, - stages: stageData, - errors: documentData.errors, - warnings: result.warnings || [] - }) - setProgress(100) - toast.success(`${documentData.validData.length}개 문서가 성공적으로 임포트되었습니다.`) - } else { - throw new Error(result.error || "임포트에 실패했습니다.") - } - - } catch (error) { - toast.error(error instanceof Error ? error.message : "임포트 중 오류가 발생했습니다.") - setImportResult({ - documents: [], - stages: [], - errors: [error instanceof Error ? error.message : "알 수 없는 오류"], - warnings: [] - }) - } finally { - setIsProcessing(false) - setProcessStep("") - setProgress(0) - } - } - - const handleClose = () => { - setFile(null) - setImportResult(null) - setProgress(0) - setProcessStep("") - onOpenChange(false) - } - - const handleConfirmImport = () => { - router.refresh() - handleClose() - } - - return ( - <Dialog open={open} onOpenChange={onOpenChange}> - <DialogContent className="sm:max-w-[600px] max-h-[90vh] flex flex-col"> - <DialogHeader className="flex-shrink-0"> - <DialogTitle> - <FileSpreadsheet className="inline w-5 h-5 mr-2" /> - Excel 파일 임포트 - </DialogTitle> - <DialogDescription> - Excel 파일을 사용하여 문서를 일괄 등록합니다. - </DialogDescription> - </DialogHeader> - - <div className="flex-1 overflow-y-auto pr-2"> - <div className="grid gap-4 py-4"> - {/* 템플릿 다운로드 섹션 */} - <div className="border rounded-lg p-4 bg-blue-50/30 dark:bg-blue-950/30"> - <h4 className="font-medium text-blue-800 dark:text-blue-200 mb-2">1. 템플릿 다운로드</h4> - <p className="text-sm text-blue-700 dark:text-blue-300 mb-3"> - 올바른 형식과 드롭다운이 적용된 템플릿을 다운로드하세요. - </p> - <Button - variant="outline" - size="sm" - onClick={handleDownloadTemplate} - disabled={isDownloadingTemplate} - > - {isDownloadingTemplate ? ( - <Loader2 className="h-4 w-4 animate-spin mr-2" /> - ) : ( - <Download className="h-4 w-4 mr-2" /> - )} - 템플릿 다운로드 - </Button> - </div> - - {/* 파일 업로드 섹션 */} - <div className="border rounded-lg p-4"> - <h4 className="font-medium mb-2">2. 파일 업로드</h4> - <div className="grid gap-2"> - <Label htmlFor="excel-file">Excel 파일 선택</Label> - <Input - id="excel-file" - type="file" - accept=".xlsx,.xls" - onChange={handleFileChange} - disabled={isProcessing} - className="file:mr-4 file:py-2 file:px-4 file:rounded-full file:border-0 file:text-sm file:font-semibold file:bg-blue-50 file:text-blue-700 hover:file:bg-blue-100" - /> - {file && ( - <p className="text-sm text-gray-600 dark:text-gray-400 mt-1"> - 선택된 파일: {file.name} ({(file.size / 1024 / 1024).toFixed(2)} MB) - </p> - )} - </div> - </div> - - {/* 진행 상태 */} - {isProcessing && ( - <div className="border rounded-lg p-4 bg-yellow-50/30 dark:bg-yellow-950/30"> - <div className="flex items-center gap-2 mb-2"> - <Loader2 className="h-4 w-4 animate-spin text-yellow-600" /> - <span className="text-sm font-medium text-yellow-800 dark:text-yellow-200">처리 중...</span> - </div> - <p className="text-sm text-yellow-700 dark:text-yellow-300 mb-2">{processStep}</p> - <Progress value={progress} className="h-2" /> - </div> - )} - - {/* 임포트 결과 */} - {importResult && ( - <ImportResultDisplay importResult={importResult} /> - )} - - {/* 파일 형식 가이드 */} - <FileFormatGuide projectType={projectType} /> - </div> - </div> - - <DialogFooter className="flex-shrink-0"> - <Button variant="outline" onClick={handleClose}> - {importResult ? "닫기" : "취소"} - </Button> - {!importResult ? ( - <Button - onClick={handleImport} - disabled={!file || isProcessing} - > - {isProcessing ? ( - <Loader2 className="h-4 w-4 animate-spin mr-2" /> - ) : ( - <Upload className="h-4 w-4 mr-2" /> - )} - {isProcessing ? "처리 중..." : "임포트 시작"} - </Button> - ) : importResult.documents.length > 0 ? ( - <Button onClick={handleConfirmImport}> - 완료 및 새로고침 - </Button> - ) : null} - </DialogFooter> - </DialogContent> - </Dialog> - ) -} - -// ============================================================================= -// Sub Components -// ============================================================================= -function ImportResultDisplay({ importResult }: { importResult: ImportResult }) { - return ( - <div className="space-y-3"> - {importResult.documents.length > 0 && ( - <Alert> - <CheckCircle className="h-4 w-4" /> - <AlertDescription> - <strong>{importResult.documents.length}개</strong> 문서가 성공적으로 임포트되었습니다. - {importResult.stages.length > 0 && ( - <> ({importResult.stages.length}개 스테이지 계획날짜 포함)</> - )} - </AlertDescription> - </Alert> - )} - - {importResult.warnings.length > 0 && ( - <Alert> - <AlertCircle className="h-4 w-4" /> - <AlertDescription> - <strong>경고:</strong> - <ul className="mt-1 list-disc list-inside"> - {importResult.warnings.map((warning, index) => ( - <li key={index} className="text-sm">{warning}</li> - ))} - </ul> - </AlertDescription> - </Alert> - )} - - {importResult.errors.length > 0 && ( - <Alert variant="destructive"> - <AlertCircle className="h-4 w-4" /> - <AlertDescription> - <strong>오류:</strong> - <ul className="mt-1 list-disc list-inside"> - {importResult.errors.map((error, index) => ( - <li key={index} className="text-sm">{error}</li> - ))} - </ul> - </AlertDescription> - </Alert> - )} - </div> - ) -} - -function FileFormatGuide({ projectType }: { projectType: "ship" | "plant" }) { - return ( - <div className="bg-gray-50 dark:bg-gray-900 border border-gray-200 dark:border-gray-700 rounded-lg p-4"> - <h4 className="font-medium text-gray-800 dark:text-gray-200 mb-2">파일 형식 가이드</h4> - <div className="text-sm text-gray-700 dark:text-gray-300 space-y-1"> - <p><strong>Documents 시트:</strong></p> - <ul className="ml-4 list-disc"> - <li>Document Number* (문서번호)</li> - <li>Document Name* (문서명)</li> - <li>Document Class* (문서클래스 - 드롭다운 선택)</li> - {projectType === "plant" && ( - <li>Project Doc No. (벤더문서번호)</li> - )} - </ul> - <p className="mt-2"><strong>Stage Plan Dates 시트 (선택사항):</strong></p> - <ul className="ml-4 list-disc"> - <li>Document Number* (문서번호)</li> - <li>Stage Name* (스테이지명 - 드롭다운 선택)</li> - <li>Plan Date (계획날짜: YYYY-MM-DD)</li> - </ul> - <p className="mt-2 text-green-600 dark:text-green-400"><strong>스마트 기능:</strong></p> - <ul className="ml-4 list-disc text-green-600 dark:text-green-400"> - <li>Document Class는 드롭다운으로 정확한 값만 선택 가능</li> - <li>Stage Name도 드롭다운으로 오타 방지</li> - <li>"사용 가이드" 시트에서 각 클래스별 사용 가능한 스테이지 확인 가능</li> - </ul> - <p className="mt-2 text-red-600 dark:text-red-400">* 필수 항목</p> - </div> - </div> - ) -} - -// ============================================================================= -// Helper Functions -// ============================================================================= -function validateFileExtension(file: File): boolean { - const allowedExtensions = ['.xlsx', '.xls'] - const fileName = file.name.toLowerCase() - return allowedExtensions.some(ext => fileName.endsWith(ext)) -} - -function validateFileSize(file: File, maxSizeMB: number): boolean { - const maxSizeBytes = maxSizeMB * 1024 * 1024 - return file.size <= maxSizeBytes -} - -// ExcelJS 컬럼 인덱스를 문자로 변환 (A, B, C, ... Z, AA, AB, ...) -function getExcelColumnName(index: number): string { - let result = "" - while (index > 0) { - index-- - result = String.fromCharCode(65 + (index % 26)) + result - index = Math.floor(index / 26) - } - return result -} - -// 헤더 행 스타일링 함수 -function styleHeaderRow(headerRow: ExcelJS.Row, bgColor: string = 'FF4472C4') { - headerRow.eachCell((cell) => { - cell.fill = { - type: 'pattern', - pattern: 'solid', - fgColor: { argb: bgColor } - } - cell.font = { - color: { argb: 'FFFFFFFF' }, - bold: true - } - cell.alignment = { - horizontal: 'center', - vertical: 'middle' - } - cell.border = { - top: { style: 'thin' }, - left: { style: 'thin' }, - bottom: { style: 'thin' }, - right: { style: 'thin' } - } - - if (String(cell.value).includes('*')) { - cell.fill = { - type: 'pattern', - pattern: 'solid', - fgColor: { argb: 'FFE74C3C' } - } - } - }) -} - -// 템플릿 생성 함수 - Stage Plan Dates 부분 수정 -async function createImportTemplate(projectType: "ship" | "plant", contractId: number) { - const res = await getDocumentClassOptionsByContract(contractId) - if (!res.success) throw new Error(res.error || "데이터 로딩 실패") - - const documentClasses = res.data.classes // [{id, code, description}] - const options = res.data.options // [{documentClassId, optionValue, ...}] - - // 클래스별 옵션 맵 - const optionsByClassId = new Map<number, string[]>() - for (const c of documentClasses) optionsByClassId.set(c.id, []) - for (const o of options) { - optionsByClassId.get(o.documentClassId)?.push(o.optionValue) - } - - // 모든 스테이지 명 (유니크) - const allStageNames = Array.from(new Set(options.map(o => o.optionValue))) - - const workbook = new ExcelJS.Workbook() - - // ================= Documents (첫 번째 시트) ================= - const documentsSheet = workbook.addWorksheet("Documents") - const documentHeaders = [ - "Document Number*", - "Document Name*", - "Document Class*", - ...(projectType === "plant" ? ["Project Doc No."] : []), - "Notes", - ] - const documentHeaderRow = documentsSheet.addRow(documentHeaders) - styleHeaderRow(documentHeaderRow) - - const sampleDocumentData = - projectType === "ship" - ? [ - "SH-2024-001", - "기본 설계 도면", - documentClasses[0] ? `${documentClasses[0].description}` : "", - "참고사항", - ] - : [ - "PL-2024-001", - "공정 설계 도면", - documentClasses[0] ? `${documentClasses[0].description}` : "", - "V-001", - "참고사항", - ] - - documentsSheet.addRow(sampleDocumentData) - - // Document Class 드롭다운 - const docClassColIndex = 3 // C - const docClassCol = getExcelColumnName(docClassColIndex) - documentsSheet.dataValidations.add(`${docClassCol}2:${docClassCol}1000`, { - type: "list", - allowBlank: false, - formulae: [`ReferenceData!$A$2:$A${documentClasses.length + 1}`], - }) - - documentsSheet.columns = [ - { width: 15 }, - { width: 25 }, - { width: 28 }, - ...(projectType === "plant" ? [{ width: 18 }] : []), - { width: 24 }, - ] - - // ================= Stage Plan Dates (두 번째 시트) - 수정됨 ================= - const stagesSheet = workbook.addWorksheet("Stage Plan Dates") - - // Document Class Helper 컬럼과 Valid Stage Helper 컬럼 추가 - const stageHeaderRow = stagesSheet.addRow([ - "Document Number*", - "Document Class", // Helper 컬럼 - 자동으로 채워짐 - "Stage Name*", - "Plan Date", - "Valid Stages" // Helper 컬럼 - 유효한 스테이지 목록 - ]) - styleHeaderRow(stageHeaderRow, "FF27AE60") - - const firstClass = documentClasses[0] - const firstClassOpts = firstClass ? optionsByClassId.get(firstClass.id) ?? [] : [] - - // 샘플 데이터 - const sampleStageData = [ - [ - projectType === "ship" ? "SH-2024-001" : "PL-2024-001", - firstClass ? firstClass.description : "", - firstClassOpts[0] ?? "", - "2024-02-15", - firstClassOpts.join(", ") // 유효한 스테이지 목록 - ], - [ - projectType === "ship" ? "SH-2024-001" : "PL-2024-001", - firstClass ? firstClass.description : "", - firstClassOpts[1] ?? "", - "2024-03-01", - firstClassOpts.join(", ") // 유효한 스테이지 목록 - ], - ] - - sampleStageData.forEach(row => { - const r = stagesSheet.addRow(row) - r.getCell(4).numFmt = "yyyy-mm-dd" - }) - - // B열(Document Class)에 VLOOKUP 수식 추가 - for (let i = 3; i <= 1000; i++) { - const cell = stagesSheet.getCell(`B${i}`) - cell.value = { - formula: `IFERROR(VLOOKUP(A${i},Documents!A:C,3,FALSE),"")`, - result: "" - } - } - - - // E열(Valid Stages)에 수식 추가 - Document Class에 해당하는 스테이지 목록 표시 - // MATCH와 OFFSET을 사용한 동적 참조 - - - // Helper 컬럼 숨기기 옵션 (B, E열) - stagesSheet.getColumn(2).hidden = false // Document Class는 보이도록 (확인용) - stagesSheet.getColumn(5).hidden = false // Valid Stages도 보이도록 (가이드용) - - // Helper 컬럼 스타일링 - stagesSheet.getColumn(2).eachCell((cell, rowNumber) => { - if (rowNumber > 1) { - cell.fill = { - type: 'pattern', - pattern: 'solid', - fgColor: { argb: 'FFF0F0F0' } // 연한 회색 배경 - } - cell.protection = { locked: true } // 편집 방지 - } - }) - - stagesSheet.getColumn(5).eachCell((cell, rowNumber) => { - if (rowNumber > 1) { - cell.fill = { - type: 'pattern', - pattern: 'solid', - fgColor: { argb: 'FFFFF0E0' } // 연한 주황색 배경 - } - cell.font = { size: 9, italic: true } - } - }) - - // Stage Name 드롭다운 - 전체 스테이지 목록 사용 (ExcelJS 제약으로 인해) - // 하지만 조건부 서식으로 잘못된 선택 강조 - const allStagesCol = getExcelColumnName(documentClasses.length + 2) - stagesSheet.dataValidations.add("C3:C1000", { - type: "list", - allowBlank: false, - formulae: [`ReferenceData!${allStagesCol}$2:${allStagesCol}${allStageNames.length + 1}`], - promptTitle: "Stage Name 선택", - prompt: "Valid Stages 컬럼을 참고하여 올바른 Stage를 선택하세요", - showErrorMessage: true, - errorTitle: "Stage 선택 확인", - error: "Valid Stages 컬럼에 있는 Stage만 유효합니다" - }) - - // 조건부 서식 추가 - 잘못된 Stage 선택시 빨간색 표시 - for (let i = 3; i <= 100; i++) { - try { - // COUNTIF를 사용하여 선택한 Stage가 Valid Stages에 포함되는지 확인 - const rule = { - type: 'expression', - formulae: [`ISERROR(SEARCH(C${i},E${i}))`], - style: { - fill: { - type: 'pattern', - pattern: 'solid', - bgColor: { argb: 'FFFF0000' } // 빨간색 배경 - }, - font: { - color: { argb: 'FFFFFFFF' } // 흰색 글자 - } - } - } - stagesSheet.addConditionalFormatting({ - ref: `C${i}`, - rules: [rule] - }) - } catch (e) { - console.warn(`Row ${i}: 조건부 서식 추가 실패`) - } - } - - stagesSheet.columns = [ - { width: 15 }, // Document Number - { width: 20 }, // Document Class (Helper) - { width: 30 }, // Stage Name - { width: 12 }, // Plan Date - { width: 50 } // Valid Stages (Helper) - ] - - // ================= 사용 가이드 (세 번째 시트) - 수정됨 ================= - const guideSheet = workbook.addWorksheet("사용 가이드") - const guideContent: (string[])[] = [ - ["문서 임포트 가이드"], - [""], - ["1. Documents 시트"], - [" - Document Number*: 고유한 문서 번호를 입력하세요"], - [" - Document Name*: 문서명을 입력하세요"], - [" - Document Class*: 드롭다운에서 문서 클래스를 선택하세요"], - [" - Project Doc No.: 벤더 문서 번호 (Plant 프로젝트만)"], - [" - Notes: 참고사항"], - [""], - ["2. Stage Plan Dates 시트 (선택사항)"], - [" - Document Number*: Documents 시트의 Document Number와 일치해야 합니다"], - [" - Document Class: Document Number 입력시 자동으로 표시됩니다 (회색 배경)"], - [" - Stage Name*: Valid Stages 컬럼을 참고하여 해당 클래스의 스테이지를 선택하세요"], - [" - Plan Date: 계획 날짜 (YYYY-MM-DD 형식)"], - [" - Valid Stages: 해당 Document Class에서 선택 가능한 스테이지 목록 (주황색 배경)"], - [""], - ["3. Stage Name 선택 방법"], - [" ① Document Number를 먼저 입력합니다"], - [" ② Document Class가 자동으로 표시됩니다"], - [" ③ Valid Stages 컬럼에서 사용 가능한 스테이지를 확인합니다"], - [" ④ Stage Name 드롭다운에서 Valid Stages에 있는 항목만 선택합니다"], - [" ⑤ 잘못된 스테이지 선택시 셀이 빨간색으로 표시됩니다"], - [""], - ["4. 주의사항"], - [" - * 표시는 필수 항목입니다"], - [" - Document Number는 고유해야 합니다"], - [" - Stage Name은 Valid Stages에 표시된 것만 유효합니다"], - [" - 빨간색으로 표시된 Stage Name은 잘못된 선택입니다"], - [" - 날짜는 YYYY-MM-DD 형식으로 입력하세요"], - [""], - ["5. Document Class별 사용 가능한 Stage Names"], - [""], - ] - - for (const c of documentClasses) { - guideContent.push([`${c.code} - ${c.description}:`]) - ;(optionsByClassId.get(c.id) ?? []).forEach(v => guideContent.push([` • ${v}`])) - guideContent.push([""]) - } - - guideContent.forEach((row, i) => { - const r = guideSheet.addRow(row) - if (i === 0) r.getCell(1).font = { bold: true, size: 14 } - else if (row[0] && row[0].includes(":") && !row[0].startsWith(" ")) r.getCell(1).font = { bold: true } - }) - guideSheet.getColumn(1).width = 60 - - // ================= ReferenceData (마지막 시트, hidden) ================= - const referenceSheet = workbook.addWorksheet("ReferenceData", { state: "hidden" }) - - let joinColStart = /* A=1 기준, 빈 안전한 위치 선택 */ documentClasses.length + 3; -const keyCol = getExcelColumnName(joinColStart); // 예: X -const joinedCol = getExcelColumnName(joinColStart+1); // 예: Y -referenceSheet.getCell(`${keyCol}1`).value = "DocClass"; -referenceSheet.getCell(`${joinedCol}1`).value = "JoinedStages"; - - // A열: DocumentClasses - referenceSheet.getCell("A1").value = "DocumentClasses" - documentClasses.forEach((dc, idx) => { - referenceSheet.getCell(`${keyCol}${idx+2}`).value = dc.description; - const stages = (optionsByClassId.get(dc.id) ?? []).join(", "); - referenceSheet.getCell(`${joinedCol}${idx+2}`).value = stages; - }); - - for (let i = 3; i <= 1000; i++) { - stagesSheet.getCell(`E${i}`).value = { - formula: `IFERROR("유효한 스테이지: "&VLOOKUP(B${i},ReferenceData!$${keyCol}$2:$${joinedCol}$${documentClasses.length+1},2,FALSE),"Document Number를 먼저 입력하세요")`, - result: "" - }; - } - - - // B열부터: 각 클래스의 Stage 옵션 - let currentCol = 2 // B - for (const docClass of documentClasses) { - const colLetter = getExcelColumnName(currentCol) - referenceSheet.getCell(`${colLetter}1`).value = docClass.description - - const list = optionsByClassId.get(docClass.id) ?? [] - list.forEach((v, i) => { - referenceSheet.getCell(`${colLetter}${i + 2}`).value = v - }) - - currentCol++ - } - - // 마지막 열: AllStageNames - referenceSheet.getCell(`${allStagesCol}1`).value = "AllStageNames" - allStageNames.forEach((v, i) => { - referenceSheet.getCell(`${allStagesCol}${i + 2}`).value = v - }) - - return workbook -} - -// ============================================================================= -// Type Definitions -// ============================================================================= -interface ParsedDocument { - docNumber: string - title: string - documentClass: string - vendorDocNumber?: string - notes?: string - } - - interface ParsedStage { - docNumber: string - stageName: string - planDate?: string - } - - interface ParseResult { - validData: ParsedDocument[] - errors: string[] - } - - -// ============================================================================= -// 1. Parse Documents Sheet -// ============================================================================= -export async function parseDocumentsSheet( - worksheet: ExcelJS.Worksheet, - projectType: "ship" | "plant" - ): Promise<ParseResult> { - const documents: ParsedDocument[] = [] - const errors: string[] = [] - const seenDocNumbers = new Set<string>() - - // 헤더 행 확인 - const headerRow = worksheet.getRow(1) - const headers: string[] = [] - headerRow.eachCell((cell, colNumber) => { - headers[colNumber - 1] = String(cell.value || "").trim() - }) - - // 헤더 인덱스 찾기 - const docNumberIdx = headers.findIndex(h => h.includes("Document Number")) - const docNameIdx = headers.findIndex(h => h.includes("Document Name")) - const docClassIdx = headers.findIndex(h => h.includes("Document Class")) - const vendorDocNoIdx = projectType === "plant" - ? headers.findIndex(h => h.includes("Project Doc No")) - : -1 - const notesIdx = headers.findIndex(h => h.includes("Notes")) - - if (docNumberIdx === -1 || docNameIdx === -1 || docClassIdx === -1) { - errors.push("필수 헤더가 누락되었습니다: Document Number, Document Name, Document Class") - return { validData: [], errors } - } - - // 데이터 행 파싱 - worksheet.eachRow((row, rowNumber) => { - if (rowNumber === 1) return // 헤더 행 스킵 - - const docNumber = String(row.getCell(docNumberIdx + 1).value || "").trim() - const docName = String(row.getCell(docNameIdx + 1).value || "").trim() - const docClass = String(row.getCell(docClassIdx + 1).value || "").trim() - const vendorDocNo = vendorDocNoIdx >= 0 - ? String(row.getCell(vendorDocNoIdx + 1).value || "").trim() - : undefined - const notes = notesIdx >= 0 - ? String(row.getCell(notesIdx + 1).value || "").trim() - : undefined - - // 빈 행 스킵 - if (!docNumber && !docName) return - - // 유효성 검사 - if (!docNumber) { - errors.push(`행 ${rowNumber}: Document Number가 없습니다`) - return - } - if (!docName) { - errors.push(`행 ${rowNumber}: Document Name이 없습니다`) - return - } - if (!docClass) { - errors.push(`행 ${rowNumber}: Document Class가 없습니다`) - return - } - - // 중복 체크 - if (seenDocNumbers.has(docNumber)) { - errors.push(`행 ${rowNumber}: 중복된 Document Number: ${docNumber}`) - return - } - seenDocNumbers.add(docNumber) - - documents.push({ - docNumber, - title: docName, - documentClass: docClass, - vendorDocNumber: vendorDocNo || undefined, - notes: notes || undefined - }) - }) - - return { validData: documents, errors } - } - - - -// parseStagesSheet 함수도 수정이 필요합니다 -export async function parseStagesSheet(worksheet: ExcelJS.Worksheet): Promise<ParsedStage[]> { - const stages: ParsedStage[] = [] - - // 헤더 행 확인 - const headerRow = worksheet.getRow(1) - const headers: string[] = [] - headerRow.eachCell((cell, colNumber) => { - headers[colNumber - 1] = String(cell.value || "").trim() - }) - - // 헤더 인덱스 찾기 (Helper 컬럼들 고려) - const docNumberIdx = headers.findIndex(h => h.includes("Document Number")) - // Stage Name 찾기 - "Stage Name*" 또는 "Stage Name"을 찾음 - const stageNameIdx = headers.findIndex(h => h.includes("Stage Name") && !h.includes("Valid")) - const planDateIdx = headers.findIndex(h => h.includes("Plan Date")) - - if (docNumberIdx === -1 || stageNameIdx === -1) { - console.error("Stage Plan Dates 시트에 필수 헤더가 없습니다") - return [] - } - - // 데이터 행 파싱 - worksheet.eachRow((row, rowNumber) => { - if (rowNumber === 1) return // 헤더 행 스킵 - - const docNumber = String(row.getCell(docNumberIdx + 1).value || "").trim() - const stageName = String(row.getCell(stageNameIdx + 1).value || "").trim() - let planDate: string | undefined - - // Plan Date 파싱 - if (planDateIdx >= 0) { - const planDateCell = row.getCell(planDateIdx + 1) - - if (planDateCell.value) { - // Date 객체인 경우 - if (planDateCell.value instanceof Date) { - planDate = planDateCell.value.toISOString().split('T')[0] - } - // 문자열인 경우 - else { - const dateStr = String(planDateCell.value).trim() - // YYYY-MM-DD 형식 검증 - if (/^\d{4}-\d{2}-\d{2}$/.test(dateStr)) { - planDate = dateStr - } - } - } - } - - // 빈 행 스킵 - if (!docNumber && !stageName) return - - // 유효성 검사 - if (!docNumber || !stageName) { - console.warn(`행 ${rowNumber}: Document Number 또는 Stage Name이 누락됨`) - return - } - - stages.push({ - docNumber, - stageName, - planDate - }) - }) - - return stages -}
\ No newline at end of file diff --git a/lib/vendor-document-list/plant/excel-import-stage.tsx b/lib/vendor-document-list/plant/excel-import-stage.tsx index 24db6ea1..4f83d687 100644 --- a/lib/vendor-document-list/plant/excel-import-stage.tsx +++ b/lib/vendor-document-list/plant/excel-import-stage.tsx @@ -803,14 +803,14 @@ async function parseDocumentsWithStages( errors.push("Document Class 정보를 불러올 수 없습니다") return { validData: [], errors } } - const documentClasses = res.data.classes as Array<{ id: number; code: string; description: string }> + const documentClasses = res.data.classes as Array<{ id: number; value: string; description: string }> const options = res.data.options as Array<{ documentClassId: number; optionValue: string }> - // 클래스별 유효한 스테이지 맵 (code 기반) + // 클래스별 유효한 스테이지 맵 (value 기반) const validStagesByClass = new Map<string, Set<string>>() for (const c of documentClasses) { const stages = options.filter((o) => o.documentClassId === c.id).map((o) => o.optionValue) - validStagesByClass.set(c.code, new Set(stages)) + validStagesByClass.set(c.value, new Set(stages)) } // 헤더 파싱 |