(김준회) SWP 문서 업로드 (Submisssion) 초기 개발건

1 files changed, 273 insertions, 0 deletions

diff --git a/lib/swp/vendor-actions.ts b/lib/swp/vendor-actions.ts
new file mode 100644
index 00000000..7d6dfa85
--- /dev/null
+++ b/lib/swp/vendor-actions.ts
@@ -0,0 +1,273 @@
+"use server";
+
+import { getServerSession } from "next-auth";
+import { authOptions } from "@/app/api/auth/[...nextauth]/route";
+import db from "@/db/db";
+import { vendors } from "@/db/schema/vendors";
+import { contracts } from "@/db/schema/contract";
+import { projects } from "@/db/schema/projects";
+import { swpDocumentFiles, swpDocumentRevisions } from "@/db/schema/SWP/swp-documents";
+import { eq, and, sql } from "drizzle-orm";
+import { fetchSwpDocuments, type SwpTableParams } from "./actions";
+
+// ============================================================================
+// 벤더 세션 정보 조회
+// ============================================================================
+
+interface VendorSessionInfo {
+  vendorId: number;
+  vendorCode: string;
+  vendorName: string;
+  companyId: number;
+}
+
+export async function getVendorSessionInfo(): Promise<VendorSessionInfo | null> {
+  const session = await getServerSession(authOptions);
+  
+  if (!session?.user?.companyId) {
+    return null;
+  }
+
+  const companyId = typeof session.user.companyId === 'string' 
+    ? parseInt(session.user.companyId, 10)
+    : session.user.companyId as number;
+
+  // vendors 테이블에서 companyId로 벤더 정보 조회
+  const vendor = await db
+    .select({
+      id: vendors.id,
+      vendorCode: vendors.vendorCode,
+      vendorName: vendors.vendorName,
+    })
+    .from(vendors)
+    .where(eq(vendors.id, companyId))
+    .limit(1);
+
+  if (!vendor[0] || !vendor[0].vendorCode) {
+    return null;
+  }
+
+  return {
+    vendorId: vendor[0].id,
+    vendorCode: vendor[0].vendorCode,
+    vendorName: vendor[0].vendorName,
+    companyId,
+  };
+}
+
+// ============================================================================
+// 벤더의 프로젝트 목록 조회
+// ============================================================================
+
+export async function fetchVendorProjects() {
+  try {
+    const vendorInfo = await getVendorSessionInfo();
+    
+    if (!vendorInfo) {
+      throw new Error("벤더 정보를 찾을 수 없습니다.");
+    }
+
+    // contracts 테이블에서 해당 벤더의 계약들의 프로젝트 조회
+    const vendorProjects = await db
+      .selectDistinct({
+        PROJ_NO: projects.code,
+        PROJ_NM: projects.name,
+        contract_count: sql<number>`COUNT(DISTINCT ${contracts.id})::int`,
+      })
+      .from(contracts)
+      .innerJoin(projects, eq(contracts.projectId, projects.id))
+      .where(eq(contracts.vendorId, vendorInfo.vendorId))
+      .groupBy(projects.id, projects.code, projects.name)
+      .orderBy(sql`COUNT(DISTINCT ${contracts.id}) DESC`);
+
+    return vendorProjects;
+  } catch (error) {
+    console.error("[fetchVendorProjects] 오류:", error);
+    return [];
+  }
+}
+
+// ============================================================================
+// 벤더 필터링된 문서 목록 조회
+// ============================================================================
+
+export async function fetchVendorDocuments(params: SwpTableParams) {
+  try {
+    const vendorInfo = await getVendorSessionInfo();
+    
+    if (!vendorInfo) {
+      throw new Error("벤더 정보를 찾을 수 없습니다.");
+    }
+
+    // 벤더 코드를 필터에 자동 추가
+    const vendorParams: SwpTableParams = {
+      ...params,
+      filters: {
+        ...params.filters,
+        vndrCd: vendorInfo.vendorCode, // 벤더 코드 필터 강제 적용
+      },
+    };
+
+    // 기존 fetchSwpDocuments 재사용
+    return await fetchSwpDocuments(vendorParams);
+  } catch (error) {
+    console.error("[fetchVendorDocuments] 오류:", error);
+    throw new Error("문서 목록 조회 실패");
+  }
+}
+
+// ============================================================================
+// 파일 업로드
+// ============================================================================
+
+export interface FileUploadParams {
+  revisionId: number;
+  file: {
+    FILE_NM: string;
+    FILE_SEQ: string;
+    FILE_SZ: string;
+    FLD_PATH: string;
+    STAT?: string;
+    STAT_NM?: string;
+  };
+}
+
+export async function uploadFileToRevision(params: FileUploadParams) {
+  try {
+    const vendorInfo = await getVendorSessionInfo();
+    
+    if (!vendorInfo) {
+      throw new Error("벤더 정보를 찾을 수 없습니다.");
+    }
+
+    const { revisionId, file } = params;
+
+    // 1. 해당 리비전이 벤더에게 제공된 문서인지 확인
+    const revisionCheck = await db
+      .select({
+        DOC_NO: swpDocumentRevisions.DOC_NO,
+        VNDR_CD: sql<string>`(
+          SELECT d."VNDR_CD" 
+          FROM swp.swp_documents d 
+          WHERE d."DOC_NO" = ${swpDocumentRevisions.DOC_NO}
+        )`,
+      })
+      .from(swpDocumentRevisions)
+      .where(eq(swpDocumentRevisions.id, revisionId))
+      .limit(1);
+
+    if (!revisionCheck[0]) {
+      throw new Error("리비전을 찾을 수 없습니다.");
+    }
+
+    // 벤더 코드가 일치하는지 확인
+    if (revisionCheck[0].VNDR_CD !== vendorInfo.vendorCode) {
+      throw new Error("이 문서에 대한 권한이 없습니다.");
+    }
+
+    // 2. 파일 정보 저장 (upsert)
+    const existingFile = await db
+      .select({ id: swpDocumentFiles.id })
+      .from(swpDocumentFiles)
+      .where(
+        and(
+          eq(swpDocumentFiles.revision_id, revisionId),
+          eq(swpDocumentFiles.FILE_SEQ, file.FILE_SEQ)
+        )
+      )
+      .limit(1);
+
+    if (existingFile[0]) {
+      // 업데이트
+      await db.execute(sql`
+        UPDATE swp.swp_document_files
+        SET 
+          "FILE_NM" = ${file.FILE_NM},
+          "FILE_SZ" = ${file.FILE_SZ},
+          "FLD_PATH" = ${file.FLD_PATH},
+          "STAT" = ${file.STAT || null},
+          "STAT_NM" = ${file.STAT_NM || null},
+          sync_status = 'synced',
+          updated_at = NOW()
+        WHERE id = ${existingFile[0].id}
+      `);
+
+      return { success: true, fileId: existingFile[0].id, action: "updated" };
+    } else {
+      // 삽입
+      const result = await db.execute<{ id: number }>(sql`
+        INSERT INTO swp.swp_document_files 
+          (revision_id, "FILE_NM", "FILE_SEQ", "FILE_SZ", "FLD_PATH", "STAT", "STAT_NM", sync_status)
+        VALUES 
+          (${revisionId}, ${file.FILE_NM}, ${file.FILE_SEQ}, ${file.FILE_SZ}, ${file.FLD_PATH}, ${file.STAT || null}, ${file.STAT_NM || null}, 'synced')
+        RETURNING id
+      `);
+
+      return { success: true, fileId: result.rows[0].id, action: "created" };
+    }
+  } catch (error) {
+    console.error("[uploadFileToRevision] 오류:", error);
+    throw new Error(
+      error instanceof Error ? error.message : "파일 업로드 실패"
+    );
+  }
+}
+
+// ============================================================================
+// 벤더 통계 조회
+// ============================================================================
+
+export async function fetchVendorSwpStats(projNo?: string) {
+  try {
+    const vendorInfo = await getVendorSessionInfo();
+    
+    if (!vendorInfo) {
+      throw new Error("벤더 정보를 찾을 수 없습니다.");
+    }
+
+    const whereConditions = [
+      sql`d."VNDR_CD" = ${vendorInfo.vendorCode}`,
+    ];
+
+    if (projNo) {
+      whereConditions.push(sql`d."PROJ_NO" = ${projNo}`);
+    }
+
+    const stats = await db.execute<{
+      total_documents: number;
+      total_revisions: number;
+      total_files: number;
+      uploaded_files: number;
+      last_sync: Date | null;
+    }>(sql`
+      SELECT 
+        COUNT(DISTINCT d."DOC_NO")::int as total_documents,
+        COUNT(DISTINCT r.id)::int as total_revisions,
+        COUNT(f.id)::int as total_files,
+        COUNT(CASE WHEN f."FLD_PATH" IS NOT NULL AND f."FLD_PATH" != '' THEN 1 END)::int as uploaded_files,
+        MAX(d.last_synced_at) as last_sync
+      FROM swp.swp_documents d
+      LEFT JOIN swp.swp_document_revisions r ON d."DOC_NO" = r."DOC_NO"
+      LEFT JOIN swp.swp_document_files f ON r.id = f.revision_id
+      WHERE ${sql.join(whereConditions, sql` AND `)}
+    `);
+
+    return stats.rows[0] || {
+      total_documents: 0,
+      total_revisions: 0,
+      total_files: 0,
+      uploaded_files: 0,
+      last_sync: null,
+    };
+  } catch (error) {
+    console.error("[fetchVendorSwpStats] 오류:", error);
+    return {
+      total_documents: 0,
+      total_revisions: 0,
+      total_files: 0,
+      uploaded_files: 0,
+      last_sync: null,
+    };
+  }
+}
+