(대표님) RFQ-last, tbe-last, 기본계약 템플릿 내 견적,입찰,계약 추가, env.dev NAS_PATH 수정

1 files changed, 247 insertions, 0 deletions

diff --git a/lib/tbe-last/service.ts b/lib/tbe-last/service.ts
new file mode 100644
index 00000000..760f66ac
--- /dev/null
+++ b/lib/tbe-last/service.ts
@@ -0,0 +1,247 @@
+// lib/tbe-last/service.ts
+'use server'
+
+import { unstable_cache } from "next/cache";
+import db from "@/db/db";
+import { and, desc, asc, eq, sql, or, isNull, isNotNull, ne, inArray } from "drizzle-orm";
+import { tbeLastView, tbeDocumentsView } from "@/db/schema";
+import { rfqPrItems } from "@/db/schema/rfqLast";
+import { rfqLastTbeDocumentReviews, rfqLastTbePdftronComments, rfqLastTbeVendorDocuments } from "@/db/schema";
+import { filterColumns } from "@/lib/filter-columns";
+import { GetTBELastSchema } from "./validations";
+
+// ==========================================
+// 1. TBE 세션 목록 조회
+// ==========================================
+export async function getAllTBELast(input: GetTBELastSchema) {
+  return unstable_cache(
+    async () => {
+      // 페이징
+      const offset = ((input.page ?? 1) - 1) * (input.perPage ?? 10);
+      const limit = input.perPage ?? 10;
+
+      // 고급 필터
+      const advancedWhere = filterColumns({
+        table: tbeLastView,
+        filters: input.filters ?? [],
+        joinOperator: input.joinOperator ?? "and",
+      });
+
+      // 글로벌 검색
+      let globalWhere;
+      if (input.search) {
+        const s = `%${input.search}%`;
+        globalWhere = or(
+          sql`${tbeLastView.sessionCode} ILIKE ${s}`,
+          sql`${tbeLastView.rfqCode} ILIKE ${s}`,
+          sql`${tbeLastView.vendorName} ILIKE ${s}`,
+          sql`${tbeLastView.vendorCode} ILIKE ${s}`,
+          sql`${tbeLastView.projectCode} ILIKE ${s}`,
+          sql`${tbeLastView.projectName} ILIKE ${s}`,
+          sql`${tbeLastView.packageNo} ILIKE ${s}`,
+          sql`${tbeLastView.packageName} ILIKE ${s}`
+        );
+      }
+
+      // 최종 WHERE
+      const finalWhere = and(advancedWhere, globalWhere);
+
+      // 정렬
+      const orderBy = input.sort?.length
+        ? input.sort.map((s) => {
+            const col = (tbeLastView as any)[s.id];
+            return s.desc ? desc(col) : asc(col);
+          })
+        : [desc(tbeLastView.createdAt)];
+
+      // 메인 SELECT
+      const [rows, total] = await db.transaction(async (tx) => {
+        const data = await tx
+          .select()
+          .from(tbeLastView)
+          .where(finalWhere)
+          .orderBy(...orderBy)
+          .offset(offset)
+          .limit(limit);
+
+        const [{ count }] = await tx
+          .select({ count: sql<number>`count(*)`.as("count") })
+          .from(tbeLastView)
+          .where(finalWhere);
+
+        return [data, Number(count)];
+      });
+
+      const pageCount = Math.ceil(total / limit);
+      return { data: rows, pageCount };
+    },
+    [JSON.stringify(input)],
+    {
+      revalidate: 60,
+      tags: ["tbe-last-sessions"],
+    }
+  )();
+}
+
+// ==========================================
+// 2. TBE 세션 상세 조회
+// ==========================================
+export async function getTBESessionDetail(sessionId: number) {
+  return unstable_cache(
+    async () => {
+      // 세션 기본 정보
+      const [session] = await db
+        .select()
+        .from(tbeLastView)
+        .where(eq(tbeLastView.tbeSessionId, sessionId))
+        .limit(1);
+
+      if (!session) {
+        return null;
+      }
+
+      // PR 아이템 목록
+      const prItems = await db
+        .select()
+        .from(rfqPrItems)
+        .where(eq(rfqPrItems.rfqsLastId, session.rfqId))
+        .orderBy(desc(rfqPrItems.majorYn), asc(rfqPrItems.prItem));
+
+      // 문서 목록 (구매자 + 벤더)
+      const documents = await db
+        .select()
+        .from(tbeDocumentsView)
+        .where(eq(tbeDocumentsView.tbeSessionId, sessionId))
+        .orderBy(
+          sql`CASE document_source WHEN 'buyer' THEN 0 ELSE 1 END`,
+          asc(tbeDocumentsView.documentName)
+        );
+
+      // PDFTron 코멘트 통계
+      const comments = await db
+        .select({
+          documentReviewId: rfqLastTbePdftronComments.documentReviewId,
+          totalCount: sql<number>`count(*)`.as("total_count"),
+          openCount: sql<number>`sum(case when status = 'open' then 1 else 0 end)`.as("open_count"),
+        })
+        .from(rfqLastTbePdftronComments)
+        .innerJoin(
+          rfqLastTbeDocumentReviews,
+          eq(rfqLastTbePdftronComments.documentReviewId, rfqLastTbeDocumentReviews.id)
+        )
+        .where(eq(rfqLastTbeDocumentReviews.tbeSessionId, sessionId))
+        .groupBy(rfqLastTbePdftronComments.documentReviewId);
+
+      // 문서별 코멘트 수 매핑
+      const commentsByDocumentId = new Map(
+        comments.map(c => [c.documentReviewId, {
+          totalCount: c.totalCount,
+          openCount: c.openCount
+        }])
+      );
+
+      // 문서에 코멘트 정보 추가
+      const documentsWithComments = documents.map(doc => ({
+        ...doc,
+        comments: doc.documentReviewId 
+          ? commentsByDocumentId.get(doc.documentReviewId) || { totalCount: 0, openCount: 0 }
+          : { totalCount: 0, openCount: 0 }
+      }));
+
+      return {
+        session,
+        prItems,
+        documents: documentsWithComments,
+      };
+    },
+    [`tbe-session-${sessionId}`],
+    {
+      revalidate: 60,
+      tags: [`tbe-session-${sessionId}`],
+    }
+  )();
+}
+
+// ==========================================
+// 3. 문서별 PDFTron 코멘트 조회
+// ==========================================
+export async function getDocumentComments(documentReviewId: number) {
+  const comments = await db
+    .select({
+      id: rfqLastTbePdftronComments.id,
+      pdftronAnnotationId: rfqLastTbePdftronComments.pdftronAnnotationId,
+      pageNumber: rfqLastTbePdftronComments.pageNumber,
+      commentText: rfqLastTbePdftronComments.commentText,
+      commentCategory: rfqLastTbePdftronComments.commentCategory,
+      severity: rfqLastTbePdftronComments.severity,
+      status: rfqLastTbePdftronComments.status,
+      createdBy: rfqLastTbePdftronComments.createdBy,
+      createdByType: rfqLastTbePdftronComments.createdByType,
+      createdAt: rfqLastTbePdftronComments.createdAt,
+      resolvedBy: rfqLastTbePdftronComments.resolvedBy,
+      resolvedAt: rfqLastTbePdftronComments.resolvedAt,
+      resolutionNote: rfqLastTbePdftronComments.resolutionNote,
+      replies: rfqLastTbePdftronComments.replies,
+    })
+    .from(rfqLastTbePdftronComments)
+    .where(eq(rfqLastTbePdftronComments.documentReviewId, documentReviewId))
+    .orderBy(asc(rfqLastTbePdftronComments.pageNumber), desc(rfqLastTbePdftronComments.createdAt));
+
+  return comments;
+}
+
+// ==========================================
+// 4. TBE 평가 결과 업데이트
+// ==========================================
+export async function updateTBEEvaluation(
+  sessionId: number,
+  data: {
+    evaluationResult: "pass" | "conditional_pass" | "non_pass";
+    conditionalRequirements?: string;
+    technicalSummary?: string;
+    commercialSummary?: string;
+    overallRemarks?: string;
+  }
+) {
+  // 실제 업데이트 로직
+  // await db.update(rfqLastTbeSessions)...
+  
+  // 캐시 무효화
+  return { success: true };
+}
+
+// ==========================================
+// 5. 벤더 문서 업로드
+// ==========================================
+export async function uploadVendorDocument(
+  sessionId: number,
+  file: {
+    fileName: string;
+    originalFileName: string;
+    filePath: string;
+    fileSize: number;
+    fileType: string;
+    documentType: string;
+    description?: string;
+  }
+) {
+  const [document] = await db
+    .insert(rfqLastTbeVendorDocuments)
+    .values({
+      tbeSessionId: sessionId,
+      documentType: file.documentType as any,
+      fileName: file.fileName,
+      originalFileName: file.originalFileName,
+      filePath: file.filePath,
+      fileSize: file.fileSize,
+      fileType: file.fileType,
+      description: file.description,
+      reviewRequired: true,
+      reviewStatus: "pending",
+      submittedBy: 1, // TODO: 실제 사용자 ID
+      submittedAt: new Date(),
+    })
+    .returning();
+
+  return document;
+}
\ No newline at end of file