diff options
Diffstat (limited to 'app/service.ts')
| -rw-r--r-- | app/service.ts | 224 |
1 files changed, 224 insertions, 0 deletions
diff --git a/app/service.ts b/app/service.ts new file mode 100644 index 00000000..5c9b9a1d --- /dev/null +++ b/app/service.ts @@ -0,0 +1,224 @@ +import { unstable_cache } from "next/cache" +import { count, desc, asc, and, or, gte, lte, ilike, eq } from "drizzle-orm" +import { filterColumns } from "@/lib/filter-columns" +import db from "@/db/db" +import { bRfqs, projects, users } from "@/db/schema" // 실제 스키마 import 경로에 맞게 수정 +import { rfqDashboardView } from "@/db/schema" // 뷰 import +import type { SQL } from "drizzle-orm" +import { GetRFQDashboardSchema } from "./validations" + +export async function getRFQDashboard(input: GetRFQDashboardSchema) { + return unstable_cache( + async () => { + try { + const offset = (input.page - 1) * input.perPage; + + const rfqFilterMapping = createRFQFilterMapping(); + const joinedTables = getRFQJoinedTables(); + + console.log(input, "RFQ Dashboard input") + + // 1) 고급 필터 조건 + let advancedWhere: SQL<unknown> | undefined = undefined; + if (input.filters && input.filters.length > 0) { + advancedWhere = filterColumns({ + table: rfqDashboardView, + filters: input.filters, + joinOperator: input.joinOperator || 'and', + joinedTables, + customColumnMapping: rfqFilterMapping, + }); + } + + // 2) 기본 필터 조건 + let basicWhere: SQL<unknown> | undefined = undefined; + if (input.basicFilters && input.basicFilters.length > 0) { + basicWhere = filterColumns({ + table: rfqDashboardView, + filters: input.basicFilters, + joinOperator: input.basicJoinOperator || 'and', + joinedTables, + customColumnMapping: rfqFilterMapping, + }); + } + + // 3) 글로벌 검색 조건 + let globalWhere: SQL<unknown> | undefined = undefined; + if (input.search) { + const s = `%${input.search}%`; + + const validSearchConditions: SQL<unknown>[] = []; + + const rfqCodeCondition = ilike(rfqDashboardView.rfqCode, s); + if (rfqCodeCondition) validSearchConditions.push(rfqCodeCondition); + + const descriptionCondition = ilike(rfqDashboardView.description, s); + if (descriptionCondition) validSearchConditions.push(descriptionCondition); + + const projectNameCondition = ilike(rfqDashboardView.projectName, s); + if (projectNameCondition) validSearchConditions.push(projectNameCondition); + + const projectCodeCondition = ilike(rfqDashboardView.projectCode, s); + if (projectCodeCondition) validSearchConditions.push(projectCodeCondition); + + const picNameCondition = ilike(rfqDashboardView.picName, s); + if (picNameCondition) validSearchConditions.push(picNameCondition); + + const packageNoCondition = ilike(rfqDashboardView.packageNo, s); + if (packageNoCondition) validSearchConditions.push(packageNoCondition); + + const packageNameCondition = ilike(rfqDashboardView.packageName, s); + if (packageNameCondition) validSearchConditions.push(packageNameCondition); + + if (validSearchConditions.length > 0) { + globalWhere = or(...validSearchConditions); + } + } + + // 4) 날짜 조건 + let dueDateFromWhere: SQL<unknown> | undefined = undefined; + let dueDateToWhere: SQL<unknown> | undefined = undefined; + + if (input.dueDateFrom) { + const fromDate = new Date(input.dueDateFrom); + dueDateFromWhere = gte(rfqDashboardView.dueDate, fromDate); + } + + if (input.dueDateTo) { + const toDate = new Date(input.dueDateTo); + dueDateToWhere = lte(rfqDashboardView.dueDate, toDate); + } + + // 5) 진행률 조건 + let progressWhere: SQL<unknown> | undefined = undefined; + if (input.progressMin > 0 || input.progressMax < 100) { + const progressConditions: SQL<unknown>[] = []; + if (input.progressMin > 0) { + progressConditions.push(gte(rfqDashboardView.overallProgress, input.progressMin)); + } + if (input.progressMax < 100) { + progressConditions.push(lte(rfqDashboardView.overallProgress, input.progressMax)); + } + if (progressConditions.length > 0) { + progressWhere = and(...progressConditions); + } + } + + // 6) 최종 WHERE 조건 생성 + const whereConditions: SQL<unknown>[] = []; + + if (advancedWhere) whereConditions.push(advancedWhere); + if (basicWhere) whereConditions.push(basicWhere); + if (globalWhere) whereConditions.push(globalWhere); + if (dueDateFromWhere) whereConditions.push(dueDateFromWhere); + if (dueDateToWhere) whereConditions.push(dueDateToWhere); + if (progressWhere) whereConditions.push(progressWhere); + + const finalWhere = whereConditions.length > 0 ? and(...whereConditions) : undefined; + + // 7) 전체 데이터 수 조회 + const totalResult = await db + .select({ count: count() }) + .from(rfqDashboardView) + .where(finalWhere); + + const total = totalResult[0]?.count || 0; + + if (total === 0) { + return { data: [], pageCount: 0, total: 0 }; + } + + // 8) 정렬 및 페이징 처리된 데이터 조회 + const orderByColumns = input.sort.map((sort) => { + const column = sort.id as keyof typeof rfqDashboardView.$inferSelect; + return sort.desc ? desc(rfqDashboardView[column]) : asc(rfqDashboardView[column]); + }); + + if (orderByColumns.length === 0) { + orderByColumns.push(desc(rfqDashboardView.createdAt)); + } + + const rfqData = await db + .select() + .from(rfqDashboardView) + .where(finalWhere) + .orderBy(...orderByColumns) + .limit(input.perPage) + .offset(offset); + + const pageCount = Math.ceil(total / input.perPage); + + return { data: rfqData, pageCount, total }; + } catch (err) { + console.error("Error in getRFQDashboard:", err); + return { data: [], pageCount: 0, total: 0 }; + } + }, + [JSON.stringify(input)], + { + revalidate: 3600, + tags: ["rfq-dashboard"], + } + )(); +} + +// 헬퍼 함수들 +function createRFQFilterMapping() { + return { + // 뷰의 컬럼명과 실제 필터링할 컬럼 매핑 + rfqCode: rfqDashboardView.rfqCode, + description: rfqDashboardView.description, + status: rfqDashboardView.status, + projectName: rfqDashboardView.projectName, + projectCode: rfqDashboardView.projectCode, + picName: rfqDashboardView.picName, + packageNo: rfqDashboardView.packageNo, + packageName: rfqDashboardView.packageName, + dueDate: rfqDashboardView.dueDate, + overallProgress: rfqDashboardView.overallProgress, + createdAt: rfqDashboardView.createdAt, + }; +} + +function getRFQJoinedTables() { + return { + // 조인된 테이블 정보 (뷰이므로 실제로는 사용되지 않을 수 있음) + projects, + users, + }; +} + +// ================================================================ +// 3. RFQ Dashboard 타입 정의 +// ================================================================ +export interface RFQDashboard { + rfqId: number; + rfqCode: string; + description: string | null; + status: string; + dueDate: Date | null; + projectCode: string | null; + projectName: string | null; + projectType: string | null; + packageNo: string | null; + packageName: string | null; + picName: string | null; + totalAttachments: number; + initialVendorCount: number; + finalVendorCount: number; + initialResponseRate: number; + finalResponseRate: number; + overallProgress: number; + daysToDeadline: number; + createdAt: Date; + // 추가 계산 필드들 + statusBadge: { + variant: "default" | "outline" | "secondary" | "destructive" | "success"; + label: string; + }; + progressBadge: { + variant: "default" | "outline" | "secondary" | "destructive" | "success"; + label: string; + }; + urgencyLevel: "high" | "medium" | "low"; +}
\ No newline at end of file |