diff options
Diffstat (limited to 'lib/swp/sync-service.ts')
| -rw-r--r-- | lib/swp/sync-service.ts | 537 |
1 files changed, 0 insertions, 537 deletions
diff --git a/lib/swp/sync-service.ts b/lib/swp/sync-service.ts deleted file mode 100644 index 787b28ae..00000000 --- a/lib/swp/sync-service.ts +++ /dev/null @@ -1,537 +0,0 @@ -"use server"; - -import db from "@/db/db"; -import { eq, and, sql } from "drizzle-orm"; -import { - swpDocuments, - swpDocumentRevisions, - swpDocumentFiles, - type SwpDocumentInsert, - type SwpDocumentRevisionInsert, - type SwpDocumentFileInsert, -} from "@/db/schema/SWP/swp-documents"; - -// ============================================================================ -// API 응답 타입 정의 -// ============================================================================ - -export interface SwpDocumentApiResponse { - // 필수 필드 - DOC_NO: string; - DOC_TITLE: string; - PROJ_NO: string; - CPY_CD: string; - CPY_NM: string; - PIC_NM: string; - PIC_DEPTNM: string; - SKL_CD: string; - CRTER: string; - CRTE_DTM: string; - CHGR: string; - CHG_DTM: string; - - // 선택적 필드 (null 가능) - DOC_GB: string | null; - DOC_TYPE: string | null; - OWN_DOC_NO: string | null; - SHI_DOC_NO: string | null; - PROJ_NM: string | null; - PKG_NO: string | null; - MAT_CD: string | null; - MAT_NM: string | null; - DISPLN: string | null; - CTGRY: string | null; - VNDR_CD: string | null; - PIC_DEPTCD: string | null; - LTST_REV_NO: string | null; - LTST_REV_SEQ: string | null; - LTST_ACTV_STAT: string | null; - STAGE: string | null; - MOD_TYPE: string | null; - ACT_TYPE_NM: string | null; - USE_YN: string | null; - REV_DTM: string | null; -} - -export interface SwpFileApiResponse { - // 필수 필드 - OWN_DOC_NO: string; - REV_NO: string; - STAGE: string; - FILE_NM: string; - FILE_SEQ: string; - CRTER: string; - CRTE_DTM: string; - CHGR: string; - CHG_DTM: string; - - // 선택적 필드 (null 가능) - FILE_SZ: string | null; - FLD_PATH: string | null; - ACTV_NO: string | null; - ACTV_SEQ: string | null; - BOX_SEQ: string | null; - OFDC_NO: string | null; - PROJ_NO: string | null; - PKG_NO: string | null; - VNDR_CD: string | null; - CPY_CD: string | null; - STAT: string | null; - STAT_NM: string | null; - IDX: string | null; -} - -// ============================================================================ -// 동기화 결과 타입 -// ============================================================================ - -export interface SyncResult { - success: boolean; - projectNo: string; - stats: { - documents: { - total: number; - inserted: number; - updated: number; - }; - revisions: { - total: number; - inserted: number; - updated: number; - }; - files: { - total: number; - inserted: number; - updated: number; - }; - }; - errors: string[]; - duration: number; -} - -// ============================================================================ -// 동기화 메인 함수 -// ============================================================================ - -export async function syncSwpProject( - projectNo: string, - documents: SwpDocumentApiResponse[], - files: SwpFileApiResponse[] -): Promise<SyncResult> { - const startTime = Date.now(); - const errors: string[] = []; - const stats = { - documents: { total: 0, inserted: 0, updated: 0 }, - revisions: { total: 0, inserted: 0, updated: 0 }, - files: { total: 0, inserted: 0, updated: 0 }, - }; - - try { - // 트랜잭션으로 일괄 처리 - await db.transaction(async (tx) => { - // 1. 문서 동기화 - console.log(`[SYNC] 문서 동기화 시작: ${documents.length}개`); - for (const doc of documents) { - try { - const result = await upsertDocument(tx, doc); - stats.documents.total++; - if (result.inserted) stats.documents.inserted++; - if (result.updated) stats.documents.updated++; - } catch (error) { - errors.push( - `문서 ${doc.DOC_NO} 동기화 실패: ${error instanceof Error ? error.message : String(error)}` - ); - } - } - - // 2. 리비전별로 파일 그룹핑 - const revisionMap = new Map<string, SwpFileApiResponse[]>(); - for (const file of files) { - const key = `${file.OWN_DOC_NO}|${file.REV_NO}`; - if (!revisionMap.has(key)) { - revisionMap.set(key, []); - } - revisionMap.get(key)!.push(file); - } - - // 3. 리비전 및 파일 동기화 - console.log(`[SYNC] 리비전 동기화 시작: ${revisionMap.size}개`); - for (const [key, revFiles] of revisionMap) { - const [docNo, revNo] = key.split("|"); - const firstFile = revFiles[0]; - - try { - // 리비전 생성/업데이트 - const revisionResult = await upsertRevision(tx, docNo, firstFile); - stats.revisions.total++; - if (revisionResult.inserted) stats.revisions.inserted++; - if (revisionResult.updated) stats.revisions.updated++; - - const revisionId = revisionResult.id; - - // 파일들 생성/업데이트 - for (const file of revFiles) { - try { - const fileResult = await upsertFile(tx, revisionId, docNo, file); - stats.files.total++; - if (fileResult.inserted) stats.files.inserted++; - if (fileResult.updated) stats.files.updated++; - } catch (error) { - errors.push( - `파일 ${file.FILE_NM} 동기화 실패: ${error instanceof Error ? error.message : String(error)}` - ); - } - } - } catch (error) { - errors.push( - `리비전 ${docNo}-${revNo} 동기화 실패: ${error instanceof Error ? error.message : String(error)}` - ); - } - } - - console.log( - `[SYNC] 동기화 완료: 문서 ${stats.documents.total}, 리비전 ${stats.revisions.total}, 파일 ${stats.files.total}` - ); - }); - - return { - success: errors.length === 0, - projectNo, - stats, - errors, - duration: Date.now() - startTime, - }; - } catch (error) { - return { - success: false, - projectNo, - stats, - errors: [ - ...errors, - `트랜잭션 실패: ${error instanceof Error ? error.message : String(error)}`, - ], - duration: Date.now() - startTime, - }; - } -} - -// ============================================================================ -// Upsert 헬퍼 함수들 -// ============================================================================ - -async function upsertDocument( - tx: any, - doc: SwpDocumentApiResponse -): Promise<{ id: string; inserted: boolean; updated: boolean }> { - const data: SwpDocumentInsert = { - DOC_NO: doc.DOC_NO, - PROJ_NO: doc.PROJ_NO, - DOC_TITLE: doc.DOC_TITLE, - DOC_GB: doc.DOC_GB || null, - DOC_TYPE: doc.DOC_TYPE || null, - OWN_DOC_NO: doc.OWN_DOC_NO || null, - SHI_DOC_NO: doc.SHI_DOC_NO || null, - PROJ_NM: doc.PROJ_NM || null, - PKG_NO: doc.PKG_NO || null, - MAT_CD: doc.MAT_CD || null, - MAT_NM: doc.MAT_NM || null, - DISPLN: doc.DISPLN || null, - CTGRY: doc.CTGRY || null, - VNDR_CD: doc.VNDR_CD || null, - CPY_CD: doc.CPY_CD, - CPY_NM: doc.CPY_NM, - PIC_NM: doc.PIC_NM, - PIC_DEPTCD: doc.PIC_DEPTCD || null, - PIC_DEPTNM: doc.PIC_DEPTNM, - LTST_REV_NO: doc.LTST_REV_NO || null, - LTST_REV_SEQ: doc.LTST_REV_SEQ || null, - LTST_ACTV_STAT: doc.LTST_ACTV_STAT || null, - STAGE: doc.STAGE || null, - SKL_CD: doc.SKL_CD, - MOD_TYPE: doc.MOD_TYPE || null, - ACT_TYPE_NM: doc.ACT_TYPE_NM || null, - USE_YN: doc.USE_YN || null, - CRTER: doc.CRTER, - CRTE_DTM: doc.CRTE_DTM, - CHGR: doc.CHGR, - CHG_DTM: doc.CHG_DTM, - REV_DTM: doc.REV_DTM || null, - sync_status: "synced", - last_synced_at: new Date(), - updated_at: new Date(), - }; - - // 기존 문서 확인 (복합키: DOC_NO + PROJ_NO) - const existing = await tx - .select() - .from(swpDocuments) - .where( - and( - eq(swpDocuments.DOC_NO, doc.DOC_NO), - eq(swpDocuments.PROJ_NO, doc.PROJ_NO) - ) - ) - .limit(1); - - if (existing.length > 0) { - // 업데이트 - await tx - .update(swpDocuments) - .set(data) - .where( - and( - eq(swpDocuments.DOC_NO, doc.DOC_NO), - eq(swpDocuments.PROJ_NO, doc.PROJ_NO) - ) - ); - return { id: `${doc.DOC_NO}|${doc.PROJ_NO}`, inserted: false, updated: true }; - } else { - // 삽입 - await tx.insert(swpDocuments).values(data); - return { id: `${doc.DOC_NO}|${doc.PROJ_NO}`, inserted: true, updated: false }; - } -} - -async function upsertRevision( - tx: any, - docNo: string, - file: SwpFileApiResponse -): Promise<{ id: number; inserted: boolean; updated: boolean }> { - const data: Omit<SwpDocumentRevisionInsert, "id"> = { - DOC_NO: docNo, - REV_NO: file.REV_NO, - STAGE: file.STAGE, - ACTV_NO: file.ACTV_NO || null, - ACTV_SEQ: file.ACTV_SEQ || null, - BOX_SEQ: file.BOX_SEQ || null, - OFDC_NO: file.OFDC_NO || null, - PROJ_NO: file.PROJ_NO || null, - PKG_NO: file.PKG_NO || null, - VNDR_CD: file.VNDR_CD || null, - CPY_CD: file.CPY_CD || null, - sync_status: "synced", - last_synced_at: new Date(), - updated_at: new Date(), - }; - - // 기존 리비전 확인 - const existing = await tx - .select() - .from(swpDocumentRevisions) - .where( - and( - eq(swpDocumentRevisions.DOC_NO, docNo), - eq(swpDocumentRevisions.REV_NO, file.REV_NO) - ) - ) - .limit(1); - - if (existing.length > 0) { - // 업데이트 - await tx - .update(swpDocumentRevisions) - .set(data) - .where(eq(swpDocumentRevisions.id, existing[0].id)); - return { id: existing[0].id, inserted: false, updated: true }; - } else { - // 삽입 - const result = await tx - .insert(swpDocumentRevisions) - .values(data) - .returning({ id: swpDocumentRevisions.id }); - return { id: result[0].id, inserted: true, updated: false }; - } -} - -async function upsertFile( - tx: any, - revisionId: number, - docNo: string, - file: SwpFileApiResponse -): Promise<{ id: number; inserted: boolean; updated: boolean }> { - const data: Omit<SwpDocumentFileInsert, "id"> = { - revision_id: revisionId, - DOC_NO: docNo, - FILE_NM: file.FILE_NM, - FILE_SEQ: file.FILE_SEQ, - FILE_SZ: file.FILE_SZ || null, - FLD_PATH: file.FLD_PATH || null, - STAT: file.STAT || null, - STAT_NM: file.STAT_NM || null, - IDX: file.IDX || null, - ACTV_NO: file.ACTV_NO || null, - CRTER: file.CRTER, - CRTE_DTM: file.CRTE_DTM, - CHGR: file.CHGR, - CHG_DTM: file.CHG_DTM, - sync_status: "synced", - last_synced_at: new Date(), - updated_at: new Date(), - }; - - // 기존 파일 확인 (revision + fileSeq로 unique) - const existing = await tx - .select() - .from(swpDocumentFiles) - .where( - and( - eq(swpDocumentFiles.revision_id, revisionId), - eq(swpDocumentFiles.FILE_SEQ, file.FILE_SEQ) - ) - ) - .limit(1); - - if (existing.length > 0) { - // 업데이트 - await tx - .update(swpDocumentFiles) - .set(data) - .where(eq(swpDocumentFiles.id, existing[0].id)); - return { id: existing[0].id, inserted: false, updated: true }; - } else { - // 삽입 - const result = await tx - .insert(swpDocumentFiles) - .values(data) - .returning({ id: swpDocumentFiles.id }); - return { id: result[0].id, inserted: true, updated: false }; - } -} - -// ============================================================================ -// 조회 헬퍼 함수들 -// ============================================================================ - -/** - * 프로젝트의 문서 계층 구조 조회 (복잡한 JSON 집계는 SQL 직접 실행) - */ -export async function getProjectDocumentsHierarchy(projectNo: string) { - return db.execute(sql` - SELECT - d."DOC_NO", - d."DOC_TITLE", - d."PROJ_NO", - d."PROJ_NM", - d."PKG_NO", - d."VNDR_CD", - d."CPY_NM", - d."MAT_NM", - d."LTST_REV_NO", - d."LTST_ACTV_STAT", - d.sync_status, - d.last_synced_at, - - COALESCE( - json_agg( - json_build_object( - 'id', r.id, - 'revNo', r."REV_NO", - 'stage', r."STAGE", - 'actvNo', r."ACTV_NO", - 'ofdcNo', r."OFDC_NO", - 'syncStatus', r.sync_status, - 'fileCount', ( - SELECT COUNT(*)::int - FROM swp.swp_document_files f2 - WHERE f2.revision_id = r.id - ), - 'files', ( - SELECT COALESCE(json_agg( - json_build_object( - 'id', f.id, - 'fileNm', f."FILE_NM", - 'fileSeq', f."FILE_SEQ", - 'fileSz', f."FILE_SZ", - 'fldPath', f."FLD_PATH", - 'stat', f."STAT", - 'statNm', f."STAT_NM", - 'syncStatus', f.sync_status, - 'createdAt', f.created_at - ) - ORDER BY f."FILE_SEQ" - ), '[]'::json) - FROM swp.swp_document_files f - WHERE f.revision_id = r.id - ) - ) - ORDER BY r."REV_NO" DESC - ) FILTER (WHERE r.id IS NOT NULL), - '[]'::json - ) as revisions, - - COUNT(DISTINCT r.id)::int as revision_count, - COUNT(f.id)::int as total_file_count - - 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 d."PROJ_NO" = ${projectNo} - GROUP BY - d."DOC_NO", - d."DOC_TITLE", - d."PROJ_NO", - d."PROJ_NM", - d."PKG_NO", - d."VNDR_CD", - d."CPY_NM", - d."MAT_NM", - d."LTST_REV_NO", - d."LTST_ACTV_STAT", - d.sync_status, - d.last_synced_at - ORDER BY d."DOC_NO" - `); -} - -/** - * 특정 문서의 모든 리비전 조회 - */ -export async function getDocumentRevisions(docNo: string) { - return db - .select() - .from(swpDocumentRevisions) - .where(eq(swpDocumentRevisions.DOC_NO, docNo)) - .orderBy(sql`${swpDocumentRevisions.REV_NO} DESC`); -} - -/** - * 특정 리비전의 모든 파일 조회 - */ -export async function getRevisionFiles(revisionId: number) { - return db - .select() - .from(swpDocumentFiles) - .where(eq(swpDocumentFiles.revision_id, revisionId)) - .orderBy(swpDocumentFiles.FILE_SEQ); -} - -/** - * 프로젝트 동기화 상태 조회 - */ -export async function getProjectSyncStatus(projectNo: string) { - return db.execute(sql` - SELECT - d."PROJ_NO", - d."PROJ_NM", - - 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(DISTINCT d."DOC_NO") FILTER (WHERE d.sync_status = 'synced')::int as docs_synced, - COUNT(DISTINCT d."DOC_NO") FILTER (WHERE d.sync_status = 'pending')::int as docs_pending, - COUNT(DISTINCT d."DOC_NO") FILTER (WHERE d.sync_status = 'error')::int as docs_error, - - COUNT(DISTINCT r.id) FILTER (WHERE r.sync_status = 'synced')::int as revs_synced, - COUNT(f.id) FILTER (WHERE f.sync_status = 'synced')::int as files_synced, - - MAX(d.last_synced_at) as last_sync_time - - 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 d."PROJ_NO" = ${projectNo} - GROUP BY d."PROJ_NO", d."PROJ_NM" - `); -} - |