From dd20ba9785cdbd3d61f6b014d003d3bd9646ad13 Mon Sep 17 00:00:00 2001 From: joonhoekim <26rote@gmail.com> Date: Thu, 14 Aug 2025 00:26:53 +0000 Subject: (고건) 리스크 관리 페이지 추가 MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit --- lib/risk-management/service.ts | 636 +++++++++++++++++++++++++++++++++++++++++ 1 file changed, 636 insertions(+) create mode 100644 lib/risk-management/service.ts (limited to 'lib/risk-management/service.ts') diff --git a/lib/risk-management/service.ts b/lib/risk-management/service.ts new file mode 100644 index 00000000..1c58657c --- /dev/null +++ b/lib/risk-management/service.ts @@ -0,0 +1,636 @@ +/* eslint-disable @typescript-eslint/no-explicit-any */ + +'use server'; + +/* IMPORT */ +import { + and, + asc, + desc, + eq, + gte, + ilike, + lte, + or, +} from 'drizzle-orm'; +import { authOptions } from '@/app/api/auth/[...nextauth]/route'; +import { + countRisksView, + insertRiskEvents, + selectRiskEvents, + selectRisksView, + updateRiskEvents, +} from './repository'; +import db from '@/db/db'; +import ExcelJS from 'exceljs'; +import { filterColumns } from '@/lib/filter-columns'; +import { getServerSession } from 'next-auth'; +import { RISK_EVENT_TYPE_LIST, RISK_PROVIDER_LIST } from '@/config/risksConfig'; +import { + riskEvents, + risksView, + type RiskEvents, + type RisksView, + users, + vendors, +} from '@/db/schema'; +import { selectUsers } from '../admin-users/repository'; +import { selectVendors } from '../vendors/repository'; +import { sendEmail } from '../mail/sendEmail'; +import { type GetRisksSchema } from './validations'; + +// ---------------------------------------------------------------------------------------------------- + +/* HELPER FUNCTION FOR GETTING CURRENT USER ID */ +async function getCurrentUserId(): Promise { + 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 GETTING RISKS */ +async function getRisksView(input: GetRisksSchema) { + try { + const offset = (input.page - 1) * input.perPage; + + const dateWhere = and( + gte(risksView.occuredAt, new Date(input.from)), + lte(risksView.occuredAt, new Date(input.to)), + ); + + const advancedWhere = filterColumns({ + table: risksView, + filters: input.filters, + joinOperator: input.joinOperator, + }); + + // Filtering + let globalWhere; + if (input.search) { + const s = `%${input.search}%`; + globalWhere = or( + ilike(risksView.eventType, s), + ilike(risksView.vendorCode, s), + ilike(risksView.vendorName, s), + ilike(risksView.businessNumber, s), + ilike(risksView.provider, s), + ); + } + const finalWhere = and(dateWhere, advancedWhere, globalWhere); + + // Sorting + const orderBy = input.sort.length > 0 + ? input.sort.map((item) => { + return item.desc + ? desc(risksView[item.id]) + : asc(risksView[item.id]); + }) + : [asc(risksView.id)]; + + // Getting Data + const { data, total } = await db.transaction(async (tx) => { + const data = await selectRisksView(tx, { + where: finalWhere, + orderBy, + offset, + limit: input.perPage, + }); + const total = await countRisksView(tx, finalWhere); + + return { data, total }; + }); + const pageCount = Math.ceil(total / input.perPage); + return { data, pageCount }; + } catch (err) { + console.error('Error in Getting Risk Data: ', err); + return { data: [], pageCount: 0 }; + } +} + +/* FUNCTION FOR GETTING RISKS VIEW BY ID */ +async function getRisksViewById(id: number) { + try { + return await db.transaction(async (tx) => { + return await selectRisksView(tx, { + where: eq(risksView.id, id), + }); + }); + } catch (err) { + console.error('Error in Getting Risk Events by ID: ', err); + return null; + } +} + +/* FUNCTION FOR GETTING RISK EVENTS BY ID */ +async function getRiskEventsById(id: number) { + try { + return await db.transaction(async (tx) => { + return await selectRiskEvents(tx, { + where: eq(riskEvents.id, id), + }); + }); + } catch (err) { + console.error('Error in Getting Risk Events by ID: ', err); + return null; + } +} + +/* FUNCTION FOR GETTING RISKS VIEW DATA COUNT */ +async function getRisksViewCount(input: GetRisksSchema) { + try { + const dateWhere = and( + gte(risksView.occuredAt, new Date(input.from)), + lte(risksView.occuredAt, new Date(input.to)), + ); + + const advancedWhere = filterColumns({ + table: risksView, + filters: input.filters, + joinOperator: input.joinOperator, + }); + + let globalWhere; + if (input.search) { + const s = `%${input.search}%`; + globalWhere = or( + ilike(risksView.eventType, s), + ilike(risksView.vendorCode, s), + ilike(risksView.vendorName, s), + ilike(risksView.businessNumber, s), + ilike(risksView.provider, s), + ); + } + const statusWhere = eq(risksView.eventStatus, true); + const finalWhere = and(dateWhere, advancedWhere, globalWhere, statusWhere); + + const { total } = await db.transaction(async (tx) => { + const total = await countRisksView(tx, finalWhere); + + return { total }; + }); + + return { count: total }; + } catch (err) { + console.error('Error in Counting Risks: ', err); + return { count: 0 }; + } +} + +/* FUNCTION FOR GETTING PROCUREMENT MANAGER LIST */ +async function getProcurementManagerList() { + try { + return await db.transaction(async (tx) => { + const managerData = await selectUsers(tx, { + where: eq(users.deptName, '구매'), // 추후 수정 필요 + }); + + return managerData.map((user) => ({ + id: user.id, + name: user.name, + email: user.email, + deptName: user.deptName, + })); + }); + } catch (err) { + console.error('Error in Getting Procurement Manager List: ', err); + return []; + } +} + +// ---------------------------------------------------------------------------------------------------- + +/* FUNCTION FOR MODIFYING RISK EVENTS */ +async function modifyRiskEvents( + id: number, + riskData: Partial, +) { + try { + const currentUserId = await getCurrentUserId(); + + return await db.transaction(async (tx) => { + const modifiedRiskEvent = await updateRiskEvents(tx, id, { + ...riskData, + updatedAt: new Date(), + updatedBy: currentUserId, + }); + + return { ...modifiedRiskEvent }; + }); + } catch (error) { + console.error('Error in Modifying Risk Events: ', error); + throw new Error('Failed to Modify Risk Events'); + } +} + +// ---------------------------------------------------------------------------------------------------- + +/* FUNCTION FOR SENDING RISK EMAIL */ +async function sendRiskEmail( + vendorId: number, + managerId: number, + adminComment: string, + selectedEventTypeMap: Record, + attachment?: { filename: string; content: Buffer }, +) { + try { + // Getting Vendor Information + const { vendorList } = await db.transaction(async (tx) => { + const selectRes = await selectVendors(tx, { + where: eq(vendors.id, vendorId), + }); + + return { vendorList: selectRes }; + }); + if (vendorList.length === 0) { + throw new Error('존재하지 않는 협력업체에요. 다시 한 번 확인해주세요.'); + } + const { vendorName, vendorCode, taxId } = vendorList[0]; + const businessNumber = /^\d{10}$/.test(taxId) + ? `${taxId.slice(0, 3)}-${taxId.slice(3, 5)}-${taxId.slice(5)}` + : taxId; + + // Getting Procurement Manager Information + const procurementManagerList = await db.transaction(async (tx) => { + const managerData = await selectUsers(tx, { + where: eq(users.id, managerId), + }); + + return managerData.map((user) => ({ + id: user.id, + name: user.name, + email: user.email, + deptName: user.deptName, + })); + }); + if (!procurementManagerList || procurementManagerList.length === 0) { + throw new Error('해당하는 구매 담당자가 존재하지 않아요.'); + } + const procurementManager = procurementManagerList[0]; + + // Getting Risk Manager Information + const riskManagerId = await getCurrentUserId(); + const riskManagerList = await db.transaction(async (tx) => { + const managerData = await selectUsers(tx, { + where: eq(users.id, riskManagerId), + }); + + return managerData.map((user) => ({ + id: user.id, + name: user.name, + email: user.email, + deptName: user.deptName, + })); + }); + if (!riskManagerList || riskManagerList.length === 0) { + throw new Error('해당하는 리스크 관리 담당자가 존재하지 않아요.'); + } + const riskManager = riskManagerList[0]; + + // Getting Ranking Information + const ratingList = ['종합등급', '신용등급', '현금흐름등급', 'WATCH등급']; + const { ratingMap } = await db.transaction(async (tx) => { + const selectResList: Record = {}; + + for (const rating of ratingList) { + const selectRes = await selectRiskEvents(tx, { + where: and( + eq(riskEvents.vendorId, vendorId), + eq(riskEvents.eventType, rating), + ), + orderBy: [desc(riskEvents.occuredAt)], + limit: 1, + }); + + selectResList[rating] = selectRes.length > 0 && selectRes[0].content !== null ? selectRes[0].content : '-'; + } + + return { ratingMap: selectResList }; + }); + + const riskItems = Object.entries(selectedEventTypeMap).map(([eventType, items]) => { + const contents = items.map(item => item.content).join(', '); + return { eventType, content: contents }; + }); + + const attachments = attachment + ? [ + { + filename: attachment.filename, + content: attachment.content, + }, + ] + : []; + + await sendEmail({ + to: procurementManager.email, + // from: `"${riskManager.name}" <${riskManager.email}>`, // 추후 수정 필요 + from: `"${riskManager.name}" `, + subject: `[eVCP] 협력업체 리스크 알림 및 관리 요청 - ${vendorName}(${vendorCode}, 사업자등록번호: ${businessNumber})`, + template: 'risks-notification', + context: { + adminComment, + vendorName, + vendorCode, + businessNumber, + ratingTotal: ratingMap['종합등급'], + ratingCredit: ratingMap['신용등급'], + ratingCashflow: ratingMap['현금흐름등급'], + ratingWatch: ratingMap['WATCH등급'], + riskItems, + senderName: riskManager.name, + senderEmail: riskManager.email, + }, + attachments, + }); + } catch (error) { + console.error('Error in Sending Risk Email: ', error); + throw new Error('Failed to Send Risk Email'); + } +} + +// ---------------------------------------------------------------------------------------------------- + +/* FUNCTION FOR GENERATING EXCEL TEMPLATE */ +async function generateRiskEventsTemplate(): Promise { + try { + const workbook = new ExcelJS.Workbook(); + const worksheet = workbook.addWorksheet('협력업체 리스크 입력 템플릿'); + workbook.creator = 'EVCP System'; + workbook.created = new Date(); + + const templateHeaders = [ + { key: 'eventType', header: '항목', width: 30 }, + { key: 'vendorName', header: '협력업체명', width: 30 }, + { key: 'businessNumber', header: '사업자등록번호', width: 30 }, + { key: 'provider', header: '신용평가사', width: 30 }, + { key: 'content', header: '상세 내용', width: 50 }, + { key: 'occuredAt', header: '발생일자', width: 50 }, + ]; + + worksheet.columns = templateHeaders; + + const headerRow = worksheet.getRow(1); + headerRow.font = { bold: true }; + headerRow.fill = { + type: 'pattern', + pattern: 'solid', + fgColor: { argb: 'FFCCCCCC' }, + }; + headerRow.alignment = { horizontal: 'center', vertical: 'middle' }; + + const exampleData = [ + { + eventType: '리스크 항목을 입력하세요.', + vendorName: '협력업체명을 입력하세요.', + businessNumber: '사업자등록번호를 입력하세요.', + provider: '신용평가사를 입력하세요.', + content: '상세 내용을 입력하세요.', + occuredAt: '발생일자를 YYYY-MM-DD형식으로 입력하세요.' + }, + ]; + + exampleData.forEach((row) => { + worksheet.addRow(row); + }); + + const validationSheet = workbook.addWorksheet('ValidationData'); + validationSheet.state = 'hidden'; + validationSheet.getColumn(1).values = ['리스크 항목', ...RISK_EVENT_TYPE_LIST]; + validationSheet.getColumn(2).values = ['신용평가사', ...RISK_PROVIDER_LIST]; + + const eventTypeColIndex = templateHeaders.findIndex(col => col.key === 'eventType') + 1; + const providerColIndex = templateHeaders.findIndex(col => col.key === 'provider') + 1; + + if (eventTypeColIndex > 0) { + (worksheet as any).dataValidations.add(`${worksheet.getColumn(eventTypeColIndex).letter}2:${worksheet.getColumn(eventTypeColIndex).letter}1000`, { + type: 'list', + allowBlank: false, + formulae: [`ValidationData!$A$2:$A$${RISK_EVENT_TYPE_LIST.length + 1}`], + }); + } + + if (providerColIndex > 0) { + (worksheet as any).dataValidations.add(`${worksheet.getColumn(providerColIndex).letter}2:${worksheet.getColumn(providerColIndex).letter}1000`, { + type: 'list', + allowBlank: false, + formulae: [`ValidationData!$B$2:$B$${RISK_PROVIDER_LIST.length + 1}`], + }); + } + + worksheet.eachRow((row) => { + row.eachCell((cell) => { + cell.border = { + top: { style: 'thin' }, + left: { style: 'thin' }, + bottom: { style: 'thin' }, + right: { style: 'thin' }, + }; + }); + }); + + return await workbook.xlsx.writeBuffer() as ArrayBuffer; + } catch (error) { + console.error('Error in generating Excel template:', error); + throw new Error('Failed to generate Excel template'); + } +} + +/* FUNCTION FOR IMPORTING EXCEL FILE */ +async function importRiskEventsExcel(file: File): Promise<{ + errorFile?: Blob; + errorMessage?: string; + successMessage?: string; +}> { + try { + const arrayBuffer = await file.arrayBuffer(); + const workbook = new ExcelJS.Workbook(); + await workbook.xlsx.load(arrayBuffer); + + const worksheet = workbook.getWorksheet(1); + if (!worksheet) { + return { errorMessage: '워크시트를 찾을 수 없어요.' }; + } + const errors: string[] = []; + const importDataList: { + eventType: string; + vendorId: number; + vendorName: string; + businessNumber: string; + provider: string; + content: string; + occuredAt: string; + }[] = []; + + const rows = worksheet.getRows(2, worksheet.rowCount - 1); + + if (!rows) { + return { errorMessage: '새로 추가할 리스크 데이터가 존재하지 않아요.' }; + } + + for (const [index, row] of rows.entries()) { + const rowIndex = index + 2; + try { + const rowData = { + eventType: row.getCell(1).value?.toString()?.trim() || '', + vendorId: 0, + vendorName: row.getCell(2).value?.toString()?.trim() || '', + businessNumber: row.getCell(3).value?.toString()?.trim() || '', + provider: row.getCell(4).value?.toString()?.trim() || '', + content: row.getCell(5).value?.toString()?.trim() || '', + occuredAt : '', + }; + + let occuredAtRaw = row.getCell(6).value; + let occuredAt = ''; + + if (occuredAtRaw instanceof Date) { + const year = occuredAtRaw.getFullYear(); + const month = String(occuredAtRaw.getMonth() + 1).padStart(2, '0'); + const day = String(occuredAtRaw.getDate()).padStart(2, '0'); + occuredAt = `${year}-${month}-${day}`; + } else if (typeof occuredAtRaw === 'number') { + const excelEpoch = new Date(Date.UTC(1899, 11, 30)); + const dateObj = new Date(excelEpoch.getTime() + occuredAtRaw * 86400 * 1000); + const year = dateObj.getUTCFullYear(); + const month = String(dateObj.getUTCMonth() + 1).padStart(2, '0'); + const day = String(dateObj.getUTCDate()).padStart(2, '0'); + occuredAt = `${year}-${month}-${day}`; + } else if (typeof occuredAtRaw === 'string') { + occuredAt = occuredAtRaw.trim(); + } + + rowData.occuredAt = occuredAt || ''; + + if (!rowData.eventType && !rowData.vendorName && !rowData.businessNumber && !rowData.provider && !rowData.occuredAt) { + continue; + } + + if (!rowData.eventType || !rowData.vendorName || !rowData.businessNumber || !rowData.provider || !rowData.occuredAt) { + errors.push(`행 ${rowIndex}: 필수 필드(항목, 협력업체명, 사업자등록번호, 신용평가사, 발생일자)가 누락되었어요.`); + continue; + } + + rowData.businessNumber = rowData.businessNumber.replace(/\D/g, ''); + + if (rowData.businessNumber.length !== 10) { + errors.push(`행 ${rowIndex}: 사업자등록번호는 숫자 10자리여야 해요.`); + continue; + } + + const datePattern = /^\d{4}-\d{2}-\d{2}$/; + if (!datePattern.test(rowData.occuredAt)) { + errors.push(`행 ${rowIndex}: 발생일자는 YYYY-MM-DD 형식이어야 해요.`); + continue; + } + + const dateObj = new Date(rowData.occuredAt); + const [year, month, day] = rowData.occuredAt.split('-').map(Number); + if ( + dateObj.getFullYear() !== year || + dateObj.getMonth() + 1 !== month || + dateObj.getDate() !== day + ) { + errors.push(`행 ${rowIndex}: 발생일자가 올바른 날짜가 아니에요.`); + continue; + } + + const { vendorList } = await db.transaction(async (tx) => { + const selectRes = await selectVendors(tx, { + where: eq(vendors.taxId, rowData.businessNumber), + }); + + return { vendorList: selectRes }; + }); + + if (vendorList.length === 0) { + errors.push(`행 ${rowIndex}: 협력업체로 등록되지 않은 사업자등록번호에요. 다시 한 번 확인해주세요.`); + continue; + } + + rowData.vendorId = vendorList[0].id; + rowData.vendorName = vendorList[0].vendorName; + 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 { vendorId, eventType, provider, content, occuredAt } = importData; + await db.transaction(async (tx) => { + await insertRiskEvents(tx, { + vendorId, + provider, + eventType, + content, + occuredAt: new Date(occuredAt), + createdBy: currentUserId, + updatedBy: currentUserId, + }); + }); + } + + return { successMessage: `Excel 파일이 성공적으로 업로드되었어요. ${importDataList.length}개의 리스크 이벤트가 추가되었어요.` }; + } catch (error) { + console.error('Error in Importing Regular Evaluation Criteria from Excel:', error); + return { errorMessage: 'Excel 파일 처리 중 오류가 발생했어요.' }; + } +} + +// ---------------------------------------------------------------------------------------------------- + +/* EXPORT */ +export { + getProcurementManagerList, + getRisksView, + getRisksViewById, + getRisksViewCount, + getRiskEventsById, + generateRiskEventsTemplate, + importRiskEventsExcel, + modifyRiskEvents, + sendRiskEmail, +}; \ No newline at end of file -- cgit v1.2.3