(고건) 리스크 관리 페이지 추가

1 files changed, 636 insertions, 0 deletions

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<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 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<RiskEvents>,

+) {

+  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<string, RisksView[]>,

+  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<string, string> = {};

+

+      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}" <dujin.kim@dtsolution.co.kr>`,

+      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<ArrayBuffer> {

+  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