diff options
Diffstat (limited to 'lib/vendor-document-list/enhanced-document-service.ts')
| -rw-r--r-- | lib/vendor-document-list/enhanced-document-service.ts | 782 |
1 files changed, 782 insertions, 0 deletions
diff --git a/lib/vendor-document-list/enhanced-document-service.ts b/lib/vendor-document-list/enhanced-document-service.ts new file mode 100644 index 00000000..00f40ea6 --- /dev/null +++ b/lib/vendor-document-list/enhanced-document-service.ts @@ -0,0 +1,782 @@ +// enhanced-document-service.ts +"use server" + +import { revalidatePath, unstable_cache } from "next/cache" +import { and, asc, desc, eq, ilike, or, count, avg } from "drizzle-orm" +import db from "@/db/db" +import { documentAttachments, documents, enhancedDocumentsView, issueStages, revisions, type EnhancedDocumentsView } from "@/db/schema/vendorDocu" +import { filterColumns } from "@/lib/filter-columns" +import type { + CreateDocumentInput, + UpdateDocumentInput, + CreateStageInput, + UpdateStageInput, + CreateRevisionInput, + UpdateRevisionStatusInput, + ApiResponse, + StageWithRevisions, + FullDocument, + DocumentAttachment, + Revision + } from "@/types/enhanced-documents" + +// 스키마 타입 정의 +export interface GetEnhancedDocumentsSchema { + page: number + perPage: number + search?: string + filters?: Array<{ + id: string + value: string | string[] + operator?: "eq" | "ne" | "like" | "ilike" | "in" | "notin" | "lt" | "lte" | "gt" | "gte" + }> + joinOperator?: "and" | "or" + sort?: Array<{ + id: keyof EnhancedDocumentsView + desc: boolean + }> +} + +// Repository 함수들 +export async function selectEnhancedDocuments( + tx: any, + options: { + where?: any + orderBy?: any + offset?: number + limit?: number + } +) { + const { where, orderBy, offset, limit } = options + + let query = tx.select().from(enhancedDocumentsView) + + if (where) { + query = query.where(where) + } + + if (orderBy) { + query = query.orderBy(...orderBy) + } + + if (offset !== undefined) { + query = query.offset(offset) + } + + if (limit !== undefined) { + query = query.limit(limit) + } + + return await query +} + +export async function countEnhancedDocuments(tx: any, where?: any) { + let query = tx.select({ count: count() }).from(enhancedDocumentsView) + + if (where) { + query = query.where(where) + } + + const result = await query + return result[0]?.count || 0 +} + +// 메인 서버 액션 +export async function getEnhancedDocuments( + input: GetEnhancedDocumentsSchema, + contractId: number +) { + return unstable_cache( + async () => { + try { + const offset = (input.page - 1) * input.perPage + + // 고급 필터 처리 + const advancedWhere = filterColumns({ + table: enhancedDocumentsView, + filters: input.filters || [], + joinOperator: input.joinOperator || "and", + }) + + // 전역 검색 처리 + let globalWhere + if (input.search) { + const searchTerm = `%${input.search}%` + globalWhere = or( + ilike(enhancedDocumentsView.title, searchTerm), + ilike(enhancedDocumentsView.docNumber, searchTerm), + ilike(enhancedDocumentsView.currentStageName, searchTerm), + ilike(enhancedDocumentsView.currentStageAssigneeName, searchTerm) + ) + } + + // 최종 WHERE 조건 + const finalWhere = and( + advancedWhere, + globalWhere, + eq(enhancedDocumentsView.contractId, contractId) + ) + + // 정렬 처리 + const orderBy = input.sort && input.sort.length > 0 + ? input.sort.map((item) => + item.desc + ? desc(enhancedDocumentsView[item.id]) + : asc(enhancedDocumentsView[item.id]) + ) + : [desc(enhancedDocumentsView.createdAt)] + + // 트랜잭션 실행 + const { data, total } = await db.transaction(async (tx) => { + const data = await selectEnhancedDocuments(tx, { + where: finalWhere, + orderBy, + offset, + limit: input.perPage, + }) + + const total = await countEnhancedDocuments(tx, finalWhere) + + return { data, total } + }) + + const pageCount = Math.ceil(total / input.perPage) + + return { data, pageCount, total } + } catch (err) { + console.error("Error fetching enhanced documents:", err) + return { data: [], pageCount: 0, total: 0 } + } + }, + [JSON.stringify(input), String(contractId)], + { + revalidate: 3600, + tags: [`enhanced-documents-${contractId}`], + } + )() +} + +// 통계 데이터 가져오기 +export async function getDocumentStatistics(contractId: number) { + return unstable_cache( + async () => { + try { + const result = await db + .select({ + total: count(), + overdue: count(enhancedDocumentsView.isOverdue), + dueSoon: count(), // 별도 필터링 필요 + highPriority: count(), + avgProgress: count(), // 별도 계산 필요 + }) + .from(enhancedDocumentsView) + .where(eq(enhancedDocumentsView.contractId, contractId)) + + // 더 정확한 통계를 위한 별도 쿼리들 + const [overdue, dueSoon, highPriority] = await Promise.all([ + db + .select({ count: count() }) + .from(enhancedDocumentsView) + .where( + and( + eq(enhancedDocumentsView.contractId, contractId), + eq(enhancedDocumentsView.isOverdue, true) + ) + ), + db + .select({ count: count() }) + .from(enhancedDocumentsView) + .where( + and( + eq(enhancedDocumentsView.contractId, contractId), + eq(enhancedDocumentsView.isOverdue, false), + // daysUntilDue <= 3 AND daysUntilDue >= 0 조건 추가 필요 + ) + ), + db + .select({ count: count() }) + .from(enhancedDocumentsView) + .where( + and( + eq(enhancedDocumentsView.contractId, contractId), + eq(enhancedDocumentsView.currentStagePriority, "HIGH") + ) + ) + ]) + + // 평균 진행률 계산 + const avgProgressResult = await db + .select({ + avgProgress: avg(enhancedDocumentsView.progressPercentage) + }) + .from(enhancedDocumentsView) + .where(eq(enhancedDocumentsView.contractId, contractId)) + + return { + total: result[0]?.total || 0, + overdue: overdue[0]?.count || 0, + dueSoon: dueSoon[0]?.count || 0, + highPriority: highPriority[0]?.count || 0, + avgProgress: Math.round(avgProgressResult[0]?.avgProgress || 0), + } + } catch (err) { + console.error("Error fetching document statistics:", err) + return { + total: 0, + overdue: 0, + dueSoon: 0, + highPriority: 0, + avgProgress: 0, + } + } + }, + [`document-stats-${contractId}`], + { + revalidate: 1800, // 30분 캐시 + tags: [`document-stats-${contractId}`], + } + )() +} + +// 빠른 필터 데이터 +export async function getQuickFilterData(contractId: number) { + return unstable_cache( + async () => { + try { + const [all, overdue, dueSoon, inProgress, highPriority] = await Promise.all([ + countEnhancedDocuments(db, eq(enhancedDocumentsView.contractId, contractId)), + countEnhancedDocuments(db, and( + eq(enhancedDocumentsView.contractId, contractId), + eq(enhancedDocumentsView.isOverdue, true) + )), + // dueSoon 조건은 SQL에서 직접 처리하거나 별도 뷰 필요 + countEnhancedDocuments(db, and( + eq(enhancedDocumentsView.contractId, contractId), + eq(enhancedDocumentsView.currentStageStatus, "IN_PROGRESS") + )), + countEnhancedDocuments(db, and( + eq(enhancedDocumentsView.contractId, contractId), + eq(enhancedDocumentsView.currentStageStatus, "IN_PROGRESS") + )), + countEnhancedDocuments(db, and( + eq(enhancedDocumentsView.contractId, contractId), + eq(enhancedDocumentsView.currentStagePriority, "HIGH") + )) + ]) + + return { + all, + overdue, + dueSoon: 0, // 별도 계산 필요 + inProgress, + highPriority, + } + } catch (err) { + console.error("Error fetching quick filter data:", err) + return { + all: 0, + overdue: 0, + dueSoon: 0, + inProgress: 0, + highPriority: 0, + } + } + }, + [`quick-filter-${contractId}`], + { + revalidate: 1800, + tags: [`quick-filter-${contractId}`], + } + )() +} + +// 단일 문서 상세 정보 +export async function getDocumentDetails(documentId: number) { + return unstable_cache( + async () => { + try { + const result = await db + .select() + .from(enhancedDocumentsView) + .where(eq(enhancedDocumentsView.documentId, documentId)) + .limit(1) + + return result[0] || null + } catch (err) { + console.error("Error fetching document details:", err) + return null + } + }, + [`document-details-${documentId}`], + { + revalidate: 1800, + tags: [`document-details-${documentId}`], + } + )() +} + + +// 문서 CRUD 작업들 + export async function createDocument(input: CreateDocumentInput): Promise<ApiResponse<number>> { + try { + const [newDocument] = await db.insert(documents).values({ + contractId: input.contractId, + docNumber: input.docNumber, + title: input.title, + pic: input.pic, + issuedDate: input.issuedDate, + }).returning({ id: documents.id }) + + revalidatePath("/documents") + return { + success: true, + data: newDocument.id, + message: "문서가 성공적으로 생성되었습니다." + } + } catch (error) { + console.error("Error creating document:", error) + return { + success: false, + error: "문서 생성 중 오류가 발생했습니다." + } + } + } + + export async function updateDocument(input: UpdateDocumentInput): Promise<ApiResponse<void>> { + try { + await db.update(documents) + .set({ + ...input, + updatedAt: new Date(), + }) + .where(eq(documents.id, input.id)) + + revalidatePath("/documents") + return { + success: true, + message: "문서가 성공적으로 업데이트되었습니다." + } + } catch (error) { + console.error("Error updating document:", error) + return { + success: false, + error: "문서 업데이트 중 오류가 발생했습니다." + } + } + } + + export async function deleteDocument(id: number): Promise<ApiResponse<void>> { + try { + await db.delete(documents).where(eq(documents.id, id)) + + revalidatePath("/documents") + return { + success: true, + message: "문서가 성공적으로 삭제되었습니다." + } + } catch (error) { + console.error("Error deleting document:", error) + return { + success: false, + error: "문서 삭제 중 오류가 발생했습니다." + } + } + } + + // 스테이지 CRUD 작업들 + export async function createStage(input: CreateStageInput): Promise<ApiResponse<number>> { + try { + const [newStage] = await db.insert(issueStages).values({ + documentId: input.documentId, + stageName: input.stageName, + planDate: input.planDate, + stageOrder: input.stageOrder ?? 0, + priority: input.priority ?? 'MEDIUM', + assigneeId: input.assigneeId, + assigneeName: input.assigneeName, + description: input.description, + reminderDays: input.reminderDays ?? 3, + }).returning({ id: issueStages.id }) + + revalidatePath("/documents") + return { + success: true, + data: newStage.id, + message: "스테이지가 성공적으로 생성되었습니다." + } + } catch (error) { + console.error("Error creating stage:", error) + return { + success: false, + error: "스테이지 생성 중 오류가 발생했습니다." + } + } + } + + export async function updateStage(input: UpdateStageInput): Promise<ApiResponse<void>> { + try { + await db.update(issueStages) + .set({ + ...input, + updatedAt: new Date(), + }) + .where(eq(issueStages.id, input.id)) + + revalidatePath("/documents") + return { + success: true, + message: "스테이지가 성공적으로 업데이트되었습니다." + } + } catch (error) { + console.error("Error updating stage:", error) + return { + success: false, + error: "스테이지 업데이트 중 오류가 발생했습니다." + } + } + } + + export async function updateStageStatus( + stageId: number, + status: string + ): Promise<ApiResponse<void>> { + try { + const updateData: any = { + stageStatus: status, + updatedAt: new Date(), + } + + // 상태에 따른 자동 날짜 업데이트 + if (status === 'COMPLETED' || status === 'APPROVED') { + updateData.actualDate = new Date().toISOString().split('T')[0] + } + + await db.update(issueStages) + .set(updateData) + .where(eq(issueStages.id, stageId)) + + revalidatePath("/documents") + return { + success: true, + message: "스테이지 상태가 업데이트되었습니다." + } + } catch (error) { + console.error("Error updating stage status:", error) + return { + success: false, + error: "스테이지 상태 업데이트 중 오류가 발생했습니다." + } + } + } + + // 리비전 CRUD 작업들 + export async function createRevision(input: CreateRevisionInput): Promise<ApiResponse<number>> { + try { + const result = await db.transaction(async (tx) => { + // 리비전 생성 + const [newRevision] = await tx.insert(revisions).values({ + issueStageId: input.issueStageId, + revision: input.revision, + uploaderType: input.uploaderType, + uploaderId: input.uploaderId, + uploaderName: input.uploaderName, + comment: input.comment, + submittedDate: new Date().toISOString().split('T')[0], + }).returning({ id: revisions.id }) + + // 첨부파일들 생성 + if (input.attachments && input.attachments.length > 0) { + await tx.insert(documentAttachments).values( + input.attachments.map(attachment => ({ + revisionId: newRevision.id, + fileName: attachment.fileName, + filePath: attachment.filePath, + fileType: attachment.fileType, + fileSize: attachment.fileSize, + })) + ) + } + + // 스테이지 상태를 SUBMITTED로 업데이트 + await tx.update(issueStages) + .set({ + stageStatus: 'SUBMITTED', + updatedAt: new Date(), + }) + .where(eq(issueStages.id, input.issueStageId)) + + return newRevision.id + }) + + revalidatePath("/documents") + return { + success: true, + data: result, + message: "리비전이 성공적으로 업로드되었습니다." + } + } catch (error) { + console.error("Error creating revision:", error) + return { + success: false, + error: "리비전 업로드 중 오류가 발생했습니다." + } + } + } + + export async function updateRevisionStatus(input: UpdateRevisionStatusInput): Promise<ApiResponse<void>> { + try { + const updateData: any = { + revisionStatus: input.revisionStatus, + reviewerId: input.reviewerId, + reviewerName: input.reviewerName, + reviewComments: input.reviewComments, + updatedAt: new Date(), + } + + // 상태에 따른 자동 날짜 업데이트 + const today = new Date().toISOString().split('T')[0] + if (input.revisionStatus === 'UNDER_REVIEW') { + updateData.reviewStartDate = today + } else if (input.revisionStatus === 'APPROVED') { + updateData.approvedDate = today + } else if (input.revisionStatus === 'REJECTED') { + updateData.rejectedDate = today + } + + await db.update(revisions) + .set(updateData) + .where(eq(revisions.id, input.id)) + + revalidatePath("/documents") + return { + success: true, + message: "리비전 상태가 업데이트되었습니다." + } + } catch (error) { + console.error("Error updating revision status:", error) + return { + success: false, + error: "리비전 상태 업데이트 중 오류가 발생했습니다." + } + } + } + + // 조회 작업들 + export async function getDocumentWithStages(documentId: number): Promise<ApiResponse<FullDocument>> { + try { + // 문서 기본 정보 + const [document] = await db.select() + .from(documents) + .where(eq(documents.id, documentId)) + + if (!document) { + return { + success: false, + error: "문서를 찾을 수 없습니다." + } + } + + // 스테이지와 리비전, 첨부파일 조회 + const stagesData = await db.select({ + stage: issueStages, + revision: revisions, + attachment: documentAttachments, + }) + .from(issueStages) + .leftJoin(revisions, eq(issueStages.id, revisions.issueStageId)) + .leftJoin(documentAttachments, eq(revisions.id, documentAttachments.revisionId)) + .where(eq(issueStages.documentId, documentId)) + .orderBy(asc(issueStages.stageOrder), desc(revisions.createdAt)) + + // 데이터 구조화 + const stagesMap = new Map<number, StageWithRevisions>() + + stagesData.forEach(({ stage, revision, attachment }) => { + if (!stagesMap.has(stage.id)) { + stagesMap.set(stage.id, { + ...stage, + revisions: [] + }) + } + + const stageData = stagesMap.get(stage.id)! + + if (revision) { + let revisionData = stageData.revisions.find(r => r.id === revision.id) + if (!revisionData) { + revisionData = { + ...revision, + attachments: [] + } + stageData.revisions.push(revisionData) + } + + if (attachment) { + revisionData.attachments.push(attachment) + } + } + }) + + const stages = Array.from(stagesMap.values()) + + return { + success: true, + data: { + ...document, + stages, + currentStage: stages.find(s => s.stageStatus === 'IN_PROGRESS'), + latestRevision: stages + .flatMap(s => s.revisions) + .sort((a, b) => new Date(b.createdAt).getTime() - new Date(a.createdAt).getTime())[0] + } + } + } catch (error) { + console.error("Error getting document with stages:", error) + return { + success: false, + error: "문서 조회 중 오류가 발생했습니다." + } + } + } + + // 문서의 스테이지와 리비전만 가져오는 경량화된 함수 + export async function getDocumentStagesWithRevisions(documentId: number): Promise<ApiResponse<StageWithRevisions[]>> { + try { + const stagesData = await db.select({ + stage: issueStages, + revision: revisions, + attachment: documentAttachments, + }) + .from(issueStages) + .leftJoin(revisions, eq(issueStages.id, revisions.issueStageId)) + .leftJoin(documentAttachments, eq(revisions.id, documentAttachments.revisionId)) + .where(eq(issueStages.documentId, documentId)) + .orderBy(asc(issueStages.stageOrder), desc(revisions.createdAt)) + + console.log(documentId, stagesData) + + // 데이터 구조화 + const stagesMap = new Map<number, StageWithRevisions>() + + stagesData.forEach(({ stage, revision, attachment }) => { + if (!stagesMap.has(stage.id)) { + stagesMap.set(stage.id, { + ...stage, + revisions: [] + }) + } + + const stageData = stagesMap.get(stage.id)! + + if (revision) { + let revisionData = stageData.revisions.find(r => r.id === revision.id) + if (!revisionData) { + revisionData = { + ...revision, + attachments: [] + } + stageData.revisions.push(revisionData) + } + + if (attachment) { + revisionData.attachments.push(attachment) + } + } + }) + + const stages = Array.from(stagesMap.values()) + + return { + success: true, + data: stages + } + } catch (error) { + console.log(error) + console.error("Error getting document stages with revisions:", error) + return { + success: false, + error: "스테이지 조회 중 오류가 발생했습니다." + } + } + } + + // 특정 스테이지의 리비전들만 가져오는 함수 + export async function getStageRevisions(stageId: number): Promise<ApiResponse<Array<Revision & { attachments: DocumentAttachment[] }>>> { + try { + const revisionsData = await db.select({ + revision: revisions, + attachment: documentAttachments, + }) + .from(revisions) + .leftJoin(documentAttachments, eq(revisions.id, documentAttachments.revisionId)) + .where(eq(revisions.issueStageId, stageId)) + .orderBy(desc(revisions.createdAt)) + + console.log(stageId, revisionsData) + + // 데이터 구조화 + const revisionsMap = new Map<number, Revision & { attachments: DocumentAttachment[] }>() + + revisionsData.forEach(({ revision, attachment }) => { + if (!revisionsMap.has(revision.id)) { + revisionsMap.set(revision.id, { + ...revision, + attachments: [] + }) + } + + const revisionData = revisionsMap.get(revision.id)! + + if (attachment) { + revisionData.attachments.push(attachment) + } + }) + + return { + success: true, + data: Array.from(revisionsMap.values()) + } + } catch (error) { + console.error("Error getting stage revisions:", error) + return { + success: false, + error: "리비전 조회 중 오류가 발생했습니다." + } + } + } + + export async function bulkUpdateStageStatus( + stageIds: number[], + status: string + ): Promise<ApiResponse<void>> { + try { + const updateData: any = { + stageStatus: status, + updatedAt: new Date(), + } + + if (status === 'COMPLETED' || status === 'APPROVED') { + updateData.actualDate = new Date().toISOString().split('T')[0] + } + + await db.update(issueStages) + .set(updateData) + .where( + and( + ...stageIds.map(id => eq(issueStages.id, id)) + ) + ) + + revalidatePath("/documents") + return { + success: true, + message: `${stageIds.length}개 스테이지가 업데이트되었습니다.` + } + } catch (error) { + console.error("Error bulk updating stage status:", error) + return { + success: false, + error: "일괄 상태 업데이트 중 오류가 발생했습니다." + } + } + }
\ No newline at end of file |