(임수민) 법무검토 요청 데이터 조회 수정

1 files changed, 265 insertions, 84 deletions

diff --git a/lib/basic-contract/service.ts b/lib/basic-contract/service.ts
index 55ac149e..6ae3c237 100644
--- a/lib/basic-contract/service.ts
+++ b/lib/basic-contract/service.ts
@@ -4,7 +4,7 @@ import { revalidateTag, unstable_noStore } from "next/cache";
 import db from "@/db/db";
 import { getErrorMessage } from "@/lib/handle-error";
 import { unstable_cache } from "@/lib/unstable-cache";
-import { asc, desc, ilike, inArray, and, gte, lte, not, or, sql, eq, isNull, ne, isNotNull, count,like } from "drizzle-orm";
+import { asc, desc, ilike, inArray, and, gte, lte, not, or, sql, eq, isNull, ne, isNotNull, count,like, SQL } from "drizzle-orm";
 import { v4 as uuidv4 } from "uuid";
 import {
   basicContract,
@@ -70,6 +70,7 @@ import { deleteFile, saveBuffer, saveFile, saveDRMFile } from "@/lib/file-stroag
 import { decryptWithServerAction } from "@/components/drm/drmUtils";
 import { getServerSession } from "next-auth/next"
 import { authOptions } from "@/app/api/auth/[...nextauth]/route"
+import { getSSLVWPurInqReqByRegNo } from "./sslvw-service";
 
 
 // 템플릿 추가
@@ -858,6 +859,7 @@ export async function getBasicContractsByTemplateId(
 ) {
   // return unstable_cache(
   //   async () => {
+  // 법무검토 요청 데이터 조회 하는 오라클 sql 문에서 id 연결한 상태값 가져와서 
       try {
 
         console.log(input.sort)
@@ -906,22 +908,29 @@ export async function getBasicContractsByTemplateId(
             )
             : [asc(basicContractView.createdAt)];
 
-        // 트랜잭션 내부에서 Repository 호출
-        const { data, total } = await db.transaction(async (tx) => {
-          const data = await selectBasicContractsById(tx, {
-            where,
-            orderBy,
-            offset,
-            limit: input.perPage,
-          });
+        const queryOptions = {
+          where,
+          orderBy,
+          offset,
+          limit: input.perPage,
+        }
 
-          const total = await countBasicContractsById(tx, where);
-          return { data, total };
-        });
+        const initialResult = await loadBasicContractsByTemplate(queryOptions);
+        let finalData = initialResult.data;
+        let finalPageCount = Math.ceil(initialResult.total / input.perPage);
 
-        const pageCount = Math.ceil(total / input.perPage);
+        try {
+          const synced = await syncLegalReviewStatusesForContracts(finalData);
+          if (synced) {
+            const refreshedResult = await loadBasicContractsByTemplate(queryOptions);
+            finalData = refreshedResult.data;
+            finalPageCount = Math.ceil(refreshedResult.total / input.perPage);
+          }
+        } catch (syncError) {
+          console.error('[getBasicContractsByTemplateId] 법무 상태 동기화 실패:', syncError);
+        }
 
-        return { data, pageCount };
+        return { data: finalData, pageCount: finalPageCount };
       } catch (err) {
         // 에러 발생 시 디폴트\
         console.log(err)
@@ -936,6 +945,75 @@ export async function getBasicContractsByTemplateId(
   // )();
 }
 
+const extractStatusText = (value: unknown): string => {
+  if (typeof value === 'string') {
+    return value.trim()
+  }
+  return ''
+}
+
+async function syncLegalReviewStatusesForContracts(
+  contracts: Array<{
+    id: number
+    legalReviewRegNo: string | null
+    legalReviewProgressStatus?: string | null
+  }>
+): Promise<boolean> {
+  const targets = contracts.filter((contract) => contract.legalReviewRegNo)
+  if (targets.length === 0) {
+    return false
+  }
+
+  let hasUpdates = false
+
+  for (const contract of targets) {
+    const regNo = contract.legalReviewRegNo!
+    const { success, data } = await getSSLVWPurInqReqByRegNo(regNo)
+
+    if (!success || !data) {
+      continue
+    }
+
+    const latestStatus = extractStatusText(data.PRGS_STAT_DSC ?? data.prgs_stat_dsc)
+    if (!latestStatus) {
+      continue
+    }
+
+    if (latestStatus === extractStatusText(contract.legalReviewProgressStatus)) {
+      continue
+    }
+
+    await persistLegalReviewStatus({
+      contractId: contract.id,
+      regNo,
+      progressStatus: latestStatus,
+    })
+
+    hasUpdates = true
+  }
+
+  return hasUpdates
+}
+
+async function loadBasicContractsByTemplate(options: {
+  where: SQL<unknown> | undefined
+  orderBy: SQL<unknown>[]
+  offset: number
+  limit: number
+}) {
+  return db.transaction(async (tx) => {
+    const data = await selectBasicContractsById(tx, {
+      where: options.where,
+      orderBy: options.orderBy,
+      offset: options.offset,
+      limit: options.limit,
+    });
+
+    const total = await countBasicContractsById(tx, options.where);
+    return { data, total };
+  });
+}
+
 export async function getAllTemplates(): Promise<BasicContractTemplate[]> {
   try {
     return await findAllTemplates();
@@ -2784,6 +2862,47 @@ export async function requestLegalReviewAction(
   }
 }
 
+const persistLegalReviewStatus = async ({
+  contractId,
+  regNo,
+  progressStatus,
+}: {
+  contractId: number
+  regNo: string
+  progressStatus: string
+}) => {
+  const now = new Date()
+
+  await db.transaction(async (tx) => {
+    await tx
+      .update(basicContract)
+      .set({
+        legalReviewRegNo: regNo,
+        legalReviewProgressStatus: progressStatus,
+        updatedAt: now,
+      })
+      .where(eq(basicContract.id, contractId))
+
+    const existingLegalWork = await tx
+      .select({ id: legalWorks.id })
+      .from(legalWorks)
+      .where(eq(legalWorks.basicContractId, contractId))
+      .limit(1)
+
+    if (existingLegalWork[0]) {
+      await tx
+        .update(legalWorks)
+        .set({
+          status: progressStatus,
+          updatedAt: now,
+        })
+        .where(eq(legalWorks.id, existingLegalWork[0].id))
+    }
+  })
+
+  revalidateTag("basic-contracts")
+}
+
 /**
  * SSLVW 데이터로부터 법무검토 상태 업데이트
  * @param sslvwData 선택된 SSLVW 데이터 배열
@@ -2791,7 +2910,7 @@ export async function requestLegalReviewAction(
  * @returns 성공 여부 및 메시지
  */
 export async function updateLegalReviewStatusFromSSLVW(
-  sslvwData: Array<{ VEND_CD?: string; PRGS_STAT_DSC?: string; [key: string]: any }>,
+  sslvwData: Array<{ REG_NO?: string; reg_no?: string; PRGS_STAT_DSC?: string; prgs_stat_dsc?: string; [key: string]: any }>,
   selectedContractIds: number[]
 ): Promise<{ success: boolean; message: string; updatedCount: number; errors: string[] }> {
   try {
@@ -2815,90 +2934,92 @@ export async function updateLegalReviewStatusFromSSLVW(
       }
     }
 
-    // 선택된 계약서 정보 조회
-    const selectedContracts = await db
-      .select({
-        id: basicContractView.id,
-        vendorCode: basicContractView.vendorCode,
-        legalReviewStatus: basicContractView.legalReviewStatus
-      })
-      .from(basicContractView)
-      .where(inArray(basicContractView.id, selectedContractIds))
-
-    let updatedCount = 0
-    const errors: string[] = []
-
-    // 각 SSLVW 데이터에 대해 처리
-    for (const sslvwItem of sslvwData) {
-      const vendorCode = sslvwItem.VEND_CD || sslvwItem.vendorCode
-      const prgsStatDsc = sslvwItem.PRGS_STAT_DSC || sslvwItem.prgsStatDsc
+    if (selectedContractIds.length !== 1) {
+      return {
+        success: false,
+        message: '한 개의 계약서만 선택해 주세요.',
+        updatedCount: 0,
+        errors: []
+      }
+    }
 
-      if (!vendorCode || !prgsStatDsc) {
-        errors.push(`벤더코드 또는 상태 정보가 없습니다: ${JSON.stringify(sslvwItem)}`)
-        continue
+    if (sslvwData.length !== 1) {
+      return {
+        success: false,
+        message: '법무 시스템 데이터도 한 건만 선택해 주세요.',
+        updatedCount: 0,
+        errors: []
       }
+    }
 
-      // 해당 벤더의 선택된 계약서들 찾기
-      const contractsToUpdate = selectedContracts.filter(contract =>
-        contract.vendorCode === vendorCode
-      )
+    const contractId = selectedContractIds[0]
+    const sslvwItem = sslvwData[0]
+    const regNo = String(
+      sslvwItem.REG_NO ??
+      sslvwItem.reg_no ??
+      sslvwItem.RegNo ??
+      ''
+    ).trim()
+    const progressStatus = String(
+      sslvwItem.PRGS_STAT_DSC ??
+      sslvwItem.prgs_stat_dsc ??
+      sslvwItem.PrgsStatDsc ??
+      ''
+    ).trim()
 
-      if (contractsToUpdate.length === 0) {
-        console.log(`벤더 ${vendorCode}의 선택된 계약서가 없음`)
-        continue
+    if (!regNo) {
+      return {
+        success: false,
+        message: 'REG_NO 값을 찾을 수 없습니다.',
+        updatedCount: 0,
+        errors: []
       }
+    }
 
-      // PRGS_STAT_DSC를 legalWorks.status로 매핑
-      const statusMapping: Record<string, string> = {
-        '신규등록': '신규등록',
-        '검토요청': '검토요청',
-        '담당자배정': '담당자배정',
-        '검토중': '검토중',
-        '답변완료': '답변완료',
-        '재검토요청': '재검토요청',
-        '보류': '보류',
-        '취소': '취소'
+    if (!progressStatus) {
+      return {
+        success: false,
+        message: 'PRGS_STAT_DSC 값을 찾을 수 없습니다.',
+        updatedCount: 0,
+        errors: []
       }
+    }
 
-      const mappedStatus = statusMapping[prgsStatDsc] || prgsStatDsc
+    const contract = await db
+      .select({
+        id: basicContract.id,
+        legalReviewRegNo: basicContract.legalReviewRegNo,
+      })
+      .from(basicContract)
+      .where(eq(basicContract.id, contractId))
+      .limit(1)
 
-      // 각 계약서의 legalWorks 상태 업데이트
-      for (const contract of contractsToUpdate) {
-        try {
-          const updateResult = await db
-            .update(legalWorks)
-            .set({
-              status: mappedStatus,
-              updatedAt: new Date()
-            })
-            .where(eq(legalWorks.basicContractId, contract.id))
-            .returning({ id: legalWorks.id })
-
-          if (updateResult.length > 0) {
-            console.log(`법무작업 상태 업데이트: 계약서 ${contract.id}, 상태 ${mappedStatus}`)
-            updatedCount++
-          } else {
-            console.log(`법무작업 레코드 없음: 계약서 ${contract.id}`)
-            errors.push(`계약서 ${contract.id}: 법무작업 레코드가 없습니다`)
-          }
-        } catch (contractError) {
-          console.error(`계약서 ${contract.id} 상태 업데이트 실패:`, contractError)
-          errors.push(`계약서 ${contract.id}: 업데이트 실패`)
-        }
+    if (!contract[0]) {
+      return {
+        success: false,
+        message: `계약서(${contractId})를 찾을 수 없습니다.`,
+        updatedCount: 0,
+        errors: []
       }
     }
 
-    const message = updatedCount > 0
-      ? `${updatedCount}건의 계약서 법무검토 상태가 업데이트되었습니다.`
-      : '업데이트된 계약서가 없습니다.'
+    if (contract[0].legalReviewRegNo && contract[0].legalReviewRegNo !== regNo) {
+      console.warn(`[updateLegalReviewStatusFromSSLVW] REG_NO가 변경됩니다: ${contract[0].legalReviewRegNo} -> ${regNo}`)
+    }
+
+    await persistLegalReviewStatus({
+      contractId,
+      regNo,
+      progressStatus,
+    })
 
-    console.log(`[updateLegalReviewStatusFromSSLVW] 완료: ${message}`)
+    console.log(`[updateLegalReviewStatusFromSSLVW] 완료: 계약서 ${contractId}, REG_NO ${regNo}, 상태 ${progressStatus}`)
 
     return {
-      success: updatedCount > 0,
-      message,
-      updatedCount,
-      errors
+      success: true,
+      message: '법무검토 상태가 업데이트되었습니다.',
+      updatedCount: 1,
+      errors: []
     }
 
   } catch (error) {
@@ -2912,6 +3033,66 @@ export async function updateLegalReviewStatusFromSSLVW(
   }
 }
 
+export async function refreshLegalReviewStatusFromOracle(contractId: number): Promise<{
+  success: boolean
+  message: string
+  updated?: boolean
+}> {
+  try {
+    const contract = await db
+      .select({
+        id: basicContract.id,
+        legalReviewRegNo: basicContract.legalReviewRegNo,
+        legalReviewProgressStatus: basicContract.legalReviewProgressStatus,
+      })
+      .from(basicContract)
+      .where(eq(basicContract.id, contractId))
+      .limit(1)
+
+    if (!contract[0]) {
+      return { success: false, message: '계약서를 찾을 수 없습니다.' }
+    }
+
+    if (!contract[0].legalReviewRegNo) {
+      return { success: false, message: '연결된 REG_NO가 없습니다.' }
+    }
+
+    const { success, data, error } = await getSSLVWPurInqReqByRegNo(contract[0].legalReviewRegNo)
+
+    if (!success || !data) {
+      return { success: false, message: error ?? '법무 시스템 데이터를 찾을 수 없습니다.' }
+    }
+
+    const latestStatus = String(
+      data.PRGS_STAT_DSC ??
+      data.prgs_stat_dsc ??
+      ''
+    ).trim()
+
+    if (!latestStatus) {
+      return { success: false, message: '법무 시스템 상태 정보를 찾을 수 없습니다.' }
+    }
+
+    if (contract[0].legalReviewProgressStatus === latestStatus) {
+      return { success: true, message: '변경된 상태가 없습니다.', updated: false }
+    }
+
+    await persistLegalReviewStatus({
+      contractId,
+      regNo: contract[0].legalReviewRegNo,
+      progressStatus: latestStatus,
+    })
+
+    return { success: true, message: '법무검토 상태가 최신 정보로 업데이트되었습니다.', updated: true }
+  } catch (error) {
+    console.error('[refreshLegalReviewStatusFromOracle] 오류:', error)
+    return {
+      success: false,
+      message: error instanceof Error ? error.message : '법무검토 상태 새로고침 중 오류가 발생했습니다.'
+    }
+  }
+}
+
 export async function resendContractsAction(contractIds: number[]) {
   try {
     // 세션 확인