diff options
Diffstat (limited to 'lib/evaluation-target-list/service.ts')
| -rw-r--r-- | lib/evaluation-target-list/service.ts | 539 |
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 파일 처리 중 오류가 발생했습니다.' }; + } +} |